[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_update_task_asgmt_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
l_update_count NUMBER;
l_update_task_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
SELECT person_id
FROM pa_resource_txn_attributes
WHERE resource_id = l_assignment_rec.resource_id;
SELECT project_system_status_code
FROM PA_PROJECT_STATUSES
WHERE project_status_code = l_assignment_rec.status_code;
SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
FROM PA_RESOURCE_ASSIGNMENTS ra
,PA_BUDGET_VERSIONS bv
,PA_PROJ_ELEM_VER_STRUCTURE evs
WHERE ra.project_id = bv.project_id
AND bv.project_id = evs.project_id
AND bv.budget_type_code IS NULL -- added for bug 7492618
AND ra.budget_version_id = bv.budget_version_id
AND bv.project_structure_version_id = evs.element_version_id
AND ra.project_id = l_assignment_rec.project_id
AND ra.resource_list_member_id = l_assignment_rec.resource_list_member_id
AND ra.project_assignment_id = -1
-- AND evs.latest_eff_published_flag = 'N'
AND ra.budget_version_id = p_budget_version_id;
SELECT resource_assignment_id, wbs_element_version_id, budget_version_id, project_structure_version_id
FROM
(
(SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
FROM PA_RESOURCE_ASSIGNMENTS ra
,PA_BUDGET_VERSIONS bv
,PA_PROJ_ELEM_VER_STRUCTURE evs
WHERE ra.project_id = bv.project_id
AND bv.project_id = evs.project_id
AND bv.budget_type_code IS NULL -- added for bug 7492618
AND ra.budget_version_id = bv.budget_version_id
AND bv.project_structure_version_id = evs.element_version_id
AND ra.project_id = l_assignment_rec.project_id
AND ra.resource_list_member_id = l_assignment_rec.resource_list_member_id
AND ra.project_assignment_id = -1
AND evs.status_code = 'STRUCTURE_WORKING')
UNION ALL
(SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
FROM PA_RESOURCE_ASSIGNMENTS ra
,PA_BUDGET_VERSIONS bv
,PA_PROJ_ELEM_VER_STRUCTURE evs
,PA_PROJ_WORKPLAN_ATTR pwa
WHERE pwa.wp_enable_Version_flag = 'N'
AND pwa.project_id = ra.project_id
AND pwa.proj_element_id = evs.proj_element_id
AND ra.project_id = bv.project_id
AND bv.project_id = evs.project_id
AND bv.budget_type_code IS NULL -- added for bug 7492618
AND ra.budget_version_id = bv.budget_version_id
AND bv.project_structure_version_id = evs.element_version_id
AND ra.resource_list_member_id = l_assignment_rec.resource_list_member_id
AND ra.project_id = l_assignment_rec.project_id
AND ra.project_assignment_id = -1)
)
ORDER BY budget_version_id, project_structure_version_id;
SELECT resource_organization_id
FROM pa_resources_denorm
WHERE l_assignment_rec.start_date BETWEEN resource_effective_start_date AND resource_effective_end_date
AND resource_id = l_assignment_rec.resource_id;
SELECT 'X'
FROM per_all_people_f
WHERE person_id = l_person_id_tmp
AND effective_start_date <= sysdate
AND rownum = 1;
SELECT location_id
INTO l_assignment_rec.location_id
FROM pa_projects_all
WHERE project_id = l_assignment_rec.project_id;
SELECT proj_asgmt_res_format_id
INTO l_proj_asgmt_res_format_id
FROM PA_PROJECTS_ALL
WHERE project_id = p_assignment_rec.project_id;
SELECT person_id
INTO l_person_id
FROM pa_resources_denorm
WHERE resource_id = l_assignment_rec.resource_id
AND rownum = 1;
SELECT nvl(future_term_wf_flag,'N')
INTO l_future_term_wf_flag
FROM pa_resources
WHERE resource_id = l_assignment_rec.resource_id;
SELECT job_id
INTO l_fcst_job_id_tmp
FROM pa_resources_denorm prd
WHERE prd.person_id = l_person_id_tmp
AND l_assignment_rec.start_date BETWEEN prd.resource_effective_start_date
AND prd.resource_effective_end_date ;
SELECT decode(nvl(peo.employee_number,0), 0,'CWK','EMP')
INTO l_person_type_code
FROM per_all_people_f peo
WHERE peo.person_id = l_person_id_tmp
AND l_assignment_rec.start_date BETWEEN peo.effective_start_date
AND peo.effective_end_date ;
SELECT job_id
INTO l_fcst_job_id_tmp
FROM per_all_assignments_f assn
WHERE assn.person_id = l_person_id_tmp
AND l_assignment_rec.start_date BETWEEN assn.effective_start_date
AND assn.effective_end_date
AND assn.assignment_type in ('C','E')
AND assn.primary_flag = 'Y'
AND ROWNUM = 1;
SELECT decode(peo.current_employee_flag, 'Y', 'EMP', 'CWK')
INTO l_person_type_code
FROM per_all_people_f peo
WHERE peo.person_id = l_person_id_tmp
AND l_assignment_rec.start_date BETWEEN peo.effective_start_date
AND peo.effective_end_date
AND ROWNUM = 1;
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Create_Staffed_Assignment.insert_row'
,x_msg => 'Insert new assignment into Table.'
,x_log_level => 5);
PA_PROJECT_ASSIGNMENTS_PKG.Insert_Row
(p_assignment_name => l_assignment_rec.assignment_name
,p_assignment_type => l_assignment_rec.assignment_type
,p_multiple_status_flag => l_assignment_rec.multiple_status_flag
,p_status_code => l_assignment_rec.status_code
-- Commented for PJR Enhancement 5130421 ,p_apprvl_status_code => PA_ASSIGNMENT_APPROVAL_PUB.g_working
,p_apprvl_status_code => l_apprvl_status_code -- Included for 5130421
,p_staffing_priority_code => l_assignment_rec.staffing_priority_code
,p_project_id => l_assignment_rec.project_id
,p_project_role_id => l_assignment_rec.project_role_id
,p_resource_id => l_assignment_rec.resource_id
,p_project_party_id => l_assignment_rec.project_party_id
,p_description => l_assignment_rec.description
,p_start_date => l_assignment_rec.start_date
,p_end_date => l_assignment_rec.end_date
,p_assignment_effort => l_assignment_rec.assignment_effort
,p_extension_possible => l_assignment_rec.extension_possible
,p_source_assignment_id => l_assignment_rec.source_assignment_id
,p_additional_information => l_assignment_rec.additional_information
,p_work_type_id => l_assignment_rec.work_type_id
,p_revenue_currency_code => l_assignment_rec.revenue_currency_code
,p_revenue_bill_rate => l_assignment_rec.revenue_bill_rate
,p_markup_percent => l_assignment_rec.markup_percent
,p_fcst_tp_amount_type => l_assignment_rec.fcst_tp_amount_type
,p_fcst_job_id => l_assignment_rec.fcst_job_id
,p_fcst_job_group_id => l_assignment_rec.fcst_job_group_id
,p_expenditure_org_id => l_assignment_rec.expenditure_org_id
,p_expenditure_organization_id => l_assignment_rec.expenditure_organization_id
,p_expenditure_type_class => l_assignment_rec.expenditure_type_class
,p_expenditure_type => l_assignment_rec.expenditure_type
,p_expense_owner => l_assignment_rec.expense_owner
,p_expense_limit => l_assignment_rec.expense_limit
,p_expense_limit_currency_code => l_assignment_rec.expense_limit_currency_code
,p_location_id => l_assignment_rec.location_id
,p_calendar_type => l_assignment_rec.calendar_type
,p_calendar_id => l_assignment_rec.calendar_id
,p_resource_calendar_percent => l_assignment_rec.resource_calendar_percent
,p_bill_rate_override => l_assignment_rec.bill_rate_override
,p_bill_rate_curr_override => l_assignment_rec.bill_rate_curr_override
,p_markup_percent_override => l_assignment_rec.markup_percent_override
,p_discount_percentage => l_assignment_rec.discount_percentage -- FP.L Development
,p_rate_disc_reason_code => l_assignment_rec.rate_disc_reason_code -- FP.L Development
,p_tp_rate_override => l_assignment_rec.tp_rate_override
,p_tp_currency_override => l_assignment_rec.tp_currency_override
,p_tp_calc_base_code_override => l_assignment_rec.tp_calc_base_code_override
,p_tp_percent_applied_override => l_assignment_rec.tp_percent_applied_override
,p_staffing_owner_person_id => l_assignment_rec.staffing_owner_person_id -- FP.L Development
,p_attribute_category => l_assignment_rec.attribute_category
,p_attribute1 => l_assignment_rec.attribute1
,p_attribute2 => l_assignment_rec.attribute2
,p_attribute3 => l_assignment_rec.attribute3
,p_attribute4 => l_assignment_rec.attribute4
,p_attribute5 => l_assignment_rec.attribute5
,p_attribute6 => l_assignment_rec.attribute6
,p_attribute7 => l_assignment_rec.attribute7
,p_attribute8 => l_assignment_rec.attribute8
,p_attribute9 => l_assignment_rec.attribute9
,p_attribute10 => l_assignment_rec.attribute10
,p_attribute11 => l_assignment_rec.attribute11
,p_attribute12 => l_assignment_rec.attribute12
,p_attribute13 => l_assignment_rec.attribute13
,p_attribute14 => l_assignment_rec.attribute14
,p_attribute15 => l_assignment_rec.attribute15
,p_resource_list_member_id => l_assignment_rec.resource_list_member_id
,x_assignment_row_id => x_assignment_row_id
,x_new_assignment_id => x_new_assignment_id
,x_assignment_number => l_assignment_rec.assignment_number
,x_return_status => x_return_status
);
,x_msg => 'after insert_row, status='||x_return_status
,x_log_level => li_message_level);
pa_assignments_pvt.Update_Task_Assignments(
p_mode => 'CREATE'
,p_task_assignment_id_tbl => l_task_assignment_id_tbl
,p_task_version_id_tbl => l_task_version_id_tbl
,p_budget_version_id_tbl => l_budget_version_id_tbl
,p_struct_version_id_tbl => l_struct_version_id_tbl
-- change project_assignment_id to this assignment_id
,p_project_assignment_id => PA_ASSIGNMENTS_PUB.g_assignment_id_tbl(1).assignment_id
-- ,p_resource_list_member_id => l_assignment_rec.resource_list_member_id
-- change the named role to this assignment name
,p_named_role => p_assignment_rec.assignment_name
,p_project_role_id => p_assignment_rec.project_role_id
,x_return_status => l_return_status
);
,x_msg => 'Update_Task_Assignments status '||l_return_status
,x_log_level => li_message_level);
PROCEDURE Update_Staffed_Assignment
( p_assignment_rec IN PA_ASSIGNMENTS_PUB.Assignment_Rec_Type
,p_location_city IN pa_locations.city%TYPE := FND_API.G_MISS_CHAR
,p_location_region IN pa_locations.region%TYPE := FND_API.G_MISS_CHAR
,p_location_country_code IN pa_locations.country_code%TYPE := FND_API.G_MISS_CHAR
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_assignment_rec PA_ASSIGNMENTS_PUB.Assignment_Rec_Type;
l_update_task_asgmt_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
l_update_count NUMBER;
l_update_task_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
SELECT status_code, start_date, end_date
FROM pa_project_assignments
WHERE assignment_id = p_assignment_rec.assignment_id;
SELECT resource_assignment_id, wbs_element_version_id, budget_version_id, project_structure_version_id
FROM
(
(SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
FROM PA_RESOURCE_ASSIGNMENTS ra
,PA_BUDGET_VERSIONS bv
,PA_PROJ_ELEM_VER_STRUCTURE evs
WHERE ra.project_id = bv.project_id
AND bv.project_id = evs.project_id
AND ra.budget_version_id = bv.budget_version_id
AND bv.project_structure_version_id = evs.element_version_id
AND ra.project_id = l_assignment_rec.project_id
AND ra.resource_list_member_id = l_assignment_rec.resource_list_member_id
AND ra.project_assignment_id = -1
AND evs.status_code = 'STRUCTURE_WORKING')
UNION ALL
(SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
FROM PA_RESOURCE_ASSIGNMENTS ra
,PA_BUDGET_VERSIONS bv
,PA_PROJ_ELEM_VER_STRUCTURE evs
,PA_PROJ_WORKPLAN_ATTR pwa
WHERE pwa.wp_enable_Version_flag = 'N'
AND pwa.project_id = ra.project_id
AND pwa.proj_element_id = evs.proj_element_id
AND ra.project_id = bv.project_id
AND bv.project_id = evs.project_id
AND ra.budget_version_id = bv.budget_version_id
AND bv.project_structure_version_id = evs.element_version_id
AND ra.resource_list_member_id = l_assignment_rec.resource_list_member_id
AND ra.project_id = l_assignment_rec.project_id
AND ra.project_assignment_id = -1)
)
ORDER BY budget_version_id, project_structure_version_id;
SELECT resource_assignment_id, wbs_element_version_id, budget_version_id, project_structure_version_id
FROM
(
(SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
FROM PA_RESOURCE_ASSIGNMENTS ra
,PA_BUDGET_VERSIONS bv
,PA_PROJ_ELEM_VER_STRUCTURE evs
WHERE ra.project_id = bv.project_id
AND bv.project_id = evs.project_id
AND ra.budget_version_id = bv.budget_version_id
AND bv.project_structure_version_id = evs.element_version_id
AND ra.project_id = l_assignment_rec.project_id
AND ra.project_assignment_id = l_assignment_rec.assignment_id
AND evs.status_code = 'STRUCTURE_WORKING')
UNION ALL
(SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
FROM PA_RESOURCE_ASSIGNMENTS ra
,PA_BUDGET_VERSIONS bv
,PA_PROJ_ELEM_VER_STRUCTURE evs
,PA_PROJ_WORKPLAN_ATTR pwa
WHERE pwa.wp_enable_Version_flag = 'N'
AND pwa.project_id = ra.project_id
AND pwa.proj_element_id = evs.proj_element_id
AND ra.project_id = bv.project_id
AND bv.project_id = evs.project_id
AND ra.budget_version_id = bv.budget_version_id
AND bv.project_structure_version_id = evs.element_version_id
AND ra.project_id = l_assignment_rec.project_id
AND ra.project_assignment_id = l_assignment_rec.assignment_id)
)
ORDER BY budget_version_id, project_structure_version_id;
SELECT rf.res_format_id, rf.RES_TYPE_ENABLED_FLAG,
rf.ORGN_ENABLED_FLAG, rf.FIN_CAT_ENABLED_FLAG,
rf.ROLE_ENABLED_FLAG
FROM pa_res_formats_b rf,
pa_resource_list_members rlm
WHERE rlm.res_format_id = rf.res_format_id
AND rlm.resource_list_member_id = l_assignment_rec.resource_list_member_id;
SELECT resource_id, fcst_job_id, expenditure_organization_id,
expenditure_type,
project_role_id, assignment_name,
resource_list_member_id,
project_role_id
FROM pa_project_assignments
WHERE assignment_id = l_assignment_rec.assignment_id;
SELECT resource_organization_id
FROM pa_resources_denorm
WHERE l_assignment_rec.start_date BETWEEN resource_effective_start_date AND resource_effective_end_date
AND resource_id = l_assignment_rec.resource_id;
PA_DEBUG.set_err_stack('PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment');
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.begin'
,x_msg => 'Beginning of Update_Staff_Assignment'
,x_log_level => 5);
pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
,x_msg => 'l_asgmt_rec.resource_id='||l_assignment_rec.resource_id
,x_log_level => li_message_level);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.begin'
,x_msg => 'Old resource list member id='||l_cur_resource_list_member_id
,x_log_level => 5);
select person_id
into l_person_id
from pa_resource_txn_attributes
WHERE resource_id = l_assignment_rec.resource_id;
pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
,x_msg => 'l_person_id='||l_person_id
,x_log_level => li_message_level);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.begin'
,x_msg => 'Mandatory attributes changed'
,x_log_level => 5);
SELECT proj_asgmt_res_format_id
INTO l_proj_asgmt_res_format_id
FROM PA_PROJECTS_ALL
WHERE project_id = p_assignment_rec.project_id;
SELECT person_id
INTO l_person_id
FROM pa_resources_denorm
WHERE resource_id = p_assignment_rec.resource_id
AND rownum = 1;
pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
,x_msg => 'l_resource_organization_id='||l_resource_organization_id
,x_log_level => li_message_level);
SELECT nvl(future_term_wf_flag,'N')
INTO l_future_term_wf_flag
FROM pa_resources
WHERE resource_id = l_assignment_rec.resource_id;
SELECT job_id
INTO l_fcst_job_id_tmp
FROM pa_resources_denorm prd
WHERE prd.person_id = l_person_id_tmp
AND l_assignment_rec.start_date BETWEEN prd.resource_effective_start_date
AND prd.resource_effective_end_date ;
pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
,x_msg => 'l_fcst_job_id_tmp='||l_fcst_job_id_tmp
,x_log_level => li_message_level);
SELECT decode(nvl(peo.employee_number,0), 0,'CWK','EMP')
INTO l_person_type_code
FROM per_all_people_f peo
WHERE peo.person_id = l_person_id_tmp
AND l_assignment_rec.start_date BETWEEN peo.effective_start_date
AND peo.effective_end_date ;
SELECT job_id
INTO l_fcst_job_id_tmp
FROM per_all_assignments_f assn, pa_project_assignments pa
WHERE assn.person_id = l_person_id_tmp
AND pa.assignment_id = l_assignment_rec.assignment_id
AND pa.start_date BETWEEN assn.effective_start_date
AND assn.effective_end_date
AND assn.assignment_type in ('C','E')
AND assn.primary_flag = 'Y'
AND ROWNUM = 1;
pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
,x_msg => 'l_fcst_job_id_tmp='||l_fcst_job_id_tmp
,x_log_level => li_message_level);
SELECT decode(peo.current_employee_flag, 'Y', 'EMP', 'CWK')
INTO l_person_type_code
FROM per_all_people_f peo, pa_project_assignments pa
WHERE peo.person_id = l_person_id_tmp
AND pa.assignment_id = l_assignment_rec.assignment_id
AND pa.start_date BETWEEN peo.effective_start_date
AND peo.effective_end_date
AND ROWNUM = 1;
pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
,x_msg => 'l_person_type_code='||l_person_type_code
,x_log_level => li_message_level);
pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
,x_msg => 'proj_id='||p_assignment_rec.project_id||
' res_format='||l_cur_res_format_id||
' person_id='||l_person_id_tmp||
' job_id='||l_fcst_job_id_tmp
,x_log_level => li_message_level);
pa_debug.write(x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
,x_msg => 'org_id='||l_resource_organization_id||
' exp_type='||l_expenditure_type_tmp||
' role_id='||l_project_role_id_tmp||
' named_role='||l_assignment_name_tmp
,x_log_level => li_message_level);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.begin'
,x_msg => 'new resource list member id='||l_assignment_rec.resource_list_member_id
,x_log_level => 5);
-- Invoke Update_Planning_Transaction API
pa_assignments_pvt.Update_Task_Assignments(
p_task_assignment_id_tbl => l_task_assignment_id_tbl
,p_task_version_id_tbl => l_task_version_id_tbl
,p_budget_version_id_tbl => l_budget_version_id_tbl
,p_struct_version_id_tbl => l_struct_version_id_tbl
,p_project_assignment_id => l_assignment_rec.assignment_id
-- change resource list member
,p_resource_list_member_id => l_assignment_rec.resource_list_member_id
/* -- pass in resource attributes
,p_resource_class_flag => l_resource_class_flag_tbl(1)
,p_resource_class_code => l_resource_class_code_tbl(1)
,p_resource_class_id => l_resource_class_id_tbl(1)
,p_res_type_code => l_res_type_code_tbl(1)
,p_incur_by_res_type => l_incur_by_res_type_tbl(1)
,p_person_id => l_person_id_tbl(1)
,p_job_id => l_job_id_tbl(1)
,p_person_type_code => l_person_type_code_tbl(1) */
,p_named_role => l_assignment_rec.assignment_name
/* ,p_bom_resource_id => l_bom_resource_id_tbl(1)
,p_non_labor_resource => l_non_labor_resource_tbl(1)
,p_inventory_item_id => l_inventory_item_id_tbl(1)
,p_item_category_id => l_item_category_id_tbl(1)
*/ ,p_project_role_id => l_assignment_rec.project_role_id
/* ,p_organization_id => l_organization_id_tbl(1)
,p_fc_res_type_code => l_fc_res_type_code_tbl(1)
,p_expenditure_type => l_expenditure_type_tbl(1)
,p_expenditure_category => l_expenditure_category_tbl(1)
,p_event_type => l_event_type_tbl(1)
,p_revenue_category_code => l_revenue_category_code_tbl(1)
,p_supplier_id => l_supplier_id_tbl(1)
,p_spread_curve_id => l_spread_curve_id_tbl(1)
,p_etc_method_code => l_etc_method_code_tbl(1)
,p_mfc_cost_type_id => l_mfc_cost_type_id_tbl(1)
,p_incurred_by_res_flag => l_incurred_by_res_flag_tbl(1)
,p_incur_by_res_class_code => l_incur_by_res_class_code_tbl(1)
,p_incur_by_role_id => l_incur_by_role_id_tbl(1)
,p_unit_of_measure => l_unit_of_measure_tbl(1)
,p_org_id => l_org_id_tbl(1)
,p_rate_based_flag => l_rate_based_flag_tbl(1)
,p_rate_expenditure_type => l_rate_expenditure_type_tbl(1)
,p_rate_func_curr_code => l_rate_func_curr_code_tbl(1)
-- ,p_rate_incurred_by_org_id => l_rate_incurred_by_org_id_tbl(1) */
,x_return_status => l_return_status
);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
,x_msg => 'Update_task_assignments, status='||l_return_status
,x_log_level => 5);
pa_assignments_pvt.Update_Task_Assignments(
p_task_assignment_id_tbl => l_task_assignment_id_tbl
,p_task_version_id_tbl => l_task_version_id_tbl
,p_budget_version_id_tbl => l_budget_version_id_tbl
,p_struct_version_id_tbl => l_struct_version_id_tbl
-- change project_assignment_id to this assignment_id
,p_project_assignment_id => l_assignment_rec.assignment_id
,p_resource_list_member_id => l_assignment_rec.resource_list_member_id
-- change the named role to this assignment name
,p_named_role => l_assignment_rec.assignment_name
,p_project_role_id => l_assignment_rec.project_role_id
,x_return_status => l_return_status
);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
,x_msg => 'Update_task_assignments, status='||l_return_status
,x_log_level => 5);
pa_assignments_pvt.Update_Task_Assignments(
p_task_assignment_id_tbl => l_task_assignment_id_tbl
,p_task_version_id_tbl => l_task_version_id_tbl
,p_budget_version_id_tbl => l_budget_version_id_tbl
,p_struct_version_id_tbl => l_struct_version_id_tbl
,p_project_assignment_id => -1
,x_return_status => l_return_status
);
pa_assignments_pvt.Update_Task_Assignments(
p_task_assignment_id_tbl => l_task_assignment_id_tbl
,p_task_version_id_tbl => l_task_version_id_tbl
,p_budget_version_id_tbl => l_budget_version_id_tbl
,p_struct_version_id_tbl => l_struct_version_id_tbl
,p_project_assignment_id => -1
,p_named_role => FND_API.G_MISS_CHAR
,x_return_status => l_return_status
);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.begin'
,x_msg => 'Update_task_assignments, status='||l_return_status
,x_log_level => 5);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.begin'
,x_msg => 'Mandatory attributes not changed'
,x_log_level => 5);
pa_assignments_pvt.Update_Task_Assignments(
p_task_assignment_id_tbl => l_task_assignment_id_tbl
,p_task_version_id_tbl => l_task_version_id_tbl
,p_budget_version_id_tbl => l_budget_version_id_tbl
,p_struct_version_id_tbl => l_struct_version_id_tbl
,p_named_role => l_assignment_rec.assignment_name
,p_project_role_id => l_assignment_rec.project_role_id
,x_return_status => l_return_status
);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.begin'
,x_msg => 'Update_task_assignments, status='||l_return_status
,x_log_level => 5);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.insert_history'
,x_msg => 'Inserting into Assignment History.'
,x_log_level => 5);
PA_ASSIGNMENT_APPROVAL_PVT.Insert_Into_Assignment_History ( p_assignment_id => l_assignment_rec.assignment_id
,x_change_id => l_change_id
,x_return_status => l_return_status);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.get_next_stus'
,x_msg => 'Getting next assignment approval status.'
,x_log_level => 5);
PA_ASSIGNMENT_APPROVAL_PVT.Get_Next_Status_After_Action ( p_action_code => PA_ASSIGNMENT_APPROVAL_PUB.g_update_action
,p_status_code => l_assignment_rec.apprvl_status_code
-- ,x_status_code => l_assignment_rec.apprvl_status_code * commented for Bug: 4537865
,x_status_code => l_new_status_code -- added for Bug: 4537865
,x_return_status => l_return_status);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment.update_row'
,x_msg => 'Update Assignment Record.'
,x_log_level => 5);
PA_PROJECT_ASSIGNMENTS_PKG.Update_Row
(p_assignment_row_id => l_assignment_rec.assignment_row_id
,p_assignment_id => l_assignment_rec.assignment_id
,p_record_version_number => l_assignment_rec.record_version_number
,p_assignment_name => l_assignment_rec.assignment_name
,p_assignment_type => l_assignment_rec.assignment_type
,p_multiple_status_flag => l_assignment_rec.multiple_status_flag
,p_apprvl_status_code => l_assignment_rec.apprvl_status_code
,p_status_code => l_assignment_rec.status_code
,p_staffing_priority_code => l_assignment_rec.staffing_priority_code
,p_project_id => l_assignment_rec.project_id
,p_project_role_id => l_assignment_rec.project_role_id
,p_project_party_id => l_assignment_rec.project_party_id
,p_description => l_assignment_rec.description
,p_start_date => l_assignment_rec.start_date
,p_end_date => l_assignment_rec.end_date
,p_assignment_effort => l_assignment_rec.assignment_effort
,p_extension_possible => l_assignment_rec.extension_possible
,p_source_assignment_id => l_assignment_rec.source_assignment_id
,p_additional_information => l_assignment_rec.additional_information
,p_work_type_id => l_assignment_rec.work_type_id
,p_revenue_currency_code => l_assignment_rec.revenue_currency_code
,p_revenue_bill_rate => l_assignment_rec.revenue_bill_rate
,p_markup_percent => l_assignment_rec.markup_percent
,p_expense_owner => l_assignment_rec.expense_owner
,p_expense_limit => l_assignment_rec.expense_limit
,p_expense_limit_currency_code => l_assignment_rec.expense_limit_currency_code
,p_fcst_tp_amount_type => l_assignment_rec.fcst_tp_amount_type
,p_fcst_job_id => l_assignment_rec.fcst_job_id
,p_fcst_job_group_id => l_assignment_rec.fcst_job_group_id
,p_expenditure_org_id => l_assignment_rec.expenditure_org_id
,p_expenditure_organization_id => l_assignment_rec.expenditure_organization_id
,p_expenditure_type_class => l_assignment_rec.expenditure_type_class
,p_expenditure_type => l_assignment_rec.expenditure_type
,p_location_id => l_assignment_rec.location_id
,p_calendar_type => l_assignment_rec.calendar_type
,p_calendar_id => l_assignment_rec.calendar_id
,p_resource_calendar_percent => l_assignment_rec.resource_calendar_percent
,p_bill_rate_override => l_assignment_rec.bill_rate_override
,p_bill_rate_curr_override => l_assignment_rec.bill_rate_curr_override
,p_markup_percent_override => l_assignment_rec.markup_percent_override
,p_discount_percentage => l_assignment_rec.discount_percentage -- Bug 2590938
,p_rate_disc_reason_code => l_assignment_rec.rate_disc_reason_code -- Bug 2590938
,p_tp_rate_override => l_assignment_rec.tp_rate_override
,p_tp_currency_override => l_assignment_rec.tp_currency_override
,p_tp_calc_base_code_override => l_assignment_rec.tp_calc_base_code_override
,p_tp_percent_applied_override => l_assignment_rec.tp_percent_applied_override
,p_staffing_owner_person_id => l_assignment_rec.staffing_owner_person_id
,p_attribute_category => l_assignment_rec.attribute_category
,p_attribute1 => l_assignment_rec.attribute1
,p_attribute2 => l_assignment_rec.attribute2
,p_attribute3 => l_assignment_rec.attribute3
,p_attribute4 => l_assignment_rec.attribute4
,p_attribute5 => l_assignment_rec.attribute5
,p_attribute6 => l_assignment_rec.attribute6
,p_attribute7 => l_assignment_rec.attribute7
,p_attribute8 => l_assignment_rec.attribute8
,p_attribute9 => l_assignment_rec.attribute9
,p_attribute10 => l_assignment_rec.attribute10
,p_attribute11 => l_assignment_rec.attribute11
,p_attribute12 => l_assignment_rec.attribute12
,p_attribute13 => l_assignment_rec.attribute13
,p_attribute14 => l_assignment_rec.attribute14
,p_attribute15 => l_assignment_rec.attribute15
,p_resource_list_member_id => l_assignment_rec.resource_list_member_id
,x_return_status => x_return_status
);
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_STAFFED_ASSIGNMENT_PVT.Update_Staffed_Assignment'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Update_Staffed_Assignment;
PROCEDURE Delete_Staffed_Assignment
( p_assignment_row_id IN ROWID --(Bug-1851096)
,p_assignment_id IN pa_project_assignments.assignment_id%TYPE := FND_API.G_MISS_NUM
,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
,p_project_party_id IN pa_project_parties.project_party_id%TYPE := FND_API.G_MISS_NUM
,p_calling_module IN VARCHAR2 := FND_API.G_MISS_CHAR
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_return_status VARCHAR2(1);
SELECT asgn.start_date, asgn.end_date, asgn.project_id, res.person_id
FROM pa_project_assignments asgn,
pa_resources_denorm res
WHERE assignment_id = p_assignment_id
AND res.resource_id = asgn.resource_id
AND rownum=1;
PA_DEBUG.set_err_stack('PA_STAFFED_ASSIGNMENT_PVT.Delete_Staffed_Assignment');
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Delete_Staffed_Assignment.begin'
,x_msg => 'Beginning of Delete_Staff_Assignment'
,x_log_level => 5);
,p_calling_mode => 'DELETE'
,p_project_id => l_project_id
,p_person_id => l_person_id
,p_old_start_date => null
,p_old_end_date => null
,p_new_start_date => l_start_date
,p_new_end_date => l_end_date
,x_error_message_code => l_error_message_code
,x_return_status => l_return_status);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Delete_Staffed_Assignment.del_schedules'
,x_msg => 'Deleting Assignment Schedules'
,x_log_level => 5);
PA_SCHEDULE_PVT.Delete_Asgn_Schedules
( p_assignment_id => p_assignment_id
,p_perm_delete => FND_API.G_TRUE --Added for bug 4389372
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Delete_Staffed_Assignment.del_asgmt'
,x_msg => 'Deleting Assignment record.'
,x_log_level => 5);
PA_PROJECT_ASSIGNMENTS_PKG.Delete_Row
( p_assignment_row_id => p_assignment_row_id
,p_assignment_id => p_assignment_id
,p_record_version_number => p_record_version_number
,x_return_status => x_return_status
);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Delete_Staffed_Assignment.proj_party.'
,x_msg => 'Deleting Project Party'
,x_log_level => 5);
PA_PROJECT_PARTIES_PVT.Delete_Project_Party(
p_commit => 'F',
p_validate_only => 'F',
p_project_party_id => p_project_party_id,
p_calling_module => 'ASSIGNMENT',
p_record_version_number => null,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_STAFFED_ASSIGNMENT_PVT.Delete_Staffed_Assignment.del_history'
,x_msg => 'Deleting Assignment History.'
,x_log_level => 5);
PA_ASSIGNMENTS_HISTORY_PKG.Delete_By_Assignment ( p_assignment_id =>p_assignment_id
,x_return_status => x_return_status);
PA_ASGMT_WFSTD.Delete_Assignment_WF_Records (p_assignment_id => p_assignment_id,
p_project_id => l_project_id);
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_STAFFED_ASSIGNMENT_PVT.Delete_Staffed_Assignment'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Delete_Staffed_Assignment;