[Home] [Help]
PACKAGE BODY: APPS.IRC_OFFER_NOTIFICATIONS_PKG
Source
1 Package body irc_offer_notifications_pkg as
2 /* $Header: irofrnotif.pkb 120.30.12020000.6 2013/01/03 06:28:45 nitnaras 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 ,asg.recruiter_id as recruiter_id
59 ,job.name as job_title
60 ,asg.person_id as applicant_id
61 ,ppf.full_name as applicant_name
62 ,iof.created_by as creator_id
63 ,ipc.name as job_posting_title
64 ,ipc.job_title as ipc_job_title
65 from irc_offers iof
66 ,per_all_vacancies vac
67 ,per_jobs_vl job
68 ,per_all_assignments_f asg
69 ,per_all_people_f ppf
70 ,irc_posting_contents_vl ipc
71 where
72 iof.offer_status = 'EXTENDED'
73 and iof.vacancy_id = vac.vacancy_id
74 and vac.job_id = job.job_id(+)
75 and asg.assignment_id = iof.applicant_assignment_id
76 and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
77 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
78 and ppf.person_id = asg.person_id
79 and iof.offer_id = p_offer_id
80 and vac.primary_posting_id = ipc.posting_content_id;
81
82 -- ***************************************************************************
83 -- Cursor to get offer details for the given offer id
84 -- ***************************************************************************
85 CURSOR csr_send_apl_resp
86 (p_offer_id IN number)
87 IS
88 select vac.name as vacancy_name
89 ,asg.supervisor_id as manager_id
90 ,asg.recruiter_id as recruiter_id
91 ,iof.created_by as creator
92 ,job.name as job_title
93 ,asg.person_id as applicant_id
94 ,ppf.full_name as applicant_name
95 ,ipc.name as job_posting_title
96 ,ipc.job_title as ipc_job_title
97 ,iof.last_updated_by as last_updated
98 from irc_offers iof
99 ,per_all_vacancies vac
100 ,per_jobs_vl job
101 ,per_all_assignments_f asg
102 ,per_all_people_f ppf
103 ,irc_posting_contents_vl ipc
104 where
105 iof.offer_status = 'CLOSED'
106 and iof.vacancy_id = vac.vacancy_id
107 and vac.job_id = job.job_id(+)
108 and asg.assignment_id = iof.offer_assignment_id
109 and asg.effective_start_date = (select max(effective_start_date)
110 from per_assignments_f asg2 where
111 asg.assignment_id=asg2.assignment_id
112 and asg2.effective_start_date <= trunc(sysdate))
113 and trunc(sysdate) between ppf.effective_start_date
114 and ppf.effective_end_date
115 and ppf.person_id = asg.person_id
116 and iof.offer_id = p_offer_id
117 and ipc.posting_content_id(+) = vac.primary_posting_id;
118
119 -- ***************************************************************************
120 -- Cursor to find offers which are about to expire in p_number_of_days
121 -- ***************************************************************************
122 CURSOR csr_get_expiry_offer_rec
123 (p_number_of_days in number)
124 IS
125 select iof.offer_id
126 ,nvl((iof.offer_extended_method),
127 (fnd_profile.VALUE('IRC_OFFER_SEND_METHOD'))) extended_method
128 ,iof.applicant_assignment_id
129 ,vac.name as vacancy_name
130 ,vac.manager_id as manager_id
131 ,job.name as job_title
132 ,asg.person_id as applicant_id
133 ,ppf.full_name as applicant_name
134 ,iof.expiry_date
135 ,iof.created_by as creator_id
136 ,ipc.name as job_posting_title
137 ,ipc.job_title as ipc_job_title
138 from irc_offers iof
139 ,per_all_vacancies vac
140 ,per_jobs_vl job
141 ,per_all_assignments_f asg
142 ,per_all_people_f ppf
143 ,irc_posting_contents_vl ipc
144 where
145 iof.offer_status = 'EXTENDED'
146 and iof.latest_offer = 'Y'
147 and iof.vacancy_id = vac.vacancy_id
148 and vac.job_id = job.job_id(+)
149 and asg.assignment_id = iof.applicant_assignment_id
150 and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
151 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
152 and ppf.person_id = asg.person_id
153 and (iof.expiry_date between trunc(sysdate) + 0 and
154 trunc(sysdate) + p_number_of_days)
155 and vac.primary_posting_id = ipc.posting_content_id;
156
157 -- ***************************************************************************
158 -- Cursor to find offers which are expired in the past day
159 -- ***************************************************************************
160
161 CURSOR csr_get_expired_offer_rec
162 IS
163 select iof.offer_id
164 ,vac.name as vacancy_name
165 ,vac.manager_id as manager_id
166 ,job.name as job_title
167 ,asg.person_id as applicant_id
168 ,ppf.full_name as applicant_name
169 ,iof.created_by as creator_id
170 ,ipc.name as job_posting_title
171 ,ipc.job_title as ipc_job_title
172 from irc_offers iof
173 ,per_all_vacancies vac
174 ,per_jobs_vl job
175 ,per_all_assignments_f asg
176 ,per_all_people_f ppf
177 ,irc_posting_contents_vl ipc
178 where
179 iof.vacancy_id = vac.vacancy_id
180 and iof.offer_status = 'EXTENDED'
181 and iof.latest_offer = 'Y'
182 and vac.job_id = job.job_id(+)
183 and asg.assignment_id = iof.applicant_assignment_id
184 and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
185 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
186 and ppf.person_id = asg.person_id
187 and iof.expiry_date < trunc(sysdate) + 0
188 and vac.primary_posting_id = ipc.posting_content_id;
189
190 -- ***************************************************************************
191 -- Cursor to get offer details for the given offer id
192 -- ***************************************************************************
193 CURSOR csr_onhold_offer
194 (p_offer_id IN number)
195 IS
196 select vac.name as vacancy_name
197 ,iof.created_by as creator_id
198 ,job.name as job_title
199 ,ppf.full_name as applicant_name
200 from irc_offers iof
201 ,per_all_vacancies vac
202 ,per_jobs_vl job
203 ,per_all_assignments_f asg
204 ,per_all_people_f ppf
205 where
206 iof.offer_status = 'HOLD'
207 and iof.vacancy_id = vac.vacancy_id
208 and vac.job_id = job.job_id(+)
209 and asg.assignment_id = iof.applicant_assignment_id
210 and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
211 and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
212 and ppf.person_id = asg.person_id
213 and iof.offer_id = p_offer_id;
214
215 CURSOR csr_get_user_name
216 (p_user_id IN number)
217 IS
218 select
219 usr.user_name
220 from
221 fnd_user usr
222 where usr.user_id = p_user_id;
223 --
224 cursor csr_get_apl_assignment_id (p_offer_id in number) is
225 select applicant_assignment_id
226 from irc_offers
227 where offer_id = p_offer_id;
228 --
229 cursor get_nls_lang (p_dft_lang varchar2) is
230 select NLS_LANGUAGE
231 from fnd_languages_vl
232 where language_code = p_dft_lang;
233 --
234 cursor get_user_lang(l_applicant_id number) is
235 select FND_PROFILE.value_specific('ICX_LANGUAGE',usr.user_id)
236 from fnd_user usr
237 where EMPLOYEE_ID = l_applicant_id;
238 -- ----------------------------------------------------------------------------
239 -- FUNCTIONS
240 -- ----------------------------------------------------------------------------
241
242 --
243 -- -------------------------------------------------------------------------
244 -- |-----------------------< get_view_offer_url >--------------------------|
245 -- -------------------------------------------------------------------------
246 --
247 FUNCTION get_view_offer_url
248 ( p_person_id number
249 ,p_apl_asg_id number)
250 RETURN varchar2
251 IS
252 l_url varchar2(4000);
253 l_apps_fwk_agent varchar2(2000);
254 --
255 l_resp_key fnd_responsibility.responsibility_key%type;
256 l_resp_id fnd_responsibility.responsibility_id%type;
257 l_apl_id fnd_application.application_id%type;
258 l_apl_asg_id per_all_assignments_f.assignment_id%type;
259 l_function_name fnd_profile_option_values.profile_option_value%type;
260 --
261 --
262 CURSOR csr_get_apl_id (apl_short_name varchar2)
263 IS
264 select application_id
265 from fnd_application
266 where application_short_name = apl_short_name;
267 --
268 CURSOR csr_get_resp_id (resp_key varchar2,apl_id number)
269 IS
270 SELECT responsibility_id
271 FROM fnd_responsibility
272 WHERE responsibility_key = resp_key
273 AND application_id = apl_id;
274 --
275 CURSOR csr_get_src_assignment
276 IS
277 select src_apl_asg_id from per_vac_linked_assignments
278 where tgt_apl_asg_id = p_apl_asg_id
279 and sysdate between nvl(start_date,sysdate) and
280 nvl(end_date,sysdate) ;
281 BEGIN
282 --
283 l_apl_asg_id := p_apl_asg_id;
284 --
285 if (irc_utilities_pkg.is_internal_person(p_person_id,trunc(sysdate))='TRUE') then
286 l_apps_fwk_agent := rtrim(fnd_profile.value_specific('APPS_FRAMEWORK_AGENT')
287 ||fnd_profile.value('ICX_PREFIX'),'/');
288 l_resp_key := 'IRC_EMP_CANDIDATE';
289 else
290 l_apps_fwk_agent := rtrim(nvl(fnd_profile.value('IRC_FRAMEWORK_AGENT'),
291 fnd_profile.value('APPS_FRAMEWORK_AGENT'))
292 ||fnd_profile.value('ICX_PREFIX'),'/');
293 l_resp_key := 'IRC_EXT_CANDIDATE';
294 end if;
295 --
296 open csr_get_apl_id('PER');
297 fetch csr_get_apl_id into l_apl_id ;
298 close csr_get_apl_id;
299 --
300 open csr_get_resp_id(l_resp_key,l_apl_id);
301 fetch csr_get_resp_id into l_resp_id ;
302 close csr_get_resp_id;
303 --
304 l_function_name := fnd_profile.value_specific
305 (name => 'IRC_VIEW_OFFER_DETAILS_FUNC'
306 ,responsibility_id => l_resp_id
307 ,application_id => l_apl_id);
308 --
309 open csr_get_src_assignment;
310 fetch csr_get_src_assignment into l_apl_asg_id;
311 close csr_get_src_assignment;
312 --
313 l_url:=l_apps_fwk_agent
314 ||'/OA_HTML/OA.jsp?OAFunc='
315 ||l_function_name
316 ||'&addBreadCrumb=Y'
317 ||'&TabAction=OfferDetails'
318 ||'&retainAM=Y&p_aplid='||to_char(l_apl_asg_id);
319 RETURN l_url;
320 END get_view_offer_url;
321 --
322 -- -------------------------------------------------------------------------
323 -- |------------------< get_manager_view_offer_url >-----------------------|
324 -- -------------------------------------------------------------------------
325 --
326 FUNCTION get_manager_view_offer_url
327 ( p_person_id number
328 ,p_apl_asg_id number)
329 RETURN varchar2
330 IS
331 cursor c_func(p_function_name varchar2) is
332 select function_id from fnd_form_functions
333 where function_name = p_function_name;
334 --
335 CURSOR csr_get_src_assignment
336 IS
337 select src_apl_asg_id from per_vac_linked_assignments
338 where tgt_apl_asg_id = p_apl_asg_id
339 and sysdate between nvl(start_date,sysdate) and
340 nvl(end_date,sysdate) ;
341 --
342 l_url varchar2(4000);
343 l_apps_fwk_agent varchar2(2000);
344 l_params varchar2(32767);
345 l_funcId number;
346 l_apl_asg_id per_all_assignments_f.assignment_id%type;
347 BEGIN
348 l_apl_asg_id := p_apl_asg_id;
349 l_apps_fwk_agent := rtrim(fnd_profile.value_specific('APPS_FRAMEWORK_AGENT')
350 ||fnd_profile.value('ICX_PREFIX'),'/');
351 --
352 l_apps_fwk_agent := l_apps_fwk_agent ||'/OA_HTML';
353 --
354 open c_func('IRC_RELAUNCH_PG');
355 fetch c_func into l_funcId;
356 close c_func;
357 --
358 open csr_get_src_assignment;
359 fetch csr_get_src_assignment into l_apl_asg_id;
360 close csr_get_src_assignment;
361 --
362 l_params := 'IrcAction=OfferDetails'
363 ||'&p_aplid='||to_char(l_apl_asg_id)
364 ||'&p_sprty='||to_char(p_person_id);
365 --
366 l_url:= fnd_run_function.get_run_function_url ( p_function_id =>l_funcId,
367 p_resp_appl_id =>-1,
368 p_resp_id =>-1,
369 p_security_group_id =>0,
370 p_override_agent=>l_apps_fwk_agent,
371 p_parameters =>l_params,
372 p_encryptParameters =>true ) ;
373 --
374 RETURN l_url;
375 END get_manager_view_offer_url;
376 --
377 -- -------------------------------------------------------------------------
378 -- |------------------< get_extend_offer_duration_url >--------------------|
379 -- -------------------------------------------------------------------------
380 --
381 FUNCTION get_extend_offer_duration_url
382 ( p_person_id number
383 , p_offer_id number)
384 RETURN varchar2
385 IS
386 l_url varchar2(4000);
387 l_apps_fwk_agent varchar2(2000);
388 BEGIN
389 l_apps_fwk_agent := rtrim(fnd_profile.value_specific('APPS_FRAMEWORK_AGENT')
390 ||fnd_profile.value('ICX_PREFIX'),'/');
391
392 l_url:=l_apps_fwk_agent
393 ||'/OA_HTML/OA.jsp?OAFunc='
394 ||fnd_profile.value('IRC_EXTEND_OFFER_DURATION_FUNC')
395 ||'&addBreadCrumb=Y'
396 ||'&retainAM=Y&p_sofferid='||to_char(p_offer_id);
397 RETURN l_url;
398 END get_extend_offer_duration_url;
399
400
401 --
402 -- ----------------------------------------------------------------------------
403 -- send_rcvd_wf_notification --
404 -- called internally to send offer received notification : --
405 -- sends the notification to applicant and manager --
406 -- ----------------------------------------------------------------------------
407 --
408 PROCEDURE send_rcvd_wf_notification(itemtype in varchar2,
409 itemkey in varchar2,
410 actid in number,
411 funcmode in varchar2,
412 resultout out nocopy varchar2) is
413 --
414
415
416 Event wf_event_t;
417 EventDocument CLOB;
418 l_note CLOB;
419
420 l_id number;
421 l_applicant_id number;
422 l_manager_id number;
423 l_recruiter_id number;
424 l_offer_id number;
425 l_apl_asg_id number;
426 l_creator_id number;
427 l_begining number;
428 l_end number := 1;
429 l_offer_status_history_id number;
430
431 l_note_text varchar2(32767);
432 l_creator_name varchar2(4000);
433 l_vacancy_name varchar2(4000);
434 l_job_title varchar2(4000);
435 l_offer_status varchar2(4000);
436 l_applicant_name varchar2(4000);
437 l_url varchar2(4000);
438 l_job_posting_title varchar2(240);
439 l_ipc_job_title varchar2(240);
440
441 l_subject varchar2(15599);
442 l_html_body varchar2(15599);
443 l_text_body varchar2(15599);
444 l_proc varchar2(30) default '.send_rcvd_wf_notification';
445 l_current_set_lang varchar2(40);
446 l_apl_lang varchar2(40);
447 l_mgr_lang varchar2(40);
448 l_intial_set_lang varchar2(40);
449 --
450 cursor csr_offer_notes (p_offer_status_history_id IN NUMBER) is
451 select note.note_text
452 from irc_notes note
453 where note.offer_status_history_id = p_offer_status_history_id
454 order by creation_date desc;
455
456 --
457 BEGIN
458 --
459 hr_utility.set_location(l_proc, 10);
460 --Fetch applicantion's current session Language
461 open get_nls_lang(userenv('LANG'));
462 fetch get_nls_lang into l_intial_set_lang;
463 close get_nls_lang;
464
465 --Assign the session language to current set language.
466 l_current_set_lang := l_intial_set_lang;
467
468 IF (funcmode='RUN') THEN
469
470 hr_utility.set_location(l_proc, 20);
471
472 -- get the event name
473 Event:=wf_engine.getActivityAttrEvent
474 (itemtype => itemtype
475 ,itemkey => itemkey
476 ,actid => actid
477 ,name => 'EVENT');
478
479 -- get event data
480 EventDocument:=Event.getEventData();
481 l_offer_id:= to_number(irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_id'));
482 l_offer_status:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_status');
483 l_offer_status_history_id:=to_number(irc_xml_util.valueOf(EventDocument,
484 '/offer_status_history/offer_status_history_id'));
485
486 -- only when the new row is inserted with 'EXTENDED' offer status in irc_offer_status_history table,
487 -- proceed further
488 IF ( l_offer_status = 'EXTENDED') THEN
489
490 hr_utility.set_location(l_proc, 30);
491
492 -- get the note entered when extending the offer
493 open csr_offer_notes(l_offer_status_history_id);
494 fetch csr_offer_notes into l_note;
495 if csr_offer_notes%found then
496 --
497 -- convert clob data to varchar2
498 --
499 l_begining := DBMS_LOB.GETLENGTH(l_note);
500 DBMS_LOB.READ(l_note, l_begining, l_end, l_note_text);
501 end if;
502 close csr_offer_notes;
503
504 open csr_send_offer_rcvd(l_offer_id);
505 fetch csr_send_offer_rcvd into l_apl_asg_id,l_vacancy_name,l_manager_id, l_recruiter_id,
506 l_job_title,l_applicant_id,l_applicant_name,l_creator_id,l_job_posting_title,l_ipc_job_title;
507 close csr_send_offer_rcvd;
508
509 -- get the user name for creator
510 open csr_get_user_name(l_creator_id);
511 fetch csr_get_user_name into l_creator_name;
512 close csr_get_user_name;
513
514 hr_utility.set_location(l_proc, 40);
515
516 -- get the view offer letter url
517 -- pass the applicant assignment id because drilldown used in application details page uses it
518 l_url := get_view_offer_url
519 (p_person_id => l_applicant_id
520 ,p_apl_asg_id => l_apl_asg_id);
521
522
523 -- get the employee id for the applicant
524 -- this is required to send notification
525 open csr_get_employee_id(l_applicant_id);
526 fetch csr_get_employee_id into l_applicant_id;
527 close csr_get_employee_id;
528
529
530 hr_utility.set_location(l_proc, 50);
531
532 -- Fetch applicant pref language
533
534 open get_user_lang(l_applicant_id);
535 fetch get_user_lang into l_apl_lang;
536 close get_user_lang;
537
538 --set the applicant pref language to the session
539
540 if l_current_set_lang <> l_apl_lang then
541 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_apl_lang||'''');
542 l_current_set_lang := l_apl_lang;
543 end if;
544
545 -- build subject message
546 fnd_message.set_name('PER','IRC_OFFER_RECEIVED_APL_SUBJECT');
547 fnd_message.set_token('VACANCY_NAME',l_job_posting_title, false);
548 fnd_message.set_token('JOB_TITLE',l_ipc_job_title, false);
549 l_subject := fnd_message.get;
550
551 hr_utility.set_location(l_proc, 60);
552
553 -- build html body
554 fnd_message.set_name('PER','IRC_OFFER_RECEIVED_APL_HTML');
555 fnd_message.set_token('VACANCY_NAME',l_job_posting_title, false);
556 fnd_message.set_token('JOB_TITLE',l_ipc_job_title, false);
557 l_html_body := fnd_message.get;
558
559 --
560
561 l_html_body := l_html_body
562 || '<BR>' || l_note_text;
563
564 if (fnd_profile.value('IRC_OFFER_SEND_METHOD')='SYSTEM') then
565 l_html_body := l_html_body
566 || '<BR><BR><a HREF="'||l_url
567 || '">'
568 || 'View Offer'
569 || '</a>'
570 || '<BR>';
571 end if;
572
573 hr_utility.set_location(l_proc, 70);
574
575 -- build text body
576 --
577 fnd_message.set_name('PER','IRC_OFFER_RECEIVED_APL_TEXT');
578 fnd_message.set_token('VACANCY_NAME',l_job_posting_title, false);
579 fnd_message.set_token('JOB_TITLE',l_ipc_job_title, false);
580 l_text_body := fnd_message.get;
581
582 --
583 l_text_body := l_text_body
584 || '\n' || l_note_text;
585
586 if (fnd_profile.value('IRC_OFFER_SEND_METHOD')='SYSTEM') then
587 l_text_body := l_text_body
588 || '\n\n'||'View Offer'
589 || '\n'||l_url
590 || '\n';
591 end if;
592
593 --
594 hr_utility.set_location(l_proc, 80);
595
596 -- send notification
597 if l_applicant_id is not null then
598 l_id := irc_notification_helper_pkg.send_notification
599 ( p_person_id => l_applicant_id
600 , p_subject => l_subject
601 , p_html_body => l_html_body
602 , p_text_body => l_text_body
603 , p_from_role => l_creator_name
604 );
605 end if;
606 hr_utility.set_location(l_proc, 90);
607
608 -- now send the notification to manager also
609 IF( l_manager_id is not null or l_recruiter_id is not null) THEN
610 --
611 hr_utility.set_location(l_proc, 100);
612
613 -- get the employee id for manager
614 open csr_get_employee_id(l_manager_id);
615 fetch csr_get_employee_id into l_manager_id;
616 close csr_get_employee_id;
617
618 -- get the employee id for recruiter
619 open csr_get_employee_id(l_recruiter_id);
620 fetch csr_get_employee_id into l_recruiter_id;
621 close csr_get_employee_id;
622
623 --fetch the resp. Manager pref. language
624 open get_user_lang(l_manager_id);
625 fetch get_user_lang into l_mgr_lang;
626 close get_user_lang;
627
628 -- set the pref language of Manager to the session */
629
630 if l_current_set_lang <> l_mgr_lang then
631 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_mgr_lang||'''');
632 l_current_set_lang := l_mgr_lang;
633 end if;
634
635 -- build subject message
636 fnd_message.set_name('PER','IRC_OFFER_SENT_MGR_SUBJECT');
637 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
638 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
639 l_subject := fnd_message.get;
640
641 hr_utility.set_location(l_proc, 110);
642
643
644 -- build html body
645 fnd_message.set_name('PER','IRC_OFFER_SENT_MGR_HTML');
646 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
647 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
648 l_html_body := fnd_message.get || '<BR>' || l_note_text;
649
650 --
651 hr_utility.set_location(l_proc, 120);
652
653 -- build text body
654 --
655 fnd_message.set_name('PER','IRC_OFFER_SENT_MGR_TEXT');
656 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
657 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
658 l_text_body := fnd_message.get || '\n' || l_note_text;
659
660 --
661 hr_utility.set_location(l_proc, 130);
662
663 -- send notification to manager
664 if l_manager_id is not null then
665 l_id := irc_notification_helper_pkg.send_notification
666 ( p_person_id => l_manager_id
667 , p_subject => l_subject
668 , p_html_body => l_html_body
669 , p_text_body => l_text_body
670 , p_from_role => l_creator_name
671 );
672 end if;
673 -- send notification to recruiter
674 if l_recruiter_id is not null and l_manager_id <> l_recruiter_id then
675 l_id := irc_notification_helper_pkg.send_notification
676 ( p_person_id => l_recruiter_id
677 , p_subject => l_subject
678 , p_html_body => l_html_body
679 , p_text_body => l_text_body
680 , p_from_role => l_creator_name
681 );
682 end if;
683 hr_utility.set_location(l_proc, 140);
684
685 --
686 END IF;
687 END IF;
688 --
689 END IF;
690 resultout:='COMPLETE';
691 hr_utility.set_location(' Leaving:'||l_proc, 150);
692 --
693 END send_rcvd_wf_notification;
694 --
695 --
696 -- ----------------------------------------------------------------------------
697 -- send_expiry_notification --
698 -- called from concurrent process to send offer expiry notification : --
699 -- sends the notification to applicant and manager --
700 -- ----------------------------------------------------------------------------
701 --
702 PROCEDURE send_expiry_notification
703 ( errbuf out nocopy varchar2
704 , retcode out nocopy number
705 , p_number_of_days in number)
706 is
707 --
708 l_id number;
709 l_applicant_id number;
710 l_apl_asg_id number;
711 l_manager_id number;
712 l_creator_name varchar2(4000);
713
714 l_subject varchar2(240);
715 l_html_body varchar2(32000);
716 l_text_body varchar2(32000);
717 l_url varchar2(4000);
718 l_proc varchar2(30) default '.send_expiry_notification';
719 l_extend_method varchar2(30);
720 l_offer_sent_date date;
721
722 send_notitfication_status varchar(10);
723 processed_record_count number;
724 failure_record_count number;
725
726 l_current_set_lang varchar2(40);
727 l_apl_lang varchar2(40);
728 l_mgr_lang varchar2(40);
729 l_intial_set_lang varchar2(40);
730
731 --
732 BEGIN
733 --
734 hr_utility.set_location('Entering'||l_proc, 10);
735
736 processed_record_count :=0;
737 failure_record_count :=0;
738
739 -- setting header to the log file
740 fnd_file.put_line
741 (which => fnd_file.log
742 ,buff => rpad('Sr#',4,' ')
743 || rpad('Vacancy Name',16,' ')
744 || rpad('Offer ID',12,' ')
745 || rpad('Applicant ID',13,' ')
746 || rpad('Applicant Last Name',27,' ')
747 || lpad('Status',8,' ')
748 );
749 fnd_file.NEW_LINE(WHICH => fnd_file.log );
750
751 --Fetch applicantion's current session Language
752 open get_nls_lang(userenv('LANG'));
753 fetch get_nls_lang into l_intial_set_lang;
754 close get_nls_lang;
755
756 --Assign the session language to current set language.
757 l_current_set_lang := l_intial_set_lang;
758
759 -- Loop through all the job seekers and send them
760 -- a general notification.
761 for offer_expiry_rec in csr_get_expiry_offer_rec
762 (p_number_of_days => p_number_of_days)loop
763
764 -- increment the value of record count
765 processed_record_count := processed_record_count+1;
766 send_notitfication_status :='success';
767 l_apl_asg_id := offer_expiry_rec.applicant_assignment_id;
768
769 --get offer sent date
770 open csr_get_offer_sent_date(offer_expiry_rec.offer_id);
771 fetch csr_get_offer_sent_date into l_offer_sent_date;
772 close csr_get_offer_sent_date;
773
774 --get the user name for creator
775 open csr_get_user_name(offer_expiry_rec.creator_id);
776 fetch csr_get_user_name into l_creator_name;
777 close csr_get_user_name;
778
779 hr_utility.set_location(l_proc,20);
780
781 -- get offer view url
782 l_url := get_view_offer_url
783 (p_person_id => offer_expiry_rec.applicant_id
784 ,p_apl_asg_id => l_apl_asg_id);
785
786
787 -- get the employee id for applicant
788 open csr_get_employee_id(offer_expiry_rec.applicant_id);
789 fetch csr_get_employee_id into l_applicant_id;
790 close csr_get_employee_id;
791 -- Fetch applicant pref language
792
793 open get_user_lang(l_applicant_id);
794 fetch get_user_lang into l_apl_lang;
795 close get_user_lang;
796
797
798 --set the applicant pref language to the session
799
800 if l_current_set_lang <> l_apl_lang then
801 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',l_apl_lang);
802 l_current_set_lang := l_apl_lang;
803 end if;
804
805 -- build subject message
806 fnd_message.set_name('PER','IRC_OFFER_EXPIRY_SUBJECT');
807 fnd_message.set_token('DAY_OF_EXPIRY',offer_expiry_rec.expiry_date, false);
808 fnd_message.set_token('VACANCY_NAME',offer_expiry_rec.job_posting_title, false);
809 fnd_message.set_token('JOB_TITLE',offer_expiry_rec.ipc_job_title, false);
810 l_subject := fnd_message.get;
811
812 hr_utility.set_location(l_proc,30);
813
814 --
815 -- Build the body of the message both in text and html
816 --
817
818 -- build html body
819 fnd_message.set_name('PER','IRC_OFFER_EXPIRY_APL_HTML');
820 fnd_message.set_token('SENT_DATE',l_offer_sent_date, false);
821 fnd_message.set_token('DAY_OF_EXPIRY',offer_expiry_rec.expiry_date, false);
822 l_html_body := fnd_message.get;
823
824 --
825 l_extend_method := offer_expiry_rec.extended_method;
826 if (l_extend_method='SYSTEM') then
827 l_html_body := l_html_body
828 || '<BR><a HREF="'||l_url
829 || '">'
830 || 'View Offer'
831 || '</a>'
832 || '<BR>';
833 end if;
834 --
835 hr_utility.set_location(l_proc,40);
836
837 -- build text body
838
839 fnd_message.set_name('PER','IRC_OFFER_EXPIRY_APL_TEXT');
840 fnd_message.set_token('SENT_DATE',l_offer_sent_date, false);
841 fnd_message.set_token('DAY_OF_EXPIRY',offer_expiry_rec.expiry_date, false);
842 l_text_body := fnd_message.get;
843
844 --
845 if (l_extend_method='SYSTEM') then
846 l_text_body := l_text_body
847 || '\n'||'View Offer'
848 || '\n'||l_url
849 || '\n';
850 end if;
851 --
852 hr_utility.set_location(l_proc,50);
853
854 -- send notification to applicant
855 BEGIN
856 if l_applicant_id is not null then
857 l_id := irc_notification_helper_pkg.send_notification
858 ( p_person_id => l_applicant_id
859 , p_subject => l_subject
860 , p_html_body => l_html_body
861 , p_text_body => l_text_body
862 , p_from_role => l_creator_name
863 );
864 end if;
865 EXCEPTION
866 When OTHERS then
867 send_notitfication_status := 'failure';
868 failure_record_count := failure_record_count + 1;
869 fnd_file.put_line
870 ( which => fnd_file.log
871 ,buff => rpad(processed_record_count ||'.',4,' ')
872 || rpad(offer_expiry_rec.vacancy_name,16,' ')
873 || rpad(offer_expiry_rec.offer_id,12,' ')
874 || rpad(offer_expiry_rec.applicant_id,13,' ')
875 || rpad(rtrim(substr(rtrim(offer_expiry_rec.applicant_name),1, INSTR(rtrim(offer_expiry_rec.applicant_name), ',', 1, 1)-1)),27,' ')
876 || lpad(send_notitfication_status,8,' ')
877 );
878
879 fnd_file.put_line
880 ( which => fnd_file.log
881 ,buff => 'Failure Reason: '|| fnd_message.get_string('PER','IRC_412059_NO_ROLE_4_PARTY')
882 );
883 END;
884
885 hr_utility.set_location(l_proc,60);
886
887 -- send notification to manager
888 IF ( offer_expiry_rec.manager_id is not null) THEN
889
890 hr_utility.set_location(l_proc,70);
891
892 --get the employee id for manager
893 open csr_get_employee_id(offer_expiry_rec.manager_id);
894 fetch csr_get_employee_id into l_manager_id;
895 close csr_get_employee_id;
896
897 hr_utility.set_location(l_proc,80);
898
899 -- get extend offer duration url
900 l_url := get_extend_offer_duration_url
901 (p_person_id => l_manager_id
902 ,p_offer_id => offer_expiry_rec.offer_id);
903
904
905 hr_utility.set_location(l_proc,90);
906 --fetch the resp. Manager pref. language
907 open get_user_lang(l_manager_id);
908 fetch get_user_lang into l_mgr_lang;
909 close get_user_lang;
910
911 -- set the pref language of Manager to the session */
912
913 if l_current_set_lang <> l_mgr_lang then
914 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',l_mgr_lang);
915 l_current_set_lang := l_mgr_lang;
916 end if;
917
918 -- build subject message
919 fnd_message.set_name('PER','IRC_412671_OFFER_EXPY_MGR_SUB');
920 fnd_message.set_token('DAY_OF_EXPIRY',offer_expiry_rec.expiry_date, false);
921 fnd_message.set_token('VACANCY_NAME',offer_expiry_rec.vacancy_name, false);
922 fnd_message.set_token('JOB_TITLE',offer_expiry_rec.job_title, false);
923 l_subject := fnd_message.get;
924
925 --
926 -- Build the body of the message both in text and html
927 --
928 if send_notitfication_status = 'success' then
929 -- build html body
930 fnd_message.set_name('PER','IRC_OFFER_EXPIRY_MGR_HTML');
931 fnd_message.set_token('APPLICANT_NAME',offer_expiry_rec.applicant_name, false);
932 fnd_message.set_token('VACANCY_NAME',offer_expiry_rec.vacancy_name, false);
933 fnd_message.set_token('DAY_OF_EXPIRY',offer_expiry_rec.expiry_date, false);
934 l_html_body := fnd_message.get;
935 --
936 l_html_body := l_html_body
937 || '<BR><a HREF="'||l_url
938 || '">'
939 || 'Extend Duration'
940 || '</a>'
941 || '<BR>';
942 --
943 hr_utility.set_location(l_proc,100);
944
945 -- build text body
946
947 fnd_message.set_name('PER','IRC_OFFER_EXPIRY_MGR_TEXT');
948 fnd_message.set_token('APPLICANT_NAME',offer_expiry_rec.applicant_name, false);
949 fnd_message.set_token('VACANCY_NAME',offer_expiry_rec.vacancy_name, false);
950 fnd_message.set_token('DAY_OF_EXPIRY',offer_expiry_rec.expiry_date, false);
951 l_text_body := fnd_message.get;
952
953 --
954 l_text_body := l_text_body
955 || '\n'||'Extend Duration'
956 || '\n'||l_url
957 || '\n';
958 --
959 hr_utility.set_location(l_proc,110);
960
961 else --- send_notitfication_status is failure
962 -- build html body
963 fnd_message.set_name('PER','IRC_OFFER_EXPIRY_MGR_HTML');
964 fnd_message.set_token('APPLICANT_NAME',offer_expiry_rec.applicant_name, false);
965 fnd_message.set_token('VACANCY_NAME',offer_expiry_rec.vacancy_name, false);
966 fnd_message.set_token('DAY_OF_EXPIRY',offer_expiry_rec.expiry_date, false);
967 l_html_body := fnd_message.get;
968 l_html_body := l_html_body
969 ||'However, <br/>'||fnd_message.get_string('PER','IRC_412059_NO_ROLE_4_PARTY')
970 ||'<br/> Please Take Appropriate Action.';
971 -- build text body
972 fnd_message.set_name('PER','IRC_OFFER_EXPIRY_MGR_TEXT');
973 fnd_message.set_token('APPLICANT_NAME',offer_expiry_rec.applicant_name, false);
974 fnd_message.set_token('VACANCY_NAME',offer_expiry_rec.vacancy_name, false);
975 fnd_message.set_token('DAY_OF_EXPIRY',offer_expiry_rec.expiry_date, false);
976 l_text_body := fnd_message.get;
977 l_text_body := l_text_body
978 ||'However, <br/>'||fnd_message.get_string('PER','IRC_412059_NO_ROLE_4_PARTY')
979 ||'<br/> Please Take Appropriate Action.';
980
981 End if;
982 ---- sending notification to the manager
983 BEGIN
984 if l_manager_id is not null then
985 l_id := irc_notification_helper_pkg.send_notification
986 ( p_person_id => l_manager_id
987 , p_subject => l_subject
988 , p_html_body => l_html_body
989 , p_text_body => l_text_body
990 , p_from_role => l_creator_name
991 );
992 end if;
993 hr_utility.set_location(l_proc,120);
994 EXCEPTION
995 When OTHERS then
996 fnd_file.put_line
997 ( which => fnd_file.log
998 ,buff => 'The Manager' || rpad(offer_expiry_rec.manager_id,10,' ')||'dont have workflow role'
999 );
1000
1001 fnd_file.put_line
1002 ( which => fnd_file.log
1003 ,buff => ' Failure Reason: '|| fnd_message.get_string('PER','IRC_412059_NO_ROLE_4_PARTY')
1004 );
1005 END;
1006 END IF;
1007 if send_notitfication_status = 'success' then
1008 fnd_file.put_line
1009 ( which => fnd_file.log
1010 ,buff => rpad(processed_record_count ||'.',4,' ')
1011 || rpad(offer_expiry_rec.vacancy_name,16,' ')
1012 || rpad(offer_expiry_rec.offer_id,12,' ')
1013 || rpad(offer_expiry_rec.applicant_id,13,' ')
1014 || rpad(rtrim(substr(rtrim(offer_expiry_rec.applicant_name),1, INSTR(rtrim(offer_expiry_rec.applicant_name), ',', 1, 1)-1)),27,' ')
1015 || lpad(send_notitfication_status,8,' ')
1016 );
1017 end if;
1018 end loop;
1019 --Reset applicantion's current session Language
1020 if l_intial_set_lang <> l_current_set_lang then
1021 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',l_intial_set_lang);
1022 end if;
1023 fnd_file.NEW_LINE(WHICH => fnd_file.log );
1024 fnd_file.put_line
1025 (which => fnd_file.log
1026 ,buff => 'Total Record Processed ' || processed_record_count ||
1027 ' Successful Records '|| (processed_record_count - failure_record_count) ||
1028 ' Error Records '|| failure_record_count
1029 );
1030 hr_utility.set_location(' Leaving:'||l_proc, 130);
1031
1032 exception
1033 when others then
1034 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',l_intial_set_lang);
1035 --
1036 END send_expiry_notification;
1037 --
1038 --
1039 --
1040 -- ----------------------------------------------------------------------------
1041 -- send_expired_notification --
1042 -- called from concurrent process to send offer expired notification : --
1043 -- sends the notification to applicant and manager --
1044 -- ----------------------------------------------------------------------------
1045 --
1046 PROCEDURE send_expired_notification
1047 ( errbuf out nocopy varchar2
1048 , retcode out nocopy number)
1049 is
1050 --
1051 l_id number;
1052 l_applicant_id number;
1053 l_manager_id number;
1054 l_creator_name varchar2(4000);
1055
1056 l_subject varchar2(240);
1057 l_html_body varchar2(32000);
1058 l_text_body varchar2(32000);
1059 l_url varchar2(4000);
1060 l_proc varchar2(30) default '.send_expired_notification';
1061 l_offer_sent_date date;
1062
1063 send_notitfication_status varchar(10); --to keep the notification status
1064 processed_record_count number; -- keep track of no of processed record
1065 failure_record_count number; -- keeo track of no of failure records
1066
1067 l_current_set_lang varchar2(40);
1068 l_apl_lang varchar2(40);
1069 l_mgr_lang varchar2(40);
1070 l_intial_set_lang varchar2(40);
1071
1072 --
1073 BEGIN
1074 --
1075 hr_utility.set_location('Entering'||l_proc, 10);
1076 processed_record_count :=0;
1077 failure_record_count :=0;
1078
1079 --setting header to the log file
1080 fnd_file.put_line
1081 (which => fnd_file.log
1082 ,buff => rpad('Sr#',4,' ')
1083 || rpad('Vacancy Name',16,' ')
1084 || rpad('Offer ID',12,' ')
1085 || rpad('Applicant ID',13,' ')
1086 || rpad('Applicant Last Name',27,' ')
1087 || lpad('Status',8,' ')
1088 );
1089
1090 fnd_file.NEW_LINE(WHICH => fnd_file.log );
1091 --Fetch applicantion's current session Language
1092 open get_nls_lang(userenv('LANG'));
1093 fetch get_nls_lang into l_intial_set_lang;
1094 close get_nls_lang;
1095
1096 --Assign the session language to current set language.
1097 l_current_set_lang := l_intial_set_lang;
1098
1099 -- Loop through all the job seekers and send them
1100 -- a general notification.
1101 for offer_expired_rec in csr_get_expired_offer_rec loop
1102
1103 -- increment the value of record count
1104 processed_record_count := processed_record_count+1;
1105 send_notitfication_status :='success';
1106
1107 --get offer sent date
1108 open csr_get_offer_sent_date(offer_expired_rec.offer_id);
1109 fetch csr_get_offer_sent_date into l_offer_sent_date;
1110 close csr_get_offer_sent_date;
1111
1112 --get the user name for creator
1113 open csr_get_user_name(offer_expired_rec.creator_id);
1114 fetch csr_get_user_name into l_creator_name;
1115 close csr_get_user_name;
1116
1117 hr_utility.set_location(l_proc,20);
1118
1119 -- get the person id
1120 open csr_get_employee_id(offer_expired_rec.applicant_id);
1121 fetch csr_get_employee_id into l_applicant_id;
1122 close csr_get_employee_id;
1123
1124 -- Fetch applicant pref language
1125 open get_user_lang(l_applicant_id);
1126 fetch get_user_lang into l_apl_lang;
1127 close get_user_lang;
1128 --set the applicant pref language to the session
1129 if l_current_set_lang <> l_apl_lang then
1130 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',l_apl_lang);
1131 l_current_set_lang := l_apl_lang;
1132 end if;
1133
1134 -- build subject message
1135 fnd_message.set_name('PER','IRC_OFFER_EXPIRED_SUBJECT');
1136 fnd_message.set_token('VACANCY_NAME',offer_expired_rec.job_posting_title, false);
1137 fnd_message.set_token('JOB_TITLE',offer_expired_rec.ipc_job_title, false);
1138 l_subject := fnd_message.get;
1139
1140 hr_utility.set_location(l_proc,30);
1141
1142 --
1143 -- Build the body of the message both in text and html
1144 --
1145
1146 -- build html body
1147 fnd_message.set_name('PER','IRC_OFFER_EXPIRED_APL_HTML');
1148 fnd_message.set_token('SENT_DATE',l_offer_sent_date, false);
1149 l_html_body := fnd_message.get;
1150
1151 --
1152 hr_utility.set_location(l_proc,40);
1153
1154 -- build text body
1155 fnd_message.set_name('PER','IRC_OFFER_EXPIRED_APL_TEXT');
1156 fnd_message.set_token('SENT_DATE',l_offer_sent_date, false);
1157 l_text_body := fnd_message.get;
1158
1159 --
1160 hr_utility.set_location(l_proc,50);
1161
1162 -- send notification
1163
1164 BEGIN
1165
1166 if l_applicant_id is not null then
1167 l_id := irc_notification_helper_pkg.send_notification
1168 ( p_person_id => l_applicant_id
1169 , p_subject => l_subject
1170 , p_html_body => l_html_body
1171 , p_text_body => l_text_body
1172 , p_from_role => l_creator_name
1173 );
1174 end if;
1175 EXCEPTION
1176 When OTHERS then
1177 send_notitfication_status := 'failure';
1178 failure_record_count := failure_record_count + 1;
1179 fnd_file.put_line
1180 ( which => fnd_file.log
1181 ,buff => rpad(processed_record_count ||'.',4,' ')
1182 || rpad(offer_expired_rec.vacancy_name,16,' ')
1183 || rpad(offer_expired_rec.offer_id,12,' ')
1184 || rpad(offer_expired_rec.applicant_id,13,' ')
1185 || rpad(rtrim(substr(rtrim(offer_expired_rec.applicant_name),1, INSTR(rtrim(offer_expired_rec.applicant_name), ',', 1, 1)-1)),27,' ')
1186 || lpad(send_notitfication_status,8,' ')
1187 );
1188
1189 fnd_file.put_line
1190 ( which => fnd_file.log
1191 ,buff => 'Failure Reason: '|| fnd_message.get_string('PER','IRC_412059_NO_ROLE_4_PARTY')
1192 );
1193 END;
1194
1195 hr_utility.set_location(l_proc,60);
1196
1197 -- send notification to manager
1198 IF ( offer_expired_rec.manager_id is not null ) THEN
1199 --
1200 hr_utility.set_location(l_proc,70);
1201
1202
1203 --get the employee id for manager
1204 open csr_get_employee_id(offer_expired_rec.manager_id);
1205 fetch csr_get_employee_id into l_manager_id;
1206 close csr_get_employee_id;
1207 --
1208 --fetch the resp. Manager pref. language
1209 open get_user_lang(l_manager_id);
1210 fetch get_user_lang into l_mgr_lang;
1211 close get_user_lang;
1212
1213 -- set the pref language of Manager to the session */
1214 if l_current_set_lang <> l_mgr_lang then
1215 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',l_mgr_lang);
1216 l_current_set_lang := l_mgr_lang;
1217 end if;
1218
1219 -- build subject message
1220 fnd_message.set_name('PER','IRC_412670_OFFER_EXPD_MGR_SUB');
1221 fnd_message.set_token('VACANCY_NAME',offer_expired_rec.vacancy_name, false);
1222 fnd_message.set_token('JOB_TITLE',offer_expired_rec.job_title, false);
1223 l_subject := fnd_message.get;
1224
1225 -- Build the body of the message both in text and html
1226 if send_notitfication_status = 'success' then
1227 -- get extend offer duration url
1228 l_url := get_extend_offer_duration_url
1229 (p_person_id => l_manager_id
1230 ,p_offer_id => offer_expired_rec.offer_id);
1231
1232
1233 -- build html body
1234 fnd_message.set_name('PER','IRC_OFFER_EXPIRED_MGR_HTML');
1235 fnd_message.set_token('APPLICANT_NAME',offer_expired_rec.applicant_name, false);
1236 fnd_message.set_token('VACANCY_NAME',offer_expired_rec.vacancy_name, false);
1237 l_html_body := fnd_message.get;
1238
1239 --
1240 l_html_body := l_html_body
1241 || '<BR><a HREF="'||l_url
1242 || '">'
1243 || 'Extend Duration'
1244 || '</a>'
1245 || '<BR>';
1246 --
1247 hr_utility.set_location(l_proc,80);
1248
1249 -- build text body
1250 fnd_message.set_name('PER','IRC_OFFER_EXPIRED_MGR_TEXT');
1251 fnd_message.set_token('APPLICANT_NAME',offer_expired_rec.applicant_name, false);
1252 fnd_message.set_token('VACANCY_NAME',offer_expired_rec.vacancy_name, false);
1253 l_text_body := fnd_message.get;
1254
1255 --
1256 l_text_body := l_text_body
1257 || '\n'||'Extend Duration'
1258 || '\n'||l_url
1259 || '\n';
1260 --
1261 else --- send_notitfication_status is failure
1262 -- build html body
1263 fnd_message.set_name('PER','IRC_OFFER_EXPIRED_MGR_HTML');
1264 fnd_message.set_token('APPLICANT_NAME',offer_expired_rec.applicant_name, false);
1265 fnd_message.set_token('VACANCY_NAME',offer_expired_rec.vacancy_name, false);
1266 l_html_body := fnd_message.get;
1267 l_html_body := l_html_body
1268 ||'However, <br/>'||fnd_message.get_string('PER','IRC_412059_NO_ROLE_4_PARTY')
1269 ||'<br/> Please Take Appropriate Action.';
1270
1271 -- build text body
1272 fnd_message.set_name('PER','IRC_OFFER_EXPIRED_MGR_TEXT');
1273 fnd_message.set_token('APPLICANT_NAME',offer_expired_rec.applicant_name, false);
1274 fnd_message.set_token('VACANCY_NAME',offer_expired_rec.vacancy_name, false);
1275 l_text_body := fnd_message.get;
1276 l_text_body := l_text_body
1277 ||'However, <br/>'||fnd_message.get_string('PER','IRC_412059_NO_ROLE_4_PARTY')
1278 ||'<br/> Please Take Appropriate Action.';
1279
1280 End if;
1281
1282 hr_utility.set_location(l_proc,90);
1283 ---- sending notification to the manager
1284 BEGIN
1285 if l_manager_id is not null then
1286 l_id := irc_notification_helper_pkg.send_notification
1287 ( p_person_id => l_manager_id
1288 , p_subject => l_subject
1289 , p_html_body => l_html_body
1290 , p_text_body => l_text_body
1291 , p_from_role => l_creator_name
1292 );
1293 end if;
1294 hr_utility.set_location(l_proc,100);
1295 EXCEPTION
1296 When OTHERS then
1297 fnd_file.put_line
1298 ( which => fnd_file.log
1299 ,buff => 'The Manager' || rpad(offer_expired_rec.manager_id,10,' ')||'dont have workflow role'
1300 );
1301
1302 fnd_file.put_line
1303 ( which => fnd_file.log
1304 ,buff => ' Failure Reason: '|| fnd_message.get_string('PER','IRC_412059_NO_ROLE_4_PARTY')
1305 );
1306
1307 END;
1308 --
1309 END IF;
1310
1311 --Set the offer status to 'CLOSED' for the expired offers
1312 irc_offers_api.close_offer
1313 ( p_validate => false
1314 ,p_effective_date => trunc(sysdate)
1315 ,p_offer_id => offer_expired_rec.offer_id
1316 ,p_change_reason => 'EXPIRED'
1317 ,p_status_change_date => trunc(sysdate)
1318 );
1319 hr_utility.set_location(l_proc,110);
1320
1321 if send_notitfication_status = 'success' then
1322 fnd_file.put_line
1323 ( which => fnd_file.log
1324 ,buff => rpad(processed_record_count ||'.',4,' ')
1325 || rpad(offer_expired_rec.vacancy_name,16,' ')
1326 || rpad(offer_expired_rec.offer_id,12,' ')
1327 || rpad(offer_expired_rec.applicant_id,13,' ')
1328 || rpad(rtrim(substr(rtrim(offer_expired_rec.applicant_name),1, INSTR(rtrim(offer_expired_rec.applicant_name), ',', 1, 1)-1)),27,' ')
1329 || lpad(send_notitfication_status,8,' ')
1330 );
1331 end if;
1332
1333
1334 end loop;
1335 --Reset applicantion's current session Language
1336 if l_intial_set_lang <> l_current_set_lang then
1337 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',l_intial_set_lang);
1338 end if;
1339 fnd_file.NEW_LINE(WHICH => fnd_file.log );
1340 fnd_file.put_line
1341 (which => fnd_file.log
1342 ,buff => 'Total Record Processed ' || processed_record_count ||
1343 ' Successful Records '|| (processed_record_count - failure_record_count) ||
1344 ' Error Records '|| failure_record_count
1345 );
1346 hr_utility.set_location(' Leaving:'||l_proc, 120);
1347 exception
1348 when others then
1349 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',l_intial_set_lang);
1350 --
1351 END send_expired_notification;
1352 --
1353 -- ----------------------------------------------------------------------------
1354 -- send_applicant_response --
1355 -- called internally to send notification about applicant response : --
1356 -- sends the notification to applicant and manager --
1357 -- ----------------------------------------------------------------------------
1358 --
1359 PROCEDURE send_applicant_response(itemtype in varchar2,
1360 itemkey in varchar2,
1361 actid in number,
1362 funcmode in varchar2,
1363 resultout out nocopy varchar2) is
1364 --
1365 Event wf_event_t;
1366 EventDocument CLOB;
1367 l_note CLOB;
1368
1369 l_id number;
1370 l_applicant_id number;
1371 l_offer_id number;
1372 l_manager_id number;
1373 l_recruiter_id number;
1374 l_creator_id number;
1375 l_referrer_id number;
1376 l_begining number;
1377 l_user_id number;
1378 l_end number := 1;
1379 l_offer_status_history_id number;
1380 l_creator_name varchar2(4000);
1381
1382 l_apl_response boolean := false;
1383 l_apl_accepted boolean := false;
1384
1385 l_note_text varchar2(32767);
1386 l_vacancy_name varchar2(4000);
1387 l_job_title varchar2(4000);
1388 l_applicant_name varchar2(4000);
1389 l_offer_status varchar2(4000);
1390 l_change_reason varchar2(4000);
1391 l_decline_reason varchar2(4000);
1392 l_decline_reason_meaning varchar2(4000);
1393 l_job_posting_title varchar2(4000);
1394 l_ipc_job_title varchar2(240);
1395 l_last_updated_by number;
1396
1397 l_subject varchar2(15599);
1398 l_html_body varchar2(15599);
1399 l_text_body varchar2(15599);
1400 l_url varchar2(4000);
1401 l_applicant_asg_id number;
1402
1403 l_proc varchar2(30) default '.send_applicant_response';
1404 l_current_set_lang varchar2(40);
1405 l_apl_lang varchar2(40);
1406 l_mgr_lang varchar2(40);
1407 l_intial_set_lang varchar2(40);
1408 l_ref_lang varchar2(40);
1409 --
1410 cursor csr_offer_notes (p_offer_status_history_id IN NUMBER) is
1411 select note.note_text
1412 from irc_notes note
1413 where note.offer_status_history_id = p_offer_status_history_id
1414 order by creation_date desc;
1415 --
1416 cursor csr_get_decline_reason (p_decline_reason IN VARCHAR2) is
1417 select meaning
1418 from hr_lookups
1419 where lookup_type = 'IRC_OFFER_DECLINE_REASON' and
1420 lookup_code = p_decline_reason;
1421 --
1422 cursor csr_get_apl_referrer_id (p_offer_id in number) is
1423 select iri.source_person_id
1424 from irc_referral_info iri,
1425 irc_offers iof
1426 where iri.object_id = iof.APPLICANT_ASSIGNMENT_ID
1427 and iri.object_type = 'APPLICATION'
1428 and iof.offer_id = p_offer_id;
1429 --
1430 BEGIN
1431 --
1432
1433 hr_utility.set_location(l_proc, 10);
1434 --Fetch applicantion's current session Language
1435 open get_nls_lang(userenv('LANG'));
1436 fetch get_nls_lang into l_intial_set_lang;
1437 close get_nls_lang;
1438
1439
1440 --Assign the session language to current set language.
1441 l_current_set_lang := l_intial_set_lang;
1442
1443
1444 IF (funcmode='RUN') THEN
1445
1446 hr_utility.set_location(l_proc, 20);
1447
1448 Event:=wf_engine.getActivityAttrEvent
1449 (itemtype => itemtype
1450 ,itemkey => itemkey
1451 ,actid => actid
1452 ,name => 'EVENT');
1453
1454 EventDocument:=Event.getEventData();
1455 l_offer_id:= to_number(irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_id'));
1456 l_offer_status:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_status');
1457 l_change_reason:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/change_reason');
1458 l_offer_status_history_id:=to_number(irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_status_history_id'));
1459
1460
1461 IF ( l_offer_status = 'CLOSED' and l_change_reason = 'APL_ACCEPTED') THEN
1462 l_apl_response := true;
1463 l_apl_accepted := true;
1464 ELSIF( l_offer_status = 'CLOSED' and l_change_reason = 'APL_DECLINED') THEN
1465 l_apl_response := true;
1466 l_apl_accepted := false;
1467 ELSE
1468 l_apl_response := false;
1469 END IF;
1470
1471 IF ( l_apl_response = true ) THEN
1472 --
1473 hr_utility.set_location(l_proc, 30);
1474
1475 open csr_send_apl_resp(l_offer_id);
1476 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_ipc_job_title,l_last_updated_by;
1477 close csr_send_apl_resp;
1478
1479 --get the user name for creator
1480 open csr_get_user_name(l_creator_id);
1481 fetch csr_get_user_name into l_creator_name;
1482 close csr_get_user_name;
1483
1484 --get the person id for creator
1485 open csr_get_user_employee_id(l_creator_id);
1486 fetch csr_get_user_employee_id into l_creator_id;
1487 close csr_get_user_employee_id;
1488
1489 hr_utility.set_location(l_proc, 40);
1490
1491 -- get the person id
1492 open csr_get_employee_id(l_applicant_id);
1493 fetch csr_get_employee_id into l_applicant_id;
1494 close csr_get_employee_id;
1495
1496 -- get the person id for referrer
1497 open csr_get_apl_referrer_id(l_offer_id);
1498 fetch csr_get_apl_referrer_id into l_referrer_id;
1499 close csr_get_apl_referrer_id;
1500
1501 hr_utility.set_location(l_proc, 50);
1502
1503 -- Fetch applicant pref language
1504
1505 open get_user_lang(l_applicant_id);
1506 fetch get_user_lang into l_apl_lang;
1507 close get_user_lang;
1508
1509
1510 --set the applicant pref language to the session
1511
1512 if l_current_set_lang <> l_apl_lang then
1513 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_apl_lang||'''');
1514 l_current_set_lang := l_apl_lang;
1515 end if;
1516
1517 -- build subject message for applicant
1518 fnd_message.set_name('PER','IRC_OFFER_RESPONSE_APL_SUBJECT');
1519 fnd_message.set_token('VACANCY_NAME',l_job_posting_title, false);
1520 fnd_message.set_token('JOB_TITLE',l_ipc_job_title, false);
1521 l_subject := fnd_message.get;
1522 --
1523
1524 --
1525 hr_utility.set_location(l_proc, 60);
1526
1527 open csr_offer_notes(l_offer_status_history_id);
1528 fetch csr_offer_notes into l_note;
1529 if csr_offer_notes%found then
1530 --
1531 -- convert clob data to varchar2
1532 --
1533 l_begining := DBMS_LOB.GETLENGTH(l_note);
1534 DBMS_LOB.READ(l_note, l_begining, l_end, l_note_text);
1535 end if;
1536 close csr_offer_notes;
1537
1538 -- build html body for applicant
1539 IF ( l_apl_accepted = true ) THEN
1540 fnd_message.set_name('PER','IRC_OFFER_ACCEPTED_APL_HTML');
1541 ELSE
1542 fnd_message.set_name('PER','IRC_OFFER_REJECTED_APL_HTML');
1543 END IF;
1544
1545 fnd_message.set_token('VACANCY_NAME',l_job_posting_title, false);
1546 fnd_message.set_token('JOB_TITLE',l_ipc_job_title, false);
1547 l_html_body := fnd_message.get || '<BR>' || l_note_text;
1548
1549 hr_utility.set_location(l_proc, 70);
1550
1551 -- build text body
1552 IF ( l_apl_accepted = true ) THEN
1553 fnd_message.set_name('PER','IRC_OFFER_ACCEPTED_APL_TEXT');
1554 ELSE
1555 fnd_message.set_name('PER','IRC_OFFER_REJECTED_APL_TEXT');
1556 END IF;
1557 fnd_message.set_token('VACANCY_NAME',l_job_posting_title, false);
1558 fnd_message.set_token('JOB_TITLE',l_ipc_job_title, false);
1559 l_text_body := fnd_message.get || '\n' || l_note_text;
1560
1561
1562 --
1563 hr_utility.set_location(l_proc, 80);
1564
1565 -- send notification
1566 if l_applicant_id is not null then
1567 l_id := irc_notification_helper_pkg.send_notification
1568 ( p_person_id => l_applicant_id
1569 , p_subject => l_subject
1570 , p_html_body => l_html_body
1571 , p_text_body => l_text_body
1572 , p_from_role => l_creator_name
1573 );
1574 end if;
1575 hr_utility.set_location(l_proc, 90);
1576
1577 --send notification to manager
1578 IF( l_manager_id is not null or l_recruiter_id is not null or l_creator_id is not null) THEN
1579
1580 hr_utility.set_location(l_proc, 100);
1581
1582 IF ( l_apl_accepted = false ) THEN
1583 -- get the decline reason
1584 l_decline_reason:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/decline_reason');
1585 open csr_get_decline_reason(l_decline_reason);
1586 fetch csr_get_decline_reason into l_decline_reason_meaning;
1587 close csr_get_decline_reason;
1588 END IF;
1589
1590 --fetch the resp. Manager pref. language
1591 open get_user_lang(l_manager_id);
1592 fetch get_user_lang into l_mgr_lang;
1593 close get_user_lang;
1594
1595 -- set the pref language of Manager to the session */
1596
1597 if l_current_set_lang <> l_mgr_lang then
1598 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_mgr_lang||'''');
1599 l_current_set_lang := l_mgr_lang;
1600 end if;
1601
1602 -- build subject message for manager
1603 IF ( l_apl_accepted = true ) THEN
1604 fnd_message.set_name('PER','IRC_OFFER_ACCEPTED_MGR_SUBJECT');
1605 ELSE
1606 fnd_message.set_name('PER','IRC_OFFER_REJECTED_MGR_SUBJECT');
1607 END IF;
1608 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1609 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1610 l_subject := fnd_message.get;
1611
1612 -- build html body for applicant
1613 IF ( l_apl_accepted = true ) THEN
1614 fnd_message.set_name('PER','IRC_OFFER_ACCEPTED_MGR_HTML');
1615 ELSE
1616 fnd_message.set_name('PER','IRC_OFFER_REJECTED_MGR_HTML');
1617 fnd_message.set_token('DECLINE_REASON',l_decline_reason_meaning, false);
1618 END IF;
1619
1620 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1621 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1622 l_html_body := fnd_message.get || '<BR>' || l_note_text;
1623 --
1624 hr_utility.set_location(l_proc, 120);
1625 --
1626 -- get the applicant_assignment_id
1627 open csr_get_apl_assignment_id (l_offer_id);
1628 fetch csr_get_apl_assignment_id into l_applicant_asg_id;
1629 close csr_get_apl_assignment_id;
1630 --
1631 hr_utility.set_location(l_proc, 130);
1632 --
1633 l_url := get_manager_view_offer_url( p_person_id =>l_applicant_id
1634 ,p_apl_asg_id =>l_applicant_asg_id);
1635 --
1636 hr_utility.set_location(l_proc, 140);
1637 --
1638 l_html_body := l_html_body
1639 || '<BR>Click <a HREF="'||l_url
1640 || '">'
1641 || 'here'
1642 || '</a> to view details.'
1643 || '<BR>';
1644 --
1645
1646 hr_utility.set_location(l_proc, 110);
1647
1648 -- build text body
1649 IF ( l_apl_accepted = true ) THEN
1650 fnd_message.set_name('PER','IRC_OFFER_ACCEPTED_MGR_TEXT');
1651 ELSE
1652 fnd_message.set_name('PER','IRC_OFFER_REJECTED_MGR_TEXT');
1653 fnd_message.set_token('DECLINE_REASON',l_decline_reason_meaning, false);
1654 END IF;
1655 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1656 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1657 l_text_body := fnd_message.get || '\n' || l_note_text;
1658 --
1659 l_text_body := l_text_body
1660 || '\n\n'||'View Details'
1661 || '\n'||l_url
1662 || '\n';
1663 --
1664 hr_utility.set_location(l_proc, 120);
1665
1666 -- send notification to manager
1667 IF( l_manager_id is not null ) THEN
1668 l_id := irc_notification_helper_pkg.send_notification
1669 ( p_person_id => l_manager_id
1670 , p_subject => l_subject
1671 , p_html_body => l_html_body
1672 , p_text_body => l_text_body
1673 , p_from_role => l_creator_name
1674 );
1675 hr_utility.set_location(l_proc, 130);
1676 END IF;
1677 -- send notification to recruiter
1678 IF( l_recruiter_id is not null
1679 and (l_manager_id is null or l_recruiter_id <> l_manager_id) ) THEN
1680 l_id := irc_notification_helper_pkg.send_notification
1681 ( p_person_id => l_recruiter_id
1682 , p_subject => l_subject
1683 , p_html_body => l_html_body
1684 , p_text_body => l_text_body
1685 , p_from_role => l_creator_name
1686 );
1687 hr_utility.set_location(l_proc, 140);
1688 END IF;
1689 -- send notification to creator
1690 IF( l_creator_id is not null
1691 and (l_recruiter_id is null or l_creator_id <> l_recruiter_id )
1692 and (l_manager_id is null or l_creator_id <> l_manager_id )) THEN
1693 l_id := irc_notification_helper_pkg.send_notification
1694 ( p_person_id => l_creator_id
1695 , p_subject => l_subject
1696 , p_html_body => l_html_body
1697 , p_text_body => l_text_body
1698 , p_from_role => l_creator_name
1699 );
1700 hr_utility.set_location(l_proc, 150);
1701
1702 END IF;
1703
1704 -- send notification to referrer
1705 IF( l_referrer_id is not null
1706 and l_apl_accepted = true
1707 and (l_creator_id is null or l_referrer_id <> l_creator_id)
1708 and (l_recruiter_id is null or l_referrer_id <> l_recruiter_id)
1709 and (l_manager_id is null or l_referrer_id <> l_manager_id) ) THEN
1710
1711 --fetch the referrer pref. language
1712 open get_user_lang(l_referrer_id);
1713 fetch get_user_lang into l_ref_lang;
1714 close get_user_lang;
1715
1716 -- set the pref language of referrer to the session
1717
1718 if l_current_set_lang <> l_mgr_lang then
1719 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_ref_lang||'''');
1720 l_current_set_lang := l_ref_lang;
1721 end if;
1722 -- build subject message for referrer
1723 fnd_message.set_name('PER','IRC_412441_OFR_ACCEPT_REF_SUB');
1724 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1725 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1726 l_subject := fnd_message.get;
1727
1728 -- build html body for applicant
1729 fnd_message.set_name('PER','IRC_412442_OFR_ACCEPT_REF_HTML');
1730 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1731 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1732 l_html_body := fnd_message.get;
1733
1734 hr_utility.set_location(l_proc, 155);
1735
1736 -- build text body
1737 fnd_message.set_name('PER','IRC_412509_OFR_ACCEPT_REF_TEXT');
1738 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1739 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1740 l_text_body := fnd_message.get;
1741 if l_referrer_id is not null then
1742 l_id := irc_notification_helper_pkg.send_notification
1743 ( p_person_id => l_referrer_id
1744 , p_subject => l_subject
1745 , p_html_body => l_html_body
1746 , p_text_body => l_text_body
1747 , p_from_role => l_creator_name
1748 );
1749 end if;
1750 end if;
1751 --
1752 END IF;
1753 END IF;
1754 --
1755 END IF;
1756 resultout:='COMPLETE';
1757 hr_utility.set_location(' Leaving:'||l_proc, 160);
1758 --Reset applicantion's current session Language
1759 if l_intial_set_lang <> l_current_set_lang then
1760 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_intial_set_lang||'''');
1761 end if;
1762
1763 exception
1764 when others then
1765 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_intial_set_lang||'''');
1766 --
1767 END send_applicant_response;
1768 --
1769 -- ----------------------------------------------------------------------------
1770 -- send_applicant_response --
1771 -- called internally to send notification about applicant response : --
1772 -- sends the notification to applicant and manager --
1773 -- ----------------------------------------------------------------------------
1774 --
1775 PROCEDURE send_onhold_notification(itemtype in varchar2,
1776 itemkey in varchar2,
1777 actid in number,
1778 funcmode in varchar2,
1779 resultout out nocopy varchar2) is
1780 --
1781 Event wf_event_t;
1782 EventDocument CLOB;
1783
1784 l_id number;
1785 l_offer_id number;
1786 l_creator_id number;
1787
1788 l_vacancy_name varchar2(4000);
1789 l_job_title varchar2(4000);
1790 l_applicant_name varchar2(4000);
1791 l_offer_status varchar2(4000);
1792 l_creator_name varchar2(4000);
1793
1794 l_subject varchar2(15599);
1795 l_html_body varchar2(15599);
1796 l_text_body varchar2(15599);
1797
1798 l_proc varchar2(30) default '.send_onhold_notification';
1799 l_current_set_lang varchar2(40);
1800 l_apl_lang varchar2(40);
1801 l_mgr_lang varchar2(40);
1802 l_intial_set_lang varchar2(40);
1803 --
1804 BEGIN
1805 --
1806
1807 hr_utility.set_location(l_proc, 10);
1808 --Fetch applicantion's current session Language
1809 open get_nls_lang(userenv('LANG'));
1810 fetch get_nls_lang into l_intial_set_lang;
1811 close get_nls_lang;
1812
1813
1814 --Assign the session language to current set language.
1815 l_current_set_lang := l_intial_set_lang;
1816 IF (funcmode='RUN') THEN
1817
1818 hr_utility.set_location(l_proc, 20);
1819
1820 Event:=wf_engine.getActivityAttrEvent
1821 (itemtype => itemtype
1822 ,itemkey => itemkey
1823 ,actid => actid
1824 ,name => 'EVENT');
1825
1826 EventDocument:=Event.getEventData();
1827 l_offer_id:= to_number(irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_id'));
1828 l_offer_status:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_status');
1829
1830
1831 hr_utility.set_location(l_proc, 30);
1832 open csr_onhold_offer(l_offer_id);
1833 fetch csr_onhold_offer into l_vacancy_name,l_creator_id,l_job_title,l_applicant_name;
1834 close csr_onhold_offer;
1835
1836 --get the user name for creator
1837 open csr_get_user_name(l_creator_id);
1838 fetch csr_get_user_name into l_creator_name;
1839 close csr_get_user_name;
1840
1841 --get the person id for creator
1842 open csr_get_user_employee_id(l_creator_id);
1843 fetch csr_get_user_employee_id into l_creator_id;
1844 close csr_get_user_employee_id;
1845
1846 hr_utility.set_location(l_proc, 40);
1847
1848 --send notification to manager
1849 IF(l_creator_id is not null) THEN
1850
1851 hr_utility.set_location(l_proc, 50);
1852
1853 --fetch the Manager pref. language
1854 open get_user_lang(l_creator_id);
1855 fetch get_user_lang into l_mgr_lang;
1856 close get_user_lang;
1857
1858 -- set the pref language of Manager to the session
1859
1860 if l_current_set_lang <> l_mgr_lang then
1861 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_mgr_lang||'''');
1862 l_current_set_lang := l_mgr_lang;
1863 end if;
1864
1865 -- build subject message for manager
1866 fnd_message.set_name('PER','IRC_OFFER_ONHOLD_MGR_SUBJECT');
1867 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1868 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1869 l_subject := fnd_message.get;
1870
1871 -- build html body for applicant
1872 fnd_message.set_name('PER','IRC_OFFER_ONHOLD_MGR_HTML');
1873 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1874 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1875 l_html_body := fnd_message.get;
1876
1877 -- build text body
1878 fnd_message.set_name('PER','IRC_OFFER_ONHOLD_MGR_TEXT');
1879 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1880 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1881 l_text_body := fnd_message.get;
1882
1883 --
1884 hr_utility.set_location(l_proc, 60);
1885
1886 -- send notification to creator
1887 IF( l_creator_id is not null ) THEN
1888 l_id := irc_notification_helper_pkg.send_notification
1889 ( p_person_id => l_creator_id
1890 , p_subject => l_subject
1891 , p_html_body => l_html_body
1892 , p_text_body => l_text_body
1893 , p_from_role => l_creator_name
1894 );
1895 hr_utility.set_location(l_proc, 70);
1896
1897 END IF;
1898 --
1899 END IF;
1900
1901 END IF;
1902 resultout:='COMPLETE';
1903 hr_utility.set_location(' Leaving:'||l_proc, 90);
1904 --Reset applicantion's current session Language
1905 if l_intial_set_lang <> l_current_set_lang then
1906 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_intial_set_lang||'''');
1907 end if;
1908
1909 exception
1910 when others then
1911 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_intial_set_lang||'''');
1912 --
1913 END send_onhold_notification;
1914 -- ----------------------------------------------------------------------------
1915 -- send_withdrawal_notification --
1916 -- called internally to send notification about offer withdrawal : --
1917 -- sends the notification to applicant and manager/recruiter --
1918 -- ----------------------------------------------------------------------------
1919 --
1920 PROCEDURE send_withdrawal_notification(itemtype in varchar2,
1921 itemkey in varchar2,
1922 actid in number,
1923 funcmode in varchar2,
1924 resultout out nocopy varchar2) is
1925 --
1926 Event wf_event_t;
1927 EventDocument CLOB;
1928 l_note CLOB;
1929
1930 l_id number;
1931 l_applicant_id number;
1932 l_offer_id number;
1933 l_manager_id number;
1934 l_recruiter_id number;
1935 l_creator_id number;
1936 l_begining number;
1937 l_user_id number;
1938 l_end number := 1;
1939 l_offer_status_history_id number;
1940 l_creator_name varchar2(4000);
1941 l_action_performer varchar2(4000);
1942 l_last_updated_by number;
1943 l_last_updated_emp_id number;
1944 l_mgr_withdraw boolean := false;
1945
1946 l_note_text varchar2(32767);
1947 l_vacancy_name varchar2(4000);
1948 l_job_title varchar2(4000);
1949 l_applicant_name varchar2(4000);
1950 l_offer_status varchar2(4000);
1951 l_change_reason varchar2(4000);
1952 l_prev_change_reason varchar2(4000);
1953 l_prev_offer_status varchar2(4000);
1954 l_withdrawal_reason varchar2(4000);
1955 l_withdrawal_reason_meaning varchar2(4000);
1956 l_hiring_manager varchar2(4000);
1957 l_recruiter varchar2(4000);
1958 l_job_posting_title varchar2(4000);
1959 l_ipc_job_title varchar2(240);
1960
1961 l_subject varchar2(15599);
1962 l_html_body varchar2(15599);
1963 l_text_body varchar2(15599);
1964 l_url varchar2(4000);
1965 l_applicant_asg_id number;
1966
1967 l_proc varchar2(30) default '.send_withdrawal_notification';
1968 l_current_set_lang varchar2(40);
1969 l_apl_lang varchar2(40);
1970 l_mgr_lang varchar2(40);
1971 l_intial_set_lang varchar2(40);
1972 --
1973 cursor csr_offer_notes (p_offer_status_history_id IN NUMBER) is
1974 select note.note_text
1975 from irc_notes note
1976 where note.offer_status_history_id = p_offer_status_history_id
1977 order by creation_date desc;
1978 --
1979 cursor csr_get_withdrawal_reason (p_withdrawal_reason IN VARCHAR2) is
1980 select meaning
1981 from hr_lookups
1982 where lookup_type = 'IRC_OFFER_WITHDRAWAL_REASON' and
1983 lookup_code = p_withdrawal_reason;
1984 --
1985 cursor csr_get_prev_offer_details (p_offer_status_history_id IN NUMBER,p_offer_id IN NUMBER) is
1986 select offer_status,change_reason
1987 from irc_offer_status_history
1988 where offer_id = p_offer_id
1989 and status_change_date = (select max(status_change_date)
1990 from irc_offer_status_history
1991 where offer_id = p_offer_id
1992 and offer_status_history_id <> p_offer_status_history_id);
1993 --
1994 CURSOR csr_get_name
1995 (p_person_id IN number)
1996 IS
1997 select ppf.full_name
1998 from per_all_people_f ppf
1999 where ppf.person_id = p_person_id
2000 and sysdate between effective_start_date and effective_end_date;
2001 --
2002
2003 --
2004 BEGIN
2005 --
2006
2007 hr_utility.set_location(l_proc, 10);
2008 --Fetch applicantion's current session Language
2009 open get_nls_lang(userenv('LANG'));
2010 fetch get_nls_lang into l_intial_set_lang;
2011 close get_nls_lang;
2012 --Assign the session language to current set language.
2013 l_current_set_lang := l_intial_set_lang;
2014
2015 IF (funcmode='RUN') THEN
2016
2017 hr_utility.set_location(l_proc, 20);
2018
2019 Event:=wf_engine.getActivityAttrEvent
2020 (itemtype => itemtype
2021 ,itemkey => itemkey
2022 ,actid => actid
2023 ,name => 'EVENT');
2024
2025 EventDocument:=Event.getEventData();
2026 l_offer_id:= to_number(irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_id'));
2027 l_offer_status:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_status');
2028 l_change_reason:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/change_reason');
2029 l_offer_status_history_id:=to_number(irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_status_history_id'));
2030
2031 open csr_get_prev_offer_details(l_offer_status_history_id,l_offer_id);
2032 fetch csr_get_prev_offer_details into l_prev_offer_status,l_prev_change_reason;
2033 close csr_get_prev_offer_details;
2034
2035
2036 IF ( l_offer_status = 'CLOSED' and l_change_reason = 'MGR_WITHDRAW') THEN
2037 l_mgr_withdraw := true;
2038 ELSE
2039 l_mgr_withdraw := false;
2040 END IF;
2041
2042 IF ( l_mgr_withdraw = true ) THEN
2043 --
2044 hr_utility.set_location(l_proc, 30);
2045
2046 open csr_send_apl_resp(l_offer_id);
2047 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_ipc_job_title,l_last_updated_by;
2048 close csr_send_apl_resp;
2049
2050 --get the user name for creator
2051 open csr_get_user_name(l_creator_id);
2052 fetch csr_get_user_name into l_creator_name;
2053 close csr_get_user_name;
2054
2055 -- get the recruiter name
2056 open csr_get_name(l_recruiter_id);
2057 fetch csr_get_name into l_recruiter;
2058 close csr_get_name;
2059
2060 -- get the manager name
2061 open csr_get_name(l_manager_id);
2062 fetch csr_get_name into l_hiring_manager;
2063 close csr_get_name;
2064
2065 --get the person id for creator
2066 open csr_get_user_employee_id(l_creator_id);
2067 fetch csr_get_user_employee_id into l_creator_id;
2068 close csr_get_user_employee_id;
2069
2070 hr_utility.set_location(l_proc, 40);
2071
2072 -- get the person id
2073 open csr_get_employee_id(l_applicant_id);
2074 fetch csr_get_employee_id into l_applicant_id;
2075 close csr_get_employee_id;
2076
2077 hr_utility.set_location(l_proc, 50);
2078
2079 open csr_offer_notes(l_offer_status_history_id);
2080 fetch csr_offer_notes into l_note;
2081 if csr_offer_notes%found then
2082 --
2083 -- convert clob data to varchar2
2084 --
2085 l_begining := DBMS_LOB.GETLENGTH(l_note);
2086 DBMS_LOB.READ(l_note, l_begining, l_end, l_note_text);
2087 end if;
2088 close csr_offer_notes;
2089
2090 hr_utility.set_location(l_proc, 55);
2091 -- Send notification to the candidate only when the offer has already been extended
2092
2093 if l_prev_offer_status='EXTENDED' or l_prev_offer_status='PENDING_EXTENDED_DURATION' or l_prev_change_reason='APL_ACCEPTED' then
2094
2095 -- Fetch applicant pref language
2096 open get_user_lang(l_applicant_id);
2097 fetch get_user_lang into l_apl_lang;
2098 close get_user_lang;
2099
2100 --set the applicant pref language to the session
2101 if l_current_set_lang <> l_apl_lang then
2102 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_apl_lang||'''');
2103 l_current_set_lang := l_apl_lang;
2104 end if;
2105
2106 -- build subject message for applicant
2107 fnd_message.set_name('PER','IRC_412572_OFFER_WITHDRAW_APL');
2108 fnd_message.set_token('JOB_TITLE',l_job_posting_title, false);
2109 l_subject := fnd_message.get;
2110 --
2111
2112 --
2113 hr_utility.set_location(l_proc, 60);
2114
2115 -- build html body for applicant
2116 fnd_message.set_name('PER','IRC_412573_WITHDRAWAL_APL_HTML');
2117 fnd_message.set_token('JOB_TITLE',l_job_posting_title, false);
2118 fnd_message.set_token('HIRING_MANAGER',l_hiring_manager, false);
2119 l_html_body := fnd_message.get || '<BR>' || l_note_text;
2120
2121 hr_utility.set_location(l_proc, 70);
2122
2123 -- build text body
2124 fnd_message.set_name('PER','IRC_412574_WITHDRAWAL_APL_TEXT');
2125 fnd_message.set_token('JOB_TITLE',l_job_posting_title, false);
2126 fnd_message.set_token('HIRING_MANAGER',l_hiring_manager, false);
2127
2128 l_text_body := fnd_message.get || '\n' || l_note_text;
2129
2130 --
2131 hr_utility.set_location(l_proc, 80);
2132
2133 -- send notification
2134 if l_applicant_id is not null then
2135 l_id := irc_notification_helper_pkg.send_notification
2136 ( p_person_id => l_applicant_id
2137 , p_subject => l_subject
2138 , p_html_body => l_html_body
2139 , p_text_body => l_text_body
2140 , p_from_role => l_creator_name
2141 );
2142 end if;
2143 hr_utility.set_location(l_proc, 90);
2144
2145 end if;
2146
2147 --send notification to manager
2148 IF( l_manager_id is not null or l_recruiter_id is not null or l_creator_id is not null) THEN
2149
2150 hr_utility.set_location(l_proc, 100);
2151
2152 --fetch the resp. Manager pref. language
2153 open get_user_lang(l_manager_id);
2154 fetch get_user_lang into l_mgr_lang;
2155 close get_user_lang;
2156
2157 -- set the pref language of Manager to the session */
2158 if l_current_set_lang <> l_mgr_lang then
2159 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_mgr_lang||'''');
2160 l_current_set_lang := l_mgr_lang;
2161 end if;
2162
2163 hr_utility.set_location('l_last_updated_by' || l_last_updated_by, 20);
2164
2165 open csr_get_user_employee_id(l_last_updated_by);
2166 fetch csr_get_user_employee_id into l_last_updated_emp_id;
2167 close csr_get_user_employee_id;
2168
2169 hr_utility.set_location('l_last_updated_emp_id' || l_last_updated_emp_id, 20);
2170
2171 open csr_get_name(l_last_updated_emp_id);
2172 fetch csr_get_name into l_action_performer;
2173 close csr_get_name;
2174
2175 hr_utility.set_location('l_action_performer' || l_action_performer, 20);
2176
2177 -- get the decline reason
2178 l_withdrawal_reason:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/decline_reason');
2179 open csr_get_withdrawal_reason(l_withdrawal_reason);
2180 fetch csr_get_withdrawal_reason into l_withdrawal_reason_meaning;
2181 close csr_get_withdrawal_reason;
2182
2183 -- build subject message for manager
2184 fnd_message.set_name('PER','IRC_412575_OFFER_WITHDRAW_MGR');
2185 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
2186 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
2187 l_subject := fnd_message.get;
2188
2189 -- build html body for applicant
2190 fnd_message.set_name('PER','IRC_412564_WITHDRAWAL_MGR_HTML');
2191 fnd_message.set_token('ACTION_PERFORMER',l_action_performer, false);
2192 fnd_message.set_token('WITHDRAWAL_REASON',l_withdrawal_reason_meaning, false);
2193 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
2194 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
2195 fnd_message.set_token('NOTES',l_note_text, false);
2196
2197 l_html_body := fnd_message.get;
2198 --
2199 -- get the applicant_assignment_id
2200 open csr_get_apl_assignment_id (l_offer_id);
2201 fetch csr_get_apl_assignment_id into l_applicant_asg_id;
2202 close csr_get_apl_assignment_id;
2203 --
2204 hr_utility.set_location(l_proc, 110);
2205 --
2206 l_url := get_manager_view_offer_url( p_person_id =>l_applicant_id
2207 ,p_apl_asg_id =>l_applicant_asg_id);
2208 --
2209 hr_utility.set_location(l_proc, 120);
2210 --
2211 l_html_body := l_html_body
2212 || '<BR>Click <a HREF="'||l_url
2213 || '">'
2214 || 'here'
2215 || '</a> to view details.'
2216 || '<BR>';
2217 --
2218 hr_utility.set_location(l_proc, 115);
2219
2220 -- build text body
2221 fnd_message.set_name('PER','IRC_412565_WITHDRAWAL_MGR_TEXT');
2222 fnd_message.set_token('ACTION_PERFORMER',l_action_performer, false);
2223 fnd_message.set_token('WITHDRAWAL_REASON',l_withdrawal_reason_meaning, false);
2224 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
2225 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
2226 fnd_message.set_token('NOTES',l_note_text, false);
2227
2228 l_text_body := fnd_message.get;
2229 --
2230 l_text_body := l_text_body
2231 || '\n\n'||'View Details'
2232 || '\n'||l_url
2233 || '\n';
2234 --
2235 hr_utility.set_location(l_proc, 120);
2236
2237 -- send notification to manager
2238 IF( l_manager_id is not null ) THEN
2239 l_id := irc_notification_helper_pkg.send_notification
2240 ( p_person_id => l_manager_id
2241 , p_subject => l_subject
2242 , p_html_body => l_html_body
2243 , p_text_body => l_text_body
2244 , p_from_role => l_creator_name
2245 );
2246 hr_utility.set_location(l_proc, 130);
2247 END IF;
2248 -- send notification to recruiter
2249 IF( l_recruiter_id is not null
2250 and (l_manager_id is null or l_recruiter_id <> l_manager_id) ) THEN
2251 l_id := irc_notification_helper_pkg.send_notification
2252 ( p_person_id => l_recruiter_id
2253 , p_subject => l_subject
2254 , p_html_body => l_html_body
2255 , p_text_body => l_text_body
2256 , p_from_role => l_creator_name
2257 );
2258 hr_utility.set_location(l_proc, 140);
2259 END IF;
2260 -- send notification to creator
2261 IF( l_creator_id is not null
2262 and (l_recruiter_id is null or l_creator_id <> l_recruiter_id )
2263 and (l_manager_id is null or l_creator_id <> l_manager_id )) THEN
2264 l_id := irc_notification_helper_pkg.send_notification
2265 ( p_person_id => l_creator_id
2266 , p_subject => l_subject
2267 , p_html_body => l_html_body
2268 , p_text_body => l_text_body
2269 , p_from_role => l_creator_name
2270 );
2271 hr_utility.set_location(l_proc, 150);
2272
2273 END IF;
2274
2275 END IF;
2276 END IF;
2277 --
2278 END IF;
2279 resultout:='COMPLETE';
2280 hr_utility.set_location(' Leaving:'||l_proc, 160);
2281 --Reset applicantion's current session Language
2282 if l_intial_set_lang <> l_current_set_lang then
2283 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_intial_set_lang||'''');
2284 end if;
2285
2286 exception
2287 when others then
2288 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_intial_set_lang||'''');
2289
2290 --
2291 END send_withdrawal_notification;
2292 --
2293 -- ----------------------------------------------------------------------------
2294 -- send_dcln_acptd_offer_notif --
2295 -- called internally to send notification about the applicant declining --
2296 -- offer after acceptance :
2297 -- sends the notification to applicant and manager/recruiter --
2298 -- ----------------------------------------------------------------------------
2299 --
2300 PROCEDURE send_dcln_acptd_offer_notif(itemtype in varchar2,
2301 itemkey in varchar2,
2302 actid in number,
2303 funcmode in varchar2,
2304 resultout out nocopy varchar2) is
2305 --
2306 Event wf_event_t;
2307 EventDocument CLOB;
2308 l_note CLOB;
2309
2310 l_id number;
2311 l_applicant_id number;
2312 l_offer_id number;
2313 l_manager_id number;
2314 l_recruiter_id number;
2315 l_creator_id number;
2316 l_begining number;
2317 l_user_id number;
2318 l_end number := 1;
2319 l_offer_status_history_id number;
2320 l_creator_name varchar2(4000);
2321 l_last_updated_by number;
2322 l_apl_dec_acpt boolean := false;
2323
2324 l_note_text varchar2(32767);
2325 l_vacancy_name varchar2(4000);
2326 l_job_title varchar2(4000);
2327 l_applicant_name varchar2(4000);
2328 l_change_reason varchar2(4000);
2329 l_offer_status varchar2(4000);
2330 l_decline_reason varchar2(4000);
2331 l_decline_reason_meaning varchar2(4000);
2332 l_hiring_manager varchar2(4000);
2333 l_recruiter varchar2(4000);
2334 l_job_posting_title varchar2(4000);
2335 l_ipc_job_title varchar2(240);
2336
2337 l_subject varchar2(15599);
2338 l_html_body varchar2(15599);
2339 l_text_body varchar2(15599);
2340 l_url varchar2(4000);
2341 l_applicant_asg_id number;
2342 l_proc varchar2(30) default '.send_dcln_acptd_offer_notif';
2343 l_current_set_lang varchar2(40);
2344 l_apl_lang varchar2(40);
2345 l_mgr_lang varchar2(40);
2346 l_intial_set_lang varchar2(40);
2347 --
2348 cursor csr_offer_notes (p_offer_status_history_id IN NUMBER) is
2349 select note.note_text
2350 from irc_notes note
2351 where note.offer_status_history_id = p_offer_status_history_id
2352 order by creation_date desc;
2353 --
2354 cursor csr_get_decline_reason (p_decline_reason IN VARCHAR2) is
2355 select meaning
2356 from hr_lookups
2357 where lookup_type = 'IRC_OFFER_DECLINE_REASON' and
2358 lookup_code = p_decline_reason;
2359 --
2360 cursor csr_get_prev_offer_details (p_offer_status_history_id IN NUMBER,p_offer_id IN NUMBER) is
2361 select offer_status,change_reason
2362 from irc_offer_status_history
2363 where offer_id = p_offer_id
2364 and status_change_date = (select max(status_change_date)
2365 from irc_offer_status_history
2366 where offer_id = p_offer_id
2367 and offer_status_history_id <> p_offer_status_history_id);
2368 --
2369 CURSOR csr_get_name
2370 (p_person_id IN number)
2371 IS
2372 select ppf.full_name
2373 from per_all_people_f ppf
2374 where ppf.person_id = p_person_id
2375 and sysdate between effective_start_date and effective_end_date;
2376 --
2377
2378 --
2379 BEGIN
2380 --
2381
2382 hr_utility.set_location(l_proc, 10);
2383
2384 --Fetch applicantion's current session Language
2385 open get_nls_lang(userenv('LANG'));
2386 fetch get_nls_lang into l_intial_set_lang;
2387 close get_nls_lang;
2388
2389
2390 --Assign the session language to current set language.
2391 l_current_set_lang := l_intial_set_lang;
2392
2393 IF (funcmode='RUN') THEN
2394
2395 hr_utility.set_location(l_proc, 20);
2396
2397 Event:=wf_engine.getActivityAttrEvent
2398 (itemtype => itemtype
2399 ,itemkey => itemkey
2400 ,actid => actid
2401 ,name => 'EVENT');
2402
2403 EventDocument:=Event.getEventData();
2404 l_offer_id:= to_number(irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_id'));
2405 l_offer_status:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_status');
2406 l_change_reason:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/change_reason');
2407 l_offer_status_history_id:=to_number(irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_status_history_id'));
2408
2409 IF ( l_offer_status = 'CLOSED' and l_change_reason = 'APL_DECLINED_ACCEPTANCE') THEN
2410 l_apl_dec_acpt := true;
2411 ELSE
2412 l_apl_dec_acpt := false;
2413 END IF;
2414
2415 IF ( l_apl_dec_acpt = true ) THEN
2416 --
2417 hr_utility.set_location(l_proc, 30);
2418
2419 open csr_send_apl_resp(l_offer_id);
2420 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_ipc_job_title,l_last_updated_by;
2421 close csr_send_apl_resp;
2422
2423 --get the user name for creator
2424 open csr_get_user_name(l_creator_id);
2425 fetch csr_get_user_name into l_creator_name;
2426 close csr_get_user_name;
2427
2428 -- get the recruiter name
2429 open csr_get_name(l_recruiter_id);
2430 fetch csr_get_name into l_recruiter;
2431 close csr_get_name;
2432
2433 -- get the manager name
2434 open csr_get_name(l_manager_id);
2435 fetch csr_get_name into l_hiring_manager;
2436 close csr_get_name;
2437
2438 --get the person id for creator
2439 open csr_get_user_employee_id(l_creator_id);
2440 fetch csr_get_user_employee_id into l_creator_id;
2441 close csr_get_user_employee_id;
2442
2443 hr_utility.set_location(l_proc, 40);
2444
2445 -- get the person id
2446 open csr_get_employee_id(l_applicant_id);
2447 fetch csr_get_employee_id into l_applicant_id;
2448 close csr_get_employee_id;
2449
2450 hr_utility.set_location(l_proc, 50);
2451
2452 open csr_offer_notes(l_offer_status_history_id);
2453 fetch csr_offer_notes into l_note;
2454 if csr_offer_notes%found then
2455 --
2456 -- convert clob data to varchar2
2457 --
2458 l_begining := DBMS_LOB.GETLENGTH(l_note);
2459 DBMS_LOB.READ(l_note, l_begining, l_end, l_note_text);
2460 end if;
2461 close csr_offer_notes;
2462
2463 -- get the decline reason
2464 l_decline_reason:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/decline_reason');
2465 open csr_get_decline_reason(l_decline_reason);
2466 fetch csr_get_decline_reason into l_decline_reason_meaning;
2467 close csr_get_decline_reason;
2468
2469 hr_utility.set_location(l_proc, 55);
2470
2471 -- Fetch applicant pref language
2472 open get_user_lang(l_applicant_id);
2473 fetch get_user_lang into l_apl_lang;
2474 close get_user_lang;
2475
2476 --set the applicant pref language to the session
2477 if l_current_set_lang <> l_apl_lang then
2478 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_apl_lang||'''');
2479 l_current_set_lang := l_apl_lang;
2480 end if;
2481
2482
2483 -- build subject message for applicant
2484 fnd_message.set_name('PER','IRC_412566_DECLINED_ACCEPT_APL');
2485 fnd_message.set_token('JOB_TITLE',l_job_posting_title, false);
2486 l_subject := fnd_message.get;
2487 --
2488
2489 --
2490 hr_utility.set_location(l_proc, 60);
2491
2492 -- build html body for applicant
2493 fnd_message.set_name('PER','IRC_412567_DEC_ACCEPT_APL_HTML');
2494 fnd_message.set_token('DECLINE_REASON',l_decline_reason_meaning, false);
2495 fnd_message.set_token('HIRING_MANAGER',l_hiring_manager, false);
2496 l_html_body := fnd_message.get || '<BR>' || l_note_text;
2497
2498 hr_utility.set_location(l_proc, 70);
2499
2500 -- build text body
2501 fnd_message.set_name('PER','IRC_412568_DEC_ACCEPT_APL_TEXT');
2502 fnd_message.set_token('DECLINE_REASON',l_decline_reason_meaning, false);
2503 fnd_message.set_token('HIRING_MANAGER',l_hiring_manager, false);
2504
2505 l_text_body := fnd_message.get || '\n' || l_note_text;
2506
2507 --
2508 hr_utility.set_location(l_proc, 80);
2509
2510 -- send notification
2511 if l_applicant_id is not null then
2512 l_id := irc_notification_helper_pkg.send_notification
2513 ( p_person_id => l_applicant_id
2514 , p_subject => l_subject
2515 , p_html_body => l_html_body
2516 , p_text_body => l_text_body
2517 , p_from_role => l_creator_name
2518 );
2519 end if;
2520 hr_utility.set_location(l_proc, 90);
2521
2522 end if;
2523
2524 --send notification to manager
2525 IF( l_manager_id is not null or l_recruiter_id is not null or l_creator_id is not null) THEN
2526
2527 hr_utility.set_location(l_proc, 100);
2528
2529 --fetch the resp. Manager pref. language
2530 open get_user_lang(l_manager_id);
2531 fetch get_user_lang into l_mgr_lang;
2532 close get_user_lang;
2533
2534 -- set the pref language of Manager to the session */
2535 if l_current_set_lang <> l_mgr_lang then
2536 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_mgr_lang||'''');
2537 l_current_set_lang := l_mgr_lang;
2538 end if;
2539
2540 -- build subject message for manager
2541 fnd_message.set_name('PER','IRC_412569_DECLINED_ACCEPT_MGR');
2542 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
2543 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
2544 l_subject := fnd_message.get;
2545
2546 -- build html body for applicant
2547 fnd_message.set_name('PER','IRC_412570_DEC_ACCEPT_MGR_HTML');
2548 fnd_message.set_token('DECLINE_REASON',l_decline_reason_meaning, false);
2549 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
2550 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
2551 fnd_message.set_token('NOTES',l_note_text, false);
2552
2553 l_html_body := fnd_message.get;
2554 -- get the applicant_assignment_id
2555 open csr_get_apl_assignment_id (l_offer_id);
2556 fetch csr_get_apl_assignment_id into l_applicant_asg_id;
2557 close csr_get_apl_assignment_id;
2558 --
2559 l_url := get_manager_view_offer_url( p_person_id =>l_applicant_id
2560 ,p_apl_asg_id =>l_applicant_asg_id);
2561 --
2562 l_html_body := l_html_body
2563 || '<BR>Click <a HREF="'||l_url
2564 || '">'
2565 || 'here'
2566 || '</a> to view details.'
2567 || '<BR>';
2568 --
2569 hr_utility.set_location(l_proc, 110);
2570 -- build text body
2571 fnd_message.set_name('PER','IRC_412571_DEC_ACCEPT_MGR_TEXT');
2572 fnd_message.set_token('DECLINE_REASON',l_decline_reason_meaning, false);
2573 fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
2574 fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
2575 fnd_message.set_token('NOTES',l_note_text, false);
2576 --
2577 l_text_body := fnd_message.get;
2578 --
2579 l_text_body := l_text_body
2580 || '\n\n'||'View Details'
2581 || '\n'||l_url
2582 || '\n';
2583 --
2584 hr_utility.set_location(l_proc, 120);
2585
2586 -- send notification to manager
2587 IF( l_manager_id is not null ) THEN
2588 l_id := irc_notification_helper_pkg.send_notification
2589 ( p_person_id => l_manager_id
2590 , p_subject => l_subject
2591 , p_html_body => l_html_body
2592 , p_text_body => l_text_body
2593 , p_from_role => l_creator_name
2594 );
2595 hr_utility.set_location(l_proc, 130);
2596 END IF;
2597 -- send notification to recruiter
2598 IF( l_recruiter_id is not null
2599 and (l_manager_id is null or l_recruiter_id <> l_manager_id) ) THEN
2600 l_id := irc_notification_helper_pkg.send_notification
2601 ( p_person_id => l_recruiter_id
2602 , p_subject => l_subject
2603 , p_html_body => l_html_body
2604 , p_text_body => l_text_body
2605 , p_from_role => l_creator_name
2606 );
2607 hr_utility.set_location(l_proc, 140);
2608 END IF;
2609 -- send notification to creator
2610 IF( l_creator_id is not null
2611 and (l_recruiter_id is null or l_creator_id <> l_recruiter_id )
2612 and (l_manager_id is null or l_creator_id <> l_manager_id )) THEN
2613 l_id := irc_notification_helper_pkg.send_notification
2614 ( p_person_id => l_creator_id
2615 , p_subject => l_subject
2616 , p_html_body => l_html_body
2617 , p_text_body => l_text_body
2618 , p_from_role => l_creator_name
2619 );
2620 hr_utility.set_location(l_proc, 150);
2621
2622 END IF;
2623
2624 END IF;
2625 --
2626 END IF;
2627 resultout:='COMPLETE';
2628 hr_utility.set_location(' Leaving:'||l_proc, 160);
2629
2630 --Reset applicantion's current session Language
2631 if l_intial_set_lang <> l_current_set_lang then
2632 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_intial_set_lang||'''');
2633 end if;
2634
2635 --- before end of proc
2636 exception
2637 when others then
2638 DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_intial_set_lang||'''');
2639
2640 --
2641 END send_dcln_acptd_offer_notif;
2642
2643 --
2644 END irc_offer_notifications_pkg;