[Home] [Help]
PACKAGE BODY: APPS.BEN_DETERMINE_ACTUAL_PREMIUM
Source
1 Package Body BEN_DETERMINE_ACTUAL_PREMIUM as
2 /* $Header: benacprm.pkb 120.5.12010000.1 2008/07/29 12:02:32 appldev 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 */
124 --------------------------------------------------------------------------------
125 --
126 g_package varchar2(80) := 'ben_determine_actual_premium';
127
128 procedure calc_fonm_dates( p_effective_date in date
129 ,p_business_group_id in number
130 ,p_per_in_ler_id in number
131 ,p_person_id in number
132 ,p_inst_set in ben_epe_cache.g_pilepe_inst_row
133 ,p_calc_type in varchar2
134 ,p_cvg_strt_dt out nocopy date
135 ,p_rt_strt_dt out nocopy date )
136 is
137
138 l_package varchar2(80) := g_package||'.calc_fonm_dates';
139
140 l_enrt_cvg_strt_dt date ;
141 l_rt_strt_dt date ;
142 l_dummy_d date ;
143 l_dummy_v varchar2(30);
144 l_dummy_n number;
145 Begin
146
147 hr_utility.set_location ('Entering ' ||l_package,10);
148
149 ben_determine_date.rate_and_coverage_dates
150 (p_which_dates_cd => nvl(p_calc_type,'B')
151 ,p_date_mandatory_flag => 'N'
152 ,p_compute_dates_flag => 'Y'
153 ,p_elig_per_elctbl_chc_id => p_inst_set.elig_per_elctbl_chc_id
154 ,p_business_group_id => p_business_group_id
155 ,P_PER_IN_LER_ID => p_per_in_ler_id
156 ,P_PERSON_ID => p_person_id
157 ,P_PGM_ID => p_inst_set.pgm_id
158 ,P_PL_ID => p_inst_set.pl_id
159 ,P_OIPL_ID => p_inst_set.oipl_id
160 ,P_LEE_RSN_ID => p_inst_set.lee_rsn_id
161 ,P_ENRT_PERD_ID => p_inst_set.enrt_perd_id
162 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt
163 ,p_enrt_cvg_strt_dt_cd => l_dummy_v
164 ,p_enrt_cvg_strt_dt_rl => l_dummy_n
165 ,p_rt_strt_dt => l_rt_strt_dt
166 ,p_rt_strt_dt_cd => l_dummy_v
167 ,p_rt_strt_dt_rl => l_dummy_n
168 ,p_enrt_cvg_end_dt => l_dummy_d
169 ,p_enrt_cvg_end_dt_cd => l_dummy_v
170 ,p_enrt_cvg_end_dt_rl => l_dummy_n
171 ,p_rt_end_dt => l_dummy_d
172 ,p_rt_end_dt_cd => l_dummy_v
173 ,p_rt_end_dt_rl => l_dummy_n
174 ,p_effective_date => p_effective_date
175 ,p_lf_evt_ocrd_dt => p_effective_date)
176 ;
177
178 p_cvg_strt_dt := l_enrt_cvg_strt_dt ;
179 p_rt_strt_dt := l_rt_strt_dt ;
180
181 hr_utility.set_location ('Leaving ' ||l_package,10);
182
183 End calc_fonm_dates ;
184
185
186 --------------------------------------------------------------------------------
187 -- COMPUTE_PREMIUM
188 --------------------------------------------------------------------------------
189 procedure compute_premium
190 (p_person_id in number,
191 p_lf_evt_ocrd_dt IN date,
192 p_effective_date IN date,
193 p_business_group_id in number,
194 p_per_in_ler_id in number,
195 p_ler_id in number,
196 p_actl_prem_id in number,
197 p_perform_rounding_flg IN boolean default true,
198 p_calc_only_rt_val_flag in boolean default false,
199 p_pgm_id in number,
200 p_pl_typ_id in number,
201 p_pl_id in number,
202 p_oipl_id in number,
203 p_opt_id in number,
204 p_elig_per_elctbl_chc_id in number,
205 p_enrt_bnft_id in number,
206 p_bnft_amt in number,
207 p_prem_val in number,
208 p_mlt_cd in varchar2,
209 p_bnft_rt_typ_cd in varchar2,
210 p_val_calc_rl in number,
211 p_rndg_cd in varchar2,
212 p_rndg_rl in number,
213 p_upr_lmt_val in number,
214 p_lwr_lmt_val in number,
215 p_upr_lmt_calc_rl in number,
216 p_lwr_lmt_calc_rl in number,
217 p_fonm_cvg_strt_dt in date default null ,
218 p_fonm_rt_strt_dt in date default null ,
219 p_computed_val out nocopy number) IS
220
221 l_package varchar2(80) := g_package||'.compute_premium';
222 -- fonm new p_date param added
223 Cursor c_state (p_date date) is
224 select region_2
225 from hr_locations_all loc,per_all_assignments_f asg
226 where loc.location_id = asg.location_id
227 and asg.person_id = p_person_id
228 and asg.assignment_type <> 'C'
229 and asg.primary_flag = 'Y'
230 and p_date between
231 asg.effective_start_date and asg.effective_end_date;
232
233 l_state c_state%rowtype;
234 --
235 -- fonm new p_date param added
236 cursor c_asg(p_date date) is
237 select asg.assignment_id,asg.organization_id,loc.region_2
238 from per_all_assignments_f asg,hr_locations_all loc, per_assignment_status_types ast
239 where asg.person_id = p_person_id
240 and asg.assignment_type <> 'C'
241 and asg.primary_flag = 'Y'
242 and asg.location_id = loc.location_id(+)
243 and asg.assignment_status_type_id = ast.assignment_status_type_id(+)
244 and ast.per_system_status(+) = 'ACTIVE_ASSIGN'
245 --and nvl(p_lf_evt_ocrd_dt,p_effective_date)
246 and p_date
247 between asg.effective_start_date
248 and asg.effective_end_date
249 order by assignment_type desc, effective_start_date desc; -- BUG 4644867
250 l_asg c_asg%rowtype;
251
252 l_jurisdiction_code varchar2(30);
253 l_outputs ff_exec.outputs_t;
254 l_variable_val number;
255 l_vr_trtmt_cd varchar(30);
256 l_dummy_number number;
257 l_dummy_char varchar(30);
258 l_ultmt_upr_lmt number ;
259 l_ultmt_lwr_lmt number ;
260 l_ultmt_upr_lmt_calc_rl number ;
261 l_ultmt_lwr_lmt_calc_rl number ;
262 l_vr_ann_mn_elcn_val number;
263 l_vr_ann_mx_elcn_val number;
264
265 -- bof FONM
266 l_fonm_date date ;
267 --eof FONM
268 begin
269 hr_utility.set_location ('Entering ' ||l_package,10);
270 hr_utility.set_location ('l_apr.mlt_cd -> '||p_mlt_cd,10);
271 --
272 -- get values for rules and limit checking
273 --bof FONM
274 l_fonm_date := nvl(p_lf_evt_ocrd_dt,p_effective_date ) ;
275 if ben_manage_life_events.fonm = 'Y' then
276 l_fonm_date := nvl(p_fonm_cvg_strt_dt, l_fonm_date ) ;
277 end if ;
278 --eof FONM
279 /* -- 4031733 - cursor used to populate l_state.region_2
280 -- param for benutils.limit_checks which is not used down the line
281 --
282 open c_state (l_fonm_date);
283 fetch c_state into l_state;
284 close c_state;
285 */
286 open c_asg (l_fonm_date);
287 fetch c_asg into l_asg;
288 close c_asg;
289
290 --if l_asg.region_2 is not null then
291
292 -- l_jurisdiction_code :=
293 -- pay_mag_utils.lookup_jurisdiction_code
294 -- (p_state => l_asg.region_2);
295
299 -- Flat-Fixed
296 --end if;
297
298 --
300 --
301 if p_mlt_cd = 'FLFX' then
302 hr_utility.set_location ('in flfx',20);
303 p_computed_val := p_prem_val;
304 --
305 -- Multiple of Coverage
306 --
307 elsif p_mlt_cd = 'CVG' then
308 hr_utility.set_location ('in cvg',30);
309 if p_bnft_amt is null then
310 if p_enrt_bnft_id is null then
311 hr_utility.set_location ('BEN_91579_BENACPRM_INPT_EB',40);
312 fnd_message.set_name('BEN','BEN_91579_BENACPRM_INPT_EB');
313 fnd_message.set_token('PROC',l_package);
314 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
315 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
316 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
317 fnd_message.set_token('PL_ID',to_char(p_pl_id));
318 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
319 fnd_message.set_token('LF_EVT_OCRD_DT',
320 to_char(p_lf_evt_ocrd_dt));
321 fnd_message.set_token('MLT_CD',p_mlt_cd);
322 fnd_message.raise_error;
323 end if;
324 end if;
325
326 if p_bnft_amt is NULL then
327 --
328 -- Means that cvg to be selected upon enrt
329 --
330 p_computed_val := 0;
331 --
332 -- if coverage value is not null then process as defined
333 --
334 else
335 benutils.rt_typ_calc
336 (p_rt_typ_cd => p_bnft_rt_typ_cd
337 ,p_val => p_prem_val
338 ,p_val_2 => p_bnft_amt
339 ,p_calculated_val => p_computed_val);
340 end if;
341 --
342 -- Rule
343 --
344 elsif p_mlt_cd = 'RL' then
345 --
346 -- Call formula initialise routine
347 hr_utility.set_location ('in rl',10);
348
349 l_outputs := benutils.formula
350 (p_formula_id => p_val_calc_rl,
351 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
352 p_business_group_id=> p_business_group_id,
353 p_assignment_id => l_asg.assignment_id,
354 p_organization_id => l_asg.organization_id,
355 p_pgm_id => p_pgm_id,
356 p_pl_id => p_pl_id,
357 p_pl_typ_id => p_pl_typ_id,
358 p_opt_id => p_opt_id,
359 p_ler_id => p_ler_id,
360 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
361 p_jurisdiction_code=> l_jurisdiction_code,
362 --- Bof FONM
363 p_param1 => 'RT_STRT_DT',
364 p_param1_value => fnd_date.date_to_canonical(p_fonm_rt_strt_dt), -- null is passed, 4 future
365 p_param2 => 'CVG_STRT_DT',
366 p_param2_value => fnd_date.date_to_canonical(p_fonm_cvg_strt_dt)
367 --- Eof FONM
368 );
369 --
370 p_computed_val := fnd_number.canonical_to_number(l_outputs(l_outputs.first).value);
371
372 elsif p_mlt_cd = 'NSVU' then
373 -- Do nothing for NO STANDARD VALUE USED
374 -- Value used comes from Variable rates below.
375 hr_utility.set_location ('in nsvu',10);
376 null;
377 else
378 hr_utility.set_location ('BEN_91584_BENACPRM_MLT_CD',50);
379 fnd_message.set_name('BEN','BEN_91584_BENACPRM_MLT_CD');
380 fnd_message.set_token('PROC',l_package);
381 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
382 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
383 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
384 fnd_message.set_token('PL_ID',to_char(p_pl_id));
385 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
386 fnd_message.set_token('LF_EVT_OCRD_DT',to_char(p_lf_evt_ocrd_dt));
387 fnd_message.set_token('MLT_CD',p_mlt_cd);
388 fnd_message.raise_error;
389 end if;
390 --
391 ----bug : 143393 Limit validation applied to premium
392 ---- before calc VAPRP, after vapro and premium
393 ---- ultimate value of vapro will be applied
394 ---- the same apply to rounding
395
396 -- perform appropriate rounding based on the source table.
397 -- rounding_cd or rule cannot both be null, perform_rounding_flag
398 -- must be true....
399 --
400 -- Bug 4948(WWW Bug 1259220)
401 -- When l_vr_trtmt_cd is null rounding is not applied.
402 -- So nvl applied around l_vr_trtmt_cd.
403 --
404 if (p_rndg_cd is not null or
405 p_rndg_rl is not null) and
406 p_perform_rounding_flg = true and
407 p_computed_val is not null and
408 nvl(l_vr_trtmt_cd, 'XXXX') <> 'RPLC' then
412 p_rounding_rl => p_rndg_rl,
409 hr_utility.set_location ('rounding ',70);
410 p_computed_val := benutils.do_rounding
411 (p_rounding_cd => p_rndg_cd,
413 p_value => p_computed_val,
414 --- fonm
415 p_effective_date => l_fonm_date ) ; --- nvl(p_lf_evt_ocrd_dt,p_effective_date));
416 end if;
417 --
418 -- check upr/lwr limit
419 --
420 hr_utility.set_location('check the limit of prm ',20);
421 benutils.limit_checks
422 (p_upr_lmt_val => p_upr_lmt_val,
423 p_lwr_lmt_val => p_lwr_lmt_val,
424 p_upr_lmt_calc_rl => p_upr_lmt_calc_rl,
425 p_lwr_lmt_calc_rl => p_lwr_lmt_calc_rl,
426 -- fonm
427 p_effective_date => l_fonm_date , -- nvl(p_lf_evt_ocrd_dt,p_effective_date),
428 p_business_group_id => p_business_group_id,
429 p_assignment_id => l_asg.assignment_id,
430 p_organization_id => l_asg.organization_id,
431 p_pgm_id => p_pgm_id,
432 p_pl_id => p_pl_id,
433 p_pl_typ_id => p_pl_typ_id,
434 p_opt_id => p_opt_id,
435 p_ler_id => p_ler_id,
436 p_state => l_state.region_2,
437 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
438 p_val => p_computed_val);
439
440
441 ------------------------------------------
442 -- variable rates
443 ------------------------------------------
444 --
445 ben_determine_variable_rates.main
446 (p_person_id => p_person_id,
447 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
448 p_enrt_bnft_id => p_enrt_bnft_id,
449 p_actl_prem_id => p_actl_prem_id,
450 --
451 p_effective_date => l_fonm_date , ---p_effective_date,
452 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
453 p_calc_only_rt_val_flag => p_calc_only_rt_val_flag,
454 p_pgm_id => p_pgm_id,
455 p_pl_id => p_pl_id,
456 p_oipl_id => p_oipl_id,
457 p_pl_typ_id => p_pl_typ_id,
458 p_per_in_ler_id => p_per_in_ler_id,
459 p_ler_id => p_ler_id,
460 p_business_group_id => p_business_group_id,
461 p_bnft_amt => p_bnft_amt,
462 p_val => l_variable_val,
463 p_mn_elcn_val => l_dummy_number,
464 p_mx_elcn_val => l_dummy_number,
465 p_incrmnt_elcn_val => l_dummy_number,
466 p_dflt_elcn_val => l_dummy_number, -- do not apply to premiums
467 p_tx_typ_cd => l_dummy_char,
468 p_acty_typ_cd => l_dummy_char,
469 p_vrbl_rt_trtmt_cd => l_vr_trtmt_cd,
470 p_ann_mn_elcn_val => l_vr_ann_mn_elcn_val,
471 p_ann_mx_elcn_val => l_vr_ann_mx_elcn_val,
472 p_ultmt_upr_lmt => l_ultmt_upr_lmt,
473 p_ultmt_lwr_lmt => l_ultmt_lwr_lmt,
474 p_ultmt_upr_lmt_calc_rl => l_ultmt_upr_lmt_calc_rl,
475 p_ultmt_lwr_lmt_calc_rl => l_ultmt_lwr_lmt_calc_rl);
476 --
477 hr_utility.set_location ('after variable rates',60);
478 hr_utility.set_location ('l_vr_trtmt_cd -> '||l_vr_trtmt_cd,60);
479 hr_utility.set_location ('l_vr_val -> '||l_variable_val,60);
480 hr_utility.set_location ('l_val -> '||p_computed_val,60);
481
482 if p_computed_val is null then
483 l_vr_trtmt_cd := 'RPLC';
484 end if;
485
486 if l_variable_val is not null then
487 --
488 -- Replace
489 if l_vr_trtmt_cd = 'RPLC' then
490 p_computed_val := l_variable_val;
491
492 -- Multiply By
493 elsif l_vr_trtmt_cd = 'MB' then
494 p_computed_val := p_computed_val * l_variable_val;
495
496 -- Subtract From
497 elsif l_vr_trtmt_cd = 'SF' then
498 p_computed_val := p_computed_val - l_variable_val;
499
500 -- Add To
501 elsif l_vr_trtmt_cd = 'ADDTO' then
502 p_computed_val := p_computed_val + l_variable_val;
503 else -- Replace
504 p_computed_val := l_variable_val;
505 end if;
506
507 if l_ultmt_upr_lmt is not null or l_ultmt_lwr_lmt is not null
508 OR l_ultmt_upr_lmt_calc_rl is not null or l_ultmt_lwr_lmt_calc_rl is not null then
509 --- if ultmate limit defined in VAPRO chek the limit
510 hr_utility.set_location('calling limit for ultmt of prem',393);
511 hr_utility.set_location('upper '|| l_ultmt_upr_lmt ||' Lower' || l_ultmt_lwr_lmt,393);
512 hr_utility.set_location('ammount '|| p_computed_val ,393);
513 benutils.limit_checks
517 p_lwr_lmt_calc_rl => l_ultmt_lwr_lmt_calc_rl,
514 (p_upr_lmt_val => l_ultmt_upr_lmt,
515 p_lwr_lmt_val => l_ultmt_lwr_lmt,
516 p_upr_lmt_calc_rl => l_ultmt_upr_lmt_calc_rl,
518 --- fonm
519 p_effective_date => l_fonm_date , --- nvl(p_lf_evt_ocrd_dt,p_effective_date),
520 p_business_group_id => p_business_group_id,
521 p_assignment_id => l_asg.assignment_id,
522 p_organization_id => l_asg.organization_id,
523 p_pgm_id => p_pgm_id,
524 p_pl_id => p_pl_id,
525 p_pl_typ_id => p_pl_typ_id,
526 p_opt_id => p_opt_id,
527 p_ler_id => p_ler_id,
528 p_state => l_state.region_2,
529 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
530 p_val => p_computed_val);
531 hr_utility.set_location('ammount '|| p_computed_val ,393);
532
533 end if ;
534 end if;
535 --
536 /*
537 -- perform appropriate rounding based on the source table.
538 -- rounding_cd or rule cannot both be null, perform_rounding_flag
539 -- must be true....
540 --
541 -- Bug 4948(WWW Bug 1259220)
542 -- When l_vr_trtmt_cd is null rounding is not applied.
543 -- So nvl applied around l_vr_trtmt_cd.
544 --
545 if (p_rndg_cd is not null or
546 p_rndg_rl is not null) and
547 p_perform_rounding_flg = true and
548 p_computed_val is not null and
549 nvl(l_vr_trtmt_cd, 'XXXX') <> 'RPLC' then
550 hr_utility.set_location ('rounding ',70);
551 p_computed_val := benutils.do_rounding
552 (p_rounding_cd => p_rndg_cd,
553 p_rounding_rl => p_rndg_rl,
554 p_value => p_computed_val,
555 -- fonm
556 p_effective_date => l_fonm_date) ; --- nvl(p_lf_evt_ocrd_dt,p_effective_date));
557 end if;
558 --
559 -- check upr/lwr limit
560 --
561 benutils.limit_checks
562 (p_upr_lmt_val => p_upr_lmt_val,
563 p_lwr_lmt_val => p_lwr_lmt_val,
564 p_upr_lmt_calc_rl => p_upr_lmt_calc_rl,
565 p_lwr_lmt_calc_rl => p_lwr_lmt_calc_rl,
566 -- fonm
567 p_effective_date => l_fonm_date , -- nvl(p_lf_evt_ocrd_dt,p_effective_date),
568 p_business_group_id => p_business_group_id,
569 p_assignment_id => l_asg.assignment_id,
570 p_organization_id => l_asg.organization_id,
571 p_pgm_id => p_pgm_id,
572 p_pl_id => p_pl_id,
573 p_pl_typ_id => p_pl_typ_id,
574 p_opt_id => p_opt_id,
575 p_ler_id => p_ler_id,
576 p_state => l_state.region_2,
577 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
578 p_val => p_computed_val);
579 */
580
581 hr_utility.set_location ('Leaving '||l_package,99);
582
583 end compute_premium;
584 --------------------------------------------------------------------------------
585 -- MAIN
586 --------------------------------------------------------------------------------
587 PROCEDURE main
588 ( p_person_id in number,
589 p_effective_date IN date,
590 p_lf_evt_ocrd_dt IN date,
591 p_perform_rounding_flg IN boolean default true,
592 p_calc_only_rt_val_flag in boolean default false,
593 p_pgm_id in number default null,
594 p_pl_id in number default null,
595 p_oipl_id in number default null,
596 p_pl_typ_id in number default null,
597 p_per_in_ler_id in number default null,
598 p_ler_id in number default null,
599 p_bnft_amt in number default null,
600 p_business_group_id in number default null,
601 p_mode in varchar2
602 ) IS
603 --
604 l_package varchar2(80) := g_package||'.main';
605 --
606 l_currepe_set ben_epe_cache.g_pilepe_inst_tbl;
607 --
608 l_val_va benutils.g_number_table := benutils.g_number_table();
609 l_uom_va benutils.g_v2_30_table := benutils.g_v2_30_table();
610 l_elig_per_elctbl_chc_id_va benutils.g_number_table := benutils.g_number_table();
611 l_enrt_bnft_id_va benutils.g_number_table := benutils.g_number_table();
612 l_actl_prem_id_va benutils.g_number_table := benutils.g_number_table();
613 l_business_group_id_va benutils.g_number_table := benutils.g_number_table();
614 --
615 l_epr_attribute_category_va benutils.g_v2_30_table := benutils.g_v2_30_table();
616 l_epr_attribute1_va benutils.g_v2_150_table := benutils.g_v2_150_table();
617 l_epr_attribute2_va benutils.g_v2_150_table := benutils.g_v2_150_table();
621 l_epr_attribute6_va benutils.g_v2_150_table := benutils.g_v2_150_table();
618 l_epr_attribute3_va benutils.g_v2_150_table := benutils.g_v2_150_table();
619 l_epr_attribute4_va benutils.g_v2_150_table := benutils.g_v2_150_table();
620 l_epr_attribute5_va benutils.g_v2_150_table := benutils.g_v2_150_table();
622 l_epr_attribute7_va benutils.g_v2_150_table := benutils.g_v2_150_table();
623 l_epr_attribute8_va benutils.g_v2_150_table := benutils.g_v2_150_table();
624 l_epr_attribute9_va benutils.g_v2_150_table := benutils.g_v2_150_table();
625 l_epr_attribute10_va benutils.g_v2_150_table := benutils.g_v2_150_table();
626 l_epr_attribute11_va benutils.g_v2_150_table := benutils.g_v2_150_table();
627 l_epr_attribute12_va benutils.g_v2_150_table := benutils.g_v2_150_table();
628 l_epr_attribute13_va benutils.g_v2_150_table := benutils.g_v2_150_table();
629 l_epr_attribute14_va benutils.g_v2_150_table := benutils.g_v2_150_table();
630 l_epr_attribute15_va benutils.g_v2_150_table := benutils.g_v2_150_table();
631 l_epr_attribute16_va benutils.g_v2_150_table := benutils.g_v2_150_table();
632 l_epr_attribute17_va benutils.g_v2_150_table := benutils.g_v2_150_table();
633 l_epr_attribute18_va benutils.g_v2_150_table := benutils.g_v2_150_table();
634 l_epr_attribute19_va benutils.g_v2_150_table := benutils.g_v2_150_table();
635 l_epr_attribute20_va benutils.g_v2_150_table := benutils.g_v2_150_table();
636 l_epr_attribute21_va benutils.g_v2_150_table := benutils.g_v2_150_table();
637 l_epr_attribute22_va benutils.g_v2_150_table := benutils.g_v2_150_table();
638 l_epr_attribute23_va benutils.g_v2_150_table := benutils.g_v2_150_table();
639 l_epr_attribute24_va benutils.g_v2_150_table := benutils.g_v2_150_table();
640 l_epr_attribute25_va benutils.g_v2_150_table := benutils.g_v2_150_table();
641 l_epr_attribute26_va benutils.g_v2_150_table := benutils.g_v2_150_table();
642 l_epr_attribute27_va benutils.g_v2_150_table := benutils.g_v2_150_table();
643 l_epr_attribute28_va benutils.g_v2_150_table := benutils.g_v2_150_table();
644 l_epr_attribute29_va benutils.g_v2_150_table := benutils.g_v2_150_table();
645 l_epr_attribute30_va benutils.g_v2_150_table := benutils.g_v2_150_table();
646 --
647 l_object_version_number_va benutils.g_number_table := benutils.g_number_table();
648 --
649 l_request_id_va benutils.g_number_table := benutils.g_number_table();
650 l_program_application_id_va benutils.g_number_table := benutils.g_number_table();
651 l_program_id_va benutils.g_number_table := benutils.g_number_table();
652 l_program_update_date_va benutils.g_date_table := benutils.g_date_table();
653 --
654 l_val number;
655 l_enrt_prem_id number;
656 l_object_version_number number;
657 l_ap_found boolean := true;
658 --
659 -- fonm new parameter of date
660 cursor c_apr_pl(p_pl_id in number,p_date date) is
661 select apr.actl_prem_id,
662 apr.mlt_cd,
663 apr.val,
664 apr.rndg_cd,
665 apr.rndg_rl,
666 apr.rt_typ_cd,
667 apr.bnft_rt_typ_cd,
668 apr.comp_lvl_fctr_id,
669 apr.prem_asnmt_cd,
670 apr.val_calc_rl,
671 apr.upr_lmt_val,
672 apr.upr_lmt_calc_rl,
673 apr.lwr_lmt_val,
674 apr.lwr_lmt_calc_rl,
675 apr.uom
676 from ben_actl_prem_f apr
677 where apr.pl_id = p_pl_id
678 and apr.prem_asnmt_cd = 'ENRT' -- PROC are dealt with in benprplc.pkb
679 and p_date
680 between apr.effective_start_date
681 and apr.effective_end_date;
682 -- Fonm new parameter of date
683 cursor c_apr_oipl(p_oipl_id in number,p_date date) is
684 select apr.actl_prem_id,
685 apr.mlt_cd,
686 apr.val,
687 apr.rndg_cd,
688 apr.rndg_rl,
689 apr.rt_typ_cd,
690 apr.bnft_rt_typ_cd,
691 apr.comp_lvl_fctr_id,
692 apr.prem_asnmt_cd,
693 apr.val_calc_rl,
694 apr.upr_lmt_val,
695 apr.upr_lmt_calc_rl,
696 apr.lwr_lmt_val,
697 apr.lwr_lmt_calc_rl,
698 apr.uom
699 from ben_actl_prem_f apr
700 where apr.oipl_id = p_oipl_id
701 and apr.prem_asnmt_cd = 'ENRT' -- PROC are dealt with in benprplc.pkb
702 and p_date
703 between apr.effective_start_date
704 and apr.effective_end_date;
705 --
706 l_apr c_apr_pl%rowtype;
707 -- fonm p_date parameter added
708 cursor c_opt(p_oipl_id in number, p_date date) is
709 select oipl.opt_id
710 from ben_oipl_f oipl
711 where oipl.oipl_id = p_oipl_id
712 --and p_effective_date between
713 and p_date between
714 oipl.effective_start_date and oipl.effective_end_date;
715
716 l_opt c_opt%rowtype;
717 l_epr_elenum pls_integer;
718 -- Bof FONM
719 l_fonm_date date ;
720 l_fonm_flag varchar2(1) := ben_manage_life_events.fonm ;
721 l_fonm_cvg_strt_dt date ;
722 l_fonm_rt_strt_dt date ;
723 l_dummy_d date ;
724 l_fonm_per_rec per_all_people_f%rowtype;
725 l_fonm_ass_rec per_all_assignments_f%rowtype;
726 l_comp_prem_idx number;
727
728 -- Eof FONM
729
730 BEGIN
731 --
735 hr_utility.set_location ('p_lf_evt_ocrd_dt : '||p_lf_evt_ocrd_dt ,10);
732 hr_utility.set_location ('Entering '||l_package,10);
733 hr_utility.set_location ('p_person_id : '||p_person_id ,10);
734 hr_utility.set_location ('p_effective_date : '||p_effective_date ,10);
736 hr_utility.set_location ('p_pgm_id : '||p_pgm_id ,10);
737 hr_utility.set_location ('p_pl_id : '||p_pl_id ,10);
738 hr_utility.set_location ('p_oipl_id : '||p_oipl_id ,10);
739 hr_utility.set_location ('p_pl_typ_id : '||p_pl_typ_id ,10);
740 hr_utility.set_location ('p_per_in_ler_id : '||p_per_in_ler_id ,10);
741 hr_utility.set_location ('p_bnft_amt : '||p_bnft_amt ,10);
742 hr_utility.set_location ('p_business_group_id : '||p_business_group_id,10);
743 -- since the premium called out side of compobject loop
744 -- global variable can not be used to determine the fonm mode
745 -- use the epe fonmv cvg date used for the purpose
746
747
748
749 -- Edit to ensure that the input p_person_id has a value
750 --
751 If (p_person_id is null) then
752 hr_utility.set_location ('BEN_91574_BENACPRM_INPT_PRSN',20);
753 fnd_message.set_name('BEN','BEN_91574_BENACPRM_INPT_PRSN');
754 fnd_message.set_token('PROC',l_package);
755 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
756 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
757 fnd_message.set_token('PL_ID',to_char(p_pl_id));
758 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
759 fnd_message.set_token('LF_EVT_OCRD_DT',to_char(p_lf_evt_ocrd_dt));
760 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
761 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
762 fnd_message.raise_error;
763 end if;
764 --
765 -- Edit to insure that the input p_effective_date has a value
766 --
767 If (p_effective_date is null) then
768 hr_utility.set_location ('BEN_91575_BENACPRM_INPT_EFFDT',30);
769 fnd_message.set_name('BEN','BEN_91575_BENACPRM_INPT_EFFDT');
770 fnd_message.set_token('PROC',l_package);
771 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
772 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
773 fnd_message.set_token('PL_TYP_ID',to_char(p_pl_typ_id));
774 fnd_message.set_token('PL_ID',to_char(p_pl_id));
775 fnd_message.set_token('OIPL_ID',to_char(p_oipl_id));
776 fnd_message.set_token('LF_EVT_OCRD_DT',to_char(p_lf_evt_ocrd_dt));
777 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
778 fnd_message.raise_error;
779 end if;
780
781 if p_calc_only_rt_val_flag then
782 --
783 l_currepe_set(0).pl_id := p_pl_id;
784 l_currepe_set(0).oipl_id := p_oipl_id;
785 l_currepe_set(0).pgm_id := p_pgm_id;
786 l_currepe_set(0).pl_typ_id := p_pl_typ_id;
787 l_currepe_set(0).per_in_ler_id := p_per_in_ler_id;
788 l_currepe_set(0).ler_id := p_ler_id;
789 l_currepe_set(0).business_group_id := p_business_group_id;
790 l_currepe_set(0).val := p_bnft_amt;
791 --
792 else
793 --
794 -- unrestricted enhancement- added per_in_ler_id parameter
795 ben_epe_cache.get_perpilepe_list
796 (p_person_id => p_person_id
797 ,p_per_in_ler_id =>p_per_in_ler_id
798 --
799 ,p_inst_set => l_currepe_set
800 );
801 --
802 end if;
803 --
804 -- Clear epe context row
805 --
806 ben_epe_cache.init_context_pileperow;
807 --
808 -- 6330056: Reset global values before calculation.
809 g_computed_prem_tbl.delete;
810 l_comp_prem_idx := 0;
811 --
812 -- loop through all choices for this person
813 --
814 if l_currepe_set.count > 0 then
815 --
816 l_epr_elenum := 1;
817 --
818 for epe_elenum in l_currepe_set.first..l_currepe_set.last loop
819 hr_utility.set_location (' St EPE CORVF loop ',40);
820 --
821 -- Set the electable choice context variable
822 --
823 ben_epe_cache.g_currepe_row := l_currepe_set(epe_elenum);
824 --
825 hr_utility.set_location ('loop choices -> '||l_currepe_set(epe_elenum).elig_per_elctbl_chc_id,50);
826
827 -- BOF FONM
828 l_fonm_cvg_strt_dt := null ;
829 l_fonm_rt_strt_dt := null ;
830
831 if l_currepe_set(epe_elenum).fonm_cvg_strt_dt is not null then
832 l_FONM_flag := 'Y' ;
833 else
834 l_FONM_flag := 'N' ;
835 l_FONM_date := nvl(p_lf_evt_ocrd_dt, p_effective_date) ;
836 ben_manage_life_events.g_fonm_cvg_strt_dt := null ;
837 ben_manage_life_events.fonm := 'N' ;
838 end if ;
839 hr_utility.set_location ('premium fonm -> '||l_FONM_flag,50);
840
841 if l_FONM_flag = 'Y' then
842
843 if l_currepe_set(epe_elenum).fonm_cvg_strt_dt is not null then
844 l_FONM_date := l_currepe_set(epe_elenum).fonm_cvg_strt_dt ;
845 l_fonm_cvg_strt_dt:= l_currepe_set(epe_elenum).fonm_cvg_strt_dt ;
846 ben_manage_life_events.g_fonm_cvg_strt_dt := l_fonm_cvg_strt_dt ;
847 ben_manage_life_events.fonm := 'Y' ;
848 end if ;
849
850 /* -- for future
851 --- calcualte the rate start date
852 if l_fonm_rt_strt_dt is null then
856 ,p_per_in_ler_id => p_per_in_ler_id
853 -- Calcaulte the coverage start date
854 calc_fonm_dates(p_effective_date => nvl(p_lf_evt_ocrd_dt, p_effective_date)
855 ,p_business_group_id => p_business_group_id
857 ,p_person_id => p_person_id
858 ,p_inst_set => ben_epe_cache.g_currepe_row
859 ,p_calc_type => 'R'
860 ,p_cvg_strt_dt => l_dummy_d
861 ,p_rt_strt_dt => l_fonm_rt_strt_dt ) ;
862 end if ;
863 */
864
865 end if ;
866 hr_utility.set_location ('g_fonm_cvg_strt_dt '||ben_manage_life_events.g_fonm_cvg_strt_dt,50);
867 -- EOF FONM
868
869 -- l_enrt_bnft_id := l_currepe_set(epe_elenum).enrt_bnft_id;
870 -- l_coverage_value := null;
871
872 if l_currepe_set(epe_elenum).oipl_id is not NULL then
873 hr_utility.set_location ('getting apr_oipl -> '||l_currepe_set(epe_elenum).oipl_id,60);
874 -- FONM l_fonm_date send as param
875 open c_apr_oipl(l_currepe_set(epe_elenum).oipl_id,l_FONM_date);
876 fetch c_apr_oipl into l_apr;
877 if c_apr_oipl%notfound then
878 --
879 -- no actual premiums for this oipl
880 --
881 hr_utility.set_location ('apr_oipl -> '||l_currepe_set(epe_elenum).oipl_id||' <- not found',70);
882 close c_apr_oipl;
883 l_ap_found := false;
884 else
885 -- FONM l_fonm_date send as param
886 open c_opt(l_currepe_set(epe_elenum).oipl_id,l_FONM_date);
887 fetch c_opt into l_opt;
888 close c_opt;
889 end if;
890 elsif l_currepe_set(epe_elenum).pl_id is not NULL then
891 hr_utility.set_location ('getting apr_pl -> '||l_currepe_set(epe_elenum).pl_id,80);
892 -- FONM l_fonm_date send as param
893 open c_apr_pl(l_currepe_set(epe_elenum).pl_id,l_FONM_date);
894 fetch c_apr_pl into l_apr;
895 if c_apr_pl%notfound then
896 --
897 -- no actual premiums for this pl
898 --
899 hr_utility.set_location ('apr_pl -> '||l_currepe_set(epe_elenum).pl_id||' <- not found',90);
900 close c_apr_pl;
901 l_ap_found := false;
902 end if;
903 else
904 l_ap_found := false;
905 end if;
906 --
907 -- loop through all actl prem for this elctbl_chc
908 --
909 hr_utility.set_location ('FONM CVG DATE -> '||l_fonm_date ,90);
910 hr_utility.set_location ('FONM Rate DATE ->'||l_fonm_rt_strt_dt ,90);
911 if l_ap_found then
912
913
914 -- fonm caching validation for premium
915 -- fonm check the caching for person level and asg level
916 ben_person_object.get_object(p_person_id => p_person_id,
917 p_rec => l_fonm_per_rec);
918 if not nvl(l_fonm_cvg_strt_dt,l_FONM_date ) between l_fonm_per_rec.effective_start_date
919 and l_fonm_per_rec.effective_end_date then
920
921 hr_utility.set_location('cache clearence ' || l_package ,10);
922 ben_use_cvg_rt_date.fonm_clear_down_cache;
923 else
924 ben_person_object.get_object(p_person_id => p_person_id,
925 p_rec => l_fonm_ass_rec);
926 if not nvl(l_fonm_cvg_strt_dt,l_FONM_date ) between l_fonm_ass_rec.effective_start_date
927 and l_fonm_ass_rec.effective_end_date then
928 hr_utility.set_location('cache clearence ' || l_package ,10);
929 ben_use_cvg_rt_date.fonm_clear_down_cache;
930 end if ;
931 end if ;
932 --
933 --
934 loop
935 hr_utility.set_location (l_package||' St EPE AP loop ',100);
936 if l_currepe_set(epe_elenum).oipl_id is not null then
937 exit when c_apr_oipl%notfound;
938 elsif l_currepe_set(epe_elenum).pl_id is not null then
939 exit when c_apr_pl%notfound;
940 end if;
941 --hr_utility.set_location ('l_apr.actl_prem_id->'||l_apr.actl_prem_id,110);
942 -- hr_utility.set_location ('l_apr.prem_asnmt_cd -> '||l_apr.prem_asnmt_cd,110);
943 -- hr_utility.set_location ('l_apr.mlt_cd -> '||l_apr.mlt_cd,110);
944
945 -- if l_apr.prem_asnmt_cd = 'ENRT' then
946 compute_premium
947 (p_person_id => p_person_id,
948 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
949 p_effective_date => p_effective_date,
950 p_business_group_id => l_currepe_set(epe_elenum).business_group_id,
951 p_per_in_ler_id => l_currepe_set(epe_elenum).per_in_ler_id,
952 p_ler_id => l_currepe_set(epe_elenum).ler_id,
953 p_actl_prem_id => l_apr.actl_prem_id,
954 p_perform_rounding_flg => p_perform_rounding_flg,
955 p_calc_only_rt_val_flag => p_calc_only_rt_val_flag,
956 p_pgm_id => l_currepe_set(epe_elenum).pgm_id,
957 p_pl_typ_id => l_currepe_set(epe_elenum).pl_typ_id,
958 p_pl_id => l_currepe_set(epe_elenum).pl_id,
962 p_enrt_bnft_id => l_currepe_set(epe_elenum).enrt_bnft_id,
959 p_oipl_id => l_currepe_set(epe_elenum).oipl_id,
960 p_opt_id => l_opt.opt_id,
961 p_elig_per_elctbl_chc_id => l_currepe_set(epe_elenum).elig_per_elctbl_chc_id,
963 p_bnft_amt => l_currepe_set(epe_elenum).val,
964 p_prem_val => l_apr.val,
965 p_mlt_cd => l_apr.mlt_cd,
966 p_bnft_rt_typ_cd => l_apr.bnft_rt_typ_cd,
967 p_val_calc_rl => l_apr.val_calc_rl,
968 p_rndg_cd => l_apr.rndg_cd,
969 p_rndg_rl => l_apr.rndg_rl,
970 p_upr_lmt_val => l_apr.upr_lmt_val,
971 p_lwr_lmt_val => l_apr.lwr_lmt_val,
972 p_upr_lmt_calc_rl => l_apr.upr_lmt_calc_rl,
973 p_lwr_lmt_calc_rl => l_apr.lwr_lmt_calc_rl,
974 p_fonm_cvg_strt_dt => l_fonm_cvg_strt_dt,
975 p_fonm_rt_strt_dt => l_fonm_rt_strt_dt,
976 p_computed_val => l_val); -- ouput
977
978 --bug :1676551 assigning the valu to global variable
979 g_computed_prem_val := l_val ;
980 --
981 -- 6330056 : Store all the premiums evaluated into
982 -- global pl/sql tbl
983 l_comp_prem_idx := l_comp_prem_idx + 1;
984 g_computed_prem_tbl(l_comp_prem_idx).actl_prem_id := l_apr.actl_prem_id;
985 g_computed_prem_tbl(l_comp_prem_idx).val := l_val;
986 --
987 hr_utility.set_location ('Premium Count ' || l_comp_prem_idx,120);
988 hr_utility.set_location ('actl_prem_id ' || l_apr.actl_prem_id,120);
989 hr_utility.set_location ('Premium Value ' || l_val,120);
990 --
991 -- write enrt_prem record
992 --
993 hr_utility.set_location ('writing enrt_prem ',120);
994 --
995 -- Add enrt prem row to the varray
996 --
997 l_val_va.extend(1);
998 l_val_va(l_epr_elenum) := nvl(l_val,0);
999 l_uom_va.extend(1);
1000 l_uom_va(l_epr_elenum) := l_apr.uom;
1001 l_elig_per_elctbl_chc_id_va.extend(1);
1002 l_elig_per_elctbl_chc_id_va(l_epr_elenum) := l_currepe_set(epe_elenum).elig_per_elctbl_chc_id;
1003 l_enrt_bnft_id_va.extend(1);
1004 l_enrt_bnft_id_va(l_epr_elenum) := l_currepe_set(epe_elenum).enrt_bnft_id;
1005 l_actl_prem_id_va.extend(1);
1006 l_actl_prem_id_va(l_epr_elenum) := l_apr.actl_prem_id;
1007 l_business_group_id_va.extend(1);
1008 l_business_group_id_va(l_epr_elenum) := l_currepe_set(epe_elenum).business_group_id;
1009 --
1010 l_epr_attribute_category_va.extend(1);
1011 l_epr_attribute_category_va(l_epr_elenum) := null;
1012 l_epr_attribute1_va.extend(1);
1013 l_epr_attribute1_va(l_epr_elenum) := null;
1014 l_epr_attribute2_va.extend(1);
1015 l_epr_attribute2_va(l_epr_elenum) := null;
1016 l_epr_attribute3_va.extend(1);
1017 l_epr_attribute3_va(l_epr_elenum) := null;
1018 l_epr_attribute4_va.extend(1);
1022 l_epr_attribute6_va.extend(1);
1019 l_epr_attribute4_va(l_epr_elenum) := null;
1020 l_epr_attribute5_va.extend(1);
1021 l_epr_attribute5_va(l_epr_elenum) := null;
1023 l_epr_attribute6_va(l_epr_elenum) := null;
1024 l_epr_attribute7_va.extend(1);
1025 l_epr_attribute7_va(l_epr_elenum) := null;
1026 l_epr_attribute8_va.extend(1);
1027 l_epr_attribute8_va(l_epr_elenum) := null;
1028 l_epr_attribute9_va.extend(1);
1029 l_epr_attribute9_va(l_epr_elenum) := null;
1030 l_epr_attribute10_va.extend(1);
1031 l_epr_attribute10_va(l_epr_elenum) := null;
1032 l_epr_attribute11_va.extend(1);
1033 l_epr_attribute11_va(l_epr_elenum) := null;
1034 l_epr_attribute12_va.extend(1);
1035 l_epr_attribute12_va(l_epr_elenum) := null;
1036 l_epr_attribute13_va.extend(1);
1037 l_epr_attribute13_va(l_epr_elenum) := null;
1038 l_epr_attribute14_va.extend(1);
1039 l_epr_attribute14_va(l_epr_elenum) := null;
1040 l_epr_attribute15_va.extend(1);
1041 l_epr_attribute15_va(l_epr_elenum) := null;
1042 l_epr_attribute16_va.extend(1);
1043 l_epr_attribute16_va(l_epr_elenum) := null;
1044 l_epr_attribute17_va.extend(1);
1045 l_epr_attribute17_va(l_epr_elenum) := null;
1046 l_epr_attribute18_va.extend(1);
1047 l_epr_attribute18_va(l_epr_elenum) := null;
1048 l_epr_attribute19_va.extend(1);
1049 l_epr_attribute19_va(l_epr_elenum) := null;
1050 l_epr_attribute20_va.extend(1);
1051 l_epr_attribute20_va(l_epr_elenum) := null;
1052 l_epr_attribute21_va.extend(1);
1053 l_epr_attribute21_va(l_epr_elenum) := null;
1054 l_epr_attribute22_va.extend(1);
1055 l_epr_attribute22_va(l_epr_elenum) := null;
1056 l_epr_attribute23_va.extend(1);
1057 l_epr_attribute23_va(l_epr_elenum) := null;
1058 l_epr_attribute24_va.extend(1);
1059 l_epr_attribute24_va(l_epr_elenum) := null;
1060 l_epr_attribute25_va.extend(1);
1061 l_epr_attribute25_va(l_epr_elenum) := null;
1062 l_epr_attribute26_va.extend(1);
1063 l_epr_attribute26_va(l_epr_elenum) := null;
1064 l_epr_attribute27_va.extend(1);
1065 l_epr_attribute27_va(l_epr_elenum) := null;
1066 l_epr_attribute28_va.extend(1);
1067 l_epr_attribute28_va(l_epr_elenum) := null;
1068 l_epr_attribute29_va.extend(1);
1069 l_epr_attribute29_va(l_epr_elenum) := null;
1070 l_epr_attribute30_va.extend(1);
1071 l_epr_attribute30_va(l_epr_elenum) := null;
1072 --
1073 l_object_version_number_va.extend(1);
1074 l_object_version_number_va(l_epr_elenum) := 1;
1075 --
1076 l_request_id_va.extend(1);
1077 l_request_id_va(l_epr_elenum) := fnd_global.conc_request_id;
1078 l_program_application_id_va.extend(1);
1079 l_program_application_id_va(l_epr_elenum) := fnd_global.prog_appl_id;
1080 l_program_id_va.extend(1);
1081 l_program_id_va(l_epr_elenum) := fnd_global.conc_program_id;
1082 l_program_update_date_va.extend(1);
1083 l_program_update_date_va(l_epr_elenum) := sysdate;
1084 --
1085 l_epr_elenum := l_epr_elenum+1;
1086
1087 -- end if;
1088 hr_utility.set_location ('get next pl or oipl and loop back',130);
1089 if l_currepe_set(epe_elenum).oipl_id is not null then
1090 hr_utility.set_location ('next c_apr_oipl for oipl_id -> '||l_currepe_set(epe_elenum).oipl_id,130);
1091 fetch c_apr_oipl into l_apr;
1092 else
1093 hr_utility.set_location ('next c_apr_pl for pl_id -> '||l_currepe_set(epe_elenum).pl_id,130);
1094 fetch c_apr_pl into l_apr;
1095 end if;
1096 hr_utility.set_location ('AFTER FETCH',140);
1097
1098 end loop;
1099
1100 end if;
1101 if l_currepe_set(epe_elenum).oipl_id is not null and l_ap_found then
1102 close c_apr_oipl;
1103 elsif l_currepe_set(epe_elenum).pl_id is not null and l_ap_found then
1104 close c_apr_pl;
1105 end if;
1106 l_ap_found := true;
1107 end loop;
1108 end if;
1109 --
1110 if l_elig_per_elctbl_chc_id_va.count > 0 then
1111 --
1112 if p_mode in ('U','R') then
1113 --
1114 FOR i IN l_elig_per_elctbl_chc_id_va.FIRST .. l_elig_per_elctbl_chc_id_va.LAST loop
1115 --
1116 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);
1117 l_enrt_prem_id := null;
1118 l_enrt_prem_id := ben_manage_unres_life_events.epr_exists
1119 (l_elig_per_elctbl_chc_id_va(i),
1120 l_enrt_bnft_id_va(i),
1121 l_actl_prem_id_va(i));
1122 --
1123 if l_enrt_prem_id is not null then
1124 --
1125 ben_manage_unres_life_events.update_enrt_prem
1129 ,p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id_va(i)
1126 ( p_enrt_prem_id => l_enrt_prem_id
1127 ,p_val => l_val_va(i)
1128 ,p_uom => l_uom_va(i)
1130 ,p_enrt_bnft_id => l_enrt_bnft_id_va(i)
1131 ,p_actl_prem_id => l_actl_prem_id_va(i)
1132 ,p_business_group_id => p_business_group_id
1133 ,p_object_version_number => l_object_version_number
1134 ,p_request_id => l_request_id_va(i)
1135 ,p_program_application_id => l_program_application_id_va(i)
1136 ,p_program_id => l_program_id_va(i)
1137 ,p_program_update_date => l_program_update_date_va(i)
1138 );
1139 else
1140 --
1141 INSERT INTO ben_enrt_prem
1142 (enrt_prem_id,
1143 val,
1144 uom,
1145 elig_per_elctbl_chc_id,
1146 enrt_bnft_id,
1147 actl_prem_id,
1148 business_group_id,
1149 epr_attribute_category,
1150 epr_attribute1,
1151 epr_attribute2,
1152 epr_attribute3,
1153 epr_attribute4,
1154 epr_attribute5,
1155 epr_attribute6,
1156 epr_attribute7,
1157 epr_attribute8,
1158 epr_attribute9,
1159 epr_attribute10,
1160 epr_attribute11,
1161 epr_attribute12,
1162 epr_attribute13,
1163 epr_attribute14,
1164 epr_attribute15,
1165 epr_attribute16,
1166 epr_attribute17,
1167 epr_attribute18,
1168 epr_attribute19,
1169 epr_attribute20,
1170 epr_attribute21,
1171 epr_attribute22,
1172 epr_attribute23,
1173 epr_attribute24,
1174 epr_attribute25,
1175 epr_attribute26,
1176 epr_attribute27,
1177 epr_attribute28,
1178 epr_attribute29,
1179 epr_attribute30,
1180 object_version_number,
1181 request_id,
1182 program_application_id,
1183 program_id,
1184 program_update_date
1185 )
1186 VALUES
1187 (ben_enrt_prem_s.nextval,
1188 l_val_va(i),
1189 l_uom_va(i),
1190 l_elig_per_elctbl_chc_id_va(i),
1191 l_enrt_bnft_id_va(i),
1192 l_actl_prem_id_va(i),
1193 l_business_group_id_va(i),
1194 l_epr_attribute_category_va(i),
1195 l_epr_attribute1_va(i),
1196 l_epr_attribute2_va(i),
1197 l_epr_attribute3_va(i),
1198 l_epr_attribute4_va(i),
1199 l_epr_attribute5_va(i),
1200 l_epr_attribute6_va(i),
1201 l_epr_attribute7_va(i),
1202 l_epr_attribute8_va(i),
1203 l_epr_attribute9_va(i),
1204 l_epr_attribute10_va(i),
1205 l_epr_attribute11_va(i),
1206 l_epr_attribute12_va(i),
1207 l_epr_attribute13_va(i),
1208 l_epr_attribute14_va(i),
1209 l_epr_attribute15_va(i),
1210 l_epr_attribute16_va(i),
1211 l_epr_attribute17_va(i),
1212 l_epr_attribute18_va(i),
1213 l_epr_attribute19_va(i),
1214 l_epr_attribute20_va(i),
1215 l_epr_attribute21_va(i),
1216 l_epr_attribute22_va(i),
1217 l_epr_attribute23_va(i),
1218 l_epr_attribute24_va(i),
1219 l_epr_attribute25_va(i),
1220 l_epr_attribute26_va(i),
1221 l_epr_attribute27_va(i),
1222 l_epr_attribute28_va(i),
1223 l_epr_attribute29_va(i),
1224 l_epr_attribute30_va(i),
1225 l_object_version_number_va(i),
1226 l_request_id_va(i),
1227 l_program_application_id_va(i),
1228 l_program_id_va(i),
1229 l_program_update_date_va(i)
1230 );
1231 end if;
1232 end loop;
1233 --
1234 else
1235 --
1236 FORALL i IN l_elig_per_elctbl_chc_id_va.FIRST .. l_elig_per_elctbl_chc_id_va.LAST
1237 INSERT INTO ben_enrt_prem
1238 (enrt_prem_id,
1239 val,
1240 uom,
1241 elig_per_elctbl_chc_id,
1242 enrt_bnft_id,
1243 actl_prem_id,
1244 business_group_id,
1245 epr_attribute_category,
1246 epr_attribute1,
1247 epr_attribute2,
1248 epr_attribute3,
1249 epr_attribute4,
1250 epr_attribute5,
1251 epr_attribute6,
1252 epr_attribute7,
1253 epr_attribute8,
1254 epr_attribute9,
1255 epr_attribute10,
1256 epr_attribute11,
1257 epr_attribute12,
1258 epr_attribute13,
1259 epr_attribute14,
1260 epr_attribute15,
1261 epr_attribute16,
1262 epr_attribute17,
1263 epr_attribute18,
1264 epr_attribute19,
1265 epr_attribute20,
1266 epr_attribute21,
1267 epr_attribute22,
1268 epr_attribute23,
1269 epr_attribute24,
1270 epr_attribute25,
1271 epr_attribute26,
1272 epr_attribute27,
1273 epr_attribute28,
1274 epr_attribute29,
1275 epr_attribute30,
1276 object_version_number,
1277 request_id,
1278 program_application_id,
1279 program_id,
1280 program_update_date
1281 )
1282 VALUES
1283 (ben_enrt_prem_s.nextval,
1284 l_val_va(i),
1285 l_uom_va(i),
1286 l_elig_per_elctbl_chc_id_va(i),
1287 l_enrt_bnft_id_va(i),
1288 l_actl_prem_id_va(i),
1289 l_business_group_id_va(i),
1290 l_epr_attribute_category_va(i),
1291 l_epr_attribute1_va(i),
1292 l_epr_attribute2_va(i),
1293 l_epr_attribute3_va(i),
1294 l_epr_attribute4_va(i),
1295 l_epr_attribute5_va(i),
1296 l_epr_attribute6_va(i),
1297 l_epr_attribute7_va(i),
1298 l_epr_attribute8_va(i),
1299 l_epr_attribute9_va(i),
1300 l_epr_attribute10_va(i),
1301 l_epr_attribute11_va(i),
1302 l_epr_attribute12_va(i),
1303 l_epr_attribute13_va(i),
1304 l_epr_attribute14_va(i),
1305 l_epr_attribute15_va(i),
1306 l_epr_attribute16_va(i),
1307 l_epr_attribute17_va(i),
1308 l_epr_attribute18_va(i),
1309 l_epr_attribute19_va(i),
1310 l_epr_attribute20_va(i),
1311 l_epr_attribute21_va(i),
1312 l_epr_attribute22_va(i),
1313 l_epr_attribute23_va(i),
1314 l_epr_attribute24_va(i),
1315 l_epr_attribute25_va(i),
1316 l_epr_attribute26_va(i),
1317 l_epr_attribute27_va(i),
1318 l_epr_attribute28_va(i),
1319 l_epr_attribute29_va(i),
1320 l_epr_attribute30_va(i),
1321 l_object_version_number_va(i),
1322 l_request_id_va(i),
1323 l_program_application_id_va(i),
1324 l_program_id_va(i),
1325 l_program_update_date_va(i)
1326 );
1327 --
1328 end if;
1329 --
1330 end if;
1331 --
1332 -- Clear epe context row
1333 --
1334 ben_epe_cache.init_context_pileperow;
1335 --
1336 hr_utility.set_location ('Leaving '||l_package,99);
1337 end main;
1338 end ben_determine_actual_premium;