1 PACKAGE BODY hr_dm_business AS
2 /* $Header: perdmbiz.pkb 120.0 2005/05/31 17:04:52 appldev noship $ */
3
4 /*--------------------------- PRIVATE ROUTINES ---------------------------*/
5
6 -- ------------------------- rule_1_source ------------------------
7 -- Description: No migration can proceed if the current database is not
8 -- the source database.
9 --
10 -- Note - this rule is similar to rule_1_dest
11 --
12 --
13 -- Input Parameters
14 -- r_migration_data - record containing migration information
15 -- p_valid - current validity of migration
16 -- p_warning - cuurent warning of migration
17 --
18 --
19 -- Output Parameters
20 -- validity of migration - E = Error
21 -- V = Valid
22 -- W = Warning
23 --
24 -- ------------------------------------------------------------------------
25
26
27 --
28 PROCEDURE rule_1_source(r_migration_data IN hr_dm_utility.r_migration_rec,
29 p_valid IN OUT NOCOPY VARCHAR2,
30 p_warning IN OUT NOCOPY VARCHAR2) IS
31 --
32
33
34 --
35 BEGIN
36 --
37
38 hr_dm_utility.message('ROUT','entry:hr_dm_business.rule_1_source', 5);
39 hr_dm_utility.message('PARA','(r_migration_data - record)' ||
40 '(p_valid - ' || p_valid ||
41 ')(p_warning - ' || p_warning || ')', 10);
42
43 -- r_migration_data holds either S or D if in source or destination
44 -- otherwise NULL
45 IF (NVL(r_migration_data.database_location,'?') <> 'S') THEN
46 p_valid := 'E';
47 hr_dm_utility.message('INFO','Business rule 1 broken - ' ||
48 r_migration_data.database_location ||
49 ' - No migration can proceed if the current' ||
50 ' database is not the source database.', 15);
51
52 END IF;
53
54
55 hr_dm_utility.message('INFO','Validated rule 1', 215);
56 hr_dm_utility.message('SUMM','Validated rule 1', 220);
57 hr_dm_utility.message('ROUT','exit:hr_dm_business.rule_1_source', 225);
58 hr_dm_utility.message('PARA','(p_valid - ' || p_valid ||
59 ')(p_warning - ' || p_warning || ')', 230);
60
61
62 -- error handling
63 EXCEPTION
64 WHEN OTHERS THEN
65 hr_dm_utility.error(SQLCODE,'hr_dm_business.rule_1_source',
66 '(none)','R');
67 RAISE;
68
69 --
70 END rule_1_source;
71 --
72
73 -- ------------------------- rule_2_source ------------------------
74 -- Description: For any migration, the business group must exist
75 -- on the source database.
76 --
77 -- Note - this rule is similar to rule_2_dest
78 --
79 -- Input Parameters
80 -- r_migration_data - record containing migration information
81 -- p_valid - current validity of migration
82 -- p_warning - cuurent warning of migration
83 --
84 --
85 -- Output Parameters
86 -- validity of migration - E = Error
87 -- V = Valid
88 -- W = Warning
89 --
90 -- ------------------------------------------------------------------------
91
92
93 --
94 PROCEDURE rule_2_source(r_migration_data IN hr_dm_utility.r_migration_rec,
95 p_valid IN OUT NOCOPY VARCHAR2,
96 p_warning IN OUT NOCOPY VARCHAR2) IS
97 --
98
99 l_business_group_id NUMBER;
100
101 CURSOR csr_biz_grp IS
102 SELECT business_group_id
103 FROM per_business_groups
104 WHERE business_group_id = r_migration_data.business_group_id;
105
106 --
107 BEGIN
108 --
109
110 hr_dm_utility.message('ROUT','entry:hr_dm_business.rule_2_source', 5);
111 hr_dm_utility.message('PARA','(r_migration_data - record)' ||
112 '(p_valid - ' || p_valid ||
113 ')(p_warning - ' || p_warning || ')', 10);
114
115 -- r_migration_data holds the business_group_ids
116 OPEN csr_biz_grp;
117 FETCH csr_biz_grp INTO l_business_group_id;
118 CLOSE csr_biz_grp;
119 IF (r_migration_data.business_group_id <>
120 NVL(l_business_group_id, -1)) THEN
121 p_valid := 'E';
122 hr_dm_utility.message('INFO','Business rule 2 broken - ' ||
123 r_migration_data.database_location ||
124 ' - For any migration, the business group must' ||
125 ' exist on the source database.', 20);
126 END IF;
127
128 hr_dm_utility.message('INFO','Validated rule 2', 215);
129 hr_dm_utility.message('SUMM','Validated rule 2', 220);
130 hr_dm_utility.message('ROUT','exit:hr_dm_business.rule_2_source', 225);
131 hr_dm_utility.message('PARA','(p_valid - ' || p_valid ||
132 ')(p_warning - ' || p_warning || ')', 230);
133
134
135 -- error handling
136 EXCEPTION
137 WHEN OTHERS THEN
138 hr_dm_utility.error(SQLCODE,'hr_dm_business.rule_2_source',
139 '(none)','R');
140 RAISE;
141
142 --
143 END rule_2_source;
144 --
145
146 -- ------------------------- rule_8_source ------------------------
147 -- Description: Only one migration can proceed at any one time. Only
148 -- one migration at any one time may have a status of S - Started,
149 -- NS - Not Started or E - Error, indicating that a migration is in
150 -- progress. Once a migration is C - Completed on the source or
151 -- destination, then a new migration can proceed. If a migration is
152 -- to be abandoned, then it will be given a status of A - Abandoned
153 -- to enable a new migration to be started.
154 --
155 --
156 -- Input Parameters
157 -- r_migration_data - record containing migration information
158 -- p_valid - current validity of migration
159 -- p_warning - cuurent warning of migration
160 --
161 --
162 -- Output Parameters
163 -- validity of migration - E = Error
164 -- V = Valid
165 -- W = Warning
166 --
167 -- ------------------------------------------------------------------------
168
169
170 --
171 PROCEDURE rule_8_source(r_migration_data IN hr_dm_utility.r_migration_rec,
172 p_valid IN OUT NOCOPY VARCHAR2,
173 p_warning IN OUT NOCOPY VARCHAR2) IS
174 --
175
176 l_mig_count NUMBER;
177
178 CURSOR csr_mig_count IS
179 SELECT count(*)
180 FROM hr_dm_migrations
181 WHERE status IN ('S', 'NS', 'E');
182
183 --
184 BEGIN
185 --
186
187 hr_dm_utility.message('ROUT','entry:hr_dm_business.rule_8_source', 5);
188 hr_dm_utility.message('PARA','(r_migration_data - record)' ||
189 '(p_valid - ' || p_valid ||
190 ')(p_warning - ' || p_warning || ')', 10);
191
192 OPEN csr_mig_count;
193 FETCH csr_mig_count INTO l_mig_count;
194 CLOSE csr_mig_count;
195
196 IF (l_mig_count > 1) THEN
197 p_valid := 'E';
198 hr_dm_utility.message('INFO','Business rule 8 broken - ' ||
199 r_migration_data.database_location ||
200 ' - Only one migration can proceed at any one' ||
201 ' time. Only one migration at any one time may' ||
202 ' have a status of S - Started, NS - Not Started' ||
203 ' or E - Error, indicating that a migration is in' ||
204 ' progress. Once a migration is C - Completed on' ||
205 ' the source or destination, then a new migration' ||
206 ' can proceed. If a migration is to be abandoned,' ||
207 ' then it will be given a statusof A - Abandoned' ||
208 ' to enable a new migration to be started.', 40);
209 END IF;
210
211
212 hr_dm_utility.message('INFO','Validated rule 8', 215);
213 hr_dm_utility.message('SUMM','Validated rule 8', 220);
214 hr_dm_utility.message('ROUT','exit:hr_dm_business.rule_8_source', 225);
215 hr_dm_utility.message('PARA','(p_valid - ' || p_valid ||
216 ')(p_warning - ' || p_warning || ')', 230);
217
218
219 -- error handling
220 EXCEPTION
221 WHEN OTHERS THEN
222 hr_dm_utility.error(SQLCODE,'hr_dm_business.rule_8_source',
223 '(none)','R');
224 RAISE;
225
226 --
227 END rule_8_source;
228 --
229
230 -- ------------------------- rule_9_source ------------------------
231 -- Description: A migration can only be run / rerun if it has a
232 -- status of NS - Not Started or E - Error.
233 --
234 --
235 -- Input Parameters
236 -- r_migration_data - record containing migration information
237 -- p_valid - current validity of migration
238 -- p_warning - cuurent warning of migration
239 --
240 --
241 -- Output Parameters
242 -- validity of migration - E = Error
243 -- V = Valid
244 -- W = Warning
245 --
246 -- ------------------------------------------------------------------------
247
248
249 --
250 PROCEDURE rule_9_source(r_migration_data IN hr_dm_utility.r_migration_rec,
251 p_valid IN OUT NOCOPY VARCHAR2,
252 p_warning IN OUT NOCOPY VARCHAR2) IS
253 --
254
255 l_status VARCHAR2(30);
256
257 CURSOR csr_mig_status IS
258 SELECT status
259 FROM hr_dm_migrations
260 WHERE (migration_id = r_migration_data.migration_id);
261
262 --
263 BEGIN
264 --
265
266 hr_dm_utility.message('ROUT','entry:hr_dm_business.rule_9_source', 5);
267 hr_dm_utility.message('PARA','(r_migration_data - record)' ||
268 '(p_valid - ' || p_valid ||
269 ')(p_warning - ' || p_warning || ')', 10);
270
271 OPEN csr_mig_status;
272 FETCH csr_mig_status INTO l_status;
273 CLOSE csr_mig_status;
274
275 IF (l_status NOT IN ('NS', 'E')) THEN
276 p_valid := 'E';
277 hr_dm_utility.message('INFO','Business rule 9 broken - ' ||
278 r_migration_data.database_location ||
279 ' - A migration can only be rerun if it has a status' ||
280 ' of NS - Not Started or E - Error.', 45);
281 END IF;
282
283
284 hr_dm_utility.message('INFO','Validated rule 9', 215);
285 hr_dm_utility.message('SUMM','Validated rule 9', 220);
286 hr_dm_utility.message('ROUT','exit:hr_dm_business.rule_9_source', 225);
287 hr_dm_utility.message('PARA','(p_valid - ' || p_valid ||
288 ')(p_warning - ' || p_warning || ')', 230);
289
290
291 -- error handling
292 EXCEPTION
293 WHEN OTHERS THEN
294 hr_dm_utility.error(SQLCODE,'hr_dm_business.rule_9_source',
295 '(none)','R');
296 RAISE;
297
298 --
299 END rule_9_source;
300 --
301
302 -- ------------------------- rule_10_source ------------------------
303 -- Description: A migration can only be rerun if all the slaves and
304 -- sub-slaves launched by the previous run have either completed or
305 -- errored.
306
307 --
308 --
309 -- Input Parameters
310 -- r_migration_data - record containing migration information
311 -- p_valid - current validity of migration
312 -- p_warning - cuurent warning of migration
313 --
314 --
315 -- Output Parameters
316 -- validity of migration - E = Error
317 -- V = Valid
318 -- W = Warning
319 --
320 -- ------------------------------------------------------------------------
321
322
323 --
324 PROCEDURE rule_10_source(r_migration_data IN hr_dm_utility.r_migration_rec,
325 p_valid IN OUT NOCOPY VARCHAR2,
326 p_warning IN OUT NOCOPY VARCHAR2) IS
327 --
328
329 l_request_id NUMBER;
330 l_call_status BOOLEAN;
331 l_phase VARCHAR2(30);
332 l_status VARCHAR2(30);
333 l_dev_phase VARCHAR2(30);
334 l_dev_status VARCHAR2(30);
335 l_message VARCHAR2(240);
336
337 CURSOR csr_requests IS
338 SELECT request_id
339 FROM hr_dm_migration_requests
340 WHERE (migration_id = r_migration_data.migration_id)
341 AND (master_slave <> 'M');
342
343 --
344 BEGIN
345 --
346
347 hr_dm_utility.message('ROUT','entry:hr_dm_business.rule_10_source', 5);
348 hr_dm_utility.message('PARA','(r_migration_data - record)' ||
349 '(p_valid - ' || p_valid ||
350 ')(p_warning - ' || p_warning || ')', 10);
351
352 -- check if a slave has errored
353 OPEN csr_requests;
354 LOOP
355 FETCH csr_requests INTO l_request_id;
356 EXIT WHEN csr_requests%NOTFOUND;
357
358 l_call_status := fnd_concurrent.get_request_status(l_request_id, '', '',
359 l_phase, l_status, l_dev_phase,
360 l_dev_status, l_message);
361 -- make sure that each slave is complete
362 -- this includes any of normal (ie finished sucessfully), error, warning,
363 -- cancelled or terminated.
364 IF ( NOT(l_dev_phase = 'COMPLETE')) THEN
365 p_valid := 'E';
366 hr_dm_utility.message('INFO','Business rule 10 broken - ' ||
367 r_migration_data.database_location ||
368 ' - A migration can only be rerun if all the' ||
369 ' slaves and sub-slaves launched by the' ||
370 ' previous run have either completed or' ||
371 ' errored.', 50);
372 END IF;
373
374 END LOOP;
375 CLOSE csr_requests;
376
377
378 hr_dm_utility.message('INFO','Validated rule 10', 215);
379 hr_dm_utility.message('SUMM','Validated rule 10', 220);
380 hr_dm_utility.message('ROUT','exit:hr_dm_business.rule_10_source', 225);
381 hr_dm_utility.message('PARA','(p_valid - ' || p_valid ||
382 ')(p_warning - ' || p_warning || ')', 230);
383
384
385 -- error handling
386 EXCEPTION
387 WHEN OTHERS THEN
388 hr_dm_utility.error(SQLCODE,'hr_dm_business.rule_10_source',
389 '(none)','R');
390 RAISE;
391
392 --
393 END rule_10_source;
394 --
395
396
397 -- ------------------------- rule_1_dest ------------------------
398 -- Description: No migration can proceed if the current database is not
399 -- the destination database.
400 --
401 -- Note - this rule is similar to rule_1_dest
402 --
403 --
404 -- Input Parameters
405 -- r_migration_data - record containing migration information
406 -- p_valid - current validity of migration
407 -- p_warning - cuurent warning of migration
408 --
409 --
410 -- Output Parameters
411 -- validity of migration - E = Error
412 -- V = Valid
413 -- W = Warning
414 --
415 -- ------------------------------------------------------------------------
416
417
418 --
419 PROCEDURE rule_1_dest(r_migration_data IN hr_dm_utility.r_migration_rec,
420 p_valid IN OUT NOCOPY VARCHAR2,
421 p_warning IN OUT NOCOPY VARCHAR2) IS
422 --
423
424
425 --
426 BEGIN
427 --
428
429 hr_dm_utility.message('ROUT','entry:hr_dm_business.rule_1_dest', 5);
430 hr_dm_utility.message('PARA','(r_migration_data - record)' ||
434 -- r_migration_data holds either S or D if in dest or destination
431 '(p_valid - ' || p_valid ||
432 ')(p_warning - ' || p_warning || ')', 10);
433
435 -- otherwise NULL
436 IF (NVL(r_migration_data.database_location,'?') <> 'D') THEN
437 p_valid := 'E';
438 hr_dm_utility.message('INFO','Business rule 1 broken - ' ||
439 r_migration_data.database_location ||
440 ' - No migration can proceed if the current' ||
441 ' database is not the dest database.', 15);
442
443 END IF;
444
445
446 hr_dm_utility.message('INFO','Validated rule 1', 215);
447 hr_dm_utility.message('SUMM','Validated rule 1', 220);
448 hr_dm_utility.message('ROUT','exit:hr_dm_business.rule_1_dest', 225);
449 hr_dm_utility.message('PARA','(p_valid - ' || p_valid ||
450 ')(p_warning - ' || p_warning || ')', 230);
451
452
453 -- error handling
454 EXCEPTION
455 WHEN OTHERS THEN
456 hr_dm_utility.error(SQLCODE,'hr_dm_business.rule_1_dest',
457 '(none)','R');
458 RAISE;
459
460 --
461 END rule_1_dest;
462 --
463
464 -- ------------------------- rule_2_dest ------------------------
465 -- Description: For a non-FW migration the business group must
466 -- exist in the destination database.
467 --
468 -- Note - this rule is similar to rule_2_source
469 --
470 --
471 -- Input Parameters
472 -- r_migration_data - record containing migration information
473 -- p_valid - current validity of migration
474 -- p_warning - cuurent warning of migration
475 --
476 --
477 -- Output Parameters
478 -- validity of migration - E = Error
479 -- V = Valid
480 -- W = Warning
481 --
482 -- ------------------------------------------------------------------------
483
484
485 --
486 PROCEDURE rule_2_dest(r_migration_data IN hr_dm_utility.r_migration_rec,
487 p_valid IN OUT NOCOPY VARCHAR2,
488 p_warning IN OUT NOCOPY VARCHAR2) IS
489 --
490
491 l_business_group_id NUMBER;
492
493 CURSOR csr_biz_grp IS
494 SELECT business_group_id
495 FROM per_business_groups
496 WHERE business_group_id = r_migration_data.business_group_id;
497
498 --
499 BEGIN
500 --
501
502 hr_dm_utility.message('ROUT','entry:hr_dm_business.rule_2_dest', 5);
503 hr_dm_utility.message('PARA','(r_migration_data - record)' ||
504 '(p_valid - ' || p_valid ||
505 ')(p_warning - ' || p_warning || ')', 10);
506
507 IF (r_migration_data.migration_type <> 'FW') THEN
508 OPEN csr_biz_grp;
509 FETCH csr_biz_grp INTO l_business_group_id;
510 CLOSE csr_biz_grp;
511 IF (r_migration_data.business_group_id <>
512 NVL(l_business_group_id, -1)) THEN
513 p_valid := 'E';
514 hr_dm_utility.message('INFO','Business rule 2 broken - ' ||
515 r_migration_data.database_location ||
516 ' - For a non-FW migration the business ' ||
517 'group must exist in the destination ' ||
518 'database.', 20);
519 END IF;
520 END IF;
521
522
523 hr_dm_utility.message('INFO','Validated rule 2', 215);
524 hr_dm_utility.message('SUMM','Validated rule 2', 220);
525 hr_dm_utility.message('ROUT','exit:hr_dm_business.rule_2_dest', 225);
526 hr_dm_utility.message('PARA','(p_valid - ' || p_valid ||
527 ')(p_warning - ' || p_warning || ')', 230);
528
529
530 -- error handling
531 EXCEPTION
532 WHEN OTHERS THEN
533 hr_dm_utility.error(SQLCODE,'hr_dm_business.rule_2_dest',
534 '(none)','R');
535 RAISE;
536
537 --
538 END rule_2_dest;
539 --
540
541
542
543 -- ------------------------- rule_6_dest ------------------------
544 -- Description: For a FW migration, the business group must not exist
545 -- on the destination database unless it has been created as part of
546 -- this upload process.
547 --
548 --
549 -- Input Parameters
550 -- r_migration_data - record containing migration information
551 -- p_valid - current validity of migration
552 -- p_warning - cuurent warning of migration
553 --
554 --
555 -- Output Parameters
556 -- validity of migration - E = Error
557 -- V = Valid
558 -- W = Warning
559 --
560 -- ------------------------------------------------------------------------
561
562
563 --
564 PROCEDURE rule_6_dest(r_migration_data IN hr_dm_utility.r_migration_rec,
565 p_valid IN OUT NOCOPY VARCHAR2,
566 p_warning IN OUT NOCOPY VARCHAR2) IS
567 --
568
569 l_business_group_created VARCHAR2(1);
570 l_business_group_id NUMBER;
571
572 CURSOR csr_mig_info IS
573 SELECT business_group_created
574 FROM hr_dm_migrations
575 WHERE (migration_id = r_migration_data.migration_id);
579 FROM per_business_groups
576
577 CURSOR csr_biz_grp IS
578 SELECT business_group_id
580 WHERE business_group_id = r_migration_data.business_group_id;
581
582 --
583 BEGIN
584 --
585
586 hr_dm_utility.message('ROUT','entry:hr_dm_business.rule_6_dest', 5);
587 hr_dm_utility.message('PARA','(r_migration_data - record)' ||
588 '(p_valid - ' || p_valid ||
589 ')(p_warning - ' || p_warning || ')', 10);
590
591 -- get the details of the current migration
592 OPEN csr_mig_info;
593 LOOP
594 FETCH csr_mig_info INTO l_business_group_created;
595 EXIT WHEN csr_mig_info%NOTFOUND;
596 END LOOP;
597 CLOSE csr_mig_info;
598
599 IF (r_migration_data.migration_type = 'FW') THEN
600 -- see if BG exists
601 l_business_group_id := NULL;
602 OPEN csr_biz_grp;
603 FETCH csr_biz_grp INTO l_business_group_id;
604 CLOSE csr_biz_grp;
605
606 -- if bg exists and we haven't created it, then raise error
607 IF (l_business_group_id IS NOT NULL)
608 AND (NVL(l_business_group_created,'N') = 'N') THEN
609 p_valid := 'E';
610 hr_dm_utility.message('INFO','Business rule 6 broken - ' ||
611 r_migration_data.database_location ||
612 ' - For a FW migration, the business group' ||
613 ' must not exist on the destination' ||
614 ' database.', 105);
615 END IF;
616
617 -- if bg doesn't exist but we think we have created it then raise error
618 IF (l_business_group_id IS NULL)
619 AND (NVL(l_business_group_created,'N') = 'Y') THEN
620 p_valid := 'E';
621 hr_dm_utility.message('INFO','Business rule 6 broken - ' ||
622 r_migration_data.database_location ||
623 ' - Business Group does not exist ' ||
624 'although it should have been ' ||
625 'created already.', 105);
626 END IF;
627
628 END IF;
629
630 hr_dm_utility.message('INFO','Validated rule 6', 215);
631 hr_dm_utility.message('SUMM','Validated rule 6', 220);
632 hr_dm_utility.message('ROUT','exit:hr_dm_business.rule_7_dest', 225);
633 hr_dm_utility.message('PARA','(p_valid - ' || p_valid ||
634 ')(p_warning - ' || p_warning || ')', 230);
635
636
637 -- error handling
638 EXCEPTION
639 WHEN OTHERS THEN
640 hr_dm_utility.error(SQLCODE,'hr_dm_business.rule_6_dest',
641 '(none)','R');
642 RAISE;
643
644 --
645 END rule_6_dest;
646 --
647
648
649 -- ------------------------- rule_7_dest ------------------------
650 -- Description: Only one migration can proceed at any one time. Only
651 -- one migration at any one time may have a status of S - Started,
652 -- NS - Not Started or E - Error, indicating that a migration is in
653 -- progress. Once a migration is C - Completed on the source or
654 -- destination, then a new migration can proceed. If a migration is
655 -- to be abandoned, then it will be given a status of A - Abandoned
656 -- to enable a new migration to be started.
657 --
658 -- Note - this rule is similar to rule_7_source
659 --
660 --
661 -- Input Parameters
662 -- r_migration_data - record containing migration information
663 -- p_valid - current validity of migration
664 -- p_warning - cuurent warning of migration
665 --
666 --
667 -- Output Parameters
668 -- validity of migration - E = Error
669 -- V = Valid
670 -- W = Warning
671 --
672 -- ------------------------------------------------------------------------
673
674
675 --
676 PROCEDURE rule_7_dest(r_migration_data IN hr_dm_utility.r_migration_rec,
677 p_valid IN OUT NOCOPY VARCHAR2,
678 p_warning IN OUT NOCOPY VARCHAR2) IS
679 --
680
681 l_mig_count NUMBER;
682
683 CURSOR csr_mig_count IS
684 SELECT count(*)
685 FROM hr_dm_migrations
686 WHERE status IN ('S', 'NS', 'E');
687
688 --
689 BEGIN
690 --
691
692 hr_dm_utility.message('ROUT','entry:hr_dm_business.rule_7_dest', 5);
693 hr_dm_utility.message('PARA','(r_migration_data - record)' ||
694 '(p_valid - ' || p_valid ||
695 ')(p_warning - ' || p_warning || ')', 10);
696
697 OPEN csr_mig_count;
698 FETCH csr_mig_count INTO l_mig_count;
699 CLOSE csr_mig_count;
700
701 IF (l_mig_count > 1) THEN
702 p_valid := 'E';
703 hr_dm_utility.message('INFO','Business rule 7 broken - ' ||
704 r_migration_data.database_location ||
705 ' - Only one migration can proceed at any one' ||
706 ' time. Only one migration at any one time may' ||
707 ' have a status of S - Started, NS - Not Started' ||
708 ' or E - Error, indicating that a migration is in' ||
709 ' progress. Once a migration is C - Completed on' ||
710 ' the source or destination, then a new migration' ||
714 END IF;
711 ' can proceed. If a migration is to be abandoned,' ||
712 ' then it will be given a statusof A - Abandoned' ||
713 ' to enable a new migration to be started.', 40);
715
716
717
718 hr_dm_utility.message('INFO','Validated rule 7', 215);
719 hr_dm_utility.message('SUMM','Validated rule 7', 220);
720 hr_dm_utility.message('ROUT','exit:hr_dm_business.rule_7_dest', 225);
721 hr_dm_utility.message('PARA','(p_valid - ' || p_valid ||
722 ')(p_warning - ' || p_warning || ')', 230);
723
724
725 -- error handling
726 EXCEPTION
727 WHEN OTHERS THEN
728 hr_dm_utility.error(SQLCODE,'hr_dm_business.rule_7_dest',
729 '(none)','R');
730 RAISE;
731
732 --
733 END rule_7_dest;
734 --
735
736 -- ------------------------- rule_8_dest ------------------------
737 -- Description: Only one migration can proceed at any one time. Only
738 -- one migration at any one time may have a status of S - Started,
739 -- NS - Not Started or E - Error, indicating that a migration is in
740 -- progress. Once a migration is C - Completed on the source or
741 -- destination, then a new migration can proceed. If a migration is
742 -- to be abandoned, then it will be given a status of A - Abandoned
743 -- to enable a new migration to be started.
744 --
745 -- Note - this rule is similar to rule_9_source
746 --
747 --
748 -- Input Parameters
749 -- r_migration_data - record containing migration information
750 -- p_valid - current validity of migration
751 -- p_warning - cuurent warning of migration
752 --
753 --
754 -- Output Parameters
755 -- validity of migration - E = Error
756 -- V = Valid
757 -- W = Warning
758 --
759 -- ------------------------------------------------------------------------
760
761
762 --
763 PROCEDURE rule_8_dest(r_migration_data IN hr_dm_utility.r_migration_rec,
764 p_valid IN OUT NOCOPY VARCHAR2,
765 p_warning IN OUT NOCOPY VARCHAR2) IS
766 --
767
768 l_status VARCHAR2(30);
769
770 CURSOR csr_mig_status IS
771 SELECT status
772 FROM hr_dm_migrations
773 WHERE (migration_id = r_migration_data.migration_id);
774
775 --
776 BEGIN
777 --
778
779 hr_dm_utility.message('ROUT','entry:hr_dm_business.rule_8_dest', 5);
780 hr_dm_utility.message('PARA','(r_migration_data - record)' ||
781 '(p_valid - ' || p_valid ||
782 ')(p_warning - ' || p_warning || ')', 10);
783
784 OPEN csr_mig_status;
785 FETCH csr_mig_status INTO l_status;
786 CLOSE csr_mig_status;
787
788 IF (l_status NOT IN ('NS', 'E')) THEN
789 p_valid := 'E';
790 hr_dm_utility.message('INFO','Business rule 8 broken - ' ||
791 r_migration_data.database_location ||
792 ' - A migration can only be rerun if it has a status' ||
793 ' of NS - Not Started or E - Error.', 45);
794 END IF;
795
796 hr_dm_utility.message('INFO','Validated rule 8', 215);
797 hr_dm_utility.message('SUMM','Validated rule 8', 220);
798 hr_dm_utility.message('ROUT','exit:hr_dm_business.rule_8_dest', 225);
799 hr_dm_utility.message('PARA','(p_valid - ' || p_valid ||
800 ')(p_warning - ' || p_warning || ')', 230);
801
802
803 -- error handling
804 EXCEPTION
805 WHEN OTHERS THEN
806 hr_dm_utility.error(SQLCODE,'hr_dm_business.rule_8_dest',
807 '(none)','R');
808 RAISE;
809
810 --
811 END rule_8_dest;
812 --
813
814 -- ------------------------- rule_9_dest ------------------------
815 -- Description: A migration can only be run / rerun if it has a
816 -- status of NS - Not Started or E - Error.
817
818 --
819 -- Note - this rule is similar to rule_10_source
820 --
821 --
822 -- Input Parameters
823 -- r_migration_data - record containing migration information
824 -- p_valid - current validity of migration
825 -- p_warning - cuurent warning of migration
826 --
827 --
828 -- Output Parameters
829 -- validity of migration - E = Error
830 -- V = Valid
831 -- W = Warning
832 --
833 -- ------------------------------------------------------------------------
834
835
836 --
837 PROCEDURE rule_9_dest(r_migration_data IN hr_dm_utility.r_migration_rec,
838 p_valid IN OUT NOCOPY VARCHAR2,
839 p_warning IN OUT NOCOPY VARCHAR2) IS
840 --
841
842 l_status VARCHAR2(30);
843
844 CURSOR csr_mig_status IS
845 SELECT status
846 FROM hr_dm_migrations
847 WHERE (migration_id = r_migration_data.migration_id);
848
849 --
850 BEGIN
851 --
852
853 hr_dm_utility.message('ROUT','entry:hr_dm_business.rule_9_dest', 5);
854 hr_dm_utility.message('PARA','(r_migration_data - record)' ||
855 '(p_valid - ' || p_valid ||
859 FETCH csr_mig_status INTO l_status;
856 ')(p_warning - ' || p_warning || ')', 9);
857
858 OPEN csr_mig_status;
860 CLOSE csr_mig_status;
861
862 IF (l_status NOT IN ('NS', 'E')) THEN
863 p_valid := 'E';
864 hr_dm_utility.message('INFO','Business rule 9 broken - ' ||
865 r_migration_data.database_location ||
866 ' - A migration can only be rerun if it has a status' ||
867 ' of NS - Not Started or E - Error.', 45);
868 END IF;
869
870 hr_dm_utility.message('INFO','Validated rule 9', 215);
871 hr_dm_utility.message('SUMM','Validated rule 9', 220);
872 hr_dm_utility.message('ROUT','exit:hr_dm_business.rule_9_dest', 225);
873 hr_dm_utility.message('PARA','(p_valid - ' || p_valid ||
874 ')(p_warning - ' || p_warning || ')', 230);
875
876
877 -- error handling
878 EXCEPTION
879 WHEN OTHERS THEN
880 hr_dm_utility.error(SQLCODE,'hr_dm_business.rule_9_dest',
881 '(none)','R');
882 RAISE;
883
884 --
885 END rule_9_dest;
886 --
887
888
889 /*---------------------------- PUBLIC ROUTINES ---------------------------*/
890
891 -- ------------------------- last_migration_date ------------------------
892 -- Description: For an A, P, SL, SF and SD migration, data is migrated
893 -- starting with the date of the latest previously finished migration
894 -- of the same type, business group, source and destination and finishing
895 -- with the start date and time of the current migration. Where a previous
896 -- migration of the same type, business group, source and destination has
897 -- not been performed or has not been finished, then the starting date is
898 -- that of the finished FW migration for the same business group, source
899 -- and destination.
900 --
901 -- For the first P migration, the last migration date is null.
902 --
903 -- For an SR migration, the last migration date is always null.
904 --
905 -- For a D migration, the last migration date is always null.
906 --
907 --
908 -- Input Parameters
909 -- r_migration_data - record containing migration information
910 --
911 --
912 -- Output Parameters
913 -- <none>
914 --
915 --
916 -- Return Value
917 -- last_migration_date - date of last sucessful migration
918 --
919 -- ------------------------------------------------------------------------
920
921
922 --
923 FUNCTION last_migration_date(
924 r_migration_data IN hr_dm_utility.r_migration_rec)
925 RETURN DATE IS
926 --
927
928 l_last_migration_date DATE;
929 l_last_fw_date DATE;
930 l_migration_type_test VARCHAR2(30);
931 l_business_group_id NUMBER;
932 l_last_date DATE;
933
934
935 CURSOR csr_mig_info IS
936 SELECT business_group_id
937 FROM hr_dm_migrations
938 WHERE (migration_id = r_migration_data.migration_id)
939 AND (UPPER(source_database_instance) =
940 UPPER(r_migration_data.source_database_instance))
941 AND (UPPER(destination_database_instance) =
942 UPPER(r_migration_data.destination_database_instance));
943
944 CURSOR csr_mig_date IS
945 SELECT MAX(effective_date)
946 FROM hr_dm_migrations
947 WHERE (status = 'F')
948 AND (business_group_id = l_business_group_id)
949 AND (migration_type = 'FW')
950 AND
951 (
952 (
953 UPPER(source_database_instance) =
954 UPPER(r_migration_data.source_database_instance)
955 AND
956 UPPER(destination_database_instance) =
957 UPPER(r_migration_data.destination_database_instance)
958 )
959 OR
960 (
961 UPPER(source_database_instance) =
962 UPPER(r_migration_data.destination_database_instance)
963 AND
964 UPPER(destination_database_instance) =
965 UPPER(r_migration_data.source_database_instance)
966 )
967 );
968
969 CURSOR csr_mig_date_othr IS
970 SELECT MAX(effective_date)
971 FROM hr_dm_migrations
972 WHERE (status = 'F')
973 AND (business_group_id = l_business_group_id)
974 AND (migration_type = l_migration_type_test)
975 AND (effective_date > NVL(l_last_fw_date,
976 hr_general.start_of_time))
977 AND
978 (
979 (
980 UPPER(source_database_instance) =
981 UPPER(r_migration_data.source_database_instance)
982 AND
983 UPPER(destination_database_instance) =
984 UPPER(r_migration_data.destination_database_instance)
985 )
986 OR
987 (
988 UPPER(source_database_instance) =
989 UPPER(r_migration_data.destination_database_instance)
990 AND
991 UPPER(destination_database_instance) =
992 UPPER(r_migration_data.source_database_instance)
993 )
994 );
995
996 --
997 BEGIN
998 --
999
1000 hr_dm_utility.message('ROUT','entry:hr_dm_business.last_migration_date', 5);
1004 -- data, so last migration date is null
1001 hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
1002
1003 -- if we are doing an FW / SR migration, then we want to migrate all
1005
1006 hr_dm_utility.message('INFO','src - ' ||
1007 r_migration_data.source_database_instance, 10);
1008 hr_dm_utility.message('INFO','dest - ' ||
1009 r_migration_data.destination_database_instance, 10);
1010
1011 IF r_migration_data.migration_type IN('FW','SR', 'D') THEN
1012 l_last_migration_date := NULL;
1013 ELSE
1014
1015 -- we have a non-FW/SR migration
1016
1017 -- get the details of the current migration
1018 OPEN csr_mig_info;
1019 LOOP
1020 FETCH csr_mig_info INTO l_business_group_id;
1021 EXIT WHEN csr_mig_info%NOTFOUND;
1022 END LOOP;
1023 CLOSE csr_mig_info;
1024
1025
1026 -- find the date of last finished FW migration
1027 -- assume last FW date of null (ie start of time)
1028 l_last_fw_date := NULL;
1029 OPEN csr_mig_date;
1030 FETCH csr_mig_date INTO l_last_date;
1031 IF (l_last_date > NVL(l_last_fw_date,
1032 hr_general.start_of_time)) THEN
1033 l_last_fw_date := l_last_date;
1034 END IF;
1035 CLOSE csr_mig_date;
1036
1037 hr_dm_utility.message('INFO','l_last_fw_date - ' || l_last_fw_date, 10);
1038 hr_dm_utility.message('INFO','l_last_date - ' || l_last_date, 10);
1039
1040 -- have we finished a migration for the same business group, source and
1041 -- destination databases for the same migration type since the last
1042 -- finished FW migration?
1043 --
1044 -- If we are doing the first P migration then the last_migration_date
1045 -- is null as we haven't yet migrated any participants.
1046 IF (r_migration_data.migration_type = 'P') THEN
1047 l_last_migration_date := NULL;
1048 ELSE
1049 l_last_migration_date := l_last_fw_date;
1050 END IF;
1051
1052 hr_dm_utility.message('INFO','l_last_migration_date - ' || l_last_migration_date, 10);
1053
1054 l_migration_type_test := r_migration_data.migration_type;
1055 OPEN csr_mig_date_othr;
1056 FETCH csr_mig_date_othr INTO l_last_date;
1057 IF (NVL(l_last_date, hr_general.start_of_time) >
1058 NVL(l_last_migration_date, hr_general.start_of_time)) THEN
1059 l_last_migration_date := l_last_date;
1060 END IF;
1061 CLOSE csr_mig_date_othr;
1062
1063 hr_dm_utility.message('INFO','l_last_migration_date - ' || l_last_migration_date, 10);
1064
1065
1066 END IF;
1067
1068
1069 hr_dm_utility.message('INFO','Found last migration date', 15);
1070 hr_dm_utility.message('SUMM','Found last migration date', 20);
1071 hr_dm_utility.message('ROUT','exit:hr_dm_business.last_migration_date', 25);
1072 hr_dm_utility.message('PARA','(l_last_migration_date - ' ||
1073 l_last_migration_date || ')', 30);
1074
1075
1076
1077 RETURN(l_last_migration_date);
1078
1079 -- error handling
1080 EXCEPTION
1081 WHEN OTHERS THEN
1082 hr_dm_utility.error(SQLCODE,'hr_dm_business.last_migration_date','(none)',
1083 'R');
1084 RAISE;
1085
1086
1087 --
1088 END last_migration_date;
1089 --
1090
1091
1092 -- ------------------------- validate_migration ------------------------
1093 -- Description: The details of the current migration are compared against
1094 -- the aplicable business rules, detailed in the code.
1095 --
1096 --
1097 -- Input Parameters
1098 -- r_migration_data - record containing migration information
1099 --
1100 --
1101 -- Output Parameters
1102 -- <none>
1103 --
1104 --
1105 -- Return Value
1106 -- validity of migration - E = Error
1107 -- V = Valid
1108 -- W = Warning
1109 --
1110 -- ------------------------------------------------------------------------
1111
1112
1113 --
1114 FUNCTION validate_migration(r_migration_data IN
1115 hr_dm_utility.r_migration_rec)
1116 RETURN VARCHAR2 IS
1117 --
1118
1119 l_valid VARCHAR2(1);
1120 l_warning VARCHAR2(1);
1121 l_source_instance VARCHAR2(30);
1122 l_destination_instance VARCHAR2(30);
1123
1124 CURSOR csr_mig_info IS
1125 SELECT source_database_instance, destination_database_instance
1126 FROM hr_dm_migrations
1127 WHERE (migration_id = r_migration_data.migration_id);
1128
1129
1130 --
1131 BEGIN
1132 --
1133
1134 hr_dm_utility.message('ROUT','entry:hr_dm_business.validate_migration', 5);
1135 hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
1136
1137
1138 -- assume that migration is valid - then test to see if there are problems
1139 l_valid := 'V';
1140 l_warning := 'N';
1141
1142 -- validate business rules, as per DM LLD
1143 -- get the details of the current migration
1144 OPEN csr_mig_info;
1145 LOOP
1146 FETCH csr_mig_info INTO l_source_instance,
1147 l_destination_instance;
1148 EXIT WHEN csr_mig_info%NOTFOUND;
1149 END LOOP;
1150 CLOSE csr_mig_info;
1151
1152 -- source business rules
1153 -- implemented in code
1154 -- and applicable to main controller
1155
1156 IF (r_migration_data.database_location = 'S') THEN
1157 rule_1_source(r_migration_data, l_valid, l_warning);
1158 rule_2_source(r_migration_data, l_valid, l_warning);
1159 rule_8_source(r_migration_data, l_valid, l_warning);
1160 rule_9_source(r_migration_data, l_valid, l_warning);
1161 rule_10_source(r_migration_data, l_valid, l_warning);
1162 END IF;
1163
1164 IF (r_migration_data.database_location = 'D') THEN
1165 rule_1_dest(r_migration_data, l_valid, l_warning);
1166 rule_2_dest(r_migration_data, l_valid, l_warning);
1167 rule_6_dest(r_migration_data, l_valid, l_warning);
1168 rule_7_dest(r_migration_data, l_valid, l_warning);
1169 rule_8_dest(r_migration_data, l_valid, l_warning);
1170 rule_9_dest(r_migration_data, l_valid, l_warning);
1171 END IF;
1172
1173
1174
1175 -- if valid but a warning has been found, show warning
1176 IF (l_valid = 'Y' AND l_warning = 'V') THEN
1177 l_valid := 'W';
1178 END IF;
1179
1183 hr_dm_utility.message('PARA','(l_valid - ' || l_valid || ')', 230);
1180 hr_dm_utility.message('INFO','Validated migration', 215);
1181 hr_dm_utility.message('SUMM','Validated migration', 220);
1182 hr_dm_utility.message('ROUT','exit:hr_dm_business.validate_migration', 225);
1184
1185
1186 -- overide
1187 -- uncomment to turn off business rule validation for testing purposes
1188 --l_valid := 'V';
1189
1190
1191 RETURN(l_valid);
1192
1193 -- error handling
1194 EXCEPTION
1195 WHEN OTHERS THEN
1196 hr_dm_utility.error(SQLCODE,'hr_dm_business.validate_migration',
1197 '(none)','R');
1198 RAISE;
1199
1200 --
1201 END validate_migration;
1202 --
1203
1204
1205
1206
1207 END hr_dm_business;
[Home] [Help]