APPS.CZ_PUBL_SYNC_CRASH SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 32

		SELECT local_name, fndnam_link_name FROM CZ_SERVERS
		WHERE server_local_id = p_server_id;

Line: 38

	' SELECT fndnam_link_name,local_name, hostname FROM CZ_SERVERS WHERE server_local_id = :1'
      INTO lLinkName, lServerName, lHostName
	USING p_server_id ;

Line: 59

	   execute immediate 'select INSTANCE_NAME, HOST_NAME from v$instance@'|| lLinkName
		INTO lSid, lHost;

Line: 105

	' SELECT local_name, hostname, instance_name  FROM CZ_SERVERS' || p_link_name ||
	' WHERE source_server_flag = ''1'''
	INTO lServerName,lHostName, lInstanceName;

Line: 117

	      execute immediate 'select INSTANCE_NAME, HOST_NAME from v$instance'
			INTO lSid, lHost ;

Line: 156

		SELECT local_name FROM CZ_SERVERS
		WHERE source_server_flag = '1'
		AND server_local_id = p_server_id;

Line: 167

	select fndnam_link_name into lName from CZ_SERVERS
	WHERE server_local_id = p_server_id;

Line: 172

	   EXECUTE IMMEDIATE 'SELECT local_name, server_local_id FROM CZ_SERVERS' || lName ||
		' WHERE source_server_flag = ''1'' '
	   INTO lServerName, rServerId;

Line: 190

/* Deletes publication data from the source */


PROCEDURE DELETE_PUBLICATION_DATA (p_target_server_id	IN       NUMBER)
IS
-- xERROR BOOLEAN := FALSE;

Line: 199

	UPDATE cz_model_publications
	SET deleted_flag = '1'
	WHERE server_id = p_target_server_id;

Line: 203

	DELETE FROM cz_pb_client_apps
	WHERE publication_id in (SELECT publication_id from cz_model_publications
					 where server_id = p_target_server_id);

Line: 207

	DELETE FROM cz_publication_usages
	WHERE  publication_id in (SELECT publication_id from cz_model_publications
					 where server_id = p_target_server_id);

Line: 211

	DELETE FROM cz_pb_languages
	WHERE  publication_id in (SELECT publication_id from cz_model_publications
					 where server_id = p_target_server_id);

Line: 215

	DELETE FROM cz_pb_model_exports
	WHERE  server_id = p_target_server_id;

Line: 222

    cz_utils.log_report(pkg_name, 'DELETE_PUBLICATION_DATA', null, ERRBUF, fnd_log.LEVEL_ERROR);

Line: 223

	RAISE DELETE_PUBLICATION_ERROR;

Line: 224

END DELETE_PUBLICATION_DATA ;

Line: 227

/* Deletes publication data from the source */

/* not in use currently
PROCEDURE DELETE_PUBLICATION_DATA (p_target_server_id	IN NUMBER,
					     p_date			IN DATE default to_date('01/01/1970', 'mm/dd/yyyy') )
IS
-- xERROR BOOLEAN := FALSE;

Line: 238

	UPDATE cz_model_publications
	SET deleted_flag = '1'
	WHERE server_id = p_target_server_id
	and model_last_updated > p_date;

Line: 243

	DELETE FROM cz_pb_client_apps
	WHERE publication_id in (SELECT publication_id from cz_model_publications
					 where server_id = p_target_server_id
					  and model_last_updated > p_date);

Line: 248

	DELETE FROM cz_publication_usages
	WHERE  publication_id in (SELECT publication_id from cz_model_publications
					 where server_id = p_target_server_id
					  and model_last_updated  > p_date);

Line: 253

	DELETE FROM cz_pb_languages
	WHERE  publication_id in (SELECT publication_id from cz_model_publications
					 where server_id = p_target_server_id
					  and model_last_updated  > p_date);

Line: 258

	DELETE FROM cz_pb_model_exports
	WHERE  server_id = p_target_server_id;

Line: 263

	RAISE DELETE_PUBLICATION_ERROR;

Line: 264

END DELETE_PUBLICATION_DATA ;

Line: 267

/* Deletes publication data from the source */

PROCEDURE DELETE_PUBLICATION ( p_publication_id 	IN NUMBER,
					 p_target_server_id	IN NUMBER DEFAULT 0,
					 p_link_name		IN VARCHAR2 DEFAULT NULL,
				       p_date			IN DATE)
