«
»

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

Tags: ,

This entry was posted on Saturday, June 9th, 2018 at 1:24 pm and is filed under Dynamics GP. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.