Notification Mailer User Preference

Change the notification preference for One User:

exec FND_PREFERENCE.put('&User', 'WF', 'MAILTYPE', 'QUERY');
COMMIT;

How to update User's Email Preference to MAILHTML for all or bulk?

Unfortunately, currently there is no seeded way to do this. An enhancement request has been logged for this under Bug 5748131 (NEED PLEASANT WAY TO BULK RESET NOTIFICATION PREFERENCE FROM DISABLED). It is under Oracle Development's review

Currently, the only workaround to change the Email Style of all users is to update the tables. Note that there are 2 tables to update : FND_USER_PREFERENCES and WF_LOCAL_ROLES.

NB:You should backup those tables before performing the updates.

Updates for All Users would look like :

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-'; 

Updates for Users having the preference set to "Disabled" would look like :

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") 
SUMHTL (corresponds to preference value "HTML summary mail") 
DISABLED (corresponds to preference value "Disabled")