Search Results refresh_opm_margin

Overview

OPI_DBI_COGS_OPM_PKG is a PL/SQL package owned by APPS in the Oracle E-Business Suite, declared with AUTHID CURRENT_USER. It functions as the cost-of-goods-sold (COGS) extraction and staging component of the Oracle Process Manufacturing (OPM) extract-transform-load (ETL) framework used by Oracle Daily Business Intelligence (DBI). Its documented purpose is to orchestrate refresh activity against the OPI_DBI_COGS_FSTG staging table, which supplies the margin and COGS reporting subject areas. The package provides two documented entry points: one for the initial or complete load and one for the incremental load. The package header was authored in 2004 and the documented procedures retain the signature pattern required by Oracle Concurrent Manager, returning status through the standard errbuf and retcode OUT parameters together with package-level constants g_OK, g_WARNING, and g_ERROR mapped to 0, 1, and -1 respectively.

Key Procedures and Functions

Two procedures are documented in the ETRM metadata for this object:

• COMPLETE_REFRESH_OPM_MARGIN — described in the source as the wrapper routine for the initial load of COGS for the OPM ETL. It accepts the standard concurrent request OUT parameters (errbuf, retcode) and, in the documented specification, an optional degree parameter used for parallel execution. Its stated purpose is to drive a full refresh of the OPM margin data set rather than a delta.
• REFRESH_OPM_MARGIN — the procedure matching the user search term. It is documented as the wrapper routine for the incremental load of COGS for the OPM ETL. It carries only the standard errbuf and retcode parameters. Its purpose is to process only the changed or newly eligible transactions since the prior successful run, using the run log to determine the extraction window.

Both procedures return retcode values consistent with the package constants: 0 for success, 1 for warning, and -1 for error, with errbuf empty on success and populated with a message otherwise.

Tables Accessed

The documented objects referenced through APPS synonyms indicate the extraction, staging, and audit footprint of the package:

• OPI_DBI_COGS_FSTG — the primary staging table populated by the refresh routines.
• OPI_DBI_COGS_RUN_LOG — the run history used to track refresh outcomes and establish incremental windows.
• RCV_TRANSACTIONS and OE_ORDER_LINES_ALL — receipt and order line sources used to derive cost and revenue movement.
• IC_TRAN_PND and IC_WHSE_MST — intercompany pending transactions and warehouse master, used for inventory and transfer valuation context.
• MTL_SYSTEM_ITEMS_B — item master attributes for costed items.
• HZ_CUST_ACCOUNTS — customer account data supporting margin and selling-party analysis.
• GL_SUBR_LED and GL_SUBR_TST — general ledger subledger and trial balance staging used to reconcile COGS amounts.
• DBMS_SQL and DUAL — used for dynamic SQL execution and single-row control logic.

Usage Notes

The package is designed to be invoked from Oracle Concurrent Manager. COMPLETE_REFRESH_OPM_MARGIN is run during initial DBI implementation or as a data recovery step when the OPM margin staging table must be rebuilt in full. REFRESH_OPM_MARGIN is scheduled as a recurring incremental program after the initial load, producing the delta population consumed by the DBI COGS and margin subject areas. Both procedures conform to the standard concurrent program interface, allowing direct registration on a concurrent program definition with errbuf and retcode bound to the request output and completion status. The ETRM metadata records no packages that reference this object, indicating it is a top-level driver rather than a dependency of other PL/SQL APIs; custom code should call it only through the concurrent program interface to preserve run-log and error-handling behavior.