Automating Business Processes

Automate business processes with APEX Workflows to coordinate work and show progress clearly.

A business process is a sequence of steps your organization follows to get something done. It can involve the work of multiple colleagues, each responsible for an activity like completing a job or approving a request. The tasks may also include interacting with external applications, collecting data, researching historical information, notifying stakeholders, or waiting for an event to occur or a period of time to elapse.

APEX Workflows let you automate business processes, improving efficiency for the people involved and enhancing visibility into the process for better decision-making.

• Automating a Process with Workflow
  Model business processes visually and show progress in your app as they run.
• Exploring Patient Onboarding Example
  Explore how APEX Workflow automates patient onboarding at the fictitous Woods Clinic.
• Automating Business Entity Lifecycle Logic
  Use a trigger and automation to wake workflow lifecycle logic on business entity status changes.
• Maintaining Workflow Processes
  Maintain workflows and task definitions through import, debugging, deployment, and cleanup.

Model business processes visually and show progress in your app as they run.

In App Builder, you define the sequence of activities in a visual diagram that anyone can understand at a glance. A workflow specifies the blueprint for a business process, and the APEX Engine manages each concrete example of every workflow. Using the Create Page Wizard, you can generate a workflow console page to show users "in flight" processes. The companion workflow detail page shows the status of a workflow instance complete with an annotated diagram so everyone involved stays aligned.

• Using the Diagram and Activities Palette
  Use the Workflow Designer to add activities, connect them, and edit their properties.
• Surveying the Native Activities
  Understand the extensible set of native workflow activities.
• Connecting and Routing Activities
  Learn the basic diagramming moves for creating, connecting, and routing workflow activities.
• Resolving Errors Before Saving a Workflow
  Resolve any Workflow Designer errors before saving your changes.
• Associating a Table with a Workflow
  Associate a workflow with a table or query so activities can reference related row data.
• Retrieving Data for an Activity
  Use activity-level Additional Data to query one row of values the current activity needs.
• Defining Parameters and Variables
  Use parameters and variables for values a workflow cannot query or must capture at a point in time.
• Understanding Background Execution
  Workflows run in a background session without page session state or a logged-in user.
• Getting Approval or User Input in a Workflow
  Wait for user approval or input by creating a task or sending a notification.
• Identifying Workflow Participants
  Assign workflow owners and administrators using case-sensitive usernames.
• Listing Workflows and Viewing Details
  Create Workflow Console pages to list the workflows a user owns, administers, or initiated.

Explore how APEX Workflow automates patient onboarding at the fictitous Woods Clinic.

Consider a concrete use case to explore how an APEX workflow solves real business challenges. Imagine you are the lead developer at Woods Clinic, a fictional ambulatory medical clinic. You've been tasked with improving the efficiency of new patient onboarding. You start by understanding the current situation, then use APEX Workflow to deliver business value by automating a number of previously-manual steps.

• Understanding the Current Process
  Understand the manual patient inquiry process before workflow automation.
• Identifying Automation Opportunities
  Identify the patient onboarding steps workflow automation can handle.
• Starting a Patient Onboarding Workflow
  Create a workflow definition and start building its first version in Workflow Designer.
• Associating the Patients Table
  Associate the workflow with the patients table so activities can reference patient row data.
• Configuring a Meaningful Title
  Set a meaningful workflow title using values from the related patient row.
• Building Patient Onboarding Visually
  Explore and understand key aspects of building the Patient Onboarding workflow visually.

Use a trigger and automation to wake workflow lifecycle logic on business entity status changes.

Your workflows can wait for data change events to implement custom business logic visually as a business entity row progresses through its natural "lifecycle".

• Learning the Medical Procedure Lifecycle
  Understand the patient procedure lifecycle tracked by the STATUS column.
• Seeing the Medical Procedure Lifecycle
  Visualize patient procedures by status to see how work flows through the clinic.
• Modeling the Medical Procedure Lifecycle
  Model status-driven business logic with a workflow that waits for procedure status or schedule changes.
• Starting the Lifecycle Workflow
  Start a procedure lifecycle workflow when onboarding creates the patient procedure row.
• Following a Medical Procedure Lifecycle
  Follow a patient procedure through its full lifecycle in the Woods Clinic app.
• Notifying Workflow of Data Changes
  Notify a waiting workflow about data changes with events an automation processes periodically.

Maintain workflows and task definitions through import, debugging, deployment, and cleanup.

• Importing vs. Deleting an Application
  Import upgrades over the existing app to preserve production workflow and task instances.
• Recognizing Development Mode
  Recognize Development Mode by the Developer Toolbar or APP_BUILDER_SESSION value.
• Debugging Business Process Logic
  Use APEX debug tracing to investigate workflow and task errors.
• Accessing Workflow and Task Data
  Understand APEX dictionary views and packages for workflows, tasks, and their definitions.
• Understanding Task and Workflow Purging
  Plan for workflow and task purging by storing any permanent history in application tables.
• Activating an In Development Workflow
  Activate a workflow version before running it outside Development Mode.
• Deploying Workflows to Production
  Promote active workflow versions from development so production stays in sync.
• Deleting a Workflow with No Instances
  Check whether any workflow instances still use a version before deleting it.
• Removing Development Instances
  Remove Development Mode workflow instances and their related tasks after testing.

Use the Workflow Designer to add activities, connect them, and edit their properties.

The Diagram Builder surface occupies the bulk of the Workflow Designer layout. The Activities palette below contains actions you can drag onto the diagram as workflow steps. Connection arrows between activities define the sequence the APEX engine follows. The Workflows tab on the left presents diagram elements organized in a tree. The Property Editor on the right lets you inspect and change settings for activities and connections.

A workflow begins with the Start activity shown below as a circle with the "play" triangle icon. If the workflow contains Switch activities to make decisions, there are multiple "paths" from the start activity to a Workflow End activity that appears as a circle with the "stop" square icon. While there is always only one Start activity, to simplify diagram layout you can include as many End activities as needed.

Understand the extensible set of native workflow activities.

When none of the built-in activities meets your needs, you can install a community-developed workflow activity plug-in or create one yourself. Just create a process type plug-in with its Workflow Activity checkbox checked in the Supported For section.

Learn the basic diagramming moves for creating, connecting, and routing workflow activities.

Creating organized, readable diagrams is straightforward after learning and practicing the basics of creating, connecting and routing activities.

• Adding an Activity from the Palette
  Add workflow activities by dragging them from the palette onto the canvas.
• Naming a New Activity
  Name each new activity clearly in the Property Editor.
• Changing a Connection's Target Activity
  Reattach a connection by dragging its arrowhead or starting dot to another activity.
• Fine-Tuning Line Slope with Anchor Dots
  Adjust a connection line’s slope by moving its source or target anchor dots.
• Moving Connections with Grab Handles
  Move horizontal or vertical connection segments with their grab handles.
• Adding a Connection Between Activities
  Connect activities by dragging a plus sign from the source activity to the target activity.
• Bending a Connection with Elbow Dots
  Bend a connection by adding, moving, or removing elbow dots.
• Adding Next Activity from Existing One
  Add and connect the next activity directly from an existing activity.
• Configuring a Switch Connection
  Configure a Switch connection with a condition and name for its decision outcome.
• Inserting Activity Between Existing Ones
  Insert an activity into an existing connection.
• Moving a Group of Activities
  Move selected activities as a group while their connections follow.
• Bringing Connections in Front of Activities
  Ensure arrowheads stay visible over activities in the runtime workflow progress diagram.

Resolve any Workflow Designer errors before saving your changes.

When working in Workflow Designer, if your diagram contains any errors a toolbar indicator shows the number of problems in a red circle. Click this icon to open the Messages dialog to review the details. Click a message to navigate to the item that needs fixing. When the diagram has no errors, click (Save) to make your changes permanent.

Associate a workflow with a table or query so activities can reference related row data.

Then, wherever your app starts a new workflow instance, remember to supply the primary key value of the business entity row to which the new flow relates.

For example if your Additional Data table's primary key column is ID, then reference :ID or &ID. or configure ID as the item name providing the value of an activity property.

Use activity-level Additional Data to query one row of values the current activity needs.

In addition to the workflow-level Additional Data table or SQL query, every activity also supports an optional Additional Data query. Wherever it adds value, use it to declaratively retrieve a single row of relevant information the current activity requires to get its job done. Using bind variables, the query can reference column values from the workflow-level Additional Data, parameters, and version variables. It can join in data from any tables in the data model and engage functions in its SELECT list. This makes it convenient to perform on the fly computations whose results are useful to the current activity.

Use parameters and variables for values a workflow cannot query or must capture at a point in time.

Workflow parameters and variables are a convenient way to persistently store values unique to a workflow instance. Parameters get their value at instance start time and are read-only during the flow. In contrast, your workflow activities can both read and change the value of variables. So one activity can store results in a variable that a later activity can reference or modify. The APEX engine manages their storage automatically.

You define parameters at the workflow definition level, while you specify variables on the workflow version level. While formally called version variables, often workflow variable or just variable are clear alternatives. While local PL/SQL variables are transient values available only within the scope you declare them, workflow variables are available from any activity for the life of the workflow.

Most workflows configure an Additional Details table or query at the workflow level. When combined with the Details Primary Key value passed in when starting the workflow instance, this makes the values of Additional Data columns available anywhere in the flow using :NAME, &NAME. or NAME syntax. Activities can also use the value of the additional data table's primary key column to query other necessary data the process requires by defining an activity-level Additional Details query.

• Creating a Workflow Parameter
  Create workflow parameters with meaningful static IDs for start-time values.
• Creating a Workflow Variable
  Create workflow variables with meaningful static IDs for values activities can change.

Workflows run in a background session without page session state or a logged-in user.

The APEX engine manages the progress of workflow instances asynchronously in the background. It processes each instance's next activity in a distinct engine session with no logged-in user. A workflow engine session is also different from the user session created for each end user of your application's pages.

Wait for user approval or input by creating a task or sending a notification.

Either way, the user clicks a link, opens a page, and provides their input. The workflow waits in the background. It moves on only after the user responds, whether that’s in a few minutes or a few days.

• Assigning Work to a User's Task Inbox
  Assign work to users so APEX manages the task lifecycle and continues the workflow when completed.
• Waiting for a Signal to Continue
  Send users a page link, wait for their response, and continue the workflow from application logic.

Assign workflow owners and administrators using case-sensitive usernames.

A workflow participant is either a workflow owner or workflow administrator. A workflow owner is a user with overall responsibility for the business process, while a workflow administrator helps address a problem relating to the flow. For example, if user DAVID is the owner of the Patient Onboarding workflow, he's the one who can see it in his workflow console when the context is set to My Workflows. He can also retry or terminate the flow. If user CARLA is the workflow's business administrator, she'll see it in her Admin Workflows console list. She can suspend it, change its variable values if needed to fix a problem, and resume it again.

• Configuring Static Workflow Participants
  To create a new participant, choose Create Participant from the context menu of the Participants node in the Workflows tree.
• Determining Participants Dynamically
  To determine workflow participants dynamically, use a participant Value > Type of SQL Query, Function Body, or Expression.
• Passing in Workflow Participants
  A common requirement is to let the page or workflow activity that creates a workflow pass in the list of task assignees as an input parameter.

Create Workflow Console pages to list the workflows a user owns, administers, or initiated.

In the Create Page Wizard, create a Workflow Console page to list workflow instances. You provide the name for the page that lists workflow instances, and the workflow details page. The Report Context you choose determines the filtered list of workflow instances the page displays at runtime.

You can use the pages as they come, or customize them in Page Designer as needed. The page below is a slightly modified version of an Admin Workflows console page. Notice the workflow title you configure is used as the Title of the Content Row region the generated console page contains.

select *
  from table ( apex_workflow.get_workflows (
                   p_context => 'ADMIN_WORKFLOWS'
                   --, p_application_id => :APP_ID
                   ) )

Clicking on the title of a particular workflow instance like New Patient Danny Deng, the generated workflow detail page appears. It shows the list of completed activities for the selected instance. Expand the Variables, Parameters, and History sections to see additional information.

Understand the manual patient inquiry process before workflow automation.

A new patient inquires about a particular medical procedure they need. Then, Woods Clinic staff members review the provided information to decide if the clinic can accommodate the patient's request.

• Inquiring About a Procedure
  Submit a procedure inquiry so the clinic can decide whether it can help.
• Onboarding the Patient Manually
  Review the manual onboarding steps staff follow before approving or rejecting a patient.

Identify the patient onboarding steps workflow automation can handle.

Create a workflow definition and start building its first version in Workflow Designer.

In the existing application, you create a new Workflow shared component named Patient Onboarding. The workflow Diagram Builder feels familiar, since it works like Page Designer. As shown below, your workflow appears in the tree in the Workflows tab. Since a business process can change over time, a workflow definition can have multiple versions. Out of habit, you name the first version 1.0. A version starts in the In Development state until you activate it later for use in production. Notice the 1.0 [Dev] label in the tree below confirms this state. The Property Editor also reflects it.

To get you started, App Builder creates a single Execute Code activity sequenced between a Start and an End. You can change its type and properties as needed, and add other activities and connections to the diagram to build up your flow. As flows get more involved or you identify a reusable "subflow", one workflow can invoke another like a subroutine, passing parameters in and out as required.

Associate the workflow with the patients table so activities can reference patient row data.

The patient onboarding process relates to patients in the FRC_PATIENTS table. Each patient's onboarding journey is a concrete example of the process "blueprint" for one particular patient. Since the Patient Onboarding workflow represents this business process, most of its steps reference or modify the FRC_PATIENTS row whose onboarding progress each instance represents.

As shown below, Additional Data is a workflow version property. Just set the Patient Onboarding workflow's table to FRC_PATIENTS and identify ID as its Primary Key Column. Then, wherever your app starts a new Patient Onboarding workflow instance, remember to supply the ID value of the patient to whom the new flow relates. This lets you reference patient details as &NAME. or :NAME anywhere in the flow using any FRC_PATIENTS column name. For example, you can reference the patient's FIRST_NAME and LAST_NAME anywhere you need them in the flow.

Set a meaningful workflow title using values from the related patient row.

New Patient &FIRST_NAME. &LAST_NAME.

Explore and understand key aspects of building the Patient Onboarding workflow visually.

You build business processes visually by adding activities to your workflow diagram, sequencing them with connection arrows, and using the Property Editor to configure their settings. The Woods Clinic Patient Onboarding flow uses of many APEX workflow's native features.

• Applying Activities to Patient Onboarding
  Review the native workflow activities used to automate patient onboarding.
• Evaluating Spatial Distance with an API
  Use built-in geospatial functions to check whether a patient lives within service range.
• Switching Path Using a Condition
  Use a Switch activity to decide which path the workflow takes.
• Predicting Results with Machine Learning
  Use historical data to predict likely outcomes and automate confident decisions.
• Visualizing Workflow Progress
  Show a workflow instance’s progress in a Workflow Diagram region.
• Awaiting Completion of a Human Task
  Create an approval task and wait for its outcome before the workflow continues.
• Emailing Users a Rejection
  Notify rejected patients with an email template populated from workflow data.
• Provisioning a New User Account
  Provision a portal user account when the workflow approves a patient request.
• Waiting Until Patient Completes Profile
  Wait for the approved patient to complete portal registration before the workflow continues.

Understand the patient procedure lifecycle tracked by the STATUS column.

In your Woods Clinic application, once a patient's initial procedure request is approved, you track the medical procedure for the patient as a new row in the Patient Procedures table. You can see below how it fits into the application data model. The figure shows an entity relationship diagram of the business entities involved in the Woods Clinic application. This scenario focuses on the Patient Procedure. It tracks a particular Patient's visit at which a selected clinic Doctor performs a certain Medical Procedure they need. Each patient has an Insurance Provider and can make Patient Referrals to help bring in new patients. Multiple doctors might perform a given procedure, so Doctor Procedures tracks that skills matrix. Finally, a patient procedure might have one or more Invoices to bill the insurance provider and the patient who pays the uncovered amount.

Your FRC_PATIENT_PROCEDURES table has a STATUS column whose value reflects the current status of the "Patient Procedure" business entity.

Visualize patient procedures by status to see how work flows through the clinic.

Model status-driven business logic with a workflow that waits for procedure status or schedule changes.

You can use a workflow with a Wait activity as shown below to automate this medical procedure lifecycle. Notice the Wait activity has the Static ID unique identifier of status_or_date_changed and that it has left the Timeout Type unset. This makes the workflow wait indefinitely until your application logic signals it to continue.

