1 PACKAGE BODY hr_dm_master AS
2 /* $Header: perdmmas.pkb 120.0 2005/05/31 17:11:52 appldev noship $ */
3
4
5 /*-------------------------- PRIVATE ROUTINES ----------------------------*/
6
7 -- ------------------------- controller_init ------------------------
8 -- Description: Various initialization processes are undertaken:
9 -- a) ensuring that data for the migration exists in hr_dm_migrations
10 -- b) entries in hr_dm_migration_requests are marked as inactive
11 -- c) r_migration_data is seeded with information about the migration
12 -- d) the migration count is incremented
13 -- e) the validity of the migration is checked
14 -- f) the migration status is set to started
15 --
16 --
17 -- Input Parameters
18 -- p_migration_id - migration id
19 --
20 -- r_migration_data - record containing migration information
21 --
22 -- p_request_data - concurrent request data (contains previous
23 -- phase code - if it was a concurrent phase)
24 --
25 --
26 -- Output Parameters
27 -- r_migration_data - record containing migration information
28 --
29 --
30 -- ------------------------------------------------------------------------
31
32 --
33 PROCEDURE controller_init(p_migration_id IN NUMBER,
34 r_migration_data IN OUT
35 hr_dm_utility.r_migration_rec,
36 p_request_data IN VARCHAR2) IS
37 --
38
39 l_current_phase_status VARCHAR2(30);
40 l_migration_type VARCHAR2(30);
41 l_application_id NUMBER;
42 l_migration_id NUMBER;
43 l_database_name VARCHAR2(30);
44 l_source_database_name VARCHAR2(30);
45 l_destination_database_name VARCHAR2(30);
46 l_last_migrated_date DATE;
47 l_phase_flag VARCHAR2(1);
48 l_migration_count NUMBER;
49 l_business_group_id NUMBER;
50 l_valid_migration VARCHAR2(1);
51 e_fatal_error EXCEPTION;
52 l_fatal_error_message VARCHAR2(200);
53 l_database_location VARCHAR2(30);
54 l_phase_name VARCHAR2(30);
55 l_status VARCHAR2(30);
56 l_migration_date DATE;
57
58
59 CURSOR csr_migration IS
60 SELECT MIGRATION_ID, UPPER(SOURCE_DATABASE_INSTANCE),
61 UPPER(DESTINATION_DATABASE_INSTANCE),
62 MIGRATION_TYPE, APPLICATION_ID, MIGRATION_COUNT, BUSINESS_GROUP_ID,
63 STATUS
64 FROM hr_dm_migrations
65 WHERE (migration_id = p_migration_id);
66
67 CURSOR csr_migration_request IS
68 SELECT MAX(creation_date)
69 FROM hr_dm_migration_requests
70 WHERE (migration_id = p_migration_id);
71
72
73 CURSOR csr_database IS
74 SELECT UPPER(name)
75 FROM v$database;
76
77 --
78 BEGIN
79 --
80
81 hr_dm_utility.message('ROUT','entry:hr_dm_master.controller_init', 5);
82 hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
83 ')(r_migration_data - record' ||
84 ')(p_request_data - ' || p_request_data || ')', 10);
85
86
87 -- identify the migration type, current database and application id
88 OPEN csr_migration;
89 LOOP
90 FETCH csr_migration INTO l_migration_id, l_source_database_name,
91 l_destination_database_name,
92 l_migration_type, l_application_id,
93 l_migration_count,
94 l_business_group_id, l_status;
95 EXIT WHEN csr_migration%NOTFOUND;
96 END LOOP;
97 CLOSE csr_migration;
98
99 -- raise error if no matching row in hr_dm_migrations
100 IF (l_migration_id IS NULL) THEN
101 l_fatal_error_message := 'No row identified in HR_DM_MIGRATIONS!';
102 RAISE e_fatal_error;
103 END IF;
104
105
106 -- set all the enabled flags to N for the current migration_request_id
107 -- except for the current run of the migration
108 -- (non-paused)
109 IF (p_request_data IS NULL) THEN
110 OPEN csr_migration_request;
111 FETCH csr_migration_request INTO l_migration_date;
112 CLOSE csr_migration_request;
113
114 UPDATE hr_dm_migration_requests
115 SET enabled_flag = 'N'
116 WHERE ((migration_id = p_migration_id)
117 AND (creation_date <> l_migration_date));
118
119 COMMIT;
120
121 END IF;
122
123
124 OPEN csr_database;
125 LOOP
126 FETCH csr_database INTO l_database_name;
127 EXIT WHEN csr_database%NOTFOUND;
128 END LOOP;
129 CLOSE csr_database;
130
131 IF (l_database_name = l_destination_database_name) THEN
132 l_database_location := 'D';
133 END IF;
134 IF (l_database_name = l_source_database_name) THEN
135 l_database_location := 'S';
136 END IF;
137
138 -- seed data into record
139 r_migration_data.migration_id := p_migration_id;
140 r_migration_data.migration_type := l_migration_type;
141 r_migration_data.database_location := l_database_location;
142 r_migration_data.application_id := l_application_id;
143 r_migration_data.business_group_id := l_business_group_id;
144 r_migration_data.source_database_instance := l_source_database_name;
145 r_migration_data.destination_database_instance :=
146 l_destination_database_name;
147 r_migration_data.last_migration_date :=
148 hr_dm_business.last_migration_date(
149 r_migration_data);
150
151 -- increment migration count in hr_dm_migrations (non-paused)
152 IF (p_request_data IS NULL) THEN
153 UPDATE hr_dm_migrations
154 SET migration_count = l_migration_count+1
155 WHERE migration_id = p_migration_id;
156 COMMIT;
157 END IF;
158
159 -- check if migration is valid / warning
160 -- (first run only)
161 IF (p_request_data IS NULL) THEN
162 l_valid_migration := hr_dm_business.validate_migration(r_migration_data);
163 IF (l_valid_migration = 'E') THEN
164 -- raise error
165 l_fatal_error_message := 'Invalid migration - business rule broken';
166 hr_dm_utility.update_migrations(p_new_status => 'E',
167 p_id => p_migration_id);
168 RAISE e_fatal_error;
169 END IF;
170 END IF;
171
172 -- update status of migration to started (un-paused)
173 IF (NVL(p_request_data, '?') = '?') THEN
174 hr_dm_utility.update_migrations(p_new_status => 'S',
175 p_id => p_migration_id);
176 END IF;
177
178
179 hr_dm_utility.message('INFO','Main controller initialized', 15);
180 hr_dm_utility.message('SUMM','Main controller initialized', 20);
181 hr_dm_utility.message('ROUT','exit:hr_dm_master.controller_init', 25);
182 hr_dm_utility.message('PARA','(none)', 30);
183
184
185
186 -- error handling
187 EXCEPTION
188 WHEN e_fatal_error THEN
189 hr_dm_utility.error(SQLCODE,'hr_dm_range.main_controller',
190 l_fatal_error_message,'R');
191 report_error(l_phase_name, p_migration_id,
192 l_fatal_error_message ||
193 ' in hr_dm_master.main_controller', 'M');
194 RAISE;
195 WHEN OTHERS THEN
196 hr_dm_utility.error(SQLCODE,'hr_dm_master.main_controller',
197 '(none)','R');
198 report_error(l_phase_name, p_migration_id,
199 'Untrapped error in hr_dm_master.main_controller', 'M');
200 RAISE;
201
202
203 --
204 END controller_init;
205 --
206
207 -- ------------------------- insert_request ------------------------
208 -- Description: Inserts the details of a concurrent manager request into
209 -- the table hr_dm_migration_requests.
210 --
211 --
212 -- Input Parameters
213 -- p_phase - phase code
214 --
215 -- p_request_id - concurrent manager request id
216 --
217 -- p_migration_id - migration id
218 --
219 -- p_phase_id - for a slave request
220 --
221 -- p_phase_item_id - for a slave request from a slave
222 --
223 --
224 -- Output Parameters
225 -- <none>
226 --
227 --
228 -- ------------------------------------------------------------------------
229
230
231 --
232 PROCEDURE insert_request(p_phase IN VARCHAR2,
233 p_request_id IN NUMBER,
234 p_master_slave IN VARCHAR2 DEFAULT 'S',
235 p_migration_id IN NUMBER,
236 p_phase_id IN NUMBER DEFAULT NULL,
237 p_phase_item_id IN NUMBER DEFAULT NULL) IS
238 --
239
240 --
241 BEGIN
242 --
243
244 hr_dm_utility.message('ROUT','entry:hr_dm_master.insert_request', 5);
245 hr_dm_utility.message('PARA','(p_phase - ' || p_phase ||
246 ')(p_request_id - ' || p_request_id ||
247 ')(p_master_slave - ' || p_master_slave ||
248 ')(p_migration_id - ' || p_migration_id ||
249 ')(p_phase_id - ' || p_phase_id ||
250 ')(p_phase_item_id - ' || p_phase_item_id || ')', 10);
251
252
253 INSERT INTO hr_dm_migration_requests (migration_request_id,
254 migration_id,
255 phase_id,
256 phase_item_id,
257 request_id,
258 enabled_flag,
259 master_slave,
260 last_updated_by,
261 last_update_date,
262 last_update_login,
263 creation_date,
264 created_by)
265 SELECT hr_dm_migration_requests_s.nextval,
266 p_migration_id,
267 p_phase_id,
268 p_phase_item_id,
269 p_request_id,
270 'Y',
271 p_master_slave,
272 1,
273 sysdate,
274 1,
275 sysdate,
276 1
277 FROM sys.dual
278 WHERE NOT EXISTS
279 (SELECT NULL FROM hr_dm_migration_requests
280 WHERE request_id = p_request_id);
281
282 COMMIT;
283
284 hr_dm_utility.message('INFO','Inserted into hr_dm_migration_requests', 15);
285 hr_dm_utility.message('SUMM','Inserted into hr_dm_migration_requests', 20);
286 hr_dm_utility.message('ROUT','exit:hr_dm_master.insert_request', 25);
287 hr_dm_utility.message('PARA','(none)', 30);
288
289 -- error handling
290 EXCEPTION
291 WHEN OTHERS THEN
292 hr_dm_utility.error(SQLCODE,'hr_dm_master.insert_request','(none)','R');
293 RAISE;
294
295
296 --
297 END insert_request;
298 --
299
300
301 -- ------------------------- spawn_slaves ------------------------
302 -- Description: The appropriate concurrent program for the current phase
303 -- is spawned and details recorded (by calling insert_request).
304 --
305 --
306 -- Input Parameters
307 -- p_current_phase - phase code
308 --
309 -- r_migration_data - record containing migration information
310 --
311 --
312 -- Output Parameters
313 -- <none>
314 --
315 --
316 -- ------------------------------------------------------------------------
317
318 --
319 PROCEDURE spawn_slaves(p_current_phase IN VARCHAR2,
320 r_migration_data IN hr_dm_utility.r_migration_rec) IS
321 --
322
323 l_counter NUMBER;
324 l_request_id NUMBER;
325 e_fatal_error EXCEPTION;
326 l_fatal_error_message VARCHAR2(200);
327 l_slave_program VARCHAR2(30);
328 l_phase_id NUMBER;
329 l_threads NUMBER;
330
331
332 --
333 BEGIN
334 --
335
336 hr_dm_utility.message('ROUT','entry:hr_dm_master.spawn_slaves', 5);
337 hr_dm_utility.message('PARA','(p_current_phase - ' || p_current_phase ||
338 ')(r_migration_data - record)', 10);
339
340
341 -- set up name for appropriate concurrent slave
342 l_slave_program := 'HRDMSLV' || p_current_phase;
343
344 -- get the current phase_id
345 l_phase_id := hr_dm_utility.get_phase_id(p_current_phase,
346 r_migration_data.migration_id);
347
348 -- find the number of threads to use
349 l_threads := hr_dm_utility.number_of_threads(
350 r_migration_data.business_group_id);
351 -- only single thread for UP phase
352 IF (p_current_phase = 'UP') THEN
353 l_threads := 1;
354 END IF;
355
356 -- only single thread for UA phase
357 IF (p_current_phase = 'UA') THEN
358 l_threads := 1;
359 END IF;
360
361 -- only single thread for C phase
362 IF (p_current_phase = 'C') THEN
363 l_threads := 1;
364 END IF;
365
366 -- set current processing
367 hr_dm_utility.set_process(l_threads || ' slaves',
368 p_current_phase,
369 r_migration_data.migration_id);
370
371
372 FOR l_counter IN 1..l_threads LOOP
373 hr_dm_utility.message('INFO','Spawning slave #' || l_counter, 16);
374
375
376 l_request_id := fnd_request.submit_request(
377 application => 'PER',
378 program => l_slave_program,
379 sub_request => TRUE,
380 argument1 => TO_CHAR(r_migration_data.migration_id),
381 argument2 => 'Y',
382 argument3 =>
383 TO_CHAR(r_migration_data.last_migration_date),
384 argument4 => TO_CHAR(l_counter));
385
386
387 -- update table hr_dm_migration_requests
388 insert_request(p_phase => p_current_phase,
389 p_request_id => l_request_id,
390 p_master_slave => 'S',
391 p_migration_id => r_migration_data.migration_id,
392 p_phase_id => l_phase_id);
393
394 COMMIT;
395 hr_dm_utility.message('INFO','Slave request ID#' || l_request_id, 17);
396 IF (l_request_id = 0) THEN
397 l_fatal_error_message := 'Unable to start slave process';
398 report_error(p_current_phase, r_migration_data.migration_id,
399 l_fatal_error_message, 'P');
400 RAISE e_fatal_error;
401 END IF;
402 END LOOP;
403
404 hr_dm_utility.message('INFO','Spawned slaves', 15);
405 hr_dm_utility.message('SUMM','Spawned slaves', 20);
406 hr_dm_utility.message('ROUT','exit:hr_dm_master.spawn_slaves', 25);
407 hr_dm_utility.message('PARA','(none)', 30);
408
409
410
411 -- error handling
412 EXCEPTION
413 WHEN e_fatal_error THEN
414 hr_dm_utility.error(SQLCODE,'hr_dm_master.spawn_slaves',
415 l_fatal_error_message,'R');
416 report_error(p_current_phase, r_migration_data.migration_id,
417 'Error in hr_dm_master.spawn_slaves', 'P');
418 RAISE;
419 WHEN OTHERS THEN
420 hr_dm_utility.error(SQLCODE,'hr_dm_master.spawn_slaves','(none)','R');
421 report_error(p_current_phase, r_migration_data.migration_id,
422 'Untrapped error in hr_dm_master.spawn_slaves', 'P');
423 RAISE;
424
425 --
426 END spawn_slaves;
427 --
428
429 -- ------------------------- slave_status ------------------------
430 -- Description: The status of all slaves submitted for the passed phase
431 -- are checked to ensure that all have completed normally, otherwise
432 -- the return value is set to Y.
433 --
434 --
435 -- Input Parameters
436 -- p_current_phase - phase code
437 --
438 -- r_migration_data - record containing migration information
439 --
440 --
441 -- Return Values
442 -- slave status - Y = one or more slaves errored
443 -- N = no slaves errored
444 --
445 --
446 -- ------------------------------------------------------------------------
447
448 --
449 FUNCTION slave_status(p_current_phase IN VARCHAR2,
450 r_migration_data IN hr_dm_utility.r_migration_rec)
451 RETURN VARCHAR2 IS
452 --
453
454 l_slave_status VARCHAR2(1);
455 l_slave_error VARCHAR2(1) := 'N';
456 l_call_status BOOLEAN;
457 l_phase VARCHAR2(30);
458 l_status VARCHAR2(30);
459 l_dev_phase VARCHAR2(30);
460 l_dev_status VARCHAR2(30);
461 l_message VARCHAR2(240);
462 l_request_id NUMBER;
463 l_phase_id NUMBER;
464
465 CURSOR csr_requests IS
466 SELECT request_id
467 FROM hr_dm_migration_requests
468 WHERE ((phase_id = l_phase_id)
469 AND (master_slave = 'S')
470 AND (enabled_flag = 'Y'));
471
472
473 --
474 BEGIN
475 --
476
477 hr_dm_utility.message('ROUT','entry:hr_dm_master.slave_status', 5);
478 hr_dm_utility.message('PARA','(p_current_phase - ' || p_current_phase ||
479 ')(r_migration_data - record)', 10);
480
481 -- get the current phase_id
482 l_phase_id := hr_dm_utility.get_phase_id(p_current_phase,
483 r_migration_data.migration_id);
484
485 -- check if a slave has errored
486 OPEN csr_requests;
487 LOOP
488 FETCH csr_requests INTO l_request_id;
489 EXIT WHEN csr_requests%NOTFOUND;
490
491 l_call_status := fnd_concurrent.get_request_status(l_request_id, '', '',
492 l_phase, l_status, l_dev_phase,
493 l_dev_status, l_message);
494 -- make sure that each slave is complete and normal, if not then log
495 IF ( NOT( (l_dev_phase = 'COMPLETE') AND (l_dev_status = 'NORMAL') )) THEN
496 l_slave_error := 'Y';
497 END IF;
498
499 END LOOP;
500 CLOSE csr_requests;
501
502
503 hr_dm_utility.message('INFO','Slave status request', 15);
504 hr_dm_utility.message('SUMM','Slave status request', 20);
505 hr_dm_utility.message('ROUT','exit:hr_dm_master.spawn_slaves', 25);
506 hr_dm_utility.message('PARA','(l_slave_error - ' || l_slave_error ||
507 ')', 30);
508
509
510 RETURN(l_slave_error);
511
512 -- error handling
513 EXCEPTION
514 WHEN OTHERS THEN
515 hr_dm_utility.error(SQLCODE,'hr_dm_master.slave_status','(none)','R');
516 report_error(p_current_phase, r_migration_data.migration_id,
517 'Untrapped error in hr_dm_master.slave_status', 'P');
518 RAISE;
519
520 --
521 END slave_status;
522 --
523
524 -- ------------------------- report_error ------------------------
525 -- Description: Reports the fact that an error has occurred and updates
526 -- the status of the phase / migration as appropriate.
527 --
528 --
529 -- Input Parameters
530 -- p_current_phase - phase code
531 --
532 -- p_migration - migration id
533 --
534 -- p_error_message - error text
535 --
536 -- p_stage - P = error occurred in a phase
537 -- M = error occured in migration
538 --
539 --
540 -- Output Parameters
541 -- <none>
542 --
543 --
544 -- ------------------------------------------------------------------------
545
546 --
547 PROCEDURE report_error(p_current_phase IN VARCHAR2,
548 p_migration IN NUMBER,
549 p_error_message IN VARCHAR2,
550 p_stage IN VARCHAR2
551 ) IS
552 --
553
554 --
555 BEGIN
556 --
557
558 hr_dm_utility.message('ROUT','entry:hr_dm_master.report_error', 5);
559 hr_dm_utility.message('PARA','(p_current_phase - ' || p_current_phase ||
560 ')(p_migration - ' || p_migration ||
561 ')(p_error_message - ' || p_error_message ||
562 ')(p_stage - ' || p_stage || ')', 10);
563
564
565 -- update status to show error (E)
566 -- update approriate phase, migration, etc.
567 IF (p_stage = 'P') THEN
568 hr_dm_utility.update_phases(p_new_status => 'E',
569 p_id => hr_dm_utility.get_phase_id(
570 p_current_phase, p_migration));
571 END IF;
572
573 IF (p_stage = 'M') THEN
574 hr_dm_utility.update_migrations(p_new_status => 'E', p_id => p_migration);
575 END IF;
576
577
578 hr_dm_utility.message('INFO','Error reported', 15);
579 hr_dm_utility.message('SUMM','Error reported', 20);
580 hr_dm_utility.message('ROUT','exit:hr_dm_master.report_error', 25);
581 hr_dm_utility.message('PARA','(none)', 30);
582
583
584 --
585 END report_error;
586 --
587
588 -- ------------------------- work_required ------------------------
589 -- Description: A check is made to see if for the phase passed if there
590 -- are any phase items which do not have a status of C. If all have the
591 -- status C then the phase is marked as completed. Where a phase links
592 -- to migration ranges then these are also checked to see if for a
593 -- phase item that there are ranges to process.
594 --
595 --
596 -- Input Parameters
597 -- p_current_phase - phase code
598 --
599 -- r_migration_data - record containing migration information
600 --
601 --
602 -- Return Values
603 -- work required - Y = phase items to process
604 -- N = phase items to process
605 --
606 --
607 -- ------------------------------------------------------------------------
608
609 --
610 FUNCTION work_required(p_current_phase IN VARCHAR2,
611 r_migration_data IN hr_dm_utility.r_migration_rec)
612 RETURN VARCHAR2 IS
613 --
614
615 l_work VARCHAR2(1);
616 l_phase_id NUMBER;
617 l_range_phase_id NUMBER;
618 l_required NUMBER;
619 l_phase_item_id NUMBER;
620 l_group_id NUMBER;
621
622 CURSOR csr_phase_items IS
623 SELECT NULL
624 FROM hr_dm_phase_items
625 WHERE phase_id = l_phase_id
626 AND status <> 'C';
627
628 CURSOR csr_ddp_pi IS
629 SELECT phase_item_id, group_id
630 FROM hr_dm_phase_items
631 WHERE phase_id = l_phase_id;
632
633 CURSOR csr_mig_ranges IS
634 SELECT rpi.phase_item_id
635 FROM hr_dm_phase_items rpi,
636 hr_dm_migration_ranges mr
637 WHERE rpi.group_id = l_group_id
638 AND rpi.phase_id = l_range_phase_id
639 AND rpi.phase_item_id = mr.phase_item_id
640 AND mr.status <> 'C';
641
642
643 --
644 BEGIN
645 --
646
647 hr_dm_utility.message('ROUT','entry:hr_dm_master.work_required', 5);
648 hr_dm_utility.message('PARA','(p_current_phase - ' || p_current_phase ||
649 ')(r_migration_data - record)', 10);
650
651 -- get the current phase_id
652 l_phase_id := hr_dm_utility.get_phase_id(p_current_phase,
653 r_migration_data.migration_id);
654
655 l_work := 'Y';
656
657 -- check if any phase items to process (not DP or D phases)
658 IF (p_current_phase IN ('G', 'R', 'DA', 'UA', 'UP', 'C')) THEN
659 OPEN csr_phase_items;
660 FETCH csr_phase_items INTO l_required;
661 IF (csr_phase_items%NOTFOUND) THEN
662 hr_dm_utility.message('INFO','No work required for phase ' ||
663 p_current_phase, 11);
664 l_work := 'N';
665 hr_dm_utility.update_phases(p_new_status => 'C',
666 p_id => l_phase_id);
667 END IF;
668 CLOSE csr_phase_items;
669 END IF;
670
671
672 -- check phase items for DP or D phases to see if any migration ranges
673 -- to process
674 IF (p_current_phase IN ('DP', 'D')) THEN
675 hr_dm_utility.message('INFO','checking DP/D - ' || p_current_phase, 5);
676 -- get the range phase_id
677 l_range_phase_id := hr_dm_utility.get_phase_id('R',
678 r_migration_data.migration_id);
679 hr_dm_utility.message('INFO','l_range_phase_id - ' || l_range_phase_id, 5);
680 -- find phase_item_id for D/DP phase
681 OPEN csr_ddp_pi;
682 LOOP
683 FETCH csr_ddp_pi INTO l_phase_item_id, l_group_id;
684 hr_dm_utility.message('INFO','l_phase_item_id - ' || l_phase_item_id, 5);
685 hr_dm_utility.message('INFO','l_group_id - ' || l_group_id, 5);
686 EXIT WHEN csr_ddp_pi%NOTFOUND;
687
688 -- see if any migration ranges to process for this phase_item_id
689 OPEN csr_mig_ranges;
690 FETCH csr_mig_ranges INTO l_required;
691 hr_dm_utility.message('INFO','l_required - ' || l_required, 5);
692 IF (csr_mig_ranges%NOTFOUND) THEN
693 -- mark phase item as started and then complete to ensure that
694 -- the start and end times are set
695 hr_dm_utility.message('INFO','No work required for group ' ||
696 l_group_id, 12);
697
698 hr_dm_utility.update_phase_items(p_new_status => 'S',
699 p_id => l_phase_item_id);
700 hr_dm_utility.update_phase_items(p_new_status => 'C',
701 p_id => l_phase_item_id);
702 END IF;
703 CLOSE csr_mig_ranges;
704
705 END LOOP;
706 CLOSE csr_ddp_pi;
707
708 -- see if any phase items to process
709 -- ie if csr_ddp_pi does not find any phase items
710 IF (l_phase_item_id IS NULL) THEN
711 hr_dm_utility.message('INFO','No work required for phase ' ||
712 p_current_phase, 13);
713 l_work := 'N';
714 hr_dm_utility.update_phases(p_new_status => 'C',
715 p_id => l_phase_id);
716 END IF;
717
718 -- see if phase is complete
719 IF (hr_dm_utility.get_phase_status(p_current_phase,
720 r_migration_data.migration_id) = 'C') THEN
721 l_work := 'N';
722 END IF;
723
724 END IF;
725
726
727 hr_dm_utility.message('INFO','Check work required for phase', 15);
728 hr_dm_utility.message('SUMM','Check work required for phase', 20);
729 hr_dm_utility.message('ROUT','exit:hr_dm_master.work_required', 25);
730 hr_dm_utility.message('PARA','(l_work - ' || l_work || ')', 30);
731
732 RETURN(l_work);
733
734 -- error handling
735 EXCEPTION
736 WHEN OTHERS THEN
737 hr_dm_utility.error(SQLCODE,'hr_dm_master.work_required','(none)','R');
738 report_error(p_current_phase, r_migration_data.migration_id,
739 'Untrapped error in hr_dm_master.work_required', 'P');
740 RAISE;
741
742 --
743 END work_required;
744 --
745
746
747
748 -- ------------------------------- master ------------------------------
749 -- Description: This procedure calls the code appropriate for a phase
750 -- which is processed by multiple threads. There are two paths through
751 -- the code, dependent upon if the procedure is called at the start of
752 -- a phase or after the main controller has been awoken after the slaves
753 -- have finished processing. This is determined by examining the
754 -- concurrent request data which will contain the phase code for a
755 -- re-awoken phase and null for a first run.
756 --
757 -- On a first run (l_request_data = null) the status of the previous phase
758 -- is checked to ensure that it has completed. If not then the migration
759 -- status is set to error and the procedure exits.
760 --
761 -- A check is made to see if the current phase has either started or errored
762 -- in which case a rollback of the phase (if applicable) is performed.
763 --
764 -- The status of the phase is set to started and any phase specific
765 -- pre-processing code is called.
766 --
767 -- A check is made to see if there is any work to be performed by the phase
768 -- if so, slaves are spawned to perform the work and the main controller
769 -- is paused.
770 --
771 --
772 --
773 -- On a non-first run (l_request_data <> null), the request data is first
774 -- set to ? (equivelent to null) the status of the current phase
775 -- is checked to ensure that it has completed. If not then the migration
776 -- status is set to error and the procedure exits.
777 --
778 -- A check is made of the status of each slave and if any have not completed
779 -- normally then a warning message is logged.
780 --
781 -- Any phase specific post-processing code is called.
782 --
783 --
784 --
785 -- Input Parameters
786 -- p_current_phase - phase code
787 --
788 -- p_previous_phase - phase code
789 --
790 -- r_migration_data - migration record
791 --
792 --
793 -- Output Parameters
794 -- <none>
795 --
796 --
797 -- ------------------------------------------------------------------------
798
799 --
800 PROCEDURE master(p_current_phase IN VARCHAR2,
801 p_previous_phase IN VARCHAR2,
802 r_migration_data IN hr_dm_utility.r_migration_rec) IS
803 --
804
805 l_current_phase_status VARCHAR2(30);
806 e_fatal_error EXCEPTION;
807 l_fatal_error_message VARCHAR2(200);
808 l_request_data VARCHAR2(30);
809 l_dummy VARCHAR2(1);
810
811
812 --
813 BEGIN
814 --
815
816 hr_dm_utility.message('ROUT','entry:hr_dm_master.master', 5);
817 hr_dm_utility.message('PARA','(p_current_phase - ' || p_current_phase ||
818 ')(p_previous_phase - ' || p_previous_phase ||
819 ')(r_migration_data - record)', 10);
820
821 -- see if this is the first run? (l_request_data = NULL or '?')
822 -- or
823 -- is it a restart after slaves have finished? (l_request_data =
824 -- paused phase code)
825 l_request_data := fnd_conc_global.request_data;
826
827 hr_dm_utility.message('INFO','l_request_data ' || l_request_data, 11);
828 IF (NVL(l_request_data, '?') = '?') THEN
829 -- first run processing...
830
831 hr_dm_utility.message('INFO','First run processing (pre-pause)', 12);
832
833 -- get status of previous phase, is previous phase completed?
834 -- for the first phase there is no previous phase, so check for
835 -- a NULL previous to bypass this check
836 IF ((hr_dm_utility.get_phase_status(p_previous_phase,
837 r_migration_data.migration_id) <> 'C')
838 AND (p_previous_phase IS NOT NULL) ) THEN
839 l_fatal_error_message := 'Previous phase has not completed';
840 report_error(p_current_phase, r_migration_data.migration_id,
841 l_fatal_error_message, 'P');
842 RAISE e_fatal_error;
843 END IF;
844
845 -- get status of current phase
846 l_current_phase_status := hr_dm_utility.get_phase_status(p_current_phase,
847 r_migration_data.migration_id);
848
849 -- is phase complete?
850 IF (l_current_phase_status <> 'C') THEN
851 -- do we need to explicitly rollback using rollback utility?
852 IF ( (l_current_phase_status IN('S', 'E')) AND
853 (p_current_phase IN('I', 'G', 'R', 'DP', 'DA', 'UA',
854 'UP', 'C', 'D')) ) THEN
855 hr_dm_utility.rollback(p_phase => p_current_phase,
856 p_masterslave => 'MASTER',
857 p_migration_id =>
858 r_migration_data.migration_id);
859 END IF;
860
861 -- update status to started
862 hr_dm_utility.update_phases(p_new_status => 'S',
863 p_id => hr_dm_utility.get_phase_id(
864 p_current_phase,
865 r_migration_data.migration_id));
866 COMMIT;
867
868
869 -- call phase specific pre-processing code
870
871 -- generate phase
872 -- create dummy hr_dmv views to avoid compilation errors
873 IF (p_current_phase = 'G') THEN
874 hr_dm_utility.set_process('Pre-processing',
875 p_current_phase,
876 r_migration_data.migration_id);
877 hr_dm_library.create_stub_views(r_migration_data.migration_id);
878 END IF;
879
880
881
882 -- delete phase
883 -- set current processing
884 IF (p_current_phase = 'D') THEN
885 hr_dm_utility.set_process('Pre-processing',
886 p_current_phase,
887 r_migration_data.migration_id);
888 hr_dm_delete.set_active(r_migration_data.migration_id);
889 hr_dm_delete.pre_delete_process(r_migration_data);
890 END IF;
891
892
893 -- spawn off slaves if work to be done
894 IF (work_required(p_current_phase, r_migration_data) = 'Y') THEN
895 spawn_slaves(p_current_phase, r_migration_data);
896
897 -- pause master whilst slaves process data...
898 -- set request data to indicate paused phase
899 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
900 request_data => p_current_phase);
901
902 END IF;
903
904
905
906
907 END IF;
908
909 ELSE
910 -- processing after being woken up
911 hr_dm_utility.message('INFO','Unpaused processing', 13);
912 -- set request data to indicate un-paused phase
913 fnd_conc_global.set_req_globals(request_data => '?');
914
915
916 -- first force check for all required work
917 l_dummy := work_required(p_current_phase, r_migration_data);
918
919 -- get status of current phase
920 l_current_phase_status := hr_dm_utility.get_phase_status(p_current_phase,
921 r_migration_data.migration_id);
922
923 -- is it completed?
924 IF (l_current_phase_status <> 'C') THEN
925 l_fatal_error_message := 'Current phase has not completed';
926 report_error(p_current_phase, r_migration_data.migration_id,
927 l_fatal_error_message, 'P');
928 RAISE e_fatal_error;
929 END IF;
930
931 -- has any slave errored?
932 -- if so, add warning message to log
933 IF (slave_status(p_current_phase, r_migration_data) = 'Y') THEN
934 hr_dm_utility.message('INFO', 'Warning - ' || p_current_phase ||
935 ' phase slave process errored', 13);
936 END IF;
937
938 -- call phase specific post processing code
939 IF (p_current_phase = 'G') THEN
940 -- set current processing
941 hr_dm_utility.set_process('Post-processing',
942 p_current_phase,
943 r_migration_data.migration_id);
944 hr_dm_gen_main.post_generate_validate(r_migration_data.migration_id);
945 END IF;
946
947 IF (p_current_phase = 'C') THEN
948 -- set current processing
949 hr_dm_utility.set_process('Post-processing',
950 p_current_phase,
951 r_migration_data.migration_id);
952 hr_dm_cleanup.post_cleanup_process(r_migration_data);
953 END IF;
954
955
956
957 IF (p_current_phase = 'UP') THEN
958 -- update flexfield information
959 -- for FW and A migrations only
960 IF (r_migration_data.migration_type IN ('FW','A')) THEN
961 -- Set the variable so as to disable the trigger on the table.
962 hr_general.g_data_migrator_mode := 'Y';
963 hr_dm_aol_up.post_aol_process(r_migration_data.migration_id);
964 END IF;
965 -- flush BEN_COMP_OBJ_CACHE
966 -- only for FW and A migrations for OAB product (id=805)
967 -- commented out - manual step
968 /*
969 IF (r_migration_data.migration_type IN ('FW','A'))
970 AND (r_migration_data.application_id = 805) THEN
971 -- Set the variable so as to disable the trigger on the table.
972 ben_comp_object_list.flush_multi_session_cache
973 (p_business_group_id => r_migration_data.business_group_id);
974 END IF;
975 */
976 END IF;
977
978
979 END IF;
980
981 hr_dm_utility.message('INFO','Master concurrent program', 15);
982 hr_dm_utility.message('SUMM','Master concurrent program', 20);
983 hr_dm_utility.message('ROUT','exit:hr_dm_master.master', 25);
984 hr_dm_utility.message('PARA','(none)', 30);
985
986
987
988 -- error handling
989 EXCEPTION
990 WHEN e_fatal_error THEN
991 hr_dm_utility.error(SQLCODE,'hr_dm_range.master',
992 l_fatal_error_message,'R');
993 report_error(p_current_phase, r_migration_data.migration_id,
994 'Untrapped error in hr_dm_master.master', 'P');
995 RAISE;
996 WHEN OTHERS THEN
997 hr_dm_utility.error(SQLCODE,'hr_dm_master.master','(none)','R');
998 report_error(p_current_phase, r_migration_data.migration_id,
999 'Untrapped error in hr_dm_master.master', 'P');
1000 RAISE;
1001 --
1002 END master;
1003 --
1004
1005
1006
1007
1008
1009
1010 /*---------------------------- PUBLIC ROUTINES --------------------------*/
1011
1012 -- ------------------------- main_controller ------------------------
1013 -- Description: This is the main controller for the DM which is called
1014 -- from the DM UI. It calls the required phases for the migration.
1015 --
1016 -- A check is made to see if the code is running for the first time or has
1017 -- been re-awoken.
1018 --
1019 -- The initialization code is called to set up various details (see the
1020 -- controller_init procedure).
1021 --
1022 -- The next phase to be process is found using the hr_dm_phase_rules table
1023 -- and the appropriate single or multi-threaded code is called.
1024 --
1025 -- If a multi-threaded process has spawned slaves then the main controller
1026 -- exits (to enable it to be awoken later). Otherwise it runs the next
1027 -- phase until all phases have been completed.
1028 --
1029 --
1030 -- Input Parameters
1031 -- p_migration_id - of current migration
1032 --
1033 --
1034 -- Output Parameters
1035 -- errbuf - buffer for output message (for CM manager)
1036 --
1037 -- retcode - program return code (for CM manager)
1038 --
1039 --
1040 -- ------------------------------------------------------------------------
1041
1042 --
1043 PROCEDURE main_controller(errbuf OUT NOCOPY VARCHAR2,
1044 retcode OUT NOCOPY NUMBER,
1045 p_migration_id IN BINARY_INTEGER) IS
1046 --
1047
1048 l_search_phase VARCHAR2(30);
1049 l_next_phase VARCHAR2(30);
1050 l_phase_name VARCHAR2(30);
1051 l_previous_phase VARCHAR2(30);
1052 l_previous VARCHAR2(30);
1053 e_fatal_error EXCEPTION;
1054 l_fatal_error_message VARCHAR2(200);
1055 r_migration_data hr_dm_utility.r_migration_rec;
1056 l_database_location VARCHAR2(30);
1057 l_request_data VARCHAR2(30);
1058 l_mig_status VARCHAR2(30);
1059
1060
1061 CURSOR csr_phase_rule IS
1062 SELECT phase_name, next_phase, database_location
1063 FROM hr_dm_phase_rules
1064 WHERE ((migration_type = r_migration_data.migration_type)
1065 AND (previous_phase = l_search_phase));
1066
1067 CURSOR csr_paused_phase_rule IS
1068 SELECT previous_phase, next_phase
1069 FROM hr_dm_phase_rules
1070 WHERE ((migration_type = r_migration_data.migration_type)
1071 AND (phase_name = l_request_data));
1072
1073 CURSOR csr_mig_status IS
1074 SELECT status
1075 FROM hr_dm_migrations
1076 WHERE migration_id = r_migration_data.migration_id;
1077
1078
1079 --
1080 BEGIN
1081 --
1082
1083 -- initialize messaging
1084 hr_dm_utility.message_init;
1085
1086 hr_dm_utility.message('ROUT','entry:hr_dm_master.main_controller', 5);
1087 hr_dm_utility.message('PARA','(p_migration_id - ' ||
1088 p_migration_id || ')', 10);
1089
1090
1091 -- see if this is a migration run (l_request_data = NULL)
1092 -- OR
1093 -- is it a restart after slaves have finished? (l_request_data =
1094 -- paused phase code)
1095 l_request_data := fnd_conc_global.request_data;
1096 hr_dm_utility.message('INFO','Request data - ' || l_request_data, 12);
1097
1098 -- initialize main controller data
1099 controller_init(p_migration_id,
1100 r_migration_data,
1101 l_request_data);
1102
1103 -- work out phases applicable to this migration
1104 --
1105 IF (l_request_data IS NULL) THEN
1106 -- seed first phase
1107 l_search_phase := 'START';
1108 l_next_phase := '?';
1109 ELSE
1110 -- seed with data for paused phase
1111 OPEN csr_paused_phase_rule;
1112 LOOP
1113 FETCH csr_paused_phase_rule INTO l_search_phase, l_next_phase;
1114 EXIT WHEN csr_paused_phase_rule%NOTFOUND;
1115 END LOOP;
1116 CLOSE csr_paused_phase_rule;
1117 END IF;
1118
1119 -- seed previous phase name
1120 l_previous := 'START';
1121
1122 WHILE (l_next_phase <> 'END') LOOP
1123 OPEN csr_phase_rule;
1124 FETCH csr_phase_rule INTO l_phase_name, l_next_phase,
1125 l_database_location;
1126 EXIT WHEN csr_phase_rule%NOTFOUND;
1127
1128 -- does it apply?
1129 IF (INSTR(l_database_location, r_migration_data.database_location) >0) THEN
1130
1131 hr_dm_utility.message('INFO','Current phase is ' || l_phase_name, 11);
1132 hr_dm_utility.message('INFO','Request data - ' ||
1133 NVL(l_request_data, '?'), 12);
1134
1135 -- store the previous phase that applies
1136 l_previous_phase := l_previous;
1137 hr_dm_utility.message('INFO','Previous phase is ' || l_previous_phase, 11);
1138
1139
1140
1141 -- is it completed or not awoken?
1142 -- if not, then run appropriate phase code
1143 IF ((hr_dm_utility.get_phase_status(l_phase_name, p_migration_id) <> 'C')
1144 OR (NVL(l_request_data, '?') <> '?')) THEN
1145
1146 -- call generic master code
1147 IF (l_phase_name IN ('G', 'R', 'DA', 'DP', 'UA', 'UP', 'D', 'C')) THEN
1148 master(l_phase_name, l_previous_phase, r_migration_data);
1149 ELSE
1150 -- call non-generic master code
1151 IF (l_phase_name = 'I') THEN
1152 -- set current processing
1153 hr_dm_utility.set_process('Single threaded',
1154 l_phase_name,
1155 r_migration_data.migration_id);
1156 hr_dm_init.main(r_migration_data);
1157 ELSIF (l_phase_name = 'CP') THEN
1158 -- mark copy as started
1159 -- set current processing
1160 hr_dm_utility.set_process('Single threaded',
1161 l_phase_name,
1162 r_migration_data.migration_id);
1163 hr_dm_utility.update_phases(p_new_status => 'S',
1164 p_id => hr_dm_utility.get_phase_id('CP'
1165 , r_migration_data.migration_id));
1166 -- perform source copy on source database only
1167 IF (r_migration_data.database_location = 'S') THEN
1168 hr_dm_copy.source_copy(r_migration_data.migration_id,
1169 r_migration_data.last_migration_date);
1170 END IF;
1171 -- now let the user proceed with the manual copy,
1172 -- so mark the current migration as complete (on source database)
1173 hr_dm_utility.update_phases(p_new_status => 'C',
1174 p_id => hr_dm_utility.get_phase_id('CP'
1175 , r_migration_data.migration_id));
1176 -- and then the user must update the status to complete when manual copy
1177 -- process has been completed
1178 END IF;
1179
1180 END IF;
1181
1182 -- have we paused the main controller?
1183 -- if so, exit loop
1184 l_request_data := fnd_conc_global.request_data;
1185 EXIT WHEN NVL(l_request_data, '?') <> '?';
1186
1187 -- did it complete? if not then update status and raise an error
1188 IF (hr_dm_utility.get_phase_status(l_phase_name, p_migration_id)
1189 <> 'C') THEN
1190 -- update status of migration to error
1191 hr_dm_utility.update_migrations(p_new_status => 'E',
1192 p_id => p_migration_id);
1193 COMMIT;
1194 -- raise error
1195 l_fatal_error_message := 'Error in ' || l_phase_name || ' phase';
1196 report_error(l_phase_name, p_migration_id, l_fatal_error_message,
1197 'M');
1198 RAISE e_fatal_error;
1199 END IF;
1200
1201 END IF;
1202
1203 -- store current applicable phase name for next iteration
1204 l_previous := l_phase_name;
1205 END IF;
1206 l_search_phase := l_phase_name;
1207 CLOSE csr_phase_rule;
1208 END LOOP;
1209
1210
1211 -- set up return values to concurrent manager
1212 retcode := 0;
1213 IF (NVL(l_request_data, '?') <> '?') THEN
1214 errbuf := 'No errors - examine logfiles for detailed reports.';
1215 ELSE
1216 errbuf := 'Master Controller is paused.';
1217 END IF;
1218
1219 -- see the migration has errored
1220 -- if so, error this conc. program
1221 OPEN csr_mig_status;
1222 FETCH csr_mig_status INTO l_mig_status;
1223 CLOSE csr_mig_status;
1224
1225 IF l_mig_status = 'E' THEN
1226 l_fatal_error_message := 'The migration is in error.';
1227 RAISE e_fatal_error;
1228 END IF;
1229
1230 -- if we are doing a delete migration
1231 -- and the status is Complete
1232 -- then mark the migration as Finished
1233
1234 IF r_migration_data.migration_type = 'D'
1235 AND l_mig_status = 'C' THEN
1236 UPDATE hr_dm_migrations
1237 SET status = 'F'
1238 WHERE migration_id = r_migration_data.migration_id;
1239 COMMIT;
1240 END IF;
1241
1242
1243 hr_dm_utility.message('INFO','Main controller', 15);
1244 hr_dm_utility.message('SUMM','Main controller', 20);
1245 hr_dm_utility.message('ROUT','exit:hr_dm_master.main_controller', 25);
1246 hr_dm_utility.message('PARA','(retcode - ' || retcode ||
1247 ')(errbuf - ' || errbuf || ')', 30);
1248
1249
1250 -- error handling
1251 EXCEPTION
1252 WHEN e_fatal_error THEN
1253 retcode := 2;
1254 errbuf := 'An error occurred during the migration - examine logfiles' ||
1255 ' for detailed reports.';
1256 hr_dm_utility.error(SQLCODE,'hr_dm_range.main_controller',
1257 l_fatal_error_message,'R');
1258 report_error(l_phase_name, p_migration_id,
1259 l_fatal_error_message || ' in hr_dm_master.main_controller',
1260 'M');
1261 WHEN OTHERS THEN
1262 retcode := 2;
1263 errbuf := 'An error occurred during the migration - examine logfiles' ||
1264 ' for detailed reports.';
1265 hr_dm_utility.error(SQLCODE,'hr_dm_master.main_controller','(none)','R');
1266 report_error(l_phase_name, p_migration_id,
1267 'Untrapped error in hr_dm_master.main_controller', 'M');
1268
1269 --
1270 END main_controller;
1271 --
1272
1273
1274
1275 end hr_dm_master;
[Home] [Help]