Introduction

Oracle introduced Workflows in to APEX in release 23.2 and I've been tinkering with them ever since. In this post I'll explain how I deal with terminated workflows. This is hopefully only going to be a temporary workaround! I haven't seen the Workflow future development roadmap, but I hope that there is something in there to handle terminations properly in the future.

The problem

The new Workflow features in APEX, make developing a process quite straight forward and allow for a logical flow from one activity to the next, calling pl/sql APIs, Human Tasks etc. What I've found though is that the Terminate workflow feature is nearly useless, let's say we have a simple process where we start a workflow process to walk through several activities, each activity may do different things or modify the status or an object etc. If a user, either the initiator or the workflow administrator decides the workflow process is no longer required there is the option to Terminate the workflow. This looks good on the surface, you click the button the workflow now shows as terminated. What it doesn't do is provide a facility to roll back any changes or even cancel Human Tasks. That all needs to be done manually. There isn't even a hook where you could put some code to programmatically reset the data or cancel the tasks when the terminate button is pressed.

Initial thoughts on solutions

You could add in an extra page process on the Workflow page to handle it, but the Workflow Console page, by default, is set to show all workflows in the workspace not just in the application, so you would have to add page processes in all apps possibly.

Hide the terminate button, works in some cases, force users to get to the end of a workflow and cover it in the final activity. Would be perfect if you can workout multiple endings and activities and branches to achieve them, but perhaps your workflow is a credit application and you want to allow the applicant to cancel at any time, you end up writing code to handle the rollback and deletion of data etc, cancel the human tasks and terminate the workflow.

Don't insert or update anything in a workflow until it completes, use workflow parameters to store future state etc. Very simple processes might be ok this way. Very limiting though and the workflow definition itself can get cumbersome.

In the end I went with an Automation.....

Setting the scene

For the purposes of this post, I'll be using a simple workflow process that will initially change the status column in a database row when the workflow starts to show that workflow is in use, it then calls a Human Task, once the Human task is complete it updates the status column again.

This is very simplistic but it will show how we can use an Automation based on the termination of the workflow to revert the status column and cancel the Human Task.

Task Definition

I'm using the task and workflow I setup in an earlier blog post about APEX workflow, available here, the following task definition was setup for that.

There was a single parameter used to store some captured Text and a couple of actions.

For this post, two new actions will be created, one on the create event which will send an email to advise that there is something to review.

And one on the cancel event to advise that review has been cancelled.

Now when we create the task above it should send an email and when it is cancelled it will send an email too.

Workflow Definition

The same workflow that was developed previously will be used, it allows a user to read the text from a PDF, create a task for a human to review and amend if necessary and then emails the creator if the document reviewer was not the creator.

The workflow starts once a document has been uploaded through a page, the document is stored in a table, below, and the REVIEW_STATUS is set to "PENDING REVIEW" initially, the Read Text from PDF activity calls an PL/SQL api to read the text in the BLOB uploaded and stores the text in the CLOB_CONTENT column. It then immediately creates a Task for a Human to verify the CLOB_CONTENT and adjusting before checking if the reviewer was the creator and sending a review complete email.

When the workflow is terminated we want to reset the REVIEW_STATUS to "NOT REVIEWED" and the CLOB_CONTENT to null, and cancel the Task. Both of which do not occur using the Terminate Workflow button on the workflow console.

To cancel the task we need to find the task ID, so a new Workflow Variable was added called CURRENT_TASK_ID.

This when then associated with the Task Id item for the Human Verification Task activity.

Now when the task is created it will store the task ID in the CURRENT_TASK_ID workflow variable.

Automate it

With the introduction of workflow, some new views were provided to let us access what we need, for this automation we only need a couple

• APEX_WORKFLOWS shows you the individual workflows.

• APEX_WORKFLOW_VARIABLES shows the variables and their values for a particular workflow_id.

The APEX_WORKFLOW view has a state_code that can be used to determine whether the workflow has been terminated. It also stores the DETAIL_PK which we use to store the Primary Key of the record we are processing in the workflow. In this case it is the DOC_ID in the DOCUMENT_REVIEWS table.

The following query is used to define the Automation. It will return the three fields we will use to handle the termination and the workflow_id used in reporting in the log file.

SELECT w.workflow_id,
       w.detail_pk,
       t.task_id,
       t.initiator
FROM   apex_workflows w,
       apex_workflow_variables v,
       apex_tasks T
WHERE  w.state_code = 'TERMINATED'
       AND w.application_id = :APP_ID
       AND v.number_value = t.task_id
       AND w.end_time > apex_automation.get_last_run
       AND t.state_code NOT IN ( 'CANCELED', 'ERRORED' )
       AND v.application_id = w.application_id
       AND v.workflow_id = w.workflow_id
       AND v.static_id = 'CURRENT_TASK_ID';

There is a single action on the automation which uses the following code.

The first update statement resets the review_status on the document_reviews table.

There is then some logging to the Automation Log so you can see what's gone on historically.

To cancel a task you need to be the initiator or the Business Administrator so the call to apex_session.create_session just switches user to the original task initiator, before cancelling the task and updating the log.

BEGIN
    UPDATE document_reviews
    SET    review_status = 'NOT REVIEWED'
    WHERE  doc_id = :DETAIL_PK;

    apex_automation.Log_info(p_message => 'Task_id '
                                          ||:TASK_ID
                                          ||' requires cancelling!');

    -- set user session to initiator of task
    apex_session.Create_session (p_app_id => :APP_ID, 
                                 p_page_id => 1,
                                 p_username => :INITIATOR);

    apex_automation.Log_info(p_message => 'User: '
                                          ||V('APP_USER')
                                          ||'. App is '
                                          ||V('APP_ID')
                                          ||', session is '
                                          ||V('APP_SESSION'));

    BEGIN
        apex_human_task.Cancel_task (p_task_id => :TASK_ID);

        apex_automation.Log_info(p_message => 'Task '
                                              ||:TASK_ID
                                              ||' cancelled sucessfully.');
    EXCEPTION
        WHEN OTHERS THEN
          apex_automation.Log_error(p_message => SQLERRM);

          apex_automation.Log_error(p_message => 'Error cancelling task!');
    END;
END;

We can set the automation to run as frequently as required, it will only process items that were Terminated after the last time it ran (using apex_automation.get_last_run).

And that is it, when the automation runs it will find terminated workflows, reset the status and cancel the open task, the task has the new cancel action on it an sends an email accordingly, advising of the cancellation.

For the purposes of this blog post the above has been simplified but you should be able make use of the automation code and add extra criteria to it for specific workflows or applications etc.

Closing wishes

Hopefully the Oracle APEX development team will add in a feature that allows some sort of a hook to call code or a function, procedure or plug in even that can be run when a workflow is terminated. Ideally accessed in the Workflow builder tree.

Update

Oracle APEX Development has confirmed this is on the roadmap!