[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select enforce_budget_constraints, budget_id, budget_value
into l_enforce_budget_constraints, l_budget_id, l_budget_value
from msc_plans
where plan_id=p_plan_id;
select plan_type, sr_instance_id, organization_id, plan_start_date, plan_cutoff_date
into l_plan_type, l_sr_instance_id, l_organization_id, l_plan_start_date, l_plan_cutoff_date
from msc_plan_runs
where plan_id=p_plan_id
and plan_run_id=p_plan_run_id;
insert into msc_budgets_f (
plan_id,
plan_run_id,
budget_level,
sr_instance_id,
organization_id,
category_set_id,
category_instance_id,
sr_category_id,
budget_value,
budget_value2,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id)
select
p_plan_id,
p_plan_run_id,
t.budget_level,
t.sr_instance_id,
t.organization_id,
t.category_set_id,
t.category_instance_id,
t.sr_category_id,
t.budget_value,
t.budget_value * decode(t.currency_code,
fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),
1, nvl(mcc.conv_rate,0)) budget_value2,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
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
-- budget_level: 0:plan 1:org 2:cat 3:org-cat
decode(sign(b.organization_id), -1,
decode(sign(b.sr_category_id), -1, 0, 2),
decode(sign(b.sr_category_id), -1, 1, 3)) budget_level,
decode(b.sr_instance_id, -1, -23453, b.sr_instance_id) sr_instance_id,
decode(b.organization_id, -1, -23453, b.organization_id) organization_id,
nvl(mtp.currency_code, l_owning_currency_code) currency_code,
fnd_profile.value('MSR_BUDGET_CATEGORY_SET') category_set_id,
b.category_instance_id,
b.sr_category_id,
b.budget_value
from
msc_inventory_budget_values b,
msc_trading_partners mtp
where budget_id=l_budget_id
and b.sr_instance_id=mtp.sr_instance_id(+)
and b.organization_id=mtp.sr_tp_id(+)
and mtp.partner_type(+)=3
) t,
msc_currency_conv_mv mcc
where mcc.from_currency(+)=t.currency_code
and mcc.calendar_date(+)=l_plan_start_date
and mcc.to_currency(+)=fnd_profile.value('MSC_HUB_CUR_CODE_RPT');
insert into msc_budgets_f (
plan_id,
plan_run_id,
budget_level,
sr_instance_id,
organization_id,
category_set_id,
category_instance_id,
sr_category_id,
budget_value,
budget_value2,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id)
select
p_plan_id,
p_plan_run_id,
0,
to_number(-23453),
to_number(-23453),
null,
-1,
-1,
budget_value,
budget_value * decode(currency_code,
fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),
1, nvl(mcc.conv_rate,0)) budget_value2,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
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
l_budget_value budget_value,
l_owning_currency_code currency_code
from dual), -- inline table for outer join
msc_currency_conv_mv mcc
where mcc.from_currency(+)=currency_code
and mcc.calendar_date(+)=l_plan_start_date
and mcc.to_currency(+)=fnd_profile.value('MSC_HUB_CUR_CODE_RPT');
delete from msc_st_budgets_f where st_transaction_id=p_st_transaction_id;
' insert into msc_st_budgets_f('||
' st_transaction_id,'||
' error_code,'||
' sr_instance_id,'||
' organization_id,'||
' category_set_id,'||
' category_instance_id,'||
' sr_category_id,'||
' organization_code,'||
' category_instance_code,'||
' category_name,'||
' budget_level,'||
' budget_value,'||
' budget_value2,'||
' created_by, creation_date,'||
' last_updated_by, last_update_date, last_update_login'||
' )'||
' select'||
' :p_st_transaction_id,'||
' 0,'||
' f.sr_instance_id,'||
' f.organization_id,'||
' f.category_set_id,'||
' f.category_instance_id,'||
' f.sr_category_id,'||
' mtp.organization_code,'||
' mai.instance_code category_instance_code,'||
' c.category_name,'||
' f.budget_level,'||
' f.budget_value,'||
' f.budget_value2,'||
' fnd_global.user_id, sysdate,'||
' fnd_global.user_id, sysdate, fnd_global.login_id'||
' from'||
' '||l_apps_schema||'.msc_budgets_f'||l_suffix||' f,'||
' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
' '||l_apps_schema||'.msc_phub_categories_mv'||l_suffix||' c,'||
' '||l_apps_schema||'.msc_apps_instances'||l_suffix||' mai'||
' where f.plan_run_id=:p_plan_run_id'||
' and mtp.partner_type(+)=3'||
' and mtp.sr_instance_id(+)=f.sr_instance_id'||
' and mtp.sr_tp_id(+)=f.organization_id'||
' and c.sr_instance_id(+)=f.category_instance_id'||
' and c.sr_category_id(+)=f.sr_category_id'||
' and c.category_set_id(+)=f.category_set_id'||
' and mai.instance_id(+)=f.category_instance_id';
delete from msc_budgets_f
where plan_id=p_plan_id and plan_run_id=p_plan_run_id;
msc_phub_util.log('msc_phub_budget_pkg.import_budgets_f: insert into msc_budgets_f');
insert into msc_budgets_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
category_set_id,
category_instance_id,
sr_category_id,
budget_level,
budget_value,
budget_value2,
created_by, creation_date,
last_updated_by, last_update_date, last_update_login
)
select
p_plan_id,
p_plan_run_id,
nvl(sr_instance_id, -1),
nvl(organization_id, -1),
nvl(category_set_id, -23453),
nvl(category_instance_id, -1),
nvl(sr_category_id, -1),
budget_level,
budget_value,
budget_value2,
fnd_global.user_id, sysdate,
fnd_global.user_id, sysdate, fnd_global.login_id
from msc_st_budgets_f
where st_transaction_id=p_st_transaction_id and error_code=0;
msc_phub_util.log('msc_phub_budget_pkg.import_budgets_f: inserted='||sql%rowcount);