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
	PROCEDURE DISABLE_FOREIGNKEYS IS
		err_msg	varchar2(500);
	BEGIN
		--Disable Foreign Keys For JBPM_TOKEN
		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';
		
		--Disable Foreign Keys For JBPM_ProcessInstance
		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';
		
		--Disable Foreign Keys For JBPM_Log
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_Log DISABLE CONSTRAINT JBPM_LOG#JBPM_LOG1';
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_Log DISABLE CONSTRAINT JBPM_LOG#JBPM_VARIABLEINSTANCE';
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_Log DISABLE CONSTRAINT JBPM_LOG#JBPM_TASKINSTANCE';
		
		--Disable Foreign Keys For TTV_MESSAGELOG
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.TTV_MESSAGELOG DISABLE CONSTRAINT TTV_MESSAGELOG#JBPM_LOG';
		
		--Disable Foreign Keys For jbpm_variableinstance
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.jbpm_variableinstance DISABLE CONSTRAINT FK_VARINST_TKVARMP';
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.jbpm_variableinstance DISABLE CONSTRAINT FK_VAR_TSKINST';	
	END DISABLE_FOREIGNKEYS;
	
	PROCEDURE ENABLE_FOREIGNKEYS IS
        wk_message		varchar2(500);
        wk_count		number;
        err_msg		varchar2(500);
	BEGIN
		--ENABLE Foreign Keys For JBPM_TOKEN
		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';
		
		--ENABLE Foreign Keys For JBPM_ProcessInstance
		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';
		
		--ENABLE Foreign Keys For JBPM_Log
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_Log ENABLE CONSTRAINT JBPM_LOG#JBPM_LOG1';
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_Log ENABLE CONSTRAINT JBPM_LOG#JBPM_VARIABLEINSTANCE';
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.JBPM_Log ENABLE CONSTRAINT JBPM_LOG#JBPM_TASKINSTANCE';
		
		--ENABLE Foreign Keys For jbpm_variableinstance
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.jbpm_variableinstance ENABLE CONSTRAINT FK_VARINST_TKVARMP';
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.jbpm_variableinstance ENABLE CONSTRAINT FK_VAR_TSKINST';
		
		--ENABLE Foreign Keys For TTV_MESSAGELOG
		EXECUTE IMMEDIATE 'ALTER TABLE WFS.TTV_MESSAGELOG ENABLE CONSTRAINT TTV_MESSAGELOG#JBPM_LOG';
	END ENABLE_FOREIGNKEYS;

	PROCEDURE PURGEOLDDATA(CUTOFFDATE_IN IN TIMESTAMP) IS
		wk_message		varchar2(500);
		wk_count		number;
		err_msg		varchar2(500);
	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;
		BEGIN
			DISABLE_FOREIGNKEYS();
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				wk_message := 'ERROR';
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error when disabling foreing keys.', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;	
		END;
		
		BEGIN
			DELETE FROM wfs.ttv_monitoreddrivehistory
			WHERE createdate < CUTOFFDATE_IN;
			
			INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished ttv_monitoreddrivehistory',systimestamp);
			--COMMIT;	
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from ttv_monitoreddrivehistory', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;
		
		BEGIN
			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;
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from alert_actions_hist', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;
		
		BEGIN
			DELETE 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);
			
			INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished ttv_messagelog',systimestamp);
			--COMMIT;
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from ttv_messagelog', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;
		
		BEGIN
			DELETE 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);
			
			INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished jbpm_log',systimestamp);
			--COMMIT;
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from jbpm_log', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;
		
		BEGIN	
			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;	
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from jbpm_tokenvariablemap', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;
		
		BEGIN
			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;	
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from jbpm_variableinstance', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;
		/*
			BEGIN USELESS DELETES PART 1
			These are for JBPM Tables that we don't use currently.
		*/
		BEGIN
			DELETE FROM WFS.JBPM_COMMENT
			WHERE TASKINSTANCE_ IN (SELECT ti.ID_
			                 			FROM wfs.jbpm_processinstance pi
			                 			JOIN wfs.jbpm_token t 
			                 			ON(   pi.id_ = t.processinstance_)
			                 			JOIN	wfs.jbpm_taskinstance ti
			               	  		ON(		t.id_ = ti.token_)
			               		  WHERE pi.Purge = 1);
			
			INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished JBPM_COMMENT',systimestamp);
			--COMMIT;	
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from JBPM_COMMENT', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;

		BEGIN
			DELETE FROM WFS.JBPM_MESSAGE
			WHERE TASKINSTANCE_ IN (SELECT ti.ID_
			                 			FROM wfs.jbpm_processinstance pi
			                 			JOIN wfs.jbpm_token t 
			                 			ON(   pi.id_ = t.processinstance_)
			                 			JOIN	wfs.jbpm_taskinstance ti
			               	  		ON(		t.id_ = ti.token_)
			               		  WHERE pi.Purge = 1);
			
			INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished JBPM_MESSAGE',systimestamp);
			--COMMIT;	
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from JBPM_MESSAGE', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;
		BEGIN
			DELETE FROM WFS.JBPM_TIMER
			WHERE TASKINSTANCE_ IN (SELECT ti.ID_
			                 			FROM wfs.jbpm_processinstance pi
			                 			JOIN wfs.jbpm_token t 
			                 			ON(   pi.id_ = t.processinstance_)
			                 			JOIN	wfs.jbpm_taskinstance ti
			               	  		ON(		t.id_ = ti.token_)
			               		  WHERE pi.Purge = 1);
			
			INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished JBPM_TIMER',systimestamp);
			--COMMIT;	
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from JBPM_TIMER', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;

		BEGIN
			DELETE FROM WFS.JBPM_TASKACTORPOOL
			WHERE	 TASKINSTANCE_ IN (SELECT ti.ID_
			                 			FROM wfs.jbpm_processinstance pi
			                 			JOIN wfs.jbpm_token t 
			                 			ON(   pi.id_ = t.processinstance_)
			                 			JOIN	wfs.jbpm_taskinstance ti
			               	  		ON(		t.id_ = ti.token_)
			               		  WHERE pi.Purge = 1);
			
			INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished JBPM_TASKACTORPOOL',systimestamp);
			--COMMIT;	
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from JBPM_TASKACTORPOOL', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;
			
		BEGIN
			DELETE FROM WFS.JBPM_POOLEDACTOR
			WHERE SWIMLANEINSTANCE_ IN (SELECT	sli.id_ 
												 FROM		wfs.jbpm_processinstance pi 
												 JOIN		wfs.jbpm_moduleinstance mi
												 ON(		pi.id_ = mi.processinstance_)
												 JOIN		WFS.JBPM_SWIMLANEINSTANCE sli
												 ON(		sli.TASKMGMTINSTANCE_ = mi.id_)
												 WHERE 	pi.Purge = 1);
			
			INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished JBPM_POOLEDACTOR',systimestamp);
			--COMMIT;	
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from JBPM_POOLEDACTOR', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;		
		/*
			END USELESS DELETES PART 1
		*/
		BEGIN
			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_moduleinstance',systimestamp);
			--COMMIT;	
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from jbpm_taskinstance', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;
		
		--USELESS DELETE PART 2
		BEGIN
			DELETE FROM WFS.JBPM_SWIMLANEINSTANCE
			WHERE TASKMGMTINSTANCE_	IN (SELECT	mi.id_ 
												 FROM		wfs.jbpm_processinstance pi 
												 JOIN		wfs.jbpm_moduleinstance mi
												 ON(		pi.id_ = mi.processinstance_)
												 WHERE 	pi.Purge = 1);
			
			INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished JBPM_SWIMLANEINSTANCE',systimestamp);
			--COMMIT;	
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from JBPM_SWIMLANEINSTANCE', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;
		
		BEGIN
			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;	
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from TTV_RESOURCEGROUPQUEUEITEM', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;
		
		BEGIN
			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;	
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from jbpm_moduleinstance', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;
		
		BEGIN
			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;	
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from jbpm_token', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;
		
		BEGIN
			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;	
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from ttv_resourceusagehistory', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;
		
		BEGIN
			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;	
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from ttv_PROCESSINSTANCENOTE', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;
		
		BEGIN
			DELETE FROM wfs.jbpm_processinstance WHERE Purge = 1;
			
			INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished jbpm_processinstance',systimestamp);
			--COMMIT;	
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from jbpm_processinstance', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;

		BEGIN
			ENABLE_FOREIGNKEYS();
			INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished ENABLE_FOREIGNKEYS',systimestamp);
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error when enabling foreing keys.', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
				 GOTO bypass_purge;
		END;
		
		<<bypass_purge>>
		 COMMIT;
		 INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished Purge!!! Cleaing up DELETE_LOG.',systimestamp);
		BEGIN
			DELETE FROM WFS.DELETE_LOG WHERE RECORDTIME < CUTOFFDATE_IN;
			INSERT INTO WFS.DELETE_LOG (Message, recordTime) VALUES('Finished DELETE_LOG',systimestamp);
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				err_msg :=SUBSTR(SQLERRM, 1, 500);
				--DEAR GOD I HOPE THIS NEVER HAPPENS BUT BETTER SAFE THAN SORRY
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES('Error deleting from DELETE_LOG', SYSTIMESTAMP);
				INSERT INTO WFS.DELETE_LOG (MESSAGE, RECORDTIME) VALUES(err_msg, SYSTIMESTAMP);
		END;
		
		COMMIT;
		
	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;
/
