Search Results ewip_inv




Overview
Explore in AI Chat

Overview

The view OPI_EDW_INV_VALUE_PERD_V is a reporting and integration object belonging to the OPI – Operations Intelligence product family within Oracle E-Business Suite 12.1.1 and 12.2.2. It is classified by Oracle as obsolete, meaning it is retained only for backward compatibility and is not implemented in current database instances. Its purpose is to serve as the source view for a defined set of inventory valuation reports: the On Hand Inventory Value, Total Inventory Value, Percentage Total Inventory Value, and Work-In-Process Inventory Value reports. The view aggregates period-end inventory balances across organization, item, locator, and calendar-period dimensions, enabling historical trending and comparative analysis of inventory value. Because it belongs to the Operations Intelligence layer, it was designed to feed the EDW (Enterprise Data Warehouse) staging structures that supported analytical reporting rather than transactional processing.

Underlying Base Objects

The ETRM metadata states that no base objects are formally documented for this view in the 12.2.2 repository. However, the view definition text reveals three materialized EDW base tables and one fact table:

The fact table joins to the dimensions through foreign keys: LOCATOR_FK_KEY, ITEM_ORG_FK_KEY, and PRD_DATE_FK_KEY, aligning each statistical record to a locator, an item-organization revision, and a reporting date respectively.

Key Columns

The view exposes thirteen columns. The dimension identifier columns are ALL_LOCATORS_ID, PCMP_ID (OPM company), PORG_ID (OPM organization), OPERATING_UNIT_ID, PLANT_ID, SUB_INV_ID (subinventory), CAL_PERIOD_ID, ALL_ITEMS_ID, ITEM_CAT_ID, and ITEM_ID (item revision). The three measure columns carry the analytical value of the view:

  • EONH_INV — end-on-hand inventory value, derived from IPS.END_ONH_VAL_G with NVL defaulting to 0.
  • EINT_INV — end total (internal) inventory value, derived from IPS.END_INT_VAL_G, matching the "eint_inv" search term and representing total inventory value at period end.
  • EWIP_INV — end work-in-process inventory value, derived from IPS.END_WIP_VAL_G.

All measure columns are wrapped in NVL to guarantee that absent statistics return zero rather than null, ensuring consistent aggregation in downstream reports.

Common Use Cases and Queries

The principal use case is period-over-period inventory valuation reporting, particularly trend analysis of on-hand, total, and WIP value by organization, plant, or item category. A typical query selects the period and organizational dimensions alongside the three measures, filtered by a required calendar period.

SELECT CAL_PERIOD_ID, OPERATING_UNIT_ID, SUB_INV_ID,
       SUM(EONH_INV) EONH_INV, SUM(EINT_INV) EINT_INV, SUM(EWIP_INV) EWIP_INV
FROM   OPI_EDW_INV_VALUE_PERD_V
WHERE  CAL_PERIOD_ID = :period_id
GROUP BY CAL_PERIOD_ID, OPERATING_UNIT_ID, SUB_INV_ID;

A second scenario computes percentage total inventory value, dividing each item category's EINT_INV by the grand total for the period. Because the view is documented as obsolete and not implemented, queries should be validated against current inventory valuation views before being relied upon in 12.2.2 environments.