Oracle Workflow Tutorial PDF

Oracle Workflow Tutorial Workflow
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.
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
RAJU CHINTHAPATLA
1) Open WFSTD and save as new workflow:
Navigation: File >> Open
RAJU CHINTHAPATLA
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
RAJU CHINTHAPATLA
Enter the fields as below

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

Internal Name: ERPSCHOOLS_PROCESS
Display Name: ErpSchools Process
Description: ErpSchools Process
RAJU CHINTHAPATLA
RAJU CHINTHAPATLA
Double click ErpSchools Process Icon
RAJU CHINTHAPATLA
RAJU CHINTHAPATLA
2) Create Workflow Attributes:

Navigation:
Window menu > Navigator
Right click on Attributes and click New Attribute
RAJU CHINTHAPATLA
Enter the fields

Internal Name: ERP_ITEM_NUMBER
Display Name: Item Number
Description: Item Number
Type: Text
Default Value: Value Not Assigned
RAJU CHINTHAPATLA
Click Apply and then OK

Create one more attribute
Right click on Attributes and click New Attribute
RAJU CHINTHAPATLA
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
RAJU CHINTHAPATLA
RAJU CHINTHAPATLA
3) Create Workflow Function:

Right click and then click on New Function
Properties window will open as shown below
RAJU CHINTHAPATLA
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
RAJU CHINTHAPATLA
Click Apply then OK
RAJU CHINTHAPATLA
Again Right click on white space and click New Function
RAJU CHINTHAPATLA
Change the properties as below

Item Type: Standard
Internal Name: END
RAJU CHINTHAPATLA
RAJU CHINTHAPATLA
Right click on white space and then click New Function
RAJU CHINTHAPATLA
Enter the fields

Internal Name: ERP_GET_DETAILS
Display Name: Get New Inventory Item Details
Description: Get New Inventory Item Details
Function Name: erpschools_demo_pkg.get_item_details
RAJU CHINTHAPATLA
RAJU CHINTHAPATLA
RAJU CHINTHAPATLA
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
RAJU CHINTHAPATLA
RAJU CHINTHAPATLA
5) Create Workflow Messages:

Right click on Message and click New
Properties window will pop up as show below
RAJU CHINTHAPATLA
Enter the fields

Internal Name: ERP_SEND_ITEM_DET_MSG
Display Name: Send Item Details Message
Description: Send Item Details Message
RAJU CHINTHAPATLA
Go to Body Tab and enter as shown below
RAJU CHINTHAPATLA

Navigation: Window Menu > Navigator
Select Item Form Link Attribute
RAJU CHINTHAPATLA
Drag and drop both attributes to “Send Item Details Message”
RAJU CHINTHAPATLA
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
RAJU CHINTHAPATLA
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;
RAJU CHINTHAPATLA
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
RAJU CHINTHAPATLA
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;
/
RAJU CHINTHAPATLA
 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(
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;
RAJU CHINTHAPATLA
-- 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(
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;
/
RAJU CHINTHAPATLA

Oracle Workflow Tutorial PDF

Uploaded by

Copyright:

Available Formats

Oracle Workflow Tutorial PDF

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Oracle Workflow Tutorial PDF

Uploaded by

Copyright:

Available Formats

Oracle Workflow Tutorial Workflow

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

1) Open WFSTD and save as new workflow:

Navigation: File >> Open

Click Browse then navigate to Workflow installation directory

Enter the fields as below

Now you will see ErpSchools Demo icon in the Navigator

Double click on Process to open up the properties window as shown below

Enter the fields

Double click ErpSchools Process Icon

2) Create Workflow Attributes:

Right click on Attributes and click New Attribute

Enter the fields

Default Value: Value Not Assigned

Click Apply and then OK

Enter the attribute fields

Click Apply and then OK

3) Create Workflow Function:

Properties window will open as shown below

Change/Enter the fields as below

Click Apply then OK

Again Right click on white space and click New Function

Change the properties as below

Click Apply and then OK

Right click on white space and then click New Function

Enter the fields

Click Apply and then OK

4) Create Workflow Notifications:

Click Apply and then OK

5) Create Workflow Messages:

Properties window will pop up as show below

Enter the fields

Go to Body Tab and enter as shown below

Click Apply and then OK

Drag and drop both attributes to “Send Item Details Message”

Script to Create a Adhoc Role

Script to Add user to already existing Adhoc Role

Script to Remove user from existing Adhoc Role

Using Adhoc roles in workflow notifications:

7) Launching workflow from PL/SQL:

 Create Database Trigger

 Create PL/SQL Package to kickoff workflow

CREATE OR REPLACE PACKAGE BODY APPS.ERP_DEMO IS

-- assign values to variables so that you can usethe attributes

-- start the workflow process

You might also like