GL Balances (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 Balance details per code combination. **

Shows GL balance amounts (Opening Balance, Debit, Credit, Net, Closing Balance) grouped by code combination, with drill-to-journal capability. Supports all balance types: PTD, YTD, QTD, PJTD, FYS, FYE, CTD. The Drill To Journal column allows further drilldown into journal 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, Period, Ledger ID, Amount Type, Currency Code, Currency Type, Balance Type, Budget Name, Encumbrance Type, Concatenated Segments, Code Combination ID, Restrict CCIDs through GTT

Oracle EBS Tables Used

gl_budget_versions, gl_encumbrance_types, gl_ledgers, gl_balances, gl_code_combinations_kfv

Report Categories

Enginatics

GL Balance, GL Balance Drilldown, GL Journals, GL Account Distribution Analysis, GL Account Analysis

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_Balances_Drilldown.xml
SQL Source GL Balances (Drilldown).sql
Full Report on Enginatics www.enginatics.com/reports/gl-balances-drilldown/

Case Study & Technical Analysis: GL Balances (Drilldown)

Executive Summary

The GL Balances (Drilldown) report shows General Ledger balances for each code combination, with opening balance, debit, credit, net and closing balance. It is the balance-level detail behind the GL Financial Statement and Drilldown reports, and it can be used on its own by accountants who want to see which accounts make up a financial statement line. Each row carries a drill-to-journal formula, so the user can move from a balance to the journal lines that produced it. The report supports every balance type, including period-to-date, year-to-date, quarter-to-date, project-to-date, and the financial year start and end balances.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. GL_LEDGERS is joined to GL_BALANCES on the ledger ID, and GL_BALANCES is joined to GL_CODE_COMBINATIONS_KFV on the code combination ID.
  2. The combination’s chart of accounts must match the ledger’s chart of accounts, and only detail accounts are kept.
  3. Template balances are excluded, so only balances from actual ledger postings are returned.
  4. Budget name and encumbrance type are looked up by scalar subquery from the balance’s version and encumbrance type IDs.
  5. An inner aggregation sums the balance amounts by ledger, combination, balance type, actual flag, budget, encumbrance type, currency and drill formula. Rows with all amounts at zero are removed.
  6. The aggregated result is joined back to GL_CODE_COMBINATIONS_KFV to show the concatenated account, and sorted by it.

Parameters & Filtering

Performance & Optimization

FAQ

Q: How do I see the journals behind a balance? A: Use the drill-to-journal formula in the Drill To Journal column. It opens the journal details for that ledger, period, amount type, currency, balance type and code combination. Q: Why does an account appear more than once? A: The report returns a row for each balance type, currency, budget version and encumbrance type that has a non-zero amount for the account. Q: Which balance types can I run? A: The report supports PTD, YTD, QTD, PJTD, FYS, FYE and CTD balances, selected through the Amount Type parameter.

© Enginatics GmbH