Search Results avg_onh_val




Overview
Explore in AI Chat

Overview

OPI_EDW_INV_TURNS_PERD_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the OPI – Operations Intelligence product family. In both 12.1.1 and 12.2.2, its documented purpose is to serve as the source view for the Inventory Turns (Period Level) report. The view exposes period-bucketed inventory statistics that allow analysts and report consumers to evaluate how efficiently inventory is being consumed relative to the average on-hand balance held during a given calendar period.

Its role is strictly analytical: it does not store data and performs no transactional writes. Instead, it joins pre-aggregated Operations Intelligence fact data with the Enterprise Data Warehouse inventory location and time dimensions, producing a denormalized, report-ready result set that maps internal surrogate keys to meaningful business identifiers such as plant, operating unit, subinventory, and item organization.

Underlying Base Objects

The ETRM documentation for 12.2.2 lists no explicitly documented base objects for this view, but the published view text identifies three source objects in the FROM clause:

  • OPI_EDW_INV_PERD_STAT_F – the inventory period statistics fact table, which supplies the cost of goods sold and average on-hand value measures.
  • EDW_MTL_INVENTORY_LOC_M – the material inventory location dimension, which supplies the locator, company, organization, operating unit, plant, and subinventory keys.
  • EDW_TIME_M – the time dimension, which supplies the calendar period key and the period end date used to filter out future periods.

Joins are made between the fact's locator foreign key and the inventory location key, and between the fact's period date foreign key and the day-level key in the time dimension. A predicate restricts output to periods where CPER_END_DATE is less than or equal to the current system date.

Key Columns

  • ALL_LOCATORS_ID – maps to the inventory locator primary key (INV.ALL_PK_KEY), identifying the specific stocking locator.
  • PCMP_ID – OPM company key from the inventory location dimension.
  • PORG_ID – OPM organization key.
  • OPERATING_UNIT_ID – operating unit key for multi-org reporting.
  • PLANT_ID – plant key (INV.PLNT_PLANT_PK_KEY); this is the column returned when users search on "plant_id," and it is the principal plant-level grouping attribute in the view. In the delivered text PLANT_ID is populated from the location dimension's plant surrogate key, so it is a warehouse identifier rather than a descriptive plant name.
  • SUB_INV_ID – subinventory key, enabling analysis below the plant level.
  • CAL_PERIOD_ID – calendar period key from EDW_TIME_M; the finer-grain day, year, and quarter keys are commented out in the delivered definition.
  • ITEM – item organization foreign key (ITEM_ORG_FK_KEY) identifying the inventory item.
  • COGS – cost of goods sold, derived as a negative of TOT_CUST_SHIP_VAL_G.
  • AVG_ONH_VAL – average on-hand value for the period.

Common Use Cases and Queries

Inventory turns are typically computed as COGS divided by AVG_ONH_VAL for a period, optionally annualized by multiplying by the number of periods per year. A representative query grouping by plant is:

  • SELECT PLANT_ID, CAL_PERIOD_ID, SUM(COGS) COGS, SUM(AVG_ONH_VAL) AVG_ONH_VAL, SUM(COGS)/NULLIF(SUM(AVG_ONH_VAL),0) TURNS FROM APPS.OPI_EDW_INV_TURNS_PERD_V GROUP BY PLANT_ID, CAL_PERIOD_ID ORDER BY PLANT_ID, CAL_PERIOD_ID;
  • Filtering by a specific plant using a WHERE PLANT_ID = :plant_id predicate when reconciling turns against a single facility.
  • Drilling from plant to subinventory by adding SUB_INV_ID to the select list and grouping, useful for identifying slow-moving stockrooms.
  • Item-level turns analysis by grouping on ITEM after restricting PLANT_ID to the relevant organization.

Because the view returns surrogate keys rather than descriptions, it is normally joined back to the corresponding OPI or EDW dimensions, or consumed through the Operations Intelligence reporting layer, when descriptive plant, item, or period labels are required.