WORKFLOW TABLES

Oracle Workflow:


Business processes today involve getting many types of information to multiple people according to rules that are constantly changing. Oracle Workflow lets you automate and continuously improve business processes, routing information of any type according to business rules you can easily change to people both inside and outside your enterprise. Oracle Workflow Builder lets you create, view, or modify a business process with simple drag and drop operations. Using the Workflow Builder, you can create and modify all workflow objects, including activities, item types, and messages. At any time you can add, remove, or change workflow activities, or set up new prerequisite relationships among activities.

Workflow Engine

The Workflow Engine embedded in the Oracle8 server monitors workflow states and coordinates the routing of activities for a process. The Workflow Engine supports sophisticated workflow rules, including looping, branching, parallel flows, and subflows.


Workflow Processes

Oracle Workflow manages business processes according to rules that you define. The rules, which we call a workflow process definition, include the activities that occur in the process and the relationship between those activities. A workflow process is initiated when an application calls a set of Oracle Workflow Engine APIs.

Setup Flowchart

Access Level

The following range of levels are presumed by Oracle Workflow:

0–9 : Oracle Workflow

10–19 : Oracle Application Object Library

20–99 : Oracle Applications development

100–999 : Customer organization. You can determine how you want this range to be interpreted. For example, 100 can represent headquarters, while 101 can represent a regional office, and so on.

1000 : Public

Each user of Oracle Workflow operates the system at a certain access level according to the range of levels listed above. A ”user of Oracle Workflow” in this case, represents someone who is operating Oracle Workflow Builder, or the Workflow Definitions Loader program, which loads workflow process definitions from a file into a database.

Protection Level

Whenever you create a workflow object in Oracle Workflow Builder, you have the option of protecting the object at a certain level. An object’s protection level controls whether other users can modify the object based on their access levels.

Customization Level

Every workflow object, in addition to having a protection level, also records a customization level equal to your access level when you modify the object and save it to a database or file. For example, if a workflow object is customizable (protection level is 1000), and you customize it at an access level of 100, you now mark the object as having a customization level of 100. The customization level indicates that the object can only be further modified by someone operating at an access level equal to or higher than the customization level. So in this example, you can only customize the object further if your access level is 100 or higher. If you are operating at an access level lower than an object’s customization level, you will see a small lock on that workflow object’s icon, indicating that the object can be used but not modified.

Using the Workflow Definitions Loader

Rather than use the File Save or File Open menu options in Oracle Workflow Builder, you can also run a program called Workflow Definitions Loader to save or load process definitions from a database or flat file. When you upgrade your database, use the Workflow Definitions Loader to preserve and back up your process definitions to a flat file. When the database upgrade is complete, use the Loader program again to upload the definitions back into your database.

The following six tertiary branches appear beneath each item type branch:

1. Attributes—lists the attributes for the current item type. Item type attributes describe features of an item type. For example, if an item type is a purchase order requisition, then an item type attribute can be the requisition amount or the requisition ID.

2. Processes—lists the process activities or workflow process definitions for the current item type.

3. Notifications—lists the notification activities associated with the current item type. A notification activity sends a message to a user or role. The message may prompt for a response or may simply provide information.

4. Functions—lists the function activities associated with the current item type. A function activity represents a PL/SQL stored procedure that the Workflow Engine executes automatically. A function activity can also have activity attributes associated with it.

5. Messages—lists the messages that a notification activity associated with the current item type can send to a user or role. A message can have message attributes associated with it.

6. Lookup Types—lists the lookup types associated with the current item type. A lookup type has one or more values called lookup codes associated with it. A lookup type is a list of values that can be referenced by a message, or by a notification, function, or process as its possible result type.


Defining Workflow Process Components

Workflow Process Components

Depending on the workflow process you wish to create, you need to define all or some of the following types of components to make up the process :

· Item Types

· Lookup Types

· Messages

· Activities

· Attributes

· Roles


* * * * * The recipient of a workflow message is called the Performer.


Activities

