Search Results msc_items

Overview

The MSC_ITEMS table is a core data repository within the Oracle E-Business Suite Advanced Supply Chain Planning (ASCP) module. Its primary role is to serve as the master reference for all inventory items that have been successfully registered with the planning server. This registration occurs during the data collection process, which extracts and transforms transactional data from source systems (like Oracle Inventory and Bills of Material) into the planning server's proprietary schema. The table acts as a critical control point, ensuring that only valid, collected items are available for consideration within supply chain planning runs, demand forecasting, and plan simulations.

Key Information Stored

The table's central purpose is to track unique inventory item identifiers. As indicated by its primary key, the most critical column is INVENTORY_ITEM_ID. This column stores the unique identifier for an item, which corresponds to the INVENTORY_ITEM_ID in the transactional schema (e.g., MTL_SYSTEM_ITEMS_B). While the provided metadata does not list additional columns, typical implementations of such a control table in the MSC schema would also include related foreign keys, such as ORGANIZATION_ID (to define the item-organization relationship), SRC_INSTANCE_ID (to identify the source system instance), and timestamps for collection. The presence of a primary key ensures data integrity and prevents duplicate item registration from the same source system.

Common Use Cases and Queries

The primary use case is to validate and report on the scope of data available to the planning engine. Planners and administrators frequently query this table to confirm that specific items have been collected and to troubleshoot data synchronization issues. A fundamental query involves joining to transactional tables to see the descriptive details of planned items. For example:

• Verify Collected Items: SELECT COUNT(*) FROM msc_items WHERE src_instance_id = 1; to check the volume of items collected from a specific source instance.
• List Items for Planning: SELECT mi.inventory_item_id, msib.segment1 item_code, msib.description FROM msc_items mi, mtl_system_items_b msib WHERE msib.inventory_item_id = mi.inventory_item_id AND msib.organization_id = mi.organization_id; This join retrieves the item code and description for all items registered in the planning server.

It is also commonly referenced in custom reports that analyze planning data coverage and in scripts that clean up or audit the MSC staging environment.

Related Objects

MSC_ITEMS is intrinsically linked to numerous other tables within the MSC planning schema. It serves as a parent table to detailed planning data tables that store item-specific supply, demand, and plan outputs. Key related objects include:

• Transactional Source Tables: MTL_SYSTEM_ITEMS_B (and its language-specific view MTL_SYSTEM_ITEMS_TL) is the ultimate source of the INVENTORY_ITEM_ID.
• Planning Data Tables: Tables such as MSC_SUPPLIES, MSC_DEMANDS, and MSC_PLAN_ORGANIZATIONS will typically use the INVENTORY_ITEM_ID (and often ORGANIZATION_ID) as foreign keys back to MSC_ITEMS.
• Collection Program: The data collection engine (e.g., the MSC_COLLECT package) is the primary process that populates this table.