The following reports retrieve data from the manufacturing order closing file, it illustrates the following details for every single MO per item number:

  • Material Cost
  • Material Fixed Overhead
  • Material Variable Overhead
  • Labor Cost
  • Labor Fixed Overhead
  • Labor Variable Overhead
  • Machine
  • Machine Fixed Overhead
  • Machine Variable Overhead
  • Total MO cost

 

SELECT ITEMNMBR,

DATEPART(yy, COMPLETECLOSEDATE) AS RecYear,

DATEPART(mm, COMPLETECLOSEDATE) AS RecMonth,

SUM(ENDQTY_I) AS RecQTY,

BASEUOFM,

SUM(ITEM_COSTS_ARRAY_I_1) AS [Material_Costs],

SUM(ITEM_COSTS_ARRAY_I_2) AS [Material_Fixed_Overhead],

SUM(ITEM_COSTS_ARRAY_I_3) AS [Material_Variable_Overhead],

SUM(ITEM_COSTS_ARRAY_I_4) AS [Labor],

SUM(ITEM_COSTS_ARRAY_I_5) AS [Labor_Fixed_Overhead],

SUM(ITEM_COSTS_ARRAY_I_6) AS [Labor_Variable_Overhead],

SUM(ITEM_COSTS_ARRAY_I_7) AS [Machine],

SUM(ITEM_COSTS_ARRAY_I_8) AS [Machine_Fixed_Overhead],

SUM(ITEM_COSTS_ARRAY_I_9) AS [Machine_Variable_Overhead],

SUM(ITEM_COSTS_ARRAY_I_10) AS [Total_Cost]
FROM
(

SELECT A.*,

B.ITEMNMBR,

B.COMPLETECLOSEDATE,

B.ENDQTY_I,

D.[BASEUOFM]

FROM WO010701 AS A

INNER JOIN WO010032 AS B
            ON
A.MANUFACTUREORDER_I = B.MANUFACTUREORDER_I

INNER JOIN IV00101 AS C
            ON
B.[ITEMNMBR] = C.[ITEMNMBR]

INNER JOIN IV40201 AS D
            ON
C.[UOMSCHDL] = D.[UOMSCHDL]

WHERE MANUFACTUREORDERST_I = 8
) AS E
GROUP BY ITEMNMBR,

DATEPART(yy, COMPLETECLOSEDATE),

DATEPART(mm, COMPLETECLOSEDATE),
         BASEUOFM
Best Regards, 
Mahmoud M. AlSaadi