This post was originally published on 8 August, 2022.
What is a Multi-level Approval?
A multi-level approval system involves more than one level of approval to process a request. In case of a Leave approval, the request usually goes to the applicant’s manager and the request is completed once the manager approves or rejects the request. This is an example of a single-level approval. However, in case of a Travel Approval , the travel request, once approved by the immediate manager, can go to the next manager and so on depending on the travel expense amount. This is a typical use case for a multi-level approval.
Designing a Travel Request App
In this section, we will design a Travel Request Application where an employee can make a request to apply for travel and the manager of the Employee either approves or rejects the travel. Depending on the budget amount, the request might need further approval from the manager’s manager and so on.
This is how such an approval flow would typically look:
Note: With the availability of Workflow feature in APEX 23.2 release, it is now possible to visualize and design multi-level approvals in a more declarative manner, which you can read about in another blog. This blog is still relevant if you are looking to use using Approval Components, standalone, without Workflow and is also relevant for pre 23.2 versions of APEX.
Prerequisite
It is assumed that the users logging in into the application at run time, have already been created beforehand. Create the following users using the Manage Users And Groups menu option under Workspace Administration: SOPHIE, MATT, JANE, CLARA and JOHN.
Create the App
- Navigate to App Builder
- Click Create
- Click New Application
For Name – enter Travel Request Approval
Sample Table and Sample Data
Install the Dataset
- Navigate to SQL Workshop and click SQL Scripts
- Click Create
- Copy and Paste the commands given below into the Script Editor to create a new Table EMP_1 and insert employee records into it.
- Click Run
- Click Run Now.
CREATE TABLE "EMP_1"
("EMPNO" NUMBER GENERATED BY DEFAULT ON NULL
AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 8000 CACHE 20 NOORDER NOCYCLE NOKEEP NOT NULL ENABLE,
"EMP_NAME" VARCHAR2(10),
"MGR" NUMBER(4,0),
CONSTRAINT "EMP_1_PK" PRIMARY KEY ("EMPNO"));
insert into emp_1(empno, emp_name, mgr) values (10, 'JOHN', 30);
insert into emp_1(empno, emp_name, mgr) values (20, 'CLARA',30);
insert into emp_1(empno, emp_name, mgr) values (30, 'JANE', 40);
insert into emp_1(empno, emp_name, mgr) values (40, 'MATT', 50);
insert into emp_1(empno, emp_name, mgr) values (50, 'SOPHIE', null);
commit;
CREATE TABLE "EMP_TRAVEL_REQUEST"
("REQ_ID" NUMBER GENERATED BY DEFAULT ON NULL
AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999
INCREMENT BY 1 START WITH 8000 CACHE 20 NOORDER NOCYCLE NOKEEP NOT NULL ENABLE,
"EMP_NO" VARCHAR2(10),
"TRAVEL_TYPE" VARCHAR2(25),
"NO_OF_DAYS" NUMBER(4,0),
"ESTIMATED_COST" NUMBER(8,0),
"UPDATED_BY" VARCHAR2(20),
"STATUS" VARCHAR2(20),
CONSTRAINT "EMP_TRAVEL_REQUEST_PK" PRIMARY KEY ("REQ_ID"));
Creating the Task Definition
In the empty Application, we go to the Shared Components→ Workflows and Automations→ Task Definitions and hit Create.
Fill in the Create Task Definition Wizard fields as shown below, and hit Create.
Leave the Potential Owner and Business Administrator fields empty for now. We will fill them up in the next section.
We can now further configure the Travel Request Task Definition in the Edit Task Definition screen that opens once the Create is hit.
Adding the Action source – This step ties our task to the system of records, in this case, the employee records. Select SQL Query as the action source and add the following SQL statement in the Actions SQL Query editor.
select e.empno, e.emp_name, m.emp_name as mgr_name from emp_1 e, emp_1 m where m.empno(+)=e.mgr and e.empno=:APEX$TASK_PK
APEX$TASK_PK is a substitution string that holds the value of primary key of the system of records to which the task is tied. In this case, it will hold the employee number of the employee for whom this travel request task is being submitted. Note: Please check the documentation for a complete list of Substitution Strings available for Approvals Component.
Generating Task Details Page – Notice that the Task Details Page Number is empty. Click on the Create Task Details Page button to generate the details page for this task. You will get a popup dialog asking if the next available page number should be used. Click OK.
We will find that the Task Details Page Number is replaced by the Task Details Page URL containing the link to the generated Details Page.
Add Task Participants
In the Participants section, select Participant Type as Potential Owner and Value Type as SQL Query. In the Value field, add the following SQL query
select emp_name from emp_1 where empno=(select mgr from emp_1 where empno=(select empno from emp_1 where emp_name=:APP_USER))
NOTE: This query will fetch the manager of the employee logging in. So when an employee submits the travel request, the potential owner of the task is his manager.
We will revisit this query again when we talk about how the multi-level approval is achieved through the Actions defined for the Task Definition.
Add Task Parameters
Add the following entries to the Task Parameters section.
NOTE: Any parameter you add to the Task Parameters , can be used as bind variables in your participant or actions source SQL queries, as well as substitution strings in the task definition subject as shown in this example, where the subject contains a reference to the TRAVEL_TYPE parameter.
Add Actions
You could choose to perform additional actions when an event (defined in the Task Events section above) occurs on a task. Click on Add Action as shown below . This will take you to the Task Definition Actions Edit Page
We will add the following actions:
1) An action for when the Travel Request is submitted, i.e when the task gets created.
In the Edit Action page, enter the following details:
Name: CREATE_TRAVEL_REQUEST_ENTRY
Type: Select Execute Code from the Select List
Execution Sequence: Enter 10
On Event: Select Create from the Select List.
In the Code Section, select Language as PL/SQL and add the following code in the Code Editor.
declare
l_req_id number;
begin
if :APP_USER = :EMP_NAME then --this is the original initiator
l_req_id := :APEX$TASK_ID;
-- create a new record in the Employee Travel Request table EMP_TRAVEL_REQUEST
insert into emp_travel_request values
(to_number(l_req_id),
:EMPNO,
:TRAVEL_TYPE,
:NO_OF_DAYS,
:TRAVEL_COST,
'',
'PENDING');
end if;
end;
2) An action for when the Travel Request is approved by the immediate manager, i.e when the task gets approved by the potential owner calculated using the Query specified when defining the potential owner.
The action in this case would execute a code that
a) checks if the travel cost is below 50,000/- or if the current approver is already the top line manager, in that case the travel is approved and does not need another level of approval.
b) if the travel cost is 50,000/- or more, then it creates a new task based on the same TRAVEL_REQUEST task definition , and assign it to the manager of the current approver of the task. The query defined for the potential owner in the Task Participant section will always return the manager of the current logged in user who is approving the task.
apex_approval.create_task API is called passing the task parameters from the original travel request task and the updated_by parameter is set to the current owner of the task who has approved it.
To define this action, click on Add Action button in the Task Definition Edit page
In the Edit Action Page, enter the following details:
Name: NEXT_APPROVER_OR_UPDATE_STATUS
Type: Select Execute Code from the list.
Execution Sequence: 20
On Event: Select Complete from the list
Outcome: Click on the Approved radio button
In the Code Section, select Language as PL/SQL and add the following code in the Code Editor.
declare
l_mgr number;
l_task_id number;
l_request_id number;
l_req_status varchar2(10) :='PENDING';
begin
select mgr into l_mgr from emp_1 where emp_name=:APP_USER;
if :APP_USER = :MGR_NAME then --this is the first approver
-- set the request id to be the id of the task created when the request was submitted
l_request_id := :APEX$TASK_ID;
else
-- this is an intermediate approver, set the request id from the corresponding task parameter value
l_request_id := :REQ_ID;
end if;
if l_mgr is null or :TRAVEL_COST < 50000 then -- the approval is complete
update emp_travel_request set status = 'APPROVED', updated_by=updated_by||'->'||:APP_USER
where req_id = l_request_id and emp_no=:APEX$TASK_PK;
l_req_status := 'APPROVED';
else -- the request needs to go through another level of approval
-- updated the request record with details of the current approver in the chain of approvers
update emp_travel_request set updated_by = updated_by||'->'||:APEX$TASK_OWNER
where req_id = l_request_id
and emp_no=:APEX$TASK_PK;
-- create a new task assigned to the manager of the current approver
l_task_id := apex_approval.create_task(
p_application_id => :APP_ID,
p_task_def_static_id => 'TRAVEL_REQUEST',
p_initiator => :EMP_NAME, -- ensure initiator is the original requestor and not the current task owner
p_parameters => apex_approval.t_task_parameters(
1 => apex_approval.t_task_parameter(static_id => 'TRAVEL_TYPE', string_value => :TRAVEL_TYPE),
2 => apex_approval.t_task_parameter(static_id => 'NO_OF_DAYS', string_value => :NO_OF_DAYS),
3 => apex_approval.t_task_parameter(static_id => 'TRAVEL_COST', string_value => :TRAVEL_COST),
4 => apex_approval.t_task_parameter(static_id => 'REQ_ID', string_value => l_request_id),
5 => apex_approval.t_task_parameter(static_id => 'STATUS', string_value => l_req_status)
),
p_detail_pk => :APEX$TASK_PK
);
end if;
end;
Click on the Create Button.
3) An action for when the request is rejected. In this case the EMP_TRAVEL_REQUEST table will be updated with the status and the updated_by information will be saved.
To define this action, click on Add Action button in the Task Definition Edit page
In the Edit Action Page, enter the following details:
Name: UPDATE_REQUEST_STATUS
Type: Select Execute Code from the list.
Execution Sequence: 30
On Event: Select Complete from the list
Outcome: Click on the Rejected radio button
In the Code Section, select Language as PL/SQL and add the following code in the Code Editor.
declare
l_mgr number;
l_task_id number;
l_request_id number;
l_req_status varchar2(10) :='PENDING';
begin
select mgr into l_mgr from emp_1 where emp_name=:APP_USER;
if :APP_USER = :MGR_NAME then --this is the first approver
l_request_id := :APEX$TASK_ID;
else
l_request_id := :REQ_ID;
end if;
-- the request is complete and rejected
update emp_travel_request set status = 'REJECTED', updated_by=updated_by||'->'||:APP_USER
where req_id = l_request_id and emp_no=:APEX$TASK_PK;
end;
Click on the Create button to add this action.
At the end, your Task Definition Edit page should show 3 actions as shown below:
That’s all! Your task definition for Travel Request is complete. Apply Changes and come back to your application.
Creating the Apply for Travel page
Now that we have the task definition created, we will need to create the page which the employee will submit when requesting for travel. Submitting this page will create a Travel Request task based on the task definition we have just configured.
Click on Create Page in your Travel Request Approval application.
Name this page “Apply For Travel” and add it as a breadcrumb under the Home page
In the Page Designer, create a new Form and name it New Travel Request. Choose SQL Query as the source and enter the following in the query region:
select e.empno, e.emp_name, m.emp_name as mgr_name from emp_1 e, emp_1 m where m.empno(+)=e.mgr and e.empno=:P3_EMPNO
You will find the following page items already created: P3_EMPNO, P3_EMP_NAME and P3_MGR_NAME.
Add 3 new page items P3_NO_OF_DAYS, P3_TRAVEL_COST and P3_TRAVEL_TYPE
Set the type for P3_TRAVEL_TYPE to Select List and define a list of static values as following:
In the Pre-Rendering section add a New Process above the Initialize form Apply For Travel and name it “Fetch Employee Details for User”. Set the type to Execute Code and add the following in the PL/SQL Code editor
select empno into :P3_EMPNO from emp_1 where emp_name=:APP_USER;
Navigate to the Processing Tab and Create a new Page Process of type Human Task – Create. Name the process “Submit Travel Request“.
Under the Settings, Click the Task Definition and select “Travel Request” from the drop down.
Set the Details Primary Key Item as P3_EMP_NO. This is how the task in your approvals component gets tied to the employee system of records.
Add a Success Message which will show up once the Request is submitted.
You will notice Travel Type , Request Id, Travel Cost, Travel Status, and No Of Days under the Parameters on the Left pane to be highlighted in RED. This is because, these were defined as required Parameters in the Travel Request Task Definition.
For No Of Days, Travel Cost and Travel Type, click on each parameter and set it to the corresponding page item (P3_NO_OF_DAYS , P3_TRAVEL_COST, P3_TRAVEL_TYPE) you had created earlier. Shown below is an example of how you set it for No Of Days.
For the Request Id parameter, set its Value to Null
For the Travel Status parameter, set it to a Static Value of PENDING
Click Save and navigate back to the Rendering Section. Create a new Button and name it Submit Request as shown below.
Go back to the Submit Travel Request Process and under server side conditions, select “When Button Pressed” –Submit Request.
Your Apply For Travel Page is now complete!
Creating the My Approvals and My Requests Pages
We now create the pages where a) The managers of the employee can go to approve or reject a travel request and b) The employee can go and check the status of his request. For this we will create two Unified Task Lists.
Creating the My Approvals Page
Click on the Create Page button and select Unified Task List from the Components section .
Name the Page “My Approvals” and select Report Context as “My Tasks“. This page will be used by the manager and line managers of the employee requesting travel approval to Approve or Reject the request.
Similarly create another Unified Task List, name it “My Travel Requests” and set the Report Context as “Initiated By Me“. This will be used by the requestor of the travel approval, to see the progress of his request.
Advanced
Customise the Task Details page to show the Travel Request Details
Open Page 2 Task Details and navigate to the Details region of the page. Open the source and you will find the following query.
select param_label,
param_value
from apex_task_parameters
where task_id = :P2_TASK_ID
and is_visible = 'Y';
The auto generated Details page bases the Details region on the task parameter values. But this is NOT set in stone and can be customised depending on the application we are building. And we will do exactly that!
Replace the above query with the following one:
select travel_type,no_of_days,estimated_cost,status,updated_by from emp_travel_request where req_id = (select param_value from apex_task_parameters where task_id= :P2_TASK_ID and param_static_id='REQ_ID') union select travel_type,no_of_days,estimated_cost,status,updated_by from emp_travel_request where req_id = :P2_TASK_ID;
Note: What is this query doing?
It is returning the details of the travel request from EMP_TRAVEL_REQUEST table where the request ID (REQ_ID) corresponds to the task id of the task created when the request was submitted.
Go to the Attributes tab and change the Template in the Appearance section from Value Attribute Pairs – Row to Value Attribute Pairs – Column.
Save the changes. This is how the Task Details page will look in the Page Designer now.
Customising the My Travel Requests page for the initiator’s view of the requests submitted
When the requestor of the travel submits the request, he or she will want to see the progress of his request(s) on the My Travel Requests Page.
For e.g. when John requests for travel, the approval task created is assigned to his manager Jane. When Jane approves this request, the approval task is complete. However the request is not complete and another task is created and assigned to Jane’s manager Matt for approving John’s request.
So, when John logs in at this point and opens the My Travel Requests page he will see 2 tasks. One task assigned to his manager Jane in Completed state and one task assigned to Jane’s manager Matt. Ideally, from John’s perspective it is one travel request so we might improve the experience for him and show him only the task that is the most current one for this particular request, which in this case is the one assigned to Matt.
For this we will customise the auto-generated Unified Task List page “My Travel Requests”
Open the My Travel Requests page and navigate to the Initiated by Me – Report Cards region. Check the source of the region and you will find the following query:
select * from table ( apex_approval.get_tasks ( p_context => 'INITIATED_BY_ME' ) )
apex_approval.get_tasks with the above context will return all tasks for which the logged in user is the initiator.
We would further filter this result to only return the most current task for a particular request id (the travel request id which got created when the travel request was submitted) for which the logged in user is the initiator.
Replace the above query with:
select *
from table ( apex_approval.get_tasks ( p_context => 'INITIATED_BY_ME' ) )
where task_id in
(
select max(task_id) from apex_task_parameters where param_static_id ='REQ_ID'
and param_value is not null group by param_value
union
select task_id from apex_task_parameters where param_static_id ='REQ_ID'
and param_value is null and task_id not in (select distinct param_value from
apex_task_parameters where param_static_id ='REQ_ID' and param_value is not null )
)
Click Save.
The page will now look like this :
Your application is now complete!
Running the Application
Run the Travel Request Approval Application by clicking on the Play button . You will be directed to a Login Screen. Let us log in as John.
From the Home page, navigate to the Apply For Travel page.
You will find the Emp No, Emp Name and Mgr Name fields pre-populated based on John’s employee record. Enter the following values for the remaining fields in the Travel Request form.
Click on Submit Request.
You will see a success message showing that the request was submitted.
Click on My Travel Requests to see the submitted request which was assigned to Jane :
Clicking on the task will open the Task Details page which looks like this
Log out and then Log back in as Jane. Navigate to the My Approvals page.
Click on the task and open the task details to Approve this task.
Log back in as John and navigate to the My Travel Requests page.
You will find that the request is now assigned to Matt. Open the task details. The status will still show as PENDING. The Updated By will show Jane as the last actor of this request.
Log in as Matt and navigate to My Approvals to approve this request.
The request will now go to Matt’s manager Sophie.
Log in as Sophie and navigate to My Approvals.
Open the task details.
You will find that the Updated By now shows JANE→MATT indicating who all have approved this request prior to her.
Approve this request in the same way as you did for Matt.
Log in as John. You will find that the task is in Completed state and was last assigned to Sophie. Notice that the intermediate tasks are not shown which makes it less confusing for John.
Open the task. You will find that the request status is now Approved. The updated by shows JANE->MATT→SOPHIE as the actors on the request.
Let us see what happens, when John submits a travel request with budget less than 50,000/- .
Navigate to Apply for Travel and submit the form with Travel Cost as 20,000/- , Travel Type as DOMESTIC and No. Of Days as 3.
Log in as Jane and navigate to My Approvals to approve this task.
Log back in as John and navigate to My Travel Requests. You will find that the task is Completed with Jane shown as the assignee. Open the task.
You will find that the request is Approved and Updated By Jane.
Since the travel cost was less than 50,000/- it was completed without requiring the next levels of approval.
What did you learn?
The above examples demonstrated how to use the Approvals Component to set up multi-level approvals which would be condition-dependent.
You learnt how to
a. Set up Actions in the Task Definition for Task Create and Task Complete (Approve and Reject) Events so that multi-level approvals can function.
b. Customise the Task Details page to show the Employee’s Travel Request data effectively.
c. Customise the Initiated By Me Unified Task List to filter out intermediate approval tasks.
Note:
Just like the task details region, the task history and comments can also be customised to show more relevant data that is specific to the particular application being developed.
The possibilities are endless!