Search Results xtr_revaluation_details_v




Overview
Explore in AI Chat

Overview

XTR_REVALUATION_DETAILS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, defined within the XTR (Treasury) product module. Its status is VALID in both Oracle EBS 12.1.1 and 12.2.2. The view consolidates revaluation detail records from the Treasury revaluation engine, exposing aggregated profit and loss figures, currency and rate information, and period-related attributes across deals. Because the revaluation process for Treasury instruments can span multiple deal categories and realized/unrealized states, the view presents a denormalized, group-by aggregated result set rather than a simple projection of a single base table.

Underlying Base Objects

The view is defined over XTR_REVALUATION_DETAILS (referenced through a synonym). The defining SQL is a compound query composed of multiple UNION ALL branches. The first branch filters on DEAL_TYPE = 'CA' and REALIZED_FLAG IN ('Y', 'N'), aggregating rows by BATCH_ID, ACCOUNT_NO, and REALIZED_FLAG. A second branch applies conditional decoding of deal types such as 'ONC' and 'IG', using DECODE expressions to selectively surface contract codes, deal numbers, and effective dates. Both branches apply aggregation functions (MAX, MIN, SUM, AVG) to numeric and date fields, which means the view collapses many row-level revaluation detail records into grouped summaries.

Key Columns

Common Use Cases and Queries

The view is typically used for Treasury revaluation reporting, P&L reconciliation, and downstream extracts where aggregated realized and unrealized positions by batch, account, and realized flag are required. A representative query retrieving the year basis alongside grouped revaluation amounts:

  • SELECT BATCH_ID, ACCOUNT_NO, YEAR_BASIS, REALISED_PL, UNREALISED_PL, REVAL_CCY FROM APPS.XTR_REVALUATION_DETAILS_V WHERE REALIZED_FLAG = 'Y';
  • SELECT DEAL_TYPE, SUM(FACE_VALUE) FACE_VALUE, SUM(UNREALISED_PL) UNREALISED_PL FROM APPS.XTR_REVALUATION_DETAILS_V GROUP BY DEAL_TYPE;
  • Filtering by COMPLETE_FLAG = 'Y' to isolate fully processed revaluation batches before feeding treasury P&L reports or reconciliations.

Because YEAR_BASIS is aggregated with MAX, consumers should treat it as representative of the grouped revaluation detail set rather than a guaranteed uniform value across all constituent rows.