Print 'Doing pre run Checks to ensure script not already run';
Select * from Script_History where Script_Name='StockViewUpdate13062025';

if @@ROWCOUNT!=0
	BEGIN
		RAISERROR('THIS SCRIPT HAS ALREADY BEEN RUN ON THIS SERVER, CANNOT CONTINUE, PLEASE CONTACT DEV',11,1);
		RETURN;
	END
GO

ALTER VIEW [dbo].[vwStock]
AS
SELECT        Product_ID, Product_Category_ID, Product_Brand_ID, Make, Product_Code, Description, Stock_Code, Item_Stock_Type_ID, Serial_Number, Condition, Unit_Cost, Notes, LayByeDate, Target_Release_Date, Source_Contract, 
                         Stock_Holding_Group_ID, Stock_Count, stocktbldesc, Stock_Item_Count_ID, Category, Division, Type, Stock_Value, HQ_Product_ID, (CASE WHEN PromoRRP > 0 THEN
                             (SELECT        CASE WHEN Soiled = 1 THEN Standard_RRP ELSE PromoRRP END) ELSE Standard_RRP END) AS RRP, (CASE WHEN PromoRRPExVat > 0 THEN
                             (SELECT        CASE WHEN Soiled = 1 THEN Standard_RRP_ExVat ELSE PromoRRPExVat END) ELSE Standard_RRP_ExVat END) - Unit_Cost AS GPRands,
							CAST(((( (CASE WHEN PromoRRPExVat > 0 THEN  (SELECT        CASE WHEN Soiled = 1 THEN Standard_RRP_ExVat ELSE PromoRRPExVat END) ELSE Standard_RRP_ExVat END) -Unit_Cost))/
							
							(case when (CASE WHEN PromoRRPExVat > 0 THEN  (SELECT        CASE WHEN Soiled = 1 THEN Standard_RRP_ExVat ELSE PromoRRPExVat END) ELSE Standard_RRP_ExVat END) =0 then 1 else 
							(CASE WHEN PromoRRPExVat > 0 THEN  (SELECT        CASE WHEN Soiled = 1 THEN Standard_RRP_ExVat ELSE PromoRRPExVat END) ELSE Standard_RRP_ExVat END) end) ) *100 as numeric(38,2))   as GPPerc			 
							 , Store_ReProcessed, HQ_Processed, 
                         (CASE WHEN PromoRRP > 0 THEN 'ACTIVE' ELSE 'NONE' END) AS Promo, CCF_Funded, (CASE WHEN Supplier_ID IS NULL THEN 'Buyshop' ELSE Supplier_ID END) AS Supplier_ID, Division_ID, CategoryId, 
                         Sales_Date AS Last_Sale_Date, Product_Catalogue_Status, Original_Purchase_Date, DATEDIFF(DAY, Target_Release_Date, Sales_Date) AS Days_To_Sell, DATEDIFF(DAY, Target_Release_Date, 
                         (CASE WHEN Sales_Date IS NULL THEN GETDATE() ELSE Sales_Date END)) AS Days_In_Store, Returned, skuIn AS SKU, CAST((CASE WHEN ItemOnHold IS NULL THEN 0 ELSE 1 END) AS BIT) AS OnHold, 
                         CAST((CASE WHEN Soiled IS NULL THEN 0 ELSE 1 END) AS BIT) AS Soiled, Standard_RRP, Standard_RRP_ExVat
