# Oracle EBS GL Unposted Journals and Period Status SQL Generator

> Source: https://netray.co/tools/oracle-ebs-gl-open-journals-and-period-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Enter a ledger and period and get queries to see period status, unposted journals, stuck import rows and subledger entries not yet in the GL.

## Category

oracle

## Erp

Oracle E-Business Suite

## Audience

- Oracle EBS finance analyst
- GL accountant
- Oracle EBS developer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Ledger ID

**Key:** ledger_id

**Type:** number

**Default:** 1001

**Help:** From GL_LEDGERS.LEDGER_ID

#### Period name

**Key:** period_name

**Type:** text

**Default:** OCT-26

**Help:** As defined in your accounting calendar

#### Subledger application ID

**Key:** app_id

**Type:** select

**Options:**

- 200
- 222
- 140
- 201

**Default:** 200

**Help:** 200 Payables, 222 Receivables, 140 Fixed Assets, 201 Purchasing

**Blocks:**

#### 1. Period status by application

**Body:** SELECT fa.application_short_name,
       ps.period_name,
       ps.closing_status,
       DECODE(ps.closing_status,
              'O', 'Open',
              'C', 'Closed',
              'F', 'Future entry',
              'N', 'Never opened',
              'P', 'Permanently closed',
              ps.closing_status) AS status_text
FROM   gl_period_statuses ps,
       fnd_application fa
WHERE  ps.application_id = fa.application_id
AND    ps.ledger_id = {{ledger_id}}
AND    ps.period_name = '{{period_name}}'
AND    ps.application_id IN (101, 200, 222, 140, 201)
ORDER  BY fa.application_short_name;

#### 2. Unposted journals in the period

**Body:** SELECT jh.je_header_id,
       jh.name,
       jh.je_source,
       jh.je_category,
       jh.status,
       jh.running_total_dr,
       jh.running_total_cr,
       jh.created_by,
       jh.creation_date
FROM   gl_je_headers jh
WHERE  jh.ledger_id = {{ledger_id}}
AND    jh.period_name = '{{period_name}}'
AND    jh.actual_flag = 'A'
AND    jh.status <> 'P'
ORDER  BY jh.je_source, jh.name;

#### 3. Rows stuck in GL_INTERFACE

**Body:** SELECT gi.status,
       gi.user_je_source_name,
       gi.group_id,
       COUNT(*)  AS row_count,
       SUM(NVL(gi.entered_dr, 0)) AS total_dr,
       SUM(NVL(gi.entered_cr, 0)) AS total_cr
FROM   gl_interface gi
WHERE  gi.ledger_id = {{ledger_id}}
AND    gi.status <> 'PROCESSED'
GROUP  BY gi.status, gi.user_je_source_name, gi.group_id
ORDER  BY gi.user_je_source_name;

#### 4. Subledger entries not transferred to GL

**Body:** SELECT xah.application_id,
       xah.period_name,
       xah.accounting_entry_status_code,
       xah.gl_transfer_status_code,
       COUNT(*) AS entries
FROM   xla_ae_headers xah
WHERE  xah.ledger_id = {{ledger_id}}
AND    xah.application_id = {{app_id}}
AND    xah.period_name = '{{period_name}}'
AND    xah.gl_transfer_status_code <> 'Y'
GROUP  BY xah.application_id, xah.period_name,
          xah.accounting_entry_status_code, xah.gl_transfer_status_code;

#### 5. Trial balance check for the period

**Body:** SELECT gcc.segment1,
       gcc.segment2,
       gcc.segment3,
       SUM(NVL(gb.period_net_dr, 0)) AS period_dr,
       SUM(NVL(gb.period_net_cr, 0)) AS period_cr
FROM   gl_balances gb,
       gl_code_combinations gcc
WHERE  gb.code_combination_id = gcc.code_combination_id
AND    gb.ledger_id = {{ledger_id}}
AND    gb.period_name = '{{period_name}}'
AND    gb.actual_flag = 'A'
AND    gb.currency_code = (SELECT currency_code FROM gl_ledgers WHERE ledger_id = {{ledger_id}})
GROUP  BY gcc.segment1, gcc.segment2, gcc.segment3
ORDER  BY gcc.segment1, gcc.segment2, gcc.segment3;

**Notes:** Queries read only. Application IDs 101 (GL), 200 (Payables), 222 (Receivables), 140 (Fixed Assets) and 201 (Purchasing) are the standard seeded values. Segment meanings in query 5 depend on your chart of accounts, so adjust the segment columns. Status values should be verified for your release. GL_BALANCES also holds translated and converted balances, so check the currency and translated flags if you report in several currencies.

## Content

**Intro:** During month end, the GL close often stalls on the same few things: a subledger period still open, unposted journals, a Journal Import that never finished, or subledger entries not transferred. These queries find each one.

**Sections:**

#### How to use the generator

**Body:** Enter the ledger ID and period name exactly as they appear in your calendar. Run query 1 first to see which applications are still open, then queries 2 to 4 to find the blockers. Query 5 gives a quick debit and credit view per account segment for a reasonableness check. Export each result to Excel for the close log.

**Bullets:**

- Find the ledger ID in GL_LEDGERS
- Period names are case sensitive in the query
- Re-run after each fix to watch the counts drop

#### What the status codes mean

**Body:** In GL_PERIOD_STATUSES, CLOSING_STATUS values are O for open, C for closed, F for future entry, N for never opened and P for permanently closed. A GL period normally cannot be closed cleanly while a subledger that feeds it is still open for the same period. In GL_JE_HEADERS, STATUS P means posted, while U means unposted and other values may indicate a journal in error or in process.

#### Reading each query

**Body:** Query 2 lists journals that would not be included in balances until posted, filtered to actuals. Query 3 shows interface rows that are not PROCESSED, grouped by source and group, which points to failed or never-run Journal Import. Query 4 compares to the SLA status, where a transfer status other than Y means the entry has not reached the GL interface. Resolve in that order: transfer, import, then post.

#### Common mistakes

**Body:** Filtering on period name without the ledger returns rows from other ledgers in a shared calendar. Posting journals before subledgers are closed produces late adjustments, and deleting rows from GL_INTERFACE by hand instead of using the Correct Journal Import Data form leaves control tables inconsistent.

**Cta:** Netray can run these close checks for you on a schedule and answer questions like 'what is blocking the October close' in plain language, with the SQL shown and read-only access.

**Faq:**

#### How do I find unposted journals in Oracle EBS?

**Answer:** Query GL_JE_HEADERS for the ledger and period with a status that is not P, filtered to actual journals. The result lists each unposted journal with source, category and creator so you can post or correct it before close.

#### What does CLOSING_STATUS mean in GL_PERIOD_STATUSES?

**Answer:** O is open, C closed, F future entry, N never opened and P permanently closed. Rows exist per application and ledger, so the GL and each subledger have their own status for the same period.

#### Why are subledger entries not in the GL?

**Answer:** The Transfer Journal Entries to GL program may not have run, the accounting may still be draft, or Journal Import may have failed. Check GL_TRANSFER_STATUS_CODE in XLA_AE_HEADERS and the interface rows in GL_INTERFACE.

#### Can I use these queries across several ledgers?

**Answer:** Run them once per ledger ID, or remove the ledger filter and add LEDGER_ID to the select list and group by. Keep the ledger in the output so you can tell the results apart.

## Search terms

oracle ebs unposted journals query, gl_period_statuses sql, oracle gl period close query, gl_interface stuck rows, xla_ae_headers gl_transfer_status_code, oracle ebs open periods sql, oracle apps month end close sql, gl_je_headers status U

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