Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT dda.baseline_plan,
- dda.plan_run_id,
- dda.demand_id,
- dda.org_code,
- dda.planning_policy,
- dda.master_scheduler,
- dda.planning_group,
- dda.item,
- dda.bottleneck,
- dda.override_atp ,
- dda.level_load,
- dda.mcm,
- dda.customer_group_class,
- dda.business_strategy,
- dda.sc_product_classification,
- dda.customer_name,
- dda.salesrep_name,
- dda. sales_order_number,
- dda.order_number,
- dda.line_number,
- dda.so_type,
- dda.selling_price AS UNIT_SELLING_price,
- dda.currency,
- dda.demand_class,
- dda.origination_type,
- dda.order_type,
- dda.request_date,
- dda.promise_date,
- -- trunc(dda.promise_date)-trunc(dda.schedule_ship_date) as OriginalSSD_SSD ,
- nvl(trunc(promise_date)-trunc(schedule_ship_date),0) AS OriginalSSD_SSD ,
- dda.mlx_lt_date,
- dda.mdlt,
- dda.schedule_ship_date,
- dda.dmd_satisfied_date,
- dda.ssd_minus_mdlt,
- dda.dsd_minus_mdlt,
- dda.ssd_minus_rd,
- dda.dsd_minus_rd,
- dda.dsd_minus_ssd,
- dda.mdlt_minus_rd,
- dda.dmd_qty,
- dda.qty_by_due_date,
- dda.crit_org_code,
- dda.crit_item_name,
- dda.crit_order_number,
- dda.crit_dept_code,
- dda.crit_resource_code,
- dda.crit_order_type,
- dda.crit_source_org_code,
- dda.supp_need_by_date,
- dda.supp_earliest_comp_date,
- dda.supp_end_date,
- dda.supp_start_date,
- dda.days_late_contribution_rd,
- dda.exception_type
- FROM
- ( SELECT
- mp.compile_designator baseline_plan,
- stg.plan_run_id plan_run_id,
- stg.demand_id demand_id,
- RANK() OVER(
- PARTITION BY stg.demand_id
- ORDER BY
- stg.exception_type,ROWNUM ) AS rank,
- stg.org_code org_code,
- itm.attribute1 planning_policy,
- itmb.xxmlx_attribute2 master_scheduler,
- cat_set.category_name planning_group,
- stg.item_name item,
- itmb.xxmlx_attribute13 bottleneck,
- stg.ATTRIBUTE14 OVERRIDE_ATP ,
- itmb.xxmlx_attribute14 level_load,
- itmb.xxmlx_attribute15 mcm,
- itmb.xxmlx_attribute16 customer_group_class,
- itmb.xxmlx_attribute17 business_strategy,
- ( SELECT cat2.category_name
- FROM msc_phub_categories_mv cat2
- WHERE itmb.sr_instance_id = itmb.sr_instance_id
- AND cat2.category_set_id = fnd_profile.VALUE('MSC_HUB_CAT_SET_ID_3')
- AND cat2.sr_category_id = itmb.sr_category_id3
- AND ROWNUM = 1 ) sc_product_classification,
- stg.customer_name,
- stg.salesrep_name,
- stg.sales_order_number,
- to_number( regexp_substr (stg.sales_order_number, '[^.]+', 1, 1)) AS order_number,
- regexp_substr (stg.sales_order_number, '[^.]+', 1, 2) AS so_type ,
- regexp_substr( regexp_substr (stg.sales_order_number, '[^(]+', 1, 2), '[^)]+', 1, 1)AS line_number ,
- md.selling_price,
- 'EUR' currency,
- stg.demand_class,
- stg.origination_type,
- stg.order_type,
- TO_CHAR(stg.request_date,'DD-MON-YYYY') request_date,
- TO_CHAR(stg.promise_date,'DD-MON-YYYY') promise_date,
- -- trunc(stg.promise_date)-trunc(stg.schedule_ship_date) as OriginalSSD_SSD ,
- TO_CHAR(stg.mlx_lt_date,'DD-MON-YYYY') mlx_lt_date,
- stg.attribute15 mdlt,
- TO_CHAR(stg.schedule_ship_date,'DD-MON-YYYY') schedule_ship_date,
- TO_CHAR(stg.dmd_satisfied_date,'DD-MON-YYYY') dmd_satisfied_date,
- round(stg.schedule_ship_date - stg.mlx_lt_date) ssd_minus_mdlt,
- round(stg.dmd_satisfied_date - stg.mlx_lt_date) dsd_minus_mdlt,
- round(stg.schedule_ship_date - stg.request_date) ssd_minus_rd,
- round(stg.dmd_satisfied_date - stg.request_date) dsd_minus_rd,
- round(stg.dmd_satisfied_date - stg.schedule_ship_date) dsd_minus_ssd,
- round(stg.mlx_lt_date - stg.request_date) mdlt_minus_rd,
- stg.dmd_qty,
- stg.qty_by_due_date,
- stg.crit_org_code,
- stg.crit_item_name,
- stg.crit_order_number,
- stg.crit_dept_code,
- stg.crit_resource_code,
- stg.crit_order_type,
- stg.crit_source_org_code,
- TO_CHAR(stg.supp_need_by_date,'DD-MON-YYYY') supp_need_by_date,
- TO_CHAR(stg.supp_earliest_comp_date,'DD-MON-YYYY') supp_earliest_comp_date,
- TO_CHAR(stg.supp_end_date,'DD-MON-YYYY') supp_end_date,
- TO_CHAR(stg.supp_start_date,'DD-MON-YYYY') supp_start_date,
- round(stg.days_late_contribution) days_late_contribution_rd,
- stg.exception_type
- FROM
- msc_plans mp,
- msc_plan_runs mpr,
- xxmlx_msc_delayed_demands_stg stg,
- xxmlx_msc_apcc_item_d itm,
- msc_apcc_item_d itmb,
- msc_phub_item_categories_mv cat1,
- msc_phub_categories_mv cat_set,
- msc_demands md
- WHERE
- mp.compile_designator ='MRP_PLAN'
- -- AND (itmb.xxmlx_attribute2 =:p_master_scheduler OR :p_master_scheduler IS NULL)
- AND mp.plan_id = mpr.plan_id
- AND mpr.last_run_flag = 1
- AND stg.sr_instance_id = mpr.sr_instance_id
- AND stg.plan_id = mpr.plan_id
- AND stg.plan_run_id = mpr.plan_run_id
- AND stg.sr_instance_id = itm.sr_instance_id
- AND stg.plan_id = itm.plan_id
- AND stg.plan_run_id = itm.plan_run_id
- AND stg.organization_id = itm.organization_id
- AND stg.inventory_item_id = itm.inventory_item_id
- AND stg.sr_instance_id = itmb.sr_instance_id
- AND stg.plan_id = itmb.plan_id
- AND stg.organization_id = itmb.organization_id
- AND stg.inventory_item_id = itmb.inventory_item_id
- AND cat1.category_set_id = fnd_profile.VALUE('MSC_HUB_CAT_SET_ID_1')
- AND cat1.sr_instance_id = itmb.sr_instance_id
- AND cat1.organization_id = itmb.organization_id
- AND cat1.inventory_item_id = itmb.inventory_item_id
- AND cat1.sr_category_id = itmb.sr_category_id1
- AND cat1.category_set_id = cat_set.category_set_id
- AND cat1.sr_instance_id = cat_set.sr_instance_id
- AND cat1.sr_category_id = cat_set.sr_category_id
- AND md.demand_id(+)=stg.demand_id
- AND md.plan_id(+)=stg.plan_id
- UNION ALL
- SELECT
- mp.compile_designator baseline_plan,
- mpr.plan_run_id plan_run_id,
- md.demand_id demand_id,
- RANK() OVER(
- PARTITION BY md.demand_id
- ORDER BY
- md.using_assembly_demand_date,ROWNUM
- ) AS rank,
- msi.organization_code org_code,
- itm.attribute1 planning_policy,
- itmb.xxmlx_attribute2 master_scheduler,
- cat_set.category_name planning_group,
- itmb.item_name item,
- itmb.xxmlx_attribute13 bottleneck,
- NULL OVERRIDE_ATP ,
- itmb.xxmlx_attribute14 level_load,
- itmb.xxmlx_attribute15 mcm,
- itmb.xxmlx_attribute16 customer_group_class,
- itmb.xxmlx_attribute17 business_strategy,
- ( SELECT cat2.category_name
- FROM msc_phub_categories_mv cat2
- WHERE itmb.sr_instance_id = itmb.sr_instance_id
- AND cat2.category_set_id = fnd_profile.VALUE('MSC_HUB_CAT_SET_ID_3')
- AND cat2.sr_category_id = itmb.sr_category_id3
- AND ROWNUM = 1 ) sc_product_classification,
- NULL customer_name,
- NULL salesrep_name,
- NULL sales_order_number,
- NULL order_number,
- NULL so_type ,
- NULL line_number ,
- 0 selling_price,
- 'EUR' currency,
- NULL demand_class,
- md.origination_type,
- 'Forecast' order_type,
- TO_CHAR(md.request_date,'DD-MON-YYYY') request_date,
- TO_CHAR(md.promise_date,'DD-MON-YYYY') promise_date,
- -- trunc(md.promise_date)-trunc(schedule_ship_date) as OriginalSSD_SSD ,
- NULL mlx_lt_date,
- NULL mdlt,
- TO_CHAR(md.using_assembly_demand_date,'DD-MON-YYYY') schedule_ship_date,
- TO_CHAR(md.dmd_satisfied_date,'DD-MON-YYYY') dmd_satisfied_date,
- NULL ssd_minus_mdlt,
- NULL dsd_minus_mdlt,
- round(md.using_assembly_demand_date - md.request_date) ssd_minus_rd,
- round(md.dmd_satisfied_date - md.request_date) dsd_minus_rd,
- round(md.dmd_satisfied_date - md.using_assembly_demand_date) dsd_minus_ssd,
- NULL mdlt_minus_rd,
- md.using_requirement_quantity,
- md.quantity_by_due_date,
- NULL crit_org_code,
- NULL crit_item_name,
- NULL crit_order_number,
- NULL crit_dept_code,
- NULL crit_resource_code,
- NULL crit_order_type,
- NULL crit_source_org_code,
- NULL supp_need_by_date,
- NULL supp_earliest_comp_date,
- NULL supp_end_date,
- NULL supp_start_date,
- NULL days_late_contribution_rd,
- NULL exception_type
- FROM
- msc_plans mp,
- msc_plan_runs mpr,
- msc_demands md,
- msc_system_items msi,
- xxmlx_msc_apcc_item_d itm,
- msc_apcc_item_d itmb,
- msc_phub_item_categories_mv cat1,
- msc_phub_categories_mv cat_set
- WHERE
- mp.compile_designator ='MRP_PLAN'
- -- AND ( itmb.xxmlx_attribute2 =:p_master_scheduler OR :p_master_scheduler IS NULL )
- AND mp.plan_id = mpr.plan_id
- AND mpr.last_run_flag = 1
- AND md.sr_instance_id = mpr.sr_instance_id
- AND md.plan_id = mpr.plan_id
- AND md.sr_instance_id = itm.sr_instance_id
- AND md.plan_id = itm.plan_id
- AND mpr.plan_run_id = itm.plan_run_id
- AND md.organization_id = itm.organization_id
- AND md.inventory_item_id = itm.inventory_item_id
- AND md.sr_instance_id = itmb.sr_instance_id
- AND md.sr_instance_id = msi.sr_instance_id
- AND md.plan_id = msi.plan_id
- AND md.organization_id = msi.organization_id
- AND md.inventory_item_id = msi.inventory_item_id
- AND msi.bom_item_type <> 5
- AND md.plan_id = itmb.plan_id
- AND md.organization_id = itmb.organization_id
- AND md.inventory_item_id = itmb.inventory_item_id
- AND cat1.category_set_id = fnd_profile.VALUE('MSC_HUB_CAT_SET_ID_1')
- AND cat1.sr_instance_id = itmb.sr_instance_id
- AND cat1.organization_id = itmb.organization_id
- AND cat1.inventory_item_id = itmb.inventory_item_id
- AND cat1.sr_category_id = itmb.sr_category_id1
- AND cat1.category_set_id = cat_set.category_set_id
- AND cat1.sr_instance_id = cat_set.sr_instance_id
- AND cat1.sr_category_id = cat_set.sr_category_id
- AND md.origination_type IN (22,29)
- AND md.using_requirement_quantity > 0
- AND trunc(md.using_assembly_demand_date) >= trunc(md.dmd_satisfied_date)
- ) dda
- WHERE 1=1
- AND rank <= DECODE('Y','Y',1,999999)
- ORDER BY baseline_plan,plan_run_id,demand_id ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement