Previous	Next		Contents	Index	Glossary

Directory Service Views for Oracle Applications

• WF_USERS is based on WF_LOCAL_ROLES where the user flag is set to Y and on WF_LOCAL_ROLES_TL.

• WF_ROLES is based on WF_LOCAL_ROLES and on WF_LOCAL_ROLES_TL.

• WF_USER_ROLES is based on WF_LOCAL_USER_ROLES.

• WF_USER_ROLE_ASSIGNMENTS_V is based on WF_USER_ROLE_ASSIGNMENTS.

Note: You can customize your directory service by creating your own custom view definitions, provided that you define the required columns and map to the Workflow local tables. However, note that only the predefined directory service views provided by Oracle Workflow are supported by Oracle. See: Oracle Workflow Support Policy, Oracle Workflow Developer's Guide.

In Oracle Applications, the only roles in WF_LOCAL_ROLES that are marked as individual users with the user flag set to Y are roles that represent Oracle Applications users, originating from the FND_USER table, roles that represent Oracle Trading Community Architecture (TCA) person parties, roles that represent TCA contacts (relationship parties), or roles that represent ad hoc users. Records originating from other application tables are treated solely as roles, with the user flag set to N. The WF_LOCAL_USER_ROLES table is used to associate Oracle Applications users, TCA person parties, and TCA contacts with roles defined by other applications.

Note: An Oracle Applications user may be associated with an Oracle Human Resources person. In this case, some person information is combined into the user's record in WF_LOCAL_ROLES. In such a combined record, the originating system is changed from FND_USR to PER, and the display name is taken from Oracle Human Resources, while the internal name is the Oracle Applications user name from FND_USER, and the user flag is still set to Y.

Each Oracle Human Resources person is also represented in WF_LOCAL_ROLES as a role with the originating system PER_ROLE and the user flag set to N. This record remains unaffected whether the person is linked to an Oracle Applications user or not.

The following table summarizes the different ways in which Oracle Applications users and Oracle Human Resources people are stored in WF_LOCAL_ROLES.

Type of Role	Orig_System	User_Flag
Oracle Applications user, not linked to an Oracle Human Resources person	FND_USR	Y
Oracle Applications user linked to an Oracle Human Resources person	PER	Y
Oracle Human Resources person	PER_ROLE	N

To link an Oracle Applications user to an Oracle Human Resources person, navigate to the Users window in Oracle Applications and select the appropriate person name in the Person field for that user. See: Users Window, Oracle Applications System Administrator's Guide.

You should only link an Oracle Human Resources person to one Oracle Applications user. If a person is linked to more than one user, notifications for that person may become inaccessible, and workflow processes may be halted while waiting for those notifications to be completed. Additionally, assigning a person to multiple users may cause errors in other Oracle Applications modules as well. For this reason, you must not link an Oracle Human Resources person to more than one Oracle Applications user.

The WF_LOCAL_ROLES and WF_LOCAL_USER_ROLES tables are partitioned by the originating system within Oracle Applications that was the source of the denormalized information. This partitioning provides faster data access and also allows each originating system to be synchronized with the Workflow local tables individually. Each table also includes a separate partition that contains ad hoc users and roles as well as data from any system that does not have its own partition.

The partition information for each originating system is stored in the WF_DIRECTORY_PARTITIONS table. There are partitions for the following systems:

• WF_LOCAL_ROLES - Ad hoc users and roles, as well as data from any originating system that does not have its own partition

• FND_USR - FND users, which may or may not be linked to Oracle Human Resources people

• FND_RESP - FND responsibilities

• PER_ROLE - HR people

• POS - HR positions

• AMV_APPR - MarketView approvals

• AMV_CHN - MarketView channels

• ENG_LIST - Engineering approval list

• HZ_GROUP - TCA groups

• HZ_PARTY - TCA person parties and contacts

• GBX - Federal HR group boxes

• HTB_SEC - This partition does not participate in bulk synchronization.

• PQH_ROLES - Position Control roles

• UMX - User Management roles. This partition does not participate in bulk synchronization.

