Search Results get_instances

Overview

MSD_DEM_COLLECT_LEVEL_TYPES is an Oracle E-Business Suite PL/SQL package body owned by the APPS schema that supports the Demand Planning and collections framework within Oracle Advanced Supply Chain Planning (ASCP) / ETRM. Its business purpose is to manage the collection level types used when staging demand planning data, and to prepare the temporary staging tables that feed the demand planning engine. The package operates on global temporary tables such as MSD_DEM_LOCATIONS_GTT and MSD_DEM_ITEMS_GTT, which act as intermediate storage for location and item demand records prior to transformation into planning data. The package is classified under the ETRM documentation taxonomy as an OTHER API, meaning it is a supporting internal utility rather than a public, callable business API. Its primary responsibilities include de-duplicating staged demand records across multiple application instances and populating the temporary tables used by downstream collection and planning processes. The header comment indicates a version lineage dating to 2007 under the 120.x code line, which is consistent with its presence in EBS 12.1.1 and 12.2.2.

Key Procedures and Functions

The documented interface exposes one procedure, COLLECT_LEVELS, which serves as the entry point for the collection-level processing handled by this package. Beyond the documented procedure, the package body contains internal helper procedures that drive the core logic.

• delete_duplicates — Accepts an output return code and removes duplicate rows from the MSD_DEM_ITEMS_GTT staging table. It iterates over application instances and dynamically deletes duplicate item/demand-class combinations that exceed a count of one, scoped by source instance identifier.
• populate_temporary_tables — Accepts standard concurrent program OUT parameters (errbuf, retcode) plus instance, collect-level-type, and plan identifiers. It resolves the sales staging table name via a lookup and reads plan metadata before populating the temporary staging tables.
• get_instances — An internal cursor (not a standalone procedure) that selects instance_id values from MSC_APPS_INSTANCES in ascending order. This cursor is the mechanism that allows the delete logic to operate across every registered application instance in a multi-instance environment.

Tables Accessed

• MSC_APPS_INSTANCES — Read by the get_instances cursor to enumerate application instance IDs for cross-instance de-duplication.
• MSC_PLANS — Read to retrieve plan_type for the supplied plan_id, governing how staging data is prepared.
• MSD_DEM_NEW_ITEMS — Referenced as part of the demand item staging and filtering logic.
• DBA_TABLES / DBA_TAB_COLUMNS — Used for metadata validation of staging and temporary table structures.
• DUAL — Used for lookup and scalar utility queries.
• DBMS_OUTPUT — Used for diagnostic output during execution.

Global temporary tables MSD_DEM_ITEMS_GTT and MSD_DEM_LOCATIONS_GTT are also manipulated directly through dynamic SQL to hold transient collection data.

Usage Notes

This package is invoked indirectly rather than through a user-facing form. It is called by the demand planning collections concurrent programs and by one additional referencing package, which orchestrates the staging of demand data at a given collection level. The delete_duplicates routine is invoked to ensure idempotent staging across multiple instances, while populate_temporary_tables runs as part of a concurrent request with errbuf and retcode parameters. Because the package relies on dynamic SQL and DBA_ view access, it requires privileges consistent with APPS schema execution. Customizations should avoid direct calls; instead, invoke the standard concurrent program that wraps COLLECT_LEVELS to preserve supported upgrade paths across 12.1.1 and 12.2.2.