APEX

Subject: Approval Workflow for Task Creation in Project Management System

Hello everyone,

I’m working on a Project Management System where users can create projects and define multiple tasks under each project, assigning them to various users.

Requirement:
When a task is created under any project, an approval request needs to be sent to the Project Manager of the corresponding project. Once the Project Manager approves it, the request ownership should transfer to the Administrator of the application for final approval.

Here is how I’ve implemented it:

Task Definition:
The potential owner of the task is determined by fetching the username of the Project Manager associated with the relevant project:

SELECT
T.USERNAME
FROM
PROJECTS P, T_USERS T
WHERE
PROJECT_ID = (SELECT PROJECT_ID
FROM T_TASKS
WHERE TASK_ID = :TASK_ID)
AND PROJECT_MANAGER = USER_ID;

Approval Process:

1. On Approve:
   Below is the PL/SQL block I’m using for the approval process:

DECLARE
l_project_manager VARCHAR2(100); -- Project Manager
l_admin VARCHAR2(100); -- Admin (second approver)
l_task_id NUMBER; -- Task ID
l_request_id NUMBER; -- Request ID
l_request_status VARCHAR2(50) := 'WAITING FIRST APPROVAL'; -- Default status
BEGIN
-- Fetch Project Manager for the Current Task
SELECT T.USERNAME
INTO l_project_manager
FROM PROJECTS P
JOIN T_USERS T ON P.PROJECT_MANAGER = T.USER_ID
WHERE P.PROJECT_ID = (
SELECT PROJECT_ID
FROM T_TASKS
WHERE TASK_ID = :TASK_ID
);

-- Check if the current user is the Project Manager
IF UPPER(:APP_USER) = UPPER(l_project_manager) THEN
l_request_id := :APEX$TASK_ID;

  -- Update Task Status  
  UPDATE t\_tasks  
  SET status = 'WAITING ADMIN APPROVAL'  
  WHERE task\_id = l\_request\_id;

  l\_request\_status := 'WAITING ADMIN APPROVAL';

  -- Fetch Admin (Second Approver)  
  SELECT TU.USERNAME   
  INTO l\_admin  
  FROM APEX\_APPL\_ACL\_USERS AU  
  JOIN T\_USERS TU ON TU.USERNAME = AU.USER\_NAME  
  WHERE INSTR(UPPER(AU.ROLE\_NAMES), 'ADMIN') > 0  
    AND AU.WORKSPACE = 'MGHANNAM'  
    AND AU.WORKSPACE\_ID = 19749082586604612299  
    AND AU.APPLICATION\_ID = 269417;

  -- Create a Task for Admin Approval  
  l\_task\_id := apex\_approval.create\_task(  
      p\_application\_id       => :APP\_ID,  
      p\_task\_def\_static\_id   => 'TASK\_APPROVAL',  
      p\_initiator            => :APP\_USER,   
      p\_parameters           => apex\_approval.t\_task\_parameters(  
          1 => apex\_approval.t\_task\_parameter(static\_id => 'TASK\_ID',        string\_value => :TASK\_ID),  
          2 => apex\_approval.t\_task\_parameter(static\_id => 'PROJECT\_ID',     string\_value => :PROJECT\_ID),  
          3 => apex\_approval.t\_task\_parameter(static\_id => 'STATUS', string\_value => l\_request\_status)  
      ),  
      p\_detail\_pk            => :APEX$TASK\_PK  
  );

ELSIF :APP_USER = l_admin THEN
l_request_id := :TASK_ID;

  -- Update Task Status to 'APPROVED'  
  UPDATE t\_tasks  
  SET status = 'APPROVED'  
  WHERE task\_id = l\_request\_id;

  l\_request\_status := 'APPROVED';  

ELSE
-- Unauthorized User Error
RAISE_APPLICATION_ERROR(-20001, 'You are not authorized to approve this request.');
END IF;
END;

____________________________________________________________________________________

for the reject. (On Reject):

DECLARE
l_project_manager VARCHAR2(100); -- Project Manager
l_admin VARCHAR2(100); -- Admin (second approver)
l_task_id NUMBER; -- Task ID
l_request_id NUMBER; -- Request ID
BEGIN
-- Step 1: Fetch Project Manager for the Current Task
SELECT T.USERNAME
INTO l_project_manager
FROM PROJECTS P
JOIN T_USERS T ON P.PROJECT_MANAGER = T.USER_ID
WHERE P.PROJECT_ID = (
SELECT PROJECT_ID
FROM T_TASKS
WHERE TASK_ID = :TASK_ID
);

-- Fetch the Admin (Second-Level Approver)
SELECT TU.USERNAME
INTO l_admin
FROM APEX_APPL_ACL_USERS AU
JOIN T_USERS TU ON TU.USERNAME = AU.USER_NAME
WHERE INSTR(UPPER(AU.ROLE_NAMES), 'ADMIN') > 0
AND AU.WORKSPACE = 'MGHANNAM'
AND AU.WORKSPACE_ID = 19749082586604612299
AND AU.APPLICATION_ID = 269417;

-- Determine Request ID Based on Approval Level
IF UPPER(:APP_USER) = UPPER(l_project_manager) THEN
l_request_id := :APEX$TASK_PK; -- Set Request ID to the Task ID (Project Manager Rejection)
ELSIF :APP_USER = l_admin THEN
l_request_id := :TASK_ID; -- Set Request ID to the Task ID (Admin Rejection)
ELSE
-- If the user is not an authorized approver, raise an error
RAISE_APPLICATION_ERROR(-20001, 'You are not authorized to reject this request.');
END IF;

-- Update the Task as REJECTED and Append Rejector to Audit Trail
UPDATE t_tasks
SET status = 'REJECTED'
WHERE task_id = l_request_id;

END;
____________________________________________________

Is this the best approach to handle the approval workflow for tasks in Oracle APEX? Are there any suggestions or improvements to make the logic more efficient or secure??

thanks in advance