MRP Pegging – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Detail report for MRP planning pegging showing the hierarchy from demand, including forecast, sales orders, work order to supply (WIP jobs, purchase orders and on hand stock)

Report Parameters

Organization Code, Plan, Sales Order, Order Type, Item, Supplier, Planner, Buyer, Make or Buy, Pegging, Exception, Origination Type, Plan Delay Days from, Actual Delay Days from, Supply Type, Receipt Date older than x days, Exclude On Hand

Oracle EBS Tables Used

bom_calendar_dates, rcv_transactions, oe_sets, per_people_x, po_headers_all, po_document_types_all_vl, po_lines_all, mrp_exception_details, mtl_safety_stocks, mrp_forecast_designators, mrp_forecast_dates

Report Categories

Enginatics

Running This SQL Without Blitz Report

Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.

Download & Import Options

Resource	Link
Excel Example Output	MRP Pegging 02-Aug-2018 222325.xlsx
Blitz Report™ XML Import	MRP_Pegging.xml
Full SQL on Enginatics	www.enginatics.com/reports/mrp-pegging/

MRP Pegging - Case Study & Technical Analysis

Executive Summary

The MRP Pegging report provides a comprehensive view of the supply-demand links established by the planning engine. Unlike the “End Assembly Pegging” report which focuses on the final product, this report details the immediate parent-child relationships for any item, showing exactly what demand is driving a specific supply order.

Business Challenge

Planners need to justify every supply order.

Justification: “Why is the system telling me to buy 500 units of Part X? What is it used for?”
Allocation: “I have 100 units of stock. Which work orders are claiming that stock?”
Impact Assessment: “If this Purchase Order is late, which specific Work Orders will be delayed?”

Solution

The MRP Pegging report exposes the “pegging” tree.

Key Features:

Upstream/Downstream: Can show what demand is driving a supply (Upstream) or what supply is satisfying a demand (Downstream).
Order Details: Includes specific order numbers (PO #, Job #, SO #).
Delay Analysis: Highlights cases where the supply date is later than the demand date (Plan Delay).

Technical Architecture

The report queries the pegging data generated during the MRP run.

Key Tables and Views

MRP_FULL_PEGGING: The core table storing the links between transaction IDs (Supply ID -> Demand ID).
MRP_GROSS_REQUIREMENTS: Details the demand side (Sales Orders, Forecasts, WIP Component Demand).
MRP_RECOMMENDATIONS: Details the supply side (Planned Orders, Reschedule Recommendations).
PO_HEADERS_ALL / WIP_DISCRETE_JOBS: Provides details for existing supply orders.

Core Logic

Root Identification: Starts with a specific item or order.
Traversal: Follows the PEGGING_ID links in MRP_FULL_PEGGING to find the related transactions.
Enrichment: Joins to source tables (PO, WIP, OE) to get human-readable details like Order Numbers and Customer Names.

Business Impact

Inventory Reduction: Helps identify “orphan” supply (supply with no pegging) that can be cancelled.
Expediting: Pinpoints exactly which supply orders are critical for meeting customer demand.
Transparency: Removes the “black box” mystery of MRP by showing the exact logic behind every recommendation.

MRP Pegging | Oracle EBS SQL Report

Detail report for MRP planning pegging showing the hierarchy from demand, including forecast, sales orders, work order to supply (WIP jobs, purchase…