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.
👉
In this post, I will describe how this workflow was built and review lessons learned and tips for getting the most out of APEX workflow.
💪
The project was for a multi-billion dollar company with over 100,000 employees, so this was an industrial-grade test for APEX Workflow.
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.
The user creates a Journal. Once complete, they submit the Journal for approval.
APEX Workflow initiates an APEX Human Approval task, sending emails to appropriate approvers.
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.
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.
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.
The final step marks the Journal as closed and emails interested parties.
👉
Now that we understand the use case, let's dive deep into what I have learned.
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.
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:
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
👉
This section will review what I learned from building the above workflow.
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:
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.
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
apex_session.create_session
(p_app_id => 110, p_page_id => 1, p_username => 'ADMIN');
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.
💡
It would be much easier if apex_workflow.resume could have an admin parameter where you can run it from PL/SQL without logging in as a specific user.
📢
I received an email today, August 16th, 2024, saying that the idea I submitted to the APEX Ideas App to provide Admin-level versions of the Workflow APIs is on the roadmap!
Admin Level Workflow and Task PL/SQL APIs
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.
💡
You can't call PL/SQL APIs like apex_approval.cancel_task or apex_workflow.terminate if you do not have the correct role for a workflow or task instance.
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.
💡
Don't be afraid to customize the pages created by APEX to provide your users with the information they need to see when managing workflows or approving tasks.
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.
🔭
I am looking forward to what is next for Oracle APEX Workflow!
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.