The Switch activities can use the value of the V_WHAT_CHANGED variable and Additional Data columns like STATUS, DOCTOR, and PROCEDURE_DATE of the associated FRC_MEDICAL_PROCEDURES row to perform appropriate business logic. Most of the paths in the workflow connect back to the Wait activity to await further data changes. However, when STATUS reflects the final "Paid" status, the workflow ends.

Start a procedure lifecycle workflow when onboarding creates the patient procedure row.

The final step of the patient_onboarding workflow uses an Invoke API activity to call the COMPLETED_REGISTRATION procedure below in the FRC_ONBOARDING package. It inserts a row in FRC_PATIENT_PROCEDURES to track the patient's medical procedure to perform at the clinic. Just after that, it calls the START_WORKFLOW_FOR helper procedure to create the procedure_lifecycle workflow instance related to the newly-assigned patient procedure ID.

-- in frc_onboarding package
procedure completed_registration(p_patient_id number)
is
    l_patient t_patient := patient(p_patient_id);
    l_new_procedure_id number;
begin
    -- insert initial procedure
    for j in (select l_patient.id as patient_id,
                     code,
                     'AwaitingSchedule' as status,
                     price,
                     duration_hours
                from frc_medical_procedures
               where code = l_patient.initial_procedure)
    loop
        insert into frc_patient_procedures(
            patient_id,
            medical_procedure,
            status,
            amount_due,
            duration_hours)
        values (
            j.patient_id,
            j.code,
            j.status,
            j.price,
            j.duration_hours)
        return id into l_new_procedure_id;
        start_workflow_for(l_new_procedure_id);
    end loop;
end;

The START_WORKFLOW_FOR helper method starts a workflow with Static ID procedure_lifecycle, passing in the medical procedure ID for the Details Primary Key value. It calls the START_WORKFLOW function in the APEX_WORKFLOW package.

-- internal in package frc_procedure_lifecycle
------------------------------------------------------------------
-- Start a procedure_lifecycle workflow instance associated with
-- the supplied patient procedure id
------------------------------------------------------------------
procedure start_workflow_for(
    p_procedure_id in number)
is
    l_workflow_id number;
begin
   l_workflow_id := apex_workflow.start_workflow(
                        p_application_id => frc_app.clinic_app_id,
                        p_static_id      => 'procedure_lifecycle',
                        p_detail_pk      => p_procedure_id,
                        p_initiator      => 'ADMIN');
end start_workflow_for;

PLS-00221: 'START_WORKFLOW' is not a procedure or is undefined

Follow a patient procedure through its full lifecycle in the Woods Clinic app.

Woods Clinic staff member David approved patient Danny Deng's medical procedure. Danny got his confirmation email with login credentials from your patient_onboarding workflow. He logged in and completed his registration. This concluded the patient onboarding workflow, whose last step is to create the patient procedure row and its corresponding procedure_lifecycle workflow. The steps that follow illustrate how David takes Danny's medical procedure through its lifecycle at the clinic.

• Launching Woods Clinic Application
  Launch the Woods Clinic application like a native app with persistent authentication.
• Scheduling a Medical Procedure
  Schedule a medical procedure and let the workflow notify the patient.
• Receiving Medical Procedure Schedule
  Receive the scheduled procedure details by email.
• Discharging Patient After Procedure
  Complete a patient procedure and generate draft discharge instructions with AI.
• Preparing Pixel-Perfect PDF Invoice
  Generate a pixel-perfect PDF invoice in the background with a report query and layout.
• Reviewing Invoice with Insurance Provider
  Review the prepared invoice and enter the insurance coverage amount.
• Regenerating Invoice in Background
  Regenerate an invoice in the background while preventing duplicate runs for the same procedure.
• Invoicing Patient for the Balance
  Mark the invoice complete so the workflow emails it to the patient and advances the procedure lifecycle.
• Acknowledging Receipt of Payment
  Record the patient’s payment so the procedure reaches its final status.
• Confirming the Lifecycle is Complete
  Confirm the paid procedure completed its workflow and full business lifecycle.

Notify a waiting workflow about data changes with events an automation processes periodically.

After their initial approval, your Procedure Lifecycle workflow automates previously manual steps performed as medical procedures progress through the clinic. It accomplishes this by reacting to changes in the procedure status or schedule.

To notify the workflow of data changes, you use a combination of a database trigger to log patient procedure lifecycle events, and an automation to periodically process the logged events. As the automation processes any events, it notifies the workflow of data changes by signaling the Wait activity to continue.

• Understanding Lifecycle Event Strategy
  Log procedure lifecycle events in a trigger and process them on a schedule with an automation.
• Simplifying Event Management
  Define a package API that lets the trigger add events and the automation process them.
• Adding Events Using a Trigger
  Use a trigger to log status or schedule changes as lifecycle events.
• Processing Events with an Automation
  Use an automation to process queued lifecycle events on a schedule.
• Continuing a Waiting Lifecycle Workflow
  Continue the waiting workflow activity and pass the event type into a workflow variable.

Import upgrades over the existing app to preserve production workflow and task instances.

Once you deploy your application using workflows and tasks into production, end users begin exercising its functionality to get their jobs done. The workflow and task instances your application creates are business critical, so they are as important as any other rows of data in your application data model. As you enhance your app and fix issues users report, you periodically import a new version of your application into production. When you do this, it is critical that you perform the upgrade without deleting the existing application first. There is clear motivation for this guidance. When you import a new application version over an existing one, any existing workflow and task instances remain as they are. This is not the case when you delete an application.

Recognize Development Mode by the Developer Toolbar or APP_BUILDER_SESSION value.

During development, when you run your app from App Builder, it's in "Development Mode". The Developer Toolbar appears when in this mode. Using its settings menu, as shown below, you can change its default display position, auto-hide behavior, and button display style.

You can disable the Developer Toolbar in Development Mode session if necessary. As shown below, change the Availability > Status setting in Shared Components > Application Definition to Available instead of the default Available with Developer Toolbar.

Use APEX debug tracing to investigate workflow and task errors.

When a business process encounters an error, use APEX debug tracing to learn more information about what went wrong.

• Querying Recent Debug Messages
  Query recent debug messages to find workflow errors and related trace details.
• Enabling Debug Tracing Proactively
  Enable debug tracing from the Developer Toolbar or directly on a workflow version.
• Viewing Debug Trace Information
  View workflow debug traces by clearing page filters in the Debug Message Data window.
• Adding Custom Debug Messages
  Add custom debug messages with APEX_DEBUG.INFO when needed.

Understand APEX dictionary views and packages for workflows, tasks, and their definitions.

APEX dictionary views shown below contain complete information about your workflow definitions and instances. The APEX_WORKFLOW package is your API to work programmatically with workflows.

The diagram shows the relationship between dictionary views related to workflow definition metadata and workflow instance data.

The additional views shown below contain all relevant information about your task definitions and instances. The APEX_HUMAN_TASK package is your API to work programmatically with tasks.

The diagram shows the relationship between dictionary views related to task definition metadata and task instance data.

Plan for workflow and task purging by storing any permanent history in application tables.

The APEX platform manages the workflow and task instances for all workspaces. To avoid unbounded storage requirements, a daily job enforces the retention period your instance administrator's configures for completed tasks and workflows.

The default retention period defaults to 7 days for tasks and 30 days for workflows. The administrator can extend these up to 30 days for tasks and up to 100 days for workflows. Any completed instances beyond the retention period get archived, then purged.

Activate a workflow version before running it outside Development Mode.

To activate a version, select it in the Workflows tree. First, ensure it has a Workflow Owner participant configured. It's recommended to configure a Workflow Administrator, too. Then, use the Activate option on the context menu. Alternatively, as shown below, click the (Activate) button next to the State setting in the Property Editor. A confirmation dialog appears informing you any currently active version will be deactivated. This means the state of the existing Active workflow changes to Inactive.

Workflow versions in the Inactive state display with a strikethrough font in the Workflows tree like 1.0.8 and 1.0.9 below. Make any activations and deactivations permanent by clicking (Save) in the Diagram Builder.

Promote active workflow versions from development so production stays in sync.

Once your workflow has an Active version – for example, 1.0.0 – you can promote your application to the production environment. Any new workflow instances created in production, use the Active version at their time of creation. Every workflow instance tracks the version used to create it, and continues using that version for its entire lifecycle.

Business processes change over time. So naturally, at some point new requirements compel you to duplicate Active version 1.0.0 to create the In Development version 1.0.1. Always do this in your Development environment. You make any necessary changes to the process, testing the In Development version in Development Mode by running your app from App Builder. When it's ready, you activate version 1.0.1 in development.

When you next promote your application to production, new workflow instances will now use the Active version 1.0.1, while previously created flows continue using the Inactive version 1.0.0 they were created with. As time goes on, your application accumulates new workflow versions by repeating this process over and over. You only modify In Development workflow versions in your Development environment, promote the application to Production, and never modify workflow versions in Production. This ensures your production environment is always in sync with development. The diagram illustrates how workflow versions accumulate in production over an application's lifecycle. The starred workflow version is the single active one. The versions with a moon icon are inactive, but might still be used by existing workflow instances.

Check whether any workflow instances still use a version before deleting it.

Workflow versions let your business process evolve over time. Every workflow instance created outside of Development Mode uses the Active version at the time of creation. The WORKFLOW_VERSION column in the APEX_WORKFLOWS dictionary view reflects the version each instance uses. A given workflow instance continues using the workflow version it was created with throughout its lifecycle.

If you activate a new version, this only affects new workflow instances. An Inactive version does not mean an Unused version. It only means no new workflow instances will use that inactive version.

In addition to deactivating previous workflow versions, the Workflow Builder also lets you delete workflow versions. However, you must ensure no workflow instances are using that version any more before you delete it. Before deleting a workflow, run the following query in the SQL Workshop > SQL Commands page to verify that the version you are considering deleting is no longer in use.

-- list procedure_lifecycle workflow versions in use
select distinct workflow_version
 from apex_workflows
where application_id = 103
  and workflow_def_static_id = 'procedure_lifecycle'
order by workflow_version

Remove Development Mode workflow instances and their related tasks after testing.

In Development Mode, starting a workflow uses the In Development version if available. Otherwise, the Active version is used. During development, you typically create many workflow instances for testing purposes. When you want to clean up these testing artifacts, call the REMOVE_DEVELOPMENT_INSTANCES procedure in the APEX_WORKFLOW package. It immediately purges any workflow instances created in Development Mode without archiving them. Since task instances created by workflows are dependent artifacts, this procedure also immediately purges any tasks related to these deleted workflow instances without archiving them.

Task instances created outside of Development Mode cannot be deleted. The workflow administrator can cancel them, and the daily job that purges canceled workflows and tasks removes them within 24 hours.

Add workflow activities by dragging them from the palette onto the canvas.

To add a new activity to the workflow canvas, drag it from the Activities palette and drop it onto the canvas. As shown below, guidelines appear to help you align it to existing activities.

Name each new activity clearly in the Property Editor.

By default, a new activity is named New, so use the Property Editor as shown below to give it a more meaningful name. For this exercise, you just name it B.

Reattach a connection by dragging its arrowhead or starting dot to another activity.

To reattach an existing connection line, as shown below, drag its arrowhead onto the new activity it should connect to. An orange outline helps you see when you are within the visual boundary of a new target activity. Release the mouse button to complete the operation. Now activity A continues to B.

The figure below shows a diagram with a start activity connected to an activity named A. A then connects to the End activity. A B activity is in the upper right corner, initially with no inbound or outbound connections. A Which Way? switch activity lies in the center with one connection to C and another connection to E.

You can change a connection's origin as well by dragging its starting dot onto a new source activity. In the figure, the mouse drags the arrowhead previously connecting A to the End activity to drop it onto B. The result is that A then connects to B instead.

Adjust a connection line’s slope by moving its source or target anchor dots.

When you select a connection line, anchor dots appear inside the boundaries of the source and target activities as shown below. The anchor dots determine the slope of the connection line between source and target. To help your eye see this concept, the figure below includes additional dotted line segments. Notice in the other connection lines that only the segment from the edge of the source activity to the edge of the target one is actually visible.

You can move the anchor dot in the source or target activity anywhere within the boundary of the respective shape. For example, as shown below, by moving the anchor dot within the B activity's shape, you can change the slope of the connection line to be horizontal. Also notice the grab handle appears on the now-horizontal line.

Move horizontal or vertical connection segments with their grab handles.

Grab handles appear on any segment of a connection line that is horizontal or vertical, and, as shown below, you can use the drag handle to move the line segment up/down. The same goes for using the grab handle to move a vertical segment left and right more easily.

Connect activities by dragging a plus sign from the source activity to the target activity.

To connect two activities, hover over the source activity until the four plus signs appear. Then, as shown below, drag one of these plus signs and drop it into target activity rectangle. The orange highlight confirms the target activity. Your pointer position within the target activity shape when you release the mouse button is meaningful. It determines the initial placement of the target's anchor dot. You can then adjust the position of a selected connection's anchor dots anytime.

Bend a connection by adding, moving, or removing elbow dots.

To bend a line, start by selecting it. Then, as shown below, single-click on the line where you want to place the elbow dot.

After you've added an elbow dot, drag it into a new position as shown below.

As shown below, you can repeat the process of adding an elbow dot and dragging it to a new position to achieve whatever clean line routing feels right. If necessary, you can reposition the source or target anchor dots to fine-tune the slope of the initial or terminal connection line segments. Notice when your connection contains horizontal or vertical segments, grab handles appear that you can use to adjust each one separately in a single movement. To remove an elbow dot, select the connection line and then double-click on the dot.

Add and connect the next activity directly from an existing activity.

Click on one of the plus icons on an existing activity to create an activity that happens next in the flow. As shown below, a context menu presents available activities to choose from.

Your chosen activity type appears on the diagram already connected with a connection from the source you clicked on to initiate the process. By default, it is named New, so use the Property Editor to change its name to something more meaningful. Here, you name it D. Since the source activity is a Switch, the new connection line is red to let you know some configuration adjustment is needed.

Configure a Switch connection with a condition and name for its decision outcome.

In addition to its descriptive name, a connection originating from a Switch activity also identifies which decision outcome it represents. A red connection line indicates this detail is missing. Select the connection line originating from the Switch and, as shown below, configure its condition and give it a meaningful name. The Which Way? switch activity in this example decides based on a workflow variable. This particular D connection line represents the case when the variable Is Equal To the value D. Here the connection name and the target activity are both named D for simplicity. Rest assured your workflows can use any connection and activity names that best communicate the business process flow to developers and end users.

Insert an activity into an existing connection.

To add a new activity between two existing ones, select the connection line between them and click the plus in the middle of the line. As shown below, a context menu appears letting you choose the activity type to create.

Alternatively, as shown below, you can drag an activity from the palette and drop it in the middle of the connection line between the existing activities.

Move selected activities as a group while their connections follow.

If you've already positioned a set of activities and connections but need to make space, you can select and move a group. Use [Control]-Click on Windows or [Command]-click on the Mac to select multiple activities as shown below. Then you can drag the group as a unit by clicking on a shape inside the selected area. This action maintains relative positions and alignment of the group elements. You only need to select activities, the connection lines move accordingly.

An even faster way to select multiple activities is to hold down the [Shift] key and drag out a rectangle as shown below. This gesture selects any activities the rectangular selection touches. Once selected, as before, you can move them as a group by clicking on a shape inside the selected area.

Ensure arrowheads stay visible over activities in the runtime workflow progress diagram.

When viewing the runtime workflow diagram, you may notice some connection arrowheads appear to be "buried under" the activity shapes. While this effect does not confuse end users, it does look less than perfect. During the natural course of developing your workflow diagram, the "stacking order" of the shapes can result in activities stacking above connections. As shown below, this causes the tip of the connection arrow head to be obscured by the activity shape in a way that is even more noticeable in the read-only Workflow Diagram region.

The solution is simple. As shown below, just use the "Bring to Front" tool in the toolbar to bring connections on top of activity shapes. Alternatively, use the "Send to Back" tool next to it to send the activity shapes behind the connections.

Create workflow parameters with meaningful static IDs for start-time values.

Create a parameter using the Create Parameter option on the workflow's context menu in the Workflows tree. If a parameter already exists, the same option appears on the Parameters header in the tree. For each parameter you create, use the Property Editor to choose a data type and assign it a meaningful Static ID. While not required, using a parameter static ID that starts with a prefix like P_ can help you distinguish parameter names from Additional Data column names and from workflow variable names. You use the parameter's static ID to reference its value as a bind variable :P_YOUR_STATIC_ID in code or queries, &P_YOUR_STATIC_ID. in substitutions, and P_YOUR_STATIC_ID in activity properties requiring an item name.

Create workflow variables with meaningful static IDs for values activities can change.

