Search Results amount_code

Overview

XTR_RECONCILIATION_MATRIX_V is a Treasury (XTR) module view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2, and is delivered in VALID status. It exposes the reconciliation matrix that drives the bank statement reconciliation engine used by Treasury cash management and deal management processing. Conceptually, the view presents, for each verification method defined on a reconciliation pass, a flattened set of reconciliation attributes — amount, bank account, date, deal number, deal type, party, portfolio, product, and serial number — indicating how each attribute is matched during the automated reconciliation process.

The view plays a descriptive rather than transactional role. It does not store or produce reconciled data; instead it translates the configured reconciliation rules (passes, sequence order, and cross-reference attributes) into a report-friendly result set. This makes it the reference source for diagnostics, reconciliaton setup verification, and integration extracts where downstream systems need to know which fields are compared and how. A prominent design characteristic is the extensive use of DECODE to pivot row-based configuration into descriptive columns, with the lookup_code values from FND_LOOKUPS supplying the semantic meaning for each reconciliation detail.

Underlying Base Objects

The view is defined over the following documented objects:

• XTR_RECONCILIATION_PASSES_V — the primary source of configuration rows, aliased A in the view text. It supplies VERIFY_METHOD, PASS_CODE, VER_SEQ, PASS_COL, and CROSS.
• XTR_RECONCILIATION_PROCESS_V — the source of the parameter codes (D) that the inner DECODE expressions test against, including AMOUNT_CODE, BANK_ACCT_CODE, DATE_CODE, DEAL_NUM_CODE, DEAL_TYPE_CODE, PARTY_CODE, PORTFOLIO_CODE, PRODUCT_CODE, and SERIAL_NUM_CODE.
• FND_LOOKUPS — the Oracle Application Object Library lookup view, which resolves stored codes to meanings such as MATCH_MEANING, SUMDATE_MEANING, and SUMRANGE_MEANING.
• FND_GLOBAL — the standard EBS package referenced for session context such as USER_ID and RESP_ID.

The nested DECODE chains join these sources so that each pass column is rendered into the correct topical column of the matrix.

Key Columns

• VERIFICATION_METHOD — the matching method assigned to the reconciliation pass.
• RECONCILIATION_PASS_CODE — identifier of the reconciliation pass, sourced from PASS_CODE.
• SEQUENCE_ORDER — evaluation sequence (VER_SEQ) in which passes are applied.
• AMOUNT_RECON_DETAIL, BANK_ACCT_RECON_DETAIL, DATE_RECON_DETAIL, DEAL_NUM_RECON_DETAIL, DEAL_TYPE_RECON_DETAIL, PARTY_RECON_DETAIL, PORTFOLIO_RECON_DETAIL, PRODUCT_RECON_DETAIL, SERIAL_NUM_RECON_DETAIL — one column per reconciliable attribute. Each is populated only when PASS_COL matches the corresponding parameter code; the inner DECODE then resolves the cross-match code to a meaningful description, defaulting to the raw CROSS value.

Because all detail columns derive from the same PASS_COL/CROSS pair, only one detail column is typically non-null per row — the view behaves as a sparse matrix.

Common Use Cases and Queries

Typical scenarios include verifying reconciliation setup before month-end close, troubleshooting why a pass did not match, and feeding reconciliation rules into audit reports.

• List all passes and their sequence order for a verification method.
• Identify which attributes are compared for a given pass.
• Locate passes that reference a specific cross-match code (the lookup_code the user searched for).

Sample SQL:

SELECT verification_method,
       reconciliation_pass_code,
       sequence_order,
       amount_recon_detail,
       bank_acct_recon_detail,
       date_recon_detail,
       deal_num_recon_detail
FROM   apps.xtr_reconciliation_matrix_v
WHERE  verification_method = :p_verify_method
ORDER  BY sequence_order;

SELECT reconciliation_pass_code, sequence_order
FROM   apps.xtr_reconciliation_matrix_v
WHERE  amount_recon_detail IS NOT NULL
   OR  date_recon_detail IS NOT NULL;