CREATE OR REPLACE PACKAGE SYS.PURGE_WFS AS

	PROCEDURE PURGEOLDDATA(CUTOFFDATE_IN IN TIMESTAMP);
  	--PROCEDURE DISBABLE_FOREIGNKEYS;
	--PROCEDURE ENBABLE_FOREIGNKEYS;

END;
/

CREATE OR REPLACE PACKAGE BODY SYS.PURGE_WFS AS

  /*----------------------------------------------------------------------------------------------------------
												REDEFTABLES
	----------------------------------------------------------------------------------------------------------*/
  PROCEDURE REDEFTABLES(vschemaname varchar2,vorigtabname varchar2,vinttabname varchar2) IS
    vnum_errors NUMBER := 0;
  BEGIN
    dbms_output.enable(99999);
    dbms_redefinition.can_redef_table(vschemaname,vorigtabname);
    DBMS_REDEFINITION.START_REDEF_TABLE(uname=>vschemaname,orig_table=>vorigtabname,int_table=>vinttabname);
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname=>vschemaname,orig_table=>vorigtabname,int_table=>vinttabname,ignore_errors=>TRUE,num_errors=>vnum_errors,copy_statistics=>TRUE);
    dbms_output.put_line('TABLE_NAME:ERRORS => '||vorigtabname||':'||vnum_errors);
    execute immediate 'insert into '||vschemaname||'.temp_redef_errors select * From dbms_redefinition_errors';
    DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>vschemaname,orig_table=>vorigtabname,int_table=>vinttabname);
  END;	
  
  /*----------------------------------------------------------------------------------------------------------
												CREATETEMPTABLES
	----------------------------------------------------------------------------------------------------------*/
	PROCEDURE CREATETEMPTABLES IS
	BEGIN
		-- creating table TTV_MESSAGELOG_TEMP
		EXECUTE IMMEDIATE 'CREATE TABLE WFS.TTV_MESSAGELOG_TEMP nologging TABLESPACE WFS_DATA AS
							SELECT	*
							FROM 	WFS.TTV_MESSAGELOG
							WHERE	LOGID IN (	SELECT	l.ID_
							                  FROM		wfs.jbpm_processinstance pi
							                  JOIN		wfs.jbpm_Token t 
							                  ON(   	pi.id_ = t.processinstance_)
							                  JOIN		wfs.JBPM_Log l
							                  ON(		t.ID_ = l.token_)
							                	WHERE 	pi.purge <> 1)
							ORDER BY MESSAGELOGID';

		execute immediate 'alter TABLE WFS.TTV_MESSAGELOG_TEMP logging';
    
    -- creating table JBPM_LOG_TEMP
		EXECUTE IMMEDIATE 'CREATE TABLE WFS.JBPM_LOG_TEMP  nologging TABLESPACE WFS_DATA AS
					SELECT	*
					FROM	WFS.JBPM_LOG
					WHERE	token_ IN ( SELECT t.ID_
			                      	FROM wfs.jbpm_processinstance pi
			                     	JOIN wfs.jbpm_Token t 
			                     	ON(   pi.id_ = t.processinstance_)
                    					WHERE pi.purge <> 1)
					ORDER BY ID_';
          
    execute immediate 'alter TABLE WFS.JBPM_LOG_TEMP logging';
    
    EXECUTE IMMEDIATE 'CREATE TABLE WFS.JBPM_VARIABLEINSTANCE_TEMP nologging TABLESPACE WFS_DATA AS 
  								 SELECT 	vi.*
								 FROM		wfs.JBPM_VARIABLEINSTANCE vi
                 where vi.ProcessInstance_ in (select pi.id_ 
                        from   wfs.jbpm_processinstance pi
        								WHERE	pi.purge <> 1)';
	execute immediate 'alter TABLE WFS.JBPM_VARIABLEINSTANCE_TEMP logging';
	
  END CREATETEMPTABLES;
  
  /*----------------------------------------------------------------------------------------------------------
												INSERTINTOTEMPTABLES
	----------------------------------------------------------------------------------------------------------*/
	PROCEDURE INSERTINTOTEMPTABLES(CUTOFFDATE_IN IN TIMESTAMP) IS
	BEGIN
		EXECUTE IMMEDIATE 'INSERT INTO WFS.TTV_MESSAGELOG_TEMP
							SELECT	MESSAGELOGID, LOGID, TYPE, MESSAGEUID, NAME, ISINCOMING, MESSAGE, RESOURCEID, MESSAGEDATETIME, CREATEDATE, UPDATEDATE
							FROM 	WFS.TTV_MESSAGELOG
							WHERE	LOGID IN (	SELECT	l.ID_
							                  FROM		wfs.jbpm_processinstance pi
							                  JOIN		wfs.jbpm_Token t 
							                  ON(   	pi.id_ = t.processinstance_)
							                  JOIN		wfs.JBPM_Log l
							                  ON(		t.ID_ = l.token_)
							                	WHERE 	pi.purge <> 1)
							ORDER BY MESSAGELOGID';
		INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('DONE WITH INSERT TTV_MESSAGELOG_TEMP', SYSTIMESTAMP);
			COMMIT;
		EXECUTE IMMEDIATE 'INSERT INTO WFS.JBPM_LOG_TEMP (ID_, CLASS_, INDEX_, DATE_, TOKEN_, PARENT_, MESSAGE_, EXCEPTION_, 
							ACTION_, NODE_, ENTER_, LEAVE_, DURATION_, TRANSITION_, CHILD_, SOURCENODE_, 
							DESTINATIONNODE_, VARIABLEINSTANCE_, OLDBYTEARRAY_, NEWBYTEARRAY_, OLDDATEVALUE_, 
							NEWDATEVALUE_, OLDDOUBLEVALUE_, NEWDOUBLEVALUE_, OLDLONGIDCLASS_, OLDLONGIDVALUE_, 
							NEWLONGIDCLASS_, NEWLONGIDVALUE_, OLDSTRINGIDCLASS_, OLDSTRINGIDVALUE_, NEWSTRINGIDCLASS_, 
							NEWSTRINGIDVALUE_, OLDLONGVALUE_, NEWLONGVALUE_, OLDSTRINGVALUE_, NEWSTRINGVALUE_, 
							TASKINSTANCE_, TASKACTORID_, TASKOLDACTORID_, SWIMLANEINSTANCE_, CREATEDATE, 
							UPDATEDATE, RESOURCEGROUPID, RESOURCEID, OPERATIONALSTATUSID, ADMINISTRATIVESTATUSID)
					SELECT	ID_, CLASS_, INDEX_, DATE_, TOKEN_, PARENT_, MESSAGE_, EXCEPTION_, 
							ACTION_, NODE_, ENTER_, LEAVE_, DURATION_, TRANSITION_, CHILD_, SOURCENODE_, 
							DESTINATIONNODE_, VARIABLEINSTANCE_, OLDBYTEARRAY_, NEWBYTEARRAY_, OLDDATEVALUE_, 
							NEWDATEVALUE_, OLDDOUBLEVALUE_, NEWDOUBLEVALUE_, OLDLONGIDCLASS_, OLDLONGIDVALUE_, 
							NEWLONGIDCLASS_, NEWLONGIDVALUE_, OLDSTRINGIDCLASS_, OLDSTRINGIDVALUE_, NEWSTRINGIDCLASS_, 
							NEWSTRINGIDVALUE_, OLDLONGVALUE_, NEWLONGVALUE_, OLDSTRINGVALUE_, NEWSTRINGVALUE_, 
							TASKINSTANCE_, TASKACTORID_, TASKOLDACTORID_, SWIMLANEINSTANCE_, CREATEDATE, 
							UPDATEDATE, RESOURCEGROUPID, RESOURCEID, OPERATIONALSTATUSID, ADMINISTRATIVESTATUSID 
					FROM	WFS.JBPM_LOG
					WHERE	token_ IN ( SELECT t.ID_
			                      	FROM wfs.jbpm_processinstance pi
			                     	JOIN wfs.jbpm_Token t 
			                     	ON(   pi.id_ = t.processinstance_)
                    					WHERE pi.purge <> 1)
					ORDER BY ID_';
		INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('DONE WITH INSERT JBPM_LOG_TEMP', SYSTIMESTAMP);
			COMMIT;
		EXECUTE IMMEDIATE 'INSERT INTO WFS.JBPM_VARIABLEINSTANCE_TEMP (ID_,CLASS_,NAME_,CONVERTER_,TOKEN_,TOKENVARIABLEMAP_,PROCESSINSTANCE_,
																							BYTEARRAYVALUE_,DATEVALUE_,DOUBLEVALUE_,LONGIDCLASS_,LONGVALUE_,STRINGIDCLASS_,
																							STRINGVALUE_,TASKINSTANCE_,CREATEDATE,UPDATEDATE)
								 SELECT 	vi.ID_,vi.CLASS_,vi.NAME_,vi.CONVERTER_,vi.TOKEN_,vi.TOKENVARIABLEMAP_,vi.PROCESSINSTANCE_,
											vi.BYTEARRAYVALUE_,vi.DATEVALUE_,vi.DOUBLEVALUE_,vi.LONGIDCLASS_,vi.LONGVALUE_,vi.STRINGIDCLASS_,
											vi.STRINGVALUE_,vi.TASKINSTANCE_,vi.CREATEDATE,vi.UPDATEDATE
								 FROM		wfs.jbpm_processinstance pi
								 JOIN		wfs.JBPM_VARIABLEINSTANCE vi
								 ON(		pi.ID_ = vi.ProcessInstance_)
								 WHERE	pi.purge <> 1';
		INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('DONE WITH INSERT JBPM_VARIABLEINSTANCE_TEMP', SYSTIMESTAMP);
			COMMIT;
	END INSERTINTOTEMPTABLES;
	/*----------------------------------------------------------------------------------------------------------
												DROPTABLES
	----------------------------------------------------------------------------------------------------------*/
	PROCEDURE DROPTABLES IS
	BEGIN
		EXECUTE IMMEDIATE 'DROP TABLE WFS.TTV_MESSAGELOG';
		EXECUTE IMMEDIATE 'DROP TABLE WFS.JBPM_LOG';
		EXECUTE IMMEDIATE 'DROP TABLE WFS.JBPM_VARIABLEINSTANCE';
	END DROPTABLES;
	
	/*----------------------------------------------------------------------------------------------------------
												RENAMETEMPTABLES
	----------------------------------------------------------------------------------------------------------*/
	PROCEDURE RENAMETEMPTABLES IS
	BEGIN
