How can I extract G/L data with an SQL script?

ilkerjde

Member
I am working as a financial auditor and I want to manually compare journal entries on JDE with the year-end G/L. To do this, I want to extract all journal entry data on F0911 and sum them to reach G/L account balances. I am using a third party data analysis tool and the number of records or complexity of calculations is not a problem.

However, since I am not very experienced in JDE and there are a lot of document types, company codes..etc on F0911, I have no idea which records should I use to check G/L.

As some basic principles:
Sum of all records belong to each journal entry must be equal to zero
Sum of all records must be equal to zero
Sum of all journal entries belong to each account must be equal to the balance of that account on G/L

I would appreciate if you share any SQL scripts which can be used to extract data I require.
 
Last edited:
Hi Ilker

No disrespect, but there are a whole host of standard JDE Integrity reports that monitor G/L integrity.
Any company worth a pinch of salt, should be running these reports on a regular basis and should be able to demonstrate compliance in this area.
 
Thank you for response Sef, but what I try to do is more than checking integrity of G/L. I am planning to perform more detailed tests once I extract journal entries.

However, I can not get a list of journal entries or the ones I extracted do not match with G/L.
 
Ilker,

You need to go back to your client to find out what the relevant search criteria should be in your SQL. Things like Companies, Ledger Types, whether Ledger Types have to balance, Document Types, etc, are subject to change in each JDE implementation (actually even each Environment). We could answer what is “normal”, but with your lack of JDE experience, plus not knowing the implementation specifics you are working on, even that may lead you astray.
 
[ QUOTE ]
Thank you for response Sef, but what I try to do is more than checking integrity of G/L. I am planning to perform more detailed tests once I extract journal entries.

However, I can not get a list of journal entries or the ones I extracted do not match with G/L.

[/ QUOTE ]

I found out that the problem was with the number format
smile.gif
After analysing a few indivicual records, I simply extracted all data with using correct number format and without any restriction. it matched with G/L perfectly.
 
Back
Top