[Home] [Help]
PACKAGE BODY: APPS.BEN_AUTOMATIC_ENROLLMENTS
Source
4 g_package varchar2(30) := 'ben_automatic_enrollments.';
1 package body ben_automatic_enrollments as
2 /* $Header: benauten.pkb 120.12 2011/05/21 10:30:02 sagnanas ship $ */
3 --
5 --
6 procedure reinstate_dpnt(p_pgm_id in number,
7 p_pl_id in number,
8 p_oipl_id in number,
9 p_business_group_id in number,
10 p_person_id in number,
11 p_per_in_ler_id in number,
12 p_elig_per_elctbl_chc_id in number,
13 p_dpnt_cvg_strt_dt_cd in varchar2,
14 p_dpnt_cvg_strt_dt_rl in number,
15 p_enrt_cvg_strt_dt in date,
16 p_effective_date in date ,
17 p_prev_prtt_enrt_rslt_id in number default null ) is
18
19 l_lf_evt_ocrd_dt date;
20 l_cvg_strt_dt date;
21 l_old_cvg_strt_dt date;
22 l_pl_typ_id number ;
23 l_opt_id number ;
24 --
25 --- To validate the plan type , type id is compared # 2508745
26 cursor c_pl_typ is
27 select pl_typ_id
28 from ben_pl_f pl
29 where pl.pl_id = p_pl_id
30 and p_effective_date between
31 pl.effective_start_date and
32 pl.effective_end_date ;
33
34 --- To validate the option , opt_id is compared # 2508745
35 cursor c_opt is
36 select opt_id
37 from ben_oipl_f oipl
38 where oipl.oipl_id = p_oipl_id
39 and p_effective_date between
40 oipl.effective_start_date and
41 oipl.effective_end_date ;
42
43
44
45 cursor c_prev_per_in_ler is
46 select max(pil.lf_evt_ocrd_dt)
47 from ben_per_in_ler pil
48 where pil.business_group_id = p_business_group_id
49 and pil.person_id = p_person_id
50 and pil.per_in_ler_id <> p_per_in_ler_id
51 and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD');
52
53
54
55 --- if the person enroled in option he must conitnue with the same option and plan type
56 --- regardless of program and plan. if the person enrolled in plan he must conitnue with
57 --- same plan type to reinstate the dependent # 2508745
58
59 --- Find then ler_id which last updated the dpnt row
60 ---- this is not taking the first piece of dentrolled row but the second piece
61
62 cursor c_previous_ptip_oipl_result is
63 select pen.prtt_enrt_rslt_id,pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
64 from ben_prtt_enrt_rslt_f pen,
65 ben_oipl_f oipl
66 where oipl.oipl_id = pen.oipl_id
67 and pen.pl_typ_id = l_pl_typ_id
68 and oipl.opt_id = l_opt_id
69 and pen.person_id = p_person_id
70 and pen.per_in_ler_id = p_per_in_ler_id
71 and pen.effective_end_date = hr_api.g_eot
72 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
73 and pen.effective_start_date between oipl.effective_start_date
74 and oipl.effective_end_date
75 AND pen.prtt_enrt_rslt_stat_cd IS NULL
76 ;
77
78
79 cursor c_previous_pgm_ptip_result is
80 select pen.prtt_enrt_rslt_id,pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
81 from ben_prtt_enrt_rslt_f pen
82 where pen.pl_typ_id = l_pl_typ_id
83 and pen.person_id = p_person_id
84 and pen.per_in_ler_id = p_per_in_ler_id
85 and pen.effective_end_date = hr_api.g_eot
86 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
87 AND pen.prtt_enrt_rslt_stat_cd IS NULL
88 ;
89
90 cursor c_previous_pl_oipl_result is
91 select pen.prtt_enrt_rslt_id,pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
92 from ben_prtt_enrt_rslt_f pen,
93 ben_oipl_f oipl
94 where oipl.oipl_id = pen.oipl_id
95 and pen.pl_typ_id = l_pl_typ_id
96 and oipl.opt_id = l_opt_id
97 and pen.person_id = p_person_id
98 and pen.per_in_ler_id = p_per_in_ler_id
99 and pen.effective_end_date = hr_api.g_eot
100 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
101 and pen.effective_start_date between oipl.effective_start_date
105
102 and oipl.effective_end_date
103 AND pen.prtt_enrt_rslt_stat_cd IS NULL
104 ;
106
107
108
109 --- when the previois result_id available
110
111 cursor c_previous_result_id_result is
112 select pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
113 from ben_prtt_enrt_rslt_f pen
114 where
115 pen.prtt_enrt_rslt_id = p_prev_prtt_enrt_rslt_id
116 and pen.person_id = p_person_id
117 -- this condition removed to CFD from any result
118 --and pen.per_in_ler_id = p_per_in_ler_id
119 and pen.effective_end_date = hr_api.g_eot
120 --and pen.enrt_cvg_thru_dt <> hr_api.g_eot --commented for bug 11842018
121 AND pen.prtt_enrt_rslt_stat_cd is null
122 ;
123
124
125 cursor c_previous_pl_result is
126 select pen.prtt_enrt_rslt_id,pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
127 from ben_prtt_enrt_rslt_f pen
128 where pen.pl_typ_id = l_pl_typ_id
129 and pen.person_id = p_person_id
130 and pen.per_in_ler_id = p_per_in_ler_id
131 and pen.effective_end_date = hr_api.g_eot
132 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
133 AND pen.prtt_enrt_rslt_stat_cd IS NULL
134 ;
135
136 cursor c_prev_pen_dpnts(v_enrt_rslt_id number,v_per_in_ler_id number) is
137 select
138 pdp_old.EFFECTIVE_END_DATE,
139 pdp_old.CVG_STRT_DT,
140 pdp_old.CVG_THRU_DT,
141 pdp_old.CVG_PNDG_FLAG,
142 pdp_old.OVRDN_FLAG,
143 pdp_old.OVRDN_THRU_DT,
144 pdp_old.PRTT_ENRT_RSLT_ID,
145 pdp_old.DPNT_PERSON_ID,
146 pdp_old.PER_IN_LER_ID,
147 pdp_old.BUSINESS_GROUP_ID,
148 pdp_old.PDP_ATTRIBUTE_CATEGORY,
149 pdp_old.PDP_ATTRIBUTE1,
150 pdp_old.PDP_ATTRIBUTE2,
151 pdp_old.PDP_ATTRIBUTE3,
152 pdp_old.PDP_ATTRIBUTE4,
153 pdp_old.PDP_ATTRIBUTE5,
154 pdp_old.PDP_ATTRIBUTE6,
155 pdp_old.PDP_ATTRIBUTE7,
156 pdp_old.PDP_ATTRIBUTE8,
157 pdp_old.PDP_ATTRIBUTE9,
158 pdp_old.PDP_ATTRIBUTE10,
159 pdp_old.PDP_ATTRIBUTE11,
160 pdp_old.PDP_ATTRIBUTE12,
161 pdp_old.PDP_ATTRIBUTE13,
162 pdp_old.PDP_ATTRIBUTE14,
163 pdp_old.PDP_ATTRIBUTE15,
164 pdp_old.PDP_ATTRIBUTE16,
165 pdp_old.PDP_ATTRIBUTE17,
166 pdp_old.PDP_ATTRIBUTE18,
167 pdp_old.PDP_ATTRIBUTE19,
168 pdp_old.PDP_ATTRIBUTE20,
169 pdp_old.PDP_ATTRIBUTE21,
170 pdp_old.PDP_ATTRIBUTE22,
171 pdp_old.PDP_ATTRIBUTE23,
172 pdp_old.PDP_ATTRIBUTE24,
173 pdp_old.PDP_ATTRIBUTE25,
174 pdp_old.PDP_ATTRIBUTE26,
175 pdp_old.PDP_ATTRIBUTE27,
176 pdp_old.PDP_ATTRIBUTE28,
177 pdp_old.PDP_ATTRIBUTE29,
178 pdp_old.PDP_ATTRIBUTE30,
179 pdp_old.LAST_UPDATE_DATE,
180 pdp_old.LAST_UPDATED_BY,
181 pdp_old.LAST_UPDATE_LOGIN,
182 pdp_old.CREATED_BY,
183 pdp_old.CREATION_DATE,
184 pdp_old.REQUEST_ID,
185 pdp_old.PROGRAM_APPLICATION_ID,
186 pdp_old.PROGRAM_ID,
187 pdp_old.PROGRAM_UPDATE_DATE,
188 pdp_old.OBJECT_VERSION_NUMBER,
189 pdp_old.elig_cvrd_dpnt_id,
190 pdp_old.EFFECTIVE_START_DATE
191 from ben_elig_cvrd_dpnt_f pdp_old
192 where
193 pdp_old.per_in_ler_id = v_per_in_ler_id
194 and pdp_old.prtt_enrt_rslt_id = v_enrt_rslt_id
195 and pdp_old.business_group_id = p_business_group_id;
196
197 cursor c_epe_dpnt(l_elig_per_elctbl_chc_id number,l_dpnt_person_id number) is
198 select edg.*
199 from ben_elig_dpnt edg
200 where edg.elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id
201 and edg.business_group_id = p_business_group_id
202 and edg.dpnt_person_id = l_dpnt_person_id;
203
204
205 --# bug 2623034 cursot to find the max dpnd allowed
206 cursor c_total_rqmt is
207 select r.mx_dpnts_alwd_num,
208 r.no_mx_num_dfnd_flag,
209 r.dsgn_rqmt_id,
210 r.grp_rlshp_cd
211 from ben_dsgn_rqmt_f r
212 where ((r.pl_id = p_pl_id)
213 or
214 (r.oipl_id = p_oipl_id)
215 or
216 (r.opt_id = (select opt_id
217 from ben_oipl_f
218 where oipl_id = p_oipl_id
219 and p_effective_date between effective_start_date
220 and effective_end_date
221 and business_group_id = p_business_group_id)))
222 and r.dsgn_typ_cd = 'DPNT'
223 -- this should be reoved but couldnt locate relation between grp and type
224 -- and r.grp_rlshp_cd is null
225 --
226 and r.business_group_id = p_business_group_id
227 and p_effective_date between r.effective_start_date
228 and r.effective_end_date;
229 --
230 cursor c_tot_elig_dpnt
231 ( v_per_in_ler_id number,
232 v_prtt_enrt_rslt_id number,
233 v_dsgn_rqmt_id number ,
234 v_grp_rlshp_cd varchar2 ) is
235 select count(pdp.dpnt_person_id)
236 from ben_elig_cvrd_dpnt_f pdp,
237 ben_elig_dpnt egd ,
238 per_contact_relationships pcr
239 where pdp.business_group_id = p_business_group_id
243 and p_effective_date between pdp.effective_start_date
240 and pdp.per_in_ler_id = v_per_in_ler_id
241 and pdp.prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
242 and pdp.cvg_strt_dt is not null
244 and pdp.effective_end_date
245 and egd.business_group_id = pdp.business_group_id
246 and pdp.dpnt_person_id = egd.dpnt_person_id
247 and egd.per_in_ler_id = v_per_in_ler_id
248 and egd.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
249 and pcr.person_id = p_person_id
250 and pcr.contact_person_id = egd.dpnt_person_id
251 and p_effective_date between nvl(pcr.date_start,p_effective_date)
252 and nvl(pcr.date_end,p_effective_date)
253 and (pcr.contact_type in
254 ( select drt.rlshp_typ_cd
255 from ben_dsgn_rqmt_f bdr ,
256 ben_dsgn_rqmt_rlshp_typ drt
257 where bdr.dsgn_rqmt_id = v_dsgn_rqmt_id
258 and drt.dsgn_rqmt_id = bdr.dsgn_rqmt_id
259 and ( bdr.grp_rlshp_cd = v_grp_rlshp_cd or
260 (bdr.grp_rlshp_cd is null and v_grp_rlshp_cd is null )
261 )
262 and p_effective_date between bdr.effective_start_date
263 and bdr.effective_end_date
264 )
265 --- if there is no relation typ defind take all
266 or
267 not exists
268 (select 'x' from ben_dsgn_rqmt_rlshp_typ drt
269 where drt.dsgn_rqmt_id = v_dsgn_rqmt_id
270 )
271 ) ;
272
273
274 l_tot_elig_dpnt number(15);
275 l_tot_rqmt_allow varchar2(30) ;
276 l_ttl_max_num number(15);
277 l_ttl_no_max_flag varchar2(30);
278 l_grp_rlshp_cd ben_dsgn_rqmt_f.grp_rlshp_cd%type ;
279 l_epe_dpnt_rec c_epe_dpnt%rowtype;
280 l_proc varchar2(80) := g_package||'reinstate_dpnt';
281 l_rslt_id number;
282 l_pil_id number;
283 l_dsgn_rqmt_id number;
284 l_object_version_number number;
285 l_eff_start_date date;
286 l_eff_end_date date;
287 l_elig_cvrd_dpnt_id ben_elig_cvrd_dpnt_f.elig_cvrd_dpnt_id%TYPE;
288
289 begin
290 --
291 hr_utility.set_location('Entering: '||l_proc,10);
292 hr_utility.set_location('program : '||p_pgm_id,745);
293 hr_utility.set_location('plan : ' ||p_pl_id,745);
294 hr_utility.set_location('option : ' ||p_oipl_id,745);
295 hr_utility.set_location('p_elig_per_elctbl_chc_id : ' ||p_elig_per_elctbl_chc_id,745);
296 hr_utility.set_location('eff date : ' ||p_effective_date,745 );
297
298 ---- Pl ty and opt id is determined for pl type and opt validation # 2508745
299 open c_pl_typ ;
300 fetch c_pl_typ into l_pl_typ_id ;
301 close c_pl_typ ;
302
303 open c_opt ;
304 fetch c_opt into l_opt_id ;
305 close c_opt ;
306
307 hr_utility.set_location('option : ' ||l_opt_id,745);
308 hr_utility.set_location('pl type : ' ||l_pl_typ_id,745);
309
310 if p_prev_prtt_enrt_rslt_id is not null then
311
312 l_rslt_id := p_prev_prtt_enrt_rslt_id ;
313 hr_utility.set_location('p_prev_prtt_enrt_rslt_id after result',745 );
314 open c_previous_result_id_result ;
315 fetch c_previous_result_id_result into l_old_cvg_strt_dt ,l_pil_id;
316 if c_previous_result_id_result%notfound then
317 hr_utility.set_location('0 null : '||l_proc,745);
318 end if ;
319 close c_previous_result_id_result;
320
321 else
322
323 if p_pgm_id is not null then
324 if p_oipl_id is not null then
325 open c_previous_ptip_oipl_result;
326 fetch c_previous_ptip_oipl_result into l_rslt_id,l_old_cvg_strt_dt ,l_pil_id;
327 if c_previous_ptip_oipl_result%notfound then null;
328 hr_utility.set_location('1 null : '||l_proc,745);
329 end if;
330 close c_previous_ptip_oipl_result;
331 hr_utility.set_location('1 : '||l_proc,745);
332
333 else
334 open c_previous_pgm_ptip_result;
335 fetch c_previous_pgm_ptip_result into l_rslt_id,l_old_cvg_strt_dt ,l_pil_id;
336 if c_previous_pgm_ptip_result%notfound then null;
337 end if;
338 close c_previous_pgm_ptip_result;
339 hr_utility.set_location('2 : '||l_proc,745);
340 end if;
341 else
342
343 if p_oipl_id is not null then
344 open c_previous_pl_oipl_result;
345 fetch c_previous_pl_oipl_result into l_rslt_id,l_old_cvg_strt_dt ,l_pil_id;
346 if c_previous_pl_oipl_result%notfound then null;
347 hr_utility.set_location('3 null : '||l_proc,745);
348 end if;
349 close c_previous_pl_oipl_result;
350 hr_utility.set_location('3 : '||l_proc,745);
351 elsif p_oipl_id is null then
352 open c_previous_pl_result;
353 fetch c_previous_pl_result into l_rslt_id,l_old_cvg_strt_dt ,l_pil_id;
354 if c_previous_pl_result%notfound then null;
355 hr_utility.set_location('4 nul : '||l_proc,745);
356 end if;
357 close c_previous_pl_result;
358 hr_utility.set_location('4 : '||l_proc,745);
359 end if;
360 end if;
361 end if ; --- p_prev_prtt_enrt_rslt_id
362
363 if l_rslt_id is not null and l_pil_id is not null then
364
365 hr_utility.set_location('Reinstating dependent person id',99);
366 hr_utility.set_location('pil id '|| l_pil_id ,99);
367 hr_utility.set_location('rslt id '|| l_rslt_id ,99);
368 hr_utility.set_location('cvg start '|| l_old_cvg_strt_dt ,99);
369
370 --- # 2623034 Find out the maximum required dpnt
371 --- Validate every groep of relation match with
372
373 l_tot_rqmt_allow := 'Y' ;
374
375 open c_total_rqmt;
376 Loop
377 fetch c_total_rqmt into l_ttl_max_num, l_ttl_no_max_flag,l_dsgn_rqmt_id,l_grp_rlshp_cd;
378 if c_total_rqmt%notfound then
379 exit ;
380 end if;
381 hr_utility.set_location(' grp_rlshp_cd ' || l_grp_rlshp_cd, 99 );
382 hr_utility.set_location(' l_dsgn_rqmt_id ' || l_dsgn_rqmt_id, 99 );
383 hr_utility.set_location(' ttl_no_max_flag ' || l_ttl_no_max_flag, 99 );
384 hr_utility.set_location(' ttl_max_num ' || l_ttl_max_num, 99 );
385 l_tot_elig_dpnt := 0 ;
386 open c_tot_elig_dpnt (l_pil_id,l_rslt_id,l_dsgn_rqmt_id,l_grp_rlshp_cd) ;
387 fetch c_tot_elig_dpnt into l_tot_elig_dpnt ;
388 close c_tot_elig_dpnt ;
389 hr_utility.set_location(' total eligible ' || l_tot_elig_dpnt, 99 );
390 if l_ttl_no_max_flag = 'N' and
391 nvl(l_tot_elig_dpnt,0) > l_ttl_max_num then
392 l_tot_rqmt_allow := 'N' ;
393 -- exit ;
394 end if ;
395 End loop ;
396 close c_total_rqmt ;
397 hr_utility.set_location(' l_tot_rqmt_allow ' || l_tot_rqmt_allow, 99 );
398
399
400 if l_tot_rqmt_allow = 'Y' then
401 -- Create the dependents row.
402 for l_prev_pen_dpnts in c_prev_pen_dpnts(l_rslt_id,l_pil_id) loop
403 --
404 hr_utility.set_location('Reinstating dependent in loop ',99);
405 hr_utility.set_location('p_dpnt_cvg_strt_dt_cd '|| p_dpnt_cvg_strt_dt_cd,99);
406 if p_dpnt_cvg_strt_dt_cd is null then
407 --
408 fnd_message.set_name('BEN','BEN_92558_DPNT_CVG_CD');
409 fnd_message.raise_error;
410 --
411 end if;
412 --
413 -- Calculate Dependents Coverage Start Date
414 --
415 ben_determine_date.main
416 (p_date_cd => p_dpnt_cvg_strt_dt_cd
417 ,p_per_in_ler_id => null
418 ,p_person_id => null
419 ,p_pgm_id => null
420 ,p_pl_id => null
421 ,p_oipl_id => null
422 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
423 ,p_business_group_id => p_business_group_id
424 ,p_formula_id => p_dpnt_cvg_strt_dt_rl
425 ,p_effective_date => p_effective_date
426 ,p_returned_date => l_cvg_strt_dt);
427
428 if l_cvg_strt_dt < p_enrt_cvg_strt_dt then
429 l_cvg_strt_dt := p_enrt_cvg_strt_dt;
430 end if;
431 hr_utility.set_location('Cvg start dt ='||to_char(l_cvg_strt_dt), 25);
432 --hook the depenedent to the new enrollment result.
433 open c_epe_dpnt(p_elig_per_elctbl_chc_id,l_prev_pen_dpnts.dpnt_person_id);
434 fetch c_epe_dpnt into l_epe_dpnt_rec;
435 if c_epe_dpnt%notfound then
436 null;
437 else
438 --
439 ben_sspndd_enrollment.g_cfw_dpnt_flag := 'Y' ;
440 hr_utility.set_location(' g_cfw_dpnt_flag '||ben_sspndd_enrollment.g_cfw_dpnt_flag,122);
441 --
442 ben_ELIG_DPNT_api.process_dependent(
443 p_elig_dpnt_id => l_epe_dpnt_rec.elig_dpnt_id,
444 p_business_group_id => p_business_group_id,
445 p_effective_date => p_effective_date,
446 p_cvg_strt_dt => l_cvg_strt_dt,
450 p_effective_start_date => l_eff_start_date,
447 p_cvg_thru_dt => hr_api.g_eot,
448 p_datetrack_mode => hr_api.g_insert,
449 p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id,
451 p_effective_end_date => l_eff_end_date,
455 ben_sspndd_enrollment.g_cfw_dpnt_flag := 'N' ;
452 p_object_version_number => l_object_version_number,
453 p_multi_row_actn => TRUE );
454 --
456 hr_utility.set_location(' g_cfw_dpnt_flag '||ben_sspndd_enrollment.g_cfw_dpnt_flag,122);
457 --
458 end if;
459 close c_epe_dpnt;
460 end loop;
461 End if ;
462 end if;
463 --
464 hr_utility.set_location('Leaving: '||l_proc,10);
465 --
466 end reinstate_dpnt;
467
468
469 procedure process_person(p_person_id in number,
470 p_business_group_id in number,
471 p_ler_id in number,
472 p_effective_date in date) is
473 --
474 cursor c_prtt_result(v_prtt_enrt_rslt_id number,
475 p_effective_date date) is
476 select pen.prtt_enrt_rslt_id,
477 pen.effective_start_date,
478 pen.effective_end_date,
479 pen.object_version_number,
480 pen.bnft_amt,
481 pen.uom,
482 pen.enrt_mthd_cd,
483 pen.business_group_id,
484 pen.enrt_cvg_strt_dt,
485 pen.enrt_cvg_thru_dt,
486 -- 3517682 start
487 pen.pen_attribute_category ,
488 pen.pen_attribute1 ,
489 pen.pen_attribute2 ,
490 pen.pen_attribute3 ,
491 pen.pen_attribute4 ,
492 pen.pen_attribute5 ,
493 pen.pen_attribute6 ,
494 pen.pen_attribute7 ,
495 pen.pen_attribute8 ,
496 pen.pen_attribute9 ,
497 pen.pen_attribute10 ,
498 pen.pen_attribute11 ,
499 pen.pen_attribute12 ,
500 pen.pen_attribute13 ,
501 pen.pen_attribute14 ,
502 pen.pen_attribute15 ,
503 pen.pen_attribute16 ,
504 pen.pen_attribute17 ,
505 pen.pen_attribute18 ,
506 pen.pen_attribute19 ,
507 pen.pen_attribute20 ,
508 pen.pen_attribute21 ,
509 pen.pen_attribute22,
510 pen.pen_attribute23,
511 pen.pen_attribute24,
512 pen.pen_attribute25,
513 pen.pen_attribute26,
514 pen.pen_attribute27,
515 pen.pen_attribute28,
516 pen.pen_attribute29,
517 pen.pen_attribute30
518 -- 3517682 end
519 from ben_prtt_enrt_rslt_f pen
520 where pen.prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
521 and p_effective_date
522 between pen.effective_start_date
523 and pen.effective_end_date
524 and pen.business_group_id = p_business_group_id;
525 --
526 l_prtt_result c_prtt_result%rowtype;
527 --
528 cursor c_choice_info(v_per_in_ler_id number) is
529 select pil.object_version_number,
530 epe.elig_per_elctbl_chc_id,
531 pel.enrt_typ_cycl_cd,
532 epe.enrt_cvg_strt_dt_cd,
533 pel.enrt_perd_end_dt,
534 pel.enrt_perd_strt_dt,
535 epe.enrt_cvg_strt_dt_rl,
536 epe.enrt_cvg_strt_dt,
537 to_date('31-12-4712','DD-MM-YYYY') enrt_cvg_end_dt,
538 epe.crntly_enrd_flag,
539 epe.dflt_flag,
540 epe.elctbl_flag,
541 epe.mndtry_flag,
542 pel.dflt_enrt_dt,
543 epe.dpnt_cvg_strt_dt_cd,
544 epe.dpnt_cvg_strt_dt_rl,
545 epe.alws_dpnt_dsgn_flag,
546 epe.dpnt_dsgn_cd,
547 epe.ler_chg_dpnt_cvg_cd,
548 epe.erlst_deenrt_dt,
549 epe.procg_end_dt,
550 epe.comp_lvl_cd,
551 epe.pl_id,
552 epe.oipl_id,
553 epe.pgm_id,
554 epe.plip_id,
555 epe.ptip_id,
556 epe.pl_typ_id,
557 epe.cmbn_ptip_id,
558 epe.cmbn_ptip_opt_id,
559 epe.spcl_rt_pl_id,
560 epe.spcl_rt_oipl_id,
561 epe.must_enrl_anthr_pl_id,
562 epe.prtt_enrt_rslt_id,
563 epe.bnft_prvdr_pool_id,
564 epe.per_in_ler_id,
565 epe.yr_perd_id,
566 epe.business_group_id,
567 'N' stage,
568 'N' suspended,
569 epe.cryfwd_elig_dpnt_cd
570 from ben_elig_per_elctbl_chc epe,
571 ben_per_in_ler pil,
572 ben_pil_elctbl_chc_popl pel
573 where epe.auto_enrt_flag = 'Y'
574 --
575 -- Bug No: 4024312
576 -- bug 4730268 reverse the fix of 4024312
577 -- as per discussion wth pb/lt we will add new enrollment method to validate
578 -- electable flag on auto enrollment. since many ct are use to the current way
579 -- we are reversing the fix
580 --and epe.elctbl_flag = 'Y'
581 and pil.business_group_id = p_business_group_id
582 and pil.per_in_ler_id = v_per_in_ler_id -- bug 4968574 :Get correct row for non-Unrestricted as UNRESTRICT is always in STARTED state
583 and epe.business_group_id = pil.business_group_id
584 and pel.business_group_id = epe.business_group_id
585 and pil.person_id = p_person_id
586 and epe.per_in_ler_id = pil.per_in_ler_id
587 and pel.per_in_ler_id = epe.per_in_ler_id
588 and pil.per_in_ler_stat_cd = 'STRTD'
592 from ben_prtt_enrt_rslt_f pen
589 and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
590 and (epe.pgm_id is not null
591 and not exists(select null
593 where pen.pgm_id = epe.pgm_id
594 and pen.per_in_ler_id = epe.per_in_ler_id
595 and pen.enrt_cvg_thru_dt = hr_api.g_eot
596 and pen.effective_end_date = hr_api.g_eot)
597 or epe.pl_id is not null
598 and not exists(select null
599 from ben_prtt_enrt_rslt_f pen
600 where pen.pl_id = epe.pl_id
601 and pen.per_in_ler_id = epe.per_in_ler_id
602 and pen.enrt_cvg_thru_dt = hr_api.g_eot
603 and pen.effective_end_date = hr_api.g_eot)
604 )
605 order by epe.pgm_id, epe.pl_id;
606 --
607 l_choice_info c_choice_info%rowtype;
608 --
609
610 cursor c_elctbl_epe is
611 select null
612 from ben_elig_per_elctbl_chc epe,
613 ben_per_in_ler pil,
614 ben_pil_elctbl_chc_popl pel
615 where epe.elctbl_flag = 'Y'
616 --and pil.business_group_id = p_business_group_id
617 --and epe.business_group_id = pil.business_group_id
618 --and pel.business_group_id = epe.business_group_id
619 and pil.person_id = p_person_id
620 and epe.per_in_ler_id = pil.per_in_ler_id
621 and pel.per_in_ler_id = epe.per_in_ler_id
622 and pil.ler_id = p_ler_id -- 5861855: Only the current LE should be checked.
623 and pil.per_in_ler_stat_cd = 'STRTD'
624 and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
625 and (epe.pgm_id is not null
626 and not exists(select null
627 from ben_prtt_enrt_rslt_f pen
628 where pen.pgm_id = epe.pgm_id
629 and pen.per_in_ler_id = epe.per_in_ler_id
630 and pen.enrt_cvg_thru_dt = hr_api.g_eot
631 and pen.effective_end_date = hr_api.g_eot)
632 or epe.pl_id is not null
633 and not exists(select null
634 from ben_prtt_enrt_rslt_f pen
635 where pen.pl_id = epe.pl_id
636 and pen.per_in_ler_id = epe.per_in_ler_id
637 and pen.enrt_cvg_thru_dt = hr_api.g_eot
638 and pen.effective_end_date = hr_api.g_eot));
639 --
640 cursor c_pgm_enrt_dt(v_elig_per_elctbl_chc_id number,v_pgm_id number) is
641 select pel.enrt_perd_strt_dt
642 from ben_pil_elctbl_chc_popl pel,ben_elig_per_elctbl_chc epe
643 where pel.pgm_id = v_pgm_id
644 and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
645 and epe.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id;
646
647 l_pgm_enrt_dt c_pgm_enrt_dt%rowtype;
648
649 cursor c_pl_enrt_dt(v_elig_per_elctbl_chc_id number,v_pl_id number) is
650 select pel.enrt_perd_strt_dt
651 from ben_pil_elctbl_chc_popl pel,ben_elig_per_elctbl_chc epe
652 where pel.pl_id = v_pl_id
653 and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
654 and epe.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id;
655
659 select enb.enrt_bnft_id,
656 l_pl_enrt_dt c_pl_enrt_dt%rowtype;
657
658 cursor c_bnft(v_elig_per_elctbl_chc_id number) is
660 decode(enb.entr_val_at_enrt_flag,'Y',enb.dflt_val,enb.val) val,
661 enb.dflt_flag,
662 enb.cvg_mlt_cd
663 from ben_enrt_bnft enb
664 where enb.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
665 ;
666 --
667 l_pgm_id number;
668 l_pl_id number;
669 l_pil_id number;
670 l_oipl_id number;
671 l_lf_evt_ocrd_dt date;
672 l_bnft c_bnft%rowtype;
673 l_bnft_reset c_bnft%rowtype; -- BBULUSU CODE
674 l_dflt_bnft c_bnft%rowtype;
675 l_dflt_found boolean;
676 l_per_in_ler_id number := benutils.get_per_in_ler_id
677 (p_person_id => p_person_id,
678 p_business_group_id => p_business_group_id,
679 p_ler_id => p_ler_id,
680 p_effective_date => p_effective_date);
681 --
682
683 cursor c_rt(v_elig_per_elctbl_chc_id number,
684 v_enrt_bnft_id number) is
685 select ecr.enrt_rt_id,
686 nvl(ecr.val,ecr.dflt_val) default_value,
687 nvl(ecr.ann_dflt_val,ecr.ann_val) ann_rt_val,
688 ecr.prtt_rt_val_id
689 from ben_enrt_rt ecr
690 where ecr.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
691 and ecr.business_group_id = p_business_group_id
692 and ecr.entr_val_at_enrt_flag = 'Y'
693 and ecr.spcl_rt_enrt_rt_id is null
694 union
695 select ecr.enrt_rt_id,
696 nvl(ecr.val,ecr.dflt_val) default_value,
697 nvl(ecr.ann_dflt_val,ecr.ann_val) ann_rt_val,
698 ecr.prtt_rt_val_id
699 from ben_enrt_rt ecr
700 where ecr.enrt_bnft_id = v_enrt_bnft_id
701 and ecr.business_group_id = p_business_group_id
702 and ecr.entr_val_at_enrt_flag = 'Y'
703 and ecr.spcl_rt_enrt_rt_id is null
704 ;
705 --
706 l_rt c_rt%rowtype;
707 --
708 l_cvg_strt_dt date;
709
710 type g_rt_rec is record
711 (enrt_rt_id ben_enrt_rt.enrt_rt_id%type,
712 dflt_val ben_enrt_rt.dflt_val%type,
713 prtt_rt_val_id ben_enrt_rt.prtt_rt_val_id%type,
714 ann_rt_val ben_enrt_rt.ann_val%type);
715 --
716 type g_rt_table is table of g_rt_rec index by binary_integer;
717 --
718 l_rt_table g_rt_table;
719 --
720 --
721 -- Bug 5529696
722 -- Following cursor is meant to query PGM_IDs and PNIP_ID's into which benrtchg made enrollments (due to change in BNFT_AMT)
723 -- and hence did not get caught by c_choice_info.
724 --
725 cursor c_pgm_pnip_id(p_per_in_ler_id number) is
726 --
727 select distinct pen.pgm_id pgm_id, null pl_id
728 from ben_prtt_enrt_rslt_f pen,
729 ben_per_in_ler pil,
730 ben_elig_per_elctbl_chc epe
731 where pil.per_in_ler_id = p_per_in_ler_id
732 and pil.per_in_ler_Stat_cd = 'STRTD'
733 and pen.per_in_ler_id = pil.per_in_ler_id
734 and pen.business_group_id = p_business_group_id
735 and pen.enrt_cvg_thru_dt = hr_api.g_eot
736 and pen.effective_end_date = hr_api.g_eot
737 and p_effective_date between pen.effective_start_date
738 and pen.effective_end_date
739 and pen.pgm_id=epe.pgm_id
740 and epe.auto_enrt_flag = 'Y'
741 and epe.per_in_ler_id = pil.per_in_ler_id
742 UNION
743 select distinct null pgm_id, pen.pl_id pl_id
744 from ben_prtt_enrt_rslt_f pen,
745 ben_per_in_ler pil,
746 ben_elig_per_elctbl_chc epe
747 where pil.per_in_ler_id = p_per_in_ler_id
748 and pil.per_in_ler_Stat_cd = 'STRTD'
749 and pen.per_in_ler_id = pil.per_in_ler_id
750 and pen.business_group_id = p_business_group_id
751 and pen.enrt_cvg_thru_dt = hr_api.g_eot
752 and pen.effective_end_date = hr_api.g_eot
753 and pen.pgm_id is null
754 and p_effective_date between pen.effective_start_date
755 and pen.effective_end_date
756 and epe.auto_enrt_flag = 'Y'
757 and epe.per_in_ler_id = pil.per_in_ler_id;
758
759 --
760 cursor c_lf_evt_ocrd_dt(p_per_in_ler_id number) is
761 select pil.lf_evt_ocrd_dt
762 from ben_per_in_ler pil
763 where pil.business_group_id = p_business_group_id
764 and pil.per_in_ler_id = p_per_in_ler_id
765 and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD');
766 --
767 ll_lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type;
768
769 -- Bug 5529696
770 -- Local Variables
771 --
772 l_proc varchar2(80) := g_package||'process_person';
773 l_dpnt_actn_warning boolean;
774 l_bnf_actn_warning boolean;
775 l_ctfn_actn_warning boolean;
776 l_new_election boolean := false;
777 l_datetrack_mode varchar2(30);
778 l_prev_pgm_id number := -99999;
779 l_prev_pl_id number := -99999;
780 l_prtt_enrt_interim_id number;
781 l_rslt_id number;
782 l_count number;
783 l_object_version_number number;
784 l_suspend_flag varchar2(30);
785 l_dummy varchar2(30);
786 l_person_susp varchar2(30) := 'N';
787 l_cls_enrt_flag boolean := true;
788 l_effective_start_date date;
789 l_effective_end_date date;
790 l_eff_start_date date;
791 l_eff_end_date date;
792 l_effective_dt date;
793 l_prev_eff_dt date;
797 l_cryfwd_elig_dpnt_cd varchar2(30) ;
794 l_elig_cvrd_dpnt_id ben_elig_cvrd_dpnt_f.elig_cvrd_dpnt_id%TYPE;
795
796 l_rec ben_env_object.g_global_env_rec_type;
798 l_prev_rslt_id_at number := 0 ;
799 l_prev_prtt_enrt_rslt_id number ;
800 l_cursor boolean := false;
801
802 --
803 begin
804 --
805 hr_utility.set_location('Entering: '||l_proc,10);
806
807 -- a call to get the benmngle effective run date.
808 ben_env_object.get(p_rec => l_rec);
809 hr_utility.set_location('Effective date: '||l_rec.effective_date,10);
810
811 --
812 -- Check if we need to close the enrollment, we only do this if all
813 -- electable choices for a per_in_ler are autoutomatic.
814 --
815
816 open c_elctbl_epe;
817 --
818 fetch c_elctbl_epe into l_dummy;
819 if c_elctbl_epe%found then
820 --
821 hr_utility.set_location('Per in Ler will not be closed : '||l_proc,10);
822 l_cls_enrt_flag := false;
823 --
824 else
825 --
826 hr_utility.set_location('Per in Ler will be closed : '||l_proc,10);
827 l_cls_enrt_flag := true;
828 --
829 end if;
830 --
831 close c_elctbl_epe;
832 --
833 open c_choice_info(l_per_in_ler_id);
834 --
835 loop
836 --
837 l_bnft := l_bnft_reset; -- BBULUSU CODE
838 --
839 fetch c_choice_info into l_choice_info;
840 exit when c_choice_info%notfound;
841 --
842 --bug#4478186 - moved the code from down below
843 if l_choice_info.pgm_id is not null then
844 open c_pgm_enrt_dt(l_choice_info.elig_per_elctbl_chc_id,
845 l_choice_info.pgm_id);
846 fetch c_pgm_enrt_dt into l_pgm_enrt_dt;
847 /* bug 7623319
848 if l_pgm_enrt_dt.enrt_perd_strt_dt < l_rec.effective_date then
849 l_effective_dt := l_pgm_enrt_dt.enrt_perd_strt_dt;
850 else
851 l_effective_dt := l_rec.effective_date;
852 end if;
853 */
854 -- bug 7623319: Assign automatic on enrollment period start date
855 l_effective_dt := l_pgm_enrt_dt.enrt_perd_strt_dt;
856 close c_pgm_enrt_dt;
857 elsif l_choice_info.pl_id is not null then
858 open c_pl_enrt_dt(l_choice_info.elig_per_elctbl_chc_id,
859 l_choice_info.pl_id);
860 fetch c_pl_enrt_dt into l_pl_enrt_dt;
861 /* bug 7623319
862 if l_pl_enrt_dt.enrt_perd_strt_dt < l_rec.effective_date then
863 l_effective_dt := l_pl_enrt_dt.enrt_perd_strt_dt;
864 else
865 l_effective_dt := l_rec.effective_date;
866 end if;
867 */
868 l_effective_dt := l_pl_enrt_dt.enrt_perd_strt_dt;
869 close c_pl_enrt_dt;
870 end if;
871 -- Get participant enrollment result information
872
873 hr_utility.set_location('cvg strt cd: '||l_choice_info.dpnt_cvg_strt_dt_cd,10);
874 hr_utility.set_location('epe id : '||l_choice_info.elig_per_elctbl_chc_id,10);
875 hr_utility.set_location('l_effective_dt'||l_effective_dt,11);
876 --
877 if l_choice_info.crntly_enrd_flag = 'Y' then
878 --
879 open c_prtt_result(l_choice_info.prtt_enrt_rslt_id,
880 l_effective_dt);
881 --
882 fetch c_prtt_result into l_prtt_result;
883 if c_prtt_result%notfound then
884 --
885 close c_prtt_result;
886 fnd_message.set_name('BEN','BEN_91711_ENRT_RSLT_NOT_FOUND');
887 fnd_message.set_token('PROC',l_proc);
888 fnd_message.set_token('ID',l_choice_info.prtt_enrt_rslt_id);
889 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
890 fnd_message.set_token('LER_ID',to_char(p_ler_id));
891 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
892 fnd_message.raise_error;
893 --
894 end if;
895 --
896 close c_prtt_result;
897 --
898 l_new_election := false;
899 l_datetrack_mode := hr_api.g_update;
900 l_choice_info.stage := 'N';
901 --
902 else
903 --
904 l_new_election := true;
905 l_datetrack_mode := hr_api.g_insert;
906 l_choice_info.stage := 'Y';
907 --
908 --Bug 5589466 : Reset l_prtt_result if the current election is a new one
909 l_prtt_result := null;
910 --End 5589466
911
912 end if;
913 --
914 -- Get benefit information
915 --
916 l_dflt_found:=false;
917 hr_utility.set_location(l_proc,20);
918 open c_bnft(l_choice_info.elig_per_elctbl_chc_id);
919 loop
920 hr_utility.set_location(l_proc,30);
921 --
922 fetch c_bnft into l_bnft;
923 --
924 exit when c_bnft%notfound;
925 hr_utility.set_location(l_proc,40);
926 if l_bnft.dflt_flag='Y' then
927 hr_utility.set_location(l_proc,50);
928 l_dflt_bnft:=l_bnft;
929 l_dflt_found:=true;
930 end if;
931 hr_utility.set_location(l_proc,60);
932 end loop;
933 hr_utility.set_location(l_proc,70);
934 close c_bnft;
938 end if;
935 if l_dflt_found=true then
936 hr_utility.set_location(l_proc,80);
937 l_bnft:=l_dflt_bnft;
939 hr_utility.set_location(l_proc,90);
940 --
941 -- Get Rate information
942 --
943 for l_count in 1..10 loop
944 --
945 -- Initialise array to null
946 --
947 l_rt_table(l_count).enrt_rt_id := null;
948 l_rt_table(l_count).dflt_val := null;
949 l_rt_table(l_count).prtt_rt_val_id:=null;
950 --
951 end loop;
952 --
953 l_count:= 0;
954 --
955 for l_rec in c_rt(l_choice_info.elig_per_elctbl_chc_id,
956 l_bnft.enrt_bnft_id) loop
957 --
958 l_count := l_count+1;
959 l_rt_table(l_count).enrt_rt_id := l_rec.enrt_rt_id;
960 l_rt_table(l_count).dflt_val := l_rec.default_value;
961 l_rt_table(l_count).prtt_rt_val_id:=l_rec.prtt_rt_val_id;
962 --
963 end loop;
964 --
965 l_suspend_flag := 'N';
966 --
967 -- Call election information batch process
968 --
969 /* moved above to pass the l_effective_dt to cursor to get current
970 * enrollment
971 if l_choice_info.pgm_id is not null then
972 open c_pgm_enrt_dt(l_choice_info.elig_per_elctbl_chc_id,
973 l_choice_info.pgm_id);
974 fetch c_pgm_enrt_dt into l_pgm_enrt_dt;
975 if l_pgm_enrt_dt.enrt_perd_strt_dt < l_rec.effective_date then
976 l_effective_dt := l_pgm_enrt_dt.enrt_perd_strt_dt;
977 else
978 l_effective_dt := l_rec.effective_date;
979 end if;
980 close c_pgm_enrt_dt;
981 elsif l_choice_info.pl_id is not null then
982 open c_pl_enrt_dt(l_choice_info.elig_per_elctbl_chc_id,
983 l_choice_info.pl_id);
984 fetch c_pl_enrt_dt into l_pl_enrt_dt;
985 if l_pl_enrt_dt.enrt_perd_strt_dt < l_rec.effective_date then
986 l_effective_dt := l_pl_enrt_dt.enrt_perd_strt_dt;
987 else
988 l_effective_dt := l_rec.effective_date;
989 end if;
990 close c_pl_enrt_dt;
991 end if;
992 */
993 hr_utility.set_location('cvg_mlt_cd='||l_bnft.cvg_mlt_cd,13);
994 hr_utility.set_location('bnft_val='||l_bnft.val,13);
995 hr_utility.set_location('rate_val='||l_rt_table(1).dflt_val,13);
996 hr_utility.set_location('ann_rt_val='||l_rt_table(1).ann_rt_val,13);
997 if l_bnft.cvg_mlt_cd='SAAEAR' and
998 l_rt_table(1).ann_rt_val is null then
999 l_rt_table(1).ann_rt_val:=l_rt_table(1).dflt_val;
1000 end if;
1001 --hr_utility.set_location('ann_rt_val='||l_rt_table(1).ann_rt_val,13);
1002 ben_election_information.election_information
1003 (p_elig_per_elctbl_chc_id => l_choice_info.elig_per_elctbl_chc_id,
1004 p_prtt_enrt_rslt_id => l_choice_info.prtt_enrt_rslt_id,
1005 p_effective_date => l_effective_dt,
1006 p_enrt_mthd_cd => 'A',
1007 p_business_group_id => p_business_group_id,
1008 p_enrt_bnft_id => l_bnft.enrt_bnft_id,
1009 p_bnft_val => l_bnft.val,
1010 p_enrt_rt_id1 => l_rt_table(1).enrt_rt_id,
1011 p_rt_val1 => l_rt_table(1).dflt_val,
1012 p_enrt_rt_id2 => l_rt_table(2).enrt_rt_id,
1013 p_rt_val2 => l_rt_table(2).dflt_val,
1014 p_enrt_rt_id3 => l_rt_table(3).enrt_rt_id,
1015 p_rt_val3 => l_rt_table(3).dflt_val,
1016 p_enrt_rt_id4 => l_rt_table(4).enrt_rt_id,
1017 p_rt_val4 => l_rt_table(4).dflt_val,
1018 p_enrt_rt_id5 => l_rt_table(5).enrt_rt_id,
1019 p_rt_val5 => l_rt_table(5).dflt_val,
1020 p_enrt_rt_id6 => l_rt_table(6).enrt_rt_id,
1021 p_rt_val6 => l_rt_table(6).dflt_val,
1022 p_enrt_rt_id7 => l_rt_table(7).enrt_rt_id,
1023 p_rt_val7 => l_rt_table(7).dflt_val,
1024 p_enrt_rt_id8 => l_rt_table(8).enrt_rt_id,
1025 p_rt_val8 => l_rt_table(8).dflt_val,
1026 p_enrt_rt_id9 => l_rt_table(9).enrt_rt_id,
1027 p_rt_val9 => l_rt_table(9).dflt_val,
1028 p_enrt_rt_id10 => l_rt_table(10).enrt_rt_id,
1029 p_rt_val10 => l_rt_table(10).dflt_val,
1030 p_datetrack_mode => l_datetrack_mode,
1031 p_suspend_flag => l_suspend_flag,
1032 p_prtt_enrt_interim_id => l_prtt_enrt_interim_id,
1033 p_prtt_rt_val_id1 => l_rt_table(1).prtt_rt_val_id,
1034 p_prtt_rt_val_id2 => l_rt_table(2).prtt_rt_val_id,
1035 p_prtt_rt_val_id3 => l_rt_table(3).prtt_rt_val_id,
1036 p_prtt_rt_val_id4 => l_rt_table(4).prtt_rt_val_id,
1037 p_prtt_rt_val_id5 => l_rt_table(5).prtt_rt_val_id,
1038 p_prtt_rt_val_id6 => l_rt_table(6).prtt_rt_val_id,
1039 p_prtt_rt_val_id7 => l_rt_table(7).prtt_rt_val_id,
1040 p_prtt_rt_val_id8 => l_rt_table(8).prtt_rt_val_id,
1041 p_prtt_rt_val_id9 => l_rt_table(9).prtt_rt_val_id,
1042 p_prtt_rt_val_id10 => l_rt_table(10).prtt_rt_val_id,
1043 p_ann_rt_val1 => l_rt_table(1).ann_rt_val,
1044 p_ann_rt_val2 => l_rt_table(2).ann_rt_val,
1045 p_ann_rt_val3 => l_rt_table(3).ann_rt_val,
1046 p_ann_rt_val4 => l_rt_table(4).ann_rt_val,
1047 p_ann_rt_val5 => l_rt_table(5).ann_rt_val,
1051 p_ann_rt_val9 => l_rt_table(9).ann_rt_val,
1048 p_ann_rt_val6 => l_rt_table(6).ann_rt_val,
1049 p_ann_rt_val7 => l_rt_table(7).ann_rt_val,
1050 p_ann_rt_val8 => l_rt_table(8).ann_rt_val,
1052 p_ann_rt_val10 => l_rt_table(10).ann_rt_val,
1053 -- 3517682 start
1054 p_pen_attribute_category =>l_prtt_result.pen_attribute_category ,
1055 p_pen_attribute1 =>l_prtt_result.pen_attribute1 ,
1056 p_pen_attribute2 =>l_prtt_result.pen_attribute2 ,
1057 p_pen_attribute3 =>l_prtt_result.pen_attribute3 ,
1058 p_pen_attribute4 =>l_prtt_result.pen_attribute4 ,
1059 p_pen_attribute5 =>l_prtt_result.pen_attribute5 ,
1060 p_pen_attribute6 =>l_prtt_result.pen_attribute6 ,
1061 p_pen_attribute7 =>l_prtt_result.pen_attribute7 ,
1062 p_pen_attribute8 =>l_prtt_result.pen_attribute8 ,
1063 p_pen_attribute9 =>l_prtt_result.pen_attribute9 ,
1064 p_pen_attribute10 =>l_prtt_result.pen_attribute10 ,
1065 p_pen_attribute11 =>l_prtt_result.pen_attribute11 ,
1066 p_pen_attribute12 =>l_prtt_result.pen_attribute12 ,
1067 p_pen_attribute13 =>l_prtt_result.pen_attribute13 ,
1068 p_pen_attribute14 =>l_prtt_result.pen_attribute14 ,
1069 p_pen_attribute15 =>l_prtt_result.pen_attribute15 ,
1070 p_pen_attribute16 =>l_prtt_result.pen_attribute16 ,
1071 p_pen_attribute17 =>l_prtt_result.pen_attribute17 ,
1072 p_pen_attribute18 =>l_prtt_result.pen_attribute18 ,
1073 p_pen_attribute19 =>l_prtt_result.pen_attribute19 ,
1074 p_pen_attribute20 =>l_prtt_result.pen_attribute20 ,
1075 p_pen_attribute21 =>l_prtt_result.pen_attribute21 ,
1076 p_pen_attribute22 =>l_prtt_result.pen_attribute22,
1077 p_pen_attribute23 =>l_prtt_result.pen_attribute23,
1078 p_pen_attribute24 =>l_prtt_result.pen_attribute24,
1079 p_pen_attribute25 =>l_prtt_result.pen_attribute25,
1080 p_pen_attribute26 =>l_prtt_result.pen_attribute26,
1081 p_pen_attribute27 =>l_prtt_result.pen_attribute27,
1082 p_pen_attribute28 =>l_prtt_result.pen_attribute28,
1083 p_pen_attribute29 =>l_prtt_result.pen_attribute29,
1084 p_pen_attribute30 =>l_prtt_result.pen_attribute30,
1085 -- 3517682 end
1086 p_object_version_number => l_prtt_result.object_version_number,
1087 p_effective_start_date => l_effective_start_date,
1088 p_effective_end_date => l_effective_end_date,
1089 p_dpnt_actn_warning => l_dpnt_actn_warning,
1090 p_bnf_actn_warning => l_bnf_actn_warning,
1091 p_ctfn_actn_warning => l_ctfn_actn_warning);
1092 --
1093 l_choice_info.suspended := l_suspend_flag;
1094 --
1095 if l_choice_info.suspended = 'Y' then
1096 --
1097 l_person_susp := 'Y';
1098 l_choice_info.stage := 'S';
1099 --
1100 end if;
1101 --
1102 open c_prtt_result(l_choice_info.prtt_enrt_rslt_id,
1103 l_effective_dt);
1104 --
1105 fetch c_prtt_result into l_prtt_result;
1106 if c_prtt_result%notfound then
1107 --
1108 close c_prtt_result;
1109 fnd_message.set_name('BEN','BEN_91711_ENRT_RSLT_NOT_FOUND');
1110 fnd_message.set_token('PROC',l_proc);
1111 fnd_message.set_token('ID',l_choice_info.prtt_enrt_rslt_id);
1112 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1113 fnd_message.set_token('LER_ID',to_char(p_ler_id));
1114 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
1115 fnd_message.raise_error;
1116 --
1117 else
1118 --
1119 l_choice_info.enrt_cvg_strt_dt := l_prtt_result.enrt_cvg_strt_dt;
1120 l_choice_info.enrt_cvg_end_dt := l_prtt_result.enrt_cvg_thru_dt;
1121 --
1122 -- after the enhncemnt # 2685018 cryfwd_elig_dpnt_cd value is concated with
1123 -- result id from where the dpnt carry forwarded , this will seprate the code from
1124 --- result id
1125
1126 l_prev_prtt_enrt_rslt_id := null; -- Reintializing the previous enrt result id
1127 l_cryfwd_elig_dpnt_cd := l_choice_info.cryfwd_elig_dpnt_cd ;
1128 l_prev_rslt_id_at := instr(l_cryfwd_elig_dpnt_cd, '^') ;
1129 --- if the result id concated with the code, then the caht '^' must be aprt of the
1130 --- the code
1131
1132 if l_prev_rslt_id_at > 0 then
1133 --- if the to_number errors , catch the exception
1134 Begin
1135 l_prev_prtt_enrt_rslt_id := to_number(substr(l_cryfwd_elig_dpnt_cd,l_prev_rslt_id_at+1) );
1136 Exception
1137 when value_error then
1138 l_prev_prtt_enrt_rslt_id := null;
1139 End ;
1140
1141 l_cryfwd_elig_dpnt_cd := substr(l_cryfwd_elig_dpnt_cd,1,l_prev_rslt_id_at-1) ;
1142 ---
1143 end if ;
1144
1145
1146 hr_utility.set_location('l_cryfwd_elig_dpnt_cd '||l_cryfwd_elig_dpnt_cd,744);
1147 hr_utility.set_location('l_prev_prtt_enrt_rslt_id '||l_prev_prtt_enrt_rslt_id,744);
1148
1149 if l_datetrack_mode = hr_api.g_insert and l_cryfwd_elig_dpnt_cd = 'CFRRWP' then
1150
1151 hr_utility.set_location('cvg strt cd: '||l_choice_info.dpnt_cvg_strt_dt_cd,10);
1152 -- p_effective_date is now changed to l_effective_dt , when ever LE reprocessed
1153 -- result created as on effective date and automeatic enrollment called with
1154 -- lE_ocurd_Dt as affective date so there is no result as on effective date (le_ocrd_dt)
1155 -- this is fixed sending l_effective_dt # 3042033
1156
1157 reinstate_dpnt(p_pgm_id =>l_choice_info.pgm_id,
1161 p_person_id => p_person_id,
1158 p_pl_id => l_choice_info.pl_id,
1159 p_oipl_id => l_choice_info.oipl_id,
1160 p_business_group_id => p_business_group_id,
1162 p_per_in_ler_id => l_per_in_ler_id,
1163 p_elig_per_elctbl_chc_id => l_choice_info.elig_per_elctbl_chc_id,
1164 p_dpnt_cvg_strt_dt_cd => l_choice_info.dpnt_cvg_strt_dt_cd,
1165 p_dpnt_cvg_strt_dt_rl => l_choice_info.dpnt_cvg_strt_dt_rl,
1166 p_enrt_cvg_strt_dt => l_choice_info.enrt_cvg_strt_dt,
1167 p_effective_date => l_effective_dt,
1168 p_prev_prtt_enrt_rslt_id => l_prev_prtt_enrt_rslt_id
1169 );
1170 end if;
1171 end if;
1172 --
1173 close c_prtt_result;
1174 --
1175 -- Do Post enrollment - Writes elecment entries, calls close enrollment
1176 --
1177 if l_choice_info.pgm_id is null then
1178 --
1179 -- Invoke post result process
1180 --
1181 ben_proc_common_enrt_rslt.process_post_results
1182 (p_person_id => p_person_id,
1183 p_enrt_mthd_cd => 'A',
1184 p_effective_date => l_effective_dt,
1185 p_business_group_id => p_business_group_id,
1186 p_per_in_ler_id => l_per_in_ler_id);
1187 ben_proc_common_enrt_rslt.process_post_enrollment
1188 (p_per_in_ler_id => l_per_in_ler_id,
1189 p_pgm_id => l_choice_info.pgm_id,
1190 p_pl_id => l_choice_info.pl_id,
1191 p_cls_enrt_flag => l_cls_enrt_flag,
1192 p_enrt_mthd_cd => 'A',
1193 p_proc_cd => null,
1194 p_person_id => p_person_id,
1195 p_business_group_id => p_business_group_id,
1196 p_effective_date => l_rec.effective_date);
1197 --
1198 end if;
1199 --
1200 -- Do multi row edit stuff
1201 --
1202 if l_prev_pgm_id = -99999 then
1203 --
1204 l_prev_pgm_id := l_choice_info.pgm_id;
1205 l_prev_eff_dt := l_effective_dt;
1206 --
1207 elsif nvl(l_prev_pgm_id,-1) <> nvl(l_choice_info.pgm_id,-1) then
1208
1209 -- call multi-row edit if per-in-ler has no default nor explict
1210 -- choices available.
1211 if l_cls_enrt_flag then
1212 ben_prtt_enrt_result_api.multi_rows_edit
1213 (p_person_id => p_person_id,
1214 p_effective_date => l_effective_dt,
1215 p_business_group_id => p_business_group_id,
1216 p_pgm_id => l_prev_pgm_id);
1217 end if;
1218 --
1219 -- Invoke process post enrollment
1220 --
1221 if l_prev_pgm_id is not null then
1222 --
1223 -- Invoke post result process
1224 --
1225 ben_proc_common_enrt_rslt.process_post_results
1226 (p_person_id => p_person_id,
1227 p_enrt_mthd_cd => 'A',
1228 --bug#1420160 p_effective_date => l_rec.effective_date,
1229 p_effective_date => l_effective_dt,
1230 p_business_group_id => p_business_group_id,
1231 p_per_in_ler_id => l_per_in_ler_id);
1232 ben_proc_common_enrt_rslt.process_post_enrollment
1233 (p_per_in_ler_id => l_per_in_ler_id,
1234 p_pgm_id => l_prev_pgm_id,
1235 p_pl_id => null,
1236 p_enrt_mthd_cd => 'A',
1237 p_cls_enrt_flag => l_cls_enrt_flag,
1238 p_proc_cd => null,
1239 p_person_id => p_person_id,
1240 p_business_group_id => p_business_group_id,
1241 p_effective_date => l_rec.effective_date);
1242 --
1243 end if;
1244 --
1245 l_prev_pgm_id := l_choice_info.pgm_id;
1246 l_prev_eff_dt := l_effective_dt;
1247 --
1248 end if;
1249 --
1250 end loop;
1251 --
1252 -- close c_choice_info;
1253 --
1254 -- Check if last multi edit passed
1255 --
1256 if l_prev_pgm_id <> -99999 then
1257 --
1258 if l_cls_enrt_flag then
1259 ben_prtt_enrt_result_api.multi_rows_edit
1260 (p_person_id => p_person_id,
1261 p_effective_date => l_prev_eff_dt,
1262 p_business_group_id => p_business_group_id,
1263 p_pgm_id => l_prev_pgm_id);
1264 end if;
1265 --
1266 -- Do post enrollment
1267 --
1268 if l_prev_pgm_id is not null then
1269 --
1270 --
1271 -- Invoke post result process
1272 --
1273 ben_proc_common_enrt_rslt.process_post_results
1274 (p_person_id => p_person_id,
1275 p_enrt_mthd_cd => 'A',
1276 -- bug#1420160 p_effective_date => l_rec.effective_date,
1277 p_effective_date => l_effective_dt,
1278 p_business_group_id => p_business_group_id,
1279 p_per_in_ler_id => l_per_in_ler_id);
1280 ben_proc_common_enrt_rslt.process_post_enrollment
1281 (p_per_in_ler_id => l_per_in_ler_id,
1282 p_pgm_id => l_prev_pgm_id,
1286 p_proc_cd => null,
1283 p_pl_id => null,
1284 p_enrt_mthd_cd => 'A',
1285 p_cls_enrt_flag => l_cls_enrt_flag,
1287 p_person_id => p_person_id,
1288 p_business_group_id => p_business_group_id,
1289 p_effective_date => l_rec.effective_date);
1290 --
1291 end if;
1292 --
1293 --BUG 5529696
1294
1295 elsif (l_cls_enrt_flag and l_prev_pgm_id = -99999) then
1296 --
1297 hr_utility.set_location ('inside -99999', 213);
1298 --
1299 open c_lf_evt_ocrd_dt (l_per_in_ler_id);
1300 --
1301 fetch c_lf_evt_ocrd_dt
1302 into ll_lf_evt_ocrd_dt;
1303 --
1304 close c_lf_evt_ocrd_dt;
1305 --
1306 -- only for programs we need to call multi_rows_edit so for plan not in programs it is not called.
1307 -- Also when election_information is called from benrtchg then only the following case will arise and since
1308 -- in benrtchg, p_effective_date is used as least(p_effective_date,p_lf_evt_ocrd_dt) here also p_effective_date is used
1309 -- in the similar way.
1310
1311 for l_pgm_pnip_id in c_pgm_pnip_id (l_per_in_ler_id)
1312 loop
1313 --
1314 l_cursor := TRUE;
1315 if l_pgm_pnip_id.pgm_id is not null then --PGM
1316 --
1317 l_prev_pgm_id := l_pgm_pnip_id.pgm_id;
1318 --
1319 ben_prtt_enrt_result_api.multi_rows_edit
1320 (p_person_id => p_person_id,
1321 p_effective_date => least(p_effective_date,ll_lf_evt_ocrd_dt),
1322 p_business_group_id => p_business_group_id,
1323 p_pgm_id => l_pgm_pnip_id.pgm_id
1324 );
1325 --
1326 ben_proc_common_enrt_rslt.process_post_results
1327 (p_person_id => p_person_id,
1328 p_enrt_mthd_cd => 'A',
1329 p_effective_date => least(p_effective_date,ll_lf_evt_ocrd_dt),
1330 p_business_group_id => p_business_group_id,
1331 p_per_in_ler_id => l_per_in_ler_id
1332 );
1333 --
1334 elsif l_pgm_pnip_id.pl_id is not null then -- PNIP
1335 --
1336 l_prev_pl_id := l_pgm_pnip_id.pl_id;
1337 --
1338 ben_proc_common_enrt_rslt.process_post_results
1339 (p_person_id => p_person_id,
1340 p_enrt_mthd_cd => 'A',
1341 p_effective_date => least(p_effective_date,ll_lf_evt_ocrd_dt),
1342 p_business_group_id => p_business_group_id,
1343 p_per_in_ler_id => l_per_in_ler_id,
1344 p_pl_id => l_prev_pl_id
1345 );
1346 --
1347 end if;
1348 --
1349 end loop;
1350 --
1351 -- TO CLOSE THE LE
1352 --
1353 if l_cursor then
1354 --
1355 if l_prev_pgm_id <> -99999 and l_prev_pl_id = -99999 then --only plan in pgm exist
1356 --
1357 ben_proc_common_enrt_rslt.process_post_enrollment
1358 (p_per_in_ler_id => l_per_in_ler_id,
1359 p_pgm_id => l_prev_pgm_id,
1360 p_pl_id => null,
1361 p_enrt_mthd_cd => 'A',
1362 p_cls_enrt_flag => l_cls_enrt_flag,
1363 p_proc_cd => null,
1364 p_person_id => p_person_id,
1365 p_business_group_id => p_business_group_id,
1366 p_effective_date => least(p_effective_date, ll_lf_evt_ocrd_dt)
1367 );
1368 --
1369 elsif l_prev_pgm_id = -99999 and l_prev_pl_id <> -99999 then --only plan not in pgm exist
1370 --
1371 ben_proc_common_enrt_rslt.process_post_enrollment
1372 (p_per_in_ler_id => l_per_in_ler_id,
1373 p_pgm_id => null,
1374 p_pl_id => l_prev_pl_id,
1375 p_enrt_mthd_cd => 'A',
1376 p_cls_enrt_flag => l_cls_enrt_flag,
1377 p_proc_cd => null,
1378 p_person_id => p_person_id,
1379 p_business_group_id => p_business_group_id,
1380 p_effective_date => least(p_effective_date,ll_lf_evt_ocrd_dt)
1381 );
1382 --
1383 else
1384 --
1385 ben_proc_common_enrt_rslt.process_post_enrollment --both plan in pgm and not in pgm exist
1386 (p_per_in_ler_id => l_per_in_ler_id,
1387 p_pgm_id => l_prev_pgm_id,
1388 p_pl_id => null,
1389 p_enrt_mthd_cd => 'A',
1390 p_cls_enrt_flag => l_cls_enrt_flag,
1391 p_proc_cd => null,
1395 );
1392 p_person_id => p_person_id,
1393 p_business_group_id => p_business_group_id,
1394 p_effective_date => least(p_effective_date,ll_lf_evt_ocrd_dt)
1396 --
1397 end if;
1398 --
1399 end if;
1400 --
1401 end if;
1402 --
1403 close c_choice_info;
1404 --
1405 --BUG 5529696
1406 --
1407 hr_utility.set_location('Leaving: '||l_proc,10);
1408 --
1409 end process_person;
1410 --
1411 procedure main(p_person_id in number,
1412 p_ler_id in number,
1413 p_business_group_id in number,
1414 p_mode in varchar2,
1415 p_effective_date in date) is
1416 --
1417 l_proc varchar2(80) := g_package||'main';
1418 l_person_id number;
1419 --
1420 cursor c_person_population is
1421 select distinct(pil.person_id) -- Bug 5529696
1422 from ben_per_in_ler pil, ben_pil_elctbl_chc_popl pel
1423 where
1424 pel.dflt_asnd_dt is null
1425 -- and pil.business_group_id = p_business_group_id
1426 and pel.dflt_asnd_dt is null
1427 and pel.elcns_made_dt is null
1428 and pil.person_id = p_person_id
1429 and pil.ler_id = p_ler_id
1430 and pil.per_in_ler_stat_cd = 'STRTD'
1431 and pil.per_in_ler_id = pel.per_in_ler_id
1432 --and pel.business_group_id = pil.business_group_id
1433 and exists(select null
1434 from ben_elig_per_elctbl_chc epe,
1435 ben_pil_elctbl_chc_popl pel
1436 where epe.per_in_ler_id = pil.per_in_ler_id
1437 and epe.per_in_ler_id = pel.per_in_ler_id
1438 and pel.pil_elctbl_chc_popl_id =
1439 epe.pil_elctbl_chc_popl_id
1440 and pel.business_group_id = epe.business_group_id
1441 and epe.business_group_id = pil.business_group_id
1442 and epe.auto_enrt_flag = 'Y');
1443 --
1444 begin
1445 --
1446 hr_utility.set_location('Entering: '||l_proc,10);
1447 hr_utility.set_location('p_person_id'||p_person_id,10);
1448 hr_utility.set_location('p_ler_id'||p_ler_id,10);
1449 hr_utility.set_location('p_business_group_id'||p_business_group_id,10);
1450 hr_utility.set_location('p_mode'||p_mode,10);
1451 hr_utility.set_location('p_effective_date'||p_effective_date,10);
1452 --
1453 -- Code only runs in Life Event or Scheduled Enrollment mode
1454 -- ABSENCES : Automatic enrollments should happen for absence mode.
1455 --
1456 if p_mode not in ('L','C', 'M') then
1457 --
1458 -- Wrong mode my friend!
1459 --
1460 return;
1461 --
1462 end if;
1463 --
1464 -- First derive person population we are going to process
1465 --
1466 open c_person_population;
1467 --
1468 loop
1469 --
1470 fetch c_person_population into l_person_id;
1474 p_business_group_id => p_business_group_id,
1471 exit when c_person_population%notfound;
1472 --
1473 process_person(p_person_id => l_person_id,
1475 p_ler_id => p_ler_id,
1476 p_effective_date => p_effective_date);
1477 --
1478 end loop;
1479 --
1480 close c_person_population;
1481 --
1482 hr_utility.set_location('Leaving: '||l_proc,10);
1483 --
1484 end main;
1485 --
1486 end ben_automatic_enrollments;
1487 --