Results for “mrp_system_items”

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

Overview

MRP_SYSTEM_ITEMS is the item master table of the Oracle Master Scheduling/MRP (MRP) module in Oracle E-Business Suite 12.1.1 and 12.2.2. It is owned by the MRP schema and stores the planning-relevant snapshot of every item that participates in a planning run. Whereas MTL_SYSTEM_ITEMS_B holds the transactional, organization-level definition of an item, MRP_SYSTEM_ITEMS carries the derived and planning-specific attributes that the material requirements planning engine consumes during a plan compilation: lead times, planning codes, safety stock parameters, order modifiers, exception thresholds, and sourcing attributes. Rows are scoped to a plan through the COMPILE_DESIGNATOR column, so the same inventory item may appear many times — once per plan — with plan-specific values.

The table is defined with 84 columns in the documented physical schema and is classified heuristically as hub-leaning in the Data Vault sense. This suggests modeling MRP_SYSTEM_ITEMS primarily as a hub keyed by the item/organization/plan combination, since it serves as the stable reference point from which a very large constellation of MRP planning facts (requirements, recommendations, schedules, exceptions) hang.

Key Information Stored

The primary key MRP_SYSTEM_ITEMS_PK comprises three columns: INVENTORY_ITEM_ID, ORGANIZATION_ID, and COMPILE_DESIGNATOR. The unique index MRP_SYSTEM_ITEMS_U1 (ORGANIZATION_ID, COMPILE_DESIGNATOR, INVENTORY_ITEM_ID) confirms this triad as the business-key candidate. INVENTORY_ITEM_ID and ORGANIZATION_ID are surrogate references inherited from the inventory item definition; COMPILE_DESIGNATOR identifies the plan instance.

• MRP_PLANNING_CODE and INVENTORY_PLANNING_CODE — determine whether and how the item is planned and netted.
• PLANNING_MAKE_BUY_CODE, WIP_SUPPLY_TYPE, and REPETITIVE_TYPE — drive supply generation logic.
• FULL_LEAD_TIME, PREPROCESSING_LEAD_TIME, POSTPROCESSING_LEAD_TIME, and CUMULATIVE_TOTAL_LEAD_TIME — the lead-time values used in scheduling.
• SAFETY_STOCK_CODE, SAFETY_STOCK_PERCENT, SAFETY_STOCK_QUANTITY, and SAFETY_STOCK_BUCKET_DAYS — safety stock policy.
• FIXED_ORDER_QUANTITY, FIXED_LOT_MULTIPLIER, MINIMUM_ORDER_QUANTITY, and MAXIMUM_ORDER_QUANTITY — order modifiers applied to planned orders.
• PLANNING_TIME_FENCE_DAYS, DEMAND_TIME_FENCE_DAYS, and INVENTORY_USE_UP_DATE — time-fence controls.
• PLANNING_EXCEPTION_SET, EXCEPTION_SHORTAGE_DAYS, EXCEPTION_EXCESS_DAYS, and EXCEPTION_CODE — exception configuration and results.
• CATEGORY_ID, ABC_CLASS, BUYER_ID, PLANNER_CODE, and REVISION — descriptive and sourcing attributes mirrored from inventory and purchasing.
• BASE_ITEM_ID — links an item to its base (template/master) item, supporting the self-referencing hierarchy.

Common Use Cases and Queries

MRP_SYSTEM_ITEMS is the anchor for essentially every planning report: the Planner Workbench, exception messages, material plans, and supply/demand pegging queries all join back to it. A typical pattern retrieves plan-scoped item attributes for a given plan:

SELECT msi.inventory_item_id, msi.organization_id, msi.description,  msi.planner_code, msi.mrp_planning_code, msi.full_lead_time, msi.safety_stock_quantity FROM mrp.mrp_system_items msi WHERE msi.compile_designator = :p_plan AND msi.organization_id = :p_org AND msi.mrp_planning_code IN (1,2);

Planners use these rows to review order modifiers and time fences before releasing planned orders, to identify items with aggressive safety stock, or to audit which items were excluded from netting. Because MRP_SYSTEM_ITEMS is repopulated each time a plan is compiled, queries for historical comparison must reference the plan designator rather than assuming current values.

Related Objects

MRP_SYSTEM_ITEMS sits at the center of a dense relationship graph. It references MTL_SYSTEM_ITEMS_B (INVENTORY_ITEM_ID, ORGANIZATION_ID), MTL_CATEGORIES_B (CATEGORY_ID), MTL_ABC_CLASSES (ABC_CLASS), MTL_ITEM_REVISIONS_B (INVENTORY_ITEM_ID, ORGANIZATION_ID, REVISION), PO_AGENTS (BUYER_ID), and MRP_PLANNING_EXCEPTION_SETS (PLANNING_EXCEPTION_SET); it also self-references through BASE_ITEM_ID. Numerous planning fact tables depend on it, including MRP_MATERIAL_PLANS, MRP_GROSS_REQUIREMENTS, MRP_RECOMMENDATIONS, MRP_ITEM_EXCEPTIONS, MRP_ONHAND_QUANTITIES, MRP_BOM_COMPONENTS, MRP_SAFETY_STOCK, and MRP_WORKBENCH_QUERY, each joined on the inventory item, organization, and compile designator. This makes MRP_SYSTEM_ITEMS the natural hub for planning-star reporting in the MRP schema.