The following reports retrieves 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
            ONA.MANUFACTUREORDER_I = B.MANUFACTUREORDER_I
        INNER JOIN IV00101 AS C
            ONB.[ITEMNMBR] = C.[ITEMNMBR]
        INNER JOIN IV40201 AS D
            ONC.[UOMSCHDL] = D.[UOMSCHDL]
    WHERE MANUFACTUREORDERST_I = 8
) AS E
GROUP BY ITEMNMBR,
         DATEPART(yy, COMPLETECLOSEDATE),
         DATEPART(mm, COMPLETECLOSEDATE),
         BASEUOFM




Best Regards, 
Mahmoud M. AlSaadi