Select f.plan_id,f.plan_run_id,
f.sr_instance_id,f.organization_id,f.inventory_item_id,
f.project_id,f.task_id,f.customer_id,f.customer_site_id,f.demand_class,
f.order_type,f.bkt_start_date,
SUM(nvl(d.indep_demand_qty ,0)- nvl(d.INDEP_BY_DUE_DATE_QTY ,0))
OVER (PARTITION BY f.plan_id,f.plan_run_id,
f.sr_instance_id,f.organization_id,f.inventory_item_id,
f.project_id,f.task_id,f.customer_id,f.customer_site_id,
f.demand_class,f.order_type
ORDER by f.bkt_start_date) backlog_qty ,
SUM(nvl(d.sales_order_qty ,0))
OVER (PARTITION BY f.plan_id,f.plan_run_id,
f.sr_instance_id,f.organization_id,f.inventory_item_id,
f.project_id,f.task_id,f.customer_id,f.customer_site_id,
f.demand_class,f.order_type
ORDER by f.bkt_start_date) sum_sales_order_qty ,
SUM(nvl(d.forecast_qty ,0))
OVER (PARTITION BY f.plan_id,f.plan_run_id,
f.sr_instance_id,f.organization_id,f.inventory_item_id,
f.project_id,f.task_id,f.customer_id,f.customer_site_id,
f.demand_class,f.order_type
ORDER by f.bkt_start_date) sum_forecast_qty
From msc_demands_f d,
(select unique mpb.bkt_start_date,
d1.plan_id,d1.plan_run_id,
d1.sr_instance_id,d1.organization_id,d1.inventory_item_id,
nvl(d1.project_id,-23453) project_id,
nvl(d1.task_id,-23453) task_id,
nvl(d1.customer_id,-23453) customer_id,
nvl(d1.customer_site_id,-23453) customer_site_id,
nvl(d1.demand_class,-23453) demand_class,
d1.order_type
from msc_demands_f d1,msc_plan_buckets mpb,msc_plans mp
where mp.plan_id= mpb.plan_id
and mp.sr_instance_id = mpb.sr_instance_id
and mp.organization_id = mpb.organization_id
and mpb.curr_flag=1
and mp.plan_id = d1.plan_id) f
where d.plan_id(+) = f.plan_id
and d.plan_run_id(+) = f.plan_run_id
and d.sr_instance_id(+) = f.sr_instance_id
and d.organization_id(+) = f.organization_id
and d.inventory_item_id(+) = f.inventory_item_id
and d.project_id(+) = f.project_id
and d.task_id (+) =f.task_id
and d.customer_id(+) = f.customer_id
and d.customer_site_id(+)=f.customer_site_id
and d.demand_class(+) =f.demand_class
and d.order_Type(+) = f.order_type
and d.order_date(+) = f.bkt_start_date
[Home] [Help]