MSC Pegging Hierarchy – Oracle EBS SQL Report

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

Overview

ASCP Pegging Hierarchy. This reports shows the pegging hierarchy from the Top Level Demand.

Report Parameters

Planning Instance, Plan, Organization, Show Source Org. Pegging, Category Set, Category, End Assembly, Item, Project, Planner, Buyer, Make / Buy, Supply Type, Supplier, Supply Date From, Supply Date To, Exception, Demand Origination, Demand Order, Other Demand Origination, Exclude Other Demand, Demand Date From, Demand Date To, Pegging, Show End Pegs Only, Show Item Descriptive Attributes

Oracle EBS Tables Used

msc_full_pegging&a2m_dblink, msc_system_items&a2m_dblink, msc_boms&a2m_dblink, msc_safety_stocks&a2m_dblink

Report Categories

Enginatics, R12 only

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	MSC Pegging Hierarchy - Pivot Full Hierarchy (Default) 02-Apr-2026 120700.xlsm
Blitz Report™ XML Import	MSC_Pegging_Hierarchy.xml
Full SQL on Enginatics	www.enginatics.com/reports/msc-pegging-hierarchy/

MSC Pegging Hierarchy - Case Study & Technical Analysis

Executive Summary

The MSC Pegging Hierarchy report provides a deep-dive view into the complex supply chain relationships established by ASCP. It traces the lineage from a top-level demand (like a Sales Order) down through all levels of the Bill of Materials and distribution network to the ultimate supply source.

Business Challenge

In a multi-org, multi-level supply chain, it’s hard to know “why” a supply order exists.

Traceability: “This Purchase Order is for a raw material. Which specific customer order is it supporting?”
Impact Analysis: “If this shipment from the supplier is delayed, which finished goods in which distribution centers will be affected?”
Allocation: “We have limited supply. Which orders should we prioritize?”

Solution

The MSC Pegging Hierarchy report visualizes the full pegging tree.

Key Features:

End-to-End Trace: Links Independent Demand (SO) -> Dependent Demand (WIP) -> Supply (PO/Work Order).
Multi-Org: Can trace pegging across different organizations (e.g., Factory -> Distribution Center).
Details: Shows dates, quantities, and order numbers at each level of the hierarchy.

Technical Architecture

The report uses the MSC_FULL_PEGGING table, which stores the parent-child links generated by the planning engine.

Key Tables and Views

MSC_FULL_PEGGING: The core table storing the graph of transaction links.
MSC_DEMANDS: Details of the demand side.
MSC_SUPPLIES: Details of the supply side.
MSC_SYSTEM_ITEMS: Item attributes.

Core Logic

Root Selection: Starts with a specific demand or supply transaction.
Recursive Traversal: Uses a CONNECT BY query (or recursive CTE) to traverse the MSC_FULL_PEGGING table up or down the tree.
Enrichment: Joins to the supply/demand tables to get human-readable details.

Business Impact

Customer Service: Enables accurate “Available to Promise” (ATP) checks and proactive notification of delays.
Inventory Management: Helps identify “orphan” supply that can be cancelled.
Supply Chain Visibility: Provides a clear map of material flow across the enterprise.

MSC Pegging Hierarchy | Oracle EBS SQL Report