MSC Exceptions

➜ Back to Library
Description
Categories: Enginatics, Toolkit - Operations
Repository: Github
ASCP: Export Planning Exceptions from the Planners Workbench.
Run MSC Exceptions and other Oracle EBS reports with Blitz Report™ on our demo environment
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
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
 Char
Planning Instance 
 
 LOV
Plan 
 
 LOV
Organization 
medv.organization_code = :org_code
 LOV
Category Set 
 
 LOV
Category 
medv.category_name = :cat
 LOV
Item 
medv.item_segments=:item
 LOV
Planner 
medv.planner_code = :planner
 LOV
Buyer 
medv.buyer_name = :buyer
 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
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
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: