Search Results xla_control_balances

Overview

XLA_CONTROL_BALANCES is a Subledger Accounting (XLA) table in the Oracle E-Business Suite database, owned by the XLA schema and present in both release 12.1.1 and 12.2.2. Its documented purpose is to store the balances for each third party control account. In the Subledger Accounting architecture, control balances provide a party-level view of activity and balances held against control accounts, enabling reconciliation between subledger transactions and the corresponding General Ledger balances at the granularity of a specific third party (customer, supplier, or other party) and party site, within a given ledger, accounting period, and accounting flexfield combination.

Rows are keyed by the combination of application, ledger, code combination, party type, party, party site, and period, which is the documented unique index XLA_CONTROL_BALANCES_U1 and the basis of the primary key constraint XLA_CONTROL_BALANCES_PK. From a dimensional modeling perspective, the mined relationship structure suggests a link classification: the table sits between a set of reference or dimension-like entities (ledger, accounting flexfield combination, and party identifiers) and carries period-scoped balance measure columns. It therefore behaves as an associative, transaction-like table rather than a pure descriptive hub or a slowly changing satellite, although the modeling classification is offered only as a heuristic suggestion.

Key Information Stored

The table is documented with 28 columns. The most significant are grouped as follows:

• Business key columns: APPLICATION_ID, LEDGER_ID, CODE_COMBINATION_ID, PARTY_TYPE_CODE, PARTY_ID, PARTY_SITE_ID, and PERIOD_NAME together form the unique business key (XLA_CONTROL_BALANCES_U1) and the primary key constraint XLA_CONTROL_BALANCES_PK. These identify the ledger of record, the accounting flexfield combination acting as the control account, the third party and its site, and the accounting period for which the balance is held.
• Actual balances: BEGINNING_BALANCE_DR, BEGINNING_BALANCE_CR, PERIOD_BALANCE_DR, and PERIOD_BALANCE_CR hold the opening and period activity amounts on the debit and credit sides for the third party control account.
• Draft balances: DRAFT_BEGINNING_BALANCE_DR, DRAFT_BEGINNING_BALANCE_CR, PERIOD_DRAFT_BALANCE_DR, and PERIOD_DRAFT_BALANCE_CR store unposted or provisional versions of the same amounts, allowing the posting process to stage balance movement before it is finalized.
• Period control flags: INITIAL_BALANCE_FLAG, FIRST_PERIOD_FLAG, PERIOD_YEAR, and EFFECTIVE_PERIOD_NUM support period roll-forward logic, identifying the first period of activity and tracking the chronological position of each period.
• Standard audit and concurrency columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PROGRAM_UPDATE_DATE, PROGRAM_APPLICATION_ID, PROGRAM_ID, and REQUEST_ID.

Common Use Cases and Queries

The principal use case is third party control account reconciliation and reporting. Because the table carries both beginning and period movements split into debit and credit, it supports period-over-period roll-forward analysis and drill-down from a control account total to individual parties and sites. A representative query pattern filters on the ledger, period, and code combination and aggregates by party type:

• Control account roll-forward: select PERIOD_NAME, sum(PERIOD_BALANCE_DR) minus sum(PERIOD_BALANCE_CR) grouped by LEDGER_ID and CODE_COMBINATION_ID.
• Party-level detail for a specific account and period: filter on LEDGER_ID, CODE_COMBINATION_ID, PARTY_TYPE_CODE, and PERIOD_NAME, then order by PARTY_ID and PARTY_SITE_ID.
• Draft versus final comparison: compare PERIOD_BALANCE_DR/PERIOD_BALANCE_CR against PERIOD_DRAFT_BALANCE_DR/PERIOD_DRAFT_BALANCE_CR to identify unposted activity.
• Period sequence analysis: use PERIOD_YEAR, EFFECTIVE_PERIOD_NUM, FIRST_PERIOD_FLAG, and INITIAL_BALANCE_FLAG to determine how far balances have been rolled forward.

Audit and diagnostic queries commonly join to GL_LEDGERS and GL_CODE_COMBINATIONS to translate the surrogate identifiers into ledger names and account strings, and use the CREATED_BY and REQUEST_ID columns to trace which concurrent program populated a given set of balances.

Related Objects

The documented foreign keys establish the reference relationships for this table:

• GL_LEDGERS — joined on XLA_CONTROL_BALANCES.LEDGER_ID = GL_LEDGERS.LEDGER_ID; provides the ledger definition (name, chart of accounts, currency).
• GL_CODE_COMBINATIONS — joined on XLA_CONTROL_BALANCES.CODE_COMBINATION_ID = GL_CODE_COMBINATIONS.CODE_COMBINATION_ID; supplies the accounting flexfield combination that constitutes the control account.

Beyond the documented foreign keys, other subledger accounting objects operate in the same balance and reconciliation processing flow in a typical implementation, including the XLA_* balances and journal entry line tables populated by the Create Accounting and Transfer Journal Entries to GL programs, and the GL balance tables used as the counterpart for reconciliation. Functionally, the table is consumed by Subledger Accounting balance inquiries and by concurrent programs that maintain control account balances for third parties. Any reporting or reconciliation solution should treat the seven business key columns as the join criteria to the ledger and code combination dimensions and should not rely on a single surrogate identifier, since the documented primary key is composite.