[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
OPEN gl_ref_cursor FOR 'SELECT source_server_flag
FROM cz_servers'||p_link_name||' t
WHERE UPPER(t.local_name) = UPPER(:1)' USING p_source_server;
CURSOR src_server_info IS SELECT hostname, instance_name, source_server_flag
FROM cz_servers
WHERE UPPER(local_name) = 'LOCAL';
SELECT COUNT(*)
INTO l_src_count
FROM cz_servers
WHERE cz_servers.source_server_flag = SRC_SERVER_FLAG;
SELECT COUNT(*)
INTO x_src_verification
FROM v$instance
WHERE UPPER(host_name) = UPPER(l_hostname)
AND UPPER(instance_name) = UPPER(l_instance_name);
OPEN gl_ref_cursor FOR 'SELECT instance_name,host_name
FROM v$instance'||p_link_name;
OPEN gl_ref_cursor FOR 'SELECT instance_name,hostname,notes
FROM cz_servers'||p_link_name||' t
WHERE UPPER(local_name) = ''LOCAL'' ';
OPEN gl_ref_cursor FOR 'SELECT distinct server_id
FROM cz_model_publications'||p_link_name||' t
WHERE t.source_target_flag = ''T''
AND t.deleted_flag = ''0'' ';
SELECT 1
INTO v_src_server_count
FROM cz_model_publications
WHERE cz_model_publications.server_id = v_tgt_pb_server_id
AND cz_model_publications.deleted_flag = '0'
AND ROWNUM < 2;
OPEN gl_ref_cursor FOR 'SELECT COUNT(*) FROM cz_model_publications'||p_link_name||' t
WHERE t.deleted_flag = ''0''
AND t.model_persistent_id IN (SELECT model_persistent_id
FROM cz_model_publications x
WHERE x.deleted_flag = ''0''
AND x.source_target_flag = ''S'')';
OPEN gl_ref_cursor FOR 'SELECT COUNT(*) FROM cz_model_publications'||p_link_name||' t
WHERE t.deleted_flag = ''0''
AND t.export_status = ''OK''
AND t.source_target_flag = ''T''
AND t.publication_id IN (SELECT remote_publication_id
FROM cz_model_publications x
WHERE x.export_status = ''OK''
AND x.deleted_flag = ''0'')';
UPDATE cz_model_publications
SET deleted_flag = '1'
WHERE publication_id = p_publication_id;
/* DELETE FROM cz_model_publications
WHERE publication_id = p_publication_id; */
DELETE FROM cz_pb_client_apps
WHERE publication_id = p_publication_id;
DELETE FROM cz_pb_languages
WHERE publication_id = p_publication_id;
DELETE FROM cz_publication_usages
WHERE publication_id = p_publication_id;
delete from cz_pb_model_exports;
PROCEDURE update_tgt_server_id(p_target_server_id IN cz_servers.server_local_id%TYPE,
p_link_name In cz_servers.fndnam_link_name%TYPE)
IS
BEGIN
EXECUTE IMMEDIATE
'UPDATE cz_model_publications'||p_link_name||' t SET t.server_id = '||p_target_server_id||' WHERE t.deleted_flag = ''0'' ';
END update_tgt_server_id;
EXECUTE IMMEDIATE 'SELECT organization_id FROM org_organization_definitions'||p_import_link_name||
' WHERE UPPER(organization_name) = ' ||
' (SELECT UPPER(organization_name) FROM org_organization_definitions' || p_sync_tgt_link_name ||
' WHERE organization_id = :1)'
INTO p_org_id USING l_org_id;
OPEN l_remote_item_cursor FOR ' SELECT concatenated_segments FROM MTL_SYSTEM_ITEMS_VL'
|| p_sync_tgt_link_name || ' t ' || ' WHERE t.inventory_item_id = ' || p_item_id
|| ' AND organization_id = '||l_org_id;
OPEN c_local_item_id FOR ' SELECT inventory_item_id FROM MTL_SYSTEM_ITEMS_VL'
|| p_import_link_name||' WHERE concatenated_segments = '''||l_concatenated_segments||''' AND organization_id = '||p_org_id;
SELECT cz_model_publications_s.nextval
INTO l_new_pb_id
FROM dual;
SELECT fndnam_link_name INTO linkName
FROM cz_servers
WHERE import_enabled='1';
' SELECT *'||
' FROM cz_model_publications'||p_link_name||' t ' ||
' WHERE t.publication_id = '||p_publication_id ;
INSERT INTO cz_model_publications(
PUBLICATION_ID
,OBJECT_ID
,OBJECT_TYPE
,SERVER_ID
,ORGANIZATION_ID
,TOP_ITEM_ID
,PRODUCT_KEY
,PUBLICATION_MODE
,UI_DEF_ID
,UI_STYLE
,APPLICABLE_FROM
,APPLICABLE_UNTIL
,EXPORT_STATUS
,DELETED_FLAG
,MODEL_LAST_STRUCT_UPDATE
,MODEL_LAST_LOGIC_UPDATE
,MODEL_LAST_UPDATED
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,USER_STR01
,USER_STR02
,USER_STR03
,USER_STR04
,USER_NUM01
,USER_NUM02
,USER_NUM03
,USER_NUM04
,MODEL_PERSISTENT_ID
,SOURCE_TARGET_FLAG
,REMOTE_PUBLICATION_ID
,PAGE_LAYOUT
,CONTAINER
,DISABLED_FLAG
,PUBLISHED
) VALUES
(
l_new_pb_id
,p_src_object_id
,l_pub_cursor.OBJECT_TYPE
,p_tgt_server_id
,DECODE(l_pub_cursor.ORGANIZATION_ID,NULL,NULL, v_org_id)
,DECODE(l_pub_cursor.TOP_ITEM_ID ,NULL, NULL, v_item_id)
,DECODE(l_pub_cursor.PRODUCT_KEY , NULL,NULL, v_product_key )
,l_pub_cursor.PUBLICATION_MODE
,l_ui_def_id
,l_pub_cursor.UI_STYLE
,l_pub_cursor.APPLICABLE_FROM
,l_pub_cursor.APPLICABLE_UNTIL
,l_pub_cursor.EXPORT_STATUS
,l_pub_cursor.DELETED_FLAG
,l_pub_cursor.MODEL_LAST_STRUCT_UPDATE
,l_pub_cursor.MODEL_LAST_LOGIC_UPDATE
,l_pub_cursor.MODEL_LAST_UPDATED
,l_pub_cursor.CREATED_BY
,l_pub_cursor.CREATION_DATE
,l_pub_cursor.LAST_UPDATED_BY
,l_pub_cursor.LAST_UPDATE_DATE
,l_pub_cursor.USER_STR01
,l_pub_cursor.USER_STR02
,l_pub_cursor.USER_STR03
,l_pub_cursor.USER_STR04
,l_pub_cursor.USER_NUM01
,l_pub_cursor.USER_NUM02
,l_pub_cursor.USER_NUM03
,l_pub_cursor.USER_NUM04
,l_pub_cursor.MODEL_PERSISTENT_ID
,'S'
,p_publication_id
,l_pub_cursor.PAGE_LAYOUT
,l_pub_cursor.CONTAINER
,l_pub_cursor.DISABLED_FLAG
,l_pub_cursor.PUBLISHED
);
'UPDATE cz_model_publications'||p_link_name||' t ' ||
' SET t.remote_publication_id = '||l_new_pb_id||
' WHERE t.publication_id = '||p_publication_id ;
'INSERT INTO cz_pb_client_apps(publication_id,fnd_application_id,application_short_name,notes)' ||
' SELECT '||l_new_pb_id||',' ||
' s.application_id,' ||
' x.application_short_name,' ||
' x.notes' ||
' FROM cz_pb_client_apps'||p_link_name||' x, fnd_applications s' ||
' WHERE x.publication_id = '||p_publication_id ||
' AND x.application_short_name = s.application_short_name' ;
'INSERT INTO cz_publication_usages(publication_id,usage_id)' ||
' SELECT '||l_new_pb_id||',usage_id' ||
' FROM cz_publication_usages'||p_link_name||' z' ||
' WHERE z.publication_id = '||p_publication_id ;
'INSERT INTO cz_pb_languages(publication_id,language)' ||
' SELECT '||l_new_pb_id||', language' ||
' FROM cz_pb_languages'||p_link_name||' y' ||
' WHERE y.publication_id = '||p_publication_id;
PROCEDURE delete_tgt_publication(p_publication_id IN cz_model_publications.publication_id%TYPE,
p_link_name IN cz_servers.fndnam_link_name%TYPE)
IS
BEGIN
EXECUTE IMMEDIATE
'delete from cz_model_publications'||p_link_name||' t' ||
' where t.publication_id = '||p_publication_id ;
SELECT instance_name
INTO x_src_instance
FROM cz_servers
WHERE cz_servers.server_local_id = 0;
SELECT instance_name
INTO x_tgt_instance
FROM cz_servers
WHERE cz_servers.server_local_id = p_tgt_server_id ;
SELECT publication_id
BULK
COLLECT
INTO l_src_pub_tbl
FROM cz_model_publications
WHERE server_id = p_target_server_id ;
l_tgt_src_pub_tbl.DELETE;
l_tgt_rem_pub_tbl.DELETE;
l_tgt_object_id_tbl.DELETE;
l_tgt_object_type_tbl.DELETE;
l_tgt_src_model_tbl.DELETE;
l_tgt_src_ui_def_tbl.DELETE;
l_tgt_object_name_tbl.DELETE;
'SELECT publication_id,
remote_publication_id,
object_id,
object_type,
source_model_id,
source_ui_def_id
FROM cz_model_publications'||l_link_name||' t
WHERE t.deleted_flag = ''0''
AND t.source_target_flag = ''T''
AND t.export_status = ''OK'' ';
'SELECT name
FROM cz_devl_projects'||l_link_name||' t
WHERE t.devl_project_id = '||l_tgt_object_id||'
AND t.deleted_flag = ''0'' ';
'SELECT template_name
FROM cz_ui_templates'||l_link_name||' t
WHERE t.template_id = '||l_tgt_object_id||'
AND t.ui_def_id = '||GLOBAL_UI_DEF_PUB||'
AND t.deleted_flag = ''0'' ';
SELECT publication_id,object_id,object_type,ui_def_id
INTO l_src_pb_id,l_src_object_id,l_src_object_type,l_src_ui_def_id
FROM cz_model_publications
WHERE cz_model_publications.publication_id = l_tgt_rem_pub_tbl(i);
delete_tgt_publication(l_tgt_src_pub_tbl(i),l_link_name);
SELECT name INTO l_src_object_name
FROM cz_devl_projects
WHERE cz_devl_projects.devl_project_id = l_src_object_id;
SELECT template_name INTO l_src_object_name
FROM cz_ui_templates
WHERE template_id = l_src_object_id AND ui_def_id = GLOBAL_UI_DEF_SRC;
SELECT instance_name,hostname,db_listener_port,fndnam_link_name
INTO l_instance_name,l_host_name,l_listener_port,l_fndnam_link_name
FROM cz_servers
WHERE UPPER(cz_servers.local_name) = UPPER(p_target_instance);
PROCEDURE update_server_info(p_target_server_id IN cz_servers.server_local_id%TYPE,
p_link_name IN cz_servers.fndnam_link_name%TYPE)
IS
l_instance_name cz_servers.instance_name%TYPE;
SELECT instance_name,hostname,db_listener_port
INTO l_instance_name,l_host_name,l_listener_port
FROM cz_servers
WHERE cz_servers.server_local_id = p_target_server_id;
'UPDATE cz_servers'||p_link_name||' t ' ||
' SET t.instance_name = '''||l_instance_name||''', ' ||
' t.hostname = '''||l_host_name||''', ' ||
' t.db_listener_port = '||l_listener_port||', ' ||
' t.notes = '''||l_sync_date||''' ' ||
' WHERE UPPER(t.local_name) = ''LOCAL'' ';
----update server id in tgt publication record
v_link_name := cz_pb_sync_util.retrieve_link_name(p_target_server_id);
update_tgt_server_id(p_target_server_id,v_link_name);
----update target server information
update_server_info(p_target_server_id,v_link_name);
m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_INSERT_ERR', 'SQLERRM', SQLERRM);
m_msg_tbl.DELETE;
SELECT host_name,instance_name
INTO x_hostname,x_instance_name
FROM v$instance;
v_str := ' UPDATE cz_servers'||p_link_name||' SET hostname = '''||p_hostname||''', ' ||
' instance_name = '''||p_instance_name||''', ' ||
' local_name = :1 ' ||
' WHERE source_server_flag = ''1'' ';
SELECT fndnam_link_name
INTO l_link_name
FROM cz_servers
WHERE UPPER(local_name) = UPPER(p_local_name);
m_msg_tbl.DELETE;
-----update local server entry
get_local_server_info (l_hostname,l_instance_name);
UPDATE cz_servers
set hostname = l_hostname,
instance_name = l_instance_name
WHERE UPPER(cz_servers.local_name) = 'LOCAL';
SELECT local_name
BULK
COLLECT
INTO l_local_name_tbl
FROM cz_servers
WHERE UPPER(cz_servers.local_name) <> 'LOCAL';
UPDATE cz_model_publications set deleted_flag = '1';
SELECT name INTO l_src_instance from v$database;