IF (OBJECT_ID('cleanInstance') IS NOT NULL)
  DROP PROCEDURE cleanInstance;
GO
IF (OBJECT_ID('TEMP_CLEANUP') IS NOT NULL)
  DROP TABLE TEMP_CLEANUP;
GO

DECLARE @I_STATE INT;
SET @I_STATE = 30;
DECLARE @L_ACTIVE DATETIME;
SET @L_ACTIVE=CURRENT_TIMESTAMP-2;
SELECT ID INTO TEMP_CLEANUP FROM ODE_PROCESS_INSTANCE WHERE INSTANCE_STATE = @I_STATE AND LAST_ACTIVE_TIME < @L_ACTIVE;
GO

CREATE PROCEDURE cleanInstance
AS
BEGIN
	PRINT ' Start deleting instance data with instance ids ';
	BEGIN TRANSACTION;
	DELETE FROM ODE_EVENT WHERE INSTANCE_ID IN (SELECT ID FROM TEMP_CLEANUP);
  	DELETE FROM ODE_CORSET_PROP WHERE CORRSET_ID IN (SELECT cs.CORRELATION_SET_ID FROM ODE_CORRELATION_SET cs WHERE cs.SCOPE_ID IN (SELECT os.SCOPE_ID FROM ODE_SCOPE os WHERE	 os.PROCESS_INSTANCE_ID IN (SELECT ID FROM TEMP_CLEANUP)));
  	DELETE FROM ODE_CORRELATION_SET WHERE SCOPE_ID IN (SELECT os.SCOPE_ID FROM ODE_SCOPE os WHERE os.PROCESS_INSTANCE_ID IN (SELECT ID FROM TEMP_CLEANUP));
  	DELETE FROM ODE_PARTNER_LINK WHERE SCOPE_ID IN (SELECT os.SCOPE_ID FROM ODE_SCOPE os WHERE os.PROCESS_INSTANCE_ID IN (SELECT ID FROM TEMP_CLEANUP));
  	DELETE FROM ODE_XML_DATA_PROP WHERE XML_DATA_ID IN (SELECT xd.XML_DATA_ID FROM ODE_XML_DATA xd WHERE xd.SCOPE_ID IN (SELECT os.SCOPE_ID FROM ODE_SCOPE os WHERE os.PROCESS_INSTANCE_ID IN (SELECT ID FROM TEMP_CLEANUP)));
  	DELETE FROM ODE_XML_DATA WHERE SCOPE_ID IN (SELECT os.SCOPE_ID FROM ODE_SCOPE os WHERE os.PROCESS_INSTANCE_ID IN (SELECT ID FROM TEMP_CLEANUP));
  	DELETE FROM ODE_SCOPE WHERE PROCESS_INSTANCE_ID IN (SELECT ID FROM TEMP_CLEANUP);
  	DELETE FROM ODE_MEX_PROP WHERE MEX_ID IN (SELECT mex.MESSAGE_EXCHANGE_ID FROM ODE_MESSAGE_EXCHANGE mex WHERE mex.PROCESS_INSTANCE_ID IN (SELECT ID FROM TEMP_CLEANUP));
  	DELETE FROM ODE_MESSAGE WHERE MESSAGE_EXCHANGE_ID IN (SELECT mex.MESSAGE_EXCHANGE_ID FROM ODE_MESSAGE_EXCHANGE mex WHERE mex.PROCESS_INSTANCE_ID IN (SELECT ID FROM TEMP_CLEANUP));
  	DELETE FROM ODE_MESSAGE_EXCHANGE WHERE PROCESS_INSTANCE_ID IN (SELECT ID FROM TEMP_CLEANUP);
  	DELETE FROM ODE_MESSAGE_ROUTE WHERE PROCESS_INSTANCE_ID IN (SELECT ID FROM TEMP_CLEANUP);
  	DELETE FROM ODE_PROCESS_INSTANCE WHERE ID IN (SELECT ID FROM TEMP_CLEANUP);
	COMMIT;
	PRINT ' End deleting instance data with instance ids ';
END
GO

BEGIN
  PRINT (' Starting cleanInstance procedure ');
  EXEC cleanInstance;
  PRINT (' Ending cleanInstance procedure '); 
END
GO

IF (OBJECT_ID('cleanTaskInstance') IS NOT NULL)
  DROP PROCEDURE cleanTaskInstance
GO
IF (OBJECT_ID('TEMP_CLEANUP') IS NOT NULL)
  DROP TABLE TEMP_CLEANUP;
GO

SELECT ID INTO TEMP_CLEANUP FROM HT_TASK WHERE STATUS ='COMPLETED';
GO

CREATE PROCEDURE cleanTaskInstance
AS
BEGIN
	PRINT ' Start deleting task instance data with instance ids '; 
	BEGIN TRANSACTION;
	DELETE FROM HT_DEADLINE WHERE TASK_ID IN (SELECT ID FROM TEMP_CLEANUP);
	DELETE FROM HT_EVENT WHERE HT_EVENT.TASK_ID IN (SELECT ID FROM TEMP_CLEANUP);
	DELETE FROM HT_ORG_ENTITY WHERE ORG_ENTITY_ID IN ( SELECT ORGENTITY_ID FROM HT_HUMANROLE_ORGENTITY WHERE HUMANROLE_ID IN ( SELECT GHR_ID FROM HT_GENERIC_HUMAN_ROLE WHERE TASK_ID IN ( SELECT ID FROM TEMP_CLEANUP)));
	DELETE FROM HT_HUMANROLE_ORGENTITY WHERE HUMANROLE_ID IN ( SELECT GHR_ID FROM HT_GENERIC_HUMAN_ROLE WHERE TASK_ID IN ( SELECT ID FROM TEMP_CLEANUP));
	DELETE FROM HT_GENERIC_HUMAN_ROLE WHERE TASK_ID IN(SELECT ID FROM TEMP_CLEANUP);
	DELETE FROM HT_PRESENTATION_ELEMENT WHERE TASK_ID IN (SELECT ID FROM TEMP_CLEANUP);
	DELETE FROM HT_PRESENTATION_PARAM WHERE TASK_ID IN (SELECT ID FROM TEMP_CLEANUP);
	DELETE FROM HT_MESSAGE WHERE TASK_ID IN (SELECT ID FROM TEMP_CLEANUP);
	DELETE FROM HT_TASK_COMMENT WHERE TASK_ID IN(SELECT ID FROM TEMP_CLEANUP);
	DELETE FROM HT_TASK WHERE ID IN(SELECT ID FROM TEMP_CLEANUP);
	DELETE FROM ATTACHMENT WHERE ATTACHMENT_URL IN (SELECT ATTACHMENT_VALUE FROM HT_TASK_ATTACHMENT WHERE TASK_ID IN (SELECT ID FROM TEMP_CLEANUP));
	DELETE FROM HT_TASK_ATTACHMENT WHERE TASK_ID IN (SELECT ID FROM TEMP_CLEANUP);
	COMMIT;
	PRINT ' End deleting task instance data with instance ids ';
END
GO

BEGIN
  PRINT (' Starting cleanTaskInstance procedure ');
  EXEC cleanTaskInstance;
  PRINT (' Ending cleanTaskInstance procedure '); 
END
GO

DROP TABLE TEMP_CLEANUP;
GO

