Building a Conditional Workflow and Workflow Monitoring Console in Oracle APEX

A Practical Guide to Oracle APEX Workflow Automation, PL/SQL Activities, Workflow Variables, Conditional Branching, and Workflow Monitoring

1. Introduction

Workflow automation is an important part of modern enterprise applications. Many business processes require more than simply collecting information and saving it into a database. They need to evaluate business conditions, execute different actions, maintain execution history, and provide visibility into the current status of each process.

Oracle APEX provides native Workflow capabilities that allow developers to design and manage process automation directly within an APEX application.

In this practical project, I explored Oracle APEX Workflow by building a conditional workflow that evaluates an input amount and executes different PL/SQL activities based on the result.

The application also includes a Workflow Console that allows users to monitor initiated workflows, inspect workflow activities, review audit history, view workflow variables, and manage workflow execution through available controls.

The objective of this project is to demonstrate how Oracle APEX Workflow can be integrated with application pages and database logic to build maintainable automation solutions.

2. Project Overview

The application is named WORKFLOW and contains a conditional workflow called CHECK_AMOUNT.

The main business scenario is straightforward:

• A user enters an amount on the Submit Page.

• The application starts the CHECK_AMOUNT workflow.

• The entered amount is passed into a workflow input variable.

• A Switch activity evaluates whether the amount is greater than 1,000.

• Based on the condition, the workflow executes one of two PL/SQL activities.

• The workflow reaches its End activity.

• The execution can be inspected through the Workflow Console.

This example demonstrates how business rules can be separated from the page submission process and managed through a visual workflow definition.

3. Application Architecture

The application is organized around three main functional areas:

1. Workflow Submission

The Submit Page collects the amount and starts the workflow using a native APEX Workflow page process.

2. Workflow Definition

The CHECK_AMOUNT workflow contains a Start activity, Switch activity, two PL/SQL activities, and an End activity.

3. Workflow Monitoring

The Console and Workflow Console provide access to workflow instances, activity details, execution history, variables, and workflow management actions.

High-Level Process Flow

User Opens Submit Page
         |
         v
   Enter Amount
         |
         v
   Click Submit
         |
         v
 Start CHECK_AMOUNT
         |
         v
   Start Activity
         |
         v
   Switch Activity
         |
         v
 Is Amount > 1000?
       /     
     YES      NO
      |        |
      v        v
 Greater    Less Than
 Than 1000    1000
 PL/SQL       PL/SQL
 Activity     Activity
              /
             /
          v
       End Activity
          |
          v
       Completed

4. Creating the Workflow Definition

The application contains a workflow definition named:

CHECK_AMOUNT

Its static ID is:

check-amount

The workflow also has a version named CHECK AMOUNT.

The exported application identifies this workflow version as being in the Development state.

The workflow uses an input variable named AM_PARAMETER.

Workflow Variable Configuration

Property	Configuration
Label	AM PARAMETER
Static ID	AM_PARAMETER
Direction	IN
Data Type	VARCHAR2
Required	No

Workflow variables allow information to be passed into workflow activities without relying directly on page-item session state throughout the process.

In this example, the amount entered by the user is mapped to the workflow input variable.

5. Designing the Workflow Activities

The workflow definition contains the following activities:

Activity	Type	Purpose
Start	Workflow Start	Begins workflow execution
CEHCK AMOUNT	Workflow Switch	Evaluates the amount condition
Greater than 1000	PL/SQL	Executes the greater-than branch
less then 1000	PL/SQL	Executes the alternative branch
End	Workflow End	Completes the workflow

The workflow uses a Switch activity to evaluate the input variable.

Switch Activity Condition

The condition configured in the exported application is:

:AM_PARAMETER > 1000

This expression checks whether the supplied amount is greater than 1,000.

The workflow has two outgoing branches:

• Greater than 1000

• less then 1000

This approach allows the workflow to direct execution to different activities according to a business condition.

6. Implementing PL/SQL Activities

After evaluating the condition, the workflow executes the corresponding PL/SQL activity.

Greater Than 1000 Activity

The exported application contains:

INSERT INTO workflow_demo
VALUES (:AM_PARAMETER, 'Greater than 1000');

This activity stores the supplied amount along with the result message.

Less Than 1000 Activity

The alternative activity contains:

INSERT INTO workflow_demo
VALUES (:AM_PARAMETER, 'Less than 1000');

Both activities execute locally in the application’s database environment.

Supporting Database Table

The export references WORKFLOW_DEMO, but it does not include the table’s CREATE TABLE statement.

For a fresh demonstration environment, the following is an example of a compatible two-column table:

CREATE TABLE workflow_demo (
    amount_value   NUMBER,
    result_message VARCHAR2(100)
);

For better maintainability, explicit column names can be used in the activity code:

INSERT INTO workflow_demo (
    amount_value,
    result_message
)
VALUES (
    :AM_PARAMETER,
    'Greater than 1000'
);

Use the corresponding message in the alternative activity.

Implementation note: The exported workflow variable is defined as VARCHAR2, while the Switch expression performs a numeric comparison. For a production amount-based workflow, I recommend using a numeric workflow variable where supported by the configuration, and validating the input before starting the workflow.

7. Starting the Workflow from an Oracle APEX Page

The application contains a page named Submit Page (Page 2).

It includes the following items:

• P2_AMOUNT – accepts the amount entered by the user.

• P2_WORKFLOW – receives the workflow instance ID returned by the workflow process.

The page also contains a Submit button.

When the user clicks Submit, an After Submit process starts the workflow.

Native Workflow Page Process

The exported application uses the native process type:

NATIVE_WORKFLOW

Its process configuration includes:

Property	Value
Process Name	check amount
Process Point	After Submit
Process Type	Native Workflow
Operation	START
Workflow Definition	CHECK_AMOUNT
Return Workflow ID Item	P2_WORKFLOW

The workflow input parameter is mapped as follows:

Workflow Variable : AM_PARAMETER
Value Type        : ITEM
Page Item         : P2_AMOUNT

This mapping is important because it connects the APEX page input with the workflow definition.

The workflow can therefore start using the amount submitted by the user without requiring a separate custom procedure to initiate the process.

8. Monitoring Workflows with the APEX Workflow Console

Creating a workflow is only one part of process automation. Monitoring its execution is equally important.

The application includes a page named console (Page 3).

This page contains an Initiated by Me report and Smart Filters.

The report uses the Oracle APEX workflow API:

SELECT workflow_id,
       workflow_def_name,
       workflow_def_app_name,
       title,
       initiator,
       state_code,
       created_on,
       created_ago,
       created_ago_hours,
       last_updated_on,
       badge_text,
       badge_state
  FROM TABLE (
       apex_workflow.get_workflows (
           p_context => 'INITIATED_BY_ME'
       )
  );

This query retrieves workflow instances initiated by the current user.

The report displays useful information, including:

• Workflow ID

• Workflow definition

• Workflow title

• Initiator

• Workflow state

• Creation date

• Last updated date

• Status badge

Smart Filters

The Console also includes Smart Filters for searching and narrowing workflow records.

The exported application includes filters related to:

• Application

• Initiator

• Initiated time

• Workflow state

• Workflow type

• Search

This makes it easier to locate a particular workflow instance without manually searching through database records.

9. Workflow Details and Activity Monitoring

When a workflow is selected from the Console, the application opens the workflow console page (Page 4) in a modal drawer.

This page provides detailed information about the selected workflow.

It includes several important regions.

Subject

The Subject region retrieves workflow information through:

SELECT title,
       workflow_def_name,
       initiator,
       created_ago,
       badge_text,
       badge_state
  FROM TABLE (
       apex_workflow.get_workflows (
           p_context     => 'SINGLE_WORKFLOW',
           p_workflow_id => :P4_WORKFLOW_ID
       )
  );

It displays the workflow title, definition, initiator, creation information, and current state.

Activities

The Activities region retrieves activity-level execution information from:

APEX_WORKFLOW_ACTIVITIES

The query includes:

• Activity ID

• Activity type

• Activity name

• Execution state

• Error message

• Due date

• Retry count

• Start time

• End time

• Branch name

It also checks related workflow and activity records to identify invoked workflows and parallel branches.

This is useful when investigating how an individual workflow instance is progressing.

Activity Audit

The Activity Audit region uses:

SELECT created_by,
       created_on,
       action,
       display_msg
  FROM apex_workflow_audit
 WHERE workflow_id         = :P4_WORKFLOW_ID
   AND current_activity_id = :P4_ACTIVITY_ID
 ORDER BY audit_id;

This provides activity-level audit information, including the user, date, action, and message.

History

The History region displays workflow-level audit records:

SELECT display_msg,
       created_by,
       created_on
  FROM apex_workflow_audit
 WHERE workflow_id = :P4_WORKFLOW_ID
   AND current_activity_id IS NULL
 ORDER BY audit_id DESC;

This provides a chronological view of workflow-level events.

10. Viewing Workflow Variables and Parameters

The Workflow Console also includes separate regions for workflow variables and parameters.

Variables

The Variables region retrieves workflow variables using:

SELECT static_id,
       label,
       display_value
  FROM apex_workflow_variables
 WHERE workflow_id = :P4_WORKFLOW_ID
   AND variable_type = 'VARIABLE'
 ORDER BY label;

Parameters

The Parameters region retrieves workflow parameters using:

SELECT label,
       display_value
  FROM apex_workflow_variables
 WHERE workflow_id = :P4_WORKFLOW_ID
   AND variable_type = 'PARAMETER'
 ORDER BY label;

