Results for “hedge_attribute_id”

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

Overview

XTR_DEAL_RETRO_TESTS is a Treasury (XTR) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores deal-level retrospective effectiveness test and measurement details. It supports hedge accounting under IAS 39 / IFRS 9 by persisting the outcome of retrospective effectiveness assessments performed against hedging relationships at the individual deal level. Because effectiveness testing is a recurring, batch-driven activity, the table is populated by concurrent programs and consumed by reporting and accounting engines.

Based on the foreign key structure, the table can be modeled as a link in a Data Vault heuristic — it connects a deal (XTR_DEALS), a hedge attribute definition (XTR_HEDGE_ATTRIBUTES), a batch run (XTR_BATCHES), and a related hedge-level retrospective test record (self-referencing HEDGE_RETRO_TEST_ID). This classification is a modeling suggestion rather than an Oracle-documented designation.

Key Information Stored

The table is defined with 23 columns and a single-column primary key, XTR_DEAL_RETRO_TESTS_PK, on DEAL_RETRO_TEST_ID. A unique index, XTR_DEAL_RETRO_TESTS_U1, also exists on DEAL_RETRO_TEST_ID, making it the primary business-key candidate in addition to the surrogate primary key.

• DEAL_RETRO_TEST_ID — surrogate primary key and unique-indexed business identifier of each test row.
• DEAL_NUMBER — foreign key to XTR_DEALS; identifies the deal being tested.
• HEDGE_ATTRIBUTE_ID — foreign key to XTR_HEDGE_ATTRIBUTES; identifies the hedge attribute/relationship definition.
• HEDGE_RETRO_TEST_ID — self-referencing foreign key linking this deal-level record to its hedge-level retrospective test.
• BATCH_ID — foreign key to XTR_BATCHES; identifies the concurrent batch run that produced the measurement.
• AMOUNT_TYPE — classifies the measurement amount (e.g., gain/loss categorization).
• EFF_PRD_GAIN_LOSS_AMT — periodic effective portion gain/loss amount.
• INEFF_PRD_GAIN_LOSS_AMT — periodic ineffective portion gain/loss amount.
• EXCLUDED_PRD_GAIN_LOSS_AMT — periodic excluded portion (e.g., time value) gain/loss amount.
• RECLASS_GAIN_LOSS_AMT — amount reclassified to earnings.
• EFF_CUM_GAIN_LOSS_AMT, INEFF_CUM_GAIN_LOSS_AMT, EXCLUDED_CUM_GAIN_LOSS_AMT — cumulative counterparts of the periodic figures.
• RESULT_DATE — date of the effectiveness result.
• Standard WHO/audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, plus PROGRAM_ID, PROGRAM_LOGIN_ID, PROGRAM_APPLICATION_ID, and REQUEST_ID for traceability to the originating concurrent request.

Common Use Cases and Queries

The table is primarily queried for effectiveness testing reports, hedge accounting audit trails, and reconciliation of periodic versus cumulative amounts. A typical pattern joins to XTR_DEALS to obtain deal context and to XTR_BATCHES to identify the run:

• Extract all retrospective tests for a specific deal and result date range.
• Compare EFF_PRD_GAIN_LOSS_AMT against INEFF_PRD_GAIN_LOSS_AMT to determine effectiveness ratios.
• Report cumulative amounts (EFF_CUM_, INEFF_CUM_, EXCLUDED_CUM_) for period-end close.
• Trace test rows back to the concurrent program via REQUEST_ID and PROGRAM_ID.

A representative query:

SELECT r.DEAL_NUMBER, r.RESULT_DATE, r.AMOUNT_TYPE, r.EFF_PRD_GAIN_LOSS_AMT, r.INEFF_PRD_GAIN_LOSS_AMT, r.EFF_CUM_GAIN_LOSS_AMT FROM XTR_DEAL_RETRO_TESTS r WHERE r.DEAL_NUMBER = :deal AND r.RESULT_DATE BETWEEN :from_date AND :to_date ORDER BY r.RESULT_DATE;

Related Objects

The following objects are related through the documented foreign keys and primary key relationship:

• XTR_DEALS — joined on DEAL_NUMBER; source of deal definition.
• XTR_HEDGE_ATTRIBUTES — joined on HEDGE_ATTRIBUTE_ID; defines hedging relationship attributes.
• XTR_BATCHES — joined on BATCH_ID; identifies the originating batch execution.
• XTR_DEAL_RETRO_TESTS — self-reference via HEDGE_RETRO_TEST_ID, linking deal-level rows to hedge-level test records.

These relationships make the table a central link between deals, hedge definitions, and batch-driven effectiveness processing within the Treasury module.