Search Results item_number_id

Overview

APPS.OPI_EDW_INV_VALUE_PERD_V is an Oracle E-Business Suite database view belonging to the Operational Intelligence / Enterprise Data Warehouse (OPI/EDW) reporting layer. It presents a consolidated, period-based inventory valuation fact set by joining the stored inventory statistics fact table OPI_EDW_INV_PERD_STAT_F against the EDW location, item, and time dimension tables. The view exposes surrogate key identifiers (the "_PK_KEY" and "_ID" columns) alongside three valued inventory measures, providing a denormalized structure optimised for ad-hoc querying, BI Publisher reports, Discoverer workbooks, and downstream data warehouse extraction rather than for transactional data entry.

The view is relevant to both Oracle EBS 12.1.1 and 12.2.2, as the EDW/OPI star-schema objects are independent of the underlying Oracle Forms-based application schema and remain consistent across these releases. It is a read-only object; no DML is supported.

Underlying Base Objects

The documented view definition joins four objects. Three are EDW dimension master tables and one is the inventory period statistics fact table:

• OPI_EDW_INV_PERD_STAT_F — the inventory period statistical fact table, aliased IPS. This is the primary source of the valuation measures.
• EDW_MTL_INVENTORY_LOC_M — the inventory location dimension, aliased INV, joined on INV.INVL_LOCATOR_PK_KEY = IPS.LOCATOR_FK_KEY.
• EDW_TIME_M — the time/calendar dimension, aliased TIME, joined on TIME.CDAY_CAL_DAY_PK_KEY = IPS.PRD_DATE_FK_KEY.
• EDW_ITEMS_M — the items dimension, aliased IT, joined on IT.IREV_ITEM_REVISION_PK_KEY = IPS.ITEM_ORG_FK_KEY.

No further base tables are documented for this view, so all descriptive attributes are retrieved through these four dimension objects. Each join is on a surrogate foreign key from the fact table to the corresponding primary key in the dimension, which is the standard star-schema access pattern.

Key Columns

The projection resolves fact foreign keys into named dimension identifiers and applies NVL to guarantee non-null measures:

• ALL_LOCATORS_ID (inv.ALL_PK_KEY) — the location/locator surrogate key.
• PCMP_ID, PORG_ID, OPERATING_UNIT_ID, PLANT_ID, SUB_INV_ID — company, organization, operating unit, plant, and subinventory identifiers derived from the location dimension.
• CAL_PERIOD_ID (time.cper_cal_period_pk_key) — the calendar period key used to bucket the statistics.
• ALL_ITEMS_ID, ITEM_CAT_ID, ITEM_ID, ITEM_NUMBER_ID, ITEM_ORG_ID — item-level identifiers. The ITEM_NUMBER_ID column (it.ITEM_ITEM_NUMBER_PK_KEY) is the surrogate key aligned to the item number attribute, which is the column most frequently referenced by users searching for "item_number_id".
• EONH_INV (END_ONH_VAL_G), EINT_INV (END_INT_VAL_G), EWIP_INV (END_WIP_VAL_G) — respectively end-of-period on-hand, intransit, and WIP inventory values, each defaulted to zero via NVL.

Common Use Cases and Queries

Typical scenarios include period-end inventory valuation reporting, reconciliation of on-hand against intransit and WIP balances, and item- or organization-level roll-ups for financial analysis. Because the view keys are surrogates, it is normally joined back to the corresponding EDW base tables to obtain descriptive names.

Retrieving valuations for a given item number:

• SELECT item_number_id, cal_period_id, sum(eonh_inv) eonh, sum(eint_inv) eint, sum(ewip_inv) ewip FROM apps.opi_edw_inv_value_perd_v WHERE item_number_id = :p_item_number GROUP BY item_number_id, cal_period_id ORDER BY cal_period_id;

Aggregating by organization and accounting period:

• SELECT porg_id, operating_unit_id, cal_period_id, sum(eonh_inv) FROM apps.opi_edw_inv_value_perd_v WHERE cal_period_id BETWEEN :from_period AND :to_period GROUP BY porg_id, operating_unit_id, cal_period_id;

Because the view already supplies zeroed measures through NVL, aggregations can be performed without additional null handling.