WFLOAD Command for Oracle Workflow Migration


We use the Workflow Definitions Loader to save or load process definitions from a database or flat file. We can also define as it is a utility that moves workflow data between a file and a database and it is also used to upgrade, upload and download the workflow data.

Following is the syntax and the parameter details of WFLOAD.

WFLOAD <apps/pwd>@<connect_string> 0 Y {UPLOAD | UPGRADE | FORCE} <filepath>[<file_name.wft>]

Example:

WFLOAD apps/pwd@<connect_string> 0 Y UPLOAD $XXSCM_TOP/install/POAPPRV.wft

Different “Upload Modes” applicable to WFLOAD:

UPGRADE Honors both protection and customization levels of data
UPLOAD Honors only protection level of data [No respect of Customization Level]
FORCE Force upload regardless of protection or customization level

Use the following command to download workflow definition:

WFLOAD <apps_user_name>/<password>@db 0 Y DOWNLOAD file_name.wft <Item_Type>

Example:

WFLOAD <apps_user_name>/<password>@db 0 Y DOWNLOAD POAPPRV.wft

As a standard practice, Oracle’s upgrade patches mostly run in UPGRADE mode, hence your customizations can protected if best practices for development are followed.

One can find the seeded workflow definition files under$<PRODUCT_TOP>/patch/115/import/<LANG> directory.

For example, .wft files related to Purchasing can be located in the following directory:-
$PO_TOP/patch/115/import/US

Advertisements

Oracle Workflow Access Protection: A Technical Note


Oracle Workflow assigns a protection and customization level to every workflow object definition stored in the database and requires every user of Oracle Workflow to operate at a certain access level.

The combination of protection, customization, and access levels build the access protection feature and determine whether a user can modify a given workflow object. The level, in all three cases, is a numeric value ranging from 0 to 1000 that indicates the relationship between different organizations as providers and consumers of seed data.

The following ranges of levels are there in 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

Access Level:

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.

Navigation to view your access level:

Oracle Workflow Builder > Help > About Workflow

Workflow Access Level

Note: If you are going to run the Workflow Definitions Loader program to download workflow process definitions from the database to a file, check the value for the environment variable WF_ACCESS_LEVEL on your workflow server.

The Access tab lets you define whether:

  • Future customizations to the object are preserved during a workflow definition upgrade.
  • The object can be edited by users operating at a higher access level.

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 helps control whether other users can modify the object based on their access levels, by allowing only users with an access level equal to or lower than the object’s protection level to modify the object.

Navigation to set the protection level of an object:

Oracle Workflow Builder > object’s property page > Access tab

Notes:

  • If you protect an object against customization, you effectively assign the object a protection level equal to your current access level.
  • Objects protected against customizations are considered “seed data.”
  • Only users operating at an access level equal to or lower than the protection level of the object can modify the object.
  • Users operating at an access level greater than the protection level of the object will see a small lock on the icon for the object in the navigator tree, indicating that the object is read-only.

Customization Level:

An object’s customization level helps control whether other users can modify the object based on their access levels, by allowing only users with an access level equal to or higher than the object’s customization level to modify the object.

Navigation to set the customization level of an object:

Oracle Workflow Builder > object’s property page > Access tab

Setting the customization level ensures that a customizable object that has been customized never gets overwritten during a seed data upgrade, because the upgrade always occurs with the Workflow Definitions Loader operating at an access level below the customized object’s customization level.

Use the Options region to set the protection and customization levels of an object.

Lock at this Access Level: Protects the object at the current access level and does not allow the object to be customized by higher access levels

Preserve Customizations: Prevents customized objects from being overwritten during a workflow definition upgrade.

Situation
Access Level
Lock at this Access Level
Preserve Customization
Level Summary
1 Set as 100

No

No

A: 100, P: 1000, C: 0
2 Set as 100

Yes

No

A: 100, P: 100, C: 0
3 Set as 100

No

Yes

A: 100, P: 1000, C: 100
4 Set as 100

Yes

Yes

A: 100, P: 100, C: 100

The indicator bar provides a visual range of access levels that can edit the object:

Black vertical line: Current access level

White range: Can’t edit the object

