[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pen.*
from ben_prtt_enrt_rslt_f pen
,ben_per_in_ler pil
,ben_ler_f ler
where pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.sspndd_flag = 'Y'
and pen.business_group_id = p_business_group_id
and pil.per_in_ler_id = pen.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and pil.lf_evt_ocrd_dt <= p_lf_evt_ocrd_dt
and pil.ler_id = ler.ler_id
and ler.typ_cd not in ('SCHEDDU', 'COMP', 'ABS','GSP')
and ler.business_group_id = pil.business_group_id
and p_effective_date between
ler.effective_start_date and ler.effective_end_date
and ((p_effective_date between
pen.effective_start_date and pen.effective_end_date)
or (p_lf_evt_ocrd_dt <= pen.effective_start_date)) -- 5741760: PEN recs on a Future date shud also be carried fwd.
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot;
select pea.*
from ben_prtt_enrt_actn_f pea
,ben_per_in_ler pil
where pea.business_group_id = p_business_group_id
and pea.cmpltd_dt is null
and pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pil.per_in_ler_id = pea.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and p_eff_dt between
pea.effective_start_date and pea.effective_end_date
order by pea.rqd_flag desc,pea.due_dt asc;
select pea.*,
pen.object_version_number pen_ovn
from ben_prtt_enrt_actn_f pea
,ben_prtt_enrt_rslt_f pen
,ben_per_in_ler pil
,ben_ler_f ler
where pea.business_group_id = p_business_group_id
and pea.cmpltd_dt is null
and pea.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.sspndd_flag = 'N'
and pen.business_group_id = pea.business_group_id
and pil.per_in_ler_id = pea.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and pil.ler_id = ler.ler_id
and p_eff_dt between
ler.effective_start_date and ler.effective_end_date
-- For Bug 6941981 Added an option
-- and ler.typ_cd not in ('COMP', 'ABS', 'GSP')
and ler.typ_cd not in ('SCHEDDU','COMP', 'ABS', 'GSP')
and p_eff_dt between
pea.effective_start_date and pea.effective_end_date
and p_eff_dt between
pen.effective_start_date and pen.effective_end_date
and nvl(pen.enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot;
select 'Y' epe_found_flag
from ben_elig_per_elctbl_chc epe
where per_in_ler_id = p_per_in_ler_id
and (epe.pgm_id is NULL or
epe.pgm_id = l_pen_rec.pgm_id )
and (epe.oipl_id is NULL or
epe.oipl_id = l_pen_rec.oipl_id )
and epe.pl_id = l_pen_rec.pl_id ;
select enrt_perd_strt_dt
from ben_pil_elctbl_chc_popl
where pil_elctbl_chc_popl_id = l_epe_rec.pil_elctbl_chc_popl_id;
select min(effective_start_date),
max(effective_end_date)
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
select object_version_number
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = l_pen_rec.prtt_enrt_rslt_id
and prtt_enrt_rslt_stat_cd is null
and p_eff_dt between effective_start_date
and effective_end_date;
select enrt_bnft_id
from ben_elctbl_chc_ctfn
where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and rqd_flag = 'Y'
and business_group_id = p_business_group_id;
l_update boolean;
l_update_override boolean;
l_update_change_insert boolean;
l_datetrack_mode := hr_api.g_delete;
hr_utility.set_location('Delete A1'||l_pen_rec.prtt_enrt_rslt_id ,10);
ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
(p_prtt_enrt_actn_id => l_act_sus.prtt_enrt_actn_id
,p_business_group_id => p_business_group_id
,p_effective_date => l_pea_effective_date
,p_datetrack_mode => l_datetrack_mode
,p_object_version_number => l_act_sus.object_version_number
,p_prtt_enrt_rslt_id => l_pen_rec.prtt_enrt_rslt_id
,p_rslt_object_version_number => l_pen_rec.object_version_number
,p_unsuspend_enrt_flag => 'N'
,p_gnrt_cm => false
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
hr_utility.set_location('delete_enrollment 1'||l_pen_rec.prtt_enrt_rslt_id,10);
ben_prtt_enrt_result_api.delete_enrollment
(p_prtt_enrt_rslt_id => l_pen_rec.prtt_enrt_rslt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_business_group_id => p_business_group_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_pen_rec.object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => hr_api.g_delete
,p_multi_row_validate => false --BUG 4718599 to be in sync with inelig
,p_source => 'beninelg');
hr_utility.set_location('delete_enrollment 2'
||l_pen_rec.prtt_enrt_rslt_id,10);
ben_prtt_enrt_result_api.delete_enrollment
(p_prtt_enrt_rslt_id => l_pen_rec.prtt_enrt_rslt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_business_group_id => p_business_group_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_pen_rec.object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => hr_api.g_delete
,p_multi_row_validate => false --BUG 4718599 true
,p_source => 'benmngle');
l_datetrack_mode := hr_api.g_delete;
hr_utility.set_location('OPtional delete_prtt_enrt_actn'||
l_pea_rec.prtt_enrt_actn_id,10);
ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
(p_prtt_enrt_actn_id => l_pea_rec.prtt_enrt_actn_id
,p_business_group_id => p_business_group_id
,p_effective_date => l_pea_effective_date
,p_datetrack_mode => l_datetrack_mode
,p_object_version_number => l_object_version_number
,p_prtt_enrt_rslt_id => l_pea_rec.prtt_enrt_rslt_id
,p_rslt_object_version_number => l_pea_rec.pen_ovn
,p_unsuspend_enrt_flag => 'N'
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
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 lcr.bkup_tbl_id PREV_PRTT_ENRT_CTFN_PRVDD_ID,
lcr.effective_start_date,
lcr.effective_end_date,
lcr.prtt_is_cvrd_flag ENRT_CTFN_RQD_FLAG,
lcr.comp_lvl_cd ENRT_CTFN_TYP_CD,
lcr.enrt_cvg_thru_dt ENRT_CTFN_RECD_DT,
lcr.prtt_enrt_rslt_id PREV_PRTT_ENRT_RSLT_ID,
lcr.pgm_id PREV_PRTT_ENRT_ACTN_ID,
lcr.enrt_ovrid_thru_dt ENRT_CTFN_DND_DT,
lcr.bnft_typ_cd ENRT_R_BNFT_CTFN_CD,
pcs.prtt_enrt_ctfn_prvdd_id,
pcs.object_version_number,
pcs.prtt_enrt_actn_id
from ben_le_clsn_n_rstr lcr
,ben_prtt_enrt_ctfn_prvdd_f pcs
where lcr.per_in_ler_ended_id = p_per_in_ler_id
and lcr.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_CTFN_PRVDD_F_UPD'
and pcs.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pcs.effective_end_date = hr_api.g_eot
and pcs.enrt_ctfn_recd_dt is null
and pcs.enrt_ctfn_typ_cd = lcr.comp_lvl_cd
and exists (select 'x' -- To confirm if both PENs are for the same comp.object
from ben_prtt_enrt_rslt_f pen_lcr
,ben_prtt_enrt_rslt_f pen_pcs
where pen_lcr.prtt_enrt_rslt_id = lcr.prtt_enrt_rslt_id
and pen_pcs.prtt_enrt_rslt_id = pcs.prtt_enrt_rslt_id
and nvl(pen_lcr.pgm_id,-1) = nvl(pen_pcs.pgm_id,-1)
and pen_lcr.pl_id = pen_pcs.pl_id
and nvl(pen_lcr.oipl_id, -1) = nvl(pen_pcs.oipl_id, -1)
)
;
l_datetrack_mode := hr_api.g_update;
ben_prtt_enrt_ctfn_prvdd_api.update_prtt_enrt_ctfn_prvdd
(p_prtt_enrt_ctfn_prvdd_id => l_prvdd_ctfns.prtt_enrt_ctfn_prvdd_id
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_prtt_enrt_actn_id => l_prvdd_ctfns.prtt_enrt_actn_id
,p_enrt_ctfn_recd_dt => l_prvdd_ctfns.enrt_ctfn_recd_dt
,p_object_version_number => l_prvdd_ctfns.object_version_number
,p_effective_date => l_effective_date
,p_business_group_id => p_business_group_id
,p_datetrack_mode => l_datetrack_mode);
select pil.lf_evt_ocrd_dt
from ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id ;
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,
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,
pen.bnft_ordr_num,
pen.rplcs_sspndd_rslt_id
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
/* 5741760: PEN in future, can also be carried-fwd.
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
*/ and p_effective_date <= pen.effective_end_date
AND pen.prtt_enrt_rslt_stat_cd is null
and pen.business_group_id = p_business_group_id
order by pen.effective_start_date desc;
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,
nvl(enb.crntly_enrld_flag,epe.crntly_enrd_flag) 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,
nvl(enb.prtt_enrt_rslt_id,epe.prtt_enrt_rslt_id) 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_enrt_bnft enb,
ben_per_in_ler pil,
ben_pil_elctbl_chc_popl pel
where NVL(enb.crntly_enrld_flag(+),epe.crntly_enrd_flag) = 'Y'
and pil.person_id = p_person_id
and pil.per_in_ler_id = p_per_in_ler_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 IN ('STRTD' ,'PROCD') -- 6156874
--Bug 5617091 for recalc it is set to PROCD before this call
and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
and enb.elig_per_elctbl_chc_id (+) = epe.elig_per_elctbl_chc_id
and epe.prtt_enrt_rslt_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.prtt_enrt_rslt_stat_cd IS NULL
and pen.per_in_ler_id = epe.per_in_ler_id
/* Added the below condition and commented the code for Bug 7426609 */
and pen.per_in_ler_id=p_per_in_ler_id
/*and pen.prtt_enrt_rslt_id = NVL(enb.prtt_enrt_rslt_id,
epe.prtt_enrt_rslt_id)*/
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot)
and exists (select null
from ben_prtt_enrt_rslt_f pen
where pen.pl_id = epe.pl_id
and pen.prtt_enrt_rslt_stat_cd IS NULL
and pen.per_in_ler_id <> epe.per_in_ler_id
and pen.prtt_enrt_rslt_id = NVL(enb.prtt_enrt_rslt_id,
epe.prtt_enrt_rslt_id)
and pen.sspndd_flag = 'Y'
and pen.enrt_cvg_thru_dt = hr_api.g_eot
-- and pen.effective_end_date = hr_api.g_eot -- 6156874
and pen.effective_end_date >= l_lf_evt_ocrd_dt )
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.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.prtt_enrt_rslt_id,
enb.cvg_mlt_cd,
enb.crntly_enrld_flag
from ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
and enb.crntly_enrld_flag = 'Y'
and enb.prtt_enrt_rslt_id is not NULL
and enb.ordr_num = v_bnft_ordr_num
;
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 prv.*
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.per_in_ler_id = p_per_in_ler_id
and prv.prtt_rt_val_stat_cd is NULL ;
select ecr.rt_strt_dt,
ecr.rt_strt_dt_cd,
ecr.rt_strt_dt_rl,
nvl(ecr.elig_per_elctbl_chc_id,enb.elig_per_elctbl_chc_id) elig_per_elctbl_chc_id
from ben_enrt_rt ecr,
ben_enrt_bnft enb
where ecr.prtt_rt_val_id = p_prtt_rt_val_id
and ecr.enrt_bnft_id = enb.enrt_bnft_id (+) ;
l_crd_fwd_pen_id.delete;
l_datetrack_mode := hr_api.g_update;
l_datetrack_mode := hr_api.g_insert;
update ben_elig_per_elctbl_chc
set dflt_flag = 'N'
where pl_typ_id = l_choice_info.pl_typ_id
and elig_per_elctbl_chc_id <> l_choice_info.elig_per_elctbl_chc_id
and crntly_enrd_flag = 'N' -- Bug 7378468
and dflt_flag = 'Y'
and nvl(pgm_id,-1)= nvl(l_choice_info.pgm_id,-1)
and per_in_ler_id = p_per_in_ler_id ;
update ben_elig_per_elctbl_chc
set dflt_flag = 'N'
where dflt_flag = 'Y'
and per_in_ler_id = l_choice_info.per_in_ler_id
and pl_id = l_choice_info.pl_id
and oipl_id <> l_choice_info.oipl_id
and nvl(pgm_id,-1) = nvl(l_choice_info.pgm_id,-1)
and elig_per_elctbl_chc_id <> l_choice_info.elig_per_elctbl_chc_id;
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 max(pea.per_in_ler_id)
from ben_prtt_enrt_actn_f pea,
ben_per_in_ler pil
where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pea.per_in_ler_id <> p_per_in_ler_id
and pea.business_group_id = p_business_group_id
and pea.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD');
select prtt_enrt_actn_id, effective_start_date, object_version_number
from ben_prtt_enrt_actn_f
where per_in_ler_id = p_per_in_ler_id
and prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and effective_end_date < hr_api.g_eot
and business_group_id = p_business_group_id
order by prtt_enrt_actn_id, effective_start_date; -- 5394656
select prtt_enrt_ctfn_prvdd_id, effective_start_date, object_version_number
from ben_prtt_enrt_ctfn_prvdd_f
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prtt_enrt_actn_id = p_prtt_enrt_actn_id
and effective_end_date < hr_api.g_eot
and business_group_id = p_business_group_id
order by prtt_enrt_ctfn_prvdd_id, effective_start_date; -- 5394656
select 1
from ben_prtt_prem_f
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and effective_end_date = hr_api.g_eot;
select ppm.prtt_prem_id, ppm.effective_start_date, ppm.object_version_number
from ben_prtt_prem_f ppm
where ppm.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and ppm.effective_end_date <> hr_api.g_eot
and not exists (select 1
from ben_prtt_prem_f ppm2
where ppm2.prtt_prem_id = ppm.prtt_prem_id
and ppm2.effective_end_date > ppm.effective_end_date)
order by effective_start_date desc;
select pen.prtt_enrt_rslt_id,
pen.per_in_ler_id
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pen.sspndd_flag = 'Y'
and pen.per_in_ler_id <> pil.per_in_ler_id
and pil.per_in_ler_id = p_per_in_ler_id
and pen.person_id = pil.person_id
and pen.effective_end_date = hr_api.g_eot
and pen.enrt_cvg_thru_dt = hr_api.g_eot;