Search Results get_onhand_activity

Overview

The APPS.OPI_DBI_INV_VALUE_INCR_PKG package body performs the incremental load collection of Inventory Management valuation data for the Oracle E-Business Suite Discrete Business Intelligence (DBI) reporting layer. Its responsibility is to consolidate on-hand, in-transit, and work-in-process value activity into a summary fact table so that inventory valuation pages reflect current balances without requiring a full rebuild of the warehouse. The package handles both Oracle Discrete Manufacturing (ODM) and Oracle Process Manufacturing (OPM) organization types, which is significant because the two models store transaction valuation data in distinct source tables.

The header comment (version 120.27) documents a deliberate design goal: the incremental load is re-runnable and restartable even after failure. All staging tables are truncated at the start of each run, and merging into the summary table is the final step. Should staging data not be cleared at the end of a run, it is discarded at the beginning of the next, guaranteeing a clean working set.

Key Procedures and Functions

The package exposes a single documented entry point, REFRESH. Unlike a simple data-pump routine, REFRESH is an orchestrating driver that sequences initialization, extraction, conversion, and merge activities. The inline documentation enumerates the internal call chain it invokes:

• CHECK_INCR_LOAD_SETUP — validates that the incremental load environment and parameters are correctly configured before processing begins.
• DISCRETE_REFRESH — the discrete-organisation collection branch, which in turn drives CLEAN_STAGING_TABLE, OPI_DBI_BOUNDS_PKG.MAINTAIN_OPI_DBI_LOGS, and GET_NET_ACTIVITY.
• GET_NET_ACTIVITY — decomposes activity into GET_ONHAND_ACTIVITY, GET_INTRANSIT_ACTIVITY, and GET_WIP_ACTIVITY.
• OPI_DBI_INV_VALUE_UTL_PKG.Get_Conversion_Rate — supplies currency conversion rates applied to collected amounts.
• MERGE_INTO_SUMMARY — the terminal merge step that publishes staging results into the summary fact.
• OPI_DBI_INV_CPCS_PKG.RUN_PERIOD_CLOSE_ADJUSTMENT — applies period-close valuation adjustments.
• OPI_DBI_BOUNDS_PKG.print_opi_org_bounds and SET_LOAD_SUCCESSFUL — diagnostics and load-status flagging.

Package-level state includes global initialization date, user, and login identifiers, primary and secondary currency codes with their associated rate types, and the owning schema name, all of which govern conversion behaviour across the run.

Tables Accessed

The package reads source transactions and writes staging and summary structures. Source reads span MTL_MATERIAL_TRANSACTIONS, MTL_TRANSACTION_ACCOUNTS, MTL_SYSTEM_ITEMS, MTL_SYSTEM_ITEMS_B, and MTL_PARAMETERS for discrete inventory movements and item/organisation context. OPM sources are GME_MATERIAL_DETAILS and GMF_TRANSACTION_VALUATION. Organisation-to-legal-entity mapping is resolved through OPI_DBI_ORG_LE_TEMP, and currency rates through OPI_DBI_CONVERSION_RATES. Staging targets are OPI_DBI_ONHAND_STG, OPI_DBI_INTRANSIT_STG, OPI_DBI_WIP_STG, and OPI_DBI_INTR_MIP_TMP. The merged output lands in OPI_DBI_INV_VALUE_F, with run auditing captured in OPI_DBI_CONC_PROG_RUN_LOG.

Usage Notes

REFRESH is invoked as the executable behind the DBI Inventory Value incremental concurrent program, typically scheduled after transactional activity has been posted and before inventory valuation dashboards are queried. Because it is restartable, a failed run may be resubmitted without manual staging cleanup. Custom integrations should not call internal procedures directly; invoking REFRESH is the supported pattern. A UOM_CONV_ERROR exception (error -20901) is declared and raised when unit-of-measure conversion cannot be completed for a transaction, which is the specific condition most commonly surfaced when this object appears in diagnostic searches for "uom_conv_error". Companion exceptions include INITIALIZATION_ERROR (-20900).