Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Friday, March 21, 2014

General Ledger Journals with Analytical Accounting Details –SQL Script

 

In this post, SQL script for the GL Journal entries with Analytical Accounting details is provided along with all the associated details, as shown below;

Untitled

 

Tables Included:

·         AAG30000 | Analytical Accounting GL Header

·         AAG30001 | Analytical Accounting GL Distribution

·         AAG30002 | Analytical Accounting GL Assignment

·         AAG30003 | Analytical Accounting GL Code

·         AAG00401 | Analytical Accounting Transaction Dimension

·         GL00100  | Account Master

·         GL00105  | Account Index Master

 

SELECT  YEAR1 ,

        JRNENTRY ,

        B.ACTINDX ,

        H.ACTNUMST ,

        G.ACTDESCR ,

        A.aaTRXType ,

        aaGLTRXSource ,

        aaTRXSource ,

        GLPOSTDT ,

        B.DEBITAMT AS TotalDebit ,

        B.CRDTAMNT AS TotalCredit ,

        C.DEBITAMT AS aaDebit ,

        C.CRDTAMNT AS aaCredit ,

        aaTrxDim ,

        aaTrxDimDescr ,

        aaTrxDimDescr2 ,

        aaTrxDimCode ,

        aaTrxDimCodeDescr ,

        aaTrxDimCodeDescr2

FROM    [AAG30000] AS A

        LEFT OUTER JOIN [AAG30001] AS B ON A.[aaGLHdrID] = B.[aaGLHdrID]

        LEFT OUTER JOIN [AAG30002] AS C ON B.[aaGLHdrID] = C.[aaGLHdrID]

                                           AND B.[aaGLDistID] = C.[aaGLDistID]

        INNER JOIN [AAG30003] AS D ON C.[aaGLAssignID] = D.[aaGLAssignID]

                                      AND C.[aaGLDistID] = D.[aaGLDistID]

                                      AND C.[aaGLHdrID] = D.[aaGLHdrID]

        INNER JOIN [AAG00401] AS E ON D.[aaTrxDimID] = E.[aaTrxDimID]

                                      AND D.[aaTrxCodeID] = E.[aaTrxDimCodeID]

        INNER JOIN [AAG00400] AS F ON E.[aaTrxDimID] = F.[aaTrxDimID]

        LEFT OUTER JOIN dbo.GL00100 AS G ON B.ACTINDX = G.ACTINDX

        LEFT OUTER JOIN dbo.GL00105 AS H ON G.ACTINDX = H.ACTINDX

 

 

Best Regards,
Mahmoud M. AlSaadi

13 comments:

  1. Hi, your query is awesome. Anyone that can put together this many JOINS and have things work is amazing to me.

    I would love to show the DSCRIPTN field from the GL30000 table, but I don’t know how to JOIN this table to your query. Would you be so kind as to suggest a solution that would work for me?

    Thanks for your time.

    ReplyDelete
    Replies
    1. Glad to help,
      Do you know that such join may result with duplication ? Because as you see above I am not retrieving data from the GL20000 or GL30000.
      The field you are looking for resides there and such join may bring up duplicated records.

      Are you okay with that ?

      Delete
    2. Ty for your reply, sir. My apologies if this is a rather amateur inquiry, but how can you tell that making such a join would result in duplication? (Which is exactly what I get btw when I attempt to create the join.)

      I would certainly like to avoid this duplication if at all possible. Any suggestions you can provide would be much appreciated.

      Many thanks, in advance.

      Delete
    3. Radgi867,
      You can tell if it results in duplication by trying to join and then see what happens :)
      Please note the following:
      AAG30002 should match 1:1 with GL20000,
      AAG30003 includes *only* the transactions that had AA codes on them. You can't really create one-size-fits-all to this question, because it depends on the structure of your AA codes (for example: a company that would put AA codes only per project would require a different SQL than a company that would put AA code on both Location and Department).
      So if you really need it either brush up you SQL skills or pay someone to do it.
      Regards,
      Hila.
      PS Mahmood, thanks a lot!

      Delete
  2. Mahmoud,
    Apologies if this posts twice, but I believe my first attempt failed. I was curious if you could build in prior year's AA data into this view as well. I only see current year activity in this view's results. I don't see any 'where' limitations in the statement, so I'm assuming the historical data is housed in different tables. Do you think it's feasible to merge both data sets in one view?

    Thanks!

    -Matt

    ReplyDelete
  3. Hi,

    The custom samrlist works good, but the search button doesnt work, I get a error. The same issue ocurres with whatever custom smarlist. I have GP2013 Sp2

    Thanks,

    Mary

    ReplyDelete
  4. You're a life-saver, again, Mahmoud! Thanks for this post!
    Regards,
    Lyn

    ReplyDelete
  5. How can i veiw unbalanced JVs by GP smartlist option

    ReplyDelete
  6. We found many unbalanced journal vouchers in few batches, we came a cross these when we are going to post those batches, so is there any way to view such JVs by GP smart list option so that we can correct them before posting. Hope it is clear now.

    ReplyDelete
  7. I am trying to attach AA codes to GL transactions as the above query does but in my situation I also need all GL transactions where I do not have AA codes. I attempted to create a join with GL20000 but I am running into situations where I have incorrect amounts. Is there a way where I can connect AA codes to GL20000 where applicable to gain a holistic transactional view of data? Thank you,
    Jason

    ReplyDelete
  8. Dear.
    i need sql script to insert financial transaction entry
    if you can help .

    ReplyDelete