APPS.GHR_BENEFITS_EIT dependencies on BEN_LER_F

Line 90: Cursor get_ler_id(c_life_event ben_ler_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,

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

Line 93: from ben_ler_f ler

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:

Line 101: c_ler_id ben_ler_f.ler_id%type, c_person_id per_all_people_f.person_id%type) IS

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 ,

Line 121: c_ler_id ben_ler_f.ler_id%type, c_person_id per_all_people_f.person_id%type) is

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 ,

Line 155: l_ler_id ben_ler_f.ler_id%type;

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;

Line 200: and ler_id not in (select ler_id from ben_ler_f where name

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

Line 216: ben_ler_f le

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'

Line 249: from ben_per_in_ler pil,ben_ler_f lf

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

Line 727: l_ler_id ben_ler_f.ler_id%type;

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;