25.1.9.1.11.4.2 Updating Request Columns on Complete

Update request and business entity rows when an approval task completes.

Consider a Salary Change task definition that uses the following EMP_SAL_CHANGE_REQUEST table as its Actions Source table. A row in this table represents the request to change a particular employee's salary.

create table emp_sal_change_request (
    id            number 
                  generated by default on null as identity
                  constraint emp_sal_change_request_id_pk primary key,
    empno         number
                  constraint emp_sal_change_request_empno_fk 
                  references emp,
    new_salary    number,
    motivation    varchar2(255 char),
    status        varchar2(8 char)
);

For this task definition, the built-in Details Primary Key parameter suffices, since you can already reference all the columns in the EMP_SAL_CHANGE_REQUEST row it identifies. There is no need to define additional parameters.

Notice the EMP_SAL_CHANGE_REQUEST table includes an EMPNO foreign key column. So, to show the ENAME value in the task subject, you need to change from using the Actions Source table to using a query instead. This lets you join in the EMP table to include the related ENAME and existing SAL value using a query like this:

select r.id as request_id, r.empno, r.new_salary, e.ename, e.sal
  from emp_sal_change_request r
  join emp e
    on e.empno = r.empno
 where r.id = :APEX$TASK_PK

Notice above that if your task definition subject or actions don't need to reference the current value of particular columns like MOTIVATION and STATUS you can leave those out of the SELECT list.

Your task definition can now use a subject like the following, referencing the value of the Action Source query's ENAME, SAL, and NEW_SALARY columns.

Review &ENAME. Salary Change from &SAL. to &NEW_SALARY.

As before, when a Salary Change task completes successfully, it has an outcome of either APPROVED or REJECTED. Your action on the Complete event of type Execute Code needs to:

• Record the outcome of the salary change request in the STATUS column, then
• Update the SAL column to the NEW_SALARY value for EMP table row identified by the EMPNO value in the employee salary change request row related to the task instance.

Your code looks like:

-- Record the outcome in the salary change request row
update emp_sal_change_request
   set status = :APEX$TASK_OUTCOME
 where id = :REQUEST_ID;
-- If approved, update sal using Action Table query EMPNO column value
if :APEX$TASK_OUTCOME = 'APPROVED' then 
   update emp
      set   sal = :NEW_SALARY
    where empno = :EMPNO;
end if;