If Galebreaker or Rega have ran finalise update then they will need to rerun the script below
ALTER FUNCTION [dbo].[JCTransactionsNew] ( @ProjectCode char(8), @WorksOrder float, @RejectType char(1), @RejectNumber float, @DateFromInt int, @DateToInt int, @JOBCST_MANUF_BREAKOUT int, @JBCST_USER_TYPE char(2), @JOBCST_SUBWO_INCLUDE int ) RETURNS @Results table ( Works_Order float, --[1] Transaction_Type varchar(2), --[2] Stock_Type varchar(2), --[3] Manual_Transaction smallint, --[4] Unique_Ref int, --[5] Source_Works_Order float, --[6] Qty float, --[7] Fraction float, --[8] Machine_Set_Time int, --[9] Machine_Op_Time int, --[10] Labour_Set_Time int, --[11] Labour_Op_Time int, --[12] Effective_Set_Time int, --[13] 'Machine' time for 'Machine' resources or 'Labour' time for 'Labour' resources Effective_Op_Time int, --[14] 'Machine' time for 'Machine' resources or 'Labour' time for 'Labour' resources Machine_Set_Cost float, --[15] Machine_Op_Cost float, --[16] Labour_Set_Cost float , --[17] Labour_Op_Cost float, --[18] BI_Cost float, --[19] RM_Cost float, --[20] SC_Cost float, --[21] US_Cost float, --[22] OT_Cost float, --[23] BY_Value float, --[24] MA_Cost float, --[25] Rework_Time int, --[26] Rework_Cost float, --[27] Cost_Centre varchar(10), --[28] Notes varchar(200) --[29] ) BEGIN DECLARE @ProjectWO table ( Works_Order float ) IF RTRIM(@ProjectCode) <> '' INSERT @ProjectWO(Works_Order) SELECT WOHEAD.Works_Order FROM WOHEAD WHERE WOHEAD.Project_Code = @ProjectCode; -- MA Transactions WITH MYTABLE AS ( SELECT STKDETAIL.Works_Order_Issued_To, PSBATCH.Works_Order_Created_ AS Works_Order_Created_On, PSBATCH.Part_Number AS Part_Number, SUM(STKDETAIL.Stock_Qty * CASE WHEN Receipt_Or_Issue = 'R' THEN -1 ELSE 1 END) AS Quantity, MAX(dbo.IntToDateTime(STKDETAIL.Date_Booked, STKDETAIL.Time_Booked)) AS DateTime_Booked FROM STKDETAIL INNER JOIN PSBATCH ON PSBATCH.Batch_Unique_No = STKDETAIL.UniqBatchLocatioo WHERE (RTRIM(@ProjectCode) = '' OR EXISTS(SELECT * FROM @ProjectWO WHERE [@ProjectWO].Works_Order = STKDETAIL.Works_Order_Issued_To)) AND (@WorksOrder = 0 OR STKDETAIL.Works_Order_Issued_To = @WorksOrder) AND (@RejectNumber = 0 OR (STKDETAIL.Reject_Note = @RejectNumber AND STKDETAIL.Reject_Type = @RejectType)) AND PSBATCH.Works_Order_Created_ > 0 AND (@DateFromInt = 0 OR STKDETAIL.Date_Booked >= @DateFromInt) AND (@DateToInt = 0 OR STKDETAIL.Date_Booked <= @DateToInt) GROUP BY STKDETAIL.Works_Order_Issued_To, PSBATCH.Works_Order_Created_, PSBATCH.Part_Number HAVING SUM(STKDETAIL.Stock_Qty * CASE WHEN Receipt_Or_Issue = 'R' THEN -1 ELSE 1 END) <> 0 UNION ALL SELECT WOASSEM.Main_Works_Order_No, WOASSEM.Child_Works_Order_No, WOCMPNTS.Part_Number, WOCMPNTS.Quantity_Reqd - WOCMPNTS.Issued_Qty, GETDATE() FROM WOASSEM INNER JOIN WOHEAD ON WOHEAD.Works_Order = WOASSEM.Main_Works_Order_No INNER JOIN WOHEAD SUBWO ON SUBWO.Works_Order = WOASSEM.Child_Works_Order_No INNER JOIN WOCMPNTS ON WOCMPNTS.Component_Unique = WOASSEM.Component_Unique WHERE @JOBCST_SUBWO_INCLUDE = 1 AND (RTRIM(@ProjectCode) = '' OR EXISTS(SELECT * FROM @ProjectWO WHERE [@ProjectWO].Works_Order = WOASSEM.Main_Works_Order_No)) AND (@WorksOrder = 0 OR WOASSEM.Main_Works_Order_No = @WorksOrder) AND @DateFromInt = 0 AND @DateToInt = 0 AND WOHEAD.Complete = 0 AND SUBWO.Complete = 0 -- only do this if both THIS works order AND the sub-works order are still 'pending' AND WOCMPNTS.Quantity_Reqd > 0 AND (WOCMPNTS.Quantity_Reqd - WOCMPNTS.Issued_Qty) / WOCMPNTS.Quantity_Reqd > 0 ) INSERT @Results SELECT MYTABLE.Works_Order_Issued_To, 'MA', 'MA', 0, 0, MYTABLE.Works_Order_Created_On, SUM(MYTABLE.Quantity), CASE WHEN STKHEAD.Traceable = 0 OR @JOBCST_MANUF_BREAKOUT = 0 THEN 1 ELSE WC.Fraction_Per_1 * SUM(MYTABLE.Quantity) END, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 OR @JOBCST_MANUF_BREAKOUT = 0 THEN 0 ELSE WC.Machine_Set_Time_Per_1 END AS Machine_Set_Time, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 OR @JOBCST_MANUF_BREAKOUT = 0 THEN 0 ELSE WC.Machine_Op_Time_Per_1 END AS Machine_Op_Time, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 OR @JOBCST_MANUF_BREAKOUT = 0 THEN 0 ELSE WC.Labour_Set_Time_Per_1 END AS Labour_Set_Time, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 OR @JOBCST_MANUF_BREAKOUT = 0 THEN 0 ELSE WC.Labour_Op_Time_Per_1 END AS Labour_Op_Time, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 OR @JOBCST_MANUF_BREAKOUT = 0 THEN 0 ELSE WC.Effective_Set_Time_Per_1 END AS Effective_Set_Time, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 OR @JOBCST_MANUF_BREAKOUT = 0 THEN 0 ELSE WC.Effective_Op_Time_Per_1 END AS Effective_Op_Time, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 OR @JOBCST_MANUF_BREAKOUT = 0 THEN 0 ELSE WC.Machine_Set_Cost_Per_1 END AS Machine_Set_Cost, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 OR @JOBCST_MANUF_BREAKOUT = 0 THEN 0 ELSE WC.Machine_Op_Cost_Per_1 END AS Machine_Op_Cost, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 OR @JOBCST_MANUF_BREAKOUT = 0 THEN 0 ELSE WC.Labour_Set_Cost_Per_1 END AS Labour_Set_Cost, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 OR @JOBCST_MANUF_BREAKOUT = 0 THEN 0 ELSE WC.Labour_Op_Cost_Per_1 END AS Labour_Op_Cost, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 THEN 0 ELSE CASE WHEN @JOBCST_MANUF_BREAKOUT = 0 THEN 0.00000 ELSE WC.BI_Cost_Per_1 END END AS BI_Cost, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 OR @JOBCST_MANUF_BREAKOUT = 0 THEN 0 ELSE WC.RM_Cost_Per_1 END AS RM_Cost, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 OR @JOBCST_MANUF_BREAKOUT = 0 THEN 0 ELSE WC.SC_Cost_Per_1 END AS SC_Cost, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 OR @JOBCST_MANUF_BREAKOUT = 0 THEN 0 ELSE WC.US_Cost_Per_1 END AS US_Cost, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 OR @JOBCST_MANUF_BREAKOUT = 0 THEN 0 ELSE WC.OT_Cost_Per_1 END AS OT_Cost, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 OR @JOBCST_MANUF_BREAKOUT = 0 THEN 0 ELSE WC.BY_Value_Per_1 END AS BY_Value, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 THEN dbo.fn_GetEffectiveStandardCost(MYTABLE.Part_Number, dbo.DateTimeToIntDate(MAX(DateTime_Booked)), dbo.DateTimeToIntTime(MAX(DateTime_Booked))) WHEN @JOBCST_MANUF_BREAKOUT = 1 THEN WC.MA_Cost_Per_1 ELSE WC.Machine_Set_Cost_Per_1 + WC.Machine_Op_Cost_Per_1 + WC.Labour_Set_Cost_Per_1 + WC.Labour_Op_Cost_Per_1 + WC.BI_Cost_Per_1 + WC.RM_Cost_Per_1 + WC.SC_Cost_Per_1 + WC.US_Cost_Per_1 + WC.OT_Cost_Per_1 - WC.BY_Value_Per_1 + WC.MA_Cost_Per_1 END AS MA_Cost, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 OR @JOBCST_MANUF_BREAKOUT = 0 THEN 0 ELSE WC.Rework_Time_Per_1 END AS Rework_Time, SUM(MYTABLE.Quantity) * CASE WHEN STKHEAD.Traceable = 0 OR @JOBCST_MANUF_BREAKOUT = 0 THEN 0 ELSE WC.Rework_Cost_Per_1 END AS Rework_Cost, dbo.GetProjectCostCentre(MYTABLE.Part_Number, 'MA') AS Cost_Centre, '' AS Notes FROM MYTABLE INNER JOIN STKHEAD ON STKHEAD.Part_Number = MYTABLE.Part_Number AND STKHEAD.Stock_Type = 'MA' INNER JOIN INT_JC_WO_COST WC ON WC.Works_Order = MYTABLE.Works_Order_Created_On GROUP BY MYTABLE.Works_Order_Issued_To, MYTABLE.Works_Order_Created_On, MYTABLE.Part_Number, STKHEAD.Traceable, WC.Machine_Set_Time_Per_1, WC.Machine_Op_Time_Per_1, WC.Labour_Set_Time_Per_1, WC.Labour_Op_Time_Per_1, WC.Effective_Set_Time_Per_1, WC.Effective_Op_Time_Per_1, WC.Machine_Set_Cost_Per_1, WC.Machine_Op_Cost_Per_1, WC.Labour_Set_Cost_Per_1, WC.Labour_Op_Cost_Per_1, WC.BI_Cost_Per_1, WC.RM_Cost_Per_1, WC.SC_Cost_Per_1, WC.US_Cost_Per_1, WC.OT_Cost_Per_1, WC.BY_Value_Per_1, WC.MA_Cost_Per_1, WC.Fraction_Per_1, WC.Rework_Time_Per_1, WC.Rework_Cost_Per_1 --BI Transactions INSERT @Results SELECT STKDETAIL.Works_Order_Issued_To, 'BI', 'BI', 0 AS Manual_Transaction, PSBATCH.Batch_Unique_No AS Unique_Ref, 0 AS Source_Works_Order, SUM(STKDETAIL.Stock_Qty * CASE WHEN Receipt_Or_Issue = 'R' THEN -1 ELSE 1 END) AS Qty, 1, 0,0,0,0,0,0,0,0,0,0, SUM(STKDETAIL.Stock_Qty * CASE WHEN Receipt_Or_Issue = 'R' THEN -1 ELSE 1 END) * CASE WHEN STKHEAD.Traceable = 0 THEN dbo.fn_GetEffectiveStandardCost(STKDETAIL.Part_Number, MAX(STKDETAIL.Date_Booked), MAX(STKDETAIL.Time_Booked)) ELSE CASE WHEN PSBATCH.PO_Number > 0 THEN PSBATCH.Purchase_Cost / PSBATCH.Quantity ELSE (PSBATCH.Cost_Material + PSBATCH.Cost_Hrs + PSBATCH.Cost_Sub_Contract + PSBATCH.Cost_Others) / PSBATCH.Quantity END END AS BI_Cost, 0,0,0,0,0,0,0,0, dbo.GetProjectCostCentre(STKDETAIL.Part_Number, 'BI') AS Cost_Centre, '' AS Notes FROM STKDETAIL INNER JOIN STKHEAD ON STKHEAD.Part_Number = STKDETAIL.Part_Number AND STKHEAD.Stock_Type = 'MA' INNER JOIN PSBATCH ON PSBATCH.Batch_Unique_No = STKDETAIL.UniqBatchLocatioo WHERE (RTRIM(@ProjectCode) = '' OR EXISTS(SELECT * FROM @ProjectWO WHERE [@ProjectWO].Works_Order = STKDETAIL.Works_Order_Issued_To)) AND (@WorksOrder = 0 OR STKDETAIL.Works_Order_Issued_To = @WorksOrder) AND (@RejectNumber = 0 OR (STKDETAIL.Reject_Note = @RejectNumber AND STKDETAIL.Reject_Type = @RejectType)) AND (@DateFromInt = 0 OR STKDETAIL.Date_Booked >= @DateFromInt) AND (@DateToInt = 0 OR STKDETAIL.Date_Booked <= @DateToInt) AND PSBATCH.Works_Order_Created_ = 0 AND PSBATCH.Quantity > 0 GROUP BY STKDETAIL.Works_Order_Issued_To, STKDETAIL.Part_Number, STKHEAD.Traceable, PSBATCH.Batch_Unique_No, PSBATCH.PO_Number, PSBATCH.Purchase_Cost, PSBATCH.Cost_Material, PSBATCH.Cost_Hrs, PSBATCH.Cost_Sub_Contract, PSBATCH.Cost_Others, PSBATCH.Quantity HAVING SUM(STKDETAIL.Stock_Qty * CASE WHEN Receipt_Or_Issue = 'R' THEN -1 ELSE 1 END) <> 0 --RM Transactions INSERT @Results SELECT PSRMAUD.Works_Order_Number, 'RM', 'RM', 0, PSRMBAT.GIN_No, 0, SUM(PSRMAUD.Move_Qty * CASE WHEN Add_to_Stock = 1 THEN -1 ELSE 1 END), 1, 0,0,0,0,0,0,0,0,0,0,0, SUM(PSRMAUD.Move_Qty * CASE WHEN Add_to_Stock = 1 THEN -1 ELSE 1 END * PSRMBAT.Batch_Price / CASE WHEN PSRMBAT.Batch_Price_Per ='' THEN 1 WHEN PSRMBAT.Batch_Price_Per = '0' THEN 1 ELSE dbo.RMConvGetDim(PSRMBAT.Batch_Price_Per, RMFORM.Stock_Measure) END), 0,0,0,0,0,0,0, ISNULL((SELECT STOCKTYP.Project_Cost_Centre FROM STOCKTYP WHERE STOCKTYP.Stock_Type = 'RM'), ''), '' FROM PSRMAUD INNER JOIN PSRMBAT ON PSRMBAT.GIN_No = PSRMAUD.GIN_No INNER JOIN RMFORM ON RMFORM.Material_Form = PSRMBAT.Form WHERE (RTRIM(@ProjectCode) = '' OR EXISTS(SELECT * FROM @ProjectWO WHERE [@ProjectWO].Works_Order = PSRMAUD.Works_Order_Number)) AND (@WorksOrder= 0 OR PSRMAUD.Works_Order_Number = @WorksOrder) AND (@RejectNumber = 0 OR (PSRMAUD.Reject_Note = @RejectNumber AND PSRMAUD.Reject_Type = @RejectType)) AND (@DateFromInt = 0 OR PSRMAUD.Date_Booked >= @DateFromInt) AND (@DateToInt = 0 OR PSRMAUD.Date_Booked <= @DateToInt) GROUP BY PSRMAUD.Works_Order_Number, PSRMBAT.GIN_No HAVING SUM(PSRMAUD.Move_Qty * CASE WHEN Add_to_Stock = 1 THEN -1 ELSE 1 END) <> 0 -- --#Subcontract & Other Stock Type Purchases INSERT @Results SELECT POADVICE.Associated_Works_Ord, CASE PODETAIL.Stock_Type WHEN 'SC' THEN 'SC' WHEN UPPER(@JBCST_USER_TYPE) THEN 'US' ELSE 'OT' END, PODETAIL.Stock_Type, 0, POADVICE.Unique_Reference, 0, POADVICE.Qty_Delivered, 1, 0,0,0,0,0,0,0,0,0,0, 0,0, CASE PODETAIL.Stock_Type WHEN 'SC' THEN INT_PO_RECEIPT_TOTAL.Effective_Receipt_Value_Home ELSE 0 END, --SC CASE PODETAIL.Stock_Type WHEN UPPER(@JBCST_USER_TYPE) THEN INT_PO_RECEIPT_TOTAL.Effective_Receipt_Value_Home ELSE 0 END, --US CASE PODETAIL.Stock_Type WHEN 'SC' THEN 0 WHEN UPPER(@JBCST_USER_TYPE) THEN 0 ELSE INT_PO_RECEIPT_TOTAL.Effective_Receipt_Value_Home END, --OT 0, 0, 0, 0, ISNULL((SELECT STOCKTYP.Project_Cost_Centre FROM STOCKTYP WHERE STOCKTYP.Stock_Type = PODETAIL.Stock_Type), ''), '' FROM POADVICE INNER JOIN PODETAIL ON PODETAIL.PO_Number = POADVICE.Purchase_Order_No AND PODETAIL.Item = POADVICE.Order_Item_No AND PODETAIL.Stock_Type <> 'BI' AND PODETAIL.Stock_Type <> 'RM' INNER JOIN INT_PO_RECEIPT_TOTAL ON INT_PO_RECEIPT_TOTAL.Receipt_Unique = POADVICE.Unique_Reference LEFT OUTER JOIN PROGRESS ON PROGRESS.Works_Order_Number = PODETAIL.Allocated_WO_Num AND PROGRESS.Op_Number = PODETAIL.Sub_Con_Op_No WHERE (RTRIM(@ProjectCode) = '' OR EXISTS(SELECT * FROM @ProjectWO WHERE [@ProjectWO].Works_Order = POADVICE.Associated_Works_Ord)) AND (@WorksOrder = 0 OR POADVICE.Associated_Works_Ord = @WorksOrder) AND (@RejectNumber = 0 OR (PROGRESS.Rework_NCR = @RejectNumber AND PROGRESS.Associated_NCR_Type = @RejectType)) AND (@DateFromInt = 0 OR POADVICE.Date_Received >= @DateFromInt) AND (@DateToInt = 0 OR POADVICE.Date_Received <= @DateToInt) --#Subcontract & Other Stock Type Rejects INSERT @Results SELECT POADVICE.Associated_Works_Ord, CASE PODETAIL.Stock_Type WHEN 'SC' THEN 'SC' WHEN UPPER(@JBCST_USER_TYPE) THEN 'US' ELSE 'OT' END, PODETAIL.Stock_Type, 0, SUPRETRN.NCR_Number, 0, -SUPRETRN.Reject_Quantity, 1, 0,0,0,0,0,0,0,0,0,0, 0,0, CASE PODETAIL.Stock_Type WHEN 'SC' THEN CASE WHEN SUPRETRN.Invoice_Matched = 1 THEN -SUPRETRN.Reject_Value/CURRENCY.Exchange_Rate_Purch ELSE -INT_PO_RECEIPT_TOTAL.Effective_Receipt_Value_Home/POADVICE.Qty_Delivered*SUPRETRN.Reject_Quantity END ELSE 0 END, --SC CASE PODETAIL.Stock_Type WHEN UPPER(@JBCST_USER_TYPE) THEN CASE WHEN SUPRETRN.Invoice_Matched = 1 THEN -SUPRETRN.Reject_Value/CURRENCY.Exchange_Rate_Purch ELSE -INT_PO_RECEIPT_TOTAL.Effective_Receipt_Value_Home/POADVICE.Qty_Delivered*SUPRETRN.Reject_Quantity END ELSE 0 END, --US CASE PODETAIL.Stock_Type WHEN 'SC' THEN 0 WHEN UPPER(@JBCST_USER_TYPE) THEN 0 ELSE CASE WHEN SUPRETRN.Invoice_Matched = 1 THEN -SUPRETRN.Reject_Value/CURRENCY.Exchange_Rate_Purch ELSE -INT_PO_RECEIPT_TOTAL.Effective_Receipt_Value_Home/POADVICE.Qty_Delivered*SUPRETRN.Reject_Quantity END END, --OT 0, 0, 0, 0, ISNULL((SELECT STOCKTYP.Project_Cost_Centre FROM STOCKTYP WHERE STOCKTYP.Stock_Type = PODETAIL.Stock_Type), ''), '' FROM SUPRETRN INNER JOIN POADVICE ON POADVICE.Unique_Reference = SUPRETRN.Receipt_Unique INNER JOIN PODETAIL ON PODETAIL.PO_Number = SUPRETRN.Purchase_Order AND PODETAIL.Item = SUPRETRN.Purchase_Order_Item_ AND PODETAIL.Stock_Type <> 'BI' AND PODETAIL.Stock_Type <> 'RM' INNER JOIN INT_PO_RECEIPT_TOTAL ON INT_PO_RECEIPT_TOTAL.Receipt_Unique = POADVICE.Unique_Reference INNER JOIN SUPPLIER ON SUPPLIER.Supplier_Code = PODETAIL.Supplier_Code INNER JOIN CURRENCY ON CURRENCY.Currency_Code = SUPPLIER.Currency_Code LEFT OUTER JOIN PROGRESS ON PROGRESS.Works_Order_Number = PODETAIL.Allocated_WO_Num AND PROGRESS.Op_Number = PODETAIL.Sub_Con_Op_No WHERE (RTRIM(@ProjectCode) = '' OR EXISTS(SELECT * FROM @ProjectWO WHERE [@ProjectWO].Works_Order = POADVICE.Associated_Works_Ord)) AND (@WorksOrder = 0 OR POADVICE.Associated_Works_Ord = @WorksOrder) AND (@RejectNumber = 0 OR (PROGRESS.Rework_NCR = @RejectNumber AND PROGRESS.Associated_NCR_Type = @RejectType)) AND (@DateFromInt = 0 OR SUPRETRN.Reject_Date >= @DateFromInt) AND (@DateToInt = 0 OR SUPRETRN.Reject_Date <= @DateToInt) AND SUPRETRN.Reject_Complaint = 'R' AND SUPRETRN.Status <> 3 --Time Bookings INSERT @Results SELECT WOCLOCK.Works_Order, 'TB', '', 0, WOCLOCK.Operation, 0, SUM(WOCLOCK.Quantity_Produced), 1, SUM(WOCLOCK.Set_Time), SUM(WOCLOCK.Operation_Time), SUM(WOCLOCK.Set_Time_Employee), SUM(WOCLOCK.Operation_Time_Employee), SUM(CASE WHEN RESGROUP.Group_Type = 'L' THEN WOCLOCK.Set_Time_Employee ELSE WOCLOCK.Set_Time END), SUM(CASE WHEN RESGROUP.Group_Type = 'L' THEN WOCLOCK.Operation_Time_Employee ELSE WOCLOCK.Operation_Time END), SUM(WOCLOCK.Set_Time * Unit_Charge /3600), SUM(WOCLOCK.Operation_Time * Unit_Charge /3600), SUM(WOCLOCK.Set_Time_Employee * Unit_Charge_Employee /3600), SUM(WOCLOCK.Operation_Time_Employee * Unit_Charge_Employee_Op /3600), 0,0,0,0,0,0,0, SUM(CASE WHEN ChargeableNon_Charg = 'R' THEN CASE WHEN Group_Type = 'L' THEN WOCLOCK.Set_Time_Employee + WOCLOCK.Operation_Time_Employee ELSE WOCLOCK.Set_Time + WOCLOCK.Operation_Time END ELSE 0 END ), SUM(CASE WHEN ChargeableNon_Charg = 'R' THEN (WOCLOCK.Set_Time + WOCLOCK.Set_Time_Employee + WOCLOCK.Operation_Time + WOCLOCK.Operation_Time_Employee) * Unit_Charge_Employee_Op /3600 ELSE 0 END ), RESOURCE.Cost_Centre, RESOURCE.Resource_Code FROM WOCLOCK INNER JOIN RESOURCE ON RESOURCE.Resource_Code = WOCLOCK.Resource_Code_Actua INNER JOIN RESGROUP ON RESGROUP.Resource_Group = RESOURCE.Res_Group_Code WHERE (RTRIM(@ProjectCode) = '' OR EXISTS(SELECT * FROM @ProjectWO WHERE [@ProjectWO].Works_Order = WOCLOCK.Works_Order)) AND (@WorksOrder = 0 OR WOCLOCK.Works_Order = @WorksOrder) AND (@RejectNumber = 0 OR (WOCLOCK.Reject_Note = @RejectNumber AND CASE WOCLOCK.ChargeableNon_Charg WHEN 'R' THEN 'I' WHEN 'S' THEN 'S' ELSE '' END = @RejectType)) AND (@DateFromInt = 0 OR WOCLOCK.Shift_Date >= @DateFromInt) AND (@DateToInt = 0 OR WOCLOCK.Shift_Date <= @DateToInt) GROUP BY WOCLOCK.Works_Order, WOCLOCK.Operation, RESOURCE.Resource_Code, RESOURCE.Cost_Centre, RESGROUP.Group_Type --By Products INSERT @Results SELECT PSBATCH.Works_Order_Created_, 'BY', 'MA', 0, PSBATCH.Batch_Unique_No, 0, SUM(STKDETAIL.Stock_Qty * CASE WHEN Receipt_Or_Issue = 'R' THEN 1 ELSE -1 END), 1, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, SUM( (INT_STOCK_BATCH_COST.Hour_Cost_Unit + INT_STOCK_BATCH_COST.Material_Cost_Unit + INT_STOCK_BATCH_COST.Sub_Con_Cost_Unit + INT_STOCK_BATCH_COST.Other_Cost_Unit) * STKDETAIL.Stock_Qty * CASE WHEN Receipt_Or_Issue = 'R' THEN 1 ELSE -1 END ), 0,0,0, dbo.GetProjectCostCentre(PSBATCH.Part_Number, 'MA'), '' FROM STKDETAIL INNER JOIN PSBATCH ON PSBATCH.Batch_Unique_No = STKDETAIL.UniqBatchLocatioo INNER JOIN BY_PRODUCTS ON WO_No = PSBATCH.Works_Order_Created_ AND BP_Part_No = PSBATCH.Part_Number AND BY_PRODUCTS.Deduct_BP_Cost = 1 LEFT OUTER JOIN INT_STOCK_BATCH_COST ON INT_STOCK_BATCH_COST.Batch_Unique_No = STKDETAIL.UniqBatchLocatioo WHERE (RTRIM(@ProjectCode) = '' OR EXISTS(SELECT * FROM @ProjectWO WHERE [@ProjectWO].Works_Order = PSBATCH.Works_Order_Created_)) AND (@WorksOrder = 0 OR PSBATCH.Works_Order_Created_ = @WorksOrder) AND (@DateFromInt = 0 OR STKDETAIL.Date_Booked >= @DateFromInt) AND (@DateToInt = 0 OR STKDETAIL.Date_Booked <= @DateToInt) AND PSBATCH.Created_as_by_prod = 1 GROUP BY --STKDETAIL.Works_Order_Issued_To, PSBATCH.Part_Number, PSBATCH.Batch_Number, PSBATCH.Batch_Unique_No, PSBATCH.Drawing_Issue, PSBATCH.Supplier, PSBATCH.PO_Number, PSBATCH.PO_Item, PSBATCH.Advice_Note, PSBATCH.Works_Order_Created_, PSBATCH.Date_Batch_Was_Recd, STKDETAIL.Date_Booked, STKDETAIL.Time_Booked, STKDETAIL.User_Login --Manual Transaction & Split Batch Transactions INSERT @Results SELECT JOBCSTDT.WO_Number, JOBCSTDT.Transaction_Type, --CASE JOBCSTDT.Transaction_Type WHEN 'MA' THEN 'BI' ELSE JOBCSTDT.Transaction_Type END, '', 1, 0, 0, SUM(JOBCSTDT.Qty), 1, 0,0,0,0,0,0,0,0,0,0, SUM(CASE JOBCSTDT.Transaction_Type WHEN 'BI' THEN JOBCSTDT.CostSales_Value ELSE 0 END), SUM(CASE JOBCSTDT.Transaction_Type WHEN 'RM' THEN JOBCSTDT.CostSales_Value ELSE 0 END), SUM(CASE JOBCSTDT.Transaction_Type WHEN 'SC' THEN JOBCSTDT.CostSales_Value ELSE 0 END), SUM(CASE WHEN JOBCSTDT.Transaction_Type = 'US' THEN JOBCSTDT.CostSales_Value ELSE 0 END), SUM(CASE WHEN JOBCSTDT.Transaction_Type = 'OT' THEN JOBCSTDT.CostSales_Value ELSE 0 END), 0, SUM(CASE JOBCSTDT.Transaction_Type WHEN 'MA' THEN JOBCSTDT.CostSales_Value ELSE 0 END), 0,0, ISNULL((SELECT UPPER(CONTROL.CF_Alpha) FROM CONTROL INNER JOIN PROJECT_COST_CENTRES ON PROJECT_COST_CENTRES.Project_Cost_Centre = UPPER(CONTROL.CF_Alpha) WHERE CONTROL.CF_Key = 'JC_MANUAL_COST_CENTRE'), ''), '' FROM JOBCSTDT WHERE (RTRIM(@ProjectCode) = '' OR EXISTS(SELECT * FROM @ProjectWO WHERE [@ProjectWO].Works_Order = JOBCSTDT.WO_Number)) AND (@WorksOrder = 0 OR JOBCSTDT.WO_Number = @WorksOrder) AND (@DateFromInt = 0 OR JOBCSTDT.Date_Processed >= @DateFromInt) AND (@DateToInt = 0 OR JOBCSTDT.Date_Processed <= @DateToInt) AND (Manual = 1 OR Split_Transaction = 1) GROUP BY JOBCSTDT.WO_Number, JOBCSTDT.Transaction_Type --Sales Assembly BI Costs UNION ALL SELECT DELDET.Delivery_Costing_Works_Order, 'DE', CASE PSBATCH.Works_Order_Created_ WHEN 0 THEN 'BI' ELSE 'MA' END, 0, PSBATCH.Batch_Unique_No, 0, DELDET.Quantity_Delivered - DELDET.Quantity_Returned, 1, 0,0,0,0,0,0,0,0,0,0, ROUND((DELDET.Quantity_Delivered - DELDET.Quantity_Returned) * ISNULL(Hour_Cost_Unit + Material_Cost_Unit + Sub_Con_Cost_Unit + Other_Cost_Unit,0), 5), 0,0,0,0,0,0,0,0, dbo.GetProjectCostCentre(DELDET.Part_Number, CASE WHEN PSBATCH.Works_Order_Created_ = 0 THEN 'BI' ELSE 'MA' END), 'Delivery Costing - Delivery Note ''' + RTRIM(DELDET.Delivery_Note_No) + '''' FROM DELDET INNER JOIN PSBATCH ON PSBATCH.Batch_Unique_No = DELDET.Uniq_Batch INNER JOIN SOCALL ON SOCALL.Sales_Order_No = DELDET.Sales_Order_No AND SOCALL.Sales_Order_Item_No = DELDET.Sales_Order_Item_No AND SOCALL.Cust_Req_Date = DELDET.Call_Off_Date LEFT OUTER JOIN INT_STOCK_BATCH_COST ON INT_STOCK_BATCH_COST.Batch_Unique_No = DELDET.Uniq_Batch WHERE (RTRIM(@ProjectCode) = '' OR EXISTS(SELECT * FROM @ProjectWO WHERE [@ProjectWO].Works_Order = DELDET.Delivery_Costing_Works_Order)) AND (@WorksOrder = 0 OR DELDET.Delivery_Costing_Works_Order = @WorksOrder) AND (@DateFromInt = 0 OR DELDET.Delivery_Date >= @DateFromInt) AND (@DateToInt = 0 OR DELDET.Delivery_Date <= @DateToInt) AND DELDET.Delivery_Cancelled = 0 AND DELDET.Quantity_Delivered - DELDET.Quantity_Returned > 0 RETURN END