Solid green: Can edit the object

Cross-hatch green: Usually cannot modify the object because it has been customized, but can now do so because Oracle Workflow Builder is set to upload mode so that customized objects can be modified.

1] Allow access to everyone

Workflow Protection Level-1

None: Object can be updated at any time by any access level.

2] Limit access to users with access levels equal to your own or lower

Workflow Protection Level-2

Lock at this Access Level: Protects the object at the current access level and does not allow the object to be customized, except by access levels 0-100.

3] Limit access to users with access levels equal to your own or higher

Workflow Protection Level-3

Preserve Customizations: Disallows customized objects from being overwritten during a workflow upgrade.

  • Object may be updated by access levels 100-1000.
  • If the Allow modifications of customized objects check box is selected, customized objects can also be updated by access levels 0-99 as represented by green crosshatches in the indicator bar.

4] Limit access to users with access levels equal to your own

Workflow Protection Level-4

Both: Object can only be updated by the access level at which the object is protected.

  • Object may only be updated by access level 100.
  • If the Allow Modifications of Customized Objects check box is selected, customized objects can also be updated by access levels 0-99 as represented by green crosshatches in the indicator bar.

Oracle E-Business Suite customers should select both the Preserve Customizations and Lock at this Access Level check boxes to protect your workflow objects during upgrades.

During an Oracle Workflow seed data upgrade, the Workflow Definitions Loader is always run in Upgrade mode at an access level less than 100. As a result, the upgrade will not overwrite any object with a customization level of 100 or higher.

References:

docs.oracle.com

oracle.anilpassi.com

Understanding the Workflow Definition (Set up) Tables


Oracle workflow is also a database application as many other application of Oracle, which means that it also utilizes database tables as the basis of its operation. Behind its very pleasant and user-friendly GUI, It’s the database tables which store every piece of information regarding the attributes, functions, process, messages you create while designing a workflow. If you really want to know Workflow and discover how it works, you have to understand its table structures.

In this article, I have covered the tables which got affected, when you create or modify a workflow process. However it doesn’t include the tables which capture information at run time when you run a workflow. I have taken the ‘PO Approval Workflow’ (POAPPRV) for example purpose.

1] WF_ITEM_TYPES:

The wf_item_types table contains one record for each item_type created. The eight character name of the item_type represents the “Internal Name” of the item. It also functions as the primary key for this table. Some key columns are:

  • NAME: It is a mandatory field. It represents the internal name of the item type.
  • PROTECT_LEVEL: Level at which the data is protected. A mandatory field.
  • CUSTOM_LEVEL: Level of user who last updated the row. Again a mandatory field.
  • WF_SELECTOR: It stores the name of the PL/SQL procedure which implements selector function. This is an optional field.
  • PERSISTENCE_TYPE: Indicates whether item type is temporary or permanent.
  • PERSISTENCE_DAYS: Number of days until purge if persistence is temporary.

Workflow Item Type Display Name and description can be found in WF_ITEM_TYPES _TL table. Also check the view WF_ITEM_TYPES_VL.

 

SELECT * FROM WF_ITEM_TYPES WHERE NAME='POAPPRV';
SELECT * FROM WF_ITEM_TYPES_TL WHERE NAME='POAPPRV';
SELECT * FROM WF_ITEM_TYPES_VL WHERE NAME='POAPPRV';

2] WF_ITEM_ATTRIBUTES:

This table stores definitions of attributes associated with a process. The entries in this table correspond to the “Attributes” subheading in the Workflow Builder. An item attribute works like a variable which can hold values that are specific to the process instance or which may change at run time. Some key columns are:

  • ITEM_TYPE: Internal name for the item type that owns the attribute. A mandatory field.
  • NAME: Internal name of the attribute. A mandatory field.
  • SEQUENCE: Order of the attribute within the message
  • TYPE: Each item attribute is assigned a datatype, such as “Character”, “Number”, or “Date”.

There are three fields to hold a default value, but only one of them will be populated for any item attribute, depending upon the datatype. For example, if you create an item attribute with a datatype of “Number”, and then supply a default value, that value would be stored in the “number_default” field.

