Search Results opi_edw_res_util_push_log_n

Overview

OPI.OPI_EDW_RES_UTIL_PUSH_LOG is a staging and logging table within the Oracle E-Business Suite Operational Intelligence (OPI) schema. It stores the intermediate result set of Resource Utilization fact data extracted from the discrete manufacturing side of the application, prior to its movement into the enterprise data warehouse. In EBS 12.1.1 and 12.2.2, this object functions as the persistence layer for the push process that collects manufacturing resource consumption and availability metrics, enabling downstream OBIEE or EDW reporting on resource efficiency. Its documented status is VALID and it resides in the APPS_TS_SUMMARY tablespace, an appropriate location for aggregated or pre-fact summary data.

The metadata classifies the object heuristically as standalone within a Data Vault model, meaning it has no foreign key dependencies on other tables and is therefore best modeled as an independent hub or as a raw staging satellite attached to a resource-utilization hub. Because it captures a combination of descriptive business keys and numeric measures, it behaves more like a satellite carrying snapshot-style facts than a pure hub.

Key Information Stored

The table contains 17 documented columns. The surrogate primary key is RES_UTIL_PK, a VARCHAR2(240) column that uniquely identifies each push record and also backs the non-unique index OPI_EDW_RES_UTIL_PUSH_LOG_N. Although the ETRM metadata presents RES_UTIL_PK as the primary key, the natural business key for this fact is the composite of ORGANIZATION_CODE, RESOURCE_CODE, DEPARTMENT_CODE, TRX_DATE, and SOB_ID, which together describe the manufacturing context of a resource utilization measurement.

• RES_UTIL_PK — surrogate identifier for each intermediate push record.
• ORGANIZATION_CODE — the manufacturing organization from which the resource utilization was extracted.
• RESOURCE_CODE — identifies the specific resource whose usage is being measured.
• DEPARTMENT_CODE — the department owning the resource.
• TRX_DATE — transaction date anchoring the utilization snapshot in time.
• SOB_ID — set of books identifier, tying the record to the correct ledger context.
• UOM — unit of measure applicable to the reported quantities.
• ACT_RES_USAGE — the actual resource usage quantity, the primary numeric measure.
• AVAIL_RES — the available resource quantity, enabling utilization percentage calculations.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — concurrent program and WHO audit columns identifying the extraction job and its timing.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY — standard WHO audit trail columns.

Because the object is standalone with no documented foreign key references, joins to master data such as organization or resource definitions must be performed on the code columns rather than on numeric surrogate keys.

Common Use Cases and Queries

The most common use case is validating and auditing the resource utilization extraction. Queries typically select records for a given organization or transaction period, or aggregate actual versus available resource usage:

• Reconciling extracted rows against source manufacturing transactions by filtering on TRX_DATE and ORGANIZATION_CODE.
• Computing utilization ratio as ACT_RES_USAGE divided by AVAIL_RES for resource efficiency reporting.
• Reviewing the REQUEST_ID and PROGRAM_ID columns to confirm which concurrent program populated a given batch.
• Detecting incomplete or duplicate pushes by grouping on ORGANIZATION_CODE, RESOURCE_CODE, DEPARTMENT_CODE, TRX_DATE, and SOB_ID.

A representative pattern is: SELECT ORGANIZATION_CODE, RESOURCE_CODE, TRX_DATE, ACT_RES_USAGE, AVAIL_RES FROM OPI.OPI_EDW_RES_UTIL_PUSH_LOG WHERE TRX_DATE BETWEEN :start_date AND :end_date AND SOB_ID = :sob_id; This supports period-based utilization dashboards and troubleshooting of EDW load issues in both 12.1.1 and 12.2.2 environments.

Related Objects

The documented dependency section indicates that OPI_EDW_RES_UTIL_PUSH_LOG does not reference any database object and is referenced by the APPS synonym OPI_EDW_RES_UTIL_PUSH_LOG. In practice, the most significant related objects are the source manufacturing resource-utilization tables that feed this staging object and the EDW target structures that consume it. Relevant associations include organization and resource definition tables joined via ORGANIZATION_CODE and RESOURCE_CODE, the GL set of books entities referenced by SOB_ID, and the concurrent program and request metadata in FND_CONCURRENT_REQUESTS matched through REQUEST_ID. Because no foreign keys are documented, these relationships are logical rather than enforced, and integrators should treat the object as an isolated staging surface whose joins rely on coded business keys.