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 calendar_id, competence_match_wt, availability_match_wt, job_level_match_wt, search_min_availability, search_org_hier_id, search_starting_org_id, search_country_code, min_cand_score_reqd_for_nom, adv_action_set_id, start_adv_action_set_flag,
enable_automated_search -- Added for bug 4306049
FROM pa_projects_all
WHERE project_id = l_assignment_rec.project_id;
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#9095861
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 ra.budget_version_id = bv.budget_version_id
AND bv.budget_type_code IS NULL -- added for bug#9095861
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#9095861
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 proj_req_res_format_id
INTO l_proj_req_res_format_id
FROM PA_PROJECTS_ALL
WHERE project_id = p_assignment_rec.project_id;
SELECT location_id
INTO l_assignment_rec.location_id
FROM pa_projects_all
WHERE project_id = l_assignment_rec.project_id;
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment.insert_row'
,x_msg => 'Inserting record into pa_project_assignments.'
,x_log_level => li_message_level);
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
,p_staffing_priority_code => l_assignment_rec.staffing_priority_code
,p_project_id => l_assignment_rec.project_id
,p_assignment_template_id => l_assignment_rec.assignment_template_id
,p_project_role_id => l_assignment_rec.project_role_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_min_resource_job_level => l_assignment_rec.min_resource_job_level
,p_max_resource_job_level => l_assignment_rec.max_resource_job_level
,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_comp_match_weighting => l_assignment_rec.comp_match_weighting
,p_avail_match_weighting => l_assignment_rec.avail_match_weighting
,p_job_level_match_weighting => l_assignment_rec.job_level_match_weighting
,p_search_min_availability => l_assignment_rec.search_min_availability
,p_search_country_code => l_assignment_rec.search_country_code
,p_search_exp_org_struct_ver_id => l_assignment_rec.search_exp_org_struct_ver_id
,p_search_exp_start_org_id => l_assignment_rec.search_exp_start_org_id
,p_search_min_candidate_score => l_assignment_rec.search_min_candidate_score
,p_enable_auto_cand_nom_flag => l_assignment_rec.enable_auto_cand_nom_flag
,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_resource_list_member_id => l_assignment_rec.resource_list_member_id -- FP.M 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_number_of_requirements => p_number_of_requirements
,x_assignment_row_id => x_assignment_row_id
,x_new_assignment_id => l_assignment_id
,x_assignment_number => x_assignment_number
,x_return_status => x_return_status
);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Create_Open_Assignment.insert_schedule'
,x_msg => 'Insert the schedule days for the open assignment.'
,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
);
-- If creating requirement from Create Team Roles page, insert the competencies.
-- 5130421 : Added G_Calling_Application check so that competencies are copied
-- while creation of new requirments
IF (l_source_assignment_id IS NOT NULL AND
l_source_assignment_id <> FND_API.G_MISS_NUM AND
l_assignment_rec.source_assignment_type IN ( 'OPEN_ASSIGNMENT','STAFFED_ASSIGNMENT')) /*Bug#12422340*/
OR (p_assignment_rec.resource_list_member_id <> FND_API.G_MISS_NUM AND
l_assignment_rec.source_assignment_type IN ('OPEN_ASSIGNMENT','STAFFED_ASSIGNMENT')) /*bug#12422340*/
OR (p_budget_version_id IS NOT NULL AND
p_budget_version_id <> FND_API.G_MISS_NUM AND
l_assignment_rec.resource_list_member_id IS NOT NULL AND
l_assignment_rec.resource_list_member_id <> FND_API.G_MISS_NUM)
OR (PA_STARTUP.G_Calling_Application = 'PLSQL' AND PA_STARTUP.G_Calling_module = 'AMG')
THEN
FOR i IN 1..l_competencies_tbl.COUNT LOOP
/*
dbms_output.put_line('PA_ASSIGNMENTS_PUB.g_assignment_id_tbl.COUNT='||PA_ASSIGNMENTS_PUB.g_assignment_id_tbl.COUNT);
PROCEDURE Update_Open_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;
PA_DEBUG.set_err_stack('PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment');
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
,x_msg => 'Beginning of Update_Open_Assignment'
,x_log_level => li_message_level);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
,x_msg => 'Old resource list member id='||l_cur_resource_list_member_id
,x_log_level => li_message_level);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
,x_msg => 'Mandatory attributes changed'
,x_log_level => li_message_level);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
,x_msg => 'new resource list member id='||l_assignment_rec.resource_list_member_id
,x_log_level => li_message_level);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
,x_msg => 'Get resource defaults, status='||l_return_status
,x_log_level => li_message_level);
-- 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
,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_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
,x_msg => 'Update_task_assignments, status='||l_return_status
,x_log_level => li_message_level);
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_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
,x_msg => 'Update_task_assignments, status='||l_return_status
,x_log_level => li_message_level);
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_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
,x_msg => 'Update_task_assignments, status='||l_return_status
,x_log_level => li_message_level);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
,x_msg => 'Mandatory attributes not changed'
,x_log_level => li_message_level);
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_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.begin'
,x_msg => 'Update_task_assignments, status='||l_return_status
,x_log_level => li_message_level);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment.update_row'
,x_msg => 'Update Assignment Record in the table'
,x_log_level => li_message_level);
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_status_code => l_assignment_rec.status_code
,p_staffing_priority_code => l_assignment_rec.staffing_priority_code
,p_project_role_id => l_assignment_rec.project_role_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_source_assignment_id => l_assignment_rec.source_assignment_id
,p_min_resource_job_level => l_assignment_rec.min_resource_job_level
,p_max_resource_job_level => l_assignment_rec.max_resource_job_level
,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_extension_possible => l_assignment_rec.extension_possible
,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_comp_match_weighting => l_assignment_rec.comp_match_weighting
,p_avail_match_weighting => l_assignment_rec.avail_match_weighting
,p_job_level_match_weighting => l_assignment_rec.job_level_match_weighting
,p_search_min_availability => l_assignment_rec.search_min_availability
,p_search_country_code => l_assignment_rec.search_country_code
,p_search_exp_org_struct_ver_id => l_assignment_rec.search_exp_org_struct_ver_id
,p_search_exp_start_org_id => l_assignment_rec.search_exp_start_org_id
,p_search_min_candidate_score => l_assignment_rec.search_min_candidate_score
,p_enable_auto_cand_nom_flag => l_assignment_rec.enable_auto_cand_nom_flag
,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_resource_list_member_id => l_assignment_rec.resource_list_member_id -- FP-M Development -- FP.M 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
,x_return_status => x_return_status
);
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_OPEN_ASSIGNMENT_PVT.Update_Open_Assignment'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Update_Open_Assignment;
PROCEDURE Delete_Open_Assignment
( p_assignment_row_id IN ROWID
,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_calling_module IN VARCHAR2 := FND_API.G_MISS_NUM
,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) := FND_API.G_RET_STS_SUCCESS;
SELECT action_set_id, record_version_number
FROM pa_action_sets
WHERE object_id = p_assignment_id
AND object_type = 'OPEN_ASSIGNMENT'
AND action_set_type_code = 'ADVERTISEMENT'
AND status_code <> 'DELETED';
PA_DEBUG.set_err_stack('PA_OPEN_ASSIGNMENT_PVT.Delete_Open_Assignment');
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Delete_Open_Assignment.begin'
,x_msg => 'Beginning of Delete_Open_Assignment'
,x_log_level => li_message_level);
PA_DEBUG.write_log (x_module => 'pa.plsql.PA_OPEN_ASSIGNMENT_PVT.Delete_Open_Assignment.delete_schedule'
,x_msg => 'Deleting Open Assignment schedules'
,x_log_level => li_message_level);
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_ACTION_SETS_PUB.Delete_Action_Set (
p_init_msg_list => FND_API.G_FALSE -- 5130421
,p_action_set_id => l_action_set_id
,p_action_set_type_code => 'ADVERTISEMENT'
,p_object_id => p_assignment_id
,p_object_type => 'OPEN_ASSIGNMENT'
,p_record_version_number => l_record_version_number
,p_commit => p_commit
,p_validate_only => p_validate_only
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
PA_CANDIDATE_PUB.Delete_Candidates (p_assignment_id => p_assignment_id
,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_OPEN_ASSIGNMENT_PVT.Delete_Open_Assignment.del_competency'
,x_msg => 'Deleting Requirement Competencies.'
,x_log_level => li_message_level);
PA_COMPETENCE_PUB.Delete_Competence_Element
( p_object_name => 'OPEN_ASSIGNMENT'
,p_object_id => p_assignment_id
,p_competence_id => l_competency_tbl(i).competence_id
,p_element_id => l_competency_tbl(i).competence_element_id
,p_object_version_number => l_competency_tbl(i).object_version_number
,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_OPEN_ASSIGNMENT_PVT.Delete_Open_Assignment.del_asgmt'
,x_msg => 'Deleting Requirement Record'
,x_log_level => li_message_level);
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);
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_OPEN_ASSIGNMENT_PVT.Delete_Open_Assignment'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END Delete_Open_Assignment;