Tag: Workflow

APEX workflow is a new feature I was keen to experiment with. In a familiar visual designer, you automate a business process with a flow diagram. Its sequence of activities can include business logic, conditional branching, local or remote API calls, time delays, email, and push notifications. It’s also easy to wait for approvals or action tasks end users need to complete. Should your needs go beyond the core activity set, you can develop custom ones or use community-created activities using process plug-ins. I’m eager to share my initial experiences to offer insights I gathered along the way. You’ll find links at the end to download the sample app and to read additional APEX Workflow articles of interest by my colleagues.

(Technical Note: to see a larger version of any figure, right click on it and choose Open Image in New Tab from the context menu…)

Calculated Simplicity

To learn APEX workflow, I thought of the simplest process a math major’s mind could muster: adding two numbers. “Hey! That’s not a business process!” you rightly note, but humor me. This approach helped me understand all the “moving parts” first before embellishing my simple calculator workflow to include decisions and notifications. I decided to start small.

I created an eba_demo_simplecalc package and wrote the following compute() function to represent some parameterized business logic that my app needs to perform. It accepts two number operands and the operation to perform on them (plus, minus, multiplication, or division):

-- In package eba_demo_simplecalc
function compute(
    p_operand1  in number,
    p_operation in varchar2,
    p_operand2  in number)
    return         number
is
begin
    return case p_operation
                when '+' then p_operand1 + p_operand2
                when '-' then p_operand1 - p_operand2
                when '*' then p_operand1 * p_operand2
                when '/' then p_operand1 / p_operand2
           end;
end compute;

Then I built the simple Calculate Without Workflow page below to start with a baseline calculator page that did not use workflow at all. It lets a user enter two number values and has a select list with the four available operations. A display-only page item shows the computed result after the user clicks the (Calculate) button.

As shown below, this simple page has a single entry in the Processing section that invokes the compute() function using an Invoke API page process. It has a Server-side Condition to run when the Calculate button is pressed, and I configured its three parameters to get their values from the corresponding page items and return the function result into the display only page item. I quickly had a working calculator page that let a user add, subtract, multiply, or divide any two numbers. In this baseline page, no data is stored in any table. The operands, operation, and computed result only live in APEX session state.

Reusing Logic in a Business Process

Next, I learned how to reuse my compute() business logic from a workflow. Under Shared Components > Workflows, I clicked (Create) to get started. I was already familiar with the Invoke API page process, so using the Property Editor I instinctively changed the type of the default activity in the visual designer from Execute Code to an Invoke API type activity instead. The Workflow Designer looks and works like Page Designer, so I already knew how to use it. Next, I needed to configure the compute() function’s inbound parameters and its function return, so that made me think, “Where will these values come from and where will the computed result go?”

A workflow is a “headless” process that the APEX engine runs in the background using a session that’s distinct from those of end users accessing pages. So, the workflow has no access to pages or page items. Instead, it accepts initial values at start time using parameters. These values are read-only. While the workflow is running – which can last for a few seconds or many weeks depending on the business process – APEX automatically manages the storage and retrieval of workflow variable values you can change over time. As shown in the figure above, my Simple Calc (Only Params) workflow accepts three parameters to let the workflow initiator pass in two number operands and the operation. It also has one variable to store the computed result of the calculation. Both parameters and variables have a static ID that you use to reference their value as substitution strings or bind variables. They also have a display label that shows in the navigator tree to improve readability. Here, I opted for parameter static IDs of P_OPERAND1, P_OPERATION, and P_OPERAND2 and a variable static ID of V_RESULT. The P_ and V_ prefixes are not mandatory, but they helped me remember which names were parameters and which were variables.

The workflow Title property – which determines how end-users will see the workflow instance in the console – can reference parameters as substitution strings to include dynamic info. Therefore, I set my Title to the string below:

Calculate &P_OPERAND1. &P_OPERATION. &P_OPERAND2. (Using Only Params)

Workflow Versions

As business requirements change, a process can evolve over time. APEX supports this using workflow versions. When you create a new workflow the Workflow Designer also creates the first version for you. While the name can be anything meaningful, I just named mine “1.0“. Notice it shows in the workflow navigator tree as “1.0 [Dev]” to indicate this version is in development mode.

