Results for “msc_system_items_u3”

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

Overview

MSC.MSC_SYSTEM_ITEMS is the central item master staging and planning table within the Oracle Advanced Supply Chain Planning (ASCP) schema. In Oracle EBS 12.1.1 and 12.2.2 it stores organization items defined at source applications, and it also retains a snapshot copy of the items planned by the planning process. Most columns mirror attributes from the Oracle Inventory item master, MTL_SYSTEM_ITEMS, and the table is populated by the Collection and Snapshot programs and consumed by the Planner. Because the table carries both source-instance identity (SR_INSTANCE_ID) and plan identity (PLAN_ID), it is a plan-scoped replica of the item master rather than a transactional ledger.

[Modeling suggestion] Under a heuristic Data Vault classification the table behaves as a hub: it is the master reference point keyed by item and organization, and the majority of MSC planning tables (demands, supplies, BOMs, routings, sourcing) depend on it through foreign keys.

Key Information Stored

The physical surrogate key is MSC_SYSTEM_ITEMS_PK, composed of SR_INSTANCE_ID, PLAN_ID, INVENTORY_ITEM_ID, and ORGANIZATION_ID. Three unique indexes act as business-key candidates and reveal the alternate identities for a single planned item: MSC_SYSTEM_ITEMS_U1 (PLAN_ID, SR_INSTANCE_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID, NEW_PLAN_ID, SIMULATION_SET_ID) is the primary item identity; MSC_SYSTEM_ITEMS_U2 (PLAN_ID, SR_INSTANCE_ID, ORGANIZATION_ID, ITEM_NAME, NEW_PLAN_ID, SIMULATION_SET_ID) enforces unique item name within a plan-organization, which is the index referenced when users search for "msc_system_items_u2"; MSC_SYSTEM_ITEMS_U3 keys on the source-side identifier SR_INVENTORY_ITEM_ID.

• PLAN_ID / ORGANIZATION_ID / SR_INSTANCE_ID — the plan, inventory organization, and source application instance that scope each item row.
• INVENTORY_ITEM_ID / SR_INVENTORY_ITEM_ID / ITEM_NAME — internal item identifier, source-system identifier, and the 250-character item name.
• DESCRIPTION, UOM_CODE, PRODUCT_FAMILY_ID — descriptive and grouping attributes carried from the item master.
• PLANNER_CODE, BUYER_NAME / BUYER_ID, ABC_CLASS / ABC_CLASS_NAME, CATEGORY_NAME — ownership, sourcing, and classification attributes used for filtering and reporting.
• MRP_PLANNING_CODE, PLANNING_MAKE_BUY_CODE, WIP_SUPPLY_TYPE, BOM_ITEM_TYPE, SOURCE_TYPE — planning behavior drivers that determine how MRP treats the item.
• FIXED_LEAD_TIME, VARIABLE_LEAD_TIME, FULL_LEAD_TIME, CUMULATIVE_TOTAL_LEAD_TIME — lead time attributes used by the planning engine.
• SAFETY_STOCK_CODE, FIXED_SAFETY_STOCK_QTY, MINIMUM_ORDER_QUANTITY, FIXED_ORDER_QUANTITY, FIXED_LOT_MULTIPLIER, ROUNDING_CONTROL_TYPE — lot sizing and safety stock policy.
• MAKE_BUY / PLANNING status flags including PIP_FLAG, REPETITIVE_PLANNING_FLAG, SERVICE_LEVEL — planning method attributes.
• STANDARD_COST, MATERIAL_COST, RESOURCE_COST, ORDER_COST, CARRYING_COST — cost rollups consumed by plan comparisons.
• NEW_PLAN_ID, SIMULATION_SET_ID, REFRESH_NUMBER — simulation and refresh control columns that participate in the unique indexes.
• LAST_UPDATE_DATE, CREATION_DATE, LAST_UPDATED_BY, CREATED_BY, REQUEST_ID, PROGRAM_ID — standard concurrent-program audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the DFF flex columns for customer-defined item planning attributes.

Common Use Cases and Queries

The table is most frequently queried to resolve an item name to its internal identifier within a plan, and to join plan-level attributes onto demand, supply, and exception data. A typical lookup uses the U2 index pattern:

• Resolve item by name: SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID FROM MSC.MSC_SYSTEM_ITEMS WHERE PLAN_ID = :plan AND SR_INSTANCE_ID = :sr AND ORGANIZATION_ID = :org AND ITEM_NAME = :name.
• Item attribute review: filter by PLANNER_CODE and ORGANIZATION_ID using MSC_SYSTEM_ITEMS_N1 to produce a planner workbench item list.
• Exception reporting: join MSC_ITEM_EXCEPTIONS or MSC_EXCEPTION_DETAILS on INVENTORY_ITEM_ID, PLAN_ID, and ORGANIZATION_ID to report exceptions by ABC class or product family, using MSC_SYSTEM_ITEMS_N3 for PRODUCT_FAMILY_ID filtering.
• Supply type analysis: use MSC_SYSTEM_ITEMS_N6 (PLAN_ID, SR_INSTANCE_ID, ORGANIZATION_ID, WIP_SUPPLY_TYPE) to group planned items by supply type.
• Case-insensitive name search: the function-based index MSC_SYSTEM_ITEMS_N5 on UPPER("ITEM_NAME") supports queries such as WHERE UPPER(ITEM_NAME) LIKE UPPER(:pattern).
• Cost and lot-size reconciliation: compare FIXED_ORDER_QUANTITY, FIXED_LOT_MULTIPLIER, and STANDARD_COST against source MTL_SYSTEM_ITEMS values to validate collection completeness.

Related Objects

MSC_SYSTEM_ITEMS is a hub referenced throughout the MSC schema. The following are among the most significant dependents and dependencies, joined on the item, plan, organization, and source instance columns.

• MSC_BOMS.ASSEMBLY_ITEM_ID and MSC_BOM_COMPONENTS.INVENTORY_ITEM_ID — assembly and component references used in bill explosion.
• MSC_DEMANDS.INVENTORY_ITEM_ID and MSC_DEMANDS.USING_ASSEMBLY_ITEM_ID — independent and dependent demand records.
• MSC_SUPPLIES.INVENTORY_ITEM_ID — planned supply records for the item.
• MSC_ROUTINGS.ASSEMBLY_ITEM_ID and MSC_BOR_REQUIREMENTS.ASSEMBLY_ITEM_ID — routing and resource requirement links.
• MSC_ITEM_SOURCING.INVENTORY_ITEM_ID and MSC_ITEM_SUPPLIERS.INVENTORY_ITEM_ID — sourcing rules and approved supplier data.
• MSC_SAFETY_STOCKS.INVENTORY_ITEM_ID and MSC_RESERVATIONS.INVENTORY_ITEM_ID — safety stock and reservation references.
• MSC_FORECAST_UPDATES.INVENTORY_ITEM_ID, MSC_AVAILABLE_TO_PROMISE.INVENTORY_ITEM_ID, and MSC_FULL_PEGGING.INVENTORY_ITEM_ID — forecast, ATP, and pegging relationships.
• MSC_PLAN_ORGANIZATIONS (referenced via PLAN_ID) and MSC_ABC_CLASSES (referenced via ABC_CLASS) — the two outbound foreign keys that tie an item row to plan-organization and ABC classification definitions.
• MSC_COMPONENT_SUBSTITUTES.SUBSTITUTE_ITEM_ID and MSC_SEQUENCE_DEPENDENT_SETUPS.FROM_ITEM / TO_ITEM — substitution and setup references used in detailed scheduling.