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