An activity is a unit of work that contributes toward the accomplishment of a process. An activity can be a notification, a function, or a process. A notification activity sends a message to a\ workflow user. The message may simply provide the user with information or request the user to take some action. A function activity calls a PL/SQL stored procedure or some external program to perform an automated function. A process activity is a modelled workflow process, which can be included as an activity in another process to represent a sub–process.

Process Activity

A process activity represents a collection of activities in a specific relationship. When a process activity is contained in another process it is called a sub–process.

Activity Cost

Each function activity has a cost associated with it. The cost is a value representing the number of seconds it takes for the Workflow Engine to execute the activity.

Ex : The valid range for cost is 0.00 to 1,000,000.00.

Voting Activity

You can create a voting activity that lets you send a notification to a group of users in a role and tally the responses from those users. The results of the tally determine the activity that the process transitions to next. A voting activity is a notification activity that first sends a notification message to a group of users and then performs a PL/SQL post–notification function to tally the users’ responses (votes).

Roles

Oracle Workflow roles are stored in the database, in the Oracle Workflow directory service. Currently, new workflow roles cannot be created in Oracle Workflow Builder, but Oracle Workflow Builder can display and reference the roles stored in a database.


Functions :

And : Completes when the activities from all converging branches complete. Calls a PL/SQL procedure named WF_STANDARD.ANDJOIN

Or : Completes when the activities from at least one converging branch complete. Calls a PL/SQL procedure named WF_STANDARD.ORJOIN

Comparison Activities: Compare Date, Compare Number, and Compare Text:

All the Comparison activities call a PL/SQL procedure named WF_STANDARD.COMPARE

Wait Activity: The Wait activity pauses the process for the time you specify. This activity calls the PL/SQL procedure named WF_STANDARD.WAIT

Launch Process Activity: The Launch Process activity lets you launch another workflow process from the current process. This activity calls the PL/SQL procedure named WF_STANDARD.LAUNCHPROCESS

Noop Activity: The Noop activity acts as a placeholder activity that performs no action. You can use this activity anywhere you want to place a node without performing an action. You can change the display name of this activity to something meaningful when you include it in a process, so that it reminds you of what you want this activity to do in the future. This activity calls the PL/SQL procedure named WF_STANDARD.NOOP.( start activity end activity )

Notify Activity: The Notify function activity lets you send a notification, where the message being sent is determined dynamically at runtime by a prior function activity. The Notify activity calls a PL/SQL procedure named WF_STANDARD.NOTIFY.

Oracle Workflow Procedures and Functions
Oracle Workflow supplies a list of public PL/SQL procedures and functions that you can use to set up a

Workflow process. They are grouped within the following packages:

· WF_ENGINE:

· WF_CORE:

· WF_PURGE:

· WF_DIRECTORY:

· WF_PREF: WF_MONITOR:

· WF_QUEUE:

· FND_DOCUMENT_MANAGEMENT:

· WF_NOTIFICATIONS:

Workflow Engine APIs

The Workflow Engine APIs can be called by an application program or a workflow function in the runtime phase to communicate with the engine and to change the status of each of the activities. These APIs are defined in a PL/SQL package called WF_ENGINE. Many of these Workflow Engine APIs also have corresponding Java methods that you can call from any Java program to integrate with Oracle Workflow. The following list indicates whether the Workflow Engine APIs are available as PL/SQL functions/procedures, as Java methods, or both.

· CreateProcess: PL/SQL and Java

· SetItemUserKey: PL/SQL

· GetItemUserKey: PL/SQL

· GetActivityLabel: PL/SQL

· SetItemOwner: PL/SQL

· StartProcess: PL/SQL and Java

· LaunchProcess: PL/SQL and Java

· SuspendProcess: PL/SQL and Java

· ResumeProcess: PL/SQL and Java

· AbortProcess: PL/SQL and Java

· CreateForkProcess: PL/SQL

· StartForkProcess: PL/SQL

· Background: PL/SQL

· AddItemAttr: PL/SQL and Java

· SetItemAttribute: PL/SQL and Java

· SetItemAttrDocument: PL/SQL and Java

· getItemTypes: Java

· GetItemAttribute: PL/SQL

