Search Results setup_inv_mmt_bounds




Overview
Explore in AI Chat

Overview

OPI_DBI_BOUNDS_PKG is an Oracle Applications (APPS) PL/SQL package belonging to the Oracle Process Manufacturing (OPM) intelligence and inventory costing subsystem. Its principal responsibility is to establish, maintain, and advance the date and identifier boundaries that govern incremental extract, transform, and load (ETL) processing for the Oracle Daily Business Intelligence (DBI) inventory and cost-of-goods-sold fact tables. In the Oracle EBS 12.1.1 and 12.2.2 environments, DBI collections rely on "high-water mark" style boundaries so that only transactions changed since the previous successful run are processed. This package encapsulates that boundary logic, tracks the status of concurrent program runs, and records diagnostic information for each ETL invocation.

The package is classified as OTHER in the ETRM repository, indicating it is an internal support package rather than a published business API. It is referenced by eight other packages, several of which are likely the OPM DBI collection programs that depend on it to determine their extraction ranges.

Key Procedures and Functions

The package exposes fourteen documented procedures and functions. Their documented purposes are as follows:

  • MAINTAIN_OPI_DBI_LOGS — the entry point most closely associated with the user search. It maintains the OPI DBI log tables, recording and updating status for a given ETL type and load type. This is the procedure that governs log bookkeeping across collection runs.
  • CALL_ETL_SPECIFIC_BOUND — dispatches control to the boundary setup routine appropriate for the requested ETL and load type.
  • SETUP_INV_MMT_BOUNDS — establishes the material transaction boundaries used for inventory fact collection.
  • SETUP_COGS_MMT_BOUNDS — establishes material transaction boundaries specifically for cost-of-goods-sold collection.
  • CREATE_FIRST_MMT_BOUNDS — creates the initial boundary set when no prior high-water mark exists for the ETL type.
  • SET_MMT_NEW_BOUNDS — advances the material transaction boundaries after a successful collection.
  • SETUP_INV_WTA_BOUNDS — establishes boundaries based on WIP transaction account activity for inventory collection.
  • SET_SYSDATE_BOUNDS — sets a boundary using the current system date for a specified ETL type, load type, and driving table code.
  • SETUP_CC_MMT_BOUNDS — establishes material transaction boundaries for the cost collection process.
  • SET_LOAD_SUCCESSFUL — marks an ETL/load run as successfully completed, which typically triggers boundary advancement.
  • PRINT_OPI_ORG_BOUNDS — outputs or logs the current organization-level boundary values for diagnostic purposes.
  • BOUNDS_UNCOSTED — a Boolean function that reports whether uncosted transactions exist within the current boundary window.
  • WRITE — a forward-declared utility procedure used for debug and message output.
  • LOAD_OPM_ORG_LEDGER_DATA — loads organization and ledger mapping data used to resolve boundaries across OPM organizations.

Tables Accessed

The package accesses tables through APPS synonyms. MTL_MATERIAL_TRANSACTIONS supplies the transaction activity from which inventory boundaries are derived, while WIP_TRANSACTION_ACCOUNTS provides the corresponding work-in-process accounting activity used by the WIP boundary routine. MTL_PARAMETERS and GMF_FISCAL_POLICIES supply organization-level configuration and fiscal calendar context needed to translate transaction dates into valid accounting periods. OPI_DBI_ORG_LE_TEMP is a temporary staging structure for organization and ledger data loaded by LOAD_OPM_ORG_LEDGER_DATA. OPI_DBI_CONC_PROG_RUN_LOG records the outcome of concurrent program runs and is central to the logging behavior managed by MAINTAIN_OPI_DBI_LOGS and SET_LOAD_SUCCESSFUL. DUAL is used for simple value evaluation and sysdate assignment.

Usage Notes

OPI_DBI_BOUNDS_PKG is not intended for direct end-user invocation. It is called by the OPM DBI collection concurrent programs, which pass an ETL type and load type to drive the appropriate boundary logic. Typical execution follows a fixed sequence: boundaries are validated or initialized, the collection program extracts data within the resulting window, and on success SET_LOAD_SUCCESSFUL and the associated boundary advancement procedures are invoked. Custom code should not call these procedures directly; any integration should use the supported DBI collection programs. When troubleshooting a collection failure, DBAs commonly review OPI_DBI_CONC_PROG_RUN_LOG and the output of PRINT_OPI_ORG_BOUNDS to confirm boundary values and run status. Because MAINTAIN_OPI_DBI_LOGS and SET_LOAD_SUCCESSFUL write to the run log, these procedures are the primary points of visibility into ETL execution history.