Workflow Directory Service Views (Oracle Workflow Administrator's Guide)

Oracle Workflow Administrator's Guide
Release 2.6.3
Part Number B10283-02


	Previous	Next		Contents	Index	Glossary

Workflow Directory Service Views

Oracle Workflow relies on three views named WF_USERS, WF_ROLES, and WF_USER_ROLES to reference user and role information. 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, WF_USERS, WF_ROLES, and WF_USER_ROLES 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 and each user/role association in WF_LOCAL_USER_ROLES. After that date, an expired role is no longer included in the seeded WF_USERS and WF_ROLES view, and an expired user/role association is no longer included in the seeded WF_USER_ROLES view.

However, note that although the expired rows no longer appear in the views, they still exist in the Workflow local tables until you purge them using the WF_PURGE.Directory() API. You should periodically purge expired users and roles 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 WF_USERS and WF_ROLES 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 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

The WF_USERS

view references information about the individuals in your organization who may utilize Oracle Workflow functionality or receive workflow notifications.

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 allows users to receive periodic e-mail summaries of their open notifications. However, to respond to the individual notifications, they must query the notification from the Worklist web page. 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 query their notifications from the Worklist web page.

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 web page and let individual users override that default value by changing their notification preference in the User Preferences web page. 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, which can be set using the Global Preferences or User Preferences web pages in Oracle Workflow.

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 using the Global Preferences web page and let individual users override that default value by changing their language in the User Preferences web page. 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, which can be set using the Global Preferences or User Preferences web pages in Oracle Workflow.

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 using the Global Preferences web page and let individual users override that default value by changing their territory in the User Preferences web page. 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 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.

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.

Security_Group_ID--An identifier for the security group to which the user belongs. Reserved for future use. See: Overview of Security Groups, Oracle Applications System Administrator's Guide.

User_Flag--An indicator to determine whether this role is an individual user. Only roles with the user flag set to Y are included in the WF_USERS view.

Partition_ID--In Oracle Applications, an identifier for the partition in which the user record is stored. In standalone Oracle Workflow, the partition ID should always be 0 (zero). The partition ID is for Oracle Workflow internal use only.

WF_ROLES

The WF_ROLES

view references information about all the roles in your organization who may utilize Oracle Workflow functionality or receive workflow notifications. This view must contain the following required columns pertaining to the roles in your repository. Those columns that are preceded by an asterisk (*) are similar to the corresponding columns described for the WF_USERS view.

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', 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

*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.

*Security_Group_ID

User_Flag--An indicator to determine whether this role is an individual user. All roles are included in the WF_ROLES view regardless of this flag.

*Partition_ID

WF_USER_ROLES

The WF_USER_ROLES

view is an intersection of the users and roles in WF_USERS and WF_ROLES, showing which users are members of which roles.

Note: A role can contain only individual users as its members. It cannot contain another role.

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.

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_Name--The internal name of the role as listed in the view WF_ROLES.

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.

Partition_ID--In Oracle Applications, an identifier for the partition in which the record of the association of this user with this role is stored. In standalone Oracle Workflow, the partition ID should always be 0 (zero). The partition ID is for Oracle Workflow internal use only.


	Previous	Next		Contents	Index	Glossary