Search Results xtr_trial_balance_v

Overview

XTR_TRIAL_BALANCE_V is a reporting view in the Oracle E-Business Suite Treasury (XTR) module. It exposes summarized general ledger balance information maintained by Treasury for the purpose of trial balance reporting and reconciliation. The view consolidates rows from the Treasury general ledger summary table and presents them at a level suitable for financial reporting, transfer processing, and downstream integration with Oracle General Ledger.

Because the view is defined strictly as a SELECT statement over existing summary data, it holds no independent data of its own. Its principal role is to standardize the shape of Treasury trial balance data so that concurrent programs, reports, and interfaces can query a consistent, aggregated result set rather than navigating the base summary table directly. A query for the GL_TRANSFER_DATE column — a distinguishing field in this view — typically targets users seeking to identify the accounting date on which Treasury balances were transferred to General Ledger. This is central to reconciliation between Treasury subledger balances and the General Ledger.

Underlying Base Objects

The view is defined over a single documented base object: XTR_GENERAL_LEDGER_SUMMARY. That table stores balance figures, carried-forward amounts, and the transfer date that links Treasury activity to the GL interface. The view applies aggregation and a UNION ALL operation against this table.

The UNION ALL combines two grouped result sets. The first selects balances grouped by CODE_COMBINATION_ID, COMPANY_CODE, GL_TRANSFER_DATE, BALANCE_CFWD_HCE_DR_CR, and CURRENCY, and returns the actual currency value. The second selects the same groupings except CURRENCY, substitutes a NULL currency, and re-aggregates. This design produces a currency-level detail set alongside a combined (all-currency) set in one result, enabling consumers to obtain either drill-down figures or an aggregate without issuing two separate queries.

The ETRM metadata records no other referenced base objects, and the note "Not implemented in this database" indicates the view may be delivered as a definition object that is not necessarily compiled into every environment.

Key Columns

• CODE_COMBINATION_ID — Identifier of the General Ledger accounting flexfield combination to which the balances belong.
• COMPANY_CODE — Company or legal entity identifier associated with the Treasury balance.
• GL_TRANSFER_DATE — The accounting date on which balances were (or will be) transferred to the General Ledger. This is the field most closely associated with reconciliation and transfer monitoring.
• BALANCE_CFWD_HCE_DR_CR — Indicator of whether the carried-forward hard-currency-equivalent balance represents a debit or credit.
• CURRENCY — Transaction currency of the balance. NULL in the aggregated UNION ALL branch, indicating the all-currency total.
• BALANCE_CFWD — Sum of carried-forward balances, aggregated by the grouping columns.
• HCE_BALANCE_CFWD — Sum of carried-forward balances expressed in the hard currency equivalent.

Common Use Cases and Queries

Typical uses include reconciling Treasury balances to General Ledger, reviewing balances by transfer date, and feeding reporting extracts.

To inspect balances for a specific General Ledger transfer date:

• SELECT company_code, code_combination_id, currency, balance_cfwd, hce_balance_cfwd FROM xtr_trial_balance_v WHERE gl_transfer_date = :p_transfer_date ORDER BY company_code, code_combination_id;

To obtain the hard-currency aggregate per company across all currencies (rows where currency is NULL):

• SELECT company_code, SUM(hce_balance_cfwd) FROM xtr_trial_balance_v WHERE currency IS NULL GROUP BY company_code;

To identify transfer dates present in the data set for audit or period-close purposes:

• SELECT DISTINCT gl_transfer_date FROM xtr_trial_balance_v ORDER BY gl_transfer_date;

Because the view only aggregates existing summary data, query performance is largely determined by indexing on XTR_GENERAL_LEDGER_SUMMARY, particularly on GL_TRANSFER_DATE, COMPANY_CODE, and CODE_COMBINATION_ID.