Create a variable using the Create Variable option on the workflow version's context menu in the Workflows tree. The same option exists on the Variables heading in the tree. For each variable you create, use the Property Editor to choose a data type and assign it a meaningful Static ID. While not required, using a variable static ID that starts with a prefix like V_ can help you distinguish variable names from Additional Data column names and from workflow parameter names. You use the variable's static ID to reference its value as a bind variable :V_YOUR_STATIC_ID in code or queries, &V_YOUR_STATIC_ID. in substitutions, and V_YOUR_STATIC_ID in activity properties requiring an item name. For example, when configuring a variable name to receive an "out" value from activities like Invoke API, Invoke Workflow, Human Task - Create, and others, use the V_YOUR_STATIC_ID variable name.

Assign work to users so APEX manages the task lifecycle and continues the workflow when completed.

Use a Task Definition shared component to track a work item to assign to one or more people. The task Subject line you configure appears in the task list "inbox" of all assignees. They are all potential owners of the task. The first available user claims the task and becomes its actual owner. Once claimed, it no longer appears in other potential owners' task lists.

By configuring a Task Details Page, users can click a task's subject in this "inbox" to review its details. You can generate it in the task definition editor and adjust it in Page Designer as needed. Your enhancements to the page can include saving additional data to updatable task parameters or to application tables.

Some tasks require only that the owner confirm they've completed the action it represents. Others seek an outcome indicating the owner's approval or rejection. A task can be open-ended, or made time-sensitive by configuring a due date and expiration behavior. Your workflow uses Human Task - Create activities to coordinate which work items need to be done in what order. The APEX engine automatically waits for a potential owner to complete a task before proceeding to the next activity in the workflow. The figure illustrates the workflow as a postal employee delivering tasks to three different users task inboxes. One reviews tasks from a smartphone. Another sees tasks on a tablet. A third person works on tasks from a laptop.

Using this approach, you get many built-in features that enterprise applications often require. APEX manages the "lifecycle" of a task from creation to completion. Task definitions support more than just a priority and deadlines. Your dynamic task assignment can account for end-user vacations. Users can request and receive more info about a task, delegate it to a colleague, or participate in a threaded discussion about it with teammates when collaboration is required. You can relate each task instance to a particular row in a database table, and your task definition can react to changes in the state by notifying users or executing custom business logic. In fact, by defining an action on the task Create event, you can notify a user a new work item has been added to their to-do list, encouraging them to check their task "inbox".

• Deciding if Task Needs an Outcome
  Choose an action task to track completion or an approval task to capture an outcome.
• Associating a Table with a Task
  Associate a task with a table or query so task logic can reference related row data.
• Accepting Input Values in Parameters
  Use task parameters for values the task cannot get from its Actions Source row.
• Determining Task Participants
  Assign potential owners and business administrators using case-sensitive usernames.
• Understanding Task States
  Understand task states and the actions that move a task through its lifecycle.
• Creating Task "Inbox" Page for Users
  Create a Unified Task List page that shows users the tasks they need to act on.
• Collecting Data Using a Human Task
  Create a human task from a workflow to collect user input before the flow continues.
• Using and Tuning the Task Details Page
  Explore how users work with a Task Details page and how developers can tune it.
• Letting an Initiator Approve a Task
  Enable initiators to approve their own tasks when the business process requires it.
• Handling Time-Sensitive Tasks
  Configure due dates and expiration behavior for time-sensitive tasks.
• Reacting to Task Change Events
  Use task actions to react to lifecycle events while a workflow waits.

Send users a page link, wait for their response, and continue the workflow from application logic.

When continuing a waiting activity, you can also optionally pass a list of name/value pairs. If any of the names in this list match the names of workflow variables, their values get assigned automatically to the corresponding value you return.

• Enabling Deep Linking to a Specific Page
  Enable Deep Linking so notification links can open a specific application page.
• Generating Page URL for Notification
  Generate an absolute page URL for email or push notification links.
• Emailing User a Page Link
  Email a user an absolute page link generated from workflow activity data.
• Delivering Push Notification with Page Link
  Send a push notification with an absolute page link generated from workflow activity data.
• Waiting Until Application Logic Continues
  Pause a workflow with a Wait activity until application logic continues it.
• Navigating to Page Using Email Link
  Open a notification link to enter requested data on a deep-linked page.
• Retrieving Title and Activity Labels
  Retrieve workflow and activity labels to show context on the data entry page.
• Signaling to Continue and Returning Data
  Continue a waiting workflow activity and return submitted values to workflow variables.
• Inspecting Resulting Workflow Instance
  Inspect workflow variables and diagram progress after a waiting activity continues.
• Evaluating Benefits of Using Tasks Instead
  Consider using tasks when user input needs tracking, reminders, and a task inbox.

To create a new participant, choose Create Participant from the context menu of the Participants node in the Workflows tree.

Then set the participant Type in the Property Editor. As shown below, in a simple application, either or both of the Workflow Owner or Workflow Administrator participants can be a static value like DAVID or CARLA. But normally you use one of the Value > Type settings like SQL Query, Function Body, or Expression to dynamically determine one or both of these participant lists.

To determine workflow participants dynamically, use a participant Value > Type of SQL Query, Function Body, or Expression.

When using the SQL Query option, your single column SELECT list returns one case-sensitive username per row. For the Function Body or Expression options, return a comma-separated list of case-sensitive usernames.

Your query, function, or expression can use bind variable syntax to reference workflow parameter values, workflow version Additional Data column names, application items, or other APEX substitution strings.

For easiest maintainability, your expression can invoke a PL/SQL package function as well. The function can be specific to the workflow at hand, or by passing the value of the pre-defined bind variable APEX$WORKFLOW_ID your generic function can lookup the related workflow definition using the APEX_WORKFLOWS dictionary view's WORKFLOW_DEF_STATIC_ID column.

your_app_pkg.owners_for_workflow(:APEX$WORKFLOW_ID)

A common requirement is to let the page or workflow activity that creates a workflow pass in the list of task assignees as an input parameter.

:P_ASSIGNEE_LIST

select column_value
from apex_string.split(:P_ASSIGNEE_LIST,':')

Submit a procedure inquiry so the clinic can decide whether it can help.

A new patient completes the Procedure Inquiry form shown below on the clinic's website to check if the clinic can accommodate a particular medical procedure. They provide contact information, home address, and insurance provider. They also indicate the procedure they seek. In this example, Danny Deng needs a Colonoscopy and hopes Woods Clinic can help.

As shown below, after the patient completes this initial registration, they await the clinic's response.

Review the manual onboarding steps staff follow before approving or rejecting a patient.

If any of the steps results in a decision to reject the patient's request, a staff member writes an email to the patient apologizing and explaining they need to seek their care at another facility.

Review the native workflow activities used to automate patient onboarding.

Use built-in geospatial functions to check whether a patient lives within service range.

As shown below, a Geocoded Address page item in the registration page identifies the longitude and latitude of a new patient's home address. The page saves these coordinates along with the patient's other data in the FRC_PATIENTS table.

The Evaluate Proximity activity shown below is an Invoke API that calls the EVALUATE_PROXIMITY PL/SQL package API to determine if the patient's home address is within the clinic's 400km service radius.

The function accepts a patient ID and returns an outcome string of "Within Range" or "Out of Range" that the following In Service Range? switch activity uses to decide which way to flow. As shown below, you configure the invoked function's parameters using the Property Editor after selecting each parameter name under the activity’s Parameters heading in the Workflows tree. Here, the P_PATIENT_ID parameter's value comes from the workflow Additional Data column ID containing the patient identifier associated to the current workflow instance. The Function Result is mapped to the V_RESULT workflow variable whose Result label shows under the Variables heading in the tree.

The code for the invoked function appears below. It returns a VARCHAR2 result of "Within Range" if the distance from the clinic to the patient's home address is less than 400km. Otherwise, it returns "Out of Range". The function is essentially a single SELECT…INTO statement using the SDO_DISTANCE function in the SDO_GEOM package to compute the distance in kilometers between the clinic and the patient's home address.

-- Result = 'Within Range' or 'Out of Range'
function evaluate_proximity(p_patient_id number)
return varchar2
is
    l_result_val varchar2(30);
    /*
     | Assume the clinic is located at:
     | 1244 Cotter Way, Hayward, CA 94541
     */
    c_clinic_lat  constant number := 37.68255;
    c_clinic_long constant number := -122.09026;
begin
    begin
        select case
                when sdo_geom.sdo_distance(
                        sdo_geometry(2001,4326,
                            sdo_point_type(pat.address_longitude,
                                           pat.address_latitude,
                                           null),null,null),
                        sdo_geometry(2001,4326,
                            sdo_point_type(c_clinic_long,
                                           c_clinic_lat,
                                           null),null,null),
                        tol=>1,
                        unit=>'unit=KM'
                    ) > 400 /* Kilometers */
                then 'Out of Range'
                else 'Within Range'
               end
        into l_result_val
        from frc_patients pat
        where pat.id = p_patient_id
          and address_latitude  is not null
          and address_longitude is not null;
    exception
        when no_data_found then
            l_result_val := 'Within Range';
    end;
    return l_result_val;
end; 

Use a Switch activity to decide which path the workflow takes.

The Evaluate Proximity activity configures the V_RESULT workflow variable to store its Function Return value. The subsequent In Service Range? is a Switch activity that as shown below is set to check this V_RESULT variable to decide which path to follow. The Switch activity identifies the source of the conditional decision. Then, each connection originating from that Switch activity configures a condition based on this source. When evaluating the next activity, the APEX engine follows the connection path whose condition evaluates to true.

As shown below, the connection named Within Range represents the condition that the Switch source V_RESULT equals the literal value "Within Range". The name of the connection need not coincide with the condition value, but sometimes that makes the diagram the most easy to understand. For example, you could change the connection name to Inside Service Area, as long as the condition remains the same, without affecting the workflow's flow.

The following table lists all the available Switch types you can use.

Use historical data to predict likely outcomes and automate confident decisions.

Historical data can be useful to predict the future. After years of considering patient requests manually for onboarding, Woods Clinic FRC_PATIENTS table holds the institutional knowledge of what patient requests were approved and which were rejected. It potentially holds clues to further automate the patient onboarding business process. You can train a machine learning model then use it to predict patient approvals. If historically, you approved or rejected a situation with high confidence, you can automatically handle similar new cases, leaving only unsure situations to be approved manually.

• Finding Best Algorithm for Your Data
  Use AutoML to find the machine learning model that best predicts your target outcome.
• Predicting Patient Onboarding Outcome
  Invoke a machine learning model to predict patient onboarding outcomes and route the workflow.

Show a workflow instance’s progress in a Workflow Diagram region.

In the Woods Clinic app, you can use a Workflow Diagram region in a page to visualize the progress of a particular instance. For example, imagine your end users asked to quickly understand where each recent inquiry was in the onboarding process. As shown below, you can add an link icon to your Recent Patient Inquiries page to pass the workflow instance ID to a modal page containing a Workflow Diagram region.

So it's clear that Danny Deng's procedure request currently awaits manual approval by back-office staff

Create an approval task and wait for its outcome before the workflow continues.

Your Woods Clinic app uses the approval task definition shown below to track the work item for manual approval of a new patient. Its Actions Source table is FRC_PATIENTS so its Subject can reference patient details using substitutions referencing columns like &FIRST_NAME. and &LAST_NAME. in that table.

As shown below, the Patient Onboarding workflow uses a Human Task - Create activity configured to use this task definition. It passes in the patient ID as the Details Primary Key value when creating the task using the workflow's Additional Data table ID column. In its Result section, the approval Outcome and actual Owner are returned into the workflow variables named TASK_OUTCOME and APPROVER. Since capturing these two values from an approval task is very common, the Workflow Builder creates these two variables automatically when you first add a Human Task - Create activity to the diagram. You can rename them, or configure different variables to receive these values as well.

Your Patient Onboarding workflow guides Danny Deng's request to manual approval, so, as shown below, back office user David sees Danny's pending approval in his Procedure Approvals task "inbox". You can create this page by picking a Universal Task List in the Create Page Wizard, and using the My Tasks report context. Notice David has used several Smart Filters to narrow the list by Priority, Application, and State. He also typed in a text search of "New Patient" to further filter the list. He's currently sorting the list by Create Date.

Notify rejected patients with an email template populated from workflow data.

From the Recent Patient Inquiries page, as shown below, David can see that patient Anya Ableton's request was automatically rejected. The figure shows a Woods Clinic backoffice application modal dialog with a Workflow Diagram region. The dialog title shows "Patient Onboarding Status - Anya Ableton". The workflow diagram is an end-user-friendly view of the same workflow Patient Onboarding diagram from App Builder Workflow Designer. The color and bolder style of certain activity shape outlines and connection arrows help the user understand the progress. In this example, David sees the Risk Level? decision followed the Risky path to the Auto-Reject Patient activity and on to the Notify Rejection activity.

In the Patient Onboarding workflow, your Notify Rejection activity of type Send E-Mail uses the Patient Rejected email template above, and passes &FIRST_NAME. and &DESCRIPTION. as the values of the template's two placeholders. Notice the From field references the &SENDER_EMAIL. column from the activity's Additional Data query. The value of &DESCRIPTION. used for the placeholder also comes from that query.

• Emailing or Push Notifying Users
  Notify workflow users with email or push notification activities.
• Using Email Template with Placeholders
  Use an email template with placeholders for workflow notification content.
• Querying Activity Data On Demand
  Query activity-level data to supply email fields and template placeholders.
• Receiving the Rejection Email
  Receive an automatic rejection email when the workflow denies a patient request.

Provision a portal user account when the workflow approves a patient request.

Consulting the Recent Patient Inquiries page again, as shown below, David can see that patient Cristina Cordero's request was automatically approved, her portal account got provisioned, and the workflow is waiting for her to complete her registration.

• Invoking the Provision Account API
  Invoke a provisioning API to create the approved patient’s account and assign its role.
• Inserting Account Creation Request
  Insert an account creation request for a background job to process outside the workflow session.
• Scheduling Create New Account Job
  Schedule the account creation job from SQL Developer Web so it runs as the workspace schema.
• Handling New Account Requests
  Process queued account requests, create users, and email portal credentials from PL/SQL.
• Notifying User of Temporary Password
  Email the approved patient their portal username and temporary password from PL/SQL.
• Receiving New Account Email
  

Wait for the approved patient to complete portal registration before the workflow continues.

Now it's clear in the diagram that the Patient Onboarding workflow for Cristina Cordero is waiting for her to complete her registration, before proceeding on to the last leg of the "journey": marking her registration as completed.

• Waiting Indefinitely for a Custom Signal
  Use a Wait activity without a timeout to pause until custom application logic continues it.
• Completing the New Patient Profile
  Complete portal registration by entering birth date and insurance details.
• Signaling the Workflow to Continue
  Save the patient’s registration data and signal the waiting workflow to continue.
• Understanding Onboarding is Complete
  Confirm that completing registration updates the portal and finishes the onboarding workflow.

Launch the Woods Clinic application like a native app with persistent authentication.

As shown below, earlier in the week when David logged into the Woods Clinic application, he checked the Remember me checkbox on the login page. He avoids having to type in his password every time he launches the app for the next 30 days.

As shown below, today David starts his day by launching the Woods Clinic app from the dock on his iMac. You enable the Progressive Web App (PWA) installable switch in your application definition, so with one click David added the app to his desktop to launch like other native Mac apps. He can do the same on his iPad and iPhone to launch the app with a tap on his home screen. When combined with the persistent authentication feature, this lets end-users immediately get to work just like they do with other apps they use everyday like Outlook, Slack, and more.

Your Woods Clinic app opens in its own window like a native desktop application. As shown below, a PWA application has no browser address bar. Using a List region associated with your Navigation Menu shared component, the home page shows a tile for each screen David has access to as clinic staff member.

From any screen in the app, David can click the "hamburger" icon with three-horizontal lines in the upper left corner. This pops open the collapsible side menu. As shown below, he uses it to navigate to the Scheduling page to see if any medical procedures await him there.

Schedule a medical procedure and let the workflow notify the patient.

David visits the Scheduling screen and sees that Danny Deng's medical procedure needs a date and doctor assigned. As shown below, he clicks on Danny's card to open his procedure details and schedules the exam for July 28th at 11:30am with Dr. Sandy Osborn.

When David confirms the scheduling details, as shown below, Danny's procedure appears on the calendar and no longer shows in the pending list.

Checking the Procedure Radar page, as shown below, David now sees Danny Deng's procedure in the Scheduled column on the Kanban board.

