IF (select count(1) from System_Default where Default_Name = 'Region_Code' and Default_Value = 'SADC') = 1
BEGIN

update SystemSetting set SettingValueBit = 0 where SettingKey = 'PromoAutomationActivated';

/*create product change history for the changed items*/
insert into Product_Catalogue_History 
(Product_Catalogue_ID, MonthEnd, Product_Catalogue_Status_ID, Reason) 
select Product_Catalogue_ID, getdate(), Product_Catalogue_Status_ID, 'Promo 261 newlines set back to untried via script' from product_catalogue 
where 
	Product_ID in (select Product_ID from Promotion_Item where Promotion_ID = 261) and 
	Last_Catalogue_Status_ID = 7 /*Untried line*/ and 
	Product_Catalogue_Status_ID = 6 /*New line*/ and 
	Supplier_ID = 1

/*move newlines back to untried for this promo*/
update Product_Catalogue 
set 
	Product_Catalogue_Status_ID = 7 /*Untried line*/, 
	Last_Catalogue_Status_ID = 6 /*New line*/, 
	Last_Catalogue_Changed = getdate(), 
	Last_Change_Reason = 'Promo 261 newlines set back to untried via script'
where 
	Product_ID in (select Product_ID from Promotion_Item where Promotion_ID = 261) and 
	Last_Catalogue_Status_ID = 7 /*Untried line*/ and 
	Product_Catalogue_Status_ID = 6 /*New line*/ and 
	Supplier_ID = 1

/*delete stats for this promo*/
delete from Daily_Promotion_Statistics
where Promotion_Item_ID in (select Promotion_Item_ID from Promotion_Item where Promotion_ID = 261)

/*delete promo items for this promo*/
delete from Promotion_Item where Promotion_ID = 261

delete from DailyPromoData where Promotion_ID = 261

/*delete the promo*/
delete from Promotion where Promotion_ID = 261
END
GO