Oracle Workflow Setup Checklist
Step 13: Configuring the Oracle Workflow User List of Values
Several Oracle Workflow pages include fields that let you select a user or role as the field value from among the users and roles defined in your Oracle Workflow directory service. These fields provide a two-part list of values.
1. A list of user and role types that correspond to the originating system partitions in the Oracle Workflow directory service. Oracle Workflow retrieves these values from the WF_DIRECTORY_PARTITIONS table.
2. A list of the users and roles within the selected partition. Oracle Workflow retrieves these values from the WF_ROLE_LOV_VL view, which provides access to the roles stored in the WF_LOCAL_ROLES table.
By default, these lists include all partitions and all users and roles defined in the directory service. You can optionally configure the user list of values for these fields by defining grants to restrict the partitions that appear in the first list and the users and roles that appear in the second list.
The Oracle Workflow pages that contain fields with the two-part user list of values include the following:
• Notification Details page - Any response fields for Respond attributes of type role
• Respond to Notifications as Group page - Any response fields for Respond attributes of type role
• Reassign Notifications page - Assignee field
• Request More Information page - Request More Information From: Any User field
• Grant Worklist Access page - Name field
• Vacation Rule: Response page - Assignee field and any response fields for Respond attributes of type role
• Administrator Vacation Rules page - User field
• Administrator Notifications search page - Owner field, To field, and From field
• Administrator Status Monitor: Workflows page - Workflow Owned By field and Waiting for Response From field
• Signature Evidence Store: Electronic Signature page - Requested Signer field
• Developer Studio: Run Workflow page - Workflow Owner field
If you define a grant that restricts the values in the Oracle Workflow user list of values, then that grant controls the values available to the grantee in all these pages and fields.
Note: As an exception, if a notification has the special
#WF_REASSIGN_LOV message attribute defined to specify the roles to whom the notification can be reassigned, then the
#WF_REASSIGN_LOV attribute overrides any grants for the list of users and roles. However, any grants for the list of partitions will still apply. In this case, when the recipient chooses to reassign the
notification, the Reassign Notifications page or Vacation Rule:
Response page displays the available partitions in the first list within the Assignee field. The second list within the Assignee field then displays the roles specified by the #WF_REASSIGN_LOV attribute value that are within the selected partition. See: #WF_REASSIGN_LOV Attribute, Oracle Workflow Developer's Guide.
See: Setting Up a Directory Service for Oracle Workflow, page 2-15.
To configure the user list of values:
Use the Functional Administrator responsibility to define grants that control which partitions and which users and roles appear in the Oracle Workflow user list of values.
Oracle Workflow provides seeded permissions, permission sets, objects, and object instance sets that you can use to define the grants you want.
For detailed information about defining grants and related tasks, see: Defining Data Security Policies, Oracle E-Business Suite System Administrator's Guide - Security, Object Instance Sets, Oracle E-Business Suite System Administrator's Guide - Security, Grants, Oracle E-Business Suite System Administrator's Guide - Security, and Assigning Permissions to Roles, Oracle E-Business Suite System Administrator's Guide - Security.
The following table lists the permission sets that Oracle Workflow provides for use in configuring the user list of values, as well as the permissions included in each
permission set.
Permission Sets and Permissions for the User List of Values
Permission Set Permission
Workflow Directory Partition Permission Set (WF_PARTITION_PSET)
Workflow Directory Partition Permission (WF_PARTITION_FN)
Workflow Role LOV Permission Set (WF_ROLE_LOV_PSET)
Workflow Role LOV Permission (WF_ROLE_LOV_FN)
The following table lists the objects that Oracle Workflow provides for use in
configuring the user list of values, as well as the generic object instance sets provided on those objects.
Objects and Object Instance Sets for the User List of Values
Object Object Instance Set Object Instance Set
Predicate
Workflow Directory Partition (WF_PARTITION)
Generic Partition Instance Set (WF_PARTITION_ISET)
Generic Role LOV Instance Set (WF_ROLE_LOV_ISET)
The Generic Partition Instance Set lets you grant access to specific originating system partitions in the Oracle Workflow directory service, up to a maximum of ten, which is
the maximum number of parameters you can specify for a grant. The Generic Role LOV Instance Set lets you grant access to specific roles in the Oracle Workflow directory service, up to a maximum of ten. You can specify the partitions or roles you want in the grant parameters when you define a grant using the corresponding instance set. The partitions to which you can grant access are:
• PER - Employee
• FND_USR - Oracle Applications User
• PQH_ROLE - Public Sector Employee
• HZ_PARTY - Trading Community Architecture Parties
• POS - Employee Position
• ENG_LIST - Engineering List
• GBX - Government Group Box
• HTB_SEC - Healthcare Security Group
• AMV_APPR - Marketing Approvals
• AMV_CHN - Marketing Channels
• FND_RESP - Oracle Applications Responsibility
• HZ_GROUP - Trading Community Architecture Groups
• UMX - User Management
• WF_LOCAL_ROLES - Ad Hoc Roles
For more information about partitions, see: Setting Up a Directory Service for Oracle Workflow, page 2-15.
Note: The list of partitions in the two-part user list of values can also include the value All Employees and Users (
VIRTUAL_ORIG_SYS). This value represents the PER, FND_USR, and PQH_ROLE originating system partitions, grouped together to make it easier to search for roles within any of these three partitions. However, you cannot use VIRTUAL_ORIG_SYS as a grant parameter to grant access to these partitions. You must grant access to each of the PER, FND_USR, and PQH_ROLE partitions directly.
If the generic instance sets do not meet your needs, you can also define your own
Note: When defining the security context information for your grants, ensure that you consider all the contexts in which the pages that contain the Oracle Workflow user list of values can be accessed. For example, other products might include Oracle Workflow pages in their responsibilities. Additionally, users might access the Worklist pages directly from the Oracle E-Business Suite home page by choosing the Full List button in a worklist region embedded in the home page, without selecting a responsibility. In this case no responsibility context is available, and grants defined at responsibility level will not take effect. To control the values that appear in the Oracle Workflow user list of values when no responsibility context is available, you can create a role, assign the users whose access you want to control to that role, and create a grant to that role through Oracle User Management.
Example 1
To grant access to specific originating system partitions in the Oracle Workflow directory service, create a grant using the Generic Partition Instance Set. First, specify appropriate security context information such as grantee and responsibility. Then specify the following data context information:
• Object - Workflow Directory Partition
• Data Context Type - Instance Set
• Instance Set - Generic Partition Instance Set
• Parameter 1 through Parameter 10 - The originating system partitions to which you want to grant access. You can specify one partition in each parameter, up to the maximum of ten. For example, to grant access only to the partition containing Oracle E-Business Suite users and the partition containing ad hoc roles, enter FND_USR for Parameter 1 and WF_LOCAL_ROLES for Parameter 2.
• Set - Workflow Directory Partition Permission Set Example 2
To grant access to specific roles in the Oracle Workflow directory service, create a grant using the Generic Role LOV Instance Set. First, specify appropriate security context information such as grantee and responsibility. Then specify the following data context information:
• Object - Workflow Role LOV
• Data Context Type - Instance Set
• Instance Set - Generic Role LOV Instance Set
• Parameter 1 through Parameter 10 - The roles to which you want to grant access.
You can specify one role in each parameter, up to the maximum of ten.
• Set - Workflow Role LOV Permission Set Example 3
To grant a supplier user access only to other supplier users from the same supplier, first create an instance set on the Workflow Role LOV object with a predicate that defines the available users. The following excerpt shows a sample predicate.
&TABLE_ALIAS.ORIG_SYSTEM = 'FND_USR' AND &TABLE_ALIAS.ORIG_SYSTEM_ID IN (SELECT PV2.FND_USER_ID
FROM PO_SUPPLIER_USERS_V PV2 WHERE PV2.PO_VENDOR_ID = (SELECT PV3.PO_VENDOR_ID
FROM PO_SUPPLIER_USERS_V PV3
WHERE PV3.FND_USER_ID = FND_GLOBAL.USER_ID))
Then, create a grant with the supplier user as the grantee. In the data context
information, specify the Workflow Role LOV object, the instance set you created, and the Workflow Role LOV Permission Set.
Example 4
To grant access only to Oracle E-Business Suite users who are not supplier users, first create an instance set on the Workflow Role LOV object with a predicate that defines the available users. The following excerpt shows a sample predicate.
&TABLE_ALIAS.ORIG_SYSTEM = 'FND_USR' AND &TABLE_ALIAS.ORIG_SYSTEM_ID NOT IN (SELECT PS.FND_USER_ID
FROM PO_SUPPLIER_USERS_V PS) AND NOT EXISTS
(SELECT 1
FROM PO_SUPPLIER_USERS_V PV1
WHERE PV1.FND_USER_ID = FND_GLOBAL.USER_ID)
Then, create a grant with appropriate security context information, such as grantee and responsibility. In the data context information, specify the Workflow Role LOV object, the instance set you created, and the Workflow Role LOV Permission Set.
Example 5
To grant access only to users who are employees, first create an instance set on the Workflow Role LOV object with a predicate that defines the available users. The following excerpt shows a sample predicate.
&TABLE_ALIAS.ORIG_SYSTEM = 'PER' AND &TABLE_ALIAS.PARTITION_ID = 1
Then, create a grant with appropriate security context information, such as grantee and responsibility. In the data context information, specify the Workflow Role LOV object, the instance set you created, and the Workflow Role LOV Permission Set.
Example 6
To grant access only to users who are not employees, not Oracle E-Business Suite users, and not supplier users, first create an instance set on the Workflow Role LOV object with a predicate that defines the available users. The following excerpt shows a sample
predicate.
&TABLE_ALIAS.ORIG_SYSTEM <> 'FND_USR' AND &TABLE_ALIAS.ORIG_SYSTEM <> 'PER'
Then, create a grant with appropriate security context information, such as grantee and responsibility. In the data context information, specify the Workflow Role LOV object, the instance set you created, and the Workflow Role LOV Permission Set.
To test a custom predicate
If you plan to create a custom instance set on the Workflow Role LOV object, you can test the predicate you will use for that instance set in a tool such as Oracle SQL
Developer, simulating how your logic will be executed within Oracle Workflow. In this way you can verify whether the predicate correctly filters the list of all roles to define the set of roles available to the grantee, before you define the instance set. You can also use this test for diagnostic or troubleshooting purposes if necessary.
Oracle Workflow provides two SQL templates that you can use to test a custom predicate in Oracle SQL Developer. The first SQL template lets you verify which roles your predicate makes available when the grantee chooses the "All Employees and Users" value in the first part of the two-part user list of values.
SELECT * FROM
(SELECT WU.NAME AS USER_NAME, WU.DISPLAY_NAME,
WDP.ORIG_SYSTEM, WU.EMAIL_ADDRESS, WU.ORIG_SYSTEM_ID, WU.PARTITION_ID FROM WF_ROLE_LOV_VL WU,
WF_DIRECTORY_PARTITIONS WDP
WHERE WU.PARTITION_ID = WDP.PARTITION_ID AND WU.PARTITION_ID <> 1
UNION ALL
SELECT WU.NAME AS USER_NAME, WU.DISPLAY_NAME,
WU.ORIG_SYSTEM, WU.EMAIL_ADDRESS, WU.ORIG_SYSTEM_ID, WU.PARTITION_ID FROM WF_ROLE_LOV_VL WU WHERE WU.PARTITION_ID = 1 ) QRSLT
WHERE (ORIG_SYSTEM IN ('FND_USR', 'PQH_ROLE', 'PER') AND (
-- Custom predicate goes here )
)
ORDER BY DISPLAY_NAME;
Insert your custom predicate at the indicated place in the template, removing any occurrences of &TABLE_ALIAS. For example, suppose you want to test the following predicate from example 3.
&TABLE_ALIAS.ORIG_SYSTEM = 'FND_USR' AND &TABLE_ALIAS.ORIG_SYSTEM_ID IN (SELECT PV2.FND_USER_ID
FROM PO_SUPPLIER_USERS_V PV2 WHERE PV2.PO_VENDOR_ID = (SELECT PV3.PO_VENDOR_ID
FROM PO_SUPPLIER_USERS_V PV3
WHERE PV3.FND_USER_ID = FND_GLOBAL.USER_ID))
For purposes of the test, you would remove the two occurrences of &TABLE_ALIAS from this predicate as follows:
ORIG_SYSTEM = 'FND_USR' AND ORIG_SYSTEM_ID IN (SELECT PV2.FND_USER_ID
FROM PO_SUPPLIER_USERS_V PV2 WHERE PV2.PO_VENDOR_ID = (SELECT PV3.PO_VENDOR_ID
FROM PO_SUPPLIER_USERS_V PV3
WHERE PV3.FND_USER_ID = FND_GLOBAL.USER_ID))
Note: The predicate in this example references FND_GLOBAL.USER_ID.
However, note that within Oracle SQL Developer,
FND_GLOBAL.USER_ID cannot be initialized correctly. For purposes of the test, you can replace FND_GLOBAL.USER_ID with the FND user ID of the user under whose login session you want to test the list of values.
The second SQL template lets you verify which roles your predicate makes available when the grantee chooses any specific partition - that is, any value other than All Employees and Users - in the first part of the two-part user list of values.
SELECT * FROM
(SELECT WU.NAME AS USER_NAME, WU.DISPLAY_NAME,
WDP.ORIG_SYSTEM, WU.EMAIL_ADDRESS, WU.ORIG_SYSTEM_ID, WU.PARTITION_ID FROM WF_ROLE_LOV_VL WU,
WF_DIRECTORY_PARTITIONS WDP
WHERE WU.PARTITION_ID = WDP.PARTITION_ID AND WU.PARTITION_ID <> 1
UNION ALL
SELECT WU.NAME AS USER_NAME, WU.DISPLAY_NAME,
WU.ORIG_SYSTEM, WU.EMAIL_ADDRESS, WU.ORIG_SYSTEM_ID, WU.PARTITION_ID FROM WF_ROLE_LOV_VL WU WHERE WU.PARTITION_ID = 1
Insert your custom predicate at the indicated place in the template, removing any occurrences of &TABLE_ALIAS. Then run the test and, when prompted for
PARTITION_CODE, enter the code for the partition you want to test. For example, to test how the predicate works when the grantee chooses Oracle Applications User in the first part of the two-part user list of values, enter FND_USR as the input for
PARTITION_CODE.
You can check the roles returned by your tests with these SQL templates to verify that your custom predicate will provide the intended access for the grantee.