Search Results edw_items_m




Overview
Explore in AI Chat

Overview

OPI_EDW_INV_QTY_PERD_V is an APPS-owned, valid database view in Oracle E-Business Suite that serves as the source for the On Hand Inventory Quantity report within the OPI (Operations Intelligence) product family. Operations Intelligence provides the analytics and reporting layer for process manufacturing and discrete inventory operations, and this view exposes period-end on-hand inventory quantities in a denormalized, dimension-oriented form suitable for reporting and extract-transform-load consumption. It is a reporting view rather than a transactional object; it does not store data itself but projects columns from underlying fact and dimension tables maintained in the EDW (Enterprise Data Warehouse) schema family used by OPI.

The view consolidates locator, organization, plant, item, unit of measure, and calendar period context against a period-end on-hand quantity measure. This structure allows downstream reports and dashboards to aggregate inventory balances by company, operating unit, plant, stock room (subinventory), item category, or calendar period without repeatedly joining the underlying star-schema constructs.

Underlying Base Objects

The view text defines five referenced objects: OPI_EDW_INV_PERD_STAT_F (the period inventory statistic fact table, aliased IPS), EDW_MTL_INVENTORY_LOC_M (the inventory location dimension, aliased INV), EDW_ITEMS_M (the items dimension, aliased IT), EDW_TIME_M (the time dimension, aliased TIME), and EDW_MTL_UOM_M (the unit of measure dimension, aliased UOM). Joins are defined on surrogate primary keys: the locator key (INVL_LOCATOR_PK_KEY) to the fact locator foreign key, the calendar day key to the fact period date foreign key, the item revision key to the item-org foreign key, and the base UOM key to the fact base UOM foreign key.

The ETRM metadata records no referenced base objects beyond this view definition, reflecting that the documentation captures the view layer rather than the full physical model. In practice, these EDW dimension and fact tables are populated by OPI's collection and transformation programs, which draw from Oracle Inventory and process manufacturing source tables.

Key Columns

  • ALL_LOCATORS_ID — Surrogate key (ALL_PK_KEY) for the inventory locator dimension.
  • PCMP_ID / PORG_ID / OPERATING_UNIT_ID / PLANT_ID — Company, process organization, operating unit, and plant dimension keys.
  • SUB_INV_ID — Stock room / subinventory key, mapped from SUBI_STOCK_ROOM_PK_KEY. This is the column most commonly associated with the user search term "sub_inv_id," since the view surfaces the subinventory at the surrogate-key level rather than by name.
  • LOCATOR_ID — Locator dimension key, resolving to the physical storage position.
  • CAL_PERIOD_ID — Calendar period key from the time dimension, identifying the reporting period.
  • ALL_ITEMS_ID / ITEM_CAT_ID / ITEM_ID — Item-level keys: the item surrogate, the category, and the item revision.
  • UOM_ID — Unit of measure dimension key for the base UOM.
  • EONH_INV_QTY — The measure: period end on-hand inventory quantity, defaulted to zero via NVL where the fact value is null.

Common Use Cases and Queries

Typical uses include period-end on-hand reporting, inventory trend analysis by subinventory or plant, and extraction into external BI platforms. Because the view returns surrogate keys, reporting queries often join the view to the corresponding EDW dimensions to obtain descriptive names. A representative query filtering by subinventory and period follows:

  • SELECT sub_inv_id, plant_id, item_id, cal_period_id, SUM(eonh_inv_qty) FROM apps.opi_edw_inv_qty_perd_v WHERE sub_inv_id = :p_sub_inv_id GROUP BY sub_inv_id, plant_id, item_id, cal_period_id;
  • Join to EDW_MTL_INVENTORY_LOC_M or the applicable subinventory dimension on the surrogate key to resolve descriptive labels.
  • Filter by operating_unit_id or plant_id to scope results for a specific reporting entity.

Consumers should note that joins to the fact table are inner joins, so periods or locators lacking fact rows are excluded; the NVL on EONH_INV_QTY applies only where a fact row exists with a null measure.