Search Results pln_rsrc_qty




Overview
Explore in AI Chat

Overview

OPI_EDW_JOB_RSRC_F is a fact table owned by the OPI (Operations Intelligence) schema in Oracle EBS 12.1.1 and 12.2.2. It serves as the Job Resource Fact, storing detailed information about the resources consumed and produced by jobs and batches within manufacturing and process execution. The table captures usage, value, and count metrics for resources, along with quantities produced and their associated unit-of-measure context. As a fact object in the Operations Intelligence warehouse layer, it is populated by ETL processes that source transactional data from Oracle Manufacturing and Process Manufacturing execution tables, and it is designed primarily for analytical reporting and dimensional modeling rather than for OLTP transaction processing.

Within the OPI star schema, the fact acts as the central quantitative table, surrounded by conformed dimensions such as item, resource, organization (instance), operation, activity, locator, date, and currency. Heuristic Data Vault classification mined from the foreign-key structure identifies this object as standalone, meaning it does not participate in a documented hub/link/satellite pattern with its referenced tables; the modeling suggestion is therefore to treat it as a denormalized fact rather than as a normalized Data Vault construct.

Key Information Stored

The table contains 63 documented columns. The most significant include:

The documented unique indexes are OPI_EDW_JOB_RSRC_F_U2 (JOB_RSRC_PK, JOB_RSRC_PK_KEY) and OPI_EDW_JOB_RSRC_F_UKEY (JOB_RSRC_PK_KEY). These business-key candidates distinguish the surrogate JOB_RSRC_PK_KEY from the composite business identifier. Foreign-key keys include ITEM_FK_KEY, OPRN_FK_KEY, RSRC_FK_KEY, ACTIVITY_FK_KEY, INSTANCE_FK_KEY, LOCATOR_FK_KEY, TRX_DATE_FK_KEY, SOB_CURRENCY_FK_KEY, USAGE_UOM_FK_KEY, and QTY_UOM_FK_KEY. Descriptive and audit columns include DEPARTMENT, CREATION_DATE, LAST_UPDATE_DATE, USER_FK1_KEY through USER_FK5_KEY, USER_ATTRIBUTE1 through USER_ATTRIBUTE15, and USER_MEASURE1 through USER_MEASURE5.

Common Use Cases and Queries

Typical reporting scenarios include resource consumption variance, produced quantity analysis by job, and cost breakdown by resource and currency. A common query pattern joins by the surrogate keys:

  • Aggregating ACT_RSRC_USAGE against PLN_RSRC_USAGE filtered by JOB_NO to derive usage variance per operation.
  • Summing ACT_RSRC_VAL_G by RSRC_FK_KEY and TRX_DATE_FK_KEY for period cost reporting.
  • Retrieving a single fact record by the business key: SELECT * FROM OPI.OPI_EDW_JOB_RSRC_F WHERE JOB_RSRC_PK = :pk AND JOB_RSRC_PK_KEY = :key;
  • Comparing AVL_RSRC and EXTD_RSRC_COST across operations for capacity planning.

Related Objects

  • Item dimension — joined via ITEM_FK_KEY.
  • Resource dimension — joined via RSRC_FK_KEY.
  • Operation dimension — joined via OPRN_FK_KEY.
  • Activity dimension — joined via ACTIVITY_FK_KEY.
  • Instance/organization dimension — joined via INSTANCE_FK_KEY.
  • Date dimension — joined via TRX_DATE_FK_KEY.
  • Currency dimension — joined via SOB_CURRENCY_FK_KEY.
  • UOM dimension — joined via USAGE_UOM_FK_KEY and QTY_UOM_FK_KEY.
  • User dimensions — joined via USER_FK1_KEY through USER_FK5_KEY.