To configure the 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.
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.
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 and Assignee field for Transfer Request for More Information option
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
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
Administrator Request Information page - Request More Information From: Any User field and Assignee field for Transfer Request for More Information option
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 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.
See: Setting Up a Directory Service for Oracle Workflow.
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, Object Instance Sets, Grants, and Assigning Permissions to Roles.
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) | &TABLE_ALIAS.ORIG_SYSTEM IN (&GRANT_ALIAS.PARAMETER1, &GRANT_ALIAS.PARAMETER2, &GRANT_ALIAS.PARAMETER3, &GRANT_ALIAS.PARAMETER4, &GRANT_ALIAS.PARAMETER5, &GRANT_ALIAS.PARAMETER6, &GRANT_ALIAS.PARAMETER7, &GRANT_ALIAS.PARAMETER8, &GRANT_ALIAS.PARAMETER9, &GRANT_ALIAS.PARAMETER10) |
| Workflow Role LOV (WF_ROLE_LOV) | Generic Role LOV Instance Set (WF_ROLE_LOV_ISET) | &TABLE_ALIAS.USER_NAME IN (SELECT USER_NAME FROM WF_USER_ROLES WHERE ROLE_NAME IN (&GRANT_ALIAS.PARAMETER1, &GRANT_ALIAS.PARAMETER2, &GRANT_ALIAS.PARAMETER3, &GRANT_ALIAS.PARAMETER4, &GRANT_ALIAS.PARAMETER5, &GRANT_ALIAS.PARAMETER6, &GRANT_ALIAS.PARAMETER7, &GRANT_ALIAS.PARAMETER8, &GRANT_ALIAS.PARAMETER9, &GRANT_ALIAS.PARAMETER10)) |
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.
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 instance sets on these objects with predicates that define the access you want to grant.
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.
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
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
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 4To 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 5To 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 6To 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
) QRSLT
WHERE (ORIG_SYSTEM = '&PARTITION_CODE'
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. 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.