text: CREATE OR REPLACE FUNCTION cleanInstances(bpel_inst_state INT, lastActive timestamp)
  RETURNS void AS $$
  BEGIN
  
  	CREATE TABLE TEMP_CLEAN_INS AS SELECT ID FROM ODE_PROCESS_INSTANCE WHERE INSTANCE_STATE = bpel_inst_state AND LAST_ACTIVE_TIME < lastActive;
  	CREATE TABLE TEMP_CLEAN_SCOPE AS SELECT os.SCOPE_ID FROM ODE_SCOPE os INNER JOIN TEMP_CLEAN_INS tc ON os.PROCESS_INSTANCE_ID = tc.ID;
  	CREATE TABLE TEMP_CLEAN_MEX AS SELECT mex.MESSAGE_EXCHANGE_ID FROM ODE_MESSAGE_EXCHANGE mex INNER JOIN TEMP_CLEAN_INS tc ON mex.PROCESS_INSTANCE_ID = tc.ID;
  	DELETE FROM ODE_EVENT AS o USING TEMP_CLEAN_INS AS tc WHERE o.INSTANCE_ID = tc.ID;
  	DELETE FROM ODE_CORSET_PROP WHERE CORRSET_ID IN ( SELECT cs.CORRELATION_SET_ID FROM ODE_CORRELATION_SET cs INNER JOIN TEMP_CLEAN_SCOPE ts ON cs.SCOPE_ID = ts.SCOPE_ID);
  	DELETE FROM ODE_CORRELATION_SET AS cs USING TEMP_CLEAN_SCOPE ts WHERE cs.SCOPE_ID = ts.SCOPE_ID;
  	DELETE FROM ODE_PARTNER_LINK AS cs USING TEMP_CLEAN_SCOPE ts WHERE cs.SCOPE_ID = ts.SCOPE_ID;
  	DELETE FROM ODE_XML_DATA_PROP WHERE XML_DATA_ID IN ( SELECT xd.XML_DATA_ID FROM ODE_XML_DATA xd INNER JOIN TEMP_CLEAN_SCOPE ts ON xd.SCOPE_ID = ts.SCOPE_ID);
  	DELETE FROM ODE_XML_DATA AS cs USING TEMP_CLEAN_SCOPE ts WHERE cs.SCOPE_ID = ts.SCOPE_ID;
  	DELETE FROM ODE_SCOPE AS o USING TEMP_CLEAN_INS tc WHERE o.PROCESS_INSTANCE_ID = tc.ID;
  	DELETE FROM ODE_MEX_PROP AS om USING TEMP_CLEAN_MEX tc WHERE om.MEX_ID= tc.MESSAGE_EXCHANGE_ID;
  	DELETE FROM ODE_MESSAGE AS om USING TEMP_CLEAN_MEX tc WHERE om.MESSAGE_EXCHANGE_ID = tc.MESSAGE_EXCHANGE_ID;
  	DELETE FROM ODE_MESSAGE_EXCHANGE AS mex USING TEMP_CLEAN_INS tc WHERE mex.PROCESS_INSTANCE_ID = tc.ID;
  	DELETE FROM ODE_MESSAGE_ROUTE AS mr USING TEMP_CLEAN_INS tc WHERE mr.PROCESS_INSTANCE_ID = tc.ID;
  	DELETE FROM ODE_PROCESS_INSTANCE AS o USING TEMP_CLEAN_INS tc WHERE o.ID = tc.ID;
  	DROP TABLE TEMP_CLEAN_INS;
  	DROP TABLE TEMP_CLEAN_SCOPE;
  	DROP TABLE TEMP_CLEAN_MEX;
  
  END;
  $$
  LANGUAGE plpgsql;
  
  CREATE OR REPLACE FUNCTION cleanTaskInstances()
  RETURNS void AS $$
  BEGIN
  	CREATE TABLE TEMP_CLEANUP AS SELECT ID FROM HT_TASK WHERE STATUS ='COMPLETED';
  	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);
  	DROP TABLE TEMP_CLEANUP;
  END;
  $$
  LANGUAGE plpgsql;
  
  BEGIN;
  --deletes bpel instances
  --default instance state to delete - 30
  --time interval to start deletion - now()
  SELECT cleanInstances(30,localtimestamp);
  COMMIT;
  
  BEGIN;
  --deletes completed humanTask instances
  SELECT cleanTaskInstances();
  COMMIT;