[Home] [Help]
PACKAGE BODY: APPS.BEN_CLOSE_ENROLLMENT
Source
1 PACKAGE BODY ben_close_enrollment AS
2 /* $Header: benclenr.pkb 120.13.12010000.3 2008/08/05 14:35:14 ubhat ship $ */
3 --
4 g_package varchar2(80) := 'ben_close_enrollment';
5 g_max_person_err Number := 100;
6 g_persons_errored Number := 0;
7 g_persons_procd Number := 0;
8 g_cache_per_proc g_cache_person_process_rec;
9 l_pend_approvals boolean;
10 --
11 -- Global cursor declaration
12 --
13 cursor gc_pel(c_per_in_ler_id number
14 ,c_business_group_id number)
15 is
16 select a.pil_elctbl_chc_popl_id
17 ,a.cls_enrt_dt_to_use_cd
18 ,a.dflt_asnd_dt
19 ,a.dflt_enrt_dt
20 ,a.auto_asnd_dt
21 ,a.elcns_made_dt
22 ,a.enrt_perd_end_dt
23 ,a.enrt_perd_strt_dt
24 ,a.enrt_typ_cycl_cd
25 ,a.pgm_id
26 ,a.pl_id
27 ,a.pil_elctbl_popl_stat_cd
28 ,a.procg_end_dt
29 ,'N' set_flag
30 ,a.object_version_number
31 ,a.defer_deenrol_flag
32 from ben_pil_elctbl_chc_popl a
33 where a.per_in_ler_id = c_per_in_ler_id
34 and a.business_group_id = c_business_group_id
35 and a.pil_elctbl_popl_stat_cd = 'STRTD';
36 --
37 cursor c_all_auto(p_pil_elctbl_chc_popl_id number) is
38 select 'Y'
39 from ben_elig_per_elctbl_chc
40 where elctbl_flag = 'Y'
41 and auto_enrt_flag = 'N'
42 and pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id;
43 --
44 l_dummy varchar2(30);
45 --
46 -- Type declaration
47 --
48 type g_pel_rec is table of gc_pel%rowtype index by binary_integer;
49 --
50 -- -----------------------------------------------------------------------------
51 -- |-----------------------< write_person_category >---------------------------|
52 -- -----------------------------------------------------------------------------
53 --
54 procedure write_person_category
55 (p_per_in_ler_clsed in boolean
56 ,p_audit_log in varchar2 default 'N'
57 ,p_error in boolean default false
58 ,p_business_group_id in number
59 ,p_person_id in number
60 ,p_effective_date in date)
61 is
62 --
63 cursor c1 (c_prtt_enrt_rslt_id number)
64 is
65 select ecd.dpnt_person_id, ecd.cvg_strt_dt, ecd.cvg_thru_dt
66 from ben_elig_cvrd_dpnt_f ecd,
67 ben_per_in_ler pil
68 where ecd.prtt_enrt_rslt_id is not NULL
69 and ecd.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
70 and ecd.business_group_id = p_business_group_id
71 and p_effective_date between effective_start_date
72 and effective_end_date
73 and pil.per_in_ler_id=ecd.per_in_ler_id
74 and pil.business_group_id=p_business_group_id
75 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
76 --
77 l_actn varchar2(80);
78 l_cache ben_batch_utils.g_comp_obj_table := ben_batch_utils.g_cache_comp;
79 l_cache_cnt binary_integer := ben_batch_utils.g_cache_comp_cnt;
80 l_category varchar2(30);
81 l_chg boolean;
82 l_detail varchar2(132) := 'Default election asigned - ' ||
83 'no current elections changed' ;
84 l_ovn number;
85 l_id number;
86 l_ovn1 varchar2(240);
87 l_actn_cd varchar2(30);
88 --
89 l_proc varchar2(80) := g_package || '.write_person_category';
90 --
91 begin
92 --
93 hr_utility.set_location ('Entering ' || l_proc, 05);
94 --
95 if p_error then
96 --
97 if p_audit_log = 'Y' then
98 --
99 l_category := 'ERROR_C';
100 l_detail := 'Error occur while closing enrollment';
101 --
102 ben_batch_utils.write_rec(p_typ_cd => l_category
103 ,p_text => l_detail);
104 --
105 end if;
106 --
107 else
108 --
109 if l_cache_cnt = 0 then
110 --
111 if p_per_in_ler_clsed then
112 l_category := 'CLSNODEF';
113 l_detail := 'Participant enrollment closed without defaults';
114 else
115 l_category := 'CLSNNOACTN';
116 l_detail := 'Participant processed without action';
117 end if;
118 --
119 else
120 --
121 l_chg := FALSE;
122 --
123 for i in 1..l_cache_cnt
124 loop
125 --
126 if l_cache(i).upd_flag or
127 l_cache(i).ins_flag or
128 l_cache(i).del_flag then
129 --
130 l_chg := TRUE;
131 exit;
132 --
133 end if;
134 --
135 end loop;
136 --
137 if p_per_in_ler_clsed then
138 --
139 if l_chg then
140 --
141 l_category := 'CLSDEFWCHG';
142 l_detail := 'Participant Enrollment closed without default changed';
143 --
144 else
145 --
146 l_category := 'CLSDEFNOCHG';
147 l_detail := 'Participant Enrollment closed without default unchanged';
148 --
149 end if;
150 --
151 else
152 --
153 if l_chg then
154 --
155 l_category := 'CLSNDEFWCHG';
156 l_detail := 'Participant Enrollment not closed with defaults changed';
157 --
158 else
159 --
160 l_category := 'CLSNDEFNOCHG';
161 l_detail :=
162 'Participant Enrollment not closed with defaults unchanged';
163 --
164 end if;
165 --
166 end if;
167 --
168 end if;
169 --
170 l_actn := 'Calling ben_batch_utils.write_rec (DEFAULT)...';
171 --
172 ben_batch_utils.write_rec(p_typ_cd => l_category
173 ,p_text => l_detail);
174 end if;
175 --
176 if p_audit_log = 'Y' then
177 --
178 for i in 1..l_cache_cnt
179 loop
180 --
181 if l_cache(i).del_flag then
182 --
183 l_actn_cd := 'DEL';
184 --
185 elsif l_cache(i).ins_flag then
186 --
187 l_actn_cd := 'INS';
188 --
189 elsif l_cache(i).upd_flag then
190 --
191 l_actn_cd := 'UPD';
192 --
193 elsif l_cache(i).def_flag then
194 --
195 l_actn_cd := 'DEF';
196 --
197 end if;
198 --
199 l_actn := 'Calling ben_batch_rate_info_api.create_batch_rate_info...';
200 --
201 ben_batch_rate_info_api.create_batch_rate_info
202 (p_batch_rt_id => l_id
203 ,p_benefit_action_id => benutils.g_benefit_action_id
204 ,p_person_id => p_person_id
205 ,p_pgm_id => l_cache(i).pgm_id
206 ,p_pl_id => l_cache(i).pl_id
207 ,p_oipl_id => l_cache(i).oipl_id
208 ,p_dflt_val => l_cache(i).bnft_amt
209 ,p_val => l_cache(i).prtt_enrt_rslt_id
210 ,p_enrt_cvg_strt_dt => l_cache(i).cvg_strt_dt /* Bug 4229221 */
211 ,p_enrt_cvg_thru_dt => l_cache(i).cvg_thru_dt /* Bug 4229221 */
212 ,p_actn_cd => l_actn_cd
213 ,p_dflt_flag => 'Y'
214 ,p_business_group_id => p_business_group_id
215 ,p_effective_date => p_effective_date
216 ,p_object_version_number => l_ovn
217 );
218 --
219 if l_cache(i).prtt_enrt_rslt_id is not NULL then
220 --
221 for l_rec in c1(l_cache(i).prtt_enrt_rslt_id)
222 loop
223 --
224 l_actn := 'Calling ben_batch_dpnt_info_api.create_batch_dpnt_info...';
225 --
226 ben_batch_dpnt_info_api.create_batch_dpnt_info
227 (p_batch_dpnt_id => l_id
228 ,p_person_id => p_person_id
229 ,p_benefit_action_id => benutils.g_benefit_action_id
230 ,p_business_group_id => p_business_group_id
231 ,p_pgm_id => l_cache(i).pgm_id
232 ,p_pl_id => l_cache(i).pl_id
233 ,p_oipl_id => l_cache(i).oipl_id
234 ,p_enrt_cvg_strt_dt => l_rec.cvg_strt_dt
235 ,p_enrt_cvg_thru_dt => l_rec.cvg_thru_dt
236 ,p_actn_cd => l_actn_cd
237 ,p_object_version_number => l_ovn1
238 ,p_dpnt_person_id => l_rec.dpnt_person_id
239 ,p_effective_date => p_effective_date);
240 --
241 end loop;
242 --
243 end if;
244 --
245 end loop;
246 --
247 end if;
248 --
249 hr_utility.set_location ('Leaving '||l_proc,10);
250 --
251 exception
252 --
253 when others then
254 --
255 ben_batch_utils.rpt_error(p_proc => l_proc
256 ,p_last_actn => l_actn
257 ,p_rpt_flag => TRUE);
258 --
259 raise;
260 --
261 end write_person_category;
262 --
263 -- -----------------------------------------------------------------------------
264 -- |-----------------------< write_pil_elctbl_popl >---------------------------|
265 -- -----------------------------------------------------------------------------
266 --
267 procedure write_pil_elctbl_popl
268 (p_rec in out nocopy g_pel_rec
269 ,p_cnt in number
270 ,p_business_group_id in number
271 ,p_effective_date in date)
272 is
273 --
274 l_proc varchar2(80) := g_package||'.write_pil_elctbl_popl';
275 l_status varchar2(30) := 'Started';
276 l_pgm_name ben_pgm_f.name%TYPE; -- UTF8 varchar2(80);
277 l_pl_name ben_pl_f.name%TYPE; -- UTF8 varchar2(80);
278 --
279 begin
280 --
281 hr_utility.set_location ('Entering '||l_proc,05);
282 --
283 if p_cnt > 0 then
284 --
285 ben_batch_utils.write
286 (ben_batch_utils.ret_str('Program name', 30) ||
287 ben_batch_utils.ret_str('Plan name', 30) ||
288 ben_batch_utils.ret_str('Cls_to_use_cd', 15) ||
289 ben_batch_utils.ret_str('Enrt_perd_strt', 15) ||
290 ben_batch_utils.ret_str('Enrt_perd_end', 15) ||
291 ben_batch_utils.ret_str('Procd_end_date', 15) ||
292 ben_batch_utils.ret_str('Deflt_apply_dt', 15) ||
293 ben_batch_utils.ret_str('Elcn_made_date', 15) ||
294 ben_batch_utils.ret_str('Dflt_asgn_date', 15) ||
295 ben_batch_utils.ret_str('Enrt status', 10)
296 );
297 --
298 for i in 1..p_cnt
299 loop
300 --
301 if p_rec(i).set_flag = 'Y' then
302 l_status := 'Processed';
303 else
304 l_status := 'Started';
305 end if;
306 --
307 l_pgm_name := ben_batch_utils.get_pgm_name
308 (p_pgm_id => p_rec(i).pgm_id
309 ,p_business_group_id => p_business_group_id
310 ,p_effective_date => p_effective_date);
311 --
312 l_pl_name := ben_batch_utils.get_pl_name
313 (p_pl_id => p_rec(i).pl_id
314 ,p_business_group_id => p_business_group_id
315 ,p_effective_date => p_effective_date);
316 --
317 ben_batch_utils.write
318 (ben_batch_utils.ret_str(l_pgm_name,30) ||
319 ben_batch_utils.ret_str(l_pl_name, 30) ||
320 ben_batch_utils.ret_str(p_rec(i).cls_enrt_dt_to_use_cd, 15) ||
321 ben_batch_utils.ret_str(p_rec(i).enrt_perd_strt_dt, 15) ||
322 ben_batch_utils.ret_str(p_rec(i).enrt_perd_end_dt, 15) ||
323 ben_batch_utils.ret_str(p_rec(i).procg_end_dt, 15) ||
324 ben_batch_utils.ret_str(p_rec(i).dflt_enrt_dt, 15) ||
325 ben_batch_utils.ret_str(p_rec(i).elcns_made_dt, 15) ||
326 ben_batch_utils.ret_str(p_rec(i).dflt_asnd_dt, 15) ||
327 ben_batch_utils.ret_str(l_status,10)
328 );
329 --
330 end loop;
331 --
332 end if;
333 --
334 hr_utility.set_location ('Leaving ' || l_proc, 10);
335 --
336 end write_pil_elctbl_popl;
337 --
338 -- -----------------------------------------------------------------------------
339 -- |-------------------------< submit_all_reports >----------------------------|
340 -- -----------------------------------------------------------------------------
341 --
342 procedure submit_all_reports
343 (p_rpt_flag in boolean default FALSE
344 ,p_audit_log in varchar2 default 'N')
345 is
346 l_proc varchar2(80) := g_package||'.submit_all_reports';
347 l_actn varchar2(80);
348 l_request_id number;
349 --
350 begin
351 --
352 hr_utility.set_location ('Entering '||l_proc,05);
353 --
354 if fnd_global.conc_request_id <> -1 then
355 --
356 if upper(p_audit_log) = 'Y' then
357 --
358 l_actn := 'Calling ben_batch_utils.batch_report (BENCLAUD)...';
359 --
360 ben_batch_utils.batch_report
361 (p_concurrent_request_id => fnd_global.conc_request_id
362 ,p_program_name => 'BENCLAUD'
363 ,p_request_id => l_request_id);
364 --
365 end if;
366 --
367 l_actn := 'Calling ben_batch_utils.batch_report (BENCLSUM)...';
368 --
369 ben_batch_utils.batch_report
370 (p_concurrent_request_id => fnd_global.conc_request_id
371 ,p_program_name => 'BENCLSUM'
372 ,p_request_id => l_request_id);
373 --
374 -- Submit the generic error by error type and error by person reports.
375 --
376 ben_batch_reporting.batch_reports
377 (p_concurrent_request_id => fnd_global.conc_request_id
378 ,p_report_type => 'ERROR_BY_ERROR_TYPE');
379 --
380 ben_batch_reporting.batch_reports
381 (p_concurrent_request_id => fnd_global.conc_request_id
382 ,p_report_type => 'ERROR_BY_PERSON');
383 --
384 end if;
385 --
386 hr_utility.set_location ('Leaving '||l_proc,10);
387 --
388 exception
389 --
390 when others then
391 --
392 ben_batch_utils.rpt_error(p_proc => l_proc
393 ,p_last_actn => l_actn
394 ,p_rpt_flag => p_rpt_flag);
395 --
396 raise;
397 --
398 end submit_all_reports;
399 --
400 -- -----------------------------------------------------------------------------
401 -- |-------------------------< update_per_in_ler >-----------------------------|
402 -- -----------------------------------------------------------------------------
403 --
404 -- This procedure is called to update the per_in_ler record with the
405 -- appropriate status code (per_in_ler_stat_cd) when the enrollment
406 -- is to be closed.
407 --
408 procedure update_per_in_ler
409 (p_per_in_ler_id in number
410 ,p_ler_id in number
411 ,p_person_id in number
412 ,p_effective_date in date
413 ,p_business_group_id in number
414 ,p_per_in_ler_stat_cd in varchar2
415 ,p_object_version_number in number
416 ,p_datetrack_mode in varchar2
417 )
418 is
419 --
420 l_effective_start_date date;
421 l_effective_end_date date;
422 l_object_version_number ben_per_in_ler.object_version_number%TYPE;
423 l_proc varchar2(80):=g_package||'.update_per_in_ler';
424 l_actn varchar2(80);
425 l_dummy_dt date;
426 l_procd_dt date;
427 l_strtd_dt date;
428 l_voidd_dt date;
429 --
430 begin
431 --
432 hr_utility.set_location('Entering '||l_proc,10);
433 --
434 l_object_version_number := p_object_version_number;
435 --
436 -- No updates need to be done when the status code
437 -- (p_per_in_ler_stat_cd) is null as the cursor would not have fetched
438 -- the required rows.
439 --
440 if p_per_in_ler_stat_cd is NOT NULL then
441 --
442 -- Generate Communications
443 --
444 -- Update person life event
445 --
446 ben_person_life_event_api.update_person_life_event
447 (p_validate => FALSE
448 ,p_per_in_ler_id => p_per_in_ler_id
449 ,p_per_in_ler_stat_cd => p_per_in_ler_stat_cd
450 ,p_object_version_number => l_object_version_number
451 ,p_effective_date => p_effective_date
452 ,p_business_group_id => p_business_group_id
453 ,p_program_application_id => fnd_global.prog_appl_id
454 ,p_program_id => fnd_global.conc_program_id
455 ,p_request_id => fnd_global.conc_request_id
456 ,p_program_update_date => sysdate
457 ,p_procd_dt => l_procd_dt
458 ,p_strtd_dt => l_strtd_dt
459 ,p_voidd_dt => l_voidd_dt);
460 --
461 ben_generate_communications.main
462 (p_person_id => p_person_id
463 ,p_per_in_ler_id => p_per_in_ler_id
464 ,p_ler_id => p_ler_id
465 ,p_business_group_id => p_business_group_id
466 ,p_proc_cd1 => 'CLSENRT'
467 ,p_proc_cd2 => 'HPAPRTTDE'
468 ,p_proc_cd3 => 'HPADPNTLC'
469 ,p_effective_date => p_effective_date
470 ,p_source => 'benclenr');
471 --
472 benutils.update_life_event_cache(p_open_and_closed => 'N');
473 --
474 end if;
475 --
476 hr_utility.set_location('Leaving:'||l_proc, 10);
477 --
478 exception
479 --
480 when others then
481 --
482 ben_batch_utils.rpt_error(p_proc=>l_proc, p_last_actn=>l_actn);
483 raise;
484 --
485 end update_per_in_ler;
486 --
487 -- -----------------------------------------------------------------------------
488 -- |----------------------< close_single_enrollment >--------------------------|
489 -- -----------------------------------------------------------------------------
490 --
491 -- This is the main procedure to be called by other modules and procedures
492 -- to close the enrollment for a particular per_in_ler_id.
493 --
494 procedure close_single_enrollment
495 (p_per_in_ler_id in number
496 ,p_effective_date in date
497 ,p_business_group_id in number
498 ,p_validate in boolean default FALSE
499 ,p_batch_flag in boolean default FALSE
500 ,p_person_action_id in Number default NULL
501 ,p_object_version_number in Number default NULL
502 ,p_audit_log in varchar2 default 'N'
503 ,p_close_cd in varchar2 default NULL
504 ,p_close_uneai_flag in varchar2
505 ,p_uneai_effective_date in date
506 )
507 is
508 --
509 -- Cursor to fetch the per_in_ler record and exclude unrestricted per_in_ler.
510 --
511
512 cursor c_pil
513 is
514 select pil.object_version_number
515 ,pil.person_id
516 ,pil.ler_id
517 ,pil.lf_evt_ocrd_dt
518 ,ppf.business_group_id
519 ,pil.per_in_ler_stat_cd
520 from ben_per_in_ler pil,
521 per_all_people_f ppf,
522 ben_ler_f ler
523 where pil.per_in_ler_id = p_per_in_ler_id
524 and pil.business_group_id = p_business_group_id
525 and pil.per_in_ler_stat_cd = 'STRTD'
526 and ler.ler_id = pil.ler_id
527 and ppf.person_id = pil.person_id
528 and p_effective_date
529 between ppf.effective_start_date
530 and ppf.effective_end_date
531 and p_effective_date
532 between ler.effective_start_date
533 and ler.effective_end_date
534 and ler.typ_cd <> 'SCHEDDU';
535 --
536 -- Cursor to pick enrollment results for which prtt is no longer eligible.
537 --
538 cursor c_no_lngr_elig
539 is
540 select prtt_enrt_rslt_id,
541 person_id,
542 pgm_id,
543 pl_id,
544 oipl_id,
545 object_version_number,
546 ler_id
547 from ben_prtt_enrt_rslt_f pen
548 where pen.per_in_ler_id = p_per_in_ler_id
549 and pen.no_lngr_elig_flag = 'Y'
550 and nvl(pen.enrt_cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
551 and pen.effective_end_date = hr_api.g_eot
552 and pen.prtt_enrt_rslt_stat_cd is null
553 and p_effective_date between pen.effective_start_date
554 and pen.effective_end_date
555 and pen.business_group_id = p_business_group_id;
556 --
557 l_person_id number(15);
558 --
559 -- Cursor to check pending approvals.
560 --
561 cursor c_get_pending_approvals is
562 select 'Y'
563 from wf_item_activity_statuses process ,
564 wf_process_activities activity ,
565 hr_api_transactions txn,
566 hr_api_transaction_steps step ,
567 hr_api_transaction_values vlv,
568 wf_item_attribute_values submit_attribute
569 where activity.process_name = 'ROOT'
570 and activity.process_item_type = activity.activity_item_type
571 and activity.instance_id = process.process_activity
572 and process.activity_status = 'ACTIVE'
573 and txn.item_type = process.item_type
574 and txn.item_key = process.item_key
575 and txn.selected_person_id = l_person_id
576 and txn.transaction_id = step.transaction_id
577 and step.api_name = 'BEN_PROCESS_COMPENSATION_W.PROCESS_API'
578 and vlv.number_value is not null
579 and submit_attribute.text_value = 'Y'
580 and txn.item_type = submit_attribute.item_type
581 and txn.item_key = submit_attribute.item_key
582 and submit_attribute.name = 'TRAN_SUBMIT'
583 and step.transaction_step_id = vlv.transaction_step_id
584 and vlv.name = 'P_PER_IN_LER_ID'
585 and vlv.number_value = p_per_in_ler_id;
586 --
587 cursor c_ler is
588 select null
589 from ben_per_in_ler pil,
590 ben_ler_f ler
591 where pil.per_in_ler_id = p_per_in_ler_id
592 and pil.business_group_id = p_business_group_id
593 and ler.ler_id = pil.ler_id
594 and ler.business_group_id = p_business_group_id
595 and p_effective_date
596 between ler.effective_start_date
597 and ler.effective_end_date
598 and ler.typ_cd = 'SCHEDDU';
599
600 --
601 --Bug(2300866): Check cursor for closing the life event.
602 --
603 cursor c_chk_epe_exists is
604 select ELIG_PER_ELCTBL_CHC_ID,
605 ELCTBL_FLAG
606 from ben_elig_per_elctbl_chc
607 where per_in_ler_id =p_per_in_ler_id
608 and business_group_id = p_business_group_id;
609 --
610 l_chk_epe_exists c_chk_epe_exists%rowtype;
611 --
612 -- Bug 2386000
613 CURSOR c_lee_rsn_for_plan (c_ler_id number, c_pl_id number ) IS
614 SELECT leer.lee_rsn_id
615 FROM ben_lee_rsn_f leer,
616 ben_popl_enrt_typ_cycl_f petc
617 WHERE leer.ler_id = c_ler_id
618 AND leer.business_group_id = p_business_group_id
619 AND p_effective_date BETWEEN leer.effective_start_date
620 AND leer.effective_end_date
621 AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
622 AND petc.pl_id = c_pl_id
623 AND petc.enrt_typ_cycl_cd = 'L' -- life event
624 AND petc.business_group_id = p_business_group_id
625 AND p_effective_date BETWEEN petc.effective_start_date
626 AND petc.effective_end_date;
627 --
628 CURSOR c_lee_rsn_for_program (c_ler_id number, c_pgm_id number )IS
629 SELECT leer.lee_rsn_id
630 FROM ben_lee_rsn_f leer,
631 ben_popl_enrt_typ_cycl_f petc
632 WHERE leer.ler_id = c_ler_id
633 AND leer.business_group_id = p_business_group_id
634 AND p_effective_date BETWEEN leer.effective_start_date
635 AND leer.effective_end_date
636 AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
637 AND petc.pgm_id = c_pgm_id
638 AND petc.enrt_typ_cycl_cd = 'L'
639 AND petc.business_group_id = p_business_group_id
640 AND p_effective_date BETWEEN petc.effective_start_date
641 AND petc.effective_end_date;
642 --
643 cursor c_min_max_enrt_dt(c_per_in_ler_id number
644 ,c_business_group_id number)
645 is
646 select
647 max(a.enrt_perd_end_dt),
648 min(a.enrt_perd_strt_dt)
649 from ben_pil_elctbl_chc_popl a
650 where a.per_in_ler_id = c_per_in_ler_id
651 and a.business_group_id = c_business_group_id
652 and a.pil_elctbl_popl_stat_cd = 'STRTD';
653 ---
654 l_lee_rsn_id number := null ;
655 --
656 -- Cursor Pec related variable.
657 --
658 l_pec_rec g_pel_rec;
659 l_pec_cnt binary_integer := 0;
660 --
661 -- Local Variables.
662 --
663 l_object_version_number number(15);
664 l_ler_id number(15);
665 l_datetrack_mode varchar2(80);
666 l_actn varchar2(80);
667 l_set_pel_stat_cd boolean := FALSE;
668 l_set_pil_stat_cd boolean := FALSE;
669 l_step integer := 0;
670 l_dump_num number(15);
671 l_susp_flag boolean;
672 l_dflt_flag boolean := FALSE;
673 l_per_in_ler_cls boolean := FALSE;
674 l_stage varchar2(80);
675 l_pers_ovn number := p_object_version_number;
676 l_enrt_cvg_end_dt_cd hr_lookups.lookup_code%TYPE; -- UTF8 varchar2(30);
677 l_rslt_eff_start_date date;
678 l_rslt_eff_end_date date;
679 l_lf_evt_ocrd_dt date;
680 l_min_enrt_perd_strt_dt date;
681 l_max_enrt_perd_end_dt date;
682 --
683 l_dummy_dt date;
684 l_dummy_num number(15);
685 l_dummy_varchar varchar2(30);
686 l_found varchar2(1);
687 l_per_business_group_id per_all_people_f.business_group_id%type;
688 --
689 l_proc varchar2(80) := g_package||'.close_single_enrollment';
690 l_dflt_enrt_date date;
691 l_pil_stat_cd varchar2(30);
692 l_auto_flag boolean := FALSE; --Bug 6144967
693 --
694 begin
695 --
696 hr_utility.set_location ('Entering '||l_proc,10);
697 --
698 -- Make sure all the mandatory input parameters are not null
699 --
700 hr_api.mandatory_arg_error(p_api_name => l_proc
701 ,p_argument => 'p_per_in_ler_id'
702 ,p_argument_value => p_effective_date);
703 --
704 hr_api.mandatory_arg_error(p_api_name => l_proc
705 ,p_argument => 'p_effective_date'
706 ,p_argument_value => p_effective_date);
707 --
708 hr_api.mandatory_arg_error(p_api_name => l_proc
709 ,p_argument => 'p_business_group_id'
710 ,p_argument_value => p_business_group_id);
711 -- Add environment init procedure
712 --
713 -- Work out if we are being called from a concurrent program
714 -- otherwise we need to initialize the environment
715 --
716 if fnd_global.conc_request_id = -1 then
717 --
718 ben_env_object.init(p_business_group_id => p_business_group_id,
719 p_effective_date => p_effective_date,
720 p_thread_id => 1,
721 p_chunk_size => 1,
722 p_threads => 1,
723 p_max_errors => 1,
724 p_benefit_action_id => null);
725 --
726 end if;
727 --
728 --
729 -- Issue a savepoint for validation mode.
730 --
731 savepoint close_enrollment;
732 --
733 -- if per_in_ler is unrestricted, do nothing
734 open c_ler;
735 fetch c_ler into l_dummy_varchar;
736 if c_ler%found then
737 close c_ler;
738 return;
739 end if;
740 close c_ler;
741 --
742 open c_pil;
743 fetch c_pil into l_object_version_number
744 ,l_person_id
745 ,l_ler_id
746 ,l_lf_evt_ocrd_dt
747 ,l_per_business_group_id
748 ,l_pil_stat_cd;
749 --
750 if c_pil%notfound then
751 close c_pil;
752 fnd_message.set_name('BEN','BEN_91272_PER_IN_LER_MISSING');
753 fnd_message.set_token('PROC',l_proc);
754 fnd_message.set_token('PER_IN_LER_ID',to_char(p_per_in_ler_id));
755 fnd_message.set_token('PERSON_ID',null);
756 fnd_message.set_token('LER_ID',null);
757 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
758 fnd_message.set_token('BG_ID',to_char(p_business_group_id));
759 fnd_message.raise_error;
760 end if;
761 --
762 close c_pil;
763 --
764 if p_batch_flag then
765 --
766 -- 2205261 : CWB Change : Pass the persons business group
767 -- Which could be different from business group id on per in ler
768 -- for CWB data.
769 --
770 ben_batch_utils.person_header
771 (p_person_id => l_person_id
772 ,p_business_group_id => l_per_business_group_id
773 ,p_effective_date => p_effective_date);
774 --
775 ben_batch_utils.ini('COMP_OBJ');
776 --
777 end if;
778 --
779 --- Check the Enmrt Closeing date is between the min and max of enreollment period
780 --- if not through the error
781 /* Phil wanted to talk with Fido before maing any changes. so the fix will wait
782 till phil get info from Fido
783 open c_min_max_enrt_dt(p_per_in_ler_id,p_business_group_id );
784 fetch c_min_max_enrt_dt into l_min_enrt_perd_strt_dt, l_max_enrt_perd_end_dt ;
785 close c_min_max_enrt_dt ;
786
787 if l_min_enrt_perd_strt_dt is not null and l_max_enrt_perd_end_dt is not null then
788 if not p_effective_date between l_min_enrt_perd_strt_dt and l_max_enrt_perd_end_dt then
789 fnd_message.set_name('BEN','BEN_93111_ENRT_PERD_CLS_DT');
790 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
791 fnd_message.set_token('ENRT_PERD_STRT_DT',l_min_enrt_perd_strt_dt);
792 fnd_message.set_token('ENRT_PERD_END_DT',l_max_enrt_perd_end_dt);
793 fnd_message.raise_error;
794 end if ;
795 end if ;
796 */
797
798 -- Loop through all the pil_elctbl_chc_popl records for the per_in_ler_id
799 --
800 for l_rec in gc_pel(c_per_in_ler_id => p_per_in_ler_id
801 ,c_business_group_id => p_business_group_id)
802 loop
803 --
804 l_pec_cnt := l_pec_cnt + 1;
805 l_set_pel_stat_cd := FALSE;
806 l_dflt_flag := FALSE;
807 l_auto_flag := FALSE;--Bug 6144967
808 --
809 l_pend_approvals := FALSE;
810 --
811 -- Test if pil elctbl chc popl is auto enrt in which case we can
812 -- close the per in ler
813 --
814 l_dummy := 'N';
815 open c_all_auto(l_rec.pil_elctbl_chc_popl_id);
816 fetch c_all_auto into l_dummy;
817 close c_all_auto;
818 --
819 if l_dummy = 'N' then
820 --
821 l_set_pel_stat_cd := TRUE;
822 l_auto_flag := TRUE; --Bug 6144967
823 --
824 elsif p_close_cd in ('FORCE', 'PRPENDTR') then -- 1674123
825 --
826 -- PB : When open enrollment called from authentication form if a active
827 -- life event exists and PSR opts closure of the active life event.
828 --
829 hr_utility.set_location('p_close_cd is : ' || p_close_cd, 5);
830 --
831 if p_close_cd = 'PRPENDTR' then
832 --
833 open c_get_pending_approvals;
834 fetch c_get_pending_approvals into l_found;
835 if c_get_pending_approvals%found then
836 l_pend_approvals := TRUE;
837 end if;
838 close c_get_pending_approvals;
839 --
840 if l_pend_approvals then
841 --
842 -- If pending approvals exists for the person do
843 -- not process any of the pil electble choice popls.
844 --
845 l_set_pel_stat_cd := FALSE;
846 l_set_pil_stat_cd := FALSE;
847 l_pec_cnt := 0;
848
849 if p_batch_flag then
850 --
851 ben_batch_utils.write( p_text =>
852 'Life event is not closed due to pending approvals.');
853 --
854 end if;
855
856 exit;
857 --
858 end if;
859 --
860 end if;
861 --
862 if l_rec.elcns_made_dt is NULL and
863 l_rec.dflt_enrt_dt is not null and
864 l_rec.dflt_asnd_dt is null and
865 not l_pend_approvals
866 then
867 --
868 hr_utility.set_location('Defaults will be assigned', 5);
869 --
870 l_dflt_flag := TRUE;
871 l_set_pel_stat_cd := TRUE;
872 --
873 else
874 hr_utility.set_location('Elections have been made or Defaults ' ||
875 'assigned or No Defaults required', 5);
876 --
877 l_set_pel_stat_cd := TRUE;
878 --
879 end if;
880 --
881 elsif l_rec.cls_enrt_dt_to_use_cd = 'ELCNSMADE' then
882 --
883 hr_utility.set_location('cls_enrt_dt_to_use_cd is ELCNSMADE', 10);
884 --
885 if l_rec.elcns_made_dt is not NULL then
886 --
887 hr_utility.set_location('Elections have been made', 10);
888 --
889 l_set_pel_stat_cd := TRUE;
890 --
891 elsif l_rec.procg_end_dt is not null and
892 l_rec.procg_end_dt <= p_effective_date then
893 --
894 -- Elections not made. Processing end date reached.
895 --
896 hr_utility.set_location('Elections not made. procg_end_dt reached', 10);
897 --
898 if l_rec.dflt_asnd_dt is not null then
899 --
900 -- The defaults have been assigned to the enrollment.
901 --
902 hr_utility.set_location('Defaults assigned.', 10);
903 --
904 l_set_pel_stat_cd := TRUE;
905 --
906 else
907 --
908 -- Processing end date reached. Defaults not asigned.
909 --
910 hr_utility.set_location('Defaults not assigned.', 10);
911 --
912 if l_rec.dflt_enrt_dt is not null and
913 l_rec.dflt_enrt_dt <= p_effective_date then
914 --
915 hr_utility.set_location('Defaults will be assigned', 10);
916 --
917 l_dflt_flag := TRUE;
918 l_set_pel_stat_cd := TRUE;
919 --
920 elsif l_rec.dflt_enrt_dt is NULL then
921 --
922 -- This comp-object does not need to be defaulted.
923 --
924 hr_utility.set_location('No action needed.', 10);
925 --
926 l_set_pel_stat_cd := TRUE;
927 --
928 end if;
929 --
930 end if;
931 --
932 end if;
933 --
934 elsif l_rec.cls_enrt_dt_to_use_cd = 'ENRTPERDEND' then
935 --
936 hr_utility.set_location('cls_enrt_dt_to_use_cd is ENRTPERDEND', 10);
937 --
938 if l_rec.enrt_perd_end_dt is not null and
939 l_rec.enrt_perd_end_dt <= p_effective_date then
940 --
941 hr_utility.set_location('Enrt perd over', 10);
942 --
943 if l_rec.elcns_made_dt is not Null or
944 l_rec.dflt_asnd_dt is not null then
945 --
946 hr_utility.set_location('Enrt end dt reached. Either elections ' ||
947 'have been made or enrt defaulted', 10);
948 --
949 l_set_pel_stat_cd := TRUE;
950 --
951 elsif l_rec.dflt_enrt_dt is not null and
952 l_rec.dflt_enrt_dt <= p_effective_date then
953 --
954 hr_utility.set_location('No defaults yet. Assigning defaults', 10);
955 --
956 l_dflt_flag := TRUE;
957 l_set_pel_stat_cd := TRUE;
958 --
959 elsif l_rec.dflt_enrt_dt is NULL then
960 --
961 -- This comp-object does not need to be defaulted.
962 --
963 hr_utility.set_location('No action needed.', 10);
964 --
965 l_set_pel_stat_cd := TRUE;
966 --
967 end if;
968 --
969 elsif l_rec.enrt_perd_end_dt is NULL then
970 --
971 hr_utility.set_location('enrt_perd_end date is NULL', 10);
972 --
973 fnd_message.set_name('BEN','BEN_91903_ENRT_PERD_END_DT_NUL');
974 fnd_message.set_token('PROC',l_proc);
975 fnd_message.set_token('PIL_ELCTBL_CHC_POPL_ID',
976 to_char(l_rec.pil_elctbl_chc_popl_id));
977 fnd_message.set_token('PERSON_ID',to_char(l_person_id));
978 fnd_message.set_token('LER_ID',to_char(l_ler_id));
979 fnd_message.set_token('BG_ID',to_char(p_business_group_id));
980 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
981 fnd_message.raise_error;
982 --
983 end if;
984 --
985 elsif l_rec.cls_enrt_dt_to_use_cd = 'PROCGEND' then
986 --
987 hr_utility.set_location('cls_enrt_dt_to_use_cd is PROCGEND', 10);
988 --
989 if l_rec.procg_end_dt is not null and
990 l_rec.procg_end_dt <= p_effective_date then
991 --
992 hr_utility.set_location('Processing end date reached.', 10);
993 --
994 if l_rec.elcns_made_dt is not null or
995 l_rec.dflt_asnd_dt is not null then
996 --
997 --
998 hr_utility.set_location('Enrt end dt reached. Either elections ' ||
999 'have been made or enrt defaulted', 10);
1000 --
1001 l_set_pel_stat_cd := TRUE;
1002 --
1003 elsif l_rec.dflt_enrt_dt is not NULL and
1004 l_rec.dflt_enrt_dt <= p_effective_date then
1005 --
1006 hr_utility.set_location('No defaults yet. Assigning defaults', 10);
1007 --
1008 l_dflt_flag := TRUE;
1009 l_set_pel_stat_cd := TRUE;
1010 --
1011 elsif (l_rec.dflt_enrt_dt is NULL) then
1012 --
1013 -- This comp-object does not need to be defaulted.
1014 --
1015 hr_utility.set_location('No action needed', 10);
1016 --
1017 l_set_pel_stat_cd := TRUE;
1018 --
1019 end if;
1020 --
1021 elsif l_rec.procg_end_dt is null then
1022 --
1023 hr_utility.set_location('Processing end date is null', 10);
1024 --
1025 fnd_message.set_name('BEN','BEN_91904_PROCG_END_DT_NULL');
1026 fnd_message.set_token('PROC',l_proc);
1027 fnd_message.set_token('PIL_ELCTBL_CHC_POPL_ID',
1028 to_char(l_rec.pil_elctbl_chc_popl_id));
1029 fnd_message.set_token('PERSON_ID',to_char(l_person_id));
1030 fnd_message.set_token('LER_ID',to_char(l_ler_id));
1031 fnd_message.set_token('BG_ID',to_char(p_business_group_id));
1032 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
1033 fnd_message.raise_error;
1034 --
1035 end if;
1036 --
1037 else
1038 --
1039 hr_utility.set_location('Invalid cls_enrt_dt_cd', 10);
1040 --
1041 fnd_message.set_name('BEN','BEN_91905_INVLD_CLS_ENRT_DT_CD');
1042 fnd_message.set_token('PROC',l_proc);
1043 fnd_message.set_token('CLS_ENRT_DT_TO_USE_CD',
1044 l_rec.cls_enrt_dt_to_use_cd);
1045 fnd_message.set_token('PIL_ELCTBL_CHC_POPL_ID',
1046 to_char(l_rec.pil_elctbl_chc_popl_id));
1047 fnd_message.raise_error;
1048 --
1049 end if;
1050 --
1051 if l_set_pel_stat_cd = TRUE then
1052 --
1053 hr_utility.set_location('Setting pil_elcbl_chc_popl_stat to PROCD', 10);
1054 --
1055
1056 --Start Bug 6144967
1057
1058 if (l_auto_flag) then
1059
1060 --Bug 6154180 : Removed the calls to Process_Post_Enrollments
1061
1062 -- Calling Multi Rows Edit
1063 --
1064 Ben_PRTT_ENRT_RESULT_api.multi_rows_edit
1065 (p_person_id => l_person_id
1066 ,p_effective_date => p_effective_date
1067 ,p_business_group_id => p_business_group_id
1068 ,p_pgm_id => l_rec.pgm_id
1069 ,p_per_in_ler_id => p_per_in_ler_id
1070 );
1071
1072 --
1073 -- Invoke post result process.
1074 --
1075
1076 Ben_proc_common_enrt_rslt.process_post_results
1077 (p_person_id => l_person_id
1078 ,p_enrt_mthd_cd => 'E'
1079 ,p_effective_date => p_effective_date
1080 ,p_business_group_id => p_business_group_id
1081 ,p_validate => FALSE
1082 ,p_per_in_ler_id => p_per_in_ler_id
1083 );
1084
1085 end if;
1086 --End Bug 6144967
1087
1088 ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl
1089 (p_validate => p_validate
1090 ,p_pil_elctbl_chc_popl_id => l_rec.pil_elctbl_chc_popl_id
1091 ,p_pil_elctbl_popl_stat_cd => 'PROCD'
1092 ,p_business_group_id => p_business_group_id
1093 ,p_object_version_number => l_rec.object_version_number
1094 ,p_effective_date => p_effective_date
1095 ,p_defer_deenrol_flag => l_rec.defer_deenrol_flag);
1096 --
1097 -- Set the flag to indicate that the record has been updated to PROCD.
1098 --
1099 l_rec.set_flag := 'Y';
1100 --
1101 -- Check COBRA eligibility.
1102 --
1103 if l_rec.pgm_id is not null then
1104 ben_cobra_requirements.chk_cobra_eligibility
1105 (p_per_in_ler_id => p_per_in_ler_id
1106 ,p_person_id => l_person_id
1107 ,p_pgm_id => l_rec.pgm_id
1108 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
1109 ,p_business_group_id => p_business_group_id
1110 ,p_effective_date => p_effective_date
1111 ,p_validate => p_validate
1112 );
1113 end if;
1114 end if;
1115 --
1116 if l_dflt_flag = TRUE then
1117 --
1118 hr_utility.set_location('Assign defaults', 10);
1119 --
1120 -- Bug 5407755
1121 -- Close enrollment date = nvl ( ( 'Defaults will be assigned on',
1122 -- OR 'Days after Enrollment Period to Apply Defaults'
1123 -- ),
1124 -- Enrollment Period End Date
1125 -- )
1126 --
1127 l_dflt_enrt_date := nvl(l_rec.dflt_enrt_dt, l_rec.enrt_perd_end_dt);
1128 -----Bug 7133885
1129 if l_dflt_enrt_Date is null or p_close_cd = 'FORCE'
1130 then
1131 l_dflt_enrt_date := p_effective_date;
1132 end if;
1133 hr_utility.set_location('ACE l_Dflt_enrt_Date = ' || l_Dflt_enrt_Date, 9999);
1134 ben_manage_default_enrt.default_comp_obj
1135 (p_validate => FALSE
1136 ,p_per_in_ler_id => p_per_in_ler_id
1137 ,p_person_id => l_person_id
1138 ,p_business_group_id => p_business_group_id
1139 ,p_effective_date => l_Dflt_enrt_Date
1140 ,p_pgm_id => l_rec.pgm_id
1141 ,p_pl_nip_id => l_rec.pl_id
1142 ,p_susp_flag => l_susp_flag
1143 ,p_batch_flag => p_batch_flag
1144 ,p_cls_enrt_flag => FALSE);
1145 --
1146 end if;
1147 --
1148 -- Store the record into a pl/sql table structure.
1149 --
1150 l_pec_rec(l_pec_cnt) := l_rec;
1151 --
1152 end loop;-- <<end of gc_pel cursor loop>>
1153 --
1154 --Bug(2300866):Should allow to close the life event if the electable
1155 -- choice is not there or elctbl_flag is N
1156 --
1157 open c_chk_epe_exists;
1158 fetch c_chk_epe_exists into l_chk_epe_exists;
1159 close c_chk_epe_exists;
1160 --
1161 hr_utility.set_location('l_set_pil_stat_cd = ' || 'FALSE' , 9999);
1162 hr_utility.set_location('l_pec_cnt = ' || to_char(l_pec_cnt), 9999);
1163 hr_utility.set_location('ELIG_PER_ELCTBL_CHC_ID = ' || l_chk_epe_exists.ELIG_PER_ELCTBL_CHC_ID, 9999);
1164 hr_utility.set_location('l_pil_stat_cd = ' || l_pil_stat_cd, 9999);
1165 --
1166 -- Third OR condition is for all PEL in PROCD and PIL in STRTD
1167 --
1168 if l_pec_cnt <> 0
1169 or (l_chk_epe_exists.ELIG_PER_ELCTBL_CHC_ID is NULL or l_chk_epe_exists.elctbl_flag <> 'Y')
1170 or ( l_pec_cnt = 0 and l_set_pil_stat_cd = FALSE
1171 and l_chk_epe_exists.ELIG_PER_ELCTBL_CHC_ID is NOT NULL
1172 and l_pil_stat_cd = 'STRTD' )
1173 then
1174 --
1175 l_set_pil_stat_cd := TRUE;
1176 --
1177 -- Loop through all the pil_elctbl_chc_popl records stored in the l_pec_rec.
1178 --
1179 if l_pec_cnt <> 0 then
1180 for i in 1..l_pec_cnt
1181 loop
1182 --
1183 -- If any of the records has not been set to PROCD then set the flag to
1184 -- FALSE so that the per_in_ler is not closed.
1185 --
1186 if l_pec_rec(i).set_flag = 'N' then
1187 --
1188 hr_utility.set_location('PEL not processed. ' ||
1189 'Cannot set per_in_ler status to PROCD', 10);
1190 --
1191 l_set_pil_stat_cd := FALSE;
1192 --
1193 exit;
1194 --
1195 end if;
1196 --
1197 end loop;
1198 --
1199 end if;
1200 --
1201 if l_set_pil_stat_cd = TRUE then
1202 --
1203 -- All pel_elctbl_chc records have been processed. We can set per_in_ler.
1204 -- Loop through enrollments for which the prtt is no longer eligible and
1205 -- haven't been closed yet.
1206 --
1207 hr_utility.set_location('set_pil_stat_cd flag is TRUE', 10);
1208 --
1209 for rslt in c_no_lngr_elig
1210 loop
1211 --
1212 --Bug 2386000
1213 l_lee_rsn_id := null ;
1214 open c_lee_rsn_for_plan(rslt.ler_id, rslt.pl_id );
1215 fetch c_lee_rsn_for_plan into l_lee_rsn_id ;
1216 close c_lee_rsn_for_plan ;
1217 --
1218 if l_lee_rsn_id is null and rslt.pgm_id is not null then
1219 open c_lee_rsn_for_program(rslt.ler_id, rslt.pgm_id);
1220 fetch c_lee_rsn_for_program into l_lee_rsn_id ;
1221 close c_lee_rsn_for_program ;
1222 end if;
1223 --
1224 -- Get the enrt_cvg_end_dt code for the comp object.
1225 --
1226 ben_determine_date.rate_and_coverage_dates
1227 (p_which_dates_cd => 'C'
1228 ,p_date_mandatory_flag => 'N'
1229 ,p_compute_dates_flag => 'N'
1230 ,p_per_in_ler_id => p_per_in_ler_id
1231 ,p_person_id => rslt.person_id
1232 ,p_pgm_id => rslt.pgm_id
1233 ,p_pl_id => rslt.pl_id
1234 ,p_oipl_id => rslt.oipl_id
1235 ,p_lee_rsn_id => l_lee_rsn_id
1236 ,p_business_group_id => p_business_group_id
1237 ,p_enrt_cvg_strt_dt => l_dummy_dt
1238 ,p_enrt_cvg_strt_dt_cd => l_dummy_varchar
1239 ,p_enrt_cvg_strt_dt_rl => l_dummy_num
1240 ,p_rt_strt_dt => l_dummy_dt
1241 ,p_rt_strt_dt_cd => l_dummy_varchar
1242 ,p_rt_strt_dt_rl => l_dummy_num
1243 ,p_enrt_cvg_end_dt => l_dummy_dt
1244 ,p_enrt_cvg_end_dt_cd => l_enrt_cvg_end_dt_cd
1245 ,p_enrt_cvg_end_dt_rl => l_dummy_num
1246 ,p_rt_end_dt => l_dummy_dt
1247 ,p_rt_end_dt_cd => l_dummy_varchar
1248 ,p_rt_end_dt_rl => l_dummy_num
1249 ,p_effective_date => p_effective_date
1250 ,p_lf_evt_ocrd_dt => null);
1251 --
1252 if nvl(l_enrt_cvg_end_dt_cd, '-1') <> 'WEM' then
1253 --
1254 -- The end date code is not 'when elections made'. Delete enrt.
1255 --
1256 hr_utility.set_location('End date cd not WEM. Deleting enrt', 10);
1257 --
1258 ben_prtt_enrt_result_api.delete_enrollment
1259 (p_validate => p_validate
1260 ,p_per_in_ler_id => p_per_in_ler_id
1261 ,p_prtt_enrt_rslt_id => rslt.prtt_enrt_rslt_id
1262 ,p_business_group_id => p_business_group_id
1263 ,p_effective_start_date => l_rslt_eff_start_date
1264 ,p_effective_end_date => l_rslt_eff_end_date
1265 ,p_object_version_number => rslt.object_version_number
1266 ,p_effective_date => p_effective_date
1267 ,p_datetrack_mode => 'DELETE'
1268 ,p_multi_row_validate => TRUE
1269 ,p_source => 'benclenr');
1270 --
1271 end if;
1272 --
1273 end loop; -- no_lngr_elig
1274 --
1275 -- Close unresolved actn items.
1276 --
1277 if p_close_uneai_flag='Y' then
1278 --
1279 hr_utility.set_location ('effectiv_date=' ||
1280 to_char(p_effective_date,'YYYY/MM/DD HH24:MI:SS'), 05);
1281 --
1282 hr_utility.set_location ('uneai_effectiv_date=' ||
1283 to_char(p_uneai_effective_date,'YYYY/MM/DD HH24:MI:SS'), 05);
1284 --
1285 ben_cls_unresolved_actn_item.cls_per_unresolved_actn_item
1286 (p_person_id => l_person_id
1287 ,p_effective_date => p_uneai_effective_date
1288 ,p_business_group_id => p_business_group_id);
1289 --
1290 end if;
1291 --
1292 -- Update the per_in_ler's status code to PROCD
1293 -- only when called from close enrt process or BENDSPLE form
1294 -- not from default enrt process
1295 --
1296 if ( p_batch_flag = TRUE or nvl(p_close_cd,'xxx') = 'FORCE' )
1297 then
1298 --
1299 ben_newly_ineligible.defer_delete_enrollment
1300 (p_per_in_ler_id => p_per_in_ler_id,
1301 p_person_id => l_person_id,
1302 p_business_group_id => p_business_group_id,
1303 p_effective_date => p_effective_date
1304 );
1305 --
1306 update_per_in_ler
1307 (p_per_in_ler_id => p_per_in_ler_id
1308 ,p_ler_id => l_ler_id
1309 ,p_person_id => l_person_id
1310 ,p_effective_date => p_effective_date
1311 ,p_business_group_id => p_business_group_id
1312 ,p_per_in_ler_stat_cd => 'PROCD'
1313 ,p_object_version_number => l_object_version_number
1314 ,p_datetrack_mode => hr_api.g_correction);
1315 end if;
1316 --
1317 -- Update person life event logging information to show event was
1318 -- closed but also created electable choices which must have been
1319 -- automatic.
1320 --
1321 l_per_in_ler_cls := TRUE;
1322 --
1323 benutils.update_life_event_cache(p_open_and_closed => 'Y');
1324 --
1325 end if;
1326 --
1327 end if;
1328 --
1329 if p_validate = TRUE then
1330 --
1331 rollback to close_enrollment;
1332 --
1333 end if;
1334 --
1335 -- If the procedure was called from within the batch process, do additionional
1336 -- processing.
1337 --
1338 if p_batch_flag = TRUE then
1339 --
1340 g_persons_procd := g_persons_procd + 1;
1341 --
1342 -- Log person related info.
1343 --
1344 --
1345 write_person_category
1346 (p_per_in_ler_clsed => l_per_in_ler_cls
1347 ,p_person_id => l_person_id
1348 ,p_audit_log => p_audit_log
1349 ,p_business_group_id => p_business_group_id
1350 ,p_effective_date => p_effective_date);
1351
1352 --
1353 -- Log pil_elctbl_popl info
1354 --
1355 write_pil_elctbl_popl
1356 (p_rec => l_pec_rec
1357 ,p_cnt => l_pec_cnt
1358 ,p_business_group_id => p_business_group_id
1359 ,p_effective_date => p_effective_date);
1360 --
1361 -- Calling write_comp...
1362 --
1363 ben_batch_utils.write_comp
1364 (p_business_group_id => p_business_group_id
1365 ,p_effective_date => p_effective_date);
1366 --
1367 if p_person_action_id is not null then
1368 --
1369 -- update the person action status to processed.
1370 --
1371 ben_person_actions_api.update_person_actions
1372 (p_person_action_id => p_person_action_id
1373 ,p_action_status_cd => 'P'
1374 ,p_object_version_number => l_pers_ovn
1375 ,p_effective_date => p_effective_date);
1376 --
1377 end if;
1378 --
1379 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1380 --
1381 end if;
1382 --
1383 hr_utility.set_location('Leaving:'||l_proc, 10);
1384 --
1385 exception
1386 --
1387 when others then
1388 --
1389 rollback to close_enrollment;
1390 --
1391 -- If called from a batch mode, do additional processing.
1392 --
1393 if p_batch_flag then
1394 --
1395 g_persons_errored := g_persons_errored + 1;
1396 --
1397 /* 666
1398 ben_batch_utils.write( p_text =>
1399 'Life event is not closed due to pending approvals.');
1400 */
1401 write_pil_elctbl_popl
1402 (p_rec => l_pec_rec
1403 ,p_cnt => l_pec_cnt
1404 ,p_business_group_id => p_business_group_id
1405 ,p_effective_date => p_effective_date);
1406 --
1407 ben_batch_utils.write_comp
1408 (p_business_group_id => p_business_group_id
1409 ,p_effective_date => p_effective_date);
1410 --
1411 ben_batch_utils.write_error_rec;
1412 --
1413 ben_batch_utils.rpt_error(p_proc => l_proc
1414 ,p_last_actn => l_actn
1415 ,p_rpt_flag => false);
1416 --
1417 ben_batch_utils.write(p_text => benutils.g_banner_minus);
1418 --
1419 -- Update the person action status code to errored.
1420 --
1421 if p_person_action_id is not null then
1422 --
1423 ben_person_actions_api.update_person_actions
1424 (p_person_action_id => p_person_action_id
1425 ,p_action_status_cd => 'E'
1426 ,p_object_version_number => l_pers_ovn
1427 ,p_effective_date => p_effective_date);
1428 --
1429 end if;
1430 --
1431 write_person_category(p_per_in_ler_clsed => l_per_in_Ler_cls
1432 ,p_person_id => l_person_id
1433 ,p_audit_log => p_audit_log
1434 ,p_error => TRUE
1435 ,p_business_group_id => p_business_group_id
1436 ,p_effective_date => p_effective_date);
1437 --
1438 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1439 --
1440 raise ben_batch_utils.g_record_error;
1441 --
1442 else
1443 fnd_message.raise_error;
1444 end if;
1445 --
1446 end close_single_enrollment;
1447 --
1448 -- -----------------------------------------------------------------------------
1449 -- |--------------------------< do_multithread >-------------------------------|
1450 -- -----------------------------------------------------------------------------
1451 --
1452 -- This is the main batch procedure to be called from the concurrent manager
1453 -- or interactively to close all the un-resolved per_in_ler/Enrollment.
1454 --
1455 procedure do_multithread
1456 (errbuf out nocopy varchar2
1457 ,retcode out nocopy number
1458 ,p_validate in varchar2 default 'N'
1459 ,p_benefit_action_id in number
1460 ,p_thread_id in number
1461 ,p_effective_date in varchar2
1462 ,p_business_group_id in number
1463 ,p_audit_log in varchar2 default 'N')
1464 is
1465 --
1466 -- Local variable declaration
1467 --
1468 l_effective_date date;
1469 l_proc varchar2(80) := g_package || '.do_multithread';
1470 l_person_id ben_person_actions.person_id%type;
1471 l_person_action_id ben_person_actions.person_action_id%type;
1472 l_object_version_number ben_person_actions.object_version_number%type;
1473 l_ler_id ben_person_actions.ler_id%type;
1474 l_range_id ben_batch_ranges.range_id%type;
1475 l_record_number number := 0;
1476 l_start_person_action_id number := 0;
1477 l_end_person_action_id number := 0;
1478 l_actn varchar2(80);
1479 l_cnt number(5):= 0;
1480 l_validate Boolean := FALSE;
1481 l_chunk_size number;
1482 l_threads number;
1483 --
1484 -- Cursor declarations
1485 --
1486 cursor c_range_thread
1487 is
1488 select ran.range_id
1489 ,ran.starting_person_action_id
1490 ,ran.ending_person_action_id
1491 from ben_batch_ranges ran
1492 where ran.range_status_cd = 'U'
1493 and ran.benefit_action_id = p_benefit_action_id
1494 and rownum < 2
1495 for update of ran.range_status_cd;
1496 --
1497 cursor c_person_thread
1498 is
1499 select ben.person_id
1500 ,ben.person_action_id
1501 ,ben.object_version_number
1502 ,ben.ler_id
1503 from ben_person_actions ben
1504 where ben.benefit_action_id = p_benefit_action_id
1505 and ben.action_status_cd <> 'P'
1506 and ben.person_action_id between l_start_person_action_id
1507 and l_end_person_action_id
1508 order by ben.person_action_id;
1509 --
1510 cursor c_parameter
1511 is
1512 select *
1513 from ben_benefit_actions ben
1514 where ben.benefit_action_id = p_benefit_action_id;
1515 --
1516 l_parm c_parameter%rowtype;
1517 l_commit number;
1518 --
1519 -- start bug 3079317
1520 l_rec benutils.g_active_life_event;
1521 l_env ben_env_object.g_global_env_rec_type;
1522 l_per_rec per_all_people_f%rowtype;
1523 l_encoded_message varchar2(2000);
1524 l_app_short_name varchar2(2000);
1525 l_message_name varchar2(2000);
1526 g_rec ben_type.g_report_rec;
1527 --
1528 -- end bug 3079317
1529
1530 begin
1531 --
1532 hr_utility.set_location ('Entering '||l_proc,10);
1533 --
1534 /*
1535 l_effective_date := to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
1536 l_effective_date := to_date(to_char(trunc(l_effective_date),'DD/MM/RRRR')
1537 ,'DD/MM/RRRR');
1538 */
1539 l_effective_date := trunc(fnd_date.canonical_to_date(p_effective_date));
1540 --
1541 -- Put row in fnd_sessions
1542 --
1543 dt_fndate.change_ses_date
1544 (p_ses_date => l_effective_date,
1545 p_commit => l_commit);
1546 --
1547 l_actn := 'Calling benutils.get_parameter...';
1548 benutils.get_parameter(p_business_group_id => p_business_group_id
1549 ,p_batch_exe_cd => 'BENCLENR'
1550 ,p_threads => l_threads
1551 ,p_chunk_size => l_chunk_size
1552 ,p_max_errors => g_max_person_err);
1553 --
1554 -- Set up benefits environment
1555 --
1556 ben_env_object.init(p_business_group_id => p_business_group_id,
1557 p_effective_date => l_effective_date,
1558 p_thread_id => p_thread_id,
1559 p_chunk_size => l_chunk_size,
1560 p_threads => l_threads,
1561 p_max_errors => g_max_person_err,
1562 p_benefit_action_id => p_benefit_action_id);
1563 --
1564 g_persons_procd := 0;
1565 g_persons_errored := 0;
1566 --
1567 ben_batch_utils.ini;
1568 --
1569 benutils.g_benefit_action_id := p_benefit_action_id;
1570 benutils.g_thread_id := p_thread_id;
1571 --
1572 -- Fetch the parameters defined for the batch process.
1573 --
1574 open c_parameter;
1575 fetch c_parameter into l_parm;
1576 close c_parameter;
1577 --
1578 if p_validate = 'Y' then
1579 l_validate := TRUE;
1580 else
1581 l_validate := FALSE;
1582 end if;
1583 --
1584 -- Print the parameters to the log file.
1585 --
1586 ben_batch_utils.print_parameters
1587 (p_thread_id => p_thread_id
1588 ,p_benefit_action_id => p_benefit_action_id
1589 ,p_validate => p_validate
1590 ,p_business_group_id => p_business_group_id
1591 ,p_effective_date => l_effective_date
1592 ,p_person_id => l_parm.person_id
1593 ,p_person_selection_rule_id => l_parm.person_selection_rl
1594 ,p_location_id => l_parm.location_id
1595 ,p_ler_id => l_parm.ler_id
1596 ,p_mode => l_parm.mode_cd -- 1674123
1597 ,p_audit_log => p_audit_log);
1598 --
1599 loop
1600 --
1601 open c_range_thread;
1602 fetch c_range_thread into l_range_id,
1603 l_start_person_action_id,
1604 l_end_person_action_id;
1605 --
1606 exit when c_range_thread%notfound;
1607 --
1608 close c_range_thread;
1609 --
1610 -- Update the range status code to processed 'P'
1611 --
1612 update ben_batch_ranges ran
1613 set ran.range_status_cd = 'P'
1614 where ran.range_id = l_range_id;
1615 --
1616 hr_utility.set_location('Updated range ' || to_char(l_range_id) ||
1617 ' status code to P', 10);
1618 --
1619 commit;
1620 --
1621 -- Remove all records from cache
1622 --
1623 g_cache_per_proc.delete;
1624 --
1625 open c_person_thread;
1626 --
1627 l_record_number := 0;
1628 --
1629 hr_utility.set_location('Load person actions into the cache', 10);
1630 --
1631 loop
1632 --
1633 fetch c_person_thread into
1634 g_cache_per_proc(l_record_number+1).person_id
1635 ,g_cache_per_proc(l_record_number+1).person_action_id
1636 ,g_cache_per_proc(l_record_number+1).object_version_number
1637 ,g_cache_per_proc(l_record_number+1).ler_id;
1638 --
1639 exit when c_person_thread%notfound;
1640 --
1641 l_record_number := l_record_number + 1;
1642 --
1643 l_actn := 'Updating person_ations.';
1644 --
1645 update ben_person_actions
1646 set action_status_cd = 'T'
1647 where person_action_id = l_person_action_id;
1648 --
1649 end loop;
1650 --
1651 close c_person_thread;
1652 --
1653 commit;
1654 --
1655 if l_record_number > 0 then
1656 --
1657 for l_cnt in 1..l_record_number
1658 loop
1659 --
1660 hr_utility.set_location('Closing Enrollment for ' ||
1661 to_char(g_cache_per_proc(l_cnt).person_id), 10);
1662 --
1663 begin
1664 --
1665 ben_close_enrollment.Close_Single_Enrollment
1666 (p_per_in_ler_id => g_cache_per_proc(l_cnt).ler_id
1667 ,p_effective_date => l_effective_date
1668 ,p_business_group_id => p_business_group_id
1669 ,p_validate => l_validate
1670 ,p_batch_flag => TRUE
1671 ,p_person_action_id => g_cache_per_proc(l_cnt).
1672 person_action_id
1673 ,p_object_version_number => g_cache_per_proc(l_cnt).
1674 object_version_number
1675 ,p_audit_log => p_audit_log
1676 ,p_close_uneai_flag => l_parm.close_uneai_flag
1677 ,p_close_cd => l_parm.mode_cd -- 1674123
1678 ,p_uneai_effective_date => l_parm.uneai_effective_date);
1679 --
1680 exception
1681 --
1682 when others then
1683 --
1684 -- start bug 3079317
1685 ben_env_object.setenv(p_lf_evt_ocrd_dt => l_effective_date);
1686 ben_env_object.get(p_rec => l_env);
1687 ben_person_object.get_object(p_person_id => g_cache_per_proc(l_cnt).person_id,
1688 p_rec => l_per_rec);
1689 --
1690 l_encoded_message := fnd_message.get_encoded;
1691 fnd_message.parse_encoded(encoded_message => l_encoded_message,
1692 app_short_name => l_app_short_name,
1693 message_name => l_message_name);
1694
1695 fnd_message.set_encoded(encoded_message => l_encoded_message);
1696 --
1697 g_rec.text := fnd_message.get ;
1698 --
1699 g_rec.error_message_code := nvl(l_message_name , nvl(g_rec.error_message_code,sqlcode));
1700 g_rec.text := nvl(g_rec.text , nvl(g_rec.text,substr(sqlerrm,1,400)) );
1701 g_rec.rep_typ_cd := 'ERROR';
1702 g_rec.person_id := g_cache_per_proc(l_cnt).person_id;
1703 g_rec.pgm_id := l_env.pgm_id;
1704 g_rec.pl_id := l_env.pl_id;
1705 g_rec.oipl_id := l_env.oipl_id;
1706 g_rec.national_identifier := l_per_rec.national_identifier;
1707 benutils.write(p_text => g_rec.text);
1708 benutils.write(p_rec => g_rec);
1709 --
1710 update ben_person_actions
1711 set action_status_cd = 'E'
1712 where person_action_id = g_cache_per_proc(l_cnt).person_action_id;
1713 --
1714 -- end bug 3079317
1715 --
1716 if g_persons_errored > g_max_person_err then
1717 fnd_message.raise_error;
1718 end if;
1719 --
1720 end;
1721 --
1722 end loop;
1723 --
1724 else
1725 --
1726 hr_utility.set_location('No records found. Erroring out.', 10);
1727 --
1728 l_actn := 'Reporting error since there is no record found';
1729 --
1730 fnd_message.set_name('BEN','BEN_91906_PER_NOT_FND_IN_RNG');
1731 fnd_message.set_token('PROC',l_proc);
1732 fnd_message.set_token('BENEFIT_ACTION_ID',to_char(p_benefit_action_id));
1733 fnd_message.set_token('BG_ID',to_char(p_business_group_id));
1734 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
1735 fnd_message.raise_error;
1736 --
1737 end if;
1738 --
1739 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1740 --
1741 commit;
1742 --
1743 end loop;
1744 --
1745 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1746 --
1747 commit;
1748 --
1749 l_actn := 'Calling log_beneadeb_statistics...';
1750 --
1751 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_procd
1752 ,p_num_pers_errored => g_persons_errored);
1753 --
1754 hr_utility.set_location ('Leaving '||l_proc,70);
1755 --
1756 exception
1757 --
1758 when others then
1759 --
1760 rollback;
1761 benutils.write(p_text => sqlerrm);
1762 --
1763 hr_utility.set_location('BENCLENR Super Error ' || l_proc, 10);
1764 --
1765 ben_batch_utils.rpt_error(p_proc => l_proc
1766 ,p_last_actn => l_actn
1767 ,p_rpt_flag => TRUE);
1768 --
1769 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_procd
1770 ,p_num_pers_errored => g_persons_errored);
1771 --
1772 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1773 --
1774 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1775 fnd_message.set_token('PROCEDURE', l_proc);
1776 fnd_message.set_token('STEP',l_actn );
1777 fnd_message.raise_error;
1778 --
1779 end do_multithread;
1780 --
1781 -- -----------------------------------------------------------------------------
1782 -- |----------------------------< restart >------------------------------------|
1783 -- -----------------------------------------------------------------------------
1784 --
1785 procedure restart
1786 (errbuf out nocopy varchar2
1787 ,retcode out nocopy number
1788 ,p_benefit_action_id in number)
1789 is
1790 --
1791 -- Cursor Declaration
1792 --
1793 cursor c_parameters
1794 is
1795 select -- to_char(process_date, 'YYYY/MM/DD HH24:MI:SS') process_date
1796 fnd_date.date_to_canonical(process_date) process_date
1797 ,business_group_id
1798 ,pgm_id
1799 ,pl_id
1800 ,location_id
1801 ,ler_id
1802 -- PB : 5422 :
1803 ,lf_evt_ocrd_dt
1804 -- ,popl_enrt_typ_cycl_id
1805 ,person_id
1806 ,person_selection_rl
1807 ,validate_flag
1808 ,debug_messages_flag
1809 ,audit_log_flag
1810 ,close_uneai_flag
1811 ,uneai_effective_date
1812 ,mode_cd -- 1674123
1813 From ben_benefit_actions ben
1814 Where ben.benefit_action_id = p_benefit_action_id;
1815 --
1816 -- Local Variable declaration.
1817 --
1818 l_parameters c_parameters%rowtype;
1819 l_errbuf varchar2(80);
1820 l_retcode number;
1821 l_actn varchar2(80);
1822 --
1823 l_proc varchar2(80) := g_package||'.restart';
1824 --
1825 begin
1826 --
1827 hr_utility.set_location ('Entering '||l_proc,10);
1828 --
1829 -- get the parameters for a previous run and do a restart
1830 --
1831 open c_parameters;
1832 fetch c_parameters into l_parameters;
1833 --
1834 if c_parameters%notfound then
1835 --
1836 close c_parameters;
1837 fnd_message.set_name('BEN','BEN_91710_RESTRT_PARMS_NOT_FND');
1838 fnd_message.set_token('PROC',l_proc);
1839 fnd_message.raise_error;
1840 --
1841 end if;
1842 --
1843 close c_parameters;
1844 --
1845 -- Call the process procedure with parameters for restart
1846 --
1847 Process
1848 (errbuf => l_errbuf
1849 ,retcode => l_retcode
1850 ,p_benefit_action_id => p_benefit_action_id
1851 ,p_effective_date => l_parameters.process_date
1852 ,p_validate => l_parameters.validate_flag
1853 ,p_business_group_id => l_parameters.business_group_id
1854 ,p_pgm_id => l_parameters.pgm_id
1855 ,p_pl_nip_id => l_parameters.pl_id
1856 ,p_location_id => l_parameters.location_id
1857 ,p_ler_id => l_parameters.ler_id
1858 -- PB : 5422 :
1859 -- ,p_popl_enrt_typ_cycl_id => l_parameters.popl_enrt_typ_cycl_id
1860 ,p_lf_evt_ocrd_dt => fnd_date.date_to_canonical(l_parameters.lf_evt_ocrd_dt)
1861 ,p_person_id => l_parameters.person_id
1862 ,p_debug_messages => l_parameters.debug_messages_flag
1863 ,p_audit_log => l_parameters.audit_log_flag
1864 ,p_close_uneai_flag => l_parameters.close_uneai_flag
1865 ,p_close_cd => l_parameters.mode_cd -- 1674123
1866 ,p_uneai_effective_date => l_parameters.uneai_effective_date);
1867 --
1868 hr_utility.set_location ('Leaving '||l_proc,70);
1869 --
1870 end restart;
1871 --
1872 -- -----------------------------------------------------------------------------
1873 -- |-----------------------------< process >-----------------------------------|
1874 -- -----------------------------------------------------------------------------
1875 --
1876 -- -----------------------------------------------------------------------------
1877 -- This is the main batch procedure to be called from the concurrent manager
1878 -- or interactively to close any enrollment has not been closed and close
1879 -- per_in_ler as well.
1880 -- -----------------------------------------------------------------------------
1881 --
1882 procedure process
1883 (errbuf out nocopy varchar2
1884 ,retcode out nocopy number
1885 ,p_benefit_action_id in number
1886 ,p_effective_date in varchar2
1887 ,p_business_group_id in number
1888 ,p_pgm_id in number default NULL
1889 ,p_pl_nip_id in number default NULL
1890 ,p_location_id in number default NULL
1891 ,p_ler_id in number default NULL
1892 -- 5422 : PB
1893 ,p_lf_evt_ocrd_dt in varchar2 default NULL
1894 -- ,p_popl_enrt_typ_cycl_id in number default NULL
1895 ,p_Person_id in number default NULL
1896 ,p_Person_selection_rl in number default NULL
1897 ,p_validate in varchar2 default 'N'
1898 ,p_debug_messages in varchar2 default 'N'
1899 ,p_audit_log in varchar2 default 'N'
1900 ,p_uneai_effective_date in varchar2 default null
1901 ,p_close_uneai_flag in varchar2 default 'Y'
1902 ,p_close_cd in varchar2 default 'NORCLOSE' -- 1674123
1903 )
1904 is
1905 --
1906 -- Local variable declaration.
1907 --
1908 l_uneai_effective_date date;
1909 l_effective_date date;
1910 l_person_ok varchar2(30) := 'Y';
1911 l_person_actn_cnt number(15) := 0;
1912 l_start_person_actn_id number(15);
1913 l_end_person_actn_id number(15);
1914 l_object_version_number number(15);
1915 l_datetrack_mode varchar2(80);
1916 l_actn varchar2(80);
1917 l_request_id number(15);
1918 l_benefit_action_id number(15);
1919 l_person_id number(15);
1920 l_person_action_id number(15);
1921 l_ler_id number(15);
1922 l_range_id number(15);
1923 l_chunk_size number := 20;
1924 l_chunk_num number := 1;
1925 l_threads number(5) := 1;
1926 l_step number := 0;
1927 l_num_ranges number := 0;
1928 l_lf_evt_ocrd_dt date;
1929 --
1930 -- Cursor Declaration.
1931 --
1932 cursor c_pil
1933 is
1934 select distinct pil.person_id
1935 ,pil.per_in_ler_id
1936 from ben_per_in_ler pil,
1937 per_all_people_f per,
1938 ben_ler_f ler
1939 where pil.business_group_id = p_business_group_id
1940 and pil.per_in_ler_stat_cd = 'STRTD'
1941 and pil.person_id = per.person_id
1942 and pil.ler_id = ler.ler_id
1943 --GSP changes
1944 --and ler.typ_cd not in ('GSP','COMP','SCHEDDU','ABS', 'IREC') /* Bug 3981328 : Added Code IREC */
1945 and l_effective_date between ler.effective_start_date and
1946 ler.effective_end_date
1947 and l_effective_date between per.effective_start_date and
1948 per.effective_end_date
1949 and ((p_ler_id is null and ler.typ_cd not in ('GSP','COMP','SCHEDDU','ABS', 'IREC')) or
1950 (pil.ler_id = p_ler_id and ler.typ_cd not in ('GSP','SCHEDDU','ABS', 'IREC')))
1951 --Bug 4193968: Added the following check to prevent closing of the enrollments if the
1952 -- Effective date is less than the life event occured date, in case the life event type is not 'Open'
1953 and ((ler.typ_cd in('SCHEDDO')) or (l_effective_date >= pil.lf_evt_ocrd_dt ))
1954 -- End Bug 4193968
1955 and (p_person_id is null or pil.person_id = p_person_id)
1956 and (p_lf_evt_ocrd_dt is null
1957 or exists (select null
1958 from ben_pil_elctbl_chc_popl pel,
1959 ben_enrt_perd enp
1960 where pel.per_in_ler_id = pil.per_in_ler_id
1961 and pel.enrt_perd_id = enp.enrt_perd_id
1962 and enp.asnd_lf_evt_dt = l_lf_evt_ocrd_dt
1963 and pel.pil_elctbl_popl_stat_cd = 'STRTD' ))
1964 /* PB : 5422 :
1965 and (p_popl_enrt_typ_cycl_id is null
1966 or exists (select null
1967 from ben_pil_elctbl_chc_popl pel
1968 where pel.per_in_ler_id = pil.per_in_ler_id
1969 and pel.enrt_perd_id = p_popl_enrt_typ_cycl_id
1970 and pel.pil_elctbl_popl_stat_cd = 'STRTD' ))
1971 */
1972 and (p_location_id is null
1973 or exists(select null
1974 from per_all_assignments_f paf
1975 where paf.person_id = pil.person_id
1976 and paf.assignment_type <> 'C'
1977 and paf.location_id = p_location_id
1978 and paf.primary_flag = 'Y'
1979 and l_effective_date between
1980 paf.effective_start_date and paf.effective_end_date))
1981 and (p_pgm_id is null
1982 or exists(select null
1983 from ben_pil_elctbl_chc_popl pel
1984 where pel.pgm_id = p_pgm_id
1985 and pel.per_in_ler_id = pil.per_in_ler_id
1986 and pel.pil_elctbl_popl_stat_cd = 'STRTD' ))
1987 and (p_pl_nip_id is null
1988 or exists(select null
1989 from ben_pil_elctbl_chc_popl pel
1990 where pl_id = p_pl_nip_id
1991 and pel.per_in_ler_id = pil.per_in_ler_id
1992 and pel.pgm_id is null
1993 and pel.pil_elctbl_popl_stat_cd = 'STRTD' ));
1994 --
1995 -- Type declarations
1996 --
1997 Type Pil_a is table of C_pil%rowtype index by binary_integer;
1998 --
1999 l_pil_rec pil_a;
2000 l_pil_cnt binary_integer := 0;
2001 --
2002 l_proc varchar2(80) := g_package||'.process';
2003 l_commit number;
2004 --
2005 type g_number_table is varray(1000000) of number;
2006 --
2007 l_perid_va g_number_table := g_number_table();
2008 l_pilid_va g_number_table := g_number_table();
2009
2010 begin
2011 --
2012 hr_utility.set_location ('Entering ' || l_proc, 10);
2013 -- Bug 5857493
2014 if p_audit_log ='Y' then
2015 ben_batch_utils.g_audit_flag := true;
2016 else
2017 ben_batch_utils.g_audit_flag := false;
2018 end if;
2019 --
2020 -- Convert varchar2 dates to real dates
2021 -- 1) First remove time component
2022 -- 2) Next convert format
2023 --
2024 /* BUG 4046914
2025 l_effective_date := to_date(p_effective_date
2026 ,'YYYY/MM/DD HH24:MI:SS');
2027 --
2028 l_effective_date := to_date(to_char(trunc(l_effective_date)
2029 ,'DD/MM/RRRR'),'DD/MM/RRRR');
2030 l_lf_evt_ocrd_dt := to_date(p_lf_evt_ocrd_dt
2031 ,'YYYY/MM/DD HH24:MI:SS');
2032 --
2033 l_lf_evt_ocrd_dt := to_date(to_char(trunc(l_lf_evt_ocrd_dt)
2034 ,'DD/MM/RRRR'),'DD/MM/RRRR');
2035 */
2036 l_effective_date := trunc(fnd_date.canonical_to_date(p_effective_date));
2037 l_lf_evt_ocrd_dt := trunc(fnd_date.canonical_to_date(p_lf_evt_ocrd_dt));
2038 --
2039 -- Put row in fnd_sessions
2040 --
2041 dt_fndate.change_ses_date
2042 (p_ses_date => l_effective_date,
2043 p_commit => l_commit);
2044 --
2045 -- Now same for uneai_effecive_date
2046 --
2047 if p_uneai_effective_date is null then
2048 l_uneai_effective_date:=l_effective_date;
2049 else
2050 /*
2051 l_uneai_effective_date := to_date(p_uneai_effective_date
2052 ,'YYYY/MM/DD HH24:MI:SS');
2053 --
2054 l_uneai_effective_date := to_date(to_char(trunc(l_uneai_effective_date)
2055 ,'DD/MM/RRRR'),'DD/MM/RRRR');
2056 */
2057 l_uneai_effective_date := trunc(fnd_date.canonical_to_date(p_uneai_effective_date));
2058 end if;
2059 --
2060 -- Check business rules and mandatory parameters
2061 --
2062 hr_api.mandatory_arg_error
2063 (p_api_name => l_proc
2064 ,p_argument => 'p_effective_date'
2065 ,p_argument_value => l_effective_date);
2066 --
2067 -- p_pgm_id and Pl_nip are mutually exclusive
2068 --
2069 if p_pgm_id is not null and p_pl_nip_id is not null then
2070 --
2071 fnd_message.set_name('BEN', 'BEN_91907_PGM_PL_MUTUAL_EXCL');
2072 fnd_message.set_token('PROC',l_proc);
2073 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
2074 fnd_message.set_token('PL_NIP_ID',to_char(p_pl_nip_id));
2075 fnd_message.set_token('PERSON_ID',to_char(p_person_id));
2076 fnd_message.set_token('BG_ID',to_char(p_business_group_id));
2077 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
2078 fnd_message.raise_error;
2079 --
2080 end if;
2081 --
2082 -- Initialize the batch process.
2083 --
2084 ben_batch_utils.ini(p_actn_cd => 'PROC_INFO');
2085 --
2086 -- Get the parameters defined for the batch process.
2087 --
2088 benutils.get_parameter
2089 (p_business_group_id => p_business_group_id
2090 ,p_batch_exe_cd => 'BENCLENR'
2091 ,p_threads => l_threads
2092 ,p_chunk_size => l_chunk_size
2093 ,p_max_errors => g_max_person_err);
2094 --
2095 -- If p_benefit_action_id is null then this is a new batch process. Create the
2096 -- batch ranges and person actions. Else restart using the benefit_action_id.
2097 --
2098 if p_benefit_action_id is null then
2099 --
2100 -- Create a new benefit_action row.
2101 --
2102 ben_benefit_actions_api.create_benefit_actions
2103 (p_validate => FALSE
2104 ,p_benefit_action_id => l_benefit_action_id
2105 ,p_process_date => l_effective_date
2106 ,p_mode_cd => p_close_cd -- 'S' -- 1674123
2107 -- 1674123 : This param is used to pass the close_cd.
2108 ,p_derivable_factors_flag => 'N'
2109 ,p_validate_flag => p_validate
2110 ,p_person_id => p_person_id
2111 ,p_person_type_id => NULL
2112 ,p_pgm_id => p_pgm_id
2113 ,p_business_group_id => p_business_group_id
2114 ,p_pl_id => p_pl_nip_id
2115 -- 5422 : PB :
2116 -- ,p_popl_enrt_typ_cycl_id => p_popl_enrt_typ_cycl_id
2117 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2118 ,p_no_programs_flag => 'N'
2119 ,p_no_plans_flag => 'N'
2120 ,p_comp_selection_rl => NULL
2121 ,p_person_selection_rl => p_person_selection_rl
2122 ,p_ler_id => p_ler_id
2123 ,p_organization_id => NULL
2124 ,p_benfts_grp_id => NULL
2125 ,p_location_id => p_location_id
2126 ,p_pstl_zip_rng_id => NULL
2127 ,p_rptg_grp_id => NULL
2128 ,p_pl_typ_id => NULL
2129 ,p_opt_id => NULL
2130 ,p_eligy_prfl_id => NULL
2131 ,p_vrbl_rt_prfl_id => NULL
2132 ,p_legal_entity_id => NULL
2133 ,p_payroll_id => NULL
2134 ,p_audit_log_flag => p_audit_log
2135 ,p_debug_messages_flag => 'N'
2136 ,p_object_version_number => l_object_version_number
2137 ,p_effective_date => l_effective_date
2138 ,p_request_id => fnd_global.conc_request_id
2139 ,p_program_application_id => fnd_global.prog_appl_id
2140 ,p_program_id => fnd_global.conc_program_id
2141 ,p_program_update_date => sysdate
2142 ,p_uneai_effective_date => l_uneai_effective_date
2143 ,p_close_uneai_flag => p_close_uneai_flag
2144 --
2145 -- Bug No 4034201
2146 --
2147 ,p_ptnl_ler_for_per_stat_cd => p_close_cd
2148 );
2149 --
2150 benutils.g_benefit_action_id := l_benefit_action_id;
2151 --
2152 benutils.g_thread_id := 99;
2153 --
2154 l_actn := 'Removing batch ranges ';
2155 --
2156 delete from ben_batch_ranges
2157 where benefit_action_id = l_benefit_action_id;
2158 --
2159 -- Loop through rows in ben_per_in_ler_f based on the parameters passed and
2160 -- create person actions for the selected people.
2161 --
2162 open c_pil;
2163 fetch c_pil bulk collect into l_perid_va,l_pilid_va;
2164 close c_pil;
2165 --
2166 for i in 1..l_perid_va.COUNT
2167 loop
2168 --
2169 -- set variables for this iteration
2170 --
2171 l_person_ok := 'Y';
2172 --
2173 -- Check the person selection rule.
2174 --
2175 if p_person_selection_rl is not null then
2176 --
2177 l_person_ok := ben_batch_utils.person_selection_rule
2178 (p_person_id => l_perid_va(i)
2179 ,p_business_group_id => p_business_group_id
2180 ,p_person_selection_rule_id => p_person_selection_rl
2181 ,p_effective_date => l_effective_date);
2182 --
2183 end if;
2184 --
2185 if l_person_ok = 'Y' then
2186 --
2187 -- Either no person sel rule or person selection rule passed. Create a
2188 -- person action row.
2189 --
2190 ben_person_actions_api.create_person_actions
2191 (p_validate => FALSE
2192 ,p_person_action_id => l_person_action_id
2193 ,p_person_id => l_perid_va(i)
2194 ,p_ler_id => l_pilid_va(i)
2195 ,p_benefit_action_id => l_benefit_action_id
2196 ,p_action_status_cd => 'U'
2197 ,p_chunk_number => l_chunk_num
2198 ,p_object_version_number => l_object_version_number
2199 ,p_effective_date => l_effective_date);
2200 --
2201 -- increment the person action count
2202 --
2203 l_person_actn_cnt := l_person_actn_cnt + 1;
2204 --
2205 -- Set the ending person action id to the last person action id that got
2206 -- created
2207 --
2208 l_end_person_actn_id := l_person_action_id;
2209 --
2210 -- We have to create batch ranges based on the number of person actions
2211 -- created and the chunk size defined for the batch process.
2212 --
2213 if mod(l_person_actn_cnt, l_chunk_size) = 1 or l_chunk_size = 1 then
2214 --
2215 -- This is the first person action id in a new range.
2216 --
2217 l_start_person_actn_id := l_person_action_id;
2218 --
2219 end if;
2220 --
2221 if mod(l_person_actn_cnt, l_chunk_size) = 0 or l_chunk_size = 1 then
2222 --
2223 -- The number of person actions that got created equals the chunk
2224 -- size. Create a batch range for the person actions.
2225 --
2226 ben_batch_ranges_api.create_batch_ranges
2227 (p_validate => FALSE
2228 ,p_effective_date => l_effective_date
2229 ,p_benefit_action_id => l_benefit_action_id
2230 ,p_range_id => l_range_id
2231 ,p_range_status_cd => 'U'
2232 ,p_starting_person_action_id => l_start_person_actn_id
2233 ,p_ending_person_action_id => l_end_person_actn_id
2234 ,p_object_version_number => l_object_version_number);
2235 --
2236 l_num_ranges := l_num_ranges + 1;
2237 l_chunk_num := l_chunk_num + 1;
2238 --
2239 end if;
2240 --
2241 end if;
2242 --
2243 end loop;
2244 --
2245 -- There may be a few person actions left over from the loop above that may
2246 -- not have got inserted into a batch range because the number was less than
2247 -- the chunk size. Create a range for the remaining person actions. This
2248 -- also applies when only one person gets selected.
2249 --
2250 if l_person_actn_cnt > 0 and
2251 mod(l_person_actn_cnt, l_chunk_size) <> 0 then
2252 --
2253 ben_batch_ranges_api.create_batch_ranges
2254 (p_validate => FALSE
2255 ,p_effective_date => l_effective_date
2256 ,p_benefit_action_id => l_benefit_action_id
2257 ,p_range_id => l_range_id
2258 ,p_range_status_cd => 'U'
2259 ,p_starting_person_action_id => l_start_person_actn_id
2260 ,p_ending_person_action_id => l_end_person_actn_id
2261 ,p_object_version_number => l_object_version_number);
2262 --
2263 l_num_ranges := l_num_ranges + 1;
2264 --
2265 end if;
2266 --
2267 Else
2268 --
2269 -- Benefit action id is not null i.e. the batch process is being restarted
2270 -- for a certain benefit action id. Create batch ranges and person actions
2271 -- for restarting.
2272 --
2273 l_benefit_action_id := p_benefit_action_id;
2274 --
2275 hr_utility.set_location('Restarting for benefit action id : ' ||
2276 to_char(l_benefit_action_id), 10);
2277 --
2278 ben_batch_utils.create_restart_person_actions
2279 (p_benefit_action_id => p_benefit_action_id
2280 ,p_effective_date => l_effective_date
2281 ,p_chunk_size => l_chunk_size
2282 ,p_threads => l_threads
2283 ,p_num_ranges => l_num_ranges
2284 ,p_num_persons => l_person_actn_cnt);
2285 --
2286 end if;
2287 --
2288 commit;
2289 --
2290 -- Submit requests to the concurrent manager based on the number of ranges
2291 -- that got created.
2292 --
2293 if l_num_ranges > 1 then
2294 --
2295 hr_utility.set_location('More than one range got created.', 10);
2296 --
2297 --
2298 -- Set the number of threads to the lesser of the defined number of threads
2299 -- and the number of ranges created above. There's no point in submitting
2300 -- 5 threads for only two ranges.
2301 --
2302 l_threads := least(l_threads, l_num_ranges);
2303 --
2304 for l_count in 1..(l_threads - 1)
2305 loop
2306 --
2307 -- We are subtracting one from the number of threads because the main
2308 -- process will act as the last thread and will be able to keep track of
2309 -- the child requests that get submitted.
2310 --
2311 hr_utility.set_location('Submitting request ' || l_count, 10);
2312 --
2313 l_request_id := fnd_request.submit_request
2314 (application => 'BEN'
2315 ,program => 'BENCLENRS'
2316 ,description => NULL
2317 ,sub_request => FALSE
2318 ,argument1 => p_validate
2319 ,argument2 => l_benefit_action_id
2320 ,argument3 => l_count
2321 ,argument4 => p_effective_date
2322 ,argument5 => p_business_group_id
2323 ,argument6 => p_audit_log );
2324 --
2325 -- Store the request id of the concurrent request
2326 --
2327 ben_batch_utils.g_num_processes := ben_batch_utils.g_num_processes + 1;
2328 ben_batch_utils.g_processes_tbl(ben_batch_utils.g_num_processes)
2329 := l_request_id;
2330 commit;
2331 --
2332 end loop;
2333 --
2334 elsif l_num_ranges = 0 then
2335 --
2336 -- No ranges got created. i.e. no people got selected. Error out.
2337 --
2338 ben_batch_utils.print_parameters
2339 (p_thread_id => 99
2340 ,p_benefit_action_id => l_benefit_action_id
2341 ,p_validate => p_validate
2342 ,p_business_group_id => p_business_group_id
2343 ,p_effective_date => l_effective_date
2344 ,p_person_id => p_person_id
2345 ,p_person_selection_rule_id => p_person_selection_rl
2346 ,p_location_id => p_location_id
2347 ,p_ler_id => p_ler_id
2348 ,p_mode => p_close_cd -- 1674123
2349 ,p_audit_log => p_audit_log);
2350 --
2351 ben_batch_utils.write(p_text =>
2352 'No person got selected with above selection criteria.');
2353 --
2354 fnd_message.set_name('BEN','BEN_91769_NOONE_TO_PROCESS');
2355 fnd_message.set_token('PROC',l_proc);
2356 fnd_message.raise_error;
2357 --
2358 end if;
2359 --
2360 -- Carry on with the master. This will ensure that the master finishes last.
2361 --
2362 hr_utility.set_location('Submitting the master process', 10);
2363 --
2364 do_multithread
2365 (errbuf => errbuf
2366 ,retcode => retcode
2367 ,p_validate => p_validate
2368 ,p_benefit_action_id => l_benefit_action_id
2369 ,p_thread_id => l_threads
2370 ,p_effective_date => p_effective_date
2371 ,p_business_group_id => p_business_group_id
2372 ,p_audit_log => p_audit_log);
2373 --
2374 -- Check if all the slave processes are finished.
2375 --
2376 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
2377 --
2378 -- End the process.
2379 --
2380 ben_batch_utils.end_process
2381 (p_benefit_action_id => l_benefit_action_id
2382 ,p_person_selected => l_person_actn_cnt
2383 ,p_business_group_id => p_business_group_id);
2384 --
2385 -- Submit reports.
2386 --
2387 submit_all_reports(p_audit_log => p_audit_log);
2388 --
2389 hr_utility.set_location ('Leaving ' || l_proc, 10);
2390 --
2391 exception
2392 --
2393 when others then
2394 --
2395 ben_batch_utils.rpt_error(p_proc => l_proc
2396 ,p_last_actn => l_actn
2397 ,p_rpt_flag => TRUE);
2398 --
2399 benutils.write(p_text => fnd_message.get);
2400 benutils.write(p_text => sqlerrm);
2401 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
2402 --
2403 if l_num_ranges > 0 then
2404 --
2405 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
2406 --
2407 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
2408 ,p_person_selected => l_person_actn_cnt
2409 ,p_business_group_id => p_business_group_id);
2410 --
2411 submit_all_reports(p_audit_log => p_audit_log);
2412 --
2413 end if;
2414 --
2415 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2416 fnd_message.set_token('PROCEDURE', l_proc);
2417 fnd_message.set_token('STEP', l_actn );
2418 fnd_message.raise_error;
2419 --
2420 end process;
2421 --
2422 -- Bug : 1700853 : reopen the closed life event.
2423 --
2424 -- -----------------------------------------------------------------------------
2425 -- |----------------------< reopen_single_life_event >-------------------------|
2426 -- -----------------------------------------------------------------------------
2427 --
2428 -- This procedure to be called to open the single life event.
2429 --
2430 procedure reopen_single_life_event
2431 (p_per_in_ler_id in number
2432 ,p_person_id in number
2433 ,p_lf_evt_ocrd_dt in date
2434 ,p_effective_date in date
2435 ,p_business_group_id in number
2436 ,p_object_version_number in number
2437 ,p_validate in boolean default FALSE
2438 ,p_source in varchar2 default 'reopen' --Bug 5929635
2439 )
2440 is
2441 --
2442 cursor c_get_future_per_in_ler is
2443 select null
2444 from ben_per_in_ler pil,
2445 ben_ler_f ler
2446 where pil.lf_evt_ocrd_dt > p_lf_evt_ocrd_dt
2447 and pil.person_id = p_person_id
2448 and ler.ler_id = pil.ler_id
2449 and ler.typ_cd not in ( 'COMP','SCHEDDU', 'ABS', 'GSP', 'IREC') /* Bug 3981328 : Added Codes GSP, IREC, ABS */
2450 and pil.lf_evt_ocrd_dt between
2451 ler.effective_start_date and
2452 ler.effective_end_date
2453 and pil.business_group_id = p_business_group_id
2454 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
2455 --
2456 cursor c_get_pil_elctbl_chc_popl is
2457 select pel.*
2458 from ben_pil_elctbl_chc_popl pel
2459 where pel.per_in_ler_id = p_per_in_ler_id
2460 and pel.business_group_id = p_business_group_id;
2461 --
2462 cursor c_get_cbr_per_in_ler is
2463 select crp.*
2464 from ben_cbr_per_in_ler crp
2465 where crp.per_in_ler_id = p_per_in_ler_id
2466 and crp.business_group_id = p_business_group_id
2467 and crp.init_evt_flag = 'N';
2468 --
2469 cursor c_get_cbr_quald_bnf(p_cbr_per_in_ler_id number) is
2470 select cqb.*
2471 from ben_cbr_quald_bnf cqb
2472 ,ben_cbr_per_in_ler crp
2473 where crp.cbr_per_in_ler_id = p_cbr_per_in_ler_id
2474 and crp.cbr_quald_bnf_id = cqb.cbr_quald_bnf_id
2475 and cqb.business_group_id = p_business_group_id
2476 and cqb.business_group_id = crp.business_group_id;
2477 --
2478 -- Temporary until we add the reopen date.
2479 --
2480 cursor c_get_strtd_dt is
2481 select pil.*
2482 from ben_per_in_ler pil
2483 where pil.per_in_ler_id = p_per_in_ler_id
2484 and pil.business_group_id = p_business_group_id;
2485 --
2486 -- Bug(2300866):Check cursor for Reopening Life Events
2487 -- Before reopening the life event need to have a check to see if
2488 -- the electable flag is 'Y' or whether a row there is a row in
2489 -- ben_elig_per_elctbl_chc table. If the row is not there raising a error
2490 -- Tilak : there can be first row is not eligible and secodn row is elctbl
2491 --- so it is neccessary to validate the elctbl flag 2
2492 cursor c_chk_reopen_lf_event is
2493 select ELIG_PER_ELCTBL_CHC_ID,
2494 ELCTBL_FLAG
2495 from ben_elig_per_elctbl_chc
2496 where per_in_ler_id =p_per_in_ler_id
2497 and elctbl_flag = 'Y'
2498 and business_group_id = p_business_group_id;
2499 --
2500 l_chk_reopen_lf_event c_chk_reopen_lf_event%rowtype;
2501 --
2502 cursor c_enb(cv_per_in_ler_id number ) is
2503 select enb.enrt_bnft_id,
2504 enb.object_version_number,
2505 enb.business_group_id,
2506 epe.prtt_enrt_rslt_id
2507 from ben_elig_per_elctbl_chc epe,
2508 ben_enrt_bnft enb
2509 where epe.per_in_ler_id = cv_per_in_ler_id
2510 and epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
2511 and epe.prtt_enrt_rslt_id IS NOT NULL
2512 and enb.prtt_enrt_rslt_id IS NULL
2513 and exists ( select 'x' from ben_prtt_enrt_rslt_f pen
2514 where pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
2515 and pen.bnft_ordr_num = enb.ordr_num
2516 and pen.prtt_enrt_rslt_stat_cd is NULL
2517 and pen.per_in_ler_id = epe.per_in_ler_id ) ;
2518 --
2519 --
2520 l_proc varchar2(80) := g_package||'.reopen_single_life_event';
2521 l_exists varchar2(1);
2522 l_object_version_number ben_per_in_ler.object_version_number%type;
2523 l_dummy_dt date;
2524 l_procd_dt date;
2525 l_strtd_dt date;
2526 l_voidd_dt date;
2527 l_pil_rec c_get_strtd_dt%rowtype;
2528 --
2529 begin
2530 --
2531 hr_utility.set_location ('Entering '||l_proc,10);
2532
2533 --
2534 --Bug(2300866):Should not allow to reopen the bug
2535 --the prtspnt does not have any electable choices.
2536 --
2537 hr_utility.set_location('p_per_in_ler_id' || p_per_in_ler_id , 99 );
2538
2539 open c_chk_reopen_lf_event;
2540 fetch c_chk_reopen_lf_event into l_chk_reopen_lf_event;
2541 close c_chk_reopen_lf_event;
2542
2543 --
2544 if(l_chk_reopen_lf_event.ELIG_PER_ELCTBL_CHC_ID is NULL and p_source = 'reopen') then --Bug 5929635 : Do not raise error message if the call is in backout routine
2545 fnd_message.set_name('BEN', 'BEN_93044_REOPEN_LF_EVNT');
2546 fnd_message.raise_error;
2547 end if;
2548 --
2549
2550 --
2551 -- Step 1 : Check any future life events exists.
2552 -- if so return a warning message.
2553 --
2554 open c_get_future_per_in_ler;
2555 fetch c_get_future_per_in_ler into l_exists;
2556
2557 if c_get_future_per_in_ler%found then
2558 close c_get_future_per_in_ler;
2559 fnd_message.set_name('BEN', 'BEN_92711_LIFE_EVENT_EXISTS');
2560 fnd_message.raise_error;
2561 else
2562 close c_get_future_per_in_ler;
2563 end if;
2564 --
2565 -- Step 2 : Open all the pil electable choice popl rows.
2566 --
2567 for l_pel_rec in c_get_pil_elctbl_chc_popl loop
2568 ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl
2569 (p_validate => p_validate
2570 ,p_pil_elctbl_chc_popl_id => l_pel_rec.pil_elctbl_chc_popl_id
2571 ,p_pil_elctbl_popl_stat_cd => 'STRTD'
2572 ,p_business_group_id => p_business_group_id
2573 ,p_object_version_number => l_pel_rec.object_version_number
2574 ,p_effective_date => p_effective_date);
2575 --
2576 -- For COBRA participants, restore eligibility if it has been terminated.
2577 --
2578 if l_pel_rec.pgm_id is not null then
2579 --
2580 if (ben_cobra_requirements.chk_pgm_typ
2581 (p_pgm_id => l_pel_rec.pgm_id
2582 ,p_effective_date => p_effective_date
2583 ,p_business_group_id => p_business_group_id)
2584 ) then
2585 --
2586 -- Check if eligibility was terminated.
2587 --
2588 for l_crp_rec in c_get_cbr_per_in_ler loop
2589 --
2590 -- Restore eligibility.
2591 --
2592 for l_cqb_rec in c_get_cbr_quald_bnf(l_crp_rec.cbr_per_in_ler_id) loop
2593 --
2594 l_object_version_number := l_cqb_rec.object_version_number;
2595 --
2596 ben_cbr_quald_bnf_api.update_cbr_quald_bnf
2597 (p_validate => false
2598 ,p_cbr_quald_bnf_id => l_cqb_rec.cbr_quald_bnf_id
2599 ,p_cbr_elig_perd_end_dt => l_crp_rec.prvs_elig_perd_end_dt
2600 ,p_cbr_inelg_rsn_cd => null
2601 ,p_business_group_id => p_business_group_id
2602 ,p_object_version_number => l_object_version_number
2603 ,p_effective_date => p_effective_date
2604 );
2605 --
2606 -- Delete the cobra per_in_ler row.
2607 --
2608 ben_cbr_per_in_ler_api.delete_cbr_per_in_ler
2609 (p_validate => false
2610 ,p_cbr_per_in_ler_id => l_crp_rec.cbr_per_in_ler_id
2611 ,p_object_version_number => l_crp_rec.object_version_number
2612 ,p_effective_date => p_effective_date
2613 );
2614 end loop;
2615 end loop;
2616 end if;
2617 end if;
2618 --
2619 end loop;
2620 --
2621 -- Step 3 : Open the per in ler row.
2622 --
2623 open c_get_strtd_dt;
2624 fetch c_get_strtd_dt into l_pil_rec;
2625 close c_get_strtd_dt;
2626 --
2627 l_object_version_number := p_object_version_number;
2628 --
2629 ben_person_life_event_api.update_person_life_event
2630 (p_validate => p_validate
2631 ,p_per_in_ler_id => p_per_in_ler_id
2632 ,p_per_in_ler_stat_cd => 'STRTD'
2633 ,p_business_group_id => p_business_group_id
2634 ,p_object_version_number => l_object_version_number
2635 ,p_effective_date => l_pil_rec.strtd_dt
2636 ,p_procd_dt => l_procd_dt
2637 ,p_strtd_dt => l_strtd_dt
2638 ,p_voidd_dt => l_voidd_dt
2639 );
2640 --
2641 --Bug 3452376 fixes. We need to update the enrt_bnft with the pen id from
2642 --epe and valid result.
2643 --
2644 FOR l_enb IN c_enb(p_per_in_ler_id) LOOP
2645 --
2646 hr_utility.set_location ('manage_enrt_bnft '||l_enb.enrt_bnft_id,10);
2647 --
2648 ben_election_information.manage_enrt_bnft
2649 ( p_enrt_bnft_id => l_enb.enrt_bnft_id,
2650 p_effective_date => p_effective_date,
2651 p_object_version_number => l_enb.object_version_number,
2652 p_business_group_id => l_enb.business_group_id,
2653 p_prtt_enrt_rslt_id => l_enb.prtt_enrt_rslt_id,
2654 p_creation_date => null,
2655 p_created_by => null,
2656 p_per_in_ler_id => p_per_in_ler_id
2657 );
2658 --
2659 END LOOP;
2660 --
2661 hr_utility.set_location ('Leaving '||l_proc,10);
2662 --
2663 end reopen_single_life_event;
2664 --
2665 --Selfservice wrapper to call close enrollment for closing a per in ler
2666 --
2667 procedure close_single_enrollment_ss
2668 (p_per_in_ler_id in number
2669 ,p_effective_date in date
2670 ,p_business_group_id in number
2671 ,p_validate in boolean default FALSE
2672 ,p_batch_flag in boolean default FALSE
2673 ,p_person_action_id in Number default NULL
2674 ,p_object_version_number in Number default NULL
2675 ,p_audit_log in varchar2 default 'N'
2676 ,p_close_cd in varchar2 default 'FORCE'
2677 ,p_close_uneai_flag in varchar2 default NULL
2678 ,p_uneai_effective_date in date default NULL
2679 )
2680 is
2681 --
2682 begin
2683 --
2684 close_single_enrollment
2685 (p_per_in_ler_id =>p_per_in_ler_id
2686 ,p_effective_date =>p_effective_date
2687 ,p_business_group_id =>p_business_group_id
2688 ,p_validate =>p_validate
2689 ,p_close_cd =>p_close_cd
2690 ,p_close_uneai_flag =>p_close_uneai_flag
2691 ,p_uneai_effective_date =>p_uneai_effective_date
2692 );
2693 --
2694 commit;
2695 exception
2696 when others then
2697 fnd_message.set_name('BEN', 'BEN_92988_CANT_CLS_ENRT');
2698 --
2699 end close_single_enrollment_ss;
2700 --
2701 procedure close_enrt_n_run_benmngle_ss
2702 (p_person_id in number
2703 ,p_mode in varchar2 default 'L'
2704 ,p_per_in_ler_id in number
2705 ,p_effective_date in date
2706 ,p_run_date in date
2707 ,p_business_group_id in number
2708 ,p_validate in boolean default FALSE
2709 ,p_batch_flag in boolean default FALSE
2710 ,p_person_action_id in Number default NULL
2711 ,p_object_version_number in Number default NULL
2712 ,p_audit_log in varchar2 default 'N'
2713 ,p_close_cd in varchar2 default 'FORCE'
2714 ,p_close_uneai_flag in varchar2 default NULL
2715 ,p_uneai_effective_date in date default NULL
2716 )
2717 is
2718 l_return_status varchar2(10) ;
2719 --
2720 begin
2721 --
2722 close_single_enrollment_ss
2723 (p_per_in_ler_id =>p_per_in_ler_id
2724 ,p_effective_date =>p_effective_date
2725 ,p_business_group_id =>p_business_group_id
2726 ,p_validate =>p_validate
2727 ,p_close_cd =>p_close_cd
2728 ,p_close_uneai_flag =>p_close_uneai_flag
2729 ,p_uneai_effective_date =>p_uneai_effective_date
2730 );
2731 --
2732 ben_on_line_lf_evt.p_manage_life_events_w(
2733 p_person_id =>p_person_id
2734 ,p_effective_date =>p_run_date
2735 ,p_lf_evt_ocrd_dt =>null
2736 ,p_business_group_id =>p_business_group_id
2737 ,p_mode =>p_mode
2738 ,p_return_status =>l_return_status);
2739 --
2740 end close_enrt_n_run_benmngle_ss;
2741 --
2742 end ben_close_enrollment;