[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pl_typ_id
from ben_pl_f pl
where pl.pl_id = p_pl_id
and p_effective_date between
pl.effective_start_date and
pl.effective_end_date ;
select opt_id
from ben_oipl_f oipl
where oipl.oipl_id = p_oipl_id
and p_effective_date between
oipl.effective_start_date and
oipl.effective_end_date ;
select max(pil.lf_evt_ocrd_dt)
from ben_per_in_ler pil
where pil.business_group_id = p_business_group_id
and pil.person_id = p_person_id
and pil.per_in_ler_id <> p_per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD');
select pen.prtt_enrt_rslt_id,pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
from ben_prtt_enrt_rslt_f pen,
ben_oipl_f oipl
where oipl.oipl_id = pen.oipl_id
and pen.pl_typ_id = l_pl_typ_id
and oipl.opt_id = l_opt_id
and pen.person_id = p_person_id
and pen.per_in_ler_id = p_per_in_ler_id
and pen.effective_end_date = hr_api.g_eot
and pen.enrt_cvg_thru_dt <> hr_api.g_eot
and pen.effective_start_date between oipl.effective_start_date
and oipl.effective_end_date
AND pen.prtt_enrt_rslt_stat_cd IS NULL
;
select pen.prtt_enrt_rslt_id,pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
from ben_prtt_enrt_rslt_f pen
where pen.pl_typ_id = l_pl_typ_id
and pen.person_id = p_person_id
and pen.per_in_ler_id = p_per_in_ler_id
and pen.effective_end_date = hr_api.g_eot
and pen.enrt_cvg_thru_dt <> hr_api.g_eot
AND pen.prtt_enrt_rslt_stat_cd IS NULL
;
select pen.prtt_enrt_rslt_id,pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
from ben_prtt_enrt_rslt_f pen,
ben_oipl_f oipl
where oipl.oipl_id = pen.oipl_id
and pen.pl_typ_id = l_pl_typ_id
and oipl.opt_id = l_opt_id
and pen.person_id = p_person_id
and pen.per_in_ler_id = p_per_in_ler_id
and pen.effective_end_date = hr_api.g_eot
and pen.enrt_cvg_thru_dt <> hr_api.g_eot
and pen.effective_start_date between oipl.effective_start_date
and oipl.effective_end_date
AND pen.prtt_enrt_rslt_stat_cd IS NULL
;
select pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
from ben_prtt_enrt_rslt_f pen
where
pen.prtt_enrt_rslt_id = p_prev_prtt_enrt_rslt_id
and pen.person_id = p_person_id
-- this condition removed to CFD from any result
--and pen.per_in_ler_id = p_per_in_ler_id
and pen.effective_end_date = hr_api.g_eot
and pen.enrt_cvg_thru_dt <> hr_api.g_eot
AND pen.prtt_enrt_rslt_stat_cd is null
;
select pen.prtt_enrt_rslt_id,pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
from ben_prtt_enrt_rslt_f pen
where pen.pl_typ_id = l_pl_typ_id
and pen.person_id = p_person_id
and pen.per_in_ler_id = p_per_in_ler_id
and pen.effective_end_date = hr_api.g_eot
and pen.enrt_cvg_thru_dt <> hr_api.g_eot
AND pen.prtt_enrt_rslt_stat_cd IS NULL
;
select
pdp_old.EFFECTIVE_END_DATE,
pdp_old.CVG_STRT_DT,
pdp_old.CVG_THRU_DT,
pdp_old.CVG_PNDG_FLAG,
pdp_old.OVRDN_FLAG,
pdp_old.OVRDN_THRU_DT,
pdp_old.PRTT_ENRT_RSLT_ID,
pdp_old.DPNT_PERSON_ID,
pdp_old.PER_IN_LER_ID,
pdp_old.BUSINESS_GROUP_ID,
pdp_old.PDP_ATTRIBUTE_CATEGORY,
pdp_old.PDP_ATTRIBUTE1,
pdp_old.PDP_ATTRIBUTE2,
pdp_old.PDP_ATTRIBUTE3,
pdp_old.PDP_ATTRIBUTE4,
pdp_old.PDP_ATTRIBUTE5,
pdp_old.PDP_ATTRIBUTE6,
pdp_old.PDP_ATTRIBUTE7,
pdp_old.PDP_ATTRIBUTE8,
pdp_old.PDP_ATTRIBUTE9,
pdp_old.PDP_ATTRIBUTE10,
pdp_old.PDP_ATTRIBUTE11,
pdp_old.PDP_ATTRIBUTE12,
pdp_old.PDP_ATTRIBUTE13,
pdp_old.PDP_ATTRIBUTE14,
pdp_old.PDP_ATTRIBUTE15,
pdp_old.PDP_ATTRIBUTE16,
pdp_old.PDP_ATTRIBUTE17,
pdp_old.PDP_ATTRIBUTE18,
pdp_old.PDP_ATTRIBUTE19,
pdp_old.PDP_ATTRIBUTE20,
pdp_old.PDP_ATTRIBUTE21,
pdp_old.PDP_ATTRIBUTE22,
pdp_old.PDP_ATTRIBUTE23,
pdp_old.PDP_ATTRIBUTE24,
pdp_old.PDP_ATTRIBUTE25,
pdp_old.PDP_ATTRIBUTE26,
pdp_old.PDP_ATTRIBUTE27,
pdp_old.PDP_ATTRIBUTE28,
pdp_old.PDP_ATTRIBUTE29,
pdp_old.PDP_ATTRIBUTE30,
pdp_old.LAST_UPDATE_DATE,
pdp_old.LAST_UPDATED_BY,
pdp_old.LAST_UPDATE_LOGIN,
pdp_old.CREATED_BY,
pdp_old.CREATION_DATE,
pdp_old.REQUEST_ID,
pdp_old.PROGRAM_APPLICATION_ID,
pdp_old.PROGRAM_ID,
pdp_old.PROGRAM_UPDATE_DATE,
pdp_old.OBJECT_VERSION_NUMBER,
pdp_old.elig_cvrd_dpnt_id,
pdp_old.EFFECTIVE_START_DATE
from ben_elig_cvrd_dpnt_f pdp_old
where
pdp_old.per_in_ler_id = v_per_in_ler_id
and pdp_old.prtt_enrt_rslt_id = v_enrt_rslt_id
and pdp_old.business_group_id = p_business_group_id;
select edg.*
from ben_elig_dpnt edg
where edg.elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id
and edg.business_group_id = p_business_group_id
and edg.dpnt_person_id = l_dpnt_person_id;
select r.mx_dpnts_alwd_num,
r.no_mx_num_dfnd_flag,
r.dsgn_rqmt_id,
r.grp_rlshp_cd
from ben_dsgn_rqmt_f r
where ((r.pl_id = p_pl_id)
or
(r.oipl_id = p_oipl_id)
or
(r.opt_id = (select opt_id
from ben_oipl_f
where oipl_id = p_oipl_id
and p_effective_date between effective_start_date
and effective_end_date
and business_group_id = p_business_group_id)))
and r.dsgn_typ_cd = 'DPNT'
-- this should be reoved but couldnt locate relation between grp and type
-- and r.grp_rlshp_cd is null
--
and r.business_group_id = p_business_group_id
and p_effective_date between r.effective_start_date
and r.effective_end_date;
select count(pdp.dpnt_person_id)
from ben_elig_cvrd_dpnt_f pdp,
ben_elig_dpnt egd ,
per_contact_relationships pcr
where pdp.business_group_id = p_business_group_id
and pdp.per_in_ler_id = v_per_in_ler_id
and pdp.prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
and pdp.cvg_strt_dt is not null
and p_effective_date between pdp.effective_start_date
and pdp.effective_end_date
and egd.business_group_id = pdp.business_group_id
and pdp.dpnt_person_id = egd.dpnt_person_id
and egd.per_in_ler_id = v_per_in_ler_id
and egd.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and pcr.person_id = p_person_id
and pcr.contact_person_id = egd.dpnt_person_id
and p_effective_date between nvl(pcr.date_start,p_effective_date)
and nvl(pcr.date_end,p_effective_date)
and (pcr.contact_type in
( select drt.rlshp_typ_cd
from ben_dsgn_rqmt_f bdr ,
ben_dsgn_rqmt_rlshp_typ drt
where bdr.dsgn_rqmt_id = v_dsgn_rqmt_id
and drt.dsgn_rqmt_id = bdr.dsgn_rqmt_id
and ( bdr.grp_rlshp_cd = v_grp_rlshp_cd or
(bdr.grp_rlshp_cd is null and v_grp_rlshp_cd is null )
)
and p_effective_date between bdr.effective_start_date
and bdr.effective_end_date
)
--- if there is no relation typ defind take all
or
not exists
(select 'x' from ben_dsgn_rqmt_rlshp_typ drt
where drt.dsgn_rqmt_id = v_dsgn_rqmt_id
)
) ;
p_datetrack_mode => hr_api.g_insert,
p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id,
p_effective_start_date => l_eff_start_date,
p_effective_end_date => l_eff_end_date,
p_object_version_number => l_object_version_number,
p_multi_row_actn => TRUE );
select pen.prtt_enrt_rslt_id,
pen.effective_start_date,
pen.effective_end_date,
pen.object_version_number,
pen.bnft_amt,
pen.uom,
pen.enrt_mthd_cd,
pen.business_group_id,
pen.enrt_cvg_strt_dt,
pen.enrt_cvg_thru_dt,
-- 3517682 start
pen.pen_attribute_category ,
pen.pen_attribute1 ,
pen.pen_attribute2 ,
pen.pen_attribute3 ,
pen.pen_attribute4 ,
pen.pen_attribute5 ,
pen.pen_attribute6 ,
pen.pen_attribute7 ,
pen.pen_attribute8 ,
pen.pen_attribute9 ,
pen.pen_attribute10 ,
pen.pen_attribute11 ,
pen.pen_attribute12 ,
pen.pen_attribute13 ,
pen.pen_attribute14 ,
pen.pen_attribute15 ,
pen.pen_attribute16 ,
pen.pen_attribute17 ,
pen.pen_attribute18 ,
pen.pen_attribute19 ,
pen.pen_attribute20 ,
pen.pen_attribute21 ,
pen.pen_attribute22,
pen.pen_attribute23,
pen.pen_attribute24,
pen.pen_attribute25,
pen.pen_attribute26,
pen.pen_attribute27,
pen.pen_attribute28,
pen.pen_attribute29,
pen.pen_attribute30
-- 3517682 end
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date
and pen.business_group_id = p_business_group_id;
select pil.object_version_number,
epe.elig_per_elctbl_chc_id,
pel.enrt_typ_cycl_cd,
epe.enrt_cvg_strt_dt_cd,
pel.enrt_perd_end_dt,
pel.enrt_perd_strt_dt,
epe.enrt_cvg_strt_dt_rl,
epe.enrt_cvg_strt_dt,
to_date('31-12-4712','DD-MM-YYYY') enrt_cvg_end_dt,
epe.crntly_enrd_flag,
epe.dflt_flag,
epe.elctbl_flag,
epe.mndtry_flag,
pel.dflt_enrt_dt,
epe.dpnt_cvg_strt_dt_cd,
epe.dpnt_cvg_strt_dt_rl,
epe.alws_dpnt_dsgn_flag,
epe.dpnt_dsgn_cd,
epe.ler_chg_dpnt_cvg_cd,
epe.erlst_deenrt_dt,
epe.procg_end_dt,
epe.comp_lvl_cd,
epe.pl_id,
epe.oipl_id,
epe.pgm_id,
epe.plip_id,
epe.ptip_id,
epe.pl_typ_id,
epe.cmbn_ptip_id,
epe.cmbn_ptip_opt_id,
epe.spcl_rt_pl_id,
epe.spcl_rt_oipl_id,
epe.must_enrl_anthr_pl_id,
epe.prtt_enrt_rslt_id,
epe.bnft_prvdr_pool_id,
epe.per_in_ler_id,
epe.yr_perd_id,
epe.business_group_id,
'N' stage,
'N' suspended,
epe.cryfwd_elig_dpnt_cd
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil,
ben_pil_elctbl_chc_popl pel
where epe.auto_enrt_flag = 'Y'
--
-- Bug No: 4024312
-- bug 4730268 reverse the fix of 4024312
-- as per discussion wth pb/lt we will add new enrollment method to validate
-- electable flag on auto enrollment. since many ct are use to the current way
-- we are reversing the fix
--and epe.elctbl_flag = 'Y'
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_id = v_per_in_ler_id -- bug 4968574 :Get correct row for non-Unrestricted as UNRESTRICT is always in STARTED state
and epe.business_group_id = pil.business_group_id
and pel.business_group_id = epe.business_group_id
and pil.person_id = p_person_id
and epe.per_in_ler_id = pil.per_in_ler_id
and pel.per_in_ler_id = epe.per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
and (epe.pgm_id is not null
and not exists(select null
from ben_prtt_enrt_rslt_f pen
where pen.pgm_id = epe.pgm_id
and pen.per_in_ler_id = epe.per_in_ler_id
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot)
or epe.pl_id is not null
and not exists(select null
from ben_prtt_enrt_rslt_f pen
where pen.pl_id = epe.pl_id
and pen.per_in_ler_id = epe.per_in_ler_id
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot)
)
order by epe.pgm_id, epe.pl_id;
select null
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil,
ben_pil_elctbl_chc_popl pel
where epe.elctbl_flag = 'Y'
--and pil.business_group_id = p_business_group_id
--and epe.business_group_id = pil.business_group_id
--and pel.business_group_id = epe.business_group_id
and pil.person_id = p_person_id
and epe.per_in_ler_id = pil.per_in_ler_id
and pel.per_in_ler_id = epe.per_in_ler_id
and pil.ler_id = p_ler_id -- 5861855: Only the current LE should be checked.
and pil.per_in_ler_stat_cd = 'STRTD'
and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
and (epe.pgm_id is not null
and not exists(select null
from ben_prtt_enrt_rslt_f pen
where pen.pgm_id = epe.pgm_id
and pen.per_in_ler_id = epe.per_in_ler_id
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot)
or epe.pl_id is not null
and not exists(select null
from ben_prtt_enrt_rslt_f pen
where pen.pl_id = epe.pl_id
and pen.per_in_ler_id = epe.per_in_ler_id
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot));
select pel.enrt_perd_strt_dt
from ben_pil_elctbl_chc_popl pel,ben_elig_per_elctbl_chc epe
where pel.pgm_id = v_pgm_id
and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
and epe.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id;
select pel.enrt_perd_strt_dt
from ben_pil_elctbl_chc_popl pel,ben_elig_per_elctbl_chc epe
where pel.pl_id = v_pl_id
and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
and epe.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id;
select enb.enrt_bnft_id,
decode(enb.entr_val_at_enrt_flag,'Y',enb.dflt_val,enb.val) val,
enb.dflt_flag,
enb.cvg_mlt_cd
from ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
;
select ecr.enrt_rt_id,
nvl(ecr.val,ecr.dflt_val) default_value,
nvl(ecr.ann_dflt_val,ecr.ann_val) ann_rt_val,
ecr.prtt_rt_val_id
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
and ecr.business_group_id = p_business_group_id
and ecr.entr_val_at_enrt_flag = 'Y'
and ecr.spcl_rt_enrt_rt_id is null
union
select ecr.enrt_rt_id,
nvl(ecr.val,ecr.dflt_val) default_value,
nvl(ecr.ann_dflt_val,ecr.ann_val) ann_rt_val,
ecr.prtt_rt_val_id
from ben_enrt_rt ecr
where ecr.enrt_bnft_id = v_enrt_bnft_id
and ecr.business_group_id = p_business_group_id
and ecr.entr_val_at_enrt_flag = 'Y'
and ecr.spcl_rt_enrt_rt_id is null
;
select distinct pen.pgm_id pgm_id, null pl_id
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe
where pil.per_in_ler_id = p_per_in_ler_id
and pil.per_in_ler_Stat_cd = 'STRTD'
and pen.per_in_ler_id = pil.per_in_ler_id
and pen.business_group_id = p_business_group_id
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.pgm_id=epe.pgm_id
and epe.auto_enrt_flag = 'Y'
and epe.per_in_ler_id = pil.per_in_ler_id
UNION
select distinct null pgm_id, pen.pl_id pl_id
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe
where pil.per_in_ler_id = p_per_in_ler_id
and pil.per_in_ler_Stat_cd = 'STRTD'
and pen.per_in_ler_id = pil.per_in_ler_id
and pen.business_group_id = p_business_group_id
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot
and pen.pgm_id is null
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
and epe.auto_enrt_flag = 'Y'
and epe.per_in_ler_id = pil.per_in_ler_id;
select pil.lf_evt_ocrd_dt
from ben_per_in_ler pil
where pil.business_group_id = p_business_group_id
and pil.per_in_ler_id = p_per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD');
l_datetrack_mode := hr_api.g_update;
l_datetrack_mode := hr_api.g_insert;
if l_datetrack_mode = hr_api.g_insert and l_cryfwd_elig_dpnt_cd = 'CFRRWP' then
hr_utility.set_location('cvg strt cd: '||l_choice_info.dpnt_cvg_strt_dt_cd,10);
select distinct(pil.person_id) -- Bug 5529696
from ben_per_in_ler pil, ben_pil_elctbl_chc_popl pel
where
pel.dflt_asnd_dt is null
-- and pil.business_group_id = p_business_group_id
and pel.dflt_asnd_dt is null
and pel.elcns_made_dt is null
and pil.person_id = p_person_id
and pil.ler_id = p_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and pil.per_in_ler_id = pel.per_in_ler_id
--and pel.business_group_id = pil.business_group_id
and exists(select null
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel
where epe.per_in_ler_id = pil.per_in_ler_id
and epe.per_in_ler_id = pel.per_in_ler_id
and pel.pil_elctbl_chc_popl_id =
epe.pil_elctbl_chc_popl_id
and pel.business_group_id = epe.business_group_id
and epe.business_group_id = pil.business_group_id
and epe.auto_enrt_flag = 'Y');