Results for “xtr_hedge_retro_tests”

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

Overview

XTR_HEDGE_RETRO_TESTS is a Treasury (XTR) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores hedge-level retrospective effectiveness test and measurement details. Retrospective effectiveness testing is a core requirement of hedge accounting under ASC 815 / IAS 39, and this table persists the periodic results produced when the Treasury Hedge Manager evaluates whether a hedging relationship has been and continues to be highly effective in offsetting changes in fair value or cash flows of the hedged item. Each row represents a single retrospective test execution for a given hedge attribute, capturing the outcome, result date, and the dollar-decomposition of gains and losses required for journal entries and disclosure reporting. The table is owned by the XTR schema and is documented with 47 columns.

Under the heuristic Data Vault classification mined from its foreign key structure, XTR_HEDGE_RETRO_TESTS is best modeled as a link table. This is a modeling suggestion only: it carries the surrogate primary key HEDGE_RETRO_TEST_ID while resolving relationships between party/organization data (COMPANY_CODE → XTR_PARTY_INFO), hedge attribute definitions (HEDGE_ATTRIBUTE_ID → XTR_HEDGE_ATTRIBUTES), and batch execution context (BATCH_ID → XTR_BATCHES). The measured gain/loss amounts behave as link-level descriptive measures rather than pure reference attributes.

Key Information Stored

The surrogate primary key is HEDGE_RETRO_TEST_ID, enforced by the XTR_HEDGE_RETRO_TESTS_PK constraint and also carried by the unique index XTR_HEDGE_RETRO_TESTS_U1, making it the business-key candidate for stable identification. The most significant columns fall into three groups:

• Relationship keys: COMPANY_CODE (references XTR_PARTY_INFO), HEDGE_ATTRIBUTE_ID (references XTR_HEDGE_ATTRIBUTES), and BATCH_ID (references XTR_BATCHES).
• Result and status: RESULT_CODE, RESULT_DATE, COMPLETE_FLAG, BATCH_ID, COMMENTS, and GAIN_LOSS_CCY, which together describe the test outcome, its effective date, completion state, and the currency of all monetary measures.
• Measurement amounts: AMOUNT_TYPE distinguishes the measurement basis; ITEM_PRD_GAIN_LOSS_AMT and INST_PRD_GAIN_LOSS_AMT hold period item and instrument gains/losses; ITEM_CUM_GAIN_LOSS_AMT, INST_CUM_GAIN_LOSS_AMT, EFF_CUM_GAIN_LOSS_AMT, INEFF_CUM_GAIN_LOSS_AMT, and EXCLUDED_CUM_GAIN_LOSS_AMT hold cumulative amounts; PCT_EFFECTIVE holds the calculated effectiveness percentage; EFF_PRD_GAIN_LOSS_AMT, INEFF_PRD_GAIN_LOSS_AMT, EXCLUDED_PRD_GAIN_LOSS_AMT, and RECLASS_GAIN_LOSS_AMT hold period-level and reclassification amounts.
• Audit and DESCRIBE columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, PROGRAM_ID, PROGRAM_LOGIN_ID, PROGRAM_APPLICATION_ID, and REQUEST_ID track concurrency, user, and concurrent program context; the fifteen ATTRIBUTE1–ATTRIBUTE15 plus ATTRIBUTE_CATEGORY columns provide the standard EBS descriptive flexfield.

Common Use Cases and Queries

Typical uses include reviewing effectiveness history for a hedge relationship, validating that PCT_EFFECTIVE falls within the 80–125% "highly effective" threshold, reconciling period gain/loss amounts against GL postings, and reproducing disclosure schedules. A representative query joining the three foreign keys:

• SELECT r.HEDGE_RETRO_TEST_ID, r.RESULT_DATE, r.RESULT_CODE, r.PCT_EFFECTIVE, r.AMOUNT_TYPE, r.EFF_PRD_GAIN_LOSS_AMT, r.INEFF_PRD_GAIN_LOSS_AMT, r.GAIN_LOSS_CCY FROM XTR.XTR_HEDGE_RETRO_TESTS r, XTR.XTR_HEDGE_ATTRIBUTES a, XTR.XTR_PARTY_INFO p WHERE r.HEDGE_ATTRIBUTE_ID = a.HEDGE_ATTRIBUTE_ID AND r.COMPANY_CODE = p.COMPANY_CODE AND r.RESULT_DATE BETWEEN :from_date AND :to_date ORDER BY r.RESULT_DATE;
• Effectiveness exception report: filter on PCT_EFFECTIVE < 0.80 OR PCT_EFFECTIVE > 1.25 (or the stored scale) grouped by HEDGE_ATTRIBUTE_ID.
• Batch reconciliation: join to XTR_BATCHES on BATCH_ID and filter COMPLETE_FLAG to isolate unfinished test runs.
• Audit trail extraction using REQUEST_ID and PROGRAM_ID to trace the concurrent program that created each measurement row.

Related Objects

The foreign keys documented above identify the principal related objects and join columns:

• XTR_PARTY_INFO — joined on COMPANY_CODE; supplies legal entity/organization context.
• XTR_HEDGE_ATTRIBUTES — joined on HEDGE_ATTRIBUTE_ID; defines the hedge relationship being tested.
• XTR_BATCHES — joined on BATCH_ID; identifies the concurrent batch run executing the tests.
• Other XTR Treasury objects commonly referenced alongside this table include hedge designation and effectiveness test result tables, the Treasury accounting/GL interface tables that consume the gain/loss amounts, and the concurrent programs registered under PROGRAM_APPLICATION_ID/PROGRAM_ID that populate the table.

Because the table is a link-style measurement store, DBA maintenance focuses on the primary key and unique index (XTR_HEDGE_RETRO_TESTS_PK, XTR_HEDGE_RETRO_TESTS_U1) plus the three foreign-key indexes required for efficient joins and purging.