Results for “glbv_gl_average_balances”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

GLBV_GL_AVERAGE_BALANCES is a General Ledger reporting view shipped within the Oracle E-Business Suite (12.1.1 and 12.2.2) that exposes average balance information recorded against the daily balances tables used by the Average Balance Processing feature set. It is part of the GLBV ("General Ledger Balances View") family of seeded objects, which are intended to serve as a stable interface for balances data when the physical storage and column layout of the balances tables differ between releases and between the consolidated and distributed (GL_BC_PACKETS-based) storage models. The view does not itself own or persist data; it projects rows from the underlying daily balance entity and derives a per-day average balance value by decoding the daily slot position within the accounting period.

The appearance of "end_of_day" in this object is directly attributable to the END_OF_DAY1 through END_OF_DAYn columns present in the underlying daily balance table. These columns hold day-indexed balances, and the view's SELECT list references them within a large DECODE that selects the correct day column based on the offset between the accounting date and the period start date.

Underlying Base Objects

The ETRM metadata records no explicitly documented base objects for this view in the 12.2.2 repository. The view text, however, makes the dependency chain clear. The driving table is aliased DB and is the daily balances table (GL_DAILY_BALANCES in the standard, non-distributed model). It is joined to the ledger definition (aliased LGR, exposing LEDGER_ID and NAME), to the accounting period definition (aliased DPM, supplying PERIOD_NAME and ACCOUNTING_DATE), and to lookup-based descriptions for currency type and summary flag (aliased LK and the GL_LOOKUPS-driven summary meaning). The CODE_COMBINATION_ID column is exposed with inline flexfield and lookup descriptors ('_KF:SQLGL:GL#:GCC' and '_LA:...'), which instruct the reporting tool to resolve the account combination and the summary flag meaning at runtime rather than storing them on the view.

Key Columns

• LEDGER_ID / NAME — the ledger whose balances are being reported, joined from the ledger definition.
• CODE_COMBINATION_ID — the accounting flexfield combination; the embedded flexfield descriptor allows the reporting layer to present the concatenated account.
• CURRENCY_CODE / MEANING — the entered or ledger currency of the balance, with the lookup meaning resolved via GL_LOOKUPS.
• CONVERTED_FROM_CURRENCY — populated when the balance was translated from another currency.
• PERIOD_NAME / ACCOUNTING_DATE — the accounting period and the specific date to which the returned balance corresponds.
• Balance amount — an unnamed ROUND expression that returns the day's average balance. For "O" (entered) and "T" (translated) currency types it returns the day-indexed END_OF_DAYn value directly; for the aggregate currency type it computes the difference between consecutive PERIOD_AGGREGATEn columns, yielding the incremental day value. The DECODE on TRUNC(ACCOUNTING_DATE) - TRUNC(PERIOD_START_DATE)+1 selects which of the thirteen day slots applies.

Common Use Cases and Queries

This view is typically used when average balance reporting must reflect end-of-day values as captured in the daily balances table, rather than period-end only. Because the day selection is computed from the accounting date offset, one row is returned per account/currency/day combination.

• Reconciling average balances to daily balances for a given ledger and period.
• Producing end-of-day average balance extracts for downstream reconciliation or regulatory reporting.
• Feeding a custom report that needs per-day balances without querying GL_DAILY_BALANCES directly, thereby insulating the SQL from storage-model differences.

A representative query pattern follows:

• SELECT ledger_id, name, code_combination_id, currency_code, period_name, accounting_date, <balance expression> FROM glbv_gl_average_balances WHERE ledger_id = :ledger AND period_name = :period AND accounting_date = :day ORDER BY code_combination_id;

Because the day-slot logic is embedded in the view definition, callers filtering on ACCOUNTING_DATE receive the correct END_OF_DAYn value automatically for that date, eliminating the need to know which numbered column applies.