[Home] [Help]
PACKAGE BODY: APPS.CZ_MIGRATE
Source
1 PACKAGE BODY cz_migrate AS
2 /* $Header: czmigrb.pls 120.5 2006/04/27 03:19:41 kdande ship $ */
3
4 G_OA_UI_STYLE VARCHAR2(1) := '7';
5 G_OA_UIMT_STYLE VARCHAR2(1) := '5';
6
7 thisMessageId PLS_INTEGER;
8 thisRunId PLS_INTEGER;
9 thisStatusCode PLS_INTEGER;
10
11 dbLinkName user_db_links.db_link%TYPE;
12 serverLocalName cz_servers.local_name%TYPE;
13 serverLocalId cz_servers.server_local_id%TYPE;
14 NoIntegrityCheck BOOLEAN;
15
16 PROCEDURE adjust_specific_control;
17 FUNCTION verify_target_database RETURN PLS_INTEGER;
18 TYPE ref_cursor IS REF CURSOR;
19
20 ---------------------------------------------------------------------------------------
21 PROCEDURE setup_migration_cp(errbuf OUT NOCOPY VARCHAR2,
22 retcode OUT NOCOPY NUMBER,
23 p_source_name IN VARCHAR2,
24 p_force_run IN VARCHAR2 DEFAULT 'NO') IS
25
26 RunId PLS_INTEGER;
27 xError PLS_INTEGER;
28 BEGIN
29
30 retcode := CONCURRENT_SUCCESS;
31 errbuf := '';
32 xError := migrate_setup(RunId, p_source_name, p_force_run);
33
34 IF(xError = FATAL_ERROR)THEN
35
36 retcode := CONCURRENT_ERROR;
37
38 --'Migration setup cannot continue due to the previous errors. Please see log for details.'
39 errbuf := CZ_UTILS.GET_TEXT('CZ_MIGR_CANNOT_SETUP');
40 report(errbuf, URGENCY_ERROR);
41
42 ELSIF(xError = SKIPPABLE_ERROR)THEN
43
44 --'Migration setup completed successfully with warnings. Please see log for details.'
45 errbuf := CZ_UTILS.GET_TEXT('CZ_MIGR_WARNING_SETUP');
46 report(errbuf, URGENCY_WARNING);
47 END IF;
48
49 DBMS_APPLICATION_INFO.SET_MODULE('','');
50 END setup_migration_cp;
51 ---------------------------------------------------------------------------------------
52 PROCEDURE run_migration_cp(errbuf OUT NOCOPY VARCHAR2,
53 retcode OUT NOCOPY NUMBER,
54 p_force_run IN VARCHAR2 DEFAULT 'NO') IS
55
56 RunId PLS_INTEGER;
57 xError PLS_INTEGER;
58 BEGIN
59
60 retcode := CONCURRENT_SUCCESS;
61 errbuf := '';
62 xError := migrate(RunId, p_force_run,
63 500, 0, 0, 0, 0, 0, 0); -- defaults
64
65 IF(xError = FATAL_ERROR)THEN
66
67 retcode := CONCURRENT_ERROR;
68
69 --'Migration cannot continue due to the previous errors. Please see log for details.'
70 errbuf := CZ_UTILS.GET_TEXT('CZ_MIGR_CANNOT_CONTINUE');
71 report(errbuf, URGENCY_ERROR);
72
73 ELSIF(xError = SKIPPABLE_ERROR)THEN
74
75 --'Migration completed successfully with warnings. Please see log for details.'
76 errbuf := CZ_UTILS.GET_TEXT('CZ_MIGR_WARNING_RUN');
77 report(errbuf, URGENCY_WARNING);
78 END IF;
79
80 DBMS_APPLICATION_INFO.SET_MODULE('','');
81 END run_migration_cp;
82 ---------------------------------------------------------------------------------------
83 FUNCTION verify_server_entry(p_local_name IN VARCHAR2, x_link_name IN OUT NOCOPY VARCHAR2)
84 RETURN INTEGER IS
85 BEGIN
86
87 SELECT server_local_id, fndnam_link_name INTO serverLocalId, x_link_name
88 FROM cz_servers WHERE UPPER(local_name) = UPPER(p_local_name);
89
90 IF(x_link_name IS NULL)THEN
91 --'No database link is associated with the specified server name ''%LOCALNAME''.'
92 report(CZ_UTILS.GET_TEXT('CZ_MIGR_EMPTY_LINK', 'LOCALNAME', p_local_name), URGENCY_ERROR);
93 RETURN FATAL_ERROR;
94 END IF;
95
96 RETURN NO_ERROR;
97 EXCEPTION
98 WHEN OTHERS THEN
99 --'Unable to retrieve database link name for the specified server name ''%LOCALNAME'': %ERRORTEXT.'
100 report(CZ_UTILS.GET_TEXT('CZ_MIGR_NO_DATABASE_LINK', 'LOCALNAME', p_local_name, 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
101 RETURN FATAL_ERROR;
102 END;
103 ---------------------------------------------------------------------------------------
104 FUNCTION verify_database_link(p_link_name IN VARCHAR2)
105 RETURN INTEGER IS
106 BEGIN
107
108 EXECUTE IMMEDIATE 'SELECT SYSDATE FROM DUAL@' || p_link_name;
109 RETURN NO_ERROR;
110
111 EXCEPTION
112 WHEN OTHERS THEN
113 --'Database link ''%LINKNAME'' is not functional: %ERRORTEXT.'
114 report(CZ_UTILS.GET_TEXT('CZ_MIGR_BAD_DATABASE_LINK', 'LINKNAME', p_link_name, 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
115 RETURN FATAL_ERROR;
116 END;
117 ---------------------------------------------------------------------------------------
118 FUNCTION migrate_setup(x_run_id IN OUT NOCOPY PLS_INTEGER,
119 p_local_name IN VARCHAR2,
120 p_force_run IN VARCHAR2)
121 RETURN INTEGER IS
122
123 xError INTEGER;
124 errorFlag PLS_INTEGER;
125 MigrationStatus cz_db_settings.value%TYPE;
126 BEGIN
127
128 thisMessageId := MESSAGE_START_ID;
129 thisStatusCode := SETUP_STATUS_CODE;
130 thisRunId := NVL(x_run_id, GENERIC_RUN_ID);
131 NoIntegrityCheck := FALSE;
132
133 BEGIN
134 IF(x_run_id IS NULL OR x_run_id = 0)THEN
135 SELECT cz_xfr_run_infos_s.NEXTVAL INTO x_run_id FROM DUAL;
136 END IF;
137 thisRunId := x_run_id;
138 EXCEPTION
139 WHEN OTHERS THEN
140 --'Unable to generate identification number for this process, database objects are missing or invalid: %ERRORTEXT.'
141 report(CZ_UTILS.GET_TEXT('CZ_MIGR_NO_RUN_ID', 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
142 RETURN FATAL_ERROR;
143 END;
144
145 --Check for other running migration/setup sessions.
146
147 FOR c_running IN (SELECT action FROM v$session WHERE module IN (
148 'CZMIGRATION',
149 'CZ_PB_MGR',
150 'CZ_MODEL_MIGRATION',
151 'CZIMPORT',
152 CZ_PUBL_SYNC_CRASH.pbSourceClone,
153 CZ_PUBL_SYNC_CRASH.pbTargetClone,
154 CZ_PUBL_SYNC_CRASH.pbSourceCrash,
155 CZ_PUBL_SYNC_CRASH.pbTargetCrash,
156 CZ_RULE_IMPORT.CZRI_MODULE_NAME)) LOOP
157
158 --'Unable to start a new migration session because another migration session or an incompatible concurrent'
159 --'program is currently running.'
160 report(CZ_UTILS.GET_TEXT('CZ_MIGR_SESSION_EXISTS'), URGENCY_ERROR);
161 RETURN FATAL_ERROR;
162 END LOOP;
163
164 DBMS_APPLICATION_INFO.SET_MODULE('CZMIGRATION', TO_CHAR(x_run_id));
165
166 BEGIN
167 SELECT value INTO MigrationStatus FROM cz_db_settings
168 WHERE UPPER(section_name) = 'MIGRATE' AND UPPER(setting_id) = 'MIGRATIONSTATUS';
169 EXCEPTION
170 WHEN NO_DATA_FOUND THEN
171 --No setting is normal.
172 NULL;
173 WHEN OTHERS THEN
174 --'Unable to read the migration status because of %ERRORTEXT.'
175 report(CZ_UTILS.GET_TEXT('CZ_MIGR_NO_STATUS', 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
176 RETURN FATAL_ERROR;
177 END;
178
179 IF(verify_server_entry(p_local_name, dbLinkName) = FATAL_ERROR)THEN RETURN FATAL_ERROR; END IF;
180 IF(verify_database_link(dbLinkName) = FATAL_ERROR)THEN RETURN FATAL_ERROR; END IF;
181
182 --Check for the emptiness of the target database. FATAL_ERROR, can be overridden with p_force_run.
183
184 BEGIN
185
186 xError := verify_target_database;
187
188 IF(xError = SKIPPABLE_ERROR)THEN
189 IF(UPPER(MigrationStatus) IN ('STARTED', 'COMPLETED'))THEN
190
191 --'Migration setup cannot be started after the migration has been run.'
192 report(CZ_UTILS.GET_TEXT('CZ_MIGR_CANNOT_RUN_SETUP'), URGENCY_ERROR);
193 RETURN FATAL_ERROR;
194 ELSIF(UPPER(p_force_run) = 'NO')THEN
195
196 --'The target database is not a fresh installed Applications database, running migration may cause data corruption.'
197 report(CZ_UTILS.GET_TEXT('CZ_MIGR_TARGET_NOT_EMPTY'), URGENCY_ERROR);
198 RETURN FATAL_ERROR;
199 END IF;
200 END IF;
201 EXCEPTION
202 WHEN OTHERS THEN
203 --'Unable to verify the target database: %ERRORTEXT.'
204 report(CZ_UTILS.GET_TEXT('CZ_MIGR_TARGET_VERIFY', 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
205 RETURN FATAL_ERROR;
206 END;
207
208 xError := compare_versions;
209
210 IF(xError <> NO_ERROR)THEN RETURN FATAL_ERROR; END IF;
211
212 --Populate the control table.
213
214 BEGIN
215
216 DELETE FROM cz_xfr_tables WHERE UPPER(xfr_group) IN
217 ('OVERRIDE', 'SLOWREFRESH', 'MIGRATE', 'REFRESH', 'FORCESLOW', 'TRIGGERS', 'SEQUENCES');
218
219 INSERT INTO cz_xfr_tables (order_seq, xfr_group, disabled, dst_table, pk_useexpansion)
220 SELECT ROWNUM, 'OVERRIDE', '0', table_name, DECODE(logging, 'YES', 'Y', 'NO', 'N', 'N')
221 FROM all_tables
222 WHERE owner = 'CZ';
223
224 INSERT INTO cz_xfr_tables (order_seq, xfr_group, disabled, dst_table)
225 SELECT ROWNUM, 'TRIGGERS', '0', trigger_name
226 FROM user_triggers
227 WHERE status = 'ENABLED'
228 AND trigger_name LIKE 'CZ/_%' ESCAPE '/';
229
230 adjust_specific_control;
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 ROLLBACK;
235 --'Unable to populate migration control table: %ERRORTEXT.'
236 report(CZ_UTILS.GET_TEXT('CZ_MIGR_CONTROL_ERROR', 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
237 RETURN FATAL_ERROR;
238 END;
239
240 errorFlag := 0;
241
242 FOR c_tables IN (SELECT dst_table FROM cz_xfr_tables
243 WHERE xfr_group in ('OVERRIDE', 'SLOWREFRESH', 'MIGRATE', 'REFRESH', 'FORCESLOW')
244 AND disabled = '0') LOOP
245
246 xError := compare_columns(c_tables.dst_table);
247 IF(xError <> NO_ERROR)THEN errorFlag := 1; END IF;
248 END LOOP;
249
250 IF(errorFlag = 1)THEN ROLLBACK; RETURN FATAL_ERROR; END IF;
251
252 BEGIN
253 INSERT INTO cz_db_settings (section_name, setting_id, data_type, value)
254 SELECT 'MIGRATE', 'SourceServer', 4, p_local_name FROM DUAL WHERE NOT EXISTS
255 (SELECT NULL FROM cz_db_settings
256 WHERE UPPER(section_name) = 'MIGRATE' and UPPER(setting_id) = 'SOURCESERVER');
257
258 UPDATE cz_db_settings SET value = p_local_name
259 WHERE UPPER(section_name) = 'MIGRATE' AND UPPER(setting_id) = 'SOURCESERVER';
260
261 INSERT INTO cz_db_settings (section_name, setting_id, data_type, value)
262 SELECT 'MIGRATE', 'MigrationStatus', 4, 'INSTALLED' FROM DUAL WHERE NOT EXISTS
263 (SELECT NULL FROM cz_db_settings
264 WHERE UPPER(section_name) = 'MIGRATE' and UPPER(setting_id) = 'MIGRATIONSTATUS');
265
266 UPDATE cz_db_settings SET value = 'INSTALLED'
267 WHERE UPPER(section_name) = 'MIGRATE' AND UPPER(setting_id) = 'MIGRATIONSTATUS';
268
269 COMMIT;
270
271 EXCEPTION
272 WHEN OTHERS THEN
273 ROLLBACK;
274 --'Unable to update the migration status because of %ERRORTEXT.'
275 report(CZ_UTILS.GET_TEXT('CZ_MIGR_NO_STATUS_UPDATE', 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
276 RETURN FATAL_ERROR;
277 END;
278 RETURN NO_ERROR;
279 END migrate_setup;
280
281 ---------------------------------------------------------------------------------------
282 /* defaults
283 p_force_run IN VARCHAR2 DEFAULT 'NO',
284 CommitSize in pls_integer default 500,
285 StopOnSkippable in number default 0,
286 ForceSlowMode in number default 0,
287 AllowDifferentVersions in number default 0,
288 AllowRefresh in number default 0,
289 ForceProcess in number default 0,
290 DeleteDbLink in number default 0
291 */
292 FUNCTION migrate(x_run_id IN OUT NOCOPY PLS_INTEGER,
293 p_force_run IN VARCHAR2,
294 CommitSize in pls_integer,
295 StopOnSkippable in number,
296 ForceSlowMode in number,
297 AllowDifferentVersions in number,
298 AllowRefresh in number,
299 ForceProcess in number,
300 DeleteDbLink in number)
301 RETURN INTEGER IS
302
303 xError INTEGER;
304 xMsgCount INTEGER;
305 xReturnStatus VARCHAR2(255);
306 xMsgData VARCHAR2(32000);
307
308 MigrationStatus cz_db_settings.value%TYPE;
309 sourceMaxId cz_servers.server_local_id%TYPE;
310 targetMaxId cz_servers.server_local_id%TYPE;
311 srcPbSessionCnt NUMBER := 0;
312 l_src_sessn_cur REF_CURSOR;
313 begin
314
315 thisMessageId := MESSAGE_START_ID;
316 thisStatusCode := MIGRATE_STATUS_CODE;
317 thisRunId := NVL(x_run_id, GENERIC_RUN_ID);
318 NoIntegrityCheck := TRUE;
319
320 BEGIN
321 IF(x_run_id IS NULL OR x_run_id = 0)THEN
322 SELECT cz_xfr_run_infos_s.NEXTVAL INTO x_run_id FROM DUAL;
323 END IF;
324 thisRunId := x_run_id;
325 EXCEPTION
326 WHEN OTHERS THEN
327 --'Unable to generate identification number for this process, database objects are missing or invalid: %ERRORTEXT.'
328 report(CZ_UTILS.GET_TEXT('CZ_MIGR_NO_RUN_ID', 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
329 RETURN FATAL_ERROR;
330 END;
331
332 --Check for other running migration/setup sessions.
333
334 FOR c_running IN (SELECT action FROM v$session WHERE module IN (
335 'CZMIGRATION',
336 'CZ_PB_MGR',
337 'CZ_MODEL_MIGRATION',
338 'CZIMPORT',
339 CZ_PUBL_SYNC_CRASH.pbSourceClone,
340 CZ_PUBL_SYNC_CRASH.pbTargetClone,
341 CZ_PUBL_SYNC_CRASH.pbSourceCrash,
342 CZ_PUBL_SYNC_CRASH.pbTargetCrash,
343 CZ_RULE_IMPORT.CZRI_MODULE_NAME)) LOOP
344
345 --'Unable to start a new migration session because another migration session or an incompatible concurrent'
346 --'program is currently running.'
347 report(CZ_UTILS.GET_TEXT('CZ_MIGR_SESSION_EXISTS'), URGENCY_ERROR);
348 RETURN FATAL_ERROR;
349 END LOOP;
350
351 DBMS_APPLICATION_INFO.SET_MODULE('CZMIGRATION', TO_CHAR(x_run_id));
352
353 BEGIN
354 SELECT value INTO MigrationStatus FROM cz_db_settings
355 WHERE UPPER(section_name) = 'MIGRATE' AND UPPER(setting_id) = 'MIGRATIONSTATUS';
356 EXCEPTION
357 WHEN NO_DATA_FOUND THEN
358 --'Unable to read the migration status, no record exists. Please make sure the migration setup
359 -- concurrent program has been properly run.'
360 report(CZ_UTILS.GET_TEXT('CZ_MIGR_NO_STATUS_DATA'), URGENCY_ERROR);
361 RETURN FATAL_ERROR;
362 WHEN OTHERS THEN
363 --'Unable to read the migration status because of %ERRORTEXT.'
364 report(CZ_UTILS.GET_TEXT('CZ_MIGR_NO_STATUS', 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
365 RETURN FATAL_ERROR;
366 END;
367
371 EXCEPTION
368 BEGIN
369 SELECT value INTO serverLocalName FROM cz_db_settings
370 WHERE UPPER(section_name) = 'MIGRATE' and UPPER(setting_id) = 'SOURCESERVER';
372 WHEN NO_DATA_FOUND THEN
373 --'Unable to read the source server name, no record exists. Please make sure the migration setup
374 -- concurrent program has been properly run.'
375 report(CZ_UTILS.GET_TEXT('CZ_MIGR_NO_SERVER_NAME'), URGENCY_ERROR);
376 RETURN FATAL_ERROR;
377 WHEN OTHERS THEN
378 --'Unable to read the source server name because of %ERRORTEXT.'
379 report(CZ_UTILS.GET_TEXT('CZ_MIGR_NO_SERVER', 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
380 RETURN FATAL_ERROR;
381 END;
382
383 IF(verify_server_entry(serverLocalName, dbLinkName) = FATAL_ERROR)THEN RETURN FATAL_ERROR; END IF;
384 IF(verify_database_link(dbLinkName) = FATAL_ERROR)THEN RETURN FATAL_ERROR; END IF;
385
386 --Check for the emptiness of the target database. FATAL_ERROR, can be overridden with p_force_run.
387
388 BEGIN
389
390 xError := verify_target_database;
391
392 IF(xError = SKIPPABLE_ERROR)THEN
393 IF(UPPER(MigrationStatus) = 'COMPLETED')THEN
394
395 --'Migration has successfully completed for this database. Running the migration again may cause data corruption.'
396 report(CZ_UTILS.GET_TEXT('CZ_MIGR_COMPLETED_BEFORE'), URGENCY_ERROR);
397 RETURN FATAL_ERROR;
398 ELSIF(UPPER(p_force_run) = 'NO')THEN
399
400 --'The target database is not a fresh installed Applications database, running migration may cause data corruption.'
401 report(CZ_UTILS.GET_TEXT('CZ_MIGR_TARGET_NOT_EMPTY'), URGENCY_ERROR);
402 RETURN FATAL_ERROR;
403 END IF;
404 END IF;
405 EXCEPTION
406 WHEN OTHERS THEN
407 --'Unable to verify the target database: %ERRORTEXT.'
408 report(CZ_UTILS.GET_TEXT('CZ_MIGR_TARGET_VERIFY', 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
409 RETURN FATAL_ERROR;
410 END;
411
412 begin
413 update cz_db_settings set value = 'STARTED'
414 where UPPER(section_name) = 'MIGRATE' and UPPER(setting_id) = 'MIGRATIONSTATUS';
415 exception
416 when others then
417 --'Unable to update the migration status because of %ERRORTEXT.'
418 report(CZ_UTILS.GET_TEXT('CZ_MIGR_NO_STATUS_UPDATE', 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
419 RETURN FATAL_ERROR;
420 end;
421
422 xError := compare_versions;
423
424 if(xError = FATAL_ERROR OR (xError = SKIPPABLE_ERROR and AllowDifferentVersions = 0))then
425
426 RETURN FATAL_ERROR;
427 end if;
428
429 xError := disable_triggers(StopOnSkippable);
430
431 if(xError = FATAL_ERROR OR (xError = SKIPPABLE_ERROR and StopOnSkippable = 1))then
432
433 RETURN FATAL_ERROR;
434 end if;
435
436 --Start of the table-specific data preparation section----------------------------------------------------
437
438 --CZ_SERVERS: we need to make sure that the current server entry would not conflict with a server entry in
439 --the source table. Do this only if CZ_SERVERS is on the processing list and only for 'MIGRATE' operation.
440
441 FOR c_table IN (SELECT NULL FROM cz_xfr_tables
442 WHERE UPPER(dst_table) = 'CZ_SERVERS'
443 AND UPPER(xfr_group) = 'MIGRATE'
444 AND disabled = '0'
445 AND ROWNUM = 1) LOOP
446
447 BEGIN
448
449 EXECUTE IMMEDIATE 'SELECT MAX(server_local_id) FROM cz_servers' INTO targetMaxId;
450 EXECUTE IMMEDIATE 'SELECT MAX(server_local_id) FROM cz_servers@' || dbLinkName INTO sourceMaxId;
451
452 UPDATE cz_servers SET
453 server_local_id = GREATEST(NVL(sourceMaxId, 0), NVL(targetMaxId, 0))
454 WHERE server_local_id = serverLocalId
455 RETURNING server_local_id INTO serverLocalId;
456
457 EXCEPTION
458 WHEN OTHERS THEN
459
460 --'Error in table-specific data preparation for the table ''%TABLENAME'': %ERRORTEXT.'
461 report(CZ_UTILS.GET_TEXT('CZ_MIGR_PREPARE_ERROR', 'TABLENAME', 'CZ_SERVERS', 'ERRORTEXT', SQLERRM), URGENCY_WARNING);
462
463 IF(StopOnSkippable = 1)THEN RETURN FATAL_ERROR; END IF;
464 END;
465 END LOOP;
466
467 --End of the table-specific data preparation section------------------------------------------------------
468
469 FOR c_tables IN (SELECT dst_table FROM cz_xfr_tables
470 WHERE xfr_group IN ('OVERRIDE', 'SLOWREFRESH', 'MIGRATE', 'REFRESH', 'FORCESLOW')
471 AND disabled = '0' AND pk_useexpansion = 'Y') LOOP
472
473 EXECUTE IMMEDIATE 'ALTER TABLE ' || CONFIGURATOR_SCHEMA || '.' || c_tables.dst_table || ' NOLOGGING';
474 END LOOP;
475
476 xError := copy_all_tables(CommitSize, StopOnSkippable, AllowRefresh, ForceSlowMode, ForceProcess);
477
478 FOR c_tables IN (SELECT dst_table FROM cz_xfr_tables
479 WHERE xfr_group IN ('OVERRIDE', 'SLOWREFRESH', 'MIGRATE', 'REFRESH', 'FORCESLOW')
480 AND disabled = '0' AND pk_useexpansion = 'Y') LOOP
481
482 EXECUTE IMMEDIATE 'ALTER TABLE ' || CONFIGURATOR_SCHEMA || '.' || c_tables.dst_table || ' LOGGING';
483 END LOOP;
484
485 if(xError = FATAL_ERROR or (xError = SKIPPABLE_ERROR and StopOnSkippable = 1))then
486
487 xError := enable_triggers(StopOnSkippable);
488
489 if(xError = FATAL_ERROR OR (xError = SKIPPABLE_ERROR and StopOnSkippable = 1))then
493
490
491 RETURN FATAL_ERROR;
492 end if;
494 RETURN FATAL_ERROR;
495 end if;
496
497 xError := enable_triggers(StopOnSkippable);
498
499 if(xError = FATAL_ERROR OR (xError = SKIPPABLE_ERROR and StopOnSkippable = 1))then
500
501 RETURN FATAL_ERROR;
502 end if;
503
504 xError := adjust_all_sequences(StopOnSkippable);
505
506 if(xError = FATAL_ERROR OR (xError = SKIPPABLE_ERROR and StopOnSkippable = 1))then
507
508 RETURN FATAL_ERROR;
509 end if;
510
511 --
512 -- copy JRAD documents associated with a given ui_def_id +++
513 --
514
515 ----check if a pub sync or publishing process is running on the instance
516 ----from which the data is being migrated.
517 OPEN l_src_sessn_cur FOR 'SELECT count(*) FROM v$session@'||dbLinkName|| ' t where t.module = ''PUBLISH_MODEL''';
518 LOOP
519 FETCH l_src_sessn_cur INTO srcPbSessionCnt;
520 EXIT WHEN l_src_sessn_cur%NOTFOUND;
521 IF (srcPbSessionCnt > 0) THEN
522 ----'An existing sync or publishing process is in progress. Re run after these processes complete.'
523 report(CZ_UTILS.GET_TEXT('CZ_PB_SYNC_PROCESS_EXISTS'), URGENCY_ERROR);
524 RETURN FATAL_ERROR;
525 END IF;
526 END LOOP;
527 CLOSE l_src_sessn_cur;
528
529 EXECUTE IMMEDIATE
530 'begin DBMS_APPLICATION_INFO.SET_MODULE@'||dbLinkName||'(:1, :2); end;' USING 'PUBLISH_MODEL',TO_CHAR(x_run_id) ;
531
532 FOR i IN(SELECT ui_def_id FROM CZ_UI_DEFS
533 WHERE deleted_flag='0' AND ui_style IN(G_OA_UIMT_STYLE,G_OA_UI_STYLE))
534 LOOP
535 import_jrad_docs (p_ui_def_id => i.ui_def_id,
536 p_link_name => dbLinkName,
537 x_return_status => xReturnStatus,
538 x_msg_count => xMsgCount,
539 x_msg_data => xMsgData);
540
541 IF(xReturnStatus = FND_API.G_RET_STS_ERROR)THEN
542
543 --Bug #4058286 - do not stop if a jrad document is missing.
544
545 report(xMsgData, URGENCY_WARNING);
546
547 ELSIF(xReturnStatus = FND_API.G_RET_STS_UNEXP_ERROR)THEN
548
549 report(xMsgData, URGENCY_ERROR);
550 RETURN FATAL_ERROR;
551 END IF;
552 END LOOP;
553
554 import_template_jrad_docs (p_link_name => dbLinkName,
555 x_return_status => xReturnStatus,
556 x_msg_count => xMsgCount,
557 x_msg_data => xMsgData);
558
559 IF(xReturnStatus = FND_API.G_RET_STS_ERROR)THEN
560
561 --Bug #4058286 - do not stop if a jrad document is missing.
562
563 report(xMsgData, URGENCY_WARNING);
564
565 ELSIF(xReturnStatus = FND_API.G_RET_STS_UNEXP_ERROR)THEN
566
567 report(xMsgData, URGENCY_ERROR);
568 RETURN FATAL_ERROR;
569 END IF;
570
571 -----reset module info on the target
572 EXECUTE IMMEDIATE
573 'begin DBMS_APPLICATION_INFO.SET_MODULE@'||dbLinkName||'(:1, :2); end;' USING '','' ;
574
575 begin
576 update cz_db_settings set value='COMPLETED'
577 where section_name='MIGRATE' and setting_id='MigrationStatus';
578 exception
579 when others then
580 --'Migration was unable to update the migration status because of %ERRORTEXT.'
581 report(CZ_UTILS.GET_TEXT('CZ_MIGR_NO_STATUS_COMPLETE', 'ERRORTEXT', SQLERRM), URGENCY_WARNING);
582 RETURN SKIPPABLE_ERROR;
583 end;
584
585 if(DeleteDbLink = 1)then
586 declare
587 cdyn integer;
588 rdyn integer;
589 begin
590 cdyn := dbms_sql.open_cursor;
591 dbms_sql.parse(cdyn,'drop database link '||dbLinkName,dbms_sql.native);
592 rdyn := dbms_sql.execute(cdyn);
593 dbms_sql.close_cursor(cdyn);
594 exception
595 when others then
596 if(dbms_sql.is_open(cdyn))then dbms_sql.close_cursor(cdyn); end if;
597 --'Migration was unable to delete the database link because of %ERRORTEXT.'
598 report(CZ_UTILS.GET_TEXT('CZ_MIGR_NO_LINK_DELETE', 'ERRORTEXT', SQLERRM), URGENCY_WARNING);
599 RETURN SKIPPABLE_ERROR;
600 end;
601 end if;
602 RETURN NO_ERROR;
603 end;
604 ------------------------------------------------------------------------------------------------------------
605 FUNCTION compare_versions RETURN INTEGER IS
606
607 tMajorVersion cz_db_settings.value%TYPE;
608 tMinorVersion cz_db_settings.value%TYPE;
609 sMajorVersion cz_db_settings.value%TYPE;
610 sMinorVersion cz_db_settings.value%TYPE;
611
612 BEGIN
613
614 BEGIN
615
616 SELECT UPPER(value) INTO tMajorVersion FROM cz_db_settings
617 WHERE UPPER(section_name) = 'SCHEMA'
618 AND UPPER(setting_id) = 'MAJOR_VERSION';
619
620 SELECT UPPER(value) INTO tMinorVersion FROM cz_db_settings
621 WHERE UPPER(section_name) = 'SCHEMA'
622 AND UPPER(setting_id) = 'MINOR_VERSION';
623
624 EXCEPTION
625 WHEN OTHERS THEN
626 --'Unable to read the target schema version settings because of %ERRORTEXT.'
627 report(CZ_UTILS.GET_TEXT('CZ_MIGR_NO_TARGET_VERSION', 'ERRORTEXT', SQLERRM), URGENCY_WARNING);
628 return SKIPPABLE_ERROR;
629 END;
630
631 BEGIN
632
636
633 EXECUTE IMMEDIATE 'SELECT UPPER(value) FROM cz_db_settings@' || dbLinkName ||
634 ' WHERE UPPER(section_name) = ''SCHEMA''' ||
635 ' AND UPPER(setting_id) = ''MAJOR_VERSION''' INTO sMajorVersion;
637
638 EXECUTE IMMEDIATE 'SELECT UPPER(value) FROM cz_db_settings@' || dbLinkName ||
639 ' WHERE UPPER(section_name) = ''SCHEMA''' ||
640 ' AND UPPER(setting_id) = ''MINOR_VERSION''' INTO sMinorVersion;
641
642 EXCEPTION
643 WHEN OTHERS THEN
644 --'Unable to read the source schema version settings because of %ERRORTEXT.'
645 report(CZ_UTILS.GET_TEXT('CZ_MIGR_NO_SOURCE_VERSION', 'ERRORTEXT', SQLERRM), URGENCY_WARNING);
646 return SKIPPABLE_ERROR;
647 END;
648
649 IF(NOT((sMajorVersion IS NOT NULL) AND (tMajorVersion IS NOT NULL) AND
650 (sMinorVersion IS NOT NULL) AND (tMinorVersion IS NOT NULL) AND
651 (sMajorVersion = tMajorVersion) AND
652 (
653 (sMinorVersion = tMinorVersion) OR
654 (
655 sMajorVersion = 14 AND
656 (
657 (sMinorVersion = 'C' AND tMinorVersion = 'D') OR
658 (sMinorVersion = 'C' AND tMinorVersion = 'B')
659 )
660 )
661 )
662 ))THEN
663 --'The source and target schema versions are incompatible. The source schema is at
664 -- version ''%SOURCEVERSION''; the target schema is at version ''%TARGETVERSION''.'
665 report(CZ_UTILS.GET_TEXT('CZ_MIGR_DIFFERENT_VERSIONS', 'SOURCEVERSION', sMajorVersion || LOWER(sMinorVersion),
666 'TARGETVERSION', tMajorVersion || LOWER(tMinorVersion)), URGENCY_WARNING);
667 return SKIPPABLE_ERROR;
668 END IF;
669
670 return NO_ERROR;
671
672 EXCEPTION
673 WHEN OTHERS THEN
674 --'Unable to compare schema versions because of %ERRORTEXT.'
675 report(CZ_UTILS.GET_TEXT('CZ_MIGR_NO_VERSIONS', 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
676 return FATAL_ERROR;
677 END;
678 ------------------------------------------------------------------------------------------------------------
679 function compare_columns(inTableName in varchar2)
680 return integer is
681 cdyn integer;
682 rdyn integer;
683 rpkc integer;
684 ColumnName dbms_sql.varchar2_table;
685 DataType dbms_sql.varchar2_table;
686 Nullable dbms_sql.varchar2_table;
687 DataLength dbms_sql.number_table;
688 begin
689
690 if(NoIntegrityCheck)then return NO_ERROR; end if;
691
692 cdyn := dbms_sql.open_cursor;
693 dbms_sql.parse(cdyn,
694 '(SELECT column_name, data_type, nullable, data_length ' ||
695 ' FROM all_tab_columns ' ||
696 ' WHERE table_name = ''' || upper(inTableName) || '''' ||
697 ' AND owner = ''' || CONFIGURATOR_SCHEMA || '''' ||
698 ' MINUS ' ||
699 ' SELECT column_name, data_type, nullable, data_length ' ||
700 ' FROM all_tab_columns@' || dbLinkName ||
701 ' WHERE table_name = '''|| upper(inTableName) || '''' ||
702 ' AND owner = ''' || CONFIGURATOR_SCHEMA || ''')' ||
703 ' UNION ' ||
704 '(SELECT column_name, data_type, nullable, data_length ' ||
705 ' FROM all_tab_columns@' || dbLinkName ||
706 ' WHERE table_name = ''' || upper(inTableName) || ''''||
707 ' AND owner = ''' || CONFIGURATOR_SCHEMA || '''' ||
708 ' MINUS ' ||
709 ' SELECT column_name, data_type, nullable, data_length ' ||
710 ' FROM all_tab_columns ' ||
711 ' WHERE table_name = ''' || upper(inTableName) || '''' ||
712 ' AND owner = ''' || CONFIGURATOR_SCHEMA || ''')',
713 dbms_sql.native);
714 dbms_sql.define_array(cdyn,1,ColumnName,100,1);
715 dbms_sql.define_array(cdyn,2,DataType,100,1);
716 dbms_sql.define_array(cdyn,3,Nullable,100,1);
717 dbms_sql.define_array(cdyn,4,DataLength,100,1);
718 rdyn := dbms_sql.execute_and_fetch(cdyn);
719 dbms_sql.column_value(cdyn,1,ColumnName);
720 dbms_sql.column_value(cdyn,2,DataType);
721 dbms_sql.column_value(cdyn,3,Nullable);
722 dbms_sql.column_value(cdyn,4,DataLength);
723 dbms_sql.close_cursor(cdyn);
724
725 if(rdyn > 0)then
726
727 --'Definition of the table ''%TABLENAME'' is different in source and target schema.'
728 report(CZ_UTILS.GET_TEXT('CZ_MIGR_DIFFERENT_TABLES', 'TABLENAME', inTableName), URGENCY_WARNING);
729 --'Start of the list of differences for the table ''%TABLENAME'':'
730 report(CZ_UTILS.GET_TEXT('CZ_MIGR_LIST_START', 'TABLENAME', inTableName), URGENCY_WARNING);
731
732 for i in 1..rdyn loop
733
734 --'Column name: ''%COLUMNNAME'', data type: ''%DATATYPE'', nullable: ''%NULLABLE'', data length: ''%DATALENGTH''.'
735 report(CZ_UTILS.GET_TEXT('CZ_MIGR_COLUMNS_DIFF', 'COLUMNNAME', ColumnName(i), 'DATATYPE', DataType(i), 'NULLABLE', Nullable(i), 'DATALENGTH', TO_CHAR(DataLength(i))), URGENCY_WARNING);
736 end loop;
737
738 --'End of the list of differences for the table ''%TABLENAME'':'
739 report(CZ_UTILS.GET_TEXT('CZ_MIGR_LIST_END', 'TABLENAME', inTableName), URGENCY_WARNING);
740 end if;
741
742 rpkc := compare_pk_columns(inTableName);
743
744 if(rpkc = FATAL_ERROR)then return FATAL_ERROR; end if;
745 if((rpkc = SKIPPABLE_ERROR) or (rdyn > 0))then return SKIPPABLE_ERROR; end if;
746 return NO_ERROR;
747
748 exception
749 when others then
753 return FATAL_ERROR;
750 if(dbms_sql.is_open(cdyn))then dbms_sql.close_cursor(cdyn); end if;
751 --'Unable to compare columns for the table ''%TABLENAME'': %ERRORTEXT.'
752 report(CZ_UTILS.GET_TEXT('CZ_MIGR_COLUMNS_COMPARE', 'TABLENAME', inTableName, 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
754 end;
755 ------------------------------------------------------------------------------------------------------------
756 function get_table_columns(inTableName in varchar2, outNamesArray OUT NOCOPY ColumnNameArray)
757 return integer is
758 cursor c_getcolumns is
759 SELECT column_name FROM all_tab_columns
760 WHERE table_name = upper(inTableName)
761 AND owner = CONFIGURATOR_SCHEMA;
762 nCounter integer := 1;
763 begin
764 outNamesArray.delete;
765
766 open c_getcolumns;
767 loop
768 fetch c_getcolumns into outNamesArray(nCounter);
769 exit when c_getcolumns%notfound;
770 nCounter := nCounter + 1;
771 end loop;
772
773 close c_getcolumns;
774 return(nCounter - 1);
775
776 exception
777 when others then
778 if(c_getcolumns%isopen)then close c_getcolumns; end if;
779 --'Unable to retrieve the list of columns for the table ''%TABLENAME'': %ERRORTEXT.'
780 report(CZ_UTILS.GET_TEXT('CZ_MIGR_COLUMNS_UNABLE', 'TABLENAME', inTableName, 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
781 return FATAL_ERROR;
782 end;
783 ------------------------------------------------------------------------------------------------------------
784 function compare_pk_columns(inTableName in varchar2)
785 return integer is
786 cdyn integer;
787 rdyn integer;
788 xerr boolean;
789 pkName all_constraints.constraint_name%type;
790 ColumnName dbms_sql.varchar2_table;
791 cursor c_getpkname is
792 SELECT constraint_name FROM all_constraints
793 WHERE table_name = upper(inTableName)
794 AND owner = CONFIGURATOR_SCHEMA
795 AND constraint_type = 'P';
796 begin
797 open c_getpkname;
798 fetch c_getpkname into pkName;
799 xerr := c_getpkname%found;
800 close c_getpkname;
801
802 rdyn := 0;
803
804 if(xerr)then
805 cdyn := dbms_sql.open_cursor;
806 dbms_sql.parse(cdyn,
807 '(SELECT column_name FROM all_cons_columns ' ||
808 ' WHERE constraint_name = ''' || pkName || '''' ||
809 ' AND owner = ''' || CONFIGURATOR_SCHEMA || '''' ||
810 ' MINUS '||
811 'SELECT column_name FROM all_cons_columns@' || dbLinkName ||
812 ' WHERE constraint_name = ''' || pkName || '''' ||
813 ' AND owner = ''' || CONFIGURATOR_SCHEMA || ''')' ||
814 ' UNION '||
815 '(SELECT column_name FROM all_cons_columns@' || dbLinkName ||
816 ' WHERE constraint_name = ''' || pkName || '''' ||
817 ' AND owner = ''' || CONFIGURATOR_SCHEMA || '''' ||
818 ' MINUS '||
819 'SELECT column_name FROM all_cons_columns ' ||
820 ' WHERE constraint_name = ''' || pkName || '''' ||
821 ' AND owner = ''' || CONFIGURATOR_SCHEMA || ''')',
822 dbms_sql.native);
823 dbms_sql.define_array(cdyn,1,ColumnName,100,1);
824 rdyn := dbms_sql.execute_and_fetch(cdyn);
825 dbms_sql.column_value(cdyn,1,ColumnName);
826 dbms_sql.close_cursor(cdyn);
827 end if;
828
829 if(rdyn > 0)then
830
831 --'Primary key definition for the table ''%TABLENAME'' is different in source and target schema.'
832 report(CZ_UTILS.GET_TEXT('CZ_MIGR_DIFFERENT_PK', 'TABLENAME', inTableName), URGENCY_WARNING);
833 --'Start of the list of primary key differences for the table ''%TABLENAME'':'
834 report(CZ_UTILS.GET_TEXT('CZ_MIGR_PK_START', 'TABLENAME', inTableName), URGENCY_WARNING);
835
836 for i in 1..rdyn loop
837
838 --'The following column is missing from the primary key: ''%COLUMNNAME''.'
839 report(CZ_UTILS.GET_TEXT('CZ_MIGR_PK_DIFF', 'COLUMNNAME', ColumnName(i)), URGENCY_WARNING);
840 end loop;
841
842 --'End of the list of primary key differences for the table ''%TABLENAME'':'
843 report(CZ_UTILS.GET_TEXT('CZ_MIGR_PK_END', 'TABLENAME', inTableName), URGENCY_WARNING);
844 return SKIPPABLE_ERROR;
845 end if;
846
847 return NO_ERROR;
848
849 exception
850 when others then
851 if(dbms_sql.is_open(cdyn))then dbms_sql.close_cursor(cdyn); end if;
852 --'Unable to compare primary key columns for the table ''%TABLENAME'': %ERRORTEXT.'
853 report(CZ_UTILS.GET_TEXT('CZ_MIGR_PK_COMPARE', 'TABLENAME', inTableName, 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
854 return FATAL_ERROR;
855 end;
856 ------------------------------------------------------------------------------------------------------------
857 function get_table_pk_columns(inTableName in varchar2, outNamesArray OUT NOCOPY PkColumnNameArray)
858 return integer is
859 cursor c_getpkcolumns is
860 SELECT column_name FROM all_cons_columns
861 WHERE owner = CONFIGURATOR_SCHEMA
862 AND constraint_name =
863 (SELECT constraint_name FROM all_constraints
864 WHERE table_name = upper(inTableName)
865 AND owner = CONFIGURATOR_SCHEMA
866 AND constraint_type='P');
867 nCounter integer := 1;
868 begin
869 outNamesArray.delete;
870
871 open c_getpkcolumns;
872 loop
873 fetch c_getpkcolumns into outNamesArray(nCounter);
874 exit when c_getpkcolumns%notfound;
875 nCounter := nCounter + 1;
876 end loop;
877
878 close c_getpkcolumns;
882 when others then
879 return(nCounter - 1);
880
881 exception
883 if(c_getpkcolumns%isopen)then close c_getpkcolumns; end if;
884 --'Unable to retrieve the list of primary key columns for the table ''%TABLENAME'': %ERRORTEXT.'
885 report(CZ_UTILS.GET_TEXT('CZ_MIGR_PK_UNABLE', 'TABLENAME', inTableName, 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
886 return FATAL_ERROR;
887 end;
888 ------------------------------------------------------------------------------------------------------------
889 function copy_table(inTableName in varchar2,
890 inCommitSize in pls_integer,
891 inStopOnSkippable in number,
892 inRefreshable in number,
893 inForceSlowMode in number,
894 inForceProcess in number)
895 return integer is
896 nCountLocal number;
897 nCountRemote number;
898 cdyn integer;
899 rdyn integer;
900 begin
901
902 begin
903 cdyn := dbms_sql.open_cursor;
904 dbms_sql.parse(cdyn,'select count(*) from '||inTableName,dbms_sql.native);
905 dbms_sql.define_column(cdyn, 1, nCountLocal);
906 rdyn := dbms_sql.execute_and_fetch(cdyn);
907 dbms_sql.column_value(cdyn, 1, nCountLocal);
908 dbms_sql.close_cursor(cdyn);
909 exception
910 when others then
911 if(dbms_sql.is_open(cdyn))then dbms_sql.close_cursor(cdyn); end if;
912 --'Verification of the table ''%TABLENAME'' in the target schema failed: %ERRORTEXT.'
913 report(CZ_UTILS.GET_TEXT('CZ_MIGR_TARGET_TABLE', 'TABLENAME', inTableName, 'ERRORTEXT', SQLERRM), URGENCY_WARNING);
914 return SKIPPABLE_ERROR;
915 end;
916
917 begin
918 cdyn := dbms_sql.open_cursor;
919 dbms_sql.parse(cdyn,'select count(*) from '||inTableName||'@'||dbLinkName,dbms_sql.native);
920 dbms_sql.define_column(cdyn, 1, nCountRemote);
921 rdyn := dbms_sql.execute_and_fetch(cdyn);
922 dbms_sql.column_value(cdyn, 1, nCountRemote);
923 dbms_sql.close_cursor(cdyn);
924 exception
925 when others then
926 if(dbms_sql.is_open(cdyn))then dbms_sql.close_cursor(cdyn); end if;
927 --'Verification of the table ''%TABLENAME'' in the source schema failed: %ERRORTEXT.'
928 report(CZ_UTILS.GET_TEXT('CZ_MIGR_SOURCE_TABLE', 'TABLENAME', inTableName, 'ERRORTEXT', SQLERRM), URGENCY_WARNING);
929 return SKIPPABLE_ERROR;
930 end;
931
932 if((nCountLocal = nCountRemote) and (inForceProcess = 0))then
933
934 --'Table ''%TABLENAME'' has the same number of records in the source and target schema, the table will be skipped.'
935 report(CZ_UTILS.GET_TEXT('CZ_MIGR_SKIP_TABLE', 'TABLENAME', inTableName), URGENCY_MESSAGE);
936 return NO_ERROR;
937 end if;
938
939 if((nCountLocal = 0) and (inForceSlowMode = 0))then
940
941 --'Fast copy mode is selected for the table ''%TABLENAME''. Number of records in the source table: %SOURCERECORDS.'
942 report(CZ_UTILS.GET_TEXT('CZ_MIGR_COPY_FAST', 'TABLENAME', inTableName, 'SOURCERECORDS', TO_CHAR(nCountRemote)), URGENCY_MESSAGE);
943 return copy_table_fastmode(inTableName,inStopOnskippable);
944 end if;
945
946 if((inRefreshable = 0) and (inForceSlowMode = 0))then
947
948 --'Fast copy mode without refresh is selected for the table ''%TABLENAME''. Number of records in the source table: %SOURCERECORDS.
949 -- Number of records in the target table: %TARGETRECORDS.'
950 report(CZ_UTILS.GET_TEXT('CZ_MIGR_COPY_FASTNOREFRESH', 'TABLENAME', inTableName, 'SOURCERECORDS', TO_CHAR(nCountRemote), 'TARGETRECORDS', TO_CHAR(nCountLocal)), URGENCY_MESSAGE);
951 return copy_table_fastnorefresh(inTableName,inStopOnskippable);
952 end if;
953
954 if((inRefreshable = 1) and (inForceSlowMode = 0))then
955
956 --'Override mode is selected for the table ''%TABLENAME''. Number of records in the source table: %SOURCERECORDS.
957 -- Number of records in the target table: %TARGETRECORDS.'
958 report(CZ_UTILS.GET_TEXT('CZ_MIGR_COPY_FASTREFRESH', 'TABLENAME', inTableName, 'SOURCERECORDS', TO_CHAR(nCountRemote), 'TARGETRECORDS', TO_CHAR(nCountLocal)), URGENCY_MESSAGE);
959 return copy_table_fastrefresh(inTableName,inStopOnskippable);
960 end if;
961
962 --'Slow copy mode is selected for the table ''%TABLENAME''. Number of records in the source table: %SOURCERECORDS.
963 -- Number of records in the target table: %TARGETRECORDS.'
964 report(CZ_UTILS.GET_TEXT('CZ_MIGR_COPY_SLOW', 'TABLENAME', inTableName, 'SOURCERECORDS', TO_CHAR(nCountRemote), 'TARGETRECORDS', TO_CHAR(nCountLocal)), URGENCY_MESSAGE);
965 return copy_table_slowmode(inTableName,inCommitSize,inStopOnskippable,inRefreshable);
966
967 exception
968 when others then
969 --'Unable to copy the table ''%TABLENAME'' : %ERRORTEXT.'
970 report(CZ_UTILS.GET_TEXT('CZ_MIGR_TABLE_ERROR', 'TABLENAME', inTableName, 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
971 return FATAL_ERROR;
972 end;
973 ------------------------------------------------------------------------------------------------------------
974 FUNCTION copy_table_override(inTableName IN VARCHAR2,
975 inStopOnSkippable IN NUMBER)
976 RETURN INTEGER IS
977
978 nCountTarget NUMBER;
979 nCountSource NUMBER;
980 BEGIN
981
982 BEGIN
983 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || inTableName INTO nCountTarget;
984 EXCEPTION
985 WHEN OTHERS THEN
986 --'Verification of the table ''%TABLENAME'' in the target schema failed: %ERRORTEXT.'
987 report(CZ_UTILS.GET_TEXT('CZ_MIGR_TARGET_TABLE', 'TABLENAME', inTableName, 'ERRORTEXT', SQLERRM), URGENCY_WARNING);
991 BEGIN
988 return SKIPPABLE_ERROR;
989 END;
990
992 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || inTableName || '@' || dbLinkName INTO nCountSource;
993 EXCEPTION
994 WHEN OTHERS THEN
995 --'Verification of the table ''%TABLENAME'' in the source schema failed: %ERRORTEXT.'
996 report(CZ_UTILS.GET_TEXT('CZ_MIGR_SOURCE_TABLE', 'TABLENAME', inTableName, 'ERRORTEXT', SQLERRM), URGENCY_WARNING);
997 return SKIPPABLE_ERROR;
998 END;
999
1000 IF(nCountTarget = 0 AND nCountSource = 0)THEN
1001
1002 --'Table ''%TABLENAME'' has no data in the source and target schema, the table will be skipped.'
1003 report(CZ_UTILS.GET_TEXT('CZ_MIGR_TABLE_NO_DATA', 'TABLENAME', inTableName), URGENCY_MESSAGE);
1004 return NO_ERROR;
1005
1006 ELSIF(nCountTarget = 0)THEN
1007
1008 --'Fast copy mode is selected for the table ''%TABLENAME''. Number of records in the source table: %SOURCERECORDS.'
1009 report(CZ_UTILS.GET_TEXT('CZ_MIGR_COPY_FAST', 'TABLENAME', inTableName, 'SOURCERECORDS', TO_CHAR(nCountSource)), URGENCY_MESSAGE);
1010 return copy_table_fastmode(inTableName, inStopOnskippable);
1011 ELSE
1012
1013 --'Override mode is selected for the table ''%TABLENAME''. Number of records in the source table: %SOURCERECORDS.
1014 -- Number of records in the target table: %TARGETRECORDS.'
1015 report(CZ_UTILS.GET_TEXT('CZ_MIGR_COPY_FASTREFRESH', 'TABLENAME', inTableName, 'SOURCERECORDS', TO_CHAR(nCountSource), 'TARGETRECORDS', TO_CHAR(nCountTarget)), URGENCY_MESSAGE);
1016 return copy_table_fastrefresh(inTableName, inStopOnskippable);
1017 END IF;
1018
1019 EXCEPTION
1020 WHEN OTHERS THEN
1021 --'Unable to copy the table ''%TABLENAME'' : %ERRORTEXT.'
1022 report(CZ_UTILS.GET_TEXT('CZ_MIGR_TABLE_ERROR', 'TABLENAME', inTableName, 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
1023 return FATAL_ERROR;
1024 END copy_table_override;
1025 ------------------------------------------------------------------------------------------------------------
1026 function copy_all_tables(inCommitSize in pls_integer,
1027 inStopOnSkippable in number,
1028 inRefreshable in number,
1029 inForceSlowMode in number,
1030 inForceProcess in number)
1031 return integer is
1032 xError integer;
1033 begin
1034 for c_tables in (select dst_table from cz_xfr_tables where xfr_group='OVERRIDE' and disabled='0') loop
1035 xError := copy_table_override(c_tables.dst_table, inStopOnSkippable);
1036 if(xError = FATAL_ERROR or (xError = SKIPPABLE_ERROR and inStopOnSkippable = 1))then
1037 return xError;
1038 end if;
1039 end loop;
1040 for c_tables in (select dst_table from cz_xfr_tables where xfr_group='MIGRATE' and disabled='0') loop
1041 xError := copy_table(c_tables.dst_table, inCommitSize, inStopOnSkippable, inRefreshable, inForceSlowMode, inForceProcess);
1042 if(xError = FATAL_ERROR or (xError = SKIPPABLE_ERROR and inStopOnSkippable = 1))then
1043 return xError;
1044 end if;
1045 end loop;
1046 for c_tables in (select dst_table from cz_xfr_tables where xfr_group='REFRESH' and disabled='0') loop
1047 xError := copy_table(c_tables.dst_table, inCommitSize, inStopOnSkippable, 1, inForceSlowMode, 1);
1048 if(xError = FATAL_ERROR or (xError = SKIPPABLE_ERROR and inStopOnSkippable = 1))then
1049 return xError;
1050 end if;
1051 end loop;
1052 for c_tables in (select dst_table from cz_xfr_tables where xfr_group='FORCESLOW' and disabled='0') loop
1053 xError := copy_table(c_tables.dst_table, inCommitSize, inStopOnSkippable, inRefreshable, 1, inForceProcess);
1054 if(xError = FATAL_ERROR or (xError = SKIPPABLE_ERROR and inStopOnSkippable = 1))then
1055 return xError;
1056 end if;
1057 end loop;
1058 for c_tables in (select dst_table from cz_xfr_tables where xfr_group='SLOWREFRESH' and disabled='0') loop
1059 xError := copy_table(c_tables.dst_table, inCommitSize, inStopOnSkippable, 1, 1, 1);
1060 if(xError = FATAL_ERROR or (xError = SKIPPABLE_ERROR and inStopOnSkippable = 1))then
1061 return xError;
1062 end if;
1063 end loop;
1064 return NO_ERROR;
1065 exception
1066 when others then
1067 --'Error while migrating data: %ERRORTEXT.'
1068 report(CZ_UTILS.GET_TEXT('CZ_MIGR_MIGRATE_ERROR', 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
1069 return FATAL_ERROR;
1070 end;
1071 ------------------------------------------------------------------------------------------------------------
1072 function copy_table_slowmode(inTableName in varchar2,
1073 inCommitSize in pls_integer,
1074 inStopOnSkippable in number,
1075 inRefreshable in number)
1076 return integer is
1077 ColumnNames ColumnNameArray;
1078 PkColumnNames PkColumnNameArray;
1079 cdyn integer;
1080 rdyn integer;
1081 tableInsertStatement varchar2(5120);
1082 tableUpdateStatement varchar2(5120);
1083 begin
1084
1085 rdyn := compare_columns(inTableName);
1086 if(rdyn = FATAL_ERROR or (rdyn = SKIPPABLE_ERROR and inStopOnSkippable = 1))then return rdyn; end if;
1087
1088 rdyn := get_table_columns(inTableName, ColumnNames);
1089 if(rdyn = FATAL_ERROR)then return rdyn; end if;
1090 if(rdyn = 0)then return SKIPPABLE_ERROR; end if;
1091
1092 tableInsertStatement := ' insert into '||inTableName||' (';
1093 for i in ColumnNames.first..ColumnNames.last - 1 loop
1094 tableInsertStatement := tableInsertStatement||ColumnNames(i)||',';
1095 end loop;
1096 tableInsertStatement := tableInsertStatement||ColumnNames(ColumnNames.last)||') values (';
1100 tableInsertStatement := tableInsertStatement||'c_row.'||ColumnNames(ColumnNames.last)||'); ';
1097 for i in ColumnNames.first..ColumnNames.last - 1 loop
1098 tableInsertStatement := tableInsertStatement||'c_row.'||ColumnNames(i)||',';
1099 end loop;
1101
1102 tableUpdateStatement := ' NULL; ';
1103
1104 if(inRefreshable = 1)then
1105
1106 rdyn := get_table_pk_columns(inTableName, PkColumnNames);
1107 if(rdyn = FATAL_ERROR)then return rdyn; end if;
1108
1109 if(rdyn > 0)then
1110 tableUpdateStatement := ' update '||inTableName||' set ';
1111 for i in ColumnNames.first..ColumnNames.last - 1 loop
1112 tableUpdateStatement := tableUpdateStatement||ColumnNames(i)||'=c_row.'||ColumnNames(i)||',';
1113 end loop;
1114 tableUpdateStatement := tableUpdateStatement||ColumnNames(ColumnNames.last)||'=c_row.'||ColumnNames(ColumnNames.last)||' where ';
1115 for i in PkColumnNames.first..PkColumnNames.last - 1 loop
1116 tableUpdateStatement := tableUpdateStatement||PkColumnNames(i)||'=c_row.'||PkColumnNames(i)||' and ';
1117 end loop;
1118 tableUpdateStatement := tableUpdateStatement||PkColumnNames(PkColumnNames.last)||'=c_row.'||PkColumnNames(PkColumnNames.last)||'; ';
1119 end if;
1120 end if;
1121
1122 cdyn := dbms_sql.open_cursor;
1123 dbms_sql.parse(cdyn,
1124 'declare '||
1125 ' nUpdates number := 0; '||
1126 ' nInserts number := 0; '||
1127 ' nCommitCount pls_integer:=0; '||
1128 'begin '||
1129 'for c_row in (select * from '||inTableName||'@'||dbLinkName||') loop '||
1130 ' begin '||
1131 ' if(nCommitCount>=:CommitSize)then '||
1132 ' commit; '||
1133 ' nCommitCount:=0; '||
1134 ' else '||
1135 ' nCommitCount:=nCommitCount+1; '||
1136 ' end if; '||
1137 tableInsertStatement||
1138 ' nInserts := nInserts + 1; '||
1139 ' exception '||
1140 ' when others then '||
1141 ' if((sqlcode between -999 and -900) or '||
1142 ' (sqlcode between -1489 and -1400) or '||
1143 ' sqlcode = -1)then '||
1144 ' if(:Refreshable = 1)then '||
1145 ' begin '||
1146 tableUpdateStatement||
1147 ' nUpdates := nUpdates + 1; '||
1148 ' exception '||
1149 ' when others then '||
1150 ' cz_migrate.report(CZ_UTILS.GET_TEXT(''CZ_MIGR_UNABLE_UPDATE'', ''TABLENAME'', ''' || inTableName || ''', ''ERRORTEXT'', SQLERRM), cz_migrate.URGENCY_WARNING); ' ||
1151 ' if((sqlcode between -999 and -900) or '||
1152 ' (sqlcode between -1489 and -1400) or '||
1153 ' sqlcode = -1)then '||
1154 ' if(:StopOnSkippable = 1)then raise cz_migrate.CZ_MIGR_SKIPPABLE_EXCEPTION; end if; '||
1155 ' else '||
1156 ' raise cz_migrate.CZ_MIGR_FATAL_EXCEPTION; '||
1157 ' end if; '||
1158 ' end; '||
1159 ' else '||
1160 ' cz_migrate.report(CZ_UTILS.GET_TEXT(''CZ_MIGR_UNABLE_INSERT'', ''TABLENAME'', ''' || inTableName || ''', ''ERRORTEXT'', SQLERRM), cz_migrate.URGENCY_WARNING); ' ||
1161 ' if(:StopOnSkippable = 1)then raise cz_migrate.CZ_MIGR_SKIPPABLE_EXCEPTION; end if; '||
1162 ' end if; '||
1163 ' else '||
1164 ' raise cz_migrate.CZ_MIGR_FATAL_EXCEPTION; '||
1165 ' end if; '||
1166 ' end; '||
1167 ' end loop; '||
1168 ' commit; '||
1169 ' cz_migrate.report(CZ_UTILS.GET_TEXT(''CZ_MIGR_COPY_SUCCESS'', ''TABLENAME'', ''' || inTableName || ''', ''INSERTROWS'', TO_CHAR(nInserts), ''UPDATEROWS'', TO_CHAR(nUpdates)), cz_migrate.URGENCY_MESSAGE); ' ||
1170 'end;',
1171 dbms_sql.native);
1172
1173 dbms_sql.bind_variable(cdyn, ':Refreshable', inRefreshable);
1174 dbms_sql.bind_variable(cdyn, ':StopOnSkippable', inStopOnSkippable);
1175 dbms_sql.bind_variable(cdyn, ':CommitSize', inCommitSize);
1176 rdyn := dbms_sql.execute(cdyn);
1177 dbms_sql.close_cursor(cdyn);
1178 commit;
1179 return NO_ERROR;
1180
1181 exception
1182 when CZ_MIGR_SKIPPABLE_EXCEPTION then
1183 if(dbms_sql.is_open(cdyn))then dbms_sql.close_cursor(cdyn); end if;
1184 return SKIPPABLE_ERROR;
1185 when CZ_MIGR_FATAL_EXCEPTION then
1186 if(dbms_sql.is_open(cdyn))then dbms_sql.close_cursor(cdyn); end if;
1187 return FATAL_ERROR;
1188 when OTHERS then
1189 if(dbms_sql.is_open(cdyn))then dbms_sql.close_cursor(cdyn); end if;
1190 --'Unable to copy the table ''%TABLENAME'' in the selected mode: %ERRORTEXT.'
1191 report(CZ_UTILS.GET_TEXT('CZ_MIGR_COPY_ERROR', 'TABLENAME', inTableName, 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
1192 return FATAL_ERROR;
1193 end;
1194 ------------------------------------------------------------------------------------------------------------
1195 function copy_table_fastmode(inTableName in varchar2,
1196 inStopOnSkippable in number)
1197 return integer is
1198 ColumnNames ColumnNameArray;
1199 cdyn integer;
1200 rdyn integer;
1201 tableInsertStatement varchar2(5120);
1202 begin
1203
1204 rdyn := compare_columns(inTableName);
1205 if(rdyn = FATAL_ERROR or (rdyn = SKIPPABLE_ERROR and inStopOnSkippable = 1))then return rdyn; end if;
1206
1207 rdyn := get_table_columns(inTableName, ColumnNames);
1208 if(rdyn = FATAL_ERROR)then return rdyn; end if;
1209 if(rdyn = 0)then return SKIPPABLE_ERROR; end if;
1210
1211 tableInsertStatement := ColumnNames(ColumnNames.first);
1212 for i in ColumnNames.first + 1..ColumnNames.last loop
1213 tableInsertStatement := tableInsertStatement||','||ColumnNames(i);
1214 end loop;
1215
1219
1216 tableInsertStatement := ' insert /*+ APPEND */ into '||inTableName||' ('||
1217 tableInsertStatement||') select '||tableInsertStatement||' from '||
1218 inTableName||'@'||dbLinkName;
1220 cdyn := dbms_sql.open_cursor;
1221 dbms_sql.parse(cdyn,tableInsertStatement,dbms_sql.native);
1222 rdyn := dbms_sql.execute(cdyn);
1223 dbms_sql.close_cursor(cdyn);
1224 commit;
1225
1226 --'Source records are successfully inserted into the target table ''%TABLENAME''.'
1227 report(CZ_UTILS.GET_TEXT('CZ_MIGR_FAST_INSERTED', 'TABLENAME', inTableName), URGENCY_MESSAGE);
1228 return NO_ERROR;
1229
1230 exception
1231 when OTHERS then
1232 if(dbms_sql.is_open(cdyn))then dbms_sql.close_cursor(cdyn); end if;
1233 --'Unable to copy the table ''%TABLENAME'' in the selected mode: %ERRORTEXT.'
1234 report(CZ_UTILS.GET_TEXT('CZ_MIGR_COPY_ERROR', 'TABLENAME', inTableName, 'ERRORTEXT', SQLERRM), URGENCY_WARNING);
1235 if((sqlcode between -999 and -900) or (sqlcode between -1489 and -1400) or sqlcode = -1)then
1236 return SKIPPABLE_ERROR;
1237 else
1238 return FATAL_ERROR;
1239 end if;
1240 end;
1241 ------------------------------------------------------------------------------------------------------------
1242 function copy_table_fastnorefresh(inTableName in varchar2,
1243 inStopOnSkippable in number)
1244 return integer is
1245 ColumnNames ColumnNameArray;
1246 PkColumnNames PkColumnNameArray;
1247 cdyn integer;
1248 rdyn integer;
1249 tableInsertStatement varchar2(5120);
1250 PkString varchar2(5120);
1251 begin
1252
1253 rdyn := compare_columns(inTableName);
1254 if(rdyn = FATAL_ERROR or (rdyn = SKIPPABLE_ERROR and inStopOnSkippable = 1))then return rdyn; end if;
1255
1256 rdyn := get_table_columns(inTableName, ColumnNames);
1257 if(rdyn = FATAL_ERROR)then return rdyn; end if;
1258 if(rdyn = 0)then return SKIPPABLE_ERROR; end if;
1259
1260 tableInsertStatement := ColumnNames(ColumnNames.first);
1261 for i in ColumnNames.first + 1..ColumnNames.last loop
1262 tableInsertStatement := tableInsertStatement||','||ColumnNames(i);
1263 end loop;
1264
1265 tableInsertStatement := ' insert /*+ APPEND */ into '||inTableName||' ('||
1266 tableInsertStatement||') select '||tableInsertStatement||' from '||
1267 inTableName||'@'||dbLinkName;
1268
1269 rdyn := get_table_pk_columns(inTableName, PkColumnNames);
1270 if(rdyn = FATAL_ERROR)then return rdyn; end if;
1271
1272 if(rdyn > 0)then
1273 PkString := PkColumnNames(PkColumnNames.first)||'=remote.'||PkColumnNames(PkColumnNames.first);
1274 for i in PkColumnNames.first + 1..PkColumnNames.last loop
1275 PkString := PkString||' and '||PkColumnNames(i)||'=remote.'||PkColumnNames(i);
1276 end loop;
1277 tableInsertStatement := tableInsertStatement||
1278 ' remote where not exists (select null from '||
1279 inTableName||' where '||PkString||')';
1280 end if;
1281
1282 cdyn := dbms_sql.open_cursor;
1283 dbms_sql.parse(cdyn,tableInsertStatement,dbms_sql.native);
1284 rdyn := dbms_sql.execute(cdyn);
1285 dbms_sql.close_cursor(cdyn);
1286 commit;
1287
1288 --'%INSERTROWS records successfully inserted into the target table ''%TABLENAME''.'
1289 report(CZ_UTILS.GET_TEXT('CZ_MIGR_INSERT_SUCCESS', 'TABLENAME', inTableName, 'INSERTROWS', TO_CHAR(rdyn)), URGENCY_MESSAGE);
1290 return NO_ERROR;
1291
1292 exception
1293 when OTHERS then
1294 if(dbms_sql.is_open(cdyn))then dbms_sql.close_cursor(cdyn); end if;
1295 --'Unable to copy the table ''%TABLENAME'' in the selected mode: %ERRORTEXT.'
1296 report(CZ_UTILS.GET_TEXT('CZ_MIGR_COPY_ERROR', 'TABLENAME', inTableName, 'ERRORTEXT', SQLERRM), URGENCY_WARNING);
1297 if((sqlcode between -999 and -900) or (sqlcode between -1489 and -1400) or sqlcode = -1)then
1298 return SKIPPABLE_ERROR;
1299 else
1300 return FATAL_ERROR;
1301 end if;
1302 end;
1303 ------------------------------------------------------------------------------------------------------------
1304 function copy_table_fastrefresh(inTableName in varchar2,
1305 inStopOnSkippable in number)
1306 return integer is
1307 cdyn integer;
1308 rdyn integer;
1309 begin
1310
1311 cdyn := dbms_sql.open_cursor;
1312 dbms_sql.parse(cdyn, 'TRUNCATE TABLE ' || CONFIGURATOR_SCHEMA || '.' || inTableName, dbms_sql.native);
1313 rdyn := dbms_sql.execute(cdyn);
1314 dbms_sql.close_cursor(cdyn);
1315 commit;
1316
1317 return copy_table_fastmode(inTableName, inStopOnSkippable);
1318
1319 exception
1320 when OTHERS then
1321 if(dbms_sql.is_open(cdyn))then dbms_sql.close_cursor(cdyn); end if;
1322 --'Unable to copy the table ''%TABLENAME'' in the selected mode: %ERRORTEXT.'
1323 report(CZ_UTILS.GET_TEXT('CZ_MIGR_COPY_ERROR', 'TABLENAME', inTableName, 'ERRORTEXT', SQLERRM), URGENCY_WARNING);
1324 if((sqlcode between -999 and -900) or (sqlcode between -1489 and -1400) or sqlcode = -1)then
1325 return SKIPPABLE_ERROR;
1326 else
1327 return FATAL_ERROR;
1328 end if;
1329 end;
1330 ------------------------------------------------------------------------------------------------------------
1331 function disable_triggers(inStopOnSkippable in number)
1332 return integer is
1333 cdyn integer;
1334 rdyn integer;
1335 begin
1336 for c_triggers in (select dst_table from cz_xfr_tables where xfr_group='TRIGGERS' and disabled='0') loop
1337 begin
1341 dbms_sql.close_cursor(cdyn);
1338 cdyn := dbms_sql.open_cursor;
1339 dbms_sql.parse(cdyn,'alter trigger '||c_triggers.dst_table||' disable',dbms_sql.native);
1340 rdyn := dbms_sql.execute(cdyn);
1342 exception
1343 when others then
1344 if(dbms_sql.is_open(cdyn))then dbms_sql.close_cursor(cdyn); end if;
1345 --'Error while disabling triggers: %ERRORTEXT.'
1346 report(CZ_UTILS.GET_TEXT('CZ_MIGR_TRIGGERS_ERROR', 'ERRORTEXT', SQLERRM), URGENCY_WARNING);
1347 if(inStopOnSkippable = 1)then return SKIPPABLE_ERROR; end if;
1348 end;
1349 end loop;
1350 return NO_ERROR;
1351 end;
1352 ------------------------------------------------------------------------------------------------------------
1353 function enable_triggers(inStopOnSkippable in number)
1354 return integer is
1355 cdyn integer;
1356 rdyn integer;
1357 begin
1358 for c_triggers in (select dst_table from cz_xfr_tables where xfr_group='TRIGGERS' and disabled='0') loop
1359 begin
1360 cdyn := dbms_sql.open_cursor;
1361 dbms_sql.parse(cdyn,'alter trigger '||c_triggers.dst_table||' enable',dbms_sql.native);
1362 rdyn := dbms_sql.execute(cdyn);
1363 dbms_sql.close_cursor(cdyn);
1364 exception
1365 when others then
1366 if(dbms_sql.is_open(cdyn))then dbms_sql.close_cursor(cdyn); end if;
1367 --'Error while enabling triggers: %ERRORTEXT.'
1368 report(CZ_UTILS.GET_TEXT('CZ_MIGR_ENABLE_ERROR', 'ERRORTEXT', SQLERRM), URGENCY_WARNING);
1369 if(inStopOnSkippable = 1)then return SKIPPABLE_ERROR; end if;
1370 end;
1371 end loop;
1372 return NO_ERROR;
1373 end;
1374 ------------------------------------------------------------------------------------------------------------
1375 FUNCTION adjust_sequence(sequenceName IN VARCHAR2, tableName IN VARCHAR2, inPkName IN VARCHAR2, p_increment IN NUMBER)
1376 RETURN INTEGER IS
1377 cdyn integer;
1378 rdyn integer;
1379 nMaximum NUMBER;
1380 Operator VARCHAR2(4) := 'MAX';
1381 nextValue NUMBER;
1382 BEGIN
1383
1384 IF(p_increment < 0)THEN Operator := 'MIN'; END IF;
1385
1386 begin
1387 cdyn := dbms_sql.open_cursor;
1388 dbms_sql.parse(cdyn,'SELECT ' || Operator || '(' || inPkName || ') FROM ' || tableName, dbms_sql.native);
1389 dbms_sql.define_column(cdyn, 1, nMaximum);
1390 rdyn := dbms_sql.execute_and_fetch(cdyn);
1391 dbms_sql.column_value(cdyn, 1, nMaximum);
1392 dbms_sql.close_cursor(cdyn);
1393 exception
1394 when others then
1395 if(dbms_sql.is_open(cdyn))then dbms_sql.close_cursor(cdyn); end if;
1396 --'Skipping sequence ''%SEQUENCENAME'', unable to retrieve the last primary key value: %ERRORTEXT.'
1397 report(CZ_UTILS.GET_TEXT('CZ_MIGR_SEQUENCE_MAX', 'SEQUENCENAME', sequenceName, 'ERRORTEXT', SQLERRM), URGENCY_WARNING);
1398 return SKIPPABLE_ERROR;
1399 end;
1400
1401 IF((p_increment = 0) OR (nMaximum is null))THEN
1402
1403 --'Skipping sequence ''%SEQUENCENAME'', no adjustment is required.'
1404 report(CZ_UTILS.GET_TEXT('CZ_MIGR_SEQUENCE_SKIP', 'SEQUENCENAME', sequenceName), URGENCY_MESSAGE);
1405 return NO_ERROR;
1406 END IF;
1407
1408 BEGIN
1409 EXECUTE IMMEDIATE 'SELECT ' || CONFIGURATOR_SCHEMA || '.' || sequenceName || '.NEXTVAL FROM DUAL' INTO nextValue;
1410
1411 IF(SIGN(p_increment) * (nextValue - nMaximum) < 0)THEN
1412
1413 EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || CONFIGURATOR_SCHEMA || '.' || sequenceName || ' INCREMENT BY ' || (p_increment * CEIL(ABS((nextValue - nMaximum) / p_increment)));
1414 EXECUTE IMMEDIATE 'SELECT ' || CONFIGURATOR_SCHEMA || '.' || sequenceName || '.NEXTVAL FROM DUAL' INTO nextValue;
1415 EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || CONFIGURATOR_SCHEMA || '.' || sequenceName || ' INCREMENT BY ' || p_increment;
1416 END IF;
1417 EXCEPTION
1418 WHEN OTHERS THEN
1419 --'Skipping sequence ''%SEQUENCENAME'', unable to adjust: %ERRORTEXT.'
1420 report(CZ_UTILS.GET_TEXT('CZ_MIGR_SEQUENCE_ADJUST', 'SEQUENCENAME', sequenceName, 'ERRORTEXT', SQLERRM), URGENCY_WARNING);
1421 RETURN SKIPPABLE_ERROR;
1422 END;
1423
1424 COMMIT;
1425
1426 --'Sequence ''%SEQUENCENAME'' adjusted.'
1427 report(CZ_UTILS.GET_TEXT('CZ_MIGR_SEQUENCE_READY', 'SEQUENCENAME', sequenceName), URGENCY_MESSAGE);
1428 RETURN NO_ERROR;
1429
1430 exception
1431 when others then
1432 --'Fatal error while adjusting sequence ''%SEQUENCENAME'': %ERRORTEXT.'
1433 report(CZ_UTILS.GET_TEXT('CZ_MIGR_SEQUENCE_ERROR', 'SEQUENCENAME', sequenceName, 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
1434 return FATAL_ERROR;
1435 END;
1436 ------------------------------------------------------------------------------------------------------------
1437 FUNCTION adjust_all_sequences(inStopOnSkippable IN NUMBER)
1438 RETURN INTEGER IS
1439 xError INTEGER;
1440 BEGIN
1441
1442 --CZ_XFR_TABLES:
1443 -- SRC_TABLE - sequence name;
1444 -- DST_TABLE - corresponding table name;
1445 -- DST_SUBSCHEMA - corresponding table column name (the sequence is used to generate values for this column);
1446 -- FILTERSYNTAX - character representation of the sequence increment.
1447
1448 FOR c_seq IN (SELECT src_table, dst_table, dst_subschema, filtersyntax FROM cz_xfr_tables
1449 WHERE xfr_group = 'SEQUENCES' AND disabled = '0') LOOP
1450
1451 xError := adjust_sequence(c_seq.src_table, c_seq.dst_table, c_seq.dst_subschema, TO_NUMBER(c_seq.filtersyntax));
1455 RETURN NO_ERROR;
1452 IF(xError = FATAL_ERROR OR (xError = SKIPPABLE_ERROR AND inStopOnSkippable = 1))THEN RETURN xError; END IF;
1453 END LOOP;
1454
1456 END adjust_all_sequences;
1457 ------------------------------------------------------------------------------------------------------------
1458 PROCEDURE report(inMessage IN VARCHAR2, inUrgency IN PLS_INTEGER) IS
1459 BEGIN
1460 cz_utils.log_report('cz_migrate', null, thisStatusCode, inMessage, fnd_log.LEVEL_ERROR);
1461
1462 --Bug #4347347.
1463
1464 IF(FND_GLOBAL.CONC_REQUEST_ID > 0)THEN
1465 FND_FILE.PUT_LINE(FND_FILE.LOG, inMessage);
1466 END IF;
1467 EXCEPTION
1468 WHEN OTHERS THEN
1469 RAISE CZ_MIGR_UNABLE_TO_REPORT;
1470 END report;
1471 ------------------------------------------------------------------------------------------------------------
1472 --This procedure is not dynamic and significantly uses the specifics of the CZ database.
1473 --In general, any change to this procedure will require a matching change in the
1474 --verify_target_database procedure. Please review both procedures for any change.
1475
1476 PROCEDURE adjust_specific_control IS
1477
1478 migrateConfigData PLS_INTEGER;
1479 BEGIN
1480
1481 --Bug #2458532 - read the db setting.
1482
1483 BEGIN
1484 SELECT DECODE(UPPER(value), '1', 1, 'ON', 1, 'Y', 1, 'YES', 1,'TRUE', 1, 'ENABLE', 1,
1485 '0', 0, 'OFF', 0, 'N', 0, 'NO', 0,'FALSE', 0, 'DISABLE', 0,
1486 0) --the default value.
1487 INTO migrateConfigData FROM cz_db_settings
1488 WHERE UPPER(section_name) = 'MIGRATE' and UPPER(setting_id) = 'MIGRATECONFIGDATA';
1489 EXCEPTION
1490 WHEN OTHERS THEN
1491 migrateConfigData := 0; --the default behaviour.
1492 END;
1493
1494 --A. Tsyaston 14-Oct-2004 BUG 3937232 - List revised.
1495
1496 UPDATE cz_xfr_tables SET disabled = '1'
1497 WHERE UPPER(xfr_group) = 'OVERRIDE'
1498 AND UPPER(dst_table) IN
1499 ('CZ_XFR_TABLES', 'CZ_ATP_REQUESTS', 'CZ_DB_LOGS', 'CZ_EXP_TMP_LINES', 'CZ_TERMINATE_MSGS',
1500 'CZ_PRICING_STRUCTURES', 'CZ_ITEM_PARENTS', 'CZ_INTL_TEXTS', 'CZ_LOOKUP_VALUES_TL',
1501 'CZ_LOOKUP_VALUES', 'CZ_TYPE_RELATIONSHIPS');
1502
1503 UPDATE cz_xfr_tables SET disabled = '1'
1504 WHERE UPPER(xfr_group) = 'OVERRIDE'
1505 AND UPPER(dst_table) LIKE 'CZ/_IMP/_%' ESCAPE '/';
1506
1507 UPDATE cz_xfr_tables SET disabled = '1'
1508 WHERE UPPER(xfr_group) = 'OVERRIDE'
1509 AND UPPER(dst_table) NOT LIKE 'CZ/_%' ESCAPE '/';
1510
1511 --Bug #2458532 - disable migrating of the configuration data. Just disable copying the table data,
1512 --do not worry about sequences and triggers.
1513
1514 IF(migrateConfigData = 0)THEN
1515
1516 UPDATE cz_xfr_tables SET disabled = '1'
1517 WHERE UPPER(xfr_group) = 'OVERRIDE'
1518 AND UPPER(dst_table) IN
1519 ('CZ_CONFIG_ATTRIBUTES', 'CZ_CONFIG_EXT_ATTRIBUTES', 'CZ_CONFIG_HDRS', 'CZ_CONFIG_INPUTS',
1520 'CZ_CONFIG_ITEMS', 'CZ_CONFIG_MESSAGES', 'CZ_CONFIG_USAGES');
1521 END IF;
1522
1523 --CZ_XFR_FIELDS moved to the 'SLOWREFRESH' group, bug #3620255 (original #3283062).
1524
1525 UPDATE cz_xfr_tables SET xfr_group = 'SLOWREFRESH'
1526 WHERE UPPER(xfr_group) = 'OVERRIDE'
1527 AND UPPER(dst_table) IN ('CZ_DB_SETTINGS', 'CZ_XFR_FIELDS');
1528
1529 UPDATE cz_xfr_tables SET xfr_group = 'MIGRATE'
1530 WHERE UPPER(xfr_group) = 'OVERRIDE'
1531 AND UPPER(dst_table) = 'CZ_SERVERS';
1532
1533 UPDATE cz_xfr_tables SET disabled = '1'
1534 WHERE UPPER(xfr_group) = 'TRIGGERS'
1535 AND UPPER(dst_table) NOT LIKE 'CZ/_%' ESCAPE '/';
1536
1537 INSERT INTO cz_xfr_tables
1538 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1539 VALUES
1540 (1, 'SEQUENCES', '0', 'CZ_ADDRESSES_S', 'CZ_ADDRESSES', 'ADDRESS_ID', '20');
1541
1542 INSERT INTO cz_xfr_tables
1543 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1544 VALUES
1545 (2, 'SEQUENCES', '0', 'CZ_ADDRESS_USES_S', 'CZ_ADDRESS_USES', 'ADDRESS_USE_ID', '20');
1546
1547 INSERT INTO cz_xfr_tables
1548 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1549 VALUES
1550 (4, 'SEQUENCES', '0', 'CZ_COMBO_FEATURES_S', 'CZ_COMBO_FEATURES', 'FEATURE_ID', '20');
1551
1552 INSERT INTO cz_xfr_tables
1553 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1554 VALUES
1555 (5, 'SEQUENCES', '0', 'CZ_CONFIG_HDRS_S', 'CZ_CONFIG_HDRS', 'CONFIG_HDR_ID', '20');
1556
1557 INSERT INTO cz_xfr_tables
1558 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1559 VALUES
1560 (6, 'SEQUENCES', '0', 'CZ_CONFIG_INPUTS_S', 'CZ_CONFIG_INPUTS', 'CONFIG_INPUT_ID', '20');
1561
1562 INSERT INTO cz_xfr_tables
1563 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1564 VALUES
1565 (7, 'SEQUENCES', '0', 'CZ_CONFIG_ITEMS_S', 'CZ_CONFIG_ITEMS', 'CONFIG_ITEM_ID', '20');
1566
1567 INSERT INTO cz_xfr_tables
1568 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1569 VALUES
1570 (8, 'SEQUENCES', '0', 'CZ_CONFIG_MESSAGES_S', 'CZ_CONFIG_MESSAGES', 'MESSAGE_SEQ', '20');
1571
1572 INSERT INTO cz_xfr_tables
1576
1573 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1574 VALUES
1575 (9, 'SEQUENCES', '0', 'CZ_CONTACTS_S', 'CZ_CONTACTS', 'CONTACT_ID', '20');
1577 INSERT INTO cz_xfr_tables
1578 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1579 VALUES
1580 (10, 'SEQUENCES', '0', 'CZ_CUSTOMERS_S', 'CZ_CUSTOMERS', 'CUSTOMER_ID', '20');
1581
1582 INSERT INTO cz_xfr_tables
1583 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1584 VALUES
1585 (13, 'SEQUENCES', '0', 'CZ_DEVL_PROJECTS_S', 'CZ_DEVL_PROJECTS', 'DEVL_PROJECT_ID', '20');
1586
1587 INSERT INTO cz_xfr_tables
1588 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1589 VALUES
1590 (14, 'SEQUENCES', '0', 'CZ_DRILL_DOWN_ITEMS_S', 'CZ_DRILL_DOWN_ITEMS', 'DD_SEQ_NBR', '20');
1591
1592 INSERT INTO cz_xfr_tables
1593 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1594 VALUES
1595 (15, 'SEQUENCES', '0', 'CZ_EFFECTIVITY_SETS_S', 'CZ_EFFECTIVITY_SETS', 'EFFECTIVITY_SET_ID', '20');
1596
1597 INSERT INTO cz_xfr_tables
1598 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1599 VALUES
1600 (16, 'SEQUENCES', '0', 'CZ_END_USERS_S', 'CZ_END_USERS', 'END_USER_ID', '20');
1601
1602 INSERT INTO cz_xfr_tables
1603 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1604 VALUES
1605 (18, 'SEQUENCES', '0', 'CZ_EXPRESSIONS_S', 'CZ_EXPRESSIONS', 'EXPRESS_ID', '20');
1606
1607 INSERT INTO cz_xfr_tables
1608 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1609 VALUES
1610 (19, 'SEQUENCES', '0', 'CZ_EXPRESSION_NODES_S', 'CZ_EXPRESSION_NODES', 'EXPR_NODE_ID', '20');
1611
1612 INSERT INTO cz_xfr_tables
1613 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1614 VALUES
1615 (20, 'SEQUENCES', '0', 'CZ_FILTER_SETS_S', 'CZ_FILTER_SETS', 'FILTER_SET_ID', '20');
1616
1617 INSERT INTO cz_xfr_tables
1618 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1619 VALUES
1620 (22, 'SEQUENCES', '0', 'CZ_FUNC_COMP_SPECS_S', 'CZ_FUNC_COMP_SPECS', 'FUNC_COMP_ID', '20');
1621
1622 INSERT INTO cz_xfr_tables
1623 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1624 VALUES
1625 (23, 'SEQUENCES', '0', 'CZ_GRID_CELLS_S', 'CZ_GRID_CELLS', 'GRID_CELL_ID', '20');
1626
1627 INSERT INTO cz_xfr_tables
1628 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1629 VALUES
1630 (24, 'SEQUENCES', '0', 'CZ_GRID_COLS_S', 'CZ_GRID_COLS', 'GRID_COL_ID', '20');
1631
1632 INSERT INTO cz_xfr_tables
1633 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1634 VALUES
1635 (25, 'SEQUENCES', '0', 'CZ_GRID_DEFS_S', 'CZ_GRID_DEFS', 'GRID_ID', '20');
1636
1637 INSERT INTO cz_xfr_tables
1638 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1639 VALUES
1640 (26, 'SEQUENCES', '0', 'CZ_INTL_TEXTS_S', 'CZ_LOCALIZED_TEXTS', 'INTL_TEXT_ID', '20');
1641
1642 INSERT INTO cz_xfr_tables
1643 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1644 VALUES
1645 (27, 'SEQUENCES', '0', 'CZ_ITEM_MASTERS_S', 'CZ_ITEM_MASTERS', 'ITEM_ID', '20');
1646
1647 INSERT INTO cz_xfr_tables
1648 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1649 VALUES
1650 (30, 'SEQUENCES', '0', 'CZ_ITEM_TYPES_S', 'CZ_ITEM_TYPES', 'ITEM_TYPE_ID', '20');
1651
1652 INSERT INTO cz_xfr_tables
1653 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1654 VALUES
1655 (32, 'SEQUENCES', '0', 'CZ_LCE_HEADERS_S', 'CZ_LCE_HEADERS', 'LCE_HEADER_ID', '20');
1656
1657 INSERT INTO cz_xfr_tables
1658 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1659 VALUES
1660 (33, 'SEQUENCES', '0', 'CZ_LCE_LINES_S', 'CZ_LCE_LINES', 'LCE_LINE_ID', '20');
1661
1662 INSERT INTO cz_xfr_tables
1663 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1664 VALUES
1665 (34, 'SEQUENCES', '0', 'CZ_LCE_OPERANDS_S', 'CZ_LCE_OPERANDS', 'OPERAND_SEQ', '20');
1666
1667 INSERT INTO cz_xfr_tables
1668 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1669 VALUES
1670 (35, 'SEQUENCES', '0', 'CZ_LOCALES_S', 'CZ_LOCALES', 'LOCALE_ID', '20');
1671
1672 INSERT INTO cz_xfr_tables
1673 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1674 VALUES
1675 (37, 'SEQUENCES', '0', 'CZ_MODEL_PUBLICATIONS_S', 'CZ_MODEL_PUBLICATIONS', 'PUBLICATION_ID', '20');
1676
1677 INSERT INTO cz_xfr_tables
1678 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1679 VALUES
1680 (38, 'SEQUENCES', '0', 'CZ_MODEL_REF_EXPLS_S', 'CZ_MODEL_REF_EXPLS', 'MODEL_REF_EXPL_ID', '20');
1681
1682 INSERT INTO cz_xfr_tables
1683 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1684 VALUES
1688 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1685 (39, 'SEQUENCES', '0', 'CZ_MODEL_USAGES_S', 'CZ_MODEL_USAGES', 'MODEL_USAGE_ID', '1');
1686
1687 INSERT INTO cz_xfr_tables
1689 VALUES
1690 (40, 'SEQUENCES', '0', 'CZ_OPPORTUNITY_HDRS_S', 'CZ_OPPORTUNITY_HDRS', 'OPPORTUNITY_HDR_ID', '20');
1691
1692 INSERT INTO cz_xfr_tables
1693 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1694 VALUES
1695 (42, 'SEQUENCES', '0', 'CZ_PB_MODEL_EXPORTS_S', 'CZ_PB_MODEL_EXPORTS', 'EXPORT_ID', '1');
1696
1697 INSERT INTO cz_xfr_tables
1698 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1699 VALUES
1700 (43, 'SEQUENCES', '0', 'CZ_POPULATORS_S', 'CZ_POPULATORS', 'POPULATOR_ID', '20');
1701
1702 INSERT INTO cz_xfr_tables
1703 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1704 VALUES
1705 (44, 'SEQUENCES', '0', 'CZ_POPULATOR_MAPS_S', 'CZ_POPULATOR_MAPS', 'POP_MAP_ID', '20');
1706
1707 INSERT INTO cz_xfr_tables
1708 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1709 VALUES
1710 (46, 'SEQUENCES', '0', 'CZ_PRICE_GROUPS_S', 'CZ_PRICE_GROUPS', 'PRICE_GROUP_ID', '20');
1711
1712 INSERT INTO cz_xfr_tables
1713 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1714 VALUES
1715 (47, 'SEQUENCES', '0', 'CZ_PROPERTIES_S', 'CZ_PROPERTIES', 'PROPERTY_ID', '20');
1716
1717 INSERT INTO cz_xfr_tables
1718 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1719 VALUES
1720 (48, 'SEQUENCES', '0', 'CZ_PROPOSAL_HDRS_S', 'CZ_PROPOSAL_HDRS', 'PROPOSAL_HDR_ID', '20');
1721
1722 INSERT INTO cz_xfr_tables
1723 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1724 VALUES
1725 (50, 'SEQUENCES', '0', 'CZ_PSNODE_PROPCOMPAT_GENS_S', 'CZ_PSNODE_PROPCOMPAT_GENS', 'COMPAT_RUN', '20');
1726
1727 INSERT INTO cz_xfr_tables
1728 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1729 VALUES
1730 (51, 'SEQUENCES', '0', 'CZ_PS_NODES_S', 'CZ_PS_NODES', 'PS_NODE_ID', '20');
1731
1732 INSERT INTO cz_xfr_tables
1733 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1734 VALUES
1735 (53, 'SEQUENCES', '0', 'CZ_QUOTE_HDRS_S', 'CZ_QUOTE_HDRS', 'QUOTE_HDR_ID', '20');
1736
1737 INSERT INTO cz_xfr_tables
1738 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1739 VALUES
1740 (56, 'SEQUENCES', '0', 'CZ_QUOTE_SPARES_S', 'CZ_QUOTE_SPARES', 'SEQ_NUMBER', '20');
1741
1742 INSERT INTO cz_xfr_tables
1743 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1744 VALUES
1745 (57, 'SEQUENCES', '0', 'CZ_QUOTE_SPECIAL_ITEMS_S', 'CZ_QUOTE_SPECIAL_ITEMS', 'SEQ_NUMBER', '20');
1746
1747 INSERT INTO cz_xfr_tables
1748 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1749 VALUES
1750 (58, 'SEQUENCES', '0', 'CZ_REL_TYPES_S', 'CZ_REL_TYPES', 'REL_TYPE_ID', '20');
1751
1752 INSERT INTO cz_xfr_tables
1753 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1754 VALUES
1755 (59, 'SEQUENCES', '0', 'CZ_RP_ENTRIES_S', 'CZ_RP_ENTRIES', 'OBJECT_ID', '20');
1756
1757 INSERT INTO cz_xfr_tables
1758 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1759 VALUES
1760 (60, 'SEQUENCES', '0', 'CZ_RULES_S', 'CZ_RULES', 'RULE_ID', '20');
1761
1762 INSERT INTO cz_xfr_tables
1763 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1764 VALUES
1765 (61, 'SEQUENCES', '0', 'CZ_RULE_FOLDERS_S', 'CZ_RULE_FOLDERS', 'RULE_FOLDER_ID', '20');
1766
1767 INSERT INTO cz_xfr_tables
1768 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1769 VALUES
1770 (62, 'SEQUENCES', '0', 'CZ_SERVERS_S', 'CZ_SERVERS', 'SERVER_LOCAL_ID', '20');
1771
1772 INSERT INTO cz_xfr_tables
1773 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1774 VALUES
1775 (63, 'SEQUENCES', '0', 'CZ_SPARES_SPECIALS_S', 'CZ_SPARES_SPECIALS', 'PACKAGE_SEQ', '20');
1776
1777 INSERT INTO cz_xfr_tables
1778 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1779 VALUES
1780 (64, 'SEQUENCES', '0', 'CZ_SUB_CON_SETS_S', 'CZ_SUB_CON_SETS', 'SUB_CONS_ID', '20');
1781
1782 INSERT INTO cz_xfr_tables
1783 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1784 VALUES
1785 (65, 'SEQUENCES', '0', 'CZ_TERMINATE_MSGS_S', 'CZ_TERMINATE_MSGS', 'MSG_ID', '20');
1786
1787 INSERT INTO cz_xfr_tables
1788 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1789 VALUES
1790 (66, 'SEQUENCES', '0', 'CZ_UI_DEFS_S', 'CZ_UI_DEFS', 'UI_DEF_ID', '20');
1791
1792 INSERT INTO cz_xfr_tables
1793 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1794 VALUES
1795 (67, 'SEQUENCES', '0', 'CZ_UI_NODES_S', 'CZ_UI_NODES', 'UI_NODE_ID', '20');
1796
1797 INSERT INTO cz_xfr_tables
1801
1798 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1799 VALUES
1800 (70, 'SEQUENCES', '0', 'CZ_USER_GROUPS_S', 'CZ_USER_GROUPS', 'USER_GROUP_ID', '20');
1802 INSERT INTO cz_xfr_tables
1803 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1804 VALUES
1805 (71, 'SEQUENCES', '0', 'CZ_XFR_PROJECT_BILLS_S', 'CZ_XFR_PROJECT_BILLS', 'MODEL_PS_NODE_ID', '-1');
1806
1807 INSERT INTO cz_xfr_tables
1808 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1809 VALUES
1810 (72, 'SEQUENCES', '0', 'CZ_XFR_RUN_INFOS_S', 'CZ_XFR_RUN_INFOS', 'RUN_ID', '1');
1811
1812 --A. Tsyaston 14-Oct-2004 BUG 3937232 - Sequences added for synchronization.
1813
1814 INSERT INTO cz_xfr_tables
1815 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1816 VALUES
1817 (73, 'SEQUENCES', '0', 'CZ_ARCHIVES_S', 'CZ_ARCHIVES', 'ARCHIVE_ID', '20');
1818
1819 INSERT INTO cz_xfr_tables
1820 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1821 VALUES
1822 (74, 'SEQUENCES', '0', 'CZ_SIGNATURES_S', 'CZ_SIGNATURES', 'SIGNATURE_ID', '20');
1823
1824 INSERT INTO cz_xfr_tables
1825 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1826 VALUES
1827 (75, 'SEQUENCES', '0', 'CZ_UI_ACTIONS_S', 'CZ_UI_ACTIONS', 'UI_ACTION_ID', '20');
1828
1829 INSERT INTO cz_xfr_tables
1830 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1831 VALUES
1832 (76, 'SEQUENCES', '0', 'CZ_UI_PAGES_S', 'CZ_UI_PAGES', 'PAGE_ID', '20');
1833
1834 INSERT INTO cz_xfr_tables
1835 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1836 VALUES
1837 (77, 'SEQUENCES', '0', 'CZ_UI_PAGE_ELEMENTS_S', 'CZ_UI_PAGE_ELEMENTS', 'ELEMENT_ID', '20');
1838
1839 INSERT INTO cz_xfr_tables
1840 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1841 VALUES
1842 (78, 'SEQUENCES', '0', 'CZ_UI_PAGE_REFS_S', 'CZ_UI_PAGE_REFS', 'PAGE_REF_ID', '20');
1843
1844 INSERT INTO cz_xfr_tables
1845 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1846 VALUES
1847 (79, 'SEQUENCES', '0', 'CZ_UI_PAGE_SETS_S', 'CZ_UI_PAGE_SETS', 'PAGE_SET_ID', '20');
1848
1849 INSERT INTO cz_xfr_tables
1850 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1851 VALUES
1852 (80, 'SEQUENCES', '0', 'CZ_UI_TEMPLATES_S', 'CZ_UI_TEMPLATES', 'TEMPLATE_ID', '20');
1853
1854 END adjust_specific_control;
1855 ------------------------------------------------------------------------------------------------------------
1856 --This procedure is not dynamic and significantly uses the specifics of the CZ database.
1857 --In general, any change to this procedure will require a matching change in the
1858 --adjust_specific_control procedure. Please review both procedures for any change.
1859
1860 FUNCTION verify_target_database RETURN PLS_INTEGER IS
1861
1862 TYPE tabTableName IS TABLE OF all_tables.table_name%TYPE;
1863
1864 --A. Tsyaston 14-Oct-2004 BUG 3937232 - List revised.
1865 --These tables must be empty:
1866
1867 noRecords tabTableName :=
1868 tabTableName(
1869 'CZ_ADDRESSES', 'CZ_ADDRESS_USES', 'CZ_ARCHIVES', 'CZ_ARCHIVE_REFS', 'CZ_COMBO_FEATURES', 'CZ_CONFIG_ATTRIBUTES',
1870 'CZ_CONFIG_EXT_ATTRIBUTES', 'CZ_CONFIG_HDRS', 'CZ_CONFIG_INPUTS', 'CZ_CONFIG_MESSAGES', 'CZ_CONFIG_USAGES',
1871 'CZ_CONTACTS', 'CZ_CUSTOMER_END_USERS', 'CZ_DB_SIZES', 'CZ_DES_CHART_CELLS', 'CZ_DES_CHART_COLUMNS',
1872 'CZ_DES_CHART_FEATURES', 'CZ_DEVL_PRJ_USER_GROUPS', 'CZ_DRILL_DOWN_ITEMS', 'CZ_EFFECTIVITY_SETS', 'CZ_EXPRESSIONS',
1873 'CZ_FILTER_SETS', 'CZ_FUNC_COMP_REFS', 'CZ_FUNC_COMP_SPECS', 'CZ_GRID_CELLS', 'CZ_GRID_COLS', 'CZ_GRID_DEFS',
1874 'CZ_ITEM_MASTERS', 'CZ_ITEM_PROPERTY_VALUES', 'CZ_ITEM_TYPE_PROPERTIES', 'CZ_JRAD_CHUNKS', 'CZ_LCE_CLOBS',
1875 'CZ_LCE_HEADERS', 'CZ_LCE_LINES', 'CZ_LCE_LOAD_SPECS', 'CZ_LCE_OPERANDS', 'CZ_LCE_TEXTS', 'CZ_LOCALES',
1876 'CZ_LOCK_HISTORY', 'CZ_MODEL_PUBLICATIONS', 'CZ_MODEL_REF_EXPLS', 'CZ_OPP_HDR_CONTACTS', 'CZ_PB_CLIENT_APPS',
1877 'CZ_PB_LANGUAGES', 'CZ_PB_MODEL_EXPORTS', 'CZ_PB_TEMP_IDS', 'CZ_POPULATOR_MAPS', 'CZ_PRICES', 'CZ_PROPERTIES',
1878 'CZ_PROPOSAL_HDRS', 'CZ_PROP_QUOTE_HDRS', 'CZ_PSNODE_PROPCOMPAT_GENS', 'CZ_PS_NODES', 'CZ_PS_PROP_VALS',
1879 'CZ_PUBLICATION_USAGES', 'CZ_QUOTE_HDRS', 'CZ_QUOTE_MAIN_ITEMS', 'CZ_QUOTE_ORDERS', 'CZ_QUOTE_SPARES',
1880 'CZ_QUOTE_SPECIAL_ITEMS', 'CZ_REL_TYPES', 'CZ_SPARES_SPECIALS', 'CZ_SUB_CON_SETS', 'CZ_UI_NODES',
1881 'CZ_UI_NODE_PROPS', 'CZ_UI_PAGE_ELEMENTS', 'CZ_UI_PAGE_REFS', 'CZ_UI_PAGE_SETS', 'CZ_UI_PROPERTIES', 'CZ_UI_REFS',
1882 'CZ_UI_TEMPLATE_ELEMENTS', 'CZ_UI_XMLS', 'CZ_XFR_FIELD_REQUIRES', 'CZ_XFR_PRICE_LISTS', 'CZ_XFR_PROJECT_BILLS',
1883 'CZ_XFR_RUN_INFOS', 'CZ_XFR_RUN_RESULTS', 'CZ_XFR_STATUS_CODES');
1884
1885 nCount NUMBER;
1886 errorFlag PLS_INTEGER := 0;
1887 BEGIN
1888
1889 FOR i IN 1..noRecords.COUNT LOOP
1890 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || noRecords(i) INTO nCount;
1891 IF(nCount > 0)THEN
1892
1893 --'Table ''%TABLENAME'' is not empty, %RECORDCOUNT records found.'
1894 report(CZ_UTILS.GET_TEXT('CZ_MIGR_TABLE_NOT_EMPTY', 'TABLENAME', noRecords(i), 'RECORDCOUNT', nCount), URGENCY_MESSAGE);
1895 errorFlag := 1;
1896 END IF;
1897 END LOOP;
1898
1899 --A. Tsyaston 14-Oct-2004 BUG 3937232.
1903 IF(errorFlag = 1)THEN RETURN SKIPPABLE_ERROR; END IF;
1900 --It does not add a lot of value to verify tables by number of seeded records. Besides, it makes the
1901 --migration code relatively high maintainance. Removing these verifications as a part of the fix.
1902
1904 RETURN NO_ERROR;
1905 END verify_target_database;
1906
1907 ---------------------------
1908 ----This procedure retrieves the XML chunks (from the source instance)
1909 ----of the JRAD docs that are migrated to the target instance
1910 ----params:
1911 ----p_ui_def_id : ui_def_id of the UI or that in the cz_ui_templates table
1912 ----p_template_id : template id in cz_ui_templates
1913 ----If template id is NULL then the JRAD docs of UI pages passed are extracted
1914 ----If both template_id and ui_def_id are passed in then the JRAD docs of UI templates
1915 ----are extracted
1916 PROCEDURE get_xml_chunks (p_ui_def_id IN NUMBER,
1917 p_template_id IN NUMBER)
1918 IS
1919
1920 l_length BINARY_INTEGER;
1921 l_buffer VARCHAR2(32767);
1922 firstChunk VARCHAR2(32767);
1923 DOCUMENT_IS_NULL EXCEPTION;
1924 l_msg VARCHAR2(2000);
1925 l_sql_code NUMBER := 0;
1926 l_seq_nbr NUMBER := 0;
1927
1928 TYPE chunk_record IS RECORD (jrad_doc VARCHAR2(255),
1929 seq_nbr NUMBER,xml_chunk VARCHAR2(32767));
1930
1931 TYPE chunk_record_tbl IS TABLE OF chunk_record INDEX BY BINARY_INTEGER;
1932 l_chunk_tbl chunk_record_tbl;
1933 l_jrad_doc_tbl jraddoc_type_tbl;
1934 l_exportfinished BOOLEAN;
1935 BEGIN
1936 ----collect jrad docs for UI or templates
1937 BEGIN
1938 IF (p_template_id IS NULL) THEN
1939 SELECT jrad_doc
1940 BULK
1941 COLLECT
1942 INTO l_jrad_doc_tbl
1943 FROM cz_ui_pages
1944 WHERE ui_def_id = p_ui_def_id
1945 AND deleted_flag = '0';
1946 ELSE
1947 SELECT jrad_doc
1948 BULK
1949 COLLECT
1950 INTO l_jrad_doc_tbl
1951 FROM cz_ui_templates
1952 WHERE cz_ui_templates.ui_def_id = p_ui_def_id
1953 AND cz_ui_templates.template_id = p_template_id
1954 AND cz_ui_templates.seeded_flag = '0'
1955 AND cz_ui_templates.deleted_flag = '0';
1956 END IF;
1957 EXCEPTION
1958 WHEN NO_DATA_FOUND THEN
1959 NULL; ---do nothing
1960 END;
1961
1962 IF (l_jrad_doc_tbl.COUNT > 0) THEN
1963 FOR I IN l_jrad_doc_tbl.FIRST..l_jrad_doc_tbl.LAST
1964 LOOP
1965 BEGIN
1966 l_seq_nbr := 0;
1967 jdr_docbuilder.refresh;
1968 IF (l_jrad_doc_tbl(i) IS NULL) THEN
1969 RAISE DOCUMENT_IS_NULL;
1970 END IF;
1971 firstChunk := jdr_utils.EXPORTDOCUMENT(l_jrad_doc_tbl(i),l_exportfinished);
1972 IF (firstChunk IS NULL) THEN
1973 RAISE DOCUMENT_IS_NULL;
1974 END IF;
1975
1976 l_buffer := LTRIM(RTRIM(firstChunk));
1977 IF (l_buffer IS NOT NULL) THEN
1978 l_seq_nbr := l_seq_nbr + 1;
1979 l_chunk_tbl(l_seq_nbr).jrad_doc := l_jrad_doc_tbl(i);
1980 l_chunk_tbl(l_seq_nbr).seq_nbr := l_seq_nbr;
1981 l_chunk_tbl(l_seq_nbr).xml_chunk := l_buffer;
1982 END IF;
1983
1984 LOOP
1985 l_buffer := jdr_utils.EXPORTDOCUMENT(NULL,l_exportfinished);
1986 l_buffer := LTRIM(RTRIM(l_buffer));
1987 EXIT WHEN l_buffer IS NULL;
1988 IF (l_buffer IS NOT NULL) THEN
1989 l_seq_nbr := l_seq_nbr + 1;
1990 l_chunk_tbl(l_seq_nbr).jrad_doc := l_jrad_doc_tbl(i);
1991 l_chunk_tbl(l_seq_nbr).seq_nbr := l_seq_nbr;
1992 l_chunk_tbl(l_seq_nbr).xml_chunk := l_buffer;
1993 END IF;
1994 END LOOP;
1995
1996 IF (l_chunk_tbl.COUNT > 0) THEN
1997 FOR I IN 1..l_seq_nbr
1998 LOOP
1999 insert into cz_jrad_chunks(jrad_doc,seq_nbr,xml_chunk)
2000 values (l_chunk_tbl(i).jrad_doc,l_chunk_tbl(i).seq_nbr,l_chunk_tbl(i).xml_chunk);
2001 END LOOP;
2002 END IF;
2003 commit;
2004 jdr_docbuilder.refresh;
2005 EXCEPTION
2009 RAISE;
2006 WHEN DOCUMENT_IS_NULL THEN
2007 NULL; --- if no documnet exists, then it is OK, do not raise an error
2008 WHEN OTHERS THEN
2010 END;
2011 END LOOP;
2012 END IF;
2013 COMMIT;
2014 END get_xml_chunks ;
2015
2016 ----------------------------------------------------------------------
2020 p_link_name IN VARCHAR2,
2017 -----This procedure imports the JRAD docs
2018 -----of a UI from the source to the target instance
2019 PROCEDURE import_jrad_docs (p_ui_def_id IN NUMBER,
2021 x_return_status OUT NOCOPY VARCHAR2,
2022 x_msg_count OUT NOCOPY NUMBER,
2023 x_msg_data OUT NOCOPY VARCHAR2)
2024 IS
2025 l_link_name cz_servers.fndnam_link_name%TYPE;
2026 l_ref_cursor ref_cursor;
2027 l_jrad_doc cz_jrad_chunks.jrad_doc%TYPE;
2028 l_seq_nbr cz_jrad_chunks.seq_nbr%TYPE;
2029 l_XML_CHUNK VARCHAR2(32767);
2030 l_template_id cz_ui_templates.template_id%TYPE := NULL;
2031 BEGIN
2032 ----initialize link name
2033 IF (p_link_name IS NULL) THEN l_link_name := ' ';
2034 ELSE l_link_name := '@'||p_link_name;
2035 END IF;
2036
2037 ----delete from temp table on target and source
2038 EXECUTE IMMEDIATE
2039 ' begin delete from cz_jrad_chunks'||l_link_name||'; commit; end; ';
2040 delete from cz_jrad_chunks; commit;
2041
2042 ----get XML chunks from the target to be imported
2043 EXECUTE IMMEDIATE 'begin cz_migrate.get_xml_chunks'||l_link_name||'(:1,:2); end;'
2044 USING p_ui_def_id,l_template_id;
2045
2046 ----insert XML chunks from the target to the cz_jrad_chunks table on the source
2047 OPEN l_ref_cursor FOR 'SELECT JRAD_DOC,SEQ_NBR,XML_CHUNK
2048 FROM cz_jrad_chunks'||l_link_name;
2049 LOOP
2050 FETCH l_ref_cursor INTO l_jrad_doc,l_seq_nbr,l_XML_CHUNK;
2051 EXIT WHEN l_ref_cursor%NOTFOUND;
2052 insert into cz_jrad_chunks (JRAD_DOC,SEQ_NBR,XML_CHUNK)
2053 values (l_jrad_doc,l_seq_nbr,l_XML_CHUNK);
2054 END LOOP;
2055 COMMIT;
2056 CLOSE l_ref_cursor;
2057
2058 -----upload the XML to the jrad repository
2059 cz_pb_mgr.insert_jrad_docs;
2060
2061 ----delete from temp table on target and source
2062 EXECUTE IMMEDIATE
2063 ' begin delete from cz_jrad_chunks'||l_link_name||'; commit; end; ';
2064 delete from cz_jrad_chunks; commit;
2065 EXCEPTION
2066 WHEN NO_DATA_FOUND THEN
2067 x_return_status := FND_API.G_RET_STS_ERROR;
2068 x_msg_count := 1;
2069 x_msg_data := CZ_UTILS.GET_TEXT('CZ_NO_JRADDOC_EXISTS','DOC',p_ui_def_id );
2070 WHEN EXPLORETREE_ERROR THEN
2071 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2072 x_msg_count := 1;
2073
2074 fnd_message.set_name('FND', 'FND_AS_UNEXPECTED_ERROR');
2075 fnd_message.set_token('ERROR_TEXT', SQLERRM);
2076 fnd_message.set_token('PKG_NAME', 'CZ_PB_MGR');
2077 fnd_message.set_token('PROCEDURE_NAME', 'INSERT_JRAD_DOCS');
2078
2079 x_msg_data := fnd_message.get;
2080 WHEN OTHERS THEN
2081 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2082 x_msg_count := 1;
2083 x_msg_data := CZ_UTILS.GET_TEXT('CZ_JRADDOC_EXPERR', 'ERR', SQLERRM);
2084 END import_jrad_docs;
2085
2086 ----------------------------------
2087 -----This procedure imports all the JRAD docs
2088 -----of a UI templates from the source to the target instance
2089 PROCEDURE import_template_jrad_docs (p_link_name IN VARCHAR2,
2090 x_return_status OUT NOCOPY VARCHAR2,
2091 x_msg_count OUT NOCOPY NUMBER,
2092 x_msg_data OUT NOCOPY VARCHAR2)
2093 IS
2094 TYPE t_ref IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2095 l_ui_def_id_tbl t_ref;
2096 l_template_id_tbl t_ref;
2097 l_link_name cz_servers.fndnam_link_name%TYPE;
2098 l_ref_cursor ref_cursor;
2099 l_jrad_doc cz_jrad_chunks.jrad_doc%TYPE;
2100 l_seq_nbr cz_jrad_chunks.seq_nbr%TYPE;
2101 l_XML_CHUNK VARCHAR2(32767);
2102
2103 BEGIN
2104 x_return_status := FND_API.G_RET_STS_SUCCESS;
2105 IF (p_link_name IS NULL) THEN l_link_name := ' ';
2106 ELSE l_link_name := '@'||p_link_name;
2107 END IF;
2108
2109 SELECT ui_def_id, template_id
2110 BULK
2111 COLLECT
2112 INTO l_ui_def_id_tbl,l_template_id_tbl
2113 FROM cz_ui_templates
2114 WHERE cz_ui_templates.deleted_flag = '0'
2115 AND cz_ui_templates.seeded_flag = '0'
2116 AND cz_ui_templates.ui_def_id = 0
2117 OR cz_ui_templates.ui_def_id IN (SELECT ui_def_id
2118 FROM cz_ui_defs
2119 WHERE cz_ui_defs.deleted_flag = '0');
2120
2121 IF (l_ui_def_id_tbl.COUNT > 0) THEN
2122 FOR I IN l_ui_def_id_tbl.FIRST..l_ui_def_id_tbl.LAST
2123 LOOP
2124 ----delete from temp table on target and source
2125 EXECUTE IMMEDIATE
2126 ' begin delete from cz_jrad_chunks'||l_link_name||'; commit; end; ';
2127 delete from cz_jrad_chunks; commit;
2128
2129 ----get XML chunks from the target to be imported
2130 EXECUTE IMMEDIATE
2131 'begin cz_migrate.get_xml_chunks'||l_link_name||'(:1,:2); end;'
2132 USING l_ui_def_id_tbl(i),l_template_id_tbl(i);
2133
2134 ----insert XML chunks from the target to the cz_jrad_chunks table on the source
2135 OPEN l_ref_cursor FOR 'SELECT JRAD_DOC,SEQ_NBR,XML_CHUNK
2136 FROM cz_jrad_chunks'||l_link_name;
2137 LOOP
2138 FETCH l_ref_cursor INTO l_jrad_doc,l_seq_nbr,l_XML_CHUNK;
2139 EXIT WHEN l_ref_cursor%NOTFOUND;
2140 insert into cz_jrad_chunks (JRAD_DOC,SEQ_NBR,XML_CHUNK)
2141 values (l_jrad_doc,l_seq_nbr,l_XML_CHUNK);
2142 END LOOP;
2143 COMMIT;
2144 CLOSE l_ref_cursor;
2145
2146 -----upload the XML to the jrad repository
2147 cz_pb_mgr.insert_jrad_docs;
2148
2149 ----delete from temp table on target and source
2150 EXECUTE IMMEDIATE
2151 ' begin delete from cz_jrad_chunks'||l_link_name||'; commit; end; ';
2152 delete from cz_jrad_chunks; commit;
2153 END LOOP;
2154 END IF;
2155 EXCEPTION
2156 WHEN NO_DATA_FOUND THEN
2157 NULL;
2158 WHEN EXPLORETREE_ERROR THEN
2159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2160 x_msg_count := 1;
2161
2162 fnd_message.set_name('FND', 'FND_AS_UNEXPECTED_ERROR');
2163 fnd_message.set_token('ERROR_TEXT', SQLERRM);
2164 fnd_message.set_token('PKG_NAME', 'CZ_PB_MGR');
2165 fnd_message.set_token('PROCEDURE_NAME', 'INSERT_JRAD_DOCS');
2166
2167 x_msg_data := fnd_message.get;
2168 WHEN OTHERS THEN
2169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2170 x_msg_data := SQLERRM;
2171 END import_template_jrad_docs ;
2172 ------------------------------------------------------------------------------------------------------------
2173 END cz_migrate;