Results for “deal_date_amount_id”

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

Overview

XTR_DEAL_DATE_AMOUNTS is a Treasury (XTR) module table within Oracle E-Business Suite 12.1.1 and 12.2.2 that stores deal transaction information grouped by date type, amount type, and amount action. It functions as the central repository where cashflow dates, monetary amounts, and the settlement actions associated with treasury deals are persisted. Each row represents a discrete dated amount event tied to a parent deal, transaction, or rollover, providing the granular detail used for cashflow forecasting, accrual accounting, settlement processing, and exposure reporting within Oracle Treasury.

From a dimensional modeling perspective, the heuristic Data Vault classification derived from the foreign key structure is a link. The table sits between hub-like entities such as deals, bank accounts, batch processes, and reference tables (amount types, amount actions, date types), making it a transactional junction rather than a pure descriptive satellite. This classification is a modeling suggestion only; the physical table behaves as a transactional detail store in the ETRM schema.

Key Information Stored

The table contains 87 documented columns. The most significant are listed below. The surrogate primary key is DEAL_DATE_AMOUNT_ID (enforced by constraint XTR_DEAL_DATE_AMOUNT_PK1), which uniquely identifies each dated amount record. In addition to the synthetic key, a unique business-key index XTR_DEAL_DATE_AMOUNTS_U1 spans DEAL_NUMBER, TRANSACTION_NUMBER, TRANSACTION_DATE, DEAL_TYPE, DATE_TYPE, AMOUNT_TYPE, AMOUNT_DATE, SETTLEMENT_NUMBER, and CASHFLOW_AMOUNT.

• DEAL_NUMBER — the treasury deal to which the dated amount belongs.
• TRANSACTION_NUMBER — the specific transaction within the deal.
• DEAL_TYPE and DEAL_SUBTYPE — classification of the underlying instrument.
• DATE_TYPE — category of date being recorded (e.g., value date, maturity date), linked to XTR_DATE_TYPES.
• AMOUNT_TYPE — classification of the monetary amount, linked to XTR_AMOUNT_TYPES.
• ACTION_CODE — the amount action applied, linked to XTR_AMOUNT_ACTIONS.
• AMOUNT_DATE — the effective date for the amount.
• AMOUNT and CASHFLOW_AMOUNT — the transaction amount and its cashflow representation.
• CURRENCY — currency of the amount.
• TRANSACTION_DATE — the date the transaction was booked.
• BATCH_ID — the batch process that created or updated the record, linked to XTR_BATCHES.
• COMPANY_ACCT_USE_ID, CPARTY_ACCT_USE_ID, and BENEFECIARY_ACCT_USE_ID — bank account use references linking to CE_BANK_ACCT_USES_ALL.
• ACCOUNT_NO — the internal bank account, linked to XTR_BANK_ACCOUNTS.
• STATUS_CODE and SETTLEMENT_NUMBER — settlement workflow state and identifier.

Common Use Cases and Queries

The table supports cashflow reporting, accrual processing, and settlement reconciliation. Typical queries join to XTR_DEALS for deal context and to CE_BANK_ACCT_USES_ALL for banking information.

• Cashflow projection by currency and amount date:

SELECT d.deal_number, a.currency, a.amount_date, SUM(a.cashflow_amount)
FROM xtr_deal_date_amounts a, xtr_deals d
WHERE a.deal_number = d.deal_number
AND a.amount_date BETWEEN :from_date AND :to_date
GROUP BY d.deal_number, a.currency, a.amount_date;

• Reconciling amounts grouped by amount type and action using XTR_AMOUNT_TYPES and XTR_AMOUNT_ACTIONS.
• Identifying open settlement items by STATUS_CODE and SETTLEMENT_NUMBER.
• Auditing batch activity by aggregating on BATCH_ID.

Related Objects

The table participates in a dense web of foreign key relationships across the XTR and CE schemas:

• XTR_DEALS — joined on DEAL_NUMBER; the parent deal record.
• XTR_ROLLOVER_TRANSACTIONS — joined on DEAL_NUMBER and TRANSACTION_NUMBER.
• XTR_EXPOSURE_TRANSACTIONS — joined on DEAL_NUMBER for exposure detail.
• XTR_INTERGROUP_TRANSFERS — joined on DEAL_NUMBER and TRANSACTION_NUMBER.
• XTR_DATE_TYPES, XTR_AMOUNT_TYPES, and XTR_AMOUNT_ACTIONS — reference tables supplying DATE_TYPE, AMOUNT_TYPE, and ACTION_CODE.
• XTR_BANK_ACCOUNTS — joined on ACCOUNT_NO and CPARTY_CODE.
• CE_BANK_ACCT_USES_ALL — joined on COMPANY_ACCT_USE_ID, CPARTY_ACCT_USE_ID, and BENEFECIARY_ACCT_USE_ID.
• XTR_BATCHES — joined on BATCH_ID.