[Home] [Help]
PACKAGE BODY: APPS.BEN_DETERMINE_ACTUAL_PREMIUM
Source
1 Package Body BEN_DETERMINE_ACTUAL_PREMIUM as
2 /* $Header: benacprm.pkb 120.8.12020000.2 2013/01/03 11:40:03 pvelvano ship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 Name:
11 Determine Actual Premiums
12
13 Purpose:
14 This program determines the actual premium used for rates calculations.
15
16 History:
17 Date Who Version What?
18 ---- --- ------- -----
19 20 Apr 97 Ty Hayden 110.0 Created.
20 16 Jun 98 T Guy 110.1 Removed other exception.
21 25 Jun 98 T Guy 110.2 Replaced all occurrences of
22 'PER10' with 'PERTEN'
23 11 Aug 98 Ty Hayden 110.3 Added mlt cd NSVU
24 08 Oct 98 T Guy 115.3 Fixed call to determine varibable
25 rates. added message numbers and
26 debugging messages.
27 22 Oct 98 T Guy 115.4 removed show errors statement.
28 25 Oct 98 T Guy 115.4 added mlt_cd = RL
29 18 Jan 99 G Perry 115.6 LED V ED
30 09 Mar 99 G Perry 115.7 IS to AS.
31 07 Apr 99 mhoyes 115.8 Un-datetrack of per_in_ler_f changes.
32 - Removed DT restriction from
33 - main/c_epe
34 04 May 99 shdas 115.9 Added contexts to rule calls.
35 27 may 99 maagrawa 115.10 Modified the procedure to call without
36 the chc_id and pass the reqd.
37 values as parameters.
38 25 Jun 99 T Guy 115.11 changed to be called directly from
39 benmngle and added total premium
40 logic
41 2 Jul 99 lmcdonal 115.12 Made use of genutils procs rt_typ_
42 calc and limit_checks.
43 6 Jul 99 T Guy 115.13 Fixed writing of null val in
44 enrt_prem should have been zero.
45 6 Jul 99 T Guy 115.14 Fixed erroring when actual prem
46 was not found.
47 6 Jul 99 T Guy 115.15 Fixed edit check for null pl or
48 oipl id's.
49 7 Jul 99 T Guy 115.16 Fixed rate calc only edit to exit
50 before writing to enrt_prem.
51 16 Jul 99 lmcdonal 115.17 limit_checks parms changed.
52 20 Jul 99 T Guy 115.18 Fixed logic errors for writing
53 to enrt_prem.
54 20 Jul 99 T Guy 115.19 took out nocopy show errors
55 20 Jul 99 T Guy 115.20 genutils -> benutils
56 07 Sep 99 T Guy 115.21 fixed call to
57 pay_mag_utils.lookup_jurisdiction_code
58 16 Sep 99 G Perry 115.22 Fixed c_epe cursor to not error
59 when automatic enrollments have
60 closed the active life event,
61 instead now returns to calling
62 function.
63 29-Oct-99 lmcdonal 115.23 Needed to init l_coverage_value
64 for each epe selected.
65 15-Nov-99 mhoyes 115.24 - Added trace messages for profiing.
66 18-Nov-99 pbodla 115.25 - Added elig_per_elctbl_chc_id as
67 parameter while evaluating val_calc_rl
68 and passed to limit_checks.
69 17-Jan-00 tguy 115.26 Added check for rounding when vrbl
70 rt trtmt cd = rplc do not round at
71 value at this level
72 02 Feb 00 lmcdonal 115.27 Break the computation of premium
73 into a separate procedure so that
74 it can be called independently.
75 Bug 1166174.
76 04-Apr-00 mmogel 115.28 Added tokes to messages to make
77 them more meaningful to the user
78 03-May-00 mhoyes 115.29 Removed request_id join from c_epe.
79 08-Aug-00 pbodla 115.30 - Bug 4948(WWW Bug 1259220)
80 When l_vr_trtmt_cd is null rounding
81 is not applied. So nvl applied
82 around l_vr_trtmt_cd.
83 07-Nov-00 mhoyes 115.31 - Added electable choice context
84 global.
85 - Bulk inserted ben_enrt_prem.
86 22-Nov-00 mhoyes 115.32 - Changed bulk bind composite data
87 structure to a varray. This avoids
88 random composite error on 8.1.6.2.
89 05-Jan-01 kmahendr 115.33 - Added per_in_ler_id parameter to perpil_cache
90 call
91 15 mar 01 tilak 115.34 g_computed_prem_val is added
92 This is used to store the value of the
93 computed premium, whic can be used for
94 calcualtion mode only to get the ammount
95 In this mode ele_chc_id,benefit is not inserted
96 so the global_variable to get the value
97 bug :bug :1676551
98 21-mar-2001 tilak 115.35 ultmt_upr_lmt,ultmt_lwr_lmt is validated
99 02-apr-2001 tilak 115.36 ultmt_upr_lmt_calc_rl,ultmt_lwr_lmt_calc_rl is validated
100 27-aug-2001 tilak 115.37 bug:1949361 jurisdiction code is
101 derived inside benutils.formula.
102 27-Sep-2001 kmahendr 115.38 Bug#1981673-Added parameter ann_mn_elcn_val and
103 ann_mx_elcn_val to ben_determine_variable_rates
104 call
105 08-Jun-2002 pabodla 115.39 Do not select the contingent worker
106 assignment when assignment data is
107 fetched.
108 14-Jun-2002 pabodla 115.41 Added dbdrv command
109 02-Aug-2004 tjesumic 115.44 fonm, determination of fonm_flag changed from global varaible to epe dt
110 08-Sep-2004 tjesumic 115.45 fonm, global fonm_cvg_strt_dt reintialized from epe dt
111 08-Sep-2004 tjesumic 115.46 fonm, global fonm_cvg_strt_dt reintialized from epe dt
112 08-Sep-2004 tjesumic 115.47 fonm, caching clearence
113 15-Nov-2004 kmahendr 115.48 Unrest. enh changes
114 30-dec-2004 nhunur 115.49 4031733 - No need to open cursor c_state.
115 9-Jun-2005 nhunur 115.50 4383988 - do fnd_number.canonical_to_number() to the
116 FF output before assigning to a number variable.
117 03-Oct-05 ssarkar 115.51 4644867 - Added order by clause to cursoe c_asg to Query 'E' assignment first
118 and then others .
119 10-Mar-06 swjain 115.52 In cursor c_asg, added condition to fetch active assignments only
120 10-Mar-06 swjain 115.53 Updated cursor c_asg
121 10-Aug-07 bmanyam 115.54 6330056 : Store all the premiums evaluated into
122 global pl/sql tbl
123 11-Feb-2009 velvanop 115.55 Bug 7414757: Added parameter p_entr_val_at_enrt_flag.
124 VAPRO rates which are 'Enter value at Enrollment', Form field
125 should allow the user to enter a value during enrollment.
126 14-Dec-2009 sallumwa 115.56 Bug 9135034 : Get the FONM coverage date if the current LE is an
127 FONM Event.
128 14-Jun-2010 sagnanas 115.57 Bug 9774358 - Passed eletable choice info
129 3-Jan-2012 velvanop 115.58 Bug 16013053 - If Premium setup is 'Multiple of Coverage' and VAL column is NULL in BEN_ENRT_BNFT table after
130 processing the LE, consider DFLT_VAL while calculating coverage
131 */
132 --------------------------------------------------------------------------------
133 --
134 g_package varchar2(80) := 'ben_determine_actual_premium';
135
136 procedure calc_fonm_dates( p_effective_date in date
137 ,p_business_group_id in number
138 ,p_per_in_ler_id in number
139 ,p_person_id in number
140 ,p_inst_set in ben_epe_cache.g_pilepe_inst_row
141 ,p_calc_type in varchar2
142 ,p_cvg_strt_dt out nocopy date
143 ,p_rt_strt_dt out nocopy date )
144 is
145
146 l_package varchar2(80) := g_package||'.calc_fonm_dates';
147
148 l_enrt_cvg_strt_dt date ;
149 l_rt_strt_dt date ;
150 l_dummy_d date ;
151 l_dummy_v varchar2(30);
152 l_dummy_n number;
153 Begin
154
155 hr_utility.set_location ('Entering ' ||l_package,10);
156
157 ben_determine_date.rate_and_coverage_dates
158 (p_which_dates_cd => nvl(p_calc_type,'B')
159 ,p_date_mandatory_flag => 'N'
160 ,p_compute_dates_flag => 'Y'
161 ,p_elig_per_elctbl_chc_id => p_inst_set.elig_per_elctbl_chc_id
162 ,p_business_group_id => p_business_group_id
163 ,P_PER_IN_LER_ID => p_per_in_ler_id
164 ,P_PERSON_ID => p_person_id
165 ,P_PGM_ID => p_inst_set.pgm_id
166 ,P_PL_ID => p_inst_set.pl_id
167 ,P_OIPL_ID => p_inst_set.oipl_id
168 ,P_LEE_RSN_ID => p_inst_set.lee_rsn_id
169 ,P_ENRT_PERD_ID => p_inst_set.enrt_perd_id
170 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt
171 ,p_enrt_cvg_strt_dt_cd => l_dummy_v
172 ,p_enrt_cvg_strt_dt_rl => l_dummy_n
173 ,p_rt_strt_dt => l_rt_strt_dt
174 ,p_rt_strt_dt_cd => l_dummy_v
175 ,p_rt_strt_dt_rl => l_dummy_n
176 ,p_enrt_cvg_end_dt => l_dummy_d
177 ,p_enrt_cvg_end_dt_cd => l_dummy_v
178 ,p_enrt_cvg_end_dt_rl => l_dummy_n
179 ,p_rt_end_dt => l_dummy_d
180 ,p_rt_end_dt_cd => l_dummy_v
181 ,p_rt_end_dt_rl => l_dummy_n
182 ,p_effective_date => p_effective_date
183 ,p_lf_evt_ocrd_dt => p_effective_date)
184 ;
185
186 p_cvg_strt_dt := l_enrt_cvg_strt_dt ;
187 p_rt_strt_dt := l_rt_strt_dt ;
188
189 hr_utility.set_location ('Leaving ' ||l_package,10);
190
191 End calc_fonm_dates ;
192
193
194 --------------------------------------------------------------------------------
195 -- COMPUTE_PREMIUM
196 --------------------------------------------------------------------------------
197 procedure compute_premium
198 (p_person_id in number,
199 p_lf_evt_ocrd_dt IN date,
200 p_effective_date IN date,
201 p_business_group_id in number,
202 p_per_in_ler_id in number,
203 p_ler_id in number,
204 p_actl_prem_id in number,
205 p_perform_rounding_flg IN boolean default true,
206 p_calc_only_rt_val_flag in boolean default false,
207 p_pgm_id in number,
208 p_pl_typ_id in number,
209 p_pl_id in number,
210 p_oipl_id in number,
211 p_opt_id in number,
212 p_elig_per_elctbl_chc_id in number,
213 p_enrt_bnft_id in number,
214 p_bnft_amt in number,
215 p_prem_val in number,
216 p_mlt_cd in varchar2,
217 p_bnft_rt_typ_cd in varchar2,
218 p_val_calc_rl in number,
219 p_rndg_cd in varchar2,
220 p_rndg_rl in number,
221 p_upr_lmt_val in number,
222 p_lwr_lmt_val in number,
223 p_upr_lmt_calc_rl in number,
224 p_lwr_lmt_calc_rl in number,
225 p_fonm_cvg_strt_dt in date default null ,
226 p_fonm_rt_strt_dt in date default null ,
227 p_computed_val out nocopy number) IS
228
229 l_package varchar2(80) := g_package||'.compute_premium';
230 -- fonm new p_date param added
231 Cursor c_state (p_date date) is
232 select region_2
233 from hr_locations_all loc,per_all_assignments_f asg
234 where loc.location_id = asg.location_id
235 and asg.person_id = p_person_id
236 and asg.assignment_type <> 'C'
237 and asg.primary_flag = 'Y'
238 and p_date between
239 asg.effective_start_date and asg.effective_end_date;
240
241 l_state c_state%rowtype;
242 --
243 -- fonm new p_date param added
244 cursor c_asg(p_date date) is
245 select asg.assignment_id,asg.organization_id,loc.region_2
246 from per_all_assignments_f asg,hr_locations_all loc, per_assignment_status_types ast
247 where asg.person_id = p_person_id
248 and asg.assignment_type <> 'C'
249 and asg.primary_flag = 'Y'
250 and asg.location_id = loc.location_id(+)
251 and asg.assignment_status_type_id = ast.assignment_status_type_id(+)
252 and ast.per_system_status(+) = 'ACTIVE_ASSIGN'
253 --and nvl(p_lf_evt_ocrd_dt,p_effective_date)
254 and p_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 l_asg c_asg%rowtype;
259
260 --------------Bug 9135034
261 cursor c_epe is
262 select epe.fonm_cvg_strt_dt
263 from ben_elig_per_elctbl_chc epe
264 where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id ;
265 l_fonm_cvg_strt_dt1 date;
266 ---------------Bug 9135034
267 l_jurisdiction_code varchar2(30);
268 l_outputs ff_exec.outputs_t;
269 l_variable_val number;
270 l_vr_trtmt_cd varchar(30);
271 l_dummy_number number;
272 l_dummy_char varchar(30);
273 l_ultmt_upr_lmt number ;
274 l_ultmt_lwr_lmt number ;
275 l_ultmt_upr_lmt_calc_rl number ;
276 l_ultmt_lwr_lmt_calc_rl number ;
277 l_vr_ann_mn_elcn_val number;
278 l_vr_ann_mx_elcn_val number;
279 l_entr_val_at_enrt_flag varchar2(10); -- Bug 7414757
280
281 -- bof FONM
282 l_fonm_date date ;
283 --eof FONM
284 begin
285 hr_utility.set_location ('Entering ' ||l_package,10);
286 hr_utility.set_location ('l_apr.mlt_cd -> '||p_mlt_cd,10);
287 --
288 -- get values for rules and limit checking
289 --bof FONM
290 l_fonm_date := nvl(p_lf_evt_ocrd_dt,p_effective_date ) ;
291 -------Bug 9135034
292 open c_epe;
293 fetch c_epe into l_fonm_cvg_strt_dt1;
294 close c_epe;
295 hr_utility.set_location ('l_fonm_cvg_strt_dt1 -> '|| l_fonm_cvg_strt_dt1,10);
296 if l_fonm_cvg_strt_dt1 is not null then
297 ben_manage_life_events.fonm := 'Y';
298 ben_manage_life_events.g_fonm_cvg_strt_dt := l_fonm_cvg_strt_dt1;
299 ben_manage_life_events.g_fonm_rt_strt_dt := p_fonm_rt_strt_dt;
300 end if;
301 hr_utility.set_location ('ben_manage_life_events.fonm -> '||ben_manage_life_events.fonm,10);
302 ------Bug 9135034
303 if ben_manage_life_events.fonm = 'Y' then
304 l_fonm_date := nvl(p_fonm_cvg_strt_dt, l_fonm_date ) ;
305 end if ;
306 --eof FONM
307 /* -- 4031733 - cursor used to populate l_state.region_2
308 -- param for benutils.limit_checks which is not used down the line
309 --
310 open c_state (l_fonm_date);
311 fetch c_state into l_state;
312 close c_state;
313 */
314 open c_asg (l_fonm_date);
315 fetch c_asg into l_asg;
316 close c_asg;
317
318 --if l_asg.region_2 is not null then
319
320 -- l_jurisdiction_code :=
321 -- pay_mag_utils.lookup_jurisdiction_code
322 -- (p_state => l_asg.region_2);
323
324 --end if;
325
326 --
327 -- Flat-Fixed
328 --
329 if p_mlt_cd = 'FLFX' then
330 hr_utility.set_location ('in flfx',20);
331 p_computed_val := p_prem_val;
332 --
333 -- Multiple of Coverage
334 --
335 elsif p_mlt_cd = 'CVG' then
336 hr_utility.set_location ('in cvg',30);
337 if p_bnft_amt is null then
338 if p_enrt_bnft_id is null then
339 hr_utility.set_location ('BEN_91579_BENACPRM_INPT_EB',40);
340 fnd_message.set_name('BEN','BEN_91579_BENACPRM_INPT_EB');
341 fnd_message.set_token('PROC',l_package);
342 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
343 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
344 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
345 fnd_message.set_token('PL_ID',to_char(p_pl_id));
346 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
347 fnd_message.set_token('LF_EVT_OCRD_DT',
348 to_char(p_lf_evt_ocrd_dt));
349 fnd_message.set_token('MLT_CD',p_mlt_cd);
350 fnd_message.raise_error;
351 end if;
352 end if;
353
354 if p_bnft_amt is NULL then
355 --
356 -- Means that cvg to be selected upon enrt
357 --
358 p_computed_val := 0;
359 --
360 -- if coverage value is not null then process as defined
361 --
362 else
363 benutils.rt_typ_calc
364 (p_rt_typ_cd => p_bnft_rt_typ_cd
365 ,p_val => p_prem_val
366 ,p_val_2 => p_bnft_amt
367 ,p_calculated_val => p_computed_val);
368 end if;
369 --
370 -- Rule
371 --
372 elsif p_mlt_cd = 'RL' then
373 --
374 -- Call formula initialise routine
375 hr_utility.set_location ('in rl',10);
376
377 l_outputs := benutils.formula
378 (p_formula_id => p_val_calc_rl,
379 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
380 p_business_group_id=> p_business_group_id,
381 p_assignment_id => l_asg.assignment_id,
382 p_organization_id => l_asg.organization_id,
383 p_pgm_id => p_pgm_id,
384 p_pl_id => p_pl_id,
385 p_pl_typ_id => p_pl_typ_id,
386 p_opt_id => p_opt_id,
387 p_ler_id => p_ler_id,
388 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
389 p_jurisdiction_code=> l_jurisdiction_code,
390 --- Bof FONM
391 p_param1 => 'RT_STRT_DT',
392 p_param1_value => fnd_date.date_to_canonical(p_fonm_rt_strt_dt), -- null is passed, 4 future
393 p_param2 => 'CVG_STRT_DT',
394 p_param2_value => fnd_date.date_to_canonical(p_fonm_cvg_strt_dt)
395 --- Eof FONM
396 );
397 --
398 p_computed_val := fnd_number.canonical_to_number(l_outputs(l_outputs.first).value);
399
400 elsif p_mlt_cd = 'NSVU' then
401 -- Do nothing for NO STANDARD VALUE USED
402 -- Value used comes from Variable rates below.
403 hr_utility.set_location ('in nsvu',10);
404 null;
405 else
406 hr_utility.set_location ('BEN_91584_BENACPRM_MLT_CD',50);
407 fnd_message.set_name('BEN','BEN_91584_BENACPRM_MLT_CD');
408 fnd_message.set_token('PROC',l_package);
409 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
410 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
411 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
412 fnd_message.set_token('PL_ID',to_char(p_pl_id));
413 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
414 fnd_message.set_token('LF_EVT_OCRD_DT',to_char(p_lf_evt_ocrd_dt));
415 fnd_message.set_token('MLT_CD',p_mlt_cd);
416 fnd_message.raise_error;
417 end if;
418 --
419 ----bug : 143393 Limit validation applied to premium
420 ---- before calc VAPRP, after vapro and premium
421 ---- ultimate value of vapro will be applied
422 ---- the same apply to rounding
423
424 -- perform appropriate rounding based on the source table.
425 -- rounding_cd or rule cannot both be null, perform_rounding_flag
426 -- must be true....
427 --
428 -- Bug 4948(WWW Bug 1259220)
429 -- When l_vr_trtmt_cd is null rounding is not applied.
430 -- So nvl applied around l_vr_trtmt_cd.
431 --
432 if (p_rndg_cd is not null or
433 p_rndg_rl is not null) and
434 p_perform_rounding_flg = true and
435 p_computed_val is not null and
436 nvl(l_vr_trtmt_cd, 'XXXX') <> 'RPLC' then
437 hr_utility.set_location ('rounding ',70);
438 p_computed_val := benutils.do_rounding
439 (p_rounding_cd => p_rndg_cd,
440 p_rounding_rl => p_rndg_rl,
441 p_value => p_computed_val,
442 --- fonm
443 p_effective_date => l_fonm_date ) ; --- nvl(p_lf_evt_ocrd_dt,p_effective_date));
444 end if;
445 --
446 -- check upr/lwr limit
447 --
448 hr_utility.set_location('check the limit of prm ',20);
449 benutils.limit_checks
450 (p_upr_lmt_val => p_upr_lmt_val,
451 p_lwr_lmt_val => p_lwr_lmt_val,
452 p_upr_lmt_calc_rl => p_upr_lmt_calc_rl,
453 p_lwr_lmt_calc_rl => p_lwr_lmt_calc_rl,
454 -- fonm
455 p_effective_date => l_fonm_date , -- nvl(p_lf_evt_ocrd_dt,p_effective_date),
456 p_business_group_id => p_business_group_id,
457 p_assignment_id => l_asg.assignment_id,
458 p_organization_id => l_asg.organization_id,
459 p_pgm_id => p_pgm_id,
460 p_pl_id => p_pl_id,
461 p_pl_typ_id => p_pl_typ_id,
462 p_opt_id => p_opt_id,
463 p_ler_id => p_ler_id,
464 p_state => l_state.region_2,
465 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
466 p_val => p_computed_val);
467
468
469 ------------------------------------------
470 -- variable rates
471 ------------------------------------------
472 --
473 ben_determine_variable_rates.main
474 (p_person_id => p_person_id,
475 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
476 p_enrt_bnft_id => p_enrt_bnft_id,
477 p_actl_prem_id => p_actl_prem_id,
478 --
479 p_effective_date => l_fonm_date , ---p_effective_date,
480 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
481 p_calc_only_rt_val_flag => p_calc_only_rt_val_flag,
482 p_pgm_id => p_pgm_id,
483 p_pl_id => p_pl_id,
484 p_oipl_id => p_oipl_id,
485 p_pl_typ_id => p_pl_typ_id,
486 p_per_in_ler_id => p_per_in_ler_id,
487 p_ler_id => p_ler_id,
488 p_business_group_id => p_business_group_id,
489 p_bnft_amt => p_bnft_amt,
490 p_entr_val_at_enrt_flag => l_entr_val_at_enrt_flag, -- Bug 7414757
491 p_val => l_variable_val,
492 p_mn_elcn_val => l_dummy_number,
493 p_mx_elcn_val => l_dummy_number,
494 p_incrmnt_elcn_val => l_dummy_number,
495 p_dflt_elcn_val => l_dummy_number, -- do not apply to premiums
496 p_tx_typ_cd => l_dummy_char,
497 p_acty_typ_cd => l_dummy_char,
498 p_vrbl_rt_trtmt_cd => l_vr_trtmt_cd,
499 p_ann_mn_elcn_val => l_vr_ann_mn_elcn_val,
500 p_ann_mx_elcn_val => l_vr_ann_mx_elcn_val,
501 p_ultmt_upr_lmt => l_ultmt_upr_lmt,
502 p_ultmt_lwr_lmt => l_ultmt_lwr_lmt,
503 p_ultmt_upr_lmt_calc_rl => l_ultmt_upr_lmt_calc_rl,
504 p_ultmt_lwr_lmt_calc_rl => l_ultmt_lwr_lmt_calc_rl);
505 --
506 hr_utility.set_location ('after variable rates',60);
507 hr_utility.set_location ('l_vr_trtmt_cd -> '||l_vr_trtmt_cd,60);
508 hr_utility.set_location ('l_vr_val -> '||l_variable_val,60);
509 hr_utility.set_location ('l_val -> '||p_computed_val,60);
510
511 if p_computed_val is null then
512 l_vr_trtmt_cd := 'RPLC';
513 end if;
514
515 if l_variable_val is not null then
516 --
517 -- Replace
518 if l_vr_trtmt_cd = 'RPLC' then
519 p_computed_val := l_variable_val;
520
521 -- Multiply By
522 elsif l_vr_trtmt_cd = 'MB' then
523 p_computed_val := p_computed_val * l_variable_val;
524
525 -- Subtract From
526 elsif l_vr_trtmt_cd = 'SF' then
527 p_computed_val := p_computed_val - l_variable_val;
528
529 -- Add To
530 elsif l_vr_trtmt_cd = 'ADDTO' then
531 p_computed_val := p_computed_val + l_variable_val;
532 else -- Replace
533 p_computed_val := l_variable_val;
534 end if;
535
536 if l_ultmt_upr_lmt is not null or l_ultmt_lwr_lmt is not null
537 OR l_ultmt_upr_lmt_calc_rl is not null or l_ultmt_lwr_lmt_calc_rl is not null then
538 --- if ultmate limit defined in VAPRO chek the limit
539 hr_utility.set_location('calling limit for ultmt of prem',393);
540 hr_utility.set_location('upper '|| l_ultmt_upr_lmt ||' Lower' || l_ultmt_lwr_lmt,393);
541 hr_utility.set_location('ammount '|| p_computed_val ,393);
542 benutils.limit_checks
543 (p_upr_lmt_val => l_ultmt_upr_lmt,
544 p_lwr_lmt_val => l_ultmt_lwr_lmt,
545 p_upr_lmt_calc_rl => l_ultmt_upr_lmt_calc_rl,
546 p_lwr_lmt_calc_rl => l_ultmt_lwr_lmt_calc_rl,
547 --- fonm
548 p_effective_date => l_fonm_date , --- nvl(p_lf_evt_ocrd_dt,p_effective_date),
549 p_business_group_id => p_business_group_id,
550 p_assignment_id => l_asg.assignment_id,
551 p_organization_id => l_asg.organization_id,
552 p_pgm_id => p_pgm_id,
553 p_pl_id => p_pl_id,
554 p_pl_typ_id => p_pl_typ_id,
555 p_opt_id => p_opt_id,
556 p_ler_id => p_ler_id,
557 p_state => l_state.region_2,
558 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
559 p_val => p_computed_val);
560 hr_utility.set_location('ammount '|| p_computed_val ,393);
561
562 end if ;
563 end if;
564 --
565 /*
566 -- perform appropriate rounding based on the source table.
567 -- rounding_cd or rule cannot both be null, perform_rounding_flag
568 -- must be true....
569 --
570 -- Bug 4948(WWW Bug 1259220)
571 -- When l_vr_trtmt_cd is null rounding is not applied.
572 -- So nvl applied around l_vr_trtmt_cd.
573 --
574 if (p_rndg_cd is not null or
575 p_rndg_rl is not null) and
576 p_perform_rounding_flg = true and
577 p_computed_val is not null and
578 nvl(l_vr_trtmt_cd, 'XXXX') <> 'RPLC' then
579 hr_utility.set_location ('rounding ',70);
580 p_computed_val := benutils.do_rounding
581 (p_rounding_cd => p_rndg_cd,
582 p_rounding_rl => p_rndg_rl,
583 p_value => p_computed_val,
584 -- fonm
585 p_effective_date => l_fonm_date) ; --- nvl(p_lf_evt_ocrd_dt,p_effective_date));
586 end if;
587 --
588 -- check upr/lwr limit
589 --
590 benutils.limit_checks
591 (p_upr_lmt_val => p_upr_lmt_val,
592 p_lwr_lmt_val => p_lwr_lmt_val,
593 p_upr_lmt_calc_rl => p_upr_lmt_calc_rl,
594 p_lwr_lmt_calc_rl => p_lwr_lmt_calc_rl,
595 -- fonm
596 p_effective_date => l_fonm_date , -- nvl(p_lf_evt_ocrd_dt,p_effective_date),
597 p_business_group_id => p_business_group_id,
598 p_assignment_id => l_asg.assignment_id,
599 p_organization_id => l_asg.organization_id,
600 p_pgm_id => p_pgm_id,
601 p_pl_id => p_pl_id,
602 p_pl_typ_id => p_pl_typ_id,
603 p_opt_id => p_opt_id,
604 p_ler_id => p_ler_id,
605 p_state => l_state.region_2,
606 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
607 p_val => p_computed_val);
608 */
609
610 hr_utility.set_location ('Leaving '||l_package,99);
611
612 end compute_premium;
613 --------------------------------------------------------------------------------
614 -- MAIN
615 --------------------------------------------------------------------------------
616 PROCEDURE main
617 ( p_person_id in number,
618 p_effective_date IN date,
619 p_lf_evt_ocrd_dt IN date,
620 p_perform_rounding_flg IN boolean default true,
621 p_calc_only_rt_val_flag in boolean default false,
622 p_pgm_id in number default null,
623 p_pl_id in number default null,
624 p_oipl_id in number default null,
625 p_pl_typ_id in number default null,
626 p_per_in_ler_id in number default null,
627 p_ler_id in number default null,
628 p_bnft_amt in number default null,
629 p_business_group_id in number default null,
630 p_mode in varchar2
631 ) IS
632 --
633 l_package varchar2(80) := g_package||'.main';
634 --
635 l_currepe_set ben_epe_cache.g_pilepe_inst_tbl;
636 --
637 l_val_va benutils.g_number_table := benutils.g_number_table();
638 l_uom_va benutils.g_v2_30_table := benutils.g_v2_30_table();
639 l_elig_per_elctbl_chc_id_va benutils.g_number_table := benutils.g_number_table();
640 l_enrt_bnft_id_va benutils.g_number_table := benutils.g_number_table();
641 l_actl_prem_id_va benutils.g_number_table := benutils.g_number_table();
642 l_business_group_id_va benutils.g_number_table := benutils.g_number_table();
643 --
644 l_epr_attribute_category_va benutils.g_v2_30_table := benutils.g_v2_30_table();
645 l_epr_attribute1_va benutils.g_v2_150_table := benutils.g_v2_150_table();
646 l_epr_attribute2_va benutils.g_v2_150_table := benutils.g_v2_150_table();
647 l_epr_attribute3_va benutils.g_v2_150_table := benutils.g_v2_150_table();
648 l_epr_attribute4_va benutils.g_v2_150_table := benutils.g_v2_150_table();
649 l_epr_attribute5_va benutils.g_v2_150_table := benutils.g_v2_150_table();
650 l_epr_attribute6_va benutils.g_v2_150_table := benutils.g_v2_150_table();
651 l_epr_attribute7_va benutils.g_v2_150_table := benutils.g_v2_150_table();
652 l_epr_attribute8_va benutils.g_v2_150_table := benutils.g_v2_150_table();
653 l_epr_attribute9_va benutils.g_v2_150_table := benutils.g_v2_150_table();
654 l_epr_attribute10_va benutils.g_v2_150_table := benutils.g_v2_150_table();
655 l_epr_attribute11_va benutils.g_v2_150_table := benutils.g_v2_150_table();
656 l_epr_attribute12_va benutils.g_v2_150_table := benutils.g_v2_150_table();
657 l_epr_attribute13_va benutils.g_v2_150_table := benutils.g_v2_150_table();
658 l_epr_attribute14_va benutils.g_v2_150_table := benutils.g_v2_150_table();
659 l_epr_attribute15_va benutils.g_v2_150_table := benutils.g_v2_150_table();
660 l_epr_attribute16_va benutils.g_v2_150_table := benutils.g_v2_150_table();
661 l_epr_attribute17_va benutils.g_v2_150_table := benutils.g_v2_150_table();
662 l_epr_attribute18_va benutils.g_v2_150_table := benutils.g_v2_150_table();
663 l_epr_attribute19_va benutils.g_v2_150_table := benutils.g_v2_150_table();
664 l_epr_attribute20_va benutils.g_v2_150_table := benutils.g_v2_150_table();
665 l_epr_attribute21_va benutils.g_v2_150_table := benutils.g_v2_150_table();
666 l_epr_attribute22_va benutils.g_v2_150_table := benutils.g_v2_150_table();
667 l_epr_attribute23_va benutils.g_v2_150_table := benutils.g_v2_150_table();
668 l_epr_attribute24_va benutils.g_v2_150_table := benutils.g_v2_150_table();
669 l_epr_attribute25_va benutils.g_v2_150_table := benutils.g_v2_150_table();
670 l_epr_attribute26_va benutils.g_v2_150_table := benutils.g_v2_150_table();
671 l_epr_attribute27_va benutils.g_v2_150_table := benutils.g_v2_150_table();
672 l_epr_attribute28_va benutils.g_v2_150_table := benutils.g_v2_150_table();
673 l_epr_attribute29_va benutils.g_v2_150_table := benutils.g_v2_150_table();
674 l_epr_attribute30_va benutils.g_v2_150_table := benutils.g_v2_150_table();
675 --
676 l_object_version_number_va benutils.g_number_table := benutils.g_number_table();
677 --
678 l_request_id_va benutils.g_number_table := benutils.g_number_table();
679 l_program_application_id_va benutils.g_number_table := benutils.g_number_table();
680 l_program_id_va benutils.g_number_table := benutils.g_number_table();
681 l_program_update_date_va benutils.g_date_table := benutils.g_date_table();
682 --
683 l_val number;
684 l_enrt_prem_id number;
685 l_object_version_number number;
686 l_ap_found boolean := true;
687 --
688 -- fonm new parameter of date
689 cursor c_apr_pl(p_pl_id in number,p_date date) is
690 select apr.actl_prem_id,
691 apr.mlt_cd,
692 apr.val,
693 apr.rndg_cd,
694 apr.rndg_rl,
695 apr.rt_typ_cd,
696 apr.bnft_rt_typ_cd,
697 apr.comp_lvl_fctr_id,
698 apr.prem_asnmt_cd,
699 apr.val_calc_rl,
700 apr.upr_lmt_val,
701 apr.upr_lmt_calc_rl,
702 apr.lwr_lmt_val,
703 apr.lwr_lmt_calc_rl,
704 apr.uom
705 from ben_actl_prem_f apr
706 where apr.pl_id = p_pl_id
707 and apr.prem_asnmt_cd = 'ENRT' -- PROC are dealt with in benprplc.pkb
708 and p_date
709 between apr.effective_start_date
710 and apr.effective_end_date;
711 -- Fonm new parameter of date
712 cursor c_apr_oipl(p_oipl_id in number,p_date date) is
713 select apr.actl_prem_id,
714 apr.mlt_cd,
715 apr.val,
716 apr.rndg_cd,
717 apr.rndg_rl,
718 apr.rt_typ_cd,
719 apr.bnft_rt_typ_cd,
720 apr.comp_lvl_fctr_id,
721 apr.prem_asnmt_cd,
722 apr.val_calc_rl,
723 apr.upr_lmt_val,
724 apr.upr_lmt_calc_rl,
725 apr.lwr_lmt_val,
726 apr.lwr_lmt_calc_rl,
727 apr.uom
728 from ben_actl_prem_f apr
729 where apr.oipl_id = p_oipl_id
730 and apr.prem_asnmt_cd = 'ENRT' -- PROC are dealt with in benprplc.pkb
731 and p_date
732 between apr.effective_start_date
733 and apr.effective_end_date;
734 --
735 l_apr c_apr_pl%rowtype;
736 -- fonm p_date parameter added
737 cursor c_opt(p_oipl_id in number, p_date date) is
738 select oipl.opt_id
739 from ben_oipl_f oipl
740 where oipl.oipl_id = p_oipl_id
741 --and p_effective_date between
742 and p_date between
743 oipl.effective_start_date and oipl.effective_end_date;
744
745 l_opt c_opt%rowtype;
746
747 --9774358
748 cursor c_epe is
749 select elig_per_elctbl_chc_id
750 from ben_elig_per_elctbl_chc
751 where per_in_ler_id = p_per_in_ler_id
752 and nvl(pgm_id, -1) = nvl(p_pgm_id ,-1)
753 and nvl(pl_typ_id, -1) = nvl(p_pl_typ_id,-1)
754 and nvl(pl_id, -1) = nvl(p_pl_id,-1)
755 and nvl(oipl_id, -1) = nvl(p_oipl_id,-1);
756
757 l_elig_per_elctbl_chc_id number;
758 --9774358
759
760 l_epr_elenum pls_integer;
761 -- Bof FONM
762 l_fonm_date date ;
763 l_fonm_flag varchar2(1) := ben_manage_life_events.fonm ;
764 l_fonm_cvg_strt_dt date ;
765 l_fonm_rt_strt_dt date ;
766 l_dummy_d date ;
767 l_fonm_per_rec per_all_people_f%rowtype;
768 l_fonm_ass_rec per_all_assignments_f%rowtype;
769 l_comp_prem_idx number;
770 l_dflt_value number;
771
772 -- Eof FONM
773
774 BEGIN
775 --
776 hr_utility.set_location ('Entering '||l_package,10);
777 hr_utility.set_location ('p_person_id : '||p_person_id ,10);
778 hr_utility.set_location ('p_effective_date : '||p_effective_date ,10);
779 hr_utility.set_location ('p_lf_evt_ocrd_dt : '||p_lf_evt_ocrd_dt ,10);
780 hr_utility.set_location ('p_pgm_id : '||p_pgm_id ,10);
781 hr_utility.set_location ('p_pl_id : '||p_pl_id ,10);
782 hr_utility.set_location ('p_oipl_id : '||p_oipl_id ,10);
783 hr_utility.set_location ('p_pl_typ_id : '||p_pl_typ_id ,10);
784 hr_utility.set_location ('p_per_in_ler_id : '||p_per_in_ler_id ,10);
785 hr_utility.set_location ('p_bnft_amt : '||p_bnft_amt ,10);
786 hr_utility.set_location ('p_business_group_id : '||p_business_group_id,10);
787 -- since the premium called out side of compobject loop
788 -- global variable can not be used to determine the fonm mode
789 -- use the epe fonmv cvg date used for the purpose
790
791
792
793 -- Edit to ensure that the input p_person_id has a value
794 --
795 If (p_person_id is null) then
796 hr_utility.set_location ('BEN_91574_BENACPRM_INPT_PRSN',20);
797 fnd_message.set_name('BEN','BEN_91574_BENACPRM_INPT_PRSN');
798 fnd_message.set_token('PROC',l_package);
799 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
800 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
801 fnd_message.set_token('PL_ID',to_char(p_pl_id));
802 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
803 fnd_message.set_token('LF_EVT_OCRD_DT',to_char(p_lf_evt_ocrd_dt));
804 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
805 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
806 fnd_message.raise_error;
807 end if;
808 --
809 -- Edit to insure that the input p_effective_date has a value
810 --
811 If (p_effective_date is null) then
812 hr_utility.set_location ('BEN_91575_BENACPRM_INPT_EFFDT',30);
813 fnd_message.set_name('BEN','BEN_91575_BENACPRM_INPT_EFFDT');
814 fnd_message.set_token('PROC',l_package);
815 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
816 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
817 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
818 fnd_message.set_token('PL_ID',to_char(p_pl_id));
819 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
820 fnd_message.set_token('LF_EVT_OCRD_DT',to_char(p_lf_evt_ocrd_dt));
821 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
822 fnd_message.raise_error;
823 end if;
824
825 if p_calc_only_rt_val_flag then
826 --
827 --9774358
828 open c_epe;
829 fetch c_epe into l_elig_per_elctbl_chc_id;
830 close c_epe;
831
832 l_currepe_set(0).elig_per_elctbl_chc_id := l_elig_per_elctbl_chc_id;
833 --9774358
834
835 l_currepe_set(0).pl_id := p_pl_id;
836 l_currepe_set(0).oipl_id := p_oipl_id;
837 l_currepe_set(0).pgm_id := p_pgm_id;
838 l_currepe_set(0).pl_typ_id := p_pl_typ_id;
839 l_currepe_set(0).per_in_ler_id := p_per_in_ler_id;
840 l_currepe_set(0).ler_id := p_ler_id;
841 l_currepe_set(0).business_group_id := p_business_group_id;
842 l_currepe_set(0).val := p_bnft_amt;
843 --
844 else
845 --
846 -- unrestricted enhancement- added per_in_ler_id parameter
847 ben_epe_cache.get_perpilepe_list
848 (p_person_id => p_person_id
849 ,p_per_in_ler_id =>p_per_in_ler_id
850 --
851 ,p_inst_set => l_currepe_set
852 );
853 --
854 end if;
855 --
856 -- Clear epe context row
857 --
858 ben_epe_cache.init_context_pileperow;
859 --
860 -- 6330056: Reset global values before calculation.
861 g_computed_prem_tbl.delete;
862 l_comp_prem_idx := 0;
863 --
864 -- loop through all choices for this person
865 --
866 if l_currepe_set.count > 0 then
867 --
868 l_epr_elenum := 1;
869 --
870 for epe_elenum in l_currepe_set.first..l_currepe_set.last loop
871 hr_utility.set_location (' St EPE CORVF loop ',40);
872 --
873 -- Set the electable choice context variable
874 --
875 ben_epe_cache.g_currepe_row := l_currepe_set(epe_elenum);
876 --
877 hr_utility.set_location ('loop choices -> '||l_currepe_set(epe_elenum).elig_per_elctbl_chc_id,50);
878
879 /* Bug Bug 16013053; If val is NULL, then fetch the value of DFLT_VAL*/
880 if(l_currepe_set(epe_elenum).enrt_bnft_id is NOT NULL
881 and l_currepe_set(epe_elenum).val is NULL and NOT p_calc_only_rt_val_flag) then
882 begin
883 select nvl(val,dflt_val) into l_dflt_value from ben_enrt_bnft
884 where enrt_bnft_id=l_currepe_set(epe_elenum).enrt_bnft_id;
885 exception
886 when others then
887 l_dflt_value:=null;
888 end;
889 end if;
890
891 -- BOF FONM
892 l_fonm_cvg_strt_dt := null ;
893 l_fonm_rt_strt_dt := null ;
894
895 if l_currepe_set(epe_elenum).fonm_cvg_strt_dt is not null then
896 l_FONM_flag := 'Y' ;
897 else
898 l_FONM_flag := 'N' ;
899 l_FONM_date := nvl(p_lf_evt_ocrd_dt, p_effective_date) ;
900 ben_manage_life_events.g_fonm_cvg_strt_dt := null ;
901 ben_manage_life_events.fonm := 'N' ;
902 end if ;
903 hr_utility.set_location ('premium fonm -> '||l_FONM_flag,50);
904
905 if l_FONM_flag = 'Y' then
906
907 if l_currepe_set(epe_elenum).fonm_cvg_strt_dt is not null then
908 l_FONM_date := l_currepe_set(epe_elenum).fonm_cvg_strt_dt ;
909 l_fonm_cvg_strt_dt:= l_currepe_set(epe_elenum).fonm_cvg_strt_dt ;
910 ben_manage_life_events.g_fonm_cvg_strt_dt := l_fonm_cvg_strt_dt ;
911 ben_manage_life_events.fonm := 'Y' ;
912 end if ;
913
914 /* -- for future
915 --- calcualte the rate start date
916 if l_fonm_rt_strt_dt is null then
917 -- Calcaulte the coverage start date
918 calc_fonm_dates(p_effective_date => nvl(p_lf_evt_ocrd_dt, p_effective_date)
919 ,p_business_group_id => p_business_group_id
920 ,p_per_in_ler_id => p_per_in_ler_id
921 ,p_person_id => p_person_id
922 ,p_inst_set => ben_epe_cache.g_currepe_row
923 ,p_calc_type => 'R'
924 ,p_cvg_strt_dt => l_dummy_d
925 ,p_rt_strt_dt => l_fonm_rt_strt_dt ) ;
926 end if ;
927 */
928
929 end if ;
930 hr_utility.set_location ('g_fonm_cvg_strt_dt '||ben_manage_life_events.g_fonm_cvg_strt_dt,50);
931 -- EOF FONM
932
933 -- l_enrt_bnft_id := l_currepe_set(epe_elenum).enrt_bnft_id;
934 -- l_coverage_value := null;
935
936 if l_currepe_set(epe_elenum).oipl_id is not NULL then
937 hr_utility.set_location ('getting apr_oipl -> '||l_currepe_set(epe_elenum).oipl_id,60);
938 -- FONM l_fonm_date send as param
939 open c_apr_oipl(l_currepe_set(epe_elenum).oipl_id,l_FONM_date);
940 fetch c_apr_oipl into l_apr;
941 if c_apr_oipl%notfound then
942 --
943 -- no actual premiums for this oipl
944 --
945 hr_utility.set_location ('apr_oipl -> '||l_currepe_set(epe_elenum).oipl_id||' <- not found',70);
946 close c_apr_oipl;
947 l_ap_found := false;
948 else
949 -- FONM l_fonm_date send as param
950 open c_opt(l_currepe_set(epe_elenum).oipl_id,l_FONM_date);
951 fetch c_opt into l_opt;
952 close c_opt;
953 end if;
954 elsif l_currepe_set(epe_elenum).pl_id is not NULL then
955 hr_utility.set_location ('getting apr_pl -> '||l_currepe_set(epe_elenum).pl_id,80);
956 -- FONM l_fonm_date send as param
957 open c_apr_pl(l_currepe_set(epe_elenum).pl_id,l_FONM_date);
958 fetch c_apr_pl into l_apr;
959 if c_apr_pl%notfound then
960 --
961 -- no actual premiums for this pl
962 --
963 hr_utility.set_location ('apr_pl -> '||l_currepe_set(epe_elenum).pl_id||' <- not found',90);
964 close c_apr_pl;
965 l_ap_found := false;
966 end if;
967 else
968 l_ap_found := false;
969 end if;
970 --
971 -- loop through all actl prem for this elctbl_chc
972 --
973 hr_utility.set_location ('FONM CVG DATE -> '||l_fonm_date ,90);
974 hr_utility.set_location ('FONM Rate DATE ->'||l_fonm_rt_strt_dt ,90);
975 if l_ap_found then
976
977
978 -- fonm caching validation for premium
979 -- fonm check the caching for person level and asg level
980 ben_person_object.get_object(p_person_id => p_person_id,
981 p_rec => l_fonm_per_rec);
982 if not nvl(l_fonm_cvg_strt_dt,l_FONM_date ) between l_fonm_per_rec.effective_start_date
983 and l_fonm_per_rec.effective_end_date then
984
985 hr_utility.set_location('cache clearence ' || l_package ,10);
986 ben_use_cvg_rt_date.fonm_clear_down_cache;
987 else
988 ben_person_object.get_object(p_person_id => p_person_id,
989 p_rec => l_fonm_ass_rec);
990 if not nvl(l_fonm_cvg_strt_dt,l_FONM_date ) between l_fonm_ass_rec.effective_start_date
991 and l_fonm_ass_rec.effective_end_date then
992 hr_utility.set_location('cache clearence ' || l_package ,10);
993 ben_use_cvg_rt_date.fonm_clear_down_cache;
994 end if ;
995 end if ;
996 --
997 --
998 loop
999 hr_utility.set_location (l_package||' St EPE AP loop ',100);
1000 if l_currepe_set(epe_elenum).oipl_id is not null then
1001 exit when c_apr_oipl%notfound;
1002 elsif l_currepe_set(epe_elenum).pl_id is not null then
1003 exit when c_apr_pl%notfound;
1004 end if;
1005 --hr_utility.set_location ('l_apr.actl_prem_id->'||l_apr.actl_prem_id,110);
1006 -- hr_utility.set_location ('l_apr.prem_asnmt_cd -> '||l_apr.prem_asnmt_cd,110);
1007 -- hr_utility.set_location ('l_apr.mlt_cd -> '||l_apr.mlt_cd,110);
1008
1009 -- if l_apr.prem_asnmt_cd = 'ENRT' then
1010 compute_premium
1011 (p_person_id => p_person_id,
1012 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
1013 p_effective_date => p_effective_date,
1014 p_business_group_id => l_currepe_set(epe_elenum).business_group_id,
1015 p_per_in_ler_id => l_currepe_set(epe_elenum).per_in_ler_id,
1016 p_ler_id => l_currepe_set(epe_elenum).ler_id,
1017 p_actl_prem_id => l_apr.actl_prem_id,
1018 p_perform_rounding_flg => p_perform_rounding_flg,
1019 p_calc_only_rt_val_flag => p_calc_only_rt_val_flag,
1020 p_pgm_id => l_currepe_set(epe_elenum).pgm_id,
1021 p_pl_typ_id => l_currepe_set(epe_elenum).pl_typ_id,
1022 p_pl_id => l_currepe_set(epe_elenum).pl_id,
1023 p_oipl_id => l_currepe_set(epe_elenum).oipl_id,
1024 p_opt_id => l_opt.opt_id,
1025 p_elig_per_elctbl_chc_id => l_currepe_set(epe_elenum).elig_per_elctbl_chc_id,
1026 p_enrt_bnft_id => l_currepe_set(epe_elenum).enrt_bnft_id,
1027 p_bnft_amt => nvl(l_currepe_set(epe_elenum).val,l_dflt_value), --Bug 16013053: added nvl condition
1028 p_prem_val => l_apr.val,
1029 p_mlt_cd => l_apr.mlt_cd,
1030 p_bnft_rt_typ_cd => l_apr.bnft_rt_typ_cd,
1031 p_val_calc_rl => l_apr.val_calc_rl,
1032 p_rndg_cd => l_apr.rndg_cd,
1033 p_rndg_rl => l_apr.rndg_rl,
1034 p_upr_lmt_val => l_apr.upr_lmt_val,
1035 p_lwr_lmt_val => l_apr.lwr_lmt_val,
1036 p_upr_lmt_calc_rl => l_apr.upr_lmt_calc_rl,
1037 p_lwr_lmt_calc_rl => l_apr.lwr_lmt_calc_rl,
1038 p_fonm_cvg_strt_dt => l_fonm_cvg_strt_dt,
1039 p_fonm_rt_strt_dt => l_fonm_rt_strt_dt,
1040 p_computed_val => l_val); -- ouput
1041
1042 --bug :1676551 assigning the valu to global variable
1043 g_computed_prem_val := l_val ;
1044 --
1045 -- 6330056 : Store all the premiums evaluated into
1046 -- global pl/sql tbl
1047 l_comp_prem_idx := l_comp_prem_idx + 1;
1048 g_computed_prem_tbl(l_comp_prem_idx).actl_prem_id := l_apr.actl_prem_id;
1049 g_computed_prem_tbl(l_comp_prem_idx).val := l_val;
1050 --
1051 hr_utility.set_location ('Premium Count ' || l_comp_prem_idx,120);
1052 hr_utility.set_location ('actl_prem_id ' || l_apr.actl_prem_id,120);
1053 hr_utility.set_location ('Premium Value ' || l_val,120);
1054 --
1055 -- write enrt_prem record
1056 --
1057 hr_utility.set_location ('writing enrt_prem ',120);
1058 --
1059 -- Add enrt prem row to the varray
1060 --
1061 l_val_va.extend(1);
1062 l_val_va(l_epr_elenum) := nvl(l_val,0);
1063 l_uom_va.extend(1);
1064 l_uom_va(l_epr_elenum) := l_apr.uom;
1065 l_elig_per_elctbl_chc_id_va.extend(1);
1066 l_elig_per_elctbl_chc_id_va(l_epr_elenum) := l_currepe_set(epe_elenum).elig_per_elctbl_chc_id;
1067 l_enrt_bnft_id_va.extend(1);
1068 l_enrt_bnft_id_va(l_epr_elenum) := l_currepe_set(epe_elenum).enrt_bnft_id;
1069 l_actl_prem_id_va.extend(1);
1070 l_actl_prem_id_va(l_epr_elenum) := l_apr.actl_prem_id;
1071 l_business_group_id_va.extend(1);
1072 l_business_group_id_va(l_epr_elenum) := l_currepe_set(epe_elenum).business_group_id;
1073 --
1074 l_epr_attribute_category_va.extend(1);
1075 l_epr_attribute_category_va(l_epr_elenum) := null;
1076 l_epr_attribute1_va.extend(1);
1077 l_epr_attribute1_va(l_epr_elenum) := null;
1078 l_epr_attribute2_va.extend(1);
1079 l_epr_attribute2_va(l_epr_elenum) := null;
1080 l_epr_attribute3_va.extend(1);
1081 l_epr_attribute3_va(l_epr_elenum) := null;
1082 l_epr_attribute4_va.extend(1);
1083 l_epr_attribute4_va(l_epr_elenum) := null;
1084 l_epr_attribute5_va.extend(1);
1085 l_epr_attribute5_va(l_epr_elenum) := null;
1086 l_epr_attribute6_va.extend(1);
1087 l_epr_attribute6_va(l_epr_elenum) := null;
1088 l_epr_attribute7_va.extend(1);
1089 l_epr_attribute7_va(l_epr_elenum) := null;
1090 l_epr_attribute8_va.extend(1);
1091 l_epr_attribute8_va(l_epr_elenum) := null;
1092 l_epr_attribute9_va.extend(1);
1093 l_epr_attribute9_va(l_epr_elenum) := null;
1094 l_epr_attribute10_va.extend(1);
1095 l_epr_attribute10_va(l_epr_elenum) := null;
1096 l_epr_attribute11_va.extend(1);
1097 l_epr_attribute11_va(l_epr_elenum) := null;
1098 l_epr_attribute12_va.extend(1);
1099 l_epr_attribute12_va(l_epr_elenum) := null;
1100 l_epr_attribute13_va.extend(1);
1101 l_epr_attribute13_va(l_epr_elenum) := null;
1102 l_epr_attribute14_va.extend(1);
1103 l_epr_attribute14_va(l_epr_elenum) := null;
1104 l_epr_attribute15_va.extend(1);
1105 l_epr_attribute15_va(l_epr_elenum) := null;
1106 l_epr_attribute16_va.extend(1);
1107 l_epr_attribute16_va(l_epr_elenum) := null;
1108 l_epr_attribute17_va.extend(1);
1109 l_epr_attribute17_va(l_epr_elenum) := null;
1110 l_epr_attribute18_va.extend(1);
1111 l_epr_attribute18_va(l_epr_elenum) := null;
1112 l_epr_attribute19_va.extend(1);
1113 l_epr_attribute19_va(l_epr_elenum) := null;
1114 l_epr_attribute20_va.extend(1);
1115 l_epr_attribute20_va(l_epr_elenum) := null;
1116 l_epr_attribute21_va.extend(1);
1117 l_epr_attribute21_va(l_epr_elenum) := null;
1118 l_epr_attribute22_va.extend(1);
1119 l_epr_attribute22_va(l_epr_elenum) := null;
1120 l_epr_attribute23_va.extend(1);
1121 l_epr_attribute23_va(l_epr_elenum) := null;
1122 l_epr_attribute24_va.extend(1);
1123 l_epr_attribute24_va(l_epr_elenum) := null;
1124 l_epr_attribute25_va.extend(1);
1125 l_epr_attribute25_va(l_epr_elenum) := null;
1126 l_epr_attribute26_va.extend(1);
1127 l_epr_attribute26_va(l_epr_elenum) := null;
1128 l_epr_attribute27_va.extend(1);
1129 l_epr_attribute27_va(l_epr_elenum) := null;
1130 l_epr_attribute28_va.extend(1);
1131 l_epr_attribute28_va(l_epr_elenum) := null;
1132 l_epr_attribute29_va.extend(1);
1133 l_epr_attribute29_va(l_epr_elenum) := null;
1134 l_epr_attribute30_va.extend(1);
1135 l_epr_attribute30_va(l_epr_elenum) := null;
1136 --
1137 l_object_version_number_va.extend(1);
1138 l_object_version_number_va(l_epr_elenum) := 1;
1139 --
1140 l_request_id_va.extend(1);
1141 l_request_id_va(l_epr_elenum) := fnd_global.conc_request_id;
1142 l_program_application_id_va.extend(1);
1143 l_program_application_id_va(l_epr_elenum) := fnd_global.prog_appl_id;
1144 l_program_id_va.extend(1);
1145 l_program_id_va(l_epr_elenum) := fnd_global.conc_program_id;
1146 l_program_update_date_va.extend(1);
1147 l_program_update_date_va(l_epr_elenum) := sysdate;
1148 --
1149 l_epr_elenum := l_epr_elenum+1;
1150
1151 -- end if;
1152 hr_utility.set_location ('get next pl or oipl and loop back',130);
1153 if l_currepe_set(epe_elenum).oipl_id is not null then
1154 hr_utility.set_location ('next c_apr_oipl for oipl_id -> '||l_currepe_set(epe_elenum).oipl_id,130);
1155 fetch c_apr_oipl into l_apr;
1156 else
1157 hr_utility.set_location ('next c_apr_pl for pl_id -> '||l_currepe_set(epe_elenum).pl_id,130);
1158 fetch c_apr_pl into l_apr;
1159 end if;
1160 hr_utility.set_location ('AFTER FETCH',140);
1161
1162 end loop;
1163
1164 end if;
1165 if l_currepe_set(epe_elenum).oipl_id is not null and l_ap_found then
1166 close c_apr_oipl;
1167 elsif l_currepe_set(epe_elenum).pl_id is not null and l_ap_found then
1168 close c_apr_pl;
1169 end if;
1170 l_ap_found := true;
1171 end loop;
1172 end if;
1173 --
1174 if l_elig_per_elctbl_chc_id_va.count > 0 then
1175 --
1176 if p_mode in ('U','R') then
1177 --
1178 FOR i IN l_elig_per_elctbl_chc_id_va.FIRST .. l_elig_per_elctbl_chc_id_va.LAST loop
1179 --
1180 hr_utility.set_location(l_elig_per_elctbl_chc_id_va(i)||' '||l_enrt_bnft_id_va(i)||'actual'||l_actl_prem_id_va(i),111);
1181 l_enrt_prem_id := null;
1182 l_enrt_prem_id := ben_manage_unres_life_events.epr_exists
1183 (l_elig_per_elctbl_chc_id_va(i),
1184 l_enrt_bnft_id_va(i),
1185 l_actl_prem_id_va(i));
1186 --
1187 if l_enrt_prem_id is not null then
1188 --
1189 ben_manage_unres_life_events.update_enrt_prem
1190 ( p_enrt_prem_id => l_enrt_prem_id
1191 ,p_val => l_val_va(i)
1192 ,p_uom => l_uom_va(i)
1193 ,p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id_va(i)
1194 ,p_enrt_bnft_id => l_enrt_bnft_id_va(i)
1195 ,p_actl_prem_id => l_actl_prem_id_va(i)
1196 ,p_business_group_id => p_business_group_id
1197 ,p_object_version_number => l_object_version_number
1198 ,p_request_id => l_request_id_va(i)
1199 ,p_program_application_id => l_program_application_id_va(i)
1200 ,p_program_id => l_program_id_va(i)
1201 ,p_program_update_date => l_program_update_date_va(i)
1202 );
1203 else
1204 --
1205 INSERT INTO ben_enrt_prem
1206 (enrt_prem_id,
1207 val,
1208 uom,
1209 elig_per_elctbl_chc_id,
1210 enrt_bnft_id,
1211 actl_prem_id,
1212 business_group_id,
1213 epr_attribute_category,
1214 epr_attribute1,
1215 epr_attribute2,
1216 epr_attribute3,
1217 epr_attribute4,
1218 epr_attribute5,
1219 epr_attribute6,
1220 epr_attribute7,
1221 epr_attribute8,
1222 epr_attribute9,
1223 epr_attribute10,
1224 epr_attribute11,
1225 epr_attribute12,
1226 epr_attribute13,
1227 epr_attribute14,
1228 epr_attribute15,
1229 epr_attribute16,
1230 epr_attribute17,
1231 epr_attribute18,
1232 epr_attribute19,
1233 epr_attribute20,
1234 epr_attribute21,
1235 epr_attribute22,
1236 epr_attribute23,
1237 epr_attribute24,
1238 epr_attribute25,
1239 epr_attribute26,
1240 epr_attribute27,
1241 epr_attribute28,
1242 epr_attribute29,
1243 epr_attribute30,
1244 object_version_number,
1245 request_id,
1246 program_application_id,
1247 program_id,
1248 program_update_date
1249 )
1250 VALUES
1251 (ben_enrt_prem_s.nextval,
1252 l_val_va(i),
1253 l_uom_va(i),
1254 l_elig_per_elctbl_chc_id_va(i),
1255 l_enrt_bnft_id_va(i),
1256 l_actl_prem_id_va(i),
1257 l_business_group_id_va(i),
1258 l_epr_attribute_category_va(i),
1259 l_epr_attribute1_va(i),
1260 l_epr_attribute2_va(i),
1261 l_epr_attribute3_va(i),
1262 l_epr_attribute4_va(i),
1263 l_epr_attribute5_va(i),
1264 l_epr_attribute6_va(i),
1265 l_epr_attribute7_va(i),
1266 l_epr_attribute8_va(i),
1267 l_epr_attribute9_va(i),
1268 l_epr_attribute10_va(i),
1269 l_epr_attribute11_va(i),
1270 l_epr_attribute12_va(i),
1271 l_epr_attribute13_va(i),
1272 l_epr_attribute14_va(i),
1273 l_epr_attribute15_va(i),
1274 l_epr_attribute16_va(i),
1275 l_epr_attribute17_va(i),
1276 l_epr_attribute18_va(i),
1277 l_epr_attribute19_va(i),
1278 l_epr_attribute20_va(i),
1279 l_epr_attribute21_va(i),
1280 l_epr_attribute22_va(i),
1281 l_epr_attribute23_va(i),
1282 l_epr_attribute24_va(i),
1283 l_epr_attribute25_va(i),
1284 l_epr_attribute26_va(i),
1285 l_epr_attribute27_va(i),
1286 l_epr_attribute28_va(i),
1287 l_epr_attribute29_va(i),
1288 l_epr_attribute30_va(i),
1289 l_object_version_number_va(i),
1290 l_request_id_va(i),
1291 l_program_application_id_va(i),
1292 l_program_id_va(i),
1293 l_program_update_date_va(i)
1294 );
1295 end if;
1296 end loop;
1297 --
1298 else
1299 --
1300 FORALL i IN l_elig_per_elctbl_chc_id_va.FIRST .. l_elig_per_elctbl_chc_id_va.LAST
1301 INSERT INTO ben_enrt_prem
1302 (enrt_prem_id,
1303 val,
1304 uom,
1305 elig_per_elctbl_chc_id,
1306 enrt_bnft_id,
1307 actl_prem_id,
1308 business_group_id,
1309 epr_attribute_category,
1310 epr_attribute1,
1311 epr_attribute2,
1312 epr_attribute3,
1313 epr_attribute4,
1314 epr_attribute5,
1315 epr_attribute6,
1316 epr_attribute7,
1317 epr_attribute8,
1318 epr_attribute9,
1319 epr_attribute10,
1320 epr_attribute11,
1321 epr_attribute12,
1322 epr_attribute13,
1323 epr_attribute14,
1324 epr_attribute15,
1325 epr_attribute16,
1326 epr_attribute17,
1327 epr_attribute18,
1328 epr_attribute19,
1329 epr_attribute20,
1330 epr_attribute21,
1331 epr_attribute22,
1332 epr_attribute23,
1333 epr_attribute24,
1334 epr_attribute25,
1335 epr_attribute26,
1336 epr_attribute27,
1337 epr_attribute28,
1338 epr_attribute29,
1339 epr_attribute30,
1340 object_version_number,
1341 request_id,
1342 program_application_id,
1343 program_id,
1344 program_update_date
1345 )
1346 VALUES
1347 (ben_enrt_prem_s.nextval,
1348 l_val_va(i),
1349 l_uom_va(i),
1350 l_elig_per_elctbl_chc_id_va(i),
1351 l_enrt_bnft_id_va(i),
1352 l_actl_prem_id_va(i),
1353 l_business_group_id_va(i),
1354 l_epr_attribute_category_va(i),
1355 l_epr_attribute1_va(i),
1356 l_epr_attribute2_va(i),
1357 l_epr_attribute3_va(i),
1358 l_epr_attribute4_va(i),
1359 l_epr_attribute5_va(i),
1360 l_epr_attribute6_va(i),
1361 l_epr_attribute7_va(i),
1362 l_epr_attribute8_va(i),
1363 l_epr_attribute9_va(i),
1364 l_epr_attribute10_va(i),
1365 l_epr_attribute11_va(i),
1366 l_epr_attribute12_va(i),
1367 l_epr_attribute13_va(i),
1368 l_epr_attribute14_va(i),
1369 l_epr_attribute15_va(i),
1370 l_epr_attribute16_va(i),
1371 l_epr_attribute17_va(i),
1372 l_epr_attribute18_va(i),
1373 l_epr_attribute19_va(i),
1374 l_epr_attribute20_va(i),
1375 l_epr_attribute21_va(i),
1376 l_epr_attribute22_va(i),
1377 l_epr_attribute23_va(i),
1378 l_epr_attribute24_va(i),
1379 l_epr_attribute25_va(i),
1380 l_epr_attribute26_va(i),
1381 l_epr_attribute27_va(i),
1382 l_epr_attribute28_va(i),
1383 l_epr_attribute29_va(i),
1384 l_epr_attribute30_va(i),
1385 l_object_version_number_va(i),
1386 l_request_id_va(i),
1387 l_program_application_id_va(i),
1388 l_program_id_va(i),
1389 l_program_update_date_va(i)
1390 );
1391 --
1392 end if;
1393 --
1394 end if;
1395 --
1396 -- Clear epe context row
1397 --
1398 ben_epe_cache.init_context_pileperow;
1399 --
1400 hr_utility.set_location ('Leaving '||l_package,99);
1401 end main;
1402 end ben_determine_actual_premium;