Dynamics GP General Ledger SQL Code
- All GL Transactions Includes posted, unposted and historical GL transactions.
- All Posted GL Transactions Includes posted and historical GL transactions only (nothing unposted).
- Budget Refreshable Excel report for monthly GL budget in Dynamics GP.
- Fixed Allocation Accounts The distribution accounts and percentages for all active fixed allocation accounts.
- Historical GL Trial Balance Refreshable Excel report for Dynamics GP General Ledger trial balance showing the monthly net changes for the specified year.
- Open Year GL Trial Balance – Net Changes General Ledger trial balance showing the monthly net changes for the first open year in your Dynamics GP.
- Open Year GL Trial Balance – Month End Balances General Ledger trial balance showing the month end balances for the first open year in your Dynamics GP.
- Search GL Transactions Easier Has an Amount column for searching and also adds the date and time stamps,
- Variable Allocation Accounts The distribution and breakdown accounts for all active variable allocation accounts.
Victoria Yudin 
Hi Victoria,
Is there a SQL Script that can mass activate around 2500 currently inactive GL accounts?
LikeLike
Hi Rob,
What is the criteria for determining what accounts should be activated?
-Victoria
LikeLike
Hi Victoria — apologies I couldn’t find a way to post a new comment rather than reply. Quick question – I am working with a CFO and writing SQL against their DGP data. The CFO says they are using two different ledger types, Base and IFRS. Is there a way to query the ledger type on a transaction level so I can know which ledger entries are attributed to Base and which to IFRS? That way I can filter for one or the other. Thanks in advance.
I love your resources here and looking at your queries I’m realizing one I’ve been using is based off of yours.
Best,
Matt
LikeLiked by 1 person
Hi Matt,
That happens to me, too – I look at someone’s code and I’m like, “hey, that looks familiar!” 🙂
If you’re using the GL tables like GL20000 and GL30000, they will have a Ledger_ID field that you can use for this. The ledger setup is in GL40001 – so you can figure out what ID is which Ledger.
Hope that helps.
-Victoria
LikeLiked by 1 person
Thank you Victoria! You are amazing. This is exactly what I needed. I pulled GL40001 and I see Ledger_ID on GL30000 / 20000 / 10000. Thanks for the quick reply!
LikeLike
Hi Victoria,
Hoping you can help with this. Could you recommend the best way to run a report all lines of JE’s that include a distribution to a specific account? If I could run a report for a given time period that would be perfect too. Your GL_Trx view is almost what I need but I can’t filter down to only transactions that hit a specific account, when I filter on an account I lose the other lines of the transactions.
Thank you for any guidance you can offer.
LikeLike
Hi Mike,
For this you basically need to make 2 passes through the data – once to find all the journal entries that have the account number you want, then a second pass to get all the lines of those journal entries. There are much more elegant ways to do this if you were doing this for a report used all the time or if you have a huge amount of data, but for a quick result you can do something like the code below. Just change the account number at the end to the one you want:
select *
from view_GL_Trx
where Journal_Entry in
(select Journal_Entry
from view_GL_Trx
where Account_Number = ‘2000-00’)
Hope that helps.
-Victoria
LikeLike
Victoria, first of all thank you for your research and guidance on these topics. Is it possible for you to instruct us on how to add the transaction description to this query?
LikeLike
Hi Charles,
Which query specifically?
-Victoria
LikeLike
All GL Transactions. Thanks.
LikeLike
Hi Charles,
I have updated the code to include the Description.
-Victoria
LikeLike
Victoria, I incorrectly inactivated some accounts in Mass Modify. Can I simply go into GL00100 and change the flag back to Active? Are other tables involved? Thanks for your help!
LikeLike
Hi Paul,
I think just changing GL00100 should be enough. That said, I’ve not done this myself, so I am not 100% certain. I always recommend testing with one or two accounts first. Or running SQL Profiler when updating one account in GP manually to see everything impacted.
-Victoria
LikeLike
Thank you. Only affects that one table. Worked fine. Thanks again.
LikeLike
Victoria,
First thanks for maintaining such a useful blog.
My question is: I am using the AccountTransactions view and would like to know how I can link that to the vendor tables in order to provide the vendorid and the vendorname along with all of the other data in that view.
LikeLike
Hi Tim,
The vendor ID and name should already be in the AccountTransactions view – they are called Originating Master ID and Originating Master Name.
-Victoria
LikeLike
Hi Victoria,
I am trying update our account categories in the G/L. Is there any easy way to update about 5000 accounts to multiple categories?
LikeLike
Hi Josh,
You should be able to update the category in the GL00101 table. The categories are stored as numbers – you can find the numbers in the GL00102 table.
-Victoria
LikeLike
Thank you Victoria!
LikeLike
Hi Victoria,
Do you know if the batch comment from batch entry window is saved in any of the the GL tables when you post the batch?
Thanks
fek
LikeLike
Hi Fek,
Unfortunately, the batch comment is not stored, it is deleted once a batch is posted.
-Victoria
LikeLike
Hi Victoria,
First – thank you so much for maintaining such a high quality website for so many years.
My question: Are you aware of a way to see all of the je’s that have been made to one account? It would be similar to a detailed trial balance, but it would contain only entries that hit a certain account. If you select the account in the cross-reference report, it will show only the lines of the je that hit the account.
I know this can be done one at a time through JE Inquiry once you know the JE numbers, but this is difficult when an account has many je’s.
Thank you
LikeLike
Hi David,
So you want to select an account and then see all the JE lines for JE’s containing that account? So if a JE has 4 lines and you’re selecting one of the accounts you still want to see all 4 of the lines?
That’s absolutely doable, but would have to be a custom report. Nothing out-of-the-box in GP can do this. And it cannot be done in a SmartList, it has to be somewhere where you can enter a parameter – Excel, SSRS, Crystal…
If I were doing this, I would write SQL code for this first – the basic logic is a 2 step process:
Once you have that, you can use it in the reporting tool of your choice.
-Victoria
LikeLike
Victoria – is there anyway, in an of itself to know through querying if a GL entry is a Reversing Entry? Right now it appears that the only way to do that is by finding the matching GL Entry that performs the reversal
LikeLike
Hi Devin,
I believe reversing entries will have a TRXSORCE with ‘REV’ in them. In our data I see either ‘ICREV’ or ‘GLREV’ as the first 5 characters. Also the reversing entry will have the RCTRXSEQ = 1 and the original entry will have the same JE number with RCTRXSEQ = 0.
Hope that helps.
-Victoria
LikeLike
Hello Victoria,
I am currently using the “AccountTransactions” view to look at monthly inventory count adjustments. I am trying to bring in the item # and a quantity count for each adjustment. To achieve this for inventory, I am using IV30300 which seems to return the results I need. My problem is bringing in the items from the purchasing side. I assumed I would use POP10500 but not being incredibly familiar with GP, I wasn’t quite sure. I have included my query below. Any direction you could give me would be greatly appreciated. Thank you so much for your time.
Meredith
SELECT
at.[TRX Date]
,at.[Account Description]
,at.[Credit Amount]
,at.[Debit Amount]
,’QTY’ = SUM(IV33.TRXQTY)
,’TOTAL_COST’ = CASE WHEN IV33.TRXQTY>0 THEN SUM(IV33.EXTDCOST) ELSE -SUM(IV33.EXTDCOST) END
,at.[Description]
,at.[Originating Control Number]
,at.[Originating Source]
,IV11.ITEMNMBR
,iv11.ITEMDESC
FROM [AccountTransactions] at
left join IV30300 IV33 on iv33.DOCNUMBR = at.[Originating Control Number]
left JOIN IV00101 IV11 ON IV33.ITEMNMBR=IV11.ITEMNMBR
where at.[TRX Date] between ‘2015-11-01’ and ‘2015-11-30’
and at.[Account Number] = ‘100-50320-‘
group by at.[Series]
,at.[TRX Date], at.[Account Number], at.[Account Description], at.[Credit Amount], at.[Debit Amount], at.[Account Index]
,at.[Description], at.[Originating Control Number], at.[Originating Credit Amount], at.[Originating Master ID]
,at.[Originating Sequence Number], at.[Originating Source], at.[Source Document], at.[TRX Source], IV11.ITEMNMBR
,IV33.TRXQTY, IV33.EXTDCOST, iv11.ITEMDESC, iv33.[DOCNUMBR]
LikeLike
Hi Meredith,
There should not be inventory adjustments coming from the POP module… Or are you looking to link every GL line that hits inventory back to the source? Part of the problem with that will be that POP transactions are typically posted to the GL in summary, so one GL entry might be associated with 10 line items with different quantities. Or you might have a situation where there are 5 GL distributions from a POP receipt with 12 line items. How are you looking to display this data?
-Victoria
LikeLike
Victoria,
Actually, that makes complete sense. Thank you so much. You are always such a huge help!
Meredith
LikeLike
Hi Victoria,
Is there an easy way to identify which GL accounts have never been used?
LikeLike
Hi Beth,
The code below should give you the list:
select n.ACTINDX [Account Index],
rtrim(n.ACTNUMST) Account,
rtrim(a.ACTDESCR)[Account Name]
from GL00105 n
inner join GL00100 a on a.ACTINDX = n.ACTINDX
where n.ACTINDX
not in
(select distinct ACTINDX from GL10001
union
select distinct ACTINDX from GL20000
union
select distinct ACTINDX from GL30000)
Please note that this is not checking any setup windows, only GL transactions.
-Victoria
LikeLike
Hi Victoria,
Thank you for the query above, i too and looking for GL accounts which have not been used.
When i use the query above, I am getting an error
“Ambiguous column name ‘ACTINDX’ ”
I was curious if you might know what this out be, i suspect the inner join.
using GP2015 and SQL2014
Thank you for your time in advance!
LikeLike
Hi Brandon,
I just tested this code on my GP and it worked with no error.
I suspect you might have made a change to the code posted above or something didn’t copy properly into SQL.
Can you please try re-copying the code and testing it again? If you’re still having trouble, it would help to see a screenshot of what this looks like in your SQL Server Management Studio.
-Victoria
LikeLike
Hi Victoria,
I’m looking for a way to pull summary data similar to what is in the AccountSummary view, but includes entries for months with no activity. Reason being that we create YTD balances for an external reporting system and we are running into missing YTD numbers when we select a month that had no activity in it, even if that particular account had activity earlier in the year. Any advice is much appreciated!
LikeLike
Dave,
To accomplish this you really cannot use the AccountSummary view, at least not as is. You would have to build your own code that starts out with all accounts, then links to the monthly numbers. You can always pull out any rows with all zeros, but at least that will capture all data.
Another option would be to use something that gets the balances for all accounts that have a balance – take a look at the two Open Year GL Trial Balance views on this page for some ideas on this.
-Victoria
LikeLike
I’ll check those out. Thanks a ton for the quick response and in general for all the knowledge you share on your site.
Dave
LikeLike
Is there a way to pull the company name, or company ID, or database name into the same table with the GL transactions? I’m a total beginner, so feel free to give me push in the right direction. I combined our companies’ GL transactions into one report using “union all,” but now I can’t tell what entities each transaction came from because our account number convention does not define this sufficiently.
LikeLike
Hi Taylor,
You can add the company ID, name, or whatever else would be helpful by adding fields to your SQL query. Take a look at the example here: SQL view to show open AP invoices from multiple companies in GP.
-Victoria
LikeLike
I was wondering if you had an SQL view to show all journal posting details? We usually use the system default “” under the SMARTLIST > Financial > Account Transactions > “” and just use the criterias to filter but our auditor just picked up the fact that if the journal had multiple entries going to the same GL code then the smartlist picked up the 1st amount and duplicated that to all the entries for that same code:
Ex actual journal
Balance Brought FWD journal : Debtors DR 500k
Balance Brought FWD journal : Debtors DR CR 42k
Balance Brought FWD journal : Debtors DR CR 4k
etc
SMARTLIST result
Balance Brought FWD journal : Debtors DR 500k
Balance Brought FWD journal : Debtors DR 500k
Balance Brought FWD journal : Debtors DR 500k
After a mild heart attack, our controller noticed this problem with the smartlis.
LikeLike
Geraldine,
That does not sound right. SmartList should be simply pulling the data out of GP exactly how it appears. If you happen to restart the GP application and rerun the same SmartList filtering simply on that one journal entry number, do you still get the same results?
My SQL code usually filters out the Balance Brought Forward entries, since technically they would be doubling up data, but if you want to, you can change my SQL view for all GL transactions to take out that restriction. (Remove the “SOURCDOC not in (‘BBF’,’P/L’) and” on lines 43 and 51.)
-Victoria
LikeLike
Is there any View Available to findoing out Oustanding invoices from GL Table GL20000?
LikeLike
Sanjay,
There is no direct relationship between the GL20000 table and outstanding invoices, so I am not sure what you mean. If you would like to explain what you’re looking for in more detail, I can try to help.
-Victoria
LikeLike