[Home] [Help]
220: and ctu.cm_typ_id = p_cm_typ_id
221: and ctu.all_r_any_cd = 'ALL'
222: and exists
223: (select null
224: from ben_prtt_enrt_rslt_f pen1,
225: ben_pl_bnf_f pbn1
226: where pen1.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
227: and pen1.prtt_enrt_rslt_id = pbn1.prtt_enrt_rslt_id
228: and pbn1.bnf_person_id = p_rcpent_person_id
245: and epe.business_group_id = pil.business_group_id
246: and epe.per_in_ler_id = pil.per_in_ler_id
247: and epe.elctbl_flag = 'Y'
248: and exists (select null
249: from ben_prtt_enrt_rslt_f pen1,
250: ben_pl_bnf_f pbn1
251: where pbn1.business_group_id = pil.business_group_id
252: and pbn1.bnf_person_id = p_rcpent_person_id
253: and pbn1.per_in_ler_id = pil.per_in_ler_id
602: pep.effective_start_date = p_lf_evt_ocrd_dt)
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
633: pep.effective_start_date = p_lf_evt_ocrd_dt)
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
701: pep.effective_start_date = p_lf_evt_ocrd_dt)
702: -- and pep.effective_start_date = p_effective_date
703: and pep.elig_flag = p_eligible_flag
704: and exists (select null
705: from ben_prtt_enrt_rslt_f pen,
706: ben_pl_bnf_f pbn
707: where pen.business_group_id = p_business_group_id
708: and pbn.business_group_id = pen.business_group_id
709: and pbn.bnf_person_id = p_rcpent_person_id
732: pep.effective_start_date = p_lf_evt_ocrd_dt)
733: -- and pep.effective_start_date = p_effective_date
734: and pep.elig_flag = p_eligible_flag
735: and exists (select null
736: from ben_prtt_enrt_rslt_f pen,
737: ben_pl_bnf_f pbn
738: where pen.business_group_id = p_business_group_id
739: and pbn.business_group_id = pen.business_group_id
740: and pbn.bnf_person_id = p_rcpent_person_id
1085: cursor c_bnf_usage is
1086: select ctu.cm_typ_usg_id,
1087: ctu.cm_usg_rl
1088: from ben_cm_typ_usg_f ctu,
1089: ben_prtt_enrt_rslt_f pen,
1090: ben_enrt_perd enp,
1091: ben_popl_enrt_typ_cycl_f pet,
1092: ben_prtt_rt_val prv
1093: where ctu.business_group_id = p_business_group_id
1148: ;
1149: --
1150: cursor c_bnf_always is
1151: select null
1152: from ben_prtt_enrt_rslt_f pen,
1153: ben_prtt_rt_val prv
1154: where prv.business_group_id = p_business_group_id
1155: and prv.per_in_ler_id = p_per_in_ler_id
1156: and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
1174: cursor c_dpnt_usage is
1175: select ctu.cm_typ_usg_id,
1176: ctu.cm_usg_rl
1177: from ben_cm_typ_usg_f ctu,
1178: ben_prtt_enrt_rslt_f pen,
1179: ben_enrt_perd enp,
1180: ben_popl_enrt_typ_cycl_f pet,
1181: ben_prtt_rt_val prv
1182: where ctu.business_group_id = p_business_group_id
1238: ;
1239: --
1240: cursor c_dpnt_always is
1241: select null
1242: from ben_prtt_enrt_rslt_f pen,
1243: ben_prtt_rt_val prv
1244: where prv.business_group_id = p_business_group_id
1245: and prv.per_in_ler_id = p_per_in_ler_id
1246: and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
1491: cursor c_bnf_usage is
1492: select ctu.cm_typ_usg_id,
1493: ctu.cm_usg_rl
1494: from ben_cm_typ_usg_f ctu,
1495: ben_prtt_enrt_rslt_f pen,
1496: ben_enrt_perd enp,
1497: ben_popl_enrt_typ_cycl_f pet
1498: where ctu.business_group_id = p_business_group_id
1499: and pen.prtt_enrt_rslt_stat_cd is null
1549: ;
1550: --
1551: cursor c_bnf_always is
1552: select null
1553: from ben_prtt_enrt_rslt_f pen
1554: where pen.business_group_id = p_business_group_id
1555: and pen.prtt_enrt_rslt_stat_cd is null
1556: and pen.per_in_ler_id = p_per_in_ler_id
1557: and pen.enrt_cvg_thru_dt < hr_api.g_eot
1570: cursor c_dpnt_usage is
1571: select ctu.cm_typ_usg_id,
1572: ctu.cm_usg_rl
1573: from ben_cm_typ_usg_f ctu,
1574: ben_prtt_enrt_rslt_f pen,
1575: ben_enrt_perd enp,
1576: ben_popl_enrt_typ_cycl_f pet
1577: where ctu.business_group_id = p_business_group_id
1578: and pen.prtt_enrt_rslt_stat_cd is null
1627: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT'));
1628: --
1629: cursor c_dpnt_always is
1630: select null
1631: from ben_prtt_enrt_rslt_f pen
1632: where pen.business_group_id = p_business_group_id
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
1876: cursor c_bnf_usage is
1877: select ctu.cm_typ_usg_id,
1878: ctu.cm_usg_rl
1879: from ben_cm_typ_usg_f ctu,
1880: ben_prtt_enrt_rslt_f pen,
1881: ben_pil_elctbl_chc_popl pel
1882: where ctu.business_group_id = p_business_group_id
1883: and pen.prtt_enrt_rslt_stat_cd is null
1884: /* First join comp objects */
1919: and pbn.effective_end_date);
1920: --
1921: cursor c_bnf_always is
1922: select null
1923: from ben_prtt_enrt_rslt_f pen,
1924: ben_per_in_ler pil
1925: where pil.per_in_ler_id = p_per_in_ler_id
1926: and pil.business_group_id = p_business_group_id
1927: and pen.business_group_id = pil.business_group_id
1942: cursor c_dpnt_usage is
1943: select ctu.cm_typ_usg_id,
1944: ctu.cm_usg_rl
1945: from ben_cm_typ_usg_f ctu,
1946: ben_prtt_enrt_rslt_f pen,
1947: ben_pil_elctbl_chc_popl pel
1948: where ctu.business_group_id = p_business_group_id
1949: and pen.prtt_enrt_rslt_stat_cd is null
1950: /* First join comp objects */
1980: and pdp.effective_start_date = p_effective_date);
1981: --
1982: cursor c_dpnt_always is
1983: select null
1984: from ben_prtt_enrt_rslt_f pen,
1985: ben_per_in_ler pil
1986: where pil.per_in_ler_id = p_per_in_ler_id
1987: and pil.business_group_id = p_business_group_id
1988: and pen.business_group_id = pil.business_group_id
2553: and ctu.cm_typ_id = p_cm_typ_id
2554: and ctu.all_r_any_cd = 'ALL'
2555: and exists (select null
2556: from ben_pl_bnf_f pbn,
2557: ben_prtt_enrt_rslt_f pen,
2558: ben_prtt_enrt_actn_f pea
2559: where pbn.business_group_id = ctu.business_group_id
2560: and pbn.per_in_ler_id = p_per_in_ler_id
2561: and pbn.bnf_person_id = p_rcpent_person_id
2581: select null
2582: from sys.dual
2583: where exists (select null
2584: from ben_pl_bnf_f pbn,
2585: ben_prtt_enrt_rslt_f pen,
2586: ben_prtt_enrt_actn_f pea
2587: where pbn.business_group_id = p_business_group_id
2588: and pbn.per_in_ler_id = p_per_in_ler_id
2589: and pbn.bnf_person_id = p_rcpent_person_id
2626: and ctu.cm_typ_id = p_cm_typ_id
2627: and ctu.all_r_any_cd = 'ALL'
2628: and exists (select null
2629: from ben_elig_cvrd_dpnt pdp,
2630: ben_prtt_enrt_rslt_f pen,
2631: ben_prtt_enrt_actn_f pea
2632: where pdp.business_group_id = ctu.business_group_id
2633: and pdp.per_in_ler_id = p_per_in_ler_id
2634: and pdp.dpnt_person_id = p_rcpent_person_id
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
2662: and pdp.dpnt_person_id = p_rcpent_person_id
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
2926: and pdp.effective_start_date = p_effective_date
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
2944: and pdp.effective_start_date = p_effective_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
3125: and pen.prtt_enrt_rslt_stat_cd is null
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
3146: and pen.prtt_enrt_rslt_stat_cd is null
3172: and ctu.cm_typ_id = p_cm_typ_id
3173: and ctu.all_r_any_cd = 'ALL'
3174: and exists (select null
3175: from ben_pl_bnf_f pbn,
3176: ben_prtt_enrt_rslt_f pen
3177: where pbn.business_group_id = ctu.business_group_id
3178: and pbn.per_in_ler_id = p_per_in_ler_id
3179: and pbn.bnf_person_id = p_rcpent_person_id
3180: and pen.prtt_enrt_rslt_stat_cd is null
3193: select null
3194: from sys.dual
3195: where exists (select null
3196: from ben_pl_bnf_f pbn,
3197: ben_prtt_enrt_rslt_f pen
3198: where pbn.business_group_id = p_business_group_id
3199: and pbn.per_in_ler_id = p_per_in_ler_id
3200: and pbn.bnf_person_id = p_rcpent_person_id
3201: and pen.prtt_enrt_rslt_stat_cd is null
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
3808: and pen.prtt_enrt_rslt_stat_cd is null
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
3830: and pen.prtt_enrt_rslt_stat_cd is null
3856: and ctu.cm_typ_id = p_cm_typ_id
3857: and ctu.all_r_any_cd = 'ALL'
3858: and exists (select null
3859: from ben_pl_bnf_f pbn,
3860: ben_prtt_enrt_rslt_f pen
3861: where pbn.business_group_id = ctu.business_group_id
3862: and pbn.per_in_ler_id = p_per_in_ler_id
3863: and pbn.bnf_person_id = p_rcpent_person_id
3864: and pen.prtt_enrt_rslt_stat_cd is null
3878: select null
3879: from sys.dual
3880: where exists (select null
3881: from ben_pl_bnf_f pbn,
3882: ben_prtt_enrt_rslt_f pen
3883: where pbn.business_group_id = p_business_group_id
3884: and pbn.per_in_ler_id = p_per_in_ler_id
3885: and pbn.bnf_person_id = p_rcpent_person_id
3886: and pen.prtt_enrt_rslt_stat_cd is null
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,
4291: ben_oipl_f cop,
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,
4336: ben_oipl_f cop,
4408: -- plan type.
4409: --
4410: cursor c_prtt_crntly_enrd is
4411: select 'Y'
4412: from ben_prtt_enrt_rslt_f pen
4413: ,ben_pl_f pln
4414: ,ben_oipl_f cop
4415: ,ben_opt_f opt
4416: ,ben_pl_regn_f plrg
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,
4461: ben_per_in_ler pil