[Home] [Help]
859: --
860: cursor c_epe_pgm(p_pgm_id number,p_pl_id number) IS
861: select epe.fonm_cvg_strt_dt
862: from ben_pil_elctbl_chc_popl popl,
863: ben_elig_per_elctbl_chc epe
864: where popl.per_in_ler_id = p_per_in_ler_id
865: and popl.pgm_id = p_pgm_id
866: and epe.pil_elctbl_chc_popl_id = popl.pil_elctbl_chc_popl_id
867: and NVL(epe.pl_id,-1) = nvl(p_pl_id, nvl(epe.pl_id,-1)); -- 5633934 : Added this condition
868: --
869: cursor c_epe_pl(p_pl_id number) IS
870: select epe.fonm_cvg_strt_dt
871: from ben_pil_elctbl_chc_popl popl,
872: ben_elig_per_elctbl_chc epe
873: where popl.per_in_ler_id = p_per_in_ler_id
874: and popl.pl_id = p_pl_id
875: and epe.pil_elctbl_chc_popl_id = popl.pil_elctbl_chc_popl_id;
876: --
1522: AND ctu.business_group_id = pil.business_group_id
1523: AND NVL (ctu.ler_id, pil.ler_id) = pil.ler_id
1524: AND EXISTS (
1525: SELECT NULL
1526: FROM ben_elig_per_elctbl_chc epe
1527: WHERE epe.per_in_ler_id = pil.per_in_ler_id
1528: AND epe.business_group_id = pil.business_group_id
1529: AND epe.elctbl_flag = 'Y'
1530: AND NVL (ctu.pgm_id, NVL (epe.pgm_id, -1)) =
1544: ctu.pgm_id,
1545: ctu.pl_typ_id
1546: from ben_cm_typ_usg_f ctu,
1547: ben_per_in_ler pil,
1548: ben_elig_per_elctbl_chc epe
1549: where pil.per_in_ler_id = c_pil_id
1550: and pil.business_group_id = c_bgp_id
1551: and ctu.business_group_id = pil.business_group_id
1552:
1574: and ctu.all_r_any_cd = 'ALL'; */
1575: --
1576: cursor c2 is
1577: select null
1578: from ben_elig_per_elctbl_chc epe,
1579: ben_per_in_ler pil
1580: where pil.per_in_ler_id = p_per_in_ler_id
1581: and pil.business_group_id = p_business_group_id
1582: and epe.per_in_ler_id = pil.per_in_ler_id
1771: and ctu.pl_typ_id is null
1772: and ctu.enrt_perd_id is null
1773: and ctu.actn_typ_id is null
1774: and not exists (select null
1775: from ben_elig_per_elctbl_chc epe
1776: where epe.business_group_id = p_business_group_id
1777: and epe.elctbl_flag = 'Y'
1778: and epe.per_in_ler_id = p_per_in_ler_id)
1779: and ben_generate_communications.g_comm_start_date
1786: cursor c2 is
1787: select null
1788: from sys.dual
1789: where not exists (select null
1790: from ben_elig_per_elctbl_chc epe
1791: where epe.business_group_id = p_business_group_id
1792: and epe.elctbl_flag = 'Y'
1793: and epe.per_in_ler_id = p_per_in_ler_id);
1794: --
2143: l_c1 c1%rowtype;
2144: --
2145: cursor c2 is
2146: select null
2147: from ben_elig_per_elctbl_chc epe,
2148: ben_per_in_ler pil
2149: where pil.per_in_ler_id = p_per_in_ler_id
2150: and pil.business_group_id = p_business_group_id
2151: and epe.per_in_ler_id = pil.per_in_ler_id;
2297: l_c1 c1%rowtype;
2298: --
2299: cursor c2 is
2300: select null
2301: from ben_elig_per_elctbl_chc epe,
2302: ben_per_in_ler pil
2303: where pil.per_in_ler_id = p_per_in_ler_id
2304: and pil.business_group_id = p_business_group_id
2305: and epe.per_in_ler_id = pil.per_in_ler_id;
2923: and ctu.cm_typ_id = p_cm_typ_id
2924: and ctu.all_r_any_cd = 'ALL'
2925: and exists
2926: (select null
2927: from ben_elig_per_elctbl_chc epe1
2928: where epe1.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
2929: and epe1.elctbl_flag = 'Y');
2930: --
2931: cursor c2 is
2929: and epe1.elctbl_flag = 'Y');
2930: --
2931: cursor c2 is
2932: select null
2933: from ben_elig_per_elctbl_chc epe
2934: where epe.per_in_ler_id = p_per_in_ler_id
2935: and epe.elctbl_flag = 'Y'
2936: and epe.business_group_id = p_business_group_id;
2937: --
3535: --
3536: cursor c_epe(v_pgm_id in number,
3537: v_pl_typ_id in number) is
3538: select 'Y'
3539: from ben_elig_per_elctbl_chc epe,
3540: ben_per_in_ler pil,
3541: ben_pl_f pln,
3542: ben_oipl_f cop,
3543: ben_opt_f opt