Search Results print_missing_rows

Overview

OPI_EDW_OPM_JOB_RSRC_F_C is the package body behind the Oracle Process Manufacturing (OPM) job resource fact collection used by the Oracle E-Business Suite Operational Data Store / Enterprise Data Warehouse (EDW). Its role is to stage and transform OPM batch job resource transactions — the labor, machine, and resource charges recorded against process batch operations — into the denormalized fact rows consumed by the OPI_EDW_OPM_JOB_RSRC_F materialized view and downstream EDW reporting. The package resolves the primary key JOB_RSRC_PK, which for OPM-sourced rows is a compound key assembled from PM_BTCH_HDR.BATCH_ID, PM_MATL_DTL.LINE_ID, PM_OPRN_DTL.BATCHSTEP_NO, PM_OPRN_DTL.RESOURCES, PM_OPRN_DTL.ACTIVITY, PM_OPRN_DTL.BATCHSTEPLINE_ID, the source instance code, and the literal suffix "OPM". The header comment block ($Header: OPIMOJRB.pls 115.13) indicates the last shipped revision dates to the 11i/12.0 era and is carried forward unchanged into 12.1.1 and 12.2.2. The user search term "job_rsrc_pk" corresponds directly to this package's central key column.

Key Procedures and Functions

The documented package metadata lists a single public procedure, PUSH, which drives the extraction of staged OPM job resource rows into the EDW fact structures. Beyond the public interface, the body defines several private helpers that govern its behavior:

• LOCAL_SAME_AS_REMOTE — a private function returning BOOLEAN. It compares the instance code from EDW_LOCAL_INSTANCE with the instance code from EDW_LOCAL_INSTANCE@EDW_APPS_TO_WH, returning TRUE when source and target staging schemas reside on the same database. This avoids unnecessary remote round-trips during collection.
• PRINT_MISSING_ROWS — a private procedure that opens a Missing_Rate cursor over OPI_EDW_JOB_RSRC_FSTG for rows whose COLLECTION_STATUS is "RATE NOT AVAILABLE" or "INVALID CURRENCY" and whose JOB_RSRC_PK carries the OPM suffix. It parses the key substring (the segment between the third-from-last and second-from-last hyphen) and reports rows whose PRIMARY_KEY is absent or has a NULL SEQ_ID in OPI_EDW_OPM_JOB_RSRC_INC. Output is written to the EDW_LOG utility.

Tables Accessed

The package reads and writes the following objects via APPS synonyms:

• EDW_LOCAL_INSTANCE (plus the database link @EDW_APPS_TO_WH) — source and target instance identification.
• OPI_EDW_JOB_RSRC_FSTG — the shared staging table holding job resource fact rows and their collection status.
• OPI_EDW_OPM_JOB_RSRC_INC — the OPM-specific incremental key table holding PRIMARY_KEY and SEQ_ID used for change detection.
• OPI_EDW_JOB_RSRC_INC_S — the incremental sequence/support structure used to assign new fact identifiers.
• PM_BTCH_HDR and PM_OPRN_DTL — OPM batch header and operation detail tables that supply the components of the compound JOB_RSRC_PK.
• DUAL — used for single-row control queries.

Usage Notes

OPI_EDW_OPM_JOB_RSRC_F_C is not a user-facing API; API classification is OTHER. It is invoked indirectly by the EDW fact collection concurrent programs that populate and refresh the OPM job resource fact, typically as part of a scheduled warehouse load. The PUSH procedure is called by the EDW collection driver after staging completes, at which point the package evaluates LOCAL_SAME_AS_REMOTE to determine access strategy and processes the staged rows. Diagnostic output, including missing-rate rows, is written through EDW_LOG for review in the concurrent request log. The package is referenced by zero other packages, confirming that its public entry points are consumed only by the EDW collection framework and not by custom extensions.