Oracle APEX 22.1 My Multi-Approval Demo

In this article I'll try to show a very simple way to implement a Multi-Approval Application with Oracle APEX, using the new Approvals and Unified Task List in version 22.1.

My demo implements a workflow with sequential and parallel phases. The procedure is very simple and many improvements and refactoring on the code are possible (suggestions are welcome) I hope it will help to start studying this new functionality.

Here the diagram of the flow:

We have, in addition to the initial and final states, 4 states:

• State 10
• State 21 and State 22
• State 30

We want t ocreate also 5 Users:

• U00 the initiator
• U10, U21, U22, U30 the approvers

We want to approve a document stored in a table: Document.

Le't start with the table (in quick SQL)

This table has:

• 1 business key (code)
• 4 business fields (text10, text21 ,text22 ,text30)
• 2 technical fields (initiator,state)

Here the complete script:

-- create table
create table tk2_document (
? ? id? ? ? ? ? ? ? ? ? ? ? ? ? ? ?number generated by default on null as identity?
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?constraint tk2_document_id_pk primary key,
? ? code? ? ? ? ? ? ? ? ? ? ? ? ? ?varchar2(20 char)
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?constraint tk2_document_code_unq unique not null,
? ? initiator? ? ? ? ? ? ? ? ? ? ? varchar2(20 char) not null,
? ? state? ? ? ? ? ? ? ? ? ? ? ? ? varchar2(20 char) not null,
? ? text10? ? ? ? ? ? ? ? ? ? ? ? ?varchar2(20 char),
? ? text21? ? ? ? ? ? ? ? ? ? ? ? ?varchar2(20 char),
? ? text22? ? ? ? ? ? ? ? ? ? ? ? ?varchar2(20 char),
? ? text30? ? ? ? ? ? ? ? ? ? ? ? ?varchar2(20 char)
)
;


-- load data
?
-- Generated by Quick SQL Martedì Agosto 30, 2022? 15:41:34
?
/*
#settings{prefix: tk2}
document
? code vc20 /nn /unique
? initiator vc20 /nn
? state vc20 /nn
? text10 vc20
? text21 vc20
? text22 vc20
? text30 vc20


# settings = { prefix: "TK2", semantics: "CHAR", language: "EN", APEX: true }
*/        

Let's create the application with an Interactive Report and a Form:

Now Let's go to Shared Components, Task Definition and create 4 Different Task: 10, 21, 22, 30 (yes, only numbers I've no fantasy!)

Here the 1st: 10

Here we define:

• A Parameter ( &CODE. )
• A Potential Owner ( U10 )

Then we define an Action source with the following query so we can bind the task with the code of the document

select code from tk2_document where id = :APEX$TASK_PK        

Let's create a Task Details Page clicking on the Button

A Page (in this case number 4) will be created and a link will be inserted in the field below

Let's Create the Parameter used before:

Let's Repeat this Task Creation another 3 times, doing the same things and changing:

• Name
• Subject
• Static ID
• Potential Owner

In the Task Detail Page Number insert the number of the page created in the 1st task (in our case 4) dont' click on the button Create

Here the 4 tasks

Now Let's reopen each task and add an Action fr the Complete/Approved event (I left the Complete/Reject Event as exercise).

Task 10:

Here I set l_new_state and l_initiator variable and update the state field in document:

declare
l_task_id number;
l_new_state ts2_document.state%type;
l_initiator ts2_document.initiator%type;
begin
? ? l_new_state := '10';
? ? select initiator into l_initiator from ts2_document where id = :APEX$TASK_PK;

? ? update ts2_document set state = l_new_state where id = :APEX$TASK_PK;        

Then I create 2 tasks 21 and 22:

l_task_id := apex_approval.create_task
? ? ? ? ? ? p_application_id => :APP_ID,
? ? ? ? ? ? p_task_def_static_id => '21',
? ? ? ? ? ? p_initiator => l_initiator, 
? ? ? ? ? ? p_parameters => apex_approval.t_task_parameters(
? ? ? ? ? ? ? ? 1 => apex_approval.t_task_parameter(static_id => 'CODE', string_value => :CODE)
? ? ? ? ),
? ? ? ? p_detail_pk => :APEX$TASK_PK
? ? );


? ? ? ? l_task_id := apex_approval.create_task(
? ? ? ? ? ? p_application_id => :APP_ID,
? ? ? ? ? ? p_task_def_static_id => '22',
? ? ? ? ? ? p_initiator => l_initiator, 
? ? ? ? ? ? p_parameters => apex_approval.t_task_parameters(
? ? ? ? ? ? ? ? 1 => apex_approval.t_task_parameter(static_id => 'CODE', string_value => :CODE)
? ? ? ? ),
? ? ? ? p_detail_pk => :APEX$TASK_PK
? ? );
end;
        

