MSC Pegging

➜ Back to Library
Description
Categories: Enginatics, Toolkit - Operations
Repository: Github
ASCP: Export Pegging Trees from the Planners Workbench.
Run MSC Pegging and other Oracle EBS reports with Blitz Report™ on our demo environment
select
 mai.instance_code                                 planning_instance,
 mp.compile_designator                           plan_name,
 mpo.organization_code                           organization_code,
 msi.planner_code,
 msi.buyer_name,
 msi.planning_exception_set,
 msi.item_name item,
 mcs.category_set_name,
 mic.category_name,
 msc_get_name.lookup_meaning@A2M_DBLINK('MTL_PLANNING_MAKE_BUY',msi.planning_make_buy_code)  "Make/Buy",
 msc_get_name.lookup_meaning@A2M_DBLINK('MRP_ORDER_TYPE',ms.order_type) supply_type,
 msc_get_name.supply_order_number@A2M_DBLINK( ms.order_type ,ms.order_number ,ms.plan_id ,ms.sr_instance_id ,ms.transaction_id ,ms.disposition_id ) order_number,
 msc_get_name.action@A2M_DBLINK
     ('MSC_SUPPLIES', msi.bom_item_type, msi.base_item_id, msi.wip_supply_type, ms.order_type, ms.reschedule_flag
     , ms.disposition_status_type, ms.new_schedule_date, ms.old_schedule_date, ms.implemented_quantity
     , ms.quantity_in_process, ms.new_order_quantity, msi.release_time_fence_code, ms.reschedule_days, ms.firm_quantity
     , ms.plan_id, msi.critical_component_flag, msi.mrp_planning_code, msi.lots_exist, ms.item_type_value, ms.transaction_id
     )                                           action,
 mfp.supply_quantity                             supply_qty,
 mfp.allocated_quantity                          allocated_qty,
 round(mfp.allocated_quantity, 1)                pegged_qty,
 mfp.demand_quantity                             demand_qty,
 msc_get_name.lookup_meaning@A2M_DBLINK('MSC_DEMAND_ORIGINATION',md.origination_type) peg_type,
 case when mfp.demand_id < 0
 then msc_get_name.lookup_meaning@A2M_DBLINK('MRP_FLP_SUPPLY_DEMAND_TYPE',mfp.demand_id)
 else null
 end                                             peg_other,
 nvl(md.order_number,
      decode(md.origination_type
            , 1, to_char(md.disposition_id)
            , 3, msc_get_name.job_name@A2M_DBLINK(md.disposition_id, md.plan_id, md.sr_instance_id)
            , 22, to_char(md.disposition_id)
            , 50, msc_get_name.maintenance_plan@A2M_DBLINK(md.schedule_designator_id)
            , 70, msc_get_name.maintenance_plan@A2M_DBLINK(md.schedule_designator_id)
            , 92, msc_get_name.maintenance_plan@A2M_DBLINK(md.schedule_designator_id )
            , 29,decode(md.plan_id
                       , -11, msc_get_name.designator@A2M_DBLINK(md.schedule_designator_id)
                            , decode(msi.in_source_plan
                                    ,1,msc_get_name.designator@A2M_DBLINK(md.schedule_designator_id, md.forecast_set_id )
                                    , msc_get_name.scenario_designator@A2M_DBLINK(md.forecast_set_id, md.plan_id, md.organization_id, md.sr_instance_id)
                                      || decode(msc_get_name.designator@A2M_DBLINK(md.schedule_designator_id,md.forecast_set_id )
                                               , null, null
                                                     , '/'||msc_get_name.designator@A2M_DBLINK(md.schedule_designator_id,md.forecast_set_id )
                                               )
                                    )
                       )
            , 78, to_char(md.disposition_id)
                , msc_get_name.designator@A2M_DBLINK(md.schedule_designator_id)
            )
      )                                          demand_order_number,
 nvl(md.demand_priority,md2.demand_priority)     demand_priority,
 nvl(trunc(md.using_assembly_demand_date) ,mfp.demand_date)  demand_date,
 trunc(md.old_demand_date)                       old_demand_date,
 trunc(ms.new_schedule_date)                     new_supply_schedule_date,
 trunc(ms.firm_date)                             firm_supply_date,
 ms.firm_quantity                                firm_supply_qty,
 -- prev pegging
 msi2.item_name                                  using_assembly_item,
 msc_get_name.lookup_meaning@A2M_DBLINK('MSC_DEMAND_ORIGINATION',md2.origination_type)  assembly_peg_type,
 nvl(md2.order_number,
      decode(md2.origination_type
            , 1, to_char(md2.disposition_id)
            , 3, msc_get_name.job_name@A2M_DBLINK(md2.disposition_id, md2.plan_id, md2.sr_instance_id)
            , 22, to_char(md2.disposition_id)
            , 50, msc_get_name.maintenance_plan@A2M_DBLINK(md2.schedule_designator_id)
            , 70, msc_get_name.maintenance_plan@A2M_DBLINK(md2.schedule_designator_id)
            , 92, msc_get_name.maintenance_plan@A2M_DBLINK(md2.schedule_designator_id )
            , 29,decode(md2.plan_id
                       , -11, msc_get_name.designator@A2M_DBLINK(md2.schedule_designator_id)
                            , decode(msi2.in_source_plan
                                    ,1,msc_get_name.designator@A2M_DBLINK(md2.schedule_designator_id, md2.forecast_set_id )
                                    , msc_get_name.scenario_designator@A2M_DBLINK(md2.forecast_set_id, md2.plan_id, md2.organization_id, md2.sr_instance_id)
                                      || decode(msc_get_name.designator@A2M_DBLINK(md2.schedule_designator_id,md2.forecast_set_id )
                                               , null, null
                                                     , '/'||msc_get_name.designator@A2M_DBLINK(md2.schedule_designator_id,md2.forecast_set_id )
                                               )
                                    )
                       )
            , 78, to_char(md2.disposition_id)
                , msc_get_name.designator@A2M_DBLINK(md2.schedule_designator_id)
            )
      )                                          assembly_demand_order,
 trunc(md2.using_assembly_demand_date)           assembly_demand_date,
 md2.request_ship_date                           assembly_req_ship_date,
 round((md2.dmd_satisfied_date - md2.using_assembly_demand_date), 1)  assembly_days_late,
 md2.demand_priority                             assembly_demand_priority,
 round(mfp2.demand_quantity, 1)                  assembly_demand_qty,
 round(mfp2.allocated_quantity, 1)               assembly_allocated_qty
