GL Balance (pivot) – 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

Summary GL report including one line per account segment level, including product code, with opening balance, total transaction amount per month for each period selected, and closing balance (trial balance).

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 Category, Period, Show Full Year, Show Company, Show Account, Show Cost Center, Show Intercompany, Show All Segments, Summary Template, Show Account Type, Account Type, 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, Revaluation Currency, Revaluation Conversion Type, Balance Type, Budget Name, Encumbrance Type, Exclude Inactive, Batch, Journal

Oracle EBS Tables Used

gl_daily_conversion_types, gl_daily_rates, gl_ledgers, gl_period_statuses, gl_balances, gl_code_combinations_kfv, fnd_lookup_values_vl

Report Categories

Enginatics

GL Balance by Account Hierarchy, GL Account Analysis, GL Balance, GL Account Distribution Analysis, AP Accounted Invoice Aging, GL Journals, 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 Balance (pivot) 04-Aug-2021 053428.xlsx
Blitz Report™ XML Import GL_Balance_pivot.xml
SQL Source GL Balance (pivot).sql
Full Report on Enginatics www.enginatics.com/reports/gl-balance-pivot/

GL Balance (pivot) - Case Study & Technical Analysis

Executive Summary

The GL Balance (pivot) report is an advanced analytical tool designed to present General Ledger data in a pivot-friendly format. Unlike standard list reports, this solution is optimized for trend analysis and cross-tabulation, displaying one line per account segment level with columns for opening balance, monthly transaction totals, and closing balances. It empowers finance professionals to perform dynamic analysis directly in Excel, facilitating rapid identification of trends, variances, and anomalies across periods.

Business Challenge

Traditional financial reports often present data in a static, row-based format that is difficult to analyze over time.

Solution

The GL Balance (pivot) report transforms GL data into a matrix format, ideal for pivot tables. It aggregates transaction amounts per month for the selected period range, providing a horizontal view of financial activity.

Key Features:

Technical Architecture

This report utilizes a pivot logic within the SQL query to transpose period rows into columns, leveraging the GL_BALANCES table as the core data source.

Key Tables and Views

Core Logic

  1. Data Selection: Selects relevant balances from GL_BALANCES based on the Ledger, Currency, and Period range.
  2. Grouping: Aggregates data based on the user-selected segments (e.g., GL_SEGMENT1, GL_SEGMENT2).
  3. Pivoting: The SQL uses conditional aggregation (e.g., SUM(CASE WHEN period_name = 'Jan-23' THEN period_net_dr - period_net_cr ELSE 0 END)) to create separate columns for each month’s activity.
  4. Balance Calculation:
    • Opening Balance: Sum of balances prior to the start period.
    • Closing Balance: Opening Balance + Sum of all monthly activities in the range.

Business Impact

© Enginatics GmbH