General Definition of Workflow:

The flow of any work or task, which covers the entire cycle of that task, is called Workflow.
Example: If you want to apply the leave then you have to fill leave application and submit the same
to your manager. Then after reviewing your application your manager may approve/reject your
leave application. This is called workflow of leave.

Oracle Apps Workflow Definition:

Workflow is one of the components of Oracle Apps, which automates the flow of work/task. That
could be purchase order cycle, order management cycle etc.

This Oracle Apps Workflow works on the following 4 Rs.

1. Rules
2. Roles
3. Routing
4. Real time

I will discuss each R

1. Rules: This is related to business logic. This rule always refers to the requirements of the
business. It tells what to implement by using workflow. In Oracle Apps these rules are
implemented by Procedures
2. Roles: Role means who are mainly involved in the activity. For example in the above leave
workflow, Leave initiator, Manager are called roles in leave workflow. In oracle Apps
workflow, Responsibilities playing as roles.
3. Routing: Routing represents the actual flow of workflow. It tells us how the workflow is
going. In workflow diagram, the transition lines represent the routing of the workflow.
4. Real Time: This R represents the Real time concept in the workflow. By using workflow we
can control the time also. For example if the leave approval is pending with Manager for 3
days then automatically this workflow escalates to next approval authority. Time out
feature of Workflow implements this concept.

Now I will discuss the different Components of Workflow:

1. Workflow Builder: This is the main component of workflow. By using this component,
developers will develop/customize the workflow. You can create new workflow from scratch
or you can customize already existing seeded workflow.
2. Workflow Engine: This component mainly controls the entire workflow activities in Oracle
Apps.
3. Workflow Definition Loader: This component translates the graphic components to text
format while saving this workflow in disk as a file and it gets saved with .wft extension
(Workflow Text). This loader also translates the .wft format to graphics format while
opening the file in workflow builder.
4. Workflow Notification Engine: This component is responsible to send the notifications to the
roles defined in workflow. For example when employee submits the leave request,
workflow notification engine sends the leave information as notification to Manager.
 5. Workflow Status Monitor: By using this component, we can see the status of worklow
related to current transaction.

Overview:

This article will illustrate how to create or define workflow attributes, notifications, messages, roles
or users, functions, processes and last but not the least, how to launch a workflow from PL/SQL.
The workflow concepts are better explained using an example.

Business Requirement:

When an item is created in inventory, workflow needs to be launched and it should collect the
details of the item created and sends a notification to group of users along with the details and link
to master item form.

Oracle APPS : Workflow Tables

Oracle Applications - Workflow Tables (Quick Review)

Important WF Tables:

wf_user_role_assignments
wf_user_roles
wf_roles
wf_items
wf_item_attributes
wf_item_attribute_values
wf_item_attributes_tl
wf_activities
wf_activities_tl
wf_activity_attributes
wf_activity_attributes_tl
wf_activity_transitions
wf_deferred--wf_control

WF_NOTIFICATION_ATTRIBUTES
WF_MESSAGES
WF_MESSAGES_TL
WF_MESSAGE_ATTRIBUTES
WF_MESSAGE_ATTRIBUTES_TL
WF_ETS
WF_PROCESS_ACTIVITIES

WF_ACTIVITIES table stores the definition of an activity. Activities can be Processes,

notifications, functions or folders. A process activity is a modelled workflow process,
which can be included as an activity in other processes to represent a subprocess. A
notification activity sends a message to a performer. A functions activity performs an
automated function that is written as a PL/SQL stored procedure. A folder activity is not
part of a process, it provides a means of grouping activities.

WF_ITEMS is the runtime table for workflow processes. Each row defines one work item
within the system.

WF_ITEM_ATTRIBUTES table stores definitions of attributes associated with a process.

Each row includes the sequence in which the attribute is used as well as the format of the
attribute data.

WF_NOTIFICATIONS holds the runtime information about a specific

instance of a sent message. A new row is created in the table each time a message is sent.

Process flow: When an item is created it will create/insert a record in MTL_SYSTEM_ITEMS_B so

create a database trigger on the table and launch workflow from that trigger. All you need to do is
create the workflow, create the trigger, pl/sql package, roles and finally create an item in inventory.

Open WFSTD and save as new workflow

Create Attributes
Create Functions
Create Notification
Create Messages
Create Roles
Create database trigger
Create PL/SQL Package

1)
Open WFSTD and save as new workflow:
Navigation: File >> Open
Click Browse then navigate to Workflow installation directory

Navigation: Workflow Installation Directory\ WF\DATA\US\WFSTD

Now Click File >Save as, Enter ErpSchools Demo and click OK

Right click on WFSTD and select New Item type

Enter the fields as below

