USE [ScottAromatics] GO /****** Object: StoredProcedure [dbo].[ASAS_usp_JIWA_BOM_WhereUsed_INMain] Script Date: 29/08/2017 11:16:48 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[ASAS_usp_JIWA_BOM_WhereUsed_INMain] @SP_PartNo AS VARCHAR(50) = NULL AS BEGIN --[ASAS_usp_JIWA_BOM_WhereUsed_INMain]'SAGE0020' --Used by: -- grant_all_user_tables -- where used report SET NOCOUNT ON DECLARE @MoneyDecimalPlaces AS TINYINT SELECT TOP 1 @MoneyDecimalPlaces = DecimalPlaces FROM FX_Currency WHERE IsLocal = 1 SELECT WhereUsed= BM_Main.BillNo, WhereUsed_Descripton=BM_Main.Description, WhereUsed_SOH=isNULL(WhereUsed_SOH.SOH,0), WhereUsed_ProductGroup = IN_Category1.Description, WhereUsed_LastCost =WhereUsed.LCost, WhereUsed_Last12MonthSalesHistory =Last12SOLD, LineNumber=BM_Inputs.ItemNo, LineType = CASE WHEN IsRatio = 1 THEN 'Ratio' ELSE 'Fixed' END, ThisItemPartNO = IN_Main.PartNo, ThisItemDescription= COALESCE(IN_Main.Description, ''), ThisItemLastCost = COALESCE(IN_Main.LCost, 0), Formula=BM_Inputs.Quantity, ThisItem_SOH = isNULL(SOH.SOH,0) --Quantity * COALESCE(IN_Main.LCost, 0), --Notes=COALESCE(BM_Inputs.Note, '') --QtyDecimals= COALESCE(IN_Main.DecimalPlaces, 0), --MoneyDecimalPlaces = @MoneyDecimalPlaces FROM BM_Main BM_Main INNER JOIN BM_Stages BM_Stages ON BM_Main.RecID = BM_Stages.BM_Main_RecID INNER JOIN BM_Inputs BM_Inputs ON BM_Stages.RecID = BM_Inputs.BM_Stages_RecID INNER JOIN IN_Main IN_Main ON BM_Inputs.InventoryID = IN_Main.InventoryID Left outer join IN_Main WhereUsed on WhereUsed.PartNo = BM_Main.Billno left outer join IN_Category1 on WhereUsed.Catagory1ID = IN_Category1.Category1ID Left outer join (select InventoryID, SOH = sum(quantityleft) from IN_SOH where IN_LogicalID = 'ZZZZZZZZZZ0000000000' group by InventoryID) as SOH on SOH.InventoryID = IN_Main.InventoryID Left outer join (select InventoryID, SOH = sum(quantityleft) from IN_SOH where IN_LogicalID = 'ZZZZZZZZZZ0000000000' group by Inventoryid) as WhereUsed_SOH on WhereUsed_SOH.InventoryID = WhereUsed.InventoryID Left outer join (select InventoryID, Last12SOLD= sum(Quantity) from SO_Sales where DateDiff(mm,InvoiceDate, getdate()) <=12 group by InventoryID) as WhereUsed_Last12MonthSalesHistory on WhereUsed_Last12MonthSalesHistory.InventoryID = WhereUsed.InventoryID WHERE IN_Main.PartNo = @SP_PartNo --select top 100 DateDiff(mm,InvoiceDate, getdate()), * from SO_Sales order by invoiceDate desc -- [ASAS_usp_JIWA_BOM_WhereUsed]'CITR0050','CITR0050' ORDER BY BM_Main.BillNo, BM_Inputs.ItemNo -- select * from BM_Inputs SET NOCOUNT OFF END GO