SQL on the Pegging Information

Case #1

Pegging in the plan is not correct and need SQL output to see results for one item in the plan.
This is standard SQL used by DEV to help understand the pegging in the plan output

Then we show item SQL to check for Order Modifiers which could be effecting how the item is pegging

Case #2

We are reviewing the Plan output and see that planned orders are appearing early in the plan.
When we review the pegging, we see that some orders have pegging lines that are more than 30 days into the future and we want to be able to get SQL output that will help us identify these orders throughout the plan and investigate/determine the possible cause(s)?

Fix

The following SQL scripts can be used to check item pegging, item order modifiers and check for for early orders.

Pegging SQL #1
Variation to check for a specific item
-- This is usually caused by Order Modifiers on the item - see Item SQL to follow up
-- requires you to provide the plan_id, inventory_item_id, organization_id, sr_instance_id

select /*+ ORDERED */
    s.inventory_item_id, s.organization_id
    s.new_schedule_date hls_sup_dt, /* hls final schedule date*/
    p.supply_date mbp_sup_dt, /* mbp supply  date during the pegging */
    p.supply_quantity peg_sup_qty,
    p.demand_date peg_dmd_dt, /* mbp demand date during the pegging */
    p.demand_quantity peg_dmd_qty,
    p.allocated_quantity peg_acl_qty,
    s.unbucketed_demand_date sup_unb_dbd_dt, /*demand date for push down */
    d.using_assembly_demand_date dmd_dmd_dt, /* hls final schedule date for the demand */
    nvl(d.demand_id,p.demand_id) demand_id,
    d.origination_type orig_ty,
    NVL(s.order_type, p.supply_type) ord_ty,
    d.disposition_id dmd_disp_id, /*disposition_id is the assembly level supply's transaction_Id for dependent demands */
    NVL(s.transaction_id, p.transaction_id) trans_id,
    p.pegging_id  peg_id, /* pegging_id is  in sequence, the earlier peg gets lower value */
    s.sco_supply_date sco_sup_dt,/* sco supply date for the planned orders or non-firm existing orders */
    s.unbucketed_start_date unbkt_st_dt
from
msc_full_pegging p
, msc_supplies s
, msc_demands d
where
 p.plan_id= &plan_id
and p.plan_id = s.plan_id (+)
and s.transaction_id (+)= p.transaction_id
and d.demand_id (+)= p.demand_id
and d.plan_id (+) = p.plan_id
and p.inventory_item_id= &item_id  -- use this if you want to check a particular item/org/instance combination
and p.organization_id= &org_id   -- use this if you want to check a particular item/org/instance combination
and p.sr_instance_id = &inst_id   -- use this if you want to check a particular item/org/instance combination
order by p.pegging_id;

Item SQL
Item check for order modifiers
-- use this to check item setups that could explain why orders are seen "early"
-- if they have modifiers, then we may be required to get the quantity or fixed days supply which makes order look too early or too large

select substr(ITEM_NAME,1,30) Item_name
,PLAN_ID                    
,ORGANIZATION_ID            
,INVENTORY_ITEM_ID          
,SR_INSTANCE_ID             
,SR_INVENTORY_ITEM_ID       
,LOTS_EXPIRATION            
,LOT_CONTROL_CODE           
,FIXED_DAYS_SUPPLY          
,FIXED_ORDER_QUANTITY       
,FIXED_LOT_MULTIPLIER       
,MINIMUM_ORDER_QUANTITY     
,MAXIMUM_ORDER_QUANTITY     
,ROUNDING_CONTROL_TYPE      
,PLANNING_TIME_FENCE_DAYS   
,PLANNING_TIME_FENCE_DATE   
,FIXED_LEAD_TIME            
,VARIABLE_LEAD_TIME         
,PREPROCESSING_LEAD_TIME    
,POSTPROCESSING_LEAD_TIME   
,FULL_LEAD_TIME             
,CUMULATIVE_TOTAL_LEAD_TIME 
,CUM_MANUFACTURING_LEAD_TIME
from msc_system_items
where PLAN_ID  = &plan_id
and ORGANIZATION_ID = &org_id
and INVENTORY_ITEM_ID = &item_id                  
and SR_INSTANCE_ID = &inst_id;

 In an Unconstrained plan or Constrained EDD plan, most often, we see the cause is due to Order Modifiers being used for the item, so after identifying the items via the Pegging SQL, use the Item SQL to check the item setups.
In Constrained ECC and also in Constrained EDD - the plan results could be influenced by Resource or Supplier Constraints that cause the plan to move orders to a time in the plan where we have ability to produce or procure based on these constraints.
There have also been bug fixes released for these types of issues.
- In 11.5.10 and 12.0.6 in the latest ASCP Engine/UI patches, we find most all these issues related to bug fixes are resolved.
- In 12.1.3, if the customer is on earlier cumulative patches, then there might be some bug fixes that have not been performed that could cause this issue... At the time of this writing, VCP 12.1.3.8 is the latest Cumulative patch available (Ref Note 746824.1) and we have seen most of these fixes ported and included in the code.

Pegging SQL #2
Check for all planned orders that appear earlier than demand date by 30 days or more
-- This is usually caused by Order Modifiers on the item - see Item SQL to follow up
-- this uses threshold of 30 days early - which can be modified
-- requires you to provide the plan_id

select /*+ ORDERED */
    s.inventory_item_id, s.organization_id
    s.new_schedule_date hls_sup_dt, /* hls final schedule date*/
    p.supply_date mbp_sup_dt, /* mbp supply  date during the pegging */
    p.supply_quantity peg_sup_qty,
    p.demand_date peg_dmd_dt, /* mbp demand date during the pegging */
    p.demand_quantity peg_dmd_qty,
    p.allocated_quantity peg_acl_qty,
    s.unbucketed_demand_date sup_unb_dbd_dt, /*demand date for push down */
    d.using_assembly_demand_date dmd_dmd_dt, /* hls final schedule date for the demand */
    nvl(d.demand_id,p.demand_id) demand_id,
    d.origination_type orig_ty,
    NVL(s.order_type, p.supply_type) ord_ty,
    d.disposition_id dmd_disp_id, /*disposition_id is the assembly level supply's transaction_Id for dependent demands */
    NVL(s.transaction_id, p.transaction_id) trans_id,
    p.pegging_id  peg_id, /* pegging_id is  in sequence, the earlier peg gets lower value */
    s.sco_supply_date sco_sup_dt,/* sco supply date for the planned orders or non-firm existing orders */
    s.unbucketed_start_date unbkt_st_dt
from
msc_full_pegging p
, msc_supplies s
, msc_demands d
where
 p.plan_id= &plan_id
and p.plan_id = s.plan_id (+)
and s.transaction_id (+)= p.transaction_id
and d.demand_id (+)= p.demand_id
and d.plan_id (+) = p.plan_id
-- and d.origination_type = 29 using this line will restrict the query to pick only forecast demands to peg
and NVL(s.order_type, p.supply_type) = 5 -- using this line will restrict the pegging to only planned orders
and trunc(d.using_assembly_demand_date) - trunc(p.supply_date) >30  -- use this as threshold for how early orders are before the demand
order by p.pegging_id;