The “format” field stores information about a format mask that should be applied to number or date values, and the “subtype” field contains “SEND” or “RECEIVE”. The Translation table is WF_ITEM_ATTRIBUTES_TL and the related view is WF_ITEM_ATTRIBUTES_VL.

 

SELECT * FROM WF_ITEM_ATTRIBUTES WHERE ITEM_TYPE='POAPPRV' AND NAME='PO_DESCRIPTION';
SELECT * FROM WF_ITEM_ATTRIBUTES_TL WHERE ITEM_TYPE='POAPPRV' AND NAME='PO_DESCRIPTION';
SELECT * FROM WF_ITEM_ATTRIBUTES_VL WHERE ITEM_TYPE='POAPPRV' AND NAME='PO_DESCRIPTION';

3] WF_ACTIVITIES:

This table stores the definition of an activity. Activities can be processes, notifications, functions or folders. A process activity is a modeled workflow process, which can be included as an activity in other processes to represent a sub-process. 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, but it provides a means of grouping activities. Some key columns are:

  • ITEM_TYPE: Internal name for the Item Type that owns the message.
  • NAME: Internal name for the activity.
  • VERSION: It is used to support multiple versions of the same process running at the same time. The version number works in concert with the “begin_date” and “end_date” fields, to ensure that only one version of any activity is active at any given time. By versioning, the previously launched processes retain the process definition that was in force at the time they were launched.
  • TYPE: The “type” field is the way that the individual types of activities can be distinguished. There are five valid values found in the “type” field: “FUNCTION”, “NOTICE”, “EVENT”, “PROCESS”, and “FOLDER”.
  • RERUN: Determines if activity is rerun during looping.
  • EXPAND_ROLE: Determines how many roles are required to respond to a notification activity.
  • FUNCTION: For function activities only, the field is used to store the name of the PLSQL procedure that the Workflow Engine should call to implement the function.
  • RESULT_TYPE: If you intend to model transitions in a process based upon values returned by an activity node, then the expected results must be predefined by supplying a lookup type, which is stored in this field.
  • ICON_NAME: Name of activity icon used in process window.
  • MESSAGE: For notification activities only, the field called “message” will be populated. In these cases, it will contain the internal name of the message that the notification will deliver.
  • ERROR_PROCESS: Workflow process to run in case of an error.
  • ERROR_ITEM_TYPE: Name of item type to execute in case of error.
  • RUNNABLE_FLAG: Flag (Y or N) to indicate if activity is runnable.
  • FUNCTION_TYPE: Indicates whether function type is pl/sql or internal.

The Translation table is WF_ACTIVITIES_TL and the related view is WF_ACTIVITIES_VL.

SELECT * FROM WF_ACTIVITIES WHERE ITEM_TYPE='POAPPRV'
AND NAME='FIND_APPROVER';

SELECT * FROM WF_ACTIVITIES_TL WHERE ITEM_TYPE='POAPPRV'
AND NAME='FIND_APPROVER';

SELECT * FROM WF_ACTIVITIES_VL WHERE ITEM_TYPE='POAPPRV' AND NAME='FIND_APPROVER';

4] WF_ACTIVITY_ATTRIBUTES:

This table defines attributes which behave as parameters for an activity. Activity attributes are only used by function activities. Each row includes the associated activity, type of attribute, and the format used by the activity. Examples of valid attribute types are DATE, DOCUMENT, FORM, ITEMATTR, LOOKUP, and VARCHAR2. Notice that the table requires three fields just to identify to which activity the attribute is attached: the item_type, name, and version of the activity. To join this table to the wf_activities tables you must join all three of these fields to their corresponding fields in that table. Some key columns are:

  • ACTIVITY_ITEM_TYPE: Item type the activity is associated with
  • ACTIVITY_NAME: Internal name of the activity
  • ACTIVITY_VERSION: Version of the activity
  • NAME: Internal name of the attribute
  • SEQUENCE: Order of the attribute within the message
  • TYPE: This field refers to the datatype of the values that the attribute will contain.
  • VALUE_TYPE: Defines if the default is a constant or a reference to an item attribute.

The Translation table is WF_ACTIVITY_ATTRIBUTES_TL and the related view is WF_ACTIVITY_ATTRIBUTES_VL.