In the background, your Procedure Lifecycle workflow reacts to the change in status to "Scheduled". It automates notifying Danny Deng of the details. Later in the morning, when David visits the Pending Procedures page, as shown below, he clicks on the workflow icon next to Danny's procedure in the list.

The Procedure Lifecycle Status page for Danny Deng appears. It gives David a progress report at a glance. As shown below, he observes colored highlights on the workflow diagram that confirm it sent the patient email and push notifications.

Receive the scheduled procedure details by email.

Danny receives the email shown below and he makes a note of the date and time. As the message advises, he'll plan to show up 15 minutes early to register on the day.

Complete a patient procedure and generate draft discharge instructions with AI.

On the day of the appointment, when the procedure is over, Danny stops at the front desk before leaving. As shown below, David uses the three-vertical-dots dropdown menu on Danny's procedure record to open the Discharge page.

To send Danny on his way with some advice, on the Procedure Info tab, David ticks some common recommendations like sleeping at least 8 hours and to stay hydrated. He recommends Paracetamol if Danny experiences any discomfort.

On the Discharge Letter tab, David clicks the (Generate Initial Letter) button to get a head start on writing Danny's discharge letter. David adjusts the initial letter as needed, prints out a copy for Danny, and clicks (Apply Changes) to complete the discharge step.

Generate a pixel-perfect PDF invoice in the background with a report query and layout.

By using a report query and report layout, your Procedure Lifecycle workflow engages a remote printing service to generate a pixel-perfect invoice document on-demand, in the background.

• Printing Documents from a Workflow
  Print documents from a workflow by combining a Report Query and Report Layout.
• Retrieving Invoice Data
  Define a Report Query that supplies invoice data as JSON for the report layout.
• Formatting the Invoice in a Layout
  Create a Word report layout that includes invoice data using special tags.
• Generating the Data-Driven Document
  Generate a PDF invoice and store it for review, download, and email delivery.

Review the prepared invoice and enter the insurance coverage amount.

From the Review Invoices page, David sees the "Prepared" status badge on the invoice so he knows it's ready to review. He uses the three-vertical-dots dropdown menu to review the invoice details.

On the Document tab of the Review Invoice dialog that appears, David can inspect the PDF document the patient will eventually get. But for the moment, notice that it starts out with zero (0) dollars in the Covered Amount field.

David calls Danny Deng's insurance provider and learns that they will reimburse 5000 dollars of the medical procedure, so on the Overview tab David enters that amount in the Insurance Covers field. Your page uses a dynamic action event handler on the value change of this field to recalculate the Patient Pays field as shown below.

Regenerate an invoice in the background while preventing duplicate runs for the same procedure.

After David enters the definitive amount Danny Deng's insurance provider will reimburse, he can regenerate the invoice to reflect the updated value. As shown below, he does this using the Regenerate option on the invoice three-vertical-dots dropdown menu.

Since the invoice generation happens in the background, David is free to continue using the Woods Clinic app in the meantime. For example, as shown below, he visits the Overview page for a glance at the dashboard of four charts showing various clinic trends.

Your Review Invoices page makes quick work of offloading the invoice generation to the background using an Execution Chain page process type. It groups child processes, and they automatically run asynchronously when you enable its Run in Background switch. You enable its Serialize switch as well, and configure a Context Value Item. This combination ensures the APEX engine only lets one invoice generation per medical procedure ID happen simultaneously. The Generate Invoice child process invokes the same GENERATE_INVOICE procedure the workflow uses to prepare the original invoice draft.

Mark the invoice complete so the workflow emails it to the patient and advances the procedure lifecycle.

When David sees the regenerated invoice badge shows Prepared again, he reviews it to make sure the Covered Amount and patient's Balance Due look right. He confirms the insurance company's 5000 payment shows as expected, and the patient's remaining balance to pay is correct.

David marks the invoice as ready to send to the patient by choosing Invoice Complete from the three-vertical-dots dropdown menu as shown below.

When David reviews Danny Deng's procedure progress later, he notices, as shown below, that your workflow reacted to the completion of the invoice by emailing it to the patient.

He also sees Danny Deng's procedure shows in the Invoiced column in the Kanban board as shown below.

Record the patient’s payment so the procedure reaches its final status.

Patient Danny Deng gets the email, and reviews the invoice as shown below. He then calls Woods Clinic to pay the balance with his credit card.

David accepts Danny's payment on the phone, and, as shown below, he marks the invoice as settled using the Invoice Paid option on the three-vertical-dots dropdown menu.

This changes the status of Danny Deng's medical procedure row to "Paid" and, as shown below, David sees Danny's has moved off the Kanban board.

Confirm the paid procedure completed its workflow and full business lifecycle.

Reviewing patient procedures since mid-month on the All Procedures page, as shown below, David confirms that Danny Deng's medical procedure shows as Paid. He clicks the workflow icon next to the procedure to review the Procedure Lifecycle workflow progress one last time.

David sees that all lifecycle steps are complete for Danny Deng's medical procedure, so he moves on to help other patients. The Woods Clinic staff gives you positive feedback on how much more effective the clinic is running now that you've automated so many of the manual steps in their patient onboarding and medical procedure business processes.

Log procedure lifecycle events in a trigger and process them on a schedule with an automation.

To notify the workflow of data changes, you adopt a strategy shown below that decouples the lifecycle event for a status change from the work it entails. This separation keeps the user experience fast by offloading work to the background. Using an AFTER UPDATE FOR EACH ROW trigger on the FRC_PATIENT_PROCEDURES table, you call the ADD_EVENT procedure in the FRC_PROCEDURE_LIFECYCLE application package to register the event. That procedure inserts a row in the FRC_PATIENT_PROCEDURE_EVENTS table. The row contains the primary key of the patient procedure, and an event type indicating whether the procedure is new, or whether its schedule or status changed.

On a recurring schedule, an Automation shared component "wakes up" and queries the list of events to process in the current run using the EVENTS_LIST function in the same package. For each lifecycle event to process, the automation's action calls the PROCESS_EVENT procedure. The package calls CONTINUE_ACTIVITY in the APEX_WORKFLOW package, passing the event type value back to the workflow.

Define a package API that lets the trigger add events and the automation process them.

The FRC_PROCEDURE_LIFECYCLE package offers a simple API for managing lifecycle events. Its package specification shown below has procedures to add and process an event, and a function that returns the list of events.

package frc_procedure_lifecycle is
    type t_lifecycle_event is record (id frc_patient_procedure_events.id%type);
    type t_lifecycle_events is table of t_lifecycle_event;
    ------------------------------------------------------------------
    -- Add procedure lifecycle event to table processed by automation
    ------------------------------------------------------------------
    procedure add_event(
        p_procedure_id in number,
        p_type         in varchar2);
    ------------------------------------------------------------------
    -- Process procedure lifecycle event (called by automation)
    ------------------------------------------------------------------
    procedure process_event(
        p_event_id in number);
    ------------------------------------------------------------------
    -- Return list of event ids to process (used in automation query)
    ------------------------------------------------------------------
    function event_list
    return t_lifecycle_events
    pipelined;
end frc_procedure_lifecycle;

The ADD_EVENT procedure simply inserts a row into the FRC_PATIENT_PROCEDURE_EVENTS table. The table includes a primary key ID that is defaulted when null from an identity sequence, and a CREATED timestamp column that defaults to SYSTIMESTAMP. So here you only need to insert the patient procedure ID and the event type passed in (e.g. NEW, STATUS, or SCHEDULE).

-- external in package frc_procedure_lifecycle
procedure add_event(
    p_procedure_id in number,
    p_type         in varchar2)
is
begin
    insert into frc_patient_procedure_events(procedure_id, event_type)
    values (p_procedure_id, p_type);
end add_event;

The PROCESS_EVENT accepts the unique ID of a lifecycle event and signals a status or schedule change to a medical procedure's workflow. It calls the NOTIFY_DATA_CHANGED helper procedure to do so. If the process is successful, it deletes the event row it just processed from the FRC_PATIENT_PROCEDURE_LIFECYCLE table.

-- external in package frc_procedure_lifecycle
procedure process_event(
    p_event_id in number)
is
    l_success boolean := false;
begin
    for j in (select id, procedure_id, event_type
                from frc_patient_procedure_events
               where id = p_event_id )
    loop
        if j.event_type in ('STATUS','SCHEDULE') then
            l_success := notify_data_changed(j.procedure_id,j.event_type);
            if l_success then
                delete from frc_patient_procedure_events
                where id = p_event_id;
            end if;
        end if;
    end loop;
end process_event;

The EVENT_LIST function returns a table of T_LIFECYCLE_EVENT records. Each record contains a single ID field. Notice the PIPELINED modifier and corresponding PIPE ROW statement inside the loop. This combination defines a function whose result you can use in a SELECT statement as a row source by "wrapping" the function call with the TABLE() operator.

function event_list
return t_lifecycle_events
pipelined
is
begin
    for j in (select id
                from (select id,
                             row_number() over
                             (partition by procedure_id
                              order by created) as rn
                      from frc_patient_procedure_events
                  )
                 where rn = 1)
    loop
        pipe row (t_lifecycle_event(j.id));
    end loop;
exception
    when no_data_needed then
        null;
end event_list; 

Use a trigger to log status or schedule changes as lifecycle events.

When an FRC_PATIENT_PROCEDURES row is updated, it compares the :NEW.STATUS and :OLD.STATUS to decide if the status has changed. If so, it uses "STATUS" for the event type. If instead the procedure's date, doctor, or duration in hours changes, then the event type is "SCHEDULE". If either case occurs, then it calls ADD_EVENT in the FRC_PROCEDURE_LIFECYCLE package to do that.

trigger frc_patient_procedures_au
after update on frc_patient_procedures
for each row
declare
    l_event_type varchar2(80);
begin
    if :new.status != :old.status then
        l_event_type := 'STATUS';
    elsif    :new.procedure_date != :old.procedure_date
          or :new.doctor         != :old.doctor
          or :new.duration_hours != :old.duration_hours then
        l_event_type := 'SCHEDULE';
    end if;
    if l_event_type is not null then
        frc_procedure_lifecycle.add_event(:new.id, l_event_type);
    end if;
end;

Use an automation to process queued lifecycle events on a schedule.

The Handle Patient Lifecycle Event automation shown below is a shared component that defines a recurring background job. It runs periodically based on the Schedule Expression you create with the Schedule Builder dialog, or type in directly. This particular job runs in the background every two minutes.

The Query choice for Actions Initiated On means the automation's actions execute once for each row in the query you define on the Source tab.

select id
  from table(frc_procedure_lifecycle.event_list)

frc_procedure_lifecycle.process_event(:ID);

On the Action Execution tab, as shown below, you configure error handling and commit behavior for the job. By committing after each event row is processed, you ensure that a later event's error does not rollback the successful processing of earlier events.

Continue the waiting workflow activity and pass the event type into a workflow variable.

When the PROCESS_EVENT procedure in the FRC_PROCEDURE_LIFECYCLE package executes, it calls NOTIFY_DATA_CHANGED shown below. This calls a local helper function WORKFLOW_ID_FOR_WAITING_ACTIVITY to look up the procedure_lifecycle workflow instance ID for the current medical procedure row.

In the process, it ensures the workflow is active and has an activity with Static ID status_or_date_changed that is waiting to continue. If such a workflow instance is found, it passes its ID to CONTINUE_ACTIVITY in the APEX_WORKFLOW package to signal the waiting activity to continue.

It passes a map of name/value pairs back to the activity including the key V_WHAT_CHANGED whose value is the event type passed in. Any workflow variable whose name matches a map key value is automatically assigned the corresponding value in the process. Consequently, the V_WHAT_CHANGED workflow variable then contains the event type and the workflow continues to the next activity.

-- internal in package frc_procedure_lifecycle
------------------------------------------------------------------
-- Notify an instance of the procedure_lifecycle workflow associated
-- with the supplied patient procedure id that
------------------------------------------------------------------
function notify_data_changed(
    p_procedure_id in number,
    p_event_type   in varchar2)
    return            boolean
is
    l_workflow_id number := workflow_id_for_waiting_activity(
                                'procedure_lifecycle',
                                'status_or_date_changed',
                                p_procedure_id);
begin
    if l_workflow_id is not null then
        apex_workflow.continue_activity(
            p_instance_id     => l_workflow_id,
            p_static_id       => 'status_or_date_changed',
            p_activity_params => apex_global_application.vc_map(
                                    'V_WHAT_CHANGED' => p_event_type));
        return true;
    end if;
    return false;
end notify_data_changed;

The WORKFLOW_ID_FOR_WAITING_ACTIVITY helper function ensures the procedure lifecycle workflow for the Details Primary Key value passed in is active and that the desired activity is waiting. If both of these conditions hold, then it returns the ID of that workflow instance. Otherwise, it returns null.

-- internal in package frc_procedure_lifecycle
------------------------------------------------------------------
-- Return the active workflow id of static def id type passed in
-- associated with provided PK and having a waiting activity with
-- supplied static id that can be continued. Otherwise, null
------------------------------------------------------------------
function workflow_id_for_waiting_activity(
    p_workflow_static_id         in varchar2,
    p_waiting_activity_static_id in varchar2,
    p_details_pk                 in varchar2)
    return                          number
is
    l_ret number;
begin
    -- Only return the workflow id if the workflow is in ACTIVE
    -- state and the indicated continue activity is in WAITING state
    for wf in ( select workflow_id
                  from apex_workflow_activities ac
                  join apex_workflows wf using (workflow_id)
                 where wf.detail_pk              = p_details_pk
                   and wf.workflow_def_static_id = p_workflow_static_id
                   and ac.static_id              = p_waiting_activity_static_id
                   and ac.state                  = 'WAITING'
                   and wf.state_code             = 'ACTIVE')
    loop
        l_ret := wf.workflow_id;
    end loop;
    if l_ret is null then
        apex_debug.warn('workflow_id_for_waiting_activity: No active workflow (%s)'
                        |' with activity (%s) waiting for pk (%s)',
            p_workflow_static_id,
            p_waiting_activity_static_id,
            p_workflow_static_id);
    end if;
    return l_ret;
end workflow_id_for_waiting_activity;

Query recent debug messages to find workflow errors and related trace details.

The APEX engine logs unexpected errors. Query them using the APEX_DEBUG_MESSAGES view. Run the query below in the SQL Workshop > SQL Commands window to see the most recent debug messages in your workspace. These may include messages related to business process logic, or messages from page processing.

select apex_util.get_since(message_timestamp) as when, message
  from apex_debug_messages
 order by message_timestamp desc

A more detailed query against this view to see only workflow related messages is the following. Notice it also includes the WORKFLOW_INSTANCE_ID that encountered the problem:

select message_timestamp,
       elapsed_time,
       execution_time,
       message,
       page_id,
       workflow_instance_id,
       session_id,
       message_level,
       trim(replace(call_stack,'%')) as call_stack
 from apex_debug_messages
where application_id = :APP_ID
and apex_user = 'nobody'
and call_stack like '%\%WORKFLOW.%' escape '\'
and message_level < 9
order by message_timestamp desc

Enable debug tracing from the Developer Toolbar or directly on a workflow version.

Using the Developer Toolbar's Debug > Current Debug Level menu, as shown below, you can also enable debug tracing proactively. Start with the default Info level of detail. Other levels are increasingly verbose, but typically not required to triage most problems. This refreshes the current page and keeps your session in debug tracing mode until you turn if off by using the same menu.

Since your workflows run asynchronously in the background, the error may occur without any page interaction. In this case, as shown below, you can enable the debug tracing level on a workflow version in the Advanced section of the Property Editor.

View workflow debug traces by clearing page filters in the Debug Message Data window.

Use the Developer Toolbar's Debug > View Debug menu option to open the Debug Message Data window shown below. By default, the Interactive Report showing debug trace requests is filtered by the current application id and the current page. Since workflow activities execute in the background, they are not associated to a page. They also have no logged-in username. To see the workflow debug messages, as shown below, either:

• Disable the Page filter by unchecking its checkbox, or
• Clear the Page filter by clicking its "x" icon.

Then workflow messages for user nobody with no associated page appear.

The debug trace log usually includes enough information to provide clues on where the issue is happening.

Add custom debug messages with APEX_DEBUG.INFO when needed.

To log a message, use INFO as shown below. Consider starting your debug message with a prefix like ### that's easy to search for in the debug trace log. By surrounding the %s token with parentheses, a null value is easier to see.

apex_debug.info('### somevar=(%s), someexpr=(%s), anotherexpr=(%s)',
                :SOMEVAR,
                some_expression,
                another_expression);

apex_debug.info('### somevar=(%2), someexpr=(%0), anotherexpr=(%1)',
                some_expression,
                another_expression,
                :SOMEVAR);

