[Home] [Help]
PACKAGE BODY: APPS.BEN_ON_LINE_LF_EVT
Source
1 PACKAGE BODY ben_on_line_lf_evt AS
2 /* $Header: benollet.pkb 120.8 2006/07/10 07:22:14 vborkar ship $ */
3 --
4 g_package varchar2(80) := 'ben_on_line_lf_evt';
5 g_benefit_action_id number;
6 --
7 PROCEDURE error_simulation is
8 begin
9 fnd_message.set_name('BEN', 'BEN_91009_NAME_NOT_UNIQUE');
10 fnd_message.raise_error;
11 end;
12
13 --
14 -- Procedure
15 -- Start_on_line_lf_evt_proc
16 --
17 -- Description
18 -- Start the on line life event workflow process for the given p_person_id, p_effective_date and
19 -- p_business_group_id
20 --
21 PROCEDURE Start_on_line_lf_evt_proc( p_person_id in number,
22 p_effective_date in date,
23 p_business_group_id in number,
24 p_error_msg in varchar2,
25 p_userkey in varchar2,
26 p_itemkey out nocopy varchar2) is
27 --
28 l_ItemType varchar2(30) := 'BEN_OLLE';
29 l_ItemKey varchar2(30) := to_char(p_person_id);
30 l_load_form varchar2(100);
31 --
32 -- 9999Sequence number to append the person_id so that many process instances can be
33 -- launched for a single person.
34 --
35 -- Cursor C_Seq is select ben_on_line_lf_evt_s.nextval from sys.dual;
36 --
37 Cursor C_Seq is
38 select to_char(WF_ERROR_PROCESSES_S.NEXTVAL)
39 from SYS.DUAL;
40 --
41 ItemKey varchar2(240);
42 username varchar2(50) := fnd_profile.value('USERNAME');
43 ItemType varchar2(30) := 'BEN_OLLE';
44 process varchar2(100) := 'ON_LINE_LIFE_EVENT_PROC';
45 l_ben_seq number;
46 l_package varchar2(80) := g_package||'.Start_on_line_lf_evt_proc';
47 --
48 begin
49 --
50 hr_utility.set_location ('Entering '||l_package,05);
51 --
52 --
53 -- Generate a new itemkey
54 --
55 Open C_Seq;
56 Fetch C_Seq Into itemkey;
57 Close C_Seq;
58 --
59 -- Create and start the process
60 Wf_Engine.CreateProcess(itemtype, itemkey, process);
61 Wf_Engine.SetItemUserKey(itemtype, itemkey, p_userkey);
62 Wf_Engine.SetItemOwner(itemtype, itemkey, username);
63 --
64 -- Populate "special" attributes, if they exist
65 begin
66 Wf_Engine.SetItemAttrText(itemtype, itemkey, 'USER_NAME',
67 fnd_profile.value('USERNAME'));
68 Wf_Engine.SetItemAttrText(itemtype, itemkey, 'USER_ID',
69 fnd_profile.value('USER_ID'));
70 Wf_Engine.SetItemAttrText(itemtype, itemkey, 'RESP_ID',
71 fnd_profile.value('RESP_ID'));
72 Wf_Engine.SetItemAttrText(itemtype, itemkey, 'RESP_APPL_ID',
73 fnd_profile.value('RESP_APPL_ID'));
74 exception
75 when others then
76 null;
77 end;
78 --
79 -- Create and set the special mail-suppression itemattr.
80 -- This attr prevents mail from being sent to process originator.
81 --
82 Wf_Engine.AddItemAttr(itemtype, itemkey, '.MAIL_QUERY');
83 Wf_Engine.SetItemAttrText(itemtype, itemkey, '.MAIL_QUERY',
84 username);
85 --
86 --
87 wf_engine.SetItemAttrText( itemtype => ItemType,
88 itemkey => Itemkey,
89 aname => 'PERSON_ID',
90 avalue => to_char(p_person_id));
91 --
92 wf_engine.SetItemAttrText(itemtype => itemtype,
93 itemkey => itemkey,
94 aname => 'EFFECTIVE_DATE',
95 avalue => to_char(p_effective_date, 'DD/MM/YYYY'));
96 --
97 wf_engine.SetItemAttrText(itemtype => itemtype,
98 itemkey => itemkey,
99 aname => 'BUSINESS_GROUP_ID',
100 avalue => to_char(p_business_group_id));
101 --
102 wf_engine.SetItemAttrText(itemtype => itemtype,
103 itemkey => itemkey,
104 aname => 'BEN_IA_CONTEXT_SET',
105 avalue => 'Y');
106 --
107 Wf_Engine.StartProcess(itemtype, itemkey);
108 --
109 -- Commit work so our lovely new process can start grinding away,
110 -- and so the monitor widget can see it.
111 commit;
112 --
113 p_itemkey := itemkey;
114 --
115 --
116 hr_utility.set_location ('Leaving '||l_package,05);
117 --
118 --
119 exception
120 --
121 when others then
122 -- The line below records this function call in the error system
123 -- in the case of an exception.
124 wf_core.context('ben_on_line_lf_evt', 'Start_on_line_lf_evt_proc',
125 itemtype, itemkey); -- ???? add any parameters here.
126 raise;
127 --
128 end Start_on_line_lf_evt_proc;
129 --
130 --
131 --
132 PROCEDURE End_on_line_lf_evt_proc(itemtype in varchar2,
133 itemkey in varchar2,
134 actid in number,
135 funcmode in varchar2,
136 result in out nocopy varchar2) is
137 begin
138 --
139 if funcmode = 'RUN' then
140 result := 'COMPLETE:COMPLETED';
141 return;
142 end if;
143 --
144 -- Other execution modes may be created in the future.
145 -- Activity indicates that it does not implement a mode
146 -- by returning null
147 --
148 result := '';
149 return;
150 --
151 exception
152 --
153 when others then
154 -- The line below records this function call in the error system
155 -- in the case of an exception.
156 wf_core.context('ben_on_line_lf_evt', 'End_on_line_lf_evt_proc',
157 itemtype, itemkey, to_char(actid), funcmode);
158 raise;
159 --
160 end End_on_line_lf_evt_proc;
161 --
162 --
163 procedure Selector(itemtype in varchar2,
164 itemkey in varchar2,
165 actid in number,
166 funcmode in varchar2,
167 resultout out nocopy varchar2) is
168 --
169 l_package varchar2(80) := g_package||'.Selector';
170 --
171 begin
172 --
173 --
174 hr_utility.set_location ('Entering '||l_package,05);
175 --
176 --
177 if itemtype = 'BEN_OLLE' then
178 resultout := 'ON_LINE_LIFE_EVENT_PROC';
179 elsif itemtype = 'BENCSRDT' then
180 resultout := 'CSR_DESKTOP';
181 end if;
182 --
183 -- RUN mode - normal process execution.
184 --
185 if (funcmode = 'RUN' ) then
186 --
187 -- Return process to run
188 --
189 return;
190 --
191 end if;
192 --
193 if (funcmode = 'CANCEL' ) then
194 --
195 -- Return process to run
196 --
197 return;
198 --
199 end if;
200 --
201 if (funcmode = 'TIMEOUT' ) then
202 --
203 -- Return process to run
204 --
205 return;
206 --
207 end if;
208 --
209 --
210 hr_utility.set_location ('Leaving '||l_package,05);
211 --
212 exception
213 --
214 when others then
215 -- The line below records this function call in the error system
216 -- in the case of an exception.
217 wf_core.context('ben_on_line_lf_evt', 'Selector',
218 itemtype, itemkey, to_char(actid), funcmode);
219 raise;
220 --
221 end Selector;
222 --
223 --
224 procedure p_cnt_ple(itemtype in varchar2,
225 itemkey in varchar2,
226 actid in number,
227 funcmode in varchar2,
228 resultout out nocopy varchar2) is
229 --
230 -- Variables to store the item attributes values.
231 --
232 l_person_id varchar2(50);
233 l_effective_date varchar2(50);
234 l_business_group_id varchar2(50);
235 --
236 l_pil_count number := 0;
237 l_ple_count number := 0;
238 mesg varchar2(500);
239 --
240 l_package varchar2(80) := g_package||'.p_cnt_ple';
241 --
242 cursor c_ptnl_ler is
243 select count(*)
244 from ben_ptnl_ler_for_per ptn
245 where ptn.person_id = to_number(l_person_id)
246 and ptn.business_group_id+0 = to_number(l_business_group_id)
247 /* and ptn.lf_evt_ocrd_dt = p_effective_date */
248 and ptn.ptnl_ler_for_per_stat_cd in ('DTCTD','UNPROCD');
249 /* and to_date(l_effective_date, 'dd/mm/yyyy')
250 between ptn.effective_start_date
251 and ptn.effective_end_date; */
252 --
253 --
254 cursor c_pil is
255 select count(*)
256 from ben_per_in_ler pil
257 where pil.person_id = to_number(l_person_id)
258 and pil.business_group_id = to_number(l_business_group_id)
259 and pil.per_in_ler_stat_cd = 'STRTD';
260 /* and to_date(l_effective_date, 'dd/mm/yyyy')
261 between pil.effective_start_date
262 and pil.effective_end_date; */
263 --
264 begin
265
266 --
267 hr_utility.set_location ('Entering '||l_package,05);
268 --
269 -- RUN mode - normal process execution.
270 --
271 if (funcmode = 'RUN' ) then
272 --
273 -- Extract the person_id, effective_date, business_group_id
274 -- from the item type attributes.
275 --
276 l_person_id := wf_engine.GetItemAttrTEXT(
277 itemtype => itemtype,
278 itemkey => itemkey,
279 aname => 'PERSON_ID');
280 --
281 l_effective_date := wf_engine.GetItemAttrTEXT(
282 itemtype => itemtype,
283 itemkey => itemkey,
284 aname => 'EFFECTIVE_DATE');
285 --
286 l_business_group_id := wf_engine.GetItemAttrTEXT(
287 itemtype => itemtype,
288 itemkey => itemkey,
289 aname => 'BUSINESS_GROUP_ID');
290 --
291 -- Get the number of pil/ptnl ler records for the person.
292 --
293 open c_ptnl_ler;
294 fetch c_ptnl_ler into l_ple_count;
295 close c_ptnl_ler;
296 --
297 --
298 open c_pil;
299 fetch c_pil into l_pil_count;
300 close c_pil;
301 --
302 -- set the item type attributes with the extracted ptnl ler, pil record counts.
303 --
304 wf_engine.SetItemAttrNumber(
305 itemtype => itemtype,
306 itemkey => itemkey,
307 aname => 'BEN_IA_PLE_COUNT',
308 avalue => l_ple_count);
309 --
310 wf_engine.SetItemAttrNumber(
311 itemtype => itemtype,
312 itemkey => itemkey,
313 aname => 'BEN_IA_PIL_COUNT',
314 avalue => l_pil_count);
315 --
316 -- error_simulation;
317 if l_ple_count > 0 or l_pil_count > 0 then
318 --
319 resultout := 'COMPLETE:T';
320 return;
321 --
322 else
323 --
324 resultout := 'COMPLETE:F';
325 return;
326 --
327 end if;
328 end if;
329 --
330 if (funcmode = 'CANCEL' ) then
331 --
332 -- Return process to run
333 --
334 resultout := 'COMPLETE:';
335 return;
336 --
337 end if;
338 --
339 if (funcmode = 'TIMEOUT' ) then
340 --
341 -- Return process to run
342 --
343 resultout := 'COMPLETE:';
344 return;
345 --
346 end if;
347 --
348 --
349 hr_utility.set_location ('Leaving '||l_package,05);
350 --
351 exception
352 --
353 when others then
354 -- The line below records this function call in the error system
355 -- in the case of an exception.
356 mesg := fnd_message.get;
357 wf_core.context('ben_on_line_lf_evt', 'p_cnt_ple' || substr(mesg, 1, 10),
358 itemtype, itemkey, to_char(actid), funcmode);
359 raise;
360 --
361 --
362 end p_cnt_ple;
363 --
364 procedure p_run_form(itemtype in varchar2,
365 itemkey in varchar2,
366 actid in number,
367 funcmode in varchar2,
368 resultout out nocopy varchar2) is
369 begin
370 if (funcmode = 'RUN' ) then
371 --
372 /* 99999 -- Extract the person_id, effective_date, business_group_id
373 -- from the item type attributes.
374 --
375 l_person_id := wf_engine.GetItemAttrNumber(
376 itemtype => itemtype,
377 itemkey => itemkey,
378 aname => 'PERSON_ID');
379 --
380 l_effective_date := wf_engine.GetItemAttrDate(
381 itemtype => itemtype,
382 itemkey => itemkey,
383 aname => 'EFFECTIVE_DATE');
384 --
385 l_business_group_id := wf_engine.GetItemAttrNumber(
386 itemtype => itemtype,
387 itemkey => itemkey,
388 aname => 'BUSINESS_GROUP_ID');
389 --
390 -- Get the number of pil/ptnl ler records for the person.
391 --
392 open c_ptnl_ler;
393 fetch c_ptnl_ler into l_ple_count;
394 close c_ptnl_ler;
395 --
396 --
397 open c_pil;
398 fetch c_pil into l_pil_count;
399 close c_pil;
400 --
401 -- set the item type attributes with the extracted ptnl ler, pil record counts.
402 --
403 wf_engine.SetItemAttrNumber(
404 itemtype => itemtype,
405 itemkey => itemkey,
406 aname => 'BEN_IA_PLE_COUNT',
407 avalue => l_ple_count);
408 --
409 wf_engine.SetItemAttrNumber(
410 itemtype => itemtype,
411 itemkey => itemkey,
412 aname => 'BEN_IA_PIL_COUNT',
413 avalue => l_pil_count);
414 FND_FUNCTION.EXECUTE(FUNCTION_NAME =>'BENPECRT',
415 OPEN_FLAG => 'Y', OTHER_PARAMS=>
416 'G_PERSON_ID="'||TO_CHAR(:PGM_V.PERSON_ID)||'"');
417 --
418 FND_FUNCTION.EXECUTE(FUNCTION_NAME =>'BENAUTHE',
419 OPEN_FLAG => 'Y'); */
420 --
421 resultout := 'COMPLETE:T';
422 return;
423 --
424 end if;
425 --
426 if (funcmode = 'CANCEL' ) then
427 --
428 -- Return process to run
429 --
430 resultout := 'COMPLETE:';
431 return;
432 --
433 end if;
434 --
435 if (funcmode = 'TIMEOUT' ) then
436 --
437 -- Return process to run
438 --
439 resultout := 'COMPLETE:';
440 return;
441 --
442 end if;
443 --
444 exception
445 --
446 when others then
447 -- The line below records this function call in the error system
448 -- in the case of an exception.
449 wf_core.context('ben_on_line_lf_evt', 'p_run_form',
450 itemtype, itemkey, to_char(actid), funcmode);
451 raise;
452 --
453 end p_run_form;
454 --
455
456 --
457 -- procedure to evaluate the potential life events,
458 -- and life events
459 --
460 procedure p_evt_lf_events(itemtype in varchar2,
461 itemkey in varchar2,
462 actid in number,
463 funcmode in varchar2,
464 resultout out nocopy varchar2) is
465 --
466 -- Variables to store the item attributes values.
467 --
468 l_person_id number;
469 l_effective_date date;
470 l_business_group_id number;
471 l_mesg varchar2(1000);
472 l_benefit_action_id varchar2(80);
473 l_person_count number;
474 l_prog_count number;
475 l_plan_count number;
476 l_oipl_count number;
477 l_plan_nip_count number;
478 l_oipl_nip_count number;
479 l_ler_id number;
480 l_object_version_number number;
481 l_ler_count number := 0;
482 l_errbuf varchar2(1000);
483 L_RETCODE number;
484 l_package varchar2(80) := g_package||'.p_evt_lf_events';
485 --
486 type l_le_rec is record
487 (name ben_ler_f.name%type,
488 lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type);
489 --
490 type l_le_table is table of l_le_rec
491 index by binary_integer;
492 --
493 l_le_object l_le_table;
494 --
495 --
496 type l_cont_rel_table is table of varchar2(1000)
497 index by binary_integer;
498 --
499 l_cont_rel_object l_cont_rel_table;
500 --
501 l_pil_count number := 0;
502 l_ple_count number := 0;
503 l_rel_per_count number := 0;
504 l_bft_id number;
505 --
506 cursor c_ler is
507 select ler_id
508 from ben_person_actions
509 where BENEFIT_ACTION_ID = benutils.g_benefit_action_id;
510 --
511 cursor c_ptnl_ler is
512 select count(*)
513 from ben_ptnl_ler_for_per ptn
514 where ptn.person_id = l_person_id
515 and ptn.business_group_id+0 = l_business_group_id
516 /* and ptn.lf_evt_ocrd_dt = p_effective_date */
517 and ptn.ptnl_ler_for_per_stat_cd in ('DTCTD','UNPROCD');
518 /* and l_effective_date
519 between ptn.effective_start_date
520 and ptn.effective_end_date; */
521 --
522 cursor c_pil is
523 select ler.name,
524 ler.ler_id,
525 pil.lf_evt_ocrd_dt,
526 pil.per_in_ler_stat_cd,
527 pel.dflt_enrt_dt
528 from ben_per_in_ler pil,
529 ben_ler_f ler,
530 ben_pil_elctbl_chc_popl pel
531 where pil.ler_id = ler.ler_id
532 and pil.per_in_ler_id = pel.per_in_ler_id
533 and pil.person_id = l_person_id
534 and pil.business_group_id = l_business_group_id
535 and ler.business_group_id = l_business_group_id
536 and pel.business_group_id = l_business_group_id
537 and pil.per_in_ler_stat_cd = 'STRTD'
538 /*and l_effective_date
539 between pil.effective_start_date
540 and pil.effective_end_date */
541 and l_effective_date
542 between ler.effective_start_date
543 and ler.effective_end_date;
544
545 --
546 cursor c_cont_rel is
547 select csr.contact_person_id,
548 ppf.full_name,
549 ler.name,
550 ler.ler_id,
551 pil.lf_evt_ocrd_dt,
552 pil.per_in_ler_stat_cd,
553 pel.dflt_enrt_dt
554 from per_contact_relationships csr, --?? will be _f
555 per_people_f ppf,
556 ben_per_in_ler pil,
557 ben_ler_f ler,
558 ben_pil_elctbl_chc_popl pel
559 where csr.person_id = l_person_id
560 and ppf.person_id = csr.contact_person_id
561 and pil.per_in_ler_id = pel.per_in_ler_id
562 /* and l_effective_date
563 between ppf.effective_start_date
564 and ppf.effective_end_date */
565 and csr.personal_flag = 'Y'
566 and l_effective_date between nvl(csr.date_start, l_effective_date)
567 and nvl(csr.date_end, l_effective_date)
568 and pil.ler_id = ler.ler_id
569 and pil.person_id = csr.contact_person_id
570 and pil.business_group_id = l_business_group_id
571 and ler.business_group_id = l_business_group_id
572 and pel.business_group_id = l_business_group_id
573 and pil.per_in_ler_stat_cd = 'STRTD'
574 /* and l_effective_date
575 between pil.effective_start_date
576 and pil.effective_end_date */
577 and l_effective_date
578 between ler.effective_start_date
579 and ler.effective_end_date
580 order by 1;
581
582 --
583 begin
584 --
585 hr_utility.set_location ('Entering '||l_package,05);
586 --
587 --
588 -- RUN mode - normal process execution.
589 --
590 if (funcmode = 'RUN' ) then
591 --
592 -- Extract the person_id, effective_date, business_group_id
593 -- from the item type attributes.
594 --
595 l_person_id := to_number(wf_engine.GetItemAttrTEXT(
596 itemtype => itemtype,
597 itemkey => itemkey,
598 aname => 'PERSON_ID'));
599 --
600 l_effective_date := to_date(wf_engine.GetItemAttrTEXT(
601 itemtype => itemtype,
602 itemkey => itemkey,
603 aname => 'EFFECTIVE_DATE'), 'YYYY/MM/DD HH24:MI:SS');
604 --
605 l_business_group_id := to_number(wf_engine.GetItemAttrTEXT(
606 itemtype => itemtype,
607 itemkey => itemkey,
608 aname => 'BUSINESS_GROUP_ID'));
609 --
610 -- Get the number of ptnl ler records for the person.
611 -- before evaluating the life events.
612 --
613 open c_ptnl_ler;
614 fetch c_ptnl_ler into l_ple_count;
615 close c_ptnl_ler;
616 --
617 -- In case of error rollback upto this point.
618 --
619 savepoint process_lf_evts;
620 --
621 --
622 l_bft_id := null;
623 --
624 ben_manage_life_events.process
625 (
626 errbuf => l_errbuf,
627 retcode => l_retcode,
628 p_benefit_action_id => l_bft_id,
629 p_effective_date => l_effective_date,
630 p_mode => 'L',
631 p_derivable_factors => 'ASC' ,
632 p_validate => 'N',
633 p_person_id => l_person_id,
634 p_person_type_id => null,
635 p_pgm_id => null,
636 p_business_group_id => l_business_group_id,
637 p_pl_id => null,
638 p_popl_enrt_typ_cycl_id => null,
639 p_no_programs => 'N' ,
640 p_no_plans => 'N' ,
641 p_comp_selection_rule_id => null,
642 p_person_selection_rule_id => null,
643 p_ler_id => null,
644 p_organization_id => null,
645 p_benfts_grp_id => null,
646 p_location_id => null,
647 p_pstl_zip_rng_id => null,
648 p_rptg_grp_id => null,
649 p_pl_typ_id => null,
650 p_opt_id => null,
651 p_eligy_prfl_id => null,
652 p_vrbl_rt_prfl_id => null,
653 p_legal_entity_id => null,
654 p_payroll_id => null,
655 p_commit_data => 'N',
656 p_lmt_prpnip_by_org_flag => nvl(fnd_profile.value('BEN_LMT_PRPNIP_BY_ORG_FLAG'), 'N'),
657 p_lf_evt_ocrd_dt => l_effective_date );
658 --
659 ben_comp_object_list.build_comp_object_list
660 (p_benefit_action_id => benutils.g_benefit_action_id,
661 p_comp_selection_rule_id => null,
662 p_effective_date => l_effective_date,
663 p_pgm_id => null,
664 p_business_group_id => l_business_group_id,
665 p_pl_id => null,
666 p_oipl_id => null,
667 p_asnd_lf_evt_dt => null,
668 -- p_popl_enrt_typ_cycl_id => null,
669 p_no_programs => 'N',
670 p_no_plans => 'N',
671 p_rptg_grp_id => null,
672 p_pl_typ_id => null,
673 p_opt_id => null,
674 p_eligy_prfl_id => null,
675 p_vrbl_rt_prfl_id => null,
676 p_thread_id => 1,
677 p_mode => 'L');
678 --
679 ben_manage_life_events.person_header
680 (p_person_id => l_person_id,
681 p_business_group_id => l_business_group_id,
682 p_effective_date => l_effective_date);
683 --
684 open c_ler;
685 fetch c_ler into l_ler_id;
686 --
687 if c_ler%notfound then
688 --
689 close c_ler;
690 fnd_message.set_name('BEN','BEN_91791_LER_NOT_IN_PER_ACTN');
691 fnd_message.raise_error;
692 --
693 end if;
694 --
695 close c_ler;
696 ben_manage_life_events.evaluate_life_events
697 (p_person_id => l_person_id,
698 p_business_group_id => l_business_group_id,
699 p_mode => 'L',
700 -- p_popl_enrt_typ_cycl_id => null,
701 p_ler_id => l_ler_id,
702 p_lf_evt_ocrd_dt => l_effective_date,
703 p_effective_date => l_effective_date);
704 --
705 -- Populate the Item attributes with life event data to be displayed along with
706 -- message.
707 --
708 for l_pil_rec in c_pil loop
709 --
710 l_pil_count := l_pil_count + 1;
711 --
712 l_le_object(l_pil_count).name := l_pil_rec.name;
713 l_le_object(l_pil_count).lf_evt_ocrd_dt := l_pil_rec.lf_evt_ocrd_dt;
714 --
715 end loop;
716 --
717 -- Populate the Item attributes with related person life event data
718 -- to be displayed along with message.
719 --
720 for l_cont_rel_rec in c_cont_rel loop
721 --
722 l_rel_per_count := l_rel_per_count + 1;
723 l_cont_rel_object(l_rel_per_count) := 'Related Person : '|| l_cont_rel_rec.full_name
724 || ', Event : ' || l_cont_rel_rec.name
725 || ' , Date : ' ||
726 to_char(l_cont_rel_rec.lf_evt_ocrd_dt, 'DD/MM/YYY');
727 --
728 if l_rel_per_count = 10 then
729 exit;
730 end if;
731 --
732 end loop;
733 --
734 -- Now is the time to rollback;
735 l_benefit_action_id := to_char(benutils.g_benefit_action_id);
736 rollback to process_lf_evts;
737 --
738 /* ben_manage_life_events.process_comp_objects
739 (p_person_id => l_person_id,
740 p_person_action_id => null,
741 p_object_version_number => l_object_version_number,
742 p_business_group_id => l_business_group_id,
743 p_mode => 'L',
744 p_ler_id => l_ler_id,
745 p_derivable_factors => 'ASC',
746 p_person_count => l_person_count,
747 p_popl_enrt_typ_cycl_id => null,
748 p_effective_date => l_effective_date
749 );*/
750 --
751 -- Now set all the item attributes for the messaging purpose.
752 --
753 -- Save the ler id for future use.
754 --
755 wf_engine.SetItemAttrNumber(
756 itemtype => itemtype,
757 itemkey => itemkey,
758 aname => 'BEN_IA_LER_ID',
759 avalue => l_ler_id);
760 --
761 for i in 1..l_pil_count loop
762 --
763 -- Set item attribute (Life event name, occured date )values.
764 --
765 wf_engine.SetItemAttrTEXT(
766 itemtype => itemtype,
767 itemkey => itemkey,
768 aname => 'BEN_IA_LE_NAME',
769 avalue => l_le_object(l_pil_count).name);
770 --
771 wf_engine.SetItemAttrTEXT(
772 itemtype => itemtype,
773 itemkey => itemkey,
774 aname => 'BEN_IA_LF_EVT_OCRD_DT',
775 avalue => l_le_object(l_pil_count).lf_evt_ocrd_dt);
776 --
777 -- Right now assumption is only one LE exists for the person.
778 --
779 if i = 1 then
780 exit;
781 end if;
782 --
783 end loop;
784 --
785 -- set the item type attributes with the extracted ptnl ler, pil record counts.
786 --
787 wf_engine.SetItemAttrNumber(
788 itemtype => itemtype,
789 itemkey => itemkey,
790 aname => 'BEN_IA_PLE_COUNT',
791 avalue => l_ple_count);
792 --
793 wf_engine.SetItemAttrNumber(
794 itemtype => itemtype,
795 itemkey => itemkey,
796 aname => 'BEN_IA_PIL_COUNT',
797 avalue => l_pil_count);
798 --
799 -- If no life events are detected then set message to No life Events Detected.
800 --
801 if l_pil_count = 0 then
802 --
803 wf_engine.SetItemAttrTEXT(
804 itemtype => itemtype,
805 itemkey => itemkey,
806 aname => 'BEN_IA_LE_NAME',
807 avalue => ' No life Events Detected based on data changes');
808 --
809 end if;
810 --
811 --
812 -- Populate the Item attributes with related person life event data
813 -- to be displayed along with message.
814 --
815 for i in 1..l_rel_per_count loop
816 --
817 -- Set item attribute with(Related Person name, Life event name, occured date ).
818 --
819 wf_engine.SetItemAttrTEXT(
820 itemtype => itemtype,
821 itemkey => itemkey,
822 aname => 'BEN_IA_REL_PER_LF' || to_char(i),
823 avalue => l_cont_rel_object(i));
824 --
825 -- Currently only 10 related persons are handled so exit after 10 records fetch.
826 --
827 if i = 10 then
828 exit;
829 end if;
830 end loop;
831 --
832 -- Store the thread id and benefit action id's into item attributes.
833 --
834 wf_engine.SetItemAttrTEXT(
835 itemtype => itemtype,
836 itemkey => itemkey,
837 aname => 'BEN_IA_THREAD_ID',
838 avalue => '1');
839 --
840 wf_engine.SetItemAttrTEXT(
841 itemtype => itemtype,
842 itemkey => itemkey,
843 aname => 'BEN_IA_BENEFIT_ACTION_ID',
844 avalue => l_benefit_action_id);
845 -- avalue => to_char(benutils.g_benefit_action_id));
846 --
847 -- Successfully completed.
848 --
849 resultout := 'COMPLETE:COMPLETE';
850 return;
851 --
852 end if;
853 --
854 if (funcmode = 'CANCEL' ) then
855 --
856 -- Return process to run
857 --
858 resultout := 'COMPLETE:COMPLETE';
859 return;
860 --
861 end if;
862 --
863 if (funcmode = 'TIMEOUT' ) then
864 --
865 -- Return process to run
866 --
867 resultout := 'COMPLETE:COMPLETE';
868 return;
869 --
870 end if;
871 --
872 --
873 hr_utility.set_location ('Leaving '||l_package,05);
874 --
875 exception
876 --
877 when others then
878 --
879 -- The line below records this function call in the error system
880 -- in the case of an exception.
881 -- The error message is written to item attribute to be notified to the
882 -- user. ?????
883 l_mesg := fnd_message.get;
884 --
885 rollback to process_lf_evts;
886 --
887 wf_engine.SetItemAttrText(
888 itemtype => itemtype,
889 itemkey => itemkey,
890 aname => 'BEN_IA_ERROR_TEXT',
891 avalue => l_mesg);
892 --
893 --
894 -- Completed with Errors.
895 --
896 resultout := 'COMPLETE:ERROR';
897 wf_core.context('ben_on_line_lf_evt', 'p_evt_lf_events' ,
898 itemtype, itemkey, to_char(actid), funcmode);
899 return;
900 --
901 end p_evt_lf_events;
902 --
903 -- procedure to process the life events,
904 --
905 procedure p_mng_lf_events(itemtype in varchar2,
906 itemkey in varchar2,
907 actid in number,
908 funcmode in varchar2,
909 resultout out nocopy varchar2) is
910 --
911 -- Variables to store the item attributes values.
912 --
913 l_person_id varchar2(50);
914 l_effective_date varchar2(50);
915 l_business_group_id varchar2(50);
916 l_mesg varchar2(1000);
917 l_person_count number;
918 l_error_person_count number;
919 l_prog_count number;
920 l_plan_count number;
921 l_oipl_count number;
922 l_plan_nip_count number;
923 l_oipl_nip_count number;
924 l_ler_id number;
925 l_errbuf varchar2(1000);
926 l_retcode number;
927 l_object_version_number number;
928 --
929 begin
930 --
931 -- RUN mode - normal process execution.
932 --
933 if (funcmode = 'RUN' ) then
934 --
935 -- Extract the person_id, effective_date, business_group_id
936 -- from the item type attributes.
937 --
938 l_person_id := wf_engine.GetItemAttrTEXT(
939 itemtype => itemtype,
940 itemkey => itemkey,
941 aname => 'PERSON_ID');
942 --
943 l_effective_date := wf_engine.GetItemAttrTEXT(
944 itemtype => itemtype,
945 itemkey => itemkey,
946 aname => 'EFFECTIVE_DATE');
947 --
948 l_business_group_id := wf_engine.GetItemAttrTEXT(
949 itemtype => itemtype,
950 itemkey => itemkey,
951 aname => 'BUSINESS_GROUP_ID');
952 --
953 l_ler_id := wf_engine.GetItemAttrNumber(
954 itemtype => itemtype,
955 itemkey => itemkey,
956 aname => 'BEN_IA_LER_ID');
957 --
958 -- In case of error rollback upto this point.
959 --
960 savepoint process_lf_evts;
961 --
962 ben_on_line_lf_evt.p_manage_life_events(
963 p_person_id => to_number(l_person_id)
964 ,p_effective_date => to_date(l_effective_date, 'DD/MM/YYYY')
965 ,p_business_group_id => to_number(l_business_group_id)
966 ,p_pgm_id => null
967 ,p_pl_id => null
968 ,p_mode => 'L'
969 ,p_prog_count => l_prog_count
970 ,p_plan_count => l_plan_count
971 ,p_oipl_count => l_oipl_count
972 ,p_person_count => l_person_count
973 ,p_plan_nip_count => l_plan_nip_count
974 ,p_oipl_nip_count => l_oipl_nip_count
975 ,p_ler_id => l_ler_id
976 ,p_errbuf => l_errbuf
977 ,p_retcode => l_retcode);
978 --
979 --
980 -- Store the thread id and benefit action id's into item attributes.
981 --
982 wf_engine.SetItemAttrTEXT(
983 itemtype => itemtype,
984 itemkey => itemkey,
985 aname => 'BEN_IA_THREAD_ID',
986 avalue => '1');
987 --
988 wf_engine.SetItemAttrTEXT(
989 itemtype => itemtype,
990 itemkey => itemkey,
991 aname => 'BEN_IA_BENEFIT_ACTION_ID',
992 avalue => to_char(benutils.g_benefit_action_id));
993 --
994 -- Successfully completed.
995 resultout := 'COMPLETE:COMPLETE';
996 return;
997 --
998 end if;
999 --
1000 if (funcmode = 'CANCEL' ) then
1001 --
1002 -- Return process to run
1003 --
1004 resultout := 'COMPLETE:COMPLETE';
1005 return;
1006 --
1007 end if;
1008 --
1009 if (funcmode = 'TIMEOUT' ) then
1010 --
1011 -- Return process to run
1012 --
1013 resultout := 'COMPLETE:COMPLETE';
1014 return;
1015 --
1016 end if;
1017 --
1018 exception
1019 --
1020 when others then
1021 --
1022 -- The line below records this function call in the error system
1023 -- in the case of an exception.
1024 -- The error message is written to item attribute to be notified to the
1025 -- user. ?????
1026 l_mesg := fnd_message.get;
1027 --
1028 rollback to process_lf_evts;
1029 --
1030 wf_engine.SetItemAttrText(
1031 itemtype => itemtype,
1032 itemkey => itemkey,
1033 aname => 'BEN_IA_ERROR_TEXT',
1034 avalue => l_mesg);
1035 --
1036 --
1037 -- Completed with Errors.
1038 --
1039 resultout := 'COMPLETE:ERROR';
1040 wf_core.context('ben_on_line_lf_evt', 'p_mng_lf_events' ,
1041 itemtype, itemkey, to_char(actid), funcmode);
1042 return;
1043 --
1044 end p_mng_lf_events;
1045 --
1046 --
1047 -- procedure to count electable choices.
1048 --
1049 procedure p_have_elctbl_chcs(itemtype in varchar2,
1050 itemkey in varchar2,
1051 actid in number,
1052 funcmode in varchar2,
1053 resultout out nocopy varchar2) is
1054 --
1055 -- Variables to store the item attributes values.
1056 --
1057 l_person_id varchar2(50);
1058 l_effective_date varchar2(50);
1059 l_business_group_id varchar2(50);
1060 l_mesg varchar2(1000);
1061 l_elctbl_chc_count number := 0;
1062 l_can_prtcpnt_enroll char(1);
1063 --
1064 cursor c_elctbl_chc is
1065 select count(*)
1066 from ben_elig_per_elctbl_chc epe,
1067 ben_pil_elctbl_chc_popl pcp,
1068 ben_per_in_ler pil,
1069 ben_ler_f ler
1070 where pil.person_id = to_number(l_person_id)
1071 and pil.per_in_ler_id = pcp.per_in_ler_id
1072 and pcp.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
1073 and ler.ler_id = pil.ler_id
1074 and ler.business_group_id+0 = to_number(l_business_group_id)
1075 and to_date(l_effective_date, 'dd/MM/yyyy')
1076 between ler.effective_start_date
1077 and ler.effective_end_date
1078 and epe.business_group_id+0 = to_number(l_business_group_id)
1079 and pil.business_group_id+0 = to_number(l_business_group_id)
1080 and pcp.business_group_id+0 = to_number(l_business_group_id)
1081 and pil.per_in_ler_stat_cd = 'STRTD'
1082 /* and to_date(l_effective_date, 'dd/MM/yyyy')
1083 between pil.effective_start_date
1084 and pil.effective_end_date */
1085 and ( to_date(l_effective_date, 'dd/MM/yyyy')
1086 between pcp.enrt_perd_strt_dt
1087 and pcp.enrt_perd_end_dt or ler.TYP_CD = 'SCHDDU');
1088 --
1089 l_package varchar2(80) := g_package||'.p_have_elctbl_chcs';
1090 --
1091 begin
1092 --
1093 --
1094 hr_utility.set_location ('Entering '||l_package,05);
1095 --
1096 -- RUN mode - normal process execution.
1097 --
1098 if (funcmode = 'RUN' ) then
1099 --
1100 -- Extract the person_id, effective_date, business_group_id
1101 -- from the item type attributes.
1102 --
1103 l_person_id := wf_engine.GetItemAttrTEXT(
1104 itemtype => itemtype,
1105 itemkey => itemkey,
1106 aname => 'PERSON_ID');
1107 --
1108 l_effective_date := wf_engine.GetItemAttrTEXT(
1109 itemtype => itemtype,
1110 itemkey => itemkey,
1111 aname => 'EFFECTIVE_DATE');
1112 --
1113 l_business_group_id := wf_engine.GetItemAttrTEXT(
1114 itemtype => itemtype,
1115 itemkey => itemkey,
1116 aname => 'BUSINESS_GROUP_ID');
1117 --
1118 open c_elctbl_chc;
1119 fetch c_elctbl_chc into l_elctbl_chc_count;
1120 close c_elctbl_chc;
1121 --
1122 if l_elctbl_chc_count > 0 then
1123 resultout := 'COMPLETE:Y';
1124 else
1125 resultout := 'COMPLETE:N';
1126 end if;
1127 return;
1128 --
1129 end if;
1130 --
1131 if (funcmode = 'CANCEL' ) then
1132 --
1133 -- Return process to run
1134 --
1135 resultout := 'COMPLETE:Y';
1136 return;
1137 --
1138 end if;
1139 --
1140 if (funcmode = 'TIMEOUT' ) then
1141 --
1142 -- Return process to run
1143 --
1144 resultout := 'COMPLETE:Y';
1145 return;
1146 --
1147 end if;
1148 --
1149 --
1150 hr_utility.set_location ('Leaving '||l_package,05);
1151 --
1152 exception
1153 --
1154 when others then
1155 --
1156 -- The line below records this function call in the error system
1157 -- in the case of an exception.
1158 -- The error message is written to item attribute to be notified to the
1159 -- user. ?????
1160 l_mesg := fnd_message.get;
1161 --
1162 -- Completed with Errors.
1163 --
1164 resultout := 'COMPLETE:E';
1165 wf_core.context('ben_on_line_lf_evt', 'p_have_elctbl_chcs' ,
1166 itemtype, itemkey, to_char(actid), funcmode);
1167 return;
1168 --
1169 end p_have_elctbl_chcs;
1170 --
1171 -- procedure to detect a person can enroll now.
1172 --
1173 procedure p_can_prtcpnt_enrl(itemtype in varchar2,
1174 itemkey in varchar2,
1175 actid in number,
1176 funcmode in varchar2,
1177 resultout out nocopy varchar2) is
1178 --
1179 -- Variables to store the item attributes values.
1180 --
1181 l_person_id varchar2(50);
1182 l_effective_date varchar2(50);
1183 l_business_group_id varchar2(50);
1184 l_mesg varchar2(1000);
1185 l_elctbl_chc_count number := 0;
1186 l_can_prtcpnt_enroll char(1);
1187 --
1188 cursor c_elctbl_chc is
1189 select count(*)
1190 from ben_elig_per_elctbl_chc epe,
1191 ben_per_in_ler pil
1192 where pil.person_id = to_number(l_person_id)
1193 and epe.business_group_id+0 = to_number(l_business_group_id)
1194 and epe.PER_IN_LER_ID = pil.PER_IN_LER_ID
1195 and pil.business_group_id+0 = to_number(l_business_group_id)
1196 and pil.per_in_ler_stat_cd = 'PROCD';
1197 /* and to_date(l_effective_date, 'dd/MM/yyyy')
1198 between pil.effective_start_date
1199 and pil.effective_end_date; */
1200 --
1201 l_package varchar2(80) := g_package||'.p_can_prtcpnt_enrl';
1202 --
1203 begin
1204 --
1205 --
1206 hr_utility.set_location ('Entering '||l_package,05);
1207 --
1208 -- RUN mode - normal process execution.
1209 --
1210 if (funcmode = 'RUN' ) then
1211 --
1212 -- Extract the person_id, effective_date, business_group_id
1213 -- from the item type attributes.
1214 --
1215 l_person_id := wf_engine.GetItemAttrTEXT(
1216 itemtype => itemtype,
1217 itemkey => itemkey,
1218 aname => 'PERSON_ID');
1219 --
1220 l_effective_date := wf_engine.GetItemAttrTEXT(
1221 itemtype => itemtype,
1222 itemkey => itemkey,
1223 aname => 'EFFECTIVE_DATE');
1224 --
1225 l_business_group_id := wf_engine.GetItemAttrTEXT(
1226 itemtype => itemtype,
1227 itemkey => itemkey,
1228 aname => 'BUSINESS_GROUP_ID');
1229 --
1230 /*
1231 open c_elctbl_chc;
1232 fetch c_elctbl_chc into l_elctbl_chc_count;
1233 close c_elctbl_chc;
1234 */
1235 --
1236 if l_elctbl_chc_count > 0 then
1237 resultout := 'COMPLETE:Y';
1238 else
1239 resultout := 'COMPLETE:N';
1240 end if;
1241 /* 9999 delete below line */
1242 resultout := 'COMPLETE:Y';
1243 return;
1244 --
1245 end if;
1246 --
1247 if (funcmode = 'CANCEL' ) then
1248 --
1249 -- Return process to run
1250 --
1251 resultout := 'COMPLETE:Y';
1252 return;
1253 --
1254 end if;
1255 --
1256 if (funcmode = 'TIMEOUT' ) then
1257 --
1258 -- Return process to run
1259 --
1260 resultout := 'COMPLETE:Y';
1261 return;
1262 --
1263 end if;
1264 --
1265 --
1266 hr_utility.set_location ('Leaving '||l_package,05);
1267 --
1268 exception
1269 --
1270 when others then
1271 --
1272 -- The line below records this function call in the error system
1273 -- in the case of an exception.
1274 -- The error message is written to item attribute to be notified to the
1275 -- user. ?????
1276 l_mesg := fnd_message.get;
1277 --
1278 -- Completed with Errors.
1279 --
1280 resultout := 'COMPLETE:E';
1281 wf_core.context('ben_on_line_lf_evt', 'p_can_prtcpnt_enrl' ,
1282 itemtype, itemkey, to_char(actid), funcmode);
1283 return;
1284 --
1285 end p_can_prtcpnt_enrl;
1286 --
1287 -- procedure to evaluate the potential life events,
1288 -- and life events
1289 --
1290 procedure p_manage_life_events(
1291 p_person_id in number
1292 ,p_effective_date in date
1293 ,p_business_group_id in number
1294 ,p_prog_count out nocopy number
1295 ,p_plan_count out nocopy number
1296 ,p_oipl_count out nocopy number
1297 ,p_person_count out nocopy number
1298 ,p_plan_nip_count out nocopy number
1299 ,p_oipl_nip_count out nocopy number
1300 ,p_ler_id out nocopy number
1301 ,p_errbuf out nocopy varchar2
1302 ,p_retcode out nocopy number) is
1303 --
1304 -- local variables.
1305 l_object_version_number number;
1306 --
1307 cursor c_ler is
1308 select ler_id
1309 from ben_person_actions
1310 where BENEFIT_ACTION_ID = benutils.g_benefit_action_id;
1311 --
1312 begin
1313 --
1314 /* ben_manage_life_events.process
1315 (
1316 errbuf => p_errbuf,
1317 retcode => p_retcode,
1318 p_benefit_action_id => null,
1319 p_effective_date => p_effective_date,
1320 p_mode => 'L',
1321 p_derivable_factors => 'ASC' ,
1322 p_validate => 'N',
1323 p_person_id => p_person_id,
1324 p_person_type_id => null,
1325 p_pgm_id => null,
1326 p_business_group_id => p_business_group_id,
1327 p_pl_id => null,
1328 p_popl_enrt_typ_cycl_id => null,
1329 p_no_programs => 'N' ,
1330 p_no_plans => 'N' ,
1331 p_comp_selection_rule_id => null,
1332 p_person_selection_rule_id => null,
1333 p_ler_id => null,
1334 p_organization_id => null,
1335 p_benfts_grp_id => null,
1336 p_location_id => null,
1337 p_pstl_zip_rng_id => null,
1338 p_rptg_grp_id => null,
1339 p_pl_typ_id => null,
1340 p_opt_id => null,
1341 p_eligy_prfl_id => null,
1342 p_vrbl_rt_prfl_id => null,
1343 p_legal_entity_id => null,
1344 p_payroll_id => null,
1345 p_commit_data => 'N' ); */
1346 --
1347 /* ben_comp_object_list.build_comp_object_list
1348 (p_benefit_action_id => benutils.g_benefit_action_id,
1349 p_comp_selection_rule_id => null,
1350 p_effective_date => p_effective_date,
1351 p_pgm_id => null,
1352 p_business_group_id => p_business_group_id,
1353 p_pl_id => null,
1354 p_oipl_id => null,
1355 p_popl_enrt_typ_cycl_id => null,
1356 p_no_programs => 'N',
1357 p_no_plans => 'N',
1358 p_rptg_grp_id => null,
1359 p_pl_typ_id => null,
1360 p_opt_id => null,
1361 p_eligy_prfl_id => null,
1362 p_vrbl_rt_prfl_id => null,
1363 p_thread_id => 1,
1364 p_mode => 'L', -- is it a parameter????
1365 -- p_cache_single_object => 'N', -- 99999 Graham why removed
1366 p_prog_count => p_prog_count,
1367 p_plan_count => p_plan_count,
1368 p_oipl_count => p_oipl_count,
1369 p_plan_nip_count => p_plan_nip_count,
1370 p_oipl_nip_count => p_oipl_nip_count); */
1371 --
1372 ben_manage_life_events.person_header
1373 (p_person_id => p_person_id,
1374 p_business_group_id => p_business_group_id,
1375 p_effective_date => p_effective_date );
1376 --
1377 open c_ler;
1378 fetch c_ler into p_ler_id;
1379 --
1380 if c_ler%notfound then
1381 --
1382 fnd_message.set_name('BEN','BEN_91791_LER_NOT_IN_PER_ACTN');
1383 fnd_message.raise_error;
1384 --
1385 end if;
1386 --
1387 close c_ler;
1388 ben_manage_life_events.evaluate_life_events
1389 (p_person_id => p_person_id,
1390 p_business_group_id => p_business_group_id,
1391 p_mode => 'L',
1392 p_ler_id => p_ler_id,
1393 -- p_popl_enrt_typ_cycl_id => null,
1394 p_lf_evt_ocrd_dt => p_effective_date,
1395 p_effective_date => p_effective_date);
1396 --
1397 ben_manage_life_events.process_comp_objects
1398 (p_person_id => p_person_id,
1399 p_person_action_id => null,
1400 p_object_version_number => l_object_version_number,
1401 p_business_group_id => p_business_group_id,
1402 p_mode => 'L',
1403 p_ler_id => p_ler_id,
1404 p_derivable_factors => 'ASC',
1405 p_person_count => p_person_count,
1406 -- p_popl_enrt_typ_cycl_id => null,
1407 p_effective_date => p_effective_date
1408 );
1409 --
1410 end p_manage_life_events;
1411 --
1412 -- procedure to evaluate the potential life events,
1413 -- and life events
1414 --
1415 procedure p_manage_life_events(
1416 p_person_id in number
1417 ,p_effective_date in date
1418 ,p_business_group_id in number
1419 ,p_pgm_id in number default null
1420 ,p_pl_id in number default null
1421 ,p_mode in varchar2
1422 ,p_lf_evt_ocrd_dt in date default null --GLOBAL CWB
1423 ,p_prog_count out nocopy number
1424 ,p_plan_count out nocopy number
1425 ,p_oipl_count out nocopy number
1426 ,p_person_count out nocopy number
1427 ,p_plan_nip_count out nocopy number
1428 ,p_oipl_nip_count out nocopy number
1429 ,p_ler_id out nocopy number
1430 ,p_errbuf out nocopy varchar2
1431 ,p_retcode out nocopy number) is
1432 --
1433 -- local variables.
1434 l_object_version_number number;
1435 l_effective_date varchar2(30);
1436 l_lf_evt_ocrd_dt varchar2(30);
1437 l_chunk_size number := 0;
1438 l_threads number := 0;
1439 l_max_errors_allowed number ;
1440 l_rec benutils.g_active_life_event;
1441 --
1442 cursor c_ler is
1443 select ler_id
1444 from ben_person_actions
1445 where BENEFIT_ACTION_ID = benutils.g_benefit_action_id;
1446 --
1447 l_bft_id number;
1448 l_assignment_id number;
1449 l_encoded_message varchar2(2000);
1450 l_app_short_name varchar2(2000);
1451 l_message_name varchar2(2000);
1452
1453 -- start bug 4430107
1454 cursor c_pgm is
1455 select pgm_id
1456 from BEN_PIL_ELCTBL_CHC_POPL
1457 where business_group_id = p_business_group_id
1458 and PER_IN_LER_ID = l_rec.per_in_ler_id
1459 and pgm_id is not null;
1460 --
1461 cursor c_contact is
1462 select pcr.contact_person_id ,
1463 pcr.contact_relationship_id
1464 from per_contact_relationships pcr,
1465 per_all_people_f per
1466 where pcr.person_id = p_person_id
1467 and pcr.contact_person_id = per.person_id
1468 and pcr.personal_flag = 'Y'
1469 and p_effective_date between per.effective_start_date and per.effective_end_date
1470 and p_effective_date between
1471 nvl(pcr.date_start,p_effective_date) and
1472 nvl(pcr.date_end,p_effective_date)
1473 and p_effective_date > l_rec.lf_evt_ocrd_dt
1474 and nvl(pcr.date_start,p_effective_date) > l_rec.lf_evt_ocrd_dt ;
1475
1476 --
1477 --l_contact c_contact%rowtype;
1478 --end bug 4430107
1479 begin
1480 --
1481 hr_utility.set_location('Before Process',5);
1482 /*
1483 l_effective_date := to_char(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
1484 l_lf_evt_ocrd_dt := to_char(nvl(p_lf_evt_ocrd_dt,p_effective_date),'YYYY/MM/DD HH24:MI:SS');
1485 */
1486 --
1487 l_effective_date := fnd_date.date_to_canonical(p_effective_date);
1488 l_lf_evt_ocrd_dt := fnd_date.date_to_canonical(NVL(p_lf_evt_ocrd_dt,p_effective_date));
1489 --
1490 -- Bug 3486966
1491 --
1492 if p_mode in ('L', 'C','U') then
1493 l_assignment_id := null;
1494 l_assignment_id :=
1495 benutils.get_assignment_id(p_person_id=> p_person_id
1496 ,p_business_group_id => p_business_group_id
1497 ,p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date));
1498 --
1499 if l_assignment_id is null
1500 then
1501 fnd_message.set_name('BEN','BEN_93906_NO_EMP_BEN_ASG');
1502 fnd_message.raise_error;
1503 end if ;
1504 end if;
1505 l_bft_id := null;
1506 ben_manage_life_events.g_modified_mode := null;
1507 --
1508 ben_comp_object_list1.refresh_eff_date_caches;
1509 --
1510
1511 ben_manage_life_events.process
1512 (
1513 errbuf => p_errbuf,
1514 retcode => p_retcode,
1515 p_benefit_action_id => l_bft_id,
1516 p_effective_date => l_effective_date,
1517 p_mode => p_mode,
1518 p_derivable_factors => 'ASC' ,
1519 p_validate => 'N',
1520 p_person_id => p_person_id,
1521 p_person_type_id => null,
1522 p_pgm_id => p_pgm_id,
1523 p_business_group_id => p_business_group_id,
1524 p_pl_id => p_pl_id,
1525 p_popl_enrt_typ_cycl_id => null,
1526 p_no_programs => 'N' ,
1527 p_no_plans => 'N' ,
1528 p_comp_selection_rule_id => null,
1529 p_person_selection_rule_id => null,
1530 p_ler_id => null,
1531 p_organization_id => null,
1532 p_benfts_grp_id => null,
1533 p_location_id => null,
1534 p_pstl_zip_rng_id => null,
1535 p_rptg_grp_id => null,
1536 p_pl_typ_id => null,
1537 p_opt_id => null,
1538 p_eligy_prfl_id => null,
1539 p_vrbl_rt_prfl_id => null,
1540 p_legal_entity_id => null,
1541 p_payroll_id => null,
1542 p_commit_data => 'N',
1543 p_lmt_prpnip_by_org_flag => nvl(fnd_profile.value('BEN_LMT_PRPNIP_BY_ORG_FLAG'), 'N'),
1544 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt ); -- GLOBAL CWB l_effective_date );
1545 --
1546 hr_utility.set_location('After process',10);
1547 hr_utility.set_location('Before get_parameter',21);
1548 --
1549 benutils.get_parameter
1550 (p_business_group_id => p_business_group_id,
1551 p_batch_exe_cd => 'BENMNGLE',
1552 p_threads => l_threads,
1553 p_chunk_size => l_chunk_size,
1554 p_max_errors => l_max_errors_allowed);
1555 --
1556 -- Set up benefits environment
1557 --
1558 hr_utility.set_location('After get_parameter',22);
1559 hr_utility.set_location('Before clear_init_benmngle_caches',23);
1560 --
1561 ben_manage_life_events.clear_init_benmngle_caches
1562 (p_business_group_id => p_business_group_id
1563 ,p_effective_date => p_effective_date
1564 ,p_threads => l_threads
1565 ,p_chunk_size => l_chunk_size
1566 ,p_max_errors => l_max_errors_allowed
1567 ,p_benefit_action_id => benutils.g_benefit_action_id
1568 ,p_thread_id => 1
1569 );
1570 hr_utility.set_location('After clear_init_benmngle_caches',24);
1571 hr_utility.set_location('Before Build Comp Object List',30);
1572 --
1573 if (p_mode = 'U')
1574 then
1575 ben_comp_object_list.build_comp_object_list
1576 (p_benefit_action_id => benutils.g_benefit_action_id,
1577 p_comp_selection_rule_id => null,
1578 p_effective_date => p_effective_date,
1579 p_pgm_id => p_pgm_id,
1580 p_business_group_id => p_business_group_id,
1581 p_pl_id => p_pl_id,
1582 p_oipl_id => null,
1583 -- p_popl_enrt_typ_cycl_id => null,
1584 p_asnd_lf_evt_dt => null,
1585 p_no_programs => 'N',
1586 p_no_plans => 'N',
1587 p_rptg_grp_id => null,
1588 p_pl_typ_id => null,
1589 p_opt_id => null,
1590 p_eligy_prfl_id => null,
1591 p_vrbl_rt_prfl_id => null,
1592 p_thread_id => 1,
1593 p_mode => p_mode);
1594 --
1595 ben_manage_life_events.g_modified_mode := 'U';
1596 --
1597 end if;
1598 hr_utility.set_location('After build comp object',32);
1599 hr_utility.set_location('Before Cursor',35);
1600 --
1601 open c_ler;
1602 fetch c_ler into p_ler_id;
1603 --
1604 if c_ler%notfound then
1605 --
1606 fnd_message.set_name('BEN','BEN_91791_LER_NOT_IN_PER_ACTN');
1607 fnd_message.raise_error;
1608 --
1609 end if;
1610 --
1611 close c_ler;
1612 --
1613 hr_utility.set_location('After Cursor',40);
1614 hr_utility.set_location('Before evaluate life events',45);
1615 --
1616 ben_manage_life_events.evaluate_life_events
1617 (p_person_id => p_person_id,
1618 p_business_group_id => p_business_group_id,
1619 p_mode => p_mode,
1620 p_ler_id => p_ler_id,
1621 -- p_popl_enrt_typ_cycl_id => null,
1622 p_lf_evt_ocrd_dt => nvl(p_lf_evt_ocrd_dt,p_effective_date),
1623 p_effective_date => p_effective_date);
1624 --
1625 hr_utility.set_location('After evaluate life events',50);
1626 --
1627 -- save the benefit action id to be used by process_comp_objects.
1628 --
1629 g_benefit_action_id := benutils.g_benefit_action_id;
1630 --
1631 -- To synchronize with process_life_events procedure
1632 -- ben_manage_life_events.person_header is moved here.
1633 --
1634 hr_utility.set_location('Before Person Header',51);
1635 --
1636 if p_mode = 'W' then
1637 -- GLOBAL CWB : Call diferent procedures for different modes.
1638 benutils.get_active_life_event
1639 (p_person_id => p_person_id,
1640 p_business_group_id => p_business_group_id,
1641 p_effective_date => p_effective_date,
1642 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
1643 p_ler_id => p_ler_id,
1644 p_rec => l_rec);
1645 --
1646 -- GSP : Call diferent procedures for different modes.
1647 elsif p_mode = 'G' then
1648 --
1649 benutils.get_active_life_event
1650 (p_person_id => p_person_id,
1651 p_business_group_id => p_business_group_id,
1652 p_effective_date => p_effective_date,
1653 p_lf_event_mode => 'G',
1654 p_rec => l_rec);
1655 --
1656 elsif p_mode = 'U' then
1657 --
1658 benutils.get_active_life_event
1659 (p_person_id => p_person_id,
1660 p_business_group_id => p_business_group_id,
1661 p_effective_date => p_effective_date,
1662 p_lf_event_mode => 'U',
1663 p_rec => l_rec);
1664 elsif p_mode = 'M' then
1665 --
1666 benutils.get_active_life_event
1667 (p_person_id => p_person_id,
1668 p_business_group_id => p_business_group_id,
1669 p_effective_date => p_effective_date,
1670 p_lf_event_mode => 'M',
1671 p_rec => l_rec);
1672 --
1673 else
1674 --
1675 benutils.get_active_life_event
1676 (p_person_id => p_person_id,
1677 p_business_group_id => p_business_group_id,
1678 p_effective_date => p_effective_date,
1679 p_rec => l_rec);
1680 --
1681 end if;
1682 --
1683 --
1684 ben_manage_life_events.person_header
1685 (p_person_id => p_person_id,
1686 p_business_group_id => p_business_group_id,
1687 p_effective_date => nvl(l_rec.lf_evt_ocrd_dt,p_effective_date) );
1688 --
1689 hr_utility.set_location('After Person Header',52);
1690 --
1691 hr_utility.set_location('Before process comp objects',55);
1692 --
1693 hr_utility.set_location('p_ler_id '||p_ler_id,10);
1694 hr_utility.set_location('l_rec.lf_evt_ocrd_dt'||l_rec.lf_evt_ocrd_dt,10);
1695 --
1696 ben_manage_life_events.process_comp_objects
1697 (p_person_id => p_person_id,
1698 p_person_action_id => null,
1699 p_object_version_number => l_object_version_number,
1700 p_business_group_id => p_business_group_id,
1701 p_mode => p_mode,
1702 p_ler_id => p_ler_id,
1703 p_derivable_factors => 'ASC',
1704 p_person_count => p_person_count,
1705 -- p_popl_enrt_typ_cycl_id => null,
1706 p_effective_date => p_effective_date,
1707 p_lf_evt_ocrd_dt => nvl(l_rec.lf_evt_ocrd_dt,p_effective_date) --CWB GLOBAL
1708 );
1709 --
1710 hr_utility.set_location('After process comp objects',60);
1711
1712 -- start bug 4430107
1713 if p_mode <> 'U' then -- only for NON-unrestricted Life events
1714 for l_pgm in c_pgm loop
1715
1716 hr_utility.set_location('SUP: In c_pgm Loop :l_pgm.pgm_id '||l_pgm.pgm_id, 99);
1717
1718 for l_contact in c_contact loop
1719
1720 hr_utility.set_location('SUP: In c_contact Loop :l_contact.contact_person_id '||l_contact.contact_person_id, 99);
1721
1722 ben_determine_dpnt_elig_ss.main
1723 (p_pgm_id => l_pgm.pgm_id,
1724 p_per_in_ler_id => l_rec.per_in_ler_id,
1725 p_person_id => p_person_id,
1726 p_contact_person_id => l_contact.contact_person_id ,
1727 p_contact_relationship_id => l_contact.contact_relationship_id,
1728 p_effective_date => p_effective_date
1729 );
1730
1731
1732 end loop;
1733 end loop ;
1734
1735 end if; -- only for NON-unrestricted Life events
1736
1737 -- end bug 4430107
1738
1739
1740 exception
1741 when ben_manage_life_events.g_record_error then
1742 -- there is an error from benmngle, show the error message that is
1743 -- setup in benmngle.
1744 fnd_message.raise_error;
1745 when ben_manage_life_events.g_life_event_after then
1746 get_ser_message(p_encoded_message => l_encoded_message,
1747 p_app_short_name => l_app_short_name,
1748 p_message_name => l_message_name);
1749 -- Bug 4254792 Donot add substitute manual LE errors
1750 if (l_message_name = 'BEN_92396_LIFE_EVENT_MANUAL'
1751 or l_message_name = 'BEN_94209_MAN_LER_EXISTIS') then
1752 null;
1753 elsif l_message_name = 'BEN_92144_NO_LIFE_EVENTS' then --5211969
1754 null;
1755 else
1756 -- there is an error in benmngle. In this case display a special error
1757 -- message as the message in benmgle is too generic.
1758 fnd_message.set_name('BEN', 'BEN_91940_LIFE_EVT_AFTER');
1759 end if;
1760 fnd_message.raise_error;
1761
1762 when others then
1763 raise;
1764 --
1765 end p_manage_life_events;
1766 --
1767 --
1768 -- procedure to check whether the context is already established.
1769 -- if established then authentication form is bypassed.
1770 --
1771 procedure p_context_def(itemtype in varchar2,
1772 itemkey in varchar2,
1773 actid in number,
1774 funcmode in varchar2,
1775 resultout out nocopy varchar2) is
1776 --
1777 -- Variables to store the item attributes values.
1778 --
1779 l_context_set varchar2(50);
1780 l_mesg varchar2(1000);
1781 --
1782 begin
1783 --
1784 -- RUN mode - normal process execution.
1785 --
1786 if (funcmode = 'RUN' ) then
1787 --
1788 l_context_set := wf_engine.GetItemAttrTEXT(
1789 itemtype => itemtype,
1790 itemkey => itemkey,
1791 aname => 'BEN_IA_CONTEXT_SET');
1792 --
1793 if l_context_set = 'Y'
1794 then
1795 resultout := 'COMPLETE:Y';
1796 else
1797 resultout := 'COMPLETE:N';
1798 end if;
1799 return;
1800 --
1801 end if;
1802 --
1803 if (funcmode = 'CANCEL' ) then
1804 --
1805 -- Return process to run
1806 --
1807 resultout := 'COMPLETE:N';
1808 return;
1809 --
1810 end if;
1811 --
1812 if (funcmode = 'TIMEOUT' ) then
1813 --
1814 -- Return process to run
1815 --
1816 resultout := 'COMPLETE:N';
1817 return;
1818 --
1819 end if;
1820 --
1821 exception
1822 --
1823 when others then
1824 --
1825 -- The line below records this function call in the error system
1826 -- in the case of an exception.
1827 -- The error message is written to item attribute to be notified to the
1828 -- user. ?????
1829 l_mesg := fnd_message.get;
1830 --
1831 -- Completed with Errors.
1832 --
1833 resultout := 'COMPLETE:E';
1834 wf_core.context('ben_on_line_lf_evt', 'p_context_def' ,
1835 itemtype, itemkey, to_char(actid), funcmode);
1836 raise;
1837 --
1838 end p_context_def;
1839 --
1840 procedure p_commit is
1841 begin
1842 --
1843 commit;
1844 --
1845 end p_commit;
1846 --
1847 --
1848 -- This procedure to evaluate the potential life events,
1849 -- and life events called from benauthe form as a CSR
1850 -- desktop activity.
1851 --
1852 procedure p_evt_lf_evts_from_benauthe(
1853 p_person_id in number
1854 ,p_effective_date in date
1855 ,p_business_group_id in number
1856 ,p_pgm_id in number default null
1857 ,p_pl_id in number default null
1858 ,p_mode in varchar2
1859 ,p_popl_enrt_typ_cycl_id in number
1860 ,p_lf_evt_ocrd_dt in date
1861 ,p_prog_count out nocopy number
1862 ,p_plan_count out nocopy number
1863 ,p_oipl_count out nocopy number
1864 ,p_person_count out nocopy number
1865 ,p_plan_nip_count out nocopy number
1866 ,p_oipl_nip_count out nocopy number
1867 ,p_ler_id out nocopy number
1868 ,p_errbuf out nocopy varchar2
1869 ,p_retcode out nocopy number) is
1870 --
1871 -- local variables.
1872 l_package varchar2(80) := 'ben_on_line_lf_evt.p_evt_lf_evts_from_benauthe';
1873 l_object_version_number number;
1874 l_effective_date varchar2(30);
1875 l_lf_evt_ocrd_dt varchar2(30);
1876 l_chunk_size number := 0;
1877 l_threads number := 0;
1878 l_max_errors_allowed number;
1879 l_rec benutils.g_active_life_event;
1880 l_assignment_id number;
1881 --
1882 l_bft_id number;
1883 --
1884 cursor c_ler is
1885 select ler_id
1886 from ben_person_actions
1887 where BENEFIT_ACTION_ID = benutils.g_benefit_action_id;
1888 --
1889 begin
1890 --
1891 hr_utility.set_location(l_package || ' Before Process',5);
1892 --
1893 if p_effective_date is not null then
1894 --l_effective_date := to_char(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
1895 l_effective_date := fnd_date.date_to_canonical(p_effective_date);
1896 end if;
1897 if p_lf_evt_ocrd_dt is not null then
1898 --l_lf_evt_ocrd_dt := to_char(p_lf_evt_ocrd_dt,'YYYY/MM/DD HH24:MI:SS');
1899 l_lf_evt_ocrd_dt := fnd_date.date_to_canonical(p_lf_evt_ocrd_dt);
1900 end if;
1901 --
1902 -- Initialise the globals.
1903 --
1904 g_benefit_action_id := null;
1905 ben_person_object.clear_down_cache;
1906 --
1907 l_bft_id := null;
1908 --
1909 -- Bug 3486966
1910 --
1911 if p_mode in ('L', 'C','U') then
1912 l_assignment_id := null;
1913 l_assignment_id :=
1914 benutils.get_assignment_id(p_person_id=> p_person_id
1915 ,p_business_group_id => p_business_group_id
1916 ,p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date));
1917 --
1918 if l_assignment_id is null
1919 then
1920 fnd_message.set_name('BEN','BEN_93906_NO_EMP_BEN_ASG');
1921 fnd_message.raise_error;
1922 end if ;
1923 end if;
1924 ben_manage_life_events.process
1925 (
1926 errbuf => p_errbuf,
1927 retcode => p_retcode,
1928 p_benefit_action_id => l_bft_id,
1929 p_effective_date => l_effective_date,
1930 p_mode => p_mode,
1931 p_derivable_factors => 'ASC' ,
1932 p_validate => 'N',
1933 p_person_id => p_person_id,
1934 p_person_type_id => null,
1935 p_pgm_id => p_pgm_id,
1936 p_business_group_id => p_business_group_id,
1937 p_pl_id => p_pl_id,
1938 -- PB : 5422 :
1939 -- p_popl_enrt_typ_cycl_id => p_popl_enrt_typ_cycl_id,
1940 p_no_programs => 'N' ,
1941 p_no_plans => 'N' ,
1942 p_comp_selection_rule_id => null,
1943 p_person_selection_rule_id => null,
1944 p_ler_id => null,
1945 p_organization_id => null,
1946 p_benfts_grp_id => null,
1947 p_location_id => null,
1948 p_pstl_zip_rng_id => null,
1949 p_rptg_grp_id => null,
1950 p_pl_typ_id => null,
1951 p_opt_id => null,
1952 p_eligy_prfl_id => null,
1953 p_vrbl_rt_prfl_id => null,
1954 p_legal_entity_id => null,
1955 p_payroll_id => null,
1956 p_commit_data => 'N',
1957 p_lmt_prpnip_by_org_flag => nvl(fnd_profile.value('BEN_LMT_PRPNIP_BY_ORG_FLAG'), 'N'),
1958 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt );
1959 --
1960 hr_utility.set_location(l_package ||' After process',10);
1961 --
1962 hr_utility.set_location('Before get_parameter',21);
1963 --
1964 benutils.get_parameter
1965 (p_business_group_id => p_business_group_id,
1966 p_batch_exe_cd => 'BENMNGLE',
1967 p_threads => l_threads,
1968 p_chunk_size => l_chunk_size,
1969 p_max_errors => l_max_errors_allowed);
1970 --
1971 -- Set up benefits environment
1972 --
1973 hr_utility.set_location('After get_parameter',22);
1974 --
1975 -- Clear benmngle level caches
1976 --
1977 ben_manage_life_events.clear_init_benmngle_caches
1978 (p_business_group_id => p_business_group_id
1979 ,p_effective_date => p_effective_date
1980 ,p_threads => l_threads
1981 ,p_chunk_size => l_chunk_size
1982 ,p_max_errors => l_max_errors_allowed
1983 ,p_benefit_action_id => benutils.g_benefit_action_id
1984 ,p_thread_id => 1
1985 );
1986 --
1987 hr_utility.set_location('After ben_manage_life_events.clear_init_benmngle_caches',24);
1988 hr_utility.set_location('Before Cursor',35);
1989 --
1990 open c_ler;
1991 fetch c_ler into p_ler_id;
1992 --
1993 if c_ler%notfound then
1994 --
1995 -- 3948506: Changed the error message.
1996 fnd_message.set_name('BEN','BEN_92540_NOONE_TO_PROCESS_CM');
1997 --fnd_message.set_name('BEN','BEN_91791_LER_NOT_IN_PER_ACTN');
1998 fnd_message.raise_error;
1999 --
2000 end if;
2001 --
2002 close c_ler;
2003 --
2004 hr_utility.set_location('After Cursor',40);
2005 hr_utility.set_location('Before evaluate life events',45);
2006 --
2007 g_ptnls_voidd_flag := FALSE;
2008 ben_manage_life_events.g_modified_mode := null;
2009 ben_lf_evt_clps_restore.g_bckdt_pil_restored_flag := 'N';
2010 ben_manage_life_events.evaluate_life_events
2011 (p_person_id => p_person_id,
2012 p_business_group_id => p_business_group_id,
2013 p_mode => p_mode,
2014 p_ler_id => p_ler_id,
2015 -- PB : 5422 :
2016 -- p_popl_enrt_typ_cycl_id => p_popl_enrt_typ_cycl_id,
2017 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2018 p_effective_date => p_effective_date);
2019 --
2020 hr_utility.set_location('After evaluate life events',50);
2021 --
2022 -- save the benefit action id to be used by process_comp_objects.
2023 --
2024 g_benefit_action_id := benutils.g_benefit_action_id;
2025 --
2026 -- To synchronize with process_life_events procedure
2027 -- ben_manage_life_events.person_header is moved here.
2028 --
2029 hr_utility.set_location('Before Person Header',51);
2030 --
2031 -- GSP : Call diferent procedures for different modes.
2032 if p_mode = 'G' then
2033 --
2034 benutils.get_active_life_event
2035 (p_person_id => p_person_id,
2036 p_business_group_id => p_business_group_id,
2037 p_effective_date => p_effective_date,
2038 p_lf_event_mode => 'G',
2039 p_rec => l_rec);
2040 --
2041 elsif p_mode = 'U' then
2042 --
2043 benutils.get_active_life_event
2044 (p_person_id => p_person_id,
2045 p_business_group_id => p_business_group_id,
2046 p_effective_date => p_effective_date,
2047 p_lf_event_mode => 'U',
2048 p_rec => l_rec);
2049 elsif p_mode = 'M' then
2050 --
2051 benutils.get_active_life_event
2052 (p_person_id => p_person_id,
2053 p_business_group_id => p_business_group_id,
2054 p_effective_date => p_effective_date,
2055 p_lf_event_mode => 'M',
2056 p_rec => l_rec);
2057 --
2058 else
2059 --
2060 benutils.get_active_life_event
2061 (p_person_id => p_person_id,
2062 p_business_group_id => p_business_group_id,
2063 p_effective_date => p_effective_date,
2064 p_rec => l_rec);
2065 --
2066 end if;
2067 --
2068 ben_manage_life_events.person_header
2069 (p_person_id => p_person_id,
2070 p_business_group_id => p_business_group_id,
2071 p_effective_date => nvl(l_rec.lf_evt_ocrd_dt,p_effective_date) );
2072 --
2073 hr_utility.set_location('After Person Header',52);
2074 --
2075 exception
2076 when ben_manage_life_events.g_record_error then
2077 -- there is an error from benmngle, show the error message that is
2078 -- setup in benmngle.
2079 fnd_message.raise_error;
2080 when ben_manage_life_events.g_life_event_after then
2081 -- there is an error in benmngle. In this case display a special error
2082 -- message as the message in benmgle is too generic.
2083 -- fnd_message.set_name('BEN', 'BEN_91940_LIFE_EVT_AFTER');
2084 -- fnd_message.raise_error;
2085 null;
2086 when others then
2087 raise;
2088 --
2089 end p_evt_lf_evts_from_benauthe;
2090 --
2091
2092 --
2093 -- This procedure to process life events called from benauthe form as a CSR
2094 -- desktop activity.
2095 --
2096 procedure p_proc_lf_evts_from_benauthe(
2097 p_person_id in number
2098 ,p_effective_date in date
2099 ,p_business_group_id in number
2100 ,p_mode in varchar2
2101 ,p_ler_id in number
2102 -- PB : 5422 :
2103 -- ,p_popl_enrt_typ_cycl_id in number
2104 ,p_lf_evt_ocrd_dt in date default null
2105 ,p_person_count out nocopy number
2106 ,p_benefit_action_id out nocopy number
2107 ,p_errbuf out nocopy varchar2
2108 ,p_retcode out nocopy number) is
2109 --
2110 -- local variables.
2111 --
2112 cursor c_person_thread is
2113 select ben.object_version_number, ben.person_action_id
2114 from ben_person_actions ben
2115 where ben.benefit_action_id = g_benefit_action_id
2116 and ben.person_id = p_person_id
2117 and ben.action_status_cd <> 'P';
2118 --
2119 l_object_version_number number;
2120 l_person_action_id number;
2121 --
2122 begin
2123 --
2124 hr_utility.set_location('Before process comp objects',55);
2125 --
2126 --
2127 open c_person_thread;
2128 fetch c_person_thread into l_object_version_number, l_person_action_id;
2129 close c_person_thread;
2130 --
2131 ben_manage_life_events.process_comp_objects
2132 (p_person_id => p_person_id,
2133 p_person_action_id => l_person_action_id,
2134 p_object_version_number => l_object_version_number,
2135 p_business_group_id => p_business_group_id,
2136 p_mode => p_mode,
2137 p_ler_id => p_ler_id,
2138 p_derivable_factors => 'ASC',
2139 p_person_count => p_person_count,
2140 -- PB : 5422 :
2141 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2142 -- p_popl_enrt_typ_cycl_id => p_popl_enrt_typ_cycl_id,
2143 p_effective_date => p_effective_date
2144 );
2145 p_benefit_action_id := benutils.g_benefit_action_id;
2146 --
2147 -- Write results to log
2148 --
2149 benutils.WRITE_TABLE_AND_FILE(P_TABLE => TRUE, P_FILE => FALSE);
2150 --
2151 ben_cobj_cache.clear_down_cache;
2152 hr_utility.set_location('After process comp objects',60);
2153 --
2154 exception
2155 when ben_manage_life_events.g_record_error then
2156 -- there is an error from benmngle, show the error message that is
2157 -- setup in benmngle.
2158 fnd_message.raise_error;
2159 when ben_manage_life_events.g_life_event_after then
2160 -- there is an error in benmngle. In this case display a special error
2161 -- message as the message in benmgle is too generic.
2162 fnd_message.set_name('BEN', 'BEN_91940_LIFE_EVT_AFTER');
2163 fnd_message.raise_error;
2164
2165 when others then
2166 raise;
2167 --
2168 end p_proc_lf_evts_from_benauthe;
2169 --
2170 -- procedure to evaluate the potential life events,
2171 -- and life events
2172 --
2173 procedure p_watif_manage_life_events(
2174 p_person_id in number
2175 ,p_effective_date in date
2176 ,p_business_group_id in number
2177 ,p_pgm_id in number default null
2178 ,p_pl_id in number default null
2179 ,p_mode in varchar2
2180 ,p_derivable_factors in varchar2
2181 ,p_prog_count out nocopy number
2182 ,p_plan_count out nocopy number
2183 ,p_oipl_count out nocopy number
2184 ,p_person_count out nocopy number
2185 ,p_plan_nip_count out nocopy number
2186 ,p_oipl_nip_count out nocopy number
2187 ,p_ler_id out nocopy number
2188 ,p_errbuf out nocopy varchar2
2189 ,p_retcode out nocopy number) is
2190 --
2191 -- local variables.
2192 l_object_version_number number;
2193 l_effective_date varchar2(30);
2194 l_chunk_size number := 0;
2195 l_threads number := 0;
2196 l_max_errors_allowed number;
2197 --
2198 l_rec benutils.g_active_life_event;
2199 --
2200 l_bft_id number;
2201 --
2202 cursor c_ler is
2203 select ler_id
2204 from ben_person_actions
2205 where BENEFIT_ACTION_ID = benutils.g_benefit_action_id;
2206 --
2207 begin
2208 --
2209 hr_utility.set_location('Before Process',5);
2210 --
2211 -- Initialise the globals.
2212 --
2213 g_benefit_action_id := null;
2214 --
2215 --l_effective_date := to_char(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
2216 l_effective_date := fnd_date.date_to_canonical(p_effective_date);
2217 --
2218 l_bft_id := null;
2219 --
2220 ben_manage_life_events.process
2221 (
2222 errbuf => p_errbuf,
2223 retcode => p_retcode,
2224 p_benefit_action_id => l_bft_id,
2225 p_effective_date => l_effective_date,
2226 p_mode => p_mode,
2227 p_derivable_factors => p_derivable_factors,
2228 p_validate => 'N',
2229 p_person_id => p_person_id,
2230 p_person_type_id => null,
2231 p_pgm_id => p_pgm_id,
2232 p_business_group_id => p_business_group_id,
2233 p_pl_id => p_pl_id,
2234 p_popl_enrt_typ_cycl_id => null,
2235 p_no_programs => 'N' ,
2236 p_no_plans => 'N' ,
2237 p_comp_selection_rule_id => null,
2238 p_person_selection_rule_id => null,
2239 p_ler_id => null,
2240 p_organization_id => null,
2241 p_benfts_grp_id => null,
2242 p_location_id => null,
2243 p_pstl_zip_rng_id => null,
2244 p_rptg_grp_id => null,
2245 p_pl_typ_id => null,
2246 p_opt_id => null,
2247 p_eligy_prfl_id => null,
2248 p_vrbl_rt_prfl_id => null,
2249 p_legal_entity_id => null,
2250 p_payroll_id => null,
2251 p_commit_data => 'N',
2252 p_lmt_prpnip_by_org_flag => nvl(fnd_profile.value('BEN_LMT_PRPNIP_BY_ORG_FLAG'), 'N'),
2253 p_lf_evt_ocrd_dt => l_effective_date );
2254 --
2255 hr_utility.set_location('After process',10);
2256 hr_utility.set_location('Before get_parameter',21);
2257 --
2258 benutils.get_parameter
2259 (p_business_group_id => p_business_group_id,
2260 p_batch_exe_cd => 'BENMNGLE',
2261 p_threads => l_threads,
2262 p_chunk_size => l_chunk_size,
2263 p_max_errors => l_max_errors_allowed);
2264 --
2265 -- Set up benefits environment
2266 --
2267 hr_utility.set_location('After get_parameter',22);
2268 --
2269 -- Clear benmngle level caches
2270 --
2271 ben_manage_life_events.clear_init_benmngle_caches
2272 (p_business_group_id => p_business_group_id
2273 ,p_effective_date => p_effective_date
2274 ,p_threads => l_threads
2275 ,p_chunk_size => l_chunk_size
2276 ,p_max_errors => l_max_errors_allowed
2277 ,p_benefit_action_id => benutils.g_benefit_action_id
2278 ,p_thread_id => 1
2279 );
2280 --
2281 hr_utility.set_location('After ben_manage_life_events.clear_init_benmngle_caches',24);
2282 hr_utility.set_location('Before Cursor',35);
2283 --
2284 open c_ler;
2285 fetch c_ler into p_ler_id;
2286 --
2287 if c_ler%notfound then
2288 --
2289 fnd_message.set_name('BEN','BEN_91791_LER_NOT_IN_PER_ACTN');
2290 fnd_message.raise_error;
2291 --
2292 end if;
2293 --
2294 close c_ler;
2295 --
2296 hr_utility.set_location('After Cursor',40);
2297 hr_utility.set_location('Before evaluate life events',45);
2298 --
2299 ben_manage_life_events.evaluate_life_events
2300 (p_person_id => p_person_id,
2301 p_business_group_id => p_business_group_id,
2302 p_mode => p_mode,
2303 p_ler_id => p_ler_id,
2304 -- p_popl_enrt_typ_cycl_id => null,
2305 p_lf_evt_ocrd_dt => p_effective_date,
2306 p_effective_date => p_effective_date);
2307 --
2308 hr_utility.set_location('After evaluate life events',50);
2309 hr_utility.set_location('Before Person Header',25);
2310 --
2311 -- GSP : Call diferent procedures for different modes.
2312 if p_mode = 'G' then
2313 --
2314 benutils.get_active_life_event
2315 (p_person_id => p_person_id,
2316 p_business_group_id => p_business_group_id,
2317 p_effective_date => p_effective_date,
2318 p_lf_event_mode => 'G',
2319 p_rec => l_rec);
2320 --
2321 elsif p_mode = 'U' then
2322 --
2323 benutils.get_active_life_event
2324 (p_person_id => p_person_id,
2325 p_business_group_id => p_business_group_id,
2326 p_effective_date => p_effective_date,
2327 p_lf_event_mode => 'U',
2328 p_rec => l_rec);
2329 elsif p_mode = 'M' then
2330 --
2331 benutils.get_active_life_event
2332 (p_person_id => p_person_id,
2333 p_business_group_id => p_business_group_id,
2334 p_effective_date => p_effective_date,
2335 p_lf_event_mode => 'M',
2336 p_rec => l_rec);
2337 --
2338 else
2339 --
2340 benutils.get_active_life_event
2341 (p_person_id => p_person_id,
2342 p_business_group_id => p_business_group_id,
2343 p_effective_date => p_effective_date,
2344 p_rec => l_rec);
2345 --
2346 end if;
2347 --
2348 --
2349 ben_manage_life_events.person_header
2350 (p_person_id => p_person_id,
2351 p_business_group_id => p_business_group_id,
2352 p_effective_date => nvl(l_rec.lf_evt_ocrd_dt,p_effective_date));
2353 --
2354 hr_utility.set_location('After Person Header',30);
2355 hr_utility.set_location('Before process comp objects',55);
2356 --
2357 ben_manage_life_events.process_comp_objects
2358 (p_person_id => p_person_id,
2359 p_person_action_id => benutils.g_benefit_action_id, -- null,
2360 p_object_version_number => l_object_version_number,
2361 p_business_group_id => p_business_group_id,
2362 p_mode => p_mode,
2363 p_ler_id => p_ler_id,
2364 p_derivable_factors => p_derivable_factors,
2365 p_person_count => p_person_count,
2366 -- p_popl_enrt_typ_cycl_id => null,
2367 p_effective_date => p_effective_date
2368 );
2369 --
2370 hr_utility.set_location('After process comp objects',60);
2371 --
2372 exception
2373 when ben_manage_life_events.g_record_error then
2374 -- there is an error from benmngle, show the error message that is
2375 -- setup in benmngle.
2376 fnd_message.raise_error;
2377 when ben_manage_life_events.g_life_event_after then
2378 -- there is an error in benmngle. In this case display a special error
2379 -- message as the message in benmgle is too generic.
2380 -- BUG 2879140 -- change message as unrestricted enrollment does not make sense in watif
2381 fnd_message.set_name('BEN', 'BEN_93376_LIFE_EVT_AFTER');
2382 -- fnd_message.set_name('BEN', 'BEN_91940_LIFE_EVT_AFTER');
2383 fnd_message.raise_error;
2384
2385 when others then
2386 raise;
2387 --
2388 end p_watif_manage_life_events;
2389 --
2390 -- Procedure when called from a form message hook it will evaluates the
2391 -- fast formula which returns Y or N, if Y then form displays the associated
2392 -- message else nothing happens.
2393 --
2394 procedure p_oll_pop_up_message
2395 (p_person_id in number
2396 ,p_business_group_id in number
2397 ,p_function_name in varchar2
2398 ,p_block_name in varchar2
2399 ,p_field_name in varchar2
2400 ,p_event_name in varchar2
2401 ,p_effective_date in date
2402 ,p_payroll_id in number default null
2403 ,p_payroll_action_id in number default null
2404 ,p_assignment_id in number default null
2405 ,p_assignment_action_id in number default null
2406 ,p_org_pay_method_id in number default null
2407 ,p_per_pay_method_id in number default null
2408 ,p_organization_id in number default null
2409 ,p_tax_unit_id in number default null
2410 ,p_jurisdiction_code in number default null
2411 ,p_balance_date in number default null
2412 ,p_element_entry_id in number default null
2413 ,p_element_type_id in number default null
2414 ,p_original_entry_id in number default null
2415 ,p_tax_group in number default null
2416 ,p_pgm_id in number default null
2417 ,p_pl_id in number default null
2418 ,p_pl_typ_id in number default null
2419 ,p_opt_id in number default null
2420 ,p_ler_id in number default null
2421 ,p_communication_type_id in number default null
2422 ,p_action_type_id in number default null
2423 ,p_message_count out nocopy number
2424 ,p_message1 out nocopy varchar2
2425 ,p_message_type1 out nocopy varchar2
2426 ,p_message2 out nocopy varchar2
2427 ,p_message_type2 out nocopy varchar2
2428 ,p_message3 out nocopy varchar2
2429 ,p_message_type3 out nocopy varchar2
2430 ,p_message4 out nocopy varchar2
2431 ,p_message_type4 out nocopy varchar2
2432 ,p_message5 out nocopy varchar2
2433 ,p_message_type5 out nocopy varchar2
2434 ,p_message6 out nocopy varchar2
2435 ,p_message_type6 out nocopy varchar2
2436 ,p_message7 out nocopy varchar2
2437 ,p_message_type7 out nocopy varchar2
2438 ,p_message8 out nocopy varchar2
2439 ,p_message_type8 out nocopy varchar2
2440 ,p_message9 out nocopy varchar2
2441 ,p_message_type9 out nocopy varchar2
2442 ,p_message10 out nocopy varchar2
2443 ,p_message_type10 out nocopy varchar2
2444 ) is
2445 --
2446 cursor c_rule is
2447 select *
2448 from ben_pop_up_messages pop
2449 where pop.function_name = p_function_name
2450 and pop.event_name = p_event_name
2451 and nvl(pop.block_name, '***') = nvl(p_block_name, '***')
2452 and nvl(pop.field_name, '***') = nvl(p_field_name, '***')
2453 and pop.business_group_id+0 = p_business_group_id
2454 and p_effective_date
2455 between nvl(pop.start_date, p_effective_date)
2456 and nvl(pop.end_date, p_effective_date);
2457 --
2458 l_rule_rec c_rule%rowtype;
2459 --
2460 cursor per_asn is
2461 select assignment_id,organization_id
2462 from per_assignments_f paf
2463 where paf.person_id = p_person_id
2464 and paf.assignment_type <> 'C'
2465 and paf.primary_flag = 'Y'
2466 and paf.business_group_id + 0= p_business_group_id
2467 and p_effective_date between
2468 paf.effective_start_date and paf.effective_end_date;
2469 l_asn per_asn%rowtype;
2470
2471 Cursor c_state is
2472 select region_2
2473 from hr_locations_all loc,per_all_assignments_f asg
2474 where loc.location_id = asg.location_id
2475 and asg.person_id = p_person_id
2476 and asg.assignment_type <> 'C'
2477 and asg.primary_flag = 'Y'
2478 and p_effective_date between
2479 asg.effective_start_date and asg.effective_end_date
2480 and asg.business_group_id+0=p_business_group_id;
2481
2482 l_state c_state%rowtype;
2483 l_proc varchar2(80) := '.p_call_oll_ff';
2484 l_outputs ff_exec.outputs_t;
2485 l_return varchar2(30);
2486 l_formula_id number;
2487 l_jurisdiction_code varchar2(30);
2488 l_mess_type varchar2(10); -- 9999 delete it
2489 l_message_count number := 0;
2490 --
2491 begin
2492 --
2493 hr_utility.set_location ('Entering '||l_proc,10);
2494 p_message_type1 := null;
2495 p_message1 := null;
2496 p_message_type2 := null;
2497 p_message2 := null;
2498 p_message_type3 := null;
2499 p_message3 := null;
2500 p_message_type4 := null;
2501 p_message4 := null;
2502 p_message_type5 := null;
2503 p_message5 := null;
2504 p_message_type6 := null;
2505 p_message6 := null;
2506 p_message_type7 := null;
2507 p_message7 := null;
2508 p_message_type8 := null;
2509 p_message8 := null;
2510 p_message_type9 := null;
2511 p_message9 := null;
2512 p_message_type10 := null;
2513 p_message10 := null;
2514 p_message_count := null;
2515 --
2516 if p_person_id is not null then
2517 --
2518 -- Get assignment ID,organization_id form per_assignments_f table.
2519 --
2520 open per_asn;
2521 fetch per_asn into l_asn;
2522 if per_asn%notfound then
2523 --
2524 -- Defensive coding
2525 --
2526 close per_asn;
2527 raise no_data_found;
2528 end if;
2529 close per_asn;
2530 --
2531 /* -- 4031733 - Cursor c_state populates l_state variable which is no longer
2532 -- used in the package. Cursor can be commented
2533 open c_state;
2534 fetch c_state into l_state;
2535 close c_state;
2536 */
2537 --if l_state.region_2 is not null then
2538
2539 -- l_jurisdiction_code :=
2540 -- pay_mag_utils.lookup_jurisdiction_code
2541 -- (p_state => l_state.region_2);
2542
2543 --end if;
2544
2545 end if;
2546 --
2547 -- Get the message record
2548 --
2549 for l_rule_rec in c_rule loop
2550 --
2551 l_message_count := l_message_count + 1;
2552 if l_message_count > 10 then
2553 exit;
2554 end if;
2555 --
2556 if l_rule_rec.no_formula_flag = 'Y' then
2557 --
2558 -- Just return the message and message type
2559 -- As the case is to display a message
2560 -- without executing the fast formula
2561 -- Example : on WHEN-NEW-FORM-INSTANCE trigger display a message.
2562 --
2563 if l_message_count = 1 then
2564 p_message_type1 := l_rule_rec.message_type;
2565 p_message1 := l_rule_rec.message;
2566 elsif l_message_count = 2 then
2567 p_message_type2 := l_rule_rec.message_type;
2568 p_message2 := l_rule_rec.message;
2569 elsif l_message_count = 3 then
2570 p_message_type3 := l_rule_rec.message_type;
2571 p_message3 := l_rule_rec.message;
2572 elsif l_message_count = 4 then
2573 p_message_type4 := l_rule_rec.message_type;
2574 p_message4 := l_rule_rec.message;
2575 elsif l_message_count = 5 then
2576 p_message_type5 := l_rule_rec.message_type;
2577 p_message5 := l_rule_rec.message;
2578 elsif l_message_count = 6 then
2579 p_message_type6 := l_rule_rec.message_type;
2580 p_message6 := l_rule_rec.message;
2581 elsif l_message_count = 7 then
2582 p_message_type7 := l_rule_rec.message_type;
2583 p_message7 := l_rule_rec.message;
2584 elsif l_message_count = 8 then
2585 p_message_type8 := l_rule_rec.message_type;
2586 p_message8 := l_rule_rec.message;
2587 elsif l_message_count = 9 then
2588 p_message_type9 := l_rule_rec.message_type;
2589 p_message9 := l_rule_rec.message;
2590 elsif l_message_count = 10 then
2591 p_message_type10 := l_rule_rec.message_type;
2592 p_message10 := l_rule_rec.message;
2593 end if;
2594
2595 -- p_message_type := l_rule_rec.message_type;
2596 -- p_message := l_rule_rec.message;
2597 -- copy(l_rule_rec.message_type, 'p_message_type' || to_char(l_message_count));
2598 -- copy(l_rule_rec.message, 'p_message' || to_char(l_message_count));
2599 --
2600 else
2601 --
2602 -- Call formula initialise routine
2603 --
2604 l_outputs := benutils.formula
2605 (p_formula_id => l_rule_rec.formula_id
2606 ,p_effective_date => p_effective_date
2607 ,p_assignment_id => l_asn.assignment_id
2608 ,p_organization_id => l_asn.organization_id
2609 ,p_business_group_id =>p_business_group_id
2610 ,p_pgm_id =>p_pgm_id
2611 ,p_pl_id =>p_pl_id
2612 ,p_pl_typ_id =>p_pl_typ_id
2613 ,p_opt_id =>p_opt_id
2614 ,p_ler_id =>p_ler_id
2615 ,p_jurisdiction_code =>l_jurisdiction_code);
2616 --
2617 l_return := l_outputs(l_outputs.first).value;
2618 --
2619 if upper(l_return) not in ('Y', 'N') then
2620 --
2621 -- Defensive coding
2622 -- Just return 'N' means no popup in case of non Y or N.
2623 --
2624 l_return := 'N';
2625 --
2626 end if;
2627 if upper(l_return) = 'Y' then
2628 --
2629 -- Now display the message based on the message type
2630 --
2631 -- copy(l_rule_rec.message_type, 'p_message_type' || to_char(l_message_count));
2632 -- copy(l_rule_rec.message, 'p_message' || to_char(l_message_count));
2633 if l_message_count = 1 then
2634 p_message_type1 := l_rule_rec.message_type;
2635 p_message1 := l_rule_rec.message;
2636 elsif l_message_count = 2 then
2637 p_message_type2 := l_rule_rec.message_type;
2638 p_message2 := l_rule_rec.message;
2639 elsif l_message_count = 3 then
2640 p_message_type3 := l_rule_rec.message_type;
2641 p_message3 := l_rule_rec.message;
2642 elsif l_message_count = 4 then
2643 p_message_type4 := l_rule_rec.message_type;
2644 p_message4 := l_rule_rec.message;
2645 elsif l_message_count = 5 then
2646 p_message_type5 := l_rule_rec.message_type;
2647 p_message5 := l_rule_rec.message;
2648 elsif l_message_count = 6 then
2649 p_message_type6 := l_rule_rec.message_type;
2650 p_message6 := l_rule_rec.message;
2651 elsif l_message_count = 7 then
2652 p_message_type7 := l_rule_rec.message_type;
2653 p_message7 := l_rule_rec.message;
2654 elsif l_message_count = 8 then
2655 p_message_type8 := l_rule_rec.message_type;
2656 p_message8 := l_rule_rec.message;
2657 elsif l_message_count = 9 then
2658 p_message_type9 := l_rule_rec.message_type;
2659 p_message9 := l_rule_rec.message;
2660 elsif l_message_count = 10 then
2661 p_message_type10 := l_rule_rec.message_type;
2662 p_message10 := l_rule_rec.message;
2663 end if;
2664 --
2665 end if;
2666 --
2667 end if;
2668 --
2669 end loop;
2670 p_message_count := l_message_count;
2671 hr_utility.set_location ('Leaving '||l_proc,10);
2672 --
2673 return ;
2674 Exception
2675 when others then
2676 --
2677 -- Just return 'N' means no popup. Really don't stop the further processing.
2678 --
2679 l_return := 'N';
2680 return;
2681 --
2682 end p_oll_pop_up_message;
2683 --
2684 function f_ret_ptnls_voidd_flag return boolean is
2685 begin
2686 --
2687 return g_ptnls_voidd_flag;
2688 --
2689 end;
2690 --
2691 --
2692 -- Bug : 4504/1217193 This procedure is called from the form
2693 -- BENWFREP to see whether any electable choices are created by this
2694 -- run of benmngle. If not created then return a message.
2695 --
2696 function f_ret_elec_chc_created return boolean is
2697 begin
2698 --
2699 return ben_enrolment_requirements.g_electable_choice_created;
2700 --
2701 end;
2702 --
2703 -- Parse_encoded is not working properly on form side.
2704 -- So this procedure is called from form to retrive the
2705 -- message name. Based on the message BENAUTHE decides
2706 -- what mwssage to diaply after evaluating the potential
2707 -- life events.
2708 --
2709 procedure get_ser_message(p_encoded_message out nocopy varchar2,
2710 p_app_short_name out nocopy varchar2,
2711 p_message_name out nocopy varchar2) is
2712 --
2713 l_encoded_message varchar2(2000);
2714 l_app_short_name varchar2(2000);
2715 l_message_name varchar2(2000);
2716 --
2717 begin
2718 --
2719 p_encoded_message := null;
2720 p_app_short_name := null;
2721 p_message_name := null;
2722 p_encoded_message := fnd_message.get_encoded;
2723 fnd_message.parse_encoded(encoded_message => p_encoded_message,
2724 app_short_name => p_app_short_name,
2725 message_name => p_message_name);
2726 --
2727 if p_message_name is not null then
2728 fnd_message.set_encoded(encoded_message => p_encoded_message);
2729 end if;
2730 --
2731 end;
2732 --
2733 --
2734 -- self-service wrapper to run benmngle in
2735 -- unrestricted mode.
2736 --
2737 procedure p_manage_life_events_w(
2738 p_person_id in number
2739 ,p_effective_date in date
2740 ,p_lf_evt_ocrd_dt in date default null
2741 ,p_business_group_id in number
2742 ,p_mode in varchar2
2743 ,p_ss_process_unrestricted in varchar2 default 'Y'
2744 ,p_return_status out nocopy varchar2) is
2745 --
2746 l_proc varchar2(72) := g_package||'p_manage_life_events_w';
2747 l_errbuf varchar2(2000);
2748 l_retcode number;
2749 l_prog_count number;
2750 l_plan_count number;
2751 l_oipl_count number;
2752 l_person_count number;
2753 l_plan_nip_count number;
2754 l_oipl_nip_count number;
2755 l_ler_id number;
2756 --
2757 l_encoded_message varchar2(2000);
2758 l_app_short_name varchar2(2000);
2759 l_message_name varchar2(2000);
2760 --
2761 --
2762 cursor c_open(p_lf_evt_ocrd_dt date,
2763 p_person_id number ) is
2764 select ler.typ_cd
2765 from ben_ptnl_ler_for_per pel,
2766 ben_ler_f ler
2767 where p_effective_date between ler.effective_start_date
2768 and ler.effective_end_date
2769 and ler.typ_cd in ( 'SCHEDDO','SCHEDDA')
2770 and pel.ptnl_ler_for_per_stat_cd = 'UNPROCD'
2771 and pel.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
2772 and p_person_id = pel.person_id
2773 and pel.ler_id =ler.ler_id ;
2774 --
2775 l_open varchar2(30) := 'N' ;
2776 l_effective_date date := NVL(p_lf_evt_ocrd_dt,p_effective_date);
2777 l_mode varchar2(30) := p_mode ;
2778 --
2779 l_trace_param varchar2(30);
2780 l_trace_on boolean;
2781 --
2782 begin
2783 p_return_status := 'S';
2784 l_trace_param := null;
2785 l_trace_on := false;
2786 --
2787 l_trace_param := fnd_profile.value('BEN_SS_TRACE_VALUE');
2788 --
2789
2790 if l_trace_param = 'BENOLLET' then
2791 l_trace_on := true;
2792 else
2793 l_trace_on := false;
2794 end if;
2795 --
2796 if l_trace_on then
2797 hr_utility.trace_on(null,'BENOLLET');
2798 end if;
2799 --
2800 hr_utility.set_location('l_trace_param : '|| l_trace_param, 5);
2801 hr_utility.set_location ('Entering '||l_proc,10);
2802 --
2803 fnd_msg_pub.initialize;
2804
2805 /* One-Off bug 3697615
2806 The behavior of SS form function parameter 'ssProcessUnrestricted' will be as follows:
2807 1. If set to Y, it will always run Unrestricted Benmngle
2808 2. If set to P, [P for performance] it will not run unrestricted Benmngle at
2809 most of the cases (except when the person's data which affects eligibility has
2810 got changed since the last time Unrestricted was run). This is determined by calling
2811 benutils.run_osb_benmngle_flag
2812 3. If set to N, it will not run Unrestricted at all and it will also prevent the user
2813 from making any changes to his elections for programs enrolled into through Unrestricted LE.
2814 */
2815
2816 if (p_mode <> 'U'
2817 or
2818 (p_mode = 'U' and p_ss_process_unrestricted <> 'N'
2819 and (p_ss_process_unrestricted = 'Y'
2820 or (p_ss_process_unrestricted = 'P' and
2821 (benutils.run_osb_benmngle_flag(p_person_id,p_business_group_id,
2822 p_effective_date)))))) then
2823 --
2824 hr_utility.set_location ('Calling p_manage_life_events in '||p_mode||' p_mode ', 20);
2825 --
2826 if p_lf_evt_ocrd_dt IS NOT NULL THEN
2827 open c_open(p_lf_evt_ocrd_dt,p_person_id);
2828 fetch c_open into l_open ;
2829 if c_open%found then
2830 l_effective_date := p_effective_date ;
2831 l_mode := 'C';
2832 end if;
2833 close c_open ;
2834 end if;
2835 --
2836 --5194398: Check if POPL is locked
2837 if p_mode = 'U' then
2838 declare
2839 cursor c_popl_lock(p_per_in_ler_id number) is
2840 select null
2841 from ben_pil_elctbl_chc_popl pel
2842 where per_in_ler_id = p_per_in_ler_id
2843 for update nowait;
2844 l_test number;
2845
2846 record_locked EXCEPTION;
2847 PRAGMA EXCEPTION_INIT (record_locked, -54);
2848 l_rec benutils.g_active_life_event;
2849
2850 begin
2851 savepoint ben_popl_lock_savepoint;
2852
2853 benutils.get_active_life_event
2854 (p_person_id => p_person_id,
2855 p_business_group_id => p_business_group_id,
2856 p_effective_date => l_effective_date,
2857 p_lf_event_mode => 'U',
2858 p_rec => l_rec);
2859
2860 open c_popl_lock(l_rec.per_in_ler_id);
2861 fetch c_popl_lock into l_test;
2862 close c_popl_lock;
2863
2864 rollback to ben_popl_lock_savepoint; --in order to release the lock(if obtained) on POPL
2865
2866 exception
2867 when record_locked then
2868 hr_utility.set_location ('POPL locked. Returning without processing unrestricted.',25);
2869 return; --POPL is locked, so return to calling procedure without processing unrestricted
2870 end;
2871 end if;
2872 --
2873 ben_on_line_lf_evt.p_manage_life_events
2874 (p_person_id => p_person_id
2875 ,p_effective_date => l_effective_date -- p_effective_date
2876 ,p_business_group_id => p_business_group_id
2877 ,p_pgm_id => null
2878 ,p_pl_id => null
2879 ,p_mode => l_mode -- p_mode
2880 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
2881 ,p_prog_count => l_prog_count
2882 ,p_plan_count => l_plan_count
2883 ,p_oipl_count => l_oipl_count
2884 ,p_person_count => l_person_count
2885 ,p_plan_nip_count => l_plan_nip_count
2886 ,p_oipl_nip_count => l_oipl_nip_count
2887 ,p_ler_id => l_ler_id
2888 ,p_errbuf => l_errbuf
2889 ,p_retcode => l_retcode
2890 );
2891 --
2892 end if;
2893
2894 -- If no execption raised, clear the message cache to avoid
2895 -- getting "Calling ben_generate_communications".
2896 --
2897 fnd_msg_pub.initialize;
2898 --
2899 commit;
2900 --
2901 hr_utility.set_location ('Leaving '||l_proc,30);
2902 --
2903 if l_trace_on then
2904 hr_utility.trace_off;
2905 l_trace_param := null;
2906 l_trace_on := false;
2907 end if;
2908 --
2909 exception
2910 when app_exception.application_exception then
2911 get_ser_message(p_encoded_message => l_encoded_message,
2912 p_app_short_name => l_app_short_name,
2913 p_message_name => l_message_name);
2914 --
2915 -- Kill the error if it is
2916 -- "No comp objects selected".
2917 -- Bug 1972460.
2918 --
2919 if (l_message_name like '%BEN_91769_NOONE_TO_PROCESS%') then
2920 fnd_message.set_name('BEN', 'BEN_92540_NOONE_TO_PROCESS_CM');
2921 -- l_encoded_message := fnd_message.get;
2922 -- copy(l_encoded_message, 'messages.message');
2923 end if;
2924 -- Bug 4254792 Donot add manual LE errors to stack
2925 if (l_message_name = 'BEN_92396_LIFE_EVENT_MANUAL'
2926 or l_message_name = 'BEN_94209_MAN_LER_EXISTIS') then
2927 p_return_status := 'M';
2928 elsif l_message_name = 'BEN_92144_NO_LIFE_EVENTS' then --5211969
2929 commit;
2930 elsif (l_message_name = 'BEN_91664_BENMNGLE_NO_OBJECTS') then
2931 l_encoded_message := fnd_message.get_encoded;
2932 rollback;
2933 else
2934 p_return_status := 'E';
2935 fnd_msg_pub.add;
2936 rollback;
2937 end if;
2938 --
2939 if l_trace_on then
2940 hr_utility.trace_off;
2941 l_trace_param := null;
2942 l_trace_on := false;
2943 end if;
2944 --rollback;
2945 --
2946 when others then
2947 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
2948 fnd_message.set_token('2',substr(sqlerrm,1,200));
2949 fnd_msg_pub.add;
2950 p_return_status := 'E';
2951 if l_trace_on then
2952 hr_utility.trace_off;
2953 l_trace_param := null;
2954 l_trace_on := false;
2955 end if;
2956 end p_manage_life_events_w;
2957 --
2958 --
2959 -- self-service wrapper to run benmngle through
2960 -- iRecruitment
2961 --
2962 procedure p_manage_irec_life_events_w(
2963 p_person_id in number
2964 ,p_assignment_id in number
2965 ,p_effective_date in date
2966 ,p_business_group_id in number
2967 ,p_offer_assignment_rec in per_all_assignments_f%rowtype) --bug 4621751 irec2
2968
2969 is
2970 --
2971 l_proc varchar2(72) := g_package || 'p_manage_irec_life_events_w';
2972 l_errbuf varchar2(2000);
2973 l_retcode number;
2974 l_effective_date varchar2(30);
2975 --
2976 l_encoded_message varchar2(2000);
2977 l_app_short_name varchar2(2000);
2978 l_message_name varchar2(2000);
2979 --
2980
2981 begin
2982 --
2983 -- Create a save point.
2984 savepoint irec_life_events_savepoint; --irec2
2985
2986 --hr_utility.trace_on(NULL, 'IREC2'); -- ACE
2987 --
2988 hr_utility.set_location ('Entering '||l_proc,10);
2989 --
2990 fnd_msg_pub.initialize;
2991 --
2992 hr_utility.set_location ('Calling p_manage_irec_life_events_w in iRecruitment (I) mode', 20);
2993 --
2994 -- l_effective_date := to_char(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
2995 l_effective_date := fnd_date.date_to_canonical(p_effective_date);
2996 --
2997 ben_comp_object_list1.refresh_eff_date_caches;
2998 --
2999 ben_manage_life_events.irec_process
3000 (errbuf => l_errbuf,
3001 retcode => l_retcode,
3002 p_effective_date => l_effective_date,
3003 p_mode => 'I', -- Mode for iRecruitment = 'I'
3004 p_person_id => p_person_id,
3005 p_business_group_id => p_business_group_id,
3006 p_assignment_id => p_assignment_id,
3007 p_offer_assignment_rec => p_offer_assignment_rec); --bug 4621751 irec2
3008 --
3009
3010 -- If no execption raised, clear the message cache to avoid
3011 -- getting "Calling ben_generate_communications".
3012 --
3013 fnd_msg_pub.initialize;
3014 --
3015 commit;
3016 --
3017 hr_utility.set_location ('Leaving '||l_proc,30);
3018 -- hr_utility.trace_off;
3019 --
3020 exception
3021 when app_exception.application_exception then
3022 get_ser_message(p_encoded_message => l_encoded_message,
3023 p_app_short_name => l_app_short_name,
3024 p_message_name => l_message_name);
3025 --
3026 -- Kill the error if it is
3027 -- "No comp objects selected".
3028 -- Bug 1972460.
3029 --
3030 if (l_message_name like '%BEN_91769_NOONE_TO_PROCESS%') then
3031 fnd_message.set_name('BEN', 'BEN_92540_NOONE_TO_PROCESS_CM');
3032 -- l_encoded_message := fnd_message.get;
3033 -- copy(l_encoded_message, 'messages.message');
3034 end if;
3035 if (l_message_name = 'BEN_91664_BENMNGLE_NO_OBJECTS') then
3036 l_encoded_message := fnd_message.get_encoded;
3037 else
3038 fnd_msg_pub.add;
3039 end if;
3040
3041 rollback to irec_life_events_savepoint;
3042 --
3043 when others then
3044 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
3045 fnd_message.set_token('2',substr(sqlerrm,1,200));
3046 fnd_msg_pub.add;
3047 -- hr_utility.set_location(' EXCEPTION CAUGHT',9909);
3048 --hr_utility.trace_off;
3049 rollback to irec_life_events_savepoint;
3050 end p_manage_irec_life_events_w;
3051 --
3052 end ben_on_line_lf_evt;