[Home] [Help]
603: -- and pep.effective_start_date = p_effective_date
604: and pep.elig_flag = p_eligible_flag
605: and exists (select null
606: from ben_prtt_enrt_rslt_f pen,
607: ben_elig_cvrd_dpnt_f pdp
608: where pen.business_group_id = p_business_group_id
609: and pdp.business_group_id = pen.business_group_id
610: and pdp.dpnt_person_id = p_rcpent_person_id
611: and pen.prtt_enrt_rslt_stat_cd is null
634: -- and pep.effective_start_date = p_effective_date
635: and pep.elig_flag = p_eligible_flag
636: and exists (select null
637: from ben_prtt_enrt_rslt_f pen,
638: ben_elig_cvrd_dpnt_f pdp
639: where pen.business_group_id = p_business_group_id
640: and pdp.business_group_id = pen.business_group_id
641: and pdp.dpnt_person_id = p_rcpent_person_id
642: and pen.prtt_enrt_rslt_stat_cd is null
1225: and ctu.effective_end_date
1226: and ctu.cm_typ_id = p_cm_typ_id
1227: and ctu.all_r_any_cd = 'ALL'
1228: and exists (select null
1229: from ben_elig_cvrd_dpnt_f pdp,
1230: ben_per_in_ler pil
1231: where pdp.dpnt_person_id = p_rcpent_person_id
1232: and pdp.business_group_id = pen.business_group_id
1233: and pdp.effective_start_date = p_effective_date
1249: and prv.elctns_made_dt = p_effective_date
1250: and pen.business_group_id = prv.business_group_id
1251: and pen.per_in_ler_id <> prv.per_in_ler_id
1252: and exists (select null
1253: from ben_elig_cvrd_dpnt_f pdp,
1254: ben_per_in_ler pil
1255: where pdp.dpnt_person_id = p_rcpent_person_id
1256: and pdp.business_group_id = pen.business_group_id
1257: and pdp.effective_start_date = p_effective_date
1615: and ctu.cm_typ_id = p_cm_typ_id
1616: and ctu.all_r_any_cd = 'ALL'
1617: and pen.enrt_cvg_thru_dt < hr_api.g_eot
1618: and exists (select null
1619: from ben_elig_cvrd_dpnt_f pdp,
1620: ben_per_in_ler pil
1621: where pdp.dpnt_person_id = p_rcpent_person_id
1622: and pdp.business_group_id = pen.business_group_id
1623: and pdp.effective_start_date = p_effective_date
1633: and pen.prtt_enrt_rslt_stat_cd is null
1634: and pen.per_in_ler_id = p_per_in_ler_id
1635: and pen.enrt_cvg_thru_dt < hr_api.g_eot
1636: and exists (select null
1637: from ben_elig_cvrd_dpnt_f pdp,
1638: ben_per_in_ler pil
1639: where pdp.dpnt_person_id = p_rcpent_person_id
1640: and pdp.business_group_id = pen.business_group_id
1641: and pdp.effective_start_date = p_effective_date
1971: and ctu.effective_end_date
1972: and ctu.cm_typ_id = p_cm_typ_id
1973: and ctu.all_r_any_cd = 'ALL'
1974: and exists (select null
1975: from ben_elig_cvrd_dpnt_f pdp
1976: where pdp.per_in_ler_id = pel.per_in_ler_id
1977: and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
1978: and pdp.dpnt_person_id = p_rcpent_person_id
1979: and pdp.business_group_id = pel.business_group_id
1992: and p_effective_date
1993: between pen.effective_start_date
1994: and pen.effective_end_date
1995: and exists (select null
1996: from ben_elig_cvrd_dpnt_f pdp
1997: where pdp.per_in_ler_id = pil.per_in_ler_id
1998: and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
1999: and pdp.dpnt_person_id = p_rcpent_person_id
2000: and pdp.business_group_id = pil.business_group_id
2245: and ctu.effective_end_date
2246: and ctu.cm_typ_id = p_cm_typ_id
2247: and ctu.all_r_any_cd = 'ALL'
2248: and exists (select null
2249: from ben_elig_cvrd_dpnt_f pdp
2250: where pdp.dpnt_person_id = p_rcpent_person_id
2251: and pdp.business_group_id = pel.business_group_id
2252: and pdp.per_in_ler_id = pel.per_in_ler_id
2253: and p_effective_date between pdp.effective_start_date
2261: and pil.business_group_id = p_business_group_id
2262: and epe.business_group_id = pil.business_group_id
2263: and epe.per_in_ler_id = pil.per_in_ler_id
2264: and exists (select null
2265: from ben_elig_cvrd_dpnt_f pdp
2266: where pdp.dpnt_person_id = p_rcpent_person_id
2267: and pdp.business_group_id = pil.business_group_id
2268: and pdp.per_in_ler_id = pil.per_in_ler_id
2269: and pdp.effective_start_date = p_effective_date);
2653: cursor c_dpnt_always is
2654: select null
2655: from sys.dual
2656: where exists (select null
2657: from ben_elig_cvrd_dpnt_f pdp,
2658: ben_prtt_enrt_rslt_f pen,
2659: ben_prtt_enrt_actn_f pea
2660: where pdp.business_group_id = p_business_group_id
2661: and pdp.per_in_ler_id = p_per_in_ler_id
2917: and ctu.effective_end_date
2918: and ctu.cm_typ_id = p_cm_typ_id
2919: and ctu.all_r_any_cd = 'ALL'
2920: and exists (select null
2921: from ben_elig_cvrd_dpnt_f pdp,
2922: ben_prtt_enrt_rslt_f pen
2923: where pdp.business_group_id = ctu.business_group_id
2924: and pdp.dpnt_person_id = p_rcpent_person_id
2925: and pen.prtt_enrt_rslt_stat_cd is null
2935: nvl(ctu.pl_id,pen.pl_id));
2936: --
2937: cursor c_dpnt_always is
2938: select null
2939: from ben_elig_cvrd_dpnt_f pdp,
2940: ben_prtt_enrt_rslt_f pen
2941: where pdp.business_group_id = p_business_group_id
2942: and pdp.dpnt_person_id = p_rcpent_person_id
2943: and pen.prtt_enrt_rslt_stat_cd is null
3116: and ctu.effective_end_date
3117: and ctu.cm_typ_id = p_cm_typ_id
3118: and ctu.all_r_any_cd = 'ALL'
3119: and exists (select null
3120: from ben_elig_cvrd_dpnt_f pdp,
3121: ben_prtt_enrt_rslt_f pen
3122: where pdp.business_group_id = ctu.business_group_id
3123: and pdp.per_in_ler_id = p_per_in_ler_id
3124: and pdp.dpnt_person_id = p_rcpent_person_id
3137: cursor c_dpnt_always is
3138: select null
3139: from sys.dual
3140: where exists (select null
3141: from ben_elig_cvrd_dpnt_f pdp,
3142: ben_prtt_enrt_rslt_f pen
3143: where pdp.business_group_id = p_business_group_id
3144: and pdp.per_in_ler_id = p_per_in_ler_id
3145: and pdp.dpnt_person_id = p_rcpent_person_id
3481: and ctu.effective_end_date
3482: and ctu.cm_typ_id = p_cm_typ_id
3483: and ctu.all_r_any_cd = 'ALL'
3484: and exists (select null
3485: from ben_elig_cvrd_dpnt_f pdp
3486: where pdp.business_group_id = pel.business_group_id
3487: and pdp.dpnt_person_id = p_rcpent_person_id
3488: and pdp.effective_start_date = p_effective_date
3489: and pdp.per_in_ler_id = pel.per_in_ler_id);
3491: cursor c_dpnt_always is
3492: select null
3493: from sys.dual
3494: where exists (select null
3495: from ben_elig_cvrd_dpnt_f pdp
3496: where pdp.business_group_id = p_business_group_id
3497: and pdp.dpnt_person_id = p_rcpent_person_id
3498: and pdp.effective_start_date = p_effective_date
3499: and pdp.per_in_ler_id = p_per_in_ler_id);
3799: ctu.effective_start_date and ctu.effective_end_date
3800: and ctu.cm_typ_id = p_cm_typ_id
3801: and ctu.all_r_any_cd = 'ALL'
3802: and exists (select null
3803: from ben_elig_cvrd_dpnt_f pdp,
3804: ben_prtt_enrt_rslt_f pen
3805: where pdp.business_group_id = ctu.business_group_id
3806: and pdp.per_in_ler_id = p_per_in_ler_id
3807: and pdp.dpnt_person_id = p_rcpent_person_id
3821: cursor c_dpnt_always is
3822: select null
3823: from sys.dual
3824: where exists (select null
3825: from ben_elig_cvrd_dpnt_f pdp,
3826: ben_prtt_enrt_rslt_f pen
3827: where pdp.business_group_id = p_business_group_id
3828: and pdp.per_in_ler_id = p_per_in_ler_id
3829: and pdp.dpnt_person_id = p_rcpent_person_id
4282: -- and regulation.
4283: --
4284: cursor c_prev_enrt is
4285: select pen.pgm_id, pen.pl_typ_id, max(pen.enrt_cvg_thru_dt)
4286: from ben_elig_cvrd_dpnt_f pdp,
4287: ben_prtt_enrt_rslt_f pen,
4288: ben_pl_f pln,
4289: ben_pl_regn_f plrg,
4290: ben_regn_f regn,
4327: -- and regulation.
4328: --
4329: cursor c_bendsgel_prev_enrt is
4330: select max(pen.enrt_cvg_thru_dt)
4331: from ben_elig_cvrd_dpnt_f pdp,
4332: ben_prtt_enrt_rslt_f pen,
4333: ben_pl_f pln,
4334: ben_pl_regn_f plrg,
4335: ben_regn_f regn,
4452: -- plan type.
4453: --
4454: cursor c_crntly_enrd is
4455: select 'Y'
4456: from ben_elig_cvrd_dpnt_f pdp,
4457: ben_prtt_enrt_rslt_f pen,
4458: ben_pl_f pln,
4459: ben_oipl_f cop,
4460: ben_opt_f opt,