Search Results stnd_rsrc_usage

Overview

OPI_EDW_OPI_JOB_RSRC_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. Its documented purpose is to serve as the source view for extracting job-resource data from the Discrete Manufacturing source tables, feeding the Operations Intelligence data warehouse (EDW). In this capacity, it functions as a flattened, pre-joined extraction layer that denormalizes work order, resource, and job context into a single row-per-composite-key shape suitable for ETL into the OPI star schema. The naming convention — the _FCV suffix — indicates a "fact collection view," which is the standard ETRM pattern for views that materialize a fact grain for the Operations Intelligence collection programs. The view is not intended for transactional use; it is a read-only integration artifact consumed by concurrent programs and warehouse loaders.

Underlying Base Objects

The view text documents references to Discrete Manufacturing source tables and the EDW utility layer, even though the accompanying metadata lists no referenced base objects. The significant sources visible in the SELECT are work order header data (WO, supplying QUANTITY_COMPLETED and SCHEDULED_QUANTITY), work order resource data (WOR, supplying ORGANIZATION_ID, WIP_ENTITY_ID, REPETITIVE_SCHEDULE_ID, OPERATION_SEQ_NUM, RESOURCE_ID, APPLIED_RESOURCE_UNITS, USAGE_RATE_OR_AMOUNT, APPLIED_RESOURCE_VALUE, and BASIS_TYPE), resource instance data (INST, providing INSTANCE_CODE), a sequence source (INC, providing SEQ_ID), and item master data (MSI / WE, supplying PRIMARY_UOM_CODE and PRIMARY_ITEM_ID). These map to the BOM/WIP tables such as WIP_DISCRETE_JOBS, WIP_ENTITIES, WIP_REQUIREMENT_OPERATIONS or WIP_OPERATIONS, BOM_RESOURCES, and MTL_SYSTEM_ITEMS_B. The view also depends on the EDW utility layer: EDW_UTIL.GET_UOM_CONV_RATE, EDW_UTIL.GET_BASE_CURRENCY, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT, and the private package OPI_EDW_JOB_RSRC_PVT.

Key Columns

The composite key that end users most often search for — and which the view exposes as a single concatenated string — is JOB_RSRC_PK, built from ORGANIZATION_ID-WIP_ENTITY_ID-REPETITIVE_SCHEDULE_ID-OPERATION_SEQ_NUM-RESOURCE_ID-INSTANCE_CODE-OPI. Note that this key uses the numeric WIP entity identifier; it does not carry the human-readable JOB_NO (the WIP entity name displayed in Discrete Manufacturing forms). Analysts searching for "job_no" must therefore join out to the entity master to translate the surrogate ID. Other significant columns include SEQ_ID and VIEW_ID (both set to constants and the INC sequence), ACT_RSRC_QTY and PLN_RSRC_QTY (quantities converted to the item's primary UOM), ACT_RSRC_USAGE, PLN_RSRC_USAGE, and STND_RSRC_USAGE (the last two computed via DECODE on BASIS_TYPE, distinguishing rate-per-item from fixed-amount resource bases), and the value measures ACT_RSRC_USAGE_VAL_B / _G and PLN_RSRC_USAGE_VAL_B / _G, where the _B columns carry base currency and the _G columns are converted to the global reporting currency.

Common Use Cases and Queries

Typical usage involves resource utilization reporting, planned-versus-actual resource cost analysis, and loading the OPI job-resource fact. A representative query joining the surrogate job identifier to the readable job number is:

• SELECT v.JOB_RSRC_PK, we.WIP_ENTITY_NAME job_no, v.ACT_RSRC_USAGE, v.ACT_RSRC_USAGE_VAL_B FROM APPS.OPI_EDW_OPI_JOB_RSRC_FCV v, APPS.WIP_ENTITIES we WHERE v.ORGANIZATION_ID = we.ORGANIZATION_ID AND v.WIP_ENTITY_ID = we.WIP_ENTITY_ID; — resolves the "job_no" search against the underlying entity name.
• Filtering by organization and job for a single work order's resource detail, aggregating ACT_RSRC_QTY and PLN_RSRC_QTY to compare consumed versus scheduled quantity.
• Global-currency resource variance reporting using ACT_RSRC_USAGE_VAL_G less PLN_RSRC_USAGE_VAL_G.

Because conversion rates and currency translation are resolved through EDW utility functions, results reflect the reporting conventions configured at run time, and the view should always be queried rather than the underlying WIP tables when reproducing OPI warehouse figures.