--- Query Notification mail status is MAIL or INVALID
SELECT status, mail_status
FROM wf_notifications
WHERE notification_id = :P_notif_ID;
--- Recipient Role is a valid e-mail address and notification preference
SELECT name, email_address,
nvl(WF_PREF.get_pref(name, 'MAILTYPE'),notification_preference) preference
FROM wf_roles
WHERE upper(name) = upper(:p_recipient_role);
--- Ensure that this index is in place:
select index_name,column_name,column_position
from dba_ind_columns
where table_name='WF_NOTIFICATION_OUT' and
table_owner='APPLSYS';