SQL view for all GL transactions in Dynamics GP


I have been asked a few times now to add unposted General Ledger transactions to my Posted GL Transactions view. Here it is. This view also excludes voided transactions and introduces new columns for transaction status and source doc.

To see more information about GL tables in Dynamics GP, visit my GL Tables page. For more Dynamics GP SQL code, take a look at my GP Reports page. 


CREATE VIEW view_GL_Trx
AS

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_GL_Trx
-- Created Apr 27 2011 by Victoria Yudin - Flexible Solutions Inc
-- For updates see https://victoriayudin.com/gp-reports/
-- Returns all lines for all GL transactions, including unposted
-- Excludes year-end closing entries
-- Excludes voided transactions
-- Returns Functional amounts only
-- Tables used:
-- GL10000 - Work Trx header
-- GL10001 - Work Trx detail
-- GL20000 - Open Year Trx
-- GL30000 - Historical Trx
-- GL00100 - Account Master
-- GL00105 - Account Index Master
-- Updated Nov 15, 2012 to add last user and user who posted
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

SELECT
 Trx_Status,
 TRXDATE Trx_Date,
 JRNENTRY Journal_Entry,
 ACTNUMST Account_Number,
 ACTDESCR Account_Description,
 DEBITAMT Debit_Amount,
 CRDTAMNT Credit_Amount,
 REFRENCE Reference,
 SOURCDOC Source_Document,
 ORTRXSRC Originating_TRX_Source,
 ORMSTRID Originating_Master_ID,
 ORMSTRNM Originating_Master_Name,
 ORDOCNUM Originating_Doc_Number,
 CURNCYID Currency_ID,
 LASTUSER Last_User,
 USWHPSTD User_Who_Posted

FROM
(SELECT ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE,
 ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT, CURNCYID,
 Trx_Status = 'Open', LASTUSER, USWHPSTD
 FROM GL20000
 WHERE SOURCDOC not in ('BBF','P/L')
 AND VOIDED = 0

UNION ALL

 SELECT ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE,
 ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT, CURNCYID,
 Trx_Status = 'History', LASTUSER, USWHPSTD
 FROM GL30000
 WHERE SOURCDOC not in ('BBF','P/L')
 AND VOIDED = 0

UNION ALL

 SELECT GD.ACTINDX, GH.TRXDATE, GH.SOURCDOC, GH.JRNENTRY,
 GH.ORTRXSRC, GH.REFRENCE, GD.ORDOCNUM, GD.ORMSTRID, GD.ORMSTRNM,
 GD.DEBITAMT, GD.CRDTAMNT, GH.CURNCYID, Trx_Status = 'Work',
 LASTUSER, USWHPSTD
 FROM GL10000 GH
 INNER JOIN GL10001 GD
 ON GH.JRNENTRY = GD.JRNENTRY
 WHERE VOIDED = 0) GL

INNER JOIN GL00105 GM
 ON GL.ACTINDX = GM.ACTINDX

INNER JOIN GL00100 GA
 ON GL.ACTINDX = GA.ACTINDX

-- the following will grant permissions to this view to DYNGRP,
--leave this section off if you do not want to grant permissions
GO
GRANT SELECT ON view_GL_Trx TO DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