from
 msc_apps_instances@A2M_DBLINK          mai,
 msc_plans@A2M_DBLINK                   mp,
 msc_plan_organizations@A2M_DBLINK      mpo,
 msc_system_items@A2M_DBLINK            msi,
 msc_item_categories@A2M_DBLINK         mic,
 msc_category_sets@A2M_DBLINK           mcs,
 msc_supplies@A2M_DBLINK                ms,
 msc_demands@A2M_DBLINK                 md,
 msc_full_pegging@A2M_DBLINK            mfp,
 --
 msc_full_pegging@A2M_DBLINK            mfp2,
 msc_demands@A2M_DBLINK                 md2,
 msc_system_items@A2M_DBLINK            msi2
 --
where
    mai.instance_id          = mp.sr_instance_id 
and mp.sr_instance_id        = mpo.sr_instance_id
and mp.plan_id               = mpo.plan_id
--
and mpo.sr_instance_id       = msi.sr_instance_id
and mpo.plan_id              = msi.plan_id
and mpo.organization_id      = msi.organization_id
--
and msi.sr_instance_id       = mic.sr_instance_id
and msi.organization_id      = mic.organization_id
and msi.inventory_item_id    = mic.inventory_item_id
and mic.sr_instance_id       = mcs.sr_instance_id
and mic.category_set_id      = mcs.category_set_id 
--
and msi.sr_instance_id       = ms.sr_instance_id
and msi.plan_id              = ms.plan_id
and msi.organization_id      = ms.organization_id
and msi.inventory_item_id    = ms.inventory_item_id
--
and ms.sr_instance_id        = mfp.sr_instance_id
and ms.plan_id               = mfp.plan_id
and ms.organization_id       = mfp.organization_id
and ms.transaction_id        = mfp.transaction_id
--
and mfp.sr_instance_id       = md.sr_instance_id (+)
and mfp.plan_id              = md.plan_id (+)
and mfp.organization_id      = md.organization_id (+)
and mfp.demand_id            = md.demand_id (+)
--
and mfp.sr_instance_id       = mfp2.sr_instance_id (+)
and mfp.plan_id              = mfp2.plan_id (+)
and mfp.organization_id      = mfp2.organization_id (+)
and mfp.prev_pegging_id      = mfp2.pegging_id (+)
--
and mfp2.sr_instance_id      = md2.sr_instance_id (+)
and mfp2.plan_id             = md2.plan_id (+)
and mfp2.organization_id     = md2.organization_id (+)
and mfp2.demand_id           = md2.demand_id (+)
--
and mfp2.sr_instance_id      = msi2.sr_instance_id (+)
and mfp2.plan_id             = msi2.plan_id (+)
and mfp2.organization_id     = msi2.organization_id (+)
and mfp2.inventory_item_id   = msi2.inventory_item_id (+)
--
and mai.instance_code = :p_instance_code
and mp.compile_designator = :p_plan_name
and mcs.category_set_name = :p_cat_set_name
and 1=1
--
order by
 mai.instance_code,
 mp.compile_designator,
 mpo.organization_code,
 msi.item_name
