[Home] [Help]
PACKAGE BODY: APPS.BEN_DM_MASTER
Source
1 PACKAGE BODY ben_dm_master AS
2 /* $Header: benfdmdmas.pkb 120.0 2006/05/04 04:47:39 nkkrishn noship $ */
3
4
5 g_delimiter varchar2(1) := fnd_global.local_chr(01);
6 g_data_file_name varchar2(30) := 'out_file.dat';
7 g_transfer_file_name varchar2(30) := 'transfer_file.dat';
8 /*-------------------------- PRIVATE ROUTINES ----------------------------*/
9
10 -- ------------------------- controller_init ------------------------
11 -- Description: Various initialization processes are undertaken:
12 -- a) ensuring that data for the migration exists in ben_dm_migrations
13 -- b) entries in ben_dm_migration_requests are marked as inactive
14 -- c) r_migration_data is seeded with information about the migration
15 -- d) the migration count is incremented
16 -- e) the validity of the migration is checked
17 -- f) the migration status is set to started
18 --
19 --
20 -- Input Parameters
21 -- p_migration_id - migration id
22 --
23 -- r_migration_data - record containing migration information
24 --
25 -- p_request_data - concurrent request data (contains previous
26 -- phase code - if it was a concurrent phase)
27 --
28 --
29 -- Output Parameters
30 -- r_migration_data - record containing migration information
31 --
32 --
33 -- ------------------------------------------------------------------------
34
35 --
36 PROCEDURE controller_init(p_migration_id IN NUMBER,
37 r_migration_data IN OUT nocopy
38 ben_dm_utility.r_migration_rec,
39 p_request_data IN VARCHAR2) IS
40 --
41
42 l_current_phase_status VARCHAR2(30);
43 l_migration_type VARCHAR2(30);
44 l_application_id NUMBER;
45 l_migration_id NUMBER;
46 l_migration_name VARCHAR2(80);
47 l_input_parameter_file_name VARCHAR2(30);
48 l_input_parameter_file_path VARCHAR2(60);
49 l_data_file_name VARCHAR2(30);
50 l_data_file_path VARCHAR2(60);
51 l_last_migrated_date DATE;
52 l_phase_flag VARCHAR2(1);
53 l_migration_count NUMBER;
54 l_business_group_id NUMBER;
55 l_valid_migration VARCHAR2(1);
56 e_fatal_error EXCEPTION;
57 l_fatal_error_message VARCHAR2(200);
58 l_database_location VARCHAR2(30);
59 l_phase_name VARCHAR2(30);
60 l_status VARCHAR2(30);
61 l_migration_date DATE;
62
63
64 CURSOR csr_migration IS
65 SELECT MIGRATION_ID, MIGRATION_NAME,
66 INPUT_PARAMATER_FILE_NAME,
67 INPUT_PARAMETER_FILE_PATH,
68 DATA_FILE_NAME,
69 DATA_FILE_PATH,
70 MIGRATION_COUNT,
71 DATABASE_LOCATION,
72 STATUS
73 FROM ben_dm_migrations
74 WHERE (migration_id = p_migration_id);
75
76 CURSOR csr_migration_request IS
77 SELECT MAX(creation_date)
78 FROM ben_dm_migration_requests
79 WHERE (migration_id = p_migration_id);
80
81
82 --
83 BEGIN
84 --
85
86 ben_dm_utility.message('ROUT','entry:ben_dm_master.controller_init', 5);
87 ben_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
88 ')(r_migration_data - record' ||
89 ')(p_request_data - ' || p_request_data || ')', 10);
90
91
92 -- identify the current migrations.
93 OPEN csr_migration;
94 LOOP
95 FETCH csr_migration INTO l_migration_id, l_migration_name,
96 l_input_parameter_file_name,
97 l_input_parameter_file_path,
98 l_data_file_name,
99 l_data_file_path,
100 l_migration_count,
101 l_database_location,
102 l_status;
103 EXIT WHEN csr_migration%NOTFOUND;
104 END LOOP;
105 CLOSE csr_migration;
106
107 -- raise error if no matching row in ben_dm_migrations
108 IF (l_migration_id IS NULL) THEN
109 l_fatal_error_message := 'No row identified in ben_dm_migrations!';
110 RAISE e_fatal_error;
111 END IF;
112
113
114 -- set all the enabled flags to N for the current migration_request_id
115 -- except for the current run of the migration
116 -- (non-paused)
117 IF (p_request_data IS NULL) THEN
118 OPEN csr_migration_request;
119 FETCH csr_migration_request INTO l_migration_date;
120 CLOSE csr_migration_request;
121
122 UPDATE ben_dm_migration_requests
123 SET enabled_flag = 'N'
124 WHERE ((migration_id = p_migration_id)
125 AND (creation_date <> l_migration_date));
126
127 COMMIT;
128
129 END IF;
130
131 -- seed data into record
132 r_migration_data.migration_id := p_migration_id;
133 r_migration_data.migration_name := l_migration_name;
134 r_migration_data.input_parameter_file_name := l_input_parameter_file_name;
135 r_migration_data.input_parameter_file_path := l_input_parameter_file_path;
136 r_migration_data.data_file_name := l_data_file_name;
137 r_migration_data.data_file_path := l_data_file_path;
138 r_migration_data.database_location := l_database_location;
139 r_migration_data.last_migration_date :=
140 ben_dm_business.last_migration_date(
141 r_migration_data);
142
143 -- increment migration count in ben_dm_migrations (non-paused)
144 IF (p_request_data IS NULL) THEN
145 UPDATE ben_dm_migrations
146 SET migration_count = l_migration_count+1
147 WHERE migration_id = p_migration_id;
148 COMMIT;
149 END IF;
150
151 -- check if migration is valid / warning
152 -- (first run only)
153 IF (p_request_data IS NULL) THEN
154 l_valid_migration := ben_dm_business.validate_migration(r_migration_data);
155 IF (l_valid_migration = 'E') THEN
156 -- raise error
157 l_fatal_error_message := 'Invalid migration - business rule broken';
158 ben_dm_utility.update_migrations(p_new_status => 'E',
159 p_id => p_migration_id);
160 RAISE e_fatal_error;
161 END IF;
162 END IF;
163
164 -- update status of migration to started (un-paused)
165 IF (NVL(p_request_data, '?') = '?') THEN
166 ben_dm_utility.update_migrations(p_new_status => 'S',
167 p_id => p_migration_id);
168 END IF;
169
170
171 ben_dm_utility.message('INFO','Main controller initialized', 15);
172 ben_dm_utility.message('SUMM','Main controller initialized', 20);
173 ben_dm_utility.message('ROUT','exit:ben_dm_master.controller_init', 25);
174 ben_dm_utility.message('PARA','(none)', 30);
175
176
177
178 -- error handling
179 EXCEPTION
180 WHEN e_fatal_error THEN
181 ben_dm_utility.error(SQLCODE,'hr_dm_range.main_controller',
182 l_fatal_error_message,'R');
183 report_error(l_phase_name, p_migration_id,
184 l_fatal_error_message ||
185 ' in ben_dm_master.main_controller', 'M');
186 RAISE;
187 WHEN OTHERS THEN
188 ben_dm_utility.error(SQLCODE,'ben_dm_master.main_controller',
189 '(none)','R');
190 report_error(l_phase_name, p_migration_id,
191 'Untrapped error in ben_dm_master.main_controller', 'M');
192 RAISE;
193
194
195 --
196 END controller_init;
197 --
198
199 -- ------------------------- getTableSchema ------------------------
200 -- Description: Gets the BEN schema name used. This info
201 -- will be used when we truncate the tables for migrations.
202 --
203 --
204 -- Input Parameters
205 -- <none>
206 --
207 -- Output Parameters
208 -- <none>
209 --
210 --
211 -- ------------------------------------------------------------------------
212
213 FUNCTION getTableSchema RETURN VARCHAR2 IS
214 l_status VARCHAR2(100) := '';
215 l_industry VARCHAR2(100) := '';
216 l_result BOOLEAN;
217 l_schema_owner VARCHAR2(10) := '';
218 BEGIN
219 l_result := FND_INSTALLATION.GET_APP_INFO(
220 'BEN',
221 l_status,
222 l_industry,
223 l_schema_owner);
224
225 IF l_result THEN
226 RETURN l_schema_owner;
227 ELSE
228 RETURN 'BEN';
229 END IF;
230 END getTableSchema;
231
232 -- ------------------------- insert_request ------------------------
233 -- Description: Inserts the details of a concurrent manager request into
234 -- the table ben_dm_migration_requests.
235 --
236 --
237 -- Input Parameters
238 -- p_phase - phase code
239 --
240 -- p_request_id - concurrent manager request id
241 --
242 -- p_migration_id - migration id
243 --
244 -- p_phase_id - for a slave request
245 --
246 -- p_phase_item_id - for a slave request from a slave
247 --
248 --
249 -- Output Parameters
250 -- <none>
251 --
252 --
253 -- ------------------------------------------------------------------------
254
255
256 --
257 PROCEDURE insert_request(p_phase IN VARCHAR2,
258 p_request_id IN NUMBER,
259 p_master_slave IN VARCHAR2 DEFAULT 'S',
260 p_migration_id IN NUMBER,
261 p_phase_id IN NUMBER DEFAULT NULL,
262 p_phase_item_id IN NUMBER DEFAULT NULL) IS
263 --
264
265 --
266 BEGIN
267 --
268
269 ben_dm_utility.message('ROUT','entry:ben_dm_master.insert_request', 5);
270 ben_dm_utility.message('PARA','(p_phase - ' || p_phase ||
271 ')(p_request_id - ' || p_request_id ||
272 ')(p_master_slave - ' || p_master_slave ||
273 ')(p_migration_id - ' || p_migration_id ||
274 ')(p_phase_id - ' || p_phase_id ||
275 ')(p_phase_item_id - ' || p_phase_item_id || ')', 10);
276
277
278 INSERT INTO ben_dm_migration_requests (migration_request_id,
279 migration_id,
280 phase_id,
281 phase_item_id,
282 request_id,
283 enabled_flag,
284 master_slave,
285 last_updated_by,
286 last_update_date,
287 last_update_login,
288 creation_date,
289 created_by)
290 SELECT ben_dm_migration_requests_s.nextval,
291 p_migration_id,
292 nvl(p_phase_id,-1),
293 nvl(p_phase_item_id,-1),
294 p_request_id,
295 'Y',
296 p_master_slave,
297 1,
298 sysdate,
299 1,
300 sysdate,
301 1
302 FROM sys.dual
303 WHERE NOT EXISTS
304 (SELECT NULL FROM ben_dm_migration_requests
305 WHERE request_id = p_request_id);
306
307 COMMIT;
308
309 ben_dm_utility.message('INFO','Inserted into ben_dm_migration_requests', 15);
310 ben_dm_utility.message('SUMM','Inserted into ben_dm_migration_requests', 20);
311 ben_dm_utility.message('ROUT','exit:ben_dm_master.insert_request', 25);
312 ben_dm_utility.message('PARA','(none)', 30);
313
314 -- error handling
315 EXCEPTION
316 WHEN OTHERS THEN
317 ben_dm_utility.error(SQLCODE,'ben_dm_master.insert_request','(none)','R');
318 RAISE;
319
320
321 --
322 END insert_request;
323 --
324
325
326 -- ------------------------- spawn_slaves ------------------------
327 -- Description: The appropriate concurrent program for the current phase
328 -- is spawned and details recorded (by calling insert_request).
329 --
330 --
331 -- Input Parameters
332 -- p_current_phase - phase code
333 --
334 -- r_migration_data - record containing migration information
335 --
336 --
337 -- Output Parameters
338 -- <none>
339 --
340 --
341 -- ------------------------------------------------------------------------
342
343 --
344 PROCEDURE spawn_slaves(p_current_phase IN VARCHAR2,
345 r_migration_data IN ben_dm_utility.r_migration_rec) IS
346 --
347
348 l_counter NUMBER;
349 l_request_id NUMBER;
350 e_fatal_error EXCEPTION;
351 l_fatal_error_message VARCHAR2(200);
352 l_slave_program VARCHAR2(30);
353 l_phase_id NUMBER;
354 l_threads NUMBER;
355 l_sub_request boolean := true;
356 l_no_of_files number;
357 l_argument1 varchar2(255);
358 l_argument2 varchar2(255);
359 l_argument3 varchar2(255);
360 l_argument4 varchar2(255);
361 l_argument5 varchar2(255);
362 l_argument6 varchar2(255);
363 l_argument7 varchar2(255);
364 l_argument8 varchar2(255);
365
366 --
367 BEGIN
368 --
369
370 ben_dm_utility.message('ROUT','entry:ben_dm_master.spawn_slaves', 5);
371 ben_dm_utility.message('PARA','(p_current_phase - ' || p_current_phase ||
372 ')(r_migration_data - record)', 10);
373
374
375 -- set up name for appropriate concurrent slave
376 l_slave_program := 'BENDMSLV' || p_current_phase;
377
378 -- get the current phase_id
379 if p_current_phase <> 'UF' then
380 l_phase_id := ben_dm_utility.get_phase_id(p_current_phase,
381 r_migration_data.migration_id);
382 else
383 l_phase_id := -1;
384 end if;
385
386 -- find the number of threads to use
387 l_threads := ben_dm_utility.number_of_threads(fnd_profile.value('PER_BUSINESS_GROUP_ID'));
388
389 if l_threads is null then
390 l_threads := 3;
391 end if;
392
393 -- only single thread for UF phase
394 IF (p_current_phase = 'UF') THEN
395 l_argument1 := '1';
396 l_argument2 := r_migration_data.input_parameter_file_path;
397 l_argument3 := g_transfer_file_name;
398 l_argument4 := g_data_file_name;
399
400 ben_dm_create_control_files.get_no_of_inp_files
401 (p_dir_name => l_argument2,
402 p_data_file => g_data_file_name,
403 p_no_of_files => l_argument5);
404
405 ben_dm_utility.message('INFO','p_no_of_files ' || l_argument5, 17);
406 IF nvl(l_argument5,0) = 0 THEN
407 l_fatal_error_message := 'Unable to find input data files';
408 report_error(p_current_phase, r_migration_data.migration_id,
409 l_fatal_error_message, 'P');
410 RAISE e_fatal_error;
411 END IF;
412
413 ben_dm_create_control_files.main
414 (p_dir_name => l_argument2,
415 p_no_of_threads => l_threads,
416 p_transfer_file => l_argument3,
417 p_data_file => l_argument4);
418
419 l_threads := 1;
420 END IF;
421
422 -- only single thread for LF phase
423 IF (p_current_phase = 'LF') THEN
424 l_threads := 1;
425 l_slave_program := 'BENDMSLV' ||'UF';
426 l_argument1 := '2';
427 l_argument2 := r_migration_data.input_parameter_file_path;
428 l_argument3 := g_transfer_file_name;
429 l_argument4 := g_data_file_name;
430
431 ben_dm_create_control_files.get_no_of_inp_files
432 (p_dir_name => l_argument2,
433 p_data_file => g_data_file_name,
434 p_no_of_files => l_argument5);
435
436 END IF;
437
438 IF (p_current_phase in ('G','DP','UP','DE')) THEN
439 l_sub_request := true;
440 l_argument1 := to_char(r_migration_data.migration_id);
441 l_argument2 := 'Y';
442 l_argument3 := to_char(r_migration_data.last_migration_date,'YYYY/MM/DD HH24:MI:SS');
443 l_argument4 := null;
444 l_argument5 := r_migration_data.input_parameter_file_path;
445 l_argument6 := g_data_file_name;
446 l_argument7 := g_delimiter;
447 l_argument8 := fnd_profile.value('PER_BUSINESS_GROUP_ID');
448
449 --
450 --set UP phase threads same as number of files
451 --
452 if p_current_phase = 'DP' then
453
454 ben_dm_create_control_files.touch_files
455 (p_dir_name => l_argument5,
456 p_no_of_threads => l_threads,
457 p_data_file => g_data_file_name,
458 p_file_type => 'in');
459
460 elsif p_current_phase = 'UP' then
461 ben_dm_create_control_files.get_no_of_inp_files
462 (p_dir_name => l_argument5,
463 p_data_file => g_data_file_name,
464 p_no_of_files => l_threads);
465
466 l_threads := nvl(l_threads,0);
467 ben_dm_utility.message('INFO','p_no_of_files ' || l_threads, 17);
468 IF nvl(l_threads,0) = 0 THEN
469 l_fatal_error_message := 'Unable to find input data files';
470 report_error(p_current_phase, r_migration_data.migration_id,
471 l_fatal_error_message, 'P');
472 RAISE e_fatal_error;
473 END IF;
474
475 end if;
476
477 END IF;
478
479
480 FOR l_counter IN 1..l_threads LOOP
481 ben_dm_utility.message('INFO','Spawning slave #' || l_counter, 16);
482 ben_dm_utility.message('INFO','submiting request for ' || l_slave_program , 5);
483
484 l_request_id := fnd_request.submit_request(
485 application => 'BEN',
486 program => l_slave_program,
487 sub_request => l_sub_request,
488 argument1 => l_argument1,
489 argument2 => l_argument2,
490 argument3 => l_argument3,
491 argument4 => nvl(l_argument4,to_char(l_counter)),
492 argument5 => l_argument5,
493 argument6 => l_argument6,
494 argument7 => l_argument7,
495 argument8 => l_argument8);
496
497
498 -- update table ben_dm_migration_requests
499 insert_request(p_phase => p_current_phase,
500 p_request_id => l_request_id,
501 p_master_slave => 'S',
502 p_migration_id => r_migration_data.migration_id,
503 p_phase_id => l_phase_id);
504
505 COMMIT;
506
507 ben_dm_utility.message('INFO','Slave request ID#' || l_request_id, 17);
508 IF (l_request_id = 0) THEN
509 l_fatal_error_message := 'Unable to start slave process';
510 report_error(p_current_phase, r_migration_data.migration_id,
511 l_fatal_error_message, 'P');
512 RAISE e_fatal_error;
513 END IF;
514 END LOOP;
515
516 ben_dm_utility.message('INFO','Spawned slaves', 15);
517 ben_dm_utility.message('SUMM','Spawned slaves', 20);
518 ben_dm_utility.message('ROUT','exit:ben_dm_master.spawn_slaves', 25);
519 ben_dm_utility.message('PARA','(none)', 30);
520
521 EXCEPTION
522 WHEN e_fatal_error THEN
523 ben_dm_utility.error(SQLCODE,'ben_dm_master.spawn_slaves',
524 l_fatal_error_message,'R');
525 report_error(p_current_phase, r_migration_data.migration_id,
526 'Error in ben_dm_master.spawn_slaves', 'P');
527 RAISE;
528 WHEN OTHERS THEN
529 ben_dm_utility.error(SQLCODE,'ben_dm_master.spawn_slaves','(none)','R');
530 report_error(p_current_phase, r_migration_data.migration_id,
531 'Untrapped error in ben_dm_master.spawn_slaves', 'P');
532 RAISE;
533
534 --
535 END spawn_slaves;
536 --
537
538 -- ------------------------- slave_status ------------------------
539 -- Description: The status of all slaves submitted for the passed phase
540 -- are checked to ensure that all have completed normally, otherwise
541 -- the return value is set to Y.
542 --
543 --
544 -- Input Parameters
545 -- p_current_phase - phase code
546 --
547 -- r_migration_data - record containing migration information
548 --
549 --
550 -- Return Values
551 -- slave status - Y = one or more slaves errored
552 -- N = no slaves errored
553 --
554 --
555 -- ------------------------------------------------------------------------
556
557 --
558 FUNCTION slave_status(p_current_phase IN VARCHAR2,
559 r_migration_data IN ben_dm_utility.r_migration_rec)
560 RETURN VARCHAR2 IS
561 --
562
563 l_slave_status VARCHAR2(1);
564 l_slave_error VARCHAR2(1) := 'N';
565 l_call_status BOOLEAN;
566 l_phase VARCHAR2(30);
567 l_status VARCHAR2(30);
568 l_dev_phase VARCHAR2(30);
569 l_dev_status VARCHAR2(30);
570 l_message VARCHAR2(240);
571 l_request_id NUMBER;
572 l_phase_id NUMBER;
573
574 CURSOR csr_requests(p_migration_id number,
575 p_phase_id number) IS
576 SELECT request_id
577 FROM ben_dm_migration_requests
578 WHERE migration_id = p_migration_id
579 AND phase_id = p_phase_id
580 AND master_slave = 'S'
581 AND enabled_flag = 'Y';
582 --
583 BEGIN
584 --
585
586 ben_dm_utility.message('ROUT','entry:ben_dm_master.slave_status', 5);
587 ben_dm_utility.message('PARA','(p_current_phase - ' || p_current_phase ||
588 ')(r_migration_data - record)', 10);
589
590 -- get the current phase_id
591 if p_current_phase <> 'UF' then
592 l_phase_id := ben_dm_utility.get_phase_id(p_current_phase,
593 r_migration_data.migration_id);
594 else
595 l_phase_id := -1;
596 end if;
597
598 -- check if a slave has errored
599 OPEN csr_requests(r_migration_data.migration_id,
600 l_phase_id);
601 LOOP
602 FETCH csr_requests INTO l_request_id;
603 EXIT WHEN csr_requests%NOTFOUND;
604
605 l_call_status := fnd_concurrent.get_request_status(l_request_id, '', '',
606 l_phase, l_status, l_dev_phase,
607 l_dev_status, l_message);
608 -- make sure that each slave is complete and normal, if not then log
609 IF ( NOT( (l_dev_phase = 'COMPLETE') AND (l_dev_status = 'NORMAL') )) THEN
610 l_slave_error := 'Y';
611 END IF;
612
613 END LOOP;
614 CLOSE csr_requests;
615
616
617 ben_dm_utility.message('INFO','Slave status request', 15);
618 ben_dm_utility.message('SUMM','Slave status request', 20);
619 ben_dm_utility.message('ROUT','exit:ben_dm_master.spawn_slaves', 25);
620 ben_dm_utility.message('PARA','(l_slave_error - ' || l_slave_error ||
621 ')', 30);
622
623
624 RETURN(l_slave_error);
625
626 -- error handling
627 EXCEPTION
628 WHEN OTHERS THEN
629 ben_dm_utility.error(SQLCODE,'ben_dm_master.slave_status','(none)','R');
630 report_error(p_current_phase, r_migration_data.migration_id,
631 'Untrapped error in ben_dm_master.slave_status', 'P');
632 RAISE;
633
634 --
635 END slave_status;
636 --
637
638 -- ------------------------- report_error ------------------------
639 -- Description: Reports the fact that an error has occurred and updates
640 -- the status of the phase / migration as appropriate.
641 --
642 --
643 -- Input Parameters
644 -- p_current_phase - phase code
645 --
646 -- p_migration - migration id
647 --
648 -- p_error_message - error text
649 --
650 -- p_stage - P = error occurred in a phase
651 -- M = error occured in migration
652 --
653 --
654 -- Output Parameters
655 -- <none>
656 --
657 --
658 -- ------------------------------------------------------------------------
659
660 --
661 PROCEDURE report_error(p_current_phase IN VARCHAR2,
662 p_migration IN NUMBER,
663 p_error_message IN VARCHAR2,
664 p_stage IN VARCHAR2
665 ) IS
666 --
667
668 --
669 BEGIN
670 --
671
672 ben_dm_utility.message('ROUT','entry:ben_dm_master.report_error', 5);
673 ben_dm_utility.message('PARA','(p_current_phase - ' || p_current_phase ||
674 ')(p_migration - ' || p_migration ||
675 ')(p_error_message - ' || p_error_message ||
676 ')(p_stage - ' || p_stage || ')', 10);
677
678
679 -- update status to show error (E)
680 -- update approriate phase, migration, etc.
681 IF (p_stage = 'P') THEN
682 ben_dm_utility.update_phases(p_new_status => 'E',
683 p_id => ben_dm_utility.get_phase_id(
684 p_current_phase, p_migration));
685 END IF;
686
687 IF (p_stage = 'M') THEN
688 ben_dm_utility.update_migrations(p_new_status => 'E', p_id => p_migration);
689 END IF;
690
691
692 ben_dm_utility.message('INFO','Error reported', 15);
693 ben_dm_utility.message('SUMM','Error reported', 20);
694 ben_dm_utility.message('ROUT','exit:ben_dm_master.report_error', 25);
695 ben_dm_utility.message('PARA','(none)', 30);
696
697
698 --
699 END report_error;
700 --
701
702 -- ------------------------- work_required ------------------------
703 -- Description: A check is made to see if for the phase passed if there
704 -- are any phase items which do not have a status of C. If all have the
705 -- status C then the phase is marked as completed. Where a phase links
706 -- to migration ranges then these are also checked to see if for a
707 -- phase item that there are ranges to process.
708 --
709 --
710 -- Input Parameters
711 -- p_current_phase - phase code
712 --
713 -- r_migration_data - record containing migration information
714 --
715 --
716 -- Return Values
717 -- work required - Y = phase items to process
718 -- N = phase items to process
719 --
720 --
721 -- ------------------------------------------------------------------------
722
723 --
724 FUNCTION work_required(p_current_phase IN VARCHAR2,
725 r_migration_data IN ben_dm_utility.r_migration_rec)
726 RETURN VARCHAR2 IS
727 --
728
729 l_work VARCHAR2(1);
730 l_phase_id NUMBER;
731 l_range_phase_id NUMBER;
732 l_required NUMBER;
733 l_phase_item_id NUMBER;
734 l_group_id NUMBER;
735
736 CURSOR csr_phase_items IS
737 SELECT NULL
738 FROM ben_dm_phase_items
739 WHERE phase_id = l_phase_id
740 AND status <> 'C';
741
742 CURSOR csr_ddp_pi IS
743 SELECT phase_item_id, group_order
744 FROM ben_dm_phase_items
745 WHERE phase_id = l_phase_id;
746
747 --
748 BEGIN
749 --
750
751 ben_dm_utility.message('ROUT','entry:ben_dm_master.work_required', 5);
752 ben_dm_utility.message('PARA','(p_current_phase - ' || p_current_phase ||
753 ')(r_migration_data - record)', 10);
754
755 ben_dm_utility.message('INFO','Migration Id ' || r_migration_data.migration_id, 17);
756
757 -- get the current phase_id
758 l_phase_id := ben_dm_utility.get_phase_id(p_current_phase,
759 r_migration_data.migration_id);
760
761 l_work := 'Y';
762
763 ben_dm_utility.message('INFO','Phase Id ' || l_phase_id, 19);
764
765 -- check if any phase items to process
766 IF (p_current_phase IN ('G', 'DP', 'CF', 'UP','RC')) THEN
767 OPEN csr_phase_items;
768 FETCH csr_phase_items INTO l_required;
769 IF (csr_phase_items%NOTFOUND) THEN
770 ben_dm_utility.message('INFO','No work required for phase ' ||
771 p_current_phase, 11);
772 l_work := 'N';
773 ben_dm_utility.update_phases(p_new_status => 'C',
774 p_id => l_phase_id);
775 END IF;
776 CLOSE csr_phase_items;
777 END IF;
778
779 IF (ben_dm_utility.get_phase_status(p_current_phase,
780 r_migration_data.migration_id) = 'C') THEN
781 l_work := 'N';
782 END IF;
783
784
785 ben_dm_utility.message('INFO','Check work required for phase', 15);
786 ben_dm_utility.message('SUMM','Check work required for phase', 20);
787 ben_dm_utility.message('ROUT','exit:ben_dm_master.work_required', 25);
788 ben_dm_utility.message('PARA','(l_work - ' || l_work || ')', 30);
789
790
791 RETURN(l_work);
792
793 -- error handling
794 EXCEPTION
795 WHEN OTHERS THEN
796 ben_dm_utility.error(SQLCODE,'ben_dm_master.work_required','(none)','R');
797 report_error(p_current_phase, r_migration_data.migration_id,
798 'Untrapped error in ben_dm_master.work_required', 'P');
799 RAISE;
800
801 --
802 END work_required;
803 --
804
805
806
807 -- ------------------------------- master ------------------------------
808 -- Description: This procedure calls the code appropriate for a phase
809 -- which is processed by multiple threads. There are two paths through
810 -- the code, dependent upon if the procedure is called at the start of
811 -- a phase or after the main controller has been awoken after the slaves
812 -- have finished processing. This is determined by examining the
813 -- concurrent request data which will contain the phase code for a
814 -- re-awoken phase and null for a first run.
815 --
816 -- On a first run (l_request_data = null) the status of the previous phase
817 -- is checked to ensure that it has completed. If not then the migration
818 -- status is set to error and the procedure exits.
819 --
820 -- A check is made to see if the current phase has either started or errored
821 -- in which case a rollback of the phase (if applicable) is performed.
822 --
823 -- The status of the phase is set to started and any phase specific
824 -- pre-processing code is called.
825 --
826 -- A check is made to see if there is any work to be performed by the phase
827 -- if so, slaves are spawned to perform the work and the main controller
828 -- is paused.
829 --
830 --
831 --
832 -- On a non-first run (l_request_data <> null), the request data is first
833 -- set to ? (equivelent to null) the status of the current phase
834 -- is checked to ensure that it has completed. If not then the migration
835 -- status is set to error and the procedure exits.
836 --
837 -- A check is made of the status of each slave and if any have not completed
838 -- normally then a warning message is logged.
839 --
840 -- Any phase specific post-processing code is called.
841 --
842 --
843 --
844 -- Input Parameters
845 -- p_current_phase - phase code
846 --
847 -- p_previous_phase - phase code
848 --
849 -- r_migration_data - migration record
850 --
851 --
852 -- Output Parameters
853 -- <none>
854 --
855 --
856 -- ------------------------------------------------------------------------
857
858 --
859 PROCEDURE master(p_current_phase IN VARCHAR2,
860 p_previous_phase IN VARCHAR2,
861 r_migration_data IN ben_dm_utility.r_migration_rec) IS
862 --
863
864 l_current_phase_status VARCHAR2(30);
865 e_fatal_error EXCEPTION;
866 l_fatal_error_message VARCHAR2(200);
867 l_request_data VARCHAR2(30);
868 l_dummy VARCHAR2(1);
869 l_slave_errored varchar2(30) := 'N';
870
871 --
872 BEGIN
873 --
874
875 ben_dm_utility.message('ROUT','entry:ben_dm_master.master', 5);
876 ben_dm_utility.message('PARA','(p_current_phase - ' || p_current_phase ||
877 ')(p_previous_phase - ' || p_previous_phase ||
878 ')(r_migration_data - record)', 10);
879
880 -- see if this is the first run? (l_request_data = NULL or '?')
881 -- or
882 -- is it a restart after slaves have finished? (l_request_data =
883 -- paused phase code)
884 l_request_data := fnd_conc_global.request_data;
885
886 ben_dm_utility.message('INFO','l_request_data ' || l_request_data, 11);
887 IF (NVL(l_request_data, '?') = '?') THEN
888 -- first run processing...
889
890 ben_dm_utility.message('INFO','First run processing (pre-pause)', 12);
891
892 -- get status of previous phase, is previous phase completed?
893 -- for the first phase there is no previous phase, so check for
894 -- a NULL previous to bypass this check
895 IF ((ben_dm_utility.get_phase_status(p_previous_phase,
896 r_migration_data.migration_id) <> 'C')
897 AND (p_previous_phase <> 'START') ) THEN
898 l_fatal_error_message := 'Previous phase has not completed';
899 report_error(p_current_phase, r_migration_data.migration_id,
900 l_fatal_error_message, 'P');
901 RAISE e_fatal_error;
902 END IF;
903
904 -- get status of current phase
905 l_current_phase_status := ben_dm_utility.get_phase_status(p_current_phase,
906 r_migration_data.migration_id);
907
908 -- is phase complete?
909 IF (l_current_phase_status <> 'C') THEN
910 -- do we need to explicitly rollback using rollback utility?
911 IF ( (l_current_phase_status IN('S', 'E')) AND
912 (p_current_phase IN('I', 'G', 'DP',
913 'UP', 'CF', 'D','DE')) ) THEN
914 ben_dm_utility.rollback(p_phase => p_current_phase,
915 p_masterslave => 'MASTER',
916 p_migration_id =>
917 r_migration_data.migration_id);
918 END IF;
919
920 -- update status to started
921 ben_dm_utility.update_phases(p_new_status => 'S',
922 p_id => ben_dm_utility.get_phase_id(
923 p_current_phase,
924 r_migration_data.migration_id));
925 COMMIT;
926
927 -- call phase specific processing code
928 -- spawn off slaves if work to be done
929 IF (work_required(p_current_phase, r_migration_data) = 'Y') THEN
930 spawn_slaves(p_current_phase, r_migration_data);
931
932 -- pause master whilst slaves process data...
933 -- set request data to indicate paused phase
934 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
935 request_data => p_current_phase);
936
937 END IF;
938
939 END IF;
940
941 ELSE
942 -- processing after being woken up
943 ben_dm_utility.message('INFO','Unpaused processing', 13);
944 -- set request data to indicate un-paused phase
945 fnd_conc_global.set_req_globals(request_data => '?');
946
947
948 -- first force check for all required work
949 l_dummy := work_required(p_current_phase, r_migration_data);
950
951 l_slave_errored := slave_status(p_current_phase, r_migration_data);
952
953
954 if p_current_phase in ('DE','LF','UF') then
955 if l_slave_errored = 'Y' then
956 l_current_phase_status := 'E';
957 else
958 l_current_phase_status := 'C';
959 end if;
960 ben_dm_utility.update_phases(p_new_status => l_current_phase_status,
961 p_id => ben_dm_utility.get_phase_id(p_current_phase, r_migration_data.migration_id));
962 else
963 l_current_phase_status := ben_dm_utility.get_phase_status(p_current_phase,
964 r_migration_data.migration_id);
965 end if;
966
967 -- is it completed?
968 IF (l_current_phase_status <> 'C') THEN
969 l_fatal_error_message := 'Current phase has not completed';
970 report_error(p_current_phase, r_migration_data.migration_id,
971 l_fatal_error_message, 'P');
972 RAISE e_fatal_error;
973 END IF;
974
975 -- has any slave errored?
976 -- if so, add warning message to log
977 IF (l_slave_errored = 'Y') THEN
978 ben_dm_utility.message('INFO', 'Warning - ' || p_current_phase ||
979 ' phase slave process errored', 13);
980 END IF;
981
982
983 IF (p_current_phase = 'UP') THEN
984 -- Set the variable so as to disable the trigger on the table.
985 hr_general.g_data_migrator_mode := 'Y';
986 END IF;
987
988 END IF;
989
990 ben_dm_utility.message('INFO','Master concurrent program', 15);
991 ben_dm_utility.message('SUMM','Master concurrent program', 20);
992 ben_dm_utility.message('ROUT','exit:ben_dm_master.master', 25);
993 ben_dm_utility.message('PARA','(none)', 30);
994
995
996
997 -- error handling
998 EXCEPTION
999 WHEN e_fatal_error THEN
1000 ben_dm_utility.error(SQLCODE,'hr_dm_range.master',
1001 l_fatal_error_message,'R');
1002 report_error(p_current_phase, r_migration_data.migration_id,
1003 'Untrapped error in ben_dm_master.master', 'P');
1004 RAISE;
1005 WHEN OTHERS THEN
1006 ben_dm_utility.error(SQLCODE,'ben_dm_master.master','(none)','R');
1007 report_error(p_current_phase, r_migration_data.migration_id,
1008 'Untrapped error in ben_dm_master.master', 'P');
1009 RAISE;
1010 --
1011 END master;
1012 --
1013
1014 /*---------------------------- PUBLIC ROUTINES --------------------------*/
1015
1016 -- ------------------------- Start_Download ------------------------
1017 -- Description: Start Download
1018 --
1019 --
1020 -- Input Parameters
1021 --
1022 --
1023 -- Output Parameters
1024 -- errbuf - buffer for output message (for CM manager)
1025 --
1026 -- retcode - program return code (for CM manager)
1027 --
1028 --
1029 -- ------------------------------------------------------------------------
1030
1031 --
1032
1033 -- ------------------------- main_controller ------------------------
1034 -- Description: This is the main controller for the PM which is called
1035 -- from the SRS. It calls the required phases for the migration.
1036 --
1037 -- A check is made to see if the code is running for the first time or has
1038 -- been re-awoken.
1039 --
1040 -- The initialization code is called to set up various details (see the
1041 -- controller_init procedure).
1042 --
1043 -- The next phase to be process is found using the hr_dm_phase_rules table
1044 -- and the appropriate single or multi-threaded code is called.
1045 --
1046 -- If a multi-threaded process has spawned slaves then the main controller
1047 -- exits (to enable it to be awoken later). Otherwise it runs the next
1048 -- phase until all phases have been completed.
1049 --
1050 --
1051 -- Input Parameters
1052 -- p_migration_id - of current migration
1053 -- p_migration_name - Migration Name
1054 -- p_input_file_path
1055 -- p_input_file_name
1056 -- p_output_file_path
1057 -- p_output_file_name
1058 -- p_migration_type
1059 -- p_restart_migration_id
1060 -- p_disable_generation
1061 --
1062 --
1063 -- Output Parameters
1064 -- errbuf - buffer for output message (for CM manager)
1065 --
1066 -- retcode - program return code (for CM manager)
1067 --
1068 --
1069 -- ------------------------------------------------------------------------
1070
1071 --
1072 PROCEDURE main_controller(errbuf OUT nocopy VARCHAR2,
1073 retcode OUT nocopy NUMBER,
1074 p_migration_id IN BINARY_INTEGER,
1075 p_migration_name IN VARCHAR2,
1076 p_input_file_path IN VARCHAR2,
1077 p_input_file_name IN VARCHAR2,
1078 p_output_file_path IN VARCHAR2,
1079 p_output_file_name IN VARCHAR2,
1080 p_migration_type IN VARCHAR2,
1081 p_restart_migration_id IN NUMBER,
1082 p_disable_generation IN VARCHAR2
1083 ) IS
1084 --
1085
1086 l_search_phase VARCHAR2(30);
1087 l_next_phase VARCHAR2(30);
1088 l_phase_name VARCHAR2(30);
1089 l_previous_phase VARCHAR2(30);
1090 l_previous VARCHAR2(30);
1091 e_fatal_error EXCEPTION;
1092 l_fatal_error_message VARCHAR2(200);
1093 r_migration_data ben_dm_utility.r_migration_rec;
1094 l_database_location VARCHAR2(30);
1095 l_request_data VARCHAR2(60);
1096 l_mig_status VARCHAR2(30);
1097 l_loader_lct_file VARCHAR2(100);
1098 l_data_file VARCHAR2(100);
1099 l_request_id NUMBER(15);
1100 l_migration_id number(15);
1101 l_db_loc VARCHAR2(30);
1102
1103 CURSOR csr_phase_rule IS
1104 SELECT phase_name, next_phase, database_location
1105 FROM hr_dm_phase_rules
1106 WHERE ((migration_type = 'SP')
1107 AND (previous_phase = l_search_phase))
1108 AND database_location like l_db_loc;
1109
1110 CURSOR csr_paused_phase_rule IS
1111 SELECT previous_phase, next_phase
1112 FROM hr_dm_phase_rules
1113 WHERE ((migration_type = 'SP')
1114 AND (phase_name = l_request_data));
1115
1116 CURSOR csr_mig_status IS
1117 SELECT status
1118 FROM ben_dm_migrations
1119 WHERE migration_id = r_migration_data.migration_id;
1120
1121 --
1122 BEGIN
1123 --
1124
1125 --
1126 -- Set if the database_location to be used in the csr_phase_rule
1127 -- cursor
1128 --
1129 IF p_migration_type = 'SU' or p_migration_type = 'RU' then
1130 l_db_loc := '%D%';
1131 ELSE
1132 l_db_loc := '%S%';
1133 END IF;
1134
1135 -- initialize messaging
1136 ben_dm_utility.message_init;
1137 --
1138 -- see if this is a migration run (l_request_data = NULL)
1139 -- OR
1140 -- is it a restart after slaves have finished? (l_request_data =
1141 -- paused phase code)
1142 l_request_data := fnd_conc_global.request_data;
1143 ben_dm_utility.message('INFO','Request data - ' || l_request_data, 12);
1144 IF (l_request_data IS NULL) THEN
1145
1146 If p_migration_type = 'SD' then
1147 ben_dm_utility.message('INFO','Start of Truncate from ben_dm_entity_results, ben_dm_resolve_mappings and ben_dm_input_file tables', 15);
1148 ben_dm_utility.message('SUMM','Start of Truncate from ben_dm_entity_results, ben_dm_resolve_mappings and ben_dm_input_file tables', 20);
1149
1150 -- Truncate From ben_dm_entity_results, ben_dm_resolve_mappings and ben_dm_input_file tables.
1151
1152 execute immediate 'truncate table '||getTableSchema||'.ben_dm_entity_results';
1153 execute immediate 'truncate table '||getTableSchema||'.ben_dm_resolve_mappings';
1154 execute immediate 'truncate table '||getTableSchema||'.ben_dm_input_file';
1155
1156 commit;
1157
1158 ben_dm_utility.message('INFO','End Truncate of ben_dm_entity_results, ben_dm_resolve_mappings and ben_dm_input_file tables', 15);
1159 ben_dm_utility.message('SUMM','End Truncate of ben_dm_entity_results, ben_dm_resolve_mappings and ben_dm_input_file tables', 20);
1160
1161 --
1162 -- We are ready to do the insert, so pull the next
1163 -- sequence number from ben_dm_migrations_s.
1164 --
1165 SELECT ben_dm_migrations_s.nextval
1166 INTO l_migration_id
1167 FROM dual;
1168
1169 INSERT INTO ben_dm_migrations
1170 (migration_id
1171 ,migration_name
1172 ,source_migration_id
1173 ,input_parameter_file_path
1174 ,input_paramater_file_name
1175 ,Data_file_path
1176 ,Data_file_name
1177 ,migration_start_date
1178 ,migration_end_date
1179 ,status
1180 ,migration_type
1181 ,effective_date
1182 ,migration_count
1183 ,last_updated_by
1184 ,last_update_date
1185 ,last_update_login
1186 ,creation_date
1187 ,created_by
1188 ,database_location
1189 )
1190 VALUES
1191 (l_migration_id
1192 ,p_migration_name
1193 ,NULL
1194 ,p_input_file_path
1195 ,nvl(p_input_file_name,'null')
1196 ,p_output_file_path
1197 ,p_output_file_name
1198 ,SYSDATE
1199 ,NULL
1200 ,'NS'
1201 ,NULL
1202 ,SYSDATE
1203 ,0
1204 ,fnd_global.user_id
1205 ,SYSDATE
1206 ,fnd_global.user_id
1207 ,SYSDATE
1208 ,fnd_global.user_id
1209 ,'S'
1210 );
1211
1212 Commit;
1213
1214 select fnd_global.conc_request_id
1215 into l_request_id
1216 from dual;
1217
1218 IF l_request_id = 0 THEN
1219 ben_dm_utility.message('INFO','Problem with Inserting into BEN_DM_MIGRATION_REQUESTS', 15);
1220 ben_dm_utility.message('SUMM','Problem with Inserting into BEN_DM_MIGRATION_REQUESTS', 20);
1221 ELSE
1222 --
1223 -- The request submission was successful - insert
1224 -- a row into ben_dm_migration_requests so that
1225 -- the user can view the logfile later on.
1226 --
1227 INSERT INTO ben_dm_migration_requests
1228 (migration_request_id
1229 ,phase_id
1230 ,phase_item_id
1231 ,migration_id
1232 ,request_id
1233 ,master_slave
1234 ,enabled_flag
1235 ,last_update_date
1236 ,last_updated_by
1237 ,last_update_login
1238 ,created_by
1239 ,creation_date
1240 )
1241 VALUES
1242 (ben_dm_migration_requests_s.nextval
1243 ,-1
1244 ,-1
1245 ,l_migration_id
1246 ,l_request_id
1247 ,'M'
1248 ,'Y'
1249 ,SYSDATE
1250 ,fnd_global.user_id
1251 ,fnd_global.login_id
1252 ,fnd_global.user_id
1253 ,SYSDATE
1254 );
1255
1256 END IF;
1257
1258 -- seed data into record
1259 r_migration_data.migration_id := l_migration_id;
1260 r_migration_data.migration_name := p_migration_name;
1261 r_migration_data.input_parameter_file_name := p_input_file_name;
1262 r_migration_data.input_parameter_file_path := p_input_file_path;
1263 r_migration_data.data_file_name := p_output_file_name;
1264 r_migration_data.data_file_path := p_input_file_path;
1265 r_migration_data.database_location := 'S';
1266
1267 -- p_migration_id := l_migration_id;
1268
1269 -- Read the Input File and Process the Data
1270 ben_dm_utility.message('INFO','Start Of Process Input File', 15);
1271 ben_dm_utility.message('SUMM','Start Of Process Input File', 20);
1272
1273 ben_dm_input_file_pkg.read_file(r_migration_data);
1274
1275 ben_dm_utility.message('INFO','End Of Process Input File', 15);
1276 ben_dm_utility.message('SUMM','End Of Process Input File', 20);
1277
1278
1279
1280 end if;
1281
1282 -- New code
1283 If p_migration_type = 'SU' then
1284 ben_dm_utility.message('INFO','Start of the SU Phase', 15);
1285 ben_dm_utility.message('SUMM','Start of the SU Phase', 20);
1286 ben_dm_utility.message('INFO','Start of Truncate from ben_dm_entity_results, ben_dm_resolve_mappings and ben_dm_input_file tables', 15);
1287 ben_dm_utility.message('SUMM','Start of Truncate from ben_dm_entity_results, ben_dm_resolve_mappings and ben_dm_input_file tables', 20);
1288
1289 -- Truncate From ben_dm_entity_results, ben_dm_resolve_mappings and ben_dm_input_file tables.
1290
1291 execute immediate 'truncate table '||getTableSchema||'.ben_dm_entity_results';
1292 execute immediate 'truncate table '||getTableSchema||'.ben_dm_resolve_mappings';
1293 execute immediate 'truncate table '||getTableSchema||'.ben_dm_input_file';
1294
1295 commit;
1296
1297 ben_dm_utility.message('INFO','End Truncate of ben_dm_entity_results, ben_dm_resolve_mappings and ben_dm_input_file tables', 15);
1298 ben_dm_utility.message('SUMM','End Truncate of ben_dm_entity_results, ben_dm_resolve_mappings and ben_dm_input_file tables', 20);
1299
1300 --
1301 -- We are ready to do the insert, so pull the next
1302 -- sequence number from ben_dm_migrations_s.
1303 --
1304 SELECT ben_dm_migrations_s.nextval
1305 INTO l_migration_id
1306 FROM dual;
1307
1308 INSERT INTO ben_dm_migrations
1309 (migration_id
1310 ,migration_name
1311 ,source_migration_id
1312 ,input_parameter_file_path
1313 ,input_paramater_file_name
1314 ,Data_file_path
1315 ,Data_file_name
1316 ,migration_start_date
1317 ,migration_end_date
1318 ,status
1319 ,migration_type
1320 ,effective_date
1321 ,migration_count
1322 ,last_updated_by
1323 ,last_update_date
1324 ,last_update_login
1325 ,creation_date
1326 ,created_by
1327 ,database_location
1328 )
1329 VALUES
1330 (l_migration_id
1331 ,p_migration_name
1332 ,NULL
1333 ,p_input_file_path
1334 ,nvl(p_input_file_name,'null')
1335 ,p_output_file_path
1336 ,p_output_file_name
1337 ,SYSDATE
1338 ,NULL
1339 ,'NS'
1340 ,NULL
1341 ,SYSDATE
1342 ,0
1343 ,fnd_global.user_id
1344 ,SYSDATE
1345 ,fnd_global.user_id
1346 ,SYSDATE
1347 ,fnd_global.user_id
1348 ,'D'
1349 );
1350
1351 Commit;
1352
1353 select fnd_global.conc_request_id
1354 into l_request_id
1355 from dual;
1356
1357 IF l_request_id = 0 THEN
1358 ben_dm_utility.message('INFO','Problem with Inserting into BEN_DM_MIGRATION_REQUESTS', 15);
1359 ben_dm_utility.message('SUMM','Problem with Inserting into BEN_DM_MIGRATION_REQUESTS', 20);
1360 ELSE
1361 --
1362 -- The request submission was successful - insert
1363 -- a row into ben_dm_migration_requests so that
1364 -- the user can view the logfile later on.
1365 --
1366 INSERT INTO ben_dm_migration_requests
1367 (migration_request_id
1368 ,phase_id
1369 ,phase_item_id
1370 ,migration_id
1371 ,request_id
1372 ,master_slave
1373 ,enabled_flag
1374 ,last_update_date
1375 ,last_updated_by
1376 ,last_update_login
1377 ,created_by
1378 ,creation_date
1379 )
1380 VALUES
1381 (ben_dm_migration_requests_s.nextval
1382 ,-1
1383 ,-1
1384 ,l_migration_id
1385 ,l_request_id
1386 ,'M'
1387 ,'Y'
1388 ,SYSDATE
1389 ,fnd_global.user_id
1390 ,fnd_global.login_id
1391 ,fnd_global.user_id
1392 ,SYSDATE
1393 );
1394
1395 END IF;
1396
1397 -- seed data into record
1398 r_migration_data.migration_id := l_migration_id;
1399 r_migration_data.migration_name := p_migration_name;
1400 r_migration_data.input_parameter_file_name := p_input_file_name;
1401 r_migration_data.input_parameter_file_path := p_input_file_path;
1402 r_migration_data.data_file_name := p_output_file_name;
1403 r_migration_data.data_file_path := p_input_file_path;
1404 r_migration_data.database_location := 'D';
1405
1406 -- p_migration_id := l_migration_id;
1407
1408 end if;
1409
1410
1411 -- New code
1412
1413
1414 end if;
1415
1416
1417 If (p_migration_type = 'RD' or p_migration_type = 'RU') then
1418 ben_dm_utility.message('INFO','Migration Type detected'||p_migration_type, 15);
1419 ben_dm_utility.message('SUMM','Migration Type detected'||p_migration_type, 20);
1420 ben_dm_utility.message('SUMM','Re Start Migration ID : '||p_restart_migration_id, 20);
1421
1422 l_migration_id := p_restart_migration_id;
1423
1424 select fnd_global.conc_request_id
1425 into l_request_id
1426 from dual;
1427
1428 if l_request_data IS NULL THEN
1429 -- The request submission was successful - insert
1430 -- a row into ben_dm_migration_requests so that
1431 -- the user can view the logfile later on.
1432 --
1433 INSERT INTO ben_dm_migration_requests
1434 (migration_request_id
1435 ,phase_id
1436 ,phase_item_id
1437 ,migration_id
1438 ,request_id
1439 ,master_slave
1440 ,enabled_flag
1441 ,last_update_date
1442 ,last_updated_by
1443 ,last_update_login
1444 ,created_by
1445 ,creation_date
1446 )
1447 VALUES
1448 (ben_dm_migration_requests_s.nextval
1449 ,-1
1450 ,-1
1451 ,l_migration_id
1452 ,l_request_id
1453 ,'M'
1454 ,'Y'
1455 ,SYSDATE
1456 ,fnd_global.user_id
1457 ,fnd_global.login_id
1458 ,fnd_global.user_id
1459 ,SYSDATE
1460 );
1461 commit;
1462 end if;
1463
1464 end if;
1465
1466 -- initialize messaging
1467 ben_dm_utility.message_init;
1468
1469 select migration_id
1470 into l_migration_id
1471 from ben_dm_migration_requests
1472 where request_id = fnd_global.conc_request_id;
1473
1474 ben_dm_utility.message('INFO','l_migration_id : '||l_migration_id, 15);
1475 ben_dm_utility.message('SUMM','l_migration_id : '||l_migration_id, 15);
1476
1477 -- p_migration_id := l_migration_id;
1478
1479 ben_dm_utility.message('ROUT','entry:ben_dm_master.main_controller', 5);
1480 ben_dm_utility.message('PARA','(p_migration_id - ' ||
1481 l_migration_id || ')', 10);
1482
1483
1484 -- see if this is a migration run (l_request_data = NULL)
1485 -- OR
1486 -- is it a restart after slaves have finished? (l_request_data =
1487 -- paused phase code)
1488 l_request_data := fnd_conc_global.request_data;
1489 ben_dm_utility.message('INFO','Request data - ' || l_request_data, 12);
1490
1491 -- initialize main controller data
1492 controller_init(l_migration_id,
1493 r_migration_data,
1494 l_request_data);
1495
1496 -- work out phases applicable to this migration
1497 --
1498 IF (l_request_data IS NULL) THEN
1499 -- seed first phase
1500 l_search_phase := 'START';
1501 l_next_phase := '?';
1502 ELSE
1503 -- seed with data for paused phase
1504 OPEN csr_paused_phase_rule;
1505 LOOP
1506 FETCH csr_paused_phase_rule INTO l_search_phase, l_next_phase;
1507 EXIT WHEN csr_paused_phase_rule%NOTFOUND;
1508 END LOOP;
1509 CLOSE csr_paused_phase_rule;
1510 END IF;
1511
1512 -- seed previous phase name
1513 l_previous := 'START';
1514
1515 WHILE (l_next_phase <> 'END') LOOP
1516 OPEN csr_phase_rule;
1517 FETCH csr_phase_rule INTO l_phase_name, l_next_phase,
1518 l_database_location;
1519 EXIT WHEN csr_phase_rule%NOTFOUND;
1520 ben_dm_utility.message('INFO','Start of Main Loop 2', 11);
1521 ben_dm_utility.message('INFO','Start of Main Loop 2 - l_phase_name '|| l_phase_name, 11);
1522 ben_dm_utility.message('INFO','Start of Main Loop 2 - l_next_phase '|| l_next_phase, 11);
1523 ben_dm_utility.message('INFO','Start of Main Loop 2 - l_database_location '|| l_database_location, 11);
1524
1525 -- does it apply?
1526 IF (INSTR(l_database_location, r_migration_data.database_location) >0) THEN
1527
1528 ben_dm_utility.message('INFO','Current phase is ' || l_phase_name, 11);
1529 ben_dm_utility.message('INFO','Request data - ' ||
1530 NVL(l_request_data, '?'), 12);
1531
1532 -- store the previous phase that applies
1533 l_previous_phase := l_previous;
1534 ben_dm_utility.message('INFO','Previous phase is ' || l_previous_phase, 11);
1535 --
1536 -- is it completed or not awoken?
1537 -- if not, then run appropriate phase code
1538 --
1539 IF ((ben_dm_utility.get_phase_status(l_phase_name, l_migration_id) <> 'C')
1540 OR (NVL(l_request_data, '?') <> '?')) THEN
1541
1542 -- call generic master code
1543 IF (l_phase_name IN ('G', 'DP', 'UP','DE','UF','LF')) THEN
1544 --
1545 -- Derive The Target ID from Developer Keys
1546 --
1547 If l_phase_name = 'UP' THEN
1548 ben_dm_utility.message('INFO','Start of Derive Target ID', 11);
1549 ben_dm_upload_dk.get_dk_frm_all;
1550 ben_dm_utility.message('INFO','End of Derive Target ID', 11);
1551 end if;
1552
1553 master(l_phase_name, l_previous_phase, r_migration_data);
1554 ELSE
1555 -- call non-generic master code
1556 IF (l_phase_name = 'I') THEN
1557 ben_dm_init.main(r_migration_data);
1558 ELSIF (l_phase_name = 'CF') THEN
1559 ben_dm_utility.update_phases(p_new_status => 'S',
1560 p_id => ben_dm_utility.get_phase_id('CF'
1561 , r_migration_data.migration_id));
1562 ben_dm_create_transfer_file.main
1563 (p_dir_name => r_migration_data.input_parameter_file_path,
1564 p_file_name => g_transfer_file_name,
1565 p_delimiter => g_delimiter);
1566
1567 ben_dm_utility.update_phases(p_new_status => 'C',
1568 p_id => ben_dm_utility.get_phase_id('CF'
1569 , r_migration_data.migration_id));
1570
1571 ELSIF (l_phase_name = 'RC') THEN
1572 ben_dm_utility.update_phases(p_new_status => 'S',
1573 p_id => ben_dm_utility.get_phase_id('RC'
1574 , r_migration_data.migration_id));
1575
1576 ben_dm_utility.message('INFO','Start of Custom Code - RC', 11);
1577 ben_dm_custom_code.handle_custom_data(r_migration_data);
1578 ben_dm_utility.message('INFO','Start of Custom Code - RC', 11);
1579
1580 ben_dm_utility.update_phases(p_new_status => 'C',
1581 p_id => ben_dm_utility.get_phase_id('RC'
1582 ,r_migration_data.migration_id));
1583
1584 -- processing after being woken up
1585 ben_dm_utility.message('INFO','Unpaused processing', 13);
1586 -- set request data to indicate un-paused phase
1587 fnd_conc_global.set_req_globals(request_data => '?');
1588
1589 END IF;
1590
1591 END IF;
1592
1593 -- have we paused the main controller?
1594 -- if so, exit loop
1595 l_request_data := fnd_conc_global.request_data;
1596 EXIT WHEN NVL(l_request_data, '?') <> '?';
1597
1598 -- did it complete? if not then update status and raise an error
1599 IF l_phase_name <> 'UF' and
1600 (ben_dm_utility.get_phase_status(l_phase_name, l_migration_id)
1601 <> 'C') THEN
1602 -- update status of migration to error
1603 ben_dm_utility.update_migrations(p_new_status => 'E',
1604 p_id => l_migration_id);
1605 COMMIT;
1606 -- raise error
1607 l_fatal_error_message := 'Error in ' || l_phase_name || ' phase';
1608 report_error(l_phase_name, l_migration_id, l_fatal_error_message,
1609 'M');
1610 RAISE e_fatal_error;
1611 END IF;
1612
1613 END IF;
1614
1615 -- store current applicable phase name for next iteration
1616 l_previous := l_phase_name;
1617 END IF;
1618 l_search_phase := l_phase_name;
1619 CLOSE csr_phase_rule;
1620 END LOOP;
1621
1622 if p_migration_type = 'SU' and
1623 l_phase_name = 'LF' then
1624 ben_dm_create_control_files.rebuild_indexes;
1625 end if;
1626
1627 -- set up return values to concurrent manager
1628 retcode := 0;
1629 IF (NVL(l_request_data, '?') <> '?') THEN
1630 errbuf := 'No errors - examine logfiles for detailed reports.';
1631 ELSE
1632 errbuf := 'Master Controller is paused.';
1633 END IF;
1634
1635 -- see the migration has errored
1636 -- if so, error this conc. program
1637 OPEN csr_mig_status;
1638 FETCH csr_mig_status INTO l_mig_status;
1639 CLOSE csr_mig_status;
1640
1641 IF l_mig_status = 'E' THEN
1642 l_fatal_error_message := 'The migration is in error.';
1643 RAISE e_fatal_error;
1644 END IF;
1645
1646 ben_dm_utility.message('INFO','Main controller', 15);
1647 ben_dm_utility.message('SUMM','Main controller', 20);
1648 ben_dm_utility.message('ROUT','exit:ben_dm_master.main_controller', 25);
1649 ben_dm_utility.message('PARA','(retcode - ' || retcode ||
1650 ')(errbuf - ' || errbuf || ')', 30);
1651
1652 EXCEPTION
1653 WHEN e_fatal_error THEN
1654 retcode := 2;
1655 errbuf := 'An error occurred during the migration - examine logfiles' ||
1656 ' for detailed reports.';
1657 ben_dm_utility.error(SQLCODE,'hr_dm_range.main_controller',
1658 l_fatal_error_message,'R');
1659 report_error(l_phase_name, l_migration_id,
1660 l_fatal_error_message || ' in ben_dm_master.main_controller',
1661 'M');
1662 WHEN OTHERS THEN
1663 retcode := 2;
1664 errbuf := 'An error occurred during the migration - examine logfiles' ||
1665 ' for detailed reports.';
1666 ben_dm_utility.error(SQLCODE,'ben_dm_master.main_controller','(none)','R');
1667 report_error(l_phase_name, l_migration_id,
1668 'Untrapped error in ben_dm_master.main_controller', 'M');
1669
1670 --
1671 END main_controller;
1672 --
1673
1674 end ben_dm_master;