Search Results opi_cogs_001_mv

Overview

OPI_COGS_001_MV is a materialized view owned by the APPS schema and registered under the FND – Application Object Library product in Oracle E-Business Suite 12.1.1 and 12.2.2. Despite the FND product assignment, its column structure – organization, item, category, time dimensions, and multiple Cost of Goods Sold (COGS) value measures – identifies it as a reporting aggregation object used by Oracle's manufacturing cost and inventory analytics. The object contains twenty documented columns and carries a status of VALID in the ETRM 12.1.1 physical schema snapshot.

The metadata's heuristic Data Vault classification is standalone, meaning the mined foreign-key topology does not connect this object into a broader hub/link/satellite model. As a modeling suggestion, this is consistent with an aggregate reporting structure: it behaves less like a normalized transactional table and more like a self-contained summary, refreshed from underlying COGS source tables. The single documented foreign key, OPI_COGS_001_MV.YEAR_ID → JAI_FA_AST_YEARS, reflects a shared fiscal year dimension rather than a strong operational lineage.

Key Information Stored

• Organization and item dimensions: ORGANIZATION_ID and ITEM_ORG_ID identify the inventory organization and item organization context for each aggregation row; INV_CATEGORY_ID and UOM_CODE further qualify the category and unit of measure grouping.
• Aggregation control: AGGREGATION_LEVEL_FLAG and GRP_ID determine the granularity at which each record was summarized, distinguishing rolled-up totals from more detailed contributions.
• Time dimension: TIME_ID, PERIOD_TYPE_ID, YEAR_ID, QTR_ID, MONTH_ID, WEEK_ID, and DAY_ID provide a full calendar hierarchy for period-based COGS reporting.
• Measures: The value columns represent COGS figures by cost basis – COGS_VAL_B, COGS_VAL_G, and COGS_VAL_SG – paired with count columns CNT_COGS_VAL_B, CNT_COGS_VAL_G, and CNT_COGS_VAL_SG, plus a CNT_TOTAL rollup. The suffixes correspond to cost elements used in Oracle cost accounting (for example, burdened, group, and standard/subgroup bases).

Because this is a materialized view, the physical row identity is the aggregate key combination rather than a single surrogate column. The unique index I_SNAP$_OPI_COGS_001_MV (a snapshot index generated by materialized view refresh) enforces uniqueness across ORGANIZATION_ID, INV_CATEGORY_ID, ITEM_ORG_ID, UOM_CODE, YEAR_ID, QTR_ID, MONTH_ID, WEEK_ID, DAY_ID, AGGREGATION_LEVEL_FLAG, and GRP_ID. This composite constitutes the business-key candidate for the object.

Common Use Cases and Queries

Typical use cases include period-end COGS analysis, cost element variance reporting, and inventory valuation dashboards. A representative query aggregates the measures across organizations for a fiscal quarter:

SELECT organization_id, inv_category_id, SUM(cogs_val_b) cogs_b, SUM(cnt_total) rows_cnt FROM apps.opi_cogs_001_mv WHERE year_id = :year AND qtr_id = :qtr GROUP BY organization_id, inv_category_id ORDER BY organization_id;

A drill-down pattern joins YEAR_ID to the fiscal calendar to translate surrogate period values into readable dates, while filtering on AGGREGATION_LEVEL_FLAG isolates the intended summary tier and prevents double counting across granularities. Trend reports compare COGS_VAL_SG to COGS_VAL_G over MONTH_ID to highlight standard-versus-group cost deltas.

Related Objects

• JAI_FA_AST_YEARS – joined via YEAR_ID; provides the fiscal year definition referenced by the documented foreign key.
• MTL_SYSTEM_ITEMS_B / MTL_ITEM_CATEGORIES – resolve ITEM_ORG_ID and INV_CATEGORY_ID to item and category descriptions.
• MTL_UNITS_OF_MEASURE – decodes UOM_CODE into descriptive unit names.
• HR_ALL_ORGANIZATION_UNITS / ORG_ORGANIZATION_DEFINITIONS – translate ORGANIZATION_ID into legal entity and operating unit context.
• GL_PERIODS / FND_CALENDAR – map PERIOD_TYPE_ID and the calendar columns to accounting periods.
• CST_COGS_ELEMENTS and related costing views – supply the underlying COGS cost element basis reflected in the _B, _G, and _SG measures.
• OPI_COGS_001_MV indexes (I_SNAP$_…) – refresh artifacts that support query performance and enforce the aggregate uniqueness constraint.