The message, including substituted values, can be up to 32727 characters. However, messages exceeding 4000 characters appear "chunked" in the debug viewer. By default, the parameter values are truncated at 1000 characters each. Pass the optional p_max_length parameter to specify longer values.

Choose an action task to track completion or an approval task to capture an outcome.

When you create a Task Definition shared component, as shown below, set the Type to Action Task to track only that an assignee has completed the task. Choose Approval Task instead to capture the actual owner's APPROVED or REJECTED outcome. If you realize later that you picked the wrong task type, then you'll need to try again by creating a new task definition. You can only set this property at create time.

The task type determines the buttons end users will see in the Task List and Task Details pages when your app assigns them tasks to work on. As shown below, by default approval tasks display quick action buttons to approve or reject the task, while action tasks do not.

Similarly, while the default task detail page for an approval task shows (Approve) and (Reject) buttons, as shown below, the generated page for an action task has just a (Complete) button instead.

Associate a task with a table or query so task logic can reference related row data.

An end-user task often relates to a business entity in your application data model. A task definition models that work item, and a task instance represents one example of it for a particular row in that business entity's table. Task assignment and your actions that react to task status changes tend to reference or modify data in this related business entity row.

Then, wherever your app creates a new task instance, remember to supply the primary key value of the business entity row to which the new task relates.

For example if your Actions Source table's primary key column is ID, then you can reference :ID in action PL/SQL code or participant assignment expressions or queries. In email notifications use the substitution &ID. instead.

Use task parameters for values the task cannot get from its Actions Source row.

Use the pre-defined APEX$TASK_PK parameter to reference the value of the Details Primary Key passed in when a task instance is created. For many use cases, it is the only parameter value you need. Assuming you have also configured the task definition's Actions Source table or query, this combination can often provide all necessary context information. However, if there are additional values unique to the pending work item that are not (yet) available as Actions Source column values, you can define explicit task parameters to accept them when the task is created.

When defining a parameter, you provide a user-friendly label that can be in mixed case and include spaces. You also provide an alphanumeric static ID. You can reference any parameter's value throughout the task definition using the &STATIC_ID. or :STATIC_ID notation depending on the context. The APEX engine manages task parameter storage and makes them queryable in a generic way through the APEX_TASK_PARAMETERS dictionary view.

update emp
   set sal = :P_PROPOSED_SAL
  where empno = :EMPNO;

Assign potential owners and business administrators using case-sensitive usernames.

A task participant is either a potential owner or business administrator. A potential owner is a user you expect to perform the work the task represents, while a business administrator can help address a problem relating to the task.

For example, if user DAVID is the potential owner of the New Patient approval task, he sees it in his My Tasks list. He's also the one who can click (Approve) or (Reject). If user CARLA is the task's business administrator, she sees it in her Admin Tasks list. She can reassign the task to another colleague if a response is due today but DAVID is unexpectedly absent. CARLA the business administrator can also adjust a task's due date.

• Setting Static Task Participants
  Set fixed task participants when a simple process always assigns work to the same users.
• Assigning Participants Dynamically
  Assign task participants dynamically with queries, expressions, or functions that return case-sensitive usernames.
• Passing Participants in a Parameter
  Pass task assignees in a parameter and use it to assign potential owners.
• Accounting for Vacations or Absences
  Use a vacation rule procedure to add alternate task participants for absent users.

Understand task states and the actions that move a task through its lifecycle.

Every task instance has a state. It reflects the status of the task as it progresses through its lifecycle from creation to closure. Each state has a corresponding state code. The state is a mixed-case string like Information Requested, while the state code is uppercase and contains no spaces like INFO_REQUESTED. With the exception of this example, all other state codes are the uppercase version of the corresponding state name.

When you create a task, as shown in the diagram below, it starts in the Unassigned state when it has multiple potential owners, or the Assigned state when there is just one. The arrows in the diagram indicate the action that changes the state of a task instance. Once a task is Assigned, if it's an action task, the actual owner can complete it. For an approval task, they can approve or reject it.

Before they take action on the task, the actual owner can request additional information from the initiator of the task, which makes it appear in the initiator's task list until they supply the requested information. Users can comment on a task at any time, which doesn't change its state. That's why the Comment action is not reflected in the diagram.

A task's business administrator can cancel it at any time, add a new participant, and delegate it to a different actual owner. They can also renew a task that has expired or encountered an error in order to retry the lifecycle.

The flow diagram depicts the states of the task lifecycle and the actions that change state. It starts with Unassigned, progresses to Assigned, and continues to Completed. Optionally, the task can be Cancelled, Errored, Expired, or have additional Information Requested.

Create a Unified Task List page that shows users the tasks they need to act on.

Choose Unified Task List in the Create Page Wizard to add a task "inbox" page for end users.

• Tailoring Task List to Purpose
  Choose a report context to control which task instances a Unified Task List page shows.
• Showing Only Current Application Tasks
  Limit a Unified Task List page to tasks from the current application.
• Including Extra Columns in Task List
  Expose additional GET_TASKS columns so the task list can show more task details.
• Limiting Which Types of Tasks Appear
  Add a WHERE clause to show only selected task definition types.
• Joining Extra Tables into Task List
  Join application tables into the task list to display related business data.

Create a human task from a workflow to collect user input before the flow continues.

Use a Human Task - Create activity in a workflow to create an instance of a work item to be completed. A task definition formalizes the work item by configuring potential owners, parameters, and actions that can react to task lifecycle events. The workflow waits until the task completes its lifecycle before continuing, and can return the task id, the username of the actual owner, and the outcome back into workflow variables.

• Defining Updatable Task Parameters
  Define updatable task parameters to collect values from the task owner.
• Controlling Parameter Visibility
  Control which task parameters appear on task details pages.
• Parameterizing the Task Assignee
  Use a task parameter to supply the potential owner at runtime.
• Creating the Human Task Instance
  Create a human task instance and store its task ID and actual owner in workflow variables.
• Passing Workflow Parameter to a Task
  Pass a workflow parameter value into a task definition parameter.
• Assigning Task Parameters to Variables
  Store completed task parameter values in workflow variables for later activities.

Explore how users work with a Task Details page and how developers can tune it.

• Viewing Work Items You Requested
  View requested work items with a Unified Task List page filtered to tasks the user initiated.
• Adding a Task Details Page
  Add or assign a Task Details page so users can open a task from its subject link.
• Viewing Tasks Requiring Your Attention
  View assigned work items from a My Tasks page and open a task's details page.
• Requesting More Info for a Task
  Request more information from the task initiator when the assigned work is unclear.
• Responding to Requests for Information
  Respond to a request for information so the task can return to its owner.
• Editing Updatable Parameters
  Edit updatable task parameters from the Task Details page.
• Completing an Action Task
  Complete an action task when its work is done.
• Inspecting Overall Workflow Status
  Inspect workflow details to review status, history, parameters, and variables.
• Fine-Tuning a Task Details Page
  Fine-tune a generated Task Details page for the needs of each task definition.

Enable initiators to approve their own tasks when the business process requires it.

By default, a user who initiates an approval task isn't allowed to approve their own request. Even if their username is among the participants as a potential owner, the APEX engine filters out the initiator's username to enforce this common business practice. However, if you need to allow a requester to approve their own task to meet a business requirement, then enable the approval task definition's Initiator Can Complete switch to allow it.

Configure due dates and expiration behavior for time-sensitive tasks.

When tasks have a due date, they display additional information in the Unified Task List "inbox" page and users can sort by the due date to work on the most time-critical tasks first. At any time, a task's business administrator can change the due date if necessary.

When using one of these expiration behaviors, you can notify assignees about the impending deadline using a task action on the Before Expire event. You can declaratively send a reminder email or push notification, or execute custom code. When it finally expires, you can react to that with an action on the Expired event.

If you opt for the creation of a renewal task, know that it is a new task instance. It is linked to the previous now-expired task. When viewing a renewal task in a task details page, the end-user can optionally view the history of the complete "task renewal chain" if needed. Your dynamic participant assignment logic can reference the value of :APEX$TASK_RENEWAL_COUNT and :APEX$MAX_RENEWAL_COUNT to decide whether to assign the renewal task in a different way.

• Letting an Initiator Approve a Task
  Enable initiators to approve their own tasks when the business process requires it.

Use task actions to react to lifecycle events while a workflow waits.

Each Human Task - Create activity in a workflow creates a task instance, then waits for that task instance to complete its lifecycle before continuing to the next activity in the flow. Define task actions to react to changes in the task instance's state during a task's lifecycle.

• Choosing Event for an Action
  Choose the task event that should trigger an email, push notification, or code action.
• Accessing Task Information
  Reference task source columns and built-in substitutions from task actions.
• Notifying Assignees on Create
  Email potential owners when a new task requires their attention.
• Updating Columns on Completion
  A common requirement is to react to the outcome of an approval task by updating one or more columns in associated business entity row. You do this with an action on the Complete event.

Enable Deep Linking so notification links can open a specific application page.

Normally, a link to your application navigates the user to the application home page after login. If you want the user to navigate to a specific page by clicking on a link, as shown below, you need to enable Deep Linking under Shared Components > Security Attributes for your application.

Generate an absolute page URL for email or push notification links.

After enabling deep linking, use a function like the one below to generate a link to the page for the user to provide additional details. The GET_URL function in the APEX_PAGE package returns a relative URL by default. This means the https://example.com part of the URL is omitted. The browser interprets such a link relative to the page in which you embed it. Relative URLs are fine in an application page, but when generating a page link for an email or push notification, use an absolute URL instead. The user can click on this complete URL from any context.

Notice the ABSOLUTE_PAGE_URL function uses v('APEX$WORKFLOW_ID'). In a workflow activity context, this variable returns the workflow instance ID. The function assumes the target page number n has two hidden page items Pn_WORKFLOW_ID and Pn_ACTIVITY_STATIC_ID. Finally, note that it passes true to p_absolute_url and 0 to p_session.

function absolute_page_url(
    p_page                         in number,
    p_activity_static_id           in varchar2)
    return                            varchar2
is
    c_workflow_page_item constant varchar2(255)
        := 'P'||p_page||'_WORKFLOW_ID';
    c_activity_static_id_item constant varchar2(255)
        := 'P'||p_page||'_ACTIVITY_STATIC_ID';
begin
    return apex_page.get_url(
            p_page         => p_page,
            p_items        => c_workflow_page_item
                              ||','
                              ||c_activity_static_id_item,
            p_values       => v('APEX$WORKFLOW_ID')
                              ||','
                              ||p_activity_static_id,
            p_absolute_url => true,
            p_session      => 0,
            p_debug        => 'NO');
end absolute_page_url;

Email a user an absolute page link generated from workflow activity data.

To notify a user via email with a link to a data entry page, use a Send E-Mail activity. Since a workflow runs asynchronously in the background, the APP_USER system variable returns nobody. Therefore, this example workflow accepts a P_REQUESTED_USERNAME parameter, along with a P_SUBJECT parameter. The Additional Data query below on the Send E-Mail activity calls an ABSOLUTE_PAGE_URL function to generate the link to page 5, also passing the unique static ID of the wait activity user_provide_two_values. The query aliases the result to the LINK_TO_PAGE column name. A second SELECT list expression uses a subquery to lookup the email address of the requested username from the APEX_WORKSPACE_APEX_USERS view, using the value of the P_REQUESTED_USERNAME parameter as a bind variable. It aliases that result to REQUESTED_USER_EMAIL.

select
    workflow_user_input.absolute_page_url(
        5,
        'user_provide_two_values'
    ) as link_to_page,
   (select email
      from apex_workspace_apex_users
     where user_name = :P_REQUESTED_USERNAME) as requested_user_email
  from dual

As shown below, the Send E-Mail activity's Subject uses the &P_SUBJECT. substitution, the To field references the &REQUESTED_USER_EMAIL. from the Additional Data query result, and the Body HTML references the &LINK_TO_PAGE!ATTR. in an href attribute of an HTML anchor (<a>) element. The !ATTR modifier ensures the URL value is correctly formatted for inclusion in an HTML attribute.

Also notice the From field defaults to the &APP_EMAIL. substitution. This is a system variable whose value you configure in the Shared Components > Application Definition page in the Properties section as shown below.

Send a push notification with an absolute page link generated from workflow activity data.

To prompt a user via push with a link to a data entry page, use a Send Push Notification activity. Since a workflow runs asynchronously in the background, the APP_USER system variable returns nobody. Therefore, this example workflow accepts a P_REQUESTED_USERNAME parameter, along with a P_SUBJECT parameter. The Additional Data query below on the Send Push Notification activity calls an ABSOLUTE_PAGE_URL function to generate the link to page 5, also passing the unique static ID of the wait activity user_provide_two_values. The query aliases the result to the LINK_TO_PAGE column name.

select
    workflow_user_input.absolute_page_url(
        5,
        'user_provide_two_values'
    ) as link_to_page
  from dual

As shown below, the Send Push Notification activity's Title uses the &P_SUBJECT. substitution, the To field references the &P_REQUESTED_USERNAME. parameter, and the Link Target references #LINK_TO_PAGE# from the Additional Data query result.

Pause a workflow with a Wait activity until application logic continues it.

To pause a workflow until your app tells it to continue, use a Wait activity with no Timeout Type set. The Static ID you set on the activity is the value you pass to CONTINUE_ACTIVITY in the APEX_WORKFLOW package. Notice below that the Wait activity's Static ID is user_provide_two_values and that it leaves Timeout Type with no selection.

Open a notification link to enter requested data on a deep-linked page.

When a user receives a notification containing an absolute URL page link, as shown below, they can click the link to navigate to the page.

After logging in, since you enabled Deep Linking, they navigate to the page where they can provide the additional data and click (Submit Info) button to confirm their input. Notice the page uses a Static Content region with the Hero template to show an application icon and title. The title includes the workflow instance title and the label of the Wait workflow activity.

Retrieve workflow and activity labels to show context on the data entry page.

&P5_WORKFLOW_TITLE. - &P5_ACTIVITY_LABEL.

The GET_LABELS_FOR_PAGE procedure looks like this. It retrieves the title and activity label by joining the APEX_WORKFLOWS and APEX_WORKFLOW_ACTIVITIES views.

procedure get_labels_for_page(
    p_application_id     in  number default null,
    p_workflow_id        in  number,
    p_activity_static_id in  varchar2,
    p_workflow_title     out varchar2,
    p_activity_label     out varchar2)
is
    l_application_id apex_applications.application_id%type;
begin
    l_application_id := coalesce(p_application_id,v('APP_ID'));
    select a.label as activity_label,
           w.title as workflow_title
     into p_activity_label,
          p_workflow_title
     from apex_workflow_activities a
     join apex_workflows w
       on w.workflow_id = a.workflow_id
    where a.application_id = l_application_id
      and w.workflow_id    =  p_workflow_id
      and a.type_code      = 'NATIVE_WORKFLOW_WAIT'
      and a.state          = 'WAITING'
      and a.static_id      = p_activity_static_id;
exception
    when no_data_found then
        null;
end get_labels_for_page;

Continue a waiting workflow activity and return submitted values to workflow variables.

The page below contains hidden page items P5_WORKFLOW_ID and P5_ACTIVITY_STATIC_ID. When the user clicks the (Submit Information) button, a Provide Info page process in the Processing section executes a small block of PL/SQL code.

It first populates a parameter map containing the user's submitted values. Then it calls CONTINUE_ACTIVITY, passing the workflow ID, activity static ID, and the parameter map. The map is a set of key/value pairs. Notice that the keys are the Static ID values of three workflow variables. Any workflow variable whose Static ID matches a key in the parameter map is assigned its corresponding value when the activity continues.

declare
    l_user_input apex_application_global.vc_map;
begin
    l_user_input('V_PROVIDING_USERNAME') := :APP_USER;
    l_user_input('V_VALUE1')             := :P5_VALUE1;
    l_user_input('V_VALUE2')             := :P5_VALUE2;
    apex_workflow.continue_activity(
        p_instance_id     => :P5_WORKFLOW_ID,
        p_static_id       => :P5_ACTIVITY_STATIC_ID,
        p_activity_params => l_user_input);
end;

Inspect workflow variables and diagram progress after a waiting activity continues.

If a page calls CONTINUE_ACTIVITY in the APEX_WORKFLOW package to signal a Wait activity to proceed, any workflow variable whose Static ID matches a key in the parameter map is assigned its corresponding value. The default workflow detail page you generate when creating a Workflow Console in the Create Page Wizard displays the workflow variables and parameter values. It also displays a Workflow Diagram showing the workflow path completed so far. As shown below, while the requested username parameter was LEO, he must have forwarded the email to his colleague LUCY to click on the link and provide the data. Notice in the Variables region, the values she provided are reflected, along with her username. The page displays the user-friendly labels and values of the V_PROVIDING_USERNAME, V_VALUE1, and V_VALUE2 variables. The Completed badge in the Initiated Workflows Content Row region confirms this workflow instance is done executing. The visual diagram's highlighted activities also confirm this.

