Search Results xtr_reconciliation_passes




Overview
Explore in AI Chat

Overview

XTR_RECONCILIATION_PASSES is a Treasury (XTR) module configuration table that stores the reconciliation criteria applied by Oracle Cash Management and Treasury when automatically matching bank statement lines to system transactions. Each row in the table defines a "reconciliation pass" — an ordered rule set that specifies which transaction attribute is compared, what comparison logic is used, and how the resulting match is categorized. The table therefore drives the automated reconciliation engine that clears open transactions against imported bank statements, and it is foundational to the accuracy of cash positioning, bank account balancing, and Treasury cash forecasting.

From a Data Vault modeling perspective, the mined relationship data classifies this object heuristically as standalone. The absence of foreign-key relationships to other tables suggests it is best modeled as a hub (a reference/lookup entity) rather than a link or satellite, since it holds independent, slowly changing configuration values keyed by a deterministic business code. This is a modeling suggestion, not a documented fact; the object contains no dependent child tables in the mined FK structure.

Key Information Stored

The table is documented in ETRM 12.2.2 with owner XTR and four columns. The primary key XTR_RECONCILIATION_PASSES_PK is a composite key comprising three of these columns:

  • RECONCILED_PASS_CODE — Part of the composite primary key; a short code identifying the reconciliation pass (for example, a sequence number or named pass such as "1", "2", or a descriptive identifier).
  • RECONCILE_DETAIL — Part of the composite primary key; identifies the specific detail or grouping to which the pass applies, effectively segmenting the criteria within a pass.
  • RECONCILE_ON_COLUMN — Part of the composite primary key; the transaction or statement attribute (column) against which the reconciliation comparison is performed, such as amount, currency, or reference.
  • PASS_DESCRIPTION — A non-key descriptive label explaining the purpose or behavior of the pass, used for user reference and reporting.

The composite primary key (RECONCILED_PASS_CODE, RECONCILE_DETAIL, RECONCILE_ON_COLUMN) behaves as the business key; the table has no separate surrogate key column documented. This three-part key enforces uniqueness across the pass definition, ensuring that no single attribute is reconciled twice within the same pass and detail combination.

Common Use Cases and Queries

Typical scenarios include reviewing or auditing automatic reconciliation criteria, diagnosing why a bank statement line failed to match, and reproducing reconciliation results in reporting. A representative query lists all defined passes and the columns they reconcile on:

  • SELECT RECONCILED_PASS_CODE, RECONCILE_DETAIL, RECONCILE_ON_COLUMN, PASS_DESCRIPTION FROM XTR.XTR_RECONCILIATION_PASSES ORDER BY RECONCILED_PASS_CODE, RECONCILE_DETAIL;
  • SELECT PASS_DESCRIPTION FROM XTR.XTR_RECONCILIATION_PASSES WHERE RECONCILE_ON_COLUMN = :attribute; — identifies which pass reconciles a given transaction attribute.
  • Configuration validation: count passes per code to confirm the expected number of criteria is registered.
  • Reporting joins: the table can be joined to Cash Management reconciliation results to explain match outcomes in custom dashboards.

Because the object is small and configuration-oriented, queries against it are inexpensive and suitable for inclusion in reconciliation troubleshooting reports and Treasury setup documentation.

Related Objects

The mined FK data classifies XTR_RECONCILIATION_PASSES as standalone, meaning it is not formally linked by foreign keys to other tables. Its functional dependencies, however, lie within the Cash Management and Treasury reconciliation ecosystem. Significant related components include Cash Management bank statement and reconciliation tables (such as CE_STATEMENT_LINES and CE_STATEMENT_RECONCILS) that consume the pass criteria, and the Treasury XTR reconciliation and cash positioning tables that reference pass codes for matching. Reconciliation results and exception records produced by applying these passes are also logically dependent on this configuration. Because no FK relationships are documented, joins with these objects are enforced at the application layer rather than by database constraints, and any custom SQL should join on RECONCILED_PASS_CODE and RECONCILE_ON_COLUMN rather than assuming a referential constraint exists.