[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT RESOURCE_ID
FROM PA_SEARCH_RESULTS_TEMP
WHERE MANDATORY_COMPETENCE_COUNT = MANDATORY_COMPETENCE_MATCH;
stmt_select VARCHAR2(2000);
select decode(lookup_code,'ALL','ALL','EMP','Y','CWK','N') into l_person_type
from fnd_lookup_values
where lookup_type in ('PERSON_TYPE','PA_ANY_ALL') and
meaning = p_search_criteria.person_type and language = userenv('LANG')
and lookup_code in ('EMP','CWK','ALL');
'SELECT /*+ LEADING(RD) INDEX(RD PA_RESOURCES_DENORM_N6) USE_NL(RD PDF OHD PPF) */ RD.person_id, RD.resource_id, RD.resource_name, RD.resource_type ' || -- adding another hint for 6911907
-- 'SELECT RD.person_id, RD.resource_id, RD.resource_name, RD.resource_type ' ||
', RD.resource_organization_id, RD.resource_country_code,RD.resource_country, RD.resource_region '||
', RD.resource_city, RD.resource_job_level, RD.manager_name,PPF.email_address ';
SELECT RD.person_id
, RD.resource_id
, RD.resource_name
, RD.resource_type
, RD.resource_organization_id
, RD.resource_country_code
, RD.resource_country
, RD.resource_region
, RD.resource_city
, RD.resource_job_level
, RD.manager_name
, PPF.email_address
FROM PA_RESOURCES_DENORM RD
, PA_ORG_HIERARCHY_DENORM ohd
, PER_ALL_PEOPLE_F ppf
, PER_DEPLOYMENT_FACTORS pdf
WHERE ((p_search_criteria.min_job_level IS NOT NULL AND
RD.resource_job_level >= p_search_criteria.min_job_level)
OR
p_search_criteria.min_job_level IS NULL)
AND ((p_search_criteria.max_job_level IS NOT NULL AND
RD.resource_job_level <= p_search_criteria.max_job_level)
OR
p_search_criteria.max_job_level IS NULL)
AND ((p_search_criteria.territory_code IS NOT NULL AND
(nvl(RD.resource_country_code,
p_search_criteria.territory_code) =
p_search_criteria.territory_code)) OR
(p_search_criteria.territory_code IS NULL))
AND ((p_search_criteria.region IS NOT NULL AND
(nvl(RD.resource_region, p_search_criteria.region) =
p_search_criteria.region)) OR
(p_search_criteria.region IS NULL))
AND ((p_search_criteria.city IS NOT NULL AND
(nvl(RD.resource_city, p_search_criteria.city) =
p_search_criteria.city)) OR
(p_search_criteria.city IS NULL))
AND RD.employee_flag =
decode(p_search_criteria.employees_only,'Y','Y',RD.employee_flag)
AND RD.resource_organization_id = OHD.child_organization_id
AND OHD.PARENT_ORGANIZATION_ID = p_search_criteria.organization_id
AND OHD.org_hierarchy_version_id =
p_search_criteria.org_hierarchy_version_id
AND OHD.pa_org_use_type = 'EXPENDITURES'
AND RD.SCHEDULABLE_FLAG = 'Y'
AND RD.resource_effective_start_date <= p_search_criteria.start_date
AND NVL(RD.resource_effective_end_date, p_search_criteria.start_date) >= p_search_criteria.start_date
AND RD.person_id = ppf.person_id
AND p_search_criteria.start_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND RD.person_id = pdf.person_id(+)
AND ((p_search_criteria.work_current_loc = 'Y' AND
PDF.only_current_location = 'Y') OR
(nvl(p_search_criteria.work_current_loc, 'N') = 'N'))
AND ((p_search_criteria.work_all_loc = 'Y' AND
PDF.work_any_location = 'Y') OR
(nvl(p_search_criteria.work_all_loc, 'N') = 'N'))
AND ((p_search_criteria.travel_domestically = 'Y' AND
PDF.travel_required = 'Y') OR
(nvl(p_search_criteria.travel_domestically, 'N') = 'N'))
AND ((p_search_criteria.travel_internationally = 'Y' AND
PDF.visit_internationally = 'Y') OR
(nvl(p_search_criteria.travel_internationally, 'N') = 'N')); */
SELECT asgn.assignment_id
, asgn.assignment_name
, asgn.assignment_number
, asgn.start_date
, asgn.end_date
, asgn.status_code
, proj.project_id
, proj.name project_name
, proj.segment1 project_number
, loc.country_code
, ter.territory_short_name
, loc.region
, loc.city
FROM pa_project_assignments asgn
, pa_org_hierarchy_denorm ohd
, pa_projects_all proj
, pa_locations loc
, fnd_territories_vl ter
, pa_project_statuses ps
, pa_advertised_open_req_v aor
WHERE asgn.assignment_id = p_search_criteria.assignment_id
AND asgn.min_resource_job_level <= p_search_criteria.min_job_level
AND asgn.max_resource_job_level >= p_search_criteria.min_job_level
AND asgn.project_role_id = nvl(p_search_criteria.role_id, asgn.project_role_id)
AND asgn.start_date >= p_search_criteria.start_date
AND asgn.start_date <= p_search_criteria.end_date
AND nvl(asgn.staffing_priority_code, -999) = nvl(p_search_criteria.staffing_priority_code, nvl(asgn.staffing_priority_code, -999))
AND nvl(asgn.staffing_owner_person_id, -999) = nvl(p_search_criteria.staffing_owner_person_id, nvl(asgn.staffing_owner_person_id, -999))
AND asgn.assignment_type='OPEN_ASSIGNMENT'
AND asgn.status_code = ps.project_status_code (+)
AND (ps.project_system_status_code= 'OPEN_ASGMT' or ps.project_system_status_code is NULL)
AND asgn.project_id = proj.project_id
AND proj.carrying_out_organization_id = OHD.child_organization_id
AND OHD.parent_organization_id = p_search_criteria.organization_id
AND OHD.org_hierarchy_version_id = p_search_criteria.org_hierarchy_version_id
AND OHD.pa_org_use_type = 'PROJECTS'
AND asgn.location_id = loc.location_id(+)
AND nvl(loc.country_code, -999) = nvl(p_search_criteria.territory_code, nvl(loc.country_code, -999))
AND loc.country_code = ter.territory_code(+)
AND asgn.assignment_id = aor.assignment_id;
SELECT asgn.assignment_id
, asgn.assignment_name
, asgn.assignment_number
, asgn.start_date
, asgn.end_date
, asgn.status_code
, proj.project_id
, proj.name project_name
, proj.segment1 project_number
, loc.country_code
, ter.territory_short_name
, loc.region
, loc.city
FROM pa_project_assignments asgn
, pa_org_hierarchy_denorm ohd
, pa_projects_all proj
, pa_locations loc
, fnd_territories_vl ter
, pa_project_statuses ps
, pa_advertised_open_req_v aor
WHERE asgn.min_resource_job_level <= p_search_criteria.min_job_level
AND asgn.max_resource_job_level >= p_search_criteria.min_job_level
AND asgn.project_role_id = p_search_criteria.role_id
AND asgn.start_date >= p_search_criteria.start_date
AND asgn.start_date <= p_search_criteria.end_date
AND asgn.staffing_priority_code = p_search_criteria.staffing_priority_code
AND nvl(asgn.staffing_owner_person_id, -999) = nvl(p_search_criteria.staffing_owner_person_id, nvl(asgn.staffing_owner_person_id, -999))
AND asgn.assignment_type='OPEN_ASSIGNMENT'
AND asgn.status_code = ps.project_status_code (+)
AND (ps.project_system_status_code= 'OPEN_ASGMT' or ps.project_system_status_code is NULL)
AND asgn.project_id = proj.project_id
AND proj.carrying_out_organization_id = OHD.child_organization_id
AND OHD.parent_organization_id = p_search_criteria.organization_id
AND OHD.org_hierarchy_version_id = p_search_criteria.org_hierarchy_version_id
AND OHD.pa_org_use_type = 'PROJECTS'
AND asgn.location_id = loc.location_id
AND loc.country_code = p_search_criteria.territory_code
AND loc.country_code = ter.territory_code
AND asgn.assignment_id = aor.assignment_id;
SELECT asgn.assignment_id
, asgn.assignment_name
, asgn.assignment_number
, asgn.start_date
, asgn.end_date
, asgn.status_code
, proj.project_id
, proj.name project_name
, proj.segment1 project_number
, loc.country_code
, ter.territory_short_name
, loc.region
, loc.city
FROM pa_project_assignments asgn
, pa_org_hierarchy_denorm ohd
, pa_projects_all proj
, pa_locations loc
, fnd_territories_vl ter
, pa_project_statuses ps
, pa_advertised_open_req_v aor
WHERE asgn.min_resource_job_level <= p_search_criteria.min_job_level
AND asgn.max_resource_job_level >= p_search_criteria.min_job_level
AND asgn.project_role_id = p_search_criteria.role_id
AND asgn.start_date >= p_search_criteria.start_date
AND asgn.start_date <= p_search_criteria.end_date
AND asgn.staffing_priority_code = p_search_criteria.staffing_priority_code
AND nvl(asgn.staffing_owner_person_id, -999) = nvl(p_search_criteria.staffing_owner_person_id, nvl(asgn.staffing_owner_person_id, -999))
AND asgn.assignment_type='OPEN_ASSIGNMENT'
AND asgn.status_code = ps.project_status_code (+)
AND (ps.project_system_status_code= 'OPEN_ASGMT' or ps.project_system_status_code is NULL)
AND asgn.project_id = proj.project_id
AND proj.carrying_out_organization_id = OHD.child_organization_id
AND OHD.parent_organization_id = p_search_criteria.organization_id
AND OHD.org_hierarchy_version_id = p_search_criteria.org_hierarchy_version_id
AND OHD.pa_org_use_type = 'PROJECTS'
AND asgn.location_id = loc.location_id(+)
AND loc.country_code = ter.territory_code(+)
AND asgn.assignment_id = aor.assignment_id;
SELECT asgn.assignment_id
, asgn.assignment_name
, asgn.assignment_number
, asgn.start_date
, asgn.end_date
, asgn.status_code
, proj.project_id
, proj.name project_name
, proj.segment1 project_number
, loc.country_code
, ter.territory_short_name
, loc.region
, loc.city
FROM pa_project_assignments asgn
, pa_org_hierarchy_denorm ohd
, pa_projects_all proj
, pa_locations loc
, fnd_territories_vl ter
, pa_project_statuses ps
, pa_advertised_open_req_v aor
WHERE asgn.min_resource_job_level <= p_search_criteria.min_job_level
AND asgn.max_resource_job_level >= p_search_criteria.min_job_level
AND asgn.project_role_id = p_search_criteria.role_id
AND asgn.start_date >= p_search_criteria.start_date
AND asgn.start_date <= p_search_criteria.end_date
AND asgn.assignment_type='OPEN_ASSIGNMENT'
AND asgn.status_code = ps.project_status_code (+)
AND (ps.project_system_status_code= 'OPEN_ASGMT' or ps.project_system_status_code is NULL)
AND nvl(asgn.staffing_owner_person_id, -999) = nvl(p_search_criteria.staffing_owner_person_id, nvl(asgn.staffing_owner_person_id, -999))
AND asgn.project_id = proj.project_id
AND proj.carrying_out_organization_id = OHD.child_organization_id
AND OHD.parent_organization_id = p_search_criteria.organization_id
AND OHD.org_hierarchy_version_id = p_search_criteria.org_hierarchy_version_id
AND OHD.pa_org_use_type = 'PROJECTS'
AND asgn.location_id = loc.location_id(+)
AND nvl(loc.country_code, -999) = nvl(p_search_criteria.territory_code, nvl(loc.country_code, -999))
AND loc.country_code = ter.territory_code(+)
AND asgn.assignment_id = aor.assignment_id;
SELECT asgn.assignment_id
, asgn.assignment_name
, asgn.assignment_number
, asgn.start_date
, asgn.end_date
, asgn.status_code
, proj.project_id
, proj.name project_name
, proj.segment1 project_number
, loc.country_code
, ter.territory_short_name
, loc.region
, loc.city
FROM pa_project_assignments asgn
, pa_org_hierarchy_denorm ohd
, pa_projects_all proj
, pa_locations loc
, fnd_territories_vl ter
, pa_project_statuses ps
, pa_advertised_open_req_v aor
WHERE asgn.min_resource_job_level <= p_search_criteria.min_job_level
AND asgn.max_resource_job_level >= p_search_criteria.min_job_level
AND asgn.start_date >= p_search_criteria.start_date
AND asgn.start_date <= p_search_criteria.end_date
AND nvl(asgn.staffing_priority_code, -999) = nvl(p_search_criteria.staffing_priority_code, nvl(asgn.staffing_priority_code, -999))
AND nvl(asgn.staffing_owner_person_id, -999) = nvl(p_search_criteria.staffing_owner_person_id, nvl(asgn.staffing_owner_person_id, -999))
AND asgn.assignment_type='OPEN_ASSIGNMENT'
AND asgn.status_code = ps.project_status_code (+)
AND (ps.project_system_status_code= 'OPEN_ASGMT' or ps.project_system_status_code is NULL)
AND asgn.project_id = proj.project_id
AND proj.carrying_out_organization_id = OHD.child_organization_id
AND OHD.parent_organization_id = p_search_criteria.organization_id
AND OHD.org_hierarchy_version_id = p_search_criteria.org_hierarchy_version_id
AND OHD.pa_org_use_type = 'PROJECTS'
AND asgn.location_id = loc.location_id(+)
AND nvl(loc.country_code, -999) = nvl(p_search_criteria.territory_code, nvl(loc.country_code, -999))
AND loc.country_code = ter.territory_code(+)
AND asgn.assignment_id = aor.assignment_id;
INSERT INTO PA_SEARCH_RESULTS_TEMP ( person_id
, resource_id
, resource_organization_id
, resource_name
, resource_type
, resource_job_level
, country
, country_code
, region
, city
, optional_competence_match
, optional_competence_count
, mandatory_competence_match
, mandatory_competence_count
, availability
, potential_availability
, resource_email
, resource_manager
)
VALUES ( l_person_id_tbl(k)
, l_resource_id_tbl(k)
, l_resource_organization_id_tbl(k)
, l_resource_name_tbl(k)
, l_resource_type_tbl(k)
, l_resource_job_level_tbl(k)
, l_country_tbl(k)
, l_country_code_tbl(k)
, l_region_tbl(k)
, l_city_tbl(k)
, 0
, l_optional_competence_count
, 0
, l_mandatory_competence_count
, null
, null
, l_email_tbl(k)
, l_manager_tbl(k)
);
INSERT INTO pa_competence_criteria_temp(competence_id,
competence_name,
rating_level,
mandatory_flag)
VALUES(l_competence_id_tbl(i),
l_competence_name_tbl(i),
l_rating_level_tbl(i),
l_mandatory_flag_tbl(i));
UPDATE pa_search_results_temp
SET (mandatory_competence_match,optional_competence_match)=
(SELECT /*+ ORDERED */nvl(sum(decode(pct.mandatory_flag,'Y',1,0)),0), nvl(sum(decode(pct.mandatory_flag,'N',1,0)),0)
FROM pa_competence_criteria_temp pct,
per_competence_elements pce,
per_rating_levels prl
WHERE (pct.competence_id <> -1 AND
pce.competence_id = pct.competence_id)
AND pce.person_id = l_person_id_tbl(i)
AND pce.proficiency_level_id =
prl.rating_level_id(+)
/*code added for bug 2932045*/
AND PA_SEARCH_GLOB.g_search_criteria.start_date between
NVL(pce.EFFECTIVE_DATE_FROM,PA_SEARCH_GLOB.g_search_criteria.start_date)
and nvl(pce.EFFECTIVE_DATE_TO,PA_SEARCH_GLOB.g_search_criteria.start_date)
/*code addition ends for bug 2932045*/
AND decode(prl.step_value, NULL,
decode(pct.rating_level, NULL, -999,
PA_SEARCH_GLOB.get_min_prof_level(pct.competence_id)),
prl.step_value) >=
nvl(pct.rating_level, nvl(prl.step_value , -999))
)
WHERE person_id = l_person_id_tbl(i);
UPDATE pa_search_results_temp
SET (mandatory_competence_match,optional_competence_match)=
(SELECT /*+ ORDERED */
(nvl(sum(decode(pct.mandatory_flag,'Y',1,0)),0) + nvl(mandatory_competence_match, 0)),
(nvl(sum(decode(pct.mandatory_flag,'N',1,0)),0) + nvl(optional_competence_match, 0))
FROM pa_competence_criteria_temp pct
WHERE pct.competence_id = -1
AND EXISTS ( SELECT 'Y'
FROM per_competence_elements pce,
per_competences pc,
per_rating_levels prl
WHERE pce.person_id = l_person_id_tbl(i)
AND pce.competence_id = pc.competence_id
/*code added for bug 2932045*/
AND PA_SEARCH_GLOB.g_search_criteria.start_date between
NVL(pce.EFFECTIVE_DATE_FROM,PA_SEARCH_GLOB.g_search_criteria.start_date)
and nvl(pce.EFFECTIVE_DATE_TO,PA_SEARCH_GLOB.g_search_criteria.start_date)
/*code addition end for bug 2932045*/
AND pc.name like
(replace(pct.competence_name, '...') || '%')
AND pce.proficiency_level_id =
prl.rating_level_id(+)
AND decode(prl.step_value, NULL,
decode(pct.rating_level, NULL, -999,
PA_SEARCH_GLOB.get_min_prof_level(pce.competence_id)),
prl.step_value) >=
nvl(pct.rating_level, nvl(prl.step_value , -999))
)
)
WHERE person_id = l_person_id_tbl(i);
SELECT count(*) INTO l_assignment_days
FROM pa_forecast_items
WHERE assignment_id = p_search_criteria.assignment_id
AND delete_flag = 'N'
AND error_flag IN ('Y','N')
AND item_date >= trunc(SYSDATE)
AND item_quantity > 0;
DELETE FROM PA_FI_ASSIG_TEMP;
INSERT INTO PA_FI_ASSIG_TEMP
(
SELECT asgmt.item_date ,
asgmt.item_quantity
FROM pa_forecast_items asgmt
WHERE asgmt.assignment_id = p_search_criteria.assignment_id
AND asgmt.delete_flag = 'N'
AND asgmt.error_flag IN ('Y','N')
AND asgmt.item_date >= trunc(SYSDATE)
AND asgmt.item_quantity > 0
);
l_resource_tab.delete;
stmt_select := 'SELECT /*+ INDEX (res, PA_FORECAST_ITEMS_N3) */ ' ||
' res.person_id, ' ||
' TRUNC(SUM(DECODE(SIGN( ' ||
' (nvl(res.capacity_quantity, 0) - nvl(res.confirmed_qty, 0) - ' ||
' nvl(res.provisional_qty,0)) / asgmt.item_quantity-1),1, 1, ' ||
' greatest((nvl(res.capacity_quantity, 0) - nvl(res.confirmed_qty, 0) - ' || /*Greatest function is added for bug 2782464*/
' nvl(res.provisional_qty,0)),0) / ' ||
' asgmt.item_quantity))/ :1 ' || ' * 100), ' ||
' TRUNC(SUM(DECODE(SIGN( ' ||
' (nvl(res.capacity_quantity, 0) - nvl(res.confirmed_qty, 0))/asgmt.item_quantity-1),1, 1,' ||
' greatest((nvl(res.capacity_quantity, 0) - ' ||
' nvl(res.confirmed_qty, 0)), 0)/ asgmt.item_quantity))/ :1 ' || '* 100) ';
' AND res.delete_flag = ''N'' ' ||
' AND res.item_date = asgmt.item_date ' ||
' AND res.item_date BETWEEN :2 AND :3'|| -- added for Bug 6911907
' AND res.resource_id IN (';
OPEN cur_avail FOR (stmt_select || stmt_from || stmt_where || str_resource_ids || stmt_groupby)
-- USING l_assignment_days, l_assignment_days; -- added below for Bug 6911907
SELECT /*+ ORDERED USE_NL(srt res) INDEX (res, PA_FORECAST_ITEMS_N3)*/srt.person_id, count(res.resource_id)
BULK COLLECT INTO l_person_id_tbl, l_days_in_period_tbl
FROM PA_SEARCH_RESULTS_TEMP srt,
PA_FORECAST_ITEMS res
WHERE srt.resource_id = res.resource_id
AND res.forecast_item_type = 'U'
AND res.delete_flag = 'N'
AND res.error_flag IN ('Y','N')
AND res.item_date BETWEEN p_search_criteria.start_date AND
p_search_criteria.end_date
AND srt.mandatory_competence_count =
srt.mandatory_competence_match /* Added for bug 4624826 */
AND res.capacity_quantity > 0
GROUP BY srt.person_id ;
SELECT
TRUNC(SUM(greatest((nvl(res.capacity_quantity, 0) - nvl(res.confirmed_qty, 0) -
nvl(res.provisional_qty,0)), 0) / res.capacity_quantity) /
l_days_in_period_tbl(i) * 100),
TRUNC(SUM(greatest((nvl(res.capacity_quantity, 0) -
nvl(res.confirmed_qty, 0)), 0) / res.capacity_quantity) /
l_days_in_period_tbl(i) * 100)
INTO l_pot_availability_tbl(i),
l_availability_tbl(i)
FROM PA_SEARCH_RESULTS_TEMP srt,
PA_FORECAST_ITEMS res
WHERE srt.person_id = l_person_id_tbl(i)
AND srt.resource_id = res.resource_id
AND res.forecast_item_type = 'U'
AND res.delete_flag = 'N'
AND res.capacity_quantity > 0
AND res.item_date BETWEEN p_search_criteria.start_date AND
p_search_criteria.end_date
AND srt.mandatory_competence_count =
srt.mandatory_competence_match
GROUP BY srt.person_id;
SELECT srt.person_id,
confirmed.percent,
potential.percent
BULK COLLECT INTO l_person_id_tbl,
l_availability_tbl,
l_pot_availability_tbl
FROM PA_SEARCH_RESULTS_TEMP srt,
PA_RES_AVAILABILITY confirmed,
PA_RES_AVAILABILITY potential
WHERE srt.resource_id = confirmed.resource_id
AND confirmed.record_type = 'C'
AND p_search_criteria.start_date BETWEEN confirmed.start_date
AND confirmed.end_date
AND srt.resource_id = potential.resource_id
AND potential.record_type = 'B'
AND p_search_criteria.start_date BETWEEN potential.start_date
AND potential.end_date
AND srt.mandatory_competence_count =
srt.mandatory_competence_match
GROUP BY srt.person_id;
UPDATE pa_search_results_temp
SET availability = l_availability_tbl(k),
potential_availability = l_pot_availability_tbl(k),
--resource_organization_name = pa_expenditures_utils.GetOrgTlName(resource_organization_id), 4778073
resource_organization_name = pa_resource_utils.get_organization_name(resource_organization_id),
candidate_score = PA_CANDIDATE_UTILS.Get_Candidate_Score(
resource_id,
l_person_id_tbl(k),
p_search_criteria.assignment_id,
p_search_criteria.project_id,
null,
optional_competence_match+mandatory_competence_match,
optional_competence_count+mandatory_competence_count,
l_availability_tbl(k),
resource_job_level,
p_search_criteria.min_job_level,
p_search_criteria.max_job_level,
p_search_criteria.competence_match_weighting,
p_search_criteria.availability_match_weighting,
p_search_criteria.job_level_match_weighting)
WHERE person_id = l_person_id_tbl(k)
AND ((nvl(p_search_criteria.provisional_availability, 'N') = 'N' AND
l_availability_tbl(k) >=
p_search_criteria.min_availability)
OR
(p_search_criteria.provisional_availability = 'Y' AND
l_pot_availability_tbl(k) >=
p_search_criteria.min_availability));
UPDATE pa_search_results_temp
SET availability = 0,
potential_availability = 0,
--resource_organization_name = pa_expenditures_utils.GetOrgTlName(resource_organization_id), 4778073
resource_organization_name = pa_resource_utils.get_organization_name(resource_organization_id),
candidate_score = PA_CANDIDATE_UTILS.Get_Candidate_Score(
resource_id,
person_id,
p_search_criteria.assignment_id,
p_search_criteria.project_id,
null,
optional_competence_match+mandatory_competence_match,
optional_competence_count+mandatory_competence_count,
0,
resource_job_level,
p_search_criteria.min_job_level,
p_search_criteria.max_job_level,
p_search_criteria.competence_match_weighting,
p_search_criteria.availability_match_weighting,
p_search_criteria.job_level_match_weighting)
WHERE mandatory_competence_count = mandatory_competence_match
AND availability IS NULL
AND resource_organization_name IS NULL
AND candidate_score IS NULL;
INSERT INTO PA_SEARCH_RESULTS_TEMP ( assignment_id
, assignment_name
, assignment_number
, assignment_start_date
, assignment_end_date
, project_id
, project_name
, project_number
, country
, country_code
, region
, city
, optional_competence_match
, optional_competence_count
, mandatory_competence_match
, mandatory_competence_count
)
VALUES ( l_assignment_id_tbl(k)
, l_assignment_name_tbl(k)
, l_assignment_number_tbl(k)
, l_assignment_start_date_tbl(k)
, l_assignment_end_date_tbl(k)
, l_project_id_tbl(k)
, l_project_name_tbl(k)
, l_project_number_tbl(k)
, l_country_tbl(k)
, l_country_code_tbl(k)
, l_region_tbl(k)
, l_city_tbl(k)
,0
,0
,0
,0
);
DELETE FROM pa_search_results_temp
WHERE show_req_in_search(l_assignment_id_tbl(i), l_status_code_tbl(i)) = 'N'
AND assignment_id = l_assignment_id_tbl(i);
UPDATE pa_search_results_temp
SET (mandatory_competence_count, optional_competence_count) =
(SELECT nvl(sum(decode(mandatory,'Y',1,0)),0), nvl(sum(decode(mandatory,'N',1,0)),0)
FROM per_competence_elements pce
WHERE pce.object_name = 'OPEN_ASSIGNMENT'
AND pce.object_id = l_assignment_id_tbl(i))
WHERE assignment_id = l_assignment_id_tbl(i);
UPDATE pa_search_results_temp
SET (mandatory_competence_match,optional_competence_match)=
(SELECT nvl(sum(decode(pce.mandatory,'Y',1,0)),0), nvl(sum(decode(pce.mandatory,'N',1,0)),0)
FROM per_competence_elements pce,
per_competence_elements pce2,
per_rating_levels prl,
per_rating_levels prl2
WHERE pce.object_id = l_assignment_id_tbl(i)
AND pce.object_name = 'OPEN_ASSIGNMENT'
AND pce.proficiency_level_id = prl.rating_level_id(+)
AND pce.competence_id = pce2.competence_id
AND pce2.person_id = p_search_criteria.resource_source_id
AND pce2.proficiency_level_id = prl2.rating_level_id(+)
AND decode(prl2.step_value, NULL, decode(prl.step_value, NULL, -999, PA_SEARCH_GLOB.get_min_prof_level(pce2.competence_id)), prl2.step_value) >= nvl(prl.step_value, nvl(prl2.step_value , -999)))
WHERE assignment_id = l_assignment_id_tbl(i);
UPDATE pa_search_results_temp
SET candidate_in_req_flag = decode(p_search_criteria.resource_source_id, NULL, NULL,PA_CANDIDATE_UTILS.Check_Resource_Is_Candidate(PA_RESOURCE_UTILS.Get_Resource_Id(p_search_criteria.resource_source_id), assignment_id))
WHERE ((mandatory_competence_count=mandatory_competence_match AND p_search_criteria.restrict_res_comp = FND_API.G_TRUE)
OR p_search_criteria.restrict_res_comp = FND_API.G_FALSE);
SELECT DISTINCT status_code BULK COLLECT INTO l_status_code_tbl
FROM pa_schedules
WHERE assignment_id = p_assignment_id;
SELECT asgn.assignment_id,
asgn.project_id,
asgn.min_resource_job_level,
asgn.max_resource_job_level,
asgn.start_date,
asgn.end_date,
asgn.search_min_availability,
asgn.search_country_code,
asgn.search_exp_org_struct_ver_id,
asgn.search_exp_start_org_id,
asgn.search_min_candidate_score,
asgn.COMPETENCE_MATCH_WEIGHTING,
asgn.AVAILABILITY_MATCH_WEIGHTING,
asgn.JOB_LEVEL_MATCH_WEIGHTING,
asgn.ENABLE_AUTO_CAND_NOM_FLAG,
proj.enable_automated_search
FROM pa_project_assignments asgn,
pa_projects_all proj,
pa_project_statuses ps
WHERE proj.project_id = p_project_id
AND proj.project_id = asgn.project_id
AND trunc(asgn.start_date) BETWEEN decode(p_req_start_date_days, NULL, asgn.start_date, trunc(SYSDATE)) AND decode(p_req_start_date_days, NULL, asgn.start_date, trunc(SYSDATE) + p_req_start_date_days)
AND asgn.assignment_type='OPEN_ASSIGNMENT'
AND asgn.status_code = ps.project_status_code (+)
AND (ps.project_system_status_code= 'OPEN_ASGMT' or ps.project_system_status_code is NULL)
AND p_auto_search_mode = 'SINGLE_PROJECT'
UNION ALL
SELECT asgn.assignment_id,
asgn.project_id,
asgn.min_resource_job_level,
asgn.max_resource_job_level,
asgn.start_date,
asgn.end_date,
asgn.search_min_availability,
asgn.search_country_code,
asgn.search_exp_org_struct_ver_id,
asgn.search_exp_start_org_id,
asgn.search_min_candidate_score,
asgn.COMPETENCE_MATCH_WEIGHTING,
asgn.AVAILABILITY_MATCH_WEIGHTING,
asgn.JOB_LEVEL_MATCH_WEIGHTING,
asgn.ENABLE_AUTO_CAND_NOM_FLAG,
proj.enable_automated_search
FROM pa_project_assignments asgn,
pa_projects_all proj,
pa_project_statuses ps
WHERE proj.segment1 BETWEEN nvl(p_project_number_from, proj.segment1) AND nvl(p_project_number_to, proj.segment1)
-- AND trunc(proj.start_date) BETWEEN decode(p_proj_start_date_days, NULL, proj.start_date, trunc(SYSDATE)) AND decode(p_proj_start_date_days, NULL, proj.start_date, trunc(SYSDATE) + p_proj_start_date_days)
-- Changed for Bug 5299668
AND trunc(nvl(proj.start_date, SYSDATE)) BETWEEN
decode(p_proj_start_date_days, NULL, nvl(proj.start_date, trunc(SYSDATE)), trunc(SYSDATE))
AND
decode(p_proj_start_date_days, NULL, nvl(proj.start_date, trunc(SYSDATE)), trunc(SYSDATE) + p_proj_start_date_days)
AND proj.project_status_code = nvl(p_project_status_code, proj.project_status_code)
AND proj.project_id = asgn.project_id
AND trunc(asgn.start_date) BETWEEN decode(p_req_start_date_days, NULL, asgn.start_date, trunc(SYSDATE)) AND decode(p_req_start_date_days, NULL, asgn.start_date, trunc(SYSDATE) + p_req_start_date_days)
AND asgn.assignment_type='OPEN_ASSIGNMENT'
AND asgn.status_code = ps.project_status_code (+)
AND (ps.project_system_status_code= 'OPEN_ASGMT' or ps.project_system_status_code is NULL)
AND p_auto_search_mode = 'MULTI_PROJECT'
;
l_competency_tbl.delete; -- Added for Bug 3098252
PA_SEARCH_GLOB.g_competence_criteria.delete; -- Added for Bug 3098252
SELECT step_value INTO PA_SEARCH_GLOB.g_competence_criteria(i).rating_level
FROM per_rating_levels
WHERE rating_level_id = l_competency_tbl(i).rating_level_id;
DELETE FROM pa_search_results_temp;
DELETE FROM pa_competence_criteria_temp;
SELECT resource_id BULK COLLECT INTO l_resource_id_tbl
FROM pa_search_results_temp
WHERE candidate_score >= l_min_candidate_score;
fnd_file.put_line(FND_FILE.LOG,'about to call API to delete qualified candidates');
PA_CANDIDATE_PUB.Delete_Candidates
(p_assignment_id => PA_SEARCH_GLOB.g_search_criteria.assignment_id,
p_status_code => '114',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
fnd_file.put_line(FND_FILE.LOG,'done with call to API to delete qualified candidates');
SELECT candidate_id BULK COLLECT INTO l_candidate_id_tbl
FROM pa_candidates cand,
pa_project_statuses ps
WHERE assignment_id = PA_SEARCH_GLOB.g_search_criteria.assignment_id
AND cand.status_code = ps.project_status_code
AND ps.status_type = 'CANDIDATE'
AND ps.project_system_status_code = l_cand_system_status_code
AND resource_id NOT IN
(SELECT resource_id
FROM pa_search_results_temp
WHERE candidate_score >= l_min_candidate_score)
;
fnd_file.put_line(FND_FILE.LOG,'number of previous system nominated candidates to be deleted = '||l_candidate_id_tbl.COUNT);
fnd_file.put_line(FND_FILE.LOG,'looping through candidates in order to delete');
fnd_file.put_line(FND_FILE.LOG,'calling withdraw_candidate API to delete candidate id '||l_candidate_id_tbl(i));
SELECT message_text INTO l_system_nom_candidate_text
FROM fnd_new_messages
WHERE message_name = 'PA_SYSTEM_NOMINATED_CANDIDATE'
and application_id = 275
and language_code = userenv('LANG');
fnd_file.put_line(FND_FILE.LOG,'about to update last_auto_search_date for assignment id = '||PA_SEARCH_GLOB.g_search_criteria.assignment_id);
UPDATE PA_PROJECT_ASSIGNMENTS
SET last_auto_search_date = SYSDATE
WHERE assignment_id = PA_SEARCH_GLOB.g_search_criteria.assignment_id;