[Home] [Help]
9: g_bckt_csd_lf_evt_ocrd_dt date;
10: --
11: type g_pgm_rec is record
12: (pgm_id ben_pgm_f.pgm_id%type,
13: enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type,
14: multi_row_edit_done boolean,
15: non_automatics_flag boolean,
16: max_enrt_esd date);
17: --
16: max_enrt_esd date);
17: --
18: type g_pl_rec is record
19: (pl_id ben_pl_f.pl_id%type,
20: enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type,
21: multi_row_edit_done boolean,
22: max_enrt_esd date);
23: --
24: type g_enrt_rec is record
21: multi_row_edit_done boolean,
22: max_enrt_esd date);
23: --
24: type g_enrt_rec is record
25: (prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
26: bckdt_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
27: bckdt_enrt_ovridn_flag varchar2(1),
28: bckdt_enrt_cvg_strt_dt date,
29: bckdt_enrt_cvg_thru_dt date,
22: max_enrt_esd date);
23: --
24: type g_enrt_rec is record
25: (prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
26: bckdt_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
27: bckdt_enrt_ovridn_flag varchar2(1),
28: bckdt_enrt_cvg_strt_dt date,
29: bckdt_enrt_cvg_thru_dt date,
30: enrt_ovrid_thru_dt date,
31: --Bug 13355114
32: bckdt_sspndd_flag varchar2(1),
33: bckdt_RPLCS_SSPNDD_RSLT_ID number,
34: --Bug 13355114
35: enrt_ovrid_rsn_cd ben_prtt_enrt_rslt_f.enrt_ovrid_rsn_cd%type,
36: g_sys_date date,
37: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
38: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
39: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
33: bckdt_RPLCS_SSPNDD_RSLT_ID number,
34: --Bug 13355114
35: enrt_ovrid_rsn_cd ben_prtt_enrt_rslt_f.enrt_ovrid_rsn_cd%type,
36: g_sys_date date,
37: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
38: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
39: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
40: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
41: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
34: --Bug 13355114
35: enrt_ovrid_rsn_cd ben_prtt_enrt_rslt_f.enrt_ovrid_rsn_cd%type,
36: g_sys_date date,
37: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
38: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
39: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
40: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
41: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
42: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
35: enrt_ovrid_rsn_cd ben_prtt_enrt_rslt_f.enrt_ovrid_rsn_cd%type,
36: g_sys_date date,
37: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
38: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
39: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
40: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
41: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
42: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
43: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
36: g_sys_date date,
37: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
38: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
39: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
40: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
41: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
42: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
43: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
44: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
37: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
38: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
39: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
40: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
41: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
42: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
43: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
44: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
45: ler_id ben_ler_f.ler_id%type,
38: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
39: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
40: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
41: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
42: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
43: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
44: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
45: ler_id ben_ler_f.ler_id%type,
46: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
39: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
40: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
41: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
42: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
43: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
44: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
45: ler_id ben_ler_f.ler_id%type,
46: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
47: dpnt_cvg_strt_dt_cd ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_cd%type,
40: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
41: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
42: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
43: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
44: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
45: ler_id ben_ler_f.ler_id%type,
46: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
47: dpnt_cvg_strt_dt_cd ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_cd%type,
48: dpnt_cvg_strt_dt_rl ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_rl%type,
45: ler_id ben_ler_f.ler_id%type,
46: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
47: dpnt_cvg_strt_dt_cd ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_cd%type,
48: dpnt_cvg_strt_dt_rl ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_rl%type,
49: effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%type,
50: reinstate_flag varchar2(1) DEFAULT 'N',
51: enrt_mthd_cd varchar2(1) -- Bug 9045559
52: );
53: --
141: pen.REQUEST_ID,
142: pen.RPLCS_SSPNDD_RSLT_ID,
143: pen.SSPNDD_FLAG,
144: pen.UOM
145: from ben_prtt_enrt_rslt_f pen,
146: ben_per_in_ler pil,
147: ben_pil_elctbl_chc_popl pel --Added for Bug 4423161
148: where pil.per_in_ler_id = c_bckdt_per_in_ler_id
149: and pil.person_id = c_person_id
162: and ( c_pl_id is null or
163: pen.pl_id = c_pl_id )
164: and (pen.effective_end_date = hr_api.g_eot or
165: pen.effective_end_date = (select max(effective_end_date)
166: from ben_prtt_enrt_rslt_f
167: where prtt_enrt_rslt_id =
168: pen.prtt_enrt_rslt_id))
169: and (pen.enrt_cvg_thru_dt is null or
170: pen.enrt_cvg_thru_dt = hr_api.g_eot
171: )
172: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
173: and pen.prtt_enrt_rslt_id not in (
174: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
175: from ben_prtt_enrt_rslt_f pen_inner,
176: ben_per_in_ler pil_inner
177: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
178: and pil_inner.person_id = c_person_id
179: and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
187: ben_per_in_ler pil_inner
188: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
189: and pil_inner.person_id = c_person_id
190: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
191: and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
192: and (pen_inner.enrt_cvg_thru_dt is null or
193: pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
194: )
195: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
286: and (pel.dflt_asnd_dt is not null or
287: pel.elcns_made_dt is not null or
288: pel.auto_asnd_dt is not null) -- Bug 8305552, Check for Automatic Enrolled Plans
289: AND pil.per_in_ler_id = pen.per_in_ler_id
290: and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
291: --To Handle Programs and Plans not in Program
292: and ((pen.pgm_id is null and c_pgm_id is null) or
293: pen.pgm_id = c_pgm_id )
294: and ( c_pl_id is null or
299: )
300: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
301: and pen.bkup_tbl_id not in (
302: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
303: from ben_prtt_enrt_rslt_f pen_inner,
304: ben_per_in_ler pil_inner
305: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
306: and pil_inner.person_id = c_person_id
307: and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
315: ben_per_in_ler pil_inner
316: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
317: and pil_inner.person_id = c_person_id
318: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
319: and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
320: and (pen_inner.enrt_cvg_thru_dt is null or
321: pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
322: )
323: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
352: pen.PRTT_ENRT_RSLT_STAT_CD,
353: pen.PTIP_ID,
354: pen.RPLCS_SSPNDD_RSLT_ID,
355: pen.SSPNDD_FLAG
356: from ben_prtt_enrt_rslt_f pen,
357: ben_per_in_ler pil,
358: ben_pil_elctbl_chc_popl pel --Added for Bug 4423161
359: where pil.per_in_ler_id = c_bckdt_per_in_ler_id
360: and pil.person_id = c_person_id
362: and pil.per_in_ler_id = pel.per_in_ler_id
363: and (pel.dflt_asnd_dt is null and
364: pel.elcns_made_dt is null) -- Bug 8305552, Check for Automatic Enrolled Plans
365: and exists
366: (select '1' from ben_prtt_enrt_rslt_f epen where
367: epen.per_in_ler_id = c_bckdt_per_in_ler_id
368: and epen.person_id = c_person_id
369: and (epen.enrt_cvg_thru_dt is null or
370: epen.enrt_cvg_thru_dt = hr_api.g_eot)
371: and epen.effective_end_date = hr_api.g_eot)
372: --To Handle Programs and Plans not in Program
373: and (pen.effective_end_date = hr_api.g_eot or
374: pen.effective_end_date = (select max(effective_end_date)
375: from ben_prtt_enrt_rslt_f
376: where prtt_enrt_rslt_id =
377: pen.prtt_enrt_rslt_id))
378: and (pen.enrt_cvg_thru_dt is null or
379: pen.enrt_cvg_thru_dt = hr_api.g_eot
380: )
381: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
382: and pen.prtt_enrt_rslt_id not in (
383: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
384: from ben_prtt_enrt_rslt_f pen_inner,
385: ben_per_in_ler pil_inner
386: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
387: and pil_inner.person_id = c_person_id
388: and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
396: ben_per_in_ler pil_inner
397: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
398: and pil_inner.person_id = c_person_id
399: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
400: and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
401: and (pen_inner.enrt_cvg_thru_dt is null or
402: pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
403: )
404: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
431: and pil.per_in_ler_id = pel.per_in_ler_id
432: and (pel.dflt_asnd_dt is null and
433: pel.elcns_made_dt is null) -- Bug 8305552, Check for Automatic Enrolled Plans
434: and exists
435: (select '1' from ben_prtt_enrt_rslt_f epen where
436: epen.per_in_ler_id = c_bckdt_per_in_ler_id
437: and epen.person_id = c_person_id
438: and (epen.enrt_cvg_thru_dt is null or
439: epen.enrt_cvg_thru_dt = hr_api.g_eot)
438: and (epen.enrt_cvg_thru_dt is null or
439: epen.enrt_cvg_thru_dt = hr_api.g_eot)
440: and epen.effective_end_date = hr_api.g_eot)
441: AND pil.per_in_ler_id = pen.per_in_ler_id
442: and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
443: and ((pen.enrt_cvg_thru_dt is null or
444: pen.enrt_cvg_thru_dt = hr_api.g_eot) and
445: pen.effective_end_date = hr_api.g_eot
446: )
446: )
447: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
448: and pen.bkup_tbl_id not in (
449: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
450: from ben_prtt_enrt_rslt_f pen_inner,
451: ben_per_in_ler pil_inner
452: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
453: and pil_inner.person_id = c_person_id
454: and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
462: ben_per_in_ler pil_inner
463: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
464: and pil_inner.person_id = c_person_id
465: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
466: and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
467: and (pen_inner.enrt_cvg_thru_dt is null or
468: pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
469: )
470: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
726: -- life events.
727: --
728: cursor c_enrt_rslt_exists is
729: select 'Y'
730: from ben_prtt_enrt_rslt_f pen,
731: ben_per_in_ler pil
732: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
733: and pil.business_group_id = p_business_group_id
734: and pen.per_in_ler_id = pil.per_in_ler_id
778: --
779: -- Following are the tables whose data will be compared to
780: -- find any differences exists between the two runs of same ler.
781: --
782: -- ben_prtt_enrt_rslt_f
783: --
784: cursor c_pen_dat(p_pil_id number) is
785: select
786: pen.OIPL_ID,
786: pen.OIPL_ID,
787: pen.PGM_ID,
788: pen.PL_ID,
789: pen.SSPNDD_FLAG
790: from ben_prtt_enrt_rslt_f pen,
791: ben_per_in_ler pil
792: where pil.per_in_ler_id = p_pil_id
793: and pil.person_id = p_person_id
794: and pil.business_group_id = p_business_group_id
1005: l_prev_per_in_ler_id1 number;
1006:
1007: cursor c_ele_md_for_inter_pil is
1008: select 'Y'
1009: from ben_prtt_enrt_rslt_f pen,
1010: ben_ler_f ler
1011: where pen.per_in_ler_id not in (p_per_in_ler_id,p_bckdt_per_in_ler_id)
1012: /* Bug 8305552: Added 'not in (p_per_in_ler_id)'. Do not check elections for the current LifeEvent,because for Automatic enrollments
1013: record will be present during validation of the cursor*/
3253: --
3254: -- Following are the tables whose data will be compared to
3255: -- find any differences exists between the two runs of same ler.
3256: --
3257: -- ben_prtt_enrt_rslt_f
3258: -- ben_prtt_rt_val
3259: --
3260: l_bckdt_pen_cnt number := 0;
3261: l_bckdt_pen_cnt_temp number := 0;
3340: pen.REQUEST_ID,
3341: pen.RPLCS_SSPNDD_RSLT_ID,
3342: pen.SSPNDD_FLAG,
3343: pen.UOM
3344: from ben_prtt_enrt_rslt_f pen,
3345: ben_per_in_ler pil
3346: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
3347: and pil.person_id = p_person_id
3348: and pil.business_group_id = p_business_group_id
3432: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
3433: and pil.person_id = p_person_id
3434: and pil.business_group_id = p_business_group_id
3435: AND pil.per_in_ler_id = pen.per_in_ler_id
3436: and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
3437: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP');
3438: -- and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
3439: --
3440: cursor c_curr_pen_dat is
3438: -- and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
3439: --
3440: cursor c_curr_pen_dat is
3441: select pen.*
3442: from ben_prtt_enrt_rslt_f pen,
3443: ben_per_in_ler pil
3444: where pil.per_in_ler_id = p_per_in_ler_id
3445: and pil.person_id = p_person_id
3446: and pil.business_group_id = p_business_group_id
3467: -- Bug 1266433 : Do not consider the results of flex credits and
3468: -- imputed income as they are not considered in c_curr_pen_dat
3469: --
3470: select count(*) into l_bckdt_pen_cnt_temp
3471: from ben_prtt_enrt_rslt_f pen,
3472: ben_per_in_ler pil
3473: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
3474: and pil.person_id = p_person_id
3475: and pil.business_group_id = p_business_group_id
3483: and pil.person_id = p_person_id
3484: and pil.business_group_id = p_business_group_id
3485: AND pil.per_in_ler_id = pen.per_in_ler_id
3486: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
3487: and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F';
3488: --
3489: l_bckdt_pen_cnt := l_bckdt_pen_cnt_temp + l_bckdt_pen_cnt;
3490: l_curr_pen_table.delete;
3491: l_next_row := nvl(l_curr_pen_table.LAST, 0) + 1;
3846: pen.PGM_ID,
3847: pen.PL_ID,
3848: pen.PL_TYP_ID,
3849: pen.PTIP_ID
3850: from ben_prtt_enrt_rslt_f pen,
3851: ben_per_in_ler pil
3852: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
3853: and pil.person_id = p_person_id
3854: and pil.business_group_id = p_business_group_id
3868: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
3869: and pil.person_id = p_person_id
3870: and pil.business_group_id = p_business_group_id
3871: AND pil.per_in_ler_id = pen.per_in_ler_id
3872: and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
3873: and pen.comp_lvl_cd = 'PLANFC'
3874: order by 1; -- pen.effective_end_date; -- Low to High
3875: --
3876: cursor c_curr_pen(cp_pl_id in number, cp_oipl_id in number,
3883: pen.PL_ID,
3884: pen.PL_TYP_ID,
3885: pen.PTIP_ID,
3886: pen.effective_start_date
3887: from ben_prtt_enrt_rslt_f pen,
3888: ben_per_in_ler pil
3889: where pil.per_in_ler_id = p_per_in_ler_id
3890: and pil.person_id = p_person_id
3891: and pil.business_group_id = p_business_group_id
5034: l_epe_dpnt_rec c_epe_dpnt%rowtype;
5035: -- bug 5895645
5036: cursor get_dt_c is
5037: select enrt_cvg_strt_dt
5038: from ben_prtt_enrt_rslt_f
5039: where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5040: and p_effective_date between effective_start_date
5041: and effective_end_date;
5042: -- end 5895645
6094: pen.REQUEST_ID,
6095: pen.RPLCS_SSPNDD_RSLT_ID,
6096: pen.SSPNDD_FLAG,
6097: pen.UOM
6098: from ben_prtt_enrt_rslt_f pen,
6099: ben_per_in_ler pil
6100: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
6101: and pil.person_id = p_person_id
6102: and pil.business_group_id = p_business_group_id
6103: and pil.per_in_ler_id = pen.per_in_ler_id
6104: /*Pick up both end-dated and non-end-dated results*/
6105: and (pen.effective_end_date = hr_api.g_eot or
6106: pen.effective_end_date = (select max(effective_end_date)
6107: from ben_prtt_enrt_rslt_f
6108: where prtt_enrt_rslt_id =
6109: pen.prtt_enrt_rslt_id))
6110: and (pen.enrt_cvg_thru_dt is null or
6111: pen.enrt_cvg_thru_dt = hr_api.g_eot
6112: )
6113: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
6114: and pen.prtt_enrt_rslt_id not in (
6115: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
6116: from ben_prtt_enrt_rslt_f pen_inner,
6117: ben_per_in_ler pil_inner
6118: where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
6119: and pil_inner.person_id = p_person_id
6120: and pil_inner.business_group_id = p_business_group_id
6130: where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
6131: and pil_inner.person_id = p_person_id
6132: and pil_inner.business_group_id = p_business_group_id
6133: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
6134: and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
6135: and (pen_inner.enrt_cvg_thru_dt is null or
6136: pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
6137: )
6138: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
6221: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
6222: and pil.person_id = p_person_id
6223: and pil.business_group_id = p_business_group_id
6224: AND pil.per_in_ler_id = pen.per_in_ler_id
6225: and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
6226: and ((pen.enrt_cvg_thru_dt is null or
6227: pen.enrt_cvg_thru_dt = hr_api.g_eot) and
6228: --bug#2604375 - added to control updated result rows for the same per_in_ler
6229: pen.effective_end_date = hr_api.g_eot
6230: )
6231: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
6232: and pen.bkup_tbl_id not in (
6233: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
6234: from ben_prtt_enrt_rslt_f pen_inner,
6235: ben_per_in_ler pil_inner
6236: where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
6237: and pil_inner.person_id = p_person_id
6238: and pil_inner.business_group_id = p_business_group_id
6248: where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
6249: and pil_inner.person_id = p_person_id
6250: and pil_inner.business_group_id = p_business_group_id
6251: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
6252: and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
6253: and (pen_inner.enrt_cvg_thru_dt is null or
6254: pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
6255: )
6256: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
6344: l_count number;
6345: --
6346: type pgm_rec is record
6347: (pgm_id ben_pgm_f.pgm_id%type,
6348: enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type,
6349: multi_row_edit_done boolean,
6350: non_automatics_flag boolean,
6351: max_enrt_esd date);
6352: --
6351: max_enrt_esd date);
6352: --
6353: type pl_rec is record
6354: (pl_id ben_pl_f.pl_id%type,
6355: enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type,
6356: multi_row_edit_done boolean,
6357: max_enrt_esd date);
6358: --
6359: type enrt_rec is record
6356: multi_row_edit_done boolean,
6357: max_enrt_esd date);
6358: --
6359: type enrt_rec is record
6360: (prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
6361: bckdt_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
6362: bckdt_enrt_ovridn_flag varchar2(1),
6363: bckdt_enrt_cvg_strt_dt date,
6364: bckdt_enrt_cvg_thru_dt date,
6357: max_enrt_esd date);
6358: --
6359: type enrt_rec is record
6360: (prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
6361: bckdt_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
6362: bckdt_enrt_ovridn_flag varchar2(1),
6363: bckdt_enrt_cvg_strt_dt date,
6364: bckdt_enrt_cvg_thru_dt date,
6365: g_sys_date date,
6362: bckdt_enrt_ovridn_flag varchar2(1),
6363: bckdt_enrt_cvg_strt_dt date,
6364: bckdt_enrt_cvg_thru_dt date,
6365: g_sys_date date,
6366: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
6367: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
6368: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
6369: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
6370: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
6363: bckdt_enrt_cvg_strt_dt date,
6364: bckdt_enrt_cvg_thru_dt date,
6365: g_sys_date date,
6366: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
6367: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
6368: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
6369: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
6370: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
6371: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
6364: bckdt_enrt_cvg_thru_dt date,
6365: g_sys_date date,
6366: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
6367: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
6368: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
6369: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
6370: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
6371: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
6372: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
6365: g_sys_date date,
6366: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
6367: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
6368: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
6369: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
6370: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
6371: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
6372: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
6373: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
6366: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
6367: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
6368: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
6369: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
6370: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
6371: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
6372: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
6373: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
6374: ler_id ben_ler_f.ler_id%type,
6367: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
6368: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
6369: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
6370: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
6371: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
6372: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
6373: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
6374: ler_id ben_ler_f.ler_id%type,
6375: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
6368: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
6369: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
6370: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
6371: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
6372: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
6373: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
6374: ler_id ben_ler_f.ler_id%type,
6375: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
6376: dpnt_cvg_strt_dt_cd ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_cd%type,
6369: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
6370: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
6371: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
6372: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
6373: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
6374: ler_id ben_ler_f.ler_id%type,
6375: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
6376: dpnt_cvg_strt_dt_cd ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_cd%type,
6377: dpnt_cvg_strt_dt_rl ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_rl%type,
6374: ler_id ben_ler_f.ler_id%type,
6375: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
6376: dpnt_cvg_strt_dt_cd ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_cd%type,
6377: dpnt_cvg_strt_dt_rl ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_rl%type,
6378: effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%type
6379: );
6380: --
6381: type t_pgm_table is table of pgm_rec index by binary_integer;
6382: type t_pl_table is table of pl_rec index by binary_integer;
6403: l_prv_rec_nulls c_prv%rowtype;
6404: --
6405: cursor c_bckt_csd_pen(cv_per_in_ler_id in number) is
6406: select pen.*, pil.lf_evt_ocrd_dt
6407: from ben_prtt_enrt_rslt_f pen,
6408: ben_per_in_ler pil
6409: where pen.per_in_ler_id = cv_per_in_ler_id
6410: and pen.per_in_ler_id = pil.per_in_ler_id
6411: and pen.business_group_id = p_business_group_id
6455: and pdp2.effective_end_date;
6456: --
6457: cursor c_ovn(v_prtt_enrt_rslt_id number) is
6458: select object_version_number
6459: from ben_prtt_enrt_rslt_f
6460: where prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
6461: and effective_end_date = hr_api.g_eot;
6462: --
6463: cursor c_prv_ovn (v_prtt_rt_val_id number) is
8138: l_prv_rec_nulls c_prv%rowtype;
8139: --
8140: cursor c_bckt_csd_pen(cv_per_in_ler_id in number) is
8141: select pen.*, pil.lf_evt_ocrd_dt
8142: from ben_prtt_enrt_rslt_f pen,
8143: ben_per_in_ler pil
8144: where pen.per_in_ler_id = cv_per_in_ler_id
8145: and pen.per_in_ler_id = pil.per_in_ler_id
8146: and pen.business_group_id = p_business_group_id
8211: /* Bug 9307262:Moved the fix done for Bug 7426609 from procedure 'reinstate_the_prev_enrt' to 'reinstate_prev_enrt_for_popl'*/
8212: /*Added for Bug 7426609*/
8213: cursor c_get_epe_id(c_prtt_enrt_rslt_id number,c_per_in_ler_id number) is
8214: select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc epe,
8215: ben_prtt_enrt_rslt_f pen
8216: where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
8217: and pen.per_in_ler_id = c_per_in_ler_id
8218: and epe.per_in_ler_id=pen.per_in_ler_id
8219: and epe.pl_id=pen.pl_id
8274: cursor c_prev_pil_epe_id( c_elig_per_elctbl_chc_id number,c_per_in_ler_id number) is
8275: select prevepe.elig_per_elctbl_chc_id
8276: from ben_elig_per_elctbl_chc prevepe,
8277: ben_elig_per_elctbl_chc newepe,
8278: ben_prtt_enrt_rslt_f prevpen
8279: where newepe.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
8280: and prevepe.per_in_ler_id = c_per_in_ler_id
8281: and newepe.pl_id = prevepe.pl_id
8282: and nvl(prevepe.pgm_id,-1) = nvl(newepe.pgm_id,-1)
8305: pen.bnft_amt,
8306: epe.object_version_number
8307: from
8308: ben_elig_per_elctbl_chc epe,
8309: ben_prtt_enrt_rslt_f pen,
8310: ben_enrt_bnft bnft
8311: where pen.per_in_ler_id = c_prev_pil_id
8312: and pen.pl_id = epe.pl_id
8313: and pen.pgm_id = epe.pgm_id
8317: and pen.prtt_enrt_rslt_stat_cd is null
8318: and bnft.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
8319: and exists
8320: (select '1' from
8321: ben_prtt_enrt_rslt_f pen1
8322: where pen1.RPLCS_SSPNDD_RSLT_ID = pen.prtt_enrt_rslt_id
8323: and pen1.per_in_ler_id = pen.per_in_ler_id
8324: and pen1.prtt_enrt_rslt_stat_cd is null
8325: and pen.pl_id = pen1.pl_id
8789: -- changed 7176884 begin
8790: delete from ben_le_clsn_n_rstr
8791: where bkup_tbl_id = l_bckdt_pen_rec.prtt_enrt_rslt_id
8792: and per_in_ler_id = p_bckdt_per_in_ler_id
8793: and bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
8794: and person_id = p_person_id;
8795: -- changed 7176884 end
8796: --
8797: l_prtt_rt_val_table(1) := l_prtt_rt_val_id1;
9067: l_voidd_dt date;
9068: l_esd_out date;
9069: l_eed_out date;
9070: l_ovn number(15);
9071: l_enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type;
9072:
9073: /* Bug 10072439: For the backed out Life event if elections are made through
9074: Self Service, on reprocessing the backed out life event, communications of
9075: triggers configured for Web Enrollments should be reinstated.*/
9091: cursor c_interim_enrt(c_sus_pen_id number,
9092: c_bckdt_prtt_enrt_rslt_id number,
9093: c_bckdt_per_in_ler_id number ) is
9094: select pen_int.* from
9095: ben_prtt_enrt_rslt_f pen_curr,
9096: ben_prtt_enrt_rslt_f pen_int
9097: where pen_curr.prtt_enrt_rslt_id = c_sus_pen_id
9098: and pen_curr.prtt_enrt_rslt_stat_cd is NULL
9099: and pen_curr.per_in_ler_id = p_per_in_ler_id
9092: c_bckdt_prtt_enrt_rslt_id number,
9093: c_bckdt_per_in_ler_id number ) is
9094: select pen_int.* from
9095: ben_prtt_enrt_rslt_f pen_curr,
9096: ben_prtt_enrt_rslt_f pen_int
9097: where pen_curr.prtt_enrt_rslt_id = c_sus_pen_id
9098: and pen_curr.prtt_enrt_rslt_stat_cd is NULL
9099: and pen_curr.per_in_ler_id = p_per_in_ler_id
9100: and pen_curr.SSPNDD_FLAG = 'Y'
9104: and pen_curr.RPLCS_SSPNDD_RSLT_ID = pen_int.prtt_enrt_rslt_id
9105: and pen_curr.effective_end_date = hr_api.g_eot
9106: and pen_int.effective_end_date = hr_api.g_eot
9107: and exists
9108: (select '1' from ben_prtt_enrt_rslt_f pen_bckdt,
9109: ben_prtt_enrt_rslt_f pen_int_bckdt
9110: where pen_bckdt.prtt_enrt_rslt_id = c_bckdt_prtt_enrt_rslt_id
9111: and pen_bckdt.per_in_ler_id = c_bckdt_per_in_ler_id
9112: and pen_bckdt.SSPNDD_FLAG = 'Y'
9105: and pen_curr.effective_end_date = hr_api.g_eot
9106: and pen_int.effective_end_date = hr_api.g_eot
9107: and exists
9108: (select '1' from ben_prtt_enrt_rslt_f pen_bckdt,
9109: ben_prtt_enrt_rslt_f pen_int_bckdt
9110: where pen_bckdt.prtt_enrt_rslt_id = c_bckdt_prtt_enrt_rslt_id
9111: and pen_bckdt.per_in_ler_id = c_bckdt_per_in_ler_id
9112: and pen_bckdt.SSPNDD_FLAG = 'Y'
9113: and pen_bckdt.RPLCS_SSPNDD_RSLT_ID is not NULL
9474: and pdp2.effective_end_date;
9475: --
9476: cursor c_ovn(v_prtt_enrt_rslt_id number) is
9477: select object_version_number
9478: from ben_prtt_enrt_rslt_f
9479: where prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
9480: and effective_end_date = hr_api.g_eot;
9481: --
9482: cursor c_epe_enrt_rt(v_elig_per_elctbl_chc_id number,
9497: and ecr.acty_base_rt_id = v_acty_base_rt_id ;
9498: --
9499: cursor c_flx_pen(c_person_id number,c_per_in_ler_id number,c_effective_date date ) is
9500: select pen.prtt_enrt_rslt_id,pen.pgm_id,pen.pl_id
9501: from ben_prtt_enrt_rslt_f pen,
9502: ben_pl_f pln
9503: where pen.person_id = c_person_id
9504: and pen.per_in_ler_id = c_per_in_ler_id
9505: and pen.pl_id = pln.pl_id
9965:
9966:
9967: cursor c_exp_enrt_exists(c_per_in_ler_id number,c_pgm_id number,c_pl_typ_id number) is
9968: select 'Y' from
9969: ben_prtt_enrt_rslt_f pen
9970: where pen.per_in_ler_id = c_per_in_ler_id
9971: and nvl(pen.pgm_id,-1) = nvl(c_pgm_id,-1)
9972: and pen.pl_typ_id = c_pl_typ_id
9973: and pen.enrt_mthd_cd = 'E'
9976: and pen.enrt_cvg_thru_dt >= pen.enrt_cvg_strt_dt ;
9977:
9978: cursor c_chk_exp_inter_pil is
9979: select 'Y'
9980: from ben_prtt_enrt_rslt_f pen,
9981: ben_ler_f ler
9982: where pen.per_in_ler_id not in (p_per_in_ler_id,p_bckdt_per_in_ler_id)
9983: and pen.person_id = p_person_id
9984: and pen.prtt_enrt_rslt_stat_cd is null
9993: /* Bug 12688164 : To check whether elections are made for intervening LE, enrollment should
9994: not be either suspended or interim*/
9995: and nvl(pen.sspndd_flag,'N') = 'N'
9996: and not exists
9997: (select '1' from ben_prtt_enrt_rslt_f pen1
9998: where pen1.RPLCS_SSPNDD_RSLT_ID = pen.prtt_enrt_rslt_id
9999: and pen1.per_in_ler_id = pen.per_in_ler_id)
10000: and exists
10001: (select 'Y' from ben_elig_per_elctbl_chc epe1
10129: WHERE pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
10130: AND pil_inner.person_id = p_person_id
10131: AND pil_inner.business_group_id = p_business_group_id
10132: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
10133: AND pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
10134: AND ( (pen_inner.enrt_cvg_thru_dt IS NOT NULL and pen_inner.enrt_cvg_thru_dt <> hr_api.g_eot )
10135: and pen_inner.effective_end_date = hr_api.g_eot
10136: )
10137: AND pen_inner.comp_lvl_cd NOT IN('PLANFC','PLANIMP');
11532: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
11533: and pil.person_id = p_person_id
11534: and pil.business_group_id = p_business_group_id
11535: AND pil.per_in_ler_id = pen.per_in_ler_id
11536: and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
11537: and ((pen.enrt_cvg_thru_dt is null or
11538: pen.enrt_cvg_thru_dt = hr_api.g_eot) and
11539: --bug#2604375 - added to control updated result rows for the same per_in_ler
11540: pen.effective_end_date = hr_api.g_eot
11541: )
11542: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
11543: and pen.bkup_tbl_id not in (
11544: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
11545: from ben_prtt_enrt_rslt_f pen_inner,
11546: ben_per_in_ler pil_inner
11547: where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
11548: and pil_inner.person_id = p_person_id
11549: and pil_inner.business_group_id = p_business_group_id
11559: where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
11560: and pil_inner.person_id = p_person_id
11561: and pil_inner.business_group_id = p_business_group_id
11562: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
11563: and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
11564: and (pen_inner.enrt_cvg_thru_dt is null or
11565: pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
11566: )
11567: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
11660: l_count number;
11661: --
11662: type pgm_rec is record
11663: (pgm_id ben_pgm_f.pgm_id%type,
11664: enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type,
11665: multi_row_edit_done boolean,
11666: non_automatics_flag boolean,
11667: max_enrt_esd date);
11668: --
11667: max_enrt_esd date);
11668: --
11669: type pl_rec is record
11670: (pl_id ben_pl_f.pl_id%type,
11671: enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type,
11672: multi_row_edit_done boolean,
11673: max_enrt_esd date);
11674: --
11675: type enrt_rec is record
11672: multi_row_edit_done boolean,
11673: max_enrt_esd date);
11674: --
11675: type enrt_rec is record
11676: (prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
11677: bckdt_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
11678: bckdt_enrt_ovridn_flag varchar2(1),
11679: bckdt_enrt_cvg_strt_dt date,
11680: bckdt_enrt_cvg_thru_dt date,
11673: max_enrt_esd date);
11674: --
11675: type enrt_rec is record
11676: (prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
11677: bckdt_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type,
11678: bckdt_enrt_ovridn_flag varchar2(1),
11679: bckdt_enrt_cvg_strt_dt date,
11680: bckdt_enrt_cvg_thru_dt date,
11681: g_sys_date date,
11678: bckdt_enrt_ovridn_flag varchar2(1),
11679: bckdt_enrt_cvg_strt_dt date,
11680: bckdt_enrt_cvg_thru_dt date,
11681: g_sys_date date,
11682: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
11683: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
11684: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
11685: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
11686: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
11679: bckdt_enrt_cvg_strt_dt date,
11680: bckdt_enrt_cvg_thru_dt date,
11681: g_sys_date date,
11682: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
11683: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
11684: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
11685: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
11686: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
11687: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
11680: bckdt_enrt_cvg_thru_dt date,
11681: g_sys_date date,
11682: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
11683: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
11684: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
11685: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
11686: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
11687: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
11688: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
11681: g_sys_date date,
11682: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
11683: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
11684: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
11685: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
11686: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
11687: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
11688: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
11689: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
11682: pen_ovn_number ben_prtt_enrt_rslt_f.object_version_number%type,
11683: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
11684: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
11685: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
11686: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
11687: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
11688: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
11689: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
11690: ler_id ben_ler_f.ler_id%type,
11683: old_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
11684: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
11685: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
11686: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
11687: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
11688: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
11689: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
11690: ler_id ben_ler_f.ler_id%type,
11691: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
11684: new_pl_id ben_prtt_enrt_rslt_f.pl_id%type,
11685: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
11686: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
11687: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
11688: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
11689: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
11690: ler_id ben_ler_f.ler_id%type,
11691: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
11692: dpnt_cvg_strt_dt_cd ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_cd%type,
11685: old_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
11686: new_oipl_id ben_prtt_enrt_rslt_f.oipl_id%type,
11687: old_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
11688: new_pl_typ_id ben_prtt_enrt_rslt_f.pl_typ_id%type,
11689: pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
11690: ler_id ben_ler_f.ler_id%type,
11691: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
11692: dpnt_cvg_strt_dt_cd ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_cd%type,
11693: dpnt_cvg_strt_dt_rl ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_rl%type,
11690: ler_id ben_ler_f.ler_id%type,
11691: elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type,
11692: dpnt_cvg_strt_dt_cd ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_cd%type,
11693: dpnt_cvg_strt_dt_rl ben_elig_per_elctbl_chc.dpnt_cvg_strt_dt_rl%type,
11694: effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%type
11695: );
11696: --
11697: --
11698: type t_pgm_table is table of pgm_rec index by binary_integer;
11722: l_prv_rec_nulls c_prv%rowtype;
11723: --
11724: cursor c_bckt_csd_pen(cv_per_in_ler_id in number) is
11725: select pen.*, pil.lf_evt_ocrd_dt
11726: from ben_prtt_enrt_rslt_f pen,
11727: ben_per_in_ler pil
11728: where pen.per_in_ler_id = cv_per_in_ler_id
11729: and pen.per_in_ler_id = pil.per_in_ler_id
11730: and pen.business_group_id = p_business_group_id
11774: and pdp2.effective_end_date;
11775: --
11776: cursor c_ovn(v_prtt_enrt_rslt_id number) is
11777: select object_version_number
11778: from ben_prtt_enrt_rslt_f
11779: where prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
11780: and effective_end_date = hr_api.g_eot;
11781: --
11782: cursor c_prv_ovn (v_prtt_rt_val_id number) is
11795: p_pl_id number,
11796: p_oipl_id number,
11797: p_per_in_ler_id number) is -- 8626297
11798: select 'Y'
11799: from ben_prtt_enrt_rslt_f pen
11800: where pen.person_id = p_person_id
11801: and pen.pl_id = p_pl_id
11802: and nvl(pen.oipl_id,-9999) = nvl(p_oipl_id,-9999) --5287988
11803: and pen.per_in_ler_id = p_per_in_ler_id -- 8626297
11807:
11808: /* Bug 8863079: Added cursor c_get_epe_id to determine the interim epe record */
11809: cursor c_get_epe_id(c_prtt_enrt_rslt_id number,c_per_in_ler_id number) is
11810: select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc epe,
11811: ben_prtt_enrt_rslt_f pen
11812: where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
11813: and epe.per_in_ler_id = c_per_in_ler_id
11814: and epe.pl_id = pen.pl_id
11815: and epe.pl_typ_id = pen.pl_typ_id