Search Results res_dept_id

Overview

OPI_EDW_RSRC_EFFICIENCY_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the OPI (Operations Intelligence) product family. It functions as the source view for the Resource Efficiency report, one of the analytical outputs generated by the Operations Intelligence application. Operations Intelligence provides manufacturing and production analytics by staging transactional data into an enterprise data warehouse (EDW) star schema consisting of fact and dimension tables. This view exposes a denormalized, query-ready projection that joins a resource usage fact with inventory, time, and resource dimension tables, allowing reporting tools and concurrent programs to retrieve resource efficiency metrics without embedding complex join logic.

The view is defined with status VALID in both Oracle EBS 12.1.1 and 12.2.2. It is read-only by design; no DML is permitted against it. Because the view resolves to EDW staging tables rather than to live transactional tables such as WIP or BOM, its contents depend on the successful execution of the OPI data collection and warehouse load programs. The ALL_TIME_ID column referenced in the user's search originates from the EDW_TIME_M dimension and serves as the foreign key linking each fact row to a specific calendar day.

Underlying Base Objects

The documented view text references the following base objects:

• OPI_EDW_JOB_RSRC_F — the resource usage fact table (aliased JR), supplying actual and planned resource usage and the foreign keys for locator, transaction date, and resource.
• EDW_MTL_INVENTORY_LOC_M — the inventory location dimension (aliased INV), supplying inventory, company, organization, operating unit, and plant keys.
• EDW_TIME_M — the time dimension (aliased TIME), supplying the calendar day, period, quarter, and year keys.
• EDW_BOM_RES_M — the resource dimension (aliased RES), supplying department, resource group, and resource keys.

The joins are equijoins on surrogate keys: INV.INVL_LOCATOR_PK_KEY = JR.LOCATOR_FK_KEY, TIME.CDAY_CAL_DAY_PK_KEY = JR.TRX_DATE_FK_KEY, and RES.RSRC_RESOURCE_PK_KEY = JR.RSRC_FK_KEY. The ETRM metadata does not document additional base objects beyond these four.

Key Columns

• ALL_INV_ID, PCMP_ID, PORG_ID, OPERATING_UNIT_ID, PLANT_ID — inventory location and its organizational hierarchy, derived from the location dimension.
• ALL_TIME_ID — the surrogate key to the calendar day in EDW_TIME_M; the principal time grain for the view.
• CAL_YR_ID, CAL_QTR_ID, CAL_PERIOD_ID — calendar year, quarter, and accounting period keys for period-based aggregation.
• ALL_RES_ID, RES_DEPT_ID, RES_GRP_ID, RES_RES_ID — resource identifiers and their department and resource group groupings.
• ACT_USAGE — actual resource usage from the fact table (JR.ACT_RSRC_USAGE).
• STD_USAGE — planned or standard resource usage from the fact table (JR.PLN_RSRC_USAGE).

The ratio of ACT_USAGE to STD_USAGE yields the core efficiency measure the report is designed to present.

Common Use Cases and Queries

Typical usage includes resource efficiency variance analysis by plant, department, or resource group, and period-over-period trending. A representative query filtering on the time key is:

SELECT ALL_TIME_ID, PLANT_ID, RES_GRP_ID, RES_RES_ID, ACT_USAGE, STD_USAGE
FROM APPS.OPI_EDW_RSRC_EFFICIENCY_V
WHERE ALL_TIME_ID = :p_time_id;

Aggregated efficiency by resource group for a calendar period:

SELECT RES_GRP_ID, SUM(ACT_USAGE) ACT_TOT, SUM(STD_USAGE) STD_TOT,
  DECODE(SUM(STD_USAGE),0,NULL,SUM(ACT_USAGE)/SUM(STD_USAGE)) EFFICIENCY
FROM APPS.OPI_EDW_RSRC_EFFICIENCY_V
WHERE CAL_PERIOD_ID = :p_period_id
GROUP BY RES_GRP_ID;

Because the view is built on EDW dimension and fact tables, queries should constrain on indexed key columns such as ALL_TIME_ID, PLANT_ID, or CAL_PERIOD_ID to limit full scans across the warehouse. Refresh of the underlying OPI collection programs must complete before the view reflects current-period data.