How to Create Tasks and Run Workflows from PL/SQL Using APEX_HUMAN_TASK and APEX_WORKFLOW

Oracle APEX has four kinds of long-running work: human tasks that wait for a person, workflows that chain activities and tasks, automations that run on a schedule, and background execution chains of page processes. Each is configured declaratively, and each has a PL/SQL package for everything the built-in pages do and more: APEX_HUMAN_TASK, APEX_WORKFLOW, APEX_AUTOMATION, and APEX_BACKGROUND_PROCESS.

This guide covers all four with tested examples and their real output: creating, claiming, approving, rejecting, and delegating tasks; starting, suspending, resuming, and terminating workflows; scheduling and running automations; and reporting background progress.

Quick Reference

How to Run These Examples

The examples ran in Oracle APEX 26.1 against a test application with ID 200, which has these components:

• An approval task definition, Order Approval (order-approval), whose potential owners are the users of the Can Approve Orders authorization. Approving runs orb_sales.approve_order; rejecting returns the order to NEW.
• An action task definition, Shipment Confirmation (shipment-confirmation), assigned to ADMIN.
• A workflow, Order Fulfillment (order-fulfillment), that reserves stock and prepares the invoice in parallel, waits for a shipment confirmation task, then ships the order.
• An automation, Remind Pending Approvals (remind-pending-approvals), that emails a reminder for each order awaiting approval.
• A background execution chain, Recalculate Totals, on page 8.

Each example needs an APEX session of application 200, created with APEX_SESSION.CREATE_SESSION as shown in the guide to creating APEX sessions and managing session state from PL/SQL. The orders come from the Orbit Outfitters sample schema, available from the orb_tables repository on GitHub. The examples roll back or undo their changes. The output under each example is exactly what the database printed. The declarative side is covered in the guides to approvals, tasks, and workflows and automations, email, and push notifications.

Human Tasks: APEX_HUMAN_TASK

A task has a definition (Shared Components, Task Definitions), an initiator, potential owners and business administrators, and, once someone claims it, an actual owner. It moves through the states UNASSIGNED, ASSIGNED, and INFO_REQUESTED, and finally COMPLETED, CANCELLED, EXPIRED, FAILED, or ERRORED. Completing an approval task with the outcome APPROVED or REJECTED runs the definition's actions for that outcome.

Every operation checks the user: APEX_HUMAN_TASK works as the session's user, just like the task list pages do. An initiator cannot work on their own task unless the definition allows it, which is why these examples create tasks as KIM.LEE and work on them as ADMIN.

CREATE_TASK

Creates a task from a definition and returns its ID. p_detail_pk is the primary key of the row the task is about, available as :APEX$TASK_PK in the definition's query and actions. p_subject, p_priority, and p_due_date override the definition, p_parameters sets its parameters (an array of static_id and string_value records), and p_initiator defaults to the current user. A task with a single potential owner is assigned to that owner immediately.

Syntax:

apex_human_task.create_task(p_application_id in number default apex_application.g_flow_id,
    p_task_def_static_id in varchar2, p_subject in varchar2 default null,
    p_parameters in t_task_parameters default c_empty_task_parameters, p_priority in integer default null,
    p_initiator in varchar2 default null, p_initiator_can_complete in boolean default null,
    p_detail_pk in varchar2 default null, p_due_date in timestamp with time zone default null) return number

GET_TASKS, IS_ALLOWED, and GET_TASK_HISTORY

