Workflow Mailer Torubleshooting - Part-3

When the workflow mailer is either disabled or the mail server restricts its access to the workflow mailer, all the workflow notifications will retry and upto the limit set at the workflow mailer configuration and later all the notifications will be failed. This will result in 2 things at Application level

i) MAIL_STATUS column of the WF_NOTIFICATIONS set to FAILED. 

ii) After a series of failure notifications the Notification Style for the respected User will be set as Disabled and a corresponding user details are sent to SYSADMIN.

A similar issue araised in one of the client and the many of the users notification style has been set to Disabled.

Solution:

To overcome the above issue, initially fix the workflow mailer issue and later need to change the Notification Preference at Workflow Administrator to "HTML with Attachments" (or any other as per the client's requirement).

But sometime this setting won't be affected to all the users whose Preference is set to Disabled. To overcome this situation:

i) Backup the following tables:

CREATE FND_USER_PREFERENCES_BAK as select * from FND_USER_PREFERENCES;

CREATE WF_LOCAL_ROLES_BAK as select * from WF_LOCAL_ROLES;

ii) To set the mail preference for all the users execute the following:

update wf_local_roles
set notification_preference='<wished_preference>'
where orig_system in ('FND_USR','PER');

update fnd_user_preferences
set preference_value='<wished_preference>'
where preference_name='MAILTYPE'
and module_name='WF'
and user_name <> '-WF_DEFAULT-'; 

iii) To update the mail preference of the users only whose preference is set to "FAILED", execute the below steps:

 update wf_local_roles
set notification_preference='<wished_preference>'
where orig_system in ('FND_USR','PER')
and name in
(select user_name
from fnd_user_preferences
where preference_name='MAILTYPE'
and module_name='WF'
and preference_value='DISABLED');

update fnd_user_preferences
set preference_value='<wished_preference>'
where preference_name='MAILTYPE'
and module_name='WF'
and preference_value='DISABLED';

Possible values for <wished_preference> are:

QUERY (corresponds to preference value "Do not send me mail")
MAILTEXT (corresponds to preference value "Plain text mail")
MAILATTH (corresponds to preference value "Plain text mail with HTML attachments")
MAILHTML (corresponds to preference value "HTML mail with attachments")
MAILHTM2 (corresponds to preference value "HTML mail")
SUMMARY (corresponds to preference value "Plain text summary mail")
SUMHTML (corresponds to preference value "HTML summary mail")
DISABLED (corresponds to preference value "Disabled")

iii) To Resend all the failed notifications again to the respected users run the following concurrent program as SYSADMIN, with the relevant parameters.

Retry Errored Workflow Activities