MSC Exceptions

➜ Back to Library
Description
Categories: Toolkit - Operations
Columns: Plan Instance, Plan Name, Org Code, Planner, Buyer, Exception Group, Exception Type, Action Taken, Item, Item Description ...
ASCP: Export Planning Exceptions from the Planners Workbench. 
select
 mai.instance_code planning_instance,
 mp.compile_designator plan_name,
 medv.organization_code,
 medv.planner_code,
 medv.buyer_name, 
 msc_phub_util.get_exception_group@A2M_DBLINK(medv.exception_type) exception_group,
 medv.exception_type_text exception_type,
 msc_get_name.lookup_meaning@A2M_DBLINK('MSC_ADI_YES_NO',medv.action_taken)  action_taken,
 -- item
 medv.item_segments item,
 medv.item_description,
 mcs.category_set_name,
 medv.category_name,
 medv.end_item_segments                end_item,
 -- order details
 medv.quantity                         quantity, 
 medv.order_number                     order_number,
 coalesce(medv.order_type,
          msc_get_name.lookup_meaning@A2M_DBLINK('MSC_DEMAND_ORIGINATION',md.origination_type)
         )                             order_type,
 medv.end_order_number,
 medv.firm_type,
 medv.order_priority                   order_priority,    
 -- dates
 trunc(medv.old_due_date)              old_due_date,
 trunc(medv.due_date)                  due_date,
 trunc(medv.dmd_schedule_due_date)     demand_scheduled_due_date,
 trunc(medv.dmd_satisfied_date)        demand_satisfied_date, 
 trunc(medv.curr_date)                 current_date,
 trunc(medv.from_date)                 from_date,
 trunc(medv.to_date)                   to_date,
 trunc(medv.request_ship_date)         request_ship_date,
 trunc(medv.promise_ship_date)         promise_ship_date,
 trunc(medv.orig_sched_ship_date)      orig_sched_ship_date,
 trunc(medv.sched_ship_date)           sched_ship_date,
 trunc(medv.sched_arrival_date)        sched_arrival_date,
 trunc(medv.new_dock_date)             new_dock_date,
 trunc(medv.comp_demand_date)          comp_demand_date,
 --kpis
 medv.days_late                        days_late,
 medv.days_late_arrival                days_late_arrival,
 medv.days_early_before_ladate         days_early_b4_ladate,
 medv.days_early_arrival               days_early_arriv,
 medv.compression_days                 compression_days,
 medv.days_compressed                  days_compressed,
 medv.compression_pct,
 medv.constraint_pct,
 -- customer
 medv.customer_name,
 medv.customer_site,
 medv.customer_item_name,
 medv.customer_fcst_qty,
 medv.customer_order_fcst_qty,
 medv.customer_po,
 medv.customer_po_release,
 medv.customer_po_line,
 medv.customer_po_quantity,
 medv.customer_po_creation_date,
 medv.customer_po_updated_date,
 medv.customer_po_cancel_date,
 medv.customer_po_need_by_date,
 medv.customer_po_receipt_date,
 -- demand
 medv.demand_class,
 medv.demand_organization_code,
 medv.demand_quantity,
 medv.demand_date_quantity,
 medv.dmd_schedule_item_name           demand_schedule_item_name,
 medv.dmd_schedule_order_number        demand_schedule_order_number,
 medv.dmd_schedule_org_code            demand_schedule_org_code,
 medv.dmd_schedule_qty                 demand_schedule_qty,
 -- source
 medv.source_organization_code,
 -- supply
 medv.supply_organization_code,
 medv.supply_commit_qty,
 medv.supply_item_segments,
 medv.supply_order_type,
 medv.supply_planner_code,
 medv.supply_planning_group,
 medv.supply_project_number,
 medv.supply_source_org_code,
 medv.supply_supplier_name,
 medv.supply_supplier_site,
 medv.supply_task_number,
 -- supplier details
 medv.supplier_name,
 medv.supplier_site,
 medv.supplier_so,
 medv.supplier_so_creation_date,
 medv.supplier_so_line,
 medv.supplier_item_name,
 medv.supplier_so_quantity,
 medv.supplier_so_receipt_date,
 medv.supplier_so_ship_date,
 medv.supplier_supply_commit_qty,
 medv.supplier_fcst_qty,
 -- resource
 medv.department_line_code,
 medv.resource_type_code,
 medv.resource_code,
 medv.utilization_rate load_ratio
from 
 msc_apps_instances@A2M_DBLINK      mai
,msc_plans@A2M_DBLINK               mp 
,msc_exception_details_v@A2M_DBLINK medv
,msc_category_sets@A2M_DBLINK       mcs
,msc_demands@A2M_DBLINK             md
where
    mai.instance_id       = mp.sr_instance_id 
and mp.plan_id            = medv.plan_id
and mp.sr_instance_id     = medv.sr_instance_id
and medv.sr_instance_id   = mcs.sr_instance_id  (+)
and medv.category_set_id  = mcs.category_set_id (+)
and medv.sr_instance_id   = md.sr_instance_id (+)
and medv.plan_id          = md.plan_id (+)
and medv.demand_id        = md.demand_id (+)
and mai.instance_code = :p_instance_code
and mp.compile_designator = :p_plan_name
and nvl(mcs.category_set_name,:p_cat_set_name) = :p_cat_set_name
and 1=1
order by
 mai.instance_code,
 mp.compile_designator,
 medv.organization_code,
 medv.planner_code,
 exception_group,
 medv.exception_type_text,
 medv.item_segments,
 medv.from_date,
 medv.due_date
Parameter Name SQL text Validation
Plan 
 
 LOV
Planning Instance 
 
 LOV
Buyer 
medv.buyer_name = :buyer
 LOV
Category Set 
 
 LOV
Category 
medv.category_name = :cat
 LOV
Exception Group 
msc_phub_util.get_exception_group@A2M_DBLINK (medv.exception_type) = :excptn_grp
 LOV
Exception Type 
medv.exception_type_text=:excptn
 LOV
Organization 
medv.organization_code = :org_code
 LOV
Item 
medv.item_segments=:item
 LOV
Planner 
medv.planner_code = :planner
 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
Due Date From 
medv.due_date >= :due_date_fr
 Date
Due Date To 
medv.due_date < :due_date_to + 1
 Date
From Date From 
medv.from_date >= :from_date_fr
 Date
From Date To 
medv.from_date < :from_date_to + 1
 Date
To Date From 
medv.to_date >= :to_date_fr
 Date
To Date To 
medv.to_date < :to_date_to + 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
 'medv.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
 'medv.organization_id || ''|'' || medv.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
 
Related Blitz Reports
MSC Pegging MSC Supply and Demand Orders MSC Vertical Plan MSC Horizontal Plan MSC Sourcing Rules (Plan Side) CAC ICP PII Inventory and Intransit Value (Period-End) CAC Inventory and Intransit Value (Period-End) PO Purchase Requisition Interface PO RCV Interface
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: