Previous	Next		Contents	Index	Glossary

If you are using the standalone version of Oracle Workflow, you can choose to implement definitions for these views from one of two predefined directory services provided by Oracle Workflow, which let you either integrate with Oracle Internet Directory (OID) as your directory repository or use Oracle Database users and roles for your directory service. If you are using the version of Oracle Workflow embedded in Oracle Applications, directory service views for the unified Oracle Applications environment are automatically defined for you during installation.

Note: An expiration date can be assigned to each role in WF_LOCAL_ROLES, each user/role association in WF_LOCAL_USER_ROLES, and each user/role assignment in WF_ROLE_HIERARCHIES. After that date, an expired role is no longer included in the predefined WF_USERS and WF_ROLES view, an expired user/role association is no longer included in the predefined WF_USER_ROLES view, and an expired user/role assignment is no longer included in the WF_USER_ROLE_ASSIGNMENTS_V view.

However, note that although the expired rows no longer appear in these views, they still exist in the Workflow local tables. You should periodically purge expired ad hoc users and roles using the WF_PURGE.Directory() API in order to improve performance. See: Directory, Oracle Workflow API Reference.

You can also create your own directory service by defining custom views with the required columns. However, note that only the predefined directory services provided by Oracle Workflow are supported by Oracle. See: Oracle Workflow Support Policy, Oracle Workflow Developer's Guide.

If you create your own custom view definitions:

• Each individual user identified by WF_USERS must also appear in the WF_ROLES view as a role.

• You should set the user flag in the underlying tables to Y for all the roles that also represent individual users, and set the user flag to N for all other roles.

• You should include the WF_LOCAL_ROLES and WF_LOCAL_USER_ROLES tables in the view definitions. You can optionally also include the WF_LOCAL_ROLES_TL table for multiple language support.

• You should avoid selecting from DUAL to incorporate additional users and roles into the directory service views as this allows you to violate the unique constraint on certain columns of the views and reduces performance with unnecessary joins between the 'select from DUAL' statements.

• To take advantage of unique indexes when querying users, make sure you initially enter the usernames in your database in uppercase only. Forcing the usernames to uppercase in your view definition results in poor performance when accessing these views.

• You should run the script wfdirchk.sql to validate your directory service data model. This script is located on your server in the Oracle Workflow admin/sql subdirectory for the standalone version of Oracle Workflow or in the sql subdirectory under $FND_TOP for the version of Oracle Workflow embedded in Oracle Applications. See: Wfdirchk.sql.

Note: Avoid making a join to a view that contains a union, as this results in poor database performance. The Oracle Database is unable to preserve the indexes in that view when you make such a join. The workflow directory service views you create will most likely contain union; therefore you should not join to them directly. If you need to retrieve data from any of the three directory services views, use the appropriate directory services API. See: Workflow Directory Service APIs, Oracle Workflow API Reference.

WF_USERS

Note: In WF_LOCAL_ROLES, a role that is an individual user has the user flag set to Y.

Note: For Oracle Applications, this view now includes only Oracle Applications users originating from the FND_USER table, TCA person parties, TCA contacts, and ad hoc users, although an Oracle Applications user record may also include information from Oracle Human Resources if the user is linked to an Oracle Human Resources person.

The WF_USERS view must contain the following required columns:

• Name--The internal name of the user as referenced by the Workflow Engine and Notification System. For example, an internal name for a user can be MBEECH or 009, where 009 represents the user's employee ID.

Attention: If you define custom views, the Name column must be sourced from a column that is no longer than 320 characters, and it is recommended that the internal name be all uppercase. If your source table does not have a column that meets these criteria, DO NOT use string functions to force these restrictions. Instead, define the Name column to be <orig_system>:<orig_system_id> so that Oracle Workflow can reference the original base table where users are stored and a unique user in that table. For example, "PER_PEOPLE:009" could represent a user whose employee ID is 009 and whose record is stored in a personnel table called PER_PEOPLE.

• Display_Name--The display name of the user. An example of a display name can be 'Beech, Matthew'.

• Description--An optional description of the user.

• Notification_Preference--The method by which this user prefers to receive notifications. A value of MAILTEXT, MAILHTML, MAILHTM2, or MAILATTH allows users to receive and respond to notifications by plain text e-mail, HTML-formatted e-mail with attachments, HTML-formatted e-mail without attachments, or by plain text e-mail with HTML attachments, respectively. A value of QUERY allows users to query notifications from the Worklist web page. Finally, a value of SUMMARY or, for Oracle Applications only, SUMHTML, allows users to receive periodic e-mail summaries of their open notifications. However, to respond to the individual notifications, they must view the notification in the Worklist web pages. See: Overview of Notification Handling, Oracle Workflow User's Guide and Notification Preferences.

