[Home] [Help]
86: AND opt_id = c_opt_id
87: AND business_group_id = c_business_group_id
88: AND c_effective_date between effective_start_date and effective_end_date;
89:
90: Cursor get_ler_id(c_life_event ben_ler_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
91: c_effective_date ben_pgm_f.effective_start_date%type) is
92: select ler.ler_id
93: from ben_ler_f ler
94: where ler.business_group_id = c_business_group_id
89:
90: Cursor get_ler_id(c_life_event ben_ler_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
91: c_effective_date ben_pgm_f.effective_start_date%type) is
92: select ler.ler_id
93: from ben_ler_f ler
94: where ler.business_group_id = c_business_group_id
95: and ler.name = c_life_event
96: and c_effective_date between effective_start_date and effective_end_date;
97:
97:
98: CURSOR get_elig_chc_id_opt(c_pgm_id ben_pgm_f.pgm_id%type, c_pl_typ_id ben_pl_typ_f.pl_typ_id%type,
99: c_pl_id ben_pl_f.pl_id%type, c_plip_id ben_plip_f.plip_id%type,
100: c_ptip_id ben_ptip_f.ptip_id%type, c_oipl_id ben_oipl_f.oipl_id%type,
101: c_ler_id ben_ler_f.ler_id%type, c_person_id per_all_people_f.person_id%type) IS
102: SELECT elig_per_elctbl_chc_id,
103: pil.per_in_ler_id,
104: prtt_enrt_rslt_id
105: FROM ben_elig_per_ELCTBL_chc chc ,
117:
118: Cursor get_elig_chc_id(c_pgm_id ben_pgm_f.pgm_id%type, c_pl_typ_id ben_pl_typ_f.pl_typ_id%type,
119: c_pl_id ben_pl_f.pl_id%type, c_plip_id ben_plip_f.plip_id%type,
120: c_ptip_id ben_ptip_f.ptip_id%type,
121: c_ler_id ben_ler_f.ler_id%type, c_person_id per_all_people_f.person_id%type) is
122: select elig_per_elctbl_chc_id,
123: pil.per_in_ler_id,
124: prtt_enrt_rslt_id
125: from ben_elig_per_ELCTBL_chc chc ,
151: l_pl_id ben_pl_f.pl_id%type;
152: l_opt_id ben_opt_f.opt_id%type;
153: l_plip_id ben_plip_f.plip_id%type;
154: l_oipl_id ben_oipl_f.oipl_id%type;
155: l_ler_id ben_ler_f.ler_id%type;
156: l_ptnl_ler_for_per_id NUMBER;
157: l_elig_per_elctbl_chc_id NUMBER;
158: l_prtt_enrt_rslt_id number;
159: l_per_in_ler_id NUMBER;
196: select ler_id from ben_ptnl_ler_for_per
197: where business_group_id = p_business_group_id
198: and person_id = p_person_id
199: and ptnl_ler_for_per_stat_cd ='UNPROCD'
200: and ler_id not in (select ler_id from ben_ler_f where name
201: = 'Unrestricted' and business_group_id = p_business_group_id)
202: and LF_EVT_OCRD_DT = l_effective_date;
203:
204: cursor c_get_cvg_st_dt
212: cursor c_get_unproc_lf_evt
213: is
214: select le.name
215: from ben_ptnl_ler_for_per ptnl,
216: ben_ler_f le
217: where ptnl.business_group_id = p_business_group_id
218: and ptnl.person_id = p_person_id
219: and ptnl_ler_for_per_stat_cd ='UNPROCD'
220: and le.name <> 'Unrestricted'
245: p_business_group_id in number,
246: p_person_id in number)
247: is
248: select per_in_ler_id
249: from ben_per_in_ler pil,ben_ler_f lf
250: where pil.person_id = p_person_id
251: and pil.business_group_id = p_business_group_id
252: and pil.PER_IN_LER_STAT_CD in ('STRTD')
253: and lf.ler_id = pil.ler_id
723: l_pl_id ben_pl_f.pl_id%type;
724: l_opt_id ben_opt_f.opt_id%type;
725: l_plip_id ben_plip_f.plip_id%type;
726: l_oipl_id ben_oipl_f.oipl_id%type;
727: l_ler_id ben_ler_f.ler_id%type;
728: l_ptnl_ler_for_per_id NUMBER;
729: l_elig_per_elctbl_chc_id NUMBER;
730: l_prtt_enrt_rslt_id number;
731: l_per_in_ler_id NUMBER;
1044: AND opt_id = c_opt_id
1045: AND business_group_id = c_business_group_id
1046: AND c_effective_date between effective_start_date and effective_end_date;
1047:
1048: CURSOR get_ler_id(c_life_event ben_ler_f.name%type,
1049: c_business_group_id ben_pgm_f.business_group_id%type,
1050: c_effective_date ben_pgm_f.effective_start_date%type) is
1051: SELECT ler.ler_id
1052: FROM ben_ler_f ler
1048: CURSOR get_ler_id(c_life_event ben_ler_f.name%type,
1049: c_business_group_id ben_pgm_f.business_group_id%type,
1050: c_effective_date ben_pgm_f.effective_start_date%type) is
1051: SELECT ler.ler_id
1052: FROM ben_ler_f ler
1053: WHERE ler.business_group_id = c_business_group_id
1054: AND ler.name = c_life_event
1055: AND c_effective_date between effective_start_date and effective_end_date;
1056:
1059: c_pl_id ben_pl_f.pl_id%type,
1060: c_plip_id ben_plip_f.plip_id%type,
1061: c_ptip_id ben_ptip_f.ptip_id%type,
1062: c_oipl_id ben_oipl_f.oipl_id%type,
1063: c_ler_id ben_ler_f.ler_id%type,
1064: c_person_id per_all_people_f.person_id%type) is
1065: SELECT elig_per_elctbl_chc_id,
1066: pil.per_in_ler_id
1067: FROM ben_elig_per_ELCTBL_chc chc ,
1080: c_pl_typ_id ben_pl_typ_f.pl_typ_id%type,
1081: c_pl_id ben_pl_f.pl_id%type,
1082: c_plip_id ben_plip_f.plip_id%type,
1083: c_ptip_id ben_ptip_f.ptip_id%type,
1084: c_ler_id ben_ler_f.ler_id%type,
1085: c_person_id per_all_people_f.person_id%type) is
1086: SELECT elig_per_elctbl_chc_id,
1087: pil.per_in_ler_id
1088: FROM ben_elig_per_ELCTBL_chc chc ,
1106: FROM ben_ptnl_ler_for_per
1107: WHERE business_group_id = p_business_group_id
1108: AND person_id = p_person_id
1109: AND ptnl_ler_for_per_stat_cd ='UNPROCD'
1110: AND ler_id not in (select ler_id from ben_ler_f
1111: where name = 'Unrestricted'
1112: and business_group_id = p_business_group_id)
1113: AND lf_evt_ocrd_dt = c_effective_date;
1114:
1141: l_pl_id ben_pl_f.pl_id%type;
1142: l_opt_id ben_opt_f.opt_id%type;
1143: l_plip_id ben_plip_f.plip_id%type;
1144: l_oipl_id ben_oipl_f.oipl_id%type;
1145: l_ler_id ben_ler_f.ler_id%type;
1146: l_ptnl_ler_for_per_id NUMBER;
1147: l_elig_per_elctbl_chc_id NUMBER;
1148: l_prtt_enrt_rslt_id number;
1149: l_per_in_ler_id NUMBER;