[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.9.12020000.4 2012/10/17 13:52:39 amnaraya 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 savepoint p_manage_life_events_savepoint; -- Bug 8290746
1483 /*
1484 l_effective_date := to_char(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
1485 l_lf_evt_ocrd_dt := to_char(nvl(p_lf_evt_ocrd_dt,p_effective_date),'YYYY/MM/DD HH24:MI:SS');
1486 */
1487 --
1488 l_effective_date := fnd_date.date_to_canonical(p_effective_date);
1489 l_lf_evt_ocrd_dt := fnd_date.date_to_canonical(NVL(p_lf_evt_ocrd_dt,p_effective_date));
1490 --
1491 -- Bug 3486966
1492 --
1493 if p_mode in ('L', 'C','U') then
1494 l_assignment_id := null;
1495 l_assignment_id :=
1496 benutils.get_assignment_id(p_person_id=> p_person_id
1497 ,p_business_group_id => p_business_group_id
1498 ,p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date));
1499 --
1500 if l_assignment_id is null
1501 then
1502 fnd_message.set_name('BEN','BEN_93906_NO_EMP_BEN_ASG');
1503 fnd_message.raise_error;
1504 end if ;
1505 end if;
1506 l_bft_id := null;
1507 ben_manage_life_events.g_modified_mode := null;
1508 --
1509 ben_comp_object_list1.refresh_eff_date_caches;
1510 --
1511
1512 ben_manage_life_events.process
1513 (
1514 errbuf => p_errbuf,
1515 retcode => p_retcode,
1516 p_benefit_action_id => l_bft_id,
1517 p_effective_date => l_effective_date,
1518 p_mode => p_mode,
1519 p_derivable_factors => 'ASC' ,
1520 p_validate => 'N',
1521 p_person_id => p_person_id,
1522 p_person_type_id => null,
1523 p_pgm_id => p_pgm_id,
1524 p_business_group_id => p_business_group_id,
1525 p_pl_id => p_pl_id,
1526 p_popl_enrt_typ_cycl_id => null,
1527 p_no_programs => 'N' ,
1528 p_no_plans => 'N' ,
1529 p_comp_selection_rule_id => null,
1530 p_person_selection_rule_id => null,
1531 p_ler_id => null,
1532 p_organization_id => null,
1533 p_benfts_grp_id => null,
1534 p_location_id => null,
1535 p_pstl_zip_rng_id => null,
1536 p_rptg_grp_id => null,
1537 p_pl_typ_id => null,
1538 p_opt_id => null,
1539 p_eligy_prfl_id => null,
1540 p_vrbl_rt_prfl_id => null,
1541 p_legal_entity_id => null,
1542 p_payroll_id => null,
1543 p_commit_data => 'N',
1544 p_lmt_prpnip_by_org_flag => nvl(fnd_profile.value('BEN_LMT_PRPNIP_BY_ORG_FLAG'), 'N'),
1545 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt ); -- GLOBAL CWB l_effective_date );
1546 --
1547 hr_utility.set_location('After process',10);
1548 hr_utility.set_location('Before get_parameter',21);
1549 --
1550 benutils.get_parameter
1551 (p_business_group_id => p_business_group_id,
1552 p_batch_exe_cd => 'BENMNGLE',
1553 p_threads => l_threads,
1554 p_chunk_size => l_chunk_size,
1555 p_max_errors => l_max_errors_allowed);
1556 --
1557 -- Set up benefits environment
1558 --
1559 hr_utility.set_location('After get_parameter',22);
1560 hr_utility.set_location('Before clear_init_benmngle_caches',23);
1561 --
1562 ben_manage_life_events.clear_init_benmngle_caches
1563 (p_business_group_id => p_business_group_id
1564 ,p_effective_date => p_effective_date
1565 ,p_threads => l_threads
1566 ,p_chunk_size => l_chunk_size
1567 ,p_max_errors => l_max_errors_allowed
1568 ,p_benefit_action_id => benutils.g_benefit_action_id
1569 ,p_thread_id => 1
1570 );
1571 hr_utility.set_location('After clear_init_benmngle_caches',24);
1572 hr_utility.set_location('Before Build Comp Object List',30);
1573 --
1574 if (p_mode = 'U')
1575 then
1576 ben_comp_object_list.build_comp_object_list
1577 (p_benefit_action_id => benutils.g_benefit_action_id,
1578 p_comp_selection_rule_id => null,
1579 p_effective_date => p_effective_date,
1580 p_pgm_id => p_pgm_id,
1581 p_business_group_id => p_business_group_id,
1582 p_pl_id => p_pl_id,
1583 p_oipl_id => null,
1584 -- p_popl_enrt_typ_cycl_id => null,
1585 p_asnd_lf_evt_dt => null,
1586 p_no_programs => 'N',
1587 p_no_plans => 'N',
1588 p_rptg_grp_id => null,
1589 p_pl_typ_id => null,
1590 p_opt_id => null,
1591 p_eligy_prfl_id => null,
1592 p_vrbl_rt_prfl_id => null,
1593 p_thread_id => 1,
1594 p_mode => p_mode);
1595 --
1596 ben_manage_life_events.g_modified_mode := 'U';
1597 --
1598 end if;
1599 hr_utility.set_location('After build comp object',32);
1600 hr_utility.set_location('Before Cursor',35);
1601 --
1602 open c_ler;
1603 fetch c_ler into p_ler_id;
1604 --
1605 if c_ler%notfound then
1606 --
1607 fnd_message.set_name('BEN','BEN_91791_LER_NOT_IN_PER_ACTN');
1608 fnd_message.raise_error;
1609 --
1610 end if;
1611 --
1612 close c_ler;
1613 --
1614 hr_utility.set_location('After Cursor',40);
1615 hr_utility.set_location('Before evaluate life events',45);
1616 --
1617 ben_manage_life_events.evaluate_life_events
1618 (p_person_id => p_person_id,
1619 p_business_group_id => p_business_group_id,
1620 p_mode => p_mode,
1621 p_ler_id => p_ler_id,
1622 -- p_popl_enrt_typ_cycl_id => null,
1623 p_lf_evt_ocrd_dt => nvl(p_lf_evt_ocrd_dt,p_effective_date),
1624 p_effective_date => p_effective_date);
1625 --
1626 hr_utility.set_location('After evaluate life events',50);
1627 --
1628 -- save the benefit action id to be used by process_comp_objects.
1629 --
1630 g_benefit_action_id := benutils.g_benefit_action_id;
1631 --
1632 -- To synchronize with process_life_events procedure
1633 -- ben_manage_life_events.person_header is moved here.
1634 --
1635 hr_utility.set_location('Before Person Header',51);
1636 --
1637 if p_mode = 'W' then
1638 -- GLOBAL CWB : Call diferent procedures for different modes.
1639 benutils.get_active_life_event
1640 (p_person_id => p_person_id,
1641 p_business_group_id => p_business_group_id,
1642 p_effective_date => p_effective_date,
1643 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
1644 p_ler_id => p_ler_id,
1645 p_rec => l_rec);
1646 --
1647 -- GSP : Call diferent procedures for different modes.
1648 elsif p_mode = 'G' then
1649 --
1650 benutils.get_active_life_event
1651 (p_person_id => p_person_id,
1652 p_business_group_id => p_business_group_id,
1653 p_effective_date => p_effective_date,
1654 p_lf_event_mode => 'G',
1655 p_rec => l_rec);
1656 --
1657 elsif p_mode = 'U' then
1658 --
1659 benutils.get_active_life_event
1660 (p_person_id => p_person_id,
1661 p_business_group_id => p_business_group_id,
1662 p_effective_date => p_effective_date,
1663 p_lf_event_mode => 'U',
1664 p_rec => l_rec);
1665 elsif p_mode = 'M' then
1666 --
1667 benutils.get_active_life_event
1668 (p_person_id => p_person_id,
1669 p_business_group_id => p_business_group_id,
1670 p_effective_date => p_effective_date,
1671 p_lf_event_mode => 'M',
1672 p_rec => l_rec);
1673 --
1674 else
1675 --
1676 benutils.get_active_life_event
1677 (p_person_id => p_person_id,
1678 p_business_group_id => p_business_group_id,
1679 p_effective_date => p_effective_date,
1680 p_rec => l_rec);
1681 --
1682 end if;
1683 --
1684 --
1685 ben_manage_life_events.person_header
1686 (p_person_id => p_person_id,
1687 p_business_group_id => p_business_group_id,
1688 p_effective_date => nvl(l_rec.lf_evt_ocrd_dt,p_effective_date) );
1689 --
1690 hr_utility.set_location('After Person Header',52);
1691 --
1692 hr_utility.set_location('Before process comp objects',55);
1693 --
1694 hr_utility.set_location('p_ler_id '||p_ler_id,10);
1695 hr_utility.set_location('l_rec.lf_evt_ocrd_dt'||l_rec.lf_evt_ocrd_dt,10);
1696 --
1697 ben_manage_life_events.process_comp_objects
1698 (p_person_id => p_person_id,
1699 p_person_action_id => null,
1700 p_object_version_number => l_object_version_number,
1701 p_business_group_id => p_business_group_id,
1702 p_mode => p_mode,
1703 p_ler_id => p_ler_id,
1704 p_derivable_factors => 'ASC',
1705 p_person_count => p_person_count,
1706 -- p_popl_enrt_typ_cycl_id => null,
1707 p_effective_date => p_effective_date,
1708 p_lf_evt_ocrd_dt => nvl(l_rec.lf_evt_ocrd_dt,p_effective_date) --CWB GLOBAL
1709 );
1710 --
1711 hr_utility.set_location('After process comp objects',60);
1712
1713 -- start bug 4430107
1714 if p_mode <> 'U' then -- only for NON-unrestricted Life events
1715 for l_pgm in c_pgm loop
1716
1717 hr_utility.set_location('SUP: In c_pgm Loop :l_pgm.pgm_id '||l_pgm.pgm_id, 99);
1718
1719 for l_contact in c_contact loop
1720
1721 hr_utility.set_location('SUP: In c_contact Loop :l_contact.contact_person_id '||l_contact.contact_person_id, 99);
1722
1723 ben_determine_dpnt_elig_ss.main
1724 (p_pgm_id => l_pgm.pgm_id,
1725 p_per_in_ler_id => l_rec.per_in_ler_id,
1726 p_person_id => p_person_id,
1727 p_contact_person_id => l_contact.contact_person_id ,
1728 p_contact_relationship_id => l_contact.contact_relationship_id,
1729 p_effective_date => p_effective_date
1730 );
1731
1732
1733 end loop;
1734 end loop ;
1735
1736 end if; -- only for NON-unrestricted Life events
1737
1738 -- end bug 4430107
1739
1740
1741 exception
1742 when ben_manage_life_events.g_record_error then
1743 -- there is an error from benmngle, show the error message that is
1744 -- setup in benmngle.
1745 fnd_message.raise_error;
1746 when ben_manage_life_events.g_life_event_after then
1747 get_ser_message(p_encoded_message => l_encoded_message,
1748 p_app_short_name => l_app_short_name,
1749 p_message_name => l_message_name);
1750 -- Bug 4254792 Donot add substitute manual LE errors
1751 if (l_message_name = 'BEN_92396_LIFE_EVENT_MANUAL'
1752 or l_message_name = 'BEN_94209_MAN_LER_EXISTIS') then
1753 null;
1754 elsif l_message_name = 'BEN_92144_NO_LIFE_EVENTS' then --5211969
1755 null;
1756 elsif l_message_name = 'BEN_94750_NO_LIFE_EVENTS' then--AMN Bug 14680386
1757 null;
1758 else
1759 -- there is an error in benmngle. In this case display a special error
1760 -- message as the message in benmgle is too generic.
1761 fnd_message.set_name('BEN', 'BEN_91940_LIFE_EVT_AFTER');
1762 end if;
1763 fnd_message.raise_error;
1764
1765 -- Bug 8290746
1766 when ben_manage_life_events.g_cwb_trk_ineligible then
1767 --
1768 hr_utility.set_location ('Exception g_cwb_trk_ineligible ',20);
1769
1770 --
1771 rollback to p_manage_life_events_savepoint;
1772
1773 -- Bug 8290746
1774 when others then
1775 raise;
1776 --
1777 end p_manage_life_events;
1778 --
1779 --
1780 -- procedure to check whether the context is already established.
1781 -- if established then authentication form is bypassed.
1782 --
1783 procedure p_context_def(itemtype in varchar2,
1784 itemkey in varchar2,
1785 actid in number,
1786 funcmode in varchar2,
1787 resultout out nocopy varchar2) is
1788 --
1789 -- Variables to store the item attributes values.
1790 --
1791 l_context_set varchar2(50);
1792 l_mesg varchar2(1000);
1793 --
1794 begin
1795 --
1796 -- RUN mode - normal process execution.
1797 --
1798 if (funcmode = 'RUN' ) then
1799 --
1800 l_context_set := wf_engine.GetItemAttrTEXT(
1801 itemtype => itemtype,
1802 itemkey => itemkey,
1803 aname => 'BEN_IA_CONTEXT_SET');
1804 --
1805 if l_context_set = 'Y'
1806 then
1807 resultout := 'COMPLETE:Y';
1808 else
1809 resultout := 'COMPLETE:N';
1810 end if;
1811 return;
1812 --
1813 end if;
1814 --
1815 if (funcmode = 'CANCEL' ) then
1816 --
1817 -- Return process to run
1818 --
1819 resultout := 'COMPLETE:N';
1820 return;
1821 --
1822 end if;
1823 --
1824 if (funcmode = 'TIMEOUT' ) then
1825 --
1826 -- Return process to run
1827 --
1828 resultout := 'COMPLETE:N';
1829 return;
1830 --
1831 end if;
1832 --
1833 exception
1834 --
1835 when others then
1836 --
1837 -- The line below records this function call in the error system
1838 -- in the case of an exception.
1839 -- The error message is written to item attribute to be notified to the
1840 -- user. ?????
1841 l_mesg := fnd_message.get;
1842 --
1843 -- Completed with Errors.
1844 --
1845 resultout := 'COMPLETE:E';
1846 wf_core.context('ben_on_line_lf_evt', 'p_context_def' ,
1847 itemtype, itemkey, to_char(actid), funcmode);
1848 raise;
1849 --
1850 end p_context_def;
1851 --
1852 procedure p_commit is
1853 begin
1854 --
1855 commit;
1856 --
1857 end p_commit;
1858 --
1859 --
1860 -- This procedure to evaluate the potential life events,
1861 -- and life events called from benauthe form as a CSR
1862 -- desktop activity.
1863 --
1864 procedure p_evt_lf_evts_from_benauthe(
1865 p_person_id in number
1866 ,p_effective_date in date
1867 ,p_business_group_id in number
1868 ,p_pgm_id in number default null
1869 ,p_pl_id in number default null
1870 ,p_mode in varchar2
1871 ,p_popl_enrt_typ_cycl_id in number
1872 ,p_lf_evt_ocrd_dt in date
1873 ,p_prog_count out nocopy number
1874 ,p_plan_count out nocopy number
1875 ,p_oipl_count out nocopy number
1876 ,p_person_count out nocopy number
1877 ,p_plan_nip_count out nocopy number
1878 ,p_oipl_nip_count out nocopy number
1879 ,p_ler_id out nocopy number
1880 ,p_errbuf out nocopy varchar2
1881 ,p_retcode out nocopy number) is
1882 --
1883 -- local variables.
1884 l_package varchar2(80) := 'ben_on_line_lf_evt.p_evt_lf_evts_from_benauthe';
1885 l_object_version_number number;
1886 l_effective_date varchar2(30);
1887 l_lf_evt_ocrd_dt varchar2(30);
1888 l_chunk_size number := 0;
1889 l_threads number := 0;
1890 l_max_errors_allowed number;
1891 l_rec benutils.g_active_life_event;
1892 l_assignment_id number;
1893 --
1894 l_bft_id number;
1895 --
1896 cursor c_ler is
1897 select ler_id
1898 from ben_person_actions
1899 where BENEFIT_ACTION_ID = benutils.g_benefit_action_id;
1900 --
1901 begin
1902 --
1903 hr_utility.set_location(l_package || ' Before Process',5);
1904 --
1905 if p_effective_date is not null then
1906 --l_effective_date := to_char(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
1907 l_effective_date := fnd_date.date_to_canonical(p_effective_date);
1908 end if;
1909 if p_lf_evt_ocrd_dt is not null then
1910 --l_lf_evt_ocrd_dt := to_char(p_lf_evt_ocrd_dt,'YYYY/MM/DD HH24:MI:SS');
1911 l_lf_evt_ocrd_dt := fnd_date.date_to_canonical(p_lf_evt_ocrd_dt);
1912 end if;
1913 --
1914 -- Initialise the globals.
1915 --
1916 g_benefit_action_id := null;
1917 ben_person_object.clear_down_cache;
1918 --
1919 l_bft_id := null;
1920 --
1921 -- Bug 3486966
1922 --
1923 if p_mode in ('L', 'C','U') then
1924 l_assignment_id := null;
1925 l_assignment_id :=
1926 benutils.get_assignment_id(p_person_id=> p_person_id
1927 ,p_business_group_id => p_business_group_id
1928 ,p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date));
1929 --
1930 if l_assignment_id is null
1931 then
1932 fnd_message.set_name('BEN','BEN_93906_NO_EMP_BEN_ASG');
1933 fnd_message.raise_error;
1934 end if ;
1935 end if;
1936 ben_manage_life_events.process
1937 (
1938 errbuf => p_errbuf,
1939 retcode => p_retcode,
1940 p_benefit_action_id => l_bft_id,
1941 p_effective_date => l_effective_date,
1942 p_mode => p_mode,
1943 p_derivable_factors => 'ASC' ,
1944 p_validate => 'N',
1945 p_person_id => p_person_id,
1946 p_person_type_id => null,
1947 p_pgm_id => p_pgm_id,
1948 p_business_group_id => p_business_group_id,
1949 p_pl_id => p_pl_id,
1950 -- PB : 5422 :
1951 -- p_popl_enrt_typ_cycl_id => p_popl_enrt_typ_cycl_id,
1952 p_no_programs => 'N' ,
1953 p_no_plans => 'N' ,
1954 p_comp_selection_rule_id => null,
1955 p_person_selection_rule_id => null,
1956 p_ler_id => null,
1957 p_organization_id => null,
1958 p_benfts_grp_id => null,
1959 p_location_id => null,
1960 p_pstl_zip_rng_id => null,
1961 p_rptg_grp_id => null,
1962 p_pl_typ_id => null,
1963 p_opt_id => null,
1964 p_eligy_prfl_id => null,
1965 p_vrbl_rt_prfl_id => null,
1966 p_legal_entity_id => null,
1967 p_payroll_id => null,
1968 p_commit_data => 'N',
1969 p_lmt_prpnip_by_org_flag => nvl(fnd_profile.value('BEN_LMT_PRPNIP_BY_ORG_FLAG'), 'N'),
1970 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt );
1971 --
1972 hr_utility.set_location(l_package ||' After process',10);
1973 --
1974 hr_utility.set_location('Before get_parameter',21);
1975 --
1976 benutils.get_parameter
1977 (p_business_group_id => p_business_group_id,
1978 p_batch_exe_cd => 'BENMNGLE',
1979 p_threads => l_threads,
1980 p_chunk_size => l_chunk_size,
1981 p_max_errors => l_max_errors_allowed);
1982 --
1983 -- Set up benefits environment
1984 --
1985 hr_utility.set_location('After get_parameter',22);
1986 --
1987 -- Clear benmngle level caches
1988 --
1989 ben_manage_life_events.clear_init_benmngle_caches
1990 (p_business_group_id => p_business_group_id
1991 ,p_effective_date => p_effective_date
1992 ,p_threads => l_threads
1993 ,p_chunk_size => l_chunk_size
1994 ,p_max_errors => l_max_errors_allowed
1995 ,p_benefit_action_id => benutils.g_benefit_action_id
1996 ,p_thread_id => 1
1997 );
1998 --
1999 hr_utility.set_location('After ben_manage_life_events.clear_init_benmngle_caches',24);
2000 hr_utility.set_location('Before Cursor',35);
2001 --
2002 open c_ler;
2003 fetch c_ler into p_ler_id;
2004 --
2005 if c_ler%notfound then
2006 --
2007 -- 3948506: Changed the error message.
2008 fnd_message.set_name('BEN','BEN_92540_NOONE_TO_PROCESS_CM');
2009 --fnd_message.set_name('BEN','BEN_91791_LER_NOT_IN_PER_ACTN');
2010 fnd_message.raise_error;
2011 --
2012 end if;
2013 --
2014 close c_ler;
2015 --
2016 hr_utility.set_location('After Cursor',40);
2017 hr_utility.set_location('Before evaluate life events',45);
2018 --
2019 g_ptnls_voidd_flag := FALSE;
2020 ben_manage_life_events.g_modified_mode := null;
2021 ben_lf_evt_clps_restore.g_bckdt_pil_restored_flag := 'N';
2022 ben_manage_life_events.evaluate_life_events
2023 (p_person_id => p_person_id,
2024 p_business_group_id => p_business_group_id,
2025 p_mode => p_mode,
2026 p_ler_id => p_ler_id,
2027 -- PB : 5422 :
2028 -- p_popl_enrt_typ_cycl_id => p_popl_enrt_typ_cycl_id,
2029 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2030 p_effective_date => p_effective_date);
2031 --
2032 hr_utility.set_location('After evaluate life events',50);
2033 --
2034 -- save the benefit action id to be used by process_comp_objects.
2035 --
2036 g_benefit_action_id := benutils.g_benefit_action_id;
2037 --
2038 -- To synchronize with process_life_events procedure
2039 -- ben_manage_life_events.person_header is moved here.
2040 --
2041 hr_utility.set_location('Before Person Header',51);
2042 --
2043 -- GSP : Call diferent procedures for different modes.
2044 if p_mode = 'G' then
2045 --
2046 benutils.get_active_life_event
2047 (p_person_id => p_person_id,
2048 p_business_group_id => p_business_group_id,
2049 p_effective_date => p_effective_date,
2050 p_lf_event_mode => 'G',
2051 p_rec => l_rec);
2052 --
2053 elsif p_mode = 'U' then
2054 --
2055 benutils.get_active_life_event
2056 (p_person_id => p_person_id,
2057 p_business_group_id => p_business_group_id,
2058 p_effective_date => p_effective_date,
2059 p_lf_event_mode => 'U',
2060 p_rec => l_rec);
2061 elsif p_mode = 'M' then
2062 --
2063 benutils.get_active_life_event
2064 (p_person_id => p_person_id,
2065 p_business_group_id => p_business_group_id,
2066 p_effective_date => p_effective_date,
2067 p_lf_event_mode => 'M',
2068 p_rec => l_rec);
2069 --
2070 else
2071 --
2072 benutils.get_active_life_event
2073 (p_person_id => p_person_id,
2074 p_business_group_id => p_business_group_id,
2075 p_effective_date => p_effective_date,
2076 p_rec => l_rec);
2077 --
2078 end if;
2079 --
2080 ben_manage_life_events.person_header
2081 (p_person_id => p_person_id,
2082 p_business_group_id => p_business_group_id,
2083 p_effective_date => nvl(l_rec.lf_evt_ocrd_dt,p_effective_date) );
2084 --
2085 hr_utility.set_location('After Person Header',52);
2086 --
2087 exception
2088 when ben_manage_life_events.g_record_error then
2089 -- there is an error from benmngle, show the error message that is
2090 -- setup in benmngle.
2091 fnd_message.raise_error;
2092 when ben_manage_life_events.g_life_event_after then
2093 -- there is an error in benmngle. In this case display a special error
2094 -- message as the message in benmgle is too generic.
2095 -- fnd_message.set_name('BEN', 'BEN_91940_LIFE_EVT_AFTER');
2096 -- fnd_message.raise_error;
2097 null;
2098 when others then
2099 raise;
2100 --
2101 end p_evt_lf_evts_from_benauthe;
2102 --
2103
2104 --
2105 -- This procedure to process life events called from benauthe form as a CSR
2106 -- desktop activity.
2107 --
2108 procedure p_proc_lf_evts_from_benauthe(
2109 p_person_id in number
2110 ,p_effective_date in date
2111 ,p_business_group_id in number
2112 ,p_mode in varchar2
2113 ,p_ler_id in number
2114 -- PB : 5422 :
2115 -- ,p_popl_enrt_typ_cycl_id in number
2116 ,p_lf_evt_ocrd_dt in date default null
2117 ,p_person_count out nocopy number
2118 ,p_benefit_action_id out nocopy number
2119 ,p_errbuf out nocopy varchar2
2120 ,p_retcode out nocopy number) is
2121 --
2122 -- local variables.
2123 --
2124 cursor c_person_thread is
2125 select ben.object_version_number, ben.person_action_id
2126 from ben_person_actions ben
2127 where ben.benefit_action_id = g_benefit_action_id
2128 and ben.person_id = p_person_id
2129 and ben.action_status_cd <> 'P';
2130 --
2131 l_object_version_number number;
2132 l_person_action_id number;
2133 --
2134 begin
2135 --
2136 hr_utility.set_location('Before process comp objects',55);
2137 --
2138 --
2139 open c_person_thread;
2140 fetch c_person_thread into l_object_version_number, l_person_action_id;
2141 close c_person_thread;
2142 --
2143 ben_manage_life_events.process_comp_objects
2144 (p_person_id => p_person_id,
2145 p_person_action_id => l_person_action_id,
2146 p_object_version_number => l_object_version_number,
2147 p_business_group_id => p_business_group_id,
2148 p_mode => p_mode,
2149 p_ler_id => p_ler_id,
2150 p_derivable_factors => 'ASC',
2151 p_person_count => p_person_count,
2152 -- PB : 5422 :
2153 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2154 -- p_popl_enrt_typ_cycl_id => p_popl_enrt_typ_cycl_id,
2155 p_effective_date => p_effective_date
2156 );
2157 p_benefit_action_id := benutils.g_benefit_action_id;
2158 --
2159 -- Write results to log
2160 --
2161 benutils.WRITE_TABLE_AND_FILE(P_TABLE => TRUE, P_FILE => FALSE);
2162 --
2163 ben_cobj_cache.clear_down_cache;
2164 hr_utility.set_location('After process comp objects',60);
2165 --
2166 exception
2167 when ben_manage_life_events.g_record_error then
2168 -- there is an error from benmngle, show the error message that is
2169 -- setup in benmngle.
2170 fnd_message.raise_error;
2171 when ben_manage_life_events.g_life_event_after then
2172 -- there is an error in benmngle. In this case display a special error
2173 -- message as the message in benmgle is too generic.
2174 fnd_message.set_name('BEN', 'BEN_91940_LIFE_EVT_AFTER');
2175 fnd_message.raise_error;
2176
2177 when others then
2178 raise;
2179 --
2180 end p_proc_lf_evts_from_benauthe;
2181 --
2182 -- procedure to evaluate the potential life events,
2183 -- and life events
2184 --
2185 procedure p_watif_manage_life_events(
2186 p_person_id in number
2187 ,p_effective_date in date
2188 ,p_business_group_id in number
2189 ,p_pgm_id in number default null
2190 ,p_pl_id in number default null
2191 ,p_mode in varchar2
2192 ,p_derivable_factors in varchar2
2193 ,p_prog_count out nocopy number
2194 ,p_plan_count out nocopy number
2195 ,p_oipl_count out nocopy number
2196 ,p_person_count out nocopy number
2197 ,p_plan_nip_count out nocopy number
2198 ,p_oipl_nip_count out nocopy number
2199 ,p_ler_id out nocopy number
2200 ,p_errbuf out nocopy varchar2
2201 ,p_retcode out nocopy number) is
2202 --
2203 -- local variables.
2204 l_object_version_number number;
2205 l_effective_date varchar2(30);
2206 l_chunk_size number := 0;
2207 l_threads number := 0;
2208 l_max_errors_allowed number;
2209 --
2210 l_rec benutils.g_active_life_event;
2211 --
2212 l_bft_id number;
2213 --
2214 cursor c_ler is
2215 select ler_id
2216 from ben_person_actions
2217 where BENEFIT_ACTION_ID = benutils.g_benefit_action_id;
2218 --
2219 begin
2220 --
2221 hr_utility.set_location('Before Process',5);
2222 --
2223 -- Initialise the globals.
2224 --
2225 g_benefit_action_id := null;
2226 --
2227 --l_effective_date := to_char(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
2228 l_effective_date := fnd_date.date_to_canonical(p_effective_date);
2229 --
2230 l_bft_id := null;
2231 --
2232 ben_manage_life_events.process
2233 (
2234 errbuf => p_errbuf,
2235 retcode => p_retcode,
2236 p_benefit_action_id => l_bft_id,
2237 p_effective_date => l_effective_date,
2238 p_mode => p_mode,
2239 p_derivable_factors => p_derivable_factors,
2240 p_validate => 'N',
2241 p_person_id => p_person_id,
2242 p_person_type_id => null,
2243 p_pgm_id => p_pgm_id,
2244 p_business_group_id => p_business_group_id,
2245 p_pl_id => p_pl_id,
2246 p_popl_enrt_typ_cycl_id => null,
2247 p_no_programs => 'N' ,
2248 p_no_plans => 'N' ,
2249 p_comp_selection_rule_id => null,
2250 p_person_selection_rule_id => null,
2251 p_ler_id => null,
2252 p_organization_id => null,
2253 p_benfts_grp_id => null,
2254 p_location_id => null,
2255 p_pstl_zip_rng_id => null,
2256 p_rptg_grp_id => null,
2257 p_pl_typ_id => null,
2258 p_opt_id => null,
2259 p_eligy_prfl_id => null,
2260 p_vrbl_rt_prfl_id => null,
2261 p_legal_entity_id => null,
2262 p_payroll_id => null,
2263 p_commit_data => 'N',
2264 p_lmt_prpnip_by_org_flag => nvl(fnd_profile.value('BEN_LMT_PRPNIP_BY_ORG_FLAG'), 'N'),
2265 p_lf_evt_ocrd_dt => l_effective_date );
2266 --
2267 hr_utility.set_location('After process',10);
2268 hr_utility.set_location('Before get_parameter',21);
2269 --
2270 benutils.get_parameter
2271 (p_business_group_id => p_business_group_id,
2272 p_batch_exe_cd => 'BENMNGLE',
2273 p_threads => l_threads,
2274 p_chunk_size => l_chunk_size,
2275 p_max_errors => l_max_errors_allowed);
2276 --
2277 -- Set up benefits environment
2278 --
2279 hr_utility.set_location('After get_parameter',22);
2280 --
2281 -- Clear benmngle level caches
2282 --
2283 ben_manage_life_events.clear_init_benmngle_caches
2284 (p_business_group_id => p_business_group_id
2285 ,p_effective_date => p_effective_date
2286 ,p_threads => l_threads
2287 ,p_chunk_size => l_chunk_size
2288 ,p_max_errors => l_max_errors_allowed
2289 ,p_benefit_action_id => benutils.g_benefit_action_id
2290 ,p_thread_id => 1
2291 );
2292 --
2293 hr_utility.set_location('After ben_manage_life_events.clear_init_benmngle_caches',24);
2294 hr_utility.set_location('Before Cursor',35);
2295 --
2296 open c_ler;
2297 fetch c_ler into p_ler_id;
2298 --
2299 if c_ler%notfound then
2300 --
2301 fnd_message.set_name('BEN','BEN_91791_LER_NOT_IN_PER_ACTN');
2302 fnd_message.raise_error;
2303 --
2304 end if;
2305 --
2306 close c_ler;
2307 --
2308 hr_utility.set_location('After Cursor',40);
2309 hr_utility.set_location('Before evaluate life events',45);
2310 --
2311 ben_manage_life_events.evaluate_life_events
2312 (p_person_id => p_person_id,
2313 p_business_group_id => p_business_group_id,
2314 p_mode => p_mode,
2315 p_ler_id => p_ler_id,
2316 -- p_popl_enrt_typ_cycl_id => null,
2317 p_lf_evt_ocrd_dt => p_effective_date,
2318 p_effective_date => p_effective_date);
2319 --
2320 hr_utility.set_location('After evaluate life events',50);
2321 hr_utility.set_location('Before Person Header',25);
2322 --
2323 -- GSP : Call diferent procedures for different modes.
2324 if p_mode = 'G' then
2325 --
2326 benutils.get_active_life_event
2327 (p_person_id => p_person_id,
2328 p_business_group_id => p_business_group_id,
2329 p_effective_date => p_effective_date,
2330 p_lf_event_mode => 'G',
2331 p_rec => l_rec);
2332 --
2333 elsif p_mode = 'U' then
2334 --
2335 benutils.get_active_life_event
2336 (p_person_id => p_person_id,
2337 p_business_group_id => p_business_group_id,
2338 p_effective_date => p_effective_date,
2339 p_lf_event_mode => 'U',
2340 p_rec => l_rec);
2341 elsif p_mode = 'M' then
2342 --
2343 benutils.get_active_life_event
2344 (p_person_id => p_person_id,
2345 p_business_group_id => p_business_group_id,
2346 p_effective_date => p_effective_date,
2347 p_lf_event_mode => 'M',
2348 p_rec => l_rec);
2349 --
2350 else
2351 --
2352 benutils.get_active_life_event
2353 (p_person_id => p_person_id,
2354 p_business_group_id => p_business_group_id,
2355 p_effective_date => p_effective_date,
2356 p_rec => l_rec);
2357 --
2358 end if;
2359 --
2360 --
2361 ben_manage_life_events.person_header
2362 (p_person_id => p_person_id,
2363 p_business_group_id => p_business_group_id,
2364 p_effective_date => nvl(l_rec.lf_evt_ocrd_dt,p_effective_date));
2365 --
2366 hr_utility.set_location('After Person Header',30);
2367 hr_utility.set_location('Before process comp objects',55);
2368 --
2369 ben_manage_life_events.process_comp_objects
2370 (p_person_id => p_person_id,
2371 p_person_action_id => benutils.g_benefit_action_id, -- null,
2372 p_object_version_number => l_object_version_number,
2373 p_business_group_id => p_business_group_id,
2374 p_mode => p_mode,
2375 p_ler_id => p_ler_id,
2376 p_derivable_factors => p_derivable_factors,
2377 p_person_count => p_person_count,
2378 -- p_popl_enrt_typ_cycl_id => null,
2379 p_effective_date => p_effective_date
2380 );
2381 --
2382 hr_utility.set_location('After process comp objects',60);
2383 --
2384 exception
2385 when ben_manage_life_events.g_record_error then
2386 -- there is an error from benmngle, show the error message that is
2387 -- setup in benmngle.
2388 fnd_message.raise_error;
2389 when ben_manage_life_events.g_life_event_after then
2390 -- there is an error in benmngle. In this case display a special error
2391 -- message as the message in benmgle is too generic.
2392 -- BUG 2879140 -- change message as unrestricted enrollment does not make sense in watif
2393 fnd_message.set_name('BEN', 'BEN_93376_LIFE_EVT_AFTER');
2394 -- fnd_message.set_name('BEN', 'BEN_91940_LIFE_EVT_AFTER');
2395 fnd_message.raise_error;
2396
2397 when others then
2398 raise;
2399 --
2400 end p_watif_manage_life_events;
2401 --
2402 -- Procedure when called from a form message hook it will evaluates the
2403 -- fast formula which returns Y or N, if Y then form displays the associated
2404 -- message else nothing happens.
2405 --
2406 procedure p_oll_pop_up_message
2407 (p_person_id in number
2408 ,p_business_group_id in number
2409 ,p_function_name in varchar2
2410 ,p_block_name in varchar2
2411 ,p_field_name in varchar2
2412 ,p_event_name in varchar2
2413 ,p_effective_date in date
2414 ,p_payroll_id in number default null
2415 ,p_payroll_action_id in number default null
2416 ,p_assignment_id in number default null
2417 ,p_assignment_action_id in number default null
2418 ,p_org_pay_method_id in number default null
2419 ,p_per_pay_method_id in number default null
2420 ,p_organization_id in number default null
2421 ,p_tax_unit_id in number default null
2422 ,p_jurisdiction_code in number default null
2423 ,p_balance_date in number default null
2424 ,p_element_entry_id in number default null
2425 ,p_element_type_id in number default null
2426 ,p_original_entry_id in number default null
2427 ,p_tax_group in number default null
2428 ,p_pgm_id in number default null
2429 ,p_pl_id in number default null
2430 ,p_pl_typ_id in number default null
2431 ,p_opt_id in number default null
2432 ,p_ler_id in number default null
2433 ,p_communication_type_id in number default null
2434 ,p_action_type_id in number default null
2435 ,p_message_count out nocopy number
2436 ,p_message1 out nocopy varchar2
2437 ,p_message_type1 out nocopy varchar2
2438 ,p_message2 out nocopy varchar2
2439 ,p_message_type2 out nocopy varchar2
2440 ,p_message3 out nocopy varchar2
2441 ,p_message_type3 out nocopy varchar2
2442 ,p_message4 out nocopy varchar2
2443 ,p_message_type4 out nocopy varchar2
2444 ,p_message5 out nocopy varchar2
2445 ,p_message_type5 out nocopy varchar2
2446 ,p_message6 out nocopy varchar2
2447 ,p_message_type6 out nocopy varchar2
2448 ,p_message7 out nocopy varchar2
2449 ,p_message_type7 out nocopy varchar2
2450 ,p_message8 out nocopy varchar2
2451 ,p_message_type8 out nocopy varchar2
2452 ,p_message9 out nocopy varchar2
2453 ,p_message_type9 out nocopy varchar2
2454 ,p_message10 out nocopy varchar2
2455 ,p_message_type10 out nocopy varchar2
2456 ) is
2457 --
2458 cursor c_rule is
2459 select *
2460 from ben_pop_up_messages pop
2461 where pop.function_name = p_function_name
2462 and pop.event_name = p_event_name
2463 and nvl(pop.block_name, '***') = nvl(p_block_name, '***')
2464 and nvl(pop.field_name, '***') = nvl(p_field_name, '***')
2465 and pop.business_group_id+0 = p_business_group_id
2466 and p_effective_date
2467 between nvl(pop.start_date, p_effective_date)
2468 and nvl(pop.end_date, p_effective_date);
2469 --
2470 l_rule_rec c_rule%rowtype;
2471 --
2472 cursor per_asn is
2473 select assignment_id,organization_id
2474 from per_assignments_f paf
2475 where paf.person_id = p_person_id
2476 and paf.assignment_type <> 'C'
2477 and paf.primary_flag = 'Y'
2478 and paf.business_group_id + 0= p_business_group_id
2479 and p_effective_date between
2480 paf.effective_start_date and paf.effective_end_date;
2481 l_asn per_asn%rowtype;
2482
2483 Cursor c_state is
2484 select region_2
2485 from hr_locations_all loc,per_all_assignments_f asg
2486 where loc.location_id = asg.location_id
2487 and asg.person_id = p_person_id
2488 and asg.assignment_type <> 'C'
2489 and asg.primary_flag = 'Y'
2490 and p_effective_date between
2491 asg.effective_start_date and asg.effective_end_date
2492 and asg.business_group_id+0=p_business_group_id;
2493
2494 l_state c_state%rowtype;
2495 l_proc varchar2(80) := '.p_call_oll_ff';
2496 l_outputs ff_exec.outputs_t;
2497 l_return varchar2(30);
2498 l_formula_id number;
2499 l_jurisdiction_code varchar2(30);
2500 l_mess_type varchar2(10); -- 9999 delete it
2501 l_message_count number := 0;
2502 --
2503 begin
2504 --
2505 hr_utility.set_location ('Entering '||l_proc,10);
2506 p_message_type1 := null;
2507 p_message1 := null;
2508 p_message_type2 := null;
2509 p_message2 := null;
2510 p_message_type3 := null;
2511 p_message3 := null;
2512 p_message_type4 := null;
2513 p_message4 := null;
2514 p_message_type5 := null;
2515 p_message5 := null;
2516 p_message_type6 := null;
2517 p_message6 := null;
2518 p_message_type7 := null;
2519 p_message7 := null;
2520 p_message_type8 := null;
2521 p_message8 := null;
2522 p_message_type9 := null;
2523 p_message9 := null;
2524 p_message_type10 := null;
2525 p_message10 := null;
2526 p_message_count := null;
2527 --
2528 if p_person_id is not null then
2529 --
2530 -- Get assignment ID,organization_id form per_assignments_f table.
2531 --
2532 open per_asn;
2533 fetch per_asn into l_asn;
2534 if per_asn%notfound then
2535 --
2536 -- Defensive coding
2537 --
2538 close per_asn;
2539 raise no_data_found;
2540 end if;
2541 close per_asn;
2542 --
2543 /* -- 4031733 - Cursor c_state populates l_state variable which is no longer
2544 -- used in the package. Cursor can be commented
2545 open c_state;
2546 fetch c_state into l_state;
2547 close c_state;
2548 */
2549 --if l_state.region_2 is not null then
2550
2551 -- l_jurisdiction_code :=
2552 -- pay_mag_utils.lookup_jurisdiction_code
2553 -- (p_state => l_state.region_2);
2554
2555 --end if;
2556
2557 end if;
2558 --
2559 -- Get the message record
2560 --
2561 for l_rule_rec in c_rule loop
2562 --
2563 l_message_count := l_message_count + 1;
2564 if l_message_count > 10 then
2565 exit;
2566 end if;
2567 --
2568 if l_rule_rec.no_formula_flag = 'Y' then
2569 --
2570 -- Just return the message and message type
2571 -- As the case is to display a message
2572 -- without executing the fast formula
2573 -- Example : on WHEN-NEW-FORM-INSTANCE trigger display a message.
2574 --
2575 if l_message_count = 1 then
2576 p_message_type1 := l_rule_rec.message_type;
2577 p_message1 := l_rule_rec.message;
2578 elsif l_message_count = 2 then
2579 p_message_type2 := l_rule_rec.message_type;
2580 p_message2 := l_rule_rec.message;
2581 elsif l_message_count = 3 then
2582 p_message_type3 := l_rule_rec.message_type;
2583 p_message3 := l_rule_rec.message;
2584 elsif l_message_count = 4 then
2585 p_message_type4 := l_rule_rec.message_type;
2586 p_message4 := l_rule_rec.message;
2587 elsif l_message_count = 5 then
2588 p_message_type5 := l_rule_rec.message_type;
2589 p_message5 := l_rule_rec.message;
2590 elsif l_message_count = 6 then
2591 p_message_type6 := l_rule_rec.message_type;
2592 p_message6 := l_rule_rec.message;
2593 elsif l_message_count = 7 then
2594 p_message_type7 := l_rule_rec.message_type;
2595 p_message7 := l_rule_rec.message;
2596 elsif l_message_count = 8 then
2597 p_message_type8 := l_rule_rec.message_type;
2598 p_message8 := l_rule_rec.message;
2599 elsif l_message_count = 9 then
2600 p_message_type9 := l_rule_rec.message_type;
2601 p_message9 := l_rule_rec.message;
2602 elsif l_message_count = 10 then
2603 p_message_type10 := l_rule_rec.message_type;
2604 p_message10 := l_rule_rec.message;
2605 end if;
2606
2607 -- p_message_type := l_rule_rec.message_type;
2608 -- p_message := l_rule_rec.message;
2609 -- copy(l_rule_rec.message_type, 'p_message_type' || to_char(l_message_count));
2610 -- copy(l_rule_rec.message, 'p_message' || to_char(l_message_count));
2611 --
2612 else
2613 --
2614 -- Call formula initialise routine
2615 --
2616 l_outputs := benutils.formula
2617 (p_formula_id => l_rule_rec.formula_id
2618 ,p_effective_date => p_effective_date
2619 ,p_assignment_id => l_asn.assignment_id
2620 ,p_organization_id => l_asn.organization_id
2621 ,p_business_group_id =>p_business_group_id
2622 ,p_pgm_id =>p_pgm_id
2623 ,p_pl_id =>p_pl_id
2624 ,p_pl_typ_id =>p_pl_typ_id
2625 ,p_opt_id =>p_opt_id
2626 ,p_ler_id =>p_ler_id
2627 ,p_jurisdiction_code =>l_jurisdiction_code);
2628 --
2629 l_return := l_outputs(l_outputs.first).value;
2630 --
2631 if upper(l_return) not in ('Y', 'N') then
2632 --
2633 -- Defensive coding
2634 -- Just return 'N' means no popup in case of non Y or N.
2635 --
2636 l_return := 'N';
2637 --
2638 end if;
2639 if upper(l_return) = 'Y' then
2640 --
2641 -- Now display the message based on the message type
2642 --
2643 -- copy(l_rule_rec.message_type, 'p_message_type' || to_char(l_message_count));
2644 -- copy(l_rule_rec.message, 'p_message' || to_char(l_message_count));
2645 if l_message_count = 1 then
2646 p_message_type1 := l_rule_rec.message_type;
2647 p_message1 := l_rule_rec.message;
2648 elsif l_message_count = 2 then
2649 p_message_type2 := l_rule_rec.message_type;
2650 p_message2 := l_rule_rec.message;
2651 elsif l_message_count = 3 then
2652 p_message_type3 := l_rule_rec.message_type;
2653 p_message3 := l_rule_rec.message;
2654 elsif l_message_count = 4 then
2655 p_message_type4 := l_rule_rec.message_type;
2656 p_message4 := l_rule_rec.message;
2657 elsif l_message_count = 5 then
2658 p_message_type5 := l_rule_rec.message_type;
2659 p_message5 := l_rule_rec.message;
2660 elsif l_message_count = 6 then
2661 p_message_type6 := l_rule_rec.message_type;
2662 p_message6 := l_rule_rec.message;
2663 elsif l_message_count = 7 then
2664 p_message_type7 := l_rule_rec.message_type;
2665 p_message7 := l_rule_rec.message;
2666 elsif l_message_count = 8 then
2667 p_message_type8 := l_rule_rec.message_type;
2668 p_message8 := l_rule_rec.message;
2669 elsif l_message_count = 9 then
2670 p_message_type9 := l_rule_rec.message_type;
2671 p_message9 := l_rule_rec.message;
2672 elsif l_message_count = 10 then
2673 p_message_type10 := l_rule_rec.message_type;
2674 p_message10 := l_rule_rec.message;
2675 end if;
2676 --
2677 end if;
2678 --
2679 end if;
2680 --
2681 end loop;
2682 p_message_count := l_message_count;
2683 hr_utility.set_location ('Leaving '||l_proc,10);
2684 --
2685 return ;
2686 Exception
2687 when others then
2688 --
2689 -- Just return 'N' means no popup. Really don't stop the further processing.
2690 --
2691 l_return := 'N';
2692 return;
2693 --
2694 end p_oll_pop_up_message;
2695 --
2696 function f_ret_ptnls_voidd_flag return boolean is
2697 begin
2698 --
2699 return g_ptnls_voidd_flag;
2700 --
2701 end;
2702 --
2703 --
2704 -- Bug : 4504/1217193 This procedure is called from the form
2705 -- BENWFREP to see whether any electable choices are created by this
2706 -- run of benmngle. If not created then return a message.
2707 --
2708 function f_ret_elec_chc_created return boolean is
2709 begin
2710 --
2711 return ben_enrolment_requirements.g_electable_choice_created;
2712 --
2713 end;
2714 --
2715 -- Parse_encoded is not working properly on form side.
2716 -- So this procedure is called from form to retrive the
2717 -- message name. Based on the message BENAUTHE decides
2718 -- what mwssage to diaply after evaluating the potential
2719 -- life events.
2720 --
2721 procedure get_ser_message(p_encoded_message out nocopy varchar2,
2722 p_app_short_name out nocopy varchar2,
2723 p_message_name out nocopy varchar2) is
2724 --
2725 l_encoded_message varchar2(2000);
2726 l_app_short_name varchar2(2000);
2727 l_message_name varchar2(2000);
2728 --
2729 begin
2730 --
2731 p_encoded_message := null;
2732 p_app_short_name := null;
2733 p_message_name := null;
2734 p_encoded_message := fnd_message.get_encoded;
2735 fnd_message.parse_encoded(encoded_message => p_encoded_message,
2736 app_short_name => p_app_short_name,
2737 message_name => p_message_name);
2738 --
2739 if p_message_name is not null then
2740 fnd_message.set_encoded(encoded_message => p_encoded_message);
2741 end if;
2742 --
2743 end;
2744 --Start Bug 14137075
2745 --Below procedure is created to pass the token values to
2746 --Forms for the message number 94750 only.
2747 procedure get_tokens_94750(p_current_ler out nocopy varchar2,
2748 p_new_ler out nocopy varchar2,
2749 p_lf_evt_ocrd_dt out nocopy date,
2750 p_effective_date out nocopy date) is
2751
2752 begin
2753 --
2754 p_current_ler := fnd_message.get_token('CURRENT_LE');
2755 p_new_ler := fnd_message.get_token('NEW_LE');
2756 p_lf_evt_ocrd_dt := fnd_message.get_token('LF_EVT_OCRD_DT');
2757 p_effective_date := fnd_message.get_token('EFFECTIVE_DATE');
2758 --
2759 end;
2760 --End Bug 14137075
2761 --
2762 --
2763 -- self-service wrapper to run benmngle in
2764 -- unrestricted mode.
2765 --
2766 procedure p_manage_life_events_w(
2767 p_person_id in number
2768 ,p_effective_date in date
2769 ,p_lf_evt_ocrd_dt in date default null
2770 ,p_business_group_id in number
2771 ,p_mode in varchar2
2772 ,p_ss_process_unrestricted in varchar2 default 'Y'
2773 ,p_return_status out nocopy varchar2) is
2774 --
2775 l_proc varchar2(72) := g_package||'p_manage_life_events_w';
2776 l_errbuf varchar2(2000);
2777 l_retcode number;
2778 l_prog_count number;
2779 l_plan_count number;
2780 l_oipl_count number;
2781 l_person_count number;
2782 l_plan_nip_count number;
2783 l_oipl_nip_count number;
2784 l_ler_id number;
2785 --
2786 l_encoded_message varchar2(2000);
2787 l_app_short_name varchar2(2000);
2788 l_message_name varchar2(2000);
2789 --
2790 --
2791 cursor c_open(p_lf_evt_ocrd_dt date,
2792 p_person_id number ) is
2793 select ler.typ_cd
2794 from ben_ptnl_ler_for_per pel,
2795 ben_ler_f ler
2796 where p_effective_date between ler.effective_start_date
2797 and ler.effective_end_date
2798 and ler.typ_cd in ( 'SCHEDDO','SCHEDDA')
2799 and pel.ptnl_ler_for_per_stat_cd = 'UNPROCD'
2800 and pel.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
2801 and p_person_id = pel.person_id
2802 and pel.ler_id =ler.ler_id ;
2803 --
2804 l_open varchar2(30) := 'N' ;
2805 l_effective_date date := NVL(p_lf_evt_ocrd_dt,p_effective_date);
2806 l_mode varchar2(30) := p_mode ;
2807 --
2808 l_trace_param varchar2(30);
2809 l_trace_on boolean;
2810 --AMN Bug 14680386
2811 l_current_ler VARCHAR2(240);
2812 l_new_ler VARCHAR2(240);
2813 l_lf_evt_ocrd_date date;
2814 l_effective_dt date;
2815 --AMN Bug 14680386
2816 --
2817 begin
2818 p_return_status := 'S';
2819 l_trace_param := null;
2820 l_trace_on := false;
2821 --
2822 l_trace_param := fnd_profile.value('BEN_SS_TRACE_VALUE');
2823 --
2824
2825 if l_trace_param = 'BENOLLET' then
2826 l_trace_on := true;
2827 else
2828 l_trace_on := false;
2829 end if;
2830 --
2831 if l_trace_on then
2832 hr_utility.trace_on(null,'BENOLLET');
2833 end if;
2834 --
2835 hr_utility.set_location('l_trace_param : '|| l_trace_param, 5);
2836 hr_utility.set_location ('Entering '||l_proc,10);
2837 --
2838 fnd_msg_pub.initialize;
2839
2840 /* One-Off bug 3697615
2841 The behavior of SS form function parameter 'ssProcessUnrestricted' will be as follows:
2842 1. If set to Y, it will always run Unrestricted Benmngle
2843 2. If set to P, [P for performance] it will not run unrestricted Benmngle at
2844 most of the cases (except when the person's data which affects eligibility has
2845 got changed since the last time Unrestricted was run). This is determined by calling
2846 benutils.run_osb_benmngle_flag
2847 3. If set to N, it will not run Unrestricted at all and it will also prevent the user
2848 from making any changes to his elections for programs enrolled into through Unrestricted LE.
2849 */
2850
2851 if (p_mode <> 'U'
2852 or
2853 (p_mode = 'U' and p_ss_process_unrestricted <> 'N'
2854 and (p_ss_process_unrestricted = 'Y'
2855 or (p_ss_process_unrestricted = 'P' and
2856 (benutils.run_osb_benmngle_flag(p_person_id,p_business_group_id,
2857 p_effective_date)))))) then
2858 --
2859 hr_utility.set_location ('Calling p_manage_life_events in '||p_mode||' p_mode ', 20);
2860 --
2861 if p_lf_evt_ocrd_dt IS NOT NULL THEN
2862 open c_open(p_lf_evt_ocrd_dt,p_person_id);
2863 fetch c_open into l_open ;
2864 if c_open%found then
2865 l_effective_date := p_effective_date ;
2866 l_mode := 'C';
2867 end if;
2868 close c_open ;
2869 end if;
2870 --
2871 --5194398: Check if POPL is locked
2872 if p_mode = 'U' then
2873 declare
2874 cursor c_popl_lock(p_per_in_ler_id number) is
2875 select null
2876 from ben_pil_elctbl_chc_popl pel
2877 where per_in_ler_id = p_per_in_ler_id
2878 for update nowait;
2879 l_test number;
2880
2881 record_locked EXCEPTION;
2882 PRAGMA EXCEPTION_INIT (record_locked, -54);
2883 l_rec benutils.g_active_life_event;
2884
2885 begin
2886 savepoint ben_popl_lock_savepoint;
2887
2888 benutils.get_active_life_event
2889 (p_person_id => p_person_id,
2890 p_business_group_id => p_business_group_id,
2891 p_effective_date => l_effective_date,
2892 p_lf_event_mode => 'U',
2893 p_rec => l_rec);
2894
2895 open c_popl_lock(l_rec.per_in_ler_id);
2896 fetch c_popl_lock into l_test;
2897 close c_popl_lock;
2898
2899 rollback to ben_popl_lock_savepoint; --in order to release the lock(if obtained) on POPL
2900
2901 exception
2902 when record_locked then
2903 hr_utility.set_location ('POPL locked. Returning without processing unrestricted.',25);
2904 return; --POPL is locked, so return to calling procedure without processing unrestricted
2905 end;
2906 end if;
2907 --
2908 ben_on_line_lf_evt.p_manage_life_events
2909 (p_person_id => p_person_id
2910 ,p_effective_date => l_effective_date -- p_effective_date
2911 ,p_business_group_id => p_business_group_id
2912 ,p_pgm_id => null
2913 ,p_pl_id => null
2914 ,p_mode => l_mode -- p_mode
2915 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
2916 ,p_prog_count => l_prog_count
2917 ,p_plan_count => l_plan_count
2918 ,p_oipl_count => l_oipl_count
2919 ,p_person_count => l_person_count
2920 ,p_plan_nip_count => l_plan_nip_count
2921 ,p_oipl_nip_count => l_oipl_nip_count
2922 ,p_ler_id => l_ler_id
2923 ,p_errbuf => l_errbuf
2924 ,p_retcode => l_retcode
2925 );
2926 --
2927 end if;
2928
2929 -- If no execption raised, clear the message cache to avoid
2930 -- getting "Calling ben_generate_communications".
2931 --
2932 fnd_msg_pub.initialize;
2933 --
2934 commit;
2935 --
2936 hr_utility.set_location ('Leaving '||l_proc,30);
2937 --
2938 if l_trace_on then
2939 hr_utility.trace_off;
2940 l_trace_param := null;
2941 l_trace_on := false;
2942 end if;
2943 --
2944 exception
2945 when app_exception.application_exception then
2946 get_ser_message(p_encoded_message => l_encoded_message,
2947 p_app_short_name => l_app_short_name,
2948 p_message_name => l_message_name);
2949 --
2950 -- Kill the error if it is
2951 -- "No comp objects selected".
2952 -- Bug 1972460.
2953 --
2954 if (l_message_name like '%BEN_91769_NOONE_TO_PROCESS%') then
2955 fnd_message.set_name('BEN', 'BEN_92540_NOONE_TO_PROCESS_CM');
2956 -- l_encoded_message := fnd_message.get;
2957 -- copy(l_encoded_message, 'messages.message');
2958 end if;
2959 -- Bug 4254792 Donot add manual LE errors to stack
2960 if (l_message_name = 'BEN_92396_LIFE_EVENT_MANUAL'
2961 or l_message_name = 'BEN_94209_MAN_LER_EXISTIS') then
2962 p_return_status := 'M';
2963 elsif l_message_name = 'BEN_92144_NO_LIFE_EVENTS' then --5211969
2964 commit;
2965 elsif (l_message_name = 'BEN_91664_BENMNGLE_NO_OBJECTS') then
2966 l_encoded_message := fnd_message.get_encoded;
2967 rollback;
2968 --Start AMN Bug 14680386
2969 elsif l_message_name = 'BEN_94750_NO_LIFE_EVENTS' then --AMN 136
2970 get_tokens_94750(p_current_ler => l_current_ler,
2971 p_new_ler => l_new_ler,
2972 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date,
2973 p_effective_date => l_effective_dt);
2974 fnd_message.set_name('BEN','BEN_94751_NO_LIFE_EVENTS');
2975 fnd_message.set_token('CURRENT_LE',l_current_ler);
2976 fnd_message.set_token('NEW_LE',l_new_ler);
2977 fnd_message.set_token('LF_EVT_OCRD_DT',l_lf_evt_ocrd_date);
2978 fnd_message.set_token('EFFECTIVE_DATE',l_effective_dt);
2979 p_return_status := 'BEN_94751';
2980 fnd_msg_pub.add_detail(p_message_type => FND_MSG_PUB.g_information_msg);
2981 rollback;
2982 --End Bug 14680386
2983 else
2984 p_return_status := 'E';
2985 fnd_msg_pub.add;
2986 rollback;
2987 end if;
2988 --
2989 if l_trace_on then
2990 hr_utility.trace_off;
2991 l_trace_param := null;
2992 l_trace_on := false;
2993 end if;
2994 --rollback;
2995 --
2996 when others then
2997 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
2998 fnd_message.set_token('2',substr(sqlerrm,1,200));
2999 fnd_msg_pub.add;
3000 p_return_status := 'E';
3001 if l_trace_on then
3002 hr_utility.trace_off;
3003 l_trace_param := null;
3004 l_trace_on := false;
3005 end if;
3006 end p_manage_life_events_w;
3007 --
3008 --
3009 -- self-service wrapper to run benmngle through
3010 -- iRecruitment
3011 --
3012 procedure p_manage_irec_life_events_w(
3013 p_person_id in number
3014 ,p_assignment_id in number
3015 ,p_effective_date in date
3016 ,p_business_group_id in number
3017 ,p_offer_assignment_rec in per_all_assignments_f%rowtype) --bug 4621751 irec2
3018
3019 is
3020 --
3021 l_proc varchar2(72) := g_package || 'p_manage_irec_life_events_w';
3022 l_errbuf varchar2(2000);
3023 l_retcode number;
3024 l_effective_date varchar2(30);
3025 --
3026 l_encoded_message varchar2(2000);
3027 l_app_short_name varchar2(2000);
3028 l_message_name varchar2(2000);
3029 --
3030
3031 begin
3032 --
3033 -- Create a save point.
3034 savepoint irec_life_events_savepoint; --irec2
3035
3036 --hr_utility.trace_on(NULL, 'IREC2'); -- ACE
3037 --
3038 hr_utility.set_location ('Entering '||l_proc,10);
3039 --
3040 fnd_msg_pub.initialize;
3041 --
3042 hr_utility.set_location ('Calling p_manage_irec_life_events_w in iRecruitment (I) mode', 20);
3043 --
3044 -- l_effective_date := to_char(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
3045 l_effective_date := fnd_date.date_to_canonical(p_effective_date);
3046 --
3047 ben_comp_object_list1.refresh_eff_date_caches;
3048 --
3049 ben_manage_life_events.irec_process
3050 (errbuf => l_errbuf,
3051 retcode => l_retcode,
3052 p_effective_date => l_effective_date,
3053 p_mode => 'I', -- Mode for iRecruitment = 'I'
3054 p_person_id => p_person_id,
3055 p_business_group_id => p_business_group_id,
3056 p_assignment_id => p_assignment_id,
3057 p_offer_assignment_rec => p_offer_assignment_rec); --bug 4621751 irec2
3058 --
3059
3060 -- If no execption raised, clear the message cache to avoid
3061 -- getting "Calling ben_generate_communications".
3062 --
3063 fnd_msg_pub.initialize;
3064 --
3065 commit;
3066 --
3067 hr_utility.set_location ('Leaving '||l_proc,30);
3068 -- hr_utility.trace_off;
3069 --
3070 exception
3071 when app_exception.application_exception then
3072 get_ser_message(p_encoded_message => l_encoded_message,
3073 p_app_short_name => l_app_short_name,
3074 p_message_name => l_message_name);
3075 --
3076 -- Kill the error if it is
3077 -- "No comp objects selected".
3078 -- Bug 1972460.
3079 --
3080 if (l_message_name like '%BEN_91769_NOONE_TO_PROCESS%') then
3081 fnd_message.set_name('BEN', 'BEN_92540_NOONE_TO_PROCESS_CM');
3082 -- l_encoded_message := fnd_message.get;
3083 -- copy(l_encoded_message, 'messages.message');
3084 end if;
3085 if (l_message_name = 'BEN_91664_BENMNGLE_NO_OBJECTS') then
3086 l_encoded_message := fnd_message.get_encoded;
3087 else
3088 fnd_msg_pub.add;
3089 end if;
3090
3091 rollback to irec_life_events_savepoint;
3092 --
3093 when others then
3094 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
3095 fnd_message.set_token('2',substr(sqlerrm,1,200));
3096 fnd_msg_pub.add;
3097 -- hr_utility.set_location(' EXCEPTION CAUGHT',9909);
3098 --hr_utility.trace_off;
3099 rollback to irec_life_events_savepoint;
3100 end p_manage_irec_life_events_w;
3101 --
3102 end ben_on_line_lf_evt;