Search Results alter_temp_table

Overview

MSC_CL_COLLECTION is the core collection-engine package in the Oracle Advanced Supply Chain Planning (ASCP) schema (MSC). Its primary business function is to orchestrate the collection of source data from transactional and planning applications into the ASCP planning staging and interface tables. Collection is the process that extracts, transforms, and loads supply chain data — items, bills of material, sourcing rules, organizations, trading partners, on-hand balances, and supplies — so that the planning engine can generate plans. In Oracle EBS 12.1.1 and 12.2.2, this package controls collection run lifecycle management, parallel worker dispatch, monitoring of concurrent collection activity, and staging-table maintenance. The specification header (MSCCLBAS.pls 120.14) and the embedded profile references (INV_CTP, MRP_DEBUG, MSC_INDEX_PARALLEL_THREADS, MSC_SRP_ENABLED, MSC_X_CONFIGURATION) confirm its role as a scheduling and configuration-aware driver. It exposes 21 documented procedures and functions and is referenced by 19 other packages, making it a central hub in the MSC dependency chain.

Key Procedures and Functions

• LAUNCH_WORKER — Dispatches an individual collection worker process for a given task.
• LAUNCH_MONITOR — Initiates the monitor process that tracks collection progress.
• LAUNCH_MONITOR_CONT — Continuation entry point for the monitor; the object named in the user's search. It resumes or continues monitoring for an in-flight collection run.
• LAUNCH_MONITOR_DET_SCH — Launches the monitor with a detailed scheduling scope.
• LAUNCH_MON_PARTIAL — Launches monitoring for partial (subset) collection runs.
• Q_PARTIAL_TASK — Queues a partial collection task.
• DELETE_PROCESS — Removes a collection process record once complete or aborted.
• ALTER_TEMP_TABLE — Alters temporary staging tables, typically to add or adjust columns before load.
• TRUNCATE_MSC_TABLE — Empties an MSC table prior to a fresh load.
• DELETE_MSC_TABLE — Deletes rows from an MSC table, often for targeted refreshes.
• PURGE_STAGING_TABLES and PURGE_STAGING_TABLES_SUB — Purge obsolete staging data; the SUB variant performs the subordinate detailed cleanup.
• GENERATE_ITEM_KEYS — Creates item surrogate keys used across MSC tables.
• GENERATE_TRADING_PARTNER_KEYS — Creates trading partner surrogate keys.
• ENTER_MODELLED_INFO — Inserts modelled planning information into staging tables.
• LOG_MESSAGE — Writes diagnostic or error messages to the MSC error/log tables.
• IS_MSCTBL_PARTITIONED — Returns whether an MSC table is partitioned, guiding load strategy.
• INITIALIZE — Performs base package initialization.
• INITIALIZE_LOAD_GLOBALS — Sets global variables governing a load (refresh mode, instance, exchange mode).
• COMPUTE_RES_AVAIL — Computes resource availability for planning.

Tables Accessed

• MSC_COLLECTION_S — Master collection run records, read and updated to track status.
• MSC_COLL_PARAMETERS — Collection parameters defining scope and refresh mode.
• MSC_APPS_INSTANCES — Source instance registry used to resolve the collecting application.
• MSC_COMPANIES / MSC_TRADING_PARTNERS — Company and partner data populated during collection.
• MSC_SOURCING_HISTORY / MSC_PLANS — Sourcing and plan definitions read by the engine.
• MSC_ERRORS_S — Error records written by LOG_MESSAGE.
• FND_LOOKUP_VALUES, FND_ORACLE_USERID, FND_PRODUCT_INSTALLATIONS — Foundation setup data for validation and mode resolution.
• AD_DDL, ALL_SOURCE, ALL_TABLES — Data dictionary access for DDL and partition inspection.
• DBMS_LOCK — Serialization of concurrent collection operations.

Usage Notes

MSC_CL_COLLECTION is invoked primarily from concurrent programs launched through the ASCP collection manager, and indirectly from forms and scheduled planning processes. Custom code should call the public procedures rather than manipulate staging tables directly. The LAUNCH_MONITOR_CONT path is typically used when a collection is resumed or when a prior monitor call must continue without reinitializing globals — an important distinction when debugging stalled or partially completed collections in 12.1.1 and 12.2.2 environments.