Michael_Lowry

Automatically cancel old tasks in status 'Waiting for user input'

Discussion created by Michael_Lowry on Nov 1, 2016
We have noticed that some users start a task with a prompt set, but then never submit the prompt set. These tasks then accumulate in the Activity window.

To resolve this problem, I made an SQL (Oracle) job to list tasks in the status Waiting for user input that were started more than a certain number of days ago.
select EH_Name, EH_AH_IDnr, EH_Usr_Idnr, USR_FirstName, USR_LastName, USR_EMail1, OH_Name, EH_RDate
from EH left join USR
on EH_Usr_Idnr = USR_OH_Idnr
left join OH
on USR_OH_Idnr = OH_Idnr
where 1=1
and EH_Client = &$CLIENT#
and EH_OType  = '&CHECK_TYPE#'
and EH_Status = &CHECK_STATUS#
and EH_Name   = '&CHECK_NAME#'
and EH_RDate <= (current_date - &CHECK_PERIOD#)
I put this SQL job in a workflow and set the following variables at the level of the workflow.

Variable name
Description
Example
&CHECK_NAME#Object name of tasks to cancel
ABC.MYWORKFLOW.JOBP
&CHECK_PERIOD#Minimum age in days of tasks to cancel
1
&CHECK_STATUS#Status of tasks to cancel
1301
&CHECK_TYPE#Object type of tasks to cancel
JOBP

In a subsequent SCRI in the same workflow, I added some commands to parse the SQL output and cancel the tasks returned by the query.
:SET &prevId# = SYS_ACT_PREV_NR()
:SET &TaskCounter# = 0
:SET &TaskSearchResults# = PREP_PROCESS_REPORT(,&prevId# , "REP", "&CHECK_NAME#*", "COL=DELIMITER", "DELIMITER=@;

)
:PROCESS &TaskSearchResults#
:  SET &TaskCounter#  = &TaskCounter# + 1
:  SET &TaskCounter#  = FORMAT(&TaskCounter#)
:  SET &RunID#         = GET_PROCESS_LINE(&TaskSearchResults#, 2)
:  SET &UserFirstName# = GET_PROCESS_LINE(&TaskSearchResults#, 4)
:  SET &UserLasstName# = GET_PROCESS_LINE(&TaskSearchResults#, 5)
:  SET &UserEmail#     = GET_PROCESS_LINE(&TaskSearchResults#, 6)
:  SET &UserID#        = GET_PROCESS_LINE(&TaskSearchResults#, 7)
:  SET &StartDate#     = GET_PROCESS_LINE(&TaskSearchResults#, 8)
:  PRINT

Task #&TaskCounter#:
&CHECK_NAME# (&RunID#)"
:  PRINT "Start date     : &StartDate#"
:  PRINT "Executing user : &UserFirstName# &UserLasstName# (&UserID#) <&UserEmail#>"
:  PRINT "Cancelling task..."
:  SET &RC# = CANCEL_UC_OBJECT(&RunID#)
:  INCLUDE UC4.HANDLE_RC.CANCEL_UC_OBJECT.JOBI
:  PRINT
:ENDPROCESS
:CLOSE_PROCESS &TaskSearchResults#
:IF &TaskCounter# > 0
:  PRINT "Processed &TaskCounter# tasks."
:ELSE
:  PRINT "No tasks found matching search criteria."
:ENDIF

The JOBI UC4.HANDLE_RC.CANCEL_UC_OBJECT.JOBI is simple; I created it just so that I can reuse this bit of code wherever I use CANCEL_UC_OBJECT.
:SWITCH &RC#
:  CASE 0
:    PRINT "Task cancelled successfully."
:  CASE 11049
:    PRINT "Task not found."
:  CASE 11050
:    PRINT "Task cannot be cancelled."
:  CASE 20347
:    PRINT "It is not possible to cancel a script with CANCEL_UC_OBJECT when it was started via a CallAPI."
:  OTHER
:    PRINT "Unhandled return code: &RC#"
:ENDSWITCH
Enjoy!

Outcomes