[Home] [Help]
219: , vac.primary_posting_id
220: FROM
221: irc_search_criteria isc
222: , per_all_vacancies vac
223: , hr_locations_all loc
224: WHERE isc.object_type = 'VACANCY'
225: AND isc.object_id = vac.vacancy_id
226: AND vac.location_id = loc.location_id (+)
227: AND vac.status='APPROVED'
259: is not */
260:
261: FUNCTION get_location_match
262: ( p_location_to_match IN varchar2,
263: p_location_id IN hr_locations_all.LOCATION_ID%TYPE)
264: RETURN number
265: IS
266: l_location_to_match VARCHAR2(240);
267: l_return NUMBER;
271: if p_location_to_match IS NULL
272: THEN
273: l_return := 1;
274: ELSE
275: select count(*) into l_return from hr_locations_all loc
276: where LOC.location_id = p_location_id
277: AND catsearch(loc.derived_locale, p_location_to_match, null) > 0;
278: END IF;
279: --
831: , per_recruitment_activity_for praf
832: , per_recruitment_activities pra
833: , irc_all_recruiting_sites site
834: , per_all_vacancies vac
835: , hr_locations_all loc
836: WHERE
837: icrit_vac.object_type = ''VACANCY''
838: AND icrit_vac.object_id = vac.vacancy_id
839: AND vac.location_id = loc.location_id(+)
1395: FROM
1396: irc_search_criteria icrit
1397: , per_all_people_f ppf
1398: , irc_posting_contents_tl ipc_tl
1399: , hr_locations_all loc
1400: , irc_notification_preferences inp
1401: WHERE icrit.object_type =''WPREF''
1402: AND icrit.object_id = ppf.person_id
1403: AND ipc_tl.posting_content_id = :posting_content_id
1473: -- Location Keyword Match
1474: l_sql := l_sql||' AND loc.location_id(+)=:location_id
1475: AND catsearch( loc.derived_locale(+), nvl(icrit.location,''*''), null) > 0 ';
1476: -- Location Distance Match
1477: l_sql := l_sql||' AND (icrit.geometry.sdo_point.x is null or exists (select 1 from hr_locations_all loc2 where
1478: loc2.location_id=:location_id
1479: and icrit.geometry.sdo_point.x is not null
1480: and loc2.geometry is not null
1481: and locator_within_distance