Search Results find_missing_rate_records




Overview
Explore in AI Chat

Overview

OPI_EDW_OPI_JOB_RSRC_F_C is an APPS-owned PL/SQL package that belongs to the Oracle Process Manufacturing (OPM) and Oracle Discrete Manufacturing reporting integration layer within the Oracle E-Business Suite data warehouse extraction framework. Its documented purpose is to extract, stage, and load job resource fact data — production labour and machine resource consumption captured against work in process jobs and schedules — into the OPI Enterprise Data Warehouse (EDW) fact tables. The package name follows the standard OPI EDW fact-loading convention, where the trailing "_F" denotes a fact table loader and "_C" identifies the specification body. The header comment references OPIMJRSS.pls and dates the source to 2005, placing this object within the 11i lineage that was carried forward into Release 12.1.1 and 12.2.2. The API classification is OTHER, indicating it is not part of a formally published public API surface but is instead an internal staging and transformation component invoked by higher-level EDW extraction programs.

Key Procedures and Functions

The package specification documents four program units:

  • PUSH — The primary driver procedure. It accepts an error buffer, a return code, and a date range (p_from_date and p_to_date) that scopes the extraction window. PUSH orchestrates the full fact load cycle for job resource data, propagating status back to the calling concurrent program through the OUT parameters.
  • PUSH_TO_LOCAL — A helper function that takes a view identifier and a sequence identifier and returns a numeric status. It moves or resolves staged rows for a specific extraction view and sequence batch, supporting the incremental load pattern used across OPI EDW fact packages.
  • FIND_MISSING_RATE_RECORDS — A diagnostic function taking a view identifier and returning a count of records for which resource rate information could not be resolved. This supports data quality validation, alerting the user when costed resource activity cannot be matched to rates.
  • DELETE_STG — A staging cleanup procedure, invoked to purge rows from the job resource staging table after a successful load cycle, preventing duplicate extraction on subsequent runs.

Tables Accessed

The package reads from and writes to a substantial set of manufacturing and HR tables via APPS synonyms. Source-side tables include WIP_DISCRETE_JOBS, WIP_REPETITIVE_SCHEDULES, WIP_FLOW_SCHEDULES, WIP_ENTITIES, WIP_OPERATIONS, WIP_OPERATION_RESOURCES, and WIP_TRANSACTIONS, which supply job, schedule, operation, and resource transaction detail. BOM_OPERATIONAL_ROUTINGS, BOM_OPERATION_SEQUENCES, and BOM_DEPARTMENTS provide routing, sequence, and department context, while HR_ORGANIZATION_INFORMATION supplies organisation attributes for the inventory organisation dimension. On the target side, the package populates OPI_EDW_OPI_JOB_RSRC_INC (the incremental staging collection), OPI_EDW_JOB_RSRC_INC_S, OPI_EDW_JOB_RSRC_FSTG (the fact staging table), and OPI_EDW_OPI_JOB_RSRC_MR_TMP (a temporary table used for missing-rate analysis). DELETE_STG operates against the staging table.

Usage Notes

OPI_EDW_OPI_JOB_RSRC_F_C is typically invoked from the OPI EDW concurrent program framework, where PUSH is called with a user-supplied date range to extract a defined period of job resource activity. The package is referenced by one other package, indicating it is a dependent component rather than a top-level entry point. DELETE_STG is called after a successful load to maintain staging hygiene, and is the object most commonly located by administrators searching for "delete_stg". Because the package is classified OTHER and is not a published API, customisations should avoid direct calls; instead, extend or wrap the EDW extraction program. Behaviour is consistent across 12.1.1 and 12.2.2, as the underlying WIP and BOM schemas were not structurally altered in a way affecting this logic.