Search Results base_uom_fk




Overview
Explore in AI Chat

Overview

OPI_EDW_OPMINV_DAILY_STAT_FCV is an APPS-owned, VALID database view within the OPI (Operations Intelligence) product family of Oracle E-Business Suite 12.1.1 and 12.2.2. It is classified as a flat/collection view (FCV), the terminal layer in the OPI extract-transform-load pipeline, and serves as the source object from which the Inventory Daily Transaction Summary is extracted from Process Manufacturing and Discrete inventory source tables.

The view produces a denormalized, surrogate-key-encoded result set suitable for consumption by the Oracle Business Intelligence Applications (OBIA) or Oracle Data Integrator/EDW staging layer. Rather than exposing raw transactional identifiers, the view transforms Process Manufacturing summary data into foreign-key references such as BASE_UOM_FK, ITEM_ORG_FK, LOCATOR_FK, LOT_FK, and date dimension keys, conforming the row grain to the warehouse model used by Operations Intelligence daily inventory metrics.

The row grain is one record per combination of operating company (CO_CODE), organization (ORGN_CODE), warehouse (WHSE_CODE), location, item, transaction date, and lot, plus the instance and cost group context. This composite is materialized in the synthetic primary key INV_DAILY_STATUS_PK.

Underlying Base Objects

The ETRM metadata for this view does not document a discrete list of referenced base objects; the view's implementation is expressed defensively through package function calls, which obscures the literal underlying tables. Inspection of the view text, however, reveals the following principal source objects within the APPS schema:

All of these are wrapped through EDW_* and EDW_CURRENCY package functions and OPI_* foreign-key derivation packages, meaning the view is not a simple join projection but an engineered conformance layer.

Key Columns

The view exposes both foreign-key surrogate columns and raw measures:

  • INV_DAILY_STATUS_PK — composite synthetic primary key built from company, organization, warehouse, location, item, transaction date, lot, cost group, instance, and the literal 'OPM' tag.
  • BASE_UOM_FK — derived via EDW_UTIL.GET_EDW_UOM(UOM.UOM_CODE, MSI.INVENTORY_ITEM_ID). This is the column matching the user's search term "base_uom_fk"; it resolves the item's UOM to the EDW conformed unit-of-measure dimension key.
  • BASE_CURRENCY_FK — the set-of-books base currency code (GPM.BASE_CURRENCY_CODE).
  • INSTANCE_FK — instance code from INST.
  • INV_ORG_FK — derived by EDW_ORGANIZATION_PKG.INT_ORGANIZATION_FK.
  • ITEM_ORG_FK — derived by EDW_ITEMS_PKG.ITEM_ORG_FK.
  • LOCATOR_FK — derived by EDW_MTL_INVENTORY_LOC_PKG.GET_LOCATOR_FK.
  • LOT_FK — composite of lot, item, 'OPM', and instance.
  • TRX_DATE_FK / PRD_DATE_FK — EDW_TIME_PKG calendar day and calendar period keys.
  • Measure columns — ON_HAND quantities and values at average, beginning, and end of period, with BEG_ONH_VAL_G and END_ONH_VAL_G translated via EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT. Many interior/WIP measures are nulled out for OPM daily statistics.

Common Use Cases and Queries

Typical uses include feeding the OBIA Inventory Daily fact table, reconciling Process Manufacturing on-hand balances by UOM, and validating period-over-period inventory movements. A representative query retrieving base UOM context is:

SELECT inv_daily_status_pk, base_uom_fk, inv_org_fk, item_org_fk, trx_date_fk, avg_onh_qty, beg_onh_qty, end_onh_qty FROM apps.opi_edw_opminv_daily_stat_fcv WHERE base_uom_fk IS NOT NULL AND trx_date_fk = :p_trx_date_fk;

Because all keys are surrogate references to EDW dimensions, downstream joins to item, organization, locator, and UOM attributes are performed against the EDW dimension tables rather than the EBS transactional tables, ensuring consistent reporting across OPM and discrete inventory sources.