Dynamics GP – Manufacturing Order Cost Distribution

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
Comments are closed.