Search Results all_items_id




Overview
Explore in AI Chat

Overview

The OPI_EDW_PRODUCT_GROSS_MARGIN_V view is a reporting source object delivered under the OPI — Operations Intelligence product, and it is now classified as obsolete within Oracle E-Business Suite 12.1.1 and 12.2.2. Its stated purpose is to serve as the source view for the Product Gross Margin report, exposing pre-aggregated revenue and cost-of-goods-sold figures alongside the descriptive dimensions required to slice margin performance across organization, time, item, sales channel, and geography.

The view is a denormalized dimensional read model. It resolves foreign keys held in a margin fact table into the surrogate keys of the EDW conformed dimensions, allowing downstream reporting tools to join or filter on organization, calendar, item, channel, and location attributes without directly navigating the star schema. The metadata records that the object is not implemented in this database, so no runnable definition exists in the target instance; all statements below derive from the documented view text.

Underlying Base Objects

The view is defined over six documented warehouse objects. The single fact source is OPI_EDW_PERD_MARGIN_F, aliased MARGIN, which supplies the measures PROD_AMT_G (revenue) and COGS_G (cost of goods sold). It is joined to five conformed dimensions:

The joins are performed on natural surrogate-key relationships: operating unit, sales channel, item revision, ship-to location, and margin period. No other base objects are documented. Because the view is unimplemented, no column-level lineage to EBS transactional tables is recorded.

Key Columns

The column list is purely dimensional keys plus two measures. Notable entries include:

Common Use Cases and Queries

The principal use case is margin analysis by geographic and organizational dimension. A typical query aggregates revenue and COGS by city, filtered to a single operating unit and calendar year:

  • Margin by city: SELECT CITY_ID, SUM(SALES_REVENUE) REV, SUM(COGS) COGS, SUM(SALES_REVENUE - COGS) GM FROM OPI_EDW_PRODUCT_GROSS_MARGIN_V WHERE OU_ID = :ou AND CAL_YEAR_ID = :yr GROUP BY CITY_ID;
  • Margin by channel and category: grouping on SCH_ID and ITEM_CAT_ID.
  • Marginal trend: grouping on CAL_PERIOD_ID or CAL_QTR_ID.

Because the object is obsolete and absent from the instance, these queries are illustrative of the documented shape only. Customers on 12.1.1 or 12.2.2 should confirm availability or migrate to a supported replacement before relying on it.