[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select yrp.start_date,
yrp.end_date
from ben_yr_perd yrp,
ben_popl_yr_perd cpy
where cpy.pgm_id = p_pgm_id
and cpy.yr_perd_id = yrp.yr_perd_id
and l_effective_date
between yrp.start_date
and yrp.end_date;
select yrp.start_date,
yrp.end_date
from ben_yr_perd yrp,
ben_popl_yr_perd cpy
where cpy.pl_id = p_pl_id
and cpy.yr_perd_id = yrp.yr_perd_id
and l_effective_date
between yrp.start_date
and yrp.end_date;
select yrp.start_date,
yrp.end_date
from ben_yr_perd yrp,
ben_popl_yr_perd cpy,
ben_oipl_f cop
where cpy.pl_id = cop.pl_id
and cop.oipl_id = p_oipl_id
and cpy.yr_perd_id = yrp.yr_perd_id
and l_effective_date
between yrp.start_date
and yrp.end_date
and l_effective_date
between cop.effective_start_date
and cop.effective_end_date;
select fft.formula_type_id,fft.formula_type_name
from ff_formula_types fft,ff_formulas_f ff
where ff.formula_id=p_formula_id
and ff.formula_type_id= fft.formula_type_id
and p_effective_date between ff.effective_start_date and ff.effective_end_date;
select name
from ben_pgm_f pgm
where pgm_id=p_pgm_id
and p_effective_Date between effective_start_date and effective_end_date;
select name
from ben_pl_f pln
where pl_id=p_pl_id
and p_effective_Date between effective_start_date and effective_end_date;
select name
from ben_opt_f opt
where opt_id=p_opt_id
and p_effective_Date between effective_start_date and effective_end_date;
select 1
from ben_per_in_ler pil,
ben_ler_f ler
where pil.ler_id = ler.ler_id
and pil.per_in_ler_id=p_per_in_ler_id
and ler.typ_cd not like 'SCHED%'
and p_lf_evt_ocrd_dt between effective_start_date and effective_end_date;
select national_identifier
from per_all_people_f
where person_id=p_person_id;
select yrp.start_date, yrp.end_date
from ben_yr_perd yrp,
ben_popl_yr_perd cpy
where cpy.pgm_id = p_pgm_id
and cpy.yr_perd_id = yrp.yr_perd_id
and l_effective_date < yrp.start_date
order by 1;
select yrp.start_date, yrp.end_date
from ben_yr_perd yrp,
ben_popl_yr_perd cpy
where cpy.pl_id = p_pl_id
and cpy.yr_perd_id = yrp.yr_perd_id
and l_effective_date < yrp.start_date
order by 1;
select yrp.start_date, yrp.end_date
from ben_yr_perd yrp,
ben_popl_yr_perd cpy,
ben_oipl_f cop
where cpy.pl_id = cop.pl_id
and cop.oipl_id = p_oipl_id
and cpy.yr_perd_id = yrp.yr_perd_id
and l_effective_date < yrp.start_date
and l_effective_date between cop.effective_start_date
and cop.effective_end_date
order by 1;
select pil.lf_evt_ocrd_dt
from ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id;
select pil.ntfn_dt
from ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id;
select asg.assignment_id,asg.organization_id
from per_all_assignments_f asg
where asg.person_id = l_person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and nvl(p_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)) between asg.effective_start_date
and asg.effective_end_date
order by asg.assignment_type desc , asg.effective_start_date desc ;
select pil.per_in_ler_id,
pil.person_id,
epe.pgm_id,
epe.pl_id,
epe.pl_typ_id,
epe.oipl_id,
pil.ler_id,
epe.business_group_id,
epe.enrt_cvg_strt_dt
from ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.per_in_ler_id = pil.per_in_ler_id;
select opt_id
from ben_oipl_f oipl
where oipl_id = l_oipl_id
and business_group_id = p_business_group_id
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between oipl.effective_start_date
and oipl.effective_end_date;
select pl.pl_typ_id
from ben_pl_f pl
where pl.pl_id = l_pl_id
and pl.business_group_id = p_business_group_id
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between pl.effective_start_date
and pl.effective_end_date;
select pil.ler_id
from 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;
select tpe.start_date,
tpe.end_date
from per_time_periods tpe,
per_all_assignments_f asg
where tpe.payroll_id = asg.payroll_id
and asg.assignment_type <> 'C'
and asg.business_group_id = l_business_group_id
and asg.person_id = l_person_id
and asg.primary_flag = 'Y'
and asg.assignment_id = nvl(p_assignment_id,asg.assignment_id) -----------Bug 8394662
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between asg.effective_start_date
and asg.effective_end_date
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between tpe.start_date
and tpe.end_date
--and rownum = 1
order by decode(asg.assignment_type,'E',1,2) asc;
select region_2
from hr_locations_all loc,per_all_assignments_f asg
where loc.location_id = asg.location_id
and asg.person_id = p_person_id
and asg.assignment_type <> 'C'
and p_effective_date between
asg.effective_start_date and asg.effective_end_date
and asg.business_group_id =p_business_group_id;
select tpe.start_date,
tpe.end_date
from per_time_periods tpe,
per_all_assignments_f asg
where tpe.payroll_id = asg.payroll_id
and asg.assignment_type <> 'C'
and asg.business_group_id = l_business_group_id
and asg.person_id = l_person_id
and asg.primary_flag = 'Y'
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between asg.effective_start_date
and asg.effective_end_date
and p_date_dt between tpe.start_date and tpe.end_date
order by decode(asg.assignment_type,'E',1,2) asc;
select min(tpe.start_date )
from per_time_periods tpe,
per_all_assignments_f asg
where tpe.payroll_id = asg.payroll_id
and asg.assignment_type = p_assignment_type
and asg.business_group_id = l_business_group_id
and asg.person_id = l_person_id
and asg.primary_flag = 'Y'
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between asg.effective_start_date
and asg.effective_end_date
and p_date_dt <= nvl(tpe.regular_payment_date,tpe.end_date);
select max(tpe.end_date )
from per_time_periods tpe,
per_all_assignments_f asg
where tpe.payroll_id = asg.payroll_id
and asg.assignment_type = p_assignment_type
and asg.business_group_id = l_business_group_id
and asg.person_id = l_person_id
and asg.primary_flag = 'Y'
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between asg.effective_start_date
and asg.effective_end_date
and p_date_dt > nvl(tpe.regular_payment_date,tpe.end_date);
select tpe.start_date,
tpe.end_date
from per_time_periods tpe,
per_all_assignments_f asg
where tpe.payroll_id = asg.payroll_id
and asg.assignment_type <> 'C'
and asg.business_group_id = l_business_group_id
and asg.person_id = l_person_id
and asg.primary_flag = 'Y'
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between asg.effective_start_date
and asg.effective_end_date
and asg.assignment_id = nvl(p_assignment_id,asg.assignment_id) -----------Bug 8394662
and tpe.start_date > p_date_dt
order by decode(asg.assignment_type,'E',1,2) asc,
tpe.start_date;
select tpe.start_date,
tpe.end_date
from per_time_periods tpe,
per_all_assignments_f asg
where tpe.payroll_id = asg.payroll_id
and asg.assignment_type <> 'C'
and asg.business_group_id = l_business_group_id
and asg.person_id = l_person_id
and asg.primary_flag = 'Y'
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between asg.effective_start_date
and asg.effective_end_date
and tpe.end_date < p_date_dt
order by decode(asg.assignment_type,'E',1,2) asc,
tpe.end_date desc;
select date_start
from per_periods_of_service pps
where pps.person_id = p_person_id
and pps.date_start = (select max(pps1.date_start) -- this gets most recent
from per_periods_of_service pps1
where pps1.person_id = p_person_id
and pps1.date_start = nvl(l_lf_evt_ocrd_dt,p_effective_date )
);
select max(date_start)
from per_periods_of_service pps
where pps.person_id = p_person_id
and pps.date_start <= nvl(l_lf_evt_ocrd_dt,p_effective_date);
select yrp.lmtn_yr_strt_dt,
yrp.lmtn_yr_end_dt
from ben_yr_perd yrp,
ben_popl_yr_perd cpy
where cpy.pgm_id = l_pgm_id
and cpy.yr_perd_id = yrp.yr_perd_id
and yrp.business_group_id = l_business_group_id
and cpy.business_group_id = l_business_group_id
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between yrp.start_date
and yrp.end_date;
select yrp.lmtn_yr_strt_dt,
yrp.lmtn_yr_end_dt
from ben_yr_perd yrp,
ben_popl_yr_perd cpy
where cpy.pl_id = l_pl_id
and cpy.yr_perd_id = yrp.yr_perd_id
and yrp.business_group_id = l_business_group_id
and cpy.business_group_id = l_business_group_id
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between yrp.start_date
and yrp.end_date;
select yrp.lmtn_yr_strt_dt,
yrp.lmtn_yr_end_dt
from ben_yr_perd yrp,
ben_popl_yr_perd cpy,
ben_oipl_f cop
where cpy.pl_id = cop.pl_id
and cop.oipl_id = l_oipl_id
and cpy.yr_perd_id = yrp.yr_perd_id
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between yrp.start_date
and yrp.end_date
and cpy.business_group_id = l_business_group_id
and cop.business_group_id = l_business_group_id
and yrp.business_group_id = l_business_group_id
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between cop.effective_start_date
and cop.effective_end_date;
select pel.enrt_perd_end_dt,
pel.procg_end_dt
from ben_pil_elctbl_chc_popl pel,
ben_elig_per_elctbl_chc epe
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.business_group_id = l_business_group_id
and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and pel.business_group_id = l_business_group_id;
select enrt_perd_strt_dt
from ben_pil_elctbl_chc_popl
where per_in_ler_id = l_per_in_ler_id
and ((l_pgm_id is not null and pgm_id = l_pgm_id)
or (l_pgm_id is null and pl_id = l_pl_id)
or (l_pgm_id is null and l_pl_id is null))
and business_group_id = l_business_group_id;
select min(enrt_perd_end_dt)
from ben_pil_elctbl_chc_popl
where per_in_ler_id = l_per_in_ler_id
and ((l_pgm_id is not null and pgm_id = l_pgm_id)
or (l_pgm_id is null and pl_id = l_pl_id)
or (l_pgm_id is null and l_pl_id is null))
and business_group_id = l_business_group_id;
select min(dflt_asnd_dt)
from ben_pil_elctbl_chc_popl
where per_in_ler_id = l_per_in_ler_id
and ((l_pgm_id is not null and pgm_id = l_pgm_id)
or (l_pgm_id is null and pl_id = l_pl_id)
or (l_pgm_id is null and l_pl_id is null))
and business_group_id = l_business_group_id;
select min(auto_asnd_dt)
from ben_pil_elctbl_chc_popl
where per_in_ler_id = l_per_in_ler_id
and ((l_pgm_id is not null and pgm_id = l_pgm_id)
or (l_pgm_id is null and pl_id = l_pl_id)
or (l_pgm_id is null and l_pl_id is null))
and business_group_id = l_business_group_id;
select min(elcns_made_dt)
from ben_pil_elctbl_chc_popl
where per_in_ler_id = l_per_in_ler_id
and ((l_pgm_id is not null and pgm_id = l_pgm_id)
or (l_pgm_id is null and pl_id = l_pl_id)
or (l_pgm_id is null and l_pl_id is null))
and business_group_id = l_business_group_id;
select min(prtn_strt_dt)
from ben_elig_per_f pep, ben_per_in_ler pil
where pep.person_id = p_person_id
and pep.business_group_id = p_business_group_id
and p_effective_date = pep.effective_start_date
and pil.per_in_ler_id(+) = pep.per_in_ler_id
and pil.business_group_id = p_business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
or
pil.per_in_ler_stat_cd is null);
select min(prtn_end_dt)
from ben_elig_per_f pep, ben_per_in_ler pil
where pep.person_id = p_person_id
-- and pep.business_group_id = p_business_group_id
and nvl(p_fonm_cvg_strt_dt,p_effective_date ) = pep.effective_start_date
and pil.per_in_ler_id(+) = pep.per_in_ler_id
and pil.business_group_id = p_business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
or
pil.per_in_ler_stat_cd is null);
select ecd.cvg_thru_dt,
ecd.effective_end_date
from ben_elig_cvrd_dpnt_f ecd
where ecd.per_in_ler_id = l_per_in_ler_id
and ecd.business_group_id = l_business_group_id
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between ecd.effective_start_date
and ecd.effective_end_date;
select pen.enrt_cvg_thru_dt,
pen.enrt_cvg_strt_dt,
pen.effective_start_date
from ben_prtt_enrt_rslt_f pen
where pen.per_in_ler_id = l_per_in_ler_id and
pen.pl_id=l_pl_id
and nvl(pen.pgm_id,-1)=nvl(l_pgm_id,-1)
and nvl(pen.oipl_id,-1)=nvl(l_oipl_id,-1)
and pen.business_group_id = l_business_group_id
-- Bug 1633284
/*
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between pen.effective_start_date
and pen.effective_end_date; */
select prv.rt_strt_dt,
prv.rt_end_dt
from ben_prtt_rt_val prv,
ben_prtt_enrt_rslt_f pen
where pen.per_in_ler_id = l_per_in_ler_id
and pen.pl_id=l_pl_id
and nvl(pen.pgm_id,-1)=nvl(l_pgm_id,-1)
and nvl(pen.oipl_id,-1)=nvl(l_oipl_id,-1)
and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
and prv.business_group_id = l_business_group_id
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between pen.effective_start_date
and pen.effective_end_date;
select null
from
ben_prtt_enrt_rslt_f pen
where pen.per_in_ler_id = l_per_in_ler_id
and pen.pl_id=l_pl_id
and nvl(pen.pgm_id,-1)=nvl(l_pgm_id,-1)
-- and nvl(pen.oipl_id,-1)=nvl(l_oipl_id,-1)
-- and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
and pen.business_group_id = l_business_group_id
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date;
select er.rt_strt_dt
from ben_enrt_rt er,
ben_enrt_bnft eb
where eb.elig_per_elctbl_chc_id =p_elig_per_elctbl_chc_id
and er.elig_per_elctbl_chc_id is null
and eb.enrt_bnft_id=er.enrt_bnft_id
--
union
--
select er.rt_strt_dt
from ben_enrt_rt er
where er.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id ;
select ecd.cvg_strt_dt,
ecd.cvg_thru_dt
from ben_elig_cvrd_dpnt_f ecd
where ecd.per_in_ler_id = l_per_in_ler_id
and ecd.business_group_id = l_business_group_id
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between ecd.effective_start_date
and ecd.effective_end_date;
select paf.date_of_birth
from per_all_people_f paf
where paf.person_id = p_person_id;
select pil.ler_id
from ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id;
select asg.payroll_id
from per_time_periods tpe,
per_all_assignments_f asg
where tpe.payroll_id = asg.payroll_id
and asg.assignment_type <> 'C'
and asg.business_group_id = l_business_group_id
and asg.person_id = l_person_id
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between asg.effective_start_date
and asg.effective_end_date
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between tpe.start_date
and tpe.end_date
order by decode(asg.assignment_type,'E',1,2) asc;
select pen.enrt_cvg_thru_dt,
pen.enrt_cvg_strt_dt,
pen.effective_start_date
from ben_prtt_enrt_rslt_f pen
where pen.per_in_ler_id = c_per_in_ler_id
and pen.pl_id=l_pl_id
and nvl(pen.pgm_id,-1)=nvl(l_pgm_id,-1)
and nvl(pen.oipl_id,-1)=nvl(l_oipl_id,-1)
--and pen.pl_typ_id = l_pl_typ_id
and pen.business_group_id = l_business_group_id
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and not exists
(select '1' from ben_prtt_enrt_rslt_f pen1
where pen.prtt_enrt_rslt_id = pen1.RPLCS_SSPNDD_RSLT_ID
and pen1.per_in_ler_id = c_per_in_ler_id);
select prv.rt_strt_dt,
prv.rt_end_dt
from ben_prtt_enrt_rslt_f pen,
ben_prtt_rt_val prv
where pen.per_in_ler_id = c_per_in_ler_id
and pen.pl_id=l_pl_id
and nvl(pen.pgm_id,-1)=nvl(l_pgm_id,-1)
and nvl(pen.oipl_id,-1)=nvl(l_oipl_id,-1)
--and pen.pl_typ_id = l_pl_typ_id
and pen.business_group_id = l_business_group_id
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and prv.per_in_ler_id = c_per_in_ler_id
and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and not exists
(select '1' from ben_prtt_enrt_rslt_f pen1
where pen.prtt_enrt_rslt_id = pen1.RPLCS_SSPNDD_RSLT_ID
and pen1.per_in_ler_id = c_per_in_ler_id);
select 1 order_number,
epp.enrt_cvg_strt_dt_cd,
epp.enrt_cvg_strt_dt_rl,
epp.enrt_cvg_end_dt_cd,
epp.enrt_cvg_end_dt_rl,
epp.rt_strt_dt_cd,
epp.rt_strt_dt_rl,
epp.rt_end_dt_cd,
epp.rt_end_dt_rl
from ben_enrt_perd_for_pl_f epp
where epp.ENRT_PERD_FOR_PL_ID = c_epfp_id
and c_effective_date
between epp.effective_start_date and epp.effective_end_date
union
-- Bug # 4356591
-- in case of a plip. if the enrt_perd details are defined at the plan level
-- then details are selected by this select. The enrt_perd_id stored in
-- ben_pil_elctbl_chc is of the pgm or the first plan defined in the program
select 2 order_number,
epd.enrt_cvg_strt_dt_cd,
epd.enrt_cvg_strt_dt_rl,
epd.enrt_cvg_end_dt_cd,
epd.enrt_cvg_end_dt_rl,
epd.rt_strt_dt_cd,
epd.rt_strt_dt_rl,
epd.rt_end_dt_cd,
epd.rt_end_dt_rl
from ben_popl_enrt_typ_cycl_f popl,
ben_enrt_perd epd,
ben_per_in_ler pil
where popl.pl_id = c_pl_id
and epd.popl_enrt_typ_cycl_id = popl.popl_enrt_typ_cycl_id
and pil.per_in_ler_id = c_per_in_ler_id
and epd.asnd_lf_evt_dt = pil.lf_evt_ocrd_dt /* removed join btw PIL and LER instead made join btw epd $ pil.*/
and c_effective_date between popl.effective_start_date and popl.effective_end_date
and ( enrt_cvg_strt_dt_cd is not null
and enrt_cvg_end_dt_cd is not null
and rt_strt_dt_cd is not null
and rt_end_dt_cd is not null )
-- end 4356591
union
-- Bug # 4356591
-- If the enrt_perd details defined at program level and not at plan level
-- then details are selected by this select.
select 3 order_number,
epd.enrt_cvg_strt_dt_cd,
epd.enrt_cvg_strt_dt_rl,
epd.enrt_cvg_end_dt_cd,
epd.enrt_cvg_end_dt_rl,
epd.rt_strt_dt_cd,
epd.rt_strt_dt_rl,
epd.rt_end_dt_cd,
epd.rt_end_dt_rl
from ben_popl_enrt_typ_cycl_f popl, -- start 4356591
ben_enrt_perd epd,
ben_per_in_ler pil
where popl.pgm_id = c_pgm_id
and epd.popl_enrt_typ_cycl_id = popl.popl_enrt_typ_cycl_id
and pil.per_in_ler_id = c_per_in_ler_id
and epd.asnd_lf_evt_dt = pil.lf_evt_ocrd_dt /* removed join btw PIL and LER instead made join btw epd $ pil.*/
and c_effective_date between popl.effective_start_date and popl.effective_end_date
and ( enrt_cvg_strt_dt_cd is not null
and enrt_cvg_end_dt_cd is not null
and rt_strt_dt_cd is not null
and rt_end_dt_cd is not null ) -- end 4356591
union
select 4 order_number,
plp.enrt_cvg_strt_dt_cd,
plp.enrt_cvg_strt_dt_rl,
plp.enrt_cvg_end_dt_cd,
plp.enrt_cvg_end_dt_rl,
plp.rt_strt_dt_cd,
plp.rt_strt_dt_rl,
plp.rt_end_dt_cd,
plp.rt_end_dt_rl
from ben_plip_f plp
where plp.plip_id=c_plip_id
and c_effective_date
between plp.effective_start_date and plp.effective_end_date
union
select 5 order_number,
pln.enrt_cvg_strt_dt_cd,
pln.enrt_cvg_strt_dt_rl,
pln.enrt_cvg_end_dt_cd,
pln.enrt_cvg_end_dt_rl,
pln.rt_strt_dt_cd,
pln.rt_strt_dt_rl,
pln.rt_end_dt_cd,
pln.rt_end_dt_rl
from ben_pl_f pln
where pln.pl_id=c_pl_id
and c_effective_date
between pln.effective_start_date and pln.effective_end_date
union
select 6 order_number,
ptip.enrt_cvg_strt_dt_cd,
ptip.enrt_cvg_strt_dt_rl,
ptip.enrt_cvg_end_dt_cd,
ptip.enrt_cvg_end_dt_rl,
ptip.rt_strt_dt_cd,
ptip.rt_strt_dt_rl,
ptip.rt_end_dt_cd,
ptip.rt_end_dt_rl
from ben_ptip_f ptip
where ptip.ptip_id=c_ptip_id
and c_effective_date
between ptip.effective_start_date and ptip.effective_end_date
union
select 7 order_number,
pgm.enrt_cvg_strt_dt_cd,
pgm.enrt_cvg_strt_dt_rl,
pgm.enrt_cvg_end_dt_cd,
pgm.enrt_cvg_end_dt_rl,
pgm.rt_strt_dt_cd,
pgm.rt_strt_dt_rl,
pgm.rt_end_dt_cd,
pgm.rt_end_dt_rl
from ben_pgm_f pgm
where pgm.pgm_id = c_pgm_id
and c_effective_date
between pgm.effective_start_date and pgm.effective_end_date
order by 1; -- bug 5717428
select '2' order_number,
enrt_cvg_strt_dt_cd,
enrt_cvg_strt_dt_rl,
enrt_cvg_end_dt_cd,
enrt_cvg_end_dt_rl,
rt_strt_dt_cd,
rt_strt_dt_rl,
rt_end_dt_cd,
rt_end_dt_rl
from ben_enrt_perd
where enrt_perd_id=l_enrt_perd_id and
business_group_id =p_business_group_id
union
select '4' order_number,
enrt_cvg_strt_dt_cd,
enrt_cvg_strt_dt_rl,
enrt_cvg_end_dt_cd,
enrt_cvg_end_dt_rl,
rt_strt_dt_cd,
rt_strt_dt_rl,
rt_end_dt_cd,
rt_end_dt_rl
from ben_pl_f
where pl_id=l_pl_id and
business_group_id =p_business_group_id and
nvl(p_lf_evt_ocrd_dt,p_effective_date) between
effective_start_date and effective_end_date
order by 1;
select 1 order_number,
epp.enrt_cvg_strt_dt_cd,
epp.enrt_cvg_strt_dt_rl,
epp.enrt_cvg_end_dt_cd,
epp.enrt_cvg_end_dt_rl,
epp.rt_strt_dt_cd,
epp.rt_strt_dt_rl,
epp.rt_end_dt_cd,
epp.rt_end_dt_rl
from ben_enrt_perd_for_pl_f epp
where epp.ENRT_PERD_FOR_PL_ID=c_epfp_id
and c_effective_date
between epp.effective_start_date and epp.effective_end_date
and ( enrt_cvg_strt_dt_cd is not null
and enrt_cvg_end_dt_cd is not null
and rt_strt_dt_cd is not null
and rt_end_dt_cd is not null
)
union
-- Bug # 2527347
-- in case of a plip. if the lee_rsn details are defined at the plan level
-- then details are selected by this select. The lee_rsn_id stored in
-- ben_pil_elctbl_chc is of the pgm or the first plan defined in the program
select 2 order_number,
lee.enrt_cvg_strt_dt_cd,
lee.enrt_cvg_strt_dt_rl,
lee.enrt_cvg_end_dt_cd,
lee.enrt_cvg_end_dt_rl,
lee.rt_strt_dt_cd,
lee.rt_strt_dt_rl,
lee.rt_end_dt_cd,
lee.rt_end_dt_rl
from ben_popl_enrt_typ_cycl_f popl,
ben_lee_rsn_f lee,
ben_ler_f ler,
ben_per_in_ler pil
where popl.pl_id = c_pl_id
and c_effective_date between popl.effective_start_date and popl.effective_end_date
and lee.popl_enrt_typ_cycl_id = popl.popl_enrt_typ_cycl_id
and pil.per_in_ler_id = c_per_in_ler_id
and ler.ler_id = pil.ler_id
and lee.ler_id = ler.ler_id
and c_effective_date between lee.effective_start_date and lee.effective_end_date
and c_effective_date between popl.effective_start_date and popl.effective_end_date
and ( enrt_cvg_strt_dt_cd is not null
and enrt_cvg_end_dt_cd is not null
and rt_strt_dt_cd is not null
and rt_end_dt_cd is not null )
-- end bug # 2527347
union
-- Bug # 4356591
-- If the lee_rsn_details defined at program level and not at plan level
-- then details are selected by this select.
select 3 order_number,
lee.enrt_cvg_strt_dt_cd,
lee.enrt_cvg_strt_dt_rl,
lee.enrt_cvg_end_dt_cd,
lee.enrt_cvg_end_dt_rl,
lee.rt_strt_dt_cd,
lee.rt_strt_dt_rl,
lee.rt_end_dt_cd,
lee.rt_end_dt_rl
from ben_popl_enrt_typ_cycl_f popl, -- start 4356591
ben_lee_rsn_f lee,
ben_per_in_ler pil
where popl.pgm_id = c_pgm_id
and c_effective_date between popl.effective_start_date and popl.effective_end_date
and lee.popl_enrt_typ_cycl_id = popl.popl_enrt_typ_cycl_id
and pil.per_in_ler_id = c_per_in_ler_id
and lee.ler_id = pil.ler_id
and c_effective_date between lee.effective_start_date and lee.effective_end_date
and c_effective_date between popl.effective_start_date and popl.effective_end_date -- end 4356591
and ( enrt_cvg_strt_dt_cd is not null
and enrt_cvg_end_dt_cd is not null
and rt_strt_dt_cd is not null
and rt_end_dt_cd is not null
)
union
select 4 order_number,
plp.enrt_cvg_strt_dt_cd,
plp.enrt_cvg_strt_dt_rl,
plp.enrt_cvg_end_dt_cd,
plp.enrt_cvg_end_dt_rl,
plp.rt_strt_dt_cd,
plp.rt_strt_dt_rl,
plp.rt_end_dt_cd,
plp.rt_end_dt_rl
from ben_plip_f plp
where plp.plip_id=c_plip_id
and c_effective_date
between plp.effective_start_date and plp.effective_end_date
and ( enrt_cvg_strt_dt_cd is not null
and enrt_cvg_end_dt_cd is not null
and rt_strt_dt_cd is not null
and rt_end_dt_cd is not null
)
union
select 5 order_number,
pln.enrt_cvg_strt_dt_cd,
pln.enrt_cvg_strt_dt_rl,
pln.enrt_cvg_end_dt_cd,
pln.enrt_cvg_end_dt_rl,
pln.rt_strt_dt_cd,
pln.rt_strt_dt_rl,
pln.rt_end_dt_cd,
pln.rt_end_dt_rl
from ben_pl_f pln
where pln.pl_id=c_pl_id
and c_effective_date
between pln.effective_start_date and pln.effective_end_date
and ( enrt_cvg_strt_dt_cd is not null
and enrt_cvg_end_dt_cd is not null
and rt_strt_dt_cd is not null
and rt_end_dt_cd is not null
)
union
select 6 order_number,
ptip.enrt_cvg_strt_dt_cd,
ptip.enrt_cvg_strt_dt_rl,
ptip.enrt_cvg_end_dt_cd,
ptip.enrt_cvg_end_dt_rl,
ptip.rt_strt_dt_cd,
ptip.rt_strt_dt_rl,
ptip.rt_end_dt_cd,
ptip.rt_end_dt_rl
from ben_ptip_f ptip
where ptip.ptip_id=c_ptip_id
and c_effective_date
between ptip.effective_start_date and ptip.effective_end_date
and ( enrt_cvg_strt_dt_cd is not null
and enrt_cvg_end_dt_cd is not null
and rt_strt_dt_cd is not null
and rt_end_dt_cd is not null
)
union
select 7 order_number,
pgm.enrt_cvg_strt_dt_cd,
pgm.enrt_cvg_strt_dt_rl,
pgm.enrt_cvg_end_dt_cd,
pgm.enrt_cvg_end_dt_rl,
pgm.rt_strt_dt_cd,
pgm.rt_strt_dt_rl,
pgm.rt_end_dt_cd,
pgm.rt_end_dt_rl
from ben_pgm_f pgm
where pgm.pgm_id=c_pgm_id
and c_effective_date
between pgm.effective_start_date and pgm.effective_end_date
and ( enrt_cvg_strt_dt_cd is not null
and enrt_cvg_end_dt_cd is not null
and rt_strt_dt_cd is not null
and rt_end_dt_cd is not null
)
order by 1 ; -- Bug 2122643
select '2' order_number,
enrt_cvg_strt_dt_cd,
enrt_cvg_strt_dt_rl,
enrt_cvg_end_dt_cd,
enrt_cvg_end_dt_rl,
rt_strt_dt_cd,
rt_strt_dt_rl,
rt_end_dt_cd,
rt_end_dt_rl
from ben_lee_rsn_f
where lee_rsn_id=l_lee_rsn_id and
business_group_id =p_business_group_id and
nvl(p_lf_evt_ocrd_dt,p_effective_date) between
effective_start_date and effective_end_date
union
select '4' order_number,
enrt_cvg_strt_dt_cd,
enrt_cvg_strt_dt_rl,
enrt_cvg_end_dt_cd,
enrt_cvg_end_dt_rl,
rt_strt_dt_cd,
rt_strt_dt_rl,
rt_end_dt_cd,
rt_end_dt_rl
from ben_pl_f
where pl_id=l_pl_id and
business_group_id =p_business_group_id and
nvl(p_lf_evt_ocrd_dt,p_effective_date) between
effective_start_date and effective_end_date
order by 1;
select
epe.pl_id,
epe.pgm_id,
pel.enrt_perd_id,
pel.lee_rsn_id,
epe.oipl_id,
epe.per_in_ler_id,
pil.person_id,
epe.fonm_cvg_strt_dt
from
ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil
where
epe.elig_per_elctbl_chc_id=p_elig_per_elctbl_chc_id and
pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id and
pil.per_in_ler_id=epe.per_in_ler_id;
select ptp.ptip_id
from ben_ptip_f ptp,
ben_pl_f pln
where ptp.pl_typ_id = pln.pl_typ_id
and c_effective_date
between pln.effective_start_date and pln.effective_end_date
and ptp.pgm_id = c_pgm_id
and pln.pl_id = c_pl_id
and c_effective_date
between ptp.effective_start_date and ptp.effective_end_date;
select plp.plip_id
from ben_plip_f plp
where plp.pgm_id = c_pgm_id
and plp.pl_id = c_pl_id
and c_effective_date
between plp.effective_start_date and plp.effective_end_date;
select epfp.ENRT_PERD_FOR_PL_ID
from ben_enrt_perd_for_pl_f epfp
where epfp.lee_rsn_id = c_lee_rsn_id
and epfp.pl_id = c_pl_id
and c_effective_date
between epfp.effective_start_date and epfp.effective_end_date;
select epfp.ENRT_PERD_FOR_PL_ID
from ben_enrt_perd_for_pl_f epfp
where epfp.enrt_perd_id = c_enrt_perd_id
and epfp.pl_id = c_pl_id
and c_effective_date
between epfp.effective_start_date and epfp.effective_end_date;
select 1 order_number,
epp.enrt_cvg_strt_dt_cd,
epp.enrt_cvg_strt_dt_rl,
epp.enrt_cvg_end_dt_cd,
epp.enrt_cvg_end_dt_rl,
epp.rt_strt_dt_cd,
epp.rt_strt_dt_rl,
epp.rt_end_dt_cd,
epp.rt_end_dt_rl
from ben_enrt_perd_for_pl_f epp
where epp.ENRT_PERD_FOR_PL_ID=c_epfp_id
and c_effective_date
between epp.effective_start_date and epp.effective_end_date
and ( enrt_cvg_strt_dt_cd is not null
and enrt_cvg_end_dt_cd is not null
and rt_strt_dt_cd is not null
and rt_end_dt_cd is not null
);
select 2 order_number,
lee.enrt_cvg_strt_dt_cd,
lee.enrt_cvg_strt_dt_rl,
lee.enrt_cvg_end_dt_cd,
lee.enrt_cvg_end_dt_rl,
lee.rt_strt_dt_cd,
lee.rt_strt_dt_rl,
lee.rt_end_dt_cd,
lee.rt_end_dt_rl
from ben_lee_rsn_f lee
where lee.lee_rsn_id=c_lee_rsn_id
and c_effective_date
between lee.effective_start_date and lee.effective_end_date
and ( enrt_cvg_strt_dt_cd is not null
and enrt_cvg_end_dt_cd is not null
and rt_strt_dt_cd is not null
and rt_end_dt_cd is not null
);
select pln.name
from ben_pl_f pln
where pln.pl_id = c_pl_id and pln.business_group_id = p_business_group_id
and c_effective_date between pln.effective_start_date and pln.effective_end_date;
select pgm.name
from ben_pgm_f pgm
where pgm.pgm_id = c_pgm_id and pgm.business_group_id = p_business_group_id
and c_effective_date between pgm.effective_start_date and pgm.effective_end_date;
select opt.name
from ben_oipl_f oipl, ben_opt_f opt
where oipl.business_group_id=p_business_group_id
and oipl.opt_id = opt.opt_id and oipl.oipl_id = c_oipl_id
and c_effective_date between oipl.effective_start_date and oipl.effective_end_date
and c_effective_date between opt.effective_start_date and opt.effective_end_date;
select prv.rt_strt_dt,
prv.rt_end_dt
from ben_prtt_enrt_rslt_f pen,
ben_prtt_rt_val prv
where pen.per_in_ler_id = c_per_in_ler_id
and pen.pl_id=l_pl_id
and nvl(pen.pgm_id,-1)=nvl(l_pgm_id,-1)
and nvl(pen.oipl_id,-1)=nvl(l_oipl_id,-1)
--and pen.pl_typ_id = l_pl_typ_id
and pen.business_group_id = p_business_group_id
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and prv.per_in_ler_id = c_per_in_ler_id
and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and not exists
(select '1' from ben_prtt_enrt_rslt_f pen1
where pen.prtt_enrt_rslt_id = pen1.RPLCS_SSPNDD_RSLT_ID
and pen1.per_in_ler_id = c_per_in_ler_id);
l_union_set.delete;