[Home] [Help]
PACKAGE BODY: APPS.BEN_BACK_OUT_CONC
Source
1 package body ben_back_out_conc as
2 /* $Header: benbocon.pkb 120.0 2005/05/28 03:44:14 appldev noship $ */
3 --
4 /* ============================================================================
5 * Name
6 * Back-out Life Events Concurrent Manager Processes
7 *
8 * Purpose
9 * This package simply houses the concurrent manager and multi-thread
10 * processes for Back-out Life Events.
11 *
12 * History
13 * Date Who Version What?
14 * --------- --------- ------- --------------------------------------
15 * 13-Jul-99 isen 115.0 Created
16 * 20-JUL-99 Gperry 115.1 genutils -> benutils package rename.
17 * 03-AUG-99 Gperry 115.2 CBO fix to_char with soft coded
18 * keyflex.
19 * 13-SEP-99 Asen 115.3 Added call to error reports.
20 * 04-OCT-99 Gperry 115.4 Made whole process actually work.
21 * 14-MAY-00 Anupam 115.5 Assigning values into g_rec.text for
22 * extration of data for Summary report.
23 * 18-MAY-00 Anupam 115.6 Assigning per_in_ler_id into g_rec.temporal_ler_id
24 * for extraction of data for Audit Report
25 * 27-DEC-01 Rpillay 115.7 Added check to allow only the latest
26 * life event to be backed out nocopy Bug# 2129181
27 * 27-DEC-01 Rpillay 115.8 added dbdrv,checkfile and commit
28 * 18-Jan-01 Rpillay 115.9 CWB changes Bug # 2183388
29 * 16-APR-02 vsethi 115.10 bug # 2275321 changed the person query to not
30 include the union clause
31 * 24-MAY-02 rpillay 115.12 Bug# 2376330 Added code for displaying
32 * Error Message code and National identifier
33 * in Person error reports
34 * 08-Jun-02 pabodla 115.13 Do not select the contingent worker
35 * assignment when assignment data is
36 * fetched.
37 * 09-Jun-02 pbodla 115.14 Bug 2547536 : Backout the dummy per in
38 * ler's (Associated with managers)
39 * created in cross business group.
40 * 19-DEC-02 nhunur No copy.
41 * 27-Apr-03 mmudigon 115.16 Absences July FP enhancements
42 * 08-Sep-03 pbodla 115.17 When backout process errors for a
43 * person subsequent life events are
44 * not backed out for the thread.
45 * 09-Sep-03 rpgupta 115.18 3136058 Grade step backout
46 * 20-Aug-04 nhunur 115.19 3840255 - Changed person selecton rule exception handling.
47 * 03-Dec-04 swjain 115.20 4034201 - passed p_bckt_stat_cd as input parameter for
48 * p_ptnl_ler_for_per_stat_cd in create_benefit_actions call.
49 * -----------------------------------------------------------------------------
50 */
51 --
52 -- Global cursor and variables declaration
53 --
54 g_package varchar2(80) := 'ben_back_out_conc';
55 g_persons_processed number(9) := 0;
56 g_persons_ended number(9) := 0;
57 g_persons_passed number(9) := 0;
58 g_persons_errored number(9) := 0;
59 g_max_errors_allowed number(9) := 200;
60 g_rec ben_type.g_report_rec;
61 --
62 --
63 -- ============================================================================
64 -- << Procedure: Do_Multithread >>
65 -- Description:
66 -- this procedure is called from 'process'. It calls the back-out routine.
67 -- ============================================================================
68 procedure do_multithread
69 (errbuf out nocopy varchar2
70 ,retcode out nocopy number
71 ,p_validate in varchar2 default 'N'
72 ,p_benefit_action_id in number
73 ,p_thread_id in number
74 ,p_effective_date in varchar2
75 ,p_business_group_id in number
76 ,p_from_ocrd_date in varchar2
77 ,p_to_ocrd_date in varchar2
78 ,p_life_event_id in number
79 ,p_organization_id in number
80 ,p_location_id in number
81 ,p_benfts_grp_id in number
82 ,p_legal_entity_id in number
83 ,p_bckt_stat_cd in varchar2
84 ,p_abs_ler in varchar2) is
85 --
86 -- Local variable declaration
87 --
88 l_proc varchar2(80) := g_package||'.do_multithread';
89 l_person_id ben_person_actions.person_id%type;
90 l_person_action_id ben_person_actions.person_action_id%type;
91 l_object_version_number ben_person_actions.object_version_number%type;
92 l_ler_id ben_person_actions.ler_id%type;
93 l_range_id ben_batch_ranges.range_id%type;
94 l_record_number number := 0;
95 l_start_person_action_id number := 0;
96 l_end_person_action_id number := 0;
97 l_actn varchar2(80);
98 l_cnt number(5):= 0;
99 l_chunk_size number(15);
100 l_threads number(15);
101 l_effective_date date;
102 l_from_ocrd_date date;
103 l_to_ocrd_date date;
104 --
105 -- Cursors declaration
106 --
107 Cursor c_range_thread is
108 Select ran.range_id
109 ,ran.starting_person_action_id
110 ,ran.ending_person_action_id
111 From ben_batch_ranges ran
112 Where ran.range_status_cd = 'U'
113 And ran.BENEFIT_ACTION_ID = P_BENEFIT_ACTION_ID
114 And rownum < 2
115 For update of ran.range_status_cd;
116 --
117 cursor c_person_thread is
118 select ben.person_id,
119 ben.person_action_id
120 from ben_person_actions ben
121 where ben.benefit_action_id = p_benefit_action_id
122 and ben.action_status_cd not in ('P','E')
123 and ben.person_action_id
124 between l_start_person_action_id
125 and l_end_person_action_id
126 order by ben.person_action_id;
127 --
128 cursor c_ler_thread is
129
130 -- grade step backout
131 -- 3136058
132 select pil.per_in_ler_id,
133 pil.person_id,
134 pil.per_in_ler_stat_cd,
135 pil.lf_evt_ocrd_dt,
136 ler.typ_cd,
137 ler.ler_id
138 from ben_per_in_ler pil,
139 ben_ler_f ler
140 where pil.person_id = l_person_id
141 and pil.lf_evt_ocrd_dt
142 between l_from_ocrd_date
143 and l_to_ocrd_date
144 and pil.business_group_id+0 = p_business_group_id
145 and ((p_abs_ler = 'N' and
146 pil.ler_id = p_life_event_id
147 and
148 ( ( ler.typ_cd = 'GSP'
149 and pil.per_in_ler_stat_cd = 'STRTD'
150 )
151 or
152 ( ler.typ_cd <> 'GSP'
153 and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
154 )
155 )
156 ) or
157 (p_abs_ler = 'Y' and
158 pil.ler_id in
159 (select ler.ler_id
160 from ben_ler_f ler
161 where ler.typ_cd = 'ABS'
162 and ler.lf_evt_oper_cd in ('START','END')
163 and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
164 and ler.business_group_id = p_business_group_id
165 and l_effective_date between ler.effective_start_date
166 and ler.effective_end_date)))
167 and ler.ler_id = pil.ler_id
168 and nvl(l_effective_date,trunc(sysdate))
169 between ler.effective_start_date
170 and ler.effective_end_date
171 order by pil.person_id desc,
172 pil.lf_evt_ocrd_dt desc,
173 decode(ler.lf_evt_oper_cd,'END',2,1) desc;
174
175
176 --
177 l_ler_thread c_ler_thread%rowtype;
178 --
179 cursor c_ler_abs_thread is
180 select pil.per_in_ler_id,
181 pil.person_id,
182 pil.per_in_ler_stat_cd,
183 pil.lf_evt_ocrd_dt,
184 ler.typ_cd,
185 ler.ler_id
186 from ben_per_in_ler pil,
187 ben_ler_f ler
188 where pil.person_id = l_person_id
189 and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
190 and pil.lf_evt_ocrd_dt
191 between l_ler_thread.lf_evt_ocrd_dt
192 and l_to_ocrd_date
193 and pil.business_group_id+0 = p_business_group_id
194 and ler.ler_id = pil.ler_id
195 and nvl(l_effective_date,trunc(sysdate))
196 between ler.effective_start_date
197 and ler.effective_end_date
198 and pil.per_in_ler_id <> l_ler_thread.per_in_ler_id
199 and ler.lf_evt_oper_cd <> 'DEL'
200 order by pil.person_id desc,
201 pil.lf_evt_ocrd_dt desc,
202 decode(ler.lf_evt_oper_cd,'END',2,1) desc;
203 l_ler_abs_thread c_ler_abs_thread%rowtype;
204
205 Cursor c_parameter is
206 Select *
207 From ben_benefit_actions ben
208 Where ben.benefit_action_id = p_benefit_action_id;
209 --
210 l_parm c_parameter%rowtype;
211 --
212 l_commit number;
213 l_per_rec per_all_people_f%rowtype;
214 l_per_dummy_rec per_all_people_f%rowtype;
215 --
216 cursor c_latest_ler is
217 select pil.per_in_ler_id,
218 ler.name
219 from ben_per_in_ler pil,
220 ben_ler_f ler
221 where pil.person_id = l_person_id
222 and pil.business_group_id+0 = p_business_group_id
223 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
224 and pil.ler_id = ler.ler_id
225 and ler.typ_cd not in ('SCHEDDU','COMP', 'GSP')
226 and nvl(l_effective_date,trunc(sysdate))
227 between ler.effective_start_date
228 and ler.effective_end_date
229 order by pil.lf_evt_ocrd_dt desc, pil.per_in_ler_id desc;
230 --
231 cursor c_latest_ler_abs is
232 select pil.per_in_ler_id,
233 ler.name
234 from ben_per_in_ler pil,
235 ben_ler_f ler
236 where pil.person_id = l_person_id
237 and pil.business_group_id+0 = p_business_group_id
238 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
239 and pil.ler_id = ler.ler_id
240 and ler.typ_cd not in ('ABS','SCHEDDU','COMP', 'GSP')
241 and pil.lf_evt_ocrd_dt >= l_from_ocrd_date
242 and nvl(l_effective_date,trunc(sysdate))
243 between ler.effective_start_date
244 and ler.effective_end_date
245 order by pil.lf_evt_ocrd_dt desc, pil.per_in_ler_id desc;
246 --
247 cursor c_latest_ler_cwb is
248 select pil.per_in_ler_id,
249 ler.name
250 from ben_per_in_ler pil,
251 ben_ler_f ler
252 where pil.person_id = l_person_id
253 and pil.business_group_id+0 = p_business_group_id
254 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
255 and pil.ler_id = ler.ler_id
256 and ler.typ_cd = 'COMP'
257 and ler.ler_id = p_life_event_id
258 and nvl(l_effective_date,trunc(sysdate))
259 between ler.effective_start_date
260 and ler.effective_end_date
261 order by pil.lf_evt_ocrd_dt desc, pil.per_in_ler_id desc;
262 -- 3136058
263 cursor c_latest_ler_gsp is
264 select pil.per_in_ler_id,
265 ler.name
266 from ben_per_in_ler pil,
267 ben_ler_f ler
268 where pil.person_id = l_person_id
269 and pil.business_group_id+0 = p_business_group_id
270 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
271 and pil.ler_id = ler.ler_id
272 and ler.typ_cd = 'GSP'
273 and ler.ler_id = p_life_event_id
274 and nvl(l_effective_date,trunc(sysdate))
275 between ler.effective_start_date
276 and ler.effective_end_date
277 order by pil.lf_evt_ocrd_dt desc, pil.per_in_ler_id desc;
278
279
280
281 --
282 l_latest_ler c_latest_ler%rowtype;
283 --
284
285 cursor c_person is
286 select ppf.*
287 from per_all_people_f ppf
288 where ppf.person_id = l_person_id
289 and nvl(l_effective_date,trunc(sysdate))
290 between ppf.effective_start_date
291 and ppf.effective_end_date;
292
293 Begin
294 --
295 hr_utility.set_location ('Entering '||l_proc,10);
296 --
297 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
298 fnd_message.set_token('PROC','dt_fndate.change_ses_date');
299 dt_fndate.change_ses_date
300 (p_ses_date => l_effective_date,
301 p_commit => l_commit);
302 --
303 l_effective_date:=trunc(fnd_date.canonical_to_date(p_effective_date));
304 l_from_ocrd_date:=trunc(fnd_date.canonical_to_date(p_from_ocrd_date));
305 l_to_ocrd_date:=trunc(fnd_date.canonical_to_date(p_to_ocrd_date));
306 --
307 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
308 fnd_message.set_token('PROC','benutils.get_parameter');
309 benutils.get_parameter(p_business_group_id => p_business_group_id
310 ,p_batch_exe_cd => 'BENBOCON'
311 ,p_threads => l_threads
312 ,p_chunk_size => l_chunk_size
313 ,p_max_errors => g_max_errors_allowed);
314 --
315 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
316 fnd_message.set_token('PROC','ben_env_object.init');
317 ben_env_object.init(p_business_group_id => p_business_group_id,
318 p_effective_date => l_effective_date,
319 p_thread_id => p_thread_id,
320 p_chunk_size => l_chunk_size,
321 p_threads => l_threads,
322 p_max_errors => g_max_errors_allowed,
323 p_benefit_action_id => p_benefit_action_id);
324 --
325 -- Copy benefit action id to global in benutils package
326 --
327 benutils.g_benefit_action_id := p_benefit_action_id;
328 benutils.g_thread_id := p_thread_id;
329 g_persons_errored := 0;
330 g_persons_processed := 0;
331 --
332 open c_parameter;
333 --
334 fetch c_parameter into l_parm;
335 --
336 close c_parameter;
337 --
338 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
339 fnd_message.set_token('PROC','ben_batch_utils.print_parameters');
340 ben_batch_utils.print_parameters
341 (p_thread_id => p_thread_id
342 ,p_benefit_action_id => p_benefit_action_id
343 ,p_validate => p_validate
344 ,p_business_group_id => p_business_group_id
345 ,p_effective_date => l_effective_date
346 ,p_person_selection_rule_id => l_parm.person_selection_rl
347 ,p_organization_id => l_parm.organization_id
348 ,p_benfts_grp_id => l_parm.benfts_grp_id
349 ,p_location_id => l_parm.location_id
350 ,p_legal_entity_id => l_parm.legal_entity_id);
351
352
353 --
354 -- While loop to only try and fetch records while they exist
355 -- we always try and fetch the size of the chunk, if we get less
356 -- then we know that the process is finished so we end the while loop.
357 -- The process is as follows :
358 -- 1) Lock the rows that are not processed
359 -- 2) Grab as many rows as we can upto the chunk size
360 -- 3) Put each row into the person cache.
361 -- 4) Process the person cache
362 -- 5) Go to number 1 again.
363 --
364 hr_utility.set_location('getting range',10);
365 --
366 Loop
367 --
368 open c_range_thread;
369 --
370 fetch c_range_thread into l_range_id
371 ,l_start_person_action_id
372 ,l_end_person_action_id;
373 hr_utility.set_location('doing range fetch',10);
374 --
375 if c_range_thread%notfound then
376 --
377 hr_utility.set_location('range not Found',10);
378 --
379 exit;
380 --
381 end if;
382 --
383 hr_utility.set_location('range Found',10);
384 --
385 close c_range_thread;
386 --
387 update ben_batch_ranges ran
388 set ran.range_status_cd = 'P'
389 where ran.range_id = l_range_id;
390 --
391 commit;
392 --
393 -- Get person who are in the range
394 --
395 open c_person_thread;
396 --
397 loop
398 --
399 fetch c_person_thread into l_person_id,
400 l_person_action_id;
401 hr_utility.set_location('person id'||l_person_id,10);
402 --
403 exit when c_person_thread%notfound;
404 --
405 savepoint last_place;
406 benutils.set_cache_record_position;
407 --
408
409 --
410 -- Commented out for CWB - Bug # 2183388
411 -- Using cursor c_person below to duplicate the functionality
412 --
413 /*
414 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
415 fnd_message.set_token('PROC','ben_person_object');
416 ben_person_object.get_object(p_person_id => l_person_id,
417 p_rec => l_per_rec);
418 */
419
420 --
421 -- CWB - Added to avoid calling ben_person_object.get_object
422 --
423
424 open c_person;
425 l_per_rec := l_per_dummy_rec;
426 fetch c_person into l_per_rec;
427 close c_person;
428
429 --
430 -- CWB
431 --
432
433 --
434 begin
435 --
436 hr_utility.set_location('Before open',10);
437 open c_ler_thread;
438 --
439 Loop
440 --
441 fetch c_ler_thread into l_ler_thread;
442 exit when c_ler_thread%notfound;
443 fnd_file.put_line(fnd_file.log,'per_in_ler_id '||l_ler_thread.per_in_ler_id);
444 fnd_file.put_line(fnd_file.log,'typ_cd '||l_ler_thread.typ_cd);
445
446 --
447 -- This is to check that only the most recent LE's
448 -- are allowed to be backed out
449 --
450 --
451
452 if l_ler_thread.typ_cd = 'ABS' then
453 --
454 open c_latest_ler_abs;
455 fetch c_latest_ler_abs into l_latest_ler;
456 if c_latest_ler_abs%found then
457 close c_latest_ler_abs;
458 fnd_message.set_name('BEN','BEN_93383_ABS_LE_CANNOT_BO');
459 fnd_message.set_token('P_LER',l_latest_ler.name);
460 fnd_message.raise_error;
461 end if;
462 close c_latest_ler_abs;
463 --
464 elsif l_ler_thread.typ_cd = 'COMP' then
465 --
466 open c_latest_ler_cwb;
467 fetch c_latest_ler_cwb into l_latest_ler;
468 if c_latest_ler_cwb%found then
469 if l_latest_ler.per_in_ler_id <> l_ler_thread.per_in_ler_id then
470 close c_latest_ler_cwb;
471 fnd_message.set_name('BEN','BEN_92216_NOT_LATST_PER_IN_LER');
472 fnd_message.raise_error;
473 end if;
474 end if;
475 close c_latest_ler_cwb;
476 --
477 elsif l_ler_thread.typ_cd = 'GSP' then
478 -- 3136058
479 open c_latest_ler_gsp;
480 fetch c_latest_ler_gsp into l_latest_ler;
481 if c_latest_ler_gsp%found then
482 if l_latest_ler.per_in_ler_id <> l_ler_thread.per_in_ler_id then
483 close c_latest_ler_gsp;
484 fnd_message.set_name('BEN','BEN_92216_NOT_LATST_PER_IN_LER');
485 fnd_message.raise_error;
486 end if;
487 end if;
488 close c_latest_ler_gsp;
489
490 --
491 else
492 --
493 open c_latest_ler;
494 fetch c_latest_ler into l_latest_ler;
495 if c_latest_ler%found then
496 if l_latest_ler.per_in_ler_id <> l_ler_thread.per_in_ler_id then
497 close c_latest_ler;
498 fnd_message.set_name('BEN','BEN_92216_NOT_LATST_PER_IN_LER');
499 fnd_message.raise_error;
500 end if;
501 end if;
502 close c_latest_ler;
503 --
504 end if;
505 --
506 --
507 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
508 fnd_message.set_token('PROC','ben_back_out_life_event');
509 ben_back_out_life_event.g_enrt_made_flag := Null;
510 ben_back_out_life_event.back_out_life_events
511 (p_per_in_ler_id => l_ler_thread.per_in_ler_id
512 ,p_business_group_id => p_business_group_id
513 ,p_bckt_stat_cd => p_bckt_stat_cd
514 ,p_effective_date => l_effective_date);
515 --
516 g_rec.ler_id := l_ler_thread.ler_id;
517 g_rec.rep_typ_cd := 'LFBO';
518 g_rec.person_id := l_person_id;
519 --
520 -- This is to assign the global variable which contains information about
521 -- the closed or in process life events with or without election,
522 -- that were backed out.
523 --
524 g_rec.text := l_ler_thread.per_in_ler_stat_cd ||
525 ben_back_out_life_event.g_enrt_made_flag;
526 --
527 -- This is to assign the per_in_ler_id in the record to extract the
528 -- the electable choices later.
529 g_rec.temporal_ler_id := l_ler_thread.per_in_ler_id;
530
531 benutils.write(p_rec => g_rec);
532 --
533 End loop;
534 --
535 close c_ler_thread;
536 --
537 -- If we get here it was successful.
538 --
539 update ben_person_actions
540 set action_status_cd = 'P'
541 where person_id = l_person_id
542 and benefit_action_id = p_benefit_action_id;
543 --
544 benutils.write(l_per_rec.full_name||' processed successfully');
545 g_persons_processed := g_persons_processed + 1;
546 --
547 exception
548 --
549 when others then
550 --
551 hr_utility.set_location('Super Error exception level',10);
552 hr_utility.set_location(sqlerrm,10);
553
554 if c_latest_ler%isopen then
555
556 close c_latest_ler;
557 --
558 end if;
559
560 --
561 if c_ler_thread%isopen then
562
563 close c_ler_thread;
564 --
565 end if;
566 --
567 rollback to last_place;
568 benutils.rollback_cache;
569 --
570 update ben_person_actions
571 set action_status_cd = 'E'
572 where person_id = l_person_id
573 and benefit_action_id = p_benefit_action_id;
574 --
575 commit;
576 --
577 g_persons_errored := g_persons_errored + 1;
578 g_rec.ler_id := nvl(p_life_event_id,l_ler_thread.ler_id);
579 g_rec.rep_typ_cd := 'ERROR_LF';
580 -- g_rec.text := fnd_message.get; -- Bug 2376630 Moved code below benutils.get_message_name
581 g_rec.person_id := l_person_id;
582
583 -- Bug 2376330 start
584 -- Added National Identifier and Error Message code
585
586 g_rec.national_identifier := l_per_rec.national_identifier;
587 g_rec.error_message_code := benutils.get_message_name;
588 g_rec.text := fnd_message.get;
589
590 -- Bug 2376330 end
591 --
592 hr_utility.set_location('Error Message '||g_rec.text,10);
593 benutils.write(l_per_rec.full_name||' processed unsuccessfully');
594 benutils.write(g_rec.text);
595 benutils.write(p_rec => g_rec);
596 --
597 hr_utility.set_location('Max Errors = '||g_max_errors_allowed,10);
598 hr_utility.set_location('Num Errors = '||g_persons_errored,10);
599 if g_persons_errored > g_max_errors_allowed then
600 --
601 fnd_message.set_name('BEN','BEN_92431_BENBOCON_ERROR_LIMIT');
602 benutils.write(p_text => fnd_message.get);
603 --
604 raise;
605 --
606 end if;
607 --
608 end;
609 --
610 hr_utility.set_location('Closing c_person_thread',10);
611 --
612 end loop;
613 --
614 close c_person_thread;
615 --
616 -- Commit chunk
617 --
618 if p_validate = 'Y' then
619 --
620 hr_utility.set_location('Rolling back transaction ',10);
621 --
622 rollback;
623 --
624 end if;
625 --
626 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
627 fnd_message.set_token('PROC','benutils.write_table_and_file');
628 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
629 commit;
630 --
631 end loop;
632 --
633 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
634 fnd_message.set_token('PROC','benbatch_utils.write_logfile');
635 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_processed
636 ,p_num_pers_errored => g_persons_errored);
637 --
638 commit;
639 --
640 hr_utility.set_location ('Leaving '||l_proc,70);
641 --
642 Exception
643 --
644 When others then
645 --
646 hr_utility.set_location('Super Error',10);
647 hr_utility.set_location(sqlerrm,10);
648 hr_utility.set_location('Super Error',10);
649 rollback;
650 benutils.rollback_cache;
651 --
652 g_rec.ler_id := nvl(p_life_event_id,l_ler_thread.ler_id);
653 g_rec.rep_typ_cd := 'FATAL';
654 g_rec.text := fnd_message.get;
655 g_rec.person_id := l_person_id;
656 --
657 benutils.write(p_text => g_rec.text);
658 benutils.write(p_rec => g_rec);
659 --
660 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_processed
661 ,p_num_pers_errored => g_persons_errored);
662 --
663 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
664 --
665 commit;
666 --
667 fnd_message.raise_error;
668 --
669 End do_multithread;
670 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
671 -- << Procedure: Restart >>
672 -- *****************************************************************
673 --
674 procedure restart (errbuf out nocopy varchar2
675 ,retcode out nocopy number
676 ,p_benefit_action_id in number) is
677 --
678 -- Cursor Declaration
679 --
680 cursor c_parameters is
681 Select process_date
682 ,mode_cd
683 ,validate_flag
684 ,person_id
685 ,business_group_id
686 ,popl_enrt_typ_cycl_id
687 ,person_selection_rl
688 ,ler_id
689 ,organization_id
690 ,location_id
691 ,benfts_grp_id
692 ,legal_entity_id
693 ,debug_messages_flag
694 ,date_from
695 ,uneai_effective_date
696 ,ptnl_ler_for_per_stat_cd
697 ,inelg_action_cd
698 From ben_benefit_actions ben
699 Where ben.benefit_action_id = p_benefit_action_id;
700 --
701 -- Local Variable declaration.
702 --
703 l_proc varchar2(80) := g_package||'.restart';
704 l_parameters c_parameters%rowtype;
705 l_errbuf varchar2(80);
706 l_retcode number;
707 --
708 Begin
709 --
710 hr_utility.set_location ('Entering '||l_proc,10);
711 --
712 -- get the parameters for a previous run and do a restart
713 --
714 open c_parameters;
715 --
716 fetch c_parameters into l_parameters;
717 If c_parameters%notfound then
718 --
719 fnd_message.set_name('BEN','BEN_91710_RESTRT_PARMS_NOT_FND');
720 fnd_message.raise_error;
721 --
722 End if;
723 --
724 close c_parameters;
725 --
726 -- Call process procedure with parameters for restart
727 --
728 process(errbuf => l_errbuf
729 ,retcode => l_retcode
730 ,p_benefit_action_id => p_benefit_action_id
731 ,p_effective_date => fnd_date.date_to_canonical
732 (l_parameters.process_date)
733 ,p_validate => l_parameters.validate_flag
734 ,p_business_group_id => l_parameters.business_group_id
735 ,p_life_event_id => l_parameters.ler_id
736 ,p_from_ocrd_date => fnd_date.date_to_canonical
737 (l_parameters.date_from)
738 ,p_to_ocrd_date => fnd_date.date_to_canonical
739 (l_parameters.uneai_effective_date)
740 ,p_organization_id => l_parameters.organization_id
741 ,p_location_id => l_parameters.location_id
742 ,p_benfts_grp_id => l_parameters.benfts_grp_id
743 ,p_legal_entity_id => l_parameters.legal_entity_id
744 ,p_person_selection_rule_id => l_parameters.person_selection_rl
745 ,p_debug_messages => l_parameters.debug_messages_flag);
746 --
747 hr_utility.set_location ('Leaving '||l_proc,70);
748 --
749 end restart;
750 --
751 -- *************************************************************************
752 -- * << Procedure: Process >>
753 -- *************************************************************************
754 -- This is what is called from the concurrent manager screen
755 --
756 procedure process(errbuf out nocopy varchar2
757 ,retcode out nocopy number
758 ,p_benefit_action_id in number default null
759 ,p_effective_date in varchar2
760 ,p_validate in varchar2 default 'N'
761 ,p_business_group_id in number
762 ,p_life_event_id in number
763 ,p_from_ocrd_date in varchar2
764 ,p_to_ocrd_date in varchar2
765 ,p_organization_id in number default null
766 ,p_location_id in number default null
767 ,p_benfts_grp_id in number default null
768 ,p_legal_entity_id in number default null
769 ,p_person_selection_rule_id in number default null
770 ,p_debug_messages in varchar2 default 'N'
771 ,p_bckt_stat_cd in varchar2 default 'UNPROCD'
772 ,p_abs_ler in varchar2 default 'N') is
773 --
774 l_effective_date date;
775 l_from_ocrd_date date;
776 l_to_ocrd_date date;
777 l_no_one_to_process exception; --Bug 2253040
778 l_bckt_stat varchar2(20);
779 --
780 -- Cursors declaration.
781 --
782 cursor c_person is
783 -- grade step backout
784 -- 3136058
785 select ppf.person_id
786 from per_all_people_f ppf
787 where -- bug 2547536 ppf.business_group_id = p_business_group_id and
788 l_effective_date
789 between ppf.effective_start_date
790 and ppf.effective_end_date
791 and exists (select null
792 from ben_per_in_ler pil
793 , ben_ler_f ler
794 where pil.lf_evt_ocrd_dt between l_from_ocrd_date
795 and l_to_ocrd_date
796 and pil.ler_id = ler.ler_id
797 and l_effective_date between ler.effective_start_date
798 and ler.effective_end_date
799 and ler.business_group_id = p_business_group_id
800 and ((p_abs_ler = 'N'
801 and pil.ler_id = p_life_event_id
802 and
803 ( ( ler.typ_cd = 'GSP'
804 and pil.per_in_ler_stat_cd = 'STRTD'
805 )
806 or
807 ( ler.typ_cd <> 'GSP'
808 and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
809 )
810 )
811 )
812 or
813 (p_abs_ler = 'Y'
814 and ler.typ_cd = 'ABS'
815 and ler.lf_evt_oper_cd in ('START','END')
816 and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
817 )
818 )
819 and pil.business_group_id = p_business_group_id
820 and pil.person_id = ppf.person_id
821 )
822 -- Bug 2547536 -- and pil.business_group_id = ppf.business_group_id)
823 and (p_organization_id is null
824 or exists (select null
825 from per_all_assignments_f paa
826 where paa.person_id = ppf.person_id
827 and l_effective_date
828 between paa.effective_start_date
829 and paa.effective_end_date
830 and paa.business_group_id = ppf.business_group_id
831 and paa.primary_flag = 'Y'
832 and paa.organization_id = p_organization_id))
833 and (p_location_id is null
834 or exists (select null
835 from per_all_assignments_f paa
836 where paa.person_id = ppf.person_id
837 and l_effective_date
838 between paa.effective_start_date
839 and paa.effective_end_date
840 and paa.business_group_id = ppf.business_group_id
841 and paa.primary_flag = 'Y'
842 and paa.location_id = p_location_id))
843 and (p_benfts_grp_id is null
844 or exists (select null
845 from per_all_people_f pap
846 where pap.person_id = ppf.person_id
847 and pap.business_group_id = ppf.business_group_id
848 and l_effective_date
849 between pap.effective_start_date
850 and pap.effective_end_date
851 and pap.benefit_group_id = p_benfts_grp_id))
852 and (p_legal_entity_id is null
853 or exists (select null
854 from per_assignments_f paf,
855 hr_soft_coding_keyflex soft
856 where paf.person_id = ppf.person_id
857 and paf.assignment_type <> 'C'
858 and l_effective_date
859 between paf.effective_start_date
860 and paf.effective_end_date
861 and paf.business_group_id = ppf.business_group_id
862 and paf.primary_flag = 'Y'
863 and soft.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
864 and soft.segment1 = to_char(p_legal_entity_id))) ;
865
866 -- begin bug #2275321
867 -- The union does not contain checks for organization, benefit group,
868 -- legal entity and location. Also as all types of life events (comp, dsblty etc)
869 -- are fetched by the above sql, there is no need for the union
870 /*
871 UNION
872 select ppf.person_id
873 from per_all_people_f ppf
874 where l_effective_date between ppf.effective_start_date and ppf.effective_end_date
875 and exists (select null
876 from ben_per_in_ler pil,
877 ben_ler_f ler
878 where ler.ler_id = pil.ler_id
879 and ler.typ_cd = 'COMP'
880 and l_effective_date
881 between ler.effective_start_date
882 and ler.effective_end_date
883 and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
884 and pil.lf_evt_ocrd_dt
885 between l_from_ocrd_date
886 and l_to_ocrd_date
887 and pil.ler_id = p_life_event_id
888 and pil.person_id = ppf.person_id
889 and pil.business_group_id = p_business_group_id);
890 */
891 -- end bug # 2275321
892 --
893 -- local variable declaration.
894 --
895 l_request_id number;
896 l_proc varchar2(80) := g_package||'.process';
897 l_benefit_action_id ben_benefit_actions.benefit_action_id%type;
898 l_object_version_number ben_benefit_actions.object_version_number%type;
899 l_person_id per_people_f.person_id%type;
900 l_person_action_id ben_person_actions.person_action_id%type;
901 l_ler_id ben_ler_f.ler_id%type;
902 l_range_id ben_batch_ranges.range_id%type;
903 l_chunk_size number := 20;
904 l_threads number := 1;
905 l_start_person_action_id number := 0;
906 l_end_person_action_id number := 0;
907 l_prev_person_id number := 0;
908 rl_ret char(1);
909 skip boolean;
910 l_person_cnt number := 0;
911 l_cnt number := 0;
912 l_num_range number := 0;
913 l_chunk_num number := 1;
914 l_num_row number := 0;
915 l_commit number;
916 --
917 l_person_ok varchar2(1) := 'Y';
918 l_err_message varchar2(2000);
919 Begin
920 --
921 hr_utility.set_location ('Entering '||l_proc,10);
922 --
923 l_effective_date:=trunc(fnd_date.canonical_to_date(p_effective_date));
924 l_from_ocrd_date:=trunc(fnd_date.canonical_to_date(p_from_ocrd_date));
925 l_to_ocrd_date:=trunc(fnd_date.canonical_to_date(p_to_ocrd_date));
926 --
927 dt_fndate.change_ses_date
928 (p_ses_date => l_effective_date,
929 p_commit => l_commit);
930 --
931 -- Get chunk_size and Thread values for multi-thread process.
932 --
933 ben_batch_utils.ini;
934 ben_batch_utils.ini(p_actn_cd => 'PROC_INFO');
935 --
936 benutils.get_parameter(p_business_group_id => p_business_group_id
937 ,p_batch_exe_cd => 'BENBOCON'
938 ,p_threads => l_threads
939 ,p_chunk_size => l_chunk_size
940 ,p_max_errors => g_max_errors_allowed);
941 --
942 -- Create benefit actions parameters in the benefit action table.
943 -- Do not create if a benefit action already exists, in other words
944 -- we are doing a restart.
945 --
946 If p_benefit_action_id is null then
947 --
948 ben_benefit_actions_api.create_benefit_actions
949 (p_validate => false
950 ,p_benefit_action_id => l_benefit_action_id
951 ,p_process_date => l_effective_date
952 ,p_mode_cd => 'S'
953 ,p_derivable_factors_flag => 'N'
954 ,p_validate_flag => p_validate
955 ,p_person_id => null
956 ,p_person_type_id => null
957 ,p_pgm_id => null
958 ,p_business_group_id => p_business_group_id
959 ,p_pl_typ_id => null
960 ,p_pl_id => null
961 ,p_popl_enrt_typ_cycl_id => null
962 ,p_no_programs_flag => 'N'
963 ,p_no_plans_flag => 'N'
964 ,p_comp_selection_rl => null
965 ,p_person_selection_rl => p_person_selection_rule_id
966 ,p_ler_id => p_life_event_id
967 ,p_organization_id => p_organization_id
968 ,p_benfts_grp_id => p_benfts_grp_id
969 ,p_location_id => p_location_id
970 ,p_pstl_zip_rng_id => null
971 ,p_rptg_grp_id => null
972 ,p_opt_id => null
973 ,p_eligy_prfl_id => null
974 ,p_vrbl_rt_prfl_id => null
975 ,p_legal_entity_id => p_legal_entity_id
976 ,p_payroll_id => null
977 ,p_debug_messages_flag => p_debug_messages
978 ,p_object_version_number => l_object_version_number
979 ,p_effective_date => l_effective_date
980 ,p_request_id => fnd_global.conc_request_id
981 ,p_program_application_id => fnd_global.prog_appl_id
982 ,p_program_id => fnd_global.conc_program_id
983 ,p_program_update_date => sysdate
984 ,p_date_from => l_from_ocrd_date
985 ,p_uneai_effective_date => l_to_ocrd_date
986 --Bug No 4034201
987 ,p_ptnl_ler_for_per_stat_cd => p_bckt_stat_cd);
988 --
989 benutils.g_benefit_action_id := l_benefit_action_id;
990 --
991 -- Delete/clear ranges from ben_batch_ranges table
992 --
993 Delete from ben_batch_ranges
994 Where benefit_action_id = l_benefit_action_id;
995 --
996 -- Now lets create person actions for all the people we are going to
997 -- process in the Back-out life event run
998 --
999 open c_person;
1000 --
1001 l_person_cnt := 0;
1002 l_cnt := 0;
1003 --
1004 loop
1005 --
1006 fetch c_person into l_person_id;
1007 exit when c_person%notfound;
1008 --
1009 l_cnt := l_cnt + 1;
1010 --
1011 l_person_ok := 'Y';
1012 --
1013 If p_person_selection_rule_id is not NULL then
1014 --
1015 ben_batch_utils.person_selection_rule
1016 (p_person_id => l_person_id
1017 ,p_business_group_id => p_business_group_id
1018 ,p_person_selection_rule_id=> p_person_selection_rule_id
1019 ,p_effective_date => l_effective_date
1020 ,p_return => l_person_ok
1021 ,p_err_message => l_err_message );
1022
1023 if l_err_message is not null
1024 then
1025 Ben_batch_utils.write(p_text =>
1026 '<< Person id : '||to_char(l_person_id)||' failed.'||
1027 ' Reason : '|| l_err_message ||' >>' );
1028 l_err_message := NULL ;
1029 end if ;
1030 --
1031 End if;
1032 --
1033 -- Store person_id into person actions table.
1034 --
1035 If l_person_ok = 'Y' then
1036 --
1037 Ben_person_actions_api.create_person_actions
1038 (p_validate => false
1039 ,p_person_action_id => l_person_action_id
1040 ,p_person_id => l_person_id
1041 ,p_ler_id => l_ler_id
1042 ,p_benefit_action_id => l_benefit_action_id
1043 ,p_action_status_cd => 'U'
1044 ,p_chunk_number => l_chunk_num
1045 ,p_object_version_number => l_object_version_number
1046 ,p_effective_date => l_effective_date);
1047 --
1048 l_num_row := l_num_row + 1;
1049 l_person_cnt := l_person_cnt + 1;
1050 l_end_person_action_id := l_person_action_id;
1051 --
1052 If l_num_row = 1 then
1053 --
1054 l_start_person_action_id := l_person_action_id;
1055 --
1056 End if;
1057 --
1058 If l_num_row = l_chunk_size then
1059 --
1060 -- Create a range of data to be multithreaded.
1061 --
1062 Ben_batch_ranges_api.create_batch_ranges
1063 (p_validate => false
1064 ,p_benefit_action_id => l_benefit_action_id
1065 ,p_range_id => l_range_id
1066 ,p_range_status_cd => 'U'
1067 ,p_starting_person_action_id => l_start_person_action_id
1068 ,p_ending_person_action_id => l_end_person_action_id
1069 ,p_object_version_number => l_object_version_number
1070 ,p_effective_date => l_effective_date);
1071 --
1072 l_start_person_action_id := 0;
1073 l_end_person_action_id := 0;
1074 l_num_row := 0;
1075 l_num_range := l_num_range + 1;
1076 --
1077 End if;
1078 --
1079 End if;
1080 --
1081 End loop;
1082 --
1083 close c_person;
1084 --
1085 hr_utility.set_location('l_num_row='||to_char(l_num_row),18);
1086 --
1087 If l_num_row <> 0 then
1088 --
1089 Ben_batch_ranges_api.create_batch_ranges
1090 (p_validate => false
1091 ,p_benefit_action_id => l_benefit_action_id
1092 ,p_range_id => l_range_id
1093 ,p_range_status_cd => 'U'
1094 ,p_starting_person_action_id => l_start_person_action_id
1095 ,p_ending_person_action_id => l_end_person_action_id
1096 ,p_object_version_number => l_object_version_number
1097 ,p_effective_date => l_effective_date);
1098 --
1099 l_num_range := l_num_range + 1;
1100 --
1101 End if;
1102 --
1103 Else
1104 --
1105 l_benefit_action_id := p_benefit_action_id;
1106 --
1107 Ben_batch_utils.create_restart_person_actions
1108 (p_benefit_action_id => p_benefit_action_id
1109 ,p_effective_date => l_effective_date
1110 ,p_chunk_size => l_chunk_size
1111 ,p_threads => l_threads
1112 ,p_num_ranges => l_num_range
1113 ,p_num_persons => l_person_cnt);
1114 --
1115 End if;
1116 --
1117 If l_num_range > 1 then
1118 --
1119 For l_count in 1..least(l_threads,l_num_range)-1 loop
1120 --
1121 l_request_id := fnd_request.submit_request
1122 (application => 'BEN'
1123 ,program => 'BENBOCOM'
1124 ,description => NULL
1125 ,sub_request => FALSE
1126 ,argument1 => p_validate
1127 ,argument2 => l_benefit_action_id
1128 ,argument3 => l_count
1129 ,argument4 => p_effective_date
1130 ,argument5 => p_business_group_id
1131 ,argument6 => p_from_ocrd_date
1132 ,argument7 => p_to_ocrd_date
1133 ,argument8 => p_life_event_id
1134 ,argument9 => p_organization_id
1135 ,argument10 => p_location_id
1136 ,argument11 => p_benfts_grp_id
1137 ,argument12 => p_legal_entity_id
1138 ,argument13 => p_bckt_stat_cd
1139 ,argument14 => p_abs_ler);
1140 --
1141 -- Store the request id of the concurrent request
1142 --
1143 ben_batch_utils.g_num_processes := ben_batch_utils.g_num_processes + 1;
1144 ben_batch_utils.g_processes_tbl(ben_batch_utils.g_num_processes)
1145 := l_request_id;
1146 --
1147 End loop;
1148 --
1149 commit;
1150 --
1151 Elsif (l_num_range = 0 ) then
1152 --
1153 Ben_batch_utils.print_parameters
1154 (p_thread_id => 99
1155 ,p_benefit_action_id => l_benefit_action_id
1156 ,p_validate => p_validate
1157 ,p_business_group_id => p_business_group_id
1158 ,p_effective_date => l_effective_date
1159 ,p_person_selection_rule_id => p_person_selection_rule_id
1160 ,p_ler_id => p_life_event_id
1161 ,p_organization_id => p_organization_id
1162 ,p_benfts_grp_id => p_benfts_grp_id
1163 ,p_location_id => p_location_id
1164 ,p_legal_entity_id => p_legal_entity_id);
1165 --
1166 fnd_message.set_name('BEN','BEN_91769_NOONE_TO_PROCESS');
1167 --Bug 2253040
1168 fnd_message.set_token('PROC' , l_proc);
1169 raise l_no_one_to_process;
1170 -- fnd_message.raise_error;
1171 --Bug 2253040
1172 --
1173 End if;
1174 --
1175 do_multithread(errbuf => errbuf
1176 ,retcode => retcode
1177 ,p_validate => p_validate
1178 ,p_benefit_action_id => l_benefit_action_id
1179 ,p_thread_id => l_threads+1
1180 ,p_effective_date => p_effective_date
1181 ,p_business_group_id => p_business_group_id
1182 ,p_from_ocrd_date => p_from_ocrd_date
1183 ,p_to_ocrd_date => p_to_ocrd_date
1184 ,p_life_event_id => p_life_event_id
1185 ,p_organization_id => p_organization_id
1186 ,p_location_id => p_location_id
1187 ,p_benfts_grp_id => p_benfts_grp_id
1188 ,p_legal_entity_id => p_legal_entity_id
1189 ,p_bckt_stat_cd => p_bckt_stat_cd
1190 ,p_abs_ler => p_abs_ler);
1191 --
1192 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1193 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
1194 ,p_person_selected => l_person_cnt
1195 ,p_business_group_id => p_business_group_id);
1196 --
1197 -- submit summary report here
1198 --
1199 l_request_id := fnd_request.submit_request
1200 (application => 'BEN',
1201 program => 'BENBOSUM',
1202 description => null,
1203 sub_request => false,
1204 argument1 => fnd_global.conc_request_id);
1205 --
1206 --submit Error reports here
1207 --
1208 l_request_id := fnd_request.submit_request
1209 (application => 'BEN',
1210 program => 'BENERTYP',
1211 description => null,
1212 sub_request => false,
1213 argument1 => fnd_global.conc_request_id);
1214 --
1215 l_request_id := fnd_request.submit_request
1216 (application => 'BEN',
1217 program => 'BENERPER',
1218 description => null,
1219 sub_request => false,
1220 argument1 => fnd_global.conc_request_id);
1221 --
1222 hr_utility.set_location ('Leaving '||l_proc,70);
1223 --
1224 hr_utility.trace_off;
1225 Exception
1226 --Bug 2253040
1227 when l_no_one_to_process then
1228 benutils.write(p_text => fnd_message.get);
1229 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1230 --Bug 2253040
1231
1232 when others then
1233 --
1234 hr_utility.set_location('Super Error',10);
1235 rollback;
1236 benutils.write(p_text => fnd_message.get);
1237 benutils.write(p_text => sqlerrm);
1238 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
1239 If (l_num_range > 0) then
1240 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
1241 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
1242 ,p_person_selected => l_person_cnt
1243 ,p_business_group_id => p_business_group_id
1244 ) ;
1245 End if;
1246 fnd_message.raise_error;
1247 End process;
1248 --
1249 end ben_back_out_conc; -- End of Package.