Search Results merge_into_summary

Overview

OPI_DBI_INV_CPCS_PKG is an APPS-owned PL/SQL package body that supports the Oracle Inventory (OPI) dimensional business intelligence (DBI) collection process in Oracle E-Business Suite 12.1.1 and 12.2.2. Its primary responsibility is to prepare and populate the inventory value fact tables used by the Discrete Business Intelligence / Inventory Value reporting layer. The package controls the staging, transformation, and summarization of on-hand, in-transit, and period-close adjustment data so that downstream inventory valuation reports and analytics reflect current and historical balances. The package is classified as "OTHER" in the ETRM metadata, indicating that it is not a standard public API but an internal collection routine invoked as part of the DBI data warehouse refresh cycle.

Key Procedures and Functions

• RUN_PERIOD_CLOSE_ADJUSTMENT — The single documented procedure in this package. It performs the period-close adjustment processing that aligns inventory value fact records with closed accounting periods. It is the entry point that drives the adjustment logic used to reconcile inventory balances across period boundaries within the DBI collection framework.
• Clean_Staging_Table — An internal function that truncates the staging tables OPI_DBI_ONHAND_STG, OPI_DBI_INTRANSIT_STG, and OPI_DBI_CONVERSION_RATES using dynamic SQL against the OPI schema returned by fnd_installation.get_app_info. It returns a constant success or error code and rolls back on failure.
• Merge_Into_Summary — The function matched by the searched term. It inserts rows into the inventory value fact table OPI_DBI_INV_VALUE_F using an append hint with parallel execution. Its parameters are declared as standard concurrent-program errbuf and retcode OUT NOCOPY variables, and it returns a numeric status code.

Tables Accessed

The package reads and writes a defined set of tables via APPS synonyms:

• OPI_DBI_INV_VALUE_F — The target fact table populated by Merge_Into_Summary for inventory valuation reporting.
• OPI_DBI_ONHAND_STG, OPI_DBI_INTRANSIT_STG, OPI_DBI_CONVERSION_RATES — Staging tables truncated by Clean_Staging_Table prior to collection.
• OPI_DBI_CONC_PROG_RUN_LOG and OPI_DBI_INV_VALUE_LOG — Logging tables used to record concurrent program execution and fact-table load activity.
• CST_PERIOD_CLOSE_SUMMARY, MTL_MATERIAL_TRANSACTIONS, MTL_PARAMETERS, MTL_SYSTEM_ITEMS_B, ORG_ACCT_PERIODS, and DUAL — Source tables supplying period-close balances, transaction detail, organization parameters, item master data, and accounting period definitions.

Usage Notes

OPI_DBI_INV_CPCS_PKG is typically invoked as a concurrent program step within the DBI inventory collection flow rather than from a form or direct user session. Because the metadata indicates it is referenced by two other packages, it functions as a shared internal service called by orchestration packages that sequence staging cleanup, data load, and summarization. The Merge_Into_Summary entry, in particular, is designed to run with parallel DML enabled, so DBAs should verify the parallel degree settings and tablespace capacity before invocation. When customizing or extending the DBI inventory value process, developers should treat this package as internal and call it only through its documented entry point, RUN_PERIOD_CLOSE_ADJUSTMENT, to preserve consistency of the value fact tables and their logs.