Users Notification Preference Becomes Disabled In FND_USER_PREFERENCES Table

Lets understand why notification_preference for recipients to become DISABLED.

If your email server crashed and caused an outage of several hours then its caused the recipients notification_preference to change to DISABLED in the FND_USER_PREFERENCES and WF_LOCAL_ROLES tables.


We can find out  those user that are DISABLED using following sql statement in FND_USER_PREFERENCES .

SQL>select user_name,preference_value from fnd_user_preferences where PREFERENCE_VALUE = 'DISABLED';

SQL>select * from WF_LOCAL_ROLES where notification_preference in ('DISABLED','QUERY') and orig_system='PER';

SQL>select *from WF_LOCAL_ROLES where notification_preference='DISABLED';

To identify disabled user using below statement.

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

SQL>select *from WF_LOCAL_ROLES where notification_preference='DISABLED';

-----------Please ensure a backup is performed for recovery as needed. Apply in TEST instance before in PROD ---------

.

We need to update  FND_USER_PREFERENCES and WF_LOCAL_ROLES manually.

FND_USER_PREFERENCES for particular user and all user:- 

SQL>update FND_USER_PREFERENCES set PREFERENCE_VALUE = 'MAILHTML' where USER_NAME='SYSADMIN' and MODULE_NAME='WF' and PREFERENCE_NAME='MAILTYPE' ;

SQL>commit;


For all User:-

SQL>update WF_LOCAL_ROLES set notification_preference='MAILHTML' where notification_preference='DISABLED';

SQL>commit;


WF_LOCAL_ROLES for particular user and all user:- 

SQL>update WF_LOCAL_ROLES set notification_preference='MAILHTML' where notification_preference='DISABLED' and name='ISBENIWAL-I0001';

SQL>commit;


For all User:-

SQL>update WF_LOCAL_ROLES set notification_preference='MAILHTML' where notification_preference='DISABLED';

SQL>commit;


Another way to fix this issue:-

Set ‘General Preferences show flag’ profile at user level, so that it will enable the ‘Preference’ flag in user application.

Go to preference tab, Notifications, set email style to ‘HTML mail with attachments’.

If this is ‘Disabled’ or ‘Do not send me email’ users wont get notifications through mail.If you want to receive notification change it as 'HTML mail with attachments'.


Sometimes it is difficult to set it for every user.