MSC Pegging

Description
Categories: Toolkit - Operations
Columns: Plan Instance, Plan Name, Organization Code, Planner, Buyer, Ex. Set, Item, Cat Set, Cat Name, Make/Buy ...
ASCP: Export Pegging Trees from the Planners Workbench.
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
Plan
 
LOV
Peg Other
msc_get_name.lookup_meaning@A2M_DBLINK('MRP_FLP_SUPPLY_DEMAND_TYPE',mfp.demand_id) = :peg_other
LOV
Planning Instance
 
LOV
Make / Buy
msc_get_name.lookup_meaning@A2M_DBLINK('MTL_PLANNING_MAKE_BUY',msi.planning_make_buy_code) = :makeorbuy
LOV
Buyer
msi.buyer_name = :buyer
LOV
Category Set
 
LOV
Category
mic.category_name = :cat
LOV
Peg Type
msc_get_name.lookup_meaning@A2M_DBLINK('MSC_DEMAND_ORIGINATION',md.origination_type) = :peg_type
LOV
Organization
mpo.organization_code = :org_code
LOV
Item
msi.item_name=:item
LOV
Using Assembly
msi2.item_name = :using_assembly
LOV
Planner
msi.planner_code = :planner
LOV
Supply Type
msc_get_name.lookup_meaning@A2M_DBLINK('MRP_ORDER_TYPE',ms.order_type) = :supply_type
LOV
instance_id|plan_id|organization_id|inventory_item_id
select distinct
 'mai.instance_id = ' || xrrpv.value
from
(select substr(xrrpv.value,1,instr(xrrpv.value,'|',1,1)-1) value
 from
  (select dbms_lob.substr(xrrpv.value,4000,1) value
   from
     xxen_report_runs                      xrr
   , xxen_report_parameters_vl    xrpv
   , xxen_report_run_param_values xrrpv
   where
           xrr.request_id = fnd_global.conc_request_id
   and xrpv.parameter_name = 'instance_id|plan_id|organization_id|inventory_item_id'
   and xrpv.parameter_id   = xrrpv.parameter_id
   and xrrpv.run_id        = xrr.run_id
   and rownum             <= 1
  ) xrrpv
) xrrpv
Supply Date From
ms.new_schedule_date >= :supdatefr
Date
Supply Date To
ms.new_schedule_date < :supdateto + 1
Date
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
instance_id|plan_id|organization_id|inventory_item_id
select distinct
 'mp.plan_id = ' || xrrpv.value
from
(select substr(xrrpv.value,instr(xrrpv.value,'|',1,1)+1,instr(xrrpv.value,'|',1,2)-instr(xrrpv.value,'|',1,1)-1) value
 from
  (select dbms_lob.substr(xrrpv.value,4000,1) value
   from
     xxen_report_runs             xrr
   , xxen_report_parameters_vl    xrpv
   , xxen_report_run_param_values xrrpv
   where
       xrr.request_id = fnd_global.conc_request_id
   and xrpv.parameter_name = 'instance_id|plan_id|organization_id|inventory_item_id'
   and xrpv.parameter_id   = xrrpv.parameter_id
   and xrrpv.run_id        = xrr.run_id
   and rownum             <= 1
  ) xrrpv
) xrrpv
instance_id|plan_id|organization_id|inventory_item_id
select distinct
 'mpo.organization_id in (' || listagg(xrrpv.value,',') within group (order by xrrpv.value) || ')'
from
(select distinct substr(xrrpv.value,instr(xrrpv.value,'|',1,2)+1,instr(xrrpv.value,'|',1,3)-instr(xrrpv.value,'|',1,2)-1) value
 from
  (select dbms_lob.substr(xrrpv.value,4000,1) value
   from
     xxen_report_runs             xrr
   , xxen_report_parameters_vl    xrpv
   , xxen_report_run_param_values xrrpv
   where
       xrr.request_id = fnd_global.conc_request_id
   and xrpv.parameter_name = 'instance_id|plan_id|organization_id|inventory_item_id'
   and xrpv.parameter_id   = xrrpv.parameter_id
   and xrrpv.run_id        = xrr.run_id
  ) xrrpv
) xrrpv
instance_id|plan_id|organization_id|inventory_item_id
select distinct
 'msi.organization_id || ''|'' || msi.inventory_item_id in (' || listagg(xrrpv.value,',') within group (order by xrrpv.value) || ')'
from
(select distinct '''' || substr(xrrpv.value,instr(xrrpv.value,'|',1,2)+1,20) || ''''  value
 from
  (select dbms_lob.substr(xrrpv.value,4000,1) value
   from
     xxen_report_runs             xrr
   , xxen_report_parameters_vl    xrpv
   , xxen_report_run_param_values xrrpv
   where
       xrr.request_id = fnd_global.conc_request_id
   and xrpv.parameter_name = 'instance_id|plan_id|organization_id|inventory_item_id'
   and xrpv.parameter_id   = xrrpv.parameter_id
   and xrrpv.run_id        = xrr.run_id
  ) xrrpv
) xrrpv
Download