[Home] [Help]
PACKAGE BODY: APPS.BEN_REOPEN_LER_CONC
Source
1 PACKAGE BODY ben_reopen_ler_conc AS
2 /* $Header: benrecon.pkb 120.4.12000000.1 2007/07/12 10:08:18 gsehgal noship $ */
3 /*
4 --------------------------------------------------------------------------------
5 rem Name
6 rem Reopen Life Event Process
7 rem Purpose
8 rem This package is used to reopen latest life event for all the persons
9 rem --------------------------------------------------------------------------------
10 rem History
11 rem -------
12 rem Version Date Author Comment
13 rem -------+----------+----------+------------------------------------------------
14 rem 115.0 8/9/2006 gsehgal Created.
15 rem 115.2 8/9/2006 nhunur person selection rule changes.
16 rem 115.3 10/3/2006 gsehgal SSN no was not printed and from date parameter
17 rem was not displayed
18 rem 115.4 10/13/2006 gsehgal Bug: 5589226. Process was erroring out when
19 rem no persons were selected.
20 ------------------------------------------------------------------------------------
21 ------------------------------------------------------------------------------------
22 */
23
24
25 --
26 -- global variables
27 g_package VARCHAR2 (80) := 'ben_reopen_ler_conc';
28 g_max_person_err NUMBER := 100;
29 g_persons_errored NUMBER := 0;
30 g_persons_procd NUMBER := 0;
31 g_cache_per_proc g_cache_person_process_rec;
32 l_pend_approvals BOOLEAN;
33 --
34 -- this main process is called for the executable(BENROLER) defined for "Reopen Life Events" Concurrent Program
35 --
36 PROCEDURE process (
37 errbuf OUT NOCOPY VARCHAR2,
38 retcode OUT NOCOPY NUMBER,
39 p_benefit_action_id IN NUMBER,
40 p_effective_date IN VARCHAR2,
41 p_validate IN VARCHAR2 DEFAULT 'N',
42 p_business_group_id IN NUMBER,
43 p_ler_id IN NUMBER DEFAULT NULL,
44 p_from_ocrd_date IN VARCHAR2 DEFAULT NULL,
45 p_organization_id IN NUMBER DEFAULT NULL,
46 p_location_id IN NUMBER DEFAULT NULL,
47 p_benfts_grp_id IN NUMBER DEFAULT NULL,
48 p_legal_entity_id IN NUMBER DEFAULT NULL,
49 p_person_selection_rl IN NUMBER DEFAULT NULL,
50 p_debug_messages IN VARCHAR2 DEFAULT 'N'
51 )
52 IS
53 --
54 -- Local variable declaration.
55 --
56 l_proc VARCHAR2 (100) := g_package || '.process';
57 l_effective_date DATE;
58 l_person_ok VARCHAR2 (30) := 'Y';
59 l_person_actn_cnt NUMBER (15) := 0;
60 l_start_person_actn_id NUMBER (15);
61 l_end_person_actn_id NUMBER (15);
62 l_object_version_number NUMBER (15);
63 l_datetrack_mode VARCHAR2 (80);
64 l_actn VARCHAR2 (80);
65 l_request_id NUMBER (15);
66 l_benefit_action_id NUMBER (15);
67 l_person_id NUMBER (15);
68 l_person_action_id NUMBER (15);
69 l_ler_id NUMBER (15);
70 l_range_id NUMBER (15);
71 l_chunk_size NUMBER := 20;
72 l_chunk_num NUMBER := 1;
73 l_threads NUMBER (5) := 1;
74 l_step NUMBER := 0;
75 l_num_ranges NUMBER := 0;
76 l_from_ocrd_date DATE;
77 l_commit NUMBER;
78 -- Exceptions
79 l_no_one_to_process EXCEPTION;
80 l_err_message varchar2(2000);
81 --
82 -- curosrs
83 --
84 -- this cursor will fetch all the processed life events with the ler id
85 -- given from ben_per_in_ler. We will to check whether this life event is
86 -- latest or not at the time reopening life event api
87 CURSOR c_pil
88 IS
89 SELECT per.person_id, pil.per_in_ler_id
90 FROM per_all_people_f per, ben_per_in_ler pil, ben_ler_f ler
91 WHERE pil.person_id = per.person_id
92 AND pil.per_in_ler_stat_cd = 'PROCD'
93 AND pil.ler_id = p_ler_id
94 AND pil.ler_id = ler.ler_id
95 AND l_effective_date BETWEEN ler.effective_start_date
96 AND ler.effective_end_date
97 AND l_effective_date BETWEEN per.effective_start_date
98 AND per.effective_end_date
99 AND ler.typ_cd NOT IN ('GSP', 'COMP', 'SCHEDDU', 'ABS', 'IREC')
100 AND pil.lf_evt_ocrd_dt >= l_from_ocrd_date
101 AND ( p_organization_id IS NULL
102 OR EXISTS (
103 SELECT NULL
104 FROM per_all_assignments_f paa
105 WHERE paa.person_id = per.person_id
106 AND l_effective_date
107 BETWEEN paa.effective_start_date
108 AND paa.effective_end_date
109 AND paa.business_group_id = per.business_group_id
110 AND paa.primary_flag = 'Y'
111 AND paa.organization_id = p_organization_id)
112 )
113 AND ( p_location_id IS NULL
114 OR EXISTS (
115 SELECT NULL
116 FROM per_all_assignments_f paa
117 WHERE paa.person_id = per.person_id
118 AND l_effective_date
119 BETWEEN paa.effective_start_date
120 AND paa.effective_end_date
121 AND paa.business_group_id = per.business_group_id
122 AND paa.primary_flag = 'Y'
123 AND paa.location_id = p_location_id)
124 )
125 AND ( p_benfts_grp_id IS NULL
126 OR EXISTS (
127 SELECT NULL
128 FROM per_all_people_f pap
129 WHERE pap.person_id = per.person_id
130 AND pap.business_group_id = per.business_group_id
131 AND l_effective_date
132 BETWEEN pap.effective_start_date
133 AND pap.effective_end_date
134 AND pap.benefit_group_id = p_benfts_grp_id)
135 )
136 AND ( p_legal_entity_id IS NULL
137 OR EXISTS (
138 SELECT NULL
139 FROM per_assignments_f paf,
140 hr_soft_coding_keyflex soft
141 WHERE paf.person_id = per.person_id
142 AND paf.assignment_type <> 'C'
143 AND l_effective_date
144 BETWEEN paf.effective_start_date
145 AND paf.effective_end_date
146 AND paf.business_group_id = per.business_group_id
147 AND paf.primary_flag = 'Y'
148 AND soft.soft_coding_keyflex_id =
149 paf.soft_coding_keyflex_id
150 AND soft.segment1 = TO_CHAR (p_legal_entity_id))
151 )
152 ORDER BY pil.person_id ASC, pil.lf_evt_ocrd_dt DESC;
153 --
154 -- Type declarations
155 --
156 --
157 BEGIN
158 -- hr_utility.trace_on (NULL, 'ORACLE');
159 hr_utility.set_location ('Entering ' || l_proc, 10);
160 -- changing in date format
161 hr_utility.set_location ('Changing date formats ', 20);
162 l_effective_date :=
163 TRUNC (fnd_date.canonical_to_date (p_effective_date));
164 l_from_ocrd_date :=
165 TRUNC (fnd_date.canonical_to_date (p_from_ocrd_date));
166 --
167 -- Put row in fnd_sessions
168 --
169 dt_fndate.change_ses_date (p_ses_date => l_effective_date,
170 p_commit => l_commit
171 );
172 hr_utility.set_location ('Created fnd session ', 30);
173 --
174 -- Check business rules and mandatory parameters
175 -- as effective date, ler_id and from occured date are mandatory
176 hr_api.mandatory_arg_error (p_api_name => l_proc,
177 p_argument => 'p_effective_date',
178 p_argument_value => p_effective_date
179 );
180 --
181 hr_api.mandatory_arg_error (p_api_name => l_proc,
182 p_argument => 'p_ler_id',
183 p_argument_value => p_ler_id
184 );
185 --
186 hr_api.mandatory_arg_error (p_api_name => l_proc,
187 p_argument => 'p_from_ocrd_date',
188 p_argument_value => p_from_ocrd_date
189 );
190 hr_utility.set_location ('Checked mandatory checks ', 20);
191 --
192 --
193 -- Initialize the batch process.
194 --
195 ben_batch_utils.ini (p_actn_cd => 'PROC_INFO');
196 --
197 -- Get the parameters defined for the batch process.
198 --
199 benutils.get_parameter (p_business_group_id => p_business_group_id,
200 p_batch_exe_cd => 'BENROLER',
201 p_threads => l_threads,
202 p_chunk_size => l_chunk_size,
203 p_max_errors => g_max_person_err
204 );
205 hr_utility.set_location ('l_chunk_size ' || TO_CHAR (l_chunk_size),70);
206 hr_utility.set_location ('l_threads ' || TO_CHAR (l_threads),70);
207
208 --
209 -- If p_benefit_action_id is null then this is a new batch process. Create the
210 -- batch ranges and person actions. Else restart using the benefit_action_id.
211 --
212 --
213 IF p_benefit_action_id IS NULL
214 THEN
215 --
216 ben_benefit_actions_api.create_benefit_actions
217 (p_validate => FALSE,
218 p_benefit_action_id => l_benefit_action_id,
219 p_process_date => l_effective_date,
220 p_mode_cd => 'S',
221 p_derivable_factors_flag => 'N',
222 p_validate_flag => p_validate,
223 p_business_group_id => p_business_group_id,
224 p_no_programs_flag => 'N',
225 p_no_plans_flag => 'N',
226 p_person_selection_rl => p_person_selection_rl,
227 p_ler_id => p_ler_id,
228 p_organization_id => p_organization_id,
229 p_benfts_grp_id => p_benfts_grp_id,
230 p_location_id => p_location_id,
231 p_legal_entity_id => p_legal_entity_id,
232 p_debug_messages_flag => p_debug_messages,
233 p_object_version_number => l_object_version_number,
234 p_effective_date => l_effective_date,
235 p_request_id => fnd_global.conc_request_id,
236 p_program_application_id => fnd_global.prog_appl_id,
237 p_program_id => fnd_global.conc_program_id,
238 p_program_update_date => SYSDATE,
239 p_date_from => l_from_ocrd_date
240 );
241 --
242 benutils.g_benefit_action_id := l_benefit_action_id;
243 --
244 benutils.g_thread_id := 99;
245 --
246 l_actn := 'Removing batch ranges ';
247
248 --
249 DELETE FROM ben_batch_ranges
250 WHERE benefit_action_id = l_benefit_action_id;
251
252 --
253 -- Loop through rows in ben_per_in_ler_f based on the parameters passed and
254 -- create person actions for the selected people.
255 --
256 FOR l_rec IN c_pil
257 LOOP
258 --
259 -- set variables for this iteration
260 --
261 hr_utility.set_location('processing from c_pil for person_id: '|| TO_CHAR (l_rec.person_id),40);
262 l_person_ok := 'Y';
263 --
264 -- Check the person selection rule.
265 --
266 If p_person_selection_rl is not NULL then
267 --
268 ben_batch_utils.person_selection_rule
269 (p_person_id => l_rec.person_id
270 ,p_business_group_id => p_business_group_id
271 ,p_person_selection_rule_id=> p_person_selection_rl
272 ,p_effective_date => l_effective_date
273 ,p_return => l_person_ok
274 ,p_err_message => l_err_message );
275
276 if l_err_message is not null
277 then
278 Ben_batch_utils.write(p_text =>
279 '<< Person id : '||to_char(l_person_id)||' failed.'||
280 ' Reason : '|| l_err_message ||' >>' );
281 l_err_message := NULL ;
282 end if ;
283 --
284 End if;
285
286 IF l_person_ok = 'Y'
287 THEN
288 --
289 -- Either no person sel rule or person selection rule passed. Create a
290 -- person action row.
291 --
292 ben_person_actions_api.create_person_actions
293 (p_validate => FALSE,
294 p_person_action_id => l_person_action_id,
295 p_person_id => l_rec.person_id,
296 p_ler_id => l_rec.per_in_ler_id,
297 p_benefit_action_id => l_benefit_action_id,
298 p_action_status_cd => 'U',
299 p_chunk_number => l_chunk_num,
300 p_object_version_number => l_object_version_number,
301 p_effective_date => l_effective_date
302 );
303 --
304 -- increment the person action count
305 --
306 l_person_actn_cnt := l_person_actn_cnt + 1;
307 --
308 -- Set the ending person action id to the last person action id that got
309 -- created
310 --
311 l_end_person_actn_id := l_person_action_id;
312
313 --
314 -- We have to create batch ranges based on the number of person actions
315 -- created and the chunk size defined for the batch process.
316 --
317 IF MOD (l_person_actn_cnt, l_chunk_size) = 1
318 OR l_chunk_size = 1
319 THEN
320 --
321 -- This is the first person action id in a new range.
322 --
323 l_start_person_actn_id := l_person_action_id;
324 --
325 END IF;
326
327 --
328 IF MOD (l_person_actn_cnt, l_chunk_size) = 0
329 OR l_chunk_size = 1
330 THEN
331 --
332 -- The number of person actions that got created equals the chunk
333 -- size. Create a batch range for the person actions.
334 --
335 hr_utility.set_location ('l_range_id: '||to_char(l_range_id),60);
336 hr_utility.set_location ('l_start_person_actn_id '||to_char(l_start_person_actn_id),60);
337 hr_utility.set_location ('l_end_person_actn_id '||to_char(l_end_person_actn_id),60);
338
339 ben_batch_ranges_api.create_batch_ranges
340 (p_validate => FALSE,
341 p_effective_date => l_effective_date,
342 p_benefit_action_id => l_benefit_action_id,
343 p_range_id => l_range_id,
344 p_range_status_cd => 'U',
345 p_starting_person_action_id => l_start_person_actn_id,
346 p_ending_person_action_id => l_end_person_actn_id,
347 p_object_version_number => l_object_version_number
348 );
349 --
350 l_num_ranges := l_num_ranges + 1;
351 l_chunk_num := l_chunk_num + 1;
352 --
353 END IF;
354 --
355 END IF;
356 --
357 END LOOP;
358 hr_utility.set_location ('l_num_ranges: ' || TO_CHAR (l_num_ranges),60);
359 hr_utility.set_location ('l_chunck_num ' || TO_CHAR (l_chunk_num),70);
360 --
361 -- There may be a few person actions left over from the loop above that may
362 -- not have got inserted into a batch range because the number was less than
363 -- the chunk size. Create a range for the remaining person actions. This
364 -- also applies when only one person gets selected.
365 --
366 IF l_person_actn_cnt > 0
367 AND MOD (l_person_actn_cnt, l_chunk_size) <> 0
368 THEN
369 --
370 ben_batch_ranges_api.create_batch_ranges
371 (p_validate => FALSE,
372 p_effective_date => l_effective_date,
373 p_benefit_action_id => l_benefit_action_id,
374 p_range_id => l_range_id,
375 p_range_status_cd => 'U',
376 p_starting_person_action_id => l_start_person_actn_id,
377 p_ending_person_action_id => l_end_person_actn_id,
378 p_object_version_number => l_object_version_number
379 );
380 --
381 l_num_ranges := l_num_ranges + 1;
382 --
383 END IF;
384 --
385 ELSE
386 --
387 -- Benefit action id is not null i.e. the batch process is being restarted
388 -- for a certain benefit action id. Create batch ranges and person actions
389 -- for restarting.
390 --
391 l_benefit_action_id := p_benefit_action_id;
392 --
393 hr_utility.set_location ( 'Restarting for benefit action id : '|| TO_CHAR (l_benefit_action_id),10);
394 --
395 ben_batch_utils.create_restart_person_actions
396 (p_benefit_action_id => p_benefit_action_id,
397 p_effective_date => l_effective_date,
398 p_chunk_size => l_chunk_size,
399 p_threads => l_threads,
400 p_num_ranges => l_num_ranges,
401 p_num_persons => l_person_actn_cnt
402 );
403 --
404 END IF;
405 --
406 COMMIT;
407 --
408 -- Submit requests to the concurrent manager based on the number of ranges
409 -- that got created.
410 --
411 IF l_num_ranges > 1
412 THEN
413 --
414 hr_utility.set_location ('More than one range got created.', 10);
415 --
416 --
417 -- Set the number of threads to the lesser of the defined number of threads
418 -- and the number of ranges created above. There's no point in submitting
419 -- 5 threads for only two ranges.
420 --
421 l_threads := LEAST (l_threads, l_num_ranges);
422
423 --
424 FOR l_count IN 1 .. (l_threads - 1)
425 LOOP
426 --
427 -- We are subtracting one from the number of threads because the main
428 -- process will act as the last thread and will be able to keep track of
429 -- the child requests that get submitted.
430 --
431 hr_utility.set_location ('Submitting request no: '|| TO_CHAR (l_count),10);
432 --
433 l_request_id :=
434 -- submitting the process for multi threading
435 fnd_request.submit_request (application => 'BEN',
436 program => 'BENROLERT',
437 description => NULL,
438 sub_request => FALSE,
439 argument1 => p_validate,
440 argument2 => l_benefit_action_id,
441 argument3 => p_effective_date,
442 argument4 => p_business_group_id,
443 argument5 => p_ler_id,
444 argument6 => l_count
445 );
446 --
447 -- Store the request id of the concurrent request
448 --
449 ben_batch_utils.g_num_processes :=
450 ben_batch_utils.g_num_processes + 1;
451 ben_batch_utils.g_processes_tbl (ben_batch_utils.g_num_processes) :=
452 l_request_id;
453 COMMIT;
454 --
455 END LOOP;
456 ELSIF (l_num_ranges = 0)
457 THEN
458 --
459 hr_utility.set_location ('l_num_ranges = 0 ', 50);
460 hr_utility.set_location ('p_validate ' || p_validate, 10);
461 ben_batch_utils.print_parameters
462 (p_thread_id => 99,
463 p_benefit_action_id => l_benefit_action_id,
464 p_validate => p_validate,
465 p_business_group_id => p_business_group_id,
466 p_effective_date => l_effective_date,
467 p_person_selection_rule_id => p_person_selection_rl,
468 p_ler_id => p_ler_id,
469 p_organization_id => p_organization_id,
470 p_benfts_grp_id => p_benfts_grp_id,
471 p_location_id => p_location_id,
472 p_legal_entity_id => p_legal_entity_id
473 );
474
475 --
476 -- bug: 5578779
477 ben_batch_utils.write (p_text =>'From Occured Date :'
478 || to_char(l_from_ocrd_date,'DD/MM/YYYY'));
479 --
480 fnd_message.set_name ('BEN', 'BEN_91769_NOONE_TO_PROCESS');
481 fnd_message.set_token ('PROC', l_proc);
482 -- changed bug: 5589226
483 RAISE l_no_one_to_process;
484 -- fnd_message.raise_error;
485 END IF;
486 --
487 -- Carry on with the master. This will ensure that the master finishes last.
488 --
489 hr_utility.set_location ('Submitting the master process', 10);
490 --
491 do_multithread (errbuf => errbuf,
492 retcode => retcode,
493 p_validate => p_validate,
494 p_benefit_action_id => l_benefit_action_id,
495 p_effective_date => p_effective_date,
496 p_business_group_id => p_business_group_id,
497 p_ler_id => p_ler_id,
498 p_thread_id => l_threads + 1
499 );
500
501 --
502 -- Check if all the slave processes are finished.
503 --
504 ben_batch_utils.check_all_slaves_finished (p_rpt_flag => TRUE);
505 --
506 -- End the process.
507 --
508 ben_batch_utils.end_process (p_benefit_action_id => l_benefit_action_id,
509 p_person_selected => l_person_actn_cnt,
510 p_business_group_id => p_business_group_id
511 );
512 --
513 -- Submit reports.
514 --
515 submit_all_reports;
516 --
517 hr_utility.set_location ('Leaving ' || l_proc, 10);
518 --
519 EXCEPTION
520 -- bug: 5589226
521 when l_no_one_to_process then
522 benutils.write(p_text => fnd_message.get);
523 benutils.write_table_and_file(p_table => TRUE, p_file => TRUE);
524 -- end
525 WHEN OTHERS
526 THEN
527 --
528 DECLARE
529 l_sqlerrm VARCHAR2 (100);
530 BEGIN
531 l_sqlerrm := SUBSTR (SQLERRM, 1, 100);
532 hr_utility.set_location ('Un identified Exception ', 80);
533 hr_utility.set_location (l_sqlerrm, 90);
534 END;
535
536 ben_batch_utils.rpt_error (p_proc => l_proc,
537 p_last_actn => l_actn,
538 p_rpt_flag => TRUE
539 );
540 --
541 benutils.WRITE (p_text => fnd_message.get);
542 benutils.WRITE (p_text => SQLERRM);
543 benutils.write_table_and_file (p_table => TRUE, p_file => TRUE);
544
545 --
546 IF l_num_ranges > 0
547 THEN
548 --
549 ben_batch_utils.check_all_slaves_finished (p_rpt_flag => TRUE);
550 --
551 ben_batch_utils.end_process
552 (p_benefit_action_id => l_benefit_action_id,
553 p_person_selected => l_person_actn_cnt,
554 p_business_group_id => p_business_group_id
555 );
556 --
557 submit_all_reports;
558 --
559 END IF;
560
561 --
562 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
563 fnd_message.set_token ('PROCEDURE', l_proc);
564 fnd_message.set_token ('STEP', l_actn);
565 fnd_message.raise_error;
566 --
567 END process;
568
569 ------------------------------------------------------------------------------
570 ------------------------------------------------------------------------------
571 ------------------------------------------------------------------------------
572 PROCEDURE do_multithread (
573 errbuf OUT NOCOPY VARCHAR2,
574 retcode OUT NOCOPY NUMBER,
575 p_validate IN VARCHAR2 DEFAULT 'N',
576 p_benefit_action_id IN NUMBER,
577 p_effective_date IN VARCHAR2,
578 p_business_group_id IN NUMBER,
579 p_ler_id IN NUMBER,
580 p_thread_id IN NUMBER
581 )
582 IS
583 --
584 -- Local variable declaration
585 --
586 l_proc VARCHAR2 (80)
587 := g_package || '.do_multithread';
588 l_person_id ben_person_actions.person_id%TYPE;
589 l_person_action_id ben_person_actions.person_action_id%TYPE;
590 l_object_version_number ben_person_actions.object_version_number%TYPE;
591 l_lf_evt_ocrd_dt DATE;
592 l_ler_id ben_person_actions.ler_id%TYPE;
593 l_range_id ben_batch_ranges.range_id%TYPE;
594 l_record_number NUMBER := 0;
595 l_start_person_action_id NUMBER := 0;
596 l_end_person_action_id NUMBER := 0;
597 l_actn VARCHAR2 (80);
598 l_cnt NUMBER (5) := 0;
599 l_chunk_size NUMBER (15);
600 l_threads NUMBER (15);
601 l_effective_date DATE;
602 l_from_ocrd_date DATE;
603 l_validate BOOLEAN := FALSE;
604 l_ler_name ben_ler_f.NAME%TYPE;
605 --
606 -- bug: 5578779
607 l_per_rec per_all_people_f%rowtype;
608 -- Cursors declaration
609 --
610 CURSOR c_range_thread
611 IS
612 -- to fetch all the ranges
613 SELECT ran.range_id, ran.starting_person_action_id,
614 ran.ending_person_action_id
615 FROM ben_batch_ranges ran
616 WHERE ran.range_status_cd = 'U'
617 AND ran.benefit_action_id = p_benefit_action_id
618 AND ROWNUM < 2
619 FOR UPDATE OF ran.range_status_cd;
620
621 --
622 CURSOR c_person_thread
623 IS
624 -- to fetch all the persons actions
625 SELECT ben.person_id, ben.person_action_id,
626 ben.object_version_number, ben.ler_id
627 FROM ben_person_actions ben
628 WHERE ben.benefit_action_id = p_benefit_action_id
629 AND ben.action_status_cd <> 'P'
630 AND ben.person_action_id BETWEEN l_start_person_action_id
631 AND l_end_person_action_id
632 ORDER BY ben.person_action_id;
633
634 --
635 CURSOR c_parameter
636 IS
637 -- fetch all the parameters of the process from ben_benefit actions
638 SELECT *
639 FROM ben_benefit_actions ben
640 WHERE ben.benefit_action_id = p_benefit_action_id;
641
642 --
643 CURSOR c_ler
644 IS
645 SELECT NAME
646 FROM ben_ler_f
647 WHERE ler_id = p_ler_id;
648
649 --
650 CURSOR c_per_in_ler (p_per_in_ler_id IN NUMBER)
651 IS
652 SELECT lf_evt_ocrd_dt, object_version_number
653 FROM ben_per_in_ler
654 WHERE per_in_ler_id = p_per_in_ler_id;
655
656 --
657 l_parm c_parameter%ROWTYPE;
658 l_commit NUMBER;
659 l_encoded_message VARCHAR2 (2000);
660 l_app_short_name VARCHAR2 (2000);
661 l_message_name VARCHAR2 (2000);
662 g_rec ben_type.g_report_rec;
663 --
664 BEGIN
665 hr_utility.set_location ('Entering ' || l_proc, 10);
666 l_effective_date :=
667 TRUNC (fnd_date.canonical_to_date (p_effective_date));
668 --
669 -- Put row in fnd_sessions
670 --
671 dt_fndate.change_ses_date (p_ses_date => l_effective_date,
672 p_commit => l_commit
673 );
674 --
675 OPEN c_ler;
676 LOOP
677 FETCH c_ler INTO l_ler_name;
678 EXIT WHEN c_ler%NOTFOUND;
679 END LOOP;
680 CLOSE c_ler;
681 --
682 l_actn := 'Calling benutils.get_parameter...';
683 benutils.get_parameter (p_business_group_id => p_business_group_id,
684 p_batch_exe_cd => 'BENROLER',
685 p_threads => l_threads,
686 p_chunk_size => l_chunk_size,
687 p_max_errors => g_max_person_err
688 );
689 --
690 -- Set up benefits environment
691 --
692 ben_env_object.init (p_business_group_id => p_business_group_id,
693 p_effective_date => l_effective_date,
694 p_thread_id => p_thread_id,
695 p_chunk_size => l_chunk_size,
696 p_threads => l_threads,
697 p_max_errors => g_max_person_err,
698 p_benefit_action_id => p_benefit_action_id
699 );
700 --
701 g_persons_procd := 0;
702 g_persons_errored := 0;
703 --
704 ben_batch_utils.ini;
705 --
706 benutils.g_benefit_action_id := p_benefit_action_id;
707 benutils.g_thread_id := p_thread_id;
708 --
709 -- Fetch the parameters defined for the batch process.
710 --
711 OPEN c_parameter;
712 FETCH c_parameter INTO l_parm;
713 CLOSE c_parameter;
714 --
715 IF p_validate = 'Y'
716 -- as he argument passed to reopen_single_life event
717 -- is boolean
718 THEN
719 l_validate := TRUE;
720 ELSE
721 l_validate := FALSE;
722 END IF;
723 --
724 -- Print the parameters to the log file.
725 --
726 hr_utility.set_location ('p_validate ' || p_validate, 10);
727 ben_batch_utils.print_parameters
728 (p_thread_id => p_thread_id,
729 p_benefit_action_id => p_benefit_action_id,
730 p_validate => p_validate,
731 p_business_group_id => p_business_group_id,
732 p_effective_date => l_effective_date,
733 p_person_selection_rule_id => l_parm.person_selection_rl,
734 p_organization_id => l_parm.organization_id,
735 p_benfts_grp_id => l_parm.benfts_grp_id,
736 p_location_id => l_parm.location_id,
737 p_legal_entity_id => l_parm.legal_entity_id,
738 p_ler_id => p_ler_id
739 );
740 -- bug: 5578779
741 ben_batch_utils.write (p_text =>'From Occured Date :'
742 || to_char(l_parm.date_from,'DD/MM/YYYY'));
743 --
744 LOOP
745 --
746 OPEN c_range_thread;
747 FETCH c_range_thread INTO l_range_id, l_start_person_action_id, l_end_person_action_id;
748 --
749 EXIT WHEN c_range_thread%NOTFOUND;
750 --
751 CLOSE c_range_thread;
752 --
753 -- Update the range status code to processed 'P'
754 --
755 UPDATE ben_batch_ranges ran
756 SET ran.range_status_cd = 'P'
757 WHERE ran.range_id = l_range_id;
758 --
759 hr_utility.set_location ('Updated range '|| TO_CHAR (l_range_id)|| ' status code to P',10);
760 --
761 COMMIT;
762 --
763 -- Remove all records from cache
764 --
765 g_cache_per_proc.DELETE;
766 --
767 OPEN c_person_thread;
768 --
769 l_record_number := 0;
770 --
771 hr_utility.set_location ('Load person actions into the cache', 10);
772 --
773 LOOP
774 --
775 FETCH c_person_thread
776 INTO g_cache_per_proc (l_record_number + 1).person_id,
777 g_cache_per_proc (l_record_number + 1).person_action_id,
778 g_cache_per_proc (l_record_number + 1).object_version_number,
779 g_cache_per_proc (l_record_number + 1).ler_id;
780 --
781 EXIT WHEN c_person_thread%NOTFOUND;
782 --
783 l_record_number := l_record_number + 1;
784 --
785 l_actn := 'Updating person_ations.';
786 --
787 UPDATE ben_person_actions
788 SET action_status_cd = 'T'
789 WHERE person_action_id = l_person_action_id;
790 --
791 END LOOP;
792 --
793 CLOSE c_person_thread;
794 --
795 COMMIT;
796 --
797 IF l_record_number > 0
798 THEN
799 --
800 FOR l_cnt IN 1 .. l_record_number
801 LOOP
802 --
803 IF g_persons_errored = g_max_person_err
804 THEN
805 fnd_message.set_name('BEN','BEN_94665_BENROLER_ERROR_LIMIT');
806 fnd_message.raise_error;
807 END IF;
808 hr_utility.set_location('Reopening Life event for '|| TO_CHAR(g_cache_per_proc (l_cnt).person_id),10);
809 --
810 hr_utility.set_location ('Printing person details ', 20);
811 -- Storing the value for a person
812 l_person_id := g_cache_per_proc (l_cnt).person_id;
813 ben_manage_life_events.person_header
814 (p_person_id => g_cache_per_proc(l_cnt).person_id,
815 p_business_group_id => p_business_group_id,
816 p_effective_date => l_effective_date
817 );
818 hr_utility.set_location ('Printed person header', 30);
819
820 BEGIN
821 hr_utility.set_location('Before Api call ',10);
822 OPEN c_per_in_ler (g_cache_per_proc (l_cnt).ler_id);
823 LOOP
824 FETCH c_per_in_ler
825 INTO l_lf_evt_ocrd_dt, l_object_version_number;
826 EXIT;
827 END LOOP;
828 CLOSE c_per_in_ler;
829 hr_utility.set_location ('Calling main proc', 10);
830 -- call the procedure for reopening here
831 ben_close_enrollment.reopen_single_life_event
832 (p_per_in_ler_id => g_cache_per_proc(l_cnt).ler_id,
833 p_person_id => g_cache_per_proc(l_cnt).person_id,
834 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
835 p_effective_date => l_effective_date,
836 p_business_group_id => p_business_group_id,
837 p_object_version_number => l_object_version_number,
838 p_validate => l_validate
839 );
840
841 UPDATE ben_person_actions
842 SET action_status_cd = 'P'
843 WHERE person_action_id = g_cache_per_proc (l_cnt).person_action_id;
844
845 fnd_message.set_name ('BEN', 'BEN_94646_LF_EVT_REOPENED');
846 fnd_message.set_token ('LIFE_EVENT', l_ler_name);
847 benutils.WRITE (p_text => fnd_message.get);
848 g_persons_procd := g_persons_procd + 1;
849 --
850 EXCEPTION
851 WHEN OTHERS
852 THEN
853 --- in the excption handler writing the errored person
854 --- in the log file along with error text
855 --
856 IF c_per_in_ler%ISOPEN
857 THEN
858 CLOSE c_per_in_ler;
859 END IF;
860
861 -- bug: 5578779
862 ben_person_object.get_object(p_person_id => g_cache_per_proc (l_cnt).person_id,
863 p_rec => l_per_rec);
864 g_rec.national_identifier := l_per_rec.national_identifier;
865 -- end 5578779
866 l_encoded_message := fnd_message.get_encoded;
867 fnd_message.parse_encoded
868 (encoded_message => l_encoded_message,
869 app_short_name => l_app_short_name,
870 message_name => l_message_name
871 );
872 fnd_message.set_encoded(encoded_message => l_encoded_message);
873 --
874 g_rec.text := fnd_message.get;
875 --
876 g_rec.error_message_code :=
877 NVL (l_message_name, NVL (g_rec.error_message_code, SQLCODE));
878 g_rec.text := NVL (g_rec.text,NVL (g_rec.text, SUBSTR (SQLERRM, 1, 400)));
879 g_rec.rep_typ_cd := 'ERROR';
880 g_rec.person_id := g_cache_per_proc (l_cnt).person_id;
881
882 benutils.WRITE (p_rec => g_rec);
883
884 UPDATE ben_person_actions
885 SET action_status_cd = 'E'
886 WHERE person_action_id = g_cache_per_proc(l_cnt).person_action_id;
887
888 g_persons_errored := g_persons_errored + 1;
889 /*
890 IF g_persons_errored = g_max_person_err
891 THEN
892 fnd_message.set_name('BEN','BEN_94665_BENROLER_ERROR_LIMIT');
893 fnd_message.raise_error;
894 END IF;
895 */
896 END;
897 --
898 END LOOP;
899 ELSE
900 --
901 hr_utility.set_location ('No records found. Erroring out.', 10);
902 --
903 l_actn := 'Reporting error since there is no record found';
904 --
905 fnd_message.set_name ('BEN', 'BEN_91906_PER_NOT_FND_IN_RNG');
906 fnd_message.set_token ('PROC', l_proc);
907 fnd_message.set_token ('BENEFIT_ACTION_ID',
908 TO_CHAR (p_benefit_action_id)
909 );
910 fnd_message.set_token ('BG_ID', TO_CHAR (p_business_group_id));
911 fnd_message.set_token ('EFFECTIVE_DATE', p_effective_date);
912 fnd_message.raise_error;
913 --
914 END IF;
915
916 --
917 benutils.write_table_and_file (p_table => TRUE, p_file => TRUE);
918 --
919 COMMIT;
920 --
921 END LOOP;
922
923 --
924 benutils.write_table_and_file (p_table => TRUE, p_file => TRUE);
925 --
926 COMMIT;
927 --
928 l_actn := 'Calling log_beneadeb_statistics...';
929 --
930 ben_batch_utils.write_logfile (p_num_pers_processed => g_persons_procd,
931 p_num_pers_errored => g_persons_errored
932 );
933 --
934 hr_utility.set_location ('Leaving ' || l_proc, 70);
935 --
936 EXCEPTION
937 --
938 WHEN OTHERS
939 THEN
940 --
941 ROLLBACK;
942 --
943 hr_utility.set_location ('BENROLER Super Error ' || l_proc, 10);
944 hr_utility.set_location (SQLERRM, 10);
945 benutils.rollback_cache;
946 g_rec.rep_typ_cd := 'FATAL';
947 g_rec.text := fnd_message.get;
948 g_rec.person_id := l_person_id;
949
950 benutils.Write(p_text => benutils.g_banner_minus);
951 benutils.WRITE (p_text => SQLERRM);
952 benutils.WRITE (p_rec => g_rec);
953 /*
954 ben_batch_utils.rpt_error (p_proc => l_proc,
955 p_last_actn => l_actn,
956 p_rpt_flag => TRUE
957 );
958 */
959 benutils.write_table_and_file (p_table => TRUE, p_file => TRUE);
960 --
961 ben_batch_utils.write_logfile
962 (p_num_pers_processed => g_persons_procd,
963 p_num_pers_errored => g_persons_errored
964 );
965 --
966 COMMIT;
967 --
968 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
969 fnd_message.set_token ('PROCEDURE', l_proc);
970 fnd_message.set_token ('STEP', l_actn);
971 fnd_message.raise_error;
972 --
973 END do_multithread;
974
975 --
976 PROCEDURE submit_all_reports (p_rpt_flag IN BOOLEAN DEFAULT FALSE)
977 IS
978 -- local variables
979 l_proc VARCHAR2 (80) := g_package || '.submit_all_reports';
980 l_actn VARCHAR2 (80);
981 l_request_id NUMBER;
982 --
983 BEGIN
984 --
985 hr_utility.set_location ('Entering ' || l_proc, 05);
986
987 --
988 IF fnd_global.conc_request_id <> -1
989 THEN
990 --
991 -- Submit the generic error by error type and error by person reports.
992 --
993 l_actn := 'ben_batch_reporting.batch_reports ERROR_BY_ERROR_TYPE...';
994 ben_batch_reporting.batch_reports
995 (p_concurrent_request_id => fnd_global.conc_request_id,
996 p_report_type => 'ERROR_BY_ERROR_TYPE'
997 );
998 --
999 l_actn := 'ben_batch_reporting.batch_reports ERROR_BY_PERSON...';
1000 ben_batch_reporting.batch_reports
1001 (p_concurrent_request_id => fnd_global.conc_request_id,
1002 p_report_type => 'ERROR_BY_PERSON'
1003 );
1004 --
1005 END IF;
1006
1007 --
1008 hr_utility.set_location ('Leaving ' || l_proc, 10);
1009 --
1010 EXCEPTION
1011 --
1012 WHEN OTHERS
1013 THEN
1014 --
1015 ben_batch_utils.rpt_error (p_proc => l_proc,
1016 p_last_actn => l_actn,
1017 p_rpt_flag => p_rpt_flag
1018 );
1019 --
1020 RAISE;
1021 --
1022 --
1023 END submit_all_reports;
1024 END ben_reopen_ler_conc; -- end package body