How to Automate Business Process with Oracle APEX Workflows, Task Definitions, Push Notifications, ORDS and Data Redaction

Automating Business Processes:

A Case Study on Opening a Premium Bank Account

Introduction

In today's fast-paced digital landscape, automating business processes has become essential for organisations seeking to enhance efficiency, reduce manual errors, and improve customer satisfaction. This article will delve into the automation of a business process, specifically the opening of a new premium bank account.

Use Case: Opening a Premium Bank Account

The process of opening a premium bank account involves a series of steps, which can be automated to streamline the experience for both the applicant and the bank. The use case is as follows:

1. Application Submission: An individual interested in opening a premium bank account submits basic information, including:
2. Name and address
3. Work details
4. Bank reference (an existing account holder)
5. Income details
6. Unique identifications (e.g., photo, driving license, AADHAR, and PAN number in India)
7. House and work address
8. Document Upload: The applicant uploads supporting documents, such as a copy of their unique ID.
9. Application Review: The business process checks the uploaded content for validity. If the information is incomplete or inaccurate, or if the income is below the required threshold, the application is rejected.
10. Application Status Update: If the information is valid, the application status is updated from "Pending" to "Waiting for Approval."
11. Approval Process: Bank officials review the application and approve or reject it based on the submitted profile and any existing data on the applicant.
12. Account Opening and Notification: Upon successful completion of the application process, the applicant is notified and becomes a bank customer. They may be required to deposit an initial amount as part of the account opening process and will be provided with a debit or credit card in accordance with the bank's policies.

Benefits of Automation

Automating the business process of opening a premium bank account offers numerous benefits, including:

• Improved Efficiency: Automation reduces manual errors and streamlines the application process, resulting in faster processing times.
• Enhanced Customer Experience: Applicants can track the status of their application in real-time, reducing uncertainty and improving overall satisfaction.
• Increased Accuracy: Automated checks ensure that applications are complete and accurate, reducing the risk of errors and rejections.
• Reduced Costs: Automation minimizes the need for manual intervention, resulting in cost savings for the bank.

By automating the business process of opening a premium bank account, organizations can improve efficiency, enhance customer experience, and reduce costs. As technology continues to evolve, it is essential for businesses to leverage automation to stay competitive and meet the changing needs of their customers.

Table of Contents

1. Database Configuration
2. -> Create Database Objects
3. Mobile and Notification Services
4. -> Enable Mobile and Push Notification Services
5. Task Management
6. -> Create Task Definitions
7. -> Test Task Definitions
8. Application Initialization
9. -> 4.1 Initial Decision-Making of Application
10. Service Integration
11. -> ORDS and Service Invocation
12. Workflow Management
13. -> Business Process Workflow
14. -> Test Business Process Workflow
15. End-to-End Demonstration
16. -> End-to-End Demo Flow
17. Create Workflow Administrative Console
18. Data Security
19. -> Data Protection through Oracle Data Redaction
20. Create Oracle APEX Users, Groups and Assign Users to Groups

01. Database Configuration

Initializing Customer Application Data

To commence the process, we establish two fundamental tables: the Customer Application Table and the Card Information Table. The Customer Application Table serves as a centralized repository for storing comprehensive application data, while the Card Information Table is designed to store newly generated credit or debit card information.

Download Scripts

02. Mobile App and Push Notification Services

Progressive Web Apps

Oracle APEX provides developers with the ability to create Progressive Web Apps (PWAs) that can be easily installed on any desktop or mobile device, offering users a more native app experience. This application serves as a useful reference for developers looking to incorporate key PWA features into their own applications, helping them to create more engaging and user-friendly apps.

Enable Oracle APEX Push Notifications Service

In the Application Builder, Select the Application and click on Edit Application Definition

Click on Progressive Web App, Enable Progressive Web App and Installable

Enable Push Notifications, Generate Credentials

Enable Push Notifications in Device

Install the application on Mobile or Desktop Device using Install icon on the application. in the settings enable push notifications.

On iOS or Android devices you would need to enable notifications in settings as well.

View of push notification in a mobile device

View of push notification in a wearable device (Watch)

03. Task Management

Task Definitions within Business Processes

Task definitions can be viewed as integral components of a broader workflow or business process. In the context of customer onboarding for bank account creation, a single business process may encompass multiple task definitions, each with its own set of actions and automation.

Importance of Task-Level Testing

Given the complexity and interdependence of these tasks, it is essential to conduct thorough testing of each task individually before proceeding to end-to-end workflow testing. This approach ensures that each task functions as intended, reducing the likelihood of errors and inefficiencies in the overall process.

Multi-Level Approval Processes

A practical illustration of this concept can be seen in business processes requiring multiple levels of approval. For instance, in the customer onboarding process, a tiered approval system may be implemented, comprising:

