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