select /*+ push_pred(medv)*/
mai.instance_code planning_instance,
mp.compile_designator plan_name,
medv.organization_code,
medv.project_number,
medv.task_number,
&lp_custom_attributes
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,
(select mcs.category_set_name from msc_category_sets&a2m_dblink mcs where medv.category_set_id=mcs.category_set_id) category_set_name,
medv.category_name,
regexp_substr(medv.category_name,'[^.]+',1,1) category1,
regexp_substr(medv.category_name,'[^.]+',1,2) category2,
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,
trunc(medv.requested_start_date) requested_start_date,
trunc(medv.requested_completion_date) requested_completion_date,
trunc(medv.required_completion_date) required_completion_date,
trunc(medv.scheduled_completion_date) scheduled_completion_date,
--kpis
medv.days_late,
medv.days_late_arrival,
medv.days_late_beyond_ladate,
medv.days_early,
medv.days_early_arrival,
medv.days_early_before_ladate,
medv.days_past,
medv.days_past_due,
medv.compression_days,
medv.days_compressed,
medv.compression_pct,
medv.constraint_pct,
medv.reschedule_in_days,
medv.reschedule_out_days,
medv.excess_duration,
medv.excess_quantity,
medv.excess_quantity_pct,
medv.shortage_duration,
medv.shortage_quantity,
medv.shortage_quantity_pct,
medv.order_fcst_qty,
medv.actual_order_qty,
medv.qty_on_hand,
medv.qty_on_order,
medv.qty_mismatch,
medv.qty_mismatch_pct,
medv.delivery_perf_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,
-- destination
medv.dest_organization_code destination_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_task_number,
medv.supply_source_org_code,
medv.supply_supplier_name,
medv.supply_supplier_site,
-- 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,
-- counts
count(distinct medv.item_segments) over () item_count,
count(distinct medv.end_item_segments) over () end_item_count,
count(distinct medv.order_type || ':' || medv.order_number) over () order_count,
-- item dff attributes
&lp_item_dff_cols
'.' last_col_flag
from
msc_apps_instances&a2m_dblink mai
,msc_plans&a2m_dblink mp
,msc_exception_details_v&a2m_dblink medv
,msc_system_items&a2m_dblink msi
,msc_demands&a2m_dblink md
where
1=1
and 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 = msi.sr_instance_id
and medv.plan_id = msi.plan_id
and medv.organization_id = msi.organization_id
and medv.inventory_item_id = msi.inventory_item_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
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 |
