Workflow Email Notifications not working for a particular user

I faced this issue on Oracle Apps R12.1.2 where in a user is not receiving email notifications.

To confirm whether the problem exists or not I have checked the mail status of notifications sent to the user and they are in FAILED status.

here are the steps which I have followed to resolve the issue

STEP 1:

Firstly I have checked the user preferences from FND_USER_PREFERENCES table with the following query

SELECT *
FROM FND_USER_PREFERENCES
WHERE user_name LIKE '&user_name'
AND module_name = 'WF' ;

I could see that preference value is defined for MAILTYPE preference.

STEP 2:

I have checked whether email address is correct or not in FND_USER, PER_ALL_PEOPLE_F and FND_USERS tables.

Email address is all the above three tables are valid and correct.

STEP 3:

Next I have checked the user role in WF_LOCAL_ROLES table with the following query

SELECT * FROM WF_LOCAL_ROLES
WHERE name LIKE '&user_name';

I got two records with different ORIG_SYSTEM values, interestingly both roles are active but generally only one role with ORIG_SYSTEM = PER must be active.

here is the screenshot of the output which shows where the real problem is

In the screenshot you can see that multiple roles are active for a same user.

STEP 4:

I tried to resolve the issue by Inactivating the role which is with ORIG_SYSTEM = WF_LOCAL_USERS.

I couldn’t find a perfect API to INACTIVE a specific role of a user, so I wrote an UPDATE statement and inactivated the role.

here is the UPDATE statement

UPDATE wf_local_roles
SET STATUS        ='INACTIVE' ,
  expiration_date = (sysdate-1)
WHERE name LIKE '&user_name'
AND parent_orig_system      ='WF_LOCAL_USERS'
AND notification_preference ='DISABLED';

NOTE: Take backup of WF_LOCAL_ROLES table before you update and also try this in one of your TEST Instance before you apply it in PROD Instance. Do it at your own risk 😉

STEP 5:

After executing the update statement I ran “Synchronize WF LOCAL tables” concurrent program to sync all the roles.

TESTING:

I have created a sample requisition and email notifications have been sent to the user.