Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

Tuesday, May 13, 2014

Sales Invoices and Returns with General Ledger Journals Details

 

In this post, an SQL script to link sales invoices and returns from the Sales Order Processing Module with associated journal entries from the General Ledger module.

1

 

Tables Included:

 

·         SOP30200 | Sales Transactions History

·         SOP30300 | Sales Transactions Amounts History

·         GL00100  | Account Master

·         GL00105  | Account Index Master

·         GL20000  | Year to Date Transactions Open

·         GL30000  | Account Transaction History

 

 

SELECT  *

FROM    ( SELECT    CASE A.SOPTYPE

                      WHEN 1 THEN 'Quote'

                      WHEN 2 THEN 'Order'

                      WHEN 3 THEN 'Invoice'

                      WHEN 4 THEN 'Return'

                      WHEN 5 THEN 'Back Order'

                      WHEN 6 THEN 'Fulfillment Order'

                      ELSE ''

                    END AS SOPTYPE ,

                    A.SOPNUMBE ,

                    A.CUSTNMBR ,

                    A.TRXSORCE ,

                    A.CUSTNAME ,

                    A.DOCDATE ,

                    A.GLPOSTDT ,

                    B.ITEMNMBR ,

                    B.ITEMDESC ,

                    B.QUANTITY ,

                    B.NONINVEN ,

                    B.UOFM ,

                    B.LOCNCODE ,

                    B.INVINDX ,

                    B.UNITCOST ,

                    B.UNITPRCE ,

                    B.XTNDPRCE ,

                    B.EXTDCOST ,

                    GLMS.ACTNUMST AS InventoryAccount ,

                    GLMS.ACTDESCR AS InventoryAccountDescription

          FROM      SOP30200 AS A

                    LEFT OUTER JOIN dbo.SOP30300 AS B

                    ON A.SOPNUMBE = B.SOPNUMBE

                    LEFT OUTER JOIN ( SELECT    X.ACTINDX ,

                                                X.ACTDESCR ,

                                                Y.ACTNUMST

                                      FROM      dbo.GL00100 AS X

                                                LEFT OUTER JOIN

                                                dbo.GL00105 AS Y

                                                ON X.ACTINDX = Y.ACTINDX

                                    ) AS GLMS ON GLMS.ACTINDX = B.INVINDX

          WHERE     A.SOPTYPE IN ( 3, 4 )

        ) AS SLS

        LEFT OUTER JOIN ( SELECT    SERIES ,

                                    JRNENTRY ,

                                    SUM(DEBITAMT) AS DEBIT ,

                                    SUM(CRDTAMNT) AS CREDIT ,

                                    ORGNTSRC ,

                                    ORCTRNUM ,

                                    ORDOCNUM ,

                                    ORMSTRID

                          FROM      ( SELECT    SERIES ,

                                                JRNENTRY ,

                                                DEBITAMT ,

                                                CRDTAMNT ,

                                                ORGNTSRC ,

                                                ORCTRNUM ,

                                                ORDOCNUM ,

                                                ORMSTRID

                                      FROM      dbo.GL20000

                                      WHERE     SERIES = 3

                                      UNION ALL

                                      SELECT    SERIES ,

                                                JRNENTRY ,

                                                DEBITAMT ,

                                                CRDTAMNT ,

                                                ORGNTSRC ,

                                                ORCTRNUM ,

                                                ORDOCNUM ,

                                                ORMSTRID

                                      FROM      dbo.GL30000

                                      WHERE     SERIES = 3

                                    ) AS GL

                          GROUP BY  SERIES ,

                                    JRNENTRY ,

                                    ORGNTSRC ,

                                    ORCTRNUM ,

                                    ORDOCNUM ,

                                    ORMSTRID

                        ) AS GL ON SLS.TRXSORCE = GL.ORGNTSRC

                                   AND SLS.SOPNUMBE = GL.ORDOCNUM

                                   AND SLS.SOPNUMBE = GL.ORCTRNUM

                                   AND SLS.CUSTNMBR = GL.ORMSTRID

 

 

Best Regards,
Mahmoud M. AlSaadi

No comments:

Post a Comment