1 package body hr_data_pump as
2 /* $Header: hrdpump.pkb 120.8 2006/01/25 06:03:08 arashid noship $ */
3 /*
4 NOTES
5 o This package body contains the Data Pump engine code.
6 o For documentation, see the following:
7 - hld/hrdpump.lld : low level design document.
8 - txt/hrdsche.txt : schema description document.
9 *** These documents should be kept in step with the code ***
10 */
11 /*---------------------------------------------------------------------------*/
12 /*----------------------- constant definitions ------------------------------*/
13 /*---------------------------------------------------------------------------*/
14 PURGE_MIN_ROWS constant binary_integer := 2000;
15 PURGE_SIZE_DEFAULT constant binary_integer := 500;
16 RANGE_SIZE_DEFAULT constant binary_integer := 10;
17 THREADS_DEFAULT constant binary_integer := 1;
18 ERRORS_DEFAULT constant binary_integer := 20;
19 DEBUG_PAGES constant binary_integer := 20;
20
21 /*---------------------------------------------------------------------------*/
22 /*------------- internal Data Pump engine data structures -------------------*/
23 /*---------------------------------------------------------------------------*/
24 /*
25 * The following record holds 'startup' information that will
26 * be passed to all the internal master procedures. This includes
27 * information about number of slaves to start, range size and
28 * so on. Collected into one place so it's easy to change.
29 */
30 type master_env_r is record
31 (
32 business_group_id number,
33 security_group_id number,
34 range_size binary_integer,
35 threads binary_integer,
36 error_limit binary_integer
37 ,pap_group_id number
38 );
39
40 /*
44 * procedures. May well be useful for debug output.
41 * This record holds information about a particular
42 * slave process. All held in one structure so it
43 * can be passed through the different slave
45 */
46 type slave_info_r is record
47 (
48 lines_proc number, -- number of batch lines processed.
49 wrap_total number, -- number of wrappers called.
50 wcachehit number, -- number of times hit the cache.
51 errortotal number, -- total number of errors encountered.
52 rangetotal number, -- total number of ranges processed.
53 fail_bline number, -- batch line processed at failure.
54 single_threaded boolean -- running in single threaded mode.
55 );
56
57 /*
58 * The following holds information about
59 * the range of rows we are trying to
60 * insert for parallelisation.
61 */
62 type range_info_r is record
63 (
64 rows_in_range number,
65 range_number number,
66 range_start number,
67 range_end number
68 );
69
70 /*
71 * The following data structure holds information for
72 * the API wrapper modules. This was primarily a cache
73 * for DBMS_SQL cursors. It is now used to cache the
74 * SQL call string.
75 * Note that in this first version, no upper limit is
76 * being put on the size of the cache.
77 * This might need alteration in future versions.
78 * There is more information held here than is strictly
79 * necessary for normal functioning. Other information
80 * is for logging and debugging purposes.
81 */
82 type wrap_cache_r is record
83 (
84 api_module_id number,
85 call_string varchar2(2000)
86 );
87
88 type wrap_cache_t is table of wrap_cache_r index by binary_integer;
89
90 /*
91 * Debug information.
92 */
93
94 type debug_info_r is record
95 (
96 message_dbg boolean, /* allow specific log messages */
97 call_trace_dbg boolean, /* entry and exit macros */
98 wrap_cache_dbg boolean, /* wrap cache information dump */
99 api_module_dbg boolean, /* api module trace info */
100 stack_dump_dbg boolean, /* dump information on total failure */
101 exit_info_dbg boolean, /* information on exit (success) */
102 range_ins_dbg boolean, /* information about range inserts */
103 range_proc_dbg boolean, /* info on processing ranges */
104 get_id_dbg boolean, /* log info from failing get_id functions */
105 conc_file_dbg boolean, /* send messages to concurrent log file */
106 batch_line_info boolean /* show batch_line information */
107 );
108
109 /*
110 * Holds some information about failures occurring
111 * in get_id functions.
112 */
113
114 type fail_info_r is record
115 (
116 fail_flag boolean,
117 func_name varchar2(30),
118 error_msg varchar2(2000),
119 arg_values varchar2(2000)
120 );
121
122 /*
123 * Types for different fetching and locking from HR_PUMP_RANGES. In the
124 * single-threaded case, an ORDER BY statement is used, but not in the
125 * multi-threaded case.
126 */
127 type range_fetch_info_r is record
128 (rowid_ urowid
129 ,starting_process_sequence number
130 ,ending_process_sequence number
131 );
132
133 type range_fetch_cursor_t is ref cursor return range_fetch_info_r;
134 /*---------------------------------------------------------------------------*/
135 /*----------------------- Data Pump engine globals --------------------------*/
136 /*---------------------------------------------------------------------------*/
137 g_wrapper_cache wrap_cache_t; -- holds API wrapper cache info.
138 g_debug debug_info_r; -- debug flags.
139 g_senv slave_info_r; -- slave environment.
140 g_fail_info fail_info_r; -- for special info messages.
141
142 /*
143 * Procedure to get action parameter values.
144 */
145 procedure get_action_parameter
146 (p_para_name in varchar2
147 ,p_para_value out nocopy varchar2
148 ,p_found out nocopy boolean
149 ) is
150 begin
151 --
152 --
153 -- Ideally, the code should directly call
154 -- pay_core_utils.get_action_parameter, but that restricts the
155 -- back-portability of this code as it's an HRMS FP.E feature.
156 --
157 select parameter_value
158 into p_para_value
159 from pay_action_parameters
160 where parameter_name = p_para_name;
161 --
162 p_found := TRUE;
163 --
164 exception
165 when no_data_found then
166 p_found := FALSE;
167 --
168 end get_action_parameter;
169
170
171 /*---------------------------------------------------------------------------*/
172 /*------------------ local functions and procedures -------------------------*/
173 /*---------------------------------------------------------------------------*/
174
175 /*
176 * header_core
177 * Core code to fetch header information.
178 */
179 procedure header_core
180 (p_batch_id in number
181 ,p_batch_name out nocopy varchar2
182 ,p_business_group_id out nocopy number
183 ,p_business_group_name out nocopy varchar2
184 ,p_security_group_id out nocopy number
185 ,p_batch_status out nocopy varchar2
186 ,p_atomic_linked_calls out nocopy varchar2
187 ) is
188 begin
189 --
193 -- name.
190 -- Get some information from the batch header. This includes the
191 -- business_group_id and the current batch status. The outer join is
192 -- necessary because the header does not have to have a business group
194 --
195 select grp.business_group_id
196 , grp.security_group_id
197 , pbh.batch_name
198 , pbh.batch_status
199 , pbh.business_group_name
200 , nvl(upper(pbh.atomic_linked_calls), 'N')
201 into p_business_group_id
202 , p_security_group_id
203 , p_batch_name
204 , p_batch_status
205 , p_business_group_name
206 , p_atomic_linked_calls
207 from per_business_groups_perf grp
208 , hr_pump_batch_headers pbh
209 where pbh.batch_id = p_batch_id
210 and grp.name (+) = pbh.business_group_name
211 for update of pbh.batch_status
212 ;
213 end header_core;
214
215 /*
216 * headerTAS - header Test And Set
217 * Lock the batch header and set the batch status to 'P' if the header is
218 * not already processing.
219 */
220 procedure headerTAS
221 (p_batch_id in number
222 ,p_batch_name out nocopy varchar2
223 ,p_business_group_id out nocopy number
224 ,p_business_group_name out nocopy varchar2
225 ,p_security_group_id out nocopy number
226 ,p_batch_status out nocopy varchar2
227 ) is
228 l_batch_status varchar2(30);
229 l_atomic_linked_calls varchar2(30);
230 begin
231 savepoint headerTAS;
232 --
233 header_core
234 (p_batch_id => p_batch_id
235 ,p_batch_name => p_batch_name
236 ,p_business_group_id => p_business_group_id
237 ,p_business_group_name => p_business_group_name
238 ,p_security_group_id => p_security_group_id
239 ,p_batch_status => l_batch_status
240 ,p_atomic_linked_calls => l_atomic_linked_calls
241 );
242
243 --
244 -- Is the batch already processing ?
245 --
246 p_batch_status := l_batch_status;
247 if l_batch_status = 'P' then
248 --
249 -- Somebody else is processing the batch.
250 --
251 rollback to headerTAS;
252 else
253 --
254 -- Set the header status to processing.
255 --
256 update hr_pump_batch_headers h
257 set h.batch_status = 'P'
258 where h.batch_id = p_batch_id;
259 --
260 commit;
261 end if;
262
263 exception
264 when others then
265 rollback to headerTAS;
266 raise;
267 end headerTAS;
268
269 /*
270 * header_read
271 * Read information from the batch header and error if the batch does not exist.
272 */
273 procedure header_read
274 (p_batch_id in number
275 ,p_atomic_linked_calls out nocopy boolean
276 ) is
277 l_batch_name hr_pump_batch_headers.batch_name%type;
278 l_business_group_id number;
279 l_business_group_name hr_pump_batch_headers.batch_name%type;
280 l_security_group_id number;
281 l_batch_status hr_pump_batch_headers.batch_status%type;
282 l_atomic_linked_calls hr_pump_batch_headers.atomic_linked_calls%type;
283 begin
284 --
285 header_core
286 (p_batch_id => p_batch_id
287 ,p_batch_name => l_batch_name
288 ,p_business_group_id => l_business_group_id
289 ,p_business_group_name => l_business_group_name
290 ,p_security_group_id => l_security_group_id
291 ,p_batch_status => l_batch_status
292 ,p_atomic_linked_calls => l_atomic_linked_calls
293 );
294
295 p_atomic_linked_calls := (l_atomic_linked_calls = 'Y');
296 exception
297 when no_data_found then
298 hr_utility.set_message(800, 'HR_33798_DP_BATCH_NOT_FOUND');
299 hr_utility.set_message_token('BATCH_ID', to_char(p_batch_id));
300 hr_utility.raise_error;
301 end header_read;
302
303 procedure fail
304 (
305 p_function_name in varchar2,
306 p_error_message in varchar2,
307 p_arg01 in varchar2 default null,
308 p_arg02 in varchar2 default null,
309 p_arg03 in varchar2 default null,
310 p_arg04 in varchar2 default null,
311 p_arg05 in varchar2 default null,
312 p_arg06 in varchar2 default null,
313 p_arg07 in varchar2 default null,
314 p_arg08 in varchar2 default null
315 ) is
316 begin
317 g_fail_info.fail_flag := TRUE; -- Indicate failure has occured.
318 g_fail_info.func_name := p_function_name;
319 g_fail_info.error_msg := p_error_message;
320
321 -- Deal with logging of the argument values.
322 g_fail_info.arg_values := NULL;
323 if(p_arg01 is not null) then
324 g_fail_info.arg_values := p_arg01;
325 end if;
326
327 if(p_arg02 is not null) then
328 g_fail_info.arg_values := g_fail_info.arg_values || ':' || p_arg02;
329 end if;
330
331 if(p_arg03 is not null) then
332 g_fail_info.arg_values := g_fail_info.arg_values || ':' || p_arg03;
333 end if;
334
335 if(p_arg04 is not null) then
336 g_fail_info.arg_values := g_fail_info.arg_values || ':' || p_arg04;
337 end if;
338
339 if(p_arg05 is not null) then
340 g_fail_info.arg_values := g_fail_info.arg_values || ':' || p_arg05;
341 end if;
342
343 if(p_arg06 is not null) then
347 if(p_arg07 is not null) then
344 g_fail_info.arg_values := g_fail_info.arg_values || ':' || p_arg06;
345 end if;
346
348 g_fail_info.arg_values := g_fail_info.arg_values || ':' || p_arg07;
349 end if;
350
351 if(p_arg08 is not null) then
352 g_fail_info.arg_values := g_fail_info.arg_values || ':' || p_arg08;
353 end if;
354
355 fnd_message.set_name('PER','HR_DP_EXCEPTION');
356 fnd_message.set_token('ROUTINE',p_function_name);
357 fnd_message.set_token('SQL_ERROR',p_error_message);
358 fnd_message.set_token('PARAM',g_fail_info.arg_values);
359 fnd_message.raise_error;
360
361 end fail;
362
363 /* logs failure from get id functions */
364 procedure get_id_failure
365 is
366 begin
367 if(not g_debug.get_id_dbg or not g_fail_info.fail_flag) then
368 return;
369 end if;
370
371 -- Reset the failure flag.
372 g_fail_info.fail_flag := FALSE;
373
374 hr_utility.trace_on('F', 'REQID');
375 hr_utility.trace('Fail [' || g_fail_info.func_name || ']');
376 hr_utility.trace('args: ' || g_fail_info.arg_values);
377 hr_utility.trace(g_fail_info.error_msg);
378 hr_utility.trace_off;
379
380 end get_id_failure;
381
382 /* provides entry macro */
383 procedure entry
384 (
385 p_procedure_name in varchar2
386 ) is
387 begin
388 if(not g_debug.call_trace_dbg) then
389 return;
390 end if;
391
392 hr_utility.trace_on('F', 'REQID');
393 hr_utility.trace('In : ' || p_procedure_name);
394 hr_utility.trace_off;
395 end entry;
396
397 /* provides entry macro */
398 procedure exit
399 (
400 p_procedure_name in varchar2
401 ) is
402 begin
403 if(not g_debug.call_trace_dbg) then
404 return;
405 end if;
406
407 hr_utility.trace_on('F', 'REQID');
408 hr_utility.trace('Out : ' || p_procedure_name);
409 hr_utility.trace_off;
410 end exit;
411
412 /* API trace on. */
413 procedure api_trc_on is
414 begin
415 if(not g_debug.api_module_dbg) then
416 return;
417 end if;
418
419 hr_utility.trace_on('F', 'REQID');
420 end api_trc_on;
421
422 /* API trace off. */
423 procedure api_trc_off is
424 begin
425 if(not g_debug.api_module_dbg) then
426 return;
427 end if;
428
429 hr_utility.trace_off;
430 end api_trc_off;
431
432 /* general message procedure */
433 procedure message
434 (
435 p_message varchar2
436 ) is
437 begin
438
439 -- send message to concurrent log file, if enabled
440 if(g_debug.conc_file_dbg) then
441 fnd_file.put_line(fnd_file.log, p_message);
442 end if;
443
444 if(not g_debug.message_dbg) then
445 return;
446 end if;
447
448 hr_utility.trace_on('F', 'REQID');
449 hr_utility.trace(p_message);
450 hr_utility.trace_off;
451 end message;
452
453 /*
454 * Procedure to output information about the
455 * information in the wrapper cache.
456 */
457 procedure wrap_cache_debug
458 is
459 begin
460 -- Check for debug
461 if(g_debug.wrap_cache_dbg) then
462 -- Initialise the trace.
463 hr_utility.trace_on('F', 'REQID');
464
465 -- Output the information.
466 hr_utility.trace('Wrapper cache debug....');
467
468 -- Finished, no more trace.
469 hr_utility.trace_off;
470 end if;
471 end wrap_cache_debug;
472
473
474 /*
475 * Outputs information about range inserts.
476 */
477 procedure range_ins_debug
478 (
479 p_info in range_info_r
480 ) is
481 l_rows varchar2(80);
482 l_number varchar2(80);
483 l_start varchar2(80);
484 l_end varchar2(80);
485 l_rows_in_range number;
486 begin
487
488 if(not g_debug.range_ins_dbg) then
489 return;
490 end if;
491
492 hr_utility.trace_on('F', 'REQID');
493
494 -- The following outputs a header line for every few
495 -- rows of logging output to make things easier when
496 -- there are lots of rows being processed.
497 if(p_info.range_number = 1 or mod(p_info.range_number, DEBUG_PAGES) = 0)
498 then
499 hr_utility.trace(' * RRI Ins Info * |---- Range ----|');
500 hr_utility.trace(' Number Rows Start End');
501 hr_utility.trace(' -------- -------- -------- --------');
502 end if;
503
504 -- Number of rows is always over counted by one.
505 l_rows_in_range := p_info.rows_in_range - 1;
506
507 l_number := lpad(p_info.range_number, 9) || ' ';
508 l_rows := lpad(l_rows_in_range, 8) || ' ';
509 l_start := lpad(p_info.range_start, 8) || ' ';
510 l_end := lpad(p_info.range_end, 8);
511
512 hr_utility.trace(l_number || l_rows || l_start || l_end);
513
514 hr_utility.trace_off;
515
516 end range_ins_debug;
517
518 /*
519 * Outputs information about the processing of a range.
520 */
521 procedure range_proc_debug
522 (
523 p_range_start in number,
524 p_range_end in number,
525 p_errcnt in binary_integer
526 ) is
527 l_total varchar2(80);
528 l_start varchar2(80);
529 l_end varchar2(80);
530 l_errcnt varchar2(80);
531 begin
532 if(not g_debug.range_proc_dbg) then
533 return;
534 end if;
535
539 -- rows of logging output to make things easier when
536 hr_utility.trace_on('F', 'REQID');
537
538 -- The following outputs a header line for every few
540 -- there are lots of rows being processed.
541 if(g_senv.rangetotal = 1 or mod(g_senv.rangetotal, DEBUG_PAGES) = 0)
542 then
543 hr_utility.trace('* Proc * |---- Range ----|');
544 hr_utility.trace(' Rge Num Start End Errcount');
545 hr_utility.trace(' -------- -------- -------- --------');
546 end if;
547
548 l_total := lpad(g_senv.rangetotal, 9) || ' ';
549 l_start := lpad(p_range_start, 8) || ' ';
550 l_end := lpad(p_range_end, 8) || ' ';
551 l_errcnt := lpad(p_errcnt, 8);
552 hr_utility.trace(l_total || l_start || l_end || l_errcnt);
553
554 hr_utility.trace_off;
555
556 end range_proc_debug;
557
558 /*
559 * Outputs summary information following failure.
560 * Note that this information may be similar to
561 * that for success.
562 */
563 procedure stack_dump
564 is
565 begin
566 if(not g_debug.stack_dump_dbg) then
567 return;
568 end if;
569
570 -- Give us some info!
571 hr_utility.trace_on('F', 'REQID');
572
573 hr_utility.trace('Stack Dump');
574 hr_utility.trace('batch lines : ' || g_senv.lines_proc);
575 hr_utility.trace('wrappers called : ' || g_senv.wrap_total);
576 hr_utility.trace('wrap cache hits : ' || g_senv.wcachehit);
577 hr_utility.trace('total errors : ' || g_senv.errortotal);
578 hr_utility.trace('ranges processed : ' || g_senv.rangetotal);
579 hr_utility.trace('batch id failed : ' || g_senv.fail_bline);
580
581 hr_utility.trace_off;
582 end stack_dump;
583
584 /*
585 * Outputs summary information following success.
586 * Note that this information may be similar to
587 * that for failure.
588 */
589 procedure exit_info
590 is
591 begin
592 if(not g_debug.exit_info_dbg) then
593 return;
594 end if;
595
596 -- Give us some info.
597 hr_utility.trace_on('F', 'REQID');
598
599 hr_utility.trace('Exit information');
600 hr_utility.trace('batch lines : ' || g_senv.lines_proc);
601 hr_utility.trace('wrappers called : ' || g_senv.wrap_total);
602 hr_utility.trace('wrap cache hits : ' || g_senv.wcachehit);
603 hr_utility.trace('total errors : ' || g_senv.errortotal);
604 hr_utility.trace('ranges processed : ' || g_senv.rangetotal);
605
606 hr_utility.trace_off;
607 end exit_info;
608
609 /*
610 * Procedure to clear the debug state. Used to
611 * initialise the debug state and stop debugging if
612 * there is an exception raised from the tracing code.
613 */
614 procedure clear_debug
615 is
616 begin
617 g_debug.message_dbg := FALSE;
618 g_debug.call_trace_dbg := FALSE;
619 g_debug.wrap_cache_dbg := FALSE;
620 g_debug.api_module_dbg := FALSE;
621 g_debug.stack_dump_dbg := FALSE;
622 g_debug.exit_info_dbg := FALSE;
623 g_debug.range_ins_dbg := FALSE;
624 g_debug.range_proc_dbg := FALSE;
625 g_debug.get_id_dbg := FALSE;
626 g_debug.conc_file_dbg := FALSE;
627 g_debug.batch_line_info:= FALSE;
628 end clear_debug;
629
630 /*
631 * Function sets the appropriate debug levels.
632 * Also outputs 'header' information to help
633 * the interpretation of the output where
634 * this seems necessary.
635 */
636 procedure set_debug
637 is
638 l_debug_str pay_action_parameters.parameter_value%type;
639 l_found boolean;
640 begin
641 -- Initialise the debugging information structure.
642 clear_debug;
643
644 -- Ensure trace is off when we start.
645 hr_utility.trace_off;
646
647 /*
648 * Attempt to get the debugging level
649 * from the action parameters table.
650 * If there is no row, return immediately.
651 */
652 get_action_parameter
653 (p_para_name => 'PUMP_DEBUG_LEVEL'
654 ,p_para_value => l_debug_str
655 ,p_found => l_found
656 );
657 if not l_found then
658 return;
659 end if;
660
661 /*
662 * Search the strings and look for the debug.
663 */
664 if(instr(l_debug_str, 'MSG') <> 0) then
665 g_debug.message_dbg := TRUE;
666 end if;
667
668 if(instr(l_debug_str, 'ROU') <> 0) then
669 g_debug.call_trace_dbg := TRUE;
670 end if;
671
672 if(instr(l_debug_str, 'WCD') <> 0) then
673 g_debug.wrap_cache_dbg := TRUE;
674 end if;
675
676 if(instr(l_debug_str, 'AMD') <> 0) then
677 g_debug.api_module_dbg := TRUE;
678 end if;
679
680 if(instr(l_debug_str, 'STK') <> 0) then
681 g_debug.stack_dump_dbg := TRUE;
682 end if;
683
684 if(instr(l_debug_str, 'EXT') <> 0) then
685 g_debug.exit_info_dbg := TRUE;
686 end if;
687
688 if(instr(l_debug_str, 'RRI') <> 0) then
689 g_debug.range_ins_dbg := TRUE;
690 end if;
691
692 if(instr(l_debug_str, 'RRP') <> 0) then
693 g_debug.range_proc_dbg := TRUE;
694 end if;
695
696 if(instr(l_debug_str, 'GID') <> 0) then
697 g_debug.get_id_dbg := TRUE;
698 end if;
699
700 -- check for CLF debug setting
701 if(instr(l_debug_str, 'CLF') <> 0) then
702 g_debug.conc_file_dbg := TRUE;
703 end if;
704 -- check for batch_line info setting
705 if(instr(l_debug_str, 'BLI') <> 0) then
706 g_debug.batch_line_info := TRUE;
707 end if;
708
709
710 -- Initialise the special info table.
711 g_fail_info.func_name := null;
712 g_fail_info.error_msg := null;
713 g_fail_info.arg_values := null;
714
715 end set_debug;
716
717 /*
718 * Post the error to exceptions table.
719 * Returns the actual error text, so we can
720 * pass this to the exit status message var.
721 * Note: there must be an exception handler
722 * around any trace code called by this function
723 * so that batch line and batch header rows are
724 * updated correctly.
725 * p_overmsg is an override message e.g. in validate mode
726 * the code saves error text before it rolls back any
727 * processing it performed. The saved error text is used
728 * as the override message in a call to post_error after the
729 * rollback.
730 */
731 function post_error
732 (
733 p_sqlcode in number,
734 p_errmsg in varchar2,
735 p_overmsg in varchar2,
736 p_level in varchar2,
737 p_type in varchar2,
738 p_id in number,
739 p_processing in boolean default false
740 ) return varchar2 is
741 l_exception_text hr_pump_batch_exceptions.exception_text%type;
742 l_encoded varchar2(2000); -- hold AOL encoded text.
743 begin
744
745 -- Need to get text from appropriate place.
746 if (p_overmsg is not null) then
747 -- Use the override message, if available.
748 l_exception_text := p_overmsg;
749 elsif (sqlcode = hr_utility.HR_ERROR_NUMBER) then
750 -- This is an application error.
751 l_encoded := fnd_message.get_encoded;
752 fnd_message.set_encoded(l_encoded);
753 l_exception_text := fnd_message.get;
754 if l_exception_text is null then
755 l_exception_text := sqlerrm;
756 end if;
757 else
758 -- Get message text for oracle error.
759 l_exception_text := p_errmsg;
760 end if;
761
762 -- Update the appropriate status
763 if(p_type = 'BATCH_HEADER' and not p_processing) then
764 update hr_pump_batch_headers pbh
765 set pbh.batch_status = 'E'
766 where pbh.batch_id = p_id;
767 elsif (p_type <> 'BATCH_HEADER') then
768 update hr_pump_batch_lines pbl
769 set pbl.line_status = 'E'
770 where pbl.batch_line_id = p_id;
771 end if;
772
773 begin
774 hr_data_pump.message('exception : ' || l_exception_text);
775 exception
776 -- Catch exceptions from logging code to allow status information
777 -- to be updated.
778 when others then
779 clear_debug;
780 end;
781
782 insert into hr_pump_batch_exceptions (
783 exception_sequence,
784 exception_level,
785 source_id,
786 source_type,
787 format,
788 exception_text)
789 values (hr_pump_batch_exceptions_s.nextval,
790 p_level,
791 p_id,
792 p_type,
793 'TRANSLATED',
794 l_exception_text);
795
796 return(l_exception_text);
797
798 end post_error;
799
800 /*
801 * Inserts a pump requests row.
802 * Gets the concurrent request from profile.
803 * Does not perform commit.
804 */
805 procedure ins_pump_request
806 (
807 p_batch_id in number,
808 p_process_type in varchar2
809 ) is
810 l_request_id number;
811 begin
812 -- Get the request_id profile value.
813 fnd_profile.get('CONC_REQUEST_ID', l_request_id);
814
815 -- Following in case we are not running
816 -- from the concurrent manager.
817 if(l_request_id is null) then
818 l_request_id := 0;
819 end if;
820
821 insert into hr_pump_requests (
822 batch_id,
823 request_id,
824 process_type)
825 values (p_batch_id,
826 l_request_id,
827 p_process_type);
828
829 end ins_pump_request;
830
831 /*
832 * Deletes a pump request row for the
833 * current request_id.
834 * Does not perform commit.
835 */
836 procedure del_pump_request
837 (
838 p_batch_id number
839 ) is
840 l_request_id number;
841 begin
842 -- Get the concurrent request id.
843 fnd_profile.get('CONC_REQUEST_ID', l_request_id);
844
845 -- Following in case we are not running
846 -- from the concurrent manager.
847 if(l_request_id is null) then
848 l_request_id := 0;
849 end if;
850
851 delete from hr_pump_requests hpr
852 where hpr.batch_id = p_batch_id
853 and hpr.request_id = l_request_id;
854
855 end del_pump_request;
856
857 /*
858 * Procedure to disable continuous calc triggers.
859 */
860 procedure disable_cont_calc
861 is
862 l_found boolean;
863 l_pap_value pay_action_parameters.parameter_value%type;
864 begin
865 get_action_parameter
866 (p_para_name => 'DATA_PUMP_DISABLE_CONT_CALC'
867 ,p_para_value => l_pap_value
868 ,p_found => l_found
869 );
870 --
871 -- The default value for the DATA_PUMP_DISABLE_CONT_CALC action
872 -- parameter is to enable continous calc (existing behaviour).
873 -- Continuous calc is only disabled when explicitly requested.
874 --
875 if l_found and upper(l_pap_value) = 'Y' then
876 pay_continuous_calc.g_override_cc := true;
877 end if;
878 end disable_cont_calc;
879
880 /*
881 * Procedure to handle disabling of auditing.
882 */
883 procedure disable_audit
884 is
885 l_found boolean;
886 l_pap_value pay_action_parameters.parameter_value%type;
887 begin
888 get_action_parameter
889 (p_para_name => 'DATA_PUMP_NO_FND_AUDIT'
890 ,p_para_value => l_pap_value
891 ,p_found => l_found
892 );
893 --
894 -- The default value for the DATA_PUMP_NO_FND_AUDIT action parameter
895 -- is to allow auditing to continue (existing behaviour). Auditing is
896 -- only turned off when explicitly requested.
897 --
898 if l_found and upper(l_pap_value) = 'Y' then
899 fnd_profile.put
900 (name => 'AUDITTRAIL:ACTIVATE'
901 ,val => 'N'
902 );
903 end if;
904 end;
905
906 /*
907 * Procedure to handle disabling of lookup checks.
908 */
909 procedure disable_lookup_checks is
910 l_found boolean;
911 l_pap_value pay_action_parameters.parameter_value%type;
912 begin
913 get_action_parameter
914 (p_para_name => 'DATA_PUMP_NO_LOOKUP_CHECKS'
915 ,p_para_value => l_pap_value
916 ,p_found => l_found
917 );
918 --
919 -- User must explicitly set the action parameter to 'N' to
920 -- disable the lookup checks.
921 --
922 hr_data_pump.g_disable_lookup_checks :=
923 l_found and upper(l_pap_value) = 'Y';
924 end;
925
926 /*
927 * Procedure to handle the setting of the Date-Track foreign
928 * key locking.
929 */
930 procedure set_dt_foreign_locking is
931 l_found boolean;
932 l_pap_value pay_action_parameters.parameter_value%type;
933 l_lock boolean;
934 begin
935 get_action_parameter
936 (p_para_name => 'PUMP_DT_ENFORCE_FOREIGN_LOCKS'
937 ,p_para_value => l_pap_value
938 ,p_found => l_found
939 );
940
941 --
942 -- Default behaviour is to lock. Also lock is the parameter
943 -- value is 'Y'.
944 --
945 l_lock :=
946 not l_found or
947 (l_found and (l_pap_value = 'Y' or l_pap_value = 'y'));
948
949 hr_pump_utils.set_dt_enforce_foreign_locks(p_enforce => l_lock);
950
951 if l_lock then
952 hr_data_pump.message('Foreign key locking enforced');
953 else
954 hr_data_pump.message('***** Foreign key locking NOT enforced *****');
955 end if;
956 end set_dt_foreign_locking;
957
958 /*
959 * Procedure to select important startup
960 * data for the master process.
961 */
962 procedure get_startup_info
963 (
964 p_batch_id in number,
965 p_pap_group_id in number,
966 p_env out nocopy master_env_r,
967 p_batch_status out nocopy varchar2
968 ) is
969 l_found boolean;
970 l_bg_name hr_pump_batch_headers.business_group_name%type;
971 l_batch_name hr_pump_batch_headers.batch_name%type;
972 begin
973 -- Give some defaults for the startup info.
974 p_env.business_group_id := null;
975 p_env.security_group_id := null;
976 p_env.range_size := RANGE_SIZE_DEFAULT;
977 p_env.threads := THREADS_DEFAULT;
978 p_env.error_limit := ERRORS_DEFAULT;
979 p_env.pap_group_id := p_pap_group_id;
980
981 --
982 -- Test-and-set the batch header.
983 --
984 headerTAS
985 (p_batch_id => p_batch_id
986 ,p_business_group_id => p_env.business_group_id
987 ,p_batch_name => l_batch_name
988 ,p_business_group_name => l_bg_name
989 ,p_security_group_id => p_env.security_group_id
990 ,p_batch_status => p_batch_status
991 );
992
993 --
994 -- Handle case of incorrect business group name.
995 --
996 if l_bg_name is not null and p_env.business_group_id is null then
997 message('BUSINESS_GROUP_NAME[1]: ' || l_bg_name);
998 hr_utility.set_message (800, 'HR_7208_API_BUS_GRP_INVALID');
999 hr_utility.raise_error;
1000 end if;
1001
1002 -- Get information about range size and
1003 -- threads parameters. Defaults have already
1004 -- been set. There do not have to be any rows.
1005 get_action_parameter
1006 (p_para_name => 'CHUNK_SIZE'
1007 ,p_para_value => p_env.range_size
1008 ,p_found => l_found
1009 );
1010 -- Check for reasonable values.
1011 if(not l_found or p_env.range_size < 1 or p_env.range_size > 100) then
1012 p_env.range_size := RANGE_SIZE_DEFAULT;
1013 end if;
1014
1015 get_action_parameter
1016 (p_para_name => 'THREADS'
1017 ,p_para_value => p_env.threads
1018 ,p_found => l_found
1019 );
1020 -- Check for reasonable values.
1021 if(not l_found or p_env.threads < 1 or p_env.threads > 100) then
1022 p_env.threads := THREADS_DEFAULT;
1023 end if;
1024
1025 get_action_parameter
1026 (p_para_name => 'MAX_ERRORS_ALLOWED'
1027 ,p_para_value => p_env.error_limit
1028 ,p_found => l_found
1029 );
1030 -- Check for reasonable values.
1031 if(not l_found or p_env.error_limit < 0) then
1032 p_env.error_limit := ERRORS_DEFAULT;
1033 end if;
1034
1035 hr_data_pump.message('range : ' || p_env.range_size);
1036 hr_data_pump.message('threads : ' || p_env.threads);
1037 hr_data_pump.message('max errors : ' || p_env.error_limit);
1038
1039 exception
1040 when others then
1041 raise;
1042
1043 end get_startup_info;
1044
1045 /*
1046 * Procedure to insert a row in the ranges table.
1047 * Before insert, it checks that the range
1048 * is not empty.
1049 */
1050 procedure insert_range
1051 (
1052 p_batch_id in number,
1053 p_range_info in range_info_r
1054 ) is
1055 begin
1056 hr_data_pump.entry('insert_range');
1057
1058 -- Check that there are actually some rows
1059 -- in the range we are about to insert.
1060 if(p_range_info.rows_in_range > 0) then
1061 insert into hr_pump_ranges (
1062 batch_id,
1063 range_number,
1064 range_status,
1065 starting_process_sequence,
1066 ending_process_sequence)
1067 values (p_batch_id,
1068 p_range_info.range_number,
1069 'U',
1070 p_range_info.range_start,
1071 p_range_info.range_end);
1072 end if;
1073
1074 hr_data_pump.exit('insert_range');
1075 end insert_range;
1076
1077 /*
1078 * Procedure to insert range rows for parallelisation.
1079 * Note that these rows are inserted afresh on every
1080 * run on the process. This function should not be
1081 * called if there are rows in existence.
1082 */
1083 function process_ranges
1084 (
1085 p_env in master_env_r,
1086 p_batch_id in number
1087 ) return number is
1088 -- Cursor returning rows to process.
1089 cursor c1 is
1090 select pbl.batch_line_id,
1091 pbl.link_value
1092 from hr_pump_batch_lines pbl
1093 where pbl.batch_id = p_batch_id
1094 and pbl.line_status <> 'C'
1095 order by nvl(pbl.user_sequence, pbl.batch_line_id);
1096
1097 c1rec c1%rowtype;
1098
1099 l_range_info range_info_r;
1100 l_proc_seq number;
1101 l_prv_link_val hr_pump_batch_lines.link_value%type;
1102 begin
1103 hr_data_pump.entry('process_ranges');
1104
1105 -- Initialise the variables for range insertion.
1106 l_range_info.rows_in_range := 0;
1107 l_range_info.range_number := 0;
1108 l_range_info.range_start := 1;
1109 l_range_info.range_end := 0;
1110
1111 l_proc_seq := 0;
1112 l_prv_link_val := null;
1113
1114 -- Use explicit cursor, because we want
1115 -- to examine the row fetched attribute.
1116 open c1;
1117
1118 /*
1119 * This section processes the batch lines we
1120 * return from the cursor, deciding how these
1121 * rows should be divided up into ranges.
1122 * The only real difficulty here is ensuring
1123 * that contiguous rows with the same
1124 * link_value are inserted into the same
1125 * range.
1126 */
1127 loop
1128 fetch c1 into c1rec;
1129
1130 -- Look for no rows found at all condition. i.e.
1131 -- we have no ranges to insert at all.
1132 exit when c1%notfound and l_proc_seq = 0;
1133
1134 -- Must increment this value here.
1135 l_range_info.rows_in_range := l_range_info.rows_in_range + 1;
1136
1137 -- Increment the absolute processing sequence.
1138 l_proc_seq := l_proc_seq + 1;
1139
1140 if(c1%found) then
1141 -- This update will control the order in which
1142 -- rows are processed by the slave process.
1143 update hr_pump_batch_lines pbl
1144 set pbl.process_sequence = l_proc_seq
1145 where pbl.batch_line_id = c1rec.batch_line_id;
1146 end if;
1147
1148 -- Check if there is a new range to insert.
1149 -- We have a new range if we have the following
1150 -- conditions:
1151 -- Have reached end of rows to process or
1152 -- we have gone over range limit and the rows are not linked.
1153 if(c1%notfound
1154 or (l_range_info.rows_in_range > p_env.range_size and
1155 (c1rec.link_value is null or l_prv_link_val is null or
1156 c1rec.link_value <> l_prv_link_val))
1157 )
1158 then
1159 -- Set values for range before insert.
1160 l_range_info.range_end := l_proc_seq - 1;
1164 insert_range(p_batch_id, l_range_info);
1161 l_range_info.range_number := l_range_info.range_number + 1;
1162
1163 -- Perform the insert of the previous range.
1165 range_ins_debug(l_range_info);
1166
1167 -- Set values for new range.
1168 l_range_info.rows_in_range := 1; -- already one row in range.
1169 l_range_info.range_start := l_proc_seq;
1170 end if;
1171
1172 -- We need to exit now if no row found.
1173 exit when c1%notfound;
1174
1175 -- Finally, set previous link value
1176 l_prv_link_val := c1rec.link_value;
1177 end loop;
1178
1179 close c1;
1180
1181 return(l_range_info.range_number);
1182
1183 hr_data_pump.exit('process_ranges');
1184
1185 end process_ranges;
1186
1187 /*
1188 * This procedure spawns the slave processes on
1189 * the concurrent manager.
1190 */
1191 procedure start_slaves
1192 (
1193 p_env in master_env_r,
1194 p_batch_id in number,
1195 p_validate in varchar2 default 'N',
1196 p_num_ranges in number
1197 ) is
1198 l_count number;
1199 l_request_id number;
1200 l_slaves number;
1201 begin
1202 hr_data_pump.entry('start_slaves');
1203
1204 -- Start one less than threads.
1205 l_slaves := p_env.threads - 1;
1206
1207 if (l_slaves > (p_num_ranges - 1)) then
1208 l_slaves := p_num_ranges - 1;
1209 end if;
1210
1211
1212 -- Start the slave processes.
1213 for l_count in 1..l_slaves loop
1214
1215 hr_data_pump.message('fnd_request.submit_request : ' || l_count);
1216
1217 l_request_id := fnd_request.submit_request
1218 (
1219 application => 'PER',
1220 program => 'DATAPUMP_SLAVE',
1221 description => null,
1222 sub_request => FALSE,
1223 argument1 => to_char(p_env.business_group_id),
1224 argument2 => to_char(p_env.security_group_id),
1225 argument3 => to_char(p_batch_id),
1226 argument4 => to_char(p_env.error_limit),
1227 argument5 => p_validate
1228 ,argument6 => to_char(p_env.pap_group_id)
1229 );
1230
1231 hr_data_pump.message('l_request_id : ' || l_request_id);
1232
1233 end loop;
1234
1235 hr_data_pump.exit('start_slaves');
1236 end start_slaves;
1237
1238 /*
1239 * This procedure calls the API wrapper module.
1240 * It is the wrapper that calls the API itself.
1241 * This wrapper has to be called dynamically.
1242 */
1243 procedure call_wrapper
1244 (
1245 p_business_group_id in number,
1246 p_batch_line_id in number,
1247 p_api_module_id in number,
1248 p_module_package in varchar2,
1249 p_module_name in varchar2
1250 ) is
1251 l_package_name varchar2(30);
1252 l_view_name varchar2(30); -- don't actually need this.
1253 l_call_string varchar2(2000);
1254 l_cache_entry number;
1255 begin
1256
1257 hr_data_pump.entry('call_wrapper');
1258
1259 -- Start by parsing the call if necessary.
1260 -- It may not be if the information is
1261 -- already in the wrapper cache.
1262 l_cache_entry := null;
1263 for i in 1 .. g_wrapper_cache.count loop
1264 if g_wrapper_cache(i).api_module_id = p_api_module_id then
1265 l_cache_entry := i;
1266 exit;
1267 end if;
1268 end loop;
1269
1270 if(l_cache_entry is null) then
1271 -- Not in cache - will need to parse.
1272 -- Start by getting the appropriate name.
1273 hr_pump_utils.name(p_module_package, p_module_name,
1274 l_package_name, l_view_name);
1275
1276 -- Build call string.
1277 l_call_string := 'begin ' || l_package_name || '.' ||
1278 'call(:p_business_group_id, :p_batch_line_id); end;';
1279
1280 -- Store details of the wrapper.
1281 l_cache_entry := g_wrapper_cache.count + 1;
1282 g_wrapper_cache(l_cache_entry).api_module_id := p_api_module_id;
1283 g_wrapper_cache(l_cache_entry).call_string := l_call_string;
1284
1285 else
1286 -- Get the call string for run.
1287 l_call_string := g_wrapper_cache(l_cache_entry).call_string;
1288 g_senv.wcachehit := g_senv.wcachehit + 1; -- count for debug.
1289 end if;
1290
1291 /*
1292 * Execute the call. API module debug is now done by the generated
1293 * code itself.
1294 */
1295 g_senv.wrap_total := g_senv.wrap_total + 1; -- count for debug.
1296
1297 -- show batch_line_id
1298 if(g_debug.batch_line_info) then
1299 message('p_batch_line_id - ' || p_batch_line_id);
1300 end if;
1301
1302 --
1303 -- Setup for multi-message support.
1304 -- 1. Clear the message list.
1305 -- 2. Reset multi message error flag.
1306 --
1307 hr_multi_message.enable_message_list;
1308 hr_pump_utils.set_multi_msg_error_flag(false);
1309
1310 -- Call the API.
1311 execute immediate l_call_string
1312 using in p_business_group_id
1313 , in p_batch_line_id
1314 ;
1315
1316 --
1317 -- If there are multi-message errors then raise the exception. The
1318 -- exception cannot be propagated out from the call block because
1319 -- it gets converted to an unhandled user exception.
1320 --
1321 if hr_pump_utils.multi_msg_errors_exist then
1322 raise hr_multi_message.error_message_exist;
1323 end if;
1324
1325 hr_data_pump.exit('call_wrapper');
1326
1327 end call_wrapper;
1328
1329 /*
1330 * Updates results for a range. Used at after the API have been executed
1331 * to:
1332 * DELETE existing rows from HR_PUMP_BATCH_EXCEPTIONS.
1333 * UPDATE HR_PUMP_BATCH_LINES LINE_STATUS.
1334 * INSERT rows into HR_PUMP_BATCH_EXCEPTIONS.
1335 */
1336 procedure update_range_results
1337 (p_failed_lines in dbms_sql.number_table
1338 ,p_exc_ids in dbms_sql.number_table
1339 ,p_exc_text in dbms_sql.varchar2_table
1340 ,p_ls_ids in dbms_sql.number_table
1341 ,p_ls_statuses in dbms_sql.varchar2s
1342 ) is
1343 lbound binary_integer;
1344 ubound binary_integer;
1345 nrows binary_integer;
1346 begin
1347 hr_data_pump.entry('update_range_results');
1348
1349 -- Delete the existing HR_PUMP_BATCH_EXCEPTIONS rows.
1350 lbound := 1;
1351 nrows := 250;
1352 while lbound <= p_failed_lines.count loop
1353 ubound := lbound + nrows - 1;
1354 if ubound > p_failed_lines.count then
1355 ubound := p_failed_lines.count;
1356 end if;
1357
1358 FORALL i IN lbound .. ubound
1359 delete from hr_pump_batch_exceptions e
1360 where e.source_id = p_failed_lines(i)
1361 and e.source_type = 'BATCH_LINE'
1362 ;
1363
1364 lbound := lbound + nrows;
1365 end loop;
1366
1367 -- Update HR_PUMP_BATCH_LINES LINE_STATUS values.
1368 lbound := 1;
1369 nrows := 500;
1370 while lbound <= p_ls_ids.count loop
1371 ubound := lbound + nrows - 1;
1372 if ubound > p_ls_ids.count then
1373 ubound := p_ls_ids.count;
1374 end if;
1375
1376 FORALL i IN lbound .. ubound
1377 update hr_pump_batch_lines bl
1378 set bl.line_status = p_ls_statuses(i)
1379 where bl.batch_line_id = p_ls_ids(i);
1380
1381 lbound := lbound + nrows;
1382 end loop;
1383
1384 -- Insert new batch exceptions.
1385 lbound := 1;
1386 nrows := 100;
1387 while lbound <= p_exc_ids.count loop
1388 ubound := lbound + nrows - 1;
1389 if ubound > p_exc_ids.count then
1390 ubound := p_exc_ids.count;
1391 end if;
1392
1393 FORALL i IN lbound .. ubound
1394 insert into hr_pump_batch_exceptions
1395 (exception_sequence
1396 ,exception_level
1397 ,source_id
1398 ,source_type
1399 ,format
1400 ,exception_text
1401 )
1402 values
1403 (hr_pump_batch_exceptions_s.nextval
1404 ,'F'
1405 ,p_exc_ids(i)
1406 ,'BATCH_LINE'
1407 ,'TRANSLATED'
1408 ,p_exc_text(i)
1409 );
1410
1411 lbound := lbound + nrows;
1412 end loop;
1413
1414 hr_data_pump.exit('update_range_results');
1415 end update_range_results;
1416
1417 /*
1418 * Handle API exceptions.
1419 */
1420 procedure handle_api_exc
1421 (p_multi_msg_error in boolean
1422 ,p_module_package in varchar2
1423 ,p_module_name in varchar2
1424 ,p_batch_line_id in number
1425 ,p_exc_ids in out nocopy dbms_sql.number_table
1426 ,p_exc_text in out nocopy dbms_sql.varchar2_table
1427 ) is
1428 l_message varchar2(4000);
1429 l_msg_index number;
1430 l_which_msg number := fnd_msg_pub.g_first;
1431 i number;
1432 l_procedure varchar2(128);
1433 l_ret boolean;
1434 begin
1435 -- Record the batch line that errored.
1436 g_senv.fail_bline := p_batch_line_id;
1437
1438 --
1439 -- For debugging purposes, we look at function
1440 -- that outputs information about get_id failures.
1441 --
1442 get_id_failure;
1443
1444 --
1445 -- API can implement multi-message support, but still raise an
1446 -- unrelated exception e.g. because of a locking failure. Add
1447 -- this to the message list. This should handle apps messages
1448 -- and non-apps messages cleanly.
1449 --
1450 -- Adding to the message list is appropriate as it preserves
1451 -- order and avoids problems with expanding message text for
1452 -- FND_MESSAGE.RAISE_ERROR case.
1453 --
1454 if not p_multi_msg_error then
1455 -- Use the API name as the error source.
1456 l_procedure := p_module_package || '.' || p_module_name;
1457
1458 l_ret := hr_multi_message.unexpected_error_add(l_procedure);
1459 end if;
1460
1461 --
1462 -- Update hr_pump_batch_exceptions error text list.
1463 --
1464 i := p_exc_ids.count + 1;
1465 for j in 1 .. fnd_msg_pub.count_msg loop
1466 --
1467 -- Standard multi-message code handling.
1468 --
1469 fnd_msg_pub.get
1470 (p_msg_index => l_which_msg
1471 ,p_encoded => fnd_api.g_false
1472 ,p_data => l_message
1473 ,p_msg_index_out => l_msg_index
1474 );
1475 l_which_msg := fnd_msg_pub.g_next;
1476
1477 p_exc_ids(i) := p_batch_line_id;
1478 p_exc_text(i) := l_message;
1479
1480 i := i + 1;
1481 end loop;
1482 end handle_api_exc;
1483
1484 /*
1485 * Process all the batch lines in current range.
1486 */
1487 procedure proc_lines_in_range
1488 (
1489 p_batch_id in number,
1490 p_business_group_id in number,
1491 p_security_group_id in number,
1492 p_max_errors in binary_integer,
1493 p_validate in boolean,
1494 p_atomic_calls in boolean,
1495 p_range_start in number,
1496 p_range_end in number
1497 ) is
1498 cursor c1 is
1499 select pbl.batch_line_id
1500 , pbl.line_status
1501 , ham.api_module_id
1502 , ham.module_package
1503 , ham.module_name
1504 , pbl.link_value
1505 , grp.business_group_id
1506 , grp.security_group_id
1507 , pbl.business_group_name
1508 from hr_pump_batch_lines pbl
1509 , hr_api_modules ham
1510 , per_business_groups_perf grp
1511 where pbl.batch_id = p_batch_id
1512 and ham.api_module_id = pbl.api_module_id
1513 and pbl.process_sequence between
1514 p_range_start and p_range_end
1515 and pbl.line_status <> 'C'
1516 and grp.name (+)= pbl.business_group_name
1517 order by pbl.process_sequence;
1518
1519 cursor c2(p_business_group_name in varchar2) is
1520 select business_group_id
1521 , security_group_id
1522 from per_business_groups_perf
1523 where name = p_business_group_name
1524 ;
1525
1526 l_prv_link_val hr_pump_batch_lines.link_value%type;
1527 l_err_mode boolean;
1528 l_err_count number; -- count for this range only.
1529 l_bus_group_id number;
1530 l_sec_group_id number;
1531
1532 -- Previous and this call are not linked.
1533 l_unlinked_calls boolean;
1534
1535 -- Maximum errors exceeded during this execution.
1536 l_max_errors boolean;
1537
1538 --
1539 -- List of HR_PUMP_BATCH_LINES rows previously in error. This is
1540 -- used to delete existing exception lines.
1541 --
1542 l_failed_lines dbms_sql.number_table;
1543
1544 --
1545 -- Exceptions list. This is used to update HR_PUMP_BATCH_EXCEPTIONS.
1546 --
1547 l_exc_ids dbms_sql.number_table;
1548 l_exc_text dbms_sql.varchar2_table;
1549
1550 --
1551 -- List for holding HR_PUMP_BATCH_LINES LINE_STATUS. This is used
1552 -- to update the LINE_STATUS column.
1553 --
1554 l_ls_statuses dbms_sql.varchar2s;
1555 l_ls_ids dbms_sql.number_table;
1556 l_ls_link_start number;
1557 l_ls_pos number;
1558
1559 --
1560 -- Flag to indicate early loop termination e.g. after MAX_ERRORS_ALLOWED
1561 -- exceeded for this thread.
1562 --
1563 l_complete boolean;
1564
1565 -- Process errors after API exceptions.
1566 l_process_errors boolean;
1567 begin
1568
1569 hr_data_pump.entry('proc_lines_in_range');
1570
1571 -- Initialise variables.
1572 l_prv_link_val := null;
1573 l_err_mode := FALSE;
1574 l_err_count := 0;
1575 l_max_errors := false;
1576 l_complete := false;
1577 l_process_errors := false;
1578
1579 g_senv.rangetotal := g_senv.rangetotal + 1; -- count for debug.
1580
1581 --
1582 -- Set up savepoint so that the validate code may rollback all API results.
1583 --
1584 savepoint before_api_calls;
1585
1586 for c1rec in c1 loop
1587 -- Count rows for debug purposes.
1588 g_senv.lines_proc := g_senv.lines_proc + 1;
1589
1590 -- Set up failed lines list.
1591 if c1rec.line_status = 'E' then
1592 l_failed_lines(l_failed_lines.count + 1) := c1rec.batch_line_id;
1593 end if;
1594
1595 -- Set up line status list.
1596 l_ls_pos := l_ls_ids.count + 1;
1597 l_ls_ids(l_ls_pos) := c1rec.batch_line_id;
1598 l_ls_statuses(l_ls_pos) := 'U';
1599
1600 /*
1601 * Check if we should call the wrapper. The condition
1602 * ensures that we do not call the wrapper if there
1603 * was a previous error and we are processing items
1604 * related by link_value - i.e. if one fails, the
1605 * rest of the related items must not be processed.
1606 *
1607 * Note: code can carry on after MAX_ERRORS_ALLOWED is
1608 * exceeded.
1609 */
1610
1611 l_unlinked_calls := c1rec.link_value is null or
1612 l_prv_link_val is null or
1613 c1rec.link_value <> l_prv_link_val;
1614
1615 if not l_max_errors and (not l_err_mode or l_unlinked_calls) then
1616
1617 begin
1618 --
1619 -- Set a SAVEPOINT to ROLLBACK failed API call. For atomic
1620 -- calls this is only done at the start of a set of linked
1621 -- calls.
1622 --
1623 if not p_atomic_calls or l_unlinked_calls then
1624 savepoint before_call_wrapper;
1625
1626 if p_atomic_calls then
1627 l_ls_link_start := l_ls_pos;
1628 end if;
1629 end if;
1630
1631 --
1632 -- The batch line-specific business group information
1633 -- overrides that from the batch header.
1634 --
1635 if c1rec.business_group_name is not null and
1636 c1rec.business_group_id is null then
1637 --
1638 -- A business group name is present on the row, but it was
1639 -- not possible to match it against PER_BUSINESS_GROUPS.
1640 -- This may be because the business group was created by
1641 -- an API call earlier in the batch; such a business group
1642 -- is not picked up in the C1 rowset. In this case, use the
1643 -- C2 cursor to match the business group.
1644 --
1645 open c2(p_business_group_name => c1rec.business_group_name);
1646 fetch c2
1647 into l_bus_group_id
1648 , l_sec_group_id
1649 ;
1650 if c2%notfound then
1651 close c2;
1652 --
1653 -- No match occurred, something is wrong with the batch
1654 -- setup.
1655 --
1656 message
1657 ('BUSINESS_GROUP_NAME[2]: ' || c1rec.business_group_name);
1658 --
1659 -- Initialise code for output of error message.
1660 --
1661 hr_multi_message.enable_message_list;
1662 hr_pump_utils.set_multi_msg_error_flag(false);
1663 --
1664 hr_utility.set_message (800, 'HR_7208_API_BUS_GRP_INVALID');
1665 hr_utility.raise_error;
1666 end if;
1667 close c2;
1668 else
1669 --
1670 -- Either BUSINESS_GROUP_NAME IS NULL or a match has been
1671 -- made. In the former case, override with the passed-in
1672 -- values (from HR_PUMP_BATCH_HEADERS).
1673 --
1674 l_bus_group_id :=
1675 nvl(c1rec.business_group_id, p_business_group_id);
1676 l_sec_group_id :=
1677 nvl(c1rec.security_group_id, p_security_group_id);
1678 end if;
1679
1680 -- Set the security_group_id in the CLIENT_INFO before each
1681 -- API call, because a previous API call may have overridden
1682 -- a previous setting.
1683 hr_api.set_security_group_id(l_sec_group_id);
1684 -- Call the wrapper module.
1685 call_wrapper(l_bus_group_id, c1rec.batch_line_id,
1686 c1rec.api_module_id, c1rec.module_package,
1687 c1rec.module_name);
1688
1689 -- If we reach here, no exception raised so
1690 -- able to reset the error mode.
1691
1692 l_err_mode := FALSE;
1693
1694 -- Record success by setting status.
1695 if (p_validate) then
1696 --
1697 -- Update range result table entry to say that line
1698 -- validated okay.
1699 --
1700 l_ls_statuses(l_ls_pos) := 'V';
1701 else
1702 l_ls_statuses(l_ls_pos) := 'C';
1703 end if;
1704 /*
1705 * Deal with exceptions raised at batch line level.
1706 * these do not cause failure of the entire process
1707 * until they exceed the pre-set count.
1708 */
1709 exception
1710 when hr_multi_message.error_message_exist then
1711 l_process_errors := true;
1712
1713 --
1714 -- Do the minimum necessary error handling here.
1715 --
1716 handle_api_exc
1717 (p_multi_msg_error => true
1718 ,p_module_package => c1rec.module_package
1719 ,p_module_name => c1rec.module_name
1720 ,p_batch_line_id => c1rec.batch_line_id
1721 ,p_exc_ids => l_exc_ids
1722 ,p_exc_text => l_exc_text
1723 );
1724
1725 when others then
1726 l_process_errors := true;
1727
1728 --
1729 -- Do the minimum necessary error handling here.
1730 --
1731 handle_api_exc
1732 (p_multi_msg_error => false
1733 ,p_module_package => c1rec.module_package
1734 ,p_module_name => c1rec.module_name
1735 ,p_batch_line_id => c1rec.batch_line_id
1736 ,p_exc_ids => l_exc_ids
1737 ,p_exc_text => l_exc_text
1738 );
1739 end;
1740
1741 --
1742 -- Common error handling code.
1743 --
1744 if l_process_errors then
1745
1746 l_process_errors := false;
1747 l_err_mode := true;
1748
1749 --
1750 -- Undo the API call (or linked calls for atomic linked calls).
1751 --
1752 rollback to before_call_wrapper;
1753
1754 --
1755 -- Set LINE_STATUS for the failed line.
1756 --
1757 l_ls_statuses(l_ls_pos) := 'E';
1758
1759 --
1760 -- Set LINE_STATUS to R (ROLLED BACK) for atomic linked calls.
1761 --
1762 if p_atomic_calls and l_ls_pos <> l_ls_link_start then
1763 for i in l_ls_link_start .. l_ls_pos - 1 loop
1764 l_ls_statuses(i) := 'R';
1765 end loop;
1766 end if;
1767
1768 -- Need to close C2.
1769 if c2%isopen then
1770 close c2;
1771 end if;
1772
1773 l_err_count := l_err_count + 1; -- count for this range.
1774
1775 --
1776 -- Check if maximum errors exceeded.
1777 --
1778 g_senv.errortotal := g_senv.errortotal + 1;
1779 if g_senv.errortotal >= p_max_errors then
1780
1781 l_max_errors := true;
1782
1783 --
1784 -- For atomic linked calls some work still needs to be
1785 -- done. In all other cases, the work is complete.
1786 --
1787 l_complete := (not p_atomic_calls);
1788 end if;
1789 end if;
1790
1791 --
1792 -- If one of the atomic linked calls had failed, the remaining
1793 -- linked calls must be set to status 'N' (not processed). This
1794 -- status allows Data Pump Purge to completely remove failed
1795 -- atomic linked calls as a set.
1796 --
1797 elsif l_err_mode and p_atomic_calls and not l_unlinked_calls then
1798 l_ls_statuses(l_ls_pos) := 'N';
1799
1800 --
1801 -- The code was finishing off after MAX_ERRORS_ALLOWED was
1802 -- exceeded. This happens for atomic calls. The code has reached
1803 -- an unlinked API so do tidy up and set completion flag.
1804 --
1805 elsif l_max_errors then
1806
1807 --
1808 -- Delete any information for this line from the lists.
1809 --
1810 if l_failed_lines.count > 0 and
1811 l_failed_lines(l_failed_lines.count) = c1rec.batch_line_id
1812 then
1813 l_failed_lines.delete(l_failed_lines.count);
1814 end if;
1815
1816 l_ls_ids.delete(l_ls_pos);
1817 l_ls_statuses.delete(l_ls_pos);
1818
1819 l_complete := true;
1820 end if;
1821
1822 --
1823 -- Exit the loop early if necessary.
1824 --
1825 exit when l_complete;
1826
1827 --
1828 -- Important to set this whether or not an exception occurred.
1829 --
1830 l_prv_link_val := c1rec.link_value;
1831 end loop;
1832
1833 ----------------------------
1834 -- API EXECUTION COMPLETE --
1835 ----------------------------
1836
1837 --
1838 -- Rollback all the API results in validate mode.
1839 --
1840 if p_validate then
1841 rollback to before_api_calls;
1842 end if;
1843
1844 --
1845 -- Delete the old exception lines, update the line status and
1846 -- exception text.
1847 --
1848 update_range_results
1849 (p_failed_lines => l_failed_lines
1850 ,p_exc_ids => l_exc_ids
1851 ,p_exc_text => l_exc_text
1852 ,p_ls_ids => l_ls_ids
1853 ,p_ls_statuses => l_ls_statuses
1854 );
1855
1856 --
1857 -- COMMIT the results.
1858 --
1859 commit;
1860
1861 -- Output debugging. Note error count is for this range.
1862 range_proc_debug(p_range_start, p_range_end, l_err_count);
1863
1864 hr_data_pump.exit('proc_lines_in_range');
1865
1866 --
1867 -- If MAXIMUM_ERRORS_ALLOWED exceeded, need to raise an error.
1868 --
1869 if l_max_errors then
1870 -- Raise error to exit completely.
1871 hr_utility.set_message (800, 'HR_7269_ASS_TOO_MANY_ERRORS');
1872 hr_utility.raise_error;
1873 end if;
1874
1875 end proc_lines_in_range;
1876
1877 /*---------------------------------------------------------------------------*/
1878 /*------------------ global functions and procedures ------------------------*/
1879 /*---------------------------------------------------------------------------*/
1880
1881 ------------------------------- internal_slave --------------------------------
1882 /*
1883 NAME
1884 internal_slave
1885 DESCRIPTION
1886 Internal entry point for slave process.
1887 NOTES
1888 This interface is called from either the master process
1889 when it becomes a 'slave' or from the direct concurrent
1890 manager interface.
1891 */
1892 procedure internal_slave
1893 (
1894 errbuf out nocopy varchar2,
1895 retcode out nocopy number,
1896 p_business_group_id in number,
1897 p_security_group_id in number,
1898 p_batch_id in number,
1899 p_max_errors in binary_integer,
1900 p_validate in varchar2 default 'N',
1901 p_single_threaded in boolean default false
1902 ) is
1903 l_batch_status varchar2(1);
1904 l_range_rowid urowid;
1905 l_range_start number;
1906 l_range_end number;
1907 l_range_count number;
1908 l_encoded varchar2(2000); -- hold AOL encoded text.
1909 l_validate boolean;
1910 l_data_migrator_mode varchar2(30);
1911 l_found boolean := false;
1912 l_pap_value pay_action_parameters.parameter_value%type;
1913 l_csr_range_rows range_fetch_cursor_t;
1914 l_atomic_calls boolean;
1915 begin
1916
1917 hr_data_pump.entry('internal_slave');
1918
1919 if p_single_threaded then
1920 hr_data_pump.message('SINGLE-THREADED');
1921 else
1922 hr_data_pump.message('MULTI-THREADED');
1923 end if;
1924
1925 -- Initialise the slave environment record.
1926 -- Do this here rather than in initialisation
1927 -- section, because we might be running several times
1928 -- from one sqlplus session (for debugging and the like)
1929 -- which would mean these would not get re-set.
1930 g_senv.lines_proc := 0;
1931 g_senv.wcachehit := 0;
1932 g_senv.wrap_total := 0;
1933 g_senv.errortotal := 0;
1934 g_senv.rangetotal := 0;
1935 g_senv.fail_bline := NULL;
1936 g_senv.single_threaded := p_single_threaded;
1937
1938 if ( p_validate = 'Y' ) then
1939 l_validate := true;
1940 else
1941 l_validate := false;
1942 end if;
1943
1944 -- Get any further required information from HR_PUMP_BATCH_HEADERS.
1945 header_read
1946 (p_batch_id => p_batch_id
1947 ,p_atomic_linked_calls => l_atomic_calls
1948 );
1949
1950 --
1951 -- Set global for data_migrator_mode. Only do this if the global
1952 -- has a value of 'N'.
1953 --
1954 hr_data_pump.entry('g_data_migrator_mode'||hr_general.g_data_migrator_mode);
1955 --
1956 l_data_migrator_mode := hr_general.g_data_migrator_mode;
1957 --
1958 if hr_general.g_data_migrator_mode = 'N' then
1959 get_action_parameter
1960 (p_para_name => 'DATA_MIGRATOR_MODE'
1961 ,p_para_value => l_pap_value
1962 ,p_found => l_found
1963 );
1964 if l_found then
1965 -- hr_general.g_data_migrator_mode is a varchar2(1).
1966 l_pap_value := substr(l_pap_value, 1, 1);
1967 --
1968 -- In case a stupid value has been put into the parameter set it
1969 -- back to 'N' in this case.
1970 --
1971 if l_pap_value not in ('P','Y','N') then
1972 l_pap_value := 'N';
1973 end if;
1974 hr_general.g_data_migrator_mode := l_pap_value;
1975 end if;
1976 end if;
1977 --
1978 hr_data_pump.entry('g_data_migrator_mode'||hr_general.g_data_migrator_mode);
1979 --
1980 -- Disable the FND audit.
1981 --
1982 disable_audit;
1983 --
1984 -- Disable Continuous Calc.
1985 --
1986 disable_cont_calc;
1987 --
1988 -- Disable lookup checks.
1989 --
1990 disable_lookup_checks;
1991 --
1992 -- Set Date-Track foreign key locking.
1993 --
1994 set_dt_foreign_locking;
1995 --
1996 -- Mark this session as a running Data Pump session.
1997 --
1998 hr_pump_utils.set_current_session_running(p_running => true);
1999
2000 /*
2001 * Main processing loop. We attempt to grab a
2002 * range of batch lines to process until there
2003 * are none left.
2004 */
2005 loop
2006 --
2007 -- Open the cursor. Use an ORDER BY for the single-threaded case.
2008 --
2009 if g_senv.single_threaded then
2010 hr_data_pump.message('RR:SINGLE-THREADED');
2011 open l_csr_range_rows for
2012 select hpr.rowid,
2013 hpr.starting_process_sequence,
2014 hpr.ending_process_sequence
2015 from hr_pump_ranges hpr,
2016 hr_pump_batch_headers pbh
2017 where hpr.batch_id = p_batch_id
2018 and hpr.range_status = 'U'
2019 and pbh.batch_id = hpr.batch_id
2020 and pbh.batch_status <> 'E'
2021 order by
2022 hpr.starting_process_sequence
2023 for update of
2024 hpr.starting_process_sequence, pbh.batch_status
2025 ;
2026 else
2027 hr_data_pump.message('RR:MULTI-THREADED');
2028 open l_csr_range_rows for
2029 select hpr.rowid,
2030 hpr.starting_process_sequence,
2031 hpr.ending_process_sequence
2032 from hr_pump_ranges hpr,
2033 hr_pump_batch_headers pbh
2034 where hpr.batch_id = p_batch_id
2035 and hpr.range_status = 'U'
2036 and pbh.batch_id = hpr.batch_id
2037 and pbh.batch_status <> 'E'
2038 and rownum < 2 -- only get one row
2039 for update of
2040 hpr.starting_process_sequence, pbh.batch_status
2041 ;
2042 end if;
2043
2044 fetch l_csr_range_rows
2045 into l_range_rowid
2046 , l_range_start
2047 , l_range_end
2048 ;
2049 --
2050 -- There are no more unprocessed range rows or the batch has
2051 -- errored, so exit the loop.
2052 --
2053 if l_csr_range_rows%notfound then
2054 close l_csr_range_rows;
2055 exit;
2056 end if;
2057 close l_csr_range_rows;
2058
2059 -- Change status to show we are processing this.
2060 update hr_pump_ranges hpr
2061 set hpr.range_status = 'P'
2062 where hpr.rowid = l_range_rowid;
2063
2064 commit; -- release the lock.
2065
2066 -- Call procedure to process the current range.
2067 proc_lines_in_range (p_batch_id, p_business_group_id,
2068 p_security_group_id,
2069 p_max_errors, l_validate,
2070 l_atomic_calls,
2071 l_range_start, l_range_end);
2072
2073 -- Finished with range, so remove it and commit.
2074 delete from hr_pump_ranges hpr
2075 where hpr.rowid = l_range_rowid;
2076 commit;
2077
2078 end loop;
2079
2080 /*
2081 * If we are exiting the loop, this means we could not
2082 * lock a row. This normally indicates we have finished
2083 * but might mean another slave has errored and we have
2084 * to exit. Therefore, attempt to update the batch status
2085 * as appropriate to what has happened.
2086 */
2087
2088 -- Attempt to lock the row.
2089 select pbh.batch_status
2090 into l_batch_status
2091 from hr_pump_batch_headers pbh
2092 where pbh.batch_id = p_batch_id
2093 for update of pbh.batch_status;
2094
2095 -- Check the current status.
2096 -- Only want to change it if still processing.
2097 if(l_batch_status = 'P') then
2098 -- Need to know if we are the last process
2099 -- still working. See if this is so by
2100 -- looking for any ranges still processing.
2101 select count(*)
2102 into l_range_count
2103 from hr_pump_ranges hpr
2104 where hpr.batch_id = p_batch_id;
2105
2106 if(l_range_count = 0) then
2107 -- We are the last, so update the batch status.
2108 update hr_pump_batch_headers pbh
2109 set pbh.batch_status = 'C'
2110 where pbh.batch_id = p_batch_id;
2111 end if;
2112 end if;
2113
2114 -- Show success information.
2115 exit_info;
2116
2117 -- release any locks.
2118 commit;
2119
2120 -- reset value of g_data_migrator
2121
2122 hr_general.g_data_migrator_mode := l_data_migrator_mode;
2123
2124 --
2125 -- This is no longer a running Data Pump session.
2126 --
2127 hr_pump_utils.set_current_session_running(p_running => false);
2128
2129 hr_data_pump.exit('internal_slave');
2130
2131 /*
2132 * Deal with any exceptions that came through. This would
2133 * most likely be the 'too many errors' error, although it
2134 * might not be.
2135 */
2136 exception
2137 when others then
2138 if l_csr_range_rows%isopen then
2139 close l_csr_range_rows;
2140 end if;
2141
2142 rollback;
2143
2144 if l_range_rowid is not null then
2145 delete from hr_pump_ranges hpr
2146 where hpr.rowid = l_range_rowid;
2147 end if;
2148
2149 errbuf := post_error(sqlcode, sqlerrm, null, 'F', 'BATCH_HEADER',
2150 p_batch_id);
2151 commit;
2152
2153 -- Call debug logging.
2154 stack_dump;
2155
2156 -- reset value of g_data_migrator
2157
2158 hr_general.g_data_migrator_mode := l_data_migrator_mode;
2159
2160 -- Set the exit conditions.
2161 retcode := 2; -- error.
2162
2163 --
2164 -- This is no longer a running Data Pump session.
2165 --
2166 hr_pump_utils.set_current_session_running(p_running => false);
2167
2168 end internal_slave;
2169
2170 ---------------------------------- slave --------------------------------------
2171 /*
2172 NAME
2173 slave
2174 DESCRIPTION
2175 Entry point for slave process.
2176 NOTES
2177 This procedure should be called via the concurrent manager.
2178 Under normal circumstances, it should NOT be called directly.
2179 The only difference is the SRS interface sets the debug
2180 and inserts a pump request row - both of which would already
2181 have been done if the slave is called directly from the master.
2182 */
2183
2184 procedure slave
2185 (
2186 errbuf out nocopy varchar2,
2187 retcode out nocopy number,
2188 p_business_group_id in number,
2189 p_security_group_id in number,
2190 p_batch_id in number,
2191 p_max_errors in binary_integer,
2192 p_validate in varchar2 default 'N'
2193 ,p_pap_group_id in number default null
2194 ) is
2195 begin
2196 --
2197 -- Set action_parameter_group_id. This must be done before any
2198 -- code that accesses PAY_ACTION_PARAMETERS.
2199 --
2200 pay_core_utils.set_pap_group_id(p_pap_group_id => p_pap_group_id);
2201
2202 -- Set any requested debug.
2203 set_debug;
2204
2205 -- Insert a pump request row.
2206 ins_pump_request(p_batch_id, 'SLAVE');
2207
2208 -- Start by assuming success.
2209 errbuf := null;
2210 retcode := 0;
2211
2212 internal_slave(errbuf, retcode, p_business_group_id,
2213 p_security_group_id,
2214 p_batch_id, p_max_errors, p_validate);
2215
2216 -- Delete the pump request row.
2217 del_pump_request(p_batch_id);
2218 commit;
2219 end slave;
2220
2221 ---------------------------------- main ---------------------------------------
2222 /*
2223 NAME
2224 main
2225 DESCRIPTION
2226 Main entry point for Data Pump engine.
2227 NOTES
2228 This procedure should be called via the concurrent manager.
2229 Under normal circumstances, it should NOT be called directly.
2230 */
2231
2232 procedure main
2233 (
2234 errbuf out nocopy varchar2,
2235 retcode out nocopy number,
2236 p_batch_id in number, -- batch_id
2237 p_validate in varchar2 default 'N'
2238 ,p_pap_group_id in number default null
2239 ) is
2240 l_env master_env_r;
2241 l_batch_status varchar2(1);
2242 l_processing boolean := false;
2243 l_num_ranges number;
2244 begin
2245
2246 -- Start by assuming success.
2247 errbuf := null;
2248 retcode := 0;
2249
2250 --
2251 -- Set action_parameter_group_id. This must be done before any
2252 -- code that accesses PAY_ACTION_PARAMETERS.
2253 --
2254 pay_core_utils.set_pap_group_id(p_pap_group_id => p_pap_group_id);
2255
2256 -- Set any requested debug.
2257 set_debug;
2258
2259 hr_data_pump.message('p_batch_id : ' || p_batch_id);
2260
2261 -- Get startup information for this process.
2262 -- Return batch status while we are at it.
2263 get_startup_info(p_batch_id, p_pap_group_id, l_env, l_batch_status);
2264
2265 hr_data_pump.message('l_batch_status : ' || l_batch_status);
2266
2267 --
2268 -- Only enter main processing if we are not doing so already.
2269 --
2270 if l_batch_status is not null and l_batch_status <> 'P' then
2271
2272 -- Delete any existing pump request rows.
2273 delete from hr_pump_requests hpr
2274 where hpr.batch_id = p_batch_id;
2275
2276 -- Insert a new pump request row.
2277 ins_pump_request(p_batch_id, 'MASTER');
2278
2279 -- Remove any messages that are might exist for
2280 -- the batch header.
2281 delete from hr_pump_batch_exceptions e
2282 where e.source_id = p_batch_id
2283 and e.source_type = 'BATCH_HEADER';
2284
2285 -- We delete any existing range rows for this batch_id each time
2286 -- the process runs.
2287 delete from hr_pump_ranges where batch_id = p_batch_id;
2288 commit;
2289
2290 -- Call the appropriate function
2291 -- to insert new range rows.
2292 l_num_ranges := process_ranges(l_env, p_batch_id);
2293
2294 -- All ranges inserted, so commit;
2295 commit;
2296
2297 -- Start slave processes.
2298 if l_num_ranges >0 then
2299 start_slaves(l_env, p_batch_id, p_validate, l_num_ranges);
2300 end if;
2301
2302 -- The master becomes the slave....
2303 internal_slave(errbuf, retcode, l_env.business_group_id,
2304 l_env.security_group_id,
2305 p_batch_id, l_env.error_limit, p_validate, l_env.threads = 1);
2306
2307 -- Delete the pump request row.
2308 del_pump_request(p_batch_id);
2309
2310 --
2311 -- The batch was not found.
2312 --
2313 elsif l_batch_status is null then
2314 hr_utility.set_message(800, 'HR_33798_DP_BATCH_NOT_FOUND');
2315 hr_utility.set_message_token('BATCH_ID', to_char(p_batch_id));
2316 hr_utility.raise_error;
2317
2318 --
2319 -- The batch is already being processed.
2320 --
2321 else
2322 -- Raise an error.
2323 l_processing := true;
2324 hr_utility.set_message(800, 'HR_50329_DP_ALREADY_PROCESSING');
2325 hr_utility.raise_error;
2326 end if;
2327
2328 -- Ensure all commited before exit.
2329 commit;
2330
2331 exception
2332 when others then
2333 rollback;
2334
2335 --
2336 -- Write error to the exceptions table for this batch header.
2337 --
2338 if l_batch_status is not null then
2339 errbuf :=
2340 post_error(sqlcode, sqlerrm, null, 'F', 'BATCH_HEADER', p_batch_id,
2341 l_processing);
2342 else
2343 --
2344 -- No batch available.
2345 --
2346 errbuf := sqlerrm;
2347 end if;
2348
2349 commit;
2350
2351 -- Set exit code.
2352 retcode := 2;
2353 end main;
2354
2355 ----------------------------
2356 --* DATA PUMP PURGE CODE *--
2357 ----------------------------
2358
2359 ------------------------------- purgeEOC --------------------------------------
2360 /*
2361 NAME
2362 purgeEOC purge End-Of-Chunk
2363 Description
2364 Carries End-Of-Chunk purge processing.
2365 */
2366 procedure purgeEOC
2367 (p_chunk_size in number
2368 ,p_work_to_do out nocopy boolean
2369 )
2370 is
2371 begin
2372 --
2373 -- If all possible rows were not processed then there is still work to
2374 -- do.
2375 --
2376 p_work_to_do := (sql%rowcount = p_chunk_size);
2377 --
2378 -- If rows were processed then commit the changes;
2379 --
2380 if sql%found then
2381 commit;
2382 end if;
2383 end purgeEOC;
2384
2385 ------------------------------- purgelines ------------------------------------
2386 /*
2387 NAME
2388 purgelines
2389 Description
2390 Purge HR_PUMP_BATCH_LINES rows with a specified status.
2391 */
2392 procedure purgelines
2393 (p_batch_id in number
2394 ,p_chunk_size in number
2395 ,p_line_status in varchar2
2396 ) is
2397 l_work_to_do boolean;
2398 begin
2399 hr_data_pump.message('HR_PUMP_BATCH_LINES:' || p_line_status);
2400
2401 l_work_to_do := true;
2402 while l_work_to_do loop
2403 delete
2404 from hr_pump_batch_lines l
2405 where l.batch_id = p_batch_id
2406 and l.line_status = p_line_status
2407 and rownum <= p_chunk_size
2408 ;
2409 --
2410 purgeEOC
2411 (p_chunk_size => p_chunk_size
2412 ,p_work_to_do => l_work_to_do
2413 );
2414 end loop;
2415 end purgelines;
2416
2417 ------------------------- purgeorphanuserkeys ---------------------------------
2418 /*
2419 NAME
2420 purgeorphanuserkeys
2421 Description
2422 Purge orphaned user keys i.e. those with NULL BATCH_LINE_ID.
2423 */
2424 procedure purgeorphanuserkeys
2425 (p_chunk_size in number
2426 ,p_thread_number in number
2427 ,p_threads in number
2428 ,p_write_log in boolean default false
2429 ,p_lower_bound in number
2430 ,p_upper_bound in number
2431 ,p_error_message out nocopy varchar2
2432 ) is
2433 l_work_to_do boolean;
2434 l_message fnd_new_messages.message_text%type;
2435 begin
2436 hr_data_pump.message('HR_PUMP_BATCH_LINE_USER_KEYS:BATCH_LINE_ID IS NULL');
2437
2438 p_error_message := null;
2439
2440 l_work_to_do := true;
2441 while l_work_to_do loop
2442 delete
2443 from hr_pump_batch_line_user_keys uk
2444 where uk.batch_line_id is null
2445 and uk.user_key_id between
2446 p_lower_bound and p_upper_bound
2447 and rownum <= p_chunk_size
2448 ;
2449 --
2450 purgeEOC
2451 (p_chunk_size => p_chunk_size
2452 ,p_work_to_do => l_work_to_do
2453 );
2454 end loop;
2455 ---------------------------------------
2456 -- Write success message to the log. --
2457 ---------------------------------------
2458 hr_utility.set_message(800, 'HR_33796_DP_PURGED_USER_KEYS');
2459 l_message := hr_utility.get_message;
2460 if p_write_log then
2461 fnd_file.put_line(fnd_file.log, l_message);
2462 end if;
2463
2464 hr_data_pump.message(l_message);
2465
2466 exception
2467 when others then
2468 hr_utility.set_message(800, 'HR_33797_DP_USER_KEY_PURGE_ERR');
2469 hr_utility.set_message_token('ERROR_MESSAGE', sqlerrm);
2470 l_message := hr_utility.get_message;
2471 p_error_message := l_message;
2472 if p_write_log then
2473 fnd_file.put_line(fnd_file.log, l_message);
2474 end if;
2475
2476 hr_data_pump.message(l_message);
2477 return;
2478 end purgeorphanuserkeys;
2479
2480 ------------------------------- purgebatch ------------------------------------
2481 /*
2482 NAME
2483 purgebatch
2484 DESCRIPTION
2485 Internal purge routine for the data pump engine.
2486 NOTES
2487 This procedure purges a single batch. It is called from the concurrent
2488 manager routines.
2489 */
2490 procedure purgebatch
2491 (p_batch_id in number
2492 ,p_chunk_size in number
2493 ,p_write_log in boolean default false
2494 ,p_preserve_user_keys in boolean
2495 ,p_purge_unprocessed in boolean
2496 ,p_purge_errored in boolean
2497 ,p_purge_completed in boolean
2498 ,p_delete_header in boolean
2499 ,p_error_message out nocopy varchar2
2500 ) is
2501 l_work_to_do boolean;
2502 l_status varchar2(32);
2503 l_business_group_name hr_pump_batch_headers.business_group_name%type;
2504 l_batch_name hr_pump_batch_headers.batch_name%type;
2505 l_business_group_id number;
2506 l_security_group_id number;
2507 l_message fnd_new_messages.message_text%type;
2508 l_count number;
2509 begin
2510 p_error_message := null;
2511 ----------------------------------------
2512 -- 1. Check and set the batch header. --
2513 ----------------------------------------
2514 headerTAS
2515 (p_batch_id => p_batch_id
2516 ,p_business_group_id => l_business_group_id
2517 ,p_batch_name => l_batch_name
2518 ,p_business_group_name => l_business_group_name
2519 ,p_security_group_id => l_security_group_id
2520 ,p_batch_status => l_status
2521 );
2522 --
2523 -- This header is already being processed.
2524 --
2525 if l_status = 'P' then
2526 hr_utility.set_message(800, 'HR_50329_DP_ALREADY_PROCESSING');
2527 hr_utility.raise_error;
2528 elsif l_status is null then
2529 --
2530 -- Batch does not exist - it may have been purged already.
2531 --
2532 return;
2533 end if;
2534
2535 hr_data_pump.message('PURGE: ' || l_batch_name);
2536
2537 -----------------------------------------------------
2538 -- 2. Preserve or delete user keys for this batch. --
2539 -----------------------------------------------------
2540 hr_data_pump.message('HR_PUMP_BATCH_LINE_USER_KEYS');
2541
2542 l_work_to_do := true;
2543 while l_work_to_do loop
2544 if p_preserve_user_keys then
2545 update hr_pump_batch_line_user_keys uk
2549 select bl.batch_line_id
2546 set uk.batch_line_id = null
2547 where uk.batch_line_id in
2548 (
2550 from hr_pump_batch_lines bl
2551 where bl.batch_id = p_batch_id
2552 )
2553 and rownum <= p_chunk_size
2554 ;
2555 else
2556 delete
2557 from hr_pump_batch_line_user_keys uk
2558 where uk.batch_line_id in
2559 (
2560 select l.batch_line_id
2561 from hr_pump_batch_lines l
2562 where l.batch_id = p_batch_id
2563 )
2564 and rownum <= p_chunk_size
2565 ;
2566 end if;
2567 --
2568 purgeEOC
2569 (p_chunk_size => p_chunk_size
2570 ,p_work_to_do => l_work_to_do
2571 );
2572 end loop;
2573
2574 ----------------------------------------------------
2575 -- 3. Delete the batch exceptions for this batch. --
2576 ----------------------------------------------------
2577 hr_data_pump.message('HR_PUMP_BATCH_EXCEPTIONS');
2578
2579 l_work_to_do := true;
2580 while l_work_to_do loop
2581 delete
2582 from hr_pump_batch_exceptions e
2583 where (
2584 (
2585 e.source_id = p_batch_id and
2586 e.source_type = 'BATCH_HEADER'
2587 ) or
2588 (
2589 e.source_id in
2590 (
2591 select l.batch_line_id
2592 from hr_pump_batch_lines l
2593 where l.batch_id = p_batch_id
2594 ) and
2595 e.source_type = 'BATCH_LINE'
2596 )
2597 )
2598 and rownum <= p_chunk_size;
2599 --
2600 purgeEOC
2601 (p_chunk_size => p_chunk_size
2602 ,p_work_to_do => l_work_to_do
2603 );
2604 end loop;
2605
2606 -----------------------------------------------
2607 -- 4. Delete the batch lines for this batch. --
2608 -----------------------------------------------
2609 if p_purge_completed then
2610 purgelines
2611 (p_batch_id => p_batch_id
2612 ,p_chunk_size => p_chunk_size
2613 ,p_line_status => 'C'
2614 );
2615 end if;
2616 --
2617 if p_purge_errored then
2618 purgelines
2619 (p_batch_id => p_batch_id
2620 ,p_chunk_size => p_chunk_size
2621 ,p_line_status => 'E'
2622 );
2623 --
2624 -- Lines that had to be ROLLED BACK with ATOMIC linked APIs are effectively in
2625 -- error. Also, linked APIs that were not processed because of an error in a linked
2626 -- call are also in error. The relevant line statuses are R and N, respectively.
2627 --
2628 purgelines
2629 (p_batch_id => p_batch_id
2630 ,p_chunk_size => p_chunk_size
2631 ,p_line_status => 'R'
2632 );
2633 purgelines
2634 (p_batch_id => p_batch_id
2635 ,p_chunk_size => p_chunk_size
2636 ,p_line_status => 'N'
2637 );
2638 end if;
2639 if p_purge_unprocessed then
2640 purgelines
2641 (p_batch_id => p_batch_id
2642 ,p_chunk_size => p_chunk_size
2643 ,p_line_status => 'U'
2644 );
2645 --
2646 purgelines
2647 (p_batch_id => p_batch_id
2648 ,p_chunk_size => p_chunk_size
2649 ,p_line_status => 'V'
2650 );
2651 end if;
2652
2653 ----------------------------------------------
2654 -- 5. Delete the range rows for this batch. --
2655 ----------------------------------------------
2656 hr_data_pump.message('HR_PUMP_RANGES');
2657
2658 l_work_to_do := true;
2659 while l_work_to_do loop
2660 delete
2661 from hr_pump_ranges r
2662 where r.batch_id = p_batch_id
2663 and rownum <= p_chunk_size
2664 ;
2665 --
2666 purgeEOC
2667 (p_chunk_size => p_chunk_size
2668 ,p_work_to_do => l_work_to_do
2669 );
2670 end loop;
2671
2672 ------------------------------------------------------------
2673 -- 6. Delete the batch header or update the batch status. --
2674 ------------------------------------------------------------
2675 hr_data_pump.message('HR_PUMP_BATCH_HEADERS');
2676
2677 delete from hr_pump_requests
2678 where batch_id = p_batch_id;
2679 if p_delete_header then
2680
2681 --
2682 -- Only do the delete if there are no remaining batch lines.
2683 --
2684 select count(*)
2685 into l_count
2686 from hr_pump_batch_lines
2687 where batch_id = p_batch_id
2688 ;
2689
2690 if l_count = 0 then
2691 delete
2692 from hr_pump_batch_headers
2693 where batch_id = p_batch_id
2694 ;
2695 else
2696 update hr_pump_batch_headers
2697 set batch_status = 'C'
2698 where batch_id = p_batch_id;
2699 end if;
2700 else
2701 update hr_pump_batch_headers
2702 set batch_status = 'C'
2703 where batch_id = p_batch_id;
2704 end if;
2705 --
2706 commit;
2707
2708 ---------------------------------------
2709 -- Write success message to the log. --
2710 ---------------------------------------
2711 hr_utility.set_message(800, 'HR_33794_DP_COMPLETED_BATCH');
2712 hr_utility.set_message_token('BATCH_NAME', l_batch_name);
2713 l_message := hr_utility.get_message;
2714 if p_write_log then
2718 hr_data_pump.message(l_message);
2715 fnd_file.put_line(fnd_file.log, l_message);
2716 end if;
2717
2719
2720 exception
2721 when others then
2722 hr_utility.set_message(800, 'HR_33795_DP_PROCESSING_ERROR');
2723 hr_utility.set_message_token('BATCH_NAME', l_batch_name);
2724 hr_utility.set_message_token('ERROR_MESSAGE', sqlerrm);
2725 l_message := hr_utility.get_message;
2726 p_error_message := l_message;
2727 if p_write_log then
2728 fnd_file.put_line(fnd_file.log, l_message);
2729 end if;
2730
2731 hr_data_pump.message(l_message);
2732
2733 --
2734 -- Reset the batch header.
2735 --
2736 update hr_pump_batch_headers
2737 set batch_status = 'E'
2738 where batch_id = p_batch_id;
2739 --
2740 commit;
2741 end purgebatch;
2742
2743 ------------------------------- purgeslave ------------------------------------
2744 procedure purgeslave
2745 (errbuf out nocopy varchar2
2746 ,retcode out nocopy number
2747 ,p_batch_id in number default null
2748 ,p_all_batches in varchar2 default 'N'
2749 ,p_preserve_user_keys in varchar2 default 'N'
2750 ,p_purge_unprocessed in varchar2 default 'Y'
2751 ,p_purge_errored in varchar2 default 'Y'
2752 ,p_purge_completed in varchar2 default 'Y'
2753 ,p_delete_header in varchar2 default 'Y'
2754 ,p_chunk_size in number
2755 ,p_thread_number in number
2756 ,p_threads in number
2757 ,p_pap_group_id in number
2758 ,p_lower_bound in number
2759 ,p_upper_bound in number
2760 ) is
2761 l_all_batches boolean;
2762 l_preserve_user_keys boolean;
2763 l_purge_unprocessed boolean;
2764 l_purge_errored boolean;
2765 l_purge_completed boolean;
2766 l_delete_header boolean;
2767 l_error boolean;
2768 l_error_message fnd_new_messages.message_text%type;
2769 --
2770 -- Cursor to restrict the processed batches.
2771 --
2772 cursor csr_batches
2773 (p_lower_bound in number
2774 ,p_upper_bound in number
2775 ) is
2776 select h.batch_id
2777 from hr_pump_batch_headers h
2778 where h.batch_id between
2779 p_lower_bound and p_upper_bound
2780 and h.batch_status <> 'P'
2781 ;
2782 begin
2783 --
2784 -- Start by assuming success.
2785 --
2786 errbuf := null;
2787 retcode := 0;
2788 l_error := false;
2789
2790 ----------------------------
2791 -- 1. Process parameters. --
2792 ----------------------------
2793
2794 --
2795 -- Set action_parameter_group_id. This must be done before any
2796 -- code that accesses PAY_ACTION_PARAMETERS.
2797 --
2798 pay_core_utils.set_pap_group_id(p_pap_group_id => p_pap_group_id);
2799
2800 --
2801 -- Process debugging options.
2802 --
2803 set_debug;
2804
2805 hr_data_pump.message('THREAD_NUMBER:' || to_char(p_thread_number));
2806 hr_data_pump.message('LOWER_BOUND:' || to_char(p_lower_bound));
2807 hr_data_pump.message('UPPER_BOUND:' || to_char(p_upper_bound));
2808
2809 -- Note: interpret as restrictively as possible.
2810 l_all_batches := upper(p_all_batches) = 'Y';
2811 l_preserve_user_keys := upper(p_preserve_user_keys) <> 'N';
2812 l_purge_unprocessed := upper(p_purge_unprocessed) = 'Y';
2813 l_purge_errored := upper(p_purge_errored) = 'Y';
2814 l_purge_completed := upper(p_purge_completed) = 'Y';
2815 l_delete_header := upper(p_delete_header) = 'Y';
2816
2817 -------------------------------
2818 -- 2. Process the batch(es). --
2819 -------------------------------
2820 if l_all_batches then
2821 for crec in csr_batches
2822 (p_lower_bound => p_lower_bound
2823 ,p_upper_bound => p_upper_bound
2824 ) loop
2825 message('ALL_THREADS:BATCH_ID:' || to_char(crec.batch_id));
2826
2827 purgebatch
2828 (p_batch_id => crec.batch_id
2829 ,p_chunk_size => p_chunk_size
2830 ,p_write_log => true
2831 ,p_preserve_user_keys => l_preserve_user_keys
2832 ,p_purge_unprocessed => l_purge_unprocessed
2833 ,p_purge_errored => l_purge_errored
2834 ,p_purge_completed => l_purge_completed
2835 ,p_delete_header => l_delete_header
2836 ,p_error_message => l_error_message
2837 );
2838
2839 --
2840 -- Only set return error status for the first error encountered.
2841 --
2842 if not l_error and l_error_message is not null then
2843 l_error := true;
2844 retcode := 2;
2845 errbuf := l_error_message;
2846 end if;
2847 end loop;
2848 elsif p_batch_id is not null then
2849 purgebatch
2850 (p_batch_id => p_batch_id
2851 ,p_chunk_size => p_chunk_size
2852 ,p_write_log => true
2853 ,p_preserve_user_keys => l_preserve_user_keys
2854 ,p_purge_unprocessed => l_purge_unprocessed
2855 ,p_purge_errored => l_purge_errored
2856 ,p_purge_completed => l_purge_completed
2857 ,p_delete_header => l_delete_header
2858 ,p_error_message => l_error_message
2859 );
2860
2861 --
2862 -- Check and set the return error status.
2863 --
2864 if l_error_message is not null then
2865 retcode := 2;
2866 errbuf := l_error_message;
2867 end if;
2868 elsif not l_preserve_user_keys then
2869 purgeorphanuserkeys
2870 (p_chunk_size => p_chunk_size
2871 ,p_thread_number => p_thread_number
2875 ,p_upper_bound => p_upper_bound
2872 ,p_threads => p_threads
2873 ,p_write_log => true
2874 ,p_lower_bound => p_lower_bound
2876 ,p_error_message => l_error_message
2877 );
2878
2879 --
2880 -- Check and set the return error status.
2881 --
2882 if l_error_message is not null then
2883 retcode := 2;
2884 errbuf := l_error_message;
2885 end if;
2886 end if;
2887
2888 exception
2889 when others then
2890 errbuf := sqlerrm;
2891 retcode := 2;
2892 end purgeslave;
2893
2894 -------------------------------- allocwork ------------------------------------
2895 /*
2896 NAME
2897 allocwork
2898 DESCRIPTION
2899 Calculates the upper and lower values each slave has to process.
2900
2901 p_lower, p_upper are set to NULL if calculated p_lower > p_max.
2902
2903 Otherwise, p_lower and p_upper are allocated values between p_min and
2904 p_max.
2905 NOTES
2906 Expects that p_max >= p_min, and p_increment > 0.
2907 */
2908 procedure allocwork
2909 (p_increment in number
2910 ,p_thread_no in number
2911 ,p_min in number
2912 ,p_max in number
2913 ,p_lower out nocopy number
2914 ,p_upper out nocopy number
2915 ) is
2916 l_upper number;
2917 l_lower number;
2918 begin
2919 --
2920 -- Calculate the lower value.
2921 --
2922 l_lower := p_min + (p_thread_no * p_increment);
2923
2924 --
2925 -- Case 1: Calculated lower is in range.
2926 --
2927 if l_lower <= p_max then
2928 l_upper := l_lower + p_increment - 1;
2929 if l_upper > p_max then
2930 l_upper := p_max;
2931 end if;
2932 --
2933 -- Case 2: Calculated lower value is out of range.
2934 --
2935 else
2936 l_lower := null;
2937 l_upper := null;
2938 end if;
2939
2940 p_lower := l_lower;
2941 p_upper := l_upper;
2942
2943 end allocwork;
2944 -------------------------------- purgemain ------------------------------------
2945 procedure purgemain
2946 (errbuf out nocopy varchar2
2947 ,retcode out nocopy number
2948 ,p_batch_id in number default null
2949 ,p_all_batches in varchar2 default 'N'
2950 ,p_preserve_user_keys in varchar2 default 'N'
2951 ,p_purge_unprocessed in varchar2 default 'Y'
2952 ,p_purge_errored in varchar2 default 'Y'
2953 ,p_purge_completed in varchar2 default 'Y'
2954 ,p_delete_header in varchar2 default 'Y'
2955 ,p_pap_group_id in number default null
2956 ) is
2957 l_chunk_size number;
2958 l_threads number;
2959 l_increment number;
2960 l_all_batches varchar2(32);
2961 l_batch_count number;
2962 l_request_id number;
2963 l_found boolean;
2964 l_min number;
2965 l_max number;
2966 l_lower number;
2967 l_upper number;
2968 --
2969 -- Find the maximum and minimum BATCH_ID from HR_PUMP_BATCH_HEADERS. The
2970 -- queries utilise the INDEX FULL SCAN (MIN/MAX) optimization.
2971 --
2972 cursor csr_bh_minmax is
2973 select A.maximum
2974 , B.minimum
2975 from (select max(batch_id) maximum from hr_pump_batch_headers) A
2976 , (select min(batch_id) minimum from hr_pump_batch_headers) B
2977 ;
2978 --
2979 -- Find maximum and minimum USER_KEY_ID from HR_PUMP_BATCH_LINE_USER_KEYS.
2980 -- This is for case where BATCH_LINE_ID IS NULL but don't include
2981 -- BATCH_LINE_ID to avoid FULL TABLE SCAN.
2982 --
2983 cursor csr_uk_minmax is
2984 select A.maximum
2985 , B.minimum
2986 from (select max(user_key_id) maximum from hr_pump_batch_line_user_keys) A
2987 , (select min(user_key_id) minimum from hr_pump_batch_line_user_keys) B
2988 ;
2989 begin
2990 --
2991 -- Start by assuming success.
2992 --
2993 errbuf := null;
2994 retcode := 0;
2995
2996 ----------------------------
2997 -- 1. Process parameters. --
2998 ----------------------------
2999
3000 --
3001 -- Set action_parameter_group_id. This must be done before any
3002 -- code that accesses PAY_ACTION_PARAMETERS.
3003 --
3004 pay_core_utils.set_pap_group_id(p_pap_group_id => p_pap_group_id);
3005
3006 --
3007 -- Process debugging options.
3008 --
3009 set_debug;
3010
3011 get_action_parameter
3012 (p_para_name => 'DATA_PUMP_PURGE_CHUNK_SIZE'
3013 ,p_para_value => l_chunk_size
3014 ,p_found => l_found
3015 );
3016 if(not l_found or l_chunk_size < 1 or l_chunk_size > 5000) then
3017 l_chunk_size := PURGE_SIZE_DEFAULT;
3018 end if;
3019
3020 get_action_parameter
3021 (p_para_name => 'THREADS'
3022 ,p_para_value => l_threads
3023 ,p_found => l_found
3024 );
3025 if(not l_found or l_threads < 1 or l_threads > 100) then
3026 l_threads := THREADS_DEFAULT;
3027 end if;
3028
3029 --
3030 -- Single batch overrides all batches.
3031 --
3032 if p_batch_id is null and upper(p_all_batches) = 'Y' then
3033 l_all_batches := 'Y';
3034 else
3035 l_all_batches := 'N';
3036 end if;
3037
3038 hr_data_pump.message('P_BATCH_ID:' || to_char(p_batch_id));
3039 hr_data_pump.message('P_ALL_BATCHES:' || l_all_batches);
3040 hr_data_pump.message('P_PRESERVE_USER_KEYS:' || p_preserve_user_keys);
3041 hr_data_pump.message('P_PURGE_UNPROCESSED:' || p_purge_unprocessed);
3042 hr_data_pump.message('P_PURGE_ERRORED:' || p_purge_errored);
3043 hr_data_pump.message('P_PURGE_COMPLETED:' || p_purge_completed);
3044 hr_data_pump.message('P_DELETE_HEADER:' || p_delete_header);
3045 hr_data_pump.message('P_PAP_GROUP_ID:' || p_pap_group_id);
3046 hr_data_pump.message('THREADS:' || to_char(l_threads));
3047 hr_data_pump.message('CHUNK_SIZE:' || to_char(l_chunk_size));
3048
3049 --
3050 -- Exit if there is nothing to do.
3051 --
3052 if p_all_batches = 'N' and p_batch_id is null and
3053 upper(p_preserve_user_keys) <> 'N' then
3054 hr_data_pump.message('NOTHING TO PURGE:ARGS');
3055 return;
3056 end if;
3057
3058 -----------------------------------------------
3059 -- 2. Allocate threads and run if necessary. --
3060 -----------------------------------------------
3061 if l_all_batches = 'Y' or p_batch_id is null then
3062
3063 --
3064 -- Case 1: Purging all batches.
3065 --
3066 if l_all_batches = 'Y' then
3067 open csr_bh_minmax;
3068 fetch csr_bh_minmax
3069 into l_max
3070 , l_min
3071 ;
3072 if csr_bh_minmax%notfound or l_max is null then
3073 close csr_bh_minmax;
3074 hr_data_pump.message('NOTHING TO PURGE:BATCHES');
3075 return;
3076 end if;
3077 close csr_bh_minmax;
3078
3079 --
3080 -- Reduce the number of threads if there are more batches than
3081 -- threads.
3082 --
3083 l_batch_count := l_max + 1 - l_min;
3084 if l_threads > l_batch_count then
3085 l_threads := ceil(l_batch_count / 2);
3086
3087 hr_data_pump.message('THREADS(MODIFIED):BATCHES:' || to_char(l_threads));
3088 end if;
3089
3090 l_increment := ceil((l_max + 1 - l_min) / l_threads);
3091
3092 hr_data_pump.message('INCREMENT:BATCHES:' || to_char(l_increment));
3093
3094 --
3095 -- Case 2: Purging user keys not connected with any batch.
3096 --
3097 elsif upper(p_preserve_user_keys) = 'N' then
3098 open csr_uk_minmax;
3099 fetch csr_uk_minmax
3100 into l_max
3101 , l_min
3102 ;
3103 if csr_uk_minmax%notfound or l_max is null then
3104 close csr_uk_minmax;
3105 hr_data_pump.message('NOTHING TO PURGE:USER_KEYS');
3106 return;
3107 end if;
3108 close csr_uk_minmax;
3109
3110 --
3111 -- Let each thread process PURGE_MIN_ROWS rows.
3112 --
3113 l_batch_count := ceil((l_max + 1 - l_min) / PURGE_MIN_ROWS);
3114 if l_threads > l_batch_count then
3115 l_threads := l_batch_count;
3116
3117 hr_data_pump.message('THREADS(MODIFIED):USER_KEYS:' || to_char(l_threads));
3118 end if;
3119
3120 l_increment := ceil((l_max + 1 - l_min) / l_threads);
3121
3122 hr_data_pump.message('INCREMENT:USER_KEYS:' || to_char(l_increment));
3123 end if;
3124
3125 --
3126 -- Start off the slave threads.
3127 --
3128 for thread in 1 .. l_threads - 1 loop
3129 hr_data_pump.message('FND_REQUEST.SUBMIT_REQUEST: ' || thread);
3130
3131 allocwork
3132 (p_increment => l_increment
3133 ,p_thread_no => thread
3134 ,p_min => l_min
3135 ,p_max => l_max
3136 ,p_lower => l_lower
3137 ,p_upper => l_upper
3138 );
3139
3140 --
3141 -- No need to proceed further as this and higher threads have no more work to do.
3142 --
3143 if l_lower is null then
3144 hr_data_pump.message('NO MORE THREADS REQUIRED:' || to_char(thread));
3145 exit;
3146 end if;
3147
3148 l_request_id :=
3149 fnd_request.submit_request
3150 (application => 'PER'
3151 ,program => 'DATAPUMP_PURGE_SLAVE'
3152 ,sub_request => FALSE
3153 ,argument1 => to_char(null)
3154 ,argument2 => p_all_batches
3155 ,argument3 => p_preserve_user_keys
3156 ,argument4 => p_purge_unprocessed
3157 ,argument5 => p_purge_errored
3158 ,argument6 => p_purge_completed
3159 ,argument7 => p_delete_header
3160 ,argument8 => to_char(l_chunk_size)
3161 ,argument9 => to_char(thread)
3162 ,argument10 => to_char(l_threads)
3163 ,argument11 => to_char(p_pap_group_id)
3164 ,argument12 => to_char(l_lower)
3165 ,argument13 => to_char(l_upper)
3166 );
3167 end loop;
3168 --
3169 -- Single batch will only have a single thread.
3170 --
3171 else
3172 l_threads := 1;
3173
3174 --
3175 -- Indicate that allocwork is not to be called.
3176 --
3177 l_increment := null;
3178 end if;
3179
3180 -------------------------------
3181 -- 3. Execute slave process. --
3182 -------------------------------
3183
3184 if l_increment is not null then
3185 allocwork
3186 (p_increment => l_increment
3187 ,p_thread_no => 0
3188 ,p_min => l_min
3189 ,p_max => l_max
3190 ,p_lower => l_lower
3191 ,p_upper => l_upper
3192 );
3193 end if;
3194
3195 purgeslave
3196 (errbuf => errbuf
3197 ,retcode => retcode
3198 ,p_batch_id => p_batch_id
3199 ,p_all_batches => l_all_batches
3200 ,p_preserve_user_keys => p_preserve_user_keys
3201 ,p_purge_unprocessed => p_purge_unprocessed
3202 ,p_purge_errored => p_purge_errored
3203 ,p_purge_completed => p_purge_completed
3204 ,p_delete_header => p_delete_header
3205 ,p_chunk_size => l_chunk_size
3206 ,p_thread_number => 0
3207 ,p_threads => l_threads
3208 ,p_pap_group_id => p_pap_group_id
3209 ,p_lower_bound => l_lower
3210 ,p_upper_bound => l_upper
3211 );
3212
3213 exception
3214 when others then
3215 if csr_bh_minmax%isopen then
3216 close csr_bh_minmax;
3217 end if;
3218
3219 if csr_uk_minmax%isopen then
3220 close csr_uk_minmax;
3221 end if;
3222
3223 errbuf := sqlerrm;
3224 retcode := 2;
3225 end purgemain;
3226
3227 /*
3228 * Data Pump initialisation section.
3229 */
3230 begin
3231 -- Any package init here.
3232 null;
3233
3234 end hr_data_pump;
[Home] [Help]