Introduction

I recently had the opportunity to build a complex integration between APEX and Oracle Fusion Cloud ERP. The integration used APEX Workflow to manage the approval process and orchestrate the Oracle Fusion Cloud ERP integration.

Use Case

The project aimed to allow users to enter and maintain manual General Ledger Journal entries in APEX. The APEX UI is much more responsive and user-friendly than Fusion Applications, improving user experience and efficiency and reducing the overall time to perform month-end close. APEX also allowed us to build additional validations and client-specific features to improve productivity.

Once a journal is completed, it must be approved and integrated into Oracle Fusion ERP Cloud. The diagram below illustrates the process at a high level.

1. The user creates a Journal. Once complete, they submit the Journal for approval.

2. APEX Workflow initiates an APEX Human Approval task, sending emails to appropriate approvers.

3. Once approval is complete, the workflow runs a PL/SQL API, which creates a specially formatted CSV file from the Journal, Base64 encodes it, ZIPs it, and then calls a REST API in Oracle Fusion ERP Cloud.

4. The Oracle Fusion ERP Cloud REST API launches an asynchronous Oracle Enterprise Scheduler (ESS) Job. This job orchestrates the steps necessary to unzip the CSV file, load it into the Journal Interface table, and run the Journal Import job. Once the process is complete, Fusion POSTs the result to an EndPoint, which we provided in the initial call. In our case, this is an Oracle REST Data Services (ORDS) REST Handler.

5. While the Fusion process runs, the APEX workflow is put in a wait state. When Fusion calls the ORDS EndPoint, ORDS calls a PL/SQL procedure in our ATP instance to restart the workflow.

6. The final step marks the Journal as closed and emails interested parties.

Step By Step

The diagram below shows the complete workflow. I will break this down into smaller parts in the following sections.

Submit Journal for Approval

The workflow starts when a user submits a Journal for approval. I initiate the workflow using the apex_workflow.start_workflow PL/SQL API.

  l_workflow_id := 
    apex_workflow.start_workflow 
      (p_static_id      => 'JOURNAL_APPROVAL',
       p_detail_pk      => p_journal_id,
       p_initiator      => UPPER(get_user_email (p_user_id => p_preparer_user_id)),
       p_parameters     => apex_workflow.t_workflow_parameters
                            (1 => apex_workflow.t_workflow_parameter(static_id => 'P_JOURNAL_ID', string_value => p_journal_id),
                             2 => apex_workflow.t_workflow_parameter(static_id => 'P_SUBMITTER_USER_ID', string_value => p_preparer_user_id),
                             3 => apex_workflow.t_workflow_parameter(static_id => 'P_APPROVER_LIST', string_value => l_approver_list),
                             4 => apex_workflow.t_workflow_parameter(static_id => 'P_APPROVAL_GROUP_LIST', string_value => p_approval_group_ids)),
       p_debug_level => apex_debug.c_log_level_info);

• p_detail_pk - The value you pass here is used for the APEX$WORKFLOW_DETAIL_PK substitution string and can be found in the column detail_pk in the APEX view apex_workflows.

• p_initiator - This parameter is useful to control what value is used as the workflow initiator. By default, APEX will use the currently logged-in user (APP_USER). In my case, I always wanted the person who prepared the journal to be tagged as the workflow initiator, even if someone else submitted the workflow. The initiator is crucial because only the initiator can withdraw a workflow, and the initiator receives requests for information.

• p_parameters - This is where you set your workflow parameters. Workflow parameters are like global variables that persist for the duration of the workflow.

• p_debug_level - This parameter allows you to enable debugging for the workflow's life. Enabling debug here will allow any apex_debug statements you include in workflow Execute Code activities will produce debug messages in the apex_debug_messages view. Debug levels (info, warn, error, trace) work exactly like they do for regular APEX debug messages. You will also notice that the apex_debug_messages view includes an additional column workflow_instance_id. This column helps track down debug messages for a specific workflow instance.

Now, let's get into the workflow steps...

Before starting the approval process, I needed to set the status of the Journal and clean up from any previous approval attempts. This is done using an 'Invoke API' activity to call a PL/SQL API.

Journal Approval

Journal Approval was achieved using several workflow activities.

• Send Request for Approval Email

  • The first step is an 'Invoke API' activity, which calls a PL/SQL procedure to send emails to the potential approvers of the Journal. You may wonder why I did not use a declarative option, i.e., the 'Send E-Mail' activity. Several similar emails needed to be sent during the workflow's life, and the email template had 15 placeholders. Using a PL/SQL API to handle the email allowed me to code a single procedure to build the email template placeholders JSON and improve re-use. I would have used the standard component if the email had simpler and fewer placeholders.

    • 💡 It would be an excellent addition to the 'Send E-Mail' activity to call a function to return the placeholders' JSON to the activity.

