Manufacturing Order Cost Distribution

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

            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