Search Results xtr_validate_transactions_v

Overview

XTR_VALIDATE_TRANSACTIONS_V is a Treasury (XTR) reporting view in Oracle E-Business Suite 12.1.1 and 12.2.2 that consolidates Treasury deal and transaction records into a single validation-oriented result set. Its distinguishing feature is a discriminator column named VALIDATE_TYPE, which classifies each row as one of three categories: TRANS, DEAL, or SETTLE. This design allows Treasury validation and authorization processes to query one unified structure rather than three separate source views, simplifying the identification of records awaiting dual authorization or settlement validation.

The view is documented as not implemented at the database level in the source environment, and no base objects are formally registered against it in the ETRM metadata. Nevertheless, its view text is fully defined and references other Treasury views. The view serves as a logical reporting and integration layer, exposing deal-level attributes alongside user-friendly deal type and subtype descriptions resolved from configuration tables.

Underlying Base Objects

The view text is constructed from three branches combined with UNION ALL:

• XTR_ROLLOVER_TRANSACTIONS_V (aliased A) — supplies the TRANS branch, restricted to deal types ONC and CMF.
• XTR_DEALS_V (aliased A) — supplies both the DEAL branch (deal types not in ONC/CMF) and the SETTLE branch, the latter additionally filtered by a non-null DUAL_AUTHORISATION_BY and a non-null SETTLE_DATE.
• XTR_DEAL_TYPES (aliased DT) — configuration table joined on DEAL_TYPE to resolve USER_DEAL_TYPE.
• XTR_DEAL_SUBTYPES (aliased ST) — configuration table joined on the composite key DEAL_TYPE and DEAL_SUBTYPE to resolve USER_DEAL_SUBTYPE.

All three branches share identical join predicates against the deal type and subtype configuration tables, ensuring consistent descriptive enrichment across the union.

Key Columns

• VALIDATE_TYPE — the union discriminator; values are TRANS, DEAL, or SETTLE. This is the principal filter column.
• DEAL_NUM — deal number, sourced from DEAL_NUMBER (transactions branch) or DEAL_NO (deals branch).
• TRANS_NUM — transaction number, sourced from TRANSACTION_NUMBER or TRANSACTION_NO.
• CPARTY_CODE — counterparty code associated with the deal.
• DEAL_TYPE / USER_DEAL_TYPE — internal code and its user-facing description.
• DEAL_SUBTYPE / USER_DEAL_SUBTYPE — internal subtype code and its user-facing description.
• PRODUCT_TYPE — Treasury product classification.
• DEAL_DATE — date of the deal.
• DUAL_AUTHORISATION_BY / DUAL_AUTHORISATION_ON — for the SETTLE branch these are drawn from SETTLE_DUAL_AUTHORISATION_BY and SETTLE_DUAL_AUTHORISATION_ON, reflecting settlement authorization rather than deal authorization.

Common Use Cases and Queries

The view supports pending-authorization reporting, settlement reconciliation, and integration extracts where a single, categorized feed of Treasury activity is required. Because VALIDATE_TYPE partitions the data, consumers can isolate the population relevant to each stage.

Retrieving all pending settlement validations:

SELECT validate_type, deal_num, trans_num, cparty_code,
       user_deal_type, user_deal_subtype,
       dual_authorisation_by, dual_authorisation_on
FROM   xtr_validate_transactions_v
WHERE  validate_type = 'SETTLE';

Counting records by validation category for a dashboard:

SELECT validate_type, COUNT(*) rec_count
FROM   xtr_validate_transactions_v
GROUP  BY validate_type;

Locating deals of a specific user-facing type and subtype:

SELECT deal_num, trans_num, deal_date, product_type
FROM   xtr_validate_transactions_v
WHERE  user_deal_type = :p_deal_type
AND    user_deal_subtype = :p_subtype
AND    validate_type = 'DEAL';

Because the view is documented as not implemented in the source database, administrators should confirm its presence and validity in each target instance before relying on it in concurrent programs, BI Publisher reports, or outbound interfaces.