Note: A notification preference of MAILTEXT, MAILHTML, MAILHTM2, or MAILATTH also allows users to view their notifications in the Worklist web pages.

Note: In Oracle Applications, if no notification preference is provided by the originating system, the notification preference is set to MAILHTML by default.

Note: If you define custom views, you can map the Notification_Preference column over the Oracle Workflow preferences table using the statement below. The benefit of doing so is that you can then globally set the default notification preference for all users in your enterprise using the Global Preferences page in standalone Oracle Workflow or the Workflow Configuration page in Oracle Applications, and let individual users override that default value by changing their notification preference in the User Preferences page in standalone Oracle Workflow or the Preferences page in Oracle Applications. See: Setting Global User Preferences, Setting User Preferences, Oracle Workflow User's Guide, and get_pref, Oracle Workflow Developer's Guide.

			NVL(wf_pref.get_pref(USR.USER_NAME,'MAILTYPE'),

			'MAILHTML')

• Language--The value of the database NLS_LANGUAGE initialization parameter that specifies the default language-dependent behavior of the user's notification session. The Language column is required and cannot be left null. Refer to your Oracle Database user's guide or installation manual for the list of supported language conventions.

Note: In Oracle Applications, for roles that are Oracle Applications users marked with an originating system of FND_USR or PER, Oracle Workflow uses the GetRoleInfo() procedure to find the language for a user, rather than querying this column in the directory service views. GetRoleInfo() by default retrieves the language value from the ICX: Language profile option for that Oracle Applications user.

However, if the WF_PREFERENCE resource token is defined and set to FND, then the GetRoleInfo() procedure obtains the language value from the Oracle Workflow preferences table instead.

The WF_PREFERENCE resource token is not used in the standalone version of Oracle Workflow.

Note: If you define custom views, and, if you are using Oracle Applications, the WF_PREFERENCE resource token is set to FND, then you can map the Language column over the Oracle Workflow preferences table using the statement below. The benefit of doing so is that you can then globally set the default language for all users in your enterprise and let individual users override that default value by changing their language preference. See: Setting Global User Preferences, Setting User Preferences, Oracle Workflow User's Guide, and get_pref, Oracle Workflow Developer's Guide.

			NVL(wf_pref.get_pref(USR.USER_NAME,'LANGUAGE'),

			<default_language>)

Attention: Make sure that the e-mail templates used by notification mailers to send notifications have been translated by Oracle to the language you wish to set. The standard e-mail templates are delivered in a file called wfmail.wft under the subdirectory $ORACLE_HOME/wf/res/<lang> for the standalone version of Oracle Workflow or $FND_TOP/import/<lang> for the version of Oracle Workflow embedded in Oracle Applications. You can check the appropriate language subdirectory to verify if the templates have been translated to the language you wish to set. See: Modifying Your Message Templates.

• Territory--The value of the database NLS_TERRITORY initialization parameter that specifies the default territory-dependent formatting used in the user's notification session. The Territory column is required and cannot be left null. Refer to your Oracle Database user's guide or installation manual for the list of supported territory conventions.

Note: In Oracle Applications, for roles that are Oracle Applications users marked with an originating system of FND_USR or PER, Oracle Workflow uses the GetRoleInfo() procedure to find the territory for a user, rather than querying this column in the directory service views. GetRoleInfo() by default retrieves the territory value from the ICX: Territory profile option for that Oracle Applications user.

However, if the WF_PREFERENCE resource token is defined and set to FND, then the GetRoleInfo() procedure obtains the territory value from the Oracle Workflow preferences table instead.

The WF_PREFERENCE resource token is not used in the standalone version of Oracle Workflow.

Note: If you define custom views, and, if you are using Oracle Applications, the WF_PREFERENCE resource token is set to FND, then you can map the Territory column over the Oracle Workflow preferences table using the statement below. The benefit of doing so is that you can then globally set the default territory for all users in your enterprise and let individual users override that default value by changing their territory preference. See: Setting Global User Preferences, Setting User Preferences, Oracle Workflow User's Guide, and get_pref, Oracle Workflow Developer's Guide.

			NVL(wf_pref.get_pref(USR.USER_NAME,'TERRITORY'),

			<default_territory>)

• Email_Address--A valid electronic mail address for this user or a mail distribution list defined by your electronic mail system.

• Fax--A fax number for the user.