Very observant readers may have noticed my workflow’s Result variable is actually indented under the “1.0 [Dev]” version node in the navigator tree. Since the set of variables can change over time as well, they are part of the workflow definition that’s included in the workflow version. This is why they’re called “version variables.”

While building your app, you can use your workflow in development mode when running from the APEX Builder. However, to run the app outside of the APEX Builder or to deploy it another environment, you will need to activate the development version of all workflows in use before end-users can use them. For simplicity, in this article we’ll just keep all the workflows to a single development-mode version and always run the app from the builder.

Workflow Definition Versus Instance

The workflow version diagram represents the definition of a business process. It starts with a start activity and shows how to continue on to other activities until eventually concluding in an end activity. It will always have a single starting point, but may allow multiple possible paths to reach an end activity. Think of it like a recipe, blueprint, or template that describes how each particular concrete example of the workflow will behave.

A workflow instance is a particular example of the workflow definition that your application starts, usually by passing in one or more parameter values that provide the context information that makes this example unique. The APEX workflow designer lets you specify the definition and the Workflow page process (or APEX_WORKFLOW PL/SQL API) lets your application start a new workflow instance that will follow the activities you laid out in the designer. Now let’s put these abstract ideas into practice in this example. To perform a calculation using my Simple Calc (Only Params) workflow definition, I needed to create a page that uses the Workflow page process to start a new instance of the workflow to perform a specific calculation.

Calculating Using the Workflow

I copied my original No Workflow page to a new Workflow Params Only page and changed the page process type from Invoke API to be type Workflow instead as shown below. In the Settings section of the Property Editor, I set the Type of workflow action to Start since clicking the (Calculate Using Workflow) button should start the workflow. I chose the Definition of Simple Calc (Only Params), the name of the workflow I created above. Finally, I configured each of the three workflow parameters to get its value from the corresponding page item.

Running the page, after entering two operands and an operation and clicking the (Calculate Using Workflow) button, as shown below, it appears that nothing happens. However, behind the scenes the workflow has started. The result is not immediately visible in the page as it was in my original No Workflow page. The workflow is now responsible for computing the result and it is doing that in the background. So we need a different way to see the value of the result, since it lives inside the workflow version variable V_RESULT with display label Result. In the next section we’ll create a workflow console page to inspect the workflow instance and see the result of the calculation in the Result variable value.

Reviewing Results in the Console

A Workflow Console page lets you see the progress of workflows. You create one using the Create Page wizard by choosing the Workflow Console page type. The wizard will create two pages: one lists the workflows, and the other shows the details of a selected workflow in the list. As shown below, I named the list page Workflows Started and the form page Workflow Detail. The Report Context setting determines whether the list page shows the current user the workflows they initiated, those in which they are a participant, or the ones they can administer. I chose “Initiated by Me” so it will show all the workflow instances I start.

Refreshing the browser and navigating to this new Workflows Started page, I see the list below containing the one workflow instance I have started so far. We can see from the badge that it has already completed. To see the workflow instance’s details, we just click on the title whose text we notice was computed using our Title property we configured above that used substitution strings.

As shown below, the Workflow Detail modal drawer page appears showing the workflow activities completed and the value of the Result variable.

Seeing Workflows for One App or All?

One small change I made to the default Workflows Started page was to adjust the SQL query of its Initiated by Me region of type Content Row so that it only showed the workflow instances related to the current application. By default it shows workflows from all apps in the workspace, but that’s not what I needed for this simple learning app, so I tweaked the query to look like the following to pass the :APP_ID bind variable as the value of the p_application_id parameter to the pipelined table function that returns the workflow instance information:

select ..., 
       title,
       initiator,
       ...
  from table ( apex_workflow.get_workflows (
                   p_context => 'INITIATED_BY_ME',
                   -- Leave out this param to show workflows
                   -- from all apps in the workspace
                   p_application_id => :APP_ID
                   ) )

Exploring a Faulted Workflow

There’s not much that can go wrong when performing a simple math calculation, but there is one thing: dividing by zero! I wanted to explore what would happen if I asked my simple workflow-based calculator to perform a calculation that would generate an error. I also wanted to learn how the workflow administrator could fix a faulted workflow in the console by editing a problematic variable value involved and retrying the faulted activity. Recall from above that the value of a workflow parameter like P_OPERAND2 is read-only. So I needed to slightly change the workflow to:

1. Add a second version variable V_OPERAND2 of type NUMBER,
2. Initialize its default value from the value of the parameter P_OPERAND2, and
3. Adjust the Invoke API activity to pass V_OPERAND2 to the compute() function

After doing this, I entered the values you see below to compute 5678 divided by zero (0) and clicked (Calculate Using Workflow).

Again, it appears that nothing has happens in the page, but as shown below the Workflows Started console page shows us what’s happened. The workflow started and faulted due to the divide by zero error.

Clicking on the title of the faulted workflow shows the details you see in the figure below. We see the error that caused the fault, and can edit the value of the V_OPERAND2 variable (with label Operand2) by clicking the Edit link to the right of the variable.

We can fix the error by entering a corrected value for the workflow version variable. For example, let’s enter the value 1 instead and click (Apply Changes) as shown below…

Lastly, we can click (Retry) as shown below to have the APEX engine try processing the faulted activity again…

The workflow now completes successfully and we can see the result by clicking on the workflow title in the Workflows Started page as shown below.

Associating a Workflow to a Row by PK

In the previous workflow, we passed in all of the initial information required using parameters. However, very often the information a workflow needs is already contained in the columns of a table in your app schema. In those cases, the only parameter value you may need to pass is a primary key from the table in question so it’s clear to which row the new workflow instance is associated.

In fact, this use case is so common that you don’t even need to create your own workflow parameter to handle it. Every APEX workflow has an optional, predefined parameter named APEX$WORKFLOW_DETAIL_PK whose display label is Details Primary Key. I set out next to investigate using this feature with my simple calculator workflow.

The first step was creating an EBA_DEMO_CALCULATION table shown below to store the calculation rows to which each workflow instance will be associated by primary key. The table has a primary key (ID) and then columns for the two operands, the operation, and the result of the calculation.

Next I created a Calculations Interactive Report page with an associated form page named Calculation. These pages let me quickly list, create, edit, and delete calculation rows. Then I created the Simple Calc (Details PK) workflow you see below. To associate it with the EBA_DEMO_CALCULATION table, as shown below I clicked on the “1.0 [Dev]” version node in the navigator tree on the left, and then in the Additional Data section of the Property Editor, set the table name and configured the primary key column (ID) to use.

This table name configuration is one of the two required ingredients to make a table-associated workflow function correctly. The other important step occurs in the configuration of the page process that starts this workflow.

In the Calculation form page, the wizard has already created a page process of type Form – Automatic Row Processing (DML) to save the calculation row, and a Close Dialog page process to dismiss the dialog and return to the calling page. I added a new Workflow page process just between these two. As shown below, I configured it to start workflow Simple Calc (Details PK), but this time I’ve added one additional configuration value that makes a big difference. I set the Details Primary Key Item to P4_ID. This is the name of the page item that will provide the primary key value of the EBA_DEMO_CALCULATION table row to which the new workflow instance will be associated. The P4_ID page item is the primary key in the form region that creates, edits, or deletes a row in the EBA_DEMO_CALCULATION table. However, since its item type is Hidden it does not appear on the visual layout.

Now we have a workflow with a Details Primary Key Item value being passed in for the built-in APEX$WORKFLOW_DETAIL_PK parameter, and we’ve configured the Additional Data on the workflow version so APEX knows to which column in what table this primary key parameter value applies. This combination produces an interesting benefit. The APEX engine will automatically make all column values available by name for the appropriate EBA_DEMO_CALCULATION table row for the duration of the workflow. This lets us easily refer to the value of the first operand using its OPERAND1 column name, the second operand using OPERAND2 and the operation to perform using the column name OPERATION. As shown below, I’ve configured the parameters of the Calculate Result Invoke API activity in the workflow using these column name values.

The second activity Store Result is another Invoke API activity that calls the UPDATE_RESULT procedure in the EBA_DEMO_SIMPLECALC package to store the computed result in V_RESULT back into the RESULT column of the appropriate EBA_DEMO_CALCULATION table row. For procedure parameter p_calculation_id it passes the item value APEX$WORKFLOW_DETAIL_PK and for parameter p_result it uses the V_RESULT item’s value.

