Search Results reconciled_reference




Overview
Explore in AI Chat

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

Overview

CE_CP_XTO_V is a Cash Management (CE) view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is documented as the "Cash Positioning source for Discoverer - Treasury Inflow," meaning it is designed to feed Oracle Discoverer worksheets and other cash positioning reports that analyze treasury inflows. Specifically, this view surfaces negative cashflow amounts—payments and outflows recorded in the treasury deal tables—so that cash managers can position these items against reconciled bank activity.

The view is a query-only construct; it stores no data and is defined entirely over base objects in the APPS schema. Its role in EBS reporting is to reconcile treasury deal activity (XTR tables) with bank account definitions (CE tables) and party/legal-entity information (XTR_PARTY_INFO). The presence of the RECONCILED_REFERENCE column—the term that drove the user's search—makes this view particularly relevant for tracing how treasury deals map to reconciliation records.

Underlying Base Objects

The view is defined over three documented base objects:

  • XTR_DEAL_DATE_AMOUNTS (SYNONYM) — the primary fact source, supplying deal dates, amounts, deal type/subtype, status, company code, currency, account number, and the reconciled reference.
  • CE_BANK_ACCTS_GT_V (VIEW) — supplies bank account IDs, account owner organization, currency, and bank account number, joined to the treasury data to resolve the correct cash account.
  • XTR_PARTY_INFO (SYNONYM) — provides the legal entity identifier used to link the treasury party to the bank account owner.

The joins apply outer joins ((+)) on the party, currency, and account number predicates, so deals without a matching bank account or party are still returned. Filter predicates restrict rows to non-cancelled deals (STATUS_CODE <> 'CANCELLED'), negative cashflow amounts (CASHFLOW_AMOUNT < 0), and single settlements only (NVL(MULTIPLE_SETTLEMENTS,'N') = 'N').

Key Columns

  • REFERENCE_ID — aliases DEAL_DATE_AMOUNT_ID, the primary key of the deal date amount record.
  • BANK_ACCOUNT_ID — the resolved CE bank account identifier.
  • CASH_ACTIVITY_DATE — coalesces ACTUAL_SETTLEMENT_DATE with AMOUNT_DATE, giving the effective date for cash positioning.
  • DEAL_NUMBER — the treasury deal reference.
  • BASE_AMOUNT — the cashflow amount; negative values represent outflows.
  • INDICATIVE_FLAG — a DECODE expression returning 'Y' for EXP/INDIC deal type/subtype combinations, otherwise 'N'.
  • DEAL_TYPE — the treasury deal classification (e.g., EXP).
  • RECONCILED_REFERENCE — the reconciliation identifier carried forward from XTR_DEAL_DATE_AMOUNTS, enabling users to trace which bank statement reconciliation a treasury outflow corresponds to.

Common Use Cases and Queries

Typical usages include building Discoverer cash positioning reports, reconciling treasury outflows to bank statements, and auditing which deals have been reconciled. A sample query retrieving reconciled outflows for a bank account follows:

  • SELECT deal_number, cash_activity_date, base_amount, reconciled_reference FROM ce_cp_xto_v WHERE bank_account_id = :p_account_id AND reconciled_reference IS NOT NULL ORDER BY cash_activity_date;
  • SELECT deal_type, COUNT(*), SUM(base_amount) FROM ce_cp_xto_v WHERE reconciled_reference IS NULL GROUP BY deal_type; — identifies unreconciled treasury outflows.
  • SELECT deal_number, indicative_flag, cash_activity_date FROM ce_cp_xto_v WHERE indicative_flag = 'Y'; — isolates indicative deals prior to final settlement.

Because the view filters to negative amounts and single settlements, users should not rely on it for inflow or multi-settlement analysis; those require querying XTR_DEAL_DATE_AMOUNTS directly.