CREATE OR REPLACE PACKAGE SYS.TEMPLATEBUILDERDIRECTV AS

  PROCEDURE BEFORE_UPGRADE;
  PROCEDURE AFTER_UPGRADE;

END;
/

CREATE OR REPLACE PACKAGE BODY SYS.TEMPLATEBUILDERDIRECTV AS

/*---------------------------------------------------
			BEFORE_UPGRADE
----------------------------------------------------*/

	PROCEDURE BEFORE_UPGRADE IS

        wk_message		varchar2(500);
        
	BEGIN
		wk_message := '';
		DELETE FROM SYS.INSTALL_TABLE;
	----------------------------------------------------
	--Disable Foreign Keys
	----------------------------------------------------
		-- DISABLE FK_MESSAGE_RSRCTYP
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_MESSAGE DISABLE CONSTRAINT FK_MESSAGE_RSRCTYP';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO DISABLE FOREIGN KEY FK_MESSAGE_RSRCTYP'')';
	      	GOTO bypass_install;
		END;
		
		-- DISABLE 
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_MESSAGENODEDEFINITIONMAP DISABLE CONSTRAINT FK_MSGNDDEFMP_MSG';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO DISABLE FOREIGN KEY FK_MSGNDDEFMP_MSG'')';
	      	GOTO bypass_install;
		END;

		-- DISABLE 
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_MESSAGENODEDEFINITIONMAP DISABLE CONSTRAINT FK_MSGNDDEFMP_NDDEF';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO DISABLE FOREIGN KEY FK_MSGNDDEFMP_NDDEF'')';
	      	GOTO bypass_install;
		END;

		-- DISABLE FK_NODEDEFINITION_RSRCTYP
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_NODEDEFINITION DISABLE CONSTRAINT FK_NODEDEFINITION_RSRCTYP';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO DISABLE FOREIGN KEY FK_NODEDEFINITION_RSRCTYP'')';
	      	GOTO bypass_install;
		END;

		-- DISABLE FK_NODEDEFINITIONPARENT_CHILD
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_NODEDEFINITIONPARENTMAP DISABLE CONSTRAINT FK_NODEDEFINITIONPARENT_CHILD';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO DISABLE FOREIGN KEY FK_NODEDEFINITIONPARENT_CHILD'')';
	      	GOTO bypass_install;
		END;
		
		-- DISABLE FK_NODEDEFINITIONPARENT_PARENT
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_NODEDEFINITIONPARENTMAP DISABLE CONSTRAINT FK_NODEDEFINITIONPARENT_PARENT';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO DISABLE FOREIGN KEY FK_NODEDEFINITIONPARENT_PARENT'')';
	      	GOTO bypass_install;
		END;
		
		-- DISABLE KEYFK_NDDEFTMPMP_NDDEF
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_NODEDEFINITIONTEMPLATEMAP DISABLE CONSTRAINT FK_NDDEFTMPMP_NDDEF';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO DISABLE FOREIGN KEYFK_NDDEFTMPMP_NDDEF '')';
	      	GOTO bypass_install;
		END;
		
		-- DISABLE FK_NDDEFTMPMP_TMP
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_NODEDEFINITIONTEMPLATEMAP DISABLE CONSTRAINT FK_NDDEFTMPMP_TMP';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO DISABLE FOREIGN KEY FK_NDDEFTMPMP_TMP'')';
	      	GOTO bypass_install;
		END;
		
		-- DISABLE FK_RESOURCEGROUP_RSRCTYP
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_RESOURCEGROUP DISABLE CONSTRAINT FK_RESOURCEGROUP_RSRCTYP';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO DISABLE FOREIGN KEY FK_RESOURCEGROUP_RSRCTYP'')';
	      	GOTO bypass_install;
		END;

		-- DISABLE FK_RESOURCEGROUP_PRTCKY
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_RESOURCEGROUP DISABLE CONSTRAINT FK_RESOURCEGROUP_PRTCKY';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO DISABLE FOREIGN KEY FK_RESOURCEGROUP_PRTCKY'')';
	      	GOTO bypass_install;
		END;
		
		-- DISABLE FK_RSRCGPTMPMP_RSRCGP
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_RESOURCEGROUPTEMPLATEMAP DISABLE CONSTRAINT FK_RSRCGPTMPMP_RSRCGP';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO DISABLE FOREIGN KEY FK_RSRCGPTMPMP_RSRCGP'')';
	      	GOTO bypass_install;
		END;
		
		-- DISABLE FK_RSRCGPTMPMP_TMP
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_RESOURCEGROUPTEMPLATEMAP DISABLE CONSTRAINT FK_RSRCGPTMPMP_TMP';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO DISABLE FOREIGN KEY FK_RSRCGPTMPMP_TMP'')';
	      	GOTO bypass_install;
		END;
		
		-- DISABLE FK_RSRCTYPPRMTR_RSRCTYP
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_RESOURCETYPEPARAMETER DISABLE CONSTRAINT FK_RSRCTYPPRMTR_RSRCTYP';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO DISABLE FOREIGN KEY FK_RSRCTYPPRMTR_RSRCTYP'')';
	      	GOTO bypass_install;
		END;
		
		-- DISABLE FK_TMPPRTCKYMP_PRTCKY
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_TEMPLATEPROTECTIONKEYMAP DISABLE CONSTRAINT FK_TMPPRTCKYMP_PRTCKY';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO DISABLE FOREIGN KEY FK_TMPPRTCKYMP_PRTCKY'')';
	      	GOTO bypass_install;
		END;
		
		-- DISABLE FK_TMPPRTCKYMP_TMP
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_TEMPLATEPROTECTIONKEYMAP DISABLE CONSTRAINT FK_TMPPRTCKYMP_TMP';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO DISABLE FOREIGN KEY FK_TMPPRTCKYMP_TMP'')';
	      	GOTO bypass_install;
		END;
		
		<<bypass_install>>
		
		IF wk_message = 'ERROR'
		THEN
		 NULL;
		ELSE
		 EXECUTE IMMEDIATE 'INSERT INTO sys.install_table values
		       (''Installation completed SUCCESSFULLY'')';
		END IF;
		
		COMMIT;

	END BEFORE_UPGRADE;