FROM            (SELECT        dbo.Product.Product_ID, dbo.Product.Product_Category_ID, dbo.Product.Product_Brand_ID, dbo.Product.Product_Code, dbo.Product.Description, dbo.Stock_Item.Stock_Code, dbo.Stock_Item.Item_Stock_Type_ID, 
                                                    dbo.Stock_Item.Serial_Number, ic.Description AS Condition, dbo.Stock_Item.Unit_Cost, ss.Notes, dbo.Lay_Bye.Expiry_Date AS LayByeDate, dbo.Stock_Item.Target_Release_Date, dbo.Stock_Item.Source_Contract, 
                                                    dbo.Stock_Item_Count.Stock_Holding_Group_ID, dbo.Stock_Item_Count.Stock_Count, dbo.Stock_Item.Item_Description AS stocktbldesc, dbo.Stock_Item_Count.Stock_Item_Count_ID, 
                                                    dbo.vwProductCategory.Category, dbo.vwProductCategory.Division, dbo.vwProductCategory.Type, 
                                                    (CASE WHEN stock_item.is_Client_Stock = 1 THEN 0 ELSE dbo.Stock_Item.Unit_Cost * dbo.Stock_Item_Count.Stock_Count END) AS Stock_Value, dbo.Product.HQ_Product_ID, ISNULL
                                                        ((SELECT        TOP (1) RRP
                                                            FROM            dbo.Pricing
                                                            WHERE        (Stock_Item_ID = dbo.Stock_Item.Stock_Item_ID)
                                                            ORDER BY To_Date DESC), 0) AS Standard_RRP, ISNULL
                                                        ((SELECT        TOP (1) RRP - RRP_VAT AS Expr1
                                                            FROM            dbo.Pricing AS Pricing_1
                                                            WHERE        (Stock_Item_ID = dbo.Stock_Item.Stock_Item_ID)
                                                            ORDER BY To_Date DESC), 0) AS Standard_RRP_ExVat, dbo.Product.Store_ReProcessed, dbo.Product.HQ_Processed, ISNULL
                                                        ((SELECT        TOP (1) pim.Promo_RRP
                                                            FROM            dbo.Promotion_Item AS pim INNER JOIN
                                                                                     dbo.Promotion AS p ON pim.Promotion_ID = p.Promotion_ID
                                                            WHERE        (p.Period_Start <= CURRENT_TIMESTAMP) AND (p.Period_End >= CURRENT_TIMESTAMP) AND (pim.Product_ID = dbo.Stock_Item.Product_ID) AND (dbo.Stock_Item.Item_Stock_Type_ID IN (4, 5))
                                                            ORDER BY p.Period_Start DESC), 0) AS PromoRRP, ISNULL
                                                        ((SELECT        TOP (1) pim.Promo_RRP - pim.Promo_RRP_VAT AS Expr1
                                                            FROM            dbo.Promotion_Item AS pim INNER JOIN
                                                                                     dbo.Promotion AS p ON pim.Promotion_ID = p.Promotion_ID
                                                            WHERE        (p.Period_Start <= CURRENT_TIMESTAMP) AND (p.Period_End >= CURRENT_TIMESTAMP) AND (pim.Product_ID = dbo.Stock_Item.Product_ID) AND (dbo.Stock_Item.Item_Stock_Type_ID IN (4, 5))
                                                            ORDER BY p.Period_Start DESC), 0) AS PromoRRPExVat, ISNULL(dbo.Stock_Item.CCF_Funded, 0) AS CCF_Funded,
                                                        (SELECT        TOP (1) s.Name
                                                          FROM            dbo.Product_Catalogue AS pc INNER JOIN
                                                                                    dbo.Supplier AS s ON pc.Supplier_ID = s.Supplier_ID
                                                          WHERE        (pc.Product_ID = dbo.Stock_Item.Product_ID)
                                                          ORDER BY pc.Product_Catalogue_ID) AS Supplier_ID, dbo.vwProductCategory.Division_ID, dbo.vwProductCategory.CategoryId, dbo.Stock_Item.Sales_Date, pb.Description AS Make,
                                                        (SELECT        TOP (1) pcs.Description
                                                          FROM            dbo.Product_Catalogue AS pc INNER JOIN
                                                                                    dbo.Product_Catalogue_Status AS pcs ON pc.Product_Catalogue_Status_ID = pcs.Product_Catalogue_Status_ID
                                                          WHERE        (pc.Product_ID = dbo.Stock_Item.Product_ID)
                                                          ORDER BY pc.Product_Catalogue_ID) AS Product_Catalogue_Status, dbo.Stock_Item.Original_Purchase_Date,
                                                        (SELECT        TOP (1) Created
                                                          FROM            dbo.Goods_Return AS GR
                                                          WHERE        (Stock_Item_ID = dbo.Stock_Item.Stock_Item_ID)
                                                          ORDER BY Created DESC) AS Returned,
                                                        (SELECT        TOP (1) UniqueCode
                                                          FROM            dbo.HQ_Product_Stock AS hq
                                                          WHERE        (Product_ID = dbo.Product.Product_ID) AND (HQ_Branch_ID =
                                                                                        (SELECT        TOP (1) Default_Value
                                                                                          FROM            dbo.System_Default AS sd
                                                                                          WHERE        (Default_Name = 'HQ_Store_Branch')))) AS skuIn, oh.ItemOnHold, dbo.Stock_Item.Soiled
                          FROM            dbo.Stock_Item INNER JOIN
                                                    dbo.Product ON dbo.Stock_Item.Product_ID = dbo.Product.Product_ID INNER JOIN
                                                    dbo.Stock_Item_Count ON dbo.Stock_Item.Stock_Item_ID = dbo.Stock_Item_Count.Stock_Item_ID INNER JOIN
                                                    dbo.vwProductCategory ON dbo.Product.Product_Category_ID = dbo.vwProductCategory.Product_Category_ID INNER JOIN
                                                    dbo.Product_Brand AS pb ON dbo.Product.Product_Brand_ID = pb.Product_Brand_ID LEFT OUTER JOIN
                                                    dbo.Buyshop_Trans_Line AS ss ON ss.Buyshop_Trans_Line_ID = dbo.Stock_Item.Buyshop_Trans_Line_ID LEFT OUTER JOIN
                                                    dbo.Item_Condition AS ic ON ic.Item_Condition_ID = ss.Item_Condition_ID LEFT OUTER JOIN
                                                    dbo.Lay_Bye ON dbo.Lay_Bye.Stock_Item_ID = dbo.Stock_Item.Stock_Item_ID AND dbo.Lay_Bye.Lay_Bye_Status_ID = 1 AND dbo.Lay_Bye.laybystockstatusid = 3 LEFT OUTER JOIN
                                                        (SELECT DISTINCT Stock_Item_ID, 1 AS ItemOnHold
                                                          FROM            (SELECT        Stock_Item_ID,
                                                                                                                  (SELECT        TOP (1) Created
                                                                                                                    FROM            dbo.StockOnHold AS h
                                                                                                                    WHERE        (Stock_Item_ID = s.Stock_Item_ID)
                                                                                                                    ORDER BY StockOnHoldID DESC) AS created,
                                                                                                                  (SELECT        TOP (1) StockOnHoldStatusID
                                                                                                                    FROM            dbo.StockOnHold AS h
                                                                                                                    WHERE        (Stock_Item_ID = s.Stock_Item_ID)
                                                                                                                    ORDER BY StockOnHoldID DESC) AS stockonholdstatusid
                                                                                    FROM            dbo.StockOnHold AS s) AS a
                                                          WHERE        (stockonholdstatusid = 1)) AS oh ON dbo.Stock_Item.Stock_Item_ID = oh.Stock_Item_ID
                          WHERE        (dbo.Stock_Item_Count.Stock_Count > 0)) AS intbl
GO

Print 'Insert into script history if everything else is cool';

	Insert into Script_History (RunDate,Script_Name) Values (CURRENT_TIMESTAMP,'StockViewUpdate13062025');
	IF @@ERROR != 0
		BEGIN
			RAISERROR ('UNABLE TO CONTINUE WITH SCRIPT', 11, 1);
			RETURN;
		END
GO