[Home] [Help]
PACKAGE BODY: APPS.BEN_CARRY_FORWARD_ITEMS
Source
1 package body ben_carry_forward_items as
2 /* $Header: bencfwsu.pkb 120.21.12010000.5 2008/09/08 10:16:04 bachakra ship $ */
3
4 g_package varchar2(31) := 'ben_carry_forward_items.';
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)
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;
45 l_pen_rec c_pen%rowtype;
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 --
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
182 --
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
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
299 ,p_datetrack_mode => l_datetrack_mode
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 ;
378 from ben_prtt_enrt_rslt_f pen
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
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
430 pdp_old.EFFECTIVE_END_DATE,
431 pdp_old.CVG_STRT_DT,
432 pdp_old.CVG_THRU_DT,
433 pdp_old.CVG_PNDG_FLAG,
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
509 from ben_oipl_f
506 (r.oipl_id = p_oipl_id)
507 or
508 (r.opt_id = (select opt_id
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
559 not exists
560 (select 'x' from ben_dsgn_rqmt_rlshp_typ drt
561 where drt.dsgn_rqmt_id = v_dsgn_rqmt_id
562 )
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;
631 hr_utility.set_location('2 : '||l_proc,745);
628 if c_previous_pgm_ptip_result%notfound then null;
629 end if;
630 close c_previous_pgm_ptip_result;
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 );
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' ;
685 -- exit ;
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,
736 p_datetrack_mode => hr_api.g_insert,
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,
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
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
790 and exists (select 'x' -- To confirm if both PENs are for the same comp.object
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;
836 ,p_effective_start_date => l_esd
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
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.
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
914 AND pen.prtt_enrt_rslt_stat_cd is null
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
966 ben_pil_elctbl_chc_popl pel
963 from ben_elig_per_elctbl_chc epe,
964 ben_enrt_bnft enb,
965 ben_per_in_ler pil,
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 and pen.prtt_enrt_rslt_id = NVL(enb.prtt_enrt_rslt_id,
983 epe.prtt_enrt_rslt_id)
984 and pen.enrt_cvg_thru_dt = hr_api.g_eot
985 and pen.effective_end_date = hr_api.g_eot)
986 and exists (select null
987 from ben_prtt_enrt_rslt_f pen
988 where pen.pl_id = epe.pl_id
989 and pen.prtt_enrt_rslt_stat_cd IS NULL
990 and pen.per_in_ler_id <> epe.per_in_ler_id
991 and pen.prtt_enrt_rslt_id = NVL(enb.prtt_enrt_rslt_id,
992 epe.prtt_enrt_rslt_id)
993 and pen.sspndd_flag = 'Y'
994 and pen.enrt_cvg_thru_dt = hr_api.g_eot
995 -- and pen.effective_end_date = hr_api.g_eot -- 6156874
996 and pen.effective_end_date >= l_lf_evt_ocrd_dt )
997 order by epe.pgm_id, epe.pl_id;
998 --
999 l_choice_info c_choice_info%rowtype;
1000 --
1001 cursor c_elctbl_epe is
1002 select null
1003 from ben_elig_per_elctbl_chc epe,
1004 ben_per_in_ler pil,
1005 ben_pil_elctbl_chc_popl pel
1006 where epe.elctbl_flag = 'Y'
1007 --and pil.business_group_id = p_business_group_id
1008 --and epe.business_group_id = pil.business_group_id
1009 --and pel.business_group_id = epe.business_group_id
1010 and pil.person_id = p_person_id
1011 and epe.per_in_ler_id = pil.per_in_ler_id
1012 and pel.per_in_ler_id = epe.per_in_ler_id
1013 and pil.per_in_ler_stat_cd = 'STRTD'
1014 and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
1015 and (epe.pgm_id is not null
1016 and not exists(select null
1017 from ben_prtt_enrt_rslt_f pen
1018 where pen.pgm_id = epe.pgm_id
1019 and pen.per_in_ler_id = epe.per_in_ler_id
1020 and pen.enrt_cvg_thru_dt = hr_api.g_eot
1021 and pen.effective_end_date = hr_api.g_eot)
1022 or epe.pl_id is not null
1023 and not exists(select null
1024 from ben_prtt_enrt_rslt_f pen
1025 where pen.pl_id = epe.pl_id
1026 and pen.per_in_ler_id = epe.per_in_ler_id
1027 and pen.enrt_cvg_thru_dt = hr_api.g_eot
1028 and pen.effective_end_date = hr_api.g_eot));
1029 --
1030 cursor c_pgm_enrt_dt(v_elig_per_elctbl_chc_id number,v_pgm_id number) is
1031 select pel.enrt_perd_strt_dt
1032 from ben_pil_elctbl_chc_popl pel,ben_elig_per_elctbl_chc epe
1033 where pel.pgm_id = v_pgm_id
1034 and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
1035 and epe.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id;
1036
1037 l_pgm_enrt_dt c_pgm_enrt_dt%rowtype;
1038
1039 cursor c_pl_enrt_dt(v_elig_per_elctbl_chc_id number,v_pl_id number) is
1040 select pel.enrt_perd_strt_dt
1041 from ben_pil_elctbl_chc_popl pel,ben_elig_per_elctbl_chc epe
1042 where pel.pl_id = v_pl_id
1043 and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
1044 and epe.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id;
1045
1046 l_pl_enrt_dt c_pl_enrt_dt%rowtype;
1047
1048 cursor c_bnft(v_elig_per_elctbl_chc_id number, v_bnft_ordr_num number ) is
1049 select enb.enrt_bnft_id,
1050 decode(enb.entr_val_at_enrt_flag,'Y',enb.dflt_val,enb.val) val,
1051 enb.dflt_flag,
1052 enb.prtt_enrt_rslt_id,
1053 enb.cvg_mlt_cd,
1054 enb.crntly_enrld_flag
1055 from ben_enrt_bnft enb
1056 where enb.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
1057 and enb.crntly_enrld_flag = 'Y'
1058 and enb.prtt_enrt_rslt_id is not NULL
1059 and enb.ordr_num = v_bnft_ordr_num
1060 ;
1061 --
1062 /* REMOVED UNUSED CODE HERE */
1063 --
1064 l_pgm_id number;
1065 l_pl_id number;
1066 l_pil_id number;
1067 l_oipl_id number;
1068 -- l_lf_evt_ocrd_dt date;
1069 l_bnft c_bnft%rowtype;
1073 l_per_in_ler_id number := p_per_in_ler_id ;
1070 l_bnft_reset c_bnft%rowtype; -- BBULUSU CODE
1071 l_dflt_bnft c_bnft%rowtype;
1072 l_dflt_found boolean;
1074 --
1075 cursor c_rt(v_elig_per_elctbl_chc_id number,
1076 v_enrt_bnft_id number) is
1077 select ecr.enrt_rt_id,
1078 nvl(ecr.val,ecr.dflt_val) default_value,
1079 nvl(ecr.ann_dflt_val,ecr.ann_val) ann_rt_val,
1080 ecr.prtt_rt_val_id
1081 from ben_enrt_rt ecr
1082 where ecr.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
1083 and ecr.business_group_id = p_business_group_id
1084 and ecr.entr_val_at_enrt_flag = 'Y'
1085 and ecr.spcl_rt_enrt_rt_id is null
1086 union
1087 select ecr.enrt_rt_id,
1088 nvl(ecr.val,ecr.dflt_val) default_value,
1089 nvl(ecr.ann_dflt_val,ecr.ann_val) ann_rt_val,
1090 ecr.prtt_rt_val_id
1091 from ben_enrt_rt ecr
1092 where ecr.enrt_bnft_id = v_enrt_bnft_id
1093 and ecr.business_group_id = p_business_group_id
1094 and ecr.entr_val_at_enrt_flag = 'Y'
1095 and ecr.spcl_rt_enrt_rt_id is null
1096 ;
1097 --
1098 l_rt c_rt%rowtype;
1099 --
1100 cursor c_prv(p_prtt_enrt_rslt_id number, p_per_in_ler_id number) is
1101 select prv.*
1102 from ben_prtt_rt_val prv
1103 where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1104 and prv.per_in_ler_id = p_per_in_ler_id
1105 and prv.prtt_rt_val_stat_cd is NULL ;
1106 --
1107 cursor c_ecr(p_prtt_rt_val_id number) is
1108 select ecr.rt_strt_dt,
1109 ecr.rt_strt_dt_cd,
1110 ecr.rt_strt_dt_rl,
1111 nvl(ecr.elig_per_elctbl_chc_id,enb.elig_per_elctbl_chc_id) elig_per_elctbl_chc_id
1112 from ben_enrt_rt ecr,
1113 ben_enrt_bnft enb
1114 where ecr.prtt_rt_val_id = p_prtt_rt_val_id
1115 and ecr.enrt_bnft_id = enb.enrt_bnft_id (+) ;
1116 --
1117 l_ecr c_ecr%rowtype;
1118 --
1119 l_cvg_strt_dt date;
1120
1121 type g_rt_rec is record
1122 (enrt_rt_id ben_enrt_rt.enrt_rt_id%type,
1123 dflt_val ben_enrt_rt.dflt_val%type,
1124 prtt_rt_val_id ben_enrt_rt.prtt_rt_val_id%type,
1125 ann_rt_val ben_enrt_rt.ann_val%type);
1126 --
1127 type g_rt_table is table of g_rt_rec index by binary_integer;
1128 --
1129 l_rt_table g_rt_table;
1130 --
1131 type g_pen_id_table is table of number index by binary_integer;
1132 l_crd_fwd_pen_id g_pen_id_table;
1133 l_pen_count number;
1134 --
1135
1136 -- Local Variables
1137 --
1138 l_proc varchar2(80) := g_package||'process_person';
1139 l_dpnt_actn_warning boolean;
1140 l_bnf_actn_warning boolean;
1141 l_ctfn_actn_warning boolean;
1142 l_new_election boolean := false;
1143 l_datetrack_mode varchar2(30);
1144 l_prev_pgm_id number := -99999;
1145 l_prtt_enrt_interim_id number;
1146 l_rslt_id number;
1147 l_count number;
1148 l_object_version_number number;
1149 l_ovn_intr number;
1150 l_suspend_flag varchar2(30);
1151 l_dummy varchar2(30);
1152 l_person_susp varchar2(30) := 'N';
1153 l_cls_enrt_flag boolean := true;
1154 l_effective_start_date date;
1155 l_effective_end_date date;
1156 l_eff_start_date date;
1157 l_eff_end_date date;
1158 l_effective_dt date;
1159 l_prev_eff_dt date;
1160 l_elig_cvrd_dpnt_id ben_elig_cvrd_dpnt_f.elig_cvrd_dpnt_id%TYPE;
1161
1162 l_rec ben_env_object.g_global_env_rec_type;
1163 l_cryfwd_elig_dpnt_cd varchar2(30) ;
1164 l_prev_rslt_id_at number := 0 ;
1165 l_prev_prtt_enrt_rslt_id number ;
1166 l_prtt_enrt_rslt_id number ;
1167 l_bnft_amt number;
1168 --
1169 begin
1170 --
1171 g_debug := hr_utility.debug_enabled;
1172 --
1173 hr_utility.set_location('Entering: '||l_proc,10);
1174 --
1175 -- ben_env_object.get(p_rec => l_rec);
1176 --
1177 hr_utility.set_location('Effective date: '||p_effective_date,10);
1178 --
1179 ben_sspndd_enrollment.g_cfw_flag := 'Y';
1180 --
1181 l_pen_count := 0;
1182 l_crd_fwd_pen_id.delete;
1183 --
1184 open c_pil ;
1185 fetch c_pil into l_lf_evt_ocrd_dt;
1186 close c_pil ;
1187 --
1188 open c_choice_info;
1189 --
1190 loop
1191 --
1192 l_bnft := l_bnft_reset; -- BBULUSU CODE
1193 --
1194 fetch c_choice_info into l_choice_info;
1195 --
1196 hr_utility.set_location('cvg strt cd: '||l_choice_info.dpnt_cvg_strt_dt_cd,10);
1197 hr_utility.set_location('epe id : '||l_choice_info.elig_per_elctbl_chc_id,10);
1198 --
1199 exit when c_choice_info%notfound;
1200 --
1201 -- Get participant enrollment result information
1202
1203 hr_utility.set_location('cvg strt cd: '||l_choice_info.dpnt_cvg_strt_dt_cd,10);
1204 hr_utility.set_location('epe id : '||l_choice_info.elig_per_elctbl_chc_id,10);
1205 --
1206 if l_choice_info.crntly_enrd_flag = 'Y' then
1207 --
1208 open c_prtt_result(l_choice_info.prtt_enrt_rslt_id,
1209 p_effective_date);
1213 --
1210 --
1211 fetch c_prtt_result into l_prtt_result;
1212 if c_prtt_result%notfound then
1214 close c_prtt_result;
1215 fnd_message.set_name('BEN','BEN_91711_ENRT_RSLT_NOT_FOUND');
1216 fnd_message.set_token('PROC',l_proc);
1217 fnd_message.set_token('ID',l_choice_info.prtt_enrt_rslt_id);
1218 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1219 fnd_message.set_token('LER_ID',to_char(p_ler_id));
1220 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
1221 fnd_message.raise_error;
1222 --
1223 end if;
1224 --
1225 close c_prtt_result;
1226 --
1227 l_new_election := false;
1228 l_datetrack_mode := hr_api.g_update;
1229 l_choice_info.stage := 'N';
1230 --
1231 else
1232 --
1233 --Never goes here
1234 --
1235 l_new_election := true;
1236 l_datetrack_mode := hr_api.g_insert;
1237 l_choice_info.stage := 'Y';
1238 --
1239 end if;
1240 --
1241 -- Get benefit information
1242 --
1243 l_dflt_found:=false;
1244 hr_utility.set_location(l_proc,20);
1245 open c_bnft(l_choice_info.elig_per_elctbl_chc_id, l_prtt_result.bnft_ordr_num );
1246 fetch c_bnft into l_bnft;
1247 close c_bnft;
1248 --
1249 /*
1250 loop
1251 hr_utility.set_location(l_proc,30);
1252 --
1253 fetch c_bnft into l_bnft;
1254 --
1255 exit when c_bnft%notfound;
1256 hr_utility.set_location(l_proc,40);
1257 if l_bnft.dflt_flag='Y' then
1258 hr_utility.set_location(l_proc,50);
1259 l_dflt_bnft:=l_bnft;
1260 l_dflt_found:=true;
1261 end if;
1262 hr_utility.set_location(l_proc,60);
1263 end loop;
1264 hr_utility.set_location(l_proc,70);
1265 close c_bnft;
1266 */
1267 --
1268 hr_utility.set_location(l_proc||l_bnft.val,90);
1269 hr_utility.set_location('ENB ID'||l_bnft.enrt_bnft_id,90);
1270 --
1271 -- Get Rate information
1272 --
1273 for l_count in 1..10 loop
1274 --
1275 -- Initialise array to null
1276 --
1277 l_rt_table(l_count).enrt_rt_id := null;
1278 l_rt_table(l_count).dflt_val := null;
1279 l_rt_table(l_count).prtt_rt_val_id:=null;
1280 --
1281 end loop;
1282 --
1283 l_count:= 0;
1284 --
1285 for l_rec in c_rt(l_choice_info.elig_per_elctbl_chc_id,
1286 l_bnft.enrt_bnft_id) loop
1287 --
1288 l_count := l_count+1;
1289 l_rt_table(l_count).enrt_rt_id := l_rec.enrt_rt_id;
1290 l_rt_table(l_count).dflt_val := l_rec.default_value;
1291 l_rt_table(l_count).prtt_rt_val_id:=l_rec.prtt_rt_val_id;
1292 --
1293 end loop;
1294 --
1295 l_suspend_flag := 'N';
1296 --
1297 -- Call election information batch process
1298 --
1299 if l_choice_info.pgm_id is not null then
1300 open c_pgm_enrt_dt(l_choice_info.elig_per_elctbl_chc_id,
1301 l_choice_info.pgm_id);
1302 fetch c_pgm_enrt_dt into l_pgm_enrt_dt;
1303 -- if l_pgm_enrt_dt.enrt_perd_strt_dt < p_effective_date then
1304 -- 5741760: Create/Update enrollments as of first day of enrt_perd_strt_dt
1305 l_effective_dt := l_pgm_enrt_dt.enrt_perd_strt_dt;
1306 -- else
1307 --l_effective_dt := p_effective_date;
1308 --end if;
1309 close c_pgm_enrt_dt;
1310 elsif l_choice_info.pl_id is not null then
1311 open c_pl_enrt_dt(l_choice_info.elig_per_elctbl_chc_id,
1312 l_choice_info.pl_id);
1313 fetch c_pl_enrt_dt into l_pl_enrt_dt;
1314 --if l_pl_enrt_dt.enrt_perd_strt_dt < p_effective_date then
1315 -- 5741760: Create/Update enrollments as of first day of enrt_perd_strt_dt
1316 l_effective_dt := l_pl_enrt_dt.enrt_perd_strt_dt;
1317 --else
1318 -- l_effective_dt := p_effective_date;
1319 --end if;
1320 close c_pl_enrt_dt;
1321 end if;
1322 --
1323 hr_utility.set_location('cvg_mlt_cd='||l_bnft.cvg_mlt_cd,13);
1324 hr_utility.set_location('bnft_val='||l_bnft.val,13);
1325 hr_utility.set_location('rate_val='||l_rt_table(1).dflt_val,13);
1326 hr_utility.set_location('ann_rt_val='||l_rt_table(1).ann_rt_val,13);
1327 --
1328 if l_bnft.cvg_mlt_cd='SAAEAR' and
1329 l_rt_table(1).ann_rt_val is null then
1330 l_rt_table(1).ann_rt_val:=l_rt_table(1).dflt_val;
1331 end if;
1332 --
1333 l_prtt_enrt_rslt_id := nvl(l_bnft.prtt_enrt_rslt_id,l_choice_info.prtt_enrt_rslt_id);
1334 --bug#4299428 - benefit amount on enrt bnft passed if calculation is like
1335 -- compensation
1336 if instr(l_bnft.cvg_mlt_cd,'CL') <> 0 then
1337 --
1338 l_bnft_amt := l_bnft.val;
1339 --
1340 else
1341 --
1342 l_bnft_amt := NVL(l_prtt_result.bnft_amt,l_bnft.val) ;
1346 --Bug 6519487 when the enrollment is carried forward, we don't want to
1343 --
1344 end if;
1345 --
1347 --mark additional plans as default. Resetting all other plans back to
1348 --'N' so that user won't get into a situation of default one plan from
1349 --defaulting rules and another plan from carry forward functionality.
1350 --When carrying forward enrollment, remove defaults for all other
1351 -- plans/options in that plan type.
1352 --Need to be changed with API at a later date.
1353 --
1354 update ben_elig_per_elctbl_chc
1355 set dflt_flag = 'N'
1356 where pl_typ_id = l_choice_info.pl_typ_id
1357 and elig_per_elctbl_chc_id <> l_choice_info.elig_per_elctbl_chc_id
1358 and crntly_enrd_flag = 'N' -- Bug 7378468
1359 and dflt_flag = 'Y'
1360 and nvl(pgm_id,-1)= nvl(l_choice_info.pgm_id,-1)
1361 and per_in_ler_id = p_per_in_ler_id ;
1362
1363 --
1364 --END Bug 6519487
1365 --
1366 --Bug 4450214 Moved to the begining of the code
1367 -- ben_sspndd_enrollment.g_cfw_flag := 'Y';
1368 --
1369 ben_election_information.election_information
1370 (p_elig_per_elctbl_chc_id => l_choice_info.elig_per_elctbl_chc_id,
1371 p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id, -- l_choice_info.prtt_enrt_rslt_id,
1372 p_effective_date => l_effective_dt,
1373 p_enrt_mthd_cd => 'E', --We are making as Explicit [??]
1374 p_business_group_id => p_business_group_id,
1375 p_enrt_bnft_id => l_bnft.enrt_bnft_id,
1376 p_bnft_val => l_bnft_amt,
1377 p_enrt_rt_id1 => l_rt_table(1).enrt_rt_id,
1378 p_rt_val1 => l_rt_table(1).dflt_val,
1379 p_enrt_rt_id2 => l_rt_table(2).enrt_rt_id,
1380 p_rt_val2 => l_rt_table(2).dflt_val,
1381 p_enrt_rt_id3 => l_rt_table(3).enrt_rt_id,
1382 p_rt_val3 => l_rt_table(3).dflt_val,
1383 p_enrt_rt_id4 => l_rt_table(4).enrt_rt_id,
1384 p_rt_val4 => l_rt_table(4).dflt_val,
1385 p_enrt_rt_id5 => l_rt_table(5).enrt_rt_id,
1386 p_rt_val5 => l_rt_table(5).dflt_val,
1387 p_enrt_rt_id6 => l_rt_table(6).enrt_rt_id,
1388 p_rt_val6 => l_rt_table(6).dflt_val,
1389 p_enrt_rt_id7 => l_rt_table(7).enrt_rt_id,
1390 p_rt_val7 => l_rt_table(7).dflt_val,
1391 p_enrt_rt_id8 => l_rt_table(8).enrt_rt_id,
1392 p_rt_val8 => l_rt_table(8).dflt_val,
1393 p_enrt_rt_id9 => l_rt_table(9).enrt_rt_id,
1394 p_rt_val9 => l_rt_table(9).dflt_val,
1395 p_enrt_rt_id10 => l_rt_table(10).enrt_rt_id,
1396 p_rt_val10 => l_rt_table(10).dflt_val,
1397 p_datetrack_mode => l_datetrack_mode,
1398 p_suspend_flag => l_suspend_flag,
1399 p_prtt_enrt_interim_id => l_prtt_enrt_interim_id,
1400 p_prtt_rt_val_id1 => l_rt_table(1).prtt_rt_val_id,
1401 p_prtt_rt_val_id2 => l_rt_table(2).prtt_rt_val_id,
1402 p_prtt_rt_val_id3 => l_rt_table(3).prtt_rt_val_id,
1403 p_prtt_rt_val_id4 => l_rt_table(4).prtt_rt_val_id,
1404 p_prtt_rt_val_id5 => l_rt_table(5).prtt_rt_val_id,
1405 p_prtt_rt_val_id6 => l_rt_table(6).prtt_rt_val_id,
1406 p_prtt_rt_val_id7 => l_rt_table(7).prtt_rt_val_id,
1407 p_prtt_rt_val_id8 => l_rt_table(8).prtt_rt_val_id,
1408 p_prtt_rt_val_id9 => l_rt_table(9).prtt_rt_val_id,
1409 p_prtt_rt_val_id10 => l_rt_table(10).prtt_rt_val_id,
1410 p_ann_rt_val1 => l_rt_table(1).ann_rt_val,
1411 p_ann_rt_val2 => l_rt_table(2).ann_rt_val,
1412 p_ann_rt_val3 => l_rt_table(3).ann_rt_val,
1413 p_ann_rt_val4 => l_rt_table(4).ann_rt_val,
1414 p_ann_rt_val5 => l_rt_table(5).ann_rt_val,
1415 p_ann_rt_val6 => l_rt_table(6).ann_rt_val,
1416 p_ann_rt_val7 => l_rt_table(7).ann_rt_val,
1417 p_ann_rt_val8 => l_rt_table(8).ann_rt_val,
1418 p_ann_rt_val9 => l_rt_table(9).ann_rt_val,
1419 p_ann_rt_val10 => l_rt_table(10).ann_rt_val,
1420 -- 3517682 start
1421 p_pen_attribute_category => l_prtt_result.pen_attribute_category ,
1422 p_pen_attribute1 => l_prtt_result.pen_attribute1 ,
1423 p_pen_attribute2 => l_prtt_result.pen_attribute2 ,
1424 p_pen_attribute3 => l_prtt_result.pen_attribute3 ,
1425 p_pen_attribute4 => l_prtt_result.pen_attribute4 ,
1426 p_pen_attribute5 => l_prtt_result.pen_attribute5 ,
1427 p_pen_attribute6 => l_prtt_result.pen_attribute6 ,
1428 p_pen_attribute7 => l_prtt_result.pen_attribute7 ,
1429 p_pen_attribute8 => l_prtt_result.pen_attribute8 ,
1430 p_pen_attribute9 => l_prtt_result.pen_attribute9 ,
1431 p_pen_attribute10 => l_prtt_result.pen_attribute10 ,
1432 p_pen_attribute11 => l_prtt_result.pen_attribute11 ,
1433 p_pen_attribute12 => l_prtt_result.pen_attribute12 ,
1434 p_pen_attribute13 => l_prtt_result.pen_attribute13 ,
1438 p_pen_attribute17 => l_prtt_result.pen_attribute17 ,
1435 p_pen_attribute14 => l_prtt_result.pen_attribute14 ,
1436 p_pen_attribute15 => l_prtt_result.pen_attribute15 ,
1437 p_pen_attribute16 => l_prtt_result.pen_attribute16 ,
1439 p_pen_attribute18 => l_prtt_result.pen_attribute18 ,
1440 p_pen_attribute19 => l_prtt_result.pen_attribute19 ,
1441 p_pen_attribute20 => l_prtt_result.pen_attribute20 ,
1442 p_pen_attribute21 => l_prtt_result.pen_attribute21 ,
1443 p_pen_attribute22 => l_prtt_result.pen_attribute22,
1444 p_pen_attribute23 => l_prtt_result.pen_attribute23,
1445 p_pen_attribute24 => l_prtt_result.pen_attribute24,
1446 p_pen_attribute25 => l_prtt_result.pen_attribute25,
1447 p_pen_attribute26 => l_prtt_result.pen_attribute26,
1448 p_pen_attribute27 => l_prtt_result.pen_attribute27,
1449 p_pen_attribute28 => l_prtt_result.pen_attribute28,
1450 p_pen_attribute29 => l_prtt_result.pen_attribute29,
1451 p_pen_attribute30 => l_prtt_result.pen_attribute30,
1452 -- 3517682 end
1453 p_object_version_number => l_object_version_number,
1454 p_effective_start_date => l_effective_start_date,
1455 p_effective_end_date => l_effective_end_date,
1456 p_dpnt_actn_warning => l_dpnt_actn_warning,
1457 p_bnf_actn_warning => l_bnf_actn_warning,
1458 p_ctfn_actn_warning => l_ctfn_actn_warning);
1459 --
1460 -- ben_sspndd_enrollment.g_cfw_flag := 'N';
1461 --
1462 l_choice_info.suspended := l_suspend_flag;
1463 --
1464 if l_choice_info.suspended = 'Y' then
1465 --
1466 l_person_susp := 'Y';
1467 l_choice_info.stage := 'S';
1468 --
1469 end if;
1470 --
1471 --
1472 l_pen_count := l_pen_count + 1;
1473 l_crd_fwd_pen_id(l_pen_count) := l_prtt_enrt_rslt_id;
1474 --
1475 --
1476
1477 --
1478 -- update the default flag to N on the interim epe
1479 --
1480 /* Bug 5474065 : Updating the DFLT_FLAG prevents interim enrollment when interim
1481 code has Current - Default
1482 update ben_elig_per_elctbl_chc
1483 set dflt_flag = 'N'
1484 where dflt_flag = 'Y'
1485 and per_in_ler_id = l_choice_info.per_in_ler_id
1486 and pl_id = l_choice_info.pl_id
1487 and oipl_id <> l_choice_info.oipl_id
1488 and nvl(pgm_id,-1) = nvl(l_choice_info.pgm_id,-1)
1489 and elig_per_elctbl_chc_id <> l_choice_info.elig_per_elctbl_chc_id;
1490 */
1491 /* It is not required to do carryforward since it is handled in beneadeb and benauten
1492 open c_prtt_result(l_prtt_enrt_rslt_id, --ML l_choice_info.prtt_enrt_rslt_id,
1493 l_effective_dt);
1494 --
1495 fetch c_prtt_result into l_prtt_result;
1496 if c_prtt_result%notfound then
1497 --
1498 close c_prtt_result;
1499 fnd_message.set_name('BEN','BEN_91711_ENRT_RSLT_NOT_FOUND');
1500 fnd_message.set_token('PROC',l_proc);
1501 fnd_message.set_token('ID',l_prtt_enrt_rslt_id); -- l_choice_info.prtt_enrt_rslt_id);
1502 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1503 fnd_message.set_token('LER_ID',to_char(p_ler_id));
1504 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
1505 fnd_message.raise_error;
1506 --
1507 else
1508 --
1509 l_choice_info.enrt_cvg_strt_dt := l_prtt_result.enrt_cvg_strt_dt;
1510 l_choice_info.enrt_cvg_end_dt := l_prtt_result.enrt_cvg_thru_dt;
1511 --
1512 -- after the enhncemnt # 2685018 cryfwd_elig_dpnt_cd value is concated with
1513 -- result id from where the dpnt carry forwarded , this will seprate the code from
1514 --- result id
1515
1516 l_prev_prtt_enrt_rslt_id := null; -- Reintializing the previous enrt result id
1517 l_cryfwd_elig_dpnt_cd := l_choice_info.cryfwd_elig_dpnt_cd ;
1518 l_prev_rslt_id_at := instr(l_cryfwd_elig_dpnt_cd, '^') ;
1519 --- if the result id concated with the code, then the caht '^' must be aprt of the
1520 --- the code
1521
1522 if l_prev_rslt_id_at > 0 then
1523 --- if the to_number errors , catch the exception
1524 Begin
1525 l_prev_prtt_enrt_rslt_id := to_number(substr(l_cryfwd_elig_dpnt_cd,l_prev_rslt_id_at+1) );
1526 Exception
1527 when value_error then
1528 l_prev_prtt_enrt_rslt_id := null;
1529 End ;
1530
1531 l_cryfwd_elig_dpnt_cd := substr(l_cryfwd_elig_dpnt_cd,1,l_prev_rslt_id_at-1) ;
1532 ---
1533 end if ;
1534
1535
1536 hr_utility.set_location('l_cryfwd_elig_dpnt_cd '||l_cryfwd_elig_dpnt_cd,744);
1537 hr_utility.set_location('l_prev_prtt_enrt_rslt_id '||l_prev_prtt_enrt_rslt_id,744);
1538
1539 if l_datetrack_mode = hr_api.g_insert and l_cryfwd_elig_dpnt_cd = 'CFRRWP' then
1540
1541 hr_utility.set_location('cvg strt cd: '||l_choice_info.dpnt_cvg_strt_dt_cd,10);
1545 -- this is fixed sending l_effective_dt # 3042033
1542 -- p_effective_date is now changed to l_effective_dt , when ever LE reprocessed
1543 -- result created as on effective date and automeatic enrollment called with
1544 -- lE_ocurd_Dt as affective date so there is no result as on effective date (le_ocrd_dt)
1546
1547 reinstate_dpnt(p_pgm_id =>l_choice_info.pgm_id,
1548 p_pl_id => l_choice_info.pl_id,
1549 p_oipl_id => l_choice_info.oipl_id,
1550 p_business_group_id => p_business_group_id,
1551 p_person_id => p_person_id,
1552 p_per_in_ler_id => l_per_in_ler_id,
1553 p_elig_per_elctbl_chc_id => l_choice_info.elig_per_elctbl_chc_id,
1554 p_dpnt_cvg_strt_dt_cd => l_choice_info.dpnt_cvg_strt_dt_cd,
1555 p_dpnt_cvg_strt_dt_rl => l_choice_info.dpnt_cvg_strt_dt_rl,
1556 p_enrt_cvg_strt_dt => l_choice_info.enrt_cvg_strt_dt,
1557 p_effective_date => l_effective_dt,
1558 p_prev_prtt_enrt_rslt_id => l_prev_prtt_enrt_rslt_id
1559 );
1560 end if;
1561 end if;
1562 --
1563 close c_prtt_result;
1564 --
1565 */
1566 -- Do Post enrollment - Writes elecment entries, calls close enrollment
1567 --
1568 if l_choice_info.pgm_id is null then
1569 --
1570 -- Invoke post result process
1571 --
1572 ben_proc_common_enrt_rslt.process_post_results
1573 (p_person_id => p_person_id,
1574 p_enrt_mthd_cd => 'E',
1575 p_effective_date => l_effective_dt,
1576 p_business_group_id => p_business_group_id,
1577 p_per_in_ler_id => l_per_in_ler_id);
1578 --
1579 /*
1580 ben_proc_common_enrt_rslt.process_post_enrollment
1581 (p_per_in_ler_id => l_per_in_ler_id,
1582 p_pgm_id => l_choice_info.pgm_id,
1583 p_pl_id => l_choice_info.pl_id,
1584 p_cls_enrt_flag => l_cls_enrt_flag,
1585 p_enrt_mthd_cd => 'E',
1586 p_proc_cd => null,
1587 p_person_id => p_person_id,
1588 p_business_group_id => p_business_group_id,
1589 p_effective_date => p_effective_date);
1590 */
1591 --
1592 end if;
1593 --
1594 -- Do multi row edit stuff
1595 --
1596 if l_prev_pgm_id = -99999 then
1597 --
1598 l_prev_pgm_id := l_choice_info.pgm_id;
1599 l_prev_eff_dt := l_effective_dt;
1600 --
1601 elsif nvl(l_prev_pgm_id,-1) <> nvl(l_choice_info.pgm_id,-1) then
1602
1603 -- call multi-row edit if per-in-ler has no default nor explict
1604 -- choices available.
1605 /*
1606 if l_cls_enrt_flag then
1607 ben_prtt_enrt_result_api.multi_rows_edit
1608 (p_person_id => p_person_id,
1609 p_effective_date => l_effective_dt,
1610 p_business_group_id => p_business_group_id,
1611 p_pgm_id => l_prev_pgm_id);
1612 end if;
1613 */
1614 --
1615 -- Invoke process post enrollment
1616 --
1617 if l_prev_pgm_id is not null then
1618 --
1619 -- Invoke post result process
1620 --
1621 ben_proc_common_enrt_rslt.process_post_results
1622 (p_person_id => p_person_id,
1623 p_enrt_mthd_cd => 'E',
1624 p_effective_date => l_effective_dt,
1625 p_business_group_id => p_business_group_id,
1626 p_per_in_ler_id => l_per_in_ler_id);
1627 /*
1628 ben_proc_common_enrt_rslt.process_post_enrollment
1629 (p_per_in_ler_id => l_per_in_ler_id,
1630 p_pgm_id => l_prev_pgm_id,
1631 p_pl_id => null,
1632 p_enrt_mthd_cd => 'E',
1633 p_cls_enrt_flag => l_cls_enrt_flag,
1634 p_proc_cd => null,
1635 p_person_id => p_person_id,
1636 p_business_group_id => p_business_group_id,
1637 p_effective_date => p_effective_date);
1638 */
1639 --
1640 end if;
1641 --
1642 l_prev_pgm_id := l_choice_info.pgm_id;
1643 l_prev_eff_dt := l_effective_dt;
1644 --
1645 end if;
1646 --
1647 end loop;
1648 --
1649 close c_choice_info;
1650 --
1651 -- Check if last multi edit passed
1652 --
1653 if l_prev_pgm_id <> -99999 then
1654 --
1655 /*
1656 if l_cls_enrt_flag then
1657 ben_prtt_enrt_result_api.multi_rows_edit
1658 (p_person_id => p_person_id,
1659 p_effective_date => l_prev_eff_dt,
1660 p_business_group_id => p_business_group_id,
1664 --
1661 p_pgm_id => l_prev_pgm_id);
1662 end if;
1663 */
1665 -- Do post enrollment
1666 --
1667 if l_prev_pgm_id is not null then
1668 --
1669 --
1670 -- Invoke post result process
1671 --
1672 ben_proc_common_enrt_rslt.process_post_results
1673 (p_person_id => p_person_id,
1674 p_enrt_mthd_cd => 'E',
1675 p_effective_date => l_effective_dt,
1676 p_business_group_id => p_business_group_id,
1677 p_per_in_ler_id => l_per_in_ler_id);
1678 --
1679 /*
1680 ben_proc_common_enrt_rslt.process_post_enrollment
1681 (p_per_in_ler_id => l_per_in_ler_id,
1682 p_pgm_id => l_prev_pgm_id,
1683 p_pl_id => null,
1684 p_enrt_mthd_cd => 'E',
1685 p_cls_enrt_flag => l_cls_enrt_flag,
1686 p_proc_cd => null,
1687 p_person_id => p_person_id,
1688 p_business_group_id => p_business_group_id,
1689 p_effective_date => p_effective_date);
1690 */
1691 --
1692 end if;
1693 --
1694 end if;
1695 --
1696 -- 6057157 : Carry Forward Certificatons which were provided on a future
1697 -- date to the current pil.
1698 --
1699 if (l_crd_fwd_pen_id.COUNT > 0) then
1700 for i in l_crd_fwd_pen_id.FIRST..l_crd_fwd_pen_id.LAST loop
1701 --
1702 hr_utility.set_location('CRD FWD CERT l_crd_fwd_pen_id(i) ' || l_crd_fwd_pen_id(i) , 10);
1703 --
1704 reinstate_prvdd_ctfn_items(p_prtt_enrt_rslt_id => l_crd_fwd_pen_id(i)
1705 ,p_per_in_ler_id => p_per_in_ler_id
1706 ,p_business_group_id => p_business_group_id
1707 ,p_effective_date => p_effective_date);
1708
1709 end loop;
1710 end if;
1711 --
1712 ben_sspndd_enrollment.g_cfw_flag := 'N';
1713 hr_utility.set_location('Leaving: '||l_proc,10);
1714 --
1715 exception
1716 when others then
1717 ben_sspndd_enrollment.g_cfw_flag := 'N';
1718 raise;
1719
1720 end process_person;
1721 --
1722 procedure unprocess_susp_enrt_past_pil(p_prtt_enrt_rslt_id in number,
1723 p_per_in_ler_id in number,
1724 p_business_group_id in number) is
1725 --
1726 cursor c_get_past_pil (p_per_in_ler_id number) is
1727 select max(pea.per_in_ler_id)
1728 from ben_prtt_enrt_actn_f pea,
1729 ben_per_in_ler pil
1730 where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1731 and pea.per_in_ler_id <> p_per_in_ler_id
1732 and pea.business_group_id = p_business_group_id
1733 and pea.per_in_ler_id = pil.per_in_ler_id
1734 and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD');
1735 --
1736 cursor c_actn_item_for_past_pil (p_per_in_ler_id number) is
1737 select prtt_enrt_actn_id, effective_start_date, object_version_number
1738 from ben_prtt_enrt_actn_f
1739 where per_in_ler_id = p_per_in_ler_id
1740 and prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1741 and effective_end_date < hr_api.g_eot
1742 and business_group_id = p_business_group_id
1743 order by prtt_enrt_actn_id, effective_start_date; -- 5394656
1744 l_actn_item c_actn_item_for_past_pil%rowtype;
1745 --
1746 cursor c_enrt_ctfn_for_past_pil (p_prtt_enrt_actn_id number) is
1747 select prtt_enrt_ctfn_prvdd_id, effective_start_date, object_version_number
1748 from ben_prtt_enrt_ctfn_prvdd_f
1749 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1750 and prtt_enrt_actn_id = p_prtt_enrt_actn_id
1751 and effective_end_date < hr_api.g_eot
1752 and business_group_id = p_business_group_id
1753 order by prtt_enrt_ctfn_prvdd_id, effective_start_date; -- 5394656
1754 l_enrt_ctfn c_enrt_ctfn_for_past_pil%rowtype;
1755 --
1756 cursor c_check_prem_active is
1757 select 1
1758 from ben_prtt_prem_f
1759 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1760 and effective_end_date = hr_api.g_eot;
1761 l_check_prem_active c_check_prem_active%rowtype;
1762 --
1763 cursor c_ended_prem_details is
1764 select ppm.prtt_prem_id, ppm.effective_start_date, ppm.object_version_number
1765 from ben_prtt_prem_f ppm
1766 where ppm.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1767 and ppm.effective_end_date <> hr_api.g_eot
1768 and not exists (select 1
1769 from ben_prtt_prem_f ppm2
1770 where ppm2.prtt_prem_id = ppm.prtt_prem_id
1771 and ppm2.effective_end_date > ppm.effective_end_date)
1772 order by effective_start_date desc;
1773 l_ended_prem_details c_ended_prem_details%rowtype;
1774 --
1775 l_proc varchar2(80) := g_package||'.unprocess_susp_enrt_past_pil';
1776 l_per_in_ler_id number;
1777 l_prtt_enrt_actn_id number;
1778 l_effective_start_date date;
1779 l_effective_end_date date;
1780 l_object_version_number number;
1781 l_ppe_object_version_number number;
1782 --
1783 l_prev_prtt_enrt_actn_id number;
1784 l_prev_prtt_enrt_ctfn_prvdd_id number;
1785 --
1786 begin
1787 --
1791 --
1788 hr_utility.set_location ('Entering ' || l_proc , 1230);
1789 l_per_in_ler_id := p_per_in_ler_id;
1790 l_prev_prtt_enrt_actn_id := -1;
1792 for l_actn_item in c_actn_item_for_past_pil (l_per_in_ler_id) loop
1793 --
1794 -- 5394656: Since 'FUTURE_CHANGE' mode deletes all future records
1795 -- we need to run the delete_api only once for every prtt_enrt_actn_id
1796 --
1797 l_prtt_enrt_actn_id := l_actn_item.prtt_enrt_actn_id;
1798 --
1799 if (l_prev_prtt_enrt_actn_id <> l_prtt_enrt_actn_id) then
1800 --
1801 l_prev_prtt_enrt_actn_id := l_prtt_enrt_actn_id;
1802 --
1803 -- Un-enddate action item record
1804 --
1805 l_object_version_number := l_actn_item.object_version_number;
1806 --
1807 ben_pea_del.del(
1808 p_prtt_enrt_actn_id => l_actn_item.prtt_enrt_actn_id,
1809 p_effective_start_date => l_effective_start_date,
1810 p_effective_end_date => l_effective_end_date,
1811 p_object_version_number => l_object_version_number,
1812 p_effective_date => l_actn_item.effective_start_date,
1813 p_datetrack_mode => hr_api.g_future_change);
1814 --
1815 l_prev_prtt_enrt_ctfn_prvdd_id := -1;
1816 --
1817 -- Un-enddate enrollment certification record(s)
1818 --
1819 for l_enrt_ctfn in c_enrt_ctfn_for_past_pil (l_prtt_enrt_actn_id) loop
1820 --
1821 l_object_version_number := l_enrt_ctfn.object_version_number;
1822 --
1823 -- 5394656: Since 'FUTURE_CHANGE' mode deletes all future records
1824 -- we need to run the delete_api only once for every prtt_enrt_ctfn_prvdd_id
1825 --
1826 if (l_prev_prtt_enrt_ctfn_prvdd_id <> l_enrt_ctfn.prtt_enrt_ctfn_prvdd_id) then
1827 --
1828 l_prev_prtt_enrt_ctfn_prvdd_id := l_enrt_ctfn.prtt_enrt_ctfn_prvdd_id;
1829 --
1830 ben_pcs_del.del(
1831 p_prtt_enrt_ctfn_prvdd_id => l_enrt_ctfn.prtt_enrt_ctfn_prvdd_id,
1832 p_effective_start_date => l_effective_start_date,
1833 p_effective_end_date => l_effective_end_date,
1834 p_object_version_number => l_object_version_number,
1835 p_effective_date => l_enrt_ctfn.effective_start_date,
1836 p_datetrack_mode => hr_api.g_future_change);
1837 end if;
1838 --
1839 end loop;
1840 end if;
1841 --
1842 end loop;
1843 --
1844 -- Process premium, if ended (will be needed for ineligible/due date past cases)
1845 --
1846 open c_check_prem_active;
1847 fetch c_check_prem_active into l_check_prem_active;
1848 if c_check_prem_active%notfound then
1849 --
1850 -- Unend most recent premiums
1851 --
1852 for l_ended_prem_details in c_ended_prem_details loop
1853 l_ppe_object_version_number := l_ended_prem_details.object_version_number;
1854 ben_ppe_del.del(
1855 p_prtt_prem_id => l_ended_prem_details.prtt_prem_id,
1856 p_effective_start_date => l_effective_start_date,
1857 p_effective_end_date => l_effective_end_date,
1858 p_object_version_number => l_ppe_object_version_number,
1859 p_effective_date => l_ended_prem_details.effective_start_date,
1860 p_datetrack_mode => hr_api.g_future_change);
1861 end loop;
1862 --
1863 end if;
1864 close c_check_prem_active;
1865 --
1866 hr_utility.set_location ('Leaving ' || l_proc, 1230);
1867 end unprocess_susp_enrt_past_pil;
1868 procedure carry_farward_results(
1869 p_person_id in number,
1870 p_per_in_ler_id in number,
1871 p_ler_id in number,
1872 p_business_group_id in number,
1873 p_mode in varchar2,
1874 p_effective_date in date) is
1875 --
1876 l_proc varchar2(80) := g_package||'carry_farward_results';
1877 l_person_id number;
1878 --
1879 cursor c_pen_sus is
1880 select pen.prtt_enrt_rslt_id,
1881 pen.per_in_ler_id
1882 from ben_prtt_enrt_rslt_f pen,
1883 ben_per_in_ler pil
1884 where pen.sspndd_flag = 'Y'
1885 and pen.per_in_ler_id <> pil.per_in_ler_id
1886 and pil.per_in_ler_id = p_per_in_ler_id
1887 and pen.person_id = pil.person_id
1888 and pen.effective_end_date = hr_api.g_eot
1889 and pen.enrt_cvg_thru_dt = hr_api.g_eot;
1890 l_pen_sus c_pen_sus%rowtype;
1891 --
1892 begin
1893 --
1894 hr_utility.set_location('Entering: '||l_proc,10);
1895 hr_utility.set_location('p_person_id'||p_person_id,10);
1896 hr_utility.set_location('p_per_in_ler_id'||p_per_in_ler_id,10);
1897 hr_utility.set_location('p_business_group_id'||p_business_group_id,10);
1898 hr_utility.set_location('p_mode'||p_mode,10);
1899 hr_utility.set_location('p_effective_date'||p_effective_date,10);
1900 --
1901 if p_mode not in ('L','C', 'M') then
1902 --
1903 -- Wrong mode my friend!
1904 --
1905 return;
1906 --
1907 end if;
1908 --
1909 process_person(p_person_id => p_person_id,
1910 p_business_group_id => p_business_group_id,
1911 p_per_in_ler_id => p_per_in_ler_id,
1912 p_ler_id => p_ler_id,
1913 p_effective_date => p_effective_date);
1914 --
1915 hr_utility.set_location('Leaving: '||l_proc,10);
1916 --
1917 --Reopen the closed action items of the enrollment results for which there are no
1918 --electable choices exists in this life event.
1919 open c_pen_sus;
1920 loop
1921 fetch c_pen_sus into l_pen_sus;
1922 if c_pen_sus%notfound then
1923 exit;
1924 end if;
1925
1926 unprocess_susp_enrt_past_pil
1927 (l_pen_sus.prtt_enrt_rslt_id,
1928 l_pen_sus.per_in_ler_id,
1929 p_business_group_id);
1930
1931 end loop;
1932 close c_pen_sus;
1933 --
1934 end carry_farward_results;
1935 end ben_carry_forward_items;