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