[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select trunc(mp.curr_start_date),trunc(mp.curr_cutoff_date)
into l_curr_start_date,l_curr_cutoff_date
from msc_plans mp where mp.plan_id=p_plan_id;
select min(bkt_start_date), max(bkt_end_date)
into l_first_bkt_start_date, l_last_bkt_end_date
from msc_plan_buckets
where plan_id=p_plan_id;
select o.currency_code
into l_owning_currency_code
from msc_trading_partners o, msc_plans p
where o.sr_instance_id=p.sr_instance_id
and o.sr_tp_id=p.organization_id
and o.partner_type=3
and p.plan_id=p_plan_id;
select msc_hub_query_s.nextval into l_qid_vmi from dual;
insert into msc_hub_query(
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
number1, -- plan_id
number3, -- sr_instance_id
number4, -- organization_id
number5, -- inventory_item_id
number10 -- vmi flag
)
select
unique l_qid_vmi,l_sysdate,1,l_sysdate,1,1,
p_plan_id,
msi.sr_instance_id,
msi.organization_id,
msi.inventory_item_id,
nvl(mis.vmi_flag,0)
from msc_item_suppliers mis,
msc_system_items msi
where msi.plan_id = mis.plan_id
and msi.sr_instance_id = mis.sr_instance_id
and msi.organization_id = mis.organization_id
and msi.inventory_item_id = mis.inventory_item_id
and msi.plan_id=p_plan_id
and nvl(mis.vmi_flag,0)=1;
insert into msc_demands_f (
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
plan_id,
plan_run_id,
io_plan_flag,
sr_instance_id,
organization_id,
inventory_item_id,
project_id,
task_id,
customer_id,
customer_site_id,
region_id,
demand_class,
owning_org_id,
owning_inst_id,
order_date,
aggr_type, category_set_id, sr_category_id,
order_type,
vmi_flag,
demand_qty,
qty_by_due_date,
net_demand,
constrained_fcst,
constrained_fcst_value,
constrained_fcst_value2,
indep_demand_count,
indep_met_ontime_count,
indep_met_full_count,
indep_demand_value,
indep_demand_value2,
indep_demand_qty,
indep_by_due_date_qty,
Sales_order_qty,
Sales_order_count,
Sales_order_metr_count,
Sales_order_meta_count,
Forecast_qty,
--qty_by_due_date_with_p,
IO_REQUIRED_QTY,
IO_DELIVERED_QTY,
late_dmd_stf_factor,
late_order_count,
late_order_value,
late_order_value2,
service_level)
select
l_user_id,
l_sysdate,
l_user_id,
l_sysdate,
l_user_login_id,
l_program_id,
l_cp_login_id,
l_appl_id,
l_request_id,
------------------------------------------------------
p_plan_id,
p_plan_run_id,
decode(mp_tbl.plan_type,4,1,9,1,0) io_plan_flag,
demand_tbl.sr_instance_id,
demand_tbl.organization_id,
demand_tbl.inventory_item_id,
demand_tbl.project_id,
demand_tbl.task_id,
--- we should not populate customer_id/customer_site_id
--- at all for dependent demand.
--- we do not want show dependent demand in customer dimension
--- bug 6797611
decode(demand_tbl.order_type,-5,cmv.customer_id,
-6,cmv.customer_id,
-7,cmv.customer_id,
-8,cmv.customer_id,
-9,cmv.customer_id,
-10,cmv.customer_id,
-11,cmv.customer_id,
-12,cmv.customer_id,
-15,cmv.customer_id,
-22,cmv.customer_id,
-24,cmv.customer_id,
-27,cmv.customer_id,
-29,cmv.customer_id,
-30,cmv.customer_id,
-81,cmv.customer_id,
-23453),
decode(demand_tbl.order_type,-5,cmv.customer_site_id,
-6,cmv.customer_site_id,
-7,cmv.customer_site_id,
-8,cmv.customer_site_id,
-9,cmv.customer_site_id,
-10,cmv.customer_site_id,
-11,cmv.customer_site_id,
-12,cmv.customer_site_id,
-15,cmv.customer_site_id,
-22,cmv.customer_site_id,
-24,cmv.customer_site_id,
-27,cmv.customer_site_id,
-29,cmv.customer_site_id,
-30,cmv.customer_site_id,
-81,cmv.customer_site_id,
-23453),
cmv.region_id,
demand_tbl.demand_class,
demand_tbl.owning_org_id,
demand_tbl.owning_inst_id,
----- we an not just put it in curr_start_date
----- need to put it in last working day of the bucket where plan start date in
decode(sign(to_number(demand_tbl.order_date-l_curr_start_date)),-1,
msc_hub_calendar.last_work_date(p_plan_id,l_curr_start_date),
demand_tbl.order_date),
to_number(0) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
demand_tbl.order_type,
demand_tbl.vmi_flag,
sum(demand_tbl.demand_qty),
sum(demand_tbl.qty_by_due_date),
sum(demand_tbl.net_demand),
sum(demand_tbl.constrained_fcst),
sum(demand_tbl.constrained_fcst_value),
sum(demand_tbl.constrained_fcst_value * decode(demand_tbl.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))) constrained_fcst_value2,
sum(demand_tbl.indep_demand_count),
sum(demand_tbl.indep_met_ontime_count),
sum(demand_tbl.indep_met_full_count),
sum(demand_tbl.indep_demand_value),
sum(demand_tbl.indep_demand_value * decode(demand_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
nvl(mcc.CONV_RATE,0))), -- rate converation
sum(demand_tbl.indep_demand_qty),
sum(demand_tbl.indep_by_due_date_qty),
sum(demand_tbl.sales_order_qty),
sum(demand_tbl.sales_order_count),
sum(demand_tbl.sales_order_metr_count),
sum(demand_tbl.sales_order_meta_count),
sum(demand_tbl.forecast_qty),
sum(demand_tbl.io_required_qty),
sum(demand_tbl.io_delivered_qty),
sum(demand_tbl.late_dmd_stf_factor),
sum(demand_tbl.late_order_count),
sum(demand_tbl.late_order_value),
sum(demand_tbl.late_order_value * decode(demand_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
nvl(mcc.CONV_RATE,0))), --- rate
min(demand_tbl.service_level)
from(
select
-- sync sr_instance_id with organization_id
-- this is important since in org dimension,
-- it only has (inst,org)=(-23543,-23453)
decode(md.organization_id, -1, -23453, md.sr_instance_id) sr_instance_id,
-- ASCP global forecast, org leave as -1, not mapped to Org dim
-- SNO org=-1 change to Unassigned, mapped to Org dim
-- ASCP, order type 29(forecast) 77(Part_Demand) may have org=-1
-- we need to show such demand qty in order qty measure, but we
-- should not include global forecast into item's total demand,
-- total indep demand, pab measure.
decode(md.organization_id, -1, -23453, md.organization_id) organization_id,
md.inventory_item_id,
nvl(md.project_id,-23453) project_id,
nvl(md.task_id, -23453) task_id,
nvl(md.customer_id,-23453) customer_id,
nvl(md.customer_site_id, -23453) customer_site_id,
nvl(md.zone_id, -23453) region_id,
nvl(md.demand_class, -23453) demand_class,
decode(md.organization_id,-1, msc_hub_calendar.get_item_org(p_plan_id, md.inventory_item_id,
decode(md.sr_instance_id,-1, mp.sr_instance_id, md.sr_instance_id)),
md.organization_id) owning_org_id,
--- we assume that the item must exist in plan's owning inst
decode(md.sr_instance_id, -1, mp.sr_instance_id, md.sr_instance_id) owning_inst_id,
-- drp plan and
decode(mp.plan_type,5,decode(md.origination_type,
3,trunc(nvl(md.old_using_assembly_demand_date,md.using_assembly_demand_date)),
24,trunc(nvl(md.old_using_assembly_demand_date,md.using_assembly_demand_date)),
trunc(nvl(md.firm_date,md.using_assembly_demand_date))),
trunc(nvl(md.firm_date,md.using_assembly_demand_date)) ) order_date,
-1 * md.origination_type order_Type,
msi.vmi_flag,
nvl(mtp.currency_code, l_owning_currency_code) currency_code,
---- demand qty
--- take care of drp demand
--- currently in ASCP, safety_stock demand is excluded in total demand
--- but in bug 6688725,we are required to show safety stock(31) demand in order qty measure
--- however, we will not include the safety stock demand into total demand of the item
sum(decode(mp.plan_type,5,decode(md.origination_type,3,nvl(md.using_requirement_quantity,0),
24,nvl(md.using_requirement_quantity,0),
decode(md.assembly_demand_comp_date,null,
decode(md.origination_type,29,(nvl(md.probability,1)* md.using_requirement_quantity),
md.using_requirement_quantity),
decode(md.origination_type, 29,(nvl(md.probability,1)* md.daily_demand_rate),
md.daily_demand_rate))),
decode(md.assembly_demand_comp_date,null,
decode(md.origination_type,29,(nvl(md.probability,1)* md.using_requirement_quantity),
md.using_requirement_quantity),
decode(md.origination_type, 29,(nvl(md.probability,1)* md.daily_demand_rate),
md.daily_demand_rate)) )
) /
decode(nvl(least(sum(decode(md.origination_type,29,nvl(md.probability,0),null)),1),1),
0,1,
nvl(least(sum(decode(md.origination_type,29,nvl(md.probability,0),null)),1),1)
) demand_qty,
--------------------------------------------------------------------------------------------------
--- the logic for the folliwing code is.
--- if it is forecast demand ==> if min(sum(nvl(md.probability,0)),1) ==0, then =1, else min(sum(nvl(md.probability,0)),1)
--- for all other demand, it is 1
/*decode(nvl(least(sum(decode(md.origination_type,29,nvl(md.probability,0),null)),1),1),
0,1,
nvl(least(sum(decode(md.origination_type,29,nvl(md.probability,0),null)),1),1))
*/
-- take care of forecast demand which has probability
--- sum(decode(md.origination_type,31,0,nvl(md.quantity_by_due_date,0))) qty_by_due_date,
--- safety stock demand is not in total demand, so it is not in qty by due date
--- global forecast is not in total demand, so it should not in qty_by_due_date
------------------------------------------------------------------------------------------------------
sum(decode(md.origination_type,31,0,
29, decode(md.organization_id,-1,0,nvl(md.quantity_by_due_date,0) * nvl(md.probability,1)),
nvl(md.quantity_by_due_date,0) )
) /
decode(nvl(least(sum(decode(md.origination_type,29,decode(md.organization_id,-1,0,nvl(md.probability,0)),null)),1),1),
0,1,
nvl(least(sum(decode(md.origination_type,29,decode(md.organization_id,-1,0,nvl(md.probability,0)),null)),1),1)
) qty_by_due_date,
sum(decode(md.origination_type, 81, using_requirement_quantity, 0)) net_demand,
sum(decode(md.origination_type, 81, quantity_by_due_date, 0)) constrained_fcst,
sum(decode(md.origination_type, 81, quantity_by_due_date * (nvl(msi.list_price,0) * (1- nvl(msi.average_discount,0)/100)), 0)) constrained_fcst_value,
---- indep demand count
sum(decode(md.origination_type,
5,decode(nvl(md.using_requirement_quantity,0),0,0,1),
6,decode(nvl(md.using_requirement_quantity,0),0,0,1),
7,decode(nvl(md.using_requirement_quantity,0),0,0,1),
8,decode(nvl(md.using_requirement_quantity,0),0,0,1),
9,decode(nvl(md.using_requirement_quantity,0),0,0,1),
10,decode(nvl(md.using_requirement_quantity,0),0,0,1),
11,decode(nvl(md.using_requirement_quantity,0),0,0,1),
12,decode(nvl(md.using_requirement_quantity,0),0,0,1),
15,decode(nvl(md.using_requirement_quantity,0),0,0,1),
22,decode(nvl(md.using_requirement_quantity,0),0,0,1),
24,decode(nvl(md.using_requirement_quantity,0),0,0,1),
27,decode(nvl(md.using_requirement_quantity,0),0,0,1),
29,decode(md.organization_id,-1,0,decode((nvl(md.using_requirement_quantity,0)* nvl(md.probability,1)),0,0,1)),
30,decode(nvl(md.using_requirement_quantity,0),0,0,1),
81,decode(nvl(md.using_requirement_quantity,0),0,0,1),
0)) indep_demand_count,
--- indepedent demand meet on time count
sum(decode(md.origination_type,
5,decode(nvl(md.quantity_by_due_date,0),0,0,1),
6,decode(nvl(md.quantity_by_due_date,0),0,0,1),
7,decode(nvl(md.quantity_by_due_date,0),0,0,1),
8,decode(nvl(md.quantity_by_due_date,0),0,0,1),
9,decode(nvl(md.quantity_by_due_date,0),0,0,1),
10,decode(nvl(md.quantity_by_due_date,0),0,0,1),
11,decode(nvl(md.quantity_by_due_date,0),0,0,1),
12,decode(nvl(md.quantity_by_due_date,0),0,0,1),
15,decode(nvl(md.quantity_by_due_date,0),0,0,1),
22,decode(nvl(md.quantity_by_due_date,0),0,0,1),
24,decode(nvl(md.quantity_by_due_date,0),0,0,1),
27,decode(nvl(md.quantity_by_due_date,0),0,0,1),
29,decode(md.organization_id,-1,0,decode((nvl(md.quantity_by_due_date,0)* nvl(md.probability,1)),0,0,1)),
30,decode(nvl(md.quantity_by_due_date,0),0,0,1),
81,decode(nvl(md.quantity_by_due_date,0),0,0,1),
0)) indep_met_ontime_count,
--- independent demand meet full count
sum(decode(nvl(md.using_requirement_quantity,0),0,0,
decode(md.origination_type,
5,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
6,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
7,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
8,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
9,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
10,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
11,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
12,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
15,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
22,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
24,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
27,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
29,decode(md.organization_id,-1,0,decode(nvl(md.UNMET_QUANTITY,0),0,1,0)),
30,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
81,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
0))) indep_met_full_count,
---- indepndent demand value
--- using net selling price to replace std_cost
sum(decode(md.assembly_demand_comp_date,null,
decode(md.origination_type,
29,decode(md.organization_id,-1,0,
(nvl(md.probability,1)* md.using_requirement_quantity) * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100))),
5, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
6, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
7, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
8, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
9, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
10, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
11, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
12, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
15, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
22, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
24, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
27, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
30, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
81, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
0),
decode(md.origination_type,
29,decode(md.organization_id,-1,0,
(nvl(md.probability,1)*md.daily_demand_rate) * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100))),
5,md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
6, md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
7,md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
8, md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
9,md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
10, md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
11,md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
12, md.daily_demand_rate *(nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
22, md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
15,md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
24, md.daily_demand_rate *(nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
27,md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
30, md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
81, md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
0))) /
decode(nvl(least(sum(decode(md.origination_type,
29,decode(md.organization_id,-1,0,nvl(md.probability,0)), null)), 1),1),0,1,
nvl(least(sum(decode(md.origination_type,
29,decode(md.organization_id,-1,0,nvl(md.probability,0)), null)),1),1)) indep_demand_value,
--- indepedent demand qty
sum(decode(md.assembly_demand_comp_date,null,
decode(md.origination_type,
29,decode(md.organization_id,-1,0,(nvl(md.probability,1)* md.using_requirement_quantity)),
5, md.using_requirement_quantity,
6, md.using_requirement_quantity,
7, md.using_requirement_quantity,
8, md.using_requirement_quantity,
9, md.using_requirement_quantity,
10, md.using_requirement_quantity,
11, md.using_requirement_quantity,
12, md.using_requirement_quantity,
15, md.using_requirement_quantity,
22, md.using_requirement_quantity,
24, md.using_requirement_quantity,
27, md.using_requirement_quantity,
30, md.using_requirement_quantity,
81, md.using_requirement_quantity,
0),
decode(md.origination_type,
29,decode(md.organization_id,-1,0,(nvl(md.probability,1)*md.daily_demand_rate)),
5,md.daily_demand_rate,
6, md.daily_demand_rate,
7,md.daily_demand_rate,
8, md.daily_demand_rate,
9,md.daily_demand_rate,
10, md.daily_demand_rate,
11,md.daily_demand_rate,
12, md.daily_demand_rate,
22, md.daily_demand_rate,
15,md.daily_demand_rate,
24, md.daily_demand_rate,
27,md.daily_demand_rate,
30, md.daily_demand_rate,
81, md.daily_demand_rate,
0))) /
decode(nvl(least(sum(decode(md.origination_type,
29,decode(md.organization_id,-1,0,nvl(md.probability,0)), null)), 1),1),0,1,
nvl(least(sum(decode(md.origination_type,
29,decode(md.organization_id,-1,0,nvl(md.probability,0)), null)),1),1)) indep_demand_qty,
--- indep_by_due_date_qty
sum(decode(md.origination_type,5,nvl(md.quantity_by_due_date,0),
6,nvl(md.quantity_by_due_date,0),
7,nvl(md.quantity_by_due_date,0),
8,nvl(md.quantity_by_due_date,0),
9,nvl(md.quantity_by_due_date,0),
10,nvl(md.quantity_by_due_date,0),
11,nvl(md.quantity_by_due_date,0),
12,nvl(md.quantity_by_due_date,0),
15,nvl(md.quantity_by_due_date,0),
22,nvl(md.quantity_by_due_date,0),
24,nvl(md.quantity_by_due_date,0),
27,nvl(md.quantity_by_due_date,0),
29,decode(md.organization_id,-1,0,nvl(md.quantity_by_due_date,0) * nvl(md.probability,1)), -- take care of probability
30,nvl(md.quantity_by_due_date,0),
81,nvl(md.quantity_by_due_date,0),
0)) /
decode(nvl(least(sum(decode(md.origination_type,29,decode(md.organization_id,-1,nvl(md.probability,0)),null)),1),1),
0,1,
nvl(least(sum(decode(md.origination_type,29,decode(md.organization_id,-1,nvl(md.probability,0)),null)),1),1)
) indep_by_due_date_qty,
--- sales order qty
sum(decode(md.assembly_demand_comp_date,null,
decode(md.origination_type,30,md.using_requirement_quantity,to_number(null)),
decode(md.origination_type,30,md.daily_demand_rate,to_number(null)))
) sales_order_qty,
--- sales order count
sum(decode(md.origination_type,30,1,to_number(null))) sales_order_count,
--- count of sales order meets require date
sum(decode(md.origination_type,30,
decode(sign(md.SCHEDULE_SHIP_DATE-md.request_date),-1,1,0),
to_number(null))) sales_order_metr_count,
--- sales orde meets accept date
sum(decode(md.origination_type,30,
decode(sign(md.SCHEDULE_SHIP_DATE- md.LATEST_ACCEPTABLE_DATE),-1,1,0),
to_number(null))) sales_order_meta_count,
--- forecast qty
sum(decode(md.assembly_demand_comp_date,null,
decode(md.origination_type,29,decode(md.organization_id,-1,0,(nvl(md.probability,1)* md.using_requirement_quantity)),
to_number(null)),
decode(md.origination_type, 29,decode(md.organization_id,-1,0,(nvl(md.probability,1)* md.daily_demand_rate)),
to_number(null)))
) /
decode(nvl(least(sum(decode(md.origination_type,29,decode(md.organization_id,-1,0,nvl(md.probability,0)),null)),1),1),
0,1,
nvl(least(sum(decode(md.origination_type,29,decode(md.organization_id,-1,0,nvl(md.probability,0)),null)),1),1)
) forecast_qty,
sum(decode(mp.plan_type,4,decode(md.origination_type,
5,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
6,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
7,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
8,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
9,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
10,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
11,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
12,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
15,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
22,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
24,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
27,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
29,decode(md.organization_id,-1,0,nvl(md.old_demand_quantity,0) * nvl(md.probability,1)),
30,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
0),
9,decode(md.origination_type,
5,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
6,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
7,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
8,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
9,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
10,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
11,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
12,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
15,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
22,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
24,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
27,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
29,decode(md.organization_id,-1,0,nvl(md.old_demand_quantity,0) * nvl(md.probability,1)),
30,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
0),
0)) io_delivered_qty,
sum(decode(mp.plan_type,4,decode(md.origination_type,
5,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
6,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
7,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
8,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
9,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
10,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
11,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
12,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
15,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
22,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
24,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
27,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
29,decode(md.organization_id,-1,0,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1)),
30,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
0),
9,decode(md.origination_type,
5,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
6,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
7,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
8,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
9,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
10,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
11,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
12,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
15,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
22,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
24,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
27,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
29,decode(md.organization_id,-1,0,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1)),
30,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
0),
0)) io_required_qty,
-----------------------------------------------------------------------------------------------------
to_number(null) late_dmd_stf_factor,
to_number(null) late_order_count,
to_number(null) late_order_value,
---- indep demand service_level
min(decode(md.origination_type,
5,nvl(md.service_level, 50),
6,nvl(md.service_level, 50),
7,nvl(md.service_level, 50),
8,nvl(md.service_level, 50),
9,nvl(md.service_level, 50),
10,nvl(md.service_level, 50),
11,nvl(md.service_level, 50),
12,nvl(md.service_level, 50),
15,nvl(md.service_level, 50),
22,nvl(md.service_level, 50),
24,nvl(md.service_level, 50),
27,nvl(md.service_level, 50),
29,nvl(md.service_level, 50),
30,nvl(md.service_level, 50),
81,nvl(md.service_level, 50),
null)) service_level
from msc_demands md, msc_trading_partners mtp, msc_plans mp,
(select msi_1.plan_id,
msi_1.sr_instance_id,
msi_1.organization_id,
msi_1.inventory_item_id,
msi_1.standard_cost,
msi_1.list_price,
msi_1.AVERAGE_DISCOUNT,
nvl(f_1.number10,0) vmi_flag
from msc_system_items msi_1,
msc_hub_query f_1
where f_1.query_id(+) = l_qid_vmi
and f_1.number1(+) = msi_1.plan_id
and f_1.number3(+) = msi_1.sr_instance_id
and f_1.number4(+) = msi_1.organization_id
and f_1.number5(+) = msi_1.inventory_item_id) msi
where md.plan_id = p_plan_id
and msi.plan_id = md.plan_id
and msi.sr_instance_id = decode(md.sr_instance_id, -1, mp.sr_instance_id, md.sr_instance_id)
and msi.organization_id = decode(md.organization_id,-1,
msc_hub_calendar.get_item_org(p_plan_id, md.inventory_item_id,
decode(md.sr_instance_id, -1, mp.sr_instance_id, md.sr_instance_id)),
md.organization_id)
and msi.inventory_item_id = md.inventory_item_id
and md.sr_instance_id = mtp.sr_instance_id(+)
and md.organization_id = mtp.sr_tp_id(+)
and mtp.partner_type(+) = 3
and mp.plan_id=md.plan_id
group by
decode(md.organization_id, -1, -23453, md.sr_instance_id),
decode(md.organization_id, -1, -23453, md.organization_id),
md.inventory_item_id,
nvl(md.project_id,-23453),
nvl(md.task_id, -23453),
nvl(md.customer_id,-23453),
nvl(md.customer_site_id, -23453),
nvl(md.zone_id, -23453),
nvl(md.demand_class, -23453),
decode(md.organization_id, -1, msc_hub_calendar.get_item_org(p_plan_id, md.inventory_item_id,
decode(md.sr_instance_id, -1, mp.sr_instance_id, md.sr_instance_id)),
md.organization_id),
decode(md.sr_instance_id, -1, mp.sr_instance_id, md.sr_instance_id),
decode(mp.plan_type,5,decode(md.origination_type,
3,trunc(nvl(md.old_using_assembly_demand_date,md.using_assembly_demand_date)),
24,trunc(nvl(md.old_using_assembly_demand_date,md.using_assembly_demand_date)),
trunc(nvl(md.firm_date,md.using_assembly_demand_date))),
trunc(nvl(md.firm_date,md.using_assembly_demand_date)) ),
-1 * md.origination_type,
msi.vmi_flag,
nvl(mtp.currency_code, l_owning_currency_code)
union all
select
md1.sr_instance_id,
md1.organization_id,
md1.inventory_item_id,
nvl(md1.project_id,-23453) project_id,
nvl(md1.task_id, -23453) task_id,
nvl(md1.customer_id,-23453) customer_id,
nvl(md1.customer_site_id,-23453) customer_site_id,
nvl(md1.zone_id,-23453) region_id,
nvl(md1.demand_class,-23453) demand_class,
md1.organization_id owning_org_id,
md1.sr_instance_id owning_inst_id,
trunc(nvl(md1.firm_date,md1.using_assembly_demand_date)) order_date,
-1 * md1.origination_type order_type,
msi1.vmi_flag,
nvl(mtp1.currency_code, l_owning_currency_code) currency_code,
to_number(null) demand_Qty,
to_number(null) qty_by_due_date,
to_number(null) net_demand,
to_number(null) constrained_fcst,
to_number(null) constrained_fcst_value,
to_number(null) indep_demand_count,
to_number(null) indep_met_ontime_count,
to_number(null) indep_met_full_count,
to_number(null) indep_demand_value,
to_number(null) indep_demand_qty,
to_number(null) indep_by_due_date_qty,
to_number(null) sales_order_qty,
to_number(null) sales_order_count,
to_number(null) sales_order_metr_count,
to_number(null) sales_order_meta_count,
to_number(null) forecast_qty,
to_number(null) io_delivered_qty,
to_number(null) io_required_qty,
--- late demand satisfaction factor
--
sum(decode(md1.assembly_demand_comp_date,null,
decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.using_requirement_quantity),
31,0,md1.using_requirement_quantity),
decode(md1.origination_type, 29,(nvl(md1.probability,1)* md1.daily_demand_rate),
31, 0,md1.daily_demand_rate))
* round(decode(med1.exception_type,
24, decode(sign(md1.dmd_satisfied_date - md1.using_assembly_demand_date), 0,0,
md1.dmd_satisfied_date - md1.using_assembly_demand_date),
69, 0, --- only for exception 24 and 26
26,decode(sign(md1.dmd_satisfied_date - md1.using_assembly_demand_date), 0,0,
md1.dmd_satisfied_date - md1.using_assembly_demand_date),0))
)
/decode(nvl(least(sum(decode(md1.origination_type,29,nvl(md1.probability,0),null)),1),1),
0,1,
nvl(least(sum(decode(md1.origination_type,29,nvl(md1.probability,0),null)),1),1))
- sum( nvl(md1.quantity_by_due_date,0) * nvl(md1.probability,1)
* round(decode(med1.exception_type,
24, decode(sign(md1.dmd_satisfied_date - md1.using_assembly_demand_date), 0,0,
md1.dmd_satisfied_date - md1.using_assembly_demand_date),
69, 0, --- only for exception 24 and 26
26,decode(sign(md1.dmd_satisfied_date - md1.using_assembly_demand_date), 0,0,
md1.dmd_satisfied_date - md1.using_assembly_demand_date),0))
)
/decode(nvl(least(sum(decode(md1.origination_type,29,nvl(md1.probability,0),null)),1),1),
0,1,
nvl(least(sum(decode(md1.origination_type,29,nvl(md1.probability,0),null)),1),1)) late_dmd_stf_factor,
--- late demand count
sum(decode(med1.exception_type,
24,1,
26,1,
69,1,
to_number(null))) late_order_count,-- all demand type
--- late demand val
--- need denominator part for forecast demand qty???
--- simply the decode???
--- replace std_cost with net selling price
sum(decode(med1.exception_type,
24,
decode(md1.assembly_demand_comp_date,null,
decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.using_requirement_quantity),
31,0,md1.using_requirement_quantity),
decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.daily_demand_rate),
31, 0,md1.daily_demand_rate)),
69,
decode(md1.assembly_demand_comp_date,null,
decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.using_requirement_quantity),
31,0,md1.using_requirement_quantity),
decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.daily_demand_rate),
31, 0,md1.daily_demand_rate)),
26,
decode(md1.assembly_demand_comp_date,null,
decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.using_requirement_quantity),
31,0,md1.using_requirement_quantity),
decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.daily_demand_rate),
31, 0,md1.daily_demand_rate)),
to_number(null)) * (nvl(msi1.list_price,0) * (1- nvl(msi1.AVERAGE_DISCOUNT,0)/100))
) /
decode(nvl(least(sum(decode(md1.origination_type,29,nvl(md1.probability,0),
null)),1),1),0,1,
nvl(least(sum(decode(md1.origination_type,29,nvl(md1.probability,0),null)),1),1)
) late_order_value,
min(nvl(md1.service_level, 50))
from msc_demands md1,msc_trading_partners mtp1,
msc_exception_details med1,
msc_plans mp1,
(select msi_2.plan_id,
msi_2.sr_instance_id,
msi_2.organization_id,
msi_2.inventory_item_id,
msi_2.standard_cost,
msi_2.list_price,
msi_2.AVERAGE_DISCOUNT,
nvl(f_2.number10,0) vmi_flag
from msc_system_items msi_2,
msc_hub_query f_2
where f_2.query_id(+) = l_qid_vmi
and f_2.number1(+) = msi_2.plan_id
and f_2.number3(+) = msi_2.sr_instance_id
and f_2.number4(+) = msi_2.organization_id
and f_2.number5(+) = msi_2.inventory_item_id) msi1
where md1.plan_id=med1.plan_id
and md1.plan_id=p_plan_id
and md1.origination_type in (5,6,7,8,9,10,11,12,15,22,24,27,29,30) --- only for indep demand
and md1.sr_instance_id = med1.sr_instance_id
and md1.organization_id =med1.organization_id
and md1.inventory_item_id=med1.inventory_item_id
and md1.demand_id= MED1.NUMBER1
and md1.plan_id=msi1.plan_id
and md1.sr_instance_id = msi1.sr_instance_id
and md1.organization_id = msi1.organization_id
and md1.inventory_item_id = msi1.inventory_item_id
and med1.EXCEPTION_TYPE in (24,26,69)
and md1.sr_instance_id = mtp1.sr_instance_id(+)
and md1.organization_id = mtp1.sr_tp_id(+)
and mtp1.partner_type(+) = 3
and mp1.plan_id = md1.plan_id
and mp1.plan_type <> 6
and md1.sr_instance_id<>-1
and md1.organization_id<>-1 -- exclude global f/c
group by
md1.sr_instance_id,
md1.organization_id,
md1.inventory_item_id,
nvl(md1.project_id,-23453),
nvl(md1.task_id, -23453),
nvl(md1.customer_id,-23453),
nvl(md1.customer_site_id,-23453),
nvl(md1.zone_id,-23453),
nvl(md1.demand_class,-23453),
md1.organization_id,
md1.sr_instance_id,
trunc(nvl(md1.firm_date,md1.using_assembly_demand_date)),
-1 * md1.origination_type,
msi1.vmi_flag,
nvl(mtp1.currency_code, l_owning_currency_code)
order by 1,2,3,4,5,6,7,8,9,10,11,12,13) demand_tbl,msc_plans mp_tbl,
MSC_CURRENCY_CONV_MV mcc,msc_phub_customers_mv cmv
where
mcc.FROM_CURRENCY(+) =demand_tbl.currency_code -- make sure 'XXX' is not a valid currency code
and mcc.TO_CURRENCY(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
and mcc.CALENDAR_DATE (+)= demand_tbl.order_date
and mp_tbl.plan_id = p_plan_id
and cmv.customer_id = nvl(demand_tbl.customer_id,-23453)
and cmv.customer_site_id = nvl(demand_tbl.customer_site_id,-23453)
and cmv.region_id = decode(nvl(demand_tbl.region_id,-23453),
-23453,decode(nvl(demand_tbl.customer_id,-23453),-23453,-23453,cmv.region_id),
demand_tbl.region_id)
group by
decode(mp_tbl.plan_type,4,1,9,1,0),
demand_tbl.sr_instance_id,
demand_tbl.organization_id,
demand_tbl.inventory_item_id,
demand_tbl.project_id,
demand_tbl.task_id,
decode(demand_tbl.order_type,-5,cmv.customer_id,
-6,cmv.customer_id,
-7,cmv.customer_id,
-8,cmv.customer_id,
-9,cmv.customer_id,
-10,cmv.customer_id,
-11,cmv.customer_id,
-12,cmv.customer_id,
-15,cmv.customer_id,
-22,cmv.customer_id,
-24,cmv.customer_id,
-27,cmv.customer_id,
-29,cmv.customer_id,
-30,cmv.customer_id,
-81,cmv.customer_id,
-23453),
decode(demand_tbl.order_type,-5,cmv.customer_site_id,
-6,cmv.customer_site_id,
-7,cmv.customer_site_id,
-8,cmv.customer_site_id,
-9,cmv.customer_site_id,
-10,cmv.customer_site_id,
-11,cmv.customer_site_id,
-12,cmv.customer_site_id,
-15,cmv.customer_site_id,
-22,cmv.customer_site_id,
-24,cmv.customer_site_id,
-27,cmv.customer_site_id,
-29,cmv.customer_site_id,
-30,cmv.customer_site_id,
-81,cmv.customer_site_id,
-23453),
cmv.region_id,
demand_tbl.demand_class,
demand_tbl.owning_org_id,
demand_tbl.owning_inst_id,
decode(sign(to_number(demand_tbl.order_date-l_curr_start_date)),-1,
msc_hub_calendar.last_work_date(p_plan_id,l_curr_start_date),
demand_tbl.order_date),
demand_tbl.order_type,
demand_tbl.vmi_flag;
select msc_hub_query_s.nextval into l_qid_last_date from dual;
insert into msc_hub_query (
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
date1
)
select unique l_qid_last_date, l_sysdate, 1, l_sysdate, 1, 1, trunc(mcd.month_end_date)
from msc_calendar_dtl mcd
where mcd.month_end_date between l_first_bkt_start_date and l_last_bkt_end_date
union
select unique l_qid_last_date, l_sysdate, 1, l_sysdate, 1, 1, trunc(mw.week_end_date)
from msc_phub_mfg_cal_weeks_mv mw
where mw.week_end_date between l_first_bkt_start_date and l_last_bkt_end_date
union
select unique l_qid_last_date, l_sysdate, 1, l_sysdate, 1, 1, trunc(bp.end_date)
from msc_phub_fiscal_periods_mv bp
where bp.end_date between l_first_bkt_start_date and l_last_bkt_end_date
union
select l_qid_last_date, l_sysdate, 1, l_sysdate, 1, 1, trunc(l_last_bkt_end_date)
from dual
order by 1;
select msc_hub_query_s.nextval into l_qid_demand_date from dual;
insert into msc_hub_query (
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
date1
)
select
unique l_qid_demand_date,
l_sysdate,1,l_sysdate,1,1,
mdf.order_date
from msc_demands_f mdf
where mdf.plan_id=p_plan_id
and mdf.plan_run_id=p_plan_run_id
and mdf.aggr_type=0;
select msc_hub_query_s.nextval into l_qid_demand_item from dual;
insert into msc_hub_query (
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
number1,
number2,
number3,
number4,
number5,
number6,
number7, -- customer_id
number8, -- customer_site_id
number11, -- region_id
char1,
number9, -- owning_org_id
number10 -- owning_inst_id
)
select
unique l_qid_demand_item,
l_sysdate,1,l_sysdate,1,1,
mdf.plan_id,
mdf.plan_run_id,
mdf.sr_instance_id,
mdf.organization_id,
mdf.inventory_item_id,
mdf.vmi_flag,
mdf.customer_id,
mdf.customer_site_id,
mdf.region_id,
mdf.demand_class,
mdf.owning_org_id,
mdf.owning_inst_id
from msc_demands_f mdf
where mdf.plan_id=p_plan_id
and mdf.plan_run_id=p_plan_run_id
and mdf.aggr_type=0;
select msc_hub_query_s.nextval into l_qid_dl_relation from dual;
insert into msc_hub_query (
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
date1, --- last date
date2
)
select
unique l_qid_dl_relation,
l_sysdate,1,l_sysdate,1,1,
l.date1,
(select max(d.date1) from msc_hub_query d
where d.query_id=l_qid_demand_date
and d.date1<=l.date1)
from msc_hub_query l where l.query_id=l_qid_last_date;
insert into msc_demands_cum_f (
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
---------------------------------------------------
plan_id,
plan_run_id,
io_plan_flag,
sr_instance_id,
organization_id,
inventory_item_id,
vmi_flag,
customer_id,
customer_site_id,
region_id,
demand_class,
owning_org_id,
owning_inst_id,
order_date,
aggr_type, category_set_id, sr_category_id,
backlog_qty,
cum_sales_order_qty,
cum_forecast_qty,
cum_constrained_fcst,
cum_constrained_fcst_value,
cum_constrained_fcst_value2)
select
l_user_id,
l_sysdate,
l_user_id,
l_sysdate,
l_user_login_id,
l_program_id,
l_cp_login_id,
l_appl_id,
l_request_id,
------------------------------------------------
demand_cum_tbl.plan_id,
demand_cum_tbl.plan_run_id,
decode(mp_cum_tbl.plan_type,4,1,9,1,0) io_plan_flag,
demand_cum_tbl.sr_instance_id,
demand_cum_tbl.organization_id,
demand_cum_tbl.inventory_item_id,
demand_cum_tbl.vmi_flag,
demand_cum_tbl.customer_id,
demand_cum_tbl.customer_site_id,
demand_cum_tbl.region_id,
demand_cum_tbl.demand_Class,
demand_cum_tbl.owning_org_id,
demand_cum_tbl.owning_inst_id,
demand_cum_tbl.order_date,
to_number(0) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
nvl(demand_cum_tbl.cum_indep_request_qty,0) - nvl(demand_cum_tbl.cum_indep_qty_by_due_date,0),
nvl(demand_cum_tbl.cum_sales_order_qty,0),
nvl(demand_cum_tbl.cum_forecast_qty,0),
nvl(demand_cum_tbl.cum_constrained_fcst,0),
nvl(demand_cum_tbl.cum_constrained_fcst_value,0),
nvl(demand_cum_tbl.cum_constrained_fcst_value2,0)
from
(select
cum.plan_id,
cum.plan_run_id,
cum.sr_instance_id,
cum.organization_id,
cum.inventory_item_id,
cum.vmi_flag,
cum.customer_id,
cum.customer_site_id,
cum.region_id,
cum.demand_class,
cum.owning_org_id,
cum.owning_inst_id,
last_date.date1 order_date,
LAST_VALUE(cum.cum_indep_request_qty ignore nulls )
OVER (PARTITION BY cum.plan_id,cum.plan_run_id,cum.sr_instance_id,
cum.organization_id,cum.inventory_item_id,
cum.demand_class,cum.customer_id,cum.customer_site_id,cum.region_id
order by last_date.date1) cum_indep_request_qty,
LAST_VALUE(cum.cum_indep_qty_by_due_date ignore nulls )
OVER (PARTITION BY cum.plan_id,cum.plan_run_id,cum.sr_instance_id,
cum.organization_id,cum.inventory_item_id,
cum.demand_class,cum.customer_id,cum.customer_site_id,cum.region_id
order by last_date.date1) cum_indep_qty_by_due_date,
LAST_VALUE(cum.cum_FORECAST_QTY ignore nulls )
OVER (PARTITION BY cum.plan_id,cum.plan_run_id,cum.sr_instance_id,
cum.organization_id,cum.inventory_item_id,
cum.demand_class,cum.customer_id,cum.customer_site_id,cum.region_id
order by last_date.date1) cum_FORECAST_QTY,
LAST_VALUE(cum.cum_SALES_ORDER_QTY ignore nulls )
OVER (PARTITION BY cum.plan_id,cum.plan_run_id,cum.sr_instance_id,
cum.organization_id,cum.inventory_item_id,
cum.demand_class,cum.customer_id,cum.customer_site_id,cum.region_id
order by last_date.date1) cum_SALES_ORDER_QTY,
LAST_VALUE(cum.cum_constrained_fcst ignore nulls )
OVER (PARTITION BY cum.plan_id,cum.plan_run_id,cum.sr_instance_id,
cum.organization_id,cum.inventory_item_id,
cum.demand_class,cum.customer_id,cum.customer_site_id,cum.region_id
order by last_date.date1) cum_constrained_fcst,
LAST_VALUE(cum.cum_constrained_fcst_value ignore nulls )
OVER (PARTITION BY cum.plan_id,cum.plan_run_id,cum.sr_instance_id,
cum.organization_id,cum.inventory_item_id,
cum.demand_class,cum.customer_id,cum.customer_site_id,cum.region_id
order by last_date.date1) cum_constrained_fcst_value,
LAST_VALUE(cum.cum_constrained_fcst_value2 ignore nulls )
OVER (PARTITION BY cum.plan_id,cum.plan_run_id,cum.sr_instance_id,
cum.organization_id,cum.inventory_item_id,
cum.demand_class,cum.customer_id,cum.customer_site_id,cum.region_id
order by last_date.date1) cum_constrained_fcst_value2
from
msc_hub_query last_date,
(
select
demand_item.number1 plan_id,
demand_item.number2 plan_run_id,
demand_item.number3 sr_instance_id,
demand_item.number4 organization_id,
demand_item.number5 inventory_item_id,
demand_item.number6 vmi_flag,
demand_item.number7 customer_id,
demand_item.number8 customer_site_id,
demand_item.number11 region_id,
demand_item.char1 demand_class,
demand_item.date1 order_date,
demand_item.number9 owning_org_id,
demand_item.number10 owning_inst_id,
------------------------------------------------------------
sum( t1.indep_demand_qty) over(partition by
demand_item.number1,demand_item.number2,
demand_item.number3,demand_item.number4,
demand_item.number5,demand_item.char1,
demand_item.number7,demand_item.number8,demand_item.number9
order by demand_item.date1) cum_indep_request_qty,
sum(t1.INDEP_BY_DUE_DATE_QTY) over(partition by
demand_item.number1,demand_item.number2,
demand_item.number3,demand_item.number4,
demand_item.number5,demand_item.char1,
demand_item.number7,demand_item.number8,demand_item.number9
order by demand_item.date1) cum_indep_qty_by_due_date,
sum(t1.FORECAST_QTY) over(partition by
demand_item.number1,demand_item.number2,
demand_item.number3,demand_item.number4,
demand_item.number5,demand_item.char1,
demand_item.number7,demand_item.number8,demand_item.number9
order by demand_item.date1) cum_FORECAST_QTY,
sum(t1.SALES_ORDER_QTY) over(partition by
demand_item.number1,demand_item.number2,
demand_item.number3,demand_item.number4,
demand_item.number5,demand_item.char1,
demand_item.number7,demand_item.number8,demand_item.number9
order by demand_item.date1) cum_SALES_ORDER_QTY,
sum(t1.constrained_fcst) over(partition by
demand_item.number1,demand_item.number2,
demand_item.number3,demand_item.number4,
demand_item.number5,demand_item.char1,
demand_item.number7,demand_item.number8,demand_item.number9
order by demand_item.date1) cum_constrained_fcst,
sum(t1.constrained_fcst_value) over(partition by
demand_item.number1,demand_item.number2,
demand_item.number3,demand_item.number4,
demand_item.number5,demand_item.char1,
demand_item.number7,demand_item.number8,demand_item.number9
order by demand_item.date1) cum_constrained_fcst_value,
sum(t1.constrained_fcst_value2) over(partition by
demand_item.number1,demand_item.number2,
demand_item.number3,demand_item.number4,
demand_item.number5,demand_item.char1,
demand_item.number7,demand_item.number8,demand_item.number9
order by demand_item.date1) cum_constrained_fcst_value2
from msc_demands_f t1,
(select item.number1,
item.number2,
item.number3,
item.number4,
item.number5,
item.number6,
item.number7,
item.number8,
item.number11,
item.char1,
demand_date.date1,
item.number9,
item.number10
from msc_hub_query item,
msc_hub_query demand_date
where item.query_id=l_qid_Demand_item
and demand_date.query_id = l_qid_demand_date) demand_item
where demand_item.date1 = t1.order_date(+)
and demand_item.number1 = t1.plan_id(+)
and demand_item.number2 = t1.plan_run_id(+)
and demand_item.number3 = t1.sr_instance_id(+)
and demand_item.number4 = t1.organization_id(+)
and demand_item.number5 = t1.inventory_item_id(+)
and demand_item.number7 = t1.customer_id(+)
and demand_item.number8 = t1.customer_site_id(+)
and demand_item.number11 = t1.region_id(+)
and demand_item.char1=t1.demand_class(+)
and t1.aggr_type(+) = 0) cum
where last_date.date2 = cum.order_Date
and last_date.query_id = l_qid_dl_relation ) demand_cum_tbl,msc_plans mp_cum_tbl
where mp_cum_tbl.plan_id = demand_cum_tbl.plan_id;
insert into msc_demands_f (
plan_id, plan_run_id,
io_plan_flag,
sr_instance_id, organization_id, inventory_item_id,
project_id, task_id,
customer_id, customer_site_id, region_id,
demand_class,
owning_org_id, owning_inst_id,
order_date,
aggr_type, category_set_id, sr_category_id,
order_type, vmi_flag,
demand_qty,
qty_by_due_date,
net_demand,
constrained_fcst,
constrained_fcst_value,
constrained_fcst_value2,
indep_demand_count,
indep_met_ontime_count,
indep_met_full_count,
indep_demand_value,
indep_demand_value2,
indep_demand_qty,
indep_by_due_date_qty,
sales_order_qty,
sales_order_count,
sales_order_metr_count,
sales_order_meta_count,
forecast_qty,
io_required_qty,
io_delivered_qty,
late_dmd_stf_factor,
late_order_count,
late_order_value,
late_order_value2,
service_level,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
-- category (42, 43, 44)
select
f.plan_id, f.plan_run_id,
f.io_plan_flag,
f.sr_instance_id, f.organization_id,
to_number(-23453) inventory_item_id,
f.project_id, f.task_id,
f.customer_id, f.customer_site_id, f.region_id,
f.demand_class,
f.owning_org_id, f.owning_inst_id,
f.order_date,
to_number(42) aggr_type,
l_category_set_id1 category_set_id,
nvl(q.sr_category_id, -23453),
f.order_type, f.vmi_flag,
sum(f.demand_qty),
sum(f.qty_by_due_date),
sum(f.net_demand),
sum(f.constrained_fcst),
sum(f.constrained_fcst_value),
sum(f.constrained_fcst_value2),
sum(f.indep_demand_count),
sum(f.indep_met_ontime_count),
sum(f.indep_met_full_count),
sum(f.indep_demand_value),
sum(f.indep_demand_value2),
sum(f.indep_demand_qty),
sum(f.indep_by_due_date_qty),
sum(f.sales_order_qty),
sum(f.sales_order_count),
sum(f.sales_order_metr_count),
sum(f.sales_order_meta_count),
sum(f.forecast_qty),
sum(f.io_required_qty),
sum(f.io_delivered_qty),
sum(f.late_dmd_stf_factor),
sum(f.late_order_count),
sum(f.late_order_value),
sum(f.late_order_value2),
min(service_level),
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
msc_demands_f f,
msc_phub_item_categories_mv q
where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
and f.aggr_type=0
and f.owning_inst_id=q.sr_instance_id(+)
and f.owning_org_id=q.organization_id(+)
and f.inventory_item_id=q.inventory_item_id(+)
and q.category_set_id(+)=l_category_set_id1
group by
f.plan_id, f.plan_run_id,
f.io_plan_flag,
f.sr_instance_id, f.organization_id,
f.project_id, f.task_id,
f.customer_id, f.customer_site_id, f.region_id,
f.demand_class,
f.owning_org_id, f.owning_inst_id,
f.order_date,
nvl(q.sr_category_id, -23453),
f.order_type, f.vmi_flag;
insert into msc_demands_cum_f (
plan_id, plan_run_id,
io_plan_flag,
sr_instance_id, organization_id, inventory_item_id,
vmi_flag,
customer_id, customer_site_id, region_id,
demand_class,
owning_org_id, owning_inst_id,
order_date,
aggr_type, category_set_id, sr_category_id,
backlog_qty,
cum_sales_order_qty,
cum_forecast_qty,
cum_constrained_fcst,
cum_constrained_fcst_value,
cum_constrained_fcst_value2,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
-- category (42, 43, 44)
select
f.plan_id, f.plan_run_id,
f.io_plan_flag,
f.sr_instance_id, f.organization_id,
to_number(-23453) inventory_item_id,
f.vmi_flag,
f.customer_id, f.customer_site_id, f.region_id,
f.demand_class,
f.owning_org_id, f.owning_inst_id,
f.order_date,
to_number(42) aggr_type,
l_category_set_id1 category_set_id,
nvl(q.sr_category_id, -23453),
sum(f.backlog_qty),
sum(f.cum_sales_order_qty),
sum(f.cum_forecast_qty),
sum(f.cum_constrained_fcst),
sum(f.cum_constrained_fcst_value),
sum(f.cum_constrained_fcst_value2),
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
msc_demands_cum_f f,
msc_phub_item_categories_mv q
where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
and f.aggr_type=0
and f.owning_inst_id=q.sr_instance_id(+)
and f.owning_org_id=q.organization_id(+)
and f.inventory_item_id=q.inventory_item_id(+)
and q.category_set_id(+)=l_category_set_id1
group by
f.plan_id, f.plan_run_id,
f.io_plan_flag,
f.sr_instance_id, f.organization_id,
f.vmi_flag,
f.customer_id, f.customer_site_id, f.region_id,
f.demand_class,
f.owning_org_id, f.owning_inst_id,
f.order_date,
nvl(q.sr_category_id, -23453);
insert into msc_demands_f (
plan_id, plan_run_id, io_plan_flag,
sr_instance_id, organization_id, inventory_item_id,
project_id, task_id,
customer_id, customer_site_id, region_id,
demand_class, owning_org_id, owning_inst_id, order_date,
aggr_type, category_set_id, sr_category_id,
order_type, vmi_flag,
demand_qty,
qty_by_due_date,
net_demand,
constrained_fcst,
constrained_fcst_value,
constrained_fcst_value2,
indep_demand_count,
indep_met_ontime_count,
indep_met_full_count,
indep_demand_value,
indep_demand_value2,
indep_demand_qty,
indep_by_due_date_qty,
sales_order_qty,
sales_order_count,
sales_order_metr_count,
sales_order_meta_count,
forecast_qty,
io_required_qty,
io_delivered_qty,
late_dmd_stf_factor,
late_order_count,
late_order_value,
late_order_value2,
service_level,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
-- category-mfg_period (1016, 1017, 1018)
select
f.plan_id, f.plan_run_id, f.io_plan_flag,
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.region_id,
f.demand_class, f.owning_org_id, f.owning_inst_id,
mp.period_start_date order_date,
decode(f.aggr_type, 42, 1016, 43, 1017, 1018) aggr_type,
f.category_set_id, f.sr_category_id,
f.order_type, f.vmi_flag,
sum(f.demand_qty),
sum(f.qty_by_due_date),
sum(f.net_demand),
sum(f.constrained_fcst),
sum(f.constrained_fcst_value),
sum(f.constrained_fcst_value2),
sum(f.indep_demand_count),
sum(f.indep_met_ontime_count),
sum(f.indep_met_full_count),
sum(f.indep_demand_value),
sum(f.indep_demand_value2),
sum(f.indep_demand_qty),
sum(f.indep_by_due_date_qty),
sum(f.sales_order_qty),
sum(f.sales_order_count),
sum(f.sales_order_metr_count),
sum(f.sales_order_meta_count),
sum(f.forecast_qty),
sum(f.io_required_qty),
sum(f.io_delivered_qty),
sum(f.late_dmd_stf_factor),
sum(f.late_order_count),
sum(f.late_order_value),
sum(f.late_order_value2),
min(f.service_level),
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
msc_demands_f f,
msc_phub_mfg_cal_periods_mv mp
where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
and f.aggr_type between 42 and 44
and f.order_date between mp.period_start_date and mp.period_end_date
group by
f.plan_id, f.plan_run_id, f.io_plan_flag,
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.region_id,
f.demand_class, f.owning_org_id, f.owning_inst_id,
mp.period_start_date,
decode(f.aggr_type, 42, 1016, 43, 1017, 1018),
f.category_set_id, f.sr_category_id,
f.order_type, f.vmi_flag
union all
-- category-fiscal_period (1019, 1020, 1021)
select
f.plan_id, f.plan_run_id, f.io_plan_flag,
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.region_id,
f.demand_class, f.owning_org_id, f.owning_inst_id,
fp.start_date order_date,
decode(f.aggr_type, 42, 1019, 43, 1020, 1021) aggr_type,
f.category_set_id, f.sr_category_id,
f.order_type, f.vmi_flag,
sum(f.demand_qty),
sum(f.qty_by_due_date),
sum(f.net_demand),
sum(f.constrained_fcst),
sum(f.constrained_fcst_value),
sum(f.constrained_fcst_value2),
sum(f.indep_demand_count),
sum(f.indep_met_ontime_count),
sum(f.indep_met_full_count),
sum(f.indep_demand_value),
sum(f.indep_demand_value2),
sum(f.indep_demand_qty),
sum(f.indep_by_due_date_qty),
sum(f.sales_order_qty),
sum(f.sales_order_count),
sum(f.sales_order_metr_count),
sum(f.sales_order_meta_count),
sum(f.forecast_qty),
sum(f.io_required_qty),
sum(f.io_delivered_qty),
sum(f.late_dmd_stf_factor),
sum(f.late_order_count),
sum(f.late_order_value),
sum(f.late_order_value2),
min(f.service_level),
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
msc_demands_f f,
msc_phub_fiscal_periods_mv fp
where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
and f.aggr_type between 42 and 44
and f.order_date between fp.start_date and fp.end_date
group by
f.plan_id, f.plan_run_id, f.io_plan_flag,
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.region_id,
f.demand_class, f.owning_org_id, f.owning_inst_id,
fp.start_date,
decode(f.aggr_type, 42, 1019, 43, 1020, 1021),
f.category_set_id, f.sr_category_id,
f.order_type, f.vmi_flag;
delete /*+ PARALLEL(mos) */ from msc_demands_f
where rownum<=p_commit_size and plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
select count(1) into l_num from msc_demands_f
where plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);