[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_program_update_date in date
,p_ws_mgr_id in number
,p_assignment_id in number
--
,p_pil_elctbl_chc_popl_id out nocopy number
,p_oiplip_id out nocopy number
)
is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) ;
l_update_pel boolean;
select
pil_elctbl_chc_popl_id,
object_version_number,
dflt_enrt_dt,
cls_enrt_dt_to_use_cd,
enrt_typ_cycl_cd,
enrt_perd_strt_dt,
enrt_perd_end_dt,
lee_rsn_id,
enrt_perd_id,
procg_end_dt,
uom,
acty_ref_perd_cd
from ben_pil_elctbl_chc_popl
where
per_in_ler_id=p_per_in_ler_id and
pgm_id=l_pel_pgm_id
;
select
pil_elctbl_chc_popl_id,
object_version_number,
dflt_enrt_dt,
cls_enrt_dt_to_use_cd,
enrt_typ_cycl_cd,
enrt_perd_strt_dt,
enrt_perd_end_dt,
lee_rsn_id,
enrt_perd_id,
procg_end_dt,
uom,
acty_ref_perd_cd
from ben_pil_elctbl_chc_popl
where
per_in_ler_id=p_per_in_ler_id and
pl_id=l_pel_pl_id
;
select oiplip_id
from ben_oiplip_f oi
where oi.oipl_id = p_oipl_id
and oi.plip_id = p_plip_id
and p_effective_date between
oi.effective_start_date and oi.effective_end_date;
select yrp.end_date
from ben_yr_perd yrp
where yrp.yr_perd_id = p_yr_perd_id
and yrp.business_group_id = p_business_group_id;
select enp.DFLT_WS_ACC_CD ,enp.ler_id,
enp.WS_UPD_END_DT, enp.BDGT_UPD_END_DT,
enp.auto_distr_flag,
enp.reinstate_cd,
enp.reinstate_ovrdn_cd,
enp.reopen_le_on_reprocess
from ben_enrt_perd enp
where enrt_perd_id = p_enrt_perd_id;
select len.reinstate_cd,
len.reinstate_ovrdn_cd,
len.reopen_le_on_reprocess
from ben_lee_rsn_f len
where len.lee_rsn_id = c_lee_rsn_id
and c_effective_date between len.effective_start_date
and len.effective_end_date ;
SELECT 'Y'
FROM ben_acty_base_rt_f abr
WHERE abr.acty_base_rt_stat_cd = 'A'
AND abr.pl_id = c_pl_id
AND abr.rt_usg_cd <> 'FLXCR'
and abr.acty_typ_cd in ('CWBWB','CWBDB')
AND c_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date;
SELECT 'Y'
FROM ben_acty_base_rt_f abr
WHERE abr.acty_base_rt_stat_cd = 'A'
AND abr.oipl_id = c_oipl_id
AND abr.rt_usg_cd <> 'FLXCR'
and abr.acty_typ_cd in ('CWBWB','CWBDB')
AND c_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date;
select opt.oipl_id
from ben_oipl_f opt
where pl_id = p_pl_id
and p_effective_date between opt.effective_start_date
and opt.effective_end_date;
,p_program_update_date => p_program_update_date
,p_object_version_number => l_pel_ovn
,p_effective_date => p_effective_date
,p_pl_id => l_pl_id
,p_pgm_id => l_pgm_id
,p_uom => l_uom
,p_acty_ref_perd_cd => l_acty_ref_perd_cd
-- Bug 2174005
,p_ws_due_dt => l_ws_upd_end_dt
,p_bdgt_due_dt => l_bdgt_upd_end_dt
,p_bdgt_acc_cd => l_bdgt_acc_cd
,p_ws_acc_cd => l_ws_acc_cd
,p_bdgt_stat_cd => l_bdgt_stat_cd
,p_ws_stat_cd => l_ws_stat_cd
,p_pop_cd => l_pop_cd
,p_bdgt_iss_dt => l_bdgt_iss_dt
,p_ws_iss_dt => l_ws_iss_dt
,p_reinstate_cd => l_reinstate_cd
,p_reopen_le_on_reprocess => l_reopen_le_on_reprocess
,p_reinstate_ovrdn_cd => l_reinstate_ovrdn_cd
);
update ben_Pil_Elctbl_chc_Popl
set ws_mgr_id = p_ws_mgr_id,
assignment_id = p_assignment_id
where Pil_Elctbl_chc_Popl_id = l_pil_elctbl_chc_popl_id;
l_update_pel:=false;
l_update_pel:=true;
l_update_pel:=true;
l_update_pel:=true;
l_update_pel:=true;
l_update_pel:=true;
l_update_pel:=true;
l_update_pel:=true;
l_update_pel:=true;
l_update_pel:=true;
l_update_pel:=true;
if l_update_pel then
if g_debug then
hr_utility.set_location('BPECPAPI_UPD: '|| l_proc, 10);
ben_Pil_Elctbl_chc_Popl_api.update_Pil_Elctbl_chc_Popl
(p_validate => FALSE
,p_pil_elctbl_chc_popl_id => l_pil_elctbl_chc_popl_id
,p_object_version_number => l_pel_ovn
,p_dflt_enrt_dt => l_pel_dflt_enrt_dt
,p_cls_enrt_dt_to_use_cd => l_pel_cls_enrt_dt_to_use_cd
,p_enrt_typ_cycl_cd => l_pel_enrt_typ_cycl_cd
,p_enrt_perd_end_dt => l_pel_enrt_perd_end_dt
,p_enrt_perd_strt_dt => l_pel_enrt_perd_strt_dt
,p_procg_end_dt => l_pel_procg_end_dt
,p_lee_rsn_id => l_pel_lee_rsn_id
,p_enrt_perd_id => l_pel_enrt_perd_id
,p_effective_date => p_effective_date
,p_uom => l_pel_uom
,p_acty_ref_perd_cd => l_pel_acty_ref_perd_cd
);
,p_program_update_date in date default null
,p_object_version_number out nocopy number
,p_effective_date in date
,p_enrt_perd_id in number default null
,p_lee_rsn_id in number default null
,p_cls_enrt_dt_to_use_cd in varchar2 default null
,p_uom in varchar2 default null
,p_acty_ref_perd_cd in varchar2 default null
)
is
--
l_proc varchar2(72) ;
,p_program_update_date => p_program_update_date
,p_ws_mgr_id => p_ws_mgr_id
,p_assignment_id => p_assignment_id
--
,p_pil_elctbl_chc_popl_id => l_pil_elctbl_chc_popl_id
,p_oiplip_id => l_oiplip_id
);
,p_program_update_date => p_program_update_date
,p_effective_date => trunc(p_effective_date)
);
,p_program_update_date => p_program_update_date
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
);
,p_program_update_date => p_program_update_date
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
);
,p_program_update_date in date default null
,p_object_version_number out nocopy number
,p_effective_date in date
,p_enrt_perd_id in number default null
,p_lee_rsn_id in number default null
,p_cls_enrt_dt_to_use_cd in varchar2 default null
,p_uom in varchar2 default null
,p_acty_ref_perd_cd in varchar2 default null
-- CWB changes.
,p_mode in varchar2 default null
)
is
--
l_proc varchar2(72) ;
l_last_update_date ben_elig_per_elctbl_chc.last_update_date%TYPE;
l_last_updated_by ben_elig_per_elctbl_chc.last_updated_by%TYPE;
l_last_update_login ben_elig_per_elctbl_chc.last_update_login%TYPE;
,p_program_update_date => p_program_update_date
,p_ws_mgr_id => p_ws_mgr_id
,p_assignment_id => p_assignment_id
--
,p_pil_elctbl_chc_popl_id => l_pil_elctbl_chc_popl_id
,p_oiplip_id => l_oiplip_id
);
hr_utility.set_location('Insert EPE: '||l_proc, 5);
insert into ben_elig_per_elctbl_chc
( elig_per_elctbl_chc_id,
-- enrt_typ_cycl_cd,
enrt_cvg_strt_dt_cd,
-- enrt_perd_end_dt,
-- enrt_perd_strt_dt,
enrt_cvg_strt_dt_rl,
-- rt_strt_dt,
-- rt_strt_dt_rl,
-- rt_strt_dt_cd,
ctfn_rqd_flag,
pil_elctbl_chc_popl_id,
roll_crs_flag,
crntly_enrd_flag,
dflt_flag,
elctbl_flag,
mndtry_flag,
in_pndg_wkflow_flag,
-- dflt_enrt_dt,
dpnt_cvg_strt_dt_cd,
dpnt_cvg_strt_dt_rl,
enrt_cvg_strt_dt,
alws_dpnt_dsgn_flag,
dpnt_dsgn_cd,
ler_chg_dpnt_cvg_cd,
erlst_deenrt_dt,
procg_end_dt,
comp_lvl_cd,
pl_id,
oipl_id,
pgm_id,
plip_id,
ptip_id,
pl_typ_id,
oiplip_id,
cmbn_plip_id,
cmbn_ptip_id,
cmbn_ptip_opt_id,
assignment_id,
spcl_rt_pl_id,
spcl_rt_oipl_id,
must_enrl_anthr_pl_id,
interim_elig_per_elctbl_chc_id,
prtt_enrt_rslt_id,
bnft_prvdr_pool_id,
per_in_ler_id,
yr_perd_id,
auto_enrt_flag,
business_group_id,
pl_ordr_num,
plip_ordr_num,
ptip_ordr_num,
oipl_ordr_num,
-- cwb
comments,
elig_flag,
elig_ovrid_dt,
elig_ovrid_person_id,
inelig_rsn_cd,
mgr_ovrid_dt,
mgr_ovrid_person_id,
ws_mgr_id,
-- cwb
epe_attribute_category,
epe_attribute1,
epe_attribute2,
epe_attribute3,
epe_attribute4,
epe_attribute5,
epe_attribute6,
epe_attribute7,
epe_attribute8,
epe_attribute9,
epe_attribute10,
epe_attribute11,
epe_attribute12,
epe_attribute13,
epe_attribute14,
epe_attribute15,
epe_attribute16,
epe_attribute17,
epe_attribute18,
epe_attribute19,
epe_attribute20,
epe_attribute21,
epe_attribute22,
epe_attribute23,
epe_attribute24,
epe_attribute25,
epe_attribute26,
epe_attribute27,
epe_attribute28,
epe_attribute29,
epe_attribute30,
approval_status_cd,
fonm_cvg_strt_dt,
cryfwd_elig_dpnt_cd,
request_id,
program_application_id,
program_id,
program_update_date,
object_version_number
)
Values
(ben_elig_per_elctbl_chc_s.nextval,
-- p_enrt_typ_cycl_cd,
p_enrt_cvg_strt_dt_cd,
-- p_enrt_perd_end_dt,
-- p_enrt_perd_strt_dt,
p_enrt_cvg_strt_dt_rl,
-- p_rt_strt_dt,
-- p_rt_strt_dt_rl,
-- p_rt_strt_dt_cd,
p_ctfn_rqd_flag,
l_pil_elctbl_chc_popl_id,
p_roll_crs_flag,
p_crntly_enrd_flag,
p_dflt_flag,
p_elctbl_flag,
p_mndtry_flag,
p_in_pndg_wkflow_flag,
-- p_dflt_enrt_dt,
p_dpnt_cvg_strt_dt_cd,
p_dpnt_cvg_strt_dt_rl,
p_enrt_cvg_strt_dt,
p_alws_dpnt_dsgn_flag,
p_dpnt_dsgn_cd,
p_ler_chg_dpnt_cvg_cd,
p_erlst_deenrt_dt,
p_procg_end_dt,
p_comp_lvl_cd,
p_pl_id,
p_oipl_id,
p_pgm_id,
p_plip_id,
p_ptip_id,
p_pl_typ_id,
l_oiplip_id,
p_cmbn_plip_id,
p_cmbn_ptip_id,
p_cmbn_ptip_opt_id,
p_assignment_id,
p_spcl_rt_pl_id,
p_spcl_rt_oipl_id,
p_must_enrl_anthr_pl_id,
p_int_elig_per_elctbl_chc_id,
p_prtt_enrt_rslt_id,
p_bnft_prvdr_pool_id,
p_per_in_ler_id,
p_yr_perd_id,
p_auto_enrt_flag,
p_business_group_id,
p_pl_ordr_num,
p_plip_ordr_num,
p_ptip_ordr_num,
p_oipl_ordr_num,
-- cwb
p_comments,
l_elig_flag,
p_elig_ovrid_dt,
p_elig_ovrid_person_id,
l_inelig_rsn_cd,
p_mgr_ovrid_dt,
p_mgr_ovrid_person_id,
p_ws_mgr_id,
-- cwb
p_epe_attribute_category,
p_epe_attribute1,
p_epe_attribute2,
p_epe_attribute3,
p_epe_attribute4,
p_epe_attribute5,
p_epe_attribute6,
p_epe_attribute7,
p_epe_attribute8,
p_epe_attribute9,
p_epe_attribute10,
p_epe_attribute11,
p_epe_attribute12,
p_epe_attribute13,
p_epe_attribute14,
p_epe_attribute15,
p_epe_attribute16,
p_epe_attribute17,
p_epe_attribute18,
p_epe_attribute19,
p_epe_attribute20,
p_epe_attribute21,
p_epe_attribute22,
p_epe_attribute23,
p_epe_attribute24,
p_epe_attribute25,
p_epe_attribute26,
p_epe_attribute27,
p_epe_attribute28,
p_epe_attribute29,
p_epe_attribute30,
p_approval_status_cd,
p_fonm_cvg_strt_dt,
p_cryfwd_elig_dpnt_cd,
p_request_id,
p_program_application_id,
p_program_id,
p_program_update_date,
l_object_version_number
) RETURNING elig_per_elctbl_chc_id into l_elig_per_elctbl_chc_id;
hr_utility.set_location('Dn Insert: '||l_proc, 5);
procedure update_ELIG_PER_ELC_CHC
(p_validate in boolean default false
,p_elig_per_elctbl_chc_id in number
,p_enrt_typ_cycl_cd in varchar2 default hr_api.g_varchar2
,p_enrt_cvg_strt_dt_cd in varchar2 default hr_api.g_varchar2
,p_enrt_perd_end_dt in date default hr_api.g_date
,p_enrt_perd_strt_dt in date default hr_api.g_date
,p_enrt_cvg_strt_dt_rl in varchar2 default hr_api.g_varchar2
,p_ctfn_rqd_flag in varchar2 default hr_api.g_varchar2
,p_pil_elctbl_chc_popl_id in number default hr_api.g_number
,p_roll_crs_flag in varchar2 default hr_api.g_varchar2
,p_crntly_enrd_flag in varchar2 default hr_api.g_varchar2
,p_dflt_flag in varchar2 default hr_api.g_varchar2
,p_elctbl_flag in varchar2 default hr_api.g_varchar2
,p_mndtry_flag in varchar2 default hr_api.g_varchar2
,p_in_pndg_wkflow_flag in varchar2 default hr_api.g_varchar2
,p_dflt_enrt_dt in date default hr_api.g_date
,p_dpnt_cvg_strt_dt_cd in varchar2 default hr_api.g_varchar2
,p_dpnt_cvg_strt_dt_rl in varchar2 default hr_api.g_varchar2
,p_enrt_cvg_strt_dt in date default hr_api.g_date
,p_alws_dpnt_dsgn_flag in varchar2 default hr_api.g_varchar2
,p_dpnt_dsgn_cd in varchar2 default hr_api.g_varchar2
,p_ler_chg_dpnt_cvg_cd in varchar2 default hr_api.g_varchar2
,p_erlst_deenrt_dt in date default hr_api.g_date
,p_procg_end_dt in date default hr_api.g_date
,p_comp_lvl_cd in varchar2 default hr_api.g_varchar2
,p_pl_id in number default hr_api.g_number
,p_oipl_id in number default hr_api.g_number
,p_pgm_id in number default hr_api.g_number
,p_plip_id in number default hr_api.g_number
,p_ptip_id in number default hr_api.g_number
,p_pl_typ_id in number default hr_api.g_number
,p_oiplip_id in number default hr_api.g_number
,p_cmbn_plip_id in number default hr_api.g_number
,p_cmbn_ptip_id in number default hr_api.g_number
,p_cmbn_ptip_opt_id in number default hr_api.g_number
,p_assignment_id in number default hr_api.g_number
,p_spcl_rt_pl_id in number default hr_api.g_number
,p_spcl_rt_oipl_id in number default hr_api.g_number
,p_must_enrl_anthr_pl_id in number default hr_api.g_number
,p_int_elig_per_elctbl_chc_id in number default hr_api.g_number
,p_prtt_enrt_rslt_id in number default hr_api.g_number
,p_bnft_prvdr_pool_id in number default hr_api.g_number
,p_per_in_ler_id in number default hr_api.g_number
,p_yr_perd_id in number default hr_api.g_number
,p_auto_enrt_flag in varchar2 default hr_api.g_varchar2
,p_business_group_id in number default hr_api.g_number
,p_pl_ordr_num in number default hr_api.g_number
,p_plip_ordr_num in number default hr_api.g_number
,p_ptip_ordr_num in number default hr_api.g_number
,p_oipl_ordr_num in number default hr_api.g_number
-- cwb
,p_comments in varchar2 default hr_api.g_varchar2
,p_elig_flag in varchar2 default hr_api.g_varchar2
,p_elig_ovrid_dt in date default hr_api.g_date
,p_elig_ovrid_person_id in number default hr_api.g_number
,p_inelig_rsn_cd in varchar2 default hr_api.g_varchar2
,p_mgr_ovrid_dt in date default hr_api.g_date
,p_mgr_ovrid_person_id in number default hr_api.g_number
,p_ws_mgr_id in number default hr_api.g_number
-- cwb
,p_epe_attribute_category in varchar2 default hr_api.g_varchar2
,p_epe_attribute1 in varchar2 default hr_api.g_varchar2
,p_epe_attribute2 in varchar2 default hr_api.g_varchar2
,p_epe_attribute3 in varchar2 default hr_api.g_varchar2
,p_epe_attribute4 in varchar2 default hr_api.g_varchar2
,p_epe_attribute5 in varchar2 default hr_api.g_varchar2
,p_epe_attribute6 in varchar2 default hr_api.g_varchar2
,p_epe_attribute7 in varchar2 default hr_api.g_varchar2
,p_epe_attribute8 in varchar2 default hr_api.g_varchar2
,p_epe_attribute9 in varchar2 default hr_api.g_varchar2
,p_epe_attribute10 in varchar2 default hr_api.g_varchar2
,p_epe_attribute11 in varchar2 default hr_api.g_varchar2
,p_epe_attribute12 in varchar2 default hr_api.g_varchar2
,p_epe_attribute13 in varchar2 default hr_api.g_varchar2
,p_epe_attribute14 in varchar2 default hr_api.g_varchar2
,p_epe_attribute15 in varchar2 default hr_api.g_varchar2
,p_epe_attribute16 in varchar2 default hr_api.g_varchar2
,p_epe_attribute17 in varchar2 default hr_api.g_varchar2
,p_epe_attribute18 in varchar2 default hr_api.g_varchar2
,p_epe_attribute19 in varchar2 default hr_api.g_varchar2
,p_epe_attribute20 in varchar2 default hr_api.g_varchar2
,p_epe_attribute21 in varchar2 default hr_api.g_varchar2
,p_epe_attribute22 in varchar2 default hr_api.g_varchar2
,p_epe_attribute23 in varchar2 default hr_api.g_varchar2
,p_epe_attribute24 in varchar2 default hr_api.g_varchar2
,p_epe_attribute25 in varchar2 default hr_api.g_varchar2
,p_epe_attribute26 in varchar2 default hr_api.g_varchar2
,p_epe_attribute27 in varchar2 default hr_api.g_varchar2
,p_epe_attribute28 in varchar2 default hr_api.g_varchar2
,p_epe_attribute29 in varchar2 default hr_api.g_varchar2
,p_epe_attribute30 in varchar2 default hr_api.g_varchar2
,p_approval_status_cd in varchar2 default hr_api.g_varchar2
,p_fonm_cvg_strt_dt in date default hr_api.g_date
,p_cryfwd_elig_dpnt_cd in varchar2 default hr_api.g_varchar2
,p_request_id in number default hr_api.g_number
,p_program_application_id in number default hr_api.g_number
,p_program_id in number default hr_api.g_number
,p_program_update_date in date default hr_api.g_date
,p_object_version_number in out nocopy number
,p_effective_date in date
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) ;
l_proc := g_package||'update_ELIG_PER_ELC_CHC';
savepoint update_ELIG_PER_ELC_CHC;
ben_ELIG_PER_ELC_CHC_bk2.update_ELIG_PER_ELC_CHC_b
(
p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
,p_enrt_cvg_strt_dt_cd => p_enrt_cvg_strt_dt_cd
,p_enrt_cvg_strt_dt_rl => p_enrt_cvg_strt_dt_rl
,p_ctfn_rqd_flag => p_ctfn_rqd_flag
,p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id
,p_roll_crs_flag => p_roll_crs_flag
,p_crntly_enrd_flag => p_crntly_enrd_flag
,p_dflt_flag => p_dflt_flag
,p_elctbl_flag => p_elctbl_flag
,p_mndtry_flag => p_mndtry_flag
,p_in_pndg_wkflow_flag => p_in_pndg_wkflow_flag
,p_dpnt_cvg_strt_dt_cd => p_dpnt_cvg_strt_dt_cd
,p_dpnt_cvg_strt_dt_rl => p_dpnt_cvg_strt_dt_rl
,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
,p_alws_dpnt_dsgn_flag => p_alws_dpnt_dsgn_flag
,p_dpnt_dsgn_cd => p_dpnt_dsgn_cd
,p_ler_chg_dpnt_cvg_cd => p_ler_chg_dpnt_cvg_cd
,p_erlst_deenrt_dt => p_erlst_deenrt_dt
,p_procg_end_dt => p_procg_end_dt
,p_comp_lvl_cd => p_comp_lvl_cd
,p_pl_id => p_pl_id
,p_oipl_id => p_oipl_id
,p_pgm_id => p_pgm_id
,p_plip_id => p_plip_id
,p_ptip_id => p_ptip_id
,p_pl_typ_id => p_pl_typ_id
,p_oiplip_id => p_oiplip_id
,p_cmbn_plip_id => p_cmbn_plip_id
,p_cmbn_ptip_id => p_cmbn_ptip_id
,p_cmbn_ptip_opt_id => p_cmbn_ptip_opt_id
,p_assignment_id => p_assignment_id
,p_spcl_rt_pl_id => p_spcl_rt_pl_id
,p_spcl_rt_oipl_id => p_spcl_rt_oipl_id
,p_must_enrl_anthr_pl_id => p_must_enrl_anthr_pl_id
,p_int_elig_per_elctbl_chc_id => p_int_elig_per_elctbl_chc_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
,p_per_in_ler_id => p_per_in_ler_id
,p_yr_perd_id => p_yr_perd_id
,p_auto_enrt_flag => p_auto_enrt_flag
,p_business_group_id => p_business_group_id
,p_pl_ordr_num => p_pl_ordr_num
,p_plip_ordr_num => p_plip_ordr_num
,p_ptip_ordr_num => p_ptip_ordr_num
,p_oipl_ordr_num => p_oipl_ordr_num
--cwb
,p_comments => p_comments
,p_elig_flag => p_elig_flag
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_inelig_rsn_cd => p_inelig_rsn_cd
,p_mgr_ovrid_dt => p_mgr_ovrid_dt
,p_mgr_ovrid_person_id => p_mgr_ovrid_person_id
,p_ws_mgr_id => p_ws_mgr_id
--cwb
,p_epe_attribute_category => p_epe_attribute_category
,p_epe_attribute1 => p_epe_attribute1
,p_epe_attribute2 => p_epe_attribute2
,p_epe_attribute3 => p_epe_attribute3
,p_epe_attribute4 => p_epe_attribute4
,p_epe_attribute5 => p_epe_attribute5
,p_epe_attribute6 => p_epe_attribute6
,p_epe_attribute7 => p_epe_attribute7
,p_epe_attribute8 => p_epe_attribute8
,p_epe_attribute9 => p_epe_attribute9
,p_epe_attribute10 => p_epe_attribute10
,p_epe_attribute11 => p_epe_attribute11
,p_epe_attribute12 => p_epe_attribute12
,p_epe_attribute13 => p_epe_attribute13
,p_epe_attribute14 => p_epe_attribute14
,p_epe_attribute15 => p_epe_attribute15
,p_epe_attribute16 => p_epe_attribute16
,p_epe_attribute17 => p_epe_attribute17
,p_epe_attribute18 => p_epe_attribute18
,p_epe_attribute19 => p_epe_attribute19
,p_epe_attribute20 => p_epe_attribute20
,p_epe_attribute21 => p_epe_attribute21
,p_epe_attribute22 => p_epe_attribute22
,p_epe_attribute23 => p_epe_attribute23
,p_epe_attribute24 => p_epe_attribute24
,p_epe_attribute25 => p_epe_attribute25
,p_epe_attribute26 => p_epe_attribute26
,p_epe_attribute27 => p_epe_attribute27
,p_epe_attribute28 => p_epe_attribute28
,p_epe_attribute29 => p_epe_attribute29
,p_epe_attribute30 => p_epe_attribute30
,p_approval_status_cd => p_approval_status_cd
,p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt
,p_cryfwd_elig_dpnt_cd => p_cryfwd_elig_dpnt_cd
,p_request_id => p_request_id
,p_program_application_id => p_program_application_id
,p_program_id => p_program_id
,p_program_update_date => p_program_update_date
,p_object_version_number => p_object_version_number
,p_effective_date => trunc(p_effective_date)
);
(p_module_name => 'UPDATE_ELIG_PER_ELC_CHC'
,p_hook_type => 'BP'
);
,p_program_update_date => p_program_update_date
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
);
ben_ELIG_PER_ELC_CHC_bk2.update_ELIG_PER_ELC_CHC_a
(
p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
,p_enrt_cvg_strt_dt_cd => p_enrt_cvg_strt_dt_cd
,p_enrt_cvg_strt_dt_rl => p_enrt_cvg_strt_dt_rl
,p_ctfn_rqd_flag => p_ctfn_rqd_flag
,p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id
,p_roll_crs_flag => p_roll_crs_flag
,p_crntly_enrd_flag => p_crntly_enrd_flag
,p_dflt_flag => p_dflt_flag
,p_elctbl_flag => p_elctbl_flag
,p_mndtry_flag => p_mndtry_flag
,p_in_pndg_wkflow_flag => p_in_pndg_wkflow_flag
,p_dpnt_cvg_strt_dt_cd => p_dpnt_cvg_strt_dt_cd
,p_dpnt_cvg_strt_dt_rl => p_dpnt_cvg_strt_dt_rl
,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
,p_alws_dpnt_dsgn_flag => p_alws_dpnt_dsgn_flag
,p_dpnt_dsgn_cd => p_dpnt_dsgn_cd
,p_ler_chg_dpnt_cvg_cd => p_ler_chg_dpnt_cvg_cd
,p_erlst_deenrt_dt => p_erlst_deenrt_dt
,p_procg_end_dt => p_procg_end_dt
,p_comp_lvl_cd => p_comp_lvl_cd
,p_pl_id => p_pl_id
,p_oipl_id => p_oipl_id
,p_pgm_id => p_pgm_id
,p_plip_id => p_plip_id
,p_ptip_id => p_ptip_id
,p_pl_typ_id => p_pl_typ_id
,p_oiplip_id => p_oiplip_id
,p_cmbn_plip_id => p_cmbn_plip_id
,p_cmbn_ptip_id => p_cmbn_ptip_id
,p_cmbn_ptip_opt_id => p_cmbn_ptip_opt_id
,p_assignment_id => p_assignment_id
,p_spcl_rt_pl_id => p_spcl_rt_pl_id
,p_spcl_rt_oipl_id => p_spcl_rt_oipl_id
,p_must_enrl_anthr_pl_id => p_must_enrl_anthr_pl_id
,p_int_elig_per_elctbl_chc_id => p_int_elig_per_elctbl_chc_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
,p_per_in_ler_id => p_per_in_ler_id
,p_yr_perd_id => p_yr_perd_id
,p_auto_enrt_flag => p_auto_enrt_flag
,p_business_group_id => p_business_group_id
,p_pl_ordr_num => p_pl_ordr_num
,p_plip_ordr_num => p_plip_ordr_num
,p_ptip_ordr_num => p_ptip_ordr_num
,p_oipl_ordr_num => p_oipl_ordr_num
--cwb
,p_comments => p_comments
,p_elig_flag => p_elig_flag
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_inelig_rsn_cd => p_inelig_rsn_cd
,p_mgr_ovrid_dt => p_mgr_ovrid_dt
,p_mgr_ovrid_person_id => p_mgr_ovrid_person_id
,p_ws_mgr_id => p_ws_mgr_id
--cwb
,p_epe_attribute_category => p_epe_attribute_category
,p_epe_attribute1 => p_epe_attribute1
,p_epe_attribute2 => p_epe_attribute2
,p_epe_attribute3 => p_epe_attribute3
,p_epe_attribute4 => p_epe_attribute4
,p_epe_attribute5 => p_epe_attribute5
,p_epe_attribute6 => p_epe_attribute6
,p_epe_attribute7 => p_epe_attribute7
,p_epe_attribute8 => p_epe_attribute8
,p_epe_attribute9 => p_epe_attribute9
,p_epe_attribute10 => p_epe_attribute10
,p_epe_attribute11 => p_epe_attribute11
,p_epe_attribute12 => p_epe_attribute12
,p_epe_attribute13 => p_epe_attribute13
,p_epe_attribute14 => p_epe_attribute14
,p_epe_attribute15 => p_epe_attribute15
,p_epe_attribute16 => p_epe_attribute16
,p_epe_attribute17 => p_epe_attribute17
,p_epe_attribute18 => p_epe_attribute18
,p_epe_attribute19 => p_epe_attribute19
,p_epe_attribute20 => p_epe_attribute20
,p_epe_attribute21 => p_epe_attribute21
,p_epe_attribute22 => p_epe_attribute22
,p_epe_attribute23 => p_epe_attribute23
,p_epe_attribute24 => p_epe_attribute24
,p_epe_attribute25 => p_epe_attribute25
,p_epe_attribute26 => p_epe_attribute26
,p_epe_attribute27 => p_epe_attribute27
,p_epe_attribute28 => p_epe_attribute28
,p_epe_attribute29 => p_epe_attribute29
,p_epe_attribute30 => p_epe_attribute30
,p_approval_status_cd => p_approval_status_cd
,p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt
,p_cryfwd_elig_dpnt_cd => p_cryfwd_elig_dpnt_cd
,p_request_id => p_request_id
,p_program_application_id => p_program_application_id
,p_program_id => p_program_id
,p_program_update_date => p_program_update_date
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
);
(p_module_name => 'UPDATE_ELIG_PER_ELC_CHC'
,p_hook_type => 'AP'
);
ROLLBACK TO update_ELIG_PER_ELC_CHC;
ROLLBACK TO update_ELIG_PER_ELC_CHC;
end update_ELIG_PER_ELC_CHC;
select per1.full_name person1,
per2.full_name person2
from ben_cwb_mgr_hrchy cwb1,
ben_elig_per_elctbl_chc epe1,
ben_per_in_ler pil1,
per_all_people_f per1,
per_all_people_f per2
where cwb1.mgr_elig_per_elctbl_chc_id = p_emp_epe_id
and cwb1.lvl_num > 0
and epe1.elig_per_elctbl_chc_id = cwb1.mgr_elig_per_elctbl_chc_id
and pil1.per_in_ler_id = epe1.per_in_ler_id
and per1.person_id = pil1.person_id
and trunc(sysdate) between per1.effective_start_date
and per1.effective_end_date
and per2.person_id = p_new_mgr_id
and trunc(sysdate) between per2.effective_start_date
and per2.effective_end_date
and exists
( select 'x'
from ben_elig_per_elctbl_chc epe2,
ben_per_in_ler pil2
where pil2.person_id = p_new_mgr_id
and pil2.lf_evt_ocrd_dt = pil1.lf_evt_ocrd_dt
and pil2.ler_id = pil1.ler_id
and epe2.per_in_ler_id = pil2.per_in_ler_id
and epe2.pl_id = epe1.pl_id
and epe2.elig_per_elctbl_chc_id = cwb1.emp_elig_per_elctbl_chc_id);
procedure update_perf_ELIG_PER_ELC_CHC
(p_validate in boolean default false
,p_elig_per_elctbl_chc_id in number
-- ,p_enrt_typ_cycl_cd in varchar2 default hr_api.g_varchar2
,p_enrt_cvg_strt_dt_cd in varchar2 default hr_api.g_varchar2
--,p_enrt_perd_end_dt in date default hr_api.g_date
--,p_enrt_perd_strt_dt in date default hr_api.g_date
,p_enrt_cvg_strt_dt_rl in varchar2 default hr_api.g_varchar2
,p_ctfn_rqd_flag in varchar2 default hr_api.g_varchar2
,p_pil_elctbl_chc_popl_id in number default hr_api.g_number
,p_roll_crs_flag in varchar2 default hr_api.g_varchar2
,p_crntly_enrd_flag in varchar2 default hr_api.g_varchar2
,p_dflt_flag in varchar2 default hr_api.g_varchar2
,p_elctbl_flag in varchar2 default hr_api.g_varchar2
,p_mndtry_flag in varchar2 default hr_api.g_varchar2
,p_in_pndg_wkflow_flag in varchar2 default hr_api.g_varchar2
-- ,p_dflt_enrt_dt in date default hr_api.g_date
,p_dpnt_cvg_strt_dt_cd in varchar2 default hr_api.g_varchar2
,p_dpnt_cvg_strt_dt_rl in varchar2 default hr_api.g_varchar2
,p_enrt_cvg_strt_dt in date default hr_api.g_date
,p_alws_dpnt_dsgn_flag in varchar2 default hr_api.g_varchar2
,p_dpnt_dsgn_cd in varchar2 default hr_api.g_varchar2
,p_ler_chg_dpnt_cvg_cd in varchar2 default hr_api.g_varchar2
,p_erlst_deenrt_dt in date default hr_api.g_date
,p_procg_end_dt in date default hr_api.g_date
,p_comp_lvl_cd in varchar2 default hr_api.g_varchar2
,p_pl_id in number default hr_api.g_number
,p_oipl_id in number default hr_api.g_number
,p_pgm_id in number default hr_api.g_number
,p_plip_id in number default hr_api.g_number
,p_ptip_id in number default hr_api.g_number
,p_pl_typ_id in number default hr_api.g_number
,p_oiplip_id in number default hr_api.g_number
,p_cmbn_plip_id in number default hr_api.g_number
,p_cmbn_ptip_id in number default hr_api.g_number
,p_cmbn_ptip_opt_id in number default hr_api.g_number
,p_assignment_id in number default hr_api.g_number
,p_spcl_rt_pl_id in number default hr_api.g_number
,p_spcl_rt_oipl_id in number default hr_api.g_number
,p_must_enrl_anthr_pl_id in number default hr_api.g_number
,p_int_elig_per_elctbl_chc_id in number default hr_api.g_number
,p_prtt_enrt_rslt_id in number default hr_api.g_number
,p_bnft_prvdr_pool_id in number default hr_api.g_number
,p_per_in_ler_id in number default hr_api.g_number
,p_yr_perd_id in number default hr_api.g_number
,p_auto_enrt_flag in varchar2 default hr_api.g_varchar2
,p_business_group_id in number default hr_api.g_number
,p_pl_ordr_num in number default hr_api.g_number
,p_plip_ordr_num in number default hr_api.g_number
,p_ptip_ordr_num in number default hr_api.g_number
,p_oipl_ordr_num in number default hr_api.g_number
-- cwb
,p_comments in varchar2 default hr_api.g_varchar2
,p_elig_flag in varchar2 default hr_api.g_varchar2
,p_elig_ovrid_dt in date default hr_api.g_date
,p_elig_ovrid_person_id in number default hr_api.g_number
,p_inelig_rsn_cd in varchar2 default hr_api.g_varchar2
,p_mgr_ovrid_dt in date default hr_api.g_date
,p_mgr_ovrid_person_id in number default hr_api.g_number
,p_ws_mgr_id in number default hr_api.g_number
-- cwb
,p_epe_attribute_category in varchar2 default hr_api.g_varchar2
,p_epe_attribute1 in varchar2 default hr_api.g_varchar2
,p_epe_attribute2 in varchar2 default hr_api.g_varchar2
,p_epe_attribute3 in varchar2 default hr_api.g_varchar2
,p_epe_attribute4 in varchar2 default hr_api.g_varchar2
,p_epe_attribute5 in varchar2 default hr_api.g_varchar2
,p_epe_attribute6 in varchar2 default hr_api.g_varchar2
,p_epe_attribute7 in varchar2 default hr_api.g_varchar2
,p_epe_attribute8 in varchar2 default hr_api.g_varchar2
,p_epe_attribute9 in varchar2 default hr_api.g_varchar2
,p_epe_attribute10 in varchar2 default hr_api.g_varchar2
,p_epe_attribute11 in varchar2 default hr_api.g_varchar2
,p_epe_attribute12 in varchar2 default hr_api.g_varchar2
,p_epe_attribute13 in varchar2 default hr_api.g_varchar2
,p_epe_attribute14 in varchar2 default hr_api.g_varchar2
,p_epe_attribute15 in varchar2 default hr_api.g_varchar2
,p_epe_attribute16 in varchar2 default hr_api.g_varchar2
,p_epe_attribute17 in varchar2 default hr_api.g_varchar2
,p_epe_attribute18 in varchar2 default hr_api.g_varchar2
,p_epe_attribute19 in varchar2 default hr_api.g_varchar2
,p_epe_attribute20 in varchar2 default hr_api.g_varchar2
,p_epe_attribute21 in varchar2 default hr_api.g_varchar2
,p_epe_attribute22 in varchar2 default hr_api.g_varchar2
,p_epe_attribute23 in varchar2 default hr_api.g_varchar2
,p_epe_attribute24 in varchar2 default hr_api.g_varchar2
,p_epe_attribute25 in varchar2 default hr_api.g_varchar2
,p_epe_attribute26 in varchar2 default hr_api.g_varchar2
,p_epe_attribute27 in varchar2 default hr_api.g_varchar2
,p_epe_attribute28 in varchar2 default hr_api.g_varchar2
,p_epe_attribute29 in varchar2 default hr_api.g_varchar2
,p_epe_attribute30 in varchar2 default hr_api.g_varchar2
,p_approval_status_cd in varchar2 default hr_api.g_varchar2
,p_fonm_cvg_strt_dt in date default hr_api.g_date
,p_cryfwd_elig_dpnt_cd in varchar2 default hr_api.g_varchar2
,p_request_id in number default hr_api.g_number
,p_program_application_id in number default hr_api.g_number
,p_program_id in number default hr_api.g_number
,p_program_update_date in date default hr_api.g_date
,p_object_version_number in out nocopy number
,p_effective_date in date
)
is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) ;
l_proc := g_package||'update_perf_ELIG_PER_ELC_CHC';
savepoint update_perf_ELIG_PER_ELC_CHC;
p_program_update_date,
null
);
update ben_elig_per_elctbl_chc
set
elig_per_elctbl_chc_id = l_rec.elig_per_elctbl_chc_id,
-- enrt_typ_cycl_cd = l_rec.enrt_typ_cycl_cd,
enrt_cvg_strt_dt_cd = l_rec.enrt_cvg_strt_dt_cd,
-- enrt_perd_end_dt = l_rec.enrt_perd_end_dt,
-- enrt_perd_strt_dt = l_rec.enrt_perd_strt_dt,
enrt_cvg_strt_dt_rl = l_rec.enrt_cvg_strt_dt_rl,
-- rt_strt_dt = l_rec.rt_strt_dt,
-- rt_strt_dt_rl = l_rec.rt_strt_dt_rl,
-- rt_strt_dt_cd = l_rec.rt_strt_dt_cd,
ctfn_rqd_flag = l_rec.ctfn_rqd_flag,
pil_elctbl_chc_popl_id = l_rec.pil_elctbl_chc_popl_id,
roll_crs_flag = l_rec.roll_crs_flag,
crntly_enrd_flag = l_rec.crntly_enrd_flag,
dflt_flag = l_rec.dflt_flag,
elctbl_flag = l_rec.elctbl_flag,
mndtry_flag = l_rec.mndtry_flag,
in_pndg_wkflow_flag = l_rec.in_pndg_wkflow_flag,
-- dflt_enrt_dt = l_rec.dflt_enrt_dt,
dpnt_cvg_strt_dt_cd = l_rec.dpnt_cvg_strt_dt_cd,
dpnt_cvg_strt_dt_rl = l_rec.dpnt_cvg_strt_dt_rl,
enrt_cvg_strt_dt = l_rec.enrt_cvg_strt_dt,
alws_dpnt_dsgn_flag = l_rec.alws_dpnt_dsgn_flag,
dpnt_dsgn_cd = l_rec.dpnt_dsgn_cd,
ler_chg_dpnt_cvg_cd = l_rec.ler_chg_dpnt_cvg_cd,
erlst_deenrt_dt = l_rec.erlst_deenrt_dt,
procg_end_dt = l_rec.procg_end_dt,
comp_lvl_cd = l_rec.comp_lvl_cd,
pl_id = l_rec.pl_id,
oipl_id = l_rec.oipl_id,
pgm_id = l_rec.pgm_id,
plip_id = l_rec.plip_id,
ptip_id = l_rec.ptip_id,
pl_typ_id = l_rec.pl_typ_id,
oiplip_id = l_rec.oiplip_id,
cmbn_plip_id = l_rec.cmbn_plip_id,
cmbn_ptip_id = l_rec.cmbn_ptip_id,
cmbn_ptip_opt_id = l_rec.cmbn_ptip_opt_id,
spcl_rt_pl_id = l_rec.spcl_rt_pl_id,
spcl_rt_oipl_id = l_rec.spcl_rt_oipl_id,
must_enrl_anthr_pl_id = l_rec.must_enrl_anthr_pl_id,
interim_elig_per_elctbl_chc_id = l_rec.int_elig_per_elctbl_chc_id,
prtt_enrt_rslt_id = l_rec.prtt_enrt_rslt_id,
bnft_prvdr_pool_id = l_rec.bnft_prvdr_pool_id,
per_in_ler_id = l_rec.per_in_ler_id,
yr_perd_id = l_rec.yr_perd_id,
auto_enrt_flag = l_rec.auto_enrt_flag,
business_group_id = l_rec.business_group_id,
pl_ordr_num = l_rec.pl_ordr_num,
plip_ordr_num = l_rec.plip_ordr_num,
ptip_ordr_num = l_rec.ptip_ordr_num,
oipl_ordr_num = l_rec.oipl_ordr_num,
-- cwb
comments = l_rec.comments,
elig_flag = l_rec.elig_flag,
elig_ovrid_dt = l_rec.elig_ovrid_dt,
elig_ovrid_person_id = l_rec.elig_ovrid_person_id,
inelig_rsn_cd = l_rec.inelig_rsn_cd,
mgr_ovrid_dt = l_rec.mgr_ovrid_dt,
mgr_ovrid_person_id = l_rec.mgr_ovrid_person_id,
ws_mgr_id = l_rec.ws_mgr_id,
-- cwb
epe_attribute_category = l_rec.epe_attribute_category,
epe_attribute1 = l_rec.epe_attribute1,
epe_attribute2 = l_rec.epe_attribute2,
epe_attribute3 = l_rec.epe_attribute3,
epe_attribute4 = l_rec.epe_attribute4,
epe_attribute5 = l_rec.epe_attribute5,
epe_attribute6 = l_rec.epe_attribute6,
epe_attribute7 = l_rec.epe_attribute7,
epe_attribute8 = l_rec.epe_attribute8,
epe_attribute9 = l_rec.epe_attribute9,
epe_attribute10 = l_rec.epe_attribute10,
epe_attribute11 = l_rec.epe_attribute11,
epe_attribute12 = l_rec.epe_attribute12,
epe_attribute13 = l_rec.epe_attribute13,
epe_attribute14 = l_rec.epe_attribute14,
epe_attribute15 = l_rec.epe_attribute15,
epe_attribute16 = l_rec.epe_attribute16,
epe_attribute17 = l_rec.epe_attribute17,
epe_attribute18 = l_rec.epe_attribute18,
epe_attribute19 = l_rec.epe_attribute19,
epe_attribute20 = l_rec.epe_attribute20,
epe_attribute21 = l_rec.epe_attribute21,
epe_attribute22 = l_rec.epe_attribute22,
epe_attribute23 = l_rec.epe_attribute23,
epe_attribute24 = l_rec.epe_attribute24,
epe_attribute25 = l_rec.epe_attribute25,
epe_attribute26 = l_rec.epe_attribute26,
epe_attribute27 = l_rec.epe_attribute27,
epe_attribute28 = l_rec.epe_attribute28,
epe_attribute29 = l_rec.epe_attribute29,
epe_attribute30 = l_rec.epe_attribute30,
approval_status_cd = l_rec.approval_status_cd,
fonm_cvg_strt_dt = l_rec.fonm_cvg_strt_dt,
cryfwd_elig_dpnt_cd = l_rec.cryfwd_elig_dpnt_cd,
request_id = l_rec.request_id,
program_application_id = l_rec.program_application_id,
program_id = l_rec.program_id,
program_update_date = l_rec.program_update_date,
object_version_number = l_object_version_number
where elig_per_elctbl_chc_id = l_rec.elig_per_elctbl_chc_id;
ROLLBACK TO update_perf_ELIG_PER_ELC_CHC;
ROLLBACK TO update_perf_ELIG_PER_ELC_CHC;
end update_perf_ELIG_PER_ELC_CHC;
procedure delete_ELIG_PER_ELC_CHC
(p_validate in boolean default false
,p_elig_per_elctbl_chc_id in number
,p_object_version_number in out nocopy number
,p_effective_date in date
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) ;
/* select ecc.elctbl_chc_ctfn_id,
ecc.object_version_number
from ben_elctbl_chc_ctfn ecc
where elig_per_elctbl_chc_id in
(select elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc
where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id)
or enrt_bnft_id in
(select enrt_bnft_id
from ben_enrt_bnft
where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id);*/
select ecc.elctbl_chc_ctfn_id,
ecc.object_version_number
from ben_elctbl_chc_ctfn ecc
where elig_per_elctbl_chc_id in
(select elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc
where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id)
UNION
select ecc.elctbl_chc_ctfn_id,
ecc.object_version_number
from ben_elctbl_chc_ctfn ecc
where enrt_bnft_id in
(select enrt_bnft_id
from ben_enrt_bnft
where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id);
/* select ecr.enrt_rt_id,
ecr.object_version_number
from ben_enrt_rt ecr
where elig_per_elctbl_chc_id in
(select elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc
where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id)
or enrt_bnft_id in
(select enrt_bnft_id
from ben_enrt_bnft
where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id);
select ecr.enrt_rt_id,
ecr.object_version_number
from ben_enrt_rt ecr
where elig_per_elctbl_chc_id in
(select elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc
where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id)
UNION all
select ecr.enrt_rt_id,
ecr.object_version_number
from ben_enrt_rt ecr
where enrt_bnft_id in
(select enrt_bnft_id
from ben_enrt_bnft
where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id);
select enb.enrt_bnft_id,
enb.object_version_number
from ben_enrt_bnft enb
where elig_per_elctbl_chc_id in
(select elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc
where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id);
l_proc := g_package||'update_ELIG_PER_ELC_CHC';
savepoint delete_ELIG_PER_ELC_CHC;
ben_ELIG_PER_ELC_CHC_bk3.delete_ELIG_PER_ELC_CHC_b
(
p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
,p_object_version_number => p_object_version_number
,p_effective_date => trunc(p_effective_date)
);
(p_module_name => 'DELETE_ELIG_PER_ELC_CHC'
,p_hook_type => 'BP'
);
ben_enrollment_rate_api.delete_enrollment_rate
(p_validate => false,
p_enrt_rt_id => l_rate.enrt_rt_id,
p_object_version_number => l_rate.object_version_number,
p_effective_date => p_effective_date);
ben_enrt_bnft_api.delete_enrt_bnft
(p_validate => false,
p_enrt_bnft_id => l_bnft.enrt_bnft_id,
p_object_version_number => l_bnft.object_version_number,
p_effective_date => p_effective_date);
ben_ELIG_PER_ELC_CHC_bk3.delete_ELIG_PER_ELC_CHC_a
(
p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
);
(p_module_name => 'DELETE_ELIG_PER_ELC_CHC'
,p_hook_type => 'AP'
);
ROLLBACK TO delete_ELIG_PER_ELC_CHC;
ROLLBACK TO delete_ELIG_PER_ELC_CHC;
end delete_ELIG_PER_ELC_CHC;