Consider using tasks when user input needs tracking, reminders, and a task inbox.

Prompting an end user with a link to a data entry page using email or push notification is straightforward. However, if the user dismisses the notification or the message gets lost among other emails, your business process may stall. A workflow could remain stuck waiting for input that never arrives.

Before building a custom solution to add these features, consider whether using a Human Task - Create activity combined with a well-designed task definition can save you significant time and effort.

Use AutoML to find the machine learning model that best predicts your target outcome.

Your application has access to over 30 different machine learning algorithms. You can use them to "mine" your historical data for interesting correlations. By training a machine learning model using the most appropriate algorithm, your applications can predict future outcomes using that model.

As shown below, Oracle Cloud's AutoML tool lets you run an experiment on your historical data to help you determine the machine learning algorithm that best predicts the outcome you seek. Setting up an experiment is simple. You tell the tool which table to analyze, what column represents the outcome you want to predict, and identify the table's primary key column. For the Woods Clinic app, you select the FRC_PATIENTS table, with its ID primary key column, and specify the patient's onboarding STATUS as the value you aim to predict.

AutoML runs an experiment trying all of its available machine learning algorithms on the Woods Clinic patient onboarding data. This experiment takes about 13 minutes to run. As shown below, it keeps a "Leader Board" of the algorithms that deliver the best prediction accuracy. Clicking on the name of any candidate model in the experiment, you can see the column values that had the strongest impact on predicting the historical outcome. It identifies the INSURANCE_PROVIDER and the INITIAL_PROCEDURE the patient requested during registration as the strongest predictors.

The experiment finds the Neural Network model with the system-generated name of NN_CE379F672F gives the best results, so you can use that model in your Patient Onboarding business process to attempt to automatically predict the patient onboarding approval.

Invoke a machine learning model to predict patient onboarding outcomes and route the workflow.

The Patient Onboarding workflow uses the machine learning model in two different Invoke API activities. First, the Evaluate Insurance Risk calls the EVALUATE_RISK function to detect if historically a patient with a similar request to the current one was automatically rejected. Office staff tells you that this automatic rejection was due to the patient's insurance not reimbursing the procedure.

The code for EVALUATE_RISK appears below. Predicting the outcome of the onboarding patient approval using the Neural Network machine learning model that won the AutoML experiment is a single SQL statement using the PREDICTION function. Its USING clause feeds the model the values of the current patient's insurance provider and initial procedure. Recall that these were the two columns that most strongly predicted the outcome. If the model predicts the STATUS to be "AutoRejected", then the function returns the outcome "Risky", otherwise it returns "Low Risk". The Evaluate Insurance Risk activity returns the Function Result into the V_RESULT workflow variable, and the subsequent Risk Level? Switch activity uses that variable containing the outcome value to decide the path to follow.

-- Result = 'Low Risk' or 'Risky'
function evaluate_risk(p_patient_id number)
return varchar2 is
    l_result varchar2(30);
begin
    /*
     | Machine Learning Model NN_CE379F672F was the winning model
     | automatically identified by using AutoML.
     */
    select
         prediction (NN_CE379F672F
         using
            pat.insurance_provider  as insurance_provider,
            pat.initial_procedure   as initial_procedure
         )
      into l_result
      from frc_patients pat
     where pat.id = p_patient_id;
    if l_result = 'AutoRejected' then
        return 'Risky';
    else
        return 'Low Risk';
    end if;
end;  

The Predict Approval activity uses Invoke API again to call a similar PREDICT_APPROVAL function. It accepts the previous prediction as a parameter. If that prediction was "AutoApproved" then it returns the outcome string "Auto-Approved", otherwise it returns the string "Manual. The Predict Approval activity returns the Function Result into the V_RESULT workflow variable, and the subsequent Auto‑Approve? Switch activity uses that variable containing the outcome value to decide the path to follow.

Notify workflow users with email or push notification activities.

It's equally easy to send users a push notification or an email. The figure shows the Workflow Designer palette offers both Send E-Mail and Send Push Notfication activities. The Woods Clinic app opted for the email option. Notice the Notify Rejection Send E-Mail activity is selected in the diagram, and the Email Template configured is Patient Rejected.

Use an email template with placeholders for workflow notification content.

To send the rejection email, you first setup an appropriate Patient Rejected shared component email template shown below. It includes two placeholders in the email Body #FIRST_NAME# and #PROCEDURE_DESCRIPTION#.

Query activity-level data to supply email fields and template placeholders.

As shown below, the Notify Rejection activity uses the following Additional Data query. It retrieves the sender's email, and the description of the requested medical procedure by joining in another table. The From field references the SENDER_EMAIL column value, while a placeholder value uses the DESCRIPTION column.

select prc.description,
       frc_app.get_sender_email as sender_email
from frc_patients pat
left join frc_medical_procedures prc
       on prc.code = pat.initial_procedure
where pat.id = :ID

The figure shows the App Builder Workflow Designer with the Notify Rejection activity selected in the diagram. The Property Editor shows the Additional Data SQL Query above, that includes a SENDER_EMAIL column in its select list. The Email Header section references its value using &SENDER_EMAIL. in the value of its From property.

Receive an automatic rejection email when the workflow denies a patient request.

With no back office staff involvement, your Patient Onboarding workflow now automatically notifies Anya Ableton that she'll need to seek her procedure at another facility.

When Ms. Ableton's procedure request is rejected, the Patient Onboarding workflow automatically notifies her as shown below, with no back office staff involvement. The figure shows Anya's email client with the subject line "Unable to Accommodate Your Request" and a message explaining she'll need to seek the care she needs at another facility.

Invoke a provisioning API to create the approved patient’s account and assign its role.

As shown below, your Patient Onboarding workflow's Provision Account activity is an Invoke API that calls the PROVISION_ACCOUNT procedure.

The PROVISION_ACCOUNT routine is below. It looks up the patient information using the patient ID passed in, then calls a CREATE_USER procedure in the FRC_APEX_USER package to create the APEX user account. Finally, it calls ADD_USER_ROLE in the APEX_ACL package to add the new account's username to the Access Control List (ACL) role with Static ID PATIENTS. The Woods Clinic application serves both back office staff and patients. It uses Authorization Schemes based on ACL role membership to automatically show each user appropriate pages and functionality based on their role.

procedure provision_account(
    p_patient_id number)
is
    l_patient t_patient    := patient(p_patient_id);
    l_clinic_app_id number := frc_app.clinic_app_id();
begin
    frc_apex_user.create_user(
        p_username   => l_patient.username,
        p_email      => l_patient.email,
        p_patient_id => p_patient_id
    );
    apex_acl.add_user_role(
        p_application_id => l_clinic_app_id,
        p_user_name      => l_patient.username,
        p_role_static_id => 'PATIENTS'
    );
end;

Insert an account creation request for a background job to process outside the workflow session.

To create an APEX user, you call the CREATE_USER procedure in the APEX_UTIL package. However, since this must be run from context outside of an APEX application session, the CREATE_USER function in the Woods Clinic package uses a two-step approach. Here, it simply inserts a row in a table to track the request to create a new user account. Then, a DBMS_SCHEDULER background job periodically processes the table to actually create the accounts.

-- package frc_apex_user
procedure create_user(
    p_username   in varchar2,
    p_email      in varchar2,
    p_patient_id in number)
is
    l_group_id number := apex_util.get_group_id(p_group_name=>'Patients');
begin
    insert into frc_apex_users_queue(
       username,
       group_id,
       email,
       patient_id)
    values (
       upper(p_username),
       l_group_id,
       p_email,
       p_patient_id);
end;

Schedule the account creation job from SQL Developer Web so it runs as the workspace schema.

Scheduling a background job is simple, but if you run the one-line PL/SQL block below inside in the SQL Commands or SQL Scripts pages of SQL Workshop, DBMS Scheduler infers a JOB_CREATOR of APEX_PUBLIC_USER. That generic schema does not have privileges to call APEX_UTIL.CREATE_USER.

Instead, use SQL Workshop > SQL Developer Web if your APEX instance administrator has enabled this integration. This menu option opens SQL Developer Web in a new browser tab, automatically logged in as the workspace schema user WOODS (the Woods Clinic schema). Then, from the SQL Worksheet in SQL Developer Web, the DBMS Scheduler correctly infers the WOODS schema as the Job Creator. Notice that the JOB_ACTION parameter passes the PL/SQL block to handle the new users queue, and the REPEAT_INTERVAL indicates to run every minute.

--
-- NOTA BENE: Run this outside of App Builder so that the
-- ~~~~~~~~~  JOB_CREATOR ends up correctly reflecting WOODS
--            instead of the APEX_PUBLIC_USER proxy user.
--
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
      job_name            => 'APEX_CREATE_USER_JOB',
      job_type            => 'PLSQL_BLOCK',
      job_action          => 'begin frc_apex_user.handle_new_users_queue; end;',
      number_of_arguments => 0,
      repeat_interval     => 'FREQ=MINUTELY',
      end_date            => NULL,
      enabled             => TRUE,
      auto_drop           => TRUE,
      comments            => ''
      );
END;

Process queued account requests, create users, and email portal credentials from PL/SQL.

A DBMS_SCHEDULER job calls the procedure below every minute to check the new account creation request table. It loops over any rows in that table, generates a temporary password, creates the new APEX end-user account as part of the Patients group that was recorded in the request table. Then it notifies the new user of their patient portal credentials by calling a SEND_APPROVAL_EMAIL in the FRC_ONBOARDING package. This email could have been sent using another Send E-Mail workflow activity, but doing it from PL/SQL is an opportunity to see how to send mail programmatically.

-- package frc_apex_user
procedure handle_new_users_queue is
    l_temp_password varchar2(255);
begin
    -- set workspace context
    apex_util.set_workspace('WOODS');
    for x in (select * from frc_apex_users_queue)
    loop
        -- Generate temporary password
        l_temp_password := generate_password();
        -- create the apex user in the supplied group if present
        apex_util.create_user
        (
            p_user_name     => x.username,
            p_web_password  => l_temp_password,
            p_email_address => x.email,
            p_group_ids     => x.group_id
        );
        execute immediate q'~
          begin
             frc_onboarding.send_approval_email(
                :PATIENT_ID,
                :TEMP_PASSWORD);
          end;
        ~' using x.patient_id,
                 l_temp_password;
        -- remove the user from the queue
        delete from frc_apex_users_queue where id = x.id;
    end loop;
end; 

Email the approved patient their portal username and temporary password from PL/SQL.

The final step of user account provisioning is notifying the user of their new username and temporary password. While you could send the email from the workflow, here you explore the programmatic way to send an email using SEND_MAIL in the APEX_EMAIL package. It's using an email template for the new account notification that uses a number of placeholder values. Notice that the P_PLACEHOLDERS argument expects to receive the placeholder name/value pairs as a JSON document. The local APPROVAL_PLACEHOLDERS helper function builds up that JSON document using PL/SQL's native JSON_OBJECT_T type and returns it as a CLOB in JSON format.

-- in package frc_onboarding
procedure send_approval_email(
    p_patient_id         in number,
    p_temporary_password in varchar2)
is
    l_patient   t_patient := patient(p_patient_id);
    -----------------------------------------------
    function approval_placeholders(
        p_patient            in t_patient,
        p_temporary_password in varchar2)
        return                  clob
    is
        l_json json_object_t := json_object_t;
        l_clinic_app_url varchar2(255);
    begin
        l_clinic_app_url := frc_app.url_for_page(
                                frc_app.clinic_app_id,'home');
        l_json.put('FIRST_NAME',
                    p_patient.first_name);
        l_json.put('PROCEDURE_DESCRIPTION',
                    p_patient.procedure_description);
        l_json.put('PORTAL_URL',
                    l_clinic_app_url);
        l_json.put('USERNAME',
                    lower(p_patient.username));
        l_json.put('TEMPORARY_PASSWORD',
                    p_temporary_password);
        return l_json.stringify();
    end;
begin
    apex_mail.send(
        p_to                 => l_patient.email,
        p_from               => frc_app.get_sender_email,
        p_template_static_id => frc_app.c_approved_email_template,
        p_placeholders       => approval_placeholders(
                                    l_patient,
                                    p_temporary_password),
        p_application_id     => frc_app.onboarding_app_id());
    -- Force mail to be sent immediately
    apex_mail.push_queue;
end;  

As shown below, your Patient Onboarding workflow's Provision Account activity results in automatically sending Cristina Cordero the credentials for her new account.

Use a Wait activity without a timeout to pause until custom application logic continues it.

Your Patient Onboarding workflow uses a Wait activity to pause until the new patient completes their profile by providing a date of birth and proof of insurance scan. If a Unified Task List "inbox" is not the kind of user experience you want for your end users, you can use the combination of a Wait activity and a later call to CONTINUE_ACTIVITY in the APEX_WORKFLOW package to craft a custom user experience. Notice that by not choosing a Timeout Type, you opt to wait indefinitely until your own application logic in another part of the application signals the waiting activity should continue.

Complete portal registration by entering birth date and insurance details.

Cristina clicks the link in her notification email and logs into the portal for the first time. After changing her initial password, she sees tiles for functionality available to her. To complete her profile, she clicks on Complete Registration.

As shown below, Cristina enters her date of birth on the first step of the Complete Registration wizard, then clicks (Next >).

On step 2 of the wizard, she uploads her proof of insurance and enters her insurance policy number, then clicks (Finish).

Save the patient’s registration data and signal the waiting workflow to continue.

The last page of the Complete Registration wizard uses an Invoke API page process to call the COMPLETE_REGISTRATION procedure below. Each data item the wizard collected gets passed in by declaratively configuring the parameter value expressions. The value of an Image Upload page item provides the unique file name in the APEX_APPLICATION_TEMP_FILES table where the APEX engine stores the uploaded file for application-specific processing.

Notice that after retrieving the uploaded proof of insurance scan image blob from the temporary file table, it updates Cristina Cordero's FRC_PATIENTS row with date of birth and proof of insurance information. For fraud detection purposes, the wizard also uses a Get Current Position dynamic action step in a Page Load event handler to record the latitude and longitude of the user's position when uploading the proof of insurance.

After updating the patient row, the procedure calls CONTINUE_ACTIVITY in the APEX_WORKFLOW package to signal that Cristina Cordero's Patient Onboarding workflow should now proceed.

procedure complete_registration(
    p_patient_id              in number,
    p_year_of_birth           in number,
    p_month_of_birth          in number,
    p_day_of_birth            in number,
    p_insurance_policy_number in varchar2,
    p_scan_uploadname         in varchar2,
    p_scan_latitude           in number,
    p_scan_longitude          in number)
is
    l_scan_blob   blob;
    l_params apex_application_global.vc_map;
begin
    -- get uploaded insurance proof scan blob from temp storage
    for scan in (select blob_content
                   from apex_application_temp_files
                  where name = p_scan_upload_name)
    loop
        l_scan_blob := scan.blob_content;
    end loop;
    -- update patient to include insurance proof scan blob
    update frc_patients
    set insurance_proof_scan = l_scan_blob,
        date_of_birth = to_date(
                        to_char(p_year_of_birth)||
                        to_char(p_month_of_birth,'00')||
                        to_char(p_day_of_birth,'00'),
                        'YYYYMMDD'),
        insurance_policy_number   = p_insurance_policy_number,
        insurance_proof_latitude  = p_scan_latitude,
        insurance_proof_longitude = p_scan_longitude
    where id = p_patient_id;
    -- Continue onboarding workflow that's waiting for new patient
    -- registration to be complete. Use workflow id from the most
    -- recent onboarding workflow associated with this patient id
    for wf in (select workflow_id
                 from apex_workflows
                where workflow_def_static_id = 'patient_onboarding'
                  and detail_pk = to_char(p_patient_id)
                order by start_time desc
                fetch first row only)
    loop
        -- Continue the complete_registration "Wait" activity
        apex_workflow.continue_activity(
            p_instance_id     => wf.workflow_id,
            p_static_id       => 'complete_registration',
            p_activity_params => l_params);
    end loop;
end;

Confirm that completing registration updates the portal and finishes the onboarding workflow.

After completing her profile, as shown below, Cristina Cordero sees the Complete Registration tile is replaced by a Your Profile tile. This confirms she provided all necessary information the clinic requires.

