text: -- WSO2 BPS Oracle Clean up Script V3
  --
  -- Important : Before you run this script, configure instance states and Data retention time period in STMT2 (line 44).
  --
  SET AUTOCOMMIT OFF;
  SET SERVEROUTPUT ON
  CREATE OR REPLACE
  PROCEDURE CLEANINSTANCE AUTHID CURRENT_USER
  IS
    STMT1  VARCHAR2(2048);
    STMT2  VARCHAR2(2048);
    STMT3  VARCHAR2(2048);
    STMT4  VARCHAR2(2048);
    STMT5  VARCHAR2(2048);
    STMT6  VARCHAR2(2048);
    STMT7  VARCHAR2(2048);
    STMT8  VARCHAR2(2048);
    STMT9  VARCHAR2(2048);
    STMT10 VARCHAR2(2048);
    STMT11 VARCHAR2(2048);
    STMT12 VARCHAR2(2048);
    STMT13 VARCHAR2(2048);
    STMT14 VARCHAR2(2048);
    STMT15 VARCHAR2(2048);
    STMT16 VARCHAR2(2048);
    STMT17 VARCHAR2(2048);
  BEGIN
    DBMS_OUTPUT.PUT_LINE (' Start deleting instance data ');
    STMT1 := 'CREATE TABLE TEMP_CLEANUP(ID NUMBER)';
    EXECUTE IMMEDIATE STMT1;
    --
    -- stmt2 : Configure Followings before executing this script.
    --
    --      * Instance states : List of instance states, which need to be cleaned from the database. Followings are the instance states in BPS engine.
    --                           20  - Active.
    --                           30  - Completed.
    --                           40  - Completed with Fault.
    --                           50  - Suspended.
    --                           60  - Terminated.
    --      * Last Active Time : Last active time of the instances, which need to be cleaned from the database. .
    --                            Eg: (SYSTIMESTAMP - 1) will filter instances which are older than 1 day.
    --                            Eg: (SYSTIMESTAMP - 7) will filter instances which are older than 7 days.
    --
    STMT2 := 'INSERT INTO TEMP_CLEANUP(ID) SELECT ID FROM ODE_PROCESS_INSTANCE WHERE INSTANCE_STATE IN (30 , 40 , 60) AND LAST_ACTIVE_TIME < (SYSTIMESTAMP - 1)';
    EXECUTE IMMEDIATE STMT2;
    ---
    ----------------------------ODE_XML_DATA_LOBS---------------------------------------------------
    DBMS_OUTPUT.PUT_LINE (' Start cleanup of ODE_XML_DATA BLOBS ');
    EXECUTE IMMEDIATE 'ALTER TABLE "ODE_XML_DATA" ENABLE ROW MOVEMENT';
    DBMS_OUTPUT.PUT_LINE (' Set ODE_XML_DATA LOBs to null ');
    STMT3 := 'UPDATE ODE_XML_DATA SET ODE_XML_DATA.DATA = NULL WHERE SCOPE_ID IN (SELECT os.SCOPE_ID FROM ODE_SCOPE os WHERE os.PROCESS_INSTANCE_ID IN (SELECT ID FROM TEMP_CLEANUP))';
    EXECUTE IMMEDIATE STMT3;
    DBMS_OUTPUT.PUT_LINE (' Deleting from ODE_XML_DATA ');
    STMT4 := '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))';
    EXECUTE IMMEDIATE STMT4;
    DBMS_OUTPUT.PUT_LINE (' Shrinking ODE_XML_DATA table');
    EXECUTE IMMEDIATE 'ALTER TABLE "ODE_XML_DATA" SHRINK SPACE CASCADE';
    --------------------------------------ODE_MESSAGE_LOBS---------------------------------------------------
    DBMS_OUTPUT.PUT_LINE (' Start cleanup of ODE_MESSAGE BLOBS ');
    EXECUTE IMMEDIATE 'ALTER TABLE "ODE_MESSAGE" ENABLE ROW MOVEMENT';
      DBMS_OUTPUT.PUT_LINE (' Set ODE_MESSAGE LOBs to null ');
    STMT5 := 'UPDATE ODE_MESSAGE SET ODE_MESSAGE.DATA = NULL, ODE_MESSAGE.HEADER = NULL 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))';
    EXECUTE IMMEDIATE STMT5;
    DBMS_OUTPUT.PUT_LINE (' Deleting from ODE_MESSAGE ');
    STMT6 := '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))';
    EXECUTE IMMEDIATE STMT6;
    DBMS_OUTPUT.PUT_LINE (' Shrinking ODE_MESSAGE table');
    EXECUTE IMMEDIATE 'ALTER TABLE "ODE_MESSAGE" SHRINK SPACE CASCADE';
    ----------------------------------------------------ODE Events---------------------------------
    EXECUTE IMMEDIATE 'ALTER TABLE "ODE_EVENT" ENABLE ROW MOVEMENT';
    STMT7 :='DELETE FROM ODE_EVENT WHERE INSTANCE_ID IN (SELECT ID FROM TEMP_CLEANUP)';
    EXECUTE IMMEDIATE 'ALTER TABLE "ODE_EVENT" SHRINK SPACE CASCADE';
    ----------------------------------------------------OTHER---------------------------------
    EXECUTE IMMEDIATE STMT7;
    STMT8 :='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)))';
    EXECUTE IMMEDIATE STMT8;
    STMT9 :='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))';
    EXECUTE IMMEDIATE STMT9;
    STMT10 := '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))';
    EXECUTE IMMEDIATE STMT10;
    STMT11 :='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)))';
    EXECUTE IMMEDIATE STMT11;
    STMT12 := 'DELETE FROM ODE_SCOPE WHERE PROCESS_INSTANCE_ID IN (SELECT ID FROM TEMP_CLEANUP)';
    EXECUTE IMMEDIATE STMT12;
    STMT13 := '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))';
    EXECUTE IMMEDIATE STMT13;
    STMT14 := 'DELETE FROM ODE_MESSAGE_EXCHANGE WHERE PROCESS_INSTANCE_ID IN (SELECT ID FROM TEMP_CLEANUP)';
    EXECUTE IMMEDIATE STMT14;
    STMT15 := 'DELETE FROM ODE_MESSAGE_ROUTE WHERE PROCESS_INSTANCE_ID IN (SELECT ID FROM TEMP_CLEANUP)';
    EXECUTE IMMEDIATE STMT15;
    STMT16 := 'DELETE FROM ODE_PROCESS_INSTANCE WHERE ID IN (SELECT ID FROM TEMP_CLEANUP)';
    EXECUTE IMMEDIATE STMT16;
    DBMS_OUTPUT.PUT_LINE (' End deleting instance data ');
    STMT17 := 'DROP TABLE TEMP_CLEANUP';
    EXECUTE IMMEDIATE STMT17;
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE (' Triggered Exception sequence. ');
      STMT17 := 'DROP TABLE TEMP_CLEANUP';
      EXECUTE IMMEDIATE STMT17;
      COMMIT;
  END;
  /
  SET AUTOCOMMIT OFF;
  BEGIN
    DBMS_OUTPUT.PUT_LINE (' Starting cleanInstance procedure');
    CLEANINSTANCE();
    DBMS_OUTPUT.PUT_LINE (' Ending cleanInstance procedure');
  END;
  /
  SET AUTOCOMMIT ON;
  
  
  SET AUTOCOMMIT OFF;
  CREATE TABLE TEMP_CLEANUP AS SELECT ID FROM HT_TASK WHERE STATUS ='COMPLETED';
  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;