IS
-- xERROR BOOLEAN := FALSE;

Line: 286

	'UPDATE cz_model_publications' || p_link_name ||
	' SET deleted_flag = ''1'' WHERE publication_id = :1 AND last_update_date > :2'
	USING p_publication_id, p_date;

Line: 291

	' DELETE FROM cz_pb_client_apps' || p_link_name ||
	' WHERE publication_id = :1'
	USING p_publication_id;

Line: 296

	' DELETE FROM cz_publication_usages' || p_link_name ||
	' WHERE  publication_id = :1'
	USING p_publication_id;

Line: 301

	' DELETE FROM cz_pb_languages' || p_link_name ||
	' WHERE  publication_id publication_id = :1'
	USING p_publication_id;

Line: 306

	' DELETE FROM cz_pb_model_exports' || p_link_name ||
	' WHERE  server_id = :1'
	USING p_target_server_id;

Line: 319

	RAISE DELETE_PUBLICATION_ERROR;

Line: 320

END DELETE_PUBLICATION;

Line: 324

/* Deletes publication data from the source and target */

PROCEDURE DELETE_DELETED_PUBLICATIONS ( p_server_id	IN NUMBER,
					          p_date	      IN DATE default to_date('01/01/1970', 'mm/dd/yyyy'))
IS
-- xERROR BOOLEAN := FALSE;

Line: 340

v_deleted_pub_tbl 		t_publ_tbl;

Line: 341

v_deleted_remote_pub_tbl 	t_publ_tbl;

Line: 348

	SELECT fndnam_link_name, server_local_id INTO lLinkName, lServerName FROM CZ_SERVERS
		WHERE server_local_id = p_server_id;

Line: 357

	' SELECT publication_id BULK COLLECT INTO ' || v_deleted_pub_tbl ||
        ' from cz_model_publications' || lLinkName ||
	' where deleted_flag = ''1''';

Line: 361

	IF (v_deleted_pub_tbl.COUNT > 0) THEN
	 FOR i IN v_deleted_pub_tbl.FIRST..v_deleted_pub_tbl.LAST
	 LOOP
 		DELETE_PUBLICATION(v_deleted_pub_tbl(i),p_server_id,lLinkName);

Line: 369

	-- delete publications in source for which the target publication has been deleted
	SELECT publication_id, remote_publication_id
	BULK COLLECT INTO v_deleted_pub_tbl, v_deleted_remote_pub_tbl
      from cz_model_publications
	where deleted_flag = '1';

Line: 375

	IF (v_deleted_pub_tbl.COUNT > 0) THEN
	 FOR i IN v_deleted_pub_tbl.FIRST..v_deleted_pub_tbl.LAST
	 LOOP
		DELETE_PUBLICATION(v_deleted_remote_pub_tbl(i),p_server_id,lLinkName,p_date);

Line: 382

	-- delete publications in target for which the source publication has been deleted
	SELECT publication_id
	BULK COLLECT INTO v_pub_tbl
      from cz_model_publications
	where deleted_flag = '0';

Line: 392

		' SELECT publication_id INTO ' || v_publication_id ||
      	        ' from cz_model_publications' || lLinkName ||
		' where remote_publication_id = :1 and deleted_flag = ''1'''
		USING v_pub_tbl(i);

Line: 398

			DELETE_PUBLICATION(v_pub_tbl(i),0,NULL,p_date);

Line: 403

	-- Delete all history for this target on the source.

	SELECT hostname, instance_name
	INTO lHostName, lSid
	FROM CZ_SERVERS
	WHERE server_local_id = '0';

Line: 411

	'SELECT SERVER_LOCAL_ID FROM CZ_SERVERS' || lLinkName ||
	' INTO ' || lTargetServerId ||
	' WHERE hostname = ' || lHostName ||
	' AND instance_name = ' || lSid;

Line: 417

	' DELETE FROM CZ_PB_MODEL_EXPORTS' || lLinkName ||
	' WHERE server_id = :1 AND last_update_date = :2'
	USING lTargetServerId, p_date;

Line: 429

	RAISE DELETE_DEL_PUBLICATION_ERROR;

Line: 430

END DELETE_DELETED_PUBLICATIONS ;

Line: 452

   SELECT language BULK COLLECT INTO lPublishedLanguages
   FROM CZ_PB_LANGUAGES
   WHERE publication_id = publicationId;

Line: 456

   SELECT usage_id BULK COLLECT INTO lPublUsages
   FROM CZ_PUBLICATION_USAGES
   WHERE publication_id = publicationId;