SELECT *
FROM WF_ACTIVITY_ATTRIBUTES
WHERE ACTIVITY_ITEM_TYPE='POAPPRV'
AND ACTIVITY_NAME       ='GET_NOTIFICATION_ATTRIBUTE'
AND ACTIVITY_VERSION    =
  (SELECT VERSION
  FROM WF_ACTIVITIES
  WHERE ITEM_TYPE='POAPPRV'
  AND NAME       ='GET_NOTIFICATION_ATTRIBUTE'
  AND TRUNC(SYSDATE) BETWEEN TRUNC(BEGIN_DATE) AND TRUNC(NVL(END_DATE,SYSDATE))
  );

5] WF_ACTIVITY_ATTR_VALUES:

This table used to track values contained in activity attributes. This table is identical in purpose to wf_item_attribute_values except it holds values for activity attributes instead of item attributes. Each row includes the process activity id and the associated value for the attribute. The interesting thing about this table is that it uses the process_activity_id to identify the activity to which the attribute is attached. The same activity can be inserted into a process more than one time, so the only way to uniquely identify the node to which this attribute is attached is to use the process_activity_id.

SELECT * FROM WF_ACTIVITY_ATTR_VALUES WHERE NAME='NTF_USER_NAME';

6] WF_MESSAGES:

The messages that are associated with notifications are stored in this table. Each message, which is uniquely identified by the combination of item_type and message_name (stored in the fields “type” and “name”) receives a single record in the wf_messages table. The actual text of the message is stored only in its localization table (wf_messages_tl). They can found in the “body” and “html_body” fields.

SELECT * FROM WF_MESSAGES
WHERE TYPE='POAPPRV' AND NAME='NOTIFY_BUYER';

SELECT * FROM WF_MESSAGES_TL
WHERE TYPE='POAPPRV' AND NAME='NOTIFY_BUYER';

7] WF_MESSAGE_ATTRIBUTES:

This table contains message attribute definitions. Each message may have zero or more message attributes. Message attributes define additional information that is to be sent to, or received from the user. These attributes can be used as tokens in the subject or body of a message template to place variables values into the message at runtime.

SELECT * FROM WF_MESSAGE_ATTRIBUTES
WHERE MESSAGE_TYPE='POAPPRV'
AND MESSAGE_NAME  ='NOTIFY_BUYER'
AND NAME          ='BUYER_DISPLAY_NAME';

8] WF_PROCESS_ACTIVITIES:

A process is a sequence of activities performed in a pre-determined order. When you create a process definition in the Workflow Builder by dragging various notifications and functions into the process window, the records created by the Builder are stored into this table.

SELECT * FROM WF_PROCESS_ACTIVITIES
WHERE PROCESS_ITEM_TYPE='POAPPRV'
AND PROCESS_NAME       ='APPROVE_PO_SUB_PROCESS';

9] WF_ACTIVITY_TRANSITIONS:

The flow of a process from node to node as indicated by the transition arrows is not saved in the wf_process_activities table. Instead this information is stored in this table.

A transition is defined by three discrete pieces of information: the node where the arrow begins, the node toward which the arrow points, and the result which, when returned by the beginning node, causes the transition to be followed. Not surprisingly, it is those three fields which are the most important fields in this table: “from_process_activity”, “to_process_activity”, and “result_code”. The values stored in “from_process_activity” and “to_process_activity” are numbers which represent the instance_id of the records from wf_process_activities from which and to which the transition is moving.

SELECT *
FROM WF_ACTIVITY_TRANSITIONS
WHERE FROM_PROCESS_ACTIVITY =
  (SELECT INSTANCE_ID
  FROM WF_PROCESS_ACTIVITIES
  WHERE PROCESS_ITEM_TYPE='POAPPRV'
  AND PROCESS_NAME       ='APPROVE_PO_SUB_PROCESS'
  AND INSTANCE_LABEL     ='START'
  AND PROCESS_VERSION    =
    (SELECT MAX(PROCESS_VERSION)
    FROM WF_PROCESS_ACTIVITIES
    WHERE PROCESS_ITEM_TYPE='POAPPRV'
    AND PROCESS_NAME       ='APPROVE_PO_SUB_PROCESS'
    AND INSTANCE_LABEL     ='START'
    )
  )
