DROP TABLE TEMP_ACT_HI_PROCINST;
DROP TABLE TEMP_ACT_HI_TASKINST;
CREATE TABLE TEMP_ACT_HI_PROCINST AS SELECT * from ACT_HI_PROCINST  WHERE END_TIME_ is not NULL AND END_TIME_ < SYSTIMESTAMP AND ROWNUM <= 2147483647 order by ID_ asc; 
CREATE TABLE TEMP_ACT_HI_TASKINST AS ( SELECT ACT_HI_TASKINST.ID_ FROM ACT_HI_TASKINST INNER JOIN  TEMP_ACT_HI_PROCINST ON ACT_HI_TASKINST.PROC_INST_ID_ = TEMP_ACT_HI_PROCINST.PROC_INST_ID_);
set serveroutput on
CREATE OR REPLACE PROCEDURE cleanInstance
IS
BEGIN
	dbms_output.put_line (' Start deleting instance data with instance ids ');

        DELETE FROM ACT_HI_ACTINST WHERE (ACT_HI_ACTINST.PROC_INST_ID_) IN ( SELECT PROC_INST_ID_  FROM TEMP_ACT_HI_PROCINST);
        DELETE FROM ACT_HI_COMMENT WHERE (ACT_HI_COMMENT.TASK_ID_ ) IN ( SELECT ID_  FROM TEMP_ACT_HI_TASKINST);
        DELETE FROM ACT_HI_COMMENT WHERE (ACT_HI_COMMENT.PROC_INST_ID_ ) IN ( SELECT PROC_INST_ID_  FROM TEMP_ACT_HI_PROCINST);
        DELETE FROM ACT_HI_IDENTITYLINK WHERE (ACT_HI_IDENTITYLINK.TASK_ID_) IN ( SELECT ID_ FROM TEMP_ACT_HI_TASKINST);
        DELETE FROM ACT_HI_IDENTITYLINK WHERE (ACT_HI_IDENTITYLINK.PROC_INST_ID_) IN ( SELECT PROC_INST_ID_ FROM TEMP_ACT_HI_PROCINST);
        DELETE FROM ACT_HI_VARINST WHERE (ACT_HI_VARINST.TASK_ID_) IN ( SELECT ID_ FROM TEMP_ACT_HI_TASKINST);
        DELETE FROM ACT_HI_VARINST WHERE (ACT_HI_VARINST.PROC_INST_ID_) IN ( SELECT PROC_INST_ID_ FROM TEMP_ACT_HI_PROCINST);
        DELETE FROM ACT_HI_ATTACHMENT WHERE (ACT_HI_ATTACHMENT.TASK_ID_) IN ( SELECT ID_ FROM TEMP_ACT_HI_TASKINST);
        DELETE FROM ACT_HI_ATTACHMENT WHERE (ACT_HI_ATTACHMENT.PROC_INST_ID_) IN ( SELECT PROC_INST_ID_ FROM TEMP_ACT_HI_TASKINST);

      
        DELETE FROM ACT_HI_TASKINST WHERE (ACT_HI_TASKINST.ID_) IN ( SELECT ID_ FROM TEMP_ACT_HI_TASKINST);
        DELETE FROM ACT_HI_PROCINST WHERE (ACT_HI_PROCINST.PROC_INST_ID_) IN ( SELECT PROC_INST_ID_ FROM TEMP_ACT_HI_PROCINST);

	COMMIT;
	dbms_output.put_line (' End deleting instance data with instance ids ');
END;
/

SET AUTOCOMMIT OFF;
BEGIN
  dbms_output.put_line (' Starting cleanInstance procedure ');
  cleanInstance();
  dbms_output.put_line (' Ending cleanInstance procedure '); 
END;
/
SET AUTOCOMMIT ON;

/