--		EXECUTE IMMEDIATE 'ALTER TABLE WFS.TTV_MESSAGELOG RENAME TO TTV_MESSAGELOG_BACKUP';
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.TTV_MESSAGELOG_TEMP RENAME TO TTV_MESSAGELOG';
--		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_LOG RENAME TO JBPM_LOG_BACKUP';
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_LOG_TEMP RENAME TO JBPM_LOG';
--    EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_VARIABLEINSTANCE RENAME TO JBPM_VARIABLEINSTANCE_BACKUP';    
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_VARIABLEINSTANCE_TEMP RENAME TO JBPM_VARIABLEINSTANCE';
	END RENAMETEMPTABLES;
	
	/*----------------------------------------------------------------------------------------------------------
												DROPFOREIGNKEYS
	----------------------------------------------------------------------------------------------------------*/
	PROCEDURE DROPFOREIGNKEYS IS
	BEGIN
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_TOKEN DISABLE CONSTRAINT FK_TOKEN_PARENT';
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_TOKEN DISABLE CONSTRAINT FK_TOKEN_PROCINST';
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_TOKEN DISABLE CONSTRAINT FK_TOKEN_SUBPI';
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_ProcessInstance DISABLE CONSTRAINT JBPM_PINSTANCE#JBPM_TOKEN1';
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_ProcessInstance DISABLE CONSTRAINT JBPM_PINSTANCE#JBPM_TOKEN2';
	END DROPFOREIGNKEYS;
	
	
	/*----------------------------------------------------------------------------------------------------------
												CREATEPRIMARYKEYS
	----------------------------------------------------------------------------------------------------------*/
	PROCEDURE CREATEPRIMARYKEYS IS
	BEGIN
		-- Creating Primary Key on TTV_MESSAGELOG
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.TTV_MESSAGELOG
		ADD ( CONSTRAINT PK_TTV_MESSAGELOG PRIMARY KEY (MESSAGELOGID) USING INDEX TABLESPACE WFS_INDEX )';
		
		-- Creating Primary Key on JBPM_LOG
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_LOG ADD ( CONSTRAINT PK_JBPM_LOG PRIMARY KEY (ID_)  USING INDEX TABLESPACE WFS_INDEX )';
		--Creating Primary Key on JBPM_VARIABLEINSTANCE
      EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_VARIABLEINSTANCE ADD ( CONSTRAINT PK_JBPM_VARIABLEINSTANCE PRIMARY KEY (ID_) USING INDEX TABLESPACE WFS_INDEX )';
	END CREATEPRIMARYKEYS;
	
	/*----------------------------------------------------------------------------------------------------------
												CREATEFOREIGNKEYS
	----------------------------------------------------------------------------------------------------------*/
	PROCEDURE CREATEFOREIGNKEYS IS
	BEGIN
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_TOKEN ENABLE CONSTRAINT FK_TOKEN_PARENT';
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_TOKEN ENABLE CONSTRAINT FK_TOKEN_PROCINST';
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_TOKEN ENABLE CONSTRAINT FK_TOKEN_SUBPI';
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_ProcessInstance ENABLE CONSTRAINT JBPM_PINSTANCE#JBPM_TOKEN1';
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_ProcessInstance ENABLE CONSTRAINT JBPM_PINSTANCE#JBPM_TOKEN2';
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.TTV_MESSAGELOG ADD (
					                      CONSTRAINT TTV_MESSAGELOG#TTV_RESOURCE
					                              FOREIGN KEY (RESOURCEID) 
					                              REFERENCES WFS.TTV_RESOURCE (RESOURCEID),
					                      CONSTRAINT TTV_MESSAGELOG#JBPM_LOG
					                              FOREIGN KEY (LOGID) 
					                              REFERENCES WFS.JBPM_LOG (ID_))';
	    
	      EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_LOG ADD (
	                                  CONSTRAINT JBPM_LOG#TTV_PSTATUS1 
	                                          FOREIGN KEY (ADMINISTRATIVESTATUSID) 
	                                          REFERENCES WFS.TTV_PROCESSSTATUS (PROCESSSTATUSID),
	                                  CONSTRAINT JBPM_LOG#TTV_PSTATUS2 
	                                          FOREIGN KEY (OPERATIONALSTATUSID) 
	                                          REFERENCES WFS.TTV_PROCESSSTATUS (PROCESSSTATUSID),
	                                  CONSTRAINT JBPM_LOG#TTV_RESOURCE 
	                                          FOREIGN KEY (RESOURCEID) 
	                                          REFERENCES WFS.TTV_RESOURCE (RESOURCEID),
	                                  CONSTRAINT JBPM_LOG#TTV_RESOURCEGROUP 
	                                          FOREIGN KEY (RESOURCEGROUPID) 
	                                          REFERENCES WFS.TTV_RESOURCEGROUP (RESOURCEGROUPID),
	                                  CONSTRAINT JBPM_LOG#JBPM_VARIABLEINSTANCE 
	                                          FOREIGN KEY (VARIABLEINSTANCE_) 
	                                          REFERENCES WFS.JBPM_VARIABLEINSTANCE (ID_),
	                                  CONSTRAINT JBPM_LOG#JBPM_TRANSITION 
	                                          FOREIGN KEY (TRANSITION_) 
	                                          REFERENCES WFS.JBPM_TRANSITION (ID_),
	                                  CONSTRAINT JBPM_LOG#JBPM_TASKINSTANCE 
	                                          FOREIGN KEY (TASKINSTANCE_) 
	                                          REFERENCES WFS.JBPM_TASKINSTANCE (ID_),
	                                  CONSTRAINT JBPM_LOG#JBPM_SWIMLANEINSTANCE 
	                                          FOREIGN KEY (SWIMLANEINSTANCE_) 
	                                          REFERENCES WFS.JBPM_SWIMLANEINSTANCE (ID_),
	                                  CONSTRAINT JBPM_LOG#JBPM_LOG1 
	                                          FOREIGN KEY (PARENT_) 
	                                          REFERENCES WFS.JBPM_LOG (ID_),
	                                  CONSTRAINT JBPM_LOG#JBPM_BYTEARRAY1 
	                                          FOREIGN KEY (OLDBYTEARRAY_) 
	                                          REFERENCES WFS.JBPM_BYTEARRAY (ID_),
	                                  CONSTRAINT JBPM_LOG#JBPM_BYTEARRAY2 
	                                          FOREIGN KEY (NEWBYTEARRAY_) 
	                                          REFERENCES WFS.JBPM_BYTEARRAY (ID_),
	                                  CONSTRAINT JBPM_LOG#JBPM_ACTION 
	                                          FOREIGN KEY (ACTION_) 
	                                          REFERENCES WFS.JBPM_ACTION (ID_))';
	      EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_VARIABLEINSTANCE ADD ( 
                      CONSTRAINT FK_VAR_TSKINST
                          FOREIGN KEY(TASKINSTANCE_)
                          REFERENCES WFS.JBPM_TASKINSTANCE(ID_),
                      CONSTRAINT FK_VARINST_TKVARMP
                          FOREIGN KEY(TOKENVARIABLEMAP_)
                          REFERENCES WFS.JBPM_TOKENVARIABLEMAP(ID_),
                      CONSTRAINT FK_BYTEINST_ARRAY
                          FOREIGN KEY(BYTEARRAYVALUE_)
                          REFERENCES WFS.JBPM_BYTEARRAY(ID_))';
