Search Results lot_fk_key




Overview
Explore in AI Chat

Overview

APPS.OPI_EDW_INV_TURNS_QTR_V is a quarterly inventory turns view that belongs to the Oracle E-Business Suite Operational Intelligence (OPI) enterprise data warehouse family. It presents aggregated inventory movement and valuation metrics at the quarter, item, and locator level of granularity, deriving inventory turns as a function of cost of goods sold (COGS) and average on-hand value. The view is designed for analytical reporting and performance measurement within the OPM (Oracle Process Manufacturing) inventory domain, supporting calculations that would otherwise require complex joins across the transaction and dimensional models of the EDW schema.

The view consolidates inventory data into a form suitable for inventory turns analysis, a standard supply chain KPI that measures how efficiently inventory is converted into sales over a defined period. Because it aggregates by calendar quarter and inventory locator, it enables period-over-period comparison of turn rates across companies, organizations, operating units, plants, subinventories, and items.

Underlying Base Objects

The view is defined over three documented base objects:

The joins are performed between the locator foreign key and the inventory locator dimension, and between the period date foreign key and the time dimension, with a filter restricting the fact records to those whose period end date (CPER_END_DATE) is on or before the current system date.

Key Columns

  • ALL_LOCATORS_ID — derived from INV.ALL_PK_KEY; the primary locator identifier.
  • PCMP_ID, PORG_ID, OPERATING_UNIT_ID, PLANT_ID, SUB_INV_ID — the company, organization, operating unit, plant, and subinventory identifiers forming the organizational hierarchy.
  • CAL_QTR_ID — the calendar quarter key (CQTR_CAL_QTR_PK_KEY) defining the aggregation period.
  • ITEM — the item-organization foreign key (ITEM_ORG_FK_KEY); this is the column the user searched for. It groups the results at the item/organization level and is the principal attribute for item-level inventory turn analysis.
  • COGS — computed as -1 * SUM(IPS.TOT_CUST_SHIP_VAL_G), expressing the cost of goods shipped as a positive value.
  • AVG_ONH_VAL — computed as AVG(IPS.AVG_ONH_VAL_G), the average on-hand inventory value for the group.
  • LOT_FK_KEY — included in the GROUP BY clause, permitting lot-level granularity in the aggregate.

Common Use Cases and Queries

The view is typically queried to compute quarterly inventory turns per item or organization, supporting supply chain and cost management dashboards. A representative query follows:

  • SELECT CAL_QTR_ID, ITEM, SUM(COGS) COGS, AVG(AVG_ONH_VAL) AVG_ONH FROM APPS.OPI_EDW_INV_TURNS_QTR_V GROUP BY CAL_QTR_ID, ITEM;
  • Joining ITEM back to the item-organization dimension to resolve item names and descriptions for reporting.
  • Filtering by PLANT_ID or SUB_INV_ID to analyze turns by facility or subinventory.
  • Trending COGS versus average on-hand value across quarters to identify slow-moving or excess inventory.

Because the view already applies the CPER_END_DATE <= SYSDATE filter, it excludes future periods, making it suitable for point-in-time historical analysis.