• Journal Approval

  • The second step is the approval step. Approval management is handled by an APEX 'Task Definition'. Task definitions have been available in APEX for a while, so it was a natural progression to incorporate these components into the workflow via the 'Human Task - Create' activity.

  • An important aspect of the 'Human Task - Create' activity is configuring the Settings. Here is a screenshot of the settings for the 'Human Task - Create' activity in the workflow:

    

    • 'Task ID Item' is a workflow variable that APEX populates with the ID of the Human Task instance created when it starts the Human Task activity. This is very useful when you are querying the APEX views, as it allows you to tie the workflow instance in apex_workflows with the Human Task in the view apex_tasks.

    • 'Details Primary Key Item' allows you to pass in the ID of the transaction to which the approval is related. This populates the APEX$TASK_PK substitution string in the Task Definition and can also be found in the column detail_pk of the APEX view apex_tasks.

    • 'Outcome' APEX sets the workflow variable you reference here with the result of the 'Human Task - Create' activity. This can be Approved or Rejected.

    • 'Owner' When someone Approves/Rejects or Claims a Task, APEX sets the workflow variable you reference here with the owner's username.

• Approval Timeout

  • Handling timeouts of 'Human Task - Create' activities within a Workflow can be tricky. Task Definitions have their own timeout functionality.

    

  • A 'Human Task - Create' activity within a workflow also has a Deadline:

    

  • Per the documentation, you should not need to set the Deadline in the Task Definition, as the Workflow Deadline takes precedence. I found that I had to set the Deadline in the Task Definition for the 'Expire' activity to fire when the activity expires.

• Set Approval Result

  • The final step involves an 'Invoke API' activity which calls a procedure to update the Journal record with the result of the approval (Approve / Reject).

Approved/Rejected Emails

I am using a 'Switch' activity to branch the workflow depending on whether the journal was approved or rejected. This results in either an approved or rejected email being sent.

The switch compares the 'Outcome' variable V_TASK_OUTCOME from the 'Human Task - Create' activity:

Send Journal to Fusion

If the Journal is approved, the next step is to build a specially formatted CSV file, ZIP it, Base64 encode it, and then call the Oracle Fusion ERP REST API called erpintegrations to load the data. This is handled by a PL/SQL procedure, which is called the 'Send Journal to Fusion' Invoke API activity.

Base 64 encoding, zipping, and sending the file to Fusion is made much easier with three APEX PL/SQL APIs:

• apex_web_service.blob2clobbase64 - Used to Base 64 Encode the CSV File.

• apex_zip - Used to ZIP the Base 64 encoded CSV File.

• apex_web_service.make_rest_request - Used to call the erpintegrationsREST API.

If the erpintegrationsREST API call is successful, we put the workflow in a wait state using a 'Wait' activity. This is because the processing on the Fusion side is Aynchronous. In this example, I am having the workflow wait up to 30 minutes.

We need to have the workflow wait for processing to complete and for Fusion to call an ORDS REST API.

Handling the Callback

Once called by Fusion, the ORDS REST API creates an APEX session and calls theapex_workflow.continue_activity to progress the workflow to the next step.

apex_workflow.continue_activity
 (p_instance_id     => lr_callback_rec.workflow_id,
  p_static_id       => 'FUSION_CALLBACK_TIMEOUT',
  p_activity_params => l_activity_result);

Completing the Workflow

All that remains now is to determine if the Fusion import was successful and either:

• Mark the Journal as completed and send an email to the preparer.

• OR, if Fusion encountered an error importing the Journal, mark the Journal as failed and email the preparer with details.

Lessons Learned

Think About Design

As with most development work, it usually pays to take a step back and think about design before building. At a minimum, draw out the workflow using your favorite drawing tool and use the diagram to review the activities with your users. It will save you time in the long run.

Exposing Logic in the Workflow

There are two approaches to designing workflow:

1. Expose all of the decisions and logic in the workflow diagram and call smaller, more atomic PL/SQL procedures. This results in a more complex workflow diagram, but it is easier to spot where things go wrong and visualize the workflow process. The downside is that you will make more changes to your workflow.

2. Have fewer steps in the workflow and incorporate logic into fewer, more complex procedures. This often leads to fewer changes to your workflow and more changes to your PL/SQL logic, which is easier to deploy. The downside is the workflow logic is obfuscated within your PL/SQL.

I see a happy medium where the major steps in the process are visible in the workflow, and less consequential logic and decision points can be incorporated into your PL/SQL.

Workflow Parameters