/*      	EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_TASKINSTANCE ADD (
                                  CONSTRAINT JBPM_TINSTANCE#JBPM_MINSTANCE 
                                          FOREIGN KEY (TASKMGMTINSTANCE_) 
                                          REFERENCES WFS.JBPM_MODULEINSTANCE (ID_),
                                  CONSTRAINT JBPM_TINSTANCE#JBPM_TASK 
                                          FOREIGN KEY (TASK_) 
                                          REFERENCES WFS.JBPM_TASK (ID_),
                                  CONSTRAINT JBPM_TINSTANCE#JBPM_SLINSTANCE 
                                          FOREIGN KEY (SWIMLANINSTANCE_) 
                                          REFERENCES WFS.JBPM_SWIMLANEINSTANCE (ID_))';*/
	END CREATEFOREIGNKEYS;
	
	/*----------------------------------------------------------------------------------------------------------
												CREATETRIGGERS
	----------------------------------------------------------------------------------------------------------*/
	PROCEDURE CREATETRIGGERS IS
	BEGIN	
		-- Create Trigger TRG_TTV_MESSAGELOG_SEQ
      EXECUTE IMMEDIATE 'CREATE TRIGGER WFS.TRG_TTV_MESSAGELOG_SEQ
                              BEFORE INSERT ON WFS.TTV_MessageLog
                              FOR EACH ROW
                              BEGIN
                                  IF :NEW.MessageLogId IS NULL THEN
                                      SELECT WFS.TTV_MessageLog_SEQ.NEXTVAL INTO :NEW.MessageLogId FROM DUAL;
                                  END IF;
                              END;';
  
  		-- Create Trigger TRG_TTV_MESSAGELOG_UPD
      EXECUTE IMMEDIATE 'CREATE TRIGGER WFS.TRG_TTV_MESSAGELOG_UPD
                              BEFORE UPDATE
                              ON WFS.TTV_MessageLog
                              FOR EACH ROW
                              DECLARE updatedate      date;
                              BEGIN
                              SELECT  SYSTIMESTAMP
                              INTO    updatedate
                              FROM    DUAL;
                              :NEW.UpdateDate := updatedate;
                              END;';
      
      -- Create Trigger TRG_JBPM_LOG_SEQ
		EXECUTE IMMEDIATE 'CREATE TRIGGER WFS.TRG_JBPM_LOG_SEQ
		                      BEFORE INSERT ON WFS.JBPM_LOG
		                      FOR EACH ROW
		                      BEGIN
		                          IF :NEW.ID_ IS NULL THEN
		                              SELECT WFS.JBPM_LOG_SEQ.NEXTVAL INTO :NEW.ID_ FROM DUAL;
		                          END IF;
		                      END;';

		-- Create Trigger TRG_JBPM_LOG_UPD
	   EXECUTE IMMEDIATE 'CREATE TRIGGER WFS.TRG_JBPM_LOG_UPD
		                      BEFORE UPDATE
		                      ON WFS.JBPM_LOG
		                      FOR EACH ROW
		                      DECLARE updatedate      date;
		                      BEGIN
		                      SELECT  SYSTIMESTAMP
		                      INTO    updatedate
		                      FROM    DUAL;
		                      :NEW.UpdateDate := updatedate;
		                      END;';
		
		-- Create Trigger TRG_JBPM_VARIABLEINSTANCE_SEQ
      EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER WFS.TRG_JBPM_VARIABLEINSTANCE_SEQ
                               BEFORE INSERT ON WFS.JBPM_VARIABLEINSTANCE
                               FOR EACH ROW
                               BEGIN

                                 IF :NEW.ID_ IS NULL THEN
                                   SELECT WFS.JBPM_VARIABLEINSTANCE_SEQ.NEXTVAL INTO :NEW.ID_ FROM DUAL;
                                 END IF;

                               END;';

      -- Create Trigger TRG_JBPM_VARIABLEINSTANCE_UPD
      EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER WFS.TRG_JBPM_VARIABLEINSTANCE_UPD
                               BEFORE UPDATE ON WFS.JBPM_VARIABLEINSTANCE
                               FOR EACH ROW
                               BEGIN

                                 SELECT SYSTIMESTAMP
                                 INTO :NEW.UpdateDate
                                 FROM DUAL;

                               END;';
	END CREATETRIGGERS;
	
	/*----------------------------------------------------------------------------------------------------------
												CREATEINDEXES
	----------------------------------------------------------------------------------------------------------*/
	PROCEDURE CREATEINDEXES IS
	BEGIN
		-- Create index IDX_JBPM_LOG_01
		EXECUTE IMMEDIATE 'CREATE INDEX WFS.IDX_JBPM_LOG_01 
                           ON WFS.JBPM_LOG (CLASS_, NODE_, TOKEN_)
                           TABLESPACE WFS_INDEX nologging';
		EXECUTE IMMEDIATE 'ALTER INDEX WFS.IDX_JBPM_LOG_01 logging';
                           
		-- Create Index IDX_JBPM_VARIABLEINSTANCE_01 on JBPM_VARIABLEINSTANCE
   	EXECUTE IMMEDIATE 'CREATE INDEX WFS.IDX_JBPM_VARIABLEINSTANCE_01
                           ON WFS.JBPM_VARIABLEINSTANCE (NAME_, STRINGVALUE_)
                           TABLESPACE WFS_INDEX nologging';
   	EXECUTE IMMEDIATE 'ALTER INDEX WFS.IDX_JBPM_VARIABLEINSTANCE_01 logging';
                           
		-- Create Index IDX_JBPM_VARIABLEINSTANCE_02 on JBPM_VARIABLEINSTANCE
		EXECUTE IMMEDIATE 'CREATE INDEX WFS.IDX_JBPM_VARIABLEINSTANCE_02 
		                     ON WFS.JBPM_VARIABLEINSTANCE (PROCESSINSTANCE_, NAME_, STRINGVALUE_, TOKENVARIABLEMAP_, CLASS_)
		                      TABLESPACE WFS_INDEX nologging';
		EXECUTE IMMEDIATE 'ALTER INDEX WFS.IDX_JBPM_VARIABLEINSTANCE_02 logging';
                          
		-- Create Index IDX_JBPM_VARIABLEINSTANCE_03 on JBPM_VARIABLEINSTANCE
		EXECUTE IMMEDIATE 'CREATE INDEX WFS.IDX_JBPM_VARIABLEINSTANCE_03
		                      ON WFS.JBPM_VARIABLEINSTANCE(TASKINSTANCE_)
		                      TABLESPACE WFS_INDEX nologging';
		EXECUTE IMMEDIATE 'ALTER INDEX WFS.IDX_JBPM_VARIABLEINSTANCE_03 logging';
                          
		-- Create Index IDX_JBPM_VARIABLEINSTANCE_04 on JBPM_VARIABLEINSTANCE
		EXECUTE IMMEDIATE 'CREATE INDEX WFS.IDX_JBPM_VARIABLEINSTANCE_04
		                      ON WFS.JBPM_VARIABLEINSTANCE(TOKENVARIABLEMAP_)
		                      TABLESPACE WFS_INDEX nologging';
		EXECUTE IMMEDIATE 'ALTER INDEX WFS.IDX_JBPM_VARIABLEINSTANCE_04 logging';


		-- Create Index IDX_TTV_MESSAGELOG_01 on TTV_MESSAGELOG
		EXECUTE IMMEDIATE 'CREATE INDEX WFS.IDX_TTV_MESSAGELOG_01  ON WFS.TTV_MESSAGELOG (LOGID ASC) TABLESPACE WFS_INDEX nologging';
		EXECUTE IMMEDIATE 'ALTER INDEX WFS.IDX_TTV_MESSAGELOG_01 logging';
	
  END CREATEINDEXES;

	/*----------------------------------------------------------------------------------------------------------
												PURGEOLDDATA
	----------------------------------------------------------------------------------------------------------*/
	PROCEDURE PURGEOLDDATA(CUTOFFDATE_IN IN TIMESTAMP) IS
		wk_message		varchar2(500);
		wk_count		number;
		err_msg		varchar2(500);
    v_dtstamp varchar2(14);
	BEGIN
		
		INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Starting',systimestamp);
		COMMIT;
		UPDATE	wfs.JBPM_ProcessInstance
		SET		Purge = 1
		WHERE		ID_ IN (
						SELECT pi.ID_ 
						FROM 	WFS.JBPM_ProcessInstance pi
						LEFT JOIN	WFS.JBPM_VARIABLEINSTANCE vi
						ON(		pi.ID_ =PROCESSINSTANCE_ AND vi.NAME_ = 'licEnd')
						WHERE	pi.createDate < CUTOFFDATE_IN
						AND    (((STRINGVALUE_ IS NULL OR STRINGVALUE_ = ' ') AND DATEVALUE_ IS NULL)
							OR (CASE WHEN DATEVALUE_ IS NULL 
						            THEN (CASE WHEN STRINGVALUE_ = ' ' THEN NULL ELSE to_date(TRANSLATE(STRINGVALUE_, 'T', ' '), 'YYYY-MM-DD HH24:MI:SS') END)
						            ELSE DATEVALUE_ END) < (systimestamp - INTERVAL '1' DAY)));		
	  INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished marking work orders.',systimestamp);
		COMMIT;
		DELETE FROM wfs.ttv_monitoreddrivehistory
		WHERE createdate < CUTOFFDATE_IN;
		INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished ttv_monitoreddrivehistory',systimestamp);
		COMMIT;
		DELETE FROM wfs.alert_actions_hist
		WHERE hist_key IN ( SELECT  hist_key
		                   FROM    wfs.alerts_hist 
		                   WHERE   TIME_KEY < CUTOFFDATE_IN);
		
		INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished alert_actions_hist',systimestamp);
		COMMIT;
		                 
		DELETE FROM wfs.alerts_hist 
		WHERE   TIME_KEY < CUTOFFDATE_IN;
		
		INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished alerts_hist',systimestamp);
		COMMIT;
		---------------------------------------------------------------------------------------------
