[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
This process creates or updates the participant's record with
information about plans and options elected. This process
determines the effective date of new elections. The enrollment
coverage end date for comp objects de-enrolled is in a later
function.
History
Date Who Version What?
---- --- ------- -----
20 Apr 98 jcarpent 110.0 Created
06 Jun 98 jcarpent 110.1 Added call to manage_enrt_bnft
08 Jun 98 jcarpent 110.2 added busines group id to manage_enrt
_bnft
10 Jun 98 jcarpent 115.1 changed prtt_rt_val eff date to real
eff dt
11 Jun 98 jcarpent 115.2 On comp object ch do create then
delete enrt When delete use effective
_dt-1 for correctin
15 Jun 98 jcarpent 115.3 Added p_validate flag.
When delete use ed-1 for update not
correct. Make rt_strt_dt and cvg_strt
_dt required.
18 Jun 98 jcarpent 115.4 Added business group to everything
Changed out nocopy parameters
23 Jun 98 jcarpent 115.5 Check status code for per_in_ler.
08 Jul 98 Jmohapat 115.6 added batch who columns to api
calls(ben_prtt_enrt_result_api.
create/update_enrollment,
ben_enrt_bnft_api. updateenrt_bnft
24 Jul 98 jcarpent 115.7 Fixed message removed fnd_message.get
28 Jul 98 jcarpent 115.8 Added flex fields.
22 SEP 98 GPERRY 115.9 Corrected error messages
25 SEP 98 bbulusu 115.10 Added out nocopy parameters (warnings) at
end of election_information and in
the call to ben_ prtt_enrt_rslt_api.
create_enrollment
12 Oct 98 jcarpent 115.11 model changes to prv and pen
19 Oct 98 jcarpent 115.12 Moved rate start date calculation.
22 Oct 98 jcarpent 115.13 Handle SAAEAR cvg_mlt_cd
Update csd if bnft_amt changes.
Keep old orgnl_enrt_dt if plan stays
same
26 Oct 98 jcarpent 115.14 Updated prv to non-datetracked
27 Oct 98 jcarpent 115.15 Changed election_rate_information
29 Oct 98 jcarpent 115.16 Fixed calls to ben_prtt_rt_val_api.
Don't compare old elctns made date
29 Oct 98 stee 115.17 Get rt_typ_cd and rt_mlt_cd from
enrt_rt.
31 Oct 98 bbulusu 115.18 Added per_in_ler_d to delet_enrolment
04 Nov 98 jcarpent 115.19 Call annual_to_period/period_to_anual
Pass different OVN to create_enrt
05 Nov 98 jcarpent 115.20 Use prv_id from enrt_rt not one
passed
01 Dec 98 jcarpent 115.21 Made p_ovn optional (get it if null)
10 Dec 98 stee 115.22 Fixed bnf warning.
15 Dec 98 jcarpent 115.23 Added call to Create_debit_ledger..
31 Dec 98 lmcdonal 115.24 Better debug messages.
12 Jan 98 maagrawa 115.25 Modified calls to
ben_prtt_rt_val_api. (Column
acty_base_rt_id added to
table ben_prtt_rt_val).
01 Feb 99 jcarpent 115.26 Changed calls to update prv to use
ended_per_in_ler_id.
24 Feb 99 jcarpent 115.27 Changed rate cursor in ele_rt_info
to include union to join to choice
03 Mar 99 yrathman 115.28 Added bnft_ordr_num logic
04 Mar 99 jcarpent 115.29 Don't end old enrollment if it is
the interim enrollment.
24 Mar 99 jcarpent 115.31 Always call manage_enrt_bnft.
Delete/end date rate on bnft_amt chg
29 Mar 99 thayden 115.32 Added call to ben_ext_chlg.
05 Apr 99 mhoyes 115.33 Un-datetrack per_in_ler_f changes.
06 May 99 jcarpent 115.34 Write new prv if suspended
19 May 99 lmcdonal 115.35 Overloaded election_information to chg
which save point to set and rollback.
Because create_enrollment was also
changed, changed the call.
04 Jun 99 lmcdonal 115.36 Added loading of result.comp_lvl_cd
11 Jun 99 jcarpent 115.37 Fixed messages.
09 Jul 99 jcarpent 115.38 Added checks for backed out nocopy pil
20 Jul 99 jcarpent 115.39 New result for bnft amt change.
12 Aug 99 gperry 115.40 Added ben_env_object call for cases
when the process is being called
from forms. This makes all the
caching work correctly.
12 Aug 99 GPERRY 115.41 Backported 115.38 to add environment
package call.
24 Aug 99 GPERRY 115.42 Version 115.40 brought to top.
30 Aug 99 shdas 115.43 Added ordr_num columns to create_enrollment.
14 Sep 99 shdas 115.44 changed election_information to add bnft_val
29 Sep 99 jcarpent 115.45 If pen_id is null get from choice.
12 Oct 99 shdas 115.46 changed election information.
12-Nov-1999 jcarpent 115.47 Added enrt_bnft/choice globals
12-Nov-1999 jcarpent 115.48 Null out nocopy globals after calls.
12-Nov-1999 lmcdonal 115.49 Better debugging messages
17-Nov-1999 pbodla 115.50 l_acty_base_rt_id as passed to
ben_determine_date.rate_and_coverage_dates
,ben_determine_date.main
which is used as context for rules.
19-Nov-1999 pbodla 115.51 elig_per_elctbl_chc_id passed to
benutils.formula.
29-Nov-1999 jcarpent 115.52 - Write new prv most of the time in
order to fix element entries.
bug 3556
- Write new enrollment result if bnft
amount changed, put back in because
removed in version 115.46.
- Added lee_rsn/enrt_perd to del_enrt.
14-Dec-1999 jcarpent 115.53 - Removed 'STRTD' restriction on pil.
16-Dec-1999 jcarpent 115.54 - Recompute cvg date if bnft amt chg
03-Jan-2000 lmcdonal 115.55 Bug 1121022. Do not re-calc rt-strt-dt
if a prtt-rt-val row exists.
20-Jan-2000 maagrawa 115.56 Pass payroll_id to ben_distribute_rates
21-Jan-2000 lmcdonal 115.57 If the rate is mult-of-cvg and cvg
can be entered at enrt, re-calc rate.
Bug 1118016.
25-Jan-2000 maagrawa 115.58 Pass per_in_ler_id to
create_debit_ledger_entry.
27-Jan-2000 thayden 115.59 New parameters for change event log.
02-Feb-2000 jcarpent 115.60 Remove l_effective_date adjustment.
24-Feb-2000 maagrawa 115.61 Re-open the rate only if the new
rate start date is before the old
rate end date.
26-Feb-2000 maagrawa 115.62 Added two parameters dpnt_actn_warning
and bnf_actn_warning to the procedure
update_enrollment.(1211553)
15-Mar-2000 shdas 115.63 Added parameters p_old_pl_id,p_old_oipl_id,
p_old_bnft_amt to log_benefit_chg(create portion)
for extract( bug 1187479).
20-Mar-2000 shdas 115.64 call create_debit_ledger_entry if result
is not suspndd(1217192).
23-Mar-2000 lmcdonal 115.65 Bug 1247110 - When calling update_
enrollment, pass eot as cvg_thru_dt.
30-Mar-2000 maagrawa 115.66 Re-use the enrollment result, if it
exists, when the choice is marked as
currently enrolled. (4875).
05-Apr-00 lmcdonal 115.67 Bug 1253007. Using globals for epe, pil,
pel, pen, enb, asg data for performance.
06-Apr-2000 mmogel 115.68 Added tokens to message calls to make
the messages more meaningful
07-Apr-00 lmcdonal 115.69 Call 'clear_enb' procedure.
13-Apr-00 jcarpent 115.70 Call clear_enb cache procedure at end
and refetch after pen_api is called
to handle recursive calls.
13-Apr-00 lmcdonal 115.71 Change globals to locals
04-May-00 shdas 115.72 added parameters p_use_balance_flag
,p_enrt_rt_id to period_to_annual and
changed cmplt_yr_flag to "N"(5043)
05-may-00 jcarpent 115.73 If form passes in wrong result_id,
(will somtimes pass in default/not
currently enrolled) use value from
epe. (5073)
19-may-00 shdas 115.74 Always calculate period_to_annual.
22-May-00 lmcdonal 115.75 update enb globals right before call
to manage_enrt_bnft.
28-Jun-00 shdas 115.76 Alwayss calculate period to annual with
complt yr flag = yes.Calculate communicated
based on periodic value.
28-Jun-00 jcarpent 115.77 Bug 4936: Use new cvg_strt_dt if sched
enrt and plan is for 125 or 129
regulation.
19-Jul-00 rchase 115.78 Bug 5353: bug 5353 - iterim cvg not selecting
previous cvg if previous cvg exists in same
pl or pl_typ
20-jul-00 kmahendr 115.79 Bug5369 - Not able to save if the same plan is selected
in a new life event after electing a different plan
Result ID is assigned only when form is not sending any
result in election_information procedure
15-Aug-00 maagrawa 115.80 Added procedure election_information_w
(Wrapper for self-service).
07-Sep-00 maagrawa 115.81 Modified exception handling for
self-service wrapper.
09-Oct-00 maagrawa 115.82 Added checks for min/max benefit
amounts in self-service wrapper.
(1417250).
06-Nov-00 tilak 115.83 bug 1480407 when entr at enrl and calculation
cales the rate calculation for enterd value
16-Nov-00 jcarpent 115.84 Bug 1495632. If no benefit amounts then
was always creating a new pen.
14-Dec-00 maagrawa 115.85 Overloaded the self-service wrapper.
15-dec-00 tilak 115.86 bug:1527086 funcation added to calculate the
erlst_deenrt_dt , this function called wheenever the coverage
date calcualted and the cvg date differ from cvg dt of ele_chc
02-Jan-01 Ikasire 115.87 Bug 1543438 Standart rate entered at enrollment is not
saving inputed rate amount. Added rt_mlt_cd <> 'FLFX' for
the ben_determine_rate.main calling if condition in
election_rate_information procedure.
05-Jan-01 maagrawa 115.88 Added parameters p_enrt_cvg_strt_dt,
p_enrt_cvg_thru_dt for Individual
Compensation to allow user to
enter the coverage start date and
coverage through date when the codes
say they are enterable (ENTRBL).
08-Jan-01 maagrawa 115.89 Set the local variable
l_prtt_enrt_rslt_id to
l_global_epe_rec.epe.prtt_enrt_rslt_id
15-Jan-01 maagrawa 115.90 Modified the self-service wrapper
to handle multiple rates.
16-Jan-01 mhoyes 115.91 - Added calculate only mode parameter
to election_rate_information.
24-Jan-01 tilak 115.92 parent rate calucaltion is fixed
bug : 1555624
09-feb-01 ikasire 115.93 bug 1584238 and 1627373 remove the edit
checking rate start date always to
be after the coverage start date
09-feb-01 ikasire 115.94 correct the version number
23-Feb-01 maagrawa 115.95 Modified the error messages 92394,
92395 for self-service.
06-mar-01 ikasire 115.96 bug 1650517 remove the
p_complete_year_flag => 'Y' parameter
for SAREC condition for cmcd_val
09-Mar-01 maagrawa 115.97 Added rt_strt_dt and rt_end_dt
parameters to pass rate dates when
they are enterable.
Added support for rt_strt_dt_cd of
ENTRBL(Enterable) and rt_end_dt_cd of
WAENT(1 Prior or Enterable).
09-Mar-01 maagrawa 115.98 Create/Update the rate whenever the
per_in_ler_id changes.
Added check that the rate start date
should be less than or equal to rate
end date in election_rate_info...
17-May-01 maagrawa 115.99 Removed use of ben_comp_object and
replaced with ben_cobj_cache.
Modified calls to ben_global_enrt.
29-May-01 kmahendr 115.100 Modified cursor c_prv2 Bug#1771887
18-Jun-01 kmahendr 115.101 Bug#1830930-rate end date code is enterable
and rate start dt is not passed thro PUI then
end date is arrived as rate start dt - 1
20-Jun-01 ikasire 115.102 bug 1840961 added p_enrt_cvg_strt_dt and
p_enrt_cvg_thru_dt to the election_information
procedure call.
20-Jun-01 ikasire 115.104 this file is 115.102 after backporting version
115.103 for bug 1840961
25-Jun-01 kmahendr 115.105 The else condition in if code is WAENT is corrected
01-Jul-01 kmahendr 115.106 Unrestricted changes
23-Jul-01 kmahendr 115.107 Bug#1807450 - Coverage start date is not computed
if there is any waiting period attached
23-Jul-01 ikasire 115.108 Bug#1888085 fixed the issue of not calculating the
actual amount for 'Percent of' and
use_calc_acty_bs_rt_flag is checked
24-Jul-01 mmorishi 115.109 Election_information_w: Added rt_strt_dt_cd
and person_id parms. Added call to prv_delete.
06-Aug-01 ikasire 115.110 Bug1913254 added a new cursor to handle the cases
for SAAEAR coverage code and the rate is not
entered at enrollment.
09-Aug-01 kmahendr 115.111 Bug#1890996 - Added cursor c_abr to check non recur-
ring rate and not to call update_prtt_rt_val to end
previous rate. Assign rate start date to rate end
date if the rate is non-recurring
17-Aug-01 maagrawa 115.112 Added parameter p_rt_update_mode
to election_information_w.
28-Aug-01 kmahendr 115.113 Added condition to check necessity to call update
prtt_rt_val
29-aug-01 tilak 115.114 bug:1949361 jurisdiction code is
derived inside benutils.formula.
30-aug-01 kmahendr 115.115 Already ended recurring rate is extended in the
subsequent rate - added condition to check old
rate end date
31-aug-01 pbodla 115.117 Version 116 is actually version 111 with changes
in version 114.
25-Oct-01 kmahendr 115.118 Bug#2055961-codes starting with 1 prior handled
before updating prtt_rt_val
5-Dec-01 dschwart 115.119 - Bug#2141172: changed call to ben_determine
_date.rate_and_coverage_dates to use life
event date.
18-dec-01 tjesumic 115.120 - cwb changes
20-dec-01 ikasire 115.121 added dbdrv lines
01-feb-02 ikasire 115.122 Bug 2172036 populate assignment_id in the pen records
the one available in epe record
12-Feb-02 ikasire 115.123 Bug 2212194 If there exist multiple rates while using
SAAREC code for Coverage Calculation.
12-Feb-02 ikasire 115.125 Bug 2212194 This is version 115.23 since we gave a
backport to ADS in 115.124
13-Feb-02 ikasire 115.126 Bug 2223694 When Rate code SAREC and the Coverage is
not enter value at enrollment, defined amount and
element entries not calculated.
26-Feb-02 rpillay 115.127 Bug 2093830 Changed from p_effective_date
to lf_evt_ocrd_dt in c_enrt_rt cursor
27-Feb-02 tjesumic 115.128 bug : 1794303 fixed by passing parameter
p_element_type_id to annual_to_period
04-Mar-02 kmahendr 115.129 bug#2048236 - added cursors - c_element_type and
if condition before update_prtt_rt_val -
removed changes made in 128 per Tilak.
14-Mar-02 rpillay 115.130 UTF8 Changes Bug 2254683
23-May-02 kmahendr 115.131 Changed logic for communicated value
115.132 No changes
04-Jun-02 kmahendr 115.133 Bug#2398448 and #2392732 - enrt_id is passed instead
of electable choice id for computing communicated
value.
08-Jun-02 pabodla 115.134 Do not select the contingent worker
assignment when assignment data is
fetched.
20-Jun-02 ikasire 115.135 Bug 2407041 wrong communication amount
when payroll changes.
06-Jun-02 ikasire 115.136 Bug 2483991 and Interim Ehancements.
07-Aug-02 ikasire 115.136 Bug 2483991 wrong datetrack mode was passed
to delete enrollment if the user replaces the
defaulted enrollment with the some other
compensation object.
08-Aug-02 ikasire 115.137 Bug 2502633 Interim enhancements.This needs to
go with the bensuenr.pkb/pkh as we are using a
package global variable here.
29-Aug-02 kmahendr 115.138 Bug#2207956 -sec 125 or 129 references removed and
codes added for starting a new result.
05-Sep-02 ikasire 115.140 Bug 2547005 and 2543071 for interim changes
28-Sep-02 ikasire 115.141 Bug 2600087 fixes for deenrolled result
28-Sep-02 ikasire 115.141 Bug 2600087 fixed error making infinit loop
10-Oct-02 shdas 115.143 Changed election_information_w for multirates
11-Oct-02 vsethi 115.144 Rates Sequence no enhancements. Modified to cater
to new column ord_num on ben_acty_base_rt_f
15-Oct-02 ikasire 115.145 Bug 2627078 fixes
28-Nov-02 lakrish 115.146 Bug 2499754, set tokens for error messages
BEN_91711_ENRT_RSLT_NOT_FND and
BEN_91453_CVG_STRT_DT_NOT_FOUN
02-Dec-02 kmullapu 115.147 out nocopy param added to election_information_w
13-Dec-02 ikasire 115.148 Bug 2675486 FSA Dont recmpute the fsa if
there is no change in the annual amount
within the plan year in the subsequent
life events
27-Dec-02 ikasire 115.149 Bug 2677804 override thru date changes
02-jan-03 hmani 115.149 Bug 2714383 - Passed pen_attributes
after checking whether its equalto hr_api.g_varchar2
Created a new function called 'decd_attribute'
09-jan-03 kmahendr 115.151 Bug#2734491 - Rates with child code is treated as
parent for annual target.
23-Jan-03 ikasire 115.152 Bug#2149438 Used the overloaded annual_to_period
procedure to determine the defined amount for
FSA calculations.
24-Jan-03 ikasire 115.153 Added nocopy changes
13-Feb-03 kmahendr 115.154 Added a parameter to call -acty_base_rt.main
13-feb-03 hnarayan 115.155 hr_utility.set_location - 'if g_debug' changes
06-Mar-03 ikasire 115.156 Bug2833116 rounding issue for FSA - annual rates
12-May-03 ikasire 115.157 Bug 2957028 nocopy bug fix for call to
ben_determine_activity_base_rt.main procedure
22-May-03 kmahendr 115.158 Fix for new rt mlt cd - ERL
29-May-03 ikasire 115.159 Bug 2976103 for nonrecurring element entries
22-Jul-03 tjesumic 115.160 rate start dt is passes as param to rate updated
when the rate strt dt is enterable
22-Jul-03 ikasire 115.161 brought forward 115.159 version.
DONT USE 115.160 -- BUG 3053267
13-Aug-03 kmahendr 115.162 Fix for new cvg_mlt_cd - ERL
26-Sep-03 kmahendr 115.163 Fix for new rt strt dt codes.
08-Oct-03 lakrish 115.164 Bug 3181158, for Enterable coverages
raise min-max error properly even if
default value is not defined for
Coverage.
15-Oct-03 mmudigon 115.165 Bug 2775742. Update rates for rt chg
process when element/input attached
to abr is changed.
30-Oct-03 tjesumic 115.165 # 2982606 new procedure backout_future_coverage added to
115.166 backout the future dated coverage and the plan in not continued
The backout caled for result level backed out
related changes in benbolfe , bendenrr ,benleclr
14-Nov-03 ikasire 115.167 Bug 3253180 Get the right l_rt_val from the
annual value when p_rt_val is null for
l_enrt_rt.entr_ann_val_flag = 'Y'
also - if the same plan yr period if there is no change
in the annual contribution dont recompute the rates.
18-Nov-03 kmahendr 115.169 Bug#3260564 - added cursor c_future_rates to delete future
dated prtt_rt_val
20-Nov-03 mmudigon 115.170 Bug#3250360. Changes to
election_rate_information. cursors
c_prtt_rt_val_1 and c_prtt_rt_val_2
to pick the correct old prv
21-nov-03 nhunur 115.171 setting the flags to 'N' if the cursor
c_prtt_rt_val_2 does not return any rows.
25-nov-03 tjesumic 115.172 hr_utility.debug_enabled added to all public procedure
16-Jan-04 kmahendr 115.173 Bug#3364910- added payroll change condition for
fSA plans
20-Jan-04 kmahendr 115.174 Bug#3378865 - added cursor c_abr to check for
ele_entry_val_cd change
21-Jan-04 mmudigon 115.175 Bug#3378865 - modified data type for
variable l_ele_entry_val_cd
03-Feb-04 kmahendr 115.176 Bug#3400822 - the subquery modified with effective_end_date condition to return only one
row - cursor c_enrt_rslt
16-Feb-04 mmudigon 115.177 Bug 3437083. Logic to determine abr
assignment changes
24-Feb-04 stee 115.178 Bug 3457483. Check the assignment to use code
in activity base rate when selecting the
assignment.
08-mar-04 hmani 115.179 Bug 3488286 - Added p_lf_evt_ocrd_date parameter to
BEN_DETERMINE_ACTIVITY_BASE_RT call
19-Mar-04 ikasire 115.180 Added new procedure call for rate_periodization_rl
23-Mar-04 ikasire 115.181 GSCC error
05-Apr-04 bmanyam 115.182 Bug: 3547233. Copied annual value to l_ann_rt_val variable
for prtt_enrt_rt record for 'Set Annual Value to Coverage'
calculation method.
09-Apr-04 kmahendr 115.183 Bug#3540351 - rt_val and cmcd_val assigned
value 0 if the value is negative.
26-Apr-04 kmahendr 115.185 Bug#3510633 - person_id added to annual_to_period
function
07-jun-04 nhunur 115.186 bug 3602579 - original enrt date fix for waive oipls
08-jun-04 mmudigon 115.187 FONM
02-jul-04 rpgupt 115.188 3733745 - Do not delete future enrts. if it is an
interim.
15-Jul-04 kmahendr 115.189 Bug#3702090 - added enrt_mthd_cd condition while creating
new result - new flex plan enrollment is created when
override flex rate"
27-Jul-04 mmudigon 115.190 Bug 3797946. Logic to determine
change in extra input values
02-Aug-04 ikasire 115.191 Bug 3804813 to recompute the rates if
l_enrt_rt.entr_bnft_val_flag = 'Y'
23-Aug-04 mmudigon 115.192 CFW. Added p_act_item_flag
2534391 :NEED TO LEAVE ACTION ITEMS
09-sep-04 mmudigon 115.193 CFW. Continued
09-sep-04 mmudigon 115.194 CFW. p_act_item_flag no longer needed
20-Sep-04 ikasire 115.195 Bug 3787832 backout only the future results
of past life events.
30-sep-04 mmudigon 115.196 Bug 3854378. In proc
election_rate_information, old prv is
re-opened only when it is necessary
11-Oct-04 tjesumic 115.197 future backout only for a pgm, pgm_id added in validation
20-nov-04 nhunur 115.198 4020061 - bnft_val should be null if enrt_bnft_id is passed as null.
23-Nov-04 kmahendr 115.199 start_date passed for communicate val cal for fsa
to handle new rate codes
30-Nov-04 mmudigon 115.200 Bug 4018874. Changes to cursor
c_fut_pen in backout_future_coverage
01-Dec-04 ikasire 115.201 Bug 3988565 date type changed for a parameter for SSBEN
12-Dec-04 vvprabhu 115.202 Bug 3980063. SSBEN Trace enhnacement. Changes to exception
handling in wrapper packages, addition of debug statements
and logic to enable trace based on profile value.
22-Dec-04 maagrawa 115.203 Added more parms to election_information_w
to have both procedures in sync.
23-Dec-04 tjesumic 115.204 new param p_prtt_enrt_rslt_id added backout_future_coverage
p_prtt_enrt_rslt_id nullified if the rslt is backedout
28-Dec-04 kmahendr 115.205 Bug#4078828 - element_type and input_value fetched
based on rate start date in case of start date code
based on election
29-Dec-04 tjesumic 115.206 continuation of 115.204 # 3945471 new cursor c_csr added
to validate the future cvrd results deleted
30-dec-2004 nhunur 115.207 4031733 - No need to open cursor c_state.
04-Jan-04 tjesumic 115.208 continuation of 115.204 # 3945471
04-Jan-04 tjesumic 115.209 continuation of 115.204 future backoit skipped for cwb,abs,comp ler
11-Jan-04 ikasire 115.210 CF Interim Suspended BUG 4064635
07-Feb-05 tjesumic 115.211 backout_future_result is removed, future cvg taken care
in delete_enrollment # 4118315
09-Feb-05 ikasire 115.212 Bug 4173505 Need to sspndd_flag to update_enrollment otherwise
suspended enrollment will be carried forward with suspend flag
checked even it is not required.
10-Feb-05 mmudigon 115.213 Bug 4157759. Added proc
handle_overlap_rates()
09-Mar-05 vvprabhu 115.214 Bug 4216475 changes to election_information_w to
validate coverage amount when default is outside
coverage range
13-Apr-05 ikasire 115.215 Added a new parameter to manage_enrt_bnft procedure
26-May-05 vborkar 115.216 Bug 4387247 : In wrapper method exception
handlers changes made to avoid null errors
on SS pages
01-Jun-05 vborkar 115.217 Bug 4387247 : Modifications to take care of
application exceptions.
17-Jun-05 vborkar 115.218 Bug 4436578 : In SS wrapper app exception
handler added generic(default) code.
12-jul-05 ssarkar 115.219 BUG 4203714 : Modified c_comp_obj_name for proc handle_overlap_rates.
29-jul-05 tjesumic 115.230 BUG 4510798 : fonm variable intialised on election information
11-aug-05 ssarkar 115.221 BUG 4203714 : Rtrim to chop off the trailing spaces
17-aug-05 kmahendr 115.222 Bug#4549089 - new result is created only
if the per_in_ler is different in sspd enroll
25-Aug-05 ikasire 115.223 Bug 4568911 fix
08-Sep-05 kmahendr 115.224 Bug#4555320 - new result is not created
if the per in ler ids are same
27-Sep-05 vborkar 115.225 Bug 4543745 : Avoid election-information call for
electable choices whose existing enrollments
cannot be changed in current LE.
03-Nov-05 rbingi 115.226 4710188: removed case entr_bnft_val='Y' in if condition of
call to Std rates proc. will be called only for Post-Enrollment
03-Nov-05 rbingi 115.227 reverted previous version changes
08-Nov-05 rbingi 115.228 4710188: Calling acty_base_rt proc for ERL case only if
rt value is not enterable
02-dec-05 ssarkar 115.229 4775760 : Creat prtt_rt_val not be to called if sspndd_flag = 'Y' and prtt_rt_val is already present.
12-dec-05 ssarkar 115.230 4775760,4871284 : rollback fix of 115.229 and commented the restrcition of Unrestricted.
11-jan-06 ikasire 115.231 4938498 fixed c_prv2 cursor to exclude voided and backedout
records.
16-Jan-06 abparekh 115.232 Bug 4954541 : If bnft_amt is nullified, create new enrollment
10-Feb-06 kmahendr 115.233 Bug#5032364 - before delete prtt row, it
is captured in ben_le_clsn_n_rstr table
for backout purpose
22-Mar-06 rtagarra 115.234 Bug#5099296 : Changed the sspndd_flag parameter passed to update_enrollment.
23-Mar-06 kmahendr 115.235 Bug#5105122 - coverage date is recomputed
before create_enrollment
16-Jun-06 rbingi 115.236 Bug#5259005-Moved code for calulating ann_rt_val, rt_val based in
entr_val flag to proc election_rate_information from pld.
09-Jul-06 rbingi 115.237 Bug#5303167-corrected if condition in proc elec_rt_info
17-jul-06 ssarkar 115.238 Bug 5375381 - ben_determine_activity_base_rt being called for interim.
03-aug-06 ssarkar 115.239 bug 5417132 - passing comp_lvl_cd to update_enrollment call
27-Sep-06 ikasired 115.240 Bug 5502202 fix for Interim Rule. We need to recompute the rates when
interim rule returs amounts.
10-Oct-06 abparekh 115.241 Bug 5572484 : Take backup in BEN_LE_CLSN_N_RSTR only if the correction
is due to a new life event.
11-Oct-06 ssarkar 115.242 Bug 5555269 - commented out fix of bug 5032364 as well 5572484
12-Oct-06 gsehgal 115.243 Bug 5584813 - move the code to calculate rates to procedure
calc_rt_ann_rt_vals and also call this when cvg_mlt_cd='SAAEAR'
17-Oct-06 abparekh 115.244 Bug 5600697 - In procedure ELECTION_INFORMATION
Set P_PRTT_ENRT_RSLT_ID only if
PRTT_ENRT_RSLT_STAT_CD is NULL
20-Oct-06 bmanyam 115.245 5612091 cursor c_enrt_rt corrected.
join ecv with enb was incomplete. OR-ed with epe
26-Oct-06 ssarkar 115.246 5621049 : dont compare the cvg amt of option2 with option1
10-Nov-06 ssarkar 115.247 bug 5653168 : resetting g_use_new_result for any exception raised in election_information
18-nov-06 ssarkar 115.248 Bug 5717428 - passed life event occurred date to rate_and_coverage_dates
24-jan-07 stee 115.249 Bug 5766477 - Restore dependent
coverage when future enrollments
are deleted.
05-apr-07 ssarkar 115.250 bug 5942441 - overiding info to be passed to subsequent prv
08-may-07 gsehgal 115.251 bug 6022111 - wrong prv record was being fetched
for parent record in cursor c_prv2
11-May-07 ikasired 115.252 Bug 5985777. When action items are completed in future
we need to extend the suspended enrollments with future
delete store the future completed action items in
backup table for future reinstate in the event
the current life event backout.
25-May-07 bmanyam 115.253 6057157 : -- same as above--
Reopen future interim PRV records. This was
missed out in the prev version.
21-Jun-07 swjain 115.254 6067740 - Updated procedure election_rate_information for SAREC
cases.
26-Jun-07 sshetty 115.255 6132571--changed logic to detect the
payroll id changes by calling
get_abr_assignment for the old
rate start date
24-Aug-07 gsehgal 115.256 bug 6337803 - dont delete the enrollment if enrollment to be suspended has same
comp object as interim
05-Oct-07 swjain 115.258 Removing the fix in 6067740
23-Oct-07 rgajula 115.259 Bug 6503304 : Passed lf_evt_ocrd_dt instead of p_effective_date to procedures get_ptip_dets, get_plip_dets, get_oipl_dets,get_pl_dets
to get the details as of life event occured date (open life event case)
28-aug-08 bachakra 115.260 Bug 7206471: adjust the coverage and rates when event date life event
is processed after fonm life event in the same month.
*/
-------------------------------------------------------------------------------
--
-- Package Variables
--
g_debug boolean := hr_utility.debug_enabled;
select prv.prtt_rt_val_id,
prv.acty_base_rt_id,
prv.acty_ref_perd_cd,
prv.rt_strt_dt,
prv.object_version_number
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and ((prv.rt_strt_dt > p_rt_strt_dt) or -- old prv in future
(p_unrestricted = 'N' and -- old prv on same date
prv.per_in_ler_id <> p_per_in_ler_id and
prv.rt_strt_dt = p_rt_strt_dt))
and prv.prtt_rt_val_stat_cd is null
and (prv.acty_base_rt_id = p_acty_base_rt_id
or exists
(select 'x'
from per_all_assignments_f asg,
pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where pel.element_type_id = p_element_type_id
and pee.element_entry_id = pev.element_entry_id
and pev.element_entry_value_id = prv.element_entry_value_id
and pee.element_link_id = pel.element_link_id
and pee.assignment_id = asg.assignment_id
and pee.creator_type ='F'
and pee.creator_id = p_prtt_enrt_rslt_id
and asg.person_id = p_person_id)
)
order by prv.rt_strt_dt desc;
select prtt_rt_val_id,
acty_base_rt_id,
rt_strt_dt,
rt_end_dt,
object_version_number
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_rt_strt_dt between prv.rt_strt_dt
and prv.rt_end_dt
and prv.acty_base_rt_id = l_future_prv.acty_base_rt_id
and prv.prtt_rt_val_stat_cd is null;
select elk.element_type_id
from pay_element_entry_values_f elv,
pay_element_entries_f ele,
pay_element_links_f elk
where elv.element_entry_value_id = p_element_entry_value_id
and elv.element_entry_id = ele.element_entry_id
and elv.effective_start_date between ele.effective_start_date
and ele.effective_end_date
and ele.element_link_id = elk.element_link_id
and ele.effective_start_date between elk.effective_start_date
and elk.effective_end_date;
select rtrim(substr(pln.name||' '||opt.name,1,60)) -- 4203714 rtrim to chop off the trailing spaces
from ben_prtt_enrt_rslt_f pen,
ben_pl_f pln,
ben_oipl_f oipl,
ben_opt_f opt
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.pl_id = pln.pl_id
and pen.oipl_id = oipl.oipl_id(+)
and nvl(oipl.opt_id,0) = opt.opt_id (+)
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
and p_effective_date between pln.effective_start_date
and pln.effective_end_date
and p_effective_date between nvl(oipl.effective_start_date,p_effective_date) --start 4203714
and nvl(oipl.effective_end_date,p_effective_date)
and p_effective_date between nvl(opt.effective_start_date,p_effective_date)
and nvl(opt.effective_end_date,p_effective_date); -- end 4203714
ben_prtt_rt_val_api.delete_prtt_rt_val
(p_prtt_rt_val_id => l_future_prv.prtt_rt_val_id
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_future_prv.object_version_number
,p_effective_date => l_future_prv.rt_strt_dt);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_validate => false,
p_person_id => p_person_id,
p_business_group_id => p_business_group_id,
p_prtt_rt_val_id => l_future_prv.prtt_rt_val_id,
p_rt_end_dt => l_future_prv.rt_strt_dt -1,
p_prtt_rt_val_stat_cd => 'BCKDT',
p_ended_per_in_ler_id => p_per_in_ler_id,
p_object_version_number => l_future_prv.object_version_number,
p_effective_date => l_future_prv.rt_strt_dt);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_prtt_rt_val_id => l_current_prv.prtt_rt_val_id
,p_person_id => p_person_id
,p_rt_end_dt => hr_api.g_eot
,p_business_group_id => p_business_group_id
,p_object_version_number => l_current_prv.object_version_number
,p_effective_date => p_effective_date);
select abr.acty_base_rt_id,
abr.ele_rqd_flag,
abr.element_type_id,
abr.input_value_id,
abr.rcrrg_cd,
abr.use_calc_Acty_bs_rt_flag,
abr.entr_val_At_enrt_flag ,
abr.rt_typ_cd abr_typ_cd,
abr.val abr_val,
abr.rndg_cd,
abr.rndg_rl,
abr.ele_entry_val_cd,
abr.input_va_calc_rl,
abr.effective_start_date abr_esd,
abr.effective_end_date abr_eed,
er.rt_typ_cd,
er.tx_typ_cd,
er.acty_typ_cd,
er.rt_mlt_cd,
er.rt_strt_dt,
er.rt_strt_dt_cd,
er.rt_strt_dt_rl,
er.bnft_rt_typ_cd,
er.cmcd_acty_ref_perd_cd,
er.val,
er.ann_val,
er.cmcd_val,
er.dsply_on_enrt_flag,
er.cvg_amt_calc_mthd_id,
er.actl_prem_id,
er.comp_lvl_fctr_id,
er.business_group_id,
nvl(eb.elig_per_elctbl_chc_id,
er.elig_per_elctbl_chc_id) elig_per_elctbl_chc_id,
er.entr_ann_val_flag,
er.prtt_rt_val_id,
er.decr_bnft_prvdr_pool_id,
nvl(eb.enrt_bnft_id,0) enrt_bnft_id,
nvl(eb.entr_val_at_enrt_flag,'N') entr_bnft_val_flag,
er.pp_in_yr_used_num,
er.ordr_num,
eb.cvg_mlt_cd,
abr.rate_periodization_rl, --GEVITY
nvl(eb.mx_wo_ctfn_flag,'N') interim_flag --BUG 5502202
from ben_enrt_rt er,
ben_enrt_bnft eb,
ben_acty_base_rt_f abr
where er.enrt_rt_id=p_enrt_rt_id
and eb.enrt_bnft_id(+)=er.enrt_bnft_id
and er.acty_base_rt_id=abr.acty_base_rt_id
and ( (ben_manage_life_events.fonm = 'Y' and
nvl(nvl(p_rt_strt_dt,er.rt_strt_dt),l_effective_date) between
abr.effective_start_date and abr.effective_end_date) or
(nvl(ben_manage_life_events.fonm,'N') = 'N'
and l_effective_date between abr.effective_start_date
and abr.effective_end_date) ) ;
select prv.rt_typ_cd,
prv.tx_typ_cd,
prv.acty_typ_cd,
prv.mlt_cd,
prv.acty_ref_perd_cd,
prv.rt_val,
prv.prtt_enrt_rslt_id,
prv.business_group_id,
prv.object_version_number,
prv.rt_strt_dt,
prv.ann_rt_val,
prv.bnft_rt_typ_cd,
prv.cmcd_ref_perd_cd,
prv.cmcd_rt_val,
prv.dsply_on_enrt_flag,
prv.cvg_amt_calc_mthd_id,
prv.actl_prem_id,
prv.comp_lvl_fctr_id,
prv.rt_end_dt,
prv.per_in_ler_id,
prv.element_entry_value_id,
prv.elctns_made_dt,
prv.rt_ovridn_flag,
prv.rt_ovridn_thru_dt
from ben_prtt_rt_val prv
where prtt_rt_val_id=l_enrt_rt.prtt_rt_val_id and
p_business_group_id=business_group_id;
select prv.prtt_rt_val_id,
prv.rt_typ_cd,
prv.tx_typ_cd,
prv.acty_typ_cd,
prv.mlt_cd,
prv.acty_ref_perd_cd,
prv.rt_val,
prv.prtt_enrt_rslt_id,
prv.business_group_id,
prv.object_version_number,
prv.rt_strt_dt,
prv.ann_rt_val,
prv.bnft_rt_typ_cd,
prv.cmcd_ref_perd_cd,
prv.cmcd_rt_val,
prv.dsply_on_enrt_flag,
prv.cvg_amt_calc_mthd_id,
prv.actl_prem_id,
prv.comp_lvl_fctr_id,
prv.rt_end_dt,
prv.per_in_ler_id,
prv.element_entry_value_id,
prv.elctns_made_dt,
prv.rt_ovridn_flag,
prv.rt_ovridn_thru_dt
from ben_prtt_rt_val prv
where prv.acty_base_rt_id = l_enrt_rt.acty_base_rt_id
and prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.prtt_rt_val_stat_cd is null
and l_enrt_rt.rt_strt_dt between prv.rt_strt_dt
and prv.rt_end_dt
and p_business_group_id=prv.business_group_id;
select abr2.entr_val_at_enrt_flag,
abr2.use_calc_acty_bs_rt_flag,
abr2.acty_base_rt_id,
abr2.rt_mlt_cd,
abr2.entr_ann_val_flag
from ben_acty_base_rt_f abr,
ben_acty_base_rt_f abr2
where abr.acty_base_rt_id = c_acty_base_rt_id
and abr2.acty_base_rt_id = abr.parnt_acty_base_rt_id
and abr2.parnt_chld_cd = 'PARNT'
and c_effective_date
between abr.effective_start_date
and abr.effective_end_date
and c_effective_date
between abr2.effective_start_date
and abr2.effective_end_date;
select rt_val,
ann_rt_val
from ben_prtt_rt_val
where acty_base_rt_id = c_acty_base_rt_id
and prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and per_in_ler_id = p_per_in_ler_id
-- bug 6022111
AND ( ( p_effective_date BETWEEN rt_strt_dt AND rt_end_dt
-- for recurring rate
AND rt_strt_dt < rt_end_dt
)
OR ( -- for non-recurring rate
rt_strt_dt = rt_end_dt
AND rt_strt_dt <= p_effective_date
AND rt_strt_dt =
(SELECT MAX (rt_strt_dt)
FROM ben_prtt_rt_val
WHERE acty_base_rt_id = c_acty_base_rt_id
AND prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
AND per_in_ler_id = p_per_in_ler_id
AND rt_strt_dt = rt_end_dt
AND prtt_rt_val_stat_cd IS NULL)
)
)
-- end bug 6022111
and prtt_rt_val_stat_cd IS NULL ; --BUG 4938498
select null
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id = p_per_in_ler_id
and pil.ler_id = ler.ler_id
and ler.typ_cd = 'SCHEDDU'
and ler.business_group_id = p_business_group_id
and p_effective_date between ler.effective_start_date
and ler.effective_end_date;
select pet.processing_type
from pay_element_types_f pet
where pet.element_type_id = l_enrt_rt.element_type_id
and l_effective_date between pet.effective_start_date
and pet.effective_end_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 pay_element_links_f elk,
per_all_assignments_f asg,
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
order by pee.effective_end_date desc ;
SELECT distinct yp.start_date
FROM ben_popl_yr_perd pyp, ben_yr_perd yp
WHERE ( pyp.pl_id = cv_pl_id or pyp.pgm_id = cv_pgm_id )
AND pyp.yr_perd_id = yp.yr_perd_id
AND pyp.business_group_id = p_business_group_id
AND cv_lf_evt_ocrd_dt BETWEEN yp.start_date AND yp.end_date
AND yp.business_group_id = p_business_group_id ;
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 prv.prtt_rt_val_id,
prv.rt_strt_dt,
prv.acty_ref_perd_cd,
prv.object_version_number
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.acty_base_rt_id = p_acty_base_rt_id
and prv.rt_strt_dt >= p_rt_strt_dt
and prv.per_in_ler_id <> p_per_in_ler_id
and prv.prtt_rt_val_stat_cd is null
order by rt_strt_dt desc;
select abr.ele_entry_val_cd,
abr.element_type_id,
abr.input_value_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;
select name
from ben_pl_f pln
where pln.pl_id = p_pl_id
and l_effective_date between
pln.effective_start_date and pln.effective_end_date;
select epe.fonm_cvg_strt_dt
from ben_elig_per_elctbl_chc epe
where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and epe.per_in_ler_id = p_per_in_ler_id;
SELECT abr.entr_val_at_enrt_flag, er.rt_mlt_cd,
NVL (eb.entr_val_at_enrt_flag, 'N') entr_bnft_val_flag,
eb.cvg_mlt_cd
FROM ben_enrt_rt er,
ben_enrt_bnft eb,
ben_acty_base_rt_f abr,
ben_prtt_enrt_rslt_f pen
WHERE eb.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
AND pen.rplcs_sspndd_rslt_id = p_prtt_enrt_rslt_id
AND eb.elig_per_elctbl_chc_id = cv_elig_per_elctbl_chc_id
AND pen.per_in_ler_id = p_per_in_ler_id
AND eb.enrt_bnft_id = er.enrt_bnft_id
AND er.acty_base_rt_id = abr.acty_base_rt_id
AND l_effective_date BETWEEN pen.effective_start_date
AND pen.effective_end_date
AND ( ( ben_manage_life_events.fonm = 'Y'
AND NVL (er.rt_strt_dt, l_effective_date)
BETWEEN abr.effective_start_date
AND abr.effective_end_date
)
OR ( NVL (ben_manage_life_events.fonm, 'N') = 'N'
AND l_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
)
);
select 'Y'
from ben_per_in_ler pil, ben_per_in_ler pil2
where pil.per_in_ler_id <> p_per_in_ler_id
and trunc(pil.lf_evt_ocrd_dt, 'MM') = trunc(pil2.lf_evt_ocrd_dt, 'MM')
and pil2.per_in_ler_id = p_per_in_ler_id
and pil2.person_id = p_person_id
and pil.person_id = p_person_id
and pil.business_group_id = pil2.business_group_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select pgi_information1
from ben_pgm_extra_info
where information_type = 'ADJ_RATE_PREV_LF_EVT'
and pgm_id = p_pgm_id;
select min(ecr.rt_strt_dt) rt_strt_dt
,epe.ptip_id
from ben_elig_per_elctbl_chc epe
,ben_enrt_rt ecr
,ben_enrt_bnft enb
where epe.per_in_ler_id = p_per_in_ler_id
and epe.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) = epe.elig_per_elctbl_chc_id
and enb.enrt_bnft_id (+) = ecr.enrt_bnft_id
and ecr.rt_strt_dt is not null
and ecr.business_group_id = p_business_group_id
group by epe.ptip_id;
select prv.*
,abr.element_type_id
,abr.input_value_id
,pen.person_id
from ben_prtt_enrt_rslt_f pen
,ben_prtt_rt_val prv
,ben_acty_base_rt_f abr
where pen.effective_end_date = hr_api.g_eot
and pen.enrt_cvg_thru_dt <> hr_api.g_eot
and pen.prtt_enrt_rslt_stat_cd is null
and pen.person_id = p_person_id
and pen.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
and pen.ptip_id = p_ptip_id
and prv.prtt_rt_val_stat_cd is null
and prv.rt_end_dt >= p_rt_end_dt
and prv.acty_base_rt_id = abr.acty_base_rt_id
and p_effective_date between abr.effective_start_date
and abr.effective_end_date;
select null
from ben_le_clsn_n_rstr
where BKUP_TBL_TYP_CD = 'BEN_PRTT_RT_VAL_ADJ'
AND BKUP_TBL_ID = p_prtt_rt_val_id
AND PER_IN_LER_ID = p_per_in_ler_id;
* rates having same element type, the first input value is updated
* with value zero along with element entry. This while processing the second
* rate,the cursor c_element_info gets the latest payroll id, which while
* processing the second rate does not set the flag l_element_changed
* in the condition below, which impact all further processing */
ben_element_entry.get_abr_assignment
(p_person_id => p_person_id,
p_effective_date => l_old_rt_strt_dt,
p_acty_base_rt_id => l_enrt_rt.acty_base_rt_id,
p_assignment_id => l_old_assignment_id,
p_payroll_id => l_old_payroll_id,
p_organization_id => l_old_organization_id);
l_ext_inpval_tab.delete;
l_inpval_tab.delete;
ben_prtt_rt_val_api.delete_prtt_rt_val
(p_prtt_rt_val_id => p_prtt_rt_val_id
,p_enrt_rt_id => p_enrt_rt_id
,p_person_id => p_person_id
,p_business_group_id => l_enrt_rt.business_group_id
,p_object_version_number => l_old_object_version_number
,p_effective_date => p_effective_date
);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_prtt_rt_val_id => p_prtt_rt_val_id
,p_rt_end_dt => hr_api.g_eot
,p_acty_base_rt_id => l_enrt_rt.acty_base_rt_id
,p_input_value_id => l_enrt_rt.input_value_id
,p_element_type_id => l_enrt_rt.element_type_id
,p_person_id => p_person_id
,p_ended_per_in_ler_id => null
,p_business_group_id => l_enrt_rt.business_group_id
,p_object_version_number => l_old_object_version_number
,p_effective_date => p_effective_date
);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_prtt_rt_val_id => p_prtt_rt_val_id
,p_rt_end_dt => l_xrt_end_dt
,p_ended_per_in_ler_id => p_per_in_ler_id
,p_acty_base_rt_id => l_enrt_rt.acty_base_rt_id
,p_input_value_id => l_enrt_rt.input_value_id
,p_element_type_id => l_enrt_rt.element_type_id
,p_person_id => p_person_id
,p_business_group_id => l_enrt_rt.business_group_id
,p_object_version_number => l_old_object_version_number
,p_effective_date => p_effective_date
,p_no_end_element => l_no_end_element
);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_prtt_rt_val_id => p_prtt_rt_val_id
,p_rt_end_dt => l_xrt_end_dt
,p_ended_per_in_ler_id => p_per_in_ler_id
,p_acty_base_rt_id => l_enrt_rt.acty_base_rt_id
,p_input_value_id => l_enrt_rt.input_value_id
,p_element_type_id => l_enrt_rt.element_type_id
,p_person_id => p_person_id
,p_business_group_id => l_enrt_rt.business_group_id
,p_object_version_number => l_old_object_version_number
,p_effective_date => p_effective_date
,p_no_end_element => l_no_end_element
);
insert into BEN_LE_CLSN_N_RSTR (
BKUP_TBL_TYP_CD,
BKUP_TBL_ID,
per_in_ler_id,
person_id,
RT_END_DT,
business_group_id,
object_version_number)
values (
'BEN_PRTT_RT_VAL_ADJ',
l_pen.prtt_rt_val_id,
p_per_in_ler_id,
l_pen.person_id,
l_pen.rt_end_dt,
p_business_group_id,
l_pen.object_version_number
);
ben_prtt_rt_val_api.update_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_pen.prtt_rt_val_id
,P_RT_END_DT => l_epe.rt_strt_dt - 1
,p_person_id => l_pen.person_id
,p_input_value_id => l_pen.input_value_id
,p_element_type_id => l_pen.element_type_id
,p_business_group_id => p_business_group_id
,P_OBJECT_VERSION_NUMBER => l_pen.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_prtt_rt_val_id => p_prtt_rt_val_id
,p_rt_end_dt => hr_api.g_eot
,p_acty_base_rt_id => l_enrt_rt.acty_base_rt_id
,p_input_value_id => l_enrt_rt.input_value_id
,p_element_type_id => l_enrt_rt.element_type_id
,p_person_id => p_person_id
,p_ended_per_in_ler_id => null
,p_business_group_id => l_enrt_rt.business_group_id
,p_object_version_number => l_old_object_version_number
,p_effective_date => p_effective_date
);
insert into BEN_LE_CLSN_N_RSTR (
BKUP_TBL_TYP_CD,
BKUP_TBL_ID,
per_in_ler_id,
person_id,
RT_END_DT,
business_group_id,
object_version_number)
values (
'BEN_PRTT_RT_VAL_ADJ',
l_pen.prtt_rt_val_id,
p_per_in_ler_id,
l_pen.person_id,
l_pen.rt_end_dt,
p_business_group_id,
l_pen.object_version_number
);
ben_prtt_rt_val_api.update_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_pen.prtt_rt_val_id
,P_RT_END_DT => l_epe.rt_strt_dt - 1
,p_person_id => l_pen.person_id
,p_input_value_id => l_pen.input_value_id
,p_element_type_id => l_pen.element_type_id
,p_business_group_id => p_business_group_id
,P_OBJECT_VERSION_NUMBER => l_pen.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_prtt_rt_val_id => p_prtt_rt_val_id
,p_rt_end_dt => l_rt_end_dt
,p_ended_per_in_ler_id => p_per_in_ler_id
,p_acty_base_rt_id => l_enrt_rt.acty_base_rt_id
,p_input_value_id => l_enrt_rt.input_value_id
,p_element_type_id => l_enrt_rt.element_type_id
,p_person_id => p_person_id
,p_business_group_id => l_enrt_rt.business_group_id
,p_object_version_number => l_old_object_version_number
,p_effective_date => p_effective_date);
SELECT yp.end_date,
pyp.ordr_num
FROM ben_popl_yr_perd pyp, ben_yr_perd yp
WHERE pyp.pl_id = p_pl_id
AND pyp.yr_perd_id = yp.yr_perd_id
AND pyp.business_group_id = p_business_group_id
AND p_lf_evt_ocrd_dt BETWEEN yp.start_date AND yp.end_date
AND yp.business_group_id = p_business_group_id;
select opt_id
from ben_oipl_f
where oipl_id = p_oipl_id
and business_group_id = p_business_group_id
and p_effective_date between EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE;
select asg.assignment_id ,
asg.organization_id
from per_all_assignments_f asg
where asg.person_id = p_person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and p_effective_date between
asg.effective_start_date and asg.effective_end_date;
SELECT loc.region_2
FROM hr_locations_all loc, per_all_assignments_f asg
WHERE loc.location_id = asg.location_id
AND asg.person_id = p_person_id
and asg.assignment_type <> 'C'
AND asg.primary_flag = 'Y'
AND p_lf_evt_ocrd_dt BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.business_group_id = p_business_group_id;
select pen.enrt_cvg_strt_dt,
pen.enrt_cvg_thru_dt,
pen.bnft_amt,
pen.pl_id,
pen.oipl_id,
pen.orgnl_enrt_dt,
pen.per_in_ler_id,
pen.object_version_number,
pen.erlst_deenrt_dt ,
pen.enrt_ovrid_thru_dt,
pen.enrt_ovrid_rsn_cd,
pen.enrt_ovridn_flag,
pen.sspndd_flag
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id=v_prtt_enrt_rslt_id and
pen.business_group_id=p_business_group_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 pen.prtt_enrt_rslt_id ,
pen.object_version_number
from ben_prtt_enrt_rslt_f pen
where pen.rplcs_sspndd_rslt_id = v_prtt_enrt_rslt_id and
pen.prtt_enrt_rslt_id = v_sspnd_result_id and
p_effective_date between
pen.effective_start_date and pen.effective_end_date
;
select ordr_num
from ben_oipl_f
where oipl_id = p_oipl_id and
business_group_id = p_business_group_id and
p_effective_date between
effective_start_date and effective_end_date
;
select 'Y'
from ben_pl_regn_f prg,
ben_regn_f regn
where prg.pl_id=l_global_epe_rec.pl_id
and p_effective_date between
prg.effective_start_date and prg.effective_end_date
and prg.business_group_id=p_business_group_id
and regn.regn_id=prg.regn_id
and regn.name in ('IRC Section 125','IRC Section 129')
and p_effective_date between
regn.effective_start_date and regn.effective_end_date
and regn.business_group_id=p_business_group_id
;
select ann_val
from ben_enrt_rt ert
where ert.enrt_bnft_id = p_enrt_bnft_id
and ENTR_VAL_AT_ENRT_FLAG = 'N'
and ann_val is not null ;
select ert.ann_val,ert.ENTR_VAL_AT_ENRT_FLAG,
abr.acty_base_rt_id,
abr.rate_periodization_rl,
enb.elig_per_elctbl_chc_id,
ert.entr_ann_val_flag
from ben_enrt_rt ert,
ben_acty_base_rt_f abr,
ben_enrt_bnft enb
where enb.enrt_bnft_id = p_enrt_bnft_id
and ert.enrt_bnft_id = enb.enrt_bnft_id
and nvl(abr.PARNT_CHLD_CD,'PARNT') = 'PARNT'
and abr.acty_base_rt_id = ert.acty_base_rt_id
and p_effective_date between abr.effective_start_date
and abr.effective_end_date
and ert.acty_typ_cd not like 'PRD%'
and ert.acty_typ_cd <> 'PRFRFS' ;
select epe.assignment_id ,
epe.fonm_cvg_strt_dt
from ben_elig_per_elctbl_chc epe
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id ;
select enb.prtt_enrt_rslt_id
from ben_enrt_bnft enb,
ben_prtt_enrt_rslt_f pen
where enb.enrt_bnft_id = p_enrt_bnft_id
and enb.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.effective_end_date = hr_api.g_eot
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.prtt_enrt_rslt_stat_cd is NULL
;
select pen.*
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 = (select pen2.effective_start_date - 1
from ben_prtt_enrt_rslt_f pen2
where pen2.enrt_cvg_thru_dt <> hr_api.g_eot
and pen2.effective_end_date = hr_api.g_eot
and pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen2.prtt_enrt_rslt_stat_cd is null);
select pen.*
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;
select enrt_rt_id
from ben_enrt_rt ecr
where (ecr.enrt_bnft_id = p_enrt_bnft_id
or ecr.ELIG_PER_ELCTBL_CHC_ID = p_elig_per_elctbl_chc_id)
and ecr.acty_base_rt_id = p_acty_base_rt_id;
select prv.prtt_rt_val_id,
prv.acty_base_rt_id
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.prtt_rt_val_stat_cd is null
order by prv.rt_strt_dt asc;
select pdp.*
from ben_elig_cvrd_dpnt_f pdp
where pdp.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pdp.cvg_thru_dt is not null
and p_effective_date
between pdp.effective_start_date
and pdp.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.business_group_id = p_business_group_id
and p_effective_date between pen.effective_start_date
and pen.effective_end_date ;
select opt.invk_wv_opt_flag
from ben_opt_f opt , ben_oipl_f oipl
where oipl.oipl_id = p_oipl_id
and oipl.opt_id = opt.opt_id
and oipl.business_group_id = p_business_group_id
and p_effective_date between oipl.effective_start_date and oipl.effective_end_date
and p_effective_date between opt.effective_start_date and opt.effective_end_date ;
select pen.prtt_enrt_Rslt_stat_Cd
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id and
pen.business_group_id= p_business_group_id and
p_effective_date between pen.effective_start_date and pen.effective_end_date;
select pen.*
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and pen.effective_end_date <> hr_api.g_eot
and pen.prtt_enrt_rslt_stat_cd is null
and pen.business_group_id = p_business_group_id
order by pen.effective_start_date desc ;
select prv.*
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.per_in_ler_id = p_per_in_ler_id
and prv.prtt_rt_val_stat_cd is NULL ;
select ecr.rt_strt_dt,
ecr.rt_strt_dt_cd,
ecr.rt_strt_dt_rl,
nvl(ecr.elig_per_elctbl_chc_id,enb.elig_per_elctbl_chc_id) elig_per_elctbl_chc_id
from ben_enrt_rt ecr,
ben_enrt_bnft enb
where ecr.prtt_rt_val_id = p_prtt_rt_val_id
and ecr.enrt_bnft_id = enb.enrt_bnft_id (+) ;
select null
from ben_elig_per_elctbl_chc
where elig_per_elctbl_chc_id = cv_elig_per_elctblc_chc_id
and prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id;
select 'Y'
from ben_elig_per_elctbl_chc epe, ben_per_in_ler pil,
ben_per_in_ler pil2
where epe.elig_per_elctbl_chc_id=p_elig_per_elctbl_chc_id
and pil.per_in_ler_id <> epe.per_in_ler_id
and trunc(pil.lf_evt_ocrd_dt, 'MM') = trunc(pil2.lf_evt_ocrd_dt, 'MM')
and pil2.per_in_ler_id = epe.per_in_ler_id
and pil.business_group_id = pil2.business_group_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select distinct epe.pgm_id
from ben_elig_per_elctbl_chc epe
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
select pgi_information1
from ben_pgm_extra_info
where information_type = 'ADJ_CVG_PREV_LF_EVT'
and pgm_id = p_pgm_id;
select min(epe.enrt_cvg_strt_dt) enrt_cvg_strt_dt
,epe.ptip_id
from ben_elig_per_elctbl_chc epe
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.business_group_id = p_business_group_id
group by epe.ptip_id;
select pen.*, epe.per_in_ler_id pil_id
from ben_prtt_enrt_rslt_f pen
,ben_ptip_f ptip
,ben_per_in_ler pil
,ben_elig_per_elctbl_chc epe
where pen.effective_end_date = hr_api.g_eot -- '31-dec-4712'
and pen.enrt_cvg_thru_dt <> hr_api.g_eot -- '31-dec-4712'
and pen.prtt_enrt_rslt_stat_cd is null
and pen.person_id = pil.person_id -- 318321
and pen.business_group_id = p_business_group_id -- 81545
and pen.ptip_id = p_ptip_id -- 54444
and pen.enrt_cvg_thru_dt >= p_cvg_end_dt -- '20-jan-2008'
and pen.ptip_id = ptip.ptip_id
and pil.per_in_ler_id = epe.per_in_ler_id
and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and p_effective_date between ptip.effective_start_date
and ptip.effective_end_date;
select null
from ben_le_clsn_n_rstr leclr, ben_elig_per_elctbl_chc epe
where leclr.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_ADJ'
AND leclr.BKUP_TBL_ID = p_prtt_enrt_rslt_id
AND leclr.PER_IN_LER_ID = epe.per_in_ler_id
AND epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
ben_prtt_enrt_result_api.delete_prtt_enrt_result
(p_validate => false,
p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_pen_curr.object_version_number,
p_effective_date => l_pen_curr.effective_end_date,
p_datetrack_mode => hr_api.g_future_change,
p_multi_row_validate => FALSE);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_validate => false,
p_person_id => l_global_pil_rec.person_id,
p_business_group_id => p_business_group_id,
p_prtt_rt_val_id => l_rec.prtt_rt_val_id,
p_rt_strt_dt => nvl(l_ecr.rt_strt_dt,l_rec.rt_strt_dt),
p_rt_end_dt => hr_api.g_eot,
p_object_version_number => l_rec.object_version_number,
p_effective_date => p_effective_date );
ben_prtt_enrt_result_api.delete_prtt_enrt_result
(p_validate => false,
p_prtt_enrt_rslt_id => l_pen_curr_interim.prtt_enrt_rslt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_pen_curr_interim.object_version_number,
p_effective_date => l_pen_curr_interim.effective_end_date,
p_datetrack_mode => hr_api.g_future_change,
p_multi_row_validate => FALSE);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_validate => false,
p_person_id => l_global_pil_rec.person_id,
p_business_group_id => p_business_group_id,
p_prtt_rt_val_id => l_rec.prtt_rt_val_id,
p_rt_end_dt => hr_api.g_eot,
p_object_version_number => l_rec.object_version_number,
p_effective_date => p_effective_date );
select count(*)
into l_interim_count
from ben_prtt_enrt_rslt_f pen
where pen.rplcs_sspndd_rslt_id = p_prtt_enrt_rslt_id
and nvl(pen.sspndd_flag , 'N') = 'Y'
and p_effective_date
between pen.effective_start_date and pen.effective_end_date;
insert into BEN_LE_CLSN_N_RSTR (
BKUP_TBL_TYP_CD,
COMP_LVL_CD,
LCR_ATTRIBUTE16,
LCR_ATTRIBUTE17,
LCR_ATTRIBUTE18,
LCR_ATTRIBUTE19,
LCR_ATTRIBUTE20,
LCR_ATTRIBUTE21,
LCR_ATTRIBUTE22,
LCR_ATTRIBUTE23,
LCR_ATTRIBUTE24,
LCR_ATTRIBUTE25,
LCR_ATTRIBUTE26,
LCR_ATTRIBUTE27,
LCR_ATTRIBUTE28,
LCR_ATTRIBUTE29,
LCR_ATTRIBUTE30,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
BKUP_TBL_ID, -- PRTT_ENRT_RSLT_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
ENRT_CVG_STRT_DT,
ENRT_CVG_THRU_DT,
SSPNDD_FLAG,
PRTT_IS_CVRD_FLAG,
BNFT_AMT,
BNFT_NNMNTRY_UOM,
BNFT_TYP_CD,
UOM,
ORGNL_ENRT_DT,
ENRT_MTHD_CD,
ENRT_OVRIDN_FLAG,
ENRT_OVRID_RSN_CD,
ERLST_DEENRT_DT,
ENRT_OVRID_THRU_DT,
NO_LNGR_ELIG_FLAG,
BNFT_ORDR_NUM,
PERSON_ID,
ASSIGNMENT_ID,
PGM_ID,
PRTT_ENRT_RSLT_STAT_CD,
PL_ID,
OIPL_ID,
PTIP_ID,
PL_TYP_ID,
LER_ID,
PER_IN_LER_ID,
RPLCS_SSPNDD_RSLT_ID,
BUSINESS_GROUP_ID,
LCR_ATTRIBUTE_CATEGORY,
LCR_ATTRIBUTE1,
LCR_ATTRIBUTE2,
LCR_ATTRIBUTE3,
LCR_ATTRIBUTE4,
LCR_ATTRIBUTE5,
LCR_ATTRIBUTE6,
LCR_ATTRIBUTE7,
LCR_ATTRIBUTE8,
LCR_ATTRIBUTE9,
LCR_ATTRIBUTE10,
LCR_ATTRIBUTE11,
LCR_ATTRIBUTE12,
LCR_ATTRIBUTE13,
LCR_ATTRIBUTE14,
LCR_ATTRIBUTE15 ,
PER_IN_LER_ENDED_ID,
PL_ORDR_NUM,
PLIP_ORDR_NUM,
PTIP_ORDR_NUM,
OIPL_ORDR_NUM)
values (
'BEN_PRTT_ENRT_RSLT_F_CORR',
l_enrt_rslt2.COMP_LVL_CD,
l_enrt_rslt2.PEN_ATTRIBUTE16,
l_enrt_rslt2.PEN_ATTRIBUTE17,
l_enrt_rslt2.PEN_ATTRIBUTE18,
l_enrt_rslt2.PEN_ATTRIBUTE19,
l_enrt_rslt2.PEN_ATTRIBUTE20,
l_enrt_rslt2.PEN_ATTRIBUTE21,
l_enrt_rslt2.PEN_ATTRIBUTE22,
l_enrt_rslt2.PEN_ATTRIBUTE23,
l_enrt_rslt2.PEN_ATTRIBUTE24,
l_enrt_rslt2.PEN_ATTRIBUTE25,
l_enrt_rslt2.PEN_ATTRIBUTE26,
l_enrt_rslt2.PEN_ATTRIBUTE27,
l_enrt_rslt2.PEN_ATTRIBUTE28,
l_enrt_rslt2.PEN_ATTRIBUTE29,
l_enrt_rslt2.PEN_ATTRIBUTE30,
l_enrt_rslt2.LAST_UPDATE_DATE,
l_enrt_rslt2.LAST_UPDATED_BY,
l_enrt_rslt2.LAST_UPDATE_LOGIN,
l_enrt_rslt2.CREATED_BY,
l_enrt_rslt2.CREATION_DATE,
l_enrt_rslt2.REQUEST_ID,
l_enrt_rslt2.PROGRAM_APPLICATION_ID,
l_enrt_rslt2.PROGRAM_ID,
l_enrt_rslt2.PROGRAM_UPDATE_DATE,
l_enrt_rslt2.OBJECT_VERSION_NUMBER,
l_enrt_rslt2.PRTT_ENRT_RSLT_ID,
l_enrt_rslt2.EFFECTIVE_START_DATE,
l_enrt_rslt2.EFFECTIVE_END_DATE,
l_enrt_rslt2.ENRT_CVG_STRT_DT,
l_enrt_rslt2.ENRT_CVG_THRU_DT,
l_enrt_rslt2.SSPNDD_FLAG,
l_enrt_rslt2.PRTT_IS_CVRD_FLAG,
l_enrt_rslt2.BNFT_AMT,
l_enrt_rslt2.BNFT_NNMNTRY_UOM,
l_enrt_rslt2.BNFT_TYP_CD,
l_enrt_rslt2.UOM,
l_enrt_rslt2.ORGNL_ENRT_DT,
l_enrt_rslt2.ENRT_MTHD_CD,
l_enrt_rslt2.ENRT_OVRIDN_FLAG,
l_enrt_rslt2.ENRT_OVRID_RSN_CD,
l_enrt_rslt2.ERLST_DEENRT_DT,
l_enrt_rslt2.ENRT_OVRID_THRU_DT,
l_enrt_rslt2.NO_LNGR_ELIG_FLAG,
l_enrt_rslt2.BNFT_ORDR_NUM,
l_enrt_rslt2.PERSON_ID,
l_enrt_rslt2.ASSIGNMENT_ID,
l_enrt_rslt2.PGM_ID,
l_enrt_rslt2.PRTT_ENRT_RSLT_STAT_CD,
l_enrt_rslt2.PL_ID,
l_enrt_rslt2.OIPL_ID,
l_enrt_rslt2.PTIP_ID,
l_enrt_rslt2.PL_TYP_ID,
l_enrt_rslt2.LER_ID,
l_enrt_rslt2.PER_IN_LER_ID,
l_enrt_rslt2.RPLCS_SSPNDD_RSLT_ID,
l_enrt_rslt2.BUSINESS_GROUP_ID,
l_enrt_rslt2.PEN_ATTRIBUTE_CATEGORY,
l_enrt_rslt2.PEN_ATTRIBUTE1,
l_enrt_rslt2.PEN_ATTRIBUTE2,
l_enrt_rslt2.PEN_ATTRIBUTE3,
l_enrt_rslt2.PEN_ATTRIBUTE4,
l_enrt_rslt2.PEN_ATTRIBUTE5,
l_enrt_rslt2.PEN_ATTRIBUTE6,
l_enrt_rslt2.PEN_ATTRIBUTE7,
l_enrt_rslt2.PEN_ATTRIBUTE8,
l_enrt_rslt2.PEN_ATTRIBUTE9,
l_enrt_rslt2.PEN_ATTRIBUTE10,
l_enrt_rslt2.PEN_ATTRIBUTE11,
l_enrt_rslt2.PEN_ATTRIBUTE12,
l_enrt_rslt2.PEN_ATTRIBUTE13,
l_enrt_rslt2.PEN_ATTRIBUTE14,
l_enrt_rslt2.PEN_ATTRIBUTE15,
l_global_epe_rec.per_in_ler_id ,
l_enrt_rslt2.PL_ORDR_NUM,
l_enrt_rslt2.PLIP_ORDR_NUM,
l_enrt_rslt2.PTIP_ORDR_NUM,
l_enrt_rslt2.OIPL_ORDR_NUM
);
ben_prtt_enrt_result_api.delete_prtt_enrt_result
(p_validate => false,
p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_enrt_rslt.object_version_number,
p_effective_date => l_enrt_rslt.effective_end_date,
p_datetrack_mode => hr_api.g_future_change,
p_multi_row_validate => FALSE);
ben_elig_cvrd_dpnt_api.delete_elig_cvrd_dpnt
(p_validate => false,
p_elig_cvrd_dpnt_id => l_dpnt_rec.elig_cvrd_dpnt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_dpnt_rec.object_version_number,
p_business_group_id => p_business_group_id,
p_effective_date => l_enrt_rslt.effective_end_date,
p_datetrack_mode => hr_api.g_future_change,
p_multi_row_actn => FALSE);
update ben_enrt_rt
set prtt_rt_val_id = l_prtt_rt_val_id
where enrt_rt_id = l_enrt_rt_id;
hr_utility.set_location('bef update cvg '|| l_global_epe_rec.enrt_cvg_strt_dt,8086.2);
hr_utility.set_location('bef update erly '||l_global_epe_rec.erlst_deenrt_dt,8086.2);
ben_PRTT_ENRT_RESULT_api.update_enrollment(
p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_enrt_mthd_cd => p_enrt_mthd_cd
,p_enrt_cvg_strt_dt => l_global_epe_rec.enrt_cvg_strt_dt
,p_enrt_cvg_thru_dt => hr_api.g_eot
,p_enrt_ovrid_thru_dt => l_old_enrt_ovrid_thru_dt
,p_enrt_ovrid_rsn_cd => l_old_enrt_ovrid_rsn_cd
,p_enrt_ovridn_flag => l_old_enrt_ovridn_flag
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_pgm_id => l_global_epe_rec.pgm_id
,p_ptip_id => l_global_epe_rec.ptip_id
,p_pl_typ_id => l_global_epe_rec.pl_typ_id
,p_pl_id => l_global_epe_rec.pl_id
,p_oipl_id => l_global_epe_rec.oipl_id
,p_enrt_bnft_id => p_enrt_bnft_id
,p_business_group_id => p_business_group_id
,p_erlst_deenrt_dt => l_global_epe_rec.erlst_deenrt_dt
,p_per_in_ler_id => l_global_epe_rec.per_in_ler_id
-- ,p_sspndd_flag => nvl(l_old_sspndd_flag,'N') --Bug#5099296
,p_sspndd_flag => nvl(l_global_pen_rec.sspndd_flag,'N') --Bug#5099296
,p_multi_row_validate => FALSE
-- derive from per_in_ler_id
,p_ler_id => l_global_pil_rec.ler_id
,p_person_id => l_global_pil_rec.person_id
,p_bnft_amt => l_global_enb_rec.val
,p_uom => l_global_pel_rec.uom
,p_bnft_nnmntry_uom => l_global_enb_rec.nnmntry_uom
,p_bnft_typ_cd => l_global_enb_rec.bnft_typ_cd
,p_bnft_ordr_num => l_global_enb_rec.ordr_num
,p_suspend_flag => p_suspend_flag
,p_prtt_enrt_interim_id => l_prtt_enrt_interim_id
,p_comp_lvl_cd => l_global_pen_rec.comp_lvl_cd -- bug 5417132
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate
,p_pen_attribute_category => p_pen_attribute_category
,p_pen_attribute1 => p_pen_attribute1
,p_pen_attribute2 => p_pen_attribute2
,p_pen_attribute3 => p_pen_attribute3
,p_pen_attribute4 => p_pen_attribute4
,p_pen_attribute5 => p_pen_attribute5
,p_pen_attribute6 => p_pen_attribute6
,p_pen_attribute7 => p_pen_attribute7
,p_pen_attribute8 => p_pen_attribute8
,p_pen_attribute9 => p_pen_attribute9
,p_pen_attribute10 => p_pen_attribute10
,p_pen_attribute11 => p_pen_attribute11
,p_pen_attribute12 => p_pen_attribute12
,p_pen_attribute13 => p_pen_attribute13
,p_pen_attribute14 => p_pen_attribute14
,p_pen_attribute15 => p_pen_attribute15
,p_pen_attribute16 => p_pen_attribute16
,p_pen_attribute17 => p_pen_attribute17
,p_pen_attribute18 => p_pen_attribute18
,p_pen_attribute19 => p_pen_attribute19
,p_pen_attribute20 => p_pen_attribute20
,p_pen_attribute21 => p_pen_attribute21
,p_pen_attribute22 => p_pen_attribute22
,p_pen_attribute23 => p_pen_attribute23
,p_pen_attribute24 => p_pen_attribute24
,p_pen_attribute25 => p_pen_attribute25
,p_pen_attribute26 => p_pen_attribute26
,p_pen_attribute27 => p_pen_attribute27
,p_pen_attribute28 => p_pen_attribute28
,p_pen_attribute29 => p_pen_attribute29
,p_pen_attribute30 => p_pen_attribute30
,p_dpnt_actn_warning => l_dpnt_actn_warning
,p_bnf_actn_warning => l_bnf_actn_warning
,p_ctfn_actn_warning => l_ctfn_actn_warning
);
hr_utility.set_location('bef update cvg '|| l_global_epe_rec.enrt_cvg_strt_dt,8086.2);
hr_utility.set_location('bef update erly '||l_global_epe_rec.erlst_deenrt_dt,8086.2);
ben_PRTT_ENRT_RESULT_api.update_enrollment(
p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_enrt_mthd_cd => p_enrt_mthd_cd
,p_enrt_cvg_strt_dt => l_global_epe_rec.enrt_cvg_strt_dt
,p_enrt_cvg_thru_dt => hr_api.g_eot
,p_enrt_ovrid_thru_dt => l_crntly_enrd_rslt_rec.enrt_ovrid_thru_dt
,p_enrt_ovrid_rsn_cd => l_crntly_enrd_rslt_rec.enrt_ovrid_rsn_cd
,p_enrt_ovridn_flag => l_crntly_enrd_rslt_rec.enrt_ovridn_flag
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_pgm_id => l_global_epe_rec.pgm_id
,p_ptip_id => l_global_epe_rec.ptip_id
,p_pl_typ_id => l_global_epe_rec.pl_typ_id
,p_pl_id => l_global_epe_rec.pl_id
,p_oipl_id => l_global_epe_rec.oipl_id
,p_enrt_bnft_id => p_enrt_bnft_id
,p_business_group_id => l_global_epe_rec.business_group_id
,p_erlst_deenrt_dt => l_global_epe_rec.erlst_deenrt_dt
,p_per_in_ler_id => l_global_epe_rec.per_in_ler_id
-- ,p_sspndd_flag => nvl(l_old_sspndd_flag,'N') --Bug#5099296
,p_sspndd_flag => nvl(l_global_pen_rec.sspndd_flag,'N') --Bug#5099296
,p_multi_row_validate => FALSE
-- derive from per_in_ler_id
,p_ler_id => l_global_pil_rec.ler_id
,p_person_id => l_global_pil_rec.person_id
,p_bnft_amt => l_global_enb_rec.val
,p_uom => l_global_pel_rec.uom
,p_bnft_nnmntry_uom => l_global_enb_rec.nnmntry_uom
,p_bnft_typ_cd => l_global_enb_rec.bnft_typ_cd
,p_bnft_ordr_num => l_global_enb_rec.ordr_num
,p_suspend_flag => p_suspend_flag
,p_prtt_enrt_interim_id => l_prtt_enrt_interim_id
,p_comp_lvl_cd => l_global_pen_rec.comp_lvl_cd -- 5417132
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate
,p_pen_attribute_category => p_pen_attribute_category
,p_pen_attribute1 => p_pen_attribute1
,p_pen_attribute2 => p_pen_attribute2
,p_pen_attribute3 => p_pen_attribute3
,p_pen_attribute4 => p_pen_attribute4
,p_pen_attribute5 => p_pen_attribute5
,p_pen_attribute6 => p_pen_attribute6
,p_pen_attribute7 => p_pen_attribute7
,p_pen_attribute8 => p_pen_attribute8
,p_pen_attribute9 => p_pen_attribute9
,p_pen_attribute10 => p_pen_attribute10
,p_pen_attribute11 => p_pen_attribute11
,p_pen_attribute12 => p_pen_attribute12
,p_pen_attribute13 => p_pen_attribute13
,p_pen_attribute14 => p_pen_attribute14
,p_pen_attribute15 => p_pen_attribute15
,p_pen_attribute16 => p_pen_attribute16
,p_pen_attribute17 => p_pen_attribute17
,p_pen_attribute18 => p_pen_attribute18
,p_pen_attribute19 => p_pen_attribute19
,p_pen_attribute20 => p_pen_attribute20
,p_pen_attribute21 => p_pen_attribute21
,p_pen_attribute22 => p_pen_attribute22
,p_pen_attribute23 => p_pen_attribute23
,p_pen_attribute24 => p_pen_attribute24
,p_pen_attribute25 => p_pen_attribute25
,p_pen_attribute26 => p_pen_attribute26
,p_pen_attribute27 => p_pen_attribute27
,p_pen_attribute28 => p_pen_attribute28
,p_pen_attribute29 => p_pen_attribute29
,p_pen_attribute30 => p_pen_attribute30
,p_dpnt_actn_warning => l_dpnt_actn_warning
,p_bnf_actn_warning => l_bnf_actn_warning
,p_ctfn_actn_warning => l_ctfn_actn_warning
);
ben_prtt_enrt_result_api.update_prtt_enrt_result
(p_validate => FALSE,
p_prtt_enrt_rslt_id => l_delink_interim.prtt_enrt_rslt_id,
p_effective_start_date => l_dlink_effective_start_date,
p_effective_end_date => l_dlink_effective_end_date,
p_business_group_id => p_business_group_id,
p_RPLCS_SSPNDD_RSLT_ID => null,
p_object_version_number => l_delink_interim.object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_correction,
p_multi_row_validate => FALSE,
p_program_application_id => fnd_global.prog_appl_id,
p_program_id => fnd_global.conc_program_id,
p_request_id => fnd_global.conc_request_id,
p_program_update_date => sysdate);
l_datetrack_mode := hr_api.g_update;
insert into BEN_LE_CLSN_N_RSTR (
BKUP_TBL_TYP_CD,
BKUP_TBL_ID,
per_in_ler_id,
person_id,
ENRT_CVG_THRU_DT,
business_group_id,
object_version_number)
values (
'BEN_PRTT_ENRT_RSLT_F_ADJ',
l_get_enrt_rslts_for_pen.prtt_enrt_rslt_id,
l_get_enrt_rslts_for_pen.pil_id,
l_get_enrt_rslts_for_pen.person_id,
l_get_enrt_rslts_for_pen.enrt_cvg_thru_dt,
p_business_group_id,
l_get_enrt_rslts_for_pen.object_version_number
);
ben_prtt_enrt_result_api.update_prtt_enrt_result
(p_validate => FALSE,
p_prtt_enrt_rslt_id => l_get_enrt_rslts_for_pen.prtt_enrt_rslt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_business_group_id => p_business_group_id,
p_object_version_number => l_get_enrt_rslts_for_pen.object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_correction,
p_multi_row_validate => FALSE,
p_enrt_cvg_thru_dt => l_get_elctbl_chc_for_cvg.enrt_cvg_strt_dt - 1
);
,p_program_update_date => sysdate
-- Bug #2714383
-- Passing null if the attribute value is $Sys_Def$
,p_pen_attribute_category => decd_attribute(p_pen_attribute_category)
,p_pen_attribute1 => decd_attribute(p_pen_attribute1)
,p_pen_attribute2 => decd_attribute(p_pen_attribute2)
,p_pen_attribute3 => decd_attribute(p_pen_attribute3)
,p_pen_attribute4 => decd_attribute(p_pen_attribute4)
,p_pen_attribute5 => decd_attribute(p_pen_attribute5)
,p_pen_attribute6 => decd_attribute(p_pen_attribute6)
,p_pen_attribute7 => decd_attribute(p_pen_attribute7)
,p_pen_attribute8 => decd_attribute(p_pen_attribute8)
,p_pen_attribute9 => decd_attribute(p_pen_attribute9)
,p_pen_attribute10 => decd_attribute(p_pen_attribute10)
,p_pen_attribute11 => decd_attribute(p_pen_attribute11)
,p_pen_attribute12 => decd_attribute(p_pen_attribute12)
,p_pen_attribute13 => decd_attribute(p_pen_attribute13)
,p_pen_attribute14 => decd_attribute(p_pen_attribute14)
,p_pen_attribute15 => decd_attribute(p_pen_attribute15)
,p_pen_attribute16 => decd_attribute(p_pen_attribute16)
,p_pen_attribute17 => decd_attribute(p_pen_attribute17)
,p_pen_attribute18 => decd_attribute(p_pen_attribute18)
,p_pen_attribute19 => decd_attribute(p_pen_attribute19)
,p_pen_attribute20 => decd_attribute(p_pen_attribute20)
,p_pen_attribute21 => decd_attribute(p_pen_attribute21)
,p_pen_attribute22 => decd_attribute(p_pen_attribute22)
,p_pen_attribute23 => decd_attribute(p_pen_attribute23)
,p_pen_attribute24 => decd_attribute(p_pen_attribute24)
,p_pen_attribute25 => decd_attribute(p_pen_attribute25)
,p_pen_attribute26 => decd_attribute(p_pen_attribute26)
,p_pen_attribute27 => decd_attribute(p_pen_attribute27)
,p_pen_attribute28 => decd_attribute(p_pen_attribute28)
,p_pen_attribute29 => decd_attribute(p_pen_attribute29)
,p_pen_attribute30 => decd_attribute(p_pen_attribute30)
,p_dpnt_actn_warning => l_dpnt_actn_warning
,p_bnf_actn_warning => l_bnf_actn_warning
,p_ctfn_actn_warning => l_ctfn_actn_warning
,p_enrt_bnft_id => p_enrt_bnft_id
,p_source => 'benelinf'
);
(p_action => 'UPDATE' --plan and option changes
,p_pl_id => l_global_epe_rec.pl_id
,p_old_pl_id => l_old_pl_id
,p_oipl_id => l_global_epe_rec.oipl_id
,p_old_oipl_id => l_old_oipl_id
,p_enrt_cvg_strt_dt => l_global_epe_rec.enrt_cvg_strt_dt
,p_old_enrt_cvg_strt_dt => l_old_enrt_cvg_strt_dt
,p_old_enrt_cvg_end_dt => l_old_enrt_cvg_thru_dt
,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
,p_old_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_per_in_ler_id => l_global_epe_rec.per_in_ler_id
,p_old_per_in_ler_id => l_old_per_in_ler_id
,p_person_id => l_global_pil_rec.person_id
,p_business_group_id => l_global_epe_rec.business_group_id
,p_effective_date => p_effective_date
);
ben_PRTT_ENRT_RESULT_api.delete_enrollment(
p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_per_in_ler_id => l_global_epe_rec.per_in_ler_id
,p_business_group_id => l_global_epe_rec.business_group_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => nvl(l_datetrack_mode, p_datetrack_mode) /*2483991*/
,p_multi_row_validate => FALSE
,p_source => 'benelinf'
,p_lee_rsn_id => l_global_pel_rec.lee_rsn_id
,p_enrt_perd_id => l_global_pel_rec.enrt_perd_id
);
if p_datetrack_mode = hr_api.g_insert then
l_datetrack_mode := hr_api.g_correction;
ben_PRTT_ENRT_RESULT_api.delete_enrollment(
p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_per_in_ler_id => l_global_epe_rec.per_in_ler_id
,p_business_group_id => l_global_epe_rec.business_group_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_multi_row_validate => FALSE
,p_source => 'benelinf'
,p_lee_rsn_id => l_global_pel_rec.lee_rsn_id
,p_enrt_perd_id => l_global_pel_rec.enrt_perd_id
,p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt
,p_mode => 'CVG_END_DATE_ENTERABLE'
);
select ebr.enrt_bnft_id,
ebr.object_version_number
from ben_enrt_bnft ebr,
ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where ebr.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id and
( p_enrt_bnft_id is null OR
ebr.enrt_bnft_id <> p_enrt_bnft_id )
and epe.elig_per_elctbl_chc_id=ebr.elig_per_elctbl_chc_id
and pil.per_in_ler_id=epe.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pil.per_in_ler_id = p_per_in_ler_id ;
ben_enrt_bnft_api.update_enrt_bnft
(p_enrt_bnft_id => rec.enrt_bnft_id
,p_effective_date => p_effective_date
,p_object_version_number => rec.object_version_number
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => NULL
,p_program_application_id =>fnd_global.prog_appl_id
,p_program_id =>fnd_global.conc_program_id
,p_request_id =>fnd_global.conc_request_id
,p_program_update_date =>sysdate
);
ben_enrt_bnft_api.update_enrt_bnft
(p_enrt_bnft_id => p_enrt_bnft_id
,p_effective_date => p_effective_date
,p_object_version_number => p_object_version_number
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate
);
,p_rt_update_mode in varchar2 default null
,p_rt_strt_dt1 in date default null
,p_rt_end_dt1 in date default null
,p_rt_strt_dt_cd1 in varchar2 default null
,p_return_status out nocopy varchar2
) is
--
l_effective_date date := trunc(sysdate);
,p_rt_update_mode => p_rt_update_mode
,p_rt_strt_dt1 => p_rt_strt_dt1
,p_rt_end_dt1 => p_rt_end_dt1
,p_rt_strt_dt_cd1 => p_rt_strt_dt_cd1
,p_api_error => l_api_error);
,p_rt_update_mode in varchar2 default null
,p_api_error out nocopy boolean)
is
l_validate boolean := false;
select enb.val val,-- nvl(enb.val, enb.dflt_val) val, Bug 4216475
enb.mn_val,
enb.mx_val,
enb.incrmt_val
from ben_enrt_bnft enb
where enb.enrt_bnft_id = p_enrt_bnft_id;
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_elig_per_elctbl_chc_id
and epe.pl_id = pln.pl_id
and epe.oipl_id = oipl.oipl_id(+)
and oipl.opt_id = opt.opt_id(+)
and p_effective_date between
pln.effective_start_date and pln.effective_end_date
and p_effective_date between
oipl.effective_start_date(+) and oipl.effective_end_date(+)
and p_effective_date between
opt.effective_start_date(+) and opt.effective_end_date(+);
select epe.crntly_enrd_flag
,epe.elctbl_flag
,epe.mndtry_flag
from ben_elig_per_elctbl_chc epe
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
l_datetrack_mode := hr_api.g_update;
ben_determine_rate_chg.prv_delete
(p_prtt_rt_val_id => p_prtt_rt_val_id1
,p_enrt_rt_id => p_enrt_rt_id1
,p_rt_val => p_rt_val1
,p_rt_strt_dt => p_rt_strt_dt1
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_person_id => p_person_id
,p_effective_date => p_effective_date
,p_mode => p_rt_update_mode);