[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pcm.per_cm_id
from ben_per_cm_f pcm, ben_per_in_ler pil
-- if commu table has no ler id dont compare , pil_id take care
-- of validation # 3296015
where (pcm.ler_id is null or pcm.ler_id = c_ler_id)
and nvl(pcm.prtt_enrt_actn_id,-1) = nvl(c_pna_id,-1)
and nvl(pcm.person_id,-1) = nvl(c_per_id,-1)
and nvl(pcm.bnf_person_id,-1) = nvl(c_bnfper_id,-1)
and nvl(pcm.dpnt_person_id,-1) = nvl(c_dpntper_id,-1)
and nvl(pcm.cm_typ_id,-1) = nvl(c_cm_typ_id,-1)
and nvl(pcm.per_in_ler_id,-1) = nvl(c_pil_id,-1)
and nvl(pcm.lf_evt_ocrd_dt,nvl(c_leo_dt,c_eff_dt)) =
nvl(c_leo_dt,c_eff_dt)
and pcm.business_group_id = c_bgp_id
and c_comm_sdt
between pcm.effective_start_date
and pcm.effective_end_date
and pil.per_in_ler_id(+) = pcm.per_in_ler_id
and nvl(pil.business_group_id,c_bgp_id) =
c_bgp_id
and nvl(pil.per_in_ler_stat_cd,'-1') not in ('VOIDD', 'BCKDT');
select pcm.per_cm_id
from ben_per_cm_f pcm,ben_per_cm_prvdd_f pcpf
where pcm.per_in_ler_id = c_pil_id
-- if commu table has no ler id dont compare , pil_id take care
-- of validation # 3296015
and (pcm.ler_id is null or pcm.ler_id = c_ler_id)
and nvl(pcm.prtt_enrt_actn_id,-1) = nvl(c_pna_id,-1)
and nvl(pcm.person_id,-1) = nvl(c_per_id,-1)
and nvl(pcm.bnf_person_id,-1) = nvl(c_bnfper_id,-1)
and nvl(pcm.dpnt_person_id,-1) = nvl(c_dpntper_id,-1)
and nvl(pcm.cm_typ_id,-1) = nvl(c_cm_typ_id,-1)
and pcm.per_cm_id = pcpf.per_cm_id
and pcpf.per_cm_prvdd_stat_cd <> 'VOID'
-- Bug :7629124, Check to see if Communication is VOIDed.If VOIDed, then create a new Communication for the same LE
-- Added ben_per_cm_prvdd_f table to the cursor for verification
and nvl(pcm.lf_evt_ocrd_dt,nvl(c_leo_dt,c_eff_dt)) =
nvl(c_leo_dt,c_eff_dt)
and c_comm_sdt
between pcm.effective_start_date and pcm.effective_end_date;
select pcm.per_cm_id
from ben_per_cm_f pcm
where pcm.person_id = c_per_id
-- if commu table has no ler id dont compare , pil_id take care
-- of validation # 3296015
and (pcm.ler_id is null or pcm.ler_id = c_ler_id)
and nvl(pcm.prtt_enrt_actn_id,-1) = nvl(c_pna_id,-1)
and nvl(pcm.bnf_person_id,-1) = nvl(c_bnfper_id,-1)
and nvl(pcm.dpnt_person_id,-1) = nvl(c_dpntper_id,-1)
and nvl(pcm.cm_typ_id,-1) = nvl(c_cm_typ_id,-1)
and nvl(pcm.lf_evt_ocrd_dt,nvl(c_leo_dt,c_eff_dt)) =
nvl(c_leo_dt,c_eff_dt)
and c_comm_sdt
between pcm.effective_start_date and pcm.effective_end_date;
SELECT effective_start_date
FROM ben_prtt_enrt_actn_f
WHERE prtt_enrt_actn_id = p_prtt_enrt_actn_id;
SELECT oipl_id
FROM ben_prtt_enrt_rslt_f
WHERE prtt_enrt_rslt_id IN (
SELECT prtt_enrt_rslt_id
FROM ben_prtt_enrt_actn_f
WHERE prtt_enrt_actn_id =
p_prtt_enrt_actn_id);
select obj.ELIG_OBJ_ID from ben_elig_obj_f obj ,
ben_elig_obj_elig_profl_f obe
where p_eff_date between obj.effective_start_date
and obj.effective_end_date
and p_eff_date between obe.effective_start_date
and obe.effective_end_date
and obj.table_name = 'BEN_CM_TYP_F'
and obj.column_name = 'CM_TYP_ID'
and obj.column_value = p_cm_typ_id
and obj.business_group_id = p_business_group_id
and obe.business_group_id = p_business_group_id
and obj.ELIG_OBJ_ID = obe.ELIG_OBJ_ID;
,p_program_update_date => sysdate
);
select null
from ben_per_cm_trgr_f pcr
where pcr.cm_trgr_id = p_cm_trgr_id
and pcr.per_cm_id = p_per_cm_id
and pcr.business_group_id = p_business_group_id
and ben_generate_communications.g_comm_start_date
between pcr.effective_start_date
and pcr.effective_end_date;
select pcd.*
from ben_per_cm_prvdd_f pcd
where pcd.per_cm_id = p_per_cm_id
and pcd.sent_dt is null
and pcd.business_group_id = p_business_group_id
and ben_generate_communications.g_comm_start_date
between pcd.effective_start_date
and pcd.effective_end_date;
select max(pcd.instnc_num)
from ben_per_cm_prvdd_f pcd
where pcd.per_cm_id = p_per_cm_id
and pcd.business_group_id = p_business_group_id
and ben_generate_communications.g_comm_start_date
between pcd.effective_start_date
and pcd.effective_end_date;
select pcu.per_cm_usg_id
from ben_per_cm_usg_f pcu
where pcu.per_cm_id = p_per_cm_id
and pcu.cm_typ_usg_id = p_cm_typ_usg_id
and pcu.business_group_id = p_business_group_id
and ben_generate_communications.g_comm_start_date between
pcu.effective_start_date and pcu.effective_end_date;
select pgm_id,
pl_id
from ben_elig_per_f pep
where pep.per_in_ler_id = p_per_in_ler_id
and pep.elig_per_id = p_elig_per_id ;
select epe.fonm_cvg_strt_dt
from ben_pil_elctbl_chc_popl popl,
ben_elig_per_elctbl_chc epe
where popl.per_in_ler_id = p_per_in_ler_id
and popl.pgm_id = p_pgm_id
and epe.pil_elctbl_chc_popl_id = popl.pil_elctbl_chc_popl_id
and NVL(epe.pl_id,-1) = nvl(p_pl_id, nvl(epe.pl_id,-1)) -- 5633934 : Added this condition
and not exists (select 'x' from ben_pil_elctbl_chc_popl popl1, ben_elig_per_elctbl_chc epe1
where popl1.per_in_ler_id = p_per_in_ler_id
and popl1.pgm_id = p_pgm_id
and epe1.pil_elctbl_chc_popl_id = popl1.pil_elctbl_chc_popl_id
and NVL(epe1.pl_id,-1) = nvl(p_pl_id, nvl(epe1.pl_id,-1))
and epe.fonm_cvg_strt_dt > epe1.fonm_cvg_strt_dt); /*Added not exists clause for Bug 7268357*/
select epe.fonm_cvg_strt_dt
from ben_pil_elctbl_chc_popl popl,
ben_elig_per_elctbl_chc epe
where popl.per_in_ler_id = p_per_in_ler_id
and popl.pl_id = p_pl_id
and epe.pil_elctbl_chc_popl_id = popl.pil_elctbl_chc_popl_id;
select pil.per_in_ler_id
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id not in (c_per_in_ler_id)
and pil.person_id = p_person_id
and pil.ler_id = ler.ler_id
and p_effective_date between
ler.effective_start_date and ler.effective_end_date
and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
and pil.per_in_ler_stat_cd not in('BCKDT', 'VOIDD')
order by pil.lf_evt_ocrd_dt desc;
select pil.per_in_ler_id from ben_per_in_ler pil,ben_ler_f le
where pil.lf_evt_ocrd_dt=c_lf_evt_ocrd_dt
and pil.ler_id=le.ler_id
and pil.person_id=p_person_id
and c_lf_evt_ocrd_dt between le.effective_start_date and le.effective_end_date
and pil.per_in_ler_stat_cd NOT IN ( 'VOIDD', 'BCKDT')
and le.typ_cd not in ('IREC','GSP','COMP','ABS','SCHEDDU','SCHEDDA');
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl,
ctu.pl_id, -- Bug 1555557
ctu.pgm_id,
ctu.ler_id,
ctu.pl_typ_id,
get_cvg_strt_dt(pep.elig_per_id,pil.per_in_ler_id) cvg_dt,
pil.per_in_ler_id,
pep.elig_per_id,
pep.pgm_id ppgm_id,
pep.pl_id ppl_id
from ben_cm_typ_usg_f ctu,
ben_elig_per_f pep,
ben_per_in_ler pil
where ctu.business_group_id = p_business_group_id
and pep.business_group_id = ctu.business_group_id
and pep.person_id = p_person_id
and pil.per_in_ler_id = l_per_in_ler_id -- Bug 8227214
and pep.per_in_ler_id = pil.per_in_ler_id -- Bug 8227214
/*Commented condition for Bug 8227214 */
/*and nvl(get_cvg_strt_dt(pep.elig_per_id,pil.per_in_ler_id),cv_effective_date)
between pep.effective_start_date and pep.effective_end_date*/
and nvl(ctu.ler_id,nvl(pil.ler_id,-1)) = nvl(pil.ler_id,-1)
and nvl(ctu.pgm_id,nvl(pep.pgm_id,-1)) = nvl(pep.pgm_id,-1)
/* Bug 8809596: Pick the correct eligibility record instead of looping through all the
eligibility records when Plan Usage is Null or PlanType Usage is Null*/
and ( (ctu.pl_typ_id is not null and nvl(ctu.pl_id,nvl(pep.pl_id,-1)) = nvl(pep.pl_id,-1) )
or (ctu.pl_typ_id is null and nvl(ctu.pl_id,-1) = nvl(pep.pl_id,-1))
)
and (p_ler_id is null or
nvl(ctu.ler_id,p_ler_id) = p_ler_id)
and (p_pl_typ_id is null or
nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
/* Now join in enrollment period */
and (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
)
)
-- if pl_typ is in usages , validte the pl against pl_type
and (ctu.pl_typ_id is null or
exists
( select 'x'
from ben_pl_f pl
where pl.pl_id = pep.pl_id
and pl.pl_typ_id = ctu.pl_typ_id
and cv_effective_date between
pl.effective_start_date
and pl.effective_end_date
)
)
/* Use nvl here as only pgm pl can be populated */
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 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 pil.per_in_ler_id(+) = pep.per_in_ler_id
and nvl(pil.business_group_id,p_business_group_id) =
p_business_group_id
and nvl(pil.per_in_ler_stat_cd,'-1') not in ('VOIDD','BCKDT')
---look for previous eligble
and (( p_eligible_flag = 'Y'
and not exists ( SELECT 'x'
FROM ben_elig_per_f pep2, ben_per_in_ler pil2
WHERE pep2.person_id = pep.person_id
AND (ctu.pl_id is null or nvl(pep2.pl_id,-1) = nvl(pep.pl_id,-1) )
AND (ctu.pgm_id is null or nvl(pep2.pgm_id,-1) = nvl(pep.pgm_id,-1) )
--- pep ler id is not updated so pil ler id is validated # 2784972
/*Bug 9454579 : Uncommented the below condition*/
AND (ctu.ler_id is null or nvl(ctu.ler_id,-1) = nvl(pil2.ler_id,-1) )
AND (ctu.pl_typ_id is null or
(exists
( select 'x'
from ben_pl_f pl
where pl.pl_id = nvl(pep2.pl_id,pl.pl_id)
and pl.pl_typ_id = nvl(ctu.pl_typ_id,pl.pl_typ_id)
and cv_effective_date between
pl.effective_start_date
and pl.effective_end_date
)
) )
AND pep2.business_group_id = pep.business_group_id
AND pep2.elig_flag = 'Y'
AND pep.effective_start_date-1 -- Bug 8809596 : modified cond to pep.effective_start_date-1
BETWEEN pep2.effective_start_date AND pep2.effective_end_date
AND pil2.per_in_ler_id = pep2.per_in_ler_id
AND pep2.per_in_ler_id = l_prev_pil_id /* bug 11654610 */
/*Commented below condition for Bug 8809596*/
--and pil2.per_in_ler_id <> l_per_in_ler_id -- Bug 8227214
AND pil2.business_group_id = pep2.business_group_id
AND ( pil2.per_in_ler_stat_cd NOT IN
( 'VOIDD', 'BCKDT')
OR pil2.per_in_ler_stat_cd IS NULL) --
)
)
---Bug 11654610: For looking for ineligble, check for whether Participant is eligible
--for previous life event and not eligible for the current LE. Added both exists clause for the bug fix
OR
( p_eligible_flag = 'N'
and exists ( SELECT 'x'
FROM ben_elig_per_f pep2, ben_per_in_ler pil2
WHERE pep2.person_id = pep.person_id
AND (ctu.pl_id is null or nvl(pep2.pl_id,-1) = nvl(pep.pl_id,-1) )
AND (ctu.pgm_id is null or nvl(pep2.pgm_id,-1) = nvl(pep.pgm_id,-1) )
-- AND (ctu.ler_id is null or nvl(ctu.ler_id,-1) = nvl(pil2.ler_id,-1) )
AND (ctu.pl_typ_id is null or
(exists
( select 'x'
from ben_pl_f pl
where pl.pl_id = nvl(pep2.pl_id,pl.pl_id)
and pl.pl_typ_id = nvl(ctu.pl_typ_id,pl.pl_typ_id)
and cv_effective_date between
pl.effective_start_date
and pl.effective_end_date
)
) )
AND pep2.business_group_id = pep.business_group_id
AND pep2.elig_flag = 'Y'
AND pep.effective_start_date-1
BETWEEN pep2.effective_start_date AND pep2.effective_end_date
AND pil2.per_in_ler_id (+) = pep2.per_in_ler_id
AND pep2.per_in_ler_id = l_prev_pil_id
AND pil2.business_group_id (+) = pep2.business_group_id
AND ( pil2.per_in_ler_stat_cd NOT IN
( 'VOIDD', 'BCKDT')
OR pil2.per_in_ler_stat_cd IS NULL) --
)
and exists ( SELECT 'x'
FROM ben_elig_per_f pep2, ben_per_in_ler pil2
WHERE pep2.person_id = pep.person_id
AND (ctu.pl_id is null or nvl(pep2.pl_id,-1) = nvl(pep.pl_id,-1) )
AND (ctu.pgm_id is null or nvl(pep2.pgm_id,-1) = nvl(pep.pgm_id,-1) )
AND (ctu.ler_id is null or nvl(ctu.ler_id,-1) = nvl(pil2.ler_id,-1) )
AND (ctu.pl_typ_id is null or
(exists
( select 'x'
from ben_pl_f pl
where pl.pl_id = nvl(pep2.pl_id,pl.pl_id)
and pl.pl_typ_id = nvl(ctu.pl_typ_id,pl.pl_typ_id)
and cv_effective_date between
pl.effective_start_date
and pl.effective_end_date
)
) )
AND pep2.business_group_id = pep.business_group_id
AND pep2.elig_flag = 'N'
AND pep.effective_start_date
BETWEEN pep2.effective_start_date AND pep2.effective_end_date
AND pil2.per_in_ler_id (+) = pep2.per_in_ler_id
AND pep2.per_in_ler_id = l_per_in_ler_id
AND pil2.business_group_id (+) = pep2.business_group_id
AND ( pil2.per_in_ler_stat_cd NOT IN
( 'VOIDD', 'BCKDT')
OR pil2.per_in_ler_stat_cd IS NULL) --
)
)
) ;
select null
from ben_elig_per_f pep,
ben_per_in_ler pil
where pep.business_group_id = p_business_group_id
and pep.person_id = p_person_id
and nvl(get_cvg_strt_dt(pep.elig_per_id,pil.per_in_ler_id),cv_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 = cv_effective_date or
pep.effective_start_date = cv_lf_evt_ocrd_dt or
pep.effective_start_date = get_cvg_strt_dt(pep.elig_per_id,pil.per_in_ler_id))
-- and pep.effective_start_date = p_effective_date
and pep.elig_flag = p_eligible_flag
and pil.per_in_ler_id(+) = pep.per_in_ler_id
and nvl(pil.business_group_id,p_business_group_id) =
p_business_group_id
and nvl(pil.per_in_ler_stat_cd,'-1') not in ('VOIDD','BCKDT');
select 'x'
FROM ben_elig_per_f pep,
ben_per_in_ler pil,
ben_elig_dpnt egd
WHERE egd.dpnt_person_id = p_person_id
and egd.business_group_id = p_business_group_id
and egd.elig_per_id = pep.elig_per_id
and (c_pl_id is null or nvl(c_pl_id,-1) = nvl(pep.pl_id,-1) )
and (c_pgm_id is null or nvl(c_pgm_id,-1) = nvl(pep.pgm_id,-1) )
and (c_ler_id is null or nvl(c_ler_id,-1) = nvl(pep.ler_id,-1) )
and (c_pl_typ_id is null or
(exists
( select 'x'
from ben_pl_f pl
where pl.pl_id = pep.pl_id
and pl.pl_typ_id = c_pl_typ_id
and cv_effective_date between
pl.effective_start_date
and pl.effective_end_date
)
) )
and pep.business_group_id = pep.business_group_id
and nvl(get_cvg_strt_dt(pep.elig_per_id,pil.per_in_ler_id),cv_effective_date) -1
BETWEEN pep.effective_start_date AND pep.effective_end_date
AND pil.per_in_ler_id (+) = pep.per_in_ler_id
and pil.business_group_id (+) = pep.business_group_id
and ( pil.per_in_ler_stat_cd NOT IN
( 'VOIDD', 'BCKDT')
OR pil.per_in_ler_stat_cd IS NULL) ;
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl,
ctu.pl_id, -- Bug 1555557
ctu.pgm_id,
ctu.pl_typ_id
from ben_cm_typ_usg_f ctu,
ben_prtt_enrt_rslt_f pen
where ctu.business_group_id = p_business_group_id
/* First join comp objects */
and pen.business_group_id = ctu.business_group_id
and pen.person_id = p_person_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 */
and (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 (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'
/* Final test make sure created in the same run
Checking using per_in_ler_id and conc_request_id */
and pen.request_id = fnd_global.conc_request_id
and pen.enrt_mthd_cd = 'A'
and pen.prtt_enrt_rslt_stat_cd is null;
select null
from ben_prtt_enrt_rslt_f pen
where pen.business_group_id = p_business_group_id
and pen.person_id = p_person_id
and pen.per_in_ler_id = p_per_in_ler_id
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
/* Final test make sure created in the same run
Checking using per_in_ler_id and conc_request_id */
and pen.request_id = fnd_global.conc_request_id
and pen.enrt_mthd_cd = 'A'
and pen.prtt_enrt_rslt_stat_cd is null;
SELECT ctu.cm_typ_usg_id, ctu.cm_usg_rl, ctu.pl_id, ctu.pgm_id,
ctu.pl_typ_id
FROM ben_cm_typ_usg_f ctu, ben_per_in_ler pil
WHERE ctu.business_group_id = c_bgp_id
AND ctu.cm_typ_id = c_cm_typ_id
AND ctu.all_r_any_cd = 'ALL'
AND ( c_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 = c_asnd_lf_evt_dt)
)
AND c_comm_st_date BETWEEN ctu.effective_start_date
AND ctu.effective_end_date
AND ( c_pl_typ_id IS NULL
OR NVL (ctu.pl_typ_id, c_pl_typ_id) = c_pl_typ_id
)
AND pil.per_in_ler_id = c_pil_id
AND ctu.business_group_id = pil.business_group_id
AND NVL (ctu.ler_id, pil.ler_id) = pil.ler_id
AND EXISTS (
SELECT NULL
FROM ben_elig_per_elctbl_chc epe
WHERE epe.per_in_ler_id = pil.per_in_ler_id
AND epe.business_group_id = pil.business_group_id
AND epe.elctbl_flag = 'Y'
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)
AND ROWNUM = 1);
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl,
ctu.pl_id,
ctu.pgm_id,
ctu.pl_typ_id
from ben_cm_typ_usg_f ctu,
ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe
where pil.per_in_ler_id = c_pil_id
and pil.business_group_id = c_bgp_id
and ctu.business_group_id = pil.business_group_id
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 (c_pl_typ_id is null or
nvl(ctu.pl_typ_id,c_pl_typ_id) = c_pl_typ_id)
and (c_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 = c_asnd_lf_evt_dt
)
)
and c_comm_st_date
between ctu.effective_start_date and ctu.effective_end_date
and ctu.cm_typ_id = c_cm_typ_id
and ctu.all_r_any_cd = 'ALL'; */
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.per_in_ler_id = pil.per_in_ler_id
and epe.elctbl_flag = 'Y';
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 ctu.cm_typ_id = p_cm_typ_id
and ctu.ler_id = p_ler_id
and ctu.pgm_id is null
and ctu.pl_id is null
and ctu.pl_typ_id is null
and ctu.enrt_perd_id is null
and ctu.actn_typ_id is null
and not exists (select null
from ben_elig_per_elctbl_chc epe
where epe.business_group_id = p_business_group_id
and epe.elctbl_flag = 'Y'
and epe.per_in_ler_id = p_per_in_ler_id)
and ben_generate_communications.g_comm_start_date
between ctu.effective_start_date
and ctu.effective_end_date
/* This process code can only apply to ALL since one context only
can be set, we code for both though just in case */
and ctu.all_r_any_cd in ('ALL','ANY');
select null
from sys.dual
where not exists (select null
from ben_elig_per_elctbl_chc epe
where epe.business_group_id = p_business_group_id
and epe.elctbl_flag = 'Y'
and epe.per_in_ler_id = p_per_in_ler_id);
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl,
ctu.pl_id, -- Bug 1555557
ctu.pgm_id,
ctu.pl_typ_id
from ben_cm_typ_usg_f ctu,
ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where ctu.business_group_id = p_business_group_id
/* 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 pil.per_in_ler_id = pen.per_in_ler_id -- 5926672 new pil join
and pen.ler_id = nvl(p_ler_id,pen.ler_id)
and ( (p_effective_date
between pen.effective_start_date -- 5926672 or condition As Enrollment window might have shifted to future dates
and pen.effective_end_date)
or pil.LF_EVT_OCRD_DT = p_effective_date
or pil.STRTD_DT = p_effective_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
and nvl(ctu.pl_typ_id,pen.pl_typ_id) = pen.pl_typ_id
/* Now join in enrollment period */
and (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 pen.enrt_cvg_thru_dt < hr_api.g_eot
and pen.prtt_enrt_rslt_stat_cd is null;
select null
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pen.business_group_id = p_business_group_id
and pen.per_in_ler_id = p_per_in_ler_id
and pen.per_in_ler_id = pil.per_in_ler_id -- 5926672 join to pil
and pen.enrt_cvg_thru_dt < hr_api.g_eot
and (
(l_effective_date
between pen.effective_start_date -- 5926672 chnged to pil
and pen.effective_end_date
)
or pil.LF_EVT_OCRD_DT = l_effective_date
or pil.STRTD_DT = l_effective_date
)
and pen.prtt_enrt_rslt_stat_cd is null;
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl,
ctu.pl_id, -- Bug 1555557
ctu.pgm_id,
ctu.pl_typ_id
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
/* 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
-- validate the incomming parameter to make sure comm triiger for right plan
and ( ctu.pgm_id is null or p_pgm_id is null or p_pgm_id = ctu.pgm_id )
and ( ctu.pl_id is null or p_pl_id is null or p_pl_id = ctu.pl_id )
--if he pl_type_id is passed compare with pl_type_id or
-- compare with pen.pl_type_id
--and (p_pl_typ_id is null or
-- nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_id)
and ( (p_pl_typ_id is null and
nvl(ctu.pl_typ_id,pen.pl_typ_id ) = pen.pl_typ_id)
or (p_pl_typ_id is not null and
nvl(ctu.pl_typ_id,p_pl_typ_id) = p_pl_typ_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))
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.prtt_enrt_rslt_stat_cd is 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.per_in_ler_id = pil.per_in_ler_id;
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl,
ctu.pl_id, -- Bug 1555557
ctu.pgm_id,
ctu.pl_typ_id
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
/* 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 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,pen.pl_typ_id) = pen.pl_typ_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))
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.prtt_enrt_rslt_stat_cd is 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.per_in_ler_id = pil.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_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';
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 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 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';
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';
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)
and (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
)
)
/* 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_per_elctbl_chc epe1
where epe1.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and epe1.elctbl_flag = 'Y');
select null
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.elctbl_flag = 'Y'
and epe.business_group_id = p_business_group_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';
select ctu.cm_typ_usg_id,
ctu.cm_usg_rl,
ctu.pl_id, -- Bug 1555557
ctu.pgm_id,
ctu.pl_typ_id
from ben_cm_typ_usg_f ctu,
ben_prtt_enrt_rslt_f pen,
ben_prtt_rt_val prv
where ctu.business_group_id = p_business_group_id
/* First join comp objects */
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 pen.business_group_id = prv.business_group_id
and pen.per_in_ler_id <> prv.per_in_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,nvl(p_ler_id,-1)) = nvl(p_ler_id,-1)
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 */
and (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 pen.prtt_enrt_rslt_stat_cd is null
and prv.prtt_rt_val_stat_cd is null;
select null
from ben_prtt_rt_val prv,
ben_prtt_enrt_rslt_f pen
where prv.business_group_id = p_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 pen.business_group_id = prv.business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and prv.prtt_rt_val_stat_cd is null
and pen.per_in_ler_id <> prv.per_in_ler_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_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_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';
select distinct pen.pgm_id, pen.pl_typ_id
from 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 pen.per_in_ler_id = p_per_in_ler_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
--8818355
--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);
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 'Y'
from ben_prtt_enrt_rslt_f pen,
ben_pl_f pln,
ben_oipl_f cop,
ben_opt_f opt
where pen.person_id = p_person_id
-- and nvl(pen.pgm_id,-1) = nvl(v_pgm_id,-1) maagrawa (02/11/00)
and pen.pl_typ_id = v_pl_typ_id
and pen.enrt_cvg_thru_dt = hr_api.g_eot
--8818355
and pen.effective_end_date = hr_api.g_eot
and pen.sspndd_flag = 'N'
and pen.prtt_enrt_rslt_stat_cd is null
--8818355
--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 pgm_id, pl_id, oipl_id, business_group_id
from ben_prtt_enrt_rslt_f
where pl_typ_id = v_pl_typ_id
and pgm_id = v_pgm_id
and per_in_ler_id = v_per_in_ler_id
and ler_id = v_ler_id
order by prtt_enrt_rslt_id desc
;
select 'x'
from ben_per_cm_prvdd_f
where per_cm_id = p_per_cm_id
--
-- Bug No: 3752029
-- Commented out this condition since it was only allowing selecting records for which we
-- have 'To_be_sent_code' as 'As of event date'. So to disallow duplicate HIPAA letter
-- generation for other To_be_sent_code values, this condition is removed.
--
-- and to_be_sent_dt = g_to_be_sent_dt
and p_effective_date between
effective_start_date and effective_end_date
;
select ctu.pl_typ_id,
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_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
)
)
and ctu.pl_typ_id is not 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.pl_typ_id is not null
and ctu.all_r_any_cd = 'ALL';
select ctr.cm_trgr_src_cd,
ctr.cm_trgr_typ_cd,
ctr.cm_trgr_id,
ctt.cm_typ_trgr_rl,
cct.whnvr_trgrd_flag,
cmt.cm_dlvry_mthd_typ_cd,
cmd.cm_dlvry_med_typ_cd,
cct.inspn_rqd_flag,
cct.cm_typ_id,
cct.to_be_sent_dt_cd,
cct.to_be_sent_dt_rl,
cct.cm_typ_rl,
cct.inspn_rqd_rl,
ctr.proc_cd,
cct.rcpent_cd,
cct.name
from ben_cm_trgr ctr,
ben_cm_typ_trgr_f ctt,
ben_cm_typ_f cct,
ben_cm_dlvry_mthd_typ cmt,
ben_cm_dlvry_med_typ cmd
/* if p_cm_trgr_typ_cd is specified pick only those rows */
where ctr.cm_trgr_typ_cd = nvl(p_cm_trgr_typ_cd, ctr.cm_trgr_typ_cd)
and ctt.cm_trgr_id = ctr.cm_trgr_id
and ctt.business_group_id = p_business_group_id
and p_eff_date
between ctt.effective_start_date
and ctt.effective_end_date
/* if p_cm_typ_id is specified, pick only those rows */
and cct.cm_typ_id = nvl(p_cm_typ_id, cct.cm_typ_id)
and cct.cm_typ_id = ctt.cm_typ_id
and p_eff_date
between cct.effective_start_date
and cct.effective_end_date
and cct.cm_typ_id = cmt.cm_typ_id(+)
and nvl(cmt.dflt_flag,'Y') = 'Y'
and nvl(cct.inactive_flag,'N') = 'N' -- Bug 12572670
and cmt.cm_dlvry_mthd_typ_id = cmd.cm_dlvry_mthd_typ_id(+)
and nvl(cmd.dflt_flag,'Y') = 'Y'
and ctr.proc_cd in (p_proc_cd1,
p_proc_cd2,
p_proc_cd3,
p_proc_cd4,
p_proc_cd5,
p_proc_cd6,
p_proc_cd7,
p_proc_cd8,
p_proc_cd9,
p_proc_cd10);
select pil.lf_evt_ocrd_dt,
ler.typ_cd
from ben_per_in_ler pil ,
ben_ler_f ler
where pil.per_in_ler_id = p_per_in_ler_id
and ler.ler_id = pil.ler_id
and p_effective_date between
ler.effective_start_date and ler.effective_end_date ;
SELECT pil.*
FROM ben_per_in_ler pil, ben_ler_f ler
WHERE pil.person_id = p_person_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 ;
g_ct_eval_tab.delete;