/*    BEGIN
      select to_char(sysdate,'YYMMDDHH24MI') into v_dtstamp from dual;
      
      begin
        execute immediate 'drop table wfs.temp_redef_errors purge';
      exception
        when others then null;
      end;

      execute immediate 'create table wfs.temp_redef_errors as select * from dba_redefinition_errors where 1=2';
      
      execute immediate 'create table wfs.ttv_msglog_'||v_dtstamp||' nologging AS SELECT	* FROM 	WFS.TTV_MESSAGELOG WHERE	LOGID IN (	SELECT	l.ID_ FROM		wfs.jbpm_processinstance pi JOIN		wfs.jbpm_Token t  ON(   	pi.id_ = t.processinstance_) JOIN		wfs.JBPM_Log l ON(		t.ID_ = l.token_) WHERE 	pi.purge <> 1) ORDER BY MESSAGELOGID';
      execute immediate 'alter table wfs.TTV_MESSAGELOG logging';
      REDEFTABLES('WFS','TTV_MESSAGELOG','ttv_msglog_'||v_dtstamp);

      execute immediate 'create table wfs.jbpm_log_'||v_dtstamp||' nologging AS  SELECT	* FROM	WFS.JBPM_LOG WHERE	token_ IN ( SELECT t.ID_ FROM wfs.jbpm_processinstance pi JOIN wfs.jbpm_Token t  ON(   pi.id_ = t.processinstance_) WHERE pi.purge <> 1) ORDER BY ID_';
      execute immediate 'alter table wfs.JBPM_LOG logging';
      REDEFTABLES('WFS','JBPM_LOG','jbpm_log_'||v_dtstamp);

      execute immediate 'create table wfs.jbpm_vinst_'||v_dtstamp||' nologging AS  SELECT 	vi.* FROM		wfs.JBPM_VARIABLEINSTANCE vi WHERE vi.ProcessInstance_ IN (SELECT pi.ID_ wfs.jbpm_processinstance pi WHERE	pi.purge <> 1)';
      execute immediate 'alter table wfs.JBPM_VARIABLEINSTANCE logging';
      REDEFTABLES('WFS','JBPM_VARIABLEINSTANCE','jbpm_vinst_'||v_dtstamp);

      
    END;
*/		BEGIN
			DROPFOREIGNKEYS();
			INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('DONE WITH DROPFOREIGNKEYS', SYSTIMESTAMP);
			COMMIT;
		EXCEPTION 
		   WHEN OTHERS THEN
		   
		     	wk_message := 'ERROR';
		   	INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('ERROR WHILE DROPFOREIGNKEYS', SYSTIMESTAMP);
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
		     	GOTO bypass_install;
		END;
		
		BEGIN
			CREATETEMPTABLES();
			INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('DONE WITH CREATETEMPTABLES', SYSTIMESTAMP);
			COMMIT;
		EXCEPTION 
		   WHEN OTHERS THEN
		   
		     	wk_message := 'ERROR';
		   	INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('ERROR WHILE CREATETEMPTABLES', SYSTIMESTAMP);
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
		     	GOTO bypass_install;
		END;
		
