[Home] [Help]
666: epe.PGM_ID,
667: epe.PL_TYP_ID,
668: epe.PER_IN_LER_ID,
669: nvl(epe.prtt_enrt_rslt_id,-1) prtt_enrt_rslt_id
670: from ben_elig_per_elctbl_chc epe
671: where epe.per_in_ler_id = p_per_in_ler_id
672: and epe.business_group_id = p_business_group_id
673: and epe.pl_id = p_pl_id
674: and epe.oipl_id is null
754: epe.pgm_id,
755: epe.pl_typ_id,
756: epe.pl_id,
757: epe.fonm_cvg_strt_dt
758: from ben_elig_per_elctbl_chc epe,
759: ben_per_in_ler pil,
760: ben_pl_f pl,
761: ben_pil_elctbl_chc_popl pel /* bug 10262697 */
762: where epe.PER_IN_LER_ID = p_per_in_ler_id
1679: and subj_to_imptd_incm_typ_cd is not null
1680: union
1681: select pen.prtt_enrt_rslt_id, pen.enrt_cvg_strt_Dt,pl.subj_to_imptd_incm_typ_cd
1682: from ben_prtt_enrt_rslt_f pen,
1683: ben_elig_per_elctbl_chc epe,
1684: ben_pl_f pl,
1685: ben_per_in_ler pil
1686: where epe.per_in_ler_id <> pen.per_in_ler_id
1687: and pen.pl_id = pl.pl_id
2108: --
2109: cursor c_imp_plan_exists is
2110: select 'Y'
2111: from ben_pl_f pln,
2112: ben_elig_per_elctbl_chc epe
2113: where pln.subj_to_imptd_incm_typ_cd is not null
2114: and pln.pl_id = epe.pl_id
2115: and epe.per_in_ler_id = p_per_in_ler_id
2116: and pln.pl_stat_cd = 'A'
2126: and pen.comp_lvl_cd = 'PLANIMP'
2127: and pen.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')
2128: and pen.prtt_enrt_rslt_stat_cd is null
2129: and pen.enrt_cvg_thru_dt = to_date('31-12-4712','dd-mm-yyyy')
2130: and exists (select null from ben_elig_per_elctbl_chc epe
2131: where pen.pl_id = epe.pl_id
2132: and epe.per_in_ler_id = p_per_in_ler_id);
2133: --
2134: l_exists varchar2(30) := 'N';
2154: and pl.imptd_incm_calc_cd = p_imptd_incm_calc_cd
2155: and pl.pl_stat_cd = 'A'
2156: and pen.enrt_cvg_strt_dt between pl.effective_start_date and pl.effective_end_date
2157: and not exists (select 1
2158: from ben_elig_per_elctbl_chc epe
2159: where epe.pl_id = pen.pl_id
2160: and epe.pgm_id = pen.pgm_id
2161: and epe.per_in_ler_id = p_per_in_ler_id);
2162: