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.
Both CURRENT_STATUS and NEXT_POSSIBLE_STATUS are foreign keys to a TBL_STATUS table, that looks like this:
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):
|2||Submitted to Level 1||SUBMIT_LVL_1||Submit to Level 1|
|4||Submitted to Level 2||SUBMIT_LVL_2||Submit to Level 2|
|5||Returned for revisions||RETURN_REV||Return for Revisions|
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:
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”>
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:
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!