Results for “year_basis”

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

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

• ACTION_CODE, COMPANY_CODE, PORTFOLIO_CODE, PRODUCT_TYPE, DEAL_TYPE, DEAL_SUBTYPE — descriptive Treasury hierarchy attributes carried through via MAX aggregation.
• CURRENCYA / REVAL_CCY — the source and revaluation currencies used in the P&L computation.
• FACE_VALUE, FAIR_VALUE — summed principal and fair value amounts across grouped rows.
• REALISED_PL, UNREALISED_PL — summed realized and unrealized profit/loss figures, central to revaluation reporting.
• REVAL_RATE, TRANSACTION_RATE, EXCHANGE_RATE_ONE — averaged rates used in translation and revaluation.
• YEAR_BASIS — aggregated via MAX; this field conveys the day-count or year-basis convention applied to interest or revaluation calculations, which the user specifically searched for.
• PERIOD_FROM, PERIOD_TO, TRANSACTION_PERIOD, EFFECTIVE_DATE — period and date context for the aggregated result.
• REALIZED_FLAG, COMPLETE_FLAG, OVERWRITE_REASON — status and control flags; COMPLETE_FLAG is derived via DECODE on MIN(COMPLETE_FLAG), yielding 'Y' or 'N'.
• BATCH_ID, ACCOUNT_NO, REF_NUMBER, REVALUATION_DETAILS_ID — identifiers linking aggregated rows back to their source batches and accounts.

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.