[Home] [Help]
PACKAGE BODY: APPS.CZ_MIGRATE
Source
4 G_OA_UI_STYLE VARCHAR2(1) := '7';
1 PACKAGE BODY cz_migrate AS
2 /* $Header: czmigrb.pls 120.6 2012/02/28 21:41:40 smanna ship $ */
3
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
68
65 IF(xError = FATAL_ERROR)THEN
66
67 retcode := CONCURRENT_ERROR;
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
171 --No setting is normal.
168 WHERE UPPER(section_name) = 'MIGRATE' AND UPPER(setting_id) = 'MIGRATIONSTATUS';
169 EXCEPTION
170 WHEN NO_DATA_FOUND THEN
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;
183
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.
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;
319
316 thisStatusCode := MIGRATE_STATUS_CODE;
317 thisRunId := NVL(x_run_id, GENERIC_RUN_ID);
318 NoIntegrityCheck := TRUE;
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
335 'CZMIGRATION',
332 --Check for other running migration/setup sessions.
333
334 FOR c_running IN (SELECT action FROM v$session WHERE module IN (
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
368 BEGIN
369 SELECT value INTO serverLocalName FROM cz_db_settings
370 WHERE UPPER(section_name) = 'MIGRATE' and UPPER(setting_id) = 'SOURCESERVER';
371 EXCEPTION
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
429 xError := disable_triggers(StopOnSkippable);
426 RETURN FATAL_ERROR;
427 end if;
428
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
485 if(xError = FATAL_ERROR or (xError = SKIPPABLE_ERROR and StopOnSkippable = 1))then
482 EXECUTE IMMEDIATE 'ALTER TABLE ' || CONFIGURATOR_SCHEMA || '.' || c_tables.dst_table || ' LOGGING';
483 END LOOP;
484
486
487 xError := enable_triggers(StopOnSkippable);
488
489 if(xError = FATAL_ERROR OR (xError = SKIPPABLE_ERROR and StopOnSkippable = 1))then
490
491 RETURN FATAL_ERROR;
492 end if;
493
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
545 report(xMsgData, URGENCY_WARNING);
542
543 --Bug #4058286 - do not stop if a jrad document is missing.
544
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
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;
636
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 (
656 (
653 (sMinorVersion = tMinorVersion) OR
654 (
655 sMajorVersion = 14 AND
657 (sMinorVersion = 'C' AND tMinorVersion = 'D') OR
658 (sMinorVersion = 'C' AND tMinorVersion = 'B')
662 ))THEN
659 )
660 )
661 )
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
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);
753 return FATAL_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;
792 SELECT constraint_name FROM all_constraints
789 pkName all_constraints.constraint_name%type;
790 ColumnName dbms_sql.varchar2_table;
791 cursor c_getpkname is
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 ' ||
811 'SELECT column_name FROM all_cons_columns@' || dbLinkName ||
808 ' WHERE constraint_name = ''' || pkName || '''' ||
809 ' AND owner = ''' || CONFIGURATOR_SCHEMA || '''' ||
810 ' MINUS '||
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;
879 return(nCounter - 1);
880
881 exception
882 when others then
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.'
888 ------------------------------------------------------------------------------------------------------------
885 report(CZ_UTILS.GET_TEXT('CZ_MIGR_PK_UNABLE', 'TABLENAME', inTableName, 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
886 return FATAL_ERROR;
887 end;
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
957 -- Number of records in the target table: %TARGETRECORDS.'
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.
961
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;
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);
988 return SKIPPABLE_ERROR;
989 END;
990
991 BEGIN
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);
1072 function copy_table_slowmode(inTableName in varchar2,
1069 return FATAL_ERROR;
1070 end;
1071 ------------------------------------------------------------------------------------------------------------
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 (';
1097 for i in ColumnNames.first..ColumnNames.last - 1 loop
1098 tableInsertStatement := tableInsertStatement||'c_row.'||ColumnNames(i)||',';
1099 end loop;
1100 tableInsertStatement := tableInsertStatement||'c_row.'||ColumnNames(ColumnNames.last)||'); ';
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;
1197 return integer is
1194 ------------------------------------------------------------------------------------------------------------
1195 function copy_table_fastmode(inTableName in varchar2,
1196 inStopOnSkippable in number)
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
1216 tableInsertStatement := ' insert /*+ APPEND */ into '||inTableName||' ('||
1217 tableInsertStatement||') select '||tableInsertStatement||' from '||
1218 inTableName||'@'||dbLinkName;
1219
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);
1327 return FATAL_ERROR;
1324 if((sqlcode between -999 and -900) or (sqlcode between -1489 and -1400) or sqlcode = -1)then
1325 return SKIPPABLE_ERROR;
1326 else
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
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);
1341 dbms_sql.close_cursor(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
1454
1451 xError := adjust_sequence(c_seq.src_table, c_seq.dst_table, c_seq.dst_subschema, TO_NUMBER(c_seq.filtersyntax));
1452 IF(xError = FATAL_ERROR OR (xError = SKIPPABLE_ERROR AND inStopOnSkippable = 1))THEN RETURN xError; END IF;
1453 END LOOP;
1455 RETURN NO_ERROR;
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
1532
1529 UPDATE cz_xfr_tables SET xfr_group = 'MIGRATE'
1530 WHERE UPPER(xfr_group) = 'OVERRIDE'
1531 AND UPPER(dst_table) = 'CZ_SERVERS';
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
1571
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');
1572 INSERT INTO cz_xfr_tables
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');
1576
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
1685 (39, 'SEQUENCES', '0', 'CZ_MODEL_USAGES_S', 'CZ_MODEL_USAGES', 'MODEL_USAGE_ID', '1');
1686
1687 INSERT INTO cz_xfr_tables
1688 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1689 VALUES
1693 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1690 (40, 'SEQUENCES', '0', 'CZ_OPPORTUNITY_HDRS_S', 'CZ_OPPORTUNITY_HDRS', 'OPPORTUNITY_HDR_ID', '20');
1691
1692 INSERT INTO cz_xfr_tables
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
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');
1801
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
1815 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1812 --A. Tsyaston 14-Oct-2004 BUG 3937232 - Sequences added for synchronization.
1813
1814 INSERT INTO cz_xfr_tables
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 INSERT INTO cz_xfr_tables
1854 (order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
1855 VALUES
1856 (81, 'SEQUENCES', '0', 'CZ_FCE_FILES_S', 'CZ_FCE_FILES', 'FCE_FILE_ID', '20');
1857
1858 END adjust_specific_control;
1859 ------------------------------------------------------------------------------------------------------------
1860 --This procedure is not dynamic and significantly uses the specifics of the CZ database.
1861 --In general, any change to this procedure will require a matching change in the
1862 --adjust_specific_control procedure. Please review both procedures for any change.
1863
1864 FUNCTION verify_target_database RETURN PLS_INTEGER IS
1865
1866 TYPE tabTableName IS TABLE OF all_tables.table_name%TYPE;
1867
1868 --A. Tsyaston 14-Oct-2004 BUG 3937232 - List revised.
1869 --These tables must be empty:
1870
1871 noRecords tabTableName :=
1872 tabTableName(
1873 'CZ_ADDRESSES', 'CZ_ADDRESS_USES', 'CZ_ARCHIVES', 'CZ_ARCHIVE_REFS', 'CZ_COMBO_FEATURES', 'CZ_CONFIG_ATTRIBUTES',
1874 'CZ_CONFIG_EXT_ATTRIBUTES', 'CZ_CONFIG_HDRS', 'CZ_CONFIG_INPUTS', 'CZ_CONFIG_MESSAGES', 'CZ_CONFIG_USAGES',
1875 'CZ_CONTACTS', 'CZ_CUSTOMER_END_USERS', 'CZ_DB_SIZES', 'CZ_DES_CHART_CELLS', 'CZ_DES_CHART_COLUMNS',
1876 'CZ_DES_CHART_FEATURES', 'CZ_DEVL_PRJ_USER_GROUPS', 'CZ_DRILL_DOWN_ITEMS', 'CZ_EFFECTIVITY_SETS', 'CZ_EXPRESSIONS',
1877 'CZ_FILTER_SETS', 'CZ_FUNC_COMP_REFS', 'CZ_FUNC_COMP_SPECS', 'CZ_GRID_CELLS', 'CZ_GRID_COLS', 'CZ_GRID_DEFS',
1878 'CZ_ITEM_MASTERS', 'CZ_ITEM_PROPERTY_VALUES', 'CZ_ITEM_TYPE_PROPERTIES', 'CZ_JRAD_CHUNKS', 'CZ_LCE_CLOBS',
1879 'CZ_LCE_HEADERS', 'CZ_FCE_FILES','CZ_LCE_LINES', 'CZ_LCE_LOAD_SPECS', 'CZ_LCE_OPERANDS', 'CZ_LCE_TEXTS', 'CZ_LOCALES',
1880 'CZ_LOCK_HISTORY', 'CZ_MODEL_PUBLICATIONS', 'CZ_MODEL_REF_EXPLS', 'CZ_OPP_HDR_CONTACTS', 'CZ_PB_CLIENT_APPS',
1881 'CZ_PB_LANGUAGES', 'CZ_PB_MODEL_EXPORTS', 'CZ_PB_TEMP_IDS', 'CZ_POPULATOR_MAPS', 'CZ_PRICES', 'CZ_PROPERTIES',
1882 'CZ_PROPOSAL_HDRS', 'CZ_PROP_QUOTE_HDRS', 'CZ_PSNODE_PROPCOMPAT_GENS', 'CZ_PS_NODES', 'CZ_PS_PROP_VALS',
1883 'CZ_PUBLICATION_USAGES', 'CZ_QUOTE_HDRS', 'CZ_QUOTE_MAIN_ITEMS', 'CZ_QUOTE_ORDERS', 'CZ_QUOTE_SPARES',
1884 'CZ_QUOTE_SPECIAL_ITEMS', 'CZ_REL_TYPES', 'CZ_SPARES_SPECIALS', 'CZ_SUB_CON_SETS', 'CZ_UI_NODES',
1885 'CZ_UI_NODE_PROPS', 'CZ_UI_PAGE_ELEMENTS', 'CZ_UI_PAGE_REFS', 'CZ_UI_PAGE_SETS', 'CZ_UI_PROPERTIES', 'CZ_UI_REFS',
1886 'CZ_UI_TEMPLATE_ELEMENTS', 'CZ_UI_XMLS', 'CZ_XFR_FIELD_REQUIRES', 'CZ_XFR_PRICE_LISTS', 'CZ_XFR_PROJECT_BILLS',
1887 'CZ_XFR_RUN_INFOS', 'CZ_XFR_RUN_RESULTS', 'CZ_XFR_STATUS_CODES');
1888
1889 nCount NUMBER;
1890 errorFlag PLS_INTEGER := 0;
1891 BEGIN
1892
1893 FOR i IN 1..noRecords.COUNT LOOP
1894 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || noRecords(i) INTO nCount;
1895 IF(nCount > 0)THEN
1896
1897 --'Table ''%TABLENAME'' is not empty, %RECORDCOUNT records found.'
1898 report(CZ_UTILS.GET_TEXT('CZ_MIGR_TABLE_NOT_EMPTY', 'TABLENAME', noRecords(i), 'RECORDCOUNT', nCount), URGENCY_MESSAGE);
1899 errorFlag := 1;
1900 END IF;
1901 END LOOP;
1902
1903 --A. Tsyaston 14-Oct-2004 BUG 3937232.
1904 --It does not add a lot of value to verify tables by number of seeded records. Besides, it makes the
1905 --migration code relatively high maintainance. Removing these verifications as a part of the fix.
1906
1907 IF(errorFlag = 1)THEN RETURN SKIPPABLE_ERROR; END IF;
1908 RETURN NO_ERROR;
1909 END verify_target_database;
1910
1911 ---------------------------
1912 ----This procedure retrieves the XML chunks (from the source instance)
1913 ----of the JRAD docs that are migrated to the target instance
1914 ----params:
1915 ----p_ui_def_id : ui_def_id of the UI or that in the cz_ui_templates table
1916 ----p_template_id : template id in cz_ui_templates
1917 ----If template id is NULL then the JRAD docs of UI pages passed are extracted
1918 ----If both template_id and ui_def_id are passed in then the JRAD docs of UI templates
1919 ----are extracted
1920 PROCEDURE get_xml_chunks (p_ui_def_id IN NUMBER,
1921 p_template_id IN NUMBER)
1925 l_buffer VARCHAR2(32767);
1922 IS
1923
1924 l_length BINARY_INTEGER;
1926 firstChunk VARCHAR2(32767);
1927 DOCUMENT_IS_NULL EXCEPTION;
1928 l_msg VARCHAR2(2000);
1929 l_sql_code NUMBER := 0;
1930 l_seq_nbr NUMBER := 0;
1931
1932 TYPE chunk_record IS RECORD (jrad_doc VARCHAR2(255),
1933 seq_nbr NUMBER,xml_chunk VARCHAR2(32767));
1934
1935 TYPE chunk_record_tbl IS TABLE OF chunk_record INDEX BY BINARY_INTEGER;
1936 l_chunk_tbl chunk_record_tbl;
1937 l_jrad_doc_tbl jraddoc_type_tbl;
1938 l_exportfinished BOOLEAN;
1939 BEGIN
1940 ----collect jrad docs for UI or templates
1941 BEGIN
1942 IF (p_template_id IS NULL) THEN
1943 SELECT jrad_doc
1944 BULK
1945 COLLECT
1946 INTO l_jrad_doc_tbl
1947 FROM cz_ui_pages
1948 WHERE ui_def_id = p_ui_def_id
1949 AND deleted_flag = '0';
1950 ELSE
1951 SELECT jrad_doc
1952 BULK
1953 COLLECT
1954 INTO l_jrad_doc_tbl
1955 FROM cz_ui_templates
1956 WHERE cz_ui_templates.ui_def_id = p_ui_def_id
1957 AND cz_ui_templates.template_id = p_template_id
1958 AND cz_ui_templates.seeded_flag = '0'
1959 AND cz_ui_templates.deleted_flag = '0';
1960 END IF;
1961 EXCEPTION
1962 WHEN NO_DATA_FOUND THEN
1963 NULL; ---do nothing
1964 END;
1965
1966 IF (l_jrad_doc_tbl.COUNT > 0) THEN
1967 FOR I IN l_jrad_doc_tbl.FIRST..l_jrad_doc_tbl.LAST
1968 LOOP
1969 BEGIN
1970 l_seq_nbr := 0;
1971 jdr_docbuilder.refresh;
1972 IF (l_jrad_doc_tbl(i) IS NULL) THEN
1973 RAISE DOCUMENT_IS_NULL;
1974 END IF;
1975 firstChunk := jdr_utils.EXPORTDOCUMENT(l_jrad_doc_tbl(i),l_exportfinished);
1976 IF (firstChunk IS NULL) THEN
1977 RAISE DOCUMENT_IS_NULL;
1978 END IF;
1979
1980 l_buffer := LTRIM(RTRIM(firstChunk));
1981 IF (l_buffer IS NOT NULL) THEN
1982 l_seq_nbr := l_seq_nbr + 1;
1983 l_chunk_tbl(l_seq_nbr).jrad_doc := l_jrad_doc_tbl(i);
1984 l_chunk_tbl(l_seq_nbr).seq_nbr := l_seq_nbr;
1985 l_chunk_tbl(l_seq_nbr).xml_chunk := l_buffer;
1986 END IF;
1987
1988 LOOP
1989 l_buffer := jdr_utils.EXPORTDOCUMENT(NULL,l_exportfinished);
1990 l_buffer := LTRIM(RTRIM(l_buffer));
1991 EXIT WHEN l_buffer IS NULL;
1992 IF (l_buffer IS NOT NULL) THEN
1993 l_seq_nbr := l_seq_nbr + 1;
1994 l_chunk_tbl(l_seq_nbr).jrad_doc := l_jrad_doc_tbl(i);
1995 l_chunk_tbl(l_seq_nbr).seq_nbr := l_seq_nbr;
1996 l_chunk_tbl(l_seq_nbr).xml_chunk := l_buffer;
1997 END IF;
1998 END LOOP;
1999
2000 IF (l_chunk_tbl.COUNT > 0) THEN
2001 FOR I IN 1..l_seq_nbr
2002 LOOP
2003 insert into cz_jrad_chunks(jrad_doc,seq_nbr,xml_chunk,publication_id)
2004 values (l_chunk_tbl(i).jrad_doc,l_chunk_tbl(i).seq_nbr,l_chunk_tbl(i).xml_chunk,0);
2005 END LOOP;
2006 END IF;
2007 commit;
2008 jdr_docbuilder.refresh;
2009 EXCEPTION
2010 WHEN DOCUMENT_IS_NULL THEN
2011 NULL; --- if no documnet exists, then it is OK, do not raise an error
2012 WHEN OTHERS THEN
2013 RAISE;
2014 END;
2015 END LOOP;
2016 END IF;
2017 COMMIT;
2018 END get_xml_chunks ;
2019
2020 ----------------------------------------------------------------------
2021 -----This procedure imports the JRAD docs
2022 -----of a UI from the source to the target instance
2023 --- Bug#13781204 : We have made a change in cz_pb_mgr.insert_jrad_docs where we are
2024 --passing an additional parameter publication_id to isolate the jrad chunks
2025 --specific to a publication. But this is not required for schema migration.
2026 --Since we are using the existing code from publication we are just passing 0 as
2027 --publication id during jrad chunk population and passing the same 0 during
2028 --insert_jrad_docs to match it. Which means actually the same logic for migration.
2029 --The same logic for template jrad doc as well
2030
2031 PROCEDURE import_jrad_docs (p_ui_def_id IN NUMBER,
2032 p_link_name IN VARCHAR2,
2033 x_return_status OUT NOCOPY VARCHAR2,
2034 x_msg_count OUT NOCOPY NUMBER,
2035 x_msg_data OUT NOCOPY VARCHAR2)
2036 IS
2037 l_link_name cz_servers.fndnam_link_name%TYPE;
2038 l_ref_cursor ref_cursor;
2039 l_jrad_doc cz_jrad_chunks.jrad_doc%TYPE;
2040 l_seq_nbr cz_jrad_chunks.seq_nbr%TYPE;
2041 l_XML_CHUNK VARCHAR2(32767);
2042 l_template_id cz_ui_templates.template_id%TYPE := NULL;
2043 BEGIN
2044 ----initialize link name
2045 IF (p_link_name IS NULL) THEN l_link_name := ' ';
2046 ELSE l_link_name := '@'||p_link_name;
2047 END IF;
2048
2049 ----delete from temp table on target and source
2050 EXECUTE IMMEDIATE
2051 ' begin delete from cz_jrad_chunks'||l_link_name||'; commit; end; ';
2052 delete from cz_jrad_chunks; commit;
2053
2054 ----get XML chunks from the target to be imported
2055 EXECUTE IMMEDIATE 'begin cz_migrate.get_xml_chunks'||l_link_name||'(:1,:2); end;'
2056 USING p_ui_def_id,l_template_id;
2057
2058 ----insert XML chunks from the target to the cz_jrad_chunks table on the source
2059 OPEN l_ref_cursor FOR 'SELECT JRAD_DOC,SEQ_NBR,XML_CHUNK
2060 FROM cz_jrad_chunks'||l_link_name;
2061 LOOP
2062 FETCH l_ref_cursor INTO l_jrad_doc,l_seq_nbr,l_XML_CHUNK;
2063 EXIT WHEN l_ref_cursor%NOTFOUND;
2064 insert into cz_jrad_chunks (JRAD_DOC,SEQ_NBR,XML_CHUNK,PUBLICATION_ID)
2065 values (l_jrad_doc,l_seq_nbr,l_XML_CHUNK,0);
2066 END LOOP;
2067 COMMIT;
2068 CLOSE l_ref_cursor;
2069
2070 -----upload the XML to the jrad repository
2074 EXECUTE IMMEDIATE
2071 cz_pb_mgr.insert_jrad_docs(0);
2072
2073 ----delete from temp table on target and source
2075 ' begin delete from cz_jrad_chunks'||l_link_name||'; commit; end; ';
2076 delete from cz_jrad_chunks; commit;
2077 EXCEPTION
2078 WHEN NO_DATA_FOUND THEN
2079 x_return_status := FND_API.G_RET_STS_ERROR;
2080 x_msg_count := 1;
2081 x_msg_data := CZ_UTILS.GET_TEXT('CZ_NO_JRADDOC_EXISTS','DOC',p_ui_def_id );
2082 WHEN EXPLORETREE_ERROR THEN
2083 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2084 x_msg_count := 1;
2085
2086 fnd_message.set_name('FND', 'FND_AS_UNEXPECTED_ERROR');
2087 fnd_message.set_token('ERROR_TEXT', SQLERRM);
2088 fnd_message.set_token('PKG_NAME', 'CZ_PB_MGR');
2089 fnd_message.set_token('PROCEDURE_NAME', 'INSERT_JRAD_DOCS');
2090
2091 x_msg_data := fnd_message.get;
2092 WHEN OTHERS THEN
2093 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2094 x_msg_count := 1;
2095 x_msg_data := CZ_UTILS.GET_TEXT('CZ_JRADDOC_EXPERR', 'ERR', SQLERRM);
2096 END import_jrad_docs;
2097
2098 ----------------------------------
2099 -----This procedure imports all the JRAD docs
2100 -----of a UI templates from the source to the target instance
2101 PROCEDURE import_template_jrad_docs (p_link_name IN VARCHAR2,
2102 x_return_status OUT NOCOPY VARCHAR2,
2103 x_msg_count OUT NOCOPY NUMBER,
2104 x_msg_data OUT NOCOPY VARCHAR2)
2105 IS
2106 TYPE t_ref IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2107 l_ui_def_id_tbl t_ref;
2108 l_template_id_tbl t_ref;
2109 l_link_name cz_servers.fndnam_link_name%TYPE;
2110 l_ref_cursor ref_cursor;
2111 l_jrad_doc cz_jrad_chunks.jrad_doc%TYPE;
2112 l_seq_nbr cz_jrad_chunks.seq_nbr%TYPE;
2113 l_XML_CHUNK VARCHAR2(32767);
2114
2115 BEGIN
2116 x_return_status := FND_API.G_RET_STS_SUCCESS;
2117 IF (p_link_name IS NULL) THEN l_link_name := ' ';
2118 ELSE l_link_name := '@'||p_link_name;
2119 END IF;
2120
2121 SELECT ui_def_id, template_id
2125 FROM cz_ui_templates
2122 BULK
2123 COLLECT
2124 INTO l_ui_def_id_tbl,l_template_id_tbl
2126 WHERE cz_ui_templates.deleted_flag = '0'
2127 AND cz_ui_templates.seeded_flag = '0'
2128 AND cz_ui_templates.ui_def_id = 0
2129 OR cz_ui_templates.ui_def_id IN (SELECT ui_def_id
2130 FROM cz_ui_defs
2131 WHERE cz_ui_defs.deleted_flag = '0');
2132
2133 IF (l_ui_def_id_tbl.COUNT > 0) THEN
2134 FOR I IN l_ui_def_id_tbl.FIRST..l_ui_def_id_tbl.LAST
2135 LOOP
2136 ----delete from temp table on target and source
2137 EXECUTE IMMEDIATE
2138 ' begin delete from cz_jrad_chunks'||l_link_name||'; commit; end; ';
2139 delete from cz_jrad_chunks; commit;
2140
2141 ----get XML chunks from the target to be imported
2142 EXECUTE IMMEDIATE
2143 'begin cz_migrate.get_xml_chunks'||l_link_name||'(:1,:2); end;'
2144 USING l_ui_def_id_tbl(i),l_template_id_tbl(i);
2145
2146 ----insert XML chunks from the target to the cz_jrad_chunks table on the source
2147 OPEN l_ref_cursor FOR 'SELECT JRAD_DOC,SEQ_NBR,XML_CHUNK
2148 FROM cz_jrad_chunks'||l_link_name;
2149 LOOP
2150 FETCH l_ref_cursor INTO l_jrad_doc,l_seq_nbr,l_XML_CHUNK;
2151 EXIT WHEN l_ref_cursor%NOTFOUND;
2152 insert into cz_jrad_chunks (JRAD_DOC,SEQ_NBR,XML_CHUNK,PUBLICATION_ID)
2153 values (l_jrad_doc,l_seq_nbr,l_XML_CHUNK,0);
2154 END LOOP;
2155 COMMIT;
2156 CLOSE l_ref_cursor;
2157
2158 -----upload the XML to the jrad repository
2159 cz_pb_mgr.insert_jrad_docs(0);
2160
2161 ----delete from temp table on target and source
2162 EXECUTE IMMEDIATE
2163 ' begin delete from cz_jrad_chunks'||l_link_name||'; commit; end; ';
2164 delete from cz_jrad_chunks; commit;
2165 END LOOP;
2166 END IF;
2167 EXCEPTION
2168 WHEN NO_DATA_FOUND THEN
2169 NULL;
2170 WHEN EXPLORETREE_ERROR THEN
2171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2172 x_msg_count := 1;
2173
2174 fnd_message.set_name('FND', 'FND_AS_UNEXPECTED_ERROR');
2175 fnd_message.set_token('ERROR_TEXT', SQLERRM);
2176 fnd_message.set_token('PKG_NAME', 'CZ_PB_MGR');
2177 fnd_message.set_token('PROCEDURE_NAME', 'INSERT_JRAD_DOCS');
2178
2179 x_msg_data := fnd_message.get;
2180 WHEN OTHERS THEN
2181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2182 x_msg_data := SQLERRM;
2183 END import_template_jrad_docs ;
2184 ------------------------------------------------------------------------------------------------------------
2185 END cz_migrate;