[Home] [Help]
PACKAGE BODY: APPS.HR_DM_INIT
Source
1 PACKAGE BODY hr_dm_init AS
2 /* $Header: perdmini.pkb 115.19 2004/03/24 08:29:10 mmudigon ship $ */
3
4 -- ------------------------- check_custom_flex ------------------------
5 -- Description: Check if custom flex code use is enabled. This is determined
6 -- from the table pay_action_parameters, using a parameter name of
7 -- HR_DM_CUSTOM_AOL_CODE.
8 --
9 -- Input Parameters
10 -- <none>
11 --
12 --
13 -- Output Parameters
14 -- parameter value if one exists, otherwise null
15 --
16 --
17 -- ------------------------------------------------------------------------
18
19 --
20 FUNCTION check_custom_flex RETURN VARCHAR2 IS
21 --
22 CURSOR csr_value IS
23 SELECT UPPER(parameter_value)
24 FROM pay_action_parameters
25 WHERE parameter_name = 'HR_DM_CUSTOM_AOL_CODE';
26
27 l_retval VARCHAR2(80) := NULL;
28
29 --
30 BEGIN
31 --
32
33 hr_dm_utility.message('ROUT','entry:hr_dm_init.check_custom_flex', 5);
34 hr_dm_utility.message('PARA','(none)', 10);
35
36 -- read values from pay_action_parameters
37
38 OPEN csr_value;
39 LOOP
40 FETCH csr_value INTO l_retval;
41 EXIT WHEN csr_value%NOTFOUND;
42 END LOOP;
43 CLOSE csr_value;
44
45 -- looking for a Y, otherwise return null
46 IF l_retval <> 'Y' THEN
47 l_retval := NULL;
48 END IF;
49
50
51 hr_dm_utility.message('INFO','Checked custom flex setting', 15);
52 hr_dm_utility.message('SUMM','Checked custom flex setting', 20);
53 hr_dm_utility.message('ROUT','exit:hr_dm_init.check_custom_flex', 25);
54 hr_dm_utility.message('PARA','(l_retval - ' || l_retval || ')', 30);
55
56
57 RETURN(l_retval);
58
59 -- error handling
60 EXCEPTION
61 WHEN OTHERS THEN
62 hr_dm_utility.error(SQLCODE,'hr_dm_init.check_custom_flex','(none)','R');
63 RAISE;
64
65 --
66 END check_custom_flex;
67 --
68
69
70
71 -- ------------------------- populate_pi_table_i ------------------------
72 -- Description: The phase items for the initialization phase are seeded
73 -- into the hr_dm_phase_items. (currently none required)
74 --
75 --
76 -- Input Parameters
77 -- r_migration_data - record containing migration information
78 --
79 --
80 -- Output Parameters
81 -- <none>
82 --
83 --
84 -- ------------------------------------------------------------------------
85
86
87 --
88 PROCEDURE populate_pi_table_i(r_migration_data IN
89 hr_dm_utility.r_migration_rec) IS
90 --
91
92 --
93 BEGIN
94 --
95
96 hr_dm_utility.message('ROUT','entry:hr_dm_init.populate_pi_table_i', 5);
97 hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
98
99 --
100 -- phase specific code to be inserted here
101 --
102 -- no code required
103 --
104
105 hr_dm_utility.message('INFO','Populate Phase Items table - I phase', 15);
106 hr_dm_utility.message('SUMM','Populate Phase Items table - I phase', 20);
107 hr_dm_utility.message('ROUT','exit:hr_dm_init.populate_pi_table_i', 25);
108 hr_dm_utility.message('PARA','(none)', 30);
109
110 -- error handling
111 EXCEPTION
112 WHEN OTHERS THEN
113 hr_dm_utility.error(SQLCODE,'hr_dm_init.populate_pi_table_i','(none)',
114 'R');
115 RAISE;
116
117 --
118 END populate_pi_table_i;
119 --
120
121 -- ------------------------- populate_pi_table_g ------------------------
122 -- Description: The phase items for the generator phase are seeded
123 -- into the hr_dm_phase_items.
124 --
125 --
126 -- Input Parameters
127 -- r_migration_data - record containing migration information
128 --
129 --
130 -- Output Parameters
131 -- <none>
132 --
133 --
134 -- ------------------------------------------------------------------------
135
136 --
137 PROCEDURE populate_pi_table_g(r_migration_data IN
138 hr_dm_utility.r_migration_rec) IS
139 --
140
141 l_phase_id NUMBER;
142 l_loader_name VARCHAR2(30);
143 l_group_id NUMBER;
144 l_table_name VARCHAR2(30);
145 l_generator_version VARCHAR2(2000);
146 l_status varchar2(50);
147 l_industry varchar2(50);
148 l_per_owner varchar2(30);
149 l_ben_owner varchar2(30);
150 l_pay_owner varchar2(30);
151 l_ff_owner varchar2(30);
152 l_fnd_owner varchar2(30);
153 l_apps_owner varchar2(30);
154
155 l_ret1 boolean := FND_INSTALLATION.GET_APP_INFO ('PAY', l_status,
156 l_industry, l_pay_owner);
157 l_ret2 boolean := FND_INSTALLATION.GET_APP_INFO ('BEN', l_status,
158 l_industry, l_ben_owner);
159 l_ret3 boolean := FND_INSTALLATION.GET_APP_INFO ('FF', l_status,
160 l_industry, l_ff_owner);
161 l_ret4 boolean := FND_INSTALLATION.GET_APP_INFO ('FND', l_status,
162 l_industry, l_fnd_owner);
163 l_ret5 boolean := FND_INSTALLATION.GET_APP_INFO ('PER', l_status,
164 l_industry, l_per_owner);
165 l_ret6 boolean := FND_INSTALLATION.GET_APP_INFO ('APPS', l_status,
166 l_industry, l_apps_owner);
167
168 -- select tables for generating where
169 -- 1. generator not yet run (last_generated_date is null) OR
170 -- 2. table updated since last run (last_ddl_time >= last_generated_date) OR
171 -- 3. generator updated (generator_version(table) <>
172 -- generator version(generator))
173 -- 4. row in hr_dm_tables has been updated since the last generation
174 -- (last_update_date > last_generated_date)
175
176 CURSOR csr_select_pi IS
177 SELECT DISTINCT tbl.loader_name, tbg.group_id, tbl.table_name
178 FROM hr_dm_tables tbl, hr_dm_table_groupings tbg,
179 all_objects obj, hr_dm_application_groups app,
180 hr_dm_migrations mig
181 WHERE (tbl.table_id = tbg.table_id)
182 AND (app.group_id = tbg.group_id)
183 AND (mig.application_id = app.application_id)
184 AND (mig.migration_id = r_migration_data.migration_id)
185 AND (obj.object_name = tbl.table_name)
186 AND (obj.object_type = 'TABLE')
187 AND obj.owner in
188 (l_apps_owner,
189 l_fnd_owner,
190 l_ff_owner,
191 l_ben_owner,
192 l_pay_owner,
193 l_per_owner)
194 AND (
195 (obj.last_ddl_time >= NVL(tbl.last_generated_date,
196 obj.last_ddl_time))
197 OR (l_generator_version <> NVL(tbl.generator_version,
198 'none'))
199 OR (tbl.last_update_date > NVL(tbl.last_generated_date,
200 tbl.last_update_date))
201 )
202 UNION
203 SELECT DISTINCT tbl.loader_name, tbg.group_id, tbl.table_name
204 FROM hr_dm_tables tbl, hr_dm_table_groupings tbg,
205 all_objects obj, hr_dm_application_groups app,
206 hr_dm_migrations mig
207 WHERE (tbl.table_id = tbg.table_id)
208 AND (app.group_id = tbg.group_id)
209 AND (mig.application_id = app.application_id)
210 AND (mig.migration_id = r_migration_data.migration_id)
211 AND (obj.object_name = tbl.upload_table_name)
212 AND (obj.object_type = 'TABLE')
213 AND obj.owner in
214 (l_apps_owner,
215 l_fnd_owner,
216 l_ff_owner,
217 l_ben_owner,
218 l_pay_owner,
219 l_per_owner)
220 AND (
221 (obj.last_ddl_time >= NVL(tbl.last_generated_date,
222 obj.last_ddl_time))
223 OR (l_generator_version <> NVL(tbl.generator_version,
224 'none'))
225 OR (tbl.last_update_date > NVL(tbl.last_generated_date,
226 tbl.last_update_date))
227 )
228 UNION
229 SELECT DISTINCT tbl.loader_name, tbg.group_id, tbl.table_name
230 FROM hr_dm_tables tbl, hr_dm_table_groupings tbg,
231 hr_dm_application_groups app,
232 hr_dm_migrations mig
233 WHERE (tbl.table_id = tbg.table_id)
234 AND (app.group_id = tbg.group_id)
235 AND (mig.application_id = app.application_id)
236 AND (mig.migration_id = r_migration_data.migration_id)
237 AND (tbl.table_name like 'HR_DMVP%')
238 AND (tbl.upload_table_name IS NULL);
239
240
241
242 --
243 BEGIN
244 --
245
246 hr_dm_utility.message('ROUT','entry:hr_dm_init.populate_pi_table_g', 5);
247 hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
248
249 -- get phase id
250 l_phase_id := hr_dm_utility.get_phase_id('G',
251 r_migration_data.migration_id);
252
253 -- read generator version
254 hr_dm_library.get_generator_version(p_generator_version =>
255 l_generator_version);
256
257 -- we always want to generate for ff_formulas_f
258 UPDATE hr_dm_tables
259 SET last_generated_date = NULL
260 WHERE table_name = 'FF_FORMULAS_F'
261 OR table_name LIKE 'HR_DMV%';
262 COMMIT;
263
264
265 OPEN csr_select_pi;
266 LOOP
267 FETCH csr_select_pi INTO l_loader_name, l_group_id, l_table_name;
268 EXIT WHEN csr_select_pi%NOTFOUND;
269
270 INSERT INTO hr_dm_phase_items (PHASE_ITEM_ID,
271 PHASE_ID,
272 LOADER_NAME,
273 BATCH_ID,
274 GROUP_ID,
275 TABLE_NAME,
276 STATUS,
277 START_TIME,
278 END_TIME,
279 CREATED_BY,
280 CREATION_DATE,
281 LAST_UPDATED_BY,
282 LAST_UPDATE_DATE,
283 LAST_UPDATE_LOGIN)
284 SELECT hr_dm_phase_items_s.nextval,
285 l_phase_id,
286 l_loader_name,
287 NULL,
288 l_group_id,
289 l_table_name,
290 'NS',
291 NULL,
292 NULL,
293 1,
294 SYSDATE,
295 1,
296 SYSDATE,
297 NULL
298 FROM dual
299 WHERE NOT EXISTS
300 (SELECT NULL FROM hr_dm_phase_items
301 WHERE ((phase_id = l_phase_id)
302 AND (table_name = l_table_name)));
303
304 COMMIT;
305
306 hr_dm_utility.message('INFO','Seeding ' || l_table_name, 11);
307
308 END LOOP;
309 CLOSE csr_select_pi;
310
311
312 hr_dm_utility.message('INFO','Populate Phase Items table - G phase', 15);
313 hr_dm_utility.message('SUMM','Populate Phase Items table - G phase', 20);
314 hr_dm_utility.message('ROUT','exit:hr_dm_init.populate_pi_table_g', 25);
315 hr_dm_utility.message('PARA','(none)', 30);
316
317 -- error handling
318 EXCEPTION
319 WHEN OTHERS THEN
320 hr_dm_utility.error(SQLCODE,'hr_dm_init.populate_pi_table_g','(none)',
321 'R');
322 RAISE;
323
324 --
325 END populate_pi_table_g;
326 --
327
328
329 -- ------------------------- populate_pi_table_r ------------------------
330 -- Description: The phase items for the range phase are seeded
331 -- into the hr_dm_phase_items. An entry is made for each table within
332 -- a group that is applicable for the current migration.
333 --
334 --
335 -- Input Parameters
336 -- r_migration_data - record containing migration information
337 --
338 --
339 -- Output Parameters
340 -- <none>
341 --
342 --
343 -- ------------------------------------------------------------------------
344
345 --
346 PROCEDURE populate_pi_table_r(r_migration_data IN
347 hr_dm_utility.r_migration_rec) IS
348 --
349
350 l_phase_id NUMBER;
351 l_group_id NUMBER;
352 l_table_name VARCHAR2(30);
353
354 CURSOR csr_select_pi IS
355 SELECT tbg.group_id, tbl.table_name
356 FROM hr_dm_tables tbl, hr_dm_table_groupings tbg,
357 hr_dm_groups grp, hr_dm_application_groups apg
358 WHERE ( (tbl.table_id = tbg.table_id)
359 AND (tbg.group_id = grp.group_id)
360 AND (grp.group_type = 'D')
361 AND (grp.group_id = apg.group_id)
362 AND (apg.application_id = r_migration_data.application_id)
363 AND (apg.migration_type = r_migration_data.migration_type) );
364
365 --
366 BEGIN
367 --
368
369 hr_dm_utility.message('ROUT','entry:hr_dm_init.populate_pi_table_r', 5);
370 hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
371
372 l_phase_id := hr_dm_utility.get_phase_id('R',
373 r_migration_data.migration_id);
374
375 OPEN csr_select_pi;
376 LOOP
377 FETCH csr_select_pi INTO l_group_id, l_table_name;
378 EXIT WHEN csr_select_pi%NOTFOUND;
379
380 INSERT INTO hr_dm_phase_items (PHASE_ITEM_ID,
381 PHASE_ID,
382 LOADER_NAME,
383 BATCH_ID,
384 GROUP_ID,
385 TABLE_NAME,
386 STATUS,
387 START_TIME,
388 END_TIME,
389 CREATED_BY,
390 CREATION_DATE,
391 LAST_UPDATED_BY,
392 LAST_UPDATE_DATE,
393 LAST_UPDATE_LOGIN)
394 SELECT hr_dm_phase_items_s.nextval,
395 l_phase_id,
396 NULL,
397 NULL,
398 l_group_id,
399 l_table_name,
400 'NS',
401 NULL,
402 NULL,
403 1,
404 SYSDATE,
405 1,
406 SYSDATE,
407 NULL
408 FROM dual
409 WHERE NOT EXISTS
410 (SELECT NULL FROM hr_dm_phase_items
411 WHERE ((phase_id = l_phase_id)
412 AND (table_name = l_table_name)));
413
414 COMMIT;
415
416 hr_dm_utility.message('INFO','Seeding ' || l_table_name, 11);
417
418 END LOOP;
419 CLOSE csr_select_pi;
420
421 hr_dm_utility.message('INFO','Populate Phase Items table - R phase', 15);
422 hr_dm_utility.message('SUMM','Populate Phase Items table - R phase', 20);
423 hr_dm_utility.message('ROUT','exit:hr_dm_init.populate_pi_table_r', 25);
424 hr_dm_utility.message('PARA','(none)', 30);
425
426 -- error handling
427 EXCEPTION
428 WHEN OTHERS THEN
429 hr_dm_utility.error(SQLCODE,'hr_dm_init.populate_pi_table_r','(none)',
430 'R');
431 RAISE;
432
433 --
434 END populate_pi_table_r;
435 --
436
437
438 -- ----------------------------- get_filename -----------------------------
439 -- Description: Generates a filename for the AOL loader to download into
440 -- based on the last 5 digits (0 padded) of the phase_item_id
441 --
442 --
443 -- Input Parameters
444 -- p_phase_item_id phase item id from the DA phase
445 -- p_loader_conc_program to determine file extension
446 --
447 --
448 -- Output Parameters
449 -- created filename
450 --
451 --
452 -- ------------------------------------------------------------------------
453
454 --
455 FUNCTION get_filename (p_phase_item_id NUMBER,
456 p_loader_conc_program VARCHAR2)
457 RETURN VARCHAR2 IS
458 --
459
460 l_filename VARCHAR2(11);
461 l_length NUMBER;
462
463 --
464 BEGIN
465 --
466
467 hr_dm_utility.message('ROUT','entry:hr_dm_init.get_filename', 5);
468 hr_dm_utility.message('PARA','(p_phase_item_id - ' || p_phase_item_id ||
469 ')(p_loader_conc_program - ' || p_loader_conc_program ||
470 ')', 10);
471
472 l_length := length(to_char(p_phase_item_id));
473
474 IF l_length > 5 THEN
475 l_filename := substrb(to_char(p_phase_item_id),
476 lengthb(to_char(p_phase_item_id))-4);
477 ELSE
478 l_filename := lpad(to_char(p_phase_item_id),5,'0');
479 END IF;
480
481 IF (p_loader_conc_program = 'FNDSLOAD') THEN
482 l_filename := 'DM' || l_filename || '.slt';
483 ELSE
484 l_filename := 'DM' || l_filename || '.ldt';
485 END IF;
486
487 hr_dm_utility.message('INFO','Generated filename', 15);
488 hr_dm_utility.message('SUMM','Generated filename', 20);
489 hr_dm_utility.message('ROUT','exit:hr_dm_init.get_filename', 25);
490 hr_dm_utility.message('PARA','(l_filename - ' || l_filename || ')', 30);
491
492
493 RETURN(l_filename);
494
495 -- error handling
496 EXCEPTION
497 WHEN OTHERS THEN
498 hr_dm_utility.error(SQLCODE,'hr_dm_init.get_filename','(none)','R');
499 RAISE;
500
501 --
502 END get_filename;
503 --
504
505 -- --------------------------- seed_ua_da_data ---------------------------
506 -- Description: Seeds the passed data into the hr_dm_phase_items and the
507 -- hr_dm_loader_phase_items tables
508 --
509 --
510 -- Input Parameters
511 -- p_param_rec data record
512 -- p_custom_code_specified Y if custom code specified
513 --
514 --
515 -- Output Parameters
516 -- <none>
517 --
518 --
519 -- ------------------------------------------------------------------------
520
521 --
522 PROCEDURE seed_ua_da_data (p_param_rec r_loader_param_rec,
523 p_custom_code_specified VARCHAR2,
524 p_phase_id_da NUMBER,
525 p_phase_id_ua NUMBER) IS
526 --
527
528 l_phase_item_id_ua NUMBER;
529 l_phase_item_id_da NUMBER;
530 l_filename VARCHAR2(11);
531
532 --
533 BEGIN
534 --
535
536 hr_dm_utility.message('ROUT','entry:hr_dm_init.seed_ua_da_data', 5);
537 hr_dm_utility.message('PARA','(p_param_rec - record)', 10);
538
539 hr_dm_utility.message('INFO','Seeding ' || p_param_rec.loader_name , 10);
540
541 -- get phase_item_ids for ua and da phases
542 SELECT HR_DM_PHASE_ITEMS_S.nextval
543 INTO l_phase_item_id_da
544 FROM dual;
545 SELECT HR_DM_PHASE_ITEMS_S.nextval
546 INTO l_phase_item_id_ua
547 FROM dual;
548 -- hr_dm_phase_items da phase
549 INSERT INTO hr_dm_phase_items (PHASE_ITEM_ID,
550 PHASE_ID,
551 LOADER_NAME,
552 LOADER_PARAMS_ID,
553 BATCH_ID,
554 GROUP_ID,
555 TABLE_NAME,
556 STATUS,
557 START_TIME,
558 END_TIME)
559 SELECT l_phase_item_id_da,
560 p_phase_id_da,
561 p_param_rec.loader_name,
562 p_param_rec.loader_params_id,
563 NULL,
564 p_param_rec.group_id,
565 NULL,
566 'NS',
567 NULL,
568 NULL
569 FROM dual;
570 -- hr_dm_phase_items da phase
571 INSERT INTO hr_dm_phase_items (PHASE_ITEM_ID,
572 PHASE_ID,
573 LOADER_NAME,
574 LOADER_PARAMS_ID,
575 BATCH_ID,
576 GROUP_ID,
577 TABLE_NAME,
578 STATUS,
579 START_TIME,
580 END_TIME)
581 SELECT l_phase_item_id_ua,
582 p_phase_id_ua,
583 p_param_rec.loader_name,
584 p_param_rec.loader_params_id,
585 NULL,
586 p_param_rec.group_id,
587 NULL,
588 'NS',
589 NULL,
590 NULL
591 FROM dual;
592
593 -- generate filename
594 l_filename := get_filename(l_phase_item_id_da,
595 p_param_rec.loader_conc_program);
596
597 -- hr_dm_loader_phase_items
598 INSERT INTO hr_dm_loader_phase_items (
599 loader_phase_item_id,
600 da_phase_item_id,
601 ua_phase_item_id,
602 loader_name,
603 loader_conc_program,
604 loader_config_file,
605 loader_application,
606 application_id,
607 filename,
608 parameter_1,
609 parameter_2,
610 parameter_3,
611 parameter_4,
612 parameter_5,
613 parameter_6,
614 parameter_7,
615 parameter_8,
616 parameter_9,
617 parameter_10,
618 custom_code_specified)
619 SELECT
620 hr_dm_loader_phase_items_s.nextval,
621 l_phase_item_id_da,
622 l_phase_item_id_ua,
623 p_param_rec.loader_name,
624 p_param_rec.loader_conc_program,
625 p_param_rec.loader_config_file,
626 p_param_rec.loader_application,
627 p_param_rec.application_id,
628 l_filename,
629 p_param_rec.parameter1,
630 p_param_rec.parameter2,
631 p_param_rec.parameter3,
632 p_param_rec.parameter4,
633 p_param_rec.parameter5,
634 p_param_rec.parameter6,
635 p_param_rec.parameter7,
636 p_param_rec.parameter8,
637 p_param_rec.parameter9,
638 p_param_rec.parameter10,
639 p_custom_code_specified
640 FROM dual;
641
642 COMMIT;
643
644 hr_dm_utility.message('INFO','Seeded UA/DA phase item', 15);
645 hr_dm_utility.message('SUMM','Seeded UA/DA phase item', 20);
646 hr_dm_utility.message('ROUT','exit:hr_dm_init.seed_ua_da_data', 25);
647 hr_dm_utility.message('PARA','(none)', 30);
648
649 -- error handling
650 EXCEPTION
651 WHEN OTHERS THEN
652 hr_dm_utility.error(SQLCODE,'hr_dm_init.seed_ua_da_data','(none)','R');
653 RAISE;
654
655 --
656 END seed_ua_da_data;
657 --
658
659 -- ----------------------------- custom_specified -----------------------------
660 -- Description: Calls the custom code to determine if a flexfield is
661 -- to be migrated.
662 --
663 -- Input Parameters
664 -- p_phase_item_id phase item id from the DA phase
665 -- p_loader_conc_program to determine file extension
666 --
667 --
668 -- Output Parameters
669 -- created filename
670 --
671 --
672 -- ------------------------------------------------------------------------
673
674 --
675 FUNCTION custom_specified(p_check_custom_flex_call VARCHAR2,
676 r_migration_data hr_dm_utility.r_migration_rec,
677 r_flexfield_rec r_flexfield_rec)
678 RETURN BOOLEAN IS
679 --
680
681 l_retval BOOLEAN := FALSE;
682 l_message VARCHAR2(200);
683
684 --
685 BEGIN
686 --
687
688 hr_dm_utility.message('ROUT','entry:hr_dm_init.custom_specified', 5);
689 hr_dm_utility.message('PARA','(p_check_custom_flex_call - ' ||
690 p_check_custom_flex_call ||
691 ')(r_migration_data - record)' ||
692 '(r_flexfield_rec - record)', 10);
693
694 hr_dm_utility.message('INFO','Calling hr_dm_aol_ext.custom_test', 15);
695
696
697 IF r_flexfield_rec.flexfield_type = 'D' THEN
698 l_message := '(descriptive_flexfield_name =' ||
699 r_flexfield_rec.descriptive_flexfield_name ||
700 ')(descriptive_flex_context_code =' ||
701 r_flexfield_rec.descriptive_flex_context_code || ')';
702 ELSE
703 l_message := '(id_flex_code =' ||
704 r_flexfield_rec.id_flex_code ||
705 ')(id_flex_structure_codee =' ||
706 r_flexfield_rec.id_flex_structure_code || ')';
707 END IF;
708
709 hr_dm_utility.message('INFO',l_message,12);
710
711 -- call test code directly
712
713 l_retval := hr_dm_aol_ext.custom_test(r_migration_data,
714 r_flexfield_rec);
715
716 IF l_retval THEN
717 hr_dm_utility.message('INFO','Custom code requests download of ' ||
718 l_message, 30);
719 ELSE
720 hr_dm_utility.message('INFO','Custom code does not request download of ' ||
721 l_message, 30);
722 END IF;
723
724
725 hr_dm_utility.message('INFO','Checked custom flex', 15);
726 hr_dm_utility.message('SUMM','Checked custom flex', 20);
727 hr_dm_utility.message('ROUT','exit:hr_dm_init.custom_specified', 25);
728 IF l_retval THEN
729 hr_dm_utility.message('PARA','(l_retval - TRUE)', 30);
730 ELSE
731 hr_dm_utility.message('PARA','(l_retval - FALSE)', 30);
732 END IF;
733
734 RETURN(l_retval);
735
736 -- error handling
737 EXCEPTION
738 WHEN OTHERS THEN
739 hr_dm_utility.error(SQLCODE,'hr_dm_init.custom_specified','(none)','R');
740 RAISE;
741
742 --
743 END custom_specified;
744 --
745
746 -- --------------------------- seed_data ---------------------------
747 -- Description: Seeds the custom specified flexfield data
748 --
749 --
750 -- Input Parameters
751 -- p_param_rec data record
752 -- p_custom_code_specified Y if custom code specified
753 --
754 --
755 -- Output Parameters
756 -- <none>
757 --
758 --
759 -- ------------------------------------------------------------------------
760
761 --
762 PROCEDURE seed_data (r_migration_data IN hr_dm_utility.r_migration_rec,
763 r_flexfield_data IN r_flexfield_rec,
764 p_phase_id_da IN NUMBER,
765 p_phase_id_ua IN NUMBER) IS
766 --
767
768 l_param_rec r_loader_param_rec;
769 l_app_short_name VARCHAR2(50);
770
771 CURSOR csr_loader_info IS
772 SELECT tbl.loader_conc_program,
773 tbl.loader_config_file,
774 tbl.loader_application,
775 grp.group_id
776 FROM hr_dm_groups grp,
777 hr_dm_application_groups apg,
778 hr_dm_table_groupings tbg,
779 hr_dm_tables tbl
780 WHERE tbl.table_id = tbg.table_id
781 AND tbg.group_id = grp.group_id
782 AND grp.group_type = 'A'
783 AND grp.group_id = apg.group_id
784 AND apg.application_id = r_migration_data.application_id
785 AND apg.migration_type = r_migration_data.migration_type
786 AND tbl.loader_name = l_param_rec.loader_name;
787
788 CURSOR csr_app_id IS
789 SELECT application_short_name
790 FROM fnd_application
791 WHERE application_id = r_flexfield_data.application_id;
792
793 --
794 BEGIN
795 --
796
797 hr_dm_utility.message('ROUT','entry:hr_dm_init.seed_data', 5);
798 hr_dm_utility.message('PARA','(r_migration_data - record)' ||
799 '(r_flexfield_data - record)' ||
800 '(p_phase_id_da - ' || p_phase_id_da ||
801 ')(p_phase_id_ua - ' || p_phase_id_ua ||
802 ')', 10);
803
804
805 -- build up r_loader_param_rec record
806
807 l_param_rec.loader_params_id := NULL;
808 l_param_rec.application_id := r_migration_data.application_id;
809 l_param_rec.parameter1 := ':mode';
810 l_param_rec.parameter2 := ':config';
811 l_param_rec.parameter3 := ':data';
812
813 -- Find the flexfield application short name
814
815 OPEN csr_app_id;
816 FETCH csr_app_id INTO l_app_short_name;
817 CLOSE csr_app_id;
818
819 hr_dm_utility.message('INFO','r_flexfield_data.flexfield_type - ' ||
820 r_flexfield_data.flexfield_type,1);
821
822 IF r_flexfield_data.flexfield_type = 'K' THEN
823 -- key flexfield data
824 l_param_rec.parameter4 := 'KEY_FLEX';
825 l_param_rec.parameter5 := 'APPLICATION_SHORT_NAME=' ||
826 l_app_short_name;
827 l_param_rec.parameter6 := 'ID_FLEX_CODE=' ||
828 r_flexfield_data.id_flex_code;
829 l_param_rec.parameter7 := 'P_LEVEL=''COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:' ||
830 'WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL''';
831 l_param_rec.parameter8 := 'P_STRUCTURE_CODE=' ||
832 r_flexfield_data.id_flex_structure_code;
833 l_param_rec.parameter9 := NULL;
834
835 l_param_rec.loader_name := 'Key flexfields';
836
837 ELSE
838 -- descriptive flexfield data
839 l_param_rec.parameter4 := 'DESC_FLEX';
840 l_param_rec.parameter5 := 'APPLICATION_SHORT_NAME=' ||
841 l_app_short_name;
842 l_param_rec.parameter6 := 'DESCRIPTIVE_FLEXFIELD_NAME=' ||
843 r_flexfield_data.descriptive_flexfield_name;
844 l_param_rec.parameter7 := 'P_LEVEL=''COL_ALL:REF_ALL:CTX_ONE:SEG_ALL''';
845 l_param_rec.parameter8 := 'P_CONTEXT_CODE=' ||
846 r_flexfield_data.descriptive_flex_context_code;
847 l_param_rec.parameter9 := NULL;
848
849 IF r_migration_data.migration_type = 'SD' THEN
850 l_param_rec.loader_name := 'Desc flexfields (selective)';
851 l_param_rec.parameter9 := ':selective';
852 ELSIF r_migration_data.migration_type = 'SL' THEN
853 l_param_rec.loader_name := 'Desc flexfields (lookups)';
854 ELSE
855 l_param_rec.loader_name := 'Descriptive flexfields';
856 END IF;
857
858 END IF;
859
860 hr_dm_utility.message('INFO','l_param_rec.loader_name - ' ||
861 l_param_rec.loader_name,1);
862
863
864 -- null out unused entries
865 l_param_rec.parameter10 := NULL;
866
867 -- get loader info from hr_dm_tables
868 OPEN csr_loader_info;
869 FETCH csr_loader_info INTO l_param_rec.loader_conc_program,
870 l_param_rec.loader_config_file,
871 l_param_rec.loader_application,
872 l_param_rec.group_id;
873 CLOSE csr_loader_info;
874
875 -- check for FNDSLOAD and seed dummy config file
876 IF l_param_rec.loader_conc_program = 'FNDSLOAD' THEN
877 l_param_rec.loader_config_file := 'n/a';
878 END IF;
879
880 -- insert data
881 seed_ua_da_data (l_param_rec,
882 'Y',
883 p_phase_id_da,
884 p_phase_id_ua);
885
886 hr_dm_utility.message('INFO','Seeded UA/DA phase item', 15);
887 hr_dm_utility.message('SUMM','Seeded UA/DA phase item', 20);
888 hr_dm_utility.message('ROUT','exit:hr_dm_init.seed_ua_da_data', 25);
889 hr_dm_utility.message('PARA','(none)', 30);
890
891 -- error handling
892 EXCEPTION
893 WHEN OTHERS THEN
894 hr_dm_utility.error(SQLCODE,'hr_dm_init.seed_data','(none)','R');
895 RAISE;
896
897 --
898 END seed_data;
899 --
900
901
902 -- ------------------------- populate_pi_table_da ------------------------
903 -- Description: The phase items for the download aol phase are seeded
904 -- into the hr_dm_phase_items. An entry is made for each aol loader within
905 -- a group that is applicable for the current migration.
906 --
907 -- UA phase items are also seeded in this procedure.
908 --
909 --
910 -- Input Parameters
911 -- r_migration_data - record containing migration information
912 --
913 --
914 -- Output Parameters
915 -- <none>
916 --
917 --
918 -- ------------------------------------------------------------------------
919
920
921 --
922 PROCEDURE populate_pi_table_da(r_migration_data IN
923 hr_dm_utility.r_migration_rec) IS
924 --
925
926 l_phase_id_da NUMBER;
927 l_phase_id_ua NUMBER;
928 l_check_custom_flex_call VARCHAR2(100);
929 l_param_rec r_loader_param_rec;
930 r_flexfield_data r_flexfield_rec;
931
932 CURSOR csr_select_pi IS
933 SELECT tbl.loader_name,
934 tbl.loader_conc_program,
935 tbl.loader_config_file,
936 tbl.loader_application,
937 lp.loader_params_id,
938 lp.application_id,
939 lp.parameter1,
940 lp.parameter2,
941 lp.parameter3,
942 lp.parameter4,
943 lp.parameter5,
944 lp.parameter6,
945 lp.parameter7,
946 lp.parameter8,
947 lp.parameter9,
948 lp.parameter10,
949 grp.group_id
950 FROM hr_dm_groups grp,
951 hr_dm_application_groups apg,
952 hr_dm_table_groupings tbg,
953 hr_dm_tables tbl,
954 hr_dm_loader_params lp
955 WHERE (lp.table_id = tbl.table_id)
956 AND (lp.application_id = r_migration_data.application_id)
957 AND (tbl.table_id = tbg.table_id)
958 AND (tbg.group_id = grp.group_id)
959 AND (grp.group_type = 'A')
960 AND (grp.group_id = apg.group_id)
961 AND (apg.application_id = lp.application_id)
962 AND (apg.migration_type = r_migration_data.migration_type);
963
964 CURSOR csr_dff IS
965 SELECT 'D',
966 a.application_id,
967 NULL,
968 NULL,
969 fc.descriptive_flexfield_name,
970 fc.descriptive_flex_context_code
971 FROM fnd_descr_flex_contexts_vl fc,
972 fnd_descriptive_flexs_vl f,
973 fnd_application a
974 WHERE fc.descriptive_flexfield_name =
975 f.descriptive_flexfield_name
976 AND f.application_id = a.application_id
977 AND a.application_short_name IN ('PER','PAY','BEN','FND')
978 AND fc.descriptive_flexfield_name NOT LIKE '$SRS$%';
979
980 CURSOR csr_sdff IS
981 SELECT 'D',
982 a.application_id,
983 NULL,
984 NULL,
985 fc.descriptive_flexfield_name,
986 fc.descriptive_flex_context_code
987 FROM fnd_descr_flex_contexts_vl fc,
988 fnd_descriptive_flexs_vl f,
989 fnd_application a,
990 hr_dm_migrations mig
991 WHERE fc.descriptive_flexfield_name =
992 f.descriptive_flexfield_name
993 AND f.application_id = a.application_id
994 AND a.application_short_name IN ('PER','PAY','BEN','FND')
995 AND fc.descriptive_flexfield_name NOT LIKE '$SRS$%'
996 AND fc.descriptive_flexfield_name = mig.selective_migration_criteria
997 AND mig.migration_id = r_migration_data.migration_id;
998
999 CURSOR csr_kff IS
1000 SELECT 'K',
1001 a.application_id,
1002 f.id_flex_code,
1003 f.id_flex_structure_code,
1004 NULL,
1005 NULL
1006 FROM fnd_id_flex_structures_vl f,
1007 fnd_application a,
1008 fnd_id_flexs fc
1009 WHERE f.id_flex_code = fc.id_flex_code
1010 AND fc.application_id = f.application_id
1011 AND f.application_id = a.application_id
1012 AND a.application_short_name IN ('PER','PAY','BEN','FND');
1013
1014
1015 --
1016 BEGIN
1017 --
1018
1019 hr_dm_utility.message('ROUT','entry:hr_dm_init.populate_pi_table_da', 5);
1020 hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
1021
1022 l_phase_id_da := hr_dm_utility.get_phase_id('DA',
1023 r_migration_data.migration_id);
1024 l_phase_id_ua := hr_dm_utility.get_phase_id('UA',
1025 r_migration_data.migration_id);
1026
1027 -- see if we are using the custom_flex_solution
1028 l_check_custom_flex_call := check_custom_flex;
1029
1030 OPEN csr_select_pi;
1031 LOOP
1032 FETCH csr_select_pi INTO l_param_rec;
1033
1034 EXIT WHEN csr_select_pi%NOTFOUND;
1035
1036 -- seed data for:
1037 -- 1. hr_dm_phase_items da phase
1038 -- 2. hr_dm_phase_items ua phase
1039 -- 3. hr_dm_loader_phase_items
1040
1041 -- no data is automatically seeded when:
1042 -- 1. l_check_custom_flex_call is not null
1043 -- 2. up/downloading flexfield data
1044
1045 hr_dm_utility.message('INFO','Checking ' || l_param_rec.loader_name , 10);
1046 hr_dm_utility.message('INFO','l_check_custom_flex_call ' ||
1047 NVL(l_check_custom_flex_call,'-NULL-') , 10);
1048
1049
1050 IF (
1051 (l_check_custom_flex_call IS NOT NULL)
1052 AND
1053 (l_param_rec.loader_name IN ('Descriptive flexfields',
1054 'Desc flexfields (selective)',
1055 'Desc flexfields (lookups)',
1056 'Key flexfields')
1057 )
1058 ) THEN
1059
1060 -- process this data later
1061 -- this is for data where confirmation is required
1062 NULL;
1063
1064 ELSE
1065
1066 -- check for FNDSLOAD and seed dummy config file
1067 IF l_param_rec.loader_conc_program = 'FNDSLOAD' THEN
1068 l_param_rec.loader_config_file := 'n/a';
1069 END IF;
1070
1071 seed_ua_da_data(l_param_rec,
1072 'N',
1073 l_phase_id_da,
1074 l_phase_id_ua);
1075
1076 END IF;
1077
1078 END LOOP;
1079 CLOSE csr_select_pi;
1080
1081 -- now seed custom flex data
1082 IF (l_check_custom_flex_call IS NOT NULL) THEN
1083
1084 -- descriptive flexfields
1085
1086 IF r_migration_data.migration_type = 'SD' THEN
1087
1088 -- selective dff migration
1089 hr_dm_utility.message('INFO','Seeding selective dff migration' , 10);
1090 OPEN csr_sdff;
1091 LOOP
1092 FETCH csr_sdff INTO r_flexfield_data;
1093 EXIT WHEN csr_sdff%NOTFOUND;
1094
1095 -- see if required
1096 IF (custom_specified(l_check_custom_flex_call,
1097 r_migration_data,
1098 r_flexfield_data)) THEN
1099 seed_data(r_migration_data,
1100 r_flexfield_data,
1101 l_phase_id_da,
1102 l_phase_id_ua);
1103 END IF;
1104
1105 END LOOP;
1106 CLOSE csr_sdff;
1107
1108 ELSE
1109
1110 -- non-selective migration
1111 hr_dm_utility.message('INFO','Seeding dff migration' , 10);
1112 OPEN csr_dff;
1113 LOOP
1114 FETCH csr_dff INTO r_flexfield_data;
1115 EXIT WHEN csr_dff%NOTFOUND;
1116
1117 -- see if required
1118 IF (custom_specified(l_check_custom_flex_call,
1119 r_migration_data,
1120 r_flexfield_data)) THEN
1121 seed_data(r_migration_data,
1122 r_flexfield_data,
1123 l_phase_id_da,
1124 l_phase_id_ua);
1125 END IF;
1126
1127 END LOOP;
1128 CLOSE csr_dff;
1129
1130
1131 -- seed key flex for non-SL migrations
1132 IF r_migration_data.migration_type <> 'SL' THEN
1133
1134 -- key flexfields
1135 hr_dm_utility.message('INFO','Seeding kff migration' , 10);
1136 OPEN csr_kff;
1137 LOOP
1138 FETCH csr_kff INTO r_flexfield_data;
1139 EXIT WHEN csr_kff%NOTFOUND;
1140
1141 -- see if required
1142 IF (custom_specified(l_check_custom_flex_call,
1143 r_migration_data,
1144 r_flexfield_data)) THEN
1145 seed_data(r_migration_data,
1146 r_flexfield_data,
1147 l_phase_id_da,
1148 l_phase_id_ua);
1149 END IF;
1150
1151 END LOOP;
1152 CLOSE csr_kff;
1153
1154 END IF;
1155
1156 END IF;
1157
1158 END IF;
1159
1160 hr_dm_utility.message('INFO','Populate Phase Items table - DA phase', 15);
1161 hr_dm_utility.message('SUMM','Populate Phase Items table - DA phase', 20);
1162 hr_dm_utility.message('ROUT','exit:hr_dm_init.populate_pi_table_da', 25);
1163 hr_dm_utility.message('PARA','(none)', 30);
1164
1165 -- error handling
1166 EXCEPTION
1167 WHEN OTHERS THEN
1168 hr_dm_utility.error(SQLCODE,'hr_dm_init.populate_pi_table_da','(none)',
1169 'R');
1170 RAISE;
1171
1172 --
1173 END populate_pi_table_da;
1174 --
1175
1176 -- ------------------------- populate_pi_table_dp ------------------------
1177 -- Description: The phase items for the download phase are seeded
1178 -- into the hr_dm_phase_items. An entry is made for each group that is
1179 -- applicable for the current migration.
1180 --
1181 -- It also truncates the datapump tables
1182 --
1183 --
1184 -- Input Parameters
1185 -- r_migration_data - record containing migration information
1186 --
1187 --
1188 -- Output Parameters
1189 -- <none>
1190 --
1191 --
1192 -- ------------------------------------------------------------------------
1193
1194
1195 --
1196 PROCEDURE populate_pi_table_dp(r_migration_data IN
1197 hr_dm_utility.r_migration_rec) IS
1198 --
1199
1200 l_phase_id NUMBER;
1201 l_group_id NUMBER;
1202 l_batch_id NUMBER;
1203 l_business_group_name hr_dm_migrations.business_group_name%type;
1204 l_batch_name VARCHAR2(80);
1205 l_group_text VARCHAR2(240);
1206
1207 CURSOR csr_select_pi IS
1208 SELECT grp.group_id, grp.description
1209 FROM hr_dm_groups grp, hr_dm_application_groups apg
1210 WHERE ((grp.group_type = 'D')
1211 AND (grp.group_id = apg.group_id)
1212 AND (apg.application_id = r_migration_data.application_id)
1213 AND (apg.migration_type = r_migration_data.migration_type) )
1214 ORDER BY apg.group_order;
1215
1216 CURSOR csr_select_bg_name IS
1217 SELECT pbg.name
1218 FROM per_business_groups pbg, hr_dm_migrations mig
1219 WHERE ((mig.migration_id = r_migration_data.migration_id)
1220 AND (mig.business_group_id = pbg.business_group_id));
1221
1222
1223 --
1224 BEGIN
1225 --
1226
1227 hr_dm_utility.message('ROUT','entry:hr_dm_init.populate_pi_table_dp', 5);
1228 hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
1229
1230 l_phase_id := hr_dm_utility.get_phase_id('DP',
1231 r_migration_data.migration_id);
1232
1233 -- truncate datapump tables
1234 hr_dm_copy.delete_datapump_tables;
1235
1236
1237
1238
1239 OPEN csr_select_pi;
1240 LOOP
1241 FETCH csr_select_pi INTO l_group_id, l_group_text;
1242 EXIT WHEN csr_select_pi%NOTFOUND;
1243
1244 -- allocate batch_id for this group
1245 OPEN csr_select_bg_name;
1246 LOOP
1247 FETCH csr_select_bg_name INTO l_business_group_name;
1248 EXIT WHEN csr_select_bg_name%NOTFOUND;
1249 END LOOP;
1250 CLOSE csr_select_bg_name;
1251
1252 l_batch_name := '(Mig ID ' || r_migration_data.migration_id ||
1253 ')(Ph ID ' || l_phase_id ||
1254 ')(Grp ID ' || l_group_id || ')' ||
1255 '[' || l_group_text || ']';
1256
1257 l_batch_id := hr_pump_utils.create_batch_header(
1258 p_batch_name => l_batch_name,
1259 p_business_group_name => l_business_group_name,
1260 p_reference => 'HR Data Migrator');
1261
1262 INSERT INTO hr_dm_phase_items (PHASE_ITEM_ID,
1263 PHASE_ID,
1264 LOADER_NAME,
1265 BATCH_ID,
1266 GROUP_ID,
1267 TABLE_NAME,
1268 STATUS,
1269 START_TIME,
1270 END_TIME,
1271 CREATED_BY,
1272 CREATION_DATE,
1273 LAST_UPDATED_BY,
1274 LAST_UPDATE_DATE,
1275 LAST_UPDATE_LOGIN)
1276 SELECT hr_dm_phase_items_s.nextval,
1277 l_phase_id,
1278 NULL,
1279 l_batch_id,
1280 l_group_id,
1281 NULL,
1282 'NS',
1283 NULL,
1284 NULL,
1285 1,
1286 SYSDATE,
1287 1,
1288 SYSDATE,
1289 NULL
1290 FROM dual
1291 WHERE NOT EXISTS
1292 (SELECT NULL FROM hr_dm_phase_items
1293 WHERE ((phase_id = l_phase_id)
1294 AND (batch_id = l_batch_id)
1295 AND (group_id = l_group_id)));
1296
1297 COMMIT;
1298
1299 hr_dm_utility.message('INFO','Seeding ' || l_group_text, 11);
1300
1301
1302 END LOOP;
1303 CLOSE csr_select_pi;
1304
1305
1306 hr_dm_utility.message('INFO','Populate Phase Items table - DP phase', 15);
1307 hr_dm_utility.message('SUMM','Populate Phase Items table - DP phase', 20);
1308 hr_dm_utility.message('ROUT','exit:hr_dm_init.populate_pi_table_dp', 25);
1309 hr_dm_utility.message('PARA','(none)', 30);
1310
1311 -- error handling
1312 EXCEPTION
1313 WHEN OTHERS THEN
1314 hr_dm_utility.error(SQLCODE,'hr_dm_init.populate_pi_table_dp','(none)',
1315 'R');
1316 RAISE;
1317
1318 --
1319 END populate_pi_table_dp;
1320 --
1321
1322 -- ------------------------- populate_pi_table_cp ------------------------
1323 -- Description: The phase items for the copy phase are seeded
1324 -- into the hr_dm_phase_items. (none required)
1325 --
1326 --
1327 -- Input Parameters
1328 -- r_migration_data - record containing migration information
1329 --
1330 --
1331 -- Output Parameters
1332 -- <none>
1333 --
1334 --
1335 -- ------------------------------------------------------------------------
1336
1337
1338 --
1339 PROCEDURE populate_pi_table_cp(r_migration_data IN
1340 hr_dm_utility.r_migration_rec) IS
1341 --
1342
1343 --
1344 BEGIN
1345 --
1346
1347 hr_dm_utility.message('ROUT','entry:hr_dm_init.populate_pi_table_cp', 5);
1348 hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
1349
1350 --
1351 -- phase specific code to be inserted here
1352 --
1353 -- Copy phase is now offline only
1354 --
1355 -- no code required
1356 --
1357
1358 hr_dm_utility.message('INFO','Populate Phase Items table - CP phase', 15);
1359 hr_dm_utility.message('SUMM','Populate Phase Items table - CP phase', 20);
1360 hr_dm_utility.message('ROUT','exit:hr_dm_init.populate_pi_table_cp', 25);
1361 hr_dm_utility.message('PARA','(none)', 30);
1362
1363 -- error handling
1364 EXCEPTION
1365 WHEN OTHERS THEN
1366 hr_dm_utility.error(SQLCODE,'hr_dm_init.populate_pi_table_cp','(none)',
1367 'R');
1368 RAISE;
1369
1370 --
1371 END populate_pi_table_cp;
1372 --
1373
1374
1375 -- ------------------------- populate_pi_table_up ------------------------
1376 -- Description: The phase items for the upload phase are seeded
1377 -- into the hr_dm_phase_items. An entry is made for each group that is
1378 -- applicable for the current migration.
1379 --
1380 -- The table hr_dm_resolve_pks has entries for migrations from the current
1381 -- source database deleted for an FW or SR migration.
1382 --
1383 -- Input Parameters
1384 -- r_migration_data - record containing migration information
1385 --
1386 --
1387 -- Output Parameters
1388 -- <none>
1389 --
1390 --
1391 -- ------------------------------------------------------------------------
1392
1393
1394
1395 --
1396 PROCEDURE populate_pi_table_up(r_migration_data IN
1397 hr_dm_utility.r_migration_rec) IS
1398 --
1399
1400 l_phase_id NUMBER;
1401 l_group_id NUMBER;
1402 l_batch_id NUMBER;
1403 l_business_group_name hr_dm_migrations.business_group_name%type;
1404 l_batch_name VARCHAR2(80);
1405 l_group_text VARCHAR2(240);
1406 l_batch_start NUMBER;
1407 l_batch_end NUMBER;
1408 l_batch_txt VARCHAR2(80);
1409
1410 CURSOR csr_select_pi IS
1411 SELECT grp.group_id, grp.description
1412 FROM hr_dm_groups grp, hr_dm_application_groups apg
1413 WHERE ((grp.group_type = 'D')
1414 AND (grp.group_id = apg.group_id)
1415 AND (apg.application_id = r_migration_data.application_id)
1416 AND (apg.migration_type = r_migration_data.migration_type) )
1417 ORDER BY apg.group_order;
1418
1419 CURSOR csr_select_batch_id IS
1420 SELECT bh.batch_id, bh.batch_name
1421 FROM hr_pump_batch_headers bh
1422 WHERE bh.reference = 'HR Data Migrator'
1423 AND EXISTS (SELECT NULL
1424 FROM hr_pump_batch_lines bl
1425 WHERE bh.batch_id = bl.batch_id
1426 AND ROWNUM < 2);
1427
1428
1429 --
1430 BEGIN
1431 --
1432
1433 hr_dm_utility.message('ROUT','entry:hr_dm_init.populate_pi_table_up', 5);
1434 hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
1435
1436 l_phase_id := hr_dm_utility.get_phase_id('UP',
1437 r_migration_data.migration_id);
1438
1439 OPEN csr_select_pi;
1440 LOOP
1441 FETCH csr_select_pi INTO l_group_id, l_group_text;
1442 EXIT WHEN csr_select_pi%NOTFOUND;
1443
1444
1445 -- find the batch_id for this group
1446
1447
1448 OPEN csr_select_batch_id;
1449 LOOP
1450 FETCH csr_select_batch_id INTO l_batch_id, l_batch_name;
1451 EXIT WHEN (csr_select_batch_id%NOTFOUND);
1452
1453
1454
1455 l_batch_start := instr(l_batch_name, '[')+1;
1456 l_batch_end := instr(l_batch_name, ']');
1457 l_batch_txt := substr (l_batch_name, l_batch_start,
1458 l_batch_end - l_batch_start);
1459 EXIT WHEN (csr_select_batch_id%NOTFOUND);
1460
1461 IF (l_group_text = l_batch_txt) THEN
1462 INSERT INTO hr_dm_phase_items (PHASE_ITEM_ID,
1463 PHASE_ID,
1464 LOADER_NAME,
1465 BATCH_ID,
1466 GROUP_ID,
1467 TABLE_NAME,
1468 STATUS,
1469 START_TIME,
1470 END_TIME,
1471 CREATED_BY,
1472 CREATION_DATE,
1473 LAST_UPDATED_BY,
1474 LAST_UPDATE_DATE,
1475 LAST_UPDATE_LOGIN)
1476 SELECT hr_dm_phase_items_s.nextval,
1477 l_phase_id,
1478 NULL,
1479 l_batch_id,
1480 l_group_id,
1481 NULL,
1482 'NS',
1483 NULL,
1484 NULL,
1485 1,
1486 SYSDATE,
1487 1,
1488 SYSDATE,
1489 NULL
1490 FROM dual
1491 WHERE NOT EXISTS
1492 (SELECT NULL FROM hr_dm_phase_items
1493 WHERE ((phase_id = l_phase_id)
1494 AND (batch_id = l_batch_id)
1495 AND (group_id = l_group_id)));
1496
1497 COMMIT;
1498
1499 hr_dm_utility.message('INFO','Seeding ' || l_group_text, 11);
1500
1501 END IF;
1502
1503 END LOOP;
1504 CLOSE csr_select_batch_id;
1505
1506
1507
1508 END LOOP;
1509 CLOSE csr_select_pi;
1510
1511
1512
1513
1514 IF r_migration_data.migration_type in ('FW','SR') THEN
1515 hr_dm_utility.message('INFO',
1516 'Deleting hr_dm_resolve_pks table for source database' ||
1517 ' for FW or SR migration', 15);
1518 DELETE FROM hr_dm_resolve_pks
1519 WHERE source_database_instance = r_migration_data.source_database_instance;
1520 END IF;
1521
1522 IF r_migration_data.migration_type = 'A' THEN
1523 hr_dm_utility.message('INFO',
1524 'Deleting hr_dm_resolve_pks table for source database' ||
1525 ' for A migration and NR_NAVIGATION_UNITS data', 15);
1526 DELETE FROM hr_dm_resolve_pks
1527 WHERE source_database_instance = r_migration_data.source_database_instance
1528 AND TABLE_NAME = 'HR_NAVIGATION_UNITS';
1529 END IF;
1530
1531
1532
1533 hr_dm_utility.message('INFO','Populate Phase Items table - UP phase', 15);
1534 hr_dm_utility.message('SUMM','Populate Phase Items table - UP phase', 20);
1535 hr_dm_utility.message('ROUT','exit:hr_dm_init.populate_pi_table_up', 25);
1536 hr_dm_utility.message('PARA','(none)', 30);
1537
1538 -- error handling
1539 EXCEPTION
1540 WHEN OTHERS THEN
1541 hr_dm_utility.error(SQLCODE,'hr_dm_init.populate_pi_table_up','(none)',
1542 'R');
1543 RAISE;
1544
1545 --
1546 END populate_pi_table_up;
1547 --
1548
1549
1550
1551 -- ------------------------- populate_pi_table_ua ------------------------
1552 -- Description: The phase items for the upload aol phase are seeded
1553 -- into the hr_dm_phase_items. An entry is made for each aol loader within
1554 -- a group that is applicable for the current migration.
1555 --
1556 -- This data is now seeded in the populate_pi_table_da procedure.
1557 --
1558 --
1559 -- Input Parameters
1560 -- r_migration_data - record containing migration information
1561 --
1562 --
1563 -- Output Parameters
1564 -- <none>
1565 --
1566 --
1567 -- ------------------------------------------------------------------------
1568
1569 --
1570 PROCEDURE populate_pi_table_ua(r_migration_data IN
1571 hr_dm_utility.r_migration_rec) IS
1572 --
1573
1574 l_phase_id NUMBER;
1575 l_group_id NUMBER;
1576 l_loader_name VARCHAR2(30);
1577 l_loader_params_id NUMBER;
1578
1579 CURSOR csr_select_pi IS
1580 SELECT tbl.loader_name,
1581 lp.loader_params_id,
1582 grp.group_id
1583 FROM hr_dm_groups grp,
1584 hr_dm_application_groups apg,
1585 hr_dm_table_groupings tbg,
1586 hr_dm_tables tbl,
1587 hr_dm_loader_params lp
1588 WHERE (lp.table_id = tbl.table_id)
1589 AND (lp.application_id = r_migration_data.application_id)
1590 AND (tbl.table_id = tbg.table_id)
1591 AND (tbg.group_id = grp.group_id)
1592 AND (grp.group_type = 'A')
1593 AND (grp.group_id = apg.group_id)
1594 AND (apg.application_id = r_migration_data.application_id)
1595 AND (apg.migration_type = r_migration_data.migration_type)
1596 ORDER BY tbl.table_id;
1597
1598 --
1599 BEGIN
1600 --
1601
1602 hr_dm_utility.message('ROUT','entry:hr_dm_init.populate_pi_table_ua', 5);
1603 hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
1604
1605 l_phase_id := hr_dm_utility.get_phase_id('UA',
1606 r_migration_data.migration_id);
1607
1608
1609 -- no work is done in this procedure as the UA phase items are
1610 -- seeded as part of the DA phase item seeding
1611
1612
1613 hr_dm_utility.message('INFO','Populate Phase Items table - UA phase', 15);
1614 hr_dm_utility.message('SUMM','Populate Phase Items table - UA phase', 20);
1615 hr_dm_utility.message('ROUT','exit:hr_dm_init.populate_pi_table_ua', 25);
1616 hr_dm_utility.message('PARA','(none)', 30);
1617
1618 -- error handling
1619 EXCEPTION
1620 WHEN OTHERS THEN
1621 hr_dm_utility.error(SQLCODE,'hr_dm_init.populate_pi_table_ua','(none)',
1622 'R');
1623 RAISE;
1624
1625
1626 --
1627 END populate_pi_table_ua;
1628 --
1629
1630 -- ------------------------- populate_pi_table_d ------------------------
1631 -- Description: The phase items for the delete phase are seeded
1632 -- into the hr_dm_phase_items. An entry is made for each group that is
1633 -- applicable for the current migration.
1634 --
1635 --
1636 -- Input Parameters
1637 -- r_migration_data - record containing migration information
1638 --
1639 --
1640 -- Output Parameters
1641 -- <none>
1642 --
1643 --
1644 -- ------------------------------------------------------------------------
1645
1646
1647
1648 --
1649 PROCEDURE populate_pi_table_d(r_migration_data IN
1650 hr_dm_utility.r_migration_rec) IS
1651 --
1652
1653 l_phase_id NUMBER;
1654 l_group_id NUMBER;
1655 l_group_text VARCHAR2(240);
1656
1657 CURSOR csr_select_pi IS
1658 SELECT apg.group_id, grp.description
1659 FROM hr_dm_application_groups apg,
1660 hr_dm_groups grp
1661 WHERE ((apg.application_id = r_migration_data.application_id)
1662 AND (apg.migration_type = r_migration_data.migration_type)
1663 AND (apg.group_id = grp.group_id))
1664 ORDER BY apg.group_order DESC;
1665
1666 --
1667 BEGIN
1668 --
1669
1670 hr_dm_utility.message('ROUT','entry:hr_dm_init.populate_pi_table_d', 5);
1671 hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
1672
1673 l_phase_id := hr_dm_utility.get_phase_id('D',
1674 r_migration_data.migration_id);
1675
1676
1677 OPEN csr_select_pi;
1678 LOOP
1679 FETCH csr_select_pi INTO l_group_id, l_group_text;
1680 EXIT WHEN csr_select_pi%NOTFOUND;
1681
1682 INSERT INTO hr_dm_phase_items (PHASE_ITEM_ID,
1683 PHASE_ID,
1684 LOADER_NAME,
1685 BATCH_ID,
1686 GROUP_ID,
1687 TABLE_NAME,
1688 STATUS,
1689 START_TIME,
1690 END_TIME,
1691 CREATED_BY,
1692 CREATION_DATE,
1693 LAST_UPDATED_BY,
1694 LAST_UPDATE_DATE,
1695 LAST_UPDATE_LOGIN)
1696 SELECT hr_dm_phase_items_s.nextval,
1697 l_phase_id,
1698 NULL,
1699 NULL,
1700 l_group_id,
1701 NULL,
1702 'NS',
1703 NULL,
1704 NULL,
1705 1,
1706 SYSDATE,
1707 1,
1708 SYSDATE,
1709 NULL
1710 FROM dual
1711 WHERE NOT EXISTS
1712 (SELECT NULL FROM hr_dm_phase_items
1713 WHERE ((phase_id = l_phase_id)
1714 AND (group_id = l_group_id)));
1715
1716 COMMIT;
1717
1718 hr_dm_utility.message('INFO','Seeding ' || l_group_text, 11);
1719
1720 END LOOP;
1721 CLOSE csr_select_pi;
1722
1723
1724 hr_dm_utility.message('INFO','Populate Phase Items table - D phase', 15);
1725 hr_dm_utility.message('SUMM','Populate Phase Items table - D phase', 20);
1726 hr_dm_utility.message('ROUT','exit:hr_dm_init.populate_pi_table_d', 25);
1727 hr_dm_utility.message('PARA','(none)', 30);
1728
1729 -- error handling
1730 EXCEPTION
1731 WHEN OTHERS THEN
1732 hr_dm_utility.error(SQLCODE,'hr_dm_init.populate_pi_table_d','(none)',
1733 'R');
1734 RAISE;
1735
1736 --
1737 END populate_pi_table_d;
1738 --
1739
1740 -- ------------------------- populate_pi_table_ua ------------------------
1741 -- Description: The phase items for the clean up phase are seeded
1742 -- into the hr_dm_phase_items. An entry is made for each aol loader within
1743 -- a group that is applicable for the current migration.
1744 --
1745 --
1746 -- Input Parameters
1747 -- r_migration_data - record containing migration information
1748 --
1749 --
1750 -- Output Parameters
1751 -- <none>
1752 --
1753 --
1754 -- ------------------------------------------------------------------------
1755
1756
1757
1758 --
1759 PROCEDURE populate_pi_table_c(r_migration_data IN
1760 hr_dm_utility.r_migration_rec) IS
1761 --
1762
1763 l_phase_id NUMBER;
1764 l_group_id NUMBER;
1765 l_loader_name VARCHAR2(30);
1766 l_loader_params_id NUMBER;
1767
1768 CURSOR csr_select_pi IS
1769 SELECT tbl.loader_name,
1770 lp.loader_params_id,
1771 grp.group_id
1772 FROM hr_dm_groups grp,
1773 hr_dm_application_groups apg,
1774 hr_dm_table_groupings tbg,
1775 hr_dm_tables tbl,
1776 hr_dm_loader_params lp
1777 WHERE (lp.table_id = tbl.table_id)
1778 AND (lp.application_id = r_migration_data.application_id)
1779 AND (tbl.table_id = tbg.table_id)
1780 AND (tbg.group_id = grp.group_id)
1781 AND (grp.group_type = 'C')
1782 AND (grp.group_id = apg.group_id)
1783 AND (apg.application_id = r_migration_data.application_id)
1784 AND (apg.migration_type = r_migration_data.migration_type)
1785 ORDER BY tbl.table_id;
1786
1787
1788 --
1789 BEGIN
1790 --
1791
1792 hr_dm_utility.message('ROUT','entry:hr_dm_init.populate_pi_table_c', 5);
1793 hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
1794
1795 l_phase_id := hr_dm_utility.get_phase_id('C',
1796 r_migration_data.migration_id);
1797
1798 OPEN csr_select_pi;
1799 LOOP
1800 FETCH csr_select_pi INTO l_loader_name, l_loader_params_id, l_group_id;
1801 EXIT WHEN csr_select_pi%NOTFOUND;
1802
1803 INSERT INTO hr_dm_phase_items (PHASE_ITEM_ID,
1804 PHASE_ID,
1805 LOADER_NAME,
1806 LOADER_PARAMS_ID,
1807 BATCH_ID,
1808 GROUP_ID,
1809 TABLE_NAME,
1810 STATUS,
1811 START_TIME,
1812 END_TIME,
1813 CREATED_BY,
1814 CREATION_DATE,
1815 LAST_UPDATED_BY,
1816 LAST_UPDATE_DATE,
1817 LAST_UPDATE_LOGIN)
1818 SELECT hr_dm_phase_items_s.nextval,
1819 l_phase_id,
1820 l_loader_name,
1821 l_loader_params_id,
1822 NULL,
1823 l_group_id,
1824 NULL,
1825 'NS',
1826 NULL,
1827 NULL,
1828 1,
1829 SYSDATE,
1830 1,
1831 SYSDATE,
1832 NULL
1833 FROM dual
1834 WHERE NOT EXISTS
1835 (SELECT NULL FROM hr_dm_phase_items
1836 WHERE ((phase_id = l_phase_id)
1837 AND (loader_name = l_loader_name)));
1838
1839 COMMIT;
1840
1841 hr_dm_utility.message('INFO','Seeding ' || l_loader_name, 11);
1842
1843 END LOOP;
1844 CLOSE csr_select_pi;
1845
1846
1847 hr_dm_utility.message('INFO','Populate Phase Items table - C phase', 15);
1848 hr_dm_utility.message('SUMM','Populate Phase Items table - C phase', 20);
1849 hr_dm_utility.message('ROUT','exit:hr_dm_init.populate_pi_table_c', 25);
1850 hr_dm_utility.message('PARA','(none)', 30);
1851
1852 -- error handling
1853 EXCEPTION
1854 WHEN OTHERS THEN
1855 hr_dm_utility.error(SQLCODE,'hr_dm_init.populate_pi_table_c','(none)'
1856 ,'R');
1857 RAISE;
1858
1859 --
1860 END populate_pi_table_c;
1861 --
1862
1863
1864 -- ------------------------- populate_pi_table ------------------------
1865 -- Description: The code to populate the current phase is called.
1866 --
1867 --
1868 -- Input Parameters
1869 -- r_migration_data - record containing migration information
1870 --
1871 -- p_phase_name - phase code
1872 --
1873 --
1874 -- Output Parameters
1875 -- <none>
1876 --
1877 --
1878 -- ------------------------------------------------------------------------
1879
1880
1881 --
1882 PROCEDURE populate_pi_table(r_migration_data IN
1883 hr_dm_utility.r_migration_rec,
1884 p_phase_name IN VARCHAR2) IS
1885 --
1886
1887 --
1888 BEGIN
1889 --
1890
1891 hr_dm_utility.message('ROUT','entry:hr_dm_init.populate_pi_table', 5);
1892 hr_dm_utility.message('PARA','(r_migration_data - record' ||
1893 ')(p_phase_name - ' || p_phase_name || ')', 10);
1894
1895 IF (p_phase_name = 'I') THEN
1896 populate_pi_table_i(r_migration_data);
1897 ELSIF (p_phase_name = 'G') THEN
1898 populate_pi_table_g(r_migration_data);
1899 ELSIF (p_phase_name = 'R') THEN
1900 populate_pi_table_r(r_migration_data);
1901 ELSIF (p_phase_name = 'DA') THEN
1902 populate_pi_table_da(r_migration_data);
1903 ELSIF (p_phase_name = 'DP') THEN
1904 populate_pi_table_dp(r_migration_data);
1905 ELSIF (p_phase_name = 'CP') THEN
1906 populate_pi_table_cp(r_migration_data);
1907 ELSIF (p_phase_name = 'UP') THEN
1908 populate_pi_table_up(r_migration_data);
1909 ELSIF (p_phase_name = 'UA') THEN
1910 populate_pi_table_ua(r_migration_data);
1911 ELSIF (p_phase_name = 'D') THEN
1912 populate_pi_table_d(r_migration_data);
1913 ELSIF (p_phase_name = 'C') THEN
1914 populate_pi_table_c(r_migration_data);
1915 END IF;
1916
1917 hr_dm_utility.message('INFO','Populate Phase Items table -' ||
1918 ' calling phase code', 15);
1919 hr_dm_utility.message('SUMM','Populate Phase Items table -' ||
1920 ' calling phase code', 20);
1921 hr_dm_utility.message('ROUT','exit:hr_dm_init.populate_pi_table', 25);
1922 hr_dm_utility.message('PARA','(none)', 30);
1923
1924 -- error handling
1925 EXCEPTION
1926 WHEN OTHERS THEN
1927 hr_dm_utility.error(SQLCODE,'hr_dm_init.populate_pi_table','(none)','R');
1928 RAISE;
1929
1930 --
1931 END populate_pi_table;
1932 --
1933
1934
1935 -- ------------------------- populate_phase_items ------------------------
1936 -- Description: The phases applicable to the current migration and the
1937 -- database location (ie source / destination) are populated by calling
1938 -- populate_pi_table.
1939 --
1940 --
1941 -- Input Parameters
1942 -- r_migration_data - record containing migration information
1943 --
1944 --
1945 -- Output Parameters
1946 -- <none>
1947 --
1948 --
1949 -- ------------------------------------------------------------------------
1950
1951 --
1952 PROCEDURE populate_phase_items(r_migration_data IN
1953 hr_dm_utility.r_migration_rec) IS
1954
1955 --
1956
1957 l_search_phase VARCHAR2(30);
1958 l_phase_name VARCHAR2(30);
1959 l_previous_phase VARCHAR2(30);
1960 l_next_phase VARCHAR2(30);
1961 l_database_location VARCHAR2(30);
1962
1963 CURSOR csr_phase_rule IS
1964 SELECT phase_name, previous_phase, next_phase,
1965 database_location
1966 FROM hr_dm_phase_rules
1967 WHERE ((migration_type = r_migration_data.migration_type)
1968 AND (previous_phase = l_search_phase));
1969
1970 --
1971 BEGIN
1972 --
1973
1974 hr_dm_utility.message('ROUT','entry:hr_dm_init.populate_phase_items', 5);
1975 hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
1976
1977 -- seed first phase
1978 l_search_phase := 'START';
1979 l_next_phase := '?';
1980
1981 WHILE (l_next_phase <> 'END') LOOP
1982 OPEN csr_phase_rule;
1983 FETCH csr_phase_rule INTO l_phase_name, l_previous_phase,
1984 l_next_phase, l_database_location;
1985 -- add check to exit if table is not seed/problem
1986 EXIT WHEN csr_phase_rule%NOTFOUND;
1987
1988 -- does it apply?
1989 IF (INSTR(l_database_location, r_migration_data.database_location) >0) THEN
1990 populate_pi_table(r_migration_data, l_phase_name);
1991 END IF;
1992 l_search_phase := l_phase_name;
1993 CLOSE csr_phase_rule;
1994 END LOOP;
1995
1996 hr_dm_utility.message('INFO','Populate Phase Items table', 15);
1997 hr_dm_utility.message('SUMM','Populate Phase Items table', 20);
1998 hr_dm_utility.message('ROUT','exit:hr_dm_init.populate_phase_items', 25);
1999 hr_dm_utility.message('PARA','(none)', 30);
2000
2001 -- error handling
2002 EXCEPTION
2003 WHEN OTHERS THEN
2004 hr_dm_utility.error(SQLCODE,'hr_dm_init.populate_phase_items',
2005 '(none)','R');
2006 RAISE;
2007
2008 --
2009 END populate_phase_items;
2010 --
2011
2012 -- ------------------------- populate_p_table ----------------------
2013 -- Description: The phases applicable to the current migration and the
2014 -- database location (ie source / destination) are seeded into the
2015 -- hr_dm_phases table.
2016 --
2017 --
2018 -- Input Parameters
2019 -- r_migration_data - record containing migration information
2020 --
2021 --
2022 -- Output Parameters
2023 -- <none>
2024 --
2025 --
2026 -- ------------------------------------------------------------------------
2027
2028 --
2029 PROCEDURE populate_p_table(r_migration_data IN hr_dm_utility.r_migration_rec,
2030 p_phase_name IN VARCHAR2) IS
2031 --
2032
2033 --
2034 BEGIN
2035 --
2036
2037 hr_dm_utility.message('ROUT','entry:hr_dm_init.populate_p_table', 5);
2038 hr_dm_utility.message('PARA','(r_migration_data - record' ||
2039 ')(p_phase_name - ' || p_phase_name || ')', 10);
2040
2041 INSERT INTO hr_dm_phases (PHASE_ID,
2042 MIGRATION_ID,
2043 PHASE_NAME,
2044 STATUS,
2045 START_TIME,
2046 END_TIME,
2047 CREATED_BY,
2048 CREATION_DATE,
2049 LAST_UPDATED_BY,
2050 LAST_UPDATE_DATE,
2051 LAST_UPDATE_LOGIN)
2052 SELECT hr_dm_phases_s.nextval,
2053 r_migration_data.migration_id,
2054 p_phase_name,
2055 'NS',
2056 NULL,
2057 NULL,
2058 1,
2059 SYSDATE,
2060 1,
2061 SYSDATE,
2062 NULL
2063 FROM dual
2064 WHERE NOT EXISTS
2065 (SELECT NULL FROM hr_dm_phases
2066 WHERE ((migration_id = r_migration_data.migration_id)
2067 AND (phase_name = p_phase_name)));
2068
2069 COMMIT;
2070
2071 hr_dm_utility.message('INFO','Populate Phases table', 15);
2072 hr_dm_utility.message('SUMM','Populate Phases table', 20);
2073 hr_dm_utility.message('ROUT','exit:hr_dm_init.populate_p_table', 25);
2074 hr_dm_utility.message('PARA','(none)', 30);
2075
2076 -- error handling
2077 EXCEPTION
2078 WHEN OTHERS THEN
2079 hr_dm_utility.error(SQLCODE,'hr_dm_init.populate_p_table','(none)','R');
2080 RAISE;
2081
2082 --
2083 END populate_p_table;
2084 --
2085
2086
2087 -- ------------------------- populate_populate_phases ----------------------
2088 -- Description: The phases applicable to the current migration and the
2089 -- database location (ie source / destination) are populated by calling
2090 -- populate_p_table.
2091 --
2092 --
2093 -- Input Parameters
2094 -- r_migration_data - record containing migration information
2095 --
2096 --
2097 -- Output Parameters
2098 -- <none>
2099 --
2100 --
2101 -- ------------------------------------------------------------------------
2102
2103 --
2104 PROCEDURE populate_phases(r_migration_data IN
2105 hr_dm_utility.r_migration_rec) IS
2106 --
2107
2108 l_phase_name VARCHAR2(30);
2109 l_database_location VARCHAR2(30);
2110
2111 CURSOR csr_phase_rule IS
2112 SELECT phase_name, database_location
2113 FROM hr_dm_phase_rules
2114 WHERE ((migration_type = r_migration_data.migration_type)
2115 AND (INSTR(database_location,
2116 r_migration_data.database_location) >0));
2117
2118 --
2119 BEGIN
2120 --
2121
2122 hr_dm_utility.message('ROUT','entry:hr_dm_init.populate_phases', 5);
2123 hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
2124
2125
2126 OPEN csr_phase_rule;
2127 LOOP
2128 FETCH csr_phase_rule INTO l_phase_name, l_database_location;
2129 EXIT WHEN csr_phase_rule%NOTFOUND;
2130 populate_p_table(r_migration_data, l_phase_name);
2131 END LOOP;
2132 CLOSE csr_phase_rule;
2133
2134 hr_dm_utility.message('INFO','Populate Phase Items table', 15);
2135 hr_dm_utility.message('SUMM','Populate Phase Items table', 20);
2136 hr_dm_utility.message('ROUT','exit:hr_dm_init.populate_phases', 25);
2137 hr_dm_utility.message('PARA','(none)', 30);
2138
2139 -- error handling
2140 EXCEPTION
2141 WHEN OTHERS THEN
2142 hr_dm_utility.error(SQLCODE,'hr_dm_init.populate_phases','(none)','R');
2143 RAISE;
2144
2145 --
2146 END populate_phases;
2147 --
2148
2149
2150
2151
2152 /*-------------------------- PUBLIC ROUTINES ---------------------------*/
2153
2154 -- ------------------------- main ----------------------
2155 -- Description: The phases and associated phase items that are applicable
2156 -- to the current migration and the database location (ie source /
2157 -- destination) are seeded.
2158 --
2159 --
2160 -- Input Parameters
2161 -- r_migration_data - record containing migration information
2162 --
2163 --
2164 -- Output Parameters
2165 -- <none>
2166 --
2167 --
2168 -- ------------------------------------------------------------------------
2169
2170 --
2171 PROCEDURE main(r_migration_data IN hr_dm_utility.r_migration_rec) IS
2172 --
2173
2174 l_current_phase_status VARCHAR2(30);
2175
2176 --
2177 BEGIN
2178 --
2179
2180 hr_dm_utility.message('ROUT','entry:hr_dm_init.main', 5);
2181 hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
2182
2183
2184 -- get status of initialization phase, is phase completed?
2185 -- if null returned, then assume it is NS.
2186 l_current_phase_status := NVL(hr_dm_utility.get_phase_status('I',
2187 r_migration_data.migration_id), 'NS');
2188
2189 -- is phase complete?
2190 -- if so, skip all processing
2191 IF (l_current_phase_status <> 'C') THEN
2192 -- do we need to explicitly rollback using rollback utility?
2193 IF (l_current_phase_status IN('S', 'E')) THEN
2194 hr_dm_utility.rollback(p_phase => 'I',
2195 p_migration_id => r_migration_data.migration_id);
2196 END IF;
2197
2198
2199 -- populate phases table
2200 populate_phases(r_migration_data);
2201
2202
2203 -- update status to started
2204 hr_dm_utility.update_phases(p_new_status => 'S',
2205 p_id => hr_dm_utility.get_phase_id('I',
2206 r_migration_data.migration_id));
2207
2208 -- populate phase_items table
2209 populate_phase_items(r_migration_data);
2210
2211 -- delete the contents of hr_dm_exp_imps table
2212 -- if we are on the source database only
2213 IF (r_migration_data.database_location = 'S') THEN
2214 DELETE hr_dm_exp_imps;
2215 COMMIT;
2216 END IF;
2217
2218 -- update status to completed
2219 hr_dm_utility.update_phases(p_new_status => 'C',
2220 p_id => hr_dm_utility.get_phase_id('I',
2221 r_migration_data.migration_id));
2222
2223 END IF;
2224
2225
2226 hr_dm_utility.message('INFO','Initialization Phase', 15);
2227 hr_dm_utility.message('SUMM','Initialization Phase', 20);
2228 hr_dm_utility.message('ROUT','exit:hr_dm_init.main', 25);
2229 hr_dm_utility.message('PARA','(none)', 30);
2230
2231 -- error handling
2232 EXCEPTION
2233 WHEN OTHERS THEN
2234 hr_dm_utility.error(SQLCODE,'hr_dm_init.main','(none)','R');
2235 RAISE;
2236
2237 --
2238 END main;
2239 --
2240
2241
2242
2243 END hr_dm_init;