1. Initial Verification: Review of documentation to ensure completeness and accuracy.
2. Background Check: A secondary level of verification to assess the applicant's credibility.
3. Final Approval: The manager reviews recommendations from previous approvers, making an informed decision to approve or reject the application.

By breaking down complex business processes into discrete tasks and testing each component rigorously, organizations can ensure seamless execution and optimize overall efficiency.

Creating Tasks

Login to Oracle APEX workspace, access your application and click on Shared Components

Click on Task Definitions

Provide Task Name and Static ID

Create Task Definition

Provide Task Settings with a subject line, we can also auto-generate Task Details Page

-- Actions SQL 
select CUSTOMER_NAME, ACCOUNT_NUMBER
FROM BANK_CUSTOMERS WHERE ID = :APEX$TASK_PK        

Provide the required input parameters for this Task

Add Task Participants

In this case, we are setting task participants based on a static value, but it can also be based on an SQL query, expression, or PL/SQL function.

In the above screen we have set Process Owner is set as BANKER, its important to know that this is Group of users, to know how to create users and groups, please see "How to create Users and Groups section" of this article

Task Action Configuration

The Task Action step is a critical component of the workflow, allowing for the definition of multiple actions that dictate the outcome of the task. Specifically, two key actions can be configured:

• On Approval: Upon approval of the task, the outcome is automatically set to "Approved," triggering the execution of a designated code block.
• On Rejection: Conversely, if the task is rejected, the outcome is updated to "Rejected," initiating the execution of a separate, predefined code block.

This configurable task action framework enables seamless integration with downstream processes, ensuring a streamlined and automated workflow.

On Approve and On Reject, execute SQL code.

-- On Approval
update BANK_CUSTOMERS set ACCOUNT_STATUS = 'Approved' WHERE ID = :APEX$TASK_PK;

-- On Rejection
update BANK_CUSTOMERS set ACCOUNT_STATUS = 'Rejected' WHERE ID = :APEX$TASK_PK;        

Save the Task. We can test the Task as shown below.

Testing Tasks with Oracle APEX Page

Create Process as shown below

Add Task Details of Type = Human Task - Create, Select the Task Definition we have just created, provide the required parameters from APEX page items, and finally invoke the process through Dynamic Action of a Submit button.

Testing Tasks with PL/SQL

We can also create Dynamic Action by clicking a button and running the following SQL.

-- Example 1 --

l_task_id := apex_approval.create_task(
                 p_application_id => 114,
                 p_task_def_static_id => 'NEW_CUSTOMER_ONBOARDING',
                 p_subject => 'New Customer Approval for Customer ' || l_bank_customer || ' Account No: ' || l_account_number,
                 p_initiator => l_user,
                 p_parameters => apex_approval.t_task_parameters(
                     1 => apex_approval.t_task_parameter(static_id => 'P_ACCOUNT_NUMBER', string_value => l_account_number),
                     2 => apex_approval.t_task_parameter(static_id => 'P_CUSTOMER_NAME', string_value => l_bank_customer)),
                 p_detail_pk => o_cust_id); 

-- Example 2 --
 
    l_task_id := apex_approval.create_task(
                 p_application_id => 114,
                 p_task_def_static_id => 'REQAPPROVALS',
                 p_subject => 'Requisition ' || l_req_id || ': ' || l_req_item || ' for ' || l_req_amount,
                 p_initiator => :emp_uid,
                 p_parameters => apex_approval.t_task_parameters(
                 1 => apex_approval.t_task_parameter(static_id => 'REQ_DATE', string_value => sysdate),
                 2 => apex_approval.t_task_parameter(static_id => 'REQ_AMOUNT', string_value => l_req_amount),
                 3 => apex_approval.t_task_parameter(static_id => 'REQ_ITEM', string_value => l_req_item),
                 4 => apex_approval.t_task_parameter(static_id => 'REQ_ID', string_value => l_req_id)),
                 p_detail_pk => l_req_id);
         

Download Create Task PL/SQL || Documentation Reference

04. Application Initialization

Create Oracle APEX Application Form

Create Oracle APEX Page, Create form to collect the form data as required to fill in BANK_CUSTOMERS table, you can alternatively give a different name such as BANK_CUSTOMER_APPLICATIONS later move the applications record to customer table after final approval

Save Application Data in a Table

Application Submission and Data Storage

Upon completion of the application, the submitted information is persisted in the Customer Application Table. At this juncture, the application status is initialized as "Initiated," and the account type is designated as a savings bank account. The primary key associated with the application is returned, facilitating seamless tracking and management of the application throughout its lifecycle.

Create a Dynamic Action to save form data into table.

