Search Results gl_archive_daily_balances_pk

Overview

The GL_ARCHIVE_DAILY_BALANCES table is a specialized data structure within Oracle E-Business Suite's General Ledger (GL) module, designed to store archived daily aggregate balances. Its primary role is to serve as a historical repository for summarized financial data, facilitating performance optimization and long-term data retention strategies. By archiving daily-level balances, this table enables organizations to maintain detailed historical financial information without overburdening the primary transactional tables, thereby supporting efficient period-end closing, historical trend analysis, and regulatory compliance. The metadata indicates this table is "Not implemented in this database," suggesting it may represent a predefined but optionally deployed object within the EBS data model, potentially activated for specific archival or reporting functionalities.

Key Information Stored

The table's structure is centered on the core dimensions required to uniquely identify a financial balance at a daily granularity. Its composite primary key (GL_ARCHIVE_DAILY_BALANCES_PK) highlights the essential attributes: SET_OF_BOOKS_ID (the ledger), CODE_COMBINATION_ID (the accounting flexfield), CURRENCY_CODE, CURRENCY_TYPE, ACTUAL_FLAG (to distinguish actual, budget, or encumbrance data), PERIOD_NAME, and CONVERTED_FROM_CURRENCY. This key ensures a unique record for each combination of ledger, account, currency, period, and balance type. Additional foreign key columns, such as PERIOD_TYPE and TEMPLATE_ID, link to related setup tables, indicating the table can store balances for different period types and potentially for data generated from specific summary templates.

Common Use Cases and Queries

This table is primarily leveraged for historical financial reporting and analysis where daily balance detail is required from archived periods. A common use case is generating trend reports that analyze account fluctuations on a daily basis across closed fiscal years, without querying the voluminous GL_BALANCES or GL_JE_LINES tables. Sample queries often involve joining to GL_CODE_COMBINATIONS to get account segment values and to GL_PERIODS to resolve period dates. For example, to retrieve archived daily actual balances for a specific account range in a prior period, one might use a SQL pattern such as:

• SELECT gab.period_name, gab.currency_code, gab.balance_column, gcc.concatenated_segments FROM gl_archive_daily_balances gab, gl_code_combinations gcc WHERE gab.code_combination_id = gcc.code_combination_id AND gab.set_of_books_id = :ledger_id AND gab.actual_flag = 'A' AND gab.period_name = 'JAN-2023' AND gcc.segment1 BETWEEN '01' AND '02';

Its utility is pronounced in environments with mandatory data retention policies or where purging old transactional data is necessary while preserving summarized daily history.

Related Objects

The GL_ARCHIVE_DAILY_BALANCES table maintains defined foreign key relationships with several core General Ledger setup and transactional tables, as documented in the ETRM metadata. These relationships are critical for ensuring referential integrity and for constructing accurate joins in reports.

• GL_CODE_COMBINATIONS: Joined via CODE_COMBINATION_ID to retrieve the account combination segments and descriptive information.
• GL_SETS_OF_BOOKS: Joined via SET_OF_BOOKS_ID to link to the ledger definition.
• GL_PERIOD_TYPES: Joined via PERIOD_TYPE to validate the period classification (e.g., Month, Quarter).
• GL_SUMMARY_TEMPLATES: Joined via TEMPLATE_ID, indicating balances may be archived in relation to a specific summary account template.
• FND_CURRENCIES: Joined via CURRENCY_CODE to obtain currency details.

These relationships position the table as an integral component within the GL schema, linking archived summary data back to its fundamental master and setup data.