GL Transactions with AA Details

| 09/13/2010 | 6 Comments

Hi all

Today I decided to post a simple SQL code to fetch GL Transaction Details along with dimension codes from the Analytical Accounting module.

SELECT  A.aaGLHdrID,
        A.JRNENTRY,
        A.RCTRXSEQ,
        A.YEAR1,
        A.GLPOSTDT,
        B.INTERID,
        B.ACTINDX,
        F.ACTNUMST,
        B.CURNCYID,
        C.DEBITAMT,
        C.CRDTAMNT,
        C.ORDBTAMT,
        C.ORCRDAMT,
        E.aaTrxDimCode,
        E.aaTrxDimCodeDescr,
        'Open Year' AS YEARSTATUS,
        B.SEQNUMBR,
        ( SELECT TOP 1
                    F.SOURCDOC
          FROM      dbo.GL20000 F
          WHERE     F.JRNENTRY = A.JRNENTRY
                    AND F.RCTRXSEQ = A.RCTRXSEQ
        ) AS SOURCDOC,
        ( SELECT TOP 1
                    F.REFRENCE
          FROM      dbo.GL20000 F
          WHERE     F.JRNENTRY = A.JRNENTRY
                    AND F.RCTRXSEQ = A.RCTRXSEQ
        ) AS REFERENCE,
        ( SELECT TOP 1
                    F.USWHPSTD
          FROM      dbo.GL20000 F
          WHERE     F.JRNENTRY = A.JRNENTRY
                    AND F.RCTRXSEQ = A.RCTRXSEQ
        ) AS USERPOSTED,
        ( SELECT TOP 1
                    F.TRXSORCE
          FROM      dbo.GL20000 F
          WHERE     F.JRNENTRY = A.JRNENTRY
                    AND F.RCTRXSEQ = A.RCTRXSEQ
        ) AS AUDITTRAIL,
        ( SELECT TOP 1
                    F.DSCRIPTN
          FROM      dbo.GL20000 F
          WHERE     F.JRNENTRY = A.JRNENTRY
                    AND F.RCTRXSEQ = A.RCTRXSEQ
        ) AS TRXDESC
FROM    dbo.AAG30000 A
        INNER JOIN dbo.AAG30001 B ON A.aaGLHdrID = B.aaGLHdrID
        INNER JOIN dbo.AAG30002 C ON B.aaGLHdrID = C.aaGLHdrID
                                     AND B.aaGLDistID = C.aaGLDistID
        INNER JOIN dbo.AAG30003 D ON C.aaGLHdrID = D.aaGLHdrID
                                     AND C.aaGLDistID = D.aaGLDistID
                                     AND C.aaGLAssignID = D.aaGLAssignID
        INNER JOIN dbo.AAG00401 E ON D.aaTrxDimID = E.aaTrxDimID
                                     AND D.aaTrxCodeID = E.aaTrxDimCodeID
        INNER JOIN dbo.GL00105 F ON B.ACTINDX = F.ACTINDX
UNION ALL
SELECT  A.aaGLHdrID,
        A.JRNENTRY,
        A.RCTRXSEQ,
        A.YEAR1,
        A.GLPOSTDT,
        B.INTERID,
        B.ACTINDX,
        F.ACTNUMST,
        B.CURNCYID,
        C.DEBITAMT,
        C.CRDTAMNT,
        C.ORDBTAMT,
        C.ORCRDAMT,
        E.aaTrxDimCode,
        E.aaTrxDimCodeDescr,
        'Historical Year' AS YEARSTATUS,
        B.SEQNUMBR,
        ( SELECT TOP 1
                    F.SOURCDOC
          FROM      dbo.GL30000 F
          WHERE     F.JRNENTRY = A.JRNENTRY
                    AND F.RCTRXSEQ = A.RCTRXSEQ
        ) AS SOURCDOC,
        ( SELECT TOP 1
                    F.REFRENCE
          FROM      dbo.GL30000 F
          WHERE     F.JRNENTRY = A.JRNENTRY
                    AND F.RCTRXSEQ = A.RCTRXSEQ
        ) AS REFERENCE,
        ( SELECT TOP 1
                    F.USWHPSTD
          FROM      dbo.GL30000 F
          WHERE     F.JRNENTRY = A.JRNENTRY
                    AND F.RCTRXSEQ = A.RCTRXSEQ
        ) AS USERPOSTED,
        ( SELECT TOP 1
                    F.TRXSORCE
          FROM      dbo.GL30000 F
          WHERE     F.JRNENTRY = A.JRNENTRY
                    AND F.RCTRXSEQ = A.RCTRXSEQ
        ) AS AUDITTRAIL,
        ( SELECT TOP 1
                    F.DSCRIPTN
          FROM      dbo.GL30000 F
          WHERE     F.JRNENTRY = A.JRNENTRY
                    AND F.RCTRXSEQ = A.RCTRXSEQ
        ) AS TRXDESC
