GL Account Analysis – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: General Ledger · all General Ledger reports

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.

Show Open/Close Balances adds a ledger currency opening balance row for the first selected period and a closing balance row for the last selected period per account combination, marked as Balance in the Record Type column. Balance rows follow the ledger, period and account filters, not journal level filters such as Source or Category. The chosen value controls whether balance rows appear only for accounts with journal activity in the range, also for accounts with a nonzero balance, or for every account with a balance record, while All Accounts Grouped shows a single opening and closing balance row summed over the selected accounts.

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, 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, Journal Line DFF Display, Invoice DFF Display, 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, per_jobs_vl, hr_all_positions_f_tl, hr_all_organization_units_vl, mtl_generic_dispositions, cst_cost_updates, mtl_cycle_count_headers, mtl_physical_inventories, okc_k_headers_all_b, mtl_txn_request_headers, fnd_application_vl, fnd_id_flex_segments, fnd_flex_values, 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, gl_daily_rates, zx_rates_b, fa_transaction_headers, fa_additions_b, ap_invoices_all, ap_invoice_distributions_all, ap_invoice_payments_all, ap_checks_all, ap_batches_all, po_headers_all, po_releases_all, po_requisition_headers_all, po_requisition_lines_all, ap_suppliers, ap_supplier_sites_all, hz_cust_accounts, hz_parties, hz_cust_site_uses_all, ra_customer_trx_all, ra_batch_sources_all, ra_batches_all, ar_payment_schedules_all, jtf_rs_salesreps, jtf_rs_resource_extns_vl, ar_adjustments_all, ar_receivable_applications_all, ar_cash_receipts_all, ar_cash_receipt_history_all, ar_batches_all, pa_projects_all, pa_tasks, pa_budget_versions, pa_draft_revenues_all, pa_agreements_all, pa_expenditure_items_all, pa_expenditures_all, pa_expenditure_types, per_all_people_f, pay_assignment_actions, per_all_assignments_f, rcv_transactions, rcv_shipment_headers, rcv_shipment_lines, wip_transactions, wip_entities, bom_departments, bom_resources, gmf_xla_extract_headers, mtl_material_transactions, mtl_system_items_vl, mtl_parameters, mtl_transaction_types, mtl_txn_source_types, financials_system_params_all, cst_write_offs, po_distributions_all, oe_order_lines_all, oe_order_headers_all, gl_balances, fnd_lookup_values_vl

Report Categories

Enginatics

GL Account Distribution Analysis, GL Account Analysis (Drilldown) (with inventory and WIP), GL Account Analysis 11g, GL Account Analysis (Drilldown), GL Journals 11i, GL Journals, GL Journals (Drilldown) 11g, GL Journals (Drilldown), AP Invoices and Lines

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 GL Account Analysis - Pivot by Account Hierarchy 17-Jan-2023 194056.xlsx
Blitz Report™ XML Import GL_Account_Analysis.xml
SQL Source GL Account Analysis.sql
Full Report 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.

The Solution

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

Technical Architecture

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

Parameters & Filtering

Performance & Optimization

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.

© Enginatics GmbH