[Home] [Help]
PACKAGE BODY: APPS.PER_IREC_ICD_ENRLL_PROCESS
Source
1 Package body PER_IREC_ICD_ENRLL_PROCESS as
2 /* $Header: periricd.pkb 120.18 2011/06/03 06:36:26 amikukum ship $ */
3
4 g_package varchar2(80) := 'PER_IREC_ICD_ENRLL_PROCESS';
5
6 -- ----------------------------------------------------------------------------
7 -- CURSORS
8 -- ----------------------------------------------------------------------------
9
10 -- ***************************************************************************
11 -- Cursor to get persons and vacancies in a business group employed in the BG
12 -- and not yet enrolled.
13 -- ***************************************************************************
14 cursor vacancypersonQuery(pBGId in number) is
15 select VACANCY_ID, person_id,assignment_id,
16 PER_IN_LER_ID, BUSINESS_GROUP_ID
17 from
18 (
19 SELECT
20 vac.VACANCY_ID,paaf.person_id,paaf.assignment_id,
21 PIL.PER_IN_LER_ID, PAAF.BUSINESS_GROUP_ID
22 FROM
23 PER_VACANCIES vac, per_all_assignments_f paaf,
24 BEN_PER_IN_LER pil, ben_ler_f ler
25 WHERE
26 vac.BUSINESS_GROUP_ID = pBGId and
27 paaf.BUSINESS_GROUP_ID = vac.BUSINESS_GROUP_ID and
28 pil.BUSINESS_GROUP_ID = paaf.BUSINESS_GROUP_ID and
29 ler.BUSINESS_GROUP_ID = paaf.BUSINESS_GROUP_ID and
33 sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND
30 paaf.vacancy_id = vac.Vacancy_Id and
31 paaf.assignment_type = 'E' AND
32 paaf.primary_flag='Y' and
34 NOT EXISTS ( SELECT ben.person_id FROM ben_prtt_enrt_rslt_f ben
35 where paaf.person_id = ben.person_id) AND
36 ler.typ_cd = 'IREC' AND
37 pil.LF_EVT_OCRD_DT BETWEEN ler.effective_start_date(+) AND ler.effective_end_date(+) and
38 ler.ler_id = pil.ler_id AND
39 pil.person_id=paaf.person_id AND
40 pil.PER_IN_LER_STAT_CD='STRTD' AND
41 pil.assignment_id = paaf.assignment_id
42 --
43 union
44 --including recruiting-area linked assignments
45 SELECT
46 pav.vacancy_id,paaf.person_id,paaf.assignment_id,
47 PIL.PER_IN_LER_ID, PAAF.BUSINESS_GROUP_ID
48 FROM
49 per_all_assignments_f paaf,
50 BEN_PER_IN_LER pil, ben_ler_f ler,
51 per_all_vacancies pav,
52 per_vac_linked_assignments pvla
53 WHERE
54 pvla.tgt_apl_asg_id = paaf.assignment_id and
55 sysdate between nvl(pvla.start_date,sysdate) and nvl(pvla.end_date,sysdate) and
56 pav.vacancy_id = pvla.vacancy_id and
57 paaf.BUSINESS_GROUP_ID = pBGId and
58 pil.BUSINESS_GROUP_ID = paaf.BUSINESS_GROUP_ID and
59 ler.BUSINESS_GROUP_ID = paaf.BUSINESS_GROUP_ID and
60 paaf.assignment_type = 'E' AND
61 paaf.primary_flag='Y' and
62 sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND
63 NOT EXISTS ( SELECT ben.person_id FROM ben_prtt_enrt_rslt_f ben
64 where paaf.person_id = ben.person_id) AND
65 ler.typ_cd = 'IREC' AND
66 pil.LF_EVT_OCRD_DT BETWEEN ler.effective_start_date(+) AND ler.effective_end_date(+) and
67 ler.ler_id = pil.ler_id AND
68 pil.person_id=paaf.person_id AND
69 pil.PER_IN_LER_STAT_CD='STRTD' AND
70 pil.assignment_id = paaf.assignment_id
71 )
72 order by
73 vacancy_id,person_id;
74
75 -- ***************************************************************************
76 -- Cursor to get all persons employed for the vacancy and not yet enrolled
77 -- ***************************************************************************
78 cursor personQuery(pVacancyId in number) is
79 SELECT
80 paaf.person_id, paaf.assignment_id, PIL.PER_IN_LER_ID,
81 PAAF.BUSINESS_GROUP_ID
82 FROM
83 per_all_assignments_f paaf,BEN_PER_IN_LER pil, ben_ler_f ler
84 WHERE
85 pil.BUSINESS_GROUP_ID = paaf.BUSINESS_GROUP_ID and
86 ler.BUSINESS_GROUP_ID = paaf.BUSINESS_GROUP_ID and
87 pVacancyId = nvl(paaf.vacancy_id,
88 (select vacancy_id
89 from per_vac_linked_assignments
90 where tgt_apl_asg_id = paaf.assignment_id
91 and sysdate between nvl(start_date,sysdate)
92 and nvl(end_date,sysdate) and rownum<2
93 )
94 ) and
95 paaf.assignment_type = 'E' AND
96 paaf.primary_flag='Y' and
97 sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND
98 NOT EXISTS ( SELECT ben.person_id FROM ben_prtt_enrt_rslt_f ben
99 where paaf.person_id = ben.person_id) and
100 ler.typ_cd = 'IREC' AND
101 pil.LF_EVT_OCRD_DT BETWEEN ler.effective_start_date(+) AND ler.effective_end_date(+) and
102 ler.ler_id = pil.ler_id AND
103 pil.person_id=paaf.person_id AND
104 pil.PER_IN_LER_STAT_CD='STRTD' AND
105 pil.assignment_id = paaf.assignment_id
106 order by
107 paaf.person_id;
108
109 -- ***************************************************************************
110 -- Functions to get the names for Person, Vacancy, Business Group
111 -- ***************************************************************************
112 --
113 function get_person_hire_date(p_assignment_id number)
114 return DATE is
115 --
116 l_hire_date DATE;
117 --
118 cursor c_hire_date is
119 select min(EFFECTIVE_START_DATE)
120 from PER_ALL_ASSIGNMENTS_F
121 where ASSIGNMENT_ID = p_assignment_id
122 and ASSIGNMENT_TYPE = 'E'
123 and PRIMARY_FLAG = 'Y';
124 --
125 begin
126 --
127 open c_hire_date;
128 fetch c_hire_date into l_hire_date;
129 if c_hire_date%FOUND then
130 close c_hire_date;
131 return l_hire_date;
132 else
133 close c_hire_date;
134 return null;
135 end if;
136 --
137 end get_person_hire_date;
138 function get_person_full_name(p_person_id number)
139 return varchar2 is
140 --
141 l_dummy varchar2(240);
142 cursor c_person_name is
143 select full_name
144 from per_all_people_f
145 where person_id = p_person_id
146 AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
147 --
148 begin
149 --
150 open c_person_name;
151 fetch c_person_name into l_dummy;
152 if c_person_name%FOUND then
153 close c_person_name;
154 return l_dummy;
155 else
156 close c_person_name;
157 return ' ';
158 end if;
159 --
160 end get_person_full_name;
161
162 function get_vacancy_name(p_vacancy_id number)
163 return varchar2 is
164 --
165 l_dummy varchar2(30);
166 cursor c_vacancy_name is
167 select name
168 from per_all_vacancies
169 where vacancy_id = p_vacancy_id;
170 --
171 begin
172 --
173 open c_vacancy_name;
174 fetch c_vacancy_name into l_dummy;
175 if c_vacancy_name%FOUND then
176 close c_vacancy_name;
177 return l_dummy;
178 else
179 close c_vacancy_name;
180 return ' ';
181 end if;
182 --
183 end get_vacancy_name;
184
185 function get_business_group_name(p_bg_id number)
186 return varchar2 is
187 --
188 l_dummy varchar2(240);
189 cursor c_bg_name is
190 SELECT haou.NAME
191 FROM HR_ORGANIZATION_UNITS haou, HR_ORGANIZATION_INFORMATION haoi
192 WHERE
193 haou.organization_id = haoi.organization_id AND
194 haoi.org_information_context = 'CLASS' AND
195 haoi.org_information1 = 'HR_BG' AND
196 haoi.org_information2 = 'Y' AND
197 not exists (select 1 from HR_ORGANIZATION_INFORMATION haoi2
198 where haou.organization_id=haoi2.organization_id
199 and haoi2.org_information_context='BG Recruitment'
200 and haoi2.org_information9='Y') AND
201 BUSINESS_GROUP_ID=p_bg_id;
202 --
203 begin
204 --
205 open c_bg_name;
206 fetch c_bg_name into l_dummy;
207 if c_bg_name%FOUND then
208 close c_bg_name;
209 return l_dummy;
210 else
211 close c_bg_name;
212 return ' ';
213 end if;
214 --
215 end get_business_group_name;
216
217 --
218 --
219 -- ----------------------------------------------------------------------------
220 -- is_offer_accepted_or_extended
221 --
222 --
223 -- ----------------------------------------------------------------------------
224 --
225 function is_offer_accepted_or_extended
226 ( pPersonId in number
227 , pAssignmentId in number)
228 return Boolean
229 is
230 --
231 l_proc varchar2(80) default g_package||'.is_offer_accepted_or_extended';
232 l_person_name_with_id varchar2(300);
233 l_description varchar2(500);
234 l_offer_status irc_offer_status_history.offer_status%TYPE;
235 l_change_reason irc_offer_status_history.change_reason%TYPE;
236 l_decline_reason irc_offer_status_history.decline_reason%TYPE;
237 cursor csr_offer_status is
238 select * from(
239 select ofrhis.offer_status, ofrhis.change_reason, ofrhis.decline_reason
240 from irc_offer_status_history ofrhis, irc_offers ofr, per_all_assignments_f paaf
241 where paaf.assignment_id = pAssignmentId and ofr.applicant_assignment_id = paaf.assignment_id
242 and ofrhis.offer_id = ofr.offer_id and ofr.latest_offer='Y'
243 and sysdate between paaf.effective_start_date and paaf.effective_end_date order by status_change_date desc)
244 where rownum<2;
245 --
246 BEGIN
247 --
248 hr_utility.set_location('Entering'||l_proc, 10);
249 l_person_name_with_id:=get_person_full_name(pPersonId)||'(person_id='||pPersonId||')';
250 open csr_offer_status;
251 fetch csr_offer_status into l_offer_status,
252 l_change_reason,
253 l_decline_reason;
254 --
255 if csr_offer_status%notfound then
256 --
257 close csr_offer_status;
258 --
259 return false;
260 else
261 close csr_offer_status;
262
263 -- check if the offer is in accepted state
264 -- Also check the change reason is 'APL_HIRED'
265 -- Note : ('APL_HIRED' is introduced with patch 6006935)
266 --
267 if (l_offer_status='CLOSED' and l_change_reason in ('APL_ACCEPTED','APL_HIRED'))
268 or l_offer_status='EXTENDED' THEN
269 return true;
270 end if;
271
272 fnd_message.set_name('PER','IRC_412236_INV_OFFER_STATUS');
273 fnd_message.set_token('PERSON_NAME',l_person_name_with_id);
274 l_description:=substrb(fnd_message.get,1,500);
275 fnd_message.clear;
276
277 fnd_file.put_line(fnd_file.log,l_description);
278 --REVIEW : The enrollment process did not run for AAAAAA for the following reason.
279 -- The person did not have an offer extended OR
280 -- The person declined the offer.
281 return false;
282 hr_utility.set_location(' Leaving:'||l_proc, 130);
283 end if;
284 --
285 END is_offer_accepted_or_extended;
286 --
287 --
288 --
289 --
290 -- ----------------------------------------------------------------------------
291 -- is_person_future_terminated
292 --
293 --
294 -- ----------------------------------------------------------------------------
295 --
296 function is_person_future_terminated
297 ( pPersonId in number)
298 return Boolean
299 is
300 --
301 l_proc varchar2(80) default g_package||'.is_person_future_terminated';
302 l_person_name varchar2(300);
306 SELECT ppf.full_name
303 l_description varchar2(500);
304
305 cursor csr_future_person_rec is
307 FROM per_all_people_f ppf
308 WHERE ppf.person_id = pPersonId and
309 ppf.effective_end_date > To_Date('30-12-4712','dd-mm-yyyy') and
310 ppf.current_employee_flag IS null;
311 --
312 BEGIN
313 --
314 hr_utility.set_location('Entering'||l_proc, 10);
315
316 open csr_future_person_rec;
317 fetch csr_future_person_rec into l_person_name;
318 --
319 if csr_future_person_rec%notfound then
320 --
321 close csr_future_person_rec;
322 --
323 return false;
324 else
325 close csr_future_person_rec;
326
327 fnd_message.set_name('PER','IRC_412243_INVALID_PERSON_ID');
328 fnd_message.set_token('PERSON_NAME',l_person_name||'(person_id='||pPersonId||')');
329 l_description:=substrb(fnd_message.get,1,500);
330 fnd_message.clear;
331
332 fnd_file.put_line(fnd_file.log,l_description);
333 return true;
334 end if;
335 --
336 END is_person_future_terminated;
337 --
338 --
339 --
340 -- ----------------------------------------------------------------------------
341 -- populate_pay_elements
342 -- called from concurrent process to populate Pay Elements :
343 --
344 -- ----------------------------------------------------------------------------
345 --
346 PROCEDURE populate_pay_elements
347 ( errbuf out nocopy varchar2
348 , retcode out nocopy number
349 , pBgId in number
350 , pVacancyid in number default null
351 , pPersonId in number default null)
352 is
353 --
354 l_proc varchar2(80) default g_package||'.populate_pay_elements';
355 l_person_name varchar2(240);
356 l_vacancy_name varchar2(30);
357 l_bg_name varchar2(240);
358 l_description_start varchar2(500);
359 l_description_end varchar2(500);
360 --
361 BEGIN
362 hr_utility.set_location('Entering'||l_proc,10);
363 hr_utility.set_location(' Person Id:'||pPersonId||' Vacancy Id:'||pVacancyid||' BusinessGroup Id:'||pBgId, 20);
364
365 hr_utility.set_location('Calling the CP with UserName:'||fnd_global.user_name||',UserId='||fnd_global.user_id,30);
366 hr_utility.set_location('Responsibility Application Id:'||fnd_global.resp_appl_id||',Responsibility Id:'||fnd_global.resp_id,40);
367 hr_utility.set_location('Security Group Id:'||fnd_global.security_group_id, 50);
368
369 fnd_message.set_name('PER','IRC_412237_CRT_ENRLM_STRTD');
370 l_description_start:=substrb(fnd_message.get,1,500);
371 fnd_message.clear;
372
373 fnd_message.set_name('PER','IRC_412238_CRT_ENRLM_CMPLTD');
374 l_description_end:=substrb(fnd_message.get,1,500);
375 fnd_message.clear;
376
377 if pPersonId is not null THEN
378 l_person_name:=get_person_full_name(pPersonId);
379 l_vacancy_name:=get_vacancy_name(pVacancyid);
380 l_bg_name:=get_business_group_name(pBgId);
381 fnd_file.put_line(fnd_file.log,l_description_start||
382 '
383 Person :'||l_person_name||
384 '
385 Person Id :'||pPersonId);
386 fnd_file.put_line(fnd_file.log,
387 'Vacancy :'||l_vacancy_name||
388 '
389 Vacancy Id :'||pVacancyId);
390 fnd_file.put_line(fnd_file.log,
391 'Business Group :'||l_bg_name||
392 '
393 Business Group Id :'||pBgId);
394 fnd_file.put_line(fnd_file.log,
395 '+---------------------------------------------------------------------------+');
396 --REVIEW: The iRecruitment Create Enrollment process is started.
397 -- Person : XXXXXX
398 -- Person Id : 121212
399 populate_for_person(errbuf, retcode, pPersonId);
400 fnd_file.put_line(fnd_file.log, l_description_end);
401 --REVIEW: The iRecruitment Create Enrollment process is completed.
402
403 else
404 if pVacancyid is not null THEN
405 l_vacancy_name:=get_vacancy_name(pVacancyid);
406 l_bg_name:=get_business_group_name(pBgId);
407 fnd_file.put_line(fnd_file.log,l_description_start||
408 '
409 Vacancy :'||l_vacancy_name||
410 '
411 Vacancy Id :'||pVacancyid);
412 fnd_file.put_line(fnd_file.log,
413 'Business Group :'||l_bg_name||
414 '
415 Business Group Id :'||pBgId);
416 fnd_file.put_line(fnd_file.log,
417 '+---------------------------------------------------------------------------+');
418 populate_for_vacancy(errbuf, retcode, pVacancyid);
419 fnd_file.put_line(fnd_file.log, l_description_end);
420 else
421 if pBgId is not null THEN
422 l_bg_name:=get_business_group_name(pBgId);
423 fnd_file.put_line(fnd_file.log,l_description_start||
424 '
425 Business Group :'||l_bg_name||
426 '
427 Business Group Id :'||pBgId);
428 fnd_file.put_line(fnd_file.log,
429 '+---------------------------------------------------------------------------+');
430 populate_for_bg(errbuf, retcode, pBgId);
431 fnd_file.put_line(fnd_file.log, l_description_end);
432 else
433 fnd_message.set_name('PER','HR_289541_PJU_INV_BG_ID');
434 fnd_message.raise_error;
435 end if;
436 end if;
437 end if;
438 hr_utility.set_location('retcode='||retcode||' and errbuf='||errbuf, 60);
439 hr_utility.set_location(' Leaving:'||l_proc, 70);
440 --
441 END populate_Pay_Elements;
442 --
443
444 --
445 --
446 -- ----------------------------------------------------------------------------
447 -- populate_for_bg
448 --
449 --
450 -- ----------------------------------------------------------------------------
451 --
452 PROCEDURE populate_for_bg
453 ( errbuf out nocopy varchar2
454 , retcode out nocopy number
455 , pBgId in number)
456 is
457 --
458 l_proc varchar2(80) default g_package||'.populate_for_bg';
459 --
460 BEGIN
461 --
462 hr_utility.set_location('Entering'||l_proc, 10);
463 for vacancypersonQuery_rec in vacancypersonQuery(pBgId) LOOP
464 fnd_file.put_line(fnd_file.log,
465 '+---------------------------------------------------------------------------+');
466 if is_person_future_terminated(vacancypersonQuery_rec.person_id) then
467 --do not run the process if the person is terminated in future.
468 hr_utility.set_location('Entering'||l_proc, 20);
469 else
470 if is_offer_accepted_or_extended(
471 vacancypersonQuery_rec.person_id,
472 vacancypersonQuery_rec.assignment_id)
473 then
474 run_enrollment(errbuf, retcode,
475 vacancypersonQuery_rec.person_id,
476 vacancypersonQuery_rec.assignment_id,
477 vacancypersonQuery_rec.PER_IN_LER_id,
478 vacancypersonQuery_rec.BUSINESS_GROUP_id);
479 end if;
480 end if;
481 fnd_file.put_line(fnd_file.log,
482 '+---------------------------------------------------------------------------+');
483 end loop;
484 hr_utility.set_location(' Leaving:'||l_proc, 20);
485 --
486 END populate_for_bg;
487 --
488
489 --
490 --
491 -- ----------------------------------------------------------------------------
492 -- populate_for_vacancy
493 --
494 --
495 -- ----------------------------------------------------------------------------
496 --
497 PROCEDURE populate_for_vacancy
498 ( errbuf out nocopy varchar2
499 , retcode out nocopy number
500 , pVacancyId in number)
501 is
502 --
503 l_proc varchar2(80) default g_package||'.populate_for_vacancy';
504 --
505 BEGIN
506 --
507 hr_utility.set_location('Entering'||l_proc, 10);
508 for personQuery_rec in personQuery(pVacancyId) LOOP
509 fnd_file.put_line(fnd_file.log,
510 '+---------------------------------------------------------------------------+');
511
512 if is_person_future_terminated(personQuery_rec.person_id) then
513 --do not run the process if the person is terminated in future.
514 hr_utility.set_location('Entering'||l_proc, 20);
515 else
516 if is_offer_accepted_or_extended(
517 personQuery_rec.person_id,
518 personQuery_rec.assignment_id)
519 then
520 run_enrollment(errbuf, retcode,
521 personQuery_rec.person_id,
522 personQuery_rec.assignment_id,
523 personQuery_rec.PER_IN_LER_id,
524 personQuery_rec.BUSINESS_GROUP_id);
525 end if;
526 end if;
527 fnd_file.put_line(fnd_file.log,
528 '+---------------------------------------------------------------------------+');
529
530 end loop;
531 hr_utility.set_location(' Leaving:'||l_proc, 20);
532 --
533 END populate_for_vacancy;
534 --
535
536 --
537 --
538 -- ----------------------------------------------------------------------------
539 -- populate_for_person
540 --
541 --
542 -- ----------------------------------------------------------------------------
543 --
544 PROCEDURE populate_for_person
545 ( errbuf out nocopy varchar2
546 , retcode out nocopy number
547 , pPersonId in number)
548 is
549 --
550 l_proc varchar2(80) default g_package||'.populate_for_person';
551 l_assignment_id number;
552 l_per_in_ler_id NUMBER;
553 l_business_group_id NUMBER;
554 l_description varchar2(500);
555 cursor csr_assignment is
556 select PAAF.assignment_id,PAAF.BUSINESS_GROUP_ID,PIL.PER_IN_LER_ID
557 from per_all_assignments_f PAAF, BEN_PER_IN_LER pil, ben_ler_f ler
558 where PAAF.person_id = pPersonId and PAAF.assignment_type='E' and
559 paaf.primary_flag='Y' and
560 pil.BUSINESS_GROUP_ID = paaf.BUSINESS_GROUP_ID and
561 ler.BUSINESS_GROUP_ID = paaf.BUSINESS_GROUP_ID and
562 SYSDATE BETWEEN PAAF.effective_start_date AND PAAF.effective_end_date AND
563 NOT EXISTS ( SELECT ben.person_id FROM ben_prtt_enrt_rslt_f ben
564 where paaf.person_id = ben.person_id AND ROWNUM = 1) AND
565 ler.typ_cd = 'IREC' AND
566 pil.LF_EVT_OCRD_DT BETWEEN ler.effective_start_date(+) AND ler.effective_end_date(+) and
567 ler.ler_id = pil.ler_id AND pil.person_id=paaf.person_id AND
568 pil.PER_IN_LER_STAT_CD='STRTD' AND
569 pil.assignment_id = paaf.assignment_id;
570 --
571 BEGIN
572 --
573 hr_utility.set_location('Entering'||l_proc, 10);
574 fnd_file.put_line(fnd_file.log,
575 '+---------------------------------------------------------------------------+');
576
577 open csr_assignment;
578 fetch csr_assignment into l_assignment_id,l_business_group_id,l_per_in_ler_id;
579 --
580 if csr_assignment%notfound then
581 --
582 close csr_assignment;
583 fnd_message.set_name('PER','IRC_412239_INV_OFFER_COMP');
584 fnd_message.set_token('PERSON_NAME',get_person_full_name(pPersonId)||'(person_id='||pPersonId||')');
585 l_description:=substrb(fnd_message.get,1,500);
586 fnd_message.clear;
587
588 fnd_file.put_line(fnd_file.log,l_description);
589 hr_utility.set_location(l_proc,20);
590 --
591 else
592 close csr_assignment;
593
594 if is_person_future_terminated(pPersonid) then
595 --do not run the process if the person is terminated in future.
596 hr_utility.set_location(l_proc,30);
597 else
598 if is_offer_accepted_or_extended(pPersonid,l_assignment_id) then
599 run_enrollment(errbuf, retcode,
600 pPersonId,
601 l_assignment_id,
602 l_per_in_ler_id,
603 l_business_group_id);
604 end if;
605 end if;
606 end if;
607 hr_utility.set_location(' Leaving:'||l_proc, 30);
608 fnd_file.put_line(fnd_file.log,
609 '+---------------------------------------------------------------------------+');
610 --
611 END populate_for_person;
612 --
613
614 --
615 --
616 -- ----------------------------------------------------------------------------
617 -- run_enrollment
618 --
619 --
620 -- ----------------------------------------------------------------------------
621 --
622 PROCEDURE run_enrollment
623 ( errbuf out nocopy varchar2
624 , retcode out nocopy number
625 , pPersonId in number
626 , pAssignmentId in number
627 , pPerInLerId in number
628 , pBgId in number)
629 is
630 --
631 l_person_name varchar2(240);
632 l_prt_id number(15);
633 l_person_name_with_id varchar2(260);
634 l_description VARCHAR2(500);
635 l_hire_date DATE;
636 l_proc varchar2(80) default g_package||'.run_enrollment';
637 cursor c_participation is
638 select prtt_enrt_rslt_id
639 from ben_prtt_enrt_rslt_f
643 hr_utility.set_location('Entering '||l_proc, 10);
640 where person_id = pPersonId;
641 BEGIN
642 --
644 l_person_name_with_id:=get_person_full_name(pPersonId)||'(person_id='||pPersonId||')';
645 l_person_name:=get_person_full_name(pPersonId);
646 l_hire_date:=get_person_hire_date(pAssignmentId);
647
648 fnd_message.set_name('PER','IRC_412240_CRT_ENRLM_INITIAT');
649 fnd_message.set_token('PERSON_NAME',l_person_name_with_id);
650 fnd_message.set_token('HIRE_DATE',l_hire_date);
651 l_description:=substrb(fnd_message.get,1,500);
652 fnd_message.clear;
653
654 fnd_file.put_line(fnd_file.log, l_description);
655
656 ben_irec_process.create_enrollment_for_irec(
657 p_irec_per_in_ler_id => pPerInLerId
658 ,p_person_id => pPersonId
659 ,p_business_group_id => pBgId
660 ,p_effective_date => l_hire_date);
661 open c_participation;
662 fetch c_participation into l_prt_id;
663 --
664 if c_participation%FOUND then
665 --
666 close c_participation;
667 -- as the life event run is enrolled successfully set the life_run status to PROCD
668 update ben_per_in_ler
669 set per_in_ler_stat_cd = 'PROCD',
670 PROCD_DT = Trunc(sysdate)
671 where per_in_ler_id = pPerInLerId;
672 -- issue commit so that data is committed.
673 commit;
674
675 fnd_message.set_name('PER','IRC_412241_CRT_ENRLM_SUCCESS');
676 fnd_message.set_token('PERSON_NAME',l_person_name);
677 l_description:=substrb(fnd_message.get,1,500);
678 fnd_message.clear;
679
680 fnd_file.put_line(fnd_file.log, l_description);
681 --
682 else
683 --
684 close c_participation;
685 -- Set the return parameters to indicate failure
686 errbuf:=fnd_message.get;
687
688 fnd_message.set_name('PER','IRC_412242_CRT_ENRLM_ERRORED');
689 fnd_message.set_token('PERSON_NAME',l_person_name);
690 l_description:=substrb(fnd_message.get,1,500);
691 fnd_message.clear;
692
693 fnd_file.put_line(fnd_file.log, l_description||
694 '
695 '||errbuf);
696 retcode := 2;
697 --
698 end if;
699 --
700 --
701 hr_utility.set_location('Leaving '||l_proc, 20);
702 EXCEPTION
703 --
704 when others then
705 --
706 hr_utility.set_location(substr(SQLERRM,1,30),1234);
707 hr_utility.set_location(substr(SQLERRM,31,60),1235);
708 hr_utility.set_location(substr(SQLERRM,61,90),1236);
709 hr_utility.set_location(substr(SQLERRM,91,120),1237);
710 errbuf:=fnd_message.get;
711 --
712 -- Set the return parameters to indicate failure
713 --
714 fnd_message.set_name('PER','IRC_412242_CRT_ENRLM_ERRORED');
715 fnd_message.set_token('PERSON_NAME',l_person_name);
716 l_description:=substrb(fnd_message.get,1,500);
717 fnd_message.clear;
718
719 fnd_file.put_line(fnd_file.log, l_description||
720 '
721 '||errbuf);
722 --REVIEW : The enrollment process errored out for the person XXXXXXX with the following message.
723 -- ERROR : <TOKEN FOR ERROR MESSAGE>
724
725 retcode := 2;
726 hr_utility.set_location('Leaving '||l_proc, 30);
727 END run_enrollment;
728 --
729 --
730 END PER_IREC_ICD_ENRLL_PROCESS;