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