Results for “ce_reconciliation_errors_v”

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

Overview

CE_RECONCILIATION_ERRORS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the Cash Management (CE) module. Its documented purpose is to expose reconciliation errors — the messages raised by Oracle Cash Management when an attempt to reconcile, unreconcile, or clear a bank statement line fails. The view presents one row per error record, joining the error log to the statement line that triggered it where such a line exists.

Because reconciliation failures are otherwise recorded only in an internal error table keyed by surrogate identifiers, the view exists to make that information readable. It resolves the statement header and statement line identifiers into a combined, user-facing result set and supplies a default application short name, allowing reconciliation exceptions to be reported alongside the originating bank statement without additional joins. It participates in EBS reporting and integration in the standard way for a CE view: it may be queried directly in a BI Publisher or custom report, referenced by a concurrent program, or exposed through a database link or interface table to an external reconciliation dashboard. The view is read-only and carries the standard Oracle proprietary and confidential designation.

Underlying Base Objects

The ETRM 12.2.2 metadata documents two referenced base objects, both surfaced through APPS synonyms: CE_RECONCILIATION_ERRORS and CE_STATEMENT_LINES. The view text confirms a UNION ALL over two branches of CE_RECONCILIATION_ERRORS.

• CE_RECONCILIATION_ERRORS — the error log itself, providing MESSAGE_NAME, CREATION_DATE, CREATED_BY, APPLICATION_SHORT_NAME, and the STATEMENT_LINE_ID / STATEMENT_HEADER_ID keys.
• CE_STATEMENT_LINES — the bank statement line table, joined on STATEMENT_LINE_ID to retrieve LINE_NUMBER for display.

The first branch of the union joins the two tables on STATEMENT_LINE_ID and returns rows where the error is attached to a specific statement line. The second branch selects errors where STATEMENT_LINE_ID IS NULL — header-level or non-line-specific errors — and substitutes TO_NUMBER(NULL) for both STATEMENT_LINE_ID and STATEMENT_LINE_NUMBER, keeping the column list and data types aligned across the union. The result is a complete exception list in which line-specific and header-only errors coexist.

Key Columns

• ROW_ID — the ROWID of the underlying CE_RECONCILIATION_ERRORS row; usable as a unique handle for the error record.
• STATEMENT_HEADER_ID — identifier of the bank statement header associated with the error.
• STATEMENT_LINE_ID — identifier of the statement line that caused the error; NULL for errors not tied to a specific line. This is the column most frequently searched for, since it is the join key between the error log and the statement line.
• STATEMENT_LINE_NUMBER — the display line number from CE_STATEMENT_LINES; NULL in the second union branch.
• MESSAGE_NAME — the name of the reconciliation error message raised.
• CREATION_DATE and CREATED_BY — audit columns recording when and by whom the error row was inserted.
• APPLICATION_SHORT_NAME — the originating application, defaulted to 'CE' via NVL when not populated.

Common Use Cases and Queries

The principal use case is exception reporting on bank statement reconciliation. A typical query lists all errors for a statement header, ordered by creation date:

• SELECT statement_header_id, statement_line_id, statement_line_number, message_name, creation_date, created_by FROM ce_reconciliation_errors_v WHERE statement_header_id = :header_id ORDER BY creation_date;

To isolate errors tied to a specific statement line — the common lookup behind a user's search for statement_line_id — filter on that column directly:

• SELECT * FROM ce_reconciliation_errors_v WHERE statement_line_id = :line_id;

To separate line-level failures from header-level failures, test for null:

• SELECT * FROM ce_reconciliation_errors_v WHERE statement_line_id IS NULL;

A further scenario is identifying the most frequent reconciliation errors over a period, grouped by message and application:

• SELECT application_short_name, message_name, COUNT(*) FROM ce_reconciliation_errors_v WHERE creation_date BETWEEN :from_date AND :to_date GROUP BY application_short_name, message_name ORDER BY 3 DESC;

Because the view resolves the error log against statement lines, any error whose statement line has since been purged will fall out of the first union branch and appear only if it is a header-level row, a behaviour worth accounting for when reconciling report totals against CE_RECONCILIATION_ERRORS directly.