· GetItemAttrDocument: PL/SQL

· getItemAttributes: Java

· GetItemAttrInfo: PL/SQL

· GetActivityAttrInfo: PL/SQL

· GetActivityAttribute: PL/SQL

· BeginActivity: PL/SQL

· CompleteActivity: PL/SQL

· AssignActivity: PL/SQL

· HandleError: PL/SQL

· SetItemParent: PL/SQL

· ItemStatus: PL/SQL and Java

· getProcessStatus: Java


Workflow Core APIs

PL/SQL procedures called by function activities can use a set of core Oracle Workflow APIs to raise and catch errors. The following APIs can be called by an application program or workflow function in the runtime phase to handle error processing. These APIs are stored in the PL/SQL package called WF_CORE.

· CLEAR

· GET_ERROR

· TOKEN

· RAISE

· CONTEXT

· TRANSLATE


Workflow Purge APIs

The following APIs can be called by an application program or workflow function in the runtime phase to purge obsolete runtime data. These APIs are defined in the PL/SQL package called WF_PURGE. WF_PURGE can be used to purge obsolete runtime data for completed items and processes, and to purge information for obsolete activity versions that are no longer in use. You may want to periodically purge this obsolete data from your system to increase performance.

WF_PURGE.ITEMS – purge all runtime data associated with completed items, their processes, and notifications sent by them

WF_PURGE.ACTIVITIES – purge obsolete versions of activities that are no longer in use by any item.

WF_PURGE.TOTAL – purge both item data and activity data. The other auxiliary routines purge only certain tables or classes of data, and can be used in circumstances where a full purge is not desired.

The complete list of purge APIs are as follows:

· Items:

· Activities:

· Notifications:

· Total:

· TotalPERM:

· AdHocDirectory:


Workflow Directory Service APIs

The following APIs can be called by an application program or a workflow function in the runtime phase to retrieve information about existing users and roles, as well as create and manage new ad hoc users

and roles in the directory service. These APIs are defined in a PL/SQL package called WF_DIRECTORY.

· . GetRoleUsers:

· . GetUserRoles:

· . GetRoleInfo:

· . GetRoleInfo2:

· . IsPerformer:

· . CurrentUser:

· . UserActive:

· . GetUserName:

· . GetRoleName:

· . GetRoleDisplayName

· . SetAdHocUserStatus:

· . SetAdHocRoleStatus:

· . CreateAdHocUser:

· . CreateAdHocRole:

· . AddUsersToAdHocRole:

· . SetAdHocUserExpiration:

· . SetAdHocRoleExpiration:

· . SetAdHocUserAttr:

· . SetAdHocRoleAttr:

· . RemoveUsersFromAdHocRole


Workflow Preferences API

Call the following API to retrieve user preference information. The API is defined in the PL/SQL package called WF_PREF.get_pref


Workflow Monitor APIs

Call the following APIs to generate a complete URL to access the various pages of the Workflow Monitor. The APIs are defined in the PL/SQL package called WF_MONITOR.

· GetDiagramURL

· GetEnvelopeURL

· GetAdvancedEnvelopeURL


Workflow Queue APIs

Oracle Workflow queue APIs can be called by an application program or a workflow function in the runtime phase to handle workflow Advanced Queues processing. In Oracle Workflow, an ’outbound’ and an ’inbound’ queue is established. A package of data on the queue is referred to as an event or a message. A message in this context is different from the messages associated with notification activities.

· . EnqueueInbound:

· DequeueOutbound:

· . DequeueEventDetail:

· . PurgeEvent:

· . PurgeItemtype:

· . ProcessInboundQueue:

· . GetMessageHandle:

· . Deferred_queue:
· . Inbound_queue:

· . Outbound_queue:


Document Management APIs

The following document management APIs can be called by user interface (UI) agents to return URLs or javascript functions that enable integrated access to supported document management systems. All supported document management (DM) systems accommodate a URL interface to access documents. The document management APIs allow you to access documents across multiple instances of the same DM system, as well as across multiple instances of DM systems from different vendors within the same network. The document management APIs are defined in a PL/SQL package called FND_DOCUMENT_MANAGEMENT

