Oracle APEX lets low-coders easily automate human approvals. With a bit of coordination logic, you can manage multi-step ones, too. Read on to learn how a company gets stories for their corporate website approved by all the required stakeholders before publishing. You’ll find the link to download and try the sample app at the end.
Who Writes & Approves Stories?
To make the sample app more realistic and interesting, we imagine a small widget-making company with a corporate web site. Each new story for the site needs a multi-step approval before publishing. The figure below shows the company’s eight colleagues. KIM is the founder and runs the company. CHAZ leads Marketing and is RORY‘s manager. GINA directs Editorial and mentors TINA. Last but not least, PAT oversees Product Management (PM) and manages BO and JANE.
Typically product managers BO and JANE author stories for the website. These first get approved by their manager, then by someone in the Editorial and Marketing departments, and finally by KIM the CEO before the story can go live.
Adding Workplace Info for APEX Users
Oracle APEX user accounts have a user_name and email , but no built-in department name or manager field. Since the multi-step process depends on approvals by manager and by multiple different departments, I added tables to the application’s data model to track department names, which department each user belongs to, and who their manager is. The figure below shows the page in the sample app for managing departments. The MARKETING, EDITORIAL, and PM (product management) ones are predefined.
The page for managing users is below. The initial users’ departments and managers are also setup by the app’s Supporting Objects installation scripts.
Overview of the App Data Model
The two pages above manage rows in the eba_demo_story_department and eba_demo_story_users table shown in the data model below. Stories bound for the corporate website live in the eba_demo_story table. The eba_demo_story_appr_step_def table contains the “template” approval steps that every story needs to follow. Finally, the eba_demo_story_appr_steps tracks the progress of each approval step for each story requiring approval.
Defining the Approval Steps
The page to manage the approval step “template” is below. The four steps you see come pre-installed. They illustrate the different ways the sample lets you define the approver for a step. It can be a specific username like KIM, a first-to-fourth level manager of the story author (e.g. #MANAGER1#), or all the members of a particular department (e.g. #EDITORIAL#). Each step also provides a descriptive title.
Submitting a Story for Approval
As shown below, from the Stories page in the sample, Jane clicks (Create) to author a story about a new version of the company’s widget.
After Jane creates the story, she can revisit the Story page to edit it. As shown below, when she’s ready to submit it for approval, she clicks on the (Submit for Approval) button. It uses an Invoke API page process to call the submit_for_approval() procedure in the eba_demo_story_approvals package explained in the next section.
Once she’s submitted her story for approval, the approval status appears in the Stories page as shown below. Its status is Submitted for Approval and indicates it’s waiting on PAT in step 1 of 4 to give the first level manager approval.
Any user viewing the story after it’s been submitted for approval sees the Approvals tab shown below with the current progress of the approval chain. It’s a classic report region showing the rows in the approval steps table, for the given story id and iteration number of the approval chain for that story.
The Approval Coordination Package
The eba_demo_story_approvals package coordinates the multi-step process. When the Story page invokes submit_for_approval(), passing the story id, it inserts rows in the approval steps table (eba_demo_story_appr_steps). These come directly from the “template” steps in the approval steps definition table (eba_demo_story_appr_step_def) as shown below. In the process it determines what iteration number of the approval process is about to start, and includes the story_id and iteration number in the table as well.
The coordinator package initiates the single Story Approval task definition (with static id STORY_APPROVAL) once for each step of the approval process. It does this by calling the create_task() function in the apex_approval package. The function returns the task ID of the new task instance, which the coordinator package stores in the task_id column of the approval steps tracking table for the current step.
-- Create new STORY_APPROVAL task instance for approval step
l_new_task_id :=
apex_approval.create_task(
p_task_def_static_id => 'STORY_APPROVAL',
p_subject => apex_string.format(
p_approval_step.title
||' for "%s"',
l_story.name),
p_initiator => l_story.created_by,
p_parameters => approval_params(
p_approval_step,
p_total_steps),
p_detail_pk => p_approval_step.story_id);When creating the task instance, it passes in key information using task parameters. For example, it provides the number of the current approval step (p_approval_step), the total number of approval steps (p_total_steps), and a comma-separated list of one or more approver usernames (p_potential_owners) for the current step in the approval “chain”. It also sets the subject of the current approval task based on the current approval step’s title, the initiator to be the story’s creator, and the Detail Primary Key to be the story ID. This information lets a single task definition handle all levels.
Story Approval Task Definition
As the figure below depicts, the STORY_APPROVAL task definition has actions for approval and rejection. Each is a one-line PL/SQL call to the handle_approval() or handle_rejection() procedure in the coordinator package.
For example, the Complete (Approved) action’s PL/SQL code is the following line, passing in the task ID using the built-in :APEX$TASK_ID bind variable.
eba_demo_story_approvals.handle_approval(:APEX$TASK_ID);As conveyed by the arrow on the left side of the handle_approval() procedure in the figure, if another approval step exists with a null status it repeats the process described above to call create_task() again for the next step. Using the current iteration number, it determines the next step (if any) by selecting the first row from the approval steps table with the current story’s ID where status is null ordered ascending by the step column.
Finally, notice in the figure above that the STORY_APPROVAL task definition contains several other actions that send notification emails:
- On creation, it notifies the one or more potential approvers
- On request information, it alerts the story creator that more info is needed
- On submit info, it informs the approver that info has been provided
- On approval, if it’s the final approval step, it emails the story creator
Enhancing the Task Details Page
After Jane’s first-level manager Pat receives the email that a story awaits her approval, Pat logs in and sees Jane’s story in her Approvals page as shown below. Notice how the title of approval step 1 and the story title were incorporated into the subject of the task.
Clicking on the subject of the approval, Pat opens the Story Review page below. I created this page by clicking on the (Create Task Details Page) button on the STORY_APPROVAL task definition edit page in the APEX builder. By default it has all of the built-in APEX approvals functionality. I then simplified it in Page Designer by hiding two regions and enhanced it to add the story text to review. Of course Pat can approve or reject the task, but she can also add comments, request more information from Jane, review the approval audit history, or delegate the approval to a colleague. Since Pat also happens to be the business administrator for all approvals in the company, she’s allowed to perform additional actions like invite an additional approver or change the due date.
In short, the enhancements I made were:
- Hid Overview & Details regions by setting Server-Side Condition to Never
- Added region to display read-only story text
- Searched page for
Task approved&Task rejectedand changed Task → Story
I updated similar generic-sounding Task approved and Task rejected messages on the Approvals Unified Task List page, too. These changes along with other small tweaks to No Data Found messages around the app make a big difference for end users so are well worth the time. Now when a user approves a story, they aren’t scratching their head wondering, “What’s a task?” or “What’s a row?” The figure below shows the app-specific “Story approved” success message and the “No stories need your approval at the moment.” No Data Found text on the Approvals page.
After Pat approves the story, the task definition’s approval action invokes the coordinator package’s handle_approval() as mentioned above. It determines there is another step to perform and creates a new task instance for that new step. Pat sees in the Stories page below that either Gina or Tina in the editorial department is next in line.
Letting Coordinator Pass In Approvers
A task definition can specify its approvers (called “potential owners”) using a static value, a SQL query, or using PL/SQL. In the SQL Query case, it needs to return a one-column result containing one username per row. In the PL/SQL case, it needs to return a comma-separated list of case-sensitive usernames. Did I mention the usernames ARE case SeNsItIvE? This is the most common problem developers encounter using task definitions. Since APEX user accounts are always in UPPERCASE, keep that in mind when using approval features.
That said, in the multi-step use case we need the approval step coordinator logic to pass in the list of approvers for the current task instance at create time. It gets the list of approvers for the current step from the approver column of the approval steps table. We enable this by defining the P_POTENTIAL_OWNERS parameter in the STORY_APPROVAL task definition, and setting its Potential Owner to the PL/SQL expression consisting simply of the task parameter bind variable :P_POTENTIAL_OWNERS. Accordingly the coordinator package passes in a comma-separated list of case-sensitive usernames for the value of the P_POTENTIAL_OWNERS parameter.
Expanding Approver Substitutions
We saw above that step 2 in the approval chain was assigned to GINA or TINA. When multiple usernames appear in the potential owners list, the approval task shows in the Unified Task List inbox page for all of them. When one claims the task to work on, approves, or rejects it, the others no longer see it.
The coordinator’s submit_for_approval() procedure starts by calling copy_story_approval_steps_for(). It “clones” the approval step definition template rows for the new multi-step story approval process being created. As it loops through the approval step definition rows, it calls the expand_substitution() function for any approver value like #%# to replace it by the one or more usernames that the substitution token represents.
Step 1 in the story approval steps definition uses the substition token #MANAGER1# to require a first-level manager’s approval. Step 2 specifies #EDITORIAL# to represent a list of anyone in the Editorial department. The expand_substitution() is straightforward. If the substitution token matches the regular expression ^#MANAGER[1-4]#$ then it’s replaced by the corresponding Nth-level manager if it exists. To compute the values of up to four levels of managers in one row, the code queries the eba_demo_story_users table using the story creator’s username, and outer joins with the same table four times:
select mgr1.username manager1_username,
mgr2.username manager2_username,
mgr3.username manager3_username,
mgr4.username manager4_username
from eba_demo_story_users usr
left outer join eba_demo_story_users mgr1
on mgr1.id = usr.manager_id
left outer join eba_demo_story_users mgr2
on mgr2.id = mgr1.manager_id
left outer join eba_demo_story_users mgr3
on mgr3.id = mgr2.manager_id
left outer join eba_demo_story_users mgr4
on mgr4.id = mgr3.manager_id
where usr.username = upper(p_story_creator)If instead the substitution token is a name surrounded by pound signs, it trims them off both sides and uses the department name to query the eba_demo_story_department table joined with the users table. Using listagg() it retrieves a comma-separated list of department users like this:
l_department_name := trim(both '#' from p_substitution_text);
select listagg(u.username,',')
into l_result
from eba_demo_story_users u
left join eba_demo_story_department d
on d.id = u.department_id
where d.name = l_department_name;Understanding Actions Source Query
After someone in the Editorial and Marketing departments approves the story, as shown below it’s ready for CEO Kim’s approval.
Once Kim approves, the STORY_APPROVAL task definition will finally email Jane the story’s creator to let her know the good news. It’s important to understand the grand declarative power of the task definition’s Actions Source Query. Recall that every task definition has an associated system of record primary key called its Detail Primary Key. In this app, the coordinator package passes in the story ID to play this role. It’s available inside the task definition using the :APEX$TASK_PK bind variable. Other frequently-used bind variables include :APEX$TASK_ID, :APEX$TASK_INITIATOR, and :APEX$TASK_OWNER. Refer to the Substitution Strings for Tasks in the APEX doc for the complete list.
The Actions Source Query lets you easily retrieve any additional information your task definition requires to get its job done. This includes anything action code, emails, push notificiations, or their server-side conditions may require. The SQL query will typically reference the Detail Primary Key value and possibly the task ID to pull in all supplementary data required. Any of its SELECT list column names is available to reference throughout the task definition using either bind variable or substitution string syntax. The task parameter names can also be used in the same way.
The query below is the STORY_APPROVAL task definition’s Actions Source Query. It includes a CASE statement to derive the Y or N value for an IS_FINAL_APPROVAL column depending on whether the P_APPROVAL_STEP parameter equals the P_TOTAL_STEPS parameter. It includes multiple joins to the apex_workspace_apex_users table to pickup the email addresses of the story creator, potential approvers, and the task owner. It references the apex_task_participants view to access the task’s potential owners (i.e. approvers). Its SELECT list column values are referenced by name as appropriate in the task definition actions that send emails and by their server-side conditions to make the actions conditional. The admittedly odd-looking coalesce(…) expressions let you override the email address for all notifications for demo purposes as explained in a later section.
select stp.approver as approvers,
case
when :P_APPROVAL_STEP = :P_TOTAL_STEPS
then 'Y' else 'N'
end as is_final_approval,
sty.name as story_title,
sty.created_by as story_author,
coalesce(:P12_OVERRIDE_EMAIL_ADDRESS,
styusr.email) as story_author_email,
(select listagg(coalesce(:P12_OVERRIDE_EMAIL_ADDRESS,
aprusr.email),',')
from apex_task_participants apr
left outer join apex_workspace_apex_users aprusr
on aprusr.user_name = apr.participant
where apr.participant_type = 'POTENTIAL_OWNER'
and apr.task_id = :APEX$TASK_ID
and aprusr.email is not null) as approvers_email,
coalesce(:P12_OVERRIDE_EMAIL_ADDRESS,
ownusr.email) as task_owner_email
from eba_demo_story sty
left join eba_demo_story_appr_steps stp
on stp.story_id = sty.id
left outer join apex_workspace_apex_users styusr
on styusr.user_name = sty.created_by
left outer join apex_workspace_apex_users ownusr
on ownusr.user_name = :APEX$TASK_OWNER
where stp.story_id = :APEX$TASK_PK
and stp.step = :P_APPROVAL_STEPUnifying Task Comments & History
Stories don’t always emerge from the approval chain unscathed. At any step of the process, the current approver can add comments on the Story Approval Review page, request additional information from the story creator, or even outright reject the story. The sample app lets you explore these outcomes as well, and anticipates them in two key ways:
- It unifies comments and history across task instances in the chain, and
- It lets the story approval chain undergo multiple iterations
In this section we’ll explore the comments and history, and the following one explains the iterations. For example, the screenshot below shows the feedback Bo got from Pat, her first level manager. Pat has requested more information before approving it.
Once Bo updates the story and visits the My Story Approvals page to indicate she’s submitted the information Pat requested, Pat gets an email letting her know the story is ready for another look.
After Pat approves, as shown in the figure below, when Gina in Editorial reviews Bo’s story next she sees the discussion from previous approval steps, too. She also requests more information from Bo, asking her to remove the use of passive voice.
By default, the generated Task Details page the APEX builder creates only shows the comments for a single task instance. To unify the display of all comments from all tasks in the approval chain, I simply modified the WHERE clause of the Comments region on that page to what appears below. It shows the comments for all the task IDs from the story approval steps table related to the current task’s story ID and iteration number. Had it been desired, the iteration could have been removed from the criteria to show all comments across all iterations of a story’s approval process.
select ... etc. ...
from apex_task_comments
where task_id in (
select task_id
from eba_demo_story_appr_steps
where (story_id,iteration)
in (select story_id, iteration
from eba_demo_story_appr_steps
where task_id = :P3_TASK_ID)
)Along a similar vector, I modified the query of the page’s History region to display the task history for the same set of task IDs using the query below:
select x.event_type,
x.event_timestamp,
x.event_creator_lower,
x.display_msg
from eba_demo_story_appr_steps steps,
table ( apex_approval.get_task_history (
p_task_id => steps.task_id,
p_include_all => :P3_ALL_HISTORY ) ) x
where steps.task_id in (
select task_id
from eba_demo_story_appr_steps
where (story_id,iteration)
in (select story_id, iteration
from eba_demo_story_appr_steps
where task_id = :P3_TASK_ID))Rejections and Iterations
When a story gets rejected by any approver, the handle_rejection() procedure in the coordinator package sets the status of the approval step and the story to Rejected. But that’s not the end of the line for the author. As shown below, Chaz in Marketing rejected Bo’s customer profile for a reason out of her control.
She can rework the story based on constructive feedback received and submit it again for another iteration through the approval chain. After Bo spoke with Chaz, she learned she could save her story by using different people’s names for the testimonials, so Bo made the changes and resubmitted for approval.
The submit_for_approval() procedure calls the package’s get_latest_iteration() function to determine the maximum value of the iteration column for a given story ID in the approval steps table. If it returns null, then the logic defaults the iteration number to 1. Otherwise, it increments the latest iteration number by one. As shown below, if a story going through the multi-step approval process for a second or subsequent time, the Story page’s Approvals tab conditionally displays the “Second Iteration” heading.
One interesting technique I learned while implementing this part of the sample was how easy it wasy to format the number value of the iteration into ordinal words like “Second”, “Third”, or “Fourth.” The expression below is used on the Story page in a pre-rendering computation for the hidden P10_ITERATION_TITLE page item.
initcap(to_char(to_date(:P10_LATEST_APPROVAL_ITERATION,'j'),'jspth'))Soft Deleting and Undeleting Stories
Since approval task instances are historical records that reference the Detail Primary Key of a row in a system of record table, we don’t want to actually remove a story when an author decides one is a lost cause and deletes it. This would “orphan” the task instance related to that story and potentially cause problems. So, we let the end-users think they are deleting a story, but we really “soft delete” it. This happens by changing the deleted flag column on the eba_demo_story row in question from 0 to 1. Accordingly the Stories page contains a WHERE clause to show stories where deleted = 0.
To implement this soft delete, on the Story page I duplicated the Process form Story page process of type Form – Automatic Row Processing (DML) and renamed the original one to Process form Story (Create,Update) and the copy to Process form Story (Soft Delete). As shown below, I configured a Server-side Condition on the (Create,Update) one to execute only if the Request is contained in Value CREATE,SAVE.
For the (Soft Delete) one, as shown below, I set the Target Type to PL/SQL Code and entered the simple update statement to set the current story’s deleted flag column to the value 1. By also adding a Server-side Condition I confined this special-case handling to only happen for story delete requests. Thanks goes to my colleague Carsten for the tip on handle this soft-delete in a low-code way.
With the deleted flag in place, and the soft-delete working. It was easy to add a Recently Deleted Stories page to give authors a 10-day period in which to undelete any story that “deleted” by mistake. Even after 10 days we don’t actually delete the story, but to avoid accumulating a long list of deleted stories over the months and years, we set a 10-day “grace period” which felt right. The page is shown below. It’s essentially a copy of the Stories page, but with a where deleted = 1 clause instead. Its Interactive Report region’s Link Column is configured to use an fa-undo icon instead of the usual edit icon:
<span role="img" aria-label="Undelete"><span class="fa fa-undo" aria-hidden="true" title="Undelete"></span></span>As shown below, its link Target redirects to the Stories page (9) setting the P9_STORY_ID_TO_UNDELETE page item to the value of the clicked-on story ID to undelete and signalling special processing is required by setting the Request to UNDELETE.
Over in the Stories page, a conditional Execute Code page process configured to only run if Request = UNDELETE performs the simple update statement to revert the deleted flag back to 0 for the story id passed in the parameter.
Overriding Email Address for Notifications
For a sample app, it is convenient to have all the email notifications come to your own email address. You can do that by typing in the email you want to use on the Email page and clicking the button to save the setting for the duration of the current session.
If the setting is not provided, the sample tries to retrieve email addresses from the APEX use account for the story creator and approvers. If neither email address resolves to a valid value, no email gets sent. This P12_OVERRIDE_EMAIL_ADDRESS page item with its Storage property set to Per Session (Persistent) made an appearance above in the Story Approval task definition’s Actions Source Query. Using the coalesce() expression in multiple places in reference to the value of :P12_OVERRIDE_EMAIL_ADDRESS that query allowed its value to take precedence over the email address values it tried to query from the apex_workspace_apex_users view.
Easy Demo-Purpose-Only Logins
The last aspect of the sample to explore involves the techique I’ve used to simplify switching between different users to experiment with the story approval process. For a production application, you should ABSOLUTELY use Oracle APEX accounts, sigle sign on, or some other supported authentication scheme to ensure that only expected users with valid credentials can access your app. However, for a sample aimed at helping developers learn about approvals, I’m trying out a new idea using a custom authentication scheme that considers any username that appears in the eba_demo_story_users table to be a valid user. The sample app’s login page shows the users in a management hierarchy, and allows you to easily login as any user in the tree by selecting a user node and clicking the (Login as USER) button that appears. When you first run the application, you won’t be logged in so you can select any user and click the button to login as shown below.
Once you are logged in, you can easily switch to wear the “hat” of another user in the company by visiting the Switch User page shown below. It confirms who you are currently logged-in as, and lets you select any different user in the tree and login by clicking the button. In both situations, you’re seeing the Demo Purposes Only Login page. However, using an appropriate condition on the Navigation Menu items labeled Login and Switch User we help guide the user to perform the task at hand.
This approach, again recommended only for demo purposes, also saves developers the trouble of having to create eight additional APEX user accounts before they can try out the sample. If any APEX account does exist with a username matching a sample user in the eba_demo_story_users table, then the sample will pickup their email address (unless it’s been overridden for the session using the facility described above).
Downloading the Sample
You can download the sample from here. Try creating some stories and running them through the approval chain. Try adjusting the approval step definitions using multiple levels of management to see what happens when a story author doesn’t have a third-level manager, for example. Try changing the departments, the users, or how users are assigned to departments or report to each other. Hope the sample proves useful and you find some exciting ways to use APEX approvals in your upcoming apps.
Dive Into APEX 