The following lines contain the word 'select', 'insert', 'update' or 'delete':
it's above the max but then update
the choice to be not electable. (5095)
09-May-00 lmcdonal 115.62 ctfn's at the option level are written
in benchctf.pkb. don't write them at
the bnft level too. Bug 1277371.
12-May-00 lmcdonal 115.63 Bug 1249901 - when coverage is entered
at enrollment, we don't know if ctfns
are required until during enrt. Write
ctfn records, load new mx_wout_ctfn
field. Also override cvg-calc-mx
with rstrn-mx-with-ctfn val when writing
enrt-bnft row.
14-May-00 shdas 115.64 Bug 5152.Moved rounding function from
write coverage to main so that cvg value gets
rounded before applying variable profile.
18-May-00 jcarpent 115.65 Bug 5209. If choice is updated to be
not electable also make not the default.
Also, deenroll from the result if
above the max.
24-May-00 tmicheal 115.66 bug 4844 fixd
05-Jun-00 stee 115.67 When writing certifications, do
not restrict by the
bnft_or_option_retrctn_cd. There
is a check in the write_ctfn
procedure for duplicate
certification. wwbug #1308629.
20-Jul-00 bbulusu 115.68 Bug Fix 5368. Was not checking
for enter at enrollment when
comparing enrolled benefit amount
to null computed value.
25 Sep 00 mhoyes 115.69 - Commented out nocopy highly executed hr_utility
.set_locations.
07 Nov 00 mhoyes 115.70 - Referenced electable choice performance
APIs.
- Referenced comp object loop electable
choice context globals.
22 Nov 00 mhoyes 115.71 - Forward port of 1473060 on 115.69.
22 Nov 00 mhoyes 115.72 - Reinstated 115.70.
21 Nov 00 jcarpent 115.73 - bug 1470467. Check for provided
ctfns if over max. Leapfrog
based on 115.69
22 Nov 00 jcarpent 115.74 - Merged version with 115.70 and 115.71.
23-Jan-01 mhoyes 115.75 - Added calculate only mode for EFC.
21-Feb-01 kmahendr 115.76 - Bug#1569790 - Benefit row is inserted
for interim coverage if multi_cd is Flat
amt with enter value at enrollment flag on
22-Mar-01 mhoyes 115.77 - Called epecobjtree_getcvgdets to get l_cvg.
27-mar-01 tilak 115.78 - bug : 1433393 ,ultm lwr limit and upr limit
column added in vapro
the resilt of vapro and cvg validated against the
ultiamte limit
03-apr-01 tilak 115.79 bug : 1712464 ultm lwr limit rl,and upr limit rl
added
02-Aug-01 ikasire 115.80 Bug 1895846 fixes for suspended enrollments
02-Aug-01 ikasire 115.81 to add modification history for 115.80
28-Aug-01 kmahendr 115.82 Bug#1936976-result_id is populated based on
benefit amount
29-aug-01 tilak 115.83 bug:1949361 jurisdiction code is
derived inside benutils.formula.
27-Sep-01 kmahendr 115.84 Bug#1981673-Added parameter ann_mn_elcn_val and
ann_mx_elcn_val to ben_determine_variable_rates
call
02-nov-01 tjesumic 115.85 bug : 2083726 fixed , if variable rate define
and the treatment code is replace then
default valu , increment value , min , max taken from
variable rate
14-nov-01 tjesumic 115.86 DBDRV added
21-nov-01 tjesumic 115.87 default valu , increment value , min , max taken from
variable rat for REPLC , SF , ADDTO,MB
21-nov-01 tjesumic 115.88 correction in REPLC , SF , ADDTO,MB calculation
28-Dec-01 jcarpent 115.89 - Bug:2157614: Altered cursor
c_other_oipls_in_plan_type for
performance issue.
28-Dec-01 jcarpent 115.90 - Bug#1910867: Fixed code to handle
dschwart/cdaniels condition when the maximum amount is NULL.
28-Dec-01 dschwart/ 115.91 - Bug#2054078: Max Coverage without certification
BBurns/jcarpent was not showing up on form because it was not
being written to ben_enrt_bnft. Made modifications
to chk_bnft_ctfn_val parameters + related code to
fix this.
26-Mar-02 kmahendr 115.92 - Bug#1833008 - Added parameter p_cal_for to the call
determine_compensation to handle multi-assignment.
22-Apr-02 hnarayan 115.92 Bug 2319790 - changed chk_bnft_ctfn procedure
29-May-02 shdas 115.93 Bug 2325109 - Message token not sho wing values.
08-Jun-02 pabodla 115.94 Do not select the contingent worker
assignment when assignment data is
fetched.
08-AUg-02 ikasire 115.95 Bug 2502633 Interim Enhancements
Added logic to create additional benefit
row for the purpose of interim enrollment.
03-Sep-02 ikasire 115.96 Bug 2543071 interim codes Current Same Option
in plan typo.wrong parameter passed.
05-Sep-02 ikasire 115.97 Bug 2551711 Getting default amount instead of
getting min amount for interim
06-Sep-02 ikasire 115.98 need to populate current pen_id on the
enb record created for SAME case
13-Sep-02 tjesumic 115.99 if the dflt enrt cd is rule and global variable
g_dflt_elctn_val has the value then the global
value is used for default value,global value
is populated in bendenrr # 2534744
17-Sep-02 hnarayan 115.100 bug 2560721 - added code for setting dflt_flag
when coverage is Flat Range , for new enrollments
28-Sep-02 ikasire 115.101 Bug 2600087 added nvl(p_lf_evt_ocrd_dt,
p_effective_date) to the cursors
18-Dec-02 mmudigon 115.102 Bug 2708100 Changes to Flat Range
(FLRNG) calculations.
Old Flat Rng :
Min ---> Max
New Flat Rng :
Lower Limit --> Upper Limit
23-DEC-02 lakrish 115.104 NOCOPY changes
03-Jan-03 kmahendr 115.105 Bug#2708285 - the limit is checked after adding
the flat amount for CLPFLRNG calculation
15-Jan-03 tjesumic 115.106 bug#2736519 new parameter added in chk_bnft_ctfn
p_no_mn_cvg_amt_apls_flag,p_no_mx_cvg_amt_apls_flag
if the max is 'Y' the l_mx_amt is not validated
10-Mar-03 pbodla 115.107 If the coverage calculation method
is rule then pass the option id
by fetching it if it's not already
fetched.
13-Aug-03 kmahendr 115.108 Added cvg_mlt_cd ERL
14-Aug-03 vsethi 115.109 Bug#3095224 - the limit is checked after adding
the flat amount for FLFXPCL, FLPCLRNG calculation
25-Aug-03 kmahendr 115.111 Bug#3108422 - ctfn calls made for RL and ERL
26-Aug-03 kmahendr 115.112 Bug#3108422 - ctfn_rqd_flag changed for ERL
02-Oct-03 ikasire 115.113 Bug#3132792 - Enrollment is not suspending if
certification was received for the plan in
lower amount.
31-Oct-03 kmahendr 115.114 Bug#3230649 - added primary_flag condition and
order by in cursors c_asg.
15-Mar-04 rpgupta 115.115 Bug 3497676 - Allow a 0 coverage to be created
08-Apr-04 kmahendr 115.116 Bug3560065 - check for other_pl_cvg removed
17-Mar-04 pbodla 115.117 FONM : use cvg_strt_dt changes .
22-Apr-04 kmahendr 115.118 Bug#3585768 - limitation check removed
01-Jul-04 ikasire 115.119 Bug 3695079 We need to take the min of
mx with cert and increases plus current benefit
amount in deriving the max with cert amount
09-Aug-04 kmahendr 115.120 Bug#3772179 - Max without certification is
assigned a value in chk_bnft_ctfn.
05-Aug-04 tjesumic 115.121 FONM for ben_derive_factors fonm date paseed as param
17-Aug-04 hmani 115.122 Added newly added lookup codes 3806262
23-Aug-04 mmudigon 115.123 Excluding sspndd enrollmets in
cursor c_current_enrt
2534391 :NEED TO LEAVE ACTION ITEMS
15-Nov-04 kmahendr 115.124 Unrest. enh changes
01-Dec-04 abparekh 115.125 Bug 3828288 : For Flat Amount Calc Method consider Max Amount
as minimum of CCM Max, PLN Max WCfn, ( Current + PLN Max Incr WCfn )
01-dec-04 kmahendr 115.126 Unrest. enh changes
27-Dec-04 abparekh 115.127 Bug 3828288 : For Flat Amount Calc Method consider Max Increase
Amount only for Currently Enrolled records.
11-Jan-05 ikasire 115.128 CF Interim Suspended BUG 4064635 Multiple Changes
18-Jan-05 ikasire 115.129 CF Interim Suspended more changes
01-Feb-05 ikasire 115.130 CF Changes for 'SM' case
23-Feb-05 kmahendr 115.131 p_calculate_only_mode added not to
create_enrt_bnft when the value is false
07-Mar-05 ikasire 115.132 Bug 4223840 Flat Fixed Enter Value at enrollment
issue with mx_wout_ctfn_val issue
25-Mar-05 ikasire 115.133 Bug 4262970. Default is not identified properly in the
subsequent life event.
27-Mar-05 ikasire 115.134 Bug 4262970 - extend the fix to other flat range codes
28-Mar-05 kmahendr 115.135 Bug#4256191 - least of max, upper_limit_value
sent to get_least_val procedure
30-Mar-05 kmahendr 115.136 Bug#4256191 - rule evaluated before
determining the least amount
18-Apr-05 ssarkar 115.137 Bug# 4275929 : max_witout_cert is passed to max_with_cert ,
when null, while calling get_least_value proc.
06-jun-05 nhunur 115.138 4383988 - apply fnd_number on what FF returns in numeric variable.
30-Jun-05 ikasire 115.139 BUG 4449437 always setting the default flag to N for FLFXPCL
19-jul-05 rgajula 115.140 Bug 4436573 Added a new parameter p_cvg_mlt_cd to the
ben_determine_coverage.chk_bnft_ctfn to perform few more
validations to set the certification required flag of ben_enrt_bnft.
25-Sep-05 kmahendr 115.141 Bug#4630782 - in unrestricted mode inelig
process not called
27-Sep-05 ikasire 115.142 Bug 4627840
04-Oct-05 ikasire 115.143 Bug 4649262 ERL fix regression from 4436573
20-oct-05 rgajula 115.144 Bug 4644489 Added an additional parameter p_rstrn_mn_cvg_rqd_amt
ben_determine_coverage.chk_bnft_ctfn to return the minimum coverage amount
as calculated by the minium rule in coverage restrictions, the out parameter will
will return the amount as calculated by the rule or the min value in the min feild in retrictions.
This returned value is used in the write record for Flat Amount Calculation method as
max(cvg_rstrn_amount,cvg_calc_mthd_min_amnt).
16-Jan-06 abparekh 115.145 Bug 4954541 : Unrestricted : If coverage does not exist, then delete
corresponding BEN_ENRT_BNFT record
11-Apr-06 bmanyam 115.147 5105118 changes (115.146) are undone
This is leapfrog of 115.145.
5105118 is not-a-bug.
May 24, 2006 rgajula 115.148 Bug 5236985 Added the l_cvg_rstn_max_with_cert not null condition
This necessary if the l_rstrn.mx_cvg_wcfn_amt is
made null in the subsequent life event
28-Jun-2006 swjain 115.149 Bug 5331889 Added person_id param to mn_cvg_rl and mx_cvg_rl
in procedure chk_bnft_ctfn
11-Aug-2006 nhunur 115.150 Ult Lower limit value rule is not honoured
28-sep-2006 ssarkar 115.151 bug 5529258 : interim rule to be evaluated
29-sep-2006 ssarkar 115.152 5529258 : more fix
4-Oct-2006 rgajula 115.154 Bug 5573274 : Populated the mx_wout_ctfn value for Flat Range.
12-Oct-2006 ikasired 115.154 Bug 5596918 fix for 'SAME' part in carryforward. bnft amt null issue
12-Oct-2006 ikasired 115.154 Bug 5596907 fix for carryforward to use right bnft record
3-Nov-2006 rgajula 115.156 Bug 5637851 : Added nvl clauses in chk_bnft_ctfn.
27-Nov-2006 rgajula 115.157 Bug 5679097: Fix of regression for bug 5637851
23-Feb-2007 swjain 115.158 Bug 5887665: Passed ctfn_determine_cd in call to
ben_determine_chc_ctfn.write_ctfn in procedure main
02-Mar-2007 ssarkar 115.159 Bug 5900235 : write_ctfn to be called for RL if ctfn_rqd is true
22-Mar-2007 rgajula 115.161 Bug 5942733 : Removed the NVL clause which was causing the max_wout_cert to be set to max and causing unnecessary UI issues.
28-Mar-2007 swjain 115.162 Bug 5933576 : Added code in procedure main to check benefit certifications for 'NSVU' calculation method as well
30-Mar-2007 swjain 115.163 Bug 5933576 : When vr_val is null, then set it to default val in procedure main
17-May-2007 rtagarra 115.164 Bug 6054310 : Moved the condition added in 115.163 to NSVU Case only.
22-may-2007 nhunur 115.165 6066580: FLFXPCL do rounding after addition of flat amount
20-Jun-2007 rtagarra 115.166 Bug 6068097 : Made changes for the code NSDCS and NNCS.
04-Jul-2007 rtagarra 115.167 Bug 6164688 : For codes 'INCRCTF','DECRCTF' no need to check for order_number for calculation.
24-Oct-2007 bmanyam 115.168 6502657 :
If Coverage of more than one option
in same Plan is reduced due to
Upper Limit, then only one of
them is retained as Electable.
25-Oct-2007 sshetty Fixed bug 6523477
12-May-2008 For bug 7004909, changed the default value of coverage amount that was being passed to write coverages function for the type
Multiple of compensation
23-Jan-2009 sallumwa Bug 7490759 :Modified the cursor c_cf_bnft_result to fetch the correct suspended
result ID when FONM and Event date LE's are processed in the same month.
02-Feb-2009 sallumwa 115.172 Bug 7704956 : Modified the procedure main to update the
currently_enrld_flag in ben_enrt_bnft table to 'Y' when the
benefit amount matches if multiple rows are set to 'Y' corresponding
to one elig_per_elctbl_chc_id.
11-Feb-2009 velvanop 115.173 Bug 7414757: Added parameter p_entr_val_at_enrt_flag.
VAPRO rates which are 'Enter value at Enrollment', Form field
should allow the user to enter a value during enrollment.
04-May-2009 sallumwa 115.174 Bug 8453712 : Modified cursor c_cf_bnft_result to carry forward the latest pen row.
02-Jun-2009 sagnanas 115.175 8567963 - Modified cursor c_cf_bnft_result
09-Jun-2009 krupani 115.176 Bug 8568862 : For NSVU, default cvg value was not getting considered. Fixed the same.
29-Jun-2009 sallumwa 120.31.12010000.9 Bug 8716693 : Enhancement
EOI Does not Carry Forward for Life Events that Dont have EOI Configuration
05-Aug-2009 sallumwa 120.31.12010000.10 Bug 8767376 : Handled the Flat Range cases for the coverage for the above Enhancement
20-Oct-2009 krupani 120.31.12010000.11 Bug 9008389 : 12.1 forward port of 8940075
07-Feb-2009 sallumwa 120.31.12010000.12 Bug 9308931 : Extended the fix done for the bug 7704956 to
OAB life events also.
10-Mar-2010 sagnanas 120.31.12010000.13 Bug 9434155 : Handled currently_enrld_flag and defaulting case for CLRNG
01-Jul-2010 velvanop 120.31.12010000.14 Bug 9774647:When salary change event occurs, update the coverage amount of the interim with the newly calculated value
17-Feb-2011 amnaraya 120.31.12010000.15 Bug #8501248: Set the min, max, increment and default values as well as the enter value at enrollment flag for the variable
coverage profile with calculation method NSVU
06-Jun-2011 usaraswa 120.31.12010000.16 Bug 14137476: If the benefit amount is enter value at enrollment then for the elig_per_elctbl_chc_id there should be a record
present in ben_elctbl_chc_ctfn table.
*/
-------------------------------------------------------------------------------------------------------------------------------------------------
--
g_package varchar2(80) := 'ben_determine_coverage';
select asg.assignment_id,asg.organization_id
from per_all_assignments_f asg
where asg.person_id = p_person_id
and asg.primary_flag = 'Y'
and asg.assignment_type <> 'C'
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between asg.effective_start_date
and asg.effective_end_date
order by decode(asg.assignment_type, 'E',1,'B',2,3);
select epe.business_group_id,
epe.pgm_id,
epe.pl_id,
epe.pl_typ_id,
epe.ptip_id,
epe.plip_id,
epe.oipl_id,
epe.prtt_enrt_rslt_id,
pil.ler_id,
epe.object_version_number,
epe.elctbl_flag,
epe.per_in_ler_id
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.per_in_ler_id = pil.per_in_ler_id;
select pen.bnft_amt,
pen.bnft_ordr_num
from ben_prtt_enrt_rslt_f pen,
ben_elig_per_elctbl_chc epe
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.prtt_enrt_rslt_id = l_rslt_id
and pen.prtt_enrt_rslt_id = l_rslt_id;
select oipl.opt_id
from ben_oipl_f oipl
where oipl.oipl_id = cv_oipl_id
and cv_effective_date
between oipl.effective_start_date
and oipl.effective_end_date;
select 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.primary_flag = 'Y'
and asg.assignment_type <> 'C'
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between asg.effective_start_date
and asg.effective_end_date
and asg.business_group_id =p_business_group_id
order by decode(asg.assignment_type, 'E',1,'B',2,3);
select enb.val
from ben_elig_per_elctbl_chc epe,
ben_enrt_bnft enb
where epe.pl_id=l_epe.pl_id
and nvl(epe.pgm_id,-1)=nvl(l_epe.pgm_id,-1)
and epe.per_in_ler_id=l_epe.per_in_ler_id
and epe.business_group_id=l_epe.business_group_id
and enb.elig_per_elctbl_chc_id=epe.elig_per_elctbl_chc_id
and enb.cvg_mlt_cd=p_cvg_mlt_cd
and enb.business_group_id=epe.business_group_id
and enb.val=l_val
;
select enb.val
from ben_elig_per_elctbl_chc epe,
ben_enrt_bnft enb
where epe.ptip_id=l_epe.ptip_id
and nvl(epe.pgm_id,-1)=nvl(l_epe.pgm_id,-1)
and epe.per_in_ler_id=l_epe.per_in_ler_id
and epe.business_group_id=l_epe.business_group_id
and enb.elig_per_elctbl_chc_id=epe.elig_per_elctbl_chc_id
and enb.cvg_mlt_cd=p_cvg_mlt_cd
and enb.business_group_id=epe.business_group_id
and enb.val=l_val
;
select enb.val
from ben_elig_per_elctbl_chc epe,
ben_oipl_f oipl,
ben_oipl_f oipl2,
ben_enrt_bnft enb
where epe.ptip_id=l_epe.ptip_id
and nvl(epe.pgm_id,-1)=nvl(l_epe.pgm_id,-1)
and epe.pl_typ_id=l_epe.pl_typ_id
and epe.per_in_ler_id=l_epe.per_in_ler_id
and epe.business_group_id=l_epe.business_group_id
and oipl.oipl_id=epe.oipl_id
and oipl.business_group_id=epe.business_group_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between
oipl.effective_start_date and oipl.effective_end_date
and oipl2.oipl_id=l_epe.oipl_id
and oipl2.business_group_id=p_business_group_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between
oipl2.effective_start_date and oipl2.effective_end_date
and oipl2.opt_id=oipl.opt_id -- why we join to oipls
and enb.elig_per_elctbl_chc_id=epe.elig_per_elctbl_chc_id
and enb.cvg_mlt_cd=p_cvg_mlt_cd
and enb.business_group_id=epe.business_group_id
and enb.val=l_val
;
select '1', mx_enrd_alwd_ovrid_num
from ben_ptip_f
where ptip_id=l_epe.ptip_id
and business_group_id=l_epe.business_group_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between
effective_start_date and effective_end_date
and mx_enrd_alwd_ovrid_num is not null
union
select '2', mx_enrl_alwd_num
from ben_pl_typ_f
where pl_typ_id=l_epe.pl_typ_id
and business_group_id=l_epe.business_group_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between
effective_start_date and effective_end_date
and mx_enrl_alwd_num is not null
order by 1
;
select 'Y'
from ben_elig_per_elctbl_chc epe
where epe.pl_typ_id=l_epe.pl_typ_id
and epe.pl_id<>l_epe.pl_id
and nvl(epe.pgm_id,-1)=nvl(l_epe.pgm_id,-1)
and epe.oipl_id is not null
and epe.business_group_id=p_business_group_id
;
select 'Y'
from ben_pl_f pln,
ben_oipl_f oipl
where pln.pl_typ_id=l_epe.pl_typ_id
and pln.pl_id<>l_epe.pl_id
and cv_effective_date between -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date) between
pln.effective_start_date and pln.effective_end_date
and pln.business_group_id=p_business_group_id
and oipl.pl_id=pln.pl_id
and oipl.business_group_id=p_business_group_id
and cv_effective_date between -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date) between
oipl.effective_start_date and oipl.effective_end_date
;
select 'Y'
from ben_ler_f ler
where ler.typ_cd = 'SCHEDDU'
and ler.ler_id = p_ler_id
and p_effective_date between ler.effective_start_date
and ler.effective_end_date;
SELECT pen.prtt_enrt_rslt_id,
pen.bnft_amt,
pen.bnft_ordr_num
FROM ben_prtt_enrt_rslt_f pen,
ben_ler_f ler
WHERE pen.ler_id = ler.ler_id
AND pen.person_id = p_person_id
AND pen.business_group_id = p_business_group_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
--AND pen.effective_end_date = hr_api.g_eot
--8567963
AND ((pen.effective_end_date = hr_api.g_eot
and ler.typ_cd <> 'SCHEDDU')
or ( cv_effective_date between pen.effective_start_date and pen.effective_end_date
and ler.typ_cd = 'SCHEDDU'))
--8567963
AND cv_effective_date <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND NVL(l_epe.oipl_id,-1) = NVL(pen.oipl_id,-1)
AND l_epe.pl_id = pen.pl_id
AND NVL(l_epe.pgm_id,-1) = NVL(pen.pgm_id,-1)
AND ( pen.bnft_ordr_num = p_ordr_num OR
pen.bnft_amt = p_val )
----------Bug 7490759
AND (pen.sspndd_flag = 'N' --CFW
OR (pen.sspndd_flag = 'Y' and
pen.enrt_cvg_thru_dt = hr_api.g_eot
)
)
-- order by pen.enrt_cvg_strt_dt,decode(pen.sspndd_flag,'Y',1,2); --Bug 8453712
ben_elig_per_elc_chc_api.update_perf_elig_per_elc_chc(
p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
p_elctbl_flag => 'N',
p_dflt_flag => 'N',
p_object_version_number => l_epe.object_version_number,
p_effective_date => p_effective_date, -- FONM : no need to send the cvg strt dt
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_manage_unres_life_events.update_enrt_bnft
(
p_dflt_flag => p_dflt_flag,
p_val_has_bn_prortd_flag => 'N',
p_bndry_perd_cd => p_bndry_perd_cd,
p_bnft_typ_cd => p_bnft_typ_cd,
p_val => l_val,
p_nnmntry_uom => p_nnmntry_uom,
p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id,
p_business_group_id => p_business_group_id,
p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
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_comp_lvl_fctr_id => p_comp_lvl_fctr_id,
p_cvg_mlt_cd => p_cvg_mlt_cd,
p_crntly_enrld_flag => l_crnt_enrld_flag,
p_ctfn_rqd_flag => p_ctfn_rqd_flag,
p_entr_val_at_enrt_flag => p_entr_val_at_enrt_flag,
p_mx_val => p_mx_val,
p_mx_wout_ctfn_val => p_mx_wout_ctfn_val,
p_mn_val => p_mn_val,
p_incrmt_val => p_incrmt_val,
p_dflt_val => p_dflt_val,
p_rt_typ_cd => p_rt_typ_cd,
p_program_update_date => sysdate,
p_enrt_bnft_id => l_enrt_bnft_id,
p_ordr_num => p_ordr_num);
p_program_update_date => sysdate,
p_enrt_bnft_id => l_enrt_bnft_id,
p_object_version_number => l_object_version_number,
p_ordr_num => p_ordr_num);
select person_id
from per_all_assignments_f
where assignment_id = p_assignment_id;
select pil.person_id,
epe.prtt_enrt_rslt_id, --5529258
epe.pgm_id,
epe.pl_id,
epe.pl_typ_id,
epe.oipl_id,
epe.plip_id,
epe.ptip_id,
epe.dflt_flag,
pil.per_in_ler_id,
pil.ler_id,
epe.business_group_id,
epe.crntly_enrd_flag,
epe.elctbl_flag,
epe.object_version_number
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.per_in_ler_id = pil.per_in_ler_id ;
select null
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.per_in_ler_id = pil.per_in_ler_id
and nvl(epe.in_pndg_wkflow_flag,'N') <> 'N' ;
select opt_id
from ben_oipl_f
where oipl_id = rqd_oipl_id
and cv_effective_date -- nvl(p_lf_evt_ocrd_dt,p_effective_date)
between effective_start_date
and effective_end_date;
select rstrn.ler_bnft_rstrn_id,
rstrn.pl_id,
rstrn.cvg_incr_r_decr_only_cd,
rstrn.mx_cvg_wcfn_amt,
rstrn.mx_cvg_wcfn_mlt_num,
rstrn.mx_cvg_mlt_incr_num,
rstrn.mx_cvg_mlt_incr_wcf_num,
rstrn.mx_cvg_incr_alwd_amt,
rstrn.mx_cvg_incr_wcf_alwd_amt,
rstrn.mn_cvg_amt mn_cvg_rqd_amt,
rstrn.mx_cvg_alwd_amt,
rstrn.mx_cvg_rl,
rstrn.mn_cvg_rl,
rstrn.no_mn_cvg_incr_apls_flag no_mn_cvg_amt_apls_flag,
rstrn.no_mx_cvg_amt_apls_flag no_mx_cvg_amt_apls_flag,
rstrn.dflt_to_asn_pndg_ctfn_cd, /*ENH*/
rstrn.dflt_to_asn_pndg_ctfn_rl -- 5529258
from ben_ler_bnft_rstrn_f rstrn
where rstrn.ler_id = l_ler_id
and rstrn.pl_id = l_pl_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between rstrn.effective_start_date
and rstrn.effective_end_date;
select null ler_bnft_rstrn_id,
pln.pl_id,
pln.cvg_incr_r_decr_only_cd,
pln.mx_cvg_wcfn_amt,
pln.mx_cvg_wcfn_mlt_num,
pln.mx_cvg_mlt_incr_num,
pln.mx_cvg_mlt_incr_wcf_num,
pln.mx_cvg_incr_alwd_amt,
pln.mx_cvg_incr_wcf_alwd_amt,
pln.mn_cvg_rqd_amt,
pln.mx_cvg_alwd_amt,
pln.mx_cvg_rl,
pln.mn_cvg_rl,
pln.no_mn_cvg_amt_apls_flag,
pln.no_mx_cvg_amt_apls_flag,
pln.dflt_to_asn_pndg_ctfn_cd, /*ENH*/
pln.dflt_to_asn_pndg_ctfn_rl --5529258
from ben_pl_f pln
where pln.pl_id = l_pl_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between pln.effective_start_date
and pln.effective_end_date;
select ctfn.rqd_flag,
ctfn.enrt_ctfn_typ_cd,
ctfn.ctfn_rqd_when_rl,
lbr.ctfn_determine_cd
from ben_ler_bnft_rstrn_ctfn_f ctfn, ben_ler_bnft_rstrn_f lbr -- Bug 5887665
where ctfn.ler_bnft_rstrn_id = l_ler_bnft_rstrn_id
and ctfn.ler_bnft_rstrn_id = lbr.ler_bnft_rstrn_id
and ctfn.business_group_id = l_business_group_id
and lbr.business_group_id = l_business_group_id
and cv_effective_date
between lbr.effective_start_date
and lbr.effective_end_date
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between ctfn.effective_start_date
and ctfn.effective_end_date;
select ctfn.rqd_flag,
ctfn.enrt_ctfn_typ_cd,
ctfn.ctfn_rqd_when_rl,
pln.ctfn_determine_cd
from ben_bnft_rstrn_ctfn_f ctfn, ben_pl_f pln -- Bug 5887675
where ctfn.pl_id = l_pl_id
and ctfn.pl_id = pln.pl_id
and ctfn.business_group_id = l_business_group_id
and pln.business_group_id = l_business_group_id
and cv_effective_date
between pln.effective_start_date
and pln.effective_end_date
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between ctfn.effective_start_date
and ctfn.effective_end_date;
select asg.assignment_id,asg.organization_id
from per_all_assignments_f asg
where asg.person_id = l_epe.person_id
and asg.primary_flag = 'Y'
and asg.assignment_type <> 'C'
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between asg.effective_start_date
and asg.effective_end_date
order by decode(asg.assignment_type, 'E',1,'B',2,3);
select region_2
from hr_locations_all loc,
per_all_assignments_f asg
where loc.location_id = asg.location_id
and asg.person_id = l_epe.person_id
and asg.primary_flag = 'Y'
and asg.assignment_type <> 'C'
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between asg.effective_start_date
and asg.effective_end_date
order by decode(asg.assignment_type, 'E',1,'B',2,3);
select bnft_ordr_num,
bnft_amt,
'Y'
from ben_prtt_enrt_rslt_f
where business_group_id = l_business_group_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between enrt_cvg_strt_dt
and enrt_cvg_thru_dt
and enrt_cvg_thru_dt <= effective_end_date
and prtt_enrt_rslt_stat_cd is null
and sspndd_flag = 'N'
and l_person_id = person_id
and l_pl_id = pl_id
and ((l_oipl_id is null and oipl_id is null) or
l_oipl_id = oipl_id)
order by decode(sspndd_flag,'Y',1,2) ; --CF Changes
select bnft_ordr_num,
bnft_amt,
'Y'
from ben_prtt_enrt_rslt_f
where business_group_id = l_business_group_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between enrt_cvg_strt_dt
and enrt_cvg_thru_dt
and enrt_cvg_thru_dt <= effective_end_date
and prtt_enrt_rslt_stat_cd is null
and sspndd_flag = 'Y'
and l_person_id = person_id
and l_pl_id = pl_id
and ((l_oipl_id is null and oipl_id is null) or
l_oipl_id = oipl_id)
order by decode(sspndd_flag,'Y',1,2) ; --CF Changes
select pl_id,oipl_id
from ben_prtt_enrt_rslt_f
where business_group_id = l_business_group_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between enrt_cvg_strt_dt
and enrt_cvg_thru_dt
and enrt_cvg_thru_dt <= effective_end_date
and sspndd_flag = 'N'
and prtt_enrt_rslt_stat_cd is null
and l_person_id = person_id
and c_pl_typ_id = pl_typ_id ;
select pl_id,oipl_id
from ben_prtt_enrt_rslt_f
where business_group_id = l_business_group_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between enrt_cvg_strt_dt
and enrt_cvg_thru_dt
and enrt_cvg_thru_dt <= effective_end_date
and sspndd_flag = 'N'
and prtt_enrt_rslt_stat_cd is null
and l_person_id = person_id
and c_pl_id = pl_id ;
select pl_id,oipl_id
from ben_prtt_enrt_rslt_f
where business_group_id = l_business_group_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between enrt_cvg_strt_dt
and enrt_cvg_thru_dt
and enrt_cvg_thru_dt <= effective_end_date
and sspndd_flag = 'N'
and prtt_enrt_rslt_stat_cd is null
and l_person_id = person_id
and c_oipl_id = oipl_id ;
select
pen.bnft_amt,
enb.*
from ben_prtt_enrt_rslt_f pen,
ben_enrt_bnft enb,
ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where pen.business_group_id = l_business_group_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between pen.enrt_cvg_strt_dt
and pen.enrt_cvg_thru_dt
and pen.enrt_cvg_thru_dt <= pen.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null
and pen.sspndd_flag = 'N' ---CF Changes
and l_person_id = pen.person_id
and c_pl_id = pen.pl_id
and ((c_oipl_id is null and pen.oipl_id is null) or
c_oipl_id = pen.oipl_id)
and pen.prtt_enrt_rslt_id = enb.prtt_enrt_rslt_id
and pen.bnft_ordr_num = enb.ordr_num
and enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and epe.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd = 'PROCD'
and pil.person_id = l_person_id ;
select clf.comp_src_cd,
clf.name
from ben_comp_lvl_fctr clf
where p_comp_lvl_fctr_id = clf.comp_lvl_fctr_id;
select 'Y'
from
ben_prtt_enrt_rslt_f pen,
ben_prtt_enrt_actn_f pea,
ben_per_in_ler pil,
ben_prtt_enrt_ctfn_prvdd_f ecp
where
-- pen
pen.person_id=p_person_id
and pen.business_group_id = l_business_group_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date) between
between pen.effective_start_date and pen.effective_end_date
and pen.pl_id=p_pl_id
and nvl(pen.pgm_id,-1)=nvl(p_pgm_id,-1)
and pen.prtt_enrt_rslt_stat_cd is null
-- pea
and pea.prtt_enrt_rslt_id=pen.prtt_enrt_rslt_id
and pea.business_group_id=pen.business_group_id
and pea.pl_bnf_id is null
and pea.elig_cvrd_dpnt_id is null
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between pea.effective_start_date
and pea.effective_end_date
-- pil
and pil.per_in_ler_id=pea.per_in_ler_id
and pil.business_group_id=pea.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
-- ecp
and ecp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and ecp.prtt_enrt_actn_id=pea.prtt_enrt_actn_id
and ecp.enrt_ctfn_recd_dt is not null
and ecp.business_group_id = pen.business_group_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between ecp.effective_start_date
and ecp.effective_end_date
and ecp.enrt_ctfn_typ_cd=p_enrt_ctfn_typ_cd
--Bug 3132792 only if the benefit amount is more that the combined value
-- we need to return 'Y' for the certification received flag
and pen.bnft_amt >= l_combined_val
;
select 'Y'
from ben_ler_f ler
where ler.typ_cd = 'SCHEDDU'
and ler.ler_id = p_ler_id
and p_effective_date between ler.effective_start_date
and ler.effective_end_date;
SELECT enb.*
FROM ben_enrt_bnft enb
WHERE elig_per_elctbl_chc_id = p_epe_id
AND crntly_enrld_flag = 'Y';
SELECT pen.prtt_enrt_rslt_id,
pen.bnft_amt,
pen.bnft_ordr_num
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.prtt_enrt_rslt_id = l_epe.prtt_enrt_rslt_id
and pen.business_group_id = l_epe.business_group_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.effective_end_date = hr_api.g_eot
AND cv_effective_date <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND NVL(l_epe.oipl_id,-1) = NVL(pen.oipl_id,-1)
AND l_epe.pl_id = pen.pl_id
AND NVL(l_epe.pgm_id,-1) = NVL(pen.pgm_id,-1)
AND (pen.sspndd_flag = 'N'
OR (pen.sspndd_flag = 'Y' and
pen.enrt_cvg_thru_dt = hr_api.g_eot
)
)
order by pen.enrt_cvg_strt_dt;
SELECT pil.per_in_ler_id,
epe.pl_id,
epe.oipl_id,
epe.pgm_id,
epe.pl_typ_id
FROM ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe
WHERE pil.business_group_id = epe.business_group_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 epe.crntly_enrd_flag = 'Y';
SELECT pil.*
FROM ben_per_in_ler pil,
ben_ler_f ler
WHERE pil.person_id = p_person_id
AND pil.ler_id = ler.ler_id
AND ler.business_group_id = pil.business_group_id
AND ler.typ_cd NOT IN ('IREC','GSP','COMP','ABS','SCHEDDU')
AND pil.per_in_ler_stat_cd = 'PROCD'
AND pil.lf_evt_ocrd_dt < p_lf_evt_ocrd_dt
ORDER BY pil.lf_evt_ocrd_dt desc;
SELECT null
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.per_in_ler_id = p_pil_id
AND pen.sspndd_flag = 'Y'
AND pen.prtt_enrt_rslt_stat_cd IS NULL
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.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot;
SELECT enb1.*
FROM ben_elig_per_elctbl_chc epe,
ben_elig_per_elctbl_chc epe1,
ben_enrt_bnft enb1
WHERE epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
AND epe.crntly_enrd_flag = 'Y'
AND epe1.per_in_ler_id = p_prev_pil_id
AND epe1.pl_id = epe.pl_id
AND Nvl(epe1.oipl_id,-1) = Nvl(epe.oipl_id,-1)
AND epe1.pgm_id = epe.pgm_id
AND epe1.business_group_id = epe.business_group_id
AND enb1.elig_per_elctbl_chc_id = epe1.elig_per_elctbl_chc_id
AND enb1.business_group_id = epe.business_group_id;
SELECT ecc.*
FROM ben_elctbl_chc_ctfn ecc
WHERE ecc.business_group_id = p_business_group_id
AND ecc.elig_per_elctbl_chc_id = p_epe_id
---Added for the bug 8767376
and l_cvg(0).cvg_mlt_cd not in ('CLRNG','FLPCLRNG','CLPFLRNG','FLRNG')
union
SELECT ecc.*
FROM ben_elctbl_chc_ctfn ecc,
ben_enrt_bnft enb
WHERE ecc.elig_per_elctbl_chc_id = p_epe_id
AND ecc.business_group_id = p_business_group_id
AND enb.business_group_id = ecc.business_group_id
AND enb.elig_per_elctbl_chc_id = ecc.elig_per_elctbl_chc_id
AND enb.enrt_bnft_id = ecc.enrt_bnft_id
AND nvl(enb.val,-1) = nvl(p_val,-1)
AND ecc.enrt_bnft_id IS NOT NULL
and l_cvg(0).cvg_mlt_cd in ('CLRNG','FLPCLRNG','CLPFLRNG','FLRNG');
SELECT NULL
FROM ben_elctbl_chc_ctfn ecc,
ben_enrt_bnft enb ---Added for the bug 8767376
WHERE ecc.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
AND ecc.business_group_id = p_business_group_id
---Added for the bug 8767376
AND enb.business_group_id = ecc.business_group_id
AND enb.elig_per_elctbl_chc_id = ecc.elig_per_elctbl_chc_id
AND enb.enrt_bnft_id = ecc.enrt_bnft_id
AND enb.crntly_enrld_flag = 'Y'
AND ecc.enrt_bnft_id IS NOT NULL;
select ENTR_VAL_AT_ENRT_FLAG
from ben_enrt_bnft
where enrt_bnft_id = l_enrt_bnft_id;
delete from ben_enrt_Bnft
where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
ben_manage_unres_life_events.update_enrt_bnft
(p_dflt_flag => 'N',
p_val_has_bn_prortd_flag => 'N', -- change when prorating
p_bndry_perd_cd => l_cvg(0).bndry_perd_cd,
p_bnft_typ_cd => l_cvg(0).bnft_typ_cd,
p_val => l_interim_bnft_val, -- l_rstrn.mx_cvg_alwd_amt, /*ENH */
p_nnmntry_uom => l_cvg(0).nnmntry_uom,
p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
p_prtt_enrt_rslt_id => null,
p_business_group_id => l_cvg(0).business_group_id,
p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
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_comp_lvl_fctr_id => l_cvg(0).comp_lvl_fctr_id,
p_cvg_mlt_cd => l_cvg(0).cvg_mlt_cd,
p_crntly_enrld_flag => 'N',
p_ctfn_rqd_flag => 'N',
p_entr_val_at_enrt_flag => 'N',
p_mx_val => null, -- max with ctfn
p_mx_wout_ctfn_val => null, -- max without ctfn
p_mn_val => null,
p_incrmt_val => null,
p_dflt_val => null,
p_rt_typ_cd => l_cvg(0).rt_typ_cd,
p_mx_wo_ctfn_flag => 'Y',
p_program_update_date => sysdate,
p_enrt_bnft_id => l_enrt_bnft_id,
p_ordr_num => 0);
p_program_update_date => sysdate,
p_enrt_bnft_id => l_enrt_bnft_id,
p_object_version_number => l_object_version_number,
p_ordr_num => 0);
ben_elig_per_elc_chc_api.update_perf_elig_per_elc_chc(
p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
p_elctbl_flag => 'N',
p_dflt_flag => 'N',
p_object_version_number => l_epe.object_version_number,
p_effective_date => p_effective_date,
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_elig_per_elc_chc_api.update_perf_elig_per_elc_chc(
p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
p_elctbl_flag => 'N',
p_dflt_flag => 'N',
p_object_version_number => l_epe.object_version_number,
p_effective_date => p_effective_date,
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
);
--update enb table if order number matches.
if l_get_prev_bnft.bnft_amt <> l_check.val then
ben_enrt_bnft_api.update_enrt_bnft
(p_enrt_bnft_id => l_check.enrt_bnft_id
,p_dflt_flag => 'N' ---l_check.dflt_flag,Bug 9308931
,p_val_has_bn_prortd_flag => l_check.val_has_bn_prortd_flag
,p_bndry_perd_cd => l_check.bndry_perd_cd
,p_val => l_check.val
,p_nnmntry_uom => l_check.nnmntry_uom
,p_bnft_typ_cd => l_check.bnft_typ_cd
,p_entr_val_at_enrt_flag => l_check.entr_val_at_enrt_flag
,p_mn_val => l_check.mn_val
,p_mx_val => l_check.mx_val
,p_incrmt_val => l_check.incrmt_val
,p_dflt_val => l_check.dflt_val
,p_rt_typ_cd => l_check.rt_typ_cd
,p_cvg_mlt_cd => l_check.cvg_mlt_cd
,p_ctfn_rqd_flag => l_check.ctfn_rqd_flag
,p_ordr_num => l_check.ordr_num
,p_crntly_enrld_flag => 'N'
,p_elig_per_elctbl_chc_id => l_check.elig_per_elctbl_chc_id
,p_prtt_enrt_rslt_id => null
,p_comp_lvl_fctr_id => l_check.comp_lvl_fctr_id
,p_business_group_id => l_check.business_group_id
,p_enb_attribute_category => l_check.enb_attribute_category
,p_enb_attribute1 => l_check.enb_attribute1
,p_enb_attribute2 => l_check.enb_attribute2
,p_enb_attribute3 => l_check.enb_attribute3
,p_enb_attribute4 => l_check.enb_attribute4
,p_enb_attribute5 => l_check.enb_attribute5
,p_enb_attribute6 => l_check.enb_attribute6
,p_enb_attribute7 => l_check.enb_attribute7
,p_enb_attribute8 => l_check.enb_attribute8
,p_enb_attribute9 => l_check.enb_attribute9
,p_enb_attribute10 => l_check.enb_attribute10
,p_enb_attribute11 => l_check.enb_attribute11
,p_enb_attribute12 => l_check.enb_attribute12
,p_enb_attribute13 => l_check.enb_attribute13
,p_enb_attribute14 => l_check.enb_attribute14
,p_enb_attribute15 => l_check.enb_attribute15
,p_enb_attribute16 => l_check.enb_attribute16
,p_enb_attribute17 => l_check.enb_attribute17
,p_enb_attribute18 => l_check.enb_attribute18
,p_enb_attribute19 => l_check.enb_attribute19
,p_enb_attribute20 => l_check.enb_attribute20
,p_enb_attribute21 => l_check.enb_attribute21
,p_enb_attribute22 => l_check.enb_attribute22
,p_enb_attribute23 => l_check.enb_attribute23
,p_enb_attribute24 => l_check.enb_attribute24
,p_enb_attribute25 => l_check.enb_attribute25
,p_enb_attribute26 => l_check.enb_attribute26
,p_enb_attribute27 => l_check.enb_attribute27
,p_enb_attribute28 => l_check.enb_attribute28
,p_enb_attribute29 => l_check.enb_attribute29
,p_enb_attribute30 => l_check.enb_attribute30
,p_request_id => l_check.request_id
,p_program_application_id => l_check.program_application_id
,p_program_id => l_check.program_id
,p_program_update_date => l_check.program_update_date
,p_mx_wout_ctfn_val => l_check.mx_wout_ctfn_val
,p_mx_wo_ctfn_flag => l_check.mx_wo_ctfn_flag
,p_object_version_number => l_check.object_version_number
,p_effective_date => trunc(p_effective_date));
ben_enrt_bnft_api.update_enrt_bnft
(p_enrt_bnft_id => l_check.enrt_bnft_id
,p_dflt_flag => 'N'
,p_val_has_bn_prortd_flag => l_check.val_has_bn_prortd_flag
,p_bndry_perd_cd => l_check.bndry_perd_cd
,p_val => l_check.val
,p_nnmntry_uom => l_check.nnmntry_uom
,p_bnft_typ_cd => l_check.bnft_typ_cd
,p_entr_val_at_enrt_flag => l_check.entr_val_at_enrt_flag
,p_mn_val => l_check.mn_val
,p_mx_val => l_check.mx_val
,p_incrmt_val => l_check.incrmt_val
,p_dflt_val => l_check.dflt_val
,p_rt_typ_cd => l_check.rt_typ_cd
,p_cvg_mlt_cd => l_check.cvg_mlt_cd
,p_ctfn_rqd_flag => l_check.ctfn_rqd_flag
,p_ordr_num => l_check.ordr_num
,p_crntly_enrld_flag => 'N'
,p_elig_per_elctbl_chc_id => l_check.elig_per_elctbl_chc_id
,p_prtt_enrt_rslt_id => null
,p_comp_lvl_fctr_id => l_check.comp_lvl_fctr_id
,p_business_group_id => l_check.business_group_id
,p_enb_attribute_category => l_check.enb_attribute_category
,p_enb_attribute1 => l_check.enb_attribute1
,p_enb_attribute2 => l_check.enb_attribute2
,p_enb_attribute3 => l_check.enb_attribute3
,p_enb_attribute4 => l_check.enb_attribute4
,p_enb_attribute5 => l_check.enb_attribute5
,p_enb_attribute6 => l_check.enb_attribute6
,p_enb_attribute7 => l_check.enb_attribute7
,p_enb_attribute8 => l_check.enb_attribute8
,p_enb_attribute9 => l_check.enb_attribute9
,p_enb_attribute10 => l_check.enb_attribute10
,p_enb_attribute11 => l_check.enb_attribute11
,p_enb_attribute12 => l_check.enb_attribute12
,p_enb_attribute13 => l_check.enb_attribute13
,p_enb_attribute14 => l_check.enb_attribute14
,p_enb_attribute15 => l_check.enb_attribute15
,p_enb_attribute16 => l_check.enb_attribute16
,p_enb_attribute17 => l_check.enb_attribute17
,p_enb_attribute18 => l_check.enb_attribute18
,p_enb_attribute19 => l_check.enb_attribute19
,p_enb_attribute20 => l_check.enb_attribute20
,p_enb_attribute21 => l_check.enb_attribute21
,p_enb_attribute22 => l_check.enb_attribute22
,p_enb_attribute23 => l_check.enb_attribute23
,p_enb_attribute24 => l_check.enb_attribute24
,p_enb_attribute25 => l_check.enb_attribute25
,p_enb_attribute26 => l_check.enb_attribute26
,p_enb_attribute27 => l_check.enb_attribute27
,p_enb_attribute28 => l_check.enb_attribute28
,p_enb_attribute29 => l_check.enb_attribute29
,p_enb_attribute30 => l_check.enb_attribute30
,p_request_id => l_check.request_id
,p_program_application_id => l_check.program_application_id
,p_program_id => l_check.program_id
,p_program_update_date => l_check.program_update_date
,p_mx_wout_ctfn_val => l_check.mx_wout_ctfn_val
,p_mx_wo_ctfn_flag => l_check.mx_wo_ctfn_flag
,p_object_version_number => l_check.object_version_number
,p_effective_date => trunc(p_effective_date));
ben_elig_per_elc_chc_api.update_perf_elig_per_elc_chc(
p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
p_elctbl_flag => 'N',
p_dflt_flag => 'N',
p_object_version_number => l_epe.object_version_number,
p_effective_date => p_effective_date,
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_elig_per_elc_chc_api.update_perf_elig_per_elc_chc(
p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
p_elctbl_flag => 'N',
p_dflt_flag => 'N',
p_object_version_number => l_epe.object_version_number,
p_effective_date => p_effective_date,
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
);
/*Bug 9774647:When salary change event occurs, update the coverage amount of the interim with the newly calculated value */
p_nnmntry_uom => l_current_enb.nnmntry_uom, -- l_cvg(0).nnmntry_uom,
p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
p_prtt_enrt_rslt_id => l_current_enb.prtt_enrt_rslt_id,
p_business_group_id => l_current_enb.business_group_id, -- l_cvg(0).business_group_id,
p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
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_comp_lvl_fctr_id => l_current_enb.comp_lvl_fctr_id, --l_cvg(0).comp_lvl_fctr_id,
p_cvg_mlt_cd => l_current_enb.cvg_mlt_cd, -- l_cvg(0).cvg_mlt_cd,
p_crntly_enrld_flag => 'N',
p_ctfn_rqd_flag => 'N',
p_entr_val_at_enrt_flag => 'N',
p_mx_val => null, -- max with ctfn
p_mx_wout_ctfn_val => null, -- max without ctfn
p_mn_val => null,
p_incrmt_val => null,
p_dflt_val => null,
p_rt_typ_cd => l_current_enb.rt_typ_cd, -- l_cvg(0).rt_typ_cd,
p_mx_wo_ctfn_flag => 'Y',
p_program_update_date => sysdate,
p_enrt_bnft_id => l_enrt_bnft_id,
p_object_version_number => l_object_version_number,
p_ordr_num => -1);