[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT start_date
INTO l_start_dt
FROM pa_projects_all
WHERE project_id = l_prj_id;
SELECT completion_date
INTO l_compl_dt
FROM pa_projects_all
WHERE project_id = l_prj_id;
SELECT 'Y' into l_dummy
FROM dual
WHERE l_approver_id in
(SELECT pa_resource_utils.get_hr_manager_id(res_denorm.resource_id,l_start_date) approver_id
from pa_resources_denorm res_denorm
WHERE l_start_date between resource_effective_start_date
and resource_effective_end_date
START WITH resource_id = l_resource_id
CONNECT BY
prior pa_resource_utils.get_hr_manager_id(res_denorm.resource_id,l_start_date)= person_id
and pa_resource_utils.get_hr_manager_id(res_denorm.resource_id,l_start_date) <> prior person_id
and l_start_date between prior resource_effective_start_date and prior resource_effective_end_date
and l_start_date between resource_effective_start_date and resource_effective_end_date
UNION
SELECT per.person_id approver_id
from pa_resources_denorm res_denorm,
fnd_grants fg,
fnd_objects fob,
per_all_people_f per,
wf_roles wfr,
(select pa_security_pvt.get_menu_id('PA_PRM_RES_PRMRY_CONTACT') menu_id
from dual) prmry_contact_menu
where fob.obj_name = 'ORGANIZATION'
and res_denorm.resource_id = l_resource_id
and l_start_date between res_denorm.resource_effective_start_date and res_denorm.resource_effective_end_date
and fg.instance_pk1_value = to_char(res_denorm.resource_organization_id)
and fg.instance_type = 'INSTANCE'
and fg.object_id = fob.object_id
and fg.grantee_type = 'USER'
AND fg.grantee_key = wfr.name
AND wfr.orig_system = 'HZ_PARTY'
AND per.party_id = wfr.orig_system_id
and sysdate between per.effective_start_date and per.effective_end_date
and fg.menu_id = prmry_contact_menu.menu_id
and trunc(SYSDATE) between trunc(fg.start_date) and trunc(NVL(fg.end_date, SYSDATE+1))
UNION
select per.person_id approver_id
from pa_resources_denorm res_denorm,
fnd_grants fg,
fnd_objects fob,
wf_roles wfr,
per_people_f per,
(select pa_security_pvt.get_menu_id('PA_PRM_RES_AUTH') menu_id
from dual) res_auth_menu
where fob.obj_name = 'ORGANIZATION'
and res_denorm.resource_id = l_resource_id
and fg.instance_pk1_value = to_char(res_denorm.resource_organization_id)
and l_start_date between res_denorm.resource_effective_start_date and res_denorm.resource_effective_end_date
and fg.instance_type = 'INSTANCE'
and fg.object_id = fob.object_id
and fg.grantee_type = 'USER'
and fg.menu_id = res_auth_menu.menu_id
and trunc(SYSDATE) between trunc(fg.start_date)
and trunc(NVL(fg.end_date, SYSDATE+1))
AND fg.grantee_key = wfr.name
AND wfr.orig_system = 'HZ_PARTY'
AND per.party_id = wfr.orig_system_id
and sysdate between per.effective_start_date and per.effective_end_date
and per.person_id <> res_denorm.manager_id
and per.person_id not in
(
select per2.person_Id
from
fnd_grants fg2,
fnd_objects fob2,
wf_roles wfr2,
(select pa_security_pvt.get_menu_id('PA_PRM_RES_PRMRY_CONTACT') menu_id
from dual) prmry_contact_menu,
per_people_f per2
where fob.obj_name = 'ORGANIZATION'
and fg2.instance_pk1_value = to_char(res_denorm.resource_organization_id)
and fg2.instance_type = 'INSTANCE'
and fg2.object_id = fob2.object_id
and fg2.grantee_type = 'USER'
and fg2.menu_id = prmry_contact_menu.menu_id
and trunc(SYSDATE) between trunc(fg2.start_date)
and trunc(NVL(fg2.end_date, SYSDATE+1))
AND fg2.grantee_key = wfr2.name
AND wfr2.orig_system = 'HZ_PARTY'
AND per2.party_id = wfr2.orig_system_id
and sysdate between per2.effective_start_date
and per2.effective_end_date
)
) ;
SELECT lookup_code
FROM pa_lookups
WHERE lookup_type = 'CHANGE_CALENDAR_TYPE_CODE'
AND lookup_code = c_code
AND lookup_code <> 'RESOURCE';
SELECT role_list_id, multi_currency_billing_flag, calendar_id, work_type_id, location_id
FROM pa_projects_all
WHERE project_id = c_project_id;
SELECT role_list_id, calendar_id, work_type_id
FROM pa_team_templates
WHERE team_template_id = c_team_templ_id;
SELECT meaning, default_min_job_level, default_max_job_level, default_job_id
FROM pa_project_role_types_vl
WHERE project_role_id = c_role_id ;
SELECT impl.rate_discount_reason_flag ,impl.br_override_flag, impl.br_discount_override_flag
FROM pa_implementations_all impl
, pa_projects_all proj
WHERE proj.org_id=impl.org_id -- Removed nvl condition from org_id : Post review changes for Bug 5130421
AND proj.project_id = c_project_id ;
SELECT 'Y'
FROM dual
WHERE EXISTS
(SELECT 'XYZ' FROM pa_lookups WHERE lookup_type = c_lookup_type AND lookup_code = c_lookup_code);
SELECT country_code, region, city
FROM pa_locations
WHERE location_id = c_location_id;
SELECT country_code
FROM pa_country_v
WHERE name = c_country_name;
SELECT name
FROM pa_country_v
WHERE country_code = c_country_code;
SELECT
ra.resource_list_member_id
, firstrow.person_id
, rlm.resource_id
, PA_RESOURCE_UTILS.get_person_name_no_date(firstrow.person_id)
, ra.project_id
, ra.budget_version_id
, decode (ra.role_count, 1, firstrow.named_role, null) named_role
, decode (ra.role_count, 1, firstrow.project_role_id, null) project_role_id
, decode (ra.role_count, 1, ro.meaning, null) project_role
, ra.min_date task_assign_start_date
, ra.max_date task_assign_end_date
, firstrow.resource_assignment_id
, firstrow.res_type_code
FROM pa_resource_assignments firstrow
, pa_resource_list_members rlm
, pa_proj_roles_v ro
, (SELECT project_id , budget_version_id , resource_list_member_id , count(1) role_count , max(max_id) max_id
, min(min_date) min_date , max(max_date) max_date
FROM (SELECT project_id , budget_version_id , resource_list_member_id , project_role_id
, max(resource_assignment_id) max_id , min(SCHEDULE_START_DATE) min_date , max(SCHEDULE_END_DATE) max_date
FROM pa_resource_assignments
WHERE ta_display_flag = 'Y' and nvl(PROJECT_ASSIGNMENT_ID, -1) = -1
AND resource_class_code = 'PEOPLE'
GROUP BY project_id, budget_version_id, resource_list_member_id, project_role_id
) res_roles
GROUP BY project_id, budget_version_id, resource_list_member_id
) ra
WHERE ra.resource_list_member_id = rlm.resource_list_member_id
AND firstrow.resource_assignment_id = ra.max_id
AND firstrow.project_role_id = ro.project_role_id (+)
AND ra.budget_version_id = c_budget_version_id
AND ra.resource_list_member_id = c_resource_list_member_id
AND ra.project_id = c_project_id
AND firstrow.person_id IS NULL;
SELECT organization_id
FROM hr_organization_units
WHERE business_group_id = c_business_group_id
AND name = c_exp_organization_name;
PA_HR_UPDATE_API.CHECK_EXP_OU
(p_org_id => l_req_rec.expenditure_org_id
,x_return_status => l_return_status
,x_error_message_code => l_error_message_code ) ;
SELECT business_group_id
INTO l_business_group_id
FROM hr_organization_units
WHERE organization_id = l_req_rec.expenditure_org_id;
UPDATE PA_PROJECT_ASSIGNMENTS
SET orig_system_code = l_req_rec.orig_system_code
, orig_system_reference = l_req_rec.orig_system_reference
WHERE assignment_id = l_new_assignment_id_tbl(j);
FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
PROCEDURE UPDATE_REQUIREMENTS
(
p_api_version_number IN NUMBER := 1.0
, p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_requirement_in_tbl IN REQUIREMENT_IN_TBL_TYPE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_calling_application VARCHAR2(10) := 'PLSQL';
l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.UPDATE_REQUIREMENTS';
l_asgn_update_mode VARCHAR2(10) := 'FULL';
SELECT impl.rate_discount_reason_flag ,impl.br_override_flag, impl.br_discount_override_flag
FROM pa_implementations_all impl
, pa_projects_all proj
WHERE proj.org_id=impl.org_id -- Removed nvl condition from org_id : Post review changes for Bug 5130421
AND proj.project_id = c_project_id ;
SELECT 'Y'
FROM dual
WHERE EXISTS
(SELECT 'XYZ' FROM pa_lookups WHERE lookup_type = c_lookup_type AND lookup_code = c_lookup_code);
SELECT project_subteam_party_id, project_subteam_id
FROM pa_project_subteam_parties
WHERE object_id = c_requirement_id
AND object_type = 'PA_PROJECT_ASSIGNMENTS'
AND primary_subteam_flag = 'Y';
SELECT *
FROM pa_project_assignments
WHERE assignment_type = 'OPEN_ASSIGNMENT'
AND assignment_id = c_requirement_id;
SELECT project_system_status_code
FROM pa_project_statuses
WHERE status_type = c_status_type
AND project_status_code = c_status_code;
SELECT country_code, region, city
FROM pa_locations
WHERE location_id = c_location_id;
SELECT country_code
FROM pa_country_v
WHERE name = c_country_name;
SELECT name
FROM pa_country_v
WHERE country_code = c_country_code;
SELECT multi_currency_billing_flag
FROM pa_projects_all
WHERE project_id = c_project_id;
PA_DEBUG.set_curr_function(p_function => 'UPDATE_REQUIREMENTS', p_debug_mode => l_debug_mode);
savepoint UPDATE_REQUIREMENTS_SP;
pa_debug.write(l_module, 'Start of update_requirements', l_log_level);
pa_debug.write(l_module, 'Calling PA_ASSIGNMENTS_PUB.EXECUTE_UPDATE_REQUIREMENT for Record#'||i, l_log_level);
PA_ASSIGNMENTS_PUB.EXECUTE_UPDATE_REQUIREMENT
(
p_api_version => p_api_version_number
, p_init_msg_list => l_init_msg_list
, p_commit => l_commit
, p_validate_only => l_validate_only
, p_asgn_update_mode => l_asgn_update_mode
, p_assignment_id => l_req_rec.requirement_id
, p_assignment_name => l_req_rec.requirement_name
-- , p_assignment_number =>
, p_assignment_type => l_assignment_type
, p_assignment_template_id => l_req_rec.team_template_id
-- , p_source_assignment_id => l_req_dtls_csr.source_assignment_id
-- , p_number_of_requirements => l_req_rec.number_of_requirements
, p_project_role_id => l_req_rec.project_role_id
, p_project_role_name => l_req_rec.project_role_name
, p_project_id => l_req_rec.project_id
-- , p_project_name => l_req_rec.project_name
, p_project_number => l_req_rec.project_number
-- , p_resource_id =>
-- , p_project_party_id =>
-- , p_resource_name =>
-- , p_resource_source_id => null
, p_staffing_owner_person_id => l_req_rec.staffing_owner_person_id
-- , p_staffing_owner_name =>
, p_staffing_priority_code => l_req_rec.staffing_priority_code
, p_staffing_priority_name => l_req_rec.staffing_priority_name
, p_project_subteam_id => l_req_rec.project_subteam_id
, p_project_subteam_name => l_req_rec.project_subteam_name
, p_project_subteam_party_id => l_project_subteam_party_id
, p_location_id => l_req_rec.location_id
, p_location_city => l_req_rec.location_city
, p_location_region => l_req_rec.location_region
, p_location_country_name => l_req_rec.location_country_name
, p_location_country_code => l_req_rec.location_country_code
, p_min_resource_job_level => l_req_rec.min_resource_job_level
, p_max_resource_job_level => l_req_rec.max_resource_job_level
, p_description => l_req_rec.description
, p_additional_information => l_req_rec.additional_information
, p_start_date => l_req_rec.start_date
, p_end_date => l_req_rec.end_date
, p_status_code => l_req_rec.status_code
, p_project_status_name => l_req_rec.status_name
, p_multiple_status_flag => l_req_dtls_csr.multiple_status_flag
-- , p_assignment_effort =>
-- , p_resource_list_member_id => l_req_dtls_csr.resource_list_member_id
-- , p_budget_version_id =>
-- , p_sum_tasks_flag =>
-- , p_calendar_type => l_req_rec.calendar_type
, p_calendar_id => l_req_rec.calendar_id
, p_calendar_name => l_req_rec.calendar_name
-- , p_start_adv_action_set_flag => l_req_rec.start_adv_action_set_flag
-- , p_adv_action_set_id => l_req_rec.adv_action_set_id
-- , p_adv_action_set_name => l_req_rec.adv_action_set_name
-- As of now internal code does not support setting the candidate search options
-- at create time. It can only be updated.
, p_comp_match_weighting => l_req_rec.comp_match_weighting
, p_avail_match_weighting => l_req_rec.avail_match_weighting
, p_job_level_match_weighting => l_req_rec.job_level_match_weighting
, p_enable_auto_cand_nom_flag => l_req_rec.enable_auto_cand_nom_flag
, p_search_min_availability => l_req_rec.search_min_availability
, p_search_exp_org_struct_ver_id => l_req_rec.search_exp_org_str_ver_id
, p_search_exp_org_hier_name => l_req_rec.search_exp_org_hier_name
, p_search_exp_start_org_id => l_req_rec.search_exp_start_org_id
, p_search_exp_start_org_name => l_req_rec.search_exp_start_org_name
, p_search_country_code => l_req_rec.search_country_code
, p_search_country_name => l_req_rec.search_country_name
, p_search_min_candidate_score => l_req_rec.search_min_candidate_score
, p_expenditure_org_id => l_req_rec.expenditure_org_id
, p_expenditure_org_name => l_req_rec.expenditure_org_name
, p_expenditure_organization_id => l_req_rec.expenditure_organization_id
, p_exp_organization_name => l_req_rec.expenditure_organization_name
, p_expenditure_type_class => l_req_rec.expenditure_type_class
, p_expenditure_type => l_req_rec.expenditure_type
, p_fcst_job_group_id => l_req_rec.fcst_job_group_id
, p_fcst_job_group_name => l_req_rec.fcst_job_group_name
, p_fcst_job_id => l_req_rec.fcst_job_id
, p_fcst_job_name => l_req_rec.fcst_job_name
-- , p_fcst_tp_amount_type => l_req_rec.fcst_tp_amount_type
, p_work_type_id => l_req_rec.work_type_id
, p_work_type_name => l_req_rec.work_type_name
, p_bill_rate_override => l_req_rec.bill_rate_override
, p_bill_rate_curr_override => l_req_rec.bill_rate_curr_override
, p_markup_percent_override => l_req_rec.markup_percent_override
, p_discount_percentage => l_req_rec.discount_percentage
, p_rate_disc_reason_code => l_req_rec.rate_disc_reason_code
, p_tp_rate_override => l_req_rec.tp_rate_override
, p_tp_currency_override => l_req_rec.tp_currency_override
, p_tp_calc_base_code_override => l_req_rec.tp_calc_base_code_override
, p_tp_percent_applied_override => l_req_rec.tp_percent_applied_override
, p_extension_possible => l_req_rec.extension_possible
, p_expense_owner => l_req_rec.expense_owner
, p_expense_limit => l_req_rec.expense_limit
, p_expense_limit_currency_code => l_req_dtls_csr.expense_limit_currency_code
, p_revenue_currency_code => l_req_dtls_csr.revenue_currency_code
, p_revenue_bill_rate => l_req_dtls_csr.revenue_bill_rate
, p_markup_percent => l_req_dtls_csr.markup_percent
-- , p_resource_calendar_percent =>
, p_record_version_number => l_req_rec.record_version_number
, p_attribute_category => l_req_rec.attribute_category
, p_attribute1 => l_req_rec.attribute1
, p_attribute2 => l_req_rec.attribute2
, p_attribute3 => l_req_rec.attribute3
, p_attribute4 => l_req_rec.attribute4
, p_attribute5 => l_req_rec.attribute5
, p_attribute6 => l_req_rec.attribute6
, p_attribute7 => l_req_rec.attribute7
, p_attribute8 => l_req_rec.attribute8
, p_attribute9 => l_req_rec.attribute9
, p_attribute10 => l_req_rec.attribute10
, p_attribute11 => l_req_rec.attribute11
, p_attribute12 => l_req_rec.attribute12
, p_attribute13 => l_req_rec.attribute13
, p_attribute14 => l_req_rec.attribute14
, p_attribute15 => l_req_rec.attribute15
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
UPDATE PA_PROJECT_ASSIGNMENTS
SET orig_system_code = decode(l_req_rec.orig_system_code, null, orig_system_code, l_req_rec.orig_system_code)
, orig_system_reference = decode(l_req_rec.orig_system_reference, null, orig_system_reference, l_req_rec.orig_system_reference)
WHERE assignment_id = l_req_rec.requirement_id;
FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
ROLLBACK TO UPDATE_REQUIREMENTS_SP;
ROLLBACK TO UPDATE_REQUIREMENTS_SP;
, p_procedure_name => 'UPDATE_REQUIREMENTS'
, p_error_text => x_msg_data);
END UPDATE_REQUIREMENTS;
PROCEDURE DELETE_REQUIREMENTS
(
p_api_version_number IN NUMBER := 1.0
, p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_requirement_in_tbl IN REQUIREMENT_IN_TBL_TYPE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_calling_application VARCHAR2(10) := 'PLSQL';
l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.DELETE_REQUIREMENTS';
SELECT ROWID,project_id,record_version_number,ASSIGNMENT_TEMPLATE_ID,assignment_number,status_code
FROM pa_project_assignments
WHERE assignment_id = p_requirement_id
AND ASSIGNMENT_TYPE = l_assignment_type ;
SELECT PROJECT_SYSTEM_STATUS_CODE
FROM pa_project_statuses
WHERE project_status_code = l_status_code
AND status_type = 'OPEN_ASGMT';
PA_DEBUG.set_curr_function(p_function => 'DELETE_REQUIREMENTS', p_debug_mode => l_debug_mode);
savepoint DELETE_REQUIREMENTS_SP;
pa_debug.write(l_module, 'Start of DELETE_REQUIREMENTS', l_log_level);
pa_debug.write(l_module, 'Calling PA_ASSIGNMENTS_PUB.DELETE_ASSIGNMENT for record number'||i, l_log_level);
PA_ASSIGNMENTS_PUB.Delete_Assignment
( p_assignment_row_id => l_assignment_row_id
, p_assignment_id => l_assignment_id
, p_record_version_number => l_record_version_number
, p_assignment_type => l_assignment_type
, p_assignment_number => l_assignment_number
, p_commit => l_commit
, p_validate_only => l_validate_only
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
pa_debug.write(l_module, 'After call PA_ASSIGNMENTS_PUB.DELETE_ASSIGNMENT l_return_status='||l_return_status, l_log_level);
FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
ROLLBACK TO DELETE_REQUIREMENTS_SP;
ROLLBACK TO DELETE_REQUIREMENTS_SP;
, p_procedure_name => 'DELETE_REQUIREMENTS'
, p_error_text => x_msg_data);
END DELETE_REQUIREMENTS;
SELECT project_id,ASSIGNMENT_TEMPLATE_ID ,status_code,start_date,end_date
FROM pa_project_assignments
WHERE assignment_id = c_source_reqmt_id ;
SELECT project_status_name from pa_project_statuses
where
status_type = 'STAFFED_ASGMT'
and project_status_code = l_assignment_status_code
and trunc(SYSDATE) between start_date_active and nvl(end_date_active, trunc(SYSDATE))
and starting_status_flag = 'Y' and project_system_status_code <> 'STAFFED_ASGMT_CONF';
SELECT project_status_name from pa_project_statuses
where
status_type = 'OPEN_ASGMT'
and project_status_code = l_unfilled_assign_status_code
and trunc(SYSDATE) between start_date_active and nvl(end_date_active, trunc(SYSDATE))
and starting_status_flag = 'Y' ;
SELECT project_status_code from pa_project_statuses
where
status_type = 'STAFFED_ASGMT'
and project_status_name = l_assignment_status_name
and trunc(SYSDATE) between start_date_active and nvl(end_date_active, trunc(SYSDATE))
and starting_status_flag = 'Y' and project_system_status_code <> 'STAFFED_ASGMT_CONF';
SELECT project_status_code from pa_project_statuses
where
status_type = 'OPEN_ASGMT'
and project_status_name = l_unfilled_assign_status_name
and trunc(SYSDATE) between start_date_active and nvl(end_date_active, trunc(SYSDATE))
and starting_status_flag = 'Y' ;
-- Because stack moves down after delete
FND_MSG_PUB.get (
p_msg_index => l_start_msg_count+1,
p_encoded => FND_API.G_FALSE,
p_data => l_data,
p_msg_index_out => l_msg_index_out );
-- Always delete at first location in stack i.e. l_start_msg_count+1
-- Because stack moves down after delete
FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
SELECT project_id,ASSIGNMENT_TEMPLATE_ID,status_code
FROM pa_project_assignments
WHERE assignment_id = p_req_asgn_id ;
SELECT PROJECT_SYSTEM_STATUS_CODE
FROM pa_project_statuses
WHERE project_status_code = l_status_code
AND status_type in ('OPEN_ASGMT','STAFFED_ASGMT');
FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
SELECT lookup_code
FROM pa_lookups
WHERE lookup_type = 'CHANGE_CALENDAR_TYPE_CODE'
AND lookup_code = c_code
AND lookup_code <> 'RESOURCE';
SELECT role_list_id, multi_currency_billing_flag, calendar_id, work_type_id, location_id
FROM pa_projects_all
WHERE project_id = c_project_id;
SELECT role_list_id, calendar_id, work_type_id
FROM pa_team_templates
WHERE team_template_id = c_team_templ_id;
SELECT meaning
FROM pa_project_role_types_vl
WHERE project_role_id = c_role_id ;
SELECT impl.rate_discount_reason_flag ,impl.br_override_flag, impl.br_discount_override_flag
FROM pa_implementations_all impl
, pa_projects_all proj
WHERE proj.org_id=impl.org_id -- Removed nvl condition from org_id : Post review changes for Bug 5130421
AND proj.project_id = c_project_id ;
SELECT 'Y'
FROM dual
WHERE EXISTS
(SELECT 'XYZ' FROM pa_lookups WHERE lookup_type = c_lookup_type AND lookup_code = c_lookup_code);
SELECT country_code, region, city
FROM pa_locations
WHERE location_id = c_location_id;
SELECT country_code
FROM pa_country_v
WHERE name = c_country_name;
SELECT name
FROM pa_country_v
WHERE country_code = c_country_code;
SELECT
ra.resource_list_member_id
, firstrow.person_id
, rlm.resource_id
, PA_RESOURCE_UTILS.get_person_name_no_date(firstrow.person_id)
, ra.project_id
, ra.budget_version_id
, decode (ra.role_count, 1, firstrow.named_role, null) named_role
, decode (ra.role_count, 1, firstrow.project_role_id, null) project_role_id
, decode (ra.role_count, 1, ro.meaning, null) project_role
, ra.min_date task_assign_start_date
, ra.max_date task_assign_end_date
, firstrow.resource_assignment_id
, firstrow.res_type_code
FROM pa_resource_assignments firstrow
, pa_resource_list_members rlm
, pa_proj_roles_v ro
, (SELECT project_id , budget_version_id , resource_list_member_id , count(1) role_count , max(max_id) max_id
, min(min_date) min_date , max(max_date) max_date
FROM (SELECT project_id , budget_version_id , resource_list_member_id , project_role_id
, max(resource_assignment_id) max_id , min(SCHEDULE_START_DATE) min_date , max(SCHEDULE_END_DATE) max_date
FROM pa_resource_assignments
WHERE ta_display_flag = 'Y' and nvl(PROJECT_ASSIGNMENT_ID, -1) = -1
AND resource_class_code = 'PEOPLE'
GROUP BY project_id, budget_version_id, resource_list_member_id, project_role_id
) res_roles
GROUP BY project_id, budget_version_id, resource_list_member_id
) ra
WHERE ra.resource_list_member_id = rlm.resource_list_member_id
AND firstrow.resource_assignment_id = ra.max_id
AND firstrow.project_role_id = ro.project_role_id (+)
AND ra.budget_version_id = c_budget_version_id
AND ra.resource_list_member_id = c_resource_list_member_id
AND ra.project_id = c_project_id
AND firstrow.person_id IS NULL;
SELECT project_system_status_code,starting_status_flag
FROM pa_project_statuses
WHERE status_type = c_status_type
AND project_status_code = c_status_code;
SELECT project_system_status_code,starting_status_flag
FROM pa_project_statuses
WHERE status_type = c_status_type
AND project_status_name = c_status_name;
UPDATE PA_PROJECT_ASSIGNMENTS
SET orig_system_code = l_asgn_rec.orig_system_code
, orig_system_reference = l_asgn_rec.orig_system_reference
WHERE assignment_id = l_new_assignment_id_tbl(j);
FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
PROCEDURE UPDATE_ASSIGNMENTS
(
p_api_version_number IN NUMBER := 1.0
, p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_assignment_in_tbl IN ASSIGNMENT_IN_TBL_TYPE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_calling_application VARCHAR2(10) := 'PLSQL';
l_module VARCHAR2(100) := 'PA_RES_MANAGEMENT_AMG_PUB.UPDATE_ASSIGNMENTS';
l_asgn_update_mode VARCHAR2(10) := 'FULL'; -- This is just a dummy value
SELECT project_system_status_code
FROM pa_project_statuses
WHERE project_status_code = l_in_status_code
AND status_type= 'ASGMT_APPRVL';
SELECT * from pa_project_assignments
WHERE assignment_id = l_assignment_id
AND assignment_type <> 'OPEN_ASSIGNMENT' ;
SELECT project_subteam_party_id, project_subteam_id
FROM pa_project_subteam_parties
WHERE object_id = l_in_assignment_id
AND object_type = 'PA_PROJECT_ASSIGNMENTS'
AND primary_subteam_flag = 'Y';
SELECT impl.rate_discount_reason_flag ,impl.br_override_flag, impl.br_discount_override_flag
FROM pa_implementations_all impl
, pa_projects_all proj
WHERE proj.org_id=impl.org_id -- Removed nvl condition from org_id : Post review changes for Bug 5130421
AND proj.project_id = c_project_id ;
SELECT 'Y'
FROM dual
WHERE EXISTS
(SELECT 'XYZ' FROM pa_lookups WHERE lookup_type = c_lookup_type AND lookup_code = c_lookup_code);
SELECT COUNTRY_CODE
FROM PA_COUNTRY_V
WHERE NAME = c_country_name;
SELECT NAME
FROM PA_COUNTRY_V
WHERE COUNTRY_CODE = c_country_code;
SELECT multi_currency_billing_flag
FROM pa_projects_all
WHERE project_id = c_project_id;
PA_DEBUG.set_curr_function(p_function => 'UPDATE_ASSIGNMENTS', p_debug_mode => l_debug_mode);
savepoint UPDATE_ASSIGNMENTS_SP;
pa_debug.write(l_module, 'Start of UPDATE_ASSIGNMENTS', l_log_level);
-- Update Project Assignment Flow
-- Bug 5174557 : Assignment Type Change is not allowed.
IF l_asgn_rec.assignment_type IS NOT NULL AND
l_asgn_rec.assignment_type <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
IF l_asgn_rec.assignment_type <> l_asgn_db_values_rec.assignment_type THEN
l_missing_params := l_missing_params||', ASSIGNMENT_TYPE';
SELECT country_code,region,city
INTO l_dummy_country_code,l_dummy_state,l_dummy_city
FROM PA_LOCATIONS
WHERE location_id = l_asgn_db_values_rec.location_id;
-- These parameters are Not used for Update Flow:
-- Internal API requires to pass FND_API miss nums instead of null
-- if we pass null, they treat it as update and raise error
l_asgn_rec.project_role_id := FND_API.G_MISS_NUM;
-- These parameters are not For Update flow
l_asgn_rec.start_date := l_asgn_db_values_rec.start_date ;
pa_debug.write(l_module, 'Calling PA_ASSIGNMENTS_PUB.EXECUTE_UPDATE_ASSIGNMENT for Record No.'||i, l_log_level);
--This consistent with Update_Assignment API code in PARAPUBB.pls
IF (l_asgn_rec.location_country_code IS NULL
AND l_asgn_rec.location_country_name IS NULL)
THEN
l_asgn_rec.location_id := NULL ;
PA_ASSIGNMENTS_PUB.EXECUTE_UPDATE_ASSIGNMENT
(
p_api_version => p_api_version_number
, p_init_msg_list => l_init_msg_list
, p_commit => l_commit
, p_validate_only => l_validate_only
, p_asgn_update_mode => l_asgn_update_mode
, p_assignment_id => l_asgn_rec.assignment_id
, p_record_version_number => l_asgn_rec.record_version_number
, p_assignment_name => l_asgn_rec.assignment_name
, p_assignment_type => l_asgn_rec.assignment_type
, p_multiple_status_flag => l_asgn_db_values_rec.multiple_status_flag
, p_project_status_name => l_asgn_rec.status_name
, p_status_code => l_asgn_rec.status_code
, p_start_date => l_asgn_rec.start_date
, p_end_date => l_asgn_rec.end_date
, p_staffing_priority_code => l_asgn_rec.staffing_priority_code
, p_project_id => l_asgn_rec.project_id
-- , p_assignment_template_id => l_asgn_rec.assignment_template_id
, p_project_role_id => l_asgn_rec.project_role_id
, p_project_subteam_id => l_asgn_rec.project_subteam_id
, p_project_subteam_party_id => l_project_subteam_party_id
, p_description => l_asgn_rec.description
-- , p_assignment_effort => l_asgn_rec.assignment_effort
, p_extension_possible => l_asgn_rec.extension_possible
-- , p_source_assignment_id => l_asgn_rec.source_assignment_id
-- , p_min_resource_job_level => l_asgn_rec.min_resource_job_level
-- , p_max_resource_job_level => l_asgn_rec.max_resource_job_level
-- , p_assignment_number => l_asgn_rec.assignment_number --
, p_additional_information => l_asgn_rec.additional_information
, p_location_id => l_asgn_rec.location_id
, p_work_type_id => l_asgn_rec.work_type_id
-- ,p_revenue_currency_code => l_asgn_rec.revenue_currency_code
-- ,p_revenue_bill_rate => l_asgn_rec.revenue_bill_rate
-- ,p_markup_percent => l_asgn_rec.markup_percent
,p_expense_owner => l_asgn_rec.expense_owner
,p_expense_limit => l_asgn_rec.expense_limit
-- ,p_expense_limit_currency_code => l_asgn_rec.expense_limit_currency_code
-- ,p_fcst_tp_amount_type => l_asgn_rec.fcst_tp_amount_type
-- ,p_fcst_job_id => l_asgn_rec.fcst_job_id
-- ,p_fcst_job_group_id => l_asgn_rec.fcst_job_group_id
-- ,p_expenditure_org_id => l_asgn_rec.expenditure_org_id
-- ,p_expenditure_organization_id => l_asgn_rec.expenditure_organization_id
,p_expenditure_type_class => l_asgn_rec.expenditure_type_class
,p_expenditure_type => l_asgn_rec.expenditure_type
-- ,p_project_number =>
-- ,p_resource_name =>
-- ,p_resource_source_id =>
,p_resource_id => l_asgn_rec.resource_id
,p_project_subteam_name => l_asgn_rec.project_subteam_name
,p_staffing_priority_name => l_asgn_rec.staffing_priority_name
-- ,p_project_role_name => l_asgn_rec.project_role_name
,p_location_city => l_asgn_rec.location_city
,p_location_region => l_asgn_rec.location_region
-- ,p_location_country_name => l_asgn_rec.location_country_name
,p_location_country_code => l_asgn_rec.location_country_code
,p_calendar_name => l_asgn_rec.calendar_name
,p_calendar_id => l_asgn_rec.calendar_id
,p_work_type_name => l_asgn_rec.work_type_name
-- ,p_fcst_job_name =>
-- ,p_fcst_job_group_name
-- ,p_expenditure_org_name
-- ,p_exp_organization_name
-- ,p_comp_match_weighting
-- ,p_avail_match_weighting
-- ,p_job_level_match_weighting
-- ,p_search_min_availability
-- ,p_search_country_code
-- ,p_search_country_name
-- ,p_search_exp_org_struct_ver_id
-- ,p_search_exp_org_hier_name
-- ,p_search_exp_start_org_id
-- ,p_search_exp_start_org_name
-- ,p_search_min_candidate_score
-- ,p_enable_auto_cand_nom_flag
,p_bill_rate_override => l_asgn_rec.bill_rate_override
,p_bill_rate_curr_override => l_asgn_rec.bill_rate_curr_override
,p_markup_percent_override => l_asgn_rec.markup_percent_override
,p_discount_percentage => l_asgn_rec.discount_percentage
,p_rate_disc_reason_code => l_asgn_rec.rate_disc_reason_code
,p_tp_rate_override => l_asgn_rec.tp_rate_override
,p_tp_currency_override => l_asgn_rec.tp_currency_override
,p_tp_calc_base_code_override => l_asgn_rec.tp_calc_base_code_override
,p_tp_percent_applied_override => l_asgn_rec.tp_percent_applied_override
,p_staffing_owner_person_id => l_asgn_rec.staffing_owner_person_id
-- ,p_staffing_owner_name =>
-- ,p_resource_list_member_id
,p_attribute_category => l_asgn_rec.attribute_category
,p_attribute1 => l_asgn_rec.attribute1
,p_attribute2 => l_asgn_rec.attribute2
,p_attribute3 => l_asgn_rec.attribute3
,p_attribute4 => l_asgn_rec.attribute4
,p_attribute5 => l_asgn_rec.attribute5
,p_attribute6 => l_asgn_rec.attribute6
,p_attribute7 => l_asgn_rec.attribute7
,p_attribute8 => l_asgn_rec.attribute8
,p_attribute9 => l_asgn_rec.attribute9
,p_attribute10 => l_asgn_rec.attribute10
,p_attribute11 => l_asgn_rec.attribute11
,p_attribute12 => l_asgn_rec.attribute12
,p_attribute13 => l_asgn_rec.attribute13
,p_attribute14 => l_asgn_rec.attribute14
,p_attribute15 => l_asgn_rec.attribute15
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
pa_debug.write(l_module, 'After call PA_ASSIGNMENTS_PUB.EXECUTE_UPDATE_ASSIGNMENT l_return_status='||l_return_status, l_log_level);
-- UPDATE PA_PROJECT_ASSIGNMENTS
-- SET orig_system_code = decode(l_asgn_rec.orig_system_code, null, orig_system_code, l_asgn_rec.orig_system_code)
-- , orig_system_reference = decode(l_asgn_rec.orig_system_reference, null, orig_system_reference, l_asgn_rec.orig_system_reference)
-- WHERE assignment_id = l_asgn_rec.assignment_id;
UPDATE PA_PROJECT_ASSIGNMENTS
SET orig_system_code = l_asgn_rec.orig_system_code
, orig_system_reference = l_asgn_rec.orig_system_reference
WHERE assignment_id = l_asgn_rec.assignment_id;
FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
ROLLBACK TO UPDATE_ASSIGNMENTS_SP;
ROLLBACK TO UPDATE_ASSIGNMENTS_SP;
, p_procedure_name => 'UPDATE_ASSIGNMENTS'
, p_error_text => x_msg_data);
END UPDATE_ASSIGNMENTS ;
PROCEDURE DELETE_ASSIGNMENTS (
p_commit IN VARCHAR2 := 'F'
, p_init_msg_list IN VARCHAR2 := 'T'
, p_api_version_number IN NUMBER := 1.0
, p_assignment_in_tbl IN ASSIGNMENT_IN_TBL_TYPE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
-- Debug Params
l_debug_level NUMBER :=3;
l_module VARCHAR2(255) := 'PA_RES_MANAGEMENT_AMG_PUB.DELETE_ASSIGNMENTS';
SELECT ROWID , assignment_type, status_code, project_id, record_version_number, assignment_number,
resource_id, start_date, mass_wf_in_progress_flag, apprvl_status_code
FROM pa_project_assignments
WHERE assignment_id=l_assignment_id
AND assignment_type <> 'OPEN_ASSIGNMENT' ;
SELECT PROJECT_SYSTEM_STATUS_CODE
FROM pa_project_statuses
WHERE project_status_code = l_status_code ;
PA_DEBUG.set_curr_function(p_function => 'DELETE_ASSIGNMENTS', p_debug_mode => l_debug_mode);
savepoint DELETE_ASSIGNMENTS_SP;
pa_debug.write(l_module, 'Start of delete_assignments', l_debug_level);
pa_debug.write(l_module, 'Calling PA_ASSIGNMENTS_PUB.DELETE_ASSIGNMENT for record number'||i, l_debug_level);
PA_ASSIGNMENTS_PUB.DELETE_ASSIGNMENT (
p_assignment_row_id => l_assignment_row_id
, p_assignment_id => l_assignment_id
, p_record_version_number => l_record_version_number
, p_assignment_type => l_assignment_type
, p_assignment_number => l_assignment_number
, p_commit => l_commit
, p_validate_only => l_validate_only
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data );
pa_debug.write(l_module, 'After call PA_ASSIGNMENTS_PUB.DELETE_ASSIGNMENT l_return_status='||l_return_status, l_debug_level);
FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
ROLLBACK TO DELETE_ASSIGNMENTS_SP;
ROLLBACK TO DELETE_ASSIGNMENTS_SP;
, p_procedure_name => 'DELETE_REQUIREMENTS'
, p_error_text => x_msg_data);
END DELETE_ASSIGNMENTS;
SELECT project_id , resource_id,start_date,assignment_type,mass_wf_in_progress_flag ,apprvl_status_code,record_version_number
FROM pa_project_assignments
WHERE assignment_type <> 'OPEN_ASSIGNMENT'
AND assignment_id = p_assignment_id ;
SELECT per.full_name, ppt.SYSTEM_PERSON_TYPE , ppt.USER_PERSON_TYPE
FROM per_all_people_f per , per_person_types ppt
where per.person_type_id = ppt.person_type_id
AND per.person_id = p_person_id
AND per.effective_end_date = (SELECT MAX(pf.effective_end_date)
FROM per_all_people_f pf
WHERE pf.person_id = p_person_id);
SELECT project_system_status_code
FROM pa_project_statuses
WHERE project_status_code = l_in_status_code
AND status_type= 'ASGMT_APPRVL';
FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
SELECT project_id,status_code,mass_wf_in_progress_flag
FROM pa_project_assignments
where assignment_id = l_requirement_id
AND assignment_type = 'OPEN_ASSIGNMENT'
AND nvl(template_flag,'N') = 'N';
SELECT project_system_status_code
FROM pa_project_statuses
WHERE project_status_code = l_status_code
AND status_type= 'OPEN_ASGMT';
FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
PROCEDURE UPDATE_REQUIREMENT_COMPETENCE
(
p_commit IN VARCHAR2 := 'F'
, p_init_msg_list IN VARCHAR2 := 'T'
, p_api_version_number IN NUMBER := 1.0
, p_competence_in_tbl IN COMPETENCE_IN_TBL_TYPE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
-- Debug Level
l_module VARCHAR2(255) := 'PA_RES_MANAGEMENT_AMG_PUB.UPDATE_REQUIREMENT_COMPETENCE';
SELECT *
FROM PA_OPEN_ASGMT_COMPETENCES_V
WHERE competence_element_id = l_competence_element_id;
SELECT *
FROM PA_OPEN_ASGMT_COMPETENCES_V
WHERE assignment_id = l_requirement_id
AND competence_id=l_competence_id;*/
SELECT project_id,assignment_template_id,status_code,mass_wf_in_progress_flag
FROM pa_project_assignments
where assignment_id = l_requirement_id;
SELECT project_system_status_code
FROM pa_project_statuses
WHERE project_status_code = l_status_code;
-- Only we will Allow Mandatery Flag and Rating level to be updated.
--Flows which are not supported by this API
----------------------------------------------------------
-- Updating a Competence by Passing Combination of Requirement_id and one of the
-- competence id or competence alias or competence name
x_return_status := FND_API.G_RET_STS_SUCCESS;
PA_DEBUG.set_curr_function(p_function => 'UPDATE_REQUIREMENT_COMPETENCE', p_debug_mode => l_debug_mode);
SAVEPOINT UPDATE_REQU_COMPETENCE_SP;
pa_debug.write(l_module,'Start of UPDATE_REQUIREMENT_COMPETENCE ', l_debug_level);
pa_debug.write(l_module, 'Calling PA_COMPETENCE_PUB.UPDATE_COMPETENCE_ELEMENT ', l_debug_level);
PA_COMPETENCE_PUB.UPDATE_COMPETENCE_ELEMENT(
p_object_name =>'OPEN_ASSIGNMENT'
, p_object_id => l_competence_in_rec.requirement_id
, p_competence_id => l_competence_in_rec.competence_id
, p_competence_alias => l_competence_in_rec.competence_alias
, p_competence_name => l_competence_in_rec.competence_name
, p_element_id => l_competence_in_rec.competence_element_id
, p_rating_level_id => l_competence_in_rec.rating_level_id
, p_rating_level_value => l_competence_in_rec.rating_level_value
, p_mandatory_flag => l_competence_in_rec.mandatory_flag
, p_init_msg_list => 'F'
, p_element_rowid => l_cur_competence_in_rec.row_id
, p_commit => l_commit
, p_validate_only => 'N'
, p_object_version_number => l_competence_in_rec.record_version_number
, x_object_version_number => l_object_version_number
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_return_status => l_return_status);
pa_debug.write(l_module, 'After Calling PA_COMPETENCE_PUB.UPDATE_COMPETENCE_ELEMENT ', l_debug_level);
pa_debug.write(l_module, 'After Calling PA_COMPETENCE_PUB.UPDATE_COMPETENCE_ELEMENT ', l_debug_level);
FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
pa_debug.write(l_module,'Out Of PA_COMPETENCE_PUB.UPDATE_COMPETENCE_ELEMENT API calling Loop', l_debug_level);
ROLLBACK TO UPDATE_REQU_COMPETENCE_SP;
ROLLBACK TO UPDATE_REQU_COMPETENCE_SP;
END UPDATE_REQUIREMENT_COMPETENCE;
PROCEDURE DELETE_REQUIREMENT_COMPETENCE (
p_commit IN VARCHAR2 :='F'
, p_init_msg_list IN VARCHAR2 := 'T'
, p_api_version_number IN NUMBER := 1.0
, p_competence_in_tbl IN COMPETENCE_IN_TBL_TYPE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
-- Debug Level
l_module VARCHAR2(255) := 'PA_RES_MANAGEMENT_AMG_PUB.DELETE_REQUIREMENT_COMPETENCE';
SELECT *
FROM PA_OPEN_ASGMT_COMPETENCES_V
WHERE competence_element_id = l_competence_element_id;
SELECT project_id,assignment_template_id,status_code,mass_wf_in_progress_flag
FROM pa_project_assignments
where assignment_id = l_requirement_id;
SELECT project_system_status_code
FROM pa_project_statuses
WHERE project_status_code = l_status_code
AND status_type= 'OPEN_ASGMT';
PA_DEBUG.set_curr_function(p_function => 'DELETE_REQUIREMENT_COMPETENCE', p_debug_mode => l_debug_mode);
SAVEPOINT DELETE_REQU_COMPETENCE_SP;
pa_debug.write(l_module,'Start of DELETE_REQUIREMENT_COMPETENCE ', l_debug_level);
pa_debug.write(l_module, 'Calling PA_COMPETENCE_PUB.DELETE_COMPETENCE_ELEMENT', l_debug_level);
PA_COMPETENCE_PUB.DELETE_COMPETENCE_ELEMENT(
p_object_name => 'OPEN_ASSIGNMENT'
, p_object_id => l_competence_in_rec.requirement_id
, p_competence_id => l_competence_in_rec.competence_id
, p_competence_alias => l_competence_in_rec.competence_alias
, p_competence_name => l_competence_in_rec.competence_name
, p_element_rowid => l_cur_competence_in_rec.row_id
, p_element_id => l_competence_in_rec.competence_element_id
, p_init_msg_list => 'F'
, p_commit => l_commit
, p_validate_only => 'F'
, p_object_version_number =>l_competence_in_rec.record_version_number
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data);
pa_debug.write(l_module,'After Calling PA_COMPETENCE_PUB.DELETE_COMPETENCE_ELEMENT ', l_debug_level);
pa_debug.write(l_module,'After Calling PA_COMPETENCE_PUB.DELETE_COMPETENCE_ELEMENT ', l_debug_level);
FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
pa_debug.write(l_module,'Out Of PA_COMPETENCE_PUB.DELETE_COMPETENCE_ELEMENT API calling Loop', l_debug_level);
ROLLBACK TO DELETE_REQU_COMPETENCE_SP;
ROLLBACK TO DELETE_REQU_COMPETENCE_SP;
END DELETE_REQUIREMENT_COMPETENCE;
SELECT project_id, start_date, status_code, mass_wf_in_progress_flag
FROM pa_project_assignments
WHERE assignment_id = c_assignment_id
AND assignment_type = 'OPEN_ASSIGNMENT';
SELECT project_system_status_code
FROM pa_project_statuses
WHERE trunc(SYSDATE) BETWEEN start_date_active AND nvl(end_date_active, trunc(SYSDATE))
AND status_type = c_status_type
AND project_status_code = c_status_code;
SELECT resource_source_id
FROM pa_c_elig_resource_v
WHERE resource_id = c_resource_id;
SELECT employee_id
FROM fnd_user
WHERE user_id = c_user_id;
SELECT candidate_id
FROM pa_candidates
WHERE assignment_id = c_assignment_id
AND resource_id = c_resource_id;
FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
PROCEDURE UPDATE_CANDIDATES
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
, p_api_version_number IN NUMBER := 1.0
, p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_debug_mode VARCHAR2(1);
l_module VARCHAR2(100) := 'PA_CANDIDATE_AMG_PUB.UPDATE_CANDIDATES';
SELECT project_system_status_code FROM pa_project_statuses
WHERE trunc(SYSDATE) BETWEEN start_date_active AND nvl(end_date_active, trunc(SYSDATE))
AND status_type = 'CANDIDATE'
AND project_status_code = c_status_code;
SELECT project_id
FROM pa_project_assignments
WHERE assignment_id = c_assignment_id
AND assignment_type = 'OPEN_ASSIGNMENT';
SELECT candidate_id, status_code, candidate_ranking, record_version_number, assignment_id
FROM pa_candidates
WHERE candidate_id = c_candidate_id;
PA_DEBUG.set_curr_function(p_function => 'UPDATE_CANDIDATES', p_debug_mode => l_debug_mode);
savepoint UPDATE_CANDIDATES_SP;
pa_debug.write(l_module, 'Start of UPDATE_CANDIDATES', l_log_level);
pa_debug.write(l_module, 'Calling PA_CANDIDATE_PUB.UPDATE_CANDIDATE for Record#'||i, l_log_level);
PA_CANDIDATE_PUB.UPDATE_CANDIDATE
(
p_candidate_id => l_candidate_in_rec.candidate_id
, p_status_code => l_candidate_in_rec.status_code
, p_ranking => l_candidate_in_rec.ranking
, p_change_reason_code => l_candidate_in_rec.change_reason_code
, p_record_version_number => l_candidate_in_rec.record_version_number
, p_init_msg_list => FND_API.G_FALSE
, p_validate_status => FND_API.G_TRUE
-- Added for bug 9187892
, p_attribute_category => l_candidate_in_rec.attribute_category
, p_attribute1 => l_candidate_in_rec.attribute1
, p_attribute2 => l_candidate_in_rec.attribute2
, p_attribute3 => l_candidate_in_rec.attribute3
, p_attribute4 => l_candidate_in_rec.attribute4
, p_attribute5 => l_candidate_in_rec.attribute5
, p_attribute6 => l_candidate_in_rec.attribute6
, p_attribute7 => l_candidate_in_rec.attribute7
, p_attribute8 => l_candidate_in_rec.attribute8
, p_attribute9 => l_candidate_in_rec.attribute9
, p_attribute10 => l_candidate_in_rec.attribute10
, p_attribute11 => l_candidate_in_rec.attribute11
, p_attribute12 => l_candidate_in_rec.attribute12
, p_attribute13 => l_candidate_in_rec.attribute13
, p_attribute14 => l_candidate_in_rec.attribute14
, p_attribute15 => l_candidate_in_rec.attribute15
, x_record_version_number => l_record_version_number
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_return_status => l_return_status
);
pa_debug.write(l_module, 'After call PA_CANDIDATE_PUB.UPDATE_CANDIDATE l_return_status='||l_return_status, l_log_level);
pa_debug.write(l_module, 'PA_CANDIDATE_PUB.UPDATE_CANDIDATE unsuccessful', l_log_level);
pa_debug.write(l_module, 'PA_CANDIDATE_PUB.UPDATE_CANDIDATE successful', l_log_level);
pa_debug.write(l_module, 'Updated record_version_number = '||l_record_version_number, l_log_level);
FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
ROLLBACK TO UPDATE_CANDIDATES_SP;
ROLLBACK TO UPDATE_CANDIDATES_SP;
, p_procedure_name => 'UPDATE_CANDIDATES'
, p_error_text => x_msg_data);
END UPDATE_CANDIDATES;
PROCEDURE DELETE_CANDIDATES
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
, p_api_version_number IN NUMBER := 1.0
, p_candidate_in_tbl IN CANDIDATE_IN_TBL_TYPE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_debug_mode VARCHAR2(1);
l_module VARCHAR2(100) := 'PA_CANDIDATE_AMG_PUB.DELETE_CANDIDATES';
SELECT project_id, record_version_number
FROM pa_project_assignments
WHERE assignment_id = c_assignment_id
AND assignment_type = 'OPEN_ASSIGNMENT';
--1. Validations like whether candidates can be deleted after once being confirmed are left for the underlying apis to handle.
--2. Either all or none of the candidates would be deleted for the specified requirement_id.
x_return_status := FND_API.G_RET_STS_SUCCESS;
PA_DEBUG.set_curr_function(p_function => 'DELETE_CANDIDATES', p_debug_mode => l_debug_mode);
savepoint DELETE_CANDIDATES_SP;
pa_debug.write(l_module, 'Start of DELETE_CANDIDATES', l_log_level);
pa_debug.write(l_module, 'Calling PA_CANDIDATE_PUB.DELETE_CANDIDATES for Record#'||i, l_log_level);
PA_CANDIDATE_PUB.DELETE_CANDIDATES
(
p_assignment_id => l_candidate_in_rec.requirement_id
, p_status_code => null
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
pa_debug.write(l_module, 'After call PA_CANDIDATE_PUB.DELETE_CANDIDATES l_return_status='||l_return_status, l_log_level);
pa_debug.write(l_module, 'PA_CANDIDATE_PUB.DELETE_CANDIDATES unsuccessful', l_log_level);
pa_debug.write(l_module, 'Calling PA_PROJECT_ASSIGNMENTS_PKG.UPDATE_ROW for Record#'||i, l_log_level);
PA_PROJECT_ASSIGNMENTS_PKG.UPDATE_ROW
(
p_assignment_id => l_candidate_in_rec.requirement_id
, p_no_of_active_candidates => 0
, p_record_version_number => l_record_version_number
, x_return_status => l_return_status
);
pa_debug.write(l_module, 'After call PA_PROJECT_ASSIGNMENTS_PKG.UPDATE_ROW l_return_status='||l_return_status, l_log_level);
pa_debug.write(l_module, 'PA_PROJECT_ASSIGNMENTS_PKG.UPDATE_ROW unsuccessful', l_log_level);
pa_debug.write(l_module, 'PA_PROJECT_ASSIGNMENTS_PKG.UPDATE_ROW and PA_CANDIDATE_PUB.DELETE_CANDIDATES successful', l_log_level);
FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);
ROLLBACK TO DELETE_CANDIDATES_SP;
ROLLBACK TO DELETE_CANDIDATES_SP;
, p_procedure_name => 'DELETE_CANDIDATES'
, p_error_text => x_msg_data);
END DELETE_CANDIDATES;
SELECT lookup_code
FROM pa_lookups
WHERE lookup_type = 'CANDIDATE_STS_CHANGE_REASON'
AND lookup_code = c_change_reason_code;
SELECT pcv.project_id, pc.assignment_id, pc.record_version_number
FROM pa_candidates pc, pa_candidates_v pcv
WHERE pc.candidate_id = c_candidate_id
AND pcv.candidate_number = c_candidate_id;
SELECT project_system_status_code FROM pa_project_statuses
WHERE trunc(SYSDATE) BETWEEN start_date_active AND nvl(end_date_active, trunc(SYSDATE))
AND status_type = 'CANDIDATE'
AND project_status_code = c_status_code;
FND_MSG_PUB.DELETE_MSG(p_msg_index => l_start_msg_count+1);