Results for “ce_801_reconciled_v”

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

Overview

CE_801_RECONCILED_V is a Cash Management view owned by the APPS schema that exposes reconciled payroll payments for the unreconciliation process in Oracle E-Business Suite 12.1.1 and 12.2.2. It is not a general-purpose bank reconciliation report view; rather, it conforms to the column contract expected by the Cash Management reconciliation engine, which is why the projection includes a large number of positional placeholders (NULL, TO_NUMBER(NULL), TO_DATE(NULL)) that satisfy the engine's expected row shape while only a subset of columns carries meaningful data for payroll payments.

The "801" prefix identifies the view as a member of the CE_801_* family of reconciliation source views, where 801 is the internal transaction source type used for payroll payment reconciliation. Its documented purpose — reconciled payroll payments for unreconciling — means it returns candidate rows that the Reconcile Bank Statements or Reconcile Payroll Payments flow can display and act upon when a previously reconciled payroll payment must be reversed. The view is marked VALID in the APPS schema and is intended to be queried at runtime rather than materialized.

Underlying Base Objects

The view is defined over a join spanning Cash Management, Payables-side payment schema objects, and HRMS payroll tables. Documented base objects include:

• CE_STATEMENT_HEADERS, CE_STATEMENT_LINES, CE_TRANSACTION_CODES, CE_STATEMENT_RECONCILS_ALL — the bank statement and reconciliation records that anchor each returned row.
• CE_BANK_ACCOUNTS, CE_BANK_ACCT_USES_ALL — bank account and account-use details, supplying account name, number, currency, and bank branch.
• CE_LOOKUPS, FND_COMMON_LOOKUPS — decode lookups for status, action status, and transaction type meanings.
• CE_SYSTEM_PARAMETERS, GL_SETS_OF_BOOKS — used in DECODE logic to classify amounts as FUNCTIONAL, BANK, or FOREIGN currency.
• PAY_CE_RECONCILED_PAYMENTS, PAY_PAYROLL_ACTIONS, PAY_ACTION_INTERLOCKS, PAY_ASSIGNMENT_ACTIONS, PAY_PRE_PAYMENTS, PAY_ORG_PAYMENT_METHODS_F — the payroll payment, action, and payment method tables that identify the reconciled payroll payments eligible for unreconciliation.
• CE_SECURITY_PROFILES_GT and FND_GLOBAL — applied to enforce organization and user-based security filtering.

The view text selects from CE_STATEMENT_RECONCILS_ALL aliased CRE, joining statement lines and headers to payroll reconciled payments (aliased PAA, from PAY_CE_RECONCILED_PAYMENTS), which supplies the ROWID used as the view's identity column.

Key Columns

• ROWID / PAA.ROWID — the row identifier of the underlying reconciled payroll payment, used by the reconciliation engine to target the record for unreconciliation.
• STATEMENT_LINE_ID, STATEMENT_HEADER_ID, LINE_NUMBER — links back to the bank statement line and header being reconciled.
• BANK_ACCOUNT_ID, BANK_ACCT_USE_ID, BANK_ACCOUNT_NAME, BANK_ACCOUNT_NUM, CURRENCY_CODE, BANK_BRANCH_ID — bank account and branch identification.
• PAYMENT_TYPE flags ('PAYMENT', 'PAY') and L.MEANING, L1.MEANING — transaction type and status lookups; the literal 'V' and lookup meaning for ACTION_STATUS 'V' indicate reconciled status.
• Amount columns — DECODE expressions that resolve PPP.VALUE, PPP.BASE_CURRENCY_VALUE, or bank account currency equivalents to express the payment amount as FUNCTIONAL, BANK, or FOREIGN currency, depending on whether POPM.CURRENCY_CODE matches the set of books or bank account currency.
• TRX_DATE, AMOUNT, STATUS, TRX_TYPE, DESCRIPTION — statement line transaction date, amount, status, and transaction code description.
• EFFECTIVE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, REQUEST_ID — audit and effective-dating attributes.
• Literal 801 and 1 — the transaction source identifier and a version/active indicator consumed by the Cash Management engine.

Common Use Cases and Queries

The view is typically invoked by the Cash Management reconciliation and unreconciliation programs rather than by end users directly. Reconciliation and treasury analysts may query it to audit which payroll payments are currently marked reconciled and therefore available for reversal.

Typical query pattern:

• SELECT ROWID, STATEMENT_LINE_ID, BANK_ACCOUNT_ID, STATEMENT_NUMBER, TRX_DATE, AMOUNT, CURRENCY_CODE, STATUS FROM APPS.CE_801_RECONCILED_V WHERE BANK_ACCOUNT_ID = :p_bank_account_id;
• SELECT STATEMENT_LINE_ID, STATEMENT_NUMBER, TRX_DATE, AMOUNT, BANK_ACCOUNT_NAME FROM APPS.CE_801_RECONCILED_V WHERE TRX_DATE BETWEEN :p_from AND :p_to ORDER BY TRX_DATE;
• SELECT STATEMENT_HEADER_ID, STATEMENT_LINE_ID, BANK_ACCT_USE_ID, CURRENCY_CODE, AMOUNT FROM APPS.CE_801_RECONCILED_V WHERE STATEMENT_LINE_ID = :p_statement_line_id;

Because CE_SECURITY_PROFILES_GT and FND_GLOBAL participate in the definition, queries return only records permitted by the user's Cash Management security profile. Callers should treat CE_801_RECONCILED_V as read-only; unreconciliation itself is performed through the standard Cash Management payroll reconciliation flows, which use the view's ROWID and statement line identifiers to locate the payment to reverse.