[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
16 Jun 98 T Guy 110.1 Deleted others exception
18 Jun 98 Ty Hayden 110.2 Added p_rt_usg_cd
p_bnft_prvdr_pool_id
p_prtt_rt_val_id
08 jul 98 Pbodla 110.3 Added a local variable
l_elig_per_elctbl_chc_id1 to
avoid making
l_epe.elig_per_elctbl_chc_id null
08 jul 98 jcarpent 110.4 Added PLANFC and PLANIMP
10 aug 98 tguy 110.5 Changed inner looping cursor into
seperate cursors for performance
reasons. Put inner loop logic
into procedure ben_rates.This is
called by associated cursor
loop, (ie. plan, ptip, plip, pgm,
or oipl).
11 Aug 98 Ty Hayden 110.6 Added local variables l_pl_id etc.
11 Aug 98 Ty Hayden 110.7 Fix to local variable placement.
07 Oct 98 T Guy 115.2 Implemented schema changes for
ben_enrt_rt. Added location
debugging calls.Added message
numbers.
18 Oct 98 T Guy 115.3 Added annual values and cmcd
values
20 Oct 98 T Guy 115.4 p_actl_prem_id,
p_cvg_amt_cal_mthd_id
p_bnft_rt_typ_cd, p_rt_typ_cd
p_rt_mlt_cd, p_comp_lvl_fctr_id
p_entr_ann_val_flag
p_ptd_comp_lvl_fctr_id
p_ann_dflt_val
p_rt_start_dt, p_rt_start_dt_cd
p_rt_start_dt_rl
23 Oct 98 T Guy 115.5 added person_id to main
02 Nov 98 T Guy 115.6 fixed asn_on_enrt_flag
assignment for ben_enrt_rt
02 Dec 98 T Guy 115.7 fixed mx_ann_elcn_val variable
assignment error.
20 Dec 98 T Guy 115.8 Added in caching information
for the created enrollment rate.
11 Jan 99 T Guy 115.9 added elctbl_chc_id as passed in parm.
added edit to check if elctbl_chc_id
was passed if so use it, if not then
use the one from electbl_chc
18 Jan 99 G Perry 115.10 LED V ED
30 Jan 99 S Das 115.11 Added codes for comp objects.
04 Mar 99 T Guy 115.12 Fixed dflt_flag and ctfn_rqd_flag
09 Mar 99 G Perry 115.14 IS to AS.
05 Apr 99 mhoyes 115.15 - Un-datetrack of per_in_ler_f changes.
- Removed DT restriction from
- main/c_epe
30 Apr 99 lmcdonal 115.16 Add per_in_ler status restriction.
08 May 99 G Perry 115.17 Changed PLIP Cursor so it checks
for PLIP first and if there are
no PLIP's of type 'STD' then
it switches to PL or if there are
no PLIP rates. LM speced change.
18 Jun 99 maagrawa 115.18 Override Oipl level rates, when
oiplip rates are available.
Override Plan rates when plip
rates are present.
20-JUL-99 Gperry 115.19 genutils -> benutils package
rename.
22-JUL-99 mhoyes 115.20 - Added new trace messages
07-SEP-99 shdas 115.21 Added codes for cmbn ptip opt .
02-Oct-99 lmcdonal 115.22 was loading annual-min into annual-
max field. fixed it.
15-Nov-99 mhoyes 115.23 - Added trace messages.
09-Mar-00 lmcdonal 115.24 Add handling of new comp-lvl-cds
for flex credit choices. Restructured
main.
21-Mar-00 mhoyes 115.25 - Fixed batch rate info problem caused
by not setting g_rec.person_id before
calling ben_rates from the sub
electable choice loop.
30-Mar-00 mmogel 115.26 - Added tokens to messages to more
clearly identify the source of the
problem from the message text
03-May-00 mhoyes 115.27 - Tuned c_epe. Removed
nvl on p_elig_per_elctbl_chc_id and
request_id restriction.
12-May-00 mhoyes 115.28 - Added profiling trace messages.
15-May-00 mhoyes 115.29 - Called performance API.
29-May-00 mhoyes 115.30 - Added EPE context record.
- Passed around record structures.
31-May-00 mhoyes 115.31 - Removed nvls from SQL.
28-Jun-00 mhoyes 115.32 - Moved eligibility cursor outside of
abr loop.
- Referenced elig per cache rather than
sql.
03-Aug-00 mhoyes 115.34 - Bulk bind of ben_enrt_rt.
08-Sep-00 kmahendr 115.35 - added more attributes to cursors-plan not in
programs - www#1186195- Variable rate profiles
overriden
22-Sep-00 mhoyes 115.36 - Added calls clear down ATP and PTA function
caches.
07-Nov-00 mhoyes 115.37 - Added electable choice context global.
05-Jan-01 kmahendr 115.38 - Added parameter per_in_ler_id for unrestricted
02-Aug-01 ikasire 115.39 Bug1895846 added exclusion condition for
suspended enrollment results
02-Aug-01 ikasre 115.40 added modification history for 115.39
28-Aug-01 kmahendr 115.41 bug#1936976-for coverage ranze code
prtt_rt_val_id is populated based on
benefit amount
19-dec-01 pbodla 115.42 CWB Changes - ben_old_retes only
looks at non comp per in ler's
20-dec-01 ikasire 115.43 added dbdrv lines
22-feb-02 pbodla 115.44 Bug 2234582 : Some times null
second parameter is giving error.
Explicit second parameter is passed
to hr_utility. set_location
15-may-02 ikasire 115.45 Bug 2200139 added a new parameter
p_elig_per_elctbl_chc_id to the main
procedure to called from override
process.For other processes it is
always passed as null .
23-May-02 kmahendr 115.46 Added a parameter to ben_determine_acty_base_rt
10-jun-02 pabodla 115.47 LGE : Create rate certifications
28-Sep-02 ikasire 115.49 reverted the changes made in 115.48
11-Oct-02 vsethi 115.50 Rates Sequence no enhancements. Modified to cater
to new column ord_num on ben_acty_base_rt_f
28-Oct-02 kmahendr 115.51 Land O Lakes performance fix - cursor c_enrt_rt
split into two.
26-Dec-02 rpillay 115.52 NOCOPY changes
13-feb-02 vsethi 115.53 Enclosed all hr_utility debug calls inside if
13-feb-02 kmahendr 115.54 Added a parameter to call -acty_base_rt.main
15-Apr-02 tjesumic 115.55 # 2897152 When the Std. Rate Calc Method code
is No Standard Values Used' NSVU and all vapro
attached to the rate are failed then no rate
will be created for the std rate.
This is fixed by deleting all the enrt_rt for
the above condition
08-May-03 ikasire 115.56 Option Level rates enhancements.
16-Sep-03 kmahendr 115.57 GSP changes
17-Dec-03 vvprabhu 115.58 Added the assignment for g_debug at the start of
each public procedure
14-Jan-03 bmanyam 115.59 Bug: 3234092: GSP Changes. Enabling
calculation of 'Salary Caclculation Rule'
instead of Activity Rates, in GSP mode.
14-Jan-03 pbodla 115.60,61 GLOBALCWB : Added code to populate
06-Feb-04 pbodla 115.62 GLOBALCWB : Added code to populate
WS_RT_START_DAT in ben_cwb_person_rates
25-Feb-04 pbodla 115.63 GLOBALCWB : Even if rates are not
defined still populate the cwb
tables - person_groups and
person_rates.
14-Apr-04 mmudigon 115.64 FONM changes
21-May-04 pbodla 115.65 FONM changes continued
mmudigon
24-May-04 nhunur 115.66 3633345 - changed cursor c_enrt_ctfn_rt.
16-Jun-04 mmudigon 115.68 same as version 115.66. Incorrect
arcs in version 115.67
03-sep-04 nhunur 115.69 3274902 : Changed code to populate cmcd_dflt_val
03-sep-04 nhunur 115.70 3234092 : Added code to get rt_strt_dt for GSP
17-sep-04 nhunur 115.71 iRec : c_per_in_ler modified not to look
at gsp/irec/abs/comp type of events.
15-Nov-04 kmahendr 115.73 Unrest. enh changes
18-Jan-05 kmahendr 115.74 Bug#4126093 - added check before creating
enrollment rate certificate
10-Mar-05 abparekh 115.75 Bug 4230502 : Populate L_ENRT_RT_ID_TAB
with created / updated ECR PK Ids
30-mar-05 nhunur 115.76 GSP change to ensure ff contexts are correctly passed.
26-Jul-05 nhunur 115.77 assign the FONM CVG date before calling rate calcaultion
08-Nov-05 pbodla 115.78 Bug 4693040 : When a rate is defined as nsvu
in cwb mode no need to delete the enrt_rt row
as it is not created at all, also rate
certifications not relevant for CWB rates.
25-Jan-05 swjain 115.79 Absences Enhancement: Added order by abr_seq_num
in all the abr cursors in procedure main
21-feb-06 pbodla 115.80 CWB : added multi currency support code.
24-feb-06 pbodla 115.81 CWB : fixed data type for
l_currency_cd
21-Apr-06 ikasired 115.82 CWB : 5148387 handling for benefit assignment
22-Feb-08 rtagarra 115.83 Bug 6840074
17-Jun-08 sagnanas 116.84 Bug 7154229
*/
----------------------------------------------------------------------------------------------
g_package VARCHAR2(80) := 'ben_determine_rates';
SELECT b.val
FROM ben_enrt_rt b
WHERE b.acty_base_rt_id = p_acty_base_rt_id
AND b.elig_per_elctbl_chc_id = l_elctbl_chc_id;
SELECT b.elig_per_elctbl_chc_id
FROM ben_elig_per_elctbl_chc b
WHERE b.per_in_ler_id = l_per_in_ler
AND ( b.pgm_id = p_pgm_id
OR pgm_id IS NULL)
AND ( b.pl_id = p_pl_id
OR pl_id IS NULL)
AND ( b.oipl_id = p_oipl_id
OR p_oipl_id IS NULL);
SELECT pil.per_in_ler_id
FROM ben_per_in_ler pil,
ben_ler_f ler
WHERE pil.person_id = p_person_id
and pil.ler_id = ler.ler_id
and ler.typ_cd not in ('COMP','GSP', 'IREC', 'ABS')
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
AND lf_evt_ocrd_dt =
(SELECT MAX(b.lf_evt_ocrd_dt)
FROM ben_per_in_ler b,
ben_ler_f ler1
WHERE b.person_id = p_person_id
and b.ler_id = ler1.ler_id
and b.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
and ler1.typ_cd not in ('COMP','GSP', 'IREC', 'ABS')
AND b.lf_evt_ocrd_dt < p_lf_evt_ocrd_dt);
l_last_update_date ben_enrt_rt.last_update_date%TYPE;
l_last_updated_by ben_enrt_rt.last_updated_by%TYPE;
l_last_update_login ben_enrt_rt.last_update_login%TYPE;
l_program_update_date ben_enrt_rt.program_update_date%TYPE
:= SYSDATE;
select null
from ben_prtt_enrt_rslt_f pen,
ben_enrt_bnft enb
where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and pen.bnft_ordr_num = enb.ordr_num
and enb.enrt_bnft_id = p_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;
select ben_enrt_rt_s.nextval
from sys.dual;
select ecr.enrt_rt_id,
abc.enrt_ctfn_typ_cd,
abc.rqd_flag
from ben_enrt_rt ecr,
ben_enrt_bnft enb,
BEN_ACTY_BASE_RT_CTFN_F abc
where ecr.acty_base_rt_id = p_acty_base_rt_id
and ecr.business_group_id = p_business_group_id
and decode(ecr.enrt_bnft_id, null, ecr.elig_per_elctbl_chc_id,
enb.elig_per_elctbl_chc_id) =
p_elig_per_elctbl_chc_id
and enb.enrt_bnft_id (+) = ecr.enrt_bnft_id
and abc.acty_base_rt_id = ecr.acty_base_rt_id;
select ecr.enrt_rt_id,
abc.enrt_ctfn_typ_cd,
abc.rqd_flag
from ben_enrt_rt ecr,
BEN_ACTY_BASE_RT_CTFN_F abc
where ecr.acty_base_rt_id = p_acty_base_rt_id
and ecr.business_group_id = p_business_group_id
and ecr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and abc.acty_base_rt_id = ecr.acty_base_rt_id+0;
select ecr.enrt_rt_id,
abc.enrt_ctfn_typ_cd,
abc.rqd_flag
from ben_enrt_rt ecr,
ben_enrt_bnft enb,
BEN_ACTY_BASE_RT_CTFN_F abc
where ecr.acty_base_rt_id = p_acty_base_rt_id
and ecr.business_group_id = p_business_group_id
and enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and enb.enrt_bnft_id = ecr.enrt_bnft_id
and abc.acty_base_rt_id = ecr.acty_base_rt_id;
select null
from ben_enrt_rt_ctfn erc
where erc.enrt_rt_id = p_enrt_rt_id
and erc.enrt_ctfn_typ_cd = p_enrt_ctfn_typ_cd;
SELECT pgm.pgm_typ_cd, pgm.salary_calc_mthd_cd, pgm.salary_calc_mthd_rl
FROM ben_pgm_f pgm
where pgm.pgm_id = p_pgm_id
and p_effective_date between pgm.effective_start_date and pgm.effective_end_date;
select abr.acty_typ_cd,
abr.tx_typ_cd,
abr.dsply_on_enrt_flag,
abr.use_to_calc_net_flx_cr_flag,
abr.entr_val_at_enrt_flag,
abr.entr_ann_val_flag,
abr.currency_det_cd,
abr.element_det_rl,
abr.element_type_id
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id = p_acty_base_rt_id
and p_effective_date between abr.effective_start_date and abr.effective_end_date;
hr_utility.set_location('Insert ECR: ' || l_package, 5);
ben_manage_unres_life_events.update_enrt_rt
( p_enrt_rt_id => l_enrt_rt_id,
p_acty_typ_cd => l_acty_typ_cd(i),
p_tx_typ_cd => l_tx_typ_cd(i),
p_ctfn_rqd_flag => p_ctfn_rqd_flag,
p_dflt_flag => p_dflt_flag,
p_dflt_pndg_ctfn_flag => l_no,
p_dsply_on_enrt_flag => l_dsply_on_enrt_flag(i),
p_use_to_calc_net_flx_cr_flag => l_use_to_calc_net_flx_cr_flag(i),
p_entr_val_at_enrt_flag => l_entr_val_at_enrt_flag(i),
p_asn_on_enrt_flag => p_asn_on_enrt_flag_table(i),
p_rl_crs_only_flag => l_no,
p_dflt_val => l_dflt_val(i),
p_ann_val => l_ann_val(i),
p_ann_mn_elcn_val => l_ann_mn_elcn_val(i),
p_ann_mx_elcn_val => l_ann_mx_elcn_val(i),
p_val => l_val(i),
p_nnmntry_uom => l_nnmntry_uom(i),
p_mx_elcn_val => l_mx_elcn_val(i),
p_mn_elcn_val => l_mn_elcn_val(i),
p_incrmt_elcn_val => l_incrmt_elcn_val(i),
p_cmcd_acty_ref_perd_cd => l_cmcd_acty_ref_perd_cd(i),
p_cmcd_mn_elcn_val => l_cmcd_mn_elcn_val(i),
p_cmcd_mx_elcn_val => l_cmcd_mx_elcn_val(i),
p_cmcd_val => l_cmcd_val(i),
p_cmcd_dflt_val => l_cmcd_val(i), --7154229
p_rt_usg_cd => l_rt_usg_cd(i),
p_ann_dflt_val => l_ann_dflt_val(i),
p_bnft_rt_typ_cd => l_bnft_rt_typ_cd(i),
p_rt_mlt_cd => l_rt_mlt_cd(i),
p_dsply_mn_elcn_val => l_dsply_mn_elcn_val(i),
p_dsply_mx_elcn_val => l_dsply_mx_elcn_val(i),
p_entr_ann_val_flag => l_entr_ann_val_flag(i),
p_rt_strt_dt => l_rt_strt_dt(i),
p_rt_strt_dt_cd => l_rt_strt_dt_cd(i),
p_rt_strt_dt_rl => l_rt_strt_dt_rl(i),
p_rt_typ_cd => l_rt_typ_cd(i),
p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id1,
p_acty_base_rt_id => p_acty_base_rt_id_table(i),
p_spcl_rt_enrt_rt_id => l_number_null,
p_enrt_bnft_id => p_enrt_bnft_id,
p_prtt_rt_val_id => l_prtt_rt_val_id(i),
p_decr_bnft_prvdr_pool_id => l_decr_bnft_prvdr_pool_id(i),
p_cvg_amt_calc_mthd_id => l_cvg_amt_calc_mthd_id(i),
p_actl_prem_id => l_actl_prem_id(i),
p_comp_lvl_fctr_id => l_comp_lvl_fctr_id(i),
p_ptd_comp_lvl_fctr_id => l_ptd_comp_lvl_fctr_id(i),
p_clm_comp_lvl_fctr_id => l_clm_comp_lvl_fctr_id(i),
p_business_group_id => p_business_group_id,
p_request_id => l_request_id,
p_program_application_id => l_program_application_id,
p_program_id =>l_program_id,
p_program_update_date => l_program_update_date ,
p_effective_date => p_effective_date,
p_pp_in_yr_used_num => l_pp_in_yr_used_num(i),
p_ordr_num => l_ordr_num(i),
p_iss_val => l_iss_val(i))
;
INSERT INTO ben_enrt_rt
(
enrt_rt_id,
acty_typ_cd,
tx_typ_cd,
ctfn_rqd_flag,
dflt_flag,
dflt_pndg_ctfn_flag,
dsply_on_enrt_flag,
use_to_calc_net_flx_cr_flag,
entr_val_at_enrt_flag,
asn_on_enrt_flag,
rl_crs_only_flag,
dflt_val,
ann_val,
ann_mn_elcn_val,
ann_mx_elcn_val,
val,
nnmntry_uom,
mx_elcn_val,
mn_elcn_val,
incrmt_elcn_val,
cmcd_acty_ref_perd_cd,
cmcd_mn_elcn_val,
cmcd_mx_elcn_val,
cmcd_val,
cmcd_dflt_val,
rt_usg_cd,
ann_dflt_val,
bnft_rt_typ_cd,
rt_mlt_cd,
dsply_mn_elcn_val,
dsply_mx_elcn_val,
entr_ann_val_flag,
rt_strt_dt,
rt_strt_dt_cd,
rt_strt_dt_rl,
rt_typ_cd,
elig_per_elctbl_chc_id,
acty_base_rt_id,
spcl_rt_enrt_rt_id,
enrt_bnft_id,
prtt_rt_val_id,
decr_bnft_prvdr_pool_id,
cvg_amt_calc_mthd_id,
actl_prem_id,
comp_lvl_fctr_id,
ptd_comp_lvl_fctr_id,
clm_comp_lvl_fctr_id,
business_group_id,
ecr_attribute_category,
ecr_attribute1,
ecr_attribute2,
ecr_attribute3,
ecr_attribute4,
ecr_attribute5,
ecr_attribute6,
ecr_attribute7,
ecr_attribute8,
ecr_attribute9,
ecr_attribute10,
ecr_attribute11,
ecr_attribute12,
ecr_attribute13,
ecr_attribute14,
ecr_attribute15,
ecr_attribute16,
ecr_attribute17,
ecr_attribute18,
ecr_attribute19,
ecr_attribute20,
ecr_attribute21,
ecr_attribute22,
ecr_attribute23,
ecr_attribute24,
ecr_attribute25,
ecr_attribute26,
ecr_attribute27,
ecr_attribute28,
ecr_attribute29,
ecr_attribute30,
last_update_login,
created_by,
creation_date,
last_updated_by,
last_update_date,
request_id,
program_application_id,
program_id,
program_update_date,
object_version_number,
pp_in_yr_used_num,
ordr_num,
iss_val)
VALUES(
ben_enrt_rt_s.nextval,
l_acty_typ_cd(i),
l_tx_typ_cd(i),
p_ctfn_rqd_flag,
p_dflt_flag,
l_no,
l_dsply_on_enrt_flag(i),
l_use_to_calc_net_flx_cr_flag(i),
l_entr_val_at_enrt_flag(i),
p_asn_on_enrt_flag_table(i),
l_no,
l_dflt_val(i),
l_ann_val(i),
l_ann_mn_elcn_val(i),
l_ann_mx_elcn_val(i),
l_val(i),
l_nnmntry_uom(i),
l_mx_elcn_val(i),
l_mn_elcn_val(i),
l_incrmt_elcn_val(i),
l_cmcd_acty_ref_perd_cd(i),
l_cmcd_mn_elcn_val(i),
l_cmcd_mx_elcn_val(i),
l_cmcd_val(i),
l_cmcd_val(i) , --7154229
l_rt_usg_cd(i),
l_ann_dflt_val(i),
l_bnft_rt_typ_cd(i),
l_rt_mlt_cd(i),
l_dsply_mn_elcn_val(i),
l_dsply_mx_elcn_val(i),
l_entr_ann_val_flag(i),
l_rt_strt_dt(i),
l_rt_strt_dt_cd(i),
l_rt_strt_dt_rl(i),
l_rt_typ_cd(i),
l_elig_per_elctbl_chc_id1,
p_acty_base_rt_id_table(i),
l_number_null,
p_enrt_bnft_id,
l_prtt_rt_val_id(i),
l_decr_bnft_prvdr_pool_id(i),
l_cvg_amt_calc_mthd_id(i),
l_actl_prem_id(i),
l_comp_lvl_fctr_id(i),
l_ptd_comp_lvl_fctr_id(i),
l_clm_comp_lvl_fctr_id(i),
p_business_group_id,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_last_update_login,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_request_id,
l_program_application_id,
l_program_id,
l_program_update_date,
l_object_version_number,
l_pp_in_yr_used_num(i),
l_ordr_num(i),
l_iss_val(i))
returning enrt_rt_id into l_enrt_rt_id_tab(i) ; /* Bug 4230502 */
INSERT INTO ben_enrt_rt
(
enrt_rt_id,
acty_typ_cd,
tx_typ_cd,
ctfn_rqd_flag,
dflt_flag,
dflt_pndg_ctfn_flag,
dsply_on_enrt_flag,
use_to_calc_net_flx_cr_flag,
entr_val_at_enrt_flag,
asn_on_enrt_flag,
rl_crs_only_flag,
dflt_val,
ann_val,
ann_mn_elcn_val,
ann_mx_elcn_val,
val,
nnmntry_uom,
mx_elcn_val,
mn_elcn_val,
incrmt_elcn_val,
cmcd_acty_ref_perd_cd,
cmcd_mn_elcn_val,
cmcd_mx_elcn_val,
cmcd_val,
cmcd_dflt_val,
rt_usg_cd,
ann_dflt_val,
bnft_rt_typ_cd,
rt_mlt_cd,
dsply_mn_elcn_val,
dsply_mx_elcn_val,
entr_ann_val_flag,
rt_strt_dt,
rt_strt_dt_cd,
rt_strt_dt_rl,
rt_typ_cd,
elig_per_elctbl_chc_id,
acty_base_rt_id,
spcl_rt_enrt_rt_id,
enrt_bnft_id,
prtt_rt_val_id,
decr_bnft_prvdr_pool_id,
cvg_amt_calc_mthd_id,
actl_prem_id,
comp_lvl_fctr_id,
ptd_comp_lvl_fctr_id,
clm_comp_lvl_fctr_id,
business_group_id,
ecr_attribute_category,
ecr_attribute1,
ecr_attribute2,
ecr_attribute3,
ecr_attribute4,
ecr_attribute5,
ecr_attribute6,
ecr_attribute7,
ecr_attribute8,
ecr_attribute9,
ecr_attribute10,
ecr_attribute11,
ecr_attribute12,
ecr_attribute13,
ecr_attribute14,
ecr_attribute15,
ecr_attribute16,
ecr_attribute17,
ecr_attribute18,
ecr_attribute19,
ecr_attribute20,
ecr_attribute21,
ecr_attribute22,
ecr_attribute23,
ecr_attribute24,
ecr_attribute25,
ecr_attribute26,
ecr_attribute27,
ecr_attribute28,
ecr_attribute29,
ecr_attribute30,
last_update_login,
created_by,
creation_date,
last_updated_by,
last_update_date,
request_id,
program_application_id,
program_id,
program_update_date,
object_version_number,
pp_in_yr_used_num,
ordr_num,
iss_val)
VALUES(
ben_enrt_rt_s.nextval,
l_acty_typ_cd(i),
l_tx_typ_cd(i),
p_ctfn_rqd_flag,
p_dflt_flag,
l_no,
l_dsply_on_enrt_flag(i),
l_use_to_calc_net_flx_cr_flag(i),
l_entr_val_at_enrt_flag(i),
p_asn_on_enrt_flag_table(i),
l_no,
l_dflt_val(i),
l_ann_val(i),
l_ann_mn_elcn_val(i),
l_ann_mx_elcn_val(i),
l_val(i),
l_nnmntry_uom(i),
l_mx_elcn_val(i),
l_mn_elcn_val(i),
l_incrmt_elcn_val(i),
l_cmcd_acty_ref_perd_cd(i),
l_cmcd_mn_elcn_val(i),
l_cmcd_mx_elcn_val(i),
l_cmcd_val(i),
l_cmcd_val(i) , --7154229
l_rt_usg_cd(i),
l_ann_dflt_val(i),
l_bnft_rt_typ_cd(i),
l_rt_mlt_cd(i),
l_dsply_mn_elcn_val(i),
l_dsply_mx_elcn_val(i),
l_entr_ann_val_flag(i),
l_rt_strt_dt(i),
l_rt_strt_dt_cd(i),
l_rt_strt_dt_rl(i),
l_rt_typ_cd(i),
l_elig_per_elctbl_chc_id1,
p_acty_base_rt_id_table(i),
l_number_null,
p_enrt_bnft_id,
l_prtt_rt_val_id(i),
l_decr_bnft_prvdr_pool_id(i),
l_cvg_amt_calc_mthd_id(i),
l_actl_prem_id(i),
l_comp_lvl_fctr_id(i),
l_ptd_comp_lvl_fctr_id(i),
l_clm_comp_lvl_fctr_id(i),
p_business_group_id,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_varchar2_null,
l_last_update_login,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_request_id,
l_program_application_id,
l_program_id,
l_program_update_date,
l_object_version_number,
l_pp_in_yr_used_num(i),
l_ordr_num(i),
l_iss_val(i))
returning enrt_rt_id BULK COLLECT into l_enrt_rt_id_tab ;
hr_utility.set_location('after Insert ' , 5);
Delete from Ben_enrt_rt
where val is null
and rt_mlt_cd = l_rt_mlt_cd(l_nsuv_num)
and acty_base_rt_id = p_acty_base_rt_id_table(l_nsuv_num)
and business_group_id = p_business_group_id
and enrt_rt_id = l_enrt_rt_id_tab(l_nsuv_num) ;
,p_program_update_date => l_program_update_date
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
) ;
,p_program_update_date => l_program_update_date
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
) ;
SELECT epe.elig_per_elctbl_chc_id,
epe.comp_lvl_cd,
epe.oipl_id,
epe.pl_id,
epe.pgm_id,
epe.plip_id,
epe.oiplip_id,
epe.ptip_id,
epe.pl_typ_id,
epe.cmbn_plip_id,
epe.cmbn_ptip_id,
epe.cmbn_ptip_opt_id,
epe.business_group_id,
epe.dflt_flag,
epe.ctfn_rqd_flag,
enb.enrt_bnft_id,
epe.per_in_ler_id,
epe.prtt_enrt_rslt_id,
epe.enrt_cvg_strt_dt,
epe.enrt_cvg_strt_dt_cd,
epe.enrt_cvg_strt_dt_rl,
epe.yr_perd_id,
pel.enrt_perd_strt_dt,
pel.enrt_perd_id,
pel.lee_rsn_id,
pel.acty_ref_perd_cd,
pil.ler_id,
pil.person_id
FROM ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_enrt_bnft enb,
ben_per_in_ler pil
WHERE epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id (+)
AND epe.per_in_ler_id = pil.per_in_ler_id
AND epe.per_in_ler_id = pel.per_in_ler_id
AND epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
-- AND pil.per_in_ler_stat_cd = 'STRTD'
-- added for unrestricted enhancement
and pil.per_in_ler_id = p_per_in_ler_id
AND pil.person_id = p_person_id
-- Override Enrollment Changes
AND nvl(epe.elig_per_elctbl_chc_id,-1) = nvl(p_elig_per_elctbl_chc_id,-1);
SELECT abr.acty_base_rt_id,
abr.asn_on_enrt_flag
FROM ben_acty_base_rt_f abr
WHERE abr.oiplip_id = l_oiplip_id
AND abr.rt_usg_cd <> 'FLXCR'
AND abr.acty_base_rt_stat_cd = 'A'
AND c_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
ORDER BY abr_seq_num;
SELECT abr.acty_base_rt_id,
abr.asn_on_enrt_flag
FROM ben_acty_base_rt_f abr
WHERE abr.acty_base_rt_stat_cd = 'A'
AND abr.oipl_id = l_oipl_id
AND abr.rt_usg_cd <> 'FLXCR'
AND c_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
ORDER BY abr_seq_num;
SELECT abr.acty_base_rt_id,
abr.asn_on_enrt_flag
FROM ben_acty_base_rt_f abr
WHERE abr.acty_base_rt_stat_cd = 'A'
AND abr.opt_id = l_opt_id
AND abr.rt_usg_cd <> 'FLXCR'
AND c_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
ORDER BY abr_seq_num;
SELECT abr.acty_base_rt_id,
abr.asn_on_enrt_flag
FROM ben_acty_base_rt_f abr
WHERE abr.acty_base_rt_stat_cd = 'A'
AND abr.plip_id = l_plip_id
AND abr.rt_usg_cd <> 'FLXCR'
AND abr.cmbn_plip_id IS NULL
AND c_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
ORDER BY abr_seq_num;
SELECT abr.acty_base_rt_id,
abr.asn_on_enrt_flag
FROM ben_acty_base_rt_f abr
WHERE abr.acty_base_rt_stat_cd = 'A'
AND abr.pl_id = l_pl_id
AND abr.rt_usg_cd <> 'FLXCR'
AND c_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
ORDER BY abr_seq_num;
SELECT abr.acty_base_rt_id,
abr.asn_on_enrt_flag
FROM ben_acty_base_rt_f abr
WHERE abr.acty_base_rt_stat_cd = 'A'
AND abr.pgm_id = l_pgm_id
AND c_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
ORDER BY abr_seq_num;
SELECT abr.acty_base_rt_id,
abr.asn_on_enrt_flag
FROM ben_acty_base_rt_f abr
WHERE abr.acty_base_rt_stat_cd = 'A'
AND abr.ptip_id = l_ptip_id
AND abr.cmbn_ptip_id IS NULL
AND abr.cmbn_ptip_opt_id IS NULL
AND c_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
ORDER BY abr_seq_num;
SELECT abr.acty_base_rt_id,
abr.asn_on_enrt_flag
FROM ben_acty_base_rt_f abr
WHERE abr.acty_base_rt_stat_cd = 'A'
AND abr.cmbn_ptip_id = l_cmbn_ptip_id
AND c_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
AND abr.cmbn_ptip_opt_id IS NULL
AND abr.ptip_id IS NULL
ORDER BY abr_seq_num;
SELECT abr.acty_base_rt_id,
abr.asn_on_enrt_flag
FROM ben_acty_base_rt_f abr
WHERE abr.acty_base_rt_stat_cd = 'A'
AND abr.plip_id = l_plip_id
AND abr.rt_usg_cd = 'FLXCR'
AND abr.cmbn_plip_id IS NULL
AND c_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
ORDER BY abr_seq_num;
SELECT abr.acty_base_rt_id,
abr.asn_on_enrt_flag
FROM ben_acty_base_rt_f abr
WHERE abr.acty_base_rt_stat_cd = 'A'
AND abr.cmbn_plip_id = l_cmbn_plip_id
AND c_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
AND abr.plip_id IS NULL
ORDER BY abr_seq_num;
SELECT abr.acty_base_rt_id,
abr.asn_on_enrt_flag
FROM ben_acty_base_rt_f abr
WHERE abr.oiplip_id = l_oiplip_id
AND abr.rt_usg_cd = 'FLXCR'
AND abr.acty_base_rt_stat_cd = 'A'
AND c_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
ORDER BY abr_seq_num;
SELECT abr.acty_base_rt_id,
abr.asn_on_enrt_flag
FROM ben_acty_base_rt_f abr
WHERE abr.acty_base_rt_stat_cd = 'A'
AND abr.cmbn_ptip_opt_id = l_cmbn_ptip_opt_id
AND c_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
AND abr.ptip_id IS NULL
AND abr.cmbn_ptip_id IS NULL
ORDER BY abr_seq_num;
SELECT ep.prtn_strt_dt,
ep.prtn_ovridn_flag,
ep.prtn_ovridn_thru_dt,
ep.rt_age_val,
ep.rt_los_val,
ep.rt_hrs_wkd_val,
ep.rt_cmbn_age_n_los_val
FROM ben_elig_per_f ep, ben_per_in_ler pil
WHERE ep.person_id = c_person_id
AND ep.pgm_id IS NULL
AND ep.pl_id = c_pl_id
AND c_effective_date BETWEEN ep.effective_start_date
AND ep.effective_end_date
AND pil.per_in_ler_id (+) = ep.per_in_ler_id
AND (
pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
OR pil.per_in_ler_stat_cd IS NULL);
SELECT epo.prtn_strt_dt,
epo.prtn_ovridn_flag,
epo.prtn_ovridn_thru_dt,
epo.rt_age_val,
epo.rt_los_val,
epo.rt_hrs_wkd_val,
epo.rt_cmbn_age_n_los_val
FROM ben_elig_per_f ep, ben_elig_per_opt_f epo, ben_per_in_ler pil
WHERE ep.person_id = c_person_id
AND ep.pgm_id IS NULL
AND ep.pl_id = c_pl_id
AND epo.opt_id = c_opt_id
AND c_effective_date BETWEEN ep.effective_start_date
AND ep.effective_end_date
AND ep.elig_per_id = epo.elig_per_id
AND c_effective_date BETWEEN epo.effective_start_date
AND epo.effective_end_date
AND pil.per_in_ler_id (+) = ep.per_in_ler_id
AND (
pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
OR pil.per_in_ler_stat_cd IS NULL);
SELECT oipl.opt_id
FROM ben_oipl_f oipl
WHERE oipl.oipl_id = c_oipl_id
AND c_effective_date BETWEEN oipl.effective_start_date
AND oipl.effective_end_date;
l_acty_base_rt_id_table.DELETE;
l_asn_on_enrt_flag_table.DELETE;