Search Results act_rsrc_usage_val_b




Overview
Explore in AI Chat

Overview

OPI_EDW_OPM_JOB_RSRC_FCV is a source view owned by the APPS schema within the Operations Intelligence (OPI) product family of Oracle E-Business Suite. It is classified as a Flat Collection View (FCV), a naming convention used throughout the OPI/EDW extract layer to designate denormalized, surrogate-key-based views that feed the Enterprise Data Warehouse staging and fact tables. The view extracts job-resource transactional data from Process Manufacturing (OPM) source tables and reshapes it into the dimensional model expected by the Operations Intelligence analytics schema.

The central architectural purpose of this view is the construction of federated primary and foreign keys. Rather than exposing raw OPM identifiers, the view concatenates batch, line, step, resource, activity, and instance codes into composite business keys such as JOB_RSRC_PK, ACTIVITY_FK, LOCATOR_FK, RSRC_FK, OPRN_FK, and INSTANCE_FK. Each key is suffixed with the literal -OPM to identify the source system, enabling cross-module conformance across OPM, discrete manufacturing, and other EDW source feeds.

Underlying Base Objects

The documented ETRM metadata records no explicitly listed base objects for this view; however, the view text references several OPM tables and shared packages through aliases. The principal source table is the OPM batch resource table (aliased BR), which supplies resource quantities, usage, counts, and date columns including ACTUAL_CMPLT_DATE, ACTUAL_START_DATE, and PLAN_CMPLT_DATE. It is joined to a batch header table (BH) providing PLANT_CODE and to a batch detail table (BD) providing LINE_ID, which together form the locator and job key components.

Supporting tables include an item master reference (IW), a resource/currency master (PM) supplying BASE_CURRENCY_CODE, a UOM table (UOM), an instance configuration table (INST) providing INSTANCE_CODE, an operation reference (OPRM) supplying OPRN_ID, and a capacity/availability reference (CR) supplying DAILY_AVAIL_USE. The view also invokes OPI_OPM_COMMON_PKG.OPMCO_GET_RSRC_COST, EDW_ITEMS_PKG.ITEM_ORG_FK, EDW_TIME_PKG.CAL_DAY_FK, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT, and EDW_UTIL.GET_EDW_UOM to derive surrogate keys and converted cost amounts.

Key Columns

  • JOB_RSRC_PK — Composite primary key built from batch, line, step, resource, activity, step-line, and instance identifiers, terminated by -OPM.
  • ITEM_FK — Surrogate item-organization key resolved via EDW_ITEMS_PKG.ITEM_ORG_FK.
  • RSRC_FK — Resource key; when RESOURCES is null, the literal NA_EDW is substituted.
  • TRX_DATE_FK — Transaction date surrogate derived from ACTUAL_CMPLT_DATE through EDW_TIME_PKG.CAL_DAY_FK, truncated to 120 characters.
  • ACT_RSRC_USAGE_VAL_B / _G — Actual resource usage value in base and global currency, the latter produced by EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT.
  • PLAN_RSRC_USAGE, PLAN_RSRC_QTY, PLAN_RSRC_COUNT — Planned resource usage, quantity, and count metrics.
  • ACTUAL_RSRC_USAGE, ACTUAL_RSRC_QTY, ACTUAL_RSRC_COUNT — Actual consumption metrics, with nulls coalesced to zero.
  • OPRN_FK, ACTIVITY_FK, LOCATOR_FK, INSTANCE_FK — Conformed foreign keys linking to operation, activity, plant locator, and instance dimensions.

Common Use Cases and Queries

The view is typically queried during EDW fact-load processes or for ad-hoc variance analysis comparing planned versus actual resource consumption and cost. A typical query retrieves resource usage and base-currency valuation for a plant and date range:

  • SELECT job_rsrc_pk, rsrc_fk, trx_date_fk, actual_rsrc_usage, act_rsrc_usage_val_b FROM opi_edw_opm_job_rsrc_fcv WHERE locator_fk LIKE 'PLANT01%';
  • Planned-versus-actual comparisons join PLAN_RSRC_USAGE against ACTUAL_RSRC_USAGE on JOB_RSRC_PK.
  • Cost rollups aggregate ACT_RSRC_USAGE_VAL_B by RSRC_FK and TRX_DATE_FK for period reporting.

The ETRM metadata does not document the underlying physical tables beyond what appears in the view text, so DBAs validating lineage should inspect ALL_DEPENDENCIES for the compiled view definition in the target 12.1.1 or 12.2.2 environment.