How To: Query Basic Information from JD Edwards General Ledger with Example

JD Edwards General Ledger Tables

Financial reporting is a constant and growing concern for most companies. While ERP systems like JD Edwards EnterpriseOne (E1) or JD Edwards World can improve the quality of your business, new users may need some help. With any JD Edwards implementation or system conversion, understanding some table basics can help save time when writing SQL scripts.  For this example we will retrieve basic information from the JDE General Ledger through a simple SQL query.  The following tables are primarily used together for JD Edwards General Ledger reporting.  Refer to the list at the end of the post for frequently used fields and descriptions within these tables.

  • F0901 – Account Master:  Contains Account Descriptions, Level of Detail, and Category Codes.
  • F0902 – General Ledger Balances:  Contains Account Balances with various details
  • F0911 – General Ledger Details:  Contains journal entries that are rolled up and posted to the F0902 during the GL Posting process.
  • F0006 – Business Unit Master:  Contains Business Unit Descriptions and Category Code Information.

Example:

Let’s say that you would like to see JDE GL balances for a range of accounts.  You may also want to see the account descriptions and business unit descriptions.  So we will begin looking at how to relate the two tables so we can retrieve the data we need.  In order to write this query, you will need to join the F0902, F0006, and F0901 tables.

To join the F0902 to the F0901, we will relate F0902.GBAID to F0901.GMAID
To join the F0902 to the F0006, we will relate F0902.GBMCU to F0006.MCMCU

Sample SQL:

SELECT * FROM F0902
INNER JOIN F0901 ON GBAID = GMAID
INNER JOIN F0006 ON GBMCU = GMMCU

Now, let’s do the same thing for the GL Details Table.

To join the F0911 to the F0901, we will relate F0911.GLAID  = F0901.GMAID
To join the F0911 to the F0006, we will relate F0911.GLMCU = F0006.MCMCU

Sample SQL:

SELECT * FROM F0911
INNER JOIN F0901 ON GLAID = GMAID
INNER JOIN F0006 ON GLMCU = GMMCU

***Note*** The above SQL is based on SQL Server.  You may need to change the owner.  If you are using Oracle or DB2, you will need to change the syntax in order for the SQL to work.

Each of these queries will return all of the fields from their three joined JDE tables.  This sample SQL can help combine account details for easy reference.  Check back for future SQL examples used with JD Edwards.  Checkout www.w3schools.com/sql/ for more information on specific SQL functions or syntax.

The next post in the series will focus on filtering the data we have gathered. 

How To: Filter Query Data from JD Edwards General Ledger Tables with SQL Examples

 

Below is a list of frequently used tables and fields for JD Edwards General Ledger Module:

F0901 – Account Master (GM)

  • GMOBJ – Object/Account
  • GMSUB – Subsidiary
  • GMLDA – Level of Detail
  • GMDL01 – Description
  • GMR001 – GMR023 – Cat Code 1 – 23

F0902 – General Ledger Balances (GB)

  • GBFY – Fiscal Year
  • GBLT – Ledger Type
  • GBSBL – Subledger
  • GBCO -  Company
  • GBAPYC – Balance Forward
  • GBAN01 – GBAN14 – Period 1-14
  • GBBORG – Original Budget
  • GBMCU- Business Unit
  • GBOBJ – Object/Account
  • GBSUB –Subsidiary
  • GBSBLT – Ledger Type

F0911 – General Ledger Details (GL)

  • GLDCT – Doc Type
  • GLDOC – Document Number
  • GLDGJ – GL Date
  • GLPOST – Post
  • GLICU – Batch Number
  • GLICUT – Batch Type
  • GLDICJ – Batch Date
  • GLCO – Company
  • GLMCU – Business Unit
  • GLOBJ – Object/Account
  • GLSUB – Subsidiary
  • GLSBL – Subledger
  • GLSBLT – Subledger Type
  • GLLT – Ledger Type
  • GLPN – Period Number
  • GLFY – Fiscal Year
  • GLAA – Amount
  • GLU – Units
  • GLEXA – Explanation
  • GLEXR – Remark
  • GLAN8 – Address Number
  • GLIVD – Invoice Date
  • GLDCTO – Order Type
  • GLLNID – Line Number

F0006 – Business Unit Master (MC)

  • MCMCU – Business Unit
  • MCSTYL -   Business Unit Type
  • MCDL01 – Description
  • MCRP01 – MCRP30 – Cat Code 1 – 30

Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

  • Recent Replies