[Home] [Help]
PACKAGE BODY: APPS.BEN_REOPEN_ENDED_RESULTS
Source
1 Package Body ben_reopen_ended_results as
2 /* $Header: benreopn.pkb 120.13 2012/01/19 06:35:24 rpahune noship $ */
3 ---
4 -- Package Variables
5 --
6 g_package varchar2(300) := 'ben_reopen_ended_results.';
7 g_debug boolean := hr_utility.debug_enabled;
8 --
9 -- bug 6127624 added
10 FUNCTION check_prev_eligible (
11 p_person_id IN NUMBER,
12 p_pgm_id IN NUMBER,
13 p_pl_id IN NUMBER,
14 p_oipl_id IN NUMBER,
15 p_effective_date IN DATE,
16 p_business_group_id IN NUMBER,
17 p_per_in_ler_id IN NUMBER
18 )
19 RETURN BOOLEAN
20 IS
21 CURSOR csr_opt
22 IS
23 SELECT opt_id
24 FROM ben_oipl_f
25 WHERE oipl_id = p_oipl_id
26 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
27
28 l_opt_id ben_opt_f.opt_id%TYPE;
29
30 ---
31 ---
32 CURSOR csr_prev_opt_elig_check (
33 c_person_id IN NUMBER,
34 c_effective_date IN DATE,
35 c_pl_id IN NUMBER,
36 c_opt_id IN NUMBER,
37 c_pgm_id IN NUMBER
38 )
39 IS
40 SELECT epo.elig_flag
41 FROM ben_elig_per_opt_f epo, ben_per_in_ler pil, ben_elig_per_f pep
42 WHERE pep.person_id = c_person_id
43 AND pep.pl_id = c_pl_id
44 AND epo.opt_id = c_opt_id
45 AND pep.elig_per_id = epo.elig_per_id
46 AND NVL(pep.pgm_id,-1) = NVL(c_pgm_id,-1)
47 AND epo.effective_start_date BETWEEN pep.effective_start_date
48 AND pep.effective_end_date
49 AND epo.effective_start_date < c_effective_date
50 AND epo.per_in_ler_id <> p_per_in_ler_id
51 AND pil.per_in_ler_id = epo.per_in_ler_id
52 AND pil.business_group_id = epo.business_group_id
53 AND pil.per_in_ler_stat_cd IN ('STRTD', 'PROCD')
54 ORDER BY epo.effective_start_date DESC;
55
56 rec_prev_opt_elig_check csr_prev_opt_elig_check%ROWTYPE;
57
58 ---
59 ---
60 CURSOR csr_prev_elig_check (
61 c_person_id IN NUMBER,
62 c_pgm_id IN NUMBER,
63 c_pl_id IN NUMBER,
64 c_ptip_id IN NUMBER,
65 c_effective_date IN DATE
66 )
67 IS
68 SELECT pep.elig_flag
69 FROM ben_elig_per_f pep, ben_per_in_ler pil
70 WHERE pep.person_id = c_person_id
71 AND NVL (pep.pgm_id, -1) = NVL (c_pgm_id, -1)
72 -- bug 5947036/ bug 6379215
73 AND pep.pl_id = c_pl_id
74 -- AND NVL (pep.pl_id, -1) = c_pl_id
75 --AND pep.plip_id IS NULL
76 --AND NVL (pep.ptip_id, -1) = nvl(c_ptip_id,-1)
77 AND pep.effective_start_date < c_effective_date
78 AND pep.per_in_ler_id <> p_per_in_ler_id
79 AND pil.per_in_ler_id = pep.per_in_ler_id
80 AND pil.business_group_id = pep.business_group_id
81 AND pil.per_in_ler_stat_cd IN ('STRTD', 'PROCD')
82 ORDER BY pep.effective_start_date DESC;
83
84 rec_prev_elig_check csr_prev_elig_check%ROWTYPE;
85 ---
86 l_return BOOLEAN := FALSE;
87 l_epo_row ben_derive_part_and_rate_facts.g_cache_structure;
88 l_pep_row ben_derive_part_and_rate_facts.g_cache_structure;
89 l_proc VARCHAR2 (100)
90 := g_package || 'check_prev_eligible';
91 BEGIN
92 IF g_debug
93 THEN
94 hr_utility.set_location ('Entering ' || l_proc, 121);
95 hr_utility.set_location ('p_person_id ' || p_person_id, 121);
96 hr_utility.set_location ('p_pgm_id ' || p_pgm_id, 121);
97 hr_utility.set_location ('p_pl_id ' || p_pl_id, 121);
98 hr_utility.set_location ('p_oipl_id ' || p_oipl_id, 121);
99 hr_utility.set_location ('p_business_group_id ' || p_business_group_id,
100 121
101 );
102 hr_utility.set_location ( 'p_effective_date '
103 || TO_CHAR (p_effective_date, 'YYYY/MM/DD'),
104 121
105 );
106 hr_utility.set_location ('p_per_in_ler_id ' || p_per_in_ler_id, 121);
107 END IF;
108
109 IF p_oipl_id IS NOT NULL
110 THEN
111 OPEN csr_opt;
112
113 FETCH csr_opt
114 INTO l_opt_id;
115
116 CLOSE csr_opt;
117
118 OPEN csr_prev_opt_elig_check (p_person_id,
119 p_effective_date,
120 p_pl_id,
121 l_opt_id,
122 p_pgm_id
123 );
124
125 FETCH csr_prev_opt_elig_check
126 INTO rec_prev_opt_elig_check;
127
128 CLOSE csr_prev_opt_elig_check;
129
130 IF rec_prev_opt_elig_check.elig_flag = 'Y'
131 THEN
132 IF g_debug
133 THEN
134 hr_utility.set_location ('oipl elig ', 121);
135 END IF;
136
137 l_return := TRUE;
138 END IF;
139 ELSE
140 OPEN csr_prev_elig_check (
141 p_person_id,
142 p_pgm_id,
143 p_pl_id,
144 null,
145 p_effective_date
146 );
147 FETCH csr_prev_elig_check
148 INTO rec_prev_elig_check;
149
150 CLOSE csr_prev_elig_check;
151
152 IF rec_prev_opt_elig_check.elig_flag = 'Y'
153 THEN
154 IF g_debug
155 THEN
156 hr_utility.set_location ('pl elig', 121);
157 END IF;
158
159 l_return := TRUE;
160 END IF;
161 END IF;
162
163 hr_utility.set_location ('Leaving ' || l_proc, 121);
164 RETURN l_return;
165 EXCEPTION
166 WHEN OTHERS
167 THEN
168 hr_utility.set_location (SQLERRM, -121);
169 hr_utility.set_location ('Leaving ' || l_proc, -121);
170 RAISE;
171 END check_prev_eligible;
172
173
174
175
176 PROCEDURE reopen_routine (p_per_in_ler_id IN number,
177 p_business_group_id IN number,
178 p_lf_evt_ocrd_dt in date,
179 p_person_id in number,
180 p_effective_date in date) is
181 --
182
183 CURSOR csr_electable_epes
184 IS
185 SELECT elig_per_elctbl_chc_id,
186 pl_id,
187 pgm_id,
188 oipl_id
189 FROM ben_elig_per_elctbl_chc
190 WHERE per_in_ler_id = p_per_in_ler_id AND elctbl_flag = 'Y';
191
192 l_rec_electable_epes csr_electable_epes%rowtype;
193 --
194 cursor c_ended_result (p_per_in_ler_id number,
195 p_business_group_id number) is
196 select pen.*
197 from ben_prtt_enrt_rslt_f pen,
198 ben_elig_per_elctbl_chc epe
199 where pen.prtt_enrt_rslt_stat_cd is null
200 and pen.effective_end_date = hr_api.g_eot
201 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
202 and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
203 and pen.per_in_ler_id <> p_per_in_ler_id -- 5365585 . result ended in the same life event will not be picked up .
204 and epe.per_in_ler_id = p_per_in_ler_id
205 and epe.CRNTLY_ENRD_FLAG = 'Y'
206 -- and epe.ELCTBL_FLAG = 'Y'
207 and epe.BUSINESS_GROUP_ID = p_business_group_id
208 --Bug 5102337. we need to exclude interim enrollments being selected here.
209 and not exists ( select 'x'
210 from ben_prtt_enrt_rslt_f susp
211 where susp.RPLCS_SSPNDD_RSLT_ID = pen.prtt_enrt_rslt_id
212 and susp.effective_end_date <> hr_api.g_eot
213 and susp.prtt_enrt_rslt_stat_cd is null
214 and susp.enrt_cvg_thru_dt = hr_api.g_eot ) ;
215 -- bug#5345189
216 cursor c_future_results (p_person_id in number,
217 p_enrt_cvg_thru_dt in date,
218 p_pgm_id in number,
219 p_pl_id in number,
220 p_oipl_id in number,
221 p_pl_typ_id in number,
222 p_business_group_id in number) is
223
224 select pen.*
225 from ben_prtt_enrt_rslt_f pen,
226 ben_per_in_ler pil
227 where pen.person_id = p_person_id
228 and pen.effective_end_date = hr_api.g_eot
229 and pen.business_group_id = p_business_group_id
230 and pil.business_group_id = p_business_group_id
231 and pen.enrt_cvg_strt_dt > p_enrt_cvg_thru_dt
232 and pen.per_in_ler_id = pil.per_in_ler_id
233 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
234 and nvl(pen.pgm_id,-1) = nvl(p_pgm_id,-1)
235 and pen.pl_typ_id = p_pl_typ_id -- 5376652
236 --and nvl(pen.oipl_id,-1) = nvl(p_oipl_id,-1) -- 5376652
237 and pen.prtt_enrt_rslt_stat_cd is null;
238 --
239 -- bug#8968118
240 --
241 cursor c_future_results2 (p_person_id in number,
242 p_enrt_cvg_thru_dt in date,
243 p_pl_typ_id in number,
244 p_business_group_id in number) is
245
246 select pen.*
247 from ben_prtt_enrt_rslt_f pen,
248 ben_per_in_ler pil
249 where pen.person_id = p_person_id
250 and pen.effective_end_date = hr_api.g_eot
251 and pen.business_group_id = p_business_group_id
252 and pil.business_group_id = p_business_group_id
253 and pen.enrt_cvg_strt_dt > p_enrt_cvg_thru_dt
254 and pen.per_in_ler_id = pil.per_in_ler_id
255 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
256 and pen.pl_typ_id = p_pl_typ_id
257 and pen.prtt_enrt_rslt_stat_cd is null
258 and exists (select null
259 from ben_elig_per_elctbl_chc epe
260 where nvl(epe.pgm_id,-1) = nvl(pen.pgm_id,-1)
261 and epe.per_in_ler_id = p_per_in_ler_id
262 and elctbl_flag = 'Y');
263 -- end 8968118
264 --
265 cursor c_pl_typ (p_per_in_ler_id number) is
266 select epe.pgm_id,
267 epe.pl_typ_id
268 from ben_elig_per_elctbl_chc epe
269 where epe.per_in_ler_id = p_per_in_ler_id
270 and epe.pgm_id is not null
271 and epe.business_group_id = p_business_group_id
272 group by pgm_id, pl_typ_id;
273 --
274 cursor c_ended_prv (p_per_in_ler_id number,
275 p_person_id number,
276 p_pgm_id number) is
277 select prv.*
278 from ben_prtt_rt_val prv
279 where prv.prtt_enrt_rslt_id in
280 (select pen.prtt_enrt_rslt_id
281 from ben_prtt_enrt_rslt_f pen
282 where pen.effective_end_date = hr_api.g_eot
283 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
284 and pen.person_id = p_person_id
285 and pen.pgm_id <> p_pgm_id
286 and pen.enrt_cvg_thru_dt < p_lf_evt_ocrd_dt
287 and pen.prtt_enrt_rslt_stat_cd is null
288 and pen.per_in_ler_id = p_per_in_ler_id
289 and pen.business_group_id = p_business_group_id
290 and not exists (select null from ben_elig_per_elctbl_chc
291 where per_in_ler_id = p_per_in_ler_id
292 and pgm_id = pen.pgm_id))
293 and prv.prtt_rt_val_stat_cd is null;
294
295 cursor c_ended_result2(p_lf_evt_ocrd_dt date,
296 p_pl_typ_id number,
297 p_person_id number,
298 p_business_group_id number,
299 p_pgm_id number) is
300 select pen.*
301 from ben_prtt_enrt_rslt_f pen
302 where pen.effective_end_date = hr_api.g_eot
303 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
304 and pen.person_id = p_person_id
305 and pen.pgm_id <> p_pgm_id
306 and pen.pl_typ_id = p_pl_typ_id
307 and p_lf_evt_ocrd_dt between pen.enrt_cvg_strt_dt and
308 pen.enrt_cvg_thru_dt
309 and pen.prtt_enrt_rslt_stat_cd is null
310 and pen.per_in_ler_id <> p_per_in_ler_id
311 and pen.business_group_id = p_business_group_id
312 and not exists (select null from ben_elig_per_elctbl_chc
313 where per_in_ler_id = p_per_in_ler_id
314 and pgm_id = pen.pgm_id);
315 --
316 cursor c_pen (p_prtt_enrt_rslt_id number) is
317 select pen.effective_start_date,
318 pen.object_version_number
319 from ben_prtt_enrt_rslt_f pen
320 where pen.effective_end_date = hr_api.g_eot
321 and pen.prtt_enrt_rslt_stat_cd is null
322 and pen.business_group_id = p_business_group_id
323 and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
324 --
325 cursor c_prv (p_per_in_ler_id number,
326 p_acty_base_rt_id number,
327 p_prtt_enrt_rslt_id number) is
328 select prv.*
329 from ben_prtt_rt_val prv
330 where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
331 and prv.ended_per_in_ler_id = p_per_in_ler_id
332 and prv.acty_base_rt_id = p_acty_base_rt_id
333 and prv.prtt_rt_val_stat_cd is not null;
334 --
335 -- 8896851
336 --
337 cursor c_get_prv (p_prtt_enrt_rslt_id number,
338 p_per_in_ler_id number) is
339 select prv.*
340 from ben_prtt_rt_val prv
341 where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
342 and prv.rt_end_dt <> hr_api.g_eot
343 and prv.ended_per_in_ler_id = p_per_in_ler_id
344 and prv.prtt_rt_val_stat_cd is null;
345 --
346 -- end 8896851
347 --
348 -- 5376652
349 cursor c_epe (p_per_in_ler_id number,
350 p_prtt_enrt_rslt_id number) is
351 select elig_per_elctbl_chc_id
352 from ben_elig_per_elctbl_chc epe
353 where epe.per_in_ler_id in (select per_in_ler_id
354 from ben_prtt_enrt_rslt_f
355 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id)
356 and epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
357 --
358 cursor c_get_prior_per_in_ler is
359 select 'Y'
360 from ben_per_in_ler pil
361 where pil.per_in_ler_id <> p_per_in_ler_id
362 and trunc(p_lf_evt_ocrd_dt, 'MM') = trunc(pil.lf_evt_ocrd_dt, 'MM')
363 and pil.person_id = p_person_id
364 and pil.business_group_id = p_business_group_id
365 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
366 --
367 cursor c_get_enrt_rslts(p_rt_end_dt date
368 ,p_ptip_id number
369 ) is
370 select prv.*
371 ,abr.element_type_id
372 ,abr.input_value_id
373 ,pen.person_id
374 from ben_prtt_enrt_rslt_f pen
375 ,ben_prtt_rt_val prv
376 ,ben_acty_base_rt_f abr
377 where pen.effective_end_date = hr_api.g_eot
378 and pen.enrt_cvg_thru_dt <> hr_api.g_eot
379 and pen.prtt_enrt_rslt_stat_cd is null
380 and pen.person_id = p_person_id
381 and pen.business_group_id = p_business_group_id
382 and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
383 and pen.ptip_id = p_ptip_id
384 and prv.prtt_rt_val_stat_cd is null
385 and prv.rt_end_dt >= p_rt_end_dt
386 and prv.acty_base_rt_id = abr.acty_base_rt_id
387 and p_effective_date between abr.effective_start_date
388 and abr.effective_end_date;
389 --
390 -- Added for bug 7206471
391 --
392 cursor c_get_enrt_rslts_for_pen(p_cvg_end_dt date
393 ,p_ptip_id number
394 ) is
395 select pen.*
396 from ben_prtt_enrt_rslt_f pen
397 ,ben_ptip_f ptip
398 where pen.effective_end_date = hr_api.g_eot -- '31-dec-4712'
399 and pen.enrt_cvg_thru_dt <> hr_api.g_eot -- '31-dec-4712'
400 and pen.prtt_enrt_rslt_stat_cd is null
401 and pen.person_id = p_person_id -- 318321
402 and pen.business_group_id = p_business_group_id -- 81545
403 and pen.ptip_id = p_ptip_id -- 54444
404 and pen.enrt_cvg_thru_dt >= p_cvg_end_dt -- '20-jan-2008'
405 and pen.ptip_id = ptip.ptip_id
406 and p_effective_date between ptip.effective_start_date
407 and ptip.effective_end_date;
408 -- End bug 7206471
409 --
410 cursor c_get_pgm is
411 select distinct epe.pgm_id
412 from ben_elig_per_elctbl_chc epe
413 where epe.per_in_ler_id = p_per_in_ler_id;
414 --
415 -- Get program extra info to determine if rates should be adjusted.
416 --
417 cursor c_get_pgm_extra_info(p_pgm_id number) is
418 select pgi_information1
419 from ben_pgm_extra_info
420 where information_type = 'ADJ_RATE_PREV_LF_EVT'
421 and pgm_id = p_pgm_id;
422 --
423 -- Added for bug 7206471
424 --
425 cursor c_get_pgm_extra_info_cvg(p_pgm_id number) is
426 select pgi_information1
427 from ben_pgm_extra_info
428 where information_type = 'ADJ_CVG_PREV_LF_EVT'
429 and pgm_id = p_pgm_id;
430 --
431 -- Ended bug 7206471
432 --
433 cursor c_get_elctbl_chc is
434 select min(ecr.rt_strt_dt) rt_strt_dt
435 ,epe.ptip_id
436 from ben_elig_per_elctbl_chc epe
437 ,ben_enrt_rt ecr
438 ,ben_enrt_bnft enb
439 where epe.per_in_ler_id = p_per_in_ler_id
440 and epe.business_group_id = p_business_group_id
441 and decode(ecr.enrt_bnft_id, null, ecr.elig_per_elctbl_chc_id,
442 enb.elig_per_elctbl_chc_id) = epe.elig_per_elctbl_chc_id
443 and enb.enrt_bnft_id (+) = ecr.enrt_bnft_id
444 and ecr.rt_strt_dt is not null
445 and ecr.business_group_id = p_business_group_id
446 group by epe.ptip_id;
447 --
448 -- Added for bug 7206471
449 --
450 cursor c_get_elctbl_chc_for_cvg is
451 select min(epe.enrt_cvg_strt_dt) enrt_cvg_strt_dt
452 ,epe.ptip_id
453 from ben_elig_per_elctbl_chc epe
454 where epe.per_in_ler_id = p_per_in_ler_id
455 and epe.business_group_id = p_business_group_id
456 group by epe.ptip_id;
457 --
458 --End bug 7206471
459 --
460 cursor c_prtt_rt_val_adj (p_per_in_ler_id number,
461 p_prtt_rt_val_id number) is
462 select null
463 from ben_le_clsn_n_rstr
464 where BKUP_TBL_TYP_CD = 'BEN_PRTT_RT_VAL_ADJ'
465 AND BKUP_TBL_ID = p_prtt_rt_val_id
466 AND PER_IN_LER_ID = p_per_in_ler_id;
467 --
468 -- Added for bug 7206471
469 --
470 cursor c_prtt_enrt_rslt_adj (p_per_in_ler_id number,
471 p_prtt_enrt_rslt_id number) is
472 select null
473 from ben_le_clsn_n_rstr
474 where BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_ADJ'
475 AND BKUP_TBL_ID = p_prtt_enrt_rslt_id
476 AND PER_IN_LER_ID = p_per_in_ler_id;
477 --
478 -- End bug 7206471
479 --
480 l_elig_per_elctbl_chc_id number;
481 l_ended_result c_ended_result%rowtype;
482 l_ended_result2 c_ended_result2%rowtype;
483 l_pgm_id number;
484 l_pl_typ_id number;
485 l_pen c_pen%rowtype;
486 l_effective_start_date date;
487 l_effective_end_date date;
488 l_ended_prv c_ended_prv%rowtype;
489 l_prv c_prv%rowtype;
490 l_exists varchar2(1);
491 l_adjust varchar2(1);
492 l_cvg_adjust varchar2(1); -- bug 7206471
493
494 begin
495 --
496 hr_utility.set_location ('Entering '||g_package, 10);
497 -- bug 6127624
498 OPEN csr_electable_epes;
499
500 FETCH csr_electable_epes
501 INTO l_rec_electable_epes;
502
503 IF csr_electable_epes%FOUND
504 THEN
505 IF g_debug
506 THEN
507 hr_utility.set_location ('Electable choices found ', 121);
508 END IF;
509 open c_ended_result (p_per_in_ler_id,p_business_group_id);
510 loop
511 fetch c_ended_result into l_ended_result;
512 if c_ended_result%notfound then
513 exit;
514 end if;
515 -- bug 6127624
516 IF check_prev_eligible (p_person_id => p_person_id,
517 p_pgm_id => l_ended_result.pgm_id,
518 p_pl_id => l_ended_result.pl_id,
519 p_oipl_id => l_ended_result.oipl_id,
520 p_effective_date => p_effective_date,
521 p_business_group_id => p_business_group_id,
522 p_per_in_ler_id => p_per_in_ler_id
523 )
524 THEN
525 IF g_debug
526 THEN
527 hr_utility.set_location ( 'Eligible for pen_id '|| l_ended_result.prtt_enrt_rslt_id,121);
528 END IF;
529
530 insert into BEN_LE_CLSN_N_RSTR (
531 BKUP_TBL_TYP_CD,
532 COMP_LVL_CD,
533 LCR_ATTRIBUTE16,
534 LCR_ATTRIBUTE17,
535 LCR_ATTRIBUTE18,
536 LCR_ATTRIBUTE19,
537 LCR_ATTRIBUTE20,
538 LCR_ATTRIBUTE21,
539 LCR_ATTRIBUTE22,
540 LCR_ATTRIBUTE23,
541 LCR_ATTRIBUTE24,
542 LCR_ATTRIBUTE25,
543 LCR_ATTRIBUTE26,
544 LCR_ATTRIBUTE27,
545 LCR_ATTRIBUTE28,
546 LCR_ATTRIBUTE29,
547 LCR_ATTRIBUTE30,
548 LAST_UPDATE_DATE,
549 LAST_UPDATED_BY,
550 LAST_UPDATE_LOGIN,
551 CREATED_BY,
552 CREATION_DATE,
553 REQUEST_ID,
554 PROGRAM_APPLICATION_ID,
555 PROGRAM_ID,
556 PROGRAM_UPDATE_DATE,
557 OBJECT_VERSION_NUMBER,
558 BKUP_TBL_ID, -- PRTT_ENRT_RSLT_ID,
559 EFFECTIVE_START_DATE,
560 EFFECTIVE_END_DATE,
561 ENRT_CVG_STRT_DT,
562 ENRT_CVG_THRU_DT,
563 SSPNDD_FLAG,
564 PRTT_IS_CVRD_FLAG,
565 BNFT_AMT,
566 BNFT_NNMNTRY_UOM,
567 BNFT_TYP_CD,
568 UOM,
569 ORGNL_ENRT_DT,
570 ENRT_MTHD_CD,
571 ENRT_OVRIDN_FLAG,
572 ENRT_OVRID_RSN_CD,
573 ERLST_DEENRT_DT,
574 ENRT_OVRID_THRU_DT,
575 NO_LNGR_ELIG_FLAG,
576 BNFT_ORDR_NUM,
577 PERSON_ID,
578 ASSIGNMENT_ID,
579 PGM_ID,
580 PRTT_ENRT_RSLT_STAT_CD,
581 PL_ID,
582 OIPL_ID,
583 PTIP_ID,
584 PL_TYP_ID,
585 LER_ID,
586 PER_IN_LER_ID,
587 RPLCS_SSPNDD_RSLT_ID,
588 BUSINESS_GROUP_ID,
589 LCR_ATTRIBUTE_CATEGORY,
590 LCR_ATTRIBUTE1,
591 LCR_ATTRIBUTE2,
592 LCR_ATTRIBUTE3,
593 LCR_ATTRIBUTE4,
594 LCR_ATTRIBUTE5,
595 LCR_ATTRIBUTE6,
596 LCR_ATTRIBUTE7,
597 LCR_ATTRIBUTE8,
598 LCR_ATTRIBUTE9,
599 LCR_ATTRIBUTE10,
600 LCR_ATTRIBUTE11,
601 LCR_ATTRIBUTE12,
602 LCR_ATTRIBUTE13,
603 LCR_ATTRIBUTE14,
604 LCR_ATTRIBUTE15 ,
605 PER_IN_LER_ENDED_ID,
606 PL_ORDR_NUM,
607 PLIP_ORDR_NUM,
608 PTIP_ORDR_NUM,
609 OIPL_ORDR_NUM)
610 values (
611 'BEN_PRTT_ENRT_RSLT_F_DEL',
612 l_ended_result.COMP_LVL_CD,
613 l_ended_result.PEN_ATTRIBUTE16,
614 l_ended_result.PEN_ATTRIBUTE17,
615 l_ended_result.PEN_ATTRIBUTE18,
616 l_ended_result.PEN_ATTRIBUTE19,
617 l_ended_result.PEN_ATTRIBUTE20,
618 l_ended_result.PEN_ATTRIBUTE21,
619 l_ended_result.PEN_ATTRIBUTE22,
620 l_ended_result.PEN_ATTRIBUTE23,
621 l_ended_result.PEN_ATTRIBUTE24,
622 l_ended_result.PEN_ATTRIBUTE25,
623 l_ended_result.PEN_ATTRIBUTE26,
624 l_ended_result.PEN_ATTRIBUTE27,
625 l_ended_result.PEN_ATTRIBUTE28,
626 l_ended_result.PEN_ATTRIBUTE29,
627 l_ended_result.PEN_ATTRIBUTE30,
628 l_ended_result.LAST_UPDATE_DATE,
629 l_ended_result.LAST_UPDATED_BY,
630 l_ended_result.LAST_UPDATE_LOGIN,
631 l_ended_result.CREATED_BY,
632 l_ended_result.CREATION_DATE,
633 l_ended_result.REQUEST_ID,
634 l_ended_result.PROGRAM_APPLICATION_ID,
635 l_ended_result.PROGRAM_ID,
636 l_ended_result.PROGRAM_UPDATE_DATE,
637 l_ended_result.OBJECT_VERSION_NUMBER,
638 l_ended_result.PRTT_ENRT_RSLT_ID,
639 l_ended_result.EFFECTIVE_START_DATE,
640 l_ended_result.EFFECTIVE_END_DATE,
641 l_ended_result.ENRT_CVG_STRT_DT,
642 l_ended_result.ENRT_CVG_THRU_DT,
643 l_ended_result.SSPNDD_FLAG,
644 l_ended_result.PRTT_IS_CVRD_FLAG,
645 l_ended_result.BNFT_AMT,
646 l_ended_result.BNFT_NNMNTRY_UOM,
647 l_ended_result.BNFT_TYP_CD,
648 l_ended_result.UOM,
649 l_ended_result.ORGNL_ENRT_DT,
650 l_ended_result.ENRT_MTHD_CD,
651 l_ended_result.ENRT_OVRIDN_FLAG,
652 l_ended_result.ENRT_OVRID_RSN_CD,
653 l_ended_result.ERLST_DEENRT_DT,
654 l_ended_result.ENRT_OVRID_THRU_DT,
655 l_ended_result.NO_LNGR_ELIG_FLAG,
656 l_ended_result.BNFT_ORDR_NUM,
657 l_ended_result.PERSON_ID,
658 l_ended_result.ASSIGNMENT_ID,
659 l_ended_result.PGM_ID,
660 l_ended_result.PRTT_ENRT_RSLT_STAT_CD,
661 l_ended_result.PL_ID,
662 l_ended_result.OIPL_ID,
663 l_ended_result.PTIP_ID,
664 l_ended_result.PL_TYP_ID,
665 l_ended_result.LER_ID,
666 l_ended_result.PER_IN_LER_ID,
667 l_ended_result.RPLCS_SSPNDD_RSLT_ID,
668 l_ended_result.BUSINESS_GROUP_ID,
669 l_ended_result.PEN_ATTRIBUTE_CATEGORY,
670 l_ended_result.PEN_ATTRIBUTE1,
671 l_ended_result.PEN_ATTRIBUTE2,
672 l_ended_result.PEN_ATTRIBUTE3,
673 l_ended_result.PEN_ATTRIBUTE4,
674 l_ended_result.PEN_ATTRIBUTE5,
675 l_ended_result.PEN_ATTRIBUTE6,
676 l_ended_result.PEN_ATTRIBUTE7,
677 l_ended_result.PEN_ATTRIBUTE8,
678 l_ended_result.PEN_ATTRIBUTE9,
679 l_ended_result.PEN_ATTRIBUTE10,
680 l_ended_result.PEN_ATTRIBUTE11,
681 l_ended_result.PEN_ATTRIBUTE12,
682 l_ended_result.PEN_ATTRIBUTE13,
683 l_ended_result.PEN_ATTRIBUTE14,
684 l_ended_result.PEN_ATTRIBUTE15,
685 p_per_in_ler_id,
686 l_ended_result.PL_ORDR_NUM,
687 l_ended_result.PLIP_ORDR_NUM,
688 l_ended_result.PTIP_ORDR_NUM,
689 l_ended_result.OIPL_ORDR_NUM
690 );
691
692 /* bug # 5345189 */
693 for l_future_results in c_future_results (p_person_id,
694 l_ended_result.enrt_cvg_thru_dt,
695 l_ended_result.pgm_id,
696 l_ended_result.pl_id,
697 l_ended_result.oipl_id,
698 l_ended_result.pl_typ_id,
699 l_ended_result.business_group_id) loop
700
701 insert into BEN_LE_CLSN_N_RSTR (
702 BKUP_TBL_TYP_CD,
703 COMP_LVL_CD,
704 LCR_ATTRIBUTE16,
705 LCR_ATTRIBUTE17,
706 LCR_ATTRIBUTE18,
707 LCR_ATTRIBUTE19,
708 LCR_ATTRIBUTE20,
709 LCR_ATTRIBUTE21,
710 LCR_ATTRIBUTE22,
711 LCR_ATTRIBUTE23,
712 LCR_ATTRIBUTE24,
713 LCR_ATTRIBUTE25,
714 LCR_ATTRIBUTE26,
715 LCR_ATTRIBUTE27,
716 LCR_ATTRIBUTE28,
717 LCR_ATTRIBUTE29,
718 LCR_ATTRIBUTE30,
719 LAST_UPDATE_DATE,
720 LAST_UPDATED_BY,
721 LAST_UPDATE_LOGIN,
722 CREATED_BY,
723 CREATION_DATE,
724 REQUEST_ID,
725 PROGRAM_APPLICATION_ID,
726 PROGRAM_ID,
727 PROGRAM_UPDATE_DATE,
728 OBJECT_VERSION_NUMBER,
729 BKUP_TBL_ID, -- PRTT_ENRT_RSLT_ID,
730 EFFECTIVE_START_DATE,
731 EFFECTIVE_END_DATE,
732 ENRT_CVG_STRT_DT,
733 ENRT_CVG_THRU_DT,
734 SSPNDD_FLAG,
735 PRTT_IS_CVRD_FLAG,
736 BNFT_AMT,
737 BNFT_NNMNTRY_UOM,
738 BNFT_TYP_CD,
739 UOM,
740 ORGNL_ENRT_DT,
741 ENRT_MTHD_CD,
742 ENRT_OVRIDN_FLAG,
743 ENRT_OVRID_RSN_CD,
744 ERLST_DEENRT_DT,
745 ENRT_OVRID_THRU_DT,
746 NO_LNGR_ELIG_FLAG,
747 BNFT_ORDR_NUM,
748 PERSON_ID,
749 ASSIGNMENT_ID,
750 PGM_ID,
751 PRTT_ENRT_RSLT_STAT_CD,
752 PL_ID,
753 OIPL_ID,
754 PTIP_ID,
755 PL_TYP_ID,
756 LER_ID,
757 PER_IN_LER_ID,
758 RPLCS_SSPNDD_RSLT_ID,
759 BUSINESS_GROUP_ID,
760 LCR_ATTRIBUTE_CATEGORY,
761 LCR_ATTRIBUTE1,
762 LCR_ATTRIBUTE2,
763 LCR_ATTRIBUTE3,
764 LCR_ATTRIBUTE4,
765 LCR_ATTRIBUTE5,
766 LCR_ATTRIBUTE6,
767 LCR_ATTRIBUTE7,
768 LCR_ATTRIBUTE8,
769 LCR_ATTRIBUTE9,
770 LCR_ATTRIBUTE10,
771 LCR_ATTRIBUTE11,
772 LCR_ATTRIBUTE12,
773 LCR_ATTRIBUTE13,
774 LCR_ATTRIBUTE14,
775 LCR_ATTRIBUTE15 ,
776 PER_IN_LER_ENDED_ID,
777 PL_ORDR_NUM,
778 PLIP_ORDR_NUM,
779 PTIP_ORDR_NUM,
780 OIPL_ORDR_NUM)
781 values (
782 'BEN_PRTT_ENRT_RSLT_F_DEL',
783 l_future_results.COMP_LVL_CD,
784 l_future_results.PEN_ATTRIBUTE16,
785 l_future_results.PEN_ATTRIBUTE17,
786 l_future_results.PEN_ATTRIBUTE18,
787 l_future_results.PEN_ATTRIBUTE19,
788 l_future_results.PEN_ATTRIBUTE20,
789 l_future_results.PEN_ATTRIBUTE21,
790 l_future_results.PEN_ATTRIBUTE22,
791 l_future_results.PEN_ATTRIBUTE23,
792 l_future_results.PEN_ATTRIBUTE24,
793 l_future_results.PEN_ATTRIBUTE25,
794 l_future_results.PEN_ATTRIBUTE26,
795 l_future_results.PEN_ATTRIBUTE27,
796 l_future_results.PEN_ATTRIBUTE28,
797 l_future_results.PEN_ATTRIBUTE29,
798 l_future_results.PEN_ATTRIBUTE30,
799 l_future_results.LAST_UPDATE_DATE,
800 l_future_results.LAST_UPDATED_BY,
801 l_future_results.LAST_UPDATE_LOGIN,
802 l_future_results.CREATED_BY,
803 l_future_results.CREATION_DATE,
804 l_future_results.REQUEST_ID,
805 l_future_results.PROGRAM_APPLICATION_ID,
806 l_future_results.PROGRAM_ID,
807 l_future_results.PROGRAM_UPDATE_DATE,
808 l_future_results.OBJECT_VERSION_NUMBER,
809 l_future_results.PRTT_ENRT_RSLT_ID,
810 l_future_results.EFFECTIVE_START_DATE,
811 l_future_results.EFFECTIVE_END_DATE,
812 l_future_results.ENRT_CVG_STRT_DT,
813 l_future_results.ENRT_CVG_THRU_DT,
814 l_future_results.SSPNDD_FLAG,
815 l_future_results.PRTT_IS_CVRD_FLAG,
816 l_future_results.BNFT_AMT,
817 l_future_results.BNFT_NNMNTRY_UOM,
818 l_future_results.BNFT_TYP_CD,
819 l_future_results.UOM,
820 l_future_results.ORGNL_ENRT_DT,
821 l_future_results.ENRT_MTHD_CD,
822 l_future_results.ENRT_OVRIDN_FLAG,
823 l_future_results.ENRT_OVRID_RSN_CD,
824 l_future_results.ERLST_DEENRT_DT,
825 l_future_results.ENRT_OVRID_THRU_DT,
826 l_future_results.NO_LNGR_ELIG_FLAG,
827 l_future_results.BNFT_ORDR_NUM,
828 l_future_results.PERSON_ID,
829 l_future_results.ASSIGNMENT_ID,
830 l_future_results.PGM_ID,
831 l_future_results.PRTT_ENRT_RSLT_STAT_CD,
832 l_future_results.PL_ID,
833 l_future_results.OIPL_ID,
834 l_future_results.PTIP_ID,
835 l_future_results.PL_TYP_ID,
836 l_future_results.LER_ID,
837 l_future_results.PER_IN_LER_ID,
838 l_future_results.RPLCS_SSPNDD_RSLT_ID,
839 l_future_results.BUSINESS_GROUP_ID,
840 l_future_results.PEN_ATTRIBUTE_CATEGORY,
841 l_future_results.PEN_ATTRIBUTE1,
842 l_future_results.PEN_ATTRIBUTE2,
843 l_future_results.PEN_ATTRIBUTE3,
844 l_future_results.PEN_ATTRIBUTE4,
845 l_future_results.PEN_ATTRIBUTE5,
846 l_future_results.PEN_ATTRIBUTE6,
847 l_future_results.PEN_ATTRIBUTE7,
848 l_future_results.PEN_ATTRIBUTE8,
849 l_future_results.PEN_ATTRIBUTE9,
850 l_future_results.PEN_ATTRIBUTE10,
851 l_future_results.PEN_ATTRIBUTE11,
852 l_future_results.PEN_ATTRIBUTE12,
853 l_future_results.PEN_ATTRIBUTE13,
854 l_future_results.PEN_ATTRIBUTE14,
855 l_future_results.PEN_ATTRIBUTE15,
856 p_per_in_ler_id,
857 l_future_results.PL_ORDR_NUM,
858 l_future_results.PLIP_ORDR_NUM,
859 l_future_results.PTIP_ORDR_NUM,
860 l_future_results.OIPL_ORDR_NUM
861 );
862 hr_utility.set_location('DDD 33 Backingout l_future_results.prtt_enrt_rslt_id'||l_future_results.prtt_enrt_rslt_id,999);
863
864 ben_back_out_life_event.back_out_life_events
865 (p_per_in_ler_id => l_future_results.per_in_ler_id,
866 p_business_group_id => p_business_group_id,
867 p_bckdt_prtt_enrt_rslt_id => l_future_results.prtt_enrt_rslt_id,
868 p_effective_date => p_effective_date);
869 -- strt 5376652: to avoid 91203 error the prtt_rt_val_id needs to be updated
870 open c_epe(p_per_in_ler_id,
871 l_future_results.prtt_enrt_rslt_id);
872 loop
873 fetch c_epe into l_elig_per_elctbl_chc_id;
874 if c_epe%notfound then
875 exit;
876 end if;
877 --
878 update ben_enrt_rt set prtt_rt_val_id = null
879 where elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id;
880 end loop;
881 close c_epe;
882
883 -- update to avoid 91711 error
884 update ben_elig_per_elctbl_chc set prtt_enrt_rslt_id = null,
885 CRNTLY_ENRD_FLAG = 'N'
886 where prtt_enrt_rslt_id = l_future_results.prtt_enrt_rslt_id
887 and per_in_ler_id = p_per_in_ler_id;
888
889 -- end 5376652
890
891 end loop;
892 /* bug#5345189 */
893 --
894 ben_back_out_life_event.back_out_life_events
895 (p_per_in_ler_id => l_ended_result.per_in_ler_id,
896 p_business_group_id => p_business_group_id,
897 p_bckdt_prtt_enrt_rslt_id => l_ended_result.prtt_enrt_rslt_id,
898 p_effective_date => p_effective_date);
899 --
900 end if; -- check_prev_eligible
901 end loop;
902 close c_ended_result;
903 hr_utility.set_location ('Leaving'||g_package, 20);
904 -- Reopen ended result in other ineligible program and deenroll
905 open c_pl_typ (p_per_in_ler_id);
906 loop
907 fetch c_pl_typ into l_pgm_id, l_pl_typ_id;
908 if c_pl_typ%notfound then
909 exit;
910 end if;
911 -- to backout the rate attached to the closed result
912 open c_ended_prv (p_per_in_ler_id => p_per_in_ler_id,
913 p_person_id => p_person_id,
914 p_pgm_id => l_pgm_id);
915 loop
916 fetch c_ended_prv into l_ended_prv;
917 if c_ended_prv%notfound then
918 exit;
919 end if;
920 --
921 open c_prv (p_per_in_ler_id => p_per_in_ler_id,
922 p_acty_base_rt_id => l_ended_prv.acty_base_rt_id,
923 p_prtt_enrt_rslt_id => l_ended_prv.prtt_enrt_rslt_id);
924 fetch c_prv into l_prv;
925 if c_prv%found then
926 --
927 hr_utility.set_location('Update prtt rt val'||l_ended_prv.prtt_rt_val_id,100);
928 if l_prv.rt_end_dt < l_ended_prv.rt_end_dt then -- 5947036
929
930 hr_utility.set_location('l_prv.rt_end_dt ' || l_prv.rt_end_dt ,121);
931 hr_utility.set_location('l_ended_prv.rt_end_dt ' || l_ended_prv.rt_end_dt ,121);
932 hr_utility.set_location('Rate should be ended with lesser date ',121);
933
934 ben_prtt_rt_val_api.update_prtt_rt_val
935 (P_VALIDATE => FALSE
936 ,P_PRTT_RT_VAL_ID => l_ended_prv.prtt_rt_val_id
937 ,P_RT_END_DT => l_prv.rt_end_dt
938 ,p_person_id => p_person_id
939 ,p_ended_per_in_ler_id => p_per_in_ler_id
940 ,p_business_group_id => p_business_group_id
941 ,P_OBJECT_VERSION_NUMBER => l_ended_prv.object_version_number
942 ,P_EFFECTIVE_DATE => p_effective_date
943 );
944 else
945 hr_utility.set_location('l_prv.rt_end_dt ' || l_prv.rt_end_dt ,121);
946 hr_utility.set_location('l_ended_prv.rt_end_dt ' || l_ended_prv.rt_end_dt ,121);
947 hr_utility.set_location('Rate is ended with a lesser date already ',121);
948 end if;
949 -- end 5947036
950 end if;
951 close c_prv;
952
953 end loop;
954 close c_ended_prv;
955 --
956
957 open c_ended_result2(p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
958 p_pl_typ_id => l_pl_typ_id,
959 p_person_id => p_person_id,
960 p_business_group_id => p_business_group_id,
961 p_pgm_id => l_pgm_id);
962 loop
963 fetch c_ended_result2 into l_ended_result2;
964 if c_ended_result2%notfound then
965 exit;
966 end if;
967 --
968 hr_utility.set_location ('ENdedn result 2',100);
969 hr_utility.set_location ('p_lf_evt_ocrd_dt '||p_lf_evt_ocrd_dt,100);
970 hr_utility.set_location ('p_pl_typ_id '||l_pl_typ_id,100);
971 hr_utility.set_location ('p_pgm_id '||l_pgm_id,100);
972 hr_utility.set_location ('l_ended_results2.prtt_enrt_rslt_id '||l_ended_result2.prtt_enrt_rslt_id,100);
973 insert into BEN_LE_CLSN_N_RSTR (
974 BKUP_TBL_TYP_CD,
975 COMP_LVL_CD,
976 LCR_ATTRIBUTE16,
977 LCR_ATTRIBUTE17,
978 LCR_ATTRIBUTE18,
979 LCR_ATTRIBUTE19,
980 LCR_ATTRIBUTE20,
981 LCR_ATTRIBUTE21,
982 LCR_ATTRIBUTE22,
983 LCR_ATTRIBUTE23,
984 LCR_ATTRIBUTE24,
985 LCR_ATTRIBUTE25,
986 LCR_ATTRIBUTE26,
987 LCR_ATTRIBUTE27,
988 LCR_ATTRIBUTE28,
989 LCR_ATTRIBUTE29,
990 LCR_ATTRIBUTE30,
991 LAST_UPDATE_DATE,
992 LAST_UPDATED_BY,
993 LAST_UPDATE_LOGIN,
994 CREATED_BY,
995 CREATION_DATE,
996 REQUEST_ID,
997 PROGRAM_APPLICATION_ID,
998 PROGRAM_ID,
999 PROGRAM_UPDATE_DATE,
1000 OBJECT_VERSION_NUMBER,
1001 BKUP_TBL_ID, -- PRTT_ENRT_RSLT_ID,
1002 EFFECTIVE_START_DATE,
1003 EFFECTIVE_END_DATE,
1004 ENRT_CVG_STRT_DT,
1005 ENRT_CVG_THRU_DT,
1006 SSPNDD_FLAG,
1007 PRTT_IS_CVRD_FLAG,
1008 BNFT_AMT,
1009 BNFT_NNMNTRY_UOM,
1010 BNFT_TYP_CD,
1011 UOM,
1012 ORGNL_ENRT_DT,
1013 ENRT_MTHD_CD,
1014 ENRT_OVRIDN_FLAG,
1015 ENRT_OVRID_RSN_CD,
1016 ERLST_DEENRT_DT,
1017 ENRT_OVRID_THRU_DT,
1018 NO_LNGR_ELIG_FLAG,
1019 BNFT_ORDR_NUM,
1020 PERSON_ID,
1021 ASSIGNMENT_ID,
1022 PGM_ID,
1023 PRTT_ENRT_RSLT_STAT_CD,
1024 PL_ID,
1025 OIPL_ID,
1026 PTIP_ID,
1027 PL_TYP_ID,
1028 LER_ID,
1029 PER_IN_LER_ID,
1030 RPLCS_SSPNDD_RSLT_ID,
1031 BUSINESS_GROUP_ID,
1032 LCR_ATTRIBUTE_CATEGORY,
1033 LCR_ATTRIBUTE1,
1034 LCR_ATTRIBUTE2,
1035 LCR_ATTRIBUTE3,
1036 LCR_ATTRIBUTE4,
1037 LCR_ATTRIBUTE5,
1038 LCR_ATTRIBUTE6,
1039 LCR_ATTRIBUTE7,
1040 LCR_ATTRIBUTE8,
1041 LCR_ATTRIBUTE9,
1042 LCR_ATTRIBUTE10,
1043 LCR_ATTRIBUTE11,
1044 LCR_ATTRIBUTE12,
1045 LCR_ATTRIBUTE13,
1046 LCR_ATTRIBUTE14,
1047 LCR_ATTRIBUTE15 ,
1048 PER_IN_LER_ENDED_ID,
1049 PL_ORDR_NUM,
1050 PLIP_ORDR_NUM,
1051 PTIP_ORDR_NUM,
1052 OIPL_ORDR_NUM)
1053 values (
1054 'BEN_PRTT_ENRT_RSLT_F_DEL',
1055 l_ended_result2.COMP_LVL_CD,
1056 l_ended_result2.PEN_ATTRIBUTE16,
1057 l_ended_result2.PEN_ATTRIBUTE17,
1058 l_ended_result2.PEN_ATTRIBUTE18,
1059 l_ended_result2.PEN_ATTRIBUTE19,
1060 l_ended_result2.PEN_ATTRIBUTE20,
1061 l_ended_result2.PEN_ATTRIBUTE21,
1062 l_ended_result2.PEN_ATTRIBUTE22,
1063 l_ended_result2.PEN_ATTRIBUTE23,
1064 l_ended_result2.PEN_ATTRIBUTE24,
1065 l_ended_result2.PEN_ATTRIBUTE25,
1066 l_ended_result2.PEN_ATTRIBUTE26,
1067 l_ended_result2.PEN_ATTRIBUTE27,
1068 l_ended_result2.PEN_ATTRIBUTE28,
1069 l_ended_result2.PEN_ATTRIBUTE29,
1070 l_ended_result2.PEN_ATTRIBUTE30,
1071 l_ended_result2.LAST_UPDATE_DATE,
1072 l_ended_result2.LAST_UPDATED_BY,
1073 l_ended_result2.LAST_UPDATE_LOGIN,
1074 l_ended_result2.CREATED_BY,
1075 l_ended_result2.CREATION_DATE,
1076 l_ended_result2.REQUEST_ID,
1077 l_ended_result2.PROGRAM_APPLICATION_ID,
1078 l_ended_result2.PROGRAM_ID,
1079 l_ended_result2.PROGRAM_UPDATE_DATE,
1080 l_ended_result2.OBJECT_VERSION_NUMBER,
1081 l_ended_result2.PRTT_ENRT_RSLT_ID,
1082 l_ended_result2.EFFECTIVE_START_DATE,
1083 l_ended_result2.EFFECTIVE_END_DATE,
1084 l_ended_result2.ENRT_CVG_STRT_DT,
1085 l_ended_result2.ENRT_CVG_THRU_DT,
1086 l_ended_result2.SSPNDD_FLAG,
1087 l_ended_result2.PRTT_IS_CVRD_FLAG,
1088 l_ended_result2.BNFT_AMT,
1089 l_ended_result2.BNFT_NNMNTRY_UOM,
1090 l_ended_result2.BNFT_TYP_CD,
1091 l_ended_result2.UOM,
1092 l_ended_result2.ORGNL_ENRT_DT,
1093 l_ended_result2.ENRT_MTHD_CD,
1094 l_ended_result2.ENRT_OVRIDN_FLAG,
1095 l_ended_result2.ENRT_OVRID_RSN_CD,
1096 l_ended_result2.ERLST_DEENRT_DT,
1097 l_ended_result2.ENRT_OVRID_THRU_DT,
1098 l_ended_result2.NO_LNGR_ELIG_FLAG,
1099 l_ended_result2.BNFT_ORDR_NUM,
1100 l_ended_result2.PERSON_ID,
1101 l_ended_result2.ASSIGNMENT_ID,
1102 l_ended_result2.PGM_ID,
1103 l_ended_result2.PRTT_ENRT_RSLT_STAT_CD,
1104 l_ended_result2.PL_ID,
1105 l_ended_result2.OIPL_ID,
1106 l_ended_result2.PTIP_ID,
1107 l_ended_result2.PL_TYP_ID,
1108 l_ended_result2.LER_ID,
1109 l_ended_result2.PER_IN_LER_ID,
1110 l_ended_result2.RPLCS_SSPNDD_RSLT_ID,
1111 l_ended_result2.BUSINESS_GROUP_ID,
1112 l_ended_result2.PEN_ATTRIBUTE_CATEGORY,
1113 l_ended_result2.PEN_ATTRIBUTE1,
1114 l_ended_result2.PEN_ATTRIBUTE2,
1115 l_ended_result2.PEN_ATTRIBUTE3,
1116 l_ended_result2.PEN_ATTRIBUTE4,
1117 l_ended_result2.PEN_ATTRIBUTE5,
1118 l_ended_result2.PEN_ATTRIBUTE6,
1119 l_ended_result2.PEN_ATTRIBUTE7,
1120 l_ended_result2.PEN_ATTRIBUTE8,
1121 l_ended_result2.PEN_ATTRIBUTE9,
1122 l_ended_result2.PEN_ATTRIBUTE10,
1123 l_ended_result2.PEN_ATTRIBUTE11,
1124 l_ended_result2.PEN_ATTRIBUTE12,
1125 l_ended_result2.PEN_ATTRIBUTE13,
1126 l_ended_result2.PEN_ATTRIBUTE14,
1127 l_ended_result2.PEN_ATTRIBUTE15,
1128 p_per_in_ler_id,
1129 l_ended_result2.PL_ORDR_NUM,
1130 l_ended_result2.PLIP_ORDR_NUM,
1131 l_ended_result2.PTIP_ORDR_NUM,
1132 l_ended_result2.OIPL_ORDR_NUM
1133 );
1134 --
1135 -- Back up the prtt rt val -- 8896851
1136 --
1137 hr_utility.set_location ('l_ended_result2.prtt_enrt_rslt_id '||l_ended_result2.prtt_enrt_rslt_id,101);
1138 hr_utility.set_location ('l_ended_result2.per_in_ler_id '||l_ended_result2.per_in_ler_id,101);
1139 for l_prv_rec in c_get_prv(l_ended_result2.prtt_enrt_rslt_id
1140 ,l_ended_result2.per_in_ler_id) loop
1141 hr_utility.set_location ('l_prv_rec.prtt_rt_val_id'||l_prv_rec.prtt_rt_val_id,102);
1142 hr_utility.set_location ('l_prv_rec.ended_per_in_ler_id'||l_prv_rec.ended_per_in_ler_id,102);
1143 hr_utility.set_location ('p_per_in_ler_id'||p_per_in_ler_id,102);
1144 insert into BEN_LE_CLSN_N_RSTR (
1145 BKUP_TBL_TYP_CD,
1146 RT_END_DT,
1147 PER_IN_LER_ID,
1148 PER_IN_LER_ENDED_ID,
1149 BKUP_TBL_ID, -- prtt_rt_val_id
1150 OBJECT_VERSION_NUMBER)
1151
1152 values (
1153 'BEN_PRTT_RT_VAL',
1154 l_prv_rec.rt_end_dt,
1155 p_per_in_ler_id,
1156 l_prv_rec.ended_per_in_ler_id,
1157 l_prv_rec.prtt_rt_val_id,
1158 l_prv_rec.object_version_number
1159 );
1160 end loop;
1161 --
1162 -- end 8896851
1163 --
1164 /* bug # 8968118 */
1165 hr_utility.set_location ('enrt_cvg_thru_dt '||l_ended_result2.enrt_cvg_thru_dt,101);
1166 hr_utility.set_location ('p_pgm_id '||l_ended_result2.pgm_id, 101);
1167 for l_future_results2 in c_future_results2 (p_person_id
1168 ,l_ended_result2.enrt_cvg_thru_dt
1169 ,l_ended_result2.pl_typ_id
1170 ,l_ended_result2.business_group_id
1171 ) loop
1172
1173 insert into BEN_LE_CLSN_N_RSTR (
1174 BKUP_TBL_TYP_CD,
1175 COMP_LVL_CD,
1176 LCR_ATTRIBUTE16,
1177 LCR_ATTRIBUTE17,
1178 LCR_ATTRIBUTE18,
1179 LCR_ATTRIBUTE19,
1180 LCR_ATTRIBUTE20,
1181 LCR_ATTRIBUTE21,
1182 LCR_ATTRIBUTE22,
1183 LCR_ATTRIBUTE23,
1184 LCR_ATTRIBUTE24,
1185 LCR_ATTRIBUTE25,
1186 LCR_ATTRIBUTE26,
1187 LCR_ATTRIBUTE27,
1188 LCR_ATTRIBUTE28,
1189 LCR_ATTRIBUTE29,
1190 LCR_ATTRIBUTE30,
1191 LAST_UPDATE_DATE,
1192 LAST_UPDATED_BY,
1193 LAST_UPDATE_LOGIN,
1194 CREATED_BY,
1195 CREATION_DATE,
1196 REQUEST_ID,
1197 PROGRAM_APPLICATION_ID,
1198 PROGRAM_ID,
1199 PROGRAM_UPDATE_DATE,
1200 OBJECT_VERSION_NUMBER,
1201 BKUP_TBL_ID, -- PRTT_ENRT_RSLT_ID,
1202 EFFECTIVE_START_DATE,
1203 EFFECTIVE_END_DATE,
1204 ENRT_CVG_STRT_DT,
1205 ENRT_CVG_THRU_DT,
1206 SSPNDD_FLAG,
1207 PRTT_IS_CVRD_FLAG,
1208 BNFT_AMT,
1209 BNFT_NNMNTRY_UOM,
1210 BNFT_TYP_CD,
1211 UOM,
1212 ORGNL_ENRT_DT,
1213 ENRT_MTHD_CD,
1214 ENRT_OVRIDN_FLAG,
1215 ENRT_OVRID_RSN_CD,
1216 ERLST_DEENRT_DT,
1217 ENRT_OVRID_THRU_DT,
1218 NO_LNGR_ELIG_FLAG,
1219 BNFT_ORDR_NUM,
1220 PERSON_ID,
1221 ASSIGNMENT_ID,
1222 PGM_ID,
1223 PRTT_ENRT_RSLT_STAT_CD,
1224 PL_ID,
1225 OIPL_ID,
1226 PTIP_ID,
1227 PL_TYP_ID,
1228 LER_ID,
1229 PER_IN_LER_ID,
1230 RPLCS_SSPNDD_RSLT_ID,
1231 BUSINESS_GROUP_ID,
1232 LCR_ATTRIBUTE_CATEGORY,
1233 LCR_ATTRIBUTE1,
1234 LCR_ATTRIBUTE2,
1235 LCR_ATTRIBUTE3,
1236 LCR_ATTRIBUTE4,
1237 LCR_ATTRIBUTE5,
1238 LCR_ATTRIBUTE6,
1239 LCR_ATTRIBUTE7,
1240 LCR_ATTRIBUTE8,
1241 LCR_ATTRIBUTE9,
1242 LCR_ATTRIBUTE10,
1243 LCR_ATTRIBUTE11,
1244 LCR_ATTRIBUTE12,
1245 LCR_ATTRIBUTE13,
1246 LCR_ATTRIBUTE14,
1247 LCR_ATTRIBUTE15 ,
1248 PER_IN_LER_ENDED_ID,
1249 PL_ORDR_NUM,
1250 PLIP_ORDR_NUM,
1251 PTIP_ORDR_NUM,
1252 OIPL_ORDR_NUM)
1253 values (
1254 'BEN_PRTT_ENRT_RSLT_F_DEL',
1255 l_future_results2.COMP_LVL_CD,
1256 l_future_results2.PEN_ATTRIBUTE16,
1257 l_future_results2.PEN_ATTRIBUTE17,
1258 l_future_results2.PEN_ATTRIBUTE18,
1259 l_future_results2.PEN_ATTRIBUTE19,
1260 l_future_results2.PEN_ATTRIBUTE20,
1261 l_future_results2.PEN_ATTRIBUTE21,
1262 l_future_results2.PEN_ATTRIBUTE22,
1263 l_future_results2.PEN_ATTRIBUTE23,
1264 l_future_results2.PEN_ATTRIBUTE24,
1265 l_future_results2.PEN_ATTRIBUTE25,
1266 l_future_results2.PEN_ATTRIBUTE26,
1267 l_future_results2.PEN_ATTRIBUTE27,
1268 l_future_results2.PEN_ATTRIBUTE28,
1269 l_future_results2.PEN_ATTRIBUTE29,
1270 l_future_results2.PEN_ATTRIBUTE30,
1271 l_future_results2.LAST_UPDATE_DATE,
1272 l_future_results2.LAST_UPDATED_BY,
1273 l_future_results2.LAST_UPDATE_LOGIN,
1274 l_future_results2.CREATED_BY,
1275 l_future_results2.CREATION_DATE,
1276 l_future_results2.REQUEST_ID,
1277 l_future_results2.PROGRAM_APPLICATION_ID,
1278 l_future_results2.PROGRAM_ID,
1279 l_future_results2.PROGRAM_UPDATE_DATE,
1280 l_future_results2.OBJECT_VERSION_NUMBER,
1281 l_future_results2.PRTT_ENRT_RSLT_ID,
1282 l_future_results2.EFFECTIVE_START_DATE,
1283 l_future_results2.EFFECTIVE_END_DATE,
1284 l_future_results2.ENRT_CVG_STRT_DT,
1285 l_future_results2.ENRT_CVG_THRU_DT,
1286 l_future_results2.SSPNDD_FLAG,
1287 l_future_results2.PRTT_IS_CVRD_FLAG,
1288 l_future_results2.BNFT_AMT,
1289 l_future_results2.BNFT_NNMNTRY_UOM,
1290 l_future_results2.BNFT_TYP_CD,
1291 l_future_results2.UOM,
1292 l_future_results2.ORGNL_ENRT_DT,
1293 l_future_results2.ENRT_MTHD_CD,
1294 l_future_results2.ENRT_OVRIDN_FLAG,
1295 l_future_results2.ENRT_OVRID_RSN_CD,
1296 l_future_results2.ERLST_DEENRT_DT,
1297 l_future_results2.ENRT_OVRID_THRU_DT,
1298 l_future_results2.NO_LNGR_ELIG_FLAG,
1299 l_future_results2.BNFT_ORDR_NUM,
1300 l_future_results2.PERSON_ID,
1301 l_future_results2.ASSIGNMENT_ID,
1302 l_future_results2.PGM_ID,
1303 l_future_results2.PRTT_ENRT_RSLT_STAT_CD,
1304 l_future_results2.PL_ID,
1305 l_future_results2.OIPL_ID,
1306 l_future_results2.PTIP_ID,
1307 l_future_results2.PL_TYP_ID,
1308 l_future_results2.LER_ID,
1309 l_future_results2.PER_IN_LER_ID,
1310 l_future_results2.RPLCS_SSPNDD_RSLT_ID,
1311 l_future_results2.BUSINESS_GROUP_ID,
1312 l_future_results2.PEN_ATTRIBUTE_CATEGORY,
1313 l_future_results2.PEN_ATTRIBUTE1,
1314 l_future_results2.PEN_ATTRIBUTE2,
1315 l_future_results2.PEN_ATTRIBUTE3,
1316 l_future_results2.PEN_ATTRIBUTE4,
1317 l_future_results2.PEN_ATTRIBUTE5,
1318 l_future_results2.PEN_ATTRIBUTE6,
1319 l_future_results2.PEN_ATTRIBUTE7,
1320 l_future_results2.PEN_ATTRIBUTE8,
1321 l_future_results2.PEN_ATTRIBUTE9,
1322 l_future_results2.PEN_ATTRIBUTE10,
1323 l_future_results2.PEN_ATTRIBUTE11,
1324 l_future_results2.PEN_ATTRIBUTE12,
1325 l_future_results2.PEN_ATTRIBUTE13,
1326 l_future_results2.PEN_ATTRIBUTE14,
1327 l_future_results2.PEN_ATTRIBUTE15,
1328 p_per_in_ler_id,
1329 l_future_results2.PL_ORDR_NUM,
1330 l_future_results2.PLIP_ORDR_NUM,
1331 l_future_results2.PTIP_ORDR_NUM,
1332 l_future_results2.OIPL_ORDR_NUM
1333 );
1334 hr_utility.set_location('DDD 44 Backing out l_future_results2.prtt_enrt_rslt_id'||l_future_results2.prtt_enrt_rslt_id,999);
1335
1336 ben_back_out_life_event.back_out_life_events
1337 (p_per_in_ler_id => l_future_results2.per_in_ler_id
1338 ,p_business_group_id => p_business_group_id
1339 ,p_bckdt_prtt_enrt_rslt_id => l_future_results2.prtt_enrt_rslt_id
1340 ,p_effective_date => p_effective_date
1341 );
1342 --
1343 open c_epe(p_per_in_ler_id,
1344 l_future_results2.prtt_enrt_rslt_id);
1345 loop
1346 fetch c_epe into l_elig_per_elctbl_chc_id;
1347 if c_epe%notfound then
1348 exit;
1349 end if;
1350 --
1351 update ben_enrt_rt set prtt_rt_val_id = null
1352 where elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id;
1353 end loop;
1354 close c_epe;
1355 --
1356 -- update to avoid 91711 error
1357 --
1358 update ben_elig_per_elctbl_chc
1359 set prtt_enrt_rslt_id = null
1360 ,CRNTLY_ENRD_FLAG = 'N'
1361 where prtt_enrt_rslt_id = l_future_results2.prtt_enrt_rslt_id
1362 and per_in_ler_id = p_per_in_ler_id;
1363 end loop; -- c_future_results2
1364 --
1365 -- end bug 8968118
1366 --
1367 ben_back_out_life_event.back_out_life_events
1368 (p_per_in_ler_id => l_ended_result2.per_in_ler_id,
1369 p_business_group_id => p_business_group_id,
1370 p_bckdt_prtt_enrt_rslt_id => l_ended_result2.prtt_enrt_rslt_id,
1371 p_effective_date => p_effective_date);
1372 --
1373 l_pen.effective_start_date := null;
1374 open c_pen(l_ended_result2.prtt_enrt_rslt_id);
1375 fetch c_pen into l_pen;
1376 close c_pen;
1377 --
1378 hr_utility.set_location ('Delete enrollment ',101);
1379 if l_pen.effective_start_date is not null then
1380 ben_prtt_enrt_result_api.delete_enrollment
1381 (p_validate => false ,
1382 p_prtt_enrt_rslt_id => l_ended_result2.prtt_enrt_rslt_id,
1383 p_per_in_ler_id => p_per_in_ler_id,
1384 p_business_group_id => p_business_group_id ,
1385 p_effective_start_date => l_effective_start_date,
1386 p_effective_end_date => l_effective_end_date,
1387 p_object_version_number => l_pen.object_version_number,
1388 p_effective_date => l_pen.effective_start_date,
1389 p_datetrack_mode => 'DELETE',
1390 p_multi_row_validate => false);
1391
1392 end if;
1393 --
1394 end loop;
1395 close c_ended_result2;
1396 end loop;
1397 close c_pl_typ;
1398 /*
1399 --
1400 -- Bug 5391554. Check if the rates should be adjusted.
1401 --
1402 --
1403 -- Check if there is a life event in the same month.
1404 --
1405 open c_get_prior_per_in_ler;
1406 fetch c_get_prior_per_in_ler into l_exists;
1407 if c_get_prior_per_in_ler%found then
1408 --
1409 for l_pgm in c_get_pgm loop
1410 --
1411 open c_get_pgm_extra_info(l_pgm.pgm_id);
1412 fetch c_get_pgm_extra_info into l_adjust;
1413 if c_get_pgm_extra_info%found then
1414 --
1415 if l_adjust = 'Y' then
1416 --
1417 -- Get rt end dt
1418 --
1419 for l_epe in c_get_elctbl_chc loop
1420 --
1421 -- Get all results that were de-enrolled for the event.
1422 --
1423 for l_pen in c_get_enrt_rslts(l_epe.rt_strt_dt
1424 ,l_epe.ptip_id ) loop
1425 hr_utility.set_location('Adjusting rate '||l_epe.rt_strt_dt,111);
1426 --
1427 open c_prtt_rt_val_adj(p_per_in_ler_id,l_pen.prtt_rt_val_id);
1428 fetch c_prtt_rt_val_adj into l_exists;
1429 if c_prtt_rt_val_adj%notfound then
1430 insert into BEN_LE_CLSN_N_RSTR (
1431 BKUP_TBL_TYP_CD,
1432 BKUP_TBL_ID,
1433 per_in_ler_id,
1434 person_id,
1435 RT_END_DT,
1436 business_group_id,
1437 object_version_number)
1438 values (
1439 'BEN_PRTT_RT_VAL_ADJ',
1440 l_pen.prtt_rt_val_id,
1441 p_per_in_ler_id,
1442 l_pen.person_id,
1443 l_pen.rt_end_dt,
1444 p_business_group_id,
1445 l_pen.object_version_number
1446 );
1447 end if;
1448 close c_prtt_rt_val_adj;
1449 --
1450 ben_prtt_rt_val_api.update_prtt_rt_val
1451 (P_VALIDATE => FALSE
1452 ,P_PRTT_RT_VAL_ID => l_pen.prtt_rt_val_id
1453 ,P_RT_END_DT => l_epe.rt_strt_dt - 1
1454 ,p_person_id => l_pen.person_id
1455 ,p_input_value_id => l_pen.input_value_id
1456 ,p_element_type_id => l_pen.element_type_id
1457 ,p_business_group_id => p_business_group_id
1458 ,P_OBJECT_VERSION_NUMBER => l_pen.object_version_number
1459 ,P_EFFECTIVE_DATE => p_effective_date
1460 );
1461 end loop; -- c_get_enrt_rslts
1462 end loop; -- c_get_elctbl_chc
1463 end if; -- l_adjust = 'Y'
1464 end if; -- c_get_pgm_extra_info
1465 close c_get_pgm_extra_info;
1466 end loop; -- c_get_pgm
1467 --
1468 -- Bug 7206471. Check if the coverage should be adjusted.
1469 --
1470 for l_pgm in c_get_pgm loop
1471 --
1472 open c_get_pgm_extra_info_cvg(l_pgm.pgm_id);
1473 fetch c_get_pgm_extra_info_cvg into l_cvg_adjust;
1474 if c_get_pgm_extra_info_cvg%found then
1475 --
1476 if l_cvg_adjust = 'Y' then
1477 --
1478 hr_utility.set_location('l_cvg_adjust '||l_cvg_adjust,44333);
1479 --
1480 -- Get cvg end dt
1481 --
1482 for l_get_elctbl_chc_for_cvg in c_get_elctbl_chc_for_cvg loop
1483 --
1484 -- Get all results that were de-enrolled for the event.
1485 --
1486 for l_get_enrt_rslts_for_pen in c_get_enrt_rslts_for_pen(l_get_elctbl_chc_for_cvg.enrt_cvg_strt_dt
1487 ,l_get_elctbl_chc_for_cvg.ptip_id ) loop
1488 hr_utility.set_location('Adjusting Coverage for '||l_get_elctbl_chc_for_cvg.enrt_cvg_strt_dt,44333);
1489 --
1490 open c_prtt_enrt_rslt_adj(p_per_in_ler_id,l_get_enrt_rslts_for_pen.prtt_enrt_rslt_id);
1491 fetch c_prtt_enrt_rslt_adj into l_exists;
1492 if c_prtt_enrt_rslt_adj%notfound then
1493 insert into BEN_LE_CLSN_N_RSTR (
1494 BKUP_TBL_TYP_CD,
1495 BKUP_TBL_ID,
1496 per_in_ler_id,
1497 person_id,
1498 ENRT_CVG_THRU_DT,
1499 business_group_id,
1500 object_version_number)
1501 values (
1502 'BEN_PRTT_ENRT_RSLT_F_ADJ',
1503 l_get_enrt_rslts_for_pen.prtt_enrt_rslt_id,
1504 p_per_in_ler_id,
1505 l_get_enrt_rslts_for_pen.person_id,
1506 l_get_enrt_rslts_for_pen.enrt_cvg_thru_dt,
1507 p_business_group_id,
1508 l_get_enrt_rslts_for_pen.object_version_number
1509 );
1510 end if;
1511 close c_prtt_enrt_rslt_adj;
1512 --
1513 ben_prtt_enrt_result_api.update_prtt_enrt_result
1514 (p_validate => FALSE,
1515 p_prtt_enrt_rslt_id => l_get_enrt_rslts_for_pen.prtt_enrt_rslt_id,
1516 p_effective_start_date => l_effective_start_date,
1517 p_effective_end_date => l_effective_end_date,
1518 p_business_group_id => p_business_group_id,
1519 p_object_version_number => l_get_enrt_rslts_for_pen.object_version_number,
1520 p_effective_date => p_effective_date,
1521 p_datetrack_mode => hr_api.g_correction,
1522 p_multi_row_validate => FALSE,
1523 p_enrt_cvg_thru_dt => l_get_elctbl_chc_for_cvg.enrt_cvg_strt_dt - 1
1524 );
1525 end loop; -- c_get_enrt_rslts_for_pen
1526 end loop; -- c_get_elctbl_chc_for_cvg
1527 end if; -- l_cvg_adjust = 'Y'
1528 end if; -- c_get_pgm_extra_info_cvg
1529 close c_get_pgm_extra_info_cvg;
1530 end loop; -- c_get_pgm
1531 --
1532 -- End bug 7206471
1533 end if;
1534 close c_get_prior_per_in_ler;*/
1535 --
1536 -- bug 6127624
1537 end if; -- end if csr_electable_epe
1538 close csr_electable_epes;
1539 hr_utility.set_location ('Leaving '||g_package, 10);
1540 end;
1541 --
1542 end ben_reopen_ended_results;