The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select 'X'
from pa_lookups
where lookup_type='PM_PRODUCT_CODE'
and lookup_code = p_pm_product_code;
SELECT budget_version_id
,budget_status_code
FROM pa_budget_versions
WHERE project_id = c_project_id
AND budget_type_code = c_budget_type_code
AND budget_status_code IN ('W','S')
AND ci_id IS NULL; -- -- Added an extra clause ci_id IS NULL--Bug # 3507156
SELECT 'x'
FROM pa_budget_versions bv
,pa_resource_assignments ra
,pa_budget_lines bl
WHERE bv.budget_version_id = c_budget_version_id
AND bv.budget_version_id = ra.budget_version_id (+)
AND ra.resource_assignment_id = bl.resource_assignment_id (+)
AND bv.ci_id IS NULL -- -- Added an extra clause ci_id IS NULL--Bug # 3507156
FOR UPDATE OF bv.budget_version_id,ra.budget_version_id,bl.resource_assignment_id NOWAIT;
SELECT budget_amount_code
INTO l_budget_amount_code
FROM pa_budget_types
WHERE budget_type_code = p_budget_type_code;
SELECT time_phased_type_code
,categorization_code
,entry_level_code
INTO l_time_phased_type_code
,l_categorization_code
,l_entry_level_code
FROM pa_budget_entry_methods
WHERE budget_entry_method_code = p_entry_method_code
AND trunc(sysdate) BETWEEN trunc(start_date_active) and trunc(nvl(end_date_active,sysdate));
SELECT segment1
INTO l_amg_segment1
FROM pa_projects_all
WHERE project_id=l_project_id;
select fin_plan_type_id
into l_fp_type_id
from pa_budget_versions
where budget_version_id = l_budget_version_id;
select DECODE(l_fp_type_id, null, 'Y','N') into l_old_model from dual;
UPDATE pa_budget_versions
SET pm_budget_reference = p_pm_budget_reference
, pm_product_code = p_pm_product_code
WHERE budget_version_id = l_budget_version_id;
pa_budget_pvt.insert_budget_line
( p_return_status => l_return_status
,p_pa_project_id => l_project_id
,p_budget_type_code => p_budget_type_code
,p_pa_task_id => l_budget_line_in_rec.pa_task_id
,p_pm_task_reference => l_budget_line_in_rec.pm_task_reference
,p_resource_alias => l_budget_alias
,p_member_id => l_budget_rlmid
,p_budget_start_date => l_budget_line_in_rec.budget_start_date
,p_budget_end_date => l_budget_line_in_rec.budget_end_date
,p_period_name => l_budget_line_in_rec.period_name
,p_description => l_budget_line_in_rec.description
,p_raw_cost => l_budget_line_in_rec.raw_cost
,p_burdened_cost => l_budget_line_in_rec.burdened_cost
,p_revenue => l_budget_line_in_rec.revenue
,p_quantity => l_budget_line_in_rec.quantity
,p_pm_product_code => l_budget_line_in_rec.pm_product_code
,p_pm_budget_line_reference => l_budget_line_in_rec.pm_budget_line_reference
,p_resource_list_id => l_resource_list_id
,p_attribute_category => l_budget_line_in_rec.attribute_category
,p_attribute1 => l_budget_line_in_rec.attribute1
,p_attribute2 => l_budget_line_in_rec.attribute2
,p_attribute3 => l_budget_line_in_rec.attribute3
,p_attribute4 => l_budget_line_in_rec.attribute4
,p_attribute5 => l_budget_line_in_rec.attribute5
,p_attribute6 => l_budget_line_in_rec.attribute6
,p_attribute7 => l_budget_line_in_rec.attribute7
,p_attribute8 => l_budget_line_in_rec.attribute8
,p_attribute9 => l_budget_line_in_rec.attribute9
,p_attribute10 => l_budget_line_in_rec.attribute10
,p_attribute11 => l_budget_line_in_rec.attribute11
,p_attribute12 => l_budget_line_in_rec.attribute12
,p_attribute13 => l_budget_line_in_rec.attribute13
,p_attribute14 => l_budget_line_in_rec.attribute14
,p_attribute15 => l_budget_line_in_rec.attribute15
,p_time_phased_type_code => l_time_phased_type_code
,p_entry_level_code => l_entry_level_code
,p_budget_amount_code => l_budget_amount_code
,p_budget_entry_method_code => p_entry_method_code
,p_categorization_code => l_categorization_code
,p_budget_version_id => l_budget_version_id );
pa_debug.g_err_stage := 'Unexpected Error inserting line '||i;
pa_debug.g_err_stage := 'Error inserting line '||i;
pa_debug.g_err_stage := 'Done with the insertion of line '||i;
select fin_plan_type_id
into l_fp_type_id
from pa_budget_versions
where budget_version_id = l_budget_version_id;
select DECODE(l_fp_type_id, null, 'Y','N') into l_old_model from dual;
SELECT prlm.unit_of_measure
INTO l_unit_of_measure
FROM pa_resource_list_members prlm
WHERE prlm.resource_list_member_id = l_budget_lines_in(i).resource_list_member_id;
select locked_by_person_id
into l_locked_by_person_id from pa_budget_versions
where budget_version_id = l_CW_version_id;
G_budget_lines_in_tbl.delete;
G_budget_lines_out_tbl.delete;
SELECT 1
FROM pa_budget_types
WHERE budget_type_code = p_budget_type_code;
SELECT 1
FROM pa_budget_lines
WHERE budget_version_id = p_budget_version_id;
SELECT budget_version_id
FROM pa_budget_versions
WHERE project_id = c_project_id
AND budget_type_code = c_budget_type_code
AND budget_status_code = 'W'
AND ci_id IS NULL; -- -- Added an extra clause ci_id IS NULL--Bug # 3507156
SELECT budget_version_id
FROM pa_budget_versions
WHERE project_id = c_project_id
AND budget_type_code = c_budget_type_code
AND budget_status_code = 'B'
AND ci_id IS NULL; -- -- Added an extra clause ci_id IS NULL--Bug # 3507156
SELECT v.resource_list_id,
t.project_type_class_code
FROM pa_project_types t,
pa_projects p,
pa_budget_versions v
WHERE v.budget_version_id = p_draft_version_id
AND v.project_id = p.project_id
AND p.project_type = t.project_type
AND v.ci_id IS NULL; -- -- Added an extra clause v.ci_id IS NULL--Bug # 3507156
SELECT 'x'
FROM pa_budget_versions
WHERE budget_version_id = p_budget_version_id
AND ci_id IS NULL -- -- Added an extra clause ci_id IS NULL--Bug # 3507156
FOR UPDATE NOWAIT;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT 'X'
FROM pa_proj_fp_options pfo
WHERE pfo.project_id=c_project_id
AND pfo.fin_plan_type_id=c_fin_plan_type_id
AND pfo.fin_plan_option_level_code=PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
SELECT budget_version_id
,record_version_number
FROM pa_budget_versions
WHERE project_id=c_project_id
AND fin_plan_type_id=c_fin_plan_type_id
AND version_type=c_version_type
AND current_original_flag='Y'
AND ci_id IS NULL; -- -- Added an extra clause ci_id IS NULL--Bug # 3507156
SELECT name
INTO l_fin_plan_type_name
FROM pa_fin_plan_types_vl
WHERE fin_plan_type_id = l_fin_plan_type_id;
/* -- dbms_output.put_line('WORKFLOW USED...Update Draft to Submitted, IN_ROUTE'); */
UPDATE pa_budget_versions
SET budget_status_code = 'S', WF_status_code = 'IN_ROUTE'
WHERE budget_version_id = l_curr_working_version_id;
UPDATE pa_budget_versions
SET budget_status_code = 'S', WF_status_code = NULL
WHERE budget_version_id = l_curr_working_version_id;
UPDATE pa_budget_versions
SET budget_status_code = 'W'
WHERE budget_version_id = l_curr_working_version_id;
PA_BUDGET_PVT.insert_budget_line
( p_return_status => p_return_status
,p_pa_project_id => l_project_id
,p_budget_type_code => l_budget_type_code
,p_pa_task_id => p_pa_task_id
,p_pm_task_reference => p_pm_task_reference
,p_resource_alias => p_resource_alias
,p_member_id => p_resource_list_member_id
,p_budget_start_date => p_budget_start_date
,p_budget_end_date => p_budget_end_date
,p_period_name => p_period_name
,p_description => p_description
,p_raw_cost => p_raw_cost
,p_burdened_cost => p_burdened_cost
,p_revenue => p_revenue
,p_quantity => p_quantity
,p_pm_product_code => p_pm_product_code
,p_pm_budget_line_reference => p_pm_budget_line_reference
,p_resource_list_id => l_resource_list_id
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_time_phased_type_code => l_time_phased_code
,p_entry_level_code => l_entry_level_code
,p_budget_amount_code => l_budget_amount_code
,p_budget_entry_method_code => l_budget_entry_method_code
,p_categorization_code => l_categorization_code
,p_budget_version_id => l_budget_version_id
,p_change_reason_code => p_change_reason_code);
pa_debug.g_err_stage := 'PA_BUDGET_PVT.insert_budget_line API falied - unexpected error';
pa_debug.g_err_stage := 'PA_BUDGET_PVT.insert_budget_line API falied - expected error';
ELSE --insert budget line for new FinPlan model
--Store the budget line data in budget line table
IF p_resource_alias = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
l_budget_lines_in(1).resource_alias := NULL;
END IF; --end of code to insert budget line
PROCEDURE delete_draft_budget
( p_api_version_number IN NUMBER
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_budget_type_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
-- Parameters required for Fin Plan Model
,p_fin_plan_type_name IN pa_fin_plan_types_vl.name%TYPE
,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
,p_version_number IN pa_budget_versions.version_number%TYPE
,p_version_type IN pa_budget_versions.version_type%TYPE
)
IS
CURSOR l_budget_version_csr
( p_project_id NUMBER
, p_budget_type_code VARCHAR2 )
IS
SELECT budget_version_id
FROM pa_budget_versions
WHERE project_id = p_project_id
AND budget_type_code = p_budget_type_code
AND budget_status_code = 'W'
AND ci_id IS NULL; -- -- Added an extra clause ci_id IS NULL--Bug # 3507156
SELECT 1
FROM pa_budget_types
WHERE budget_type_code = p_budget_type_code;
SELECT 1
FROM pa_fin_plan_types_b
WHERE fin_plan_type_id = p_fin_plan_type_id
AND use_for_workplan_flag = 'Y';
SELECT 'x'
FROM pa_budget_versions bv
, pa_resource_assignments ra
, pa_budget_lines bl
WHERE bv.budget_version_id = p_budget_version_id
AND bv.budget_version_id = ra.budget_version_id (+)
AND ra.resource_assignment_id = bl.resource_assignment_id (+)
AND bv.ci_id IS NULL -- -- Added an extra clause bv.ci_id IS NULL--Bug # 3507156
FOR UPDATE OF bv.budget_version_id,ra.budget_version_id,bl.resource_assignment_id NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'delete_draft_budget';
l_module_name VARCHAR2(80) := g_module_name ||'.DELETE_DRAFT_BUDGET';
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SAVEPOINT delete_draft_budget_pub;
pa_debug.set_curr_function( p_function => 'delete_draft_budget',
p_debug_mode => l_debug_mode );
,p_function_name => 'PA_PM_DELETE_DRAFT_BUDGET'
,p_version_type => null
,x_return_status => p_return_status
,x_ret_code => l_security_ret_code );
pa_debug.g_err_stage := 'Cannnot delete budget version - '
|| 'budgetary control is enabled';
PA_BUDGET_UTILS.delete_draft( x_budget_version_id => l_budget_version_id
,x_err_code => l_err_code
,x_err_stage => l_err_stage
,x_err_stack => l_err_stack );
pa_debug.g_err_stage := 'Delete draft falied';
( p_old_message_code => 'PA_DELETE_DRAFT_FAILED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'BUDG'
,p_attribute1 => l_amg_segment1
,p_attribute2 => ''
,p_attribute3 => p_budget_type_code
,p_attribute4 => ''
,p_attribute5 => '');
pa_debug.g_err_stage := 'Error in Delete draft';
pa_debug.g_err_stage := 'Unexpected Error in Delete draft';
, p_procedure_name => 'DELETE_DRAFT'
, p_error_text => 'ORA-'||LPAD(substr(l_err_code,2),5,'0') );
,p_function_name => 'PA_PM_DELETE_DRAFT_BUDGET'
,p_version_type => l_version_type
,x_return_status => p_return_status
,x_ret_code => l_security_ret_code );
SELECT name
INTO l_fin_plan_type_name
FROM pa_fin_plan_types_vl
WHERE fin_plan_type_id = l_fin_plan_type_id;
pa_debug.g_err_stage := 'Cannot delete draft budgets attached' ||
'to an organisation forecasting project';
PA_FIN_PLAN_PUB.Delete_Version
( p_project_id => l_project_id
,p_budget_version_id => l_budget_version_id
,p_record_version_number => l_record_version_number
,x_return_status => p_return_status
,x_msg_count => p_msg_count
,x_msg_data => p_msg_data );
UPDATE pa_proj_fp_options
SET gen_src_cost_plan_version_id = NULL,
record_version_number = record_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = to_number(nvl(fnd_profile.value('USER_ID'),fnd_global.user_id)),
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE project_id = l_project_id
AND gen_src_cost_plan_version_id = l_budget_version_id;
UPDATE pa_proj_fp_options
SET gen_src_rev_plan_version_id = NULL,
record_version_number = record_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = to_number(nvl(fnd_profile.value('USER_ID'),fnd_global.user_id)),
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE project_id = l_project_id
AND gen_src_rev_plan_version_id = l_budget_version_id;
UPDATE pa_proj_fp_options
SET gen_src_all_plan_version_id = NULL,
record_version_number = record_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = to_number(nvl(fnd_profile.value('USER_ID'),fnd_global.user_id)),
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE project_id = l_project_id
AND gen_src_all_plan_version_id = l_budget_version_id;
UPDATE pa_budget_versions
SET record_version_number = record_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = to_number(nvl(fnd_profile.value('USER_ID'),fnd_global.user_id)),
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE project_id = l_project_id
AND budget_version_id = l_budget_version_id;
pa_debug.g_err_stage := 'Exiting delete draft budget';
ROLLBACK TO delete_draft_budget_pub;
ROLLBACK TO delete_draft_budget_pub;
ROLLBACK TO delete_draft_budget_pub;
ROLLBACK TO delete_draft_budget_pub;
END delete_draft_budget;
PROCEDURE delete_baseline_budget
( p_api_version_number IN NUMBER
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_msg_count OUT NOCOPY NUMBER
,p_msg_data OUT NOCOPY VARCHAR2
,p_return_status OUT NOCOPY VARCHAR2
,p_pm_product_code IN pa_projects_all.pm_product_code%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_project_id IN pa_projects_all.project_id%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pm_project_reference IN pa_projects_all.pm_project_reference%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_budget_type_code IN pa_budget_versions.budget_type_code%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_fin_plan_type_name IN pa_fin_plan_types_vl.name%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_version_type IN pa_budget_versions.version_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_version_number IN pa_budget_versions.version_number%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
)
IS
--This cursor is used to check if a valid combination of
--project_id, budget_type_code and version_number has
--been passed to this api for a baseline budget
--version. If yes, then current and current_original
--flags and budget_version_id are retrieved
CURSOR l_budget_version_no_csr
( p_project_id NUMBER
, p_budget_type_code VARCHAR2
, p_version_number NUMBER )
IS
SELECT budget_version_id
,current_flag
,current_original_flag
FROM pa_budget_versions
WHERE project_id = p_project_id
AND budget_type_code = p_budget_type_code
AND version_number = p_version_number
AND budget_status_code = 'B';
SELECT budget_version_id
FROM pa_budget_versions
WHERE project_id = p_project_id
AND fin_plan_type_id = p_fin_plan_type_id
AND version_type = p_version_type
AND version_number = p_version_number
AND ci_id IS NULL --Added for better readability (Venketesh's suggestion)
AND budget_status_code = 'B';
SELECT 1
FROM pa_budget_types
WHERE budget_type_code = p_budget_type_code;
SELECT 1
FROM pa_fin_plan_types_b
WHERE fin_plan_type_id = p_fin_plan_type_id
AND use_for_workplan_flag = 'Y';
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_BASELINE_BUDGET';
l_module_name CONSTANT VARCHAR2(100) := g_module_name || '.DELETE_BASELINE_BUDGET';
SAVEPOINT delete_baseline_budget_pub;
pa_debug.set_curr_function( p_function => 'delete_baseline_budget',
p_debug_mode => l_debug_mode );
'or current original can not be deleted';
pa_debug.g_err_stage := 'Cannnot delete budget version - '
|| 'budgetary control is enabled';
PA_BUDGET_UTILS.delete_draft( x_budget_version_id => l_budget_version_no_rec.budget_version_id
,x_err_code => l_err_code
,x_err_stage => l_err_stage
,x_err_stack => l_err_stack );
pa_debug.g_err_stage := 'PA_BUDGET_UTILS.DELETE_DRAFT falied';
( p_old_message_code => 'PA_DELETE_DRAFT_FAILED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'BUDG'
,p_attribute1 => l_amg_segment1
,p_attribute2 => ''
,p_attribute3 => l_budget_type_code
,p_attribute4 => ''
,p_attribute5 => '');
ELSE --valid error message has been returned by Delete_Draft
IF(l_debug_mode='Y') THEN
pa_debug.g_err_stage := 'Error in Delete_draft api';
pa_debug.g_err_stage := 'Unexpected Error in Delete_draft api';
, p_procedure_name => 'DELETE_DRAFT'
, p_error_text => 'ORA-'||LPAD(substr(l_err_code,2),5,'0') );
l_function_name:='PA_PM_DELETE_BASELINE_BUDGET';
pa_debug.g_err_stage := 'Cannot delete baseline budgets attached' ||
'to an organisation forecasting project';
PA_FIN_PLAN_PUB.delete_version
( p_project_id => l_project_id
,p_budget_version_id => l_budget_version_id
,p_record_version_number => l_record_version_number
,x_return_status => p_return_status
,x_msg_count => p_msg_count
,x_msg_data => p_msg_data );
UPDATE pa_proj_fp_options
SET gen_src_cost_plan_version_id = NULL,
record_version_number = record_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = to_number(nvl(fnd_profile.value('USER_ID'),fnd_global.user_id)),
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE project_id = l_project_id
AND gen_src_cost_plan_version_id = l_budget_version_id;
UPDATE pa_proj_fp_options
SET gen_src_rev_plan_version_id = NULL,
record_version_number = record_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = to_number(nvl(fnd_profile.value('USER_ID'),fnd_global.user_id)),
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE project_id = l_project_id
AND gen_src_rev_plan_version_id = l_budget_version_id;
UPDATE pa_proj_fp_options
SET gen_src_all_plan_version_id = NULL,
record_version_number = record_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = to_number(nvl(fnd_profile.value('USER_ID'),fnd_global.user_id)),
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE project_id = l_project_id
AND gen_src_all_plan_version_id = l_budget_version_id;
UPDATE pa_budget_versions
SET record_version_number = record_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = to_number(nvl(fnd_profile.value('USER_ID'),fnd_global.user_id)),
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE project_id = l_project_id
AND budget_version_id = l_budget_version_id;
pa_debug.g_err_stage := 'Exiting delete baseline budget version';
ROLLBACK TO delete_baseline_budget_pub;
ROLLBACK TO delete_baseline_budget_pub;
ROLLBACK TO delete_baseline_budget_pub;
ROLLBACK TO delete_baseline_budget_pub;
END delete_baseline_budget;
PROCEDURE delete_budget_line
( p_api_version_number IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,p_pm_product_code IN VARCHAR2
,p_pa_project_id IN NUMBER
,p_pm_project_reference IN VARCHAR2
,p_budget_type_code IN VARCHAR2
,p_pa_task_id IN NUMBER
,p_pm_task_reference IN VARCHAR2
,p_resource_alias IN VARCHAR2
,p_resource_list_member_id IN NUMBER
,p_start_date IN DATE
,p_period_name IN VARCHAR2
--Parameters added for FP.M
,p_fin_plan_type_id IN NUMBER
,p_fin_plan_type_name IN VARCHAR2
,p_version_type IN VARCHAR2
,p_version_number IN NUMBER
,p_currency_code IN VARCHAR2 )
IS
CURSOR l_resource_assignment_csr
(p_budget_version_id NUMBER
,p_task_id NUMBER
,p_member_id NUMBER )
IS
SELECT resource_assignment_id
FROM pa_resource_assignments
WHERE budget_version_id = p_budget_version_id
AND task_id = p_task_id
AND resource_list_member_id = p_member_id;
SELECT rowidtochar(rowid)
,txn_currency_code
,start_date
,end_date
FROM pa_budget_lines
WHERE resource_assignment_id = p_resource_assignment_id
AND trunc(start_date) = nvl(trunc(p_budget_start_date),trunc(start_date))
AND txn_currency_code = nvl(p_currency_code,txn_currency_code);
SELECT prlm.resource_list_member_id
FROM pa_resource_lists prl
, pa_resource_list_members prlm
WHERE prl.resource_list_id = prlm.resource_list_id
AND prl.uncategorized_flag='Y'
and prlm.resource_class_code = 'FINANCIAL_ELEMENTS';
SELECT trunc(period_start_date)
FROM pa_budget_periods_v
WHERE period_name = p_period_name
AND period_type_code = p_time_phased_type_code;
SELECT 1
FROM pa_budget_periods_v
WHERE trunc(period_start_date) = trunc(p_start_date)
AND period_type_code = p_time_phased_type_code;
SELECT 'x'
FROM pa_budget_lines
WHERE rowid = p_budget_line_rowid
FOR UPDATE NOWAIT;
SELECT approved_rev_plan_type_flag
FROM pa_proj_fp_options
WHERE project_id=c_project_id
AND fin_plan_type_id=c_fin_plan_type_id
AND fin_plan_option_level_code=PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
SELECT 1
FROM pa_projects_all
WHERE project_id = c_project_id
AND projfunc_currency_code = c_currency_code;
SELECT 1
FROM pa_fp_txn_currencies
WHERE fin_plan_version_id = c_fin_plan_version_id
AND txn_currency_code = c_currency_code;
SELECT pt.plan_class_code
,bv.etc_start_date
FROM pa_budget_versions bv,
pa_fin_plan_types_b pt
WHERE bv.budget_version_id = c_budget_version_id
AND pt.fin_plan_type_id = bv.fin_plan_type_id;
l_api_name CONSTANT VARCHAR2(30) := 'delete_budget_line';
l_delete_budget_lines_tab SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type();
SAVEPOINT delete_budget_line_pub;
l_module_name := g_module_name || ':Delete_Budget_Line ';
,p_function_name => 'PA_PM_DELETE_BUDGET_LINE'
,x_budget_entry_method_code => l_budget_entry_method_code
,x_resource_list_id => l_resource_list_id
,x_budget_version_id => l_budget_version_id
,x_fin_plan_level_code => l_entry_level_code
,x_time_phased_code => l_time_phased_code
,x_plan_in_multi_curr_flag => l_multi_curr_flag
,x_budget_amount_code => l_budget_amount_code
,x_categorization_code => l_categorization_code
,x_project_number => l_amg_project_number
/* Plan Amount Entry flags introduced by bug 6408139 */
/*Passing all as G_PA_MISS_CHAR since validations not required*/
,px_raw_cost_flag => l_pa_miss_char
,px_burdened_cost_flag => l_pa_miss_char
,px_revenue_flag => l_pa_miss_char
,px_cost_qty_flag => l_pa_miss_char
,px_revenue_qty_flag => l_pa_miss_char
,px_all_qty_flag => l_pa_miss_char
,px_bill_rate_flag => l_pa_miss_char
,px_cost_rate_flag => l_pa_miss_char
,px_burden_rate_flag => l_pa_miss_char
/* Plan Amount Entry flags introduced by bug 6408139 */
,x_msg_count => p_msg_count
,x_msg_data => p_msg_data
,x_return_status => p_return_status );
/*FPB2: MRC PA_BUDGET_LINES_V_PKG.delete_row( l_budget_line_rowid ); */
pa_budget_lines_v_pkg.delete_row(X_Rowid => l_budget_line_rowid);
, p_procedure_name => 'DELETE_ROW'
, p_error_text => SQLCODE );
l_delete_budget_lines_tab.extend(1);
l_delete_budget_lines_tab(i) := 'Y';
PA_FP_PLANNING_TRANSACTION_PUB.delete_planning_transactions
(p_context => l_plan_class_code
,p_task_or_res => 'ASSIGNMENT'
,p_resource_assignment_tbl => l_resource_assignment_tab
,p_currency_code_tbl => l_currency_code_tbl
,x_return_status => p_return_status
,x_msg_count => p_msg_count
,x_msg_data => p_msg_data
);
pa_debug.g_err_stage := 'PA_FP_PLANNING_TRANSACTION_PUB.delete_planning_transactions API has thrown error';
,p_delete_budget_lines_tab => l_delete_budget_lines_tab
,p_txn_currency_code_tab => l_txn_currency_code_tab
,p_line_start_date_tab => l_line_start_date_tab
,p_line_end_date_tab => l_line_end_date_tab
,x_return_status => p_return_status
,x_msg_count => p_msg_count
,x_msg_data => p_msg_data );
END IF;--end of code to delete budget line
ROLLBACK TO delete_budget_line_pub;
ROLLBACK TO delete_budget_line_pub;
ROLLBACK TO delete_budget_line_pub;
ROLLBACK TO delete_budget_line_pub;
ROLLBACK TO delete_budget_line_pub;
END delete_budget_line;
PROCEDURE update_budget
( p_api_version_number IN NUMBER
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_budget_type_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_change_reason_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute_category IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute1 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute3 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute4 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute5 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute6 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute7 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute8 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute9 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute10 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute11 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute12 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute13 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute14 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute15 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_budget_lines_in IN budget_line_in_tbl_type
,p_budget_lines_out OUT NOCOPY budget_line_out_tbl_type
--Added for the bug 3453650
,p_resource_list_id IN pa_budget_versions.resource_list_id%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_set_current_working_flag IN pa_budget_versions.current_working_flag%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
-- ,p_locked_by_person_id IN pa_budget_versions.locked_by_person_id%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_budget_version_number IN pa_budget_versions.version_number%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_budget_version_name IN pa_budget_versions.version_name%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_version_type IN pa_budget_versions.version_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR -- 3453650
,p_finplan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_finplan_type_name IN pa_fin_plan_types_vl.name%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_plan_in_multi_curr_flag IN pa_proj_fp_options.plan_in_multi_curr_flag%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_time_phased_code IN pa_proj_fp_options.cost_time_phased_code%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_projfunc_cost_rate_type IN pa_proj_fp_options.projfunc_cost_rate_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_projfunc_cost_rate_date_typ IN pa_proj_fp_options.projfunc_cost_rate_date_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_projfunc_cost_rate_date IN pa_proj_fp_options.projfunc_cost_rate_date%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_projfunc_cost_exchange_rate IN pa_budget_lines.projfunc_cost_exchange_rate%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_projfunc_rev_rate_type IN pa_proj_fp_options.projfunc_rev_rate_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_projfunc_rev_rate_date_typ IN pa_proj_fp_options.projfunc_rev_rate_date_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_projfunc_rev_rate_date IN pa_proj_fp_options.projfunc_rev_rate_date%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_projfunc_rev_exchange_rate IN pa_budget_lines.projfunc_cost_exchange_rate%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_project_cost_rate_type IN pa_proj_fp_options.project_cost_rate_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_project_cost_rate_date_typ IN pa_proj_fp_options.project_cost_rate_date_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_project_cost_rate_date IN pa_proj_fp_options.project_cost_rate_date%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_project_cost_exchange_rate IN pa_budget_lines.project_cost_exchange_rate%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_project_rev_rate_type IN pa_proj_fp_options.project_rev_rate_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_project_rev_rate_date_typ IN pa_proj_fp_options.project_rev_rate_date_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_project_rev_rate_date IN pa_proj_fp_options.project_rev_rate_date%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_project_rev_exchange_rate IN pa_budget_lines.project_rev_exchange_rate%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
/* Plan Amount Entry flags introduced by bug 6408139 */
,p_raw_cost_flag IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_burdened_cost_flag IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_revenue_flag IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_cost_qty_flag IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_revenue_qty_flag IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_all_qty_flag IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_bill_rate_flag IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_cost_rate_flag IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_burden_rate_flag IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
)
IS
--needed to check the validity of the incoming budget type
CURSOR l_budget_type_csr
(p_budget_type_code VARCHAR2 )
IS
SELECT budget_amount_code
FROM pa_budget_types
WHERE budget_type_code = p_budget_type_code;
SELECT 'X'
FROM pa_budget_lines
WHERE resource_assignment_id = p_resource_assigment_id
AND start_date = p_budget_start_date;
SELECT budget_version_id
, budget_entry_method_code
, resource_list_id
, change_reason_code
, description
FROM pa_budget_versions
WHERE project_id = p_project_id
AND budget_type_code = p_budget_type_code
AND budget_status_code = 'W';
SELECT time_phased_type_code
, entry_level_code
, categorization_code
FROM pa_budget_entry_methods
WHERE budget_entry_method_code = p_budget_entry_method_code;
SELECT resource_assignment_id
FROM pa_resource_assignments
WHERE budget_version_id = p_budget_version_id
AND task_id = p_task_id
AND resource_list_member_id = p_member_id;
SELECT trunc(period_start_date)
FROM pa_budget_periods_v
WHERE period_name = p_period_name
AND period_type_code = p_period_type_code;
SELECT prlm.resource_list_member_id
FROM pa_resource_lists prl
, pa_resource_list_members prlm
WHERE prl.resource_list_id = prlm.resource_list_id
AND prl.uncategorized_flag='Y';
SELECT prlm.resource_list_member_id
FROM pa_resource_lists prl
, pa_resource_list_members prlm
WHERE prl.resource_list_id = prlm.resource_list_id
AND prl.uncategorized_flag='Y'
and prlm.resource_class_code = 'FINANCIAL_ELEMENTS';
SELECT 'x'
FROM pa_lookups
WHERE lookup_type = 'BUDGET CHANGE REASON'
AND lookup_code = p_change_reason_code;
SELECT 'x'
FROM pa_budget_versions bv
, pa_resource_assignments ra
, pa_budget_lines bl
WHERE bv.budget_version_id = p_budget_version_id
AND bv.budget_version_id = ra.budget_version_id (+)
AND ra.resource_assignment_id = bl.resource_assignment_id (+)
AND bv.ci_id IS NULL -- -- Added an extra clause bv.ci_id IS NULL--Bug # 3507156
FOR UPDATE OF bv.budget_version_id,ra.budget_version_id,bl.resource_assignment_id NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'update_budget';
l_update_yes_flag VARCHAR2(1);
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT task_number
FROM pa_tasks p
WHERE p.task_id = p_pa_task_id;
SELECT BUDGET_VERSION_ID,version_name,budget_entry_method_code,
RESOURCE_LIST_ID,CHANGE_REASON_CODE,DESCRIPTION
FROM PA_BUDGET_VERSIONS
--WHERE VERSION_NAME=p_budget_version_number
Where budget_type_code=p_budget_type_code
and project_id = p_project_id
and budget_status_code = 'W';
SELECT version_name,CURRENT_WORKING_FLAG,
RESOURCE_LIST_ID,CHANGE_REASON_CODE,DESCRIPTION,version_type
FROM PA_BUDGET_VERSIONS
WHERE budget_version_id = c_budget_version_id;
SELECT name
FROM pa_fin_plan_types_vl
WHERE fin_plan_type_id=p_finplan_type_id;
SELECT name
FROM pa_resource_lists
WHERE p_resource_list_id=p_resource_list_id;
select budget_status_code
from pa_budget_versions
where budget_version_id = p_budget_version_id;
SELECT 'X'
FROM pa_budget_lines
WHERE resource_assignment_id = p_resource_assigment_id
AND start_date = p_budget_start_date
AND txn_currency_code = p_txn_currency_code;
select attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
from pa_budget_versions
where budget_version_id = p_budget_version_id;
SAVEPOINT update_budget_pub;
/* The G_MISS_XXX/null handling for these variables below has been deleted from the existing code in update_budget
. This handling would be done in validate_header_info now */
l_project_id := p_pa_project_id;
,p_function_name => 'PA_PM_UPDATE_BUDGET'
,x_budget_entry_method_code => l_budget_entry_method_code
,x_resource_list_id => lx_resource_list_id
,x_budget_version_id => l_budget_version_id
,x_fin_plan_level_code => l_fin_plan_level_code
,x_time_phased_code => lx_time_phased_type_code
,x_plan_in_multi_curr_flag => lx_plan_in_multi_curr_flag
,x_budget_amount_code => l_budget_amount_code
,x_categorization_code => l_categorization_code
,x_project_number => l_amg_project_number
/* Plan Amount Entry flags introduced by bug 6408139 */
,px_raw_cost_flag => lx_raw_cost_flag
,px_burdened_cost_flag => lx_burdened_cost_flag
,px_revenue_flag => lx_revenue_flag
,px_cost_qty_flag => lx_cost_qty_flag
,px_revenue_qty_flag => lx_revenue_qty_flag
,px_all_qty_flag => lx_all_qty_flag
,px_bill_rate_flag => lx_bill_rate_flag
,px_cost_rate_flag => lx_cost_rate_flag
,px_burden_rate_flag => lx_burden_rate_flag
/* Plan Amount Entry flags introduced by bug 6408139 */
,x_msg_count => p_msg_count
,x_msg_data => p_msg_data
,x_return_status => p_return_status );
pa_debug.write('UPDATE_BUDGET: ' || g_module_name,pa_debug.g_err_stage,5);
l_statement := ' UPDATE PA_BUDGET_VERSIONS SET ';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
IF l_update_yes_flag = 'Y'
THEN
l_statement := l_statement ||
' LAST_UPDATE_DATE = '||''''||
SYSDATE||''''||',';
' LAST_UPDATED_BY = '||G_USER_ID||',';
' LAST_UPDATE_LOGIN = '||G_LOGIN_ID;
pa_debug.write('UPDATE_BUDGET: ' || g_module_name,pa_debug.g_err_stage,3);
pa_debug.write('UPDATE_BUDGET: ' || g_module_name,pa_debug.g_err_stage,3);
pa_debug.write('UPDATE_BUDGET: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
pa_debug.write('UPDATE_BUDGET: ' || g_module_name,pa_debug.g_err_stage,3);
pa_debug.write('UPDATE_BUDGET: ' || g_module_name,pa_debug.g_err_stage,3);
UPDATE pa_proj_fp_options
SET cost_amount_set_id = l_amount_set_id
WHERE proj_fp_options_id = l_proj_fp_options_id_new;
UPDATE pa_proj_fp_options
SET revenue_amount_set_id = l_amount_set_id
WHERE proj_fp_options_id = l_proj_fp_options_id_new;
UPDATE pa_proj_fp_options
SET all_amount_set_id = l_amount_set_id
WHERE proj_fp_options_id = l_proj_fp_options_id_new;
SELECT p.project_currency_code
,p.projfunc_currency_code
INTO l_project_currency_code
,l_projfunc_currency_code
FROM pa_projects_all p
WHERE p.project_id = l_project_id;
SELECT projfunc_cost_rate_type
,projfunc_rev_rate_type
,project_cost_rate_type
,project_rev_rate_type
INTO l_projfunc_cost_rate_type
,l_projfunc_rev_rate_type
,l_project_cost_rate_type
,l_project_rev_rate_type
FROM pa_proj_fp_options
WHERE project_id = l_project_id
AND fin_plan_type_id=l_fin_plan_type_id
AND fin_plan_version_id IS NULL
AND fin_plan_option_level_code= PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
update pa_proj_fp_options
set
projfunc_cost_rate_type = decode(p_projfunc_cost_rate_type, PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR, projfunc_cost_rate_type , p_projfunc_cost_rate_type)
,projfunc_cost_rate_date_type = decode(p_projfunc_cost_rate_date_typ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR, projfunc_cost_rate_date_type, p_projfunc_cost_rate_date_typ)
,projfunc_cost_rate_date = decode(p_projfunc_cost_rate_date, PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE, projfunc_cost_rate_date , p_projfunc_cost_rate_date)
,projfunc_rev_rate_type = decode(p_projfunc_rev_rate_type, PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR, projfunc_rev_rate_type , p_projfunc_rev_rate_type)
,projfunc_rev_rate_date_type = decode(p_projfunc_rev_rate_date_typ, PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR, projfunc_rev_rate_date_type , p_projfunc_rev_rate_date_typ)
,projfunc_rev_rate_date = decode(p_projfunc_rev_rate_date, PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE, projfunc_rev_rate_date , p_projfunc_rev_rate_date)
,project_cost_rate_type = decode(p_project_cost_rate_type, PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR, project_cost_rate_type , p_project_cost_rate_type)
,project_cost_rate_date_type = decode(p_project_cost_rate_date_typ, PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR, project_cost_rate_date_type , p_project_cost_rate_date_typ)
,project_cost_rate_date = decode(p_project_cost_rate_date, PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE, project_cost_rate_date , p_project_cost_rate_date)
,project_rev_rate_type = decode(p_project_rev_rate_type, PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR, project_rev_rate_type , p_project_rev_rate_type)
,project_rev_rate_date_type = decode(p_project_rev_rate_date_typ, PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR, project_rev_rate_date_type , p_project_rev_rate_date_typ)
,project_rev_rate_date = decode(p_project_rev_rate_date, PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE, project_rev_rate_date , p_project_rev_rate_date)
,record_version_number = record_version_number +1
,last_update_date = SYSDATE
,last_updated_by = G_USER_ID
,last_update_login = G_LOGIN_ID
where project_id = l_project_id
and fin_plan_type_id = l_fin_plan_type_id
and fin_plan_version_id = l_budget_version_id;
pa_budget_pvt.insert_budget_line
( p_return_status => l_return_status
,p_pa_project_id => l_project_id
,p_budget_type_code => l_budget_type_code
,p_pa_task_id => l_budget_line_in_rec.pa_task_id
,p_pm_task_reference => l_budget_line_in_rec.pm_task_reference
,p_resource_alias => l_budget_alias
,p_member_id => l_resource_list_member_id
,p_budget_start_date => l_budget_line_in_rec.budget_start_date
,p_budget_end_date => l_budget_line_in_rec.budget_end_date
,p_period_name => l_budget_line_in_rec.period_name
,p_description => l_budget_line_in_rec.description
,p_raw_cost => l_budget_line_in_rec.raw_cost
,p_burdened_cost => l_budget_line_in_rec.burdened_cost
,p_revenue => l_budget_line_in_rec.revenue
,p_quantity => l_budget_line_in_rec.quantity
,p_pm_product_code => l_budget_line_in_rec.pm_product_code
,p_pm_budget_line_reference => l_budget_line_in_rec.pm_budget_line_reference
,p_resource_list_id => lx_resource_list_id
,p_attribute_category => l_budget_line_in_rec.attribute_category
,p_attribute1 => l_budget_line_in_rec.attribute1
,p_attribute2 => l_budget_line_in_rec.attribute2
,p_attribute3 => l_budget_line_in_rec.attribute3
,p_attribute4 => l_budget_line_in_rec.attribute4
,p_attribute5 => l_budget_line_in_rec.attribute5
,p_attribute6 => l_budget_line_in_rec.attribute6
,p_attribute7 => l_budget_line_in_rec.attribute7
,p_attribute8 => l_budget_line_in_rec.attribute8
,p_attribute9 => l_budget_line_in_rec.attribute9
,p_attribute10 => l_budget_line_in_rec.attribute10
,p_attribute11 => l_budget_line_in_rec.attribute11
,p_attribute12 => l_budget_line_in_rec.attribute12
,p_attribute13 => l_budget_line_in_rec.attribute13
,p_attribute14 => l_budget_line_in_rec.attribute14
,p_attribute15 => l_budget_line_in_rec.attribute15
,p_time_phased_type_code => lx_time_phased_type_code
,p_entry_level_code => l_entry_level_code
,p_budget_amount_code => l_budget_amount_code
,p_budget_entry_method_code => l_budget_entry_method_code
,p_categorization_code => l_categorization_code
,p_budget_version_id => l_budget_version_id
,p_change_reason_code => l_budget_line_in_rec.change_reason_code );
ELSE -- If the line is already existing, then call update_budget_line_sql
-- ELSE for the condition : (l_dummy <> 'X' OR l_dummy IS NULL) OR (l
/*Note carefully that while making the comparision below we are reading from p_budget_lines_in and not from
*. l_budget_lines_in. l_budget_lines is an o/p parameter of validate_budget_lines call to which is made
* above in the code flow. And before calling validate_budget_lines G_MISS_XXX handling is done for the values
* present in l_budget_lines. So we cant use l_budget_lines in making the comparision again*/
IF p_budget_lines_in(l_budget_line_index).raw_cost = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
l_budget_line_in_rec.raw_cost := NULL;
PA_BUDGET_PVT.UPDATE_BUDGET_LINE_SQL
( p_return_status => l_return_status
--,p_budget_amount_code => l_budget_amount_code
,p_budget_entry_method_code => l_budget_entry_method_code
,p_resource_assignment_id => l_resource_assignment_id
,p_start_date => l_budget_start_date
,p_time_phased_type_code => lx_time_phased_type_code
,p_description => l_budget_line_in_rec.description
,p_quantity => l_budget_line_in_rec.quantity
,p_raw_cost => l_budget_line_in_rec.raw_cost
,p_burdened_cost => l_budget_line_in_rec.burdened_cost
,p_revenue => l_budget_line_in_rec.revenue
,p_change_reason_code => l_budget_line_in_rec.change_reason_code
,p_attribute_category => l_budget_line_in_rec.attribute_category
,p_attribute1 => l_budget_line_in_rec.attribute1
,p_attribute2 => l_budget_line_in_rec.attribute2
,p_attribute3 => l_budget_line_in_rec.attribute3
,p_attribute4 => l_budget_line_in_rec.attribute4
,p_attribute5 => l_budget_line_in_rec.attribute5
,p_attribute6 => l_budget_line_in_rec.attribute6
,p_attribute7 => l_budget_line_in_rec.attribute7
,p_attribute8 => l_budget_line_in_rec.attribute8
,p_attribute9 => l_budget_line_in_rec.attribute9
,p_attribute10 => l_budget_line_in_rec.attribute10
,p_attribute11 => l_budget_line_in_rec.attribute11
,p_attribute12 => l_budget_line_in_rec.attribute12
,p_attribute13 => l_budget_line_in_rec.attribute13
,p_attribute14 => l_budget_line_in_rec.attribute14
,p_attribute15 => l_budget_line_in_rec.attribute15
);
ELSE -- l_budget_type_code IS NOT NULL (Inserting lines for the finplan model)
--Checking it its a new budget line.
IF (l_dummy <> 'X' OR l_dummy IS NULL) OR (l_new_resource_assignment) OR (lx_resource_list_id <> l_resource_list_id) THEN
--This is a new line case.
--Get the uncategorized resource list info.
pa_fin_plan_utils.Get_Uncat_Resource_List_Info
(x_resource_list_id => l_uncategorized_res_list_id,
x_resource_list_member_id => l_uncategorized_rlmid,
x_track_as_labor_flag => l_unc_track_as_labor_flag,
x_unit_of_measure => l_unc_unit_of_measure,
x_return_status => p_return_status,
x_msg_count => p_msg_count,
x_msg_data => p_msg_data);
SELECT pr.unit_of_measure
,prlm.track_as_labor_flag
INTO l_unit_of_measure
,l_track_as_labor_flag
FROM pa_resources pr
,pa_resource_lists prl
,pa_resource_list_members prlm
WHERE prl.resource_list_id = lx_resource_list_id -- bug 3453650
AND pr.resource_id = prlm.resource_id
AND prl.resource_list_id = prlm.resource_list_id
AND prlm.resource_list_member_id = l_budget_lines_in(i).resource_list_member_id;
pa_debug.write('UPDATE_BUDGET: ' || g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
ROLLBACK TO update_budget_pub;
ROLLBACK TO update_budget_pub;
ROLLBACK TO update_budget_pub;
ROLLBACK TO update_budget_pub;
ROLLBACK TO update_budget_pub;
END update_budget;
PROCEDURE execute_update_budget
( p_api_version_number IN NUMBER
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_budget_type_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_change_reason_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute_category IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute1 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute3 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute4 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute5 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute6 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute7 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute8 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute9 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute10 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute11 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute12 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute13 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute14 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute15 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
--Added for the bug 3453650
,p_resource_list_id IN pa_budget_versions.resource_list_id%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_set_current_working_flag IN pa_budget_versions.current_working_flag%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_budget_version_number IN pa_budget_versions.version_number%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_budget_version_name IN pa_budget_versions.version_name%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_version_type IN pa_budget_versions.version_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_finplan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_plan_in_multi_curr_flag IN pa_proj_fp_options.plan_in_multi_curr_flag%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_time_phased_code IN pa_proj_fp_options.cost_time_phased_code%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_projfunc_cost_rate_type IN pa_proj_fp_options.projfunc_cost_rate_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_projfunc_cost_rate_date_typ IN pa_proj_fp_options.projfunc_cost_rate_date_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_projfunc_cost_rate_date IN pa_proj_fp_options.projfunc_cost_rate_date%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_projfunc_cost_exchange_rate IN pa_budget_lines.projfunc_cost_exchange_rate%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_projfunc_rev_rate_type IN pa_proj_fp_options.projfunc_rev_rate_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_projfunc_rev_rate_date_typ IN pa_proj_fp_options.projfunc_rev_rate_date_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_projfunc_rev_rate_date IN pa_proj_fp_options.projfunc_rev_rate_date%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_projfunc_rev_exchange_rate IN pa_budget_lines.projfunc_cost_exchange_rate%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_project_cost_rate_type IN pa_proj_fp_options.project_cost_rate_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_project_cost_rate_date_typ IN pa_proj_fp_options.project_cost_rate_date_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_project_cost_rate_date IN pa_proj_fp_options.project_cost_rate_date%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_project_cost_exchange_rate IN pa_budget_lines.project_cost_exchange_rate%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_project_rev_rate_type IN pa_proj_fp_options.project_rev_rate_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_project_rev_rate_date_typ IN pa_proj_fp_options.project_rev_rate_date_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_project_rev_rate_date IN pa_proj_fp_options.project_rev_rate_date%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_project_rev_exchange_rate IN pa_budget_lines.project_rev_exchange_rate%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'execute_update_budget';
SAVEPOINT execute_update_budget_pub;
/* -- dbms_output.put_line('Before update_budget'); */
update_budget( p_api_version_number => p_api_version_number
,p_commit => FND_API.G_FALSE
,p_init_msg_list => FND_API.G_FALSE
,p_msg_count => p_msg_count
,p_msg_data => p_msg_data
,p_return_status => l_return_status
,p_pm_product_code => p_pm_product_code
,p_pa_project_id => p_pa_project_id
,p_pm_project_reference => p_pm_project_reference
,p_budget_type_code => p_budget_type_code
,p_change_reason_code => p_change_reason_code
,p_description => p_description
,p_budget_lines_in => G_budget_lines_in_tbl
,p_budget_lines_out => G_budget_lines_out_tbl
-- Added for bug 4224464
--Added the new parameters for the bug 3453650
,p_resource_list_id => p_resource_list_id
,p_set_current_working_flag => p_set_current_working_flag
,p_budget_version_number => p_budget_version_number
,p_budget_version_name => p_budget_version_name
,p_version_type => p_version_type -- 3453650
,p_finplan_type_id => p_finplan_type_id
,p_plan_in_multi_curr_flag => p_plan_in_multi_curr_flag
,p_time_phased_code => p_time_phased_code
,p_projfunc_cost_rate_type => p_projfunc_cost_rate_type
,p_projfunc_cost_rate_date_typ => p_projfunc_cost_rate_date_typ
,p_projfunc_cost_rate_date => p_projfunc_cost_rate_date
,p_projfunc_cost_exchange_rate => p_projfunc_cost_exchange_rate
,p_projfunc_rev_rate_type => p_projfunc_rev_rate_type
,p_projfunc_rev_rate_date_typ => p_projfunc_rev_rate_date_typ
,p_projfunc_rev_rate_date => p_projfunc_rev_rate_date
,p_projfunc_rev_exchange_rate => p_projfunc_rev_exchange_rate
,p_project_cost_rate_type => p_project_cost_rate_type
,p_project_cost_rate_date_typ => p_project_cost_rate_date_typ
,p_project_cost_rate_date => p_project_cost_rate_date
,p_project_cost_exchange_rate => p_project_cost_exchange_rate
,p_project_rev_rate_type => p_project_rev_rate_type
,p_project_rev_rate_date_typ => p_project_rev_rate_date_typ
,p_project_rev_rate_date => p_project_rev_rate_date
,p_project_rev_exchange_rate => p_project_rev_exchange_rate );
/* -- dbms_output.put_line('After update_budget'); */
/* -- dbms_output.put_line('Return status update_budget: '||l_return_status); */
/* -- dbms_output.put_line('handling an G_EXC_ERROR exception in execute_update_budget'); */
ROLLBACK TO execute_update_budget_pub;
ROLLBACK TO execute_update_budget_pub;
/* -- dbms_output.put_line('handling an OTHERS exception in execute_update_budget'); */
ROLLBACK TO execute_update_budget_pub;
END execute_update_budget;
PROCEDURE update_budget_line
( p_api_version_number IN NUMBER
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
,p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_budget_type_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pm_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_resource_alias IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_resource_list_member_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_budget_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_budget_end_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_period_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_raw_cost IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_burdened_cost IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_revenue IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_quantity IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_attribute_category IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute1 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute3 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute4 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute5 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute6 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute7 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute8 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute9 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute10 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute11 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute12 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute13 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute14 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_attribute15 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
--Parameters added for FP.M
,p_fin_plan_type_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_fin_plan_type_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_version_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_version_number IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_currency_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_change_reason_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_projfunc_cost_rate_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_projfunc_cost_rate_date_typ IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_projfunc_cost_rate_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_projfunc_cost_exchange_rate IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_projfunc_rev_rate_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_projfunc_rev_rate_date_typ IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_projfunc_rev_rate_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_projfunc_rev_exchange_rate IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_project_cost_rate_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_project_cost_rate_date_typ IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_project_cost_rate_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_project_cost_exchange_rate IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_project_rev_rate_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_project_rev_rate_date_typ IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_project_rev_rate_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
,p_project_rev_exchange_rate IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
)
IS
--needed to get the resource assignment for this budget_version / task / member combination
CURSOR l_resource_assignment_csr
(p_budget_version_id NUMBER
,p_task_id NUMBER
,p_member_id NUMBER )
IS
SELECT resource_assignment_id
FROM pa_resource_assignments
WHERE budget_version_id = p_budget_version_id
AND task_id = p_task_id
AND resource_list_member_id = p_member_id;
SELECT rowidtochar(rowid)
,budget_line_id
FROM pa_budget_lines
WHERE resource_assignment_id = p_resource_assigment_id
AND trunc(start_date) = nvl(trunc(p_budget_start_date),trunc(start_date))
AND txn_currency_code = nvl(p_currency_code,txn_currency_code);
SELECT 'x'
FROM pa_budget_lines
WHERE rowid = p_budget_line_rowid
FOR UPDATE NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'update_budget_line';
SAVEPOINT update_budget_line_pub;
l_module_name := g_module_name || ':Update_Budget_Line ';
,p_function_name => 'PA_PM_UPDATE_BUDGET_LINE'
,x_budget_entry_method_code => l_budget_entry_method_code
,x_resource_list_id => l_resource_list_id
,x_budget_version_id => l_budget_version_id
,x_fin_plan_level_code => l_entry_level_code
,x_time_phased_code => l_time_phased_code
,x_plan_in_multi_curr_flag => l_multi_curr_flag
,x_budget_amount_code => l_budget_amount_code
,x_categorization_code => l_categorization_code
,x_project_number => l_amg_project_number
/* Plan Amount Entry flags introduced by bug 6408139 */
/*Passing all as G_PA_MISS_CHAR since validations not required*/
,px_raw_cost_flag => l_pa_miss_char
,px_burdened_cost_flag => l_pa_miss_char
,px_revenue_flag => l_pa_miss_char
,px_cost_qty_flag => l_pa_miss_char
,px_revenue_qty_flag => l_pa_miss_char
,px_all_qty_flag => l_pa_miss_char
,px_bill_rate_flag => l_pa_miss_char
,px_cost_rate_flag => l_pa_miss_char
,px_burden_rate_flag => l_pa_miss_char
/* Plan Amount Entry flags introduced by bug 6408139 */
,x_msg_count => p_msg_count
,x_msg_data => p_msg_data
,x_return_status => p_return_status );
pa_budget_pvt.update_budget_line_sql
( p_return_status => p_return_status
,p_budget_entry_method_code => l_budget_entry_method_code
,p_resource_assignment_id => l_resource_assignment_id
,p_start_date => l_budget_lines_in(1).budget_start_date
,p_time_phased_type_code => l_time_phased_code
,p_description => PA_TASK_ASSIGNMENTS_PVT.pfchar(p_description)
,p_quantity => PA_TASK_ASSIGNMENTS_PVT.pfnum(p_quantity)
,p_raw_cost => PA_TASK_ASSIGNMENTS_PVT.pfnum(p_raw_cost)
,p_burdened_cost => PA_TASK_ASSIGNMENTS_PVT.pfnum(p_burdened_cost)
,p_revenue => PA_TASK_ASSIGNMENTS_PVT.pfnum(p_revenue)
,p_change_reason_code => PA_TASK_ASSIGNMENTS_PVT.pfchar(p_change_reason_code)
,p_attribute_category => PA_TASK_ASSIGNMENTS_PVT.pfchar(p_attribute_category)
,p_attribute1 => PA_TASK_ASSIGNMENTS_PVT.pfchar(p_attribute1)
,p_attribute2 => PA_TASK_ASSIGNMENTS_PVT.pfchar(p_attribute2)
,p_attribute3 => PA_TASK_ASSIGNMENTS_PVT.pfchar(p_attribute3)
,p_attribute4 => PA_TASK_ASSIGNMENTS_PVT.pfchar(p_attribute4)
,p_attribute5 => PA_TASK_ASSIGNMENTS_PVT.pfchar(p_attribute5)
,p_attribute6 => PA_TASK_ASSIGNMENTS_PVT.pfchar(p_attribute6)
,p_attribute7 => PA_TASK_ASSIGNMENTS_PVT.pfchar(p_attribute7)
,p_attribute8 => PA_TASK_ASSIGNMENTS_PVT.pfchar(p_attribute8)
,p_attribute9 => PA_TASK_ASSIGNMENTS_PVT.pfchar(p_attribute9)
,p_attribute10 => PA_TASK_ASSIGNMENTS_PVT.pfchar(p_attribute10)
,p_attribute11 => PA_TASK_ASSIGNMENTS_PVT.pfchar(p_attribute11)
,p_attribute12 => PA_TASK_ASSIGNMENTS_PVT.pfchar(p_attribute12)
,p_attribute13 => PA_TASK_ASSIGNMENTS_PVT.pfchar(p_attribute13)
,p_attribute14 => PA_TASK_ASSIGNMENTS_PVT.pfchar(p_attribute14)
,p_attribute15 => PA_TASK_ASSIGNMENTS_PVT.pfchar(p_attribute15)
);
ELSE --insert budget line for new FinPlan model
--Copy the fin plan line data into a table of type pa_fp_rollup_tmp
--If an attribute should not be updated then pass it as null, and
--if an attribute should be updated to null then pass it as FND_API.G_MISS_XXX
l_finplan_lines_tab(1).system_reference1 := l_budget_lines_in(1).pa_task_id;
END IF;--end of code to update budget line
ROLLBACK TO update_budget_line_pub;
ROLLBACK TO update_budget_line_pub;
ROLLBACK TO update_budget_line_pub;
ROLLBACK TO update_budget_line_pub;
ROLLBACK TO update_budget_line_pub;
END update_budget_line;
,p_update_db_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_calc_budget_lines_out OUT NOCOPY calc_budget_line_out_tbl_type
-- Bug 2863564 Parameters added for new Fin Plan Model
,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
,p_fin_plan_type_name IN pa_fin_plan_types_tl.name%TYPE
,p_version_type IN pa_budget_versions.version_type%TYPE
,p_budget_version_number IN pa_budget_versions.version_number%TYPE
) IS
CURSOR l_budget_type_csr
(p_budget_type_code VARCHAR2 )
IS
SELECT 'X'
FROM pa_budget_types
WHERE budget_type_code = p_budget_type_code;
SELECT pm_task_reference,
task_name -- Bug 2863564
FROM pa_tasks
WHERE task_id = p_task_id;
SELECT bl.rowid row_id
,bl.budget_line_id budget_line_id
,DECODE(c_context,l_context_finplan,bl.txn_currency_code,bl.projfunc_currency_code) txn_currency_code
,ra.resource_assignment_id resource_assignment_id
,ra.task_id task_id
,ra.rate_based_flag rate_based_flag
,rlm.resource_list_id resource_list_id
,rlm.resource_list_member_id resource_list_member_id
,rlm.resource_id resource_id
,rlm.alias
,bl.start_date start_date
,bl.end_date end_date
,bl.period_name period_name
,bl.quantity quantity
,bl.display_quantity display_quantity --IPM Arch Enhancement Bug 4865563
,DECODE(c_context,l_context_finplan,bl.txn_raw_cost,bl.raw_cost) txn_raw_cost
,DECODE(c_context,l_context_finplan,bl.txn_burdened_cost,bl.burdened_cost) txn_burdened_cost
,DECODE(c_context,l_context_finplan,bl.txn_revenue,bl.revenue) txn_revenue
,bl.project_raw_cost project_raw_cost
,bl.project_burdened_cost project_burdened_cost
,bl.project_revenue project_revenue
,bl.raw_cost projfunc_raw_cost
,bl.burdened_cost projfunc_burdened_cost
,bl.revenue projfunc_revenue
FROM pa_budget_lines bl,
pa_resource_assignments ra,
pa_resource_list_members rlm
WHERE bl.budget_version_id = c_budget_version_id
AND bl.resource_assignment_id = ra.resource_assignment_id
AND ra.resource_list_member_id = rlm.resource_list_member_id;
SELECT m.entry_level_code
FROM pa_budget_versions v
,pa_budget_entry_methods m
WHERE v.budget_version_id = c_budget_version_id
AND v.budget_entry_method_code = m.budget_entry_method_code
AND v.ci_id IS NULL; -- -- Added an extra clause v.ci_id IS NULL--Bug # 3507156
SELECT segment1
,baseline_funding_flag
FROM pa_projects_all p
WHERE p.project_id = p_pa_project_id;
SELECT 'x'
FROM pa_proj_fp_options pfo
WHERE pfo.project_id = c_project_id
AND pfo.fin_plan_type_id = c_fin_plan_type_id
AND pfo.fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
SELECT bv.project_id project_id
,bv.budget_type_code budget_type_code
,bv.fin_plan_type_id fin_plan_type_id
,bv.version_type version_type
,bv.budget_status_code budget_status_code
,bv.ci_id ci_id -- raja
,pt.fin_plan_type_code fin_plan_type_code
,pt.name fin_plan_type_name
,pa_fin_plan_utils.get_fin_plan_level_code(bv.budget_version_id) plan_level_code
,bv.locked_by_person_id
,bv.request_id
,pt.plan_class_code
,bv.etc_start_date
,nvl(bv.wp_version_flag,'N') wp_version_flag
,bv.plan_processing_code
FROM pa_budget_versions bv,
pa_fin_plan_types_vl pt
WHERE bv.budget_version_id = c_budget_version_id
AND pt.fin_plan_type_id(+) = bv.fin_plan_type_id
AND bv.ci_id IS NULL; -- -- Added an extra clause bv.ci_id IS NULL--Bug # 3507156
SELECT budget_version_id
FROM pa_budget_versions
WHERE project_id = c_project_id
AND budget_type_code = c_budget_type_code
AND budget_status_code = 'W'
AND ci_id IS NULL; -- -- Added an extra clause ci_id IS NULL--Bug # 3507156
SELECT PERIOD_YEAR
FROM pa_budget_periods_v
WHERE period_name = p_period_name
AND period_type_code = p_period_type_code;
IF p_update_db_flag = 'Y' THEN
l_resp_id := FND_GLOBAL.Resp_id;
l_module_name := 'PA_PM_UPDATE_BUDGET_LINE';
p_function_name => 'PA_PM_UPDATE_BUDGET_LINE',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_function_allowed => l_function_allowed );
IF p_update_db_flag = 'Y' THEN
-- Now verify whether project security allows the user to update
-- project
-- If a user does not have privileges to update the project, then
-- cannot update the budget line
IF pa_security.allow_query (x_project_id => l_project_id ) = 'N' THEN
-- The user does not have query privileges on this project
-- Hence, cannot update the project.Raise error
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_PROJECT_SECURITY_ENFORCED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'GENERAL'
,p_attribute1 => ''
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
IF pa_security.allow_update (x_project_id => l_project_id ) = 'N' THEN
-- The user does not have update privileges on this project
-- Hence , raise error
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_PROJECT_SECURITY_ENFORCED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'GENERAL'
,p_attribute1 => ''
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
SELECT fin_plan_type_code
,name
, use_for_workplan_flag
INTO l_fin_plan_type_code
,l_fin_plan_type_name
,l_workplan_flag
FROM pa_fin_plan_types_vl
WHERE fin_plan_type_id = l_fin_plan_type_id;
IF p_update_db_flag = 'Y'
THEN
l_module_name := 'PA_PM_UPDATE_BUDGET_LINE'; --4615645.
IF p_update_db_flag = 'Y' AND l_context = l_context_finplan THEN
-- Fetch the record version number of the plan version
l_record_version_number :=
PA_FIN_PLAN_UTILS.RETRIEVE_RECORD_VERSION_NUMBER(l_budget_version_id);
IF ( p_update_db_flag = 'Y' )
THEN
-- UPDATE MODE
IF ( p_budget_type_code IS NOT NULL ) -- budget_type_code model
THEN
-- Test for Budgetary Control
--Check if budgetary control is enabled for the given project and
--budget type code.
PA_BUDGET_FUND_PKG.get_budget_ctrl_options
( p_project_Id => l_project_id
, p_budget_type_code => p_budget_type_code
, p_calling_mode => 'BUDGET'
, x_fck_req_flag => l_fck_req_flag
, x_bdgt_intg_flag => l_bdgt_intg_flag
, x_bdgt_ver_id => l_bdgt_ver_id
, x_encum_type_id => l_encum_type_id
, x_balance_type => l_balance_type
, x_return_status => p_return_status
, x_msg_data => p_msg_data
, x_msg_count => p_msg_count
);
END IF; -- p_update_db_flag = 'Y'
IF p_update_db_flag = 'Y' THEN
IF p_multiple_task_msg <> 'F' THEN
IF l_context = l_context_budget
THEN
FORALL i IN l_rowid_tbl.first .. l_rowid_tbl.last
UPDATE pa_budget_lines
SET raw_cost = l_txn_raw_cost_tbl(i),
burdened_cost = l_txn_burdened_cost_tbl(i),
revenue = l_txn_revenue_tbl(i),
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
WHERE rowid = l_rowid_tbl(i);
UPDATE pa_budget_lines
SET quantity = l_quantity_tbl(i),
display_quantity = l_display_quantity_tbl(i), --IPM Arch Enhancements Bug 4865563
txn_raw_cost = l_txn_raw_cost_tbl(i),
txn_burdened_cost = l_txn_burdened_cost_tbl(i),
txn_revenue = l_txn_revenue_tbl(i),
txn_cost_rate_override = decode(l_rw_cost_rate_override_tbl(i), null, txn_cost_rate_override,
l_rw_cost_rate_override_tbl(i)),
burden_cost_rate_override = decode(l_burden_cst_rate_override_tbl(i), null, burden_cost_rate_override,
l_burden_cst_rate_override_tbl(i)),
txn_bill_rate_override = decode (l_bill_rate_override_tbl(i), null, txn_bill_rate_override,
l_bill_rate_override_tbl(i)),
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
WHERE rowid = l_rowid_tbl(i);
IF p_update_db_flag = 'Y' THEN
IF l_context = l_context_budget THEN --Bug 2863564
-- Bug 2863564
-- Call to MRC apis should be done only in case of old 'BUDGETS' model
-- for NEW Financial Planning Model, this would be taken care by
-- the api pa_fp_edit_line_pkg. PROCESS_BDGTLINES_FOR_VERSION
-- Bug Fix: 4569365. Removed MRC code.
/*
IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS IS NULL
THEN
PA_MRC_FINPLAN.CHECK_MRC_INSTALL
(x_return_status => p_return_status,
x_msg_count => p_msg_count,
x_msg_data => p_msg_data);
PJI_FM_XBS_ACCUM_MAINT.PLAN_DELETE (
p_fp_version_ids => l_budget_version_id_tbl,
x_return_status => p_return_status,
x_msg_code => p_msg_data);
p_calc_budget_lines_out.delete;
G_calc_budget_lines_out_tbl.delete;
,p_update_db_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
-- Bug 2863564 Parameters required for new Fin Plan Model
,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
,p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE
,p_fin_plan_type_name IN pa_fin_plan_types_tl.name%TYPE
,p_version_type IN pa_budget_versions.version_type%TYPE
,p_budget_version_number IN pa_budget_versions.version_number%TYPE
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Execute_Calculate_Amounts';
,p_update_db_flag => p_update_db_flag
,p_calc_budget_lines_out => G_calc_budget_lines_out_tbl
-- Bug 2863564 new parameters added
,p_fin_plan_type_name => p_fin_plan_type_name
,p_fin_plan_type_id => p_fin_plan_type_id
,p_budget_version_number => p_budget_version_number
,p_version_type => p_version_type
,p_budget_version_id => p_budget_version_id
);
pa_debug.g_err_stage := 'Error in lock unlock version - cannot delete working version';
pa_fin_plan_pub.delete_version
( p_project_id => l_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';
SELECT DECODE(l_version_type,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST, cost_amount_set_id,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE, revenue_amount_set_id,
PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_ALL, all_amount_set_id)
INTO l_pt_amount_set_id
FROM pa_proj_fp_options
WHERE project_id=l_project_id
AND fin_plan_type_id=l_fin_plan_type_id
AND fin_plan_version_id IS NULL;
SELECT t.task_number
INTO l_task_number
FROM pa_tasks t
WHERE t.task_id = P_finplan_trans_tab(i).task_id;
SELECT element_version_id
INTO l_task_elem_version_id_tbl(i)
FROM pa_struct_task_wbs_v
WHERE parent_structure_version_id = l_struct_elem_version_id
AND project_id = l_project_id
AND task_id = P_finplan_trans_tab(i).task_id;
Select decode(plan_class_code,
'BUDGET',PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_BUDGET,
'FORECAST',PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_FORECAST)
Into l_plan_tran_context
From pa_fin_plan_types_b
Where fin_plan_type_id = l_fin_plan_type_id;
,p_procedure_name => 'Update_Planning_Transactions');
G_PM_PRODUCT_CODE_TBL.DELETE;
G_TASK_ID_TBL.DELETE;
G_PM_TASK_REFERENCE_TBL.DELETE;
G_PM_RES_ASGMT_REFERENCE_TBL.DELETE;
G_RESOURCE_ALIAS_TBL.DELETE;
G_CURRENCY_CODE_TBL.DELETE;
G_UNIT_OF_MEASURE_CODE_TBL.DELETE;
G_START_DATE_TBL.DELETE;
G_END_DATE_TBL.DELETE;
G_QUANTITY_TBL.DELETE;
G_RAW_COST_TBL.DELETE;
G_BURDENED_COST_TBL.DELETE;
G_REVENUE_TBL.DELETE;
G_RESOURCE_LIST_MEMBER_ID_TBL.DELETE;
G_ATTRIBUTE_CATEGORY_TBL.DELETE;
G_ATTRIBUTE1_TBL.DELETE;
G_ATTRIBUTE2_TBL.DELETE;
G_ATTRIBUTE3_TBL.DELETE;
G_ATTRIBUTE4_TBL.DELETE;
G_ATTRIBUTE5_TBL.DELETE;
G_ATTRIBUTE6_TBL.DELETE;
G_ATTRIBUTE7_TBL.DELETE;
G_ATTRIBUTE8_TBL.DELETE;
G_ATTRIBUTE9_TBL.DELETE;
G_ATTRIBUTE10_TBL.DELETE;
G_ATTRIBUTE11_TBL.DELETE;
G_ATTRIBUTE12_TBL.DELETE;
G_ATTRIBUTE13_TBL.DELETE;
G_ATTRIBUTE14_TBL.DELETE;
G_ATTRIBUTE15_TBL.DELETE;
G_ATTRIBUTE16_TBL.DELETE;
G_ATTRIBUTE17_TBL.DELETE;
G_ATTRIBUTE18_TBL.DELETE;
G_ATTRIBUTE19_TBL.DELETE;
G_ATTRIBUTE20_TBL.DELETE;
G_ATTRIBUTE21_TBL.DELETE;
G_ATTRIBUTE22_TBL.DELETE;
G_ATTRIBUTE23_TBL.DELETE;
G_ATTRIBUTE24_TBL.DELETE;
G_ATTRIBUTE25_TBL.DELETE;
G_ATTRIBUTE26_TBL.DELETE;
G_ATTRIBUTE27_TBL.DELETE;
G_ATTRIBUTE28_TBL.DELETE;
G_ATTRIBUTE29_TBL.DELETE;
G_ATTRIBUTE30_TBL.DELETE;
PROCEDURE update_plannning_element_attr
(p_api_version_number IN NUMBER
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_fin_plan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_fin_plan_type_name IN pa_fin_plan_types_tl.name%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_budget_version_number IN pa_budget_versions.version_number%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_version_type IN pa_budget_versions.version_type%TYPE :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_planning_element_rec_tbl IN planning_element_rec_tbl_type
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2)
IS
CURSOR c_get_plan_class_code(c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE) is
SELECT plan_class_code
FROM pa_fin_plan_types_b
WHERE fin_plan_type_id=c_fin_plan_type_id;
l_module_name VARCHAR2(100) :='update_plannning_element_attr';
l_api_name CONSTANT VARCHAR2(100) := 'update_plannning_element_attr';
pa_debug.set_err_stack('PA_BUDGET_PUB.update_plannning_element_attr');
pa_budget_pvt.G_res_assign_tbl.delete;
,p_function_name => 'PA_PM_UPDATE_BUDGET'
,x_budget_entry_method_code => l_budget_entry_method_code
,x_resource_list_id => l_resource_list_id
,x_budget_version_id => l_budget_version_id
,x_fin_plan_level_code => l_entry_level_code
,x_time_phased_code => l_time_phased_code
,x_plan_in_multi_curr_flag => l_multi_curr_flag
,x_budget_amount_code => l_budget_amount_code
,x_categorization_code => l_categorization_code
,x_project_number => l_project_number
/* Plan Amount Entry flags introduced by bug 6408139 */
/*Passing all as G_PA_MISS_CHAR since validations not required*/
,px_raw_cost_flag => l_pa_miss_char
,px_burdened_cost_flag => l_pa_miss_char
,px_revenue_flag => l_pa_miss_char
,px_cost_qty_flag => l_pa_miss_char
,px_revenue_qty_flag => l_pa_miss_char
,px_all_qty_flag => l_pa_miss_char
,px_bill_rate_flag => l_pa_miss_char
,px_cost_rate_flag => l_pa_miss_char
,px_burden_rate_flag => l_pa_miss_char
/* Plan Amount Entry flags introduced by bug 6408139 */
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status );
(p_calling_context => 'UPDATE_PLANNING_ELEMENT_ATTR'
,p_pa_project_id => l_project_id
,p_budget_type_code => NULL
,p_fin_plan_type_id => l_fin_plan_type_id
,p_version_type => l_version_type
,p_resource_list_id => l_resource_list_id
,p_time_phased_code => l_time_phased_code
,p_budget_entry_method_code => NULL
,p_entry_level_code => l_entry_level_code
,p_allow_qty_flag => null
,p_allow_raw_cost_flag => null
,p_allow_burdened_cost_flag => null
,p_allow_revenue_flag => null
,p_multi_currency_flag => l_multi_curr_flag
,p_project_cost_rate_type => null
,p_project_cost_rate_date_typ => null
,p_project_cost_rate_date => null
,p_project_cost_exchange_rate => null
,p_projfunc_cost_rate_type => null
,p_projfunc_cost_rate_date_typ => null
,p_projfunc_cost_rate_date => null
,p_projfunc_cost_exchange_rate => null
,p_project_rev_rate_type => null
,p_project_rev_rate_date_typ => null
,p_project_rev_rate_date => null
,p_project_rev_exchange_rate => null
,p_projfunc_rev_rate_type => null
,p_projfunc_rev_rate_date_typ => null
,p_projfunc_rev_rate_date => null
,p_projfunc_rev_exchange_rate => null
,p_planning_start_date_tbl => l_planning_start_date_tbl
,p_planning_end_date_tbl => l_planning_end_date_tbl
,p_spread_curve_name_tbl => l_spread_curve_name_tbl
,p_sp_fixed_date_tbl => l_sp_fixed_date_tbl
,p_etc_method_name_tbl => l_etc_method_name_tbl
,p_uom_tbl => l_uom_tbl
,p_mfc_cost_type_tbl => l_mfc_cost_type_tbl
,p_version_info_rec => l_version_info_rec
,px_budget_lines_in => l_budget_lines_in_tbl
,x_budget_lines_out => l_budget_lines_out_tbl
,x_mfc_cost_type_id_tbl => l_mfc_cost_type_id_tbl
,x_etc_method_code_tbl => l_etc_method_code_tbl
,x_spread_curve_id_tbl => l_spread_curve_id_tbl
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
SELECT decode(pf.approved_rev_plan_type_flag,'Y'
,pa.PROJFUNC_CURRENCY_CODE
,pa.PROJECT_CURRENCY_CODE)
INTO l_currency_code
FROM pa_projects_all pa,
pa_proj_fp_options pf
WHERE pa.project_id=l_project_id
AND pa.project_id=pf.project_id
AND pf.fin_plan_version_id=l_budget_version_id;
select resource_assignment_id
into l_resource_assignment_id_tbl(i)
from pa_resource_assignments
where budget_version_id=l_budget_version_id
and task_id=l_budget_lines_in_tbl(i).pa_task_id
and resource_list_member_id=l_budget_lines_in_tbl(i).resource_list_member_id
and project_id=l_project_id
and PROJECT_ASSIGNMENT_ID =-1;
select txn_currency_code into l_currency_code_tbl(i)
from pa_budget_lines
where resource_assignment_id=l_resource_assignment_id_tbl(i)
and rownum=1;
pa_fp_planning_transaction_pub.update_planning_transactions(
p_context => l_fin_plan_class_code
,p_budget_version_id => l_budget_version_id
,p_resource_assignment_id_tbl => l_resource_assignment_id_tbl
,p_planning_start_date_tbl => l_planning_start_date_tbl
,p_planning_end_date_tbl => l_planning_end_date_tbl
,p_mfc_cost_type_id_tbl => l_mfc_cost_type_id_tbl
,p_etc_method_code_tbl => l_etc_method_code_tbl
,p_spread_curve_id_tbl => l_spread_curve_id_tbl
,p_sp_fixed_date_tbl => l_sp_fixed_date_tbl
,p_currency_code_tbl => l_currency_code_tbl
,p_assignment_description_tbl => l_assignment_description_tbl
,p_attribute_category_tbl => l_attribute_category_tbl
,p_attribute1_tbl => l_attribute1_tbl
,p_attribute2_tbl => l_attribute2_tbl
,p_attribute3_tbl => l_attribute3_tbl
,p_attribute4_tbl => l_attribute4_tbl
,p_attribute5_tbl => l_attribute5_tbl
,p_attribute6_tbl => l_attribute6_tbl
,p_attribute7_tbl => l_attribute7_tbl
,p_attribute8_tbl => l_attribute8_tbl
,p_attribute9_tbl => l_attribute9_tbl
,p_attribute10_tbl => l_attribute10_tbl
,p_attribute11_tbl => l_attribute11_tbl
,p_attribute12_tbl => l_attribute12_tbl
,p_attribute13_tbl => l_attribute13_tbl
,p_attribute14_tbl => l_attribute14_tbl
,p_attribute15_tbl => l_attribute15_tbl
,p_attribute16_tbl => l_attribute16_tbl
,p_attribute17_tbl => l_attribute17_tbl
,p_attribute18_tbl => l_attribute18_tbl
,p_attribute19_tbl => l_attribute19_tbl
,p_attribute20_tbl => l_attribute20_tbl
,p_attribute21_tbl => l_attribute21_tbl
,p_attribute22_tbl => l_attribute22_tbl
,p_attribute23_tbl => l_attribute23_tbl
,p_attribute24_tbl => l_attribute24_tbl
,p_attribute25_tbl => l_attribute25_tbl
,p_attribute26_tbl => l_attribute26_tbl
,p_attribute27_tbl => l_attribute27_tbl
,p_attribute28_tbl => l_attribute28_tbl
,p_attribute29_tbl => l_attribute29_tbl
,p_attribute30_tbl => l_attribute30_tbl
,p_pji_rollup_required => 'Y'
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
pa_debug.write_file('Failed due to error in update_planning_transactions',5);
pa_debug.write_file('update_plannning_element_attr ' || x_msg_data,5);
,p_procedure_name => 'update_plannning_element_attr');
pa_debug.write_file('update_plannning_element_attr ' || pa_debug.G_Err_Stack,5);
END update_plannning_element_attr;