Search Results xtr_settlement_letters_v




Overview
Explore in AI Chat

Overview

XTR_SETTLEMENT_LETTERS_V is a reporting view in the Oracle E-Business Suite Treasury (XTR) module, owned by the APPS schema and shipped with a VALID status. Its purpose is to consolidate treasury deal cashflows that are ready for settlement, producing one settlement line per counterparty account, settlement date, netting reference, and currency. In EBS 12.1.1 and 12.2.2 the view serves as the data source behind settlement letter generation and netting / settlement reporting, where the treasury user needs a clean, aggregated set of amounts payable or receivable rather than the raw, granular cashflow rows.

The view is a UNION ALL of two branches drawn from XTR_DEAL_DATE_AMOUNTS_V. The first branch covers deals that participate in netting (NETOFF_NUMBER IS NOT NULL) and aggregates cashflows with SUM(CASHFLOW_AMOUNT), suppressing any netting group whose net amount is zero via a HAVING clause. The second branch covers non-netted settlements (NETOFF_NUMBER IS NULL), where each qualifying cashflow is reported individually. Both branches restrict rows to cashflows flagged SETTLE = 'Y' and exclude zero-amount cashflows, so only genuinely settling items appear.

Underlying Base Objects

  • XTR_DEAL_DATE_AMOUNTS_V (VIEW) — the immediate data source. It supplies the deal cashflow amounts, counterparty and account identifiers, currency, company code, netting reference, settlement flags, and cheque/direct-debit indicators that the settlement view filters and aggregates.
  • XTR_USER_ACCESS (PACKAGE) — the documented security/access package associated with the XTR object set. It governs row-level treasury access so that a user querying settlement data sees the deals, portfolios, and counterparties they are authorized to work with.

Because settlement letters ultimately produce external correspondence and payment instructions, access to this view is normally mediated through XTR_USER_ACCESS rather than granted directly to broad responsibility sets.

Key Columns

  • SETTLE_AMOUNT — the settlement amount; an aggregated SUM(CASHFLOW_AMOUNT) in the netted branch, or the individual cashflow amount in the non-netted branch.
  • ACTUAL_SETTLEMENT_DATE — derived as NVL(ACTUAL_SETTLEMENT_DATE, AMOUNT_DATE); the actual settlement date takes precedence, falling back to the cashflow amount date (the column the user searched on) when no actual date exists.
  • CPARTY_ACCOUNT_NO — NVL(BENEFICIARY_ACCOUNT_NO, CPARTY_ACCOUNT_NO); the beneficiary account if present, otherwise the counterparty account.
  • ACCOUNT_NO — the internal company/bank account associated with the cashflow.
  • NETOFF_NUMBER / NETOFF_GROUP_FLAG — the netting reference and its grouping indicator; NULL NETOFF_NUMBER identifies the non-netted branch.
  • SETTLE_PARTY — NVL(BENEFICIARY_PARTY, CPARTY_CODE); the party to be settled.
  • CURRENCY, COMPANY_CODE — grouping keys that keep aggregated totals currency- and entity-specific.
  • SETTLEMENT_ACTIONED, TRANS_MTS, DIRECT_DEBIT, CHQ_REQD — settlement processing status, treasury transaction reference, direct-debit, and cheque-required indicators used when producing letters.

Common Use Cases and Queries

Typical use cases include producing netted settlement letters, reconciling expected settlement amounts by currency and company, and identifying non-netted items requiring individual payment instructions.

  • List upcoming settlements by date: SELECT CPARTY_ACCOUNT_NO, ACTUAL_SETTLEMENT_DATE, CURRENCY, SETTLE_AMOUNT FROM XTR_SETTLEMENT_LETTERS_V WHERE ACTUAL_SETTLEMENT_DATE >= TRUNC(SYSDATE) ORDER BY ACTUAL_SETTLEMENT_DATE;
  • Total settlement exposure per currency: SELECT CURRENCY, SUM(SETTLE_AMOUNT) FROM XTR_SETTLEMENT_LETTERS_V GROUP BY CURRENCY;
  • Isolate netted groups: SELECT * FROM XTR_SETTLEMENT_LETTERS_V WHERE NETOFF_NUMBER IS NOT NULL;
  • Filter items requiring cheques: SELECT * FROM XTR_SETTLEMENT_LETTERS_V WHERE CHQ_REQD = 'Y';

Queries frequently join settlement output back to counterparty and deal tables using CPARTY_CODE or TRANS_MTS for detail drill-down.