[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
, FND_PROFILE.value_specific('ICX_LANGUAGE',usr.user_id) lang_pref
FROM irc_notification_preferences inp
, per_all_people_f per
, fnd_user usr
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'
)
AND usr.employee_id = inp.person_id;
SELECT isc.search_criteria_id
, isc.object_id
, p_seeker_details.party_id party_id
, isc.distance_to_location
, isc.geocode_country
, isc.geocode_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
, loc.country
, 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 responsibility_id into l_resp_id
FROM fnd_responsibility
WHERE responsibility_key = l_resp_key;
SELECT substr(brief_description,1,500), length(brief_description)
INTO l_brief_description_v, l_length
FROM irc_posting_contents_tl
WHERE posting_content_id = p_posting_details_tab(counter).posting_content_id
AND language = userenv('LANG');
SELECT substr(brief_description,1,500), length(brief_description)
INTO l_brief_description_v, l_length
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;
select NLS_LANGUAGE
from fnd_languages_vl
where language_code = p_dft_lang;
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''
)';
OR exists( select ilp.location_id
from irc_location_preferences ilp,
hr_locations_all loc_pref
where ilp.object_type (+) = ''VACANCY''
and vac.vacancy_id = ilp.object_id(+)
and ilp.location_id = loc_pref.location_id
and catsearch(loc_pref.derived_locale, :location, null ) > 0
)
)';
OR exists ( select 1
from irc_location_preferences ilp,
hr_locations_all loc_pref
where ilp.object_type (+) = ''VACANCY''
and vac.vacancy_id = ilp.object_id(+)
and ilp.location_id = loc_pref.location_id
and loc_pref.geometry IS NOT NULL
)
)
AND :longitude IS NOT NULL
AND ( locator_within_distance
( loc.geometry
, mdsys.sdo_geometry(2001,8307,mdsys.sdo_point_type(:longitude,:latitude,null),null,null)
, ''distance=''
|| :distance_to_location
||'' units=MILE''
) = ''TRUE''
OR exists ( select 1
from irc_location_preferences ilp,
hr_locations_all loc_pref
where ilp.object_type (+) = ''VACANCY''
and vac.vacancy_id = ilp.object_id(+)
and ilp.location_id = loc_pref.location_id
and locator_within_distance (loc_pref.geometry
, mdsys.sdo_geometry(2001,8307,mdsys.sdo_point_type(:longitude,:latitude,null),null,null)
, ''distance=''
|| :distance_to_location
||'' units=MILE''
) = ''TRUE''
)
)
)
OR ( :distance_to_location IS NULL
AND :geocode_location IS NULL
AND (
( :geocode_country IS NOT NULL
AND ( loc.country = :geocode_country
OR exists ( select 1
from irc_location_preferences ilp,
hr_locations_all loc_pref
where ilp.object_type (+) = ''VACANCY''
and vac.vacancy_id = ilp.object_id(+)
and ilp.location_id = loc_pref.location_id
and loc_pref.country = :geocode_country
)
)
)
)
)
OR ( :distance_to_location IS NULL
AND :longitude IS NULL
AND :geocode_country IS NULL
)
) ';
OR exists( select ilp.location_id
from irc_location_preferences ilp,
hr_locations_all loc_pref
where ilp.object_type (+) = ''VACANCY''
and vac.vacancy_id = ilp.object_id(+)
and ilp.location_id = loc_pref.location_id
and ilp.location_id = :location_id
)
)
)
OR(:location_id is null)
)';
l_sql := l_sql || ' AND ( ( exists (select 1 from irc_location_criteria_values irc_lcv
where ( loc.derived_locale = irc_lcv.derived_locale
OR exists ( select ilp.location_id
from irc_location_preferences ilp,
hr_locations_all loc_pref
where ilp.object_type (+) = ''VACANCY''
and vac.vacancy_id = ilp.object_id(+)
and ilp.location_id = loc_pref.location_id
and loc_pref.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
) ';
AND ( exists ( select 1
from hr_locations_all loc
where loc.location_id = :location_id
and catsearch(loc.derived_locale, icrit.location, null) > 0
)
OR exists ( select 1
from irc_location_preferences ilp
, hr_locations_all loc_pref
where ilp.object_type = ''VACANCY''
and ilp.object_id = :vacancy_id
and ilp.location_id = loc_pref.location_id
and catsearch(loc_pref.derived_locale, icrit.location, null) > 0
)
)
)
) ';
l_sql := l_sql||' AND ( ( 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''
)
OR exists (select 1
from irc_location_preferences ilp
, hr_locations_all loc_pref
where ilp.object_type = ''VACANCY''
and ilp.object_id = :vacancy_id
and ilp.location_id = loc_pref.location_id
and icrit.geometry.sdo_point.x is not null
and loc_pref.geometry is not null
and locator_within_distance
( loc_pref.geometry
, icrit.geometry
, ''distance=''
|| nvl(icrit.distance_to_location,0)
||'' units=MILE''
)= ''TRUE''
)
)
OR( icrit.distance_to_location IS NULL
AND icrit.geometry IS NULL
AND ( icrit.geocode_country IS NULL
OR ( icrit.geocode_country IS NOT NULL
AND ( icrit.geocode_country = :country
OR exists ( select 1
from irc_location_preferences ilp
, hr_locations_all loc_pref
where ilp.object_type = ''VACANCY''
and ilp.object_id = :vacancy_id
and ilp.location_id = loc_pref.location_id
and loc_pref.country = icrit.geocode_country
)
)
)
)
)
)';
OR exists ( select 1
from irc_location_preferences ilp
, hr_locations_all loc_pref
where ilp.object_type = ''VACANCY''
and ilp.object_id = :vacancy_id
and ilp.location_id = loc_pref.location_id
and loc_pref.location_id = icrit.location_id
)
)
)
OR(icrit.location_id is null)
)';
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 (exists (select 1 from irc_location_criteria_values irc_lcv
,irc_location_preferences ilp
,hr_locations_all loc_pref
where irc_lcv.search_criteria_id = icrit.search_criteria_id
and ilp.object_type = ''VACANCY''
and ilp.object_id = :vacancy_id
and ilp.location_id = loc_pref.location_id
and loc_pref.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;
select function_id from fnd_form_functions
where function_name = p_function_name;