IF OBJECT_ID (N'f_4P_IVGetItemPrice') IS NOT NULL
DROP FUNCTION f_4P_IVGetItemPrice
GO
CREATE FUNCTION f_4P_IVGetItemPrice (
@itemnmbr varchar(31),
@custnmbr varchar(15)
)
-- select dbo.f_4P_IVGetItemPrice('100','mwk58')
RETURNS money
AS
BEGIN
declare @uomPrice as numeric(19,5)
declare @prclevel varchar(11)
declare @baseuofm varchar(9)
declare @pricmthd smallint
--get the price level for this customer
select @prclevel = prclevel
from rm00101 c
where custnmbr = @custnmbr
--get the pricing method
select @pricmthd = pricmthd
from IV00101 i
where ITEMNMBR = @itemnmbr
--get the base uofm for this item
select @baseuofm = baseuofm
from iv40201 uoms
join iv00101 i on i.uomschdl = uoms.uomschdl
where i.itemnmbr = @itemnmbr
select @uomprice =
case i.pricmthd when 1 then COALESCE(ip.uomprice , i4.uomprice,0.00) -- currency amount
when 2 then 0 --% of List Price
when 3 then i.CURRCOST * (1 + (COALESCE(ip.uomprice , i4.uomprice,0.00)/100)) -- % Markup – Current Cost
when 4 then 0 --% Markup – Standard Cost
when 5 then 0 --% Margin – Current Cost
when 6 then 0 --% Margin – Standard Cost
end
from iv00101 i with (nolock)
left join iv00108 ip with (nolock) on ip.itemnmbr = i.itemnmbr and ip.prclevel = @prclevel and ip.uofm = @baseuofm
left join iv00108 i4 with (nolock) on i4.itemnmbr = i.itemnmbr and i4.prclevel = i.PRCLEVEL and i4.uofm = @baseuofm
where i.itemnmbr = @itemnmbr
return (@uomprice)
END
GO