MSC Exceptions

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