· get_launch_document_url

· get_launch_attach_url

· get_open_dm_display_window

· get_open_dm_attach_window

· set_document_id_html


Notification APIs

The following APIs can be called by a notification agent to manage notifications for a notification activity. The APIs are stored in the PL./SQL package called WF_NOTIFICATION. Many of these Notification APIs also have corresponding Java methods that you can call from any Java program to integrate with Oracle Workflow. The following list indicates whether the Notification APIs are available as PL/SQL Send: .functions/procedures, as Java methods, or both.

· SendGroup: .

· Forward: .

· Transfer: .

· Cancel: .

· CancelGroup: .

· Respond: .

· Responder: .

· VoteCount: .

· OpenNotificationsExist: .

· Close: .

· AddAttr: .

· SetAttribute: .

· GetAttrInfo: .

· GetInfo: .

· GetText: .

· GetShortText: .

· GetAttribute: .

· GetAttrDoc: .

· GetSubject: .

· GetBody: .

· GetShortBody: .

· TestContext: .

· AccessCheck: .

· WorkCount: .

· GetNotifications: .

· GetNotificationAttributes: .


Workflow Tables :

· WF_ITEM_ACTIVITY_STATUSES

· WF_ITEM_ACTIVITY_STATUSES_H

· WF_ITEM_ATTRIBUTE_VALUES

· WF_ITEMS

· WF_LOCAL_USERS

· WF_LOCAL_ROLES

· WF_LOCAL_USER_ROLES


Workflow Views

Public views are available for accessing workflow data. If you are using the version of Oracle Workflow embedded in Oracle Applications, these views are installed in the APPS account. If you are using the standalone version of Oracle Workflow, these view are installed in the same account as the Oracle Workflow server.

· WF_ITEM_ACTIVITY_STATUSES_V

· WF_NOTIFICATION_ATTR_RESP_V:

· WF_RUNNABLE_PROCESSES_V:

· WF_ITEMS_V:

· WF_USERS

· WF_ROLES

· WF_USER_ROLES



WORKFLOW TABLES

MASTER TABLES
WF_ACTIVITIES
WF_ITEM_TYPES
WF_ITEM_ATTRIBUTES
WF_MESSAGES
WF_LOCAL_ROLES

TRANSACTION TABLES
WF_ITEMS
WF_NOTIFICATIONS
WF_NOTIFICATIONS_ATTRIBUTES
WF_ITEM_ATTRIBUTES_VALUES

DIRECTORY
WF_ROLES
WF_USERS
WF_USER_ROLES
WF_LOCAL_ROLES

TYPES OF WORKFLOWS
SYNCHRONOUS WF
ASYNCHRONOUS WF
FORCED SYNCHRONOUS WF


SOME IMPORTANT WORKFLOWS
GL ACCOUNT GENERATOR WF
COST OF GOODS WF
SALES ORDER WF
REQUISITION APPROVAL WF
PO APPROVAL WF
HRSS WF
PAYROLL PROCESS WF
TASKS WF


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

SELECT * FROM WF_ACTIVITY_ATTR_VALUES
WHERE NAME LIKE '%MAST%'
AND PROCESS_ACTIVITY_ID
IN
( SELECT *-- PROCESS_ACTIVITY
FROM WF_ITEM_ACTIVITY_STATUSES
WHERE ITEM_TYPE = 'OEOH'
AND ITEM_KEY ='62348' )

SELECT * FROM WF_ITEM_TYPES
SELECT * FROM WF_LOOKUPS_TL
SELECT * FROM WF_NOTIFICATIONS
WHERE MESSAGE_TYPE ='TEST'
ORDER BY BEGIN_DATE DESC

SELECT * FROM WF_NOTIFICATION_ATTRIBUTES
SELECT * FROM WF_MESSAGES
SELECT * FROM WF_MESSAGES_TL
SELECT * FROM WF_MESSAGE_ATTRIBUTES
SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL
SELECT * FROM WF_ETS
SELECT * FROM WF_PROCESS_ACTIVITIES