Search Results act_res_usage




Overview
Explore in AI Chat

Overview

OPI_EDW_RSRC_UTIL_V is a reporting source view shipped as part of the Oracle Operations Intelligence (OPI) product family within Oracle E-Business Suite. It is documented as the Resource Utilization report source view, meaning it exists primarily to feed the resource utilization analysis and associated extract/loader (such as Oracle Daily Business Intelligence or Enterprise Data Warehouse style reporting) rather than to support transactional processing. The object carries the OPI - Operations Intelligence product designation.

It is important to note that OPI is flagged as Obsolete and the view is documented as not implemented in this database. This means it should be treated as a legacy or reference artifact. In practice, if a user has reached this object while searching for avail_res (available resource), the documentation confirms that avail_res is a column of this view, sourced from the OPI_EDW_RES_UTIL_F fact table.

The view presents a denormalized, star-schema-friendly projection that combines the resource utilization fact with inventory location, time, and BOM resource dimensions, exposing surrogate key identifiers alongside the two key measures: actual resource usage and available resource.

Underlying Base Objects

The documented referential metadata states "Referenced base objects: none documented." However, the embedded view text in the ETRM record reveals the actual defining objects. OPI_EDW_RSRC_UTIL_V is defined over four objects:

  • OPI_EDW_RES_UTIL_F — the resource utilization fact table, providing the measures ACT_RES_USAGE and AVAIL_RES, plus the foreign keys to the dimensions (RU alias).
  • EDW_MTL_INVENTORY_LOC_M — the inventory location dimension (INV alias), supplying inventory, company, organization, operating unit, and plant keys.
  • EDW_TIME_M — the time dimension (TIME alias), supplying calendar day, year, quarter, and period keys.
  • EDW_BOM_RES_M — the BOM resource dimension (RES alias), supplying resource, resource group, and department keys.

The joins confirm standard EDW conformed-dimension wiring: the fact's LOCATOR_FK_KEY, TRX_DATE_FK_KEY, and RES_FK_KEY map to the inventory locator, calendar day, and resource primary keys respectively. The three-letter prefixes on column names (INV, TIME, RES, RU) reflect the source dimension aliases.

Key Columns

  • ALL_INV_ID — inventory surrogate key from the inventory location dimension.
  • PCMP_ID, PORG_ID, OPERATING_UNIT_ID, PLANT_ID — OPM company, OPM organization, operating unit, and plant identifiers for organizational roll-up.
  • ALL_TIME_ID, CAL_YR_ID, CAL_QTR_ID, CAL_PERIOD_ID — time dimension keys enabling utilization reporting by calendar day, year, quarter, and period.
  • ALL_RES_ID, RES_DEPT_ID, RES_GRP_ID, RES_RES_ID — resource dimension keys, including department, resource group, and individual resource.
  • ACT_RES_USAGE — actual resource usage measure, drawn from RU.ACT_RES_USAGE.
  • AVAIL_RES — the available resource measure (the column matching the user search term), drawn from RU.AVAIL_RES. It typically expresses the capacity available for the resource over the reporting period.

Together, ACT_RES_USAGE and AVAIL_RES allow utilization percentages to be derived by simple division.

Common Use Cases and Queries

The primary use case is resource utilization analysis: comparing consumed capacity against available capacity across plant, resource group, resource, and time. Because OPI is obsolete and the view is documented as not implemented, queries should be validated against the current database before reliance.

A representative query:

SELECT plant_id, cal_period_id, res_res_id,
       act_res_usage, avail_res,
       ROUND((act_res_usage / NULLIF(avail_res,0)) * 100, 2) util_pct
FROM   opi_edw_rsrc_util_v
WHERE  cal_yr_id = :p_year
ORDER  BY plant_id, cal_period_id;

Because the object is defined over EDW conformed dimensions and a single fact, it is well suited to aggregation by department or resource group (SUM of usage versus SUM of avail_res) and to calendar-period trending. Where the view is not implemented, equivalent reporting can be reconstructed by joining the same base objects directly, preserving the documented join predicates.