[Home] [Help]
PACKAGE BODY: APPS.BEN_DETERMINE_VARIABLE_RATES
Source
4 /*
1 Package Body BEN_DETERMINE_VARIABLE_RATES AS
2 /* $Header: benvrbrt.pkb 120.22 2011/08/09 05:01:29 amnaraya noship $ */
3 --------------------------------------------------------------------------------
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 Name
11 Determine Variable Rates
12 Purpose:
13 Determine Variable rates for activity base rates, coverages, and actl premiums.
14 This process establishes if variable rate profiles or variable rate rules are
15 used, if profiles are used call evaluate profile process to determine profile to
16 use, if rules are used call fast formula to return value, and finally passes
17 back the value information for the first profile/rule that passes.
18 History:
19 Date Who Version What?
20 ---- --- ------- -----
21 2 Jun 98 Ty Hayden 110.0 Created.
22 16 Jun 98 T Guy 110.1 Removed other exception.
23 18 Jun 98 Ty Hayden 110.2 Added p_rt_usg_cd
24 p_bnft_prvdr_pool_id
25 p_prtt_rt_val_id
26 to call of determine_acty_base_rt
27 25 Jun 98 T Guy 110.3 Replaced all occurrences of
28 PER10 with PERTEN.
29 25 Sep 98 T Guy 115.2 No change.
30 07 Oct 98 T Guy 115.3 Implemented schema changes for
31 ben_enrt_rt
32 19 Oct 98 T Guy 115.4 Fixed call to ben_determine_acty_
33 base_rt. Added p_ann_val
34 p_ann_mn_elcn_val
35 p_ann_mx_elcn_val
36 p_cmcd_val
37 p_cmcd_ann_mn_elcn_val
38 p_cmcd_ann_mx_elcn_val
39 p_cmcd_acty_ref_perd_cd
40 21 Oct 98 T Guy 115.5 added p_actl_prem_id
41 p_cvg_amt_cal_mthd_id
42 p_bnft_rt_typ_cd
43 p_rt_typ_cd
44 p_rt_mlt_cd
45 p_comp_lvl_fctr_id
46 p_entr_ann_val_flag
47 p_ptd_comp_lvl_fctr_id
48 p_ann_dflt_val
49 p_rt_strt_dt
50 p_rt_strt_dt_cd
51 p_rt_strt_dt_rl
52 to call for ben_determine_acty_base_rt
53 23 Oct 98 T Guy 115.6 added p_dsply_mn_elcn_val
54 p_dsply_mx_elcn_val
55 to call for ben_determine_acty_base_rt
56 28 Oct 98 G Perry 115.7 Uncommented exit statement.
57 05 Nov 98 T Guy 115.8 Changed comp_lvl_fctr_id logic to utilize
58 new column in ben_vrbl_rt_f.
62 rules
59 Removed PRNT mlt cd and added FLFXPCL
60 05 Nov 98 T Guy 115.9 No Change
61 12 Nov 98 T Guy 115.10 Implemented overall min/max val and
63 24 Nov 98 T Guy 115.11 Fixed upr/lwr undefined rule checking
64 03 Dec 98 T Guy 115.12 Fixed version numbers. We were out nocopy of
65 sync. 115.9 was missing.
66 22 Dec 98 T Guy 115.13 Removed FLENR mlt_cd and added
67 entr_val_at_enrt_flag
68 18 Jan 99 G Perry 115.14 LED V ED
69 09 Mar 99 G Perry 115.15 IS to AS.
70 28 Apr 99 Shdas 115.16 Added contexts to rule calls(genutils.formula).
71 27 May 99 maagrawa 115.17 Modified the procedure to be called without
72 chc_id and pass the reqd. parameters instead.
73 21 Jun 99 lmcdonal 115.18 Moved limit check code into its own
74 procedure.
75 1 Jul 99 T Guy 115.19 Made total premium changes
76 1 Jul 99 lmcdonal 115.20 Made use of genutils procs rt_typ_
77 calc and limit_checks.
78 16 Jul 99 lmcdonal 115.21 Don't call rules unless the rule
79 is found in c_ava.
80 115.22 load l_state.region_2 with null, change
81 limit_check parms.
82 20-JUL-99 Gperry 115.23 genutils -> benutils package
83 rename.
84 07-SEP-99 TGuy 115.24 fixed call to pay_mag_util
85 15-Nov-99 mhoyes 115.25 - Added trace messages.
86 17-Nov-99 pbodla 115.26 - Added acty_base_rt_id context to
87 formula, limit_checks calls.
88 This is only applicable for acty rates
89 calculation.
90 19-Nov-99 pbodla 115.27 - Added elig_per_elctbl_chc_id param
91 context to benutils.formula
92 02-DEC-99 pbodla 115.28 - If the rates are being calculated
93 for dummy imputed income plan then
94 do not raise error even if the
95 enrollment benefit row is not created
96 , let the enrt row have null val.
97 On enrollment rate is recalculated.
98 22-FEB-00 mmogel 115.29 Fixed bug 1197534 (added c_pgm and
99 c_pln cursors to determine what
100 acty_ref_perd the actual premium
101 should be based on and then calcu-
102 lated the actl_prem appropriately)
103 31-MAR-00 mmogel 115.30 - added tokens to the messages so that
104 they are more meaningful to the user
105 29-May-00 mhoyes 115.31 - Added record parameters to main.
106 28-Jun-00 mhoyes 115.32 - Added p_currepe_row to main.
107 - Bypassed c_epe with p_currepe_row
108 values when p_currepe_row is set.
109 07-Nov-00 mhoyes 115.33 - Referenced electable choice context
110 global.
111 21-mar-01 tilak 115-34 param ultmt_upt_lmt,ultmt_lwr_lmt added
112 01-apr-01 tilak 115-35 param ultmt_upt_lmt_calc_rl,ultmt_lwr_lmt_calc_rl added
113 17-jul-01 tilak 115-36 cursor c_imp_inc_plan corrected for spouse code
114 29-Aug-01 pbodla 115-37 bug:1949361 jurisdiction code is
115 derived inside benutils.formula
116 27-Sep-01 kmahendr 115.38 Added ann_mn_elcn_val, ann_mx_elcn_val param
117 26-Mar-02 kmahendr 115.39 Bug#1833008 - Added parameter p_cal_for to the call
118 determine_compensation to handle multi-assignment.
119 26-Mar-02 pbodla 115.40 Bug#2260440 - While calling limit
120 checks if assignment id is null
121 fetch it and pass, as formulas
122 may need it.
123 08-Jun-02 pabodla 115.41 Do not select the contingent worker
124 assignment when assignment data is
125 fetched.
126 04-Sep-02 kmahendr 115.42 Added new acty_ref_perd_cd - PHR.
127 28-Oct-02 shdas 115.43 bug fix 2644319 -- initialize l_coverage_value before
128 everything.
129 13-Nov-2002 vsethi 115.44 Bug 1210355, if variable rate is calculated, store
130 mlt_code in g_vrbl_mlt_code. The rates mlt_cd should
131 be changed to mlt_cd of variable profile.
132 23-Dec-2002 rpgupta 115.45 Nocopy changes
133 09-Apr-2004 pbodla 115.46 FONM : Use rt or cvg start dates for
134 processing.
135 13-Aug-2004 tjesumic 115.47 FONM : dates are passed as param
136 30-dec-2004 nhunur 115.48 4031733 - No need to open cursor c_state.
140 and then others .
137 28-Jun-2005 kmahendr 115.49 Bug#4422269 - nvl used to get default
138 value of enrt bnft
139 03-Oct-05 ssarkar 115.50 4644867 - Added order by clause to cursoe c_asg to Query 'E' assignment first
141 07-oct-05 nhunur 115.51 4657978 - added support for ben_actl_prem_vrbl_rt_rl_f.
142 02-Feb-06 stee 115.52 Bug 4873847. CWB: If treatment code is RPLC,
143 calculate the dflt_val, elcn_mn_val and elcn_mx_val if
144 enter val at enrollment and mlt_cd is multiple of
145 compensation. Also round the values if rounding rule
146 or code is not null.
147 10-Mar-06 swjain 115.54 In cursor c_asg (procedure main), added condition to
148 fetch active assignments only
149 10-Apr-06 swjain 115.55 Updated cursor c_asg (procedure main)
150 09-Aug-06 maagrawa 115.56 5371364.Copied fix from benactbr
151 16-Jul-07 swjain 115.57 6219465 Updated the effective_date in benutils.formula call
152 so that rule effective the latest coverage date should get
153 picked up for imputed income calculations
154 14-Sep-07 rtagarra 115.58 Bug 6399423 removed the outer join in the cursor c_asg
155 12-May-08 dwkrishn 115.59 Bug 7003453 Recalculated the premium if the coverage is
156 Enterable at enrollment
157 27-Aug-08 bachakra 115.60 Bug 7331668 If treatment code is RPLC,
158 enter val at enrollment is 'Y' and mlt_cd is multiple of
159 compensation, then assign dflt_val to p_val.
160 20-Jan-09 stee 115.61 7728455 Remove fix for bug 6399423. Cursor c_asg
161 should include other assignment statuses.
162 11-Feb-2009 velvanop 115.62 Bug 7414757: Added parameter p_entr_val_at_enrt_flag.
163 VAPRO rates which are 'Enter value at Enrollment', Form field
164 should allow the user to enter a value during enrollment.
165 01-Feb-2010 velvanop 115.63 Bug 9306764 : VAPRO rates which are 'Enter value at Enrollment',
166 Default value for the rate should be taken from the VAPRO.
167 24-Dec-2010 sagnanas 115.64 Bug 10407950: Fixed cursor c_asg to pick assignment type 'A'
168 to fix IREC issue
169 07-Jan-2010 sagnanas 115.65 Bug 10629555: Fixed cursor c_asg to pick the correct applicant
170 assignment for the correct job when a person has multiple
171 applicant assignments on the same date
172 20-Jan-2010 sagnanas 115.66 Bug 10649969: Fixed cursor c_asg to pick the correct applicant
173 assignment for the internal and international transfer
174 27-Apr-2010 sagnanas 115.67 Bug 12395523: For FLFXPCL code, pass the multiplier and add
175 the flat amount
176 28-Apr-2010 sagnanas 115.68 Corrected checkfile to get rid of gscc error
177 25-May-2011 sagnanas 115.69 Bug 12428282: Fixed cursor c_asg to pick the correct assignment
178 09-Aug-2011 amnaraya 115.70 Bug 12810996 : Fixed the cursor c_asg to fetch only one ler.typ_cd
179 */
180 --------------------------------------------------------------------------------
181 --
182 g_package varchar2(80) := 'ben_determine_variable_rates';
183 --
187 PROCEDURE main
184 --------------------------------------------------------------------------------
185 --------------------------------- main -----------------------------------------
186 --------------------------------------------------------------------------------
188 (p_currepe_row in ben_determine_rates.g_curr_epe_rec
189 := ben_determine_rates.g_def_curr_epe_rec
190 ,p_per_row in per_all_people_F%rowtype
191 := ben_determine_rates.g_def_curr_per_rec
192 ,p_asg_row in per_all_assignments_f%rowtype
193 := ben_determine_rates.g_def_curr_asg_rec
194 ,p_ast_row in per_assignment_status_types%rowtype
195 := ben_determine_rates.g_def_curr_ast_rec
196 ,p_adr_row in per_addresses%rowtype
197 := ben_determine_rates.g_def_curr_adr_rec
198 ,p_person_id IN number
199 ,p_elig_per_elctbl_chc_id IN number
200 ,p_enrt_bnft_id IN number default null
201 ,p_actl_prem_id IN number default null --\
202 ,p_acty_base_rt_id IN number default null -- Only one of these 3 have val.
203 ,p_cvg_amt_calc_mthd_id IN number default null --/
204 ,p_effective_date IN date
205 ,p_lf_evt_ocrd_dt IN date
209 ,p_oipl_id in number default null
206 ,p_calc_only_rt_val_flag in boolean default false
207 ,p_pgm_id in number default null
208 ,p_pl_id in number default null
210 ,p_pl_typ_id in number default null
211 ,p_per_in_ler_id in number default null
212 ,p_ler_id in number default null
213 ,p_business_group_id in number default null
214 ,p_bnft_amt in number default null
215 ,p_entr_val_at_enrt_flag in out nocopy varchar2 -- Added parameter for Bug 7414757
216 ,p_val out nocopy number
217 ,p_mn_elcn_val out nocopy number
218 ,p_mx_elcn_val out nocopy number
219 ,p_incrmnt_elcn_val out nocopy number
220 ,p_dflt_elcn_val out nocopy number
221 ,p_tx_typ_cd out nocopy varchar2
222 ,p_acty_typ_cd out nocopy varchar2
223 ,p_vrbl_rt_trtmt_cd out nocopy varchar2
224 ,p_ultmt_upr_lmt out nocopy number
225 ,p_ultmt_lwr_lmt out nocopy number
226 ,p_ultmt_upr_lmt_calc_rl out nocopy number
227 ,p_ultmt_lwr_lmt_calc_rl out nocopy number
228 ,p_ann_mn_elcn_val out nocopy number
229 ,p_ann_mx_elcn_val out nocopy number
230
231 )
232 IS
233 --
234 l_package varchar2(80) := g_package||'.main';
235 --
236 l_vrbl_rt_prfl_id number;
237 l_outputs ff_exec.outputs_t;
238 l_compensation_value number;
239 l_coverage_value number := null;
240 l_actl_prem_value number;
241 l_prnt_rt_value number;
242 l_value number;
243 l_comp_lvl_fctr_id number;
244 l_actl_prem_id number;
245 l_acty_base_rt_id number;
246 l_acty_ref_perd_cd varchar2(30);
247 l_dummy_num number;
248 l_dummy_char varchar2(30);
249 l_dummy_date date;
250 l_avr_found boolean default false;
251 l_apv_found boolean default false;
252 l_bvr_found boolean default false;
253 l_rounded_value number;
254 l_jurisdiction_code varchar2(30);
255 --
256 -- Bug 4873847
257 --
258 l_cwb_dflt_val number;
259 l_cwb_incrmt_elcn_val number;
260 l_cwb_mx_elcn_val number;
261 l_cwb_mn_elcn_val number;
262 --
263 -- End Bug 4873847
264 --
265 -- FONM
266 cursor c_asg(cv_effective_date date) is
267 select asg.assignment_id,asg.organization_id
268 from per_all_assignments_f asg, per_assignment_status_types ast
269 where asg.person_id = p_person_id
270 and ((asg.assignment_type <> 'C'
271 and asg.primary_flag = 'Y'
272 and 'IREC' <> ( select ler.typ_cd
273 from ben_ler_f ler, ben_per_in_ler pil, ben_elig_per_elctbl_chc epe
274 where epe.per_in_ler_id = pil.per_in_ler_id
275 and pil.ler_id = ler.ler_id
276 and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
277 and cv_effective_date between ler.effective_start_date and ler.effective_end_date--12810996
278 and ler.business_group_id = epe.business_group_id))--10649969
279 or (asg.assignment_type = 'A'
280 and asg.primary_flag = 'N'
281 and ben_manage_life_events.g_irec_old_ass_rec.assignment_id = asg.assignment_id)--12428282
282 and asg.business_group_id = (select business_group_id
283 from ben_elig_per_elctbl_chc
284 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id )) --10649969
285 and asg.assignment_status_type_id = ast.assignment_status_type_id(+)
286 and ast.per_system_status(+) = 'ACTIVE_ASSIGN' -- Bug 6399423 removed the outer join
287 -- Bug 7728455 Added back the outer join
288 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
289 between asg.effective_start_date
290 and asg.effective_end_date
291 order by assignment_type desc, effective_start_date desc; -- BUG 4644867
292 --
293 l_asg c_asg%rowtype;
294 --
295 -- FONM
296 cursor c_avr(cv_effective_date date) is
297 select avr.acty_vrbl_rt_id
298 from ben_acty_vrbl_rt_f avr
299 where avr.acty_base_rt_id = p_acty_base_rt_id
300 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
301 between avr.effective_start_date
302 and avr.effective_end_date;
303 --
304 l_avr c_avr%rowtype;
305 --
306 --
307 --
308 -- FONM
309 cursor c_avrl(cv_effective_date date) is
310 select avr.formula_id, avr.RT_TRTMT_CD
311 from ben_actl_prem_vrbl_rt_rl_f avr
312 where avr.actl_prem_id = p_actl_prem_id
313 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
314 between avr.effective_start_date
315 and avr.effective_end_date;
316 --
317 l_avrl c_avrl%rowtype;
318 -- FONM
319 --
320 cursor c_vrr(cv_effective_date date) is
321 select vrr.formula_id
322 from ben_vrbl_rt_rl_f vrr
323 where vrr.acty_base_rt_id = p_acty_base_rt_id
324 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
325 between vrr.effective_start_date
326 and vrr.effective_end_date
327 order by vrr.ordr_to_aply_num;
328 --
329 -- FONM
330 cursor c_apv(cv_effective_date date) is
331 select apv.actl_prem_vrbl_rt_id
332 from ben_actl_prem_vrbl_rt_f apv
333 where apv.actl_prem_id = p_actl_prem_id
334 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
335 between apv.effective_start_date
336 and apv.effective_end_date;
337 --
338 l_apv c_apv%rowtype;
339 --
340 -- FONM
344 where ava.actl_prem_id = p_actl_prem_id
341 cursor c_ava(cv_effective_date date) is
342 select ava.vrbl_rt_add_on_calc_rl
343 from ben_actl_prem_f ava
345 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
346 between ava.effective_start_date
347 and ava.effective_end_date;
348 --
349 l_ava c_ava%rowtype;
350 --
351 -- FONM
352 cursor c_bvr(cv_effective_date date) is
353 select bvr.bnft_vrbl_rt_id
354 from ben_bnft_vrbl_rt_f bvr
355 where bvr.cvg_amt_calc_mthd_id = p_cvg_amt_calc_mthd_id
356 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
357 between bvr.effective_start_date
358 and bvr.effective_end_date;
359 --
360 l_bvr c_bvr%rowtype;
361 --
362 -- FONM
363 cursor c_brr(cv_effective_date date) is
364 select brr.formula_id
365 from ben_bnft_vrbl_rt_rl_f brr
366 where brr.cvg_amt_calc_mthd_id = p_cvg_amt_calc_mthd_id
367 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
368 between brr.effective_start_date
369 and brr.effective_end_date
370 order by brr.ordr_to_aply_num;
371 --
372 -- FONM
373 cursor c_vpf(cv_effective_date date) is
374 select vpf.val,
375 vpf.val_calc_rl,
376 vpf.mx_elcn_val,
377 vpf.mn_elcn_val,
378 vpf.dflt_elcn_val,
379 vpf.incrmnt_elcn_val,
380 vpf.mlt_cd,
381 vpf.acty_typ_cd,
382 vpf.rt_typ_cd,
383 vpf.bnft_rt_typ_cd,
384 vpf.tx_typ_cd,
385 vpf.vrbl_rt_trtmt_cd,
386 vpf.comp_lvl_fctr_id,
387 vpf.lwr_lmt_val,
388 vpf.lwr_lmt_calc_rl,
389 vpf.upr_lmt_val,
390 vpf.upr_lmt_calc_rl,
391 vpf.rndg_cd,
392 vpf.rndg_rl,
393 vpf.ultmt_upr_lmt,
394 vpf.ultmt_lwr_lmt,
395 vpf.ultmt_upr_lmt_calc_rl,
396 vpf.ultmt_lwr_lmt_calc_rl,
397 vpf.ann_mn_elcn_val,
398 vpf.ann_mx_elcn_val,
399 vpf.no_mn_elcn_val_dfnd_flag
400 from ben_vrbl_rt_prfl_f vpf
401 where vpf.vrbl_rt_prfl_id = l_vrbl_rt_prfl_id
402 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
403 between vpf.effective_start_date
404 and vpf.effective_end_date;
405 --
406 l_vpf c_vpf%rowtype;
407 --
408 cursor c_epe
409 is
410 select epe.pl_id,
411 epe.pl_typ_id,
412 epe.oipl_id,
413 epe.pgm_id,
414 epe.business_group_id,
415 epe.per_in_ler_id,
416 pil.ler_id
417 from ben_elig_per_elctbl_chc epe,ben_per_in_ler pil
418 where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
419 and epe.per_in_ler_id = pil.per_in_ler_id;
420 --
421 -- FONM
422 cursor c_opt(l_oipl_id number, cv_effective_date date) is
423 select opt_id
424 from ben_oipl_f oipl
425 where oipl_id = l_oipl_id
426 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
427 between oipl.effective_start_date
428 and oipl.effective_end_date;
429 l_epe c_epe%rowtype;
430 l_opt c_opt%rowtype;
431 --
432 -- FONM
433 Cursor c_state(cv_effective_date date) is
434 select loc.region_2
435 from hr_locations_all loc,per_all_assignments_f asg
436 where loc.location_id = asg.location_id
437 and asg.person_id = p_person_id
438 and asg.assignment_type <> 'C'
439 and asg.primary_flag = 'Y'
440 and cv_effective_date -- FONM p_effective_date
441 between
442 asg.effective_start_date and asg.effective_end_date;
443
444 l_state c_state%rowtype;
445
446 -- FONM
447 cursor c_apr2(cv_effective_date date) is
448 select apr.actl_prem_id
449 from ben_actl_prem_f apr,
450 ben_pl_f pln,
451 ben_oipl_f cop
452 where ((pln.pl_id = l_epe.pl_id
453 and pln.actl_prem_id = apr.actl_prem_id
454 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
455 between pln.effective_start_date
456 and pln.effective_end_date)
457 or (cop.oipl_id = l_epe.oipl_id
458 and cop.actl_prem_id = apr.actl_prem_id
459 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
460 between cop.effective_start_date
461 and cop.effective_end_date))
462 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
463 between apr.effective_start_date
464 and apr.effective_end_date;
465 --
466 -- FONM
467 cursor c_pln(cv_effective_date date) is
468 select pln.nip_acty_ref_perd_cd
469 from ben_pl_f pln
470 where pln.pl_id = l_epe.pl_id
471 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
472 between pln.effective_start_date
473 and pln.effective_end_date;
474
475 l_pln c_pln%rowtype;
476
477 -- FONM
478 cursor c_pgm(cv_effective_date date) is
479 select pgm.acty_ref_perd_cd
480 from ben_pgm_f pgm
481 where pgm.pgm_id = l_epe.pgm_id
482 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
483 between pgm.effective_start_date
484 and pgm.effective_end_date;
485
486 l_pgm c_pgm%rowtype;
487 --
488 cursor c_enb is
489 select nvl(enb.val,enb.dflt_val) -- used nvl to compute value based on default val
490 from ben_enrt_bnft enb
494 cursor c_abr(cv_effective_date date) is
491 where enb.enrt_bnft_id = p_enrt_bnft_id;
492 --
493 -- FONM
495 select abr2.acty_base_rt_id
496 from ben_acty_base_rt_f abr,
497 ben_paird_rt_f prd,
498 ben_acty_base_rt_f abr2
499 where abr.acty_base_rt_id = p_acty_base_rt_id
500 and abr.acty_base_rt_id = prd.chld_acty_base_rt_id
501 and abr2.acty_base_rt_id = prd.parnt_acty_base_rt_id
502 and abr2.parnt_chld_cd = 'PARNT'
503 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
504 between abr.effective_start_date
505 and abr.effective_end_date
506 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
507 between prd.effective_start_date
508 and prd.effective_end_date
509 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
510 between abr2.effective_start_date
511 and abr2.effective_end_date
512 and rownum = 1;
513 --
514 cursor c_prem_abr(cv_effective_date date) is
515 select abr.actl_prem_id
516 from ben_acty_base_rt_f abr
517 where abr.acty_base_rt_id = p_acty_base_rt_id
518 and cv_effective_date between abr.effective_start_date and
519 abr.effective_end_date;
520 --
521 cursor c_enrt_prem is
522 select ecr.val
523 from ben_enrt_prem ecr,
524 ben_per_in_ler pil,
525 ben_elig_per_elctbl_chc epe
526 where ecr.actl_prem_id = l_actl_prem_id
527 and ecr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
528 and epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id
529 and pil.per_in_ler_id = epe.per_in_ler_id
530 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
531 --
532 -- FONM
533 cursor c_imp_inc_plan(p_pl_id in number, cv_effective_date date) is
534 select 'Y'
535 from ben_pl_f pln
536 where pln.imptd_incm_calc_cd in ('PRTT', 'SPS', 'DPNT') and
537 pln.pl_stat_cd = 'A' and
538 pln.pl_id = p_pl_id and
539 pln.business_group_id = p_business_group_id and
540 cv_effective_date -- FONM p_effective_date
541 between pln.effective_start_date and
542 pln.effective_end_date;
543
544 --
545 -- bug 7003453
546 cursor c_entr_at_enrt_flag is
547 select ENTR_VAL_AT_ENRT_FLAG
548 from BEN_CVG_AMT_CALC_MTHD_f
549 WHERE (pl_id = p_pl_id
550 or oipl_id = p_oipl_id)
551 and p_effective_date
552 between effective_start_date
553 and effective_end_date;
554
555 l_entr_at_enrt_flag ben_cvg_amt_calc_mthd_f.entr_val_at_enrt_flag%type;
556
557 --end bug 7003453
558 l_imp_inc_plan varchar2(1) := 'N';
559 l_pay_annualization_factor number;
560 --
561 -- FONM
562 l_fonm_cvg_strt_dt date;
563 --
564 -- Bug 4873847
565 --
566 l_env ben_env_object.g_global_env_rec_type;
567 l_mode l_env.mode_cd%TYPE;
568 --
569 -- End Bug 4873847
570 --
571 BEGIN
572 --
573 hr_utility.set_location ('Entering '||l_package,10);
574 --
575 ben_env_object.get(p_rec => l_env);
576 --
577 If (p_person_id is null) then
578 --
579 fnd_message.set_name('BEN','BEN_91554_BENVRBRT_INPT_PRSN');
580 fnd_message.set_token('PACKAGE',l_package);
581 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
582 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
583 fnd_message.set_token('PL_ID',to_char(p_pl_id));
584 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
585 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
586 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
587 fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID',to_char(p_elig_per_elctbl_chc_id));
588 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
589 fnd_message.set_token('ACTL_PREM_ID',to_char(p_actl_prem_id));
590 fnd_message.set_token('ACTY_BASE_RT_ID',to_char(p_acty_base_rt_id));
591 fnd_message.set_token('CVG_AMT_CALC_MTHD_ID',to_char(p_cvg_amt_calc_mthd_id));
592 fnd_message.raise_error;
593 --
594 end if;
595 --
596 -- Edit to insure that the input p_effective_date has a value
597 If (p_effective_date is null) then
598 --
599 fnd_message.set_name('BEN','BEN_91555_BENVRBRT_INPT_EFFDT');
600 fnd_message.set_token('PACKAGE',l_package);
601 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
602 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
603 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
604 fnd_message.set_token('PL_ID',to_char(p_pl_id));
605 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
606 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
607 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
608 fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID',to_char(p_elig_per_elctbl_chc_id));
609 fnd_message.set_token('ACTL_PREM_ID',to_char(p_actl_prem_id));
610 fnd_message.set_token('ACTY_BASE_RT_ID',to_char(p_acty_base_rt_id));
611 fnd_message.set_token('CVG_AMT_CALC_MTHD_ID',to_char(p_cvg_amt_calc_mthd_id));
612 fnd_message.raise_error;
613 --
614 end if;
615 --
616 -- Edit to insure that the input p_elig_per_elctbl_chc_id has a value
617 If (p_elig_per_elctbl_chc_id is null) and not(p_calc_only_rt_val_flag) then
618 --
619 fnd_message.set_name('BEN','BEN_91556_BENVRBRT_INPT_EC');
620 fnd_message.set_token('PACKAGE',l_package);
621 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
622 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
626 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
623 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
624 fnd_message.set_token('PL_ID',to_char(p_pl_id));
625 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
627 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
628 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
629 fnd_message.set_token('ACTL_PREM_ID',to_char(p_actl_prem_id));
630 fnd_message.set_token('ACTY_BASE_RT_ID',to_char(p_acty_base_rt_id));
631 fnd_message.set_token('CVG_AMT_CALC_MTHD_ID',to_char(p_cvg_amt_calc_mthd_id));
632 fnd_message.raise_error;
633 --
634 end if;
635 --
636 -- Edit to ensure that one of the base table ids has a value
637 If (p_acty_base_rt_id is null and
638 p_actl_prem_id is null and
639 p_cvg_amt_calc_mthd_id is null) then
640 --
641 fnd_message.set_name('BEN','BEN_91557_BENVRBRT_INPT_BT');
642 fnd_message.set_token('PACKAGE',l_package);
643 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
644 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
645 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
646 fnd_message.set_token('PL_ID',to_char(p_pl_id));
647 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
648 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
649 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
650 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
651 fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID',to_char(p_elig_per_elctbl_chc_id));
652 fnd_message.raise_error;
653 --
654 end if;
655 -- bug 1210355
656 g_vrbl_mlt_code := null;
657
658 --
659 -- FONM variables initialization
660 --
661 if ben_manage_life_events.fonm = 'Y' then
662 --
663 l_fonm_cvg_strt_dt := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
664 ben_manage_life_events.g_fonm_cvg_strt_dt);
665 --
666 end if;
667 --
668 l_coverage_value := p_bnft_amt;
669 if p_calc_only_rt_val_flag then
670 --
671 l_epe.pl_id := p_pl_id;
672 l_epe.pgm_id := p_pgm_id;
673 l_epe.pl_typ_id := p_pl_typ_id;
674 l_epe.oipl_id := p_oipl_id;
675 l_epe.per_in_ler_id := p_per_in_ler_id;
676 l_epe.ler_id := p_ler_id;
677 l_epe.business_group_id := p_business_group_id;
678 --
679 --
680 -- Check if the context global is populated
681 --
682 elsif ben_epe_cache.g_currepe_row.elig_per_elctbl_chc_id is not null
683 then
684 --
685 l_epe.pgm_id := ben_epe_cache.g_currepe_row.pgm_id;
686 l_epe.pl_typ_id := ben_epe_cache.g_currepe_row.pl_typ_id;
687 l_epe.pl_id := ben_epe_cache.g_currepe_row.pl_id;
688 l_epe.oipl_id := ben_epe_cache.g_currepe_row.oipl_id;
689 l_epe.business_group_id := ben_epe_cache.g_currepe_row.business_group_id;
690 l_epe.per_in_ler_id := ben_epe_cache.g_currepe_row.per_in_ler_id;
691 l_epe.ler_id := ben_epe_cache.g_currepe_row.ler_id;
692 --
693 --
694 -- Check if the context row is populated
695 --
696 elsif p_currepe_row.elig_per_elctbl_chc_id is not null
697 then
698 --
699 l_epe.pgm_id := p_currepe_row.pgm_id;
700 l_epe.pl_typ_id := p_currepe_row.pl_typ_id;
701 l_epe.pl_id := p_currepe_row.pl_id;
702 l_epe.oipl_id := p_currepe_row.oipl_id;
703 l_epe.business_group_id := p_currepe_row.business_group_id;
704 l_epe.per_in_ler_id := p_currepe_row.per_in_ler_id;
705 l_epe.ler_id := p_currepe_row.ler_id;
706 --
707 else
708 --
709 open c_epe;
710 --
711 fetch c_epe into l_epe;
712 --
713 if c_epe%notfound then
714 --
715 close c_epe;
716 fnd_message.set_name('BEN','BEN_91558_BENVRBRT_EPE_NF');
717 fnd_message.set_token('PACKAGE',l_package);
718 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
719 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
720 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
721 fnd_message.raise_error;
722 --
723 end if;
724 --
725 close c_epe;
726 --
727 end if;
728 --
729 hr_utility.set_location(l_package||' p_abr_id NN ',10);
730 if p_acty_base_rt_id is not null then
731 --
732 -- FONM
733 open c_avr(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)))
734 ;
735 --
736 fetch c_avr into l_avr;
737 --
738 if c_avr%found then
739 --
740 l_avr_found := true;
741 --
742 open c_prem_abr(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
743 fetch c_prem_abr into l_actl_prem_id;
744 close c_prem_abr;
745 --
746 end if;
747 --
748 close c_avr;
749 --
750 end if;
751 --
752 hr_utility.set_location(l_package||' p_aprm_id NN ',10);
753 if p_actl_prem_id is not null then
754 --
755 l_actl_prem_id := p_actl_prem_id;
756 --
757 open c_apv(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
758 --
759 fetch c_apv into l_apv;
760 --
761 if c_apv%found then
762 --
763 l_apv_found := true;
764 --
765 end if;
766 --
767 close c_apv;
768 --
769 end if;
770 --
771 hr_utility.set_location(l_package||' p_cacm_id NN ',10);
775 --
772 if p_cvg_amt_calc_mthd_id is not null then
773 --
774 open c_bvr(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
776 fetch c_bvr into l_bvr;
777 --
778 if c_bvr%found then
779 --
780 l_bvr_found := true;
781 --
782 end if;
783 --
784 close c_bvr;
785 --
786 end if;
787 --
788 -- if one of these are found, then we are dealing with profiles, not rules.
789 --
790 hr_utility.set_location(l_package||' l_avr_found OR ',10);
791 if l_avr_found or l_apv_found or l_bvr_found then
792 --
793 hr_utility.set_location(l_package||' BERP_MN ',10);
794 ben_evaluate_rate_profiles.main
795 (p_currepe_row => p_currepe_row
796 ,p_per_row => p_per_row
797 ,p_asg_row => p_asg_row
798 ,p_ast_row => p_ast_row
799 ,p_adr_row => p_adr_row
800 ,p_person_id => p_person_id
801 ,p_acty_base_rt_id => p_acty_base_rt_id
802 ,p_actl_prem_id => p_actl_prem_id
803 ,p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id
804 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
805 ,p_effective_date => p_effective_date -- FONM : benrtprf handles it.
806 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
807 ,p_calc_only_rt_val_flag => p_calc_only_rt_val_flag
808 ,p_pgm_id => l_epe.pgm_id
809 ,p_pl_id => l_epe.pl_id
810 ,p_pl_typ_id => l_epe.pl_typ_id
811 ,p_oipl_id => l_epe.oipl_id
812 ,p_per_in_ler_id => l_epe.per_in_ler_id
813 ,p_ler_id => l_epe.ler_id
814 ,p_business_group_id => l_epe.business_group_id
815 ,p_vrbl_rt_prfl_id => l_vrbl_rt_prfl_id
816 ); -- this is output
817 hr_utility.set_location(l_package||' Dn BERP_MN ',10);
818 --
819 -- when l_vrbl_rt_prfl_id has no value in it, the person failed the criteria
820 -- and get out! So much for one exit point!
821 --
822 if l_vrbl_rt_prfl_id is null then
823 --
824 return;
825 --
826 end if;
827 --
828 -- All profile criteria was met!
829 --
830 if l_epe.oipl_id is not null then
831 open c_opt(l_epe.oipl_id, nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
832 fetch c_opt into l_opt;
833 close c_opt;
834 end if;
835 --
836 open c_vpf(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
837 --
838 fetch c_vpf into l_vpf;
839 --
840 if c_vpf%notfound then
841 --
842 close c_vpf;
843 fnd_message.set_name('BEN','BEN_91559_BENVRBRT_VPF_NF');
844 fnd_message.set_token('PACKAGE',l_package);
845 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
846 fnd_message.set_token('PGM_ID',to_char(l_epe.pgm_id));
847 fnd_message.set_token('PL_TYP_ID',to_char(l_epe.pl_typ_id));
848 fnd_message.set_token('PL_ID',to_char(l_epe.pl_id));
849 fnd_message.set_token('OIPL_ID',to_char(l_epe.oipl_id));
850 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
851 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
852 fnd_message.set_token('PER_IN_LER_ID',to_char(l_epe.per_in_ler_id));
853 fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID',
854 to_char(p_elig_per_elctbl_chc_id));
855 fnd_message.raise_error;
856 --
857 end if;
858 --
859 close c_vpf;
860 p_entr_val_at_enrt_flag := l_vpf.no_mn_elcn_val_dfnd_flag; -- Bug 7414757
861 hr_utility.set_location(l_package||' close c_vpf ',10);
862 --
863 if l_vpf.mlt_cd in ('CVG','CLANDCVG','APANDCVG','PRNTANDCVG') then
864 --
865 -- If the rates are being calculated for dummy imputed
866 -- income plan then do not raise error even if the
867 -- enrollment benefit row is not created, let the enrt row have
868 -- null val. On enrollment rate is recalculated anyway.
869 --
870 hr_utility.set_location(' plan id ' || p_pl_iD , 99);
871 open c_imp_inc_plan(l_epe.pl_id,
872 nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
873 fetch c_imp_inc_plan into l_imp_inc_plan;
874 close c_imp_inc_plan;
875 --
876 hr_utility.set_location(' imp_inc_plan ' || l_imp_inc_plan , 99);
877 if l_imp_inc_plan = 'N' then
878 --
879 if l_coverage_value is null then
880 --
881 if p_enrt_bnft_id is null then
882 --
883 fnd_message.set_name('BEN','BEN_91560_BENVRBRT_INPT_EB');
884 fnd_message.set_token('PACKAGE',l_package);
885 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
886 fnd_message.set_token('PGM_ID',to_char(l_epe.pgm_id));
887 fnd_message.set_token('PL_TYP_ID',to_char(l_epe.pl_typ_id));
888 fnd_message.set_token('PL_ID',to_char(l_epe.pl_id));
889 fnd_message.set_token('OIPL_ID',to_char(l_epe.oipl_id));
890 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
891 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
892 fnd_message.set_token('PER_IN_LER_ID',to_char(l_epe.per_in_ler_id));
893 fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID',
894 to_char(p_elig_per_elctbl_chc_id));
895 fnd_message.set_token('MLT_CD',l_vpf.mlt_cd);
896 fnd_message.raise_error;
897 --
898 end if;
902 fetch c_enb into l_coverage_value;
899 --
900 open c_enb;
901 --
903 --
904 if c_enb%notfound then
905 --
906 close c_enb;
907 fnd_message.set_name('BEN','BEN_91561_BENVRBRT_ENB_NF');
908 fnd_message.set_token('PACKAGE',l_package);
909 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
910 fnd_message.set_token('PGM_ID',to_char(l_epe.pgm_id));
911 fnd_message.set_token('PL_TYP_ID',to_char(l_epe.pl_typ_id));
912 fnd_message.set_token('PL_ID',to_char(l_epe.pl_id));
913 fnd_message.set_token('OIPL_ID',to_char(l_epe.oipl_id));
914 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
915 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
916 fnd_message.set_token('PER_IN_LER_ID',to_char(l_epe.per_in_ler_id));
917 fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID',
918 to_char(p_elig_per_elctbl_chc_id));
919 fnd_message.set_token('MLT_CD',l_vpf.mlt_cd);
920 fnd_message.raise_error;
921 --
922 end if;
923 --
924 close c_enb;
925 --
926 end if;
927 --
928 end if;
929 --
930 end if;
931 --
932 hr_utility.set_location(l_package||' l_vpf.mlt_cd 1 ',10);
933 if l_vpf.mlt_cd in ('CL','CLANDCVG','FLFXPCL') then
934 --
935 if l_vpf.comp_lvl_fctr_id is null then
936 --
937 fnd_message.set_name('BEN','BEN_91565_BENVRBRT_NULL_CLF');
938 fnd_message.set_token('PACKAGE',l_package);
939 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
940 fnd_message.set_token('PGM_ID',to_char(l_epe.pgm_id));
941 fnd_message.set_token('PL_TYP_ID',to_char(l_epe.pl_typ_id));
942 fnd_message.set_token('PL_ID',to_char(l_epe.pl_id));
943 fnd_message.set_token('OIPL_ID',to_char(l_epe.oipl_id));
944 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
945 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
946 fnd_message.set_token('PER_IN_LER_ID',to_char(l_epe.per_in_ler_id));
947 fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID',
948 to_char(p_elig_per_elctbl_chc_id));
949 fnd_message.set_token('MLT_CD',l_vpf.mlt_cd);
950 fnd_message.raise_error;
951 --
952 end if;
953 --
954 ben_derive_factors.determine_compensation
955 (p_comp_lvl_fctr_id => l_vpf.comp_lvl_fctr_id,
956 p_person_id => p_person_id,
957 p_pgm_id => l_epe.pgm_id,
958 p_pl_id => l_epe.pl_id,
959 p_oipl_id => l_epe.oipl_id,
960 p_per_in_ler_id => l_epe.per_in_ler_id,
961 p_business_group_id => l_epe.business_group_id,
962 p_perform_rounding_flg => true,
963 p_effective_date => p_effective_date, -- FONM : pass overloaded date
964 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
965 p_cal_for => 'R',
966 p_value => l_compensation_value,
967 p_fonm_cvg_strt_dt => l_fonm_cvg_strt_dt );
968 --
969 if l_compensation_value is null then
970 --
971 return; -- for some reason this person has no comp so return
972 --
973 end if;
974 --
975 end if;
976 --
977 hr_utility.set_location(' checking if program or plan ',70);
978 --
979 if l_epe.pgm_id is not null then
980 open c_pgm(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
981 fetch c_pgm into l_pgm;
982 if c_pgm%notfound then
983 close c_pgm;
984 fnd_message.set_name('BEN','BEN_92410_BENACTBR_PGM_NF');
985 fnd_message.set_token('ID',to_char(l_epe.pgm_id));
986 fnd_message.set_token('PACKAGE',l_package);
987 fnd_message.raise_error;
988 end if;
989 close c_pgm;
990 l_acty_ref_perd_cd := l_pgm.acty_ref_perd_cd;
991 else
992 hr_utility.set_location(' testing if plan ',80);
993 open c_pln(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
994 fetch c_pln into l_pln;
995 if c_pln%notfound then
996 close c_pln;
997 fnd_message.set_name('BEN','BEN_92411_BENACTBR_PLN_NF');
998 fnd_message.set_token('ID',to_char(l_epe.pl_id));
999 fnd_message.set_token('PACKAGE',l_package);
1000 fnd_message.raise_error;
1001 end if;
1002 close c_pln;
1003 l_acty_ref_perd_cd := l_pln.nip_acty_ref_perd_cd;
1004 end if;
1005 --
1006 hr_utility.set_location(l_package||' l_vpf.mlt_cd 2 ',10);
1007 --
1008 if l_vpf.mlt_cd in ('AP','APANDCVG') then
1009 --
1010 open c_enrt_prem;
1011 fetch c_enrt_prem into l_actl_prem_value;
1012 close c_enrt_prem;
1013 --
1014 --- Recalculating the premium here instead of fetching from c_enrt_prem
1015 --- if the coverage is enterable at enrollment.
1016 --- Fix for bug 7003453
1017 open c_entr_at_enrt_flag;
1018 fetch c_entr_at_enrt_flag into l_entr_at_enrt_flag;
1019 close c_entr_at_enrt_flag;
1020
1021 if l_entr_at_enrt_flag = 'Y' then
1022
1023 begin
1024 ben_determine_actual_premium.g_computed_prem_val := null ;
1025 ben_determine_actual_premium.main
1026 (p_person_id => p_person_id,
1027 p_effective_date => p_effective_date,
1028 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
1029 p_calc_only_rt_val_flag => p_calc_only_rt_val_flag ,
1033 p_oipl_id => p_oipl_id,
1030 p_pgm_id => p_pgm_id,
1031 p_pl_id => p_pl_id,
1032 p_pl_typ_id => p_pl_typ_id,
1034 p_per_in_ler_id => p_per_in_ler_id,
1035 p_ler_id => p_ler_id,
1036 p_bnft_amt => p_bnft_amt,
1037 p_business_group_id => p_business_group_id );
1038
1039 hr_utility.set_location('premium re calculation over ',555666);
1040 l_actl_prem_value := ben_determine_actual_premium.g_computed_prem_val ;
1041 hr_utility.set_location('re calculation premium'||l_actl_prem_value,555666);
1042 ben_determine_actual_premium.g_computed_prem_val := null ;
1043
1044 end;
1045 end if;
1046 --- end Fix for bug 7003453
1047 if l_actl_prem_value is null then
1048 --
1049 return; -- -- soft error do not stop processing.
1050 --
1051 end if;
1052 --
1053 if l_acty_ref_perd_cd = 'PWK' then
1054 l_actl_prem_value := (l_actl_prem_value * 12) / 52;
1055 elsif l_acty_ref_perd_cd = 'BWK' then
1056 l_actl_prem_value := (l_actl_prem_value * 12) / 26;
1057 elsif l_acty_ref_perd_cd = 'MO' then
1058 null; -- premiums are always monthly, so nothing to do
1059 elsif l_acty_ref_perd_cd = 'SMO' then
1060 l_actl_prem_value := (l_actl_prem_value * 12) / 6;
1061 elsif l_acty_ref_perd_cd = 'PQU' then
1062 l_actl_prem_value := (l_actl_prem_value * 12) / 4;
1063 elsif l_acty_ref_perd_cd = 'SAN' then
1064 l_actl_prem_value := (l_actl_prem_value * 12) / 2;
1065 elsif l_acty_ref_perd_cd = 'PYR' then
1066 l_actl_prem_value := l_actl_prem_value * 12;
1067 elsif l_acty_ref_perd_cd = 'PHR' then
1068 --
1069 l_pay_annualization_factor := to_number(fnd_profile.value('BEN_HRLY_ANAL_FCTR'));
1070 if l_pay_annualization_factor is null then
1071 l_pay_annualization_factor := 2080;
1072 end if;
1073 --
1074 l_actl_prem_value := l_actl_prem_value * 12 / l_pay_annualization_factor;
1075 --
1076
1077 else
1078 fnd_message.set_name('BEN','BEN_92412_UKN_ACTY_REF_PERD');
1079 fnd_message.set_token('PROC',l_package);
1080 fnd_message.set_token('VARIABLE',l_acty_ref_perd_cd);
1081 hr_utility.set_location (' FNDMS_RE 2 ',150);
1082 fnd_message.raise_error;
1083 end if;
1084 --
1085 end if;
1086 --
1087 --
1088 -- We now should have everything to do the calculation
1089 --
1090 -- Flat Amount
1091 --
1092 hr_utility.set_location(l_package||' rt_typ_calc ELSIFs ',10);
1093 if l_vpf.mlt_cd = 'FLFX' then
1094 --
1095 /* Bug 9306764: Added if..else condition.If VAPRO is 'Enter value at Enrollment',then 'VAL' column will be NULL.
1096 Take the Default value of the VAPRO*/
1097 if(l_vpf.no_mn_elcn_val_dfnd_flag = 'Y' and l_vpf.val is null) then
1098 p_val := l_vpf.dflt_elcn_val;
1099 else
1100 p_val := l_vpf.val;
1101 end if;
1102 --
1103 elsif l_vpf.mlt_cd = 'CL' then
1104 benutils.rt_typ_calc
1105 (p_rt_typ_cd => l_vpf.rt_typ_cd
1106 ,p_val => l_vpf.val
1107 ,p_val_2 => l_compensation_value
1108 ,p_calculated_val => p_val);
1109
1110 elsif l_vpf.mlt_cd = 'CVG' then
1111 benutils.rt_typ_calc
1112 (p_rt_typ_cd => l_vpf.bnft_rt_typ_cd
1113 ,p_val => l_vpf.val
1114 ,p_val_2 => l_coverage_value
1115 ,p_calculated_val => p_val);
1116
1117 elsif l_vpf.mlt_cd = 'AP' then
1118 benutils.rt_typ_calc
1119 (p_rt_typ_cd => l_vpf.rt_typ_cd
1120 ,p_val => l_vpf.val
1121 ,p_val_2 => l_actl_prem_value
1122 ,p_calculated_val => p_val);
1123
1124
1125 elsif l_vpf.mlt_cd = 'FLFXPCL' then
1126 benutils.rt_typ_calc
1127 (p_rt_typ_cd => l_vpf.rt_typ_cd
1128 ,p_val => l_vpf.mn_elcn_val --12395523
1129 ,p_val_2 => l_compensation_value
1130 ,p_calculated_val => p_val);
1131
1132 p_val := p_val + l_vpf.val; --12395523
1133
1134 elsif l_vpf.mlt_cd = 'CLANDCVG' then
1135 benutils.rt_typ_calc
1136 (p_rt_typ_cd => l_vpf.rt_typ_cd
1137 ,p_val => l_vpf.val
1138 ,p_val_2 => l_compensation_value
1139 ,p_calculated_val => l_value);
1140
1141 --
1142 -- now take l_value and apply it against the coverage
1143 --
1144 benutils.rt_typ_calc
1145 (p_rt_typ_cd => l_vpf.bnft_rt_typ_cd
1146 ,p_val => l_value
1147 ,p_val_2 => l_coverage_value
1148 ,p_calculated_val => p_val);
1149
1150 elsif l_vpf.mlt_cd = 'APANDCVG' then
1151 benutils.rt_typ_calc
1152 (p_rt_typ_cd => l_vpf.rt_typ_cd
1153 ,p_val => l_vpf.val
1154 ,p_val_2 => l_actl_prem_value
1155 ,p_calculated_val => l_value);
1156
1157 --
1158 -- now take l_value and apply it against the coverage
1159 --
1160 benutils.rt_typ_calc
1161 (p_rt_typ_cd => l_vpf.bnft_rt_typ_cd
1162 ,p_val => l_value
1163 ,p_val_2 => l_coverage_value
1164 ,p_calculated_val => p_val);
1165
1166 elsif l_vpf.mlt_cd = 'PRNTANDCVG' then
1167 benutils.rt_typ_calc
1168 (p_rt_typ_cd => l_vpf.rt_typ_cd
1169 ,p_val => l_vpf.val
1173 --
1170 ,p_val_2 => l_prnt_rt_value
1171 ,p_calculated_val => l_value);
1172
1174 -- now take l_value and apply it against the coverage
1175 --
1176 benutils.rt_typ_calc
1177 (p_rt_typ_cd => l_vpf.bnft_rt_typ_cd
1178 ,p_val => l_value
1179 ,p_val_2 => l_coverage_value
1180 ,p_calculated_val => p_val);
1181
1182 --
1183 elsif l_vpf.mlt_cd = 'RL' then
1184 --
1185 -- FONM
1186 open c_asg(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
1187 --
1188 fetch c_asg into l_asg;
1189 --
1190 close c_asg;
1191 --
1192 /* -- 4031733 - Cursor c_state populates l_state variable which is no longer
1193 -- used in the package. Cursor can be commented
1194
1195 if p_person_id is not null then
1196 open c_state(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
1197 fetch c_state into l_state;
1198 close c_state;
1199
1200 if l_state.region_2 is not null then
1201
1202 l_jurisdiction_code :=
1203 pay_mag_utils.lookup_jurisdiction_code
1204 (p_state => l_state.region_2);
1205
1206 end if;
1207
1208 end if;
1209 */
1210 -- Call formula initialise routine
1211 --greatest
1212 l_outputs := benutils.formula
1213 (p_formula_id => l_vpf.val_calc_rl,
1214 p_effective_date => nvl(l_fonm_cvg_strt_dt,greatest(nvl(p_lf_evt_ocrd_dt,p_effective_date),p_effective_date)), -- Bug 6219465
1215 p_business_group_id => l_epe.business_group_id,
1216 p_assignment_id => l_asg.assignment_id,
1217 p_organization_id => l_asg.organization_id,
1218 p_pgm_id => l_epe.pgm_id,
1219 p_pl_id => l_epe.pl_id,
1220 p_pl_typ_id => l_epe.pl_typ_id,
1221 p_opt_id => l_opt.opt_id,
1222 p_ler_id => l_epe.ler_id,
1223 p_acty_base_rt_id => p_acty_base_rt_id,
1224 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1225 p_jurisdiction_code => l_jurisdiction_code,
1226 -- FONM
1227 p_param1 => 'BEN_IV_RT_STRT_DT',
1228 p_param1_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_rt_strt_dt),
1229 p_param2 => 'BEN_IV_CVG_STRT_DT',
1230 p_param2_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_cvg_strt_dt));
1231 --
1232 p_val := l_outputs(l_outputs.first).value;
1233 --
1234 else
1235 --
1236 fnd_message.set_name('BEN','BEN_91572_BENVRBRT_MLT_CD');
1237 hr_utility.set_location(l_package||' FND 91572 ',10);
1238 fnd_message.set_token('PACKAGE',l_package);
1239 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1240 fnd_message.set_token('PGM_ID',to_char(l_epe.pgm_id));
1241 fnd_message.set_token('PL_TYP_ID',to_char(l_epe.pl_typ_id));
1242 fnd_message.set_token('PL_ID',to_char(l_epe.pl_id));
1243 fnd_message.set_token('OIPL_ID',to_char(l_epe.oipl_id));
1244 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
1245 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
1246 fnd_message.set_token('PER_IN_LER_ID',to_char(l_epe.per_in_ler_id));
1247 fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID',
1248 to_char(p_elig_per_elctbl_chc_id));
1249 fnd_message.set_token('MLT_CD',l_vpf.mlt_cd);
1250 fnd_message.raise_error;
1251 --
1252 end if; -- mult_cd
1253 --
1254 -- assign other outputs
1255 --
1256 p_mx_elcn_val := l_vpf.mx_elcn_val;
1257 p_mn_elcn_val := l_vpf.mn_elcn_val;
1258 p_dflt_elcn_val := l_vpf.dflt_elcn_val;
1259 p_incrmnt_elcn_val := l_vpf.incrmnt_elcn_val;
1260 p_ultmt_upr_lmt := l_vpf.ultmt_upr_lmt ;
1261 p_ultmt_lwr_lmt := l_vpf.ultmt_lwr_lmt ;
1262 p_ultmt_upr_lmt_calc_rl := l_vpf.ultmt_upr_lmt_calc_rl ;
1263 p_ultmt_lwr_lmt_calc_rl := l_vpf.ultmt_lwr_lmt_calc_rl ;
1264 p_ann_mn_elcn_val := l_vpf.ann_mn_elcn_val;
1265 p_ann_mx_elcn_val := l_vpf.ann_mx_elcn_val;
1266 --
1267 p_tx_typ_cd := l_vpf.tx_typ_cd;
1268 p_acty_typ_cd := l_vpf.acty_typ_cd;
1269 p_vrbl_rt_trtmt_cd := l_vpf.vrbl_rt_trtmt_cd;
1270
1271 -- bug 1210355
1272 g_vrbl_mlt_code := l_vpf.mlt_cd;
1273 --
1274 else -- profile not found so it uses rules.
1275 --
1276 -- FONM
1277 open c_asg(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
1278 --
1279 fetch c_asg into l_asg;
1280 --
1281 close c_asg;
1282 --
1283
1284 /* -- 4031733 - Cursor c_state populates l_state variable which is no longer
1285 -- used in the package. Cursor can be commented
1286
1287 if p_person_id is not null then
1288
1289 open c_state(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
1290 fetch c_state into l_state;
1291 close c_state;
1292
1293 if l_state.region_2 is not null then
1294
1295 l_jurisdiction_code :=
1296 pay_mag_utils.lookup_jurisdiction_code
1297 (p_state => l_state.region_2);
1298 end if;
1299 end if;
1300 */
1301
1302 if p_acty_base_rt_id is not null then
1303 --
1304 for l_vrr in c_vrr(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date))) loop
1305 --
1306 -- Call formula initialise routine
1307 --
1308 l_outputs := benutils.formula
1309 (p_formula_id => l_vrr.formula_id,
1310 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
1311 p_business_group_id => l_epe.business_group_id,
1315 p_pl_id => l_epe.pl_id,
1312 p_assignment_id => l_asg.assignment_id,
1313 p_organization_id => l_asg.organization_id,
1314 p_pgm_id => l_epe.pgm_id,
1316 p_pl_typ_id => l_epe.pl_typ_id,
1317 p_opt_id => l_opt.opt_id,
1318 p_ler_id => l_epe.ler_id,
1319 p_acty_base_rt_id => p_acty_base_rt_id,
1320 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1321 p_jurisdiction_code => l_jurisdiction_code,
1322 p_param1 => 'BEN_IV_RT_STRT_DT',
1323 p_param1_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_rt_strt_dt),
1324 p_param2 => 'BEN_IV_CVG_STRT_DT',
1325 p_param2_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_cvg_strt_dt));
1326 --
1327 if l_outputs(l_outputs.first).value is not null then
1328 --
1329 p_val := l_outputs(l_outputs.first).value;
1330 exit;
1331 --
1332 end if;
1333 --
1334 end loop;
1335 --
1336 elsif p_actl_prem_id is not null then
1337 --
1338 -- Call formula initialise routine
1339 --
1340 open c_ava(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
1341 fetch c_ava into l_ava;
1342 if l_ava.vrbl_rt_add_on_calc_rl is not null then
1343
1344 l_outputs := benutils.formula
1345 (p_formula_id => l_ava.vrbl_rt_add_on_calc_rl,
1346 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
1347 p_business_group_id => l_epe.business_group_id,
1348 p_assignment_id => l_asg.assignment_id,
1349 p_organization_id => l_asg.organization_id,
1350 p_pgm_id => l_epe.pgm_id,
1351 p_pl_id => l_epe.pl_id,
1352 p_pl_typ_id => l_epe.pl_typ_id,
1353 p_opt_id => l_opt.opt_id,
1354 p_ler_id => l_epe.ler_id,
1355 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1356 p_jurisdiction_code => l_jurisdiction_code,
1357 p_param1 => 'BEN_IV_RT_STRT_DT',
1358 p_param1_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_rt_strt_dt),
1359 p_param2 => 'BEN_IV_CVG_STRT_DT',
1360 p_param2_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_cvg_strt_dt));
1361
1362 if l_outputs(l_outputs.first).value is not null then
1363 p_val := l_outputs(l_outputs.first).value;
1364 end if;
1365 end if;
1366 close c_ava;
1367 --
1368 -- Call formula initialise routine nmh
1369 --
1370 for l_avrl in c_avrl(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)))
1371 loop
1372 if l_avrl.formula_id is not null then
1373
1374 l_outputs := benutils.formula
1375 (p_formula_id => l_avrl.formula_id,
1376 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
1377 p_business_group_id => l_epe.business_group_id,
1378 p_assignment_id => l_asg.assignment_id,
1379 p_organization_id => l_asg.organization_id,
1380 p_pgm_id => l_epe.pgm_id,
1381 p_pl_id => l_epe.pl_id,
1382 p_pl_typ_id => l_epe.pl_typ_id,
1383 p_opt_id => l_opt.opt_id,
1384 p_ler_id => l_epe.ler_id,
1385 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1386 p_jurisdiction_code => l_jurisdiction_code,
1387 p_param1 => 'BEN_IV_RT_STRT_DT',
1388 p_param1_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_rt_strt_dt),
1389 p_param2 => 'BEN_IV_CVG_STRT_DT',
1390 p_param2_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_cvg_strt_dt));
1391
1392 if l_outputs(l_outputs.first).value is not null then
1393 p_val := fnd_number.canonical_to_number(l_outputs(l_outputs.first).value);
1394 p_vrbl_rt_trtmt_cd := l_avrl.rt_trtmt_cd ;
1395 exit ;
1396 end if;
1397 end if;
1398 end loop;
1399 -- nmh
1400 --
1401 elsif p_cvg_amt_calc_mthd_id is not null then
1402 --
1403 for l_brr in c_brr(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date))) loop
1404 --
1405 -- Call formula initialise routine
1406 --
1407 l_outputs := benutils.formula
1408 (p_formula_id => l_brr.formula_id,
1409 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
1410 p_business_group_id => l_epe.business_group_id,
1411 p_assignment_id => l_asg.assignment_id,
1412 p_organization_id => l_asg.organization_id,
1413 p_pgm_id => l_epe.pgm_id,
1414 p_pl_id => l_epe.pl_id,
1415 p_pl_typ_id => l_epe.pl_typ_id,
1416 p_opt_id => l_opt.opt_id,
1417 p_ler_id => l_epe.ler_id,
1418 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1419 p_jurisdiction_code => l_jurisdiction_code,
1420 p_param1 => 'BEN_IV_RT_STRT_DT',
1421 p_param1_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_rt_strt_dt),
1422 p_param2 => 'BEN_IV_CVG_STRT_DT',
1423 p_param2_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_cvg_strt_dt));
1424 --
1425 if l_outputs(l_outputs.first).value is not null then
1426 --
1427 p_val := l_outputs(l_outputs.first).value;
1428 exit;
1429 --
1430 end if;
1431 --
1432 end loop;
1433 --
1434 end if;
1435 --
1436 end if;
1437 --
1441 l_vpf.no_mn_elcn_val_dfnd_flag = 'Y' and
1438 -- Bug 4873847. Calculate CWB values.
1439 --
1440 if nvl(l_env.mode_cd,'~') = 'W' and
1442 l_vpf.mlt_cd in ('CL' ) and
1443 l_vpf.acty_typ_cd like 'CWB%' and
1444 l_compensation_value is not null and
1445 l_vpf.vrbl_rt_trtmt_cd = 'RPLC'
1446 then
1447 benutils.rt_typ_calc
1448 (p_rt_typ_cd => l_vpf.rt_typ_cd
1449 ,p_val => p_mn_elcn_val
1450 ,p_val_2 => l_compensation_value
1451 ,p_calculated_val => l_cwb_mn_elcn_val);
1452 --
1453 hr_utility.set_location('l_cwb_mn_elcn_val is '||l_cwb_mn_elcn_val,951);
1454 --
1455 benutils.rt_typ_calc
1456 (p_rt_typ_cd => l_vpf.rt_typ_cd
1457 ,p_val => p_mx_elcn_val
1458 ,p_val_2 => l_compensation_value
1459 ,p_calculated_val => l_cwb_mx_elcn_val);
1460 --
1461 hr_utility.set_location('l_cwb_mx_elcn_val is '|| l_cwb_mx_elcn_val,951);
1462 --
1463 -- 5371364 : Do not Operate (i.e multiply/divide..) the Increment Value.
1464 l_cwb_incrmt_elcn_val := p_incrmnt_elcn_val;
1465 /*
1466 benutils.rt_typ_calc
1467 (p_rt_typ_cd => l_vpf.rt_typ_cd
1468 ,p_val => p_incrmnt_elcn_val
1469 ,p_val_2 => l_compensation_value
1470 ,p_calculated_val => l_cwb_incrmt_elcn_val);
1471 */
1472 --
1473 hr_utility.set_location('l_cwb_incrmt_elcn_val is '|| l_cwb_incrmt_elcn_val,951);
1474 --
1475 benutils.rt_typ_calc
1476 (p_rt_typ_cd => l_vpf.rt_typ_cd
1477 ,p_val => p_dflt_elcn_val
1478 ,p_val_2 => l_compensation_value
1479 ,p_calculated_val => l_cwb_dflt_val);
1480 --
1481 hr_utility.set_location('l_cwb_dflt_val is '|| l_cwb_dflt_val,951);
1482 --
1483 if (l_vpf.rndg_cd is not null or
1484 l_vpf.rndg_rl is not null) then
1485
1486 if l_cwb_mn_elcn_val is not null then
1487 l_rounded_value := benutils.do_rounding
1488 (p_rounding_cd => l_vpf.rndg_cd,
1489 p_rounding_rl => l_vpf.rndg_rl,
1490 p_value => l_cwb_mn_elcn_val,
1491 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date));
1492 l_cwb_mn_elcn_val := l_rounded_value;
1493 end if;
1494 --
1495 if l_cwb_mx_elcn_val is not null then
1496 l_rounded_value := benutils.do_rounding
1497 (p_rounding_cd => l_vpf.rndg_cd,
1498 p_rounding_rl => l_vpf.rndg_rl,
1499 p_value => l_cwb_mx_elcn_val,
1500 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date));
1501 l_cwb_mx_elcn_val := l_rounded_value;
1502 end if;
1503 --
1504 if l_cwb_dflt_val is not null then
1505 l_rounded_value := benutils.do_rounding
1506 (p_rounding_cd => l_vpf.rndg_cd,
1507 p_rounding_rl => l_vpf.rndg_rl,
1508 p_value => l_cwb_dflt_val,
1509 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date));
1510 l_cwb_dflt_val := l_rounded_value;
1511 end if;
1512 end if;
1513 --
1514 p_mx_elcn_val := nvl(l_cwb_mx_elcn_val, p_mx_elcn_val);
1515 p_mn_elcn_val := nvl(l_cwb_mn_elcn_val, p_mn_elcn_val);
1516 p_incrmnt_elcn_val := nvl(l_cwb_incrmt_elcn_val,p_incrmnt_elcn_val);
1517 p_dflt_elcn_val := nvl(l_cwb_dflt_val, p_dflt_elcn_val);
1518 p_val := p_dflt_elcn_val; -- Bug 7331668
1519 --
1520 end if;
1521 --
1522 -- End Bug 4873847.
1523 --
1524 hr_utility.set_location('doing rounding '||l_package,10);
1525 --
1526 if (l_vpf.rndg_cd is not null or
1527 l_vpf.rndg_rl is not null)
1528 and p_val is not null then
1529 --
1530 l_rounded_value := benutils.do_rounding
1531 (p_rounding_cd => l_vpf.rndg_cd,
1532 p_rounding_rl => l_vpf.rndg_rl,
1533 p_value => p_val,
1534 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date));
1535 --
1536 p_val := l_rounded_value;
1537 --
1538 end if;
1539 --
1540 -- Bug 2260440 : determine assignment id, as it may be used by formula.
1541 --
1542 if l_asg.assignment_id is null and
1543 (l_vpf.upr_lmt_calc_rl is not null or l_vpf.lwr_lmt_calc_rl is not null )
1544 then
1545 -- FONM
1546 open c_asg(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
1547 --
1548 fetch c_asg into l_asg;
1549 --
1550 close c_asg;
1551 end if;
1552 --
1553 hr_utility.set_location('Floor/Ceiling Rule Checking'||l_package,30);
1554 benutils.limit_checks
1555 (p_upr_lmt_val => l_vpf.upr_lmt_val,
1556 p_lwr_lmt_val => l_vpf.lwr_lmt_val,
1557 p_upr_lmt_calc_rl => l_vpf.upr_lmt_calc_rl,
1558 p_lwr_lmt_calc_rl => l_vpf.lwr_lmt_calc_rl,
1559 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
1560 p_business_group_id => l_epe.business_group_id,
1561 p_assignment_id => l_asg.assignment_id,
1562 p_organization_id => l_asg.organization_id,
1563 p_pgm_id => l_epe.pgm_id,
1564 p_pl_id => l_epe.pl_id,
1565 p_pl_typ_id => l_epe.pl_typ_id,
1566 p_opt_id => l_opt.opt_id,
1567 p_ler_id => l_epe.ler_id,
1568 p_state => l_state.region_2,
1569 p_acty_base_rt_id => p_acty_base_rt_id,
1570 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1571 p_val => p_val);
1572 --
1573 hr_utility.set_location ('Leaving '||l_package,10);
1577 end ben_determine_variable_rates;
1574 --
1575 end main;
1576 --