• Orig_System--A code that you assign to originating system, which is the directory repository that this view is ultimately based on. For example, if this view is based on the personnel data stored in a Human Resource Management System, Orig_System can be defined as PER.

• Orig_System_ID--The primary key that identifies the user in this repository system. For example, Orig_System_ID can be defined as the value stored in a column called PERSON_ID in a Human Resources database table called PER_PEOPLE.

• Parent_Orig_System--An optional code for the originating system of an entity that you want to mark as being related to this user. For example, a supplier could be marked as the parent of a supplier site.

• Parent_Orig_System_ID--The primary key that identifies the parent entity in the parent originating system.

Note: If no parent information is provided, the Parent_Orig_System and Parent_Orig_System_ID default to the user's own Orig_System and Orig_System_ID, respectively.

• Start_Date--The date at which the user becomes valid in the directory service.

• Status--The availability of the user to participate in a workflow process. The possible statuses are: active (ACTIVE), unavailable for an extended period (EXTLEAVE), permanently unavailable (INACTIVE), and temporarily unavailable (TMPLEAVE). These statuses are also stored in the lookup type called WFSTD_AVAILABILITY_STATUS.

• Expiration_Date--The date at which the user is no longer valid in the directory service. After this date, the user will no longer appear in the seeded WF_USERS view.

• Owner_Tag--A code to identify the program or application that owns the information for this user.

WF_ROLES

Attention: Each user identified by WF_USERS must also appear in the WF_ROLES view as a role. This is a requirement for Oracle Workflow.

Note: If a user is a member of a role and the user information such as language and notification preference is different from the role information, the Expand Roles option for a notification addressed to the role determines whether the user information or the role information takes precedence. If the Expand Roles option is not checked and the Notification System delivers the notification to the role, the role information will override the user information. If Expand Roles is checked, however, then each user in the role will receive a separate copy of the notification, and the user information will override the role information.

If a user has a notification preference of 'SUMMARY' or 'SUMHTML', and the user is also a member of a multi-user role with a different notification preference such as 'MAILHTML', the Notification System will use the Expand Roles setting to determine whether to deliver the notification according to the role or user notification preference. However, even if Expand Roles is not checked and the notification preference of the role takes precedence, the notification will still appear in the user's summary message because the notification is part of the user's worklist.

• Name--The internal name of the role as referenced by the Workflow Engine and Notification System.

Attention: If you define custom views, the Name column must be sourced from a column that is no longer than 320 characters, and it is recommended that the internal name be all uppercase. If your source table does not have a column that meets these criteria, DO NOT use string functions to force these restrictions. Instead, define the Name column to be <orig_system>:<orig_system_id> so that Oracle Workflow can reference the original base table where roles are stored and a unique role in that table. For example, "PER_POSITION:009" could represent a position whose ID is 009 and whose record is stored in the personnel table called PER_POSITION.

• *Display_Name

• *Description

• *Notification_Preference

• *Language

• *Territory

• Email_Address--If the e-mail address is null for a given role, notification mailers send an individual e-mail to each user within the role.

• *Fax

• *Orig_System

• *Orig_System_ID

• *Parent_Orig_System

• *Parent_Orig_System_ID

• *Start_Date

• *Status

• Expiration_Date--The date at which the role is no longer valid in the directory service. After this date, the role will no longer appear in the seeded WF_ROLES view.

• *Owner_Tag

WF_USER_ROLES

Note: A role can contain only individual users as its members. It cannot contain another role. However, in Oracle Applications roles can be related to each other in a hierarchy so that users assigned to one role automatically inherit membership in its superior roles as well.

The WF_USER_ROLES view must contain the following required columns:

• User_Name--The internal name of the user as listed in the view WF_USERS.

• Role_Name--The internal name of the role as listed in the view WF_ROLES.

• User_Orig_System--A code that you assign to the user directory repository as listed in the view WF_USERS.

• User_Orig_System_ID--The primary key that identifies the user in the user directory repository as listed in the view WF_USERS.

• Role_Orig_System--A code that you assign to the role directory repository as listed in the view WF_ROLES.

• Role_Orig_System_ID--The primary key that identifies the role in the role directory repository as listed in the view WF_ROLES.

• Start_Date--The date at which the association of this user with this role becomes valid in the directory service.

• Expiration_Date--The date at which the association of this user with this role is no longer valid in the directory service. After this date, the user and role association will no longer appear in the seeded WF_USER_ROLES view.

• Assignment_Type - A code indicating how the user was assigned to membership in this role. This column is used only in Oracle Applications.

