Simple workflow implementation in APEX

A client of mine wanted me to build an online form using Oracle APEX that, while fairly simple in design, had a fairly complicated workflow associated with it.

Each document had to flow through many different approval levels, and at each point, the possible actions a user could take would differ.

At first glance I thought I’d just manually create a lot of different buttons on the page and use various authorization schemes to display or hide them depending on document status and user role. I did not look forward to this task, considered the likeliness of the workflow changing (high!), and thought there must be a better way.

What I ended up doing was translating the workflow into table form based on the current document status.

TBL_STATUS_WORKFLOW

ID number;
CURRENT_STATUS number;
NEXT_POSSIBLE_STATUS number;

Both CURRENT_STATUS and NEXT_POSSIBLE_STATUS are foreign keys to a TBL_STATUS table, that looks like this:

TBL_STATUS

STATUS_ID number;
STATUS_DESCRIPTION varchar2;
BUTTON_REQUEST varchar2;
BUTTON_LABEL varchar2;

Say, for example, my document is in DRAFT mode (status). My workflow says that a document that is still in ‘Draft’ can go either to ‘Submitted to Level 1’ status or ‘Withdrawn’. Ideally, at this point, I would like to display 2 buttons to my user: ‘Submit to Level 1’ that would trigger a status change and email to appropriate party, and ‘Withdraw’, which would also trigger a status change.

My tables look something like this (small sample only):

TBL_STATUS

To translate my workflow and indicate that the document can move from Draft to either Submitted to Level 1 or Withdrawn, my TBL_STATUS WORKFLOW looks like this:

Obviously, this table is a lot bigger, as I stored all the possible outcomes for each of 12 different statuses. Otherwise, obviously, I would have gone the manual button route!!

How do I display my buttons on my APEX page now? By creating a PL/SQL Dynamic Content region that looks something like this:

declare

Begin

for c in (select s.status_id, button_request, button_label
from tbl_status_workflow sw, tbl_status s
where sw.current_status=:P5_STATUS and sw.next_possible_status=s.status_id) loop

if pkg_workflow.should_display_button(:P_USER_ID,c.status_id) then
htp.p(‘<button value=”‘||c.button_label||'” onclick=”apex.submit(”’||c.button_request||”’);” class=”button-gray” type=”button”>
<span>’||c.button_label||'</span>
</button>’);
end if;
end loop;
end;

Whether or not I displayed a button to a user not only depended on the document’s current status, but also to the user’s role, hence the pkg_workflow.should_display_button call.

I also created an After Submit process on the page that was simply the following:

begin

pkg_workflow.change_status(:P5_DOCUMENT_ID,:REQUEST);

pkg_workflow.notify_parties(:P5_DOCUMENT_ID,:REQUEST);

end;

Both of these take the value of the button’s request and the document’s id and process it accordingly. The first one simply changes the document’s status (and therefore automatically displaying a new series of buttons in the Dynamic PL/SQL region based on the TBL_STATUS_WORKFLOW table, cool!), and the second fires off any emails that are required by the workflow.

Obviously there are a few other tables involved (I also created a table of email templates for the email notifications, that were linked to the new status of the document), as well as a table that identified which buttons should display for which user role (used by the pkg_workflow.should_display_button function).

But basically, being able to dynamically generate the buttons using the Dynamic PL/SQL region, instead of creating them manually and adding a bunch of authorization schemes, I saved myself a ton of work!

How have you implemented workflow requirements into your APEX apps? I’d love to hear from you!