Results for “ce_101_reconciled_v”

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

Overview

The view APPS.CE_101_RECONCILED_V is a Cash Management (CE) reporting object that exposes reconciled General Ledger journal lines for the purpose of unreconciliation. Within the Oracle E-Business Suite 12.1.1 and 12.2.2 data model, bank statement reconciliation in Cash Management creates an association between a bank statement line and the GL journal line that cleared the transaction. When a user subsequently needs to reverse or undo that reconciliation, the application requires a structured, queryable representation of the jointly reconciled GL and statement data. CE_101_RECONCILED_V provides exactly that projection: a single row per reconciled GL journal line, annotated with the statement line, bank account, currency attributes, and audit columns needed to drive the unreconcile action. Because reconciliation logic in CE is tightly coupled to both the statement model and the GL journal model, this view acts as the integration bridge between the two sub-ledgers, presenting amounts, conversion information, and identity keys in a form the Unreconcile flow can consume.

As a view, it stores no data of its own; it is a read-only projection whose behavior is entirely determined by its underlying tables and by the filters (bank account, statement, transaction code) applied at query time.

Underlying Base Objects

The documented base objects underlying CE_101_RECONCILED_V span three functional areas, consistent with its role as a reconciliation bridge:

• Cash Management statement objects: CE_STATEMENT_LINES, CE_STATEMENT_HEADERS, CE_STATEMENT_RECONCILS_ALL, CE_TRANSACTION_CODES, CE_SYSTEM_PARAMETERS, CE_BANK_ACCTS_GT_V, and CE_LOOKUPS.
• General Ledger journal objects: GL_JE_LINES, GL_JE_HEADERS, GL_JE_BATCHES, GL_SETS_OF_BOOKS, GL_DAILY_CONVERSION_TYPES, and GL_LOOKUPS.

The view's SQL text confirms these relationships: journal line attributes originate from the GL tables (JEL for lines, JEH for headers, JEB for batches), while the statement side contributes STATEMENT_LINE_ID and STATEMENT_HEADER_ID from the CE statement tables. The set of books (SOB) supplies functional currency context for currency decoding, and CE_BANK_ACCTS_GT_V (aliased ABA) supplies bank account and bank currency attributes. The reconciliation linkage that makes the view "reconciled" is supplied by CE_STATEMENT_RECONCILS_ALL.

Key Columns

The view exposes a fixed column list documented in the implementation text (numbered 1 through 43). Notable columns include:

• ROWID and statement keys: the GL line ROWID, STATEMENT_LINE_ID, and STATEMENT_HEADER_ID, which anchor the unreconcile operation.
• Bank account context: BANK_ACCOUNT_ID and BANK_BRANCH_ID from the bank account object.
• Journal line identity: JE_LINE_NUM, JE_HEADER_ID, JE_BATCH_ID, and batch NAME, supporting drill-down to GL.
• Amount and payment type: a decoded PAYMENT/CASH indicator, plus entered/accounted debit and credit amounts resolved via nested DECODE expressions that distinguish payment versus cash receipts.
• Currency handling: CURRENCY_CODE and a decode yielding FUNCTIONAL, BANK, or FOREIGN, together with CURRENCY_CONVERSION_DATE, USER_CONVERSION_TYPE, and CURRENCY_CONVERSION_RATE.
• Descriptive and audit columns: MEANING (from lookups), DESCRIPTION, EFFECTIVE_DATE, STATUS, and the standard CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and LAST_UPDATED_BY.

Multiple placeholder columns emit NULL or TO_NUMBER(NULL), reflecting the fixed-width signature expected by the consuming form or API.

Common Use Cases and Queries

This view is primarily used by the Cash Management reconciliation and unreconciliation process, and secondarily by diagnostics and reporting on reconciled journal activity. A typical query filters by bank account and statement line to retrieve the GL lines eligible for unreconciliation:

SELECT statement_line_id, je_header_id, je_line_num,
       bank_account_id, currency_code, effective_date
FROM   ce_101_reconciled_v
WHERE  bank_account_id = :p_bank_account_id
AND    statement_line_id = :p_statement_line_id;

Because the view materializes amount decodes for functional, bank, and foreign currencies, it also supports reconciliation audits—reconciling the statement amount to the GL entered or accounted amount—and technical diagnostics when the Unreconcile program cannot locate a candidate line. Join it to GL_JE_HEADERS or GL_JE_LINES using the exposed keys (JE_HEADER_ID, JE_LINE_NUM) for drill-down detail. All queries should be issued against the APPS schema with the standard EBS security context.