1 PACKAGE BODY hri_opl_multi_thread AS
2 /* $Header: hriomthd.pkb 120.8 2007/01/18 07:24:56 anmajumd noship $ */
3 --
4 -- Main Entry Point from Concurren Manager
5 --
6 g_chunk NUMBER := 20;
7 g_slave_count NUMBER := 8;
8 g_errbuf VARCHAR2(2000);
9 g_retcode NUMBER;
10 --
11 -- Global variable used for caching mthd_range_id
12 --
13 g_mthd_range_id NUMBER;
14 --
15 -- Variable for setting the debug mode
16 --
17 g_debug_flag VARCHAR2(30) := NVL(fnd_profile.value('HRI_ENBL_DTL_LOG'),'N');
18 g_program VARCHAR2(30) ;
19 g_mode VARCHAR2(30) := 'N';
20 g_mthd_action_id NUMBER;
21 g_error_request_id NUMBER;
22 --
23 -- Table Type for storing the request_id's for all child threads.
24 --
25 TYPE g_num_tab is table of NUMBER INDEX BY BINARY_INTEGER;
26 --
27 -- Table for storing the concurrent request ID's
28 --
29 g_child_request_tab g_num_tab;
30 --
31 -- EXCEPTIONS
32 --
33 error_launching_thread EXCEPTION;
34 no_sql_returned EXCEPTION;
35 no_ranges_to_process EXCEPTION;
36 invalid_program EXCEPTION;
37 --
38 PRAGMA EXCEPTION_INIT(invalid_program, -6550);
39 --
40 -- -----------------------------------------------------------------------------
41 -- Procedure Call Sequence (Forward Declaration)
42 -- The PROCESS rotine is invoked from the concurrent manager. The following
43 -- procedure are called sequence to multithreading the collection process
44 -- by the process routine
45 --
46 PROCEDURE process;
47 --
48 -- 1. Start Multithreading Process
49 --
50 PROCEDURE do_multithreading;
51 --
52 -- 1.1 Generate Multi Thread Action
53 --
54 PROCEDURE gen_multi_thread_action;
55 --
56 -- 1.2 Generate Object Ranges (public in package spec)
57 --
58 -- PROCEDURE gen_object_range(g_mthd_action_array.mthd_action_id);
59 --
60 -- 1.3 Start Threads
61 --
62 PROCEDURE start_threads;
63 --
64 -- 2. Process Range
65 --
66 PROCEDURE process_range(p_program IN VARCHAR2,
67 p_mthd_action_id IN NUMBER,
68 p_errbuf OUT NOCOPY VARCHAR2,
69 p_retcode OUT NOCOPY NUMBER );
70 --
71 -- 3. Wait for Child Threads to complete Processing
72 --
73 PROCEDURE watch_child_processes(p_slave_errored out nocopy boolean);
74 --
75 -- 4. End Multithreading Process
76 --
77 PROCEDURE end_multithreading;
78 --
79 -- 4.1 Run the post process
80 --
81 PROCEDURE run_program_post_process;
82 --
83 -- 4.2 Update Multithread Action Status
84 --
85 PROCEDURE set_action_status;
86 --
87 PROCEDURE set_range_error(p_mthd_action_id NUMBER);
88 --
89 -- -----------------------------------------------------------------------------
90 -- Inserts row into concurrent program log
91 -- -----------------------------------------------------------------------------
92 --
93 PROCEDURE output(p_text VARCHAR2) IS
94 --
95 BEGIN
96 --
97 -- Bug 4105868: Collection Diagnostic
98 --
99 HRI_BPL_CONC_LOG.output(p_text);
100 --
101 END output;
102 --
103 -- -----------------------------------------------------------------------------
104 -- Inserts row into concurrent program log if debugging is enabled
105 -- -----------------------------------------------------------------------------
106 --
107 PROCEDURE dbg(p_text VARCHAR2) IS
108 --
109 BEGIN
110 --
111 -- Bug 4105868: Collection Diagnostic
112 --
113 HRI_BPL_CONC_LOG.dbg(p_text);
114 --
115 END dbg;
116 --
117 -- ----------------------------------------------------------------------------
118 -- Runs given sql statement dynamically
119 -- ----------------------------------------------------------------------------
120 --
121 PROCEDURE run_sql_stmt_noerr( p_sql_stmt VARCHAR2 )
122 IS
123 --
124 BEGIN
125 --
126 dbg(p_sql_stmt);
127 --
128 EXECUTE IMMEDIATE p_sql_stmt;
129 --
130 EXCEPTION
131 --
132 WHEN OTHERS THEN
133 --
134 output('Error running sql:');
135 output(SUBSTR(p_sql_stmt,1,230));
136 --
137 --
138 END run_sql_stmt_noerr;
139 --
140 -- ----------------------------------------------------------------------------
141 -- Procedure : GET_NEXT_MTHD_RANGE_ID
142 -- Description : This procedure is used for classifying the object records into
143 -- a range. Based on this classification the object records are
144 -- grouped into ranges. This logic is that for every nth rownum
145 -- the function will get a new range id otherwise it will return
146 -- the value in cache. Here n is the chunk size option value.
147 -- ----------------------------------------------------------------------------
148 --
149 FUNCTION get_next_mthd_range_id
150 (p_rownum number
151 ,p_chunk number
152 )
153 RETURN NUMBER IS
154 --
155 BEGIN
156 --
157 -- only when the rownum is a multiple of the chunk read a seqence
158 -- otherwise return the previous range stored in the cache.
159 --
160 IF mod(p_rownum,p_chunk) = 0 OR
161 g_mthd_range_id is null THEN
162 --
163 select hri_adm_mthd_ranges_s.nextval
164 into g_mthd_range_id
165 from dual;
166 --
167 END IF;
168 --
169 RETURN g_mthd_range_id;
170 --
171 END get_next_mthd_range_id;
172 --
173 -- ----------------------------------------------------------------------------
174 -- Procedure : GET_ERROR_REQUEST
175 -- Description : This procedure populate the global variable with the request_id
176 -- of the erroring thread
177 -- ----------------------------------------------------------------------------
178 --
179 FUNCTION get_error_request(p_mthd_action_id NUMBER)
180 RETURN NUMBER IS
181 --
182 l_error_request_id NUMBER;
183 --
184 CURSOR c_error_request IS
185 SELECT err_thread_request_id
186 FROM hri_adm_mthd_ranges
187 WHERE mthd_action_id = p_mthd_action_id
188 AND status = 'ERROR'
189 AND rownum = 1;
190 --
191 BEGIN
192 --
193 IF g_error_request_id is null THEN
194 --
195 OPEN c_error_request;
196 FETCH c_error_request INTO l_error_request_id;
197 CLOSE c_error_request;
198 --
199 END IF;
200 --
201 RETURN l_error_request_id;
202 --
203 END get_error_request;
204 --
205 -- ----------------------------------------------------------------------------
206 -- Waits for previously submitted multi-thread processes within same request
207 -- set to complete before continuing with the current one
208 -- ----------------------------------------------------------------------------
209 --
210 PROCEDURE wait_for_other_mthds IS
211
212 -- Return count of unfinished multi-threading processes
213 -- that have been submitted before the current process
214 -- within the same request set
215 CURSOR mtmasters_cur IS
216 SELECT count(req.request_id) count
217 FROM fnd_concurrent_programs cp
218 ,fnd_executables ex
219 ,fnd_concurrent_requests req
220 WHERE ex.executable_id = cp.executable_id
221 AND cp.concurrent_program_id = req.concurrent_program_id
222 AND ex.application_id = cp.EXECUTABLE_APPLICATION_ID
223 AND cp.application_id = req.PROGRAM_APPLICATION_ID
224 -- Restrict to non-complete multi-threading processes
225 AND ex.executable_name = 'HRI_OPL_MULTI_THREAD'
226 AND req.PHASE_CODE <> 'C'
227 -- Requests previously submitted within the same request set
228 AND req.request_id IN
229 (SELECT req1.request_id
230 FROM fnd_concurrent_requests req1
231 WHERE req1.priority_request_id =
232 -- Subquery for requests in request set
233 (SELECT req2.priority_request_id
234 FROM fnd_concurrent_requests req2
235 WHERE req2.request_id = fnd_global.conc_request_id)
236 -- Filter on previously submitted requests only
237 AND req1.request_id < fnd_global.conc_request_id);
238
239 l_priority_count NUMBER;
240 l_sleep NUMBER;
241
242 BEGIN
243
244 -- Take a count of the requests of higher priority at hand
245 OPEN mtmasters_cur;
246 FETCH mtmasters_cur INTO l_priority_count;
247 CLOSE mtmasters_cur;
248
249 -- Author : SMOHAPAT
250 -- IF there are requests of higher priority
251 -- THEN Sleep and dont start the process at hand,
252 -- A l_priority_count = 0 means this is the request to be run immediately.
253 -- A l_priority_count > 0 means its not of higher priority.
254 -- So wait for higher priority requests to run and hibernate the one at hand.
255 WHILE (l_priority_count > 0) LOOP
256
257 l_sleep := dbms_pipe.receive_message
258 (pipename => 'non-existant pipe to force timeout',
259 timeout => 20);
260
261 -- Is the higher priority req. count zero now, take a poll here.
262 OPEN mtmasters_cur;
263 FETCH mtmasters_cur INTO l_priority_count;
264 CLOSE mtmasters_cur;
265
266 END LOOP;
267
268 END wait_for_other_mthds;
269 --
270 --
271 -- ----------------------------------------------------------------------------
272 -- Procedure : WAIT_FOR_LOWER_LEVELS
273 -- Description : Checks processing is complete for lower hierarchy levels
274 -- ----------------------------------------------------------------------------
275 --
276 PROCEDURE wait_for_lower_levels(p_mthd_action_id IN NUMBER
277 ,p_mthd_range_lvl_order IN NUMBER) IS
278
279 CURSOR lower_hrchy_req_cur IS
280 SELECT count(*)
281 FROM hri_adm_mthd_ranges
282 WHERE mthd_action_id = p_mthd_action_id
283 AND status IN ('UNPROCESSED','PROCESSING')
284 AND mthd_range_lvl_order < p_mthd_range_lvl_order;
285
286 l_lower_hrchy_count NUMBER;
287 l_sleep VARCHAR2(3000);
288
289 BEGIN
290
291 -- Take a count of the requests for lower hierarchy levels still in progress
292 OPEN lower_hrchy_req_cur;
293 FETCH lower_hrchy_req_cur INTO l_lower_hrchy_count;
294 CLOSE lower_hrchy_req_cur;
295
296 -- If any are found, enter a wait loop until they are complete
297 WHILE (l_lower_hrchy_count > 0) LOOP
298
299 l_sleep := dbms_pipe.receive_message
300 (pipename => 'non-existant pipe to force timeout',
301 timeout => 10);
302
303 -- Recheck until the requests are completed
304 OPEN lower_hrchy_req_cur;
305 FETCH lower_hrchy_req_cur INTO l_lower_hrchy_count;
306 CLOSE lower_hrchy_req_cur;
307
308 END LOOP;
309
310 END wait_for_lower_levels;
311 --
312 -- ----------------------------------------------------------------------------
313 -- Procedure : PROCESS
314 -- Description : Overloaded procedure which acts as the main controller for the
315 -- utility. It calls the various procedure for performing different
316 -- task.
317 -- ----------------------------------------------------------------------------
318 --
319 PROCEDURE process
320 IS
321 --
322 l_request_data VARCHAR2(240);
323 l_slave_errored BOOLEAN;
324 --
325 BEGIN
326 --
327 -- Wait for any other multi-threading processes previously submitted
328 -- in the same request set to complete
329 --
330 wait_for_other_mthds;
331 --
332 -- Bug 5023754, removing the alter session statement
333 --
334 -- execute immediate 'ALTER SESSION ENABLE PARALLEL DML';
335 --
336 BEGIN
337 --
338 -- 1. Start Multithreading Process
339 -- Start the multithreading process by performing the following tasks
340 -- a. Invoke the program pre processor
341 -- b. create the object ranges
342 -- c. spawn the child threads
343 --
344 -- If there are no ranges created then the no_ranges_to_process
345 -- exception will be raised. In that case no processing is required
346 -- but the post_process should still be called
347 -- if the pre-processor does not return any SQL, then no processing
348 -- is required and also the post processor need not be called.
349 --
350 do_multithreading;
351 dbg('done do_multithreading, calling process_range');
352 --
353 -- 2. Process Range
354 --
355 process_range(p_program => g_program
356 ,p_mthd_action_id => g_mthd_action_id
357 ,p_errbuf => g_errbuf
358 ,p_retcode => g_retcode) ;
359 dbg('done process_range, calling watch_child_processes');
360 --
361 -- 3. Wait for Child Threads to complete Processing
362 --
363 watch_child_processes(l_slave_errored);
364 --
365 EXCEPTION
366 --
367 WHEN no_ranges_to_process THEN
368 --
369 -- There are no ranges to be processed, therefore only call the post_process
370 --
371 dbg('The program pre_processor returned a SQL, for which no range could be created.');
372 null;
373 WHEN no_sql_returned THEN
374 --
375 -- The pre processor may not return a SQL when the processing is done by it. This may be
376 -- required in case of processes which have a special processing logic in Foundation HR
377 -- mode. The following should happen in such a case
378 -- 1. No error should be raised
379 -- 2. the post processing routine should not be invoked.
380 -- Raise an error which will be handled at the end of this procedure. This will prevent
381 -- the call to post_processor and the process will not error out.
382 --
383 RAISE;
384 END;
385 --
386 dbg('Finished processing ranges, determining how to end the process');
387 --
388 IF l_slave_errored THEN
389 --
390 -- One of the child thread encountered an error, an error should be raised
391 --
392 dbg('An error condition was encountered in one of the child process');
393 --
394 -- Get the request id of the erroring thread
395 --
396 g_error_request_id := GET_ERROR_REQUEST(p_mthd_action_id => g_mthd_action_id);
397 --
398 fnd_message.set_name('HRI','HRI_MLT_OTHER_THREAD_IN_ERR');
399 fnd_message.set_token('REQUEST_ID',g_error_request_id);
400 RAISE other_thread_in_error;
401 --
402 ELSE
403 --
404 -- 4. End Multithreading Process
405 --
406 dbg('calling end_multithreading');
407 end_multithreading;
408 --
409 END IF;
410 --
411 dbg('Done processing exiting now');
412 --
413 EXCEPTION
414 WHEN no_sql_returned THEN
415 --
416 dbg('Inside no_sql_returned exceotion..Not doing anything');
417 RETURN;
418 --
419 -- Any other exception should be handled the the calling routine
420 --
421 END PROCESS;
422 --
423 -- ----------------------------------------------------------------------------
424 -- 1. Start Multithreading Process
425 -- Procedure : DO_MULTITHREADING
426 -- Description : This procedure does the tast of starting the Multithreading
427 -- Process. It invokes the process to call ther
428 -- Pre Processor
429 -- Create the Ranges based on the SQL returned by the the pre_process
430 -- Start the Child threads for processing the ranges
431 -- ----------------------------------------------------------------------------
432 --
433 PROCEDURE do_multithreading
434 IS
435 --
436 l_process_action_id NUMBER;
437 l_from_date DATE;
438 l_to_date DATE;
439 l_sql VARCHAR2(1000);
440 l_program VARCHAR2(100);
441 --
442 BEGIN
443 --
444 dbg('Inside do_multithreading');
445 --
446 -- 1.1 Generate Multi Thread Action
447 --
448 gen_multi_thread_action;
449 --
450 -- 1.2 Generate Object Ranges
451 --
452 gen_object_range(g_mthd_action_array.mthd_action_id);
453 --
454 -- 1.3 Start Threads
455 --
456 start_threads;
457 --
458 COMMIT;
459 --
460 dbg('Exiting do_multithreading');
461 --
462 END do_multithreading;
463 --
464 -- ----------------------------------------------------------------------------
465 -- 1.1 Generate Multi Thread Action
466 -- Procedure : GEN_MULTI_THREAD_ACTION
467 -- Description : This procedure inserts the parameters passed to the utility
468 -- by the concurrent request, into the action table.
469 -- ----------------------------------------------------------------------------
470 --
471 PROCEDURE gen_multi_thread_action IS
472 --
473 -- curosor to get the short name of the concurrent program
474 -- which is being run
475 --
476 CURSOR c_process_name IS
477 SELECT concurrent_program_name
478 FROM fnd_concurrent_programs prg,
479 fnd_concurrent_requests req
480 WHERE prg.concurrent_program_id = req.concurrent_program_id
481 AND req.request_id = fnd_global.conc_request_id;
482 --
483 l_process_name VARCHAR2(30);
484 --
485 BEGIN
486 --
487 -- mthd_action_id for the current process is not defined. A new row will have to be created
488 -- in hri_adm_mthd_action for this process
489 --
490 IF hr_general.chk_product_installed(800) = 'FALSE' THEN
491 --
492 g_mthd_action_array.foundation_hr_flag := 'Y';
493 --
494 ELSIF NVL(fnd_profile.value('HRI_DBI_FORCE_SHARED_HR'),'N') = 'Y' THEN
495 --
496 g_mthd_action_array.foundation_hr_flag := 'Y';
497 --
498 ELSE
499 --
500 g_mthd_action_array.foundation_hr_flag := 'N';
501 --
502 END IF;
503 --
504 -- Get the short name of the concurrent program that is running
505 --
506 OPEN c_process_name;
507 FETCH c_process_name INTO l_process_name;
508 CLOSE c_process_name;
509 --
510 -- Initialize the record
511 --
512 g_mthd_action_array.request_id := fnd_global.conc_request_id;
513 g_mthd_action_array.debug_flag := g_debug_flag;
514 g_mthd_action_array.process_name := l_process_name;
515 --
516 INSERT into HRI_ADM_MTHD_ACTIONS
517 ( MTHD_ACTION_ID,
518 PROGRAM,
519 REQUEST_ID,
520 COLLECT_FROM_DATE,
521 COLLECT_TO_DATE,
522 BUSINESS_GROUP_ID,
523 FULL_REFRESH_FLAG,
524 DEBUG_FLAG,
525 FOUNDATION_HR_FLAG,
526 HIERARCHICAL_PROCESS_FLAG,
527 HIERARCHICAL_PROCESS_TYPE,
528 ATTRIBUTE1,
529 ATTRIBUTE2,
530 ATTRIBUTE3,
531 ATTRIBUTE4,
532 ATTRIBUTE5,
533 ATTRIBUTE6,
534 ATTRIBUTE7,
535 ATTRIBUTE8,
536 ATTRIBUTE9,
537 ATTRIBUTE10,
538 ATTRIBUTE11,
539 ATTRIBUTE12,
540 ATTRIBUTE13,
541 ATTRIBUTE14,
542 ATTRIBUTE15,
543 ATTRIBUTE16,
544 ATTRIBUTE17,
545 ATTRIBUTE18,
546 ATTRIBUTE19,
547 ATTRIBUTE20,
548 STATUS ,
549 START_TIME ,
550 END_TIME ,
551 PROCESS_TYPE ,
552 PROCESS_NAME)
553 VALUES
554 (hri_adm_mthd_actions_s.nextval,
555 g_mthd_action_array.PROGRAM,
556 g_mthd_action_array.REQUEST_ID,
557 g_mthd_action_array.COLLECT_FROM_DATE,
558 g_mthd_action_array.COLLECT_TO_DATE,
559 g_mthd_action_array.BUSINESS_GROUP_ID,
560 g_mthd_action_array.FULL_REFRESH_FLAG,
561 g_mthd_action_array.DEBUG_FLAG,
562 g_mthd_action_array.FOUNDATION_HR_FLAG,
563 g_mthd_action_array.HIERARCHICAL_PROCESS_FLAG,
564 g_mthd_action_array.HIERARCHICAL_PROCESS_TYPE,
565 g_mthd_action_array.ATTRIBUTE1,
566 g_mthd_action_array.ATTRIBUTE2,
567 g_mthd_action_array.ATTRIBUTE3,
568 g_mthd_action_array.ATTRIBUTE4,
569 g_mthd_action_array.ATTRIBUTE5,
570 g_mthd_action_array.ATTRIBUTE6,
571 g_mthd_action_array.ATTRIBUTE7,
572 g_mthd_action_array.ATTRIBUTE8,
573 g_mthd_action_array.ATTRIBUTE9,
574 g_mthd_action_array.ATTRIBUTE10,
575 g_mthd_action_array.ATTRIBUTE11,
576 g_mthd_action_array.ATTRIBUTE12,
577 g_mthd_action_array.ATTRIBUTE13,
578 g_mthd_action_array.ATTRIBUTE14,
579 g_mthd_action_array.ATTRIBUTE15,
580 g_mthd_action_array.ATTRIBUTE16,
581 g_mthd_action_array.ATTRIBUTE17,
582 g_mthd_action_array.ATTRIBUTE18,
583 g_mthd_action_array.ATTRIBUTE19,
584 g_mthd_action_array.ATTRIBUTE20,
585 g_mthd_action_array.STATUS ,
586 g_mthd_action_array.START_TIME ,
587 g_mthd_action_array.END_TIME ,
588 g_mthd_action_array.PROCESS_TYPE ,
589 g_mthd_action_array.PROCESS_NAME)
590 RETURNING mthd_action_id INTO g_mthd_action_id;
591 --
592 g_mthd_action_array.mthd_action_id := g_mthd_action_id;
593 --
594 EXCEPTION
595 WHEN others THEN
596 RAISE;
597 END gen_multi_thread_action;
598 --
599 -- ----------------------------------------------------------------------------
600 -- 1.2 Generate Object Ranges
601 -- Procedure : GEN_OBJECT_RANGE
602 -- Description : This procedure invokes the program pre_processor and generates
603 -- the object range based on the SQL returned by the pre-processor
604 -- ----------------------------------------------------------------------------
605 --
606 PROCEDURE gen_object_range (p_mthd_action_id NUMBER)
607 IS
608 --
609 l_dyn_pre_process_sql varchar2(4000);
610 l_dyn_range_sql varchar2(4000);
611 l_sqlstr varchar2(2000);
612 l_mthd_action_id NUMBER;
613 --
614 BEGIN
615 dbg('inside gen_object_range');
616 --
617 --
618 --
619 l_mthd_action_id := nvl(p_mthd_action_id,
620 g_mthd_action_array.mthd_action_id);
621 --
622 -- Read the profile value HRI:Multithreading Chunk Size
623 --
624 g_chunk := NVL(fnd_profile.value('HRI_MTHD_CHUNK_SIZE'),20);
625 dbg('chunk size = '||g_chunk);
626 --
627 -- Create the dynamic SQL for calling the pre_processor of the program.
628 -- Based on the sql returned by the pre_process the ranges will be created.
629 -- However, if no SQL is returned, the processing should be stopped without
630 -- returning any error. This is for supporting cases when the program does
631 -- not need to do any processing e.g. Assignment events in shared HR mode
632 --
633 l_dyn_pre_process_sql := 'BEGIN '||
634 g_program||'.PRE_PROCESS(:l_mthd_action_id, :l_sqlstr);
635 END;';
636 --
637 dbg('l_dyn_pre_process_sql='||l_dyn_pre_process_sql);
638 --
639 EXECUTE IMMEDIATE l_dyn_pre_process_sql
640 USING p_mthd_action_id,
641 OUT l_sqlstr;
642 --
643 -- The pre_processor can update some of the action column so repopulate the
644 -- action array
645 --
646 g_mthd_action_array := get_mthd_action_array(p_mthd_action_id => l_mthd_action_id);
647 --
648 IF l_sqlstr is null THEN
649 --
650 -- The program did not return any SQL. The processing has already be done therefore
651 -- skip creating the ranges
652 -- skip creating the Child threads for processing the ranges
653 -- skip calling the process_range
654 -- skip calling the post_process
655 -- Raise an exception which will be handled in the calling procedure to gracefully
656 -- skip all the above listed steps
657 --
658 raise_application_error(-20996,'Multi threading Pre Processor did not return any SQL');
659 --
660 END IF;
661 --
662 -- Create the dynamic sql for creating the multithread ranges.
663 -- This SQL will create the ranges based on the SQL returned by the pre-processor
664 --
665 IF (g_mthd_action_array.hierarchical_process_flag = 'Y' AND
666 g_mthd_action_array.hierarchical_process_type = 'TOP_DOWN') THEN
667
668 l_dyn_range_sql :=
669 'INSERT /*+ append parallel(range, default,default) */ INTO
670 hri_adm_mthd_ranges range
671 (mthd_range_id
672 ,mthd_range_lvl
673 ,mthd_range_lvl_order
674 ,mthd_action_id
675 ,start_object_id
676 ,end_object_id
677 ,status)
678 SELECT
679 mthd_range_id
680 ,object_lvl
681 ,object_lvl
682 ,:mthd_action_id
683 ,min(object_id)
684 ,max(object_id)
685 ,''UNPROCESSED''
686 FROM
687 (SELECT
688 object_lvl + CEIL(ROWNUM / :chunk_size) mthd_range_id
689 ,object_lvl
690 ,object_id
691 FROM
692 (SELECT object_id, object_lvl
693 FROM (' || l_sqlstr || ')
694 ORDER BY object_lvl, object_id))
695 GROUP BY
696 mthd_range_id
697 ,object_lvl';
698
699 ELSIF (g_mthd_action_array.hierarchical_process_flag = 'Y' AND
700 g_mthd_action_array.hierarchical_process_type = 'BOTTOM_UP') THEN
701
702 l_dyn_range_sql :=
703 'INSERT /*+ append parallel(range, default,default) */ INTO
704 hri_adm_mthd_ranges range
705 (mthd_range_id
706 ,mthd_range_lvl
707 ,mthd_range_lvl_order
708 ,mthd_action_id
709 ,start_object_id
710 ,end_object_id
711 ,status)
712 SELECT
713 mthd_range_id
714 ,object_lvl
715 ,0 - object_lvl
716 ,:mthd_action_id
717 ,min(object_id)
718 ,max(object_id)
719 ,''UNPROCESSED''
720 FROM
721 (SELECT
722 1000 - object_lvl + CEIL(ROWNUM / :chunk_size) mthd_range_id
723 ,object_lvl
724 ,object_id
725 FROM
726 (' || l_sqlstr || ')
727 )
728 GROUP BY
729 mthd_range_id
730 ,object_lvl';
731
732 ELSE
733
734 l_dyn_range_sql :=
735 'INSERT /*+ append parallel(range, default,default) */ INTO
736 hri_adm_mthd_ranges range
737 (mthd_range_id
738 ,mthd_range_lvl
739 ,mthd_range_lvl_order
740 ,mthd_action_id
741 ,start_object_id
742 ,end_object_id
743 ,status)
744 SELECT
745 mthd_range_id
746 ,1
747 ,1
748 ,:mthd_action_id
749 ,min(object_id)
750 ,max(object_id)
751 ,''UNPROCESSED''
752 FROM
753 (SELECT
754 CEIL(ROWNUM / :chunk_size) mthd_range_id
755 ,object_id
756 FROM
757 (' || l_sqlstr || ')
758 )
759 GROUP BY
760 mthd_range_id';
761
762 END IF;
763
764 dbg('l_dyn_range_sql='||l_dyn_range_sql);
765 --
766 -- Bug 5023754
767 --
768 COMMIT;
769 --
770 -- Enable parallel session
771 --
772 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
773 --
774 -- Run the dynamic SQL for creating the range
775 --
776 EXECUTE IMMEDIATE l_dyn_range_sql USING l_mthd_action_id, g_chunk;
777 --
778 COMMIT;
779 --
780 -- Disable parallel session
781 --
782 EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL DML';
783 --
784 dbg('exiting gen_object_range');
785 EXCEPTION
786 --
787 WHEN invalid_program THEN
788 --
789 dbg('Invalid program name passsed to the pre process='||g_program);
790 fnd_message.set_name('HRI','HRI_MLT_INVALID_PGM_NAME');
791 RAISE invalid_program;
792 --
793 WHEN OTHERS THEN
794 IF SQLCODE = - 20997 THEN
795 --
796 dbg('Invalid SQL returned by the pre process='||SQLCODE);
797 dbg(l_dyn_range_sql);
798 fnd_message.set_name('HRI', 'HRI_MLT_INVALID_SQL');
799 RAISE invalid_sql;
800 --
801 ELSIF SQLCODE = - 20996 THEN
802 --
803 dbg(fnd_message.get);
804 dbg('No SQL returned by the pre process=');
805 RAISE no_sql_returned;
806 --
807 ELSE
808 --
809 RAISE;
810 --
811 END IF;
812 --
813 END gen_object_range;
814 --
815 -- ----------------------------------------------------------------------------
816 -- 1.3 Start Threads
817 -- Procedure : START_THREAD
818 -- Description : This procedure spawns the child threads for processing the ranges
819 -- The number of threads to be spawned is controlled by the profile
820 -- HRI: Number of Threads to Launch. However, if the number of ranges
821 -- is less then the profile then only (no of ranges -1) threads will
822 -- be launched. -1 as the master thread also helps in processing
823 -- ----------------------------------------------------------------------------
824 --
825 PROCEDURE start_threads
826 IS
827 --
828 l_request_id NUMBER;
829 l_slave_count NUMBER;
830 --
831 CURSOR c_get_range_count IS
832 SELECT count(*)
833 FROM hri_adm_mthd_ranges
834 WHERE mthd_action_id = g_mthd_action_id;
835 --
836 BEGIN
837 dbg('Inside start_threads');
838 --
839 -- Read the profile value HRI:Number of Threads to Launch
840 --
841 g_slave_count := NVL(fnd_profile.value('HRI_NO_THRDS_LAUNCH'),8);
842 dbg('threads to launch = '||g_slave_count);
843 --
844 -- The number of threads to be launched is controlled by the profile option
845 -- HRI: Number of Threads to Launch. However in case the total
846 -- number of range records created is less than the value for this profile
847 -- then only limited number of threads should be started. This will avoid
848 -- situations where a thread doesn't get any ranges to process
849 --
850 OPEN c_get_range_count;
851 FETCH c_get_range_count into l_slave_count;
852 CLOSE c_get_range_count;
853 --
854 IF l_slave_count is null THEN
855 --
856 -- No range records have been created, so raise an error
857 --
858 RAISE no_ranges_to_process;
859 --
860 ELSIF l_slave_count = 1 THEN
861 --
862 -- There is only one range to be processed, which will be processed by
863 -- the master thread so do not launch any child threads.
864 --
865 RETURN;
866 --
867 ELSIF l_slave_count > g_slave_count THEN
868 --
869 -- There are a number of ranges to be processed, so start the threads
870 --
871 l_slave_count := g_slave_count;
872 --
873 ELSE
874 --
875 l_slave_count := l_slave_count - 1;
876 --
877 END IF;
878 --
879 -- Start the child processes
880 --
881 FOR l_count in 1..l_slave_count loop
882 --
883 l_request_id :=
884 fnd_request.submit_request
885 (
886 application => 'HRI'
887 ,program => 'HRI_MTHD_CHILD_PROCESS'
888 ,sub_request => FALSE -- Indicates that the request will not be
889 -- executed as a sub process.
890 ,argument1 => fnd_global.conc_request_id
891 ,argument2 => g_program
892 ,argument3 => g_mthd_action_id
893 ,argument4 => l_count + 1 -- worker_id
894 );
895 --
896 IF l_request_id = 0 then
897 --
898 -- If request submission failed, raise exception
899 --
900 dbg('Error launching thread.');
901 RAISE error_launching_thread;
902 --
903 ELSE
904 --
905 -- Store the request_id of the submitted request in an array. This
906 -- array is used in checking if all the child threads have finished
907 -- processing the range.
908 --
909 g_child_request_tab(l_count) := l_request_id;
910 --
911 END IF;
912 --
913 END LOOP;
914 --
915 dbg('Exiting start_threads');
916 --
917 EXCEPTION
918 --
919 WHEN others THEN
920 --
921 fnd_message.set_name('HRI', 'HRI_MLT_START_THREAD');
922 dbg('Exception encountered in starting thread');
923 dbg(sqlerrm);
924 RAISE;
925 --
926 END start_threads;
927 --
928 -- ----------------------------------------------------------------------------
929 -- 2. Process Range
930 -- Procedure : PROCESS_RANGE
931 -- Description : This procedure dynamically invokes the entry point in the
932 -- collection programs package to process the ranges.
933 -- ----------------------------------------------------------------------------
934 --
935 PROCEDURE process_range(p_program IN VARCHAR2,
936 p_mthd_action_id IN NUMBER,
937 p_errbuf OUT NOCOPY VARCHAR2,
938 p_retcode OUT NOCOPY NUMBER )
939 IS
940 --
941 l_step NUMBER;
942 l_dyn_sql VARCHAR2(4000);
943 l_lvl_param VARCHAR2(100);
944 --
945 BEGIN
946 --
947 dbg('inside process_range');
948 --
949 l_step := 10;
950 --
951 IF (g_mthd_action_array.hierarchical_process_flag = 'Y') THEN
952 l_lvl_param := '
953 ,p_mthd_range_lvl => l_mthd_range_lvl';
954 END IF;
955 --
956 -- Construct the dynamic query to invoke the process_range procedure
957 -- The dynamic SQL gets the next unprocessed range and invokes the
958 -- process_range procedure to process the range. The allocation of
959 -- next range is included in the dyn sql so that the session remains
960 -- the same and the child thread does not have to re-initialize
961 -- globals variables again.
962 --
963 dbg('processing p_mthd_action_id ='||p_mthd_action_id);
964 l_dyn_sql :=
965 'DECLARE
966 l_errbuf VARCHAR2(1000);
967 l_retcode NUMBER;
968 l_mthd_range_id NUMBER;
969 l_mthd_range_lvl NUMBER;
970 l_mthd_range_lvl_nxt NUMBER;
971 l_mthd_range_lvl_order NUMBER;
972 l_start_object_id NUMBER;
973 l_end_object_id NUMBER;
974 l_mthd_action_id NUMBER := '||p_mthd_action_id||';
975 BEGIN
976 LOOP
977 hri_opl_multi_thread.get_next_range
978 (p_mthd_action_id => l_mthd_action_id
979 ,p_mthd_range_id => l_mthd_range_id
980 ,p_mthd_range_lvl => l_mthd_range_lvl_nxt
981 ,p_mthd_range_lvl_order => l_mthd_range_lvl_order
982 ,p_start_object_id => l_start_object_id
983 ,p_end_object_id => l_end_object_id);
984 hri_opl_multi_thread.dbg(''l_mthd_range_id =''||l_mthd_range_id );
985 IF l_mthd_range_id is null THEN
986 EXIT;
987 ELSE
988 IF (l_mthd_range_lvl <> l_mthd_range_lvl_nxt) THEN
989 hri_opl_multi_thread.wait_for_lower_levels
990 (p_mthd_action_id => l_mthd_action_id
991 ,p_mthd_range_lvl_order => l_mthd_range_lvl_order);
992 END IF;
993 l_mthd_range_lvl := l_mthd_range_lvl_nxt;
994 '||p_program||'.process_range(
995 errbuf => :l_errbuf
996 ,retcode => :l_retcode
997 ,p_mthd_action_id => l_mthd_action_id
998 ,p_mthd_range_id => l_mthd_range_id' || l_lvl_param || '
999 ,p_start_object_id => l_start_object_id
1000 ,p_end_object_id => l_end_object_id);
1001 END IF;
1002 END LOOP;
1003 EXCEPTION
1004 WHEN OTHERS THEN
1005 RAISE;
1006 END;';
1007 --
1008 l_step := 20;
1009 dbg('process_range sql ='||l_dyn_sql);
1010 --
1011 -- execute the dynamic sql to process the ranges.
1012 --
1013 BEGIN
1014 --
1015 EXECUTE IMMEDIATE l_dyn_sql
1016 USING OUT p_errbuf,
1017 OUT p_retcode;
1018 --
1019 -- p_retcode = 0 means the process completed successfully
1020 -- p_retcode = 1 means that the process encountered a warning
1021 -- p_retcode > 2 means that the process encountered an ERROR.
1022 -- So if the retcode returned is is greater than 1 then raise an exception
1023 --
1024 dbg('finished processing the ranges');
1025 IF p_retcode >= 2 THEN
1026 --
1027 -- Store the error code in the global error buffer and raise the error
1028 --
1029 g_errbuf := p_errbuf;
1030 g_retcode := p_retcode;
1031 dbg('The process_range routine returned a error code of '||p_retcode);
1032 raise_application_error(-20998,'CHILD_PROCESS_FAILURE');
1033 --
1034 END IF;
1035 --
1036 EXCEPTION
1037 WHEN others THEN
1038 l_step := 30;
1039 --
1040 -- When an exception is raised, then the status of the ranges and the action
1041 -- records should changed to Error. However, this should not be done when
1042 -- the application error - 20999 is raised; done when a parallel thread
1043 -- encounters an error because of which the processing of this thread should stop.
1044 -- In such a case the set_range_error procedure should not be called.
1045 -- Why try to mark the ranges as error when it has already be done by
1046 -- the thread which encountered the error.
1047 --
1048 dbg('Error raised by the process, marking the ranges as error');
1049 IF SQLCODE <> - 20999 THEN
1050 --
1051 hri_opl_multi_thread.set_range_error(p_mthd_action_id);
1052 fnd_message.set_name('HRI','HRI_MLT_CHILD_THREAD_IN_ERR');
1053 --
1054 RAISE;
1055 -- raise_application_error(- 20998,'CHILD_PROCESS_FAILURE');
1056 --
1057 END IF;
1058 RAISE;
1059 --
1060 END;
1061 --
1062 dbg('exiting process_range');
1063 --
1064 EXCEPTION
1065 --
1066 WHEN invalid_program THEN
1067 --
1068 -- Raised when the package or package.procedure does not exist
1069 --
1070 dbg('Invalid program name passsed to process_range='||g_program);
1071 fnd_message.set_name('HRI','HRI_MLT_INVALID_PGM_NAME');
1072 --
1073 RAISE invalid_program;
1074 --
1075 WHEN others THEN
1076 --
1077 dbg('error in process_range ='||sqlerrm);
1078 dbg('SQLCODE = '||SQLCODE);
1079 --
1080 IF SQLCODE = - 20999 THEN
1081 --
1082 -- An error was encountered by a parallel thread, so all the child threads
1083 -- should stop processing and be marked as errored
1084 --
1085 dbg('An error condition was encountered in one of the child process');
1086 --
1087 -- Get the request id of the erroring thread
1088 --
1089 g_error_request_id := GET_ERROR_REQUEST(p_mthd_action_id => p_mthd_action_id);
1090 --
1091 fnd_message.set_name('HRI','HRI_MLT_OTHER_THREAD_IN_ERR');
1092 fnd_message.set_token('REQUEST_ID',g_error_request_id);
1093 RAISE other_thread_in_error;
1094 --
1095 END IF;
1096 --
1097 -- If any other exception is encountered, stop processing.
1098 --
1099 RAISE;
1100 --
1101 END process_range;
1102 --
1103 -- ----------------------------------------------------------------------------
1104 -- 3. Wait for Child Threads to complete Processing
1105 -- Procedure : WATCH_CHILD_PROCESSES
1106 -- Description : Wait for Child Threads to complete Processing (watch_child_processes)
1107 -- The master thread should not finish processing until all the
1108 -- child threads have finished processing. This procedure keeps a
1109 -- watch on all the child threads that have launched for processing.
1110 -- In case an error in encountered by any of the threads, the
1111 -- p_slave_errored will be returned as TRUE. The procedure also
1112 -- detects warnings raised by any child process, so that the master
1113 -- process also reports a warning
1114 -- ----------------------------------------------------------------------------
1115 --
1116 PROCEDURE watch_child_processes(p_slave_errored out nocopy boolean)
1117 IS
1118 --
1119 l_no_slaves boolean;
1120 l_poll_loops pls_integer;
1121 l_sleep NUMBER;
1122 --
1123 CURSOR c_slaves (c_request_id number) IS
1124 SELECT phase_code,
1125 status_code
1126 FROM fnd_concurrent_requests fnd
1127 WHERE fnd.request_id = c_request_id;
1128 --
1129 l_slaves c_slaves%rowtype;
1130 --
1131 BEGIN
1132 --
1133 dbg('Inside watch_child_processes');
1134 dbg(g_child_request_tab.count||' threads to watch for');
1135 --
1136 -- watch the child threads only if the process has spawned threads
1137 -- otherwise return
1138 --
1139 IF g_child_request_tab.count > 0 then
1140 --
1141 l_no_slaves := TRUE;
1142 --
1143 WHILE l_no_slaves LOOP
1144 --
1145 l_no_slaves := false;
1146 --
1147 FOR elenum in 1..g_child_request_tab.count LOOP
1148 --
1149 -- Open the cursor to determine if the request has completed processing
1150 --
1151 OPEN c_slaves (g_child_request_tab(elenum));
1152 FETCH c_slaves into l_slaves;
1153 CLOSE c_slaves;
1154 --
1155 IF l_slaves.phase_code <> 'C' THEN
1156 --
1157 l_no_slaves := TRUE;
1158 --
1159 END IF;
1160 --
1161 IF l_slaves.status_code = 'E' THEN
1162 --
1163 p_slave_errored := TRUE;
1164 g_error_request_id := g_child_request_tab(elenum);
1165 --
1166 ELSIF l_slaves.status_code = 'G' THEN
1167 --
1168 dbg('Warning raised by request '||g_child_request_tab(elenum));
1169 --
1170 g_errbuf := 'WARNING';
1171 g_retcode := 1;
1172 --
1173 END IF;
1174 --
1175 END LOOP;
1176 --
1177 -- Pause to avoid over polling of fnd_concurrent_requests
1178 --
1179 l_sleep := dbms_pipe.receive_message
1180 (pipename => 'non-existant pipe to force timeout',
1181 timeout => 10);
1182 --
1183 END LOOP;
1184 --
1185 END IF;
1186 --
1187 dbg('Exiting watch_child_processes');
1188 --
1189 EXCEPTION
1190 WHEN OTHERS THEN
1191 dbg('error in watch_child_processes'||SQLERRM);
1192 RAISE;
1193 END watch_child_processes;
1194 --
1195 -- ----------------------------------------------------------------------------
1196 -- 4. End Multithreading Process
1197 -- Procedure : WATCH_CHILD_PROCESSES
1198 -- Description : End Multithreading Process (end_multithreading) This procedure
1199 -- calls the post-processor to wind up the collection processing
1200 -- and updates status of the action record to PROCESSED
1201 -- ----------------------------------------------------------------------------
1202 --
1203 PROCEDURE end_multithreading
1204 IS
1205 --
1206 --
1207 BEGIN
1208 --
1209 dbg('Inside end_multithreading');
1210 --
1211 -- 4.1 Run the post process
1212 --
1213 run_program_post_process;
1214 --
1215 -- 4.2 Update Multithread Action Status
1216 --
1217 set_action_status;
1218 --
1219 dbg('Exiting end_multithreading');
1220 --
1221 EXCEPTION
1222 --
1223 WHEN invalid_program THEN
1224 --
1225 dbg('Invalid program name passsed to the post process='||g_program);
1226 fnd_message.set_name('HRI','HRI_MLT_INVALID_PGM_NAME');
1227 raise invalid_program;
1228 --
1229 WHEN others THEN
1230 --
1231 dbg('Exception encountered in running the post processing');
1232 dbg(sqlerrm);
1233 RAISE;
1234 --
1235 END end_multithreading;
1236 --
1237 -- ----------------------------------------------------------------------------
1238 -- 4.1 Run Program Post-Processor
1239 -- Procedure : RUN_PROGRAM_POST_PROCESS
1240 -- Description : This procdure invokes the post_process of the collection
1241 -- program
1242 -- ----------------------------------------------------------------------------
1243 --
1244 PROCEDURE run_program_post_process
1245 IS
1246 --
1247 l_sql VARCHAR2(1000);
1248 --
1249 BEGIN
1250 --
1251 dbg('inside run_program_post_process');
1252 --
1253 -- Build the dynamic SQL
1254 --
1255 l_sql := 'BEGIN '||g_program ||'.POST_PROCESS('||g_mthd_action_id||'); END;';
1256 --
1257 dbg('post processing sql is ='||l_sql );
1258 --
1259 EXECUTE IMMEDIATE l_sql;
1260 --
1261 dbg('exiting run_program_post_process');
1262 --
1263 END run_program_post_process;
1264 --
1265 -- ----------------------------------------------------------------------------
1266 -- 4.2 Update Multithread Action Status
1267 -- Procedure : SET_ACTION_STATUS
1268 -- Description : This procdure marks the status of the action record to
1269 -- processed
1270 -- ----------------------------------------------------------------------------
1271 --
1272 PROCEDURE set_action_status
1273 IS
1274 BEGIN
1275 --
1276 dbg('inside set_action_status');
1277 --
1278 -- Update the status of the mulithread action as processed
1279 --
1280 UPDATE hri_adm_mthd_actions
1281 SET end_time = SYSDATE,
1282 status = 'PROCESSED'
1283 WHERE mthd_action_id = g_mthd_action_id;
1284 --
1285 dbg('exiting set_action_status');
1286 --
1287 END set_action_status;
1288 --
1289 -- ----------------------------------------------------------------------------
1290 -- 7.1.1 PROCESS
1291 -- Procedure : PROCESS
1292 -- Description : Main Entry Point for the Multithreading Utility. The concurrent
1293 -- program will invoke this program for processing. The procedure
1294 -- puts all the parameters passed to it into an array and calls the
1295 -- overloaded PROCESS procedure
1296 -- ----------------------------------------------------------------------------
1297 --
1298 PROCEDURE process (errbuf OUT NOCOPY VARCHAR2,
1299 retcode OUT NOCOPY NUMBER,
1300 p_program IN VARCHAR2,
1301 p_business_group_id IN NUMBER,
1302 p_collect_from_date IN VARCHAR2,
1303 p_collect_to_date IN VARCHAR2,
1304 p_full_refresh_flag IN VARCHAR2,
1305 p_hierarchical_process IN VARCHAR2 DEFAULT 'N',
1306 p_hierarchical_type IN VARCHAR2 DEFAULT NULL,
1307 p_attribute1 IN VARCHAR2 DEFAULT NULL,
1308 p_attribute2 IN VARCHAR2 DEFAULT NULL,
1309 p_attribute3 IN VARCHAR2 DEFAULT NULL,
1310 p_attribute4 IN VARCHAR2 DEFAULT NULL,
1311 p_attribute5 IN VARCHAR2 DEFAULT NULL,
1312 p_attribute6 IN VARCHAR2 DEFAULT NULL,
1313 p_attribute7 IN VARCHAR2 DEFAULT NULL,
1314 p_attribute8 IN VARCHAR2 DEFAULT NULL,
1315 p_attribute9 IN VARCHAR2 DEFAULT NULL,
1316 p_attribute10 IN VARCHAR2 DEFAULT NULL,
1317 p_attribute11 IN VARCHAR2 DEFAULT NULL,
1318 p_attribute12 IN VARCHAR2 DEFAULT NULL,
1319 p_attribute13 IN VARCHAR2 DEFAULT NULL,
1320 p_attribute14 IN VARCHAR2 DEFAULT NULL,
1321 p_attribute15 IN VARCHAR2 DEFAULT NULL,
1322 p_attribute16 IN VARCHAR2 DEFAULT NULL,
1323 p_attribute17 IN VARCHAR2 DEFAULT NULL,
1324 p_attribute18 IN VARCHAR2 DEFAULT NULL,
1325 p_attribute19 IN VARCHAR2 DEFAULT NULL,
1326 p_attribute20 IN VARCHAR2 DEFAULT NULL)
1327 IS
1328 --
1329 l_slave_errored BOOLEAN;
1330 l_hr_installed VARCHAR2(30); -- Stores HR installed or not
1331 l_frc_shrd_hr_prfl_val VARCHAR2(30); -- store Profile HRI:DBI Force Foundation HR Processes
1332 l_message fnd_new_messages.message_text%type;
1333 --
1334 BEGIN
1335 dbg('Starting HRI Multithreading Utility');
1336 --
1337 -- Debugging on the process is enabled by profile HRI:Enable Detailed Logging
1338 --
1339 g_program := p_program;
1340 --
1341 g_mthd_action_array.program := p_program;
1342 g_mthd_action_array.collect_from_date := fnd_date.canonical_to_date(p_collect_from_date);
1343 g_mthd_action_array.collect_to_date := fnd_date.canonical_to_date(p_collect_to_date);
1344 g_mthd_action_array.business_group_id := p_business_group_id;
1345 g_mthd_action_array.full_refresh_flag := p_full_refresh_flag;
1346 g_mthd_action_array.hierarchical_process_flag := p_hierarchical_process;
1347 g_mthd_action_array.hierarchical_process_type := p_hierarchical_type;
1348 g_mthd_action_array.attribute1 := p_attribute1;
1349 g_mthd_action_array.attribute2 := p_attribute2;
1350 g_mthd_action_array.attribute3 := p_attribute3;
1351 g_mthd_action_array.attribute4 := p_attribute4;
1352 g_mthd_action_array.attribute5 := p_attribute5;
1353 g_mthd_action_array.attribute6 := p_attribute6;
1354 g_mthd_action_array.attribute7 := p_attribute7;
1355 g_mthd_action_array.attribute8 := p_attribute8;
1356 g_mthd_action_array.attribute9 := p_attribute9;
1357 g_mthd_action_array.attribute10 := p_attribute10;
1358 g_mthd_action_array.attribute11 := p_attribute11;
1359 g_mthd_action_array.attribute12 := p_attribute12;
1360 g_mthd_action_array.attribute13 := p_attribute13;
1361 g_mthd_action_array.attribute14 := p_attribute14;
1362 g_mthd_action_array.attribute15 := p_attribute15;
1363 g_mthd_action_array.attribute16 := p_attribute16;
1364 g_mthd_action_array.attribute17 := p_attribute17;
1365 g_mthd_action_array.attribute18 := p_attribute18;
1366 g_mthd_action_array.attribute19 := p_attribute19;
1367 g_mthd_action_array.attribute20 := p_attribute20;
1368 g_mthd_action_array.status := 'PROCESSING';
1369 g_mthd_action_array.start_time := SYSDATE;
1370 --
1371 -- Call the overridden procedure to start the processing
1372 --
1373 process;
1374 --
1375 -- In case a child thread end with a warning or error, the
1376 -- master thread should end with a similiar note
1377 --
1378 IF (g_errbuf IS NOT NULL) THEN
1379 errbuf := g_errbuf;
1380 retcode := NVL(g_retcode,0);
1381 END IF;
1382 --
1383 dbg('Exiting HRI Multithreading Utility');
1384 --
1385 EXCEPTION
1386 WHEN invalid_program OR
1387 other_thread_in_error OR
1388 error_launching_thread OR
1389 invalid_sql
1390 THEN
1391 --
1392 -- These are standard errors so don't raise an exception
1393 --
1394 errbuf := NVL(g_errbuf,'HRI_MTHD_THREAD_IN_ERR');
1395 retcode := 2;
1396 --
1397 -- The master process should not complete before the child process
1398 -- have finished
1399 --
1400 watch_child_processes(l_slave_errored);
1401 --
1402 l_message := nvl(fnd_message.get,sqlerrm);
1403 --
1404 output(l_message);
1405 --
1406 -- Bug 4105868: Collection Diagnostics
1407 --
1408 hri_bpl_conc_log.log_process_info
1409 (p_msg_type => 'ERROR'
1410 ,p_package_name => p_program
1411 ,p_msg_group => 'MLTTHRDNG'
1412 ,p_msg_sub_group => 'PROCESS'
1413 ,p_sql_err_code => SQLCODE
1414 ,p_note => l_message
1415 );
1416 --
1417 hri_bpl_conc_log.log_process_end
1418 (p_status => FALSE
1419 ,p_period_from => hr_general.start_of_time
1420 ,p_period_to => hr_general.end_of_time
1421 ,p_package_name => p_program
1422 );
1423 --
1424 WHEN others THEN
1425 --
1426 -- Unknown exception encountered, so raise an error
1427 --
1428 errbuf := NVL(g_errbuf,SQLERRM);
1429 retcode := 2;
1430 --
1431 -- The master process should not complete before the child process
1432 -- have finished
1433 --
1434 watch_child_processes(l_slave_errored);
1435 --
1436 l_message := nvl(fnd_message.get,sqlerrm);
1437 --
1438 output(l_message);
1439 --
1440 -- Bug 4105868: Collection Diagnostics
1441 --
1442 hri_bpl_conc_log.log_process_info
1443 (p_msg_type => 'ERROR'
1444 ,p_package_name => p_program
1445 ,p_msg_group => 'MLTTHRDNG'
1446 ,p_msg_sub_group => 'PROCESS'
1447 ,p_sql_err_code => SQLCODE
1448 ,p_note => l_message
1449 );
1450 --
1451 hri_bpl_conc_log.log_process_end
1452 (p_status => FALSE
1453 ,p_period_from => hr_general.start_of_time
1454 ,p_period_to => hr_general.end_of_time
1455 ,p_package_name => p_program
1456 );
1457 --
1458 END process;
1459 --
1460 -- ----------------------------------------------------------------------------
1461 -- 7.1.2 Get Next Range
1462 -- Procedure : GET_NEXT_RANGE
1463 -- Description : This process acts as an interface between the child process
1464 -- and the range table. The process performs the following task
1465 -- 1) Provide the process with the next Unprocessed object range.
1466 -- 2) Maintain the status of object ranges that are currently being
1467 -- processed by the processes.
1468 -- 3) Update the status of the object range that has been processed
1469 -- by the child process.
1470 -- 4) In case a child process encounters an error, it returns the
1471 -- error code of 1 to the requesting process. The child process
1472 -- should then stop processing and error out.
1473 -- ----------------------------------------------------------------------------
1474 --
1475 PROCEDURE get_next_range(p_mthd_action_id IN NUMBER
1476 ,p_mthd_range_id IN OUT NOCOPY NUMBER
1477 ,p_mthd_range_lvl OUT NOCOPY NUMBER
1478 ,p_mthd_range_lvl_order OUT NOCOPY NUMBER
1479 ,p_start_object_id OUT NOCOPY NUMBER
1480 ,p_end_object_id OUT NOCOPY NUMBER
1481 ,p_mode IN VARCHAR2 default 'N')
1482 IS
1483 --
1484 l_error_status NUMBER;
1485 --
1486 CURSOR c_error IS
1487 SELECT -1
1488 FROM hri_adm_mthd_ranges
1489 WHERE status = 'ERROR'
1490 AND mthd_action_id = p_mthd_action_id
1491 AND rownum = 1;
1492 --
1493 BEGIN
1494 --
1495 -- If the p_mthd_range_id will store the previous range id, which has been processed
1496 -- by the child thread, so mark that range as PROCESSED
1497 --
1498 IF p_mthd_range_id is not null THEN
1499 --
1500 UPDATE hri_adm_mthd_ranges
1501 SET status = 'PROCESSED'
1502 WHERE status = 'PROCESSING'
1503 AND mthd_action_id = p_mthd_action_id
1504 AND mthd_range_id = p_mthd_range_id;
1505 --
1506 p_mthd_range_id := null;
1507 --
1508 END IF;
1509 --
1510 -- Get the next available range for processing
1511 --
1512 UPDATE hri_adm_mthd_ranges
1513 SET status = 'PROCESSING',
1514 request_id = fnd_global.conc_request_id
1515 WHERE status = 'UNPROCESSED'
1516 AND mthd_action_id = p_mthd_action_id
1517 AND mthd_range_id =
1518 (SELECT MIN(sub.mthd_range_id)
1519 FROM hri_adm_mthd_ranges sub
1520 WHERE sub.status = 'UNPROCESSED'
1521 AND sub.mthd_action_id = p_mthd_action_id)
1522 RETURNING
1523 mthd_range_id
1524 ,mthd_range_lvl
1525 ,mthd_range_lvl_order
1526 ,start_object_id
1527 ,end_object_id
1528 INTO
1529 p_mthd_range_id
1530 ,p_mthd_range_lvl
1531 ,p_mthd_range_lvl_order
1532 ,p_start_object_id
1533 ,p_end_object_id;
1534 --
1535 -- Commit the transactions here, this will ensure when a error is
1536 -- encountered by some other thread, the processesing done by
1537 -- the current thread are commited before an error is raised.
1538 --
1539 COMMIT;
1540 --
1541 --
1542 -- If no range is available for processing, then it could be that
1543 -- a child thread encountered an error. In which, case an
1544 -- exception should be raised and the processing should stop there.
1545 --
1546 IF p_mthd_range_id is null THEN
1547 --
1548 OPEN c_error;
1549 FETCH c_error INTO l_error_status;
1550 CLOSE c_error;
1551 --
1552 IF l_error_status = -1 THEN
1553 --
1554 p_mthd_range_id := -1;
1555 p_start_object_id := -1;
1556 p_end_object_id := -1;
1557 --
1558 -- raise an exception
1559 --
1560 dbg('error encountered in get_next_range, other_process_failure');
1561 raise_application_error(- 20999,'other_process_failure');
1562 --
1563 END IF;
1564 --
1565 END IF;
1566 --
1567 END get_next_range;
1568 --
1569 -- ----------------------------------------------------------------------------
1570 -- 7.1.3 Set Range in Error
1571 -- Procedure : SET_ERROR_STATUS
1572 -- Description : This procedure will be invoked by the child thread that
1573 -- encountered an error. It updates the marks the action and
1574 -- unprocessed ranges as Error
1575 -- ----------------------------------------------------------------------------
1576 --
1577 PROCEDURE set_range_error(p_mthd_action_id NUMBER)
1578 IS
1579 --
1580 --
1581 BEGIN
1582 dbg('Inside set_range_error');
1583 --
1584 -- Mark the object range as Error
1585 --
1586 UPDATE hri_adm_mthd_ranges
1587 SET status = 'ERROR',
1588 err_thread_request_id = fnd_global.conc_request_id
1589 WHERE mthd_action_id = p_mthd_action_id
1590 --
1591 -- The records which have not been processed
1592 --
1593 AND (status = 'UNPROCESSED' OR
1594 --
1595 -- The records which which was being processed by the thread, when the error
1596 -- was raised
1597 --
1598 (status = 'PROCESSING'
1599 AND request_id = fnd_global.conc_request_id));
1600 --
1601 dbg(sql%rowcount||' range records marked as Error');
1602 --
1603 -- Change the status action record to ERROR
1604 --
1605 UPDATE hri_adm_mthd_actions
1606 SET status = 'ERROR'
1607 WHERE mthd_action_id = p_mthd_action_id;
1608 --
1609 commit;
1610 --
1611 dbg('exiting set_range_error');
1612 --
1613 END set_range_error;
1614 --
1615 -- ----------------------------------------------------------------------------
1616 -- 7.1.5 Process Range (Entry Point for Child thread processes)
1617 -- Procedure : PROCESS_RANGE
1618 -- Description : This procedure is called by the concurrent manager. It control
1619 -- the child thread processing. It invokes the overloaded
1620 -- process_range procedure
1621 -- ----------------------------------------------------------------------------
1622 --
1623 PROCEDURE process_range(
1624 errbuf OUT NOCOPY VARCHAR2
1625 ,retcode OUT NOCOPY NUMBER
1626 ,p_master_request_id IN NUMBER
1627 ,p_program IN VARCHAR2
1628 ,p_mthd_action_id IN NUMBER
1629 ,p_worker_id IN NUMBER)
1630 IS
1631 --
1632 l_dyn_sql varchar2(4000);
1633 --
1634 BEGIN
1635 --
1636 g_mthd_action_array := get_mthd_action_array(p_mthd_action_id);
1637 --
1638 process_range(p_program => p_program
1639 ,p_mthd_action_id => p_mthd_action_id
1640 ,p_errbuf => errbuf
1641 ,p_retcode => retcode);
1642 --
1643 EXCEPTION
1644 WHEN invalid_program OR other_thread_in_error THEN
1645 --
1646 -- These are standard errors so don't raise an exception
1647 --
1648 errbuf := NVL(g_errbuf,SQLERRM);
1649 retcode := NVL(g_retcode,2);
1650 output(fnd_message.get);
1651 --
1652 -- Bug 4105868: Collection Diagnostics
1653 --
1654 hri_bpl_conc_log.log_process_end
1655 (p_status => FALSE
1656 ,p_period_from => hr_general.start_of_time
1657 ,p_period_to => hr_general.end_of_time
1658 );
1659 --
1660 WHEN others THEN
1661 --
1662 -- Unknown exception encountered, so raise an error
1663 --
1664 dbg('An error encountered while processing');
1665 errbuf := SQLERRM;
1666 retcode := 2;
1667 output(fnd_message.get);
1668 output(SQLERRM);
1669 --
1670 -- Collection Diagnostics
1671 --
1672 hri_bpl_conc_log.log_process_end
1673 (p_status => FALSE
1674 ,p_period_from => hr_general.start_of_time
1675 ,p_period_to => hr_general.end_of_time
1676 );
1677 --
1678 END process_range;
1679 --
1680 -- ----------------------------------------------------------------------------
1681 -- 7.1.6 Get multithread action array
1682 -- Procedure : GET_MTHD_ACTION_ARRAY
1683 -- This function can be called by the collection programs to populate
1684 -- the multithreading action arrays
1685 -- ----------------------------------------------------------------------------
1686 --
1687 FUNCTION get_mthd_action_array(p_mthd_action_id IN NUMBER)
1688 RETURN hri_adm_mthd_actions%rowtype
1689 IS
1690 --
1691 CURSOR c_adm_mthd_action IS
1692 SELECT *
1693 FROM HRI_ADM_MTHD_ACTIONS
1694 WHERE mthd_action_id = p_mthd_action_id;
1695 --
1696 BEGIN
1697 --
1698 OPEN c_adm_mthd_action;
1699 FETCH c_adm_mthd_action into g_mthd_action_array;
1700 CLOSE c_adm_mthd_action;
1701 --
1702 -- Populate the method action id global variable.
1703 --
1704 g_mthd_action_id := g_mthd_action_array.mthd_action_id;
1705 --
1706 RETURN g_mthd_action_array;
1707 --
1708 END get_mthd_action_array;
1709 --
1710 -- Fix for bug 4043240
1711 -- ------------------------------------------------------------------------------
1712 -- FUNCTION get_current_mthd_action_id fetches the mthd_action_id for the invoking
1713 -- process. If it does not exist, a new mthd_action_id is created and returned
1714 -- ------------------------------------------------------------------------------
1715 --
1716 FUNCTION get_mthd_action_id(p_program IN VARCHAR2,
1717 p_start_time IN DATE)
1718 RETURN NUMBER
1719 IS
1720 --
1721 l_process_name VARCHAR2(30);
1722 --
1723 BEGIN
1724 --
1725 dbg('In get_mthd_action_id');
1726 --
1727 -- Return the mthd action id of the current process if it is already defined
1728 --
1729 IF g_mthd_action_id IS NOT NULL THEN
1730 --
1731 RETURN g_mthd_action_id;
1732 --
1733 END IF;
1734 --
1735 -- The mthd action id of the current process is not defined.
1736 -- A new record will be created in hri_adm_mthd_actions
1737 -- corresponding to this process
1738 --
1739 -- store the program name and start time of the process
1740 --
1741 g_mthd_action_array.status := 'PROCESSING';
1742 g_mthd_action_array.program := p_program;
1743 g_mthd_action_array.start_time := p_start_time;
1744 --
1745 -- Insert the record in the multi threading table
1746 --
1747 gen_multi_thread_action;
1748 --
1749 dbg('Now g_mthd_action_id='||g_mthd_action_id);
1750 --
1751 --
1752 RETURN g_mthd_action_id;
1753 --
1754 END get_mthd_action_id;
1755 --
1756 -- Updates the multi-threading parameters
1757 PROCEDURE update_parameters(p_mthd_action_id IN NUMBER,
1758 p_full_refresh IN VARCHAR2,
1759 p_global_start_date IN DATE) IS
1760
1761 BEGIN
1762
1763 -- If a full refresh then set the flag and update the
1764 -- collect from date
1765 IF (p_full_refresh = 'Y') THEN
1766
1767 UPDATE hri_adm_mthd_actions
1768 SET full_refresh_flag = 'Y'
1769 ,collect_from_date = p_global_start_date
1770 WHERE mthd_action_id = p_mthd_action_id;
1771
1772 -- Otherwise just set the full refresh flag
1773 ELSE
1774
1775 UPDATE hri_adm_mthd_actions
1776 SET full_refresh_flag = 'N'
1777 WHERE mthd_action_id = p_mthd_action_id;
1778
1779 END IF;
1780
1781 -- Commit
1782 commit;
1783
1784 END update_parameters;
1785 --
1786 -- ------------------------------------------------------------------------------
1787 -- Returns worker id
1788 -- ------------------------------------------------------------------------------
1789 FUNCTION get_worker_id RETURN NUMBER IS
1790
1791 l_worker_id NUMBER;
1792 l_argument4 VARCHAR2(240);
1793
1794 CURSOR worker_csr IS
1795 SELECT
1796 req.argument4
1797 FROM
1798 fnd_concurrent_requests req
1799 ,fnd_concurrent_programs prg
1800 WHERE req.concurrent_program_id = prg.concurrent_program_id
1801 AND req.program_application_id = prg.application_id
1802 AND prg.concurrent_program_name = 'HRI_MTHD_CHILD_PROCESS'
1803 AND req.request_id = fnd_global.conc_request_id;
1804
1805 BEGIN
1806
1807 OPEN worker_csr;
1808 FETCH worker_csr INTO l_argument4;
1809 CLOSE worker_csr;
1810
1811 l_worker_id := to_number(l_argument4);
1812
1813 RETURN NVL(l_worker_id, 1);
1814
1815 EXCEPTION WHEN OTHERS THEN
1816
1817 RETURN 1;
1818
1819 END get_worker_id;
1820 --
1821 END HRI_OPL_MULTI_THREAD;
[Home] [Help]