AND TO_PROCESS_ACTIVITY =
  (SELECT INSTANCE_ID
  FROM WF_PROCESS_ACTIVITIES
  WHERE PROCESS_ITEM_TYPE='POAPPRV'
  AND PROCESS_NAME       ='APPROVE_PO_SUB_PROCESS'
  AND INSTANCE_LABEL     ='IS_DOCUMENT_APPROVED'
  AND PROCESS_VERSION    =
    (SELECT MAX(PROCESS_VERSION)
    FROM WF_PROCESS_ACTIVITIES
    WHERE PROCESS_ITEM_TYPE='POAPPRV'
    AND PROCESS_NAME       ='APPROVE_PO_SUB_PROCESS'
    AND INSTANCE_LABEL     ='IS_DOCUMENT_APPROVED'
    )
  );

10] WF_LOOKUP_TYPES_TL & WF_LOOKUPS_TL:

Wf_lookup_types_tl is the table used to set up the types of results expected from Workflow activities like functions and notifications. This table does not contain the actual result values, it holds the groupings of the result_codes – the names you see in the Workflow Builder as the names of the Lookups. Wf_lookups_tl is the table that stores the component values that comprise a lookup_type.

SELECT *
FROM WF_LOOKUP_TYPES_TL
WHERE ITEM_TYPE='POAPPRV'
AND LOOKUP_TYPE='PO_POAPPRV_APPROVE_ACTION';

SELECT * FROM WF_LOOKUPS_TL
WHERE LOOKUP_TYPE='PO_POAPPRV_APPROVE_ACTION';

Oracle 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 Type: A grouping of workflow components into a high level category. All components of a workflow process must be associated with a specific item type. An item type can contain multiple processes.

Item Type Attribute: A feature of the item type which stores information that can be globally referenced by any activity in a process. Also referred to as an item attribute.

Process Activity: A series of actions that need to be performed to accomplish a business goal. A process is represented by a workflow diagram. A process can include function activities, notification activities, event activities, and other process activities (subprocesses), as well as the transitions between these activities.

Event Activity: A business event modeled as an activity so that it can be included in a workflow process.

Notification Activity: A unit of work that requires human intervention. A notification activity sends a message to a performer.

Function Activity: An automated unit of work usually defined as a PL/SQL stored procedure. A function activity can also run an external function. In the standalone version of Oracle Workflow, a function activity can also run a Java program on the middle tier.

Message:  The information sent by a notification activity. The message may request the performer (the role receiving the message) to do some work or may simply provide information.

Lookup Type: A list of values that can be referenced by any activity in a workflow process. The values in the list are called lookup codes.

Transition: The relationship that defines the completion of one activity and the activation of another activity within a process. In a process diagram, a transition is represented as an arrow between two activities.

Item: A specific business document or transaction.

Process Instance: A unique item being managed by a process.

Oracle Workflow: Why to use it?


Oracle Workflow is a series of tools designed to facilitate the creation and management of business process
models. It manages business processes according to defined rules (workflow process definitions), including the activities occurring as part of the process and the relationships of those activities. 

The benefits:

  • In e-business, it can be used as a part of the integration hub to make communicate different enterprises with each other over the internet for an end-to-end business.
  • Lets you continuously model complex business scenarios with drag & drop interface.
  • Model sophisticated business processes that include looping, branching, parallel processing, rendezvous, and more.
  • It can help save time by reducing repetitive data entry tasks, automating the approval hierarchies, automatically delivering notifications and reminders of work to be done and also providing self-service monitoring capabilities.
  • It defines and implements your business policies, streamlines the entire process, captures exceptions and takes action and is capable to adapt your processes as your business changes.
  • E-business accelerates the demand for system integration and communication is required between systems both within and beyond the enterprise. Business event-based workflows allow
    modeling of cross-system processes, enabling business process-based integration.
  • Oracle Workflow helps you design applications for change using workflow processes, enabling continuous business process improvement.
  • Tightly integrated with Oracle Database and provides published PL/SQL & Java APIs to drive workflows.