• D - The user was directly assigned to this role.

• I - The user inherited this role through membership in another role.

• B - The user has both direct and inherited assignments to this role.

• Parent_Orig_System--An optional code for the originating system of an entity that you want to mark as being related to this user/role association.

• Parent_Orig_System_ID--The primary key that identifies the parent entity in the parent originating system.

WF_USER_ROLE_ASSIGNMENTS_V

The WF_USER_ROLE_ASSIGNMENTS_V view contains the following columns:

• User_Name--The internal name of the user as listed in the view WF_USERS.

• Role_Name--The internal name of the role as listed in the view WF_ROLES.

• Assigning_Role--The role from which the user is inheriting assignment to this role.

• Start_Date--The date at which the assignment of this user to this role becomes valid in the directory service.

• End_Date--The date at which the assignment of this user to this role is no longer valid in the directory service.

• Assignment_Type--The way in which the user was assigned to membership in this role, either DIRECT or INHERITED.

WF_ALL_ROLES_VL

The WF_ALL_ROLES_VL view contains the following columns:

• Name--The internal name of the role.

• Display_Name--The display name of the role.

• Description--An optional description of the role.

• Notification_Preference--The method by which this role prefers to receive notifications.

• Language--The value of the database NLS_LANGUAGE initialization parameter that specifies the default language-dependent behavior of the role's notification session.

• Territory--The value of the database NLS_TERRITORY initialization parameter that specifies the default territory-dependent formatting used in the role's notification session.

• Email_Address--A valid electronic mail address for this role.

• Fax--A fax number for the role.

• Orig_System--A code that you assign to originating system on which this view is ultimately based.

• Orig_System_ID--The primary key that identifies the role in the originating system.

• Start_Date--The date at which the role becomes valid in the directory service.

• Status--The availability of the role to participate in a workflow process. The possible statuses are: active (ACTIVE), unavailable for an extended period (EXTLEAVE), permanently unavailable (INACTIVE), and temporarily unavailable (TMPLEAVE). These statuses are also stored in the lookup type called WFSTD_AVAILABILITY_STATUS.

• Expiration_Date--The date at which the role is no longer valid in the directory service.

• Owner_Tag--A code to identify the program or application that owns the information for this role.

• Created_By--Standard Who column.

• Creation_Date--Standard Who column.

• Last_Updated_By--Standard Who column.

• Last_Update_Date--Standard Who column.

• Last_Update_Login--Standard Who column.

WF_ALL_USER_ROLES

The WF_ALL_USER_ROLES view contains the following columns:

• User_Name--The internal name of the user.

• Role_Name--The internal name of the role .

• User_Orig_System--A code that you assign to the user directory repository.

• User_Orig_System_ID--The primary key that identifies the user in the user originating system.

• Role_Orig_System--A code that you assign to the role directory repository.

• Role_Orig_System_ID--The primary key that identifies the role in the role originating system.

• Parent_Orig_System--An optional code for the originating system of an entity that you want to mark as being related to this user/role association.

• Parent_Orig_System_ID--The primary key that identifies the parent entity in the parent originating system.

• Assignment_Type - A code indicating how the user was assigned to membership in this role. This column is used only in Oracle Applications.

• D - The user was directly assigned to this role.

• I - The user inherited this role through membership in another role.

• B - The user has both direct and inherited assignments to this role.

• Start_Date--The date at which the association of this user with this role becomes valid in the directory service.

• Expiration_Date--The date at which the association of this user with this role is no longer valid in the directory service.

• Owner_Tag--A code to identify the program or application that owns the information for the association of this user with this role.

• Created_By--Standard Who column.

• Creation_Date--Standard Who column.

• Last_Updated_By--Standard Who column.

• Last_Update_Date--Standard Who column.

• Last_Update_Login--Standard Who column.

WF_ALL_USER_ROLE_ASSIGNMENTS

The WF_ALL_USER_ROLE_ASSIGNMENTS view contains the following columns:

• User_Name--The internal name of the user.

• Role_Name--The internal name of the role .

• Assigning_Role--The role from which the user is inheriting assignment to this role.

• Start_Date--The date at which the assignment of this user to this role becomes valid in the directory service.

• End_Date--The date at which the assignment of this user to this role is no longer valid in the directory service.

• Assignment_Type--The way in which the user was assigned to membership in this role, either DIRECT or INHERITED.

• Created_By--Standard Who column.

• Creation_Date--Standard Who column.

• Last_Updated_By--Standard Who column.

• Last_Update_Date--Standard Who column.

• Last_Update_Login--Standard Who column.