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