/*		BEGIN
			INSERTINTOTEMPTABLES(CUTOFFDATE_IN);
			INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('DONE WITH INSERTINTOTEMPTABLES', SYSTIMESTAMP);
			COMMIT;
		EXCEPTION 
		   WHEN OTHERS THEN
		   
		     	wk_message := 'ERROR';
		   	INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('ERROR WHILE INSERTINTOTEMPTABLES', SYSTIMESTAMP);
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
		     	GOTO bypass_install;
		END;
*/		
		BEGIN
			DROPTABLES();
			INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('DONE WITH DROPTABLES', SYSTIMESTAMP);
			COMMIT;
		EXCEPTION 
		   WHEN OTHERS THEN
		   
		     	wk_message := 'ERROR';
		   	INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('ERROR WHILE DROPTABLES', SYSTIMESTAMP);
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
		     	GOTO bypass_install;
		END;
		
		BEGIN
			RENAMETEMPTABLES();
			INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('DONE WITH RENAMETEMPTABLES', SYSTIMESTAMP);
			COMMIT;
		EXCEPTION 
		   WHEN OTHERS THEN
		   
		     	wk_message := 'ERROR';
		   	INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('ERROR WHILE RENAMETEMPTABLES', SYSTIMESTAMP);
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
		     	GOTO bypass_install;  
		END;
		
		BEGIN
			CREATEPRIMARYKEYS();
			INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('DONE WITH CREATEPRIMARYKEYS', SYSTIMESTAMP);
			COMMIT;
		EXCEPTION 
		   WHEN OTHERS THEN
		   
		     	wk_message := 'ERROR';
		   	INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('ERROR WHILE CREATEPRIMARYKEYS', SYSTIMESTAMP);
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
		     	GOTO bypass_install;        
		END;
		
		BEGIN
			CREATETRIGGERS();
			INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('DONE WITH CREATETRIGGERS', SYSTIMESTAMP);
			COMMIT;
		EXCEPTION 
		   WHEN OTHERS THEN
		   
		     	wk_message := 'ERROR';
		   	INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('ERROR WHILE CREATETRIGGERS', SYSTIMESTAMP);
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
		     	GOTO bypass_install;        
		END;
		
		BEGIN
			CREATEINDEXES();
			INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('DONE WITH CREATEINDEXES', SYSTIMESTAMP);
			COMMIT;
		EXCEPTION 
		   WHEN OTHERS THEN
		   
		     	wk_message := 'ERROR';
		   	INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('ERROR WHILE CREATEINDEXES', SYSTIMESTAMP);
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
		     	GOTO bypass_install;	        
		END;
   
   execute immediate 'COMMENT ON TABLE WFS.TTV_MESSAGELOG  IS ''Stores messages for each node''';
   execute immediate 'COMMENT ON TABLE WFS.JBPM_LOG  IS ''Stores process logs created by jBPM or application code during process execution
  Foreign Keys
  Token table - the token that the logs belong to
  Token table - the child token that the logs belong to
  Log table - parent/child relationship between logs
  Transition table - the transition the logs belong to
  Node table - the source node the logs belong to
  Node table - the destination node the logs belong to
  Variable Instance table - the variable instance that is referred to in the log
  Byte Array table - the old value of the byte array
  Byte Array table - the new value of the byte array
  Task Instance table - The task instance that is referred to in the log
  Swim Lane Instance table - the swimlane instance that is referred to in the log''';
  execute immediate 'COMMENT ON TABLE WFS.JBPM_VARIABLEINSTANCE  IS ''Stores information about process variable instances
            Foreign Keys
            Token table - the token that the variables belong to
            Token Variable Map table - mapping to the module instance for the token
            Process Instance table - the process instance the variables belong to
            Byte Array table - the byte array stored for a serialized object process variable instance
            Task Instance table - the task instance the variables belong to''';
 
