[Home] [Help]
1135:
1136: cursor c_pct_ft is
1137: select bep.rt_pct_fl_tm_val
1138: from ben_elig_per_f bep,
1139: ben_per_in_ler pil
1140: where bep.person_id = p_person_id
1141: and nvl(bep.pgm_id,-1) = nvl(p_pgm_id,-1)
1142: and nvl(bep.pl_id,-1) = nvl(p_pl_id,-1)
1143: and p_effective_date
1151:
1152: cursor c_pct_ft_opt is
1153: select epo.rt_pct_fl_tm_val
1154: from ben_elig_per_f bep, ben_elig_per_opt_f epo,
1155: ben_per_in_ler pil
1156: where bep.person_id = p_person_id
1157: and bep.elig_per_id = epo.elig_per_id
1158: and epo.opt_id = p_opt_id
1159: and nvl(bep.pgm_id,-1) = nvl(p_pgm_id,-1)
1169:
1170: cursor c_pct_ft_plip is
1171: select epo.rt_pct_fl_tm_val
1172: from ben_elig_per_f bep, ben_elig_per_opt_f epo,
1173: ben_per_in_ler pil
1174: where bep.person_id = p_person_id
1175: and bep.elig_per_id = epo.elig_per_id
1176: and epo.opt_id = p_opt_id
1177: and nvl(bep.pgm_id,-1) = nvl(p_pgm_id,-1)
1480:
1481: cursor c_hrs_wkd is
1482: select bep.rt_hrs_wkd_val
1483: from ben_elig_per_f bep,
1484: ben_per_in_ler pil
1485: where bep.person_id = p_person_id
1486: and nvl(bep.pgm_id,-1) = nvl(p_pgm_id,-1)
1487: and nvl(bep.pl_id,-1) = nvl(p_pl_id,-1)
1488: and p_effective_date between bep.effective_start_date
1494:
1495: cursor c_hrs_wkd_opt is
1496: select epo.rt_hrs_wkd_val
1497: from ben_elig_per_f bep, ben_elig_per_opt_f epo,
1498: ben_per_in_ler pil
1499: where bep.person_id = p_person_id
1500: and bep.elig_per_id = epo.elig_per_id
1501: and epo.opt_id = p_opt_id
1502: and nvl(bep.pgm_id,-1) = nvl(p_pgm_id,-1)
1512:
1513: cursor c_hrs_wkd_plip is
1514: select epo.rt_hrs_wkd_val
1515: from ben_elig_per_f bep, ben_elig_per_opt_f epo,
1516: ben_per_in_ler pil
1517: where bep.person_id = p_person_id
1518: and bep.elig_per_id = epo.elig_per_id
1519: and epo.opt_id = p_opt_id
1520: and nvl(bep.pgm_id,-1) = nvl(p_pgm_id,-1)
3806: cursor c_get_quald_bnf is
3807: select cqb.*, crp.per_in_ler_id
3808: from ben_cbr_quald_bnf cqb
3809: ,ben_cbr_per_in_ler crp
3810: ,ben_per_in_ler pil
3811: where cqb.quald_bnf_person_id = p_person_id
3812: and cqb.quald_bnf_flag = 'Y'
3813: and nvl(p_lf_evt_ocrd_dt,p_effective_date)
3814: between cqb.cbr_elig_perd_strt_dt and
3826: cursor c_get_all_quald_bnf(p_cvrd_emp_person_id in number) is
3827: select cqb.*
3828: from ben_cbr_quald_bnf cqb
3829: ,ben_cbr_per_in_ler crp
3830: ,ben_per_in_ler pil
3831: where cqb.cvrd_emp_person_id = p_cvrd_emp_person_id
3832: and cqb.quald_bnf_flag = 'Y'
3833: and nvl(p_lf_evt_ocrd_dt,p_effective_date)
3834: between cqb.cbr_elig_perd_strt_dt and
3846: cursor c_get_dsblity_evt(p_cbr_quald_bnf_id number) is
3847: select null
3848: from ben_cbr_per_in_ler crp
3849: , ben_ler_f ler
3850: , ben_per_in_ler pil
3851: where crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
3852: and crp.per_in_ler_id = pil.per_in_ler_id
3853: and pil.ler_id = ler.ler_id
3854: and ler.typ_cd = 'DSBLTY'
3860: and ler.business_group_id = pil.business_group_id
3861: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
3862: and crp.cnt_num = (select max(cnt_num)
3863: from ben_cbr_per_in_ler crp2
3864: ,ben_per_in_ler pil2
3865: where crp2.cbr_quald_bnf_id = p_cbr_quald_bnf_id
3866: and crp2.per_in_ler_id = pil2.per_in_ler_id
3867: and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
3868: and crp2.business_group_id = p_business_group_id
3871: cursor c_get_arra_evt(p_cbr_elig_perd_strt_dt date
3872: ,p_cbr_elig_perd_end_dt date) is
3873: select null
3874: from ben_ler_f ler
3875: , ben_per_in_ler pil
3876: where ler.ler_id = pil.ler_id
3877: and pil.person_id = p_person_id
3878: and ler.name = 'Assistance Eligible Individual'
3879: and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
4436: ,p_ptip_id in number) is
4437: select cqb.quald_bnf_flag
4438: from ben_cbr_quald_bnf cqb
4439: ,ben_cbr_per_in_ler crp
4440: ,ben_per_in_ler pil
4441: where cqb.quald_bnf_person_id = p_person_id
4442: and cqb.pgm_id = nvl(p_pgm_id,cqb.pgm_id)
4443: and nvl(cqb.ptip_id,-1) = nvl(p_ptip_id, -1)
4444: and p_lf_evt_ocrd_dt
4998: -- considering the plans which are not eligible on that p_effective_date.
4999: cursor c1(p_pl_id in number) is
5000: select null
5001: from ben_elig_per_f epo,
5002: ben_per_in_ler pil
5003: where epo.person_id = p_person_id
5004: and epo.pl_id = p_pl_id
5005: and p_effective_date
5006: between epo.effective_start_date
5157: ben_ptip_f ctp,
5158: ben_pl_regn_f prg,
5159: ben_regn_f reg,
5160: ben_elig_per_f epo,
5161: ben_per_in_ler pil
5162: where pln.pl_id = cpp.pl_id
5163: and pln.business_group_id = c_business_group_id
5164: and c_effective_date
5165: between pln.effective_start_date
6018: ben_plip_f cpp,
6019: ben_ptip_f ctp,
6020: ben_elig_per_f epo,
6021: ben_elig_dpnt edp,
6022: ben_per_in_ler pil
6023: where pln.pl_id = cpp.pl_id
6024: and pln.business_group_id = c_bgp_id
6025: and c_eff_date
6026: between pln.effective_start_date and pln.effective_end_date
6042: and epo.elig_flag = 'Y'
6043: and edp.dpnt_inelig_flag = 'N'
6044: and edp.create_dt = (select max(edp2.create_dt)
6045: from ben_elig_dpnt edp2
6046: ,ben_per_in_ler pil2
6047: where edp2.dpnt_person_id = edp.dpnt_person_id
6048: and edp2.elig_per_id = epo.elig_per_id
6049: and pil2.per_in_ler_id(+)=edp2.per_in_ler_id
6050: and pil2.business_group_id(+)=edp2.business_group_id
7235: epe.oiplip_id,
7236: pil.ler_id,
7237: pil.per_in_ler_id
7238: from ben_elig_per_elctbl_chc epe,
7239: ben_per_in_ler pil
7240: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
7241: and epe.per_in_ler_id = pil.per_in_ler_id;
7242: --
7243: l_epe c_epe%rowtype;