Search Results edw_time_m




Overview
Explore in AI Chat

Overview

OPI_EDW_PRODUCT_SALES_REV_V is a reporting source view that belongs to the Operations Intelligence (OPI) product family, now classified as obsolete within the Oracle E-Business Suite 12.1.1 and 12.2.2 code lines. The view was designed to serve as the data source for the Product Sales Revenue report, exposing a denormalized, business-intelligence-ready projection of product sales revenue across multiple analytical dimensions. It draws its data from a star-schema style warehouse layer — the EDW_* master dimension tables and the OPI_EDW_PERD_MARGIN_F fact table — rather than from the transactional EBS tables directly.

The view's principal purpose is to present current-period sales revenue alongside the corresponding prior-year (twelve months earlier) figure for the same dimensional combination, enabling period-over-period and year-over-year comparison without requiring the report to perform self-joins. In the ETRM 12.2.2 metadata this object is documented as "Not implemented in this database," meaning no instance of the view exists in the target environment; the definition is retained only as reference documentation.

Underlying Base Objects

The documented ETRM metadata lists no referenced base objects for this view, so the dependency graph must be inferred from the embedded view text. The SELECT is constructed from two identical inline subqueries aliased MARGIN1 and MARGIN2, each formed by joining OPI_EDW_PERD_MARGIN_F to EDW_TIME_M on the predicate MARGIN_PERIOD_FK_KEY = CDAY_CAL_DAY_PK_KEY. These two derived sets are outer-joined on the prior-year offset, with the (+) operator applied to every MARGIN2 join key. Four master dimension tables complete the statement: EDW_ORGANIZATION_M (ORG), EDW_OE_SLCHNL_M (SCH), EDW_ITEMS_M (IT), and EDW_GEOGRAPHY_M (GEO). Each dimension contributes its surrogate ALL_* key plus its most granular foreign key column, producing the fully cross-dimensional result set characteristic of an EDW semantic layer.

Key Columns

Common Use Cases and Queries

Because the view is obsolete and unimplemented, its principal value is as a migration or reconstruction template for teams rebuilding equivalent EDW semantic-layer reporting. Typical queries aggregate the current and prior-year measures by any dimension key:

  • Year-over-year revenue by operating unit, calendar year, and item category.
  • Prior-year comparison by sales channel and region for trend reporting.
  • Period-end revenue snapshots filtered on PERD_END_DATE.

A representative query:

SELECT CAL_YEAR_ID, OU_ID, ITEM_CAT_ID,
  SUM(SALES_REV) CURRENT_REV,
  SUM(PREV_YR_SALES) PRIOR_REV,
  SUM(SALES_REV) - SUM(PREV_YR_SALES) VARIANCE
FROM OPI_EDW_PRODUCT_SALES_REV_V
WHERE PERD_END_DATE = :p_period_end
GROUP BY CAL_YEAR_ID, OU_ID, ITEM_CAT_ID;

Use of the (+) outer-join syntax in the view definition also serves as a reminder that this SQL predates the ANSI join standard and should be reviewed before reuse in a 12.2.2 environment.