---------------------------------------------------------------------------------------------
		DELETE FROM wfs.jbpm_tokenvariablemap
		WHERE token_  IN (SELECT t.ID_
		                 FROM wfs.jbpm_processinstance pi
		                 JOIN wfs.jbpm_token t 
		                 ON(   pi.id_ = t.processinstance_)
	                    WHERE pi.Purge = 1);
		
		INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished jbpm_tokenvariablemap',systimestamp);
		COMMIT;
		
		DELETE FROM wfs.jbpm_taskinstance
		WHERE TOKEN_ IN ( SELECT t.ID_
		                 FROM wfs.jbpm_processinstance pi
		                 JOIN wfs.jbpm_token t 
		                 ON(   pi.id_ = t.processinstance_)
	                    WHERE pi.Purge = 1);
		
		INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished jbpm_taskinstance',systimestamp);
		COMMIT;
		
		DELETE FROM wfs.TTV_RESOURCEGROUPQUEUEITEM
		WHERE TOKENID IN ( SELECT t.ID_
		                 FROM wfs.jbpm_processinstance pi
		                 JOIN wfs.jbpm_token t 
		                 ON(   pi.id_ = t.processinstance_)
		                 WHERE pi.Purge = 1);
		
		INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished TTV_RESOURCEGROUPQUEUEITEM',systimestamp);
		COMMIT;
		
		DELETE FROM wfs.jbpm_moduleinstance
	  WHERE processinstance_ IN (SELECT id_ FROM wfs.jbpm_processinstance WHERE purge  = 1);
		
		INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished jbpm_moduleinstance',systimestamp);
		COMMIT;
		
		DELETE FROM wfs.jbpm_variableinstance
	  WHERE processinstance_ IN (SELECT id_ FROM wfs.jbpm_processinstance WHERE purge  = 1);
		
		INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished jbpm_variableinstance',systimestamp);
		COMMIT;
		
		DELETE FROM wfs.jbpm_token
	  WHERE processinstance_ IN (SELECT id_ FROM wfs.jbpm_processinstance WHERE purge  = 1);
		
		INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished jbpm_token',systimestamp);
		COMMIT;
		
		DELETE FROM wfs.ttv_resourceusagehistory
	  WHERE processinstanceid IN (SELECT id_ FROM wfs.jbpm_processinstance WHERE purge  = 1);
		
		INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished ttv_resourceusagehistory',systimestamp);
		COMMIT;
		
		DELETE FROM wfs.ttv_PROCESSINSTANCENOTE
	  WHERE processinstanceid IN (SELECT id_ FROM wfs.jbpm_processinstance WHERE purge  = 1);
		
		INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished ttv_PROCESSINSTANCENOTE',systimestamp);
		COMMIT;
		
		DELETE FROM wfs.jbpm_processinstance
	  WHERE purge  = 1;
		
		INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished jbpm_processinstance!!!',systimestamp);
		COMMIT;
		
		BEGIN
			CREATEFOREIGNKEYS();
			INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('DONE WITH CREATEFOREIGNKEYS', SYSTIMESTAMP);
			COMMIT;
			EXCEPTION 
		   WHEN OTHERS THEN
		   
		     	wk_message := 'ERROR';
		   	INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('ERROR WHILE CREATEFOREIGNKEYS', SYSTIMESTAMP);
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
		     	GOTO bypass_install;      
			END;	
		
		<<bypass_install>>
  
          IF wk_message = 'ERROR' THEN
          
             COMMIT;
             
          ELSE
          	ROLLBACK;
             INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Purge completed successfully!!!',systimestamp);
             
          END IF;
	  
	EXCEPTION
	  WHEN OTHERS THEN
	  		ROLLBACK;
	  		err_msg :=SUBSTR(SQLERRM, 1, 500);
			INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
			COMMIT;
	END PURGEOLDDATA;
END;
/
