[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
changed and updates corresponding data
History:
Date Who Version What?
---- --- ------- -----
25 Oct 98 T Guy 115.0 Created.
22 Dec 98 T Guy 115.1 Added enrt_mthd_cd to call for
ben_election_information.
election_rate_information
28 Dec 98 j lamoureux 115.2 Removed parameters
dflt_enrt_dt,
enrt_typ_cycl_cd,
enrt_perd_strt_dt,
enrt_perd_end_dt
in call to elig_per_elctbl_chc api
11 Jan 99 T Guy 115.3 added elctbl_chc_id to call for
ben_determine_rates.main
16 Feb 99 T Guy 115.4 added comp level cd check for
planfc and planimp
09 Mar 99 G Perry 115.6 IS to AS.
16 Apr 99 T Guy 115.7 Removed call to create new
elctbl_chc. Bendenrr now creates
a choice for currently enrolled
and sets elctbl_flag to N. Also
removed calls to determince coverage
and determine rates. These will
be already run during the normal
benmngle run.
29 Apr 99 lmcdonal 115.8 prtt_rt_val and prtt_enrt_rslt have
status codes now.
30 Jun 99 T Guy 115.9 Added total premium check
07 Jul 99 jcarpent 115.10 Added per_in_ler_id to call to
update_prtt_prem_f
09 Jul 99 jcarpent 115.11 Added checks for backed out nocopy pil
20-JUL-99 Gperry 115.12 genutils -> benutils package
rename.
14-SEP-99 shdas 115.13 added bnft_val to election_information
28-SEP-99 tguy 115.14 set global flag so that benmngle will
will close per_in_ler's with the others
being processed. This allows us to
avoid duplication and throughing off
counts for reporting purposes.
18-NOV-99 gperry 115.15 Corrected error messages.
30-Dec-99 maagrawa 115.16 Bug 3431 (1096820) fixed.
Major re-structuring of package.
Added parameter business_group_id.
20-Jan-00 thayden 115.17 Get any per_in_ler_id, not just STRTD.
02-Feb-00 lmcdonal 115.18 Re-compute certain premiums before
updating the prtt-prem row.
18-Feb-00 jcarpent 115.19 Remove 115.14 change to set the
g_electable_choice_created flag
this is already done in bendenrr.
Bug 4720 (no wwbug number)
01-Mar-00 jcarpent 115.20 Pass bnft_amt_changed flag to
ben_election_rate_info. Put in
payroll change checks.
31-Mar-00 mmogel 115.21 I changed the message number from
91382 to 91832 in the message name
BEN_91382_PACKAGE_PARAM_NULL
14-May-00 gperry 115.22 Fixed bug 1298556. Rates get created
if activity changes and rate does
not.
25-May-00 lmcdonal 115.23 Bug 1312906 leap-froged from 115.21
for aera.
call imputed income if benefit
changes on subj-to-imp plan.
25-May-00 lmcdonal 115.24 Bug 1312906 'real' version.
29-May-00 gperry 115.25 Corrected cursor for bug 1298556.
05-Jan-01 kmahendr 115.26 Added parameter per_in_ler_id
16-Jan-01 mhoyes 115.27 EFC stuff. Added new OUT NOCOPY parameter
to election_rate_information.
19-feb-01 tilak 115.28 flex credit amout changes is not affecting
on next enrollment. cursor c_flex and c_bpl
is creatd to update the ledger
15-Mar-01 kmahendr 115.29 Modified cursor c_flex and c_bpl and added call
total_pools to write prtt_rt_val for flex credit
Bug#1653733
26 Jun 01 ikasire 115.30 bug 1849019 added two new procedures
prv_delete and get_rate_codes to
handle ENTRBL rate start date codes.
17 Aug 01 kmahendr 115.31 Added parameter p_mode to prv_delete and modified
codes
22 Aug 01 kmahendr 115.32 Made changes to prv_delete procedure for future dated
rates
10 Sep 01 kmahendr 115.33 Bug#1969043 - Imputed Income compute procedure is
added
25 Sep 01 kmahendr 115.34 Added parameter p_mode to main and added condition
'R' to look for enrollment result
26 Nov 01 dschwart/ 115.35 Bug#1646442: fixed invalid date use (was using date
BBurns benmngle run, not event date).
19 Mar 02 kmahendr 115.36 Bug#2273129 - cursor c_bpl is changed to return
row only for prvdd_val and total_pools procedure
is called to write flex credit rate if bnft_amt
is changed.
19 Mar 02 kmahendr 115.37 Added dbdrv lines.
30 Apr 02 kmahendr 115.38 Added token to message 91832.
06 May 02 kmahendr 115.39 Bug#2359835 - fix made for bug#2273129 broke for
non-flex programs - before calling total_pools
program type is checked.
08-Jun-02 pabodla 115.40 Do not select the contingent worker
assignment when assignment data is
fetched.
08-Aug-02 kmahendr 115.41 Bug#2382651 - added pgm_id to total_pools call.
19-Sep-02 mmudigon 115.42 Bug#2505008 - pass l_eff_dt instead of
p_eff_dt while calling imputed_income
19-Sep-02 ikasire 115.43 Bug 2551834 we need use nvl for l_ecr.val
while calling election_information
to compute rate changes.
28-Oct-02 kmahendr 115.44 Bug#2648512 - Effective date is modified according to the
date of election in imputed income.
11-dec-2002 hmani 115.45 NoCopy changes
21-Feb-2003 kmahendr 115.46 Bug#2776740 - added call - end_prtt_rt_val
25-Feb-2003 ikasire 115.47 Bug 2789814 fixes for future enrollment results
from the previous life events
04-Apr-2003 pbodla 115.48 Bug 2841161 : Copy DFF segments if the
benefit amount changes and new enrollment is created.
23-Apr-2003 kmahendr 115.49 New function Determine_change_in_flex added - bug#290823
28-May-2003 kmahendr 115.50 Added codes for canon fix.
04-Jun-2003 kmahendr 115.51 Added a cursor c_entr_val and not to consider rates
with enter value at enrollment.Bug#2959410
07-Aug-2003 iaksire 115.52 Bug 3044116 Added code to handle the cases like
LE after Old LE date and before old le effective
date. We need to use enrt_perd_start_dt plus 1
to avoid the issue of correction.
13-Aug-2003 kmahendr 115.53 Added codes for Cvg_mlf_cd - ERL
01-Oct-2003 mmudigon 115.54 Bug 2775742. Update rates for rt chg
process when element/input attached
to abr is changed.
26-Oct-2003 mmudigon 115.55 Bug 2775742. changed <> to = line 937
30-Oct-2003 ikasire 115.56 Bug 3192923 Override Thru date needs to be
handled to coverage and rates.
11-nov-2003 nhunur 115.57 changed '= to in' in determine_change_in_flex
20-Jan-2004 kmahendr 115.58 Bug#3378865 - added ele_entry_val_cd check for
calling election_rate_information
22-Jan-2004 kmahendr 115.59 Bug#3395033 - added codes to call total_pools
to recompute flex credits
28-Jan-2004 ikasire 115.60 Bug 3394862 When rate is enter value at
enrollment, recalc is not being processed for
payroll changes. on fp.F it computes but with
null values for the rates.
16-Feb-2004 mmudigon 115.61 Bug 3437083. Logic to determine abr
assignment changes
05-Apr-2004 kmahendr 115.62 Bug#3554751 - cursor c_ppe modified to look
for per_in_ler_stat_cd
07-Apr-2004 tjesumic 115.63 fonm parameter added
14-Apr-2004 mmudigon 115.64 Additional FONM changes
08-Jul-2004 kmahendr 115.65 Bug#3739641 - modified c_ecr cursors
27-Jul-2004 mmudigon 115.66 Bug 3797946. Logic to determine
change in extra input values
11-jan-2005 kmahendr 115.67 Bug#4113295 - the change is not compared for SAREC rates
10-feb-2005 mmudigon 115.68 Bug 4157759. Modified cursor c_prv
and added a new cursor c_prv_min_dt
17-Mar-2005 kmahendr 115.69 Bug#3856424 - Modified cursor c_pen
30-Aug-2005 kmahendr 115.70 Bug#4481319 - effective date is
assigned enrt_perd_start_date
03-Nov-2005 abparekh 115.71 Bug 4715688 - Added cases to detect coverage change
10-Nov-2005 abparekh 115.72 Bug 4723828 - Added cases to detect coverage change
09-Nov-2005 ikasire 115.73 Bug 4715657 recalc doesnot work if rate start date code
is a rule
09-Nov-2005 kmahendr 115.74 Bug#4872115 - regression of fix made in version
71 and 72
05-Jan-2006 abparekh 115.75 Bug 4895872 - Nullify all member variables of L_ENB
if C_ENB not found
09-Jan-2006 kmahendr 115.76 Bug#4938930 - added mlt_cd condition
to cursor c_enb
05-Apr-2006 abparekh 115.77 Bug 5126800 : SAAEAR case added to detect change
in benefit amount during Recalculate Run
08-sep-2006 ssarkar 115.78 Bug 5507982 - initialise variable element_changed to false
for each epe .
24-oct-2006 ikasired 115.79 Bug 5617091 - need to exclude interim enrollments from
being pickedup for rate change processing
30-Jan-2007 rgajula 115.80 Bug 5768795 - Added call to PEN_API.chk_arcs_breach in main
for each of the pgm_id's in the enrollment results.
18-jun-2007 rtagarra 115.81 Bug 6133258 - Made fix 5768795 compatible for numeric overflow error
13-Feb-2008 rtagarra 115.82 Bug 6528302 - Fixed cursor c_element_info.
22-Feb-2008 rtagarra 115.83 Bug 6840074
23-Apr-08 sallumwa 115.84 Reverted back the changes made for the Bug : 6528302
*/
----------------------------------------------------------------------------------------------------
--
g_package varchar2(80) := 'ben_determine_rate_chg';
l_update boolean;
l_update_override boolean;
l_update_change_insert boolean;
select pil.per_in_ler_id, pil.lf_evt_ocrd_dt, pil.ler_id
from ben_per_in_ler pil
where pil.person_id = p_person_id
-- added per_in_ler_id for unrestricted enhancement
and pil.per_in_ler_id = p_per_in_ler_id
and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
select epe.elig_per_elctbl_chc_id,
epe.elctbl_flag,
epe.prtt_enrt_rslt_id,
pel.acty_ref_perd_cd,
epe.pl_id,
epe.oipl_id,
epe.pil_elctbl_chc_popl_id,
epe.fonm_cvg_strt_dt,
pel.enrt_perd_id,
pel.lee_rsn_id,
pel.enrt_perd_strt_dt
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel
where epe.per_in_ler_id = l_per_in_ler_id
and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and epe.per_in_ler_id = pel.per_in_ler_id
and epe.comp_lvl_cd not in ('PLANFC', 'PLANIMP');
select epe.elig_per_elctbl_chc_id,
epe.elctbl_flag,
epe.prtt_enrt_rslt_id,
pel.acty_ref_perd_cd,
epe.pl_id,
epe.oipl_id,
epe.pil_elctbl_chc_popl_id,
epe.fonm_cvg_strt_dt,
pel.enrt_perd_id,
pel.lee_rsn_id,
pel.enrt_perd_strt_dt
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel
where epe.per_in_ler_id = l_per_in_ler_id
and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and epe.per_in_ler_id = pel.per_in_ler_id
and epe.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and (exists (select null from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and ecr.rt_mlt_cd = 'ERL') or exists (
select null from ben_enrt_rt ecr, ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and ecr.enrt_bnft_id = enb.enrt_bnft_id
and ecr.rt_mlt_cd = 'ERL'));
select epe.elig_per_elctbl_chc_id,
epe.elctbl_flag,
epe.prtt_enrt_rslt_id,
pel.acty_ref_perd_cd,
epe.pl_id,
epe.oipl_id,
epe.pil_elctbl_chc_popl_id,
epe.fonm_cvg_strt_dt,
pel.enrt_perd_id,
pel.lee_rsn_id,
pel.enrt_perd_strt_dt
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel
where epe.per_in_ler_id = l_per_in_ler_id
and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and epe.per_in_ler_id = pel.per_in_ler_id
and epe.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and (exists (select null from ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and enb.cvg_mlt_cd = 'ERL')
);
select pen.prtt_enrt_rslt_id,
pen.enrt_mthd_cd,
pen.pl_id,
pen.pgm_id,
pen.pl_typ_id,
pen.oipl_id,
pen.ler_id,
pen.enrt_cvg_strt_dt,
pen.bnft_amt,
pen.object_version_number,
pen.person_id,
pen.business_group_id,
pen.per_in_ler_id,
-- Added for bug 2841161
pen.pen_attribute_category,
pen.pen_attribute1,
pen.pen_attribute2,
pen.pen_attribute3,
pen.pen_attribute4,
pen.pen_attribute5,
pen.pen_attribute6,
pen.pen_attribute7,
pen.pen_attribute8,
pen.pen_attribute9,
pen.pen_attribute10,
pen.pen_attribute11,
pen.pen_attribute12,
pen.pen_attribute13,
pen.pen_attribute14,
pen.pen_attribute15,
pen.pen_attribute16,
pen.pen_attribute17,
pen.pen_attribute18,
pen.pen_attribute19,
pen.pen_attribute20,
pen.pen_attribute21,
pen.pen_attribute22,
pen.pen_attribute23,
pen.pen_attribute24,
pen.pen_attribute25,
pen.pen_attribute26,
pen.pen_attribute27,
pen.pen_attribute28,
pen.pen_attribute29,
pen.pen_attribute30
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = l_epe.prtt_enrt_rslt_id
and pen.person_id = p_person_id
and pen.sspndd_flag = 'N'
and ( (pen.enrt_ovridn_flag = 'N' ) OR
(pen.enrt_ovridn_flag = 'Y' and nvl(pen.enrt_ovrid_thru_dt,hr_api.g_eot) < l_effective_date ))
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.prtt_enrt_rslt_stat_cd is null
and l_effective_date -- Bug 3044116 p_effective_date
between pen.effective_start_date and pen.effective_end_date
--bug#3856424 - check for any deenrollment in future - defensive coding
and exists (select null from ben_prtt_enrt_rslt_f pen2
where pen2.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen2.enrt_cvg_thru_dt = hr_api.g_eot
and pen2.effective_end_date = hr_api.g_eot)
and not exists ( select 'x' from ben_prtt_enrt_rslt_f pen3 --Bug 5617091 exclude interim results
where pen3.rplcs_sspndd_rslt_id= pen.prtt_enrt_rslt_id
and pen3.sspndd_flag = 'Y'
and pen3.prtt_enrt_rslt_stat_cd is null
and pen3.enrt_cvg_thru_dt = hr_api.g_eot
and pen3.effective_end_date = hr_api.g_eot)
;
select enb.val,
enb.enrt_bnft_id,
enb.prtt_enrt_rslt_id,
enb.entr_val_at_enrt_flag,
enb.CVG_MLT_CD
from ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = l_epe.elig_per_elctbl_chc_id
and ( enb.prtt_enrt_rslt_id = l_epe.prtt_enrt_rslt_id OR
(enb.prtt_enrt_rslt_id is null /* Bug 4715688 */
and enb.cvg_mlt_cd not like '%RNG')--bug#4938930
);
select nvl(ecr.val,ecr.dflt_val) val,
ecr.enrt_rt_id,
nvl(ecr.ann_val,ecr.ann_dflt_val) ann_val,
ecr.acty_base_rt_id,
ecr.acty_typ_cd,
ecr.tx_typ_cd,
ecr.rt_strt_dt_cd,
ecr.rt_strt_dt,
ecr.ENTR_VAL_AT_ENRT_FLAG,
ecr.rt_mlt_cd
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
and ecr.business_group_id = p_business_group_id
-- added for canon fix
and ecr.rt_mlt_cd <> 'ERL'
and ecr.ASN_ON_ENRT_FLAG = 'Y'
UNION
select nvl(ecr.val,ecr.dflt_val) val,
ecr.enrt_rt_id,
nvl(ecr.ann_val,ecr.ann_dflt_val) ann_val,
ecr.acty_base_rt_id,
ecr.acty_typ_cd,
ecr.tx_typ_cd,
ecr.rt_strt_dt_cd,
ecr.rt_strt_dt,
ecr.ENTR_VAL_AT_ENRT_FLAG,
ecr.rt_mlt_cd
from ben_enrt_rt ecr
where ecr.enrt_bnft_id = v_enrt_bnft_id
and ecr.business_group_id = p_business_group_id
-- added for canon fix
and ecr.rt_mlt_cd <> 'ERL'
and ecr.ASN_ON_ENRT_FLAG = 'Y';
select 'Y'
from dual
where exists (select null
from ben_acty_base_rt_f abr
where abr.PARNT_ACTY_BASE_RT_ID = p_acty_base_rt_id
and abr.PARNT_CHLD_CD = 'CHLD'
and abr.entr_val_at_enrt_flag = 'Y'
and abr.ACTY_BASE_RT_STAT_CD = 'A'
and p_effective_date between
abr.effective_start_date and abr.effective_end_date)
or exists (select null
from ben_acty_base_rt_f abr1,
ben_acty_base_rt_f abr2
where abr1.acty_base_rt_id = p_acty_base_rt_id
and abr1.PARNT_CHLD_CD = 'CHLD'
and abr1.PARNT_ACTY_BASE_RT_ID = abr2.acty_base_rt_id
and abr2.entr_val_at_enrt_flag = 'Y'
and abr2.ACTY_BASE_RT_STAT_CD = 'A'
and p_effective_date between
abr1.effective_start_date and abr1.effective_end_date
and p_effective_date between
abr2.effective_start_date and abr2.effective_end_date);
select nvl(ecr.val,ecr.dflt_val) val,
ecr.enrt_rt_id,
nvl(ecr.ann_val,ecr.ann_dflt_val) ann_val,
ecr.acty_base_rt_id,
ecr.acty_typ_cd,
ecr.tx_typ_cd,
ecr.rt_strt_dt_cd,
ecr.rt_strt_dt
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
and ecr.business_group_id = p_business_group_id
-- added for canon fix
and ecr.rt_mlt_cd = 'ERL'
and ecr.ASN_ON_ENRT_FLAG = 'Y'
UNION
select nvl(ecr.val,ecr.dflt_val) val,
ecr.enrt_rt_id,
nvl(ecr.ann_val,ecr.ann_dflt_val) val,
ecr.acty_base_rt_id,
ecr.acty_typ_cd,
ecr.tx_typ_cd,
ecr.rt_strt_dt_cd,
ecr.rt_strt_dt
from ben_enrt_rt ecr
where ecr.enrt_bnft_id = v_enrt_bnft_id
and ecr.business_group_id = p_business_group_id
-- added for canon fix
and ecr.rt_mlt_cd = 'ERL'
and ecr.ASN_ON_ENRT_FLAG = 'Y';
select prv.prtt_rt_val_id,
prv.rt_val,
prv.rt_strt_dt,
prv.elctns_made_dt,
prv.acty_typ_cd,
prv.tx_typ_cd,
prv.element_entry_value_id,
prv.rt_ovridn_flag,
prv.rt_ovridn_thru_dt
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = l_pen.prtt_enrt_rslt_id
and prv.acty_base_rt_id = v_acty_base_rt_id
and ((v_rt_strt_dt is not null and
v_rt_strt_dt between prv.rt_strt_dt and prv.rt_end_dt) or
(v_rt_strt_dt is null and
prv.rt_end_dt = hr_api.g_eot))
and prv.prtt_rt_val_stat_cd is null;
select min(prv.rt_strt_dt)
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_pen_id
and prv.acty_base_rt_id = p_abr_id
and prv.prtt_rt_val_stat_cd is null;
select epr.val,
epr.enrt_prem_id,
epr.actl_prem_id
from ben_enrt_prem epr
where epr.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
and epr.business_group_id = p_business_group_id
UNION
select epr.val,
epr.enrt_prem_id,
epr.actl_prem_id
from ben_enrt_prem epr
where epr.enrt_bnft_id = v_enrt_bnft_id
and epr.business_group_id = p_business_group_id;
select ppe.prtt_prem_id,
ppe.std_prem_val,
ppe.std_prem_uom,
ppe.object_version_number
from ben_prtt_prem_f ppe,
ben_per_in_ler pil
where ppe.prtt_enrt_rslt_id = l_pen.prtt_enrt_rslt_id
and ppe.actl_prem_id = v_actl_prem_id
and ppe.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
and ppe.business_group_id = p_business_group_id
/*
CODE PRIOR TO WWBUG: 1646442
and p_effective_date between
*/
/* Start of Changes for WWBUG: 1646442 */
and p_ppe_dt_to_use between
/* End of Changes for WWBUG: 1646442 */
ppe.effective_start_date and ppe.effective_end_date;
select pay.period_type
from per_all_assignments_f asg,
pay_payrolls_f pay,
per_all_assignments_f asg2,
pay_payrolls_f pay2
where asg.person_id = cp_person_id
and asg.assignment_type <> 'C'
and asg.business_group_id = cp_business_group_id
and asg.primary_flag = 'Y'
and cp_effective_date between
asg.effective_start_date and asg.effective_end_date
and pay.payroll_id=asg.payroll_id
and pay.business_group_id = asg.business_group_id
and cp_effective_date between
pay.effective_start_date and pay.effective_end_date
and asg2.person_id = cp_person_id
and asg2.assignment_type <> 'C'
and asg2.business_group_id = cp_business_group_id
and asg2.primary_flag = 'Y'
and cp_orig_effective_date between
asg2.effective_start_date and asg2.effective_end_date
and pay2.payroll_id=asg2.payroll_id
and pay2.business_group_id = asg2.business_group_id
and cp_orig_effective_date between
pay2.effective_start_date and pay2.effective_end_date
and pay2.period_type<>pay.period_type
and asg.assignment_type = asg2.assignment_type ;
select 'x' from ben_pl_f pl
where pl.pl_id = p_pl_id
and pl.SUBJ_TO_IMPTD_INCM_TYP_CD is not null
and p_eff_dt between
pl.effective_start_date and pl.effective_end_date;
select epe.bnft_prvdr_pool_id,
epe.elig_per_elctbl_chc_id,
epe.prtt_enrt_rslt_id,
epe.business_group_id,
epe.per_in_ler_id,
ecr.enrt_rt_id,
ecr.acty_base_rt_id,
nvl(ecr.dflt_val, ecr.val) val
from ben_elig_per_elctbl_chc epe1,
ben_elig_per_elctbl_chc epe,
ben_enrt_rt ecr
where epe1.elig_per_elctbl_chc_id=l_epe.elig_per_elctbl_chc_id and
epe1.business_group_id=p_business_group_id and
epe1.pgm_id = epe.pgm_id and
epe1.per_in_ler_id = epe.per_in_ler_id and
epe.bnft_prvdr_pool_id is not null and
epe.business_group_id=p_business_group_id and
epe.pgm_id = p_pgm_id and
ecr.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id and
ecr.rt_usg_cd = 'FLXCR' and
ecr.business_group_id = p_business_group_id;
select prvdd_val
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where bpl.acty_base_rt_id = c_acty_base_rt_id
and bpl.bnft_prvdr_pool_id = c_bnft_prvdr_pool_id
and bpl.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and bpl.prvdd_val is not null
and pil.per_in_ler_id = bpl.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and bpl.business_group_id = p_business_group_id
and p_effective_date between
bpl.effective_start_date and bpl.effective_end_date ;
select pel.enrt_perd_id,pel.lee_rsn_id
from ben_pil_elctbl_chc_popl pel
,ben_elig_per_elctbl_chc epe
where pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
and epe.elig_per_elctbl_chc_id = p_elig_pe_elctbl_chc_id;
select epe.prtt_enrt_rslt_id,pel.enrt_perd_strt_dt
from ben_pil_elctbl_chc_popl pel,
ben_elig_per_elctbl_chc epe
where pel.per_in_ler_id = p_per_in_ler_id
and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
and epe.comp_lvl_cd = 'PLANFC'
and epe.pgm_id = p_pgm_id
and epe.business_group_id = p_business_group_id;
select enrt_mthd_cd
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prtt_enrt_rslt_stat_cd is null
and l_effective_date -- Bug 3044116 p_effective_date
between effective_start_date and effective_end_date;
select effective_start_date
from ben_prtt_enrt_rslt_f pen,
ben_elig_per_elctbl_chc epe
where pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
and epe.per_in_ler_id = p_per_in_ler_id
and epe.comp_lvl_cd = 'PLANIMP'
and epe.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
order by effective_start_date ;
select elk.element_type_id,
eev.input_value_id,
asg.payroll_id,
pee.element_entry_id,
pee.assignment_id,
pee.effective_end_date
from per_all_assignments_f asg,
pay_element_links_f elk,
pay_element_entries_f pee,
pay_element_entry_values_f eev
where eev.element_entry_value_id = p_element_entry_value_id
and eev.element_entry_id = pee.element_entry_id
and pee.element_link_id = elk.element_link_id
and pee.effective_start_date between elk.effective_start_date
and elk.effective_end_date
and eev.effective_start_date between pee.effective_start_date
and pee.effective_end_date
and pee.creator_type = 'F'
and pee.creator_id = p_prtt_enrt_rslt_id
and asg.assignment_id = pee.assignment_id
and pee.effective_start_date between asg.effective_start_date
and asg.effective_end_date
/*
and p_effective_date between asg.effective_start_date
and asg.effective_end_date
*/
order by pee.effective_end_date desc ;
select ele_rqd_flag,
element_type_id,
input_value_id,
ele_entry_val_cd,
input_va_calc_rl,
effective_start_date,
effective_end_date
from ben_acty_base_rt_f
where acty_base_rt_id = p_acty_base_rt_id
and p_eff_dt between effective_start_date
and effective_end_date;
p_datetrack_mode => hr_api.g_update,
p_suspend_flag => l_dummy_char,
p_effective_start_date => l_dummy_date,
p_effective_end_date => l_dummy_date,
p_object_version_number => l_pen.object_version_number,
p_prtt_enrt_interim_id => l_dummy_number,
p_business_group_id => p_business_group_id,
p_dpnt_actn_warning => l_dummy_bool,
p_bnf_actn_warning => l_dummy_bool,
p_ctfn_actn_warning => l_dummy_bool);
l_ext_inpval_tab.delete;
,p_datetrack_mode => hr_api.g_update
,p_business_group_id => p_business_group_id
,p_bnft_amt_changed => l_bnft_changed
,p_ele_changed => l_element_changed
,p_prv_rt_val => l_dummy_number
,p_prv_ann_rt_val => l_dummy_number
);
p_update => l_update,
p_update_override => l_update_override,
p_update_change_insert => l_update_change_insert);
if l_update_override then
l_ppe_datetrack_mode := hr_api.g_update_override;
elsif l_update then
l_ppe_datetrack_mode := hr_api.g_update;
ben_prtt_prem_api.update_prtt_prem
(p_prtt_prem_id => l_ppe.prtt_prem_id,
p_object_version_number => l_ppe.object_version_number,
p_std_prem_val => l_epr.val,
p_per_in_ler_id => l_pil.per_in_ler_id,
/*
CODE PRIOR TO WWBUG: 1646442
p_effective_date => l_effective_date, --9999 p_effective_date,
p_datetrack_mode => hr_api.g_update,
*/
/* Start of Changes for WWBUG: 1646442 */
p_effective_date => l_ppe_dt_to_use,
p_datetrack_mode => l_ppe_datetrack_mode,
/* End of Changes for WWBUG: 1646442 */
p_effective_start_date => l_dummy_date,
p_effective_end_date => l_dummy_date);
p_datetrack_mode => hr_api.g_update,
p_suspend_flag => l_dummy_char,
p_effective_start_date => l_dummy_date,
p_effective_end_date => l_dummy_date,
p_object_version_number => l_pen.object_version_number,
p_prtt_enrt_interim_id => l_dummy_number,
p_business_group_id => p_business_group_id,
p_dpnt_actn_warning => l_dummy_bool,
p_bnf_actn_warning => l_dummy_bool,
p_ctfn_actn_warning => l_dummy_bool);
procedure prv_delete(p_prtt_rt_val_id in number ,
p_enrt_rt_id in number,
p_rt_val in number,
p_rt_strt_dt in date,
p_business_group_id in number,
p_prtt_enrt_rslt_id in number,
p_person_id in number,
p_effective_date in date,
p_mode in varchar2 default 'NEW'
) is
--
l_object_version_number number;
select
prv.rt_val,
prv.rt_strt_dt,
prv.object_version_number
from ben_prtt_rt_val prv
where prv.prtt_rt_val_id = p_prtt_rt_val_id and
prv.business_group_id = p_business_group_id and
prv.prtt_rt_val_stat_cd is null
;
select
prv.*
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id and
prv.business_group_id = p_business_group_id and
prv.acty_base_rt_id = l_acty_base_rt_id and
prv.prtt_rt_val_stat_cd is null
order by prv.rt_strt_dt desc
;
select
prv.*
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id and
prv.rt_end_dt = (l_prv.rt_strt_dt -1) and
prv.business_group_id = p_business_group_id and
prv.acty_base_rt_id = l_acty_base_rt_id and
prv.prtt_rt_val_stat_cd is null
order by prv.rt_end_dt desc
;
select
ecr.*
from ben_enrt_rt ecr
where ecr.enrt_rt_id = p_enrt_rt_id and
ecr.business_group_id = p_business_group_id ;
select
abr.*,
py.processing_type
from ben_acty_base_rt_f abr,
pay_element_types_f py
where
abr.acty_base_rt_id = l_acty_base_rt_id
and abr.business_group_id = p_business_group_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date
and abr.element_type_id = py.element_type_id(+)
and p_effective_date between
py.effective_start_date(+) and py.effective_end_date(+);
l_delete boolean := false;
l_update_prtt boolean := false;
ben_prtt_rt_val_api.delete_prtt_rt_val
(p_prtt_rt_val_id => p_prtt_rt_val_id
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_prv.object_version_number
,p_effective_date => p_effective_date
);
update ben_enrt_rt ecr
set prtt_rt_val_id = null
where enrt_rt_id = p_enrt_rt_id ;
ben_prtt_rt_val_api.delete_prtt_rt_val
(p_prtt_rt_val_id => l_prv_future.prtt_rt_val_id
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_prv_future.object_version_number
,p_effective_date => p_effective_date
);
l_delete := true;
elsif l_delete then -- if l_prv_future.rt_val <> p_rt_val then
--
/*
update ben_enrt_rt ecr
set prtt_rt_val_id = null -- l_prv_future.prtt_rt_val_id
where enrt_rt_id = p_enrt_rt_id ;
ben_prtt_rt_val_api.update_prtt_rt_val(
p_validate => FALSE
,p_prtt_rt_val_id => l_prv_future.prtt_rt_val_id
,p_person_id => p_person_id
,p_input_value_id => l_abr.input_value_id
,p_element_type_id => l_abr.element_type_id
,p_enrt_rt_id => l_ecr.enrt_rt_id
,p_rt_strt_dt => l_prv_future.rt_strt_dt
,p_rt_end_dt => p_rt_strt_dt-1
,p_rt_typ_cd => l_prv_future.rt_typ_cd
,p_tx_typ_cd => l_prv_future.tx_typ_cd
,p_acty_typ_cd => l_abr.acty_typ_cd
,p_mlt_cd => l_prv_future.mlt_cd
,p_acty_ref_perd_cd => l_prv_future.acty_ref_perd_cd
,p_rt_val => l_prv_future.rt_val
,p_ann_rt_val => l_prv_future.ann_rt_val
,p_cmcd_rt_val => l_prv_future.cmcd_rt_val
,p_cmcd_ref_perd_cd => l_prv_future.cmcd_ref_perd_cd
,p_bnft_rt_typ_cd => l_prv_future.bnft_rt_typ_cd
,p_dsply_on_enrt_flag => l_prv_future.dsply_on_enrt_flag
,p_rt_ovridn_flag => l_prv_future.rt_ovridn_flag
,p_rt_ovridn_thru_dt => l_prv_future.rt_ovridn_thru_dt
,p_elctns_made_dt => l_prv_future.elctns_made_dt
,p_prtt_rt_val_stat_cd => l_prv_future.prtt_rt_val_stat_cd
,p_prtt_enrt_rslt_id => l_prv_future.prtt_enrt_rslt_id
,p_cvg_amt_calc_mthd_id => l_prv_future.cvg_amt_calc_mthd_id
,p_actl_prem_id => l_prv_future.actl_prem_id
,p_comp_lvl_fctr_id => l_prv_future.comp_lvl_fctr_id
,p_element_entry_value_id => l_prv_future.element_entry_value_id
,p_per_in_ler_id => l_prv_future.per_in_ler_id
,p_ended_per_in_ler_id => l_prv_future.ended_per_in_ler_id
,p_acty_base_rt_id => l_prv_future.acty_base_rt_id
,p_prtt_reimbmt_rqst_id => l_prv_future.prtt_reimbmt_rqst_id
,p_business_group_id => l_prv_future.business_group_id
,p_prv_attribute_category => l_prv_future.prv_attribute_category
,p_prv_attribute1 => l_prv_future.prv_attribute1
,p_prv_attribute2 => l_prv_future.prv_attribute2
,p_prv_attribute3 => l_prv_future.prv_attribute3
,p_prv_attribute4 => l_prv_future.prv_attribute4
,p_prv_attribute5 => l_prv_future.prv_attribute5
,p_prv_attribute6 => l_prv_future.prv_attribute6
,p_prv_attribute7 => l_prv_future.prv_attribute7
,p_prv_attribute8 => l_prv_future.prv_attribute8
,p_prv_attribute9 => l_prv_future.prv_attribute9
,p_prv_attribute10 => l_prv_future.prv_attribute10
,p_prv_attribute11 => l_prv_future.prv_attribute11
,p_prv_attribute12 => l_prv_future.prv_attribute12
,p_prv_attribute13 => l_prv_future.prv_attribute13
,p_prv_attribute14 => l_prv_future.prv_attribute14
,p_prv_attribute15 => l_prv_future.prv_attribute15
,p_prv_attribute16 => l_prv_future.prv_attribute16
,p_prv_attribute17 => l_prv_future.prv_attribute17
,p_prv_attribute18 => l_prv_future.prv_attribute18
,p_prv_attribute19 => l_prv_future.prv_attribute19
,p_prv_attribute20 => l_prv_future.prv_attribute20
,p_prv_attribute21 => l_prv_future.prv_attribute21
,p_prv_attribute22 => l_prv_future.prv_attribute22
,p_prv_attribute23 => l_prv_future.prv_attribute23
,p_prv_attribute24 => l_prv_future.prv_attribute24
,p_prv_attribute25 => l_prv_future.prv_attribute25
,p_prv_attribute26 => l_prv_future.prv_attribute26
,p_prv_attribute27 => l_prv_future.prv_attribute27
,p_prv_attribute28 => l_prv_future.prv_attribute28
,p_prv_attribute29 => l_prv_future.prv_attribute29
,p_prv_attribute30 => l_prv_future.prv_attribute30
,p_object_version_number => l_prv_future.object_version_number
,p_effective_date => p_effective_date
);
if l_delete then
--
update ben_enrt_rt ecr
set prtt_rt_val_id = null -- l_prv_future.prtt_rt_val_id
where enrt_rt_id = p_enrt_rt_id ;
end prv_delete;
select bpl.acty_base_rt_id,
bpl.bnft_prvdr_pool_id,
bpl.prvdd_val,
bpl.business_group_id,
pil.person_id
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.prvdd_val is not null
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');
select ecr.val,
ecr.elig_per_elctbl_chc_id
from ben_enrt_rt ecr
where ecr.acty_base_rt_id = p_acty_base_rt_id
and ecr.elig_per_elctbl_chc_id in
(select
epe.elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc epe, ben_BNFT_PRVDR_POOL_f bpl,
ben_acty_base_rt_f abr
where epe.BNFT_PRVDR_POOL_ID = p_bnft_prvdr_pool_id
and epe.bnft_prvdr_pool_id = bpl.bnft_prvdr_pool_id
and p_effective_date between bpl.effective_start_date and
bpl.effective_end_date
and epe.per_in_ler_id = p_per_in_ler_id
and abr.acty_base_rt_id = p_acty_base_rt_id
and (abr.pgm_id = epe.pgm_id or
abr.pl_id = epe.pl_id or
abr.plip_id = epe.plip_id or
abr.ptip_id = epe.ptip_id or
abr.OIPLIP_ID = epe.OIPLIP_ID or
abr.CMBN_PTIP_ID = epe.CMBN_PTIP_ID or
abr.CMBN_PTIP_OPT_ID = epe.CMBN_PTIP_OPT_ID or
abr.CMBN_PLIP_ID = epe.CMBN_PLIP_ID)
and p_effective_date between abr.effective_start_date and
abr.effective_end_date);