Stopping Maximo workflow in bulk

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:

  • WFINSTANCE
  • WFASSIGNMENT
  • WFCALLSTACK
  • WFTRANSACTION

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.

Column Name Description
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.

18 thoughts on “Stopping Maximo workflow in bulk

  1. Hi Umar,

    I read the backend procedure to stop a workflow. I have a situation where I have some work orders assigned to a user. These work orders were assigned according to the workflow process. But now, in the existing workflow we removed two steps so from Node 1 the workflow jumps to Node 4. Is there a way I can do an escalation on the work orders so that the user on the workflow is the user in role 4 instead of the user in Role 1.
    I can provide more details if required. Could you please help me with this. Thanks in advance.

  2. Hi Kavita,

    I assume that you have created a new revision of workflow and you have some work orders which are in the middle of running the older revision of workflow.

    Each work order record will work on the workflow revision which was active when the workflow was started. There is no way to bring records from one workflow revision to another.

    If records are stuck in older version of workflow, one solution could be to stop the workflow and route it again. That way records will pick up the latest version of workflow.

    Hope this helps.

    • Thanks Umar.
      I am now trying to update the WFASSIGNMENT table using MEA.
      I am doing 2 imports one for updating the assignment table and the other for inserting into the assignment table.
      In the update file, I am updating the assignstatus field. I am providing the wfid, wfassignmentid, assignid and assignstatus to which it should be updated. I get an error “cannot update or delete” record does not exist. But I see the record through SQL server.
      In the insert file, I am giving all the field values, for roleid, it is a dataset type and i am providing the maxroleid value from the maxrole table. But, I get the roleid is not a valid role.
      So, these are the errors in each case. Can you help me fix these errors?

      Thanks
      Kavita

  3. Thanks Umar.
    I am now trying to update the WFASSIGNMENT table using MEA.
    I am doing 2 imports one for updating the assignment table and the other for inserting into the assignment table.
    In the update file, I am updating the assignstatus field. I am providing the wfid, wfassignmentid, assignid and assignstatus to which it should be updated. I get an error “cannot update or delete” record does not exist. But I see the record through SQL server.
    In the insert file, I am giving all the field values, for roleid, it is a dataset type and i am providing the maxroleid value from the maxrole table. But, I get the roleid is not a valid role.
    So, these are the errors in each case. Can you help me fix these errors?

    Thanks
    Kavita

  4. Hi Umar,

    I am trying to insert a record in WFASSIGNMENT table through MEA and I get an invalid data type error. It does not mention the column name. Can you help me fix this error. Please let me know if you want me to send the error file.

    Thanks
    Kavita

  5. Hi Syed,
    Is there is any way to complete maximo workflow using web service, as I don’t want to use Direct SQL statement on DB Server. and other then this if we complete workflow using Maximo then there is multiple option present like, Cancel, Reassign workflow, send mail like that, how we can implement that also in our custom application
    Thanks
    Siddhartha

  6. Hi Umar,

    Good post, but this explains how to stop the workflow for one workorder. I tried it and it works.

    But how do you stop the workflow for 100 workorders if you don’t want to make 400 sql statements ?

    Kind regards,

    Max

    • Hi Max,

      You can do it for more than one workorder by using PL/SQL cursor and loop (for Oracle database). If you are using some other database than Oracle, you can find very similar constructs there also like in TSQL for Microsoft Sql Server.

      Let me know if this helps or you need details.

  7. Hi

    i have workorders dropped from workflow but still cannot change status manually cause i applied somthing called action group to lock status changed if record in workflow ..the problem now i cannot unlock it even work order dropped out from workflow by script

    any advice

    • Hi Ammar,

      Now you have to unlock the workorders for status change i.e. OKSTATUS action has to be executed on these workorders. One way to do it is through an escalation.

      – First create an escalation,
      – add condition to the escalation such that it includes all affected workorders
      – add an OKSTATUS action in escalation
      – activate the escalation and it should fix the workorders for status change

  8. Syed,
    I work for IBM in Maximo support. There is now a class file you can use to stop WF on records in bulk. StopWorkflowAction.class.

    If you search the Maximo Technotes there is one that shows how to use this classfile to stop records in WF.

    cheers ………….dick

  9. Hi Umar
    Headline– Simplify workflow wofail

    Description–Currently maintenance planner has to accept CM types of work, work flow process requires one work flow “push” from maintenance planner before work order is sent to work flow. This functionality causes a lot of problems. Delete this step from work flow.

Leave a Reply

Your email address will not be published. Required fields are marked *