Internal Name: ERP_DEMO

Display Name: ErpSchools Demo

Description: ErpSchools Demo

Now you will see ErpSchools Demo icon in the Navigator
Expand the node to see attributes, processes, notifications, functions, Events, Messages and
lookup types.
Double click on Process to open up the properties window as shown below
Enter the fields

Internal Name: ERPSCHOOLS_PROCESS

Display Name: ErpSchools Process

Description: ErpSchools Process

Double click ErpSchools Process Icon
2) Create Workflow Attributes:

Navigation:
Window menu > Navigator

Right click on Attributes and click New Attribute

Enter the fields

Internal Name: ERP_ITEM_NUMBER

Display Name: Item Number

Description: Item Number

Type: Text

Default Value: Value Not Assigned

Click Apply and then OK

Create one more attribute

Right click on Attributes and click New Attribute

Enter the attribute fields

Internal Name: ERP_SEND_ITEM_FORM_LINK

Display Name: Send Item Form Link

Description: Send Item Form Link

Type: Form

Value: INVIDITM
Click Apply and then OK
3) Create Workflow Function:

Open Erpschools process in That Right click and then click on New Function

Properties window will open as shown below

Change/Enter the fields as below

Change Item Type to Standard from ErpSchools Demo

Select Internal Name as Start

Remaining fields will be populated automatically

Click Apply then OK
Again Right click on white space and click New Function
Change the properties as below

Item Type: Standard

Internal Name: END

Click Apply and then OK
Right click on white space and then click New Function
Enter the fields

Internal Name: ERP_GET_ITEM_DETAILS

Display Name: Get New Inventory Item Details

Description: Get New Inventory Item Details

Function Name: erpschools_demo_pkg.get_item_details

Click Apply and then OK
4) Create Workflow Notifications:

Right click on white space and then click New Notification

Enter fields

Internal Name: ERP_SEND_ITEM_DET

Display Name: Send Item Detials

Description: Send Item Detials

Message: Sned Item Details Message

Click Apply and then OK

5) Create Workflow Messages:

Right click on Message and click New

Properties window will pop up as show below
Enter the fields

Internal Name: ERP_SEND_ITEM_DET_MSG

Display Name: Send Item Details Message

Description: Send Item Details Message

Go to Body Tab and enter as shown below
Click Apply and then OK

Navigation: Window Menu > Navigator

Select Item Form Link Attribute

Drag and drop both attributes to Send Item Details Message

6)
Create Roles:

Adhoc roles can be created through PL/SQL from database or they can be created from
Applications using User Management Responsibility. If you use PL/SQL to create roles make sure
you give all user names and role names in UPPER case to avoid some problems

Script to Create a Adhoc Role

Script to Add user to existing Adhoc Role

Script to Remove user from existing Adhoc Role

Using Adhoc roles in workflow notifications

Adhoc Roles Tables

Script to Create a Adhoc Role

DECLARE

lv_role varchar2(100) := ERPSCHOOLS_DEMO_ROLE;

lv_role_desc varchar2(100) := ERPSCHOOLS_DEMO_ROLE;

BEGIN

wf_directory.CreateAdHocRole(lv_role,

lv_role_desc,

NULL,

NULL,

Role Demo for erpschool users,

MAILHTML,

NAME1 NAME2, USER NAME SHOULD BE IN CAPS

NULL,

NULL,

ACTIVE,

NULL);

dbms_output.put_line(Created Role || ||lv_role);

End;

Script to Add user to already existing Adhoc Role

DECLARE

v_role_name varchar2(100);

v_user_name varchar2(100);

BEGIN

v_role_name := ERPSCHOOLS_DEMO_ROLE;

v_user_name := NAME3;
WF_DIRECTORY.AddUsersToAdHocRole(v_role_name, v_user_name);

USER NAMES SHOULD BE in CAPS

END;

Script to Remove user from existing Adhoc Role

DECLARE

v_role_name varchar2(100);

v_user_name varchar2(100);

BEGIN

v_role_name := ERPSCHOOLS_DEMO_ROLE;

v_user_name := NAME3;

WF_DIRECTORY.RemoveUsersFromAdHocRole(v_role_name, v_user_name); USER NAMES in

CAPS

END;

Using Adhoc roles in workflow notifications:

Navigation: File > Load Roles from Database

Select roles you want to use and then click OK.

Open the notification properties and then navigate to node tab, select performer as the role you
just created and loaded from database.

Tables:

WF_ROLES

WF_USER_ROLES

WF_LOCAL_ROLES

WF_USER_ROLE_ASSIGNMENTS

7) Launching workflow from PL/SQL:

First create a database trigger as below to call a PL/SQL procedure from which you kick off the
workflow.

