Results for “mrp_material_plans”

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

Overview

MRP_MATERIAL_PLANS is a table in the MRP schema of Oracle E-Business Suite, owned by the Master Scheduling/MRP product module. It stores the horizontal plan for planned items, presenting time-phased supply and demand information across defined planning buckets. In Oracle EBS 12.1.1 and 12.2.2 the table is a core repository for the material plan output generated by the MRP, MPS, and DRP planning engines, and is the primary source for the Horizontal Plan window and related plan review reports.

Heuristic Data Vault classification mined from the foreign key structure indicates the table is satellite-leaning. In Data Vault modeling terms, this suggests the object behaves as a descriptive satellite attached to a business key, rather than serving as a hub of unique business entities or a link resolving many-to-many relationships. The business key is composite, spanning plan, organization, compile designator, item, bucket type, and horizontal plan type, while the descriptive payload consists largely of periodic quantity columns.

Key Information Stored

MRP_MATERIAL_PLANS contains 50 documented columns. The most significant are the composite business key and the time-phased quantity measures:

• PLAN_ID — identifies the plan to which the horizontal plan rows belong; the leading column of the unique index.
• ORGANIZATION_ID — the organization that owns the planned item.
• COMPILE_DESIGNATOR — distinguishes plan compilation runs, enabling historical comparisons.
• INVENTORY_ITEM_ID — foreign key to MRP_SYSTEM_ITEMS, identifying the planned item.
• ITEM_SEGMENTS — concatenated item flexfield segments for reporting convenience.
• BUCKET_TYPE — the planning bucket granularity, such as days or weeks.
• HORIZONTAL_PLAN_TYPE — the type of horizontal plan row (for example, supply, demand, or projected available balance). HORIZONTAL_PLAN_TYPE_TEXT provides the displayed description.
• PLAN_ORGANIZATION_ID and ORGANIZATION_CODE — the owning plan organization and its code.
• QUANTITY1 through QUANTITY36 — the periodic quantity values populated according to the bucket type, representing the time-phased plan across up to 36 periods.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY — standard audit columns.

The surrogate primary key is not separately documented; the business-key candidate is the unique index MRP_MATERIAL_PLANS_U1 on (PLAN_ID, ORGANIZATION_ID, COMPILE_DESIGNATOR, INVENTORY_ITEM_ID, BUCKET_TYPE, HORIZONTAL_PLAN_TYPE).

Common Use Cases and Queries

Planners and developers query this table to reproduce or extend the seeded Horizontal Plan display, to publish time-phased supply/demand extracts to reporting tools, and to compare successive plan compilations. A typical pattern pivots the QUANTITY columns into period buckets for a given plan, organization, and item.

• Retrieving all horizontal plan rows for one item and plan, filtered by COMPILE_DESIGNATOR to isolate a specific run.
• Aggregating QUANTITY1 through QUANTITY36 across HORIZONTAL_PLAN_TYPE to reconcile total supply against total demand per bucket.
• Joining to MRP_SYSTEM_ITEMS on INVENTORY_ITEM_ID to enrich output with item attributes.
• Feeding downstream BI dashboards that present projected available balance by bucket type.

A representative query selects PLAN_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID, HORIZONTAL_PLAN_TYPE, BUCKET_TYPE, QUANTITY1, QUANTITY2 from MRP_MATERIAL_PLANS where PLAN_ID = :plan and ORGANIZATION_ID = :org and COMPILE_DESIGNATOR = :compile, ordered by INVENTORY_ITEM_ID and HORIZONTAL_PLAN_TYPE.

Related Objects

The documented foreign key links MRP_MATERIAL_PLANS to MRP_SYSTEM_ITEMS on INVENTORY_ITEM_ID, making the item master the principal related object. Additional significant objects that reference or depend on this data include the plan definition and compilation tables keyed by PLAN_ID and COMPILE_DESIGNATOR, MRP_ORGANIZATION_PARAMETERS for organization context, and the standard concurrent programs and reports that populate and present the horizontal plan. Join conditions should always include ORGANIZATION_ID alongside INVENTORY_ITEM_ID to respect the composite key, since item identifiers are organization-scoped in MRP.