Search Results deal_retro_test_id

Overview

The XTR_DEAL_RETRO_TESTS table is a core data object within the Oracle E-Business Suite (EBS) Treasury (XTR) module, present in both the 12.1.1 and 12.2.2 versions. Its primary function is to store the results of retrospective effectiveness tests performed at the individual deal level. In the context of hedge accounting under standards such as IAS 39 or ASC 815, entities must periodically demonstrate that a hedging relationship is highly effective in achieving offsetting changes in fair value or cash flows. This table serves as the system of record for the quantitative measurements and test outcomes that validate these hedge accounting designations, ensuring compliance and auditability for treasury transactions.

Key Information Stored

While the full column list is not detailed in the provided metadata, the primary and foreign key relationships indicate the critical data points stored. The table's primary key, DEAL_RETRO_TEST_ID, uniquely identifies each test record. The DEAL_NUMBER column links the test to a specific treasury transaction in the XTR_DEALS table. The HEDGE_ATTRIBUTE_ID associates the test with specific hedge accounting criteria defined in XTR_HEDGE_ATTRIBUTES. The HEDGE_RETRO_TEST_ID suggests a potential hierarchical or grouped test structure, possibly linking multiple deal-level tests to a higher-level assessment. The BATCH_ID foreign key to XTR_BATCHES indicates that these tests are often processed and generated in batch jobs. Typical data would include test calculation dates, effectiveness ratios (e.g., dollar-offset or regression analysis results), cumulative gain/loss amounts for the hedge and the hedged item, and the final assessment (effective/ineffective).

Common Use Cases and Queries

The primary use case is generating reports for hedge accounting compliance, internal audit, and external financial statement preparation. Treasury analysts run retrospective tests via batch processes, the results of which populate this table. Common queries involve retrieving test results for a specific deal or a portfolio of deals within a period to review effectiveness. For example, a query to find ineffective hedges for a given quarter might join XTR_DEAL_RETRO_TESTS to XTR_DEALS on DEAL_NUMBER and filter based on a calculated effectiveness ratio column (e.g., where RATIO not between 0.8 and 1.25). Another routine query would join to XTR_BATCHES on BATCH_ID to audit all tests processed in a specific batch run. Data from this table is also critical for the reconciliation between the treasury subledger and the general ledger for hedge accounting entries.

Related Objects

The table is centrally connected to several key Treasury tables via documented foreign key constraints:

• XTR_DEALS: Joined via DEAL_NUMBER. This is the master relationship, linking every test to the underlying financial deal (e.g., swap, forward, option).
• XTR_HEDGE_ATTRIBUTES: Joined via HEDGE_ATTRIBUTE_ID. This links the test result to the specific hedge accounting policy and method defined for the relationship.
• XTR_BATCHES: Joined via BATCH_ID. This associates the test record with the processing batch job that created or calculated it.
• Self-Referential Relationship: The foreign key on HEDGE_RETRO_TEST_ID points to the table's own primary key (DEAL_RETRO_TEST_ID). This structure likely allows for grouping individual deal tests under a consolidated hedge-level test or establishing a parent-child hierarchy for complex hedging relationships.