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 @ID INT;

-- 	Set ID to be removed
-- 	Ex:
--	SET @ID=11;

SET @TID = 0;

DECLARE @I_STATE INT;
SET @I_STATE = 30;

SELECT ID INTO TEMP_CLEANUP FROM ODE_PROCESS_INSTANCE WHERE INSTANCE_STATE = @I_STATE AND ID = @TID;
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
