APPS.BEN_GENERATE_COMMUNICATIONS dependencies on BEN_ELIG_PER_F

Line 992: from ben_elig_per_f pep

988: --
989: cursor c_pep IS
990: select pgm_id,
991: pl_id
992: from ben_elig_per_f pep
993: where pep.per_in_ler_id = p_per_in_ler_id
994: and pep.elig_per_id = p_elig_per_id ;
995: --
996: cursor c_epe_pgm(p_pgm_id number,p_pl_id number) IS

Line 1119: ben_elig_per_f pep,

1115: pep.elig_per_id,
1116: pep.pgm_id ppgm_id,
1117: pep.pl_id ppl_id
1118: from ben_cm_typ_usg_f ctu,
1119: ben_elig_per_f pep,
1120: ben_per_in_ler pil
1121: where ctu.business_group_id = p_business_group_id
1122: and pep.business_group_id = ctu.business_group_id
1123: and pep.person_id = p_person_id

Line 1185: FROM ben_elig_per_f pep2, ben_per_in_ler pil2

1181: and nvl(pil.per_in_ler_stat_cd,'-1') not in ('VOIDD','BCKDT')
1182: ---look for previous eligble
1183: and (( p_eligible_flag = 'Y'
1184: and not exists ( SELECT 'x'
1185: FROM ben_elig_per_f pep2, ben_per_in_ler pil2
1186: WHERE pep2.person_id = pep.person_id
1187: AND (ctu.pl_id is null or nvl(pep2.pl_id,-1) = nvl(pep.pl_id,-1) )
1188: AND (ctu.pgm_id is null or nvl(pep2.pgm_id,-1) = nvl(pep.pgm_id,-1) )
1189: --- pep ler id is not updated so pil ler id is validated # 2784972

Line 1222: FROM ben_elig_per_f pep2, ben_per_in_ler pil2

1218: --for previous life event and not eligible for the current LE. Added both exists clause for the bug fix
1219: OR
1220: ( p_eligible_flag = 'N'
1221: and exists ( SELECT 'x'
1222: FROM ben_elig_per_f pep2, ben_per_in_ler pil2
1223: WHERE pep2.person_id = pep.person_id
1224: AND (ctu.pl_id is null or nvl(pep2.pl_id,-1) = nvl(pep.pl_id,-1) )
1225: AND (ctu.pgm_id is null or nvl(pep2.pgm_id,-1) = nvl(pep.pgm_id,-1) )
1226: -- AND (ctu.ler_id is null or nvl(ctu.ler_id,-1) = nvl(pil2.ler_id,-1) )

Line 1250: FROM ben_elig_per_f pep2, ben_per_in_ler pil2

1246: ( 'VOIDD', 'BCKDT')
1247: OR pil2.per_in_ler_stat_cd IS NULL) --
1248: )
1249: and exists ( SELECT 'x'
1250: FROM ben_elig_per_f pep2, ben_per_in_ler pil2
1251: WHERE pep2.person_id = pep.person_id
1252: AND (ctu.pl_id is null or nvl(pep2.pl_id,-1) = nvl(pep.pl_id,-1) )
1253: AND (ctu.pgm_id is null or nvl(pep2.pgm_id,-1) = nvl(pep.pgm_id,-1) )
1254: AND (ctu.ler_id is null or nvl(ctu.ler_id,-1) = nvl(pil2.ler_id,-1) )

Line 1283: from ben_elig_per_f pep,

1279:
1280: --
1281: cursor c2(cv_effective_date date, cv_lf_evt_ocrd_dt date) is
1282: select null
1283: from ben_elig_per_f pep,
1284: ben_per_in_ler pil
1285: where pep.business_group_id = p_business_group_id
1286: and pep.person_id = p_person_id
1287: and nvl(get_cvg_strt_dt(pep.elig_per_id,pil.per_in_ler_id),cv_effective_date)

Line 1318: FROM ben_elig_per_f pep,

1314: c_ler_id number,
1315: c_pl_typ_id number,
1316: cv_effective_date date) is
1317: select 'x'
1318: FROM ben_elig_per_f pep,
1319: ben_per_in_ler pil,
1320: ben_elig_dpnt egd
1321: WHERE egd.dpnt_person_id = p_person_id
1322: and egd.business_group_id = p_business_group_id