34 Responses to “SQL view for all GL transactions in Dynamics GP”

  1. Hi Victoria,

    I’ve been trying to do this myself for awhile, for this query is it possible to add a column that adds in the trxdscrn column from one of the AR tables? I cannot figure out how to tie the AR table back to get the information. I’m trying to accomplish this so I can have more information than just invoice entry under reference.

    I appreciate any help!

    Like

    • Hi Chris,

      Which AR table?

      -Victoria

      Like

      • I was trying to use the RM20101 table (the one you use in the AR all transactions query) as I knew that it had the information I was looking for.

        Like

        • A little clarification, I can inner join on GL30000 against RM20101 and RM30101 but, I’ve thus far been unable to do this with GL20000, I have been trying to combine them on the DEX_ROW_ID but, I searched the entire DB for a string in GL20000 and the only results are in GL20000 and ME240457 (no idea what this ME table is). I apologize if that is not clear enough, I’m an Accountant, not a programmer.

          Like

          • After lots of toying, I can make it a far simpler request. Is it possible to combine against SOP30300 or SOP30200 for the ITEMDESC column only? This way “Sales Entry” inside of the reference of the GL20000 and GL30000 has more information? This would make it so digging wouldn’t be necessary. I cannot seem to get it to work when I try to add onto the third part of the query that starts with the select for GL10000.

            Like

            • Hi Chris,

              The GL is going to be at the summary level and so there is no way to link it to the item level in SOP transactions without duplicating data. I would again suggest that you work with your GP partner or a consultant that can help you with this. It seems you’re looking to make a process easier, but I am worried that the code you come up with on your own is going to show you incorrect data.

              -Victoria

              Like

          • Hi Chris,

            You cannot use the DEX_ROW_ID for any linking like this. That’s an identity column in every GP table, meaning it simply uses the next available number on any new record and it’s also subject to change during upgrades or updates. There is no link between the DEX_ROW_ID in GL and RM tables, anything you might see is purely coincidental.

            You may want to work with your GP partner or a consultant that can help you with this. It may be too much to do in blog comments if you’re not familiar with the GP table structure and SQL coding.

            -Victoria

            Like

  2. Hi Victoria,

    Thank you so much for this great query! Is there anyway to connect this query to the DistRef and PORDNMBR columns on the payables tables? Essentially, I would like to be able to have a report that ties to the GL account / trial balance totals like this report does, but that also shows this payables details when this information exists.

    Thanks so much!

    Alex

    Like

    • Hi Alex,

      If you are posting to the GL in detail, there should be no need to pull the DistRef from other tables. It will be populated into the DSCRIPTN field in the GL tables. For the Purchase Order number, it would depend if you are entering your transactions directly into Payables or into the POP module. But it should be doable, just totally different for those scenarios. If you’ve started coding something and you’re stuck, post your code here and I can take a look for you.

      -Victoria

      Like

      • Thanks for reaching out and sorry for that angry looking avatar they gave me!

        I was delighted to see that the DSCRIPTN field worked just as you said, so that case is closed!

        With regard to POs, I don’t have any code written yet, but our transactions are written directly into payables in the transaction entry window, not purchasing entry.

        Thanks again for your help!

        Like

        • Hi Alex,

          LOL on the avatar – it’s all good. 🙂

          If you’re entering directly into payables, you can link to the PM20000 and PM30200 tables on the following:

          • ORDOCNUM in the GL tables = DOCNUMBR in the PM tables
          • ORTRXTYP in the GL tables = DOCTYPE in the PM tables
          • ORMSTRID in the GL tables = VENDORID in the PM tables

          Hope that helps,
          -Victoria

          Like

  3. is it possible to add the account segments into this report? I need to create refreshable reports but the end-user only wants certain accounts on each report.

    Like

  4. Victoria,

    How would you link a transaction back to a customer? Reason I ask is I am trying to make a report for Inter-company transactions so that we can do matching between our divisions.

    Thanks
    Chad

    Like

  5. Is there a way to run this for multi company based on a selection of DB names from (Select INTERID from dynamics.dbo.sy01500) so any added companies in the future would be dynamically added? Or is adding the DB to the table name on the select and doing unions manually for each company DB the only way?

    Like

  6. Hi Victoria,

    Great Blog as always!

    I am trying to update the Description of our GL accounts and wondered if you could advise on the best way to import the new data into the GL00100 table.
    We have a lot of accounts and I thought it would be easiest to do it in excel and then import in but I seem to not be able to do this?

    – Phil

    Like

  7. Hi victoria, is it possible to use this sql view to show gl transactions but include Void transactions? How would I modify it for that? any suggestions greatly appreciated. -Phil

    Like

    • Phil,

      There are 3 places in the code where you will find

      AND VOIDED = 0
      

      Just remove those and you will be including voided transactions.

      -Victoria

      Like

      • Thanks Victoria.

        I have done that but for some reason I now get the error Incorrect syntax near ‘ACTINDX’.despite not having changed any line of code other than commenting out the Voided = o by using double hyphens?

        Am I wrong to do it this way? – Phil

        Like

        • Phil,

          The 3rd of these has a ) that need to stay there. Putting — in front of the line comments out everything after it. You can put the ) by itself on the next line if you want, but it is needed for the rest of the code to work. If it’s still not working, please paste all of your code in here and I will take a look to see if I can help figure out the issue.

          -Victoria

          Like

  8. Is there a way to add the user who entered the transaction as well as user who posted? We typically only have 1 manager from each department post, but we have dozens of users who enter.

    Thank you,

    Like

    • Hi Jeff,

      I have added ‘Last User’ and ‘User Who Posted’. Last User is not necessarily the user who entered the transaction if someone else edited it later, but it’s the only thing that GP stores.

      -Victoria

      Like

  9. which sop table relates to gl.

    Like

  10. gp admin to canada Reply May 11, 2012 at 2:26 pm

    Hi Victoria

    When would GP use a value of P/L for the source document? I’ve looked at the GP manuals and googled but haven’t found anything. Thanks,

    Like

    • P/L and BBF will show as the Source Documents for year-end close transactions. These are typically excluded from transaction lists, as you can see I am going in this view, because they are ‘special’ entries, not real transactions.

      -Victoria

      Like

  11. Ron, can you verify if the table that was referenced when joining the “Notes table” was SY00700?

    Like

  12. Thanks Victoria! I also joined the Notes table so that I could get the notes ‘TXTFIELD’ to this. Just a thought.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: