# JD Edwards F0911 General Ledger SQL Query Generator

> Source: https://netray.co/tools/jde-f0911-general-ledger-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in schema, company, ledger type and a Julian date range to get ready-to-run SQL for F0911 detail and an account summary.

## Category

developer

## Erp

Oracle JD Edwards EnterpriseOne

## Audience

- JDE developer
- Report writer
- Finance systems analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Data schema or library

**Key:** schema

**Type:** text

**Default:** PRODDTA

**Help:** Business data schema, for example PRODDTA or TESTDTA.

#### Company (GLKCO)

**Key:** company

**Type:** text

**Default:** 00001

#### Ledger type (GLLT)

**Key:** ledger

**Type:** select

**Options:**

- AA
- BA
- AU
- CA

**Default:** AA

#### From GL date (Julian)

**Key:** from_jul

**Type:** number

**Default:** 126001

#### To GL date (Julian)

**Key:** to_jul

**Type:** number

**Default:** 126365

#### Object account (GLOBJ)

**Key:** account

**Type:** text

**Default:** 1110

#### Business unit (GLMCU)

**Key:** bu

**Type:** text

**Default:** M30

**Blocks:**

#### GL detail with decoded dates (SQL Server)

**Body:** SELECT GLKCO AS company,
       GLDCT AS doc_type,
       GLDOC AS doc_number,
       GLJELN AS line_no,
       GLEXTL AS ext_code,
       DATEADD(DAY, (GLDGJ % 1000) - 1, DATEFROMPARTS(1900 + GLDGJ / 1000, 1, 1)) AS gl_date,
       LTRIM(RTRIM(GLMCU)) AS business_unit,
       GLOBJ AS object_acct,
       GLSUB AS subsidiary,
       GLLT AS ledger_type,
       CAST(GLAA AS DECIMAL(19,2)) / 100 AS amount,
       GLEXA AS explanation,
       GLPOST AS posted_code
FROM {{schema}}.F0911
WHERE GLKCO = '{{company}}'
  AND GLLT = '{{ledger}}'
  AND GLDGJ BETWEEN {{from_jul}} AND {{to_jul}}
  AND GLOBJ = '{{account}}'
  AND LTRIM(GLMCU) = '{{bu}}'
ORDER BY GLDGJ, GLDCT, GLDOC, GLJELN;

#### Posted balance by business unit and account

**Body:** SELECT LTRIM(GLMCU) AS business_unit,
       GLOBJ AS object_acct,
       SUM(CAST(GLAA AS DECIMAL(19,2))) / 100 AS net_amount,
       COUNT(*) AS line_count
FROM {{schema}}.F0911
WHERE GLKCO = '{{company}}'
  AND GLLT = '{{ledger}}'
  AND GLPOST = 'P'
  AND GLDGJ BETWEEN {{from_jul}} AND {{to_jul}}
GROUP BY LTRIM(GLMCU), GLOBJ
ORDER BY business_unit, object_acct;

#### Unposted batches check

**Body:** SELECT GLKCO, GLDCT, GLDOC, GLICU AS batch_number, GLICUT AS batch_type, GLPOST
FROM {{schema}}.F0911
WHERE GLKCO = '{{company}}'
  AND GLLT = '{{ledger}}'
  AND GLPOST <> 'P'
  AND GLDGJ BETWEEN {{from_jul}} AND {{to_jul}}
GROUP BY GLKCO, GLDCT, GLDOC, GLICU, GLICUT, GLPOST;

**Notes:** Written for SQL Server. For Oracle replace the date expression with TO_DATE(TO_CHAR(1900 + TRUNC(GLDGJ / 1000)) || LPAD(MOD(GLDGJ, 1000), 3, '0'), 'YYYYDDD') and drop the schema syntax differences. Amounts are divided by 100 on the assumption of a two decimal currency. Company GLKCO is character data and keeps leading zeros. Run on a reporting copy first and verify column names in your release.

## Content

**Intro:** F0911 is the Account Ledger, the table behind journal entries, voucher and invoice GL postings and most finance reports in JD Edwards EnterpriseOne. This generator writes three safe, read-only queries for it that decode the Julian date, scale the amount and filter on the fields that matter.

**Sections:**

#### How to use it

**Body:** Set the schema, company and ledger type, then enter your Julian date range, which you can calculate with the Julian date converter. Copy the generated SQL into SQL Server Management Studio or your reporting tool. The three blocks give you line detail, a posted balance by business unit and object account, and a quick check for batches that never posted.

#### Key columns in F0911

**Body:** The document is identified by GLKCO (document company), GLDCT (type), GLDOC (number) and GLJELN with GLEXTL (line and extension). GLLT is the ledger type: AA is actual amounts, BA is budget amounts and AU is actual units. GLAA holds the amount, GLU the units, and GLPOST flags posted (P) or unposted rows. GLOBJ and GLSUB combine with the business unit GLMCU to make the account.

**Bullets:**

- GLDGJ is the GL date, GLDICJ the invoice date and GLDSYJ the batch date
- GLICU and GLICUT identify the batch
- GLPN is the period number and GLFY the fiscal year

#### Reconciling to account balances

**Body:** F0902 holds the period balances. A posted F0911 total for a period should agree with the F0902 net posting for the same account and ledger. Differences point to unposted records, a posting error, or an account that was rebuilt. Compare in a copy of production, never against a live balance update job.

#### Common mistakes

**Body:** Leaving out the ledger type so actuals and budgets are summed together, comparing GLMCU without trimming its padding, forgetting that GLAA is stored with implied decimals, and running unbounded queries against a large table during month end.

**Cta:** If finance users keep asking IT for ledger extracts, we can give them a read-only question and answer layer that shows the SQL it runs against JDE.

**Faq:**

#### Why is the GL amount 100 times too big?

**Answer:** JDE stores amounts as integers with implied decimals. In a two decimal currency the value 123456 means 1,234.56. Divide by 100, or by the scale of the currency in F0013 for other currencies.

#### What does ledger type AA mean?

**Answer:** AA is the actual amounts ledger. BA holds budget amounts, AU actual units and BU budget units. Filter on the ledger type or you will mix them in totals.

#### Why do I need LTRIM on the business unit?

**Answer:** The business unit is a character field of 12 that is right justified, so a short code like M30 is stored with leading spaces. Trimming lets you compare against the code as users type it.

#### Can this query write data?

**Answer:** No. All three statements are SELECT only. Never insert or update JDE tables directly, because that bypasses business functions, balances, audit and next numbers.

## Search terms

jde f0911 sql query, jd edwards general ledger sql, f0911 account ledger query, jde gl detail sql server, jd edwards glaa decimal, jde sql generator, jde trial balance sql

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json