Also notice that I introduced a single P_SUBJECT parameter (with label Subject) to let the page that starts the workflow pass in the entire subject line value. I did this since the workflow title cannot reference the workflow version-level values like variables or details table column names as substitution variables. By introducing the P_SUBJECT parameter and configuring the workflow title to be &P_WORKFLOW. I can easily set the workflow instance subject line to a useful value including dynamic contents in the page that starts the workflow instance to perform the calculation.

The figure below shows how the value of the P_SUBJECT parameter is set in the Calculation page’s workflow start page process. It includes the values of page items to achieve an appropriate subject, which the new workflow instance uses as its title.

I then used the new Calculations interactive report page and clicked (Create) to open the Calculation form to add a new calculation row. I entered the values shown below and clicked (Calculate Using Workflow). This inserted the new EBA_DEMO_CALCULATION row, started the workflow passing in the system-assigned primary key in P4_ID as the details primary key value, and closed the dialog.

When the dialog closes, I saw the page below. The workflow ran so quickly that it had already computed the result of 80 and stored it in the EBA_DEMO_CALCULATION row’s RESULT column using the details primary key value to identify which calculation row to update.

Checking the Workflows Started page, I could also inspect the details of the workflow instance using that approach as shown below. I added the Details Primary Key collapsible region to the Workflow Details page to make it easier to see which details primary key value is associated to the current workflow instance being shown. As we see, the system assigned a primary key value of 1 to the first EBA_DEMO_CALCULATION row created. Our page passed it into the APEX$WORKFLOW_DETAIL_PK built-in parameter at workflow start time. It also passed in the workflow’s P_SUBJECT parameter, whose value included the substitution variable &P4_ID. so the primary key value 1 also reflects in the title of the workflow instance.

Adding Some Fancier Features

With these basic examples under my belt, I spent some time to create a fancier version of the details-primary-key-using workflow above. In the sample app it’s called Fancier Calc (Details PK) and it appears below. It expands on the previous example by adding conditional notification of the result by email and/or by push notification. This required adding a few new parameters to let the page starting the workflow pass in: the current username, an email address to notify, a number of minutes to delay before notifying, and a boolean parameter to control whether to notify the current user using push notification.

It uses several new Switch activities to change the route of the workflow depending on various conditional evaluations. The Any Notifications? switch uses a PL/SQL expression to test whether :p_email_to_notify is not null or whether :p_push_notify_current_user is equal to ‘Y‘. The Any Wait? switch checks if :p_minutes_delay_before_notify is non-null and greater than zero (0). The Notify Email? switch tests if :p_email_to_notify is not null, and the Notify Push? switch evaluates if :p_current_username is not null and :p_push_notify_current_user equals ‘Y‘.

Notice that even though this last parameter is defined to be of type BOOLEAN, when referencing its value in PL/SQL or as a substitution parameter it will use the implicit or explicitly configured True Value and False Value as text. I chose to explicitly configure these values to ‘Y‘ and ‘N‘ respectively in the Property Editor for the Push Notify Current User parameter. I configured the Send Email activity using the &P_EMAIL_TO_NOTIFY. email address as the recipient, and the Push Notify activity using the &P_CURRENT_USERNAME. as the user to notify. Also notice the transition lines that emerge from the Switch activities have names on them. I chose to use “Yes” and “No” for the names of all of them, but of course you can use any descriptive names that make your workflow easier to maintain.

Consequently, I also enhanced the Calculation page to let the user choose between the simple or fancier workflow, as well as providing additional values if they choose to use the fancier version as shown below. Hopefully if you try out sample app, you can experiment using the fancier option to send yourself email and push notifications.

NOTE: If you install the sample app and try it yourself, make sure to (1) regenerate the Push Notification Credentials after importing the app and (2) opt-in to receive push notifications in the user settings page of the runtime application. You’ll find the (Regenerate Credentials) button in APEX Builder’s Shared Components > Progressive Web App page. The push notification opt-in is in the Settings page you can access in the dropdown menu under the logged-in username at runtime.

Why Pass in Current Username?

You may ask yourself why I needed to pass in the value of the current username as a workflow parameter, and that would be an excellent question! I originally tried to get the Push Notification activity working by simply referencing the familiar &APP_USER. substitution variable. But I frustratingly found it would never deliver the notification as expected. After reaching out to colleagues for advice, they reminded me the workflow is happening in the background and is always operating as the nobody user, which is the value of &APP_USER. when no user is logged in.

Therefore, my experiments to send a push notification to the nobody user were ignored since no user named nobody was enrolled in receiving push notifications. By passing in the p_current_username parameter value from the Calculation page, its value at workflow start time gets evaluated in the context of the page starting the workflow. This gives the workflow parameter the correct value of the end-user logged in using the page. Subsequently, the Push Notification activity’s use of &P_CURRENT_USERNAME. as the recipient of the push notification ensures it gets delivered to the logged-in user accessing the page that starts with workflow if they have opted-in to receive notifications.

NOTE: I subsequently realized I could have avoided passing in the current username in my simple example app by instead using the built-in workflow substitution string APEX$WORKFLOW_INITIATOR as the push notification recipient username. However, I decided to leave the sample they way it is in case the user to be notified by push notification is not the same username that starts the workflow in the application. Just be aware that APEX already keeps track of the username of the workflow initiator using this special item name.

Using SQL Query for Additional Data

In the Simple Calc (Details PK) workflow discussed earlier, we configured the Additional Data section of the workflow version to name the EBA_DEMO_CALCULATION table and its ID primary key column. This let the automatic row lookup work based on the Details Primary Key Item value passed in the APEX$WORKFLOW_DETAIL_PK parameter by the Workflow page process that started the flow. In turn, that allowed us to reference any of the columns of the EBA_DEMO_CALCULATION table by name anywhere in the workflow as needed. The Fancier Calc (Details PK) workflow shows off another useful alternative: using a SQL Query instead of a table name for Additional Data.

It specifies the following query that references the value of the :apex$workflow_detail_pk in its WHERE clause. Since the automatic lookup by primary key is not happening in this situation, the developer is responsible to perform the primary key lookup in their query using this built-in bind variable.

select id,
       operand1,
       operation,
       operand2,
       case operation
        when '+' then '+'
        when '-' then '-'
        when '*' then '×'
        when '/' then '÷'
       end as operation_translated
  from eba_demo_calculation
  where id = :apex$workflow_detail_pk

Just like what happened when using the table name and primary key column approach, all of the SELECT list column names in the Additional Data query are made available to reference throughout the workflow wherever needed. In the sample app, the Send Email activity references the value of the OPERATION_TRANSLATED column above by using &OPERATION_TRANSLATED. This lets your workflow perform a join or use the power of SQL to bring in whatever useful data might be relevant to every activity in the workflow. Each activity in the workflow also has an optional Additional Data query you can use to retrieve information that’s only relevant to a single activity.

NOTE: When using the SQL Query style Additional Data, if your query inadvertently retrieves no rows, then your workflow will fault with a “user-defined error” message, so be aware that this is what that error means. One way I encountered this error while creating the sample app was by accidentally using a bind variable named :apex$workflow_details_pk (with a plural “details” in the name when it needed to use the singular “detail”). Since no bind variable by this name-with-a-typo exists, its value evaluated to null and that caused my Additional Data SQL query to return no rows.

Cleaning Up Dev-Mode Workflow Instances

The apex_workflow package comes with a handy procedure called remove_development_instances() that lets you delete workflow instances you created during your development. It accepts an optional p_application_id parameter to provide the ID of the app whose dev-mode workflow instances you want to delete. If you don’t pass an app id, it cleans up the dev-mode workflow instances for all applications in the workspace. I added a button shown below to the Workflows Started page in the sample app that invokes this new procedure, passing in the :app_id bind variable to affect only the workflow instances for the current sample application.

On the Calculations list page, as shown below I added another button that deletes the rows from the EBA_DEMO_CALCULATION table that are not referenced by a workflow.

Using the combination of these two buttons, you can first delete the dev-mode workflow instances you have accumulated while trying out the sample. Then you can click the other button to remove the calculation rows that no longer reference a workflow (since you just deleted them in the step before!).

NOTE: While the simple example workflows in this article are not using approval or action tasks, one interesting corollary of the new remove_development_instances() procedure is that it will also clean up any approval or action task instances that were initiated by the deleted dev-mode workflows.

Tips on Moving & Connecting Activities

