[Home] [Help]
PACKAGE BODY: APPS.HR_DM_COPY
Source
4 --
1 package body hr_dm_copy as
2 /* $Header: perdmcp.pkb 115.18 2002/03/07 08:51:10 pkm ship $ */
3
5 ---------------------------- get_schema -----------------------------
6 -- This function identifies the scema name used for the passed product
7 -- Input Parameters
8 -- p_product - product
9 --
10 --
11 -- Output Parameters
12 -- <none>
13 --
14 --
15 -- Return Value
16 -- schema name for passed product
17
18 ---------------------------------------------------------------------
19 --
20 FUNCTION get_schema(p_product IN VARCHAR2) RETURN VARCHAR2 IS
21 --
22
23 l_value BOOLEAN;
24 l_out_status VARCHAR2(30);
25 l_out_industry VARCHAR2(30);
26 l_out_oracle_schema VARCHAR2(30);
27
28
29 --
30 BEGIN
31 --
32
33 l_value := FND_INSTALLATION.GET_APP_INFO (p_product, l_out_status,
34 l_out_industry, l_out_oracle_schema);
35
36
37 RETURN(l_out_oracle_schema);
38
39 -- error handling
40 EXCEPTION
41 WHEN OTHERS THEN
42 RAISE;
43
44 --
45 END get_schema;
46 --
47
48
49
50 --
51 ----------------------- delete_datapump_tables ----------------------
52 -- This procedure truncates the following datapump tables
53 -- hr_pump_batch_headers
54 -- hr_pump_batch_lines
55 -- hr_pump_requests
56 -- hr_pump_ranges
57 -- hr_pump_batch_exceptions
58 -- hr_pump_batch_line_user_keys
59 ---------------------------------------------------------------------
60 procedure delete_datapump_tables is
61
62 l_schema varchar2(30);
63
64 begin
65
66 hr_dm_utility.message('ROUT','entry:hr_dm_copy.delete_datapump_tables', 5);
67
68 -- get schema for PER
69 l_schema := get_schema('PER');
70
71
72 -- disable foreign keys so we can do the truncates
73 -- hr_pump_batch_lines
74 begin
75 execute immediate 'alter table ' || l_schema ||
76 '.HR_PUMP_BATCH_LINE_USER_KEYS ' ||
77 ' disable constraint HR_PUMP_BATCH_LINE_USER_KE_FK1';
78 exception
79 when others then
80 hr_dm_utility.message('INFO',
81 'Problem with constraint HR_PUMP_BATCH_LINE_USER_KE_FK1 - disable', 5);
82 end;
83 -- hr_pump_batch_headers
84 begin
85 execute immediate 'alter table ' || l_schema ||
86 '.HR_PUMP_BATCH_LINES ' ||
87 ' disable constraint HR_PUMP_BATCH_LINES_FK1';
88 exception
89 when others then
90 hr_dm_utility.message('INFO',
91 'Problem with constraint HR_PUMP_BATCH_LINES_FK1 - disable', 5);
92 end;
93 begin
94 execute immediate 'alter table ' || l_schema ||
95 '.HR_PUMP_RANGES ' ||
96 ' disable constraint HR_PUMP_RANGES_FK1';
97 exception
98 when others then
99 hr_dm_utility.message('INFO',
100 'Problem with constraint HR_PUMP_RANGES_FK1 - disable', 5);
101 end;
102 begin
103 execute immediate 'alter table ' || l_schema ||
104 '.HR_PUMP_REQUESTS ' ||
105 ' disable constraint HR_PUMP_REQUESTS_FK1';
106 exception
107 when others then
108 hr_dm_utility.message('INFO',
109 'Problem with constraint HR_PUMP_REQUESTS_FK1 - disable', 5);
110 end;
111
112
113
114 -- truncate datapump tables. call run_sql procedure to run
115 -- 'truncate ddl' command.
116 hr_dm_utility.message('INFO','Truncating table hr_pump_requests',10);
117 hr_dm_library.run_sql('truncate table ' || l_schema ||
118 '.hr_pump_requests');
119
120 hr_dm_utility.message('INFO','Truncating table hr_pump_ranges',15);
121 hr_dm_library.run_sql('truncate table ' || l_schema ||
122 '.hr_pump_ranges');
123
124 hr_dm_utility.message('INFO',
125 'Truncating table hr_pump_batch_exceptions',20);
126 hr_dm_library.run_sql('truncate table ' || l_schema ||
127 '.hr_pump_batch_exceptions');
128
129 hr_dm_utility.message('INFO',
130 'Truncating table hr_pump_batch_line_user_keys',25);
131 hr_dm_library.run_sql('truncate table ' || l_schema ||
132 '.hr_pump_batch_line_user_keys');
133
134 hr_dm_utility.message('INFO','Truncating table hr_pump_batch_lines',26);
135 hr_dm_library.run_sql('truncate table ' || l_schema ||
136 '.hr_pump_batch_lines');
137
138 hr_dm_utility.message('INFO',
139 'Truncating table hr_pump_batch_headers',30);
140 hr_dm_library.run_sql('truncate table ' || l_schema ||
141 '.hr_pump_batch_headers');
142
143
144 -- enable foreign keys so we can do the truncates
145 -- hr_pump_batch_lines
146 -- disable foreign keys so we can do the truncates
147 -- hr_pump_batch_lines
148 begin
149 execute immediate 'alter table ' || l_schema ||
150 '.HR_PUMP_BATCH_LINE_USER_KEYS ' ||
151 ' enable constraint HR_PUMP_BATCH_LINE_USER_KE_FK1';
152 exception
153 when others then
154 hr_dm_utility.message('INFO',
155 'Problem with constraint HR_PUMP_BATCH_LINE_USER_KE_FK1 - enable', 5);
156 end;
157 -- hr_pump_batch_headers
158 begin
159 execute immediate 'alter table ' || l_schema ||
160 '.HR_PUMP_BATCH_LINES ' ||
161 ' enable constraint HR_PUMP_BATCH_LINES_FK1';
162 exception
163 when others then
167 begin
164 hr_dm_utility.message('INFO',
165 'Problem with constraint HR_PUMP_BATCH_LINES_FK1 - enable', 5);
166 end;
168 execute immediate 'alter table ' || l_schema ||
169 '.HR_PUMP_RANGES ' ||
170 ' enable constraint HR_PUMP_RANGES_FK1';
171 exception
172 when others then
173 hr_dm_utility.message('INFO',
174 'Problem with constraint HR_PUMP_RANGES_FK1 - enable', 5);
175 end;
176 begin
177 execute immediate 'alter table ' || l_schema ||
178 '.HR_PUMP_REQUESTS ' ||
179 ' enable constraint HR_PUMP_REQUESTS_FK1';
180 exception
181 when others then
182 hr_dm_utility.message('INFO',
183 'Problem with constraint HR_PUMP_REQUESTS_FK1 - enable', 5);
184 end;
185
186
187 hr_dm_utility.message('ROUT','exit:hr_dm_copy.delete_datapump_tables', 35);
188
189
190
191 exception
192 when others then
193 hr_dm_utility.error(SQLCODE,'hr_dm_copy.delete_datapump_tables',
194 '(none)','R');
195 raise;
196 end delete_datapump_tables;
197
198
199 ----------------------- source_copy ---------------------------------
200 -- This procedure does some of the tasks of Copy phase in source
201 -- database. It does the following :
202 -- o Insert the data migrator packages rows from HR_API_MODULES
203 -- table into HR_DM_EXP_API_MODULES_V view based on
204 -- HR_DM_EXP_IMPS table.
205 -- o Insert the current migration row from HR_DM_MIGRATIONS tables
206 -- into HR_DM_EXP_MIGRATIONS_V view based on HR_DM_EXP_IMPS table
207 -- o Inserts the rows for the business_group_id being copied
208 -- from HR_ALL_ORGANIZATION_UNITS, HR_ORGANIZATION_INFORMATION,
209 -- HR_ALL_ORGANIZATION_UNITS_TL, HR_LOCATIONS_ALL and
210 -- HR_LOCATIONS_ALL_TL into HR_DM_EXP_IMPS for a FW migration
211 -- o Copy the values for the ID_FLEX_STRUCTURE_NAME from the table
212 -- HR_ORGANIZATION_INFORMATION into HR_DM_EXP_IMPS for a FW migration
213 -- Input Parameters :
214 -- p_migration_id - Migration Id of the current migration. Primary
215 -- key on hr_dm_migrations table.
216 -- p_last_migration_date - last migration date
217 -- Called By : Main controller in source database
218 ---------------------------------------------------------------------
219 procedure source_copy (p_migration_id number,
220 p_last_migration_date date) is
221
222 l_business_group_id number;
223 l_migration_type varchar2(30);
224 l_org_information4 varchar2(30);
225 l_org_information5 varchar2(30);
226 l_org_information6 varchar2(30);
227 l_org_information7 varchar2(30);
228 l_org_information8 varchar2(30);
229 l_org_information14 varchar2(30);
230 l_up_phase_used varchar2(30);
231
232
233 cursor csr_mig_info is
234 select business_group_id,
235 migration_type
236 from hr_dm_migrations
237 where migration_id = p_migration_id;
238
239 CURSOR csr_phase_rule IS
240 SELECT pr.phase_name
241 FROM hr_dm_phase_rules pr,
242 hr_dm_migrations m
243 WHERE m.migration_type = pr.migration_type
244 AND pr.phase_name = 'UP'
245 AND m.migration_id = p_migration_id;
246
247
248
249 begin
250 hr_dm_utility.message('ROUT','entry:hr_dm_copy.source_copy', 5);
251 hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
252 ')', 10);
253
254
255 -- insert data migrator packages rows from HR_API_MODULES i.e
256 -- where API_MODULE_TYPE = 'DM' into HR_DM_EXP_API_MODULES_V.
257 -- only when datapump will be used on the destination
258
259 open csr_phase_rule;
260 fetch csr_phase_rule into l_up_phase_used;
261 close csr_phase_rule;
262
263 if (l_up_phase_used = 'UP') then
264 hr_dm_utility.message('INFO','Inserting row into hr_dm_exp_api_modules_v',15);
265 insert into hr_dm_exp_api_modules_v (exp_imp_id
266 ,table_name
267 ,api_module_id
268 ,api_module_type
269 ,module_name
270 ,data_within_business_group
271 ,legislation_code
272 ,module_package
273 ,last_update_date
274 ,last_updated_by
275 ,last_update_login
276 ,created_by
277 ,creation_date )
278 select hr_dm_exp_imps_s.nextval
279 ,'HR_API_MODULES'
280 ,api_module_id
281 ,api_module_type
282 ,module_name
283 ,data_within_business_group
284 ,legislation_code
285 ,module_package
286 ,to_char(last_update_date,'YYYYMMDD HH24:MI:SS')
287 ,last_updated_by
288 ,last_update_login
289 ,created_by
290 ,to_char(creation_date,'YYYYMMDD HH24:MI:SS')
291 from hr_api_modules ai
292 where api_module_type = 'DM'
293 and not exists (select null
294 from hr_dm_exp_api_modules_v v
298
295 where v.api_module_id = ai.api_module_id);
296 end if;
297
299 -- Insert the current migration row from HR_DM_MIGRATIONS tables
300 -- into HR_DM_EXP_MIGRATIONS_V view based on HR_DM_EXP_IMPS table
301
302 hr_dm_utility.message('INFO','Inserting row into hr_dm_exp_migrations_v',20);
303 insert into hr_dm_exp_migrations_v ( exp_imp_id
304 ,table_name
305 ,migration_id
306 ,source_database_instance
307 ,destination_database_instance
308 ,migration_type
309 ,application_id
310 ,business_group_id
311 ,business_group_name
312 ,migration_start_date
313 ,migration_end_date
314 ,status
315 ,effective_date
316 ,migration_count
317 ,selective_migration_criteria
318 ,active_group
319 ,last_update_date
320 ,last_updated_by
321 ,last_update_login
322 ,created_by
323 ,creation_date )
324 select hr_dm_exp_imps_s.nextval
325 ,'HR_DM_MIGRATIONS'
326 ,migration_id
327 ,source_database_instance
328 ,destination_database_instance
329 ,migration_type
330 ,application_id
331 ,business_group_id
332 ,business_group_name
333 ,migration_start_date
334 ,migration_end_date
335 ,'NS'
336 ,effective_date
337 ,migration_count
338 ,selective_migration_criteria
339 ,active_group
340 ,to_char(last_update_date,'YYYYMMDD HH24:MI:SS')
341 ,last_updated_by
342 ,last_update_login
343 ,created_by
344 ,to_char(creation_date,'YYYYMMDD HH24:MI:SS')
345 from hr_dm_migrations dm
346 where migration_id = p_migration_id
347 and not exists (select null
348 from hr_dm_exp_migrations_v v
349 where v.migration_id = dm.migration_id);
350
351
352 -- find the business_group_id and migration type for the current migration
353 open csr_mig_info;
354 fetch csr_mig_info into l_business_group_id, l_migration_type;
355 close csr_mig_info;
356
357 -- only perform for an FW migration
358
359 if (l_migration_type = 'FW') then
360
361 hr_dm_utility.message('INFO','Inserting row(s) into HR_DM_EXP_HR_LOC_ALL_V',15);
362 insert into HR_DM_EXP_HR_LOC_ALL_V (
363 EXP_IMP_ID,
364 TABLE_NAME,
365 LOCATION_ID,
366 LOCATION_CODE,
367 BUSINESS_GROUP_ID,
368 DESCRIPTION,
369 SHIP_TO_LOCATION_ID,
370 SHIP_TO_SITE_FLAG,
371 RECEIVING_SITE_FLAG,
372 BILL_TO_SITE_FLAG,
373 IN_ORGANIZATION_FLAG,
374 OFFICE_SITE_FLAG,
375 DESIGNATED_RECEIVER_ID,
376 INVENTORY_ORGANIZATION_ID,
377 TAX_NAME,
378 INACTIVE_DATE,
379 STYLE,
380 ADDRESS_LINE_1,
381 ADDRESS_LINE_2,
382 ADDRESS_LINE_3,
383 TOWN_OR_CITY,
384 COUNTRY,
385 POSTAL_CODE,
386 REGION_1,
387 REGION_2,
388 REGION_3,
389 TELEPHONE_NUMBER_1,
390 TELEPHONE_NUMBER_2,
391 TELEPHONE_NUMBER_3,
392 LOC_INFORMATION13,
393 LOC_INFORMATION14,
394 LOC_INFORMATION15,
395 LOC_INFORMATION16,
396 LOC_INFORMATION17,
397 ATTRIBUTE_CATEGORY,
398 ATTRIBUTE1,
399 ATTRIBUTE2,
400 ATTRIBUTE3,
401 ATTRIBUTE4,
402 ATTRIBUTE5,
403 ATTRIBUTE6,
404 ATTRIBUTE7,
405 ATTRIBUTE8,
406 ATTRIBUTE9,
407 ATTRIBUTE10,
408 ATTRIBUTE11,
409 ATTRIBUTE12,
410 ATTRIBUTE13,
411 ATTRIBUTE14,
412 ATTRIBUTE15,
413 ATTRIBUTE16,
414 ATTRIBUTE17,
415 ATTRIBUTE18,
416 ATTRIBUTE19,
417 ATTRIBUTE20,
418 GLOBAL_ATTRIBUTE_CATEGORY,
419 GLOBAL_ATTRIBUTE1,
420 GLOBAL_ATTRIBUTE2,
421 GLOBAL_ATTRIBUTE3,
422 GLOBAL_ATTRIBUTE4,
423 GLOBAL_ATTRIBUTE5,
424 GLOBAL_ATTRIBUTE6,
425 GLOBAL_ATTRIBUTE7,
426 GLOBAL_ATTRIBUTE8,
427 GLOBAL_ATTRIBUTE9,
428 GLOBAL_ATTRIBUTE10,
429 GLOBAL_ATTRIBUTE11,
430 GLOBAL_ATTRIBUTE12,
431 GLOBAL_ATTRIBUTE13,
432 GLOBAL_ATTRIBUTE14,
433 GLOBAL_ATTRIBUTE15,
434 GLOBAL_ATTRIBUTE16,
435 GLOBAL_ATTRIBUTE17,
436 GLOBAL_ATTRIBUTE18,
440 LAST_UPDATED_BY,
437 GLOBAL_ATTRIBUTE19,
438 GLOBAL_ATTRIBUTE20,
439 LAST_UPDATE_DATE,
441 LAST_UPDATE_LOGIN,
442 CREATED_BY,
443 CREATION_DATE,
444 ENTERED_BY,
445 TP_HEADER_ID,
446 ECE_TP_LOCATION_CODE,
447 OBJECT_VERSION_NUMBER)
448 select
449 hr_dm_exp_imps_s.nextval,
450 'HR_LOCATIONS_ALL',
451 LOCATION_ID,
452 LOCATION_CODE,
453 BUSINESS_GROUP_ID,
454 DESCRIPTION,
455 SHIP_TO_LOCATION_ID,
456 SHIP_TO_SITE_FLAG,
457 RECEIVING_SITE_FLAG,
458 BILL_TO_SITE_FLAG,
459 IN_ORGANIZATION_FLAG,
460 OFFICE_SITE_FLAG,
461 DESIGNATED_RECEIVER_ID,
462 INVENTORY_ORGANIZATION_ID,
463 TAX_NAME,
464 to_char(INACTIVE_DATE,'YYYYMMDD HH24:MI:SS'),
465 STYLE,
466 ADDRESS_LINE_1,
467 ADDRESS_LINE_2,
468 ADDRESS_LINE_3,
469 TOWN_OR_CITY,
470 COUNTRY,
471 POSTAL_CODE,
472 REGION_1,
473 REGION_2,
474 REGION_3,
475 TELEPHONE_NUMBER_1,
476 TELEPHONE_NUMBER_2,
477 TELEPHONE_NUMBER_3,
478 LOC_INFORMATION13,
479 LOC_INFORMATION14,
480 LOC_INFORMATION15,
481 LOC_INFORMATION16,
482 LOC_INFORMATION17,
483 ATTRIBUTE_CATEGORY,
484 ATTRIBUTE1,
485 ATTRIBUTE2,
486 ATTRIBUTE3,
487 ATTRIBUTE4,
488 ATTRIBUTE5,
489 ATTRIBUTE6,
490 ATTRIBUTE7,
491 ATTRIBUTE8,
492 ATTRIBUTE9,
493 ATTRIBUTE10,
494 ATTRIBUTE11,
495 ATTRIBUTE12,
496 ATTRIBUTE13,
497 ATTRIBUTE14,
498 ATTRIBUTE15,
499 ATTRIBUTE16,
500 ATTRIBUTE17,
501 ATTRIBUTE18,
502 ATTRIBUTE19,
503 ATTRIBUTE20,
504 GLOBAL_ATTRIBUTE_CATEGORY,
505 GLOBAL_ATTRIBUTE1,
506 GLOBAL_ATTRIBUTE2,
507 GLOBAL_ATTRIBUTE3,
508 GLOBAL_ATTRIBUTE4,
509 GLOBAL_ATTRIBUTE5,
510 GLOBAL_ATTRIBUTE6,
511 GLOBAL_ATTRIBUTE7,
512 GLOBAL_ATTRIBUTE8,
513 GLOBAL_ATTRIBUTE9,
514 GLOBAL_ATTRIBUTE10,
515 GLOBAL_ATTRIBUTE11,
516 GLOBAL_ATTRIBUTE12,
517 GLOBAL_ATTRIBUTE13,
518 GLOBAL_ATTRIBUTE14,
519 GLOBAL_ATTRIBUTE15,
520 GLOBAL_ATTRIBUTE16,
521 GLOBAL_ATTRIBUTE17,
522 GLOBAL_ATTRIBUTE18,
523 GLOBAL_ATTRIBUTE19,
524 GLOBAL_ATTRIBUTE20,
525 to_char(last_update_date,'YYYYMMDD HH24:MI:SS'),
526 LAST_UPDATED_BY,
527 LAST_UPDATE_LOGIN,
528 CREATED_BY,
529 to_char(creation_date,'YYYYMMDD HH24:MI:SS'),
530 ENTERED_BY,
531 TP_HEADER_ID,
532 ECE_TP_LOCATION_CODE,
533 OBJECT_VERSION_NUMBER
534 from HR_LOCATIONS_ALL
535 where BUSINESS_GROUP_ID = l_business_group_id
536 or BUSINESS_GROUP_ID is null;
537
538 -- remove entries for HR_LOCATIONS_ALL in the table hr_pump_batch lines
539 -- that match the where clause used to migrate via HR_DM_EXP_IMPS table
540 -- Note that the view already matches the where clause
541 hr_dm_utility.message('INFO','Removing HR_LOCATIONS_ALL rows from hr_pump_batch_lines',15);
542 -- use dynamic sql to avoid compiliation errors where the data pump views
543 -- have not yet been created
544 execute immediate 'delete HRDPV_UHR_LOCATIONS_ALL';
545
546 hr_dm_utility.message('INFO','Inserting row(s) into HR_DM_EXP_HR_LOC_ALL_TL_V',15);
547 insert into HR_DM_EXP_HR_LOC_ALL_TL_V (
548 EXP_IMP_ID,
549 TABLE_NAME,
550 LOCATION_ID,
551 LANGUAGE,
552 SOURCE_LANG,
553 LOCATION_CODE,
554 DESCRIPTION,
555 LAST_UPDATE_DATE,
556 LAST_UPDATED_BY,
557 LAST_UPDATE_LOGIN,
558 CREATED_BY,
559 CREATION_DATE)
560 select
561 hr_dm_exp_imps_s.nextval,
562 'HR_LOCATIONS_ALL_TL',
563 LOCATION_ID,
564 LANGUAGE,
565 SOURCE_LANG,
566 LOCATION_CODE,
567 DESCRIPTION,
568 to_char(last_update_date,'YYYYMMDD HH24:MI:SS'),
569 LAST_UPDATED_BY,
570 LAST_UPDATE_LOGIN,
571 CREATED_BY,
572 to_char(creation_date,'YYYYMMDD HH24:MI:SS')
573 from HR_LOCATIONS_ALL_TL
574 where LOCATION_ID in (
575 select LOCATION_ID
576 from HR_LOCATIONS_ALL
577 where BUSINESS_GROUP_ID = l_business_group_id)
578 or LOCATION_ID in (
579 select LOCATION_ID
580 from HR_LOCATIONS_ALL
581 where BUSINESS_GROUP_ID is null);
582
583 -- remove entries for HR_LOCATIONS_ALL_TL in the table hr_pump_batch lines
584 -- that match the where clause used to migrate via HR_DM_EXP_IMPS table
585 hr_dm_utility.message('INFO','Removing HR_LOCATIONS_ALL_TL rows from hr_pump_batch_lines',15);
586 -- use dynamic sql to avoid compiliation errors where the data pump views
587 -- have not yet been created
588 execute immediate 'delete HRDPV_UHR_LOCATIONS_ALL_TL ' ||
589 'where p_LOCATION_ID in ( ' ||
590 ' select to_char(LOCATION_ID) ' ||
591 ' from HR_LOCATIONS_ALL ' ||
592 ' where BUSINESS_GROUP_ID = ' ||
593 l_business_group_id || ')';
594 execute immediate 'delete HRDPV_UHR_LOCATIONS_ALL_TL ' ||
595 'where p_LOCATION_ID in ( ' ||
596 ' select to_char(LOCATION_ID) ' ||
597 ' from HR_LOCATIONS_ALL ' ||
601 hr_dm_utility.message('INFO','Inserting row(s) into HR_DM_EXP_ALL_ORG_UNITS_V',15);
598 ' where BUSINESS_GROUP_ID is null' || ')';
599
600
602 -- the comments column has been removed as it is a long data type
603 insert into HR_DM_EXP_ALL_ORG_UNITS_V (
604 EXP_IMP_ID,
605 TABLE_NAME,
606 ORGANIZATION_ID,
607 BUSINESS_GROUP_ID,
608 COST_ALLOCATION_KEYFLEX_ID,
609 LOCATION_ID,
610 SOFT_CODING_KEYFLEX_ID,
611 DATE_FROM,
612 NAME,
613 DATE_TO,
614 INTERNAL_EXTERNAL_FLAG,
615 INTERNAL_ADDRESS_LINE,
616 TYPE,
617 REQUEST_ID,
618 PROGRAM_APPLICATION_ID,
619 PROGRAM_ID,
620 PROGRAM_UPDATE_DATE,
621 ATTRIBUTE_CATEGORY,
622 ATTRIBUTE1,
623 ATTRIBUTE2,
624 ATTRIBUTE3,
625 ATTRIBUTE4,
626 ATTRIBUTE5,
627 ATTRIBUTE6,
628 ATTRIBUTE7,
629 ATTRIBUTE8,
630 ATTRIBUTE9,
631 ATTRIBUTE10,
632 ATTRIBUTE11,
633 ATTRIBUTE12,
634 ATTRIBUTE13,
635 ATTRIBUTE14,
636 ATTRIBUTE15,
637 ATTRIBUTE16,
638 ATTRIBUTE17,
639 ATTRIBUTE18,
640 ATTRIBUTE19,
641 ATTRIBUTE20,
642 LAST_UPDATE_DATE,
643 LAST_UPDATED_BY,
644 LAST_UPDATE_LOGIN,
645 CREATED_BY,
646 CREATION_DATE)
647 select
648 hr_dm_exp_imps_s.nextval,
649 'HR_ALL_ORGANIZATION_UNITS',
650 ORGANIZATION_ID,
651 BUSINESS_GROUP_ID,
652 COST_ALLOCATION_KEYFLEX_ID,
653 LOCATION_ID,
654 SOFT_CODING_KEYFLEX_ID,
655 to_char(DATE_FROM,'YYYYMMDD HH24:MI:SS'),
656 NAME,
657 to_char(DATE_TO,'YYYYMMDD HH24:MI:SS'),
658 INTERNAL_EXTERNAL_FLAG,
659 INTERNAL_ADDRESS_LINE,
660 TYPE,
661 REQUEST_ID,
662 PROGRAM_APPLICATION_ID,
663 PROGRAM_ID,
664 to_char(PROGRAM_UPDATE_DATE,'YYYYMMDD HH24:MI:SS'),
665 ATTRIBUTE_CATEGORY,
666 ATTRIBUTE1,
667 ATTRIBUTE2,
668 ATTRIBUTE3,
669 ATTRIBUTE4,
670 ATTRIBUTE5,
671 ATTRIBUTE6,
672 ATTRIBUTE7,
673 ATTRIBUTE8,
674 ATTRIBUTE9,
675 ATTRIBUTE10,
676 ATTRIBUTE11,
677 ATTRIBUTE12,
678 ATTRIBUTE13,
679 ATTRIBUTE14,
680 ATTRIBUTE15,
681 ATTRIBUTE16,
682 ATTRIBUTE17,
683 ATTRIBUTE18,
684 ATTRIBUTE19,
685 ATTRIBUTE20,
686 to_char(last_update_date,'YYYYMMDD HH24:MI:SS'),
687 last_updated_by,
688 last_update_login,
689 created_by,
690 to_char(creation_date,'YYYYMMDD HH24:MI:SS')
691 from HR_ALL_ORGANIZATION_UNITS
692 where BUSINESS_GROUP_ID = l_business_group_id;
693
694 -- remove entries for HR_ALL_ORGANIZATION_UNITS in the table hr_pump_batch lines
695 -- that match the where clause used to migrate via HR_DM_EXP_IMPS table
696 -- Note that the view already matches the where clause
697 hr_dm_utility.message('INFO','Removing HR_ALL_ORGANIZATION_UNITS rows from hr_pump_batch_lines',15);
698 -- use dynamic sql to avoid compiliation errors where the data pump views
699 -- have not yet been created
700 execute immediate 'delete HRDPV_UHR_ALL_ORGANIZATONUNITS';
701
702 hr_dm_utility.message('INFO','Inserting row(s) into HR_DM_EXP_ORG_INFO_V',15);
703 insert into HR_DM_EXP_ORG_INFO_V (
704 EXP_IMP_ID,
705 TABLE_NAME,
706 ORG_INFORMATION_ID,
707 ORG_INFORMATION_CONTEXT,
708 ORGANIZATION_ID,
709 ORG_INFORMATION1,
710 ORG_INFORMATION10,
711 ORG_INFORMATION11,
712 ORG_INFORMATION12,
713 ORG_INFORMATION13,
714 ORG_INFORMATION14,
715 ORG_INFORMATION15,
716 ORG_INFORMATION16,
717 ORG_INFORMATION17,
718 ORG_INFORMATION18,
719 ORG_INFORMATION19,
720 ORG_INFORMATION2,
721 ORG_INFORMATION20,
722 ORG_INFORMATION3,
723 ORG_INFORMATION4,
724 ORG_INFORMATION5,
725 ORG_INFORMATION6,
726 ORG_INFORMATION7,
727 ORG_INFORMATION8,
728 ORG_INFORMATION9,
729 REQUEST_ID,
730 PROGRAM_APPLICATION_ID,
731 PROGRAM_ID,
732 PROGRAM_UPDATE_DATE,
733 ATTRIBUTE_CATEGORY,
734 ATTRIBUTE1,
735 ATTRIBUTE2,
736 ATTRIBUTE3,
737 ATTRIBUTE4,
738 ATTRIBUTE5,
739 ATTRIBUTE6,
740 ATTRIBUTE7,
741 ATTRIBUTE8,
742 ATTRIBUTE9,
743 ATTRIBUTE10,
744 ATTRIBUTE11,
745 ATTRIBUTE12,
746 ATTRIBUTE13,
747 ATTRIBUTE14,
748 ATTRIBUTE15,
749 ATTRIBUTE16,
750 ATTRIBUTE17,
751 ATTRIBUTE18,
752 ATTRIBUTE19,
753 ATTRIBUTE20,
754 LAST_UPDATE_DATE,
755 LAST_UPDATED_BY,
756 LAST_UPDATE_LOGIN,
757 CREATED_BY,
758 CREATION_DATE)
759 select
760 hr_dm_exp_imps_s.nextval,
761 'HR_ORGANIZATION_INFORMATION',
762 ORG_INFORMATION_ID,
763 ORG_INFORMATION_CONTEXT,
764 ORGANIZATION_ID,
765 ORG_INFORMATION1,
766 ORG_INFORMATION10,
767 ORG_INFORMATION11,
768 ORG_INFORMATION12,
769 ORG_INFORMATION13,
770 ORG_INFORMATION14,
771 ORG_INFORMATION15,
772 ORG_INFORMATION16,
773 ORG_INFORMATION17,
774 ORG_INFORMATION18,
778 ORG_INFORMATION3,
775 ORG_INFORMATION19,
776 ORG_INFORMATION2,
777 ORG_INFORMATION20,
779 ORG_INFORMATION4,
780 ORG_INFORMATION5,
781 ORG_INFORMATION6,
782 ORG_INFORMATION7,
783 ORG_INFORMATION8,
784 ORG_INFORMATION9,
785 REQUEST_ID,
786 PROGRAM_APPLICATION_ID,
787 PROGRAM_ID,
788 to_char(PROGRAM_UPDATE_DATE,'YYYYMMDD HH24:MI:SS'),
789 ATTRIBUTE_CATEGORY,
790 ATTRIBUTE1,
791 ATTRIBUTE2,
792 ATTRIBUTE3,
793 ATTRIBUTE4,
794 ATTRIBUTE5,
795 ATTRIBUTE6,
796 ATTRIBUTE7,
797 ATTRIBUTE8,
798 ATTRIBUTE9,
799 ATTRIBUTE10,
800 ATTRIBUTE11,
801 ATTRIBUTE12,
802 ATTRIBUTE13,
803 ATTRIBUTE14,
804 ATTRIBUTE15,
805 ATTRIBUTE16,
806 ATTRIBUTE17,
807 ATTRIBUTE18,
808 ATTRIBUTE19,
809 ATTRIBUTE20,
810 to_char(last_update_date,'YYYYMMDD HH24:MI:SS'),
811 last_updated_by,
812 last_update_login,
813 created_by,
814 to_char(creation_date,'YYYYMMDD HH24:MI:SS')
815 from HR_ORGANIZATION_INFORMATION
816 where ORGANIZATION_ID in (
817 select ORGANIZATION_ID
818 from HR_ALL_ORGANIZATION_UNITS
819 where BUSINESS_GROUP_ID = l_business_group_id);
820
821 -- remove entries for HR_ORGANIZATION_INFORMATION in the table hr_pump_batch lines
822 -- that match the where clause used to migrate via HR_DM_EXP_IMPS table
823 hr_dm_utility.message('INFO','Removing HR_ALL_ORGANIZATION_UNITS rows from hr_pump_batch_lines',15);
824 -- use dynamic sql to avoid compiliation errors where the data pump views
825 -- have not yet been created
826 execute immediate 'delete HRDPV_UHR_ORGANIZATNINFORMATON ' ||
827 ' where p_ORGANIZATION_ID in ( ' ||
828 ' select to_char(ORGANIZATION_ID) ' ||
829 ' from HR_ALL_ORGANIZATION_UNITS ' ||
830 ' where BUSINESS_GROUP_ID = ' ||
831 l_business_group_id || ')';
832
833 hr_dm_utility.message('INFO','Inserting row(s) into HR_DM_EXP_ALL_ORG_UNITS_TL_V',15);
834 insert into HR_DM_EXP_ALL_ORG_UNITS_TL_V (
835 EXP_IMP_ID,
836 TABLE_NAME,
837 ORGANIZATION_ID,
838 LANGUAGE,
839 SOURCE_LANG,
840 NAME,
841 LAST_UPDATE_DATE,
842 LAST_UPDATED_BY,
843 LAST_UPDATE_LOGIN,
844 CREATED_BY,
845 CREATION_DATE)
846 select
847 hr_dm_exp_imps_s.nextval,
848 'HR_ALL_ORGANIZATION_UNITS_TL',
849 ORGANIZATION_ID,
850 LANGUAGE,
851 SOURCE_LANG,
852 NAME,
853 to_char(last_update_date,'YYYYMMDD HH24:MI:SS'),
854 last_updated_by,
855 last_update_login,
856 created_by,
857 to_char(creation_date,'YYYYMMDD HH24:MI:SS')
858 from HR_ALL_ORGANIZATION_UNITS_TL
859 where ORGANIZATION_ID in (
860 select ORGANIZATION_ID
861 from HR_ALL_ORGANIZATION_UNITS
862 where BUSINESS_GROUP_ID = l_business_group_id);
863
864 -- remove entries for HR_ALL_ORGANIZATION_UNITS_TL in the table hr_pump_batch lines
865 -- that match the where clause used to migrate via HR_DM_EXP_IMPS table
866 hr_dm_utility.message('INFO','Removing HR_ALL_ORGANIZATION_UNITS_TL rows from hr_pump_batch_lines',15);
867 -- use dynamic sql to avoid compiliation errors where the data pump views
868 -- have not yet been created
869 execute immediate 'delete HRDPV_UHR_ALL_ORGANZTNUNITS_TL ' ||
870 ' where p_ORGANIZATION_ID in ( ' ||
871 ' select to_char(ORGANIZATION_ID) ' ||
872 ' from HR_ALL_ORGANIZATION_UNITS ' ||
873 ' where BUSINESS_GROUP_ID = ' ||
874 l_business_group_id || ')';
875
876 end if;
877
878
879 -- copy the flex structure info
880 -- only perform for an FW or A migration
881 if (l_migration_type in ('FW', 'A')) then
882
883 -- copy across the ID_FLEX_STRUCTURE_NAMEs for the current business group
884 hr_dm_utility.message('INFO','copy across the ID_FLEX_STRUCTURE_NAMEs',15);
885
886 select ID_FLEX_STRUCTURE_NAME
887 into l_org_information4
888 from fnd_id_flex_structures_vl
889 where id_flex_num = (select org_information4
890 from hr_organization_information
891 where org_information_context =
892 'Business Group Information'
893 and organization_id = l_business_group_id)
894 and ID_FLEX_CODE = 'GRD';
895 select ID_FLEX_STRUCTURE_NAME
896 into l_org_information5
897 from fnd_id_flex_structures_vl
898 where id_flex_num = (select org_information5
899 from hr_organization_information
900 where org_information_context =
901 'Business Group Information'
902 and organization_id = l_business_group_id)
903 and ID_FLEX_CODE = 'GRP';
904 select ID_FLEX_STRUCTURE_NAME
905 into l_org_information6
906 from fnd_id_flex_structures_vl
907 where id_flex_num = (select org_information6
908 from hr_organization_information
909 where org_information_context =
910 'Business Group Information'
911 and organization_id = l_business_group_id)
915 from fnd_id_flex_structures_vl
912 and ID_FLEX_CODE = 'JOB';
913 select ID_FLEX_STRUCTURE_NAME
914 into l_org_information7
916 where id_flex_num = (select org_information7
917 from hr_organization_information
918 where org_information_context =
919 'Business Group Information'
920 and organization_id = l_business_group_id)
921 and ID_FLEX_CODE = 'COST';
922 select ID_FLEX_STRUCTURE_NAME
923 into l_org_information8
924 from fnd_id_flex_structures_vl
925 where id_flex_num = (select org_information8
926 from hr_organization_information
927 where org_information_context =
928 'Business Group Information'
929 and organization_id = l_business_group_id)
930 and ID_FLEX_CODE = 'POS';
931 select SECURITY_GROUP_KEY
932 into l_org_information14
933 from fnd_security_groups_vl
934 where security_group_id = (select org_information14
935 from hr_organization_information
936 where org_information_context =
937 'Business Group Information'
938 and organization_id = l_business_group_id);
939
940 insert into hr_dm_exp_hr_org_inf_flx_v (
941 EXP_IMP_ID,
942 TABLE_NAME,
943 ORG_INFORMATION4,
944 ORG_INFORMATION5,
945 ORG_INFORMATION6,
946 ORG_INFORMATION7,
947 ORG_INFORMATION8,
948 ORG_INFORMATION14)
949 select
950 hr_dm_exp_imps_s.nextval,
951 'HR_ORG_INF_FLX',
952 l_org_information4,
953 l_org_information5,
954 l_org_information6,
955 l_org_information7,
956 l_org_information8,
957 l_org_information14
958 from dual;
959
960 end if;
961
962
963 -- copy the ben_batch_parameter info
964 -- only perform for an FW or A migration
965
966 if (l_migration_type in ('FW', 'A')) then
967
968 hr_dm_utility.message('INFO','Inserting row(s) into HR_DM_EXP_BEN_BATCH_PARAS_V',15);
969 insert into HR_DM_EXP_BEN_BATCH_PARAS_V (
970 EXP_IMP_ID,
971 TABLE_NAME,
972 BATCH_PARAMETER_ID,
973 BATCH_EXE_CD,
974 THREAD_CNT_NUM,
975 MAX_ERR_NUM,
976 CHUNK_SIZE,
977 BUSINESS_GROUP_ID,
978 LAST_UPDATE_DATE,
979 LAST_UPDATED_BY,
980 LAST_UPDATE_LOGIN,
981 CREATED_BY,
982 CREATION_DATE,
983 OBJECT_VERSION_NUMBER)
984 select
985 hr_dm_exp_imps_s.nextval,
986 'BEN_BATCH_PARAMETER',
987 batch_parameter_id,
988 batch_exe_cd,
989 thread_cnt_num,
990 max_err_num,
991 chunk_size,
992 business_group_id,
993 to_char(last_update_date,'YYYYMMDD HH24:MI:SS'),
994 last_updated_by,
995 last_update_login,
996 created_by,
997 to_char(creation_date,'YYYYMMDD HH24:MI:SS'),
998 object_version_number
999 from BEN_BATCH_PARAMETER
1000 where business_group_id = l_business_group_id
1001 and batch_exe_cd = 'HRDM'
1002 and last_update_date >= nvl(p_last_migration_date,
1003 last_update_date);
1004
1005 -- remove entry for data migrator from the batch lines table
1006 execute immediate 'delete HRDPV_UBEN_BATCH_PARAMETER ' ||
1007 ' where p_batch_exe_cd = ''HRDM''';
1008
1009 end if;
1010
1011 commit;
1012 hr_dm_utility.message('ROUT','exit:hr_dm_copy.source_copy', 25);
1013 exception
1014 when others then
1015 hr_dm_utility.error(SQLCODE,'hr_dm_copy.source_copy',
1016 '(none)','R');
1017 raise;
1018 end source_copy;
1019
1020 ----------------------- destination_copy ---------------------------------
1021 -- This procedure does some of the tasks of Copy phase in source
1022 -- database. It does the following :
1023 -- o Call procedure delete_datapump_tables to truncate datapump tables
1024 -- at destination.
1025 -- o Delete the data migrator packages rows from HR_API_MODULES table
1026 -- i.e API_MODULE_TYPE ='DM'.
1027 -- o Insert the rows into HR_API_MODULES tables from HR_DM_EXP_IMP table
1028 -- o Insert the row into HR_DM_MIGRATION table from HR_DM_EXP_IMP table
1029 -- o Inserts the rows for the business_group_id being copied
1030 -- from HR_DM_EXP_IMPS into HR_ALL_ORGANIZATION_UNITS,
1031 -- HR_ORGANIZATION_INFORMATION, HR_ALL_ORGANIZATION_UNITS_TL
1032 -- HR_LOCATIONS_ALL and HR_LOCATIONS_ALL_TL
1033 -- (for an FW migration only)
1034 -- o Update the row in the HR_DM_MIGRATIONS table to show that the business
1035 -- group has been created (for an FW migration only)
1036 -- Called By : Run manually.
1037 ---------------------------------------------------------------------
1038 procedure destination_copy is
1039
1040 l_migration_type varchar2(30);
1041 l_migration_id number;
1042 l_business_group_id number;
1043 l_exist_bgroup_id number;
1044 e_fatal_error exception;
1045 l_source varchar2(30);
1046 l_destination varchar2(30);
1047 l_migration_type_meaning varchar2(100);
1048 l_business_group_name hr_dm_migrations.business_group_name%type;
1049 l_migration_start_date date;
1050 l_cr varchar2(10);
1051 l_database_location varchar2(30);
1055 l_max_err_num number;
1052 l_up_phase_used varchar2(30);
1053 l_batch_exe_cd varchar2(30);
1054 l_thread_cnt_num number;
1056 l_chunk_size number;
1057 l_object_version_number number;
1058 l_batch_parameter_id number;
1059 l_schema varchar2(30);
1060
1061
1062 cursor csr_mig_info is
1063 select migration_type,
1064 business_group_id,
1065 source_database_instance,
1066 destination_database_instance,
1067 hr_general.decode_lookup('HR_DM_MIGRATION_TYPE',migration_type),
1068 business_group_name,
1069 migration_start_date
1070 from hr_dm_exp_migrations_v;
1071
1072 cursor csr_bg_check is
1073 select business_group_id
1074 from per_business_groups
1075 where business_group_id = l_business_group_id;
1076
1077 cursor csr_database is
1078 select upper(name)
1079 from v$database;
1080
1081 cursor csr_phase_rule is
1082 select phase_name
1083 from hr_dm_phase_rules
1084 where migration_type = l_migration_type
1085 and phase_name = 'UP';
1086
1087 cursor csr_batch_info is
1088 select
1089 batch_exe_cd,
1090 thread_cnt_num,
1091 max_err_num,
1092 chunk_size,
1093 object_version_number
1094 from HR_DM_EXP_BEN_BATCH_PARAS_V
1095 where (business_group_id = to_char(l_business_group_id))
1096 and (batch_exe_cd = 'HRDM');
1097
1098 cursor csr_batch_info_db is
1099 select
1100 batch_parameter_id
1101 from ben_batch_parameter
1102 where (business_group_id = l_business_group_id)
1103 and (batch_exe_cd = 'HRDM');
1104
1105 begin
1106
1107 -- set up data output
1108 hr_utility.set_trace_options('TRACE_DEST:DBMS_OUTPUT');
1109 hr_utility.trace_on;
1110
1111 -- get schema for PER
1112 l_schema := get_schema('PER');
1113
1114
1115 l_cr := fnd_global.local_chr(10);
1116
1117 -- check if migration is possible
1118
1119 -- find the migration type for the current migration
1120 open csr_mig_info;
1121 fetch csr_mig_info into l_migration_type,
1122 l_business_group_id,
1123 l_source,
1124 l_destination,
1125 l_migration_type_meaning,
1126 l_business_group_name,
1127 l_migration_start_date;
1128 close csr_mig_info;
1129
1130 -- find the database we are on now
1131 open csr_database;
1132 loop
1133 fetch csr_database into l_database_location;
1134 exit when csr_database%notfound;
1135 end loop;
1136 close csr_database;
1137
1138
1139 -- display information about the migration
1140 hr_utility.trace(l_cr);
1141 hr_utility.trace('HR Data Migrator');
1142 hr_utility.trace(l_cr);
1143 hr_utility.trace('Migration details');
1144 hr_utility.trace('Type : ' || l_migration_type_meaning);
1145 hr_utility.trace('Source : ' || l_source);
1146 hr_utility.trace('Destination : ' || l_destination);
1147 hr_utility.trace('Business group : ' || l_business_group_name);
1148 hr_utility.trace('Start Date : ' || l_migration_start_date);
1149 hr_utility.trace(l_cr);
1150
1151
1152 -- do some validation
1153 hr_utility.trace('Validating migration...');
1154 hr_utility.trace(l_cr);
1155
1156 -- make sure we are on the destination database
1157 if (upper(l_destination) <> l_database_location) then
1158 hr_utility.trace('Invalid migration :');
1159 hr_utility.trace(l_cr);
1160 hr_utility.trace('This is not the destination database.');
1161 hr_utility.trace('Current database : ' || l_database_location);
1162 hr_utility.trace('Destination database : ' || l_destination);
1163 hr_utility.trace('This migration can not proceed on this database.');
1164 hr_utility.trace(l_cr);
1165 raise e_fatal_error;
1166 end if;
1167
1168
1169
1170 -- see if the business group already exists
1171 open csr_bg_check;
1172 fetch csr_bg_check into l_exist_bgroup_id;
1173 close csr_bg_check;
1174
1175 if (l_exist_bgroup_id is null) and
1176 (l_migration_type <> 'FW') then
1177 -- For a non-FW migration the business group must exist
1178 hr_utility.trace('Invalid migration :');
1179 hr_utility.trace(l_cr);
1180 hr_utility.trace('For a non-FW migration the business group must exist.');
1181 hr_utility.trace('This migration can not proceed.');
1182 hr_utility.trace(l_cr);
1183 raise e_fatal_error;
1184 end if;
1185
1186 if (l_exist_bgroup_id is not null) and
1187 (l_migration_type = 'FW') then
1188 -- For an FW migration the business group must not exist
1189 hr_utility.trace('Invalid migration :');
1190 hr_utility.trace(l_cr);
1191 hr_utility.trace('For an FW migration the business group must not exist.');
1192 hr_utility.trace('This migration can not proceed.');
1193 hr_utility.trace(l_cr);
1194 raise e_fatal_error;
1195 end if;
1196
1197 hr_utility.trace('Migration passed validation checks.');
1198 hr_utility.trace(l_cr);
1199
1200 hr_utility.trace('Importing migration details...');
1201 hr_utility.trace(l_cr);
1202 hr_utility.trace(' Truncating hr_dm_dt_deletes');
1203 hr_dm_library.run_sql('truncate table ' || l_schema ||
1204 '.hr_dm_dt_deletes');
1205
1206 hr_utility.trace(' Setting g_data_migrator_mode to Y');
1207 hr_general.g_data_migrator_mode:='Y';
1208
1209
1210 -- import API details only if we are using datapump
1211
1212 open csr_phase_rule;
1213 fetch csr_phase_rule into l_up_phase_used;
1214 close csr_phase_rule;
1218 hr_utility.trace('Importing API details...');
1215
1216 if (l_up_phase_used = 'UP') then
1217
1219 hr_utility.trace('(Errors may be seen if contraint does not exist).');
1220 hr_utility.trace(l_cr);
1221
1222 -- disable constraint on HR_PUMP_BATCH_LINES
1223 -- so we can delete from HR_API_MODULES
1224 hr_utility.trace(' Disabling constraint - HR_PUMP_BATCH_LINES_FK2');
1225 begin
1226 execute immediate 'alter table ' || l_schema ||
1227 '.HR_PUMP_BATCH_LINES ' ||
1228 ' disable constraint HR_PUMP_BATCH_LINES_FK2';
1229 exception
1230 when others then
1231 hr_utility.trace('Error whilst disabling constraint');
1232 hr_utility.trace(sqlerrm(sqlcode));
1233 end;
1234
1235
1236 hr_utility.trace(' Call delete hr_api_modules');
1237 -- delete data migrator packages rows from HR_API_MODULES.
1238 delete hr_api_modules
1239 where api_module_type = 'DM';
1240
1241
1242 hr_utility.trace(' Updating hr_api_modules');
1243 -- Insert the rows into HR_API_MODULES tables from HR_DM_EXP_API_MODULES_V
1244 -- table.
1245
1246 insert into hr_api_modules ( api_module_id
1247 ,api_module_type
1248 ,module_name
1249 ,data_within_business_group
1250 ,legislation_code
1251 ,module_package
1252 ,last_update_date
1253 ,last_updated_by
1254 ,last_update_login
1255 ,created_by
1256 ,creation_date )
1257 select api_module_id
1258 ,api_module_type
1259 ,module_name
1260 ,data_within_business_group
1261 ,legislation_code
1262 ,module_package
1263 ,to_date(last_update_date,'YYYYMMDD HH24:MI:SS')
1264 ,last_updated_by
1265 ,last_update_login
1266 ,created_by
1267 ,to_date(creation_date,'YYYYMMDD HH24:MI:SS')
1268 from hr_dm_exp_api_modules_v;
1269
1270
1271 -- now re-enable constraint on HR_PUMP_BATCH_LINES
1272 hr_utility.trace('Enabling constraint - HR_PUMP_BATCH_LINES_FK2');
1273 begin
1274 execute immediate 'alter table ' || l_schema ||
1275 '.HR_PUMP_BATCH_LINES ' ||
1276 ' enable constraint HR_PUMP_BATCH_LINES_FK2';
1277 exception
1278 when others then
1279 hr_utility.trace('Error whilst enabling constraint');
1280 hr_utility.trace(sqlerrm(sqlcode));
1281 end;
1282
1283 end if;
1284
1285
1286 hr_utility.trace('call insert into hr_dm_migrations');
1287 -- Insert the current migration row from HR_DM_MIGRATIONS tables
1288 -- into HR_DM_EXP_MIGRATIONS_V view based on HR_DM_EXP_IMPS table
1289
1290 select hr_dm_migrations_s.nextval
1291 into l_migration_id
1292 from dual;
1293
1294 insert into hr_dm_migrations ( migration_id
1295 ,source_database_instance
1296 ,destination_database_instance
1297 ,migration_type
1298 ,application_id
1299 ,business_group_id
1300 ,business_group_name
1301 ,migration_start_date
1302 ,migration_end_date
1303 ,status
1304 ,effective_date
1305 ,migration_count
1306 ,selective_migration_criteria
1307 ,active_group
1308 ,last_update_date
1309 ,last_updated_by
1310 ,last_update_login
1311 ,created_by
1312 ,creation_date )
1313 select l_migration_id
1314 ,source_database_instance
1315 ,destination_database_instance
1316 ,migration_type
1317 ,application_id
1318 ,business_group_id
1319 ,business_group_name
1320 ,migration_start_date
1321 ,migration_end_date
1322 ,status
1323 ,effective_date
1324 ,migration_count
1325 ,selective_migration_criteria
1326 ,active_group
1327 ,to_date(last_update_date,'YYYYMMDD HH24:MI:SS')
1328 ,last_updated_by
1329 ,last_update_login
1330 ,created_by
1331 ,to_date(creation_date,'YYYYMMDD HH24:MI:SS')
1332 from hr_dm_exp_migrations_v;
1333
1334
1335
1336
1337 -- only perform for an FW migration
1338
1339 if (l_migration_type = 'FW') then
1340
1341 hr_utility.trace('Creating business group.');
1342
1343 insert into HR_LOCATIONS_ALL (
1344 LOCATION_ID,
1345 LOCATION_CODE,
1346 BUSINESS_GROUP_ID,
1347 DESCRIPTION,
1348 SHIP_TO_LOCATION_ID,
1349 SHIP_TO_SITE_FLAG,
1353 OFFICE_SITE_FLAG,
1350 RECEIVING_SITE_FLAG,
1351 BILL_TO_SITE_FLAG,
1352 IN_ORGANIZATION_FLAG,
1354 DESIGNATED_RECEIVER_ID,
1355 INVENTORY_ORGANIZATION_ID,
1356 TAX_NAME,
1357 INACTIVE_DATE,
1358 STYLE,
1359 ADDRESS_LINE_1,
1360 ADDRESS_LINE_2,
1361 ADDRESS_LINE_3,
1362 TOWN_OR_CITY,
1363 COUNTRY,
1364 POSTAL_CODE,
1365 REGION_1,
1366 REGION_2,
1367 REGION_3,
1368 TELEPHONE_NUMBER_1,
1369 TELEPHONE_NUMBER_2,
1370 TELEPHONE_NUMBER_3,
1371 LOC_INFORMATION13,
1372 LOC_INFORMATION14,
1373 LOC_INFORMATION15,
1374 LOC_INFORMATION16,
1375 LOC_INFORMATION17,
1376 ATTRIBUTE_CATEGORY,
1377 ATTRIBUTE1,
1378 ATTRIBUTE2,
1379 ATTRIBUTE3,
1380 ATTRIBUTE4,
1381 ATTRIBUTE5,
1382 ATTRIBUTE6,
1383 ATTRIBUTE7,
1384 ATTRIBUTE8,
1385 ATTRIBUTE9,
1386 ATTRIBUTE10,
1387 ATTRIBUTE11,
1388 ATTRIBUTE12,
1389 ATTRIBUTE13,
1390 ATTRIBUTE14,
1391 ATTRIBUTE15,
1392 ATTRIBUTE16,
1393 ATTRIBUTE17,
1394 ATTRIBUTE18,
1395 ATTRIBUTE19,
1396 ATTRIBUTE20,
1397 GLOBAL_ATTRIBUTE_CATEGORY,
1398 GLOBAL_ATTRIBUTE1,
1399 GLOBAL_ATTRIBUTE2,
1400 GLOBAL_ATTRIBUTE3,
1401 GLOBAL_ATTRIBUTE4,
1402 GLOBAL_ATTRIBUTE5,
1403 GLOBAL_ATTRIBUTE6,
1404 GLOBAL_ATTRIBUTE7,
1405 GLOBAL_ATTRIBUTE8,
1406 GLOBAL_ATTRIBUTE9,
1407 GLOBAL_ATTRIBUTE10,
1408 GLOBAL_ATTRIBUTE11,
1409 GLOBAL_ATTRIBUTE12,
1410 GLOBAL_ATTRIBUTE13,
1411 GLOBAL_ATTRIBUTE14,
1412 GLOBAL_ATTRIBUTE15,
1413 GLOBAL_ATTRIBUTE16,
1414 GLOBAL_ATTRIBUTE17,
1415 GLOBAL_ATTRIBUTE18,
1416 GLOBAL_ATTRIBUTE19,
1417 GLOBAL_ATTRIBUTE20,
1418 last_update_date,
1419 LAST_UPDATED_BY,
1420 LAST_UPDATE_LOGIN,
1421 CREATED_BY,
1422 creation_date,
1423 ENTERED_BY,
1424 TP_HEADER_ID,
1425 ECE_TP_LOCATION_CODE,
1426 OBJECT_VERSION_NUMBER)
1427 select
1428 LOCATION_ID,
1429 LOCATION_CODE,
1430 BUSINESS_GROUP_ID,
1431 DESCRIPTION,
1432 SHIP_TO_LOCATION_ID,
1433 SHIP_TO_SITE_FLAG,
1434 RECEIVING_SITE_FLAG,
1435 BILL_TO_SITE_FLAG,
1436 IN_ORGANIZATION_FLAG,
1437 OFFICE_SITE_FLAG,
1438 DESIGNATED_RECEIVER_ID,
1439 INVENTORY_ORGANIZATION_ID,
1440 TAX_NAME,
1441 to_date(INACTIVE_DATE,'YYYYMMDD HH24:MI:SS'),
1442 STYLE,
1443 ADDRESS_LINE_1,
1444 ADDRESS_LINE_2,
1445 ADDRESS_LINE_3,
1446 TOWN_OR_CITY,
1447 COUNTRY,
1448 POSTAL_CODE,
1449 REGION_1,
1450 REGION_2,
1451 REGION_3,
1452 TELEPHONE_NUMBER_1,
1453 TELEPHONE_NUMBER_2,
1454 TELEPHONE_NUMBER_3,
1455 LOC_INFORMATION13,
1456 LOC_INFORMATION14,
1457 LOC_INFORMATION15,
1458 LOC_INFORMATION16,
1459 LOC_INFORMATION17,
1460 ATTRIBUTE_CATEGORY,
1461 ATTRIBUTE1,
1462 ATTRIBUTE2,
1463 ATTRIBUTE3,
1464 ATTRIBUTE4,
1465 ATTRIBUTE5,
1466 ATTRIBUTE6,
1467 ATTRIBUTE7,
1468 ATTRIBUTE8,
1469 ATTRIBUTE9,
1470 ATTRIBUTE10,
1471 ATTRIBUTE11,
1472 ATTRIBUTE12,
1473 ATTRIBUTE13,
1474 ATTRIBUTE14,
1475 ATTRIBUTE15,
1476 ATTRIBUTE16,
1477 ATTRIBUTE17,
1478 ATTRIBUTE18,
1479 ATTRIBUTE19,
1480 ATTRIBUTE20,
1481 GLOBAL_ATTRIBUTE_CATEGORY,
1482 GLOBAL_ATTRIBUTE1,
1483 GLOBAL_ATTRIBUTE2,
1484 GLOBAL_ATTRIBUTE3,
1485 GLOBAL_ATTRIBUTE4,
1486 GLOBAL_ATTRIBUTE5,
1487 GLOBAL_ATTRIBUTE6,
1488 GLOBAL_ATTRIBUTE7,
1489 GLOBAL_ATTRIBUTE8,
1490 GLOBAL_ATTRIBUTE9,
1491 GLOBAL_ATTRIBUTE10,
1492 GLOBAL_ATTRIBUTE11,
1493 GLOBAL_ATTRIBUTE12,
1494 GLOBAL_ATTRIBUTE13,
1495 GLOBAL_ATTRIBUTE14,
1496 GLOBAL_ATTRIBUTE15,
1497 GLOBAL_ATTRIBUTE16,
1498 GLOBAL_ATTRIBUTE17,
1499 GLOBAL_ATTRIBUTE18,
1500 GLOBAL_ATTRIBUTE19,
1501 GLOBAL_ATTRIBUTE20,
1502 to_date(last_update_date,'YYYYMMDD HH24:MI:SS'),
1503 LAST_UPDATED_BY,
1504 LAST_UPDATE_LOGIN,
1505 CREATED_BY,
1506 to_date(creation_date,'YYYYMMDD HH24:MI:SS'),
1507 ENTERED_BY,
1508 TP_HEADER_ID,
1509 ECE_TP_LOCATION_CODE,
1510 OBJECT_VERSION_NUMBER
1511 from HR_DM_EXP_HR_LOC_ALL_V dmv
1512 where not exists (select null
1513 from HR_LOCATIONS_ALL tb
1514 where dmv.location_id = tb.LOCATION_ID);
1515
1516 insert into HR_LOCATIONS_ALL_TL (
1517 LOCATION_ID,
1518 LANGUAGE,
1519 SOURCE_LANG,
1520 LOCATION_CODE,
1521 DESCRIPTION,
1522 LAST_UPDATE_DATE,
1523 LAST_UPDATED_BY,
1524 LAST_UPDATE_LOGIN,
1525 CREATED_BY,
1526 CREATION_DATE)
1527 select
1528 LOCATION_ID,
1529 LANGUAGE,
1530 SOURCE_LANG,
1531 LOCATION_CODE,
1532 DESCRIPTION,
1533 to_date(last_update_date,'YYYYMMDD HH24:MI:SS'),
1534 LAST_UPDATED_BY,
1535 LAST_UPDATE_LOGIN,
1536 CREATED_BY,
1537 to_date(creation_date,'YYYYMMDD HH24:MI:SS')
1541 where dmv.location_id = tb.LOCATION_ID);
1538 from HR_DM_EXP_HR_LOC_ALL_TL_V dmv
1539 where not exists (select null
1540 from HR_LOCATIONS_ALL_TL tb
1542
1543
1544
1545
1546 -- the comments column has been removed
1547 insert into HR_ALL_ORGANIZATION_UNITS (
1548 ORGANIZATION_ID,
1549 BUSINESS_GROUP_ID,
1550 COST_ALLOCATION_KEYFLEX_ID,
1551 LOCATION_ID,
1552 SOFT_CODING_KEYFLEX_ID,
1553 DATE_FROM,
1554 NAME,
1555 DATE_TO,
1556 INTERNAL_EXTERNAL_FLAG,
1557 INTERNAL_ADDRESS_LINE,
1558 TYPE,
1559 REQUEST_ID,
1560 PROGRAM_APPLICATION_ID,
1561 PROGRAM_ID,
1562 PROGRAM_UPDATE_DATE,
1563 ATTRIBUTE_CATEGORY,
1564 ATTRIBUTE1,
1565 ATTRIBUTE2,
1566 ATTRIBUTE3,
1567 ATTRIBUTE4,
1568 ATTRIBUTE5,
1569 ATTRIBUTE6,
1570 ATTRIBUTE7,
1571 ATTRIBUTE8,
1572 ATTRIBUTE9,
1573 ATTRIBUTE10,
1574 ATTRIBUTE11,
1575 ATTRIBUTE12,
1576 ATTRIBUTE13,
1577 ATTRIBUTE14,
1578 ATTRIBUTE15,
1579 ATTRIBUTE16,
1580 ATTRIBUTE17,
1581 ATTRIBUTE18,
1582 ATTRIBUTE19,
1583 ATTRIBUTE20,
1584 LAST_UPDATE_DATE,
1585 LAST_UPDATED_BY,
1586 LAST_UPDATE_LOGIN,
1587 CREATED_BY,
1588 CREATION_DATE)
1589 select
1590 ORGANIZATION_ID,
1591 BUSINESS_GROUP_ID,
1592 COST_ALLOCATION_KEYFLEX_ID,
1593 LOCATION_ID,
1594 SOFT_CODING_KEYFLEX_ID,
1595 to_date(DATE_FROM,'YYYYMMDD HH24:MI:SS'),
1596 NAME,
1597 to_date(DATE_TO,'YYYYMMDD HH24:MI:SS'),
1598 INTERNAL_EXTERNAL_FLAG,
1599 INTERNAL_ADDRESS_LINE,
1600 TYPE,
1601 REQUEST_ID,
1602 PROGRAM_APPLICATION_ID,
1603 PROGRAM_ID,
1604 to_date(PROGRAM_UPDATE_DATE,'YYYYMMDD HH24:MI:SS'),
1605 ATTRIBUTE_CATEGORY,
1606 ATTRIBUTE1,
1607 ATTRIBUTE2,
1608 ATTRIBUTE3,
1609 ATTRIBUTE4,
1610 ATTRIBUTE5,
1611 ATTRIBUTE6,
1612 ATTRIBUTE7,
1613 ATTRIBUTE8,
1614 ATTRIBUTE9,
1615 ATTRIBUTE10,
1616 ATTRIBUTE11,
1617 ATTRIBUTE12,
1618 ATTRIBUTE13,
1619 ATTRIBUTE14,
1620 ATTRIBUTE15,
1621 ATTRIBUTE16,
1622 ATTRIBUTE17,
1623 ATTRIBUTE18,
1624 ATTRIBUTE19,
1625 ATTRIBUTE20,
1626 to_date(last_update_date,'YYYYMMDD HH24:MI:SS'),
1627 last_updated_by,
1628 last_update_login,
1629 created_by,
1630 to_date(creation_date,'YYYYMMDD HH24:MI:SS')
1631 from HR_DM_EXP_ALL_ORG_UNITS_V;
1632
1633 insert into HR_ORGANIZATION_INFORMATION (
1634 ORG_INFORMATION_ID,
1635 ORG_INFORMATION_CONTEXT,
1636 ORGANIZATION_ID,
1637 ORG_INFORMATION1,
1638 ORG_INFORMATION10,
1639 ORG_INFORMATION11,
1640 ORG_INFORMATION12,
1641 ORG_INFORMATION13,
1642 ORG_INFORMATION14,
1643 ORG_INFORMATION15,
1644 ORG_INFORMATION16,
1645 ORG_INFORMATION17,
1646 ORG_INFORMATION18,
1647 ORG_INFORMATION19,
1648 ORG_INFORMATION2,
1649 ORG_INFORMATION20,
1650 ORG_INFORMATION3,
1651 ORG_INFORMATION4,
1652 ORG_INFORMATION5,
1653 ORG_INFORMATION6,
1654 ORG_INFORMATION7,
1655 ORG_INFORMATION8,
1656 ORG_INFORMATION9,
1657 REQUEST_ID,
1658 PROGRAM_APPLICATION_ID,
1659 PROGRAM_ID,
1660 PROGRAM_UPDATE_DATE,
1661 ATTRIBUTE_CATEGORY,
1662 ATTRIBUTE1,
1663 ATTRIBUTE2,
1664 ATTRIBUTE3,
1665 ATTRIBUTE4,
1666 ATTRIBUTE5,
1667 ATTRIBUTE6,
1668 ATTRIBUTE7,
1669 ATTRIBUTE8,
1670 ATTRIBUTE9,
1671 ATTRIBUTE10,
1672 ATTRIBUTE11,
1673 ATTRIBUTE12,
1674 ATTRIBUTE13,
1675 ATTRIBUTE14,
1676 ATTRIBUTE15,
1677 ATTRIBUTE16,
1678 ATTRIBUTE17,
1679 ATTRIBUTE18,
1680 ATTRIBUTE19,
1681 ATTRIBUTE20,
1682 LAST_UPDATE_DATE,
1683 LAST_UPDATED_BY,
1684 LAST_UPDATE_LOGIN,
1685 CREATED_BY,
1686 CREATION_DATE)
1687 select
1688 ORG_INFORMATION_ID,
1689 ORG_INFORMATION_CONTEXT,
1690 ORGANIZATION_ID,
1691 ORG_INFORMATION1,
1692 ORG_INFORMATION10,
1693 ORG_INFORMATION11,
1694 ORG_INFORMATION12,
1695 ORG_INFORMATION13,
1696 ORG_INFORMATION14,
1697 ORG_INFORMATION15,
1698 ORG_INFORMATION16,
1699 ORG_INFORMATION17,
1700 ORG_INFORMATION18,
1701 ORG_INFORMATION19,
1702 ORG_INFORMATION2,
1703 ORG_INFORMATION20,
1704 ORG_INFORMATION3,
1705 ORG_INFORMATION4,
1706 ORG_INFORMATION5,
1707 ORG_INFORMATION6,
1708 ORG_INFORMATION7,
1709 ORG_INFORMATION8,
1710 ORG_INFORMATION9,
1711 REQUEST_ID,
1712 PROGRAM_APPLICATION_ID,
1713 PROGRAM_ID,
1714 to_date(PROGRAM_UPDATE_DATE,'YYYYMMDD HH24:MI:SS'),
1715 ATTRIBUTE_CATEGORY,
1716 ATTRIBUTE1,
1717 ATTRIBUTE2,
1718 ATTRIBUTE3,
1719 ATTRIBUTE4,
1720 ATTRIBUTE5,
1721 ATTRIBUTE6,
1722 ATTRIBUTE7,
1723 ATTRIBUTE8,
1724 ATTRIBUTE9,
1725 ATTRIBUTE10,
1726 ATTRIBUTE11,
1727 ATTRIBUTE12,
1728 ATTRIBUTE13,
1729 ATTRIBUTE14,
1730 ATTRIBUTE15,
1731 ATTRIBUTE16,
1732 ATTRIBUTE17,
1733 ATTRIBUTE18,
1734 ATTRIBUTE19,
1735 ATTRIBUTE20,
1736 to_date(last_update_date,'YYYYMMDD HH24:MI:SS'),
1737 last_updated_by,
1738 last_update_login,
1739 created_by,
1740 to_date(creation_date,'YYYYMMDD HH24:MI:SS')
1741 from HR_DM_EXP_ORG_INFO_V;
1742
1743 insert into HR_ALL_ORGANIZATION_UNITS_TL (
1744 ORGANIZATION_ID,
1745 LANGUAGE,
1746 SOURCE_LANG,
1747 NAME,
1748 LAST_UPDATE_DATE,
1749 LAST_UPDATED_BY,
1750 LAST_UPDATE_LOGIN,
1751 CREATED_BY,
1752 CREATION_DATE)
1753 select
1754 ORGANIZATION_ID,
1755 LANGUAGE,
1756 SOURCE_LANG,
1757 NAME,
1758 to_date(last_update_date,'YYYYMMDD HH24:MI:SS'),
1759 last_updated_by,
1760 last_update_login,
1761 created_by,
1762 to_date(creation_date,'YYYYMMDD HH24:MI:SS')
1763 from HR_DM_EXP_ALL_ORG_UNITS_TL_V;
1764
1765 update hr_dm_migrations
1766 set business_group_created = 'Y'
1767 where migration_id = l_migration_id;
1768
1769
1770 end if;
1771
1772 -- copy the ben_batch_parameter info
1773 -- only perform for an FW or A migration
1774
1775 if (l_migration_type in ('FW', 'A')) then
1776 hr_utility.trace('Importing BEN_BATCH_PARAMETER information.');
1777
1778 open csr_batch_info;
1779 fetch csr_batch_info into
1780 l_batch_exe_cd,
1781 l_thread_cnt_num,
1782 l_max_err_num,
1783 l_chunk_size,
1784 l_object_version_number;
1785
1786 if csr_batch_info%found then
1787 -- data exists from the source database,
1788 -- so check if we need to update or insert
1789
1790 open csr_batch_info_db;
1791 fetch csr_batch_info_db into l_batch_parameter_id;
1792
1793 if csr_batch_info_db%found then
1794 -- do an update
1795 update BEN_BATCH_PARAMETER
1796 set batch_exe_cd = l_batch_exe_cd,
1797 thread_cnt_num = l_thread_cnt_num,
1798 max_err_num = l_max_err_num,
1799 chunk_size = l_chunk_size,
1800 object_version_number = l_object_version_number
1801 where (business_group_id = l_business_group_id)
1802 and (batch_exe_cd = 'HRDM');
1803 else
1804 -- do an insert
1805 insert into BEN_BATCH_PARAMETER (
1806 BATCH_PARAMETER_ID,
1807 BATCH_EXE_CD,
1808 THREAD_CNT_NUM,
1809 MAX_ERR_NUM,
1810 CHUNK_SIZE,
1811 BUSINESS_GROUP_ID,
1812 LAST_UPDATE_DATE,
1813 LAST_UPDATED_BY,
1814 LAST_UPDATE_LOGIN,
1815 CREATED_BY,
1816 CREATION_DATE,
1817 OBJECT_VERSION_NUMBER)
1818 select
1819 batch_parameter_id,
1820 batch_exe_cd,
1821 thread_cnt_num,
1822 max_err_num,
1823 chunk_size,
1824 business_group_id,
1825 to_date(last_update_date,'YYYYMMDD HH24:MI:SS'),
1826 last_updated_by,
1827 last_update_login,
1828 created_by,
1829 to_date(creation_date,'YYYYMMDD HH24:MI:SS'),
1830 object_version_number
1831 from HR_DM_EXP_BEN_BATCH_PARAS_V;
1832
1833 end if;
1834
1835 end if;
1836
1837 end if;
1838
1839 commit;
1840
1841 hr_utility.trace(l_cr);
1842 hr_utility.trace('Process completed sucessfully.');
1843 hr_utility.trace(l_cr);
1844
1845
1846 -- stop user seeing the error when an invalid migration
1847 -- has been detected
1848 exception
1849 when e_fatal_error then
1850 null;
1851
1852
1853
1854 end destination_copy;
1855
1856
1857 end hr_dm_copy;