Line: 460

   SELECT fnd_application_id BULK COLLECT INTO lPublApps
   FROM CZ_PB_CLIENT_APPS
   WHERE publication_id = publicationId;

Line: 464

   EXECUTE IMMEDIATE 'Begin SELECT language bulk collect INTO rPublishedLanguages FROM CZ_PB_LANGUAGES' || linkName || ' WHERE publication_id = publicationId; End;';

Line: 466

   EXECUTE IMMEDIATE 'Begin SELECT usage_id bulk collect INTO rPublUsages FROM CZ_PUBLICATION_USAGES' || linkName || ' WHERE publication_id = publicationId; End;';

Line: 468

   EXECUTE IMMEDIATE 'Begin SELECT fnd_application_id bulk collect INTO rPublApps FROM CZ_PB_CLIENT_APPS' || linkName || ' WHERE publication_id = publicationId; End;';

Line: 551

	SELECT fndnam_link_name INTO lLinkName FROM CZ_SERVERS
		WHERE server_local_id = p_target_server_id;

Line: 558

	-- insert into publication tables
	EXECUTE IMMEDIATE
	 'SELECT count(*) from cz_model_publications' || lLinkName || ' where source_model_id is null'
	  INTO lCount;

Line: 566

		OPEN publications_cur FOR ' SELECT publication_id FROM cz_model_publications'||lLinkName ||
					        ' WHERE deleted_flag = ''0'' ';

Line: 580

			   EXECUTE IMMEDIATE 'select pb.model_id into ' || n_source_model_id ||
				' from cz_model_publications' || lLinkName || ' pb, cz_ps_nodes' || lLinkName || ' ps, ' ||
				'cz_ps_nodes p ' ||
	  			'where pb.model_id = ps.ps_node_id ' ||
	  			'and pb.persistent_node_id = ps.persistent_node_id ' ||
	  			'and pb_source_model_id is null ' ||
	  			'and pb.persistent_node_id = p.persistent_node_id ' ||
	  			'and ps.name = p.name' ;

Line: 597

	 			EXECUTE IMMEDIATE  'select pb.ui_def_id into ' || n_source_ui_def_id || ' ' ||
	  			'from cz_model_publications' || lLinkName || ' pb, cz_ui_nodes' || lLinkName || ' ui, ' ||
				'cz_ui_nodes u ' ||
	  			'where pb.ui_def_id = ui.ui_def_id ' ||
	  			'and pb.source_ui_def_id is null ' ||
	  			'and ui.persistent_node_id = u.persistent_ui_node_id ' ||
	  			'and u.parent_id is null ' ||
	  			'and ui.name = u.name' ;

Line: 618

			-- insert
			SELECT CZ_MODEL_PUBLICATIONS_S.NEXTVAL into lPublicationId from dual;

Line: 621

                        ' INSERT INTO cz_model_publications (PUBLICATION_ID ' ||
                             ' ,MODEL_ID ' ||
                             ' ,SERVER_ID ' ||
                             ' ,ORGANIZATION_ID ' ||
                             ' ,TOP_ITEM_ID ' ||
                             ' ,PRODUCT_KEY ' ||
                             ' ,PUBLICATION_MODE ' ||
                             ' ,UI_DEF_ID ' ||
                             ' ,UI_STYLE ' ||
                             ' ,APPLICABLE_FROM ' ||
                             ' ,APPLICABLE_UNTIL ' ||
                             ' ,EXPORT_STATUS ' ||
                             ' ,MODEL_PERSISTENT_ID ' ||
                             ' ,DELETED_FLAG ' ||
                             ' ,MODEL_LAST_STRUCT_UPDATE ' ||
                             ' ,MODEL_LAST_LOGIC_UPDATE ' ||
                             ' ,MODEL_LAST_UPDATED ' ||
                             ' ,CREATION_DATE ' ||
                             ' ,LAST_UPDATE_DATE ' ||
                             ' ,CREATED_BY ' ||
                             ' ,LAST_UPDATED_BY ' ||
                             ' ,SOURCE_TARGET_FLAG ' ||
                             ' ,REMOTE_PUBLICATION_ID ' ||
                             ' ) ' ||
                          ' VALUES   (SELECT lPublicationId ' ||
                             ' ,nvl(SOURCE_MODEL_ID,n_source_model_id) ' ||
                             ' ,p_target_server_id ' ||
                             ' ,ORGANIZATION_ID ' ||
                             ' ,TOP_ITEM_ID ' ||
                             ' ,PRODUCT_KEY ' ||
                             ' ,PUBLICATION_MODE ' ||
                             ' ,nvl(SOURCE_UI_DEF_ID,n_source_ui_def_id) ' ||
                             ' ,UI_STYLE ' ||
                             ' ,APPLICABLE_FROM ' ||
                             ' ,APPLICABLE_UNTIL ' ||
                             ' ,EXPORT_STATUS ' ||
                             ' ,MODEL_PERSISTENT_ID ' ||
                             ' ,DELETED_FLAG ' ||
                             ' ,MODEL_LAST_STRUCT_UPDATE ' ||
                             ' ,MODEL_LAST_LOGIC_UPDATE ' ||
                             ' ,MODEL_LAST_UPDATED ' ||
                             ' ,CREATION_DATE ' ||
                             ' ,LAST_UPDATE_DATE ' ||
                             ' ,CREATED_BY ' ||
                             ' ,LAST_UPDATED_BY ' ||
                             ' ,''S'' ' ||
                             ' ,PUBLICATION_ID ' ||
                             ' FROM CZ_MODEL_PUBLICATIONS' || lLinkName || ' remote ' ||
                             ' WHERE  cz_model_publications.remote_publication_id = remote.publication_id  ' ||
                           ' AND cz_model_publications.deleted_flag = ''1'' )';

