Featured Post

Dynamics GP 2018 is now Released

It is officially published that Microsoft Dynamics GP 2018 is available, the download link is provided below: Product download page ...

Saturday, May 20, 2017

Manufacturing Order - Finished Product Details Report



The following report provides the details of the finished product manufactured in Dynamics GP using the MO Entry, it provides the following details:
  • Manufacturing Order No.
  • Item Number
  • Item Description
  • Item Class
  • Item Class Description
  • Routing Name
  • MO Starting Quantity
  • MO Ending Quantity
  • Document Date
  • Inventory Document Number
  • Unit of Measurement
  • Extended Cost
  • GL Journal Entry Number



CREATE VIEW [dbo].[GPEssentials_vW_MOFinishedProduct]
AS
SELECT  L.MANUFACTUREORDER_I ,
        L.ITEMNMBR ,
        L.ItemDescription ,
        L.ItemClassCode ,
        L.ItemClassDescription ,
        L.ROUTINGNAME_I AS RoutingName ,
        L.ENDQTY_I AS EndingQuantity ,
        L.STARTQTY_I AS StartQuantity ,
        D.DOCDATE AS DocumentDate ,
        D.DOCNUMBR AS IV_DocumentNumber ,
        D.LOCNCODE AS LocationCode ,
        D.UOFM AS UnitOFMeasurement ,
        D.TRXQTYInBase AS IV_QuantityInBase ,
        D.EXTDCOST AS ExtendedCost ,
        D.JRNENTRY AS GL_JournalEntry
FROM    ( SELECT    MANUFACTUREORDER_I ,
                    A.ITEMNMBR ,
                    RTRIM(LTRIM(B.ITEMDESC)) AS ItemDescription ,
                    RTRIM(LTRIM(B.ITMCLSCD)) AS ItemClassCode ,
                    RTRIM(LTRIM(C.ITMCLSDC)) AS ItemClassDescription ,
                    ROUTINGNAME_I ,
                    ENDQTY_I ,
                    STARTQTY_I
            FROM      [WO010032] AS A
                    LEFT OUTER JOIN IV00101 AS B ON A.ITEMNMBR = B.ITEMNMBR
                    LEFT OUTER JOIN IV40400 AS C ON B.ITMCLSCD = C.ITMCLSCD
        ) AS L
        LEFT OUTER JOIN ( SELECT    ITEMNMBR ,
                                    DOCDATE ,
                                    DOCNUMBR ,
                                    LOCNCODE ,
                                    UOFM ,
                                    SUM(TRXQTYInBase) AS TRXQTYInBase ,
                                    SUM(EXTDCOST) AS EXTDCOST ,
                                    SRCRFRNCNMBR ,
                                    JRNENTRY
                            FROM      [SEE30303]
                            GROUP BY  ITEMNMBR ,
                                    DOCDATE ,
                                    DOCNUMBR ,
                                    LOCNCODE ,
                                    UOFM ,
                                    SRCRFRNCNMBR ,
                                    JRNENTRY
                        ) AS D ON L.MANUFACTUREORDER_I = D.SRCRFRNCNMBR
                                    AND L.ITEMNMBR = D.ITEMNMBR;

GO


Best Regards, 
Mahmoud M. AlSaadi

No comments:

Post a Comment