We would not only like to save the application data in a database table but also we want to programmatically Invoke our Workflow

Download Save Application and Invoke Workflow - PL/SQL

05. Initial Decision-Making of Application

Application Review and Approval Process

In our use case scenario, the designated application approver receives a notification on their device, prompting them to initiate the review process. Upon accessing the mobile app, the approver verifies the applicant's details and examines any existing customer data available, leveraging the provided information to inform their assessment.

Task Claiming and Application Review

The banker assumes the role of task reviewer or application reviewer, empowered to claim the task based on the inputs provided. In a real-world business context, multiple application reviewers may be involved, ensuring a robust and collaborative evaluation process. Once the task is claimed, the reviewer can approve or reject the application, effectively determining the outcome of the premium bank account opening request.

Workflow Management

Configuring Input Parameters for Application Processing

To facilitate efficient application processing, it is essential to establish the required input parameters. These parameters, integral to the application itself, enable the system to determine whether an application can be automatically rejected or requires review by a bank official.

Automated Rejection Criteria

Specifically, the input parameters can be configured to trigger outright rejection or request updates and resubmission if the applicant fails to provide:

• Mandatory documentation, such as proof of income or employment details
• Accurate and valid unique identification information

By defining these parameters, financial institutions can streamline the application review process, reduce manual intervention, and provide a more efficient and user-friendly experience for applicants.

Create Workflow

From Shared Components, under Workflows and Task Definitions, Click on Workflow

Create Initial Workflow would look like this with one path for TRUE and another path for FALSE case and test this to check if we are able to invoke the Workflow.

Provide Required Workflow Input parameters.

There can be many pre-screening decisions, in our case we will start with Is Review Needed? where we will use an expression that the applicant has the minimum required income to open this account; those failing this criterion will be automatically rejected. For others, this will be routed to the review process.

A. Create Decision Task (Switch Task)

The corresponding function body is as shown, where : P_INCOME_LEVEL is passed as the input parameter

IF :P_INCOME_LEVEL >= 1000 THEN 
  RETURN TRUE; 
ELSE 
  RETURN FALSE; 
END IF;         

B. Auto Rejection Flow (Invoke API Activity)

if Application is Rejected then update application status

Update Rejection Status is configured as shown

Pass the required input parameters as shown below

For this we would need a Procedure or a Function as shown in the code below

Download Status Update Procedure

C. Send Push Notification to Application Reviewer (and/or Email)

Add Send Notification and/or Send Email Task, where we will notify the reviewer about this application.

Activity details is as show below

D. Update Status to Pending Review (Invoke API)

This Step D. is similar to Step B; change the status input parameter value to "Pending Review."

E. Add Human Reviewer ( Human Task Create Activity)

We want the application to be reviewed by a human reviewer, who can approve or reject the application. This is where we will invoke the customer onboarding task, which was developed in Task 03 (03. Task Management)

Task Activity

V_TASK_OUTCOME, V_TASK_ITEM_ID and APPROVER are user-defined variables, please do not get confused with input parameters like P_CUSTOMER_ID

Add Human Review Activity and pass the required parameters on to our human reviewer task.

This will create a Task for the Human Reviewer, in our case, the Banker responsible for reviewing the customer's application. The output of Approval or Rejection is stored in a variable V_TASK_OUTCOME.

06. Task Notifications and Approval on Mobile Device

At this stage, if we check the App on a mobile device, as a Banker, we will get a Task Notification

Let us click on the Notification Link and check the Tasks in the workflow.

Click on the Application Link and View the task details

Claim the task Approve or Reject based on input available.

07. Closing the process with Card Issue and Notifications

At this stage, we are at the end of our business process, but we might want to update the application status and issue a debit card to the applicant.

Service: Approved and Service: Rejected: Basically update the record status fields based on the Outcome of the human task reviewer.

So, let's add Is Approved? (Switch Activity) after Human Review

Is Approved? (Switch Activity)

If the reviewer has Approved the Task, we will update application status with approved flag (Service: Approved) and Issue a Card (another service which we will talk about in the next section).

Create a Connector "Has Approved" based on condition "Is Equal To" = "APPROVED" value

If the application is rejected, we will move the flow to the Rejection lane below

Create a Connector "Has Rejected" based on condition "Is Equal To" = "REJECTED" value

08. Issue Card ORDS Service Integration

In our use case we wanted to issue a debit card such as master or visa, once the application has been successfully approved, let us also generate a credit card number.

Click on SQL Workshop > RESTful Services.

We will need some PL/SQL to randomly generate a card and issue it to our new customer.

Download the PL/SQL from here

Now that we have our REST URL, we need to create a REST Data Source

Under Shared Components, click on REST Data Sources and Create REST Data Source

