Search Results xtr_deals_interface_u1

Overview

XTR.XTR_DEALS_INTERFACE is a public, active table in the Oracle E-Tax and Treasury (ETRM) product, owned by the XTR schema. It functions as a temporary staging area for deal data awaiting transfer to the actual deal tables. Its display name is "Treasury Deal Data Import Interface," and its category is BUSINESS_ENTITY XTR_DEAL_DATA. Records are loaded here before validation, enrichment, and migration into the core treasury deal tables, making the object the primary entry point for external deal imports, batch feeds, and third-party system integrations. It resides in the APPS_TS_INTERFACE tablespace with PCT Free 10.

From a Data Vault modeling perspective, the metadata classifies this object heuristically as a link. This reflects its role as an associative structure that reconciles external identifiers with internal treasury reference data. A pragmatic reading is that the table carries a mix of business keys, descriptive attributes, and foreign-key references that together resemble a link-satellite hybrid; modelers may wish to normalize it into a true link (DEAL_TYPE plus EXTERNAL_DEAL_ID) surrounded by satellites if adopting Data Vault methodology directly.

Key Information Stored

The table contains 96 documented columns. The most significant are:

• EXTERNAL_DEAL_ID (VARCHAR2 240) — external unique primary identifier for each deal. It is also a business-key candidate.
• DEAL_TYPE — code identifying the deal or instrument type. It forms the other half of the business key.
• LOAD_STATUS_CODE (VARCHAR2 30) — the transfer status flag. Values include 'NEW' or null (new deal), 'UPDATED', 'ERROR', 'SUBMIT', 'DEAL_TYPE_ERROR', and 'DUPLICATE_DEAL_ID'. This is the principal operational control column.
• COMPANY_CODE, CPARTY_CODE, DEALER_CODE, PORTFOLIO_CODE, LIMIT_CODE, and PRODUCT_TYPE — core reference keys linking a staged deal to the company, counterparty, dealer, portfolio, limit, and product taxonomies. CPARTY_CODE additionally stores THIRDPARTY_CODE for EXP deals.
• CURRENCY_A / AMOUNT_A, CURRENCY_B / AMOUNT_B, and RATE_A / RATE_B / RATE_C — monetary and rate legs of the deal.
• DATE_A, DATE_B, DATE_C, and NO_OF_DAYS — the deal's key dates and tenor.
• EXTERNAL_SOURCE — identifies the originating system or feed.
• ACTION_CODE, VALIDATION_FLAG, and SETTLEMENT_FLAG — processing directives controlling how the record is validated and settled.
• ATTRIBUTE1–15 and ATTRIBUTE_CATEGORY — the standard EBS descriptive flexfield columns.
• Standard WHO audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

The table has no documented single-column surrogate primary key; the unique index XTR_DEALS_INTERFACE_U1 on (DEAL_TYPE, EXTERNAL_DEAL_ID) is the business-key candidate that enforces uniqueness of each staged deal per deal type.

Common Use Cases and Queries

Typical scenarios include monitoring inbound deal feeds, diagnosing import failures, and reconciling staged versus posted deals.

To inspect unprocessed deals by source:

• SELECT external_deal_id, deal_type, currency_a, amount_a FROM xtr.xtr_deals_interface WHERE load_status_code IS NULL OR load_status_code = 'NEW';

To surface error records requiring remediation:

• SELECT external_deal_id, deal_type, load_status_code FROM xtr.xtr_deals_interface WHERE load_status_code IN ('ERROR','DUPLICATE_DEAL_ID','DEAL_TYPE_ERROR');

To quantify throughput by source and status:

• SELECT external_source, load_status_code, COUNT(*) FROM xtr.xtr_deals_interface GROUP BY external_source, load_status_code;

Reporting commonly joins this table to XTR_DEALS and XTR_DEALS_HISTORY to compare staged values against posted deals and to measure validation lag between CREATION_DATE and posting.

Related Objects

Foreign-key relationships tie the interface table to core EBS master and transactional data:

• HZ_PARTIES — via FROM_BANK_BRANCH_ID and TO_BANK_BRANCH_ID, identifying the origin and destination bank branches.
• CE_BANK_ACCT_USES_ALL — via COMPANY_ACCT_A_USE_ID, COMPANY_ACCT_B_USE_ID, and CPARTY_ACCT_USE_ID, resolving company and counterparty bank account uses.
• EDR_PSIG_SECURITY — via SECURITY_ID, linking a staged deal to a security instrument.
• XTR_DEALS and XTR_DEALS_HISTORY — the principal target tables reached when a record's LOAD_STATUS_CODE advances past staging.
• XTR_DEAL_MIGRATION / XTR_DEAL_VALIDATION program units — the concurrent processes that read this table, apply validation, and transfer records into the deal tables.

In practice, any query or process addressing the interface should be keyed on (DEAL_TYPE, EXTERNAL_DEAL_ID) to exploit the unique index and avoid ambiguity when the same external identifier is reused across instrument types.