[Home] [Help]
PACKAGE BODY: APPS.BEN_DETERMINE_ACTIVITY_BASE_RT
Source
1 Package Body BEN_DETERMINE_ACTIVITY_BASE_RT as
2 /* $Header: benactbr.pkb 120.18.12010000.2 2008/08/05 14:30:04 ubhat ship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 Name: Determine Activity Base Rates
11
12 Purpose:
13 This program Determines the Activity Base Rate. Whether it be a Standard
14 Rate, Flex Credit, or Imputed Income, the calculation and processing are
15 the same.
16
17 History:
18 Date Who Version What?
19 ---- --- ------- -----
20 7 May 98 Ty Hayden 110.0 Created.
21 16 Jun 98 T Guy 110.1 Removed exception.
22 18 Jun 98 Ty Hayden 110.2 Added p_rt_usg_cd
23 p_bnft_prvdr_pool_id
24 p_prtt_rt_val_id
25 25 Jun 98 T Guy 110.3 Replaced all occurrences of
26 'PER10' with 'PERTEN'
27 15 Jul 98 Ty Hayden 110.4 Assign rt_usg_cd to output.
28 21 Jul 98 T Guy 110.5 Removed return statements and
29 replaced with hard errors for
30 NULL l_compensation,
31 l_actl_prem_value,
32 l_prnt_rt_value.
33 11 Aug 98 Ty Hayden 110.6 Added mlt_cd, val, and mn_mx edits.
34 8 Oct 98 T Guy 115.1 Added error messages and set location
35 calls.
36 19 Oct 98 T Guy 115.2 Added ann_val, ann_mn-mx val, cmcd val
37 mn - mx acty ref period cd,
38 actl_prem_id, cvg_amt_cal_mthd_id
39 bnft_rt_typ_cd, rt_typ_cd, rt_mlt_cd,
40 comp_lvl_fctr_id, entr_ann_val_flag,
41 ptd_comp_lvl_fctr_id, ann_dflt_val
42 Put in rate calculated prior to enrollment
43 logic.
44 Added call to calculate rt_strt_dt and
45 get rt_strt_dt_cd and rl.
46 ben_determine_date.rate_and_coverage_dates
47 25 Oct 98 T Guy 115.3 removed show errors
48 27 Oct 98 T Guy 115.4 removed date track for ben_prtt_rt_val_f
49 29 Oct 98 T Guy 115.5 fixed dflt_ann val
50 12 Nov 98 T Guy 115.6 Implemented overall min/max val and
51 rules
52 24 Nov 98 T Guy 115.7 Fixed undefined upr/lwr rule checking
53 02 Dec 98 T Guy 115.8 removed mlt_cd FLENR, this was replaced
54 by entr_val_at_enrt_flag.
55 02 Dec 98 T Guy 115.9 Put mlt_cd FLENR back in until form is
56 fixed to handle enter val at enrt.
57 02 Dec 98 T Guy 115.10 Version syncing.
58 08 Dec 98 T Guy 115.11 added comp_lvl_fctr_id assignment when
59 entr_at_enrt_flag = Y
60 17-Dec-98 T Guy 115.12 Fixed bug 1582 - took out nocopy cursor c_btr
61 because comp_lvl_fctr_id is now
62 stored on acty_base_rt.
63 bug 1589 - changed logic process
64 to check that actl_prem_id is
65 not null before calling actl_prem
66 22-Dec-98 T Guy 115.13 Removed the FLENR mlt_cd and added
67 entr_val_at_enrt_flag
68 05-Jan-98 T Guy 115.14 Fixed cursor c_abr2
69 18-Jan-99 G Perry 115.15 LED V ED
70 24-Feb-99 T Guy 115.16 Added overide check
71 09-Mar-99 G Perry 115.17 IS to AS.
72 06-Apr-99 T Guy 115.18 Fixed the overide date check
73 24-Apr-99 lmcdonal 115.19 prtt-rt-val now has a status code
74 as does prtt_enrt_rslt.
75 07-may-99 shdas 115.20 added parameters to genutils.formula.
76 08-May-99 jcarpent 115.21 Pass greatest(elig_strt_dt,
77 lf_evt_ocrd_dt)into start dt routines.
78 27 May 99 maagrawa 115.22 Modified the procedure to be called
79 without passing in the chc_id and pass
80 in reqd. details instead.
81 02 Jun 99 tguy 115.23 Modified the main procedure check that
82 effective start date of acty_base_rt is
83 >= enrt_perd_strt_dt for this chc.
84 28 Jun 99 tguy 115.24 Made total premium changes
85 02 Jul 99 lmcdonal 115.25 Make use of genutils limit_checks and
86 rt_typ_calc.
87 09 Jul 99 bbulusu 115.25 Added checks for backed out nocopy life evt.
88 16 Jul 99 lmcdonal 115.26 limit_checks parms changed.
89 20 JUL 99 Gperry 115.27 genutils -> benutils package rename.
90 22 JUL 99 mhoyes 115.28 - Added trace messages.
91 - Replace +0s.
92 31 AUG 99 mhoyes 115.30 - Added trace messages.
93 07 SEP 99 tguy 115.31 fixed call to
94 pay_mag_utils.lookup_jurisdiction_code
95 10-SEP-99 maagrawa 115.32 Bug 3109. enrt_perd_strt_dt to be
96 effective date when no chc is passed.
97 13-sep-99 jcarpent 115.33 Bug 3252. use only epe.bg_id not
98 p_business_group_id, since may be null
99 bug is that no current enrt found due
100 to business group mismatch (null).
101 13-sep-1999 jcarpent 115.34 - Patch on 115.29 for bug 3252.
102 13-sep-1999 jcarpent 115.35 - Leapfrog. Same as version 115.33
103 except comments.
104 21-sep-1999 tguy 115.36 added check for dflt_enrt_cd for
105 dflt_val assignemnt
106 29-Sep-1999 lmcdonal 115.37 Call prorate_min_max and compare_balances.
107 04-Oct-1999 tguy 115.38 added call to dt_fndate
108 21-Oct-1999 pbodla 115.39 benutils.limit_checks: p_val is sent
109 instead of l_rounded_value
110 29-Oct-1999 lmcdonal 115.40 When the use calc acty bs rt flag is OFF
111 do not calc the rate out, just send along
112 the 'value' field.
113 See bugs3549_3552.doc.
114 14-Nov-1999 mhoyes 115.41 - Added trace messages.
115 16-Nov-1999 pbodla 115.42 - Added acty_base_rt_id as context to
116 val_calc_rl evaluation and to
117 limit_checks, rate_and_coverage_dates
118 calls.
119 18-Nov-1999 pbodla 115.43 - Added p_elig_per_elctbl_chc_id as context to
120 val_calc_rl evaluation , passed to limit_checks
121 29-Nov-1999 jcarpent 115.44 - Added call to det._dflt_enrt_cd
122 15-Dec-1999 lmcdonal 115.45 Do not force min and max values if the entr-
123 ann-val-flag is on.
124 17-Jan-00 tguy 115.46 Added check for rounding when vrbl
125 rt trtmt cd = rplc do not round at
126 value at this level
127 20-Jan-00 maagrawa 115.47 Pass payroll_id to ben_distribute_rates
128 20-Jan-00 lmcdonal 115.48 If enrt_bnft cvg val is null, use dflt
129 val. Bug 1118016.
130 09-FEB-2000 pbodla 115.49 - Bug : 1169626 p_acty_base_rt_id is passed
131 to formula instead of l_acty_base_rt_id
132 18-FEB-2000 pbodla 115.50 - c_prv modified : order by clause added.
133 When prv record is fetched it is fetching
134 the first row. It should fetch the latest
135 participant rate value record for a given
136 enrollment result id.
137 29-Mar-00 mmogel 115.51 I changed the message numbers from
138 91382 to 91832 in the message name
139 BEN_91382_PACKAGE_PARAM_NULL and the
140 91383 to 92833 in the message name
141 BEN_91833_CURSOR_RETURN_NO_ROW and also
142 added tokens to some other messages
143 31-Mar-2000 jcarpent 115.52 - bug 1252087/4983. Fix dflt_val for
144 dflt_enrt_cds of 'NSDCS','NNCS';
145 19-Apr-2000 lmcdonal 115.53 - bug 5088, per gp, calls to dt_fndate.
146 change_ses_date should be in the highest
147 level batch processes (for rule calls)
148 not in lower level procs like this one.
149
150 10-MAY-200 Tilak 115.54 bug 4816 l_val is not rounded to hundredth fixd by
151 correcting if condition. nvl added in l_vr_trtmt_cd
152 12-MAY-2000 mhoyes 115.55 - Added messages for profiling.
153 22-May-2000 lmcdonal 115.56 Leap frog of 52 with fix for 1295277:
154 when checking to see if
155 acty-base-rt is already a prtt-rt-val
156 record, do not compare acty-typ-cd; this
157 can come from the acty-base-rt row OR
158 from vrbl-rt row.
159 22-May-2000 lmcdonal 115.57 Bug 1295277: fix noted above. This is
160 leap from version 55. 'real' version.
161 22-May-2000 lmcdonal 115.58 leap of version 56 to allow fido to fix
162 aera data.
163 22-May-20000 lmcdonal 115.59 'real' version....allow mis-matched
164 acty-type and tax-type codes, causing
165 update to be determined only by
166 acty-base-rt-id.
167 26-May-2000 shdas 115.60 round rates before variable
168 rates applied--5152.
169 29-May-2000 mhoyes 115.61 Added record structures to main.
170 29-May-2000 mhoyes 115.62 Re-applied fix 5152.
171 31-May-2000 mhoyes 115.63 Added enrt_perd_strt_dt to p_currepe_row.
172 12-JUN-2000 tilak 115.64 acty_base_rt >= enrt_perd_strt_dt condition
173 is removed from c_abr cursor for bug 5173
174 This conition return no row .when the enrollment for future
175 period the date betwen chek the future period and this condion
176 check the current period
177 26-JUN-2000 shdas 115.65 added codes for new mlt_cd 'SAREC'.
178 28-JUN-2000 mhoyes 115.66 - Fixed duplicate rate problem.
179 - Tuned c_prv.
180 - Bypassed c_current_elig when prtn_strt_dt
181 is passed in.
182 25-AUG-2000 pbodla 115.67 - Bug : 1386285 : When RT_OVRIDN_THRU_DT
183 is null assume it as EOT.
184 28-SEP-2000 stee 115.68 - Added p_cal_val for UK Select to
185 calculate a child rate if the parent
186 rate is calculate at enrollment.
187 27-AUG-2000 RCHASE 115.69 - wwbug#1207803.999 - correct perfprv
188 cursor to fetch rows when
189 updates have been made to the enrollment
190 result past the life event occured date.
191 This is a leapfrog version based on
192 115.67.
193 29-AUG-2000 jcarpent 115.70 - Merge version based on 115.68 and
194 115.69.
195 06-Nov-2000 tilak 115.71 bug-1480407 calculation of rate for enter at entrolment is added
196 06-nov-2000 tilak 115.72
197 29-nov-2000 gperry 115.73 Added person_id to epe record so that
198 rules work correctly in rate and
199 coverage call. WWBUG 1510623.
200 15-jan-2001 tilak 115.74 encremental chek is added to the rate
201 22-jan-2001 tilak 115.75 p_parent_val added to calculate the parent value
202 when the child recod called for calucaltion from
203 enrollment and parent entr_val_at_entr is on
204 07-mar-2001 tilak 115.76 bug : 1628762, when the premium based on coverage
205 and coverage is range then benefit id reauired
206 to get the premiuum value -c_enrt_prem cursor changed
207 06-mar-2001 ikasire 115.77 bug 1650517 not passing p_complete_year_flag
208 for SAREC condition for communicated amount
209 calculation
210 15-mar-2001 tilak 115.78 bun : 1676551 calling premium calcualtion
211 21-mar-2001 tilak 115.79 ultmt_upr_lmt,ultmt_lwr_lmt is validated
212 01-apr-2001 tilak 115.80 ultmt_upr_lmt_calc_rl ,ltmt_lwr_lmtcalc_rl is validated
213 01-may-2001 kmahendr 115.81 Bug#1749068 - changed p_cal_val value depending on parent rate's
214 enter value at enrollment flag
215 03-may-2001 kmahendr 115.83 As version 115.82 was based on leapfrog of version 115.77,
216 version 115.81 brought forward.
217 13-Jul-2001 ikasire 115.84 bug 1834655 fixed the code for default enrollment
218 13-Jul-2001 ikasire 115.85 removed the show errors
219 18-Jul-2001 ikasire 115.86 bug 1834655 added ann_rt_val to prv and perfprv
220 cursors
221 27-Aug-2001 mhoyes 115.87 - Replaced generic error messages,
222 - 91832 with 92748.
223 - 91833 with 92743, 92738, 92739, 92740
224 92741, 92742
225 - 91835 with 92744, 92745, 92746, 92747
226 29-aug-2001 tilak 115.88 bug:1949361 jurisdiction code is
227 derived inside benutils.formula.
228 27-Sep-2001 kmahendr 115.89 Bug#1981673-Added parameter ann_mn_elcn_val and
229 ann_mx_elcn_val to ben_determine_variable_rates
230 call and returned values assigned
231
232 05-dec-2001 tjesumic 115.90 Add,mutltifly,substract added for vapro values
233 bug:2112513
234 05-Feb-2002 kmahendr 115.91 Bug#2207947-Added a cursor c_perfPrv_2 to get the
235 prtt_rt_val_id if the coverage and rate starts in
236 future
237 05-Feb-2002 kmahendr 115.92 Added dbdrv : checkfile line
238 06-Feb-2002 pabodla 115.93 Calculating cmcd_mn/mx values.
239 07-Feb-2002 ikasire 115.94 Bug 2192102 Flex Credit fails in case of the
240 Flex Credit or VAPRO associated with the FC is
241 based on coverage calculation- fixed
242 15-Nov-2001 dschwart/ 115.95 Bug#1791203: altered estimate only functions to
243 gopal calculate correctly when frequency rules are
244 altered to 24 pay periods. bug : 1794303
245 26-Mar-2002 kmahendr 115.96 Bug#1833008 - Added parameter p_cal_for to the call
246 determine_compensation to handle multi-assignment.
247 18-Apr-2002 lmcdonal 115.97 l_coverage_val was not always being loaded
248 from p_bnft_amt. Needed for SS call to
249 this package.
250 30-Apr-2002 kmahendr 115.98 Added token to error message-91832.
251 01 May 2002 lmcdonal 115.99 Bug 2048257 Added main_w.
252 20-May-2002 pabodla 115.100 If this procedure is called from enrt
253 process then validate value with
254 enrt_rt table's min/max instead of
255 standard rate min max values.
256
257 23 May 2002 kmahendr 115.101 Added a parameter to main.
258 115.102 No changes
259 03 Jun 2002 pabodla 115.103 Bug 2400850 : Checking prtt_enrt_rslt_id
260 before assigning any values to p_dflt_val
261 05 Jun 2002 pabodla 115.104 Bug 2403243 : If vapro and abr both has
262 null values for min/max then assigning
263 null to p_mn/max_elcn_val variables
264 instead of zero
265 08-Jun-2002 pabodla 115.105 Do not select the contingent worker
266 assignment when assignment data is
267 fetched.
268 27-Jun-2002 Tilak 115.106 Bug 2438506: if vapro treatment code is
269 multiply by and abr have null value
270 then trat abr value as 1 else causes
271 null rate value calculation.
272 08-Jul-2002 ikasire 115.107 Bug 2445318 handling the p_dflt_val and
273 p_ann_dflt_val cases
274 12-Jul-2002 vsethi 115.108 Bug 1699585 added tokens for message BEN_91932_NOT_INCREMENT
275 15-Jul-2002 vsethi 115.109 Wrong variable referenced in cursor c_pln
276 04-sep-2002 kmahendr 115.111 Added new acty_ref_perd_cd - phr.
277 17-Sep-2002 hnarayan 115.112 Bug 2569884 - communicated min and max values should
278 be calculated irrespective of whether calculate for
279 enrollment flag is checked or not
280 11-Oct-2002 vsethi 115.113 Rates Sequence no enhancements. Modified to cater
281 to new column ord_num on ben_acty_base_rt_f
282 24-Oct-2002 shdas 115.114 Added ben_env_object.init to main_w
283 13-Nov-2002 vsethi 115.115 Bug 1210355, if variable rate is detected the mlt code
284 attached to the variable profile should be displayed
285 for the rate
286 09-DEC-2002 hnarayan 115.116 Bug 2691169 - added order by clause to cursor c_asg
287 so that it retrieves assignmnets of type 'E' first
288 and then of type 'B'
289 16 Dec 02 hnarayan 115.117 Added NOCOPY hint
290 24 Dec 02 kmullapu 115.118 Added new cursor c_pl_opt_name to display pl-opt name
291 in error messages. Bug 2385186
292 27 Dec 02 ikasire 115.119 Bug 2677804 changes for override thru date
293 23 Jan 03 ikasire 115.120 Bug 2149438 using to overloaded annual_to_period procedure
294 and rounding to 4 digits to improve the precision.
295 05-Feb-03 gjesumig 115.121 GSP enhancement, calculation added for new mlt_cd 'PRV'
296 12-Feb-03 tjesumic 115.122 2797031 , if premium value is null it is recaulcualted
297 nvl added to premium value
298 13-Feb-03 kmahendr 115.123 Added a parameter - p_iss_val and codes for auto_distr
299 22-May-03 kmahendr 115.124 Included ERL to evaluate formula
300 06-Jun-03 kmahendr 115.125 Rt_mlt_cd is populated from variable rate only if the
301 treatment code is Replace.Bug#2996378
302 21-oct-03 hmani 115.126 Bug 3177401 - Changed p_date_mandatory_flag to 'Y'
303 11-nov-03 hmani 115.127 Reversing back the previous fix
304 12-Nov-03 ikasire 115.128 Bug 3253180 Defaults not working when enter value at
305 enrollment annual flag is checked for the current
306 enrollment
307 13-Nov-03 kmahendr 115.129 Bug#3254240 - added codes in main_w to get the
308 communicated value
309 23-Feb-04 stee 115.130 Bug 3457483 - Check the assignment to use code
310 in activity base rate when selecting the
311 assignment.
312 14-Apr-04 mmudigon 115.132 FONM changes
313 29-May-04 mmudigon 115.133 FONM changes continued
314 05-Aug-04 tjesumic 115.134 FONM for ben_derive_factors fonm date paseed as param
315 03-Sep-04 hmani 115.135 fixed cmcd_dflt_val issue Bug 3274902
316 06-sep-04 hmani 115.136 Modified for annual value flag Bug 3274902
317 Also fixed few missed out FONM issues.
318 03-Dec-04 vvprabhu 115.137 Bug 3980063 - SSBEN Trace Enhancement
319 03-Dec-04 ikasire 115.138 Added defaults to main_w as per main
320 14-dec-04 nhunur 115.139 cwb now allows null values for min/max/incrmt val
321 so no need to validate
322 30-dec-2004 nhunur 115.140 4031733 - No need to open cursor c_state.
323 24-Jan-2004 swjain 115.141 3981982 - Min Max Enhancement. Added code to evaluate
324 min max rule.
325 27-Jan-2004 swjain 115.142 Updated the message number of the the message
326 BEN_XXXX_MN_MX_RL_OUT_ERR to
327 BEN_94130_MN_MX_RL_OUT_ERR
328 31-Jan-2004 swjain 115.143 3981982 - Added more input paramters in call to
329 benutils.formula
330 17-Feb-2004 vvprabhu 115.144 Bug 4143012 : Changes in procedure main_w
331 to avoid value of cost1 being displayed for
332 cost2 when cost1 is 'SAREC'
333 12-May-2005 ikasire 115.145 Moved the fnd_message binding into IF clause to avoid
334 misleading error message from SSBEN
335 23-May-2005 lakrish 115.146 4235088, do fnd_number.canonical_to_number() to the
336 FF output before assigning to a number variable
337 09-Jun-2005 nhunur 115.147 4383988, do fnd_number.canonical_to_number() to all
338 FF that return a number variable
339 05-Sep-05 swjain 115.148 Bug No 4538786 Per Pay Period with frequency rules
340 changes in procedure main
341 13-Sep-2005 rbingi 115.151 Bug-4604560 in procedure main, Added close for
342 cursor get_rt_and_element
343 28-Sep-2005 nhunur 115.152 Bug 4637525 : CWB- MULTIPLE OF COMPENSATION CHANGES.
344 02-Feb-2006 stee 115.153 Bug 4873847. cwb: Round the rec_val,
345 rec_mn_val, rec_mx_val.
346 24-Apr-2006 rgajula 115.154 Bug 5031047 Modified the order by clause to prv.rt_start_dt asecending
347 and added the clause c_effective_date < prv.rt_end_dt for the cursors
348 c_prv,c_perfprv,c_perfprv_2
349 18-May-2006 rgajula 115.155 Bug 5225815 do not prorate if there are no prior elections
350 or the l_rt_strt_dt is null
351 07-Jul-2006 bmanyam 115.156 5371364 : Do not Operate (i.e multiply/divide..)
352 the Increment Value.
353 21-Sep-2006 vborkar 115.157 5534498 : Passed p_person_id and p_start_date(rt_strt_dt)
354 parameters to annual_to_period for correct rate calculation
355 on 'Recalculate'.
356 25-Sep-2006 bmanyam 115.158 5557305 : Premium has to be recalculated everytime
357 for rates of type 'Multiple of Premium'
358 3-nov-2006 nhunur 115.159 c_perfprv,c_perfprv_2 to handle non recurrring
359
360 1-Feb-2007 bmanyam 115.160 5748126: Update BEN_ENRT_RT with the
361 latest PRV_ID, which occurs before the LE_OCRD_DT
362 23-Feb-2006 bmanyam 115.161 5898039: same as above. [ON or before LE_OCRD_DT]
363
364 22-Jan-2007 rtagarra 115.160 ICM Changes.
365
366 19-Dec-2007 krupani 115.162 Changes against Bug 6015724 incorporated from 115.162.
367 Forward port Bug is 6158436
368
369 26-Dec-2007 krupani 115.164 Changes against Bug 6314463 and 6330056 incorporated from 115.164
370 in R12 mainline
371 17-Jun-2008 sagnanas 115.165 Bug 7154229
372 */
373 --------------------------------------------------------------------------------
374 --
375 g_package varchar2(80) := 'ben_determine_activity_base_rt';
376 g_debug boolean := hr_utility.debug_enabled;
377 --
378 TYPE g_element_link_id_table IS TABLE OF pay_element_links_f.element_link_id%TYPE
379 INDEX BY BINARY_INTEGER;
380 --
381 -------------------------------------------------------------------------------
382 -- get_expr_val
383 -------------------------------------------------------------------------------
384 function get_expr_val(p_op in varchar2,
385 p_oper1 in number,
386 p_oper2 in number)
387 return number is
388 --
389 l_proc varchar2(80) := 'get_expr_val';
390 l_oper1 number;
391 l_oper2 number;
392 l_ret_val number := null;
393 --
394 begin
395 --
396 l_oper1 := p_oper1;
397 l_oper2 := p_oper2;
398 --
399 if p_oper1 is null and p_oper2 is null then
400 --
401 l_ret_val := null;
402 return l_ret_val;
403 --
404 elsif p_oper1 is null then
405 --
406 l_oper1 := 0;
407 --
408 elsif p_oper2 is null then
409 --
410 l_oper2 := 0;
411 --
412 end if;
413 --
414 if p_op = '+' then
415 --
416 l_ret_val := l_oper1+l_oper2;
417 --
418 elsif p_op = '-' then
419 --
420 l_ret_val := l_oper1-l_oper2;
421 --
422 elsif p_op = '*' then
423 --
424 if p_oper1 is null then
425 --
426 l_oper1 := 1;
427 --
428 elsif p_oper2 is null then
429 --
430 l_oper2 := 1;
431 --
432 end if;
433 --
434 l_ret_val := l_oper1*l_oper2;
435 end if;
436 return l_ret_val;
437 --
438 end get_expr_val;
439
440 PROCEDURE main
441 (p_currepe_row in ben_determine_rates.g_curr_epe_rec
442 := ben_determine_rates.g_def_curr_epe_rec
443 ,p_per_row in per_all_people_F%rowtype
444 := ben_determine_rates.g_def_curr_per_rec
445 ,p_asg_row in per_all_assignments_f%rowtype
446 := ben_determine_rates.g_def_curr_asg_rec
447 ,p_ast_row in per_assignment_status_types%rowtype
448 := ben_determine_rates.g_def_curr_ast_rec
449 ,p_adr_row in per_addresses%rowtype
450 := ben_determine_rates.g_def_curr_adr_rec
451 ,p_person_id IN number
452 ,p_elig_per_elctbl_chc_id IN number
453 ,p_enrt_bnft_id IN number default null
454 ,p_acty_base_rt_id IN number
455 ,p_effective_date IN date
456 ,p_lf_evt_ocrd_dt IN date default null
457 ,p_perform_rounding_flg IN boolean default true
458 ,p_calc_only_rt_val_flag in boolean default false
459 ,p_pl_id in number default null
460 ,p_oipl_id in number default null
461 ,p_pgm_id in number default null
462 ,p_pl_typ_id in number default null
463 ,p_per_in_ler_id in number default null
464 ,p_ler_id in number default null
465 ,p_bnft_amt in number default null
466 ,p_business_group_id in number default null
467 ,p_cal_val in number default null
468 ,p_parent_val in number default null
469 ,p_called_from_ss in boolean default false
470 ,p_val OUT NOCOPY number
471 ,p_mn_elcn_val OUT NOCOPY number
472 ,p_mx_elcn_val OUT NOCOPY number
473 ,p_ann_val OUT NOCOPY number
474 ,p_ann_mn_elcn_val OUT NOCOPY number
475 ,p_ann_mx_elcn_val OUT NOCOPY number
476 ,p_cmcd_val OUT NOCOPY number
477 ,p_cmcd_mn_elcn_val OUT NOCOPY number
478 ,p_cmcd_mx_elcn_val OUT NOCOPY number
479 ,p_cmcd_acty_ref_perd_cd OUT NOCOPY varchar2
480 ,p_incrmt_elcn_val OUT NOCOPY number
481 ,p_dflt_val OUT NOCOPY number
482 ,p_tx_typ_cd OUT NOCOPY varchar2
483 ,p_acty_typ_cd OUT NOCOPY varchar2
484 ,p_nnmntry_uom OUT NOCOPY varchar2
485 ,p_entr_val_at_enrt_flag OUT NOCOPY varchar2
486 ,p_dsply_on_enrt_flag OUT NOCOPY varchar2
487 ,p_use_to_calc_net_flx_cr_flag OUT NOCOPY varchar2
488 ,p_rt_usg_cd OUT NOCOPY varchar2
489 ,p_bnft_prvdr_pool_id OUT NOCOPY number
490 ,p_actl_prem_id OUT NOCOPY number
491 ,p_cvg_calc_amt_mthd_id OUT NOCOPY number
492 ,p_bnft_rt_typ_cd OUT NOCOPY varchar2
493 ,p_rt_typ_cd OUT NOCOPY varchar2
494 ,p_rt_mlt_cd OUT NOCOPY varchar2
495 ,p_comp_lvl_fctr_id OUT NOCOPY number
496 ,p_entr_ann_val_flag OUT NOCOPY varchar2
497 ,p_ptd_comp_lvl_fctr_id OUT NOCOPY number
498 ,p_clm_comp_lvl_fctr_id OUT NOCOPY number
499 ,p_ann_dflt_val OUT NOCOPY number
500 ,p_rt_strt_dt OUT NOCOPY date
501 ,p_rt_strt_dt_cd OUT NOCOPY varchar2
502 ,p_rt_strt_dt_rl OUT NOCOPY number
503 ,p_prtt_rt_val_id OUT NOCOPY number
504 ,p_dsply_mn_elcn_val OUT NOCOPY number
505 ,p_dsply_mx_elcn_val OUT NOCOPY number
506 ,p_pp_in_yr_used_num OUT NOCOPY number
507 ,p_ordr_num OUT NOCOPY number
508 ,p_iss_val OUT NOCOPY number
509 )
510 IS
511 --
512 l_value number;
513 l_rounded_value number;
514 l_pl_id number;
515 l_oipl_id number;
516 l_coverage_value number;
517 l_val number;
518 l_dflt_val number;
519 l_compensation_value number;
520 l_actl_prem_value number;
521 l_prnt_rt_value number;
522 l_comp_lvl_fctr_id number;
523 l_actl_prem_id number;
524 l_acty_base_rt_id number;
525 l_dummy_num number;
526 l_dummy_char varchar2(30);
527 l_dummy_date date;
528 l_outputs ff_exec.outputs_t;
529 l_vr_val number := null;
530 l_vr_mn_elcn_val number := null;
531 l_vr_mx_elcn_val number := null;
532 l_vr_incrmt_elcn_val number := null;
533 l_vr_dflt_elcn_val number := null;
534 l_vr_tx_typ_cd varchar2(30);
535 l_vr_acty_typ_cd varchar2(30);
536 l_vr_trtmt_cd varchar2(30);
537 l_prtt_rt_val_id number;
538 l_bnft_prvdr_pool_id number;
539 l_rt_strt_dt date;
540 l_rt_strt_dt_cd varchar2(30);
541 l_rt_strt_dt_rl number;
542 l_ann_dflt_val number;
543 l_ann_val number;
544 l_cmcd_val number;
545 l_pl_opt_name varchar2(600) := null;
546 --
547 -- For selfservice enhancement : Communicated values are required
548 -- on self service pages.
549 --
550 l_cmcd_mn_val number;
551 l_cmcd_ann_mn_val number;
552 l_cmcd_mx_val number;
553 l_cmcd_ann_mx_val number;
554 --
555
556 -- bug 3274092
557 l_cmcd_dflt_val number;
558 l_cmcd_ann_dflt_val number;
559 -- bug 3274092
560 --
561 l_cmcd_acty_ref_perd_cd varchar2(30);
562 l_acty_ref_perd_cd varchar2(30);
563 l_enrt_info_rt_freq_cd varchar2(30);
564 l_cvg_calc_amt_mthd_id varchar2(30);
565 l_lwr_outputs ff_exec.outputs_t;
566 l_upr_outputs ff_exec.outputs_t;
567 l_rt_ovridn_flag varchar2(15);
568 l_rt_ovridn_thru_dt date;
569 l_rt_val number;
570 l_prtn_strt_dt date;
571 l_jurisdiction_code varchar2(30);
572 l_dflt_enrt_cd varchar2(30);
573 l_dflt_enrt_rl number;
574 l_ptd_balance number;
575 l_clm_balance number;
576 l_commit number;
577 l_ultmt_upr_lmt number;
578 l_ultmt_lwr_lmt number;
579 l_ultmt_upr_lmt_calc_rl number;
580 l_ultmt_lwr_lmt_calc_rl number;
581 l_vr_ann_mn_elcn_val number;
582 l_vr_ann_mx_elcn_val number;
583 l_enrt_bnft_id number := p_enrt_bnft_id ;
584 l_assignment_id per_all_assignments_f.assignment_id%type;
585 l_payroll_id per_all_assignments_f.payroll_id%type;
586 l_organization_id per_all_assignments_f.organization_id%type;
587 --
588 -- Bug 4637525
589 l_cwb_dflt_val number;
590 l_cwb_incrmt_elcn_val number;
591 l_cwb_mx_elcn_val number;
592 l_cwb_mn_elcn_val number;
593 -- Bug 4637525
594 l_package varchar2(80) := g_package||'.main';
595
596 --
597 -- open c_pl_opt_name cursor only if error needs to be displayed.
598 --
599 cursor c_pl_opt_name is
600 select pln.name || ' '|| opt.name
601 from ben_elig_per_elctbl_chc epe,
602 ben_pl_f pln,
603 ben_oipl_f oipl,
604 ben_opt_f opt
605 where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
606 and epe.pl_id = pln.pl_id
607 and epe.oipl_id = oipl.oipl_id(+)
608 and oipl.opt_id = opt.opt_id(+)
609 and p_effective_date between
610 pln.effective_start_date and pln.effective_end_date
611 and p_effective_date between
612 oipl.effective_start_date(+) and oipl.effective_end_date(+)
613 and p_effective_date between
614 opt.effective_start_date(+) and opt.effective_end_date(+);
615
616 cursor c_enrt_rt(p_elig_per_elctbl_chc_id in number,
617 p_enrt_bnft_id in number,
618 p_acty_base_rt_id in number)
619 is
620 select enrt.mn_elcn_val,
621 enrt.mx_elcn_val,
622 enrt.incrmt_elcn_val
623 from ben_enrt_rt enrt
624 where (enrt.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
625 or enrt.enrt_bnft_id = p_enrt_bnft_id) and
626 enrt.acty_base_rt_id = p_acty_base_rt_id;
627 l_enrt_rt c_enrt_rt%rowtype;
628 --
629 -- commented out to fix bug 3457483
630
631 /* cursor c_asg
632 (c_effective_date in date
633 )
634 is
635 select asg.assignment_id,
636 asg.organization_id,
637 asg.payroll_id,
638 loc.region_2
639 from per_all_assignments_f asg,hr_locations_all loc
640 where asg.person_id = p_person_id
641 and asg.assignment_type <> 'C'
642 and asg.primary_flag = 'Y'
643 and asg.location_id = loc.location_id(+)
644 and c_effective_date
645 between asg.effective_start_date
646 and asg.effective_end_date
647 order by -- bug fix 2691169
648 asg.assignment_type desc;
649 l_asg c_asg%rowtype; */
650 -- end bug fix 3457483
651 cursor c_epe
652 is
653 select epe.pl_id,
654 epe.oipl_id,
655 epe.pgm_id,
656 epe.pl_typ_id,
657 epe.business_group_id,
658 epe.per_in_ler_id,
659 epe.plip_id,
660 epe.ptip_id,
661 epe.prtt_enrt_rslt_id,
662 epe.roll_crs_flag,
663 epe.elctbl_flag,
664 epe.enrt_cvg_strt_dt,
665 epe.enrt_cvg_strt_dt_cd,
666 epe.enrt_cvg_strt_dt_rl,
667 epe.yr_perd_id,
668 epe.pl_ordr_num,
669 epe.oipl_ordr_num,
670 pel.enrt_perd_id,
671 pel.lee_rsn_id,
672 pel.enrt_perd_strt_dt,
673 pel.acty_ref_perd_cd,
674 pil.person_id,
675 pil.ler_id
676 from ben_elig_per_elctbl_chc epe,
677 ben_per_in_ler pil,
678 ben_pil_elctbl_chc_popl pel
679 where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
680 and epe.per_in_ler_id = pil.per_in_ler_id
681 and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
682 and epe.per_in_ler_id = pel.per_in_ler_id;
683 l_epe c_epe%rowtype;
684 --
685 --
686 cursor c_opt
687 (c_effective_date in date
688 )
689 is
690 select opt_id
691 from ben_oipl_f oipl
692 where oipl.oipl_id = l_epe.oipl_id
693 and c_effective_date
694 between oipl.effective_start_date
695 and oipl.effective_end_date;
696
697
698 l_opt c_opt%rowtype;
699
700 cursor c_abr
701 (c_effective_date in date
702 )
703 is
704 select abr.rt_mlt_cd,
705 abr.val,
706 abr.mn_elcn_val,
707 abr.mx_elcn_val,
708 abr.incrmt_elcn_val,
709 abr.dflt_val,
710 abr.rndg_cd,
711 abr.rndg_rl,
712 abr.pl_id,
713 abr.oipl_id,
714 abr.pgm_id,
715 abr.acty_typ_cd,
716 abr.rt_typ_cd,
717 abr.bnft_rt_typ_cd,
718 abr.ptd_comp_lvl_fctr_id,
719 abr.entr_val_at_enrt_flag,
720 abr.clm_comp_lvl_fctr_id,
721 abr.tx_typ_cd,
722 abr.val_calc_rl,
723 abr.nnmntry_uom,
724 abr.entr_ann_val_flag,
725 abr.comp_lvl_fctr_id,
726 abr.dsply_on_enrt_flag,
727 abr.use_to_calc_net_flx_cr_flag,
728 abr.rt_usg_cd,
729 abr.ann_mn_elcn_val,
730 abr.ann_mx_elcn_val,
731 abr.lwr_lmt_val,
732 abr.lwr_lmt_calc_rl,
733 abr.upr_lmt_val,
734 abr.upr_lmt_calc_rl,
735 abr.actl_prem_id,
736 abr.use_calc_acty_bs_rt_flag ,
737 abr.det_pl_ytd_cntrs_cd, -- Bug#1791203: added
738 abr.element_type_id,
739 abr.pay_rate_grade_rule_id,
740 abr.ordr_num,
741 abr.rate_periodization_rl, --BUG 3463457
742 abr.mn_mx_elcn_rl, -- Min Max Enhancement : 3981982
743 abr.input_va_calc_rl
744 from ben_acty_base_rt_f abr
745 where abr.acty_base_rt_id = p_acty_base_rt_id
746 and c_effective_date
747 between abr.effective_start_date
748 and abr.effective_end_date ;
749
750 l_abr c_abr%rowtype;
751 --
752 cursor c_enb(p_enrt_bnft_id number)
753 is
754 select enb.val,
755 enb.dflt_val
756 from ben_enrt_bnft enb
757 where enb.enrt_bnft_id = p_enrt_bnft_id;
758 --
759 --Bug 2192102 added new cursor to find the cvg at the same level
760 cursor c_enb_fc is
761 select
762 enb.enrt_bnft_id
763 from
764 ben_enrt_bnft enb,
765 ben_elig_per_elctbl_chc epe,
766 ben_elig_per_elctbl_chc epe_fc
767 where
768 enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
769 and epe.pgm_id = epe_fc.pgm_id
770 and nvl(epe.oipl_id,-99999) = nvl(epe_fc.oipl_id,-99999)
771 and nvl(epe.pl_id,-99999) = nvl(epe_fc.pl_id,-99999)
772 and nvl(epe.plip_id,-99999) = nvl(epe_fc.plip_id,-99999)
773 and nvl(epe.ptip_id,-99999) = nvl(epe_fc.ptip_id,-99999)
774 and nvl(epe.pl_typ_id,-99999) = nvl(epe_fc.pl_typ_id,-99999)
775 and epe.per_in_ler_id = epe_fc.per_in_ler_id
776 and epe_fc.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id ;
777 --
778 l_enb_fc c_enb_fc%rowtype;
779 --
780 cursor c_cvg_pl
781 (c_effective_date in date
782 )
783 is
784 select ccm.cvg_amt_calc_mthd_id
785 from ben_cvg_amt_calc_mthd_f ccm,
786 ben_pl_f pln
787 where pln.pl_id = l_epe.pl_id
788 and pln.pl_id = ccm.pl_id
789 and c_effective_date
790 between pln.effective_start_date
791 and pln.effective_end_date
792 and c_effective_date
793 between ccm.effective_start_date
794 and ccm.effective_end_date;
795
796 cursor c_cvg_oipl
797 (c_effective_date in date
798 )
799 is
800 select ccm.cvg_amt_calc_mthd_id
801 from ben_cvg_amt_calc_mthd_f ccm,
802 ben_oipl_f cop
803 where cop.oipl_id = l_epe.oipl_id
804 and cop.oipl_id = ccm.oipl_id
805 and c_effective_date
806 between cop.effective_start_date
807 and cop.effective_end_date
808 and c_effective_date
809 between ccm.effective_start_date
810 and ccm.effective_end_date;
811
812 cursor c_abr2
813 (c_effective_date in date
814 )
815 is
816 select abr2.acty_base_rt_id,abr2.entr_val_at_enrt_flag
817 from ben_acty_base_rt_f abr,
818 ben_acty_base_rt_f abr2
819 where abr.acty_base_rt_id = p_acty_base_rt_id
820 and abr2.acty_base_rt_id = abr.parnt_acty_base_rt_id
821 and abr2.parnt_chld_cd = 'PARNT'
822 and c_effective_date
823 between abr.effective_start_date
824 and abr.effective_end_date
825 and c_effective_date
826 between abr2.effective_start_date
827 and abr2.effective_end_date;
828
829 cursor c_pln
830 (c_effective_date in date,
831 c_pl_id in number
832 )
833 is
834 select pln.nip_acty_ref_perd_cd
835 ,pln.nip_enrt_info_rt_freq_cd
836 ,name
837 from ben_pl_f pln
838 where pln.pl_id = c_pl_id
839 and c_effective_date
840 between pln.effective_start_date
841 and pln.effective_end_date;
842
843 l_pln c_pln%rowtype;
844 l_pln2 c_pln%rowtype;
845 cursor c_pgm
846 (c_effective_date in date
847 )
848 is
849 select pgm.acty_ref_perd_cd,
850 pgm.enrt_info_rt_freq_cd
851 from ben_pgm_f pgm
852 where pgm.pgm_id = l_epe.pgm_id
853 and c_effective_date
854 between pgm.effective_start_date
855 and pgm.effective_end_date;
856
857 l_pgm c_pgm%rowtype;
858
859 cursor c_abp
860 (c_effective_date in date
861 )
862 is
863 select abp.bnft_prvdr_pool_id
864 from ben_aplcn_to_bnft_pool_f abp
865 where abp.acty_base_rt_id = p_acty_base_rt_id
866 and c_effective_date
867 between abp.effective_start_date
868 and abp.effective_end_date
869 and abp.bnft_prvdr_pool_id in
870 (select epe.bnft_prvdr_pool_id
871 from ben_elig_per_elctbl_chc epe
872 where epe.pgm_id = l_epe.pgm_id
873 and epe.per_in_ler_id = l_epe.per_in_ler_id);
874
875
876 --Bug 5031047
877 --Modified the order by clause to prv.rt_Start_dt asecending
878 --and added the clause c_effective_date < prv.rt_end_dt
879 --for the below 3 prv cursors
880
881 cursor c_prv
882 (c_effective_date in date
883 ,c_acty_base_rt_id in number
884 ,c_elig_per_elctbl_chc_id in number
885 )
886 is
887 select prv.prtt_rt_val_id,
888 prv.rt_ovridn_flag,
889 prv.rt_ovridn_thru_dt,
890 prv.rt_val,
891 prv.ann_rt_val,
892 prv.cmcd_rt_val, -- Bug 6015724
893 prv.rt_strt_dt,
894 prv.rt_end_dt
895 from ben_prtt_rt_val prv,
896 ben_prtt_enrt_rslt_f pen,
897 ben_elig_per_elctbl_chc epe
898 where epe.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
899 and epe.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
900 and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
901 and pen.prtt_enrt_rslt_stat_cd is null
902 and prv.prtt_rt_val_stat_cd is null
903 and prv.acty_base_rt_id = c_acty_base_rt_id
904 and c_effective_date
905 between pen.enrt_cvg_strt_dt
906 and pen.enrt_cvg_thru_dt
907 --and prv.acty_typ_cd = l_abr.acty_typ_cd bug 1295277 comment out.
908 --and prv.tx_typ_cd = l_abr.tx_typ_cd
909 and c_effective_date
910 between pen.effective_start_date
911 and pen.effective_end_date
912 /* get the latest prv for the enrollment result id */
913 and ((c_effective_date <= prv.rt_end_dt) or (prv.rt_strt_dt = prv.rt_end_dt))
914 order by prv.rt_strt_dt desc; -- 5748126 Changed ORDER-BY from ASC to DESC
915
916 cursor c_perfprv
917 (c_effective_date in date
918 ,c_acty_base_rt_id in number
919 ,c_prtt_enrt_rslt_id in number
920 )
921 is
922 select prv.prtt_rt_val_id,
923 prv.rt_ovridn_flag,
924 prv.rt_ovridn_thru_dt,
925 prv.rt_val,
926 prv.ann_rt_val,
927 prv.cmcd_rt_val, -- Bug 6015724
928 prv.rt_strt_dt,
929 prv.rt_end_dt
930 from ben_prtt_rt_val prv,
931 ben_prtt_enrt_rslt_f pen
932 where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
933 and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
934 and pen.prtt_enrt_rslt_stat_cd is null
935 and prv.prtt_rt_val_stat_cd is null
936 and prv.acty_base_rt_id = c_acty_base_rt_id
937 and c_effective_date
938 between pen.enrt_cvg_strt_dt
939 and pen.enrt_cvg_thru_dt
940 --RCHASE wwbug#1207803.999 - correct perfprv cursor to fetch rows when
941 -- updates have been made to the enrollment
942 -- result past the life event occured date
943 --and c_effective_date
944 -- between pen.effective_start_date
945 -- and pen.effective_end_date
946 and pen.effective_end_date = hr_api.g_eot
947 and ((c_effective_date <= prv.rt_end_dt) or (prv.rt_strt_dt = prv.rt_end_dt))
948 --RCHASE end
949 order by prv.rt_strt_dt desc; -- 5748126 Changed ORDER-BY from ASC to DESC
950
951 -- if the coverage starts in future to the life event occurred date like coverage code 'First of
952 -- next month' - 2 life events occurs in the same month
953 cursor c_perfprv_2
954 (c_effective_date in date
955 ,c_acty_base_rt_id in number
956 ,c_prtt_enrt_rslt_id in number
957 )
958 is
959 select prv.prtt_rt_val_id,
960 prv.rt_ovridn_flag,
961 prv.rt_ovridn_thru_dt,
962 prv.rt_val,
963 prv.ann_rt_val,
964 prv.cmcd_rt_val, -- Bug 6015724
965 prv.rt_strt_dt,
966 prv.rt_end_dt
967 from ben_prtt_rt_val prv,
968 ben_prtt_enrt_rslt_f pen
969 where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
970 and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
971 and pen.prtt_enrt_rslt_stat_cd is null
972 and prv.prtt_rt_val_stat_cd is null
973 and prv.acty_base_rt_id = c_acty_base_rt_id
974 and c_effective_date < pen.enrt_cvg_thru_dt
975 and pen.effective_end_date = hr_api.g_eot
976 and ((c_effective_date <= prv.rt_end_dt) or (prv.rt_strt_dt = prv.rt_end_dt))
977 order by prv.rt_strt_dt desc; -- 5748126 Changed ORDER-BY from ASC to DESC
978
979
980 --End Bug 5031047
981
982 -- Determines the current eligibility for an option
983 --
984 cursor c_current_elig
985 (c_person_id in number
986 ,c_pgm_id in number
987 ,c_pl_id in number
988 ,c_opt_id in number
989 ,c_nvlopt_id in number
990 ,c_effective_date in date
991 )
992 is
993 select epo.prtn_strt_dt
994 from ben_elig_per_f ep,
995 ben_elig_per_opt_f epo,
996 ben_per_in_ler pil
997 where ep.person_id=c_person_id
998 and nvl(ep.pgm_id,-1)=c_pgm_id
999 and ep.pl_id=c_pl_id
1000 and epo.opt_id=c_opt_id
1001 and c_effective_date
1002 between ep.effective_start_date and ep.effective_end_date
1003 and ep.elig_per_id=epo.elig_per_id
1004 and c_effective_date
1005 between epo.effective_start_date and epo.effective_end_date
1006 and pil.per_in_ler_id(+) = ep.per_in_ler_id
1007 and ( pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
1008 or
1009 pil.per_in_ler_stat_cd is null)
1010 union
1011 select prtn_strt_dt
1012 from ben_elig_per_f ep,
1013 ben_per_in_ler pil
1014 where ep.person_id=c_person_id
1015 and nvl(ep.pgm_id,-1)=c_pgm_id
1016 and ep.pl_id=c_pl_id
1017 and c_effective_date
1018 between ep.effective_start_date and ep.effective_end_date
1019 and pil.per_in_ler_id(+) = ep.per_in_ler_id
1020 and ( pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
1021 or pil.per_in_ler_stat_cd is null)
1022 and c_nvlopt_id=hr_api.g_number;
1023
1024 cursor c_enrt_prem(p_actl_prem_id in number)
1025 is
1026 select ecr.val
1027 from ben_enrt_prem ecr,
1028 ben_per_in_ler pil,
1029 ben_elig_per_elctbl_chc epe
1030 where ecr.actl_prem_id = p_actl_prem_id
1031 and ecr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1032 and epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id
1033 and pil.per_in_ler_id = epe.per_in_ler_id
1034 --- when the premium depend on coverage , coverage inturn multi rance
1035 --- benefit is is required to find the correct row : bug 1628762
1036 and (ecr.enrt_bnft_id is null or nvl(ecr.enrt_bnft_id ,0) = nvl(p_enrt_bnft_id ,0) )
1037 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
1038
1039 Cursor c_state
1040 is
1041 select region_2
1042 from hr_locations_all loc,per_all_assignments_f asg
1043 where loc.location_id = asg.location_id
1044 and asg.assignment_type <> 'C'
1045 and asg.person_id = p_person_id
1046 and asg.primary_flag = 'Y'
1047 and p_effective_date between
1048 asg.effective_start_date and asg.effective_end_date;
1049
1050 l_state c_state%rowtype;
1051 --
1052 Cursor c_pgr (c_pay_rate_grade_rule_id in number
1053 ,c_effective_date in date ) is
1054 select value
1055 from pay_grade_rules_f pgr
1056 where grade_rule_id = c_pay_rate_grade_rule_id
1057 and c_effective_date between
1058 pgr.effective_start_date and pgr.effective_end_date;
1059 --
1060 cursor c_pln_auto_distr (p_elig_per_elctbl_chc_id number)
1061 is
1062 select auto_distr_flag
1063 from ben_enrt_perd enp,
1064 ben_pil_elctbl_chc_popl pel,
1065 ben_elig_per_elctbl_chc epe
1066 where enp.enrt_perd_id = pel.enrt_perd_id
1067 and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
1068 and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
1069 -- and enp.business_group_id = p_business_group_id;
1070 --
1071 -- Bug No 4538786 Added cursor to fetch the rate and element id
1072 -- of the Flex Shell plan instead of the flex credits
1073 --
1074 cursor get_rt_and_element(c_pgm_id in number,
1075 c_per_in_ler_id in number) is
1076 select abr.acty_base_rt_id, abr.element_type_id, epe.elig_per_elctbl_chc_id
1077 from ben_elig_per_elctbl_chc epe, ben_acty_base_rt_f abr
1078 where (epe.pl_id = abr.pl_id or epe.plip_id = abr.plip_id)
1079 and epe.pgm_id = c_pgm_id and epe.per_in_ler_id = c_per_in_ler_id
1080 and epe.comp_lvl_cd = 'PLANFC';
1081 l_rt_and_element get_rt_and_element%rowtype;
1082
1083 --
1084 --6314463
1085 cursor c_force_prem_calc(p_actl_prem_id number) is
1086 select 'Y'
1087 from ben_actl_prem_f
1088 where actl_prem_id = p_actl_prem_id
1089 and (mlt_cd in ('CVG', 'NSVU') or
1090 exists (select null from ben_actl_prem_vrbl_rt_f
1091 where actl_prem_id = p_actl_prem_id));
1092 l_force_prem_calc varchar2(30);
1093
1094 -- End Bug No 4538786
1095
1096 --ICM Changes
1097 --
1098 cursor c_abr_level is
1099 select pl_id,oipl_id
1100 from ben_acty_base_rt_f abr
1101 where abr.acty_base_rt_id = p_acty_base_rt_id
1102 and p_effective_date between abr.effective_start_date
1103 and abr.effective_end_date;
1104 --
1105 l_abr_level c_abr_level%ROWTYPE;
1106 --
1107 cursor c_elem_link(p_element_type_id number) is
1108 select p.element_link_id
1109 from pay_element_links_f p
1110 where element_type_id = p_element_type_id
1111 and p_effective_date between p.effective_start_date
1112 and p.effective_end_date;
1113 --
1114 l_elem_link pay_element_links_f.element_link_id%TYPE;
1115 --
1116 l_element_link_id_table g_element_link_id_table;
1117 --
1118 l_input_value_id pay_input_values_f.input_value_id%TYPE;
1119 --
1120 cursor c_pl_typ(p_pl_id number) is
1121 select opt_typ_cd
1122 from ben_pl_typ_f ptp,
1123 ben_pl_f pln
1124 where pln.pl_id = p_pl_id
1125 and ptp.pl_typ_id = pln.pl_typ_id;
1126 --
1127 l_opt_typ_cd ben_pl_typ_f.opt_typ_cd%TYPE;
1128 --
1129 cursor c_input_values(p_element_link_id number) is
1130 select p.input_value_id,p.default_value
1131 from pay_link_input_values_f p
1132 where element_link_id = p_element_link_id
1133 and p_effective_date between
1134 effective_start_date and effective_end_date
1135 order by input_value_id ;
1136 --
1137 l_ext_inpval c_input_values%ROWTYPE;
1138 --
1139 l_element_link_id number;
1140 --
1141 cursor c_ext_inpval(p_abr_id in number)
1142 is
1143 select eiv.extra_input_value_id,
1144 eiv.input_value_id,
1145 eiv.acty_base_rt_id,
1146 eiv.input_text,
1147 eiv.return_var_name,
1148 eiv.upd_when_ele_ended_cd
1149 from ben_extra_input_values eiv
1150 where eiv.acty_base_rt_id = p_abr_id;
1151 --
1152 l_ext_inpval_rec c_ext_inpval%rowtype;
1153 --
1154 l_param_tab ff_exec.outputs_t;
1155 l_counter number;
1156 --
1157 l_cnt_inp_vals number;
1158 l_cnt_links number;
1159 -- End Of ICM Changes
1160
1161 l_effective_date date;
1162 l_calc_val number;
1163 l_prnt_entr_val_at_enrt_flag varchar2(30);
1164 l_cal_val number;
1165 l_ann_rt_val number;
1166 l_cmcd_rt_val number; -- Bug 6015724
1167 l_element_type_id number;
1168 l_pay_annualization_factor number;
1169 l_pln_auto_distr_flag varchar2(1):= 'N';
1170 --GEVITY
1171 l_dfnd_dummy number;
1172 l_ann_dummy number;
1173 l_cmcd_dummy number;
1174 --END GEVITY
1175 l_cvg_eff_dt date;
1176 l_fonm_rt_strt_dt date;
1177
1178 -- Getting mode for bug 3274902
1179 l_env ben_env_object.g_global_env_rec_type;
1180 l_mode l_env.mode_cd%TYPE;
1181 l_prv_rt_strt_dt date; -- 5748126
1182 l_prv_rt_end_dt date; -- 5748126
1183
1184 function get_input_value_id(p_extra_input_value_id number)
1185 return number is
1186 cursor c_input_value_id is
1187 select input_value_id
1188 from ben_extra_input_values
1189 where extra_input_value_id = p_extra_input_value_id;
1190 --
1191 l_input_value_id number;
1192 --
1193 begin
1194 --
1195 open c_input_value_id;
1196 fetch c_input_value_id into l_input_value_id;
1197 close c_input_value_id;
1198 --
1199 return l_input_value_id;
1200 end;
1201 --
1202 begin
1203 --
1204 g_debug := hr_utility.debug_enabled;
1205 hr_utility.set_location ('Entering '||l_package,10);
1206 --
1207 l_effective_date := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,nvl(p_lf_evt_ocrd_dt,p_effective_date));
1208 l_cvg_eff_dt := nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,nvl(p_lf_evt_ocrd_dt,p_effective_date));
1209 l_fonm_rt_strt_dt := ben_manage_life_events.g_fonm_rt_strt_dt;
1210 --
1211 hr_utility.set_location('l_effective_date'||l_effective_date,10);
1212 hr_utility.set_location('p_acty_base_rt_id'||p_acty_base_rt_id,20);
1213 hr_utility.set_location('p_person_id '||p_person_id,20);
1214 hr_utility.set_location('p_effective_date'||p_effective_date,20);
1215 hr_utility.set_location(' p_elig_per_elctbl_chc_id'||p_elig_per_elctbl_chc_id,20);
1216 if p_calc_only_rt_val_flag then
1217 hr_utility.set_location(' p_calc_only_rt_val_flag',10);
1218 end if ;
1219 --
1220 -- Ensure relevant parameters have been populated with values
1221 --
1222 if p_acty_base_rt_id is null then
1223 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
1224 fnd_message.set_token('PACKAGE',l_package);
1225 fnd_message.set_token('PROC','Standard Rates');
1226 fnd_message.set_token('PARAM','p_acty_base_rt_id');
1227 fnd_message.raise_error;
1228 elsif p_person_id is null then
1229 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
1230 fnd_message.set_token('PACKAGE',l_package);
1231 fnd_message.set_token('PROC','Standard Rates');
1232 fnd_message.set_token('PARAM','p_person_id');
1233 fnd_message.raise_error;
1234 elsif p_effective_date is null then
1235 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
1236 fnd_message.set_token('PACKAGE',l_package);
1237 fnd_message.set_token('PROC','Standard Rates');
1238 fnd_message.set_token('PARAM','p_effective_date');
1239 fnd_message.raise_error;
1240 elsif p_elig_per_elctbl_chc_id is null and not(p_calc_only_rt_val_flag) then
1241 fnd_message.set_name('BEN','BEN_91832_PACKAGE_PARAM_NULL');
1242 fnd_message.set_token('PACKAGE',l_package);
1243 fnd_message.set_token('PROC','Standard Rates');
1244 fnd_message.set_token('PARAM','p_elig_per_elctbl_chc_id');
1245 fnd_message.raise_error;
1246 end if;
1247 hr_utility.set_location (' Checking Electable Choice ',20);
1248 --
1249 -- Populate electable choice information
1250 --
1251 l_coverage_value := p_bnft_amt;
1252 if p_calc_only_rt_val_flag then
1253 --
1254 l_epe.pl_id := p_pl_id;
1255 l_epe.pgm_id := p_pgm_id;
1256 l_epe.oipl_id := p_oipl_id;
1257 l_epe.pl_typ_id := p_pl_typ_id;
1258 l_epe.per_in_ler_id := p_per_in_ler_id;
1259 l_epe.ler_id := p_ler_id;
1260 l_epe.business_group_id := p_business_group_id;
1261 l_epe.enrt_perd_strt_dt := p_effective_date;
1262 l_epe.person_id := p_person_id;
1263 --
1264 -- Check if the context row is populated
1265 --
1266 elsif p_currepe_row.elig_per_elctbl_chc_id is not null
1267 then
1268 --
1269 l_epe.pgm_id := p_currepe_row.pgm_id;
1270 l_epe.pl_typ_id := p_currepe_row.pl_typ_id;
1271 l_epe.ptip_id := p_currepe_row.ptip_id;
1272 l_epe.pl_id := p_currepe_row.pl_id;
1273 l_epe.plip_id := p_currepe_row.plip_id;
1274 l_epe.oipl_id := p_currepe_row.oipl_id;
1275 l_epe.per_in_ler_id := p_currepe_row.per_in_ler_id;
1276 l_epe.ler_id := p_currepe_row.ler_id;
1277 l_epe.business_group_id := p_currepe_row.business_group_id;
1278 l_epe.enrt_perd_id := p_currepe_row.enrt_perd_id;
1279 l_epe.lee_rsn_id := p_currepe_row.lee_rsn_id;
1280 l_epe.enrt_perd_strt_dt := p_currepe_row.enrt_perd_strt_dt;
1281 l_epe.prtt_enrt_rslt_id := p_currepe_row.prtt_enrt_rslt_id;
1282 --
1283 l_epe.enrt_cvg_strt_dt := p_currepe_row.enrt_cvg_strt_dt;
1284 l_epe.enrt_cvg_strt_dt_cd := p_currepe_row.enrt_cvg_strt_dt_cd;
1285 l_epe.enrt_cvg_strt_dt_rl := p_currepe_row.enrt_cvg_strt_dt_rl;
1286 l_epe.yr_perd_id := p_currepe_row.yr_perd_id;
1287 l_epe.person_id := p_person_id;
1288 --
1289 else
1290 open c_epe;
1291 fetch c_epe into l_epe;
1292 if c_epe%notfound then
1293 close c_epe;
1294 fnd_message.set_name('BEN','BEN_92743_NO_ABR_EPE_EXISTS');
1295 fnd_message.raise_error;
1296 end if;
1297 close c_epe;
1298 end if;
1299 hr_utility.set_location ('Dn Pop EPE'||l_package,10);
1300 --
1301 -- Get related information from either the context record or
1302 --
1303 if p_currepe_row.oipl_id is not null
1304 then
1305 --
1306 l_opt.opt_id := p_currepe_row.opt_id;
1307 --
1308 elsif l_epe.oipl_id is not null
1309 then
1310 --
1311 open c_opt
1312 (c_effective_date => l_effective_date
1313 );
1314 fetch c_opt into l_opt;
1315 close c_opt;
1316 --
1317 end if;
1318 --
1319 open c_abr
1320 (c_effective_date => l_effective_date
1321 );
1322 fetch c_abr into l_abr;
1323 if c_abr%notfound then
1324 close c_abr;
1325 fnd_message.set_name('BEN','BEN_92738_NO_ABR_EXISTS');
1326 fnd_message.raise_error;
1327 end if;
1328 close c_abr;
1329 --
1330 if l_abr.acty_typ_cd in ('CWBWB','CWBDB') then
1331 open c_pln_auto_distr (p_currepe_row.elig_per_elctbl_chc_id);
1332 fetch c_pln_auto_distr into l_pln_auto_distr_flag;
1333 close c_pln_auto_distr;
1334 end if;
1335 hr_utility.set_location('Roundng'||l_abr.rndg_cd,951);
1336 --
1337 -- get values for rules and limit checking
1338 --
1339 -- bug fix 3457483
1340 /* open c_asg
1341 (c_effective_date => l_effective_date
1342 );
1343 fetch c_asg into l_asg;
1344 close c_asg; */
1345 ben_element_entry.get_abr_assignment
1346 (p_person_id => p_person_id
1347 ,p_effective_date => l_effective_date
1348 ,p_acty_base_rt_id => p_acty_base_rt_id
1349 ,p_organization_id => l_organization_id
1350 ,p_payroll_id => l_payroll_id
1351 ,p_assignment_id => l_assignment_id
1352 );
1353 --
1354 -- ICM Changes
1355 -- l_element_link_id_table.DELETE;
1356
1357 --
1358 OPEN c_pl_typ (l_epe.pl_id);
1359
1360 --
1361 FETCH c_pl_typ
1362 INTO l_opt_typ_cd;
1363
1364 --
1365 CLOSE c_pl_typ;
1366
1367 --
1368 hr_utility.set_location ('l_input_value_id' || l_input_value_id, 33);
1369
1370 IF l_opt_typ_cd = 'ICM'
1371 THEN
1372 --
1373 OPEN c_abr_level;
1374
1375 FETCH c_abr_level
1376 INTO l_abr_level;
1377
1378 CLOSE c_abr_level;
1379
1380 --
1381 IF l_abr_level.pl_id IS NOT NULL
1382 THEN
1383 hr_utility.set_location ('PL LEVEL' || l_abr_level.pl_id, 32);
1384 ELSIF l_abr_level.oipl_id IS NOT NULL
1385 THEN
1386 hr_utility.set_location ('OIPL LEVEL' || l_abr_level.oipl_id, 32);
1387 END IF;
1388
1389 -- To find the eligible element link for the element attached to a Rate
1390 OPEN c_elem_link (l_abr.element_type_id);
1391
1392 --
1393 LOOP
1394 --
1395 FETCH c_elem_link
1396 INTO l_input_value_id;
1397
1398 EXIT WHEN c_elem_link%NOTFOUND;
1399 hr_utility.set_location ('l_input_value_id' || l_input_value_id, 32);
1400 --
1401 ben_element_entry.get_link
1402 (p_assignment_id => l_assignment_id,
1403 p_element_type_id => l_abr.element_type_id,
1404 p_business_group_id => p_business_group_id,
1405 p_input_value_id => l_input_value_id,
1406 p_effective_date => p_effective_date,
1407 p_element_link_id => l_element_link_id
1408 );
1409
1410 --
1411 IF l_element_link_id = l_input_value_id
1412 THEN
1413 --
1414 hr_utility.set_location ('l_element_link_id' || l_element_link_id,
1415 321
1416 );
1417 l_element_link_id_table (l_element_link_id_table.COUNT + 1) :=
1418 l_element_link_id;
1419 --
1420 END IF;
1421 --
1422 END LOOP;
1423
1424 --
1425 --MAA
1426 IF l_abr.input_va_calc_rl IS NOT NULL
1427 THEN
1428 --
1429 l_outputs.DELETE;
1430 hr_utility.set_location ( 'l_abr.input_va_calc_rl'
1431 || l_abr.input_va_calc_rl,
1432 13
1433 );
1434 -- getting the values returned by rule
1435
1436 BEGIN
1437 --
1438 l_outputs :=
1439 benutils.formula (p_formula_id => l_abr.input_va_calc_rl,
1440 p_effective_date => l_effective_date,
1441 p_business_group_id => p_business_group_id,
1442 p_assignment_id => l_assignment_id,
1443 p_organization_id => l_organization_id,
1444 p_pl_id => l_epe.pl_id,
1445 p_pl_typ_id => l_epe.pl_typ_id,
1446 p_acty_base_rt_id => p_acty_base_rt_id
1447 );
1448 --
1449 hr_utility.set_location ('l_count' || l_outputs.COUNT, 321);
1450 --
1451 EXCEPTION
1452 WHEN OTHERS
1453 THEN
1454 fnd_message.set_name ('BEN', 'BEN_92311_FORMULA_VAL_PARAM');
1455 fnd_message.set_token ('PROC', l_package);
1456 fnd_message.set_token ('FORMULA', l_abr.input_va_calc_rl);
1457 fnd_message.set_token ('PARAMETER',
1458 l_outputs (l_outputs.COUNT).NAME
1459 );
1460 fnd_message.raise_error;
1461 END;
1462 --
1463 END IF;
1464
1465 hr_utility.set_location ( 'l_element_link_id_table.LAST'
1466 || l_element_link_id_table.LAST,
1467 23
1468 );
1469 --
1470 l_counter := 0;
1471 --
1472 l_cnt_links := 0;
1473 l_cnt_inp_vals := 0;
1474 hr_utility.set_location (l_element_link_id_table.FIRST, 1121);
1475
1476 -- putting all the default values in an element link to a table that will be used to insert values in ben_icd_chc_rates
1477 IF l_element_link_id_table.COUNT > 0
1478 THEN
1479 --
1480 FOR i IN
1481 l_element_link_id_table.FIRST .. l_element_link_id_table.LAST
1482 --
1483 LOOP
1484 hr_utility.set_location ( 'l_element_link_id_table (i)'
1485 || l_element_link_id_table (i),
1486 11
1487 );
1488 IF NOT l_icd_chc_rates_tab.EXISTS(1) THEN
1489 --
1490 l_cnt_links := 1;
1491 --
1492 ELSE
1493 --
1494 l_cnt_links := l_icd_chc_rates_tab.LAST + 1;
1495 --
1496 END IF;
1497 --
1498 -- l_cnt_links := 1;
1499 hr_utility.set_location('l_cnt_links'||l_cnt_links,14);
1500 --
1501 -- creating row for icd pl sql table
1502 l_icd_chc_rates_tab (l_cnt_links).element_link_id :=
1503 l_element_link_id_table (i);
1504
1505 --
1506 IF l_abr_level.pl_id IS NULL
1507 THEN
1508 hr_utility.set_location ('PLAN', 23);
1509 l_icd_chc_rates_tab (l_cnt_links).l_level := 'O';
1510 -- l_icd_chc_rates_tab (l_cnt_links).pl_id := NULL;
1511 -- l_icd_chc_rates_tab (l_cnt_links).oipl_id := l_epe.oipl_id;
1512 ELSIF l_abr_level.oipl_id IS NULL
1513 THEN
1514 hr_utility.set_location ('OIPL', 23);
1515 l_icd_chc_rates_tab (l_cnt_links).l_level := 'P';
1516 -- l_icd_chc_rates_tab (l_cnt_links).pl_id := l_epe.pl_id;
1517 -- l_icd_chc_rates_tab (l_cnt_links).oipl_id := NULL;
1518 END IF;
1519 l_icd_chc_rates_tab (l_cnt_links).pl_id := l_epe.pl_id;
1520 l_icd_chc_rates_tab (l_cnt_links).oipl_id := l_epe.oipl_id;
1521 l_icd_chc_rates_tab (l_cnt_links).l_assignment_id :=
1522 l_assignment_id;
1523 l_icd_chc_rates_tab (l_cnt_links).pl_typ_id := l_epe.pl_typ_id;
1524 l_icd_chc_rates_tab (l_cnt_links).opt_id := l_opt.opt_id;
1525 l_icd_chc_rates_tab (l_cnt_links).pl_ordr_num := l_epe.pl_ordr_num;
1526 l_icd_chc_rates_tab (l_cnt_links).oipl_ordr_num :=
1527 l_epe.oipl_ordr_num;
1528 l_icd_chc_rates_tab (l_cnt_links).pl_ordr_num := l_epe.pl_ordr_num;
1529 l_icd_chc_rates_tab (l_cnt_links).element_type_id :=
1530 l_abr.element_type_id;
1531 l_icd_chc_rates_tab (l_cnt_links).acty_base_rt_id :=
1532 p_acty_base_rt_id;
1533 --
1534 BEGIN
1535 --
1536 OPEN c_input_values (l_element_link_id_table (i));
1537
1538 -- fetching default values defined in element link
1539 l_cnt_inp_vals := 1;
1540
1541 --
1542 LOOP
1543 FETCH c_input_values
1544 INTO l_ext_inpval;
1545
1546 --
1547 EXIT WHEN c_input_values%NOTFOUND;
1548 -- assigning default values
1549 --
1550 hr_utility.set_location ('l_cnt_inp_vals' || l_cnt_inp_vals,
1551 21
1552 );
1553
1554 IF l_cnt_inp_vals = 1
1555 THEN
1556 l_icd_chc_rates_tab (l_cnt_links).input_value_id1 :=
1557 l_ext_inpval.input_value_id;
1558 l_icd_chc_rates_tab (l_cnt_links).input_value1 :=
1559 l_ext_inpval.DEFAULT_VALUE;
1560 ELSIF l_cnt_inp_vals = 2
1561 THEN
1562 l_icd_chc_rates_tab (l_cnt_links).input_value_id2 :=
1563 l_ext_inpval.input_value_id;
1564 l_icd_chc_rates_tab (l_cnt_links).input_value2 :=
1565 l_ext_inpval.DEFAULT_VALUE;
1566 ELSIF l_cnt_inp_vals = 3
1567 THEN
1568 l_icd_chc_rates_tab (l_cnt_links).input_value_id3 :=
1569 l_ext_inpval.input_value_id;
1570 l_icd_chc_rates_tab (l_cnt_links).input_value3 :=
1571 l_ext_inpval.DEFAULT_VALUE;
1572 ELSIF l_cnt_inp_vals = 4
1573 THEN
1574 l_icd_chc_rates_tab (l_cnt_links).input_value_id4 :=
1575 l_ext_inpval.input_value_id;
1576 l_icd_chc_rates_tab (l_cnt_links).input_value4 :=
1577 l_ext_inpval.DEFAULT_VALUE;
1578 ELSIF l_cnt_inp_vals = 5
1579 THEN
1580 l_icd_chc_rates_tab (l_cnt_links).input_value_id5 :=
1581 l_ext_inpval.input_value_id;
1582 l_icd_chc_rates_tab (l_cnt_links).input_value5 :=
1583 l_ext_inpval.DEFAULT_VALUE;
1584 ELSIF l_cnt_inp_vals = 6
1585 THEN
1586 l_icd_chc_rates_tab (l_cnt_links).input_value_id6 :=
1587 l_ext_inpval.input_value_id;
1588 l_icd_chc_rates_tab (l_cnt_links).input_value6 :=
1589 l_ext_inpval.DEFAULT_VALUE;
1590 ELSIF l_cnt_inp_vals = 7
1591 THEN
1592 l_icd_chc_rates_tab (l_cnt_links).input_value_id7 :=
1593 l_ext_inpval.input_value_id;
1594 l_icd_chc_rates_tab (l_cnt_links).input_value7 :=
1595 l_ext_inpval.DEFAULT_VALUE;
1596 ELSIF l_cnt_inp_vals = 8
1597 THEN
1598 l_icd_chc_rates_tab (l_cnt_links).input_value_id8 :=
1599 l_ext_inpval.input_value_id;
1600 l_icd_chc_rates_tab (l_cnt_links).input_value8 :=
1601 l_ext_inpval.DEFAULT_VALUE;
1602 ELSIF l_cnt_inp_vals = 9
1603 THEN
1604 l_icd_chc_rates_tab (l_cnt_links).input_value_id9 :=
1605 l_ext_inpval.input_value_id;
1606 l_icd_chc_rates_tab (l_cnt_links).input_value9 :=
1607 l_ext_inpval.DEFAULT_VALUE;
1608 ELSIF l_cnt_inp_vals = 10
1609 THEN
1610 l_icd_chc_rates_tab (l_cnt_links).input_value_id10 :=
1611 l_ext_inpval.input_value_id;
1612 l_icd_chc_rates_tab (l_cnt_links).input_value10 :=
1613 l_ext_inpval.DEFAULT_VALUE;
1614 ELSIF l_cnt_inp_vals = 11
1615 THEN
1616 l_icd_chc_rates_tab (l_cnt_links).input_value_id11 :=
1617 l_ext_inpval.input_value_id;
1618 l_icd_chc_rates_tab (l_cnt_links).input_value11 :=
1619 l_ext_inpval.DEFAULT_VALUE;
1620 ELSIF l_cnt_inp_vals = 12
1621 THEN
1622 l_icd_chc_rates_tab (l_cnt_links).input_value_id12 :=
1623 l_ext_inpval.input_value_id;
1624 l_icd_chc_rates_tab (l_cnt_links).input_value12 :=
1625 l_ext_inpval.DEFAULT_VALUE;
1626 ELSIF l_cnt_inp_vals = 13
1627 THEN
1628 l_icd_chc_rates_tab (l_cnt_links).input_value_id13 :=
1629 l_ext_inpval.input_value_id;
1630 l_icd_chc_rates_tab (l_cnt_links).input_value13 :=
1631 l_ext_inpval.DEFAULT_VALUE;
1632 ELSIF l_cnt_inp_vals = 14
1633 THEN
1634 l_icd_chc_rates_tab (l_cnt_links).input_value_id14 :=
1635 l_ext_inpval.input_value_id;
1636 l_icd_chc_rates_tab (l_cnt_links).input_value14 :=
1637 l_ext_inpval.DEFAULT_VALUE;
1638 ELSIF l_cnt_inp_vals = 15
1639 THEN
1640 l_icd_chc_rates_tab (l_cnt_links).input_value_id15 :=
1641 l_ext_inpval.input_value_id;
1642 l_icd_chc_rates_tab (l_cnt_links).input_value15 :=
1643 l_ext_inpval.DEFAULT_VALUE;
1644 END IF;
1645
1646 --
1647 l_cnt_inp_vals := l_cnt_inp_vals + 1;
1648 --
1649 END LOOP;
1650
1651 --
1652 CLOSE c_input_values;
1653
1654 --
1655 -- l_cnt_links := l_cnt_links + 1;
1656 --
1657 EXCEPTION
1658 WHEN OTHERS
1659 THEN
1660 hr_utility.set_location (SQLERRM, 23);
1661 END;
1662 --
1663 END LOOP;
1664 --
1665 END IF;
1666
1667 hr_utility.set_location ('Default values assigned ', 5);
1668 hr_utility.set_location ( l_icd_chc_rates_tab.FIRST
1669 || 'f counts'
1670 || l_icd_chc_rates_tab.LAST,
1671 121
1672 );
1673
1674 --
1675 --
1676 IF l_abr.input_va_calc_rl IS NOT NULL
1677 AND l_icd_chc_rates_tab.COUNT > 0
1678 AND l_outputs.COUNT > 0
1679 THEN
1680 --
1681 l_counter := 0;
1682
1683 --
1684 /* IF NOT l_icd_chc_rates_tab.EXISTS(1) THEN
1685 --
1686 l_cnt_links := 1;
1687 --
1688 ELSE
1689 --
1690 l_cnt_links := l_icd_chc_rates_tab.LAST + 1;
1691 --
1692 END IF;*/
1693
1694 -- putting all the values returned by forumula in the table that will be used to insert values in ben_icd_chc_rates
1695 FOR l_cnt IN l_icd_chc_rates_tab.FIRST .. l_icd_chc_rates_tab.LAST
1696 -- change default values to formula returned value for every link
1697 LOOP
1698 hr_utility.set_location (' in loop ', 121);
1699
1700 -- FOR l_cnt IN l_icd_chc_rates_tab.FIRST .. l_icd_chc_rates_tab.LAST
1701 FOR l_count IN l_outputs.FIRST .. l_outputs.LAST
1702 LOOP
1703 --
1704 BEGIN
1705 --
1706 OPEN c_ext_inpval (p_acty_base_rt_id);
1707
1708 LOOP
1709 FETCH c_ext_inpval
1710 INTO l_ext_inpval_rec;
1711
1712 EXIT WHEN c_ext_inpval%NOTFOUND;
1713 --
1714 hr_utility.set_location
1715 ( ' l_outputs (l_count).NAME '
1716 || l_outputs (l_count).NAME,
1717 121
1718 );
1719 hr_utility.set_location
1720 ( ' l_ext_inpval_rec.return_var_name '
1721 || l_ext_inpval_rec.return_var_name,
1722 121
1723 );
1724
1725 IF l_outputs (l_count).NAME =
1726 l_ext_inpval_rec.return_var_name
1727 THEN
1728 --
1729 l_counter := l_counter + 1;
1730 hr_utility.set_location
1731 ('Before assign extra inputs cache',
1732 11
1733 );
1734 --
1735 hr_utility.set_location
1736 ( 'l_ext_inpval_rec.extra_input_value_id'
1737 || l_ext_inpval_rec.extra_input_value_id,
1738 132
1739 );
1740 --
1741 hr_utility.set_location('l_cnt_links'||l_cnt_links,1231);
1742 l_input_value_id :=
1743 get_input_value_id
1744 (l_ext_inpval_rec.extra_input_value_id);
1745
1746 --
1747 /* IF l_outputs (l_count).VALUE IS NOT NULL
1748 THEN
1749 --
1750 IF l_icd_chc_rates_tab (l_cnt).input_value_id1 =
1751 l_input_value_id
1752 THEN
1753 l_icd_chc_rates_tab (l_cnt).default_value1 :=
1754 l_outputs (l_count).VALUE;
1755 ELSIF l_icd_chc_rates_tab (l_cnt).input_value_id2 =
1756 l_input_value_id
1757 THEN
1758 l_icd_chc_rates_tab (l_cnt).default_value2 :=
1759 l_outputs (l_count).VALUE;
1760 ELSIF l_icd_chc_rates_tab (l_cnt).input_value_id3 =
1761 l_input_value_id
1762 THEN
1763 l_icd_chc_rates_tab (l_cnt).default_value3 :=
1764 l_outputs (l_count).VALUE;
1765 ELSIF l_icd_chc_rates_tab (l_cnt).input_value_id4 =
1766 l_input_value_id
1767 THEN
1768 l_icd_chc_rates_tab (l_cnt).default_value4 :=
1769 l_outputs (l_count).VALUE;
1770 ELSIF l_icd_chc_rates_tab (l_cnt).input_value_id5 =
1771 l_input_value_id
1772 THEN
1773 l_icd_chc_rates_tab (l_cnt).default_value5 :=
1774 l_outputs (l_count).VALUE;
1775 ELSIF l_icd_chc_rates_tab (l_cnt).input_value_id6 =
1776 l_input_value_id
1777 THEN
1778 l_icd_chc_rates_tab (l_cnt).default_value6 :=
1779 l_outputs (l_count).VALUE;
1780 ELSIF l_icd_chc_rates_tab (l_cnt).input_value_id7 =
1781 l_input_value_id
1782 THEN
1783 l_icd_chc_rates_tab (l_cnt).default_value7 :=
1784 l_outputs (l_count).VALUE;
1785 ELSIF l_icd_chc_rates_tab (l_cnt).input_value_id8 =
1786 l_input_value_id
1787 THEN
1788 l_icd_chc_rates_tab (l_cnt).default_value8 :=
1789 l_outputs (l_count).VALUE;
1790 ELSIF l_icd_chc_rates_tab (l_cnt).input_value_id9 =
1791 l_input_value_id
1792 THEN
1793 l_icd_chc_rates_tab (l_cnt).default_value9 :=
1794 l_outputs (l_count).VALUE;
1795 ELSIF l_icd_chc_rates_tab (l_cnt).input_value_id10 =
1796 l_input_value_id
1797 THEN
1798 l_icd_chc_rates_tab (l_cnt).default_value10 :=
1799 l_outputs (l_count).VALUE;
1800 ELSIF l_icd_chc_rates_tab (l_cnt).input_value_id11 =
1801 l_input_value_id
1802 THEN
1803 l_icd_chc_rates_tab (l_cnt).default_value11 :=
1804 l_outputs (l_count).VALUE;
1805 ELSIF l_icd_chc_rates_tab (l_cnt).input_value_id12 =
1806 l_input_value_id
1807 THEN
1808 l_icd_chc_rates_tab (l_cnt).default_value12 :=
1809 l_outputs (l_count).VALUE;
1810 ELSIF l_icd_chc_rates_tab (l_cnt).input_value_id13 =
1811 l_input_value_id
1812 THEN
1813 l_icd_chc_rates_tab (l_cnt).default_value13 :=
1814 l_outputs (l_count).VALUE;
1815 ELSIF l_icd_chc_rates_tab (l_cnt).input_value_id14 =
1816 l_input_value_id
1817 THEN
1818 l_icd_chc_rates_tab (l_cnt).default_value14 :=
1819 l_outputs (l_count).VALUE;
1820 ELSIF l_icd_chc_rates_tab (l_cnt).input_value_id15 =
1821 l_input_value_id
1822 THEN
1823 l_icd_chc_rates_tab (l_cnt).default_value15 :=
1824 l_outputs (l_count).VALUE;
1825 END IF;*/
1826 --
1827
1828 -- my change
1829 IF l_outputs (l_count).VALUE IS NOT NULL
1830 THEN
1831 --
1832 IF l_icd_chc_rates_tab (l_cnt_links).input_value_id1 =
1833 l_input_value_id
1834 THEN
1835 l_icd_chc_rates_tab (l_cnt_links).input_value1 :=
1836 l_outputs (l_count).VALUE;
1837 ELSIF l_icd_chc_rates_tab (l_cnt_links).input_value_id2 =
1838 l_input_value_id
1839 THEN
1840 l_icd_chc_rates_tab (l_cnt_links).input_value2 :=
1841 l_outputs (l_count).VALUE;
1842 ELSIF l_icd_chc_rates_tab (l_cnt_links).input_value_id3 =
1843 l_input_value_id
1844 THEN
1845 l_icd_chc_rates_tab (l_cnt_links).input_value3 :=
1846 l_outputs (l_count).VALUE;
1847 ELSIF l_icd_chc_rates_tab (l_cnt_links).input_value_id4 =
1848 l_input_value_id
1849 THEN
1850 l_icd_chc_rates_tab (l_cnt_links).input_value4 :=
1851 l_outputs (l_count).VALUE;
1852 ELSIF l_icd_chc_rates_tab (l_cnt_links).input_value_id5 =
1853 l_input_value_id
1854 THEN
1855 l_icd_chc_rates_tab (l_cnt_links).input_value5 :=
1856 l_outputs (l_count).VALUE;
1857 ELSIF l_icd_chc_rates_tab (l_cnt_links).input_value_id6 =
1858 l_input_value_id
1859 THEN
1860 l_icd_chc_rates_tab (l_cnt_links).input_value6 :=
1861 l_outputs (l_count).VALUE;
1862 ELSIF l_icd_chc_rates_tab (l_cnt_links).input_value_id7 =
1863 l_input_value_id
1864 THEN
1865 l_icd_chc_rates_tab (l_cnt_links).input_value7 :=
1866 l_outputs (l_count).VALUE;
1867 ELSIF l_icd_chc_rates_tab (l_cnt_links).input_value_id8 =
1868 l_input_value_id
1869 THEN
1870 l_icd_chc_rates_tab (l_cnt_links).input_value8 :=
1871 l_outputs (l_count).VALUE;
1872 ELSIF l_icd_chc_rates_tab (l_cnt_links).input_value_id9 =
1873 l_input_value_id
1874 THEN
1875 l_icd_chc_rates_tab (l_cnt_links).input_value9 :=
1876 l_outputs (l_count).VALUE;
1877 ELSIF l_icd_chc_rates_tab (l_cnt_links).input_value_id10 =
1878 l_input_value_id
1879 THEN
1880 l_icd_chc_rates_tab (l_cnt_links).input_value10 :=
1881 l_outputs (l_count).VALUE;
1882 ELSIF l_icd_chc_rates_tab (l_cnt_links).input_value_id11 =
1883 l_input_value_id
1884 THEN
1885 l_icd_chc_rates_tab (l_cnt).input_value11 :=
1886 l_outputs (l_count).VALUE;
1887 ELSIF l_icd_chc_rates_tab (l_cnt).input_value_id12 =
1888 l_input_value_id
1889 THEN
1890 l_icd_chc_rates_tab (l_cnt_links).input_value12 :=
1891 l_outputs (l_count).VALUE;
1892 ELSIF l_icd_chc_rates_tab (l_cnt_links).input_value_id13 =
1893 l_input_value_id
1894 THEN
1895 l_icd_chc_rates_tab (l_cnt_links).input_value13 :=
1896 l_outputs (l_count).VALUE;
1897 ELSIF l_icd_chc_rates_tab (l_cnt_links).input_value_id14 =
1898 l_input_value_id
1899 THEN
1900 l_icd_chc_rates_tab (l_cnt_links).input_value14 :=
1901 l_outputs (l_count).VALUE;
1902 ELSIF l_icd_chc_rates_tab (l_cnt_links).input_value_id15 =
1903 l_input_value_id
1904 THEN
1905 l_icd_chc_rates_tab (l_cnt_links).input_value15 :=
1906 l_outputs (l_count).VALUE;
1907 END IF;
1908 --my change
1909 END IF;
1910 --
1911 END IF;
1912
1913 --
1914 END LOOP;
1915
1916 --
1917 CLOSE c_ext_inpval;
1918 --
1919 END;
1920 END LOOP;
1921 END LOOP;
1922 END IF;
1923 --
1924 END IF; -- ICM;
1925
1926
1927 --
1928 -- Enhancement No 3981982 - Min Max Rule
1929 --
1930 if (l_abr.mn_mx_elcn_rl IS NOT NULL) then
1931 l_outputs := benutils.formula( p_formula_id => l_abr.mn_mx_elcn_rl,
1932 p_effective_date => NVL(p_lf_evt_ocrd_dt, l_effective_date),
1933 p_assignment_id => l_assignment_id,
1934 p_organization_id => l_organization_id,
1935 p_pgm_id => l_epe.pgm_id,
1936 p_pl_id => l_epe.pl_id,
1937 p_pl_typ_id => l_epe.pl_typ_id,
1938 p_opt_id => l_opt.opt_id,
1939 p_jurisdiction_code => l_jurisdiction_code,
1940 p_ler_id => l_epe.ler_id,
1941 p_business_group_id => l_epe.business_group_id,
1942 p_param1 => 'BEN_IV_PERSON_ID',
1943 p_param1_value => to_char(p_person_id)
1944 );
1945 hr_utility.set_location('l_outputs.count'||l_outputs.count,951);
1946 if (l_outputs.count >= 4) then
1947 for l_count in l_outputs.first..l_outputs.last loop
1948 begin
1949 if l_outputs(l_count).name = 'L_MN_ELCN_VAL' then
1950 l_abr.mn_elcn_val := fnd_number.canonical_to_number(l_outputs(l_count).value);
1951 elsif l_outputs(l_count).name = 'L_MX_ELCN_VAL' then
1952 l_abr.mx_elcn_val := fnd_number.canonical_to_number(l_outputs(l_count).value);
1953 elsif l_outputs(l_count).name = 'L_INCRMT_VAL' then
1954 l_abr.incrmt_elcn_val := fnd_number.canonical_to_number(l_outputs(l_count).value);
1955 elsif l_outputs(l_count).name = 'L_DFLT_VAL' then
1956 l_abr.dflt_val := fnd_number.canonical_to_number(l_outputs(l_count).value);
1957 end if;
1958 exception
1959 when others then
1960 fnd_message.set_name('BEN','BEN_92311_FORMULA_VAL_PARAM');
1961 fnd_message.set_token('PROC',l_package);
1962 fnd_message.set_token('FORMULA',l_abr.mn_mx_elcn_rl);
1963 fnd_message.set_token('PARAMETER',l_outputs(l_count).name);
1964 fnd_message.raise_error;
1965 end;
1966 end loop;
1967 else
1968 fnd_message.set_name('BEN','BEN_94130_MN_MX_RL_OUT_ERR');
1969 fnd_message.raise_error;
1970 end if;
1971 --hr_utility.set_location('mn_elcn_val is '||l_abr.mn_elcn_val,951);
1972 --hr_utility.set_location('mx_elcn_val is '||l_abr.mx_elcn_val,951);
1973 --hr_utility.set_location('incrmt_elcn_val is '||l_abr.incrmt_elcn_val,951);
1974 --hr_utility.set_location('dflt_val is '||l_abr.dflt_val,951);
1975 end if;
1976 --
1977
1978 -- end bug fix 3457483
1979 /* -- 4031733 - Cursor used to populate l_state.region_2 param for benutils.limit_checks
1980 -- which is not used down the line
1981 --
1982 open c_state;
1983 fetch c_state into l_state;
1984 close c_state;
1985 */
1986 hr_utility.set_location (' Checking rate multiplier code ',40);
1987 if l_abr.rt_mlt_cd is null then
1988 fnd_message.set_name('BEN','BEN_91834_BASE_RATE_COLUMN_ERR');
1989 fnd_message.set_token('COLUMN','rt_mlt_cd');
1990 fnd_message.set_token('PACKAGE',l_package);
1991 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1992 fnd_message.set_token('ACTY_BASE_RT_ID',to_char(p_acty_base_rt_id));
1993 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
1994 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
1995 fnd_message.set_token('PL_ID',to_char(p_pl_id));
1996 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
1997 fnd_message.raise_error;
1998 end if;
1999 hr_utility.set_location (' Checking rate multiplier code others',50);
2000 if l_abr.rt_mlt_cd in ('FLFX','CL','AP','CVG','PRNT','CLANDCVG',
2001 'APANDCVG','PRNTANDCVG') then
2002 if l_abr.val is null and l_abr.entr_val_at_enrt_flag = 'N' then
2003 --
2004 fnd_message.set_name('BEN','BEN_91834_BASE_RATE_COLUMN_ERR');
2005 fnd_message.set_token('COLUMN','val');
2006 fnd_message.set_token('PACKAGE',l_package);
2007 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
2008 fnd_message.set_token('ACTY_BASE_RT_ID',to_char(p_acty_base_rt_id));
2009 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
2010 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
2011 fnd_message.set_token('PL_ID',to_char(p_pl_id));
2012 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
2013 fnd_message.raise_error;
2014 end if;
2015 end if;
2016 hr_utility.set_location (' Checking acty base rate values ',60);
2017 --
2018 -- cwb now allows null values for min/max/incrmt so no need to validate
2019 --
2020 ben_env_object.get(p_rec => l_env);
2021 if (l_abr.entr_val_at_enrt_flag = 'Y' and l_abr.entr_ann_val_flag = 'N')
2022 and nvl(l_env.mode_cd,'~') <> 'W' then
2023
2024 if l_abr.mn_elcn_val is null then
2025 hr_utility.set_location (' BEN_91834_BASE_RATE_COLUMN_ERR ',61);
2026 fnd_message.set_name('BEN','BEN_91834_BASE_RATE_COLUMN_ERR');
2027 fnd_message.set_token('COLUMN','mn_elcn_val');
2028 fnd_message.set_token('PACKAGE',l_package);
2029 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
2030 fnd_message.set_token('ACTY_BASE_RT_ID',to_char(p_acty_base_rt_id));
2031 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
2032 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
2033 fnd_message.set_token('PL_ID',to_char(p_pl_id));
2034 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
2035 fnd_message.raise_error;
2036
2037 elsif l_abr.mx_elcn_val is null then
2038 hr_utility.set_location (' BEN_91834_BASE_RATE_COLUMN_ERR ',62);
2039 fnd_message.set_name('BEN','BEN_91834_BASE_RATE_COLUMN_ERR');
2040 fnd_message.set_token('COLUMN','mx_elcn_val');
2041 fnd_message.set_token('PACKAGE',l_package);
2042 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
2043 fnd_message.set_token('ACTY_BASE_RT_ID',to_char(p_acty_base_rt_id));
2044 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
2045 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
2046 fnd_message.set_token('PL_ID',to_char(p_pl_id));
2047 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
2048 fnd_message.raise_error;
2049 elsif l_abr.incrmt_elcn_val is null then
2050 hr_utility.set_location (' BEN_91834_BASE_RATE_COLUMN_ERR ',63);
2051 fnd_message.set_name('BEN','BEN_91834_BASE_RATE_COLUMN_ERR');
2052 fnd_message.set_token('COLUMN','incrmt_elcn_val');
2053 fnd_message.set_token('PACKAGE',l_package);
2054 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
2055 fnd_message.set_token('ACTY_BASE_RT_ID',to_char(p_acty_base_rt_id));
2056 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
2057 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
2058 fnd_message.set_token('PL_ID',to_char(p_pl_id));
2059 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
2060 fnd_message.raise_error;
2061 end if;
2062 end if;
2063
2064 hr_utility.set_location(' checking if program or plan ',70);
2065 hr_utility.set_location ('BRRRRRP '||l_package||to_char(l_coverage_value),333);
2066
2067 if l_epe.pgm_id is not null then
2068 open c_pgm
2069 (c_effective_date => l_effective_date
2070 );
2071 fetch c_pgm into l_pgm;
2072 if c_pgm%notfound then
2073 close c_pgm;
2074 fnd_message.set_name('BEN','BEN_92410_BENACTBR_PGM_NF');
2075 fnd_message.set_token('ID',to_char(l_epe.pgm_id));
2076 fnd_message.set_token('PACKAGE',l_package);
2077 fnd_message.raise_error;
2078 end if;
2079 close c_pgm;
2080 l_acty_ref_perd_cd := l_pgm.acty_ref_perd_cd;
2081 l_enrt_info_rt_freq_cd := l_pgm.enrt_info_rt_freq_cd;
2082 else
2083 hr_utility.set_location(' testing if plan ',80);
2084 hr_utility.set_location(' l_epe.pl_id'||l_epe.pl_id,80);
2085 open c_pln
2086 (c_effective_date => l_effective_date,
2087 c_pl_id => l_epe.pl_id
2088 );
2089 fetch c_pln into l_pln;
2090 if c_pln%notfound then
2091 close c_pln;
2092 fnd_message.set_name('BEN','BEN_92411_BENACTBR_PLN_NF');
2093 fnd_message.set_token('ID',to_char(l_epe.pl_id));
2094 fnd_message.set_token('PACKAGE',l_package);
2095 fnd_message.raise_error;
2096 end if;
2097 close c_pln;
2098 l_acty_ref_perd_cd := l_pln.nip_acty_ref_perd_cd;
2099 l_enrt_info_rt_freq_cd := l_pln.nip_enrt_info_rt_freq_cd;
2100 end if;
2101 --
2102 -- Bug No 4538786
2103 --
2104 /* hr_utility.set_location('PPF changes: input epe id'||p_elig_per_elctbl_chc_id,99);
2105 hr_utility.set_location('PPF changes: element type id before changes'||l_abr.element_type_id,99); */
2106 if l_abr.rt_usg_cd = 'FLXCR' and l_enrt_info_rt_freq_cd = 'PPF' then
2107 -- hr_utility.set_location('PPF changes: fetching the element type id...',99);
2108 open get_rt_and_element(l_epe.pgm_id, l_epe.per_in_ler_id);
2109 fetch get_rt_and_element into l_rt_and_element;
2110 if get_rt_and_element%FOUND then
2111 l_abr.element_type_id := l_rt_and_element.element_type_id;
2112 end if;
2113 Close get_rt_and_element; -- Close added during bug fix 4604560
2114 end if;
2115 -- hr_utility.set_location('PPF changes: afterwards element type id'||l_abr.element_type_id,99);
2116 --
2117 -- End Bug No 4538786
2118 --
2119 -- get the dflt_enrt_cd/rule
2120 --
2121 hr_utility.set_location ('BER_DDEC '||l_package,10);
2122 ben_env_object.get(p_rec => l_env);
2123 hr_utility.set_location('l_env.mode_cd' || l_env.mode_cd,13);
2124 if nvl(l_env.mode_cd,'~') <> 'D' then --cant set this code in 'D' Plans -- ICM
2125 --
2126 ben_enrolment_requirements.determine_dflt_enrt_cd
2127 (p_oipl_id =>l_epe.oipl_id
2128 ,p_plip_id =>l_epe.plip_id
2129 ,p_pl_id =>l_epe.pl_id
2130 ,p_ptip_id =>l_epe.ptip_id
2131 ,p_pgm_id =>l_epe.pgm_id
2132 ,p_ler_id =>l_epe.ler_id
2133 ,p_dflt_enrt_cd =>l_dflt_enrt_cd
2134 ,p_dflt_enrt_rl =>l_dflt_enrt_rl
2135 ,p_business_group_id =>l_epe.business_group_id
2136 ,p_effective_date =>l_cvg_eff_dt
2137 );
2138 hr_utility.set_location ('BRRRRRP '||l_package||to_char(l_coverage_value),299);
2139 --
2140 end if;
2141 if l_abr.rt_mlt_cd in ('CVG','CLANDCVG','APANDCVG','PRNTANDCVG','SAREC') then
2142 --
2143 --Bug 2192102 if the rate is of Flex credit letus get the associated enrt bnft id
2144 if l_abr.rt_usg_cd = 'FLXCR' then
2145 --
2146 hr_utility.set_location (' Before FLXCR l_enrt_bnft_id '||l_enrt_bnft_id, 19);
2147 --
2148 open c_enb_fc ;
2149 fetch c_enb_fc into l_enrt_bnft_id ;
2150 close c_enb_fc;
2151 --
2152 hr_utility.set_location (' After FLXCR l_enrt_bnft_id '||l_enrt_bnft_id, 19);
2153 end if;
2154 --
2155 if l_coverage_value is null then
2156 if l_enrt_bnft_id is null then
2157 fnd_message.set_name('BEN','BEN_92748_ABR_ENBID_NULL');
2158 fnd_message.set_token('PACKAGE',l_package);
2159 fnd_message.set_token('PARAM','p_enrt_bnft_id');
2160 fnd_message.raise_error;
2161 end if;
2162 open c_enb(l_enrt_bnft_id);
2163 fetch c_enb into l_val, l_dflt_val;
2164 if c_enb%notfound then
2165 close c_enb;
2166 fnd_message.set_name('BEN','BEN_92739_NO_ABR_ENB_EXISTS');
2167 fnd_message.raise_error;
2168 end if;
2169 close c_enb;
2170 --
2171 l_coverage_value := nvl(l_val, l_dflt_val);
2172 --
2173 end if;
2174 if l_epe.oipl_id is not NULL then
2175 open c_cvg_oipl
2176 (c_effective_date => l_effective_date
2177 );
2178 fetch c_cvg_oipl into l_cvg_calc_amt_mthd_id;
2179 if c_cvg_oipl%notfound then
2180 close c_cvg_oipl;
2181 fnd_message.set_name('BEN','BEN_92740_NO_OIPL_CCM_ATTACH');
2182 fnd_message.raise_error;
2183 end if;
2184 close c_cvg_oipl;
2185 elsif l_epe.pl_id is not NULL then
2186 open c_cvg_pl
2187 (c_effective_date => l_effective_date
2188 );
2189 fetch c_cvg_pl into l_cvg_calc_amt_mthd_id;
2190 if c_cvg_pl%notfound then
2191 close c_cvg_pl;
2192 fnd_message.set_name('BEN','BEN_92741_NO_PLN_CCM_ATTACH');
2193 fnd_message.raise_error;
2194 end if;
2195 close c_cvg_pl;
2196 else
2197 fnd_message.set_name('BEN','BEN_92744_ABR_ENB_NULL');
2198 fnd_message.raise_error;
2199 end if;
2200 p_cvg_calc_amt_mthd_id := l_cvg_calc_amt_mthd_id;
2201 end if;
2202 hr_utility.set_location ('ABR_RTMLTCD CL'||l_package,10);
2203 if l_abr.rt_mlt_cd in ('CL','CLANDCVG') then
2204 if l_abr.comp_lvl_fctr_id is null then
2205 fnd_message.set_name('BEN','BEN_92745_NO_ABR_CLF_EXISTS');
2206 fnd_message.raise_error;
2207 end if;
2208 p_comp_lvl_fctr_id := l_abr.comp_lvl_fctr_id;
2209 ben_derive_factors.determine_compensation
2210 (p_comp_lvl_fctr_id => l_abr.comp_lvl_fctr_id,
2211 p_person_id => p_person_id,
2212 p_pgm_id => l_epe.pgm_id,
2213 p_pl_id => l_epe.pl_id,
2214 p_oipl_id => l_epe.oipl_id,
2215 p_per_in_ler_id => l_epe.per_in_ler_id,
2216 p_business_group_id => l_epe.business_group_id,
2217 p_perform_rounding_flg => true,
2218 p_effective_date => p_effective_date,
2219 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2220 p_cal_for => 'R',
2221 p_value => l_compensation_value,
2222 p_fonm_cvg_strt_dt => l_cvg_eff_dt,
2223 p_fonm_rt_strt_dt => l_fonm_rt_strt_dt );
2224
2225
2226 if l_compensation_value is null then
2227 fnd_message.set_name('BEN','BEN_92746_ABR_COMP_NULL');
2228 fnd_message.set_token('PACKAGE',l_package);
2229 fnd_message.set_token('VARIABLE','l_compensation_value');
2230 fnd_message.raise_error;
2231 end if;
2232 end if;
2233 hr_utility.set_location ('ABR_RTMLTCD AP '||l_package,10);
2234 if l_abr.rt_mlt_cd in ('AP','APANDCVG') then
2235 p_actl_prem_id := l_abr.actl_prem_id;
2236 open c_enrt_prem(l_abr.actl_prem_id);
2237 fetch c_enrt_prem into l_actl_prem_value;
2238 close c_enrt_prem;
2239 hr_utility.set_location (' Dn c_enrt_prem ',120);
2240 --6314463
2241 l_force_prem_calc := 'N';
2242 open c_force_prem_calc(p_actl_prem_id);
2243 fetch c_force_prem_calc into l_force_prem_calc;
2244 close c_force_prem_calc;
2245 --- if premium is null it will calcualte again 2797031
2246 -- 5557305 : Recalculate 'Actual Premium' irrespective of current value
2247 -- as value might have changed, Hence commented the below condition
2248 if nvl(l_env.mode_cd,'~') <> 'D' then
2249 --
2250 if /*nvl(l_actl_prem_value,0) = 0 and */ p_calc_only_rt_val_flag
2251 or (l_force_prem_calc = 'Y' and p_called_from_ss) then
2252 hr_utility.set_location('premium re calculation ',551);
2253 ben_determine_actual_premium.g_computed_prem_val := null ;
2254 ben_determine_actual_premium.main
2255 (p_person_id => p_person_id,
2256 p_effective_date => l_cvg_eff_dt,
2257 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2258 p_calc_only_rt_val_flag => true , --6314463 As we have already decided to calculate premium for the rate
2259 --let's not allow this flag to impact the premium computation
2260 p_pgm_id => l_epe.pgm_id,
2261 p_pl_id => l_epe.pl_id,
2262 p_pl_typ_id => l_epe.pl_typ_id,
2263 p_oipl_id => l_epe.oipl_id,
2264 p_per_in_ler_id => l_epe.per_in_ler_id,
2265 p_ler_id => l_epe.ler_id,
2266 p_bnft_amt => l_coverage_value,
2267 p_business_group_id => l_epe.business_group_id );
2268
2269 hr_utility.set_location('premium re calculation over ',551);
2270 hr_utility.set_location('Required actl_prem_id ' || l_abr.actl_prem_id,551);
2271 --
2272 -- 6330056 : Match the actl_prem_id for the evaluated
2273 -- premiums to get the correct required value.
2274 --
2275 if (ben_determine_actual_premium.g_computed_prem_tbl.COUNT > 0) then
2276 --
2277 for idx IN ben_determine_actual_premium.g_computed_prem_tbl.FIRST..
2278 ben_determine_actual_premium.g_computed_prem_tbl.LAST loop
2279 --
2280 hr_utility.set_location('Calculated actl_prem_id ' ||
2281 ben_determine_actual_premium.g_computed_prem_tbl(idx).actl_prem_id,551);
2282 if (ben_determine_actual_premium.g_computed_prem_tbl(idx).actl_prem_id
2283 = l_abr.actl_prem_id) then
2284 --
2285 l_actl_prem_value :=
2286 ben_determine_actual_premium.g_computed_prem_tbl(idx).val;
2287 --
2288 end if;
2289 --
2290 end loop;
2291 --
2292 end if;
2293 --
2294 hr_utility.set_location('l_actl_prem_value ' || l_actl_prem_value,100);
2295 --l_actl_prem_value := ben_determine_actual_premium.g_computed_prem_val;
2296 hr_utility.set_location('re calculation premium'||l_actl_prem_value,551);
2297 ben_determine_actual_premium.g_computed_prem_val := null;
2298 --
2299 end if;
2300
2301 if l_actl_prem_value is null then
2302 fnd_message.set_name('BEN','BEN_92747_ABR_APR_NULL');
2303 hr_utility.set_location (' FNDMS_RE 1 ',130);
2304 fnd_message.raise_error;
2305 end if;
2306 hr_utility.set_location (' Convert prem rate (monthly) to acty_ref_perd',140);
2307 hr_utility.set_location (' l_acty_ref_perd_cd -> '||l_acty_ref_perd_cd,140);
2308 --
2309
2310 if l_acty_ref_perd_cd = 'PWK' then
2311 l_actl_prem_value := (l_actl_prem_value * 12) / 52;
2312 elsif l_acty_ref_perd_cd = 'BWK' then
2313 l_actl_prem_value := (l_actl_prem_value * 12) / 26;
2314 elsif l_acty_ref_perd_cd = 'MO' then
2315 null; -- premiums are always monthly, so nothing to do
2316 elsif l_acty_ref_perd_cd = 'SMO' then
2317 l_actl_prem_value := (l_actl_prem_value * 12) / 6;
2318 elsif l_acty_ref_perd_cd = 'PQU' then
2319 l_actl_prem_value := (l_actl_prem_value * 12) / 4;
2320 elsif l_acty_ref_perd_cd = 'SAN' then
2321 l_actl_prem_value := (l_actl_prem_value * 12) / 2;
2322 elsif l_acty_ref_perd_cd = 'PYR' then
2323 l_actl_prem_value := l_actl_prem_value * 12;
2324 elsif l_acty_ref_perd_cd = 'PHR' then
2325 --
2326 l_pay_annualization_factor := to_number(fnd_profile.value('BEN_HRLY_ANAL_FCTR'));
2327 if l_pay_annualization_factor is null then
2328 l_pay_annualization_factor := 2080;
2329 end if;
2330 --
2331 l_actl_prem_value := l_actl_prem_value * 12 / l_pay_annualization_factor;
2332 --
2333 else
2334 fnd_message.set_name('BEN','BEN_92412_UKN_ACTY_REF_PERD');
2335 fnd_message.set_token('PROC',l_package);
2336 fnd_message.set_token('VARIABLE',l_acty_ref_perd_cd);
2337 hr_utility.set_location (' FNDMS_RE 2 ',150);
2338 fnd_message.raise_error;
2339 end if;
2340 end if; --'D'
2341 end if;
2342 hr_utility.set_location ('ABR_RTMLTCD PRNT '||l_package,10);
2343 if l_abr.rt_mlt_cd in ('PRNT','PRNTANDCVG') then
2344 open c_abr2
2345 (c_effective_date => l_effective_date
2346 );
2347 fetch c_abr2 into l_acty_base_rt_id ,l_prnt_entr_val_at_enrt_flag;
2348 if c_abr2%notfound then
2349 close c_abr2;
2350 fnd_message.set_name('BEN','BEN_92742_NO_PRNT_ABR_EXISTS');
2351 fnd_message.raise_error;
2352 end if;
2353 close c_abr2;
2354 /*
2355 --
2356 -- MH - removed this could never occur because BEN_92742_NO_PRNT_ABR_EXISTS
2357 -- would be raised
2358 --
2359 if l_acty_base_rt_id is null then
2360 fnd_message.set_name('BEN','BEN_91835_VARIABLE_VALUE_NULL');
2361 fnd_message.set_token('PACKAGE',l_package);
2362 fnd_message.set_token('VARIABLE','l_acty_base_rt_id');
2363 fnd_message.raise_error;
2364 end if;
2365 */
2366 hr_utility.set_location ('St BDABR_MN Sub '||l_package,10);
2367 if l_prnt_entr_val_at_enrt_flag = 'Y' then
2368 l_cal_val := null;
2369 else
2370 l_cal_val := p_cal_val;
2371 end if;
2372 if p_parent_val is not null then
2373 l_prnt_rt_value := p_parent_val ;
2374 else
2375 ben_determine_activity_base_rt.main
2376 (p_person_id => p_person_id
2377 ,p_per_row => p_per_row
2378 ,p_asg_row => p_asg_row
2379 ,p_ast_row => p_ast_row
2380 ,p_adr_row => p_adr_row
2381 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
2382 ,p_enrt_bnft_id => l_enrt_bnft_id
2383 ,p_acty_base_rt_id => l_acty_base_rt_id
2384 ,p_effective_date => p_effective_date
2385 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
2386 ,p_perform_rounding_flg => true
2387 ,p_calc_only_rt_val_flag => p_calc_only_rt_val_flag
2388 ,p_pl_id => l_epe.pl_id
2389 ,p_pgm_id => l_epe.pgm_id
2390 ,p_oipl_id => l_epe.oipl_id
2391 ,p_pl_typ_id => l_epe.pl_typ_id
2392 ,p_per_in_ler_id => l_epe.per_in_ler_id
2393 ,p_ler_id => l_epe.ler_id
2394 ,p_bnft_amt => l_coverage_value
2395 ,p_business_group_id => l_epe.business_group_id
2396 ,p_cal_val => l_cal_val/* to handle net credit method for flex credit */
2397 ,p_val => l_prnt_rt_value
2398 ,p_mn_elcn_val => l_dummy_num
2399 ,p_mx_elcn_val => l_dummy_num
2400 ,p_ann_val => l_dummy_num
2401 ,p_ann_mn_elcn_val => l_dummy_num
2402 ,p_ann_mx_elcn_val => l_dummy_num
2403 ,p_dsply_mn_elcn_val => l_dummy_num
2404 ,p_dsply_mx_elcn_val => l_dummy_num
2405 ,p_cmcd_val => l_dummy_num
2406 ,p_cmcd_mn_elcn_val => l_dummy_num
2407 ,p_cmcd_mx_elcn_val => l_dummy_num
2408 ,p_cmcd_acty_ref_perd_cd => l_dummy_char
2409 ,p_incrmt_elcn_val => l_dummy_num
2410 ,p_dflt_val => l_dummy_num
2411 ,p_tx_typ_cd => l_dummy_char
2412 ,p_acty_typ_cd => l_dummy_char
2413 ,p_nnmntry_uom => l_dummy_char
2414 ,p_entr_val_at_enrt_flag => l_dummy_char
2415 ,p_dsply_on_enrt_flag => l_dummy_char
2416 ,p_use_to_calc_net_flx_cr_flag => l_dummy_char
2417 ,p_rt_usg_cd => l_dummy_char
2418 ,p_bnft_prvdr_pool_id => l_dummy_num
2419 ,p_actl_prem_id => l_dummy_num
2420 ,p_cvg_calc_amt_mthd_id => l_dummy_num
2421 ,p_bnft_rt_typ_cd => l_dummy_char
2422 ,p_rt_typ_cd => l_dummy_char
2423 ,p_rt_mlt_cd => l_dummy_char
2424 ,p_comp_lvl_fctr_id => l_dummy_num
2425 ,p_entr_ann_val_flag => l_dummy_char
2426 ,p_ptd_comp_lvl_fctr_id => l_dummy_num
2427 ,p_clm_comp_lvl_fctr_id => l_dummy_num
2428 ,p_ann_dflt_val => l_dummy_num
2429 ,p_rt_strt_dt => l_dummy_date
2430 ,p_rt_strt_dt_cd => l_dummy_char
2431 ,p_rt_strt_dt_rl => l_dummy_num
2432 ,p_prtt_rt_val_id => l_dummy_num
2433 ,p_pp_in_yr_used_num => l_dummy_num
2434 ,p_ordr_num => l_dummy_num
2435 ,p_iss_val => l_dummy_num
2436 );
2437 end if ;
2438 hr_utility.set_location ('Dn BDABR_MN Sub '||l_package,10);
2439 --bug :1555624 when the parent defiend as enter at enrollment then vlue
2440 -- may be returend as null or 0 this will be calcualtead in post enrollement
2441 -- process
2442 if l_prnt_rt_value is null
2443 and nvl(l_prnt_entr_val_at_enrt_flag,'N') <> 'Y'
2444 then
2445 fnd_message.set_name('BEN','BEN_91835_VARIABLE_VALUE_NULL');
2446 fnd_message.set_token('PACKAGE',l_package);
2447 fnd_message.set_token('VARIABLE','l_prnt_rt_value');
2448 fnd_message.raise_error;
2449 end if;
2450 --
2451 end if;
2452
2453 p_entr_val_at_enrt_flag := l_abr.entr_val_at_enrt_flag;
2454
2455 hr_utility.set_location(' l_abr.rt_mlt_cd:'||l_abr.rt_mlt_cd,170);
2456 hr_utility.set_location(' cal val :'|| p_cal_val,170);
2457 --- bug 1480407
2458 -- when entr_val_at_enrt_flag and use_calc_acty_bs_rt_flag is true this calculation will be called
2459 -- after enrolement with the entered rate so the calulatiuon is to be done for incomming value
2460 --- also validate the min max with the ammount entered , thi validation will be skipped in Rhi for
2461 --- calcualted amonut
2462 IF l_abr.entr_val_at_enrt_flag = 'Y' and l_abr.use_calc_acty_bs_rt_flag = 'Y' and
2463 p_cal_val is not null then
2464 --
2465 open c_enrt_rt(p_elig_per_elctbl_chc_id,p_enrt_bnft_id, p_acty_base_rt_id);
2466 fetch c_enrt_rt into l_enrt_rt;
2467 close c_enrt_rt;
2468 --
2469
2470 if ((nvl(l_enrt_rt.mn_elcn_val,l_abr.mn_elcn_val) is not NULL
2471 and p_cal_val < nvl(l_enrt_rt.mn_elcn_val,l_abr.mn_elcn_val))
2472 or (nvl(l_enrt_rt.mx_elcn_val,l_abr.mx_elcn_val) is not NULL
2473 and p_cal_val > nvl(l_enrt_rt.mx_elcn_val,l_abr.mx_elcn_val))) then
2474
2475 open c_pl_opt_name;
2476 fetch c_pl_opt_name into l_pl_opt_name;
2477 close c_pl_opt_name;
2478
2479 fnd_message.set_name('BEN','BEN_91939_NOT_IN_RANGE');
2480 fnd_message.set_token('MIN',nvl(l_enrt_rt.mn_elcn_val,l_abr.mn_elcn_val));
2481 fnd_message.set_token('MAX',nvl(l_enrt_rt.mx_elcn_val,l_abr.mx_elcn_val));
2482 fnd_message.set_token('PLOPT',l_pl_opt_name);
2483 fnd_message.raise_error;
2484
2485 elsif (mod(p_cal_val,nvl(l_enrt_rt.incrmt_elcn_val,l_abr.incrmt_elcn_val))
2486 <>0) then
2487 --
2488 -- raise error is not multiple of increment
2489 --
2490
2491 -- bug # 1699585 passing the plan name in the error message
2492 open c_pl_opt_name;
2493 fetch c_pl_opt_name into l_pl_opt_name;
2494 close c_pl_opt_name;
2495 -- end # 1699585
2496
2497 fnd_message.set_name('BEN','BEN_91932_NOT_INCREMENT');
2498 fnd_message.set_token('INCREMENT', nvl(l_enrt_rt.incrmt_elcn_val,l_abr.incrmt_elcn_val));
2499 fnd_message.set_token('PLAN', l_pl_opt_name);
2500 fnd_message.raise_error;
2501
2502 End if;
2503
2504
2505 l_abr.val := p_cal_val;
2506 hr_utility.set_location(' incomming value ' || l_abr.val , 407);
2507 end if ;
2508
2509 /* Bug 6015724 */
2510 l_prv_rt_strt_dt := null; -- 5748126
2511 l_prv_rt_end_dt := null;
2512 --
2513 -- Modified for performance.
2514 --
2515 -- When the passed in EPE prtt_enrt_rslt_id is set then do not perform
2516 -- SQL. Nullify PRV values
2517 --
2518 if p_currepe_row.elig_per_elctbl_chc_id is not null
2519 and p_currepe_row.prtt_enrt_rslt_id is null
2520 then
2521 --
2522 l_prtt_rt_val_id := null;
2523 l_rt_ovridn_flag := null;
2524 l_rt_ovridn_thru_dt := null;
2525 l_rt_val := null;
2526 --
2527 -- When the EPE prtt_enrt_rslt_id is set then perform performance PRV cursor
2528 --
2529 elsif l_epe.prtt_enrt_rslt_id is not null
2530 then
2531 --
2532 open c_perfprv
2533 (c_effective_date => l_effective_date
2534 ,c_acty_base_rt_id => p_acty_base_rt_id
2535 ,c_prtt_enrt_rslt_id => l_epe.prtt_enrt_rslt_id
2536 );
2537 loop -- 5748126: Added LOOP to fetch the Latest PRV which occurs before eff_dt
2538 fetch c_perfprv into l_prtt_rt_val_id,
2539 l_rt_ovridn_flag,
2540 l_rt_ovridn_thru_dt,
2541 l_rt_val,
2542 l_ann_rt_val,
2543 l_cmcd_rt_val,
2544 l_prv_rt_strt_dt,
2545 l_prv_rt_end_dt;
2546 --
2547 if (g_debug) then
2548 hr_utility.set_location(' l_prtt_rt_val_id: '|| l_prtt_rt_val_id
2549 ||':'|| l_prv_rt_strt_dt ||':'|| l_prv_rt_end_dt, 10);
2550 end if;
2551 --
2552 exit when (c_perfprv%notfound or l_prv_rt_strt_dt <= l_effective_date); -- 5898039: Changed from < to <=
2553 end loop;
2554 if l_prv_rt_strt_dt is null then
2555 --
2556 open c_perfprv_2
2557 (c_effective_date => l_effective_date
2558 ,c_acty_base_rt_id => p_acty_base_rt_id
2559 ,c_prtt_enrt_rslt_id => l_epe.prtt_enrt_rslt_id
2560 );
2561 loop -- 5748126: Added LOOP to fetch the Latest PRV which occurs before eff_dt
2562 fetch c_perfprv_2 into l_prtt_rt_val_id,
2563 l_rt_ovridn_flag,
2564 l_rt_ovridn_thru_dt,
2565 l_rt_val,
2566 l_ann_rt_val,
2567 l_cmcd_rt_val,
2568 l_prv_rt_strt_dt,
2569 l_prv_rt_end_dt;
2570 --
2571 if (g_debug) then
2572 hr_utility.set_location(' l_prtt_rt_val_id: '|| l_prtt_rt_val_id
2573 ||':'|| l_prv_rt_strt_dt ||':'|| l_prv_rt_end_dt, 10);
2574 end if;
2575 --
2576 exit when (c_perfprv_2%notfound or l_prv_rt_strt_dt <= l_effective_date); -- 5898039: Changed from < to <=
2577 end loop;
2578 close c_perfprv_2;
2579
2580 --
2581 end if;
2582
2583 close c_perfprv;
2584 --
2585 --
2586 -- This was left here to support for calls from outside of benmngle
2587 --
2588 else
2589 --
2590 open c_prv
2591 (c_effective_date => l_effective_date
2592 ,c_acty_base_rt_id => p_acty_base_rt_id
2593 ,c_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
2594 );
2595 loop -- 5748126: Added LOOP to fetch the Latest PRV which occurs before eff_dt
2596 fetch c_prv into l_prtt_rt_val_id,
2597 l_rt_ovridn_flag,
2598 l_rt_ovridn_thru_dt,
2599 l_rt_val,
2600 l_ann_rt_val,
2601 l_cmcd_rt_val,
2602 l_prv_rt_strt_dt,
2603 l_prv_rt_end_dt;
2604 --
2605 if (g_debug) then
2606 hr_utility.set_location(' l_prtt_rt_val_id: '|| l_prtt_rt_val_id
2607 ||':'|| l_prv_rt_strt_dt ||':'|| l_prv_rt_end_dt, 10);
2608 end if;
2609 --
2610 exit when c_prv%notfound or l_prv_rt_strt_dt <= l_effective_date; -- 5898039: Changed from < to <=
2611 end loop;
2612 close c_prv;
2613 --
2614 end if;
2615 hr_utility.set_location ('Cl c_prv '||l_package,10);
2616
2617 p_prtt_rt_val_id := l_prtt_rt_val_id;
2618 /* End Bug 6015724 */
2619 --
2620 -- Flat Amount
2621 --
2622 if l_abr.rt_mlt_cd = 'FLFX' or l_abr.use_calc_acty_bs_rt_flag = 'N' then
2623 if p_cal_val is not null then
2624 l_val := p_cal_val;
2625 else
2626 l_val := l_abr.val;
2627 end if;
2628 --
2629 elsif l_abr.rt_mlt_cd = 'CL' then
2630 benutils.rt_typ_calc
2631 (p_rt_typ_cd => l_abr.rt_typ_cd
2632 ,p_val => l_abr.val
2633 ,p_val_2 => l_compensation_value
2634 ,p_calculated_val => l_val);
2635
2636 elsif l_abr.rt_mlt_cd = 'CVG' then
2637 benutils.rt_typ_calc
2638 (p_rt_typ_cd => l_abr.bnft_rt_typ_cd
2639 ,p_val => l_abr.val
2640 ,p_val_2 => l_coverage_value
2641 ,p_calculated_val => l_val);
2642 /* : 1791203: added */
2643 /*
2644 if nvl(l_abr.det_pl_ytd_cntrs_cd,'X') = 'ESTONLY' then
2645 l_val := ben_distribute_rates.estonly_pp_to_period
2646 (p_business_group_id,
2647 p_person_id,
2648 p_effective_date ,
2649 p_acty_base_rt_id,
2650 p_elig_per_elctbl_chc_id,
2651 l_abr.element_type_id,
2652 l_payroll_id,
2653 l_val
2654 );
2655 end if;
2656 */
2657
2658
2659 elsif l_abr.rt_mlt_cd = 'AP' then
2660 benutils.rt_typ_calc
2661 (p_rt_typ_cd => l_abr.rt_typ_cd
2662 ,p_val => l_abr.val
2663 ,p_val_2 => l_actl_prem_value
2664 ,p_calculated_val => l_val);
2665
2666 elsif l_abr.rt_mlt_cd = 'PRNT' then
2667
2668 hr_utility.set_location(' abr value ' || l_abr.val , 407);
2669 hr_utility.set_location(' parent ' || l_prnt_rt_value , 407);
2670 benutils.rt_typ_calc
2671 (p_rt_typ_cd => l_abr.rt_typ_cd
2672 ,p_val => l_abr.val
2673 ,p_val_2 => l_prnt_rt_value
2674 ,p_calculated_val => l_val);
2675 if l_val is null then
2676 l_val := 0;
2677 end if;
2678 hr_utility.set_location(' result ' || l_val , 407);
2679 elsif l_abr.rt_mlt_cd = 'CLANDCVG' then
2680 benutils.rt_typ_calc
2681 (p_rt_typ_cd => l_abr.rt_typ_cd
2682 ,p_val => l_abr.val
2683 ,p_val_2 => l_compensation_value
2684 ,p_calculated_val => l_value);
2685
2686 --
2687 -- now take l_value and apply it against the coverage
2688 --
2689 benutils.rt_typ_calc
2690 (p_rt_typ_cd => l_abr.bnft_rt_typ_cd
2691 ,p_val => l_value
2692 ,p_val_2 => l_coverage_value
2693 ,p_calculated_val => l_val);
2694
2695 elsif l_abr.rt_mlt_cd = 'APANDCVG' then
2696 benutils.rt_typ_calc
2697 (p_rt_typ_cd => l_abr.rt_typ_cd
2698 ,p_val => l_abr.val
2699 ,p_val_2 => l_actl_prem_value
2700 ,p_calculated_val => l_value);
2701
2702 --
2703 -- now take l_value and apply it against the coverage
2704 --
2705 benutils.rt_typ_calc
2706 (p_rt_typ_cd => l_abr.bnft_rt_typ_cd
2707 ,p_val => l_value
2708 ,p_val_2 => l_coverage_value
2709 ,p_calculated_val => l_val);
2710
2711 elsif l_abr.rt_mlt_cd = 'PRNTANDCVG' then
2712 benutils.rt_typ_calc
2713 (p_rt_typ_cd => l_abr.rt_typ_cd
2714 ,p_val => l_abr.val
2715 ,p_val_2 => l_prnt_rt_value
2716 ,p_calculated_val => l_value);
2717
2718 --
2719 -- now take l_value and apply it against the coverage
2720 --
2721 benutils.rt_typ_calc
2722 (p_rt_typ_cd => l_abr.bnft_rt_typ_cd
2723 ,p_val => l_value
2724 ,p_val_2 => l_coverage_value
2725 ,p_calculated_val => l_val);
2726
2727 elsif l_abr.rt_mlt_cd in ('RL','ERL') then -- added ERL for canon fix
2728 --
2729 -- Call formula initialise routine
2730 --
2731 if l_assignment_id is not null then
2732
2733 --if l_asg.region_2 is not null then
2734 -- l_jurisdiction_code :=
2735 -- pay_mag_utils.lookup_jurisdiction_code
2736 -- (p_state => l_asg.region_2);
2737
2738 --end if;
2739
2740 l_outputs := benutils.formula
2741 (p_formula_id => l_abr.val_calc_rl,
2742 p_effective_date => nvl(p_lf_evt_ocrd_dt,l_effective_date),
2743 p_assignment_id => l_assignment_id,
2744 p_acty_base_rt_id => p_acty_base_rt_id,
2745 p_organization_id => l_organization_id,
2746 p_business_group_id => l_epe.business_group_id,
2747 p_pgm_id => l_epe.pgm_id,
2748 p_pl_id => l_epe.pl_id,
2749 p_pl_typ_id => l_epe.pl_typ_id,
2750 p_opt_id => l_opt.opt_id,
2751 p_ler_id => l_epe.ler_id,
2752 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
2753 p_jurisdiction_code => l_jurisdiction_code,
2754 p_param1 => 'RT_STRT_DT',
2755 p_param1_value => fnd_date.date_to_canonical(l_fonm_rt_strt_dt),
2756 p_param2 => 'CVG_STRT_DT',
2757 p_param2_value => fnd_date.date_to_canonical(l_cvg_eff_dt)
2758 );
2759 l_val := fnd_number.canonical_to_number(l_outputs(l_outputs.first).value); --bug4235088
2760 else
2761 l_val := 0;
2762 end if;
2763
2764 elsif l_abr.rt_mlt_cd = 'SAREC' then
2765 hr_utility.set_location ('BDR_ATP '||l_package||to_char(l_coverage_value),299);
2766 --ikasire -- not passing the p_complete_year_flag per bug 1650517
2767 /* Bug 6015724 */
2768 if l_rt_ovridn_flag = 'Y' and nvl(l_rt_ovridn_thru_dt, hr_api.g_eot) >= l_effective_date then
2769 --
2770 p_ann_val:= l_ann_rt_val; -- ikasire
2771 p_val := l_rt_val;
2772 p_cmcd_val := l_cmcd_rt_val;
2773 --
2774 else
2775 p_ann_val := l_coverage_value;
2776 --
2777 --
2778 --GEVITY
2779 --
2780 IF l_abr.rate_periodization_rl IS NOT NULL THEN
2781 --
2782 ben_distribute_rates.periodize_with_rule
2783 (p_formula_id => l_abr.rate_periodization_rl -- in number,
2784 ,p_effective_date => nvl(p_lf_evt_ocrd_dt,l_effective_date) --in date,
2785 ,p_assignment_id => l_assignment_id --in number,
2786 ,p_convert_from_val => l_coverage_value -- in number,
2787 ,p_convert_from => 'ANNUAL' -- in varchar2,
2788 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id -- in number,
2789 ,p_acty_base_rt_id => p_acty_base_rt_id -- in number,
2790 ,p_business_group_id => l_epe.business_group_id -- in number,
2791 ,p_enrt_rt_id => NULL -- in number default null,
2792 ,p_ann_val => p_ann_val -- out nocopy number,
2793 ,p_cmcd_val => p_cmcd_val -- out nocopy number,
2794 ,p_val => l_val -- out nocopy number
2795 );
2796 --
2797 ELSE
2798 if l_enrt_info_rt_freq_cd = 'PPF' then
2799 l_element_type_id := l_abr.element_type_id;
2800 else
2801 l_element_type_id := null;
2802 end if;
2803 --
2804 l_val := ben_distribute_rates.annual_to_period
2805 (p_amount => p_ann_val,
2806 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
2807 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
2808 p_business_group_id => l_epe.business_group_id,
2809 p_effective_date => l_effective_date,
2810 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2811 p_complete_year_flag => 'Y',
2812 --1791203
2813 p_payroll_id => l_payroll_id,
2814 p_element_type_id => l_element_type_id,
2815 p_rounding_flag => 'N' ); --Bug 2149438
2816 --
2817 --Bug 2149438
2818 l_val := round(l_val,4);
2819 --
2820 hr_utility.set_location ('BDR_ATP '||l_package,291);
2821 /**************************************/
2822 /* --Bug 2149438
2823 l_calc_val := ben_distribute_rates.period_to_annual
2824 (p_amount => l_val,
2825 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
2826 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
2827 p_business_group_id => l_epe.business_group_id,
2828 p_effective_date => l_effective_date,
2829 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2830 p_complete_year_flag => 'Y',
2831 p_payroll_id => l_payroll_id);
2832 */
2833 l_calc_val := p_ann_val ;
2834 --
2835
2836 if l_enrt_info_rt_freq_cd = 'PPF' then
2837 l_element_type_id := l_abr.element_type_id;
2838 else
2839 l_element_type_id := null;
2840 end if;
2841 --
2842 p_cmcd_val := ben_distribute_rates.annual_to_period_out
2843 (p_amount => l_calc_val,
2844 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
2845 p_acty_ref_perd_cd => l_enrt_info_rt_freq_cd,
2846 p_business_group_id => l_epe.business_group_id,
2847 p_effective_date => l_effective_date,
2848 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2849 -- p_complete_year_flag => 'Y',
2850 /* Start of Changes for WWBUG: 1791203 */
2851 p_payroll_id => l_payroll_id,
2852 p_element_type_id => l_element_type_id,
2853 p_pp_in_yr_used_num => p_pp_in_yr_used_num);
2854 END IF; --GEVITY
2855 --
2856 end if; -- end if for if l_rt_ovridn_flag = 'Y'
2857 /* End Bug 6015724 */
2858 --
2859 p_cmcd_acty_ref_perd_cd := l_enrt_info_rt_freq_cd;
2860 hr_utility.set_location ('BDR_ATP '||to_char(p_ann_val),222);
2861 hr_utility.set_location ('BDR_ATP '||to_char(l_cmcd_val),223);
2862 hr_utility.set_location ('BDR_ATP '||to_char(l_val),224);
2863 elsif l_abr.rt_mlt_cd = 'NSVU' then
2864 null; -- do nothing if 'no standard value used'
2865 elsif l_abr.rt_mlt_cd = 'PRV' then
2866 --- for grade step progression
2867 hr_utility.set_location('GSP mulsti code PRV ',551) ;
2868 open c_pgr (l_abr.pay_rate_grade_rule_id , l_effective_date ) ;
2869 fetch c_pgr into l_val ;
2870 close c_pgr ;
2871 hr_utility.set_location('GSP value '|| l_val ,551) ;
2872
2873 else
2874 fnd_message.set_name('BEN','BEN_91342_UNKNOWN_CODE_1');
2875 fnd_message.set_token('PROC',l_package);
2876 fnd_message.set_token('CODE1',l_abr.rt_mlt_cd);
2877 fnd_message.raise_error;
2878 end if;
2879 hr_utility.set_location ('Dn ABR_RTMLTCDS '||l_package,10);
2880 --
2881 -- set some of the outputs
2882 --
2883 hr_utility.set_location(' set outputs ',180);
2884 p_tx_typ_cd := l_abr.tx_typ_cd;
2885 p_acty_typ_cd := l_abr.acty_typ_cd;
2886 p_nnmntry_uom := l_abr.nnmntry_uom;
2887 p_dsply_on_enrt_flag := l_abr.dsply_on_enrt_flag;
2888 p_use_to_calc_net_flx_cr_flag := l_abr.use_to_calc_net_flx_cr_flag;
2889 p_rt_usg_cd := l_abr.rt_usg_cd;
2890 p_mx_elcn_val := l_abr.mx_elcn_val;
2891 p_mn_elcn_val := l_abr.mn_elcn_val;
2892 p_incrmt_elcn_val := l_abr.incrmt_elcn_val;
2893 p_ordr_num := l_abr.ordr_num;
2894
2895 hr_utility.set_location('p_ordr_num ='||p_ordr_num,181);
2896
2897 -- get default value from the right place depending on dflt code
2898 /*
2899 if l_dflt_enrt_cd in ('NSDCS','NNCS') then
2900 p_dflt_val := l_rt_val;
2901 l_abr.dflt_val:=l_rt_val;
2902 else
2903 p_dflt_val := l_abr.dflt_val;
2904 end if;
2905 */
2906 -- Bug 1834655
2907 -- Bug 2400850 : Checking prtt_enrt_rslt_id before assigning
2908 -- values to p_dflt_val
2909 if l_dflt_enrt_cd in ('NSDCS','NNCS')
2910 and p_currepe_row.prtt_enrt_rslt_id is not null
2911 -- Bug 2677804 if there was a overriden rate and you are processing the
2912 -- enrollment after the overrident thru date dont default from the
2913 -- previous enorllment. Instead get it from the abr.
2914 and l_effective_date <= nvl( l_rt_ovridn_thru_dt,l_effective_date + 1 ) then
2915 --
2916 p_dflt_val := l_rt_val;
2917 l_abr.dflt_val:=l_rt_val;
2918 p_ann_dflt_val:= l_ann_rt_val; -- ikasire
2919 --
2920 end if;
2921 --
2922 open c_abp
2923 (c_effective_date => l_effective_date
2924 );
2925 fetch c_abp into l_bnft_prvdr_pool_id;
2926 close c_abp;
2927
2928 p_bnft_prvdr_pool_id := l_bnft_prvdr_pool_id;
2929
2930
2931 --bug : 1433393 Value us validated befoe calcualting the
2932 --variable rate riable rate with value us validated agains the ulitmate values of
2933 -- VAPRO
2934 hr_utility.set_location ('Lim Chk '||l_package,10);
2935 benutils.limit_checks
2936 (p_upr_lmt_val => l_abr.upr_lmt_val,
2937 p_lwr_lmt_val => l_abr.lwr_lmt_val,
2938 p_upr_lmt_calc_rl => l_abr.upr_lmt_calc_rl,
2939 p_lwr_lmt_calc_rl => l_abr.lwr_lmt_calc_rl,
2940 p_effective_date => nvl(p_lf_evt_ocrd_dt,l_effective_date),
2941 p_business_group_id => l_epe.business_group_id,
2942 p_assignment_id => l_assignment_id,
2943 p_acty_base_rt_id => p_acty_base_rt_id,
2944 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
2945 p_organization_id => l_organization_id,
2946 p_pgm_id => l_epe.pgm_id,
2947 p_pl_id => l_epe.pl_id,
2948 p_pl_typ_id => l_epe.pl_typ_id,
2949 p_opt_id => l_opt.opt_id,
2950 p_ler_id => l_epe.ler_id,
2951 p_state => l_state.region_2,
2952 p_val => l_val); -- l_rounded_value);
2953
2954
2955
2956
2957 if l_abr.use_calc_acty_bs_rt_flag = 'N' then
2958 -- if we are not to calculate a rate, do not perform the variable rates
2959 -- code, rounding.
2960 p_val := l_val;
2961 --- this variable used for in out parameter so intialised here
2962 l_vr_ann_mn_elcn_val := l_abr.ann_mn_elcn_val;
2963 l_vr_ann_mx_elcn_val := l_abr.ann_mx_elcn_val;
2964
2965 -- bug 1210355 safe coding make the global variable g_vrbl_mlt_code null
2966 -- if no variable rate is attached
2967 ben_determine_variable_rates.g_vrbl_mlt_code := null;
2968 --
2969
2970 else
2971 --Bug 2192102 if the rate is of Flex credit letus get the associated enrt bnft id
2972 if l_enrt_bnft_id is null and l_abr.rt_usg_cd = 'FLXCR' then
2973 --
2974 hr_utility.set_location (' Before FLXCR l_enrt_bnft_id '||l_enrt_bnft_id, 19);
2975 --
2976 open c_enb_fc ;
2977 fetch c_enb_fc into l_enrt_bnft_id ;
2978 close c_enb_fc;
2979 --
2980 hr_utility.set_location (' After FLXCR l_enrt_bnft_id '||l_enrt_bnft_id, 19);
2981 end if;
2982 --
2983 hr_utility.set_location ('BDVR_MN '||l_package,10);
2984 ben_determine_variable_rates.main
2985 (p_currepe_row => p_currepe_row
2986 ,p_per_row => p_per_row
2987 ,p_asg_row => p_asg_row
2988 ,p_ast_row => p_ast_row
2989 ,p_adr_row => p_adr_row
2990 ,p_person_id => p_person_id
2991 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
2992 ,p_enrt_bnft_id => l_enrt_bnft_id
2993 ,p_acty_base_rt_id => p_acty_base_rt_id
2994 ,p_effective_date => p_effective_date
2995 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
2996 ,p_calc_only_rt_val_flag => p_calc_only_rt_val_flag
2997 ,p_pgm_id => l_epe.pgm_id
2998 ,p_pl_id => l_epe.pl_id
2999 ,p_oipl_id => l_epe.oipl_id
3000 ,p_pl_typ_id => l_epe.pl_typ_id
3001 ,p_per_in_ler_id => l_epe.per_in_ler_id
3002 ,p_ler_id => l_epe.ler_id
3003 ,p_business_group_id => l_epe.business_group_id
3004 ,p_bnft_amt => l_coverage_value
3005 ,p_val => l_vr_val
3006 ,p_mn_elcn_val => l_vr_mn_elcn_val
3007 ,p_mx_elcn_val => l_vr_mx_elcn_val
3008 ,p_incrmnt_elcn_val => l_vr_incrmt_elcn_val
3009 ,p_dflt_elcn_val => l_vr_dflt_elcn_val
3010 ,p_tx_typ_cd => l_vr_tx_typ_cd
3011 ,p_acty_typ_cd => l_vr_acty_typ_cd
3012 ,p_vrbl_rt_trtmt_cd => l_vr_trtmt_cd
3013 ,p_ultmt_upr_lmt => l_ultmt_upr_lmt
3014 ,p_ultmt_lwr_lmt => l_ultmt_lwr_lmt
3015 ,p_ultmt_upr_lmt_calc_rl => l_ultmt_upr_lmt_calc_rl
3016 ,p_ultmt_lwr_lmt_calc_rl => l_ultmt_lwr_lmt_calc_rl
3017 ,p_ann_mn_elcn_val => l_vr_ann_mn_elcn_val
3018 ,p_ann_mx_elcn_val => l_vr_ann_mx_elcn_val
3019 );
3020
3021 hr_utility.set_location(' after ben_determine_variable_rates ',200);
3022 --if l_val is null then
3023 -- l_vr_trtmt_cd := 'RPLC';
3024 --end if;
3025 if (l_abr.rndg_cd is not null or
3026 l_abr.rndg_rl is not null) and
3027 p_perform_rounding_flg
3028 and l_val is not null and
3029 nvl(l_vr_trtmt_cd,' ') <> 'RPLC' then
3030
3031 l_rounded_value := benutils.do_rounding
3032 (p_rounding_cd => l_abr.rndg_cd,
3033 p_rounding_rl => l_abr.rndg_rl,
3034 p_value => l_val,
3035 p_effective_date => l_effective_date);
3036 l_val := l_rounded_value;
3037 end if;
3038
3039 hr_utility.set_location('rounded value rate : ' ||to_char(l_val) ,954);
3040 hr_utility.set_location('variable rate : ' ||l_vr_val ,954);
3041 hr_utility.set_location('treatement : ' ||l_vr_trtmt_cd ,954);
3042 --
3043 -- Tilak Bug 2438506 : if the abr have null values then treat as 1 for
3044 -- multiply
3045 -- as 0 for add or subtract.
3046 --
3047 if l_vr_val is not null then
3048 if l_vr_trtmt_cd = 'RPLC' then
3049 l_val := l_vr_val;
3050 elsif l_vr_trtmt_cd = 'MB' then
3051 l_val := nvl(l_val,1) * l_vr_val;
3052 elsif l_vr_trtmt_cd = 'SF' then
3053 l_val := nvl(l_val,0) - l_vr_val;
3054 elsif l_vr_trtmt_cd = 'ADDTO' then
3055 l_val := nvl(l_val,0) + l_vr_val;
3056 else
3057 l_val := l_vr_val;
3058 end if;
3059 end if;
3060
3061 if l_vr_trtmt_cd is not null then
3062 --- Tilak : this code is not validating whether the enter at enrollemt is defined
3063 --- in either side ,(rate,VAPRO), this just work on the treatment code
3064 --- the User has to make sure both the side has the same setup like enter at enrollemt
3065 --
3066 if l_vr_trtmt_cd = 'RPLC' then
3067 l_vr_mn_elcn_val := nvl(l_vr_mn_elcn_val,l_abr.mn_elcn_val) ;
3068 l_vr_mx_elcn_val := nvl(l_vr_mx_elcn_val,l_abr.mx_elcn_val) ;
3069 l_vr_incrmt_elcn_val := nvl(l_vr_incrmt_elcn_val,l_abr.incrmt_elcn_val );
3070 l_vr_dflt_elcn_val := nvl(l_vr_dflt_elcn_val , l_abr.dflt_val );
3071 l_vr_ann_mn_elcn_val := nvl(l_vr_ann_mn_elcn_val,l_abr.ann_mn_elcn_val);
3072 l_vr_ann_mx_elcn_val := nvl(l_vr_ann_mx_elcn_val,l_abr.ann_mx_elcn_val);
3073 -- Bug 2403243 - Assigning zero to the variables if vapro is returnd null
3074 -- and also l_abr values also null, which is worng.
3075 /*
3076 elsif l_vr_trtmt_cd = 'ADDTO' then
3077
3078 l_vr_mn_elcn_val := nvl(l_vr_mn_elcn_val,0)+ nvl(l_abr.mn_elcn_val,0) ;
3079 l_vr_mx_elcn_val := nvl(l_vr_mx_elcn_val,0)+ nvl(l_abr.mx_elcn_val,0) ;
3080 l_vr_incrmt_elcn_val := nvl(l_vr_incrmt_elcn_val,0)+ nvl(l_abr.incrmt_elcn_val,0);
3081 l_vr_dflt_elcn_val := nvl(l_vr_dflt_elcn_val,0)+ nvl(l_abr.dflt_val,0 );
3082 l_vr_ann_mn_elcn_val := nvl(l_vr_ann_mn_elcn_val,0)+nvl(l_abr.ann_mn_elcn_val,0);
3083 l_vr_ann_mx_elcn_val := nvl(l_vr_ann_mx_elcn_val,0)+nvl(l_abr.ann_mx_elcn_val,0);
3084
3085 elsif l_vr_trtmt_cd = 'SF' then
3086 l_vr_mn_elcn_val := nvl(l_abr.mn_elcn_val,0) - nvl(l_vr_mn_elcn_val,0) ;
3087 l_vr_mx_elcn_val := nvl(l_abr.mx_elcn_val,0) - nvl(l_vr_mx_elcn_val,0) ;
3088 l_vr_incrmt_elcn_val := nvl(l_abr.incrmt_elcn_val,0)- nvl(l_vr_incrmt_elcn_val,0);
3089 l_vr_dflt_elcn_val := nvl(l_abr.dflt_val,0 ) - nvl(l_vr_dflt_elcn_val,0);
3090 l_vr_ann_mn_elcn_val := nvl(l_abr.ann_mn_elcn_val,0)- nvl(l_vr_ann_mn_elcn_val,0);
3091 l_vr_ann_mx_elcn_val := nvl(l_abr.ann_mx_elcn_val,0)- nvl(l_vr_ann_mx_elcn_val,0);
3092
3093 elsif l_vr_trtmt_cd = 'MB' then
3094
3095 l_vr_mn_elcn_val := nvl(l_abr.mn_elcn_val,0)*nvl(l_vr_mn_elcn_val,0) ;
3096 l_vr_mx_elcn_val := nvl(l_abr.mx_elcn_val,0)*nvl(l_vr_mx_elcn_val,0) ;
3097 l_vr_incrmt_elcn_val := nvl(l_abr.incrmt_elcn_val,0)*nvl(l_vr_incrmt_elcn_val,0);
3098 l_vr_dflt_elcn_val := nvl(l_abr.dflt_val,0 )*nvl(l_vr_dflt_elcn_val,0);
3099 l_vr_ann_mn_elcn_val := nvl(l_vr_ann_mn_elcn_val,0)*nvl(l_abr.ann_mn_elcn_val,0);
3100 l_vr_ann_mx_elcn_val := nvl(l_vr_ann_mx_elcn_val,0)*nvl(l_abr.ann_mx_elcn_val,0);
3101 */
3102 elsif l_vr_trtmt_cd = 'ADDTO' then
3103
3104 l_vr_mn_elcn_val := get_expr_val('+', l_vr_mn_elcn_val,l_abr.mn_elcn_val);
3105 l_vr_mx_elcn_val := get_expr_val('+', l_vr_mx_elcn_val,l_abr.mx_elcn_val);
3106 l_vr_incrmt_elcn_val := get_expr_val('+', l_vr_incrmt_elcn_val,l_abr.incrmt_elcn_val);
3107 l_vr_dflt_elcn_val := get_expr_val('+', l_vr_dflt_elcn_val,l_abr.dflt_val);
3108 l_vr_ann_mn_elcn_val := get_expr_val('+', l_vr_ann_mn_elcn_val,l_abr.ann_mn_elcn_val);
3109 l_vr_ann_mx_elcn_val := get_expr_val('+', l_vr_ann_mx_elcn_val,l_abr.ann_mx_elcn_val);
3110
3111 elsif l_vr_trtmt_cd = 'SF' then
3112 l_vr_mn_elcn_val := get_expr_val('-', l_abr.mn_elcn_val,l_vr_mn_elcn_val);
3113 l_vr_mx_elcn_val := get_expr_val('-', l_abr.mx_elcn_val,l_vr_mx_elcn_val);
3114 l_vr_incrmt_elcn_val := get_expr_val('-', l_abr.incrmt_elcn_val,l_vr_incrmt_elcn_val);
3115 l_vr_dflt_elcn_val := get_expr_val('-', l_abr.dflt_val,l_vr_dflt_elcn_val);
3116 l_vr_ann_mn_elcn_val := get_expr_val('-', l_abr.ann_mn_elcn_val,l_vr_ann_mn_elcn_val);
3117 l_vr_ann_mx_elcn_val := get_expr_val('-', l_abr.ann_mx_elcn_val,l_vr_ann_mx_elcn_val);
3118
3119 elsif l_vr_trtmt_cd = 'MB' then
3120
3121 l_vr_mn_elcn_val := get_expr_val('*', l_abr.mn_elcn_val,l_vr_mn_elcn_val);
3122 l_vr_mx_elcn_val := get_expr_val('*', l_abr.mx_elcn_val,l_vr_mx_elcn_val) ;
3123 l_vr_incrmt_elcn_val := get_expr_val('*', l_abr.incrmt_elcn_val,l_vr_incrmt_elcn_val);
3124 l_vr_dflt_elcn_val := get_expr_val('*', l_abr.dflt_val,l_vr_dflt_elcn_val);
3125 l_vr_ann_mn_elcn_val := get_expr_val('*', l_abr.ann_mn_elcn_val,l_vr_ann_mn_elcn_val);
3126 l_vr_ann_mx_elcn_val := get_expr_val('*', l_abr.ann_mx_elcn_val,l_vr_ann_mx_elcn_val);
3127
3128 else
3129 l_vr_mn_elcn_val := l_abr.mn_elcn_val ;
3130 l_vr_mx_elcn_val := l_abr.mx_elcn_val ;
3131 l_vr_dflt_elcn_val := l_abr.dflt_val ;
3132 l_vr_incrmt_elcn_val := l_abr.incrmt_elcn_val ;
3133 --
3134 -- For selfservice enhancement :
3135 -- Should assign l_abr.ann_mn_elcn_val not l_abr.ann_mx_elcn_val
3136 --
3137 l_vr_ann_mn_elcn_val := l_abr.ann_mn_elcn_val;
3138 l_vr_ann_mx_elcn_val := l_abr.ann_mx_elcn_val;
3139
3140 end if;
3141 else
3142 l_vr_mn_elcn_val := l_abr.mn_elcn_val ;
3143 l_vr_mx_elcn_val := l_abr.mx_elcn_val ;
3144 l_vr_dflt_elcn_val := l_abr.dflt_val ;
3145 l_vr_incrmt_elcn_val := l_abr.incrmt_elcn_val ;
3146 --
3147 -- For selfservice enhancement :
3148 -- Should assign l_abr.ann_mn_elcn_val not l_abr.ann_mx_elcn_val
3149 --
3150 l_vr_ann_mn_elcn_val := l_abr.ann_mn_elcn_val;
3151 l_vr_ann_mx_elcn_val := l_abr.ann_mx_elcn_val;
3152
3153 ---
3154 end if ;
3155 --
3156 l_abr.dflt_val := nvl(l_vr_dflt_elcn_val,l_abr.dflt_val);
3157 --
3158 hr_utility.set_location(' l_vr_dflt_elcn_val ' ||l_abr.dflt_val , 199);
3159
3160 hr_utility.set_location(' mn_elcn_va ' || l_vr_mn_elcn_val , 199);
3161 p_val := l_val;
3162 hr_utility.set_location('valiable and rate : ' ||to_char(l_val) ,954);
3163 if l_ultmt_upr_lmt is not null or l_ultmt_lwr_lmt is not null
3164 OR l_ultmt_upr_lmt_calc_rl is not null or l_ultmt_lwr_lmt_calc_rl is not null then
3165 hr_utility.set_location(' calling ultmate check rate ' ,393);
3166 hr_utility.set_location('upper '|| l_ultmt_upr_lmt ||' Lower' || l_ultmt_lwr_lmt,393);
3167 hr_utility.set_location('ammount '|| p_val ,393);
3168
3169 benutils.limit_checks
3170 (p_upr_lmt_val => l_ultmt_upr_lmt,
3171 p_lwr_lmt_val => l_ultmt_lwr_lmt,
3172 p_upr_lmt_calc_rl => l_ultmt_upr_lmt_calc_rl,
3173 p_lwr_lmt_calc_rl => l_ultmt_lwr_lmt_calc_rl,
3174 p_effective_date => l_effective_date,
3175 p_business_group_id => l_epe.business_group_id,
3176 p_assignment_id => l_assignment_id,
3177 p_acty_base_rt_id => p_acty_base_rt_id,
3178 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
3179 p_organization_id => l_organization_id,
3180 p_pgm_id => l_epe.pgm_id,
3181 p_pl_id => l_epe.pl_id,
3182 p_pl_typ_id => l_epe.pl_typ_id,
3183 p_opt_id => l_opt.opt_id,
3184 p_ler_id => l_epe.ler_id,
3185 p_state => l_state.region_2,
3186 p_val => p_val); -- l_rounded_value);
3187 hr_utility.set_location('ammount '|| p_val ,393);
3188
3189 end if ;
3190 --
3191 end if;
3192 --
3193 -- Start 6015724
3194 --
3195 if l_rt_ovridn_flag = 'Y' and
3196 nvl(l_rt_ovridn_thru_dt, hr_api.g_eot) >= l_effective_date then -- p_effective_date then
3197 p_val := l_rt_val;
3198 end if;
3199 --
3200 -- End 6015724
3201 --
3202 --
3203 -- The rate value has been calculated. All the value
3204 -- related processing is done.
3205 -- Hence, if the p_calc_only_rt_flag is ON, then
3206 -- we should just return.
3207 -- Bug 2677804 use l_effective_date to handle open enrollment also
3208 if p_calc_only_rt_val_flag then
3209 /* 6015724 - Commenting out this code as we are already doing this calculation before now
3210 if l_rt_ovridn_flag = 'Y' and
3211 nvl(l_rt_ovridn_thru_dt, hr_api.g_eot) >= l_effective_date then -- p_effective_date then
3212 p_val := l_rt_val;
3213 end if; */
3214 return;
3215 end if;
3216 --
3217 if l_pln_auto_distr_flag = 'Y' then
3218 p_iss_val := p_val;
3219 end if;
3220 --
3221 -- Get the participation start date to use in
3222 -- ben_determine_date.
3223 -- Note: don't check for notfound because
3224 --
3225 -- 1) will handle null anyway.
3226 -- 2) any eligibility requirements are already handled in bendenrr
3227 -- don't want to second guess it here
3228 --
3229 -- Check if the context information for the EPE is set. When it is
3230 -- there is no need to fire cursor because the information is pre
3231 -- derived.
3232 --
3233 if ben_manage_life_events.fonm = 'Y' then
3234 l_rt_strt_dt := l_fonm_rt_strt_dt;
3235 p_rt_strt_dt := l_rt_strt_dt;
3236 else
3237 if p_currepe_row.prtn_strt_dt is not null then
3238 --
3239 l_prtn_strt_dt := p_currepe_row.prtn_strt_dt;
3240 --
3241 else
3242 --
3243 open c_current_elig
3244 (c_person_id => p_person_id
3245 ,c_pl_id => l_epe.pl_id
3246 ,c_pgm_id => nvl(l_epe.pgm_id,-1)
3247 ,c_opt_id => l_opt.opt_id
3248 ,c_nvlopt_id => nvl(l_opt.opt_id,hr_api.g_number)
3249 ,c_effective_date => l_effective_date
3250 );
3251 fetch c_current_elig into l_prtn_strt_dt;
3252 close c_current_elig;
3253 --
3254 end if;
3255 hr_utility.set_location ('BDD_RACD '||l_package,10);
3256 ben_determine_date.rate_and_coverage_dates
3257 (p_cache_mode => TRUE
3258 ,p_par_ptip_id => l_epe.ptip_id
3259 ,p_par_plip_id => l_epe.plip_id
3260 ,p_person_id => l_epe.person_id
3261 ,p_per_in_ler_id => l_epe.per_in_ler_id
3262 ,p_pgm_id => l_epe.pgm_id
3263 ,p_pl_id => l_epe.pl_id
3264 ,p_oipl_id => l_epe.oipl_id
3265 ,p_enrt_perd_id => l_epe.enrt_perd_id
3266 ,p_lee_rsn_id => l_epe.lee_rsn_id
3267 --
3268 ,p_which_dates_cd => 'R'
3269 ,p_date_mandatory_flag => 'N'
3270 ,p_compute_dates_flag => 'Y'
3271 /*
3272 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
3273 */
3274 ,p_business_group_id => l_epe.business_group_id
3275 ,p_acty_base_rt_id => p_acty_base_rt_id
3276 ,p_effective_date => p_effective_date
3277 ,p_lf_evt_ocrd_dt => greatest
3278 (nvl(p_lf_evt_ocrd_dt,l_prtn_strt_dt)
3279 ,nvl(l_prtn_strt_dt,p_lf_evt_ocrd_dt))
3280 ,p_rt_strt_dt => l_rt_strt_dt
3281 ,p_rt_strt_dt_cd => l_rt_strt_dt_cd
3282 ,p_rt_strt_dt_rl => l_rt_strt_dt_rl
3283 ,p_enrt_cvg_strt_dt => l_dummy_date
3284 ,p_enrt_cvg_strt_dt_cd => l_dummy_char
3285 ,p_enrt_cvg_strt_dt_rl => l_dummy_num
3286 ,p_enrt_cvg_end_dt => l_dummy_date
3287 ,p_enrt_cvg_end_dt_cd => l_dummy_char
3288 ,p_enrt_cvg_end_dt_rl => l_dummy_num
3289 ,p_rt_end_dt => l_dummy_date
3290 ,p_rt_end_dt_cd => l_dummy_char
3291 ,p_rt_end_dt_rl => l_dummy_num
3292 );
3293 hr_utility.set_location ('Dn BDD_RACD '||l_package,10);
3294 p_rt_strt_dt := l_rt_strt_dt;
3295 p_rt_strt_dt_cd := l_rt_strt_dt_cd;
3296 p_rt_strt_dt_rl := l_rt_strt_dt_rl;
3297 end if;
3298
3299 p_entr_ann_val_flag := l_abr.entr_ann_val_flag;
3300 p_ptd_comp_lvl_fctr_id := l_abr.ptd_comp_lvl_fctr_id;
3301 p_clm_comp_lvl_fctr_id := l_abr.clm_comp_lvl_fctr_id;
3302 --
3303
3304 -- bug 1210355, if variable rate is detected use the mlt code attached to the variable profile
3305 if l_vr_trtmt_cd = 'RPLC' then
3306 p_rt_mlt_cd := nvl(ben_determine_variable_rates.g_vrbl_mlt_code,l_abr.rt_mlt_cd);
3307 else
3308 p_rt_mlt_cd := l_abr.rt_mlt_cd;
3309 end if;
3310 --
3311
3312 --
3313 -- compute annual val and communicated values
3314 --
3315 if l_abr.rt_mlt_cd <> 'SAREC' then
3316 if l_abr.bnft_rt_typ_cd = 'PCT' or l_abr.use_calc_acty_bs_rt_flag = 'N' then
3317 -- Percents and non-calculated rates should not have computed communicated
3318 -- nor annual values.
3319 -- bug 1834655
3320 if l_dflt_enrt_cd in ('NSDCS','NNCS') and
3321 p_currepe_row.prtt_enrt_rslt_id is not null then
3322 --
3323 null ;
3324 --
3325 else
3326 if l_abr.entr_ann_val_flag = 'Y' then
3327 p_ann_dflt_val := l_abr.dflt_val;
3328 else
3329 p_dflt_val := l_abr.dflt_val;
3330 end if;
3331 end if;
3332 -- p_ann_dflt_val := l_abr.dflt_val;
3333 p_ann_val := p_val;
3334 p_ann_mn_elcn_val := l_vr_ann_mn_elcn_val;
3335 p_ann_mx_elcn_val := l_vr_ann_mx_elcn_val;
3336 p_cmcd_val := p_val;
3337 p_cmcd_mn_elcn_val := nvl(l_vr_ann_mn_elcn_val,l_abr.ann_mn_elcn_val);
3338 p_cmcd_mx_elcn_val := nvl(l_vr_ann_mx_elcn_val,l_abr.ann_mx_elcn_val);
3339 p_cmcd_acty_ref_perd_cd := l_enrt_info_rt_freq_cd;
3340
3341 else
3342 hr_utility.set_location ('BDR_PTA '||l_package,10);
3343 hr_utility.set_location (' l_abr.dflt_val '||l_abr.dflt_val,22);
3344 hr_utility.set_location (' p_elig_per_elctbl_chc_id '||p_elig_per_elctbl_chc_id,22);
3345 --
3346 --Bug 2445318 We need to see whether to assign the l_abr.dflt_val to
3347 -- p_ann_dflt_val or p_dflt_val
3348 --
3349 --BUG 3253180 We dont need to do this for current enrollment
3350 --
3351 -- if nvl(l_env.mode_cd,'~') <> 'D' then
3352 --
3353 if l_dflt_enrt_cd in ('NSDCS','NNCS') and
3354 p_currepe_row.prtt_enrt_rslt_id is not null then
3355 --
3356 null ;
3357 else
3358 --
3359 if l_abr.dflt_val is not null then
3360 if p_entr_ann_val_flag = 'Y' then
3361 --
3362 p_ann_dflt_val := l_abr.dflt_val ;
3363 --
3364 else
3365 --GEVITY
3366 IF l_abr.rate_periodization_rl IS NOT NULL THEN
3367 --
3368 ben_distribute_rates.periodize_with_rule
3369 (p_formula_id => l_abr.rate_periodization_rl
3370 ,p_effective_date => nvl(p_lf_evt_ocrd_dt,l_effective_date)
3371 ,p_assignment_id => l_assignment_id
3372 ,p_convert_from_val => l_abr.dflt_val
3373 ,p_convert_from => 'DEFINED'
3374 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
3375 ,p_acty_base_rt_id => p_acty_base_rt_id
3376 ,p_business_group_id => l_epe.business_group_id
3377 ,p_enrt_rt_id => NULL
3378 ,p_ann_val => p_ann_dflt_val
3379 ,p_cmcd_val => l_cmcd_dummy
3380 ,p_val => l_dfnd_dummy
3381 );
3382 --
3383 ELSE
3384 --
3385 p_ann_dflt_val := ben_distribute_rates.period_to_annual
3386 (p_amount => l_abr.dflt_val,
3387 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
3388 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
3389 p_business_group_id => l_epe.business_group_id,
3390 p_effective_date => l_effective_date,
3391 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
3392 p_complete_year_flag => 'Y',
3393 p_payroll_id => l_payroll_id);
3394 p_dflt_val := l_abr.dflt_val;
3395 --
3396 END IF; --GEVITY
3397 end if;
3398 end if;
3399 end if;
3400 --
3401 -- end if; -- 'D' Mode
3402 hr_utility.set_location (' p_dflt_val '||p_dflt_val,23);
3403 hr_utility.set_location (' p_ann_dflt_val '||p_ann_dflt_val,23);
3404 hr_utility.set_location (' l_ann_dflt_val '||l_ann_dflt_val,22);
3405 --GEVITY
3406 IF l_abr.rate_periodization_rl IS NOT NULL THEN
3407 --
3408 l_dfnd_dummy := p_val ;
3409 --
3410 ben_distribute_rates.periodize_with_rule
3411 (p_formula_id => l_abr.rate_periodization_rl
3412 ,p_effective_date => nvl(p_lf_evt_ocrd_dt,l_effective_date)
3413 ,p_assignment_id => l_assignment_id
3414 ,p_convert_from_val => l_dfnd_dummy
3415 ,p_convert_from => 'DEFINED'
3416 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
3417 ,p_acty_base_rt_id => p_acty_base_rt_id
3418 ,p_business_group_id => l_epe.business_group_id
3419 ,p_enrt_rt_id => NULL
3420 ,p_ann_val => l_ann_val
3421 ,p_cmcd_val => l_cmcd_val
3422 ,p_val => p_val
3423 );
3424 --
3425 l_dfnd_dummy := NULL ;
3426 --
3427 ELSE
3428 --
3429 l_ann_val := ben_distribute_rates.period_to_annual
3430 (p_amount => p_val,
3431 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
3432 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
3433 p_business_group_id => l_epe.business_group_id,
3434 p_effective_date => l_effective_date,
3435 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
3436 p_complete_year_flag => 'Y',
3437 p_payroll_id => l_payroll_id);
3438 hr_utility.set_location ('BDR_PTA '||l_package,290);
3439 --
3440 l_calc_val := ben_distribute_rates.period_to_annual
3441 (p_amount => p_val,
3442 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
3443 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
3444 p_business_group_id => l_epe.business_group_id,
3445 p_effective_date => l_effective_date,
3446 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
3447 p_complete_year_flag => 'Y',
3448 p_payroll_id => l_payroll_id);
3449 --
3450 if l_enrt_info_rt_freq_cd = 'PPF' then
3451 l_element_type_id := l_abr.element_type_id;
3452 else
3453 l_element_type_id := null;
3454 end if;
3455 --
3456 l_cmcd_val := ben_distribute_rates.annual_to_period_out
3457 (p_amount => l_calc_val,
3458 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
3459 p_acty_ref_perd_cd => l_enrt_info_rt_freq_cd,
3460 p_business_group_id => l_epe.business_group_id,
3461 p_effective_date => l_effective_date,
3462 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
3463 p_complete_year_flag => 'Y',
3464 /* Start of Changes for WWBUG: 1791203 */
3465 p_payroll_id => l_payroll_id,
3466 p_element_type_id => l_element_type_id,
3467 p_pp_in_yr_used_num => p_pp_in_yr_used_num);
3468 END IF; --GEVITY
3469 --
3470 p_ann_val := l_ann_val;
3471 p_cmcd_val := l_cmcd_val;
3472 p_cmcd_acty_ref_perd_cd := l_enrt_info_rt_freq_cd;
3473 --
3474 --Bug 5225815 do not prorate if there are no prior elections
3475 --or the l_rt_strt_dt is null
3476 if l_abr.entr_ann_val_flag = 'Y' and l_rt_strt_dt is not null then
3477 -- Before assigning the annual min and max, try to prorate the values.
3478 ben_distribute_rates.prorate_min_max
3479 (p_person_id => p_person_id
3480 ,p_effective_date => l_effective_date
3481 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
3482 ,p_acty_base_rt_id => p_acty_base_rt_id
3483 ,p_rt_strt_dt => l_rt_strt_dt
3484 ,p_ann_mn_val => l_vr_ann_mn_elcn_val
3485 ,p_ann_mx_val => l_vr_ann_mx_elcn_val ) ;
3486 -- Also, check that their period-to-date payments and claims do not
3487 -- force the minimum and maximum to be different.
3488 ben_distribute_rates.compare_balances
3489 (p_person_id => p_person_id
3490 ,p_effective_date => l_effective_date
3491 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
3492 ,p_pgm_id => l_epe.pgm_id
3493 ,p_pl_id => l_epe.pl_id
3494 ,p_oipl_id => l_epe.oipl_id
3495 ,p_per_in_ler_id => l_epe.per_in_ler_id
3496 ,p_business_group_id => l_epe.business_group_id
3497 ,p_acty_base_rt_id => p_acty_base_rt_id
3498 ,p_ann_mn_val => l_vr_ann_mn_elcn_val
3499 ,p_ann_mx_val => l_vr_ann_mx_elcn_val
3500 ,p_ptd_balance => l_ptd_balance -- outputs not used here.
3501 ,p_clm_balance => l_clm_balance ) ;
3502 end if;
3503 --
3504 p_ann_mn_elcn_val := nvl(l_vr_ann_mn_elcn_val,l_abr.ann_mn_elcn_val);
3505 p_ann_mx_elcn_val := nvl(l_vr_ann_mx_elcn_val,l_abr.ann_mx_elcn_val);
3506 hr_utility.set_location ('BDR_ATP '||l_package,290);
3507 hr_utility.set_location ('Dn BDR_ATP '||l_package,290);
3508 --
3509 end if;
3510 --
3511 -- bug fix 2569884 - Communicated Min and Max values are needed for self service
3512 -- irrespective of whether calculate for enrollment flag is checked or not.
3513 -- Hence, moving the logic of calculating l_cmcd_mn_val/l_cmcd_mx_val outside the
3514 -- if condition of use_calc_acty_bs_rt_flag (above).
3515 -- The communicated Min/Max are to be calculated only when rate is
3516 -- 'Enter Value at enrollment'
3517 --
3518 if l_abr.entr_val_at_enrt_flag = 'Y' then
3519 --
3520 -- For selfservice enhancement : Communicated values are required
3521 -- on self service pages.
3522 --
3523 if(l_abr.entr_ann_val_flag = 'Y') then
3524 --
3525 if nvl(l_vr_ann_mn_elcn_val,l_abr.ann_mn_elcn_val) is not null then
3526 --
3527 if l_enrt_info_rt_freq_cd = 'PPF' then
3528 l_element_type_id := l_abr.element_type_id;
3529 else
3530 l_element_type_id := null;
3531 end if;
3532 --
3533 IF l_abr.rate_periodization_rl IS NOT NULL THEN
3534 --
3535 ben_distribute_rates.periodize_with_rule
3536 (p_formula_id => l_abr.rate_periodization_rl
3537 ,p_effective_date => nvl(p_lf_evt_ocrd_dt,l_effective_date)
3538 ,p_assignment_id => l_assignment_id
3539 ,p_convert_from_val => nvl(l_vr_ann_mn_elcn_val,l_abr.ann_mn_elcn_val)
3540 ,p_convert_from => 'ANNUAL'
3541 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
3542 ,p_acty_base_rt_id => p_acty_base_rt_id
3543 ,p_business_group_id => l_epe.business_group_id
3544 ,p_enrt_rt_id => NULL
3545 ,p_ann_val => l_ann_dummy
3546 ,p_cmcd_val => l_cmcd_mn_val
3547 ,p_val => l_dfnd_dummy
3548 );
3549 --
3550 ELSE
3551 --
3552 l_cmcd_mn_val := ben_distribute_rates.annual_to_period
3553 (p_amount => nvl(l_vr_ann_mn_elcn_val,l_abr.ann_mn_elcn_val),
3554 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
3555 p_acty_ref_perd_cd => l_enrt_info_rt_freq_cd,
3556 p_business_group_id => l_epe.business_group_id,
3557 p_effective_date => l_effective_date,
3558 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
3559 p_complete_year_flag => 'Y',
3560 p_payroll_id => l_payroll_id,
3561 p_element_type_id => l_element_type_id);
3562 END IF; --GEVITY
3563 end if;
3564
3565 if nvl(l_vr_ann_mx_elcn_val,l_abr.ann_mx_elcn_val) is not null then
3566 --GEVITY
3567 IF l_abr.rate_periodization_rl IS NOT NULL THEN
3568 --
3569 ben_distribute_rates.periodize_with_rule
3570 (p_formula_id => l_abr.rate_periodization_rl
3571 ,p_effective_date => nvl(p_lf_evt_ocrd_dt,l_effective_date)
3572 ,p_assignment_id => l_assignment_id
3573 ,p_convert_from_val => nvl(l_vr_ann_mx_elcn_val,l_abr.ann_mx_elcn_val)
3574 ,p_convert_from => 'ANNUAL'
3575 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
3576 ,p_acty_base_rt_id => p_acty_base_rt_id
3577 ,p_business_group_id => l_epe.business_group_id
3578 ,p_enrt_rt_id => NULL
3579 ,p_ann_val => l_ann_dummy
3580 ,p_cmcd_val => l_cmcd_mx_val
3581 ,p_val => l_dfnd_dummy
3582 );
3583 --
3584 ELSE
3585 --
3586 if l_enrt_info_rt_freq_cd = 'PPF' then
3587 l_element_type_id := l_abr.element_type_id;
3588 else
3589 l_element_type_id := null;
3590 end if;
3591 --
3592 l_cmcd_mx_val := ben_distribute_rates.annual_to_period
3593 (p_amount =>nvl(l_vr_ann_mx_elcn_val,l_abr.ann_mx_elcn_val),
3594 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
3595 p_acty_ref_perd_cd => l_enrt_info_rt_freq_cd,
3596 p_business_group_id => l_epe.business_group_id,
3597 p_effective_date => l_effective_date,
3598 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
3599 p_complete_year_flag => 'Y',
3600 p_payroll_id => l_payroll_id,
3601 p_element_type_id => l_element_type_id);
3602 END IF; --GEVITY
3603 end if;
3604
3605 -- hmani
3606
3607 if l_abr.dflt_val is not null then
3608 hr_utility.set_location ('HMANI '||l_abr.dflt_val,290.3);
3609 --GEVITY
3610 IF l_abr.rate_periodization_rl IS NOT NULL THEN
3611 --
3612 --null;
3613
3614 ben_distribute_rates.periodize_with_rule
3615 (p_formula_id => l_abr.rate_periodization_rl
3616 ,p_effective_date => nvl(p_lf_evt_ocrd_dt,l_effective_date)
3617 ,p_assignment_id => l_assignment_id
3618 ,p_convert_from_val => p_ann_dflt_val
3619 ,p_convert_from => 'ANNUAL'
3620 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
3621 ,p_acty_base_rt_id => p_acty_base_rt_id
3622 ,p_business_group_id => l_epe.business_group_id
3623 ,p_enrt_rt_id => NULL
3624 ,p_ann_val => l_ann_dummy
3625 ,p_cmcd_val => l_cmcd_dflt_val
3626 ,p_val => l_dfnd_dummy
3627 );
3628 --
3629
3630 ELSE
3631 --
3632 if l_enrt_info_rt_freq_cd = 'PPF' then
3633 l_element_type_id := l_abr.element_type_id;
3634 else
3635 l_element_type_id := null;
3636 end if;
3637 --
3638 hr_utility.set_location ('HMANI Me here'||l_cmcd_dflt_val,290.3);
3639 l_cmcd_dflt_val := ben_distribute_rates.annual_to_period
3640 (p_amount => p_ann_dflt_val,
3641 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
3642 p_acty_ref_perd_cd => l_enrt_info_rt_freq_cd,
3643 p_business_group_id => l_epe.business_group_id,
3644 p_effective_date => l_effective_date,
3645 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
3646 p_complete_year_flag => 'Y',
3647 p_payroll_id => l_payroll_id,
3648 p_element_type_id => l_element_type_id);
3649
3650 --7154229
3651 if(p_cmcd_val is null) then
3652 p_cmcd_val := l_cmcd_dflt_val;
3653 end if;
3654 hr_utility.set_location ('HMANI '||l_cmcd_dflt_val,290.3);
3655 END IF; --GEVITY
3656 end if;
3657
3658 -- End of hmani
3659
3660 else
3661 --
3662 -- When enter annual value flag is 'N', we need to
3663 -- convert defined values into annual values
3664 -- and then covert them into communicated min max values
3665 --
3666 if (nvl(l_vr_mn_elcn_val,l_abr.mn_elcn_val)) is not null then
3667 --GEVITY
3668 IF l_abr.rate_periodization_rl IS NOT NULL THEN
3669 --
3670 ben_distribute_rates.periodize_with_rule
3671 (p_formula_id => l_abr.rate_periodization_rl
3672 ,p_effective_date => nvl(p_lf_evt_ocrd_dt,l_effective_date)
3673 ,p_assignment_id => l_assignment_id
3674 ,p_convert_from_val => nvl(l_vr_mn_elcn_val,l_abr.mn_elcn_val)
3675 ,p_convert_from => 'DEFINED'
3676 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
3677 ,p_acty_base_rt_id => p_acty_base_rt_id
3678 ,p_business_group_id => l_epe.business_group_id
3679 ,p_enrt_rt_id => NULL
3680 ,p_ann_val => l_cmcd_ann_mn_val
3681 ,p_cmcd_val => l_cmcd_mn_val
3682 ,p_val => l_dfnd_dummy
3683 );
3684 --
3685 ELSE
3686 --
3687 l_cmcd_ann_mn_val := ben_distribute_rates.period_to_annual
3688 (p_amount => nvl(l_vr_mn_elcn_val,l_abr.mn_elcn_val),
3689 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
3690 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
3691 p_business_group_id => l_epe.business_group_id,
3692 p_effective_date => l_effective_date,
3693 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
3694 p_complete_year_flag => 'Y',
3695 p_payroll_id => l_payroll_id);
3696 --
3697 if l_enrt_info_rt_freq_cd = 'PPF' then
3698 l_element_type_id := l_abr.element_type_id;
3699 else
3700 l_element_type_id := null;
3701 end if;
3702 --
3703 l_cmcd_mn_val := ben_distribute_rates.annual_to_period
3704 (p_amount =>l_cmcd_ann_mn_val ,
3705 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
3706 p_acty_ref_perd_cd => l_enrt_info_rt_freq_cd,
3707 p_business_group_id => l_epe.business_group_id,
3708 p_effective_date => l_effective_date,
3709 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
3710 p_complete_year_flag => 'Y',
3711 p_payroll_id => l_payroll_id,
3712 p_element_type_id => l_element_type_id);
3713 --
3714 END IF; --GEVITY
3715 end if;
3716
3717 if (nvl(l_vr_mx_elcn_val,l_abr.mx_elcn_val)) is not null then
3718 --GEVITY
3719 IF l_abr.rate_periodization_rl IS NOT NULL THEN
3720 --
3721 ben_distribute_rates.periodize_with_rule
3722 (p_formula_id => l_abr.rate_periodization_rl
3723 ,p_effective_date => nvl(p_lf_evt_ocrd_dt,l_effective_date)
3724 ,p_assignment_id => l_assignment_id
3725 ,p_convert_from_val => nvl(l_vr_mx_elcn_val,l_abr.mx_elcn_val)
3726 ,p_convert_from => 'DEFINED'
3727 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
3728 ,p_acty_base_rt_id => p_acty_base_rt_id
3729 ,p_business_group_id => l_epe.business_group_id
3730 ,p_enrt_rt_id => NULL
3731 ,p_ann_val => l_cmcd_ann_mx_val
3732 ,p_cmcd_val => l_cmcd_mx_val
3733 ,p_val => l_dfnd_dummy
3734 );
3735 --
3736 ELSE
3737 --
3738 --
3739 l_cmcd_ann_mx_val := ben_distribute_rates.period_to_annual
3740 (p_amount => nvl(l_vr_mx_elcn_val,l_abr.mx_elcn_val),
3741 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
3742 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
3743 p_business_group_id => l_epe.business_group_id,
3744 p_effective_date => l_effective_date,
3745 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
3746 p_complete_year_flag => 'Y',
3747 p_payroll_id => l_payroll_id);
3748 --
3749
3750 if l_enrt_info_rt_freq_cd = 'PPF' then
3751 l_element_type_id := l_abr.element_type_id;
3752 else
3753 l_element_type_id := null;
3754 end if;
3755 --
3756 l_cmcd_mx_val := ben_distribute_rates.annual_to_period
3757 (p_amount =>l_cmcd_ann_mx_val,
3758 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
3759 p_acty_ref_perd_cd => l_enrt_info_rt_freq_cd,
3760 p_business_group_id => l_epe.business_group_id,
3761 p_effective_date => l_effective_date,
3762 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
3763 p_complete_year_flag => 'Y',
3764 p_payroll_id => l_payroll_id,
3765 p_element_type_id => l_element_type_id);
3766 --
3767 END IF; --GEVITY
3768 end if;
3769
3770 --hmani -- 3274902
3771 if l_abr.dflt_val is not null then
3772 --GEVITY
3773 IF l_abr.rate_periodization_rl IS NOT NULL THEN
3774 --
3775 ben_distribute_rates.periodize_with_rule
3776 (p_formula_id => l_abr.rate_periodization_rl
3777 ,p_effective_date => nvl(p_lf_evt_ocrd_dt,l_effective_date)
3778 ,p_assignment_id => l_assignment_id
3779 ,p_convert_from_val => l_abr.dflt_val
3780 ,p_convert_from => 'DEFINED'
3781 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
3782 ,p_acty_base_rt_id => p_acty_base_rt_id
3783 ,p_business_group_id => l_epe.business_group_id
3784 ,p_enrt_rt_id => NULL
3785 ,p_ann_val => l_cmcd_ann_dflt_val
3786 ,p_cmcd_val => l_cmcd_dflt_val
3787 ,p_val => l_dfnd_dummy
3788 );
3789 --
3790 ELSE
3791 --
3792 --
3793 l_cmcd_ann_dflt_val := ben_distribute_rates.period_to_annual
3794 (p_amount => l_abr.dflt_val,
3795 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
3796 p_acty_ref_perd_cd => l_acty_ref_perd_cd,
3797 p_business_group_id => l_epe.business_group_id,
3798 p_effective_date => l_effective_date,
3799 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
3800 p_complete_year_flag => 'Y',
3801 p_payroll_id => l_payroll_id);
3802 --
3803
3804 if l_enrt_info_rt_freq_cd = 'PPF' then
3805 l_element_type_id := l_abr.element_type_id;
3806 else
3807 l_element_type_id := null;
3808 end if;
3809 --
3810 l_cmcd_dflt_val := ben_distribute_rates.annual_to_period
3811 (p_amount =>l_cmcd_ann_dflt_val,
3812 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
3813 p_acty_ref_perd_cd => l_enrt_info_rt_freq_cd,
3814 p_business_group_id => l_epe.business_group_id,
3815 p_effective_date => l_effective_date,
3816 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
3817 p_complete_year_flag => 'Y',
3818 p_payroll_id => l_payroll_id,
3819 p_element_type_id => l_element_type_id);
3820 hr_utility.set_location ('HMANI '||l_cmcd_dflt_val,290.678);
3821 --
3822 END IF; --GEVITY
3823 end if;
3824
3825 --End of 3274902 hmani
3826
3827 end if;
3828 ---
3829
3830 p_cmcd_mn_elcn_val := l_cmcd_mn_val;
3831 p_cmcd_mx_elcn_val :=l_cmcd_mx_val;
3832 p_cmcd_val := l_cmcd_dflt_val;
3833 --
3834 -- For selfservice enhancement : Communicated values are required
3835 -- on self service pages.
3836 --
3837 end if;
3838 --
3839 -- end fix 2569884
3840 end if;
3841 --
3842 -- 3274902
3843 -- Cmcd_dflt_val is not getting calculated or returned since there is no
3844 -- parameter added to the benrates package.
3845 -- As a workaround to fix the issue with minimal code impact, p_iss_val paramter
3846 -- has been chosen and the l_cmcd_dflt val has been returned in the p_iss_val
3847 -- We here check that the mode is not CWB since CWB only uses iss_val
3848
3849 ben_env_object.get(p_rec => l_env);
3850 hr_utility.set_location(' ben_env_object: Mode ' || l_env.mode_cd, 10);
3851
3852 if l_env.mode_cd <> 'W' and p_iss_val is null then
3853 p_iss_val := l_cmcd_dflt_val ;
3854 end if;
3855
3856 -- End of 3274902
3857
3858
3859 -- set remaining outputs, variable rate outputs override abr outputs.
3860 --
3861 if l_abr.entr_val_at_enrt_flag = 'Y' then
3862 p_bnft_rt_typ_cd := l_abr.bnft_rt_typ_cd;
3863 p_rt_typ_cd := l_abr.rt_typ_cd;
3864 -- p_dflt_val := l_abr.dflt_val; -- 2445318
3865 else
3866 p_bnft_rt_typ_cd := null;
3867 p_rt_typ_cd := null;
3868 end if;
3869
3870 if l_vr_mn_elcn_val is not null then
3871 p_mn_elcn_val := l_vr_mn_elcn_val;
3872 end if;
3873
3874 if l_vr_mx_elcn_val is not null then
3875 p_mx_elcn_val := l_vr_mx_elcn_val;
3876 end if;
3877
3878 if l_vr_incrmt_elcn_val is not null then
3879 p_incrmt_elcn_val := l_vr_incrmt_elcn_val;
3880 end if;
3881 -- 2445318 Already handled
3882 --if l_vr_dflt_elcn_val is not null then
3883 -- p_dflt_val := l_vr_dflt_elcn_val;
3884 --end if;
3885
3886 if l_vr_tx_typ_cd is not null then
3887 p_tx_typ_cd := l_vr_tx_typ_cd;
3888 end if;
3889
3890 if l_vr_acty_typ_cd is not null then
3891 p_acty_typ_cd := l_vr_acty_typ_cd;
3892 end if;
3893
3894 if l_vr_ann_mn_elcn_val is not null then
3895 p_ann_mn_elcn_val := l_vr_ann_mn_elcn_val;
3896 --
3897 -- For selfservice enhancement : Communicated values are required
3898 -- on self service pages.
3899 --
3900 p_cmcd_mn_elcn_val := l_cmcd_mn_val;
3901
3902 end if;
3903
3904 if l_vr_ann_mx_elcn_val is not null then
3905 p_ann_mx_elcn_val := l_vr_ann_mx_elcn_val;
3906 --
3907 -- For selfservice enhancement : Communicated values are required
3908 -- on self service pages.
3909 --
3910 p_cmcd_mx_elcn_val := l_cmcd_mx_val;
3911 --
3912 end if;
3913 -- Bug 4637525
3914 --
3915 hr_utility.set_location('ACE l_abr.rt_mlt_cd = ' || l_abr.rt_mlt_cd , 9898);
3916 hr_utility.set_location('ACE l_env.mode_cd = ' || l_env.mode_cd , 9898);
3917 hr_utility.set_location('ACE l_abr.entr_val_at_enrt_flag = ' || l_abr.entr_val_at_enrt_flag , 9898);
3918 hr_utility.set_location('ACE l_abr.acty_typ_cd = ' || l_abr.acty_typ_cd , 9898);
3919 hr_utility.set_location('ACE l_compensation_value = ' || l_compensation_value , 9898);
3920 --
3921 if nvl(l_env.mode_cd,'~') = 'W' and
3922 l_abr.entr_val_at_enrt_flag = 'Y' and
3923 l_abr.rt_mlt_cd in ('CL' ) and
3924 l_abr.acty_typ_cd like 'CWB%' and
3925 l_compensation_value is not null and
3926 nvl(l_vr_trtmt_cd,' ') <> 'RPLC'
3927 then
3928 --
3929 benutils.rt_typ_calc
3930 (p_rt_typ_cd => l_abr.rt_typ_cd
3931 ,p_val => p_mn_elcn_val
3932 ,p_val_2 => l_compensation_value
3933 ,p_calculated_val => l_cwb_mn_elcn_val);
3934 --
3935 hr_utility.set_location('l_cwb_mn_elcn_val is '||l_cwb_mn_elcn_val,951);
3936 --
3937 --
3938 benutils.rt_typ_calc
3939 (p_rt_typ_cd => l_abr.rt_typ_cd
3940 ,p_val => p_mx_elcn_val
3941 ,p_val_2 => l_compensation_value
3942 ,p_calculated_val => l_cwb_mx_elcn_val);
3943 --
3944 hr_utility.set_location('l_cwb_mx_elcn_val is '|| l_cwb_mx_elcn_val,951);
3945 --
3946 -- 5371364 : Do not Operate (i.e multiply/divide..) the Increment Value.
3947 l_cwb_incrmt_elcn_val := p_incrmt_elcn_val;
3948 /*
3949 benutils.rt_typ_calc
3950 (p_rt_typ_cd => l_abr.rt_typ_cd
3951 ,p_val => p_incrmt_elcn_val
3952 ,p_val_2 => l_compensation_value
3953 ,p_calculated_val => l_cwb_incrmt_elcn_val);
3954 */
3955 --
3956
3957 hr_utility.set_location('l_cwb_incrmt_elcn_val is '|| l_cwb_incrmt_elcn_val,951);
3958 --
3959 benutils.rt_typ_calc
3960 (p_rt_typ_cd => l_abr.rt_typ_cd
3961 ,p_val => l_vr_dflt_elcn_val
3962 ,p_val_2 => l_compensation_value
3963 ,p_calculated_val => l_cwb_dflt_val);
3964 --
3965 hr_utility.set_location('l_cwb_dflt_val is '|| l_cwb_dflt_val,951);
3966 --
3967 --
3968 -- Round the values. Bug 4873847.
3969 --
3970 if (l_abr.rndg_cd is not null or
3971 l_abr.rndg_rl is not null) and
3972 p_perform_rounding_flg then
3973
3974 if l_cwb_mn_elcn_val is not null then
3975 l_rounded_value := benutils.do_rounding
3976 (p_rounding_cd => l_abr.rndg_cd,
3977 p_rounding_rl => l_abr.rndg_rl,
3978 p_value => l_cwb_mn_elcn_val,
3979 p_effective_date => l_effective_date);
3980 l_cwb_mn_elcn_val := l_rounded_value;
3981 end if;
3982 --
3983 if l_cwb_mx_elcn_val is not null then
3984 l_rounded_value := benutils.do_rounding
3985 (p_rounding_cd => l_abr.rndg_cd,
3986 p_rounding_rl => l_abr.rndg_rl,
3987 p_value => l_cwb_mx_elcn_val,
3988 p_effective_date => l_effective_date);
3989 l_cwb_mx_elcn_val := l_rounded_value;
3990 end if;
3991 --
3992 if l_cwb_dflt_val is not null then
3993 l_rounded_value := benutils.do_rounding
3994 (p_rounding_cd => l_abr.rndg_cd,
3995 p_rounding_rl => l_abr.rndg_rl,
3996 p_value => l_cwb_dflt_val,
3997 p_effective_date => l_effective_date);
3998 l_cwb_dflt_val := l_rounded_value;
3999 end if;
4000 end if;
4001 --
4002 p_mx_elcn_val := nvl(l_cwb_mx_elcn_val, p_mx_elcn_val);
4003 p_mn_elcn_val := nvl(l_cwb_mn_elcn_val, p_mn_elcn_val);
4004 p_incrmt_elcn_val := nvl(l_cwb_incrmt_elcn_val,p_incrmt_elcn_val);
4005 l_vr_dflt_elcn_val := nvl(l_cwb_dflt_val, l_vr_dflt_elcn_val );
4006 p_dflt_val := l_vr_dflt_elcn_val;
4007 --
4008 hr_utility.set_location('ACE p_mn_elcn_val = ' || p_mn_elcn_val , 9898);
4009 hr_utility.set_location('ACE p_mx_elcn_val = ' || p_mx_elcn_val , 9898);
4010 hr_utility.set_location('ACE p_incrmt_elcn_val = ' || p_incrmt_elcn_val , 9898);
4011 hr_utility.set_location('ACE l_vr_dflt_elcn_val = ' || l_vr_dflt_elcn_val , 9898);
4012 --
4013 end if;
4014 --
4015 -- Bug 4637525
4016
4017 if l_abr.entr_ann_val_flag = 'Y' then
4018 p_dsply_mn_elcn_val := nvl(l_vr_ann_mx_elcn_val,l_abr.ann_mn_elcn_val);
4019 p_dsply_mx_elcn_val := nvl(l_vr_ann_mx_elcn_val,l_abr.ann_mx_elcn_val);
4020 p_comp_lvl_fctr_id := l_abr.comp_lvl_fctr_id;
4021 else
4022 p_dsply_mn_elcn_val := p_mn_elcn_val;
4023 p_dsply_mx_elcn_val := p_mx_elcn_val;
4024 end if;
4025
4026 /* Bg 6015724 : Shifting this code part above
4027 --Bug 2677804. Use l_effective_date to resolve the open enrollment issue.
4028 if l_rt_ovridn_flag = 'Y' and
4029 nvl(l_rt_ovridn_thru_dt, hr_api.g_eot) >= l_effective_date then -- p_effective_date then
4030 p_val := l_rt_val;
4031 end if;
4032 */
4033
4034 hr_utility.set_location ('Leaving '||l_package,999);
4035 exception
4036 --
4037 when others then
4038 --
4039 p_val := null;
4040 p_mn_elcn_val := null;
4041 p_mx_elcn_val := null;
4042 p_ann_val := null;
4043 p_ann_mn_elcn_val := null;
4044 p_ann_mx_elcn_val := null;
4045 p_cmcd_val := null;
4046 p_cmcd_mn_elcn_val := null;
4047 p_cmcd_mx_elcn_val := null;
4048 p_cmcd_acty_ref_perd_cd := null;
4049 p_incrmt_elcn_val := null;
4050 p_dflt_val := null;
4051 p_tx_typ_cd := null;
4052 p_acty_typ_cd := null;
4053 p_nnmntry_uom := null;
4054 p_entr_val_at_enrt_flag := null;
4055 p_dsply_on_enrt_flag := null;
4056 p_use_to_calc_net_flx_cr_flag := null;
4057 p_rt_usg_cd := null;
4058 p_bnft_prvdr_pool_id := null;
4059 p_actl_prem_id := null;
4060 p_cvg_calc_amt_mthd_id := null;
4061 p_bnft_rt_typ_cd := null;
4062 p_rt_typ_cd := null;
4063 p_rt_mlt_cd := null;
4064 p_comp_lvl_fctr_id := null;
4065 p_entr_ann_val_flag := null;
4066 p_ptd_comp_lvl_fctr_id := null;
4067 p_clm_comp_lvl_fctr_id := null;
4068 p_ann_dflt_val := null;
4069 p_rt_strt_dt := null;
4070 p_rt_strt_dt_cd := null;
4071 p_rt_strt_dt_rl := null;
4072 p_prtt_rt_val_id := null;
4073 p_dsply_mn_elcn_val := null;
4074 p_dsply_mx_elcn_val := null;
4075 p_pp_in_yr_used_num := null;
4076 p_ordr_num := null;
4077
4078 raise;
4079
4080 end main;
4081
4082 --------------------------------------------------------------------------------
4083 -- Main_w
4084 --------------------------------------------------------------------------------
4085 PROCEDURE main_w
4086 (p_person_id IN number
4087 ,p_elig_per_elctbl_chc_id IN number
4088 ,p_enrt_bnft_id IN number default null
4089 ,p_acty_base_rt_id IN number
4090 ,p_effective_date IN date
4091 ,p_lf_evt_ocrd_dt IN date default null
4092 ,p_calc_only_rt_val_flag in varchar2 default 'N'
4093 ,p_pl_id in number default null
4094 ,p_oipl_id in number default null
4095 ,p_pgm_id in number default null
4096 ,p_pl_typ_id in number default null
4097 ,p_per_in_ler_id in number default null
4098 ,p_ler_id in number default null
4099 ,p_bnft_amt in number default null
4100 ,p_business_group_id in number default null
4101 ,p_val OUT NOCOPY number
4102 ,p_ann_val OUT NOCOPY number
4103 ,p_cmcd_val OUT NOCOPY number) is
4104
4105 l_package varchar2(80) := g_package||'.main_w';
4106
4107 l_calc_only_rt_val_flag boolean := false;
4108 l_dummy_char varchar2(200);
4109 l_dummy_num number;
4110 l_dummy_date date;
4111 l_element_type_id number;
4112 l_effective_date date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
4113 cursor c_enrt_rt is
4114 select ecr.rt_mlt_cd,
4115 ecr.enrt_rt_id,
4116 ecr.cmcd_acty_ref_perd_cd,
4117 enb.elig_per_elctbl_chc_id,
4118 ecr.acty_base_rt_id,
4119 ecr.business_group_id
4120 from ben_enrt_rt ecr,
4121 ben_enrt_bnft enb
4122 where enb.enrt_bnft_id = p_enrt_bnft_id
4123 and ecr.enrt_bnft_id = enb.enrt_bnft_id
4124 and ecr.acty_base_rt_id = p_acty_base_rt_id; --Bug 4143012 get the correct rate
4125 --
4126 cursor c_acty_base_rt is
4127 select element_type_id,rate_periodization_rl
4128 from ben_acty_base_rt_f abr
4129 where abr.acty_base_rt_id = p_acty_base_rt_id
4130 and l_effective_date between abr.effective_start_date
4131 and abr.effective_end_date;
4132 --
4133 cursor c_rt_strt_dt(p_enrt_rt_id NUMBER) is
4134 select enr.rt_strt_dt
4135 from ben_enrt_rt enr
4136 where enrt_rt_id = p_enrt_rt_id;
4137 l_rt_strt_dt date;
4138 --
4139
4140 l_enrt_rt c_enrt_rt%rowtype;
4141 l_global_asg_rec ben_global_enrt.g_global_asg_rec_type;
4142 --GEVITY
4143 l_assignment_id NUMBER;
4144 l_payroll_id NUMBER;
4145 l_organization_id NUMBER;
4146 l_rate_periodization_rl NUMBER;
4147 l_dfnd_dummy number;
4148 l_ann_dummy number;
4149 l_cmcd_dummy number;
4150 --END GEVITY
4151 l_trace_param varchar2(30);
4152 l_trace_on boolean;
4153 --
4154 begin
4155 l_trace_param := null;
4156 l_trace_on := false;
4157 --
4158 l_trace_param := fnd_profile.value('BEN_SS_TRACE_VALUE');
4159 --
4160 if l_trace_param = 'BENACTBR' then
4161 l_trace_on := true;
4162 else
4163 l_trace_on := false;
4164 end if;
4165 --
4166 if l_trace_on then
4167 hr_utility.trace_on(null,'BENACTBR');
4168 end if;
4169 --
4170 hr_utility.set_location('l_trace_param : '|| l_trace_param, 5);
4171 hr_utility.set_location ('Entering '||l_package,10);
4172 --
4173 ben_env_object.init(p_business_group_id => p_business_group_id,
4174 p_effective_date => p_effective_date,
4175 p_thread_id => 1,
4176 p_chunk_size => 1,
4177 p_threads => 1,
4178 p_max_errors => 1,
4179 p_benefit_action_id => null);
4180
4181 if p_calc_only_rt_val_flag = 'Y' then
4182 l_calc_only_rt_val_flag := true;
4183 end if;
4184 -- This procedure calls the date calculation routine to get the defined, annual
4185 -- and communicated rate values when a coverage value was entered in self-service.
4186
4187 ben_determine_activity_base_rt.main
4188 (p_person_id => p_person_id
4189 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
4190 ,p_enrt_bnft_id => p_enrt_bnft_id
4191 ,p_acty_base_rt_id => p_acty_base_rt_id
4192 ,p_effective_date => p_effective_date
4193 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
4194 ,p_perform_rounding_flg => true
4195 ,p_calc_only_rt_val_flag => l_calc_only_rt_val_flag -- to get cmcd, must be false.
4196 ,p_pl_id => p_pl_id -- when calc_only_rt_val_flag is false,
4197 ,p_oipl_id => p_oipl_id -- pl, oipl, pl_typ id's can be null.
4198 ,p_pgm_id => p_pgm_id
4199 ,p_pl_typ_id => p_pl_typ_id
4200 ,p_per_in_ler_id => p_per_in_ler_id
4201 ,p_ler_id => p_ler_id
4202 ,p_bnft_amt => p_bnft_amt
4203 ,p_business_group_id => p_business_group_id
4204 ,p_called_from_ss => true --6314463
4205 ,p_val => p_val
4206 ,p_mn_elcn_val => l_dummy_num
4207 ,p_mx_elcn_val => l_dummy_num
4208 ,p_ann_val => p_ann_val
4209 ,p_ann_mn_elcn_val => l_dummy_num
4210 ,p_ann_mx_elcn_val => l_dummy_num
4211 ,p_cmcd_val => p_cmcd_val
4212 ,p_cmcd_mn_elcn_val => l_dummy_num
4213 ,p_cmcd_mx_elcn_val => l_dummy_num
4214 ,p_cmcd_acty_ref_perd_cd => l_dummy_char
4215 ,p_incrmt_elcn_val => l_dummy_num
4216 ,p_dflt_val => l_dummy_num
4217 ,p_tx_typ_cd => l_dummy_char
4218 ,p_acty_typ_cd => l_dummy_char
4219 ,p_nnmntry_uom => l_dummy_char
4220 ,p_entr_val_at_enrt_flag => l_dummy_char
4221 ,p_dsply_on_enrt_flag => l_dummy_char
4222 ,p_use_to_calc_net_flx_cr_flag=> l_dummy_char
4223 ,p_rt_usg_cd => l_dummy_char
4224 ,p_bnft_prvdr_pool_id => l_dummy_num
4225 ,p_actl_prem_id => l_dummy_num
4226 ,p_cvg_calc_amt_mthd_id => l_dummy_num
4227 ,p_bnft_rt_typ_cd => l_dummy_char
4228 ,p_rt_typ_cd => l_dummy_char
4229 ,p_rt_mlt_cd => l_dummy_char
4230 ,p_comp_lvl_fctr_id => l_dummy_num
4231 ,p_entr_ann_val_flag => l_dummy_char
4232 ,p_ptd_comp_lvl_fctr_id => l_dummy_num
4233 ,p_clm_comp_lvl_fctr_id => l_dummy_num
4234 ,p_ann_dflt_val => l_dummy_num
4235 ,p_rt_strt_dt => l_dummy_date
4236 ,p_rt_strt_dt_cd => l_dummy_char
4237 ,p_rt_strt_dt_rl => l_dummy_num
4238 ,p_prtt_rt_val_id => l_dummy_num
4239 ,p_dsply_mn_elcn_val => l_dummy_num
4240 ,p_dsply_mx_elcn_val => l_dummy_num
4241 ,p_pp_in_yr_used_num => l_dummy_num
4242 ,p_ordr_num => l_dummy_num
4243 ,p_iss_val => l_dummy_num);
4244
4245 hr_utility.set_location ('Output Values From ben_determine_activity_base_rt.main '||l_package,900);
4246 hr_utility.set_location (' p_val: '||p_val,900);
4247 hr_utility.set_location (' p_ann_val: '||p_ann_val,900);
4248 hr_utility.set_location (' p_cmcd_val: '||p_cmcd_val,900);
4249
4250 --
4251 if p_enrt_bnft_id is not null then
4252 --
4253 open c_enrt_rt;
4254 fetch c_enrt_rt into l_enrt_rt;
4255 close c_enrt_rt;
4256 if l_enrt_rt.rt_mlt_cd = 'SAREC' then
4257 --
4258 open c_acty_base_rt;
4259 fetch c_acty_base_rt into l_element_type_id,l_rate_periodization_rl;
4260 close c_acty_base_rt;
4261 /* Replaced with ben_element_entry.get_abr_assignment call
4262 ben_global_enrt.get_asg -- assignment
4263 (p_person_id => p_person_id
4264 ,p_effective_date => l_effective_date -- Bug 2407041 p_effective_date
4265 ,p_global_asg_rec => l_global_asg_rec);
4266 */
4267 --
4268 ben_element_entry.get_abr_assignment
4269 (p_person_id => p_person_id
4270 ,p_effective_date => l_effective_date
4271 ,p_acty_base_rt_id => p_acty_base_rt_id
4272 ,p_organization_id => l_organization_id
4273 ,p_payroll_id => l_payroll_id
4274 ,p_assignment_id => l_assignment_id
4275 );
4276 --
4277 --GEVITY
4278 IF l_rate_periodization_rl IS NOT NULL THEN
4279 --
4280 ben_distribute_rates.periodize_with_rule
4281 (p_formula_id => l_rate_periodization_rl
4282 ,p_effective_date => l_effective_date
4283 ,p_assignment_id => l_assignment_id
4284 ,p_convert_from_val => p_bnft_amt
4285 ,p_convert_from => 'ANNUAL'
4286 ,p_elig_per_elctbl_chc_id => l_enrt_rt.elig_per_elctbl_chc_id
4287 ,p_acty_base_rt_id => l_enrt_rt.acty_base_rt_id
4288 ,p_business_group_id => l_enrt_rt.business_group_id
4289 ,p_enrt_rt_id => l_enrt_rt.enrt_rt_id
4290 ,p_ann_val => l_dfnd_dummy
4291 ,p_cmcd_val => p_cmcd_val
4292 ,p_val => l_dfnd_dummy
4293 );
4294 --
4295 ELSE
4296 -- 5534498
4297 open c_rt_strt_dt(l_enrt_rt.enrt_rt_id);
4298 fetch c_rt_strt_dt into l_rt_strt_dt;
4299 close c_rt_strt_dt;
4300 --
4301 p_cmcd_val := ben_distribute_rates.annual_to_period
4302 (p_amount => p_bnft_amt,
4303 p_enrt_rt_id => l_enrt_rt.enrt_rt_id,
4304 p_acty_ref_perd_cd => l_enrt_rt.cmcd_acty_ref_perd_cd,
4305 p_business_group_id => p_business_group_id,
4306 p_effective_date => p_effective_date,
4307 p_use_balance_flag => 'Y',
4308 p_payroll_id => l_payroll_id,
4309 p_element_type_id => l_element_type_id,
4310 -- 5534498
4311 p_person_id => p_person_id,
4312 p_start_date => l_rt_strt_dt
4313 );
4314 --
4315 END IF; --GEVITY
4316 --
4317 end if;
4318 --
4319 end if;
4320
4321 hr_utility.set_location ('Output Values: '||l_package,900);
4322 hr_utility.set_location (' p_val: '||p_val,900);
4323 hr_utility.set_location (' p_ann_val: '||p_ann_val,900);
4324 hr_utility.set_location (' p_cmcd_val: '||p_cmcd_val,900);
4325
4326 hr_utility.set_location ('Leaving '|| l_package,999);
4327 --
4328 if l_trace_on then
4329 hr_utility.trace_off;
4330 l_trace_param := null;
4331 l_trace_on := false;
4332 end if;
4333
4334 exception
4335 --
4336 when others then
4337 --
4338 p_val := null;
4339 p_ann_val := null;
4340 p_cmcd_val := null;
4341 fnd_msg_pub.add;
4342 if l_trace_on then
4343 hr_utility.trace_off;
4344 l_trace_on := false;
4345 l_trace_param := null;
4346 end if;
4347 end main_w;
4348 end ben_determine_activity_base_rt;