[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_enrt_rslt_tab.delete;
g_enrt_rt_tab.delete;
select pen.prtt_enrt_rslt_id,
pen.pl_id,
pen.pgm_id,
pen.oipl_id,
pen.enrt_cvg_strt_dt,
pen.comp_lvl_cd
from ben_prtt_enrt_rslt_f pen
where pen.person_id = c_person_id
--
-- Bug 6445880
-- Changed enrt_mthd_cd checks, to allow Default Enrollment records to be picked
-- up when Benefit elections are made using spreadsheet from Configuration
-- workbench in which case c_enrt_mthd_cd value will be 'E'
--
-- and ( pen.enrt_mthd_cd = c_enrt_mthd_cd
-- or pen.enrt_mthd_cd = 'O' ) -- Bug 2200139 Override Enhancements
and (( pen.enrt_mthd_cd = c_enrt_mthd_cd or pen.enrt_mthd_cd = 'O' )
or (pen.enrt_mthd_cd <> c_enrt_mthd_cd
and c_enrt_mthd_cd = 'E'
and (pen.enrt_mthd_cd = 'D'
or pen.enrt_mthd_cd = 'A')))
and pen.prtt_enrt_rslt_stat_cd is null
and pen.per_in_ler_id = c_per_in_ler_id
and enrt_cvg_thru_dt = hr_api.g_eot
and pen.comp_lvl_cd <> 'PLANIMP'
and c_effective_date
between pen.effective_start_date and pen.effective_end_date
and pen.effective_end_date = hr_api.g_eot
and -- start 4354929
( EXISTS ( select null
from ben_ler_f ler,
ben_elig_per_elctbl_chc epe
where ler.ler_id = pen.ler_id
and (( ler.typ_cd = 'SCHEDDU'
and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
and epe.per_in_ler_id = c_per_in_ler_id )
or
( ler.typ_cd <> 'SCHEDDU'
and epe.per_in_ler_id = c_per_in_ler_id)
)
)--exists
) -- end 4354929
order by pen.rplcs_sspndd_rslt_id;
select distinct pen.pgm_id
from ben_prtt_enrt_rslt_f pen
where pen.person_id = c_person_id
and ( pen.enrt_mthd_cd = c_enrt_mthd_cd
or pen.enrt_mthd_cd = 'O' ) -- Bug 2200139 Override Enhancements
and pen.prtt_enrt_rslt_stat_cd is null
and pen.per_in_ler_id = c_per_in_ler_id
and enrt_cvg_thru_dt = hr_api.g_eot
and pen.comp_lvl_cd <> 'PLANIMP'
and pen.pgm_id is not null
and c_effective_date
between pen.effective_start_date and pen.effective_end_date
and pen.effective_end_date = hr_api.g_eot;
select '1'
from ben_prtt_enrt_rslt_f pen
where pen.person_id = p_person_id
and pen.business_group_id = p_business_group_id
and (pen.pl_id = v_pl_id or
pen.oipl_id = v_oipl_id)
and ( pen.enrt_mthd_cd = p_enrt_mthd_cd -- Bug 2200139 for Override
or pen.enrt_mthd_cd = 'O' )
and pen.prtt_enrt_rslt_stat_cd is null
and pen.sspndd_flag = 'N'
and enrt_cvg_thru_dt = hr_api.g_eot
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
and pen.effective_end_date = hr_api.g_eot;
select epe.pl_id,
epe.oipl_id,
epe.elig_per_elctbl_chc_id,
epe.spcl_rt_pl_id,
epe.spcl_rt_oipl_id,
epe.fonm_cvg_strt_dt,
pel.acty_ref_perd_cd
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil,
ben_pil_elctbl_chc_popl pel,
ben_prtt_enrt_rslt_f pen
where epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and pil.per_in_ler_id=epe.per_in_ler_id
and pil.per_in_ler_id = p_per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pen.prtt_enrt_rslt_id=v_enrt_rslt_id
and nvl(pen.pgm_id,-1)=nvl(epe.pgm_id,-1)
and pen.pl_id=epe.pl_id
and nvl(pen.oipl_id,-1)=nvl(epe.oipl_id,-1)
-- added bnft prvdr pool id to fetch the electable choice related to the comp.object
-- and prevent the one meant for flex credit - Bug#2177187- If flex credit is defined
-- on combination plan type and option, the cursor returns two rows without prvdr pool
-- id join
and epe.bnft_prvdr_pool_id is null
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
;
select ecr.prtt_rt_val_id,
ecr.enrt_rt_id,
ecr.val,
ecr.ann_val,
ecr.rt_mlt_cd,
ecr.acty_typ_cd,
ecr.rt_strt_dt,
ecr.acty_base_rt_id,
to_char(null) cvg_mlt_cd
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
and ecr.SPCL_RT_ENRT_RT_ID is null
and ecr.entr_val_at_enrt_flag = 'N'
and nvl(ecr.rt_strt_dt_cd,'AED') <> 'ENTRBL' --Bug 3053267
and ecr.asn_on_enrt_flag = 'Y'
and ecr.rt_mlt_cd <> 'ERL' -- added for canon fix
UNION
select ecr.prtt_rt_val_id,
ecr.enrt_rt_id,
ecr.val,
ecr.ann_val,
ecr.rt_mlt_cd,
ecr.acty_typ_cd,
ecr.rt_strt_dt,
ecr.acty_base_rt_id,
enb.cvg_mlt_cd cvg_mlt_cd
from ben_enrt_bnft enb,
ben_enrt_rt ecr
where enb.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
and enb.ENRT_BNFT_ID = ecr.ENRT_BNFT_ID
and enb.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and ecr.SPCL_RT_ENRT_RT_ID is null
and ecr.entr_val_at_enrt_flag = 'N'
and nvl(ecr.rt_strt_dt_cd,'AED') <> 'ENTRBL' --Bug 3053267
and ecr.asn_on_enrt_flag = 'Y'
and ecr.rt_mlt_cd <> 'ERL' ; -- added for canon fix
select ecr.prtt_rt_val_id,
ecr.enrt_rt_id,
ecr.val,
ecr.ann_val,
ecr.rt_mlt_cd,
ecr.acty_typ_cd,
ecr.rt_strt_dt,
ecr.acty_base_rt_id,
to_char(null) cvg_mlt_cd
from ben_enrt_rt ecr
where ecr.spcl_rt_enrt_rt_id = v_enrt_rt_id
and ecr.entr_val_at_enrt_flag = 'N'
and ecr.asn_on_enrt_flag = 'Y'
and ecr.business_group_id = p_business_group_id;
select prv.prtt_rt_val_id
from ben_prtt_rt_val prv
where prv.prtt_rt_val_id = l_use_enrt_rec.prtt_rt_val_id
and prv.per_in_ler_id = p_per_in_ler_id
and prv.prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
and prv.mlt_cd = 'FLFX'
and prv.prtt_rt_val_stat_cd is null
and prv.rt_strt_dt <= prv.rt_end_dt;
select prv.prtt_rt_val_id
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and acty_base_rt_id = p_acty_base_rt_id
and prtt_rt_val_stat_cd is null;
select 'Y'
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id = p_per_in_ler_id
and pil.ler_id = ler.ler_id
and ler.typ_cd = 'SCHEDDU'
and ler.business_group_id = p_business_group_id
and p_effective_date between ler.effective_start_date
and ler.effective_end_date;
select decode(enb.enrt_bnft_id,
null, ecr2.enrt_rt_id,
ecr1.enrt_rt_id) enrt_rt_id,
decode(enb.enrt_bnft_id,
null, ecr2.rt_mlt_cd,
ecr1.rt_mlt_cd) rt_mlt_cd,
decode(enb.enrt_bnft_id,
null, ecr2.entr_val_at_enrt_flag,
ecr1.entr_val_at_enrt_flag) entr_val_at_enrt_flag, --bug 5608160
enb.enrt_bnft_id,
nvl(enb.val, enb.dflt_val) bnft_val,
epe.elig_per_elctbl_chc_id,
pel.acty_ref_perd_cd,
pen.prtt_enrt_rslt_id,
pen.bnft_amt,
pen.object_version_number,
pen.pgm_id,
pen.pl_id,
pen.oipl_id
from ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_enrt_rt ecr1,
ben_enrt_rt ecr2,
ben_enrt_bnft enb,
ben_prtt_enrt_rslt_f pen,
ben_bnft_prvdr_pool_f bpp -- join to get only current pgm_id - rgajula
where
pil.per_in_ler_id=p_per_in_ler_id and
pil.business_group_id=p_business_group_id and
pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
pil.per_in_ler_id=epe.per_in_ler_id and
pil.per_in_ler_id = pel.per_in_ler_id and
pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id and
epe.business_group_id=p_business_group_id and
epe.elig_per_elctbl_chc_id=ecr2.elig_per_elctbl_chc_id(+) and
bpp.bnft_prvdr_pool_id in (select bnft_prvdr_pool_id from ben_bnft_pool_rlovr_rqmt_f
where business_group_id=p_business_group_id
and p_effective_date between effective_start_date and effective_end_date) and
bpp.business_group_id = p_business_group_id and --
p_effective_date between bpp.effective_start_date and bpp.effective_end_date and --
bpp.pgm_id = epe.pgm_id and --
(ecr1.acty_base_rt_id in (select acty_base_rt_id from ben_bnft_pool_rlovr_rqmt_f
where business_group_id=p_business_group_id
and p_effective_date between effective_start_date and effective_end_date) or
ecr2.acty_base_rt_id in (select acty_base_rt_id from ben_bnft_pool_rlovr_rqmt_f
where business_group_id=p_business_group_id
and p_effective_date between effective_start_date and effective_end_date)) and
pen.prtt_enrt_rslt_id(+)=epe.prtt_enrt_rslt_id and
epe.elig_per_elctbl_chc_id=enb.elig_per_elctbl_chc_id(+) and
enb.enrt_bnft_id = ecr1.enrt_bnft_id(+) and
pen.prtt_enrt_rslt_stat_cd is null and
p_effective_date between
pen.effective_start_date(+) and pen.effective_end_date(+) and
pen.business_group_id(+)=p_business_group_id ;
select pen.prtt_enrt_rslt_id,
pen.pl_id,
pen.pgm_id,
pen.oipl_id,
pen.enrt_cvg_strt_dt,
pen.comp_lvl_cd
from ben_prtt_enrt_rslt_f pen
where pen.person_id = c_person_id
and pen.enrt_mthd_cd = c_enrt_mthd_cd
and pen.prtt_enrt_rslt_stat_cd is null
and pen.per_in_ler_id = c_per_in_ler_id
and enrt_cvg_thru_dt = hr_api.g_eot
and pen.comp_lvl_cd not in ('PLANIMP','PLANFC')
and c_effective_date
between pen.effective_start_date and pen.effective_end_date
and pen.effective_end_date = hr_api.g_eot
and -- start 4354929
( EXISTS ( select null
from ben_ler_f ler,
ben_elig_per_elctbl_chc epe
where ler.ler_id = pen.ler_id
and (
( ler.typ_cd = 'SCHEDDU'
and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
and epe.per_in_ler_id = c_per_in_ler_id )
or
( ler.typ_cd <> 'SCHEDDU' and epe.per_in_ler_id = c_per_in_ler_id )
)--inner and
)--exists
) -- end 4354929
order by pen.pgm_id;
select prv.prtt_rt_val_id,
prv.acty_base_rt_id,
prv.rt_strt_dt,
prv.per_in_ler_id,
prv.object_version_number
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.rt_end_dt = hr_api.g_eot
and prv.prtt_rt_val_stat_cd is null;
select epe.pl_id,
epe.oipl_id,
epe.elig_per_elctbl_chc_id,
epe.spcl_rt_pl_id,
epe.spcl_rt_oipl_id,
pel.acty_ref_perd_cd
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_prtt_enrt_rslt_f pen
where epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and epe.per_in_ler_id = p_per_in_ler_id
and pen.prtt_enrt_rslt_id=v_enrt_rslt_id
and nvl(pen.pgm_id,-1)=nvl(epe.pgm_id,-1)
and pen.pl_id=epe.pl_id
and nvl(pen.oipl_id,-1)=nvl(epe.oipl_id,-1)
and pen.prtt_enrt_rslt_stat_cd is null
and epe.bnft_prvdr_pool_id is null
and p_effective_date between
pen.effective_start_date and pen.effective_end_date;
select DECR_BNFT_PRVDR_POOL_ID
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
and ecr.asn_on_enrt_flag = 'Y'
-- and ecr.prtt_rt_val_id = c_prtt_rt_val_id
and ecr.acty_base_rt_id = c_acty_base_rt_id
UNION
select DECR_BNFT_PRVDR_POOL_ID
from ben_enrt_bnft enb,
ben_enrt_rt ecr
where enb.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
and enb.ENRT_BNFT_ID = ecr.ENRT_BNFT_ID
--and ecr.prtt_rt_val_id = c_prtt_rt_val_id
and ecr.asn_on_enrt_flag = 'Y'
and ecr.acty_base_rt_id = c_acty_base_rt_id;
select prtt_enrt_rslt_id
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.comp_lvl_cd = 'PLANFC'
and epe.pgm_id = p_pgm_id
and epe.business_group_id = p_business_group_id;
select bpl.bnft_prvdd_ldgr_id,
bpl.object_version_number
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where bpl.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and bpl.acty_base_rt_id = p_acty_base_rt_id
and bpl.used_val is not null
and bpl.PRTT_RO_OF_UNUSD_AMT_FLAG = 'N'
--and bpl.per_in_ler_id = p_per_in_ler_id
and bpl.effective_end_date = hr_api.g_eot
and bpl.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and p_effective_date between
bpl.effective_start_date and bpl.effective_end_date;
select 'Y'
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id = p_per_in_ler_id
and pil.ler_id = ler.ler_id
and ler.typ_cd = 'SCHEDDU'
and ler.business_group_id = p_business_group_id
and p_effective_date between ler.effective_start_date
and ler.effective_end_date;
ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
p_bnft_prvdd_ldgr_id => l_ldgr_exist.bnft_prvdd_ldgr_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_ldgr_exist.object_version_number,
p_effective_date => (p_effective_date - 1),
p_datetrack_mode => hr_api.g_delete,
p_business_group_id => p_business_group_id
);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_prtt_rt_val_id => l_prtt_rt_val.prtt_rt_val_id
,p_rt_end_dt => l_rt_end_dt
,p_ended_per_in_ler_id => p_per_in_ler_id
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_prtt_rt_val.object_version_number
,p_effective_date => p_effective_date
);
select prv.prtt_rt_val_id,
prv.acty_base_rt_id,
prv.rt_strt_dt,
prv.per_in_ler_id,
prv.object_version_number
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.rt_end_dt = hr_api.g_eot
and prv.prtt_rt_val_stat_cd is null;
select epe.pl_id,
epe.oipl_id,
epe.elig_per_elctbl_chc_id,
epe.spcl_rt_pl_id,
epe.spcl_rt_oipl_id,
pel.acty_ref_perd_cd
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_prtt_enrt_rslt_f pen
where epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and epe.per_in_ler_id = p_per_in_ler_id
and pen.prtt_enrt_rslt_id=v_enrt_rslt_id
and nvl(pen.pgm_id,-1)=nvl(epe.pgm_id,-1)
and pen.pl_id=epe.pl_id
and nvl(pen.oipl_id,-1)=nvl(epe.oipl_id,-1)
and pen.prtt_enrt_rslt_stat_cd is null
and epe.bnft_prvdr_pool_id is null
and p_effective_date between
pen.effective_start_date and pen.effective_end_date;
select DECR_BNFT_PRVDR_POOL_ID
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
and ecr.asn_on_enrt_flag = 'Y'
and ecr.acty_base_rt_id = c_acty_base_rt_id
UNION
select DECR_BNFT_PRVDR_POOL_ID
from ben_enrt_bnft enb,
ben_enrt_rt ecr
where enb.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
and enb.ENRT_BNFT_ID = ecr.ENRT_BNFT_ID
and ecr.acty_base_rt_id = c_acty_base_rt_id
and ecr.asn_on_enrt_flag = 'Y';
select pen.pgm_id,
pen.enrt_mthd_cd
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null;
select prtt_enrt_rslt_id
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.comp_lvl_cd = 'PLANFC'
and epe.pgm_id = p_pgm_id
and epe.business_group_id = p_business_group_id;
select bpl.bnft_prvdd_ldgr_id,
bpl.object_version_number
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where bpl.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and bpl.acty_base_rt_id = p_acty_base_rt_id
and bpl.used_val is not null
and bpl.PRTT_RO_OF_UNUSD_AMT_FLAG = 'N'
--and bpl.per_in_ler_id = p_per_in_ler_id
and bpl.effective_end_date = hr_api.g_eot
and bpl.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and p_effective_date between
bpl.effective_start_date and bpl.effective_end_date;
select 'Y'
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id = p_per_in_ler_id
and pil.ler_id = ler.ler_id
and ler.typ_cd = 'SCHEDDU'
and ler.business_group_id = p_business_group_id
and p_effective_date between ler.effective_start_date
and ler.effective_end_date;
ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
p_bnft_prvdd_ldgr_id => l_ldgr_exist.bnft_prvdd_ldgr_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_ldgr_exist.object_version_number,
p_effective_date => (p_effective_date - 1),
p_datetrack_mode => hr_api.g_delete,
p_business_group_id => p_business_group_id
);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_prtt_rt_val_id => l_prtt_rt_val.prtt_rt_val_id
,p_rt_end_dt => l_rt_end_dt
,p_ended_per_in_ler_id => p_per_in_ler_id
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_prtt_rt_val.object_version_number
,p_effective_date => p_effective_date
);
select ecr.prtt_rt_val_id,
ecr.enrt_rt_id,
ecr.val,
ecr.ann_val,
ecr.rt_mlt_cd,
ecr.acty_typ_cd,
ecr.rt_strt_dt,
ecr.acty_base_rt_id
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
and ecr.SPCL_RT_ENRT_RT_ID is null
and ecr.entr_val_at_enrt_flag = 'N'
and ecr.asn_on_enrt_flag = 'Y'
and ecr.rt_mlt_cd = 'ERL'
UNION
select ecr.prtt_rt_val_id,
ecr.enrt_rt_id,
ecr.val,
ecr.ann_val,
ecr.rt_mlt_cd,
ecr.acty_typ_cd,
ecr.rt_strt_dt,
ecr.acty_base_rt_id
from ben_enrt_bnft enb,
ben_enrt_rt ecr
where enb.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
and enb.ENRT_BNFT_ID = ecr.ENRT_BNFT_ID
and enb.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and ecr.SPCL_RT_ENRT_RT_ID is null
and ecr.entr_val_at_enrt_flag = 'N'
and ecr.asn_on_enrt_flag = 'Y'
and ecr.rt_mlt_cd = 'ERL';