GL Account Analysis (Drilldown) – Oracle EBS SQL Report

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

Overview

** This report is used by the GL Financial Statement and Drilldown report, to show Subledger details. **

Detail GL transaction report with one line per transaction including all segments and subledger data, with amounts in both transaction currency and ledger currency. For drilldown to the transaction screen please ensure the column View Transaction is present in the Displayed Columns View Transaction

Report Parameters

Ledger, Ledger ID, Period, Status, Journal Source, Journal Category, Batch, Batch ID, Journal, Journal Header ID, Journal Line Num, Concatenated Segments, Restrict CCIDs through GTT, Restrict JHI through GTT, Balance Type, Budget Name, Encumbrance Type, Show Segments with Descriptions, Show Journal Line DFF Attributes

Oracle EBS Tables Used

ra_rules, ra_customer_trx_lines_all, hz_cust_accounts, hz_parties, xla_event_types_tl, gl_daily_conversion_types, gl_budget_versions, gl_encumbrance_types, fa_distribution_history, gl_code_combinations_kfv, po_headers_all, ap_suppliers, pa_projects_all, pa_tasks, gl_ledgers, gl_periods, gl_je_batches, gl_je_headers, gl_je_lines, gl_je_sources_vl, gl_je_categories_vl, gl_import_references, xla_ae_lines, xla_ae_headers, xla_events, xla_transaction_entities, fa_transaction_headers, fa_additions_b, ap_invoices_all, ap_invoice_distributions_all, ap_checks_all, ar_adjustments_all, ar_cash_receipts_all, pa_draft_revenues_all, pa_agreements_all, pa_expenditure_items_all, pa_expenditures_all, pa_expenditure_types, per_all_people_f, rcv_transactions, rcv_shipment_headers, ra_customer_trx_all

Report Categories

Enginatics

GL Account Analysis (Drilldown) (with inventory and WIP), GL Account Distribution Analysis, AR Customer Upload

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 None
Blitz Report™ XML Import GL_Account_Analysis_Drilldown.xml
Full SQL on Enginatics www.enginatics.com/reports/gl-account-analysis-drilldown/

GL Account Analysis (Drilldown) - Case Study & Technical Analysis

Executive Summary

The GL Account Analysis (Drilldown) report is a pivotal component of the Oracle E-Business Suite financial reporting ecosystem. It is specifically engineered to serve as the drilldown target for the GL Financial Statement and Drilldown (FSG) report. This report bridges the gap between high-level financial statements and the granular subledger transactions that comprise them, providing finance teams with immediate access to the “why” behind the numbers.

By enabling users to navigate from a summarized FSG line item directly to the underlying journal lines and subledger details, this report significantly reduces the time required for variance analysis, auditing, and period-close reconciliation.

Business Challenge

Financial statements provide a summarized view of an organization’s health, but they often lack the detail needed to investigate anomalies. When a variance is detected in an FSG report, analysts typically face:

The Solution

The GL Account Analysis (Drilldown) report solves these issues by integrating directly with the FSG reporting workflow. It acts as a dynamic detailed view that can be invoked from a summary report, providing a seamless analytical path.

Key Features:

Technical Architecture

The report’s architecture is centered on the link between General Ledger balances and Subledger Accounting events. It uses the GL_IMPORT_REFERENCES table as the critical bridge.

Key Tables Involved:

Critical Joins:

The SQL logic prioritizes the connection from GL_JE_LINES to XLA_AE_LINES via GL_IMPORT_REFERENCES. From the XLA layer, it branches out to various subledger tables based on the APPLICATION_ID and ENTITY_CODE, ensuring that the correct source table is queried for each transaction type.

Parameters & Filtering

While often invoked dynamically, the report supports standard parameters for standalone execution:

Performance & Optimization

Given its role as a drilldown report, performance is paramount:

FAQ

Q: How do I use this report with an FSG? A: This report is configured as the “Drilldown” action for specific rows or columns within the FSG definition. When a user views the FSG output, they can click a value to launch this report for that specific intersection of data.

Q: Does it show manual journal entries? A: Yes, manual GL journals are included. However, since they do not originate from a subledger, the subledger-specific columns (like Invoice Number) will be blank.

Q: Can this report be run independently? A: Yes, it can be run as a standalone concurrent request or Blitz Report, provided the user supplies the necessary parameters.

© 2026 Enginatics