In the back office, David confirms Cristina Cordero completed her Patient Onboarding workflow by glancing at the visual workflow diagram. As shown in the figure, now the End activity is highlighted on the path that ended by running the Completed Registration activity.

Print documents from a workflow by combining a Report Query and Report Layout.

As David sees from the lifecycle diagram below, in the background, your Procedure Lifecycle workflow reacts when Danny Deng's medical procedure status changes to "Completed". It prepares a draft invoice for Woods Clinic staff to review with Danny's insurance provider.

The workflow's Prepare Invoice activity invokes the PREPARE_PROCEDURE_INVOICE procedure in the FRC_CLINIC package. First, it inserts a row in the FRC_INVOICES table with a reference to the medical procedure ID. Then, it generates the pixel-perfect invoice by combining two shared components: a Report Query and a Report Layout.

Define a Report Query that supplies invoice data as JSON for the report layout.

Your Invoice Report Query shared component shown below defines the data the invoice needs. You provide one or more Source Queries whose results the APEX engine includes in a single JSON document sent to the remote printing service. For the invoice, a single query suffices.

Using the Set Bind Values dialog, you can set example values for any bind variables your source queries reference. Then, clicking the (Download) button produces a JSON file containing example data. You use it to learn the names to reference to inject data values in appropriate places in your report layout. You use the Static ID invoice_query to identify the Report Query definition at document generation time.

The Invoice source query appears below. It uses a PROCEDURE_ID_TO_INVOICE application item as a bind variable for the medical procedure whose ID needs an invoice printed.

  select  invoice_number,
          invoice_date,
          procedure_id,
          patient_id,
          patient_name,
          address,
          doctor_name,
          medical_procedure,
          procedure_date,
          status,
          amount_due,
          insurance_provider,
          covered_amount,
          balance_due
 from frc_procedures_invoices_v
 where procedure_id = :PROCEDURE_ID_TO_INVOICE

Notice this query in the Report Source edit page below, as well as the Data Loop Name. This name identifies the array containing this source query's result rows in the JSON data document.

After downloading an example JSON data document for medical procedure 2301, you can open it in an editor like Visual Studio Code shown below. Notice the "invoice" Data Loop Name and its corresponding array of a single row of JSON medical procedure query results. The property names in this document are vital when creating your report layout.

The figure shows Visual Studio Code with the SQL Developer Extension installed. On the left, the downloaded JSON appears containing the data that will drive the report layout. On the left, a SQL Developer worksheet tab where you can run SQL queries and observe the results.

Create a Word report layout that includes invoice data using special tags.

Your Invoice Report Layout shared component shown below defines a Static ID you use when generating the invoice. It includes the Microsoft Word document template that details how the invoice looks and where it includes data.

