[Home] [Help]
PACKAGE BODY: APPS.IRC_SEEKER_VAC_MATCHING_PKG
Source
1 PACKAGE BODY irc_seeker_vac_matching_pkg
2 /* $Header: irjsmtch.pkb 120.18.12020000.2 2013/01/02 13:16:46 nitnaras ship $ */
3 AS
4
5 -- ----------------------------------------------------------------------------
6 -- CURSORS
7 -- ----------------------------------------------------------------------------
8
9 -- ***************************************************************************
10 -- Outer Cursor to find Job Seekers requiring an email (one of three)
11 -- ***************************************************************************
12 CURSOR csr_seekers_for_email
13 -- If p_send_all_matching_jobs_now is not null, any relevant vacancies
14 -- will be sent immediately.
15 (p_ignore_seeker_matching_freq IN VARCHAR2 DEFAULT null)
16 IS
17 SELECT inp.person_id
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
27 AND (mod (trunc(sysdate) - trunc(inp.last_update_date)
28 ,to_number (inp.matching_job_freq)) = 0
29 OR p_ignore_seeker_matching_freq = 'Y'
30 )
31 AND usr.employee_id = inp.person_id;
32 -- ***************************************************************************
33 -- Middle Cursor to get the criteria entered by the Job Seeker (two of three)
34 -- ***************************************************************************
35 CURSOR csr_seeker_criteria_for_email
36 (p_seeker_details IN g_seeker_rec_type)
37 IS
38 SELECT isc.search_criteria_id
39 , isc.object_id
40 , p_seeker_details.party_id party_id
41 , isc.distance_to_location
42 , isc.geocode_country
43 , isc.geocode_location
44 , isc.location
45 , isc.employee
46 , isc.contractor
47 , isc.employment_category
48 , isc.keywords
49 , isc.travel_percentage
50 , isc.min_salary
51 , isc.salary_currency
52 , isc.salary_period
53 , isc.match_competence
54 , isc.match_qualification
55 , isc.job_title
56 , isc.department
57 , isc.work_at_home
58 , isc.attribute1
59 , isc.attribute2
60 , isc.attribute3
61 , isc.attribute4
62 , isc.attribute5
63 , isc.attribute6
64 , isc.attribute7
65 , isc.attribute8
66 , isc.attribute9
67 , isc.attribute10
68 , isc.attribute11
69 , isc.attribute12
70 , isc.attribute13
71 , isc.attribute14
72 , isc.attribute15
73 , isc.attribute16
74 , isc.attribute17
75 , isc.attribute18
76 , isc.attribute19
77 , isc.attribute20
78 , isc.attribute21
79 , isc.attribute22
80 , isc.attribute23
81 , isc.attribute24
82 , isc.attribute25
83 , isc.attribute26
84 , isc.attribute27
85 , isc.attribute28
86 , isc.attribute29
87 , isc.attribute30
88 , isc.isc_information1
89 , isc.isc_information2
90 , isc.isc_information3
91 , isc.isc_information4
92 , isc.isc_information5
93 , isc.isc_information6
94 , isc.isc_information7
95 , isc.isc_information8
96 , isc.isc_information9
97 , isc.isc_information10
98 , isc.isc_information11
99 , isc.isc_information12
100 , isc.isc_information13
101 , isc.isc_information14
102 , isc.isc_information15
103 , isc.isc_information16
104 , isc.isc_information17
105 , isc.isc_information18
106 , isc.isc_information19
107 , isc.isc_information20
108 , isc.isc_information21
109 , isc.isc_information22
110 , isc.isc_information23
111 , isc.isc_information24
112 , isc.isc_information25
113 , isc.isc_information26
114 , isc.isc_information27
115 , isc.isc_information28
116 , isc.isc_information29
117 , isc.isc_information30
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'
127 AND per.person_id = isc.object_id
128 AND trunc(sysdate) between per.effective_start_date and per.effective_end_date;
129 --
130 -- ***************************************************************************
131 -- Outer Cursor to find open vacancies
132 -- ***************************************************************************
133 --
134 CURSOR csr_vacancies_needing_seekers
135 (p_ignore_seeker_matching_freq IN VARCHAR2 DEFAULT null)
136 IS
137 SELECT isc.search_criteria_id
138 , isc.object_id vacancy_id
139 , isc.distance_to_location
140 , isc.location
141 , isc.employee
142 , isc.contractor
143 , isc.employment_category
144 , isc.keywords
145 , isc.travel_percentage
146 , isc.max_salary
147 , isc.salary_currency
148 , isc.salary_period
149 , isc.match_competence
150 , isc.match_qualification
151 , isc.min_qual_level
152 , isc.max_qual_level
153 , isc.department
154 , isc.professional_area
155 , isc.work_at_home
156 , isc.attribute1
157 , isc.attribute2
158 , isc.attribute3
159 , isc.attribute4
160 , isc.attribute5
161 , isc.attribute6
162 , isc.attribute7
163 , isc.attribute8
164 , isc.attribute9
165 , isc.attribute10
166 , isc.attribute11
167 , isc.attribute12
168 , isc.attribute13
169 , isc.attribute14
170 , isc.attribute15
171 , isc.attribute16
172 , isc.attribute17
173 , isc.attribute18
174 , isc.attribute19
175 , isc.attribute20
176 , isc.attribute21
177 , isc.attribute22
178 , isc.attribute23
179 , isc.attribute24
180 , isc.attribute25
181 , isc.attribute26
182 , isc.attribute27
183 , isc.attribute28
184 , isc.attribute29
185 , isc.attribute30
186 , isc.isc_information1
187 , isc.isc_information2
188 , isc.isc_information3
189 , isc.isc_information4
190 , isc.isc_information5
191 , isc.isc_information6
192 , isc.isc_information7
193 , isc.isc_information8
194 , isc.isc_information9
195 , isc.isc_information10
196 , isc.isc_information11
197 , isc.isc_information12
198 , isc.isc_information13
199 , isc.isc_information14
200 , isc.isc_information15
201 , isc.isc_information16
202 , isc.isc_information17
203 , isc.isc_information18
204 , isc.isc_information19
205 , isc.isc_information20
206 , isc.isc_information21
207 , isc.isc_information22
208 , isc.isc_information23
209 , isc.isc_information24
210 , isc.isc_information25
211 , isc.isc_information26
212 , isc.isc_information27
213 , isc.isc_information28
214 , isc.isc_information29
215 , isc.isc_information30
216 , loc.geometry
217 , loc.country
218 , vac.location_id
219 , loc.derived_locale
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
229 AND vac.location_id = loc.location_id (+)
230 AND vac.status = 'APPROVED'
231 -- we can only send to vacancies with recruiters, so
232 -- do not select any that do not have one with a login
233 AND vac.recruiter_id is not null
234 AND trunc(sysdate) between vac.date_from and nvl(vac.date_to,sysdate);
235 --
236 -- ***************************************************************************
237 -- Cursor to find job seekers requiring a general email
238 -- ***************************************************************************
239 CURSOR csr_seekers_for_notes
240 IS
241 SELECT /*+ FULL (inp) INDEX(PER PER_PEOPLE_F_PK) */
242 per.person_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
252 -- ----------------------------------------------------------------------------
253 -- FUNCTIONS
254 -- ----------------------------------------------------------------------------
255 --
256 -- -------------------------------------------------------------------------
257 -- |------------------------< get_location_match >------------------------|
258 -- -------------------------------------------------------------------------
259 /* internal function to return 1 if there is a location match or 0 if there
260 is not */
261
262 FUNCTION get_location_match
263 ( p_location_to_match IN varchar2,
264 p_location_id IN hr_locations_all.LOCATION_ID%TYPE)
265 RETURN number
266 IS
267 l_location_to_match VARCHAR2(240);
268 l_return NUMBER;
269 BEGIN
270 l_return := 0;
271 --
272 if p_location_to_match IS NULL
273 THEN
274 l_return := 1;
275 ELSE
276 select count(*) into l_return from hr_locations_all loc
277 where LOC.location_id = p_location_id
278 AND catsearch(loc.derived_locale, p_location_to_match, null) > 0;
279 END IF;
280 --
281 RETURN l_return;
282 EXCEPTION
283 WHEN OTHERS then
284 -- If there is an exception, no point in raising it - just send back 0
285 -- The calling code then won't select that job as suitable.
286 RETURN 0;
287 END get_location_match;
288 --
289 --
290 -- -------------------------------------------------------------------------
291 -- |-----------------------< convert_vacancy_amount >----------------------|
292 -- -------------------------------------------------------------------------
293 --
294 FUNCTION convert_vacancy_amount
295 (p_from_currency IN VARCHAR2
296 ,p_to_currency IN VARCHAR2
297 ,p_amount IN NUMBER
298 ,p_conversion_date IN DATE
299 ,p_business_group_id IN NUMBER
300 ,p_processing_type IN VARCHAR2)
301 RETURN NUMBER
302 IS
303 l_rate_type VARCHAR2(30);
304 l_amount NUMBER;
305 BEGIN
306 /* This function will definitely be slow
307 Possible ways of speeding it up are :
308 - Add a cache
309 - Hit the GL tables directly.
310 The issue with point2 is that that there is no guarentee any currency will be
311 held. By going via the HR api, the user should have entered which currencies
312 will be held for which business groups.
313 Definitely isn't ideal and needs performance testing.
314 */
315 --
316 l_rate_type := hr_currency_pkg.get_rate_type (
317 p_business_group_id => p_business_group_id
318 , p_conversion_date => p_conversion_date
319 , p_processing_type => p_processing_type);
320
321
322 l_amount := hr_currency_pkg.convert_amount
323 ( p_from_currency => p_from_currency
324 ,p_to_currency => p_to_currency
325 ,p_conversion_date => p_conversion_date
326 , p_amount => p_amount
327 , p_rate_type => l_rate_type
328 );
329 RETURN l_amount;
330
331 EXCEPTION
332 WHEN OTHERS then
333 -- If there is an exception, no point in raising it - just send back 0
334 -- The calling code then won't select that job as suitable.
335 RETURN 0;
336 END convert_vacancy_amount;
337 --
338 --
339 -- -------------------------------------------------------------------------
340 -- |----------------------< Remove_Html_Tags >-----------------------------|
341 -- -------------------------------------------------------------------------
342 --
343 FUNCTION remove_html_tags
344 ( p_html_string in varchar2
345 )
346 RETURN varchar2
347 IS
348 in_html BOOLEAN := FALSE;
349 tempchar VARCHAR2(1);
350 l_return_string VARCHAR2(500);
351 BEGIN
352 --
353 -- If the length of the brief description is more than 500 chars, then remove the tags.
354 --
355 for i in 1 .. length(p_html_string) loop
356 tempchar := substr( p_html_string, i, 1 );
357 if in_html then
358 if tempchar = '>' then
359 in_html := FALSE;
360 end if;
361 else
362 if tempchar = '<' then
363 in_html := TRUE;
364 end if;
365 end if;
366 if not in_html and tempchar <> '>' then
367 l_return_string := l_return_string || tempchar;
368 end if;
369 end loop;
370 --
371 RETURN l_return_string;
372 EXCEPTION
373 WHEN OTHERS THEN
374 RETURN l_return_string;
375 --
376 END remove_html_tags;
377 --
378 --
379 -- -------------------------------------------------------------------------
380 -- |----------------------< get_seeker_html_msg_body >---------------------|
381 -- -------------------------------------------------------------------------
382 --
383 FUNCTION get_seeker_html_msg_body
384 ( p_posting_details_tab g_posting_details_tab_type
385 , p_person_id number )
386 RETURN varchar2
387 IS
388 -- Size of the html text should be based on the size that WF can take
389 -- This is set in irc_notification_helper_pkg.set_v2_attributes
390 -- We define 8 packets of 1950 chars each minus 1 hence the size
391 -- of the varchar2 is set to 15599
392 --
393 l_job_html VARCHAR2(15599);
394 l_job_html_end VARCHAR2(2000);
395 l_return_job_html VARCHAR2(15599);
396 l_brief_description_v VARCHAR2(700) default '';
397 l_apps_fwk_agent VARCHAR2(2000);
398 l_job_content VARCHAR2(15599);
399 l_length NUMBER;
400 l_available_size NUMBER := 13500;
401 l_break VARCHAR2(10) := '<BR>';
402 l_function_name fnd_profile_option_values.profile_option_value%type;
403 l_resp_key fnd_responsibility.responsibility_key%type;
404 l_resp_id fnd_responsibility.responsibility_id%type;
405 --
406 BEGIN
407 --
408 IF (irc_utilities_pkg.is_internal_person(p_person_id,trunc(sysdate))='TRUE') THEN
409 l_resp_key := 'IRC_EMP_CANDIDATE';
410 SELECT responsibility_id into l_resp_id
411 FROM fnd_responsibility
412 WHERE responsibility_key = l_resp_key;
413
414 l_apps_fwk_agent := rtrim(nvl(fnd_profile.value_specific(name =>'IRC_FRAMEWORK_AGENT',responsibility_id => l_resp_id),fnd_profile.value_specific('APPS_FRAMEWORK_AGENT',0,0,0,0,0))
415 ||fnd_profile.value('ICX_PREFIX'),'/');
416 l_function_name := get_job_notification_function('Y');
417 ELSE
418 l_apps_fwk_agent := rtrim(nvl(fnd_profile.value('IRC_FRAMEWORK_AGENT'),fnd_profile.value('APPS_FRAMEWORK_AGENT'))
419 ||fnd_profile.value('ICX_PREFIX'),'/');
420 l_function_name := get_job_notification_function('N');
421 END IF;
422 --commented in bug fix 6004149.
423 --l_function_name := fnd_profile.value('IRC_JOB_NOTIFICATION_URL');
424
425 -- Loop through the suitable jobs and and list them.
426 l_job_html := fnd_message.get_string('PER','IRC_EMAIL_SEEKERS_INTRODUCTION') || l_break;
427 l_job_html_end := l_break || l_break || '<p align="center">' || get_conclusion_msg(
428 p_message_text => fnd_message.get_string('PER','IRC_EMAIL_SEEKERS_CONCLUSION')
429 ,p_person_id => p_person_id
430 ,p_action => 'UJ')||'</p>';
431 --
432 FOR counter IN 1 .. p_posting_details_tab.count LOOP
433 --
434 -- Need to select the CLOB now as it couldn't be used with a distinct
435 -- in the main cursor.
436 l_brief_description_v := null;
437 l_job_content := null;
438 l_length := 0;
439 SELECT substr(brief_description,1,500), length(brief_description)
440 INTO l_brief_description_v, l_length
441 FROM irc_posting_contents_tl
442 WHERE posting_content_id = p_posting_details_tab(counter).posting_content_id
443 AND language = userenv('LANG');
444 --
445 IF (l_length > 0) THEN
446 IF (l_length > 500) then
447 l_brief_description_v := remove_html_tags (l_brief_description_v) || ' ...';
448 END IF;
449 --
450 ELSE
451 l_brief_description_v := '';
452 END IF;
453 --
454 l_job_content := l_break || '<a HREF="'
455 || l_apps_fwk_agent
456 || '/OA_HTML/OA.jsp?OAFunc='
457 || l_function_name
458 || '&p_svid='||to_char(p_posting_details_tab(counter).object_id)
459 || '&p_spid='||to_char(p_posting_details_tab(counter).posting_content_id)
460 || '">'
461 || replace(p_posting_details_tab(counter).name,'<',fnd_global.local_chr(38)||'lt;')
462 || '</a> ' || l_break
463 || fnd_global.local_chr(38)||'nbsp; '
464 || fnd_global.local_chr(38)||'nbsp;'
465 || fnd_global.local_chr(38)||'nbsp;'
466 || fnd_global.local_chr(38)||'nbsp;'
467 || l_brief_description_v || l_break ;
468 --
469 IF (l_available_size > 0 ) THEN
470 l_available_size := l_available_size - length(l_job_html);
471 --
472 IF ( l_available_size >= length(l_job_content) ) THEN
473 l_job_html := l_job_html || l_job_content ;
474 ELSE
475 l_available_size := 0;
476 END IF;
477 --
478 END IF;
479 --
480 IF (l_available_size = 0) THEN
481 l_job_html := l_job_html || l_break || fnd_message.get_string('PER','IRC_412602_MOREJOBS_EXIST_TXT') || l_break;
482 EXIT;
483 END IF;
484 --
485 END LOOP;
486 --
487 l_return_job_html := l_job_html || l_job_html_end;
488 --
489 RETURN l_return_job_html;
490 EXCEPTION
491 WHEN OTHERS THEN
492 RETURN l_return_job_html;
493 END get_seeker_html_msg_body;
494 --
495 --
496 -- -------------------------------------------------------------------------
497 -- |----------------------< get_seeker_text_msg_body >---------------------|
498 -- -------------------------------------------------------------------------
499 --
500 FUNCTION get_seeker_text_msg_body
501 ( p_posting_details_tab g_posting_details_tab_type
502 , p_person_id number)
503 RETURN varchar2
504 IS
505 -- Size of the html text should be based on the size that WF can take
506 -- This is set in irc_notification_helper_pkg.set_v2_attributes
507 -- We define 8 packets of 1950 chars each minus 1 hence the size
508 -- of the varchar2 is set to 15599
509 --
510 l_job_text VARCHAR2(15599);
511 l_job_text_end VARCHAR2(2000);
512 l_return_job_text VARCHAR2(15599);
513 l_brief_description_v VARCHAR2(700) default '';
514 l_apps_fwk_agent VARCHAR2(2000);
515 l_job_content VARCHAR2(15599);
516 l_length NUMBER;
517 l_available_size NUMBER := 13500;
518 l_new_line VARCHAR2(10) := '\n';
519 l_function_name fnd_profile_option_values.profile_option_value%type;
520 --
521 BEGIN
522 --
523 IF (irc_utilities_pkg.is_internal_person(p_person_id,trunc(sysdate))='TRUE') THEN
524 l_apps_fwk_agent := rtrim(fnd_profile.value_specific('APPS_FRAMEWORK_AGENT',0,0,0,0,0)
525 ||fnd_profile.value('ICX_PREFIX'),'/');
526 l_function_name := get_job_notification_function('Y');
527 ELSE
528 l_apps_fwk_agent := rtrim(nvl(fnd_profile.value('IRC_FRAMEWORK_AGENT'),fnd_profile.value('APPS_FRAMEWORK_AGENT'))
529 ||fnd_profile.value('ICX_PREFIX'),'/');
530 l_function_name := get_job_notification_function('N');
531 END IF;
532 --commented in bug fix 6004149.
533 --l_function_name := fnd_profile.value('IRC_JOB_NOTIFICATION_URL');
534 -- Loop through the suitable jobs and and list them.
535 l_job_text := fnd_message.get_string('PER','IRC_EMAIL_SEEKERS_INTRO_TEXT') || l_new_line;
536 l_job_text_end := l_new_line ||get_conclusion_msg(
537 p_message_text =>fnd_message.get_string('PER','IRC_412619_JOB_CONCL_TEXT')
538 ,p_person_id =>p_person_id
539 ,p_action =>'UJ');
540 --
541 FOR counter IN 1 .. p_posting_details_tab.count LOOP
542 --
543 l_brief_description_v := null;
544 l_job_content := null;
545 l_length := 0;
546 SELECT substr(brief_description,1,500), length(brief_description)
547 INTO l_brief_description_v, l_length
548 FROM irc_posting_contents_tl
549 WHERE posting_content_id = p_posting_details_tab(counter).posting_content_id
550 AND language = userenv('LANG');
551 --
552 IF (l_length > 0) THEN
553 l_brief_description_v := remove_html_tags (l_brief_description_v) || ' ...';
554 l_brief_description_v := replace(l_brief_description_v,fnd_global.local_chr(38)||'nbsp;',' ');
555 ELSE
556 l_brief_description_v := '';
557 END IF;
558 --
559 l_job_content := l_new_line ||p_posting_details_tab(counter).name || l_new_line
560 || l_apps_fwk_agent
561 || '/OA_HTML/OA.jsp?OAFunc='
562 || l_function_name
563 || '&p_svid='||to_char(p_posting_details_tab(counter).object_id)
564 || '&p_spid='||to_char(p_posting_details_tab(counter).posting_content_id)
565 || l_new_line || l_brief_description_v || l_new_line;
566 --
567 IF (l_available_size > 0 ) THEN
568 l_available_size := l_available_size - length(l_job_text);
569 --
570 IF ( l_available_size >= length(l_job_content) ) THEN
571 l_job_text := l_job_text || l_job_content ;
572 ELSE
573 l_available_size := 0;
574 END IF;
575 --
576 END IF;
577 --
578 IF (l_available_size = 0) THEN
579 l_job_text := l_job_text || l_new_line || fnd_message.get_string('PER','IRC_412602_MOREJOBS_EXIST_TXT') || l_new_line;
580 EXIT;
581 END IF;
582 --
583 END LOOP;
584 --
585 l_return_job_text := l_job_text||l_job_text_end;
586 --
587 RETURN l_return_job_text;
588 EXCEPTION
589 WHEN OTHERS THEN
590 RETURN l_return_job_text;
591 END get_seeker_text_msg_body;
592 --
593 --
594 -- -------------------------------------------------------------------------
595 -- |--------------------< get_recruiter_html_msg_body >--------------------|
596 -- -------------------------------------------------------------------------
597 --
598 FUNCTION get_recruiter_html_msg_body
599 ( p_seeker_details_tab g_seeker_details_tab_type )
600 RETURN varchar2
601 IS
602 l_amount BINARY_INTEGER default 240;
603 l_seeker_html VARCHAR2(30000);
604 l_new_html varchar2(30000);
605 l_url varchar2(4000);
606 l_max_length number;
607 BEGIN
608 l_url:=fnd_profile.value('APPS_FRAMEWORK_AGENT');
609 if substr(l_url,-1,1)<>'/' then
610 l_url:=l_url||'/';
611 end if;
612 if fnd_profile.value('ICX_PREFIX') is not null then
613 l_url:=l_url||fnd_profile.value('ICX_PREFIX')||'/OA_HTML/';
614 else
615 l_url:=l_url||'OA_HTML/';
616 end if;
617 l_url:=l_url||'OA.jsp?OAFunc='||fnd_profile.value('IRC_SUITABLE_SEEKERS_URL')
618 ||fnd_global.local_chr(38)||'addBreadCrumb=Y'||fnd_global.local_chr(38)||'retainAM=Y'||fnd_global.local_chr(38)||'p_sprty=';
619 -- Loop through the suitable jobs and and list them.
620 l_seeker_html := fnd_message.get_string('PER','IRC_EMAIL_RECRUITER_INTRO')
621 || '<BR>';
622 --
623 l_max_length:=30000-(length(l_seeker_html)+4+length(fnd_message.get_string('PER','IRC_EMAIL_RECRUITER_CONCLUSION')));
624 FOR counter IN 1 .. p_seeker_details_tab.count LOOP
625 l_new_html:= '<BR><a HREF="'||l_url||p_seeker_details_tab(counter).person_id||'">'
626 || replace(ltrim(p_seeker_details_tab(counter).full_name,chr(0)),'<',fnd_global.local_chr(38)||'lt;')
627 || '</a>'
628 || '<BR>';
629 if(length(l_seeker_html)+length(l_new_html)<=l_max_length) then
630 l_seeker_html := l_seeker_html||l_new_html;
631 else
632 log_message('too many matching seekers');
633 end if;
634 END LOOP;
635 --
636 l_seeker_html := l_seeker_html || '<BR>' ||
637 fnd_message.get_string('PER','IRC_EMAIL_RECRUITER_CONCLUSION');
638 RETURN l_seeker_html;
639 END get_recruiter_html_msg_body;
640 --
641 --
642 -- -------------------------------------------------------------------------
643 -- |--------------------< get_recruiter_text_msg_body >--------------------|
644 -- -------------------------------------------------------------------------
645 --
646 FUNCTION get_recruiter_text_msg_body
647 ( p_seeker_details_tab g_seeker_details_tab_type )
648 RETURN varchar2
649 IS
650 l_amount BINARY_INTEGER default 240;
651 l_seeker_text VARCHAR2(30000);
652 l_new_text VARCHAR2(30000);
653 l_url varchar2(4000);
654 l_max_length number;
655 BEGIN
656 l_url:=fnd_profile.value('APPS_FRAMEWORK_AGENT');
657 if substr(l_url,-1,1)<>'/' then
658 l_url:=l_url||'/';
659 end if;
660 if fnd_profile.value('ICX_PREFIX') is not null then
661 l_url:=l_url||fnd_profile.value('ICX_PREFIX')||'/OA_HTML/';
662 else
663 l_url:=l_url||'OA_HTML/';
664 end if;
665 l_url:=l_url||'OA.jsp?OAFunc='||fnd_profile.value('IRC_SUITABLE_SEEKERS_URL')
666 ||fnd_global.local_chr(38)||'addBreadCrumb=Y'||fnd_global.local_chr(38)||'retainAM=Y'||fnd_global.local_chr(38)||'p_sprty=';
667 -- Loop through the suitable jobs and and list them.
668 l_seeker_text := fnd_message.get_string('PER','IRC_EMAIL_RECRUITER_INTRO')
669 || '\n';
670 --
671 l_max_length:=30000-(length(l_seeker_text)+4+length(fnd_message.get_string('PER','IRC_EMAIL_RECRUITER_CONCLUSION')));
672 FOR counter IN 1 .. p_seeker_details_tab.count LOOP
673 l_new_text := '\n'||ltrim(p_seeker_details_tab(counter).full_name,chr(0))
674 || '\n'||l_url||p_seeker_details_tab(counter).person_id
675 || '\n';
676 if(length(l_seeker_text)+length(l_new_text)<=l_max_length) then
677 l_seeker_text := l_seeker_text||l_new_text;
678 else
679 log_message('too many matching seekers');
680 end if;
681 END LOOP;
682 --
683 l_seeker_text := l_seeker_text || '\n' ||
684 fnd_message.get_string('PER','IRC_EMAIL_RECRUITER_CONCLUSION');
685 RETURN l_seeker_text;
686 END get_recruiter_text_msg_body;
687 --
688 --
689 --
690 -- -------------------------------------------------------------------------
691 -- |--------------------< get_int_rec_site >-------------------------------|
692 -- -------------------------------------------------------------------------
693 --
694 FUNCTION get_int_rec_site
695 ( p_vacancy_id per_all_vacancies.vacancy_id%type )
696 RETURN varchar2
697 IS
698 l_int_site irc_all_recruiting_sites.internal%type;
699 BEGIN
700
701 SELECT 'Y' INTO l_int_site
702 FROM DUAL
703 WHERE EXISTS ( SELECT 1
704 FROM per_all_vacancies vac ,
705 per_recruitment_activity_for praf ,
706 per_recruitment_activities pra ,
707 irc_all_recruiting_sites site
708 WHERE
709 vac.vacancy_id = praf.vacancy_id
710 AND praf.recruitment_activity_id = pra.recruitment_activity_id
711 AND trunc(sysdate) between pra.date_start and nvl(pra.date_end,sysdate)
712 AND pra.recruiting_site_id = site.recruiting_site_id
713 AND vac.vacancy_id = p_vacancy_id
714 AND site.internal = 'Y' );
715
716 RETURN l_int_site;
717
718 EXCEPTION
719 WHEN OTHERS THEN
720 l_int_site := 'N';
721 RETURN l_int_site;
722 END get_int_rec_site;
723 --
724 --
725 -- -------------------------------------------------------------------------
726 -- |--------------------< get_ext_rec_site >-------------------------------|
727 -- -------------------------------------------------------------------------
728 --
729 FUNCTION get_ext_rec_site
730 ( p_vacancy_id per_all_vacancies.vacancy_id%type )
731 RETURN varchar2
732 IS
733 l_ext_site irc_all_recruiting_sites.external%type;
734 BEGIN
735
736 SELECT 'Y' INTO l_ext_site
737 FROM DUAL
738 WHERE EXISTS ( SELECT 1
739 FROM per_all_vacancies vac ,
740 per_recruitment_activity_for praf ,
741 per_recruitment_activities pra ,
742 irc_all_recruiting_sites site
743 WHERE
744 vac.vacancy_id = praf.vacancy_id
745 AND praf.recruitment_activity_id = pra.recruitment_activity_id
746 AND trunc(sysdate) between pra.date_start and nvl(pra.date_end,sysdate)
747 AND pra.recruiting_site_id = site.recruiting_site_id
748 AND vac.vacancy_id = p_vacancy_id
749 AND site.external = 'Y' );
750
751 RETURN l_ext_site;
752
753 EXCEPTION
754 WHEN OTHERS THEN
755 l_ext_site := 'N';
756 RETURN l_ext_site;
757 END get_ext_rec_site;
758 --
759 --
760 -- -------------------------------------------------------------------------
761 -- |--------------------< get_job_notification_function >------------------|
762 -- -------------------------------------------------------------------------
763 --
764 --Bug 6004149. To generate different urls for internal and external candidates
765 --the profile value at corresponding resp levels are taken.Please note that
766 --even if customer uses custom resps for internal and external candidates,
767 --whatever value is set for IRC:Job Notification Url at the below resps,
768 --that value will be used to generate the url for vacancy details.
769
770 FUNCTION get_job_notification_function
771 ( p_is_internal IN varchar2)
772 RETURN varchar2
773 IS
774 CURSOR csr_get_resp_id (resp_key varchar2)
775 IS
776 SELECT responsibility_id
777 FROM fnd_responsibility
778 WHERE responsibility_key = resp_key;
779
780 l_resp_key fnd_responsibility.responsibility_key%type;
781 l_resp_id fnd_responsibility.responsibility_id%type;
782 l_function_name fnd_profile_option_values.profile_option_value%type;
783 BEGIN
784
785 if(lower(p_is_internal) = 'y')
786 then
787 l_resp_key := 'IRC_EMP_CANDIDATE';
788 else
789 l_resp_key := 'IRC_EXT_CANDIDATE';
790 end if;
791
792 open csr_get_resp_id(l_resp_key);
793 fetch csr_get_resp_id into l_resp_id ;
794 close csr_get_resp_id;
795
796 l_function_name := fnd_profile.value_specific
797 (name => 'IRC_JOB_NOTIFICATION_URL'
798 ,responsibility_id => l_resp_id);
799 return l_function_name;
800
801 EXCEPTION
802 WHEN NO_DATA_FOUND
803 THEN
804 l_function_name := fnd_profile.value('IRC_JOB_NOTIFICATION_URL');
805 return l_function_name;
806 END get_job_notification_function;
807 --
808 -- ----------------------------------------------------------------------------
809 -- PROCEDURES
810 -- ----------------------------------------------------------------------------
811 --
812 -- -------------------------------------------------------------------------
813 -- |----------------------------< log_message >----------------------------|
814 -- -------------------------------------------------------------------------
815 --
816 PROCEDURE log_message
817 ( p_message IN VARCHAR2
818 , p_type IN VARCHAR2 DEFAULT 'B'
819 )
820 IS
821 BEGIN
822 fnd_file.put_line(which => fnd_file.log,
823 buff => p_message);
824 if fnd_global.conc_request_id <> -1 then
825 -- log is additionally written out via FND_FILE,
826 -- for visibility from view SRS window.
827 fnd_file.put_line(which => fnd_file.output,
828 buff => 'REQ'||p_message);
829 /*hr_utility.trace_on('F','REQID');
830 hr_utility.trace('FRED');
831 hr_utility.trace_off;
832 */
833 end if;
834 END log_message;
835
836 --
837 -- -------------------------------------------------------------------------
838 -- |------------------< email_suitable_vacs_to_seekers >-------------------|
839 -- -------------------------------------------------------------------------
840 --
841 PROCEDURE email_suitable_vacs_to_seekers
842 ( errbuf OUT NOCOPY VARCHAR2
843 , retcode OUT NOCOPY NUMBER
844 , p_ignore_seeker_matching_freq IN VARCHAR2 DEFAULT 'N'
845 , p_ignore_job_age IN VARCHAR2 DEFAULT 'N'
846 )
847 IS
848 l_sql VARCHAR2(30000);
849 l_cursor NUMBER;
850 l_row NUMBER;
851 l_matching_flex VARCHAR2(240);
852 l_last_value VARCHAR2(240);
853 l_matching_values VARCHAR2(240);
854 l_attribute VARCHAR2(14);
855 seeker_criteria_match_vac_rec g_posting_details_rec_type;
856 matching_values_tbl dbms_sql.varchar2_table;
857 l_posting_details_tab g_posting_details_tab_type;
858 l_message_subject VARCHAR2(240);
859 l_base_url VARCHAR2(250);
860 --
861 l_counter NUMBER DEFAULT 0;
862 l_amount NUMBER;
863 --
864 l_id NUMBER;
865 e_no_base_url EXCEPTION;
866 l_dft_lang varchar2(100);
867 l_current_lang varchar2(100);
868 l_seeker_lang_pref varchar2(100);
869 l_proc VARCHAR2(35) default '.email_suitable_vacs_to_seekers';
870
871 /*Cursor to get NLS Language for the existing session language*/
872 cursor get_nls_lang (p_dft_lang varchar2) is
873 select NLS_LANGUAGE
874 from fnd_languages_vl
875 where language_code = p_dft_lang;
876
877 BEGIN
878 hr_utility.set_location('Entering'||l_proc, 10);
879 --
880 -- If the base URL isn't set up, it is futile performing a job search
881 -- as the URL for the seekers to click on won't work.
882 --
883 l_base_url := fnd_profile.value('IRC_JOB_NOTIFICATION_URL');
884 IF l_base_url IS NULL THEN
885 hr_utility.set_location('base_url is null', 20);
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
895 -- ***************************************************************************
896 --
897 l_sql:='SELECT DISTINCT
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
907 , irc_all_recruiting_sites site
908 , per_all_vacancies vac
909 , hr_locations_all loc
910 WHERE icrit_vac.object_type = ''VACANCY''
911 AND icrit_vac.object_id = vac.vacancy_id
912 AND vac.location_id = loc.location_id(+)
913 AND vac.vacancy_id = praf.vacancy_id
914 AND praf.recruitment_activity_id = pra.recruitment_activity_id
915 AND pra.posting_content_id = ipc.posting_content_id
916 AND ipc.posting_content_id = ipc_tl.posting_content_id
917 AND ipc_tl.language = userenv(''LANG'')
918 AND vac.status = ''APPROVED''
919 AND sysdate between vac.date_from and nvl(vac.date_to,sysdate)
920 AND sysdate between pra.date_start and nvl(pra.date_end,sysdate)
921 AND site.recruiting_site_id=pra.recruiting_site_id
922 AND ( (site.internal = ''Y'' and :current_employee=''Y'')
923 OR(site.external = ''Y'' and :current_employee=''N'')
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
933 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
934 )';
935
936 -- Employee and Contractor Match
937 l_sql := l_sql || ' AND ( icrit_vac.employee = :employee
938 OR icrit_vac.contractor = :contractor
939 )';
940 --
941 -- travel percentage : Assume no Seeker value means they can travel and
942 -- no vac value means no travel is involved
943 l_sql := l_sql || ' AND NVL(:travel_percentage,100) >= NVL(icrit_vac.travel_percentage,0)';
944 --
945 -- Min/Max Salary
946 l_sql := l_sql || ' AND ( ( :salary_currency = icrit_vac.salary_currency
947 AND :min_salary <= icrit_vac.max_salary
948 )
949 OR (:salary_currency is null)
950 OR (:min_salary is null)
951 OR (icrit_vac.salary_currency is null)
952 OR (icrit_vac.max_salary is null)
953 OR ( :salary_currency <> icrit_vac.salary_currency
954 AND icrit_vac.max_salary >=
955 irc_seeker_vac_matching_pkg.convert_vacancy_amount
956 (:salary_currency
957 ,icrit_vac.salary_currency
958 ,:min_salary
959 ,sysdate
960 ,vac.business_group_id
961 ,''P''
962 )
963 )
964 )';
965 -- Job Age
966 l_sql := l_sql || ' AND ( ipc.last_update_date >= (sysdate - :show_jobs_since)
967 OR :p_ignore_job_age = ''Y''
968 )';
969 -- Job Title Match
970 -- NOTE: name is a placeholder for a multi column index so it is used
971 -- to cover job_title description etc.
972 l_sql := l_sql || ' AND ( :job_title IS NULL
973 OR contains(ipc_tl.name,nvl(:job_title,''123Sys_Def321'') ) > 0
974 )';
975 -- Department Match
976 -- NOTE: name is a placeholder for a multi column index so it is used
977 -- to cover job_title org_description etc.
978 l_sql := l_sql || ' AND ( :department IS NULL
979 OR contains(ipc_tl.name,nvl(:department,''123Sys_Def321'') ) > 0
980 )';
981 -- Employment Category
982 l_sql := l_sql || ' AND ( nvl(:employment_category,''EITHER'') = ''EITHER''
983 OR icrit_vac.employment_category = ''EITHER''
984 OR :employment_category = icrit_vac.employment_category
985 )';
986 -- Keyword Match
987 l_sql := l_sql || ' AND ( :keywords is null
988 OR contains (ipc_tl.name,nvl(:keywords,''321Sys_Def123''),1)>0
989 )';
990 -- Location Keyword Match
991 l_sql := l_sql || ' AND ( irc_seeker_vac_matching_pkg.get_location_match(:location,vac.location_id) > 0
992 OR exists( select ilp.location_id
993 from irc_location_preferences ilp,
994 hr_locations_all loc_pref
995 where ilp.object_type (+) = ''VACANCY''
996 and vac.vacancy_id = ilp.object_id(+)
997 and ilp.location_id = loc_pref.location_id
998 and catsearch(loc_pref.derived_locale, :location, null ) > 0
999 )
1000 )';
1001 -- Location Distance Match
1002 l_sql := l_sql || ' AND (
1003 ( :distance_to_location IS NOT NULL
1004 AND ( loc.geometry IS NOT NULL
1005 OR exists ( select 1
1006 from irc_location_preferences ilp,
1007 hr_locations_all loc_pref
1008 where ilp.object_type (+) = ''VACANCY''
1009 and vac.vacancy_id = ilp.object_id(+)
1010 and ilp.location_id = loc_pref.location_id
1011 and loc_pref.geometry IS NOT NULL
1012 )
1013 )
1014 AND :longitude IS NOT NULL
1015 AND ( locator_within_distance
1016 ( loc.geometry
1017 , mdsys.sdo_geometry(2001,8307,mdsys.sdo_point_type(:longitude,:latitude,null),null,null)
1018 , ''distance=''
1019 || :distance_to_location
1020 ||'' units=MILE''
1021 ) = ''TRUE''
1022 OR exists ( select 1
1023 from irc_location_preferences ilp,
1024 hr_locations_all loc_pref
1025 where ilp.object_type (+) = ''VACANCY''
1026 and vac.vacancy_id = ilp.object_id(+)
1027 and ilp.location_id = loc_pref.location_id
1028 and locator_within_distance (loc_pref.geometry
1029 , mdsys.sdo_geometry(2001,8307,mdsys.sdo_point_type(:longitude,:latitude,null),null,null)
1030 , ''distance=''
1031 || :distance_to_location
1032 ||'' units=MILE''
1033 ) = ''TRUE''
1034 )
1035 )
1036 )
1037 OR ( :distance_to_location IS NULL
1038 AND :geocode_location IS NULL
1039 AND (
1040 ( :geocode_country IS NOT NULL
1041 AND ( loc.country = :geocode_country
1042 OR exists ( select 1
1043 from irc_location_preferences ilp,
1044 hr_locations_all loc_pref
1045 where ilp.object_type (+) = ''VACANCY''
1046 and vac.vacancy_id = ilp.object_id(+)
1047 and ilp.location_id = loc_pref.location_id
1048 and loc_pref.country = :geocode_country
1049 )
1050 )
1051 )
1052 )
1053 )
1054 OR ( :distance_to_location IS NULL
1055 AND :longitude IS NULL
1056 AND :geocode_country IS NULL
1057 )
1058 ) ';
1059 -- location_id match
1060 l_sql := l_sql || ' AND ( ( :location_id is not null
1061 AND ( vac.location_id = :location_id
1062 OR exists( select ilp.location_id
1063 from irc_location_preferences ilp,
1064 hr_locations_all loc_pref
1065 where ilp.object_type (+) = ''VACANCY''
1066 and vac.vacancy_id = ilp.object_id(+)
1067 and ilp.location_id = loc_pref.location_id
1068 and ilp.location_id = :location_id
1069 )
1070 )
1071 )
1072 OR(:location_id is null)
1073 )';
1074 -- derived locale exact match
1075 l_sql := l_sql || ' AND ( ( exists (select 1 from irc_location_criteria_values irc_lcv
1076 where ( loc.derived_locale = irc_lcv.derived_locale
1077 OR exists ( select ilp.location_id
1078 from irc_location_preferences ilp,
1079 hr_locations_all loc_pref
1080 where ilp.object_type (+) = ''VACANCY''
1081 and vac.vacancy_id = ilp.object_id(+)
1082 and ilp.location_id = loc_pref.location_id
1083 and loc_pref.derived_locale = irc_lcv.derived_locale
1084 )
1085 )
1086 and irc_lcv.search_criteria_id = :search_criteria_id
1087 )
1088 )
1089 OR (not exists (select 1 from irc_location_criteria_values irc_lcv
1090 where irc_lcv.search_criteria_id = :search_criteria_id
1091 )
1092 )
1093 )';
1094 -- Competence Match
1095 l_sql:= l_sql||' AND (:match_competence = ''N''
1096 OR
1097 irc_skills_matching_pkg.vacancy_match_percent
1098 ( :person_id
1099 , icrit_vac.object_id
1100 ) <>''-1'' -- This means all essential skills are matched
1101 )';
1102 -- Qualification
1103 l_sql:= l_sql||' AND (:match_qualification = ''N''
1104 OR
1105 exists (SELECT 1
1106 FROM per_qualifications qual
1107 , per_qualification_types qty
1108 WHERE qual.qualification_type_id
1109 = qty.qualification_type_id
1110 AND qual.person_id
1111 = :person_id
1112 AND qty.rank
1113 BETWEEN NVL(icrit_vac.min_qual_level,0)
1114 AND NVL(icrit_vac.max_qual_level,qty.rank)
1115 )
1116 )';
1117 -- Professional Area
1118 l_sql:= l_sql||' AND (( exists (select 1 from irc_prof_area_criteria_values irc_pacv
1119 where icrit_vac.professional_area=irc_pacv.professional_area
1120 and irc_pacv.search_criteria_id=:search_criteria_id))
1121 or (not exists (select 1 from irc_prof_area_criteria_values irc_pacv
1122 where irc_pacv.search_criteria_id=:search_criteria_id)))';
1123 -- Work At Home Area
1124 l_sql:= l_sql||' AND (icrit_vac.work_at_home = ''POSSIBLY''
1125 OR :work_at_home IS NULL
1126 OR icrit_vac.work_at_home IS NULL
1127 OR icrit_vac.work_at_home
1128 = :work_at_home
1129 )';
1130
1131 l_last_value := ltrim(substr(l_matching_flex,LENGTH(l_matching_flex)-1,LENGTH(l_matching_flex)),'|');
1132 l_matching_values := l_matching_flex;
1133 l_counter := 0;
1134
1135 -- Dynamically add in the Flex attributes matching based on the profile.
1136 while (l_matching_values IS NOT NULL)
1137 LOOP
1138 IF (l_last_value = l_matching_values)
1139 THEN
1140 l_attribute := 'ATTRIBUTE'||l_matching_values;
1141 l_matching_values := NULL;
1142 ELSE
1143 l_attribute := 'ATTRIBUTE'||substr(l_matching_values,1,instr(l_matching_values, '|')-1);
1144 l_matching_values := ltrim(ltrim(l_matching_values,substr(l_matching_values,1,instr(l_matching_values, '|')-1)),'|');
1145 END IF;
1146
1147 -- populate the matching_values_tbl so that we can bind in the critiera in
1148 -- the inner loop.
1149 matching_values_tbl(l_counter) := l_attribute;
1150 l_sql:= l_sql||' AND ((icrit_vac.'||lower(l_attribute)||
1151 ' IS NULL AND :'||l_attribute||
1152 ' IS NULL) OR lower(icrit_vac.'||l_attribute||') = :'||lower(l_attribute)||') ';
1153 l_counter := l_counter + 1;
1154
1155 END LOOP;
1156
1157 -- USE DBMS SQL so that we can parse the SQL once and bind in the criteria many
1158 -- times in the inner loop, saving parse time compared to NDS.
1159 l_cursor := DBMS_SQL.OPEN_CURSOR;
1160
1161 DBMS_SQL.PARSE (c => l_cursor,
1162 statement => l_sql,
1163 language_flag => dbms_sql.native);
1164
1165 /*Save existing session language to restore after sending notifications*/
1166 open get_nls_lang(userenv('LANG'));
1167 fetch get_nls_lang into l_dft_lang;
1168 close get_nls_lang;
1169 --
1170 hr_utility.set_location('l_dft_lang: ' || l_dft_lang, 15);
1171 if (l_dft_lang is not null) then
1172 l_dft_lang := '"' || l_dft_lang || '"';
1173 end if;
1174 hr_utility.set_location('l_dft_lang: ' || l_dft_lang, 16);
1175 --
1176 -- ************************************************************
1177 -- Loop through all the job seekers eligible for an email today
1178 -- ************************************************************
1179 FOR seeker_rec IN csr_seekers_for_email
1180 (p_ignore_seeker_matching_freq => p_ignore_seeker_matching_freq) LOOP
1181 --
1182 -- Clear the table as now we need to build up a new record for
1183 -- a different (or the first) job seeker
1184 --
1185 begin
1186 hr_utility.set_location('person_id:'
1187 ||seeker_rec.person_id, 20);
1188 hr_utility.set_location('lang_pref:'
1189 ||seeker_rec.lang_pref, 20);
1190
1191 l_seeker_lang_pref := seeker_rec.lang_pref;
1192 if (l_seeker_lang_pref is not null ) then
1193 l_seeker_lang_pref := '"' || l_seeker_lang_pref || '"';
1194 end if;
1195 /*change the session language if the user lang is differ from current lang*/
1196 open get_nls_lang(userenv('LANG'));
1197 fetch get_nls_lang into l_current_lang;
1198 close get_nls_lang;
1199
1200 if (l_current_lang <> seeker_rec.lang_pref) then
1201 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',l_seeker_lang_pref);
1202 end if;
1203 l_posting_details_tab.delete;
1204 l_counter := 0;
1205 --dbms_output.put_line('Processing Person:'||seeker_rec.person_id);
1206 --
1207 -- By having two more cursors instead of one, one email can
1208 -- easily be produced containing jobs for all the seekers
1209 -- criteria. The only drawback (which can be overcome if
1210 -- necessary) is that the same job can appear multiple times.
1211 -- For Phase 1 this is deemed to be acceptable.
1212 -- ********************************************************
1213 -- Get a list of all the job seeker search criteria
1214 -- ********************************************************
1215 FOR seeker_criteria_rec IN csr_seeker_criteria_for_email
1216 (p_seeker_details => seeker_rec) LOOP
1217 --
1218 -- ********************************************************
1219 -- Get a list of all the suitable jobs for the job seeker
1220 -- ********************************************************
1221 -- And no applicant assignment has ever existed for the vacancy
1222 --dbms_output.put_line(' Processing Search Criteria:'||seeker_criteria_rec.search_criteria_id);
1223
1224 -- define the posting_content_id column
1225 DBMS_SQL.DEFINE_COLUMN (
1226 c => l_cursor,
1227 position => 1,
1228 column => seeker_criteria_match_vac_rec.posting_content_id);
1229
1230 -- define the job_title column
1231 DBMS_SQL.DEFINE_COLUMN (
1232 c => l_cursor,
1233 position => 2,
1234 column => seeker_criteria_match_vac_rec.job_title,
1235 column_size => 240);
1236
1237 -- define the name column
1238 DBMS_SQL.DEFINE_COLUMN (
1239 c => l_cursor,
1240 position => 3,
1241 column => seeker_criteria_match_vac_rec.name,
1242 column_size => 240);
1243
1244 -- define the object_id column
1245 DBMS_SQL.DEFINE_COLUMN (
1246 c => l_cursor,
1247 position => 4,
1248 column => seeker_criteria_match_vac_rec.object_id);
1249
1250 /*
1251 dbms_output.put_line(' BINDS person_id:'||seeker_criteria_rec.object_id);
1252 dbms_output.put_line(' BINDS party_id:'||seeker_rec.party_id);
1253 dbms_output.put_line(' BINDS employee:'||seeker_criteria_rec.employee);
1254 dbms_output.put_line(' BINDS contractor:'||seeker_criteria_rec.contractor);
1255 dbms_output.put_line(' BINDS travel_percentage:'||seeker_criteria_rec.travel_percentage);
1256 dbms_output.put_line(' BINDS salary_currency:'||seeker_criteria_rec.salary_currency);
1257 dbms_output.put_line(' BINDS min_salary:'||seeker_criteria_rec.min_salary);
1258 dbms_output.put_line(' BINDS show_jobs_since:'||seeker_criteria_rec.show_jobs_since);
1259 dbms_output.put_line(' BINDS job_title:'||seeker_criteria_rec.job_title);
1260 dbms_output.put_line(' BINDS department:'||seeker_criteria_rec.department);
1261 dbms_output.put_line(' BINDS employment_category:'||seeker_criteria_rec.employment_category);
1262 dbms_output.put_line(' BINDS keywords:'||seeker_criteria_rec.keywords);
1263 dbms_output.put_line(' BINDS work_at_home:'||seeker_criteria_rec.work_at_home);
1264 dbms_output.put_line(' BINDS location:'||seeker_criteria_rec.location);
1265 dbms_output.put_line(' BINDS longitude:'||seeker_criteria_rec.geometry.sdo_point.x);
1266 dbms_output.put_line(' BINDS latitude:'||seeker_criteria_rec.geometry.sdo_point.y);
1267 dbms_output.put_line(' BINDS distance_to_location:'||seeker_criteria_rec.distance_to_location);
1268 dbms_output.put_line(' BINDS location_id:'||seeker_criteria_rec.location_id);
1269 dbms_output.put_line(' BINDS search_criteria_id:'||seeker_criteria_rec.search_criteria_id);
1270 dbms_output.put_line(' BINDS p_ignore_job_age:'||p_ignore_job_age);
1271 dbms_output.put_line(' BINDS match_qualification:'||seeker_criteria_rec.match_qualification);
1272 dbms_output.put_line(' BINDS match_competence:'||seeker_criteria_rec.match_competence);
1273 */
1274
1275 -- supply binds (bind by name)
1276 dbms_sql.bind_variable(
1277 l_cursor, 'person_id', seeker_criteria_rec.object_id);
1278 dbms_sql.bind_variable(
1279 l_cursor, 'current_employee', nvl(seeker_criteria_rec.current_employee_flag,'N'));
1280 dbms_sql.bind_variable(
1281 l_cursor, 'party_id', seeker_rec.party_id);
1282 dbms_sql.bind_variable(
1283 l_cursor, 'employee', seeker_criteria_rec.employee);
1284 dbms_sql.bind_variable(
1285 l_cursor, 'contractor', seeker_criteria_rec.contractor);
1286 dbms_sql.bind_variable(
1287 l_cursor, 'travel_percentage', seeker_criteria_rec.travel_percentage);
1288 dbms_sql.bind_variable(
1289 l_cursor, 'salary_currency', seeker_criteria_rec.salary_currency);
1290 dbms_sql.bind_variable(
1291 l_cursor, 'min_salary', seeker_criteria_rec.min_salary);
1292 dbms_sql.bind_variable(
1293 l_cursor, 'show_jobs_since', seeker_criteria_rec.show_jobs_since);
1294 dbms_sql.bind_variable(
1295 l_cursor, 'job_title', seeker_criteria_rec.job_title);
1296 dbms_sql.bind_variable(
1297 l_cursor, 'department', seeker_criteria_rec.department);
1298 dbms_sql.bind_variable(
1299 l_cursor, 'employment_category', seeker_criteria_rec.employment_category);
1300 if seeker_criteria_rec.keywords is null then
1301 dbms_sql.bind_variable(
1302 l_cursor, 'keywords', to_char(null));
1303 else
1304 dbms_sql.bind_variable(
1305 l_cursor, 'keywords', irc_query_parser_pkg.query_parser(seeker_criteria_rec.keywords));
1306 end if;
1307 dbms_sql.bind_variable(
1308 l_cursor, 'work_at_home', seeker_criteria_rec.work_at_home);
1309 dbms_sql.bind_variable(
1310 l_cursor, 'location', seeker_criteria_rec.location);
1311 dbms_sql.bind_variable(
1312 l_cursor, 'longitude', seeker_criteria_rec.geometry.sdo_point.x);
1313 dbms_sql.bind_variable(
1314 l_cursor, 'latitude', seeker_criteria_rec.geometry.sdo_point.y);
1315 dbms_sql.bind_variable(
1316 l_cursor, 'distance_to_location', seeker_criteria_rec.distance_to_location);
1317 -- Newly added for geocode_location, geocode_country start
1318 dbms_sql.bind_variable(
1319 l_cursor, 'geocode_location', seeker_criteria_rec.geocode_location);
1320 dbms_sql.bind_variable(
1321 l_cursor, 'geocode_country', seeker_criteria_rec.geocode_country);
1322 -- Newly added for geocode_location, geocode_country end
1323 dbms_sql.bind_variable(
1324 l_cursor, 'location_id', seeker_criteria_rec.location_id);
1325 dbms_sql.bind_variable(
1326 l_cursor, 'search_criteria_id', seeker_criteria_rec.search_criteria_id);
1327 dbms_sql.bind_variable(
1328 l_cursor, 'p_ignore_job_age', p_ignore_job_age);
1329 dbms_sql.bind_variable(
1330 l_cursor, 'match_qualification', seeker_criteria_rec.match_qualification);
1331 dbms_sql.bind_variable(
1332 l_cursor, 'match_competence', seeker_criteria_rec.match_competence);
1333
1334 for ct in 0..matching_values_tbl.count-1 loop
1335 IF (matching_values_tbl(ct)= 'ATTRIBUTE1')
1336 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE1', lower(seeker_criteria_rec.attribute1));
1337
1338 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE2')
1339 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE2', lower(seeker_criteria_rec.attribute2));
1340
1341 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE3')
1342 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE3', lower(seeker_criteria_rec.attribute3));
1343
1344 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE4')
1345 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE4', lower(seeker_criteria_rec.attribute4));
1346
1347 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE5')
1348 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE5', lower(seeker_criteria_rec.attribute5));
1349
1350 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE6')
1351 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE6', lower(seeker_criteria_rec.attribute6));
1352
1353 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE7')
1354 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE7', lower(seeker_criteria_rec.attribute7));
1355
1356 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE8')
1357 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE8', lower(seeker_criteria_rec.attribute8));
1358
1359 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE9')
1360 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE9', lower(seeker_criteria_rec.attribute9));
1361
1362 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE10')
1363 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE10', lower(seeker_criteria_rec.attribute10));
1364
1365 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE11')
1366 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE11', lower(seeker_criteria_rec.attribute11));
1367
1368 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE12')
1369 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE12', lower(seeker_criteria_rec.attribute12));
1370
1371 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE13')
1372 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE13', lower(seeker_criteria_rec.attribute13));
1373
1374 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE14')
1375 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE14', lower(seeker_criteria_rec.attribute14));
1376
1377 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE15')
1378 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE15', lower(seeker_criteria_rec.attribute15));
1379
1380 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE16')
1381 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE16', lower(seeker_criteria_rec.attribute16));
1382
1383 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE17')
1384 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE17', lower(seeker_criteria_rec.attribute17));
1385
1386 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE18')
1387 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE18', lower(seeker_criteria_rec.attribute18));
1388
1389 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE19')
1390 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE19', lower(seeker_criteria_rec.attribute19));
1391
1392 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE20')
1393 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE20', lower(seeker_criteria_rec.attribute20));
1394
1395 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE21')
1396 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE21', lower(seeker_criteria_rec.attribute21));
1397
1398 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE22')
1399 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE22', lower(seeker_criteria_rec.attribute22));
1400
1401 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE23')
1402 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE23', lower(seeker_criteria_rec.attribute23));
1403
1404 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE24')
1405 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE24', lower(seeker_criteria_rec.attribute24));
1406
1407 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE25')
1408 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE25', lower(seeker_criteria_rec.attribute25));
1409
1410 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE26')
1411 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE26', lower(seeker_criteria_rec.attribute26));
1412
1413 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE27')
1414 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE27', lower(seeker_criteria_rec.attribute27));
1415
1416 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE28')
1417 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE28', lower(seeker_criteria_rec.attribute28));
1418
1419 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE29')
1420 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE29', lower(seeker_criteria_rec.attribute29));
1421
1422 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE30')
1423 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE30', lower(seeker_criteria_rec.attribute30));
1424 END IF;
1425 end loop;
1426
1427 -- execute the cursor
1428 l_row := DBMS_SQL.EXECUTE (c => l_cursor);
1429
1430 -- while there is data to fetch
1431 WHILE DBMS_SQL.FETCH_ROWS (c => l_cursor) <> 0
1432 LOOP
1433 -- define the posting_content_id column
1434 DBMS_SQL.column_value (
1435 c => l_cursor,
1436 position => 1,
1437 value => seeker_criteria_match_vac_rec.posting_content_id);
1438
1439 -- define the job_title column
1440 DBMS_SQL.column_value (
1441 c => l_cursor,
1442 position => 2,
1443 value => seeker_criteria_match_vac_rec.job_title);
1444
1445 -- define the name column
1446 DBMS_SQL.column_value (
1447 c => l_cursor,
1448 position => 3,
1449 value => seeker_criteria_match_vac_rec.name);
1450
1451 -- define the object_id column
1452 DBMS_SQL.column_value (
1453 c => l_cursor,
1454 position => 4,
1455 value => seeker_criteria_match_vac_rec.object_id);
1456
1457 -- Counter for all the seekers search criteria suitable jobs
1458 -- (which is why csr_seeker_vacancies_for_email%rowcount
1459 -- can't be used)
1460 l_counter := l_counter + 1;
1461 --
1462 -- Build up a table containing all the suitable vacancies
1463 --
1464 l_posting_details_tab(l_counter)
1465 := seeker_criteria_match_vac_rec;
1466 --
1467 hr_utility.set_location('vacancy_id:'
1468 ||seeker_criteria_match_vac_rec.object_id, 30);
1469
1470 --dbms_output.put_line(' Person:'||seeker_criteria_rec.object_id||' FOR VAC:'||seeker_criteria_match_vac_rec.object_id||'>'||seeker_criteria_match_vac_rec.name);
1471 END LOOP;
1472 --
1473 END LOOP; -- seeker_criteria_rec / csr_seeker_criteria_for_email
1474 --
1475 -- ************************************************************
1476 -- Send an e-mail to the job seeker with suitable jobs
1477 -- ************************************************************
1478 IF l_posting_details_tab.count > 0 THEN
1479 -- Send the notification if any suitable vacancies have been found
1480 hr_utility.set_location('sending notification:',50);
1481 --dbms_output.put_line(' Person:'||seeker_rec.person_id||' Has Matches');
1482 --
1483 l_message_subject := fnd_message.get_string('PER','IRC_EMAIL_SEEKERS_SUBJECT');
1484 l_id :=
1485 irc_notification_helper_pkg.send_notification
1486 ( p_person_id => seeker_rec.person_id
1487 , p_subject => l_message_subject
1488 , p_html_body => get_seeker_html_msg_body
1489 ( p_posting_details_tab =>l_posting_details_tab
1490 , p_person_id => seeker_rec.person_id )
1491 , p_text_body => get_seeker_text_msg_body
1492 ( p_posting_details_tab =>l_posting_details_tab
1493 , p_person_id => seeker_rec.person_id )
1494 );
1495 END IF;
1496 --
1497 hr_utility.set_location('Location:'||l_proc,60);
1498 exception
1499 when others then
1500 -- Catch the exception so that an error doens't cause total failure.
1501 hr_utility.set_location('Problem sending notification to person:'||seeker_rec.person_id,60);
1502 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',l_dft_lang);
1503 end;
1504 END LOOP;
1505 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',l_dft_lang);
1506 DBMS_SQL.CLOSE_CURSOR (c => l_cursor);--All done, so clean up!
1507
1508 --
1509 hr_utility.set_location('Leaving'||l_proc, 80);
1510 EXCEPTION
1511 WHEN others THEN
1512 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',l_dft_lang);
1513 ERRBUF := SQLERRM||' '||fnd_message.get;
1514 RETCODE := 2;
1515 END email_suitable_vacs_to_seekers;
1516
1517 --
1518 -- -------------------------------------------------------------------------
1519 -- |-------------------< get_suitable_seekers_for_vac >--------------------|
1520 -- -------------------------------------------------------------------------
1521 --
1522 PROCEDURE get_suitable_seekers_for_vac
1523 ( errbuf OUT NOCOPY VARCHAR2
1524 , retcode OUT NOCOPY NUMBER
1525 , p_candidacy_age IN NUMBER DEFAULT 0
1526 )
1527 IS
1528 --
1529 l_sql VARCHAR2(30000);
1530 l_cursor NUMBER;
1531 l_row NUMBER;
1532 l_matching_flex VARCHAR2(240);
1533 l_last_value VARCHAR2(240);
1534 l_matching_values VARCHAR2(240);
1535 l_attribute VARCHAR2(14);
1536 seeker_details_rec g_seeker_details_rec_type;
1537 matching_values_tbl dbms_sql.varchar2_table;
1538 l_recruiter_problem_tab g_recruiter_problem_tab_type;
1539 --
1540 l_message_subject VARCHAR2(300);
1541 l_amount NUMBER;
1542 --
1543 l_counter NUMBER DEFAULT 0;
1544 --
1545 l_recruiter_id PER_VACANCIES.RECRUITER_ID%type ;
1546 l_seeker_details_tab g_seeker_details_tab_type;
1547 l_id NUMBER;
1548 l_index NUMBER default 0;
1549 l_base_url VARCHAR2(250);
1550 e_no_base_url EXCEPTION;
1551 l_msg_sent NUMBER DEFAULT 0;
1552 l_date_registered date;
1553 l_internal_site irc_all_recruiting_sites.internal%type;
1554 l_external_site irc_all_recruiting_sites.external%type;
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
1564 l_date_registered := hr_api.g_sot;
1565 end if;
1566 --
1567 -- **************************************************************************
1568 -- Inner SQL to find Job Seekers for open vacancies
1569 -- **************************************************************************
1570 -- Note : a clob, such as brief_description can't be retrieved in this
1571 -- cursor because of the DISTINCT.
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
1581 WHERE icrit.object_type = ''WPREF''
1582 AND icrit.object_id = ppf.person_id
1583 AND ipc_tl.posting_content_id = :posting_content_id
1584 AND ipc_tl.language = userenv(''LANG'')
1585 AND inp.person_id = ppf.person_id
1586 AND inp.allow_access = ''Y''
1587 AND inp.creation_date >= :date_registered
1588 AND ppf.effective_start_date >= :date_registered
1589 AND ( (ppf.current_employee_flag = ''Y'' and :internal = ''Y'')
1590 OR (ppf.current_employee_flag is null and :external = ''Y'')
1591 )
1592 AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date ';
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
1602 and trunc(sysdate) between ppf1.effective_start_date and ppf1.effective_end_date
1603 ) ';
1604 -- Employee and Contractor Match
1605 l_sql := l_sql || ' AND ( icrit.employee = :employee
1606 OR icrit.contractor = :contractor
1607 ) ';
1608 -- travel percentage : Assume no Seeker value means they can travel and
1609 -- no vac value means no travel is involved
1610 l_sql := l_sql || ' AND NVL(:travel_percentage,0) <= NVL(icrit.travel_percentage,100) ';
1611
1612 -- Min/Max Salary
1613 l_sql := l_sql||' AND ( ( :salary_currency = icrit.salary_currency
1614 AND icrit.min_salary <= :max_salary
1615 )
1616 OR (:salary_currency is null)
1617 OR (:max_salary is null)
1618 OR (icrit.salary_currency is null)
1619 OR (icrit.min_salary is null)
1620 OR ( :salary_currency <> icrit.salary_currency
1621 AND icrit.min_salary <= irc_seeker_vac_matching_pkg.convert_vacancy_amount
1622 (:salary_currency
1623 ,icrit.salary_currency
1624 ,:max_salary
1625 ,sysdate
1626 ,:business_group_id
1627 ,''P''
1628 )
1629 )
1630 ) ';
1631 -- NOTE: ipc_tl is a multistore index - it includes most of the ipc
1632 -- columns, hence it can be used for both the Job Title and
1633 -- Department Match.
1634 -- Job Title Match
1635 l_sql := l_sql || ' AND ( icrit.job_title IS NULL
1636 OR contains (ipc_tl.name,nvl(icrit.job_title,''123Sys_Def321'')) > 0
1637 ) ';
1638 -- Department Match
1639 l_sql := l_sql || ' AND ( icrit.department IS NULL
1640 OR contains (ipc_tl.name, nvl(icrit.department,''123Sys_Def321''))>0
1641 )';
1642 -- Employment Category
1643 l_sql := l_sql || ' AND ( NVL(:employment_category,''EITHER'') = ''EITHER''
1644 OR icrit.employment_category = ''EITHER''
1645 OR :employment_category = icrit.employment_category
1646 ) ';
1647 -- Keyword Match
1648 l_sql := l_sql || ' AND ( icrit.keywords IS NULL
1649 OR contains (ipc_tl.name, nvl(:keywords,''123Sys_Def321''))>0
1650 )';
1651 -- Location Keyword Match
1652 l_sql := l_sql || ' AND ( icrit.location is null
1653 OR( icrit.location is not null
1654 AND ( exists ( select 1
1655 from hr_locations_all loc
1656 where loc.location_id = :location_id
1657 and catsearch(loc.derived_locale, icrit.location, null) > 0
1658 )
1659 OR exists ( select 1
1660 from irc_location_preferences ilp
1661 , hr_locations_all loc_pref
1662 where ilp.object_type = ''VACANCY''
1663 and ilp.object_id = :vacancy_id
1664 and ilp.location_id = loc_pref.location_id
1665 and catsearch(loc_pref.derived_locale, icrit.location, null) > 0
1666 )
1667 )
1668 )
1669 ) ';
1670 -- Location Distance Match
1671 l_sql := l_sql||' AND ( ( exists(select 1
1672 from hr_locations_all loc2
1673 where loc2.location_id = :location_id
1674 and icrit.geometry.sdo_point.x is not null
1675 and loc2.geometry is not null
1676 and locator_within_distance
1677 ( loc2.geometry
1678 , icrit.geometry
1679 , ''distance=''
1680 || nvl(icrit.distance_to_location,0)
1681 ||'' units=MILE''
1682 )= ''TRUE''
1683 )
1684 OR exists (select 1
1685 from irc_location_preferences ilp
1686 , hr_locations_all loc_pref
1687 where ilp.object_type = ''VACANCY''
1688 and ilp.object_id = :vacancy_id
1689 and ilp.location_id = loc_pref.location_id
1690 and icrit.geometry.sdo_point.x is not null
1691 and loc_pref.geometry is not null
1692 and locator_within_distance
1693 ( loc_pref.geometry
1694 , icrit.geometry
1695 , ''distance=''
1696 || nvl(icrit.distance_to_location,0)
1697 ||'' units=MILE''
1698 )= ''TRUE''
1699 )
1700 )
1701 OR( icrit.distance_to_location IS NULL
1702 AND icrit.geometry IS NULL
1703 AND ( icrit.geocode_country IS NULL
1704 OR ( icrit.geocode_country IS NOT NULL
1705 AND ( icrit.geocode_country = :country
1706 OR exists ( select 1
1707 from irc_location_preferences ilp
1708 , hr_locations_all loc_pref
1709 where ilp.object_type = ''VACANCY''
1710 and ilp.object_id = :vacancy_id
1711 and ilp.location_id = loc_pref.location_id
1712 and loc_pref.country = icrit.geocode_country
1713 )
1714 )
1715 )
1716 )
1717 )
1718 )';
1719 -- location_id match
1720 l_sql := l_sql || ' AND ( ( icrit.location_id is not null
1721 AND ( icrit.location_id = :location_id
1722 OR exists ( select 1
1723 from irc_location_preferences ilp
1724 , hr_locations_all loc_pref
1725 where ilp.object_type = ''VACANCY''
1726 and ilp.object_id = :vacancy_id
1727 and ilp.location_id = loc_pref.location_id
1728 and loc_pref.location_id = icrit.location_id
1729 )
1730 )
1731 )
1732 OR(icrit.location_id is null)
1733 )';
1734 -- derived locale exact match
1735 l_sql := l_sql || ' AND ( (exists(select 1 from irc_location_criteria_values irc_lcv
1736 where irc_lcv.search_criteria_id = icrit.search_criteria_id
1737 and :derived_locale = irc_lcv.derived_locale
1738 )
1739 )
1740 OR (exists (select 1 from irc_location_criteria_values irc_lcv
1741 ,irc_location_preferences ilp
1742 ,hr_locations_all loc_pref
1743 where irc_lcv.search_criteria_id = icrit.search_criteria_id
1744 and ilp.object_type = ''VACANCY''
1745 and ilp.object_id = :vacancy_id
1746 and ilp.location_id = loc_pref.location_id
1747 and loc_pref.derived_locale = irc_lcv.derived_locale
1748 )
1749 )
1750 OR (not exists (select 1 from irc_location_criteria_values irc_lcv
1751 where irc_lcv.search_criteria_id = icrit.search_criteria_id
1752 )
1753 )
1754 OR (:derived_locale is null)
1755 )';
1756 -- This is the section that differs between the two cursors
1757 -- Competence Match
1758 l_sql := l_sql||' AND (:match_competence = ''N''
1759 OR
1760 irc_skills_matching_pkg.vacancy_match_percent
1761 ( :vacancy_id
1762 , icrit.object_id
1763 ) <> ''-1'' -- This means all essential skills are matched
1764 )';
1765 -- Qualification
1766 l_sql := l_sql||' AND (:match_qualification = ''N''
1767 OR
1768 exists (SELECT 1
1769 FROM per_qualifications qual
1770 , per_qualification_types qty
1771 WHERE qual.qualification_type_id
1772 = qty.qualification_type_id
1773 AND qual.party_id = ppf.party_id
1774 AND qty.rank
1775 BETWEEN NVL(:min_qual_level,0)
1776 AND NVL(:max_qual_level,qty.rank)
1777 )
1778 OR (:min_qual_level is null and :max_qual_level is null))';
1779 -- Professional Area
1780 l_sql := l_sql||' AND ((exists (select 1 from irc_prof_area_criteria_values irc_pacv
1781 where irc_pacv.professional_area = :professional_area
1782 and irc_pacv.search_criteria_id=icrit.search_criteria_id))
1783 OR (not exists (select 1 from irc_prof_area_criteria_values irc_pacv
1784 where irc_pacv.search_criteria_id=icrit.search_criteria_id))
1785 OR :professional_area IS NULL)';
1786 -- Work At Home Area
1787 l_sql := l_sql||' AND (icrit.work_at_home = ''POSSIBLE''
1788 OR :work_at_home IS NULL
1789 OR icrit.work_at_home IS NULL
1790 OR icrit.work_at_home = :work_at_home
1791 )';
1792
1793 -- Dynamically add in the Flex attributes matching based on the profile.
1794 l_last_value := ltrim(substr(l_matching_flex,LENGTH(l_matching_flex)-1,LENGTH(l_matching_flex)),'|');
1795 l_matching_values := l_matching_flex;
1796 l_counter := 0;
1797
1798 while (l_matching_values IS NOT NULL)
1799 LOOP
1800 IF (l_last_value = l_matching_values)
1801 THEN
1802 l_attribute := 'ATTRIBUTE'||l_matching_values;
1803 l_matching_values := NULL;
1804 ELSE
1805 l_attribute := 'ATTRIBUTE'||substr(l_matching_values,1,instr(l_matching_values, '|')-1);
1806 l_matching_values := ltrim(ltrim(l_matching_values,substr(l_matching_values,1,instr(l_matching_values, '|')-1)),'|');
1807 END IF;
1808
1809 matching_values_tbl(l_counter) := l_attribute;
1810 l_sql:= l_sql||' AND ((icrit.'||lower(l_attribute)||
1811 ' IS NULL AND :'||l_attribute||
1812 ' IS NULL) OR lower(icrit.'||l_attribute||') = :'||lower(l_attribute)||') ';
1813 l_counter := l_counter + 1;
1814
1815 END LOOP;
1816 -- USE DBMS SQL so that we can parse the SQL once and bind in the criteria many
1817 -- times in the inner loop, saving parse time compared to NDS.
1818
1819 l_cursor := DBMS_SQL.OPEN_CURSOR;
1820
1821 DBMS_SQL.PARSE (c => l_cursor,
1822 statement => l_sql,
1823 language_flag => dbms_sql.native);
1824
1825
1826 l_base_url := fnd_profile.value('IRC_SUITABLE_SEEKERS_URL');
1827 IF l_base_url IS NULL THEN
1828 hr_utility.set_location(l_proc, 20);
1829 fnd_message.set_name('PER','IRC_412064_NO_EMAIL_VAC_URL');
1830 RAISE e_no_base_url;
1831 END IF;
1832 --
1833 -- ************************************************************--
1834 -- Loop through all the outstanding vacancies --
1835 -- ************************************************************--
1836 FOR available_vacancy IN csr_vacancies_needing_seekers
1837 LOOP
1838 begin
1839 hr_utility.set_location('search_criteria_id:'
1840 ||available_vacancy.search_criteria_id, 20);
1841 --
1842 log_message ('Vacancy :'
1843 ||available_vacancy.name
1844 ||' (search_criteria_id:'
1845 ||available_vacancy.search_criteria_id
1846 ||')'
1847 );
1848 --
1849 l_internal_site := null;
1850 l_external_site := null;
1851 --
1852 l_internal_site := get_int_rec_site(available_vacancy.vacancy_id);
1853 l_external_site := get_ext_rec_site(available_vacancy.vacancy_id);
1854 -- Clear down the table and reset the counter.
1855 l_seeker_details_tab.delete;
1856 l_counter := 0;
1857 -- ************************************************************ --
1858 -- Get a list of all the suitable seekers for the vacancy --
1859 -- ************************************************************ --
1860 hr_utility.set_location('keywords not null', 30);
1861 -- define the posting_content_id column
1862
1863 -- define the name column
1864 DBMS_SQL.DEFINE_COLUMN (
1865 c => l_cursor,
1866 position => 1,
1867 column => seeker_details_rec.full_name,
1868 column_size => 240);
1869
1870 -- define the object_id column
1871 DBMS_SQL.DEFINE_COLUMN (
1872 c => l_cursor,
1873 position => 2,
1874 column => seeker_details_rec.person_id);
1875
1876 -- supply binds (bind by name)
1877 dbms_sql.bind_variable(
1878 l_cursor, 'date_registered', l_date_registered);
1879 dbms_sql.bind_variable(
1880 l_cursor, 'internal', l_internal_site);
1881 dbms_sql.bind_variable(
1882 l_cursor, 'external', l_external_site);
1883 dbms_sql.bind_variable(
1884 l_cursor, 'posting_content_id', available_vacancy.primary_posting_id);
1885 dbms_sql.bind_variable(
1886 l_cursor, 'vacancy_id', available_vacancy.vacancy_id);
1887 dbms_sql.bind_variable(
1888 l_cursor, 'employee', available_vacancy.employee);
1889 dbms_sql.bind_variable(
1890 l_cursor, 'contractor', available_vacancy.contractor);
1891 dbms_sql.bind_variable(
1892 l_cursor, 'travel_percentage', available_vacancy.travel_percentage);
1893 dbms_sql.bind_variable(
1894 l_cursor, 'salary_currency', available_vacancy.salary_currency);
1895 dbms_sql.bind_variable(
1896 l_cursor, 'max_salary', available_vacancy.max_salary);
1897 dbms_sql.bind_variable(
1898 l_cursor, 'business_group_id', available_vacancy.business_group_id);
1899 dbms_sql.bind_variable(
1900 l_cursor, 'employment_category', available_vacancy.employment_category);
1901 if available_vacancy.keywords is null then
1902 dbms_sql.bind_variable(
1903 l_cursor, 'keywords', to_char(null));
1904 else
1905 dbms_sql.bind_variable(
1906 l_cursor, 'keywords', irc_query_parser_pkg.query_parser(available_vacancy.keywords));
1907 end if;
1908 dbms_sql.bind_variable(
1909 l_cursor, 'professional_area', available_vacancy.professional_area);
1910 dbms_sql.bind_variable(
1911 l_cursor, 'work_at_home', available_vacancy.work_at_home);
1912 dbms_sql.bind_variable(
1913 l_cursor, 'location_id', available_vacancy.location_id);
1914 dbms_sql.bind_variable(
1915 l_cursor, 'derived_locale', available_vacancy.derived_locale);
1916 dbms_sql.bind_variable(
1917 l_cursor, 'country', available_vacancy.country);
1918 if available_vacancy.match_qualification is null then
1919 dbms_sql.bind_variable(
1920 l_cursor, 'match_qualification', 'N');
1921 else
1922 dbms_sql.bind_variable(
1923 l_cursor, 'match_qualification', available_vacancy.match_qualification);
1924 end if;
1925 dbms_sql.bind_variable(
1926 l_cursor, 'match_competence', available_vacancy.match_competence);
1927 dbms_sql.bind_variable(
1928 l_cursor, 'min_qual_level', available_vacancy.min_qual_level);
1929 dbms_sql.bind_variable(
1930 l_cursor, 'max_qual_level', available_vacancy.max_qual_level);
1931
1932 for ct in 0..matching_values_tbl.count-1 loop
1933 IF (matching_values_tbl(ct)= 'ATTRIBUTE1')
1934 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE1', lower(available_vacancy.attribute1));
1935
1936 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE2')
1937 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE2', lower(available_vacancy.attribute2));
1938
1939 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE3')
1940 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE3', lower(available_vacancy.attribute3));
1941
1942 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE4')
1943 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE4', lower(available_vacancy.attribute4));
1944
1945 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE5')
1946 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE5', lower(available_vacancy.attribute5));
1947
1948 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE6')
1949 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE6', lower(available_vacancy.attribute6));
1950
1951 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE7')
1952 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE7', lower(available_vacancy.attribute7));
1953
1954 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE8')
1955 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE8', lower(available_vacancy.attribute8));
1956
1957 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE9')
1958 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE9', lower(available_vacancy.attribute9));
1959
1960 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE10')
1961 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE10', lower(available_vacancy.attribute10));
1962
1963 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE11')
1964 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE11', lower(available_vacancy.attribute11));
1965
1966 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE12')
1967 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE12', lower(available_vacancy.attribute12));
1968
1969 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE13')
1970 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE13', lower(available_vacancy.attribute13));
1971
1972 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE14')
1973 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE14', lower(available_vacancy.attribute14));
1974
1975 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE15')
1976 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE15', lower(available_vacancy.attribute15));
1977
1978 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE16')
1979 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE16', lower(available_vacancy.attribute16));
1980
1981 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE17')
1982 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE17', lower(available_vacancy.attribute17));
1983
1984 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE18')
1985 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE18', lower(available_vacancy.attribute18));
1986
1987 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE19')
1988 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE19', lower(available_vacancy.attribute19));
1989
1990 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE20')
1991 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE20', lower(available_vacancy.attribute20));
1992
1993 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE21')
1994 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE21', lower(available_vacancy.attribute21));
1995
1996 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE22')
1997 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE22', lower(available_vacancy.attribute22));
1998
1999 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE23')
2000 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE23', lower(available_vacancy.attribute23));
2001
2002 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE24')
2003 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE24', lower(available_vacancy.attribute24));
2004
2005 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE25')
2006 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE25', lower(available_vacancy.attribute25));
2007
2008 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE26')
2009 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE26', lower(available_vacancy.attribute26));
2010
2011 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE27')
2012 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE27', lower(available_vacancy.attribute27));
2013
2014 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE28')
2015 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE28', lower(available_vacancy.attribute28));
2016
2017 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE29')
2018 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE29', lower(available_vacancy.attribute29));
2019
2020 ELSIF (matching_values_tbl(ct)= 'ATTRIBUTE30')
2021 THEN dbms_sql.bind_variable(l_cursor, 'ATTRIBUTE30', lower(available_vacancy.attribute30));
2022 END IF;
2023 end loop;
2024
2025 -- execute the cursor
2026 l_row := DBMS_SQL.EXECUTE (c => l_cursor);
2027
2028 -- while there is data to fetch
2029 WHILE DBMS_SQL.FETCH_ROWS (c => l_cursor) <> 0
2030 LOOP
2031 DBMS_SQL.column_value (
2032 c => l_cursor,
2033 position => 1,
2034 value => seeker_details_rec.full_name);
2035
2036 DBMS_SQL.column_value (
2037 c => l_cursor,
2038 position => 2,
2039 value => seeker_details_rec.person_id);
2040
2041 log_message ('..person_id:'||seeker_details_rec.person_id);
2042 hr_utility.set_location
2043 (' loop: seeker_details_rec.person_id='
2044 || seeker_details_rec.person_id, 35);
2045 --
2046 l_counter := l_counter + 1;
2047 --
2048 l_seeker_details_tab(l_counter)
2049 := seeker_details_rec;
2050 --
2051 END LOOP;
2052 --dbms_output.put_line('Found '||l_counter||' seekers for '||available_vacancy.name);
2053
2054 -- ************************************************************ --
2055 -- Send an e-mail to the vacancy creator with suitable seekers --
2056 -- ************************************************************ --
2057 log_message ('.suitable seekers :'
2058 ||l_seeker_details_tab.count);
2059 IF l_seeker_details_tab.count > 0 THEN
2060 -- Send the notification if any suitable candidates have been found
2061 hr_utility.set_location('stuff to send ', 50);
2062 --
2063 l_message_subject := null;
2064
2065 fnd_message.set_name('PER','IRC_EMAIL_RECRUITER_SUBJECT');
2066 fnd_message.set_token('VACNAME',available_vacancy.name);
2067 l_message_subject := fnd_message.get;
2068
2069
2070 --
2071 l_id := irc_notification_helper_pkg.send_notification
2072 ( p_person_id => available_vacancy.recruiter_id
2073 , p_subject => l_message_subject
2074 , p_html_body => get_recruiter_html_msg_body
2075 ( p_seeker_details_tab =>l_seeker_details_tab )
2076 , p_text_body => get_recruiter_text_msg_body
2077 ( p_seeker_details_tab =>l_seeker_details_tab )
2078 );
2079 l_msg_sent := l_msg_sent + 1;
2080 END IF;
2081 EXCEPTION
2082 WHEN OTHERS THEN
2083 --dbms_output.put_line('Problem sending '||available_vacancy.name);
2084 -- Build up a table of the problem vacancies/recruiters
2085 l_index :=l_index + 1;
2086 l_recruiter_problem_tab(l_index).recruiter_id := available_vacancy.recruiter_id;
2087 l_recruiter_problem_tab(l_index).vacancy_id
2088 := available_vacancy.vacancy_id;
2089 l_recruiter_problem_tab(l_index).vacancy_name:=available_vacancy.name;
2090 l_recruiter_problem_tab(l_index).sqlerrm := sqlerrm;
2091 l_recruiter_problem_tab(l_index).message := fnd_message.get;
2092 --
2093 END;
2094 --
2095 END LOOP;
2096 --
2097 fnd_message.set_name ('PER','IRC_412069_NUMBER_MSG_SENT');
2098 fnd_message.set_token ('NOMSG',l_msg_sent);
2099 log_message(fnd_message.get);
2100 --
2101 IF ( l_index > 0 ) THEN
2102 -- Maybe have an out parameter that handles these, or put them into
2103 -- the ERRBUF for concurrent processes.
2104 hr_utility.set_location('No recruiter_id ',59);
2105 fnd_message.set_name ('PER','IRC_412070_VACS_NO_OWNERS');
2106 log_message(fnd_message.get);
2107 for i in l_recruiter_problem_tab.first .. l_recruiter_problem_tab.last loop
2108 log_message(l_recruiter_problem_tab(i).vacancy_name);
2109 hr_utility.set_location('Exception ('||i||')', 60);
2110 hr_utility.set_location(' > vacancy_id:' || l_recruiter_problem_tab(i).vacancy_id, 62);
2111 end loop;
2112 END IF;
2113 hr_utility.set_location('Leaving'||l_proc, 80);
2114 EXCEPTION
2115 WHEN others THEN
2116 ERRBUF := SQLERRM||' '||fnd_message.get;
2117 RETCODE := 2;
2118 END get_suitable_seekers_for_vac;
2119 --
2120 --
2121 -- -------------------------------------------------------------------------
2122 -- |-------------------< email_general_notifications >---------------------|
2123 -- -------------------------------------------------------------------------
2124 --
2125 PROCEDURE email_general_notifications
2126 ( errbuf OUT NOCOPY VARCHAR2
2127 , retcode OUT NOCOPY NUMBER
2128 )
2129 IS
2130 --
2131 l_id NUMBER;
2132 l_message_subject VARCHAR2(240)
2133 DEFAULT fnd_message.get_string('PER','IRC_SEEKER_INFO_NOTE_SUBJECT');
2134 l_general_body_html VARCHAR2(32000);
2135 l_general_body_text VARCHAR2(32000);
2136 l_proc VARCHAR2(30) default '.email_general_notifications';
2137 BEGIN
2138 hr_utility.set_location('Entering'||l_proc, 10);
2139 -- Loop through all the job seekers and send them
2140 -- a general notification.
2141 FOR parties_wanting_it IN csr_seekers_for_notes LOOP
2142 --
2143 -- Build the body of the email both in text and html
2144 --
2145 fnd_message.set_name ('PER','IRC_SEEKER_INFO_NOTE_HTML');
2146 fnd_message.set_token ('FIRST_NAME',parties_wanting_it.first_name);
2147 fnd_message.set_token ('LAST_NAME' ,parties_wanting_it.last_name);
2148 fnd_message.set_token ('EMAIL_ADDRESS' ,parties_wanting_it.email_address);
2149 l_general_body_html := fnd_message.get;
2150 l_general_body_html :='<BR/>'|| l_general_body_html ||'<BR/><BR/><p align="center">'
2151 || get_conclusion_msg(
2152 p_message_text =>fnd_message.get_string('PER','IRC_412617_GEN_CONCL_HTML')
2153 ,p_person_id =>parties_wanting_it.person_id
2154 ,p_action =>'UG')||'</p>';
2155 --
2156 fnd_message.set_name ('PER','IRC_SEEKER_INFO_NOTE_TEXT');
2157 fnd_message.set_token ('FIRST_NAME',parties_wanting_it.first_name);
2158 fnd_message.set_token ('LAST_NAME' ,parties_wanting_it.last_name);
2159 fnd_message.set_token ('EMAIL_ADDRESS' ,parties_wanting_it.email_address);
2160 l_general_body_text := fnd_message.get;
2161 l_general_body_text := l_general_body_text ||'\n'
2162 || get_conclusion_msg(
2163 p_message_text =>fnd_message.get_string('PER','IRC_412618_GEN_CONCL_TEXT')
2164 ,p_person_id =>parties_wanting_it.person_id
2165 ,p_action =>'UG');
2166 --
2167 begin
2168 l_id :=
2169 irc_notification_helper_pkg.send_notification
2170 ( p_person_id => parties_wanting_it.person_id
2171 , p_subject => l_message_subject
2172 , p_html_body => l_general_body_html
2173 , p_text_body => l_general_body_text
2174 );
2175 exception
2176 when others then
2177 -- Catch the exception so that an error doens't cause total failure.
2178 hr_utility.set_location('Problem sending notification to person:'||parties_wanting_it.person_id,60);
2179 end;
2180 END LOOP;
2181 hr_utility.set_location('Leaving'||l_proc, 80);
2182 END email_general_notifications;
2183
2184 --
2185 FUNCTION get_conclusion_msg(p_message_text varchar2
2186 ,p_person_id number
2187 ,p_action varchar2 ) return varchar2 as
2188 --
2189 l_apps_fwk_agent varchar2(2000);
2190 l_isInternalPerson varchar2(10);
2191 l_url varchar2(4000);
2192 l_funcId number;
2193 --
2194 cursor c_func(p_function_name varchar2) is
2195 select function_id from fnd_form_functions
2196 where function_name = p_function_name;
2197 --
2198 BEGIN
2199 l_isInternalPerson := irc_utilities_pkg.is_internal_person
2200 (p_person_id=> p_person_id,
2201 p_eff_date => trunc(sysdate)
2202 );
2203 if (l_isInternalPerson ='TRUE') then
2204 l_apps_fwk_agent := fnd_profile.value_specific('APPS_FRAMEWORK_AGENT');
2205 else
2206 l_apps_fwk_agent := nvl(fnd_profile.value('IRC_FRAMEWORK_AGENT'),
2207 fnd_profile.value('APPS_FRAMEWORK_AGENT'));
2208 end if;
2209 --
2210 l_url := l_apps_fwk_agent;
2211 --
2212 if substr(l_url,-1,1)<>'/' then
2213 l_url:=l_url||'/';
2214 end if;
2215 --
2216 if fnd_profile.value('ICX_PREFIX') is not null then
2217 l_url:=l_url||fnd_profile.value('ICX_PREFIX')||'/OA_HTML/';
2218 else
2219 l_url:=l_url||'OA_HTML/';
2220 end if;
2221 --
2222 open c_func('IRC_UNSUBSCRIB_FUNC');
2223 fetch c_func into l_funcId;
2224 close c_func;
2225 --
2226 l_url:= fnd_run_function.get_run_function_url ( p_function_id =>l_funcId,
2227 p_resp_appl_id =>-1,
2228 p_resp_id =>-1,
2229 p_security_group_id =>0,
2230 p_override_agent=>l_url,
2231 p_parameters =>'personId='||p_person_id||'&action='||p_action,
2232 p_encryptParameters =>true ) ;
2233 --
2234 l_url := replace(p_message_text,'&IRC_HYPERLINK',l_url);
2235 --
2236 return l_url;
2237 END get_conclusion_msg;
2238 END irc_seeker_vac_matching_pkg;