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.
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.
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
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
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
Hi Kavita,
Please inbox me the error file. I will see if I can help…..
–Umar
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
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.
Thank you umar, this sure works for me.
And yes indeed we work with oracle db….
Kind regards,
Max
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
Hi Umar
i have workflow but still cannot change status manually in maximo app
Hi Surya,
Please provide more details like error message you are getting… etc.
this i saw your linked in prol your working EAM oil ans gas can you help me please share any kind of doc about oil and gas right now im working IBM india this is my id poll.suri8888@gmail.com
thank you very much
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
Thanks Dick for this info.
Found the following link on IBM site which has the details on how to do this using StopWorkflowAction.class.
http://www-01.ibm.com/support/docview.wss?uid=swg21593035
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.
Hi Umar,
Is there any way to reassign multiple assignments to other person using escalation ? Is there any way to start multiple records on workflow using escalation ?
Hello Gents,
Is there a way to bulk start workflow using SQL script?
Regrds