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