FROM    dbo.AAG40000 A
        INNER JOIN dbo.AAG40001 B ON A.aaGLHdrID = B.aaGLHdrID
        INNER JOIN dbo.AAG40002 C ON B.aaGLHdrID = C.aaGLHdrID
                                     AND B.aaGLDistID = C.aaGLDistID
        INNER JOIN dbo.AAG40003 D ON C.aaGLHdrID = D.aaGLHdrID
                                     AND C.aaGLDistID = D.aaGLDistID
                                     AND C.aaGLAssignID = D.aaGLAssignID
        INNER JOIN dbo.AAG00401 E ON D.aaTrxDimID = E.aaTrxDimID
                                     AND D.aaTrxCodeID = E.aaTrxDimCodeID
        INNER JOIN dbo.GL00105 F ON B.ACTINDX = F.ACTINDX

The reason why I have used sub queries to fetch data from the GL tables instead of using joins is because of the following pitfall in the AA module.

Its not easy to link the GL and AA tables together using the journal entry number and line item sequence, since the line item sequence in AA tables does not match with the GL tables in the following instances.

  • When a back-out of a journal entry happens in GL.
  • During the year end process, all lines populated in the BBF entry in the AA tables have a line item sequence of 0, which does not tie back with the line item sequence in the GL tables.

I realized that the first issue has been resolved in GP v10  SP5, but not sure of the second issue. So I have decided to stick to this query till that issue has been resolved, since this is more fool proof. :) Hope this helps you all…

Until next post…

VN:F [1.9.22_1171]
Rating: 10.0/10 (5 votes cast)
VN:F [1.9.22_1171]
Rating: +5 (from 5 votes)
GL Transactions with AA Details, 10.0 out of 10 based on 5 ratings

Tags: , , , ,

Category: Analytical Accounting, Dynamics, General Ledger, Great Plains, SQL Server 2005, SQL Server 2008

About the Author ()

  • http://www.facebook.com/tboyce1 Tonya Spivey Hazard Boyce

    Thank you! Thank you! Thank you! You are a life saver! I am so grateful that you posted this script. I spent several hours trying to figure out how to link the AA tables to the GL tables and continued to receive duplicate rows until I found this link. This script was the solution for my issue. I did not think about using the subqueries in the select section of the query. Thanks again!

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  • Angela

    Thank you very much for this query. It comes very close to what I am being required to report on. I have been trying for several hours to find the link between the GL/AA tables and the PM tables to get the Vendor # and PO # for these GL Transactions. Would you happen to know what the tables/links would be to get this information?

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  • Santosh

    Hi, If we can get period information from Fiscal period table then will be very good, insteat showiing calculated period from GL20000 using TRXDATE. I have seen make users / client refer period from fiscal period table insted of calculating from GL table.

    If you make this hanges in GL Transaction summary and this AA view it will be great help. Thanks in advance.

    Regards,

    Santosh

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  • http://www.whidbey.com Stephen Jantz

    I wasn't sure how to send a general question to you so sorry that this doesn't pertain directly to this post, besides general ledger in common. You seem to have a great knowledge of GP and SQL!!

    Would you happen to have a SQL statement that would give me the details by employee and job for SUTA, FUTA, Workers Comp? I like to have exact details to balance and with GP only having a allocation percentage for overheads does not work for me I want to balance back 100% to the GL for audit purposes.

    Another problem I have that would be wonderful is the ability to link the PO line item details to the GL transaction. It would be especially helpful to have the line item detail from the PO for $xxx.xx instead of just a reference to the PO. This causes many screen clicks to drill down to this data and have the ability in SQL to link and see these details would be awesome!

    Thank you

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    • veeyeskay

      Hi Stephen

      Unfortunatey, I don't have a script for SUTA, FUTA, Workers Comp.

      Regarding the query to link PO Line Item details with GL transaction, its a complex link, since at the GL line item level, you only can get the receiving transaction document number. From that we need to identify the PO umber and the PO line, which might be a little complex… Will try it and let u know later… :)

      VN:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
      VN:F [1.9.22_1171]
      Rating: 0 (from 0 votes)
  • Pingback: DynamicAccounting.net()