Oracle EBS R12 General Ledger (GL) Module – Practical SQL Queries

Introduction

Oracle E-Business Suite (EBS) R12 General Ledger (GL) is the core Financial module in Oracle EBS R12. It is used to manage journal entries, accounting periods, balances, and financial reporting. The GL module integrates with Payables (AP), Receivables (AR), Fixed Assets (FA), Purchasing (PO), and Inventory (INV).

The main purpose of a general ledger system is to record financial activity of a company and to produce financial and management reports to help the organization make decisions.

Below are real-world SQL queries commonly used.

  1) View configured Ledgers 

    Purpose: Fetch Ledger Configuration Details

SELECT
    ledger_id,
    name AS ledger_name,
  short_name,
  currency_code
FROM
   gl_ledgers
ORDER BY
   ledger_id;

 2) Ledger Information Query

Purpose: Fetch Ledger Configuration Details

SELECT
    gl.name              AS ledger_name,
    gl.short_name,
    gl.currency_code,
    gl.chart_of_accounts_id,
    gl.period_set_name
FROM
    gl_ledgers gl
ORDER BY 

gl.name;

3) View Currency-wise Ledgers

Purpose: Fetch details ledgers by currency

SELECT
    name,
    currency_code
FROM
    gl_ledgers
ORDER BY
    currency_code;

4) View Journal Sources

Purpose: Fetch journal source names

SELECT
    je_source_name,
    user_je_source_name
FROM
    gl_je_sources

ORDER BY
user_je_source_name;

5) Journals for Specific Period

Purpose: Filter journals by accounting period

SELECT
    name,
    je_source,
    period_name
FROM
    gl_je_headers
WHERE
period_name = :P_PERIOD_NAME;    (example--period_name = 'JAN-25')

6) GL Period Status

Purpose: View Open and Closed Accounting Periods

SELECT
    period_name,
    application_id,
    closing_status,
    start_date,
    end_date
FROM
    gl_period_statuses
ORDER BY
    start_date DESC;

 Note: Closing Status

O = Open

C = Closed

N = Never Opened

7) Journal Entry Header Details

Purpose: Get Journal Batch and Header Information

SELECT
    gjb.name               AS batch_name,
    gjh.name               AS journal_name,
    gjh.je_source,
    gjh.je_category,
    gjh.period_name,
    gjh.status,
    gjh.creation_date
FROM
    gl_je_batches gjb,
    gl_je_headers gjh
WHERE
    gjb.je_batch_id = gjh.je_batch_id;

8) Journal Entry Line Details

Purpose: View Debit/Credit Accounting Entries

SELECT
    gjh.name                    AS journal_name,
    gjl.je_line_num,
    gcc.concatenated_segments   AS account_code,
    gjl.entered_dr,
    gjl.entered_cr,
    gjl.accounted_dr,
    gjl.accounted_cr
FROM
    gl_je_headers gjh,
    gl_je_lines gjl,
    gl_code_combinations_kfv gcc
WHERE
    gjh.je_header_id = gjl.je_header_id
    AND gjl.code_combination_id = gcc.code_combination_id;
    
 9) Journal Source & Category Details

Purpose: View Source Module of Journals

SELECT
    gjh.name            AS journal_name,
    gjh.je_source,
    gjh.je_category,
    gjh.period_name,
    gjh.status
FROM
    gl_je_headers gjh;

 10) Journal Posting Status

Purpose: View Posted and Un-posted Journals

SELECT

    gjh.name           AS journal_name,

    gjh.period_name,

    gjh.status,

CASE gjh.status

WHEN 'P' THEN 'Posted'

WHEN 'U' THEN 'Unposted'

ELSE gjh.status

END posting_status,

    gjh.posted_date,

    gjh.je_source

FROM

    gl_je_headers gjh

ORDER BY

    gjh.posted_date DESC;

11) View Un-posted Journals

Purpose: Fetch journals which are pending posting

SELECT
    name,
    period_name,
    je_source,
    status
FROM
    gl_je_headers
WHERE
    status = 'U';

Note:

U = Unposted

12) Daily Journal Activity

Purpose: View Journals Created on Specific Date

SELECT
    gjh.name            AS journal_name,
    gjh.creation_date,
    gjh.je_source,
    gjh.status,
    gjl.entered_dr,
    gjl.entered_cr
FROM
    gl_je_headers gjh,
    gl_je_lines gjl
WHERE
    gjh.je_header_id = gjl.je_header_id
    AND TRUNC(gjh.creation_date) = TRUNC(SYSDATE);

 

Oracle EBS R12 General Ledger (GL) – Key Terms

Ledger:	Primary accounting entity that defines Chart of Accounts, Currency, and Accounting Calendar.
Chart of Accounts (COA):	Structure of accounting segments used to classify transactions.
Account Combination:	Complete accounting code combination from COA segments.
Cost Center:	Segment used to track departmental costs.
Currency :	Functional currency used for accounting transactions.
Accounting Calendar:	Defines accounting periods (monthly, quarterly, yearly).
Period :	Accounting month in which transactions are recorded.
Open Period :	Period available for data entry and posting.
Closed Period :	Period no longer available for posting.
Journal Entry :	Accounting transaction containing debit and credit lines.
Journal Header:	Summary information for a journal entry.
Journal Line :	Individual debit or credit accounting line.
Source :	Origin of a journal (Payables, Receivables, Inventory, Manual, etc.).
Category :	Type of  journal transaction (Invoice, Payment, Adjustment, etc.).
Unposted Journal :	Journal created but not yet posted.
Posting :	Process that updates account balances from journals.
Posted Journal:	Journal whose balances have been updated in GL.
Budget :	Planned financial figures used for comparison.
GL Transfer :	Process of moving accounting entries from SLA to General Ledger.

 Conclusion

These practical GL queries are useful to analyze journal, review ledger, monitoring accounting periods and trace transactions acros