Parameter Name SQL text Validation
instance_id|plan_id|organization_id|inventory_item_id 
with x as (
select
dbms_lob.substr(xrrpv.value) value
from
xxen_report_run_param_values xrrpv,
xxen_report_parameters_vl xrpv
where
xrrpv.run_id=:run_id and
xrrpv.parameter_id=xrpv.parameter_id and
xrpv.parameter_name='instance_id|plan_id|organization_id|inventory_item_id'
)
select
'mai.instance_id='||regexp_substr(x.value,'[^|]+',1,1)||' and'||chr(10)||
'mp.plan_id='||regexp_substr(x.value,'[^|]+',1,2)||' and' text
from x where rownum=1
union all
select distinct
'mpo.organization_id in ('||listagg(x.value,',') within group (order by x.value)||') and' text
from
(select distinct regexp_substr(dbms_lob.substr(x.value),'[^|]+',1,3) value from x) x
union all
select distinct
'msi.organization_id||''|''||msi.inventory_item_id in ('||listagg(x.value,',') within group (order by x.value) || ')' text
from
(select distinct ''''||substr(x.value,instr(x.value,'|',1,2)+1,20)||'''' value from x) x
 Char
Planning Instance 
 
 LOV
Plan 
 
 LOV
Organization 
mpo.organization_code = :org_code
 LOV
Category Set 
 
 LOV
Category 
mic.category_name = :cat
 LOV
Item 
msi.item_name=:item
 LOV
Planner 
msi.planner_code = :planner
 LOV
Buyer 
msi.buyer_name = :buyer
 LOV
Make / Buy 
msc_get_name.lookup_meaning@A2M_DBLINK('MTL_PLANNING_MAKE_BUY',msi.planning_make_buy_code) = :makeorbuy
 LOV
Supply Type 
msc_get_name.lookup_meaning@A2M_DBLINK('MRP_ORDER_TYPE',ms.order_type) = :supply_type
 LOV
Supply Date From 
ms.new_schedule_date >= :supdatefr
 Date
Supply Date To 
ms.new_schedule_date < :supdateto + 1
 Date
Peg Type 
msc_get_name.lookup_meaning@A2M_DBLINK('MSC_DEMAND_ORIGINATION',md.origination_type) = :peg_type
 LOV
Peg Other 
msc_get_name.lookup_meaning@A2M_DBLINK('MRP_FLP_SUPPLY_DEMAND_TYPE',mfp.demand_id) = :peg_other
 LOV
Demand Date From 
nvl(md.using_assembly_demand_date, mfp.demand_date)  >= :dmddatefr
 Date
Demand Date To 
nvl(md.using_assembly_demand_date, mfp.demand_date) < :dmddateto + 1
 Date
Using Assembly 
msi2.item_name = :using_assembly
 LOV
Download
 
Related Blitz Reports
MSC Supply and Demand Orders MSC Vertical Plan MSC Horizontal Plan MSC Exceptions MSC Sourcing Rules (Plan Side) CAC Inventory and Intransit Value (Period-End) CAC ICP PII Inventory and Intransit Value (Period-End) PO Purchase Requisition Interface PO RCV Interface
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: