Results for “end_of_day1”

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

Table: GL_DAILY_BALANCES — Summary

Overview

GL_DAILY_BALANCES is a General Ledger (GL) schema table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores daily aggregate balances for both detail and summary accounts. It is a denormalized balances repository: rather than holding one row per journal line, it consolidates activity and ending balances across a period, quarter, and year at the level of a ledger, accounting flexfield combination, currency, and period. It is maintained by the General Ledger program that rolls up subledger and journal activity into aggregated structures, and it is a key enabler for balance inquiries, FSG (Financial Statement Generator) reporting, and reconciliation queries that would otherwise scan GL_JE_LINES and GL_JE_HEADERS across millions of rows.

The documented ETRM Data Vault classification is a heuristic derived from the foreign-key graph; for GL_DAILY_BALANCES this resolves to a link classification. In modeling terms, the table behaves like a multi-dimensional fact/link structure: its composite key joins ledger, account, currency, and period dimensions, with aggregate measures (PERIOD_AGGREGATEn, QUARTER_AGGREGATEn, YEAR_AGGREGATEn, END_OF_DAYn) attached as descriptive attributes. The table is functionally a period- and account-level fact table linking GL_CODE_COMBINATIONS, GL_PERIOD_TYPES, and FND_CURRENCIES.

Key Information Stored

Although the documented physical schema contains 163 columns, the semantically significant columns are the key and dimensional columns plus the aggregate measures.

• LEDGER_ID — the ledger (set of books) to which the balance belongs; part of the primary key.
• CODE_COMBINATION_ID — the accounting flexfield combination; part of the primary key (and the leading column of the GL_DAILY_BALANCES_U1 unique index).
• CURRENCY_CODE — currency of the stored amounts; part of the primary key and FK to FND_CURRENCIES.
• CURRENCY_TYPE — indicates entered vs. converted balances, allowing both to coexist for the same combination.
• ACTUAL_FLAG — distinguishes actual, budget, and encumbrance balances in the same table.
• PERIOD_NAME / PERIOD_TYPE / PERIOD_YEAR / PERIOD_NUM — the accounting period and its type/sequence; PERIOD_TYPE is an FK to GL_PERIOD_TYPES.
• CONVERTED_FROM_CURRENCY — the source currency for converted balances; part of the primary key.
• PERIOD_START_DATE, PERIOD_END_DATE, QUARTER_START_DATE, YEAR_START_DATE — boundary dates used for time-based reporting and rollforward.
• TEMPLATE_ID — FK to GL_SUMMARY_TEMPLATES identifying the summary template for summarized accounts.
• PERIOD_AGGREGATE1 … PERIOD_AGGREGATE35, QUARTER_AGGREGATE1 … QUARTER_AGGREGATE35, YEAR_AGGREGATE1 … YEAR_AGGREGATE35 — up to 35 aggregate measures per period, quarter, and year for the combination.
• END_OF_DAY1 … END_OF_DAY35 — daily ending balances across the accounting period.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.

The surrogate primary key is GL_DAILY_BALANCES_PK over (LEDGER_ID, CODE_COMBINATION_ID, CURRENCY_CODE, CURRENCY_TYPE, ACTUAL_FLAG, PERIOD_NAME, CONVERTED_FROM_CURRENCY). A secondary unique index, GL_DAILY_BALANCES_U1, begins with CODE_COMBINATION_ID, making the code combination a leading business-key candidate for account-driven lookups.

Common Use Cases and Queries

The table is most often queried for month-end reporting, account balance inquiry, and daily balance tracking where the summary-level precision of GL_BALANCES is insufficient. A typical pattern retrieves period activity for a set of accounts:

SELECT gdb.CODE_COMBINATION_ID, gdb.PERIOD_NAME,
       gdb.CURRENCY_CODE, gdb.PERIOD_AGGREGATE1
  FROM GL.GL_DAILY_BALANCES gdb
 WHERE gdb.LEDGER_ID = :p_ledger_id
   AND gdb.PERIOD_NAME = :p_period_name
   AND gdb.ACTUAL_FLAG = 'A'
   AND gdb.CODE_COMBINATION_ID = :p_ccid;

Joining to GL_CODE_COMBINATIONS enables account-level reporting by segment, and joining to GL_PERIOD_TYPES restricts results to a period set. Daily trend analysis uses the END_OF_DAYn columns, while quarter and year rollups use the QUARTER_AGGREGATEn and YEAR_AGGREGATEn columns. FSG and ad hoc reconciliation reports frequently drive this table through the GL_DAILY_BALANCES_U1 index on CODE_COMBINATION_ID.

Related Objects

The most significant related objects for GL_DAILY_BALANCES, based on the documented FK/PK relationships, are:

• GL_CODE_COMBINATIONS — joined via GL_DAILY_BALANCES.CODE_COMBINATION_ID = GL_CODE_COMBINATIONS.CODE_COMBINATION_ID.
• GL_PERIOD_TYPES — joined via GL_DAILY_BALANCES.PERIOD_TYPE = GL_PERIOD_TYPES.PERIOD_TYPE.
• GL_SUMMARY_TEMPLATES — joined via GL_DAILY_BALANCES.TEMPLATE_ID = GL_SUMMARY_TEMPLATES.TEMPLATE_ID.
• FND_CURRENCIES — joined via GL_DAILY_BALANCES.CURRENCY_CODE = FND_CURRENCIES.CURRENCY_CODE.
• GL_LEDGERS / GL_SETS_OF_BOOKS — referenced by LEDGER_ID for ledger context.
• GL_BALANCES — the higher-level summary balance table; GL_DAILY_BALANCES provides the finer-grained daily detail that rolls into it.

Together these relationships make GL_DAILY_BALANCES a frequently joined object for GL reporting, inquiry, and reconciliation workloads in both EBS 12.1.1 and 12.2.2.