INTRODUCTION :
This feature lets users change an employee’s salary or job, or order a new laptop for them, after getting the approval of an appropriate individual. It highlights the key features of the Oracle APEX Approvals components.A strong workflow engine add on for Oracle APEX applications is called Flows for APEX. The Apex its able to create the process stages, and the Flows for APEX application to track the progress of your active processes. The approvals component allows Oracle APEX to create tasks for user approval. One place to manage approval tasks in the apex application.
WHY WE NEED TO DO / CAUSES :
A user should able to analyze the workflow of an approval request which was sent by the senior official. So that, the user can able to identify and solve the issue on required basis. There are several reasons why an Oracle Apex approval procedure could not perform as planned. Some frequent causes are as follows:
1) Missing input parameters
2) Incorrect Procedure
3) Incorrect Branch Logic
4) Incorrect Validations
5) Database Connection Issue
6) Concurrency Issue.
HOW DO WE SOLVE :
- Create a new procedure in the database that will handle the approval process. This procedure should take any necessary input parameters and return a status indicating whether the approval was successful or not. ln Oracle Apex, create a new process application.
- This process should be set to run on a specific event, such as when a button is clicked or when a page is submitted.In the process, add a PL/SQL package code block that calls the procedure created in step 1, passing in any necessary input parameters.
Package Specification code (EBA_DEMO_APPR) :
create or replace PACKAGE “EBA_DEMO_APPR” as
function get_approver_for(p_task_def_static_id varchar2,
p_empno number,
p_job varchar2,
p_proposed_sal number) return varchar2;
function get_admin_for(p_task_def_static_id varchar2,
p_empno number,
p_job varchar2,
p_proposed_sal number) return varchar2;
function user_has_open_approvals return boolean;
function user_has_open_admin_tasks return boolean;
procedure validate_admin_and_approver(p_task_def_static_id varchar2,
p_empno number,
p_proposed_sal number,
p_admin out varchar2,
p_approver out varchar2);
function details_task_url(p_app_id number, p_task_id number, p_url varchar2) return varchar2;
function approvers_for_task(p_task_id number) return varchar2;
function admins_for_task(p_task_id number) return varchar2;
function get_laptop_approver(p_renewal_count number) return varchar2;
end;
Body code (EBA_DEMO_APPR) :
create or replace PACKAGE BODY “EBA_DEMO_APPR” as
function get_participant_for_role(p_task_def_static_id varchar2,
p_job varchar2,
p_proposed_sal number,
p_role varchar2) return varchar2 is
l_ret varchar2(4000);
l_include_fallback_entries boolean := false;
l_current_user varchar2(200) := apex_application.g_user;
begin
apex_debug.info(‘### get_participant_for_role ‘||
‘(taskdef=%s)(job=%s)(role=%s)’,
p_task_def_static_id,p_job,p_role);
apex_debug.info(‘### get_participant_for_role (curruser=%s)’,
l_current_user);
for j in (select username,job_codes
from eba_demo_appr_approvers
where task_def_static_id = upper(p_task_def_static_id)
and (job_codes is null
or p_job is null
or (‘:’||job_codes||’:’ like ‘%:’||upper(p_job)||’:%’)
)
and (min_salary <= p_proposed_sal or min_salary is null)
and participant_role = upper(p_role)
and (participant_role = ‘ADMIN’ or username != l_current_user)
order by job_codes nulls last
) loop
— Only include the fallback entries with null JOB_CODES
— if no more specific ones found
if j.job_codes is null
and not l_include_fallback_entries
and l_ret is null then
l_include_fallback_entries := true;
end if;
if j.job_codes is not null or l_include_fallback_entries then
if l_ret is not null then
l_ret := l_ret ||’,’;
end if;
l_ret := l_ret || upper(j.username);
end if;
end loop;
apex_debug.info(‘### get_participant_for_role (returning=%s)’, l_ret);
return l_ret;
end;
—
function get_approver_for(p_task_def_static_id varchar2,
p_job varchar2,
p_proposed_sal number) return varchar2 is
begin
return get_participant_for_role(p_task_def_static_id,
p_job,
p_proposed_sal,
‘APPROVER’);
end;
—
function get_admin_for(p_task_def_static_id varchar2,
p_job varchar2,
p_proposed_sal number)
return varchar2 is
begin
return get_participant_for_role(p_task_def_static_id,
p_job,
p_proposed_sal,
‘ADMIN’);
end;
—
function get_approver_for(p_task_def_static_id varchar2,
p_empno number,
p_job varchar2,
p_proposed_sal number) return varchar2 is
begin
apex_debug.info(‘### get_approver_for ‘||
‘(taskdef=%s)(empno=%s)(job=%s)(sal=%s)’,
p_task_def_static_id,p_empno,p_job,p_proposed_sal);
return get_participant_for_role(p_task_def_static_id,
p_job,
p_proposed_sal,
‘APPROVER’);
end;
—
function get_admin_for(p_task_def_static_id varchar2,
p_empno number,
p_job varchar2,
p_proposed_sal number) return varchar2 is
begin
apex_debug.info(‘### get_admin_for ‘||
‘(taskdef=%s)(empno=%s)(job=%s)(sal=%s)’,
p_task_def_static_id,p_empno,p_job,p_proposed_sal);
return get_participant_for_role(p_task_def_static_id,
p_job,
p_proposed_sal,
‘ADMIN’);
end;
—
function user_has_open_approvals return boolean is
l_app_user varchar2(30) := apex_application.g_user;
begin
for j in (select null
from apex_task_participants tp
left join apex_tasks t on t.task_id = tp.task_id
where tp.participant = l_app_user
and t.state_code in (‘UNASSIGNED’,’ASSIGNED’)
and tp.participant_type = ‘POTENTIAL_OWNER’
and (t.initiator is null or t.initiator != l_app_user)
fetch first row only) loop
apex_debug.info(‘### Returning true for user %s ‘||
‘having open approvals tasks’,l_app_user);
return true;
end loop;
return false;
end;
function user_has_open_admin_tasks return boolean is
l_app_user varchar2(30) := apex_application.g_user;
begin
for j in (select null
from apex_task_participants tp
left join apex_tasks t on t.task_id = tp.task_id
where tp.participant = l_app_user
and t.state_code in (‘UNASSIGNED’,’ASSIGNED’)
and tp.participant_type = ‘BUSINESS_ADMIN’
fetch first row only) loop
apex_debug.info(‘### Returning true for user %s ‘||
‘having open admin tasks’,l_app_user);
return true;
end loop;
return false;
end;
—
function details_task_url(p_app_id number, p_task_id number, p_url varchar2) return varchar2 is
begin
return apex_plugin_util.replace_substitutions (
p_value => replace(replace(p_url, ‘&APP_ID.’, p_app_id), ‘&TASK_ID.’, p_task_id),
p_escape => false);
end;
—
procedure validate_admin_and_approver(p_task_def_static_id varchar2,
p_empno number,
p_proposed_sal number,
p_admin out varchar2,
p_approver out varchar2) is
l_job eba_demo_appr_emp.job%type;
begin
select job
into l_job
from eba_demo_appr_emp
where empno = p_empno;
p_admin := get_admin_for(p_task_def_static_id,
p_empno,
l_job,
p_proposed_sal);
p_approver := get_approver_for(p_task_def_static_id,
p_empno,
l_job,
p_proposed_sal);
end;
—
/*
* Workaround for RDBMS 21c issue directly using listagg() against view w/ json_table()
*/
function admins_for_task(p_task_id number) return varchar2 is
l_ret varchar2(2000);
begin
— Return admins as CSV
select listagg(participant,’, ‘)
within group (order by participant)
into l_ret
from apex_task_participants
where task_id = p_task_id
and participant_type = ‘BUSINESS_ADMIN’;
return l_ret;
end;
—
function approvers_for_task(p_task_id number) return varchar2 is
l_ret varchar2(2000);
begin
— Return approvers as CSV leaving out initiator
select listagg(participant,’, ‘)
within group (order by participant)
into l_ret
from apex_task_participants tp, apex_tasks t
where tp.task_id = p_task_id
and t.task_id = tp.task_id
and participant_type = ‘POTENTIAL_OWNER’
and (t.initiator is null or t.initiator != tp.participant);
return l_ret;
end;
function get_laptop_approver(p_renewal_count number) return varchar2 is
begin
return
case p_renewal_count
when 0 then ‘JANE’
when 1 then ‘STEVE’
else ‘BO’
end;
end;
end;
- In the process, add a branch that checks the status returned by the procedure. If the status is successful, the process should proceed to the next step. If the status is not successful, the process should display an error message or redirect the user to an error page.
- Finally, add any necessary validations or error handling to the process.
SALARY CHANGE APPROVER :
While the approvers for job changes are configured at design time, salary change approvers are determined at runtime based on the information shown on this page. Users listed here must have a participant role of approver or admin, and optionally can be associated with a comma separated list of job codes and minimum proposed salary value used by the approver and admin assignment business logic.
After saving any edits you’ve made, click test salary approver assignment to proactively test the salary change approver assignments that will be used the next time a Salary Change task is created.I had created a sample user like BO, JANE, PAT AND STEVE.
APPROVAL WORKFLOW APPLICATION :
Step 1 : A separate tab is opened to run the application. Navigate to the Apply For Salary Change page after logging in as dvdheerajsai user. Then add the fields details for Ename, Current Salary and Proposed Salary should be filled in. Then click “Submit .”
Step 2 : To see the Salary change for james task that was generated, go to the My Requests Page.
Step 3: Log out and Log back in as BO who is the manager of James. Navigate to the “My Approvals” page .
You will find Jame’s request, By pressing the buttons above, BO can Approval or Reject the request.
Step 4 : Once the task is Approved ,it’s removed from BO’s Approvals page. Log out and back in as dvdheerajsai, then go to the My Requests page. The task is shown as Completed.
Step 5 : This completes the structural workflow of an approval by an user.
Conclusion :
- Acceptances Terminology and component ideas.
- Using task definition and unified task lists, construct a simple approvals application for office administration.
- Using the Unified Task List and Task Details pages, how to approve or reject requests.
