Search Results lot_fk




Overview
Explore in AI Chat

Overview

OPI_EDW_OPIINV_DAILY_STAT_FCV is a VALID view owned by the APPS schema within the OPI (Operations Intelligence) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It functions as a source extraction view that presents Inventory Daily Transaction Summary data drawn from discrete Manufacturing source tables. The view is part of the operational data store / ETL layer used to feed the Oracle EBS data warehouse, aggregating daily inventory balances and transaction summaries into a denormalized, reporting-ready structure.

Rather than exposing raw transactional records, the view consolidates averaged, beginning-balance, and quantity/value metrics across inventory, on-hand, and WIP states. It also generates surrogate foreign key columns by concatenating natural keys, a common pattern in EDW staging views that enables conformed dimension joins in the target warehouse. The view therefore serves both as an integration artifact for data movement and as a semantic layer for downstream inventory analytics.

Underlying Base Objects

The documented ETRM metadata lists no referenced base objects for this view, meaning the dependency list is not captured in the repository record. However, the view SQL itself reveals the physical sources. The primary alias OIPL points to the underlying inventory daily summary fact table (the OPI inventory daily transaction summary staging table), which supplies quantity and value measures such as AVG_INT_QTY, AVG_ONH_QTY, AVG_WIP_QTY, and their beginning-balance counterparts. The alias INST references an instance-level table providing INSTANCE_CODE, used to build composite keys tied to the EBS instance. The alias MP supplies inventory organization master data via ORGANIZATION_CODE, and OOD supplies the SET_OF_BOOKS_ID used by the EDW_TIME_PKG calendar conversion functions.

The view additionally calls EDW_TIME_PKG.CAL_DAY_TO_CAL_PERIOD_FK and EDW_TIME_PKG.CAL_DAY_FK to derive date dimension foreign keys from OIPL.TRX_DATE.

Key Columns

The item_status concept a user might expect is not a named column here; item status is typically joined from the item master dimension, with this view supplying the ITEM_ORG_FK needed for that linkage.

Common Use Cases and Queries

Typical uses include populating the inventory daily summary fact in the EDW and powering on-hand, WIP, and internal balance trend reporting by organization, item, and period.

SELECT item_org_fk, trx_date_fk, avg_onh_qty, avg_onh_val_g
FROM   apps.opi_edw_opiinv_daily_stat_fcv
WHERE  prd_date_fk = :period_fk
  AND  inv_org_fk = :org_fk;

To enrich with item status, analysts join the generated ITEM_ORG_FK back to the item dimension. Because all keys are string-concatenated, queries must respect the composite-key format when performing joins.