[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
rate starting before the earliest coverage start date of plan subject to imputed income should be deleted, because
it would be corresponding to the plan that got de-enrolled. procedure delete_past_imp is used for this purpose
*/
procedure delete_past_imp(p_person_id number,
p_per_in_ler_id number,
p_business_group_id number,
p_effective_date date,
p_erlst_cvg_strt date,
p_imptd_incm_calc_cd varchar2)
is
--cursor to fetch imputed income plans
cursor c_imp_inc_plan(p_imptd_incm_calc_cd varchar2, p_business_group_id number, p_effective_date date)
is
select pln.pl_id
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 prv.*
from ben_prtt_rt_val prv, ben_per_in_ler pil
where pil.person_id = p_person_id
and prv.per_in_ler_id = p_per_in_ler_id
and prv.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and prv.prtt_enrt_rslt_id in (select pen.prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f pen, ben_per_in_ler pil
where pil.person_id = p_person_id
and pen.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pen.pl_id = l_pl_id
and pen.per_in_ler_id = p_per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd is NULL)
and prv.rt_strt_dt < p_erlst_cvg_strt
order by prv.rt_strt_dt desc ;
l_proc varchar2(72) := g_package||'delete_past_imp';
hr_utility.set_location (' delete past rate prv', 2);
update ben_enrt_rt set prtt_rt_val_id = NULL
where prtt_rt_val_id = nvl(l_imp_inc_rt_tab(r).prtt_rt_val_id,-1);
ben_prtt_rt_val_api.delete_prtt_rt_val
(p_validate => false
,p_prtt_rt_val_id => l_imp_inc_rt_tab(r).prtt_rt_val_id
,p_enrt_rt_id => NULL
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_imp_inc_rt_tab(r).object_version_number
,p_effective_date => l_imp_inc_rt_tab(r).rt_strt_dt);
END delete_past_imp;
/* 8716870: procedure delete_imp_inc is addded to delete the future rate and enrollment for imputed shell plan */
procedure delete_imp_inc(p_person_id number,
p_per_in_ler_id number,
p_business_group_id number,
p_effective_date date,
p_imptd_incm_calc_cd varchar2)
is
l_proc varchar2(60) := g_package||'.delete_imp_inc';
l_delete boolean;
l_delete_next_change boolean;
select pln.pl_id
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.*
from ben_prtt_enrt_rslt_f pen, ben_per_in_ler pil
where pil.person_id = p_person_id
and pen.per_in_ler_id = p_per_in_ler_id
and pen.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pen.pl_id = l_pl_id
and pen.enrt_cvg_strt_dt > p_effective_date
order by pen.effective_start_date, pen.enrt_cvg_strt_dt desc ;
select prv.*
from ben_prtt_rt_val prv, ben_per_in_ler pil
where pil.person_id = p_person_id
and prv.per_in_ler_id = p_per_in_ler_id
and prv.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and prv.prtt_enrt_rslt_id in (select pen.prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f pen, ben_per_in_ler pil
where pil.person_id = p_person_id
and pen.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pen.pl_id = l_pl_id
and pen.per_in_ler_id = p_per_in_ler_id
and pen.enrt_cvg_strt_dt > p_effective_date --Bug 14559387
and pen.prtt_enrt_rslt_stat_cd is NULL)
-- and prv.rt_strt_dt > p_effective_date
and prv.rt_strt_dt > = p_effective_date --Bug 14559387
order by prv.rt_strt_dt desc ;
select 1
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_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
and pen.enrt_cvg_thru_dt = hr_api.g_eot;
select prtt_enrt_rslt_id,per_in_ler_id,pl_typ_id,pl_id
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and per_in_ler_id = p_per_in_ler_id
and prtt_enrt_rslt_Stat_cd is NULL;
select pen.*
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between pen.effective_start_date and pen.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is NULL
order by pen.prtt_enrt_rslt_id, pen.effective_start_date desc;
select lcnr.*
from BEN_LE_CLSN_N_RSTR lcnr
where lcnr.bkup_tbl_id = l_prtt_enrt_rslt_id
and lcnr.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR'
and lcnr.enrt_cvg_thru_dt = hr_api.g_eot
and lcnr.Per_in_ler_ended_id = l_per_in_ler_id;
hr_utility.set_location (' delete future rate prv', 2);
update ben_enrt_rt set prtt_rt_val_id = NULL
where prtt_rt_val_id = nvl(l_imp_inc_rt_tab(r).prtt_rt_val_id,-1);
ben_prtt_rt_val_api.delete_prtt_rt_val
(p_validate => false
,p_prtt_rt_val_id => l_imp_inc_rt_tab(r).prtt_rt_val_id
,p_enrt_rt_id => NULL
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_imp_inc_rt_tab(r).object_version_number
,p_effective_date => l_imp_inc_rt_tab(r).rt_strt_dt);
hr_utility.set_location (' delete imputed enrollment', 2);
l_delete := null;
l_delete_next_change := null;
p_delete => l_delete,
p_future_change => l_future_change,
p_delete_next_change => l_delete_next_change);
elsif l_delete_next_change then
--
l_mode := hr_api.g_delete_next_change;
l_imp_inc_enrt_tab_prior.delete;
update ben_prtt_enrt_rslt_f
set ler_id = l_corr_pen_rec.ler_id,
per_in_ler_id = l_corr_pen_rec.per_in_ler_id
where prtt_enrt_rslt_id = l_corr_pen_rec.bkup_tbl_id
and effective_start_date = l_corr_pen_rec.effective_start_date
and exists (select 1
from ben_per_in_ler
where per_in_ler_id = l_corr_pen_rec.per_in_ler_id
and per_in_ler_stat_cd not in ('BCKDT','VOIDD'))
and business_group_id = p_business_group_id
and person_id = l_corr_pen_rec.person_id;
delete from BEN_LE_CLSN_N_RSTR
where bkup_tbl_id = nvl(l_corr_pen_rec.bkup_tbl_id,-1)
and Per_in_ler_ended_id = nvl(l_imp_inc_enrt_tab(r).per_in_ler_id,-1)
and BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR';
ben_prtt_enrt_result_api.delete_prtt_enrt_result
(p_validate => false,
p_prtt_enrt_rslt_id => l_delrec.prtt_enrt_rslt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_object_version_number,
p_effective_date => l_delrec.effective_start_date,
p_datetrack_mode => l_mode,
p_multi_row_validate => FALSE);
ben_prtt_enrt_result_api.delete_prtt_enrt_result
(p_validate => false,
p_prtt_enrt_rslt_id => l_delrec.prtt_enrt_rslt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_object_version_number,
p_effective_date => l_delrec.effective_start_date,
p_datetrack_mode => l_mode,
p_multi_row_validate => FALSE);
end delete_imp_inc;
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 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_imp_cvg_strt_dt between pln.effective_start_date
and pln.effective_end_date -- Bug 9436910
-- 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 p_cvg_strt_dt between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_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.effective_start_date, -- 8716870
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.effective_start_date, -- 8716870
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
/* 8716870 code changes */
-- 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' )
;
select pil.lf_evt_ocrd_dt, popl.elcns_made_dt, popl.dflt_asnd_dt, popl.dflt_enrt_dt
from ben_per_in_ler pil, ben_pil_elctbl_chc_popl popl
where popl.per_in_ler_id (+) = pil.per_in_ler_id
and pil.per_in_ler_id = p_per_in_ler_id;
select epe.ELIG_PER_ELCTBL_CHC_ID,
epe.pgm_id,
epe.pl_typ_id,
epe.pl_id,
epe.fonm_cvg_strt_dt
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil,
ben_pl_f pl,
ben_pil_elctbl_chc_popl pel /* bug 10262697 */
where epe.PER_IN_LER_ID = p_per_in_ler_id
and pil.per_in_ler_id = p_per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
and epe.pl_id = pl.pl_id
and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id /* bug 10262697 */
and p_effective_date between pl.effective_start_date and pl.effective_end_date
and pl.imptd_incm_calc_cd = p_imptd_incm_calc_cd;
l_datetrack_mode varchar2(30) := 'INSERT';
hr_utility.set_location (' calling delete_imp_inc with effective date as l_rt_strt_dt ', 2);
delete_imp_inc(p_person_id => p_person_id,
p_per_in_ler_id => p_per_in_ler_id,
p_business_group_id => p_business_group_id,
p_effective_date => nvl(l_rt_strt_dt,l_cvg_strt_dt),
p_imptd_incm_calc_cd => p_subj_to_imptd_incm_typ_cd);
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 => l_eff_date_for_enrt, --8716870
p_datetrack_mode => hr_api.g_delete,
p_business_group_id => p_business_group_id,
p_source => 'bendeimp',
p_multi_row_validate => FALSE);
select pen.prtt_enrt_rslt_id, pen.enrt_cvg_strt_Dt,pl.subj_to_imptd_incm_typ_cd
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil,
ben_pl_f pl
where pen.pl_id = pl.pl_id
and pen.sspndd_flag = 'N'
and pen.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pen.person_id = pil.person_id
and pen.prtt_enrt_rslt_stat_cd is NULL
and p_effective_date between pl.effective_start_date and pl.effective_end_date
/* Bug 13399004 : Commented the below two coniditons*/
--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.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
and pen.person_id = p_person_id
--and pil.person_id = p_person_id
and pil.per_in_ler_id = p_per_in_ler_id
and subj_to_imptd_incm_typ_cd is not null
union
select pen.prtt_enrt_rslt_id, pen.enrt_cvg_strt_Dt,pl.subj_to_imptd_incm_typ_cd
from ben_prtt_enrt_rslt_f pen,
ben_elig_per_elctbl_chc epe,
ben_pl_f pl,
ben_per_in_ler pil
where epe.per_in_ler_id <> pen.per_in_ler_id
and pen.pl_id = pl.pl_id
and epe.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and epe.per_in_ler_id = pil.per_in_ler_id
and nvl(epe.pl_id,-1) = nvl(pen.pl_id,-1)
and nvl(epe.oipl_id,-1) = nvl(pen.oipl_id,-1)
and epe.elctbl_flag = 'N'
and epe.crntly_enrd_flag = 'Y'
and pen.sspndd_flag = 'N'
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pen.prtt_enrt_rslt_stat_cd is NULL
and p_effective_date between pl.effective_start_date and pl.effective_end_date
-- and p_effective_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot
and pen.person_id = pil.person_id
and pen.person_id = p_person_id
and epe.per_in_ler_id = p_per_in_ler_id
and pl.subj_to_imptd_incm_typ_cd is not null
order by 2 ;
select pen.prtt_enrt_rslt_id, pen.enrt_cvg_strt_Dt,pl.subj_to_imptd_incm_typ_cd
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil,
ben_pl_f pl
where pen.pl_id = pl.pl_id
and pen.sspndd_flag = 'N'
and pen.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pen.person_id = pil.person_id
and pen.prtt_enrt_rslt_stat_cd is NULL
and p_effective_date between pl.effective_start_date and pl.effective_end_date
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot
and pen.person_id = p_person_id
and pil.person_id = p_person_id
and pil.per_in_ler_id = p_per_in_ler_id
and pl.subj_to_imptd_incm_typ_cd = p_imptd_incm_typ_cd
order by 2;
select ler.typ_cd
from ben_per_in_ler pil , ben_ler_f ler
where per_in_ler_id = p_per_in_ler_id
and ler.ler_id = pil.ler_id
and lf_evt_ocrd_dt between ler.effective_start_date and ler.effective_end_date;
select pen.prtt_enrt_rslt_id,
pen.object_version_number,
pen.pl_id,
pen.oipl_id,
pen.pgm_id,
pen.effective_start_date,
pen.pl_typ_id,
pen.enrt_cvg_strt_dt
from ben_pl_f pln,
ben_prtt_enrt_rslt_f pen
where pln.imptd_incm_calc_cd = c_imp_inc_cd -- bug 12354818
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 pen.prtt_enrt_rslt_id,
pen.enrt_cvg_strt_Dt,
pl.subj_to_imptd_incm_typ_cd
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil,
ben_pl_f pl
where pen.pl_id = pl.pl_id
and pen.sspndd_flag = 'N'
and pen.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pen.person_id = pil.person_id
and pen.prtt_enrt_rslt_stat_cd is NULL
and p_effective_date between pl.effective_start_date and pl.effective_end_date
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.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
and pen.person_id = p_person_id
-- and pil.per_in_ler_id = p_per_in_ler_id
and subj_to_imptd_incm_typ_cd = c_imp_inc_cd ; /* bug 12354818 */
delete_past_imp(p_person_id => p_person_id,
p_per_in_ler_id => p_per_in_ler_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date,
p_erlst_cvg_strt => l_erlst_cvg_strt.enrt_cvg_strt_dt,
p_imptd_incm_calc_cd => 'PRTT');
delete_past_imp(p_person_id => p_person_id,
p_per_in_ler_id => p_per_in_ler_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date,
p_erlst_cvg_strt => l_erlst_cvg_strt.enrt_cvg_strt_dt,
p_imptd_incm_calc_cd => 'DPNT');
delete_past_imp(p_person_id => p_person_id,
p_per_in_ler_id => p_per_in_ler_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date,
p_erlst_cvg_strt => l_erlst_cvg_strt.enrt_cvg_strt_dt,
p_imptd_incm_calc_cd => 'SPS');
ben_prtt_enrt_result_api.delete_enrollment
(p_prtt_enrt_rslt_id => l_imp_shell_enrt.prtt_enrt_rslt_id,
p_per_in_ler_id => p_per_in_ler_id,
p_object_version_number => l_imp_shell_enrt.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);
ben_prtt_enrt_result_api.delete_enrollment
(p_prtt_enrt_rslt_id => l_imp_shell_enrt.prtt_enrt_rslt_id,
p_per_in_ler_id => p_per_in_ler_id,
p_object_version_number => l_imp_shell_enrt.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);
ben_prtt_enrt_result_api.delete_enrollment
(p_prtt_enrt_rslt_id => l_imp_shell_enrt.prtt_enrt_rslt_id,
p_per_in_ler_id => p_per_in_ler_id,
p_object_version_number => l_imp_shell_enrt.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);
select 'Y'
from ben_prtt_enrt_rslt_f pen, ben_pl_f pl
where pen.person_id = p_person_id
and pen.comp_lvl_cd = 'PLANIMP'
and pen.effective_end_date = hr_api.g_eot
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.pl_id = pl.pl_id
and pl.imptd_incm_calc_cd = p_imptd_incm_calc_cd
and pl.pl_stat_cd = 'A'
and pen.enrt_cvg_strt_dt between pl.effective_start_date and pl.effective_end_date
and not exists (select 1
from ben_elig_per_elctbl_chc epe
where epe.pl_id = pen.pl_id
and epe.pgm_id = pen.pgm_id
and epe.per_in_ler_id = p_per_in_ler_id);