Search Results no_adj

Overview

OPI_EDW_OPM_JOB_DETAIL_FCV is a source view in the Oracle E-Business Suite Operations Intelligence (OPI) module, which is documented as obsolete in Release 12.1.1 and 12.2.2. The view functions as the OPM (Oracle Process Manufacturing) source view for the Job Detail fact, feeding the Enterprise Data Warehouse (EDW) star schema used by Operations Intelligence reporting. Its purpose is to consolidate batch and material detail into a single denormalized rowset suitable for the Job Detail fact table. The view brings together product and co-product output data, material input consumption, by-product yields, and associated currency, unit-of-measure, and calendar conversions. In practice it serves as the extraction layer between the transactional OPM batch tables and the analytical fact tables consumed by OPI dashboards and reports. The naming convention reflects this role: the FCV suffix denotes a fact-capture view, and the OPI_EDW_OPM prefix scopes it to Operations Intelligence EDW content sourced from process manufacturing. Notably, the ETRM metadata records that this view is not implemented in the reference database, meaning it is documented for lineage and upgrade-impact purposes rather than for direct runtime access.

Underlying Base Objects

The documented metadata lists no formally registered base objects, and the view is marked as not implemented in the database. However, the view text itself reveals the OPM transactional tables and PL/SQL packages it would depend upon. The principal driving table is the OPM batch table, aliased as B, which supplies batch identifiers, plant codes, item identifiers, batch status, line numbers, actual and expected completion dates, actual quantities, and the WIP warehouse code. Material and item context is joined through inventory item and organization references (aliases such as ITEM_FK_V and IW). Set of books and currency context is derived through a set-of-books reference (SOB) and a currency master (PM). Unit-of-measure data is retrieved via a UOM reference. Several OPM and EDW PL/SQL packages are invoked directly within the SELECT: OPI_EDW_OPM_PRD_PKG for by-product value and scheduled work days, OPI_OPM_COMMON_PKG.OPMCO_GET_COST for input costing, EDW_ITEMS_PKG.ITEM_ORG_FK for key resolution, EDW_TIME_PKG.CAL_DAY_FK for calendar keys, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT for global currency conversion, and EDW_UTIL.GET_EDW_UOM for UOM key resolution. These packages form the derivation logic that the view exposes as flat columns.

Key Columns

• Job Detail key — a concatenation of plant code, batch ID, item ID, instance code, and the literal OPM, forming the unique fact grain.
• Organization key — a concatenation of plant code, instance code, and the OPM/PORG literals identifying the owning organization.
• Item organization FK — resolved via EDW_ITEMS_PKG.ITEM_ORG_FK using inventory item and organization IDs.
• Calendar day FK — derived by EDW_TIME_PKG.CAL_DAY_FK, with the date source switched on batch status and last update date.
• SOB currency FK — base currency code from the set-of-books/currency source.
• Base UOM FK — resolved through EDW_UTIL.GET_EDW_UOM.
• ACT_BPR_VAL_B / ACT_BPR_VAL_G — actual by-product value in base and global currency, computed only for the first line (LINE_NO = 1).
• ACT_INP_VAL_B / ACT_INP_VAL_G — actual material input value, calculated as actual quantity multiplied by OPMCO_GET_COST, expressed in base and global currency.
• Completion and status dates — decoded from batch status to yield either actual or expected completion dates, plus a last-update timestamp.
• Placeholder columns — multiple NA_EDW literals reserve positions for future or unmapped dimensions.

Common Use Cases and Queries

The view supports batch-level yield, input-cost, and by-product analysis for process manufacturing. A typical query retrieves actual input and by-product values per batch:

• SELECT job_detail_key, actual_qty, ACT_INP_VAL_B, ACT_BPR_VAL_B FROM OPI_EDW_OPM_JOB_DETAIL_FCV;
• Filtering by completion period — WHERE ACT_INP_VAL_B > 0 — to reconcile material consumption against batch output.
• Joining resolved foreign keys (item organization FK, calendar day FK, SOB currency FK, base UOM FK) to EDW dimension tables for drill-down reporting.
• Comparing base and global currency values (ACT_INP_VAL_B versus ACT_INP_VAL_G) to validate currency conversion for multinational costing.

A related column frequently sought by users, ic_plnt_inv, relates to the plant/inventory organization context used in item-organization key resolution. Because the view is documented as obsolete and not implemented, administrators should confirm current EDW extraction lineage in their specific 12.1.1 or 12.2.2 instance before relying on this object for custom reporting.