Search Results load_demand

Overview

MSC_CL_DEMAND_ODS_LOAD is an Oracle Advanced Supply Chain Planning (ASCP) internal PL/SQL package owned by the APPS schema. It is responsible for the extract, transform, and load (ETL) of demand-related records from the Operational Data Store (ODS) staging area into the MSC plan-side demand tables used by the planning engine. In the ASCP architecture, data from source applications such as Oracle Order Management, Oracle Manufacturing, and Oracle Inventory is collected into ODS staging tables by collection programs, and subsequently moved into planning tables for the unconstrained and constrained plan runs. MSC_CL_DEMAND_ODS_LOAD performs this final movement for demand, forecasts, sales orders, reservations, and designators.

The package is declared with AUTHID CURRENT_USER, meaning that it executes with the privileges of the invoking user rather than the definer, a standard pattern for ASCP collection and plan-load utilities. Its header, dated 2011/02/24, indicates a mature, stable interface carried forward into EBS 12.1.1 and 12.2.2. The classification of OTHER reflects that it is an internal, non-public API, not intended as a formal integration point.

Key Procedures and Functions

The package exposes 16 documented program units. The load routines are the primary entry points:

• LOAD_ITEM_FORECASTS — Loads item-level forecast demand from ODS staging into the planning demand structures. The comment in the source states it is "called by load_supply," confirming it is invoked from the supply loading orchestration rather than independently.
• LOAD_FORECASTS — Processes forecast demand records more broadly.
• LOAD_CMRO_EAM_FORECASTS — Handles forecasts originating from Enterprise Asset Management and component/maintenance repair order scenarios.
• LOAD_DEMAND — General demand loader, also documented as called by LOAD_SUPPLY.
• LOAD_SALES_ORDER — Loads sales order demand; documented as called from load_supply.
• LOAD_HARD_RESERVATION — Loads hard reservation records into planning tables.
• LOAD_DESIGNATOR — Loads designator (configuration/specification) relationship data.
• LOAD_ODS_DEMAND — The core routine migrating demand rows from ODS into the demand temporary table, as described in the header comment.
• LOAD_PAYBACK_DEMANDS — Loads payback demand records.

Supporting linking and utility routines include LINK_SUPP_SO_DEMAND_EXT, LINK_SUPP_SO_DEMAND_110, and LINK_SUPP_SO_DEMAND_11I2, which resolve supporting sales order demand for different EBS release versions (external, 11.0, and 11i). LINK_PARENT_SALES_ORDERS and LINK_PARENT_SALES_ORDERS_MDS associate child sales order lines with parent records for planning and for MDS (Master Demand Schedule) contexts. DROP_DEMANDS_TMP_IND and DROP_SALES_ORDERS_TMP_IND are Boolean functions that drop temporary indexes prior to bulk loading, a common performance optimization to avoid index maintenance overhead during high-volume inserts. No parameter lists are documented and none should be assumed.

Tables Accessed

The package references staging (MSC_ST_*) and plan-side tables through APPS synonyms:

• MSC_ST_DEMANDS and MSC_DEMANDS — Source ODS demand and target planning demand.
• MSC_ST_SALES_ORDERS and MSC_SALES_ORDERS — Staged and plan-side sales order demand.
• MSC_ST_RESERVATIONS and MSC_RESERVATIONS — Staged and plan-side hard reservations.
• MSC_ST_DESIGNATORS, MSC_DESIGNATORS, and MSC_DESIGNATORS_S — Staged and plan-side designator data.
• MSC_ITEM_ID_LID, MSC_TP_ID_LID, MSC_TP_SITE_ID_LID — Key-mapping/lookup tables resolving item, trading partner, and trading partner site identifiers to internal planning IDs.
• MSC_APPS_INSTANCES — Identifies the source application instance from which data originated.
• AD_DDL — Used by the DROP_*_TMP_IND routines, which employ AD_DDL.DDL_SQL to execute dynamic DDL for dropping temporary indexes.
• DUAL — Standard singleton queries.

Usage Notes

MSC_CL_DEMAND_ODS_LOAD is internal ASCP infrastructure invoked during the data loading phase preceding a plan run. Its routines are called primarily from the supply-load orchestration layer (referenced as LOAD_SUPPLY in the source comments), which sequences the movement of collected data from ODS staging into planning tables. The package is referenced by one other package, consistent with tight coupling to the collection and plan-load framework.

Because the package is classified OTHER and marked Oracle Proprietary/Confidential, direct invocation from forms, concurrent programs, or custom code is not supported. Customers and integrators should not call these procedures directly; ASCP behavior is driven through the standard collection and plan execution flow. Customizations requiring demand data should read the materialized plan-side tables (MSC_DEMANDS, MSC_SALES_ORDERS) rather than invoking this loader. The version-specific linking routines confirm the package abstracts release-level differences across the EBS 11.x and 12.x data models, allowing the same planning engine to operate on either release.