April 27, 2011 
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
-- Updated Jun 14, 2018 to add batch and series
-- Updated Oct 23, 2018 to add description
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
select
GL.Trx_Status,
GL.TRXDATE Trx_Date,
GL.JRNENTRY Journal_Entry,
GM.ACTNUMST Account_Number,
GA.ACTDESCR Account_Description,
GL.DEBITAMT Debit_Amount,
GL.CRDTAMNT Credit_Amount,
GL.DSCRIPTN [Description],
GL.REFRENCE Reference,
GL.SOURCDOC Source_Document,
GL.ORTRXSRC Originating_TRX_Source,
GL.ORMSTRID Originating_Master_ID,
GL.ORMSTRNM Originating_Master_Name,
GL.ORDOCNUM Originating_Doc_Number,
GL.CURNCYID Currency_ID,
GL.LASTUSER Last_User,
GL.USWHPSTD User_Who_Posted,
GL.ORGNTSRC Batch_Number,
case GL.SERIES
when 1 then 'All'
when 2 then 'Financial'
when 3 then 'Sales'
when 4 then 'Purchasing'
when 5 then 'Inventory'
when 6 then 'Payroll – USA'
when 7 then 'Project'
when 10 then '3rd Party'
else 'Other'
end Series
from
(select ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE,
ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT, CURNCYID,
Trx_Status = 'Open', LASTUSER, USWHPSTD, ORGNTSRC, SERIES,
DSCRIPTN
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, ORGNTSRC, SERIES,
DSCRIPTN
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',
GH.LASTUSER, GH.USWHPSTD, GH.BACHNUMB, GH.SERIES, GD.DSCRIPTN
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.
Victoria Yudin 
Victoria,
I used your query, SQL view for all GL transactions in Dynamics GP, to create a live income statement. It worked beautifully with the addition of a few columns I can even change periods at will.
That said, I tried to join this query to add all of my various GP companies (I have 8) into a combined financial. Now the intercompany transactions are getting in the way and causing the report to vary from what was actually reported..
Is there a better table to pull transaction data from to be able to run a P&L statement and have it linked live into the data for multiple companies?
Thanks,
Dan
LikeLike
Hi Dan,
This should be possible to do, but because it’s so specific to your company and GP setup, it should really be done with your data specifically, not something generic.
If this is something you need help with, perhaps your GP partner can help? Or I can provide consulting help with if needed.
-Victoria
LikeLike
Hello Victoria, I generally use the out of the box Account transactions smartlist to search for specific account data, but do you have a query that would allow me to pull specific non-consecutive GL account transactions into one smartlist?
LikeLike
Hi Kristel,
Is it always the same accounts? If so, you could add that into the WHERE clause of this view and create a SmartList from it. If not, then what do you want the logic to be?
-Victoria
LikeLike
Hi Victoria-
How do I add the BACHNUM and SERIES field into this great query?
LikeLike
Hi Victoria,
First of all – wanted to say THANK YOU SO MUCH for this site…it has been a huge help for me. I have a question…I’m using the code you posted above to make a balance sheet. The only account not pulling through is:
3200 = “Retained Earnings GP Use Only”
Is this possible to query from the database?
LikeLike
Hi Zak,
The Retained Earnings account would not normally get transactions entered to it. It only gets updated during “internal” GP closing transactions which are specifically excluded from this code.
What are you trying to do?
-Victoria
LikeLike
I’m trying to write a SQL query containing all data for the balance sheet. I also previously wrote SQL queries to create income statements from GP.
LikeLike
Hi Zak,
I would recommend trying one of my Trial Balance reports from this page: https://victoriayudin.com/gp-reports/general-ledger-sql-views/
That might be more what you’re looking for.
-Victoria
LikeLike
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!
LikeLike
Hi Chris,
Which AR table?
-Victoria
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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!
LikeLike
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:
Hope that helps,
-Victoria
LikeLike
I am delighted to say that this connection worked! Thanks for all of your help! This is a huge win for my team!
LikeLike
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.
LikeLike
Hi Deanne,
Yes, since the GL00100 table is already added, you can add any of the GL segments that you want. For example to add the 1st segment, after User_Who_Posted add the following:
, GM.ACTNUMBR_1
-Victoria
LikeLike
Thank you – I was trying to add it in the wrong section. That’s why YOU ARE THE GURU!!! Thanks again
LikeLike
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
LikeLike
Chad,
If the GL entry was created from a sales transaction (for example in Receivables or Sales Order Processing), the ORMSTRID in the GL tables will be the Customer ID.
-Victoria
LikeLike
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?
LikeLike
Tony,
Hardcoding the db is not the only way, however you would have to do something a bit more complicated in SQL to accomplish this. Here are some articles to look at for ideas:
-Victoria
LikeLike
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
LikeLike
Hi Phil,
Here is a blog post that shows an example of updating the GL account descriptions: https://victoriayudin.com/2013/03/11/changing-part-of-a-string-in-sql-server-using-replace/. It might give you some ideas.
If you already have the new descriptions in an Excel file, what I would do is:
Of course, you want to make a backup and get all users out of GP before making this change.
-Victoria
LikeLike
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
LikeLike
Phil,
There are 3 places in the code where you will find
Just remove those and you will be including voided transactions.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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,
LikeLike
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
LikeLike
which sop table relates to gl.
LikeLike
Kk,
SOP10102 has the GL distributions for SOP transactions.
-Victoria
LikeLike
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,
LikeLike
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
LikeLike
Ron, can you verify if the table that was referenced when joining the “Notes table” was SY00700?
LikeLike
Dave,
SY00700 is the window notes – so one note per window. I do not believe that is what Ron meant. Record notes are in the SY03900 table.
-Victoria
LikeLike
Thanks Victoria! I also joined the Notes table so that I could get the notes ‘TXTFIELD’ to this. Just a thought.
LikeLike