[Home] [Help]
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
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
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
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;
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
374: ;
375: --
376: cursor c_previous_pgm_ptip_result is
377: select pen.prtt_enrt_rslt_id,pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
378: from ben_prtt_enrt_rslt_f pen
379: where pen.pl_typ_id = l_pl_typ_id
380: and pen.person_id = p_person_id
381: and pen.per_in_ler_id = p_per_in_ler_id
382: and pen.effective_end_date = hr_api.g_eot
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
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
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
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)
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
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
974: and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
975: and enb.elig_per_elctbl_chc_id (+) = epe.elig_per_elctbl_chc_id
976: and epe.prtt_enrt_rslt_id is NOT NULL
977: and not exists (select null
978: from ben_prtt_enrt_rslt_f pen
979: where pen.pl_id = epe.pl_id
980: and pen.prtt_enrt_rslt_stat_cd IS NULL
981: and pen.per_in_ler_id = epe.per_in_ler_id
982: /* Added the below condition and commented the code for Bug 7426609 */
985: epe.prtt_enrt_rslt_id)*/
986: and pen.enrt_cvg_thru_dt = hr_api.g_eot
987: and pen.effective_end_date = hr_api.g_eot)
988: and exists (select null
989: from ben_prtt_enrt_rslt_f pen
990: where pen.pl_id = epe.pl_id
991: and pen.prtt_enrt_rslt_stat_cd IS NULL
992: and pen.per_in_ler_id <> epe.per_in_ler_id
993: and pen.prtt_enrt_rslt_id = NVL(enb.prtt_enrt_rslt_id,
1015: and pil.per_in_ler_stat_cd = 'STRTD'
1016: and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
1017: and (epe.pgm_id is not null
1018: and not exists(select null
1019: from ben_prtt_enrt_rslt_f pen
1020: where pen.pgm_id = epe.pgm_id
1021: and pen.per_in_ler_id = epe.per_in_ler_id
1022: and pen.enrt_cvg_thru_dt = hr_api.g_eot
1023: and pen.effective_end_date = hr_api.g_eot)
1022: and pen.enrt_cvg_thru_dt = hr_api.g_eot
1023: and pen.effective_end_date = hr_api.g_eot)
1024: or epe.pl_id is not null
1025: and not exists(select null
1026: from ben_prtt_enrt_rslt_f pen
1027: where pen.pl_id = epe.pl_id
1028: and pen.per_in_ler_id = epe.per_in_ler_id
1029: and pen.enrt_cvg_thru_dt = hr_api.g_eot
1030: and pen.effective_end_date = hr_api.g_eot));
1932: --
1933: cursor c_pen_sus is
1934: select pen.prtt_enrt_rslt_id,
1935: pen.per_in_ler_id
1936: from ben_prtt_enrt_rslt_f pen,
1937: ben_per_in_ler pil
1938: where pen.sspndd_flag = 'Y'
1939: and pen.per_in_ler_id <> pil.per_in_ler_id
1940: and pil.per_in_ler_id = p_per_in_ler_id