[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Delete_Ver_Exc_PVT EXCEPTION;
select locked_by_person_id
from pa_budget_versions
where budget_version_id = p_budget_version_id;
/* been updated by someone else already */
PA_FIN_PLAN_UTILS.Check_Record_Version_Number
(p_unique_index => p_budget_version_id,
p_record_version_number => p_record_version_number,
x_valid_flag => l_valid_flag,
x_return_status => l_return_status,
x_error_msg_code => l_error_msg_code);
update pa_budget_versions
set locked_by_person_id = l_person_id,
record_version_number = p_record_version_number + 1,
last_update_date=SYSDATE,
last_updated_by=FND_GLOBAL.user_id,
last_update_login=FND_GLOBAL.login_id
where budget_version_id = p_budget_version_id;
update pa_budget_versions
set locked_by_person_id = null,
record_version_number = p_record_version_number + 1,
last_update_date=SYSDATE,
last_updated_by=FND_GLOBAL.user_id,
last_update_login=FND_GLOBAL.login_id
where budget_version_id = p_budget_version_id;
select nvl(locked_by_person_id, -1)
into l_locked_by_person_id
from pa_budget_versions
where budget_version_id = p_budget_version_id;
update pa_budget_versions
set locked_by_person_id = l_person_id,
record_version_number = p_record_version_number + 1,
last_update_date=SYSDATE,
last_updated_by=FND_GLOBAL.user_id,
last_update_login=FND_GLOBAL.login_id
where budget_version_id = p_budget_version_id;
update pa_budget_versions
set locked_by_person_id = null,
record_version_number = p_record_version_number + 1,
last_update_date=SYSDATE,
last_updated_by=FND_GLOBAL.user_id,
last_update_login=FND_GLOBAL.login_id
where budget_version_id = p_budget_version_id;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 1
FROM pa_budget_lines pbl
WHERE pbl.budget_version_id = p_budget_version_id
AND( pbl.cost_rejection_code IS NOT NULL
OR pbl.revenue_rejection_code IS NOT NULL
OR pbl.burden_rejection_code IS NOT NULL
OR pbl.other_rejection_code IS NOT NULL
OR pbl.pc_cur_conv_rejection_code IS NOT NULL
OR pbl.pfc_cur_conv_rejection_code IS NOT NULL));
select bv.budget_type_code,
bv.resource_list_id,
bv.version_type,
pt.project_type_class_code,
bv.approved_rev_plan_type_flag,
bv.approved_cost_plan_type_flag,
DECODE(bv.version_type,
'COST',opt.cost_time_phased_code,
'REVENUE',opt.revenue_time_phased_code,
opt.all_time_phased_code),
DECODE(bv.version_type,
'COST',opt.cost_fin_plan_level_code,
'REVENUE',opt.revenue_fin_plan_level_code,
opt.all_fin_plan_level_code),
bv.budget_entry_method_code,
bv.pm_product_code,
/* bv.created_by, Commented for bug 6176649 */
opt.fin_plan_type_id,
pavl.plan_class_code,
nvl(pr.baseline_funding_flag,'N')
into l_budget_type_code,
l_resource_list_id,
l_version_type,
l_project_type_class_code,
l_ar_flag,
l_ac_flag,
l_time_phased_type_code,
l_fin_plan_level_code,
l_budget_entry_method_code,
l_pm_product_code,
/* l_created_by, Commented for bug 6176649 */
l_fin_plan_type_id,
l_fin_plan_class_code,
l_auto_baseline_project
from pa_project_types_all pt,
pa_projects_all pr,
pa_budget_versions bv,
pa_proj_fp_options opt,
pa_fin_plan_types_b pavl
where bv.budget_version_id = p_budget_version_id and
opt.fin_plan_version_id = bv.budget_version_id and
bv.project_id = pr.project_id and
pr.project_type = pt.project_type and
--nvl(pr.org_id,-99) = nvl(pt.org_id,-99) and --Bug 5374346
pr.org_id = pt.org_id and
opt.fin_plan_type_id = pavl.fin_plan_type_id and
opt.fin_plan_option_level_code = 'PLAN_VERSION';
select entry_level_code
into l_entry_level_code
from pa_budget_entry_methods
where budget_entry_method_code = l_budget_entry_method_code;
pa_billing_core.update_funding (p_project_id,
l_funding_level,
x_err_code,
x_err_stage,
x_err_stack);
pa_billing_core.update_funding(p_project_id,
l_funding_level, -- Funding level
x_err_code,
x_err_stage,
x_err_stack);
update pa_budget_versions
set last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id,
budget_status_code = PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_WORKING, -- bug 3978894 'W',
record_version_number = record_version_number+1
where budget_version_id = p_budget_version_id;
UPDATE pa_budget_versions
SET last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id,
record_version_number = record_version_number+1,
rev_partially_impl_flag='N'
WHERE project_id = p_project_id
AND ci_id IS NOT NULL
AND rev_partially_impl_flag='Y';
pa_debug.g_err_stage := 'No of records updated '||SQL%ROWCOUNT;
SELECT migration_code
,uncategorized_flag
INTO l_migration_code, l_uncategorized_flag
FROM pa_resource_lists_all_bg
WHERE resource_list_id = l_resource_list_id;
update pa_budget_versions
set last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id,
current_flag = 'N',
record_version_number = record_version_number+1
where budget_version_id=p_orig_budget_version_id;
pa_debug.g_err_stage := 'After Copy Version-- Firing Select';
SELECT ci_id, inclusion_method_code, version_type, creation_date
BULK COLLECT INTO l_temp_ci_id_tbl, l_tmp_incl_method_code_tbl, l_version_type_tbl, l_cw_creation_date_tbl
FROM pa_fp_merged_ctrl_items
WHERE plan_version_id = p_budget_version_id
AND project_id = p_project_id;
SELECT creation_date
BULK COLLECT INTO l_bl_creation_date_tbl
FROM pa_fp_merged_ctrl_items
WHERE plan_version_id = l_target_version_id
AND project_id = p_project_id;
UPDATE pa_fp_merged_ctrl_items
SET inclusion_method_code = l_tmp_incl_method_code_tbl(i),
creation_date = l_cw_creation_date_tbl(i),
last_update_login = FND_GLOBAL.login_id,
last_updated_by = FND_GLOBAL.user_id,
last_update_date = SYSDATE
WHERE plan_version_id = l_target_version_id
AND project_id = p_project_id
AND ci_id = l_temp_ci_id_tbl(i)
AND version_type = l_version_type_tbl(i);
UPDATE pa_fp_merged_ctrl_items
SET inclusion_method_code = 'COPIED',
creation_date = l_bl_creation_date_tbl(i),
last_update_login = FND_GLOBAL.login_id,
last_updated_by = FND_GLOBAL.user_id,
last_update_date = SYSDATE
WHERE plan_version_id = p_budget_version_id
AND project_id = p_project_id
AND ci_id = l_temp_ci_id_tbl(i)
AND version_type = l_version_type_tbl(i);
select start_date,completion_date
into v_project_start_date,
v_project_completion_date
from pa_projects_all
where project_id = p_project_id;
update pa_budget_lines
set start_date= v_project_start_date,
end_date = v_project_completion_date
where resource_assignment_id in
(select resource_assignment_id
from pa_resource_assignments
where budget_version_id = l_target_version_id)
and ((start_date <> v_project_start_date) OR (end_date <> v_project_completion_date));
select start_date,completion_date
into v_project_start_date,
v_project_completion_date
from pa_projects_all
where project_id = p_project_id;
for bl_rec in (select start_date,
completion_date ,
resource_assignment_id
from pa_tasks t ,pa_resource_assignments r
where t.task_id = r.task_id and
r.budget_version_id = l_target_version_id)
loop
bl_rec.start_date := nvl(bl_rec.start_date,v_project_start_date);
update pa_budget_lines
set start_date = bl_rec.start_date,
end_date = bl_rec.completion_date
where resource_assignment_id = bl_rec.resource_assignment_id and
((start_date <> bl_rec.start_date) or (end_date <> bl_rec.completion_date));
Select 'Y'
Into l_base_line_ver_exists
From Pa_Budget_Versions
Where Project_id = p_project_id
And budget_type_code is null /* Bug 4200168*/
And Fin_plan_type_id = l_fin_plan_type_id
And Budget_status_code = 'B'
And Version_type = l_version_type
And Ci_Id Is Null
And Budget_version_id <> l_target_version_id
And Rownum < 2;
Select fin_plan_type_id,fin_plan_preference_code,primary_cost_forecast_flag, primary_rev_forecast_flag
Bulk Collect
Into l_fc_plan_type_ids_tbl,l_fc_pt_pref_code_tbl,l_primary_cost_fcst_flag_tbl,l_primary_rev_fcst_flag_tbl
From Pa_proj_fp_options
Where Project_Id = p_project_id
And Fin_Plan_Option_Level_Code = 'PLAN_TYPE'
And (primary_cost_forecast_flag = 'Y' or
primary_rev_forecast_flag = 'Y');
select bv.budget_type_code,
bv.resource_list_id,
bv.version_type,
pt.project_type_class_code,
opt.approved_rev_plan_type_flag,
opt.approved_cost_plan_type_flag,
DECODE
(opt.fin_plan_preference_code,
'COST_ONLY',opt.cost_time_phased_code,
'REVENUE_ONLY',opt.revenue_time_phased_code,
'COST_AND_REV_SAME',opt.all_time_phased_code,
DECODE
(bv.version_type,
'COST',opt.cost_time_phased_code,
'REVENUE',opt.revenue_time_phased_code
)
),
DECODE
(opt.fin_plan_preference_code,
'COST_ONLY',opt.cost_fin_plan_level_code,
'REVENUE_ONLY',opt.revenue_fin_plan_level_code,
'COST_AND_REV_SAME',opt.all_fin_plan_level_code,
DECODE
(bv.version_type,
'COST',opt.cost_fin_plan_level_code,
'REVENUE',opt.revenue_fin_plan_level_code
)
),
pavl.fin_plan_type_code,
-- entry_level_code,
bv.pm_product_code,
/* bv.created_by, Commented for bug 6176649 */
opt.fin_plan_type_id
into l_budget_type_code,
l_resource_list_id,
l_version_type,
l_project_type_class_code,
l_ar_flag,
l_ac_flag,
l_time_phased_type_code,
l_fin_plan_level_code,
l_fin_plan_type_code,
-- l_entry_level_code,
l_pm_product_code,
/* l_created_by, Commented for bug 6176649 */
l_fin_plan_type_id
from pa_project_types_all pt,
pa_projects_all pr,
pa_budget_versions bv,
-- pa_budget_entry_methods be,
pa_proj_fp_options opt,
pa_fin_plan_types_b pavl
where bv.budget_version_id = p_budget_version_id and
opt.fin_plan_version_id = bv.budget_version_id and
bv.project_id = pr.project_id and
-- be.budget_entry_method_code = bv.budget_entry_method_code and
pr.project_type = pt.project_type and
--nvl(pr.org_id,-99) = nvl(pt.org_id,-99) and --Bug 5374346
pr.org_id = pt.org_id and
opt.fin_plan_type_id = pavl.fin_plan_type_id and
opt.fin_plan_option_level_code = 'PLAN_VERSION';
update pa_budget_versions
set last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id,
budget_status_code = PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_SUBMITTED, -- bug 3978894 'S',
record_version_number = record_version_number+1
where budget_version_id = p_budget_version_id;
update pa_budget_versions
set wf_status_code = 'IN_ROUTE'
where budget_version_id = p_budget_version_id;
Modified for 3550073. Selected the amount columns in pa_fp_merged_ctrl_items in the
cursors
*/
PROCEDURE Get_Included_Ci
( p_from_bv_id IN pa_budget_versions.budget_version_id%TYPE
,p_to_bv_id IN pa_budget_versions.budget_version_id%TYPE --DEFAULT NULL
,p_impact_status IN pa_ci_impacts.status_code%TYPE
,x_ci_rec_tab OUT NOCOPY pa_fin_plan_pvt.ci_rec_tab
,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
cursor c1 is
select f.ci_id, f.ci_plan_version_id, im.ci_impact_id ,
f.version_type,
f.impl_proj_func_raw_cost ,
f.impl_proj_func_burdened_cost,
f.impl_proj_func_revenue,
f.impl_proj_raw_cost ,
f.impl_proj_burdened_cost,
f.impl_proj_revenue,
decode(f.version_type,'COST',f.impl_quantity,NULL) impl_cost_ppl_qty,
decode(f.version_type,'COST',f.impl_equipment_quantity,NULL) impl_cost_equip_qty,
decode(f.version_type,'REVENUE',f.impl_quantity,NULL) impl_rev_ppl_qty,
decode(f.version_type,'REVENUE',f.impl_equipment_quantity,NULL) impl_rev_equip_qty,
f.impl_agr_revenue impl_agr_revenue,
pbv.rev_partially_impl_flag rev_partially_impl_flag
from pa_fp_merged_ctrl_items f,
pa_ci_impacts im ,
pa_budget_versions pbv
where f.plan_version_id = p_from_bv_id
and pbv.budget_version_id=f.ci_plan_version_id
and im.ci_id = f.ci_id
and im.impact_type_code IN ('FINPLAN_COST','FINPLAN_REVENUE')
and im.status_code = nvl(p_impact_status,im.status_code)
and decode(im.impact_type_code,
'FINPLAN_COST','COST',
'FINPLAN_REVENUE','REVENUE') = f.version_type
and f.project_id=pbv.project_id
and not exists
(select 'x' from pa_fp_merged_ctrl_items t
where t.plan_version_id = p_to_bv_id
and t.ci_id = f.ci_id
and f.version_type = t.version_type
and t.ci_plan_version_id = f.ci_plan_version_id
and t.project_id=f.project_id);
select f.ci_id, f.ci_plan_version_id, im.ci_impact_id ,
f.version_type,
f.impl_proj_func_raw_cost ,
f.impl_proj_func_burdened_cost,
f.impl_proj_func_revenue,
f.impl_proj_raw_cost ,
f.impl_proj_burdened_cost,
f.impl_proj_revenue,
decode(f.version_type,'COST',f.impl_quantity,NULL) impl_cost_ppl_qty,
decode(f.version_type,'COST',f.impl_equipment_quantity,NULL) impl_cost_equip_qty,
decode(f.version_type,'REVENUE',f.impl_quantity,NULL) impl_rev_ppl_qty,
decode(f.version_type,'REVENUE',f.impl_equipment_quantity,NULL) impl_rev_equip_qty,
f.impl_agr_revenue impl_agr_revenue,
pbv.rev_partially_impl_flag rev_partially_impl_flag
from pa_fp_merged_ctrl_items f,
pa_ci_impacts im,
pa_budget_versions pbv
where f.plan_version_id = p_from_bv_id
and pbv.budget_version_id=f.ci_plan_version_id
and im.ci_id = f.ci_id
and im.impact_type_code IN ('FINPLAN_COST','FINPLAN_REVENUE')
and decode(im.impact_type_code,
'FINPLAN_COST','COST',
'FINPLAN_REVENUE','REVENUE') = f.version_type
and im.status_code = nvl(p_impact_status,im.status_code)
and f.project_id=pbv.project_id;
x_ci_rec_tab.delete;
select 'Y',
bv.project_id,
bv.fin_plan_type_id,
bv.version_type
from pa_fin_plan_types_b pt
,pa_budget_versions bv
where pt.fin_plan_type_id = bv.fin_plan_type_id
and bv.budget_version_id = p_target_bv_id
and (pt.approved_cost_plan_type_flag = 'Y'
OR
pt.approved_rev_plan_type_flag = 'Y');
l_ci_rec_tab.delete;
l_ci_rec_tab.delete;
UPDATE pa_budget_versions
SET rev_partially_impl_flag='N',
record_version_number=nvl(record_version_number,0)+1,
last_updated_by=fnd_global.user_id,
last_update_login=fnd_global.login_id,
last_update_date=sysdate
WHERE budget_Version_id=l_ci_rec_tab(i).ci_plan_version_id;
pa_fp_ci_merge.fp_ci_update_impact
( p_ci_id => l_ci_rec_tab(i).ci_id
,p_status_code => 'CI_IMPACT_PENDING'
,p_impact_type_code => l_impact_type_code
--,p_record_version_number => l_ci_rec_tab(i).record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
l_ci_rec_tab.delete;
pa_fp_ci_merge.fp_ci_update_impact
( p_ci_id => l_ci_rec_tab(i).ci_id
,p_status_code => 'CI_IMPACT_IMPLEMENTED'
,p_impact_type_code => l_impact_type_code
--,p_record_version_number => l_ci_rec_tab(i).record_version_number
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
SELECT pt.parent_task_id parent_task_id,
pt.top_task_id top_task_id ,
pelm.element_Version_id element_Version_id
FROM pa_tasks pt,
pa_proj_element_versions pelm
WHERE pt.task_id = c_impacted_task_id
AND pelm.proj_element_id=pt.task_id
AND pelm.parent_structure_version_id=PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID( p_project_id);
/* Bug 2688610 - should call delete_version rather than delete_version_helper.
pa_fin_plan_pub.Delete_Version_Helper
( p_budget_version_id => l_curr_work_version_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
pa_debug.g_err_stage := 'Error in lock unlock version - cannot delete working version';
pa_fin_plan_pub.delete_version
( p_project_id => p_project_id
,p_budget_version_id => l_curr_work_version_id
,p_record_version_number => l_record_version_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
pa_debug.g_err_stage:= 'Could not delete the current working version';
pa_debug.g_err_stage:= 'Deleted the current working version';
need to update the following in fp_options.
conversion attributes.
*/
/*
Bug 2670747 - The MC attributes need to be updated only if MC flag is Y
*/
IF (p_plan_in_mc_flag = 'Y') THEN
update pa_proj_fp_options
set projfunc_cost_rate_type = p_projfunc_cost_rate_type
,projfunc_cost_rate_date_type = p_projfunc_cost_rate_date_type
,projfunc_cost_rate_date = p_projfunc_cost_rate_date
,projfunc_rev_rate_type = p_projfunc_rev_rate_type
,projfunc_rev_rate_date_type = p_projfunc_rev_rate_date_type
,projfunc_rev_rate_date = p_projfunc_rev_rate_date
,project_cost_rate_type = p_project_cost_rate_type
,project_cost_rate_date_type = p_project_cost_rate_date_type
,project_cost_rate_date = p_project_cost_rate_date
,project_rev_rate_type = p_project_rev_rate_type
,project_rev_rate_date_type = p_project_rev_rate_date_type
,project_rev_rate_date = p_project_rev_rate_date
where proj_fp_options_id = l_fp_options_id;
need to update the following in budget_versions
change reason code,
product code, budget reference.
*/
update pa_budget_versions
set change_reason_code = p_change_reason_code,
pm_product_code = p_pm_product_code,
pm_budget_reference = p_pm_budget_reference,
attribute_category = p_attribute_category,
attribute1 = p_attribute1,
attribute2 = p_attribute2,
attribute3 = p_attribute3,
attribute4 = p_attribute4,
attribute5 = p_attribute5,
attribute6 = p_attribute6,
attribute7 = p_attribute7,
attribute8 = p_attribute8,
attribute9 = p_attribute9,
attribute10 = p_attribute10,
attribute11 = p_attribute11,
attribute12 = p_attribute12,
attribute13 = p_attribute13,
attribute14 = p_attribute14,
attribute15 = p_attribute15
where budget_version_id = l_created_version_id;
autobaseline case, resource list id is always none and hence calling insert_defaults should be fine */
IF (l_calling_context = PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE AND p_ci_id IS NOT NULL) THEN
IF ( p_impacted_task_id IS NULL OR
p_fin_plan_level_code = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT ) THEN
-- -- Bug # 3507156
-- References to PA_FP_ELEMENTS table have been commented out (FP M)
-- Comment START.
/*
-- Create fp elements and resource assignments for the budget version and the impacted task id
pa_debug.g_err_stage:='Calling pa_fp_elements_pub.insert_default...';
Pa_Fp_Elements_Pub.Insert_Default (
p_proj_fp_options_id => l_fp_options_id
,p_element_type => p_version_type
,p_planning_level => p_fin_plan_level_code
,p_resource_list_id => l_resource_list_id
-- Bug 2920954 Start of parameters added for post FP-K oneoff patch
,p_select_res_auto_flag => NULL
,p_res_planning_level => NULL
--Bug 2920954 End of parameters added for post FP-K oneoff patch
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
pa_debug.g_err_stage:= 'Exception while inserting a row into pa_resource_assignments;';
This procedure would use the input budget_line_tbl to insert records into
pa_resource_assignments, pa_budget_lines, pa_mc_budget_lines and also takes
care of rolling up the resource assignments and maintaining the denorm table.
*/
PROCEDURE CREATE_FINPLAN_LINES
( -- Bug Fix: 4569365. Removed MRC code.
-- p_calling_context IN pa_mrc_finplan.g_calling_module%TYPE /* Bug# 2674353 */
p_calling_context IN VARCHAR2
,p_fin_plan_version_id IN pa_budget_versions.budget_version_id%TYPE
,p_budget_lines_tab IN pa_fin_plan_pvt.budget_lines_tab
,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_msg_count NUMBER := 0;
pa_debug.g_err_stage:= 'Delete records if any from the rollup tmp';
delete from pa_fp_rollup_tmp; /* Included after UT */
SELECT project_currency_code
,projfunc_currency_code
INTO l_proj_currency_code
,l_projfunc_currency_code
FROM pa_projects_all
WHERE project_id = (SELECT project_id
FROM pa_budget_versions
WHERE budget_version_id = p_fin_plan_version_id);
Insert into pa_fp_rollup_tmp
(
system_reference1 --task_id
,system_reference2 --rlmid
,System_reference3 -- CBS_ELEMENT_ID 16598322
,description
,start_date
,end_date
,period_name
,quantity
,system_reference4 --unit_of_measure
,system_reference5 --track_as_labor_flag
,txn_currency_code
,project_currency_code --added for #2727304
,projfunc_currency_code --added for #2727304
,projfunc_raw_cost
,projfunc_burdened_cost
,projfunc_revenue
,txn_raw_cost
,txn_burdened_cost
,txn_revenue
,project_raw_cost
,project_burdened_cost
,project_revenue
,change_reason_code
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,PROJFUNC_COST_RATE_TYPE
,PROJFUNC_COST_RATE_DATE_TYPE
,PROJFUNC_COST_RATE_DATE
,PROJFUNC_COST_EXCHANGE_RATE
,PROJFUNC_REV_RATE_TYPE
,PROJFUNC_REV_RATE_DATE_TYPE
,PROJFUNC_REV_RATE_DATE
,PROJFUNC_REV_EXCHANGE_RATE
,PROJECT_COST_RATE_TYPE
,PROJECT_COST_RATE_DATE_TYPE
,PROJECT_COST_RATE_DATE
,PROJECT_COST_EXCHANGE_RATE
,PROJECT_REV_RATE_TYPE
,PROJECT_REV_RATE_DATE_TYPE
,PROJECT_REV_RATE_DATE
,PROJECT_REV_EXCHANGE_RATE
,pm_product_code
,pm_budget_line_reference
,quantity_source
,raw_cost_source
,burdened_cost_source
,revenue_source
,resource_assignment_id
,budget_version_id
)
Values
(
l_task_id_tab(i)
,l_resource_list_member_id_tab(i)
,l_cbs_element_id_tab(i) -- Added for CBS 16598322
,l_description_tab(i)
,l_start_date_tab(i)
,l_end_date_tab(i)
,l_period_name_tab(i)
,l_quantity_tab(i)
,l_unit_of_measure_tab(i)
,l_track_as_labor_flag_tab(i)
,l_txn_currency_code_tab(i)
,l_proj_currency_code --added for #2727304
,l_projfunc_currency_code --added for #2727304
,l_raw_cost_tab(i)
,l_burdened_cost_tab(i)
,l_revenue_tab(i)
,l_txn_raw_cost_tab(i)
,l_txn_burdened_cost_tab(i)
,l_txn_revenue_tab(i)
,l_project_raw_cost_tab(i)
,l_project_burdened_cost_tab(i)
,l_project_revenue_tab(i)
,l_change_reason_code_tab(i)
,l_attribute_category_tab(i)
,l_attribute1_tab(i)
,l_attribute2_tab(i)
,l_attribute3_tab(i)
,l_attribute4_tab(i)
,l_attribute5_tab(i)
,l_attribute6_tab(i)
,l_attribute7_tab(i)
,l_attribute8_tab(i)
,l_attribute9_tab(i)
,l_attribute10_tab(i)
,l_attribute11_tab(i)
,l_attribute12_tab(i)
,l_attribute13_tab(i)
,l_attribute14_tab(i)
,l_attribute15_tab(i)
,l_PF_COST_RATE_TYPE_tab(i)
,l_PF_COST_RATE_DATE_TYPE_tab(i)
,l_PF_COST_RATE_DATE_tab(i)
,l_PF_COST_RATE_tab(i)
,l_PF_REV_RATE_TYPE_tab(i)
,l_PF_REV_RATE_DATE_TYPE_tab(i)
,l_PF_REV_RATE_DATE_tab(i)
,l_PF_REV_RATE_tab(i)
,l_PJ_COST_RATE_TYPE_tab(i)
,l_PJ_COST_RATE_DATE_TYPE_tab(i)
,l_PJ_COST_RATE_DATE_tab(i)
,l_PJ_COST_RATE_tab(i)
,l_PJ_REV_RATE_TYPE_tab(i)
,l_PJ_REV_RATE_DATE_TYPE_tab(i)
,l_PJ_REV_RATE_DATE_tab(i)
,l_PJ_REV_RATE_tab(i)
,l_pm_product_code_tab(i)
,l_pm_budget_line_reference_tab(i)
,l_quantity_source_tab(i)
,l_raw_cost_source_tab(i)
,l_burdened_cost_source_tab(i)
,l_revenue_source_tab(i)
,l_resource_assignment_id_tab(i)
,p_fin_plan_version_id
);
pa_debug.g_err_stage:= 'number of records inserted -> ' || sql%ROWCOUNT;
* api can use that to insert/spread the budget lines passed from AMG/MSP
*----------------------------------------------------------------------------------------
INSERT INTO pa_budget_lines(
RESOURCE_ASSIGNMENT_ID
,BUDGET_LINE_ID
,BUDGET_VERSION_ID
,START_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,END_DATE
,PERIOD_NAME
,QUANTITY
,RAW_COST
,BURDENED_COST
,REVENUE
,CHANGE_REASON_CODE
,DESCRIPTION
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,RAW_COST_SOURCE
,BURDENED_COST_SOURCE
,QUANTITY_SOURCE
,REVENUE_SOURCE
,PROJFUNC_CURRENCY_CODE
,PROJFUNC_COST_RATE_TYPE
,PROJFUNC_COST_EXCHANGE_RATE
,PROJFUNC_COST_RATE_DATE_TYPE
,PROJFUNC_COST_RATE_DATE
,PROJECT_CURRENCY_CODE
,PROJECT_COST_RATE_TYPE
,PROJECT_COST_EXCHANGE_RATE
,PROJECT_COST_RATE_DATE_TYPE
,PROJECT_COST_RATE_DATE
,PROJECT_RAW_COST
,PROJECT_BURDENED_COST
,PROJECT_REVENUE
,TXN_RAW_COST
,TXN_BURDENED_COST
,TXN_REVENUE
,TXN_CURRENCY_CODE
,BUCKETING_PERIOD_CODE
,PROJFUNC_REV_RATE_DATE_TYPE
,PROJFUNC_REV_RATE_DATE
,PROJFUNC_REV_RATE_TYPE
,PROJFUNC_REV_EXCHANGE_RATE
,PROJECT_REV_RATE_TYPE
,PROJECT_REV_EXCHANGE_RATE
,PROJECT_REV_RATE_DATE_TYPE
,PROJECT_REV_RATE_DATE
,PM_PRODUCT_CODE
,PM_BUDGET_LINE_REFERENCE ) -- Added for bug 3833724
(SELECT
RESOURCE_ASSIGNMENT_ID
,pa_budget_lines_s.nextval
,p_fin_plan_version_id
,START_DATE
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,END_DATE
,PERIOD_NAME
,null--QUANTITY
,null--PROJFUNC_RAW_COST
,null--PROJFUNC_BURDENED_COST
,NULL--PROJFUNC_REVENUE
,CHANGE_REASON_CODE
,DESCRIPTION
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,nvl(RAW_COST_SOURCE,decode(PROJFUNC_RAW_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
,nvl(BURDENED_COST_SOURCE,decode(PROJFUNC_BURDENED_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
,nvl(QUANTITY_SOURCE,decode(QUANTITY,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
,nvl(REVENUE_SOURCE,decode(PROJFUNC_REVENUE,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
,PROJFUNC_CURRENCY_CODE
,PROJFUNC_COST_RATE_TYPE
,PROJFUNC_COST_EXCHANGE_RATE
,PROJFUNC_COST_RATE_DATE_TYPE
,PROJFUNC_COST_RATE_DATE
,PROJECT_CURRENCY_CODE
,PROJECT_COST_RATE_TYPE
,PROJECT_COST_EXCHANGE_RATE
,PROJECT_COST_RATE_DATE_TYPE
,PROJECT_COST_RATE_DATE
,null--PROJECT_RAW_COST
,null--PROJECT_BURDENED_COST
,null--PROJECT_REVENUE
,null--TXN_RAW_COST
,null--TXN_BURDENED_COST
,null--TXN_REVENUE
,TXN_CURRENCY_CODE
,BUCKETING_PERIOD_CODE
,PROJFUNC_REV_RATE_DATE_TYPE
,PROJFUNC_REV_RATE_DATE
,PROJFUNC_REV_RATE_TYPE
,PROJFUNC_REV_EXCHANGE_RATE
,DECODE(p_calling_context,
PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,'User',
PROJECT_REV_RATE_TYPE)--Bug 4133468. PROJECT_REV_RATE_TYPE
,DECODE(p_calling_context,
PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,DECODE(PROJFUNC_REVENUE,
NULL,NULL,
0,0,
(PROJECT_REVENUE/PROJFUNC_REVENUE)),
PROJECT_REV_EXCHANGE_RATE)--Bug 4133468. PROJECT_REV_EXCHANGE_RATE
,DECODE(p_calling_context,
PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,NULL,
PROJECT_REV_RATE_DATE_TYPE)--Bug 4133468. PROJECT_REV_RATE_DATE_TYPE
,DECODE(p_calling_context,
PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,NULL,
PROJECT_REV_RATE_DATE)--Bug 4133468. PROJECT_REV_RATE_DATE
,PM_PRODUCT_CODE -- , l_pm_product_code changed to pm_product_code for bug 3833724
,PM_BUDGET_LINE_REFERENCE -- Added for bug 3833724
FROM pa_fp_rollup_tmp tmp
WHERE tmp.budget_line_id IS NULL
AND (tmp.txn_raw_cost IS NOT NULL
or tmp.txn_burdened_cost IS NOT NULL
or tmp.quantity IS NOT NULL
or tmp.txn_revenue IS NOT NULL));
pa_debug.g_err_stage:= 'number of records inserted -> ' || sql%ROWCOUNT;
/* Bug 4221590:inserting into PA_FP_SPREAD_CALC_TMP1 */
DELETE FROM PA_FP_SPREAD_CALC_TMP1;
pa_debug.g_err_stage:= 'inserting into pa_fp_spread_calc_tmp1 -> ' || sql%ROWCOUNT;
INSERT INTO PA_FP_SPREAD_CALC_TMP1(
RESOURCE_ASSIGNMENT_ID
,BUDGET_VERSION_ID
,START_DATE
,BL_CREATION_DATE
,BL_CREATED_BY
,END_DATE
,PERIOD_NAME
,CHANGE_REASON_CODE
,DESCRIPTION
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,RAW_COST_SOURCE
,BURDENED_COST_SOURCE
,QUANTITY_SOURCE
,REVENUE_SOURCE
,PROJFUNC_CURRENCY_CODE
,PROJFUNC_COST_RATE_TYPE
,PROJFUNC_COST_EXCHANGE_RATE
,PROJFUNC_COST_RATE_DATE_TYPE
,PROJFUNC_COST_RATE_DATE
,PROJECT_CURRENCY_CODE
,PROJECT_COST_RATE_TYPE
,PROJECT_COST_EXCHANGE_RATE
,PROJECT_COST_RATE_DATE_TYPE
,PROJECT_COST_RATE_DATE
,TXN_CURRENCY_CODE
,BUCKETING_PERIOD_CODE
,PROJFUNC_REV_RATE_DATE_TYPE
,PROJFUNC_REV_RATE_DATE
,PROJFUNC_REV_RATE_TYPE
,PROJFUNC_REV_EXCHANGE_RATE
,PROJECT_REV_RATE_TYPE
,PROJECT_REV_EXCHANGE_RATE
,PROJECT_REV_RATE_DATE_TYPE
,PROJECT_REV_RATE_DATE
,PM_PRODUCT_CODE
,PM_BUDGET_LINE_REFERENCE ) -- Added for bug 3833724
(SELECT
RESOURCE_ASSIGNMENT_ID
,p_fin_plan_version_id
,START_DATE
,SYSDATE
,FND_GLOBAL.USER_ID
,END_DATE
,PERIOD_NAME
,CHANGE_REASON_CODE
,DESCRIPTION
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,nvl(RAW_COST_SOURCE,decode(PROJFUNC_RAW_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
,nvl(BURDENED_COST_SOURCE,decode(PROJFUNC_BURDENED_COST,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
,nvl(QUANTITY_SOURCE,decode(QUANTITY,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
,nvl(REVENUE_SOURCE,decode(PROJFUNC_REVENUE,null,null,PA_FP_CONSTANTS_PKG.G_AMOUNT_SOURCE_MANUAL_M))
,PROJFUNC_CURRENCY_CODE
,PROJFUNC_COST_RATE_TYPE
,PROJFUNC_COST_EXCHANGE_RATE
,PROJFUNC_COST_RATE_DATE_TYPE
,PROJFUNC_COST_RATE_DATE
,PROJECT_CURRENCY_CODE
,PROJECT_COST_RATE_TYPE
,PROJECT_COST_EXCHANGE_RATE
,PROJECT_COST_RATE_DATE_TYPE
,PROJECT_COST_RATE_DATE
,TXN_CURRENCY_CODE
,BUCKETING_PERIOD_CODE
,PROJFUNC_REV_RATE_DATE_TYPE
,PROJFUNC_REV_RATE_DATE
,PROJFUNC_REV_RATE_TYPE
,PROJFUNC_REV_EXCHANGE_RATE
,DECODE(p_calling_context,
PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,'User',
PROJECT_REV_RATE_TYPE)--Bug 4133468. PROJECT_REV_RATE_TYPE
,DECODE(p_calling_context,
PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,DECODE(PROJFUNC_REVENUE,
NULL,NULL,
0,0,
(PROJECT_REVENUE/PROJFUNC_REVENUE)),
PROJECT_REV_EXCHANGE_RATE)--Bug 4133468. PROJECT_REV_EXCHANGE_RATE
,DECODE(p_calling_context,
PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,NULL,
PROJECT_REV_RATE_DATE_TYPE)--Bug 4133468. PROJECT_REV_RATE_DATE_TYPE
,DECODE(p_calling_context,
PA_FP_CONSTANTS_PKG.G_AUTOMATIC_BASELINE,NULL,
PROJECT_REV_RATE_DATE)--Bug 4133468. PROJECT_REV_RATE_DATE
,PM_PRODUCT_CODE -- , l_pm_product_code changed to pm_product_code for bug 3833724
,PM_BUDGET_LINE_REFERENCE -- Added for bug 3833724
FROM pa_fp_rollup_tmp tmp
WHERE tmp.budget_line_id IS NULL); /*Changed for bug 4224464. When a budget line is passed for which amounts and quantity
were not passed i.e these values were miss_xxx values then these lines wont get selected here
but in this case our intent should be to not update these columns for these lines and update
the rest of the coulmns*/
/* AND (tmp.txn_raw_cost IS NOT NULL
or tmp.txn_burdened_cost IS NOT NULL
or tmp.quantity IS NOT NULL
or tmp.txn_revenue IS NOT NULL));*/
pa_debug.g_err_stage:= 'number of records inserted -> ' || sql%ROWCOUNT;
--bug#8854015 Added condition to directly update the planning_start_date and planning_end_date
-- if the updation is for non-time based.
l_time_phased_type_code := PA_FIN_PLAN_UTILS.Get_Time_Phased_code(p_fin_plan_version_id);
-- Bug 15861188 : added following update statement
UPDATE PA_FP_SPREAD_CALC_TMP1 cache
SET (cache.start_date ,cache.end_date) =
(select planning_start_date,planning_end_date
FROM pa_resource_assignments pra
where pra.resource_assignment_id = cache.resource_assignment_id )
where cache.budget_version_id = p_fin_plan_version_id
AND cache.start_date IS NULL
AND cache.end_date IS NULL;
update pa_resource_assignments pra
set (planning_start_date, planning_end_date)
= (select nvl(tmp.start_date, planning_start_date),
nvl(tmp.end_date, planning_end_date)
from pa_fp_spread_calc_tmp1 tmp
where tmp.resource_assignment_id = pra.resource_assignment_id)
where pra.budget_version_id = p_fin_plan_version_id;
update pa_resource_assignments pra
set (planning_start_date, planning_end_date)
= (select decode(min(pbl.start_date),NULL,
nvl(min(tmp.start_date), planning_start_date),
least(nvl(min(tmp.start_date), planning_start_date),
nvl(min(pbl.start_date), planning_start_date))),
greatest(nvl(max(tmp.end_date), planning_end_date), planning_end_date) -- bug#10376078
from pa_fp_spread_calc_tmp1 tmp, pa_budget_lines pbl
where tmp.resource_assignment_id = pra.resource_assignment_id
and pbl.resource_assignment_id (+)= tmp.resource_assignment_id)
where pra.budget_version_id = p_fin_plan_version_id;
update pa_resource_assignments pra
set pra.sp_fixed_date = pra.planning_start_date
where pra.budget_version_id = p_fin_plan_version_id
and pra.spread_curve_id = 6;
update pa_resource_assignments pra
set (planning_start_date, planning_end_date)
= (select nvl(min(start_date), planning_start_date),
nvl(max(end_date), planning_end_date)
from pa_fp_spread_calc_tmp1 tmp /* Bug 4221590 */
where tmp.resource_assignment_id = pra.resource_assignment_id)
where pra.budget_version_id = p_fin_plan_version_id;
SELECT fin_plan_type_id
FROM pa_fin_plan_types_vl
WHERE name=p_fin_plan_type_name;
SELECT fin_plan_type_id
FROM pa_fin_plan_types_b
WHERE fin_plan_type_id=p_fin_plan_type_id;
Procedure Name: DELETE_WP_OPTION
This procedure is added as part of FPM Development. Tracking Bug - 3354518.
Purpose: This api Deletes the proj fp options data pertaining
to the workplan type attached to the project for
the passed project id.
Deletes data from the following tables -
1) pa_proj_fp_options
2) pa_fp_txn_currencies
3) pa_proj_period_profiles
4) pa_fp_upgrade_audit
Please note that all validations before calling this API shall be done
in the calling entity.
Parameters:
IN 1) p_project_id - project id.
=======================================================================*/
PROCEDURE Delete_wp_option
(p_project_id IN pa_projects_all.project_id%TYPE
,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
--Start of variables used for debugging
l_msg_count NUMBER :=0;
SELECT project_structure_version_id
FROM pa_budget_versions
WHERE project_id = c_project_id
AND nvl(wp_version_flag,'N') = 'Y';
SAVEPOINT DELETE_WP_OPTION_SAVE;
PA_DEBUG.Set_Curr_Function( p_function => 'PA_FIN_PLAN_PVT.Delete_wp_option',
p_debug_mode => l_debug_mode );
pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,3);
pa_debug.write('Delete_wp_options Project Id is null: ' || g_module_name,pa_debug.g_err_stage,5);
pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,3);
SELECT pfo.proj_fp_options_id
INTO l_proj_fp_options_id
FROM pa_proj_fp_options pfo
,pa_fin_plan_types_b pft
WHERE pfo.project_id = p_project_id
AND pfo.fin_plan_type_id = pft.fin_plan_type_id
AND pfo.fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
AND nvl(pft.use_for_workplan_flag,'N') = 'Y';
pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,3);
pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,3);
Delete_wp_budget_versions
(p_struct_elem_version_id_tbl => l_sv_id_tbl
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
pa_debug.g_err_stage:='Call to Delete_wp_budget_versions is returning error status';
pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,3);
RAISE Delete_Ver_Exc_PVT;
pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,3);
DELETE FROM pa_proj_fp_options WHERE proj_fp_options_id = l_proj_fp_options_id;
DELETE FROM pa_fp_txn_currencies WHERE proj_fp_options_id = l_proj_fp_options_id;
DELETE FROM pa_fp_upgrade_audit WHERE proj_fp_options_id = l_proj_fp_options_id;
WHEN Delete_Ver_Exc_PVT THEN
ROLLBACK TO SAVEPOINT PA_FP_PUB_DELETE_VER;
pa_debug.g_err_stage:='Delete_wp_budget_versions returned error';
pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,5);
pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,5);
ROLLBACK TO SAVEPOINT DELETE_WP_OPTION_SAVE;
,p_procedure_name => 'Delete_wp_option');
pa_debug.write('Delete_wp_option: ' || g_module_name,pa_debug.g_err_stage,5);
END Delete_wp_option;
* Procedure Name: DELETE_WP_BUDGET_VERSIONS
* This procedure is added as part of FPM Development. Trackinb Bug - 3354518.
* Purpose: This API deletes the budget_versions for all the
* workplan structure version ids passed.
* Parameters: 1) p_struct_elem_version_id_tbl IN SYSTEM.pa_num_tbl_type
*=======================================================================*/
PROCEDURE Delete_wp_budget_versions
(p_struct_elem_version_id_tbl IN SYSTEM.pa_num_tbl_type
,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
--Start of variables used for debugging
l_msg_count NUMBER :=0;
SELECT budget_version_id,record_version_number,project_id
FROM pa_budget_versions
WHERE project_structure_version_id = nvl(c_structure_version_id,-99)
AND nvl(wp_version_flag,'N')='Y';
SAVEPOINT PA_FP_PUB_DELETE_VER;
PA_DEBUG.Set_Curr_Function( p_function => 'PA_FIN_PLAN_PVT.Delete_wp_bugdet_versions',
p_debug_mode => l_debug_mode );
pa_debug.write('Delete_wp_bugdet_versions: ' || g_module_name,pa_debug.g_err_stage,3);
pa_debug.write('Delete_wp_bugdet_versions: ' || g_module_name,pa_debug.g_err_stage,3);
pa_fin_plan_pub.Delete_Version
(p_budget_version_id => c1.budget_version_id,
p_record_version_number => c1.record_version_number,
p_context => PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_WORKPLAN,
p_project_id => c1.project_id,
x_return_Status => l_return_Status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
RAISE Delete_Ver_Exc_PVT;
pa_debug.write('Delete_wp_budget_version: ' || g_module_name,pa_debug.g_err_stage,5);
WHEN Delete_Ver_Exc_PVT THEN
ROLLBACK TO SAVEPOINT PA_FP_PUB_DELETE_VER;
pa_debug.g_err_stage:='Delete_version_helper returned error';
pa_debug.write('Delete_wp_options: ' || g_module_name,pa_debug.g_err_stage,5);
ROLLBACK TO SAVEPOINT DELETE_WP_OPTION_SAVE;
,p_procedure_name => 'Delete_wp_budget_versions');
pa_debug.write('Delete_wp_budget_versions: ' || g_module_name,pa_debug.g_err_stage,5);
END Delete_wp_budget_versions;
l_delete_budget_lines_tab SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
SELECT project_id FROM pa_budget_versions
WHERE budget_version_id = p_fin_plan_version_id ;
l_delete_budget_lines_tab.extend(l_lines_count);
SELECT start_date,
end_date,
quantity,
txn_currency_code,
txn_raw_cost,
txn_burdened_cost,
txn_revenue,
resource_assignment_id,
'N' delete_budget_lines,
'N' spread_amouts,
NULL
BULK COLLECT INTO
l_line_start_date_tab
,l_line_end_date_tab
,l_total_qty_tab
,l_txn_currency_code_tab
,l_total_raw_cost_tab
,l_total_burdened_cost_tab
,l_total_revenue_tab
,l_resource_assignment_tab
,l_delete_budget_lines_tab
,l_spread_amts_flag_tab
,l_number_null_tab -- bug 3825873
FROM pa_fp_rollup_tmp;
pa_debug.g_err_stage:='Calling Calculate API l_delete_budget_lines_tab'||l_delete_budget_lines_tab.COUNT;
Also added the parameter so as not to delete the PA_FP_SPREAD_CALC_TMP1 table in calcualte API . This parameter whould be
passed in all the flows so as not to delete the PA_FP_SPREAD_CALC_TMP1 table.*/
IF(p_calling_context = PA_FP_CONSTANTS_PKG.G_AMG_API)
THEN
PA_FP_CALC_PLAN_PKG.calculate
( p_project_id => l_project_id
,p_budget_version_id => p_fin_plan_version_id
,p_source_context => 'BUDGET_LINE'
,p_refresh_rates_flag => 'N'
,p_refresh_conv_rates_flag => 'N'
,p_conv_rates_required_flag => 'Y'
,p_spread_required_flag => 'Y'
,p_rollup_required_flag => 'Y'
,p_mass_adjust_flag => 'N'
,p_resource_assignment_tab => l_resource_assignment_tab
,p_delete_budget_lines_tab => l_delete_budget_lines_tab
,p_spread_amts_flag_tab => l_spread_amts_flag_tab
,p_txn_currency_code_tab => l_txn_currency_code_tab
,p_total_qty_tab => l_total_qty_tab
,p_addl_qty_tab => l_number_null_tab
,p_total_raw_cost_tab => l_total_raw_cost_tab
,p_addl_raw_cost_tab => l_number_null_tab
,p_total_burdened_cost_tab => l_total_burdened_cost_tab
,p_addl_burdened_cost_tab => l_number_null_tab
,p_total_revenue_tab => l_total_revenue_tab
,p_addl_revenue_tab => l_number_null_tab
,p_line_start_date_tab => l_line_start_date_tab
,p_line_end_date_tab => l_line_end_date_tab
,p_raw_cost_rate_tab => l_number_null_tab
,p_rw_cost_rate_override_tab => l_number_null_tab
,p_b_cost_rate_tab => l_number_null_tab
,p_b_cost_rate_override_tab => l_number_null_tab
,p_bill_rate_tab => l_number_null_tab
,p_bill_rate_override_tab => l_number_null_tab
,p_del_spread_calc_tmp1_flg => 'N' /* Bug: 4309290.Added the parameter to identify if
PA_FP_SPREAD_CALC_TMP1 is to be deleted or not. Frm AMG flow
we will pass N and for other calls to calculate api it would
be yes */
,p_calling_module => PA_FP_CONSTANTS_PKG.G_AMG_API
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
,p_delete_budget_lines_tab => l_delete_budget_lines_tab
,p_spread_amts_flag_tab => l_spread_amts_flag_tab
,p_txn_currency_code_tab => l_txn_currency_code_tab
,p_total_qty_tab => l_total_qty_tab
,p_addl_qty_tab => l_number_null_tab
,p_total_raw_cost_tab => l_total_raw_cost_tab
,p_addl_raw_cost_tab => l_number_null_tab
,p_total_burdened_cost_tab => l_total_burdened_cost_tab
,p_addl_burdened_cost_tab => l_number_null_tab
,p_total_revenue_tab => l_total_revenue_tab
,p_addl_revenue_tab => l_number_null_tab
,p_line_start_date_tab => l_line_start_date_tab
,p_line_end_date_tab => l_line_end_date_tab
,p_raw_cost_rate_tab => l_number_null_tab
,p_rw_cost_rate_override_tab => l_number_null_tab
,p_b_cost_rate_tab => l_number_null_tab
,p_b_cost_rate_override_tab => l_number_null_tab
,p_bill_rate_tab => l_number_null_tab
,p_bill_rate_override_tab => l_number_null_tab
,p_del_spread_calc_tmp1_flg => 'N'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
Also added the parameter so as not to delete the PA_FP_SPREAD_CALC_TMP1 table in calcualte API . This parameter whould be
passed in all the flows so as not to delete the PA_FP_SPREAD_CALC_TMP1 table.*/
IF(p_calling_context = PA_FP_CONSTANTS_PKG.G_AMG_API)
THEN
PA_FP_CALC_PLAN_PKG.calculate
( p_project_id => l_project_id
,p_budget_version_id => p_fin_plan_version_id
,p_source_context => 'RESOURCE_ASSIGNMENT'
,p_resource_assignment_tab => l_resource_assignment_tab
,p_spread_amts_flag_tab => l_spread_amts_flag_tab
,p_txn_currency_code_tab => l_txn_currency_code_tab
,p_total_qty_tab => l_total_qty_tab
,p_total_raw_cost_tab => l_total_raw_cost_tab
,p_total_burdened_cost_tab => l_total_burdened_cost_tab
,p_total_revenue_tab => l_total_revenue_tab
,p_line_start_date_tab => l_line_start_date_tab
,p_line_end_date_tab => l_line_end_date_tab
,p_calling_module => PA_FP_CONSTANTS_PKG.G_AMG_API
,p_del_spread_calc_tmp1_flg => 'N'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);