Line: 672

			-- insert into other publication request tables
			EXECUTE IMMEDIATE
			' INSERT INTO CZ_PB_LANGUAGES( PUBLICATION_ID, LANGUAGE) VALUES (SELECT v_pub_tbl(i),language FROM CZ_PB_LANGUAGES'|| lLinkName || 'r ' ||
			' WHERE r.publication_id = lPublicationId';

Line: 678

			' INSERT INTO CZ_PB_CLIENT_APPS( PUBLICATION_ID, FND_APPLICATION_ID, APPLICATION_SHORT_NAME, NOTES) VALUES ' ||
                        ' (SELECT v_pub_tbl(i),FND_APPLICATION_ID, APPLICATION_SHORT_NAME, NOTES FROM CZ_PB_CLIENT_APPS'|| lLinkName || 'r ' ||
			' WHERE r.publication_id = lPublicationId';

Line: 683

			' INSERT INTO CZ_PUBLICATION_USAGES( PUBLICATION_ID, USAGE_ID) ' ||
			' VALUES (SELECT v_pub_tbl(i),usage_id FROM Z_PUBLICATION_USAGES'|| lLinkName || 'r ' ||
			' WHERE r.publication_id = lPublicationId';

Line: 692

	-- Update remote_publication_id on target
	EXECUTE IMMEDIATE
	' update cz_model_publications'||lLinkName || ' t ' ||
	' set remote_publication_id = (select publication_id from cz_model_publications' ||
	' where remote_publication_id = t.publication_id' ||
	' and deleted_flag = ''0'')' ||
	' and deleted_flag = ''0'' ';

Line: 715

select value from CZ_DB_SETTINGS
where  setting_id='OracleSequenceIncr' and section_name='SCHEMA';

Line: 736

	' SELECT fndnam_link_name FROM CZ_SERVERS WHERE server_local_id = :1'
	INTO lLinkName
	USING p_target_server ;

Line: 753

	'SELECT greatest (max(l.item_id),max(r.item_id)) INTO ' || item_val ||
	' FROM cz_item_masters' || lLinkName || ' r, cz_item_masters l';

Line: 757

	'SELECT greatest (max(l.item_type_id),max(r.item_type_id)) INTO ' || item_type_val ||
	' FROM cz_item_types' || lLinkName || ' r, cz_item_masters l' ;

Line: 761

	'SELECT greatest (max(l.property_id),max(r.property_id)) INTO ' || property_val ||
	' FROM cz_properties' || lLinkName || ' r, cz_item_masters l' ;

Line: 765

	'SELECT greatest (max(l.ps_node_id),max(r.ps_node_id)) INTO ' || ps_node_val ||
	' FROM cz_ps_nodes' || lLinkName || ' r, cz_item_masters l' ;

Line: 769

	'SELECT greatest (max(l.ui_node_id),max(r.ui_node_id)) INTO ' || ui_node_val ||
	' FROM cz_ui_nodes' || lLinkName || ' r, cz_item_masters l' ;