/*---------------------------------------------------
			AFTER_UPGRADE
----------------------------------------------------*/
	
	PROCEDURE AFTER_UPGRADE IS

		wk_message		varchar2(500);
        
	BEGIN
		wk_message := '';
		DELETE FROM SYS.INSTALL_TABLE;
	----------------------------------------------------
	--Enable Foreign Keys
	----------------------------------------------------
		-- ENABLE FK_MESSAGE_RSRCTYP
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_MESSAGE ENABLE CONSTRAINT FK_MESSAGE_RSRCTYP';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO ENABLE FOREIGN KEY FK_MESSAGE_RSRCTYP'')';
	      	GOTO bypass_install;
		END;
		
		-- ENABLE 
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_MESSAGENODEDEFINITIONMAP ENABLE CONSTRAINT FK_MSGNDDEFMP_MSG';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO ENABLE FOREIGN KEY FK_MSGNDDEFMP_MSG'')';
	      	GOTO bypass_install;
		END;

		-- ENABLE 
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_MESSAGENODEDEFINITIONMAP ENABLE CONSTRAINT FK_MSGNDDEFMP_NDDEF';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO ENABLE FOREIGN KEY FK_MSGNDDEFMP_NDDEF'')';
	      	GOTO bypass_install;
		END;

		-- ENABLE FK_NODEDEFINITION_RSRCTYP
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_NODEDEFINITION ENABLE CONSTRAINT FK_NODEDEFINITION_RSRCTYP';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO ENABLE FOREIGN KEY FK_NODEDEFINITION_RSRCTYP'')';
	      	GOTO bypass_install;
		END;

		-- ENABLE FK_NODEDEFINITIONPARENT_CHILD
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_NODEDEFINITIONPARENTMAP ENABLE CONSTRAINT FK_NODEDEFINITIONPARENT_CHILD';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO ENABLE FOREIGN KEY FK_NODEDEFINITIONPARENT_CHILD'')';
	      	GOTO bypass_install;
		END;
		
		-- ENABLE FK_NODEDEFINITIONPARENT_PARENT
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_NODEDEFINITIONPARENTMAP ENABLE CONSTRAINT FK_NODEDEFINITIONPARENT_PARENT';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO ENABLE FOREIGN KEY FK_NODEDEFINITIONPARENT_PARENT'')';
	      	GOTO bypass_install;
		END;
		
		-- ENABLE KEYFK_NDDEFTMPMP_NDDEF
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_NODEDEFINITIONTEMPLATEMAP ENABLE CONSTRAINT FK_NDDEFTMPMP_NDDEF';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO ENABLE FOREIGN KEYFK_NDDEFTMPMP_NDDEF '')';
	      	GOTO bypass_install;
		END;
		
		-- ENABLE FK_NDDEFTMPMP_TMP
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_NODEDEFINITIONTEMPLATEMAP ENABLE CONSTRAINT FK_NDDEFTMPMP_TMP';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO ENABLE FOREIGN KEY FK_NDDEFTMPMP_TMP'')';
	      	GOTO bypass_install;
		END;
		
		-- ENABLE FK_RESOURCEGROUP_RSRCTYP
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_RESOURCEGROUP ENABLE CONSTRAINT FK_RESOURCEGROUP_RSRCTYP';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO ENABLE FOREIGN KEY FK_RESOURCEGROUP_RSRCTYP'')';
	      	GOTO bypass_install;
		END;

		-- ENABLE FK_RESOURCEGROUP_PRTCKY
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_RESOURCEGROUP ENABLE CONSTRAINT FK_RESOURCEGROUP_PRTCKY';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO ENABLE FOREIGN KEY FK_RESOURCEGROUP_PRTCKY'')';
	      	GOTO bypass_install;
		END;
		
		-- ENABLE FK_RSRCGPTMPMP_RSRCGP
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_RESOURCEGROUPTEMPLATEMAP ENABLE CONSTRAINT FK_RSRCGPTMPMP_RSRCGP';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO ENABLE FOREIGN KEY FK_RSRCGPTMPMP_RSRCGP'')';
	      	GOTO bypass_install;
		END;
		
		-- ENABLE FK_RSRCGPTMPMP_TMP
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_RESOURCEGROUPTEMPLATEMAP ENABLE CONSTRAINT FK_RSRCGPTMPMP_TMP';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO ENABLE FOREIGN KEY FK_RSRCGPTMPMP_TMP'')';
	      	GOTO bypass_install;
		END;
		
		-- ENABLE FK_RSRCTYPPRMTR_RSRCTYP
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_RESOURCETYPEPARAMETER ENABLE CONSTRAINT FK_RSRCTYPPRMTR_RSRCTYP';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO ENABLE FOREIGN KEY FK_RSRCTYPPRMTR_RSRCTYP'')';
	      	GOTO bypass_install;
		END;
		
		-- ENABLE FK_TMPPRTCKYMP_PRTCKY
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_TEMPLATEPROTECTIONKEYMAP ENABLE CONSTRAINT FK_TMPPRTCKYMP_PRTCKY';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO ENABLE FOREIGN KEY FK_TMPPRTCKYMP_PRTCKY'')';
	      	GOTO bypass_install;
		END;
		
		-- ENABLE FK_TMPPRTCKYMP_TMP
		BEGIN
			EXECUTE IMMEDIATE 'ALTER TABLE WPTB.TB_TEMPLATEPROTECTIONKEYMAP ENABLE CONSTRAINT FK_TMPPRTCKYMP_TMP';
		EXCEPTION
	  		WHEN OTHERS THEN
	 		wk_message := 'ERROR';
	  		EXECUTE IMMEDIATE 'INSERT INTO install_table values
	   		(''UNABLE TO ENABLE FOREIGN KEY FK_TMPPRTCKYMP_TMP'')';
	      	GOTO bypass_install;
		END;
	----------------------------------------------------
	--Fix Sequences
	----------------------------------------------------
		-- Create TB_RESORUCETYPEPARAMETER_SEQ sequence
		DECLARE
			startNum	NUMBER;
		BEGIN
			EXECUTE IMMEDIATE 'DROP SEQUENCE WPTB.TB_RESOURCETYPEPARAMETER_SEQ';
			EXECUTE IMMEDIATE 'SELECT  CASE WHEN MAX(RESOURCETYPEPARAMETERID) IS NULL 
				THEN 1 ELSE MAX(RESOURCETYPEPARAMETERID)+1 END
			FROM	WPTB.TB_RESOURCETYPEPARAMETER' INTO startNum;
			
			EXECUTE IMMEDIATE 'CREATE SEQUENCE WPTB.TB_RESOURCETYPEPARAMETER_SEQ
		                     	INCREMENT BY 1
		                     	START WITH	'||startNum;
		EXCEPTION
		WHEN OTHERS THEN
		
		  wk_message := 'ERROR';
		  EXECUTE IMMEDIATE 'INSERT INTO sys.install_table values
		            (''error in installing of application database objects:  
		               Unable to create TB_RESOURCETYPEPARAMETER_SEQ sequence.'')';
		  GOTO bypass_install;
		
		END;
		
		-- Create TB_RESOURCETYPE_SEQ sequence
		DECLARE
			startNum	NUMBER;
		BEGIN
			EXECUTE IMMEDIATE 'DROP SEQUENCE WPTB.TB_RESOURCETYPE_SEQ';
			EXECUTE IMMEDIATE 'SELECT  CASE WHEN MAX(RESOURCETYPEID) IS NULL 
			THEN 1 ELSE MAX(RESOURCETYPEID)+ 1 END
			FROM	WPTB.TB_RESOURCETYPE' INTO startNum;
		
			EXECUTE IMMEDIATE 'CREATE SEQUENCE WPTB.TB_RESOURCETYPE_SEQ
		                    	INCREMENT BY 1
		                    	START WITH '||startNum;
		EXCEPTION
		WHEN OTHERS THEN
		
		  wk_message := 'ERROR';
		  EXECUTE IMMEDIATE 'INSERT INTO sys.install_table values
		            (''error in installing of application database objects:  
		               Unable to create TB_RESOURCETYPE_SEQ sequence.'')';
		  GOTO bypass_install;
		
		END;
		
		-- Create TB_MESSAGE_SEQ sequence
		DECLARE
			startNum	NUMBER;
		BEGIN
			EXECUTE IMMEDIATE 'DROP SEQUENCE WPTB.TB_MESSAGE_SEQ';
			EXECUTE IMMEDIATE 'SELECT  CASE WHEN MAX(MESSAGEID) IS NULL 
			THEN 1 ELSE MAX(MESSAGEID)+ 1 END
			FROM	WPTB.TB_MESSAGE' INTO startNum;
		
			EXECUTE IMMEDIATE 'CREATE SEQUENCE WPTB.TB_MESSAGE_SEQ
		                    	INCREMENT BY 1
		                    	START WITH '||startNum;                        
		EXCEPTION
		WHEN OTHERS THEN
		
		  wk_message := 'ERROR';
		  EXECUTE IMMEDIATE 'INSERT INTO sys.install_table values
		            (''error in installing of application database objects:  
		               Unable to create TB_MESSAGE_SEQ sequence.'')';
		  GOTO bypass_install;
		END;
		
		-- Create TB_NODEDEFINITION_SEQ sequence
		DECLARE
			startNum	NUMBER;
		BEGIN
			EXECUTE IMMEDIATE 'DROP SEQUENCE WPTB.TB_NODEDEFINITION_SEQ';
			EXECUTE IMMEDIATE 'SELECT  CASE WHEN MAX(NODEDEFINITIONID) IS NULL 
			THEN 1 ELSE MAX(NODEDEFINITIONID)+ 1 END
			FROM	WPTB.TB_NODEDEFINITION' INTO startNum;
		
			EXECUTE IMMEDIATE 'CREATE SEQUENCE WPTB.TB_NODEDEFINITION_SEQ
		             			INCREMENT BY 1
		                    	START WITH '||startNum; 
		EXCEPTION
		WHEN OTHERS THEN
		
		  wk_message := 'ERROR';
		  EXECUTE IMMEDIATE 'INSERT INTO sys.install_table values
		            (''error in installing of application database objects:  
		               Unable to create TB_NODEDEFINITION_SEQ sequence.'')';
		  GOTO bypass_install;
		
		END;
		
		-- Create TB_TEMPLATE_SEQ sequence
		DECLARE
			startNum	NUMBER;
		BEGIN
			EXECUTE IMMEDIATE 'DROP SEQUENCE WPTB.TB_TEMPLATE_SEQ';
			EXECUTE IMMEDIATE 'SELECT  CASE WHEN MAX(TEMPLATEID) IS NULL 
			THEN 1 ELSE MAX(TEMPLATEID)+ 1 END
			FROM	WPTB.TB_TEMPLATE' INTO startNum;
			
			EXECUTE IMMEDIATE 'CREATE SEQUENCE WPTB.TB_TEMPLATE_SEQ
		                    	INCREMENT BY 1
		                    	START WITH '||startNum; 
		                    
		EXCEPTION
		WHEN OTHERS THEN
		
		  wk_message := 'ERROR';
		  EXECUTE IMMEDIATE 'INSERT INTO sys.install_table values
		            (''error in installing of application database objects:  
		               Unable to create TB_TEMPLATE_SEQ sequence.'')';
		  GOTO bypass_install;
		
		END;
		
		-- Create TB_PROTECTIONKEY_SEQ sequence
		DECLARE
			startNum	NUMBER;
		BEGIN
			EXECUTE IMMEDIATE 'DROP SEQUENCE WPTB.TB_PROTECTIONKEY_SEQ';
			EXECUTE IMMEDIATE 'SELECT  CASE WHEN MAX(PROTECTIONKEYID) IS NULL 
			THEN 1 ELSE MAX(PROTECTIONKEYID)+ 1 END
			FROM	WPTB.TB_PROTECTIONKEY' INTO startNum;  
		
			EXECUTE IMMEDIATE 'CREATE SEQUENCE WPTB.TB_PROTECTIONKEY_SEQ
		                    	INCREMENT BY 1
		                   	 	START WITH '||startNum; 
		                    
		EXCEPTION
		WHEN OTHERS THEN
		
		  wk_message := 'ERROR';
		  EXECUTE IMMEDIATE 'INSERT INTO sys.install_table values
		            (''error in installing of application database objects:  
		               Unable to create TB_PROTECTIONKEY_SEQ sequence.'')';
		  GOTO bypass_install;
		
		END;
		
		-- Create TB_RESOURCEGROUP_SEQ sequence
		DECLARE
			startNum	NUMBER;
		BEGIN
			EXECUTE IMMEDIATE 'DROP SEQUENCE WPTB.TB_RESOURCEGROUP_SEQ';
			EXECUTE IMMEDIATE 'SELECT  CASE WHEN MAX(RESOURCEGROUPID) IS NULL 
			THEN 1 ELSE MAX(RESOURCEGROUPID)+ 1 END
			FROM	WPTB.TB_RESOURCEGROUP' INTO startNum;
		
			EXECUTE IMMEDIATE 'CREATE SEQUENCE WPTB.TB_RESOURCEGROUP_SEQ
		                    	INCREMENT BY 1
		                    	START WITH '||startNum; 
		                    
		EXCEPTION
		WHEN OTHERS THEN
		
		  wk_message := 'ERROR';
		  EXECUTE IMMEDIATE 'INSERT INTO sys.install_table values
		            (''error in installing of application database objects:  
		               Unable to create TB_RESOURCEGROUP_SEQ sequence.'')';
		  GOTO bypass_install;
		
		END;

		<<bypass_install>>
		
		IF wk_message = 'ERROR'
		THEN
				NULL;
		ELSE
			EXECUTE IMMEDIATE 'INSERT INTO sys.install_table values
			   (''Installation completed SUCCESSFULLY'')';
		END IF;
		COMMIT;
	END AFTER_UPGRADE;

END;
/
