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

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

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, Journal Line DFF Display

Oracle EBS Tables Used

ra_rules, ra_customer_trx_lines_all, xla_distribution_links, ra_cust_trx_line_gl_dist_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, R12 only

GL Account Distribution Analysis, GL Account Analysis (Drilldown) (with inventory and WIP), GL Account Analysis, GL Account Analysis 11g, AR Transaction Upload, INV Material Account Distribution Detail

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output Example
Blitz Report™ XML Import GL_Account_Analysis_Drilldown.xml
SQL Source GL Account Analysis (Drilldown).sql
Full Report 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.

© Enginatics GmbH