GL Account Analysis – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Detail GL transaction report with one line per transaction including all segments and subledger data, with amounts in both transaction currency and ledger currency.

Report Parameters

Ledger, Period, Period From, Period To, Posted Date From, Posted Date To, Journal Source, Journal Category, Batch, Journal, Journal Line, Account Type, Hierarchy Segment, Hierarchy Name, Concatenated Segments, GL_SEGMENT1, GL_SEGMENT1 From, GL_SEGMENT1 To, GL_SEGMENT2, GL_SEGMENT2 From, GL_SEGMENT2 To, GL_SEGMENT3, GL_SEGMENT3 From, GL_SEGMENT3 To, GL_SEGMENT4, GL_SEGMENT4 From, GL_SEGMENT4 To, GL_SEGMENT5, GL_SEGMENT5 From, GL_SEGMENT5 To, GL_SEGMENT6, GL_SEGMENT6 From, GL_SEGMENT6 To, GL_SEGMENT7, GL_SEGMENT7 From, GL_SEGMENT7 To, GL_SEGMENT8, GL_SEGMENT8 From, GL_SEGMENT8 To, GL_SEGMENT9, GL_SEGMENT9 From, GL_SEGMENT9 To, GL_SEGMENT10, GL_SEGMENT10 From, GL_SEGMENT10 To, Status, Transaction Currency, Revaluation Currency, Revaluation Conversion Type, Balance Type, Budget Name, Encumbrance Type, Exclude Zero Amount Lines, Show Segments with Descriptions, Show Open/Close Balances, Show Sub Ledger Contra Accounts, Show Journal Line DFF Attributes, Show Invoice DFF Attributes, Relative Period, Relative Period From, Relative Period To

Oracle EBS Tables Used

fnd_flex_value_norm_hierarchy, gl_code_combinations_kfv, xla_event_types_tl, gl_daily_conversion_types, gl_budget_versions, gl_encumbrance_types, fnd_document_sequences, gl_je_lines_recon, fa_distribution_history, mtl_sales_orders, ra_rules, ra_customer_trx_lines_all

Report Categories

Enginatics, R12 only

Running This SQL Without Blitz Report

Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.

Download & Import Options

Resource	Link
Excel Example Output	GL Account Analysis - Pivot by Account Hierarchy 17-Jan-2023 194056.xlsx
Blitz Report™ XML Import	GL_Account_Analysis.xml
Full SQL on Enginatics	www.enginatics.com/reports/gl-account-analysis/

Case Study: Financial Reconciliation & Subledger Audit

Executive Summary

The GL Account Analysis report is the definitive tool for financial reconciliation and audit within Oracle E-Business Suite. It bridges the gap between General Ledger (GL) balances and the supporting Subledger Accounting (SLA) transactions. By providing a detailed, line-by-line listing of journal entries—complete with source documents, currency details, and segment breakdowns—it empowers finance teams to validate period-end balances, investigate anomalies, and ensure the integrity of financial statements.

Business Challenge

Financial close processes are often delayed by the difficulty of reconciling high-level GL balances with granular transaction data.

Black Box Balances: A GL balance tells you how much, but not why. Understanding the composition of a balance requires tedious drill-down through multiple screens.
Subledger Disconnect: Tracing a GL journal line back to the specific AP Invoice, AR Receipt, or Inventory Transaction that generated it is technically complex due to the SLA architecture.
Multi-Currency Complexity: Reconciling entered currency amounts vs. accounted (functional) currency amounts is a common source of confusion and error.
Audit Requirements: External auditors require detailed transaction listings that prove the validity of account balances.

The Solution

This report serves as a universal “Subledger to GL” reconciliation engine.

Unified View: Combines GL journal headers, lines, and batches into a single flat view.
Drill-Back Capability: Uses the SLA linking tables to retrieve the original transaction number (e.g., Invoice Number, Check Number) and display it alongside the GL account.
Segment Analysis: Breaks down the Accounting Flexfield into individual segments (Company, Cost Center, Account, etc.) for pivot-table friendly analysis.
Currency Precision: Reports both “Entered” (Transaction) and “Accounted” (Ledger) amounts, exposing FX impacts clearly.

Technical Architecture

The report navigates the core Financials data model, specifically the link between GL and SLA.

GL Core: GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES hold the journal entries.
Account Structure: GL_CODE_COMBINATIONS resolves the Chart of Accounts segments.
SLA Linkage: GL_IMPORT_REFERENCES is the critical bridge that connects GL lines to XLA_AE_LINES and XLA_AE_HEADERS.
Source Tables: Depending on the source, it links to AP_INVOICES_ALL, RA_CUSTOMER_TRX_ALL, etc., to fetch the “Source Document” details.

Parameters & Filtering

Ledger Context: Ledger, Period (From/To), Posted Date.
Journal Attributes: Source (e.g., Payables, Receivables), Category, Batch Name.
Account Segments: Filters for individual segments (e.g., Account Type, Cost Center range).
Display Options: Show Segments with Descriptions, Show Open/Close Balances, Show Sub Ledger Contra Accounts.
Currency: Transaction Currency, Revaluation Currency.

Performance & Optimization

Data Volume: This report can generate millions of rows for large periods. It is highly recommended to filter by specific Account Types (e.g., Expense Accounts) or Journal Sources rather than dumping the entire GL for a period.
Indexing: The query relies heavily on the GL_CODE_COMBINATIONS and GL_JE_LINES indexes. Ensure statistics are gathered regularly on these tables.
Pivot Mode: The output is designed for Excel pivot tables. Users should be trained to export the raw data and use Pivot Tables to summarize by Cost Center or Account, rather than trying to read the raw list.

FAQ

Q: Why do I see multiple lines for one transaction? A: A single business transaction (like an invoice) can generate multiple GL journal lines (liability, expense, tax, etc.). This report shows every line to ensure the debits and credits balance.

Q: Can I use this report to reconcile Intercompany accounts? A: Yes, by filtering on the Intercompany segment and the relevant Contra Accounts, you can match transactions between entities.

Q: Does it show unposted journals? A: You can filter by “Status” to include or exclude unposted journals, depending on whether you are doing a pre-close review or a final audit.

GL Account Analysis | Oracle EBS SQL Report

Detail GL transaction report with one line per transaction including all segments and subledger data, with amounts in both transaction currency and ledger…