Search Results margin_period_fk_key

Overview

The view APPS.OPI_EDW_PRODUCT_SALES_REV_V is a denormalized reporting object within the Oracle E-Business Suite Operational Intelligence (OPI) data warehouse layer. It exposes product sales revenue aggregated across a broad set of dimensional keys — organization, operating unit, legal entity, calendar period, item, item category, sales channel, and geography. The view is designed to serve as a single source for analytical queries that require period-over-period sales revenue comparison, since it materializes both the current period revenue (sales_rev) and a trailing twelve-month value (prev_yr_sales) on the same row.

The user search term prod_amt_g corresponds directly to the underlying fact measure summed inside this view. In the inner query, SUM(prod_amt_g) sales_rev produces the revenue figure that the outer query then aliases as sales_rev. This confirms that prod_amt_g is the gross product amount grain-level column, and that the view exists to present that measure in an enriched, dimensionally-joined form suitable for EDW consumption.

Underlying Base Objects

The provided ETRM metadata records no explicitly documented base tables for this view. However, the embedded view text reveals the construction pattern. The innermost query selects directly from a fact source containing the columns item_org_fk_key, operating_unit_fk_key, sales_channel_fk_key, ship_to_loc_fk_key, margin_period_fk_key, cnam_cal_name_pk_key, and the measure prod_amt_g. This inner block is annotated with the comment "One Margin row for every Period - even if Zero," indicating the source is a margin fact structure that is densified so that every period appears for each dimensional combination, guaranteeing that zero-revenue periods are still represented.

The intermediate query aggregates prod_amt_g into sales_rev grouped by the foreign keys and calendar attributes. The outermost query then joins that result set to dimension aliases: org (organization, business group, legal entity, operating unit, and organization keys), margin (all-time, calendar period, quarter, and year keys together with cper_end_date), it (item, category, item number, and item-org keys), sch (sales channel keys), and geo (location, city, state, country, region, and area keys). These aliases resolve to the OPI/EDW dimension tables that conform the fact data to the warehouse star schema.

Key Columns

• ALL_ORGS_ID, BGRP_ID, LE_ID, OU_ID, ORG_ID — organization-dimension keys covering all-orgs rollup, business group, legal entity, operating unit, and inventory organization.
• ALL_TIME_ID, CAL_PERIOD_ID, CAL_QTR_ID, CAL_YEAR_ID — calendar dimension keys for period, quarter, and year granularity.
• ALL_ITEMS_ID, ITEM_CAT_ID, ITEM_ID, ITEM_NUMBER_ID, ITEM_ORG_ID — item dimension keys spanning category, revision, item number, and item-organization.
• ALL_SCH_ID, SCH_ID — sales channel dimension keys, identifying the channel through which revenue was realized.
• ALL_LOCS_ID, LOC_ID, CITY_ID, STATE_ID, COUNTRY_ID, REGION_ID, AREA_ID — geography dimension keys for ship-to location and its hierarchical geography.
• PERD_END_DATE — end date of the calendar period; LAST_YEAR_PERD_END_DATE is derived as ADD_MONTHS(cper_end_date, -12).
• SALES_REV — the summed prod_amt_g measure for the period.
• PREV_YR_SALES — prior-year revenue computed via LAG(margin.sales_rev, 12) partitioned by calendar name, item-org, operating unit, sales channel, and ship-to location, ordered by period end date.

Common Use Cases and Queries

A primary use case is year-over-year revenue variance analysis by item and organization. Because PREV_YR_SALES is pre-computed on the same row, analysts avoid self-joins against prior periods.

SELECT item_number_id, org_id, cal_period_id,
       sales_rev, prev_yr_sales
FROM   apps.opi_edw_product_sales_rev_v
WHERE  cal_year_id = :year
AND    ou_id = :operating_unit;

A second scenario rolls revenue up to the sales channel or geography level for dashboard reporting, where the presence of the all-* rollup keys (ALL_ORGS_ID, ALL_ITEMS_ID, ALL_LOCS_ID) allows slice-and-dice without additional grouping logic. Finally, the densified "every Period - even if Zero" design makes the view suitable for trend series and exception reporting, where zero-sales periods must be explicitly visible rather than dropped through inner joins. Queries should filter on the appropriate foreign keys and join to the matching dimension views to resolve descriptive attributes for the surrogate identifiers returned.