Results for “porg_id”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

OPI_EDW_INV_TURNS_QTR_V is a reporting source view historically shipped with the Oracle Operations Intelligence (OPI) product family, an analytical layer that predates and was progressively superseded by Oracle Business Intelligence Applications and the EBS Integrated SOA/EDW reporting stack. In Oracle EBS 12.1.1 and 12.2.2, OPI is documented as obsolete. The view is not deployed in current environments, and the ETRM record explicitly notes "Not implemented in this database." Its purpose was to supply the Inventory Turns (Quarter Level) report, a supply-chain performance report that measures how many times inventory is consumed and replenished within a fiscal quarter.

Inventory turns are computed as cost of goods sold divided by average on-hand inventory value. This view aggregates those two measures at the quarter level, enabling trend analysis across comparably sized reporting periods and comparison between organizations, plants, and stock rooms. Because OPI was built on the Enterprise Data Warehouse (EDW) star schema rather than on the EBS transactional tables, the view exposes surrogate primary keys and foreign keys rather than descriptive attributes.

Underlying Base Objects

The ETRM metadata documents no referenced base objects, but the embedded view text reveals three sources joined in the FROM clause:

• OPI_EDW_INV_PERD_STAT_F — the inventory period statistics fact table, aliased IPS. It carries the measures TOT_CUST_SHIP_VAL_G (customer shipment value, used as COGS) and AVG_ONH_VAL_G (average on-hand value), keyed by locator, item organization, lot, and period date.
• EDW_MTL_INVENTORY_LOC_M — the inventory location dimension, aliased INV, providing the organizational hierarchy keys (company, operating unit, plant, stock room) and the locator key.
• EDW_TIME_M — the time dimension, aliased TIME, supplying the calendar quarter key and the period end date used to restrict the result set.

The joins are conformed: INV.INVL_LOCATOR_PK_KEY equals IPS.LOCATOR_FK_KEY, and TIME.CDAY_CAL_DAY_PK_KEY equals IPS.PRD_DATE_FK_KEY. A predicate of CPER_END_DATE <= SYSDATE ensures only closed or elapsed periods are included.

Key Columns

• ALL_LOCATORS_ID — the all-locators key derived from INV.ALL_PK_KEY; the lowest level of inventory location.
• PCMP_ID, PORG_ID, OPERATING_UNIT_ID, PLANT_ID — the organizational roll-up keys for company, OPM organization, operating unit, and plant respectively.
• SUB_INV_ID — the stock room (subinventory) surrogate key, derived from INV.SUBI_STOCK_ROOM_PK_KEY. This is the column most often referenced by the search term "sub_inv_id." It identifies the physical stocking location within a plant.
• CAL_QTR_ID — the calendar quarter key from EDW_TIME_M, the primary time grouping of the report.
• ITEM — the item organization foreign key (ITEM_ORG_FK_KEY), tying measures to a specific item within an inventory organization.
• COGS — defined as -1 * SUM(IPS.TOT_CUST_SHIP_VAL_G), a negated sum of customer shipment value.
• AVG_ONH_VAL — defined as AVG(IPS.AVG_ONH_VAL_G), the average on-hand inventory value across the quarter.

The GROUP BY clause spans the location keys, quarter key, item organization key, and LOT_FK_KEY, though LOT_FK_KEY is not projected in the select list.

Common Use Cases and Queries

Analysts used this view to calculate quarterly inventory turns by dividing COGS by AVG_ONH_VAL, filtering on a specific subinventory or item organization. A representative query joining descriptive names from the EDW dimensions would resemble:

SELECT sub_inv_id, cal_qtr_id, item,
       -1 * cogs AS cogs_value,
       avg_onh_val,
       CASE WHEN avg_onh_val > 0
            THEN (-1 * cogs) / avg_onh_val END AS turns
FROM   opi_edw_inv_turns_qtr_v
WHERE  sub_inv_id = :sub_inv_id
ORDER BY cal_qtr_id;

In 12.2.2, this view should be treated as documentation-only. Because it is not implemented, the equivalent metrics must be sourced from the current BI Applications inventory subject areas or reconstructed directly from MTL_ONHAND_QUANTITIES, MTL_TRANSACTIONS, and CST_COST_DETAILS. The OPI_EDW_INV_TURNS_QTR_V definition nonetheless remains useful as a reference model for the grain, keys, and measure logic of quarterly inventory turns reporting.