[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT inp.person_id
, per.party_id
, to_number(inp.matching_job_freq) show_jobs_since
FROM irc_notification_preferences inp
, per_all_people_f per
WHERE inp.matching_jobs='Y'
and per.person_id=inp.person_id
and trunc(sysdate) between per.effective_start_date and per.effective_end_date
AND (mod (trunc(sysdate) - trunc(inp.last_update_date)
,to_number (inp.matching_job_freq)) = 0
OR p_ignore_seeker_matching_freq = 'Y'
);
SELECT isc.search_criteria_id
, isc.object_id
, p_seeker_details.party_id party_id
, isc.distance_to_location
, isc.location
, isc.employee
, isc.contractor
, isc.employment_category
, isc.keywords
, isc.travel_percentage
, isc.min_salary
, isc.salary_currency
, isc.salary_period
, isc.match_competence
, isc.match_qualification
, isc.job_title
, isc.department
, isc.work_at_home
, isc.attribute1
, isc.attribute2
, isc.attribute3
, isc.attribute4
, isc.attribute5
, isc.attribute6
, isc.attribute7
, isc.attribute8
, isc.attribute9
, isc.attribute10
, isc.attribute11
, isc.attribute12
, isc.attribute13
, isc.attribute14
, isc.attribute15
, isc.attribute16
, isc.attribute17
, isc.attribute18
, isc.attribute19
, isc.attribute20
, isc.attribute21
, isc.attribute22
, isc.attribute23
, isc.attribute24
, isc.attribute25
, isc.attribute26
, isc.attribute27
, isc.attribute28
, isc.attribute29
, isc.attribute30
, isc.isc_information1
, isc.isc_information2
, isc.isc_information3
, isc.isc_information4
, isc.isc_information5
, isc.isc_information6
, isc.isc_information7
, isc.isc_information8
, isc.isc_information9
, isc.isc_information10
, isc.isc_information11
, isc.isc_information12
, isc.isc_information13
, isc.isc_information14
, isc.isc_information15
, isc.isc_information16
, isc.isc_information17
, isc.isc_information18
, isc.isc_information19
, isc.isc_information20
, isc.isc_information21
, isc.isc_information22
, isc.isc_information23
, isc.isc_information24
, isc.isc_information25
, isc.isc_information26
, isc.isc_information27
, isc.isc_information28
, isc.isc_information29
, isc.isc_information30
, isc.geometry
, isc.location_id
, p_seeker_details.show_jobs_since
, per.current_employee_flag
FROM
irc_search_criteria isc
, per_all_people_f per
WHERE isc.object_type in ( 'PERSON' ,'WPREF')
AND isc.object_id = p_seeker_details.person_id
AND isc.use_for_matching = 'Y'
AND per.person_id=isc.object_id
AND trunc(sysdate) between per.effective_start_date and per.effective_end_date;
SELECT
isc.search_criteria_id
, isc.object_id vacancy_id
, isc.distance_to_location
, isc.location
, isc.employee
, isc.contractor
, isc.employment_category
, isc.keywords
, isc.travel_percentage
, isc.max_salary
, isc.salary_currency
, isc.salary_period
, isc.match_competence
, isc.match_qualification
, isc.min_qual_level
, isc.max_qual_level
, isc.department
, isc.professional_area
, isc.work_at_home
, isc.attribute1
, isc.attribute2
, isc.attribute3
, isc.attribute4
, isc.attribute5
, isc.attribute6
, isc.attribute7
, isc.attribute8
, isc.attribute9
, isc.attribute10
, isc.attribute11
, isc.attribute12
, isc.attribute13
, isc.attribute14
, isc.attribute15
, isc.attribute16
, isc.attribute17
, isc.attribute18
, isc.attribute19
, isc.attribute20
, isc.attribute21
, isc.attribute22
, isc.attribute23
, isc.attribute24
, isc.attribute25
, isc.attribute26
, isc.attribute27
, isc.attribute28
, isc.attribute29
, isc.attribute30
, isc.isc_information1
, isc.isc_information2
, isc.isc_information3
, isc.isc_information4
, isc.isc_information5
, isc.isc_information6
, isc.isc_information7
, isc.isc_information8
, isc.isc_information9
, isc.isc_information10
, isc.isc_information11
, isc.isc_information12
, isc.isc_information13
, isc.isc_information14
, isc.isc_information15
, isc.isc_information16
, isc.isc_information17
, isc.isc_information18
, isc.isc_information19
, isc.isc_information20
, isc.isc_information21
, isc.isc_information22
, isc.isc_information23
, isc.isc_information24
, isc.isc_information25
, isc.isc_information26
, isc.isc_information27
, isc.isc_information28
, isc.isc_information29
, isc.isc_information30
, loc.geometry
, vac.location_id
, loc.derived_locale
, vac.business_group_id
, vac.name
, vac.recruiter_id
, vac.primary_posting_id
FROM
irc_search_criteria isc
, per_all_vacancies vac
, hr_locations_all loc
WHERE isc.object_type = 'VACANCY'
AND isc.object_id = vac.vacancy_id
AND vac.location_id = loc.location_id (+)
AND vac.status='APPROVED'
-- we can only send to vacancies with recruiters, so
-- do not select any that do not have one with a login
AND vac.recruiter_id is not null
AND trunc(sysdate) between vac.date_from and nvl(vac.date_to,sysdate);
SELECT /*+ FULL (inp) INDEX(PER PER_PEOPLE_F_PK) */
per.person_id
, per.email_address
, per.first_name
, per.last_name
FROM irc_notification_preferences inp
, per_all_people_f per
WHERE inp.person_id = per.person_id
and inp.receive_info_mail ='Y'
and trunc(sysdate) between per.effective_start_date and per.effective_end_date;
select count(*) into l_return from hr_locations_all loc
where LOC.location_id = p_location_id
AND catsearch(loc.derived_locale, p_location_to_match, null) > 0;
SELECT brief_description
INTO l_brief_description_clob
FROM irc_posting_contents_tl
WHERE posting_content_id
= p_posting_details_tab(counter).posting_content_id
and language = userenv('LANG');
SELECT brief_description
INTO l_brief_description_clob
FROM irc_posting_contents_tl
WHERE posting_content_id
= p_posting_details_tab(counter).posting_content_id
and language = userenv('LANG');
SELECT 'Y' INTO l_int_site
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM per_all_vacancies vac ,
per_recruitment_activity_for praf ,
per_recruitment_activities pra ,
irc_all_recruiting_sites site
WHERE
vac.vacancy_id = praf.vacancy_id
AND praf.recruitment_activity_id = pra.recruitment_activity_id
AND trunc(sysdate) between pra.date_start and nvl(pra.date_end,sysdate)
AND pra.recruiting_site_id = site.recruiting_site_id
AND vac.vacancy_id = p_vacancy_id
AND site.internal = 'Y' );
SELECT 'Y' INTO l_ext_site
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM per_all_vacancies vac ,
per_recruitment_activity_for praf ,
per_recruitment_activities pra ,
irc_all_recruiting_sites site
WHERE
vac.vacancy_id = praf.vacancy_id
AND praf.recruitment_activity_id = pra.recruitment_activity_id
AND trunc(sysdate) between pra.date_start and nvl(pra.date_end,sysdate)
AND pra.recruiting_site_id = site.recruiting_site_id
AND vac.vacancy_id = p_vacancy_id
AND site.external = 'Y' );
SELECT responsibility_id
FROM fnd_responsibility
WHERE responsibility_key = resp_key;
l_sql:='SELECT DISTINCT
ipc_tl.posting_content_id
, ipc_tl.job_title
, ipc_tl.name
, icrit_vac.object_id
FROM
irc_search_criteria icrit_vac
, irc_posting_contents ipc
, irc_posting_contents_tl ipc_tl
, per_recruitment_activity_for praf
, per_recruitment_activities pra
, irc_all_recruiting_sites site
, per_all_vacancies vac
, hr_locations_all loc
WHERE
icrit_vac.object_type = ''VACANCY''
AND icrit_vac.object_id = vac.vacancy_id
AND vac.location_id = loc.location_id(+)
AND vac.vacancy_id = praf.vacancy_id
AND praf.recruitment_activity_id = pra.recruitment_activity_id
AND pra.posting_content_id = ipc.posting_content_id
AND ipc.posting_content_id = ipc_tl.posting_content_id
AND ipc_tl.language=userenv(''LANG'')
AND vac.status=''APPROVED''
AND sysdate between vac.date_from and nvl(vac.date_to,sysdate)
AND sysdate between pra.date_start and nvl(pra.date_end,sysdate)
AND site.recruiting_site_id=pra.recruiting_site_id
AND ((site.internal=''Y'' and :current_employee=''Y'')
OR(site.external=''Y'' and :current_employee=''N''))';
(select 1
from per_all_people_f ppf
,per_all_assignments_f paaf
where paaf.vacancy_id = icrit_vac.object_id
and paaf.person_id=ppf.person_id
and ppf.party_id = :party_id
and trunc(sysdate) between
ppf.effective_start_date and ppf.effective_end_date)';
l_sql:= l_sql||' AND (ipc.last_update_date >=
(sysdate - :show_jobs_since)
OR :p_ignore_job_age = ''Y''
)';
l_sql:= l_sql||' AND (( exists (select 1 from irc_location_criteria_values irc_lcv
where loc.derived_locale=irc_lcv.derived_locale
and irc_lcv.search_criteria_id=:search_criteria_id))
or (not exists (select 1 from irc_location_criteria_values irc_lcv
where irc_lcv.search_criteria_id=:search_criteria_id)))';
exists (SELECT 1
FROM per_qualifications qual
, per_qualification_types qty
WHERE qual.qualification_type_id
= qty.qualification_type_id
AND qual.person_id
= :person_id
AND qty.rank
BETWEEN NVL(icrit_vac.min_qual_level,0)
AND NVL(icrit_vac.max_qual_level,qty.rank)
)
)';
l_sql:= l_sql||' AND (( exists (select 1 from irc_prof_area_criteria_values irc_pacv
where icrit_vac.professional_area=irc_pacv.professional_area
and irc_pacv.search_criteria_id=:search_criteria_id))
or (not exists (select 1 from irc_prof_area_criteria_values irc_pacv
where irc_pacv.search_criteria_id=:search_criteria_id)))';
l_posting_details_tab.delete;
l_sql :=' SELECT DISTINCT
ppf.full_name
, ppf.person_id
FROM
irc_search_criteria icrit
, per_all_people_f ppf
, irc_posting_contents_tl ipc_tl
, hr_locations_all loc
, irc_notification_preferences inp
WHERE icrit.object_type =''WPREF''
AND icrit.object_id = ppf.person_id
AND ipc_tl.posting_content_id = :posting_content_id
AND ipc_tl.language=userenv(''LANG'')
AND inp.person_id=ppf.person_id
AND inp.allow_access=''Y''
AND inp.creation_date>=:date_registered
AND ppf.effective_start_date>=:date_registered
AND ( (ppf.current_employee_flag=''Y'' and :internal=''Y'')
OR (ppf.current_employee_flag is null and :external=''Y''))
AND trunc(sysdate)
BETWEEN ppf.effective_start_date
AND ppf.effective_end_date ';
(select paaf.assignment_id
from per_all_people_f ppf1
,per_all_assignments_f paaf
where paaf.vacancy_id = :vacancy_id
and ppf1.party_id = ppf.party_id
and ppf1.person_id = paaf.person_id
and trunc(sysdate) between
ppf1.effective_start_date and ppf1.effective_end_date) ';
l_sql := l_sql||' AND (icrit.geometry.sdo_point.x is null or exists (select 1 from hr_locations_all loc2 where
loc2.location_id=:location_id
and icrit.geometry.sdo_point.x is not null
and loc2.geometry is not null
and locator_within_distance
( loc2.geometry
, icrit.geometry
, ''distance=''
|| nvl(icrit.distance_to_location,0)
||'' units=MILE''
)= ''TRUE'' ) )';
l_sql := l_sql||' AND ((exists(select 1 from irc_location_criteria_values irc_lcv
where irc_lcv.search_criteria_id=icrit.search_criteria_id
and :derived_locale=irc_lcv.derived_locale))
or (not exists (select 1 from irc_location_criteria_values irc_lcv
where irc_lcv.search_criteria_id=icrit.search_criteria_id ))
or (:derived_locale is null))';
exists (SELECT 1
FROM per_qualifications qual
, per_qualification_types qty
WHERE qual.qualification_type_id
= qty.qualification_type_id
AND qual.party_id = ppf.party_id
AND qty.rank
BETWEEN NVL(:min_qual_level,0)
AND NVL(:max_qual_level,qty.rank)
)
OR (:min_qual_level is null and :max_qual_level is null))';
l_sql := l_sql||' AND ((exists (select 1 from irc_prof_area_criteria_values irc_pacv
where irc_pacv.professional_area = :professional_area
and irc_pacv.search_criteria_id=icrit.search_criteria_id))
OR (not exists (select 1 from irc_prof_area_criteria_values irc_pacv
where irc_pacv.search_criteria_id=icrit.search_criteria_id))
OR :professional_area IS NULL)';
l_seeker_details_tab.delete;