MSC Exceptions
Description
Categories: Enginatics
Repository: Github
Repository: Github
ASCP: Export Planning Exceptions from the Planners Workbench.
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, --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_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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Planning Instance |
|
LOV | |
Plan |
|
LOV | |
Organization |
|
LOV | |
Category Set |
|
LOV | |
Category |
|
LOV | |
Item |
|
LOV | |
Planner |
|
LOV | |
Buyer |
|
LOV | |
Exception Group |
|
LOV | |
Exception Type |
|
LOV | |
Due Date From |
|
Date | |
Due Date To |
|
Date | |
From Date From |
|
Date | |
From Date To |
|
Date | |
To Date From |
|
Date | |
To Date To |
|
Date | |
Show Item Descriptive Attributes |
|
LOV |