Recently I came across a situation where I had to stop the workflow for a large number of records as workflow took a wrong route due to some configuration issue. It happened on work orders which were auto-generated from PMs (Preventive Maintenance). There was a problem in workflow configuration due to a recent modification and it resulted in hundreds of work orders routed to wrong persons.
In Maximo workflow administration application, we can stop workflow but it has to be done one by one. There is no way to do it for a list of records in one go. So I wrote a SQL script to make the relevant updates directly in database to stop the workflow on desired records. Following passages explain how this can be achieved.
There are four tables involved in stopping a workflow which are:
Whenever workflow is started on an object, a new workflow instance is created. In other words, a record is created in WFINSTANCE table. WFID field is the key field to identify a workflow instance.
The workflow instance stores the information about the object on which it is running using two fields. OWNERTABLE containts the object name like WORKORDER or SR and OWNERID contains the object ID which is the WOID in case of work orders.
First thing we need to do is to find the WFID for the workflow instance we want to stop. For finding a workflow instance for work order, we could use a SQL like this.
SELECT * FROM WFINSTANCE WHERE OWNERTABLE = 'WORKORDER' and OWNERID = <WFID>
You should replace with the WOID of your work order. The query should return one row, take note of the WFID. It is the key field which will be used to update the four tables mentioned above.
Once we know the WFID, we will run the following updates to deactivate the workflow.
UPDATE MAXIMO.WFINSTANCE SET ACTIVE = 0 WHERE WFID = <WFID> UPDATE MAXIMO.WFASSIGNMENT SET ASSIGNSTATUS = 'INACTIVE' WHERE WFID = <WFID> UPDATE MAXIMO.WFCALLSTACK SET ACTIVE = 0 WHERE WFID = <WFID>
Next we have to insert a row in WFTRANSACTION table. This table stores the history of workflow instance in terms of all the nodes and actions it went through. We will have to insert a row in this table to record the workflow stop transaction. Following table describes the fields of WFTRANSACTION table which we will help us in building our insert statement.
|TRANSID||A sequence field. To get the value for this field use wftransactionseq.nextval|
|NODEID||It is the Node ID of stop node in your workflow|
|WFID||ID of the workflow instance|
|TRANSTYPE||As we want to stop the workflow, transaction type would be ‘WFUSERSTOPPED’|
|TRANSDATE||We can put current date here using sysdate|
|NODETYPE||It should be ‘TASK’ if the workflow is currently on a task node. It could also be ‘WAIT’|
|PROCESSREV||Revision number of the process which is currently running on the object|
|PROCESSNAME||Name of the workflow process|
|PERSONID||Person who is performing this workflow step which is stopping the workflow in this case.|
|OWNERTABLE||Object name on which the workflow is running|
|OWNERID||Object ID on which the workflow is running. For instance, WOID in case of work order.|
Following is a sample insert SQL for WFTRANSACTION table.
Insert into MAXIMO.WFTRANSACTION (TRANSID, NODEID, WFID, TRANSTYPE, TRANSDATE, NODETYPE, PROCESSREV, PROCESSNAME, PERSONID, OWNERTABLE, OWNERID ) (select maximo.wftransactionseq.nextval, <NODEID>, WFID, 'WFUSERSTOPPED', sysdate, 'TASK', <PROCESSREV>, <PROCESSNAME>, 'MAXADMIN', <OWNERTABLE>, <OWNERID> from maximo.wfinstance where wfid = <WFID> )
That’s all we have to do to stop the workflow in Maximo through backend.