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...

Friday, May 16, 2014

Purchasing Transactions (POP) with General Ledger Journals Details

 

In this post, an SQL script is provided to link purchasing transactions from the Purchase Order Processing Module with associated journal entries from the General Ledger module.

1

 

Tables Included:

·         POP30300 | Purchasing Receipt History

·         POP10500 | Purchasing Receipt Line Quantities

·         GL20000  | Year to Date Transaction Open

·         GL30000  | Account Transaction History

 

 

SELECT  *

FROM    ( SELECT    B.PONUMBER ,

                    B.VENDORID ,

                    B.POPRCTNM ,

                    A.receiptdate ,

                    A.TRXSORCE ,

                    CASE B.POPTYPE

                      WHEN 1 THEN 'Shipment'

                      WHEN 2 THEN 'Invoice'

                      WHEN 3 THEN 'Shipment-Invoice'

                      WHEN 4 THEN 'Return'

                      WHEN 5 THEN 'Return with Credit'

                      WHEN 6 THEN 'Inventory Return'

                      WHEN 7 THEN 'Inventory Return with Credit'

                      WHEN 8 THEN 'InTransit'

                      ELSE ''

                    END AS POPTYPE ,

                    B.QTYSHPPD ,

                    B.QTYINVCD ,

                    B.ITEMNMBR ,

                    B.TRXLOCTN ,

                    B.UOFM ,

                    B.DATERECD ,

                    B.INVINDX ,

                    B.ACPURIDX ,

                    B.OREXTCST

          FROM      dbo.POP30300 AS A

                    LEFT OUTER JOIN dbo.POP10500 AS B

                                  ON A.POPRCTNM = B.POPRCTNM

                                  AND A.VENDORID = B.VENDORID

        ) AS POP

        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 = 4

                                      UNION ALL

                                      SELECT    SERIES ,

                                                JRNENTRY ,

                                                DEBITAMT ,

                                                CRDTAMNT ,

                                                ORGNTSRC ,

                                                ORCTRNUM ,

                                                ORDOCNUM ,

                                                ORMSTRID

                                      FROM      dbo.GL30000

                                      WHERE     SERIES = 4

                                    ) AS GL

                          GROUP BY  SERIES ,

                                    JRNENTRY ,

                                    ORGNTSRC ,

                                    ORCTRNUM ,

                                    ORDOCNUM ,

                                    ORMSTRID

                        ) AS GL ON ( GL.ORGNTSRC = POP.TRXSORCE

                                     AND GL.ORDOCNUM = POP.POPRCTNM

                                     AND GL.ORCTRNUM = POP.POPRCTNM

                                     AND GL.ORMSTRID = POP.VENDORID

                                   )

                                                        

 

 

Best Regards,
Mahmoud M. AlSaadi

 

No comments:

Post a Comment