Workflows in Oracle

In Oracle environments, especially in applications like Oracle E-Business Suite (EBS) or Oracle Fusion
Applications, workflows are automated representations of business processes, designed to
streamline operations, manage approvals, and ensure data flows efficiently between people and
systems.

1. Core concept and purpose

 Workflows automate the flow of information, documents, and tasks within and across an
organization.

 They ensure tasks are executed in a predefined sequence, often involving different
participants and systems.

 The primary purpose is to improve efficiency, reduce manual effort, and ensure adherence to
business rules.

2. Key components and how they function

 Workflow Builder: This is a graphical tool used to define and modify workflow processes
using a drag-and-drop interface.

 Workflow Engine: Embedded within the Oracle Database, the Workflow Engine monitors
workflow states, coordinates activity routing, and executes automated steps and business
rules defined in the workflow process.

 Business Event System: Utilizes Oracle Advanced Queuing (AQ) to communicate business
events between systems and launch workflows or propagate messages based on these
events.

 Notification System: Sends notifications via email or web browser to users requiring action or
information, allowing them to participate in the workflow by approving, rejecting, or
providing input.

 Activities: These are the individual steps or tasks within a workflow, which can be automated
functions, human tasks like approvals, or notifications.

 Messages: Templates used by notification activities to define the content of the notifications
sent to users.

 Attributes: Store temporary values or data used in processes, activities, or notifications

within a workflow.

3. Workflow processes and design

 Workflow processes are defined graphically using Workflow Builder, where activities are
linked with transitions to create the desired flow.

 Processes can incorporate complex logic, including branching, looping, parallel flows, and
subflows.

 Conditions, based on attributes or other factors, can dictate which path the workflow
follows.
  Custom PL/SQL or Java code can be integrated as activities to implement specific business
logic.

4. Integration and applications

 Workflows are essential for automating and integrating various modules within Oracle
Applications like E-Business Suite, enabling seamless data flow and approvals across different
functions like finance, procurement, and human resources.

 For example, a purchase requisition submitted in one module can trigger a workflow that
routes it for approval through various levels before being processed for payment in another
module.

 Workflows can also integrate Oracle applications with external systems, facilitating e-
business transactions and streamlining communication with trading partners.

In summary, Oracle workflows provide a powerful mechanism to model, automate, and monitor
business processes within and beyond the enterprise, ensuring efficiency, consistency, and timely
execution of tasks through a combination of graphical design tools, a robust engine, and flexible
notification and integration capabilities.

Example how to create a workflow.

Example: creating a simple approval workflow in Oracle

This example focuses on creating a basic purchase requisition approval workflow using Oracle
Workflow Builder. The steps provide a general overview of the process and assume you have Oracle
Workflow Builder installed and configured.

1. Plan the workflow

 Process: Purchase Requisition Approval

 Trigger: A new purchase requisition is submitted.

 Steps:

1. Start

2. Check requisition amount

3. If amount < $500, auto-approve

4. If amount >= $500, send approval notification to manager

5. Manager approves/rejects

6. Record approval/rejection

7. End

2. Open Oracle Workflow Builder and create a new item type

 Start Oracle Workflow Builder: Double-click the Oracle Workflow Builder icon or use the
command line.

 Create a new Item Type: Choose File > New to create a new workspace, then choose New
from the File menu to create a new item type.
  Define Item Type Properties:

o Internal Name: PO_REQ_APPR (Internal name for the workflow, up to 8 characters).

o Display Name: Purchase Requisition Approval (User-friendly name).

o Persistence Type: Temporary (or Permanent depending on your retention needs).

o Days: 0 (If temporary, determines when audit data can be purged after completion).

3. Create item type attributes

 Navigate to Attributes: Expand your new item type, then right-click on Attributes and choose
New Attribute.

 Define Attributes:

o REQ_AMOUNT:

o Type: Number

o Display Name: Requisition Amount

o MANAGER_USERNAME:

o Type: Text

o Display Name: Manager Username

o APPROVAL_STATUS:

o Type: Text

o Display Name: Approval Status

o Lookup Type: You can create a lookup type for Approval Status with values
like "APPROVED" and "REJECTED".

4. Define activities

 Navigate to Processes: Expand your item type, then right-click on Processes and choose New
Process.

 Define Process Properties:

o Internal Name: MAIN_APPROVAL_PROCESS

o Display Name: Main Requisition Approval Process

 Add Activities in the Process Diagram: Drag and drop the following activities from the
