[Home] [Help]
PACKAGE BODY: APPS.BEN_AUTOMATIC_ENROLLMENTS
Source
1 package body ben_automatic_enrollments as
2 /* $Header: benauten.pkb 120.4.12000000.3 2007/03/07 11:27:58 bmanyam noship $ */
3 --
4 g_package varchar2(30) := 'ben_automatic_enrollments.';
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
102 and oipl.effective_end_date
103 AND pen.prtt_enrt_rslt_stat_cd IS NULL
104 ;
105
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
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
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
243 and p_effective_date between pdp.effective_start_date
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;
363 if l_rslt_id is not null and l_pil_id is not null then
360 end if;
361 end if ; --- p_prev_prtt_enrt_rslt_id
362
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,
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,
450 p_effective_start_date => l_eff_start_date,
451 p_effective_end_date => l_eff_end_date,
452 p_object_version_number => l_object_version_number,
453 p_multi_row_actn => TRUE );
454 --
455 ben_sspndd_enrollment.g_cfw_dpnt_flag := 'N' ;
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;
463 --
460 end loop;
461 End if ;
462 end if;
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'
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
595 and pen.enrt_cvg_thru_dt = hr_api.g_eot
592 from ben_prtt_enrt_rslt_f pen
593 where pen.pgm_id = epe.pgm_id
594 and pen.per_in_ler_id = epe.per_in_ler_id
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
656 l_pl_enrt_dt c_pl_enrt_dt%rowtype;
657
658 cursor c_bnft(v_elig_per_elctbl_chc_id number) is
659 select enb.enrt_bnft_id,
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;
710 type g_rt_rec is record
707 --
708 l_cvg_strt_dt date;
709
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;
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;
797 l_cryfwd_elig_dpnt_cd varchar2(30) ;
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
846 fetch c_pgm_enrt_dt into l_pgm_enrt_dt;
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);
847 if l_pgm_enrt_dt.enrt_perd_strt_dt < l_rec.effective_date then
848 l_effective_dt := l_pgm_enrt_dt.enrt_perd_strt_dt;
849 else
850 l_effective_dt := l_rec.effective_date;
851 end if;
852 close c_pgm_enrt_dt;
853 elsif l_choice_info.pl_id is not null then
854 open c_pl_enrt_dt(l_choice_info.elig_per_elctbl_chc_id,
855 l_choice_info.pl_id);
856 fetch c_pl_enrt_dt into l_pl_enrt_dt;
857 if l_pl_enrt_dt.enrt_perd_strt_dt < l_rec.effective_date then
858 l_effective_dt := l_pl_enrt_dt.enrt_perd_strt_dt;
859 else
860 l_effective_dt := l_rec.effective_date;
861 end if;
862 close c_pl_enrt_dt;
863 end if;
864 -- Get participant enrollment result information
865
866 hr_utility.set_location('cvg strt cd: '||l_choice_info.dpnt_cvg_strt_dt_cd,10);
867 hr_utility.set_location('epe id : '||l_choice_info.elig_per_elctbl_chc_id,10);
868 hr_utility.set_location('l_effective_dt'||l_effective_dt,11);
869 --
870 if l_choice_info.crntly_enrd_flag = 'Y' then
871 --
872 open c_prtt_result(l_choice_info.prtt_enrt_rslt_id,
873 l_effective_dt);
874 --
875 fetch c_prtt_result into l_prtt_result;
876 if c_prtt_result%notfound then
877 --
878 close c_prtt_result;
879 fnd_message.set_name('BEN','BEN_91711_ENRT_RSLT_NOT_FOUND');
880 fnd_message.set_token('PROC',l_proc);
881 fnd_message.set_token('ID',l_choice_info.prtt_enrt_rslt_id);
882 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
883 fnd_message.set_token('LER_ID',to_char(p_ler_id));
884 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
885 fnd_message.raise_error;
886 --
887 end if;
888 --
889 close c_prtt_result;
890 --
891 l_new_election := false;
892 l_datetrack_mode := hr_api.g_update;
893 l_choice_info.stage := 'N';
894 --
895 else
896 --
897 l_new_election := true;
898 l_datetrack_mode := hr_api.g_insert;
899 l_choice_info.stage := 'Y';
900 --
901 --Bug 5589466 : Reset l_prtt_result if the current election is a new one
902 l_prtt_result := null;
903 --End 5589466
904
905 end if;
906 --
907 -- Get benefit information
908 --
909 l_dflt_found:=false;
910 hr_utility.set_location(l_proc,20);
911 open c_bnft(l_choice_info.elig_per_elctbl_chc_id);
912 loop
913 hr_utility.set_location(l_proc,30);
914 --
915 fetch c_bnft into l_bnft;
916 --
917 exit when c_bnft%notfound;
918 hr_utility.set_location(l_proc,40);
919 if l_bnft.dflt_flag='Y' then
920 hr_utility.set_location(l_proc,50);
921 l_dflt_bnft:=l_bnft;
922 l_dflt_found:=true;
923 end if;
924 hr_utility.set_location(l_proc,60);
925 end loop;
926 hr_utility.set_location(l_proc,70);
927 close c_bnft;
928 if l_dflt_found=true then
929 hr_utility.set_location(l_proc,80);
930 l_bnft:=l_dflt_bnft;
931 end if;
932 hr_utility.set_location(l_proc,90);
933 --
934 -- Get Rate information
935 --
936 for l_count in 1..10 loop
937 --
938 -- Initialise array to null
939 --
940 l_rt_table(l_count).enrt_rt_id := null;
941 l_rt_table(l_count).dflt_val := null;
942 l_rt_table(l_count).prtt_rt_val_id:=null;
943 --
944 end loop;
945 --
946 l_count:= 0;
947 --
948 for l_rec in c_rt(l_choice_info.elig_per_elctbl_chc_id,
949 l_bnft.enrt_bnft_id) loop
950 --
951 l_count := l_count+1;
952 l_rt_table(l_count).enrt_rt_id := l_rec.enrt_rt_id;
953 l_rt_table(l_count).dflt_val := l_rec.default_value;
954 l_rt_table(l_count).prtt_rt_val_id:=l_rec.prtt_rt_val_id;
955 --
956 end loop;
957 --
958 l_suspend_flag := 'N';
959 --
960 -- Call election information batch process
961 --
962 /* moved above to pass the l_effective_dt to cursor to get current
963 * enrollment
964 if l_choice_info.pgm_id is not null then
965 open c_pgm_enrt_dt(l_choice_info.elig_per_elctbl_chc_id,
966 l_choice_info.pgm_id);
967 fetch c_pgm_enrt_dt into l_pgm_enrt_dt;
968 if l_pgm_enrt_dt.enrt_perd_strt_dt < l_rec.effective_date then
969 l_effective_dt := l_pgm_enrt_dt.enrt_perd_strt_dt;
970 else
971 l_effective_dt := l_rec.effective_date;
972 end if;
973 close c_pgm_enrt_dt;
977 fetch c_pl_enrt_dt into l_pl_enrt_dt;
974 elsif l_choice_info.pl_id is not null then
975 open c_pl_enrt_dt(l_choice_info.elig_per_elctbl_chc_id,
976 l_choice_info.pl_id);
978 if l_pl_enrt_dt.enrt_perd_strt_dt < l_rec.effective_date then
979 l_effective_dt := l_pl_enrt_dt.enrt_perd_strt_dt;
980 else
981 l_effective_dt := l_rec.effective_date;
982 end if;
983 close c_pl_enrt_dt;
984 end if;
985 */
986 hr_utility.set_location('cvg_mlt_cd='||l_bnft.cvg_mlt_cd,13);
987 hr_utility.set_location('bnft_val='||l_bnft.val,13);
988 hr_utility.set_location('rate_val='||l_rt_table(1).dflt_val,13);
989 hr_utility.set_location('ann_rt_val='||l_rt_table(1).ann_rt_val,13);
990 if l_bnft.cvg_mlt_cd='SAAEAR' and
991 l_rt_table(1).ann_rt_val is null then
992 l_rt_table(1).ann_rt_val:=l_rt_table(1).dflt_val;
993 end if;
994 --hr_utility.set_location('ann_rt_val='||l_rt_table(1).ann_rt_val,13);
995 ben_election_information.election_information
996 (p_elig_per_elctbl_chc_id => l_choice_info.elig_per_elctbl_chc_id,
997 p_prtt_enrt_rslt_id => l_choice_info.prtt_enrt_rslt_id,
998 p_effective_date => l_effective_dt,
999 p_enrt_mthd_cd => 'A',
1000 p_business_group_id => p_business_group_id,
1001 p_enrt_bnft_id => l_bnft.enrt_bnft_id,
1002 p_bnft_val => l_bnft.val,
1003 p_enrt_rt_id1 => l_rt_table(1).enrt_rt_id,
1004 p_rt_val1 => l_rt_table(1).dflt_val,
1005 p_enrt_rt_id2 => l_rt_table(2).enrt_rt_id,
1006 p_rt_val2 => l_rt_table(2).dflt_val,
1007 p_enrt_rt_id3 => l_rt_table(3).enrt_rt_id,
1008 p_rt_val3 => l_rt_table(3).dflt_val,
1009 p_enrt_rt_id4 => l_rt_table(4).enrt_rt_id,
1010 p_rt_val4 => l_rt_table(4).dflt_val,
1011 p_enrt_rt_id5 => l_rt_table(5).enrt_rt_id,
1012 p_rt_val5 => l_rt_table(5).dflt_val,
1013 p_enrt_rt_id6 => l_rt_table(6).enrt_rt_id,
1014 p_rt_val6 => l_rt_table(6).dflt_val,
1015 p_enrt_rt_id7 => l_rt_table(7).enrt_rt_id,
1016 p_rt_val7 => l_rt_table(7).dflt_val,
1017 p_enrt_rt_id8 => l_rt_table(8).enrt_rt_id,
1018 p_rt_val8 => l_rt_table(8).dflt_val,
1019 p_enrt_rt_id9 => l_rt_table(9).enrt_rt_id,
1020 p_rt_val9 => l_rt_table(9).dflt_val,
1021 p_enrt_rt_id10 => l_rt_table(10).enrt_rt_id,
1022 p_rt_val10 => l_rt_table(10).dflt_val,
1023 p_datetrack_mode => l_datetrack_mode,
1024 p_suspend_flag => l_suspend_flag,
1025 p_prtt_enrt_interim_id => l_prtt_enrt_interim_id,
1026 p_prtt_rt_val_id1 => l_rt_table(1).prtt_rt_val_id,
1027 p_prtt_rt_val_id2 => l_rt_table(2).prtt_rt_val_id,
1028 p_prtt_rt_val_id3 => l_rt_table(3).prtt_rt_val_id,
1029 p_prtt_rt_val_id4 => l_rt_table(4).prtt_rt_val_id,
1030 p_prtt_rt_val_id5 => l_rt_table(5).prtt_rt_val_id,
1031 p_prtt_rt_val_id6 => l_rt_table(6).prtt_rt_val_id,
1032 p_prtt_rt_val_id7 => l_rt_table(7).prtt_rt_val_id,
1033 p_prtt_rt_val_id8 => l_rt_table(8).prtt_rt_val_id,
1034 p_prtt_rt_val_id9 => l_rt_table(9).prtt_rt_val_id,
1035 p_prtt_rt_val_id10 => l_rt_table(10).prtt_rt_val_id,
1036 p_ann_rt_val1 => l_rt_table(1).ann_rt_val,
1037 p_ann_rt_val2 => l_rt_table(2).ann_rt_val,
1038 p_ann_rt_val3 => l_rt_table(3).ann_rt_val,
1039 p_ann_rt_val4 => l_rt_table(4).ann_rt_val,
1040 p_ann_rt_val5 => l_rt_table(5).ann_rt_val,
1041 p_ann_rt_val6 => l_rt_table(6).ann_rt_val,
1042 p_ann_rt_val7 => l_rt_table(7).ann_rt_val,
1043 p_ann_rt_val8 => l_rt_table(8).ann_rt_val,
1044 p_ann_rt_val9 => l_rt_table(9).ann_rt_val,
1045 p_ann_rt_val10 => l_rt_table(10).ann_rt_val,
1046 -- 3517682 start
1047 p_pen_attribute_category =>l_prtt_result.pen_attribute_category ,
1048 p_pen_attribute1 =>l_prtt_result.pen_attribute1 ,
1049 p_pen_attribute2 =>l_prtt_result.pen_attribute2 ,
1050 p_pen_attribute3 =>l_prtt_result.pen_attribute3 ,
1051 p_pen_attribute4 =>l_prtt_result.pen_attribute4 ,
1052 p_pen_attribute5 =>l_prtt_result.pen_attribute5 ,
1053 p_pen_attribute6 =>l_prtt_result.pen_attribute6 ,
1054 p_pen_attribute7 =>l_prtt_result.pen_attribute7 ,
1055 p_pen_attribute8 =>l_prtt_result.pen_attribute8 ,
1056 p_pen_attribute9 =>l_prtt_result.pen_attribute9 ,
1057 p_pen_attribute10 =>l_prtt_result.pen_attribute10 ,
1058 p_pen_attribute11 =>l_prtt_result.pen_attribute11 ,
1059 p_pen_attribute12 =>l_prtt_result.pen_attribute12 ,
1060 p_pen_attribute13 =>l_prtt_result.pen_attribute13 ,
1061 p_pen_attribute14 =>l_prtt_result.pen_attribute14 ,
1062 p_pen_attribute15 =>l_prtt_result.pen_attribute15 ,
1063 p_pen_attribute16 =>l_prtt_result.pen_attribute16 ,
1064 p_pen_attribute17 =>l_prtt_result.pen_attribute17 ,
1065 p_pen_attribute18 =>l_prtt_result.pen_attribute18 ,
1069 p_pen_attribute22 =>l_prtt_result.pen_attribute22,
1066 p_pen_attribute19 =>l_prtt_result.pen_attribute19 ,
1067 p_pen_attribute20 =>l_prtt_result.pen_attribute20 ,
1068 p_pen_attribute21 =>l_prtt_result.pen_attribute21 ,
1070 p_pen_attribute23 =>l_prtt_result.pen_attribute23,
1071 p_pen_attribute24 =>l_prtt_result.pen_attribute24,
1072 p_pen_attribute25 =>l_prtt_result.pen_attribute25,
1073 p_pen_attribute26 =>l_prtt_result.pen_attribute26,
1074 p_pen_attribute27 =>l_prtt_result.pen_attribute27,
1075 p_pen_attribute28 =>l_prtt_result.pen_attribute28,
1076 p_pen_attribute29 =>l_prtt_result.pen_attribute29,
1077 p_pen_attribute30 =>l_prtt_result.pen_attribute30,
1078 -- 3517682 end
1079 p_object_version_number => l_prtt_result.object_version_number,
1080 p_effective_start_date => l_effective_start_date,
1081 p_effective_end_date => l_effective_end_date,
1082 p_dpnt_actn_warning => l_dpnt_actn_warning,
1083 p_bnf_actn_warning => l_bnf_actn_warning,
1084 p_ctfn_actn_warning => l_ctfn_actn_warning);
1085 --
1086 l_choice_info.suspended := l_suspend_flag;
1087 --
1088 if l_choice_info.suspended = 'Y' then
1089 --
1090 l_person_susp := 'Y';
1091 l_choice_info.stage := 'S';
1092 --
1093 end if;
1094 --
1095 open c_prtt_result(l_choice_info.prtt_enrt_rslt_id,
1096 l_effective_dt);
1097 --
1098 fetch c_prtt_result into l_prtt_result;
1099 if c_prtt_result%notfound then
1100 --
1101 close c_prtt_result;
1102 fnd_message.set_name('BEN','BEN_91711_ENRT_RSLT_NOT_FOUND');
1103 fnd_message.set_token('PROC',l_proc);
1104 fnd_message.set_token('ID',l_choice_info.prtt_enrt_rslt_id);
1105 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1106 fnd_message.set_token('LER_ID',to_char(p_ler_id));
1107 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
1108 fnd_message.raise_error;
1109 --
1110 else
1111 --
1112 l_choice_info.enrt_cvg_strt_dt := l_prtt_result.enrt_cvg_strt_dt;
1113 l_choice_info.enrt_cvg_end_dt := l_prtt_result.enrt_cvg_thru_dt;
1114 --
1115 -- after the enhncemnt # 2685018 cryfwd_elig_dpnt_cd value is concated with
1116 -- result id from where the dpnt carry forwarded , this will seprate the code from
1117 --- result id
1118
1119 l_prev_prtt_enrt_rslt_id := null; -- Reintializing the previous enrt result id
1120 l_cryfwd_elig_dpnt_cd := l_choice_info.cryfwd_elig_dpnt_cd ;
1121 l_prev_rslt_id_at := instr(l_cryfwd_elig_dpnt_cd, '^') ;
1122 --- if the result id concated with the code, then the caht '^' must be aprt of the
1123 --- the code
1124
1125 if l_prev_rslt_id_at > 0 then
1126 --- if the to_number errors , catch the exception
1127 Begin
1128 l_prev_prtt_enrt_rslt_id := to_number(substr(l_cryfwd_elig_dpnt_cd,l_prev_rslt_id_at+1) );
1129 Exception
1130 when value_error then
1131 l_prev_prtt_enrt_rslt_id := null;
1132 End ;
1133
1134 l_cryfwd_elig_dpnt_cd := substr(l_cryfwd_elig_dpnt_cd,1,l_prev_rslt_id_at-1) ;
1135 ---
1136 end if ;
1137
1138
1139 hr_utility.set_location('l_cryfwd_elig_dpnt_cd '||l_cryfwd_elig_dpnt_cd,744);
1140 hr_utility.set_location('l_prev_prtt_enrt_rslt_id '||l_prev_prtt_enrt_rslt_id,744);
1141
1142 if l_datetrack_mode = hr_api.g_insert and l_cryfwd_elig_dpnt_cd = 'CFRRWP' then
1143
1144 hr_utility.set_location('cvg strt cd: '||l_choice_info.dpnt_cvg_strt_dt_cd,10);
1145 -- p_effective_date is now changed to l_effective_dt , when ever LE reprocessed
1146 -- result created as on effective date and automeatic enrollment called with
1147 -- lE_ocurd_Dt as affective date so there is no result as on effective date (le_ocrd_dt)
1148 -- this is fixed sending l_effective_dt # 3042033
1149
1150 reinstate_dpnt(p_pgm_id =>l_choice_info.pgm_id,
1151 p_pl_id => l_choice_info.pl_id,
1152 p_oipl_id => l_choice_info.oipl_id,
1153 p_business_group_id => p_business_group_id,
1154 p_person_id => p_person_id,
1155 p_per_in_ler_id => l_per_in_ler_id,
1156 p_elig_per_elctbl_chc_id => l_choice_info.elig_per_elctbl_chc_id,
1157 p_dpnt_cvg_strt_dt_cd => l_choice_info.dpnt_cvg_strt_dt_cd,
1158 p_dpnt_cvg_strt_dt_rl => l_choice_info.dpnt_cvg_strt_dt_rl,
1159 p_enrt_cvg_strt_dt => l_choice_info.enrt_cvg_strt_dt,
1160 p_effective_date => l_effective_dt,
1161 p_prev_prtt_enrt_rslt_id => l_prev_prtt_enrt_rslt_id
1162 );
1163 end if;
1164 end if;
1165 --
1166 close c_prtt_result;
1167 --
1171 --
1168 -- Do Post enrollment - Writes elecment entries, calls close enrollment
1169 --
1170 if l_choice_info.pgm_id is null then
1172 -- Invoke post result process
1173 --
1174 ben_proc_common_enrt_rslt.process_post_results
1175 (p_person_id => p_person_id,
1176 p_enrt_mthd_cd => 'A',
1177 p_effective_date => l_effective_dt,
1178 p_business_group_id => p_business_group_id,
1179 p_per_in_ler_id => l_per_in_ler_id);
1180 ben_proc_common_enrt_rslt.process_post_enrollment
1181 (p_per_in_ler_id => l_per_in_ler_id,
1182 p_pgm_id => l_choice_info.pgm_id,
1183 p_pl_id => l_choice_info.pl_id,
1184 p_cls_enrt_flag => l_cls_enrt_flag,
1185 p_enrt_mthd_cd => 'A',
1186 p_proc_cd => null,
1187 p_person_id => p_person_id,
1188 p_business_group_id => p_business_group_id,
1189 p_effective_date => l_rec.effective_date);
1190 --
1191 end if;
1192 --
1193 -- Do multi row edit stuff
1194 --
1195 if l_prev_pgm_id = -99999 then
1196 --
1197 l_prev_pgm_id := l_choice_info.pgm_id;
1198 l_prev_eff_dt := l_effective_dt;
1199 --
1200 elsif nvl(l_prev_pgm_id,-1) <> nvl(l_choice_info.pgm_id,-1) then
1201
1202 -- call multi-row edit if per-in-ler has no default nor explict
1203 -- choices available.
1204 if l_cls_enrt_flag then
1205 ben_prtt_enrt_result_api.multi_rows_edit
1206 (p_person_id => p_person_id,
1207 p_effective_date => l_effective_dt,
1208 p_business_group_id => p_business_group_id,
1209 p_pgm_id => l_prev_pgm_id);
1210 end if;
1211 --
1212 -- Invoke process post enrollment
1213 --
1214 if l_prev_pgm_id is not null then
1215 --
1216 -- Invoke post result process
1217 --
1218 ben_proc_common_enrt_rslt.process_post_results
1219 (p_person_id => p_person_id,
1220 p_enrt_mthd_cd => 'A',
1221 --bug#1420160 p_effective_date => l_rec.effective_date,
1222 p_effective_date => l_effective_dt,
1223 p_business_group_id => p_business_group_id,
1224 p_per_in_ler_id => l_per_in_ler_id);
1225 ben_proc_common_enrt_rslt.process_post_enrollment
1226 (p_per_in_ler_id => l_per_in_ler_id,
1227 p_pgm_id => l_prev_pgm_id,
1228 p_pl_id => null,
1229 p_enrt_mthd_cd => 'A',
1230 p_cls_enrt_flag => l_cls_enrt_flag,
1231 p_proc_cd => null,
1232 p_person_id => p_person_id,
1233 p_business_group_id => p_business_group_id,
1234 p_effective_date => l_rec.effective_date);
1235 --
1236 end if;
1237 --
1238 l_prev_pgm_id := l_choice_info.pgm_id;
1239 l_prev_eff_dt := l_effective_dt;
1240 --
1241 end if;
1242 --
1243 end loop;
1244 --
1245 -- close c_choice_info;
1246 --
1247 -- Check if last multi edit passed
1248 --
1249 if l_prev_pgm_id <> -99999 then
1250 --
1251 if l_cls_enrt_flag then
1252 ben_prtt_enrt_result_api.multi_rows_edit
1253 (p_person_id => p_person_id,
1254 p_effective_date => l_prev_eff_dt,
1255 p_business_group_id => p_business_group_id,
1256 p_pgm_id => l_prev_pgm_id);
1257 end if;
1258 --
1259 -- Do post enrollment
1260 --
1261 if l_prev_pgm_id is not null then
1262 --
1263 --
1264 -- Invoke post result process
1265 --
1266 ben_proc_common_enrt_rslt.process_post_results
1267 (p_person_id => p_person_id,
1268 p_enrt_mthd_cd => 'A',
1269 -- bug#1420160 p_effective_date => l_rec.effective_date,
1270 p_effective_date => l_effective_dt,
1271 p_business_group_id => p_business_group_id,
1272 p_per_in_ler_id => l_per_in_ler_id);
1273 ben_proc_common_enrt_rslt.process_post_enrollment
1274 (p_per_in_ler_id => l_per_in_ler_id,
1275 p_pgm_id => l_prev_pgm_id,
1276 p_pl_id => null,
1277 p_enrt_mthd_cd => 'A',
1278 p_cls_enrt_flag => l_cls_enrt_flag,
1279 p_proc_cd => null,
1280 p_person_id => p_person_id,
1281 p_business_group_id => p_business_group_id,
1282 p_effective_date => l_rec.effective_date);
1283 --
1284 end if;
1285 --
1286 --BUG 5529696
1287
1288 elsif (l_cls_enrt_flag and l_prev_pgm_id = -99999) then
1289 --
1290 hr_utility.set_location ('inside -99999', 213);
1294 fetch c_lf_evt_ocrd_dt
1291 --
1292 open c_lf_evt_ocrd_dt (l_per_in_ler_id);
1293 --
1295 into ll_lf_evt_ocrd_dt;
1296 --
1297 close c_lf_evt_ocrd_dt;
1298 --
1299 -- only for programs we need to call multi_rows_edit so for plan not in programs it is not called.
1300 -- Also when election_information is called from benrtchg then only the following case will arise and since
1301 -- in benrtchg, p_effective_date is used as least(p_effective_date,p_lf_evt_ocrd_dt) here also p_effective_date is used
1302 -- in the similar way.
1303
1304 for l_pgm_pnip_id in c_pgm_pnip_id (l_per_in_ler_id)
1305 loop
1306 --
1307 l_cursor := TRUE;
1308 if l_pgm_pnip_id.pgm_id is not null then --PGM
1309 --
1310 l_prev_pgm_id := l_pgm_pnip_id.pgm_id;
1311 --
1312 ben_prtt_enrt_result_api.multi_rows_edit
1313 (p_person_id => p_person_id,
1314 p_effective_date => least(p_effective_date,ll_lf_evt_ocrd_dt),
1315 p_business_group_id => p_business_group_id,
1316 p_pgm_id => l_pgm_pnip_id.pgm_id
1317 );
1318 --
1319 ben_proc_common_enrt_rslt.process_post_results
1320 (p_person_id => p_person_id,
1321 p_enrt_mthd_cd => 'A',
1322 p_effective_date => least(p_effective_date,ll_lf_evt_ocrd_dt),
1323 p_business_group_id => p_business_group_id,
1324 p_per_in_ler_id => l_per_in_ler_id
1325 );
1326 --
1327 elsif l_pgm_pnip_id.pl_id is not null then -- PNIP
1328 --
1329 l_prev_pl_id := l_pgm_pnip_id.pl_id;
1330 --
1331 ben_proc_common_enrt_rslt.process_post_results
1332 (p_person_id => p_person_id,
1333 p_enrt_mthd_cd => 'A',
1334 p_effective_date => least(p_effective_date,ll_lf_evt_ocrd_dt),
1335 p_business_group_id => p_business_group_id,
1336 p_per_in_ler_id => l_per_in_ler_id,
1337 p_pl_id => l_prev_pl_id
1338 );
1339 --
1340 end if;
1341 --
1342 end loop;
1343 --
1344 -- TO CLOSE THE LE
1345 --
1346 if l_cursor then
1347 --
1348 if l_prev_pgm_id <> -99999 and l_prev_pl_id = -99999 then --only plan in pgm exist
1349 --
1350 ben_proc_common_enrt_rslt.process_post_enrollment
1351 (p_per_in_ler_id => l_per_in_ler_id,
1352 p_pgm_id => l_prev_pgm_id,
1353 p_pl_id => null,
1354 p_enrt_mthd_cd => 'A',
1355 p_cls_enrt_flag => l_cls_enrt_flag,
1356 p_proc_cd => null,
1357 p_person_id => p_person_id,
1358 p_business_group_id => p_business_group_id,
1359 p_effective_date => least(p_effective_date, ll_lf_evt_ocrd_dt)
1360 );
1361 --
1362 elsif l_prev_pgm_id = -99999 and l_prev_pl_id <> -99999 then --only plan not in pgm exist
1363 --
1364 ben_proc_common_enrt_rslt.process_post_enrollment
1365 (p_per_in_ler_id => l_per_in_ler_id,
1366 p_pgm_id => null,
1367 p_pl_id => l_prev_pl_id,
1368 p_enrt_mthd_cd => 'A',
1369 p_cls_enrt_flag => l_cls_enrt_flag,
1370 p_proc_cd => null,
1371 p_person_id => p_person_id,
1372 p_business_group_id => p_business_group_id,
1373 p_effective_date => least(p_effective_date,ll_lf_evt_ocrd_dt)
1374 );
1375 --
1376 else
1377 --
1378 ben_proc_common_enrt_rslt.process_post_enrollment --both plan in pgm and not in pgm exist
1379 (p_per_in_ler_id => l_per_in_ler_id,
1380 p_pgm_id => l_prev_pgm_id,
1381 p_pl_id => null,
1382 p_enrt_mthd_cd => 'A',
1383 p_cls_enrt_flag => l_cls_enrt_flag,
1384 p_proc_cd => null,
1385 p_person_id => p_person_id,
1389 --
1386 p_business_group_id => p_business_group_id,
1387 p_effective_date => least(p_effective_date,ll_lf_evt_ocrd_dt)
1388 );
1390 end if;
1391 --
1392 end if;
1393 --
1394 end if;
1395 --
1396 close c_choice_info;
1397 --
1398 --BUG 5529696
1399 --
1400 hr_utility.set_location('Leaving: '||l_proc,10);
1401 --
1402 end process_person;
1403 --
1404 procedure main(p_person_id in number,
1405 p_ler_id in number,
1406 p_business_group_id in number,
1407 p_mode in varchar2,
1408 p_effective_date in date) is
1409 --
1410 l_proc varchar2(80) := g_package||'main';
1411 l_person_id number;
1412 --
1413 cursor c_person_population is
1414 select distinct(pil.person_id) -- Bug 5529696
1415 from ben_per_in_ler pil, ben_pil_elctbl_chc_popl pel
1416 where
1417 pel.dflt_asnd_dt is null
1418 -- and pil.business_group_id = p_business_group_id
1419 and pel.dflt_asnd_dt is null
1420 and pel.elcns_made_dt is null
1421 and pil.person_id = p_person_id
1422 and pil.ler_id = p_ler_id
1423 and pil.per_in_ler_stat_cd = 'STRTD'
1424 and pil.per_in_ler_id = pel.per_in_ler_id
1425 --and pel.business_group_id = pil.business_group_id
1426 and exists(select null
1427 from ben_elig_per_elctbl_chc epe,
1428 ben_pil_elctbl_chc_popl pel
1429 where epe.per_in_ler_id = pil.per_in_ler_id
1430 and epe.per_in_ler_id = pel.per_in_ler_id
1431 and pel.pil_elctbl_chc_popl_id =
1432 epe.pil_elctbl_chc_popl_id
1433 and pel.business_group_id = epe.business_group_id
1434 and epe.business_group_id = pil.business_group_id
1435 and epe.auto_enrt_flag = 'Y');
1436 --
1437 begin
1438 --
1439 hr_utility.set_location('Entering: '||l_proc,10);
1440 hr_utility.set_location('p_person_id'||p_person_id,10);
1441 hr_utility.set_location('p_ler_id'||p_ler_id,10);
1442 hr_utility.set_location('p_business_group_id'||p_business_group_id,10);
1443 hr_utility.set_location('p_mode'||p_mode,10);
1444 hr_utility.set_location('p_effective_date'||p_effective_date,10);
1445 --
1446 -- Code only runs in Life Event or Scheduled Enrollment mode
1447 -- ABSENCES : Automatic enrollments should happen for absence mode.
1448 --
1449 if p_mode not in ('L','C', 'M') then
1450 --
1451 -- Wrong mode my friend!
1452 --
1453 return;
1454 --
1455 end if;
1456 --
1457 -- First derive person population we are going to process
1458 --
1459 open c_person_population;
1460 --
1461 loop
1462 --
1463 fetch c_person_population into l_person_id;
1464 exit when c_person_population%notfound;
1465 --
1466 process_person(p_person_id => l_person_id,
1467 p_business_group_id => p_business_group_id,
1468 p_ler_id => p_ler_id,
1469 p_effective_date => p_effective_date);
1470 --
1471 end loop;
1472 --
1473 close c_person_population;
1474 --
1475 hr_utility.set_location('Leaving: '||l_proc,10);
1476 --
1477 end main;
1478 --
1479 end ben_automatic_enrollments;
1480 --