MSC Exceptions

Description
Categories: Enginatics, Toolkit - Operations
Repository: Github Columns: Plan Instance, Plan Name, Organization 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 (medv.exception_type) exception_group,
 medv.exception_type_text exception_type,
 msc_get_name.lookup_meaning ('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 ('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       mai
,msc_plans                mp 
,msc_exception_details_v  medv
,msc_category_sets        mcs
,msc_demands              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  (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
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
'medv.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
'medv.organization_id||''|''||medv.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
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