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.
06-Jan-09 sallumwa 115.261 Bug 7557403 : Modified procedure election_rate_information to set the
global cvg and rate start date variables.
10-Mar-09 krupani 115.264 BUG 8244388: When Child rate is multiple of parent, fetch the parent rate value
as of Child rate strat date
01-Apr-09 krupani 115.265 Bug 8374859: While calling ben_determine_activity_base_rt.main, ler_id was
getting passed from pen record. Now, passing it from ben_per_in_ler
29-Apr-09 velvanop 115.266 Bug 8300620: Fix of Bug 7206471 causing rate gap issues on processing the LifeEvent.
Cursor c_get_elctbl_chc fetches all the choices tied to the current PIL grouped by ptip_id.
As a result, besides the required plan type to be actually adjusted, all ptip's are picked up
and the rates are adjusted.So modified cursor to pick the correct ptip instead of pickingup
all the ptip's
25-May-09 velvanop 115.267 Bug 8507247: Fixes done on top of Bug 7206471
02-Aug-09 krupani 115.268 8716870: Imputed Income Enhancement. Added new parameter p_imp_cvg_strt_dt
which will have the coverage start date of the enrollment subject to imputed income
19-Aug-09 sallumwa 115.270 Bug 8596122 : Handled the overlap coverage case.Also,reverted the fix for 8312737.
05-Sep-09 velvanop 115.271 Bug 8871911 : Performance Bug. Modified cursor 'c_get_prior_per_in_ler' ,added a join condition
to avoid full table scan on pil table
29-Sep-09 velvanop 115.272 Bug 8945818 : Coverage overlap when FONM UnSuspended code and Event date
event processed in the same month
29-Oct-09 stee 115.273 Bug 9028102 : Fix cursor c_get_dpnt to check for an effective
end date that is not equal to eot.
31-Oct-09 velvanop 115.274 Bug 9057101: Instead of passing p_effective_date, pass
effective start date of the pen_id when adjusting the future coverage
27-Jan-10 sallumwa 115.275 Bug 9309878: While determining the defined amount,pass the rate start date
to the procedure annual_to_period.
05-Feb-10 sallumwa 115.276 Bug 9139820 : When multiple changes to the elections are made in a life event,
then open up the enrollment result attached to the previous life event and update
the record with the new changes made in the current life event.
16-Feb-10 sagnanas 115.277 Bug 8589355 - Passed opt_id to get_extra_ele_inputs
02-Mar-10 sallumwa 115.278 Bug 9143356 - sarec_compute flag is set to true when there is a change in
assignment even though there is no change in payroll.
04-Mar-10 sallumwa 115.279 Bug 9430735 - Extended the fix done for the bug 9139820 to handle the case
where no elections are made against the previous life event.
03-May-10 velvanop 115.280 Bug 9538592: Basing upon the flag set in reinstate enrollments call, set l_start_new_result
19-May-10 stee 115.281 Bug 9593336: Do not recompute rate amounts
if annual amount did not change within the
payroll year.
26-Jul-10 sagnanas 115.282 Bug 9614678 - Fetch prv again as it might have been updated to avoid HR obj invalid issue
19-May-10 stee 115.283 Bug 10058794: Fix cursor for bug 9593336 to use regular payment
date instead of period_num.
30-Sep-10 velvanop 115.284 Bug 9758763: Reset the flag g_create_new_result in benleclr.pkb. Flag
will be reseted after the result is reinstated
22-Oct-10 velvanop 115.285 Bug 10137678: While adjusting the rates for overlapping rates, check for any rates that are end dating in the future
in addition to checking for the rates that are starting the future. Modified cursor 'c_get_prior_per_in_ler'.
11-Nov-10 stee 115.286 Bug 9401100: Create a new enrollment result if the benefit amount
does not change but the enrollment is suspended in the current
event.
31-Dec-10 velvanop 115.287 Bug 10419741 Continuation of Bug fix 10137678. While adjusting the rates, if there are no rate records for the previous life event
,cursor c_get_prior_per_in_ler will not fetch any rate records for adjustment. Modified cursor 'c_get_prior_per_in_ler'.
07-Feb-10 krupani 115.288 Bug 10224915 - Code changes to prevent rate gap after imputed income enhancement
15-Mar-11 stee 115.289 Bug 11869699 - Added payroll_id to the period_to_annual function.
12-Apr-11 stee 115.290 Bug 10415806 - Delete future dated element
entries for unrestricted enrollment.
31-May-11 stee 115.291 Bug 12421421 - If interim is what they were enrolled
in before, reinstate the prior enrollment if re-selected.
03-Aug-11 velvanop 115.292 Bug 12576710 - Insert the future coverage and rate records into backup table before updating them with new values
07-Dec-11 velvanop 115.293 Fixes for Bugs 13079111/13472223: If the coverage code is based on Rule, then consider the new value evaluated by rule
while saving the enrollments for cvg start date, instead of the old value evaluated when processing benmngle(stored in epe table)
15-Dec-11 usaraswa 115.294 Bug 13444556 - While adjusting the coverage for overlapping coverage, check for any coverage that are end dating in the future.
Created cursor 'c_get_prior_per_in_ler_1'.
16-Feb-2012 stee 115.295 Bug 13542327 - Added elig_per_elctbl_chc_id
to cursor c_get_ended_result.
01-May-12 stee 115.296 Bug 12583091 - Check for per_in_ler_id when
determining if enrollment is suspended.
9-Nov-12 velvanop 115.297 Bug 14803995 : Do not insert future coverage records in backup table if the Life event is Unrestricted
9-Mar-13 velvanop 115.298 Bug 16396102 : While inserting record into backup table ben_le_clsn_n_rstr for rate adjustment records 'BEN_PRTT_RT_VAL_ADJ', populate person_id
while inserting into the table
*/
-------------------------------------------------------------------------------
--
-- 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.prtt_enrt_rslt_id, -- 10415806
prv.rt_val, -- 10415806
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
select prv.prtt_rt_val_id,
prv.acty_base_rt_id,
prv.acty_ref_perd_cd,
prv.rt_strt_dt,
prv.prtt_enrt_rslt_id, -- 10415806
prv.rt_val, -- 10415806
prv.object_version_number
from ben_prtt_rt_val prv
where prtt_rt_val_id = p_prtt_rt_val_id;
,p_dt_delete_mode => hr_api.g_zap
,p_acty_base_rt_id => l_future_prv.acty_base_rt_id
,p_amt => l_future_prv.rt_val);
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 ( ( c_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 <= c_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 ler.ler_id
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.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 ptp.start_date -- 10058794
FROM per_time_periods ptp
,per_all_assignments_f asg
WHERE ptp.regular_payment_date >= p_effective_start_date -- 10058794
and ptp.payroll_id = asg.payroll_id
AND ptp.start_date <= p_effective_start_date
AND p_business_group_id = asg.business_group_id
AND asg.primary_flag = 'Y'
AND asg.person_id = p_person_id
AND p_effective_start_date
between asg.effective_start_date
and asg.effective_end_date
order by ptp.regular_payment_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 1
from per_all_assignments_f asg,
per_all_assignments_f asg2
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 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 asg.assignment_id <> asg2.assignment_id
;
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 pil.per_in_ler_id
from ben_per_in_ler pil,
ben_per_in_ler pil1,
ben_ler_f ler
where pil1.per_in_ler_id = p_per_in_ler_id
and pil1.person_id = pil.person_id
and pil1.per_in_ler_id <> pil.per_in_ler_id
and pil.ler_id = ler.ler_id
and p_effective_date between
ler.effective_start_date and ler.effective_end_date
and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
and pil.per_in_ler_stat_cd not in('BCKDT', 'VOIDD')
order by pil.lf_evt_ocrd_dt desc;
select 'Y'
from ben_per_in_ler pil, ben_per_in_ler pil2
where pil.per_in_ler_id <> p_per_in_ler_id
/* Bug 8945818: Added 'or' condition. Check for future rates for the previous life event */
and ( (trunc(pil.lf_evt_ocrd_dt, 'MM') = trunc(pil2.lf_evt_ocrd_dt, 'MM')) or
('Y' = ( select 'Y' from ben_prtt_rt_val prv,
ben_prtt_enrt_rslt_f pen_n,
ben_prtt_enrt_rslt_f pen_o
where pen_n.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen_n.prtt_enrt_rslt_stat_cd is null
and pen_n.per_in_ler_id = p_per_in_ler_id
/* Bug 10419741; If for previous life event there are no rate records created, using the value returned
and pen_o.per_in_ler_id in (select prv3.per_in_ler_id
from ben_prtt_rt_val prv3,ben_prtt_enrt_rslt_f pen3
where prv3.ended_per_in_ler_id = p_per_in_ler_id
and pen3.prtt_enrt_rslt_id = prv3.prtt_enrt_rslt_id
and pen3.per_in_ler_id = prv3.per_in_ler_id
and pen3.enrt_cvg_strt_dt <= pen3.enrt_cvg_thru_dt
and pen3.pl_typ_id = pen_n.pl_typ_id
and pen3.prtt_enrt_rslt_stat_cd is null
and prv3.PRTT_RT_VAL_STAT_CD is null)
and pen_n.ptip_id = pen_o.ptip_id
and pen_o.prtt_enrt_rslt_stat_cd is null
and pen_o.enrt_cvg_strt_dt <= pen_o.enrt_cvg_thru_dt
and pen_o.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
/* Bug 10419741; If for previous life event there are no rate records created, using the value returned
and prv.per_in_ler_id in (select prv3.per_in_ler_id
from ben_prtt_rt_val prv3,ben_prtt_enrt_rslt_f pen3
where prv3.ended_per_in_ler_id = p_per_in_ler_id
and pen3.prtt_enrt_rslt_id = prv3.prtt_enrt_rslt_id
and pen3.per_in_ler_id = prv3.per_in_ler_id
and pen3.enrt_cvg_strt_dt <= pen3.enrt_cvg_thru_dt
and pen3.pl_typ_id = pen_n.pl_typ_id
and pen3.prtt_enrt_rslt_stat_cd is null
and prv3.PRTT_RT_VAL_STAT_CD is null)
/*Bug 10137678: While adjusting the rates for overlapping rates, check for any rates that are end dating in the future
in addition to checking for the rates that are starting the future */
and ( (c_rt_strt_dt <= prv.rt_strt_dt)
or (prv.rt_strt_dt <= prv.rt_end_dt and c_rt_strt_dt <= prv.rt_end_dt)
)
and rownum = 1
))
)
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 ecr.enrt_rt_id = p_enrt_rt_id -- Bug 8300620
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;
election_rate_information and the same is not updated back to ben_enrt_rt table. So we cannot use the
min(rt_strt_dt) from ben_enrt_rt table to adjust the rates since the actual RT_STRT_DT has changed.
Instead since the adjustment is always done just before creating the new rate, we already know the new rt_strt_dt
from which we are going to create the new rate i.e. the variable l_enrt_rt.rt_strt_dt passed to create_prtt_rt_val.
The same should be used for adjusting the ended rates.*/
cursor c_get_ptip_id is
select 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 ecr.enrt_rt_id = p_enrt_rt_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;
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;
select prv.* from
ben_prtt_rt_val prv
where prv.prtt_rt_val_id = c_prtt_rt_val_id
and prv.rt_end_dt > c_rt_end_dt
--and prv.ENDED_PER_IN_LER_ID <> c_per_in_ler_id
and prv.prtt_rt_val_stat_cd is null
and prv.acty_base_rt_id = c_acty_base_rt_id
and prv.rt_end_dt <> hr_api.g_eot
and not exists
(select '1' from ben_le_clsn_n_rstr bkp
where BKUP_TBL_TYP_CD = 'BEN_PRTT_RT_VAL_ADJ'
and BKUP_TBL_ID = c_prtt_rt_val_id
and bkp.PER_IN_LER_ENDED_ID = c_per_in_ler_id
and bkp.acty_base_rt_id = c_acty_base_rt_id);
select oipl.opt_id
from ben_oipl_f oipl
where oipl.oipl_id = p_oipl_id
and business_group_id = p_business_group_id
and p_rt_strt_dt between oipl.effective_start_date and oipl.effective_end_date;
* 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
);
hr_utility.set_location('inserting into backup table ',1999);
insert into BEN_LE_CLSN_N_RSTR (
person_id,
BKUP_TBL_TYP_CD,
BKUP_TBL_ID,
per_in_ler_id,
PER_IN_LER_ENDED_ID,
RT_END_DT,
business_group_id,
object_version_number)
values (
p_person_id, -- Bug 16396102
'BEN_PRTT_RT_VAL_ADJ',
p_prtt_rt_val_id,
l_future_end_rec.per_in_ler_id,
p_per_in_ler_id,
l_future_end_rec.rt_end_dt,
p_business_group_id,
l_future_end_rec.object_version_number
);
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_enrt_rt.rt_strt_dt - 1 --Bug 8507247
,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_enrt_rt.rt_strt_dt - 1 --Bug 8507247
,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 ler.typ_cd from
ben_per_in_ler pil,ben_ler_f ler
where pil.ler_id=ler.ler_id
and p_effective_date between ler.effective_start_date
and ler.effective_end_date
and pil.per_in_ler_id=c_per_in_ler_id;
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)
and not exists
(select '1' from ben_le_clsn_n_rstr bkp where
bkp.per_in_ler_id = pen.per_in_ler_id
and bkp.effective_end_date = pen.effective_end_date
and bkp.BKUP_TBL_ID = pen.prtt_enrt_rslt_id
and bkp.enrt_cvg_thru_dt = pen.enrt_cvg_thru_dt
and bkp.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_ADJ');
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 pdp.effective_end_date <> hr_api.g_eot
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.per_in_ler_id <> p_per_in_ler_id -- 12583091
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 null
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.per_in_ler_id = p_per_in_ler_id
and pen.sspndd_flag = 'Y'
and pen.prtt_enrt_rslt_stat_cd is null
and pen.rplcs_sspndd_rslt_id = p_prtt_enrt_rslt_id
and pen.business_group_id = p_business_group_id;
select pen.*
from ben_prtt_enrt_rslt_f pen
,ben_elig_per_elctbl_chc epe
where nvl(epe.pgm_id, -1) = nvl(pen.pgm_id, -1)
and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id -- 13542327
and epe.pl_id = pen.pl_id
and nvl(epe.oipl_id, -1) = nvl(pen.oipl_id, -1)
and pen.per_in_ler_id = p_per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.business_group_id = p_business_group_id
and pen.enrt_cvg_thru_dt <> hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot;
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 ler.typ_cd
from ben_ler_f ler
where ler_id = p_ler_id
and ler.business_group_id = p_business_group_id;
select pil.per_in_ler_id
from ben_per_in_ler pil,
ben_per_in_ler pil1,
ben_ler_f ler,
ben_elig_per_elctbl_chc epe
where pil1.per_in_ler_id = epe.per_in_ler_id
and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and pil1.person_id = pil.person_id
and pil1.per_in_ler_id <> pil.per_in_ler_id
and pil.ler_id = ler.ler_id
and p_effective_date between
ler.effective_start_date and ler.effective_end_date
and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
and pil.per_in_ler_stat_cd not in('BCKDT', 'VOIDD')
order by pil.lf_evt_ocrd_dt desc;
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
/* Bug 8945818: Added 'or' condition. Check for future coverage for the previous life event */
and ( (trunc(pil.lf_evt_ocrd_dt, 'MM') = trunc(pil2.lf_evt_ocrd_dt, 'MM')) or
('Y' = (select 'Y' from ben_elig_per_elctbl_chc epe1,
ben_elig_per_elctbl_chc epe2,
ben_prtt_enrt_rslt_f pen
where epe1.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe1.per_in_ler_id <> epe2.per_in_ler_id
and epe2.per_in_ler_id = l_prev_pil_id
and epe1.ptip_id = epe2.ptip_id
and epe2.prtt_enrt_rslt_id is not null
and epe2.per_in_ler_id = pen.per_in_ler_id
and pen.person_id = pil.person_id
and epe2.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and c_cvg_strt_dt <= pen.enrt_cvg_strt_dt
and rownum = 1) )
)
and pil2.per_in_ler_id = epe.per_in_ler_id
and pil.person_id = pil2.person_id -- Bug 8871911: Performance Bug
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 '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
/* Bug 8945818: Added 'or' condition. Check for future coverage for the previous life event */
and ( (trunc(pil.lf_evt_ocrd_dt, 'MM') = trunc(pil2.lf_evt_ocrd_dt, 'MM')) or
('Y' = (select 'Y' from ben_elig_per_elctbl_chc epe1,
ben_elig_per_elctbl_chc epe2,
ben_prtt_enrt_rslt_f pen
where epe1.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe1.per_in_ler_id <> epe2.per_in_ler_id
and epe2.per_in_ler_id = l_prev_pil_id
and epe1.ptip_id = epe2.ptip_id
and epe2.prtt_enrt_rslt_id is not null
and epe2.per_in_ler_id = pen.per_in_ler_id
and pen.person_id = pil.person_id
and epe2.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
-- and c_cvg_strt_dt <= pen.enrt_cvg_strt_dt
--ud changes start
and (pen.enrt_cvg_strt_dt <= pen.enrt_cvg_thru_dt and c_cvg_strt_dt <= pen.enrt_cvg_thru_dt)
--ud changes end
and rownum = 1) )
)
and pil2.per_in_ler_id = epe.per_in_ler_id
and pil.person_id = pil2.person_id -- Bug 8871911: Performance Bug
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;
(Ex:First of Pay Period after Effective Date) before creating the new enrollment and is not updated to the choice.
Since we already know of the new cvg_strt_dt from which we are going to create the new enrollment the same can be used to
adjust the ended coverage. This will prevent any coverage gaps.*/
cursor c_get_ptip_id is
select 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;
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;
SELECT *
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.per_in_ler_id = p_per_in_ler_id
AND pen.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_id = p_pen_id
AND pen.enrt_cvg_strt_dt = p_cvg_strt_dt
and pen.sspndd_flag <> 'Y'
and not EXISTS(SELECT NULL
FROM ben_prtt_enrt_rslt_f pen2
WHERE pen2.per_in_ler_id = pen.per_in_ler_id
AND pen2.rplcs_sspndd_rslt_id = pen.prtt_enrt_rslt_id
AND pen2.sspndd_flag = 'Y'
AND pen2.prtt_enrt_rslt_stat_cd IS NULL
AND pen2.business_group_id = pen.business_group_id)
AND pen.prtt_enrt_rslt_stat_cd IS NULL
order by pen.effective_start_date desc;
SELECT bkup.*
FROM ben_le_clsn_n_rstr bkup
WHERE bkup.bkup_tbl_id = p_pen_id
AND bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F_CORR'
AND bkup.per_in_ler_id = p_pil_id
AND bkup.person_id = p_person_id
AND bkup.business_group_id = p_business_group_id
AND bkup.effective_start_date = p_esd
AND bkup.enrt_cvg_strt_dt = p_csd
AND bkup.effective_end_date = hr_api.g_eot
AND bkup.enrt_cvg_thru_dt = hr_api.g_eot;
select max(pen.object_version_number)
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_pen_id
and pen.per_in_ler_id = p_pil_id
and pen.business_group_id = p_business_group_id
and pen.effective_start_date = p_esd
order by pen.effective_start_date desc,pen.object_version_number desc;
SELECT pil.per_in_ler_id
FROM ben_per_in_ler pil
WHERE pil.business_group_id = p_business_group_id
AND pil.person_id = p_person_id
AND pil.per_in_ler_stat_cd = 'PROCD'
ORDER BY pil.lf_evt_ocrd_dt DESC ;
SELECT pil.per_in_ler_id
FROM ben_per_in_ler pil
WHERE pil.business_group_id = p_business_group_id
AND pil.person_id = p_person_id
AND pil.per_in_ler_stat_cd = 'PROCD'
and exists(select null
from ben_prtt_enrt_rslt_f pen
where pen.per_in_ler_id = pil.per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.prtt_enrt_rslt_id = p_pen_id)
ORDER BY pil.lf_evt_ocrd_dt DESC ;
SELECT *
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.per_in_ler_id = p_pil_id
AND nvl(pen.pgm_id,-1) = nvl(p_pgm_id,-1)
AND pen.pl_id = p_pl_id
AND Nvl(pen.oipl_id ,-1) = nvl(p_oipl_id,-1)
AND pen.business_group_id = p_business_group_id
and pen.enrt_cvg_thru_dt = hr_api.g_eot
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND EXISTS(SELECT NULL
FROM ben_prtt_enrt_rslt_f pen2
WHERE pen2.per_in_ler_id = pen.per_in_ler_id
AND pen2.rplcs_sspndd_rslt_id = pen.prtt_enrt_rslt_id
AND pen2.sspndd_flag = 'Y'
AND pen2.prtt_enrt_rslt_stat_cd IS NULL
and pen2.enrt_cvg_thru_dt = hr_api.g_eot
AND pen2.business_group_id = pen.business_group_id);
ben_ELIG_PER_ELC_CHC_api.update_ELIG_PER_ELC_CHC
(p_validate => FALSE
,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
,p_prtt_enrt_rslt_id => l_ended_pen.prtt_enrt_rslt_id
,p_object_version_number => l_global_epe_rec.object_version_number
,p_effective_date => p_effective_date
,p_request_id => fnd_global.conc_request_id
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_program_update_date => sysdate
);
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;
/* Bug 14803995 : Do not insert future coverage records in backup table if the Life event is Unrestricted */
open c_unrest(l_global_epe_rec.per_in_ler_id);
hr_utility.set_location('inserting into backup table',11);
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_ADJ',
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.delete_prtt_enrt_result
(p_validate => false,
p_prtt_enrt_rslt_id => l_get_prev_pil_enrt.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_get_prev_pil_enrt.object_version_number,
p_effective_date => l_get_prev_pil_enrt.effective_start_date,
p_datetrack_mode => hr_api.g_future_change,
p_multi_row_validate => FALSE);
--Update the pen record with the current pil to previous pil
ben_prtt_enrt_result_api.update_prtt_enrt_result
(p_validate => FALSE
,p_prtt_enrt_rslt_id => l_check_enrt_same_pil.prtt_enrt_rslt_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_per_in_ler_id => l_bkup_pen_rec.per_in_ler_id
,p_enrt_cvg_thru_dt => l_bkup_pen_rec.enrt_cvg_thru_dt
,p_object_version_number => l_check_enrt_same_pil.object_version_number
,p_effective_date => l_bkup_pen_rec.effective_start_date
,p_datetrack_mode => 'CORRECTION'
,p_business_group_id => p_business_group_id
,p_multi_row_validate => FALSE );
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 => l_get_enrt_rslts_for_pen.effective_start_date,--p_effective_date,
/*Bug 9057101: Instead of passing p_effective_date, pass
effective start date of the pen_id*/
p_datetrack_mode => hr_api.g_correction,
p_multi_row_validate => FALSE,
p_enrt_cvg_thru_dt => l_global_epe_rec.enrt_cvg_strt_dt - 1 -- Bug 8507247
);
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 => l_get_enrt_rslts_for_pen.effective_start_date,--p_effective_date,
p_datetrack_mode => hr_api.g_correction,
p_multi_row_validate => FALSE,
p_enrt_cvg_thru_dt => l_global_epe_rec.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);