The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT server_local_id, fndnam_link_name INTO serverLocalId, x_link_name
FROM cz_servers WHERE UPPER(local_name) = UPPER(p_local_name);
EXECUTE IMMEDIATE 'SELECT SYSDATE FROM DUAL@' || p_link_name;
SELECT cz_xfr_run_infos_s.NEXTVAL INTO x_run_id FROM DUAL;
FOR c_running IN (SELECT action FROM v$session WHERE module IN (
'CZMIGRATION',
'CZ_PB_MGR',
'CZ_MODEL_MIGRATION',
'CZIMPORT',
CZ_PUBL_SYNC_CRASH.pbSourceClone,
CZ_PUBL_SYNC_CRASH.pbTargetClone,
CZ_PUBL_SYNC_CRASH.pbSourceCrash,
CZ_PUBL_SYNC_CRASH.pbTargetCrash,
CZ_RULE_IMPORT.CZRI_MODULE_NAME)) LOOP
--'Unable to start a new migration session because another migration session or an incompatible concurrent'
--'program is currently running.'
report(CZ_UTILS.GET_TEXT('CZ_MIGR_SESSION_EXISTS'), URGENCY_ERROR);
SELECT value INTO MigrationStatus FROM cz_db_settings
WHERE UPPER(section_name) = 'MIGRATE' AND UPPER(setting_id) = 'MIGRATIONSTATUS';
DELETE FROM cz_xfr_tables WHERE UPPER(xfr_group) IN
('OVERRIDE', 'SLOWREFRESH', 'MIGRATE', 'REFRESH', 'FORCESLOW', 'TRIGGERS', 'SEQUENCES');
INSERT INTO cz_xfr_tables (order_seq, xfr_group, disabled, dst_table, pk_useexpansion)
SELECT ROWNUM, 'OVERRIDE', '0', table_name, DECODE(logging, 'YES', 'Y', 'NO', 'N', 'N')
FROM all_tables
WHERE owner = 'CZ';
INSERT INTO cz_xfr_tables (order_seq, xfr_group, disabled, dst_table)
SELECT ROWNUM, 'TRIGGERS', '0', trigger_name
FROM user_triggers
WHERE status = 'ENABLED'
AND trigger_name LIKE 'CZ/_%' ESCAPE '/';
FOR c_tables IN (SELECT dst_table FROM cz_xfr_tables
WHERE xfr_group in ('OVERRIDE', 'SLOWREFRESH', 'MIGRATE', 'REFRESH', 'FORCESLOW')
AND disabled = '0') LOOP
xError := compare_columns(c_tables.dst_table);
INSERT INTO cz_db_settings (section_name, setting_id, data_type, value)
SELECT 'MIGRATE', 'SourceServer', 4, p_local_name FROM DUAL WHERE NOT EXISTS
(SELECT NULL FROM cz_db_settings
WHERE UPPER(section_name) = 'MIGRATE' and UPPER(setting_id) = 'SOURCESERVER');
UPDATE cz_db_settings SET value = p_local_name
WHERE UPPER(section_name) = 'MIGRATE' AND UPPER(setting_id) = 'SOURCESERVER';
INSERT INTO cz_db_settings (section_name, setting_id, data_type, value)
SELECT 'MIGRATE', 'MigrationStatus', 4, 'INSTALLED' FROM DUAL WHERE NOT EXISTS
(SELECT NULL FROM cz_db_settings
WHERE UPPER(section_name) = 'MIGRATE' and UPPER(setting_id) = 'MIGRATIONSTATUS');
UPDATE cz_db_settings SET value = 'INSTALLED'
WHERE UPPER(section_name) = 'MIGRATE' AND UPPER(setting_id) = 'MIGRATIONSTATUS';
report(CZ_UTILS.GET_TEXT('CZ_MIGR_NO_STATUS_UPDATE', 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
DeleteDbLink in number default 0
*/
FUNCTION migrate(x_run_id IN OUT NOCOPY PLS_INTEGER,
p_force_run IN VARCHAR2,
CommitSize in pls_integer,
StopOnSkippable in number,
ForceSlowMode in number,
AllowDifferentVersions in number,
AllowRefresh in number,
ForceProcess in number,
DeleteDbLink in number)
RETURN INTEGER IS
xError INTEGER;
SELECT cz_xfr_run_infos_s.NEXTVAL INTO x_run_id FROM DUAL;
FOR c_running IN (SELECT action FROM v$session WHERE module IN (
'CZMIGRATION',
'CZ_PB_MGR',
'CZ_MODEL_MIGRATION',
'CZIMPORT',
CZ_PUBL_SYNC_CRASH.pbSourceClone,
CZ_PUBL_SYNC_CRASH.pbTargetClone,
CZ_PUBL_SYNC_CRASH.pbSourceCrash,
CZ_PUBL_SYNC_CRASH.pbTargetCrash,
CZ_RULE_IMPORT.CZRI_MODULE_NAME)) LOOP
--'Unable to start a new migration session because another migration session or an incompatible concurrent'
--'program is currently running.'
report(CZ_UTILS.GET_TEXT('CZ_MIGR_SESSION_EXISTS'), URGENCY_ERROR);
SELECT value INTO MigrationStatus FROM cz_db_settings
WHERE UPPER(section_name) = 'MIGRATE' AND UPPER(setting_id) = 'MIGRATIONSTATUS';
SELECT value INTO serverLocalName FROM cz_db_settings
WHERE UPPER(section_name) = 'MIGRATE' and UPPER(setting_id) = 'SOURCESERVER';
update cz_db_settings set value = 'STARTED'
where UPPER(section_name) = 'MIGRATE' and UPPER(setting_id) = 'MIGRATIONSTATUS';
report(CZ_UTILS.GET_TEXT('CZ_MIGR_NO_STATUS_UPDATE', 'ERRORTEXT', SQLERRM), URGENCY_ERROR);
FOR c_table IN (SELECT NULL FROM cz_xfr_tables
WHERE UPPER(dst_table) = 'CZ_SERVERS'
AND UPPER(xfr_group) = 'MIGRATE'
AND disabled = '0'
AND ROWNUM = 1) LOOP
BEGIN
EXECUTE IMMEDIATE 'SELECT MAX(server_local_id) FROM cz_servers' INTO targetMaxId;
EXECUTE IMMEDIATE 'SELECT MAX(server_local_id) FROM cz_servers@' || dbLinkName INTO sourceMaxId;
UPDATE cz_servers SET
server_local_id = GREATEST(NVL(sourceMaxId, 0), NVL(targetMaxId, 0))
WHERE server_local_id = serverLocalId
RETURNING server_local_id INTO serverLocalId;
FOR c_tables IN (SELECT dst_table FROM cz_xfr_tables
WHERE xfr_group IN ('OVERRIDE', 'SLOWREFRESH', 'MIGRATE', 'REFRESH', 'FORCESLOW')
AND disabled = '0' AND pk_useexpansion = 'Y') LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || CONFIGURATOR_SCHEMA || '.' || c_tables.dst_table || ' NOLOGGING';
FOR c_tables IN (SELECT dst_table FROM cz_xfr_tables
WHERE xfr_group IN ('OVERRIDE', 'SLOWREFRESH', 'MIGRATE', 'REFRESH', 'FORCESLOW')
AND disabled = '0' AND pk_useexpansion = 'Y') LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || CONFIGURATOR_SCHEMA || '.' || c_tables.dst_table || ' LOGGING';
OPEN l_src_sessn_cur FOR 'SELECT count(*) FROM v$session@'||dbLinkName|| ' t where t.module = ''PUBLISH_MODEL''';
FOR i IN(SELECT ui_def_id FROM CZ_UI_DEFS
WHERE deleted_flag='0' AND ui_style IN(G_OA_UIMT_STYLE,G_OA_UI_STYLE))
LOOP
import_jrad_docs (p_ui_def_id => i.ui_def_id,
p_link_name => dbLinkName,
x_return_status => xReturnStatus,
x_msg_count => xMsgCount,
x_msg_data => xMsgData);
update cz_db_settings set value='COMPLETED'
where section_name='MIGRATE' and setting_id='MigrationStatus';
if(DeleteDbLink = 1)then
declare
cdyn integer;
report(CZ_UTILS.GET_TEXT('CZ_MIGR_NO_LINK_DELETE', 'ERRORTEXT', SQLERRM), URGENCY_WARNING);
SELECT UPPER(value) INTO tMajorVersion FROM cz_db_settings
WHERE UPPER(section_name) = 'SCHEMA'
AND UPPER(setting_id) = 'MAJOR_VERSION';
SELECT UPPER(value) INTO tMinorVersion FROM cz_db_settings
WHERE UPPER(section_name) = 'SCHEMA'
AND UPPER(setting_id) = 'MINOR_VERSION';
EXECUTE IMMEDIATE 'SELECT UPPER(value) FROM cz_db_settings@' || dbLinkName ||
' WHERE UPPER(section_name) = ''SCHEMA''' ||
' AND UPPER(setting_id) = ''MAJOR_VERSION''' INTO sMajorVersion;
EXECUTE IMMEDIATE 'SELECT UPPER(value) FROM cz_db_settings@' || dbLinkName ||
' WHERE UPPER(section_name) = ''SCHEMA''' ||
' AND UPPER(setting_id) = ''MINOR_VERSION''' INTO sMinorVersion;
'(SELECT column_name, data_type, nullable, data_length ' ||
' FROM all_tab_columns ' ||
' WHERE table_name = ''' || upper(inTableName) || '''' ||
' AND owner = ''' || CONFIGURATOR_SCHEMA || '''' ||
' MINUS ' ||
' SELECT column_name, data_type, nullable, data_length ' ||
' FROM all_tab_columns@' || dbLinkName ||
' WHERE table_name = '''|| upper(inTableName) || '''' ||
' AND owner = ''' || CONFIGURATOR_SCHEMA || ''')' ||
' UNION ' ||
'(SELECT column_name, data_type, nullable, data_length ' ||
' FROM all_tab_columns@' || dbLinkName ||
' WHERE table_name = ''' || upper(inTableName) || ''''||
' AND owner = ''' || CONFIGURATOR_SCHEMA || '''' ||
' MINUS ' ||
' SELECT column_name, data_type, nullable, data_length ' ||
' FROM all_tab_columns ' ||
' WHERE table_name = ''' || upper(inTableName) || '''' ||
' AND owner = ''' || CONFIGURATOR_SCHEMA || ''')',
dbms_sql.native);
SELECT column_name FROM all_tab_columns
WHERE table_name = upper(inTableName)
AND owner = CONFIGURATOR_SCHEMA;
outNamesArray.delete;
SELECT constraint_name FROM all_constraints
WHERE table_name = upper(inTableName)
AND owner = CONFIGURATOR_SCHEMA
AND constraint_type = 'P';
'(SELECT column_name FROM all_cons_columns ' ||
' WHERE constraint_name = ''' || pkName || '''' ||
' AND owner = ''' || CONFIGURATOR_SCHEMA || '''' ||
' MINUS '||
'SELECT column_name FROM all_cons_columns@' || dbLinkName ||
' WHERE constraint_name = ''' || pkName || '''' ||
' AND owner = ''' || CONFIGURATOR_SCHEMA || ''')' ||
' UNION '||
'(SELECT column_name FROM all_cons_columns@' || dbLinkName ||
' WHERE constraint_name = ''' || pkName || '''' ||
' AND owner = ''' || CONFIGURATOR_SCHEMA || '''' ||
' MINUS '||
'SELECT column_name FROM all_cons_columns ' ||
' WHERE constraint_name = ''' || pkName || '''' ||
' AND owner = ''' || CONFIGURATOR_SCHEMA || ''')',
dbms_sql.native);
SELECT column_name FROM all_cons_columns
WHERE owner = CONFIGURATOR_SCHEMA
AND constraint_name =
(SELECT constraint_name FROM all_constraints
WHERE table_name = upper(inTableName)
AND owner = CONFIGURATOR_SCHEMA
AND constraint_type='P');
outNamesArray.delete;
dbms_sql.parse(cdyn,'select count(*) from '||inTableName,dbms_sql.native);
dbms_sql.parse(cdyn,'select count(*) from '||inTableName||'@'||dbLinkName,dbms_sql.native);
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || inTableName INTO nCountTarget;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || inTableName || '@' || dbLinkName INTO nCountSource;
for c_tables in (select dst_table from cz_xfr_tables where xfr_group='OVERRIDE' and disabled='0') loop
xError := copy_table_override(c_tables.dst_table, inStopOnSkippable);
for c_tables in (select dst_table from cz_xfr_tables where xfr_group='MIGRATE' and disabled='0') loop
xError := copy_table(c_tables.dst_table, inCommitSize, inStopOnSkippable, inRefreshable, inForceSlowMode, inForceProcess);
for c_tables in (select dst_table from cz_xfr_tables where xfr_group='REFRESH' and disabled='0') loop
xError := copy_table(c_tables.dst_table, inCommitSize, inStopOnSkippable, 1, inForceSlowMode, 1);
for c_tables in (select dst_table from cz_xfr_tables where xfr_group='FORCESLOW' and disabled='0') loop
xError := copy_table(c_tables.dst_table, inCommitSize, inStopOnSkippable, inRefreshable, 1, inForceProcess);
for c_tables in (select dst_table from cz_xfr_tables where xfr_group='SLOWREFRESH' and disabled='0') loop
xError := copy_table(c_tables.dst_table, inCommitSize, inStopOnSkippable, 1, 1, 1);
tableInsertStatement varchar2(5120);
tableUpdateStatement varchar2(5120);
tableInsertStatement := ' insert into '||inTableName||' (';
tableInsertStatement := tableInsertStatement||ColumnNames(i)||',';
tableInsertStatement := tableInsertStatement||ColumnNames(ColumnNames.last)||') values (';
tableInsertStatement := tableInsertStatement||'c_row.'||ColumnNames(i)||',';
tableInsertStatement := tableInsertStatement||'c_row.'||ColumnNames(ColumnNames.last)||'); ';
tableUpdateStatement := ' NULL; ';
tableUpdateStatement := ' update '||inTableName||' set ';
tableUpdateStatement := tableUpdateStatement||ColumnNames(i)||'=c_row.'||ColumnNames(i)||',';
tableUpdateStatement := tableUpdateStatement||ColumnNames(ColumnNames.last)||'=c_row.'||ColumnNames(ColumnNames.last)||' where ';
tableUpdateStatement := tableUpdateStatement||PkColumnNames(i)||'=c_row.'||PkColumnNames(i)||' and ';
tableUpdateStatement := tableUpdateStatement||PkColumnNames(PkColumnNames.last)||'=c_row.'||PkColumnNames(PkColumnNames.last)||'; ';
' nUpdates number := 0; '||
' nInserts number := 0; '||
'for c_row in (select * from '||inTableName||'@'||dbLinkName||') loop '||
' begin '||
' if(nCommitCount>=:CommitSize)then '||
' commit; '||
tableInsertStatement||
' nInserts := nInserts + 1; '||
tableUpdateStatement||
' nUpdates := nUpdates + 1; '||
' cz_migrate.report(CZ_UTILS.GET_TEXT(''CZ_MIGR_UNABLE_UPDATE'', ''TABLENAME'', ''' || inTableName || ''', ''ERRORTEXT'', SQLERRM), cz_migrate.URGENCY_WARNING); ' ||
' cz_migrate.report(CZ_UTILS.GET_TEXT(''CZ_MIGR_UNABLE_INSERT'', ''TABLENAME'', ''' || inTableName || ''', ''ERRORTEXT'', SQLERRM), cz_migrate.URGENCY_WARNING); ' ||
' 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); ' ||
tableInsertStatement varchar2(5120);
tableInsertStatement := ColumnNames(ColumnNames.first);
tableInsertStatement := tableInsertStatement||','||ColumnNames(i);
tableInsertStatement := ' insert /*+ APPEND */ into '||inTableName||' ('||
tableInsertStatement||') select '||tableInsertStatement||' from '||
inTableName||'@'||dbLinkName;
dbms_sql.parse(cdyn,tableInsertStatement,dbms_sql.native);
report(CZ_UTILS.GET_TEXT('CZ_MIGR_FAST_INSERTED', 'TABLENAME', inTableName), URGENCY_MESSAGE);
tableInsertStatement varchar2(5120);
tableInsertStatement := ColumnNames(ColumnNames.first);
tableInsertStatement := tableInsertStatement||','||ColumnNames(i);
tableInsertStatement := ' insert /*+ APPEND */ into '||inTableName||' ('||
tableInsertStatement||') select '||tableInsertStatement||' from '||
inTableName||'@'||dbLinkName;
tableInsertStatement := tableInsertStatement||
' remote where not exists (select null from '||
inTableName||' where '||PkString||')';
dbms_sql.parse(cdyn,tableInsertStatement,dbms_sql.native);
report(CZ_UTILS.GET_TEXT('CZ_MIGR_INSERT_SUCCESS', 'TABLENAME', inTableName, 'INSERTROWS', TO_CHAR(rdyn)), URGENCY_MESSAGE);
for c_triggers in (select dst_table from cz_xfr_tables where xfr_group='TRIGGERS' and disabled='0') loop
begin
cdyn := dbms_sql.open_cursor;
for c_triggers in (select dst_table from cz_xfr_tables where xfr_group='TRIGGERS' and disabled='0') loop
begin
cdyn := dbms_sql.open_cursor;
dbms_sql.parse(cdyn,'SELECT ' || Operator || '(' || inPkName || ') FROM ' || tableName, dbms_sql.native);
EXECUTE IMMEDIATE 'SELECT ' || CONFIGURATOR_SCHEMA || '.' || sequenceName || '.NEXTVAL FROM DUAL' INTO nextValue;
EXECUTE IMMEDIATE 'SELECT ' || CONFIGURATOR_SCHEMA || '.' || sequenceName || '.NEXTVAL FROM DUAL' INTO nextValue;
FOR c_seq IN (SELECT src_table, dst_table, dst_subschema, filtersyntax FROM cz_xfr_tables
WHERE xfr_group = 'SEQUENCES' AND disabled = '0') LOOP
xError := adjust_sequence(c_seq.src_table, c_seq.dst_table, c_seq.dst_subschema, TO_NUMBER(c_seq.filtersyntax));
SELECT DECODE(UPPER(value), '1', 1, 'ON', 1, 'Y', 1, 'YES', 1,'TRUE', 1, 'ENABLE', 1,
'0', 0, 'OFF', 0, 'N', 0, 'NO', 0,'FALSE', 0, 'DISABLE', 0,
0) --the default value.
INTO migrateConfigData FROM cz_db_settings
WHERE UPPER(section_name) = 'MIGRATE' and UPPER(setting_id) = 'MIGRATECONFIGDATA';
UPDATE cz_xfr_tables SET disabled = '1'
WHERE UPPER(xfr_group) = 'OVERRIDE'
AND UPPER(dst_table) IN
('CZ_XFR_TABLES', 'CZ_ATP_REQUESTS', 'CZ_DB_LOGS', 'CZ_EXP_TMP_LINES', 'CZ_TERMINATE_MSGS',
'CZ_PRICING_STRUCTURES', 'CZ_ITEM_PARENTS', 'CZ_INTL_TEXTS', 'CZ_LOOKUP_VALUES_TL',
'CZ_LOOKUP_VALUES', 'CZ_TYPE_RELATIONSHIPS');
UPDATE cz_xfr_tables SET disabled = '1'
WHERE UPPER(xfr_group) = 'OVERRIDE'
AND UPPER(dst_table) LIKE 'CZ/_IMP/_%' ESCAPE '/';
UPDATE cz_xfr_tables SET disabled = '1'
WHERE UPPER(xfr_group) = 'OVERRIDE'
AND UPPER(dst_table) NOT LIKE 'CZ/_%' ESCAPE '/';
UPDATE cz_xfr_tables SET disabled = '1'
WHERE UPPER(xfr_group) = 'OVERRIDE'
AND UPPER(dst_table) IN
('CZ_CONFIG_ATTRIBUTES', 'CZ_CONFIG_EXT_ATTRIBUTES', 'CZ_CONFIG_HDRS', 'CZ_CONFIG_INPUTS',
'CZ_CONFIG_ITEMS', 'CZ_CONFIG_MESSAGES', 'CZ_CONFIG_USAGES');
UPDATE cz_xfr_tables SET xfr_group = 'SLOWREFRESH'
WHERE UPPER(xfr_group) = 'OVERRIDE'
AND UPPER(dst_table) IN ('CZ_DB_SETTINGS', 'CZ_XFR_FIELDS');
UPDATE cz_xfr_tables SET xfr_group = 'MIGRATE'
WHERE UPPER(xfr_group) = 'OVERRIDE'
AND UPPER(dst_table) = 'CZ_SERVERS';
UPDATE cz_xfr_tables SET disabled = '1'
WHERE UPPER(xfr_group) = 'TRIGGERS'
AND UPPER(dst_table) NOT LIKE 'CZ/_%' ESCAPE '/';
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(1, 'SEQUENCES', '0', 'CZ_ADDRESSES_S', 'CZ_ADDRESSES', 'ADDRESS_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(2, 'SEQUENCES', '0', 'CZ_ADDRESS_USES_S', 'CZ_ADDRESS_USES', 'ADDRESS_USE_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(4, 'SEQUENCES', '0', 'CZ_COMBO_FEATURES_S', 'CZ_COMBO_FEATURES', 'FEATURE_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(5, 'SEQUENCES', '0', 'CZ_CONFIG_HDRS_S', 'CZ_CONFIG_HDRS', 'CONFIG_HDR_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(6, 'SEQUENCES', '0', 'CZ_CONFIG_INPUTS_S', 'CZ_CONFIG_INPUTS', 'CONFIG_INPUT_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(7, 'SEQUENCES', '0', 'CZ_CONFIG_ITEMS_S', 'CZ_CONFIG_ITEMS', 'CONFIG_ITEM_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(8, 'SEQUENCES', '0', 'CZ_CONFIG_MESSAGES_S', 'CZ_CONFIG_MESSAGES', 'MESSAGE_SEQ', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(9, 'SEQUENCES', '0', 'CZ_CONTACTS_S', 'CZ_CONTACTS', 'CONTACT_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(10, 'SEQUENCES', '0', 'CZ_CUSTOMERS_S', 'CZ_CUSTOMERS', 'CUSTOMER_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(13, 'SEQUENCES', '0', 'CZ_DEVL_PROJECTS_S', 'CZ_DEVL_PROJECTS', 'DEVL_PROJECT_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(14, 'SEQUENCES', '0', 'CZ_DRILL_DOWN_ITEMS_S', 'CZ_DRILL_DOWN_ITEMS', 'DD_SEQ_NBR', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(15, 'SEQUENCES', '0', 'CZ_EFFECTIVITY_SETS_S', 'CZ_EFFECTIVITY_SETS', 'EFFECTIVITY_SET_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(16, 'SEQUENCES', '0', 'CZ_END_USERS_S', 'CZ_END_USERS', 'END_USER_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(18, 'SEQUENCES', '0', 'CZ_EXPRESSIONS_S', 'CZ_EXPRESSIONS', 'EXPRESS_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(19, 'SEQUENCES', '0', 'CZ_EXPRESSION_NODES_S', 'CZ_EXPRESSION_NODES', 'EXPR_NODE_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(20, 'SEQUENCES', '0', 'CZ_FILTER_SETS_S', 'CZ_FILTER_SETS', 'FILTER_SET_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(22, 'SEQUENCES', '0', 'CZ_FUNC_COMP_SPECS_S', 'CZ_FUNC_COMP_SPECS', 'FUNC_COMP_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(23, 'SEQUENCES', '0', 'CZ_GRID_CELLS_S', 'CZ_GRID_CELLS', 'GRID_CELL_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(24, 'SEQUENCES', '0', 'CZ_GRID_COLS_S', 'CZ_GRID_COLS', 'GRID_COL_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(25, 'SEQUENCES', '0', 'CZ_GRID_DEFS_S', 'CZ_GRID_DEFS', 'GRID_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(26, 'SEQUENCES', '0', 'CZ_INTL_TEXTS_S', 'CZ_LOCALIZED_TEXTS', 'INTL_TEXT_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(27, 'SEQUENCES', '0', 'CZ_ITEM_MASTERS_S', 'CZ_ITEM_MASTERS', 'ITEM_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(30, 'SEQUENCES', '0', 'CZ_ITEM_TYPES_S', 'CZ_ITEM_TYPES', 'ITEM_TYPE_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(32, 'SEQUENCES', '0', 'CZ_LCE_HEADERS_S', 'CZ_LCE_HEADERS', 'LCE_HEADER_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(33, 'SEQUENCES', '0', 'CZ_LCE_LINES_S', 'CZ_LCE_LINES', 'LCE_LINE_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(34, 'SEQUENCES', '0', 'CZ_LCE_OPERANDS_S', 'CZ_LCE_OPERANDS', 'OPERAND_SEQ', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(35, 'SEQUENCES', '0', 'CZ_LOCALES_S', 'CZ_LOCALES', 'LOCALE_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(37, 'SEQUENCES', '0', 'CZ_MODEL_PUBLICATIONS_S', 'CZ_MODEL_PUBLICATIONS', 'PUBLICATION_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(38, 'SEQUENCES', '0', 'CZ_MODEL_REF_EXPLS_S', 'CZ_MODEL_REF_EXPLS', 'MODEL_REF_EXPL_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(39, 'SEQUENCES', '0', 'CZ_MODEL_USAGES_S', 'CZ_MODEL_USAGES', 'MODEL_USAGE_ID', '1');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(40, 'SEQUENCES', '0', 'CZ_OPPORTUNITY_HDRS_S', 'CZ_OPPORTUNITY_HDRS', 'OPPORTUNITY_HDR_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(42, 'SEQUENCES', '0', 'CZ_PB_MODEL_EXPORTS_S', 'CZ_PB_MODEL_EXPORTS', 'EXPORT_ID', '1');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(43, 'SEQUENCES', '0', 'CZ_POPULATORS_S', 'CZ_POPULATORS', 'POPULATOR_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(44, 'SEQUENCES', '0', 'CZ_POPULATOR_MAPS_S', 'CZ_POPULATOR_MAPS', 'POP_MAP_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(46, 'SEQUENCES', '0', 'CZ_PRICE_GROUPS_S', 'CZ_PRICE_GROUPS', 'PRICE_GROUP_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(47, 'SEQUENCES', '0', 'CZ_PROPERTIES_S', 'CZ_PROPERTIES', 'PROPERTY_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(48, 'SEQUENCES', '0', 'CZ_PROPOSAL_HDRS_S', 'CZ_PROPOSAL_HDRS', 'PROPOSAL_HDR_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(50, 'SEQUENCES', '0', 'CZ_PSNODE_PROPCOMPAT_GENS_S', 'CZ_PSNODE_PROPCOMPAT_GENS', 'COMPAT_RUN', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(51, 'SEQUENCES', '0', 'CZ_PS_NODES_S', 'CZ_PS_NODES', 'PS_NODE_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(53, 'SEQUENCES', '0', 'CZ_QUOTE_HDRS_S', 'CZ_QUOTE_HDRS', 'QUOTE_HDR_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(56, 'SEQUENCES', '0', 'CZ_QUOTE_SPARES_S', 'CZ_QUOTE_SPARES', 'SEQ_NUMBER', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(57, 'SEQUENCES', '0', 'CZ_QUOTE_SPECIAL_ITEMS_S', 'CZ_QUOTE_SPECIAL_ITEMS', 'SEQ_NUMBER', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(58, 'SEQUENCES', '0', 'CZ_REL_TYPES_S', 'CZ_REL_TYPES', 'REL_TYPE_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(59, 'SEQUENCES', '0', 'CZ_RP_ENTRIES_S', 'CZ_RP_ENTRIES', 'OBJECT_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(60, 'SEQUENCES', '0', 'CZ_RULES_S', 'CZ_RULES', 'RULE_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(61, 'SEQUENCES', '0', 'CZ_RULE_FOLDERS_S', 'CZ_RULE_FOLDERS', 'RULE_FOLDER_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(62, 'SEQUENCES', '0', 'CZ_SERVERS_S', 'CZ_SERVERS', 'SERVER_LOCAL_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(63, 'SEQUENCES', '0', 'CZ_SPARES_SPECIALS_S', 'CZ_SPARES_SPECIALS', 'PACKAGE_SEQ', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(64, 'SEQUENCES', '0', 'CZ_SUB_CON_SETS_S', 'CZ_SUB_CON_SETS', 'SUB_CONS_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(65, 'SEQUENCES', '0', 'CZ_TERMINATE_MSGS_S', 'CZ_TERMINATE_MSGS', 'MSG_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(66, 'SEQUENCES', '0', 'CZ_UI_DEFS_S', 'CZ_UI_DEFS', 'UI_DEF_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(67, 'SEQUENCES', '0', 'CZ_UI_NODES_S', 'CZ_UI_NODES', 'UI_NODE_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(70, 'SEQUENCES', '0', 'CZ_USER_GROUPS_S', 'CZ_USER_GROUPS', 'USER_GROUP_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(71, 'SEQUENCES', '0', 'CZ_XFR_PROJECT_BILLS_S', 'CZ_XFR_PROJECT_BILLS', 'MODEL_PS_NODE_ID', '-1');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(72, 'SEQUENCES', '0', 'CZ_XFR_RUN_INFOS_S', 'CZ_XFR_RUN_INFOS', 'RUN_ID', '1');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(73, 'SEQUENCES', '0', 'CZ_ARCHIVES_S', 'CZ_ARCHIVES', 'ARCHIVE_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(74, 'SEQUENCES', '0', 'CZ_SIGNATURES_S', 'CZ_SIGNATURES', 'SIGNATURE_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(75, 'SEQUENCES', '0', 'CZ_UI_ACTIONS_S', 'CZ_UI_ACTIONS', 'UI_ACTION_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(76, 'SEQUENCES', '0', 'CZ_UI_PAGES_S', 'CZ_UI_PAGES', 'PAGE_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(77, 'SEQUENCES', '0', 'CZ_UI_PAGE_ELEMENTS_S', 'CZ_UI_PAGE_ELEMENTS', 'ELEMENT_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(78, 'SEQUENCES', '0', 'CZ_UI_PAGE_REFS_S', 'CZ_UI_PAGE_REFS', 'PAGE_REF_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(79, 'SEQUENCES', '0', 'CZ_UI_PAGE_SETS_S', 'CZ_UI_PAGE_SETS', 'PAGE_SET_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(80, 'SEQUENCES', '0', 'CZ_UI_TEMPLATES_S', 'CZ_UI_TEMPLATES', 'TEMPLATE_ID', '20');
INSERT INTO cz_xfr_tables
(order_seq, xfr_group, disabled, src_table, dst_table, dst_subschema, filtersyntax)
VALUES
(81, 'SEQUENCES', '0', 'CZ_FCE_FILES_S', 'CZ_FCE_FILES', 'FCE_FILE_ID', '20');
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || noRecords(i) INTO nCount;
SELECT jrad_doc
BULK
COLLECT
INTO l_jrad_doc_tbl
FROM cz_ui_pages
WHERE ui_def_id = p_ui_def_id
AND deleted_flag = '0';
SELECT jrad_doc
BULK
COLLECT
INTO l_jrad_doc_tbl
FROM cz_ui_templates
WHERE cz_ui_templates.ui_def_id = p_ui_def_id
AND cz_ui_templates.template_id = p_template_id
AND cz_ui_templates.seeded_flag = '0'
AND cz_ui_templates.deleted_flag = '0';
insert into cz_jrad_chunks(jrad_doc,seq_nbr,xml_chunk,publication_id)
values (l_chunk_tbl(i).jrad_doc,l_chunk_tbl(i).seq_nbr,l_chunk_tbl(i).xml_chunk,0);
' begin delete from cz_jrad_chunks'||l_link_name||'; commit; end; ';
delete from cz_jrad_chunks; commit;
OPEN l_ref_cursor FOR 'SELECT JRAD_DOC,SEQ_NBR,XML_CHUNK
FROM cz_jrad_chunks'||l_link_name;
insert into cz_jrad_chunks (JRAD_DOC,SEQ_NBR,XML_CHUNK,PUBLICATION_ID)
values (l_jrad_doc,l_seq_nbr,l_XML_CHUNK,0);
cz_pb_mgr.insert_jrad_docs(0);
' begin delete from cz_jrad_chunks'||l_link_name||'; commit; end; ';
delete from cz_jrad_chunks; commit;
fnd_message.set_token('PROCEDURE_NAME', 'INSERT_JRAD_DOCS');
SELECT ui_def_id, template_id
BULK
COLLECT
INTO l_ui_def_id_tbl,l_template_id_tbl
FROM cz_ui_templates
WHERE cz_ui_templates.deleted_flag = '0'
AND cz_ui_templates.seeded_flag = '0'
AND cz_ui_templates.ui_def_id = 0
OR cz_ui_templates.ui_def_id IN (SELECT ui_def_id
FROM cz_ui_defs
WHERE cz_ui_defs.deleted_flag = '0');
----delete from temp table on target and source
EXECUTE IMMEDIATE
' begin delete from cz_jrad_chunks'||l_link_name||'; commit; end; ';
delete from cz_jrad_chunks; commit;
OPEN l_ref_cursor FOR 'SELECT JRAD_DOC,SEQ_NBR,XML_CHUNK
FROM cz_jrad_chunks'||l_link_name;
insert into cz_jrad_chunks (JRAD_DOC,SEQ_NBR,XML_CHUNK,PUBLICATION_ID)
values (l_jrad_doc,l_seq_nbr,l_XML_CHUNK,0);
cz_pb_mgr.insert_jrad_docs(0);
' begin delete from cz_jrad_chunks'||l_link_name||'; commit; end; ';
delete from cz_jrad_chunks; commit;
fnd_message.set_token('PROCEDURE_NAME', 'INSERT_JRAD_DOCS');