[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select o.currency_code
into l_owning_currency_code
from msc_trading_partners o,
msd_dem_app_instance_orgs daio,
msd_dp_ascp_scenarios_v p
where o.partner_type=3
and o.sr_instance_id=decode(sign(p.sr_instance_id), -1, o.sr_instance_id, p.sr_instance_id)
and o.organization_code=daio.organization_code
and daio.organization_id=fnd_profile.value('MSD_DEM_MASTER_ORG')
and p.scenario_id=p_plan_id;
insert into msc_demantra_f (
ods_plan_id,
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
customer_id,
customer_site_id,
region_id,
inventory_item_id,
demand_class,
owning_org_id,
owning_inst_id,
start_date,
aggr_type, category_set_id, sr_category_id,
consensus_fcst,
consensus_fcst_value,
consensus_fcst_value2,
consensus_fcst_cum,
priority,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
to_number(-1) ods_plan_id,
t.scenario_id plan_id,
p_plan_run_id,
decode(t.organization_id, -1, -23453, t.sr_instance_id) sr_instance_id, --wei: sync sr_instance_id with organization_id
decode(t.organization_id, -1, -23453, t.organization_id) organization_id,
t.customer_id,
t.customer_site_id,
mpc.region_id,
t.inventory_item_id,
t.demand_class,
decode(t.organization_id,
-1, msc_hub_calendar.get_item_org(-1, t.inventory_item_id, t.sr_instance_id),
t.organization_id) owning_org_id,
t.sr_instance_id owning_inst_id,
t.start_date,
to_number(0) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
t.consensus_fcst,
t.consensus_fcst*t.price consensus_fcst_value,
t.consensus_fcst*t.price * decode(t.currency_code,
fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))
consensus_fcst_value2,
t.consensus_fcst_cum,
t.priority,
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 (
select
k.scenario_id,
k.sr_instance_id,
k.organization_id,
k.customer_id,
k.customer_site_id,
k.zone_id,
k.inventory_item_id,
k.demand_class,
k.start_date,
nvl(f.currency_code, l_owning_currency_code) currency_code,
f.price,
f.consensus_fcst,
sum(nvl(f.consensus_fcst, 0)) over(partition by
k.scenario_id, k.sr_instance_id, k.organization_id,
k.customer_id, k.customer_site_id, k.zone_id,
k.inventory_item_id, k.demand_class
order by k.start_date) consensus_fcst_cum,
f.priority
from
(select distinct
k1.scenario_id,
k1.sr_instance_id,
k1.organization_id,
k1.customer_id,
k1.customer_site_id,
k1.zone_id,
k1.inventory_item_id,
k1.demand_class,
k2.start_date
from msd_dem_scn_entries_v k1,
(select distinct start_date
from msd_dem_scn_entries_v
where scenario_id=p_plan_id) k2
where k1.scenario_id=p_plan_id) k,
msd_dem_scn_entries_v f
where k.scenario_id = f.scenario_id(+)
and k.sr_instance_id = f.sr_instance_id(+)
and k.organization_id = f.organization_id(+)
and k.customer_id = f.customer_id(+)
and k.customer_site_id = f.customer_site_id(+)
and k.zone_id = f.zone_id(+)
and k.inventory_item_id = f.inventory_item_id(+)
and k.demand_class = f.demand_class(+)
and k.start_date = f.start_date(+)) t,
msc_currency_conv_mv mcc,
msc_phub_customers_mv mpc
where mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
and mcc.from_currency(+) = t.currency_code
and mcc.calendar_date(+) = t.start_date
and mpc.customer_id = t.customer_id
and mpc.customer_site_id = t.customer_site_id
and mpc.region_id = decode(t.customer_id, -23453, t.zone_id, mpc.region_id);
insert into msc_demantra_f (
ods_plan_id, plan_id, plan_run_id,
sr_instance_id, organization_id, owning_org_id, owning_inst_id,
inventory_item_id,
customer_id, customer_site_id, region_id,
demand_class, start_date,
aggr_type, category_set_id, sr_category_id,
consensus_fcst,
consensus_fcst_value,
consensus_fcst_value2,
consensus_fcst_cum,
priority,
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.ods_plan_id, f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id, f.owning_org_id, f.owning_inst_id,
to_number(-23453) inventory_item_id,
f.customer_id, f.customer_site_id, f.region_id,
f.demand_class, f.start_date,
to_number(42) aggr_type,
l_category_set_id1 category_set_id,
nvl(q.sr_category_id, -23453),
sum(f.consensus_fcst),
sum(f.consensus_fcst_value),
sum(f.consensus_fcst_value2),
sum(f.consensus_fcst_cum),
avg(f.priority),
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_demantra_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.ods_plan_id, f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id, f.owning_org_id, f.owning_inst_id,
f.customer_id, f.customer_site_id, f.region_id,
f.demand_class, f.start_date,
nvl(q.sr_category_id, -23453);
delete from msc_demantra_f
where plan_id = p_plan_id
and plan_run_id = nvl(p_plan_run_id, plan_run_id);