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