Oracle Apps r12 User Guides

Workflow Roles and User/Person Synchronization

By Jag - September 05, 2012
I have recently had the need to look in detail at how workflow synchronises role information with Oracle HRMS in an R12 instance and wanted to publish my findings as there is not too much information out there about how this works in one place, please leave me a comment if you find any inaccuracies in this article and I will review the content. This article assumes you have some background knowledge regarding workflow notifications and the concept of a workflow role.
From R12 onwards incremental role synchronisation is a standard feature in Oracle HRMS, two views are provided called FND_USR_ROLES and PER_ROLE_ROLES_V which can be used to see what information workflow uses to populate the WF_LOCAL_ROLES table.
What is Incremental and Bulk Synchronisation?
Bulk synchronisation is the process of updating roles in the workflow table using a concurrent program called “Synchronize WF LOCAL Tables”, this program will take information from views delivered by different EBS modules and populate the WF_LOCAL_ROLES table using this information. This job can be run for individual or all modules and for the context of this article the process would be run for the HRMS module or FND module which would synchronise information from the user form or Person form.
Incremental Synchronisation is a term used to describe the process of an Oracle module updating the workflow roles in real-time as the information is changed in that module, for example if I create a new user record the information for that record will be automatically populated in WF_LOCAL_ROLES without the need to run Bulk Synchronization. Incremental Syncing is enabled as default in R12 for Oracle HRMS and the Bulk Sync concurrent program is provided for backwards compatibility and should not need to be run.
Incremental Syncing is standard in R12 however it is also available in R11 via relevant patches, see metalink note 171703.1
Where are role details taken from when a person record is linked to a user record?
Two roles can exist in workflow for a user/employee in EBS.
Internal Employee Role
At the point that the person record is created an additional person role will be created in wf_local_roles. The role name will be along the lines “PER_ROLE:771703” (where 771703 indicates the person id from per_all_people_f) with all details such as e-mail address, display name, description being populated from the person record and in addition user preferences are now used to populate Notification Preference, Language and territory from FND_USER_PREFERENCES. The incremental synchronisation role that is used to populate WF_LOCAL_ROLES is PER_ROLE_ROLES_V.
A person record with a valid internal role can be identified with the following SQL:
SELECT * FROM wf_local_roles WHERE name =’PER_ROLE:’||:person_id and orig_system = ‘PER_ROLE’
N.B A PER_ROLE role is created regardless of if a user account is linked to the employee.
User Role with/without person record linked
This is one role that is maintained at a user level and dependant on if you have an employee linked to the user record or not the role will source its email, description and preferences information differently. The role name will be the user name held in the FND_USER table.
The view FND_USR_ROLES is used to populate role information to the user/employee role in the event that your user is linked to a person record or not. If the record is not linked to a person record the view simply takes the information including the e-mail address from the FND_USER table. In this situation certain information such as Notification Preferences, Language and Territory are defaulted in this instance to “MAILHTML’, ‘AMERICAN’ and ‘AMERICA’ respectively. This results in the role being automatically eligible to receive e-mail based notifications for processes such as the email reset function where an email can be sent to a user account based role with a link to reset their password. A user only based role can be identified in the workflow tables using the following SQL:
SELECT * FROM wf_local_roles where name = :fnd_user_name and orig_system = ‘FND_USR’
In the event that a user record is linked to a person record using the user form the following happens:
1. The email address is blanked out from the user form
2. The FND_USR role is updated with the information from the existing PER_ROLE role that belongs to the linked employee and already exists in the wf_local_roles table (See Internal Employee Role above).
3. The orig_system partition in WF_LOCAL_ROLES is updated to PER
From this point the PER role and the PER_ROLE role are synchronised and any workflow notifications sent to the role associated with the user account i.e. user from FND_USER will use the details copied from the PER_ROLE role which are originally captured from the person record such as email address and the user record in FND_USER will no longer be referenced, it could be argued that since a new person role is created (PER_ROLE) which already sources its details from the person form why does the standard FND user account role (FND_USR) also have to be switched to a (PER) role and source its details from the same location, it would make sense to have one user level role and one person level roll? This is an annoyance to some customers of Oracle who very often have to migrate their email details over from the user form to the person form when implementing HRMS.
A user with associated employee record can be identified in the workflow tables using the following SQL:
SELECT * FROM wf_local_roles WHERE name =:fnd_user_name and orig_system = ‘PER’
Workflow Notification Behaviour with standard and ADHOC Roles
Workflow notifications can be sent to standard or adhoc roles. Standard roles are roles that are maintained in embedded workflow out of the box using incremental or batch synchronisation. Standard roles are maintained for a variety of modules but for the context of this article roles are created and maintained for users, linked users (User linked to a HRMS record) and HRMS people (See previous section). The 3 types can be referenced as FND, PER and PER_ROLE. The FND role becomes a PER role at the point the user records are linked. This partition can be found in the WF_LOCAL_ROLES table in the “ORIG_SYSTEM” column.
Adhoc roles are roles that have been created using the workflow API’s at run time by a program or process. An adhoc role will have an ORIG_SYSTEM of “WF_LOCAL_ROLES”. The actual role name can vary dependant on the process.
To demonstrate how standard and adhoc roles are used by a workflow process we will look at the “Password Reset” process that is accessible in R12 from the login screen link “Login Assistance”, we will examine how the associated workflow process selects the correct role to send the notification to and how the roles have been populated based on the setup.
By default the reset password workflow will try to send the notification to the actual user role, that is the role with the same name as the user requesting the reset.
Scenario 1 – User account exists but no person record has been linked.
In the event that no email address exists on the user account then an error will be displayed when the user tries to request a reminder email:
“Your email address is either invalid or an account matching the entered email address could not be found. Please contact the System Administrator for further assistance.”
If the email address is populated the password reset workflow will select the role based on the user account so it will identify the role name as “TURLEYK” (My username in “FND_USER”), the email and description will be taken from the FND_USER table and populated to WF_LOCAL_ROLES with “FND_USR” in the ORIG_SYSTEM column. By default the email preference is set to HTML.
Scenario 2 – User account has been linked but no email address has been entered on the person record.
In the event that no email address exists on the user account as well as the person record then an error will be displayed when the user tries to request a reminder email:
“Your email address is either invalid or an account matching the entered email address could not be found. Please contact the System Administrator for further assistance.”
If an e-mail address does exist on the user account record (PER role due to the fact that it is linked to a person record) but not on the person record then something unusual happens here. As mentioned previously once a user account is linked to a person account the email address is sourced from the person record, but in this instance the person record email is not populated so workflow does not have a standard role with an email address on despite the fact that the user record has a valid email address populated.
In this situation the email reset workflow process is intelligent enough to know that an email address exists on the FND_USER table for the user name but no standard role exists with the email address synced. The result is that the workflow will build an adhoc role called FNDPWD__ and store it in wf_local_roles using the orig_system of “WF_LOCAL_ROLES”. This role will be populated with the e-mail address from fnd_user and the notification will be sent to this adhoc role.
Scenario 3 – User account has been linked and email address has been entered on the person record.
In this instance the password reset workflow will select the role based on the user account so it will identify the role name as “TURLEYK” (My username in “FND_USER”), the email and description on this role will be taken from the PER_ALL_PEOPLE_F table via the PER_ROLE_ROLES_V view and populated to WF_LOCAL_ROLES with “PER_ROLE” in the ORIG_SYSTEM column by incremental synchronization.
The above examples should give you an idea of how workflow processes can use roles to send notifications and how those roles are maintained. The behaviour will vary between workflow processes but this article should give you the knowledge to recognise how roles are used and thus how to implement them in your workflow processes.
  • Share:

You Might Also Like

1 comments