[Home] [Help]
PACKAGE BODY: APPS.IRC_OFFER_NOTIFICATIONS_PKG
Source
1 Package body irc_offer_notifications_pkg as
2 /* $Header: irofrnotif.pkb 120.21.12010000.2 2009/01/05 09:56:21 kvenukop ship $ */
3
4 -- ----------------------------------------------------------------------------
5 -- CURSORS
6 -- ----------------------------------------------------------------------------
7
8 -- ***************************************************************************
9 -- Cursor to get employee id for the given person id
10 -- ***************************************************************************
11 CURSOR csr_get_employee_id
12 (p_person_id IN number)
13 IS
14 select
15 fnd.employee_id as applicant_id
16 from
17 fnd_user fnd
18 where fnd.person_party_id in
19 (select party_id
20 from per_all_people_f
21 where person_id = p_person_id
22 and trunc(sysdate) between effective_start_date and effective_end_date
23 );
24
25 -- ***************************************************************************
26 -- Cursor to get employee id for the given user id
27 -- ***************************************************************************
28
29 CURSOR csr_get_user_employee_id
30 (p_user_id IN number)
31 IS
32 select
33 usr.employee_id
34 from
35 fnd_user usr
36 where usr.user_id = p_user_id;
37
38 -- ***************************************************************************
39 -- Cursor to get offer sent date
40 -- ***************************************************************************
41 CURSOR csr_get_offer_sent_date
42 (p_offer_id IN number)
43 IS
44 select max(ioh.status_change_date)
45 from irc_offer_status_history ioh
46 where ioh.offer_id = p_offer_id
47 and ioh.offer_status ='EXTENDED';
48
49 -- ***************************************************************************
50 -- Cursor to get offer details for the given offer id
51 -- ***************************************************************************
52 CURSOR csr_send_offer_rcvd
53 (p_offer_id IN number)
54 IS
55 select iof.applicant_assignment_id
56 ,vac.name as vacancy_name
57 ,vac.manager_id as manager_id
58 ,job.name as job_title
59 ,asg.person_id as applicant_id
60 ,ppf.full_name as applicant_name
61 ,iof.created_by as creator_id
62 from irc_offers iof
63 ,per_all_vacancies vac
64 ,per_jobs_vl job
65 ,per_all_assignments_f asg
66 ,per_all_people_f ppf
67 where
68 iof.offer_status = 'EXTENDED'
69 and iof.vacancy_id = vac.vacancy_id
70 and vac.job_id = job.job_id(+)
71 and asg.assignment_id = iof.applicant_assignment_id
72 and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
73 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
74 and ppf.person_id = asg.person_id
75 and iof.offer_id = p_offer_id;
76
77 -- ***************************************************************************
78 -- Cursor to get offer details for the given offer id
79 -- ***************************************************************************
80 CURSOR csr_send_apl_resp
81 (p_offer_id IN number)
82 IS
83 select vac.name as vacancy_name
84 ,asg.supervisor_id as manager_id
85 ,asg.recruiter_id as recruiter_id
86 ,iof.created_by as creator
87 ,job.name as job_title
88 ,asg.person_id as applicant_id
89 ,ppf.full_name as applicant_name
90 ,ipc.name as job_posting_title
91 ,iof.last_updated_by as last_updated
92 from irc_offers iof
93 ,per_all_vacancies vac
94 ,per_jobs_vl job
95 ,per_all_assignments_f asg
96 ,per_all_people_f ppf
97 ,irc_posting_contents_vl ipc
98 where
99 iof.offer_status = 'CLOSED'
100 and iof.vacancy_id = vac.vacancy_id
101 and vac.job_id = job.job_id(+)
102 and asg.assignment_id = iof.offer_assignment_id
103 and asg.effective_start_date = (select max(effective_start_date)
104 from per_assignments_f asg2 where
105 asg.assignment_id=asg2.assignment_id
106 and asg2.effective_start_date <= trunc(sysdate))
107 and trunc(sysdate) between ppf.effective_start_date
108 and ppf.effective_end_date
109 and ppf.person_id = asg.person_id
110 and iof.offer_id = p_offer_id
111 and ipc.posting_content_id(+) = vac.primary_posting_id;
112
113 -- ***************************************************************************
114 -- Cursor to find offers which are about to expire in p_number_of_days
115 -- ***************************************************************************
116 CURSOR csr_get_expiry_offer_rec
117 (p_number_of_days in number)
118 IS
119 select iof.offer_id
120 ,nvl((iof.offer_extended_method),
121 (fnd_profile.VALUE('IRC_OFFER_SEND_METHOD'))) extended_method
122 ,iof.applicant_assignment_id
123 ,vac.name as vacancy_name
124 ,vac.manager_id as manager_id
125 ,job.name as job_title
126 ,asg.person_id as applicant_id
127 ,ppf.full_name as applicant_name
128 ,iof.expiry_date
129 ,iof.created_by as creator_id
130 from irc_offers iof
131 ,per_all_vacancies vac
132 ,per_jobs_vl job
133 ,per_all_assignments_f asg
134 ,per_all_people_f ppf
135 where
136 iof.offer_status = 'EXTENDED'
137 and iof.latest_offer = 'Y'
138 and iof.vacancy_id = vac.vacancy_id
139 and vac.job_id = job.job_id(+)
140 and asg.assignment_id = iof.applicant_assignment_id
141 and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
142 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
143 and ppf.person_id = asg.person_id
144 and (iof.expiry_date between trunc(sysdate) + 0 and
145 trunc(sysdate) + p_number_of_days);
146
147 -- ***************************************************************************
148 -- Cursor to find offers which are expired in the past day
149 -- ***************************************************************************
150
151 CURSOR csr_get_expired_offer_rec
152 IS
153 select iof.offer_id
154 ,vac.name as vacancy_name
155 ,vac.manager_id as manager_id
156 ,job.name as job_title
157 ,asg.person_id as applicant_id
158 ,ppf.full_name as applicant_name
159 ,iof.created_by as creator_id
160 from irc_offers iof
161 ,per_all_vacancies vac
162 ,per_jobs_vl job
163 ,per_all_assignments_f asg
164 ,per_all_people_f ppf
165 where
166 iof.vacancy_id = vac.vacancy_id
167 and iof.offer_status = 'EXTENDED'
168 and iof.latest_offer = 'Y'
169 and vac.job_id = job.job_id(+)
170 and asg.assignment_id = iof.applicant_assignment_id
171 and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
172 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
173 and ppf.person_id = asg.person_id
174 and iof.expiry_date < trunc(sysdate) + 0;
175
176 -- ***************************************************************************
177 -- Cursor to get offer details for the given offer id
178 -- ***************************************************************************
179 CURSOR csr_onhold_offer
180 (p_offer_id IN number)
181 IS
182 select vac.name as vacancy_name
183 ,iof.created_by as creator_id
184 ,job.name as job_title
185 ,ppf.full_name as applicant_name
186 from irc_offers iof
187 ,per_all_vacancies vac
188 ,per_jobs_vl job
189 ,per_all_assignments_f asg
190 ,per_all_people_f ppf
191 where
192 iof.offer_status = 'HOLD'
193 and iof.vacancy_id = vac.vacancy_id
194 and vac.job_id = job.job_id(+)
195 and asg.assignment_id = iof.applicant_assignment_id
196 and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
197 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
198 and ppf.person_id = asg.person_id
199 and iof.offer_id = p_offer_id;
200
201 CURSOR csr_get_user_name
202 (p_user_id IN number)
203 IS
204 select
205 usr.user_name
206 from
207 fnd_user usr
208 where usr.user_id = p_user_id;
209
210 -- ----------------------------------------------------------------------------
211 -- FUNCTIONS
212 -- ----------------------------------------------------------------------------
213
214 --
215 -- -------------------------------------------------------------------------
216 -- |-----------------------< get_view_offer_url >--------------------------|
217 -- -------------------------------------------------------------------------
218 --
219 FUNCTION get_view_offer_url
220 ( p_person_id number
221 ,p_apl_asg_id number)
222 RETURN varchar2
223 IS
224 l_url varchar2(4000);
225 l_apps_fwk_agent varchar2(2000);
226 BEGIN
227 if (irc_utilities_pkg.is_internal_person(p_person_id,trunc(sysdate))='TRUE') then
228 l_apps_fwk_agent := rtrim(fnd_profile.value_specific('APPS_FRAMEWORK_AGENT')
229 ||fnd_profile.value('ICX_PREFIX'),'/');
230 else
231 l_apps_fwk_agent := rtrim(nvl(fnd_profile.value('IRC_FRAMEWORK_AGENT'),
232 fnd_profile.value('APPS_FRAMEWORK_AGENT'))
233 ||fnd_profile.value('ICX_PREFIX'),'/');
234 end if;
235
236 l_url:=l_apps_fwk_agent
237 ||'/OA_HTML/OA.jsp?OAFunc='
238 ||fnd_profile.value('IRC_VIEW_OFFER_DETAILS_FUNC')
239 ||'&addBreadCrumb=Y'
240 ||'&retainAM=Y&p_aplid='||to_char(p_apl_asg_id);
241 RETURN l_url;
242 END get_view_offer_url;
243 --
244 -- -------------------------------------------------------------------------
245 -- |------------------< get_extend_offer_duration_url >--------------------|
246 -- -------------------------------------------------------------------------
247 --
248 FUNCTION get_extend_offer_duration_url
249 ( p_person_id number
250 , p_offer_id number)
251 RETURN varchar2
252 IS
253 l_url varchar2(4000);
254 l_apps_fwk_agent varchar2(2000);
255 BEGIN
256 l_apps_fwk_agent := rtrim(fnd_profile.value_specific('APPS_FRAMEWORK_AGENT')
257 ||fnd_profile.value('ICX_PREFIX'),'/');
258
259 l_url:=l_apps_fwk_agent
260 ||'/OA_HTML/OA.jsp?OAFunc='
261 ||fnd_profile.value('IRC_EXTEND_OFFER_DURATION_FUNC')
262 ||'&addBreadCrumb=Y'
263 ||'&retainAM=Y&p_sofferid='||to_char(p_offer_id);
264 RETURN l_url;
265 END get_extend_offer_duration_url;
266
267
268 --
269 -- ----------------------------------------------------------------------------
270 -- send_rcvd_wf_notification --
271 -- called internally to send offer received notification : --
272 -- sends the notification to applicant and manager --
273 -- ----------------------------------------------------------------------------
274 --
275 PROCEDURE send_rcvd_wf_notification(itemtype in varchar2,
276 itemkey in varchar2,
277 actid in number,
278 funcmode in varchar2,
279 resultout out nocopy varchar2) is
280 --
281
282
283 Event wf_event_t;
284 EventDocument CLOB;
285 l_note CLOB;
286
287 l_id number;
288 l_applicant_id number;
289 l_manager_id number;
290 l_offer_id number;
291 l_apl_asg_id number;
292 l_creator_id number;
293 l_begining number;
294 l_end number := 1;
295 l_offer_status_history_id number;
296
297 l_note_text varchar2(32767);
298 l_creator_name varchar2(4000);
299 l_vacancy_name varchar2(4000);
300 l_job_title varchar2(4000);
301 l_offer_status varchar2(4000);
302 l_applicant_name varchar2(4000);
303 l_url varchar2(4000);
304
305 l_subject varchar2(15599);
306 l_html_body varchar2(15599);
307 l_text_body varchar2(15599);
308 l_proc varchar2(30) default '.send_rcvd_wf_notification';
309 --
310 cursor csr_offer_notes (p_offer_status_history_id IN NUMBER) is
311 select note.note_text
312 from irc_notes note
313 where note.offer_status_history_id = p_offer_status_history_id
314 order by creation_date desc;
315
316 --
317 BEGIN
318 --
319 hr_utility.set_location(l_proc, 10);
320 IF (funcmode='RUN') THEN
321
322 hr_utility.set_location(l_proc, 20);
323
324 -- get the event name
325 Event:=wf_engine.getActivityAttrEvent
326 (itemtype => itemtype
327 ,itemkey => itemkey
328 ,actid => actid
329 ,name => 'EVENT');
330
331 -- get event data
332 EventDocument:=Event.getEventData();
333 l_offer_id:= to_number(irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_id'));
334 l_offer_status:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_status');
335 l_offer_status_history_id:=to_number(irc_xml_util.valueOf(EventDocument,
336 '/offer_status_history/offer_status_history_id'));
337
338 -- only when the new row is inserted with 'EXTENDED' offer status in irc_offer_status_history table,
339 -- proceed further
340 IF ( l_offer_status = 'EXTENDED') THEN
341
342 hr_utility.set_location(l_proc, 30);
343
344 -- get the note entered when extending the offer
345 open csr_offer_notes(l_offer_status_history_id);
346 fetch csr_offer_notes into l_note;
347 if csr_offer_notes%found then
348 --
349 -- convert clob data to varchar2
350 --
351 l_begining := DBMS_LOB.GETLENGTH(l_note);
352 DBMS_LOB.READ(l_note, l_begining, l_end, l_note_text);
353 end if;
354 close csr_offer_notes;
355
356 open csr_send_offer_rcvd(l_offer_id);
357 fetch csr_send_offer_rcvd into l_apl_asg_id,l_vacancy_name,l_manager_id,
358 l_job_title,l_applicant_id,l_applicant_name,l_creator_id;
359 close csr_send_offer_rcvd;
360
361 -- get the user name for creator
362 open csr_get_user_name(l_creator_id);
363 fetch csr_get_user_name into l_creator_name;
364 close csr_get_user_name;
365
366 hr_utility.set_location(l_proc, 40);
367
368 -- get the view offer letter url
369 -- pass the applicant assignment id because drilldown used in application details page uses it
370 l_url := get_view_offer_url
371 (p_person_id => l_applicant_id
372 ,p_apl_asg_id => l_apl_asg_id);
373
374
375 -- get the employee id for the applicant
376 -- this is required to send notification
377 open csr_get_employee_id(l_applicant_id);
378 fetch csr_get_employee_id into l_applicant_id;
379 close csr_get_employee_id;
380
381
382 hr_utility.set_location(l_proc, 50);
383
384 -- build subject message
385 fnd_message.set_name('PER','IRC_OFFER_RECEIVED_APL_SUBJECT');
386 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
387 fnd_message.set_token('JOB_TITLE',l_job_title, false);
388 l_subject := fnd_message.get;
389
390 hr_utility.set_location(l_proc, 60);
391
392 -- build html body
393 fnd_message.set_name('PER','IRC_OFFER_RECEIVED_APL_HTML');
394 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
395 fnd_message.set_token('JOB_TITLE',l_job_title, false);
396 l_html_body := fnd_message.get;
397
398 --
399
400 l_html_body := l_html_body
401 || '<BR>' || l_note_text;
402
403 if (fnd_profile.value('IRC_OFFER_SEND_METHOD')='SYSTEM') then
404 l_html_body := l_html_body
405 || '<BR><BR><a HREF="'||l_url
406 || '">'
407 || 'View Offer'
408 || '</a>'
409 || '<BR>';
410 end if;
411
412 hr_utility.set_location(l_proc, 70);
413
414 -- build text body
415 --
416 fnd_message.set_name('PER','IRC_OFFER_RECEIVED_APL_TEXT');
417 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
418 fnd_message.set_token('JOB_TITLE',l_job_title, false);
419 l_text_body := fnd_message.get;
420
421 --
422 l_text_body := l_text_body
423 || '\n' || l_note_text;
424
425 if (fnd_profile.value('IRC_OFFER_SEND_METHOD')='SYSTEM') then
426 l_text_body := l_text_body
427 || '\n\n'||'View Offer'
428 || '\n'||l_url
429 || '\n';
430 end if;
431
432 --
433 hr_utility.set_location(l_proc, 80);
434
435 -- send notification
436 l_id := irc_notification_helper_pkg.send_notification
437 ( p_person_id => l_applicant_id
438 , p_subject => l_subject
439 , p_html_body => l_html_body
440 , p_text_body => l_text_body
441 , p_from_role => l_creator_name
442 );
443
444 hr_utility.set_location(l_proc, 90);
445
446 -- now send the notification to manager also
447 IF( l_manager_id is not null) THEN
448 --
449 hr_utility.set_location(l_proc, 100);
450
451 -- get the employee id for manager
452 open csr_get_employee_id(l_manager_id);
453 fetch csr_get_employee_id into l_manager_id;
454 close csr_get_employee_id;
455
456 -- build subject message
457 fnd_message.set_name('PER','IRC_OFFER_SENT_MGR_SUBJECT');
458 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
459 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
460 l_subject := fnd_message.get;
461
462 hr_utility.set_location(l_proc, 110);
463
464
465 -- build html body
466 fnd_message.set_name('PER','IRC_OFFER_SENT_MGR_HTML');
467 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
468 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
469 l_html_body := fnd_message.get || '<BR>' || l_note_text;
470
471 --
472 hr_utility.set_location(l_proc, 120);
473
474 -- build text body
475 --
476 fnd_message.set_name('PER','IRC_OFFER_SENT_MGR_TEXT');
477 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
478 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
479 l_text_body := fnd_message.get || '\n' || l_note_text;
480
481 --
482 hr_utility.set_location(l_proc, 130);
483
484 -- send notification
485 l_id := irc_notification_helper_pkg.send_notification
486 ( p_person_id => l_manager_id
487 , p_subject => l_subject
488 , p_html_body => l_html_body
489 , p_text_body => l_text_body
490 , p_from_role => l_creator_name
491 );
492 hr_utility.set_location(l_proc, 140);
493
494 --
495 END IF;
496 END IF;
497 --
498 END IF;
499 resultout:='COMPLETE';
500 hr_utility.set_location(' Leaving:'||l_proc, 150);
501 --
502 END send_rcvd_wf_notification;
503 --
504 --
505 -- ----------------------------------------------------------------------------
506 -- send_expiry_notification --
507 -- called from concurrent process to send offer expiry notification : --
508 -- sends the notification to applicant and manager --
509 -- ----------------------------------------------------------------------------
510 --
511 PROCEDURE send_expiry_notification
512 ( errbuf out nocopy varchar2
513 , retcode out nocopy number
514 , p_number_of_days in number)
515 is
516 --
517 l_id number;
518 l_applicant_id number;
519 l_apl_asg_id number;
520 l_manager_id number;
521 l_creator_name varchar2(4000);
522
523 l_subject varchar2(240);
524 l_html_body varchar2(32000);
525 l_text_body varchar2(32000);
526 l_url varchar2(4000);
527 l_proc varchar2(30) default '.send_expiry_notification';
528 l_extend_method varchar2(30);
529 l_offer_sent_date date;
530
531
532 --
533 BEGIN
534 --
535 hr_utility.set_location('Entering'||l_proc, 10);
536 -- Loop through all the job seekers and send them
537 -- a general notification.
538 for offer_expiry_rec in csr_get_expiry_offer_rec
539 (p_number_of_days => p_number_of_days)loop
540 l_apl_asg_id := offer_expiry_rec.applicant_assignment_id;
541 -- build subject message
542 fnd_message.set_name('PER','IRC_OFFER_EXPIRY_SUBJECT');
543 fnd_message.set_token('DAY_OF_EXPIRY',offer_expiry_rec.expiry_date, false);
544 fnd_message.set_token('VACANCY_NAME',offer_expiry_rec.vacancy_name, false);
545 fnd_message.set_token('JOB_TITLE',offer_expiry_rec.job_title, false);
546 l_subject := fnd_message.get;
547
548 --get offer sent date
549 open csr_get_offer_sent_date(offer_expiry_rec.offer_id);
550 fetch csr_get_offer_sent_date into l_offer_sent_date;
551 close csr_get_offer_sent_date;
552
553 --get the user name for creator
554 open csr_get_user_name(offer_expiry_rec.creator_id);
555 fetch csr_get_user_name into l_creator_name;
556 close csr_get_user_name;
557
558 hr_utility.set_location(l_proc,20);
559
560 -- get offer view url
561 l_url := get_view_offer_url
562 (p_person_id => offer_expiry_rec.applicant_id
563 ,p_apl_asg_id => l_apl_asg_id);
564
565
566 -- get the employee id for applicant
567 open csr_get_employee_id(offer_expiry_rec.applicant_id);
568 fetch csr_get_employee_id into l_applicant_id;
569 close csr_get_employee_id;
570
571 hr_utility.set_location(l_proc,30);
572
573
574
575 --
576 -- Build the body of the message both in text and html
577 --
578
579 -- build html body
580 fnd_message.set_name('PER','IRC_OFFER_EXPIRY_APL_HTML');
581 fnd_message.set_token('SENT_DATE',l_offer_sent_date, false);
582 fnd_message.set_token('DAY_OF_EXPIRY',offer_expiry_rec.expiry_date, false);
583 l_html_body := fnd_message.get;
584
585 --
586 l_extend_method := offer_expiry_rec.extended_method;
587 if (l_extend_method='SYSTEM') then
588 l_html_body := l_html_body
589 || '<BR><a HREF="'||l_url
590 || '">'
591 || 'View Offer'
592 || '</a>'
593 || '<BR>';
594 end if;
595 --
596 hr_utility.set_location(l_proc,40);
597
598 -- build text body
599
600 fnd_message.set_name('PER','IRC_OFFER_EXPIRY_APL_TEXT');
601 fnd_message.set_token('SENT_DATE',l_offer_sent_date, false);
602 fnd_message.set_token('DAY_OF_EXPIRY',offer_expiry_rec.expiry_date, false);
603 l_text_body := fnd_message.get;
604
605 --
606 if (l_extend_method='SYSTEM') then
607 l_text_body := l_text_body
608 || '\n'||'View Offer'
609 || '\n'||l_url
610 || '\n';
611 end if;
612 --
613 hr_utility.set_location(l_proc,50);
614
615 -- send notification to applicant
616 l_id := irc_notification_helper_pkg.send_notification
617 ( p_person_id => l_applicant_id
618 , p_subject => l_subject
619 , p_html_body => l_html_body
620 , p_text_body => l_text_body
621 , p_from_role => l_creator_name
622 );
623 hr_utility.set_location(l_proc,60);
624
625 -- send notification to manager
626 IF ( offer_expiry_rec.manager_id is not null) THEN
627
628 hr_utility.set_location(l_proc,70);
629
630 --get the employee id for manager
631 open csr_get_employee_id(offer_expiry_rec.manager_id);
632 fetch csr_get_employee_id into l_manager_id;
633 close csr_get_employee_id;
634
635 hr_utility.set_location(l_proc,80);
636
637 -- get extend offer duration url
638 l_url := get_extend_offer_duration_url
639 (p_person_id => l_manager_id
640 ,p_offer_id => offer_expiry_rec.offer_id);
641
642
643 hr_utility.set_location(l_proc,90);
644
645 --
646 -- Build the body of the message both in text and html
647 --
648
649 -- build html body
650 fnd_message.set_name('PER','IRC_OFFER_EXPIRY_MGR_HTML');
651 fnd_message.set_token('APPLICANT_NAME',offer_expiry_rec.applicant_name, false);
652 fnd_message.set_token('VACANCY_NAME',offer_expiry_rec.vacancy_name, false);
653 fnd_message.set_token('DAY_OF_EXPIRY',offer_expiry_rec.expiry_date, false);
654 l_html_body := fnd_message.get;
655 --
656 l_html_body := l_html_body
657 || '<BR><a HREF="'||l_url
658 || '">'
659 || 'Extend Duration'
660 || '</a>'
661 || '<BR>';
662 --
663 hr_utility.set_location(l_proc,100);
664
665 -- build text body
666
667 fnd_message.set_name('PER','IRC_OFFER_EXPIRY_MGR_TEXT');
668 fnd_message.set_token('APPLICANT_NAME',offer_expiry_rec.applicant_name, false);
669 fnd_message.set_token('VACANCY_NAME',offer_expiry_rec.vacancy_name, false);
670 fnd_message.set_token('DAY_OF_EXPIRY',offer_expiry_rec.expiry_date, false);
671 l_text_body := fnd_message.get;
672
673 --
674 l_text_body := l_text_body
675 || '\n'||'Extend Duration'
676 || '\n'||l_url
677 || '\n';
678 --
679 hr_utility.set_location(l_proc,110);
680
681 l_id := irc_notification_helper_pkg.send_notification
682 ( p_person_id => l_manager_id
683 , p_subject => l_subject
684 , p_html_body => l_html_body
685 , p_text_body => l_text_body
686 , p_from_role => l_creator_name
687 );
688 END IF;
689
690 hr_utility.set_location(l_proc,120);
691
692
693 end loop;
694 hr_utility.set_location(' Leaving:'||l_proc, 130);
695
696 --
697 END send_expiry_notification;
698 --
699 --
700 --
701 -- ----------------------------------------------------------------------------
702 -- send_expired_notification --
703 -- called from concurrent process to send offer expired notification : --
704 -- sends the notification to applicant and manager --
705 -- ----------------------------------------------------------------------------
706 --
707 PROCEDURE send_expired_notification
708 ( errbuf out nocopy varchar2
709 , retcode out nocopy number)
710 is
711 --
712 l_id number;
713 l_applicant_id number;
714 l_manager_id number;
715 l_creator_name varchar2(4000);
716
717 l_subject varchar2(240);
718 l_html_body varchar2(32000);
719 l_text_body varchar2(32000);
720 l_url varchar2(4000);
721 l_proc varchar2(30) default '.send_expired_notification';
722 l_offer_sent_date date;
723
724 --
725 BEGIN
726 --
727 hr_utility.set_location('Entering'||l_proc, 10);
728 -- Loop through all the job seekers and send them
729 -- a general notification.
730 for offer_expired_rec in csr_get_expired_offer_rec loop
731
732 -- build subject message
733 fnd_message.set_name('PER','IRC_OFFER_EXPIRED_SUBJECT');
734 fnd_message.set_token('VACANCY_NAME',offer_expired_rec.vacancy_name, false);
735 fnd_message.set_token('JOB_TITLE',offer_expired_rec.job_title, false);
736 l_subject := fnd_message.get;
737
738 --get offer sent date
739 open csr_get_offer_sent_date(offer_expired_rec.offer_id);
740 fetch csr_get_offer_sent_date into l_offer_sent_date;
741 close csr_get_offer_sent_date;
742
743 --get the user name for creator
744 open csr_get_user_name(offer_expired_rec.creator_id);
745 fetch csr_get_user_name into l_creator_name;
746 close csr_get_user_name;
747
748 hr_utility.set_location(l_proc,20);
749
750 -- get the person id
751 open csr_get_employee_id(offer_expired_rec.applicant_id);
752 fetch csr_get_employee_id into l_applicant_id;
753 close csr_get_employee_id;
754
755
756 hr_utility.set_location(l_proc,30);
757
758 --
759 -- Build the body of the message both in text and html
760 --
761
762 -- build html body
763 fnd_message.set_name('PER','IRC_OFFER_EXPIRED_APL_HTML');
764 fnd_message.set_token('SENT_DATE',l_offer_sent_date, false);
765 l_html_body := fnd_message.get;
766
767 --
768 hr_utility.set_location(l_proc,40);
769
770 -- build text body
771 fnd_message.set_name('PER','IRC_OFFER_EXPIRED_APL_TEXT');
772 fnd_message.set_token('SENT_DATE',l_offer_sent_date, false);
773 l_text_body := fnd_message.get;
774
775 --
776 hr_utility.set_location(l_proc,50);
777
778 -- send notification
779 l_id := irc_notification_helper_pkg.send_notification
780 ( p_person_id => l_applicant_id
781 , p_subject => l_subject
782 , p_html_body => l_html_body
783 , p_text_body => l_text_body
784 , p_from_role => l_creator_name
785 );
786 hr_utility.set_location(l_proc,60);
787
788 -- send notification to manager
789 IF ( offer_expired_rec.manager_id is not null) THEN
790 --
791 hr_utility.set_location(l_proc,70);
792
793 --get the employee id for manager
794 open csr_get_employee_id(offer_expired_rec.manager_id);
795 fetch csr_get_employee_id into l_manager_id;
796 close csr_get_employee_id;
797 --
798 -- Build the body of the message both in text and html
799 --
800 -- get extend offer duration url
801 l_url := get_extend_offer_duration_url
802 (p_person_id => l_manager_id
803 ,p_offer_id => offer_expired_rec.offer_id);
804
805
806 -- build html body
807 fnd_message.set_name('PER','IRC_OFFER_EXPIRED_MGR_HTML');
808 fnd_message.set_token('APPLICANT_NAME',offer_expired_rec.applicant_name, false);
809 fnd_message.set_token('VACANCY_NAME',offer_expired_rec.vacancy_name, false);
810 l_html_body := fnd_message.get;
811
812 --
813 l_html_body := l_html_body
814 || '<BR><a HREF="'||l_url
815 || '">'
816 || 'Extend Duration'
817 || '</a>'
818 || '<BR>';
819 --
820 hr_utility.set_location(l_proc,80);
821
822 -- build text body
823 fnd_message.set_name('PER','IRC_OFFER_EXPIRED_MGR_TEXT');
824 fnd_message.set_token('APPLICANT_NAME',offer_expired_rec.applicant_name, false);
825 fnd_message.set_token('VACANCY_NAME',offer_expired_rec.vacancy_name, false);
826 l_text_body := fnd_message.get;
827
828 --
829 l_text_body := l_text_body
830 || '\n'||'Extend Duration'
831 || '\n'||l_url
832 || '\n';
833 --
834 hr_utility.set_location(l_proc,90);
835
836 l_id := irc_notification_helper_pkg.send_notification
837 ( p_person_id => l_manager_id
838 , p_subject => l_subject
839 , p_html_body => l_html_body
840 , p_text_body => l_text_body
841 , p_from_role => l_creator_name
842 );
843 hr_utility.set_location(l_proc,100);
844 --
845 END IF;
846
847 -- Set the offer status to 'CLOSED' for the expired offers
848 irc_offers_api.close_offer
849 ( p_validate => false
850 ,p_effective_date => trunc(sysdate)
851 ,p_offer_id => offer_expired_rec.offer_id
852 ,p_change_reason => 'EXPIRED'
853 ,p_status_change_date => trunc(sysdate)
854 );
855
856 hr_utility.set_location(l_proc,110);
857
858 end loop;
859 hr_utility.set_location(' Leaving:'||l_proc, 120);
860 --
861 END send_expired_notification;
862 --
863 -- ----------------------------------------------------------------------------
864 -- send_applicant_response --
865 -- called internally to send notification about applicant response : --
866 -- sends the notification to applicant and manager --
867 -- ----------------------------------------------------------------------------
868 --
869 PROCEDURE send_applicant_response(itemtype in varchar2,
870 itemkey in varchar2,
871 actid in number,
872 funcmode in varchar2,
873 resultout out nocopy varchar2) is
874 --
875 Event wf_event_t;
876 EventDocument CLOB;
877 l_note CLOB;
878
879 l_id number;
880 l_applicant_id number;
881 l_offer_id number;
882 l_manager_id number;
883 l_recruiter_id number;
884 l_creator_id number;
885 l_referrer_id number;
886 l_begining number;
887 l_user_id number;
888 l_end number := 1;
889 l_offer_status_history_id number;
890 l_creator_name varchar2(4000);
891
892 l_apl_response boolean := false;
893 l_apl_accepted boolean := false;
894
895 l_note_text varchar2(32767);
896 l_vacancy_name varchar2(4000);
897 l_job_title varchar2(4000);
898 l_applicant_name varchar2(4000);
899 l_offer_status varchar2(4000);
900 l_change_reason varchar2(4000);
901 l_decline_reason varchar2(4000);
902 l_decline_reason_meaning varchar2(4000);
903 l_job_posting_title varchar2(4000);
904 l_last_updated_by number;
905
906 l_subject varchar2(15599);
907 l_html_body varchar2(15599);
908 l_text_body varchar2(15599);
909
910 l_proc varchar2(30) default '.send_applicant_response';
911 --
912 cursor csr_offer_notes (p_offer_status_history_id IN NUMBER) is
913 select note.note_text
914 from irc_notes note
915 where note.offer_status_history_id = p_offer_status_history_id
916 order by creation_date desc;
917 --
918 cursor csr_get_decline_reason (p_decline_reason IN VARCHAR2) is
919 select meaning
920 from hr_lookups
921 where lookup_type = 'IRC_OFFER_DECLINE_REASON' and
922 lookup_code = p_decline_reason;
923 --
924 cursor csr_get_apl_referrer_id (p_offer_id in number) is
925 select iri.source_person_id
926 from irc_referral_info iri,
927 irc_offers iof
928 where iri.object_id = iof.APPLICANT_ASSIGNMENT_ID
929 and iri.object_type = 'APPLICATION'
930 and iof.offer_id = p_offer_id;
931 --
932 BEGIN
933 --
934
935 hr_utility.set_location(l_proc, 10);
936 IF (funcmode='RUN') THEN
937
938 hr_utility.set_location(l_proc, 20);
939
940 Event:=wf_engine.getActivityAttrEvent
941 (itemtype => itemtype
942 ,itemkey => itemkey
943 ,actid => actid
944 ,name => 'EVENT');
945
946 EventDocument:=Event.getEventData();
947 l_offer_id:= to_number(irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_id'));
948 l_offer_status:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_status');
949 l_change_reason:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/change_reason');
950 l_offer_status_history_id:=to_number(irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_status_history_id'));
951
952
953 IF ( l_offer_status = 'CLOSED' and l_change_reason = 'APL_ACCEPTED') THEN
954 l_apl_response := true;
955 l_apl_accepted := true;
956 ELSIF( l_offer_status = 'CLOSED' and l_change_reason = 'APL_DECLINED') THEN
957 l_apl_response := true;
958 l_apl_accepted := false;
959 ELSE
960 l_apl_response := false;
961 END IF;
962
963 IF ( l_apl_response = true ) THEN
964 --
965 hr_utility.set_location(l_proc, 30);
966
967 open csr_send_apl_resp(l_offer_id);
968 fetch csr_send_apl_resp into l_vacancy_name,l_manager_id,l_recruiter_id,l_creator_id,l_job_title,l_applicant_id,l_applicant_name,l_job_posting_title,l_last_updated_by;
969 close csr_send_apl_resp;
970
971 --get the user name for creator
972 open csr_get_user_name(l_creator_id);
973 fetch csr_get_user_name into l_creator_name;
974 close csr_get_user_name;
975
976 --get the person id for creator
977 open csr_get_user_employee_id(l_creator_id);
978 fetch csr_get_user_employee_id into l_creator_id;
979 close csr_get_user_employee_id;
980
981 hr_utility.set_location(l_proc, 40);
982
983 -- get the person id
984 open csr_get_employee_id(l_applicant_id);
985 fetch csr_get_employee_id into l_applicant_id;
986 close csr_get_employee_id;
987
988 -- get the person id for referrer
989 open csr_get_apl_referrer_id(l_offer_id);
990 fetch csr_get_apl_referrer_id into l_referrer_id;
991 close csr_get_apl_referrer_id;
992
993 hr_utility.set_location(l_proc, 50);
994
995 -- build subject message for applicant
996 fnd_message.set_name('PER','IRC_OFFER_RESPONSE_APL_SUBJECT');
997 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
998 fnd_message.set_token('JOB_TITLE',l_job_title, false);
999 l_subject := fnd_message.get;
1000 --
1001
1002 --
1003 hr_utility.set_location(l_proc, 60);
1004
1005 open csr_offer_notes(l_offer_status_history_id);
1006 fetch csr_offer_notes into l_note;
1007 if csr_offer_notes%found then
1008 --
1009 -- convert clob data to varchar2
1010 --
1011 l_begining := DBMS_LOB.GETLENGTH(l_note);
1012 DBMS_LOB.READ(l_note, l_begining, l_end, l_note_text);
1013 end if;
1014 close csr_offer_notes;
1015
1016 -- build html body for applicant
1017 IF ( l_apl_accepted = true ) THEN
1018 fnd_message.set_name('PER','IRC_OFFER_ACCEPTED_APL_HTML');
1019 ELSE
1020 fnd_message.set_name('PER','IRC_OFFER_REJECTED_APL_HTML');
1021 END IF;
1022
1023 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1024 fnd_message.set_token('JOB_TITLE',l_job_title, false);
1025 l_html_body := fnd_message.get || '<BR>' || l_note_text;
1026
1027 hr_utility.set_location(l_proc, 70);
1028
1029 -- build text body
1030 IF ( l_apl_accepted = true ) THEN
1031 fnd_message.set_name('PER','IRC_OFFER_ACCEPTED_APL_TEXT');
1032 ELSE
1033 fnd_message.set_name('PER','IRC_OFFER_REJECTED_APL_TEXT');
1034 END IF;
1035 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1036 fnd_message.set_token('JOB_TITLE',l_job_title, false);
1037 l_text_body := fnd_message.get || '\n' || l_note_text;
1038
1039
1040 --
1041 hr_utility.set_location(l_proc, 80);
1042
1043 -- send notification
1044 l_id := irc_notification_helper_pkg.send_notification
1045 ( p_person_id => l_applicant_id
1046 , p_subject => l_subject
1047 , p_html_body => l_html_body
1048 , p_text_body => l_text_body
1049 , p_from_role => l_creator_name
1050 );
1051 hr_utility.set_location(l_proc, 90);
1052
1053 --send notification to manager
1054 IF( l_manager_id is not null or l_recruiter_id is not null or l_creator_id is not null) THEN
1055
1056 hr_utility.set_location(l_proc, 100);
1057
1058 IF ( l_apl_accepted = false ) THEN
1059 -- get the decline reason
1060 l_decline_reason:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/decline_reason');
1061 open csr_get_decline_reason(l_decline_reason);
1062 fetch csr_get_decline_reason into l_decline_reason_meaning;
1063 close csr_get_decline_reason;
1064 END IF;
1065
1066 -- build subject message for manager
1067 IF ( l_apl_accepted = true ) THEN
1068 fnd_message.set_name('PER','IRC_OFFER_ACCEPTED_MGR_SUBJECT');
1069 ELSE
1070 fnd_message.set_name('PER','IRC_OFFER_REJECTED_MGR_SUBJECT');
1071 END IF;
1072 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1073 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1074 l_subject := fnd_message.get;
1075
1076 -- build html body for applicant
1077 IF ( l_apl_accepted = true ) THEN
1078 fnd_message.set_name('PER','IRC_OFFER_ACCEPTED_MGR_HTML');
1079 ELSE
1080 fnd_message.set_name('PER','IRC_OFFER_REJECTED_MGR_HTML');
1081 fnd_message.set_token('DECLINE_REASON',l_decline_reason_meaning, false);
1082 END IF;
1083
1084 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1085 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1086 l_html_body := fnd_message.get || '<BR>' || l_note_text;
1087
1088 hr_utility.set_location(l_proc, 110);
1089
1090 -- build text body
1091 IF ( l_apl_accepted = true ) THEN
1092 fnd_message.set_name('PER','IRC_OFFER_ACCEPTED_MGR_TEXT');
1093 ELSE
1094 fnd_message.set_name('PER','IRC_OFFER_REJECTED_MGR_TEXT');
1095 fnd_message.set_token('DECLINE_REASON',l_decline_reason_meaning, false);
1096 END IF;
1097 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1098 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1099 l_text_body := fnd_message.get || '\n' || l_note_text;
1100
1101
1102 --
1103 hr_utility.set_location(l_proc, 120);
1104
1105 -- send notification to manager
1106 IF( l_manager_id is not null ) THEN
1107 l_id := irc_notification_helper_pkg.send_notification
1108 ( p_person_id => l_manager_id
1109 , p_subject => l_subject
1110 , p_html_body => l_html_body
1111 , p_text_body => l_text_body
1112 , p_from_role => l_creator_name
1113 );
1114 hr_utility.set_location(l_proc, 130);
1115 END IF;
1116 -- send notification to recruiter
1117 IF( l_recruiter_id is not null
1118 and (l_manager_id is null or l_recruiter_id <> l_manager_id) ) THEN
1119 l_id := irc_notification_helper_pkg.send_notification
1120 ( p_person_id => l_recruiter_id
1121 , p_subject => l_subject
1122 , p_html_body => l_html_body
1123 , p_text_body => l_text_body
1124 , p_from_role => l_creator_name
1125 );
1126 hr_utility.set_location(l_proc, 140);
1127 END IF;
1128 -- send notification to creator
1129 IF( l_creator_id is not null
1130 and (l_recruiter_id is null or l_creator_id <> l_recruiter_id )
1131 and (l_manager_id is null or l_creator_id <> l_manager_id )) THEN
1132 l_id := irc_notification_helper_pkg.send_notification
1133 ( p_person_id => l_creator_id
1134 , p_subject => l_subject
1135 , p_html_body => l_html_body
1136 , p_text_body => l_text_body
1137 , p_from_role => l_creator_name
1138 );
1139 hr_utility.set_location(l_proc, 150);
1140
1141 END IF;
1142
1143 -- send notification to referrer
1144 IF( l_referrer_id is not null
1145 and l_apl_accepted = true
1146 and (l_creator_id is null or l_referrer_id <> l_creator_id)
1147 and (l_recruiter_id is null or l_referrer_id <> l_recruiter_id)
1148 and (l_manager_id is null or l_referrer_id <> l_manager_id) ) THEN
1149 -- build subject message for referrer
1150 fnd_message.set_name('PER','IRC_412441_OFR_ACCEPT_REF_SUB');
1151 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1152 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1153 l_subject := fnd_message.get;
1154
1155 -- build html body for applicant
1156 fnd_message.set_name('PER','IRC_412442_OFR_ACCEPT_REF_HTML');
1157 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1158 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1159 l_html_body := fnd_message.get;
1160
1161 hr_utility.set_location(l_proc, 155);
1162
1163 -- build text body
1164 fnd_message.set_name('PER','IRC_412509_OFR_ACCEPT_REF_TEXT');
1165 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1166 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1167 l_text_body := fnd_message.get;
1168
1169 l_id := irc_notification_helper_pkg.send_notification
1170 ( p_person_id => l_referrer_id
1171 , p_subject => l_subject
1172 , p_html_body => l_html_body
1173 , p_text_body => l_text_body
1174 , p_from_role => l_creator_name
1175 );
1176 end if;
1177 --
1178 END IF;
1179 END IF;
1180 --
1181 END IF;
1182 resultout:='COMPLETE';
1183 hr_utility.set_location(' Leaving:'||l_proc, 160);
1184 --
1185 END send_applicant_response;
1186 --
1187 -- ----------------------------------------------------------------------------
1188 -- send_applicant_response --
1189 -- called internally to send notification about applicant response : --
1190 -- sends the notification to applicant and manager --
1191 -- ----------------------------------------------------------------------------
1192 --
1193 PROCEDURE send_onhold_notification(itemtype in varchar2,
1194 itemkey in varchar2,
1195 actid in number,
1196 funcmode in varchar2,
1197 resultout out nocopy varchar2) is
1198 --
1199 Event wf_event_t;
1200 EventDocument CLOB;
1201
1202 l_id number;
1203 l_offer_id number;
1204 l_creator_id number;
1205
1206 l_vacancy_name varchar2(4000);
1207 l_job_title varchar2(4000);
1208 l_applicant_name varchar2(4000);
1209 l_offer_status varchar2(4000);
1210 l_creator_name varchar2(4000);
1211
1212 l_subject varchar2(15599);
1213 l_html_body varchar2(15599);
1214 l_text_body varchar2(15599);
1215
1216 l_proc varchar2(30) default '.send_onhold_notification';
1217 --
1218 BEGIN
1219 --
1220
1221 hr_utility.set_location(l_proc, 10);
1222 IF (funcmode='RUN') THEN
1223
1224 hr_utility.set_location(l_proc, 20);
1225
1226 Event:=wf_engine.getActivityAttrEvent
1227 (itemtype => itemtype
1228 ,itemkey => itemkey
1229 ,actid => actid
1230 ,name => 'EVENT');
1231
1232 EventDocument:=Event.getEventData();
1233 l_offer_id:= to_number(irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_id'));
1234 l_offer_status:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_status');
1235
1236
1237 hr_utility.set_location(l_proc, 30);
1238 open csr_onhold_offer(l_offer_id);
1239 fetch csr_onhold_offer into l_vacancy_name,l_creator_id,l_job_title,l_applicant_name;
1240 close csr_onhold_offer;
1241
1242 --get the user name for creator
1243 open csr_get_user_name(l_creator_id);
1244 fetch csr_get_user_name into l_creator_name;
1245 close csr_get_user_name;
1246
1247 --get the person id for creator
1248 open csr_get_user_employee_id(l_creator_id);
1249 fetch csr_get_user_employee_id into l_creator_id;
1250 close csr_get_user_employee_id;
1251
1252 hr_utility.set_location(l_proc, 40);
1253
1254 --send notification to manager
1255 IF(l_creator_id is not null) THEN
1256
1257 hr_utility.set_location(l_proc, 50);
1258
1259 -- build subject message for manager
1260 fnd_message.set_name('PER','IRC_OFFER_ONHOLD_MGR_SUBJECT');
1261 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1262 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1263 l_subject := fnd_message.get;
1264
1265 -- build html body for applicant
1266 fnd_message.set_name('PER','IRC_OFFER_ONHOLD_MGR_HTML');
1267 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1268 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1269 l_html_body := fnd_message.get;
1270
1271 -- build text body
1272 fnd_message.set_name('PER','IRC_OFFER_ONHOLD_MGR_TEXT');
1273 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1274 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1275 l_text_body := fnd_message.get;
1276
1277 --
1278 hr_utility.set_location(l_proc, 60);
1279
1280 -- send notification to creator
1281 IF( l_creator_id is not null ) THEN
1282 l_id := irc_notification_helper_pkg.send_notification
1283 ( p_person_id => l_creator_id
1284 , p_subject => l_subject
1285 , p_html_body => l_html_body
1286 , p_text_body => l_text_body
1287 , p_from_role => l_creator_name
1288 );
1289 hr_utility.set_location(l_proc, 70);
1290
1291 END IF;
1292 --
1293 END IF;
1294
1295 END IF;
1296 resultout:='COMPLETE';
1297 hr_utility.set_location(' Leaving:'||l_proc, 90);
1298 --
1299 END send_onhold_notification;
1300 -- ----------------------------------------------------------------------------
1301 -- send_withdrawal_notification --
1302 -- called internally to send notification about offer withdrawal : --
1303 -- sends the notification to applicant and manager/recruiter --
1304 -- ----------------------------------------------------------------------------
1305 --
1306 PROCEDURE send_withdrawal_notification(itemtype in varchar2,
1307 itemkey in varchar2,
1308 actid in number,
1309 funcmode in varchar2,
1310 resultout out nocopy varchar2) is
1311 --
1312 Event wf_event_t;
1313 EventDocument CLOB;
1314 l_note CLOB;
1315
1316 l_id number;
1317 l_applicant_id number;
1318 l_offer_id number;
1319 l_manager_id number;
1320 l_recruiter_id number;
1321 l_creator_id number;
1322 l_begining number;
1323 l_user_id number;
1324 l_end number := 1;
1325 l_offer_status_history_id number;
1326 l_creator_name varchar2(4000);
1327 l_action_performer varchar2(4000);
1328 l_last_updated_by number;
1329 l_last_updated_emp_id number;
1330 l_mgr_withdraw boolean := false;
1331
1332 l_note_text varchar2(32767);
1333 l_vacancy_name varchar2(4000);
1334 l_job_title varchar2(4000);
1335 l_applicant_name varchar2(4000);
1336 l_offer_status varchar2(4000);
1337 l_change_reason varchar2(4000);
1338 l_prev_change_reason varchar2(4000);
1339 l_prev_offer_status varchar2(4000);
1340 l_withdrawal_reason varchar2(4000);
1341 l_withdrawal_reason_meaning varchar2(4000);
1342 l_hiring_manager varchar2(4000);
1343 l_recruiter varchar2(4000);
1344 l_job_posting_title varchar2(4000);
1345
1346 l_subject varchar2(15599);
1347 l_html_body varchar2(15599);
1348 l_text_body varchar2(15599);
1349
1350 l_proc varchar2(30) default '.send_withdrawal_notification';
1351 --
1352 cursor csr_offer_notes (p_offer_status_history_id IN NUMBER) is
1353 select note.note_text
1354 from irc_notes note
1355 where note.offer_status_history_id = p_offer_status_history_id
1356 order by creation_date desc;
1357 --
1358 cursor csr_get_withdrawal_reason (p_withdrawal_reason IN VARCHAR2) is
1359 select meaning
1360 from hr_lookups
1361 where lookup_type = 'IRC_OFFER_WITHDRAWAL_REASON' and
1362 lookup_code = p_withdrawal_reason;
1363 --
1364 cursor csr_get_prev_offer_details (p_offer_status_history_id IN NUMBER,p_offer_id IN NUMBER) is
1365 select offer_status,change_reason
1366 from irc_offer_status_history
1367 where offer_id = p_offer_id
1368 and status_change_date = (select max(status_change_date)
1369 from irc_offer_status_history
1370 where offer_id = p_offer_id
1371 and offer_status_history_id <> p_offer_status_history_id);
1372 --
1373 CURSOR csr_get_name
1374 (p_person_id IN number)
1375 IS
1376 select ppf.full_name
1377 from per_all_people_f ppf
1378 where ppf.person_id = p_person_id
1379 and sysdate between effective_start_date and effective_end_date;
1380 --
1381
1382 --
1383 BEGIN
1384 --
1385
1386 hr_utility.set_location(l_proc, 10);
1387 IF (funcmode='RUN') THEN
1388
1389 hr_utility.set_location(l_proc, 20);
1390
1391 Event:=wf_engine.getActivityAttrEvent
1392 (itemtype => itemtype
1393 ,itemkey => itemkey
1394 ,actid => actid
1395 ,name => 'EVENT');
1396
1397 EventDocument:=Event.getEventData();
1398 l_offer_id:= to_number(irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_id'));
1399 l_offer_status:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_status');
1400 l_change_reason:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/change_reason');
1401 l_offer_status_history_id:=to_number(irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_status_history_id'));
1402
1403 open csr_get_prev_offer_details(l_offer_status_history_id,l_offer_id);
1404 fetch csr_get_prev_offer_details into l_prev_offer_status,l_prev_change_reason;
1405 close csr_get_prev_offer_details;
1406
1407
1408 IF ( l_offer_status = 'CLOSED' and l_change_reason = 'MGR_WITHDRAW') THEN
1409 l_mgr_withdraw := true;
1410 ELSE
1411 l_mgr_withdraw := false;
1412 END IF;
1413
1414 IF ( l_mgr_withdraw = true ) THEN
1415 --
1416 hr_utility.set_location(l_proc, 30);
1417
1418 open csr_send_apl_resp(l_offer_id);
1419 fetch csr_send_apl_resp into l_vacancy_name,l_manager_id,l_recruiter_id,l_creator_id,l_job_title,l_applicant_id,l_applicant_name,l_job_posting_title,l_last_updated_by;
1420 close csr_send_apl_resp;
1421
1422 --get the user name for creator
1423 open csr_get_user_name(l_creator_id);
1424 fetch csr_get_user_name into l_creator_name;
1425 close csr_get_user_name;
1426
1427 -- get the recruiter name
1428 open csr_get_name(l_recruiter_id);
1429 fetch csr_get_name into l_recruiter;
1430 close csr_get_name;
1431
1432 -- get the manager name
1433 open csr_get_name(l_manager_id);
1434 fetch csr_get_name into l_hiring_manager;
1435 close csr_get_name;
1436
1437 --get the person id for creator
1438 open csr_get_user_employee_id(l_creator_id);
1439 fetch csr_get_user_employee_id into l_creator_id;
1440 close csr_get_user_employee_id;
1441
1442 hr_utility.set_location(l_proc, 40);
1443
1444 -- get the person id
1445 open csr_get_employee_id(l_applicant_id);
1446 fetch csr_get_employee_id into l_applicant_id;
1447 close csr_get_employee_id;
1448
1449 hr_utility.set_location(l_proc, 50);
1450
1451 open csr_offer_notes(l_offer_status_history_id);
1452 fetch csr_offer_notes into l_note;
1453 if csr_offer_notes%found then
1454 --
1455 -- convert clob data to varchar2
1456 --
1457 l_begining := DBMS_LOB.GETLENGTH(l_note);
1458 DBMS_LOB.READ(l_note, l_begining, l_end, l_note_text);
1459 end if;
1460 close csr_offer_notes;
1461
1462 hr_utility.set_location(l_proc, 55);
1463 -- Send notification to the candidate only when the offer has already been extended
1464
1465 if l_prev_offer_status='EXTENDED' or l_prev_offer_status='PENDING_EXTENDED_DURATION' or l_prev_change_reason='APL_ACCEPTED' then
1466
1467 -- build subject message for applicant
1468 fnd_message.set_name('PER','IRC_412572_OFFER_WITHDRAW_APL');
1469 fnd_message.set_token('JOB_TITLE',l_job_posting_title, false);
1470 l_subject := fnd_message.get;
1471 --
1472
1473 --
1474 hr_utility.set_location(l_proc, 60);
1475
1476 -- build html body for applicant
1477 fnd_message.set_name('PER','IRC_412573_WITHDRAWAL_APL_HTML');
1478 fnd_message.set_token('JOB_TITLE',l_job_posting_title, false);
1479 fnd_message.set_token('HIRING_MANAGER',l_hiring_manager, false);
1480 l_html_body := fnd_message.get || '<BR>' || l_note_text;
1481
1482 hr_utility.set_location(l_proc, 70);
1483
1484 -- build text body
1485 fnd_message.set_name('PER','IRC_412574_WITHDRAWAL_APL_TEXT');
1486 fnd_message.set_token('JOB_TITLE',l_job_posting_title, false);
1487 fnd_message.set_token('HIRING_MANAGER',l_hiring_manager, false);
1488
1489 l_text_body := fnd_message.get || '\n' || l_note_text;
1490
1491 --
1492 hr_utility.set_location(l_proc, 80);
1493
1494 -- send notification
1495 l_id := irc_notification_helper_pkg.send_notification
1496 ( p_person_id => l_applicant_id
1497 , p_subject => l_subject
1498 , p_html_body => l_html_body
1499 , p_text_body => l_text_body
1500 , p_from_role => l_creator_name
1501 );
1502 hr_utility.set_location(l_proc, 90);
1503
1504 end if;
1505
1506 --send notification to manager
1507 IF( l_manager_id is not null or l_recruiter_id is not null or l_creator_id is not null) THEN
1508
1509 hr_utility.set_location(l_proc, 100);
1510
1511 hr_utility.set_location('l_last_updated_by' || l_last_updated_by, 20);
1512
1513 open csr_get_user_employee_id(l_last_updated_by);
1514 fetch csr_get_user_employee_id into l_last_updated_emp_id;
1515 close csr_get_user_employee_id;
1516
1517 hr_utility.set_location('l_last_updated_emp_id' || l_last_updated_emp_id, 20);
1518
1519 open csr_get_name(l_last_updated_emp_id);
1520 fetch csr_get_name into l_action_performer;
1521 close csr_get_name;
1522
1523 hr_utility.set_location('l_action_performer' || l_action_performer, 20);
1524
1525 -- get the decline reason
1526 l_withdrawal_reason:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/decline_reason');
1527 open csr_get_withdrawal_reason(l_withdrawal_reason);
1528 fetch csr_get_withdrawal_reason into l_withdrawal_reason_meaning;
1529 close csr_get_withdrawal_reason;
1530
1531 -- build subject message for manager
1532 fnd_message.set_name('PER','IRC_412575_OFFER_WITHDRAW_MGR');
1533 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1534 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1535 l_subject := fnd_message.get;
1536
1537 -- build html body for applicant
1538 fnd_message.set_name('PER','IRC_412564_WITHDRAWAL_MGR_HTML');
1539 fnd_message.set_token('ACTION_PERFORMER',l_action_performer, false);
1540 fnd_message.set_token('WITHDRAWAL_REASON',l_withdrawal_reason_meaning, false);
1541 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1542 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1543 fnd_message.set_token('NOTES',l_note_text, false);
1544
1545 l_html_body := fnd_message.get;
1546
1547 hr_utility.set_location(l_proc, 110);
1548
1549 -- build text body
1550 fnd_message.set_name('PER','IRC_412565_WITHDRAWAL_MGR_TEXT');
1551 fnd_message.set_token('ACTION_PERFORMER',l_action_performer, false);
1552 fnd_message.set_token('WITHDRAWAL_REASON',l_withdrawal_reason_meaning, false);
1553 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1554 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1555 fnd_message.set_token('NOTES',l_note_text, false);
1556
1557 l_text_body := fnd_message.get;
1558
1559 --
1560 hr_utility.set_location(l_proc, 120);
1561
1562 -- send notification to manager
1563 IF( l_manager_id is not null ) THEN
1564 l_id := irc_notification_helper_pkg.send_notification
1565 ( p_person_id => l_manager_id
1566 , p_subject => l_subject
1567 , p_html_body => l_html_body
1568 , p_text_body => l_text_body
1569 , p_from_role => l_creator_name
1570 );
1571 hr_utility.set_location(l_proc, 130);
1572 END IF;
1573 -- send notification to recruiter
1574 IF( l_recruiter_id is not null
1575 and (l_manager_id is null or l_recruiter_id <> l_manager_id) ) THEN
1576 l_id := irc_notification_helper_pkg.send_notification
1577 ( p_person_id => l_recruiter_id
1578 , p_subject => l_subject
1579 , p_html_body => l_html_body
1580 , p_text_body => l_text_body
1581 , p_from_role => l_creator_name
1582 );
1583 hr_utility.set_location(l_proc, 140);
1584 END IF;
1585 -- send notification to creator
1586 IF( l_creator_id is not null
1587 and (l_recruiter_id is null or l_creator_id <> l_recruiter_id )
1588 and (l_manager_id is null or l_creator_id <> l_manager_id )) THEN
1589 l_id := irc_notification_helper_pkg.send_notification
1590 ( p_person_id => l_creator_id
1591 , p_subject => l_subject
1592 , p_html_body => l_html_body
1593 , p_text_body => l_text_body
1594 , p_from_role => l_creator_name
1595 );
1596 hr_utility.set_location(l_proc, 150);
1597
1598 END IF;
1599
1600 END IF;
1601 END IF;
1602 --
1603 END IF;
1604 resultout:='COMPLETE';
1605 hr_utility.set_location(' Leaving:'||l_proc, 160);
1606 --
1607 END send_withdrawal_notification;
1608 --
1609 -- ----------------------------------------------------------------------------
1610 -- send_dcln_acptd_offer_notif --
1611 -- called internally to send notification about the applicant declining --
1612 -- offer after acceptance :
1613 -- sends the notification to applicant and manager/recruiter --
1614 -- ----------------------------------------------------------------------------
1615 --
1616 PROCEDURE send_dcln_acptd_offer_notif(itemtype in varchar2,
1617 itemkey in varchar2,
1618 actid in number,
1619 funcmode in varchar2,
1620 resultout out nocopy varchar2) is
1621 --
1622 Event wf_event_t;
1623 EventDocument CLOB;
1624 l_note CLOB;
1625
1626 l_id number;
1627 l_applicant_id number;
1628 l_offer_id number;
1629 l_manager_id number;
1630 l_recruiter_id number;
1631 l_creator_id number;
1632 l_begining number;
1633 l_user_id number;
1634 l_end number := 1;
1635 l_offer_status_history_id number;
1636 l_creator_name varchar2(4000);
1637 l_last_updated_by number;
1638 l_apl_dec_acpt boolean := false;
1639
1640 l_note_text varchar2(32767);
1641 l_vacancy_name varchar2(4000);
1642 l_job_title varchar2(4000);
1643 l_applicant_name varchar2(4000);
1644 l_change_reason varchar2(4000);
1645 l_offer_status varchar2(4000);
1646 l_decline_reason varchar2(4000);
1647 l_decline_reason_meaning varchar2(4000);
1648 l_hiring_manager varchar2(4000);
1649 l_recruiter varchar2(4000);
1650 l_job_posting_title varchar2(4000);
1651
1652 l_subject varchar2(15599);
1653 l_html_body varchar2(15599);
1654 l_text_body varchar2(15599);
1655
1656 l_proc varchar2(30) default '.send_dcln_acptd_offer_notif';
1657 --
1658 cursor csr_offer_notes (p_offer_status_history_id IN NUMBER) is
1659 select note.note_text
1660 from irc_notes note
1661 where note.offer_status_history_id = p_offer_status_history_id
1662 order by creation_date desc;
1663 --
1664 cursor csr_get_decline_reason (p_decline_reason IN VARCHAR2) is
1665 select meaning
1666 from hr_lookups
1667 where lookup_type = 'IRC_OFFER_DECLINE_REASON' and
1668 lookup_code = p_decline_reason;
1669 --
1670 cursor csr_get_prev_offer_details (p_offer_status_history_id IN NUMBER,p_offer_id IN NUMBER) is
1671 select offer_status,change_reason
1672 from irc_offer_status_history
1673 where offer_id = p_offer_id
1674 and status_change_date = (select max(status_change_date)
1675 from irc_offer_status_history
1676 where offer_id = p_offer_id
1677 and offer_status_history_id <> p_offer_status_history_id);
1678 --
1679 CURSOR csr_get_name
1680 (p_person_id IN number)
1681 IS
1682 select ppf.full_name
1683 from per_all_people_f ppf
1684 where ppf.person_id = p_person_id
1685 and sysdate between effective_start_date and effective_end_date;
1686 --
1687
1688 --
1689 BEGIN
1690 --
1691
1692 hr_utility.set_location(l_proc, 10);
1693 IF (funcmode='RUN') THEN
1694
1695 hr_utility.set_location(l_proc, 20);
1696
1697 Event:=wf_engine.getActivityAttrEvent
1698 (itemtype => itemtype
1699 ,itemkey => itemkey
1700 ,actid => actid
1701 ,name => 'EVENT');
1702
1703 EventDocument:=Event.getEventData();
1704 l_offer_id:= to_number(irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_id'));
1705 l_offer_status:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_status');
1706 l_change_reason:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/change_reason');
1707 l_offer_status_history_id:=to_number(irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_status_history_id'));
1708
1709 IF ( l_offer_status = 'CLOSED' and l_change_reason = 'APL_DECLINED_ACCEPTANCE') THEN
1710 l_apl_dec_acpt := true;
1711 ELSE
1712 l_apl_dec_acpt := false;
1713 END IF;
1714
1715 IF ( l_apl_dec_acpt = true ) THEN
1716 --
1717 hr_utility.set_location(l_proc, 30);
1718
1719 open csr_send_apl_resp(l_offer_id);
1720 fetch csr_send_apl_resp into l_vacancy_name,l_manager_id,l_recruiter_id,l_creator_id,l_job_title,l_applicant_id,l_applicant_name,l_job_posting_title,l_last_updated_by;
1721 close csr_send_apl_resp;
1722
1723 --get the user name for creator
1724 open csr_get_user_name(l_creator_id);
1725 fetch csr_get_user_name into l_creator_name;
1726 close csr_get_user_name;
1727
1728 -- get the recruiter name
1729 open csr_get_name(l_recruiter_id);
1730 fetch csr_get_name into l_recruiter;
1731 close csr_get_name;
1732
1733 -- get the manager name
1734 open csr_get_name(l_manager_id);
1735 fetch csr_get_name into l_hiring_manager;
1736 close csr_get_name;
1737
1738 --get the person id for creator
1739 open csr_get_user_employee_id(l_creator_id);
1740 fetch csr_get_user_employee_id into l_creator_id;
1741 close csr_get_user_employee_id;
1742
1743 hr_utility.set_location(l_proc, 40);
1744
1745 -- get the person id
1746 open csr_get_employee_id(l_applicant_id);
1747 fetch csr_get_employee_id into l_applicant_id;
1748 close csr_get_employee_id;
1749
1750 hr_utility.set_location(l_proc, 50);
1751
1752 open csr_offer_notes(l_offer_status_history_id);
1753 fetch csr_offer_notes into l_note;
1754 if csr_offer_notes%found then
1755 --
1756 -- convert clob data to varchar2
1757 --
1758 l_begining := DBMS_LOB.GETLENGTH(l_note);
1759 DBMS_LOB.READ(l_note, l_begining, l_end, l_note_text);
1760 end if;
1761 close csr_offer_notes;
1762
1763 -- get the decline reason
1764 l_decline_reason:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/decline_reason');
1765 open csr_get_decline_reason(l_decline_reason);
1766 fetch csr_get_decline_reason into l_decline_reason_meaning;
1767 close csr_get_decline_reason;
1768
1769 hr_utility.set_location(l_proc, 55);
1770
1771 -- build subject message for applicant
1772 fnd_message.set_name('PER','IRC_412566_DECLINED_ACCEPT_APL');
1773 fnd_message.set_token('JOB_TITLE',l_job_posting_title, false);
1774 l_subject := fnd_message.get;
1775 --
1776
1777 --
1778 hr_utility.set_location(l_proc, 60);
1779
1780 -- build html body for applicant
1781 fnd_message.set_name('PER','IRC_412567_DEC_ACCEPT_APL_HTML');
1782 fnd_message.set_token('DECLINE_REASON',l_decline_reason_meaning, false);
1783 fnd_message.set_token('HIRING_MANAGER',l_hiring_manager, false);
1784 l_html_body := fnd_message.get || '<BR>' || l_note_text;
1785
1786 hr_utility.set_location(l_proc, 70);
1787
1788 -- build text body
1789 fnd_message.set_name('PER','IRC_412568_DEC_ACCEPT_APL_TEXT');
1790 fnd_message.set_token('DECLINE_REASON',l_decline_reason_meaning, false);
1791 fnd_message.set_token('HIRING_MANAGER',l_hiring_manager, false);
1792
1793 l_text_body := fnd_message.get || '\n' || l_note_text;
1794
1795 --
1796 hr_utility.set_location(l_proc, 80);
1797
1798 -- send notification
1799 l_id := irc_notification_helper_pkg.send_notification
1800 ( p_person_id => l_applicant_id
1801 , p_subject => l_subject
1802 , p_html_body => l_html_body
1803 , p_text_body => l_text_body
1804 , p_from_role => l_creator_name
1805 );
1806 hr_utility.set_location(l_proc, 90);
1807
1808 end if;
1809
1810 --send notification to manager
1811 IF( l_manager_id is not null or l_recruiter_id is not null or l_creator_id is not null) THEN
1812
1813 hr_utility.set_location(l_proc, 100);
1814
1815 -- build subject message for manager
1816 fnd_message.set_name('PER','IRC_412569_DECLINED_ACCEPT_MGR');
1817 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1818 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1819 l_subject := fnd_message.get;
1820
1821 -- build html body for applicant
1822 fnd_message.set_name('PER','IRC_412570_DEC_ACCEPT_MGR_HTML');
1823 fnd_message.set_token('DECLINE_REASON',l_decline_reason_meaning, false);
1824 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1825 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1826 fnd_message.set_token('NOTES',l_note_text, false);
1827
1828 l_html_body := fnd_message.get;
1829
1830 hr_utility.set_location(l_proc, 110);
1831
1832 -- build text body
1833 fnd_message.set_name('PER','IRC_412571_DEC_ACCEPT_MGR_TEXT');
1834 fnd_message.set_token('DECLINE_REASON',l_decline_reason_meaning, false);
1835 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1836 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1837 fnd_message.set_token('NOTES',l_note_text, false);
1838
1839 l_text_body := fnd_message.get;
1840
1841 --
1842 hr_utility.set_location(l_proc, 120);
1843
1844 -- send notification to manager
1845 IF( l_manager_id is not null ) THEN
1846 l_id := irc_notification_helper_pkg.send_notification
1847 ( p_person_id => l_manager_id
1848 , p_subject => l_subject
1849 , p_html_body => l_html_body
1850 , p_text_body => l_text_body
1851 , p_from_role => l_creator_name
1852 );
1853 hr_utility.set_location(l_proc, 130);
1854 END IF;
1855 -- send notification to recruiter
1856 IF( l_recruiter_id is not null
1857 and (l_manager_id is null or l_recruiter_id <> l_manager_id) ) THEN
1858 l_id := irc_notification_helper_pkg.send_notification
1859 ( p_person_id => l_recruiter_id
1860 , p_subject => l_subject
1861 , p_html_body => l_html_body
1862 , p_text_body => l_text_body
1863 , p_from_role => l_creator_name
1864 );
1865 hr_utility.set_location(l_proc, 140);
1866 END IF;
1867 -- send notification to creator
1868 IF( l_creator_id is not null
1869 and (l_recruiter_id is null or l_creator_id <> l_recruiter_id )
1870 and (l_manager_id is null or l_creator_id <> l_manager_id )) THEN
1871 l_id := irc_notification_helper_pkg.send_notification
1872 ( p_person_id => l_creator_id
1873 , p_subject => l_subject
1874 , p_html_body => l_html_body
1875 , p_text_body => l_text_body
1876 , p_from_role => l_creator_name
1877 );
1878 hr_utility.set_location(l_proc, 150);
1879
1880 END IF;
1881
1882 END IF;
1883 --
1884 END IF;
1885 resultout:='COMPLETE';
1886 hr_utility.set_location(' Leaving:'||l_proc, 160);
1887 --
1888 END send_dcln_acptd_offer_notif;
1889
1890 --
1891 END irc_offer_notifications_pkg;