[Home] [Help]
PACKAGE BODY: APPS.BEN_IREC_BACK_OUT_CONC
Source
1 package body ben_irec_back_out_conc as
2 /* $Header: benircbo.pkb 120.0 2005/05/28 09:04 appldev noship $ */
3 --
4 /* ============================================================================
5 * Name
6 * Back-out iRecruitment Life Events Concurrent Manager Processes
7 *
8 * Purpose
9 * This is a new package added to backout data created by the
10 * iRecruitment
11 * This package houses the procedure which would be called from
12 * the concurrent manager.
13 *
14 * Additional Notes
15 * Though iRec doesnt need multithreading, person selection etc,
16 * currently this concurrent program have all those capabilities
17 * that incase they need it in future, we can start using that.
18 *
19 * History
20 * Date Who Version What?
21 * ------- --------- ------- --------------------------------------
22 * 8-Sep-2004 hmani 115.0 Created
23 * 30-Sep-2004 hmani 115.1 Added self-service/wrapper proc
24 * -----------------------------------------------------------------------------
25 */
26
27 /* global variables */
28 g_package varchar2(80) := 'ben_irec_back_out_conc';
29 g_persons_processed number(9) := 0;
30 g_persons_ended number(9) := 0;
31 g_persons_passed number(9) := 0;
32 g_persons_errored number(9) := 0;
33 g_max_errors_allowed number(9) := 200;
34 g_rec ben_type.g_report_rec;
35 -- ============================================================================
36 -- << Procedure: Do_Multithread >>
37 -- Description:
38 -- this procedure is called from 'process'. It calls the back-out routine.
39 -- ============================================================================
40 procedure do_multithread
41 (errbuf out nocopy varchar2
42 ,retcode out nocopy number
43 ,p_validate in varchar2 default 'N'
44 ,p_benefit_action_id in number
45 ,p_thread_id in number
46 ,p_effective_date in varchar2
47 ,p_business_group_id in number
48 ,p_person_id in number
49 ,p_ocrd_date in varchar2
50 ,p_assignment_id in number
51 ,p_life_event_id in number
52 ,p_bckt_stat_cd in varchar2
53 ) is
54 -- Local variable declaration
55 --
56 l_proc varchar2(80) := g_package||'.do_multithread';
57 l_person_id ben_person_actions.person_id%type;
58 l_person_action_id ben_person_actions.person_action_id%type;
59 l_object_version_number ben_person_actions.object_version_number%type;
60 l_ler_id ben_person_actions.ler_id%type;
61 l_range_id ben_batch_ranges.range_id%type;
62 l_record_number number := 0;
63 l_start_person_action_id number := 0;
64 l_end_person_action_id number := 0;
65 l_actn varchar2(80);
66 l_cnt number(5):= 0;
67 l_chunk_size number(15);
68 l_threads number(15);
69 l_effective_date date;
70 l_ocrd_date date;
71 l_commit number;
72 l_per_rec per_all_people_f%rowtype;
73 l_dummy2 number;
74 -- l_per_dummy_rec per_all_people_f%rowtype;
75
76 -- Cursors declaration
77 --
78 Cursor c_range_thread is
79 Select ran.range_id
80 ,ran.starting_person_action_id
81 ,ran.ending_person_action_id
82 From ben_batch_ranges ran
83 Where ran.range_status_cd = 'U'
84 And ran.BENEFIT_ACTION_ID = P_BENEFIT_ACTION_ID
85 And rownum < 2
86 For update of ran.range_status_cd;
87 --
88 cursor c_person_thread is
89 select ben.person_id,
90 ben.person_action_id
91 from ben_person_actions ben
92 where ben.benefit_action_id = p_benefit_action_id
93 and ben.action_status_cd not in ('P','E')
94 and ben.person_action_id
95 between l_start_person_action_id
96 and l_end_person_action_id
97 order by ben.person_action_id;
98 --
99 cursor c_ler_thread is
100 select pil.per_in_ler_id,
101 pil.person_id,
102 pil.per_in_ler_stat_cd,
103 pil.lf_evt_ocrd_dt,
104 pil.business_group_id,
105 ler.typ_cd,
106 ler.ler_id
107 from ben_per_in_ler pil,
108 ben_ler_f ler
109 where pil.person_id = l_person_id
110 and pil.lf_evt_ocrd_dt = l_ocrd_date --between l_from_ocrd_date and l_to_ocrd_date
111 -- and pil.ler_id = p_life_event_id
112 and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
113 and pil.assignment_id = p_assignment_id
114 and pil.person_id = p_person_id
115 and ler.ler_id = pil.ler_id
116 and ler.typ_cd = 'IREC'
117 and nvl(l_effective_date,trunc(sysdate))
118 between ler.effective_start_date
119 and ler.effective_end_date
120 order by pil.person_id desc;
121 --
122 l_ler_thread c_ler_thread%rowtype;
123 --
124 Cursor c_parameter is
125 Select *
126 From ben_benefit_actions ben
127 Where ben.benefit_action_id = p_benefit_action_id;
128 --
129 l_parm c_parameter%rowtype;
130 --
131 --
132 cursor c_person is
133 select ppf.*
134 from per_all_people_f ppf
135 where ppf.person_id = l_person_id
136 and nvl(l_effective_date,trunc(sysdate))
137 between ppf.effective_start_date
138 and ppf.effective_end_date;
139
140 --
141 --
142 Begin
143 --
144
145 hr_utility.set_location ('Entering '||l_proc,10);
146 --
147 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
148 fnd_message.set_token('PROC','dt_fndate.change_ses_date');
149 dt_fndate.change_ses_date
150 (p_ses_date => l_effective_date,
151 p_commit => l_commit);
152 --
153 l_effective_date:=trunc(fnd_date.canonical_to_date(p_effective_date));
154 l_ocrd_date := trunc(fnd_date.canonical_to_date(p_ocrd_date));
155 --
156 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
157 fnd_message.set_token('PROC','benutils.get_parameter');
158 benutils.get_parameter(p_business_group_id => p_business_group_id
159 ,p_batch_exe_cd => 'BENBOCON'
160 ,p_threads => l_threads
161 ,p_chunk_size => l_chunk_size
162 ,p_max_errors => g_max_errors_allowed);
163 --
164 hr_utility.set_location ('l_threads '||l_threads,10);
165 hr_utility.set_location ('l_chunk_size '||l_chunk_size,10);
166 --
167 --
168 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
169 fnd_message.set_token('PROC','ben_env_object.init');
170 ben_env_object.init(p_business_group_id => p_business_group_id,
171 p_effective_date => l_effective_date,
172 p_thread_id => p_thread_id,
173 p_chunk_size => l_chunk_size,
174 p_threads => l_threads,
175 p_max_errors => g_max_errors_allowed,
176 p_benefit_action_id => p_benefit_action_id);
177 --
178 -- Copy benefit action id to global in benutils package
179 --
180 benutils.g_benefit_action_id := p_benefit_action_id;
181 benutils.g_thread_id := p_thread_id;
182 g_persons_errored := 0;
183 g_persons_processed := 0;
184 --
185 open c_parameter;
186 --
187 fetch c_parameter into l_parm;
188 --
189 close c_parameter;
190 --
191 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
192 fnd_message.set_token('PROC','ben_batch_utils.print_parameters');
193 ben_batch_utils.print_parameters
194 (p_thread_id => p_thread_id
195 ,p_benefit_action_id => p_benefit_action_id
196 ,p_validate => p_validate
197 ,p_business_group_id => p_business_group_id
198 ,p_effective_date => l_effective_date
199 ,p_person_selection_rule_id => l_parm.person_selection_rl
200 ,p_organization_id => l_parm.organization_id
201 ,p_benfts_grp_id => l_parm.benfts_grp_id
202 ,p_location_id => l_parm.location_id
203 ,p_legal_entity_id => l_parm.legal_entity_id);
204
205
206 --
207 -- While loop to only try and fetch records while they exist
208 -- we always try and fetch the size of the chunk, if we get less
209 -- then we know that the process is finished so we end the while loop.
210 -- The process is as follows :
211 -- 1) Lock the rows that are not processed
212 -- 2) Grab as many rows as we can upto the chunk size
213 -- 3) Put each row into the person cache.
214 -- 4) Process the person cache
215 -- 5) Go to number 1 again.
216 --
217 hr_utility.set_location('getting range',10);
218 --
219 Loop
220 --
221 open c_range_thread;
222 --
223 fetch c_range_thread into l_range_id
224 ,l_start_person_action_id
225 ,l_end_person_action_id;
226 hr_utility.set_location('doing range fetch',10);
227 --
228 if c_range_thread%notfound then
229 --
230 hr_utility.set_location('range not Found',10);
231 --
232 exit;
233 --
234 end if;
235 --
236 hr_utility.set_location('range Found',10);
237 --
238 close c_range_thread;
239 --
240 update ben_batch_ranges ran
241 set ran.range_status_cd = 'P'
242 where ran.range_id = l_range_id;
243 --
244 commit;
245 --
246 -- Get person who are in the range
247 --
248 open c_person_thread;
249 --
250 loop
251 --
252 fetch c_person_thread into l_person_id,
253 l_person_action_id;
254 hr_utility.set_location('person id'||l_person_id,10);
255 --
256 exit when c_person_thread%notfound;
257 --
258 savepoint last_place;
259 benutils.set_cache_record_position;
260
261 open c_person;
262 -- l_per_rec := l_per_dummy_rec;
263 fetch c_person into l_per_rec;
264 close c_person;
265
266 --
267 begin
268 --
269 hr_utility.set_location('Before open',10);
270 open c_ler_thread;
271 --
272 Loop
273 --
274 fetch c_ler_thread into l_ler_thread;
275 exit when c_ler_thread%notfound;
276 --fnd_file.put_line(fnd_file.log,'per_in_ler_id '||l_ler_thread.per_in_ler_id);
277 -- fnd_file.put_line(fnd_file.log,'typ_cd '||l_ler_thread.typ_cd);
278 --
279 hr_utility.set_location ('per_in_ler_id '||l_ler_thread.per_in_ler_id,10);
280 hr_utility.set_location ('typ_cd '||l_ler_thread.typ_cd,10);
281 hr_utility.set_location ('bg id '||l_ler_thread.business_group_id,10);
282
283 --
284 --
285 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
286 fnd_message.set_token('PROC','ben_back_out_life_event');
287 hr_utility.set_location ('calling bolfe ',10);
288
289 ben_back_out_life_event.g_enrt_made_flag := Null;
290
291 ben_back_out_life_event.back_out_life_events
292 (p_per_in_ler_id => l_ler_thread.per_in_ler_id
293 ,p_business_group_id => l_ler_thread.business_group_id
294 ,p_bckt_stat_cd => p_bckt_stat_cd
295 ,p_effective_date => l_effective_date);
296 --
297
298 hr_utility.set_location ('this ler is '||l_ler_thread.per_in_ler_id||' pil is '||p_life_event_id,777);
299
300 g_rec.ler_id := l_ler_thread.ler_id;
301 g_rec.rep_typ_cd := 'LFBO';
302 g_rec.person_id := l_person_id;
303 --
304 -- This is to assign the global variable which contains information about
305 -- the closed or in process life events with or without election,
306 -- that were backed out.
307 --
308 g_rec.text := l_ler_thread.per_in_ler_stat_cd ||
309 ben_back_out_life_event.g_enrt_made_flag;
310 --
311 -- This is to assign the per_in_ler_id in the record to extract the
312 -- the electable choices later.
313 g_rec.temporal_ler_id := l_ler_thread.per_in_ler_id;
314
315 benutils.write(p_rec => g_rec);
316 --
317 End loop;
318 --
319 close c_ler_thread;
320 --
321 -- If we get here it was successful.
322 --
323 update ben_person_actions
324 set action_status_cd = 'P'
325 where person_id = l_person_id
326 and benefit_action_id = p_benefit_action_id;
327 --
328 benutils.write(l_per_rec.full_name||' processed successfully');
329 g_persons_processed := g_persons_processed + 1;
330 --
331 exception
332 --
333 when others then
334 --
335 hr_utility.set_location('Super Error exception level',10);
336 hr_utility.set_location(sqlerrm,10);
337
338 --
339 if c_ler_thread%isopen then
340
341 close c_ler_thread;
342 --
343 end if;
344 --
345 rollback to last_place;
346 benutils.rollback_cache;
347 --
348 update ben_person_actions
349 set action_status_cd = 'E'
350 where person_id = l_person_id
351 and benefit_action_id = p_benefit_action_id;
352 --
353 commit;
354 --
355 g_persons_errored := g_persons_errored + 1;
356 g_rec.ler_id := nvl(p_life_event_id,l_ler_thread.ler_id);
357 g_rec.rep_typ_cd := 'ERROR_LF';
358 -- g_rec.text := fnd_message.get;
359 g_rec.person_id := l_person_id;
360
361 g_rec.national_identifier := l_per_rec.national_identifier;
362 g_rec.error_message_code := benutils.get_message_name;
363 g_rec.text := fnd_message.get;
364
365 hr_utility.set_location('Error Message '||g_rec.text,10);
366 benutils.write(l_per_rec.full_name||' processed unsuccessfully');
367 benutils.write(g_rec.text);
368 benutils.write(p_rec => g_rec);
369 --
370 hr_utility.set_location('Max Errors = '||g_max_errors_allowed,10);
371 hr_utility.set_location('Num Errors = '||g_persons_errored,10);
372 if g_persons_errored > g_max_errors_allowed then
373 --
374 fnd_message.set_name('BEN','BEN_92431_BENBOCON_ERROR_LIMIT');
375 benutils.write(p_text => fnd_message.get);
376 --
377 raise;
378 --
379 end if;
380 --
381 end;
382 --
383 hr_utility.set_location('Closing c_person_thread',10);
384 --
385 end loop;
386 --
387 close c_person_thread;
388 --
389 -- Commit chunk
390 --
391 if p_validate = 'Y' then
392 --
393 hr_utility.set_location('Rolling back transaction ',10);
394 --
395 rollback;
396 --
397 end if;
398 --
399 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
400 fnd_message.set_token('PROC','benutils.write_table_and_file');
401 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
402 commit;
403 --
404 end loop;
405 --
406 fnd_message.set_name('BEN','BEN_91333_CALLING_PROC');
407 fnd_message.set_token('PROC','benbatch_utils.write_logfile');
408 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_processed
409 ,p_num_pers_errored => g_persons_errored);
410 --
411 commit;
412 --
413 hr_utility.set_location ('Leaving '||l_proc,70);
414 --
415 Exception
416 --
417 When others then
418 --
419 hr_utility.set_location('Super Error',10);
420 hr_utility.set_location(sqlerrm,10);
421 hr_utility.set_location('Super Error',10);
422 rollback;
423 benutils.rollback_cache;
424 --
425 g_rec.ler_id := nvl(p_life_event_id,l_ler_thread.ler_id);
426 g_rec.rep_typ_cd := 'FATAL';
427 g_rec.text := fnd_message.get;
428 g_rec.person_id := l_person_id;
429 --
430 benutils.write(p_text => g_rec.text);
431 benutils.write(p_rec => g_rec);
432 --
433 ben_batch_utils.write_logfile(p_num_pers_processed => g_persons_processed
434 ,p_num_pers_errored => g_persons_errored);
435 --
436 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
437 --
438 commit;
439 --
440 fnd_message.raise_error;
441 --
442 End do_multithread;
443 --
444 -- *************************************************************************
445 -- * << Procedure: Process >>
446 -- *************************************************************************
447 -- This is called from the concurrent manager
448
449 procedure process
450 (errbuf out nocopy varchar2
451 ,retcode out nocopy number
452 ,p_benefit_action_id in number default null
453 ,p_effective_date in varchar2
454 ,p_validate in varchar2 default 'N'
455 ,p_business_group_id in number
456 ,p_person_id in number
457 ,p_assignment_id in number
458 ,p_life_event_id in number
459 ,p_ocrd_date in varchar2
460 ,p_person_selection_rule_id in number default null
461 ,p_debug_messages in varchar2 default 'N'
462 ,p_bckt_stat_cd in varchar2 default 'VOIDD'
463 ) is
464
465 /* local variable defintions */
466 l_proc varchar2(80) := g_package||'.process';
467 l_request_id number;
468 l_benefit_action_id ben_benefit_actions.benefit_action_id%type;
469 l_object_version_number ben_benefit_actions.object_version_number%type;
470 l_person_id per_people_f.person_id%type;
471 l_person_action_id ben_person_actions.person_action_id%type;
472 l_ler_id ben_ler_f.ler_id%type;
473 l_range_id ben_batch_ranges.range_id%type;
474 l_chunk_size number := 20;
475 l_threads number := 1;
476 l_start_person_action_id number := 0;
477 l_end_person_action_id number := 0;
478 l_prev_person_id number := 0;
479 rl_ret char(1);
480 skip boolean;
481 l_person_cnt number := 0;
482 l_cnt number := 0;
483 l_num_range number := 0;
484 l_chunk_num number := 1;
485 l_num_row number := 0;
486 l_commit number;
487 --
488 l_effective_date date;
489 l_ocrd_date date;
490 l_no_one_to_process exception;
491 l_no_irec_ler exception;
492 l_business_group_id number;
493 --
494 l_person_selection number;
495 l_dummy number;
496
497 /* cursor definitions*/
498
499 cursor c_person is
500 select distinct ppf.person_id, ppf.business_group_id
501 from per_all_people_f ppf
502 where l_effective_date between ppf.effective_start_date and ppf.effective_end_date
503 and exists (select null
504 from ben_per_in_ler pil
505 , ben_ler_f ler
506 where pil.lf_evt_ocrd_dt = l_ocrd_date
507 and pil.ler_id = ler.ler_id
508 and l_effective_date between ler.effective_start_date
509 and ler.effective_end_date
510 and ler.business_group_id = p_business_group_id
511 and pil.ler_id = p_life_event_id
512 and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')
513 and pil.person_id = ppf.person_id
514 and pil.person_id = p_person_id
515 and pil.assignment_id = p_assignment_id
516 and ler.typ_cd ='IREC'
517 )
518 and exists ( select null from per_person_type_usages_f ptu,
519 per_person_types ppt, per_all_assignments_f apl_ass
520 where ppt.person_type_id = ptu.person_type_id
521 and ppt.system_person_type in( 'APL', 'APL_EX_APL','EMP_APL', 'EX_EMP_APL')
522 and ppt.business_group_id = ppf.business_group_id
523 and apl_ass.business_group_id = p_business_group_id
524 and ptu.person_id = ppf.person_id
525 and l_effective_date between ptu.effective_start_date
526 and ptu.effective_end_date
527 and apl_ass.person_id = ppf.person_id
528 and apl_ass.assignment_id = p_assignment_id
529 and apl_ass.assignment_type ='A'
530 and l_effective_date between apl_ass.effective_start_date
531 and apl_ass.effective_end_date
532 ) ;
533
534 cursor c_person_selection (cv_formula_id number
535 , cv_business_group_id number
536 , cv_effective_date date
537 ) is
538 select fff.formula_id
539 from ff_formulas_f fff,
540 ff_formulas_f fff1
541 where fff.business_group_id = cv_business_group_id
542 and cv_effective_date between fff.effective_start_date
543 and fff.effective_end_date
544 and fff.formula_name = fff1.formula_name
545 and cv_effective_date between fff1.effective_start_date
546 and fff1.effective_end_date
547 and fff1.formula_id = cv_formula_id;
548
549 cursor c_chk_ler(cv_effective_date date) is
550 select a.ler_id
551 from ben_ler_f a
552 where a.business_group_id = p_business_group_id
553 and a.typ_cd = 'IREC'
554 and a.ler_id = p_life_event_id
555 and cv_effective_date between a.effective_start_date and
556 nvl(a.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'));
557
558 begin
559 --
560 --hr_utility.trace_on(null, 'TRC');
561
562 hr_utility.set_location ('Entering '||l_proc,10);
563 --
564
565 hr_utility.set_location ('p_business_group_id '||p_business_group_id,10);
566 hr_utility.set_location ('p_life_event_id '||p_life_event_id,10);
567 hr_utility.set_location ('p_ocrd_date '||p_ocrd_date,10);
568 hr_utility.set_location ('p_assignment_id '||p_assignment_id,10);
569 hr_utility.set_location ('p_person_id '||p_person_id,10);
570 hr_utility.set_location ('p_person_selection_rule_id '||p_person_selection_rule_id,10);
571 --
572
573 --
574 l_effective_date:=trunc(fnd_date.canonical_to_date(p_effective_date));
575 l_ocrd_date:=trunc(fnd_date.canonical_to_date(p_ocrd_date));
576
577 --
578 dt_fndate.change_ses_date
579 (p_ses_date => l_effective_date,
580 p_commit => l_commit);
581
582 -- the following situation should never occur
583 -- but to ensure everything goes fine, we are adding this validation
584 open c_chk_ler(l_effective_date);
585 fetch c_chk_ler into l_dummy;
586 if c_chk_ler%notfound then
587 close c_chk_ler;
588 hr_utility.set_location ('The Passed LER ID is Wrong ',30);
589 fnd_message.set_name('BEN','BEN_PASSED_LER_ID_IS_WRONG');
590 raise l_no_irec_ler;
591
592 end if;
593 close c_chk_ler;
594
595
596 --
597 -- Get chunk_size and Thread values for multi-thread process.
598 --
599 ben_batch_utils.ini;
600 ben_batch_utils.ini(p_actn_cd => 'PROC_INFO');
601 --
602
603 benutils.get_parameter(p_business_group_id => p_business_group_id
604 ,p_batch_exe_cd => 'BENBOCON'
605 ,p_threads => l_threads
606 ,p_chunk_size => l_chunk_size
607 ,p_max_errors => g_max_errors_allowed);
608
609 --
610 -- Create benefit actions parameters in the benefit action table.
611 -- Do not create if a benefit action already exists, in other words
612 -- we are doing a restart.
613 --
614 -- Create benefit actions parameters in the benefit action table.
615 -- Do not create if a benefit action already exists, in other words
616 -- we are doing a restart.
617 --
618
619 If p_benefit_action_id is null then
620 --
621 ben_benefit_actions_api.create_benefit_actions
622 (p_validate => false
623 ,p_benefit_action_id => l_benefit_action_id
624 ,p_process_date => l_effective_date
625 ,p_mode_cd => 'S'
626 ,p_derivable_factors_flag => 'N'
627 ,p_validate_flag => p_validate
628 ,p_person_id => p_person_id
629 ,p_person_type_id => null
630 ,p_pgm_id => null
631 ,p_business_group_id => p_business_group_id
632 ,p_pl_typ_id => null
633 ,p_pl_id => null
634 ,p_popl_enrt_typ_cycl_id => null
635 ,p_no_programs_flag => 'N'
636 ,p_no_plans_flag => 'N'
637 ,p_comp_selection_rl => null
638 ,p_person_selection_rl => p_person_selection_rule_id
639 ,p_ler_id => p_life_event_id
640 ,p_organization_id => null
641 ,p_benfts_grp_id => null
642 ,p_location_id => null
643 ,p_pstl_zip_rng_id => p_assignment_id -- Note that we have reused
644 ,p_rptg_grp_id => null
645 ,p_opt_id => null
646 ,p_eligy_prfl_id => null
647 ,p_vrbl_rt_prfl_id => null
648 ,p_legal_entity_id => null
649 ,p_payroll_id => null
650 ,p_debug_messages_flag => p_debug_messages
651 ,p_object_version_number => l_object_version_number
652 ,p_effective_date => l_effective_date
653 ,p_request_id => fnd_global.conc_request_id
654 ,p_program_application_id => fnd_global.prog_appl_id
655 ,p_program_id => fnd_global.conc_program_id
656 ,p_program_update_date => sysdate
657 ,p_date_from => l_ocrd_date
658 ,p_uneai_effective_date => null);
659 --
660 benutils.g_benefit_action_id := l_benefit_action_id;
661 --
662 hr_utility.set_location ('l_benefit_action_id created is '||l_benefit_action_id,30);
663 -- Delete/clear ranges from ben_batch_ranges table
664 --
665 Delete from ben_batch_ranges
666 Where benefit_action_id = l_benefit_action_id;
667 --
668 -- Now lets create person actions for all the people we are going to
669 -- process in the Back-out life event run
670 --
671 open c_person;
672 --
673 l_person_cnt := 0;
674 l_cnt := 0;
675 --
676 loop
677 --
678 l_person_selection := null;
679 fetch c_person into l_person_id, l_business_group_id;
680 hr_utility.set_location ('next person selected is '||l_person_id,30);
681 exit when c_person%notfound;
682 --
683 l_cnt := l_cnt + 1;
684 --
685 skip := false;
686 --
687 If p_person_selection_rule_id is not NULL then
688 --
689 open c_person_selection (p_person_selection_rule_id, l_business_group_id, l_ocrd_date);
690 fetch c_person_selection into l_person_selection;
691 close c_person_selection;
692 --fnd_file.put_line(fnd_file.log,' l_business_group_id '||l_business_group_id||'l_person_selection' ||l_person_selection);
693
694 if l_person_selection is not null then
695 --
696 --
697 rl_ret := ben_batch_utils.person_selection_rule
698 (p_person_id => l_person_id
699 ,p_business_group_id => l_business_group_id
700 ,p_person_selection_rule_id=> l_person_selection--p_person_selection_rule_id
701 ,p_effective_date => l_effective_date);
702 --
703 If rl_ret = 'N' then
704 --
705 skip := TRUE;
706 --
707 End if;
708 --
709 else --l_person_selection is null --
710 skip := TRUE; --
711 end if; --
712
713 End if;
714 --
715 -- Store person_id into person actions table.
716 --
717 If (not skip) then
718 --
719 hr_utility.set_location ('person passed selection rule '||l_person_id,35);
720 Ben_person_actions_api.create_person_actions
721 (p_validate => false
722 ,p_person_action_id => l_person_action_id
723 ,p_person_id => l_person_id
724 ,p_ler_id => l_ler_id
725 ,p_benefit_action_id => l_benefit_action_id
726 ,p_action_status_cd => 'U'
727 ,p_chunk_number => l_chunk_num
728 ,p_object_version_number => l_object_version_number
729 ,p_effective_date => l_effective_date);
730 --
731 hr_utility.set_location ('person action created is '||l_person_action_id,40);
732 --
733 l_num_row := l_num_row + 1;
734 l_person_cnt := l_person_cnt + 1;
735 l_end_person_action_id := l_person_action_id;
736 --
737 If l_num_row = 1 then
738 --
739 l_start_person_action_id := l_person_action_id;
740 --
741 End if;
742 ----
743 If l_num_row = l_chunk_size then
744 --
745 -- Create a range of data to be multithreaded.
746 --
747 Ben_batch_ranges_api.create_batch_ranges
748 (p_validate => false
749 ,p_benefit_action_id => l_benefit_action_id
750 ,p_range_id => l_range_id
751 ,p_range_status_cd => 'U'
752 ,p_starting_person_action_id => l_start_person_action_id
753 ,p_ending_person_action_id => l_end_person_action_id
754 ,p_object_version_number => l_object_version_number
755 ,p_effective_date => l_effective_date);
756 --
757 hr_utility.set_location ('person action range created is '||l_range_id,45);
758 --
759 l_start_person_action_id := 0;
760 l_end_person_action_id := 0;
761 l_num_row := 0;
762 l_num_range := l_num_range + 1;
763 --
764 End if;
765 --
766 End if;
767 --
768 End loop;
769 --
770 close c_person;
771 --
772 --
773 hr_utility.set_location('l_num_row='||to_char(l_num_row),48);
774 --
775 If l_num_row <> 0 then
776 --
777 Ben_batch_ranges_api.create_batch_ranges
778 (p_validate => false
779 ,p_benefit_action_id => l_benefit_action_id
780 ,p_range_id => l_range_id
781 ,p_range_status_cd => 'U'
782 ,p_starting_person_action_id => l_start_person_action_id
783 ,p_ending_person_action_id => l_end_person_action_id
784 ,p_object_version_number => l_object_version_number
785 ,p_effective_date => l_effective_date);
786 --
787 l_num_range := l_num_range + 1;
788 --
789 hr_utility.set_location('l_num_row='||to_char(l_num_row),50);
790 hr_utility.set_location ('person action range created is '||l_range_id,55);
791 --
792 End if;
793 --
794 Else
795 --
796 l_benefit_action_id := p_benefit_action_id;
797 --
798 Ben_batch_utils.create_restart_person_actions
799 (p_benefit_action_id => p_benefit_action_id
800 ,p_effective_date => l_effective_date
801 ,p_chunk_size => l_chunk_size
802 ,p_threads => l_threads
803 ,p_num_ranges => l_num_range
804 ,p_num_persons => l_person_cnt);
805 --
806 End if;
807 --
808 hr_utility.set_location('l_num_range = '||to_char(l_num_range),50);
809 hr_utility.set_location('l_person_cnt = '||to_char(l_person_cnt),50);
810
811 If l_num_range > 1 then
812 --
813 For l_count in 1..least(l_threads,l_num_range)-1 loop
814 --
815 hr_utility.set_location('spawning thread #'||l_count,60);
816 --
817 l_request_id := fnd_request.submit_request
818 (application => 'BEN'
819 ,program => 'BEIRECBT' -- Currently not there
820 ,description => NULL
821 ,sub_request => FALSE
822 ,argument1 => p_validate
823 ,argument2 => l_benefit_action_id
824 ,argument3 => l_count
825 ,argument4 => p_effective_date
826 ,argument5 => p_business_group_id
827 ,argument6 => p_ocrd_date
828 ,argument7 => p_assignment_id
829 ,argument8 => p_life_event_id
830 ,argument9 => p_bckt_stat_cd
831 );
832 --
833 -- Store the request id of the concurrent request
834 --
835 ben_batch_utils.g_num_processes := ben_batch_utils.g_num_processes + 1;
836 ben_batch_utils.g_processes_tbl(ben_batch_utils.g_num_processes)
837 := l_request_id;
838 --
839 End loop;
840 --
841 commit;
842 --
843 Elsif (l_num_range = 0 ) then
844 --
845 Ben_batch_utils.print_parameters
846 (p_thread_id => 99
847 ,p_benefit_action_id => l_benefit_action_id
848 ,p_validate => p_validate
849 ,p_business_group_id => p_business_group_id
850 ,p_effective_date => l_effective_date
851 ,p_person_selection_rule_id => p_person_selection_rule_id
852 ,p_ler_id => p_life_event_id
853 ,p_organization_id => null
854 ,p_benfts_grp_id => null
855 ,p_location_id => null
856 ,p_legal_entity_id => null);
857 --
858 --
859 hr_utility.set_location('No person selected ',999);
860 -- hr_utility.trace_off;
861 fnd_message.set_name('BEN','BEN_91769_NOONE_TO_PROCESS');
862 fnd_message.set_token('PROC' , l_proc);
863 raise l_no_one_to_process;
864 --
865 End if;
866 --
867 do_multithread(errbuf => errbuf
868 ,retcode => retcode
869 ,p_validate => p_validate
870 ,p_benefit_action_id => l_benefit_action_id
871 ,p_thread_id => l_threads+1
872 ,p_effective_date => p_effective_date
873 ,p_business_group_id => p_business_group_id
874 ,p_person_id => p_person_id
875 ,p_ocrd_date => p_ocrd_date
876 ,p_assignment_id => p_assignment_id
877 ,p_life_event_id => p_life_event_id
878 ,p_bckt_stat_cd => p_bckt_stat_cd
879 );
880 --
881 hr_utility.set_location('waiting for slaves',65);
882 --
883 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
884 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
885 ,p_person_selected => l_person_cnt
886 ,p_business_group_id => p_business_group_id);
887 --
888 hr_utility.set_location('hurray my slaves are done',70);
889 --
890
891
892 -- THIS SHOULD NOT HAPPEN HERE.
893 savepoint data_change;
894
895 if p_validate = 'Y' then
896 hr_utility.set_location('Rolling back transaction ',10);
897 rollback to data_change;
898 end if;
899 --
900
901 hr_utility.set_location ('Leaving '||l_proc,75);
902 --
903 --hr_utility.trace_off;
904 Exception
905
906 when l_no_one_to_process then
907 benutils.write(p_text => fnd_message.get);
908 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
909
910 when l_no_irec_ler then
911 benutils.write(p_text => fnd_message.get);
912 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
913
914 when others then
915 --
916 hr_utility.set_location('Super Error',10);
917 rollback;
918 benutils.write(p_text => fnd_message.get);
919 benutils.write(p_text => sqlerrm);
920 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
921 If (l_num_range > 0) then
922 ben_batch_utils.check_all_slaves_finished(p_rpt_flag => TRUE);
923 ben_batch_utils.end_process(p_benefit_action_id => l_benefit_action_id
924 ,p_person_selected => l_person_cnt
925 ,p_business_group_id => p_business_group_id
926 ) ;
927 End if;
928 fnd_message.raise_error;
929 End process;
930 --
931 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
932 -- << Procedure: Restart >>
933 -- *****************************************************************
934 --
935 procedure restart (errbuf out nocopy varchar2
936 ,retcode out nocopy number
937 ,p_benefit_action_id in number) is
938 --
939 -- Cursor Declaration
940 --
941 cursor c_parameters is
942 Select process_date
943 ,mode_cd
944 ,validate_flag
945 ,business_group_id
946 ,person_selection_rl
947 ,ler_id
948 ,debug_messages_flag
949 ,date_from
950 ,ptnl_ler_for_per_stat_cd
951 ,person_id
952 , pstl_zip_rng_id -- using this for assignment id
953 -- ,pl_id
954 From ben_benefit_actions ben
955 Where ben.benefit_action_id = p_benefit_action_id;
956 --
957 -- Local Variable declaration.
958 --
959 l_proc varchar2(80) := g_package||'.restart';
960 l_parameters c_parameters%rowtype;
961 l_errbuf varchar2(80);
962 l_retcode number;
963 --
964 Begin
965 --
966 hr_utility.set_location ('Entering '||l_proc,10);
967 --
968 -- get the parameters for a previous run and do a restart
969 --
970 open c_parameters;
971 --
972 fetch c_parameters into l_parameters;
973 If c_parameters%notfound then
974 --
975 fnd_message.set_name('BEN','BEN_91710_RESTRT_PARMS_NOT_FND');
976 fnd_message.raise_error;
977 --
978 End if;
979 --
980 close c_parameters;
981 --
982 -- Call process procedure with parameters for restart
983 --
984 process(errbuf => l_errbuf
985 ,retcode => l_retcode
986 ,p_benefit_action_id => p_benefit_action_id
987 ,p_effective_date => fnd_date.date_to_canonical
988 (l_parameters.process_date)
989 ,p_validate => l_parameters.validate_flag
990 ,p_business_group_id => l_parameters.business_group_id
991 ,p_person_id => l_parameters.person_id
992 ,p_life_event_id => l_parameters.ler_id
993 ,p_ocrd_date => fnd_date.date_to_canonical
994 (l_parameters.date_from)
995 ,p_assignment_id => l_parameters.pstl_zip_rng_id
996 ,p_person_selection_rule_id => l_parameters.person_selection_rl
997 ,p_debug_messages => l_parameters.debug_messages_flag);
998 --
999 hr_utility.set_location ('Leaving '||l_proc,70);
1000 --
1001 end restart;
1002 --
1003 --
1004 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1005 -- << Procedure: p_back_out_irec_le >>
1006 -- *****************************************************************
1007 -- this procedure is a self-service wrapper or called from BENDSPLE
1008 -- to backout irec LE
1009 --
1010 procedure p_back_out_irec_le
1011 (p_per_in_ler_id in number,
1012 p_bckt_stat_cd in varchar2 default 'VOIDD',
1013 p_business_group_id in number,
1014 p_effective_date in date) is
1015
1016 cursor c_get_le_det is
1017 select ler_id, to_char(lf_evt_ocrd_dt,'YYYY/MM/DD HH24:MI:SS'), person_id, assignment_id
1018 from ben_per_in_ler pil
1019 where pil.per_in_ler_id = p_per_in_ler_id
1020 and pil.business_group_id = p_business_group_id
1021 and pil.lf_evt_ocrd_dt <= p_effective_date;
1022 --
1023 -- Local Variable declaration.
1024 --
1025 l_proc varchar2(80) := g_package||'.p_back_out_irec_le';
1026 l_errbuf varchar2(80);
1027 l_retcode number;
1028 l_ler_id number;
1029 l_person_id number;
1030 l_assignment_id number;
1031 l_lf_evt_ocrd_dt varchar2(30);
1032 l_effective_date varchar2(30) := to_char(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
1033 --
1034 begin
1035 --
1036 hr_utility.set_location ('Entering '||l_proc,10);
1037 --
1038 -- Get all the PIL Details
1039 --
1040 open c_get_le_det;
1041 fetch c_get_le_det into l_ler_id, l_lf_evt_ocrd_dt, l_person_id, l_assignment_id;
1042 if c_get_le_det%found then
1043 close c_get_le_det;
1044 l_effective_date := to_char(p_effective_date,'YYYY/MM/DD HH24:MI:SS');
1045
1046 -- Submit the backout process
1047 ben_irec_back_out_conc.process(
1048 errbuf => l_errbuf
1049 ,retcode => l_retcode
1050 ,p_effective_date => l_effective_date
1051 ,p_validate => 'N'
1052 ,p_business_group_id => p_business_group_id
1053 ,p_person_id => l_person_id
1054 ,p_life_event_id => l_ler_id
1055 ,p_ocrd_date => l_lf_evt_ocrd_dt
1056 ,p_assignment_id => l_assignment_id
1057 , p_bckt_stat_cd => 'VOIDD'
1058 ,p_debug_messages => 'N');
1059 else
1060 close c_get_le_det;
1061 hr_utility.set_location ('PIL Details Not found '||l_proc,70);
1062 end if;
1063 hr_utility.set_location ('Leaving '||l_proc,70);
1064 --
1065 end p_back_out_irec_le;
1066 --
1067 --
1068 end ben_irec_back_out_conc;
1069 --