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
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.
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.
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.
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 😉
After executing the update statement I ran “Synchronize WF LOCAL tables” concurrent program to sync all the roles.
I have created a sample requisition and email notifications have been sent to the user.