Search Results mrp_material_plans_u1

Overview

MRP.MRP_MATERIAL_PLANS is a transaction data table in the Oracle E-Business Suite 12.1.1 and 12.2.2 environments that stores the horizontal material plan generated for an MRP, DRP, or MPS plan. The horizontal plan represents the time-phased view of supply and demand that planners review in the Planner Workbench. Rows are not created for every planning run on a continuous basis; rather, the horizontal plan data is generated and persisted in this table whenever a user navigates to the horizontal plan window of the Planner Workbench. This behavior makes the table a materialized snapshot of planning output rather than a source planning table.

Because the table stores denormalized, time-bucketed plan quantities keyed to a specific plan, organization, item, bucket type, and horizontal plan type, the heuristic Data Vault classification is satellite-leaning. In a Data Vault model this object would most naturally be represented as a satellite hanging off a hub or link representing the plan/item/organization combination, since it carries descriptive, changing plan values rather than defining new business entities.

Key Information Stored

The table is 50 columns wide, of which the majority are generic time-bucket quantity columns. The most significant columns are:

• PLAN_ID — Query identifier for the plan; a leading component of the unique index.
• ORGANIZATION_ID — Organization identifier for which the plan row applies.
• COMPILE_DESIGNATOR — Identifies the plan as MPS, MRP, or DRP.
• PLAN_ORGANIZATION_ID — The organization in which the plan was defined, which may differ from the executing organization.
• ORGANIZATION_CODE — Human-readable organization code.
• INVENTORY_ITEM_ID — Inventory item identifier; also the foreign key to MRP_SYSTEM_ITEMS.
• ITEM_SEGMENTS — Concatenated item segments for display and reporting without joining item master tables.
• HORIZONTAL_PLAN_TYPE — Numeric code denoting the supply or demand category (for example, planned orders or discrete jobs).
• HORIZONTAL_PLAN_TYPE_TEXT — Textual rendering of the horizontal plan type for reporting.
• BUCKET_TYPE — Numeric code indicating the unit of time used to store the plan, such as days, weeks, or periods.
• QUANTITY1 through QUANTITY36 — The generic quantity columns that hold the actual time-phased horizontal plan values for each bucket.
• Standard Who columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, and CREATED_BY.

The documented unique index MRP_MATERIAL_PLANS_U1 covers PLAN_ID, ORGANIZATION_ID, COMPILE_DESIGNATOR, INVENTORY_ITEM_ID, BUCKET_TYPE, and HORIZONTAL_PLAN_TYPE. This composite key is the effective business-key candidate; the table does not expose a single-column surrogate primary key in the documented metadata.

Common Use Cases and Queries

Typical usage centers on extracting time-phased supply and demand for reporting, reconciliation, and planner analytics. A representative query joins the horizontal plan rows to the system items entity and filters by plan and bucket:

• Retrieving all quantity buckets for a given item in a plan: SELECT bucket_type, horizontal_plan_type, quantity1, quantity2 FROM mrp.mrp_material_plans WHERE plan_id = :p AND organization_id = :o AND inventory_item_id = :i;
• Aggregating supply versus demand by plan type using GROUP BY on HORIZONTAL_PLAN_TYPE and BUCKET_TYPE.
• Reporting via MRP_SYSTEM_ITEMS joined on INVENTORY_ITEM_ID to obtain item descriptions not stored in ITEM_SEGMENTS.
• Comparing successive snapshots of QUANTITY1..QUANTITY36 to identify plan changes between Planner Workbench visits, since rows are refreshed each time the horizontal plan window is opened.

Because ITEM_SEGMENTS and ORGANIZATION_CODE are stored locally, many reporting queries can run without joins to the item or organization masters.

Related Objects

The documented foreign key relationship ties this table to the system items entity. Significant related objects include:

• MRP.MRP_SYSTEM_ITEMS — referenced by INVENTORY_ITEM_ID; supplies item-level attributes for reporting.
• MRP.MRP_PLANS — defines the plan identified by PLAN_ID and COMPILE_DESIGNATOR.
• MRP.MRP_FORECAST and forecast designators — provide demand context that feeds plan quantities.
• MRP.MRP_SCHEDULE_DESIGNATORS — identify plans by designator and organization.
• MRP.MRP_GROSS_REQUIREMENTS and MRP.MRP_RECOMMENDATIONS — underlying planning detail that is summarized into the horizontal plan.
• ORG_ORGANIZATION_DEFINITIONS — resolves ORGANIZATION_ID to organization names for reporting.

These relationships make MRP_MATERIAL_PLANS a reporting-oriented satellite that consolidates planning output for the Planner Workbench and analytical extracts.