[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu,
ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and ctu.business_group_id = pil.business_group_id
/* First join comp objects */
and epe.per_in_ler_id = pil.per_in_ler_id
and epe.elctbl_flag = 'Y'
and nvl(ctu.ler_id,pil.ler_id) = pil.ler_id
and nvl(ctu.pgm_id,nvl(epe.pgm_id,-1)) = nvl(epe.pgm_id,-1)
and nvl(ctu.pl_id,nvl(epe.pl_id,-1)) = nvl(epe.pl_id,-1)
and nvl(ctu.pl_typ_id, nvl(epe.pl_typ_id, -1)) = nvl(epe.pl_typ_id,-1)
/* Now join in enrollment period */
and (-- PB : 5422 :
-- p_enrt_perd_id is null or
p_asnd_lf_evt_dt is null or
ctu.enrt_perd_id is null or
exists (
select null
from ben_enrt_perd enp_c
where enp_c.enrt_perd_id=ctu.enrt_perd_id and
enp_c.business_group_id=ctu.business_group_id and
enp_c.asnd_lf_evt_dt = p_asnd_lf_evt_dt
)
/* PB : 5422 :
select null
from ben_enrt_perd enp_c,
ben_enrt_perd enp_m
where enp_c.enrt_perd_id=ctu.enrt_perd_id and
enp_c.business_group_id=ctu.business_group_id and
enp_m.enrt_perd_id=p_enrt_perd_id and
enp_m.business_group_id=ctu.business_group_id and
enp_m.strt_dt=enp_c.strt_dt
) */
)
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and exists
(select null
from ben_elig_dpnt egd1
where egd1.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and egd1.business_group_id = epe.business_group_id
and egd1.dpnt_person_id = p_rcpent_person_id
and egd1.per_in_ler_id = pil.per_in_ler_id
and egd1.elig_cvrd_dpnt_id is not null);
select null
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and epe.business_group_id = pil.business_group_id
and epe.per_in_ler_id = pil.per_in_ler_id
and epe.elctbl_flag = 'Y'
and exists
(select null
from ben_elig_dpnt egd1
where egd1.business_group_id = pil.business_group_id
and egd1.dpnt_person_id = p_rcpent_person_id
and egd1.per_in_ler_id = pil.per_in_ler_id
and egd1.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and egd1.elig_cvrd_dpnt_id is not null);
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu,
ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and ctu.business_group_id = pil.business_group_id
/* First join comp objects */
and epe.per_in_ler_id = pil.per_in_ler_id
and epe.elctbl_flag = 'Y'
and nvl(ctu.ler_id,pil.ler_id) = pil.ler_id
and nvl(ctu.pgm_id,nvl(epe.pgm_id,-1)) = nvl(epe.pgm_id,-1)
and nvl(ctu.pl_id,nvl(epe.pl_id,-1)) = nvl(epe.pl_id,-1)
and nvl(ctu.pl_typ_id,nvl(epe.pl_typ_id,-1)) = nvl(epe.pl_typ_id, -1)
/* Now join in enrollment period */
and (-- PB : 5422 :
-- p_enrt_perd_id is null or
p_asnd_lf_evt_dt is null or
ctu.enrt_perd_id is null or
exists (
select null
from ben_enrt_perd enp_c
where enp_c.enrt_perd_id=ctu.enrt_perd_id and
enp_c.business_group_id=ctu.business_group_id and
enp_c.asnd_lf_evt_dt = p_asnd_lf_evt_dt
)
/* PB : 5422 :
select null
from ben_enrt_perd enp_c,
ben_enrt_perd enp_m
where enp_c.enrt_perd_id=ctu.enrt_perd_id and
enp_c.business_group_id=ctu.business_group_id and
enp_m.enrt_perd_id=p_enrt_perd_id and
enp_m.business_group_id=ctu.business_group_id and
enp_m.strt_dt=enp_c.strt_dt
) */
)
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and exists
(select null
from ben_prtt_enrt_rslt_f pen1,
ben_pl_bnf_f pbn1
where pen1.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
and pen1.prtt_enrt_rslt_id = pbn1.prtt_enrt_rslt_id
and pbn1.bnf_person_id = p_rcpent_person_id
and pen1.prtt_enrt_rslt_stat_cd is null
and pen1.business_group_id = epe.business_group_id
and pen1.business_group_id = pbn1.business_group_id
and p_effective_date
between pbn1.effective_start_date
and pbn1.effective_end_date
and p_effective_date
between pen1.effective_start_date
and pen1.effective_end_date);
select null
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and epe.business_group_id = pil.business_group_id
and epe.per_in_ler_id = pil.per_in_ler_id
and epe.elctbl_flag = 'Y'
and exists (select null
from ben_prtt_enrt_rslt_f pen1,
ben_pl_bnf_f pbn1
where pbn1.business_group_id = pil.business_group_id
and pbn1.bnf_person_id = p_rcpent_person_id
and pbn1.per_in_ler_id = pil.per_in_ler_id
and pen1.prtt_enrt_rslt_stat_cd is null
and p_effective_date = pbn1.effective_start_date
and pen1.per_in_ler_id = pbn1.per_in_ler_id
and pen1.business_group_id = pbn1.business_group_id
and pen1.prtt_enrt_rslt_id = pbn1.prtt_enrt_rslt_id
and p_effective_date = pen1.effective_start_date
and epe.prtt_enrt_rslt_id = pen1.prtt_enrt_rslt_id);
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu,
ben_elig_per_f pep,
ben_enrt_perd enp,
ben_popl_enrt_typ_cycl_f pet
where ctu.business_group_id = p_business_group_id
/* First join comp objects */
and pep.business_group_id = ctu.business_group_id
and pep.person_id = p_person_id
and p_effective_date
between pep.effective_start_date
and pep.effective_end_date
/* Use nvl to handle nulls */
and nvl(ctu.ler_id,nvl(pep.ler_id,-1)) = nvl(pep.ler_id,-1)
and nvl(ctu.pgm_id,nvl(pep.pgm_id,-1)) = nvl(pep.pgm_id,-1)
and nvl(ctu.pl_id,nvl(pep.pl_id,-1)) = nvl(pep.pl_id,-1)
/* PB : 5422 :
and (p_enrt_perd_id is null or
nvl(ctu.enrt_perd_id,p_enrt_perd_id) = p_enrt_perd_id)
*/
and (p_asnd_lf_evt_dt is null or
nvl(enp.asnd_lf_evt_dt, p_asnd_lf_evt_dt) = p_asnd_lf_evt_dt)
and (p_ler_id is null or
nvl(ctu.ler_id,p_ler_id) = p_ler_id)
/* Now join in enrollment period */
and ctu.enrt_perd_id = enp.enrt_perd_id(+)
and nvl(enp.business_group_id,ctu.business_group_id)
= ctu.business_group_id
/* Join in enrollment type cycle */
and enp.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id (+)
and nvl(pet.business_group_id,ctu.business_group_id)
= ctu.business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between nvl(pet.effective_start_date,nvl(p_lf_evt_ocrd_dt,
p_effective_date))
and nvl(pet.effective_end_date,nvl(p_lf_evt_ocrd_dt,
p_effective_date))
/* Use nvl here as only pgm pl can be populated */
and nvl(ctu.pl_id,-1) = nvl(pet.pl_id,nvl(ctu.pl_id,-1))
and nvl(ctu.pgm_id,-1) = nvl(pet.pgm_id,nvl(ctu.pgm_id,-1))
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
/* Final test make sure elig/ineligible as of today */
--
-- Bugs : 1412882, part of bug 1412951
--
and (pep.effective_start_date = p_effective_date or
pep.effective_start_date = p_lf_evt_ocrd_dt)
and pep.elig_flag = p_eligible_flag
;
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu,
ben_elig_per_f pep,
ben_enrt_perd enp,
ben_popl_enrt_typ_cycl_f pet
where ctu.business_group_id = p_business_group_id
/* First join comp objects */
and pep.business_group_id = ctu.business_group_id
and pep.person_id = p_person_id
and p_effective_date
between pep.effective_start_date
and pep.effective_end_date
/* Use nvl to handle nulls */
and nvl(ctu.ler_id,nvl(pep.ler_id,-1)) = nvl(pep.ler_id,-1)
and nvl(ctu.pgm_id,nvl(pep.pgm_id,-1)) = nvl(pep.pgm_id,-1)
and nvl(ctu.pl_id,nvl(pep.pl_id,-1)) = nvl(pep.pl_id,-1)
/* PB : 5422 :
and (p_enrt_perd_id is null or
nvl(ctu.enrt_perd_id,p_enrt_perd_id) = p_enrt_perd_id)
*/
and (p_asnd_lf_evt_dt is null or
nvl(enp.asnd_lf_evt_dt, p_asnd_lf_evt_dt) = p_asnd_lf_evt_dt)
and (p_ler_id is null or
nvl(ctu.ler_id,p_ler_id) = p_ler_id)
/* Now join in enrollment period */
and ctu.enrt_perd_id = enp.enrt_perd_id(+)
and nvl(enp.business_group_id,ctu.business_group_id)
= ctu.business_group_id
/* Join in enrollment type cycle */
and enp.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id (+)
and nvl(pet.business_group_id,ctu.business_group_id)
= ctu.business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between nvl(pet.effective_start_date,nvl(p_lf_evt_ocrd_dt,
p_effective_date))
and nvl(pet.effective_end_date,nvl(p_lf_evt_ocrd_dt,
p_effective_date))
/* Use nvl here as only pgm pl can be populated */
and nvl(ctu.pl_id,-1) = nvl(pet.pl_id,nvl(ctu.pl_id,-1))
and nvl(ctu.pgm_id,-1) = nvl(pet.pgm_id,nvl(ctu.pgm_id,-1))
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
/* Final test make sure elig/ineligible as of today */
--
-- Bugs : 1412882, part of bug 1412951
--
and (pep.effective_start_date = p_effective_date or
pep.effective_start_date = p_lf_evt_ocrd_dt)
-- and pep.effective_start_date = p_effective_date
and pep.elig_flag = p_eligible_flag
and exists (select null
from ben_prtt_enrt_rslt_f pen,
ben_elig_cvrd_dpnt_f pdp
where pen.business_group_id = p_business_group_id
and pdp.business_group_id = pen.business_group_id
and pdp.dpnt_person_id = p_rcpent_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date = pdp.effective_start_date
and pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
and p_effective_date = pen.effective_start_date
and nvl(pen.pgm_id,-1) = nvl(pep.pgm_id,-1)
and nvl(pen.pl_id,-1) = nvl(pep.pl_id,-1)
and pdp.per_in_ler_id = p_per_in_ler_id
and pen.per_in_ler_id = pdp.per_in_ler_id);
select null
from ben_elig_per_f pep
where pep.business_group_id = p_business_group_id
and pep.person_id = p_person_id
and p_effective_date
between pep.effective_start_date
and pep.effective_end_date
/* Final test make sure ineligible as of today */
--
-- Bugs : 1412882, part of bug 1412951
--
and (pep.effective_start_date = p_effective_date or
pep.effective_start_date = p_lf_evt_ocrd_dt)
-- and pep.effective_start_date = p_effective_date
and pep.elig_flag = p_eligible_flag
and exists (select null
from ben_prtt_enrt_rslt_f pen,
ben_elig_cvrd_dpnt_f pdp
where pen.business_group_id = p_business_group_id
and pdp.business_group_id = pen.business_group_id
and pdp.dpnt_person_id = p_rcpent_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date = pdp.effective_start_date
and pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
and p_effective_date = pen.effective_start_date
and pdp.per_in_ler_id = p_per_in_ler_id
and pen.per_in_ler_id = pdp.per_in_ler_id);
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu,
ben_elig_per_f pep,
ben_enrt_perd enp,
ben_popl_enrt_typ_cycl_f pet
where ctu.business_group_id = p_business_group_id
/* First join comp objects */
and pep.business_group_id = ctu.business_group_id
and pep.person_id = p_person_id
and p_effective_date
between pep.effective_start_date
and pep.effective_end_date
/* Use nvl to handle nulls */
and nvl(ctu.ler_id,nvl(pep.ler_id,-1)) = nvl(pep.ler_id,-1)
and nvl(ctu.pgm_id,nvl(pep.pgm_id,-1)) = nvl(pep.pgm_id,-1)
and nvl(ctu.pl_id,nvl(pep.pl_id,-1)) = nvl(pep.pl_id,-1)
/* PB : 5422 :
and (p_enrt_perd_id is null or
nvl(ctu.enrt_perd_id,p_enrt_perd_id) = p_enrt_perd_id)
*/
and (p_asnd_lf_evt_dt is null or
nvl(enp.asnd_lf_evt_dt, p_asnd_lf_evt_dt) = p_asnd_lf_evt_dt)
and (p_ler_id is null or
nvl(ctu.ler_id,p_ler_id) = p_ler_id)
/* Now join in enrollment period */
and ctu.enrt_perd_id = enp.enrt_perd_id(+)
and nvl(enp.business_group_id,ctu.business_group_id)
= ctu.business_group_id
/* Join in enrollment type cycle */
and enp.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id (+)
and nvl(pet.business_group_id,ctu.business_group_id)
= ctu.business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between nvl(pet.effective_start_date,nvl(p_lf_evt_ocrd_dt,
p_effective_date))
and nvl(pet.effective_end_date,nvl(p_lf_evt_ocrd_dt,
p_effective_date))
/* Use nvl here as only pgm pl can be populated */
and nvl(ctu.pl_id,-1) = nvl(pet.pl_id,nvl(ctu.pl_id,-1))
and nvl(ctu.pgm_id,-1) = nvl(pet.pgm_id,nvl(ctu.pgm_id,-1))
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
/* Final test make sure ineligible as of today */
--
-- Bugs : 1412882, part of bug 1412951
--
and (pep.effective_start_date = p_effective_date or
pep.effective_start_date = p_lf_evt_ocrd_dt)
-- and pep.effective_start_date = p_effective_date
and pep.elig_flag = p_eligible_flag
and exists (select null
from ben_prtt_enrt_rslt_f pen,
ben_pl_bnf_f pbn
where pen.business_group_id = p_business_group_id
and pbn.business_group_id = pen.business_group_id
and pbn.bnf_person_id = p_rcpent_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date = pbn.effective_start_date
and pen.prtt_enrt_rslt_id = pbn.prtt_enrt_rslt_id
and p_effective_date = pen.effective_start_date
and pbn.per_in_ler_id = p_per_in_ler_id
and nvl(pen.pgm_id,-1) = nvl(pep.pgm_id,-1)
and nvl(pen.pl_id,-1) = nvl(pep.pl_id,-1)
and pen.per_in_ler_id = pbn.per_in_ler_id);
select null
from ben_elig_per_f pep
where pep.business_group_id = p_business_group_id
and pep.person_id = p_person_id
and p_effective_date
between pep.effective_start_date
and pep.effective_end_date
/* Final test make sure eligible as of today */
--
-- Bugs : 1412882, part of bug 1412951
--
and (pep.effective_start_date = p_effective_date or
pep.effective_start_date = p_lf_evt_ocrd_dt)
-- and pep.effective_start_date = p_effective_date
and pep.elig_flag = p_eligible_flag
and exists (select null
from ben_prtt_enrt_rslt_f pen,
ben_pl_bnf_f pbn
where pen.business_group_id = p_business_group_id
and pbn.business_group_id = pen.business_group_id
and pbn.bnf_person_id = p_rcpent_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date = pbn.effective_start_date
and pen.prtt_enrt_rslt_id = pbn.prtt_enrt_rslt_id
and p_effective_date = pen.effective_start_date
and pbn.per_in_ler_id = p_per_in_ler_id
and pen.per_in_ler_id = pbn.per_in_ler_id);
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu,
ben_prtt_enrt_rslt_f pen,
ben_enrt_perd enp,
ben_popl_enrt_typ_cycl_f pet,
ben_prtt_rt_val prv
where ctu.business_group_id = p_business_group_id
/* First join comp objects */
and prv.business_group_id = ctu.business_group_id
and prv.per_in_ler_id = p_per_in_ler_id
and prv.elctns_made_dt = p_effective_date
and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and prv.prtt_rt_val_stat_cd is null
and pen.prtt_enrt_rslt_stat_cd is null
and pen.business_group_id = prv.business_group_id
and pen.per_in_ler_id <> prv.per_in_ler_id
and pen.ler_id = nvl(p_ler_id,pen.ler_id)
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
/* Use nvl here as only pgm or pl can be populated */
and nvl(ctu.ler_id,pen.ler_id) = pen.ler_id
and nvl(ctu.pgm_id,-1) = nvl(pen.pgm_id,-1)
and nvl(ctu.pl_id,pen.pl_id) = pen.pl_id
/* Now join in enrollment period */
/* PB : 5422 :
and (p_enrt_perd_id is null or
nvl(ctu.enrt_perd_id,p_enrt_perd_id) = p_enrt_perd_id)
*/
and (p_asnd_lf_evt_dt is null or
nvl(enp.asnd_lf_evt_dt, p_asnd_lf_evt_dt) = p_asnd_lf_evt_dt)
and ctu.enrt_perd_id = enp.enrt_perd_id(+)
and nvl(enp.business_group_id,ctu.business_group_id)
= ctu.business_group_id
/* Join in enrollment type cycle */
and enp.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id (+)
and nvl(pet.business_group_id,ctu.business_group_id)
= ctu.business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between nvl(pet.effective_start_date,nvl(p_lf_evt_ocrd_dt,
p_effective_date))
and nvl(pet.effective_end_date,nvl(p_lf_evt_ocrd_dt,
p_effective_date))
/* Use nvl here as only pgm pl can be populated */
and nvl(ctu.pl_id,-1) = nvl(pet.pl_id,nvl(ctu.pl_id,-1))
and nvl(ctu.pgm_id,-1) = nvl(pet.pgm_id,nvl(ctu.pgm_id,-1))
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and exists (select null
from ben_pl_bnf_f pbn1,
ben_per_in_ler pil
where pbn1.bnf_person_id = p_rcpent_person_id
and pbn1.business_group_id = pen.business_group_id
and pbn1.effective_start_date = p_effective_date
and pbn1.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pil.per_in_ler_id=pbn1.per_in_ler_id
and pil.business_group_id=pbn1.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT'))
;
select null
from ben_prtt_enrt_rslt_f pen,
ben_prtt_rt_val prv
where prv.business_group_id = p_business_group_id
and prv.per_in_ler_id = p_per_in_ler_id
and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and prv.prtt_rt_val_stat_cd is null
and pen.prtt_enrt_rslt_stat_cd is null
and prv.elctns_made_dt = p_effective_date
and pen.business_group_id = prv.business_group_id
and pen.per_in_ler_id <> prv.per_in_ler_id
and exists (select null
from ben_pl_bnf_f pbn,
ben_per_in_ler pil
where pbn.bnf_person_id = p_rcpent_person_id
and pbn.business_group_id = pen.business_group_id
and pbn.effective_start_date = p_effective_date
and pbn.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pil.per_in_ler_id=pbn.per_in_ler_id
and pil.business_group_id=pbn.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT'))
;
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu,
ben_prtt_enrt_rslt_f pen,
ben_enrt_perd enp,
ben_popl_enrt_typ_cycl_f pet,
ben_prtt_rt_val prv
where ctu.business_group_id = p_business_group_id
/* First join comp objects */
and prv.business_group_id = ctu.business_group_id
and prv.per_in_ler_id = p_per_in_ler_id
and prv.elctns_made_dt = p_effective_date
and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and prv.prtt_rt_val_stat_cd is null
and pen.prtt_enrt_rslt_stat_cd is null
and pen.business_group_id = prv.business_group_id
and pen.per_in_ler_id <> prv.per_in_ler_id
and pen.business_group_id = ctu.business_group_id
and pen.ler_id = nvl(p_ler_id,pen.ler_id)
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
/* Use nvl here as only pgm or pl can be populated */
and nvl(ctu.ler_id,pen.ler_id) = pen.ler_id
and nvl(ctu.pgm_id,-1) = nvl(pen.pgm_id,-1)
and nvl(ctu.pl_id,-1) = nvl(pen.pl_id,-1)
/* Now join in enrollment period */
/* PB : 5422 :
and (p_enrt_perd_id is null or
nvl(ctu.enrt_perd_id,p_enrt_perd_id) = p_enrt_perd_id)
*/
and (p_asnd_lf_evt_dt is null or
nvl(enp.asnd_lf_evt_dt, p_asnd_lf_evt_dt) = p_asnd_lf_evt_dt)
and ctu.enrt_perd_id = enp.enrt_perd_id(+)
and nvl(enp.business_group_id,ctu.business_group_id)
= ctu.business_group_id
/* Join in enrollment type cycle */
and enp.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id (+)
and nvl(pet.business_group_id,ctu.business_group_id)
= ctu.business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between nvl(pet.effective_start_date,nvl(p_lf_evt_ocrd_dt,
p_effective_date))
and nvl(pet.effective_end_date,nvl(p_lf_evt_ocrd_dt,
p_effective_date))
/* Use nvl here as only pgm pl can be populated */
and nvl(ctu.pl_id,-1) = nvl(pet.pl_id,nvl(ctu.pl_id,-1))
and nvl(ctu.pgm_id,-1) = nvl(pet.pgm_id,nvl(ctu.pgm_id,-1))
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and exists (select null
from ben_elig_cvrd_dpnt_f pdp,
ben_per_in_ler pil
where pdp.dpnt_person_id = p_rcpent_person_id
and pdp.business_group_id = pen.business_group_id
and pdp.effective_start_date = p_effective_date
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pil.per_in_ler_id=pdp.per_in_ler_id
and pil.business_group_id=pdp.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT'))
;
select null
from ben_prtt_enrt_rslt_f pen,
ben_prtt_rt_val prv
where prv.business_group_id = p_business_group_id
and prv.per_in_ler_id = p_per_in_ler_id
and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and prv.prtt_rt_val_stat_cd is null
and pen.prtt_enrt_rslt_stat_cd is null
and prv.elctns_made_dt = p_effective_date
and pen.business_group_id = prv.business_group_id
and pen.per_in_ler_id <> prv.per_in_ler_id
and exists (select null
from ben_elig_cvrd_dpnt_f pdp,
ben_per_in_ler pil
where pdp.dpnt_person_id = p_rcpent_person_id
and pdp.business_group_id = pen.business_group_id
and pdp.effective_start_date = p_effective_date
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pil.per_in_ler_id=pdp.per_in_ler_id
and pil.business_group_id=pdp.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT'));
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu,
ben_prtt_enrt_rslt_f pen,
ben_enrt_perd enp,
ben_popl_enrt_typ_cycl_f pet
where ctu.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
/* First join comp objects */
and pen.business_group_id = ctu.business_group_id
and pen.per_in_ler_id = p_per_in_ler_id
and pen.ler_id = nvl(p_ler_id,pen.ler_id)
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
/* Use nvl here as only pgm or pl can be populated */
and nvl(ctu.ler_id,pen.ler_id) = pen.ler_id
and nvl(ctu.pgm_id,nvl(pen.pgm_id,-1)) = nvl(pen.pgm_id,-1)
and nvl(ctu.pl_id,pen.pl_id) = pen.pl_id
/* Now join in enrollment period */
/* PB : 5422 :
and (p_enrt_perd_id is null or
nvl(ctu.enrt_perd_id,p_enrt_perd_id) = p_enrt_perd_id)
*/
and (p_asnd_lf_evt_dt is null or
nvl(enp.asnd_lf_evt_dt, p_asnd_lf_evt_dt) = p_asnd_lf_evt_dt)
and ctu.enrt_perd_id = enp.enrt_perd_id(+)
and nvl(enp.business_group_id,ctu.business_group_id)
= ctu.business_group_id
/* Join in enrollment type cycle */
and enp.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id (+)
and nvl(pet.business_group_id,ctu.business_group_id)
= ctu.business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between nvl(pet.effective_start_date,nvl(p_lf_evt_ocrd_dt,
p_effective_date))
and nvl(pet.effective_end_date,nvl(p_lf_evt_ocrd_dt,
p_effective_date))
/* Use nvl here as only pgm pl can be populated */
and nvl(ctu.pl_id,-1) = nvl(pet.pl_id,nvl(ctu.pl_id,-1))
and nvl(ctu.pgm_id,-1) = nvl(pet.pgm_id,nvl(ctu.pgm_id,-1))
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and pen.enrt_cvg_thru_dt < hr_api.g_eot
and exists (select null
from ben_pl_bnf_f pbn1,
ben_per_in_ler pil
where pbn1.bnf_person_id = p_rcpent_person_id
and pbn1.business_group_id = pen.business_group_id
and pbn1.effective_start_date = p_effective_date
and pbn1.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pil.per_in_ler_id=pbn1.per_in_ler_id
and pil.business_group_id=pbn1.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT'))
;
select null
from ben_prtt_enrt_rslt_f pen
where pen.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.per_in_ler_id = p_per_in_ler_id
and pen.enrt_cvg_thru_dt < hr_api.g_eot
and exists (select null
from ben_pl_bnf_f pbn,
ben_per_in_ler pil
where pbn.bnf_person_id = p_rcpent_person_id
and pbn.business_group_id = pen.business_group_id
and pbn.effective_start_date = p_effective_date
and pbn.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pil.per_in_ler_id=pbn.per_in_ler_id
and pil.business_group_id=pbn.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT'))
;
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu,
ben_prtt_enrt_rslt_f pen,
ben_enrt_perd enp,
ben_popl_enrt_typ_cycl_f pet
where ctu.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
/* First join comp objects */
and pen.business_group_id = ctu.business_group_id
and pen.per_in_ler_id = p_per_in_ler_id
and pen.ler_id = nvl(p_ler_id,pen.ler_id)
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
/* Use nvl here as only pgm or pl can be populated */
and nvl(ctu.ler_id,pen.ler_id) = pen.ler_id
and nvl(ctu.pgm_id,nvl(pen.pgm_id,-1)) = nvl(pen.pgm_id,-1)
and nvl(ctu.pl_id,pen.pl_id) = pen.pl_id
/* Now join in enrollment period */
/* PB : 5422 :
and (p_enrt_perd_id is null or
nvl(ctu.enrt_perd_id,p_enrt_perd_id) = p_enrt_perd_id)
*/
and (p_asnd_lf_evt_dt is null or
nvl(enp.asnd_lf_evt_dt, p_asnd_lf_evt_dt) = p_asnd_lf_evt_dt)
and ctu.enrt_perd_id = enp.enrt_perd_id(+)
and nvl(enp.business_group_id,ctu.business_group_id)
= ctu.business_group_id
/* Join in enrollment type cycle */
and enp.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id (+)
and nvl(pet.business_group_id,ctu.business_group_id)
= ctu.business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between nvl(pet.effective_start_date,nvl(p_lf_evt_ocrd_dt,
p_effective_date))
and nvl(pet.effective_end_date,nvl(p_lf_evt_ocrd_dt,
p_effective_date))
/* Use nvl here as only pgm pl can be populated */
and nvl(ctu.pl_id,-1) = nvl(pet.pl_id,nvl(ctu.pl_id,-1))
and nvl(ctu.pgm_id,-1) = nvl(pet.pgm_id,nvl(ctu.pgm_id,-1))
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and pen.enrt_cvg_thru_dt < hr_api.g_eot
and exists (select null
from ben_elig_cvrd_dpnt_f pdp,
ben_per_in_ler pil
where pdp.dpnt_person_id = p_rcpent_person_id
and pdp.business_group_id = pen.business_group_id
and pdp.effective_start_date = p_effective_date
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pil.per_in_ler_id=pdp.per_in_ler_id
and pil.business_group_id=pdp.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT'));
select null
from ben_prtt_enrt_rslt_f pen
where pen.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.per_in_ler_id = p_per_in_ler_id
and pen.enrt_cvg_thru_dt < hr_api.g_eot
and exists (select null
from ben_elig_cvrd_dpnt_f pdp,
ben_per_in_ler pil
where pdp.dpnt_person_id = p_rcpent_person_id
and pdp.business_group_id = pen.business_group_id
and pdp.effective_start_date = p_effective_date
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pil.per_in_ler_id=pdp.per_in_ler_id
and pil.business_group_id=pdp.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT'))
;
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu,
ben_prtt_enrt_rslt_f pen,
ben_pil_elctbl_chc_popl pel
where ctu.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
/* First join comp objects */
and pen.business_group_id = ctu.business_group_id
and pen.per_in_ler_id = p_per_in_ler_id
and pen.per_in_ler_id = pel.per_in_ler_id
and pen.enrt_mthd_cd = p_enrt_mthd_cd
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
and pel.business_group_id = pen.business_group_id
and nvl(ctu.ler_id,pen.ler_id) = pen.ler_id
and nvl(ctu.pgm_id,nvl(pen.pgm_id,-1)) = nvl(pen.pgm_id,-1)
and nvl(ctu.pl_typ_id,nvl(pen.pl_typ_id,-1)) = nvl(pen.pl_typ_id,-1)
and nvl(ctu.pl_id,pen.pl_id) = pen.pl_id
/* Now join in enrollment period */
and ( (ctu.enrt_perd_id = pel.enrt_perd_id
and ((nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
and pel.pgm_id is null
) or
nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)
)
) or
(ctu.enrt_perd_id is null)
) -- ???? 5422 : No need to join to ben_enrt_perd
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and exists (select null
from ben_pl_bnf_f pbn
where pbn.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pbn.bnf_person_id = p_rcpent_person_id
and pbn.business_group_id = pel.business_group_id
and p_effective_date
between pbn.effective_start_date
and pbn.effective_end_date);
select null
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and pen.business_group_id = pil.business_group_id
and pen.per_in_ler_id = pil.per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_mthd_cd = p_enrt_mthd_cd
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
and exists (select null
from ben_pl_bnf_f pbn
where pbn.per_in_ler_id = pil.per_in_ler_id
and pbn.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pbn.bnf_person_id = p_rcpent_person_id
and pbn.business_group_id = pil.business_group_id
and pbn.effective_start_date = p_effective_date);
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu,
ben_prtt_enrt_rslt_f pen,
ben_pil_elctbl_chc_popl pel
where ctu.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
/* First join comp objects */
and pen.business_group_id = ctu.business_group_id
and pen.per_in_ler_id = p_per_in_ler_id
and pen.per_in_ler_id = pel.per_in_ler_id
and pen.enrt_mthd_cd = p_enrt_mthd_cd
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
and pel.business_group_id = pen.business_group_id
and nvl(ctu.ler_id,pen.ler_id) = pen.ler_id
and nvl(ctu.pgm_id,nvl(pen.pgm_id,-1)) = nvl(pen.pgm_id,-1)
and nvl(ctu.pl_id,pen.pl_id) = pen.pl_id
and nvl(ctu.pl_typ_id,nvl(pen.pl_typ_id,-1)) = nvl(pen.pl_typ_id,-1)
/* Now join in enrollment period */
and ((ctu.enrt_perd_id = pel.enrt_perd_id
and ((nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
and pel.pgm_id is null)
or nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)))
or (ctu.enrt_perd_id is null))
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and exists (select null
from ben_elig_cvrd_dpnt_f pdp
where pdp.per_in_ler_id = pel.per_in_ler_id
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pdp.dpnt_person_id = p_rcpent_person_id
and pdp.business_group_id = pel.business_group_id
and pdp.effective_start_date = p_effective_date);
select null
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and pen.business_group_id = pil.business_group_id
and pen.per_in_ler_id = pil.per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_mthd_cd = p_enrt_mthd_cd
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
and exists (select null
from ben_elig_cvrd_dpnt_f pdp
where pdp.per_in_ler_id = pil.per_in_ler_id
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pdp.dpnt_person_id = p_rcpent_person_id
and pdp.business_group_id = pil.business_group_id
and pdp.effective_start_date = p_effective_date);
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu,
ben_pil_elctbl_chc_popl pel
where ctu.business_group_id = p_business_group_id
and pel.business_group_id = ctu.business_group_id
and pel.per_in_ler_id = p_per_in_ler_id
/* Use nvl here as only pgm or pl can be populated */
and (p_ler_id is null or
nvl(ctu.ler_id,p_ler_id) = p_ler_id)
and nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)
and nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
and nvl(ctu.enrt_perd_id,nvl(pel.enrt_perd_id,-1))
= nvl(pel.enrt_perd_id,-1)
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and exists (select null
from ben_elig_cvrd_dpnt_f pdp
where pdp.dpnt_person_id = p_rcpent_person_id
and pdp.business_group_id = pel.business_group_id
and pdp.per_in_ler_id = pel.per_in_ler_id
and p_effective_date between pdp.effective_start_date
and pdp.effective_end_date);
select null
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and epe.business_group_id = pil.business_group_id
and epe.per_in_ler_id = pil.per_in_ler_id
and exists (select null
from ben_elig_cvrd_dpnt_f pdp
where pdp.dpnt_person_id = p_rcpent_person_id
and pdp.business_group_id = pil.business_group_id
and pdp.per_in_ler_id = pil.per_in_ler_id
and pdp.effective_start_date = p_effective_date);
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu,
ben_pil_elctbl_chc_popl pel
where ctu.business_group_id = p_business_group_id
and pel.business_group_id = ctu.business_group_id
and pel.per_in_ler_id = p_per_in_ler_id
/* Use nvl here as only pgm or pl can be populated */
and (p_ler_id is null or
nvl(ctu.ler_id,p_ler_id) = p_ler_id)
and nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)
and nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
and nvl(ctu.enrt_perd_id,nvl(pel.enrt_perd_id,-1))
= nvl(pel.enrt_perd_id,-1)
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and exists (select null
from ben_pl_bnf_f pbn
where pbn.bnf_person_id = p_rcpent_person_id
and pbn.business_group_id = pel.business_group_id
and pbn.per_in_ler_id = pel.per_in_ler_id
and pbn.effective_start_date = p_effective_date);
select null
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and epe.business_group_id = pil.business_group_id
and epe.per_in_ler_id = pil.per_in_ler_id
and exists (select null
from ben_pl_bnf_f pbn
where pbn.bnf_person_id = p_rcpent_person_id
and pbn.business_group_id = pil.business_group_id
and pbn.per_in_ler_id = pil.per_in_ler_id
and pbn.effective_start_date = p_effective_date);
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu
where ctu.business_group_id = p_business_group_id
and (p_ler_id is null or
nvl(ctu.ler_id,p_ler_id) = p_ler_id)
and (p_pgm_id is null or
nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
and (p_pl_id is null or
nvl(ctu.pl_id,p_pl_id) = p_pl_id)
and (p_pl_typ_id is null or
nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
and (p_actn_typ_id is null or
nvl(ctu.actn_typ_id,p_actn_typ_id) = p_actn_typ_id)
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and exists (select null
from ben_pl_bnf_f pbn,
ben_prtt_enrt_rslt_f pen,
ben_prtt_enrt_actn_f pea
where pbn.business_group_id = ctu.business_group_id
and pbn.per_in_ler_id = p_per_in_ler_id
and pbn.bnf_person_id = p_rcpent_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pbn.effective_start_date = p_effective_date
and pbn.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.business_group_id = pbn.business_group_id
and pea.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pea.pl_bnf_id = pbn.pl_bnf_id
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
and p_effective_date between
pea.effective_start_date and pea.effective_end_date
and pea.actn_typ_id =
nvl(ctu.actn_typ_id, pea.actn_typ_id)
and nvl(pen.pgm_id,-1) =
nvl(ctu.pgm_id,nvl(pen.pgm_id,-1))
and pen.pl_id =
nvl(ctu.pl_id,pen.pl_id));
select null
from sys.dual
where exists (select null
from ben_pl_bnf_f pbn,
ben_prtt_enrt_rslt_f pen,
ben_prtt_enrt_actn_f pea
where pbn.business_group_id = p_business_group_id
and pbn.per_in_ler_id = p_per_in_ler_id
and pbn.bnf_person_id = p_rcpent_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pbn.effective_start_date = p_effective_date
and pbn.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.business_group_id = pbn.business_group_id
and pea.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pea.pl_bnf_id = pbn.pl_bnf_id
and p_effective_date between
pea.effective_start_date and pea.effective_end_date
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
and (p_actn_typ_id is null or
pea.actn_typ_id = p_actn_typ_id)
and (p_pgm_id is null or
pen.pgm_id = p_pgm_id)
and (p_pl_id is null or
pen.pl_id = p_pl_id));
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu
where ctu.business_group_id = p_business_group_id
and (p_ler_id is null or
nvl(ctu.ler_id,p_ler_id) = p_ler_id)
and (p_pgm_id is null or
nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
and (p_pl_id is null or
nvl(ctu.pl_id,p_pl_id) = p_pl_id)
and (p_pl_typ_id is null or
nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
and (p_actn_typ_id is null or
nvl(ctu.actn_typ_id,p_actn_typ_id) = p_actn_typ_id)
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and exists (select null
from ben_elig_cvrd_dpnt pdp,
ben_prtt_enrt_rslt_f pen,
ben_prtt_enrt_actn_f pea
where pdp.business_group_id = ctu.business_group_id
and pdp.per_in_ler_id = p_per_in_ler_id
and pdp.dpnt_person_id = p_rcpent_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pdp.effective_start_date = p_effective_date
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.business_group_id = pdp.business_group_id
and pea.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pea.elig_cvrd_dpnt_id = pdp.elig_cvrd_dpnt_id
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
and p_effective_date between
pea.effective_start_date and pea.effective_end_date
and pea.actn_typ_id =
nvl(ctu.actn_typ_id, pea.actn_typ_id)
and nvl(pen.pgm_id,-1) =
nvl(ctu.pgm_id,nvl(pen.pgm_id,-1))
and pen.pl_id =
nvl(ctu.pl_id,pen.pl_id));
select null
from sys.dual
where exists (select null
from ben_elig_cvrd_dpnt_f pdp,
ben_prtt_enrt_rslt_f pen,
ben_prtt_enrt_actn_f pea
where pdp.business_group_id = p_business_group_id
and pdp.per_in_ler_id = p_per_in_ler_id
and pdp.dpnt_person_id = p_rcpent_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pdp.effective_start_date = p_effective_date
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.business_group_id = pdp.business_group_id
and pea.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pea.elig_cvrd_dpnt_id = pdp.elig_cvrd_dpnt_id
and p_effective_date between
pea.effective_start_date and pea.effective_end_date
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
and (p_actn_typ_id is null or
pea.actn_typ_id = p_actn_typ_id)
and (p_pgm_id is null or
pen.pgm_id = p_pgm_id)
and (p_pl_id is null or
pen.pl_id = p_pl_id));
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu
where ctu.business_group_id = p_business_group_id
and (p_pgm_id is null or
nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
and (p_pl_id is null or
nvl(ctu.pl_id,p_pl_id) = p_pl_id)
and (p_pl_typ_id is null or
nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and exists (select null
from ben_elig_cvrd_dpnt_f pdp,
ben_prtt_enrt_rslt_f pen
where pdp.business_group_id = ctu.business_group_id
and pdp.dpnt_person_id = p_rcpent_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pdp.effective_start_date = p_effective_date
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.business_group_id = pdp.business_group_id
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
and nvl(pen.pgm_id,-1) =
nvl(ctu.pgm_id,nvl(pen.pgm_id,-1))
and pen.pl_id =
nvl(ctu.pl_id,pen.pl_id));
select null
from ben_elig_cvrd_dpnt_f pdp,
ben_prtt_enrt_rslt_f pen
where pdp.business_group_id = p_business_group_id
and pdp.dpnt_person_id = p_rcpent_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pdp.effective_start_date = p_effective_date
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.business_group_id = pdp.business_group_id
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date;
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu
where ctu.business_group_id = p_business_group_id
and (p_pgm_id is null or
nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
and (p_pl_id is null or
nvl(ctu.pl_id,p_pl_id) = p_pl_id)
and (p_pl_typ_id is null or
nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and exists (select null
from ben_elig_cvrd_dpnt_f pdp,
ben_prtt_enrt_rslt_f pen
where pdp.business_group_id = ctu.business_group_id
and pdp.per_in_ler_id = p_per_in_ler_id
and pdp.dpnt_person_id = p_rcpent_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pdp.effective_start_date = p_effective_date
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.business_group_id = pdp.business_group_id
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
and nvl(pen.pgm_id,-1) =
nvl(ctu.pgm_id,nvl(pen.pgm_id,-1))
and pen.pl_id =
nvl(ctu.pl_id,pen.pl_id));
select null
from sys.dual
where exists (select null
from ben_elig_cvrd_dpnt_f pdp,
ben_prtt_enrt_rslt_f pen
where pdp.business_group_id = p_business_group_id
and pdp.per_in_ler_id = p_per_in_ler_id
and pdp.dpnt_person_id = p_rcpent_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pdp.effective_start_date = p_effective_date
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.business_group_id = pdp.business_group_id
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
and (p_pgm_id is null or
pen.pgm_id = p_pgm_id)
and (p_pl_id is null or
pen.pl_id = p_pl_id));
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu
where ctu.business_group_id = p_business_group_id
and (p_pgm_id is null or
nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
and (p_pl_id is null or
nvl(ctu.pl_id,p_pl_id) = p_pl_id)
and (p_pl_typ_id is null or
nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and exists (select null
from ben_pl_bnf_f pbn,
ben_prtt_enrt_rslt_f pen
where pbn.business_group_id = ctu.business_group_id
and pbn.per_in_ler_id = p_per_in_ler_id
and pbn.bnf_person_id = p_rcpent_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pbn.effective_start_date = p_effective_date
and pbn.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.business_group_id = pbn.business_group_id
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
and nvl(pen.pgm_id,-1) =
nvl(ctu.pgm_id,nvl(pen.pgm_id,-1))
and pen.pl_id =
nvl(ctu.pl_id,pen.pl_id));
select null
from sys.dual
where exists (select null
from ben_pl_bnf_f pbn,
ben_prtt_enrt_rslt_f pen
where pbn.business_group_id = p_business_group_id
and pbn.per_in_ler_id = p_per_in_ler_id
and pbn.bnf_person_id = p_rcpent_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pbn.effective_start_date = p_effective_date
and pbn.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.business_group_id = pbn.business_group_id
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
and (p_pgm_id is null or
pen.pgm_id = p_pgm_id)
and (p_pl_id is null or
pen.pl_id = p_pl_id));
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu,
ben_pil_elctbl_chc_popl pel
where ctu.business_group_id = p_business_group_id
and pel.business_group_id = ctu.business_group_id
and (p_ler_id is null or
nvl(ctu.ler_id,p_ler_id) = p_ler_id)
and (p_pl_id is null or
nvl(ctu.pl_id,p_pl_id) = p_pl_id)
and (p_pgm_id is null or
nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
and (p_pl_typ_id is null or
nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
/* PB : 5422 :
and (p_enrt_perd_id is null or
nvl(ctu.enrt_perd_id,p_enrt_perd_id) = p_enrt_perd_id)
*/
and (p_asnd_lf_evt_dt is null or
ctu.enrt_perd_id is null or
ctu.enrt_perd_id =
( select enp.enrt_perd_id
from ben_enrt_perd enp
where enp.asnd_lf_evt_dt = enp.asnd_lf_evt_dt
and enp.enrt_perd_id = pel.enrt_perd_id
and enp.business_group_id = p_business_group_id
)
)
/* Now join in enrollment period */
and pel.per_in_ler_id = p_per_in_ler_id
and nvl(ctu.enrt_perd_id,nvl(pel.enrt_perd_id,-1))
= nvl(pel.enrt_perd_id, -1)
and nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
and nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and exists (select null
from ben_elig_cvrd_dpnt_f pdp
where pdp.business_group_id = pel.business_group_id
and pdp.dpnt_person_id = p_rcpent_person_id
and pdp.effective_start_date = p_effective_date
and pdp.per_in_ler_id = pel.per_in_ler_id);
select null
from sys.dual
where exists (select null
from ben_elig_cvrd_dpnt_f pdp
where pdp.business_group_id = p_business_group_id
and pdp.dpnt_person_id = p_rcpent_person_id
and pdp.effective_start_date = p_effective_date
and pdp.per_in_ler_id = p_per_in_ler_id);
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu,
ben_pil_elctbl_chc_popl pel
where ctu.business_group_id = p_business_group_id
and pel.business_group_id = ctu.business_group_id
and (p_ler_id is null or
nvl(ctu.ler_id,p_ler_id) = p_ler_id)
and (p_pl_id is null or
nvl(ctu.pl_id,p_pl_id) = p_pl_id)
and (p_pgm_id is null or
nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
and (p_pl_typ_id is null or
nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
/* PB : 5422 :
and (p_enrt_perd_id is null or
nvl(ctu.enrt_perd_id,p_enrt_perd_id) = p_enrt_perd_id)
*/
and (p_asnd_lf_evt_dt is null or
ctu.enrt_perd_id is null or
ctu.enrt_perd_id =
( select enp.enrt_perd_id
from ben_enrt_perd enp
where enp.asnd_lf_evt_dt = enp.asnd_lf_evt_dt
and enp.enrt_perd_id = pel.enrt_perd_id
and enp.business_group_id = p_business_group_id
)
)
/* Now join in enrollment period */
and pel.per_in_ler_id = p_per_in_ler_id
and nvl(ctu.enrt_perd_id,nvl(pel.enrt_perd_id,-1))
= nvl(pel.enrt_perd_id, -1)
and nvl(ctu.pl_id,nvl(pel.pl_id,-1)) = nvl(pel.pl_id,-1)
and nvl(ctu.pgm_id,nvl(pel.pgm_id,-1)) = nvl(pel.pgm_id,-1)
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and exists (select null
from ben_pl_bnf_f pbn
where pbn.business_group_id = pel.business_group_id
and pbn.bnf_person_id = p_rcpent_person_id
and pbn.effective_start_date = p_effective_date
and pbn.per_in_ler_id = pel.per_in_ler_id);
select null
from sys.dual
where exists (select null
from ben_pl_bnf_f pbn
where pbn.business_group_id = p_business_group_id
and pbn.bnf_person_id = p_rcpent_person_id
and pbn.effective_start_date = p_effective_date
and pbn.per_in_ler_id = p_per_in_ler_id);
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu
where ctu.business_group_id = p_business_group_id
and (p_pl_id is null or
nvl(ctu.pl_id,p_pl_id) = p_pl_id)
and (p_pgm_id is null or
nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
and (p_pl_typ_id is null or
nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
and ben_generate_communications.g_comm_start_date between
ctu.effective_start_date and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and exists (select null
from ben_elig_cvrd_dpnt_f pdp,
ben_prtt_enrt_rslt_f pen
where pdp.business_group_id = ctu.business_group_id
and pdp.per_in_ler_id = p_per_in_ler_id
and pdp.dpnt_person_id = p_rcpent_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pdp.effective_start_date and pdp.effective_end_date
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.business_group_id = pdp.business_group_id
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
and nvl(pen.pgm_id,-1) =
nvl(ctu.pgm_id,nvl(pen.pgm_id,-1))
and pen.pl_id =
nvl(ctu.pl_id,pen.pl_id));
select null
from sys.dual
where exists (select null
from ben_elig_cvrd_dpnt_f pdp,
ben_prtt_enrt_rslt_f pen
where pdp.business_group_id = p_business_group_id
and pdp.per_in_ler_id = p_per_in_ler_id
and pdp.dpnt_person_id = p_rcpent_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pdp.effective_start_date and pdp.effective_end_date
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.business_group_id = pdp.business_group_id
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
and (p_pgm_id is null or
pen.pgm_id = p_pgm_id)
and (p_pl_id is null or
pen.pl_id = p_pl_id));
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu
where ctu.business_group_id = p_business_group_id
and (p_pl_id is null or
nvl(ctu.pl_id,p_pl_id) = p_pl_id)
and (p_pgm_id is null or
nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
and (p_pl_typ_id is null or
nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
and ben_generate_communications.g_comm_start_date between
ctu.effective_start_date and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL'
and exists (select null
from ben_pl_bnf_f pbn,
ben_prtt_enrt_rslt_f pen
where pbn.business_group_id = ctu.business_group_id
and pbn.per_in_ler_id = p_per_in_ler_id
and pbn.bnf_person_id = p_rcpent_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pbn.effective_start_date and pbn.effective_end_date
and pbn.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.business_group_id = pbn.business_group_id
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
and nvl(pen.pgm_id,-1) =
nvl(ctu.pgm_id,nvl(pen.pgm_id,-1))
and pen.pl_id =
nvl(ctu.pl_id,pen.pl_id));
select null
from sys.dual
where exists (select null
from ben_pl_bnf_f pbn,
ben_prtt_enrt_rslt_f pen
where pbn.business_group_id = p_business_group_id
and pbn.per_in_ler_id = p_per_in_ler_id
and pbn.bnf_person_id = p_rcpent_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pbn.effective_start_date and pbn.effective_end_date
and pbn.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.business_group_id = pbn.business_group_id
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
and (p_pgm_id is null or
pen.pgm_id = p_pgm_id)
and (p_pl_id is null or
pen.pl_id = p_pl_id));
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl
from ben_cm_typ_usg_f ctu
where ctu.business_group_id = p_business_group_id
and (p_ler_id is null or
nvl(ctu.ler_id,p_ler_id) = p_ler_id)
and (p_pgm_id is null or
nvl(ctu.pgm_id,p_pgm_id) = p_pgm_id)
and (p_pl_id is null or
nvl(ctu.pl_id,p_pl_id) = p_pl_id)
and (p_pl_typ_id is null or
nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
/* PB : 5422 :
and (p_enrt_perd_id is null or
nvl(ctu.enrt_perd_id,p_enrt_perd_id) = p_enrt_perd_id)
*/
and (p_asnd_lf_evt_dt is null or
ctu.enrt_perd_id is null or
ctu.enrt_perd_id =
( select enp.enrt_perd_id
from ben_enrt_perd enp,
ben_popl_enrt_typ_cycl_f pet
where enp.asnd_lf_evt_dt = enp.asnd_lf_evt_dt
and enp.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id
and (p_pl_id is null or
nvl(pet.pl_id,p_pl_id) = p_pl_id)
and (p_pgm_id is null or
nvl(pet.pgm_id,p_pgm_id) = p_pgm_id)
and pet.business_group_id = enp.business_group_id
and enp.business_group_id = p_business_group_id
and p_effective_date between pet.effective_start_date
and pet.effective_end_date
)
)
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
and ctu.cm_typ_id = p_cm_typ_id
and ctu.all_r_any_cd = 'ALL';
select pen.pgm_id, pen.pl_typ_id, max(pen.enrt_cvg_thru_dt)
from ben_elig_cvrd_dpnt_f pdp,
ben_prtt_enrt_rslt_f pen,
ben_pl_f pln,
ben_pl_regn_f plrg,
ben_regn_f regn,
ben_oipl_f cop,
ben_opt_f opt
where pdp.per_in_ler_id = p_per_in_ler_id
and pdp.dpnt_person_id = p_rcpent_person_id
and pdp.cvg_thru_dt <> hr_api.g_eot
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.sspndd_flag = 'N'
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pdp.effective_start_date and pdp.effective_end_date
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and pen.pl_id = pln.pl_id
and pln.invk_dcln_prtn_pl_flag = 'N'
and p_effective_date between
pln.effective_start_date and pln.effective_end_date
and plrg.pl_id = pln.pl_id
and plrg.regn_id = regn.regn_id
and regn.sttry_citn_name = 'HIPAA'
and p_effective_date between
plrg.effective_start_date and plrg.effective_end_date
and p_effective_date between
regn.effective_start_date and regn.effective_end_date
and pen.oipl_id = cop.oipl_id (+)
and p_effective_date between
nvl(cop.effective_start_date, p_effective_date) and
nvl(cop.effective_end_date, p_effective_date)
and nvl(cop.opt_id, -1) = opt.opt_id (+)
and nvl(opt.invk_wv_opt_flag, 'N') = 'N'
and p_effective_date between
nvl(opt.effective_start_date, p_effective_date) and
nvl(opt.effective_end_date, p_effective_date)
group by pen.pgm_id, pen.pl_typ_id;
select max(pen.enrt_cvg_thru_dt)
from ben_elig_cvrd_dpnt_f pdp,
ben_prtt_enrt_rslt_f pen,
ben_pl_f pln,
ben_pl_regn_f plrg,
ben_regn_f regn,
ben_oipl_f cop,
ben_opt_f opt
where pdp.effective_start_date = p_effective_date
and pdp.dpnt_person_id = p_rcpent_person_id
and pdp.request_id = fnd_global.conc_request_id
and pdp.cvg_thru_dt <> hr_api.g_eot
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.pgm_id = p_pgm_id
and pen.pl_typ_id = p_pl_typ_id
and pen.sspndd_flag = 'N'
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pdp.effective_start_date and pdp.effective_end_date
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and pen.pl_id = pln.pl_id
and pln.invk_dcln_prtn_pl_flag = 'N'
and p_effective_date between
pln.effective_start_date and pln.effective_end_date
and plrg.pl_id = pln.pl_id
and plrg.regn_id = regn.regn_id
and regn.sttry_citn_name = 'HIPAA'
and p_effective_date between
plrg.effective_start_date and plrg.effective_end_date
and p_effective_date between
regn.effective_start_date and regn.effective_end_date
and pen.oipl_id = cop.oipl_id (+)
and p_effective_date between
nvl(cop.effective_start_date, p_effective_date) and
nvl(cop.effective_end_date, p_effective_date)
and nvl(cop.opt_id, -1) = opt.opt_id (+)
and nvl(opt.invk_wv_opt_flag, 'N') = 'N'
and p_effective_date between
nvl(opt.effective_start_date, p_effective_date) and
nvl(opt.effective_end_date, p_effective_date)
group by pen.pgm_id, pen.pl_typ_id;
select 'Y'
from ben_elig_dpnt egd,
ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil,
ben_pl_f pln,
ben_oipl_f cop,
ben_opt_f opt
where epe.per_in_ler_id = p_per_in_ler_id
and nvl(epe.pgm_id,-1) = nvl(l_pgm_id,-1)
and epe.pl_typ_id = l_pl_typ_id
and epe.elctbl_flag = 'Y'
and epe.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and epe.elig_per_elctbl_chc_id = egd.elig_per_elctbl_chc_id
and egd.dpnt_person_id = p_rcpent_person_id
and egd.dpnt_inelig_flag = 'N'
and epe.pl_id = pln.pl_id
and pln.invk_dcln_prtn_pl_flag = 'N'
and p_effective_date between
pln.effective_start_date and pln.effective_end_date
and epe.oipl_id = cop.oipl_id (+)
and p_effective_date between
nvl(cop.effective_start_date, p_effective_date) and
nvl(cop.effective_end_date, p_effective_date)
and nvl(cop.opt_id, -1) = opt.opt_id (+)
and nvl(opt.invk_wv_opt_flag, 'N') = 'N'
and p_effective_date between
nvl(opt.effective_start_date, p_effective_date) and
nvl(opt.effective_end_date, p_effective_date);
select 'Y'
from ben_prtt_enrt_rslt_f pen
,ben_pl_f pln
,ben_oipl_f cop
,ben_opt_f opt
,ben_pl_regn_f plrg
,ben_regn_f regn
where pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.pl_typ_id = l_pl_typ_id
and pen.sspndd_flag = 'N'
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
and pen.business_group_id = p_business_group_id
and pen.pl_id = pln.pl_id
and pln.invk_dcln_prtn_pl_flag = 'N'
and p_effective_date between pln.effective_start_date
and pln.effective_end_date
and pln.business_group_id = pen.business_group_id
and pln.pl_id = plrg.pl_id
and plrg.regn_id = regn.regn_id
and regn.sttry_citn_name = 'HIPAA'
and p_effective_date between plrg.effective_start_date
and plrg.effective_end_date
and p_effective_date between regn.effective_start_date
and regn.effective_end_date
and pen.oipl_id = cop.oipl_id (+)
and pen.business_group_id = cop.business_group_id (+)
and p_effective_date between
cop.effective_start_date (+)
and cop.effective_end_date (+)
and cop.opt_id = opt.opt_id (+)
and nvl(opt.invk_wv_opt_flag,'N') = 'N'
and cop.business_group_id = opt.business_group_id (+)
and p_effective_date between
opt.effective_start_date (+)
and opt.effective_end_date (+);
select 'Y'
from ben_elig_cvrd_dpnt_f pdp,
ben_prtt_enrt_rslt_f pen,
ben_pl_f pln,
ben_oipl_f cop,
ben_opt_f opt
where pdp.cvg_thru_dt = hr_api.g_eot
and pdp.dpnt_person_id = p_rcpent_person_id
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
-- and nvl(pen.pgm_id,-1) = nvl(l_pgm_id,-1) maagrawa(02/11/00)
and pen.pl_typ_id = l_pl_typ_id
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.sspndd_flag = 'N'
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pdp.effective_start_date and pdp.effective_end_date
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and pen.pl_id = pln.pl_id
and pln.invk_dcln_prtn_pl_flag = 'N'
and p_effective_date between
pln.effective_start_date and pln.effective_end_date
and pen.oipl_id = cop.oipl_id (+)
and p_effective_date between
nvl(cop.effective_start_date, p_effective_date) and
nvl(cop.effective_end_date, p_effective_date)
and nvl(cop.opt_id, -1) = opt.opt_id (+)
and nvl(opt.invk_wv_opt_flag, 'N') = 'N'
and p_effective_date between
nvl(opt.effective_start_date, p_effective_date) and
nvl(opt.effective_end_date, p_effective_date);
select 'Y'
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil,
ben_pl_f pln,
ben_oipl_f cop,
ben_opt_f opt
where epe.per_in_ler_id = p_per_in_ler_id
and nvl(epe.pgm_id,-1) = nvl(v_pgm_id,-1)
and epe.pl_typ_id = v_pl_typ_id
and epe.elctbl_flag = 'Y'
and epe.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and epe.pl_id = pln.pl_id
and pln.invk_dcln_prtn_pl_flag = 'N'
and p_effective_date between
pln.effective_start_date and pln.effective_end_date
and epe.oipl_id = cop.oipl_id (+)
and p_effective_date between
nvl(cop.effective_start_date, p_effective_date) and
nvl(cop.effective_end_date, p_effective_date)
and nvl(cop.opt_id, -1) = opt.opt_id (+)
and nvl(opt.invk_wv_opt_flag, 'N') = 'N'
and p_effective_date between
nvl(opt.effective_start_date, p_effective_date) and
nvl(opt.effective_end_date, p_effective_date);
SELECT pil.*
FROM ben_per_in_ler pil, ben_ler_f ler
WHERE pil.person_id = p_person_id
AND pil.business_group_id = p_business_group_id
AND pil.per_in_ler_stat_cd = 'STRTD'
AND ler.ler_id = pil.ler_id
AND ler.ler_id = NVL (p_ler_id, pil.ler_id)
AND ler.typ_cd not in ('COMP', 'GSP', 'ABS')
AND p_effective_date BETWEEN
ler.effective_start_date AND ler.effective_end_date
ORDER BY DECODE(ler.typ_cd,'SCHEDDU',1,2) desc ;