Search Results opi_dbi_inv_value_log

Overview

OPI_DBI_INV_VALUE_OPM_INIT_PKG is an initialization and extract-transform-load (ETL) utility package owned by the APPS schema in Oracle E-Business Suite. Its role is to prepare and populate the Oracle Process Manufacturing (OPM) inventory valuation data that feeds the Oracle Daily Business Intelligence (DBI) reporting layer. The package operates within the Process Manufacturing Intelligence (OPI) product family, whose schema and installation status are resolved at runtime through the FND_INSTALLATION.GET_APP_INFO API.

Before loading new valuation data, the package truncates or deletes stale rows from staging, base, log, conversion-rate, inception-quantity, and permanent subledger tables. This clean-and-reload pattern ensures that each ETL cycle begins from a consistent baseline, which is critical for the inventory value fact tables used by DBI dashboards. The header comment ($Header: OPIDIPIB.pls 120.1 2005/08/02) indicates the package has been stable since the 11i-era code line and was carried forward into 12.1.1 and 12.2.2 without structural redesign.

Key Procedures and Functions

The package exposes a single documented entry point: RUN_OPM_FIRST_ETL. As the name implies, this procedure drives the first-stage ETL for OPM inventory valuation. It orchestrates the cleanup of target tables and then initiates the population of the DBI inventory value fact table (OPI_DBI_INV_VALUE_F) from OPM subledger and inventory sources. The internal Clean_OPM_Tables routine supports this flow by truncating staging tables such as OPI_DBI_OPM_INV_STG, OPI_DBI_OPM_CONVERSION_RATES, and OPI_DBI_OPM_INCEPTION_QTY, and by deleting rows from OPI_DBI_INV_VALUE_F where source = 2 and from OPI_DBI_INV_VALUE_LOG where type IN ('GSL','OID'). Because the ETRM metadata documents only the procedure name and not its parameter list, no signature details are asserted here.

Tables Accessed

The package reads from a broad set of OPM and Oracle Inventory source tables and writes to the DBI valuation tables. Documented references via APPS synonyms include:

• OPI_DBI_INV_VALUE_F — the base inventory value fact table; rows with source = 2 are deleted during cleanup and subsequently repopulated.
• OPI_DBI_INV_VALUE_LOG — the ETL log table; entries of type GSL and OID are purged each cycle. Users searching for "opi_dbi_inv_value_log" are typically investigating these log rows for load status or error diagnostics.
• GME_BATCH_HEADER, GME_MATERIAL_DETAILS — OPM batch and material consumption data used to derive production-related inventory movements.
• IC_ITEM_MST_B, IC_LOCT_INV, IC_TRAN_CMP, IC_TRAN_PND, IC_WHSE_MST, IC_XFER_MST — OPM item master, location inventory, completed/pending transactions, warehouse, and transfer structures.
• MTL_SYSTEM_ITEMS_B, MTL_PARAMETERS, MTL_INTERORG_PARAMETERS, MTL_SUPPLY — Oracle Inventory item, organization, and supply sources for valuation context.
• OE_ORDER_LINES_ALL — order line data supporting demand-side valuation.
• GL_SUBR_LED — subledger accounting entries providing the financial valuation amounts.

Usage Notes

RUN_OPM_FIRST_ETL is intended to be invoked as a scheduled concurrent program or called programmatically as the initial step in the OPM inventory valuation collection cycle. It should be executed before incremental OPI DBI collections, since it establishes the baseline by clearing staging and base tables. The package is referenced by one other package, indicating it participates in a dependency chain rather than being called in isolation. Administrators troubleshooting load failures should inspect OPI_DBI_INV_VALUE_LOG for rows of type GSL or OID, bearing in mind that a subsequent run of this package deletes those entries. Direct invocation by custom code is possible but should be restricted to controlled maintenance windows because of the truncate operations.