Search Results realised_pl




Overview
Explore in AI Chat

Overview

APPS.XTR_REVALUATION_DETAILS_SUM_V is an Oracle E-Business Suite view owned by the APPS schema, delivered as part of the Treasury (ETRM) module. It presents a summarized, aggregated picture of revaluation activity recorded against treasury deals, consolidating detail rows from the underlying revaluation details table into grouped result sets. The view is defined as a UNION ALL of two or more aggregation blocks, each grouping the base revaluation data by BATCH_ID, ACCOUNT_NO, and REALIZED_FLAG while applying a distinct DEAL_TYPE and REALIZED_FLAG filter predicate. This design allows the view to surface, within a single queryable object, both realized and unrealized profit-and-loss balances alongside their associated fair value, gain/loss, and exchange rate attributes.

The view plays a reporting and integration role: it is typically consumed by treasury reporting screens, accounting event processing, and downstream reconciliation logic that requires summarized revaluation balances rather than row-level detail. The presence of summed measures such as UNREALISED_PL, REALISED_PL, FAIR_VALUE, FACE_VALUE, CURR_GAIN_LOSS_AMOUNT, and SOB_FV_GAIN_LOSS_AMOUNT confirms its purpose as an analytical aggregation layer over revaluation transactions.

Underlying Base Objects

The documented base object referenced by this view is XTR_REVALUATION_DETAILS, accessed through a synonym. All projected columns in the view originate from this single table. The view does not introduce joins to independent dimension or lookup tables; instead it derives its summarized output purely through aggregate functions and GROUP BY clauses applied to XTR_REVALUATION_DETAILS.

The view text shows a structure of UNION ALL branches. Each branch filters the base table by DEAL_TYPE (for example, DEAL_TYPE = 'CA' with REALIZED_FLAG = 'Y' in the first branch) and groups by BATCH_ID, ACCOUNT_NO, and REALIZED_FLAG. Columns that participate in the grouping key (such as BATCH_ID, ACCOUNT_NO, and REALIZED_FLAG) appear as pass-through values, while all other columns are reduced through MAX, MIN, SUM, or AVG aggregates. This pattern enables multiple deal-type and realized/unrealized segments to be combined into one homogeneous result set with a consistent column list, achieved by padding unmatched columns with NULL or to_number(NULL) placeholders.

Key Columns

  • UNREALISED_PL — SUM aggregate of the unrealized profit or loss amount. This is the column most directly associated with the search term "unrealised_pl" and represents mark-to-market revaluation gains or losses not yet realized.
  • REALISED_PL — SUM aggregate of realized profit or loss for the grouped segment.
  • REVAL_CCY / REVAL_RATE — Revaluation currency (MAX) and the average revaluation rate (AVG) applied to the balances.
  • TRANSACTION_RATE / EXCHANGE_RATE_ONE — AVG aggregates representing transaction-level and secondary exchange rates used in the revaluation calculation.
  • FAIR_VALUE / FACE_VALUE — SUM aggregates of fair value and face (notional) value of the underlying deals.
  • CURR_GAIN_LOSS_AMOUNT / SOB_FV_GAIN_LOSS_AMOUNT — SUM aggregates capturing currency gain/loss and set-of-books fair value gain/loss amounts.
  • ACCOUNT_NO — Grouping key (aliased as REF_NUMBER) identifying the accounting or treasury account.
  • BATCH_ID — Grouping key identifying the revaluation batch run.
  • REALIZED_FLAG — Grouping key and filter distinguishing realized from unrealized records.
  • COMPLETE_FLAG — Derived via DECODE on MIN(COMPLETE_FLAG), returns 'Y' only when the minimum flag is 'Y', otherwise 'N'.
  • REVALUATION_DETAILS_ID — MIN aggregate surrogate identifier from the base table.
  • Audit and context columns — ACTION_CODE, COMPANY_CODE, DEAL_TYPE, DEAL_SUBTYPE, PORTFOLIO_CODE, PRODUCT_TYPE, PERIOD_FROM, PERIOD_TO, TRANSACTION_PERIOD, YEAR_BASIS, AMOUNT_TYPE, ENTERED_BY/ON, UPDATED_BY/ON, CREATED_BY/ON, and OVERWRITE_REASON.

Common Use Cases and Queries

The view is most commonly queried to report unrealized and realized revaluation results by batch and account. A typical query retrieves summarized P&L by batch:

  • SELECT batch_id, account_no, realiZed_flag, unrealised_pl, realised_pl FROM xtr_revaluation_details_sum_v WHERE account_no = :account_no;
  • SELECT batch_id, SUM(unrealised_pl) FROM xtr_revaluation_details_sum_v GROUP BY batch_id;
  • SELECT * FROM xtr_revaluation_details_sum_v WHERE complete_flag = 'Y' AND reval_ccy = :currency;

These queries support period-end revaluation reporting, reconciliation of realized versus unrealized balances, and verification of batch completion status before posting accounting entries. Because the view pre-aggregates the base table, it is preferable to querying XTR_REVALUATION_DETAILS directly when summarized balances are required.