Print 'Doing pre run Checks to ensure script not already run';
Select * from Script_History where Script_Name='FACStoresrodCatUpdate17032025';
if @@ROWCOUNT!=0
	BEGIN
		RAISERROR('THIS SCRIPT HAS ALREADY BEEN RUN ON THIS SERVER, CANNOT CONTINUE, PLEASE CONTACT DEV',11,1);
		RETURN;
	END
GO
declare @StoreId int;
set @StoreId = (select default_value from system_default where Default_Name = 'Main_store_id');
If(@StoreId in (2,3,6,17,24,25,26,27,38,41,50,54,62,67,68,69,70,75,76,82,85,86,90,93,96,100,106,110,111,113,118,121,124,126,127,128,129,131,135,142,146,149,150,155,156,158,162,164,165,173,174,177,182,183,185,199,215,219,220,227,242,246,251,262,276,9,35,43,101,186,243))
begin
	update Product_Catalogue set Product_Catalogue_Status_ID = 3, Last_Catalogue_Status_ID = Product_Catalogue_Status_ID,Last_Catalogue_Changed = GETDATE()
,Last_Change_Reason = 'Store DC old 3rd party stock'
where product_id in (SELECT Product_ID FROM (
SELECT sic.Stock_item_id,p.Product_id,(SELECT top 1 Authorised_date FROM stock_receipt_line srl LEFT JOIN stock_receipt sr 
ON sr.stock_receipt_id = srl.stock_receipt_id
 WHERE srl.stock_item_id = si.stock_item_id AND Authorised_date IS NOT NULL ORDER BY Authorised_date DESC ) AS LastPurchaseDate FROM stock_item_count sic
LEFT JOIN stock_item si ON si.stock_item_id = sic.stock_item_id
LEFT JOIN product p ON p.product_id = si.product_id
WHERE si.item_stock_type_id IN (4,8) AND sic.stock_holding_group_id IN (2,3,4)
AND sic.stock_count > 0 AND (p.hq_product_id IS NULL OR p.hq_product_id =0)) a WHERE lastpurchasedate IS NULL OR lastpurchasedate <'2024-11-15')
and Supplier_ID !=1

end
Print 'Insert into script history if everything else is cool';
	Insert into Script_History (RunDate,Script_Name) Values (CURRENT_TIMESTAMP,'FACStoresrodCatUpdate17032025');
	IF @@ERROR != 0
		BEGIN
			RAISERROR ('UNABLE TO CONTINUE WITH SCRIPT', 11, 1);
			RETURN;
		END
GO