How can you delete older archived folders data with OpenText MBPM?

You can use the following procedure to delete older archived folders data (These are only for MS SQL Server. Oracle users need to write a similar scripts with PL/SQL):

1. Delete_ProcessTable_Data

create Procedure [dbo].[Delete_ProcessTable_Data]
AS

DECLARE @P_tablename varchar(31)
DECLARE @p_sqlstring varchar(1000)

DECLARE db_cursor1 CURSOR FOR
SELECT distinct emapname
from efolder
where eArchived=1
and ecreationtime <= '2011-11-11 17:00:00.000'

OPEN db_cursor1
FETCH NEXT FROM db_cursor1 into @p_tablename

WHILE @@FETCH_STATUS = 0
BEGIN
set @p_sqlstring ='Delete from ' + @p_tablename + ' where efolderid in (select efolderid from efolder where earchived=1)'
EXEC (@p_sqlstring)
FETCH NEXT FROM db_cursor1 into @p_tablename
END
CLOSE db_cursor1
DEALLOCATE db_cursor1

2. Delete_ProcessTable_Data

create Procedure [dbo].[Delete_Archived_Folder_data]
AS
DECLARE @p_FolderId varchar(31)
DECLARE db_cursor CURSOR FOR
SELECT distinct efolderid
FROM efolder
where eArchived=1
and ecreationtime <= '2011-11-11 17:00:00.000'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @p_FolderID

WHILE @@FETCH_STATUS = 0
BEGIN

DELETE FROM eAttachment WHERE eKey LIKE N'0' + NCHAR(9) + @p_FolderID + NCHAR(9) + N'%'

DELETE FROM eAlertGeneratorLock WHERE eFolderID = @p_FolderID

DELETE FROM eDMSLink WHERE eFolderID = @p_FolderID

DELETE FROM eMSWorkflowEvent WHERE eFolderID = @p_FolderID

DELETE FROM eMSWorkflowTracking WHERE eFolderID = @p_FolderID

DELETE FROM eWait WHERE eFolderID = @p_FolderID

DELETE FROM eAlert WHERE eFolderID = @p_FolderID

DELETE FROM eAlertRequest WHERE eFolderID = @p_FolderID

DELETE FROM eEvent WHERE eFolderID = @p_FolderID

DELETE FROM eFolder WHERE eFolderID = @p_FolderID

DELETE FROM eWait WHERE eFolderID = @p_FolderID

DELETE FROM eLog WHERE eFolderID = @p_FolderID

FETCH NEXT FROM db_cursor INTO @p_FolderID
END

CLOSE db_cursor
DEALLOCATE db_cursor

3. Run the both stored procedures