Define REST Service URL and Operations (POST is required in our case)

Input Parameters for our REST Service

09. Invoke ORDS as Service API in our Workflow

Drag and Drop the Invoke API task on the workflow canvas, Select Type REST Source and Select our Data Source.

This will automatically create a list of required parameters map to process input variables.

10. Complete Workflow

11. Start to End Demonstration

12. Create Workflow Administrative Console

To View Task Details as an Administrator, create Workflow Console page

Review the flow as shown below by clicking on task

13. Troubleshooting Tips

Sometimes, you might not know if the Workflow or Task has faulted, whether it is in the Active state, or whether it has been Completed. Some of the SQL queries below help you determine the task and workflow status.

Workflow Status Check

SELECT w.workflow_id,
       w.detail_pk,
       t.task_id,
       t.initiator
FROM   apex_workflows w,
       apex_workflow_variables v,
       apex_tasks T
WHERE   w.application_id = :APP_ID order by w.WORKFLOW_ID desc;

SELECT * from apex_workflows w order by WORKFLOW_ID desc;

SELECT * from apex_tasks  order by TASK_ID desc;        

Programatically testing Workflow using PL/SQL

--- Invoke Workflow -----------
--- Replace variables -----------

   l_workflow_id := 
    apex_workflow.start_workflow 
      (p_static_id      => 'MYBANKWF',
       p_detail_pk      => o_cust_id,
       p_initiator      => UPPER(l_user),
       p_parameters     => apex_workflow.t_workflow_parameters
                            (1 => apex_workflow.t_workflow_parameter(static_id => 'P_ACCOUNT_NUMBER', string_value => l_account_number),
                             2 => apex_workflow.t_workflow_parameter(static_id => 'P_CUSTOMER_ID', string_value => o_cust_id),
                             3 => apex_workflow.t_workflow_parameter(static_id => 'P_CUSTOMER_NAME', string_value => l_bank_customer),
                             4 => apex_workflow.t_workflow_parameter(static_id => 'P_INCOME_LEVEL', string_value => l_income_level)),
       p_debug_level => apex_debug.c_log_level_info);        

Unable to Invoke Workflow? Activate Workflow

When the Workflow is in development mode, you would need to be logged in to workspace application, once Workflow has been tested end to end - Activate the Workflow.

14. Data Redaction

Oracle Data Redaction is a feature that allows you to mask sensitive data in real-time, making it unreadable to unauthorized users. It provides a way to protect sensitive data, such as credit card numbers, social security numbers, or other confidential information, by redacting or masking it.

Benefits of Oracle Data Redaction

1. Real-time data masking: Redacts sensitive data in real-time, making it unreadable to unauthorized users.
2. Compliance: Helps organizations comply with regulatory requirements, such as PCI-DSS, HIPAA, and GDPR.
3. Data protection: Protects sensitive data from unauthorized access, reducing the risk of data breaches.

Types of Oracle Data Redaction

1. Full Redaction: Replaces the entire sensitive data value with a fixed string.
2. Partial Redaction: Masks a portion of the sensitive data value, leaving some characters visible.
3. Random Redaction: Replaces the sensitive data value with a random value.
4. Regular Expression Redaction: Uses regular expressions to mask sensitive data values.

Implementing Oracle Data Redaction

Step 1: Create a Redaction Policy

-- Copy the CC_FD table 

create table cc_fd_secure as select * from cc_fd where 1=0;

-- Insert few records, including few random credit card numbers

select * from cc_fd_secure

-- this will display credit card numbers as plane text unmasked as any other columns in that table.        

Let us now Mask the credit card column cc_no in our schema docuser

-- login as ADMIN user and run this 

BEGIN
   DBMS_REDACT.ADD_POLICY(
     object_schema        => 'DOCUSER',
     object_name          => 'CC_FD_SECURE',
     column_name          => 'CC_NO',
     policy_name          => 'mask_cc_nums',
     function_type        => DBMS_REDACT.PARTIAL,
     function_parameters  => '7,1,5',
     expression           => '1=1');
END;        

Insert few records

Now run the SQL, we will see masked data for cc_no (credit card number) column, we can use SQL Commands of Oracle APEX or SQL Worksheets to test this.

Read more about DBMS_REDACT

15. How to create Oracle APEX Users and Groups

Click on Application user in the logged in Oracle APEX workspace

Click on create user button and provide user details

Once user has been created add user to group

Similarly Groups can also be created

ORDS CRUD Operations (Introduction video)

- Optional learning for ORDS beginners

Check us out here!

Oracle LiveLabs

Oracle Database 23ai Free

Oracle Cloud Free Tier

Oracle Developer Center

Thanks for Reading, Liking and Sharing

Regards, - Madhusudhan Rao