Results for “xtr_pay_rec_reconciliation”

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

Overview

The XTR_PAY_REC_RECONCILIATION table resides in the XTR (Treasury) product schema of Oracle E-Business Suite and stores reconciliation information for bank statements that have been successfully transferred into the Treasury module. It functions as a staging and audit repository that captures the outcome of payment and receipt matching against imported bank statement data, preserving the transactional detail required for cash management reconciliation, confirmation tracking, and downstream treasury reporting. The table is populated as part of the bank statement import and reconciliation process, where each successfully processed line is recorded with its reconciliation status, reference identifiers, and monetary values.

From a Data Vault modeling perspective, the mined foreign key structure suggests this object behaves as a satellite. It carries descriptive and status attributes tied to a parent import reference rather than acting as an independent hub or as a pure associative link. This classification is a heuristic recommendation; treat it as a modeling suggestion rather than a definitive architectural designation.

Key Information Stored

The table is documented with 14 columns in the ETRM 12.2.2 schema (owner XTR). The most significant columns and their roles are as follows:

• IMPORT_REFERENCE — The foreign key that links each reconciliation row to its originating record in XTR_IMPORT_TRAILER_DETAILS. This is the primary business-key candidate for joining back to the import process.
• SERIAL_REFERENCE — A sequencing or unique reference used to distinguish individual reconciliation entries; likely participates in a unique index alongside IMPORT_REFERENCE.
• RECONCILED_REFERENCE — The reference identifier of the reconciled transaction (payment, receipt, or statement line).
• RECONCILED_PASS_CODE — A status or pass/fail code indicating the reconciliation result.
• CONFIRMATION_STATUS — The confirmation state of the reconciliation, supporting workflow approvals.
• CONFIRMED_BY / CONFIRMED_ON — The user and timestamp of confirmation, providing an audit trail.
• DEBIT_AMOUNT / CREDIT_AMOUNT — The monetary values reconciled on the debit and credit sides.
• VALUE_DATE — The value date of the statement line, used for interest and cash positioning.
• PARTY_NAME — The counterparty associated with the transaction.
• PARTICULARS — Free-text narrative describing the transaction.
• RECORD_TYPE — Classifies the reconciliation record.
• COMMENTS — Additional remarks captured during reconciliation.

A surrogate primary key is not explicitly enumerated in the provided metadata; IMPORT_REFERENCE paired with SERIAL_REFERENCE typically serves as the composite business key.

Common Use Cases and Queries

Typical applications include reconciling bank statement lines to payments and receipts, auditing confirmation activity, and producing cash position reports. A common query joins reconciliation records to their import trailer detail:

• Identify unreconciled or failed statement lines by filtering on RECONCILED_PASS_CODE and CONFIRMATION_STATUS.
• Audit confirmation activity by selecting CONFIRMED_BY, CONFIRMED_ON, and CONFIRMATION_STATUS.
• Aggregate debit and credit amounts by VALUE_DATE for daily cash positioning.
• Reconcile by party using PARTY_NAME and RECONCILED_REFERENCE.

Sample pattern: SELECT r.IMPORT_REFERENCE, r.SERIAL_REFERENCE, r.RECONCILED_REFERENCE, r.DEBIT_AMOUNT, r.CREDIT_AMOUNT, r.CONFIRMATION_STATUS FROM XTR_PAY_REC_RECONCILIATION r WHERE r.VALUE_DATE = :p_date AND r.CONFIRMATION_STATUS = 'CONFIRMED';

Related Objects

The most significant related objects, based on the documented foreign key relationship, include:

• XTR_IMPORT_TRAILER_DETAILS — Referenced by IMPORT_REFERENCE; the parent table holding import trailer detail.
• XTR_IMPORT_HEADERS and XTR_IMPORT_LINES — Upstream import staging structures feeding reconciliation.
• XTR_BANK_STATEMENT_HEADERS / XTR_BANK_STATEMENT_LINES — Statement data reconciled by this table.
• Cash Management reconciliation views and the Treasury reconciliation concurrent programs that consume this data.

Join columns consistently use IMPORT_REFERENCE to relate reconciliation rows to their import origins.