[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- msc_forecast_updates.consumed_qty //pabram..need to check
c_row2_net_fcst constant integer:= 4;
-- msc_forecast_updates.overconsumption_qty //pabram..need to check
c_row2_manual_fcst constant integer:= 6;
select
row_index,
region_list_id,
region_list,
region_list_state,
region_id,
region_code,
org_list_id,
org_list,
org_list_state,
inst_id,
org_id,
org_code,
top_item_id,
top_item_name,
top_item_name_state,
item_id,
item_name
from msc_analysis_query maq
where maq.query_id = p_query_id
and ( (p_row_index is not null and maq.row_index = p_row_index) or
(p_next_rowset_index is not null and maq.parent_row_index = p_next_rowset_index) )
order by row_index;
select nvl(max(parent_row_index),0)
from msc_analysis_query
where query_id = p_query_id;
select count(*)
from msc_analysis_query
where query_id = p_query_id
and parent_row_index = p_next_rowset_index;
select
compile_designator,
sr_instance_id,
organization_id,
decode(plan_id, -1, sysdate, trunc(curr_start_date)) curr_start_date,
decode(plan_id, -1, sysdate+365, trunc(curr_cutoff_date)) curr_cutoff_date
from msc_plans
where plan_id = g_plan_id;
select sum(decode(bucket_type, 1, 1,0)) day_buckets,
sum(decode(bucket_type, 2, 1,0)) week_buckets,
sum(decode(bucket_type, 3, 1,0)) period_buckets,
min(decode(bucket_type, 2, bkt_start_date)) week_start_date,
min(decode(bucket_type, 3, bkt_start_date)) pr_start_date
from msc_plan_buckets
where plan_id = g_plan_id
and sr_instance_id = g_owning_inst_id
and organization_id = g_owning_org_id;
select bkt_start_date,
bkt_end_date,
bucket_type
from msc_plan_buckets
where plan_id = g_plan_id
and sr_instance_id = g_owning_inst_id
and organization_id = g_owning_org_id
union all
select trunc(curr_start_date)-1,
trunc(curr_start_date)-1,
-99
from msc_plans
where plan_id = g_plan_id
union all
select trunc(curr_cutoff_date)+1,
trunc(curr_cutoff_date)+1,
-99
from msc_plans
where plan_id = g_plan_id
order by 1;
select week_start_date
from msc_trading_partners mtp,
msc_cal_week_start_dates wsd
where p_cal_type = c_owning_org_cal
and mtp.sr_tp_id = g_owning_org_id
and mtp.sr_instance_id = g_owning_inst_id
and mtp.partner_type = 3
and mtp.calendar_code = wsd.calendar_code
and mtp.calendar_exception_set_id = wsd.exception_set_id
and mtp.sr_instance_id = wsd.sr_instance_id
and wsd.week_start_date >= g_plan_start_date
and wsd.week_start_date <= g_plan_end_date
union all
select mcwsd.week_start_date
from msc_cal_week_start_dates mcwsd
where p_cal_type = c_profile_cal
and mcwsd.calendar_code = p_cal_code
and mcwsd.week_start_date >= g_plan_start_date
and mcwsd.week_start_date <= g_plan_end_date
order by 1;
select mpsd.period_start_date
from msc_trading_partners mtp,
msc_period_start_dates mpsd
where p_cal_type = c_owning_org_cal
and mpsd.calendar_code = mtp.calendar_code
and mpsd.sr_instance_id = mtp.sr_instance_id
and mpsd.exception_set_id = mtp.calendar_exception_set_id
and mtp.sr_instance_id = g_owning_inst_id
and mtp.sr_tp_id = g_owning_org_id
and mtp.partner_type =3
and mpsd.period_start_date >= g_plan_start_date
and mpsd.period_start_date <= g_plan_end_date
union all
select mpsd.period_start_date
from msc_period_start_dates mpsd
where p_cal_type = c_profile_cal
and mpsd.calendar_code = p_cal_code
and mpsd.period_start_date >= g_plan_start_date
and mpsd.period_start_date <= g_plan_end_date
order by 1;
insert into msc_form_query (query_id, last_update_date, last_updated_by, creation_date, created_by, number1, date1, date2 )
values (g_plan_bkts_query_id , sysdate, -1, sysdate, -1, g_bkt_type(bktIndex), l_date1, l_date2);
select count(*)
from msc_system_items msi,
msc_forecast_rules mfr
where msi.plan_id = g_plan_id
and msi.sr_instance_id = p_inst_id
and msi.organization_id = p_org_id
and msi.inventory_item_id = p_item_id
and msi.forecast_rule_for_demands = mfr.forecast_rule_id
and nvl(mfr.enable_usage_ship_fcst, 2) = 1
and nvl(mfr.history_basis,-1) in (3,4);
insert into msc_form_query (query_id, last_update_date, last_updated_by, creation_date, created_by, number1, date1, date2 )
values (g_fcst_bkt_mfq_id , sysdate, -1, sysdate, -1, l_bkt_index, l_start_date, l_end_date);
insert into msc_form_query (query_id, last_update_date, last_updated_by, creation_date, created_by, number1, date1, date2 )
values (g_fcst_bkt_mfq_id , sysdate, -1, sysdate, -1, l_bkt_index, l_start_date, l_end_date);
select
maq.row_index,
c_row2_type_16 row_type,
md.using_assembly_demand_date new_date,
md.original_item_id due_item_id,
msc_get_name.item_name(md.original_item_id, null, null, null) due_item_name,
sum(decode(md.assembly_demand_comp_date,
null, decode(md.origination_type,
29,(nvl(md.probability,1)* md.using_requirement_quantity),
31, 0,
md.using_requirement_quantity),
decode(md.origination_type,
29,(nvl(md.probability,1)* md.daily_demand_rate),
31, 0,
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)) new_quantity
from
msc_demands md,
msc_analysis_query maq,
msc_form_query mfq2, -- item-list
msc_plans mp,
msc_form_query mfq3 --g_plan_bkts_query_id
where maq.query_id = p_query_id
and maq.region_id = c_global_reg_type
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and md.plan_id = g_plan_id
--and md.sr_instance_id = c_global_inst_id
and md.organization_id = c_global_org_id
and md.inventory_item_id = mfq2.number2
and md.zone_id = c_global_reg_id
and md.plan_id = mp.plan_id
and mfq3.query_id = g_plan_bkts_query_id
and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
and md.origination_type in (c_dmd2_popu_fcst)
group by
maq.row_index,
c_row2_type_16,
md.using_assembly_demand_date,
md.original_item_id,
msc_get_name.item_name(md.original_item_id, null, null, null)
order by 1,2,3,4;
select
mss.plan_id||' - '||mss.sr_instance_id||' - '||mss.organization_id||' - '||mss.inventory_item_id row_index,
mss.plan_id,
mss.sr_instance_id,
mss.organization_id,
mss.inventory_item_id,
mss.period_start_date,
sum(mss.achieved_days_of_supply) achieved_days_of_supply,
sum(mss.safety_stock_quantity) safety_stock_quantity
from msc_safety_stocks mss,
msc_form_query mfq1, -- org-list
msc_form_query mfq2 -- item-list
where mss.plan_id= g_plan_id
and mfq1.query_id = g_org_query_id
and mfq1.number2 = mss.sr_instance_id
and mfq1.number3 = mss.organization_id
and mfq2.query_id = g_chain_query_id
and mfq2.number2 = mss.inventory_item_id
group by mss.plan_id,
mss.sr_instance_id,
mss.organization_id,
mss.inventory_item_id,
mss.period_start_date
order by
mss.plan_id,
mss.sr_instance_id,
mss.organization_id,
mss.inventory_item_id,
mss.period_start_date;
select
to_number(null) plan_id,
to_number(null) inst_id,
to_number(null) org_id,
to_number(null) item_id,
number1 bucket_type,
date1 bkt_start_date,
date2 bkt_end_date,
to_number(null) achieved_days_of_supply,
to_number(null) safety_stock_quantity
from msc_form_query
where query_id = g_plan_bkts_query_id
order by 1,2;
insert into msc_form_query (query_id, last_update_date, last_updated_by, creation_date, created_by,
number1, number2, number3, number4,
date1, number5, number6)
values (g_ss_query_id , sysdate, -1, sysdate, -1,
lx_plan_id(i), lx_inst_id(i), lx_org_id(i), lx_item_id(i),
lx_bkt_end_date(i), lx_qty1(i), lx_qty2(i));
select
count(*)
from
msc_analysis_query maq
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index;
select
maq.row_index,
msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag, ms.source_organization_id) row_type,
msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag, ms.source_organization_id) offset,
msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, nvl(ms.firm_date,ms.new_schedule_date)) new_date,
msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, nvl(ms.firm_date,ms.new_schedule_date)) old_date,
sum(decode(msi.base_item_id,
null, decode(ms.disposition_status_type,
2, 0,
decode(ms.last_unit_completion_date,
null, nvl(ms.firm_quantity,ms.new_order_quantity),
ms.daily_rate) ),
decode(ms.last_unit_completion_date,
null, nvl(ms.firm_quantity,ms.new_order_quantity),
ms.daily_rate) )) new_quantity,
sum(nvl(ms.old_order_quantity,0)) old_quantity
from
msc_supplies ms,
msc_analysis_query maq,
msc_form_query mfq1, -- org-list
msc_form_query mfq2, -- item-list
msc_plans mp,
msc_form_query mfq3, --g_plan_bkts_query_id
msc_system_items msi
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index
and mfq1.query_id = g_org_query_id
and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,c_mbp_null_value) = nvl(maq.top_item_id,c_mbp_null_value)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and ms.plan_id = g_plan_id
and ms.sr_instance_id = mfq1.number2
and ms.organization_id = mfq1.number3
and ms.inventory_item_id = mfq2.number2
and ms.plan_id = msi.plan_id
and ms.inventory_item_id = msi.inventory_item_id
and ms.organization_id = msi.organization_id
and ms.sr_instance_id = msi.sr_instance_id
and ms.plan_id = mp.plan_id
and mfq3.query_id = g_plan_bkts_query_id
and ( trunc(nvl(ms.firm_date,ms.new_schedule_date)) between trunc(mfq3.date1) and trunc(mfq3.date2) )
group by
maq.row_index,
msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag, ms.source_organization_id),
msc_sda_pkg.getSupplyRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag, ms.source_organization_id),
msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, nvl(ms.firm_date,ms.new_schedule_date)),
msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, nvl(ms.firm_date,ms.new_schedule_date))
union all
select
maq.row_index,
msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag, md.disposition_id, mio.organization_type) row_type,
msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag, md.disposition_id, mio.organization_type) offset,
msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, nvl(md.firm_date,md.using_assembly_demand_date)) new_date,
msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, nvl(md.firm_date,md.using_assembly_demand_date)) old_date,
sum(decode(md.assembly_demand_comp_date,
null, decode(md.origination_type,
29,(nvl(md.probability,1)* nvl(md.firm_quantity,md.using_requirement_quantity)),
31, 0,
nvl(md.firm_quantity,md.using_requirement_quantity)),
decode(md.origination_type,
29,(nvl(md.probability,1)* md.daily_demand_rate),
31, 0,
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)) new_quantity,
0 old_quantity
from
msc_demands md,
msc_analysis_query maq,
msc_form_query mfq1, -- org-list
msc_form_query mfq2, -- item-list
msc_plans mp,
msc_form_query mfq3, --g_plan_bkts_query_id
msc_instance_orgs mio,
msc_system_items msi
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index
and mfq1.query_id = g_org_query_id
and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,c_mbp_null_value) = nvl(maq.top_item_id,c_mbp_null_value)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and md.plan_id = g_plan_id
and md.sr_instance_id = mfq1.number2
and md.organization_id = mfq1.number3
and md.inventory_item_id = mfq2.number2
and md.plan_id = msi.plan_id
and md.inventory_item_id = msi.inventory_item_id
and md.organization_id = msi.organization_id
and md.sr_instance_id = msi.sr_instance_id
and md.plan_id = mp.plan_id
and mfq3.query_id = g_plan_bkts_query_id
and ( trunc(nvl(md.firm_date,md.using_assembly_demand_date)) between trunc(mfq3.date1) and trunc(mfq3.date2))
and md.sr_instance_id = mio.sr_instance_id
and md.organization_id = mio.organization_id
group by
maq.row_index,
msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag, md.disposition_id, mio.organization_type),
msc_sda_pkg.getDemandRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag, md.disposition_id, mio.organization_type),
msc_sda_pkg.getSDStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, nvl(md.firm_date,md.using_assembly_demand_date)),
msc_sda_pkg.getSDEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, nvl(md.firm_date,md.using_assembly_demand_date))
union all
select
maq.row_index,
c_max_level row_type,
c_row_max_level offset,
mil.inventory_date new_date,
mil.inventory_date old_date,
max(mil.max_quantity) new_quantity,
0 old_quantity
from
msc_inventory_levels mil,
msc_analysis_query maq,
msc_form_query mfq1, -- org-list
msc_form_query mfq2 -- item-list
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index
and mfq1.query_id = g_org_query_id
and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,c_mbp_null_value) = nvl(maq.top_item_id,c_mbp_null_value)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and mil.plan_id = g_plan_id
and mil.sr_instance_id = mfq1.number2
and mil.organization_id = mfq1.number3
and mil.inventory_item_id = mfq2.number2
and mil.inventory_date <= g_plan_end_date
and nvl(mil.max_quantity,mil.max_quantity_dos) is not null
group by
maq.row_index,
c_max_level,
c_row_max_level,
mil.inventory_date,
mil.inventory_date
union all
select
maq.row_index,
c_ss_supply row_type,
c_row_ss_supply offset,
mss.date1 new_date,
mss.date1 old_date,
sum(mss.number5) new_quantity,
sum(mss.number6) old_quantity
from
msc_form_query mss,
msc_analysis_query maq,
msc_form_query mfq1, -- org-list
msc_form_query mfq2 -- item-list
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index
and mfq1.query_id = g_org_query_id
and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,c_mbp_null_value) = nvl(maq.top_item_id,c_mbp_null_value)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and mss.query_id = g_ss_query_id
and mss.number1 = g_plan_id
and mss.number2 = mfq1.number2
and mss.number3 = mfq1.number3
and mss.number4 = mfq2.number2
and mss.date1 <= g_plan_end_date
group by
maq.row_index,
c_ss_supply,
c_row_ss_supply,
mss.date1,
mss.date1
union all
select
maq.row_index,
c_target_level row_type,
c_row_target_level offset,
nvl(maa.week_start_date, maa.period_start_date) new_date,
nvl(maa.week_start_date, maa.period_start_date) old_date,
avg(maa.target_service_level) new_quantity,
0 old_quantity
from
msc_analysis_aggregate maa,
msc_analysis_query maq,
msc_plan_buckets mpb,
msc_form_query mfq1, -- org-list
msc_form_query mfq2 -- item-list
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index
and mfq1.query_id = g_org_query_id
and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,c_mbp_null_value) = nvl(maq.top_item_id,c_mbp_null_value)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and maa.plan_id = g_plan_id
and maa.sr_instance_id = mfq1.number2
and maa.organization_id = mfq1.number3
and maa.inventory_item_id = mfq2.number2
and maa.record_type = 3
and maa.period_type = 1
and mpb.plan_id = maa.plan_id
and ( (mpb.bucket_type = 2 and maa.week_start_date = mpb.bkt_start_date) or
(mpb.bucket_type = 3 and maa.period_start_date = mpb.bkt_start_date))
group by
maq.row_index,
c_target_level,
c_row_target_level,
nvl(maa.week_start_date, maa.period_start_date),
nvl(maa.week_start_date, maa.period_start_date)
union all
select
maq.row_index,
c_ss_level row_type,
c_row_ss_level offset,
nvl(maa.week_start_date, maa.period_start_date) new_date,
nvl(maa.week_start_date, maa.period_start_date) old_date,
sum(maa.achieved_service_level_qty1)
/ sum(decode(maa.achieved_service_level_qty2,
0, 1, maa.achieved_service_level_qty2)) new_quantity,
0 old_quantity
from
msc_analysis_aggregate maa,
msc_analysis_query maq,
msc_plan_buckets mpb,
msc_form_query mfq1, -- org-list
msc_form_query mfq2 -- item-list
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index
and mfq1.query_id = g_org_query_id
and nvl(mfq1.number1,c_mbp_null_value) = nvl(maq.org_list_id,c_mbp_null_value)
and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,c_mbp_null_value) = nvl(maq.top_item_id,c_mbp_null_value)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and maa.plan_id = g_plan_id
and maa.sr_instance_id = mfq1.number2
and maa.organization_id = mfq1.number3
and maa.inventory_item_id = mfq2.number2
and maa.record_type = 3
and maa.period_type = 1
and mpb.plan_id = maa.plan_id
and ( (mpb.bucket_type = 2 and maa.week_start_date = mpb.bkt_start_date) or
(mpb.bucket_type = 3 and maa.period_start_date = mpb.bkt_start_date))
group by
maq.row_index,
c_target_level,
c_row_target_level,
nvl(maa.week_start_date, maa.period_start_date),
nvl(maa.week_start_date, maa.period_start_date)
order by 1;
select
maq.row_index,
msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) row_type,
msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
md.using_assembly_demand_date) new_date,
msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
md.using_assembly_demand_date) old_date,
sum(decode(md.assembly_demand_comp_date,
null, decode(md.origination_type,
29,(nvl(md.probability,1)* md.using_requirement_quantity),
31, 0,
md.using_requirement_quantity),
decode(md.origination_type,
29,(nvl(md.probability,1)* md.daily_demand_rate),
31, 0,
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)) new_quantity,
sum(nvl(md.original_quantity, md.using_requirement_quantity)) old_quantity
from
msc_demands md,
msc_analysis_query maq,
msc_form_query mfq1, -- region-to-org-list
msc_form_query mfq2, -- item-list
msc_plans mp,
msc_form_query mfq3 --g_plan_bkts_query_id
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index
and mfq1.query_id = g_org_query_id
and mfq1.number1 = nvl(nvl(decode(maq.region_id,
c_global_reg_type, mfq1.number1, maq.region_id),
mfq1.number1), mfq1.number1)
and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and md.plan_id = g_plan_id
and md.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, md.sr_instance_id))
and md.organization_id = nvl(maq.org_id, nvl(mfq1.number3, md.organization_id))
and md.inventory_item_id = mfq2.number2
and nvl(md.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
and md.plan_id = mp.plan_id
and mfq3.query_id = g_plan_bkts_query_id
and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
and md.origination_type in (c_dmd2_net_fcst)
and nvl(maq.region_id, -1) not in (c_global_reg_type, c_local_reg_type)
and ( ( nvl(maq.region_id,-1) = -1
and (md.organization_id = -1
or (nvl(maq.org_id, c_mbp_null_value) = md.organization_id
and (md.original_demand_id is null
or md.original_demand_id in (select demand_id
from msc_demands md2
where md2.plan_id = g_plan_id
and md2.origination_type = c_dmd2_net_fcst
and md2.organization_id = -1
and md2.inventory_item_id = md.inventory_item_id))
)))
or (nvl(maq.region_id,-1) <> -1 and md.organization_id <> -1) )
--and md.organization_id <> -1
group by
maq.row_index,
msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
union all
--for global based demands
select
maq.row_index,
msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) row_type,
msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
md.using_assembly_demand_date) new_date,
msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
md.using_assembly_demand_date) old_date,
sum(decode(md.assembly_demand_comp_date,
null, decode(md.origination_type,
29,(nvl(md.probability,1)* md.using_requirement_quantity),
31, 0,
md.using_requirement_quantity),
decode(md.origination_type,
29,(nvl(md.probability,1)* md.daily_demand_rate),
31, 0,
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)) new_quantity,
sum(nvl(md.original_quantity, md.using_requirement_quantity)) old_quantity
from
msc_demands md,
msc_analysis_query maq,
msc_form_query mfq1, -- region-to-org-list
msc_form_query mfq2, -- item-list
msc_plans mp,
msc_form_query mfq3 --g_plan_bkts_query_id
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index
and mfq1.query_id = g_org_query_id
and mfq1.number1 = nvl(nvl(decode(maq.region_id,
c_global_reg_type, mfq1.number1, maq.region_id),
mfq1.number1), mfq1.number1)
and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and md.plan_id = g_plan_id
and md.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, md.sr_instance_id))
and md.organization_id = nvl(maq.org_id, nvl(mfq1.number3, md.organization_id))
and md.inventory_item_id = mfq2.number2
and nvl(md.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
and md.zone_id is null
and md.plan_id = mp.plan_id
and mfq3.query_id = g_plan_bkts_query_id
and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
and md.origination_type in (c_dmd2_net_fcst)
and nvl(maq.region_id, -1) in (c_global_reg_type)
and ( nvl(maq.org_id,-1) = md.organization_id)
and ( nvl(maq.org_id,-1) = -1
or (md.original_demand_id is null or
md.original_demand_id in (select demand_id
from msc_demands md2
where md2.plan_id = g_plan_id
and md2.origination_type = c_dmd2_net_fcst
and md2.organization_id = -1
and md2.inventory_item_id = md.inventory_item_id)) )
group by
maq.row_index,
msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag,
md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag,
md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date),
msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, md.using_assembly_demand_date)
union all
--for local based demands
select
maq.row_index,
msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, mfq4.number2, md.item_type_id, md.item_type_value, c_row_type_flag,
md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) row_type,
msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, mfq4.number2, md.item_type_id, md.item_type_value, c_offset_flag,
md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id) offset,
msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
decode(mfq4.number2, c_dmd2_manual_fcst, md.firm_date, md.using_assembly_demand_date)) new_date,
msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
decode(mfq4.number2, c_dmd2_manual_fcst, md.firm_date, md.using_assembly_demand_date)) old_date,
decode(mfq4.number2, c_dmd2_manual_fcst, sum(nvl(md.firm_quantity,0)),
(sum(decode(md.assembly_demand_comp_date,
null, decode(md.origination_type,
29,(nvl(md.probability,1)* md.using_requirement_quantity),
31, 0,
md.using_requirement_quantity),
decode(md.origination_type,
29,(nvl(md.probability,1)* md.daily_demand_rate),
31, 0,
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)))) new_quantity,
sum(nvl(md.original_quantity, md.using_requirement_quantity)) old_quantity
from
msc_demands md,
msc_analysis_query maq,
msc_form_query mfq1, -- region-to-org-list
msc_form_query mfq2, -- item-list
msc_form_query mfq4, -- msc_demands duplicate rows
msc_plans mp,
msc_form_query mfq3 --g_plan_bkts_query_id
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index
and mfq1.query_id = g_org_query_id
and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and md.plan_id = g_plan_id
and md.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, md.sr_instance_id))
and md.organization_id = nvl(maq.org_id, nvl(mfq1.number3, md.organization_id))
and md.inventory_item_id = mfq2.number2
--and nvl(md.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
and md.zone_id is null
and md.plan_id = mp.plan_id
and mfq3.query_id = g_plan_bkts_query_id
and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
and md.origination_type in (c_dmd2_net_fcst)
and nvl(maq.region_id, -1) in (c_local_reg_type, -1)
and mfq1.number1 = c_local_reg_type
and md.organization_id <> -1
and ( nvl(maq.region_id, -1) in (-1,c_local_reg_type) )
--and ( nvl(maq.region_id, -1) = c_local_reg_type or (maq.region_id is null and maq.org_id is null) )
and ( (md.original_demand_id is null or
md.original_demand_id not in (select demand_id
from msc_demands md2
where md2.plan_id = g_plan_id
and md2.origination_type = c_dmd2_net_fcst
and md2.organization_id = -1
and md2.inventory_item_id = md.inventory_item_id)) )
and mfq4.query_id = g_md_dup_rows_qid
and mfq4.number1 = md.origination_type
and ((mfq4.number2 = c_dmd2_net_fcst) or (mfq4.number2 = c_dmd2_manual_fcst and firm_date is not null))
group by
maq.row_index,
msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, mfq4.number2, md.item_type_id, md.item_type_value, c_row_type_flag,
md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
msc_sda_pkg.getFcstRowTypeOffset(c_demand_type, mfq4.number2, md.item_type_id, md.item_type_value, c_offset_flag,
md.sr_instance_id, md.organization_id, md.zone_id, md.schedule_designator_id, md.inventory_item_id),
msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
decode(mfq4.number2, c_dmd2_manual_fcst, md.firm_date, md.using_assembly_demand_date)),
msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
decode(mfq4.number2, c_dmd2_manual_fcst, md.firm_date, md.using_assembly_demand_date)),
mfq4.number2
union all
--for region based demands - consumption
select
maq.row_index,
c_drow2_consm_qty row_type,
c_row2_consumed_fcst offset,
msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) new_date,
msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) old_date,
sum(mfu.consumed_qty) new_quantity,
sum(mfu.overconsumption_qty) old_quantity
from
msc_demands md,
msc_analysis_query maq,
msc_form_query mfq1, -- region-to-org-list
msc_form_query mfq2, -- item-list
msc_plans mp,
msc_forecast_updates mfu,
msc_form_query mfq3 --g_plan_bkts_query_id
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index
and mfq1.query_id = g_org_query_id
and mfq1.number1 = nvl(nvl(decode(maq.region_id,
c_global_reg_type, mfq1.number1, maq.region_id),
mfq1.number1), mfq1.number1)
and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and md.plan_id = g_plan_id
and md.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, md.sr_instance_id))
and md.organization_id = nvl(maq.org_id, nvl(mfq1.number3, md.organization_id))
and md.inventory_item_id = mfq2.number2
and nvl(md.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
and md.plan_id = mp.plan_id
and mfq3.query_id = g_plan_bkts_query_id
and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
and md.origination_type in (c_dmd2_net_fcst)
and nvl(maq.region_id, -1) not in (c_global_reg_type, c_local_reg_type)
and ( ( nvl(maq.region_id,-1) = -1
and (md.organization_id = -1
or (nvl(maq.org_id, c_mbp_null_value) = md.organization_id
and (md.original_demand_id is null
or md.original_demand_id in (select demand_id
from msc_demands md2
where md2.plan_id = g_plan_id
and md2.origination_type = c_dmd2_net_fcst
and md2.organization_id = -1
and md2.inventory_item_id = md.inventory_item_id))
)))
or (nvl(maq.region_id,-1) <> -1 and md.organization_id <> -1) )
and md.plan_id = mfu.plan_id
and md.demand_id = mfu.forecast_demand_id
--and md.organization_id <> -1
group by
maq.row_index,
c_drow2_consm_qty,
c_row2_consumed_fcst,
msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date),
msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date)
union all
--for global based demands - consumption
select
maq.row_index,
c_drow2_consm_qty row_type,
c_row2_consumed_fcst offset,
msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) new_date,
msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) old_date,
sum(mfu.consumed_qty) new_quantity,
sum(mfu.overconsumption_qty) old_quantity
from
msc_demands md,
msc_analysis_query maq,
msc_form_query mfq1, -- region-to-org-list
msc_form_query mfq2, -- item-list
msc_plans mp,
msc_forecast_updates mfu,
msc_form_query mfq3 --g_plan_bkts_query_id
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index
and mfq1.query_id = g_org_query_id
and mfq1.number1 = nvl(nvl(decode(maq.region_id,
c_global_reg_type, mfq1.number1, maq.region_id),
mfq1.number1), mfq1.number1)
and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and md.plan_id = g_plan_id
and md.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, md.sr_instance_id))
and md.organization_id = nvl(maq.org_id, nvl(mfq1.number3, md.organization_id))
and md.inventory_item_id = mfq2.number2
and nvl(md.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
and md.zone_id is null
and md.plan_id = mp.plan_id
and mfq3.query_id = g_plan_bkts_query_id
and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
and md.origination_type in (c_dmd2_net_fcst)
and nvl(maq.region_id, -1) in (c_global_reg_type)
and ( nvl(maq.org_id,-1) = md.organization_id)
and ( nvl(maq.org_id,-1) = -1
or (md.original_demand_id is null or
md.original_demand_id in (select demand_id
from msc_demands md2
where md2.plan_id = g_plan_id
and md2.origination_type = c_dmd2_net_fcst
and md2.organization_id = -1
and md2.inventory_item_id = md.inventory_item_id)) )
and md.plan_id = mfu.plan_id
and md.demand_id = mfu.forecast_demand_id
group by
maq.row_index,
c_drow2_consm_qty,
c_row2_consumed_fcst,
msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date),
msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date)
union all
--for local based demands - consumption
select
maq.row_index,
c_drow2_consm_qty row_type,
c_row2_consumed_fcst offset,
msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) new_date,
msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date) old_date,
sum(mfu.consumed_qty) new_quantity,
sum(mfu.overconsumption_qty) old_quantity
from
msc_demands md,
msc_analysis_query maq,
msc_form_query mfq1, -- region-to-org-list
msc_form_query mfq2, -- item-list
msc_plans mp,
msc_forecast_updates mfu,
msc_form_query mfq3 --g_plan_bkts_query_id
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index
and mfq1.query_id = g_org_query_id
and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and md.plan_id = g_plan_id
and md.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, md.sr_instance_id))
and md.organization_id = nvl(maq.org_id, nvl(mfq1.number3, md.organization_id))
and md.inventory_item_id = mfq2.number2
--and nvl(md.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
and md.zone_id is null
and md.plan_id = mp.plan_id
and mfq3.query_id = g_plan_bkts_query_id
and ( trunc(md.using_assembly_demand_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
and md.origination_type in (c_dmd2_net_fcst)
and nvl(maq.region_id, -1) in (c_local_reg_type, -1)
and mfq1.number1 = c_local_reg_type
and md.organization_id <> -1
and ( nvl(maq.region_id, -1) in (-1,c_local_reg_type) )
--and ( nvl(maq.region_id, -1) = c_local_reg_type or (maq.region_id is null and maq.org_id is null) )
and ( (md.original_demand_id is null or
md.original_demand_id not in (select demand_id
from msc_demands md2
where md2.plan_id = g_plan_id
and md2.origination_type = c_dmd2_net_fcst
and md2.organization_id = -1
and md2.inventory_item_id = md.inventory_item_id)) )
and md.plan_id = mfu.plan_id
and md.demand_id = mfu.forecast_demand_id
group by
maq.row_index,
c_drow2_consm_qty,
c_row2_consumed_fcst,
msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date),
msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, mfu.consumption_date)
union all
--for region based supplies
select
maq.row_index,
msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag,
c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) row_type,
msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag,
c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) offset,
msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
ms.new_schedule_date) new_date,
msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
ms.new_schedule_date) old_date,
sum(decode(msi.base_item_id,
null, decode(ms.disposition_status_type,
2, 0,
decode(ms.last_unit_completion_date,
null, ms.new_order_quantity,
ms.daily_rate) ),
decode(ms.last_unit_completion_date,
null, ms.new_order_quantity,
ms.daily_rate) )) new_quantity,
0 old_quantity
from
msc_supplies ms,
msc_analysis_query maq,
msc_form_query mfq1, -- region-to-org-list
msc_form_query mfq2, -- item-list
msc_plans mp,
msc_system_items msi,
msc_form_query mfq3 --g_plan_bkts_query_id
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index
and mfq1.query_id = g_org_query_id
and mfq1.number1 = nvl(nvl(decode(maq.region_id,
c_global_reg_type, mfq1.number1, maq.region_id),
mfq1.number1), mfq1.number1)
and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and ms.plan_id = g_plan_id
and ms.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, ms.sr_instance_id))
and ms.organization_id = nvl(maq.org_id, nvl(mfq1.number3, ms.organization_id))
and ms.inventory_item_id = mfq2.number2
and nvl(ms.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
and ms.plan_id = mp.plan_id
and mfq3.query_id = g_plan_bkts_query_id
and ( trunc(ms.new_schedule_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
and ms.order_type in (c_sup2_rtns_fcst,
c_sup2_rtns_dmd_schd,
c_sup2_rtns_bestfit_fcst)
and nvl(maq.region_id, -1) not in (c_global_reg_type, c_local_reg_type)
and ms.plan_id = msi.plan_id
and ms.sr_instance_id = msi.sr_instance_id
and decode(ms.organization_id,-1, mp.organization_id, ms.organization_id) = msi.organization_id
and ms.inventory_item_id = msi.inventory_item_id
--and ms.organization_id <> -1
and ( ( nvl(maq.region_id,-1) = -1
and (ms.organization_id = -1 or (nvl(maq.org_id, -23453) = ms.organization_id ))
)
or (nvl(maq.region_id,-1) <> -1 and ms.organization_id <> -1)
)
group by
maq.row_index,
msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag,
c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag,
c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date),
msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date)
union all
--for global based supplies
select
maq.row_index,
msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag,
c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) row_type,
msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag,
c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) offset,
msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
ms.new_schedule_date) new_date,
msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
ms.new_schedule_date) old_date,
sum(decode(msi.base_item_id,
null, decode(ms.disposition_status_type,
2, 0,
decode(ms.last_unit_completion_date,
null, ms.new_order_quantity,
ms.daily_rate) ),
decode(ms.last_unit_completion_date,
null, ms.new_order_quantity,
ms.daily_rate) )) new_quantity,
0 old_quantity
from
msc_supplies ms,
msc_analysis_query maq,
msc_form_query mfq1, -- region-to-org-list
msc_form_query mfq2, -- item-list
msc_plans mp,
msc_system_items msi,
msc_form_query mfq3 --g_plan_bkts_query_id
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index
and mfq1.query_id = g_org_query_id
and mfq1.number1 = nvl(nvl(decode(maq.region_id,
c_global_reg_type, mfq1.number1, maq.region_id),
mfq1.number1), mfq1.number1)
and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and ms.plan_id = g_plan_id
and ms.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, ms.sr_instance_id))
and ms.organization_id = nvl(maq.org_id, nvl(mfq1.number3, ms.organization_id))
and ms.inventory_item_id = mfq2.number2
and nvl(ms.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
and ms.zone_id is null
and ms.plan_id = mp.plan_id
and mfq3.query_id = g_plan_bkts_query_id
and ( trunc(ms.new_schedule_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
and ms.order_type in (c_sup2_rtns_fcst,
c_sup2_rtns_dmd_schd,
c_sup2_rtns_bestfit_fcst)
and nvl(maq.region_id, -1) in (c_global_reg_type)
and ( nvl(maq.org_id,-1) = ms.organization_id)
and ms.plan_id = msi.plan_id
and ms.sr_instance_id = msi.sr_instance_id
and decode(ms.organization_id,-1, mp.organization_id, ms.organization_id) = msi.organization_id
and ms.inventory_item_id = msi.inventory_item_id
group by
maq.row_index,
msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag,
c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag,
c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date),
msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date, ms.new_schedule_date)
union all
--for local based supplies
select
maq.row_index,
msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, mfq4.number2, ms.item_type_id, ms.item_type_value, c_row_type_flag,
c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) row_type,
msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, mfq4.number2, ms.item_type_id, ms.item_type_value, c_offset_flag,
c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value) offset,
msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
decode(mfq4.number2, c_sup2_rtns_manual_fcst, ms.firm_date, ms.new_schedule_date)) new_date,
msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
decode(mfq4.number2, c_sup2_rtns_manual_fcst, ms.firm_date, ms.new_schedule_date)) old_date,
sum( decode(mfq4.number2, c_sup2_rtns_manual_fcst, nvl(ms.firm_quantity,0),
decode(msi.base_item_id,
null, decode(ms.disposition_status_type,
2, 0,
decode(ms.last_unit_completion_date,
null, ms.new_order_quantity,
ms.daily_rate) ),
decode(ms.last_unit_completion_date,
null, ms.new_order_quantity,
ms.daily_rate) ))) new_quantity,
0 old_quantity
from
msc_supplies ms,
msc_analysis_query maq,
msc_form_query mfq1, -- region-to-org-list
msc_form_query mfq2, -- item-list
msc_form_query mfq4, -- msc_supplies duplicate rows
msc_plans mp,
msc_system_items msi,
msc_form_query mfq3 --g_plan_bkts_query_id
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index
and mfq1.query_id = g_org_query_id
and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
and nvl(mfq1.number2, c_mbp_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_null_value))
and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and ms.plan_id = g_plan_id
and ms.sr_instance_id = nvl(maq.inst_id, nvl(mfq1.number2, ms.sr_instance_id))
and ms.organization_id = nvl(maq.org_id, nvl(mfq1.number3, ms.organization_id))
and ms.inventory_item_id = mfq2.number2
--and nvl(ms.zone_id, c_mbp_null_value) = nvl(mfq1.number1, c_mbp_null_value)
and ms.zone_id is null
and ms.plan_id = mp.plan_id
and mfq3.query_id = g_plan_bkts_query_id
and ( trunc(ms.new_schedule_date) between trunc(mfq3.date1) and trunc(mfq3.date2) )
and ms.order_type in (c_sup2_rtns_fcst,
c_sup2_rtns_dmd_schd,
c_sup2_rtns_bestfit_fcst)
and nvl(maq.region_id, -1) in (c_local_reg_type, -1)
and mfq1.number1 = c_local_reg_type
and ms.organization_id <> -1
and ( nvl(maq.region_id, -1) in (-1,c_local_reg_type) )
--and ( nvl(maq.region_id, -1) = c_local_reg_type or (maq.region_id is null and maq.org_id is null) )
and ms.plan_id = msi.plan_id
and ms.sr_instance_id = msi.sr_instance_id
and decode(ms.organization_id,-1, mp.organization_id, ms.organization_id) = msi.organization_id
and ms.inventory_item_id = msi.inventory_item_id
and mfq4.query_id = g_ms_dup_rows_qid
and mfq4.number1 = ms.order_type
and ((mfq4.number2 = c_sup2_rtns_fcst) or (mfq4.number2 = c_sup2_rtns_manual_fcst and firm_date is not null))
group by
maq.row_index,
msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, mfq4.number2, ms.item_type_id, ms.item_type_value, c_row_type_flag,
c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
msc_sda_pkg.getFcstRowTypeOffset(c_supply_type, mfq4.number2, ms.item_type_id, ms.item_type_value, c_offset_flag,
c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value, c_mbp_null_value),
msc_sda_pkg.getFcstStartDate(mfq3.date1, mp.curr_start_date, mp.curr_cutoff_date,
decode(mfq4.number2, c_sup2_rtns_manual_fcst, ms.firm_date, ms.new_schedule_date)),
msc_sda_pkg.getFcstEndDate(mfq3.date2, mp.curr_start_date, mp.curr_cutoff_date,
decode(mfq4.number2, c_sup2_rtns_manual_fcst, ms.firm_date, ms.new_schedule_date))
order by 1;
select min(date1), max(date2)
from msc_form_query
where query_id = g_hist_cal_query_id;
select date1, date2
from msc_form_query
where query_id = g_hist_cal_query_id
order by 1;
select
maq.row_index,
msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_row_type_flag) row_type,
msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_offset_flag) offset,
msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, mmhv.anchor_date) new_date,
msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, mmhv.anchor_date) old_date,
sum(mmhv.quantity) new_quantity,
0 old_quantity
from
msc_msd_history_v mmhv,
msc_analysis_query maq,
msc_form_query mfq1, -- region-to-org-list
msc_form_query mfq2, -- item-list
msc_form_query mfq3 -- history calendar
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index
and mfq1.query_id = g_org_query_id
and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
and nvl(mfq1.number2, c_mbp_not_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_not_null_value))
and nvl(mfq1.number3, -1) = nvl(maq.org_id, nvl(mfq1.number3, -1))
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and nvl(mmhv.sr_instance_id, c_mbp_not_null_value) = nvl(maq.inst_id, nvl(mfq1.number2, c_mbp_not_null_value))
and nvl(mmhv.organization_id, -1) = nvl(maq.org_id, nvl(mfq1.number3, -1))
and mmhv.inventory_item_id = mfq2.number2
and nvl(mmhv.zone_id, c_local_reg_type) = mfq1.number1
and mfq3.query_id = g_hist_cal_query_id
and trunc(mmhv.anchor_date) between mfq3.date1 and mfq3.date2
group by
maq.row_index,
msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_row_type_flag),
msc_sda_pkg.getHistRowTypeOffset(mmhv.row_type, c_mbp_null_value, c_mbp_null_value, c_offset_flag),
msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, mmhv.anchor_date),
msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, mmhv.anchor_date)
order by 1;
select
maq.row_index,
msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag) row_type,
msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag) offset,
msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, ms.new_schedule_date) new_date,
msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, ms.new_schedule_date) old_date,
sum(ms.new_order_quantity) new_quantity,
0 old_quantity
from
msc_supplies ms,
msc_analysis_query maq,
msc_form_query mfq1, -- region-to-org-list
msc_form_query mfq2, -- item-list
msc_form_query mfq3 -- history calendar
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index
and mfq1.query_id = g_org_query_id
and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and ms.plan_id = g_plan_id
and ms.sr_instance_id = nvl(maq.inst_id, mfq1.number2)
and ms.organization_id = nvl(maq.org_id, mfq1.number3)
and ms.inventory_item_id = mfq2.number2
and nvl(ms.zone_id, c_local_reg_type) = mfq1.number1
and mfq3.query_id = g_hist_cal_query_id
and trunc(ms.new_schedule_date) between mfq3.date1 and mfq3.date2
and ms.order_type = c_returns_hist
group by
maq.row_index,
msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_row_type_flag),
msc_sda_pkg.getHistRowTypeOffset(ms.order_type, ms.item_type_id, ms.item_type_value, c_offset_flag),
msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, ms.new_schedule_date),
msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, ms.new_schedule_date)
union all
select
maq.row_index,
msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag) row_type,
msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag) offset,
msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, md.using_assembly_demand_date) new_date,
msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, md.using_assembly_demand_date) old_date,
sum(md.using_requirement_quantity) new_quantity,
0 old_quantity
from
msc_demands md,
msc_analysis_query maq,
msc_form_query mfq1, -- region-to-org-list
msc_form_query mfq2, -- item-list
msc_form_query mfq3 -- history calendar
where maq.query_id = p_query_id
and maq.parent_row_index = g_next_rowset_index
and mfq1.query_id = g_org_query_id
and mfq1.number1 = nvl(maq.region_id, mfq1.number1)
and mfq1.number2 = nvl(maq.inst_id, mfq1.number2)
and mfq1.number3 = nvl(maq.org_id, mfq1.number3)
and mfq2.query_id = g_chain_query_id
and nvl(mfq2.number1,mfq2.number2) = nvl(maq.top_item_id, mfq2.number2)
and mfq2.number2 = nvl(maq.item_id, mfq2.number2)
and md.plan_id = g_plan_id
and md.sr_instance_id = nvl(maq.inst_id, mfq1.number2)
and md.organization_id = nvl(maq.org_id, mfq1.number3)
and md.inventory_item_id = mfq2.number2
and nvl(md.zone_id, md.schedule_designator_id, c_local_reg_type) = mfq1.number1
and mfq3.query_id = g_hist_cal_query_id
and trunc(md.using_assembly_demand_date) between mfq3.date1 and mfq3.date2
and md.origination_type = c_dmd_hist
group by
maq.row_index,
msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_row_type_flag),
msc_sda_pkg.getHistRowTypeOffset(md.origination_type, md.item_type_id, md.item_type_value, c_offset_flag),
msc_sda_pkg.getHistStartDate(mfq3.date1, l_min_date, l_max_date, md.using_assembly_demand_date),
msc_sda_pkg.getHistEndDate(mfq3.date2, l_min_date, l_max_date, md.using_assembly_demand_date)
order by 1;
select distinct
mfq.number1 org_list_id,
mfq.char1 org_list,
mfq.number2 inst_id,
mfq.number3 org_id,
mfq.char4 org_code,
mfq.number4 sort_column
from msc_form_query mfq
where mfq.query_id = g_org_query_id
order by sort_column;
select distinct
number1 top_item_id,
char1 top_item_name,
number2 item_id,
char2 item_name,
number3 sort_column
from msc_form_query
where query_id = g_chain_query_id
order by sort_column desc;
insert into msc_analysis_query
(query_id, row_index, parent_row_index,
org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
top_item_id, top_item_name, item_id, item_name, top_item_name_state)
values (p_query_id, g_row_index, g_next_rowset_index,
c_orgs.org_list_id, c_orgs.org_list, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, l_orglist_action,
l_row.top_item_id, l_row.top_item_name, l_row.item_id, l_row.item_name, l_itemlist_action);
insert into msc_analysis_query
(query_id, row_index, parent_row_index,
org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
top_item_id, top_item_name, item_id, item_name, top_item_name_state)
values (p_query_id, g_row_index, g_next_rowset_index,
l_row.org_list_id, l_row.org_list, l_row.inst_id, l_row.org_id, l_row.org_code, l_orglist_action,
c_item.top_item_id, c_item.top_item_name, c_item.item_id, c_item.item_name, l_itemlist_action);
select distinct
mfq.number2 region_id,
mfq.char2 region_code,
mfq.number3 sort_column
from msc_form_query mfq
where mfq.query_id = g_region_query_id
and ( (p_view_type = c_fcstview)
or (p_view_type = c_histview and mfq.number2 <> c_global_reg_type))
order by sort_column desc;
select distinct
mfq.number2 inst_id,
mfq.number3 org_id,
mfq.char1 org_code,
mfq.number4 sort_column
from msc_form_query mfq
where mfq.query_id = g_org_query_id
and mfq.number3 <> -1
and ( (p_view_type = c_fcstview and nvl(p_region_id, c_mbp_null_value) <> c_local_reg_type)
or (p_view_type = c_fcstview and nvl(p_region_id, c_mbp_null_value) = c_local_reg_type
and nvl(mfq.number2,c_mbp_null_value) <> c_mbp_null_value)
or (p_view_type = c_histview and nvl(mfq.number2,c_mbp_null_value) <> c_mbp_null_value))
order by sort_column;
select distinct
number1 top_item_id,
char1 top_item_name,
number2 item_id,
char2 item_name,
number3 sort_column
from msc_form_query
where query_id = g_chain_query_id
order by sort_column desc;
insert into msc_analysis_query
(query_id, row_index, parent_row_index,
region_list_id, region_list, region_id, region_code, region_list_state,
org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
top_item_id, top_item_name, item_id, item_name, top_item_name_state)
values (p_query_id, g_row_index, g_next_rowset_index,
l_row.region_list_id, l_row.region_list, c_regs.region_id, c_regs.region_code, l_reglist_action,
l_row.org_list_id, l_row.org_list, l_row.inst_id, l_row.org_id, l_row.org_code, l_orglist_action,
l_row.top_item_id, l_row.top_item_name, l_row.item_id, l_row.item_name, l_itemlist_action);
insert into msc_analysis_query
(query_id, row_index, parent_row_index,
region_list_id, region_list, region_id, region_code, region_list_state,
org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
top_item_id, top_item_name, item_id, item_name, top_item_name_state)
values (p_query_id, g_row_index, g_next_rowset_index,
l_row.region_list_id, l_row.region_list, l_row.region_id, l_row.region_code, l_reglist_action,
l_row.org_list_id, l_row.org_list, c_orgs.inst_id, c_orgs.org_id, c_orgs.org_code, l_orglist_action,
l_row.top_item_id, l_row.top_item_name, l_row.item_id, l_row.item_name, l_itemlist_action);
insert into msc_analysis_query
(query_id, row_index, parent_row_index,
region_list_id, region_list, region_id, region_code, region_list_state,
org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
top_item_id, top_item_name, item_id, item_name, top_item_name_state)
values (p_query_id, g_row_index, g_next_rowset_index,
l_row.region_list_id, l_row.region_list, l_row.region_id, l_row.region_code, l_reglist_action,
l_row.org_list_id, l_row.org_list, l_row.inst_id, l_row.org_id, l_row.org_code, l_orglist_action,
c_item.top_item_id, c_item.top_item_name, c_item.item_id, c_item.item_name, l_itemlist_action);
insert into msc_analysis_query
(query_id, row_index, parent_row_index, org_list_id, org_list, inst_id, org_id, org_code,
top_item_id, top_item_name, item_id, item_name, org_list_state, top_item_name_state)
values (g_sd_query_id, c_first_row_index, g_next_rowset_index, ll_org_list_id, ll_org_list, ll_inst_id, ll_org_id, ll_org_code,
ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name, ll_orglist_action, ll_itemlist_action);
insert into msc_analysis_query
(query_id, row_index, parent_row_index,
region_list_id, region_list, region_id, region_code,region_list_state,
org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
top_item_id, top_item_name, item_id, item_name, top_item_name_state)
values (g_fcst_query_id, c_first_row_index, g_next_rowset_index,
c_all_region_type, c_all_region_type_text, to_number(null), null, c_collapsed_state,
c_all_org_type, c_all_org_type_text, to_number(null), to_number(null), null, c_collapsed_state,
ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name, ll_itemlist_action);
insert into msc_analysis_query
(query_id, row_index, parent_row_index,
region_list_id, region_list, region_id, region_code,region_list_state,
org_list_id, org_list, inst_id, org_id, org_code, org_list_state,
top_item_id, top_item_name, item_id, item_name, top_item_name_state)
values (g_hist_query_id, c_first_row_index, g_next_rowset_index,
c_all_region_type, c_all_region_type_text, to_number(null), null, c_collapsed_state,
c_all_org_type, c_all_org_type_text, to_number(null), to_number(null), null, c_collapsed_state,
ll_top_item_id, ll_top_item_name, ll_item_id, ll_item_name, ll_itemlist_action);
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
values (g_md_dup_rows_qid, sysdate, -1, -1, sysdate, c_dmd2_net_fcst, c_dmd2_net_fcst);
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
values (g_md_dup_rows_qid, sysdate, -1, -1, sysdate, c_dmd2_net_fcst, c_dmd2_manual_fcst);
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
values (g_ms_dup_rows_qid, sysdate, -1, -1, sysdate, c_sup2_rtns_fcst, c_sup2_rtns_fcst);
insert into msc_form_query (query_id, creation_date, created_by, last_updated_by, last_update_date, number1, number2)
values (g_ms_dup_rows_qid, sysdate, -1, -1, sysdate, c_sup2_rtns_fcst, c_sup2_rtns_manual_fcst);
select date1
from msc_form_query
where query_id = g_hist_cal_query_id
order by 1;
select count(*)
from msc_form_query
where query_id = g_hist_cal_query_id;
insert into msc_form_query (query_id, creation_date, created_by,
last_updated_by, last_update_date,
date1, date2, number1)
select g_hist_cal_query_id, sysdate, -1, -1, sysdate, start_date, end_date,1
from (
select mpsd.period_start_date start_date, mpsd.next_date-1 end_date
from msc_trading_partners mtp,
msc_period_start_dates mpsd
where l_cal_type = c_owning_org_cal
and mpsd.calendar_code = mtp.calendar_code
and mpsd.sr_instance_id = mtp.sr_instance_id
and mpsd.exception_set_id = mtp.calendar_exception_set_id
and mtp.sr_instance_id = g_owning_inst_id
and mtp.sr_tp_id = g_owning_org_id
and mtp.partner_type =3
and mpsd.period_start_date >= g_pref_hist_start_date
and mpsd.period_start_date <= g_plan_start_date
union all
select mpsd.period_start_date start_date, mpsd.next_date-1 end_date
from msc_period_start_dates mpsd
where l_cal_type = c_profile_cal
and mpsd.calendar_code = l_cal_code
and mpsd.period_start_date >= g_pref_hist_start_date
and mpsd.period_start_date <= g_plan_start_date
order by 1);
procedure update_pref_set (p_name varchar2, p_desc varchar2,
p_days number, p_weeks number, p_periods number,
p_factor number, p_decimal_places number,
p_sd_row_list varchar2, p_fcst_row_list varchar2) is
begin
msc_sda_utils.update_pref_set(p_name, p_desc, p_days, p_weeks, p_periods,
p_factor, p_decimal_places, p_sd_row_list, p_fcst_row_list);
end update_pref_set;
procedure update_close_settings (p_event varchar2, p_event_list varchar2) is
begin
msc_sda_utils.update_close_settings(p_event, p_event_list);
end update_close_settings;
select distinct
number2 region_id
from msc_form_query
where query_id = g_region_query_id;
select distinct
'('||number2||','||number3||')' org_id
from msc_form_query
where query_id = g_org_query_id
and number2 is not null
and number3 is not null;
select distinct
number2 item_id
from msc_form_query
where query_id = g_chain_query_id;
p_from_table := 'msc_forecast_updates';
p_from_table := 'msc_forecast_updates';
if (p_from_table = 'msc_forecast_updates') then
return;
sql_stmt := 'insert into msc_form_query ('||
' query_id, last_update_date, last_updated_by, creation_date, created_by,number1) '||
' select distinct '|| p_mfq_id ||', sysdate, 1, sysdate, 1, ';
elsif (p_from_table in ('msc_demands', 'msc_forecast_updates') ) then
sql_stmt := sql_stmt || ' demand_id from '|| p_from_table;
elsif (p_from_table in ('msc_demands', 'msc_forecast_updates') ) then
sql_stmt := sql_stmt || ' and origination_type in ('|| l_order_type_list ||')';
msc_sda_utils.println('msc_demands, msc_forecast_updates '||sql_stmt);