FSG Drilldown Diagnostic – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Troubleshooting report for FSG journal drilldown issues. Checks gl_balances, posted/unposted journals, period status, account properties, and flex value security for a given account and period. Use the GL segment parameters to filter to the specific account combination that is not drilling down.

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, GL_SEGMENT1, GL_SEGMENT2, GL_SEGMENT3, GL_SEGMENT4, GL_SEGMENT5, GL_SEGMENT6, GL_SEGMENT7, GL_SEGMENT8, GL_SEGMENT9

Oracle EBS Tables Used

gl_code_combinations_kfv, gl_ledgers, gl_balances, gl_je_headers, gl_je_lines, gl_period_statuses, gl_periods, gl_code_combinations

Report Categories

Enginatics

AP Accounted Invoice Aging, GL Balance Drilldown, GL Journals, GL Account Distribution Analysis, GL Account Analysis, GL Code Combinations

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 FSG_Drilldown_Diagnostic.xml
SQL Source FSG Drilldown Diagnostic.sql
Full Report on Enginatics www.enginatics.com/reports/fsg-drilldown-diagnostic/

Case Study & Technical Analysis: FSG Drilldown Diagnostic

Executive Summary

The FSG Drilldown Diagnostic is a troubleshooting report for the General Ledger financial statement generator (FSG). When a row in an FSG report cannot be drilled down to its journals, the report shows, for one account and period, every check that the drilldown depends on. It is written for General Ledger functional consultants and finance systems administrators who must find out why a balance does not open to its journal lines. The report returns eight categories of evidence: GL balances, the balances matching the drilldown filter, posted and unposted journal totals, period status, account properties, flexfield security and the previous period baseline. It sits in the General Ledger module.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. GL_LEDGERS is resolved from the ledger name, and its chart of accounts identifier restricts the account combinations.
  2. GL_BALANCES joins to the account combinations on the code combination identifier and to the ledger on the ledger identifier.
  3. GL_JE_HEADERS joins to GL_JE_LINES on the journal header identifier, and the lines join to the account combinations on the code combination identifier.
  4. GL_PERIOD_STATUSES joins to the ledger and period name, and GL_PERIODS joins on the period set and period type for the dates.
  5. The previous period is found by a subquery that compares period year and period number for the same period set and type.
  6. Each check is a separate branch of a union all, and the branches are sorted by check type and account.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why does a financial statement row not drill down to journals in General Ledger? A: Run FSG Drilldown Diagnostic for the ledger, period and account, and compare the balance rows with the drilldown filter rows to see which condition removes the balance.

Q: How do I tell whether a missing drilldown is caused by security? A: Check the Security Access check, which reports the validate access result and whether flexfield value security applies to the user.

Q: Why are journals counted separately from balances? A: Balances are stored summary records, while the journal checks count posted and unposted lines directly, so the team can see whether journals exist that the balance does not yet reflect.

© Enginatics GmbH