How to quickly bulk archive pending processes prior to upgrading to the latest version of XMPro?

Question:

How to quickly bulk archive pending processes prior to upgrading to latest version of XMPro, especially if the pending processes are not written in Managed Code?

Solution:

As XMPro 6.7 no longer support VBScript codes, processes that are using VBScript must be rewritten in Managed Codes if the site is to be upgraded. Because of this, these pending processes won’t be able to be accessed via Action Hub upon upgrading to complete the processes. Prior to upgrading the XMPro database, the pending processes either need to be completed by the people owning the tasks or be archived manually. In most cases, the pending processes are idle processes that just need to be archived. This is due to people leaving the organization and the processes became orphaned, or the processes were left off at the acknowledgment step and people just did not complete this step. The quickest way to deal with these after reviewing that the processes are good candidates for archiving is to do bulk archiving via the SQL scripts presented in this article prior to upgrading the XMPro database using the Site Manager tool.

Steps to follow:

1. Retrieve and count the list of pending processes.
2. List and review the pending processes and their owners. Upon reviewing, either ask the owners to complete the pending processes or make sure the processes are good for manual archiving.
3. Take a back up of the XMPro database prior to bulk archiving the pending processes.
4. Bulk archive all the remaining pending processes.

SQL Scripts for each step

Run the SQL Scripts against the XMPro database.

1. Retrieve and count the list of pending processes.

--- List of total pending processes by Event Description

select p.EventID, p.EventCode, p.EventDescription, COUNT(p.ProcessID) TotalPendingProcesses from
(
       select  h.ProcessID, h.EventID, e.EventCode, e.Description EventDescription,       
            h.[Description], h.ResultUser, u.FirstName + ' ' + u.LastName PendingWithUser
       from fcEventHist h
       inner join fcUser u on (h.ResultUser = u.ID)
       inner join fcEvent e on (h.EventID = e.ID)
       where h.ResultDate = '0' and h.ResultTime = '0'       
) p
group by p.EventID, p.EventCode, p.EventDescription
order by TotalPendingProcesses desc

2. List and review pending processes and their owners.

--- List of all pending processes and the owners

select  h.ProcessID, h.EventID, e.EventCode, h.[Description], h.ResultUser, 
u.FirstName + ' ' + u.LastName PendingWithUser, h.CreateDate

from fcEventHist h
inner join fcUser u on (h.ResultUser = u.ID)
inner join fcEvent e on (h.EventID = e.ID)
where h.ResultDate = '0' and h.ResultTime = '0'
order by e.EventCode, h.ProcessID

3. Take a back up of the XMPro database prior to bulk archiving pending processes.

4. Bulk archive all the remaining pending processes.

A. Create store procedure wfi_CORE_SET_WritePendingActivityList in the XMPro database.

--- Archive a process

CREATE PROCEDURE [dbo].[wfi_CORE_SET_WritePendingActivityList]

       @dbName varchar(50),
       @ProcessID varchar(10),
       @writeDate varchar(14)

AS
BEGIN
DECLARE @commandText as varchar(4000)
SET @commandText = 'INSERT INTO ' + @dbName + '.dbo.fcEventValueArchive
(EventHistID, ControlID, ControlValue, Options)
SELECT fcEV.EventHistID, fcEV.ControlID,
fcEV.ControlValue, fcEV.Options
FROM ' + @dbName + '.dbo.fcEventHist fcEH INNER JOIN
' + @dbName + '.dbo.fcEventValue fcEV ON fcEH.ID = fcEV.EventHistID
WHERE (fcEH.ProcessID = ' + @ProcessID + ')

DELETE FROM ' + @dbName + '.dbo.fcEventValue
WHERE (EventHistID IN
(SELECT ID
FROM ' + @dbName + '.dbo.fcEventHist
WHERE ProcessID = ' + @ProcessID + '))

INSERT INTO ' + @dbName + '.dbo.fcEventArchive
(EventHistID, ProcessID, BaseID, EventID, CreateID, ResultID,
CreateDate, CreateTime, ResultDate, ResultTime, Escalated,
Assigned, WarnTrigger, WarnAmount, CreateUser, ResultUser, Description)
SELECT ID, ProcessID, BaseID, EventID, CreateID, ResultID,
CreateDate, CreateTime, ResultDate, ResultTime,
Escalated, Assigned, WarnTrigger, WarnAmount, CreateUser,
ResultUser, Description
FROM ' + @dbName + '.dbo.fcEventHist
WHERE ProcessID = ' + @ProcessID  + '

DELETE FROM ' + @dbName + '.dbo.fcEventHist
WHERE ProcessID = ' + @ProcessID + 'UPDATE ' + @dbName + '.dbo.fcEventArchive
SET ResultDate = ' + @writeDate + ', ResultTime = ''000000'' WHERE (ResultDate = 0) AND (ResultTime = 0)
UPDATE  ' + @dbName + '.dbo.fcEventArchive SET Assigned = ''Assigned'' Where Assigned = ''Unassigned'' '
exec(@commandText)

END

B. Execute the scripts below to bulk archive all pending processes. If you wish to only filter certain processes to archive, please uncomment necessarily on the filter sections.

--- Manual archive pending processes

begin transaction


declare @field1 numeric(18,0)
declare cur CURSOR LOCAL for

select ProcessID as field1
from XMPRO.dbo.fcEventHist
where ResultDate = '0'
and ResultTime = '0'

/***
and EventID in
(

     select id from XMPRO.dbo.fcEvent
     where EventCode in ('ECN010Create') 
     /* 1) Optional: To filter by Event Code, please uncomment */

)
***/
      
/***
and ResultID =
(
     select r.ID RoleID from XMPRO.dbo.fcRole r
     inner join XMPRO.dbo.fcUser u on (r.ID = u.RoleID)
     where u.UserName = 'bernard.smith' 
     /* 2) Optional: To filter by XMPro Username, please uncomment */
)
***/
  
/***
     and ProcessID between '1673' and '4839'    
     /* 3) Optional: To filter by Process ID ranges, please uncomment */

***/
    
order by ProcessID

open cur

fetch next from cur into @field1

while @@FETCH_STATUS = 0 BEGIN

    exec XMPRO.dbo.wfi_CORE_SET_WritePendingActivityList 'XMPRO', @field1, '20191211'     
    /* 4) Replace the date with today's date in format of yyyyMMdd as the archived date */    
    
    fetch next from cur into @field1

END

close cur
deallocate cur

rollback transaction