[Home] [Help]
888: --
889: cursor c_person is
890: select distinct pen.person_id
891: from ben_prtt_enrt_rslt_f pen , ben_pl_typ_f ptyp ,
892: ben_per_in_ler pil
893: where pen.prtt_enrt_rslt_stat_cd is null
894: and pen.sspndd_flag = 'N' /* unsuspended enrollments */
895: and (pen.person_id = g_parm.person_id or g_parm.person_id is null)
896: and (pen.pl_id = g_parm.pl_nip_id or g_parm.pl_nip_id is null)
994: and g_parm.effective_date
995: between svc.effective_start_date and svc.effective_end_date))
996: and (g_parm.ler_id is null
997: or exists ( select null
998: from ben_per_in_ler pil2
999: where pil2.ler_id = g_parm.ler_id
1000: and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1001: and pil.per_in_ler_id = pil2.per_in_ler_id ))
1002: and (g_parm.lf_evt_ocrd_dt is null
1000: and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1001: and pil.per_in_ler_id = pil2.per_in_ler_id ))
1002: and (g_parm.lf_evt_ocrd_dt is null
1003: or exists ( select null
1004: from ben_per_in_ler pil3
1005: where pil3.lf_evt_ocrd_dt = g_parm.lf_evt_ocrd_dt
1006: and pil3.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1007: and pil.per_in_ler_id = pil3.per_in_ler_id ))
1008: and pil.per_in_ler_id = pen.per_in_ler_id
1075: -- ==================================================================================
1076: --
1077: procedure create_enrkit_ranges is
1078: --
1079: -- Cursor to select rows from ben_per_in_ler for people that haven't enrolled
1080: -- in a plan or a program as of the effective date
1081: --
1082: cursor c_person is
1083: select distinct pil.person_id, pil.ler_id
1080: -- in a plan or a program as of the effective date
1081: --
1082: cursor c_person is
1083: select distinct pil.person_id, pil.ler_id
1084: from ben_per_in_ler pil,
1085: ben_pil_elctbl_chc_popl pel,
1086: ben_ler_f ler
1087: where (g_parm.person_id is null or pil.person_id = g_parm.person_id)
1088: and pil.per_in_ler_stat_cd = 'STRTD'