Detail GL transaction report with one line per transaction including all segments and subledger data, with amounts in both transaction currency and ledger currency.
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, 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
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
If you would like to try one of these Oracle EBS SQLs without having Blitz Report installed, note that some of the reports require functions from utility package xxen_util.
GL Account Analysis - Pivot by Account Hierarchy 17-Jan-2023 194056.xlsx
www.enginatics.com/reports/gl-account-analysis/
Blitz Report™ import options
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.
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.
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.
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_LINEShold the journal entries. - Account Structure:
GL_CODE_COMBINATIONSresolves the Chart of Accounts segments. - SLA Linkage:
GL_IMPORT_REFERENCESis the critical bridge that connects GL lines toXLA_AE_LINESandXLA_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.
- 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.
- 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_COMBINATIONSandGL_JE_LINESindexes. 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.
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.
Oracle E-Business Suite Reporting Library
We provide an open source Oracle EBS SQLs as a part of operational and project implementation support toolkits for rapid Excel reports generation.
Blitz Report™ is based on Oracle EBS forms technology, and hence requires minimal training. There are no data or performance limitations since the output files are created directly from the database without going through intermediate file formats such as XML.
Blitz Report can be used as BI Publisher and Oracle Discoverer replacement tool. Standard Oracle BI Publisher and Discoverer reports can also be imported into Blitz Report for immediate output to Excel. Typically, reports can be created and version tracked within hours instead of days. The concurrent request output automatically opens upon completion without the need for re-formatting.
The Filters, Columns, Rows and Values fields are used to create and deliver the data in pivot table format with full drill down to details.
The Excel template upload functionality in Blitz Report allows users to create their own layouts by uploading an Excel template with additional sheets and charts, automatically refreshed when the report runs again. This allows to create custom dashboards and more advanced visualizations of report data.
You can download and use Blitz Report free of charge for your first 30 reports.
The installation and implementation process usually takes less than 1 hour; you can refer to our installation and user guides for specific details.
If you would like to optimize your Oracle EBS implementation and or operational reporting you can visit www.enginatics.com to review great ideas and example usage in blog. Or why not try for yourself in our demo environment.
Blitz Report™ – World’s fastest data upload and reporting for Oracle EBS
Oracle Discoverer replacement – importing worksheets into Blitz Report™
Blitz Report™ Questions & Answers
Supply Chain Hub by Blitz Report™
© 2025 Enginatics