Results for “msc_system_items”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MSC_SYSTEM_ITEMS is the central item master staging table within the MSC (Advanced Supply Chain Planning) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores organization-level item definitions collected from source applications such as Oracle Inventory, Purchasing, and Bills of Material, and it retains a working copy of the items consumed by the planning engine during a planning run. Most columns mirror attributes from the Oracle Inventory item master, which allows the planning process to evaluate planning attributes independently of the transactional source tables without repeated joins back to the operational schemas.

From a Data Vault modeling perspective, MSC_SYSTEM_ITEMS suggests a hub classification. Its primary key and unique indexes are composed of the natural business keys of the item across instance, plan, organization, and item, which is characteristic of a hub entity anchoring item-level relationships. Tables that reference it supply links or satellites through inventory_item_id, plan_id, sr_instance_id, and organization_id.

Key Information Stored

The documented physical schema contains 242 columns. The most operationally significant include:

• SR_INSTANCE_ID, PLAN_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID — the composite surrogate/candidate primary key MSC_SYSTEM_ITEMS_PK, which uniquely identifies a planned item within a specific plan and organization on a given source instance.
• ITEM_NAME — the item identifier used in user-facing queries and reports.
• SR_INVENTORY_ITEM_ID — the source instance item reference used by alternate unique index MSC_SYSTEM_ITEMS_U2.
• NEW_PLAN_ID, NEW_PLAN_LIST, SIMULATION_SET_ID — composite columns appearing in unique indexes MSC_SYSTEM_ITEMS_U1, U2, and U3, supporting plan simulation and copy scenarios.
• REFRESH_NUMBER, LAST_REPLAN_DATE, NETCHANGE_REPLAN_FLAG, REPLAN_PERIOD — track planning refresh and replan state.
• PLANNING_MAKE_BUY_CODE, MRP_PLANNING_CODE, PLANNING_EXCEPTION_SET — drive make/buy planning behavior and exception handling.
• FIXED_LEAD_TIME, VARIABLE_LEAD_TIME, FULL_LEAD_TIME, CUMULATIVE_TOTAL_LEAD_TIME — lead time attributes used in scheduling and time-phased planning.
• SAFETY_STOCK_CODE, SAFETY_STOCK_PERCENT, FIXED_SAFETY_STOCK_QTY — safety stock policies applied during planning.
• ABC_CLASS, ABC_CLASS_NAME, CATEGORY_NAME — classification values joined to MSC_ABC_CLASSES for reporting and analysis.
• STANDARD_COST, ORDER_COST, CARRYING_COST, MATERIAL_COST, RESOURCE_COST — cost attributes supporting cost-based planning and sourcing decisions.
• LOW_LEVEL_CODE — sequences dependent demand processing during the MRP explosion.

Common Use Cases and Queries

Typical usage centers on verifying whether an item was collected into a plan, diagnosing missing item attributes, and reporting on planned item attributes. A representative query joins MRC_SYSTEM_ITEMS to MSC_PLAN_ORGANIZATIONS to validate the plan and organization context:

• SELECT msi.ITEM_NAME, msi.PLAN_ID, msi.ORGANIZATION_ID, msi.PLANNING_MAKE_BUY_CODE FROM MSC.MSC_SYSTEM_ITEMS msi WHERE msi.PLAN_ID = :plan_id AND msi.ORGANIZATION_ID = :org_id AND msi.SR_INSTANCE_ID = :sr_instance_id;
• SELECT msi.ITEM_NAME, msi.REFRESH_NUMBER, msi.LAST_REPLAN_DATE FROM MSC.MSC_SYSTEM_ITEMS msi WHERE msi.INVENTORY_ITEM_ID = :item_id AND msi.PLAN_ID = :plan_id;
• Joins to MSC_ITEM_EXCEPTIONS and MSC_EXCEPTION_DETAILS to return items with planning exceptions.
• Joins to MSC_SAFETY_STOCKS and MSC_ITEM_SOURCING to analyze safety stock and sourcing rules per item.

Related Objects

The foreign key metadata identifies the strongest dependencies and dependents:

• MSC_PLAN_ORGANIZATIONS — referenced via PLAN_ID and ORGANIZATION_ID and SR_INSTANCE_ID, defining the plan/organization context.
• MSC_ABC_CLASSES — referenced via ABC_CLASS and SR_INSTANCE_ID for classification lookups.
• MSC_BOMS and MSC_BOM_COMPONENTS — reference inventory_item_id and assembly_item_id, linking item definitions to planning bills.
• MSC_DEMANDS — references inventory_item_id and using_assembly_item_id, tying demand records to planned items.
• MSC_SUPPLIES — references inventory_item_id, linking supply records to the item master.
• MSC_ITEM_EXCEPTIONS and MSC_EXCEPTION_DETAILS — reference inventory_item_id, storing item-level exception messages.
• MSC_ITEM_SOURCING and MSC_ITEM_SUPPLIERS — reference inventory_item_id, holding sourcing and approved supplier data.
• MSC_SAFETY_STOCKS and MSC_AVAILABLE_TO_PROMISE — reference inventory_item_id, plan_id, sr_instance_id, and organization_id for ATP and safety stock analysis.
• MSC_FULL_PEGGING — references inventory_item_id, supporting full pegging reports.

These relationships confirm MSC_SYSTEM_ITEMS as the hub through which most planning data objects are expressed.