Your Microsoft Word document for the invoice uses the tag syntax the Oracle Document Generator cloud service defines to loop over JSON data arrays and include values of data object properties. Notice the {#invoice} tag opens the data loop and the matching {/invoice} closes the loop. The invoice name in these tags is the Data Loop Name you defined in your Report Query, which used it as the JSON array name for the query result rows. Inside this loop, tags like {patient_name} and {balance_due} among others reference the relative names of JSON properties in the row of invoice data.

Generate a PDF invoice and store it for review, download, and email delivery.

The workflow's Prepare Invoice activity ends up calling the GENERATE_INVOICE procedure below in the FRC_CLINIC package. This procedure looks up the procedure ID from the invoice ID, sets that value into the application item PROCEDURE_ID_TO_INVOICE used as a bind variable in the report query, and calls GENERATE_DOCUMENT in the APEX_PRINT package to create the pixel-perfect PDF invoice as a BLOB. Notice it passes the Static ID values of the Report Query and Report Layout, as well as the current application ID using V('APP_ID').

Finally, it updates the FRC_INVOICES row to assign the generated PDF to its DOCUMENT column. This lets staff review it interactively in the Woods Clinic app and download it. It also helps your workflow attach it to the email it eventually sends to the patient.

-- in frc_clinic package
procedure generate_invoice(
    p_invoice_id in number)
is
    l_pdf blob;
    l_procedure_id number;
begin
    -- Lookup patient procedure id for invoice for report query bind var
    select patient_procedure_id
      into l_procedure_id
      from frc_invoices
     where id = p_invoice_id;
    -- Set application item used as a bind variable in report query
    apex_session_state.set_value('PROCEDURE_ID_TO_INVOICE',l_procedure_id);
    update_invoice_status(p_invoice_id,'Processing');
    commit;
    -- Generate the pixel-perfect PDF invoice
    l_pdf := apex_print.generate_document(
                p_application_id          => v('APP_ID'),
                p_report_query_static_id  => 'invoice_query',
                p_report_layout_static_id => 'invoice_layout');
    update frc_invoices
    set status = 'Prepared',
        document = l_pdf
    where id = p_invoice_id;
exception
    when others then
        update_invoice_status(p_invoice_id,'Error');
        commit;
        raise;
end;

Set fixed task participants when a simple process always assigns work to the same users.

As shown below, in a simple application, either or both of the Potential Owner or Business Administrator participants can be a static value like DAVID or CARLA. But normally you use one of the Value Type settings like SQL Query, Function Body, or Expression to dynamically determine one or both of these participant lists.

Assign task participants dynamically with queries, expressions, or functions that return case-sensitive usernames.

To assign task participants dynamically, use a participant Value Type of SQL Query, Function Body, or Expression. When using the SQL Query option, your single column SELECT list returns one case-sensitive username per row. For the Function Body or Expression options, return a comma-separated list of case-sensitive usernames.

Your query, function, or expression can use bind variable syntax to reference task parameter values, action source column names, application items, or other APEX substitution strings.

your_app_pkg.owners_for_task(:APEX$TASK_ID, :APEX$TASK_PK)

By passing the value of pre-defined bind variables like APEX$TASK_ID and APEX$TASK_PK, your generic function can lookup related information from APEX dictionary views like APEX_TASKS and from your application table using the primary key value. If needed, you can join in task definition information from the APEX_APPL_TASKDEFS view, using the value of the TASK_DEF_STATIC_ID column in APEX_TASKS.

Pass task assignees in a parameter and use it to assign potential owners.

:P_ASSIGNEE_LIST

select column_value
from apex_string.split(:P_ASSIGNEE_LIST,':')

Use a vacation rule procedure to add alternate task participants for absent users.

Sometimes, stakeholders involved in task assignments are out of the office. Your task definition can use a vacation rule procedure to run custom business logic after the APEX engine determines participants using your original Participants configuration. The rule affects both initial task participant assignment, as well as task delegation.

procedure your_vacation_rule (
  p_param    in apex_human_task.t_vacation_rule_input,
  p_result  out apex_human_task.t_vacation_rule_result )

To process the list of existing participants, access the original_participants list from the p_param input record. If any alternative assignees are needed, populate the participant_changes list in the output record. For each participant change you populate the original username, the username who replaces them, and a reason explaining why that is recorded in the task history. The participant changes you return automatically become additional participants on the task.

as the value of the Vacation Procedure property. The following example vacation rule procedure adds SUSAN as an additional approver for DAVID due to some personal time off. In your application, you can read absence assignments from a table, but this code illustrates the fundamentals.

procedure handle_absences(
    p_param   in apex_human_task.t_vacation_rule_input,
    p_result out apex_human_task.t_vacation_rule_result )
is
    l_participant apex_human_task.t_task_participant;
    l_change      apex_human_task.t_task_participant_change;
    --------------------------------------------------------
    function potential_owner(
        p_username in varchar2)
        return apex_human_task.t_task_participant
    is
    begin
        return apex_human_task.t_task_participant(
                apex_human_task.c_task_potential_owner,
                apex_human_task.c_task_identity_type_user,
                p_username);
    end potential_owner;
    --------------------------------------------------------
    function absent_user_cover(
        p_original_username in varchar2,
        p_covering_username in varchar2,
        p_reason            in varchar2)
        return apex_human_task.t_task_participant_change
    is
    begin
        return apex_human_task.t_task_participant_change(
            potential_owner(p_original_username),
            potential_owner(p_covering_username),
            p_reason);
    end absent_user_cover;
begin
    -- Loop over original participants
    for j in 1..p_param.original_participants.count loop
        l_participant := p_param.original_participants(j);
        -- If the participant is DAVID as a potential owner
        if     l_participant.type  = apex_human_task.c_task_potential_owner
           and l_participant.value = 'DAVID'
        then
            -- Add participant change: SUSAN covers for DAVID's personal time off
            p_result.participant_changes(p_result.participant_changes.count + 1) :=
                absent_user_cover('DAVID','SUSAN','Personal Time Off');
            p_result.has_participant_changes := true;
        end if;
    end loop;
end handle_absences;

Choose a report context to control which task instances a Unified Task List page shows.

When creating a Unified Task List page, the Report Context you choose determines the filtered list of task instances the page displays at runtime.

You can use the page as it comes, or customize it in Page Designer as needed. Create as many task inbox pages as your app requires, choosing an appropriate Report Context in each one as needed.

Limit a Unified Task List page to tasks from the current application.

By default, the task list displays tasks from all applications in the workspace. If instead you want it to only display tasks related to the current application, one small change is required. As shown below, the Content Row region's query retrieves the task list from the GET_TASKS function in the APEX_HUMAN_TASK package. This function returns a table of task information records. By using the table() operator, this result works just like a regular table in a SELECT statement.

Notice that the p_application_id parameter is commented out using two consecutive dashes (--), the PL/SQL line comment.

select task_id,
       task_type,
         ⋮
       badge_state
  from table ( apex_human_task.get_tasks (
                   p_context            => 'MY_TASKS',
                   p_show_expired_tasks => :P33_SHOW_EXPIRED
                   --, p_application_id => :APP_ID
                   ) )

To only display tasks related to the current application, as shown below, simply remove the two dashes to uncomment the p_application_id parameter line. This passes the current application id as the value of this parameter, and limits tasks returned to those from the current application only.

select task_id,
       task_type,
         ⋮
       badge_state
  from table ( apex_human_task.get_tasks (
                   p_context            => 'MY_TASKS',
                   p_show_expired_tasks => :P33_SHOW_EXPIRED
                   , p_application_id => :APP_ID
                   ) )

Expose additional GET_TASKS columns so the task list can show more task details.

select *
  from table ( apex_human_task.get_tasks (
                   p_context            => 'MY_TASKS',
                   p_show_expired_tasks => :P33_SHOW_EXPIRED,
                   p_application_id     => :APP_ID
                   ) )

This causes Page Designer to show all available column names under the region's Columns heading in the Rendering tab. The following table highlights a few important GET_TASKS result columns.

Add a WHERE clause to show only selected task definition types.

In a new Unified Task List page, the Content Row region's query contains no WHERE clause. The user can use the Smart Filters region at runtime to narrow the list, but sometimes you may want to limit which tasks show up in the list at all. The table() operator wrapping the GET_TASKS function call lets its results participate in the query like a regular table. So, just add a WHERE clause to the query to filter the list. For example, as shown below, to show only tasks based on two specific task definition types you can add a WHERE clause that filters on the TASK_DEF_STATIC_ID column.

select task_id,
       task_type,
         ⋮
       badge_state
  from table ( apex_human_task.get_tasks (
                   p_context            => 'MY_TASKS',
                   p_show_expired_tasks => :P33_SHOW_EXPIRED,
                   p_application_id => :APP_ID
                   ) )
 where task_def_static_id in ('NEW_PATIENT','PURCHASE_OFFICE_SUPPLIES')

Join application tables into the task list to display related business data.

In a new Unified Task List page, the Content Row region's select list includes only columns related to the task itself. Sometimes, it's useful to join in data from other application tables using the Details Primary Key value in the join clause.

For example, the following query adds a left join to the FRC_PATIENTS table based on the value of the DETAIL_PK column for task instances of the NEW_PATIENT type. This lets the task list page display the related patient name.

Notice the table alias T is added for the table(apex_human_tasks.get_tasks()) row source, and that table alias is added to the columns in the SELECT list coming from that source. In addition, the table alias P for the FRC_PATIENTS table lets the PATIENT_NAME column concatenate the first name and last name values from the related patient table.

select t.task_id,
       t.task_type,
         ⋮
       t.badge_state,
       p.first_name||' '||p.last_name as patient_name
  from table ( apex_human_task.get_tasks (
                   p_context            => 'MY_TASKS',
                   p_show_expired_tasks => :P33_SHOW_EXPIRED,
                   p_application_id     => :APP_ID
                   ) ) t
  left join frc_patients p
    on p.id = case task_def_static_id
                when 'NEW_PATIENT'
                then to_number(t.detail_pk)
              end
 where task_def_static_id in ('NEW_PATIENT','PURCHASE_OFFICE_SUPPLIES')

Define updatable task parameters to collect values from the task owner.

If a task requires the actual owner to supply additional data values, model them as updatable task parameters. Notice below that the Provide Two Random Numbers task definition accepts two read-only ones to specify the purpose of the request and the requested username. It also defines two updatable parameters P_VALUE1 and P_VALUE2. They let the actual owner provide two values while completing the task. The workflow can retrieve these task parameter values using an API in the APEX_HUMAN_TASK package.

Control which task parameters appear on task details pages.

Use a parameter's Visible setting to control whether it appears by default in the task details page. This visibility hint lets you avoid displaying the parameter even in a custom task details page by including IS_VISIBLE='Y' filter in your query against the APEX_TASK_PARAMETERS view.

Use a task parameter to supply the potential owner at runtime.

The Provide Two Random Numbers task definition uses an Expression to compute its Potential Owner. As shown below, the simple expression just returns the value of the P_REQUESTED_USERNAME parameter by referencing it as a bind variable. This lets the workflow pass in the desired assignee for the task in the Human Task - Create activity.

Create a human task instance and store its task ID and actual owner in workflow variables.

The Get Two Numbers activity of type Human Task - Create shown below configures its Definition property to use the Provide Two Random Numbers task definition. Notice how it uses the special system substitution value APEX$WORKFLOW_INITIATOR to pass along the username who started the workflow as the initiator of the task instance it's creating. The Task ID Item and Owner property values name two workflow variables V_TWO_NUMBERS_TASK_ID and V_PROVIDING_USER that will store the task id and actual owner of the task instance upon completion.

Pass a workflow parameter value into a task definition parameter.

A workflow configures task definition parameters using corresponding items in the Workflows tree indented under the Human Task - Create activity's Parameters heading. As shown below, the Get Two Numbers activity configures the task definition parameter P_REQUESTED_USERNAME to the value of the workflow's parameter of the same name using the &P_REQUESTED_USERNAME. substitution in the Static Value field in the Property Editor.

Store completed task parameter values in workflow variables for later activities.

The workflow pauses after creating the human task instance. When the task's lifecycle completes, it proceeds to the next activity. If later workflow activities need to reference the values of the updatable task parameters acquired during the human task lifecycle, they can use the GET_TASK_PARAMETER_VALUE function in the APEX_HUMAN_TASK package.

Its first parameter expects the unique id of a task, motivating why it's useful for a Human Task - Create activity to return its task ID into a workflow variable. Recall that in this workflow, the return variable for the task ID is V_TWO_NUMBERS_TASK_ID.

:V_VALUE1 := apex_human_task.get_task_parameter_value(
               :V_TWO_NUMBERS_TASK_ID,'P_VALUE1');
:V_VALUE2 := apex_human_task.get_task_parameter_value(
               :V_TWO_NUMBERS_TASK_ID,'P_VALUE2');

View requested work items with a Unified Task List page filtered to tasks the user initiated.

User LUCY visits a Requested Tasks page to see a list of requests she's made. In this simple example, there is only the Provide Two Random Numbers task definition in the list, but in a real application she would see work items of any type that she initiated. The page shown below is an example of a Unified Task List page you can add to an application with the Create Page Wizard. Since its goal is to show users the tasks they initiated, it uses the Report Context of "Initiated by Me". If a task instance has an associated Task Details page, then the task Subject line is a link Lucy can click on to review the details of the task.

Add or assign a Task Details page so users can open a task from its subject link.

If you leave the field blank, APEX asks your confirmation to proceed using the next available page number.

f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues

f?p=&APP_ID.:6:&SESSION.::&DEBUG.:RP,6:P6_TASK_ID:&TASK_ID.

It leaves the Request segment empty and uses substitution values for &APP_ID., &SESSION., &DEBUG., and &TASK_ID. in the respective segments of the p query string parameter value. In short, the URL navigates to page 6 in the current application, using the current session, and the current debug mode, passing the value of the current task instance's unique ID as the value of the P6_TASK_ID parameter in that page.

View assigned work items from a My Tasks page and open a task's details page.

User LEO visits his My Tasks page to view any work items requiring his attention. As shown below, he sees LUCY initiated a task that needs his input. To review the details of a task, Leo can click on any task Subject link to open its Task Details Page.

Request more information from the task initiator when the assigned work is unclear.

Clicking on a task Subject in the My Tasks page opens its Task Details Page. As shown below, by default, it contains sections that display the values of task parameters Purpose, Value1, and Value2, along with a Comments and History section, too. Leo notices Edit links next to the Value1 and Value2 parameters, but remains a bit confused. He clicks the three-dot menu and requests more information about the task from Lucy, the requester.

A dialog box appears and, as shown below, Leo types in his question, "Do I just edit the values in the Details section?" and clicks (Request Information).

Respond to a request for information so the task can return to its owner.

While her My Tasks list was previously empty, she now sees the task she requested show up in the list since it requires her attention. The Information Requested badge gives her a clue that Leo must have a question.

Clicking on the Subject, the Task Details page opens, and as shown below, Lucy clarifies, "Yes, please. That will be perfect!" Then she clicks (Submit Information) to send the task back to Leo along with her clarification.

Edit updatable task parameters from the Task Details page.

The work item shows up again in Leo's My Tasks page and he clicks to open the Task Details page. He sees the conversation with Lucy in the Comments section and proceeds. As shown below, he enters the two values Lucy needs by clicking the Edit link next to the Value1 and Value2 parameters.

Complete an action task when its work is done.

After entering values for Value1 and Value2 in the Task Details page, Leo is done. He clicks the (Complete) button to mark the work item as completed.

Inspect workflow details to review status, history, parameters, and variables.

From her Initiated Workflows page, Lucy can see all the workflows she created. It's a Workflow Console page with Report Context of "Initiated by Me". Clicking on the Title link, the Workflow Details page opens. As shown below, the workflow details page the wizard generates displays the variable and parameter values as well as visual diagram of the workflow's current status. Scrolling up and down, Lucy can also see the activity steps list and history that are not visible below. She sees the workflow has completed, and notices the Value1 and Value2 of the updatable task parameters were assigned to the workflow values of the same names.

• Reviewing a Completed Task
  Review completed task details from the list of work items you requested.

Fine-tune a generated Task Details page for the needs of each task definition.

Your generated Task Details Page is a great starting point. Consider fine-tuning it to meet your particular task definition's needs. It offers useful built-in functionality, but know that it illustrates one particular way to use the native Human Task page processes and APEX_HUMAN_TASK programmatic APIs. Understand what you get by default, then decide on a task by task basis what to remove, add, or change.

While in theory you could use a single, generic task definition page with every task definition, the user experience would suffer. In practice, if a work item is unique enough to model as a task definition, it benefits from a task-specific Task Details Page. This way, you can show the assignee additional context tailored to that task definition, along with additional fields and functionality as required.

• Adding Page Items to Edit Parameters
  Add page items to give users a better experience when entering task parameter values.
• Making Items Conditionally Read Only
  Make task page items editable only for the assigned owner while the task is active.
• Loading Parameter Values Into Items
  Load updatable task parameter values into page items before rendering.
• Validating Parameter Page Items
  Validate task parameter page items before saving their values back to the task.
• Saving Page Items to Task Parameters
  Save validated page item values back to updatable task parameters.
• Working with a Business Entity Row
  Show or update the related business row from a task-specific details page.

Choose the task event that should trigger an email, push notification, or code action.

When creating an action, choose an event from the table below that determines when your action triggers. Then, decide whether you want to send an email, send a push notification, or execute code. If the action should trigger only in certain conditions, configure an appropriate server-side condition.

When using the Execute Code option, you can write in-line PL/SQL as part of the action definition. However, for easiest maintenance it's best to organize your code into a PL/SQL package procedure or function, then invoke that packaged program unit from the action's PL/SQL block.

Reference task source columns and built-in substitutions from task actions.

When configuring an action, you can reference any of the Actions Source columns by name using either :NAME in PL/SQL code or &NAME. in email or push notification substitutions. You can also reference any of the built-in substitution values below:

In addition, an Update Parameter action can use the HAS_TASK_PARAM_CHANGED, GET_TASK_PARAMETER_VALUE, and GET_TASK_PARAMETER_OLD_VALUE functions in the APEX_HUMAN_TASK package to get more information on which parameters have changed, what any parameter's current value is, and what a parameter's previous value was.

Email potential owners when a new task requires their attention.

Some users check their task "inbox" frequently, while others may appreciate getting notified about a new task that requires their attention. You can configure a task action to email the potential owners of a newly created task.

• Determining Assignees Generically
  Use a package function to determine potential owners for a task at runtime.
• Computing Subject and Action Values
  Compute task subject text and action values in the Actions Source query.
• Formatting Email with Templates
  Format task notification emails with templates and placeholder values.
• Sending Email on Task Creation
  Send an email on task creation using Actions Source values and template placeholders.
• Studying Generic Assignee Code
  Study package functions that return task owners and their email addresses.

A common requirement is to react to the outcome of an approval task by updating one or more columns in associated business entity row. You do this with an action on the Complete event.

• Updating Entity Columns on Complete
  Update the related business entity row when an approval task completes.
• Updating Request Columns on Complete
  Update request and business entity rows when an approval task completes.

Review completed task details from the list of work items you requested.

In her Requested Tasks page, Lucy notices the Completed badge in the list, and she clicks on the Subject link to open the Task Details page to review Leo's work.

Lucy reviews the details of the work item and sees the two random numbers Leo entered. Stakeholders can continue adding comments to the task if needed, but otherwise its details are read-only once completed.

Add page items to give users a better experience when entering task parameter values.

The Details region in the generated Task Details page offers a basic parameter editing experience. However, your end users might appreciate entering data using page items. As shown below, you can right-click on a region like Details, Developer Information, Comments, and History and choose Comment Out to temporarily ignore those regions when the page is run. As shown below, commented out elements display with a line through their label in the tree. After adding page items P6_VALUE1 and P6_VALUE2 you can set their Type to Number Field, enable Value Required, and set their Session State > Storage to Per Request (Memory Only).

Trying out the modified page, you can see that your end user immediately gets a better data entry experience. Any attempts to submit the page with non-numeric values or without filling in a required value gives the normal APEX field validation errors shown below. The figure shows the customized Task Details page for the Provide Two Random Numbers task definition. If Leo submits a Value1 by typing in words, and leaves Value 2 blank, he sees the error notification on clicking the (Complete) button. It displays "2 errors have occurred" and shows the two validation errors "Value1 must be a valid number", and "Value2 must have some value".

Make task page items editable only for the assigned owner while the task is active.

To make the page items editable only for the assignee, and only while the status of the task is Assigned, you can use the Read Only settings on P6_VALUE1 and P6_VALUE2 as shown below. The fields are be read-only if no row is returned by the query below. It returns a row when the task exists, the current user is the actual owner, and the status is ASSIGNED. Otherwise, no row is returned, and the fields render read-only.

select task_id
from apex_tasks
where task_id      = :P6_TASK_ID
  and actual_owner = :APP_USER
  and state_code   = 'ASSIGNED'

Load updatable task parameter values into page items before rendering.

Using an appropriate computation in the Pre-Rendering section of the page, as shown below, you can load the value of P6_VALUE1 from the corresponding updatable task parameter using its Static ID P_VALUE1.

apex_human_task.get_task_parameter_value(:P6_TASK_ID,'P_VALUE1')

You can add a similar computation to load the value of parameter P_VALUE2 into P6_VALUE2. The figure shows the P6_VALUE1 computation selected in the component tree and highlights its PL/SQL expression described above that retrieves the value of an updateable task parameter from the current task instance.

Validate task parameter page items before saving their values back to the task.

You can use other page features like validations to perform further data checks before letting the parameter values be saved back to the task. For example, imagine a rule that the two numbers provided must be distinct. You can enforce this by adding a Values Need to Be Different validation as shown below. It configures an appropriate error message and uses the following PL/SQL expression using the NV function to reference the value of a page item as a number.

nv('P6_VALUE1') != nv('P6_VALUE2')

To ensure the validation is performed only when the user clicks the SAVE or COMPLETE buttons to submit the page, you can add a Server-side Condition that the request is contained in the /SAVE/COMPLETE/ value. You can use any value separator other than slash as well.

As shown below, now any user who tries to submit the same value for both page items sees an error "The two values need to be different" and must try again.

Save validated page item values back to updatable task parameters.

apex_human_task.set_task_parameter_values(
  :P6_TASK_ID,
  apex_human_task.t_task_parameters(
    1 => apex_human_task.t_task_parameter('P_VALUE1', :P6_VALUE1),
    2 => apex_human_task.t_task_parameter('P_VALUE2', :P6_VALUE2)));

Show or update the related business row from a task-specific details page.

If your task is tied to a business entity row and you use a Details Primary Key, you may need to view data from that row in your Task Details page to provide additional context. You also might want the actual owner to store additional data right away into columns in that row. Add a Form region to your Task Details page to display and edit the appropriate columns from this related data row. If you do, adopt one of the following approaches.

select detail_pk
  from apex_tasks
 where task_id = :P6_TASK_ID

With the second approach, the P6_TASK_ID value is already set by the Task Details Page URL.

In either case, the Form - Initialization page process in the Pre-Rendering section of your page loads all of the Form region's columns using that primary key value. Enable the Query Only switch on any columns that should be read-only in the page. If you let the assignee edit any of the columns to acquire additional data, then ensure you have a Form - Automatic DML page process related to the form region in your page's Processing tab. It should have a server-side condition to trigger on for SAVE and COMPLETE buttons. It automatically saves the changes back to the business entity row when the user submits the page. For an approval task, the buttons are named APPROVE and REJECT instead of COMPLETE.

Use a package function to determine potential owners for a task at runtime.

The Purchase Office Supplies task definition in this example configures a static username DAVID as the business administrator, and uses an Expression to call an OWNERS_FOR_TASK function in the package YOUR_APP_PKG. This dynamically determines the list of potential owners. It returns a comma-separated list of case-sensitive usernames. The function accepts the task ID and primary key of the business entity related to the task instance as parameters. The Purchase Office Supplies task definition does not use an associated business entity row, nor does it accept a Details Primary Key at creation time. However, this function signature illustrates a best practice for providing key information that generic task owner assignment logic typically needs.

your_app_pkg.owners_for_task(:APEX$TASK_ID,:APEX$DETAILS_PK)

package your_app_pkg as
    -------------------------------------------------
    -- Returns comma-separated list of case-sensitive
    -- usernames who are potential owners for task id
    -- passed in
    -------------------------------------------------
    function owners_for_task(
        p_task_id   in number,
        p_detail_pk in varchar2)
        return         varchar2;
    -------------------------------------------------
    -- Returns comma-separated list of email
    -- addresses of potential owners for task id
    -- passed in
    -------------------------------------------------
    function owner_emails_for_task(
        p_task_id   in number,
        p_detail_pk in varchar2)
        return         varchar2;
end your_app_pkg;

Compute task subject text and action values in the Actions Source query.

Please Buy Coffee and Bagels at Zabar's

Please Buy 2GB Credit Card Sized Hard Drive

The task definition Subject field does not allow HTML Directives for conditional substitution, but it is still straightforward to get a computed task subject. The figure shows the Parameters tab of the Task Definition edit page in App Builder showing one required parameter P_ITEMS_TO_BUY and one optional parameter P_STORE.

Your Actions Source Query can retrieve one row containing any number of columns to provide information to reference in the task definition's Subject or actions. Notice below that the query computes a dynamic subject line using a CASE expression to include the optional store information in the title.

select 'Please Buy '
       ||:P_ITEMS_TO_BUY
       ||case
            when :P_STORE is not null
            then ' at '||:P_STORE
         end as computed_subject,
       your_app_pkg.owner_emails_for_task(:APEX$TASK_ID, :APEX$DETAIL_PK)
             as potential_owners_emails
from dual

As shown below, the task definition's Subject references the dynamically computed subject using the &COMPUTED_SUBJECT. notation. The figure shows the Settings tab of the Task Definition edit page in App Builder and highlights how the Subject can use &COMPUTED_SUBJECT. to reference a column from the Actions SQL Query.

Format task notification emails with templates and placeholder values.

When sending email, you can reference an Email Template. This shared component defines the email subject line along with a message header, body, and footer. These sections can all use #NAME# substitutions as placeholders for data you pass in anywhere you use the template to send a mail. Notice how the Notify New Task email template below references a placeholder name #NEW_TASK_SUBJECT# in the Email Subject field.

In the HTML Format section of the email template edit page, you can see this simple example only defines a static HTML body, but this section as well as Header and Footer can also mention additional placeholders if needed.

Send an email on task creation using Actions Source values and template placeholders.

In the Actions section, the Purchase Office Supplies action task definition configures a single action on the Create task lifecycle event. The action type is Send E-Mail.

Notice below how the Create action's To field references the Actions Source Query column POTENTIAL_OWNERS_EMAILS in the Send Email Settings section. It also uses the Notify New Task email template. Clicking on the (Set Placeholder Values) button opens the Placeholders Grid dialog shown below. It lets you configure values for all placeholders in the email template. Here, the NEW_TASK_SUBJECT placeholder uses the task's subject with the &APEX$TASK_SUBJECT. syntax.

Study package functions that return task owners and their email addresses.

The your_app_pkg contains two generic functions for returning the list of potential owners for a task, as well as a list of their emails. The package body code looks like the following. Notice the OWNER_EMAILS_FOR_TASK queries the APEX_TASK_PARTICIPANTS view using the supplied task id to find the potential owner participants, and joins that view with the APEX_WORKSPACE_APEX_USERS view to get the email addresses of the potential owners. It uses the LISTAGG function with a comma as the delimiter to return a single string of distinct email addresses.

package body your_app_pkg as
    -------------------------------------------------
    function owners_for_task(
        p_task_id   in number,
        p_detail_pk in varchar2)
        return         varchar2
    is
       l_ret varchar2(4000);
    begin
        -- Your custom logic here to return comma-separated
        -- list of case-sensitive user names.
        return l_ret;
    end owners_for_task;
    -------------------------------------------------
    function owner_emails_for_task(
        p_task_id                in number,
        p_detail_pk              in varchar2)
        return         varchar2
    is
        l_ret varchar2(4000);
    begin
         select listagg(distinct u.email,',')
           into l_ret
           from apex_task_participants p
           join apex_workspace_apex_users u
             on u.user_name = p.participant
          where p.task_id = p_task_id
            and p.participant_type = 'POTENTIAL_OWNER';
         return l_ret;
    end owner_emails_for_task;
end your_app_pkg;

Update the related business entity row when an approval task completes.

The task definition defines a subject like the following, referencing the value of the Action Source table ENAME column and the parameter name.

Review &ENAME. Salary Change to &P_PROPOSED_SALARY.

-- If approved, update sal using Action Table EMPNO column value
if :APEX$TASK_OUTCOME = 'APPROVED' then
   update emp
      set   sal = :P_PROPOSED_SALARY
    where empno = :EMPNO;
end if;

Update request and business entity rows when an approval task completes.

Consider a Salary Change task definition that uses the following EMP_SAL_CHANGE_REQUEST table as its Actions Source table. A row in this table represents the request to change a particular employee's salary.

create table emp_sal_change_request (
    id            number
                  generated by default on null as identity
                  constraint emp_sal_change_request_id_pk primary key,
    empno         number
                  constraint emp_sal_change_request_empno_fk
                  references emp,
    new_salary    number,
    motivation    varchar2(255 char),
    status        varchar2(8 char)
);

For this task definition, the built-in Details Primary Key parameter suffices, since you can already reference all the columns in the EMP_SAL_CHANGE_REQUEST row it identifies. There is no need to define additional parameters.

select r.id as request_id, r.empno, r.new_salary, e.ename, e.sal
  from emp_sal_change_request r
  join emp e
    on e.empno = r.empno
 where r.id = :APEX$TASK_PK

Notice above that if your task definition subject or actions don't need to reference the current value of particular columns like MOTIVATION and STATUS you can leave those out of the SELECT list.

Your task definition can now use a subject like the following, referencing the value of the Action Source query's ENAME, SAL, and NEW_SALARY columns.

Review &ENAME. Salary Change from &SAL. to &NEW_SALARY.

-- Record the outcome in the salary change request row
update emp_sal_change_request
   set status = :APEX$TASK_OUTCOME
 where id = :REQUEST_ID;
-- If approved, update sal using Action Table query EMPNO column value
if :APEX$TASK_OUTCOME = 'APPROVED' then
   update emp
      set   sal = :NEW_SALARY
    where empno = :EMPNO;
end if;