It is important to consider what parameters you need for a workflow. Workflow parameters are set when you start an instance of the workflow and persist for the life of the workflow instance.

I usually create parameters for data you need to capture when the workflow starts that could change during the workflow's life. I also create parameters for values that I think I may use frequently.

In this example, I have parameters to store the approval groups and approvers selected when the workflow started. I also have a parameter for the Journal ID and the user ID of the user who submitted the workflow.

Learning APEX Workflow

Starter App

I learn best by seeing working examples. The sample application 'Workflow, Approvals, and Tasks' gave me a head start.

Workflow FAQ

I found the FAQ documentation very helpful. Especially when trying to understand the dependencies between Tasks and Workflow.

Implications of Application Import

Suspended Workflows

When you import an application with a running workflow, APEX suspends all active workflows. Resuming suspended workflow instances can require a lot of clicking. Alternatively, you can create a script to resume suspended workflow instances.

DECLARE
  CURSOR cr_active_workflows IS
    SELECT workflow_id
    FROM   apex_workflows
    WHERE  application_id = 110
    AND    state          = 'Suspended';
BEGIN
  -- Create an APEX Session.
  -- Note: User must be an ADMIN for the workflow instances.
  apex_session.create_session 
    (p_app_id => 110, p_page_id => 1, p_username => 'ADMIN');
  -- Loop through Suspended Workflow Instances.
  FOR r_wflow IN cr_active_workflows LOOP
    BEGIN
      apex_workflow.resume (p_instance_id => r_wflow.workflow_id);
      dbms_output.put_line('Resumed ['||r_wflow.workflow_id||']');
    EXCEPTION WHEN OTHERS THEN
      dbms_output.put_line('* Error Resuming ['||r_wflow.workflow_id||'] '||SQLERRM);
    END;
  END LOOP;
  COMMIT;
END;

Because the user must be an ADMIN for the workflow instance to resume it, you must decide which user to use to create your APEX session. This query will help you determine the ADMIN users assigned to a workflow.

SELECT ap.participant, COUNT(*) count_workflows
FROM   apex_workflow_participants ap
,      apex_workflows aw
WHERE  aw.workflow_id            = ap.workflow_id
AND    aw.application_id         = 110
AND    aw.workflow_def_static_id = 'JOURNAL_APPROVAL'
AND    ap.participant_type_code  = 'ADMIN'
GROUP BY ap.participant;

Other

For other implications, read this post from Ralf Mueller. Search for 'Task Management'.

Workflow & Task Roles

It is important to understand the different workflow roles. Certain activities can only be performed if you have a particular role for a particular workflow or task instance.

Workflow Participants

• Workflow Owners - Can start and terminate an instance of the workflow. Can retry a faulted workflow.

• Workflow Administrators - Can start, terminate, suspend, resume, and retry a workflow instance. They can also update the variables of a workflow instance.

Task Participants

• Task Initiators - Can start new tasks or provide more information about existing tasks.

• Potential Owners - Can claim unassigned tasks. Tasks can have multiple potential owners.

• Actual Owners - Can make changes to their assigned tasks, including requesting more information about the task, approving or rejecting it, and updating the task's parameters.

• Business Administrators - Can renew expired tasks, reassign tasks to new owners, remove existing potential owners, cancel tasks, or update the task priority.

Application Runtime UI

APEX provides two components that give you an excellent head start in managing workflows and tasks.

The workflow console, which allows you to monitor and manage workflows:

The Unified Task list can create a Pending Approvals page or a My Approvals page for Human Task management. This includes activities like Withdraw, Request for and Respond to Information, and Approve and Reject.

Workflow Development Lifecycle

Spend some time understanding the workflow development lifecycle. This post from Ralf Mueller explains it very well.

Substitution Strings

Use Workflow and Task Substitution strings to easily reference important information about a running workflow instance.

APEX Views

The Workflow and Task runtime views are very helpful when troubleshooting issues with workflow.

Workflow & Task States and Transitions

Understanding how workflows and tasks progress through their lifecycle is important.

Conclusion

Having used Oracle Workflow in Oracle e-Business Suite (EBS) for many years, I was interested to see how the newcomer Oracle APEX Workflow would stand up. I have to say that APEX Workflow is every bit as flexible and provides all of the features you need to manage human task approvals and integration orchestrations.

Flows for APEX

As I am discussing enterprise workflow orchestration, I would be remiss if I did not mention Flows for APEX.

Flows for APEX is a powerful workflow engine extension for Oracle APEX applications. It allows you to model your business processes with BPMN, develop your process steps in APEX, and monitor your running processes with the Flows for APEX application.

If you are considering Flows for APEX and APEX Workflow, this post from Ralf Mueller provides an excellent comparison.