when emails are struck and not send by workflow notification mailer perform the following.
1) Login into EBS applications and send a “TEST MAILER” to yourself to validate response of workflow notification mailer.
a) In the Service Components page for notification mailers in Oracle Applications Manager, select the Workflow Notification Mailer service component and click the View Details button > Click the Test Mailer button. Note: The test page lets you send a test e-mail to a workflow role.
b) In the Recipient Role field, select your user name from the list of values. Note: Only workflow roles with a notification preference set to receive e-mail and an e-mail address defined are included in the list of values for this field.
c) Click the Send Test Message button. In your preferred mail client, verify that the e-mail account associated with this e-mail address receives the test e-mail.
d) If you receive e-mail, then “workflow notification mailer” is working properly, else proceed with next step.
2) Login is as application owner (example:applmgr) and execute the below query which gives the count of struck emails (Stuck WF Notifications count). Take a note of it.
select ‘COUNT=’||count(*)
from APPLSYS.wf_notifications
where status = ‘OPEN’ and mail_status = ‘MAIL’ and BEGIN_DATE > sysdate – 10 and BEGIN_DATE < (sysdate – 2/24)
and USER_KEY is not Null;
3)The following query gives details of email Notifcaitions which were not send atleast by 2 hours. Take a note of it.
col WF_STATUS FORMAT A10
col E_MAIL_STATUS FORMAT A20
col PO_NUMBER FORMAT A15
col message_name FORMAT A25
col USER_KEY FORMAT A20
col notification_ID FORMAT 99999999
col RECIPIENT_ROLE FORMAT A25
col FROM_USER FORMAT A25
col BEGIN_DATE FORMAT A20
select nvl(USER_KEY,’None’) as PO_NUMBER,STATUS as WF_STATUS, decode(MAIL_STATUS,’MAIL’, ‘E-Mail Not Sent ‘, MAIL_STATUS) as E_MAIL_STATUS, RECIPIENT_ROLE,
nvl(FROM_USER,’None’) as FROM_USER, to_char(BEGIN_DATE, ‘DD-MON-YY HH24:MI’) as BEGIN_DATE , notification_ID as notification_ID , message_name
from APPLSYS.wf_notifications
where status = ‘OPEN’ and mail_status = ‘MAIL’ and BEGIN_DATE > sysdate – 10
— and BEGIN_DATE < (sysdate – 1/24)
and USER_KEY is not Null
order by BEGIN_DATE asc, USER_KEY asc , notification_ID asc ;
4) Bounce only “workflow notification mailer ” or ” Workflow mailer service container” based on the need.
5) Once again run step 2 and 3 to check whether the count had reduced.
6) Once again run step 1 (TEST MAILER) to check whether you are receiving TEST e-mail.
