MRP Horizontal Plan – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Master Scheduling/MRP · all Master Scheduling/MRP reports
Overview
MRP: Horizontal Plan from the Planners Workbench.
- The report can be run for multiple organizations and items within a selected plan
- The report can be used to explode BOMS and display the HP for all components within the BOM
- Select a template to choose to either display the HP by individual components, or by End Assemblies, Components.
Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.
Report Parameters
Plan, Current or Snapshot Data, Buckets, Organization Code, Category Set, Category, Item Type, Exclude Item Type, Item, Item From, Item To, Item Contains, Explode BOMs, Exclude Items with No Activity, Make or Buy, Planner, Buyer, Supply/Demand Source
Oracle EBS Tables Used
mrp_material_plans, mrp_workbench_bucket_dates, mrp_system_items, ap_suppliers, per_people_x, mtl_system_items_vl, mtl_item_categories, mtl_categories_kfv, mtl_planners, bom_small_expl_temp, mrp_end_assemblies
Report Categories
Related Reports
INV Items, MRP Pegging, INV Item Upload 11i, INV Item Upload, MRP Plan Orders, PO Headers and Lines, AP Invoices and Lines, WIP Discrete Job Shortage, WIP Required Components
Running This SQL Without Blitz Report
The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.
Download & Import Options
| Resource | Link |
|---|---|
| Excel Example Output | MRP Horizontal Plan 26-Jul-2021 041459.xlsx |
| Blitz Report™ XML Import | MRP_Horizontal_Plan.xml |
| SQL Source | MRP Horizontal Plan.sql |
| Full Report on Enginatics | www.enginatics.com/reports/mrp-horizontal-plan/ |
MRP Horizontal Plan - Case Study & Technical Analysis
Executive Summary
The MRP Horizontal Plan is the classic “spreadsheet” view of the material plan. It displays supply, demand, and projected available balance (PAB) in time buckets (days, weeks, periods) across the horizon. It is the fundamental tool for planners to visualize the supply/demand balance for an item over time.
Business Challenge
Planners need to see the “story” of an item’s inventory. A simple list of orders isn’t enough; they need to see how the inventory level fluctuates.
- Trend Analysis: “Are we consistently running low on stock at the end of the month?”
- Capacity Planning: “Do we have a huge spike in demand in Week 40 that we need to prepare for?”
- Safety Stock Visibility: “When does our projected balance dip below the safety stock level?”
Solution
The MRP Horizontal Plan provides a time-phased view of all planning elements.
Key Features:
- Time Buckets: Aggregates data into daily, weekly, or monthly columns.
- Row Types: Displays separate rows for Gross Requirements, Scheduled Receipts, Planned Orders, and Projected Available Balance.
- BOM Explosion: Can optionally explode the bill of materials to show requirements for components.
Technical Architecture
The report mimics the “Horizontal Plan” form in the Planner’s Workbench.
Key Tables and Views
MRP_MATERIAL_PLANS: The core table storing the time-phased data (bucketed quantities) for the plan.MRP_WORKBENCH_BUCKET_DATES: Defines the start and end dates for the time buckets.MRP_SYSTEM_ITEMS: Stores plan-specific item attributes (like safety stock levels for that specific plan).
Core Logic
- Bucketing: The system aggregates individual supply/demand records into the defined time buckets.
- Calculation: It calculates the Projected Available Balance (PAB) for each bucket:
Previous PAB + Supply - Demand. - Presentation: Pivots the data so that time periods are columns and data types (Supply, Demand) are rows.
Business Impact
- Visual Planning: Makes it easy to spot trends and potential issues at a glance.
- Communication: Provides a clear, standard format for discussing inventory plans with suppliers or production managers.
- Stability: Helps planners smooth out production by visualizing peaks and valleys in demand.
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© Enginatics GmbH