Search Results create_supplies_tmp_ind

Overview

The APPS.MSC_CL_SUPPLY_ODS_LOAD package is a component of the Oracle Advanced Supply Chain Planning (ASCP) collection infrastructure, belonging to the MSC (Supply Chain Planning) application family. Its primary business function is the extraction, transformation, and loading of supply data from the operational source system into the Operational Data Store (ODS) that underpins the planning engine. In the standard ASCP collection cycle, source transaction data must be gathered, cleansed, mapped to planning entities, and staged before the plan can consume it. This package performs that supply-side loading responsibility, moving data from staging structures into the ODS supply tables where the memory-based planner can access it. It is classified as an "OTHER" API in the ETRM repository, indicating it is an internal collection utility rather than a public, externally callable interface. The package is documented as VALID under the APPS schema and forms part of the broader MSC_CL_COLLECTION dependency chain.

Key Procedures and Functions

The ETRM metadata documents seven procedures and functions within this package:

• IS_SUPPLIES_LOAD_DONE — a status-checking routine that reports whether the supply loading process has completed, allowing callers (such as the collection orchestrator) to synchronize dependent steps.
• CREATE_SUPPLIES_TMP_IND — creates temporary indexes to accelerate the supply load; such indexes are typically dropped after loading to avoid overhead on the base ODS tables.
• DROP_SUPPLIES_TMP_IND — the complement to the above, removing the temporary indexes once loading concludes.
• LOAD_SUPPLY — the central loader that populates supply records into the ODS supply structures.
• LOAD_STAGING_SUPPLY — loads supply data from the staging layer, serving as the first transformation tier.
• LOAD_ODS_SUPPLY — moves the staged supply records into the final ODS supply table.
• LOAD_PAYBACK_SUPPLIES — handles payback (consumption/supply offset) supply records, a specialized supply category in planning.

The procedure names reflect a layered ETL design: staging load, index management, ODS promotion, and status verification.

Tables Accessed

The package operates across the following documented tables (referenced via APPS synonyms):

• MSC_ST_SUPPLIES — the staging supply table, the source for transformation.
• MSC_SUPPLIES — the primary ODS supply table that the planner reads.
• MSC_APPS_INSTANCES — identifies the source application instance for multi-instance collection.
• MSC_DESIGNATORS — maps designation identifiers used during key resolution.
• MSC_ITEM_ID_LID — the item identifier logical ID mapping used to resolve item keys.
• MSC_TP_ID_LID and MSC_TP_SITE_ID_LID — trading partner and trading partner site logical ID mappings used to resolve supply source/destination keys.
• AD_DDL — utilized by the index creation and drop routines for DDL execution.
• DUAL — used for single-row utility queries.

Usage Notes

This package is an internal collection engine component and is not intended for direct external invocation. It is referenced by MSC_CL_COLLECTION, the master collection coordinator, which invokes supply loading as one phase of the overall ASCP data collection run. In practice, the package executes during scheduled collection concurrent programs (the Planning Data Collection process) rather than from a form. The temporary index routines and the completion-status function indicate it is designed for high-volume batch operation, with index management used to optimize bulk inserts and deletes. Developers extending collection logic should treat this package as a dependency-aware internal unit, ensuring that new code respects the staging-then-ODS sequence and does not bypass the temporary index lifecycle, which could degrade performance or leave orphaned indexes on the ODS tables.