Note: Normally each partition contains only records that originate from the corresponding system. However, the FND_USR partition can contain both roles with an orig_system value of FND_USR, which are unlinked Oracle Applications users, and roles with an orig_system value of PER, which are Oracle Applications users that are linked to Oracle Human Resources people.

See: Ad Hoc Users and Roles, Oracle Workflow Developer's Guide.

You can run a diagnostic test through Oracle Diagnostics to check that there are no duplicate roles in the WF_LOCAL_ROLES table. See: Oracle Workflow Diagnostic Tests.

Synchronizing Workflow User and Role Information

Incremental Synchronization

Bulk Synchronization

Oracle Workflow provides a request set named Synchronize Workflow LOCAL Tables that contains ten instances of the Synchronize WF Local Tables program, one for each originating system. You can use this request set to submit requests for all the originating systems at once. Note that because this program is incompatible with itself, each request is defined as a separate stage and the stages will run sequentially. By default, this request set is scheduled to run once a day to provide a minimal level of synchronization. You can modify the schedule for the request set to perform synchronization more frequently.

You only need to run the bulk synchronization program for products for which you do not have an incremental synchronization patch installed. After applying the patch for a product, you no longer need to run the program for originating systems owned by that product.

• After applying the patch for a product, remove the program instance for each of that product's originating systems from the Synchronize Workflow LOCAL Tables request set. See: Defining Request Sets, Oracle Applications System Administrator's Guide.

• Also, do not submit any further single requests for the Synchronize WF LOCAL Tables program for that product's originating systems.

Note: You can still use the bulk synchronization program to synchronize the product's data for troubleshooting and diagnostic purposes, if necessary.

Note: Products that use role hierarchies do not participate in bulk synchronization. These products must perform incremental synchronization.

   To submit the Synchronize Workflow LOCAL Tables request set:

1. Navigate to the Submit Requests form in Oracle Applications (System Administrator: Requests > Run). See: Running Reports and Programs, Oracle Applications User's Guide.

2. Choose to run a request set and select Synchronize Workflow LOCAL Tables as the request set to run.

3. Enter the values you want for the following parameters.

• Parallel Processes - Enter the number of parallel processes to run. The default value for this parameter is 1. However, if your hardware resources allow, you can optionally set this parameter to a higher value in order to parallelize the queries during execution of the program.

• Logging - Select the logging mode you want. This mode determines whether redo log data is generated for database operations performed by the bulk synchronization process. The default value for this parameter is LOGGING, which generates redo log data normally. You can optionally set the logging mode to NOLOGGING to suppress redo log data, obtaining a performance gain. Without this redo log data, no media recovery is possible for the Workflow directory tables and indexes, requiring that you re-run the bulk synchronization process in the event of a media recovery scenario. One additional scenario that would require the process to be re-run is when you open a standby database, whose maintenance also depends on redo log data.

4. Select the print and run options you want to define the schedule for this request set, and choose Submit to submit the requests.

   To submit a single request for the Synchronize WF LOCAL Tables concurrent program:

1. Navigate to the Submit Requests form in Oracle Applications (System Administrator: Requests > Run). See: Running Reports and Programs, Oracle Applications User's Guide.

2. Choose to run a single request and select the Synchronize WF LOCAL Tables concurrent program as the request to run.

3. In the Parameters window, enter the values you want for the following parameters:

• Orig System - Select the name of the originating system whose user and role information you want to synchronize with the WF_LOCAL tables. These system names are stored in the FND_WF_ORIG_SYSTEMS lookup type. You can select the following systems:

• AMV_APPR - MarketView Approvals

• AMV_CHN - MarketView Channels

• ENG_LIST - Engineering Approval List

• FND_RESP - FND Responsibilities

• FND_USR - FND Users

• GBX - Federal HR Group Boxes

• HZ_GROUP - TCA Groups

• HZ_PARTY - TCA Parties

• PER_ROLE - HR People

• POS - HR Positions

• PQH_ROLES - Position Control Roles

• Parallel Processes - Enter the number of parallel processes to run. The default value for this parameter is 1. However, if your hardware resources allow, you can optionally set this parameter to a higher value in order to parallelize the queries during execution of the program.

