Results for “actual_value_date”

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

Overview

CE_CP_ARR_V is a Cash Management (CE) view owned by the APPS schema in Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. It is documented in the E-Business Suite Technical Reference Manual (ETRM) as the "Cash Positioning source for Discoverer - AR Receipts." Its purpose is to consolidate Accounts Receivable receipt information into a single, denormalized row per cash receipt, suitable for cash positioning and reconciliation reporting through Oracle Discoverer and similar analytic tools. Because cash positioning requires a reliable activity date for each receipt, the view is the canonical source used when users search for the actual_value_date attribute.

The view returns one row per cash receipt, joining the receipt header, the current receipt history record, and the associated payment schedule. Only current, non-reversed history records are included, and records whose reversal GL date is populated are excluded, ensuring that reversed or superseded receipt activity does not distort cash position reporting.

Underlying Base Objects

The ETRM documents three referenced base objects, all exposed through synonyms in the APPS schema:

• AR_CASH_RECEIPTS_ALL (alias CR) — the receipt entity, supplying CASH_RECEIPT_ID, REMITTANCE_BANK_ACCOUNT_ID, ACTUAL_VALUE_DATE, RECEIPT_NUMBER, and RECEIPT_METHOD_ID.
• AR_CASH_RECEIPT_HISTORY_ALL (alias CRH) — receipt history, supplying ACCTD_AMOUNT, STATUS, TRX_DATE, ORG_ID, CURRENT_RECORD_FLAG, and REVERSAL_GL_DATE.
• AR_PAYMENT_SCHEDULES_ALL (alias APS) — payment schedules, supplying DUE_DATE and CASH_RECEIPT_ID.

Joins are made on CASH_RECEIPT_ID between receipts and history, and between history and payment schedules (outer-joined on the schedule side). Organization ID conditions use OR/IS NULL logic to tolerate records that are not organization-qualified, and the predicates CURRENT_RECORD_FLAG = 'Y', STATUS != 'REVERSED', and REVERSAL_GL_DATE IS NULL restrict results to active receipt lines.

Key Columns

• REFERENCE_ID — the CASH_RECEIPT_ID; the unique receipt identifier used for linking and drill-down.
• BANK_ACCOUNT_ID — the remittance bank account (REMITTANCE_BANK_ACCOUNT_ID) into which funds were received; central to cash positioning by bank account.
• CASH_ACTIVITY_DATE — the effective activity date for cash positioning, computed as NVL(CR.ACTUAL_VALUE_DATE, NVL(APS.DUE_DATE, CRH.TRX_DATE)). This fallback chain means the actual value date takes priority, then the payment schedule due date, then the transaction date.
• ACTUAL_VALUE_DATE — the value date recorded on the receipt; the column most frequently searched for in this view.
• MATURITY_DATE — the payment schedule due date (APS.DUE_DATE) exposed under its reporting name.
• TRX_DATE — the receipt history transaction date (CRH.TRX_DATE).
• RECEIPT_NUMBER — the user-visible receipt number.
• BASE_AMOUNT — the accounted amount from receipt history (CRH.ACCTD_AMOUNT).
• STATUS — the receipt history status.
• RECEIPT_METHOD_ID — the receipt method reference, useful for grouping by payment instrument.

Common Use Cases and Queries

Typical uses include daily cash positioning by bank account, reviewing receipts with missing value dates, and reconciling AR receipts to bank statements. A representative query selecting the searched attribute is:

SELECT reference_id,
       bank_account_id,
       cash_activity_date,
       actual_value_date,
       trx_date,
       receipt_number,
       base_amount,
       status
FROM   apps.ce_cp_arr_v
WHERE  bank_account_id = :p_bank_account_id
AND    cash_activity_date BETWEEN :p_start_date AND :p_end_date;

To isolate receipts where the value date is absent (and the fallback logic is therefore in effect):

SELECT receipt_number,
       actual_value_date,
       maturity_date,
       trx_date,
       cash_activity_date
FROM   apps.ce_cp_arr_v
WHERE  actual_value_date IS NULL;

Because the view already filters out reversed receipts and selects only the current history record, it can be consumed directly by Discoverer worksheets and custom concurrent programs without additional reversal handling, although organization-level security is typically applied outside the view.