Notifications are not getting through Email

Hi,

Please do not do the following steps without consulting your Manager/DBA and these steps may vary from one requirement to other requirement.

If users are not getting emails even though they are getting e-business notifications, follow below steps:

Workaround 1:

Due to some reasons notification_preference in wf_roles and wf_local_roles table may update to DISABLED or QUERY. In this case workflow notifications wont go as an email attachment to the users.

In this case check the following queries and do workarounds 1,2 and 3.

To get default user preference set for an environment:

select PREFERENCE_VALUE
from FND_USER_PREFERENCES
where USER_NAME='-WF_DEFAULT-'
and MODULE_NAME='WF'
and PREFERENCE_NAME='MAILTYPE' ;

select * from wf_roles where notification_preference in ('DISABLED','QUERY')
and orig_system='PER'

select * from wf_local_roles
where notification_preference in ('DISABLED','QUERY')
and orig_system='PER'

UPDATE user preference value of a user using below API:

FND_PREFERENCE.put(p_user_name    => 'USER_NAME', -- User name should be in capital letters
                                 p_module_name   => l_module_name,
                                 p_pref_name    => l_pref_name,
                                 p_pref_value   => l_new_pref_value);



Remove/delete the user preference value of a user using below API:

FND_PREFERENCE.remove(p_user_name    => 'USER_NAME', -- User name should be in capital letters
                                            p_module_name   => l_module_name,
                                            p_pref_name    => l_pref_name); 
                   
-- Above API (FND_PREFERENCE.remove) is not setting default preference to notification_preference in wf_local_roles. So update wf_local_roles separately using below query
              
                  UPDATE wf_local_roles
                  SET NOTIFICATION_PREFERENCE = l_default_preference
                  WHERE NAMe =l_notify_user;


Below step is not required if we use FND_PREFERENCE API
Update the WF_LOCAL_ROLES table… It will intern updates the WF_ROLES table

update wf_local_roles
set notification_preference = 'MAILHTML'
where orig_system='PER'
and notification_preference in ('DISABLED','QUERY')


Workaround 2: 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 attachment’.
If this is ‘Disabled’ or ‘Do not send me email’ users wont get notifications through mail.

But it is difficult to set it for every user.


Workaround 3:

For all users
See Update statement below

select * from fnd_user_preferences where PREFERENCE_NAME='MAILTYPE' and MODULE_NAME = 'WF'


select distinct PREFERENCE_VALUE from fnd_user_preferences where PREFERENCE_VALUE  like '%MAI%'


select PREFERENCE_VALUE
from FND_USER_PREFERENCES
where USER_NAME='-WF_DEFAULT-'
and MODULE_NAME='WF'
and PREFERENCE_NAME='MAILTYPE' ;
-- If the result is ‘QUERY’ users are not getting notification through email



--Then update it to ‘MAILATTH’

update FND_USER_PREFERENCES
set PREFERENCE_VALUE = 'MAILATTH'
where USER_NAME='-WF_DEFAULT-'
and MODULE_NAME='WF'
and PREFERENCE_NAME='MAILTYPE' ;

Please follow below links to understand notification preferences:

http://docs.oracle.com/cd/B14099_19/integrate.1012/b12160/instal27.htm

HTML Summary Notification is a special type of user preference which sends all the notifications (triggered in a day) in a single email. This summary notification triggers once in a day. You can optionally use the notification mailer configuration wizard to modify the start time and interval for this event's schedule.

http://docs.oracle.com/cd/B13789_01/workflow.101/b10283/instal24.htm



To give updated rows in LOG 
Anonymous block to display updated rows:

DECLARE
 l_disabled_count NUMBER;
 l_updated_count  NUMBER;
BEGIN
 DBMS_OUTPUT.PUT_LINE('Program to display updated row count');
 DBMS_OUTPUT.PUT_LINE('**************************************');
    SELECT caount(*)
    INTO   l_disabled_count
    FROM fnd_user_preferences
    WHERE PREFERENCE_VALUE = 'DISABLED';
    dbms_output.put_line('Disabled Count :'||l_disabled_count);

     BEGIN
     update fnd_user_preferences
     set    preference_value = 'MAILHTML'
     where PREFERENCE_VALUE = 'DISABLED';
       
     dbms_output.put_line('Updated Row count: '||SQL%ROWCOUNT);
       COMMIT;
       
     EXCEPTION WHEN OTHERS THEN
     dbms_output.put_line('Error in Update - '||SUBSTR(SQLERRM,1,100));
     END;
       
 DBMS_OUTPUT.PUT_LINE('**************************************');

EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in Main - '||SUBSTR(SQLERRM,1,100));
END;




Thanks,
Rajesh