Search Results item_fk_v

Overview

OPI_EDW_OPM_JOB_RSRC_FCV is an Oracle EBS 12.1.1 / 12.2.2 reporting view owned by the APPS schema and defined in the Oracle Process Manufacturing (OPM) module. The suffix FCV denotes a flattened, conformed view, indicating that its purpose is to present process manufacturing job resource consumption data in a denormalized form suitable for consumption by the Oracle Enterprise Data Warehouse (EDW) and downstream business intelligence tools. The view exposes resource usage at the granularity of a batch step resource line, joining batch, batch step, resource, item, and instance information into a single, analytics-ready row.

The view is a critical component in the OPM-to-EDW integration pipeline. It supplies fact-style records keyed by surrogate foreign key constructs — such as JOB_RSRC_PK, ACTIVITY_FK, LOCATOR_FK, ITEM_FK, RSRC_FK, and TRX_DATE_FK — that conform to the star-schema dimensional model used by the EDW. This enables consistent reporting of process manufacturing resource consumption alongside other EBS-sourced facts.

Underlying Base Objects

The ETRM 12.2.2 metadata does not document specific base tables for this view; the view text instead references a set of aliases (BR, BD, BH, IW, SOB, PM, UOM, CR, OPRM, INST) drawn from OPM batch, resource, item, and setup tables. Based on the column references and standard OPM naming, these correspond to OPM batch header, batch detail, batch step resource, item master organization, set of books, currency, unit of measure, and resource setup entities.

The view further depends on several package functions that compute derived values at query time: EDW_ITEMS_PKG.ITEM_ORG_FK generates the item-organization foreign key; EDW_UTIL.GET_EDW_UOM resolves the usage UOM; edw_time_pkg.cal_day_fk derives the calendar day foreign key from the actual completion date; and OPI_OPM_COMMON_PKG.OPMCO_GET_RSRC_COST retrieves resource cost, which is multiplied by actual or planned usage to yield usage values. Currency conversion is handled by EDW_CURRENCY.convert_global_amount. Because none of these dependencies are documented as base objects in ETRM, the view should be treated as a package-derived flattening layer rather than a simple table projection.

Key Columns

• JOB_RSRC_PK — Composite primary key concatenating batch, line, batchstep, resource, activity, batchstepline, instance code, and the literal 'OPM'.
• ITEM_FK — Surrogate foreign key returned by EDW_ITEMS_PKG.ITEM_ORG_FK, built from inventory item ID and organization ID. This is the column most relevant to the search term item_fk_v, which typically appears as a view-local variable or alias referencing the item dimension key.
• ACTIVITY_FK, OPRN_FK, RSRC_FK, INSTANCE_FK, LOCATOR_FK — Dimension foreign keys for activity, operation, resource, instance, and inventory locator.
• TRX_DATE_FK — Calendar day foreign key derived from the actual completion date via edw_time_pkg.cal_day_fk.
• ACT_RSRC_QTY, ACT_RSRC_USAGE, ACT_RSRC_USAGE_VAL_B, ACT_RSRC_USAGE_VAL_G — Actual resource quantity, usage, and base/global currency usage values.
• PLAN_RSRC_QTY, PLAN_RSRC_USAGE, PLN_RSRC_USAGE_VAL_B — Planned resource quantity and usage values with corresponding monetary amounts.
• SOB_CURRENCY_FK, USAGE_UOM_FK, QTY_UOM_FK — Currency and unit-of-measure foreign keys.

Common Use Cases and Queries

The primary use case is EDW fact-loading and OPM resource cost analytics. A typical query aggregates resource usage by item and period:

SELECT ITEM_FK, TRX_DATE_FK, SUM(ACT_RSRC_USAGE_VAL_G) VAL
FROM APPS.OPI_EDW_OPM_JOB_RSRC_FCV
GROUP BY ITEM_FK, TRX_DATE_FK;

Analysts also drill into planned-versus-actual resource variance for a given batch or resource. Because ITEM_FK (the target of the item_fk_v search) is a surrogate key, reports join it to the item dimension to obtain descriptive attributes. The 'NA_EDW' literals in several columns indicate intentionally null dimension references for measures not applicable to OPM resource facts.