Results for “ce_222_reconciled_v”

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

Overview

CE_222_RECONCILED_V is a Cash Management (CE) view owned by the APPS schema. Its documented description is "Reconciled AR receipts for unreconciling," which identifies its purpose precisely: it exposes those Accounts Receivable receipts that have already been reconciled against a bank statement line, so that a reconciliation can be reversed (unreconciled) through the standard Cash Management flow. In EBS 12.1.1 and 12.2.2, the Cash Management reconciliation engine operates against a family of numbered "222" views (CE_222_RECONCILED_V, CE_222_UNRECONCILED_V, and related variants), each serving a specific stage of the reconciliation lifecycle. This particular member of the family is consumed by the AutoReconciliation and manual unreconciliation programs when they must identify receipts currently tied to a statement line, allowing the process to detach them cleanly and restore the correct receipt status and accounting.

The view is read-only and non-historized; it is a query layer, not a table. Its role is primarily operational (feeding reconciliation and unreconciliation logic) rather than analytical, though it is frequently queried directly during reconciliation troubleshooting and data repair.

Underlying Base Objects

The view is defined over a broad set of APPS synonyms and views. The documented referenced objects are: AR_BATCHES_ALL, AR_CASH_RECEIPTS_ALL, AR_CASH_RECEIPT_HISTORY_ALL, AR_LOOKUPS, AR_PAYMENT_SCHEDULES_ALL, CE_BANK_ACCOUNTS, CE_BANK_ACCT_USES_ALL, CE_LOOKUPS, CE_SECURITY_PROFILES_GT, CE_STATEMENT_HEADERS, CE_STATEMENT_LINES, CE_STATEMENT_RECONCILS_ALL, CE_SYSTEM_PARAMETERS, CE_TRANSACTION_CODES, GL_DAILY_CONVERSION_TYPES, GL_LEDGERS, HZ_CUST_ACCOUNTS, and HZ_PARTIES.

Driving the view is AR_CASH_RECEIPT_HISTORY_ALL, aliased CRH in the view text, which supplies the cash_receipt_history_id, the history status (CLEARED, REMITTED, REVERSED), the GL date, exchange data, and the amount and factor-discount amounts. AR_CASH_RECEIPTS_ALL (CR) supplies receipt_number, currency_code, type, and receipt_date. AR_PAYMENT_SCHEDULES_ALL (APS/APS2) supplies due dates and the class attribute used to detect DM (debit memo) rows. CE_STATEMENT_LINES and CE_STATEMENT_HEADERS join to the bank statement, while CE_BANK_ACCOUNTS and CE_BANK_ACCT_USES_ALL supply the bank account currency used in the DECODE expressions. GL_LEDGERS (SOB) provides the ledger/functional currency. Security filtering is applied through CE_SECURITY_PROFILES_GT, and lookups are resolved via AR_LOOKUPS and CE_LOOKUPS.

Key Columns

• ROWID — Row identifier of the underlying cash receipt history record.
• STATEMENT_LINE_ID — The bank statement line to which the receipt is reconciled; the key to unreconcile.
• BANK_ACCOUNT_ID — Bank account that owns the reconciliation.
• CASH_RECEIPT_HISTORY_ID — History record driving the current receipt status.
• TYPE / MEANING — Receipt type code and its decoded meaning.
• RECEIPT_NUMBER, CURRENCY_CODE, RECEIPT_DATE — Core receipt identifiers.
• Currency classification — DECODE over CR.CURRENCY_CODE yielding 'FUNCTIONAL', 'BANK', or 'FOREIGN'.
• Amount columns — Receipt amount (with NVL on FACTOR_DISCOUNT_AMOUNT) and the accounted amount, conditionally derived by currency relationship.
• GL_DATE, TRX_DATE, EXCHANGE_DATE, DUE_DATE — Date attributes used for accounting and reconciliation.
• Status columns — Derived status that maps debit memos to 'REVERSED' for reconciliation purposes.
• EXCHANGE_RATE, USER_CONVERSION_TYPE — Conversion type and rate from GL_DAILY_CONVERSION_TYPES.

Common Use Cases and Queries

Typical uses include identifying reconciled receipts prior to an unreconcile operation, auditing which statement line a receipt is attached to, and verifying receipt status and currency treatment.

List reconciled receipts for a statement line:

• SELECT receipt_number, statement_line_id, cash_receipt_history_id, amount, gl_date FROM ce_222_reconciled_v WHERE statement_line_id = :p_line_id;

Locate a receipt by number:

• SELECT receipt_number, statement_line_id, status, currency_code, amount FROM ce_222_reconciled_v WHERE receipt_number = :p_receipt_num;

Review reconciled receipts by bank account:

• SELECT bank_account_id, COUNT(*), SUM(amount) FROM ce_222_reconciled_v GROUP BY bank_account_id;

Because the view returns the statement line identifier and history identifier, its output can be joined back to CE_STATEMENT_RECONCILS_ALL to drive or verify an unreconciliation. The view is valid and security-filtered, and reflects the current reconciliation state at query time.