[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pil.lf_evt_ocrd_dt,
pil.ler_id
from ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id;
select prv.rt_val , prv.tx_typ_cd,prv.acty_typ_cd
from ben_prtt_enrt_rslt_f pen,
ben_prtt_rt_val prv,
ben_acty_base_rt_f abr,
ben_pl_f pln,
ben_per_in_ler pil
where pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_cvg_thru_dt = p_eot
and pen.business_group_id = p_business_group_id
and pen.pl_id = pln.pl_id
and pen.sspndd_flag = 'N'
and pln.subj_to_imptd_incm_typ_cd =p_subj_to_imptd_incm_typ_cd
and pln.pl_stat_cd = 'A'
and pln.business_group_id = p_business_group_id
and prv.PRTT_RT_VAL_STAT_CD is null
--and prv.per_in_ler_id = pen.per_in_ler_id
and prv.Rt_end_dt = p_eot
and prv.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
and p_effective_date between pln.effective_start_date
and pln.effective_end_date
and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
AND pen.effective_end_date=hr_api.g_eot
and l_max_le_eff_date >= least(pen.effective_start_date,pen.enrt_cvg_strt_dt )
and abr.acty_base_rt_id = prv.acty_base_rt_id
and abr.subj_to_imptd_incm_flag = 'Y'
and l_max_le_eff_date between abr.effective_start_date and abr.effective_end_date ;
select sum(pen.bnft_amt)
from ben_prtt_enrt_rslt_f pen,
ben_pl_f pln,
ben_per_in_ler pil
where pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_cvg_thru_dt = p_eot
and pen.business_group_id = p_business_group_id
and pen.pl_id = pln.pl_id
and pen.sspndd_flag = 'N'
and pln.subj_to_imptd_incm_typ_cd =p_subj_to_imptd_incm_typ_cd
and pln.pl_stat_cd = 'A'
and pln.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
and p_effective_date between pln.effective_start_date
and pln.effective_end_date
-- Bug # - 1675410 - If the coverage start date is after life event occurred date,
-- the above condition does not select result row. As much as per_in_ler_id is
-- existing in pen, it is better to join pil with pen by per_in_ler_id
and pil.per_in_ler_id = pen.per_in_ler_id
AND pen.effective_end_date=hr_api.g_eot
-- Bug 1884964 to restrict pen records from different set
and l_max_le_eff_date >= least(pen.effective_start_date,pen.enrt_cvg_strt_dt )
and exists
(select 'x'
from ben_elig_per_f pep
where pep.pl_id=pen.pl_id
and nvl(pep.pgm_id,-1)=nvl(pen.pgm_id,-1)
and pep.person_id=pen.person_id
and pep.per_in_ler_id = pil.per_in_ler_id
and pep.pl_hghly_compd_flag = p_highly_comp
and pep.prtn_strt_dt <= greatest(pen.enrt_cvg_strt_dt,l_max_le_eff_date)
and pep.business_group_id = pen.business_group_id);
select pln.pl_id,
pln.pl_cd
from ben_pl_f pln
where pln.imptd_incm_calc_cd = p_imptd_incm_calc_cd
and pln.pl_stat_cd = 'A'
and pln.business_group_id = p_business_group_id
and p_effective_date
between pln.effective_start_date
and pln.effective_end_date;
select pen.prtt_enrt_rslt_id,
pen.object_version_number,
pen.pl_id,
pen.oipl_id,
pen.pgm_id,
pen.pl_typ_id
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.pl_id = p_pl_id and
pen.oipl_id is null and
pen.person_id = p_person_id
AND l_pil_rec.lf_evt_ocrd_dt between
pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
AND pen.effective_end_date=hr_api.g_eot;
select pen.prtt_enrt_rslt_id,
pen.object_version_number,
pen.pl_id,
pen.oipl_id,
pen.pgm_id,
pen.pl_typ_id
from ben_pl_f pln,
ben_prtt_enrt_rslt_f pen
where pln.imptd_incm_calc_cd = p_imptd_incm_calc_cd
and pln.pl_stat_cd = 'A'
and p_effective_date
between pln.effective_start_date
and pln.effective_end_date
and pen.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.pl_id = pln.pl_id
and pen.oipl_id is null
and pen.person_id = p_person_id
and pen.effective_end_date = hr_api.g_eot
and pen.enrt_cvg_thru_dt = hr_api.g_eot;
select epe.ELIG_PER_ELCTBL_CHC_ID,
epe.PL_ID,
epe.OIPL_ID,
epe.PGM_ID,
epe.PL_TYP_ID,
epe.PER_IN_LER_ID,
nvl(epe.prtt_enrt_rslt_id,-1) prtt_enrt_rslt_id
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.business_group_id = p_business_group_id
and epe.pl_id = p_pl_id
and epe.oipl_id is null
order by prtt_enrt_rslt_id desc;
select ecr.enrt_rt_id,
ecr.acty_base_rt_id,
ecr.prtt_rt_val_id
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id and
ecr.business_group_id = p_business_group_id
and ecr.rt_usg_cd = 'IMPTDINC';
select prv.prtt_rt_val_id,
prv.acty_base_rt_id,
prv.rt_ovridn_flag, -- Bug 2200139 Override changes
prv.rt_ovridn_thru_dt -- Bug 2200139 Override changes
from ben_prtt_rt_val prv
where prv.business_group_id = p_business_group_id
and prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.prtt_rt_val_stat_cd is null
and prv.rt_end_dt = hr_api.g_eot;
select null
from ben_prtt_enrt_rslt_f pen,
ben_pl_f pln
where pen.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.pgm_id = p_pgm_id
and pen.person_id = p_person_id
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date= hr_api.g_eot
and pln.pl_id = pen.pl_id
and pln.subj_to_imptd_incm_typ_cd =p_subj_to_imptd_incm_typ_cd
and pln.pl_stat_cd = 'A'
and pln.business_group_id = p_business_group_id
and p_effective_date between pln.effective_start_date
and pln.effective_end_date;
select acty_base_rt_id
from ben_acty_base_rt_f abr
where abr.pl_id=p_plan_id
and p_effective_date between abr.effective_start_date and abr.effective_end_date
and abr.business_group_id = p_business_group_id ;
select b.mlt_cd from
ben_acty_vrbl_rt_f a ,
ben_vrbl_rt_prfl_f b
where
a.acty_base_rt_id = p_abr_id
and a.vrbl_rt_prfl_id = b.vrbl_rt_prfl_id
and a.business_group_id =p_business_group_id
and p_effective_date between a.effective_start_date and a.effective_end_date
and p_effective_date between b.effective_start_date and b.effective_end_date
and ( mlt_cd not in ('FLFX' , 'RL') or VRBL_RT_TRTMT_CD <> 'RPLC' )
;
l_datetrack_mode varchar2(30) := 'INSERT';
ben_provider_pools.update_rate
(p_prtt_rt_val_id => l_ecr_prv_rec.prtt_rt_val_id,
p_val => rate_val_list(l_count),
p_prtt_enrt_rslt_id => l_enrt_rslt_rec.prtt_enrt_rslt_id,
p_business_group_id => p_business_group_id,
p_ended_per_in_ler_id => p_per_in_ler_id,
p_effective_date => p_effective_date);
fnd_message.set_token('PROC','ben_election_information -update');
fnd_message.set_token('PROC','ben_election_information-insert');
ben_prtt_enrt_result_api.delete_enrollment
(p_prtt_enrt_rslt_id => l_enrt_rslt_rec.prtt_enrt_rslt_id,
p_per_in_ler_id => p_per_in_ler_id,
p_object_version_number => l_enrt_rslt_rec.object_version_number
,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_delete,
p_business_group_id => p_business_group_id,
p_source => 'bendeimp',
p_multi_row_validate => FALSE);
select 'Y'
from ben_pl_f pln
where pln.imptd_incm_calc_cd is not null
and pln.pl_stat_cd = 'A'
and pln.business_group_id = p_business_group_id
and p_effective_date
between pln.effective_start_date
and pln.effective_end_date;
select 'Y'
from ben_pl_f pln,
ben_elig_per_elctbl_chc epe
where pln.subj_to_imptd_incm_typ_cd is not null
and pln.pl_id = epe.pl_id
and epe.per_in_ler_id = p_per_in_ler_id
and pln.pl_stat_cd = 'A'
and pln.business_group_id = p_business_group_id
and p_effective_date
between pln.effective_start_date
and pln.effective_end_date;
select 'Y'
from ben_prtt_enrt_rslt_f pen
where pen.person_id = p_person_id
and pen.comp_lvl_cd = 'PLANIMP'
and pen.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_cvg_thru_dt = to_date('31-12-4712','dd-mm-yyyy')
and exists (select null from ben_elig_per_elctbl_chc epe
where pen.pl_id = epe.pl_id
and epe.per_in_ler_id = p_per_in_ler_id);