Navigator into the Process window and configure them:

o Start Activity: Drag the Start function from the Standard item type.

o Function Activity (Check Amount):

o Type: Function

o Internal Name: CHECK_AMOUNT

 o Display Name: Check Requisition Amount

o Result Type: You can create a lookup type for AMOUNT_CHECK with values
like LOW_VALUE and HIGH_VALUE.

o PL/SQL Stored Procedure: Call a PL/SQL procedure that checks

the REQ_AMOUNT attribute and returns LOW_VALUE or HIGH_VALUE.

o Notification Activity (Approve Requisition):

o Type: Notification

o Internal Name: APPROVE_REQ_NOTIFICATION

o Display Name: Approve Requisition

o Message: Create a new message with details like Requisition Amount:

&REQ_AMOUNT.

o Performer: Set the performer to &MANAGER_USERNAME to route the

notification to the manager.

o Result Type: Use a Lookup Type for Approval with "APPROVED" and
"REJECTED" values.

o Function Activity (Auto Approve):

o Type: Function

o Internal Name: AUTO_APPROVE_REQ

o Display Name: Auto Approve Requisition

o PL/SQL Stored Procedure: Call a PL/SQL procedure to

update APPROVAL_STATUS to 'APPROVED'.

o Function Activity (Record Approval):

o Type: Function

o Internal Name: RECORD_APPROVAL

o Display Name: Record Requisition Approval

o PL/SQL Stored Procedure: Call a PL/SQL procedure to update the requisition

status in your application table.

o Function Activity (Record Rejection):

o Type: Function

o Internal Name: RECORD_REJECTION

o Display Name: Record Requisition Rejection

o PL/SQL Stored Procedure: Call a PL/SQL procedure to update the requisition

status in your application table.
 o End Activity: Drag the End function from the Standard item type.

5. Draw transitions

 Connect the activities in the process diagram using transitions (arrows) based on your
approval logic.

o Connect Start to Check Requisition Amount.

o From Check Requisition Amount:

o If LOW_VALUE result, connect to Auto Approve Requisition.

o If HIGH_VALUE result, connect to Approve Requisition Notification.

o From Approve Requisition Notification:

o If APPROVED result, connect to Record Requisition Approval.

o If REJECTED result, connect to Record Requisition Rejection.

o Connect Auto Approve Requisition to Record Requisition Approval.

o Connect Record Requisition Approval to End.

o Connect Record Requisition Rejection to End.

6. Save and upload

 Save the Workflow: Choose File > Save As and save the workflow definition to the database
or a local .wft file.

 Test: Initiate the workflow from your application (e.g., when a requisition is submitted) and
test the approval flow, notifications, and updates.

7. PL/SQL procedure example (for check amount)

sql

CREATE OR REPLACE PACKAGE BODY XX_PURCHASE_REQ_WF_PKG AS

-- Procedure for checking requisition amount

PROCEDURE CHECK_REQ_AMOUNT (

itemtype IN VARCHAR2,

itemkey IN VARCHAR2,

actid IN NUMBER,

funcmode IN VARCHAR2,

resultout OUT VARCHAR2

) IS

l_req_amount NUMBER;
 BEGIN

IF (funcmode = 'RUN') THEN

l_req_amount := WF_ENGINE.GetItemAttrNumber(

itemtype => itemtype,

itemkey => itemkey,

aname => 'REQ_AMOUNT'

);

IF l_req_amount < 500 THEN

resultout := 'COMPLETE:LOW_VALUE';

ELSE

resultout := 'COMPLETE:HIGH_VALUE';

END IF;

ELSIF (funcmode = 'CANCEL') THEN

resultout := 'COMPLETE'; -- Handle cancellation if needed

END IF;

EXCEPTION

WHEN OTHERS THEN

WF_CORE.CONTEXT('XX_PURCHASE_REQ_WF_PKG', 'CHECK_REQ_AMOUNT', itemtype, itemkey,

TO_CHAR(actid), funcmode);

RAISE;

END CHECK_REQ_AMOUNT;

-- Other procedures for auto-approve, record approval/rejection etc.

END XX_PURCHASE_REQ_WF_PKG;

Use code with caution.

This example provides a simplified overview of creating an Oracle Workflow for purchase requisition
approval. In a real-world scenario, you would have more detailed processes, multiple approval levels,
security considerations, and robust error handling. Remember to refer to the Oracle Help Center
documentation for comprehensive details and advanced features.