Search Results run_opm_first_etl

Overview

OPI_DBI_INV_VALUE_OPM_INIT_PKG is an Oracle E-Business Suite PL/SQL package owned by the APPS schema. It belongs to the Oracle Process Manufacturing (OPM) inventory valuation reporting layer and supports the Oracle Daily Business Intelligence (DBI) initiative for inventory value analytics. Its documented classification is OTHER, indicating that it is not part of the public, supported applications programming interface set but rather an internal utility package.

The package declares AUTHID CURRENT_USER, meaning that its SQL statements execute with the privileges of the invoking user rather than the privileges of the APPS owner. The header comment identifies the source file as OPIDIPIS.pls, with a header string of version 115.1 dated 2003/04/29, and the noship flag, which confirms that the package is not shipped as a standalone object but is deployed as part of a broader OPM/DBI patch or installation.

The business function of the package is to perform the initial extraction, transformation, and load (ETL) of OPM inventory valuation data into the DBI inventory value fact structures. This first-time ETL is a prerequisite step that seeds the DBI inventory value tables before incremental or scheduled refreshes take over.

Key Procedures and Functions

The package exposes a single documented procedure, RUN_OPM_FIRST_ETL. Its declared interface uses the conventional concurrent program signatures errbuf IN OUT NOCOPY VARCHAR2 and retcode IN OUT NOCOPY VARCHAR2, which allows the procedure to be registered and executed as an Oracle EBS concurrent program and to return standard success or failure codes to the concurrent manager.

The procedure name indicates that it drives the first, or initial, ETL run for OPM inventory valuation data. In practice it gathers the required source transactions and balances from the OPM inventory and purchasing tables and populates the OPI_DBI_INV_VALUE_F fact table. No public functions are declared in the documented package specification; the specification closes immediately after the procedure declaration. Parameter behavior beyond the standard error buffer and return code should not be assumed from the ETRM excerpt.

Tables Accessed

The ETRM metadata records references to a set of source and target tables through APPS synonyms. The fact table populated by the ETL is OPI_DBI_INV_VALUE_F. Supporting OPM master and transaction data is drawn from GME_BATCH_HEADER and GME_MATERIAL_DETAILS (process batch and material consumption data), IC_ITEM_MST_B (item master), IC_LOCT_INV (location inventory balances), IC_TRAN_CMP and IC_TRAN_PND (completed and pending inventory transactions), IC_WHSE_MST (warehouse master), and IC_XFER_MST (transfer orders).

Standard Oracle inventory and order management tables are also referenced, including MTL_SYSTEM_ITEMS_B, MTL_PARAMETERS, MTL_INTERORG_PARAMETERS, MTL_SUPPLY, and OE_ORDER_LINES_ALL, which supply item definitions, organization parameters, supply sources, and sales order line demand. GL_SUBR_LED provides subledger accounting entries that underpin valuation amounts. These references enable the initial ETL to reconcile on-hand quantities and costs across OPM and discrete inventory sources into a single inventory value representation.

Usage Notes

RUN_OPM_FIRST_ETL is intended to be invoked as a one-time initialization step, typically through a concurrent program definition registered against this package, or through custom initialization scripts executed during DBI or OPM reporting setup. It is referenced by at least one other package, indicating that it may also be called programmatically as part of a larger DBI collection sequence. Because the package is not shipped (noship) and is classified as OTHER, it is not a supported public API, and direct custom calls should be avoided unless required for a specific initialization scenario. The AUTHID CURRENT_USER declaration means the executing user must hold the necessary privileges on the referenced OPM and Oracle Inventory tables.