The application also includes an Edit Variable interface.

The availability of the edit operation is checked through APEX_WORKFLOW.IS_ALLOWED, so the application can respect the workflow engine’s permitted operations.

11. Visual Workflow Diagram

One of the useful features in this application is the native Workflow Diagram region.

The region uses:

NATIVE_WORKFLOW_DIAGRAM

It is configured with the selected workflow instance ID:

P4_WORKFLOW_ID

The diagram supports:

• Workflow visualization

• Navigation

• Export

• Activity-level inspection

A visual representation makes it easier for developers and administrators to understand the workflow structure and identify where an activity is currently executing.

12. Workflow Management Actions

The Workflow Console includes several management actions.

The exported application defines buttons for:

• Resume

• Retry

• Suspend

• Terminate

• Update Variable

These actions are useful when managing workflow instances during execution or troubleshooting.

The application checks the permitted operation through APEX_WORKFLOW.IS_ALLOWED before displaying relevant controls.

This is an important design consideration because workflow management operations should follow the permissions and current state supported by the workflow engine.

13. Understanding Oracle APEX Workflow APIs and Views

This project demonstrates the use of several native Oracle APEX Workflow APIs and views.

API / View	Usage in the Application
APEX_WORKFLOW.GET_WORKFLOWS	Retrieves workflow instance information
APEX_WORKFLOW.IS_ALLOWED	Checks whether an operation is permitted
APEX_WORKFLOW_VARIABLES	Displays workflow variables and parameters
APEX_WORKFLOW_ACTIVITIES	Displays workflow activity execution details
APEX_WORKFLOWS	Used to identify related workflow instances
APEX_WORKFLOW_AUDIT	Displays workflow and activity audit history

Using these native capabilities reduces the need to build a separate custom monitoring framework for basic workflow administration.

14. Practical Business Use Cases

The amount-based example is intentionally simple, but the same workflow concepts can be applied to more meaningful business scenarios.

For example:

Expense Processing

A submitted expense amount can be evaluated against company-defined limits.

Purchase Requisition Processing

A workflow can route requests according to their amount, department, or other business rules.

Invoice Processing

Invoice attributes can be evaluated before executing the next business activity.

Financial Operations

Transaction values can be checked against defined business conditions before additional processing.

These are potential extensions of the concepts demonstrated in this project; they are not additional approval flows implemented in the current export.

15. Important Development Considerations

While developing workflow-based applications, I recommend keeping the following points in mind:

1. Validate input values: Ensure that amount fields contain valid numeric values before workflow execution.

2. Choose appropriate variable data types: Use numeric types for numeric business calculations where supported.

3. Use meaningful activity names: Clear activity names make workflow monitoring easier.

4. Handle errors carefully: Review activity states and error messages through the Workflow Console.

5. Maintain audit visibility: Audit records are valuable for troubleshooting and traceability.

6. Check permitted operations: Use workflow operation checks before exposing management actions.

7. Publish and test workflow versions: The version in this project export is in Development state; validate and publish the appropriate version before production deployment.

8. Test boundary conditions: Test values below, equal to, and above the configured threshold.

16. What I Learned from This Project

Working on this project helped me understand how Oracle APEX Workflow connects visual process design with database execution.

Some of the key concepts I explored include:

• Creating a native Oracle APEX Workflow definition.

• Configuring workflow versions and input variables.

• Designing Start, Switch, PL/SQL, and End activities.

• Implementing conditional workflow branching.

• Starting workflows directly from an APEX page.

• Mapping page items to workflow variables.

• Monitoring workflow instances through APEX_WORKFLOW.GET_WORKFLOWS.

• Inspecting workflow activities and execution history.

• Understanding workflow variables and parameters.

• Using the native Workflow Diagram region.

• Managing workflow instances through supported workflow operations.

17. Conclusion

Oracle APEX Workflow provides a useful way to introduce process automation into database-driven applications.

In this project, I built a conditional amount-checking workflow, connected it to an APEX submission page, implemented PL/SQL activities, and explored the native Workflow Console for monitoring and administration.

The most valuable part of this exercise was understanding how workflow definitions, variables, activities, transitions, and monitoring capabilities work together.

For developers working with Oracle APEX, exploring native Workflow capabilities is a practical step toward building more structured, maintainable, and scalable business applications.

I will continue exploring advanced workflow scenarios and applying these concepts to real-world Oracle APEX solutions.

18. Oracle ACE Associate – Knowledge Sharing Contribution

This article is part of my ongoing technical learning and knowledge-sharing activities within the Oracle ecosystem.

By documenting practical Oracle APEX implementations, sharing technical explanations, and publishing reusable development examples, my goal is to help other developers understand and apply Oracle technologies in real projects.

I believe that sharing practical knowledge is an important way to contribute to the Oracle community.