--
-- Important : Before you run this script, configure TASK ID (ex: ID = 0) in line 7.
--

SET AUTOCOMMIT OFF;

CREATE TABLE TEMP_CLEANUP AS SELECT ID FROM HT_TASK WHERE STATUS ='COMPLETED' AND ID = 0;
set serveroutput on
CREATE OR REPLACE PROCEDURE cleanTaskInstance
IS
BEGIN
	dbms_output.put_line (' Start deleting task instance data with instance ids ');
	DELETE FROM ODE_EVENT WHERE INSTANCE_ID IN (SELECT ID FROM TEMP_CLEANUP);
  	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);
	DELETE FROM TEMP_CLEANUP;
	COMMIT;
	dbms_output.put_line (' End deleting task instance data with instance ids ');
END;
/

SET AUTOCOMMIT OFF;
BEGIN
  dbms_output.put_line (' Starting cleanTaskInstance procedure ');
  cleanTaskInstance();
  dbms_output.put_line (' Ending cleanTaskInstance procedure ');
END;
/
SET AUTOCOMMIT ON;
/
DROP TABLE TEMP_CLEANUP;
