[Home] [Help]
PACKAGE BODY: APPS.BEN_DETERMINE_VARIABLE_RATES
Source
1 Package Body BEN_DETERMINE_VARIABLE_RATES AS
2 /* $Header: benvrbrt.pkb 120.10.12010000.3 2008/08/28 04:33:29 bachakra ship $ */
3 --------------------------------------------------------------------------------
4 /*
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.
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
62 rules
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.
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
140 and then others .
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 */
161 --------------------------------------------------------------------------------
162 --
163 g_package varchar2(80) := 'ben_determine_variable_rates';
164 --
165 --------------------------------------------------------------------------------
166 --------------------------------- main -----------------------------------------
167 --------------------------------------------------------------------------------
168 PROCEDURE main
169 (p_currepe_row in ben_determine_rates.g_curr_epe_rec
170 := ben_determine_rates.g_def_curr_epe_rec
171 ,p_per_row in per_all_people_F%rowtype
172 := ben_determine_rates.g_def_curr_per_rec
173 ,p_asg_row in per_all_assignments_f%rowtype
174 := ben_determine_rates.g_def_curr_asg_rec
175 ,p_ast_row in per_assignment_status_types%rowtype
176 := ben_determine_rates.g_def_curr_ast_rec
177 ,p_adr_row in per_addresses%rowtype
178 := ben_determine_rates.g_def_curr_adr_rec
179 ,p_person_id IN number
180 ,p_elig_per_elctbl_chc_id IN number
181 ,p_enrt_bnft_id IN number default null
182 ,p_actl_prem_id IN number default null --\
183 ,p_acty_base_rt_id IN number default null -- Only one of these 3 have val.
184 ,p_cvg_amt_calc_mthd_id IN number default null --/
185 ,p_effective_date IN date
186 ,p_lf_evt_ocrd_dt IN date
187 ,p_calc_only_rt_val_flag in boolean default false
188 ,p_pgm_id in number default null
189 ,p_pl_id in number default null
190 ,p_oipl_id in number default null
191 ,p_pl_typ_id in number default null
192 ,p_per_in_ler_id in number default null
193 ,p_ler_id in number default null
194 ,p_business_group_id in number default null
195 ,p_bnft_amt in number default null
196 ,p_val out nocopy number
197 ,p_mn_elcn_val out nocopy number
198 ,p_mx_elcn_val out nocopy number
199 ,p_incrmnt_elcn_val out nocopy number
200 ,p_dflt_elcn_val out nocopy number
201 ,p_tx_typ_cd out nocopy varchar2
202 ,p_acty_typ_cd out nocopy varchar2
203 ,p_vrbl_rt_trtmt_cd out nocopy varchar2
204 ,p_ultmt_upr_lmt out nocopy number
205 ,p_ultmt_lwr_lmt out nocopy number
206 ,p_ultmt_upr_lmt_calc_rl out nocopy number
207 ,p_ultmt_lwr_lmt_calc_rl out nocopy number
208 ,p_ann_mn_elcn_val out nocopy number
209 ,p_ann_mx_elcn_val out nocopy number
210
211 )
212 IS
213 --
214 l_package varchar2(80) := g_package||'.main';
215 --
216 l_vrbl_rt_prfl_id number;
217 l_outputs ff_exec.outputs_t;
218 l_compensation_value number;
219 l_coverage_value number := null;
220 l_actl_prem_value number;
221 l_prnt_rt_value number;
222 l_value number;
223 l_comp_lvl_fctr_id number;
224 l_actl_prem_id number;
225 l_acty_base_rt_id number;
226 l_acty_ref_perd_cd varchar2(30);
227 l_dummy_num number;
228 l_dummy_char varchar2(30);
229 l_dummy_date date;
230 l_avr_found boolean default false;
231 l_apv_found boolean default false;
232 l_bvr_found boolean default false;
233 l_rounded_value number;
234 l_jurisdiction_code varchar2(30);
235 --
236 -- Bug 4873847
237 --
238 l_cwb_dflt_val number;
239 l_cwb_incrmt_elcn_val number;
240 l_cwb_mx_elcn_val number;
241 l_cwb_mn_elcn_val number;
242 --
243 -- End Bug 4873847
244 --
245 -- FONM
246 cursor c_asg(cv_effective_date date) is
247 select asg.assignment_id,asg.organization_id
248 from per_all_assignments_f asg, per_assignment_status_types ast
249 where asg.person_id = p_person_id
250 and asg.assignment_type <> 'C'
251 and asg.primary_flag = 'Y'
252 and asg.assignment_status_type_id = ast.assignment_status_type_id(+)
253 and ast.per_system_status = 'ACTIVE_ASSIGN' -- Bug 6399423 removed the outer join
254 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
255 between asg.effective_start_date
256 and asg.effective_end_date
257 order by assignment_type desc, effective_start_date desc; -- BUG 4644867
258 --
259 l_asg c_asg%rowtype;
260 --
261 -- FONM
262 cursor c_avr(cv_effective_date date) is
263 select avr.acty_vrbl_rt_id
264 from ben_acty_vrbl_rt_f avr
265 where avr.acty_base_rt_id = p_acty_base_rt_id
266 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
267 between avr.effective_start_date
268 and avr.effective_end_date;
269 --
270 l_avr c_avr%rowtype;
271 --
272 --
273 --
274 -- FONM
275 cursor c_avrl(cv_effective_date date) is
276 select avr.formula_id, avr.RT_TRTMT_CD
277 from ben_actl_prem_vrbl_rt_rl_f avr
278 where avr.actl_prem_id = p_actl_prem_id
279 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
280 between avr.effective_start_date
281 and avr.effective_end_date;
282 --
283 l_avrl c_avrl%rowtype;
284 -- FONM
285 --
286 cursor c_vrr(cv_effective_date date) is
287 select vrr.formula_id
288 from ben_vrbl_rt_rl_f vrr
289 where vrr.acty_base_rt_id = p_acty_base_rt_id
290 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
291 between vrr.effective_start_date
292 and vrr.effective_end_date
293 order by vrr.ordr_to_aply_num;
294 --
295 -- FONM
296 cursor c_apv(cv_effective_date date) is
297 select apv.actl_prem_vrbl_rt_id
298 from ben_actl_prem_vrbl_rt_f apv
299 where apv.actl_prem_id = p_actl_prem_id
300 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
301 between apv.effective_start_date
302 and apv.effective_end_date;
303 --
304 l_apv c_apv%rowtype;
305 --
306 -- FONM
307 cursor c_ava(cv_effective_date date) is
308 select ava.vrbl_rt_add_on_calc_rl
309 from ben_actl_prem_f ava
310 where ava.actl_prem_id = p_actl_prem_id
311 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
312 between ava.effective_start_date
313 and ava.effective_end_date;
314 --
315 l_ava c_ava%rowtype;
316 --
317 -- FONM
318 cursor c_bvr(cv_effective_date date) is
319 select bvr.bnft_vrbl_rt_id
320 from ben_bnft_vrbl_rt_f bvr
321 where bvr.cvg_amt_calc_mthd_id = p_cvg_amt_calc_mthd_id
322 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
323 between bvr.effective_start_date
324 and bvr.effective_end_date;
325 --
326 l_bvr c_bvr%rowtype;
327 --
328 -- FONM
329 cursor c_brr(cv_effective_date date) is
330 select brr.formula_id
331 from ben_bnft_vrbl_rt_rl_f brr
332 where brr.cvg_amt_calc_mthd_id = p_cvg_amt_calc_mthd_id
333 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
334 between brr.effective_start_date
335 and brr.effective_end_date
336 order by brr.ordr_to_aply_num;
337 --
338 -- FONM
339 cursor c_vpf(cv_effective_date date) is
340 select vpf.val,
341 vpf.val_calc_rl,
342 vpf.mx_elcn_val,
343 vpf.mn_elcn_val,
344 vpf.dflt_elcn_val,
345 vpf.incrmnt_elcn_val,
346 vpf.mlt_cd,
347 vpf.acty_typ_cd,
348 vpf.rt_typ_cd,
349 vpf.bnft_rt_typ_cd,
350 vpf.tx_typ_cd,
351 vpf.vrbl_rt_trtmt_cd,
352 vpf.comp_lvl_fctr_id,
353 vpf.lwr_lmt_val,
354 vpf.lwr_lmt_calc_rl,
355 vpf.upr_lmt_val,
356 vpf.upr_lmt_calc_rl,
357 vpf.rndg_cd,
358 vpf.rndg_rl,
359 vpf.ultmt_upr_lmt,
360 vpf.ultmt_lwr_lmt,
361 vpf.ultmt_upr_lmt_calc_rl,
362 vpf.ultmt_lwr_lmt_calc_rl,
363 vpf.ann_mn_elcn_val,
364 vpf.ann_mx_elcn_val,
365 vpf.no_mn_elcn_val_dfnd_flag
366 from ben_vrbl_rt_prfl_f vpf
367 where vpf.vrbl_rt_prfl_id = l_vrbl_rt_prfl_id
368 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
369 between vpf.effective_start_date
370 and vpf.effective_end_date;
371 --
372 l_vpf c_vpf%rowtype;
373 --
374 cursor c_epe
375 is
376 select epe.pl_id,
377 epe.pl_typ_id,
378 epe.oipl_id,
379 epe.pgm_id,
380 epe.business_group_id,
381 epe.per_in_ler_id,
382 pil.ler_id
383 from ben_elig_per_elctbl_chc epe,ben_per_in_ler pil
384 where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
385 and epe.per_in_ler_id = pil.per_in_ler_id;
386 --
387 -- FONM
388 cursor c_opt(l_oipl_id number, cv_effective_date date) is
389 select opt_id
390 from ben_oipl_f oipl
391 where oipl_id = l_oipl_id
392 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
393 between oipl.effective_start_date
394 and oipl.effective_end_date;
395 l_epe c_epe%rowtype;
396 l_opt c_opt%rowtype;
397 --
398 -- FONM
399 Cursor c_state(cv_effective_date date) is
400 select loc.region_2
401 from hr_locations_all loc,per_all_assignments_f asg
402 where loc.location_id = asg.location_id
403 and asg.person_id = p_person_id
404 and asg.assignment_type <> 'C'
405 and asg.primary_flag = 'Y'
406 and cv_effective_date -- FONM p_effective_date
407 between
408 asg.effective_start_date and asg.effective_end_date;
409
410 l_state c_state%rowtype;
411
412 -- FONM
413 cursor c_apr2(cv_effective_date date) is
414 select apr.actl_prem_id
415 from ben_actl_prem_f apr,
416 ben_pl_f pln,
417 ben_oipl_f cop
418 where ((pln.pl_id = l_epe.pl_id
419 and pln.actl_prem_id = apr.actl_prem_id
420 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
421 between pln.effective_start_date
422 and pln.effective_end_date)
423 or (cop.oipl_id = l_epe.oipl_id
424 and cop.actl_prem_id = apr.actl_prem_id
425 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
426 between cop.effective_start_date
427 and cop.effective_end_date))
428 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
429 between apr.effective_start_date
430 and apr.effective_end_date;
431 --
432 -- FONM
433 cursor c_pln(cv_effective_date date) is
434 select pln.nip_acty_ref_perd_cd
435 from ben_pl_f pln
436 where pln.pl_id = l_epe.pl_id
437 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
438 between pln.effective_start_date
439 and pln.effective_end_date;
440
441 l_pln c_pln%rowtype;
442
443 -- FONM
444 cursor c_pgm(cv_effective_date date) is
445 select pgm.acty_ref_perd_cd
446 from ben_pgm_f pgm
447 where pgm.pgm_id = l_epe.pgm_id
448 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
449 between pgm.effective_start_date
450 and pgm.effective_end_date;
451
452 l_pgm c_pgm%rowtype;
453 --
454 cursor c_enb is
455 select nvl(enb.val,enb.dflt_val) -- used nvl to compute value based on default val
456 from ben_enrt_bnft enb
457 where enb.enrt_bnft_id = p_enrt_bnft_id;
458 --
459 -- FONM
460 cursor c_abr(cv_effective_date date) is
461 select abr2.acty_base_rt_id
462 from ben_acty_base_rt_f abr,
463 ben_paird_rt_f prd,
464 ben_acty_base_rt_f abr2
465 where abr.acty_base_rt_id = p_acty_base_rt_id
466 and abr.acty_base_rt_id = prd.chld_acty_base_rt_id
467 and abr2.acty_base_rt_id = prd.parnt_acty_base_rt_id
468 and abr2.parnt_chld_cd = 'PARNT'
469 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
470 between abr.effective_start_date
471 and abr.effective_end_date
472 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
473 between prd.effective_start_date
474 and prd.effective_end_date
475 and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
476 between abr2.effective_start_date
477 and abr2.effective_end_date
478 and rownum = 1;
479 --
480 cursor c_prem_abr(cv_effective_date date) is
481 select abr.actl_prem_id
482 from ben_acty_base_rt_f abr
483 where abr.acty_base_rt_id = p_acty_base_rt_id
484 and cv_effective_date between abr.effective_start_date and
485 abr.effective_end_date;
486 --
487 cursor c_enrt_prem is
488 select ecr.val
489 from ben_enrt_prem ecr,
490 ben_per_in_ler pil,
491 ben_elig_per_elctbl_chc epe
492 where ecr.actl_prem_id = l_actl_prem_id
493 and ecr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
494 and epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id
495 and pil.per_in_ler_id = epe.per_in_ler_id
496 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
497 --
498 -- FONM
499 cursor c_imp_inc_plan(p_pl_id in number, cv_effective_date date) is
500 select 'Y'
501 from ben_pl_f pln
502 where pln.imptd_incm_calc_cd in ('PRTT', 'SPS', 'DPNT') and
503 pln.pl_stat_cd = 'A' and
504 pln.pl_id = p_pl_id and
505 pln.business_group_id = p_business_group_id and
506 cv_effective_date -- FONM p_effective_date
507 between pln.effective_start_date and
508 pln.effective_end_date;
509
510 --
511 -- bug 7003453
512 cursor c_entr_at_enrt_flag is
513 select ENTR_VAL_AT_ENRT_FLAG
514 from BEN_CVG_AMT_CALC_MTHD_f
515 WHERE (pl_id = p_pl_id
516 or oipl_id = p_oipl_id)
517 and p_effective_date
518 between effective_start_date
519 and effective_end_date;
520
521 l_entr_at_enrt_flag ben_cvg_amt_calc_mthd_f.entr_val_at_enrt_flag%type;
522
523 --end bug 7003453
524 l_imp_inc_plan varchar2(1) := 'N';
525 l_pay_annualization_factor number;
526 --
527 -- FONM
528 l_fonm_cvg_strt_dt date;
529 --
530 -- Bug 4873847
531 --
532 l_env ben_env_object.g_global_env_rec_type;
533 l_mode l_env.mode_cd%TYPE;
534 --
535 -- End Bug 4873847
536 --
537 BEGIN
538 --
539 hr_utility.set_location ('Entering '||l_package,10);
540 --
541 ben_env_object.get(p_rec => l_env);
542 --
543 If (p_person_id is null) then
544 --
545 fnd_message.set_name('BEN','BEN_91554_BENVRBRT_INPT_PRSN');
546 fnd_message.set_token('PACKAGE',l_package);
547 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
548 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
549 fnd_message.set_token('PL_ID',to_char(p_pl_id));
550 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
551 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
552 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
553 fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID',to_char(p_elig_per_elctbl_chc_id));
554 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
555 fnd_message.set_token('ACTL_PREM_ID',to_char(p_actl_prem_id));
556 fnd_message.set_token('ACTY_BASE_RT_ID',to_char(p_acty_base_rt_id));
557 fnd_message.set_token('CVG_AMT_CALC_MTHD_ID',to_char(p_cvg_amt_calc_mthd_id));
558 fnd_message.raise_error;
559 --
560 end if;
561 --
562 -- Edit to insure that the input p_effective_date has a value
563 If (p_effective_date is null) then
564 --
565 fnd_message.set_name('BEN','BEN_91555_BENVRBRT_INPT_EFFDT');
566 fnd_message.set_token('PACKAGE',l_package);
567 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
568 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
569 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
570 fnd_message.set_token('PL_ID',to_char(p_pl_id));
571 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
572 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
573 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
574 fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID',to_char(p_elig_per_elctbl_chc_id));
575 fnd_message.set_token('ACTL_PREM_ID',to_char(p_actl_prem_id));
576 fnd_message.set_token('ACTY_BASE_RT_ID',to_char(p_acty_base_rt_id));
577 fnd_message.set_token('CVG_AMT_CALC_MTHD_ID',to_char(p_cvg_amt_calc_mthd_id));
578 fnd_message.raise_error;
579 --
580 end if;
581 --
582 -- Edit to insure that the input p_elig_per_elctbl_chc_id has a value
583 If (p_elig_per_elctbl_chc_id is null) and not(p_calc_only_rt_val_flag) then
584 --
585 fnd_message.set_name('BEN','BEN_91556_BENVRBRT_INPT_EC');
586 fnd_message.set_token('PACKAGE',l_package);
587 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
588 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
589 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
590 fnd_message.set_token('PL_ID',to_char(p_pl_id));
591 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
592 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
593 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
594 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
595 fnd_message.set_token('ACTL_PREM_ID',to_char(p_actl_prem_id));
596 fnd_message.set_token('ACTY_BASE_RT_ID',to_char(p_acty_base_rt_id));
597 fnd_message.set_token('CVG_AMT_CALC_MTHD_ID',to_char(p_cvg_amt_calc_mthd_id));
598 fnd_message.raise_error;
599 --
600 end if;
601 --
602 -- Edit to ensure that one of the base table ids has a value
603 If (p_acty_base_rt_id is null and
604 p_actl_prem_id is null and
605 p_cvg_amt_calc_mthd_id is null) then
606 --
607 fnd_message.set_name('BEN','BEN_91557_BENVRBRT_INPT_BT');
608 fnd_message.set_token('PACKAGE',l_package);
609 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
610 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
611 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
612 fnd_message.set_token('PL_ID',to_char(p_pl_id));
613 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
614 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
615 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
616 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
617 fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID',to_char(p_elig_per_elctbl_chc_id));
618 fnd_message.raise_error;
619 --
620 end if;
621 -- bug 1210355
622 g_vrbl_mlt_code := null;
623
624 --
625 -- FONM variables initialization
626 --
627 if ben_manage_life_events.fonm = 'Y' then
628 --
629 l_fonm_cvg_strt_dt := nvl(ben_manage_life_events.g_fonm_rt_strt_dt,
630 ben_manage_life_events.g_fonm_cvg_strt_dt);
631 --
632 end if;
633 --
634 l_coverage_value := p_bnft_amt;
635 if p_calc_only_rt_val_flag then
636 --
637 l_epe.pl_id := p_pl_id;
638 l_epe.pgm_id := p_pgm_id;
639 l_epe.pl_typ_id := p_pl_typ_id;
640 l_epe.oipl_id := p_oipl_id;
641 l_epe.per_in_ler_id := p_per_in_ler_id;
642 l_epe.ler_id := p_ler_id;
643 l_epe.business_group_id := p_business_group_id;
644 --
645 --
646 -- Check if the context global is populated
647 --
648 elsif ben_epe_cache.g_currepe_row.elig_per_elctbl_chc_id is not null
649 then
650 --
651 l_epe.pgm_id := ben_epe_cache.g_currepe_row.pgm_id;
652 l_epe.pl_typ_id := ben_epe_cache.g_currepe_row.pl_typ_id;
653 l_epe.pl_id := ben_epe_cache.g_currepe_row.pl_id;
654 l_epe.oipl_id := ben_epe_cache.g_currepe_row.oipl_id;
655 l_epe.business_group_id := ben_epe_cache.g_currepe_row.business_group_id;
656 l_epe.per_in_ler_id := ben_epe_cache.g_currepe_row.per_in_ler_id;
657 l_epe.ler_id := ben_epe_cache.g_currepe_row.ler_id;
658 --
659 --
660 -- Check if the context row is populated
661 --
662 elsif p_currepe_row.elig_per_elctbl_chc_id is not null
663 then
664 --
665 l_epe.pgm_id := p_currepe_row.pgm_id;
666 l_epe.pl_typ_id := p_currepe_row.pl_typ_id;
667 l_epe.pl_id := p_currepe_row.pl_id;
668 l_epe.oipl_id := p_currepe_row.oipl_id;
669 l_epe.business_group_id := p_currepe_row.business_group_id;
670 l_epe.per_in_ler_id := p_currepe_row.per_in_ler_id;
671 l_epe.ler_id := p_currepe_row.ler_id;
672 --
673 else
674 --
675 open c_epe;
676 --
677 fetch c_epe into l_epe;
678 --
679 if c_epe%notfound then
680 --
681 close c_epe;
682 fnd_message.set_name('BEN','BEN_91558_BENVRBRT_EPE_NF');
683 fnd_message.set_token('PACKAGE',l_package);
684 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
685 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
686 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
687 fnd_message.raise_error;
688 --
689 end if;
690 --
691 close c_epe;
692 --
693 end if;
694 --
695 hr_utility.set_location(l_package||' p_abr_id NN ',10);
696 if p_acty_base_rt_id is not null then
697 --
698 -- FONM
699 open c_avr(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)))
700 ;
701 --
702 fetch c_avr into l_avr;
703 --
704 if c_avr%found then
705 --
706 l_avr_found := true;
707 --
708 open c_prem_abr(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
709 fetch c_prem_abr into l_actl_prem_id;
710 close c_prem_abr;
711 --
712 end if;
713 --
714 close c_avr;
715 --
716 end if;
717 --
718 hr_utility.set_location(l_package||' p_aprm_id NN ',10);
719 if p_actl_prem_id is not null then
720 --
721 l_actl_prem_id := p_actl_prem_id;
722 --
723 open c_apv(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
724 --
725 fetch c_apv into l_apv;
726 --
727 if c_apv%found then
728 --
729 l_apv_found := true;
730 --
731 end if;
732 --
733 close c_apv;
734 --
735 end if;
736 --
737 hr_utility.set_location(l_package||' p_cacm_id NN ',10);
738 if p_cvg_amt_calc_mthd_id is not null then
739 --
740 open c_bvr(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
741 --
742 fetch c_bvr into l_bvr;
743 --
744 if c_bvr%found then
745 --
746 l_bvr_found := true;
747 --
748 end if;
749 --
750 close c_bvr;
751 --
752 end if;
753 --
754 -- if one of these are found, then we are dealing with profiles, not rules.
755 --
756 hr_utility.set_location(l_package||' l_avr_found OR ',10);
757 if l_avr_found or l_apv_found or l_bvr_found then
758 --
759 hr_utility.set_location(l_package||' BERP_MN ',10);
760 ben_evaluate_rate_profiles.main
761 (p_currepe_row => p_currepe_row
762 ,p_per_row => p_per_row
763 ,p_asg_row => p_asg_row
764 ,p_ast_row => p_ast_row
765 ,p_adr_row => p_adr_row
766 ,p_person_id => p_person_id
767 ,p_acty_base_rt_id => p_acty_base_rt_id
768 ,p_actl_prem_id => p_actl_prem_id
769 ,p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id
770 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
771 ,p_effective_date => p_effective_date -- FONM : benrtprf handles it.
772 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
773 ,p_calc_only_rt_val_flag => p_calc_only_rt_val_flag
774 ,p_pgm_id => l_epe.pgm_id
775 ,p_pl_id => l_epe.pl_id
776 ,p_pl_typ_id => l_epe.pl_typ_id
777 ,p_oipl_id => l_epe.oipl_id
778 ,p_per_in_ler_id => l_epe.per_in_ler_id
779 ,p_ler_id => l_epe.ler_id
780 ,p_business_group_id => l_epe.business_group_id
781 ,p_vrbl_rt_prfl_id => l_vrbl_rt_prfl_id
782 ); -- this is output
783 hr_utility.set_location(l_package||' Dn BERP_MN ',10);
784 --
785 -- when l_vrbl_rt_prfl_id has no value in it, the person failed the criteria
786 -- and get out! So much for one exit point!
787 --
788 if l_vrbl_rt_prfl_id is null then
789 --
790 return;
791 --
792 end if;
793 --
794 -- All profile criteria was met!
795 --
796 if l_epe.oipl_id is not null then
797 open c_opt(l_epe.oipl_id, nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
798 fetch c_opt into l_opt;
799 close c_opt;
800 end if;
801 --
802 open c_vpf(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
803 --
804 fetch c_vpf into l_vpf;
805 --
806 if c_vpf%notfound then
807 --
808 close c_vpf;
809 fnd_message.set_name('BEN','BEN_91559_BENVRBRT_VPF_NF');
810 fnd_message.set_token('PACKAGE',l_package);
811 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
812 fnd_message.set_token('PGM_ID',to_char(l_epe.pgm_id));
813 fnd_message.set_token('PL_TYP_ID',to_char(l_epe.pl_typ_id));
814 fnd_message.set_token('PL_ID',to_char(l_epe.pl_id));
815 fnd_message.set_token('OIPL_ID',to_char(l_epe.oipl_id));
816 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
817 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
818 fnd_message.set_token('PER_IN_LER_ID',to_char(l_epe.per_in_ler_id));
819 fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID',
820 to_char(p_elig_per_elctbl_chc_id));
821 fnd_message.raise_error;
822 --
823 end if;
824 --
825 close c_vpf;
826 hr_utility.set_location(l_package||' close c_vpf ',10);
827 --
828 if l_vpf.mlt_cd in ('CVG','CLANDCVG','APANDCVG','PRNTANDCVG') then
829 --
830 -- If the rates are being calculated for dummy imputed
831 -- income plan then do not raise error even if the
832 -- enrollment benefit row is not created, let the enrt row have
833 -- null val. On enrollment rate is recalculated anyway.
834 --
835 hr_utility.set_location(' plan id ' || p_pl_iD , 99);
836 open c_imp_inc_plan(l_epe.pl_id,
837 nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
838 fetch c_imp_inc_plan into l_imp_inc_plan;
839 close c_imp_inc_plan;
840 --
841 hr_utility.set_location(' imp_inc_plan ' || l_imp_inc_plan , 99);
842 if l_imp_inc_plan = 'N' then
843 --
844 if l_coverage_value is null then
845 --
846 if p_enrt_bnft_id is null then
847 --
848 fnd_message.set_name('BEN','BEN_91560_BENVRBRT_INPT_EB');
849 fnd_message.set_token('PACKAGE',l_package);
850 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
851 fnd_message.set_token('PGM_ID',to_char(l_epe.pgm_id));
852 fnd_message.set_token('PL_TYP_ID',to_char(l_epe.pl_typ_id));
853 fnd_message.set_token('PL_ID',to_char(l_epe.pl_id));
854 fnd_message.set_token('OIPL_ID',to_char(l_epe.oipl_id));
855 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
856 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
857 fnd_message.set_token('PER_IN_LER_ID',to_char(l_epe.per_in_ler_id));
858 fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID',
859 to_char(p_elig_per_elctbl_chc_id));
860 fnd_message.set_token('MLT_CD',l_vpf.mlt_cd);
861 fnd_message.raise_error;
862 --
863 end if;
864 --
865 open c_enb;
866 --
867 fetch c_enb into l_coverage_value;
868 --
869 if c_enb%notfound then
870 --
871 close c_enb;
872 fnd_message.set_name('BEN','BEN_91561_BENVRBRT_ENB_NF');
873 fnd_message.set_token('PACKAGE',l_package);
874 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
875 fnd_message.set_token('PGM_ID',to_char(l_epe.pgm_id));
876 fnd_message.set_token('PL_TYP_ID',to_char(l_epe.pl_typ_id));
877 fnd_message.set_token('PL_ID',to_char(l_epe.pl_id));
878 fnd_message.set_token('OIPL_ID',to_char(l_epe.oipl_id));
879 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
880 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
881 fnd_message.set_token('PER_IN_LER_ID',to_char(l_epe.per_in_ler_id));
882 fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID',
883 to_char(p_elig_per_elctbl_chc_id));
884 fnd_message.set_token('MLT_CD',l_vpf.mlt_cd);
885 fnd_message.raise_error;
886 --
887 end if;
888 --
889 close c_enb;
890 --
891 end if;
892 --
893 end if;
894 --
895 end if;
896 --
897 hr_utility.set_location(l_package||' l_vpf.mlt_cd 1 ',10);
898 if l_vpf.mlt_cd in ('CL','CLANDCVG','FLFXPCL') then
899 --
900 if l_vpf.comp_lvl_fctr_id is null then
901 --
902 fnd_message.set_name('BEN','BEN_91565_BENVRBRT_NULL_CLF');
903 fnd_message.set_token('PACKAGE',l_package);
904 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
905 fnd_message.set_token('PGM_ID',to_char(l_epe.pgm_id));
906 fnd_message.set_token('PL_TYP_ID',to_char(l_epe.pl_typ_id));
907 fnd_message.set_token('PL_ID',to_char(l_epe.pl_id));
908 fnd_message.set_token('OIPL_ID',to_char(l_epe.oipl_id));
909 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
910 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
911 fnd_message.set_token('PER_IN_LER_ID',to_char(l_epe.per_in_ler_id));
912 fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID',
913 to_char(p_elig_per_elctbl_chc_id));
914 fnd_message.set_token('MLT_CD',l_vpf.mlt_cd);
915 fnd_message.raise_error;
916 --
917 end if;
918 --
919 ben_derive_factors.determine_compensation
920 (p_comp_lvl_fctr_id => l_vpf.comp_lvl_fctr_id,
921 p_person_id => p_person_id,
922 p_pgm_id => l_epe.pgm_id,
923 p_pl_id => l_epe.pl_id,
924 p_oipl_id => l_epe.oipl_id,
925 p_per_in_ler_id => l_epe.per_in_ler_id,
926 p_business_group_id => l_epe.business_group_id,
927 p_perform_rounding_flg => true,
928 p_effective_date => p_effective_date, -- FONM : pass overloaded date
929 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
930 p_cal_for => 'R',
931 p_value => l_compensation_value,
932 p_fonm_cvg_strt_dt => l_fonm_cvg_strt_dt );
933 --
934 if l_compensation_value is null then
935 --
936 return; -- for some reason this person has no comp so return
937 --
938 end if;
939 --
940 end if;
941 --
942 hr_utility.set_location(' checking if program or plan ',70);
943 --
944 if l_epe.pgm_id is not null then
945 open c_pgm(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
946 fetch c_pgm into l_pgm;
947 if c_pgm%notfound then
948 close c_pgm;
949 fnd_message.set_name('BEN','BEN_92410_BENACTBR_PGM_NF');
950 fnd_message.set_token('ID',to_char(l_epe.pgm_id));
951 fnd_message.set_token('PACKAGE',l_package);
952 fnd_message.raise_error;
953 end if;
954 close c_pgm;
955 l_acty_ref_perd_cd := l_pgm.acty_ref_perd_cd;
956 else
957 hr_utility.set_location(' testing if plan ',80);
958 open c_pln(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
959 fetch c_pln into l_pln;
960 if c_pln%notfound then
961 close c_pln;
962 fnd_message.set_name('BEN','BEN_92411_BENACTBR_PLN_NF');
963 fnd_message.set_token('ID',to_char(l_epe.pl_id));
964 fnd_message.set_token('PACKAGE',l_package);
965 fnd_message.raise_error;
966 end if;
967 close c_pln;
968 l_acty_ref_perd_cd := l_pln.nip_acty_ref_perd_cd;
969 end if;
970 --
971 hr_utility.set_location(l_package||' l_vpf.mlt_cd 2 ',10);
972 --
973 if l_vpf.mlt_cd in ('AP','APANDCVG') then
974 --
975 open c_enrt_prem;
976 fetch c_enrt_prem into l_actl_prem_value;
977 close c_enrt_prem;
978 --
979 --- Recalculating the premium here instead of fetching from c_enrt_prem
980 --- if the coverage is enterable at enrollment.
981 --- Fix for bug 7003453
982 open c_entr_at_enrt_flag;
983 fetch c_entr_at_enrt_flag into l_entr_at_enrt_flag;
984 close c_entr_at_enrt_flag;
985
986 if l_entr_at_enrt_flag = 'Y' then
987
988 begin
989 ben_determine_actual_premium.g_computed_prem_val := null ;
990 ben_determine_actual_premium.main
991 (p_person_id => p_person_id,
992 p_effective_date => p_effective_date,
993 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
994 p_calc_only_rt_val_flag => p_calc_only_rt_val_flag ,
995 p_pgm_id => p_pgm_id,
996 p_pl_id => p_pl_id,
997 p_pl_typ_id => p_pl_typ_id,
998 p_oipl_id => p_oipl_id,
999 p_per_in_ler_id => p_per_in_ler_id,
1000 p_ler_id => p_ler_id,
1001 p_bnft_amt => p_bnft_amt,
1002 p_business_group_id => p_business_group_id );
1003
1004 hr_utility.set_location('premium re calculation over ',555666);
1005 l_actl_prem_value := ben_determine_actual_premium.g_computed_prem_val ;
1006 hr_utility.set_location('re calculation premium'||l_actl_prem_value,555666);
1007 ben_determine_actual_premium.g_computed_prem_val := null ;
1008
1009 end;
1010 end if;
1011 --- end Fix for bug 7003453
1012 if l_actl_prem_value is null then
1013 --
1014 return; -- -- soft error do not stop processing.
1015 --
1016 end if;
1017 --
1018 if l_acty_ref_perd_cd = 'PWK' then
1019 l_actl_prem_value := (l_actl_prem_value * 12) / 52;
1020 elsif l_acty_ref_perd_cd = 'BWK' then
1021 l_actl_prem_value := (l_actl_prem_value * 12) / 26;
1022 elsif l_acty_ref_perd_cd = 'MO' then
1023 null; -- premiums are always monthly, so nothing to do
1024 elsif l_acty_ref_perd_cd = 'SMO' then
1025 l_actl_prem_value := (l_actl_prem_value * 12) / 6;
1026 elsif l_acty_ref_perd_cd = 'PQU' then
1027 l_actl_prem_value := (l_actl_prem_value * 12) / 4;
1028 elsif l_acty_ref_perd_cd = 'SAN' then
1029 l_actl_prem_value := (l_actl_prem_value * 12) / 2;
1030 elsif l_acty_ref_perd_cd = 'PYR' then
1031 l_actl_prem_value := l_actl_prem_value * 12;
1032 elsif l_acty_ref_perd_cd = 'PHR' then
1033 --
1034 l_pay_annualization_factor := to_number(fnd_profile.value('BEN_HRLY_ANAL_FCTR'));
1035 if l_pay_annualization_factor is null then
1036 l_pay_annualization_factor := 2080;
1037 end if;
1038 --
1039 l_actl_prem_value := l_actl_prem_value * 12 / l_pay_annualization_factor;
1040 --
1041
1042 else
1043 fnd_message.set_name('BEN','BEN_92412_UKN_ACTY_REF_PERD');
1044 fnd_message.set_token('PROC',l_package);
1045 fnd_message.set_token('VARIABLE',l_acty_ref_perd_cd);
1046 hr_utility.set_location (' FNDMS_RE 2 ',150);
1047 fnd_message.raise_error;
1048 end if;
1049 --
1050 end if;
1051 --
1052 --
1053 -- We now should have everything to do the calculation
1054 --
1055 -- Flat Amount
1056 --
1057 hr_utility.set_location(l_package||' rt_typ_calc ELSIFs ',10);
1058 if l_vpf.mlt_cd = 'FLFX' then
1059 --
1060 p_val := l_vpf.val;
1061 --
1062 elsif l_vpf.mlt_cd = 'CL' then
1063 benutils.rt_typ_calc
1064 (p_rt_typ_cd => l_vpf.rt_typ_cd
1065 ,p_val => l_vpf.val
1066 ,p_val_2 => l_compensation_value
1067 ,p_calculated_val => p_val);
1068
1069 elsif l_vpf.mlt_cd = 'CVG' then
1070 benutils.rt_typ_calc
1071 (p_rt_typ_cd => l_vpf.bnft_rt_typ_cd
1072 ,p_val => l_vpf.val
1073 ,p_val_2 => l_coverage_value
1074 ,p_calculated_val => p_val);
1075
1076 elsif l_vpf.mlt_cd = 'AP' then
1077 benutils.rt_typ_calc
1078 (p_rt_typ_cd => l_vpf.rt_typ_cd
1079 ,p_val => l_vpf.val
1080 ,p_val_2 => l_actl_prem_value
1081 ,p_calculated_val => p_val);
1082
1083
1084 elsif l_vpf.mlt_cd = 'FLFXPCL' then
1085 benutils.rt_typ_calc
1086 (p_rt_typ_cd => l_vpf.rt_typ_cd
1087 ,p_val => l_vpf.val
1088 ,p_val_2 => l_compensation_value
1089 ,p_calculated_val => p_val);
1090
1091 elsif l_vpf.mlt_cd = 'CLANDCVG' then
1092 benutils.rt_typ_calc
1093 (p_rt_typ_cd => l_vpf.rt_typ_cd
1094 ,p_val => l_vpf.val
1095 ,p_val_2 => l_compensation_value
1096 ,p_calculated_val => l_value);
1097
1098 --
1099 -- now take l_value and apply it against the coverage
1100 --
1101 benutils.rt_typ_calc
1102 (p_rt_typ_cd => l_vpf.bnft_rt_typ_cd
1103 ,p_val => l_value
1104 ,p_val_2 => l_coverage_value
1105 ,p_calculated_val => p_val);
1106
1107 elsif l_vpf.mlt_cd = 'APANDCVG' then
1108 benutils.rt_typ_calc
1109 (p_rt_typ_cd => l_vpf.rt_typ_cd
1110 ,p_val => l_vpf.val
1111 ,p_val_2 => l_actl_prem_value
1112 ,p_calculated_val => l_value);
1113
1114 --
1115 -- now take l_value and apply it against the coverage
1116 --
1117 benutils.rt_typ_calc
1118 (p_rt_typ_cd => l_vpf.bnft_rt_typ_cd
1119 ,p_val => l_value
1120 ,p_val_2 => l_coverage_value
1121 ,p_calculated_val => p_val);
1122
1123 elsif l_vpf.mlt_cd = 'PRNTANDCVG' then
1124 benutils.rt_typ_calc
1125 (p_rt_typ_cd => l_vpf.rt_typ_cd
1126 ,p_val => l_vpf.val
1127 ,p_val_2 => l_prnt_rt_value
1128 ,p_calculated_val => l_value);
1129
1130 --
1131 -- now take l_value and apply it against the coverage
1132 --
1133 benutils.rt_typ_calc
1134 (p_rt_typ_cd => l_vpf.bnft_rt_typ_cd
1135 ,p_val => l_value
1136 ,p_val_2 => l_coverage_value
1137 ,p_calculated_val => p_val);
1138
1139 --
1140 elsif l_vpf.mlt_cd = 'RL' then
1141 --
1142 -- FONM
1143 open c_asg(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
1144 --
1145 fetch c_asg into l_asg;
1146 --
1147 close c_asg;
1148 --
1149 /* -- 4031733 - Cursor c_state populates l_state variable which is no longer
1150 -- used in the package. Cursor can be commented
1151
1152 if p_person_id is not null then
1153 open c_state(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
1154 fetch c_state into l_state;
1155 close c_state;
1156
1157 if l_state.region_2 is not null then
1158
1159 l_jurisdiction_code :=
1160 pay_mag_utils.lookup_jurisdiction_code
1161 (p_state => l_state.region_2);
1162
1163 end if;
1164
1165 end if;
1166 */
1167 -- Call formula initialise routine
1168 --greatest
1169 l_outputs := benutils.formula
1170 (p_formula_id => l_vpf.val_calc_rl,
1171 p_effective_date => nvl(l_fonm_cvg_strt_dt,greatest(nvl(p_lf_evt_ocrd_dt,p_effective_date),p_effective_date)), -- Bug 6219465
1172 p_business_group_id => l_epe.business_group_id,
1173 p_assignment_id => l_asg.assignment_id,
1174 p_organization_id => l_asg.organization_id,
1175 p_pgm_id => l_epe.pgm_id,
1176 p_pl_id => l_epe.pl_id,
1177 p_pl_typ_id => l_epe.pl_typ_id,
1178 p_opt_id => l_opt.opt_id,
1179 p_ler_id => l_epe.ler_id,
1180 p_acty_base_rt_id => p_acty_base_rt_id,
1181 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1182 p_jurisdiction_code => l_jurisdiction_code,
1183 -- FONM
1184 p_param1 => 'BEN_IV_RT_STRT_DT',
1185 p_param1_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_rt_strt_dt),
1186 p_param2 => 'BEN_IV_CVG_STRT_DT',
1187 p_param2_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_cvg_strt_dt));
1188 --
1189 p_val := l_outputs(l_outputs.first).value;
1190 --
1191 else
1192 --
1193 fnd_message.set_name('BEN','BEN_91572_BENVRBRT_MLT_CD');
1194 hr_utility.set_location(l_package||' FND 91572 ',10);
1195 fnd_message.set_token('PACKAGE',l_package);
1196 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
1197 fnd_message.set_token('PGM_ID',to_char(l_epe.pgm_id));
1198 fnd_message.set_token('PL_TYP_ID',to_char(l_epe.pl_typ_id));
1199 fnd_message.set_token('PL_ID',to_char(l_epe.pl_id));
1200 fnd_message.set_token('OIPL_ID',to_char(l_epe.oipl_id));
1201 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
1202 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
1203 fnd_message.set_token('PER_IN_LER_ID',to_char(l_epe.per_in_ler_id));
1204 fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID',
1205 to_char(p_elig_per_elctbl_chc_id));
1206 fnd_message.set_token('MLT_CD',l_vpf.mlt_cd);
1207 fnd_message.raise_error;
1208 --
1209 end if; -- mult_cd
1210 --
1211 -- assign other outputs
1212 --
1213 p_mx_elcn_val := l_vpf.mx_elcn_val;
1214 p_mn_elcn_val := l_vpf.mn_elcn_val;
1215 p_dflt_elcn_val := l_vpf.dflt_elcn_val;
1216 p_incrmnt_elcn_val := l_vpf.incrmnt_elcn_val;
1217 p_ultmt_upr_lmt := l_vpf.ultmt_upr_lmt ;
1218 p_ultmt_lwr_lmt := l_vpf.ultmt_lwr_lmt ;
1219 p_ultmt_upr_lmt_calc_rl := l_vpf.ultmt_upr_lmt_calc_rl ;
1220 p_ultmt_lwr_lmt_calc_rl := l_vpf.ultmt_lwr_lmt_calc_rl ;
1221 p_ann_mn_elcn_val := l_vpf.ann_mn_elcn_val;
1222 p_ann_mx_elcn_val := l_vpf.ann_mx_elcn_val;
1223 --
1224 p_tx_typ_cd := l_vpf.tx_typ_cd;
1225 p_acty_typ_cd := l_vpf.acty_typ_cd;
1226 p_vrbl_rt_trtmt_cd := l_vpf.vrbl_rt_trtmt_cd;
1227
1228 -- bug 1210355
1229 g_vrbl_mlt_code := l_vpf.mlt_cd;
1230 --
1231 else -- profile not found so it uses rules.
1232 --
1233 -- FONM
1234 open c_asg(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)))
1235 ;
1236 --
1237 fetch c_asg into l_asg;
1238 --
1239 close c_asg;
1240 --
1241
1242 /* -- 4031733 - Cursor c_state populates l_state variable which is no longer
1243 -- used in the package. Cursor can be commented
1244
1245 if p_person_id is not null then
1246
1247 open c_state(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
1248 fetch c_state into l_state;
1249 close c_state;
1250
1251 if l_state.region_2 is not null then
1252
1253 l_jurisdiction_code :=
1254 pay_mag_utils.lookup_jurisdiction_code
1255 (p_state => l_state.region_2);
1256 end if;
1257 end if;
1258 */
1259
1260 if p_acty_base_rt_id is not null then
1261 --
1262 for l_vrr in c_vrr(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date))) loop
1263 --
1264 -- Call formula initialise routine
1265 --
1266 l_outputs := benutils.formula
1267 (p_formula_id => l_vrr.formula_id,
1268 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
1269 p_business_group_id => l_epe.business_group_id,
1270 p_assignment_id => l_asg.assignment_id,
1271 p_organization_id => l_asg.organization_id,
1272 p_pgm_id => l_epe.pgm_id,
1273 p_pl_id => l_epe.pl_id,
1274 p_pl_typ_id => l_epe.pl_typ_id,
1275 p_opt_id => l_opt.opt_id,
1276 p_ler_id => l_epe.ler_id,
1277 p_acty_base_rt_id => p_acty_base_rt_id,
1278 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1279 p_jurisdiction_code => l_jurisdiction_code,
1280 p_param1 => 'BEN_IV_RT_STRT_DT',
1281 p_param1_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_rt_strt_dt),
1282 p_param2 => 'BEN_IV_CVG_STRT_DT',
1283 p_param2_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_cvg_strt_dt));
1284 --
1285 if l_outputs(l_outputs.first).value is not null then
1286 --
1287 p_val := l_outputs(l_outputs.first).value;
1288 exit;
1289 --
1290 end if;
1291 --
1292 end loop;
1293 --
1294 elsif p_actl_prem_id is not null then
1295 --
1296 -- Call formula initialise routine
1297 --
1298 open c_ava(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
1299 fetch c_ava into l_ava;
1300 if l_ava.vrbl_rt_add_on_calc_rl is not null then
1301
1302 l_outputs := benutils.formula
1303 (p_formula_id => l_ava.vrbl_rt_add_on_calc_rl,
1304 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
1305 p_business_group_id => l_epe.business_group_id,
1306 p_assignment_id => l_asg.assignment_id,
1307 p_organization_id => l_asg.organization_id,
1308 p_pgm_id => l_epe.pgm_id,
1309 p_pl_id => l_epe.pl_id,
1310 p_pl_typ_id => l_epe.pl_typ_id,
1311 p_opt_id => l_opt.opt_id,
1312 p_ler_id => l_epe.ler_id,
1313 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1314 p_jurisdiction_code => l_jurisdiction_code,
1315 p_param1 => 'BEN_IV_RT_STRT_DT',
1316 p_param1_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_rt_strt_dt),
1317 p_param2 => 'BEN_IV_CVG_STRT_DT',
1318 p_param2_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_cvg_strt_dt));
1319
1320 if l_outputs(l_outputs.first).value is not null then
1321 p_val := l_outputs(l_outputs.first).value;
1322 end if;
1323 end if;
1324 close c_ava;
1325 --
1326 -- Call formula initialise routine nmh
1327 --
1328 for l_avrl in c_avrl(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)))
1329 loop
1330 if l_avrl.formula_id is not null then
1331
1332 l_outputs := benutils.formula
1333 (p_formula_id => l_avrl.formula_id,
1334 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
1335 p_business_group_id => l_epe.business_group_id,
1336 p_assignment_id => l_asg.assignment_id,
1337 p_organization_id => l_asg.organization_id,
1338 p_pgm_id => l_epe.pgm_id,
1339 p_pl_id => l_epe.pl_id,
1340 p_pl_typ_id => l_epe.pl_typ_id,
1341 p_opt_id => l_opt.opt_id,
1342 p_ler_id => l_epe.ler_id,
1343 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1344 p_jurisdiction_code => l_jurisdiction_code,
1345 p_param1 => 'BEN_IV_RT_STRT_DT',
1346 p_param1_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_rt_strt_dt),
1347 p_param2 => 'BEN_IV_CVG_STRT_DT',
1348 p_param2_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_cvg_strt_dt));
1349
1350 if l_outputs(l_outputs.first).value is not null then
1351 p_val := fnd_number.canonical_to_number(l_outputs(l_outputs.first).value);
1352 p_vrbl_rt_trtmt_cd := l_avrl.rt_trtmt_cd ;
1353 exit ;
1354 end if;
1355 end if;
1356 end loop;
1357 -- nmh
1358 --
1359 elsif p_cvg_amt_calc_mthd_id is not null then
1360 --
1361 for l_brr in c_brr(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date))) loop
1362 --
1363 -- Call formula initialise routine
1364 --
1365 l_outputs := benutils.formula
1366 (p_formula_id => l_brr.formula_id,
1367 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
1368 p_business_group_id => l_epe.business_group_id,
1369 p_assignment_id => l_asg.assignment_id,
1370 p_organization_id => l_asg.organization_id,
1371 p_pgm_id => l_epe.pgm_id,
1372 p_pl_id => l_epe.pl_id,
1373 p_pl_typ_id => l_epe.pl_typ_id,
1374 p_opt_id => l_opt.opt_id,
1375 p_ler_id => l_epe.ler_id,
1376 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1377 p_jurisdiction_code => l_jurisdiction_code,
1378 p_param1 => 'BEN_IV_RT_STRT_DT',
1379 p_param1_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_rt_strt_dt),
1380 p_param2 => 'BEN_IV_CVG_STRT_DT',
1381 p_param2_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_cvg_strt_dt));
1382 --
1383 if l_outputs(l_outputs.first).value is not null then
1384 --
1385 p_val := l_outputs(l_outputs.first).value;
1386 exit;
1387 --
1388 end if;
1389 --
1390 end loop;
1391 --
1392 end if;
1393 --
1394 end if;
1395 --
1396 -- Bug 4873847. Calculate CWB values.
1397 --
1398 if nvl(l_env.mode_cd,'~') = 'W' and
1399 l_vpf.no_mn_elcn_val_dfnd_flag = 'Y' and
1400 l_vpf.mlt_cd in ('CL' ) and
1401 l_vpf.acty_typ_cd like 'CWB%' and
1402 l_compensation_value is not null and
1403 l_vpf.vrbl_rt_trtmt_cd = 'RPLC'
1404 then
1405 benutils.rt_typ_calc
1406 (p_rt_typ_cd => l_vpf.rt_typ_cd
1407 ,p_val => p_mn_elcn_val
1408 ,p_val_2 => l_compensation_value
1409 ,p_calculated_val => l_cwb_mn_elcn_val);
1410 --
1411 hr_utility.set_location('l_cwb_mn_elcn_val is '||l_cwb_mn_elcn_val,951);
1412 --
1413 benutils.rt_typ_calc
1414 (p_rt_typ_cd => l_vpf.rt_typ_cd
1415 ,p_val => p_mx_elcn_val
1416 ,p_val_2 => l_compensation_value
1417 ,p_calculated_val => l_cwb_mx_elcn_val);
1418 --
1419 hr_utility.set_location('l_cwb_mx_elcn_val is '|| l_cwb_mx_elcn_val,951);
1420 --
1421 -- 5371364 : Do not Operate (i.e multiply/divide..) the Increment Value.
1422 l_cwb_incrmt_elcn_val := p_incrmnt_elcn_val;
1423 /*
1424 benutils.rt_typ_calc
1425 (p_rt_typ_cd => l_vpf.rt_typ_cd
1426 ,p_val => p_incrmnt_elcn_val
1427 ,p_val_2 => l_compensation_value
1428 ,p_calculated_val => l_cwb_incrmt_elcn_val);
1429 */
1430 --
1431 hr_utility.set_location('l_cwb_incrmt_elcn_val is '|| l_cwb_incrmt_elcn_val,951);
1432 --
1433 benutils.rt_typ_calc
1434 (p_rt_typ_cd => l_vpf.rt_typ_cd
1435 ,p_val => p_dflt_elcn_val
1436 ,p_val_2 => l_compensation_value
1437 ,p_calculated_val => l_cwb_dflt_val);
1438 --
1439 hr_utility.set_location('l_cwb_dflt_val is '|| l_cwb_dflt_val,951);
1440 --
1441 if (l_vpf.rndg_cd is not null or
1442 l_vpf.rndg_rl is not null) then
1443
1444 if l_cwb_mn_elcn_val is not null then
1445 l_rounded_value := benutils.do_rounding
1446 (p_rounding_cd => l_vpf.rndg_cd,
1447 p_rounding_rl => l_vpf.rndg_rl,
1448 p_value => l_cwb_mn_elcn_val,
1449 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date));
1450 l_cwb_mn_elcn_val := l_rounded_value;
1451 end if;
1452 --
1453 if l_cwb_mx_elcn_val is not null then
1454 l_rounded_value := benutils.do_rounding
1455 (p_rounding_cd => l_vpf.rndg_cd,
1456 p_rounding_rl => l_vpf.rndg_rl,
1457 p_value => l_cwb_mx_elcn_val,
1458 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date));
1459 l_cwb_mx_elcn_val := l_rounded_value;
1460 end if;
1461 --
1462 if l_cwb_dflt_val is not null then
1463 l_rounded_value := benutils.do_rounding
1464 (p_rounding_cd => l_vpf.rndg_cd,
1465 p_rounding_rl => l_vpf.rndg_rl,
1466 p_value => l_cwb_dflt_val,
1467 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date));
1468 l_cwb_dflt_val := l_rounded_value;
1469 end if;
1470 end if;
1471 --
1472 p_mx_elcn_val := nvl(l_cwb_mx_elcn_val, p_mx_elcn_val);
1473 p_mn_elcn_val := nvl(l_cwb_mn_elcn_val, p_mn_elcn_val);
1474 p_incrmnt_elcn_val := nvl(l_cwb_incrmt_elcn_val,p_incrmnt_elcn_val);
1475 p_dflt_elcn_val := nvl(l_cwb_dflt_val, p_dflt_elcn_val);
1476 p_val := p_dflt_elcn_val; -- Bug 7331668
1477 --
1478 end if;
1479 --
1480 -- End Bug 4873847.
1481 --
1482 hr_utility.set_location('doing rounding '||l_package,10);
1483 --
1484 if (l_vpf.rndg_cd is not null or
1485 l_vpf.rndg_rl is not null)
1486 and p_val is not null then
1487 --
1488 l_rounded_value := benutils.do_rounding
1489 (p_rounding_cd => l_vpf.rndg_cd,
1490 p_rounding_rl => l_vpf.rndg_rl,
1491 p_value => p_val,
1492 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date));
1493 --
1494 p_val := l_rounded_value;
1495 --
1496 end if;
1497 --
1498 -- Bug 2260440 : determine assignment id, as it may be used by formula.
1499 --
1500 if l_asg.assignment_id is null and
1501 (l_vpf.upr_lmt_calc_rl is not null or l_vpf.lwr_lmt_calc_rl is not null )
1502 then
1503 -- FONM
1504 open c_asg(nvl(l_fonm_cvg_strt_dt, nvl(p_lf_evt_ocrd_dt,p_effective_date)));
1505 --
1506 fetch c_asg into l_asg;
1507 --
1508 close c_asg;
1509 end if;
1510 --
1511 hr_utility.set_location('Floor/Ceiling Rule Checking'||l_package,30);
1512 benutils.limit_checks
1513 (p_upr_lmt_val => l_vpf.upr_lmt_val,
1514 p_lwr_lmt_val => l_vpf.lwr_lmt_val,
1515 p_upr_lmt_calc_rl => l_vpf.upr_lmt_calc_rl,
1516 p_lwr_lmt_calc_rl => l_vpf.lwr_lmt_calc_rl,
1517 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
1518 p_business_group_id => l_epe.business_group_id,
1519 p_assignment_id => l_asg.assignment_id,
1520 p_organization_id => l_asg.organization_id,
1521 p_pgm_id => l_epe.pgm_id,
1522 p_pl_id => l_epe.pl_id,
1523 p_pl_typ_id => l_epe.pl_typ_id,
1524 p_opt_id => l_opt.opt_id,
1525 p_ler_id => l_epe.ler_id,
1526 p_state => l_state.region_2,
1527 p_acty_base_rt_id => p_acty_base_rt_id,
1528 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1529 p_val => p_val);
1530 --
1531 hr_utility.set_location ('Leaving '||l_package,10);
1532 --
1533 end main;
1534 --
1535 end ben_determine_variable_rates;