Workflow Email Notifications not working for a user in Oracle Apps

Steps to  Troubleshoot:

1) Confirm the mail status of notifications sent to the user and they are in FAILED or SENT status.

select * from wf_notifications where recipient_role='HIMANSHU'

select name, display_name, notification_preference, email_address, orig_system
from wf_roles where name = '&enter user name';

2) Checked the user preferences from the FND_USER_PREFERENCES table with the following query

WHERE user_name LIKE '&user_name'
AND module_name = 'WF' ;

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

3) Please check whether the email address is correct or not in FND_USER, PER_ALL_PEOPLE_F

select * from PER_ALL_PEOPLE_F where first_name='Himanshu'

select * from fnd_user where user_name='HIMANSHU'

I have used SQL developer for running these so used *, you can select only limited columns as well.

4) Please check the user role in WF_LOCAL_ROLES table with the following query

WHERE name LIKE '&user_name';

Generally, only one role with ORIG_SYSTEM = PER must be active.

If more than one row are seen then disable the other.

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

5) Ran the “Synchronize WF LOCAL tables” concurrent program to sync all the roles.

If you like please follow and comment