GL Account Distribution 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

Detailed GL transaction report with one line per distribution including all segments and subledger data, with amounts in both transaction currency and ledger currency. The report includes VAT tax codes and rates for AR and AP transactions.

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, Tax Rate Code, 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, 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, gl_je_categories_vl, zx_lines, xla_event_types_tl, gl_daily_conversion_types, gl_budget_versions, gl_encumbrance_types, fa_distribution_history, ap_invoice_payments_all, ap_invoices_all, ap_payment_schedules_all, ar_payment_schedules_all, mtl_sales_orders, ra_rules, ra_customer_trx_lines_all, ap_suppliers, hz_cust_accounts, hz_parties, oe_order_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, po_requisition_headers_all, wip_entities, mtl_txn_request_headers, hz_cust_site_uses_all, ap_supplier_sites_all, 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_import_references, xla_ae_lines, xla_ae_headers, xla_events, xla_transaction_entities, xla_distribution_links, gl_daily_rates, zx_rates_b, fa_adjustments, fa_additions_b, ap_batches_all, ap_payment_hist_dists, ap_checks_all, ce_bank_acct_uses_all, ce_bank_accounts, ce_bank_branches_v, ap_invoice_distributions_all, ap_invoice_lines_all, po_distributions_all, po_headers_all, po_releases_all, po_line_locations_all, financials_system_params_all, po_lines_all, hr_operating_units, po_req_distributions_all, ra_customer_trx_all, ra_cust_trx_line_gl_dist_all, ra_batch_sources_all, ra_batches_all, jtf_rs_salesreps, jtf_rs_resource_extns_vl, ar_adjustments_all, ar_cash_receipts_all, ar_cash_receipt_history_all, ar_batches_all, pa_projects_all, pa_tasks, 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_receiving_sub_ledger, rcv_transactions, rcv_shipment_headers, rcv_shipment_lines, wip_transactions, bom_departments, bom_resources, gmf_xla_extract_headers, mtl_material_transactions, mtl_system_items_vl, mtl_parameters, mtl_transaction_types, mtl_txn_source_types, oe_order_headers_all, gl_balances, fnd_lookup_values_vl

Report Categories

Enginatics, R12 only

GL Account Analysis, GL Account Analysis 11g, GL Account Analysis (Drilldown) (with inventory and WIP)

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_Distribution_Analysis.xml
SQL Source GL Account Distribution Analysis.sql
Full Report on Enginatics www.enginatics.com/reports/gl-account-distribution-analysis/

Case Study & Technical Analysis: GL Account Distribution Analysis

Executive Summary

The GL Account Distribution Analysis report produces a detailed General Ledger listing with one line per journal distribution. Each line carries every account segment, the subledger document that created it, and the amounts in transaction currency and ledger currency. For AR and AP transactions it also shows the VAT tax rate code and rate. Financial controllers, period-close teams and auditors use it to trace a ledger balance back to its originating invoice, receipt, payment or inventory transaction. The report runs in Excel through Blitz Report.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. GL_LEDGERS joins to GL_PERIODS by period set, then to GL_JE_HEADERS by ledger and period name, and GL_JE_LINES joins to its header and batch.
  2. Each journal line joins to GL_CODE_COMBINATIONS_KFV, and through GL_IMPORT_REFERENCES to the XLA_AE_LINES rows it created.
  3. The accounting lines join to their headers and events, and XLA_DISTRIBUTION_LINKS connects them to the source distribution.
  4. The source distribution is matched to subledger tables by application ID, so each source is resolved only for its own application.
  5. The result is combined with opening and closing balance rows, and ordered by ledger, account, period, batch, journal and line.

Parameters & Filtering

Performance & Optimization

FAQ

Q: How can I trace a journal line to its invoice or receipt? A: The line links through the accounting event to the subledger, and the invoice, customer transaction, cash receipt, purchase order and receiving columns show the source document.

Q: Why does the report show opening and closing balance rows? A: When balances are requested, the report adds opening and closing balance rows per account and period, taken from the balances table, so the listing ties to the ledger.

Q: Which VAT information is included for AR and AP lines? A: The tax rate code and tax rate of each line are shown, taken from the tax lines of the transaction or from the AP invoice line.

© Enginatics GmbH