1 PACKAGE BODY ben_dm_business AS
2 /* $Header: benfdmdbiz.pkb 120.0 2006/06/13 14:54:55 nkkrishn noship $ */
3
4 /*--------------------------- PRIVATE ROUTINES ---------------------------*/
5
6
7 -- ------------------------- rule_8_source ------------------------
8 -- Description: Only one migration can proceed at any one time. Only
9 -- one migration at any one time may have a status of S - Started,
10 -- NS - Not Started or E - Error, indicating that a migration is in
11 -- progress. Once a migration is C - Completed on the source or
12 -- destination, then a new migration can proceed. If a migration is
13 -- to be abandoned, then it will be given a status of A - Abandoned
14 -- to enable a new migration to be started.
15 --
16 --
17 -- Input Parameters
18 -- r_migration_data - record containing migration information
19 -- p_valid - current validity of migration
20 -- p_warning - cuurent warning of migration
21 --
22 --
23 -- Output Parameters
24 -- validity of migration - E = Error
25 -- V = Valid
26 -- W = Warning
27 --
28 -- ------------------------------------------------------------------------
29
30
31 --
32 PROCEDURE rule_8_source(r_migration_data IN ben_dm_utility.r_migration_rec,
33 p_valid IN OUT nocopy VARCHAR2,
34 p_warning IN OUT nocopy VARCHAR2) IS
35 --
36
37 l_mig_count NUMBER;
38
39 cursor c_req_stat is
40 select 'x'
41 from fnd_concurrent_requests req,
42 fnd_concurrent_programs prog
43 where prog.concurrent_program_name in ('BENDMSD','BENDMSU')
44 and req.concurrent_program_id = prog.concurrent_program_id
45 and req.request_id <> fnd_global.conc_request_id
46 and req.phase_code <> 'C';
47
48 --
49 l_dummy varchar2(30);
50
51 BEGIN
52 --
53
54 ben_dm_utility.message('ROUT','entry:ben_dm_business.rule_8_source', 5);
55 ben_dm_utility.message('PARA','(r_migration_data - record)' ||
56 '(p_valid - ' || p_valid ||
57 ')(p_warning - ' || p_warning || ')', 10);
58
59 ben_dm_utility.message('INFO','req id '||fnd_global.conc_request_id, 5);
60
61 open c_req_stat;
62 fetch c_req_stat into l_dummy;
63 if c_req_stat%found then
64 p_valid := 'E';
65 ben_dm_utility.message('INFO','Business rule 8 broken - ' ||
66 r_migration_data.database_location ||
67 ' - Only one migration can proceed at any one' ||
68 ' time. Only one migration at any one time may' ||
69 ' have a status of S - Started, NS - Not Started' ||
70 ' or E - Error, indicating that a migration is in' ||
71 ' progress. Once a migration is C - Completed on' ||
72 ' the source or destination, then a new migration' ||
73 ' can proceed. If a migration is to be abandoned,' ||
74 ' then it will be given a statusof A - Abandoned' ||
75 ' to enable a new migration to be started.', 40);
76
77 else
78
79 update ben_dm_migrations
80 set status =decode(status,'E','A','C')
81 where migration_id <> r_migration_data.migration_id
82 and status <> 'C';
83 commit;
84
85 end if;
86 close c_req_stat;
87
88
89 ben_dm_utility.message('INFO','Validated rule 8', 215);
90 ben_dm_utility.message('SUMM','Validated rule 8', 220);
91 ben_dm_utility.message('ROUT','exit:ben_dm_business.rule_8_source', 225);
92 ben_dm_utility.message('PARA','(p_valid - ' || p_valid ||
93 ')(p_warning - ' || p_warning || ')', 230);
94
95
96 -- error handling
97 EXCEPTION
98 WHEN OTHERS THEN
99 ben_dm_utility.error(SQLCODE,'ben_dm_business.rule_8_source',
100 '(none)','R');
101 RAISE;
102
103 --
104 END rule_8_source;
105 --
106
107 -- ------------------------- rule_9_source ------------------------
108 -- Description: A migration can only be run / rerun if it has a
109 -- status of NS - Not Started or E - Error.
110 --
111 --
112 -- Input Parameters
113 -- r_migration_data - record containing migration information
114 -- p_valid - current validity of migration
115 -- p_warning - cuurent warning of migration
116 --
117 --
118 -- Output Parameters
119 -- validity of migration - E = Error
120 -- V = Valid
121 -- W = Warning
122 --
123 -- ------------------------------------------------------------------------
124
125
126 --
127 PROCEDURE rule_9_source(r_migration_data IN ben_dm_utility.r_migration_rec,
128 p_valid IN OUT nocopy VARCHAR2,
129 p_warning IN OUT nocopy VARCHAR2) IS
130 --
131
132 l_status VARCHAR2(30);
133
134 CURSOR csr_mig_status IS
135 SELECT status
136 FROM ben_dm_migrations
137 WHERE (migration_id = r_migration_data.migration_id);
138
139 --
140 BEGIN
141 --
142
143 ben_dm_utility.message('ROUT','entry:ben_dm_business.rule_9_source', 5);
144 ben_dm_utility.message('PARA','(r_migration_data - record)' ||
145 '(p_valid - ' || p_valid ||
146 ')(p_warning - ' || p_warning || ')', 10);
147
148 OPEN csr_mig_status;
149 FETCH csr_mig_status INTO l_status;
150 CLOSE csr_mig_status;
151
152 IF (l_status NOT IN ('NS', 'E')) THEN
153 p_valid := 'E';
154 ben_dm_utility.message('INFO','Business rule 9 broken - ' ||
155 r_migration_data.database_location ||
156 ' - A migration can only be rerun if it has a status' ||
157 ' of NS - Not Started or E - Error.', 45);
158 END IF;
159
160
161 ben_dm_utility.message('INFO','Validated rule 9', 215);
162 ben_dm_utility.message('SUMM','Validated rule 9', 220);
163 ben_dm_utility.message('ROUT','exit:ben_dm_business.rule_9_source', 225);
164 ben_dm_utility.message('PARA','(p_valid - ' || p_valid ||
165 ')(p_warning - ' || p_warning || ')', 230);
166
167
168 -- error handling
169 EXCEPTION
170 WHEN OTHERS THEN
171 ben_dm_utility.error(SQLCODE,'ben_dm_business.rule_9_source',
172 '(none)','R');
173 RAISE;
174
175 --
176 END rule_9_source;
177 --
178
179 -- ------------------------- rule_10_source ------------------------
180 -- Description: A migration can only be rerun if all the slaves and
181 -- sub-slaves launched by the previous run have either completed or
182 -- errored.
183
184 --
185 --
186 -- Input Parameters
187 -- r_migration_data - record containing migration information
188 -- p_valid - current validity of migration
189 -- p_warning - cuurent warning of migration
190 --
191 --
192 -- Output Parameters
193 -- validity of migration - E = Error
194 -- V = Valid
195 -- W = Warning
196 --
197 -- ------------------------------------------------------------------------
198
199
200 --
201 PROCEDURE rule_10_source(r_migration_data IN ben_dm_utility.r_migration_rec,
202 p_valid IN OUT nocopy VARCHAR2,
203 p_warning IN OUT nocopy VARCHAR2) IS
204 --
205
206 l_request_id NUMBER;
207 l_call_status BOOLEAN;
208 l_phase VARCHAR2(30);
209 l_status VARCHAR2(30);
210 l_dev_phase VARCHAR2(30);
211 l_dev_status VARCHAR2(30);
212 l_message VARCHAR2(240);
213
214 CURSOR csr_requests IS
215 SELECT request_id
216 FROM ben_dm_migration_requests
217 WHERE (migration_id = r_migration_data.migration_id)
218 AND (master_slave <> 'M');
219
220 --
221 BEGIN
222 --
223
224 ben_dm_utility.message('ROUT','entry:ben_dm_business.rule_10_source', 5);
225 ben_dm_utility.message('PARA','(r_migration_data - record)' ||
226 '(p_valid - ' || p_valid ||
227 ')(p_warning - ' || p_warning || ')', 10);
228
229 -- check if a slave has errored
230 OPEN csr_requests;
231 LOOP
232 FETCH csr_requests INTO l_request_id;
233 EXIT WHEN csr_requests%NOTFOUND;
234
235 l_call_status := fnd_concurrent.get_request_status(l_request_id, '', '',
236 l_phase, l_status, l_dev_phase,
237 l_dev_status, l_message);
238 -- make sure that each slave is complete
239 -- this includes any of normal (ie finished sucessfully), error, warning,
240 -- cancelled or terminated.
241 IF ( NOT(l_dev_phase = 'COMPLETE')) THEN
242 p_valid := 'E';
243 ben_dm_utility.message('INFO','Business rule 10 broken - ' ||
244 r_migration_data.database_location ||
245 ' - A migration can only be rerun if all the' ||
246 ' slaves and sub-slaves launched by the' ||
247 ' previous run have either completed or' ||
248 ' errored.', 50);
249 END IF;
250
251 END LOOP;
252 CLOSE csr_requests;
253
254
255 ben_dm_utility.message('INFO','Validated rule 10', 215);
256 ben_dm_utility.message('SUMM','Validated rule 10', 220);
257 ben_dm_utility.message('ROUT','exit:ben_dm_business.rule_10_source', 225);
258 ben_dm_utility.message('PARA','(p_valid - ' || p_valid ||
259 ')(p_warning - ' || p_warning || ')', 230);
260
261
262 -- error handling
263 EXCEPTION
264 WHEN OTHERS THEN
265 ben_dm_utility.error(SQLCODE,'ben_dm_business.rule_10_source',
266 '(none)','R');
267 RAISE;
268
269 --
270 END rule_10_source;
271 --
272 -- ------------------------- rule_7_dest ------------------------
273 -- Description: Only one migration can proceed at any one time. Only
274 -- one migration at any one time may have a status of S - Started,
275 -- NS - Not Started or E - Error, indicating that a migration is in
276 -- progress. Once a migration is C - Completed on the source or
277 -- destination, then a new migration can proceed. If a migration is
278 -- to be abandoned, then it will be given a status of A - Abandoned
279 -- to enable a new migration to be started.
280 --
281 -- Note - this rule is similar to rule_7_source
282 --
283 --
284 -- Input Parameters
285 -- r_migration_data - record containing migration information
286 -- p_valid - current validity of migration
287 -- p_warning - cuurent warning of migration
288 --
289 --
290 -- Output Parameters
291 -- validity of migration - E = Error
292 -- V = Valid
293 -- W = Warning
294 --
295 -- ------------------------------------------------------------------------
296
297
298 --
299 PROCEDURE rule_7_dest(r_migration_data IN ben_dm_utility.r_migration_rec,
300 p_valid IN OUT nocopy VARCHAR2,
301 p_warning IN OUT nocopy VARCHAR2) IS
302 --
303
304 l_mig_count NUMBER;
305
306 cursor c_req_stat is
307 select 'x'
308 from fnd_concurrent_requests req,
309 fnd_concurrent_programs prog
310 where prog.concurrent_program_name in ('BENDMSD','BENDMSU')
311 and req.concurrent_program_id = prog.concurrent_program_id
312 and req.request_id <> fnd_global.conc_request_id
313 and req.phase_code <> 'C';
314
315 --
316 l_dummy varchar2(30);
317
318 --
319 BEGIN
320 --
321
322 ben_dm_utility.message('ROUT','entry:ben_dm_business.rule_7_dest', 5);
323 ben_dm_utility.message('PARA','(r_migration_data - record)' ||
324 '(p_valid - ' || p_valid ||
325 ')(p_warning - ' || p_warning || ')', 10);
326
327 ben_dm_utility.message('INFO','req id '||fnd_global.conc_request_id, 5);
328
329 open c_req_stat;
330 fetch c_req_stat into l_dummy;
331 if c_req_stat%found then
332 p_valid := 'E';
333 ben_dm_utility.message('INFO','Business rule 7 broken - ' ||
334 r_migration_data.database_location ||
335 ' - Only one migration can proceed at any one' ||
336 ' time. Only one migration at any one time may' ||
337 ' have a status of S - Started, NS - Not Started' ||
338 ' or E - Error, indicating that a migration is in' ||
339 ' progress. Once a migration is C - Completed on' ||
340 ' the source or destination, then a new migration' ||
341 ' can proceed. If a migration is to be abandoned,' ||
342 ' then it will be given a statusof A - Abandoned' ||
343 ' to enable a new migration to be started.', 40);
344
345 else
346
347 update ben_dm_migrations
348 set status =decode(status,'E','A','C')
349 where migration_id <> r_migration_data.migration_id
350 and status <> 'C';
351 commit;
352
353 end if;
354 close c_req_stat;
355
356
357 ben_dm_utility.message('INFO','Validated rule 7', 215);
358 ben_dm_utility.message('SUMM','Validated rule 7', 220);
359 ben_dm_utility.message('ROUT','exit:ben_dm_business.rule_7_dest', 225);
360 ben_dm_utility.message('PARA','(p_valid - ' || p_valid ||
361 ')(p_warning - ' || p_warning || ')', 230);
362
363
364 -- error handling
365 EXCEPTION
366 WHEN OTHERS THEN
367 ben_dm_utility.error(SQLCODE,'ben_dm_business.rule_7_dest',
368 '(none)','R');
369 RAISE;
370
371 --
372 END rule_7_dest;
373 --
374
375 -- ------------------------- rule_8_dest ------------------------
376 -- Description: Only one migration can proceed at any one time. Only
377 -- one migration at any one time may have a status of S - Started,
378 -- NS - Not Started or E - Error, indicating that a migration is in
379 -- progress. Once a migration is C - Completed on the source or
380 -- destination, then a new migration can proceed. If a migration is
381 -- to be abandoned, then it will be given a status of A - Abandoned
382 -- to enable a new migration to be started.
383 --
384 -- Note - this rule is similar to rule_9_source
385 --
386 --
387 -- Input Parameters
388 -- r_migration_data - record containing migration information
389 -- p_valid - current validity of migration
390 -- p_warning - cuurent warning of migration
391 --
392 --
393 -- Output Parameters
394 -- validity of migration - E = Error
395 -- V = Valid
396 -- W = Warning
397 --
398 -- ------------------------------------------------------------------------
399
400
401 --
402 PROCEDURE rule_8_dest(r_migration_data IN ben_dm_utility.r_migration_rec,
403 p_valid IN OUT nocopy VARCHAR2,
404 p_warning IN OUT nocopy VARCHAR2) IS
405 --
406
407 l_status VARCHAR2(30);
408
409 CURSOR csr_mig_status IS
410 SELECT status
411 FROM ben_dm_migrations
412 WHERE (migration_id = r_migration_data.migration_id);
413
414 --
415 BEGIN
416 --
417
418 ben_dm_utility.message('ROUT','entry:ben_dm_business.rule_8_dest', 5);
419 ben_dm_utility.message('PARA','(r_migration_data - record)' ||
420 '(p_valid - ' || p_valid ||
421 ')(p_warning - ' || p_warning || ')', 10);
422
423 OPEN csr_mig_status;
424 FETCH csr_mig_status INTO l_status;
428 p_valid := 'E';
425 CLOSE csr_mig_status;
426
427 IF (l_status NOT IN ('NS', 'E')) THEN
429 ben_dm_utility.message('INFO','Business rule 8 broken - ' ||
430 r_migration_data.database_location ||
431 ' - A migration can only be rerun if it has a status' ||
432 ' of NS - Not Started or E - Error.', 45);
433 END IF;
434
435 ben_dm_utility.message('INFO','Validated rule 8', 215);
436 ben_dm_utility.message('SUMM','Validated rule 8', 220);
437 ben_dm_utility.message('ROUT','exit:ben_dm_business.rule_8_dest', 225);
438 ben_dm_utility.message('PARA','(p_valid - ' || p_valid ||
439 ')(p_warning - ' || p_warning || ')', 230);
440
441
442 -- error handling
443 EXCEPTION
444 WHEN OTHERS THEN
445 ben_dm_utility.error(SQLCODE,'ben_dm_business.rule_8_dest',
446 '(none)','R');
447 RAISE;
448
449 --
450 END rule_8_dest;
451 --
452
453 -- ------------------------- rule_9_dest ------------------------
454 -- Description: A migration can only be run / rerun if it has a
455 -- status of NS - Not Started or E - Error.
456
457 --
458 -- Note - this rule is similar to rule_10_source
459 --
460 --
461 -- Input Parameters
462 -- r_migration_data - record containing migration information
463 -- p_valid - current validity of migration
464 -- p_warning - cuurent warning of migration
465 --
466 --
467 -- Output Parameters
468 -- validity of migration - E = Error
469 -- V = Valid
470 -- W = Warning
471 --
472 -- ------------------------------------------------------------------------
473
474
475 --
476 PROCEDURE rule_9_dest(r_migration_data IN ben_dm_utility.r_migration_rec,
477 p_valid IN OUT nocopy VARCHAR2,
478 p_warning IN OUT nocopy VARCHAR2) IS
479 --
480
481 l_status VARCHAR2(30);
482
483 CURSOR csr_mig_status IS
484 SELECT status
485 FROM ben_dm_migrations
486 WHERE (migration_id = r_migration_data.migration_id);
487
488 --
489 BEGIN
490 --
491
492 ben_dm_utility.message('ROUT','entry:ben_dm_business.rule_9_dest', 5);
493 ben_dm_utility.message('PARA','(r_migration_data - record)' ||
494 '(p_valid - ' || p_valid ||
495 ')(p_warning - ' || p_warning || ')', 9);
496
497 OPEN csr_mig_status;
498 FETCH csr_mig_status INTO l_status;
499 CLOSE csr_mig_status;
500
501 IF (l_status NOT IN ('NS', 'E')) THEN
502 p_valid := 'E';
503 ben_dm_utility.message('INFO','Business rule 9 broken - ' ||
504 r_migration_data.database_location ||
505 ' - A migration can only be rerun if it has a status' ||
506 ' of NS - Not Started or E - Error.', 45);
507 END IF;
508
509 ben_dm_utility.message('INFO','Validated rule 9', 215);
510 ben_dm_utility.message('SUMM','Validated rule 9', 220);
511 ben_dm_utility.message('ROUT','exit:ben_dm_business.rule_9_dest', 225);
512 ben_dm_utility.message('PARA','(p_valid - ' || p_valid ||
513 ')(p_warning - ' || p_warning || ')', 230);
514
515
516 -- error handling
517 EXCEPTION
518 WHEN OTHERS THEN
519 ben_dm_utility.error(SQLCODE,'ben_dm_business.rule_9_dest',
520 '(none)','R');
521 RAISE;
522
523 --
524 END rule_9_dest;
525 --
526
527
528 /*---------------------------- PUBLIC ROUTINES ---------------------------*/
529
530 -- ------------------------- last_migration_date ------------------------
531 -- Description: For an a migration, data is migrated
532 -- starting with the date of the latest previously finished migration and
533 -- finishing with the start date and time of the current migration. Where a previous
534 -- migration has
535 -- not been performed or has not been finished, then the starting date is
536 -- that of the finished migration.
537 --
538 -- Input Parameters
539 -- r_migration_data - record containing migration information
540 --
541 --
542 -- Output Parameters
543 -- <none>
544 --
545 --
546 -- Return Value
547 -- last_migration_date - date of last sucessful migration
548 --
549 -- ------------------------------------------------------------------------
550
551
552 --
553 FUNCTION last_migration_date(
554 r_migration_data IN ben_dm_utility.r_migration_rec)
555 RETURN DATE IS
556 --
557
558 l_last_migration_date DATE;
559 l_last_mig_date DATE;
560 l_migration_type_test VARCHAR2(30);
561 l_business_group_id NUMBER;
562 l_last_date DATE;
563
564
565
566 CURSOR csr_mig_date IS
567 SELECT MAX(effective_date)
568 FROM ben_dm_migrations
569 WHERE (status = 'C');
570 --
571 BEGIN
572 --
573
574 ben_dm_utility.message('ROUT','entry:ben_dm_business.last_migration_date', 5);
575 ben_dm_utility.message('PARA','(r_migration_data - record)', 10);
576
577 l_last_migration_date := NULL;
578
579 -- find the date of last Completed 'C' migration
580 -- assume last FW date of null (ie start of time)
581 l_last_mig_date := NULL;
582 OPEN csr_mig_date;
583 FETCH csr_mig_date INTO l_last_date;
584 IF (l_last_date > NVL(l_last_mig_date,
585 hr_general.start_of_time)) THEN
586 l_last_mig_date := l_last_date;
587 l_last_migration_date := l_last_date;
588 ELSE
592
589 l_last_migration_date := hr_general.start_of_time;
590 END IF;
591 CLOSE csr_mig_date;
593
594 ben_dm_utility.message('INFO','l_last_mig_date - ' || l_last_mig_date, 10);
595 ben_dm_utility.message('INFO','l_last_date - ' || l_last_date, 10);
596 ben_dm_utility.message('INFO','Found last migration date', 15);
597 ben_dm_utility.message('SUMM','Found last migration date', 20);
598 ben_dm_utility.message('ROUT','exit:ben_dm_business.last_migration_date', 25);
599 ben_dm_utility.message('PARA','(l_last_migration_date - ' ||
600 l_last_migration_date || ')', 30);
601
602
603
604 RETURN(l_last_migration_date);
605
606 -- error handling
607 EXCEPTION
608 WHEN OTHERS THEN
609 ben_dm_utility.error(SQLCODE,'ben_dm_business.last_migration_date','(none)',
610 'R');
611 RAISE;
612
613
614 --
615 END last_migration_date;
616 --
617
618
619 -- ------------------------- validate_migration ------------------------
620 -- Description: The details of the current migration are compared against
621 -- the aplicable business rules, detailed in the code.
622 --
623 --
624 -- Input Parameters
625 -- r_migration_data - record containing migration information
626 --
627 --
628 -- Output Parameters
629 -- <none>
630 --
631 --
632 -- Return Value
633 -- validity of migration - E = Error
634 -- V = Valid
635 -- W = Warning
636 --
637 -- ------------------------------------------------------------------------
638
639
640 --
641 FUNCTION validate_migration(r_migration_data IN
642 ben_dm_utility.r_migration_rec)
643 RETURN VARCHAR2 IS
644 --
645
646 l_valid VARCHAR2(1);
647 l_warning VARCHAR2(1);
648 l_source_instance VARCHAR2(30);
649 l_destination_instance VARCHAR2(30);
650
651 --
652 BEGIN
653 --
654
655 ben_dm_utility.message('ROUT','entry:ben_dm_business.validate_migration', 5);
656 ben_dm_utility.message('PARA','(r_migration_data - record)', 10);
657
658
659 -- assume that migration is valid - then test to see if there are problems
660 l_valid := 'V';
661 l_warning := 'N';
662
663 -- validate business rules, as per DM LLD
664 -- source business rules
665 -- implemented in code
666 -- and applicable to main controller
667
668 IF (r_migration_data.database_location = 'S') THEN
669 rule_8_source(r_migration_data, l_valid, l_warning);
670 rule_9_source(r_migration_data, l_valid, l_warning);
671 rule_10_source(r_migration_data, l_valid, l_warning);
672 END IF;
673
674 IF (r_migration_data.database_location = 'D') THEN
675 rule_7_dest(r_migration_data, l_valid, l_warning);
676 rule_8_dest(r_migration_data, l_valid, l_warning);
677 rule_9_dest(r_migration_data, l_valid, l_warning);
678 END IF;
679
680
681
682 -- if valid but a warning has been found, show warning
683 IF (l_valid = 'Y' AND l_warning = 'V') THEN
684 l_valid := 'W';
685 END IF;
686
687 ben_dm_utility.message('INFO','Validated migration', 215);
688 ben_dm_utility.message('SUMM','Validated migration', 220);
689 ben_dm_utility.message('ROUT','exit:ben_dm_business.validate_migration', 225);
690 ben_dm_utility.message('PARA','(l_valid - ' || l_valid || ')', 230);
691
692
693 -- overide
694 -- uncomment to turn off business rule validation for testing purposes
695 --l_valid := 'V';
696
697
698 RETURN(l_valid);
699
700 -- error handling
701 EXCEPTION
702 WHEN OTHERS THEN
703 ben_dm_utility.error(SQLCODE,'ben_dm_business.validate_migration',
704 '(none)','R');
705 RAISE;
706
707 --
708 END validate_migration;
709 --
710
711
712
713
714 END ben_dm_business;
[Home] [Help]