Notification Mailer Health Check

SQL> select decode(state, 0, '0 = Ready', 1, '1 = Delayed', 2, '2 = Retained', 3, '3 = Exception', to_char(state)) State, count(*) COUNT from wf_notification_out group by state;
SQL> select msg_state, count(*) from applsys.aq$wf_notification_out where msg_state in ('WAITING','READY', 'PROCESSED') group by msg_state; 

As long as "Retained" and "PROCESSED" are increasing, the mailer is functioning.

SQL> select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid, decode(wfe.state,0,'0 = Ready',1,'1 = Delayed',2,'2 = Retained', 3,'3 = Exception',to_char(substr(wfe.state,1,12))) State,count(*) COUNT from applsys.wf_deferred wfe group by wfe.corrid, wfe.state;

SQL> select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid, decode(wfe.state,0,'0 = Ready',1,'1 = Delayed',2,'2 = Retained',3,'3 = Exception',to_char(substr(wfe.state,1,12))) State,count(*) COUNT from applsys.wf_notification_out wfe group by wfe.corrid, wfe.state;

SQL> select status,mail_status,MESSAGE_type ,to_char(begin_date,'DD-MON-YY HH24:MI:SS'),due_date,END_DATE from apps.wf_notifications where trunc(begin_date) like '%19-AUG-08%'  and status='OPEN' and mail_status='SENT' order by begin_date;


Notification Mailer - Test Mail Verification

To Retrieve the Notification ID:
SQL> select max(notification_id) from wf_notifications where status='OPEN' and mail_status in ('MAIL', 'ERROR');

To Retrieve the Notification Mail Status:
SQL> select status,mail_status,begin_date from wf_notifications where notification_id='<Above_Query_Output>';

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")

Notification Mailer Override Address and Status

Set Override Address

Override address is set in test instance using "Workflow Administrator Web Application" responsibility in test instance so that all notification mails goes to a single mail ID.

How to Update Override Address from Backend?

select fscpv.parameter_value, fscpt.parameter_id from fnd_svc_comp_params_tl fscpt,fnd_svc_comp_param_vals fscpv
WHERE FSCPT.DISPLAY_NAME = 'Test Address' and fscpt.parameter_id = fscpv.parameter_id
    
UPDATE FND_SVC_COMP_PARAM_VALS SET PARAMETER_VALUE='mkhawas@nomail.com'
WHERE parameter_id=(select fscpv.parameter_id from fnd_svc_comp_params_tl fscpt, fnd_svc_comp_param_vals fscpv
WHERE FSCPT.DISPLAY_NAME = 'Test Address' AND FSCPT.PARAMETER_ID = FSCPV.PARAMETER_ID)

exec FND_SVC_COMP_PARAM_VALS_PKG.LOAD_ROW ( x_component_name => 'Workflow Notification Mailer', x_parameter_name => 'TEST_ADDRESS', x_parameter_value => 'mkhawas@nomail.com', x_customization_level => 'L', x_object_version_number => -1, x_owner => 'ORACLE' );
commit;


Update SMTP Server from Backend:

UPDATE FND_SVC_COMP_PARAM_VALS SET PARAMETER_VALUE='<<<<<SMTP Server IP or Host>>>>>>'
WHERE parameter_id IN (select fscpv.parameter_id from fnd_svc_comp_params_tl fscpt, fnd_svc_comp_param_vals fscpv
WHERE FSCPT.DISPLAY_NAME = 'Outbound Server Name' AND FSCPT.PARAMETER_ID = FSCPV.PARAMETER_ID);


How to check Notification Mailer is Up/Down from Backend:

select SC.COMPONENT_TYPE, SC.COMPONENT_NAME,
FND_SVC_COMPONENT.Get_Component_Status(SC.COMPONENT_NAME) COMPONENT_STATUS
from FND_SVC_COMPONENTS SC
order by 1, 2;

Cheers !!!!!