[Home] [Help]
PACKAGE BODY: APPS.BEN_IREC_PROCESS
Source
1 PACKAGE BODY ben_irec_process AS
2 /* $Header: benirecp.pkb 120.7 2008/01/07 15:52:13 rtagarra noship $ */
3 --
4 /*
5 +========================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +========================================================================+
10 Name
11 Manage iRecruitement processes.
12 Purpose
13 This package contains all the procedures used in iRec flow.
14 History
15 Date Who Version What?
16 ---- --- ------- -----
17 23 Sep 04 pbodla 115.0 Created.
18 29 Sep 04 pabodla 115.1 Added create_enrollment_for_irec
19 05-Apr-05 vvprabhu 115.2 Bug 4254792 - p_return_status added to manage_life_events_w
20 11-Jul-07 rtagarra 115.3 Bug 6079424 - Added nvl condition for oipl case,
21 Rate condtion,added some more conditions.
22 19-Jul-07 rtagarra 115.4 Bug 6236847 - Passed correct enrt_rt_id and values to election_information_w,
23 added exception handling and proper validations.
24 24-Jul-07 rtagarra 115.6 Bug 6271595 - Passed correct arguments to election_information_w.
25 27-jul-07 nhunur 115.7 removed savepoint and corrected exception handling
26 30-jul-07 nhunur 115.8 Flat amount rates need to be handled.
27 07-Jan-08 rtagarra 115.9 Changed the message number
28 */
29 g_package VARCHAR2 (80) := 'ben_irec_process';
30 --
31 PROCEDURE p_transfer_bckdt_data (
32 p_business_group_id IN NUMBER,
33 p_effective_date IN DATE,
34 p_assignment_id IN NUMBER,
35 p_irec_per_in_ler_id IN NUMBER
36 )
37 IS
38 --
39 l_proc VARCHAR2 (80) := 'p_transfer_bckdt_data';
40 --
41 -- Cursor to find the irec backed out per in ler.
42 --
43 CURSOR c_bckdt_pil (p_assignment_id NUMBER)
44 IS
45 SELECT pil.per_in_ler_id, pil.per_in_ler_stat_cd
46 FROM ben_per_in_ler pil
47 WHERE pil.per_in_ler_id <> p_irec_per_in_ler_id
48 AND pil.assignment_id = p_assignment_id
49 ORDER BY pil.per_in_ler_id;
50
51 --
52 CURSOR c_bckdt_bnft (cp_per_in_ler_id IN NUMBER)
53 IS
54 SELECT enb.enrt_bnft_id, enb.entr_val_at_enrt_flag, enb.dflt_val,
55 enb.val, enb.dflt_flag, enb.object_version_number,
56 enb.prtt_enrt_rslt_id, enb.cvg_mlt_cd, epe.pl_id, epe.pgm_id,
57 epe.oipl_id, enb.ordr_num
58 FROM ben_enrt_bnft enb, ben_elig_per_elctbl_chc epe
59 WHERE enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
60 AND epe.per_in_ler_id = cp_per_in_ler_id;
61
62 --
63 CURSOR c_active_bnft (
64 cp_irec_per_in_ler_id IN NUMBER,
65 cp_pgm_id IN NUMBER,
66 cp_pl_id IN NUMBER,
67 cp_oipl_id IN NUMBER,
68 cp_ordr_num IN NUMBER
69 )
70 IS
71 SELECT enb.enrt_bnft_id, enb.entr_val_at_enrt_flag, enb.dflt_val,
72 enb.val, enb.dflt_flag, enb.object_version_number,
73 enb.prtt_enrt_rslt_id, enb.cvg_mlt_cd, epe.pl_id, epe.pgm_id,
74 epe.oipl_id, enb.ordr_num
75 FROM ben_enrt_bnft enb, ben_elig_per_elctbl_chc epe
76 WHERE enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
77 AND epe.per_in_ler_id = cp_irec_per_in_ler_id
78 AND NVL (epe.pgm_id, -1) = NVL (cp_pgm_id, -1)
79 AND NVL (epe.pl_id, -1) = NVL (cp_pl_id, -1)
80 AND NVL (epe.oipl_id, -1) = NVL (cp_oipl_id, -1)
81 AND enb.ordr_num = cp_ordr_num;
82
83 --
84 l_bckdt_bnft_rec c_bckdt_bnft%ROWTYPE;
85 l_bnft_rec_reset c_bckdt_bnft%ROWTYPE;
86 l_bnft_entr_val_found BOOLEAN;
87 l_num_bnft_recs NUMBER := 0;
88 l_active_bnft_rec c_active_bnft%ROWTYPE;
89 l_active_bnft_rec_reset c_active_bnft%ROWTYPE;
90
91 --
92 CURSOR c_bckdt_rt (cp_bckdt_per_in_ler_id NUMBER)
93 IS
94 SELECT ecr.enrt_rt_id, ecr.dflt_val, ecr.val,
95 ecr.entr_val_at_enrt_flag, epe.pl_id, epe.pgm_id, epe.oipl_id,
96 ecr.acty_base_rt_id, ecr.ann_val
97 FROM ben_enrt_rt ecr, ben_elig_per_elctbl_chc epe
98 WHERE epe.per_in_ler_id = cp_bckdt_per_in_ler_id
99 AND ecr.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
100 AND ecr.entr_val_at_enrt_flag = 'Y'
101 AND ecr.spcl_rt_enrt_rt_id IS NULL
102 UNION
103 SELECT ecr.enrt_rt_id, ecr.dflt_val, ecr.val,
104 ecr.entr_val_at_enrt_flag, epe.pl_id, epe.pgm_id, epe.oipl_id,
105 ecr.acty_base_rt_id, ecr.ann_val
106 FROM ben_enrt_rt ecr,
107 ben_elig_per_elctbl_chc epe,
108 ben_enrt_bnft enb
109 WHERE ecr.enrt_bnft_id = enb.enrt_bnft_id
110 AND epe.per_in_ler_id = cp_bckdt_per_in_ler_id
111 AND enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
112 AND ecr.entr_val_at_enrt_flag = 'Y'
113 AND ecr.spcl_rt_enrt_rt_id IS NULL;
114
115 --
116 CURSOR c_active_rt (
117 cp_irec_per_in_ler_id NUMBER,
118 cp_pgm_id IN NUMBER,
119 cp_pl_id IN NUMBER,
120 cp_oipl_id IN NUMBER,
121 cp_acty_base_rt_id IN NUMBER,
122 cp_ordr_num IN NUMBER
123 )
124 IS
125 SELECT ecr.enrt_rt_id, ecr.dflt_val, ecr.val,
126 ecr.entr_val_at_enrt_flag, ecr.acty_base_rt_id, ecr.ann_val,
127 ecr.object_version_number, ecr.entr_ann_val_flag
128 FROM ben_enrt_rt ecr, ben_elig_per_elctbl_chc epe
129 WHERE epe.per_in_ler_id = cp_irec_per_in_ler_id
130 AND ecr.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
131 AND ecr.entr_val_at_enrt_flag = 'Y'
132 AND ecr.spcl_rt_enrt_rt_id IS NULL
133 AND NVL (epe.pgm_id, -1) = NVL (cp_pgm_id, -1)
134 AND NVL (epe.pl_id, -1) = NVL (cp_pl_id, -1)
135 AND NVL (epe.oipl_id, -1) = NVL (cp_oipl_id, -1)
136 AND ecr.acty_base_rt_id = cp_acty_base_rt_id
137 UNION
138 SELECT ecr.enrt_rt_id, ecr.dflt_val, ecr.val,
139 ecr.entr_val_at_enrt_flag, ecr.acty_base_rt_id, ecr.ann_val,
140 ecr.object_version_number, ecr.entr_ann_val_flag
141 FROM ben_enrt_rt ecr,
142 ben_elig_per_elctbl_chc epe,
143 ben_enrt_bnft enb
144 WHERE ecr.enrt_bnft_id = enb.enrt_bnft_id
145 AND epe.per_in_ler_id = cp_irec_per_in_ler_id
146 AND enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
147 AND ecr.entr_val_at_enrt_flag = 'Y'
148 AND NVL (epe.pgm_id, -1) = NVL (cp_pgm_id, -1)
149 AND NVL (epe.pl_id, -1) = NVL (cp_pl_id, -1)
150 AND NVL (epe.oipl_id, -1) = NVL (cp_oipl_id, -1)
151 AND ecr.acty_base_rt_id = cp_acty_base_rt_id
152 AND ecr.spcl_rt_enrt_rt_id IS NULL;
153
154 --
155 l_active_rt_rec c_active_rt%ROWTYPE;
156 l_rt_rec_reset c_active_rt%ROWTYPE;
157 l_bckdt_per_in_ler_id NUMBER;
158 l_per_in_ler_stat_cd VARCHAR2 (30);
159 --
160 g_debug BOOLEAN := FALSE;
161 BEGIN
162 --
163 g_debug := hr_utility.debug_enabled;
164
165 --
166 IF g_debug
167 THEN
168 hr_utility.set_location ('Entering : ' || g_package || '.' || l_proc,
169 35
170 );
171 END IF;
172
173 --
174 /*
175 * Step 1 : Get the latest backed out pil which is linked to irec assignment.
176 * Step 2 : If it is in voided state then return.
177 * Step 3 : If in backed out state then copy the data for enter
178 * val at enrollment cases.
179 *
180 */
181 OPEN c_bckdt_pil (p_assignment_id);
182
183 FETCH c_bckdt_pil
184 INTO l_bckdt_per_in_ler_id, l_per_in_ler_stat_cd;
185
186 CLOSE c_bckdt_pil;
187
188 --
189 IF NVL (l_per_in_ler_stat_cd, 'ZZZ') = 'BCKDT'
190 THEN
191 --
192 -- Get all the epe's attached to backed out pil and are enter val at enrollment
193 --
194 --
195 l_num_bnft_recs := 0;
196 l_bnft_entr_val_found := FALSE;
197 l_bckdt_bnft_rec := l_bnft_rec_reset;
198
199 --
200 OPEN c_bckdt_bnft (l_bckdt_per_in_ler_id);
201
202 LOOP
203 --
204 hr_utility.set_location ('Inside bnft loop ' || l_proc, 20);
205
206 --
207 FETCH c_bckdt_bnft
208 INTO l_bckdt_bnft_rec;
209
210 EXIT WHEN c_bckdt_bnft%NOTFOUND;
211
212 IF l_bckdt_bnft_rec.entr_val_at_enrt_flag = 'Y'
213 OR l_bckdt_bnft_rec.cvg_mlt_cd = 'SAAEAR'
214 THEN
215 l_bnft_entr_val_found := TRUE;
216
217 --
218 -- Now find the equivalent benefit row attached to the active
219 -- irec event and update it.
220 --
221 OPEN c_active_bnft (p_irec_per_in_ler_id,
222 l_bckdt_bnft_rec.pgm_id,
223 l_bckdt_bnft_rec.pl_id,
224 l_bckdt_bnft_rec.oipl_id,
225 l_bckdt_bnft_rec.ordr_num
226 );
227
228 FETCH c_active_bnft
229 INTO l_active_bnft_rec;
230
231 CLOSE c_active_bnft;
232
233 --
234 -- Now update the active benefit row.
235 --
236 IF ( l_active_bnft_rec.entr_val_at_enrt_flag = 'Y'
237 OR l_active_bnft_rec.cvg_mlt_cd = 'SAAEAR'
238 )
239 AND l_active_bnft_rec.val <> l_bckdt_bnft_rec.val
240 THEN
241 --
242 ben_enrt_bnft_api.update_enrt_bnft
243 (p_enrt_bnft_id => l_active_bnft_rec.enrt_bnft_id,
244 p_val => l_bckdt_bnft_rec.val,
245 p_object_version_number => l_active_bnft_rec.object_version_number,
246 p_effective_date => p_effective_date
247 );
248 --
249 END IF;
250 --
251 END IF;
252 --
253 END LOOP;
254
255 CLOSE c_bckdt_bnft;
256
257 --
258 -- Now copy the rates if they are enter val at enrollment.
259 -- ENTR_VAL_AT_ENRT_FLAG, ENTR_ANN_VAL_FLAG, ORDR_NUM
260 --
261 FOR l_bckdt_rt_rec IN c_bckdt_rt (l_bckdt_per_in_ler_id)
262 LOOP
263 --
264 -- Now find the equivalent benefit row attached to the active
265 -- irec event and update it.
266 --
267 OPEN c_active_rt (p_irec_per_in_ler_id,
268 l_bckdt_rt_rec.pgm_id,
269 l_bckdt_rt_rec.pl_id,
270 l_bckdt_rt_rec.oipl_id,
271 l_bckdt_rt_rec.acty_base_rt_id,
272 NULL
273 );
274
275 FETCH c_active_rt
276 INTO l_active_rt_rec;
277
278 CLOSE c_active_rt;
279
280 --
281 IF ( l_active_rt_rec.entr_val_at_enrt_flag = 'Y'
282 OR l_active_rt_rec.entr_ann_val_flag = 'Y'
283 )
284 AND ( l_active_rt_rec.val <> l_bckdt_rt_rec.val
285 OR l_bckdt_rt_rec.ann_val <> l_bckdt_rt_rec.ann_val
286 )
287 THEN
288 --
289 ben_enrollment_rate_api.update_enrollment_rate
290 (p_enrt_rt_id => l_active_rt_rec.enrt_rt_id,
291 p_val => l_bckdt_rt_rec.val,
292 p_ann_val => l_bckdt_rt_rec.ann_val,
293 p_object_version_number => l_active_rt_rec.object_version_number,
294 p_effective_date => p_effective_date
295 );
296 --
297 END IF;
298 END LOOP;
299 --
300 END IF;
301
302 --
303 IF g_debug
304 THEN
305 hr_utility.set_location ('Leaving : ' || g_package || '.' || l_proc,
306 35
307 );
308 END IF;
309 --
310 END p_transfer_bckdt_data;
311 --
312 --
313 PROCEDURE create_enrollment_for_irec (
314 p_irec_per_in_ler_id IN NUMBER,
315 p_person_id IN NUMBER,
316 p_business_group_id IN NUMBER,
317 p_effective_date IN DATE
318 )
319 IS
320 --
321 l_dummy VARCHAR2 (30);
322 l_api_error BOOLEAN;
323 l_benmngle_called BOOLEAN := FALSE;
324 --
325 CURSOR c_elig_per_electbl (p_irec_per_in_ler_id NUMBER)
326 IS
327 SELECT *
328 FROM ben_elig_per_elctbl_chc irec_epe
329 WHERE irec_epe.per_in_ler_id = p_irec_per_in_ler_id
330 AND irec_epe.approval_status_cd = 'IRC_BEN_A'; -- Bug 6079424
331 --
332 CURSOR c_unrstr_epe (
333 p_unrstr_per_in_ler_id NUMBER,
334 p_pgm_id NUMBER,
335 p_pl_id NUMBER,
336 p_oipl_id NUMBER
337 )
338 IS
339 SELECT *
340 FROM ben_elig_per_elctbl_chc irec_epe
341 WHERE per_in_ler_id = p_unrstr_per_in_ler_id
342 AND NVL (pgm_id, -1) = NVL (p_pgm_id, -1)
343 AND pl_id = p_pl_id
344 AND NVL (oipl_id, -1) = NVL (p_oipl_id, -1);
345
346 l_unrstr_epe c_unrstr_epe%ROWTYPE;
347 --
348 CURSOR c_dflt_enb (p_elig_per_elctbl_chc_id NUMBER)
349 IS
350 SELECT enb.*
351 FROM ben_enrt_bnft enb
352 WHERE enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
353 --
354 l_dflt_enb c_dflt_enb%ROWTYPE;
355 l_unres_dflt_enb c_dflt_enb%ROWTYPE;
356 --
357 --
358 CURSOR c_rt (cp_elig_per_elctbl_chc_id NUMBER, cp_enrt_bnft_id NUMBER)
359 IS
360 SELECT ecr.enrt_rt_id, ecr.dflt_val, ecr.val,
361 ecr.entr_val_at_enrt_flag, ecr.acty_base_rt_id
362 FROM ben_enrt_rt ecr
363 WHERE ecr.elig_per_elctbl_chc_id = cp_elig_per_elctbl_chc_id
364 AND ecr.business_group_id = p_business_group_id
365 -- AND ecr.entr_val_at_enrt_flag = 'Y'
366 AND ecr.spcl_rt_enrt_rt_id IS NULL
367 UNION
368 SELECT ecr.enrt_rt_id, ecr.dflt_val, ecr.val,
369 ecr.entr_val_at_enrt_flag, ecr.acty_base_rt_id
370 FROM ben_enrt_rt ecr
371 WHERE ecr.enrt_bnft_id = cp_enrt_bnft_id
372 AND ecr.business_group_id = p_business_group_id
373 -- AND ecr.entr_val_at_enrt_flag = 'Y'
374 AND ecr.spcl_rt_enrt_rt_id IS NULL;
375 --
376 l_rt c_rt%ROWTYPE;
377 --
378 --
379 --
380 l_temp_pen_ovn NUMBER;
381 l_unrstr_per_in_ler_id NUMBER;
382 l_prtt_rt_val_id1 NUMBER;
383 l_prtt_rt_val_id2 NUMBER;
384 l_prtt_rt_val_id3 NUMBER;
385 l_prtt_rt_val_id4 NUMBER;
386 l_prtt_rt_val_id5 NUMBER;
387 l_prtt_rt_val_id6 NUMBER;
388 l_prtt_rt_val_id7 NUMBER;
389 l_prtt_rt_val_id8 NUMBER;
390 l_prtt_rt_val_id9 NUMBER;
391 l_prtt_rt_val_id10 NUMBER;
392 l_proc VARCHAR2 (72) := g_package || '.create_enrollment_for_irec';
393 --
394 TYPE g_rt_rec IS RECORD (
395 enrt_rt_id ben_enrt_rt.enrt_rt_id%TYPE,
396 val ben_enrt_rt.val%TYPE,
397 dflt_val ben_enrt_rt.dflt_val%TYPE,
398 calc_val ben_enrt_rt.dflt_val%TYPE,
399 rt_strt_dt DATE,
400 rt_end_dt DATE,
401 cmcd_rt_val NUMBER,
402 ann_rt_val NUMBER
403 );
404 --
405 TYPE g_rt_table IS TABLE OF g_rt_rec
406 INDEX BY BINARY_INTEGER;
407 --
408 TYPE g_unres_rec IS RECORD (
409 enrt_rt_id ben_enrt_rt.enrt_rt_id%TYPE,
410 val ben_enrt_rt.val%TYPE,
411 dflt_val ben_enrt_rt.dflt_val%TYPE,
412 calc_val ben_enrt_rt.dflt_val%TYPE,
413 rt_strt_dt DATE,
414 rt_end_dt DATE,
415 cmcd_rt_val NUMBER,
416 ann_rt_val NUMBER
417 );
418 --
419 TYPE g_unres_rt_table IS TABLE OF g_unres_rec
420 INDEX BY BINARY_INTEGER;
421
422 --
423 l_unres_rt_table g_unres_rt_table;
424 l_unres_count NUMBER;
425 l_msg VARCHAR2 (2000);
426 l_rt_table g_rt_table;
427 l_count NUMBER;
428 l_return_status VARCHAR2 (20);
429 --
430 BEGIN
431 --
432 hr_utility.set_location ('Entering ' || l_proc, 332);
433 --
434 --
435 FOR l_rec IN c_elig_per_electbl (p_irec_per_in_ler_id)
436 LOOP
437 --
438 IF NOT l_benmngle_called
439 THEN
440 ben_on_line_lf_evt.p_manage_life_events_w
441 (p_person_id => p_person_id,
442 p_effective_date => p_effective_date,
443 p_business_group_id => l_rec.business_group_id,
444 p_mode => 'U',
445 p_return_status => l_return_status
446 );
447 l_benmngle_called := TRUE;
448 --
449 -- Get un restricted pil
450 --
451 l_unrstr_per_in_ler_id :=
452 benutils.get_per_in_ler_id
453 (p_person_id => p_person_id,
454 p_business_group_id => p_business_group_id,
455 p_ler_id => NULL,
456 p_lf_event_mode => NULL,
457 p_effective_date => p_effective_date
458 );
459 END IF;
460
461 --
462 OPEN c_unrstr_epe (l_unrstr_per_in_ler_id,
463 l_rec.pgm_id,
464 l_rec.pl_id,
465 l_rec.oipl_id
466 );
467
468 FETCH c_unrstr_epe INTO l_unrstr_epe;
469 -- If condition is added for say some comp obj may be inelig for 'U' Mode then the cursor will fail
470 IF c_unrstr_epe%FOUND
471 THEN
472 --
473 CLOSE c_unrstr_epe;
474 OPEN c_dflt_enb (l_rec.elig_per_elctbl_chc_id);
475 FETCH c_dflt_enb INTO l_dflt_enb;
476 CLOSE c_dflt_enb;
477
478 /* Rates */
479 FOR l_count IN 1..10
480 LOOP
481 --
482 -- Initialise array to null
483 --
484 l_rt_table (l_count).enrt_rt_id := NULL;
485 l_rt_table (l_count).val := NULL;
486 --
487 END LOOP;
488 --
489 -- Now get the rates.
490 --
491 l_count := 0;
492 --
493 FOR l_ecr_rec IN c_rt (l_rec.elig_per_elctbl_chc_id,
494 l_dflt_enb.enrt_bnft_id
495 )
496 LOOP
497 --
498 -- Get the prtt rate val for this choice
499 -- Use to pass to the enrollment process.
500 --
501 l_count := l_count + 1;
502 l_rt_table (l_count).enrt_rt_id := l_ecr_rec.enrt_rt_id;
503 l_rt_table (l_count).val := l_ecr_rec.val;
504 l_rt_table (l_count).dflt_val := l_ecr_rec.dflt_val;
505 --
506 hr_utility.set_location ('Irec enrt_rt_id ' || l_rt_table (l_count).enrt_rt_id,53);
507 hr_utility.set_location ('Irec val ' || l_rt_table (l_count).val,53);
508 hr_utility.set_location ('Irec Enrt_bnft_id ' || l_dflt_enb.enrt_bnft_id,53);
509 --
510 END LOOP;
511 --
512 OPEN c_dflt_enb (l_unrstr_epe.elig_per_elctbl_chc_id);
513 FETCH c_dflt_enb INTO l_unres_dflt_enb;
514 CLOSE c_dflt_enb;
515 --
516 FOR l_unres_count IN 1..10
517 LOOP
518 --
519 -- Initialise array to null
520 --
521 l_unres_rt_table (l_unres_count).enrt_rt_id := NULL;
522 l_unres_rt_table (l_unres_count).val := NULL;
523 l_unres_rt_table (l_unres_count).dflt_val := NULL;
524 --
525 END LOOP;
526 --
527 -- Now get the unrst rates.
528 --
529 l_unres_count := 0;
530 --
531 FOR l_ecr_rec IN c_rt (l_unrstr_epe.elig_per_elctbl_chc_id,
532 l_unres_dflt_enb.enrt_bnft_id
533 )
534 LOOP
535 --
536 -- Get the rate val for this choice.
537 -- Use to pass to the enrollment process.
538 --
539 l_unres_count := l_unres_count + 1;
540 l_unres_rt_table (l_unres_count).enrt_rt_id := l_ecr_rec.enrt_rt_id;
541 l_unres_rt_table (l_unres_count).val := l_ecr_rec.val;
542 l_unres_rt_table (l_unres_count).dflt_val := l_ecr_rec.dflt_val;
543 --
544 hr_utility.set_location('Unrst Enrt_rt_id' || l_unres_rt_table (l_unres_count).enrt_rt_id,96);
545 hr_utility.set_location('Unrst Val' || l_unres_rt_table (l_unres_count).val,96);
546 hr_utility.set_location('Unrst Enrt_bnft_id' || l_unres_dflt_enb.enrt_bnft_id,96);
547 --
548 END LOOP;
549 --
550 hr_utility.set_location('Enter election information',96);
551 --
552 --
553 ben_election_information.election_information_w
554 (p_validate => 'N',
555 p_elig_per_elctbl_chc_id => l_unrstr_epe.elig_per_elctbl_chc_id,
556 p_prtt_enrt_rslt_id => l_unrstr_epe.prtt_enrt_rslt_id,
557 p_effective_date => p_effective_date,
558 p_person_id => p_person_id,
559 p_enrt_mthd_cd => 'E',
560 p_enrt_bnft_id => l_unres_dflt_enb.enrt_bnft_id ,
561 p_bnft_val => NVL(l_dflt_enb.val,l_dflt_enb.dflt_val) ,
562 p_enrt_rt_id1 => l_unres_rt_table (1).enrt_rt_id,
563 p_prtt_rt_val_id1 => NULL,
564 p_rt_val1 => NVL (l_rt_table (1).val, l_rt_table (1).dflt_val),
565 p_ann_rt_val1 => l_rt_table (1).ann_rt_val,
566 p_rt_strt_dt1 => l_rt_table (1).rt_strt_dt,
567 p_rt_end_dt1 => l_rt_table (1).rt_end_dt,
568 p_rt_strt_dt_cd1 => NULL,
569 p_enrt_rt_id2 => l_unres_rt_table (2).enrt_rt_id,
570 p_prtt_rt_val_id2 => NULL,
571 p_rt_val2 => NVL (l_rt_table (2).val, l_rt_table (2).dflt_val ),
572 p_ann_rt_val2 => l_rt_table (2).ann_rt_val,
573 p_rt_strt_dt2 => l_rt_table (2).rt_strt_dt,
574 p_rt_end_dt2 => l_rt_table (2).rt_end_dt,
575 p_enrt_rt_id3 => l_unres_rt_table (3).enrt_rt_id ,
576 p_prtt_rt_val_id3 => NULL,
577 p_rt_val3 => NVL (l_rt_table (3).val,l_rt_table (3).dflt_val),
578 p_ann_rt_val3 => l_rt_table (3).ann_rt_val,
579 p_rt_strt_dt3 => l_rt_table (3).rt_strt_dt,
580 p_rt_end_dt3 => l_rt_table (3).rt_end_dt,
581 p_datetrack_mode => hr_api.g_correction,
582 p_suspend_flag => 'N' ,
583 p_effective_start_date => p_effective_date,
584 p_object_version_number => l_temp_pen_ovn,
585 p_business_group_id => l_unrstr_epe.business_group_id,
586 p_enrt_cvg_strt_dt => l_unrstr_epe.enrt_cvg_strt_dt,
587 p_enrt_cvg_thru_dt => NULL,
588 p_api_error => l_api_error
589 );
590 hr_utility.set_location('Completed election information',96);
591 --
592 ben_proc_common_enrt_rslt.process_post_enrollment_w
593 (p_per_in_ler_id => l_unrstr_epe.per_in_ler_id,
594 p_pgm_id => l_rec.pgm_id,
595 p_pl_id => l_rec.pl_id,
596 p_enrt_mthd_cd => 'E',
597 p_cls_enrt_flag => 'N',
598 p_proc_cd => NULL,
599 p_person_id => p_person_id,
600 p_business_group_id => p_business_group_id,
601 p_effective_date => p_effective_date,
602 p_validate => 'FALSE'
603 );
604 hr_utility.set_location('Completed post enrollment',96);
605 --
606 ELSE
607 CLOSE c_unrstr_epe;
608 END IF;
609 --
610 END LOOP;
611 --
612 hr_utility.set_location ('Leaving ' || l_proc, 332);
613 --
614 EXCEPTION
615 --
616 WHEN app_exception.application_exception
617 THEN
618 --
619 IF c_unrstr_epe%ISOPEN
620 THEN
621 CLOSE c_unrstr_epe;
622 --
623 ELSIF c_rt%ISOPEN
624 THEN
625 CLOSE c_rt;
626 --
627 ELSIF c_dflt_enb%ISOPEN
628 THEN
629 CLOSE c_dflt_enb;
630 --
631 END IF;
632
633 l_msg := fnd_message.get;
634 fnd_message.set_name ('BEN', 'BEN_94875_ICD_BENMNGLE_ERROR');
635 fnd_message.set_token ('BENMNGLE_ERROR', l_msg || substr(SQLERRM,1,50));
636 ROLLBACK;
637 RAISE;
638 --
639 WHEN OTHERS
640 THEN
641 --
642 IF c_unrstr_epe%ISOPEN
643 THEN
644 CLOSE c_unrstr_epe;
645 --
646 ELSIF c_rt%ISOPEN
647 THEN
648 CLOSE c_rt;
649 --
650 ELSIF c_dflt_enb%ISOPEN
651 THEN
652 CLOSE c_dflt_enb;
653 --
654 END IF;
655 --
656 l_msg := fnd_message.get;
657 fnd_message.set_name ('BEN', 'BEN_94875_ICD_BENMNGLE_ERROR');
658 fnd_message.set_token ('BENMNGLE_ERROR', l_msg || substr(SQLERRM,1,50));
659 --
660 ROLLBACK;
661 RAISE;
662 --
663 --
664 END create_enrollment_for_irec;
665 --
666 END ben_irec_process;