Search Results opi_edw_res_util_push_log

Overview

OPI_EDW_RES_UTIL_PUSH_LOG is a staging and intermediate persistence table within the Oracle Operations Intelligence (OPI) product family, residing in the OPI schema. Its documented purpose is to store the intermediate result of data extracted from the discrete manufacturing side of Oracle E-Business Suite for the Resource Utilization fact. In practical terms, the table acts as a landing zone between the transactional manufacturing tables (work in process, bills of material, routing, and resource definitions) and the Enterprise Data Warehouse layer that ultimately publishes resource utilization analytics.

From a dimensional modeling perspective, the metadata classifies this object as standalone under the heuristic Data Vault classification mined from its foreign key structure. No FK relationships to parent tables are documented, which suggests the table is loaded through a programmatic extract-transform-load (ETL) process rather than through declarative referential integrity. In a Data Vault model, this row-level, date-stamped, and program-audited structure is best characterized as a satellite candidate, capturing descriptive measures and context for a resource utilization business key. This classification is a modeling suggestion only; in the delivered OPI implementation the table is used as a flat staging structure rather than a formally normalized vault entity.

The single documented primary key constraint, OPI_EDW_RES_UTIL_PUSH_LOG_PK, is defined on the RES_UTIL_PK column, which serves as the surrogate identifier for each pushed utilization record. The table is present and valid in both Oracle EBS 12.1.1 and 12.2.2, with 17 documented columns in the ETRM 12.1.1 physical schema.

Key Information Stored

The most significant columns fall into three functional groups: dimensional keys, utilization measures, and ETL audit columns.

• RES_UTIL_PK — the surrogate primary key and the sole column in the OPI_EDW_RES_UTIL_PUSH_LOG_PK constraint. It uniquely identifies each staged resource utilization record.
• ORGANIZATION_CODE — the discrete manufacturing organization from which the utilization data was extracted; the primary business partitioning attribute.
• RESOURCE_CODE — identifies the manufacturing resource whose capacity and usage are being measured.
• DEPARTMENT_CODE — the department owning the resource, enabling departmental roll-up reporting.
• TRX_DATE — the transaction or accounting date against which utilization is recorded; the principal time dimension for the fact.
• SOB_ID — the set of books identifier, linking the record to the correct ledger context for financial reconciliation.
• UOM — the unit of measure in which resource usage and availability are expressed, ensuring consistent aggregation.
• ACT_RES_USAGE — actual resource usage, the core measure of the utilization fact.
• AVAIL_RES — available resource capacity for the corresponding period, the denominator against which utilization percentages are computed.
• CREATION_DATE / CREATED_BY / LAST_UPDATE_DATE / LAST_UPDATED_BY — standard Oracle EBS auditing columns that record who inserted or last modified each staged row and when.
• REQUEST_ID / PROGRAM_APPLICATION_ID / PROGRAM_ID / PROGRAM_UPDATE_DATE — concurrent program context columns that identify the extraction job responsible for populating the row.

Business-key candidacy is not documented through a unique index; the natural identifier would be the combination of ORGANIZATION_CODE, RESOURCE_CODE, DEPARTMENT_CODE, TRX_DATE, and SOB_ID.

Common Use Cases and Queries

The primary use case is validating and troubleshooting the OPI resource utilization extraction. Because rows persist with their concurrent request identifiers, support teams can isolate the output of a specific run. A typical diagnostic query is:

• SELECT organization_code, resource_code, trx_date, act_res_usage, avail_res FROM opi.opi_edw_res_util_push_log WHERE request_id = :request_id;

A second use case computes utilization percentage by resource and period for reconciliation against the published EDW fact:

• SELECT resource_code, SUM(act_res_usage) actual, SUM(avail_res) available, ROUND(SUM(act_res_usage)/SUM(avail_res)*100, 2) util_pct FROM opi.opi_edw_res_util_push_log WHERE trx_date BETWEEN :from_date AND :to_date GROUP BY resource_code;

Third, the table supports pre-publication quality checks — identifying rows with zero AVAIL_RES, null UOM, or missing SOB_ID before they propagate to downstream facts. Finally, release and upgrade testing uses row counts by PROGRAM_ID to confirm that the OPI extraction concurrent programs behave consistently between 12.1.1 and 12.2.2.

Related Objects

The documented metadata records no foreign keys, so relationships are functional rather than declarative. The following objects are the most significant dependencies and join targets:

• OPI_EDW_RES_UTIL_PUSH_LOG_PK — the primary key constraint on RES_UTIL_PK that enforces uniqueness of staged rows.
• WIP_RESOURCE_UTILIZATION / WIP resource base tables — source of the discrete manufacturing resource usage and capacity values extracted into this log.
• BOM_DEPARTMENTS and BOM_RESOURCES — provide the DEPARTMENT_CODE and RESOURCE_CODE reference context for joining descriptive attributes.
• ORG_ORGANIZATION_DEFINITIONS — resolves ORGANIZATION_CODE and the operating unit for reporting.
• GL_SETS_OF_BOOKS / FND_SETID tables — used to translate SOB_ID into ledger names for financial alignment.
• FND_CONCURRENT_REQUESTS — joined on REQUEST_ID to trace which concurrent program run populated each staged row.
• OPI EDW Resource Utilization fact and related OPI_EDW staging tables — downstream consumers that read from this push log as their intermediate source.