[Home] [Help]
118: , isc.geometry
119: , isc.location_id
120: , p_seeker_details.show_jobs_since
121: , per.current_employee_flag
122: FROM irc_search_criteria isc
123: , per_all_people_f per
124: WHERE isc.object_type in ( 'PERSON' ,'WPREF')
125: AND isc.object_id = p_seeker_details.person_id
126: AND isc.use_for_matching = 'Y'
220: , vac.business_group_id
221: , vac.name
222: , vac.recruiter_id
223: , vac.primary_posting_id
224: FROM irc_search_criteria isc
225: , per_all_vacancies vac
226: , hr_locations_all loc
227: WHERE isc.object_type = 'VACANCY'
228: AND isc.object_id = vac.vacancy_id
886: fnd_message.set_name('PER','IRC_412056_NO_EMAIL_JOB_URL');
887: RAISE e_no_base_url;
888: END IF;
889:
890: l_matching_flex := fnd_profile.value('IRC_SEARCH_CRITERIA_SM');
891:
892: --
893: -- ***************************************************************************
894: -- Inner SQL to match the Job Seeker criteria to Vacancies
898: ipc_tl.posting_content_id
899: , ipc_tl.job_title
900: , ipc_tl.name
901: , icrit_vac.object_id
902: FROM irc_search_criteria icrit_vac
903: , irc_posting_contents ipc
904: , irc_posting_contents_tl ipc_tl
905: , per_recruitment_activity_for praf
906: , per_recruitment_activities pra
1555: l_proc VARCHAR2(30) default ' get_suitable_seekers_for_vac';
1556: BEGIN
1557: hr_utility.set_location('Entering'||l_proc, 10);
1558: --
1559: l_matching_flex := fnd_profile.value('IRC_SEARCH_CRITERIA_SM');
1560: --
1561: if (p_candidacy_age>0) then
1562: l_date_registered:=trunc(sysdate)-p_candidacy_age;
1563: else
1572:
1573: l_sql := ' SELECT DISTINCT
1574: ppf.full_name
1575: , ppf.person_id
1576: FROM irc_search_criteria icrit
1577: , per_all_people_f ppf
1578: , irc_posting_contents_tl ipc_tl
1579: --, hr_locations_all loc
1580: , irc_notification_preferences inp