[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
ongoing mass updates and IVR Process.
History
Date Who Version What?
---- --- ------- -----
01 Nov 05 ikasire 115.0 Created
31 Jan 06 ikasired 115.1 Added more validations
07 Feb 06 ikasired 115.2 More validations
21 Feb 06 ikasired 115.3 GSCC Errors
01 Mar 06 ikasired 115.4 Fix for c_epe cursors to
for plans in multiple programs
16 Mar 06 ikasired 115.6 Bug 5099945 fixes
17 Mar 06 ikasired 115.7 Bug 5100373 fix
21 Mar 06 ikasired 115.8 Bug 5108304 Added validation for
benefit amounts
Bug 5099864 Dependents validation fix
22 Mar 06 ikasired 115.9 Bug 5111326 fixes - more validations
23 Mar 06 ikasired 115.10 Bug 5097635 fix for beneficiary action
items
13 Apr 06 nkkrishn 115.11 Summary row elimination changes
02 May 06 nkkrishn 115.12 Fixed Beneficiary upload
10 May 06 nkkrishn 115.13 Beneficiary upload, suspend
enrollment problem fixed
10 May 06 nkkrishn 115.14 passing benefit amount param
to create_plan_beneficiary
12 Jun 06 ikasired 115.15 If condition got removed for the
call to ben_env_object.init
Bug 5259118
12 Jun 06 ikasired 115.16 Bug 5305426 to populate the proper
designation coverage start date
22 Nov 06 nkkrishn 115.18 Bug 5675220 - end dependant
designation not showing up
in PUI
07 Dec 06 nkkrishn 115.19 Bug 5675220 - end dependant
designation not showing up
in PUI.Using ben_prtt_enrt_result_api.
calc_dpnt_cvg_dt to calculate both
coverage start and end date for
dependants
29 Dec 06 nkkrishn 115.20 ENH - End Enrollment. (5738940)
16 Nov 10 velvanop 115.21 Bug 10209116: Coverage start and end dates should be taken
from the worksheet used for uploading the Dependent
elections if the date values are not NULL
*/
--
--Globals
--
g_debug boolean := hr_utility.debug_enabled;
select pil.per_in_ler_id
from ben_per_in_ler pil
where pil.ler_id = p_ler_id
and pil.lf_evt_ocrd_dt = p_life_event_date
and pil.person_id = p_person_id
and pil.per_in_ler_stat_cd = 'STRTD' ;
select epe.elig_per_elctbl_chc_id,
epe.enrt_cvg_strt_dt,
epe.enrt_cvg_strt_dt_cd,
epe.prtt_enrt_rslt_id,
epe.oipl_id,
epe.dpnt_cvg_strt_dt_cd,
epe.dpnt_cvg_strt_dt_rl,
epe.pgm_id,
epe.pl_id,
epe.ptip_id
from ben_elig_per_elctbl_chc epe,
ben_oipl_f oipl
where epe.per_in_ler_id = p_per_in_ler_id
and epe.pl_id = p_pl_id
and epe.oipl_id = oipl.oipl_id
and epe.elctbl_flag = 'Y'
and oipl.opt_id = p_opt_id
and (epe.pgm_id = p_pgm_id OR p_pgm_id IS NULL)
and p_life_event_date between oipl.effective_start_date
and oipl.effective_end_date ;
select epe.elig_per_elctbl_chc_id,
epe.enrt_cvg_strt_dt,
epe.enrt_cvg_strt_dt_cd,
epe.prtt_enrt_rslt_id,
epe.oipl_id,
epe.dpnt_cvg_strt_dt_cd,
epe.dpnt_cvg_strt_dt_rl,
epe.pgm_id,
epe.pl_id,
epe.ptip_id
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.oipl_id IS NULL --- SAVINGS PLAN FIX
and epe.pl_id = p_pl_id
and epe.elctbl_flag = 'Y'
and (epe.pgm_id = p_pgm_id OR p_pgm_id IS NULL) ;
select egd.elig_dpnt_id,
egd.elig_strt_dt,
egd.elig_thru_dt
from ben_elig_dpnt egd
where egd.per_in_ler_id = p_per_in_ler_id
and egd.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and egd.dpnt_person_id = p_dpnt_person_id ;
select pbn.pl_bnf_id,
pbn.dsgn_strt_dt,
pbn.dsgn_thru_dt,
pbn.object_version_number,
pbn.effective_start_date
from ben_pl_bnf_f pbn
where pbn.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pbn.bnf_person_id = p_bnf_person_id
and p_effective_date between pbn.effective_start_date
and pbn.effective_end_date ;
select pbn.pl_bnf_id,
pbn.dsgn_strt_dt,
pbn.dsgn_thru_dt,
pbn.object_version_number,
pbn.effective_start_date
from ben_pl_bnf_f pbn
where pbn.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pbn.organization_id = p_organization_id
and p_effective_date between pbn.effective_start_date
and pbn.effective_end_date ;
select 'x'
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
and pen.effective_end_date = hr_api.g_eot
and pen.enrt_cvg_thru_dt = hr_api.g_eot ;
select oipl_id
from ben_oipl_f
where opt_id = p_opt_id
and pl_id = p_pl_id
and p_effective_date between effective_start_date
and effective_end_date;
select pen.prtt_enrt_rslt_id,
pen.object_version_number
from ben_prtt_enrt_rslt_f pen
where pen.pl_id = p_pl_id
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
and ( p_bnft_val is NULL or
pen.bnft_amt = p_bnft_val)
and prtt_enrt_rslt_stat_cd is null;
select pen.prtt_enrt_rslt_id,
pen.object_version_number
from ben_prtt_enrt_rslt_f pen
where pen.oipl_id = l_oipl_id
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
and ( p_bnft_val is NULL or
pen.bnft_amt = p_bnft_val)
and prtt_enrt_rslt_stat_cd is null;
select pen.prtt_enrt_rslt_id,
pen.object_version_number
from ben_prtt_enrt_rslt_f pen
where pen.pgm_id = p_pgm_id
and pen.pl_id = p_pl_id
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
and ( p_bnft_val is NULL or
pen.bnft_amt = p_bnft_val)
and prtt_enrt_rslt_stat_cd is null;
select pen.prtt_enrt_rslt_id,
pen.object_version_number
from ben_prtt_enrt_rslt_f pen
where pen.pgm_id = p_pgm_id
and pen.oipl_id = l_oipl_id
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
and ( p_bnft_val is NULL or
pen.bnft_amt = p_bnft_val)
and prtt_enrt_rslt_stat_cd is null;
select oipl_id
from ben_oipl_f
where opt_id = p_opt_id
and pl_id = p_pl_id
and p_effective_date between effective_start_date
and effective_end_date;
select pen.prtt_enrt_rslt_id,
pen.object_version_number
from ben_prtt_enrt_rslt_f pen
where pen.pl_id = p_pl_id
and pen.person_id = p_person_id
and (pen.effective_end_date <> hr_api.g_eot
or pen.enrt_cvg_thru_dt <> hr_api.g_eot);
select pen.prtt_enrt_rslt_id,
pen.object_version_number
from ben_prtt_enrt_rslt_f pen
where pen.oipl_id = l_oipl_id
and pen.person_id = p_person_id
and (pen.effective_end_date <> hr_api.g_eot
or pen.enrt_cvg_thru_dt <> hr_api.g_eot);
select pen.prtt_enrt_rslt_id,
pen.object_version_number
from ben_prtt_enrt_rslt_f pen
where pen.pgm_id = p_pgm_id
and pen.pl_id = p_pl_id
and pen.person_id = p_person_id
and (pen.effective_end_date <> hr_api.g_eot
or pen.enrt_cvg_thru_dt <> hr_api.g_eot);
select pen.prtt_enrt_rslt_id,
pen.object_version_number
from ben_prtt_enrt_rslt_f pen
where pen.pgm_id = p_pgm_id
and pen.oipl_id = l_oipl_id
and pen.person_id = p_person_id
and (pen.effective_end_date <> hr_api.g_eot
or pen.enrt_cvg_thru_dt <> hr_api.g_eot);
select enb.enrt_bnft_id,
enb.cvg_mlt_cd,
enb.entr_val_at_enrt_flag,
enb.val,
enb.mn_val,
enb.mx_val,
enb.incrmt_val
from ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = l_epe.elig_per_elctbl_chc_id
and enb.mx_wo_ctfn_flag = 'N'
and (enb.entr_val_at_enrt_flag = 'Y' OR enb.vaL = p_bnft_val) ;
select enb.enrt_bnft_id,enb.cvg_mlt_cd,enb.entr_val_at_enrt_flag,enb.val,
enb.mn_val,
enb.mx_val,
enb.incrmt_val
from ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = l_epe.elig_per_elctbl_chc_id
and enb.mx_wo_ctfn_flag = 'N' ;
select enrt_rt_id,
rt_strt_dt,
rt_strt_dt_cd,
entr_val_at_enrt_flag,
val,
ann_val
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = l_epe.elig_per_elctbl_chc_id
--and ecr.entr_val_at_enrt_flag = 'Y'
and ecr.acty_base_rt_id = p_acty_base_rt_id ;
select enrt_rt_id,
rt_strt_dt,
rt_strt_dt_cd,
entr_val_at_enrt_flag,
val,
ann_val
from ben_enrt_rt ecr
where ecr.enrt_bnft_id = l_enb.enrt_bnft_id
--and ecr.entr_val_at_enrt_flag = 'Y'
and ecr.acty_base_rt_id = p_acty_base_rt_id ;
select 'Y'
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = p_epe_id
and ecr.entr_val_at_enrt_flag = 'Y' ;
select 'Y'
from ben_enrt_rt ecr
where ecr.enrt_bnft_id = p_enb_id
and ecr.entr_val_at_enrt_flag = 'Y' ;
select pln.name || ' '|| opt.name
from ben_elig_per_elctbl_chc epe,
ben_pl_f pln,
ben_oipl_f oipl,
ben_opt_f opt
where epe.elig_per_elctbl_chc_id = p_epe_id
and epe.pl_id = pln.pl_id
and epe.oipl_id = oipl.oipl_id(+)
and oipl.opt_id = opt.opt_id(+)
and p_life_event_date between
pln.effective_start_date and pln.effective_end_date
and p_life_event_date between
oipl.effective_start_date(+) and oipl.effective_end_date(+)
and p_life_event_date between
opt.effective_start_date(+) and opt.effective_end_date(+);
update ben_prtt_enrt_rslt_f
set ORGNL_ENRT_DT = p_orgnl_enrt_dt
where prtt_enrt_rslt_id = l_prtt_enrt_rslt_id ;
ben_prtt_enrt_result_api.delete_enrollment
(p_validate => p_validate
,p_per_in_ler_id => l_pil.per_in_ler_id
,p_lee_rsn_id => null --?
,p_enrt_perd_id => null --?
,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_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_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
,p_multi_row_validate => false --??
,p_source => null --?
,p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt --?
,p_mode => null); --?
select pgm_typ_cd
from ben_pgm_f pgm
where pgm.pgm_id = p_pgm_id
and p_life_event_date between pgm.effective_start_date
and pgm.effective_end_date ;
select pil.per_in_ler_id
from ben_per_in_ler pil
where pil.ler_id =p_ler_id
and pil.person_id = p_person_id
and pil.lf_evt_ocrd_dt = p_life_event_date
and pil.per_in_ler_stat_cd = 'STRTD' ;
l_dt_mode varchar2(30) := hr_api.g_update; --5675220
select object_version_number,
sspndd_flag
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
and business_group_id = p_business_group_id
and p_effective_date
between effective_start_date and effective_end_date;
l_dt_mode := hr_api.g_update ;
ben_plan_beneficiary_api.update_plan_beneficiary
( p_validate => p_validate
,p_pl_bnf_id => l_bnf.pl_bnf_id
,p_effective_start_date => l_dummy_date
,p_effective_end_date => l_dummy_date
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
,p_bnf_person_id => l_bnf_person_id
,p_organization_id => l_organization_id
,p_prmry_cntngnt_cd => l_prmry_cntngnt_cd
,p_pct_dsgd_num => l_pct_dsgd_num
,p_amt_dsgd_val => l_amt_dsgd_val
,p_amt_dsgd_uom => l_amt_dsgd_uom
,p_dsgn_strt_dt => l_dsgn_strt_dt
,p_dsgn_thru_dt => l_dsgn_thru_dt
,p_object_version_number => l_bnf.object_version_number
,p_per_in_ler_id => l_pil.per_in_ler_id
,p_effective_date => p_effective_date
,p_datetrack_mode => l_dt_mode
,p_multi_row_actn => p_multi_row_actn
);
l_dt_mode := hr_api.g_update ;
ben_plan_beneficiary_api.update_plan_beneficiary
( p_validate => p_validate
,p_pl_bnf_id => l_bnf.pl_bnf_id
,p_effective_start_date => l_dummy_date
,p_effective_end_date => l_dummy_date
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
,p_bnf_person_id => l_bnf_person_id
,p_organization_id => l_organization_id
,p_prmry_cntngnt_cd => l_prmry_cntngnt_cd
,p_pct_dsgd_num => l_pct_dsgd_num
,p_amt_dsgd_val => l_amt_dsgd_val
,p_amt_dsgd_uom => l_amt_dsgd_uom
,p_dsgn_strt_dt => l_dsgn_strt_dt
,p_dsgn_thru_dt => l_dsgn_thru_dt
,p_object_version_number => l_bnf.object_version_number
,p_per_in_ler_id => l_pil.per_in_ler_id
,p_effective_date => p_effective_date
,p_datetrack_mode => l_dt_mode
,p_multi_row_actn => p_multi_row_actn
);
ben_plan_beneficiary_api.delete_plan_beneficiary
(p_validate => p_validate
,p_pl_bnf_id => l_bnf.pl_bnf_id
,p_effective_start_date => l_dummy_date
,p_effective_end_date => l_dummy_date
,p_business_group_id => p_business_group_id
,p_object_version_number => l_bnf.object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => l_dt_mode
,p_multi_row_actn => true
);