While exploring the workflow capabilities to prepare this article, I gradually became more confident in how to achieve a nice-looking diagram with neatly aligned activities and transition lines that looked how I wanted them to. It took time to discover the way that felt most productive, so I wanted to share what I learned here in case it could save you some time. The video below puts the following tips into action, showing how to:

• Drag and drop an activity from the palette
• Grab the arrowhead to reattach a transition
• Use anchor dots to position a line
• Add and remove “elbow” points to bend lines
• Create and connect activities in one gesture
• Multi-select to move groups of activities
• Drop a new activity between two others

Creating a Custom Activity

As a last exercise, I wanted to try extending the core set of APEX workflow activities to add a custom one into the mix. Sticking with the simple calculator theme, my goal was to create a Simple Calculator activity that any APEX Workflow could use. I started by creating a process type plug-in, and made sure to check the Workflow Activity checkbox in the Supported for section. I defined four plug-in attributes to capture the (page or workflow version) item names to provide Value1, Operation, Value2, and Result. To make it self-contained, I copied the source of the compute() function from the beginning of this article into the plug-in’s PL/SQL code area, and also added the following function whose name I configured as the plug-in’s Execution Function Name:

-- function compute() removed for brevity
-- see eba_demo_simplecalc package for source

function simple_calculator (
    p_process in apex_plugin.t_process,
    p_plugin  in apex_plugin.t_plugin)
    return       apex_plugin.t_process_exec_result 
is
    c_value1      number        := nv(p_process.attribute_01);
    c_operation   varchar2(200) :=  v(p_process.attribute_02);
    c_value2      number        := nv(p_process.attribute_03);
    c_result_name varchar2(200) :=    p_process.attribute_04;
    l_result apex_plugin.t_process_exec_result;
begin
    apex_session_state.set_value(
        c_result_name, 
        compute(c_value1,c_operation,c_value2));
    l_result.success_message := 'Success'; 
    return l_result;
end simple_calculator;

The code uses the v() and nv() functions to retrieves the string and number values of the items named by the first three custom attributes. From the fourth custom attribute, it gets the name of the item into which the result should be set. Finally it uses the set_value() function in the apex_session_state package to set the value of the result item to the result returned from the compute() function.

With this simple custom activity plug-in ready, next I created the Simple Calc Using Process Plugin workflow you see below. Notice how the custom activity Simple Calculator automatically appears in the Activities Palette. To configure it, I used the Property Editor to assign the names of the workflow parameters to use for Value 1, Operation, and Value 2 attributes, and the name of the version variable to use to hold the result.

A new Workflow Process Plugin page is a slight modification of the Workflow Params Only page we created earlier, but its Workflow page process starts the new Simple Calc Using Process Plugin workflow instead.

After trying out the new page, a quick check of the Workflows Started console page shows the results of all of our tests during this article.

Summary

Of course, you’d never really use APEX workflow to just add two numbers, but hopefully this simple exercise that I found helpful to learn the basics will also prove useful to you as you think about real-world business processes you can now automate with ease.

I encourage you to download the sample app from here and give it a spin. After importing the app, there are two additional setup steps to perform before the push notification will work. First, regenerate the Push Notification Credentials. In addition, ensure you have opted-in to receive push notifications in the user settings page of the runtime application. You’ll find the (Regenerate Credentials) button in APEX Builder’s Shared Components > Progressive Web App page. The push notification opt-in is in the Settings page you can access in the dropdown menu under the logged-in username at runtime. Also check that your mobile device or desktop is not set in “do not disturb” mode that suppresses delivery of push notifications. If it was in this mode, you’ll find your push notifications have quietly accumulated in the “notification center” on your device.😊

For additional reading on APEX Workflow including business-focused use cases involving approvals and tasks see my colleague Ananya Chatterjee’s blog series Simplify Business Process Management Using APEX Workflow and her article Multi Level Expense Approval using APEX Workflow. To learn more about APEX Workflow Development Lifecycle and Management check out my colleague Ralf Mueller’s article. For more background on end-user action tasks, see my colleague Richard Allen’s Using Action Tasks blog post. Many thanks to Ralf, Ananya, Richard, and Ottmar on my team who answered a lot of questions I had as I worked my way through this first encounter with APEX workflow.