/*
Find the last price list change date
The issue is - the price has changed. When was it changed?
You can look at the change logs for an item and eventually find this information.
This 'function' can be called to find the last date a price was changed for a specific item - pricelist.
OITM and ITM1 contain the current 'loginstanc'
After comparing the current to the prior loginstanc for price changes, we would then
compare all successive loginstanc from highest to lowest until we show a price change
Created 05/27/15 dcm
Implementation is easy. Copy this function to SQL and execute it. This stores the function within the SQL
database so that it is ready to use.
Then you can call it at any time - passing it the item and price list number, and it returns the last date the
price was changed.
*/
Create Function [dbo].[DCM_FN_GetPricelistUpdateDateForItem]
(@item varchar(20), @pricelist int)
Returns DateTime
as
Begin
declare @curlog int, @ctr int, @dt datetime
select @curlog = MAX(p.loginstanc) from AIT1 p where p.ItemCode = @item
select @ctr = @curlog
if (select count(*) from AIT1 p where p.pricelist = @pricelist and p.LogInstanc = @ctr and p.itemcode = @item and (select x.price from ITm1 x where x.itemcode = p.ItemCode and x.pricelist = p.PriceList) <> p.price) > 0
begin
select @dt = updatedate from OITM i where i.itemcode = @item
end
else
begin
while @ctr > 0
begin
select @curlog = MAX(p.loginstanc) from AIT1 p where p.pricelist = @pricelist and p.ItemCode = @item and p.LogInstanc < @ctr
if (select count(*) from AIT1 p where p.pricelist = @pricelist and p.LogInstanc = @ctr and p.itemcode = @item
and (select x.price from AIT1 x where x.LogInstanc = @curlog and x.itemcode = p.ItemCode and x.pricelist = p.PriceList) <> p.price) > 0
begin
select @dt = updatedate from AITM i where i.itemcode = @item and i.LogInstanc = @ctr
select @ctr = 0
end
select @ctr = @ctr - 1
end
end
Return @dt
end