[Home] [Help]
17: SELECT inp.person_id
18: , per.party_id
19: , to_number(inp.matching_job_freq) show_jobs_since
20: FROM irc_notification_preferences inp
21: , per_all_people_f per
22: WHERE inp.matching_jobs='Y'
23: and per.person_id=inp.person_id
24: and trunc(sysdate) between per.effective_start_date and per.effective_end_date
25: AND (mod (trunc(sysdate) - trunc(inp.last_update_date)
115: , p_seeker_details.show_jobs_since
116: , per.current_employee_flag
117: FROM
118: irc_search_criteria isc
119: , per_all_people_f per
120: WHERE isc.object_type in ( 'PERSON' ,'WPREF')
121: AND isc.object_id = p_seeker_details.person_id
122: AND isc.use_for_matching = 'Y'
123: AND per.person_id=isc.object_id
240: , per.email_address
241: , per.first_name
242: , per.last_name
243: FROM irc_notification_preferences inp
244: , per_all_people_f per
245: WHERE inp.person_id = per.person_id
246: and inp.receive_info_mail ='Y'
247: and trunc(sysdate) between per.effective_start_date and per.effective_end_date;
248:
850: OR(site.external=''Y'' and :current_employee=''N''))';
851: -- And no applicant assignment has ever existed for the vacancy
852: l_sql:= l_sql||' AND NOT EXISTS
853: (select 1
854: from per_all_people_f ppf
855: ,per_all_assignments_f paaf
856: where paaf.vacancy_id = icrit_vac.object_id
857: and paaf.person_id=ppf.person_id
858: and ppf.party_id = :party_id
1393: ppf.full_name
1394: , ppf.person_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''
1413: AND ppf.effective_end_date ';
1414: -- And has no applicant assignment for the vacancy
1415: l_sql := l_sql||' AND NOT EXISTS
1416: (select paaf.assignment_id
1417: from per_all_people_f ppf1
1418: ,per_all_assignments_f paaf
1419: where paaf.vacancy_id = :vacancy_id
1420: and ppf1.party_id = ppf.party_id
1421: and ppf1.person_id = paaf.person_id