• Logging - Select the logging mode you want. This mode determines whether redo log data is generated for database operations performed by the bulk synchronization process. The default value for this parameter is LOGGING, which generates redo log data normally. You can optionally set the logging mode to NOLOGGING to suppress redo log data, obtaining a performance gain. Without this redo log data, no media recovery is possible for the Workflow directory tables and indexes, requiring that you re-run the bulk synchronization process in the event of a media recovery scenario. One additional scenario that would require the process to be re-run is when you open a standby database, whose maintenance also depends on redo log data.

4. Select the print and run options you want to define the schedule for this request, and choose Submit to submit the request. You can submit multiple requests for this program to perform synchronization for different originating systems at different frequencies. However, note that because this program is incompatible with itself, only one request for the program can run at a time.

Note: Additionally, you must not run bulk synchronization using APIs or scripts from SQL*Plus while you are running the Synchronize WF LOCAL Tables concurrent program or the Synchronize Workflow LOCAL Tables request set, as the two processes will interfere with each other.

How Bulk Synchronization Is Performed

Note: Originating systems that use role hierarchies do not participate in bulk synchronization. These originating systems must perform incremental synchronization.

For backward compatibility, the originating systems' synchronization views must present exactly the same user and role information that was included for that system in the previous implementation of the Workflow directory service views. The information must be presented in the format required by Oracle Workflow, with no duplicates. For example, the internal name for a user or role must be sourced from a column that is no longer than 320 characters. It is recommended that internal names be all uppercase. If the source table in the originating system does not have a column that meets these criteria, the internal name should be defined to be <orig_system>:<orig_system_id> instead, so that Oracle Workflow can reference the original base table where users or roles are stored and a unique user or role in that table.

Note: If internal names in all uppercase are used, the names should be initially entered in the database in all uppercase. Forcing the names to uppercase in the view definition results in poor performance when accessing these views.

Note: You can customize these originating system synchronization view definitions to specify the data you want to include in bulk synchronization, provided that your customized views meet the requirements listed above. However, note that the originating systems that have implemented incremental synchronization will also be propagating user and role information to the Workflow local tables automatically, so the synchronization views used for bulk synchronization are not the only source of data for Oracle Workflow. Also note that only the predefined synchronization views provided by Oracle Applications are supported by Oracle. See: Oracle Workflow Support Policy, Oracle Workflow Developer's Guide.

When you run the bulk synchronization program for a particular originating system, the program extracts the role and user/role association information from that system's synchronization views and loads the information into staging tables. The program then performs a partition exchange between the staging tables and the WF_LOCAL_ROLES, WF_LOCAL_ROLES_TL, WF_LOCAL_USER_ROLES, and WF_USER_ROLE_ASSIGNMENTS tables to update the partitions for that system in the Workflow local tables. Finally, the staging tables are truncated.

Note: The bulk synchronization program does not store or modify any information in the WF_LOCAL_ROLES partitions within the WF_LOCAL_ROLES and WF_LOCAL_USER_ROLES tables that contain ad hoc users and roles.

See: Setting Up an Oracle Workflow Directory Service

Role Hierarchies

For example, a company could define a role hierarchy with three roles: sales manager, sales representative, and employee. A user with the sales manager role automatically inherits the sales representative role, and a user with the sales representative role automatically inherits the employee role. If user A is assigned directly to the sales representative role, then user A will also have an inherited assignment to the employee role. If user B is assigned directly to the sales manager role, user B will also have inherited assignments to both the sales representative role and the employee role.

Oracle Workflow stores hierarchical relationships between roles in the WF_ROLE_HIERARCHIES table. Oracle Workflow also stores denormalized information about direct and inherited assignments of users to roles in the WF_USER_ROLE_ASSIGNMENTS table for performance gain. If a user is associated with a certain role through more than one direct or inherited assignment, the WF_USER_ROLE_ASSIGNMENTS table tracks which assignments are currently valid and expires the user/role association only when all assignments have ended.

Note: Originating systems that use role hierarchies do not participate in bulk synchronization. These originating systems must perform incremental synchronization.