Search Results msc_system_items

Overview

The MSC_SYSTEM_ITEMS table is a core data repository within the Oracle E-Business Suite Advanced Supply Chain Planning (ASCP) module, specifically for releases 12.1.1 and 12.2.2. It serves as the central planning engine's master item table, functioning as a snapshot and working copy of item data. Its primary role is to store item definitions sourced from transactional applications like Oracle Inventory and to maintain the specific item attributes and planning data used during a planning run. This separation from live transactional tables ensures the planning engine operates on a stable, versioned dataset, enabling consistent what-if analysis and scenario planning without impacting operational systems.

Key Information Stored

The table's structure mirrors the Oracle Inventory item master, extended with planning-specific attributes. Its primary key uniquely identifies a planned item through a composite of SR_INSTANCE_ID (source system instance), PLAN_ID, INVENTORY_ITEM_ID, and ORGANIZATION_ID. Key columns encompass a wide range of item data, including item codes, descriptions, planning methods (MPS, MRP, DRP), make/buy flags, lead times, shrinkage rates, and ABC classification codes. The table also holds critical planning parameters such as fixed order quantities, safety stock levels, and planning exception set identifiers, which directly influence the output of the planning engine.

Common Use Cases and Queries

A primary use case is analyzing the item master data upon which a specific plan was calculated. This is essential for troubleshooting plan output, validating that the correct planning parameters were loaded, and performing item-centric analytics. Common queries involve joining to planning output tables to assess coverage or diagnose exceptions. For example, to list all planned items with their associated demands within a specific plan, a query would join MSC_SYSTEM_ITEMS to MSC_DEMANDS. Another frequent pattern is querying for items with specific planning attributes, such as identifying all MPS-planned items or items with a particular sourcing rule.

• Identifying items planned in a specific scenario: SELECT msi.item_name, msi.planning_make_buy_code FROM msc_system_items msi WHERE msi.plan_id = :p_plan_id;
• Joining to demand for coverage analysis: SELECT msi.item_name, SUM(md.using_requirement_quantity) FROM msc_system_items msi, msc_demands md WHERE msi.plan_id = md.plan_id AND msi.inventory_item_id = md.inventory_item_id GROUP BY msi.item_name;

Related Objects

MSC_SYSTEM_ITEMS maintains extensive foreign key relationships with core ASCP tables, underscoring its central role. As per the provided metadata, it is referenced by numerous planning transaction and definition tables. Key documented relationships include:

• MSC_PLAN_ORGANIZATIONS: Validates that items belong to organizations included in the plan (joined on PLAN_ID, ORGANIZATION_ID, SR_INSTANCE_ID).
• MSC_AVAILABLE_TO_PROMISE, MSC_DEMANDS: Links item master data to demand and ATP transactions (joined on INVENTORY_ITEM_ID, PLAN_ID, etc.).
• MSC_BOMS and MSC_BOM_COMPONENTS: Relates items to their bill of material structures, both as assemblies and components.
• MSC_EXCEPTION_DETAILS and MSC_FORECAST_UPDATES: Associates planning exceptions and forecast consumption records with the specific item instance.
• MSC_ABC_CLASSES: References the ABC_CLASS column for classification data.