[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT c.projfunc_cost_exchange_rate
,c.projfunc_rev_exchange_rate
,c.project_cost_exchange_rate
,c.project_rev_exchange_rate
FROM pa_fp_txn_currencies c
,pa_proj_fp_options fp
WHERE fp.fin_plan_version_id = p_budget_version_id
AND fp.fin_plan_version_id = c.fin_plan_version_id
AND fp.proj_fp_options_id = c.proj_fp_options_id
AND c.txn_currency_code = p_txn_currency_code ;
VARCHAR2 DEFAULT 'UPDATE_PLAN_TRANSACTION' -- Added for bug#5395732
,x_return_status OUT NOCOPY --File.Sql.39 bug 4440895
VARCHAR2
,x_msg_count OUT NOCOPY --File.Sql.39 bug 4440895
NUMBER
,x_msg_data OUT NOCOPY --File.Sql.39 bug 4440895
VARCHAR2) IS
l_converted_amount NUMBER;
SELECT ppa.name project_name
,pt.name task_name
,prl.alias resource_name
FROM pa_projects_all ppa
,pa_proj_elements pt
,pa_resource_list_members prl
,pa_resource_assignments pra
WHERE pra.resource_assignment_id = p_resource_assignment_id
AND ppa.project_id = pra.project_id
AND pt.proj_element_id(+) = pra.task_id
AND prl.resource_list_member_id(+) = pra.resource_list_member_id;
pa_debug.g_err_stage := to_char(l_stage)||': No records selected -- Returning';
pa_debug.g_err_stage := to_char(l_stage)||': Records selected '||to_char(l_tab_count);
IF (p_calling_module IN ('BUDGET_GENERATION','FORECAST_GENERATION','UPDATE_PLAN_TRANSACTION','AMG_API')) THEN --Bug 9586291 --Bug 16448547
l_converted_amount := GL_CURRENCY_API.convert_closest_amount_sql
( x_from_currency => p_txn_currency_code_tab(i)
,x_to_currency => p_projfunc_currency_code_tab(i)
,x_conversion_date => p_projfunc_cost_rate_date_tab(i)
,x_conversion_type => p_projfunc_cost_rate_type_tab(i)
,x_user_rate => 1
,x_amount => p_txn_raw_cost_tab(i)
,x_max_roll_days => -1) ;
IF (p_calling_module IN ('BUDGET_GENERATION','FORECAST_GENERATION','UPDATE_PLAN_TRANSACTION','AMG_API')) THEN --Bug 9586291 --Bug 16448547
l_converted_amount := GL_CURRENCY_API.convert_closest_amount_sql
( x_from_currency => p_txn_currency_code_tab(i)
,x_to_currency => p_projfunc_currency_code_tab(i)
,x_conversion_date => p_projfunc_cost_rate_date_tab(i)
,x_conversion_type => p_projfunc_cost_rate_type_tab(i)
,x_user_rate => 1
,x_amount => p_txn_burdened_cost_tab(i)
,x_max_roll_days => -1) ;
IF (p_calling_module IN ('BUDGET_GENERATION','FORECAST_GENERATION','UPDATE_PLAN_TRANSACTION','AMG_API')) THEN --Bug 9586291 --Bug 16448547
l_converted_amount := GL_CURRENCY_API.convert_closest_amount_sql
( x_from_currency => p_txn_currency_code_tab(i)
,x_to_currency => p_projfunc_currency_code_tab(i)
,x_conversion_date => p_projfunc_rev_rate_date_tab(i)
,x_conversion_type => p_projfunc_rev_rate_type_tab(i)
,x_user_rate => 1
,x_amount => p_txn_revenue_tab(i)
,x_max_roll_days => -1) ;
IF (p_calling_module IN ('BUDGET_GENERATION','FORECAST_GENERATION','UPDATE_PLAN_TRANSACTION','AMG_API')) THEN --Bug 9586291 --Bug 16448547
l_converted_amount := GL_CURRENCY_API.convert_closest_amount_sql
( x_from_currency => p_txn_currency_code_tab(i)
,x_to_currency => p_proj_currency_code_tab(i)
,x_conversion_date => p_proj_cost_rate_date_tab(i)
,x_conversion_type => p_proj_cost_rate_type_tab(i)
,x_user_rate => 1
,x_amount => p_txn_raw_cost_tab(i)
,x_max_roll_days => -1) ;
IF (p_calling_module IN ('BUDGET_GENERATION','FORECAST_GENERATION','UPDATE_PLAN_TRANSACTION','AMG_API')) THEN --Bug 9586291 --Bug 16448547
l_converted_amount := GL_CURRENCY_API.convert_closest_amount_sql
( x_from_currency => p_txn_currency_code_tab(i)
,x_to_currency => p_proj_currency_code_tab(i)
,x_conversion_date => p_proj_cost_rate_date_tab(i)
,x_conversion_type => p_proj_cost_rate_type_tab(i)
,x_user_rate => 1
,x_amount => p_txn_burdened_cost_tab(i)
,x_max_roll_days => -1) ;
IF (p_calling_module IN ('BUDGET_GENERATION','FORECAST_GENERATION','UPDATE_PLAN_TRANSACTION','AMG_API')) THEN --Bug 9586291 --Bug 16448547
l_converted_amount := GL_CURRENCY_API.convert_closest_amount_sql
( x_from_currency => p_txn_currency_code_tab(i)
,x_to_currency => p_proj_currency_code_tab(i)
,x_conversion_date => p_proj_rev_rate_date_tab(i)
,x_conversion_type => p_proj_rev_rate_type_tab(i)
,x_user_rate => 1
,x_amount => p_txn_revenue_tab(i)
,x_max_roll_days => -1) ;
,p_calling_module IN VARCHAR2 DEFAULT 'UPDATE_PLAN_TRANSACTION'-- Added for Bug#5395732
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
l_txn_row_id_tab pa_fp_multi_currency_pkg.rowid_type_tab;
select v.project_id
,v.fin_plan_type_id
,o.projfunc_cost_rate_type
,o.projfunc_cost_rate_date_type
,o.projfunc_cost_rate_date
,o.projfunc_rev_rate_type
,o.projfunc_rev_rate_date_type
,o.projfunc_rev_rate_date
,o.project_cost_rate_type
,o.project_cost_rate_date_type
,o.project_cost_rate_date
,o.project_rev_rate_type
,o.project_rev_rate_date_type
,o.project_rev_rate_date
from pa_proj_fp_options o
,pa_budget_versions v
where v.budget_version_id = p_budget_version_id
and o.project_id = v.project_id
and nvl(o.fin_plan_type_id,0) = nvl(v.fin_plan_type_id,0)
and o.fin_plan_version_id = v.budget_version_id;
select segment1
,project_currency_code
,projfunc_currency_code
from pa_projects_all
where project_id = g_project_id;
select a.rowid
,a.resource_assignment_id
,a.start_date
,a.end_date
,a.txn_currency_code
,a.txn_raw_cost
,a.txn_burdened_cost
,a.txn_revenue
,nvl(a.projfunc_currency_code,g_projfunc_currency_code)
,nvl(a.projfunc_cost_rate_type,g_projfunc_cost_rate_type)
,DECODE(a.projfunc_cost_exchange_rate,null,
DECODE(nvl(a.projfunc_cost_rate_type,g_projfunc_cost_rate_type),'User',
--get_fp_cur_details( p_budget_version_id,a.txn_currency_code,'COST','PROJFUNC' ),
-9999,
a.projfunc_cost_exchange_rate),
a.projfunc_cost_exchange_rate)
projfunc_cost_exchange_rate
,DECODE(nvl(a.projfunc_cost_rate_type,g_projfunc_cost_rate_type),'User',NULL,
nvl(a.projfunc_cost_rate_date_type,g_projfunc_cost_rate_date_type))
,DECODE(nvl(a.projfunc_cost_rate_date_type,
g_projfunc_cost_rate_date_type),
'START_DATE',a.start_date,
'END_DATE' ,a.end_date,
nvl(a.projfunc_cost_rate_date,g_projfunc_cost_rate_date))
projfunc_cost_rate_date
,nvl(a.projfunc_rev_rate_type,g_projfunc_rev_rate_type)
,DECODE(a.projfunc_rev_exchange_rate,null,
DECODE(nvl(a.projfunc_rev_rate_type,g_projfunc_rev_rate_type),'User',
-9999, ---c.projfunc_rev_exchange_rate,
a.projfunc_rev_exchange_rate),
a.projfunc_rev_exchange_rate)
projfunc_rev_exchange_rate
,DECODE(nvl(a.projfunc_rev_rate_type,g_projfunc_rev_rate_type),'User',NULL,
nvl(a.projfunc_rev_rate_date_type,g_projfunc_rev_rate_date_type))
,DECODE(nvl(a.projfunc_rev_rate_date_type,g_projfunc_rev_rate_date_type),
'START_DATE',a.start_date,
'END_DATE' ,a.end_date,
nvl(a.projfunc_rev_rate_date,g_projfunc_rev_rate_date))
projfunc_rev_rate_date
,nvl(a.project_currency_code,g_proj_currency_code)
,nvl(a.project_cost_rate_type,g_proj_cost_rate_type)
,DECODE(a.project_cost_exchange_rate,null,
DECODE(nvl(a.project_cost_rate_type,g_proj_cost_rate_type),'User',
-9999, --c.project_cost_exchange_rate,
a.project_cost_exchange_rate),
a.project_cost_exchange_rate)
project_cost_exchange_rate
,DECODE(nvl(a.project_cost_rate_type,g_proj_cost_rate_type),'User',NULL,
nvl(a.project_cost_rate_date_type,g_proj_cost_rate_date_type))
,DECODE(nvl(a.project_cost_rate_date_type,g_proj_cost_rate_date_type),
'START_DATE',a.start_date,
'END_DATE' ,a.end_date,
nvl(a.project_cost_rate_date,g_proj_cost_rate_date))
project_cost_rate_date
,nvl(a.project_rev_rate_type,g_proj_rev_rate_type)
,DECODE(a.project_rev_exchange_rate,null,
DECODE(nvl(a.project_rev_rate_type,g_proj_rev_rate_type),'User',
-9999, --c.project_rev_exchange_rate,
a.project_rev_exchange_rate),
a.project_rev_exchange_rate)
project_rev_exchange_rate
,DECODE(nvl(a.project_rev_rate_type,g_proj_rev_rate_type),'User',NULL,
nvl(a.project_rev_rate_date_type,g_proj_rev_rate_date_type))
,DECODE(nvl(a.project_rev_rate_date_type,g_proj_rev_rate_date_type),
'START_DATE',a.start_date,
'END_DATE' ,a.end_date,
nvl(a.project_rev_rate_date,g_proj_rev_rate_date))
project_rev_rate_date
/* Perf Bug: 3683132 */
,get_fp_cur_details( p_budget_version_id,a.txn_currency_code,'COST','PROJFUNC' ) fp_cur_projfunc_cost_rate
,get_fp_cur_details( p_budget_version_id,a.txn_currency_code,'REV','PROJFUNC' ) fp_cur_projfunc_rev_rate
,get_fp_cur_details( p_budget_version_id,a.txn_currency_code,'COST','PROJECT' ) fp_cur_project_cost_rate
,get_fp_cur_details( p_budget_version_id,a.txn_currency_code,'REV','PROJECT' ) fp_cur_project_rev_rate
/* Bug fix:4259098 */
,a.init_quantity
,a.txn_init_raw_cost
,a.txn_init_burdened_cost
,a.txn_init_revenue
,a.init_raw_cost
,a.init_burdened_cost
,a.init_revenue
,a.project_init_raw_cost
,a.project_init_burdened_cost
,a.project_init_revenue
from pa_budget_lines a
,pa_budget_versions bv
--,pa_fp_txn_currencies c
where a.budget_version_id = p_budget_version_id
and bv.budget_version_id = a.budget_version_id
and EXISTS (select null
from pa_resource_assignments b
where b.resource_assignment_id = a.resource_assignment_id
and b.budget_version_id = a.budget_version_id
)
/** Perf Bug: 3683132 a.budget_version_id = c.fin_plan_version_id (+)
and a.txn_currency_code = c.txn_currency_code (+)
and a.resource_assignment_id in (select b.resource_assignment_id
from pa_resource_assignments b
where b.resource_assignment_id =
a.resource_assignment_id
and b.budget_version_id =
p_budget_version_id)
**/
and (((NVL(p_source_context,'BUDGET_VERSION') = 'BUDGET_LINE')
and a.budget_line_id = p_budget_line_id)
OR
(NVL(p_source_context,'BUDGET_VERSION') <> 'BUDGET_LINE')
)
/* Bug fix: 4085192 Select all budget lines only on or after the ETC STart date, if ETC date is populated */
AND ((bv.ETC_START_DATE IS NULL)
OR (bv.ETC_START_DATE IS NOT NULL
AND ((a.start_date > bv.ETC_START_DATE )
OR (bv.ETC_START_DATE between a.start_date and a.end_date)
)
)
)
order by a.resource_assignment_id,
a.start_date,
a.txn_currency_code;
select r.rowid
,r.resource_assignment_id
,r.start_date
,r.end_date
,r.txn_currency_code
,nvl(r.txn_raw_cost,0)
,nvl(r.txn_burdened_cost,0)
,nvl(r.txn_revenue,0)
,nvl(r.projfunc_currency_code,g_projfunc_currency_code)
,nvl(r.projfunc_cost_rate_type,g_projfunc_cost_rate_type)
,DECODE(r.projfunc_cost_exchange_rate,null,
DECODE(nvl(r.projfunc_cost_rate_type,g_projfunc_cost_rate_type),'User',
-9999, --c.projfunc_cost_exchange_rate,
r.projfunc_cost_exchange_rate),
r.projfunc_cost_exchange_rate)
projfunc_cost_exchange_rate
,DECODE(nvl(r.projfunc_cost_rate_type,g_projfunc_cost_rate_type),'User',Null,
nvl(r.projfunc_cost_rate_date_type,g_projfunc_cost_rate_date_type))
,DECODE(nvl(r.projfunc_cost_rate_date_type,
g_projfunc_cost_rate_date_type),
'START_DATE',r.start_date,
'END_DATE' ,r.end_date,
nvl(r.projfunc_cost_rate_date,g_projfunc_cost_rate_date))
projfunc_cost_rate_date
,nvl(r.projfunc_rev_rate_type,g_projfunc_rev_rate_type)
,DECODE(r.projfunc_rev_exchange_rate,null,
DECODE(nvl(r.projfunc_rev_rate_type,g_projfunc_rev_rate_type),'User',
-9999, ---c.projfunc_rev_exchange_rate,
r.projfunc_rev_exchange_rate),
r.projfunc_rev_exchange_rate)
projfunc_rev_exchange_rate
,DECODE(nvl(r.projfunc_rev_rate_type,g_projfunc_rev_rate_type),'User',NULL,
nvl(r.projfunc_rev_rate_date_type,g_projfunc_rev_rate_date_type))
,DECODE(nvl(r.projfunc_rev_rate_date_type,g_projfunc_rev_rate_date_type),
'START_DATE',r.start_date,
'END_DATE' ,r.end_date,
nvl(r.projfunc_rev_rate_date,g_projfunc_rev_rate_date))
projfunc_rev_rate_date
,nvl(r.project_currency_code,g_proj_currency_code)
,nvl(r.project_cost_rate_type,g_proj_cost_rate_type)
,DECODE(r.project_cost_exchange_rate,null,
DECODE(nvl(r.project_cost_rate_type,g_proj_cost_rate_type),'User',
-9999, --c.project_cost_exchange_rate,
r.project_cost_exchange_rate),
r.project_cost_exchange_rate)
project_cost_exchange_rate
,DECODE(nvl(r.project_cost_rate_type,g_proj_cost_rate_type),'User',NULL,
nvl(r.project_cost_rate_date_type,g_proj_cost_rate_date_type))
,DECODE(nvl(r.project_cost_rate_date_type,g_proj_cost_rate_date_type),
'START_DATE',r.start_date,
'END_DATE' ,r.end_date,
nvl(r.project_cost_rate_date,g_proj_cost_rate_date))
project_cost_rate_date
,nvl(r.project_rev_rate_type,g_proj_rev_rate_type)
,DECODE(r.project_rev_exchange_rate,null,
DECODE(nvl(r.project_rev_rate_type,g_proj_rev_rate_type),'User',
-9999, --c.project_rev_exchange_rate,
r.project_rev_exchange_rate),
r.project_rev_exchange_rate)
project_rev_exchange_rate
,DECODE(nvl(r.project_rev_rate_type,g_proj_rev_rate_type),'User',NULL,
nvl(r.project_rev_rate_date_type,g_proj_rev_rate_date_type))
,DECODE(nvl(r.project_rev_rate_date_type,g_proj_rev_rate_date_type),
'START_DATE',r.start_date,
'END_DATE' ,r.end_date,
nvl(r.project_rev_rate_date,g_proj_rev_rate_date))
project_rev_rate_date
/* Perf Bug: 3683132 */
,get_fp_cur_details( p_budget_version_id,r.txn_currency_code,'COST','PROJFUNC' ) fp_cur_projfunc_cost_rate
,get_fp_cur_details( p_budget_version_id,r.txn_currency_code,'REV','PROJFUNC' ) fp_cur_projfunc_rev_rate
,get_fp_cur_details( p_budget_version_id,r.txn_currency_code,'COST','PROJECT' ) fp_cur_project_cost_rate
,get_fp_cur_details( p_budget_version_id,r.txn_currency_code,'REV','PROJECT' ) fp_cur_project_rev_rate
/* Bug fix:4259098 */
,r.init_quantity
,r.txn_init_raw_cost
,r.txn_init_burdened_cost
,r.txn_init_revenue
,r.init_raw_cost
,r.init_burdened_cost
,r.init_revenue
,r.project_init_raw_cost
,r.project_init_burdened_cost
,r.project_init_revenue
from pa_fp_rollup_tmp r
--,pa_fp_txn_currencies c
where nvl(r.delete_flag,'N') = 'N'
/** Perf Bug: 3683132 and p_budget_version_id = c.fin_plan_version_id (+)
and r.txn_currency_code = c.txn_currency_code (+)
**/
order by r.resource_assignment_id,
r.start_date,
r.txn_currency_code;
l_txn_row_id_tab.delete;
l_resource_assignment_id_tab.delete;
l_start_date_tab.delete;
l_end_date_tab.delete;
l_txn_currency_code_tab.delete;
l_txn_raw_cost_tab.delete;
l_txn_burdened_cost_tab.delete;
l_txn_revenue_tab.delete;
l_projfunc_currency_code_tab.delete;
l_projfunc_cost_rate_type_tab.delete;
l_projfunc_cost_rate_tab.delete;
l_projfunc_cost_rt_dt_typ_tab.delete;
l_projfunc_cost_rate_date_tab.delete;
l_projfunc_rev_rate_type_tab.delete;
l_projfunc_rev_rate_tab.delete;
l_projfunc_rev_rt_dt_typ_tab.delete;
l_projfunc_rev_rate_date_tab.delete;
l_projfunc_raw_cost_tab.delete;
l_projfunc_burdened_cost_tab.delete;
l_projfunc_revenue_tab.delete;
l_projfunc_rejection_tab.delete;
l_proj_currency_code_tab.delete;
l_proj_cost_rate_type_tab.delete;
l_proj_cost_rate_tab.delete;
l_proj_cost_rt_dt_typ_tab.delete;
l_proj_cost_rate_date_tab.delete;
l_proj_rev_rate_type_tab.delete;
l_proj_rev_rate_tab.delete;
l_proj_rev_rt_dt_typ_tab.delete;
l_proj_rev_rate_date_tab.delete;
l_proj_raw_cost_tab.delete;
l_proj_burdened_cost_tab.delete;
l_proj_revenue_tab.delete;
l_proj_rejection_tab.delete;
l_user_validate_flag_tab.delete;
l_status_flag_tab.delete;
l_fp_cur_projfunc_cost_rt_tab.delete;
l_fp_cur_projfunc_rev_rt_tab.delete;
l_fp_cur_project_cost_rt_tab.delete;
l_fp_cur_project_rev_rt_tab.delete;
l_init_quantity_tab.delete;
l_txn_init_raw_cost_tab.delete;
l_txn_init_burden_cost_tab.delete;
l_txn_init_revenue_tab.delete;
l_pfc_init_raw_cost_tab.delete;
l_pfc_init_burden_cost_tab.delete;
l_pfc_init_revenue_tab.delete;
l_proj_init_raw_cost_tab.delete;
l_proj_init_burden_cost_tab.delete;
l_proj_init_revenue_tab.delete;
UPDATE pa_budget_lines
SET projfunc_currency_code = l_projfunc_currency_code_tab(i)
,projfunc_cost_rate_type = l_projfunc_cost_rate_type_tab(i)
,projfunc_cost_exchange_rate = l_projfunc_cost_rate_tab(i)
,projfunc_cost_rate_date_type = l_projfunc_cost_rt_dt_typ_tab(i)
,projfunc_cost_rate_date = DECODE(l_projfunc_cost_rt_dt_typ_tab(i),
'FIXED_DATE',l_projfunc_cost_rate_date_tab(i),
NULL)
,projfunc_rev_rate_type = l_projfunc_rev_rate_type_tab(i)
,projfunc_rev_exchange_rate = l_projfunc_rev_rate_tab(i)
,projfunc_rev_rate_date_type = l_projfunc_rev_rt_dt_typ_tab(i)
,projfunc_rev_rate_date = DECODE(l_projfunc_rev_rt_dt_typ_tab(i),
'FIXED_DATE',l_projfunc_rev_rate_date_tab(i),
NULL)
,raw_cost = l_projfunc_raw_cost_tab(i)
,burdened_cost = l_projfunc_burdened_cost_tab(i)
,revenue = l_projfunc_revenue_tab(i)
,pfc_cur_conv_rejection_code = l_projfunc_rejection_tab(i)
,project_currency_code = l_proj_currency_code_tab(i)
,project_cost_rate_type = l_proj_cost_rate_type_tab(i)
,project_cost_exchange_rate = l_proj_cost_rate_tab(i)
,project_cost_rate_date_type = l_proj_cost_rt_dt_typ_tab(i)
,project_cost_rate_date = DECODE(l_proj_cost_rt_dt_typ_tab(i),
'FIXED_DATE',l_proj_cost_rate_date_tab(i),
NULL)
,project_rev_rate_type = l_proj_rev_rate_type_tab(i)
,project_rev_exchange_rate = l_proj_rev_rate_tab(i)
,project_rev_rate_date_type = l_proj_rev_rt_dt_typ_tab(i)
,project_rev_rate_date = DECODE(l_proj_rev_rt_dt_typ_tab(i),
'FIXED_DATE',l_proj_rev_rate_date_tab(i),
NULL)
,project_raw_cost = l_proj_raw_cost_tab(i)
,project_burdened_cost = l_proj_burdened_cost_tab(i)
,project_revenue = l_proj_revenue_tab(i)
,pc_cur_conv_rejection_code = l_proj_rejection_tab(i)
WHERE rowid = l_txn_row_id_tab(i);
UPDATE pa_fp_rollup_tmp
SET projfunc_currency_code = l_projfunc_currency_code_tab(i)
,projfunc_cost_rate_type = l_projfunc_cost_rate_type_tab(i)
,projfunc_cost_exchange_rate = l_projfunc_cost_rate_tab(i)
,projfunc_cost_rate_date_type = l_projfunc_cost_rt_dt_typ_tab(i)
,projfunc_cost_rate_date = DECODE(l_projfunc_cost_rt_dt_typ_tab(i),
'FIXED_DATE',l_projfunc_cost_rate_date_tab(i),
NULL)
,projfunc_rev_rate_type = l_projfunc_rev_rate_type_tab(i)
,projfunc_rev_exchange_rate = l_projfunc_rev_rate_tab(i)
,projfunc_rev_rate_date_type = l_projfunc_rev_rt_dt_typ_tab(i)
,projfunc_rev_rate_date = DECODE(l_projfunc_rev_rt_dt_typ_tab(i),
'FIXED_DATE',l_projfunc_rev_rate_date_tab(i),
NULL)
,projfunc_raw_cost = l_projfunc_raw_cost_tab(i)
,projfunc_burdened_cost = l_projfunc_burdened_cost_tab(i)
,projfunc_revenue = l_projfunc_revenue_tab(i)
,pfc_cur_conv_rejection_code = l_projfunc_rejection_tab(i)
,project_currency_code = l_proj_currency_code_tab(i)
,project_cost_rate_type = l_proj_cost_rate_type_tab(i)
,project_cost_exchange_rate = l_proj_cost_rate_tab(i)
,project_cost_rate_date_type = l_proj_cost_rt_dt_typ_tab(i)
,project_cost_rate_date = DECODE(l_proj_cost_rt_dt_typ_tab(i),
'FIXED_DATE',l_proj_cost_rate_date_tab(i),
NULL)
,project_rev_rate_type = l_proj_rev_rate_type_tab(i)
,project_rev_exchange_rate = l_proj_rev_rate_tab(i)
,project_rev_rate_date_type = l_proj_rev_rt_dt_typ_tab(i)
,project_rev_rate_date = DECODE(l_proj_rev_rt_dt_typ_tab(i),
'FIXED_DATE',l_proj_rev_rate_date_tab(i),
NULL)
,project_raw_cost = l_proj_raw_cost_tab(i)
,project_burdened_cost = l_proj_burdened_cost_tab(i)
,project_revenue = l_proj_revenue_tab(i)
,pc_cur_conv_rejection_code = l_proj_rejection_tab(i)
WHERE rowid = l_txn_row_id_tab(i);
The parameters p_bls_inserted_after_id will be used only
when p_calling_context is CHANGE_ORDER_MERGE
p_bls_inserted_after_id : This value will be used to find out the budget lines that
got inserted in this flow. All the budget lines with
1. budget line id > p_bls_inserted_after_id AND
2. budget_Version_id = p_budget_version_id
will be considered as inserted in this flow.
Tracking bug No: 4035856 Rravipat Initial creation
==============================================================================*/
PROCEDURE Round_Budget_Line_Amounts(
p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
,p_bls_inserted_after_id IN pa_budget_lines.budget_line_id%TYPE DEFAULT NULL
,p_calling_context IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
AS
-- variables used for debugging
l_return_status VARCHAR2(1);
SELECT pbl.ROWID, pbl.resource_assignment_id,pbl.start_date, pbl.quantity,
pbl.raw_cost, pbl.burdened_cost, pbl.revenue,pbl.projfunc_currency_code,
pbl.project_raw_cost, pbl.project_burdened_cost, pbl.project_revenue, pbl.project_currency_code,
pbl.txn_raw_cost, pbl.txn_burdened_cost, pbl.txn_revenue, pbl.txn_currency_code, pra.rate_based_flag
FROM pa_budget_lines pbl,
pa_resource_assignments pra
WHERE pbl.budget_version_id = p_budget_version_id
AND pra.resource_assignment_id=pbl.resource_assignment_id
ORDER BY pbl.txn_currency_code;
SELECT pbl.ROWID, pbl.resource_assignment_id,pbl.start_date, pbl.quantity,
pbl.raw_cost, pbl.burdened_cost, pbl.revenue,pbl.projfunc_currency_code,
pbl.project_raw_cost, pbl.project_burdened_cost, pbl.project_revenue, pbl.project_currency_code,
pbl.txn_raw_cost, pbl.txn_burdened_cost, pbl.txn_revenue, pbl.txn_currency_code, pra.rate_based_flag
FROM pa_budget_lines pbl,
pa_resource_assignments pra
WHERE pbl.budget_version_id = p_budget_version_id
AND pra.resource_assignment_id=pbl.resource_assignment_id
AND budget_line_id > p_bls_inserted_after_id
ORDER BY pbl.txn_currency_code;
nvl(p_bls_inserted_after_id,0) <= 0 THEN
IF l_debug_mode = 'Y' THEN
pa_debug.g_err_stage:='p_bls_inserted_after_id = '||p_bls_inserted_after_id;
p_value2 => 'p_bls_inserted_after_id '||p_bls_inserted_after_id);
SELECT version_type
INTO l_version_type
FROM pa_budget_versions
WHERE budget_version_id=p_budget_version_id;
l_txn_row_id_tab.delete;
l_resource_assignment_id_tab.delete;
l_start_date_tab.delete;
l_quantity_tab.delete;
l_projfunc_raw_cost_tab.delete;
l_projfunc_burdened_cost_tab.delete;
l_projfunc_revenue_tab.delete;
l_projfunc_currency_code_tab.delete;
l_proj_raw_cost_tab.delete;
l_proj_burdened_cost_tab.delete;
l_proj_revenue_tab.delete;
l_proj_currency_code_tab.delete;
l_txn_raw_cost_tab.delete;
l_txn_burdened_cost_tab.delete;
l_txn_revenue_tab.delete;
l_txn_currency_code_tab.delete;
l_rate_based_flag_tab.delete;--Bug 4052403
l_projfunc_raw_cost_tmp_tab.delete; -- Added for bug 4290451.
l_prjfnc_burdened_cost_tmp_tab.delete; -- Added for bug 4290451.
l_projfunc_revenue_tmp_tab.delete; -- Added for bug 4290451.
l_proj_raw_cost_tmp_tab.delete; -- Added for bug 4290451.
l_proj_burdened_cost_tmp_tab.delete; -- Added for bug 4290451.
l_proj_revenue_tmp_tab.delete; -- Added for bug 4290451.
l_pfc_tmp_tab.delete; -- Added for bug 4290451.
l_pc_tmp_tab.delete; -- Added for bug 4290451.
UPDATE PA_BUDGET_LINES
SET --While deriving the override rates below, the expression used here for calculating quantity is used.
--Hence whenever this derivation is changed, the change has to be reflected below in override rate
--derivation also
quantity = Decode(l_rate_based_flag_tab(i),
'N',Decode(l_version_type,
'REVENUE',l_txn_revenue_tab(i),
l_txn_raw_cost_tab(i)),
round(l_quantity_tab(i),5))
,txn_raw_cost = l_txn_raw_cost_tab(i)
,txn_burdened_cost = l_txn_burdened_cost_tab(i)
,txn_revenue = l_txn_revenue_tab(i)
,txn_cost_rate_override = Decode(Decode(l_rate_based_flag_tab(i),
'N',Decode(l_version_type,
'REVENUE',l_txn_revenue_tab(i),
l_txn_raw_cost_tab(i)),
round(l_quantity_tab(i),5)),
null, null,
0,0,
l_txn_raw_cost_tab(i)/(Decode(l_rate_based_flag_tab(i),
'N',Decode(l_version_type,
'REVENUE',l_txn_revenue_tab(i),
l_txn_raw_cost_tab(i)),
round(l_quantity_tab(i),5))))
,burden_cost_rate_override = Decode(Decode(l_rate_based_flag_tab(i),
'N',Decode(l_version_type,
'REVENUE',l_txn_revenue_tab(i),
l_txn_raw_cost_tab(i)),
round(l_quantity_tab(i),5)),
null, null,
0,0,
l_txn_burdened_cost_tab(i)/(Decode(l_rate_based_flag_tab(i),
'N',Decode(l_version_type,
'REVENUE',l_txn_revenue_tab(i),
l_txn_raw_cost_tab(i)),
round(l_quantity_tab(i),5))))
,txn_bill_rate_override = Decode(Decode(l_rate_based_flag_tab(i),
'N',Decode(l_version_type,
'REVENUE',l_txn_revenue_tab(i),
l_txn_raw_cost_tab(i)),
round(l_quantity_tab(i),5)),
null, null,
0,0,
l_txn_revenue_tab(i)/(Decode(l_rate_based_flag_tab(i),
'N',Decode(l_version_type,
'REVENUE',l_txn_revenue_tab(i),
l_txn_raw_cost_tab(i)),
round(l_quantity_tab(i),5))))
WHERE rowid = l_txn_row_id_tab(i);
UPDATE PA_BUDGET_LINES
SET quantity = Decode(l_rate_based_flag_tab(i),
'N',Decode(l_version_type,
'REVENUE',l_txn_revenue_tab(i),
l_txn_raw_cost_tab(i)),
round(l_quantity_tab(i),5))
,raw_cost = l_projfunc_raw_cost_tab(i)
,burdened_cost = l_projfunc_burdened_cost_tab(i)
,revenue = l_projfunc_revenue_tab(i)
,project_raw_cost = l_proj_raw_cost_tab(i)
,project_burdened_cost = l_proj_burdened_cost_tab(i)
,project_revenue = l_proj_revenue_tab(i)
,txn_raw_cost = l_txn_raw_cost_tab(i)
,txn_burdened_cost = l_txn_burdened_cost_tab(i)
,txn_revenue = l_txn_revenue_tab(i)
,txn_cost_rate_override = Decode(Decode(l_rate_based_flag_tab(i),
'N',Decode(l_version_type,
'REVENUE',l_txn_revenue_tab(i),
l_txn_raw_cost_tab(i)),
round(l_quantity_tab(i),5)),
null, null,
0,0,
l_txn_raw_cost_tab(i)/(Decode(l_rate_based_flag_tab(i),
'N',Decode(l_version_type,
'REVENUE',l_txn_revenue_tab(i),
l_txn_raw_cost_tab(i)),
round(l_quantity_tab(i),5))))
,burden_cost_rate_override = Decode(Decode(l_rate_based_flag_tab(i),
'N',Decode(l_version_type,
'REVENUE',l_txn_revenue_tab(i),
l_txn_raw_cost_tab(i)),
round(l_quantity_tab(i),5)),
null, null,
0,0,
l_txn_burdened_cost_tab(i)/(Decode(l_rate_based_flag_tab(i),
'N',Decode(l_version_type,
'REVENUE',l_txn_revenue_tab(i),
l_txn_raw_cost_tab(i)),
round(l_quantity_tab(i),5))))
,txn_bill_rate_override = Decode(Decode(l_rate_based_flag_tab(i),
'N',Decode(l_version_type,
'REVENUE',l_txn_revenue_tab(i),
l_txn_raw_cost_tab(i)),
round(l_quantity_tab(i),5)),
null, null,
0,0,
l_txn_revenue_tab(i)/(Decode(l_rate_based_flag_tab(i),
'N',Decode(l_version_type,
'REVENUE',l_txn_revenue_tab(i),
l_txn_raw_cost_tab(i)),
round(l_quantity_tab(i),5))))
,projfunc_rev_exchange_rate = Decode(projfunc_rev_rate_type, 'User',
Decode(l_txn_revenue_tab(i), null, null, 0,0, l_projfunc_revenue_tab(i)/l_txn_revenue_tab(i))
,null)
,project_cost_exchange_rate = Decode(project_cost_rate_type, 'User',
Decode(l_txn_raw_cost_tab(i), null, null, 0,0, l_proj_raw_cost_tab(i)/l_txn_raw_cost_tab(i))
,null)
,project_rev_exchange_rate = Decode(project_rev_rate_type, 'User',
Decode(l_txn_revenue_tab(i), null, null, 0,0, l_proj_revenue_tab(i)/l_txn_revenue_tab(i))
,null)
WHERE rowid = l_txn_row_id_tab(i);