Task 21:

Here I set l_new_state (with a trick) and l_initiator variable and update the state field in document:

declare
l_str apex_t_varchar2;
l_new_state ts2_document.state%type;
l_state ts2_document.state%type;
l_initiator ts2_document.initiator%type;
l_task_id number;


begin
? ? select initiator,state into l_initiator,l_state from ts2_document where id = :APEX$TASK_PK;
? ? select * BULK COLLECT into l_str ?
? ? ?from table (apex_string.split(l_state||':21',':')) 
? ? order by 1;
? ? 
? ? l_new_state := apex_string.JOIN (l_str, ':');
? ? 
? ? update ts2_document set state = l_new_state where id = :APEX$TASK_PK;        

In the state field I save all the approved state. With BULK COLLECT I can populate an apex_t_varchar2 ordered. Only when all 21 and 22 task are approved I create the next task

if l_new_state = '10:21:22' the
? ? ? ?l_task_id := apex_approval.create_task(
? ? ? ? ? ? p_application_id => :APP_ID,
? ? ? ? ? ? p_task_def_static_id => '30',
? ? ? ? ? ? p_initiator => l_initiator, 
? ? ? ? ? ? p_parameters => apex_approval.t_task_parameters(
? ? ? ? ? ? ? ? 1 => apex_approval.t_task_parameter(static_id => 'CODE', string_value => :CODE)
? ? ? ? ),
? ? ? ? p_detail_pk => :APEX$TASK_PK
? ? );
? ? end if;
end;n        

Action in Task 22 is similar to 21:

declare
l_str apex_t_varchar2;
l_new_state ts2_document.state%type;
l_state ts2_document.state%type;
l_initiator ts2_document.initiator%type;
l_task_id number;


begin
? ? select initiator,state into l_initiator,l_state from ts2_document where id = :APEX$TASK_PK;
? ? select * BULK COLLECT into l_str ?
? ? ?from table (apex_string.split(l_state||':22',':')) 
? ? order by 1;
? ? 
? ? l_new_state := apex_string.JOIN (l_str, ':');
? ? 
? ? update ts2_document set state = l_new_state where id = :APEX$TASK_PK;
? ? 
? ? if l_new_state = '10:21:22' then
? ? ? ?l_task_id := apex_approval.create_task(
? ? ? ? ? ? p_application_id => :APP_ID,
? ? ? ? ? ? p_task_def_static_id => '30',
? ? ? ? ? ? p_initiator => l_initiator, 
? ? ? ? ? ? p_parameters => apex_approval.t_task_parameters(
? ? ? ? ? ? ? ? 1 => apex_approval.t_task_parameter(static_id => 'CODE', string_value => :CODE)
? ? ? ? ),
? ? ? ? p_detail_pk => :APEX$TASK_PK
? ? );
? ? end if;
end;        

TASK 30:

update ts2_document set state = state||':30' where id = :APEX$TASK_PK;        

Now the standard part: Let's create a new "Human Task - Create" Process in the form (Page 3) after Process Form, with P3_ID as Details Primary Key Item

Let's populate the Code Parameter with P3_CODE

Set default values to P3_INITIATOR ( :APP_USER ) and P3_STATE ( 00 )

Now Let's Create 2 Standard "Unified Task List" Pages: My Tasks and Initiated by Me

Let's modify the query in the two pages to filter only for this application tasks:

select *
? from table ( apex_approval.get_tasks ( p_context => 'MY_TASKS' ) )
?where details_app_id = :APP_ID        

select *
? from table ( apex_approval.get_tasks ( p_context => 'INITIATED_BY_ME' ) )
?where details_app_id = :APP_ID        

Set Open door Authentication for test

Ok Let's Start!

Now you can Enter with U00, then U10 then U21, U22 indifferently then U30

And so on.

Maybe you want the fields to be editable only in their own state

in Text10 Read Only Condition:

declare
l_task_id number;
begin
select task_id into l_task_id
? from apex_tasks 
?where application_id ?= :APP_ID
? ?and detail_pk = :P3_ID 
? ?and task_def_static_id = 10; 


return not apex_approval.is_allowed (
? ? p_task_id ? => l_task_id,
? ? p_operation => apex_approval.c_task_op_approve );
exception
? ? ? ?when others then 
? ? ? ?return true;
end;        

same in Text21, Text22, Text30 changing only a number.

It's a start, many improvements are possible, feel free to comment