--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