Search Results msc_category_sets_s

Overview

APPS.MSC_CL_ITEM_ODS_LOAD is a PL/SQL package body in the Oracle E-Business Suite Advanced Supply Chain Planning (ASCP) schema. Its name reflects its role as a collection-loader component that populates the Operational Data Store (ODS) staging and interface tables used by the MSC (Materials/Supply Chain) planning engine. The package moves item, category, supplier, substitute, and capacity data from source collection staging tables into the planning application tables, ensuring that the planning server has a consistent and current view of the master data required for plan generation.

The package is classified as OTHER in the documented API classification, meaning it is intended for internal use by the collections and planning data-management framework rather than as a public integration API. It is a dependent object that is not referenced by any other database object, confirming its position as a leaf-level loader invoked by higher-level collection processes.

Key Procedures and Functions

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

• GENERATE_ITEM_KEYS — Generates or derives surrogate item identifiers used to key ODS records consistently across the load.
• ITEM_NAME — Returns or resolves the display name of an item, supporting naming consistency during load operations.
• ADD_NEW_IMPL_ITEM_ASL — Adds newly implemented items to the Approved Supplier List (ASL) as part of the item load.
• UPDATE_LEADTIME — Updates lead-time attributes associated with items or suppliers.
• LOAD_ITEM — Loads item master records into the planning tables.
• LOAD_SUPPLIER_CAPACITY — Loads supplier capacity data used by the planning engine for sourcing and capacity constraints.
• LOAD_ABC_CLASSES — Loads ABC classification data used for item segmentation and planning prioritization.
• LOAD_ITEM_SUBSTITUTES — Loads item substitution relationships that permit alternate-item planning.
• LOAD_CATEGORY — Loads category and category set assignments for items.

Tables Accessed

The package operates across both staging (MSC_ST_*) and application-level (MSC_*) tables, using APPS synonyms. Staging sources include MSC_ST_CATEGORY_SETS, MSC_ST_ITEM_CATEGORIES, MSC_ST_ITEM_SUBSTITUTES, MSC_ST_ITEM_SUPPLIERS, MSC_ST_SUPPLIER_CAPACITIES, MSC_ST_SUPPLIER_FLEX_FENCES, and MSC_ST_SYSTEM_ITEMS. Target and lookup tables include MSC_ITEMS, MSC_ITEMS_S, MSC_ITEM_CATEGORIES, MSC_ITEM_SUBSTITUTES, MSC_ITEM_SUPPLIERS, MSC_ROUTINGS, MSC_CATEGORY_SETS, MSC_CATEGORY_SETS_S, MSC_SUPPLIER_CAPACITIES, MSC_SUPPLIER_CAPACITIES_S, MSC_SUPPLIER_FLEX_FENCES, MSC_SYSTEM_ITEMS, MSC_TRADING_PARTNERS, and MSC_UNITS_OF_MEASURE. Identifier columns are supplied via MSC_ITEM_ID_LID, MSC_CATEGORY_SET_ID_LID, MSC_TP_ID_LID, and MSC_TP_SITE_ID_LID. Utility routines such as FND_GLOBAL, FND_MESSAGE, FND_PROFILE, MSC_UTIL, MSC_GET_NAME, and MSC_ANALYSE_TABLES_PK provide context, messaging, profile access, and statistics management. The _S suffixed tables are the language/translation views, and DBA_TAB_STATISTICS is consulted for optimizer statistics.

Usage Notes

MSC_CL_ITEM_ODS_LOAD is typically invoked by the MSC collections framework during the data-load phase that precedes a planning run. It is one of eight packages depending on it, indicating it is called from higher-level collection orchestrators rather than directly by end users. The naming conventions CL (collection loader) and ODS (Operational Data Store) confirm that this package belongs to the collections/ODS staging subsystem rather than the core planning engine.

Because the package is classified as OTHER and is not referenced by other database objects, it should not be treated as a supported public API. Custom code should not invoke its procedures directly; instead, planners should trigger the standard collections concurrent programs that manage the item and supplier master data flow into the planning ODS. Direct invocation risks desynchronizing the staging and application tables and bypassing the referential checks performed by the collections framework.