Create Database Trigger

CREATE OR REPLACE TRIGGER ERP_SCHOOLS_DEMO_TRIGGER AFTER INSERT ON
INV.MTL_SYSTEM_ITEMS_B REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW

DECLARE

lv_id NUMBER := :NEW.inventory_item_id;

lv_item_segment1 VARCHAR2(100) := :NEW.segment1;

lv_itemtype VARCHAR2(80) := :NEW.item_type;

lv_user_id NUMBER := -1;

lv_itemkey VARCHAR2(10);

lv_orgid NUMBER :=2;

error_msg VARCHAR2(2000);

error_code NUMBER;

BEGIN

lv_user_id := fnd_global.user_id;

lv_orgid := fnd_global.org_id;

lv_itemkey := 1132; This should be unique value

ERP_DEMO.LAUNCH_WORKFLOW(ERP_DEMO

,lv_itemkey

,ERPSCHOOLS_PROCESS process name

,lv_id

,lv_orgid

,lv_item_segment1

);

EXCEPTION

WHEN OTHERS THEN

error_code := SQLCODE;

error_msg := SQLERRM(SQLCODE);
RAISE_APPLICATION_ERROR(-20150,error_msg);

END;

Create PL/SQL Package to kickoff workflow

CREATE OR REPLACE PACKAGE APPS.ERP_DEMO IS

PROCEDURE LAUNCH_WORKFLOW

itemtype IN VARCHAR2,

itemkey IN VARCHAR2,

process IN VARCHAR2,

item_id IN NUMBER,

org_id IN NUMBER,

item_segment1 IN VARCHAR2

);

END ERP_DEMO;

CREATE OR REPLACE PACKAGE BODY APPS.ERP_DEMO IS

PROCEDURE LAUNCH_WORKFLOW(

itemtype IN VARCHAR2,

itemkey IN VARCHAR2,

process IN VARCHAR2,

item_id IN NUMBER,

org_id IN NUMBER,

item_segment1 IN VARCHAR2

)
IS

v_master_form_link varchar2(5000);

v_item_number varchar2(100);

error_code varchar2(100);

error_msg varchar2(5000);

BEGIN

v_add_item_id := ITEM_ID= || item_id || ;

v_item_number := item_segment1;

v_master_form_link := v_master_form_link || v_add_item_id;

WF_ENGINE.Threshold := -1;

WF_ENGINE.CREATEPROCESS(itemtype, itemkey, process);

Get the value of attribute assigned in workflow

v_master_form_link := wf_engine.getitemattrtext(

itemtype => itemtype

,itemkey => itemkey

,aname => ERP_SEND_ITEM_FORM_LINK);

- assign values to variables so that you can usethe attributes

v_master_form_link varchar2(5000) := v_master_form_link||:#RESP_KEY=INVENTORY

#APP_SHORT_NAME=INV ORG_MODE=Y ;

v_master_form_link := v_master_form_link || v_add_item_id;

set the attribute values in workflow so that you can use them in notifications

WF_ENGINE.SetItemAttrText(itemtype, itemkey, MASTERFORM, v_master_form_link);

WF_ENGINE.SetItemAttrText(itemtype, itemkey, ERP_ITEM_NUMBER, item_segment1);

start the workflow process

WF_ENGINE.STARTPROCESS(itemtype, itemkey);

EXCEPTION WHEN OTHERS THEN

error_code := SQLCODE;

error_msg := SQLERRM(SQLCODE);

add dbms or fnd_output messages as required

END LAUNCH_WORKFLOW;

This procedure will just put the item number into workflow attribute ERP_ITEM_NUMBER

PROCEDURE GET_ITEM_DETAILS(

itemtype IN VARCHAR2,

itemkey IN VARCHAR2,

actid IN NUMBER,

funcmode IN VARCHAR2,

resultout OUT NOCOPY VARCHAR2

IS

v_GET_ITEM_NUMBER VARCHAR2(1000);

BEGIN

SELECT SEGMENT1 INTO V_GET_ITEM_NUMBER FROM MTL_SYSTEM_ITEMS_B WHERE

ROWNUM =1;

WF_ENGINE.SetItemAttrText(itemtype, itemkey,
ERP_ITEM_NUMBER,v_GET_ITEM_NUMBER );

you can use the get function as below.

v_GET_ITEM_NUMBER := wf_engine.getitemattrtext(

itemtype => itemtype

,itemkey => itemkey

,aname => X_ATTRIBUTE);

resultout:=COMPLETE:||Y';

exception when others then

dbms_output.put_line(Entered Exception);
fnd_file.put_line(fnd_file.log,Entered Exception);

END GET_ITEM_DETAILS;

END ERP_DEMO;