Building a Leave Request Workflow Using Flows for APEX in Oracle APEX – Step-by-Step Guide
Introduction
Flows for APEX is a powerful open-source BPMN workflow engine built on top of Oracle APEX. It allows developers and business analysts to model business processes graphically using BPMN 2.0 notation and turn them into executable workflow applications.
In this tutorial, we will build a complete Leave Request Approval System from scratch using Flows for APEX and Oracle APEX. By the end of this guide, you will have a fully working system where:
Employees can submit leave requests via a form
- Managers receive tasks in their inbox to approve or reject
- Automated emails are sent based on the decision
- Employees can track their request status in real time
- A visual BPMN flow diagram shows the current stage
Prerequisites
Before starting, ensure you have:
- Oracle APEX 23.1 or later
- Flows for APEX installed
- Basic knowledge of APEX and PL/SQL
- SMTP configured (for email notifications)
Part 1: Understanding the Flow Diagram
Flow Components
Component | Description |
Start Event | Triggers when employee submits a leave request |
Submit Request | User Task — Manager reviews and decides (APEX Page) |
APPROVED Gateway | Exclusive Gateway — routes based on APPROVAL_STATUS variable |
Notify (Yes path) | Script Task — automatically sends approval email |
Reject (No path) | Script Task — automatically sends rejection email |
End Event | Flow completes |
Part 2: Setting Up the BPMN Diagram
Step 1: Create the Flow Model
- Open Flows for APEX → Flow Management
- Click Create Model
- Enter:
- Name:
My_first_task - Version: 4
- Name:
- Click Create
Step 2: Design the BPMN Diagram
- Add Start Event
- Add User Task → Submit Request
- Add Exclusive Gateway → APPROVED
- Add two tasks:
- Notify (Script task for email notification)
- Reject (Rejection)
- Add End Event
- Connect all components
👉 Flow:
Start → Submit Request (User task) → Gateway (Approve/Reject) → Notify (Script task for email) → End
Step 3: Configure User Task
- Task Type: APEX Page
- Enable: Use APEX Metadata
- Select your Application & Page
- Click Generate Default Items - auto-fills PROCESS_ID, SUBFLOW_ID, STEP_KEY
Step 4: Configure Gateway Logic
Click the Yes arrow from the APPROVED gateway:
Property | Value |
Condition Type | Function Body |
Code | See PL/SQL below |
Yes arrow condition (Function Body — no DECLARE keyword):
l_prcs_id NUMBER;
l_status VARCHAR2(100);
BEGIN
l_prcs_id := flow_plsql_runner_pkg.get_current_prcs_id;
SELECT prov_vc2_value INTO l_status
FROM flow_process_variables
WHERE prov_prcs_id = l_prcs_id
AND prov_var_name = 'APPROVAL_STATUS'
AND ROWNUM = 1;
RETURN l_status = 'APPROVED';
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN FALSE;
END;⚠️ NEVER use :PROCESS_ID or any APEX bind variables (:) in gateway conditions — the gateway runs outside APEX session context!
Step 5: Configure Notify as Script Tasks (Email)
- Click Notify task → Wrench icon → Script Task
- In Script tab, add PL/SQL code to send approval email
- Use flow_plsql_runner_pkg.get_current_prcs_id to get process ID
- Use flow_process_vars.get_var_vc2() to retrieve stored variables
- Use apex_mail.send() to send the email
- Key code pattern for Script Tasks:
l_to_email := flow_process_vars.get_var_vc2(
pi_prcs_id => l_process_id,
pi_var_name => 'EMPLOYEE_EMAIL'
);
Step 6: Release the Flow
- Click Apply Changes
- Click Release
⚠️ Only Draft versions can be edited. Released versions require creating a New Version to modify.
Step 7: Create APEX Application
- Create new app: Leave Request System
Step 8: Create Application Items
Create:
- EMPLOYEE_NAME
- PROCESS_ID
- SUBFLOW_ID
- STEP_KEY
Step 9: Install Flows Plugins
Copy plugins:
Flows for APEX - Manage Flow Instance
Flows for APEX - Manage Flow Instance Step
Flows for APEX - Manage Flow Instance Variables
Flows for APEX - Modeler
Flows for APEX - Viewer
Step 10: Create Database Table
CREATE TABLE leave_requests (
id NUMBER GENERATED ALWAYS AS IDENTITY,
employee_name VARCHAR2(100),
employee_email VARCHAR2(100),
leave_type VARCHAR2(50),
start_date DATE,
end_date DATE,
reason VARCHAR2(500),
status VARCHAR2(20),
process_id NUMBER,
subflow_id NUMBER,
step_key VARCHAR2(100)
);
Step 11: Create Leave Request Page
Create form
Name : Leave Request Form
Type : Form
Source :
Type: Table/View
Table Name : LEAVE_REQUESTS
- Employee Name
- Employee Email
- Leave Type
- Start Date
- End Date
- Reason
Create Submit Leave Request Button.
Step 12: Start Flow from APEX
l_process_id := flow_api_pkg.flow_create(
pi_dgrm_name => 'My_first_task',
pi_prcs_name => 'Leave_' || :P1_EMPLOYEE_NAME
);
flow_api_pkg.flow_start(p_process_id => l_process_id);
Create a process in form page and paste below PL/SQL code:
Process Name: Start Leave Approval Flow
DECLARE
l_process_id NUMBER;
l_subflow_id NUMBER;
l_step_key VARCHAR2(100);
l_request_id NUMBER;
l_start_date DATE;
l_end_date DATE;
BEGIN
-- Safe date conversion
l_start_date := TO_DATE(:P1_START_DATE, 'YYYY-MM-DD');
l_end_date := TO_DATE(:P1_END_DATE, 'YYYY-MM-DD');
-- Insert into table
INSERT INTO leave_requests (
employee_name,
employee_email,
leave_type,
start_date,
end_date,
reason,
status,
created_by,
submitted_on
) VALUES (
:P1_EMPLOYEE_NAME,
:P1_EMPLOYEE_EMAIL,
:P1_LEAVE_TYPE,
l_start_date,
l_end_date,
:P1_REASON,
'PENDING',
:APP_USER,
SYSDATE
) RETURNING id INTO l_request_id;
-- Create Flow Instance
l_process_id := flow_api_pkg.flow_create(
pi_dgrm_name => 'My_first_task',
pi_prcs_name => 'Leave_' || :P1_EMPLOYEE_NAME
);
-- Save process variables
flow_process_vars.set_var(
pi_prcs_id => l_process_id,
pi_var_name => 'EMPLOYEE_NAME',
pi_vc2_value => :P1_EMPLOYEE_NAME
);
flow_process_vars.set_var(
pi_prcs_id => l_process_id,
pi_var_name => 'EMPLOYEE_EMAIL',
pi_vc2_value => :P1_EMPLOYEE_EMAIL
);
flow_process_vars.set_var(
pi_prcs_id => l_process_id,
pi_var_name => 'LEAVE_TYPE',
pi_vc2_value => :P1_LEAVE_TYPE
);
flow_process_vars.set_var(
pi_prcs_id => l_process_id,
pi_var_name => 'START_DATE',
pi_vc2_value => TO_CHAR(l_start_date, 'DD-Mon-YYYY')
);
flow_process_vars.set_var(
pi_prcs_id => l_process_id,
pi_var_name => 'END_DATE',
pi_vc2_value => TO_CHAR(l_end_date, 'DD-Mon-YYYY')
);
flow_process_vars.set_var(
pi_prcs_id => l_process_id,
pi_var_name => 'REASON',
pi_vc2_value => :P1_REASON
);
flow_process_vars.set_var(
pi_prcs_id => l_process_id,
pi_var_name => 'REQUEST_ID',
pi_vc2_value => TO_CHAR(l_request_id)
);
-- Start the Flow
flow_api_pkg.flow_start(
p_process_id => l_process_id
);
-- Get subflow ID AND step key after start
SELECT sbfl_id,
sbfl_step_key
INTO l_subflow_id,
l_step_key
FROM flow_subflows
WHERE sbfl_prcs_id = l_process_id
AND ROWNUM = 1;
-- Update table with all IDs
UPDATE leave_requests
SET
process_id = l_process_id,
subflow_id = l_subflow_id,
step_key = l_step_key
WHERE id = l_request_id;
-- Save to Application Items
:PROCESS_ID := l_process_id;
:SUBFLOW_ID := l_subflow_id;
:STEP_KEY := l_step_key;
COMMIT;
apex_application.g_print_success_message :=
'Leave request submitted successfully!';
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
apex_error.add_error(
p_message => 'Error: ' || SQLERRM,
p_display_location => apex_error.c_inline_in_notification
);
END;
Step 13: Create Task Inbox Page
👉 Create Page → Report
Use below query:
SELECT
t.sbfl_id,
t.sbfl_prcs_id,
t.sbfl_process_name AS process_name,
t.sbfl_current_name AS current_task,
t.sbfl_status AS status,
t.sbfl_step_key,
t.sbfl_became_current AS task_started,
t.sbfl_due_on,
t.sbfl_priority,
lr.employee_name,
lr.leave_type,
lr.start_date,
lr.end_date,
lr.reason,
lr.submitted_on
FROM flow_task_inbox_vw t
LEFT JOIN leave_requests lr
ON lr.process_id = t.sbfl_prcs_id
WHERE t.sbfl_dgrm_name = 'My_first_task'
AND t.sbfl_status = 'running';
👉 Next, Add Link in Current_Task column
- Give Page number for Approval page number
- Pass:
- PROCESS_ID
- SUBFLOW_ID
- STEP_KEY
Step 14: Create My Leave Request Page
👉 Create Page → Report
Use below query:
SELECT
lr.id,
lr.process_id,
lr.subflow_id,
lr.employee_name,
lr.leave_type,
TO_CHAR(lr.start_date, 'DD-Mon-YYYY') AS start_date,
TO_CHAR(lr.end_date, 'DD-Mon-YYYY') AS end_date,
lr.reason,
CASE lr.status
WHEN 'PENDING' THEN '<span style="color:orange;
font-weight:bold">⏳ Pending</span>'
WHEN 'APPROVED' THEN '<span style="color:green;
font-weight:bold">✅ Approved</span>'
WHEN 'REJECTED' THEN '<span style="color:red;
font-weight:bold">❌ Rejected</span>'
END AS status,
lr.comments AS manager_comments,
lr.approved_by,
TO_CHAR(lr.approved_on, 'DD-Mon-YYYY') AS approved_on,
TO_CHAR(lr.submitted_on, 'DD-Mon-YYYY') AS submitted_on
FROM leave_requests lr
WHERE UPPER(lr.created_by) = UPPER(:APP_USER)
ORDER BY lr.submitted_on DESC;
Step 15: Create Flow Diagram Page
Create two-page items: P6_PROCESS_ID and P6_SUBFLOW_ID, and set both items as Hidden.
Create a region and set the Type to:
Flows for APEX – Viewer
Set the Source Type to:
SQL Query
Use the following query:
SELECT
dgrm_content,
prcs_id,
all_completed,
all_errors,
all_current,
dgrm_id,
prdg_id,
prdg_prdg_id,
breadcrumb,
drilldown_allowed
FROM flow_p0010_vw
WHERE prcs_id = :P6_PROCESS_ID;
And add below code in Execute When Page Loads:
// Refresh viewer when process ID changes
apex.item('P6_PROCESS_ID').setValue(
apex.item('P6_PROCESS_ID').getValue()
);
Below Screenshot for reference to see flow diagrams for some requests:
Conclusion:
This implementation shows how to build a complete approval workflow using Oracle APEX and Flows
for APEX. It enables automated approvals, email notifications, and audit tracking in a structured way.
Each stage is clearly controlled, ensuring better visibility and accountability. The solution is scalable and
can be reused for various business processes.
Comments
Post a Comment