Oracle Apex & Camunda BPM
How to use Camunda "workflow" in Oracle Apex application?
The vast majority of business applications have some sort of "wokflow". In every business, a group of people do their job to "deliver value". But we don't do everything together and we break the task into activities because we specialize and carry out tasks according to skills - role. We do our job and pass it on to the next person - like an assembly line. These activities are performed as part of a process that works according to: template - "wokflow". It has its stages, tasks and statuses.
Oracle Apex in the latest version has a built-in workflow building tool. If the workflow is only used for the needs of the application we are building, this approach may make sense. However, if our workflow is complex, changes frequently or the process is supported by different systems, it is a much better idea to use a dedicated system - a BPM platform.
Using a platform also makes great sense to manage change. We all know how "business" changes assumptions about applications - it starts with "something small" and evolves into quite a large, complex system. So the workflow that we use, e.g. for accepting invoices from one level, expands to several levels, new business conditions and exceptions are created (e.g. the flow depends on the type of invoice, the invoice amount, the supplier/customer category).
The BPM platform will simply handle processes better and give us more opportunities. Just imagine onboarding process where several actors and applications are involved:
Required components
The system that will allow us to build a workflow for Oracle Apex application without much effort is Camunda. Camunda not only allows you to build a flow diagram in BPM notation, but is also delivers a tool for creating decision models; business conditions (DMN). Camunda has two systems - Modeler for creating diagrams and Platform for running processes. Components were written in Java as open source model under the terms of the Apache license.
When building an application that requires workflow, we separate the management of information flow from the application itself. The application "has no idea" how the business logic of information flow is organized - it passes the acceptance task to Camunda, and the BPM platform takes care of the appropriate decision. Camunda itself does not do anything in the Oracle database, it does not provide forms, it does not manage data - it only deals with providing information. It is the external system/people who take specific action based on them.
In a nutshell; the flow of information is handled by the BPM platform and the role of the application is only to read the state of the process and provide information to the process that task was copleted.
We build a workflow - let's create a process definition first
In our example, we will build a workflow that accepts or rejects invoice costs. This is a simple process that will allow us to quickly understand how to integrate Oracle Apex with Camunda.
We create a process diagram in Camunda Modeler, which we download from the Camunda site. No time to explain the program menu and the BPMN notation itself here. Let us just say that whatever you do, whatever position you work in the company, you should know at least the basics of BPM. Our sample process starts with the DMN task, but you can skip it in the first step of integrating Oracle Apex with Camunda.
Our diagram consists of two tasks. Depending on the invoice amount or department, invoices may only be accepted by one person (level 1) or second acceptance is required (level 2).
Tasks "Acceptance level 1" and "Acceptance level 2"
This is a manual task that handles the first level of invoice acceptance. Each task has its own ID (we will use the ID to communicate with the task) and a name, which is not obligatory - it is more for you to orient yourself in the process.
In the "Forms" tab, enter the name of the form in the "Form Key" field. Let's say: "10". This will inform Apex which page is responsible for completing this task. You'll see the details further down. Yes, task "Acceptance level 2" differs only in the ID and name of the form ("Form Key").
Process diagram expressions
The flow to a gateway (gateways are often mistakenly called "decision points") does not have any expression, and may keep its default ID. However, flows from the gateway (in this case of the "Exclusive gateway" type, therefore "X") have expressions assigned. An expression can be written in two ways: #{expression} or ${expression}. Enter ${!approved} (or ${not approved} for a flow that will terminate the activity and ${approved && level == 2} for a flow that will redirect the activity to second-level approval.
As you can see our process will require two variables; 'approved' and 'level'.
IMPORTANT: Diagrams in Camunda DO NOT HAVE variables declared before they are executed. You send variables when you start the process or later, e.g. when confirming the execution of a task ("task complete"). This may seem strange, but it turns out that this makes process diagrams extremely flexible.
Coming back to our variables; "approved" will have two statuses; false (approval rejected) or true (accepted). Variable "level" will have the value 1 (the invoice is accepted by only one level) or 2. The expression for the "Approved" flow from gateway to "End" is ${approved && level == 1}. The expressions for "Approval level 2" are ${approved} for the "Accepted" flow and ${!approved} for the "Not approved" flow.
Sending the process definition to the Camunda server
You can send the process definition to the Camunda server [download and run the BPM Platform for Tomcat from Camunda site] directly from Camunda Modeler or use the REST service built into the Camunda platform. If you are doing it from Modeler, select the "Deploy current diagram" menu [leave the default values in the "Deploy Diagram to Camunda Platform" window. If you want to use REST, use this Postman target [http://localhost:8080/engine-rest/deployment/create]
Oracle Apex Application
We won't describe here how to write an invoice management application; how these invoices got there or what the pages presenting the list of these invoices look like. Imagine that you have an invoice in the application that you now want to for approval. The accountant or user clicks on the "Submit invoice for approval" button, thus triggering the PL/SQL code:
declare
l_date varchar2(35);
v_url varchar2(1000);
l_rowid number:= :P300_ID;
l_invoice_amount number := :P300_AMOUNT;
l_departament varchar2(80) := :P300_DEPARTMENT;
l_invoiceno varchar2(80) := :P300_INVOICENO;
v_body clob :=
'{
"variables": {
"dept" : {
"value" : "'||l_departament||'",
"type": "String"
},
"kwota" : {
"value" : '||l_invoice_amount||',
"type": "Double"
},
"nr_faktury" : {
"value" : "'||l_invoiceno||'",
"type": "String"
},
"row_id" : {
"value" : "'||l_rowid||'",
"type": "Double"
}
}
}';
l_response clob;
begin
-- clear headers of previous sessions
apex_web_service.g_request_headers.delete;
apex_web_service.g_request_headers(1).name := 'Content-type';
apex_web_service.g_request_headers(1).value := 'application/json';
v_url := 'http://127.0.0.1:8080/engine-rest/process-definition/key/invoice-approval-test-v5/start';
l_response := apex_web_service.make_rest_request(p_url => v_url, p_http_method => 'POST', p_body => v_body);
if apex_web_service.g_status_code = 200 then
UPDATE TEST_BPMN_INVOICES SET status = 'approval level 1', approved = 0 WHERE id = l_rowid;
else
--The call failed, inspect the response and fix the code :-)
--Select from dual is just to run anything...
SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') into l_date FROM dual;
end if;
end;
What does this code do? We start a process on Camunda Platform with the variables specified in "v_body". Oracle will prevent connection to an external host by default - before executing this code, grant appropriate permissions in the Oracle database. The process will be started and the first task, "Approval level 1", will be given a task to perform. The above code does not contain the 'level' variable sent when initiating the process. This variable informs the process whether the activity should be handled by two acceptance levels or just one. For simplicity, we have disabled the "invoice classification" task that adds such a variable. So add the "level" variable to the code (e.g. assigning it the value "1").
You can check the process activity status by logging in to the Camunda platform and then entering the "cockpit" menu and selecting our process. The activities of each task are illustrated with numbers in circles. OK, so Camunda has been given a job to do - it's time to get on with it.
Building a tasklist
In the most common scenario, Camunda does not initialize any tasks - it does not run tasks on external systems. It only deals with information management - it ensures that information reaches the appropriate tasks in the BPM process. External systems (such as Oracle Apex) connect to Camunda, build tasklists and mark activities as completed (execute the "task complete" command). In our application, Oracle Apex will build tasklists - it will download the list of tasks to be performed and display this list in the application for the appropriate users.
So, what is a 'tasklist'? In the process we have tasks to be completed. Every task can be assigned to either a group of people, or a specific individual (Camunda has information and you need to display this information in a form where the application user will read and process it.). When building a custom tasklist/application, you must plan for the following aspects. You will need to
- Query BPM platform for user tasks and generate lists of those tasks.
- Filter the list along specific attributes like current assignee, candidate groups, etc.
- Select and display the right forms for starting processes and completing tasks.
- Use custom/business value data in order to filter with those values and display them correlated with the task list and within forms.
- Authorize users to access those lists, filters, and forms.
Read more about 'tasklists' when working with Camunda. Ok, let's download everything that is waiting in the "Approval level 1" task.
DECLARE
v_url VARCHAR(200);
t_url VARCHAR(200);
sJsonIndex APEX_JSON.t_values;
v_clob CLOB;
l_response CLOB;
t_response CLOB;
v_body CLOB;
t_body CLOB;
l_num_items NUMBER;
l_task_id VARCHAR2(80);
l_taskdefinitionkey VARCHAR2(80);
l_formkey VARCHAR2(5);
t_amount NUMBER(8);
t_rowid NUMBER(8);
t_dept VARCHAR2(80);
t_invoiceno VARCHAR2(80);
t_level VARCHAR2(5);
BEGIN
l_response := '';
-- clear previus session headers
apex_web_service.g_request_headers.delete;
apex_web_service.g_request_headers(1).name := 'Content-type';
apex_web_service.g_request_headers(1).value := 'application/json';
v_url := 'http://127.0.0.1:8080/engine-rest/task?taskDefinitionKey=approve_l1';
l_response := apex_web_service.make_rest_request(p_url => v_url, p_http_method => 'GET', p_body => v_body);
if apex_web_service.g_status_code = 200 then
apex_json.parse(sJsonIndex, l_response);
l_num_items := APEX_JSON.get_count(p_path => '.' , p_values => sJsonIndex);
IF l_num_items > 0 THEN
FOR i in 1 .. l_num_items LOOP
l_task_id := apex_json.get_varchar2(p_path => '[%d].id', p0 => i, p_values => sJsonIndex);
l_formkey := apex_json.get_varchar2(p_path => '[%d].formKey', p0 => i, p_values => sJsonIndex);
l_taskdefinitionkey := apex_json.get_varchar2(p_path => '[%d].taskDefinitionKey', p0 => i, p_values => sJsonIndex);
-- Using the ID of this task, get the REST variables and then update the invoice table
t_response := '';
-- clear previus session headers
apex_web_service.g_request_headers.delete;
apex_web_service.g_request_headers(1).name := 'Content-type';
apex_web_service.g_request_headers(1).value := 'application/json';
t_url := 'http://127.0.0.1:8444/engine-rest/task/'||l_task_id||'/variables';
t_response := apex_web_service.make_rest_request(p_url => t_url, p_http_method => 'GET', p_body => t_body);
if apex_web_service.g_status_code = 200 then
apex_json.parse(t_response);
t_rowid := apex_json.get_number(p_path => 'row_id.value');
t_amount := apex_json.get_number(p_path => 'amount.value');
t_dept := apex_json.get_varchar2(p_path => 'dept.value');
t_invoiceno := apex_json.get_varchar2(p_path => 'invoiceno.value');
t_level := apex_json.get_varchar2(p_path => 'level.value');
UPDATE TEST_BPMN_INVOICES SET camunda_task_id = l_task_id, approve_level = t_level, approved = 0, form_key = l_formkey, taskdefinitionkey = l_taskdefinitionkey WHERE id = t_rowid;
end if;
END LOOP;
END IF;
end if;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
What does this code do? Apex connects to Camunda and downloads all tasks that are located in task with ID "approve_l1" using the "task" method. We have a list of tasks. However, we need variables and their values assigned to these tasks. So we loop through all the tasks and execute another REST method, "task/x/variables", to read them. Camunda does not have a rest method that allows you to read the details of the task and its variables - we need to make two connections.
As a result of starting the process and sending the invoice for approval, we received the REST camunda ID of this task. So why do we have to read a given process task again to find out the task IDs? Processes may cause flows to fork, resulting in one task becoming severals. Each of these tasks will have a different ID.
We present the "tasklist" constructed in this way in the application to people who accept first-level invoices. If we add other invoice information that we have in the Apex database, we know perfectly well who should see what. Notice that we also read information about "form_key" from the task and saved it in the table next to the invoice. Thanks to this, we know what form to display to users who will accept this invoice. The entire acceptance 'logic' is contained in this page. If the user clicks the "accept" button and the invoice requires only one level of approval, the process ends. If there are two levels, the activity after approval moves to the next level. Rejection means sending the activity to flow "Not accepted."
Building the tasklist can be done by the user (for instance in the background when the to-do list is displayed) or regularly via some scheduler/cron. The code that builds the task list from the "Acceptance level 2" task is the same, except that it reads a different task.
Complete the task
After building the task list, the person accepting the invoice displays the invoice acceptance/rejection form and if user clicks on the "accept invoice" button, the followin code is executed:
DECLARE
v_file_name VARCHAR2 (25);
v_json VARCHAR2(32767);
v_url VARCHAR(200);
t_url VARCHAR(200);
sJsonIndex APEX_JSON.t_values;
tJsonIndex APEX_JSON.t_values;
v_clob CLOB;
l_response CLOB;
t_response CLOB;
v_body CLOB;
t_body CLOB;
l_task_id VARCHAR2(80) := :P312_TASKID;
l_rowid NUMBER(8) := :P312_ROWID;
l_level VARCHAR2(5) := :P312_LEVEL;
BEGIN
v_body := '{
"variables": {
"approved" : {
"value" : true,
"type": "Boolean"
}
}
}';
l_response := '';
-- clear previus session headers
apex_web_service.g_request_headers.delete;
apex_web_service.g_request_headers(1).name := 'Content-type';
apex_web_service.g_request_headers(1).value := 'application/json';
v_url := 'http://127.0.0.1:8080/engine-rest/task/'||l_task_id||'/complete';
l_response := apex_web_service.make_rest_request(p_url => v_url, p_http_method => 'POST', p_body => v_body);
if apex_web_service.g_status_code = 204 then
if l_level = 1 then
UPDATE TEST_BPMN_INVOICES SET status = 'accepted', approved = 1, CAMUNDA_TASK_ID = null WHERE id = l_rowid;
else
UPDATE TEST_BPMN_INVOICES SET status = 'accepted level 1', approved = 3, CAMUNDA_TASK_ID = null WHERE id = l_rowid;
end if;
end if;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
What does this code do? We connect to the Camunda server and execute the 'task/x/complete' method. We also send the 'approved' variable thanks to which the flow expression will know how to direct the activity. There is a very similar code under the "Reject invoice" button, with the difference that the status will take a different value and the 'approved' variable will change to 'false'. 'Task complete' sends information about the task completion to Camudna. Camuda sends such activity to the next task in the process.
Application code and communication with Camunda
Note that both this code and the previous ones are very basic. In fact, the table structure of your application will be more extensive and, for example, statuses will have descriptions defined in dictionaries. We also do not describe here what code the page presenting the tasklist should have or what are the conditions that cause users to see or not see the appropriate buttons that will display the acceptance forms. You can also build the forms themselves in different ways. This tutorial is intended to help you understand the basics of communicating with Camunda.
DMN diagram
The DMN diagram will simplify our life. Instead of drawing a complicated BPM model with many expressions and flows, we place one task with "implementation DMN" that contains business conditions. In our process model, we need information whether the invoice will be accepted by only one person or by two people (one or two levels). We can decide about this on the application side and send the "level" variable with the ready value. However, it will be much easier, and more importantly much easier to make the appropriate change later, if it is done at the Camudna process level.
Therefore, in the diagram, insert a task after the start event and change "implementation" to the appropriate one. Assign the appropriate ID. Remember that "Decision Ref" must point to the correct DMN diagram ID.
Create DMN diagram:
Click on the part of the diagram marked in blue to open the DMN rules window:
Deploying the DMN to the platform
We can do this in two ways. The first one is to click the "Deploy current diagram" button directly from Camunda. The second way is to use REST and the 'deployment/create' method.
DMN testing
Camunda offers the ability to validate DMN conditions before the process is used in production. Use the REST POST method "decision-definition/key/invoice-assign-test/evaluate" to send the content as "Content-type: application/json":
{
"variables" : {
"kwota" : { "value" : 600, "type" : "Double" },
"dept" : { "value" : "it", "type" : "String" }
}
}
Enjoy your integration of Oracle Apex with Camunda BPM!