GP Extended Pricing Database Tables

--price books

select * from SOP10109

-- price sheets

select * from sop10110

--List of customers linked to each sheet

select * from rm00500

--master setup info

select * from iv10401

--pricing details

select * from iv10402

--stored procedure that will return price list for a given customer

create proc spGetPriceList (@customerid nvarchar(15)) as

Declare @pricesheet nvarchar(30)

select @pricesheet=PRCSHID from rm00500 where LINKCODE = @customerid

Declare @count int

Set @count=0

select @count=COUNT(*) from RM00500 where LINKCODE = @customerid

If @count <>0

Begin

select iv10402.prcshid, iv10402.itemnmbr, iv00101.ITEMDESC, iv10402.uofm,

(c.PSITMVAL*c.qtybsuom)- (iv10402.psitmval*iv10402.qtybsuom) as Price

from IV10402 inner join IV00101 on IV10402.ITEMNMBR=IV00101.ITEMNMBR

inner join IV10401 on IV10402.PRCSHID=IV10401.PRCSHID and IV10401.ITEMNMBR=IV10402.ITEMNMBR

left join

(select * from IV10402 where prcshid='base') as c

on IV10402.ITEMNMBR=c.ITEMNMBR and IV10402.UOFM=c.UOFM

where iv10402.PRCSHID=@pricesheet

union all

select prcshid, iv10402.itemnmbr, iv00101.ITEMDESC,uofm, psitmval*qtybsuom as Price

from IV10402 inner join IV00101 on IV10402.ITEMNMBR=IV00101.ITEMNMBR

where PRCSHID='BASE' and

iv10402.itemnmbr not in (select ITEMNMBR from IV10402 where PRCSHID=@pricesheet)

order by ITEMNMBR

ENd

If @count=0

Begin

select prcshid, iv10402.itemnmbr, iv00101.ITEMDESC, uofm, psitmval*qtybsuom as Price

from IV10402 inner join IV00101 on IV10402.ITEMNMBR=IV00101.ITEMNMBR

where PRCSHID='BASE'

order by ITEMNMBR

ENd