Line: 822

	SELECT fndnam_link_name INTO lLinkName FROM CZ_SERVERS
		WHERE server_local_id = p_source_server;

Line: 829

	SELECT publication_id, remote_publication_id
	BULK COLLECT INTO v_pub_tbl, v_remote_pub_tbl
      from cz_model_publications
	where deleted_flag = '0';

Line: 837

		UPDATE CZ_MODEL_PUBLICATIONS SET export_status = PUBLICATION_PENDING
		WHERE export_status = PUBLICATION_OK
		AND deleted_flag = '0';

Line: 874

     INSERT INTO CZ_XFR_RUN_RESULTS (RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
     VALUES(p_runId,p_programName,p_disposition,p_rec_status,p_rec_count);

Line: 930

   SELECT local_name,fndnam_link_name INTO lServerName, lLinkName
   FROM CZ_SERVERS
   WHERE server_local_id = p_target_server_id;

Line: 936

	   -- get new run id if not there and insert record in cz_xfr_run_infos
	   IF (p_run_id = NULL) THEN
		SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO runId FROM DUAL;

Line: 939

	   	INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY,COMPLETED)
         	    SELECT runId,SYSDATE,SYSDATE,'0' FROM DUAL WHERE NOT EXISTS
         	    (SELECT 1 FROM CZ_XFR_RUN_INFOS WHERE RUN_ID = runId);

Line: 944

		-- should have been inserted by SYNC_ALL_SOURCE_CP
	      runId := p_run_id;

Line: 947

	   -- delete and recreate publication data
	   DELETE_PUBLICATION_DATA (p_target_server_id);

Line: 1000

	WHEN DELETE_PUBLICATION_ERROR THEN
		ROLLBACK;

Line: 1061

		SELECT server_local_id, local_name FROM CZ_SERVERS;

Line: 1063

	-- get new run id if not there and insert record in cz_xfr_run_infos
	IF (p_run_id = NULL) THEN
		SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO p_run_id FROM DUAL;

Line: 1067

	INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY,COMPLETED)
         SELECT p_run_id,SYSDATE,SYSDATE,'0' FROM DUAL WHERE NOT EXISTS
         	(SELECT 1 FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=p_run_id);

Line: 1113

   SELECT local_name,fndnam_link_name INTO lServerName, lLinkName
   FROM CZ_SERVERS
   WHERE server_local_id = p_source_server_id;

Line: 1121

	   -- get new run id if not there and insert record in cz_xfr_run_infos
	   IF (p_run_id = NULL) THEN
		SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO runId FROM DUAL;

Line: 1127

	   INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY,COMPLETED)
             SELECT runId,SYSDATE,SYSDATE,'0' FROM DUAL WHERE NOT EXISTS
             (SELECT 1 FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=runId);

Line: 1133

	   -- Delete all deleted publications from source and target
	   DELETE_DELETED_PUBLICATIONS(p_source_server_id,inDate);

Line: 1143

		-- This instance is not the source for the selected Target server
	   ERRBUF := CZ_UTILS.GET_TEXT('CZ_SERVER_NOT_SOURCE');

Line: 1150

	-- The selected Target's tns details do not match with that in CZ_SERVERS
	ERRBUF := CZ_UTILS.GET_TEXT('CZ_INCORRECT_TARGET');

Line: 1207

	WHEN DELETE_DEL_PUBLICATION_ERROR THEN
		ROLLBACK;

Line: 1211

      cz_utils.log_report(pkg_name, 'DELETE_DELETED_PUBLICATIONS', null, ERRBUF, fnd_log.LEVEL_ERROR);

Line: 1213

	WHEN DELETE_PUBLICATION_ERROR THEN
		ROLLBACK;

Line: 1217

      cz_utils.log_report(pkg_name, 'DELETE_DELETED_PUBLICATIONS', null, ERRBUF, fnd_log.LEVEL_ERROR);

Line: 1252

	SELECT fndnam_link_name INTO lLinkName
	FROM CZ_SERVERS
	WHERE server_local_id = p_server_id;

Line: 1261

	' SELECT hostname, instance_name INTO ' || lHost || ',' || lSid ||
	' FROM CZ_SERVERS'|| lLinkName ||
	' WHERE source_server_flag = ''1''';

Line: 1265

	SELECT INSTANCE_NAME, HOST_NAME INTO lServerName,lHostName from v$instance;