[Home] [Help]
18: , per.party_id
19: , to_number(inp.matching_job_freq) show_jobs_since
20: , FND_PROFILE.value_specific('ICX_LANGUAGE',usr.user_id) lang_pref
21: FROM irc_notification_preferences inp
22: , per_all_people_f per
23: , fnd_user usr
24: WHERE inp.matching_jobs = 'Y'
25: AND per.person_id = inp.person_id
26: AND trunc(sysdate) between per.effective_start_date and per.effective_end_date
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'
127: AND per.person_id = isc.object_id
243: , per.email_address
244: , per.first_name
245: , per.last_name
246: FROM irc_notification_preferences inp
247: , per_all_people_f per
248: WHERE inp.person_id = per.person_id
249: AND inp.receive_info_mail = 'Y'
250: AND trunc(sysdate) between per.effective_start_date and per.effective_end_date;
251:
924: )';
925: -- And no applicant assignment has ever existed for the vacancy
926: l_sql := l_sql || ' AND NOT EXISTS
927: (select 1
928: from per_all_people_f ppf
929: ,per_all_assignments_f paaf
930: where paaf.vacancy_id = icrit_vac.object_id
931: and paaf.person_id = ppf.person_id
932: and ppf.party_id = :party_id
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
1581: WHERE icrit.object_type = ''WPREF''
1593:
1594: -- And has no applicant assignment for the vacancy
1595: l_sql := l_sql || ' AND NOT EXISTS
1596: (select paaf.assignment_id
1597: from per_all_people_f ppf1
1598: ,per_all_assignments_f paaf
1599: where paaf.vacancy_id = :vacancy_id
1600: and ppf1.party_id = ppf.party_id
1601: and ppf1.person_id = paaf.person_id