Stopping Maximo Workflow using PL/SQL

IBM Maximo Asset Management is an enterprise and comprehensive solution for managing ITIL oriented services. IBM Maximo asset management includes the asset, work, service, contract, inventory and procurment management modules as a service oriented architecture. [1] For more official information about IBM Maximo Asset Management please visit the following link; IBM Maximo Asset Management

It’s not best practice to insert or modify any data from directly database. However, sometimes maximo admins may need to insert or modify bulk data directly from database. For example;  with a new untested IBM Netcool integration thousands of IT alarms could be generated. In this case, closing or cancelling these work orders from application would become a painful situation for responsible staff.

Stopping maximo workflow using PL/SQL is possible with the following script. Maximo database keeps the work flow any application on the following tables; wfinstance, wfassignment, wfcallstack and wftransaction. By modifying these tables we can simulate the close or cancel process of a workorder process.

Stopping maximo workflow using PL/SQL;

First of all, primary keys of workorders (wonum) should be imported to a temporary table such as BULK_TABLE. We will iterate these table row by row using a PL/SQL loop.

First control is whether the current wonum exists or not. If the current wonum exists; with the second loop we will find any active workflow(s) ( somehow current record may have multiple workflows ). Inside of the second loop; we will modify tables related with work flow by unique identifier of workflow WFID. After that, we are inserting a row to wostatus table to keep the last status change of record. Lastly we are inserting a row to wftransaction table that keeps the transaction records of work flow. Please note that; nodeid, processname and processrev may change depending on current implementation of application process.

 

DECLARE
    
	V_WORKORDERID MAXIMO.WORKORDER.WORKORDERID%TYPE;
	
	V_RECORD_EXISTS NUMBER := 0;
	
	V_PARENT MAXIMO.WORKORDER.PARENT%TYPE;

BEGIN

	-- LET'S ITERATE OVER A BULK TABLE THAT HAVE WONUMS
	FOR C IN ( SELECT WONUM FROM MAXIMO.BULK_TABLE ) LOOP
	
		V_RECORD_EXISTS := 0;
		
		SELECT COUNT(*) INTO V_RECORD_EXISTS FROM WORKORDER WHERE WONUM = C.WONUM;
		
		-- WE ARE SURE CURRENT WONUM EXISTS
		IF ( V_RECORD_EXISTS <> 0 ) THEN
			
			SELECT WORKORDERID INTO V_WORKORDERID FROM WORKORDER WHERE WONUM = C.WONUM;
		
			-- CURRENT WORKORDER MAY HAVE MULTIPLE ACTIVE WORK FLOWS; WE SHOULD STOP THEM ALL
			FOR C1 IN ( SELECT WFID FROM WFINSTANCE WHERE OWNERTABLE = 'WORKORDER' AND OWNERID = V_WORKORDERID AND ACTIVE = 1 ) LOOP
			
				BEGIN
				
					UPDATE WFINSTANCE SET ACTIVE = 0 WHERE WFID = C1.WFID;
				
					UPDATE WFCALLSTACK SET ACTIVE = 0 WHERE WFID = C1.WFID;
													
					UPDATE WFASSIGNMENT SET ASSIGNSTATUS = 'INACTIVE' WHERE WFID = C1.WFID AND ASSIGNSTATUS = 'ACTIVE';
								
					UPDATE WORKORDER SET STATUS = 'CANCEL', HISTORYFLAG = 1 WHERE WORKORDERID = V_WORKORDERID;
					
					SELECT PARENT INTO V_PARENT FROM WORKORDER WHERE WORKORDERID = V_WORKORDERID;
						   
					INSERT INTO WOSTATUS
					(WONUM, STATUS, CHANGEDATE, CHANGEBY, ORGID, SITEID, WOSTATUSID, PARENT)
					VALUES
					(C.WONUM, 'CANCEL', SYSDATE, 'MAXADMIN', 'COMPANY', 'SITE', WOSTATUSSEQ.NEXTVAL, V_PARENT);
					
					-- NODEID, PROCESSNAME AND PROCESS REVISION MAY CHANGE DEPENDING BY IMPLEMENTATION
					INSERT INTO WFTRANSACTION
					(TRANSID, NODEID, WFID, TRANSTYPE, TRANSDATE, MEMO, NODETYPE, PROCESSREV, PROCESSNAME, PERSONID, OWNERTABLE, OWNERID)
					VALUES
					(WFTRANSACTIONSEQ.NEXTVAL, 100, C1.WFID, 'WFSTOP', SYSDATE, 'MANUAL STOP', 'STOP', 1, 'WOPROCESS', 'MAXADMIN', 'WORKORDER', V_WORKORDERID);
						
					COMMIT;
					
				END;
			
			END LOOP;
				
		ELSE
		
			DBMS_OUTPUT.PUT_LINE('CURRENT WONUM : '|| C.WONUM ||' DOES NOT EXISTS AT WORKORDER TABLE');
			
		END IF;
		
	END LOOP;
	
END;
/
		
			

Reference(s)

1. “IBM Maximo Asset Management“, IBM USA, Retrieved on 17-05-2015 from http://www-03.ibm.com/software/products/en/maximoassetmanagement

 

 

Leave a Reply

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