[Home] [Help]
PACKAGE BODY: APPS.BEN_ENROLLMENT_PROCESS
Source
1 package body ben_enrollment_process as
2 /* $Header: benenrol.pkb 120.20 2012/01/19 06:27:27 rpahune ship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1998 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name
12 Enrollment Process
13 Purpose
14 This is a wrapper procedure for Benefits enrollments,
15 dependents and beneficiaries designation for Enrollments conversion,
16 ongoing mass updates and IVR Process.
17 History
18 Date Who Version What?
19 ---- --- ------- -----
20 01 Nov 05 ikasire 115.0 Created
21 31 Jan 06 ikasired 115.1 Added more validations
22 07 Feb 06 ikasired 115.2 More validations
23 21 Feb 06 ikasired 115.3 GSCC Errors
24 01 Mar 06 ikasired 115.4 Fix for c_epe cursors to
25 for plans in multiple programs
26 16 Mar 06 ikasired 115.6 Bug 5099945 fixes
27 17 Mar 06 ikasired 115.7 Bug 5100373 fix
28 21 Mar 06 ikasired 115.8 Bug 5108304 Added validation for
29 benefit amounts
30 Bug 5099864 Dependents validation fix
31 22 Mar 06 ikasired 115.9 Bug 5111326 fixes - more validations
32 23 Mar 06 ikasired 115.10 Bug 5097635 fix for beneficiary action
33 items
34 13 Apr 06 nkkrishn 115.11 Summary row elimination changes
35 02 May 06 nkkrishn 115.12 Fixed Beneficiary upload
36 10 May 06 nkkrishn 115.13 Beneficiary upload, suspend
37 enrollment problem fixed
38 10 May 06 nkkrishn 115.14 passing benefit amount param
39 to create_plan_beneficiary
40 12 Jun 06 ikasired 115.15 If condition got removed for the
41 call to ben_env_object.init
42 Bug 5259118
43 12 Jun 06 ikasired 115.16 Bug 5305426 to populate the proper
44 designation coverage start date
45 22 Nov 06 nkkrishn 115.18 Bug 5675220 - end dependant
46 designation not showing up
47 in PUI
48 07 Dec 06 nkkrishn 115.19 Bug 5675220 - end dependant
49 designation not showing up
50 in PUI.Using ben_prtt_enrt_result_api.
51 calc_dpnt_cvg_dt to calculate both
52 coverage start and end date for
53 dependants
54 29 Dec 06 nkkrishn 115.20 ENH - End Enrollment. (5738940)
55 16 Nov 10 velvanop 115.21 Bug 10209116: Coverage start and end dates should be taken
56 from the worksheet used for uploading the Dependent
57 elections if the date values are not NULL
58 */
59 --
60 --Globals
61 --
62 g_debug boolean := hr_utility.debug_enabled;
63 --
64 cursor c_pil(p_ler_id number,
65 p_life_event_date date,
66 p_person_id number) is
67 select pil.per_in_ler_id
68 from ben_per_in_ler pil
69 where pil.ler_id = p_ler_id
70 and pil.lf_evt_ocrd_dt = p_life_event_date
71 and pil.person_id = p_person_id
72 and pil.per_in_ler_stat_cd = 'STRTD' ;
73 --
74 cursor c_epe_oipl(p_per_in_ler_id number,
75 p_pl_id number,
76 p_opt_id number,
77 p_life_event_date date,
78 p_pgm_id number ) is
79 select epe.elig_per_elctbl_chc_id,
80 epe.enrt_cvg_strt_dt,
81 epe.enrt_cvg_strt_dt_cd,
82 epe.prtt_enrt_rslt_id,
83 epe.oipl_id,
84 epe.dpnt_cvg_strt_dt_cd,
85 epe.dpnt_cvg_strt_dt_rl,
86 epe.pgm_id,
87 epe.pl_id,
88 epe.ptip_id
89 from ben_elig_per_elctbl_chc epe,
90 ben_oipl_f oipl
91 where epe.per_in_ler_id = p_per_in_ler_id
92 and epe.pl_id = p_pl_id
93 and epe.oipl_id = oipl.oipl_id
94 and epe.elctbl_flag = 'Y'
95 and oipl.opt_id = p_opt_id
96 and (epe.pgm_id = p_pgm_id OR p_pgm_id IS NULL)
97 and p_life_event_date between oipl.effective_start_date
98 and oipl.effective_end_date ;
99 --
100 --Get EPE from Plan
101 cursor c_epe_pl(p_per_in_ler_id number,
102 p_pl_id number,
103 p_pgm_id number ) is
104 select epe.elig_per_elctbl_chc_id,
105 epe.enrt_cvg_strt_dt,
106 epe.enrt_cvg_strt_dt_cd,
107 epe.prtt_enrt_rslt_id,
108 epe.oipl_id,
109 epe.dpnt_cvg_strt_dt_cd,
110 epe.dpnt_cvg_strt_dt_rl,
111 epe.pgm_id,
112 epe.pl_id,
113 epe.ptip_id
114 from ben_elig_per_elctbl_chc epe
115 where epe.per_in_ler_id = p_per_in_ler_id
116 and epe.oipl_id IS NULL --- SAVINGS PLAN FIX
117 and epe.pl_id = p_pl_id
118 and epe.elctbl_flag = 'Y'
119 and (epe.pgm_id = p_pgm_id OR p_pgm_id IS NULL) ;
120 --
121 cursor c_egd(p_per_in_ler_id number,
122 p_elig_per_elctbl_chc_id number,
123 p_dpnt_person_id number) is
124 select egd.elig_dpnt_id,
125 egd.elig_strt_dt,
126 egd.elig_thru_dt
127 from ben_elig_dpnt egd
128 where egd.per_in_ler_id = p_per_in_ler_id
129 and egd.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
130 and egd.dpnt_person_id = p_dpnt_person_id ;
131 --
132 cursor c_bnf(p_prtt_enrt_rslt_id number,
133 p_bnf_person_id number,
134 p_effective_date date ) is
135 select pbn.pl_bnf_id,
136 pbn.dsgn_strt_dt,
137 pbn.dsgn_thru_dt,
138 pbn.object_version_number,
139 pbn.effective_start_date
140 from ben_pl_bnf_f pbn
141 where pbn.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
142 and pbn.bnf_person_id = p_bnf_person_id
143 and p_effective_date between pbn.effective_start_date
144 and pbn.effective_end_date ;
145 --
146 cursor c_bnf_org(p_prtt_enrt_rslt_id number,
147 p_organization_id number,
148 p_effective_date date ) is
149 select pbn.pl_bnf_id,
150 pbn.dsgn_strt_dt,
151 pbn.dsgn_thru_dt,
152 pbn.object_version_number,
153 pbn.effective_start_date
154 from ben_pl_bnf_f pbn
155 where pbn.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
156 and pbn.organization_id = p_organization_id
157 and p_effective_date between pbn.effective_start_date
158 and pbn.effective_end_date ;
159 --
160 procedure get_pil
161 (p_person_id in number,
162 p_ler_id in number,
163 p_life_event_date in date,
164 p_pil out nocopy c_pil%ROWTYPE ) is
165 --
166 l_proc varchar2(60) := 'ben_enrollment_process.get_pil';
167 --
168 l_pil c_pil%ROWTYPE;
169 --
170 begin
171 --Get PIL
172 OPEN c_pil(p_ler_id,p_life_event_date,p_person_id);
173 FETCH c_pil into l_pil ;
174 --
175 IF c_pil%NOTFOUND THEN
176 --
177 CLOSE c_pil;
178 if g_debug then
179 hr_utility.set_location('BEN_94534_PIL_NOT_FOUND'|| to_char(p_person_id),54);
180 end if;
181 fnd_message.set_name('BEN','BEN_94534_PIL_NOT_FOUND');
182 fnd_message.set_token('PROC',l_proc);
183 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
184 fnd_message.set_token('LER_ID',to_char(p_ler_id));
185 fnd_message.set_token('LE_DATE', p_life_event_date);
186 fnd_message.raise_error;
187 --
188 END IF;
189 CLOSE c_pil;
190 --
191 p_pil := l_pil ;
192 --
193 end get_pil ;
194 --
195 procedure get_epe
196 (p_person_id in number,
197 p_per_in_ler_id in number,
198 p_life_event_date in date,
199 p_opt_id in number,
200 p_pl_id in number,
201 p_pgm_id in number,
202 p_epe out nocopy c_epe_pl%ROWTYPE ) is
203 --
204 l_proc varchar2(60) := 'ben_enrollment_process.get_epe';
205 l_epe c_epe_pl%ROWTYPE;
206 --
207 begin
208 --Get EPE
209 --Get EPE
210 IF p_opt_id IS NOT NULL THEN
211 --
212 OPEN c_epe_oipl(p_per_in_ler_id,
213 p_pl_id,
214 p_opt_id,
215 p_life_event_date,
216 p_pgm_id ) ;
217 FETCH c_epe_oipl INTO l_epe;
218 IF c_epe_oipl%NOTFOUND THEN
219 CLOSE c_epe_oipl ;
220 if g_debug then
221 hr_utility.set_location('BEN_94612_NO_EPE_EU'|| to_char(p_person_id),54);
222 end if;
223 fnd_message.set_name('BEN','BEN_94612_NO_EPE_EU');
224 fnd_message.set_token('PROC',l_proc);
225 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
226 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
227 fnd_message.set_token('PL_ID',to_char(p_pl_id));
228 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
229 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
230 fnd_message.set_token('LE_DATE',p_life_event_date);
231 fnd_message.raise_error;
232 --
233 END IF;
234 --
235 CLOSE c_epe_oipl ;
236 --
237 ELSE
238 --
239 OPEN c_epe_pl(p_per_in_ler_id,
240 p_pl_id,
241 p_pgm_id ) ;
242 FETCH c_epe_pl INTO l_epe;
243 IF c_epe_pl%NOTFOUND THEN
244 CLOSE c_epe_pl ;
245 if g_debug then
246 hr_utility.set_location('BEN_94612_NO_EPE_EU'|| to_char(p_person_id),54);
247 end if;
248 fnd_message.set_name('BEN','BEN_94612_NO_EPE_EU');
249 fnd_message.set_token('PROC',l_proc);
250 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
251 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
252 fnd_message.set_token('PL_ID',to_char(p_pl_id));
253 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
254 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
255 fnd_message.set_token('LE_DATE',p_life_event_date);
256 fnd_message.raise_error;
257 --
258 END IF;
259 --
260 CLOSE c_epe_pl ;
261 --
262 END IF;
263 --
264 p_epe := l_epe ;
265 --
266 end get_epe ;
267 --
268 procedure get_egd
269 (p_per_in_ler_id in number,
270 p_dpnt_person_id in number,
271 p_elig_per_elctbl_chc_id in number,
272 p_egd out nocopy c_egd%ROWTYPE ) is
273 --
274 l_proc varchar2(60) := 'ben_enrollment_process.get_egd';
275 --
276 l_pil c_pil%ROWTYPE;
277 l_epe c_epe_oipl%ROWTYPE;
278 l_egd c_egd%ROWTYPE;
279 --
280 begin
281 --Get PIL
282 open c_egd(p_per_in_ler_id => p_per_in_ler_id,
283 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
284 p_dpnt_person_id => p_dpnt_person_id
285 );
286 fetch c_egd into l_egd ;
287 --
288 IF c_egd%NOTFOUND THEN
289 CLOSE c_egd ;
290 if g_debug then
291 hr_utility.set_location('BEN_94616_DPNT_WRONG',54);
292 end if;
293 fnd_message.set_name('BEN','BEN_94616_DPNT_WRONG');
294 fnd_message.set_token('PROC',l_proc);
295 fnd_message.raise_error;
296 --
297 END IF;
298 --
299 close c_egd ;
300 --
301 p_egd := l_egd ;
302 --
303 end get_egd ;
304 --
305 procedure check_pen
306 (p_per_in_ler_id in number,
307 p_prtt_enrt_rslt_id in number ) is
308 --
309 l_proc varchar2(60) := 'ben_enrollment_process.check_pen';
310 --
311 cursor c_pen is
312 select 'x'
313 from ben_prtt_enrt_rslt_f pen
314 where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
315 and pen.prtt_enrt_rslt_stat_cd is null
316 and pen.effective_end_date = hr_api.g_eot
317 and pen.enrt_cvg_thru_dt = hr_api.g_eot ;
318 --
319 l_dummy varchar2(30);
320 --
321 begin
322 --Get PIL
323 IF p_prtt_enrt_rslt_id IS NOT NULL THEN
324 open c_pen;
325 fetch c_pen into l_dummy;
326 IF c_pen%NOTFOUND THEN
327 CLOSE c_pen ;
328 if g_debug then
329 hr_utility.set_location('BEN_94617_DPNT_NO_PEN',54);
330 end if;
331 fnd_message.set_name('BEN','BEN_94617_DPNT_NO_PEN');
332 fnd_message.set_token('PROC',l_proc);
333 fnd_message.raise_error;
334 --
335 END IF;
336 --
337 close c_pen ;
338 ELSE
339 --
340 if g_debug then
341 hr_utility.set_location('BEN_94617_DPNT_NO_PEN',54);
342 end if;
343 fnd_message.set_name('BEN','BEN_94617_DPNT_NO_PEN');
344 fnd_message.set_token('PROC',l_proc);
345 fnd_message.raise_error;
346 --
347 END IF;
348 --
349 end check_pen ;
350 --
351 procedure get_pbn
352 (p_effective_date in date,
353 p_bnf_person_id in number,
354 p_organization_id in number,
355 p_prtt_enrt_rslt_id in number,
356 p_bnf out nocopy c_bnf%ROWTYPE ) is
357 --
358 l_proc varchar2(60) := 'ben_enrollment_process.get_pbn';
359 --
360 l_bnf c_bnf%ROWTYPE;
361 --
362 begin
363 --
364 IF p_organization_id IS NULL AND p_bnf_person_id IS NULL THEN
365 --
366 if g_debug then
367 hr_utility.set_location('BEN_94617_NO_BNF_PERSON',54);
368 end if;
369 fnd_message.set_name('BEN','BEN_94617_NO_BNF_PERSON');
370 fnd_message.set_token('PROC',l_proc);
371 fnd_message.raise_error;
372 --
373 END IF;
374 --Get PIL
375 IF p_organization_id IS NOT NULL THEN
376 --
377 open c_bnf_org(p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
378 p_organization_id => p_organization_id,
379 p_effective_date => p_effective_date
380 );
381 fetch c_bnf_org into l_bnf ;
382 close c_bnf_org ;
383 --
384 ELSIF p_bnf_person_id IS NOT NULL THEN
385 --
386 open c_bnf(p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
387 p_bnf_person_id => p_bnf_person_id,
388 p_effective_date => p_effective_date
389 );
390 fetch c_bnf into l_bnf ;
391 close c_bnf ;
392 --
393 ELSE
394 --Raise error;
395 null;
396 END IF;
397 --
398 p_bnf := l_bnf ;
399 --
400 end get_pbn ;
401 --
402 procedure get_current_pen
403 (p_effective_date in date,
404 p_life_event_date in date,
405 p_person_id in number,
406 p_pgm_id in number,
407 p_pl_id in number,
408 p_opt_id in number,
409 p_bnft_val in number,
410 p_prtt_enrt_rslt_id out nocopy number,
411 p_object_version_number out nocopy number) is
412 --
413 l_proc varchar2(60) := 'ben_enrollment_information_detail.get_pen';
414 l_prtt_enrt_rslt_id number ;
415 l_oipl_id number ;
416 --
417 cursor c_oipl is
418 select oipl_id
419 from ben_oipl_f
420 where opt_id = p_opt_id
421 and pl_id = p_pl_id
422 and p_effective_date between effective_start_date
423 and effective_end_date;
424 --
425 cursor c_pen_plnip is
426 select pen.prtt_enrt_rslt_id,
427 pen.object_version_number
428 from ben_prtt_enrt_rslt_f pen
429 where pen.pl_id = p_pl_id
430 and pen.person_id = p_person_id
431 and pen.effective_end_date = hr_api.g_eot
432 and pen.enrt_cvg_thru_dt = hr_api.g_eot
433 and ( p_bnft_val is NULL or
434 pen.bnft_amt = p_bnft_val)
435 and prtt_enrt_rslt_stat_cd is null;
436 --
437 cursor c_pen_oiplnip is
438 select pen.prtt_enrt_rslt_id,
439 pen.object_version_number
440 from ben_prtt_enrt_rslt_f pen
441 where pen.oipl_id = l_oipl_id
442 and pen.person_id = p_person_id
443 and pen.effective_end_date = hr_api.g_eot
444 and pen.enrt_cvg_thru_dt = hr_api.g_eot
445 and ( p_bnft_val is NULL or
446 pen.bnft_amt = p_bnft_val)
447 and prtt_enrt_rslt_stat_cd is null;
448 --
449 cursor c_pen_pl is
450 select pen.prtt_enrt_rslt_id,
451 pen.object_version_number
452 from ben_prtt_enrt_rslt_f pen
453 where pen.pgm_id = p_pgm_id
454 and pen.pl_id = p_pl_id
455 and pen.person_id = p_person_id
456 and pen.effective_end_date = hr_api.g_eot
457 and pen.enrt_cvg_thru_dt = hr_api.g_eot
458 and ( p_bnft_val is NULL or
459 pen.bnft_amt = p_bnft_val)
460 and prtt_enrt_rslt_stat_cd is null;
461 --
462 cursor c_pen_oipl is
463 select pen.prtt_enrt_rslt_id,
464 pen.object_version_number
465 from ben_prtt_enrt_rslt_f pen
466 where pen.pgm_id = p_pgm_id
467 and pen.oipl_id = l_oipl_id
468 and pen.person_id = p_person_id
469 and pen.effective_end_date = hr_api.g_eot
470 and pen.enrt_cvg_thru_dt = hr_api.g_eot
471 and ( p_bnft_val is NULL or
472 pen.bnft_amt = p_bnft_val)
473 and prtt_enrt_rslt_stat_cd is null;
474 --
475 l_pen c_pen_plnip%ROWTYPE;
476 --
477 begin
478 --
479 open c_oipl ;
480 fetch c_oipl into l_oipl_id;
481 close c_oipl ;
482 --
483 IF p_pgm_id IS NULL THEN
484 --
485 IF l_oipl_id IS NULL THEN
486 --
487 open c_pen_plnip ;
488 fetch c_pen_plnip into l_pen ;
489 close c_pen_plnip ;
490 --
491 ELSE -- Plan level
492 open c_pen_oiplnip ;
493 fetch c_pen_oiplnip into l_pen ;
494 close c_pen_oiplnip ;
495 END IF;
496 --
497 ELSE
498 --
499 IF l_oipl_id IS NULL THEN
500 --
501 open c_pen_pl;
502 fetch c_pen_pl into l_pen ;
503 close c_pen_pl ;
504 --
505 ELSE -- Plan level
506 open c_pen_oipl ;
507 fetch c_pen_oipl into l_pen ;
508 close c_pen_oipl ;
509 END IF;
510 --
511 END IF;
512 --
513 p_prtt_enrt_rslt_id := l_pen.prtt_enrt_rslt_id;
514 p_object_version_number := l_pen.object_version_number;
515 --
516 end get_current_pen ;
517 --
518 procedure get_ended_pen
519 (p_effective_date in date,
520 p_life_event_date in date,
521 p_person_id in number,
522 p_pgm_id in number,
523 p_pl_id in number,
524 p_opt_id in number,
525 p_prtt_enrt_rslt_id out nocopy number) is
526 --
527 l_proc varchar2(60) := 'ben_enrollment_information_detail.get_ended_pen';
528 l_prtt_enrt_rslt_id number ;
529 l_oipl_id number ;
530 --
531 cursor c_oipl is
532 select oipl_id
533 from ben_oipl_f
534 where opt_id = p_opt_id
535 and pl_id = p_pl_id
536 and p_effective_date between effective_start_date
537 and effective_end_date;
538 --
539 cursor c_pen_plnip is
540 select pen.prtt_enrt_rslt_id,
541 pen.object_version_number
542 from ben_prtt_enrt_rslt_f pen
543 where pen.pl_id = p_pl_id
544 and pen.person_id = p_person_id
545 and (pen.effective_end_date <> hr_api.g_eot
546 or pen.enrt_cvg_thru_dt <> hr_api.g_eot);
547 --
548 cursor c_pen_oiplnip is
549 select pen.prtt_enrt_rslt_id,
550 pen.object_version_number
551 from ben_prtt_enrt_rslt_f pen
552 where pen.oipl_id = l_oipl_id
553 and pen.person_id = p_person_id
554 and (pen.effective_end_date <> hr_api.g_eot
555 or pen.enrt_cvg_thru_dt <> hr_api.g_eot);
556 --
557 cursor c_pen_pl is
558 select pen.prtt_enrt_rslt_id,
559 pen.object_version_number
560 from ben_prtt_enrt_rslt_f pen
561 where pen.pgm_id = p_pgm_id
562 and pen.pl_id = p_pl_id
563 and pen.person_id = p_person_id
564 and (pen.effective_end_date <> hr_api.g_eot
565 or pen.enrt_cvg_thru_dt <> hr_api.g_eot);
566 --
567 cursor c_pen_oipl is
568 select pen.prtt_enrt_rslt_id,
569 pen.object_version_number
570 from ben_prtt_enrt_rslt_f pen
571 where pen.pgm_id = p_pgm_id
572 and pen.oipl_id = l_oipl_id
573 and pen.person_id = p_person_id
574 and (pen.effective_end_date <> hr_api.g_eot
575 or pen.enrt_cvg_thru_dt <> hr_api.g_eot);
576 --
577 l_pen c_pen_plnip%ROWTYPE;
578 --
579 begin
580 --
581 open c_oipl ;
582 fetch c_oipl into l_oipl_id;
583 close c_oipl ;
584 --
585 IF p_pgm_id IS NULL THEN
586 --
587 IF l_oipl_id IS NULL THEN
588 --
589 open c_pen_plnip ;
590 fetch c_pen_plnip into l_pen ;
591 close c_pen_plnip ;
592 --
593 ELSE -- Plan level
594 open c_pen_oiplnip ;
595 fetch c_pen_oiplnip into l_pen ;
596 close c_pen_oiplnip ;
597 END IF;
598 --
599 ELSE
600 --
601 IF l_oipl_id IS NULL THEN
602 --
603 open c_pen_pl;
604 fetch c_pen_pl into l_pen ;
605 close c_pen_pl ;
606 --
607 ELSE -- Plan level
608 open c_pen_oipl ;
609 fetch c_pen_oipl into l_pen ;
610 close c_pen_oipl ;
611 END IF;
612 --
613 END IF;
614 --
615 p_prtt_enrt_rslt_id := l_pen.prtt_enrt_rslt_id;
616 --
617 end get_ended_pen;
618 --
619 -- --------------------------------------------------------------------------------
620 -- |-----------------------------< ELECTION_INFORMATION >-------------------------|
621 -- -------------------------------------------------------------------------------+
622 --
623 procedure enrollment_information_detail
624 (p_validate in boolean default false
625 ,p_pgm_id in number default null
626 ,p_pl_id in number default null
627 ,p_opt_id in number default null
628 ,p_ler_id in number
629 ,p_life_event_date in date
630 ,p_ended_pl_id in number default null
631 ,p_ended_opt_id in number default null
632 ,p_ended_bnft_val in number default null
633 ,p_effective_date in date
634 ,p_person_id in number
635 ,p_bnft_val in number default null
636 ,p_acty_base_rt_id1 in number default null
637 ,p_rt_val1 in number default null
638 ,p_ann_rt_val1 in number default null
639 ,p_rt_strt_dt1 in date default null
640 ,p_rt_end_dt1 in date default null
641 ,p_acty_base_rt_id2 in number default null
642 ,p_rt_val2 in number default null
643 ,p_ann_rt_val2 in number default null
644 ,p_rt_strt_dt2 in date default null
645 ,p_rt_end_dt2 in date default null
646 ,p_acty_base_rt_id3 in number default null
647 ,p_rt_val3 in number default null
648 ,p_ann_rt_val3 in number default null
649 ,p_rt_strt_dt3 in date default null
650 ,p_rt_end_dt3 in date default null
651 ,p_acty_base_rt_id4 in number default null
652 ,p_rt_val4 in number default null
653 ,p_ann_rt_val4 in number default null
654 ,p_rt_strt_dt4 in date default null
655 ,p_rt_end_dt4 in date default null
656 ,p_business_group_id in number
657 ,p_enrt_cvg_strt_dt in date default null
658 ,p_enrt_cvg_thru_dt in date default null
659 ,p_orgnl_enrt_dt in date default null ) is
660 --
661 --
662 l_proc varchar2(60) := 'ben_enrollment_process.enrollment_information_detail';
663 l_suspend_flag varchar2(30);
664 l_dpnt_actn_warning boolean;
665 l_bnf_actn_warning boolean;
666 l_ctfn_actn_warning boolean;
667 l_object_version_number number;
668 l_prtt_enrt_rslt_id number;
669 l_prtt_rt_val_id1 number;
670 l_prtt_rt_val_id2 number;
671 l_prtt_rt_val_id3 number;
672 l_prtt_rt_val_id4 number;
673 l_prtt_rt_val_id5 number;
674 l_prtt_rt_val_id6 number;
675 l_prtt_rt_val_id7 number;
676 l_prtt_rt_val_id8 number;
677 l_prtt_rt_val_id9 number;
678 l_prtt_rt_val_id10 number;
679 l_enrt_rt_id1 number;
680 l_enrt_rt_id2 number;
681 l_enrt_rt_id3 number;
682 l_enrt_rt_id4 number;
683 l_enrt_bnft_id number;
684 l_elig_per_elctbl_chc_id number;
685 l_prtt_enrt_interim_id number;
686 l_effective_start_date date;
687 l_effective_end_date date;
688 l_datetrack_mode varchar2(30) ;
689 l_pl_id number := p_pl_id;
690 l_enroll_flag boolean := true;
691 --
692 l_pil c_pil%ROWTYPE;
693 --
694 l_epe c_epe_oipl%ROWTYPE;
695 --
696 --GET ENB when bnft amount entered by the user
697 --
698 cursor c_enb_with_amt is
699 select enb.enrt_bnft_id,
700 enb.cvg_mlt_cd,
701 enb.entr_val_at_enrt_flag,
702 enb.val,
703 enb.mn_val,
704 enb.mx_val,
705 enb.incrmt_val
706 from ben_enrt_bnft enb
707 where enb.elig_per_elctbl_chc_id = l_epe.elig_per_elctbl_chc_id
708 and enb.mx_wo_ctfn_flag = 'N'
709 and (enb.entr_val_at_enrt_flag = 'Y' OR enb.vaL = p_bnft_val) ;
710 --
711 l_enb c_enb_with_amt%ROWTYPE;
712 --
713 --GET ENB when bnft amount is not entered by the user
714 --
715 cursor c_enb is
716 select enb.enrt_bnft_id,enb.cvg_mlt_cd,enb.entr_val_at_enrt_flag,enb.val,
717 enb.mn_val,
718 enb.mx_val,
719 enb.incrmt_val
720 from ben_enrt_bnft enb
721 where enb.elig_per_elctbl_chc_id = l_epe.elig_per_elctbl_chc_id
722 and enb.mx_wo_ctfn_flag = 'N' ;
723 --
724 --ECR from EPE
725 --
726 cursor c_ecr(p_acty_base_rt_id number) is
727 select enrt_rt_id,
728 rt_strt_dt,
729 rt_strt_dt_cd,
730 entr_val_at_enrt_flag,
731 val,
732 ann_val
733 from ben_enrt_rt ecr
734 where ecr.elig_per_elctbl_chc_id = l_epe.elig_per_elctbl_chc_id
735 --and ecr.entr_val_at_enrt_flag = 'Y'
736 and ecr.acty_base_rt_id = p_acty_base_rt_id ;
737 --
738 l_ecr1 c_ecr%ROWTYPE;
739 l_ecr2 c_ecr%ROWTYPE;
740 l_ecr3 c_ecr%ROWTYPE;
741 l_ecr4 c_ecr%ROWTYPE;
742 --
743 --ECR from ENB
744 --
745 cursor c_ecr_enb(p_acty_base_rt_id number) is
746 select enrt_rt_id,
747 rt_strt_dt,
748 rt_strt_dt_cd,
749 entr_val_at_enrt_flag,
750 val,
751 ann_val
752 from ben_enrt_rt ecr
753 where ecr.enrt_bnft_id = l_enb.enrt_bnft_id
754 --and ecr.entr_val_at_enrt_flag = 'Y'
755 and ecr.acty_base_rt_id = p_acty_base_rt_id ;
756 --
757 cursor c_ecr_eve(p_epe_id number) is
758 select 'Y'
759 from ben_enrt_rt ecr
760 where ecr.elig_per_elctbl_chc_id = p_epe_id
761 and ecr.entr_val_at_enrt_flag = 'Y' ;
762 --
763 cursor c_ecr_enb_eve(p_enb_id number) is
764 select 'Y'
765 from ben_enrt_rt ecr
766 where ecr.enrt_bnft_id = p_enb_id
767 and ecr.entr_val_at_enrt_flag = 'Y' ;
768 --
769 l_dummy varchar2(30);
770 --
771 cursor c_pl_opt_name(p_epe_id number) is
772 select pln.name || ' '|| opt.name
773 from ben_elig_per_elctbl_chc epe,
774 ben_pl_f pln,
775 ben_oipl_f oipl,
776 ben_opt_f opt
777 where epe.elig_per_elctbl_chc_id = p_epe_id
778 and epe.pl_id = pln.pl_id
779 and epe.oipl_id = oipl.oipl_id(+)
780 and oipl.opt_id = opt.opt_id(+)
781 and p_life_event_date between
782 pln.effective_start_date and pln.effective_end_date
783 and p_life_event_date between
784 oipl.effective_start_date(+) and oipl.effective_end_date(+)
785 and p_life_event_date between
786 opt.effective_start_date(+) and opt.effective_end_date(+);
787 --
788 l_pl_opt_name varchar2(600) := null;
789 --
790 begin
791 --
792 hr_utility.set_location('Entering:'||l_proc, 20);
793 --
794 fnd_file.put_line
795 (which => fnd_file.log,
796 buff => 'Entering - BEN_ENROLLMENT_PROCESS.ENROLLMENT_INFORMATION_DETAIL' );
797 fnd_file.put_line
798 (which => fnd_file.log,
799 buff => 'l_person_id '||p_person_id||' l_pgm_id '||p_pgm_id||' l_pl_id '||p_pl_id);
800 --
801 fnd_file.put_line
802 (which => fnd_file.log,
803 buff => 'l_opt_id '||p_opt_id||' l_ler_id '||p_ler_id);
804 fnd_file.put_line
805 (which => fnd_file.log,
806 buff => 'p_bnft_val '||p_bnft_val);
807 fnd_file.put_line
808 (which => fnd_file.log,
809 buff => 'l_life_event_date '||p_life_event_date);
810 --
811 fnd_file.put_line
812 (which => fnd_file.log,
813 buff => 'p_ended_pl_id '||p_ended_pl_id||' p_ended_opt_id '||p_ended_opt_id);
814 --
815 get_pil
816 (p_person_id => p_person_id,
817 p_ler_id => p_ler_id,
818 p_life_event_date => p_life_event_date,
819 p_pil => l_pil
820 );
821 --
822 --Set De-Enroll flag if ended plan details have been entered by the user
823 --
824 if p_ended_pl_id is not null and p_pl_id is null then --De Enroll person from the spedified plan
825 l_enroll_flag := false;
826 end if;
827 --
828 if (l_enroll_flag) then
829 --
830 --Get EPE
831 --
832 get_epe
833 (p_person_id => p_person_id,
834 p_per_in_ler_id => l_pil.per_in_ler_id,
835 p_life_event_date => p_life_event_date,
836 p_opt_id => p_opt_id,
837 p_pl_id => p_pl_id,
838 p_pgm_id => p_pgm_id,
839 p_epe => l_epe
840 ) ;
841 --
842 --Get Ended Enrollment Result
843 --
844 get_current_pen
845 (p_effective_date => p_effective_date,
846 p_life_event_date => p_life_event_date,
847 p_person_id => p_person_id,
848 p_pgm_id => p_pgm_id,
849 p_pl_id => p_ended_pl_id,
850 p_opt_id => p_ended_opt_id,
851 p_bnft_val => p_ended_bnft_val,
852 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id,
853 p_object_version_number => l_object_version_number
854 );
855 --
856 --Coverage start date check. User should only enter for the code ENTRBL
857 IF l_epe.enrt_cvg_strt_dt_cd = 'ENTRBL' THEN
858 --
859 IF p_enrt_cvg_strt_dt IS NOT NULL THEN
860 l_epe.enrt_cvg_strt_dt := p_enrt_cvg_strt_dt ;
861 ELSE
862 --Throw Error.. Coverage start date needs to be entered
863 if g_debug then
864 hr_utility.set_location('BEN_94552_NO_CVG_STRT_DT'|| to_char(p_person_id),54);
865 end if;
866 fnd_message.set_name('BEN','BEN_94552_NO_CVG_STRT_DT');
867 fnd_message.set_token('PACKAGE',l_proc);
868 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
869 fnd_message.set_token('LER_ID', to_char(p_ler_id));
870 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
871 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
872 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
873 fnd_message.raise_error;
874 --
875 END IF;
876 --
877 ELSE
878 --
879 if g_debug then
880 hr_utility.set_location('No Coverage Start date is required '||p_enrt_cvg_strt_dt ,54);
881 end if;
882 --
883 END IF;
884 --
885 --Get ENB
886 --User needs to enter benefit amout for flat range and enter value at enrollment cases.
887 --Enter Benefit amount.
888 --Flat Range
889 --Other types.
890 --
891 --If amount is entered by user, get the matching record or MX_WO_CTFN_FLAG
892 --If no amount
893 IF p_bnft_val IS NOT NULL THEN
894 --
895 OPEN c_enb_with_amt ;
896 --
897 FETCH c_enb_with_amt INTO l_enb;
898 IF c_enb_with_amt%NOTFOUND THEN
899 CLOSE c_enb_with_amt ;
900 if g_debug then
901 hr_utility.set_location('BEN_91561_BENVRBRT_ENB_NF'|| to_char(p_person_id),54);
902 end if;
903 fnd_message.set_name('BEN','BEN_91561_BENVRBRT_ENB_NF');
904 fnd_message.set_token('PACKAGE',l_proc);
905 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
906 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
907 fnd_message.set_token('PL_ID', to_char(p_pl_id));
908 fnd_message.set_token('OIPL_ID',to_char(l_epe.oipl_id));
909 fnd_message.set_token('LF_EVT_OCRD_DT',p_life_event_date);
910 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
911 fnd_message.set_token('PER_IN_LER_ID',to_char(l_pil.per_in_ler_id));
912 fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID', to_char(l_epe.elig_per_elctbl_chc_id));
913 fnd_message.raise_error;
914 --
915 ELSE
916 --
917 --Check check to see if the coverage calculation method requires the benefit amount.
918 IF (l_enb.entr_val_at_enrt_flag = 'Y' or l_enb.cvg_mlt_cd like '%RNG') THEN
919 --
920 l_enb.val := p_bnft_val ;
921 --
922 ELSE
923 --
924 CLOSE c_enb_with_amt ;
925 if g_debug then
926 hr_utility.set_location('BEN_94558_INVALID_BNFT_VAL'|| to_char(p_person_id),54);
927 end if;
928 fnd_message.set_name('BEN','BEN_91561_BENVRBRT_ENB_NF');
929 fnd_message.set_token('PROC',l_proc);
930 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
931 fnd_message.set_token('LER_ID',to_char(p_ler_id));
932 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
933 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
934 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
935 fnd_message.raise_error;
936 --
937 END IF;
938 --
939 END IF;
940 --
941 CLOSE c_enb_with_amt ;
942 --Validation for Benefit Amount ranges
943 if p_bnft_val is not null then
944 --
945 -- Bug 3181158, added nvl in 'if' to handle
946 -- 'EnrtValAtEnrt + no default value' condition
947 --
948 if ((l_enb.mn_val is not null and p_bnft_val < l_enb.mn_val) or
949 (l_enb.mx_val is not null and p_bnft_val > l_enb.mx_val)) then
950 --
951 -- Open the c_pl_opt_name cursor only if error needs to be displayed.
952 --
953 open c_pl_opt_name(l_epe.elig_per_elctbl_chc_id);
954 fetch c_pl_opt_name into l_pl_opt_name;
955 close c_pl_opt_name;
956 --
957 fnd_message.set_name('BEN','BEN_92394_OUT_OF_RANGE');
958 fnd_message.set_token('MINIMUM', l_enb.mn_val);
959 fnd_message.set_token('MAXIMUM', l_enb.mx_val);
960 fnd_message.set_token('PLAN', l_pl_opt_name);
961 fnd_message.raise_error;
962 --
963 end if;
964 --
965 if l_enb.mn_val is not null and
966 l_enb.incrmt_val is not null and
967 mod(p_bnft_val-l_enb.mn_val, l_enb.incrmt_val) <> 0 then
968 --
969 -- Open the c_pl_opt_name cursor only if error needs to be displayed.
970 --
971 open c_pl_opt_name(l_epe.elig_per_elctbl_chc_id);
972 fetch c_pl_opt_name into l_pl_opt_name;
973 close c_pl_opt_name;
974 --
975 fnd_message.set_name('BEN','BEN_92395_NOT_IN_INCR');
976 fnd_message.set_token('INCREMENT', l_enb.incrmt_val);
977 fnd_message.set_token('PLAN', l_pl_opt_name);
978 fnd_message.raise_error;
979 --
980 end if;
981 --
982 end if;
983 --
984 --
985 ELSE
986 --
987 OPEN c_enb ;
988 FETCH c_enb INTO l_enb;
989 IF ((l_enb.entr_val_at_enrt_flag = 'Y' or l_enb.cvg_mlt_cd like '%RNG') and
990 p_bnft_val IS NULL) THEN
991 --
992 if g_debug then
993 hr_utility.set_location('BEN_94559_NO_BNFT_VAL'|| to_char(p_person_id),54);
994 end if;
995 CLOSE c_enb ;
996 fnd_message.set_name('BEN','BEN_94559_NO_BNFT_VAL');
997 fnd_message.set_token('PROC',l_proc);
998 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
999 fnd_message.set_token('LER_ID',to_char(p_ler_id));
1000 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1001 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
1002 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
1003 fnd_message.raise_error;
1004 --
1005 END IF;
1006 CLOSE c_enb ;
1007 --
1008 END IF;
1009 --
1010 IF l_enb.enrt_bnft_id IS NOT NULL THEN
1011 open c_ecr_enb_eve(l_enb.enrt_bnft_id );
1012 fetch c_ecr_enb_eve into l_dummy;
1013 close c_ecr_enb_eve;
1014 --
1015 ELSE
1016 --
1017 open c_ecr_eve(l_epe.elig_per_elctbl_chc_id);
1018 fetch c_ecr_eve into l_dummy;
1019 close c_ecr_eve;
1020 --
1021 END IF;
1022 --
1023 IF l_dummy = 'Y' THEN
1024 --
1025 IF p_acty_base_rt_id1 IS NOT NULL AND
1026 (p_rt_val1 IS NOT NULL OR p_ann_rt_val1 IS NOT NULL OR p_rt_strt_dt1 IS NOT NULL ) THEN
1027 --
1028 null;
1029 ELSIF p_acty_base_rt_id2 IS NOT NULL AND
1030 (p_rt_val2 IS NOT NULL OR p_ann_rt_val2 IS NOT NULL OR p_rt_strt_dt2 IS NOT NULL ) THEN
1031 --
1032 null;
1033 --
1034 ELSIF p_acty_base_rt_id3 IS NOT NULL AND
1035 (p_rt_val3 IS NOT NULL OR p_ann_rt_val3 IS NOT NULL OR p_rt_strt_dt3 IS NOT NULL ) THEN
1036 --
1037 null;
1038 --
1039 ELSIF p_acty_base_rt_id4 IS NOT NULL AND
1040 (p_rt_val4 IS NOT NULL OR p_ann_rt_val4 IS NOT NULL OR p_rt_strt_dt4 IS NOT NULL ) THEN
1041 --
1042 null;
1043 --
1044 ELSE
1045 --
1046 if g_debug then
1047 hr_utility.set_location('APP_94555_NO_RATE_VAL'|| to_char(p_person_id),54);
1048 end if;
1049 fnd_message.set_name('BEN','APP_94555_NO_RATE_VAL');
1050 fnd_message.set_token('PROC',l_proc);
1051 fnd_message.set_token('PERSON_ID', to_char(p_person_id));
1052 fnd_message.set_token('LER_ID',to_char(p_ler_id));
1053 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1054 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
1055 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
1056 fnd_message.raise_error;
1057 --
1058 END IF;
1059
1060 END IF ;
1061 --
1062
1063 --Get ECR
1064 --
1065 IF p_acty_base_rt_id1 IS NOT NULL AND
1066 (p_rt_val1 IS NOT NULL OR p_ann_rt_val1 IS NOT NULL OR p_rt_strt_dt1 IS NOT NULL ) THEN
1067 --
1068 IF l_enb.enrt_bnft_id IS NOT NULL THEN
1069 --
1070 OPEN c_ecr_enb(p_acty_base_rt_id1);
1071 FETCH c_ecr_enb INTO l_ecr1; -- l_enrt_rt_id1;
1072 IF c_ecr_enb%NOTFOUND THEN
1073 CLOSE c_ecr_enb ;
1074 if g_debug then
1075 hr_utility.set_location('BEN_94535_ECR_NOT_FOUND'|| to_char(p_person_id),54);
1076 end if;
1077 fnd_message.set_name('BEN','BEN_94535_ECR_NOT_FOUND');
1078 fnd_message.set_token('PROC',l_proc);
1079 fnd_message.set_token('PERSON_ID', to_char(p_person_id));
1080 fnd_message.set_token('ABR_ID', to_char(p_acty_base_rt_id1));
1081 fnd_message.set_token('RT_VAL', to_char(p_rt_val1));
1082 fnd_message.set_token('ANN_RT_VAL', to_char(p_ann_rt_val1));
1083 fnd_message.raise_error;
1084 --
1085 ELSE
1086 --
1087 IF l_ecr1.rt_strt_dt_cd = 'ENTRBL' THEN
1088 IF p_rt_strt_dt1 IS NOT NULL THEN
1089 --
1090 l_ecr1.rt_strt_dt := p_rt_strt_dt1 ;
1091 --
1092 ELSE
1093 --Throw Error
1094 if g_debug then
1095 hr_utility.set_location('BEN_94552_NO_RT_STRT_DT'|| to_char(p_person_id),54);
1096 end if;
1097 CLOSE c_ecr_enb;
1098 fnd_message.set_name('BEN','BEN_94552_NO_RT_STRT_DT');
1099 fnd_message.set_token('PACKAGE',l_proc);
1100 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1101 fnd_message.set_token('LER_ID', to_char(p_ler_id));
1102 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1103 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
1104 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
1105 fnd_message.raise_error;
1106 --
1107 END IF;
1108 END IF;
1109 --
1110 IF l_ecr1.entr_val_at_enrt_flag = 'Y' THEN
1111 --
1112 IF p_rt_val1 IS NOT NULL OR p_ann_rt_val1 IS NOT NULL THEN
1113 l_ecr1.val := p_rt_val1 ;
1114 l_ecr1.ann_val := p_ann_rt_val1;
1115 ELSE
1116 --Throw error- rates need to be entered
1117 if g_debug then
1118 hr_utility.set_location('BEN_94555_NO_RATE_VAL'|| to_char(p_person_id),54);
1119 end if;
1120 CLOSE c_ecr_enb;
1121 fnd_message.set_name('BEN','BEN_94555_NO_RATE_VAL');
1122 fnd_message.set_token('PACKAGE',l_proc);
1123 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1124 fnd_message.set_token('LER_ID', to_char(p_ler_id));
1125 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1126 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
1127 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
1128 fnd_message.raise_error;
1129 --
1130 END IF;
1131 --
1132 ELSE
1133 null ; --ignore supplied rates.
1134 END IF;
1135 END IF;
1136 --
1137 CLOSE c_ecr_enb;
1138 --
1139 ELSE
1140 --
1141 OPEN c_ecr(p_acty_base_rt_id1);
1142 FETCH c_ecr INTO l_ecr1; -- l_enrt_rt_id1;
1143 IF c_ecr%NOTFOUND THEN
1144 CLOSE c_ecr ;
1145 if g_debug then
1146 hr_utility.set_location('BEN_94535_ECR_NOT_FOUND'|| to_char(p_person_id),54);
1147 end if;
1148 fnd_message.set_name('BEN','BEN_94535_ECR_NOT_FOUND');
1149 fnd_message.set_token('PROC',l_proc);
1150 fnd_message.set_token('PERSON_ID', to_char(p_person_id));
1151 fnd_message.set_token('ABR_ID', to_char(p_acty_base_rt_id1));
1152 fnd_message.set_token('RT_VAL', to_char(p_rt_val1));
1153 fnd_message.set_token('ANN_RT_VAL', to_char(p_ann_rt_val1));
1154 fnd_message.raise_error;
1155 --
1156 ELSE
1157 --
1158 IF l_ecr1.rt_strt_dt_cd = 'ENTRBL' THEN
1159 IF p_rt_strt_dt1 IS NOT NULL THEN
1160 --
1161 l_ecr1.rt_strt_dt := p_rt_strt_dt1 ;
1162 --
1163 ELSE
1164 --Throw Error
1165 if g_debug then
1166 hr_utility.set_location('BEN_94552_NO_RT_STRT_DT'|| to_char(p_person_id),54);
1167 end if;
1168 CLOSE c_ecr;
1169 fnd_message.set_name('BEN','BEN_94552_NO_RT_STRT_DT');
1170 fnd_message.set_token('PACKAGE',l_proc);
1171 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1172 fnd_message.set_token('LER_ID', to_char(p_ler_id));
1173 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1174 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
1175 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
1176 fnd_message.raise_error;
1177 --
1178 END IF;
1179 END IF;
1180 --
1181 IF l_ecr1.entr_val_at_enrt_flag = 'Y' THEN
1182 --
1183 IF p_rt_val1 IS NOT NULL OR p_ann_rt_val1 IS NOT NULL THEN
1184 l_ecr1.val := p_rt_val1 ;
1185 l_ecr1.ann_val := p_ann_rt_val1;
1186 ELSE
1187 --Throw error- rates need to be entered
1188 if g_debug then
1189 hr_utility.set_location('BEN_94555_NO_RATE_VAL'|| to_char(p_person_id),54);
1190 end if;
1191 CLOSE c_ecr;
1192 fnd_message.set_name('BEN','BEN_94555_NO_RATE_VAL');
1193 fnd_message.set_token('PACKAGE',l_proc);
1194 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1195 fnd_message.set_token('LER_ID', to_char(p_ler_id));
1196 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1197 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
1198 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
1199 fnd_message.raise_error;
1200 --
1201 END IF;
1202 --
1203 ELSE
1204 null ; --ignore supplied rates.
1205 END IF;
1206 --
1207 END IF;
1208 --
1209 CLOSE c_ecr;
1210 --
1211 END IF;
1212 --
1213 END IF;
1214 --
1215 IF p_acty_base_rt_id2 IS NOT NULL AND
1216 (p_rt_val2 IS NOT NULL OR p_ann_rt_val2 IS NOT NULL OR p_rt_strt_dt2 IS NOT NULL ) THEN
1217 --
1218 IF l_enb.enrt_bnft_id IS NOT NULL THEN
1219 --
1220 OPEN c_ecr_enb(p_acty_base_rt_id2);
1221 FETCH c_ecr_enb INTO l_ecr2; -- l_enrt_rt_id2;
1222 IF c_ecr_enb%NOTFOUND THEN
1223 CLOSE c_ecr_enb ;
1224 if g_debug then
1225 hr_utility.set_location('BEN_94535_ECR_NOT_FOUND'|| to_char(p_person_id),54);
1226 end if;
1227 fnd_message.set_name('BEN','BEN_94535_ECR_NOT_FOUND');
1228 fnd_message.set_token('PROC',l_proc);
1229 fnd_message.set_token('PERSON_ID', to_char(p_person_id));
1230 fnd_message.set_token('ABR_ID', to_char(p_acty_base_rt_id2));
1231 fnd_message.set_token('RT_VAL', to_char(p_rt_val2));
1232 fnd_message.set_token('ANN_RT_VAL', to_char(p_ann_rt_val2));
1233 fnd_message.raise_error;
1234 --
1235 ELSE
1236 --
1237 IF l_ecr2.rt_strt_dt_cd = 'ENTRBL' THEN
1238 IF p_rt_strt_dt2 IS NOT NULL THEN
1239 --
1240 l_ecr2.rt_strt_dt := p_rt_strt_dt2 ;
1241 --
1242 ELSE
1243 --Throw Error
1244 if g_debug then
1245 hr_utility.set_location('BEN_94552_NO_RT_STRT_DT'|| to_char(p_person_id),54);
1246 end if;
1247 CLOSE c_ecr_enb;
1248 fnd_message.set_name('BEN','BEN_94552_NO_RT_STRT_DT');
1249 fnd_message.set_token('PACKAGE',l_proc);
1250 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1251 fnd_message.set_token('LER_ID', to_char(p_ler_id));
1252 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1253 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
1254 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
1255 fnd_message.raise_error;
1256 --
1257 END IF;
1258 END IF;
1259 --
1260 IF l_ecr2.entr_val_at_enrt_flag = 'Y' THEN
1261 --
1262 IF p_rt_val2 IS NOT NULL OR p_ann_rt_val2 IS NOT NULL THEN
1263 l_ecr2.val := p_rt_val2 ;
1264 l_ecr2.ann_val := p_ann_rt_val2;
1265 ELSE
1266 --Throw error- rates need to be entered
1267 if g_debug then
1268 hr_utility.set_location('BEN_94555_NO_RATE_VAL'|| to_char(p_person_id),54);
1269 end if;
1270 CLOSE c_ecr_enb;
1271 fnd_message.set_name('BEN','BEN_94555_NO_RATE_VAL');
1272 fnd_message.set_token('PACKAGE',l_proc);
1273 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1274 fnd_message.set_token('LER_ID', to_char(p_ler_id));
1275 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1276 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
1277 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
1278 fnd_message.raise_error;
1279 --
1280 END IF;
1281 --
1282 ELSE
1283 null ; --ignore supplied rates.
1284 END IF;
1285 END IF;
1286 --
1287 CLOSE c_ecr_enb;
1288 --
1289 ELSE
1290 --
1291 OPEN c_ecr(p_acty_base_rt_id2);
1292 FETCH c_ecr INTO l_ecr2; -- l_enrt_rt_id2;
1293 IF c_ecr%NOTFOUND THEN
1294 CLOSE c_ecr ;
1295 if g_debug then
1296 hr_utility.set_location('BEN_94535_ECR_NOT_FOUND'|| to_char(p_person_id),54);
1297 end if;
1298 fnd_message.set_name('BEN','BEN_94535_ECR_NOT_FOUND');
1299 fnd_message.set_token('PROC',l_proc);
1300 fnd_message.set_token('PERSON_ID', to_char(p_person_id));
1301 fnd_message.set_token('ABR_ID', to_char(p_acty_base_rt_id2));
1302 fnd_message.set_token('RT_VAL', to_char(p_rt_val2));
1303 fnd_message.set_token('ANN_RT_VAL', to_char(p_ann_rt_val2));
1304 fnd_message.raise_error;
1305 --
1306 ELSE
1307 --
1308 IF l_ecr2.rt_strt_dt_cd = 'ENTRBL' THEN
1309 IF p_rt_strt_dt2 IS NOT NULL THEN
1310 --
1311 l_ecr2.rt_strt_dt := p_rt_strt_dt2 ;
1312 --
1313 ELSE
1314 --Throw Error
1315 if g_debug then
1316 hr_utility.set_location('BEN_94552_NO_RT_STRT_DT'|| to_char(p_person_id),54);
1317 end if;
1318 CLOSE c_ecr;
1319 fnd_message.set_name('BEN','BEN_94552_NO_RT_STRT_DT');
1320 fnd_message.set_token('PACKAGE',l_proc);
1321 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1322 fnd_message.set_token('LER_ID', to_char(p_ler_id));
1323 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1324 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
1325 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
1326 fnd_message.raise_error;
1327 --
1328 END IF;
1329 END IF;
1330 --
1331 IF l_ecr2.entr_val_at_enrt_flag = 'Y' THEN
1332 --
1333 IF p_rt_val2 IS NOT NULL OR p_ann_rt_val2 IS NOT NULL THEN
1334 l_ecr2.val := p_rt_val2 ;
1335 l_ecr2.ann_val := p_ann_rt_val2;
1336 ELSE
1337 --Throw error- rates need to be entered
1338 if g_debug then
1339 hr_utility.set_location('BEN_94555_NO_RATE_VAL'|| to_char(p_person_id),54);
1340 end if;
1341 CLOSE c_ecr;
1342 fnd_message.set_name('BEN','BEN_94555_NO_RATE_VAL');
1343 fnd_message.set_token('PACKAGE',l_proc);
1344 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1345 fnd_message.set_token('LER_ID', to_char(p_ler_id));
1346 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1347 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
1348 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
1349 fnd_message.raise_error;
1350 --
1351 END IF;
1352 --
1353 ELSE
1354 null ; --ignore supplied rates.
1355 END IF;
1356 --
1357 END IF;
1358 --
1359 CLOSE c_ecr;
1360 --
1361 END IF;
1362 --
1363 END IF;
1364 --
1365 IF p_acty_base_rt_id3 IS NOT NULL AND
1366 (p_rt_val3 IS NOT NULL OR p_ann_rt_val3 IS NOT NULL OR p_rt_strt_dt3 IS NOT NULL ) THEN
1367 --
1368 IF l_enb.enrt_bnft_id IS NOT NULL THEN
1369 --
1370 OPEN c_ecr_enb(p_acty_base_rt_id3);
1371 FETCH c_ecr_enb INTO l_ecr3; -- l_enrt_rt_id3;
1372 IF c_ecr_enb%NOTFOUND THEN
1373 CLOSE c_ecr_enb ;
1374 if g_debug then
1375 hr_utility.set_location('BEN_94535_ECR_NOT_FOUND'|| to_char(p_person_id),54);
1376 end if;
1377 fnd_message.set_name('BEN','BEN_94535_ECR_NOT_FOUND');
1378 fnd_message.set_token('PROC',l_proc);
1379 fnd_message.set_token('PERSON_ID', to_char(p_person_id));
1380 fnd_message.set_token('ABR_ID', to_char(p_acty_base_rt_id3));
1381 fnd_message.set_token('RT_VAL', to_char(p_rt_val3));
1382 fnd_message.set_token('ANN_RT_VAL', to_char(p_ann_rt_val3));
1383 fnd_message.raise_error;
1384 --
1385 ELSE
1386 --
1387 IF l_ecr3.rt_strt_dt_cd = 'ENTRBL' THEN
1388 IF p_rt_strt_dt3 IS NOT NULL THEN
1389 --
1390 l_ecr3.rt_strt_dt := p_rt_strt_dt3 ;
1391 --
1392 ELSE
1393 --Throw Error
1394 if g_debug then
1395 hr_utility.set_location('BEN_94552_NO_RT_STRT_DT'|| to_char(p_person_id),54);
1396 end if;
1397 CLOSE c_ecr_enb;
1398 fnd_message.set_name('BEN','BEN_94552_NO_RT_STRT_DT');
1399 fnd_message.set_token('PACKAGE',l_proc);
1400 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1401 fnd_message.set_token('LER_ID', to_char(p_ler_id));
1402 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1403 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
1404 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
1405 fnd_message.raise_error;
1406 --
1407 END IF;
1408 END IF;
1409 --
1410 IF l_ecr3.entr_val_at_enrt_flag = 'Y' THEN
1411 --
1412 IF p_rt_val3 IS NOT NULL OR p_ann_rt_val3 IS NOT NULL THEN
1413 l_ecr3.val := p_rt_val3 ;
1414 l_ecr3.ann_val := p_ann_rt_val3;
1415 ELSE
1416 --Throw error- rates need to be entered
1417 if g_debug then
1418 hr_utility.set_location('BEN_94555_NO_RATE_VAL'|| to_char(p_person_id),54);
1419 end if;
1420 CLOSE c_ecr_enb;
1421 fnd_message.set_name('BEN','BEN_94555_NO_RATE_VAL');
1422 fnd_message.set_token('PACKAGE',l_proc);
1423 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1424 fnd_message.set_token('LER_ID', to_char(p_ler_id));
1425 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1426 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
1427 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
1428 fnd_message.raise_error;
1429 --
1430 END IF;
1431 --
1432 ELSE
1433 null ; --ignore supplied rates.
1434 END IF;
1435 END IF;
1436 --
1437 CLOSE c_ecr_enb;
1438 --
1439 ELSE
1440 --
1441 OPEN c_ecr(p_acty_base_rt_id3);
1442 FETCH c_ecr INTO l_ecr3; -- l_enrt_rt_id3;
1443 IF c_ecr%NOTFOUND THEN
1444 CLOSE c_ecr ;
1445 if g_debug then
1446 hr_utility.set_location('BEN_94535_ECR_NOT_FOUND'|| to_char(p_person_id),54);
1447 end if;
1448 fnd_message.set_name('BEN','BEN_94535_ECR_NOT_FOUND');
1449 fnd_message.set_token('PROC',l_proc);
1450 fnd_message.set_token('PERSON_ID', to_char(p_person_id));
1451 fnd_message.set_token('ABR_ID', to_char(p_acty_base_rt_id3));
1452 fnd_message.set_token('RT_VAL', to_char(p_rt_val3));
1453 fnd_message.set_token('ANN_RT_VAL', to_char(p_ann_rt_val3));
1454 fnd_message.raise_error;
1455 --
1456 ELSE
1457 --
1458 IF l_ecr3.rt_strt_dt_cd = 'ENTRBL' THEN
1459 IF p_rt_strt_dt3 IS NOT NULL THEN
1460 --
1461 l_ecr3.rt_strt_dt := p_rt_strt_dt3 ;
1462 --
1463 ELSE
1464 --Throw Error
1465 if g_debug then
1466 hr_utility.set_location('BEN_94552_NO_RT_STRT_DT'|| to_char(p_person_id),54);
1467 end if;
1468 CLOSE c_ecr;
1469 fnd_message.set_name('BEN','BEN_94552_NO_RT_STRT_DT');
1470 fnd_message.set_token('PACKAGE',l_proc);
1471 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1472 fnd_message.set_token('LER_ID', to_char(p_ler_id));
1473 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1474 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
1475 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
1476 fnd_message.raise_error;
1477 --
1478 END IF;
1479 END IF;
1480 --
1481 IF l_ecr3.entr_val_at_enrt_flag = 'Y' THEN
1482 --
1483 IF p_rt_val3 IS NOT NULL OR p_ann_rt_val3 IS NOT NULL THEN
1484 l_ecr3.val := p_rt_val3 ;
1485 l_ecr3.ann_val := p_ann_rt_val3;
1486 ELSE
1487 --Throw error- rates need to be entered
1488 if g_debug then
1489 hr_utility.set_location('BEN_94555_NO_RATE_VAL'|| to_char(p_person_id),54);
1490 end if;
1491 CLOSE c_ecr;
1492 fnd_message.set_name('BEN','BEN_94555_NO_RATE_VAL');
1493 fnd_message.set_token('PACKAGE',l_proc);
1494 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1495 fnd_message.set_token('LER_ID', to_char(p_ler_id));
1496 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1497 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
1498 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
1499 fnd_message.raise_error;
1500 --
1501 END IF;
1502 --
1503 ELSE
1504 null ; --ignore supplied rates.
1505 END IF;
1506 --
1507 END IF;
1508 --
1509 CLOSE c_ecr;
1510 --
1511 END IF;
1512 --
1513 END IF;
1514 --
1515 IF p_acty_base_rt_id4 IS NOT NULL AND
1516 (p_rt_val4 IS NOT NULL OR p_ann_rt_val4 IS NOT NULL OR p_rt_strt_dt4 IS NOT NULL ) THEN
1517 --
1518 IF l_enb.enrt_bnft_id IS NOT NULL THEN
1519 --
1520 OPEN c_ecr_enb(p_acty_base_rt_id4);
1521 FETCH c_ecr_enb INTO l_ecr4; -- l_enrt_rt_id4;
1522 IF c_ecr_enb%NOTFOUND THEN
1523 CLOSE c_ecr_enb ;
1524 if g_debug then
1525 hr_utility.set_location('BEN_94535_ECR_NOT_FOUND'|| to_char(p_person_id),54);
1526 end if;
1527 fnd_message.set_name('BEN','BEN_94535_ECR_NOT_FOUND');
1528 fnd_message.set_token('PROC',l_proc);
1529 fnd_message.set_token('PERSON_ID', to_char(p_person_id));
1530 fnd_message.set_token('ABR_ID', to_char(p_acty_base_rt_id4));
1531 fnd_message.set_token('RT_VAL', to_char(p_rt_val4));
1532 fnd_message.set_token('ANN_RT_VAL', to_char(p_ann_rt_val4));
1533 fnd_message.raise_error;
1534 --
1535 ELSE
1536 --
1537 IF l_ecr4.rt_strt_dt_cd = 'ENTRBL' THEN
1538 IF p_rt_strt_dt4 IS NOT NULL THEN
1539 --
1540 l_ecr4.rt_strt_dt := p_rt_strt_dt4 ;
1541 --
1542 ELSE
1543 --Throw Error
1544 if g_debug then
1545 hr_utility.set_location('BEN_94552_NO_RT_STRT_DT'|| to_char(p_person_id),54);
1546 end if;
1547 CLOSE c_ecr_enb;
1548 fnd_message.set_name('BEN','BEN_94552_NO_RT_STRT_DT');
1549 fnd_message.set_token('PACKAGE',l_proc);
1550 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1551 fnd_message.set_token('LER_ID', to_char(p_ler_id));
1552 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1553 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
1554 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
1555 fnd_message.raise_error;
1556 --
1557 END IF;
1558 END IF;
1559 --
1560 IF l_ecr4.entr_val_at_enrt_flag = 'Y' THEN
1561 --
1562 IF p_rt_val4 IS NOT NULL OR p_ann_rt_val4 IS NOT NULL THEN
1563 l_ecr4.val := p_rt_val4 ;
1564 l_ecr4.ann_val := p_ann_rt_val4;
1565 ELSE
1566 --Throw error- rates need to be entered
1567 if g_debug then
1568 hr_utility.set_location('BEN_94555_NO_RATE_VAL'|| to_char(p_person_id),54);
1569 end if;
1570 CLOSE c_ecr_enb;
1571 fnd_message.set_name('BEN','BEN_94555_NO_RATE_VAL');
1572 fnd_message.set_token('PACKAGE',l_proc);
1573 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1574 fnd_message.set_token('LER_ID', to_char(p_ler_id));
1575 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1576 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
1577 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
1578 fnd_message.raise_error;
1579 --
1580 END IF;
1581 --
1582 ELSE
1583 null ; --ignore supplied rates.
1584 END IF;
1585 END IF;
1586 --
1587 CLOSE c_ecr_enb;
1588 --
1589 ELSE
1590 --
1591 OPEN c_ecr(p_acty_base_rt_id4);
1592 FETCH c_ecr INTO l_ecr4; -- l_enrt_rt_id4;
1593 IF c_ecr%NOTFOUND THEN
1594 CLOSE c_ecr ;
1595 if g_debug then
1596 hr_utility.set_location('BEN_94535_ECR_NOT_FOUND'|| to_char(p_person_id),54);
1597 end if;
1598 fnd_message.set_name('BEN','BEN_94535_ECR_NOT_FOUND');
1599 fnd_message.set_token('PROC',l_proc);
1600 fnd_message.set_token('PERSON_ID', to_char(p_person_id));
1601 fnd_message.set_token('ABR_ID', to_char(p_acty_base_rt_id4));
1602 fnd_message.set_token('RT_VAL', to_char(p_rt_val4));
1603 fnd_message.set_token('ANN_RT_VAL', to_char(p_ann_rt_val4));
1604 fnd_message.raise_error;
1605 --
1606 ELSE
1607 --
1608 IF l_ecr4.rt_strt_dt_cd = 'ENTRBL' THEN
1609 IF p_rt_strt_dt4 IS NOT NULL THEN
1610 --
1611 l_ecr4.rt_strt_dt := p_rt_strt_dt4 ;
1612 --
1613 ELSE
1614 --Throw Error
1615 if g_debug then
1616 hr_utility.set_location('BEN_94552_NO_RT_STRT_DT'|| to_char(p_person_id),54);
1617 end if;
1618 CLOSE c_ecr;
1619 fnd_message.set_name('BEN','BEN_94552_NO_RT_STRT_DT');
1620 fnd_message.set_token('PACKAGE',l_proc);
1621 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1622 fnd_message.set_token('LER_ID', to_char(p_ler_id));
1623 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1624 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
1625 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
1626 fnd_message.raise_error;
1627 --
1628 END IF;
1629 END IF;
1630 --
1631 IF l_ecr4.entr_val_at_enrt_flag = 'Y' THEN
1632 --
1633 IF p_rt_val4 IS NOT NULL OR p_ann_rt_val4 IS NOT NULL THEN
1634 l_ecr4.val := p_rt_val4 ;
1635 l_ecr4.ann_val := p_ann_rt_val4;
1636 ELSE
1637 --Throw error- rates need to be entered
1638 if g_debug then
1639 hr_utility.set_location('BEN_94555_NO_RATE_VAL'|| to_char(p_person_id),54);
1640 end if;
1641 CLOSE c_ecr;
1642 fnd_message.set_name('BEN','BEN_94555_NO_RATE_VAL');
1643 fnd_message.set_token('PACKAGE',l_proc);
1644 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1645 fnd_message.set_token('LER_ID', to_char(p_ler_id));
1646 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1647 fnd_message.set_token('PLAN_ID', to_char(p_pl_id));
1648 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
1649 fnd_message.raise_error;
1650 --
1651 END IF;
1652 --
1653 ELSE
1654 null ; --ignore supplied rates.
1655 END IF;
1656 --
1657 END IF;
1658 --
1659 CLOSE c_ecr;
1660 --
1661 END IF;
1662 --
1663 END IF;
1664 --
1665 -- WAENT --1 Prior or Enterable
1666 -- ENTRBL - Enterable -- Start date
1667 --
1668 ben_election_information.election_information
1669 (p_validate => p_validate
1670 ,p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id
1671 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
1672 ,p_effective_date => p_effective_date
1673 ,p_enrt_mthd_cd => 'E'
1674 ,p_enrt_bnft_id => l_enb.enrt_bnft_id
1675 ,p_bnft_val => l_enb.val
1676 ,p_enrt_cvg_strt_dt => l_epe.enrt_cvg_strt_dt
1677 ,p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt
1678 ,p_enrt_rt_id1 => l_ecr1.enrt_rt_id
1679 ,p_prtt_rt_val_id1 => l_prtt_rt_val_id1
1680 ,p_rt_val1 => l_ecr1.val
1681 ,p_ann_rt_val1 => l_ecr1.ann_val
1682 ,p_rt_strt_dt1 => p_rt_strt_dt1
1683 ,p_rt_end_dt1 => p_rt_end_dt1
1684 ,p_enrt_rt_id2 => l_ecr2.enrt_rt_id
1685 ,p_prtt_rt_val_id2 => l_prtt_rt_val_id2
1686 ,p_rt_val2 => l_ecr2.val
1687 ,p_ann_rt_val2 => l_ecr2.ann_val
1688 ,p_rt_strt_dt2 => p_rt_strt_dt2
1689 ,p_rt_end_dt2 => p_rt_end_dt2
1690 ,p_enrt_rt_id3 => l_ecr3.enrt_rt_id
1691 ,p_prtt_rt_val_id3 => l_prtt_rt_val_id3
1692 ,p_rt_val3 => l_ecr3.val
1693 ,p_ann_rt_val3 => l_ecr3.ann_val
1694 ,p_rt_strt_dt3 => p_rt_strt_dt3
1695 ,p_rt_end_dt3 => p_rt_end_dt3
1696 ,p_enrt_rt_id4 => l_ecr4.enrt_rt_id
1697 ,p_prtt_rt_val_id4 => l_prtt_rt_val_id4
1698 ,p_rt_val4 => l_ecr4.val
1699 ,p_ann_rt_val4 => l_ecr4.ann_val
1700 ,p_rt_strt_dt4 => p_rt_strt_dt4
1701 ,p_rt_end_dt4 => p_rt_end_dt4
1702 ,p_prtt_rt_val_id5 => l_prtt_rt_val_id5
1703 ,p_prtt_rt_val_id6 => l_prtt_rt_val_id6
1704 ,p_prtt_rt_val_id7 => l_prtt_rt_val_id7
1705 ,p_prtt_rt_val_id8 => l_prtt_rt_val_id8
1706 ,p_prtt_rt_val_id9 => l_prtt_rt_val_id9
1707 ,p_prtt_rt_val_id10 => l_prtt_rt_val_id10
1708 ,p_datetrack_mode => l_datetrack_mode
1709 ,p_suspend_flag => l_suspend_flag
1710 ,p_called_from_sspnd => 'N'
1711 ,p_effective_start_date => l_effective_start_date
1712 ,p_effective_end_date => l_effective_end_date
1713 ,p_object_version_number => l_object_version_number
1714 ,p_prtt_enrt_interim_id => l_prtt_enrt_interim_id
1715 ,p_business_group_id => p_business_group_id
1716 ,p_dpnt_actn_warning => l_dpnt_actn_warning
1717 ,p_bnf_actn_warning => l_bnf_actn_warning
1718 ,p_ctfn_actn_warning => l_ctfn_actn_warning);
1719 --
1720 --Set the Original Coverage Start Date
1721 IF p_orgnl_enrt_dt IS NOT NULL and l_prtt_enrt_rslt_id IS NOT NULL THEN
1722 --
1723 update ben_prtt_enrt_rslt_f
1724 set ORGNL_ENRT_DT = p_orgnl_enrt_dt
1725 where prtt_enrt_rslt_id = l_prtt_enrt_rslt_id ;
1726 --
1727 END IF;
1728 --
1729 else --De Enroll from plan (dax)
1730 --
1731 --Get Ended Enrollment Result
1732 --
1733 get_current_pen
1734 (p_effective_date => p_effective_date,
1735 p_life_event_date => p_life_event_date,
1736 p_person_id => p_person_id,
1737 p_pgm_id => p_pgm_id,
1738 p_pl_id => p_ended_pl_id,
1739 p_opt_id => p_ended_opt_id,
1740 p_bnft_val => p_ended_bnft_val,
1741 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id,
1742 p_object_version_number => l_object_version_number
1743 );
1744 --
1745 if l_prtt_enrt_rslt_id is null then
1746 --
1747 --Check for already ended enrollments
1748 --
1749 get_ended_pen
1750 (p_effective_date => p_effective_date,
1751 p_life_event_date => p_life_event_date,
1752 p_person_id => p_person_id,
1753 p_pgm_id => p_pgm_id,
1754 p_pl_id => p_ended_pl_id,
1755 p_opt_id => p_ended_opt_id,
1756 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
1757 );
1758 --
1759 if l_prtt_enrt_rslt_id is not null then
1760 --
1761 --Raise error saying that enrollment has already been ended.
1762 --
1763 fnd_message.set_name('BEN','BEN_94658_PLN_ALRDY_DE_ENRLD');
1764 fnd_message.set_token('PACKAGE',l_proc);
1765 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1766 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1767 fnd_message.set_token('PL_ID', to_char(p_ended_pl_id));
1768 fnd_message.set_token('OPT_ID',to_char(p_ended_opt_id));
1769 fnd_message.set_token('PER_ENR_RSLT_ID',to_char(l_prtt_enrt_rslt_id));
1770 fnd_message.set_token('LE_DATE', p_life_event_date);
1771 fnd_message.raise_error;
1772 --
1773 else
1774 --
1775 --No Enrollment open/ended were found. Invalid data entered. Raise error
1776 --
1777 fnd_message.set_name('BEN','BEN_94659_NO_PLN_FOR_DE_ENRL');
1778 fnd_message.set_token('PACKAGE',l_proc);
1779 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1780 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1781 fnd_message.set_token('PL_ID', to_char(p_ended_pl_id));
1782 fnd_message.set_token('OPT_ID',to_char(p_ended_opt_id));
1783 fnd_message.set_token('LE_DATE', p_life_event_date);
1784 fnd_message.raise_error;
1785 --
1786 end if;
1787 --
1788 end if;
1789 --
1790 --Proceed with de-enrolling from the specified plan
1791 --
1792 fnd_file.put_line
1793 (which => fnd_file.log,
1794 buff => 'l_prtt_enrt_rslt_id '||l_prtt_enrt_rslt_id);
1795 --
1796 ben_prtt_enrt_result_api.delete_enrollment
1797 (p_validate => p_validate
1798 ,p_per_in_ler_id => l_pil.per_in_ler_id
1799 ,p_lee_rsn_id => null --?
1800 ,p_enrt_perd_id => null --?
1801 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
1802 ,p_business_group_id => p_business_group_id
1803 ,p_effective_start_date => l_effective_start_date
1804 ,p_effective_end_date => l_effective_end_date
1805 ,p_object_version_number => l_object_version_number
1806 ,p_effective_date => p_effective_date
1807 ,p_datetrack_mode => l_datetrack_mode
1808 ,p_multi_row_validate => false --??
1809 ,p_source => null --?
1810 ,p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt --?
1811 ,p_mode => null); --?
1812 --
1813 end if; --End of if(l_enroll_flag)
1814 --
1815 hr_utility.set_location('Leaving:'||l_proc, 20);
1816 --
1817 exception when others then
1818 raise ;
1819 end enrollment_information_detail;
1820 --
1821 -- --------------------------------------------------------------------------------
1822 -- |-----------------------------< ELECTION_INFORMATION >-------------------------|
1823 -- -------------------------------------------------------------------------------+
1824 --
1825 procedure post_enrollment
1826 (p_validate in boolean default false
1827 ,p_person_id in number
1828 ,p_ler_id in number
1829 ,p_life_event_date in date
1830 ,p_pgm_id in number default null
1831 ,p_pl_id in number default null
1832 -- ,p_flx_cr_flag in varchar2 default 'N'
1833 ,p_proc_cd in varchar2 default null
1834 ,p_business_group_id in number
1835 ,p_effective_date in date ) is
1836 --
1837 l_proc varchar2(60) := 'ben_process_enrollment.post_enrollment';
1838 --
1839 cursor c_pgm is
1840 select pgm_typ_cd
1841 from ben_pgm_f pgm
1842 where pgm.pgm_id = p_pgm_id
1843 and p_life_event_date between pgm.effective_start_date
1844 and pgm.effective_end_date ;
1845 --
1846 cursor c_pil is
1847 select pil.per_in_ler_id
1848 from ben_per_in_ler pil
1849 where pil.ler_id =p_ler_id
1850 and pil.person_id = p_person_id
1851 and pil.lf_evt_ocrd_dt = p_life_event_date
1852 and pil.per_in_ler_stat_cd = 'STRTD' ;
1853 --
1854 l_per_in_ler_id NUMBER(15);
1855 l_pgm_typ_cd hr_lookups.lookup_code%TYPE;
1856 l_flx_cr_flag VARCHAR2(30) := 'N';
1857 --
1858 begin
1859 --
1860 hr_utility.set_location('Entering:'||l_proc, 20);
1861 --
1862 IF p_pgm_id IS NOT NULL THEN
1863 OPEN c_pgm ;
1864 FETCH c_pgm INTO l_pgm_typ_cd ;
1865 CLOSE c_pgm;
1866 --
1867 IF l_pgm_typ_cd in ('FLEX','FPC') THEN
1868 --
1869 l_flx_cr_flag := 'Y';
1870 --
1871 END IF;
1872 --
1873 END IF;
1874 --
1875 OPEN c_pil;
1876 FETCH c_pil INTO l_per_in_ler_id;
1877 --
1878 IF c_pil%NOTFOUND THEN
1879 CLOSE c_pil ;
1880 --
1881 if g_debug then
1882 hr_utility.set_location('BEN_94534_PIL_NOT_FOUND'|| to_char(p_person_id),54);
1883 end if;
1884 fnd_message.set_name('BEN','BEN_94534_PIL_NOT_FOUND');
1885 fnd_message.set_token('PROC',l_proc);
1886 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1887 fnd_message.set_token('LER_ID',to_char(p_ler_id));
1888 fnd_message.set_token('LE_DATE', p_life_event_date);
1889 fnd_message.raise_error;
1890 --
1891 END IF;
1892 --
1893 CLOSE c_pil;
1894 --
1895 ben_proc_common_enrt_rslt.set_elcn_made_or_asnd_dt
1896 (p_validate => p_validate
1897 ,p_per_in_ler_id => l_per_in_ler_id
1898 ,p_pgm_id => p_pgm_id
1899 ,p_pl_id => p_pl_id
1900 ,p_enrt_mthd_cd => 'E'
1901 ,p_business_group_id => p_business_group_id
1902 ,p_effective_date => p_effective_date);
1903 --
1904 ben_prtt_enrt_result_api.multi_rows_edit
1905 (p_person_id => p_person_id
1906 ,p_per_in_ler_id => l_per_in_ler_id
1907 ,p_pgm_id => p_pgm_id
1908 ,p_business_group_id => p_business_group_id
1909 ,p_effective_date => p_effective_date);
1910 --
1911 ben_proc_common_enrt_rslt.process_post_results
1912 (p_validate => p_validate
1913 ,p_person_id => p_person_id
1914 ,p_per_in_ler_id => l_per_in_ler_id
1915 ,p_flx_cr_flag => l_flx_cr_flag
1916 ,p_enrt_mthd_cd => 'E'
1917 ,p_business_group_id => p_business_group_id
1918 ,p_effective_date => p_effective_date
1919 ,p_self_service_flag => false
1920 ,p_pgm_id => p_pgm_id
1921 ,p_pl_id => p_pl_id);
1922 --
1923 ben_proc_common_enrt_rslt.process_post_enrollment
1924 (p_validate => p_validate
1925 ,p_person_id => p_person_id
1926 ,p_per_in_ler_id => l_per_in_ler_id
1927 ,p_pgm_id => p_pgm_id
1928 ,p_pl_id => p_pl_id
1929 ,p_enrt_mthd_cd => 'E'
1930 ,p_cls_enrt_flag => false
1931 ,p_proc_cd => p_proc_cd
1932 ,p_business_group_id => p_business_group_id
1933 ,p_effective_date => p_effective_date);
1934 --
1935 hr_utility.set_location('Leaving:'||l_proc, 20);
1936 --
1937 exception when others then
1938 raise ;
1939 end post_enrollment ;
1940 --
1941 procedure create_enrollment
1942 (p_validate in boolean default false
1943 ,p_pgm_id in number default null
1944 ,p_pl_id in number default null
1945 ,p_opt_id in number default null
1946 ,p_ler_id in number
1947 ,p_life_event_date in date
1948 ,p_ended_pl_id in number default null
1949 ,p_ended_opt_id in number default null
1950 ,p_ended_bnft_val in number default null
1951 ,p_effective_date in date
1952 ,p_person_id in number
1953 ,p_bnft_val in number default null
1954 ,p_acty_base_rt_id1 in number default null
1955 ,p_rt_val1 in number default null
1956 ,p_ann_rt_val1 in number default null
1957 ,p_rt_strt_dt1 in date default null
1958 ,p_rt_end_dt1 in date default null
1959 ,p_acty_base_rt_id2 in number default null
1960 ,p_rt_val2 in number default null
1961 ,p_ann_rt_val2 in number default null
1962 ,p_rt_strt_dt2 in date default null
1963 ,p_rt_end_dt2 in date default null
1964 ,p_acty_base_rt_id3 in number default null
1965 ,p_rt_val3 in number default null
1966 ,p_ann_rt_val3 in number default null
1967 ,p_rt_strt_dt3 in date default null
1968 ,p_rt_end_dt3 in date default null
1969 ,p_acty_base_rt_id4 in number default null
1970 ,p_rt_val4 in number default null
1971 ,p_ann_rt_val4 in number default null
1972 ,p_rt_strt_dt4 in date default null
1973 ,p_rt_end_dt4 in date default null
1974 ,p_business_group_id in number
1975 ,p_enrt_cvg_strt_dt in date default null
1976 ,p_enrt_cvg_thru_dt in date default null
1977 ,p_orgnl_enrt_dt in date default null
1978 ,p_proc_cd in varchar2 default null
1979 ,p_record_typ_cd in varchar2 ) IS
1980 --
1981 l_proc varchar2(60) := 'ben_enrollment_process.create_enrollment' ;
1982 --
1983 begin
1984 --
1985 hr_utility.set_location('Entering:'||l_proc||':'||p_record_typ_cd, 20);
1986 --Bug 5259118
1987 -- IF fnd_global.conc_request_id = -1 THEN
1988 --
1989 ben_env_object.init(p_business_group_id => p_business_group_id,
1990 p_effective_date => p_effective_date,
1991 p_thread_id => 1,
1992 p_chunk_size => 1,
1993 p_threads => 1,
1994 p_max_errors => 1,
1995 p_benefit_action_id => null);
1996 --
1997 -- END IF;
1998 --
1999 IF upper(p_record_typ_cd) = 'ENROLL' THEN
2000 --
2001 enrollment_information_detail
2002 (p_validate => p_validate
2003 ,p_pgm_id => p_pgm_id
2004 ,p_pl_id => p_pl_id
2005 ,p_opt_id => p_opt_id
2006 ,p_ler_id => p_ler_id
2007 ,p_life_event_date => p_life_event_date
2008 ,p_ended_pl_id => p_ended_pl_id
2009 ,p_ended_opt_id => p_ended_opt_id
2010 ,p_effective_date => p_effective_date
2011 ,p_person_id => p_person_id
2012 ,p_bnft_val => p_bnft_val
2013 ,p_acty_base_rt_id1 => p_acty_base_rt_id1
2014 ,p_rt_val1 => p_rt_val1
2015 ,p_ann_rt_val1 => p_ann_rt_val1
2016 ,p_rt_strt_dt1 => p_rt_strt_dt1
2017 ,p_rt_end_dt1 => p_rt_end_dt1
2018 ,p_acty_base_rt_id2 => p_acty_base_rt_id2
2019 ,p_rt_val2 => p_rt_val2
2020 ,p_ann_rt_val2 => p_ann_rt_val2
2021 ,p_rt_strt_dt2 => p_rt_strt_dt2
2022 ,p_rt_end_dt2 => p_rt_end_dt2
2023 ,p_acty_base_rt_id3 => p_acty_base_rt_id3
2024 ,p_rt_val3 => p_rt_val3
2025 ,p_ann_rt_val3 => p_ann_rt_val3
2026 ,p_rt_strt_dt3 => p_rt_strt_dt3
2027 ,p_rt_end_dt3 => p_rt_end_dt3
2028 ,p_acty_base_rt_id4 => p_acty_base_rt_id4
2029 ,p_rt_val4 => p_rt_val4
2030 ,p_ann_rt_val4 => p_ann_rt_val4
2031 ,p_rt_strt_dt4 => p_rt_strt_dt4
2032 ,p_rt_end_dt4 => p_rt_end_dt4
2033 ,p_business_group_id => p_business_group_id
2034 ,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
2035 ,p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt
2036 ,p_orgnl_enrt_dt => p_orgnl_enrt_dt
2037 );
2038 --
2039 ELSIF upper(p_record_typ_cd) = 'POST' THEN
2040 --
2041 --NK
2042 --Changes to eliminate summary row in Enrollment Upload Spreadsheet.
2043 --If the record type is POST, this is the last record of the group
2044 --So, first do the enrollment and then run the post enrollment process
2045 enrollment_information_detail
2046 (p_validate => p_validate
2047 ,p_pgm_id => p_pgm_id
2048 ,p_pl_id => p_pl_id
2049 ,p_opt_id => p_opt_id
2050 ,p_ler_id => p_ler_id
2051 ,p_life_event_date => p_life_event_date
2052 ,p_ended_pl_id => p_ended_pl_id
2053 ,p_ended_opt_id => p_ended_opt_id
2054 ,p_effective_date => p_effective_date
2055 ,p_person_id => p_person_id
2056 ,p_bnft_val => p_bnft_val
2057 ,p_acty_base_rt_id1 => p_acty_base_rt_id1
2058 ,p_rt_val1 => p_rt_val1
2059 ,p_ann_rt_val1 => p_ann_rt_val1
2060 ,p_rt_strt_dt1 => p_rt_strt_dt1
2061 ,p_rt_end_dt1 => p_rt_end_dt1
2062 ,p_acty_base_rt_id2 => p_acty_base_rt_id2
2063 ,p_rt_val2 => p_rt_val2
2064 ,p_ann_rt_val2 => p_ann_rt_val2
2065 ,p_rt_strt_dt2 => p_rt_strt_dt2
2066 ,p_rt_end_dt2 => p_rt_end_dt2
2067 ,p_acty_base_rt_id3 => p_acty_base_rt_id3
2068 ,p_rt_val3 => p_rt_val3
2069 ,p_ann_rt_val3 => p_ann_rt_val3
2070 ,p_rt_strt_dt3 => p_rt_strt_dt3
2071 ,p_rt_end_dt3 => p_rt_end_dt3
2072 ,p_acty_base_rt_id4 => p_acty_base_rt_id4
2073 ,p_rt_val4 => p_rt_val4
2074 ,p_ann_rt_val4 => p_ann_rt_val4
2075 ,p_rt_strt_dt4 => p_rt_strt_dt4
2076 ,p_rt_end_dt4 => p_rt_end_dt4
2077 ,p_business_group_id => p_business_group_id
2078 ,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
2079 ,p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt
2080 ,p_orgnl_enrt_dt => p_orgnl_enrt_dt
2081 );
2082 --
2083 post_enrollment
2084 (p_validate => p_validate
2085 ,p_person_id => p_person_id
2086 ,p_ler_id => p_ler_id
2087 ,p_life_event_date => p_life_event_date
2088 ,p_pgm_id => p_pgm_id
2089 ,p_pl_id => p_pl_id
2090 ,p_proc_cd => p_proc_cd
2091 ,p_business_group_id => p_business_group_id
2092 ,p_effective_date => p_effective_date );
2093 --
2094 ELSE
2095 --
2096 if g_debug then
2097 hr_utility.set_location('BEN_94536_RECORD_TYPE_ERROR'|| to_char(p_person_id),54);
2098 end if;
2099 fnd_message.set_name('BEN','BEN_94536_RECORD_TYPE_ERROR');
2100 fnd_message.set_token('PROC',l_proc);
2101 fnd_message.set_token('PERSON_ID',p_record_typ_cd);
2102 fnd_message.raise_error;
2103 --
2104 END IF;
2105 --
2106 exception when others then
2107 raise ;
2108 end create_enrollment ;
2109 --
2110 procedure process_dependent
2111 (p_validate in boolean default false
2112 ,p_person_id in number
2113 ,p_pgm_id in number default null
2114 ,p_pl_id in number default null
2115 ,p_opt_id in number default null
2116 ,p_ler_id in number
2117 ,p_life_event_date in date
2118 ,p_effective_date in date
2119 ,p_contact_person_id in number
2120 ,p_business_group_id in number
2121 ,p_cvg_strt_dt in date default null
2122 ,p_cvg_thru_dt in date default null
2123 ,p_multi_row_actn in boolean default false
2124 ,p_record_typ_cd in varchar2 ) is
2125 --
2126 l_proc varchar2(60) := 'ben_enrollment_process.process_dependent';
2127 --
2128 l_pil c_pil%ROWTYPE;
2129 --
2130 l_epe c_epe_oipl%ROWTYPE;
2131 l_egd c_egd%ROWTYPE;
2132 l_dt_mode varchar2(30) := hr_api.g_update; --5675220
2133 l_elig_cvrd_dpnt_id number;
2134 l_dummy_date date;
2135 l_ovn number;
2136 l_person_id number := p_person_id;
2137 l_pgm_id number := p_pgm_id;
2138 l_pl_id number := p_pl_id;
2139 l_opt_id number := p_opt_id;
2140 l_ler_id number := p_ler_id;
2141 l_life_event_date date := p_life_event_date;
2142 l_contact_person_id number := p_contact_person_id;
2143 l_cvg_strt_dt date := p_cvg_strt_dt;
2144 l_pd_cvg_strt_dt date ;
2145 l_cvg_thru_dt date := p_cvg_thru_dt;
2146 l_record_typ_cd varchar2(30) := p_record_typ_cd;
2147 l_returned_strt_dt date ;
2148 l_returned_end_dt date ;
2149
2150 l_cvg_strt_dt_dummy date;
2151 l_cvg_end_dt_dummy date;
2152
2153 --
2154 begin
2155 --
2156 hr_utility.set_location('Entering:'||l_proc, 20);
2157 --
2158 --Bug 5259118
2159 -- IF fnd_global.conc_request_id = -1 THEN
2160 --
2161 ben_env_object.init(p_business_group_id => p_business_group_id,
2162 p_effective_date => p_effective_date,
2163 p_thread_id => 1,
2164 p_chunk_size => 1,
2165 p_threads => 1,
2166 p_max_errors => 1,
2167 p_benefit_action_id => null);
2168 --
2169 -- END IF;
2170 --
2171 IF l_person_id = hr_api.g_number THEN
2172 l_person_id := null;
2173 END IF;
2174 --
2175 IF l_pgm_id = hr_api.g_number THEN
2176 l_pgm_id := null;
2177 END IF;
2178 --
2179 IF l_pl_id = hr_api.g_number THEN
2180 l_pl_id := null;
2181 END IF;
2182 --
2183 IF l_opt_id = hr_api.g_number THEN
2184 l_opt_id := null;
2185 END IF;
2186 --
2187 IF l_ler_id = hr_api.g_number THEN
2188 l_ler_id := null;
2189 END IF;
2190 --
2191 IF l_life_event_date = hr_api.g_date THEN
2192 l_life_event_date := null;
2193 END IF;
2194 --
2195 IF l_contact_person_id = hr_api.g_number THEN
2196 l_contact_person_id := null;
2197 END IF;
2198 --
2199 IF l_cvg_strt_dt = hr_api.g_date THEN
2200 l_cvg_strt_dt := null;
2201 END IF;
2202 --
2203 IF l_cvg_thru_dt = hr_api.g_date THEN
2204 l_cvg_thru_dt := null;
2205 END IF;
2206 --
2207 IF l_record_typ_cd = hr_api.g_varchar2 THEN
2208 l_record_typ_cd := 'ENROLL' ;
2209 END IF;
2210 --
2211 fnd_file.put_line
2212 (which => fnd_file.log,
2213 buff => 'Entering - BEN_ENROLLMENT_PROCESS.PROCESS_DEPENDENT' );
2214 fnd_file.put_line
2215 (which => fnd_file.log,
2216 buff => 'l_person_id '||l_person_id||' l_pgm_id '||l_pgm_id||' l_pl_id '||l_pl_id);
2217 --
2218 fnd_file.put_line
2219 (which => fnd_file.log,
2220 buff => 'l_opt_id '||l_opt_id||' l_ler_id '||l_ler_id);
2221 fnd_file.put_line
2222 (which => fnd_file.log,
2223 buff => 'l_record_typ_cd '||l_record_typ_cd);
2224 fnd_file.put_line
2225 (which => fnd_file.log,
2226 buff => 'l_life_event_date '||l_life_event_date||' l_contact_person_id '||l_contact_person_id);
2227 fnd_file.put_line
2228 (which => fnd_file.log,
2229 buff => 'l_cvg_strt_dt '||l_cvg_strt_dt||' l_cvg_thru_dt '||l_cvg_thru_dt);
2230 --
2231 get_pil
2232 (p_person_id => l_person_id,
2233 p_ler_id => l_ler_id,
2234 p_life_event_date => l_life_event_date,
2235 p_pil => l_pil
2236 );
2237 --
2238 fnd_file.put_line
2239 (which => fnd_file.log,
2240 buff => 'l_pil.per_in_ler_id '||l_pil.per_in_ler_id);
2241 --Get EPE
2242 IF l_pil.per_in_ler_id IS NOT NULL THEN
2243 get_epe
2244 (p_person_id => l_person_id,
2245 p_per_in_ler_id => l_pil.per_in_ler_id,
2246 p_life_event_date => l_life_event_date,
2247 p_opt_id => l_opt_id,
2248 p_pl_id => l_pl_id,
2249 p_pgm_id => l_pgm_id,
2250 p_epe => l_epe
2251 ) ;
2252 fnd_file.put_line
2253 (which => fnd_file.log,
2254 buff => 'l_epe.elig_per_elctbl_chc_id '||l_epe.elig_per_elctbl_chc_id);
2255 --
2256 --Find if there is a valid enrollment result exists for this epe and pil
2257 --If not throw error.
2258 --
2259 check_pen
2260 (p_per_in_ler_id => l_pil.per_in_ler_id,
2261 p_prtt_enrt_rslt_id => l_epe.prtt_enrt_rslt_id );
2262 --
2263 --You can't designate a person if the person is not enrolled in this plan
2264 --or option in plan.
2265 --
2266 --
2267 IF l_epe.elig_per_elctbl_chc_id IS NOT NULL THEN
2268 --
2269 /*
2270 --Now Get the Depdenent Coverage Start Date derived.
2271 if l_EPE.DPNT_CVG_STRT_DT_CD is not null then
2272 --
2273 --
2274 ben_determine_date.main
2275 (p_cache_mode => false
2276 ,p_date_cd => l_epe.dpnt_cvg_strt_dt_cd
2277 ,p_per_in_ler_id => l_pil.per_in_ler_id
2278 ,p_person_id => p_person_id
2279 ,p_pgm_id => l_epe.pgm_id
2280 ,p_pl_id => l_epe.pl_id
2281 ,p_oipl_id => l_epe.oipl_id
2282 ,p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id
2283 ,p_business_group_id => p_business_group_id
2284 ,p_formula_id => l_epe.dpnt_cvg_strt_dt_rl
2285 ,p_acty_base_rt_id => null
2286 ,p_bnfts_bal_id => null
2287 ,p_effective_date => p_effective_date
2288 ,p_lf_evt_ocrd_dt =>null
2289 ,p_returned_date => l_pd_cvg_strt_dt
2290 ,p_start_date => null
2291 ,p_param1 => 'CON_PERSON_ID'
2292 ,p_param1_value => p_contact_person_id
2293 ,p_parent_person_id=>null
2294 ,p_enrt_cvg_end_dt =>null
2295 ,p_comp_obj_mode =>true
2296 ,p_fonm_cvg_strt_dt =>null
2297 ,p_fonm_rt_strt_dt =>null
2298 -- ,p_cmpltd_dt =>null
2299 );
2300 --
2301 end if;
2302 */
2303 ben_prtt_enrt_result_api.calc_dpnt_cvg_dt(
2304 p_calc_end_dt => true,
2305 P_calc_strt_dt => true,
2306 P_per_in_ler_id => l_pil.per_in_ler_id,
2307 p_person_id => l_person_id,
2308 p_pgm_id => l_pgm_id,
2309 p_pl_id => l_pl_id,
2310 p_oipl_id => l_epe.oipl_id,
2311 p_ptip_id => l_epe.ptip_id,
2312 p_ler_id => l_ler_id,
2313 p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id,
2314 p_business_group_id => p_business_group_id,
2315 p_effective_date => p_effective_date,
2316 p_enrt_cvg_end_dt => null,
2317 p_returned_strt_dt => l_returned_strt_dt,
2318 p_returned_end_dt => l_returned_end_dt);
2319 --
2320
2321 /* Commented for Bug 10209116
2322 IF l_cvg_thru_dt IS NULL THEN
2323 l_returned_end_dt := NULL;
2324 ELSIF l_cvg_thru_dt <> l_returned_end_dt THEN
2325 --Make entry in log file indicating mismatch between the supplied and plan design Coverge Through Date.
2326 fnd_file.put_line
2327 (which => fnd_file.log,
2328 buff => 'Mismatch found between supplied and plan design Dependant Coverage Through Date. Using the date in Plan Design: '||l_returned_end_dt);
2329 NULL;
2330 end if;
2331 --
2332 IF l_cvg_strt_dt is NULL THEN
2333 NULL;
2334 ELSIF l_cvg_strt_dt <> l_returned_strt_dt THEN
2335 IF l_cvg_thru_dt IS NULL THEN --Check for thru date null again,so that start date warning is printed only when enrolling dependants.
2336 --Make entry in log file indicating mismatch between the supplied and plan design Coverge Start Date.
2337 fnd_file.put_line
2338 (which => fnd_file.log,
2339 buff => 'Mismatch found between supplied and plan design Dependant Coverage Start Date. Using the date in Plan Design: '||l_returned_strt_dt);
2340 END IF;
2341 END IF;
2342 */
2343 /* Added for Bug 10209116: Coverage start and end dates should be taken
2344 from the worksheet used for uploading the Dependent elections if the date values are not NULL*/
2345 if(l_cvg_strt_dt is NOT NULL and l_cvg_thru_dt is not NULL) then
2346 hr_utility.set_location('Condition 1', 20);
2347 l_cvg_strt_dt_dummy := l_cvg_strt_dt;
2348 l_cvg_end_dt_dummy := l_cvg_thru_dt;
2349 elsif(l_cvg_strt_dt is NOT NULL and l_cvg_thru_dt is NULL) then
2350 hr_utility.set_location('Condition 2', 20);
2351 l_cvg_strt_dt_dummy := l_cvg_strt_dt;
2352 l_cvg_end_dt_dummy := NULL;
2353 elsif(l_cvg_strt_dt is NULL and l_cvg_thru_dt is not NULL) then
2354 hr_utility.set_location('Condition 3', 20);
2355 l_cvg_strt_dt_dummy := l_returned_strt_dt;
2356 l_cvg_end_dt_dummy := l_cvg_thru_dt;
2357 elsif(l_cvg_strt_dt is NULL and l_cvg_thru_dt is NULL) then
2358 hr_utility.set_location('Condition 4', 20);
2359 l_cvg_strt_dt_dummy := l_returned_strt_dt;
2360 l_cvg_end_dt_dummy := NULL;
2361 else
2362 NULL;
2363 end if;
2364 --
2365 /*
2366 if l_cvg_strt_dt is null then
2367 --
2368 l_cvg_strt_dt := l_pd_cvg_strt_dt;
2369 --
2370 end if;
2371 */
2372
2373
2374 --
2375 IF l_record_typ_cd = 'ENROLL' THEN
2376 --
2377 get_egd
2378 (p_per_in_ler_id => l_pil.per_in_ler_id,
2379 p_dpnt_person_id => l_contact_person_id,
2380 p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id,
2381 p_egd => l_egd
2382 );
2383 --
2384 fnd_file.put_line
2385 (which => fnd_file.log,
2386 buff => ' l_egd.elig_dpnt_id '||l_egd.elig_dpnt_id);
2387 --
2388 IF l_egd.elig_dpnt_id IS NOT NULL THEN
2389 --
2390 ben_elig_dpnt_api.process_dependent(
2391 p_validate => p_validate
2392 ,p_elig_dpnt_id => l_egd.elig_dpnt_id
2393 ,p_business_group_id => p_business_group_id
2394 ,p_effective_date => p_effective_date
2395 ,p_cvg_strt_dt => l_cvg_strt_dt_dummy -- l_cvg_strt_dt --If in EGD take it
2396 ,p_cvg_thru_dt => l_cvg_end_dt_dummy -- NVL(l_returned_end_dt,l_egd.elig_thru_dt)
2397 ,p_datetrack_mode => l_dt_mode
2398 ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
2399 ,p_effective_start_date => l_dummy_date
2400 ,p_effective_end_date => l_dummy_date
2401 ,p_object_version_number => l_ovn
2402 ,p_multi_row_actn => p_multi_row_actn);
2403 --
2404 END IF;
2405 --
2406 ELSIF l_record_typ_cd = 'POST' THEN
2407 --NK
2408 --Changes to eliminate summary row in Enrollment Upload Spreadsheet.
2409 --If the record type is POST, this is the last record of the group
2410 --So, first process the dependent information and then run the post process.
2411 --
2412 get_egd
2413 (p_per_in_ler_id => l_pil.per_in_ler_id,
2414 p_dpnt_person_id => l_contact_person_id,
2415 p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id,
2416 p_egd => l_egd
2417 );
2418 --
2419 fnd_file.put_line
2420 (which => fnd_file.log,
2421 buff => ' l_egd.elig_dpnt_id '||l_egd.elig_dpnt_id);
2422 --
2423 IF l_egd.elig_dpnt_id IS NOT NULL THEN
2424 --
2425 ben_elig_dpnt_api.process_dependent(
2426 p_validate => p_validate
2427 ,p_elig_dpnt_id => l_egd.elig_dpnt_id
2428 ,p_business_group_id => p_business_group_id
2429 ,p_effective_date => p_effective_date
2430 ,p_cvg_strt_dt => l_cvg_strt_dt_dummy -- l_cvg_strt_dt --If in EGD take it
2431 ,p_cvg_thru_dt => l_cvg_end_dt_dummy -- NVL(l_returned_end_dt,l_egd.elig_thru_dt) --If Entered take it
2432 ,p_datetrack_mode => l_dt_mode
2433 ,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
2434 ,p_effective_start_date => l_dummy_date
2435 ,p_effective_end_date => l_dummy_date
2436 ,p_object_version_number => l_ovn
2437 ,p_multi_row_actn => p_multi_row_actn);
2438 --
2439 END IF;
2440 --
2441 -- POST PROCESS
2442 --
2443 IF l_epe.prtt_enrt_rslt_id IS NOT NULL THEN
2444 --
2445 ben_elig_cvrd_dpnt_api.dpnt_actn_items(
2446 p_prtt_enrt_rslt_id => l_epe.prtt_enrt_rslt_id,
2447 p_elig_cvrd_dpnt_id => null,
2448 p_effective_date => p_effective_date,
2449 p_business_group_id => p_business_group_id,
2450 p_datetrack_mode => l_dt_mode);
2451 --
2452 ben_elig_cvrd_dpnt_api.chk_max_num_dpnt_for_pen(
2453 p_prtt_enrt_rslt_id => l_epe.prtt_enrt_rslt_id,
2454 p_effective_date => p_effective_date,
2455 p_business_group_id => p_business_group_id);
2456 --
2457 END IF;
2458 --
2459 ELSE
2460 --Throw Error invalid record type
2461 if g_debug then
2462 hr_utility.set_location('BEN_94536_RECORD_TYPE_ERROR'|| to_char(p_person_id),54);
2463 end if;
2464 fnd_message.set_name('BEN','BEN_94536_RECORD_TYPE_ERROR');
2465 fnd_message.set_token('PROC',l_proc);
2466 fnd_message.set_token('PERSON_ID',l_record_typ_cd);
2467 fnd_message.raise_error;
2468 --
2469 END IF;
2470 --
2471 END IF;
2472 ELSE
2473 if g_debug then
2474 hr_utility.set_location('BEN_94534_PIL_NOT_FOUND'|| to_char(p_person_id),54);
2475 end if;
2476 fnd_message.set_name('BEN','BEN_94534_PIL_NOT_FOUND');
2477 fnd_message.set_token('PROC',l_proc);
2478 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
2479 fnd_message.set_token('LER_ID',to_char(p_ler_id));
2480 fnd_message.set_token('LE_DATE', p_life_event_date);
2481 fnd_message.raise_error;
2482 --
2483 END IF;
2484 --
2485 exception when others then
2486 --
2487 raise;
2488 --
2489 end process_dependent;
2490 --
2491 -- --------------------------------------------------------------------------------
2492 -- |-----------------------------< PROCESS_BENEFICIARY >-------------------------|
2493 -- -------------------------------------------------------------------------------+
2494 procedure process_beneficiary
2495 (p_validate in boolean default false
2496 ,p_person_id in number
2497 ,p_pgm_id in number default null
2498 ,p_pl_id in number default null
2499 ,p_opt_id in number default null
2500 ,p_bnft_val in number default null
2501 ,p_ler_id in number
2502 ,p_life_event_date in date
2503 ,p_effective_date in date
2504 ,p_bnf_person_id in number
2505 ,p_business_group_id in number
2506 ,p_dsgn_strt_dt in date default null
2507 ,p_dsgn_thru_dt in date default null
2508 ,p_prmry_cntngnt_cd in varchar2
2509 ,p_pct_dsgd_num in number
2510 ,p_amt_dsgd_val in number default null
2511 ,p_amt_dsgd_uom in varchar2 default null
2512 ,p_addl_instrn_txt in varchar2 default null
2513 ,p_multi_row_actn in boolean default true
2514 ,p_organization_id in number default null
2515 ,p_ttee_person_id in number default null
2516 ,p_record_typ_cd in varchar2 ) is
2517 --
2518 l_proc varchar2(60) := 'ben_enrollment_information_detail.process_beneficiary';
2519 l_prtt_enrt_rslt_id number;
2520 l_object_version_number number;
2521 l_pl_bnf_id number;
2522 l_dummy_date date;
2523 l_pil c_pil%ROWTYPE;
2524 l_epe c_epe_oipl%ROWTYPE;
2525 l_bnf c_bnf%ROWTYPE;
2526 l_dt_mode varchar2(30);
2527 l_person_id number := p_person_id;
2528 l_dsgn_strt_dt date := p_dsgn_strt_dt;
2529 l_dsgn_thru_dt date := p_dsgn_thru_dt;
2530 l_bnft_val number := p_bnft_val;
2531 l_pgm_id number := p_pgm_id;
2532 l_pl_id number := p_pl_id;
2533 l_opt_id number := p_opt_id;
2534 l_ler_id number := p_ler_id;
2535 l_life_event_date date := p_effective_date;
2536 l_prmry_cntngnt_cd varchar2(30) := p_prmry_cntngnt_cd;
2537 l_bnf_person_id number := p_bnf_person_id;
2538 l_pct_dsgd_num number := p_pct_dsgd_num;
2539 l_amt_dsgd_val ben_pl_bnf_f.amt_dsgd_val%TYPE := p_amt_dsgd_val;
2540 l_amt_dsgd_uom ben_pl_bnf_f.amt_dsgd_uom%TYPE := p_amt_dsgd_uom;
2541 l_addl_instrn_txt ben_pl_bnf_f.addl_instrn_txt%TYPE := p_addl_instrn_txt;
2542 l_organization_id number := p_organization_id;
2543 l_ttee_person_id number := p_ttee_person_id;
2544 l_bnf_actn_warning boolean;
2545 l_suspend_flag varchar2(30);
2546 l_rslt_object_version_number number(9);
2547 l_record_typ_cd varchar2(30) := p_record_typ_cd;
2548 --
2549 cursor get_rslt_ovn_c is
2550 select object_version_number,
2551 sspndd_flag
2552 from ben_prtt_enrt_rslt_f
2553 where prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
2554 and business_group_id = p_business_group_id
2555 and p_effective_date
2556 between effective_start_date and effective_end_date;
2557 --
2558 begin
2559 hr_utility.set_location('Entering:'||l_proc, 20);
2560 --
2561 --Bug 5259118
2562 -- IF fnd_global.conc_request_id = -1 THEN
2563 --
2564 ben_env_object.init(p_business_group_id => p_business_group_id,
2565 p_effective_date => p_effective_date,
2566 p_thread_id => 1,
2567 p_chunk_size => 1,
2568 p_threads => 1,
2569 p_max_errors => 1,
2570 p_benefit_action_id => null);
2571 --
2572 -- END IF;
2573 --
2574 IF l_dsgn_strt_dt = hr_api.g_date THEN
2575 l_dsgn_strt_dt := NULL;
2576 END IF;
2577 --
2578 IF l_dsgn_thru_dt = hr_api.g_date THEN
2579 l_dsgn_thru_dt := NULL;
2580 END IF;
2581 --
2582 IF l_bnft_val = hr_api.g_number THEN
2583 l_bnft_val := null;
2584 END IF;
2585 --
2586 IF l_amt_dsgd_val = hr_api.g_number THEN
2587 l_amt_dsgd_val := null;
2588 END IF;
2589 --
2590 IF l_amt_dsgd_uom = hr_api.g_varchar2 THEN
2591 l_amt_dsgd_uom := null;
2592 END IF;
2593 --
2594 IF l_addl_instrn_txt = hr_api.g_varchar2 THEN
2595 l_addl_instrn_txt := null;
2596 END IF;
2597 --
2598 IF l_organization_id = hr_api.g_number THEN
2599 l_organization_id := NULL ;
2600 END IF;
2601 --
2602 IF l_ttee_person_id = hr_api.g_number THEN
2603 l_ttee_person_id := NULL;
2604 END IF;
2605 --
2606 IF l_record_typ_cd = hr_api.g_varchar2 THEN
2607 l_record_typ_cd := 'ENROLL';
2608 END IF;
2609 --
2610 fnd_file.put_line
2611 (which => fnd_file.log,
2612 buff => 'Entering - ben_enrollment_process.process_beneficiary' );
2613 --
2614 fnd_file.put_line
2615 (which => fnd_file.log,
2616 buff => 'l_person_id '||l_person_id||' l_pgm_id '||l_pgm_id||' l_pl_id '||l_pl_id);
2617 --
2618 fnd_file.put_line
2619 (which => fnd_file.log,
2620 buff => 'l_opt_id '||l_opt_id||' l_bnft_val '||l_bnft_val||' l_ler_id '||l_ler_id);
2621 fnd_file.put_line
2622 (which => fnd_file.log,
2623 buff => 'l_pct_dsgd_num '||l_pct_dsgd_num||' l_amt_dsgd_val '||l_amt_dsgd_val);
2624 fnd_file.put_line
2625 (which => fnd_file.log,
2626 buff => 'l_addl_instrn_txt '||l_addl_instrn_txt);
2627 fnd_file.put_line
2628 (which => fnd_file.log,
2629 buff => 'l_organization_id '||l_organization_id||' l_ttee_person_id '||l_ttee_person_id);
2630 fnd_file.put_line
2631 (which => fnd_file.log,
2632 buff => 'l_life_event_date '||l_life_event_date||' l_bnf_person_id '||l_bnf_person_id);
2633 fnd_file.put_line
2634 (which => fnd_file.log,
2635 buff => 'l_dsgn_strt_dt '||l_dsgn_strt_dt||' l_dsgn_thru_dt '||l_dsgn_thru_dt);
2636 fnd_file.put_line
2637 (which => fnd_file.log,
2638 buff => 'l_record_typ_cd '||l_record_typ_cd);
2639 --
2640 get_pil
2641 (p_person_id => l_person_id,
2642 p_ler_id => l_ler_id,
2643 p_life_event_date => l_life_event_date,
2644 p_pil => l_pil
2645 );
2646 --
2647 --Get Ended Enrollment Result
2648 get_current_pen
2649 (p_effective_date => p_effective_date,
2650 p_life_event_date => l_life_event_date,
2651 p_person_id => l_person_id,
2652 p_pgm_id => l_pgm_id,
2653 p_pl_id => l_pl_id,
2654 p_opt_id => l_opt_id,
2655 p_bnft_val => l_bnft_val,
2656 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id,
2657 p_object_version_number => l_object_version_number
2658 );
2659 --
2660 IF l_prtt_enrt_rslt_id IS NULL THEN
2661 --
2662 fnd_message.set_name('BEN','BEN_94614_NO_PEN_FOR_BNF');
2663 fnd_message.set_token('PROC',l_proc);
2664 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
2665 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
2666 fnd_message.set_token('PL_ID',to_char(p_pl_id));
2667 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
2668 fnd_message.set_token('PER_IN_LER_ID',to_char(l_pil.per_in_ler_id));
2669 fnd_message.set_token('LE_DATE',p_life_event_date);
2670 fnd_message.raise_error;
2671 --
2672 END IF;
2673 --
2674 open get_rslt_ovn_c;
2675 fetch get_rslt_ovn_c into l_rslt_object_version_number,
2676 l_suspend_flag;
2677 close get_rslt_ovn_c;
2678 --
2679 IF l_pil.per_in_ler_id IS NULL THEN
2680 --
2681 if g_debug then
2682 hr_utility.set_location('BEN_94534_PIL_NOT_FOUND'|| to_char(p_person_id),54);
2683 end if;
2684 fnd_message.set_name('BEN','BEN_94534_PIL_NOT_FOUND');
2685 fnd_message.set_token('PROC',l_proc);
2686 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
2687 fnd_message.set_token('LER_ID',to_char(p_ler_id));
2688 fnd_message.set_token('LE_DATE', p_life_event_date);
2689 fnd_message.raise_error;
2690 --
2691 END IF ;
2692 --
2693 get_pbn
2694 (p_effective_date => p_effective_date,
2695 p_bnf_person_id => l_bnf_person_id,
2696 p_organization_id => l_organization_id,
2697 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id,
2698 p_bnf => l_bnf
2699 );
2700 --
2701 IF l_bnf.pl_bnf_id IS NULL and l_prtt_enrt_rslt_id IS NOT NULL and
2702 l_dsgn_thru_dt IS NULL THEN
2703 --
2704 ben_plan_beneficiary_api.create_plan_beneficiary
2705 ( p_validate => p_validate
2706 ,p_pl_bnf_id => l_pl_bnf_id
2707 ,p_effective_start_date => l_dummy_date
2708 ,p_effective_end_date => l_dummy_date
2709 ,p_business_group_id => p_business_group_id
2710 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
2711 ,p_bnf_person_id => l_bnf_person_id
2712 ,p_organization_id => l_organization_id
2713 ,p_prmry_cntngnt_cd => l_prmry_cntngnt_cd
2714 ,p_pct_dsgd_num => l_pct_dsgd_num
2715 ,p_amt_dsgd_val => l_amt_dsgd_val
2716 ,p_amt_dsgd_uom => l_amt_dsgd_uom
2717 ,p_dsgn_strt_dt => l_dsgn_strt_dt
2718 ,p_dsgn_thru_dt => l_dsgn_thru_dt
2719 ,p_object_version_number => l_object_version_number
2720 ,p_per_in_ler_id => l_pil.per_in_ler_id
2721 ,p_effective_date => p_effective_date
2722 ,p_multi_row_actn => p_multi_row_actn
2723 );
2724 --
2725 ELSIF l_bnf.pl_bnf_id IS NOT NULL and l_dsgn_thru_dt IS NULL THEN
2726 --
2727 IF l_bnf.effective_start_date < p_effective_date THEN
2728 l_dt_mode := hr_api.g_update ;
2729 ELSE
2730 l_dt_mode := hr_api.g_correction ;
2731 END IF ;
2732 --
2733 ben_plan_beneficiary_api.update_plan_beneficiary
2734 ( p_validate => p_validate
2735 ,p_pl_bnf_id => l_bnf.pl_bnf_id
2736 ,p_effective_start_date => l_dummy_date
2737 ,p_effective_end_date => l_dummy_date
2738 ,p_business_group_id => p_business_group_id
2739 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
2740 ,p_bnf_person_id => l_bnf_person_id
2741 ,p_organization_id => l_organization_id
2742 ,p_prmry_cntngnt_cd => l_prmry_cntngnt_cd
2743 ,p_pct_dsgd_num => l_pct_dsgd_num
2744 ,p_amt_dsgd_val => l_amt_dsgd_val
2745 ,p_amt_dsgd_uom => l_amt_dsgd_uom
2746 ,p_dsgn_strt_dt => l_dsgn_strt_dt
2747 ,p_dsgn_thru_dt => l_dsgn_thru_dt
2748 ,p_object_version_number => l_bnf.object_version_number
2749 ,p_per_in_ler_id => l_pil.per_in_ler_id
2750 ,p_effective_date => p_effective_date
2751 ,p_datetrack_mode => l_dt_mode
2752 ,p_multi_row_actn => p_multi_row_actn
2753 );
2754 --
2755 ELSIF l_bnf.pl_bnf_id IS NOT NULL AND l_dsgn_thru_dt IS NOT NULL THEN
2756 --
2757 IF l_bnf.effective_start_date < p_effective_date THEN
2758 l_dt_mode := hr_api.g_update ;
2759 ben_plan_beneficiary_api.update_plan_beneficiary
2760 ( p_validate => p_validate
2761 ,p_pl_bnf_id => l_bnf.pl_bnf_id
2762 ,p_effective_start_date => l_dummy_date
2763 ,p_effective_end_date => l_dummy_date
2764 ,p_business_group_id => p_business_group_id
2765 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
2766 ,p_bnf_person_id => l_bnf_person_id
2767 ,p_organization_id => l_organization_id
2768 ,p_prmry_cntngnt_cd => l_prmry_cntngnt_cd
2769 ,p_pct_dsgd_num => l_pct_dsgd_num
2770 ,p_amt_dsgd_val => l_amt_dsgd_val
2771 ,p_amt_dsgd_uom => l_amt_dsgd_uom
2772 ,p_dsgn_strt_dt => l_dsgn_strt_dt
2773 ,p_dsgn_thru_dt => l_dsgn_thru_dt
2774 ,p_object_version_number => l_bnf.object_version_number
2775 ,p_per_in_ler_id => l_pil.per_in_ler_id
2776 ,p_effective_date => p_effective_date
2777 ,p_datetrack_mode => l_dt_mode
2778 ,p_multi_row_actn => p_multi_row_actn
2779 );
2780 --
2781 ELSE
2782 --
2783 l_dt_mode := hr_api.g_zap ;
2784 ben_plan_beneficiary_api.delete_plan_beneficiary
2785 (p_validate => p_validate
2786 ,p_pl_bnf_id => l_bnf.pl_bnf_id
2787 ,p_effective_start_date => l_dummy_date
2788 ,p_effective_end_date => l_dummy_date
2789 ,p_business_group_id => p_business_group_id
2790 ,p_object_version_number => l_bnf.object_version_number
2791 ,p_effective_date => p_effective_date
2792 ,p_datetrack_mode => l_dt_mode
2793 ,p_multi_row_actn => true
2794 );
2795 END IF;
2796 --
2797 ELSE
2798 --Bad case why are we here ???
2799 fnd_file.put_line
2800 (which => fnd_file.log,
2801 buff => 'Nothing happended... something wrong...');
2802 fnd_message.set_name('BEN','BEN_94615_BNF_WRONG_IF');
2803 fnd_message.set_token('PROC',l_proc);
2804 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
2805 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
2806 fnd_message.set_token('PL_ID',to_char(p_pl_id));
2807 fnd_message.set_token('OPT_ID',to_char(p_opt_id));
2808 fnd_message.set_token('PER_IN_LER_ID',to_char(l_pil.per_in_ler_id));
2809 fnd_message.set_token('LE_DATE',p_life_event_date);
2810 fnd_message.raise_error;
2811 --
2812 END IF;
2813 --
2814 -- IF l_prtt_enrt_rslt_id IS NOT NULL THEN
2815 IF l_record_typ_cd = 'POST' THEN
2816 --
2817 fnd_file.put_line
2818 (which => fnd_file.log,
2819 buff => 'Calling ben_plan_beneficiary_api.bnf_actn_items');
2820 --
2821 ben_plan_beneficiary_api.bnf_actn_items(
2822 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
2823 ,p_pl_bnf_id => l_bnf.pl_bnf_id
2824 ,p_effective_date => p_effective_date
2825 ,p_business_group_id => p_business_group_id
2826 ,p_validate => p_validate
2827 ,p_datetrack_mode => NULL);
2828
2829 /* ben_enrollment_action_items.process_bnf_actn_items(
2830 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
2831 ,p_rslt_object_version_number => l_rslt_object_version_number
2832 ,p_effective_date => trunc(p_effective_date)
2833 ,p_business_group_id => p_business_group_id
2834 ,p_validate => p_validate
2835 ,p_datetrack_mode => NULL
2836 ,p_suspend_flag => l_suspend_flag
2837 ,p_bnf_actn_warning => l_bnf_actn_warning
2838 );
2839 */
2840 --
2841 --
2842 END IF;
2843 --
2844 end process_beneficiary ;
2845 --
2846 end ben_enrollment_process;