[Home] [Help]
PACKAGE BODY: APPS.BEN_CARRY_FORWARD_ITEMS
Source
4 g_package varchar2(31) := 'ben_carry_forward_items.';
1 package body ben_carry_forward_items as
2 /* $Header: bencfwsu.pkb 120.30 2011/05/17 11:47:51 nchinnam noship $ */
3
5 g_debug boolean := false;
6
7 procedure main
8 (p_person_id number,
9 p_per_in_ler_id number,
10 p_ler_id number,
11 p_effective_date date,
12 p_lf_evt_ocrd_dt date,
13 p_business_group_id number
14 -- p_called_from varchar2
15 ) is
16
17 l_proc varchar2(72) := g_package||'main';
18 l_act_effective_date date;
19 l_effective_start_date date;
20 l_effective_end_date date;
21 l_effective_date date;
22
23 cursor c_pen is
24 select pen.*
25 from ben_prtt_enrt_rslt_f pen
26 ,ben_per_in_ler pil
27 ,ben_ler_f ler
28 where pen.person_id = p_person_id
29 and pen.prtt_enrt_rslt_stat_cd is null
30 and pen.sspndd_flag = 'Y'
31 and pen.business_group_id = p_business_group_id
32 and pil.per_in_ler_id = pen.per_in_ler_id
33 and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
34 and pil.lf_evt_ocrd_dt <= p_lf_evt_ocrd_dt
35 and pil.ler_id = ler.ler_id
36 and ler.typ_cd not in ('SCHEDDU', 'COMP', 'ABS','GSP')
37 and ler.business_group_id = pil.business_group_id
38 and p_effective_date between
39 ler.effective_start_date and ler.effective_end_date
40 and ((p_effective_date between
41 pen.effective_start_date and pen.effective_end_date)
45 l_pen_rec c_pen%rowtype;
42 or (p_lf_evt_ocrd_dt <= pen.effective_start_date)) -- 5741760: PEN recs on a Future date shud also be carried fwd.
43 and pen.enrt_cvg_thru_dt = hr_api.g_eot
44 and pen.effective_end_date = hr_api.g_eot;
46 --
47 cursor c_get_actn_items_sus(p_prtt_enrt_rslt_id number,
48 p_eff_dt date) is
49 select pea.*
50 from ben_prtt_enrt_actn_f pea
51 ,ben_per_in_ler pil
52 where pea.business_group_id = p_business_group_id
53 and pea.cmpltd_dt is null
54 and pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
55 and pil.per_in_ler_id = pea.per_in_ler_id
56 and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
57 and p_eff_dt between
58 pea.effective_start_date and pea.effective_end_date
59 order by pea.rqd_flag desc,pea.due_dt asc;
60 l_act_sus c_get_actn_items_sus%rowtype;
61 --
62 cursor c_get_opt_actn_items_unsus(p_eff_dt date) is
63 select pea.*,
64 pen.object_version_number pen_ovn
65 from ben_prtt_enrt_actn_f pea
66 ,ben_prtt_enrt_rslt_f pen
67 ,ben_per_in_ler pil
68 ,ben_ler_f ler
69 where pea.business_group_id = p_business_group_id
70 and pea.cmpltd_dt is null
71 and pea.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
72 and pen.person_id = p_person_id
73 and pen.prtt_enrt_rslt_stat_cd is null
74 and pen.sspndd_flag = 'N'
75 and pen.business_group_id = pea.business_group_id
76 and pil.per_in_ler_id = pea.per_in_ler_id
77 and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
78 and pil.ler_id = ler.ler_id
79 and p_eff_dt between
80 ler.effective_start_date and ler.effective_end_date
81 -- For Bug 6941981 Added an option
82 -- and ler.typ_cd not in ('COMP', 'ABS', 'GSP')
83 and ler.typ_cd not in ('SCHEDDU','COMP', 'ABS', 'GSP')
84 and p_eff_dt between
85 pea.effective_start_date and pea.effective_end_date
86 and p_eff_dt between
87 pen.effective_start_date and pen.effective_end_date
88 and nvl(pen.enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
89 and pen.effective_end_date = hr_api.g_eot;
90 l_opt_unsus c_get_opt_actn_items_unsus%rowtype;
91 --
92 cursor c_epe(p_per_in_ler_id number) is
93 select 'Y' epe_found_flag
94 from ben_elig_per_elctbl_chc epe
95 where per_in_ler_id = p_per_in_ler_id
96 and (epe.pgm_id is NULL or
97 epe.pgm_id = l_pen_rec.pgm_id )
98 and (epe.oipl_id is NULL or
99 epe.oipl_id = l_pen_rec.oipl_id )
100 and epe.pl_id = l_pen_rec.pl_id ;
101 --
102 l_epe_found_flag varchar2(30) := 'N' ;
103 --
104 /* Cursor Not being used in the code
105 cursor c_enrt_window is
106 select enrt_perd_strt_dt
107 from ben_pil_elctbl_chc_popl
108 where pil_elctbl_chc_popl_id = l_epe_rec.pil_elctbl_chc_popl_id;
109 --
110 */
111 cursor c_min_max_date (p_prtt_enrt_rslt_id number) is
112 select min(effective_start_date),
113 max(effective_end_date)
114 from ben_prtt_enrt_rslt_f
115 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
116 --
117 cursor c_pen_ovn(p_eff_dt date) is
118 select object_version_number
119 from ben_prtt_enrt_rslt_f
120 where prtt_enrt_rslt_id = l_pen_rec.prtt_enrt_rslt_id
121 and prtt_enrt_rslt_stat_cd is null
122 and p_eff_dt between effective_start_date
123 and effective_end_date;
124 --
125 cursor c_get_enrt_bnft (p_elig_per_elctbl_chc_id number) is
126 select enrt_bnft_id
127 from ben_elctbl_chc_ctfn
128 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
129 and rqd_flag = 'Y'
130 and business_group_id = p_business_group_id;
131 l_get_enrt_bnft c_get_enrt_bnft%rowtype;
132
133
134 l_min_start_date date;
135 l_max_end_date date;
136 l_pea_effective_date date;
137 l_datetrack_mode varchar2(30);
138 l_correction boolean;
139 l_update boolean;
140 l_update_override boolean;
141 l_update_change_insert boolean;
142 l_act_item_expired boolean := false;
143 l_enrt_perd_strt_dt date;
144 l_object_version_number number;
145 l_rslt_object_version_number number;
146 l_use_enrt_bnft boolean := false;
147 l_suspend_flag varchar2(30);
148 l_dpnt_actn_warning boolean;
149 l_bnf_actn_warning boolean;
150 l_ctfn_actn_warning boolean;
151 l_dummy_number number;
152 l_dummy_boolean boolean;
153 l_dummy_char varchar2(30);
154 l_dummy_date date;
155 l_prev_popl_id number := -1;
156
157 begin
158 -- hr_utility.trace_on(null,'trace');
159 hr_utility.set_location('Entering '||l_proc,1);
160 hr_utility.set_location('p_effective_date '||p_effective_date,10);
161 hr_utility.set_location('p_per_in_ler_id '||p_per_in_ler_id,10);
162 g_debug := hr_utility.debug_enabled;
163 --
164 l_effective_date := p_lf_evt_ocrd_dt;
165 --
166 open c_pen;
167 loop
168 --
169 fetch c_pen into l_pen_rec;
170 if c_pen%notfound then
171 exit;
172 end if;
173 --
174 --
175 l_act_item_expired := false;
176 --
177 -- check if the person has an elctbl chc for the current pil
178 --
182 --
179 l_effective_date := GREATEST(p_lf_evt_ocrd_dt,l_pen_rec.effective_start_date);
180 --
181 -- end all incomplete action items for the suspended result
183 open c_get_actn_items_sus(l_pen_rec.prtt_enrt_rslt_id,
184 l_effective_date);
185 loop
186 --
187 fetch c_get_actn_items_sus into l_act_sus;
188 if c_get_actn_items_sus%notfound then
189 exit;
190 end if;
191
192 if l_act_sus.rqd_flag = 'Y' and
193 nvl(l_act_sus.due_dt,hr_api.g_eot) < l_effective_date and
194 c_get_actn_items_sus%rowcount = 1 then
195 l_act_item_expired := true;
196 end if;
197
198 if l_act_sus.effective_start_date < l_effective_date then
199 l_datetrack_mode := hr_api.g_delete;
200 else
201 l_datetrack_mode := hr_api.g_zap;
202 end if;
203
204 l_pea_effective_date := greatest(l_effective_date-1,l_act_sus.effective_start_date);
205 hr_utility.set_location('Delete A1'||l_pen_rec.prtt_enrt_rslt_id ,10);
206
207 ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
208 (p_prtt_enrt_actn_id => l_act_sus.prtt_enrt_actn_id
209 ,p_business_group_id => p_business_group_id
210 ,p_effective_date => l_pea_effective_date
211 ,p_datetrack_mode => l_datetrack_mode
212 ,p_object_version_number => l_act_sus.object_version_number
213 ,p_prtt_enrt_rslt_id => l_pen_rec.prtt_enrt_rslt_id
214 ,p_rslt_object_version_number => l_pen_rec.object_version_number
215 ,p_unsuspend_enrt_flag => 'N'
216 ,p_gnrt_cm => false
217 ,p_effective_start_date => l_effective_start_date
218 ,p_effective_end_date => l_effective_end_date);
219 --
220 end loop;
221 --
222 close c_get_actn_items_sus;
223 --
224 if l_act_item_expired THEN
225 --
226 hr_utility.set_location ('Expired action items exist ..',10);
227 open c_min_max_date (l_pen_rec.prtt_enrt_rslt_id);
228 fetch c_min_max_date into l_min_start_date,l_max_end_date;
229 close c_min_max_date;
230 --
231 open c_pen_ovn(l_effective_date);
232 fetch c_pen_ovn into l_pen_rec.object_version_number;
233 close c_pen_ovn;
234
235 if l_min_start_date <= l_effective_date and
236 l_max_end_date > l_effective_date
237 then
238 hr_utility.set_location('delete_enrollment 1'||l_pen_rec.prtt_enrt_rslt_id,10);
239 ben_prtt_enrt_result_api.delete_enrollment
240 (p_prtt_enrt_rslt_id => l_pen_rec.prtt_enrt_rslt_id
241 ,p_per_in_ler_id => p_per_in_ler_id
242 ,p_business_group_id => p_business_group_id
243 ,p_effective_start_date => l_effective_start_date
244 ,p_effective_end_date => l_effective_end_date
245 ,p_object_version_number => l_pen_rec.object_version_number
246 ,p_effective_date => l_effective_date
247 ,p_datetrack_mode => hr_api.g_delete
248 ,p_multi_row_validate => false --BUG 4718599 to be in sync with inelig
249 ,p_source => 'beninelg');
250 else
251 hr_utility.set_location('delete_enrollment 2'
252 ||l_pen_rec.prtt_enrt_rslt_id,10);
253 ben_prtt_enrt_result_api.delete_enrollment
254 (p_prtt_enrt_rslt_id => l_pen_rec.prtt_enrt_rslt_id
255 ,p_per_in_ler_id => p_per_in_ler_id
256 ,p_business_group_id => p_business_group_id
257 ,p_effective_start_date => l_effective_start_date
258 ,p_effective_end_date => l_effective_end_date
259 ,p_object_version_number => l_pen_rec.object_version_number
260 ,p_effective_date => l_effective_date
261 ,p_datetrack_mode => hr_api.g_delete
262 ,p_multi_row_validate => false --BUG 4718599 true
263 ,p_source => 'benmngle');
264 end if;
265 end if;
266 end loop;
267 close c_pen;
268 --
269 -- Close pending optional action items from prior event.
270 --
271 for l_pea_rec in c_get_opt_actn_items_unsus(l_effective_date) loop
272 --
273 --If the Previous cert is started on this day..we have an issue.
274 --
275 IF l_pea_rec.effective_start_date = l_effective_date THEN
276 l_pea_effective_date := l_effective_date;
277 ELSE
278 l_pea_effective_date := l_effective_date - 1;
279 END IF;
280 --
281 -- l_pea_effective_date := l_effective_date - 1;
282 --
283 if l_pea_effective_date = l_pea_rec.effective_start_date then
284 l_datetrack_mode := hr_api.g_zap;
285 else
286 l_datetrack_mode := hr_api.g_delete;
287 end if;
288 --
289 l_object_version_number := l_pea_rec.object_version_number;
290 --
291 if l_pea_effective_date <> l_pea_rec.effective_end_date then
292 --
293 hr_utility.set_location('OPtional delete_prtt_enrt_actn'||
294 l_pea_rec.prtt_enrt_actn_id,10);
295 ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
299 ,p_datetrack_mode => l_datetrack_mode
296 (p_prtt_enrt_actn_id => l_pea_rec.prtt_enrt_actn_id
297 ,p_business_group_id => p_business_group_id
298 ,p_effective_date => l_pea_effective_date
300 ,p_object_version_number => l_object_version_number
301 ,p_prtt_enrt_rslt_id => l_pea_rec.prtt_enrt_rslt_id
302 ,p_rslt_object_version_number => l_pea_rec.pen_ovn
303 ,p_unsuspend_enrt_flag => 'N'
304 ,p_effective_start_date => l_effective_start_date
305 ,p_effective_end_date => l_effective_end_date);
306 --
307 hr_utility.set_location('prtt_enrt_actn_id:'||
308 l_pea_rec.prtt_enrt_actn_id, 10);
309 end if;
310 --
311 end loop;
312
313 hr_utility.set_location('Leaving '||l_proc,1);
314
315 end main;
316
317 procedure reinstate_dpnt(p_pgm_id in number,
318 p_pl_id in number,
319 p_oipl_id in number,
320 p_business_group_id in number,
321 p_person_id in number,
322 p_per_in_ler_id in number,
323 p_elig_per_elctbl_chc_id in number,
324 p_dpnt_cvg_strt_dt_cd in varchar2,
325 p_dpnt_cvg_strt_dt_rl in number,
326 p_enrt_cvg_strt_dt in date,
327 p_effective_date in date ,
328 p_prev_prtt_enrt_rslt_id in number default null ) is
329
330 l_lf_evt_ocrd_dt date;
331 l_cvg_strt_dt date;
332 l_old_cvg_strt_dt date;
333 l_pl_typ_id number ;
334 l_opt_id number ;
335 --
336 cursor c_pl_typ is
337 select pl_typ_id
338 from ben_pl_f pl
339 where pl.pl_id = p_pl_id
340 and p_effective_date between
341 pl.effective_start_date and
342 pl.effective_end_date ;
343 --
344 cursor c_opt is
345 select opt_id
346 from ben_oipl_f oipl
347 where oipl.oipl_id = p_oipl_id
348 and p_effective_date between
349 oipl.effective_start_date and
350 oipl.effective_end_date ;
351 --
352 cursor c_prev_per_in_ler is
353 select max(pil.lf_evt_ocrd_dt)
354 from ben_per_in_ler pil
355 where pil.business_group_id = p_business_group_id
356 and pil.person_id = p_person_id
357 and pil.per_in_ler_id <> p_per_in_ler_id
358 and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD');
359 --
360 cursor c_previous_ptip_oipl_result is
361 select pen.prtt_enrt_rslt_id,pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
362 from ben_prtt_enrt_rslt_f pen,
363 ben_oipl_f oipl
364 where oipl.oipl_id = pen.oipl_id
365 and pen.pl_typ_id = l_pl_typ_id
366 and oipl.opt_id = l_opt_id
367 and pen.person_id = p_person_id
368 and pen.per_in_ler_id = p_per_in_ler_id
369 and pen.effective_end_date = hr_api.g_eot
370 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
371 and pen.effective_start_date between oipl.effective_start_date
372 and oipl.effective_end_date
373 AND pen.prtt_enrt_rslt_stat_cd IS NULL
374 ;
375 --
376 cursor c_previous_pgm_ptip_result is
377 select pen.prtt_enrt_rslt_id,pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
378 from ben_prtt_enrt_rslt_f pen
379 where pen.pl_typ_id = l_pl_typ_id
380 and pen.person_id = p_person_id
381 and pen.per_in_ler_id = p_per_in_ler_id
382 and pen.effective_end_date = hr_api.g_eot
383 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
384 AND pen.prtt_enrt_rslt_stat_cd IS NULL
385 ;
386
387 cursor c_previous_pl_oipl_result is
388 select pen.prtt_enrt_rslt_id,pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
389 from ben_prtt_enrt_rslt_f pen,
390 ben_oipl_f oipl
391 where oipl.oipl_id = pen.oipl_id
392 and pen.pl_typ_id = l_pl_typ_id
393 and oipl.opt_id = l_opt_id
394 and pen.person_id = p_person_id
395 and pen.per_in_ler_id = p_per_in_ler_id
396 and pen.effective_end_date = hr_api.g_eot
397 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
398 and pen.effective_start_date between oipl.effective_start_date
399 and oipl.effective_end_date
400 AND pen.prtt_enrt_rslt_stat_cd IS NULL
401 ;
402 --
403 cursor c_previous_result_id_result is
404 select pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
405 from ben_prtt_enrt_rslt_f pen
406 where
407 pen.prtt_enrt_rslt_id = p_prev_prtt_enrt_rslt_id
408 and pen.person_id = p_person_id
409 -- this condition removed to CFD from any result
410 --and pen.per_in_ler_id = p_per_in_ler_id
411 and pen.effective_end_date = hr_api.g_eot
412 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
413 AND pen.prtt_enrt_rslt_stat_cd is null
414 ;
415
416
417 cursor c_previous_pl_result is
418 select pen.prtt_enrt_rslt_id,pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
419 from ben_prtt_enrt_rslt_f pen
420 where pen.pl_typ_id = l_pl_typ_id
421 and pen.person_id = p_person_id
422 and pen.per_in_ler_id = p_per_in_ler_id
423 and pen.effective_end_date = hr_api.g_eot
424 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
425 AND pen.prtt_enrt_rslt_stat_cd IS NULL
426 ;
427
428 cursor c_prev_pen_dpnts(v_enrt_rslt_id number,v_per_in_ler_id number) is
429 select
433 pdp_old.CVG_PNDG_FLAG,
430 pdp_old.EFFECTIVE_END_DATE,
431 pdp_old.CVG_STRT_DT,
432 pdp_old.CVG_THRU_DT,
434 pdp_old.OVRDN_FLAG,
435 pdp_old.OVRDN_THRU_DT,
436 pdp_old.PRTT_ENRT_RSLT_ID,
437 pdp_old.DPNT_PERSON_ID,
438 pdp_old.PER_IN_LER_ID,
439 pdp_old.BUSINESS_GROUP_ID,
440 pdp_old.PDP_ATTRIBUTE_CATEGORY,
441 pdp_old.PDP_ATTRIBUTE1,
442 pdp_old.PDP_ATTRIBUTE2,
443 pdp_old.PDP_ATTRIBUTE3,
444 pdp_old.PDP_ATTRIBUTE4,
445 pdp_old.PDP_ATTRIBUTE5,
446 pdp_old.PDP_ATTRIBUTE6,
447 pdp_old.PDP_ATTRIBUTE7,
448 pdp_old.PDP_ATTRIBUTE8,
449 pdp_old.PDP_ATTRIBUTE9,
450 pdp_old.PDP_ATTRIBUTE10,
451 pdp_old.PDP_ATTRIBUTE11,
452 pdp_old.PDP_ATTRIBUTE12,
453 pdp_old.PDP_ATTRIBUTE13,
454 pdp_old.PDP_ATTRIBUTE14,
455 pdp_old.PDP_ATTRIBUTE15,
456 pdp_old.PDP_ATTRIBUTE16,
457 pdp_old.PDP_ATTRIBUTE17,
458 pdp_old.PDP_ATTRIBUTE18,
459 pdp_old.PDP_ATTRIBUTE19,
460 pdp_old.PDP_ATTRIBUTE20,
461 pdp_old.PDP_ATTRIBUTE21,
462 pdp_old.PDP_ATTRIBUTE22,
463 pdp_old.PDP_ATTRIBUTE23,
464 pdp_old.PDP_ATTRIBUTE24,
465 pdp_old.PDP_ATTRIBUTE25,
466 pdp_old.PDP_ATTRIBUTE26,
467 pdp_old.PDP_ATTRIBUTE27,
468 pdp_old.PDP_ATTRIBUTE28,
469 pdp_old.PDP_ATTRIBUTE29,
470 pdp_old.PDP_ATTRIBUTE30,
471 pdp_old.LAST_UPDATE_DATE,
472 pdp_old.LAST_UPDATED_BY,
473 pdp_old.LAST_UPDATE_LOGIN,
474 pdp_old.CREATED_BY,
475 pdp_old.CREATION_DATE,
476 pdp_old.REQUEST_ID,
477 pdp_old.PROGRAM_APPLICATION_ID,
478 pdp_old.PROGRAM_ID,
479 pdp_old.PROGRAM_UPDATE_DATE,
480 pdp_old.OBJECT_VERSION_NUMBER,
481 pdp_old.elig_cvrd_dpnt_id,
482 pdp_old.EFFECTIVE_START_DATE
483 from ben_elig_cvrd_dpnt_f pdp_old
484 where
485 pdp_old.per_in_ler_id = v_per_in_ler_id
486 and pdp_old.prtt_enrt_rslt_id = v_enrt_rslt_id
487 and pdp_old.business_group_id = p_business_group_id;
488
489 cursor c_epe_dpnt(l_elig_per_elctbl_chc_id number,l_dpnt_person_id number) is
490 select edg.*
491 from ben_elig_dpnt edg
492 where edg.elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id
493 and edg.business_group_id = p_business_group_id
494 and edg.dpnt_person_id = l_dpnt_person_id;
495
496
497 --# bug 2623034 cursot to find the max dpnd allowed
498 cursor c_total_rqmt is
499 select r.mx_dpnts_alwd_num,
500 r.no_mx_num_dfnd_flag,
501 r.dsgn_rqmt_id,
502 r.grp_rlshp_cd
503 from ben_dsgn_rqmt_f r
504 where ((r.pl_id = p_pl_id)
505 or
506 (r.oipl_id = p_oipl_id)
507 or
508 (r.opt_id = (select opt_id
509 from ben_oipl_f
510 where oipl_id = p_oipl_id
511 and p_effective_date between effective_start_date
512 and effective_end_date
513 and business_group_id = p_business_group_id)))
514 and r.dsgn_typ_cd = 'DPNT'
515 -- this should be reoved but couldnt locate relation between grp and type
516 -- and r.grp_rlshp_cd is null
517 --
518 and r.business_group_id = p_business_group_id
519 and p_effective_date between r.effective_start_date
520 and r.effective_end_date;
521 --
522 cursor c_tot_elig_dpnt
523 ( v_per_in_ler_id number,
524 v_prtt_enrt_rslt_id number,
525 v_dsgn_rqmt_id number ,
526 v_grp_rlshp_cd varchar2 ) is
527 select count(pdp.dpnt_person_id)
528 from ben_elig_cvrd_dpnt_f pdp,
529 ben_elig_dpnt egd ,
530 per_contact_relationships pcr
531 where pdp.business_group_id = p_business_group_id
532 and pdp.per_in_ler_id = v_per_in_ler_id
533 and pdp.prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
534 and pdp.cvg_strt_dt is not null
535 and p_effective_date between pdp.effective_start_date
536 and pdp.effective_end_date
537 and egd.business_group_id = pdp.business_group_id
538 and pdp.dpnt_person_id = egd.dpnt_person_id
539 and egd.per_in_ler_id = v_per_in_ler_id
540 and egd.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
541 and pcr.person_id = p_person_id
542 and pcr.contact_person_id = egd.dpnt_person_id
543 and p_effective_date between nvl(pcr.date_start,p_effective_date)
544 and nvl(pcr.date_end,p_effective_date)
545 and (pcr.contact_type in
546 ( select drt.rlshp_typ_cd
547 from ben_dsgn_rqmt_f bdr ,
548 ben_dsgn_rqmt_rlshp_typ drt
549 where bdr.dsgn_rqmt_id = v_dsgn_rqmt_id
550 and drt.dsgn_rqmt_id = bdr.dsgn_rqmt_id
551 and ( bdr.grp_rlshp_cd = v_grp_rlshp_cd or
552 (bdr.grp_rlshp_cd is null and v_grp_rlshp_cd is null )
553 )
554 and p_effective_date between bdr.effective_start_date
555 and bdr.effective_end_date
556 )
557 --- if there is no relation typ defind take all
558 or
562 )
559 not exists
560 (select 'x' from ben_dsgn_rqmt_rlshp_typ drt
561 where drt.dsgn_rqmt_id = v_dsgn_rqmt_id
563 ) ;
564
565
566 l_tot_elig_dpnt number(15);
567 l_tot_rqmt_allow varchar2(30) ;
568 l_ttl_max_num number(15);
569 l_ttl_no_max_flag varchar2(30);
570 l_grp_rlshp_cd ben_dsgn_rqmt_f.grp_rlshp_cd%type ;
571 l_epe_dpnt_rec c_epe_dpnt%rowtype;
572 l_proc varchar2(80) := g_package||'reinstate_dpnt';
573 l_rslt_id number;
574 l_pil_id number;
575 l_dsgn_rqmt_id number;
576 l_object_version_number number;
577 l_eff_start_date date;
578 l_eff_end_date date;
579 l_elig_cvrd_dpnt_id ben_elig_cvrd_dpnt_f.elig_cvrd_dpnt_id%TYPE;
580
581 begin
582 --
583 hr_utility.set_location('Entering: '||l_proc,10);
584 hr_utility.set_location('program : '||p_pgm_id,745);
585 hr_utility.set_location('plan : ' ||p_pl_id,745);
586 hr_utility.set_location('option : ' ||p_oipl_id,745);
587 hr_utility.set_location('p_elig_per_elctbl_chc_id : ' ||p_elig_per_elctbl_chc_id,745);
588 hr_utility.set_location('eff date : ' ||p_effective_date,745 );
589
590 ---- Pl ty and opt id is determined for pl type and opt validation # 2508745
591 open c_pl_typ ;
592 fetch c_pl_typ into l_pl_typ_id ;
593 close c_pl_typ ;
594
595 open c_opt ;
596 fetch c_opt into l_opt_id ;
597 close c_opt ;
598
599 hr_utility.set_location('option : ' ||l_opt_id,745);
600 hr_utility.set_location('pl type : ' ||l_pl_typ_id,745);
601
602 if p_prev_prtt_enrt_rslt_id is not null then
603
604 l_rslt_id := p_prev_prtt_enrt_rslt_id ;
605 hr_utility.set_location('p_prev_prtt_enrt_rslt_id after result',745 );
606 open c_previous_result_id_result ;
607 fetch c_previous_result_id_result into l_old_cvg_strt_dt ,l_pil_id;
608 if c_previous_result_id_result%notfound then
609 hr_utility.set_location('0 null : '||l_proc,745);
610 end if ;
611 close c_previous_result_id_result;
612
613 else
614
615 if p_pgm_id is not null then
616 if p_oipl_id is not null then
617 open c_previous_ptip_oipl_result;
618 fetch c_previous_ptip_oipl_result into l_rslt_id,l_old_cvg_strt_dt ,l_pil_id;
619 if c_previous_ptip_oipl_result%notfound then null;
620 hr_utility.set_location('1 null : '||l_proc,745);
621 end if;
622 close c_previous_ptip_oipl_result;
623 hr_utility.set_location('1 : '||l_proc,745);
624
625 else
626 open c_previous_pgm_ptip_result;
627 fetch c_previous_pgm_ptip_result into l_rslt_id,l_old_cvg_strt_dt ,l_pil_id;
628 if c_previous_pgm_ptip_result%notfound then null;
629 end if;
630 close c_previous_pgm_ptip_result;
631 hr_utility.set_location('2 : '||l_proc,745);
632 end if;
633 else
634
635 if p_oipl_id is not null then
636 open c_previous_pl_oipl_result;
637 fetch c_previous_pl_oipl_result into l_rslt_id,l_old_cvg_strt_dt ,l_pil_id;
638 if c_previous_pl_oipl_result%notfound then null;
639 hr_utility.set_location('3 null : '||l_proc,745);
640 end if;
641 close c_previous_pl_oipl_result;
642 hr_utility.set_location('3 : '||l_proc,745);
643 elsif p_oipl_id is null then
644 open c_previous_pl_result;
645 fetch c_previous_pl_result into l_rslt_id,l_old_cvg_strt_dt ,l_pil_id;
646 if c_previous_pl_result%notfound then null;
647 hr_utility.set_location('4 nul : '||l_proc,745);
648 end if;
649 close c_previous_pl_result;
650 hr_utility.set_location('4 : '||l_proc,745);
651 end if;
652 end if;
653 end if ; --- p_prev_prtt_enrt_rslt_id
654
655 if l_rslt_id is not null and l_pil_id is not null then
656
657 hr_utility.set_location('Reinstating dependent person id',99);
658 hr_utility.set_location('pil id '|| l_pil_id ,99);
659 hr_utility.set_location('rslt id '|| l_rslt_id ,99);
660 hr_utility.set_location('cvg start '|| l_old_cvg_strt_dt ,99);
661
662 --- # 2623034 Find out the maximum required dpnt
663 --- Validate every groep of relation match with
664
665 l_tot_rqmt_allow := 'Y' ;
666
667 open c_total_rqmt;
668 Loop
669 fetch c_total_rqmt into l_ttl_max_num, l_ttl_no_max_flag,l_dsgn_rqmt_id,l_grp_rlshp_cd;
670 if c_total_rqmt%notfound then
671 exit ;
672 end if;
673 hr_utility.set_location(' grp_rlshp_cd ' || l_grp_rlshp_cd, 99 );
674 hr_utility.set_location(' l_dsgn_rqmt_id ' || l_dsgn_rqmt_id, 99 );
675 hr_utility.set_location(' ttl_no_max_flag ' || l_ttl_no_max_flag, 99 );
676 hr_utility.set_location(' ttl_max_num ' || l_ttl_max_num, 99 );
677 l_tot_elig_dpnt := 0 ;
678 open c_tot_elig_dpnt (l_pil_id,l_rslt_id,l_dsgn_rqmt_id,l_grp_rlshp_cd) ;
679 fetch c_tot_elig_dpnt into l_tot_elig_dpnt ;
680 close c_tot_elig_dpnt ;
681 hr_utility.set_location(' total eligible ' || l_tot_elig_dpnt, 99 );
685 -- exit ;
682 if l_ttl_no_max_flag = 'N' and
683 nvl(l_tot_elig_dpnt,0) > l_ttl_max_num then
684 l_tot_rqmt_allow := 'N' ;
686 end if ;
687 End loop ;
688 close c_total_rqmt ;
689 hr_utility.set_location(' l_tot_rqmt_allow ' || l_tot_rqmt_allow, 99 );
690
691
692 if l_tot_rqmt_allow = 'Y' then
693 -- Create the dependents row.
694 for l_prev_pen_dpnts in c_prev_pen_dpnts(l_rslt_id,l_pil_id) loop
695 --
696 hr_utility.set_location('Reinstating dependent in loop ',99);
697 hr_utility.set_location('p_dpnt_cvg_strt_dt_cd '|| p_dpnt_cvg_strt_dt_cd,99);
698 if p_dpnt_cvg_strt_dt_cd is null then
699 --
700 fnd_message.set_name('BEN','BEN_92558_DPNT_CVG_CD');
701 fnd_message.raise_error;
702 --
703 end if;
704 --
705 -- Calculate Dependents Coverage Start Date
706 --
707 ben_determine_date.main
708 (p_date_cd => p_dpnt_cvg_strt_dt_cd
709 ,p_per_in_ler_id => null
710 ,p_person_id => null
711 ,p_pgm_id => null
712 ,p_pl_id => null
713 ,p_oipl_id => null
714 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
715 ,p_business_group_id => p_business_group_id
716 ,p_formula_id => p_dpnt_cvg_strt_dt_rl
717 ,p_effective_date => p_effective_date
718 ,p_returned_date => l_cvg_strt_dt);
719
720 if l_cvg_strt_dt < p_enrt_cvg_strt_dt then
721 l_cvg_strt_dt := p_enrt_cvg_strt_dt;
722 end if;
723 hr_utility.set_location('Cvg start dt ='||to_char(l_cvg_strt_dt), 25);
724 --hook the depenedent to the new enrollment result.
725 open c_epe_dpnt(p_elig_per_elctbl_chc_id,l_prev_pen_dpnts.dpnt_person_id);
726 fetch c_epe_dpnt into l_epe_dpnt_rec;
727 if c_epe_dpnt%notfound then
728 null;
729 else
730 ben_ELIG_DPNT_api.process_dependent(
731 p_elig_dpnt_id => l_epe_dpnt_rec.elig_dpnt_id,
732 p_business_group_id => p_business_group_id,
733 p_effective_date => p_effective_date,
734 p_cvg_strt_dt => l_cvg_strt_dt,
735 p_cvg_thru_dt => hr_api.g_eot,
736 p_datetrack_mode => hr_api.g_insert,
737 p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id,
738 p_effective_start_date => l_eff_start_date,
739 p_effective_end_date => l_eff_end_date,
740 p_object_version_number => l_object_version_number,
741 p_multi_row_actn => TRUE );
742 end if;
743 close c_epe_dpnt;
744 end loop;
745 End if ;
746 end if;
747 --
748 hr_utility.set_location('Leaving: '||l_proc,10);
749 --
750 end reinstate_dpnt;
751 --
752 ----------------------------------------------------------------------------------------
753 -- reinstate_prvdd_ctfn_items --
754 ----------------------------------------------------------------------------------------
755 -- 6057157: During carry-forward of suspended elections, if any certifications were provided
756 --in future for the past pil, then those certifications are provided again in this new pil.
757 --
758 --1. We store certifications provided on a future date for the prev.pil
759 -- in backup table with backup table type code as BEN_PRTT_ENRT_CTFN_PRVDD_F_UPD
760 --2. In this procedure we pick up all such records and update
761 -- the ben_prtt_enrt_ctfn_prvdd_f table with the Certification Provided Date.
762 --
763 procedure reinstate_prvdd_ctfn_items (p_prtt_enrt_rslt_id in number
764 ,p_per_in_ler_id in number
765 ,p_business_group_id in number
766 ,p_effective_date date) is
767
768 cursor c_prvdd_ctfns_past_pil is
769 select lcr.bkup_tbl_id PREV_PRTT_ENRT_CTFN_PRVDD_ID,
770 lcr.effective_start_date,
771 lcr.effective_end_date,
772 lcr.prtt_is_cvrd_flag ENRT_CTFN_RQD_FLAG,
773 lcr.comp_lvl_cd ENRT_CTFN_TYP_CD,
774 lcr.enrt_cvg_thru_dt ENRT_CTFN_RECD_DT,
775 lcr.prtt_enrt_rslt_id PREV_PRTT_ENRT_RSLT_ID,
776 lcr.pgm_id PREV_PRTT_ENRT_ACTN_ID,
777 lcr.enrt_ovrid_thru_dt ENRT_CTFN_DND_DT,
778 lcr.bnft_typ_cd ENRT_R_BNFT_CTFN_CD,
779 pcs.prtt_enrt_ctfn_prvdd_id,
780 pcs.object_version_number,
781 pcs.prtt_enrt_actn_id
782 from ben_le_clsn_n_rstr lcr
783 ,ben_prtt_enrt_ctfn_prvdd_f pcs
784 where lcr.per_in_ler_ended_id = p_per_in_ler_id
785 and lcr.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_CTFN_PRVDD_F_UPD'
786 and pcs.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
790 and exists (select 'x' -- To confirm if both PENs are for the same comp.object
787 and pcs.effective_end_date = hr_api.g_eot
788 and pcs.enrt_ctfn_recd_dt is null
789 and pcs.enrt_ctfn_typ_cd = lcr.comp_lvl_cd
791 from ben_prtt_enrt_rslt_f pen_lcr
792 ,ben_prtt_enrt_rslt_f pen_pcs
793 where pen_lcr.prtt_enrt_rslt_id = lcr.prtt_enrt_rslt_id
794 and pen_pcs.prtt_enrt_rslt_id = pcs.prtt_enrt_rslt_id
795 and nvl(pen_lcr.pgm_id,-1) = nvl(pen_pcs.pgm_id,-1)
796 and pen_lcr.pl_id = pen_pcs.pl_id
797 and nvl(pen_lcr.oipl_id, -1) = nvl(pen_pcs.oipl_id, -1)
798 )
799 ;
800
801 l_esd date;
802 l_eed date;
803 l_datetrack_mode varchar2(30);
804 l_effective_date date;
805 --
806 begin
807 --
808 l_effective_date := p_effective_date;
809 if (g_debug) then
810 hr_utility.set_location('Entering reinstate_prvdd_ctfn_items ', 10);
811 end if;
812 --
813 for l_prvdd_ctfns in c_prvdd_ctfns_past_pil
814 loop
815 --
816 if (g_debug) then
817 hr_utility.set_location('l_effective_date ' || l_effective_date, 10);
818 hr_utility.set_location('l_prvdd_ctfns.effective_start_date ' || l_prvdd_ctfns.effective_start_date, 10);
819 hr_utility.set_location('l_prvdd_ctfns.effective_end_date ' || l_prvdd_ctfns.effective_end_date, 10);
820 hr_utility.set_location('l_prvdd_ctfns.prtt_enrt_actn_id ' || l_prvdd_ctfns.prtt_enrt_actn_id, 10);
821 hr_utility.set_location('l_prvdd_ctfns.prev_prtt_enrt_actn_id ' || l_prvdd_ctfns.prev_prtt_enrt_actn_id, 10);
822 hr_utility.set_location('l_prvdd_ctfns.prtt_enrt_ctfn_prvdd_id' || l_prvdd_ctfns.prtt_enrt_ctfn_prvdd_id, 10);
823 hr_utility.set_location('l_prvdd_ctfns.enrt_ctfn_recd_dt' || l_prvdd_ctfns.enrt_ctfn_recd_dt, 10);
824 hr_utility.set_location('l_prvdd_ctfns.ENRT_CTFN_TYP_CD' || l_prvdd_ctfns.ENRT_CTFN_TYP_CD, 10);
825 end if;
826 --
827 if (l_effective_date > l_prvdd_ctfns.effective_start_date) then
828 l_datetrack_mode := hr_api.g_update;
829 else
830 l_effective_date := GREATEST(l_effective_date, l_prvdd_ctfns.effective_start_date);
831 l_datetrack_mode := hr_api.g_correction;
832 end if;
833 --
834 ben_prtt_enrt_ctfn_prvdd_api.update_prtt_enrt_ctfn_prvdd
835 (p_prtt_enrt_ctfn_prvdd_id => l_prvdd_ctfns.prtt_enrt_ctfn_prvdd_id
836 ,p_effective_start_date => l_esd
837 ,p_effective_end_date => l_eed
838 ,p_prtt_enrt_actn_id => l_prvdd_ctfns.prtt_enrt_actn_id
839 ,p_enrt_ctfn_recd_dt => l_prvdd_ctfns.enrt_ctfn_recd_dt
840 ,p_object_version_number => l_prvdd_ctfns.object_version_number
841 ,p_effective_date => l_effective_date
842 ,p_business_group_id => p_business_group_id
843 ,p_datetrack_mode => l_datetrack_mode);
844 --
845 end loop;
846 --
847 end reinstate_prvdd_ctfn_items;
848
849
850 procedure process_person(p_person_id in number,
851 p_business_group_id in number,
852 p_per_in_ler_id in number,
853 p_ler_id in number,
854 p_effective_date in date) is
855 --
856 cursor c_pil is
857 select pil.lf_evt_ocrd_dt
858 from ben_per_in_ler pil
859 where pil.per_in_ler_id = p_per_in_ler_id ;
860 --
861 l_lf_evt_ocrd_dt date;
862 --
863 cursor c_prtt_result(v_prtt_enrt_rslt_id number,
864 p_effective_date date) is
865 select pen.prtt_enrt_rslt_id,
866 pen.effective_start_date,
867 pen.effective_end_date,
868 pen.object_version_number,
869 pen.bnft_amt,
870 pen.uom,
871 pen.enrt_mthd_cd,
872 pen.business_group_id,
873 pen.enrt_cvg_strt_dt,
874 pen.enrt_cvg_thru_dt,
875 pen.pen_attribute_category ,
876 pen.pen_attribute1 ,
877 pen.pen_attribute2 ,
878 pen.pen_attribute3 ,
879 pen.pen_attribute4 ,
880 pen.pen_attribute5 ,
881 pen.pen_attribute6 ,
882 pen.pen_attribute7 ,
883 pen.pen_attribute8 ,
884 pen.pen_attribute9 ,
885 pen.pen_attribute10 ,
886 pen.pen_attribute11 ,
887 pen.pen_attribute12 ,
888 pen.pen_attribute13 ,
889 pen.pen_attribute14 ,
890 pen.pen_attribute15 ,
891 pen.pen_attribute16 ,
892 pen.pen_attribute17 ,
893 pen.pen_attribute18 ,
894 pen.pen_attribute19 ,
895 pen.pen_attribute20 ,
896 pen.pen_attribute21 ,
897 pen.pen_attribute22,
898 pen.pen_attribute23,
899 pen.pen_attribute24,
900 pen.pen_attribute25,
901 pen.pen_attribute26,
902 pen.pen_attribute27,
903 pen.pen_attribute28,
904 pen.pen_attribute29,
905 pen.pen_attribute30,
906 pen.bnft_ordr_num,
907 pen.rplcs_sspndd_rslt_id
908 from ben_prtt_enrt_rslt_f pen
909 where pen.prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
910 /* 5741760: PEN in future, can also be carried-fwd.
914 AND pen.prtt_enrt_rslt_stat_cd is null
911 and p_effective_date between
912 pen.effective_start_date and pen.effective_end_date
913 */ and p_effective_date <= pen.effective_end_date
915 and pen.business_group_id = p_business_group_id
916 order by pen.effective_start_date desc;
917 --
918 l_prtt_result c_prtt_result%rowtype;
919 -- Bug 5102337 several changes in this cursor
920 -- a. for handling ben_enrt_bnft and b. future completed certifications
921 cursor c_choice_info is
922 select pil.object_version_number,
923 epe.elig_per_elctbl_chc_id,
924 pel.enrt_typ_cycl_cd,
925 epe.enrt_cvg_strt_dt_cd,
926 pel.enrt_perd_end_dt,
927 pel.enrt_perd_strt_dt,
928 epe.enrt_cvg_strt_dt_rl,
929 epe.enrt_cvg_strt_dt,
930 to_date('31-12-4712','DD-MM-YYYY') enrt_cvg_end_dt,
931 nvl(enb.crntly_enrld_flag,epe.crntly_enrd_flag) crntly_enrd_flag,
932 epe.dflt_flag,
933 epe.elctbl_flag,
934 epe.mndtry_flag,
935 pel.dflt_enrt_dt,
936 epe.dpnt_cvg_strt_dt_cd,
937 epe.dpnt_cvg_strt_dt_rl,
938 epe.alws_dpnt_dsgn_flag,
939 epe.dpnt_dsgn_cd,
940 epe.ler_chg_dpnt_cvg_cd,
941 epe.erlst_deenrt_dt,
942 epe.procg_end_dt,
943 epe.comp_lvl_cd,
944 epe.pl_id,
945 epe.oipl_id,
946 epe.pgm_id,
947 epe.plip_id,
948 epe.ptip_id,
949 epe.pl_typ_id,
950 epe.cmbn_ptip_id,
951 epe.cmbn_ptip_opt_id,
952 epe.spcl_rt_pl_id,
953 epe.spcl_rt_oipl_id,
954 epe.must_enrl_anthr_pl_id,
955 nvl(enb.prtt_enrt_rslt_id,epe.prtt_enrt_rslt_id) prtt_enrt_rslt_id ,
956 epe.bnft_prvdr_pool_id,
957 epe.per_in_ler_id,
958 epe.yr_perd_id,
959 epe.business_group_id,
960 'N' stage,
961 'N' suspended,
962 epe.cryfwd_elig_dpnt_cd
963 from ben_elig_per_elctbl_chc epe,
964 ben_enrt_bnft enb,
965 ben_per_in_ler pil,
966 ben_pil_elctbl_chc_popl pel
967 where NVL(enb.crntly_enrld_flag(+),epe.crntly_enrd_flag) = 'Y'
968 and pil.person_id = p_person_id
969 and pil.per_in_ler_id = p_per_in_ler_id
970 and epe.per_in_ler_id = pil.per_in_ler_id
971 and pel.per_in_ler_id = epe.per_in_ler_id
972 and pil.per_in_ler_stat_cd IN ('STRTD' ,'PROCD') -- 6156874
973 --Bug 5617091 for recalc it is set to PROCD before this call
974 and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
975 and enb.elig_per_elctbl_chc_id (+) = epe.elig_per_elctbl_chc_id
976 and epe.prtt_enrt_rslt_id is NOT NULL
977 and not exists (select null
978 from ben_prtt_enrt_rslt_f pen
979 where pen.pl_id = epe.pl_id
980 and pen.prtt_enrt_rslt_stat_cd IS NULL
981 and pen.per_in_ler_id = epe.per_in_ler_id
982 /* Added the below condition and commented the code for Bug 7426609 */
983 and pen.per_in_ler_id=p_per_in_ler_id
984 /*and pen.prtt_enrt_rslt_id = NVL(enb.prtt_enrt_rslt_id,
985 epe.prtt_enrt_rslt_id)*/
986 and pen.enrt_cvg_thru_dt = hr_api.g_eot
987 and pen.effective_end_date = hr_api.g_eot)
988 and exists (select null
989 from ben_prtt_enrt_rslt_f pen
990 where pen.pl_id = epe.pl_id
991 and pen.prtt_enrt_rslt_stat_cd IS NULL
992 and pen.per_in_ler_id <> epe.per_in_ler_id
993 and pen.prtt_enrt_rslt_id = NVL(enb.prtt_enrt_rslt_id,
994 epe.prtt_enrt_rslt_id)
995 and pen.sspndd_flag = 'Y'
996 and pen.enrt_cvg_thru_dt = hr_api.g_eot
997 -- and pen.effective_end_date = hr_api.g_eot -- 6156874
998 and pen.effective_end_date >= l_lf_evt_ocrd_dt )
999 order by epe.pgm_id, epe.pl_id;
1000 --
1001 l_choice_info c_choice_info%rowtype;
1002 --
1003 cursor c_elctbl_epe is
1004 select null
1005 from ben_elig_per_elctbl_chc epe,
1006 ben_per_in_ler pil,
1007 ben_pil_elctbl_chc_popl pel
1008 where epe.elctbl_flag = 'Y'
1009 --and pil.business_group_id = p_business_group_id
1010 --and epe.business_group_id = pil.business_group_id
1011 --and pel.business_group_id = epe.business_group_id
1012 and pil.person_id = p_person_id
1013 and epe.per_in_ler_id = pil.per_in_ler_id
1014 and pel.per_in_ler_id = epe.per_in_ler_id
1015 and pil.per_in_ler_stat_cd = 'STRTD'
1016 and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
1017 and (epe.pgm_id is not null
1018 and not exists(select null
1019 from ben_prtt_enrt_rslt_f pen
1020 where pen.pgm_id = epe.pgm_id
1021 and pen.per_in_ler_id = epe.per_in_ler_id
1025 and not exists(select null
1022 and pen.enrt_cvg_thru_dt = hr_api.g_eot
1023 and pen.effective_end_date = hr_api.g_eot)
1024 or epe.pl_id is not null
1026 from ben_prtt_enrt_rslt_f pen
1027 where pen.pl_id = epe.pl_id
1028 and pen.per_in_ler_id = epe.per_in_ler_id
1029 and pen.enrt_cvg_thru_dt = hr_api.g_eot
1030 and pen.effective_end_date = hr_api.g_eot));
1031 --
1032 cursor c_pgm_enrt_dt(v_elig_per_elctbl_chc_id number,v_pgm_id number) is
1033 select pel.enrt_perd_strt_dt
1034 from ben_pil_elctbl_chc_popl pel,ben_elig_per_elctbl_chc epe
1035 where pel.pgm_id = v_pgm_id
1036 and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
1037 and epe.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id;
1038
1039 l_pgm_enrt_dt c_pgm_enrt_dt%rowtype;
1040
1041 cursor c_pl_enrt_dt(v_elig_per_elctbl_chc_id number,v_pl_id number) is
1042 select pel.enrt_perd_strt_dt
1043 from ben_pil_elctbl_chc_popl pel,ben_elig_per_elctbl_chc epe
1044 where pel.pl_id = v_pl_id
1045 and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
1046 and epe.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id;
1047
1048 l_pl_enrt_dt c_pl_enrt_dt%rowtype;
1049
1050 cursor c_bnft(v_elig_per_elctbl_chc_id number, v_bnft_ordr_num number ) is
1051 select enb.enrt_bnft_id,
1052 decode(enb.entr_val_at_enrt_flag,'Y',enb.dflt_val,enb.val) val,
1053 enb.dflt_flag,
1054 enb.prtt_enrt_rslt_id,
1055 enb.cvg_mlt_cd,
1056 enb.crntly_enrld_flag
1057 from ben_enrt_bnft enb
1058 where enb.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
1059 and enb.crntly_enrld_flag = 'Y'
1060 and enb.prtt_enrt_rslt_id is not NULL
1061 and enb.ordr_num = v_bnft_ordr_num
1062 ;
1063 --
1064 /* REMOVED UNUSED CODE HERE */
1065 --
1066 l_pgm_id number;
1067 l_pl_id number;
1068 l_pil_id number;
1069 l_oipl_id number;
1070 -- l_lf_evt_ocrd_dt date;
1071 l_bnft c_bnft%rowtype;
1072 l_bnft_reset c_bnft%rowtype; -- BBULUSU CODE
1073 l_dflt_bnft c_bnft%rowtype;
1074 l_dflt_found boolean;
1075 l_per_in_ler_id number := p_per_in_ler_id ;
1076 --
1077 cursor c_rt(v_elig_per_elctbl_chc_id number,
1078 v_enrt_bnft_id number) is
1079 select ecr.enrt_rt_id,
1080 nvl(ecr.val,ecr.dflt_val) default_value,
1081 nvl(ecr.ann_dflt_val,ecr.ann_val) ann_rt_val,
1082 ecr.prtt_rt_val_id
1083 from ben_enrt_rt ecr
1084 where ecr.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
1085 and ecr.business_group_id = p_business_group_id
1086 and ecr.entr_val_at_enrt_flag = 'Y'
1087 and ecr.spcl_rt_enrt_rt_id is null
1088 union
1089 select ecr.enrt_rt_id,
1090 nvl(ecr.val,ecr.dflt_val) default_value,
1091 nvl(ecr.ann_dflt_val,ecr.ann_val) ann_rt_val,
1092 ecr.prtt_rt_val_id
1093 from ben_enrt_rt ecr
1094 where ecr.enrt_bnft_id = v_enrt_bnft_id
1095 and ecr.business_group_id = p_business_group_id
1096 and ecr.entr_val_at_enrt_flag = 'Y'
1097 and ecr.spcl_rt_enrt_rt_id is null
1098 ;
1099 --
1100 l_rt c_rt%rowtype;
1101 --
1102 cursor c_prv(p_prtt_enrt_rslt_id number, p_per_in_ler_id number) is
1103 select prv.*
1104 from ben_prtt_rt_val prv
1105 where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1106 and prv.per_in_ler_id = p_per_in_ler_id
1107 and prv.prtt_rt_val_stat_cd is NULL ;
1108 --
1109 cursor c_ecr(p_prtt_rt_val_id number) is
1110 select ecr.rt_strt_dt,
1111 ecr.rt_strt_dt_cd,
1112 ecr.rt_strt_dt_rl,
1113 nvl(ecr.elig_per_elctbl_chc_id,enb.elig_per_elctbl_chc_id) elig_per_elctbl_chc_id
1114 from ben_enrt_rt ecr,
1115 ben_enrt_bnft enb
1116 where ecr.prtt_rt_val_id = p_prtt_rt_val_id
1117 and ecr.enrt_bnft_id = enb.enrt_bnft_id (+) ;
1118 --
1119 l_ecr c_ecr%rowtype;
1120 --
1121 l_cvg_strt_dt date;
1122
1123 type g_rt_rec is record
1124 (enrt_rt_id ben_enrt_rt.enrt_rt_id%type,
1125 dflt_val ben_enrt_rt.dflt_val%type,
1126 prtt_rt_val_id ben_enrt_rt.prtt_rt_val_id%type,
1127 ann_rt_val ben_enrt_rt.ann_val%type);
1128 --
1129 type g_rt_table is table of g_rt_rec index by binary_integer;
1130 --
1131 l_rt_table g_rt_table;
1132 --
1133 type g_pen_id_table is table of number index by binary_integer;
1134 l_crd_fwd_pen_id g_pen_id_table;
1135 l_pen_count number;
1136 --
1137
1138 -- Local Variables
1139 --
1140 l_proc varchar2(80) := g_package||'process_person';
1141 l_dpnt_actn_warning boolean;
1142 l_bnf_actn_warning boolean;
1143 l_ctfn_actn_warning boolean;
1144 l_new_election boolean := false;
1145 l_datetrack_mode varchar2(30);
1146 l_prev_pgm_id number := -99999;
1147 l_prtt_enrt_interim_id number;
1148 l_rslt_id number;
1149 l_count number;
1150 l_object_version_number number;
1151 l_ovn_intr number;
1152 l_suspend_flag varchar2(30);
1153 l_dummy varchar2(30);
1154 l_person_susp varchar2(30) := 'N';
1155 l_cls_enrt_flag boolean := true;
1156 l_effective_start_date date;
1157 l_effective_end_date date;
1158 l_eff_start_date date;
1159 l_eff_end_date date;
1160 l_effective_dt date;
1161 l_prev_eff_dt date;
1165 l_cryfwd_elig_dpnt_cd varchar2(30) ;
1162 l_elig_cvrd_dpnt_id ben_elig_cvrd_dpnt_f.elig_cvrd_dpnt_id%TYPE;
1163
1164 l_rec ben_env_object.g_global_env_rec_type;
1166 l_prev_rslt_id_at number := 0 ;
1167 l_prev_prtt_enrt_rslt_id number ;
1168 l_prtt_enrt_rslt_id number ;
1169 l_bnft_amt number;
1170
1171 l_rdefault_table_cnt number;
1172 --
1173 begin
1174 --
1175 g_debug := hr_utility.debug_enabled;
1176 --
1177 hr_utility.set_location('Entering: '||l_proc,10);
1178 --
1179 -- ben_env_object.get(p_rec => l_rec);
1180 --
1181 hr_utility.set_location('Effective date: '||p_effective_date,10);
1182 --
1183 ben_sspndd_enrollment.g_cfw_flag := 'Y';
1184 --
1185 l_pen_count := 0;
1186 l_crd_fwd_pen_id.delete;
1187 --
1188 open c_pil ;
1189 fetch c_pil into l_lf_evt_ocrd_dt;
1190 close c_pil ;
1191 --
1192 open c_choice_info;
1193 --
1194 loop
1195 --
1196 l_bnft := l_bnft_reset; -- BBULUSU CODE
1197 --
1198 fetch c_choice_info into l_choice_info;
1199 --
1200 hr_utility.set_location('cvg strt cd: '||l_choice_info.dpnt_cvg_strt_dt_cd,10);
1201 hr_utility.set_location('epe id : '||l_choice_info.elig_per_elctbl_chc_id,10);
1202 --
1203 exit when c_choice_info%notfound;
1204 --
1205 -- Get participant enrollment result information
1206
1207 hr_utility.set_location('cvg strt cd: '||l_choice_info.dpnt_cvg_strt_dt_cd,10);
1208 hr_utility.set_location('epe id : '||l_choice_info.elig_per_elctbl_chc_id,10);
1209 --
1210 if l_choice_info.crntly_enrd_flag = 'Y' then
1211 --
1212 open c_prtt_result(l_choice_info.prtt_enrt_rslt_id,
1213 p_effective_date);
1214 --
1215 fetch c_prtt_result into l_prtt_result;
1216 if c_prtt_result%notfound then
1217 --
1218 close c_prtt_result;
1219 fnd_message.set_name('BEN','BEN_91711_ENRT_RSLT_NOT_FOUND');
1220 fnd_message.set_token('PROC',l_proc);
1221 fnd_message.set_token('ID',l_choice_info.prtt_enrt_rslt_id);
1222 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1223 fnd_message.set_token('LER_ID',to_char(p_ler_id));
1224 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
1225 fnd_message.raise_error;
1226 --
1227 end if;
1228 --
1229 close c_prtt_result;
1230 --
1231 l_new_election := false;
1232 l_datetrack_mode := hr_api.g_update;
1233 l_choice_info.stage := 'N';
1234 --
1235 else
1236 --
1237 --Never goes here
1238 --
1239 l_new_election := true;
1240 l_datetrack_mode := hr_api.g_insert;
1241 l_choice_info.stage := 'Y';
1242 --
1243 end if;
1244 --
1245 -- Get benefit information
1246 --
1247 l_dflt_found:=false;
1248 hr_utility.set_location(l_proc,20);
1249 open c_bnft(l_choice_info.elig_per_elctbl_chc_id, l_prtt_result.bnft_ordr_num );
1250 fetch c_bnft into l_bnft;
1251 close c_bnft;
1252 --
1253 /*
1254 loop
1255 hr_utility.set_location(l_proc,30);
1256 --
1257 fetch c_bnft into l_bnft;
1258 --
1259 exit when c_bnft%notfound;
1260 hr_utility.set_location(l_proc,40);
1261 if l_bnft.dflt_flag='Y' then
1262 hr_utility.set_location(l_proc,50);
1263 l_dflt_bnft:=l_bnft;
1264 l_dflt_found:=true;
1265 end if;
1266 hr_utility.set_location(l_proc,60);
1267 end loop;
1268 hr_utility.set_location(l_proc,70);
1269 close c_bnft;
1270 */
1271 --
1272 hr_utility.set_location(l_proc||l_bnft.val,90);
1273 hr_utility.set_location('ENB ID'||l_bnft.enrt_bnft_id,90);
1274 --
1275 -- Get Rate information
1276 --
1277 for l_count in 1..10 loop
1278 --
1279 -- Initialise array to null
1280 --
1281 l_rt_table(l_count).enrt_rt_id := null;
1282 l_rt_table(l_count).dflt_val := null;
1283 l_rt_table(l_count).prtt_rt_val_id:=null;
1284 --
1285 end loop;
1286 --
1287 l_count:= 0;
1288 --
1289 for l_rec in c_rt(l_choice_info.elig_per_elctbl_chc_id,
1290 l_bnft.enrt_bnft_id) loop
1291 --
1292 l_count := l_count+1;
1293 l_rt_table(l_count).enrt_rt_id := l_rec.enrt_rt_id;
1294 l_rt_table(l_count).dflt_val := l_rec.default_value;
1295 l_rt_table(l_count).prtt_rt_val_id:=l_rec.prtt_rt_val_id;
1296 --
1297 end loop;
1298 --
1299 l_suspend_flag := 'N';
1300 --
1301 -- Call election information batch process
1302 --
1303 if l_choice_info.pgm_id is not null then
1304 open c_pgm_enrt_dt(l_choice_info.elig_per_elctbl_chc_id,
1305 l_choice_info.pgm_id);
1306 fetch c_pgm_enrt_dt into l_pgm_enrt_dt;
1307 -- if l_pgm_enrt_dt.enrt_perd_strt_dt < p_effective_date then
1308 -- 5741760: Create/Update enrollments as of first day of enrt_perd_strt_dt
1309 l_effective_dt := l_pgm_enrt_dt.enrt_perd_strt_dt;
1310 -- else
1314 elsif l_choice_info.pl_id is not null then
1311 --l_effective_dt := p_effective_date;
1312 --end if;
1313 close c_pgm_enrt_dt;
1315 open c_pl_enrt_dt(l_choice_info.elig_per_elctbl_chc_id,
1316 l_choice_info.pl_id);
1317 fetch c_pl_enrt_dt into l_pl_enrt_dt;
1318 --if l_pl_enrt_dt.enrt_perd_strt_dt < p_effective_date then
1319 -- 5741760: Create/Update enrollments as of first day of enrt_perd_strt_dt
1320 l_effective_dt := l_pl_enrt_dt.enrt_perd_strt_dt;
1321 --else
1322 -- l_effective_dt := p_effective_date;
1323 --end if;
1324 close c_pl_enrt_dt;
1325 end if;
1326 --
1327 hr_utility.set_location('cvg_mlt_cd='||l_bnft.cvg_mlt_cd,13);
1328 hr_utility.set_location('bnft_val='||l_bnft.val,13);
1329 hr_utility.set_location('rate_val='||l_rt_table(1).dflt_val,13);
1330 hr_utility.set_location('ann_rt_val='||l_rt_table(1).ann_rt_val,13);
1331 --
1332 if l_bnft.cvg_mlt_cd='SAAEAR' and
1333 l_rt_table(1).ann_rt_val is null then
1334 l_rt_table(1).ann_rt_val:=l_rt_table(1).dflt_val;
1335 end if;
1336 --
1337 l_prtt_enrt_rslt_id := nvl(l_bnft.prtt_enrt_rslt_id,l_choice_info.prtt_enrt_rslt_id);
1338 --bug#4299428 - benefit amount on enrt bnft passed if calculation is like
1339 -- compensation
1340 if (instr(l_bnft.cvg_mlt_cd,'CL') <> 0
1341 or l_bnft.cvg_mlt_cd = 'RL') then -- bug 8617671
1342 --
1343 l_bnft_amt := l_bnft.val;
1344 --
1345 else
1346 --
1347 l_bnft_amt := NVL(l_prtt_result.bnft_amt,l_bnft.val) ;
1348 --
1349 end if;
1350 --
1351 --Bug 6519487 when the enrollment is carried forward, we don't want to
1352 --mark additional plans as default. Resetting all other plans back to
1353 --'N' so that user won't get into a situation of default one plan from
1354 --defaulting rules and another plan from carry forward functionality.
1355 --When carrying forward enrollment, remove defaults for all other
1356 -- plans/options in that plan type.
1360 set dflt_flag = 'N'
1357 --Need to be changed with API at a later date.
1358 --
1359 update ben_elig_per_elctbl_chc
1361 where pl_typ_id = l_choice_info.pl_typ_id
1362 and elig_per_elctbl_chc_id <> l_choice_info.elig_per_elctbl_chc_id
1363 and crntly_enrd_flag = 'N' -- Bug 7378468
1364 and dflt_flag = 'Y'
1365 and nvl(pgm_id,-1)= nvl(l_choice_info.pgm_id,-1)
1366 and per_in_ler_id = p_per_in_ler_id ;
1367
1368 --
1369 --END Bug 6519487
1370 --
1371 --Bug 4450214 Moved to the begining of the code
1372 -- ben_sspndd_enrollment.g_cfw_flag := 'Y';
1373 --
1374
1375 /* Added for Enhancement Bug 8716679
1376 To add the electable choices to pl/sql table that are carry forwarded. This pl/sql
1377 is scanned to check whether the enrollment record is already carry forwarded by the carry foward logic
1378 while defaulting and reinstating the explicit elections from intervening LE */
1379 hr_utility.set_location ('Carry Fwd epe '||l_choice_info.elig_per_elctbl_chc_id,199);
1380 l_rdefault_table_cnt := nvl( ben_lf_evt_clps_restore.g_reinstated_defaults.LAST, 0) + 1;
1381 ben_lf_evt_clps_restore.g_reinstated_defaults(l_rdefault_table_cnt) := l_choice_info.elig_per_elctbl_chc_id;
1382 /* End of Enhancement Bug 8716679*/
1383
1384 ben_election_information.election_information
1385 (p_elig_per_elctbl_chc_id => l_choice_info.elig_per_elctbl_chc_id,
1386 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id, -- l_choice_info.prtt_enrt_rslt_id,
1387 p_effective_date => l_effective_dt,
1388 p_enrt_mthd_cd => 'E', --We are making as Explicit [??]
1389 p_business_group_id => p_business_group_id,
1390 p_enrt_bnft_id => l_bnft.enrt_bnft_id,
1391 p_bnft_val => l_bnft_amt,
1392 p_enrt_rt_id1 => l_rt_table(1).enrt_rt_id,
1393 p_rt_val1 => l_rt_table(1).dflt_val,
1394 p_enrt_rt_id2 => l_rt_table(2).enrt_rt_id,
1395 p_rt_val2 => l_rt_table(2).dflt_val,
1396 p_enrt_rt_id3 => l_rt_table(3).enrt_rt_id,
1397 p_rt_val3 => l_rt_table(3).dflt_val,
1398 p_enrt_rt_id4 => l_rt_table(4).enrt_rt_id,
1399 p_rt_val4 => l_rt_table(4).dflt_val,
1400 p_enrt_rt_id5 => l_rt_table(5).enrt_rt_id,
1401 p_rt_val5 => l_rt_table(5).dflt_val,
1402 p_enrt_rt_id6 => l_rt_table(6).enrt_rt_id,
1403 p_rt_val6 => l_rt_table(6).dflt_val,
1407 p_rt_val8 => l_rt_table(8).dflt_val,
1404 p_enrt_rt_id7 => l_rt_table(7).enrt_rt_id,
1405 p_rt_val7 => l_rt_table(7).dflt_val,
1406 p_enrt_rt_id8 => l_rt_table(8).enrt_rt_id,
1408 p_enrt_rt_id9 => l_rt_table(9).enrt_rt_id,
1409 p_rt_val9 => l_rt_table(9).dflt_val,
1410 p_enrt_rt_id10 => l_rt_table(10).enrt_rt_id,
1411 p_rt_val10 => l_rt_table(10).dflt_val,
1412 p_datetrack_mode => l_datetrack_mode,
1413 p_suspend_flag => l_suspend_flag,
1414 p_prtt_enrt_interim_id => l_prtt_enrt_interim_id,
1415 p_prtt_rt_val_id1 => l_rt_table(1).prtt_rt_val_id,
1416 p_prtt_rt_val_id2 => l_rt_table(2).prtt_rt_val_id,
1417 p_prtt_rt_val_id3 => l_rt_table(3).prtt_rt_val_id,
1418 p_prtt_rt_val_id4 => l_rt_table(4).prtt_rt_val_id,
1419 p_prtt_rt_val_id5 => l_rt_table(5).prtt_rt_val_id,
1420 p_prtt_rt_val_id6 => l_rt_table(6).prtt_rt_val_id,
1421 p_prtt_rt_val_id7 => l_rt_table(7).prtt_rt_val_id,
1422 p_prtt_rt_val_id8 => l_rt_table(8).prtt_rt_val_id,
1423 p_prtt_rt_val_id9 => l_rt_table(9).prtt_rt_val_id,
1424 p_prtt_rt_val_id10 => l_rt_table(10).prtt_rt_val_id,
1425 p_ann_rt_val1 => l_rt_table(1).ann_rt_val,
1426 p_ann_rt_val2 => l_rt_table(2).ann_rt_val,
1427 p_ann_rt_val3 => l_rt_table(3).ann_rt_val,
1428 p_ann_rt_val4 => l_rt_table(4).ann_rt_val,
1429 p_ann_rt_val5 => l_rt_table(5).ann_rt_val,
1430 p_ann_rt_val6 => l_rt_table(6).ann_rt_val,
1431 p_ann_rt_val7 => l_rt_table(7).ann_rt_val,
1432 p_ann_rt_val8 => l_rt_table(8).ann_rt_val,
1433 p_ann_rt_val9 => l_rt_table(9).ann_rt_val,
1434 p_ann_rt_val10 => l_rt_table(10).ann_rt_val,
1435 -- 3517682 start
1436 p_pen_attribute_category => l_prtt_result.pen_attribute_category ,
1437 p_pen_attribute1 => l_prtt_result.pen_attribute1 ,
1438 p_pen_attribute2 => l_prtt_result.pen_attribute2 ,
1439 p_pen_attribute3 => l_prtt_result.pen_attribute3 ,
1440 p_pen_attribute4 => l_prtt_result.pen_attribute4 ,
1441 p_pen_attribute5 => l_prtt_result.pen_attribute5 ,
1442 p_pen_attribute6 => l_prtt_result.pen_attribute6 ,
1443 p_pen_attribute7 => l_prtt_result.pen_attribute7 ,
1444 p_pen_attribute8 => l_prtt_result.pen_attribute8 ,
1445 p_pen_attribute9 => l_prtt_result.pen_attribute9 ,
1446 p_pen_attribute10 => l_prtt_result.pen_attribute10 ,
1447 p_pen_attribute11 => l_prtt_result.pen_attribute11 ,
1448 p_pen_attribute12 => l_prtt_result.pen_attribute12 ,
1449 p_pen_attribute13 => l_prtt_result.pen_attribute13 ,
1450 p_pen_attribute14 => l_prtt_result.pen_attribute14 ,
1451 p_pen_attribute15 => l_prtt_result.pen_attribute15 ,
1452 p_pen_attribute16 => l_prtt_result.pen_attribute16 ,
1453 p_pen_attribute17 => l_prtt_result.pen_attribute17 ,
1454 p_pen_attribute18 => l_prtt_result.pen_attribute18 ,
1455 p_pen_attribute19 => l_prtt_result.pen_attribute19 ,
1456 p_pen_attribute20 => l_prtt_result.pen_attribute20 ,
1457 p_pen_attribute21 => l_prtt_result.pen_attribute21 ,
1458 p_pen_attribute22 => l_prtt_result.pen_attribute22,
1459 p_pen_attribute23 => l_prtt_result.pen_attribute23,
1460 p_pen_attribute24 => l_prtt_result.pen_attribute24,
1461 p_pen_attribute25 => l_prtt_result.pen_attribute25,
1462 p_pen_attribute26 => l_prtt_result.pen_attribute26,
1463 p_pen_attribute27 => l_prtt_result.pen_attribute27,
1464 p_pen_attribute28 => l_prtt_result.pen_attribute28,
1465 p_pen_attribute29 => l_prtt_result.pen_attribute29,
1466 p_pen_attribute30 => l_prtt_result.pen_attribute30,
1467 -- 3517682 end
1471 p_dpnt_actn_warning => l_dpnt_actn_warning,
1468 p_object_version_number => l_object_version_number,
1469 p_effective_start_date => l_effective_start_date,
1470 p_effective_end_date => l_effective_end_date,
1472 p_bnf_actn_warning => l_bnf_actn_warning,
1473 p_ctfn_actn_warning => l_ctfn_actn_warning);
1474 --
1475 -- ben_sspndd_enrollment.g_cfw_flag := 'N';
1476 --
1477 l_choice_info.suspended := l_suspend_flag;
1478 --
1479 if l_choice_info.suspended = 'Y' then
1480 --
1481 l_person_susp := 'Y';
1482 l_choice_info.stage := 'S';
1483 --
1487 l_pen_count := l_pen_count + 1;
1484 end if;
1485 --
1486 --
1488 l_crd_fwd_pen_id(l_pen_count) := l_prtt_enrt_rslt_id;
1489 --
1490 --
1491
1492 --
1493 -- update the default flag to N on the interim epe
1494 --
1495 /* Bug 5474065 : Updating the DFLT_FLAG prevents interim enrollment when interim
1496 code has Current - Default
1497 update ben_elig_per_elctbl_chc
1498 set dflt_flag = 'N'
1499 where dflt_flag = 'Y'
1500 and per_in_ler_id = l_choice_info.per_in_ler_id
1501 and pl_id = l_choice_info.pl_id
1502 and oipl_id <> l_choice_info.oipl_id
1503 and nvl(pgm_id,-1) = nvl(l_choice_info.pgm_id,-1)
1504 and elig_per_elctbl_chc_id <> l_choice_info.elig_per_elctbl_chc_id;
1505 */
1506 /* It is not required to do carryforward since it is handled in beneadeb and benauten
1507 open c_prtt_result(l_prtt_enrt_rslt_id, --ML l_choice_info.prtt_enrt_rslt_id,
1508 l_effective_dt);
1509 --
1510 fetch c_prtt_result into l_prtt_result;
1511 if c_prtt_result%notfound then
1512 --
1513 close c_prtt_result;
1514 fnd_message.set_name('BEN','BEN_91711_ENRT_RSLT_NOT_FOUND');
1515 fnd_message.set_token('PROC',l_proc);
1516 fnd_message.set_token('ID',l_prtt_enrt_rslt_id); -- l_choice_info.prtt_enrt_rslt_id);
1517 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1518 fnd_message.set_token('LER_ID',to_char(p_ler_id));
1519 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
1520 fnd_message.raise_error;
1521 --
1522 else
1523 --
1524 l_choice_info.enrt_cvg_strt_dt := l_prtt_result.enrt_cvg_strt_dt;
1525 l_choice_info.enrt_cvg_end_dt := l_prtt_result.enrt_cvg_thru_dt;
1526 --
1527 -- after the enhncemnt # 2685018 cryfwd_elig_dpnt_cd value is concated with
1528 -- result id from where the dpnt carry forwarded , this will seprate the code from
1529 --- result id
1530
1531 l_prev_prtt_enrt_rslt_id := null; -- Reintializing the previous enrt result id
1532 l_cryfwd_elig_dpnt_cd := l_choice_info.cryfwd_elig_dpnt_cd ;
1533 l_prev_rslt_id_at := instr(l_cryfwd_elig_dpnt_cd, '^') ;
1534 --- if the result id concated with the code, then the caht '^' must be aprt of the
1535 --- the code
1536
1537 if l_prev_rslt_id_at > 0 then
1538 --- if the to_number errors , catch the exception
1539 Begin
1540 l_prev_prtt_enrt_rslt_id := to_number(substr(l_cryfwd_elig_dpnt_cd,l_prev_rslt_id_at+1) );
1541 Exception
1542 when value_error then
1543 l_prev_prtt_enrt_rslt_id := null;
1544 End ;
1545
1546 l_cryfwd_elig_dpnt_cd := substr(l_cryfwd_elig_dpnt_cd,1,l_prev_rslt_id_at-1) ;
1547 ---
1548 end if ;
1549
1550
1551 hr_utility.set_location('l_cryfwd_elig_dpnt_cd '||l_cryfwd_elig_dpnt_cd,744);
1552 hr_utility.set_location('l_prev_prtt_enrt_rslt_id '||l_prev_prtt_enrt_rslt_id,744);
1553
1554 if l_datetrack_mode = hr_api.g_insert and l_cryfwd_elig_dpnt_cd = 'CFRRWP' then
1555
1556 hr_utility.set_location('cvg strt cd: '||l_choice_info.dpnt_cvg_strt_dt_cd,10);
1557 -- p_effective_date is now changed to l_effective_dt , when ever LE reprocessed
1558 -- result created as on effective date and automeatic enrollment called with
1559 -- lE_ocurd_Dt as affective date so there is no result as on effective date (le_ocrd_dt)
1560 -- this is fixed sending l_effective_dt # 3042033
1561
1562 reinstate_dpnt(p_pgm_id =>l_choice_info.pgm_id,
1563 p_pl_id => l_choice_info.pl_id,
1564 p_oipl_id => l_choice_info.oipl_id,
1565 p_business_group_id => p_business_group_id,
1566 p_person_id => p_person_id,
1567 p_per_in_ler_id => l_per_in_ler_id,
1568 p_elig_per_elctbl_chc_id => l_choice_info.elig_per_elctbl_chc_id,
1569 p_dpnt_cvg_strt_dt_cd => l_choice_info.dpnt_cvg_strt_dt_cd,
1570 p_dpnt_cvg_strt_dt_rl => l_choice_info.dpnt_cvg_strt_dt_rl,
1571 p_enrt_cvg_strt_dt => l_choice_info.enrt_cvg_strt_dt,
1572 p_effective_date => l_effective_dt,
1573 p_prev_prtt_enrt_rslt_id => l_prev_prtt_enrt_rslt_id
1574 );
1575 end if;
1576 end if;
1577 --
1578 close c_prtt_result;
1579 --
1580 */
1581 -- Do Post enrollment - Writes elecment entries, calls close enrollment
1582 --
1583 if l_choice_info.pgm_id is null then
1584 --
1585 -- Invoke post result process
1586 --
1587 ben_proc_common_enrt_rslt.process_post_results
1588 (p_person_id => p_person_id,
1589 p_enrt_mthd_cd => 'E',
1590 p_effective_date => l_effective_dt,
1591 p_business_group_id => p_business_group_id,
1592 p_per_in_ler_id => l_per_in_ler_id);
1593 --
1594 /*
1598 p_pl_id => l_choice_info.pl_id,
1595 ben_proc_common_enrt_rslt.process_post_enrollment
1596 (p_per_in_ler_id => l_per_in_ler_id,
1597 p_pgm_id => l_choice_info.pgm_id,
1599 p_cls_enrt_flag => l_cls_enrt_flag,
1600 p_enrt_mthd_cd => 'E',
1601 p_proc_cd => null,
1602 p_person_id => p_person_id,
1606 --
1603 p_business_group_id => p_business_group_id,
1604 p_effective_date => p_effective_date);
1605 */
1607 end if;
1608 --
1609 -- Do multi row edit stuff
1610 --
1611 if l_prev_pgm_id = -99999 then
1612 --
1613 l_prev_pgm_id := l_choice_info.pgm_id;
1614 l_prev_eff_dt := l_effective_dt;
1615 --
1616 elsif nvl(l_prev_pgm_id,-1) <> nvl(l_choice_info.pgm_id,-1) then
1617
1618 -- call multi-row edit if per-in-ler has no default nor explict
1619 -- choices available.
1620 /*
1621 if l_cls_enrt_flag then
1622 ben_prtt_enrt_result_api.multi_rows_edit
1623 (p_person_id => p_person_id,
1624 p_effective_date => l_effective_dt,
1625 p_business_group_id => p_business_group_id,
1626 p_pgm_id => l_prev_pgm_id);
1627 end if;
1628 */
1629 --
1630 -- Invoke process post enrollment
1631 --
1632 if l_prev_pgm_id is not null then
1633 --
1634 -- Invoke post result process
1635 --
1636 ben_proc_common_enrt_rslt.process_post_results
1637 (p_person_id => p_person_id,
1638 p_enrt_mthd_cd => 'E',
1639 p_effective_date => l_effective_dt,
1640 p_business_group_id => p_business_group_id,
1641 p_per_in_ler_id => l_per_in_ler_id);
1642 /*
1643 ben_proc_common_enrt_rslt.process_post_enrollment
1644 (p_per_in_ler_id => l_per_in_ler_id,
1645 p_pgm_id => l_prev_pgm_id,
1646 p_pl_id => null,
1647 p_enrt_mthd_cd => 'E',
1648 p_cls_enrt_flag => l_cls_enrt_flag,
1649 p_proc_cd => null,
1650 p_person_id => p_person_id,
1651 p_business_group_id => p_business_group_id,
1652 p_effective_date => p_effective_date);
1653 */
1654 --
1655 end if;
1656 --
1657 l_prev_pgm_id := l_choice_info.pgm_id;
1658 l_prev_eff_dt := l_effective_dt;
1659 --
1660 end if;
1661
1662 /* Bug 8900007:Reinstate the action items and certifications of carry forwarded enrollments*/
1663 if(ben_lf_evt_clps_restore.g_bckdt_sspndd_pen_list.count is not null) then
1664 hr_utility.set_location('sspnd list1 ',310);
1665 if(ben_lf_evt_clps_restore.g_bckdt_sspndd_pen_list.count > 0 ) then
1666 for i IN ben_lf_evt_clps_restore.g_bckdt_sspndd_pen_list.FIRST..ben_lf_evt_clps_restore.g_bckdt_sspndd_pen_list.LAST loop
1667 hr_utility.set_location('sspnd list ' || ben_lf_evt_clps_restore.g_bckdt_sspndd_pen_list(i).PGM_ID||' '||
1668 ben_lf_evt_clps_restore.g_bckdt_sspndd_pen_list(i).pl_id , 10);
1669 hr_utility.set_location('l_choice_info ' || l_choice_info.pgm_id||' '||l_choice_info.pl_id , 10);
1670 if( nvl(ben_lf_evt_clps_restore.g_bckdt_sspndd_pen_list(i).PGM_ID,-1) = nvl(l_choice_info.pgm_id,-1)
1671 and nvl(ben_lf_evt_clps_restore.g_bckdt_sspndd_pen_list(i).pl_id,-1) = nvl(l_choice_info.pl_id,-1)
1672 and nvl(ben_lf_evt_clps_restore.g_bckdt_sspndd_pen_list(i).pl_typ_id,-1) = nvl(l_choice_info.pl_typ_id,-1) ) then
1673 ben_lf_evt_clps_restore.reinstate_pcs_per_pen(
1674 p_person_id => ben_lf_evt_clps_restore.g_bckdt_sspndd_pen_list(i).person_id
1675 ,p_bckdt_prtt_enrt_rslt_id => ben_lf_evt_clps_restore.g_bckdt_sspndd_pen_list(i).prtt_enrt_rslt_id
1676 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id -- pen_ovn_number
1677 ,p_rslt_object_version_number => l_object_version_number -- prtt_enrt_rslt_id
1678 ,p_business_group_id => p_business_group_id
1679 ,p_per_in_ler_id => l_per_in_ler_id
1680 ,p_effective_date => l_effective_start_date
1681 ,p_bckdt_per_in_ler_id => ben_lf_evt_clps_restore.g_bckdt_sspndd_pen_list(i).PER_IN_LER_ID
1682 ,p_prtt_enrt_actn_id => null
1683 ,p_bckdt_prtt_enrt_actn_id => null);
1684
1685 ben_lf_evt_clps_restore.reinstate_pea_per_pen(
1686 p_person_id => ben_lf_evt_clps_restore.g_bckdt_sspndd_pen_list(i).person_id
1687 ,p_bckdt_prtt_enrt_rslt_id => ben_lf_evt_clps_restore.g_bckdt_sspndd_pen_list(i).prtt_enrt_rslt_id
1691 ,p_per_in_ler_id => l_per_in_ler_id
1688 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id -- pen_ovn_number
1689 ,p_rslt_object_version_number => l_object_version_number -- prtt_enrt_rslt_id
1690 ,p_business_group_id => p_business_group_id
1692 ,p_effective_date => l_effective_start_date
1693 ,p_bckdt_per_in_ler_id => ben_lf_evt_clps_restore.g_bckdt_sspndd_pen_list(i).PER_IN_LER_ID
1694 );
1695 end if;
1696 end loop;
1697 end if;
1698 end if;
1699 /* End Bug 8900007:*/
1700 --
1701 end loop;
1702 --
1703 close c_choice_info;
1704 --
1705 -- Check if last multi edit passed
1706 --
1707 if l_prev_pgm_id <> -99999 then
1708 --
1709 /*
1710 if l_cls_enrt_flag then
1711 ben_prtt_enrt_result_api.multi_rows_edit
1712 (p_person_id => p_person_id,
1713 p_effective_date => l_prev_eff_dt,
1714 p_business_group_id => p_business_group_id,
1715 p_pgm_id => l_prev_pgm_id);
1716 end if;
1717 */
1718 --
1719 -- Do post enrollment
1720 --
1721 if l_prev_pgm_id is not null then
1722 --
1723 --
1724 -- Invoke post result process
1725 --
1726 ben_proc_common_enrt_rslt.process_post_results
1727 (p_person_id => p_person_id,
1728 p_enrt_mthd_cd => 'E',
1729 p_effective_date => l_effective_dt,
1730 p_business_group_id => p_business_group_id,
1731 p_per_in_ler_id => l_per_in_ler_id);
1732 --
1733 /*
1734 ben_proc_common_enrt_rslt.process_post_enrollment
1735 (p_per_in_ler_id => l_per_in_ler_id,
1736 p_pgm_id => l_prev_pgm_id,
1737 p_pl_id => null,
1738 p_enrt_mthd_cd => 'E',
1739 p_cls_enrt_flag => l_cls_enrt_flag,
1740 p_proc_cd => null,
1741 p_person_id => p_person_id,
1742 p_business_group_id => p_business_group_id,
1743 p_effective_date => p_effective_date);
1744 */
1745 --
1746 end if;
1747 --
1748 end if;
1749 --
1750 -- 6057157 : Carry Forward Certificatons which were provided on a future
1751 -- date to the current pil.
1752 --
1753 if (l_crd_fwd_pen_id.COUNT > 0) then
1754 for i in l_crd_fwd_pen_id.FIRST..l_crd_fwd_pen_id.LAST loop
1755 --
1756 hr_utility.set_location('CRD FWD CERT l_crd_fwd_pen_id(i) ' || l_crd_fwd_pen_id(i) , 10);
1757 --
1758 reinstate_prvdd_ctfn_items(p_prtt_enrt_rslt_id => l_crd_fwd_pen_id(i)
1759 ,p_per_in_ler_id => p_per_in_ler_id
1760 ,p_business_group_id => p_business_group_id
1761 ,p_effective_date => p_effective_date);
1762
1763 end loop;
1764 end if;
1765 --
1766 ben_sspndd_enrollment.g_cfw_flag := 'N';
1767 hr_utility.set_location('Leaving: '||l_proc,10);
1768 --
1769 exception
1770 when others then
1771 ben_sspndd_enrollment.g_cfw_flag := 'N';
1772 raise;
1773
1774 end process_person;
1775 --
1776 procedure unprocess_susp_enrt_past_pil(p_prtt_enrt_rslt_id in number,
1777 p_per_in_ler_id in number,
1778 p_business_group_id in number) is
1779 --
1780 cursor c_get_past_pil (p_per_in_ler_id number) is
1784 where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1781 select max(pea.per_in_ler_id)
1782 from ben_prtt_enrt_actn_f pea,
1783 ben_per_in_ler pil
1785 and pea.per_in_ler_id <> p_per_in_ler_id
1786 and pea.business_group_id = p_business_group_id
1787 and pea.per_in_ler_id = pil.per_in_ler_id
1788 and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD');
1789 --
1790 cursor c_actn_item_for_past_pil (p_per_in_ler_id number) is
1791 select prtt_enrt_actn_id, effective_start_date, object_version_number
1792 from ben_prtt_enrt_actn_f
1793 where per_in_ler_id = p_per_in_ler_id
1794 and prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1795 and effective_end_date < hr_api.g_eot
1796 and business_group_id = p_business_group_id
1797 order by prtt_enrt_actn_id, effective_start_date; -- 5394656
1798 l_actn_item c_actn_item_for_past_pil%rowtype;
1799 --
1800 cursor c_enrt_ctfn_for_past_pil (p_prtt_enrt_actn_id number) is
1801 select prtt_enrt_ctfn_prvdd_id, effective_start_date, object_version_number
1802 from ben_prtt_enrt_ctfn_prvdd_f
1803 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1804 and prtt_enrt_actn_id = p_prtt_enrt_actn_id
1805 and effective_end_date < hr_api.g_eot
1806 and business_group_id = p_business_group_id
1807 order by prtt_enrt_ctfn_prvdd_id, effective_start_date; -- 5394656
1808 l_enrt_ctfn c_enrt_ctfn_for_past_pil%rowtype;
1809 --
1810 cursor c_check_prem_active is
1811 select 1
1812 from ben_prtt_prem_f
1813 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1814 and effective_end_date = hr_api.g_eot;
1815 l_check_prem_active c_check_prem_active%rowtype;
1816 --
1817 cursor c_ended_prem_details is
1818 select ppm.prtt_prem_id, ppm.effective_start_date, ppm.object_version_number
1819 from ben_prtt_prem_f ppm
1820 where ppm.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1821 and ppm.effective_end_date <> hr_api.g_eot
1822 and not exists (select 1
1823 from ben_prtt_prem_f ppm2
1824 where ppm2.prtt_prem_id = ppm.prtt_prem_id
1825 and ppm2.effective_end_date > ppm.effective_end_date)
1826 order by effective_start_date desc;
1827 l_ended_prem_details c_ended_prem_details%rowtype;
1828 --
1829 l_proc varchar2(80) := g_package||'.unprocess_susp_enrt_past_pil';
1830 l_per_in_ler_id number;
1831 l_prtt_enrt_actn_id number;
1832 l_effective_start_date date;
1833 l_effective_end_date date;
1834 l_object_version_number number;
1835 l_ppe_object_version_number number;
1836 --
1837 l_prev_prtt_enrt_actn_id number;
1838 l_prev_prtt_enrt_ctfn_prvdd_id number;
1839 --
1840 begin
1841 --
1842 hr_utility.set_location ('Entering ' || l_proc , 1230);
1843 l_per_in_ler_id := p_per_in_ler_id;
1844 l_prev_prtt_enrt_actn_id := -1;
1845 --
1846 for l_actn_item in c_actn_item_for_past_pil (l_per_in_ler_id) loop
1847 --
1848 -- 5394656: Since 'FUTURE_CHANGE' mode deletes all future records
1849 -- we need to run the delete_api only once for every prtt_enrt_actn_id
1850 --
1851 l_prtt_enrt_actn_id := l_actn_item.prtt_enrt_actn_id;
1852 --
1853 if (l_prev_prtt_enrt_actn_id <> l_prtt_enrt_actn_id) then
1854 --
1855 l_prev_prtt_enrt_actn_id := l_prtt_enrt_actn_id;
1856 --
1857 -- Un-enddate action item record
1858 --
1859 l_object_version_number := l_actn_item.object_version_number;
1860 --
1861 ben_pea_del.del(
1862 p_prtt_enrt_actn_id => l_actn_item.prtt_enrt_actn_id,
1863 p_effective_start_date => l_effective_start_date,
1864 p_effective_end_date => l_effective_end_date,
1865 p_object_version_number => l_object_version_number,
1866 p_effective_date => l_actn_item.effective_start_date,
1867 p_datetrack_mode => hr_api.g_future_change);
1868 --
1869 l_prev_prtt_enrt_ctfn_prvdd_id := -1;
1870 --
1871 -- Un-enddate enrollment certification record(s)
1872 --
1873 for l_enrt_ctfn in c_enrt_ctfn_for_past_pil (l_prtt_enrt_actn_id) loop
1874 --
1875 l_object_version_number := l_enrt_ctfn.object_version_number;
1876 --
1877 -- 5394656: Since 'FUTURE_CHANGE' mode deletes all future records
1878 -- we need to run the delete_api only once for every prtt_enrt_ctfn_prvdd_id
1879 --
1880 if (l_prev_prtt_enrt_ctfn_prvdd_id <> l_enrt_ctfn.prtt_enrt_ctfn_prvdd_id) then
1881 --
1882 l_prev_prtt_enrt_ctfn_prvdd_id := l_enrt_ctfn.prtt_enrt_ctfn_prvdd_id;
1883 --
1884 ben_pcs_del.del(
1885 p_prtt_enrt_ctfn_prvdd_id => l_enrt_ctfn.prtt_enrt_ctfn_prvdd_id,
1886 p_effective_start_date => l_effective_start_date,
1887 p_effective_end_date => l_effective_end_date,
1888 p_object_version_number => l_object_version_number,
1889 p_effective_date => l_enrt_ctfn.effective_start_date,
1890 p_datetrack_mode => hr_api.g_future_change);
1891 end if;
1892 --
1893 end loop;
1894 end if;
1895 --
1896 end loop;
1897 --
1898 -- Process premium, if ended (will be needed for ineligible/due date past cases)
1899 --
1900 open c_check_prem_active;
1901 fetch c_check_prem_active into l_check_prem_active;
1902 if c_check_prem_active%notfound then
1903 --
1904 -- Unend most recent premiums
1905 --
1909 p_prtt_prem_id => l_ended_prem_details.prtt_prem_id,
1906 for l_ended_prem_details in c_ended_prem_details loop
1907 l_ppe_object_version_number := l_ended_prem_details.object_version_number;
1908 ben_ppe_del.del(
1910 p_effective_start_date => l_effective_start_date,
1911 p_effective_end_date => l_effective_end_date,
1912 p_object_version_number => l_ppe_object_version_number,
1913 p_effective_date => l_ended_prem_details.effective_start_date,
1914 p_datetrack_mode => hr_api.g_future_change);
1915 end loop;
1916 --
1917 end if;
1918 close c_check_prem_active;
1919 --
1920 hr_utility.set_location ('Leaving ' || l_proc, 1230);
1921 end unprocess_susp_enrt_past_pil;
1922 procedure carry_farward_results(
1923 p_person_id in number,
1924 p_per_in_ler_id in number,
1925 p_ler_id in number,
1926 p_business_group_id in number,
1927 p_mode in varchar2,
1928 p_effective_date in date) is
1929 --
1930 l_proc varchar2(80) := g_package||'carry_farward_results';
1931 l_person_id number;
1932 --
1933 cursor c_pen_sus is
1934 select pen.prtt_enrt_rslt_id,
1935 pen.per_in_ler_id
1936 from ben_prtt_enrt_rslt_f pen,
1937 ben_per_in_ler pil
1938 where pen.sspndd_flag = 'Y'
1939 and pen.per_in_ler_id <> pil.per_in_ler_id
1940 and pil.per_in_ler_id = p_per_in_ler_id
1941 and pen.person_id = pil.person_id
1942 and pen.effective_end_date = hr_api.g_eot
1943 and pen.enrt_cvg_thru_dt = hr_api.g_eot;
1944 l_pen_sus c_pen_sus%rowtype;
1945 --
1946 begin
1947 --
1948 hr_utility.set_location('Entering: '||l_proc,10);
1949 hr_utility.set_location('p_person_id'||p_person_id,10);
1950 hr_utility.set_location('p_per_in_ler_id'||p_per_in_ler_id,10);
1951 hr_utility.set_location('p_business_group_id'||p_business_group_id,10);
1952 hr_utility.set_location('p_mode'||p_mode,10);
1953 hr_utility.set_location('p_effective_date'||p_effective_date,10);
1954 --
1955 if p_mode not in ('L','C', 'M') then
1956 --
1957 -- Wrong mode my friend!
1958 --
1959 return;
1960 --
1961 end if;
1962 --
1963 process_person(p_person_id => p_person_id,
1964 p_business_group_id => p_business_group_id,
1965 p_per_in_ler_id => p_per_in_ler_id,
1966 p_ler_id => p_ler_id,
1967 p_effective_date => p_effective_date);
1968 --
1969 hr_utility.set_location('Leaving: '||l_proc,10);
1970 --
1971 --Reopen the closed action items of the enrollment results for which there are no
1972 --electable choices exists in this life event.
1973 open c_pen_sus;
1974 loop
1975 fetch c_pen_sus into l_pen_sus;
1976 if c_pen_sus%notfound then
1977 exit;
1978 end if;
1979
1980 unprocess_susp_enrt_past_pil
1981 (l_pen_sus.prtt_enrt_rslt_id,
1982 l_pen_sus.per_in_ler_id,
1983 p_business_group_id);
1984
1985 end loop;
1986 close c_pen_sus;
1987 --
1988 end carry_farward_results;
1989 end ben_carry_forward_items;