GET_TASKS returns tasks as rows, with subject, state, outcome, priority, owners, dates, and links, for a context: c_context_my_tasks (the user's tasks to work on), c_context_admin_tasks, c_context_initiated_by_me, or c_context_single_task with p_task_id. The Unified Task List page uses it. IS_ALLOWED tells whether a user may perform an operation, such as c_task_op_claim, c_task_op_approve, or c_task_op_delegate, which is how you show or hide buttons. GET_TASK_HISTORY returns the task's history.

Syntax:

apex_human_task.get_tasks(p_context in varchar2 default c_context_my_tasks, p_user in varchar2 default apex_application.g_user,
    p_task_id in number default null, p_application_id in number default null,
    p_show_expired_tasks in varchar2 default 'N') return apex_t_approval_tasks pipelined
apex_human_task.is_allowed(p_task_id in number, p_operation in t_task_operation, p_user in varchar2 default apex_application.g_user,
    p_new_participant in varchar2 default null) return boolean
apex_human_task.get_task_history(p_task_id in number, p_include_all in varchar2 default 'N') return apex_t_approval_log_table pipelined

CLAIM_TASK, SET_TASK_PRIORITY, SET_TASK_DUE, ADD_TASK_COMMENT, and REQUEST_MORE_INFORMATION

CLAIM_TASK makes the current user the actual owner, and RELEASE_TASK gives the task back to all potential owners. SET_TASK_PRIORITY (1 urgent to 5 lowest, with constants such as c_task_priority_urgent) and SET_TASK_DUE change the priority and due date, and ADD_TASK_COMMENT adds a comment. REQUEST_MORE_INFORMATION sends a question to the initiator, or to p_to_user; the task stays INFO_REQUESTED until SUBMIT_INFORMATION answers it.

This example needs a session of application 200, page 1, as ADMIN. It rolls back at the end.

Example:

declare
    l_task number;
    procedure show is
    begin
        for t in (select * from table(apex_human_task.get_tasks(p_context => apex_human_task.c_context_single_task,
                                                                p_task_id => l_task))) loop
            dbms_output.put_line('  ' || t.state_code || ', priority ' || t.priority || ', owner '
                                 || nvl(t.actual_owner, '-') || ', due ' || to_char(t.due_on, 'DD-MON-YYYY HH24:MI'));
        end loop;
    end;
begin
    -- an approval of order ORD-12259, requested by the sales representative KIM.LEE;
    -- ADMIN is a potential owner through the authorization "Can Approve Orders"
    l_task := apex_human_task.create_task(
                  p_application_id     => 200,
                  p_task_def_static_id => 'order-approval',
                  p_detail_pk          => '2259',          -- :APEX$TASK_PK in the task definition
                  p_initiator          => 'KIM.LEE');
    for t in (select subject, initiator from table(apex_human_task.get_tasks(
                  p_context => apex_human_task.c_context_single_task, p_task_id => l_task))) loop
        dbms_output.put_line(t.subject || ' (from ' || t.initiator || ')');
    end loop;
    show;

    dbms_output.put_line('may claim:   ' || case when apex_human_task.is_allowed(l_task, apex_human_task.c_task_op_claim) then 'yes' else 'no' end);
    dbms_output.put_line('may approve: ' || case when apex_human_task.is_allowed(l_task, apex_human_task.c_task_op_approve) then 'yes' else 'no' end);

    apex_human_task.claim_task(p_task_id => l_task);                          -- ADMIN becomes the owner
    apex_human_task.set_task_priority(p_task_id => l_task, p_priority => apex_human_task.c_task_priority_urgent);
    apex_human_task.set_task_due(p_task_id => l_task, p_due_date => timestamp '2026-10-01 12:00:00 +00:00');
    apex_human_task.add_task_comment(p_task_id => l_task, p_text => 'The discount is above 15%.');
    show;
    dbms_output.put_line('may approve: ' || case when apex_human_task.is_allowed(l_task, apex_human_task.c_task_op_approve) then 'yes' else 'no' end);

    -- ask the initiator a question: the task waits for the answer
    apex_human_task.request_more_information(p_task_id => l_task, p_text => 'Why this discount?', p_to_user => 'KIM.LEE');
    show;

    for h in (select event_type, event_creator, display_msg
                from table(apex_human_task.get_task_history(p_task_id => l_task))
               order by event_timestamp) loop
        dbms_output.put_line(rpad(h.event_type, 22) || rpad(h.event_creator, 8) || h.display_msg);
    end loop;
    rollback;   -- the task is gone again
end;
/

Output:

Approve order ORD-12259 for Canyon Adventure Club (from KIM.LEE)
  UNASSIGNED, priority 2, owner -, due 27-SEP-2026 11:40
may claim:   yes
may approve: no
  ASSIGNED, priority 1, owner ADMIN, due 01-OCT-2026 12:00
may approve: yes
  INFO_REQUESTED, priority 1, owner KIM.LEE, due 01-OCT-2026 12:00
Create                KIM.LEE Task created with ID 43008019349312473
Claim                 ADMIN   Task claimed by ADMIN
Update Priority       ADMIN   Set task priority to 1
Update Due On         ADMIN   Set task due date to 01-OCT-26 12.00.00.000000 PM +00:00
Update Comment        ADMIN   Add comment: The discount is above 15%.
Request Information   ADMIN   Requested KIM.LEE for more information: Why this discount?

IS_ALLOWED said ADMIN could claim but not yet approve; after claiming, approving was allowed. The history shows every step with who did it, which is your audit trail for approvals.

APPROVE_TASK, REJECT_TASK, COMPLETE_TASK, and CANCEL_TASK

APPROVE_TASK and REJECT_TASK complete an approval task with an outcome, and COMPLETE_TASK completes an action task, or an approval task with p_outcome. They require the actual owner, or p_autoclaim true to claim the task first. The definition's actions for the outcome run in the same transaction. CANCEL_TASK cancels a task, as its initiator or a business administrator.

Syntax:

apex_human_task.approve_task | reject_task(p_task_id in number, p_autoclaim in boolean default false)
apex_human_task.complete_task(p_task_id in number, p_outcome in t_task_outcome default null, p_autoclaim in boolean default false)
apex_human_task.cancel_task(p_task_id in number)

This example needs a session of application 200, page 1, as ADMIN. It rolls back at the end.

Example:

declare
    l_task number;
    procedure show(p_order number) is
    begin
        for t in (select t.state_code, t.outcome_code, o.status
                    from table(apex_human_task.get_tasks(p_context => apex_human_task.c_context_single_task,
                                                         p_task_id => l_task)) t
                    join orb_orders o on o.order_id = p_order) loop
            dbms_output.put_line('  task ' || t.state_code || ' ' || t.outcome_code || ', order ' || t.status);
        end loop;
    end;
begin
    l_task := apex_human_task.create_task(p_task_def_static_id => 'order-approval',
                                          p_detail_pk => '2259', p_initiator => 'KIM.LEE');
    apex_human_task.approve_task(p_task_id => l_task, p_autoclaim => true);   -- runs the action "Approve Order"
    dbms_output.put_line('approve_task:');
    show(2259);

    l_task := apex_human_task.create_task(p_task_def_static_id => 'order-approval',
                                          p_detail_pk => '2260', p_initiator => 'KIM.LEE');
    apex_human_task.claim_task(p_task_id => l_task);
    apex_human_task.reject_task(p_task_id => l_task);                          -- runs "Return Order"
    dbms_output.put_line('reject_task:');
    show(2260);

    -- an action task has no approve or reject: complete it, with or without an outcome
    l_task := apex_human_task.create_task(p_task_def_static_id => 'shipment-confirmation',
                                          p_detail_pk => '2262', p_initiator => 'KIM.LEE');
    apex_human_task.complete_task(p_task_id => l_task, p_autoclaim => true);
    dbms_output.put_line('complete_task:');
    show(2262);
    rollback;   -- tasks and orders as they were
end;
/

Output:

approve_task:
  task COMPLETED APPROVED, order APPROVED
reject_task:
  task COMPLETED REJECTED, order NEW
complete_task:
  task COMPLETED , order APPROVED

Approving ran the definition's approve action and set the order to APPROVED; rejecting ran the other action and returned the order to NEW. The action task has no approve or reject, so COMPLETE_TASK finished it without an outcome. Because those actions run in the same transaction as the call, a rollback undoes both the task and the order change.

ADD_TASK_POTENTIAL_OWNER, REMOVE_POTENTIAL_OWNER, EXCLUDE_POTENTIAL_OWNER, DELEGATE_TASK, and GET_TASK_DELEGATES

ADD_TASK_POTENTIAL_OWNER adds a user as potential owner, or with p_identity_type set to c_task_identity_type_auth, the users of an authorization. REMOVE_POTENTIAL_OWNER removes one. EXCLUDE_POTENTIAL_OWNER excludes a user who would otherwise be a potential owner, for example through an authorization, such as the person an approval is about. DELEGATE_TASK makes another potential owner the actual owner, and GET_TASK_DELEGATES returns the users a task can be delegated to. IS_BUSINESS_ADMIN and IS_OF_PARTICIPANT_TYPE check a user's role.

This example needs a session of application 200, page 1, as ADMIN. It rolls back at the end.

Example:

declare
    l_task number;
    procedure owners is
        l_list varchar2(400);
    begin
        select listagg(participant_type || ':' || participant, ' ') within group (order by participant_type, participant)
          into l_list from apex_task_participants where task_id = l_task and participant_type <> 'INITIATOR';
        dbms_output.put_line('  ' || l_list);
    end;
begin
    l_task := apex_human_task.create_task(p_task_def_static_id => 'shipment-confirmation',
                                          p_detail_pk => '2262', p_initiator => 'KIM.LEE');
    owners;
    apex_human_task.add_task_potential_owner(p_task_id => l_task, p_potential_owner => 'JO.PARK');
    apex_human_task.add_task_potential_owner(p_task_id => l_task, p_potential_owner => 'SAM.ROY');
    owners;

    for d in (select disp, val from table(apex_human_task.get_task_delegates(p_task_id => l_task))) loop
        dbms_output.put_line('  could delegate to: ' || d.disp);
    end loop;
    -- ADMIN, the only potential owner at creation, owns the task already
    apex_human_task.delegate_task(p_task_id => l_task, p_to_user => 'JO.PARK');   -- JO.PARK is the owner now
    for t in (select actual_owner, state_code from table(apex_human_task.get_tasks(
                  p_context => apex_human_task.c_context_single_task, p_task_id => l_task))) loop
        dbms_output.put_line('  delegated: owner ' || t.actual_owner || ', ' || t.state_code);
    end loop;

    apex_human_task.remove_potential_owner(p_task_id => l_task, p_potential_owner => 'SAM.ROY');
    apex_human_task.exclude_potential_owner(p_task_id => l_task, p_potential_owner => 'ADMIN');
    owners;

    dbms_output.put_line('ADMIN business admin: ' || case when apex_human_task.is_business_admin(p_user => 'ADMIN') then 'yes' else 'no' end);
    dbms_output.put_line('JO.PARK potential owner: ' || case when apex_human_task.is_of_participant_type(
        p_task_id => l_task, p_participant_type => apex_human_task.c_task_potential_owner, p_user => 'JO.PARK') then 'yes' else 'no' end);
    rollback;
end;
/

Output:

  BUSINESS_ADMIN:ADMIN POTENTIAL_OWNER:ADMIN
  BUSINESS_ADMIN:ADMIN POTENTIAL_OWNER:ADMIN POTENTIAL_OWNER:JO.PARK POTENTIAL_OWNER:SAM.ROY
  could delegate to: JO.PARK
  could delegate to: SAM.ROY
  delegated: owner JO.PARK, ASSIGNED
  BUSINESS_ADMIN:ADMIN EXCLUDED_OWNER:ADMIN POTENTIAL_OWNER:ADMIN POTENTIAL_OWNER:JO.PARK
ADMIN business admin: yes
JO.PARK potential owner: yes

ADMIN, the only potential owner when the task was created, owned it at once, and DELEGATE_TASK handed it to JO.PARK. After the exclusion, ADMIN is still listed as a potential owner but also as EXCLUDED_OWNER, which wins. EXCLUDE_POTENTIAL_OWNER is the tool for a classic rule: nobody may approve their own request, even when an authorization would otherwise make them an approver.

The Other Subprograms

The list-of-values functions work in plain SQL:

Example:

select 'state' as lov, disp, val from table(apex_human_task.get_lov_state)
union all
select 'priority', disp, val from table(apex_human_task.get_lov_priority)
union all
select 'type', disp, val from table(apex_human_task.get_lov_type);

Output:

LOV      DISP                  VAL
-------- --------------------- --------------
state    Unassigned            UNASSIGNED
state    Assigned              ASSIGNED
state    Information Requested INFO_REQUESTED
state    Completed             COMPLETED
state    Canceled              CANCELLED
state    Expired               EXPIRED
state    Failed                FAILED
state    Errored               ERRORED
priority Urgent                1
priority High                  2
priority Medium                3
priority Low                   4
priority Lowest                5
type     Approval              APPROVAL
type     Action                ACTION

Workflows: APEX_WORKFLOW

A workflow instance runs a version of a workflow definition for a detail row, available as :APEX$WORKFLOW_DETAIL_PK. Its activities run in a background job, not in the call that starts the workflow, and it stops at activities that wait, for a task, a time, or CONTINUE_ACTIVITY. Only an Active version can be started from code.

START_WORKFLOW, GET_WORKFLOWS, and GET_WORKFLOW_STATE

START_WORKFLOW starts an instance of the active version, returns its ID, and commits. p_parameters sets the workflow's parameters, p_detail_pk the detail row, p_initiator the initiator, and p_debug_level the instance's log level. GET_WORKFLOWS returns instances for a context (c_context_my_workflows, c_context_admin_workflows, c_context_initiated_by_me, or c_context_single_workflow), and GET_WORKFLOW_STATE the state of one: ACTIVE, SUSPENDED, COMPLETED, TERMINATED, or FAULTED. The APEX_WORKFLOW_ACTIVITIES view shows the activities that ran.

Syntax:

apex_workflow.start_workflow(p_application_id in number default apex_application.g_flow_id, p_static_id in varchar2,
    p_parameters in t_workflow_parameters default c_empty_workflow_parameters, p_initiator in varchar2 default null,
    p_detail_pk in varchar2 default null, p_debug_level in apex_debug.t_log_level default null) return number
apex_workflow.get_workflows(p_context in varchar2 default c_context_my_workflows, p_user in varchar2 default apex_application.g_user,
    p_workflow_id in number default null, p_application_id in number default null) return apex_t_workflow_instances pipelined
apex_workflow.get_workflow_state(p_instance_id in number) return varchar2

SUSPEND, RESUME, UPDATE_VARIABLES, GET_VARIABLE_VALUE, and GET_VARIABLE_CLOB_VALUE

SUSPEND pauses an instance and RESUME continues it, either from where it stopped or from the activity p_activity_static_id. Variables can only be changed while the instance is suspended or faulted: UPDATE_VARIABLES sets them from an array of static_id and string_value, and GET_VARIABLE_VALUE and GET_VARIABLE_CLOB_VALUE read them.

This example needs a session of application 200, page 1. Because workflows run in a background job, it waits up to a minute for each step, and at the end it puts the order back and deletes the workflow and its task.

Example:

declare
    l_wf   number;
    l_task number;
    l_vars apex_workflow.t_workflow_parameters;

    -- workflows run in a background job: wait for a condition, up to a minute
    function waited(p_sql varchar2) return boolean is
        l_n number;
    begin
        for i in 1 .. 60 loop
            execute immediate p_sql into l_n using l_wf;
            if l_n > 0 then return true; end if;
            dbms_session.sleep(1);
        end loop;
        return false;
    end;
    procedure show(p_label varchar2) is
    begin
        dbms_output.put_line(p_label || ': workflow ' || apex_workflow.get_workflow_state(p_instance_id => l_wf));
        for a in (select name, state from apex_workflow_activities where workflow_id = l_wf order by start_time, name) loop
            dbms_output.put_line('  ' || rpad(a.name, 18) || a.state);
        end loop;
    end;
begin
    -- start "Order Fulfillment" for the approved order ORD-12263 (it commits)
    l_wf := apex_workflow.start_workflow(p_static_id => 'order-fulfillment', p_detail_pk => '2263',
                                         p_initiator => 'KIM.LEE');
    for w in (select title, initiator, workflow_version from table(apex_workflow.get_workflows(
                  p_context => apex_workflow.c_context_single_workflow, p_workflow_id => l_wf))) loop
        dbms_output.put_line(w.title || ', version ' || w.workflow_version || ', started by ' || w.initiator);
    end loop;

    if waited('select count(*) from apex_tasks where workflow_id = :1') then
        show('task created');
    end if;

    -- workflow variables can be changed while the workflow is suspended
    apex_workflow.suspend(p_instance_id => l_wf);
    dbms_output.put_line('suspended: ' || apex_workflow.get_workflow_state(p_instance_id => l_wf));
    l_vars(1).static_id    := 'APPROVER';
    l_vars(1).string_value := 'ADMIN';
    apex_workflow.update_variables(p_instance_id => l_wf, p_changed_params => l_vars);
    dbms_output.put_line('APPROVER = ' || apex_workflow.get_variable_value(p_instance_id => l_wf, p_variable_static_id => 'APPROVER'));
    apex_workflow.resume(p_instance_id => l_wf);
    dbms_output.put_line('resumed: ' || apex_workflow.get_workflow_state(p_instance_id => l_wf));

    -- complete the shipment confirmation: the workflow goes on
    select task_id into l_task from apex_tasks where workflow_id = l_wf;
    apex_human_task.complete_task(p_task_id => l_task, p_autoclaim => true);
    commit;
    if waited(q'~select count(*) from apex_workflows where workflow_id = :1 and state_code = 'COMPLETED'~') then
        show('task completed');
    end if;
    for o in (select status, replace(regexp_replace(notes, '\d{4}-\d\d-\d\d \d\d:\d\d ', ''), chr(10), ', ') as notes
                from orb_orders where order_id = 2263) loop
        dbms_output.put_line('order: ' || o.status || ' (' || o.notes || ')');
    end loop;

    -- put the lab back: the order, and the workflow with its task
    update orb_orders set status = 'APPROVED', shipped_date = null, notes = null where order_id = 2263;
    apex_workflow.delete_workflows(p_application_id => 200, p_static_id => 'order-fulfillment',
                                   p_include_all_versions => true);
    apex_human_task.delete_tasks(p_application_id => 200, p_include_workflow_tasks => true);
    commit;
end;
/

Output:

Fulfill order ORD-12263, version 1.0, started by KIM.LEE
task created: workflow ACTIVE
  Start             COMPLETED
  Prepare Order     COMPLETED
  Reserve Stock     COMPLETED
  Prepare Invoice   COMPLETED
  Confirm Shipment  WAITING
suspended: SUSPENDED
APPROVER = ADMIN
resumed: ACTIVE
task completed: workflow COMPLETED
  Start             COMPLETED
  Prepare Order     COMPLETED
  Reserve Stock     COMPLETED
  Prepare Invoice   COMPLETED
  Confirm Shipment  COMPLETED
  Ship Order        COMPLETED
  End               COMPLETED
order: SHIPPED (Stock reserved, Invoice prepared)

By the time the task existed, Reserve Stock and Prepare Invoice had already finished and the workflow was waiting at Confirm Shipment. While it was suspended, the APPROVER variable was changed to ADMIN. Completing that task let the workflow run to the end and ship the order. The polling helper in the example is only needed in a script; a page simply shows the current state.

TERMINATE, RETRY, and the Permission Checks

TERMINATE ends an instance, and RETRY runs a faulted instance again from the activity that failed. IS_ALLOWED checks an operation for a user, with the constants c_workflow$_op_suspend, _resume, _retry, _terminate, _update_var, and _setduedate; note the $ in the names, which the documentation leaves out. IS_ADMIN and IS_OF_PARTICIPANT_TYPE (c_workflow_owner, c_workflow_admin) check the user's role, and SET_LOG_LEVEL sets the debug level of one instance.

This example needs a session of application 200, page 1. It starts a workflow, terminates it, and cleans up, then lists the workflow states.

Example:

declare
    l_wf number;
begin
    l_wf := apex_workflow.start_workflow(p_static_id => 'order-fulfillment', p_detail_pk => '2264',
                                         p_initiator => 'KIM.LEE');
    apex_workflow.set_log_level(p_instance_id => l_wf, p_debug_level => apex_debug.c_log_level_info);

    dbms_output.put_line('ADMIN is workflow admin:  ' || case when apex_workflow.is_admin(p_user => 'ADMIN') then 'yes' else 'no' end);
    dbms_output.put_line('ADMIN is owner:           ' || case when apex_workflow.is_of_participant_type(
        p_instance_id => l_wf, p_participant_type => apex_workflow.c_workflow_owner, p_user => 'ADMIN') then 'yes' else 'no' end);
    dbms_output.put_line('KIM.LEE may terminate:    ' || case when apex_workflow.is_allowed(
        p_instance_id => l_wf, p_operation => apex_workflow.c_workflow$_op_terminate, p_user => 'KIM.LEE') then 'yes' else 'no' end);
    dbms_output.put_line('ADMIN may terminate:      ' || case when apex_workflow.is_allowed(
        p_instance_id => l_wf, p_operation => apex_workflow.c_workflow$_op_terminate, p_user => 'ADMIN') then 'yes' else 'no' end);

    apex_workflow.terminate(p_instance_id => l_wf);
    dbms_output.put_line('state: ' || apex_workflow.get_workflow_state(p_instance_id => l_wf));

    apex_workflow.delete_workflows(p_application_id => 200, p_static_id => 'order-fulfillment',
                                   p_states => apex_t_varchar2(apex_workflow.c_state_terminated),
                                   p_include_all_versions => true);
    apex_human_task.delete_tasks(p_application_id => 200, p_include_workflow_tasks => true);
    update orb_orders set notes = null where order_id = 2264;     -- notes a quick activity may have added
    commit;
    dbms_output.put_line('next purge: ' || case when apex_workflow.get_next_purge_timestamp > systimestamp then 'scheduled' end);
end;
/

select disp, val from table(apex_workflow.get_lov_workflow_state);

Output:

ADMIN is workflow admin:  yes
ADMIN is owner:           yes
KIM.LEE may terminate:    yes
ADMIN may terminate:      yes
state: TERMINATED
next purge: scheduled

DISP       VAL
---------- ----------
Active     ACTIVE
Suspended  SUSPENDED
Completed  COMPLETED
Terminated TERMINATED
Faulted    FAULTED

The initiator, KIM.LEE, was allowed to terminate the instance, as was ADMIN, the owner and administrator. CONTINUE_ACTIVITY is how an external event, such as a payment confirmation arriving through a REST call, moves a waiting workflow forward.

Automations: APEX_AUTOMATION

An automation runs actions on a schedule or on demand, once or for each row of a query. Scheduled automations run as database jobs.

ENABLE, DISABLE, RESCHEDULE, EXECUTE, and the Status Functions

ENABLE and DISABLE turn the schedule on and off, and RESCHEDULE sets the next run. EXECUTE runs an automation now, for all rows, for the rows matching p_filters in the order of p_order_bys, or for the rows of a query context (p_query_context, whose columns must match the automation's query); with p_run_in_background true it runs in a job. It commits. IS_RUNNING, GET_LAST_RUN_TIMESTAMP, and GET_SCHEDULER_JOB_NAME report on it, and TERMINATE stops a running automation (ABORT is deprecated).

Syntax:

apex_automation.enable | disable | terminate(p_application_id in number default {current}, p_static_id in varchar2)
apex_automation.reschedule(p_application_id in number default {current}, p_static_id in varchar2,
    p_next_run_at in timestamp with time zone default systimestamp)
apex_automation.execute(p_application_id in number default {current}, p_static_id in varchar2,
    p_filters in apex_exec.t_filters default ..., p_order_bys in apex_exec.t_order_bys default ...
    | p_run_in_background in boolean | p_query_context in apex_exec.t_context)

This example needs a session of application 200, page 1. Running the automation for one order queues one reminder email.

Example:

declare
    l_filters apex_exec.t_filters;
    procedure show(p_label varchar2) is
    begin
        for a in (select polling_status, polling_next_run_timestamp as next_run from apex_appl_automations
                   where application_id = 200 and static_id = 'remind-pending-approvals') loop
            dbms_output.put_line(rpad(p_label, 12) || a.polling_status || ', next run: '
                || case when a.next_run is null then '-' when a.next_run > systimestamp + interval '1' day then 'in 2 days'
                        else 'within a day' end);
        end loop;
    end;
begin
    -- the lab's "Remind Pending Approvals" e-mails a reminder per order awaiting approval
    dbms_output.put_line('job: ' || apex_automation.get_scheduler_job_name(p_static_id => 'remind-pending-approvals'));
    show('initially');
    apex_automation.enable(p_static_id => 'remind-pending-approvals');
    show('enabled');
    apex_automation.reschedule(p_static_id => 'remind-pending-approvals', p_next_run_at => systimestamp + interval '2' day);
    show('rescheduled');
    apex_automation.disable(p_static_id => 'remind-pending-approvals');
    show('disabled');

    -- run it now, for one order only (it commits, and queues one e-mail)
    apex_exec.add_filter(l_filters, apex_exec.c_filter_eq, 'ORDER_ID', 2259);
    apex_automation.execute(p_static_id => 'remind-pending-approvals', p_filters => l_filters);
    dbms_output.put_line('running: ' || case when apex_automation.is_running(p_static_id => 'remind-pending-approvals')
                                             then 'yes' else 'no' end
        || ', last run: ' || case when apex_automation.get_last_run_timestamp(p_static_id => 'remind-pending-approvals')
                                       > systimestamp - interval '1' minute then 'just now' end);
    for l in (select status, successful_row_count, error_row_count from apex_automation_log
               where application_id = 200 and automation_static_id = 'remind-pending-approvals'
               order by start_timestamp desc fetch first 1 row only) loop
        dbms_output.put_line('log: ' || l.status || ', ' || l.successful_row_count || ' row(s), '
                             || l.error_row_count || ' error(s)');
    end loop;
end;
/

Output:

job: APEX$AUTOMATION_83045491443097428
initially   Disabled, next run: -
enabled     Active, next run: within a day
rescheduled Active, next run: in 2 days
disabled    Disabled, next run: -
running: no, last run: just now
log: Successful, 1 row(s), 0 error(s)

Running an automation for one filtered row is a handy way to test it, or to offer a "send reminder now" button that reuses the automation's logic. The filters are APEX_EXEC filters, covered in the guide to querying data sources with APEX_EXEC.

LOG_INFO, LOG_WARN, LOG_ERROR, SKIP_CURRENT_ROW, EXIT, and GET_LAST_RUN

These are for the code of an automation's actions. LOG_INFO, LOG_WARN, and LOG_ERROR write to the automation's message log, APEX_AUTOMATION_MSG_LOG. SKIP_CURRENT_ROW moves on to the next row and EXIT stops processing, both with an optional log message. GET_LAST_RUN returns when the running automation last ran, so it can process only rows changed since then.

Code in an automation action:

begin
    if :DISCOUNT_PCT is null then
        apex_automation.skip_current_row(p_log_message => 'Order ' || :ORDER_NUMBER || ' has no discount');
    end if;
    apex_automation.log_info('Reminder for ' || :ORDER_NUMBER || ', changed since ' || apex_automation.get_last_run);
end;

Background Processes: APEX_BACKGROUND_PROCESS

A page process of type Execution Chain can run its child processes in the background, so the page returns immediately; the APEX_APPL_PAGE_BG_PROC_STATUS view lists the executions. The test application's page 8 runs Recalculate Totals this way, and its child process reports progress every hundred orders:

The child process of the chain:

for o in (select order_id from orb_orders) loop
    orb_sales.recalc_order_total(p_order_id => o.order_id);
    l_done := l_done + 1;
    if mod(l_done, 100) = 0 or l_done = l_total then
        apex_background_process.set_progress(p_totalwork => l_total, p_sofar => l_done);
    end if;
end loop;

This example needs a session of application 200, page 8.

Example:

declare
    l_exec apex_background_process.t_execution;
begin
    -- the latest run of "Recalculate Totals", the background execution chain of page 8
    for r in (select execution_id, process_name from apex_appl_page_bg_proc_status
               where application_id = 200 and page_id = 8
               order by created_on desc fetch first 1 row only) loop
        l_exec := apex_background_process.get_execution(p_application_id => 200, p_execution_id => r.execution_id);
        dbms_output.put_line(r.process_name || ': ' || l_exec.state || ', ' || l_exec.sofar || ' of '
                             || l_exec.totalwork || ' orders' || case when l_exec.last_status_message is not null
                                                                      then ' - ' || l_exec.last_status_message end);
    end loop;
end;
/

Output:

Recalculate Totals: SUCCESS, 2281 of 2281 orders

The page itself polls the same values through an Ajax callback to draw a progress bar while the chain runs. Reporting progress in batches, as the child process does every hundred rows, keeps the overhead negligible. Execution chains are covered in the guide to page processes in Oracle APEX.

Conclusion

APEX_HUMAN_TASK creates tasks and does everything the task pages do, from claiming, commenting, and requesting information to approving, rejecting, completing, and delegating, with a permission check for each operation and a full history. APEX_WORKFLOW starts workflow instances that run in the background, suspends and resumes them, changes their variables, continues waiting activities, and terminates or retries them. APEX_AUTOMATION enables, schedules, and runs automations, including for a filtered subset of rows, and gives action code logging and flow control. APEX_BACKGROUND_PROCESS lets a background execution chain report its progress and lets any code read it.