GL Account Analysis (Drilldown) (with inventory and WIP) – 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. This also has inventory and WIP details.

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, Journal Source, Journal Category, Batch, Status, Batch ID, Journal, Journal Header ID, Journal Line Num, Concatenated Segments, Balance Type, Budget Name, Encumbrance Type, Show Segments with Descriptions, Restrict CCIDs through GTT, Restrict JHI through GTT, Journal Line DFF Display

Oracle EBS Tables Used

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, xla_distribution_links, ra_cust_trx_line_gl_dist_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, 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, zx_rates_b, fa_transaction_headers, fa_additions_b, ap_invoices_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, 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, ap_invoice_distributions_all, oe_order_lines_all, oe_order_headers_all

Report Categories

Enginatics, R12 only

GL Account Analysis, GL Account Distribution Analysis, GL Account Analysis (Drilldown), GL Account Analysis 11g, INV Item Upload 11i, INV Items, INV Item Upload, GL Journals 11i, GL Journals (Drilldown)

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_with_inventory_and_WIP.xml
SQL Source GL Account Analysis (Drilldown) (with inventory and WIP).sql
Full Report on Enginatics www.enginatics.com/reports/gl-account-analysis-drilldown-with-inventory-and-wip/

GL Account Analysis (Drilldown) (with inventory and WIP) - Case Study & Technical Analysis

Executive Summary

The GL Account Analysis (Drilldown) (with inventory and WIP) report is an advanced extension of the standard drilldown capability, specifically enhanced for manufacturing and supply chain intensive organizations. In addition to the standard financial drilldown features, this report provides deep visibility into Inventory and Work in Process (WIP) transactions.

It is designed to support complex cost accounting analysis by linking General Ledger balances not just to subledger accounting entries, but all the way down to specific material transactions, job operations, and sales orders. This level of detail is essential for reconciling inventory value, analyzing manufacturing variances, and auditing cost of goods sold.

Business Challenge

For manufacturing companies, the link between the General Ledger and the shop floor is often opaque. Standard reports may show a “Material Overhead” or “WIP Variance” entry in the GL, but determining which specific job or material movement caused it can be difficult. Common pain points include:

The Solution

This report bridges the gap between Finance and Operations by incorporating specific logic to decode Inventory and WIP transactions. It provides a unified view that speaks the language of both the accountant (Debits, Credits, Accounts) and the operations manager (Item Numbers, Job Names, Transaction Types).

Key Features:

Technical Architecture

This report builds upon the standard GL-SLA architecture but adds specialized joins for the Inventory (Application ID 401) and WIP (Application ID 706) applications.

Key Tables Involved:

Critical Joins:

The report uses the SOURCE_DISTRIBUTION_ID_NUM_1 (and similar columns) in XLA_DISTRIBUTION_LINKS to join to MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID or WIP_TRANSACTIONS.TRANSACTION_ID. This precise linking ensures that the financial impact is correctly attributed to the physical event.

Parameters & Filtering

The report supports a comprehensive set of parameters to isolate specific cost activities:

Performance & Optimization

Querying high-volume inventory tables requires careful optimization:

FAQ

Q: Does this report show the specific item that was sold? A: Yes, for Cost of Goods Sold (COGS) entries, the report links back to the material transaction to display the Item Number and Description.

Q: Can I see which Discrete Job generated a variance? A: Yes, for WIP variance accounts, the report displays the specific Job Name (WIP Entity Name) associated with the entry.

Q: Why do I see multiple lines for a single inventory transaction? A: A single material movement can generate multiple accounting entries (e.g., Inventory Valuation, Deferred COGS, COGS). This report displays each accounting line to provide a complete financial picture.

© Enginatics GmbH