[Home] [Help]
1130: ,p_pgm_id in number default null) is
1131: --
1132: l_ok boolean := false;
1133: l_opt_id number(15);
1134: l_rt_pct_fl_tm_val ben_elig_per_f.rt_pct_fl_tm_val%type := null;
1135:
1136: cursor c_pct_ft is
1137: select bep.rt_pct_fl_tm_val
1138: from ben_elig_per_f bep,
1134: l_rt_pct_fl_tm_val ben_elig_per_f.rt_pct_fl_tm_val%type := null;
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)
1150: );
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
1168: or pil.per_in_ler_stat_cd is null); -- outer join condition
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
1474: ,p_pgm_id in number default null) is
1475: --
1476: l_ok boolean := false;
1477: l_opt_id number(15);
1478: l_rt_hrs_wkd_val ben_elig_per_f.rt_hrs_wkd_val%type;
1479:
1480:
1481: cursor c_hrs_wkd is
1482: select bep.rt_hrs_wkd_val
1479:
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)
1493: or pil.per_in_ler_stat_cd is null); -- outer join condition
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
1511: or pil.per_in_ler_stat_cd is null); -- outer join condition
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
4997: -- elig_flag as 'Y' only.If this condition is not added system is
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
5108: null
5109: from ben_pl_f pln,
5110: ben_plip_f cpp,
5111: ben_ptip_f ctp,
5112: ben_elig_per_f epo
5113: where pln.pl_id = cpp.pl_id
5114: and c_effective_date
5115: between pln.effective_start_date
5116: and pln.effective_end_date
5156: ben_plip_f cpp,
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
6016: select null
6017: from ben_pl_f pln,
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