Oracle Workflow Technical Details

Workflow:  Oracle workflow is a graphical tool that allows you to create, track and modify business process embedded in the oracle database server. It can monitor the oracle business workflow activity status.

Benefits:

1.      Create clear business process requirements.

2.     Automate the business routing.

3.     Monitor the process.

4.     Allow user to define own business scenario.

5.     Readily change the business process definition in case of a change in business process.

WFSTD: (Standard module for workflow)

Responsibility: Workflow administrator web applications

Components of Workflow: Attributes, process, notifications, functions, events, messages and lookup types.

Pre-requisites: A workflow should have unique Item name and Item key associated to run a workflow.

Note: For initializing a workflow there are certain API’s should be set in oracle pl/sql package.

1.      API used to create process of workflow: Wf_engine.createprocess(v_itemtype, v_itemkey, v_processname)

2.      API used to set item attribute text :wf_engine.SetItemAttrText(v_itemtype, v_itemkey, v_Aname, v_Avalue)

3.      API used to set Item user key: wf_engine.SetItemuserkey(v_itemtype, V_itemkey, v_UserKey)

4.      API used to set Item Owner: wf_engine.SetItemOwner(V_itemtype, V_ItemKey, ‘V_User’)

5.      API used to start Process: wf_engine.Startprocess(v_itemtype, v_itemkey)

6.      API used for printing engine completed message: wf_engine_eng_completed and wf_engine.eng_null

7.      API used to assign the workflow errors into error stack or to locate the source or error:wf_core.context

8.      API used to get the Item Attribute text or date: wf_engine.GetitemAttrtext or wf_engine.GetItemAttrdate

Wf_core.context: Oracle workflow provides different context information that corresponds to the current notification being acted upon to the Post-Notification-Function

It includes the context information in the error stack to help to locate the source of an error.

List of context variables in workflow:

1.      Wf_engine.context_nid

2.      Wf_engine.context_new_role

3.      Wf_engine.context_user_comments

4.      Wf_engine.context_user

5.      Wf_engine.context_recepient_role

6.      Wf_engine.context_original_recepient

7.      Wf_engine.context_user_variable

Note: Post-Notification-Function (PNF) should be a pl/sql procedure written to the same PI standard required for function activity.

Some more information of workflow

1)      Any procedure that is attached with an activity (notification/ function activity) must have following input parameters ( Itemtype in varchar2, Itemkey in varchar2, Actid in number, funcmode in varchar2, result_out in out varchar2)

2)      Any procedure that is attached with an activity (notification/function activity) must have an error         handling section, in case of exception occurs. Wf_core.context(<package name>,<procedure name>, item type, item key, actid, funcmode)

API to get the value of document id when a attachment is involved in our notification

Wf_engine.getItemAttrDocument(v_itemtype, v_itemkey, v_Aname, v_documetId=>’PLSQLBLOB:<procedure_name>’||v_lob_id)

API used to create roles from backend

Wf_directory.CreatAdhocRoles(v_rolename, v_roleDesc, null, null, Desc, ‘MAILHTML’, ‘User1 User2’,null. Null “active, null)

API to Add user in roles

Wf_directory.AddUserToAdhocRoles(V_rolename, Vusername)

Statuses of workflow activity

1.      Active

2.      Complete

3.      Waiting

4.      Notified

5.      Deferred

6.      Error

7.      Suspended

Different kind of funcMode involve in workflow

1.      Run

2.      Cancel

3.      Respond

4.      Forward

5.      Transfer

6.      Timeout

Question: When starting an oracle workflow process, how do you ensure that it happens in a background mode?

Ans: If initiating a process using start process do the following:

Wf_engine.threshold:=-1

Wf_engine.createprocess(l_itemtype, l_itemkey,’Process_Name’)

Wf_enfine.startprocess(I_itemtype, l_itemkey)

Or

When initiating the workflow process through an event subscribtion, set the “Execution Condtion Phase” to be equal to or above 100. It will lead the process to run in background process.

Different Tables involved in oracle workflow

1.      Wf_item_types

2.      Wf_item_types_tl

3.      Wf_item_type_vl

4.      Wf_items

5.      Wf_item_activity_statusses

6.      Wf_notifications

7.      Wf_roles

8.      Wf_user_roles

9.      Wf_local_roles

10.  Wf_user_role_asignments

11.  Wf_item_attributes

12.  Wf_item_attributes_tl

13.  Wf_item_attribute_values

14.  Wf_activities

15.  Wf_activities_tl

16.  Wf_activity_attributes

17.  Wf_activity_attributes_tl

18.  Wf_activity_transitions

19.  Wf_deferred

20.  Wf_process_activities