Some times, users do not get emails/alerts from EBS R12.1.3 even workflow Mailer is up and running. Table wf_notifications may give different status:
RECIPIENT_ROLE MESSAGE_TYPE STATUS MAIL_STATUS TO_USER
APINVGR APINVAPR OPEN FAILED AP GROUP
APINVGR APINVAPR OPEN MAIL AP GROUP
APINVGR APINVAPR OPEN SENT AP GROUP
APINVGR APINVAPR CLOSED SENT AP GROUP
Seems to me that only mails with MAIL_STATUS "SENT" were sent out successfully. If it stays in "MAIL", the Mailer may get stuck somewhere and may need a re-start.
Below
query checks the email status:
SELECT notification_id N_ID, to_char(begin_date, 'mm-dd-yyyy hh24:mi:ss') beginDate, recipient_role, message_type, message_name, status, mail_status, context, to_user, subject
FROM wf_notifications
WHERE begin_date > sysdate - 5 -- List only emails within 5 days
ORDER by notification_id desc;
Here is a notification with FAILED status. Note: the record values could be dynamically changed with approval workflow's move.
N_ID RECIPIENT_ROLE MESSAGE_TYPE STATUS MAIL_STATUS CONTEXT TO_USER
15331513 APINVGR APINVAPR OPEN FAILED APINVAPR:11345495_1:116020 AP GROUP
To troubleshoot the failed notification, find and check the Workflow status first to see if any error.
You shall verify user
APINVGR status and email address are correct.
Also try the Mailer logs in $APPLCSF/$APPLLOG to see if you are lucky to get some errors.
$ ls -lrt $APPLCSF/$APPLLOG/FNDCP*.txt
$ grep ":ERROR:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrerr.log
$ grep "Exception:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrexc.log
$ grep ":UNEXPECTED:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrunexp.log
If the logs do not tell much, Doc ID 1051421.1 is a good document to follow on enabling debug for Mailer Service Component :
- In OAM : Site Map (or, WF Manager) >
Notification Mailer > Edit > Advanced > Go to Step 2 > Change "Log Level" to "Statement" > Apply
-
Bounce the Mailer container to generate a fresh log file
In OAM: Site Map > Generic Services > under Status Overview > Next (twice) >
Generic Service Component Container > select
Workflow Mailer Service > Restart (from the dropdown on the top).
(or, Dashboard > dropdown "Application Services" Go > Next ... to find Generic Service Component Container > ... )
Note it may take 5 minutes or so for a re-start cycle.
Even Workflow Mailer Service is running, Notification Mailer could be down if two $CONTEXT_FILE parameters are not right, because autoconfig will overwrite them if company's SMTP server name, e.g. mail.comany_name.com, is only entered under Outpound EMail Account section on OAM webpage. The correct way is to define them in $CONTEXT_FILE:
<oa_smtp_server>
<hostname oa_var="s_smtphost">
mail</hostname>
<domain oa_var="s_smtpdomainname">
company_name.com</domain>
</oa_smtp_server>
If individual users do not receive notifications or their mails have Failed status, check two tables to see if their preference is turned off (
disabled)which could happen when the Workflow Mailer attempts to send a notification to an invalid email address or when email server crashed and caused an outage of hours (Doc ID 360541.1).
SQL> select * from FND_USER_PREFERENCES where preference_value = 'DISABLED';
SQL> select * from WF_LOCAL_ROLES where notification_preference = 'DISABLED';
To enable it in
FND_USER_PREFERENCES: log onto EBS as user who has the issue > click on Preferences > scroll down to Notifications > Email Style > dropdown "HTML mail with attachments"
To populate above change to
WF_LOCAL_ROLES and
WF_ROLES:
as System Administrator Responsibility > Define User form> query up user with issue:
a. Remove the Person Field > save.
b. Re-query the same user > add back the Person Field > Save
c. This effort does an internal synchronization for the particular user.
Notes on
synchronizing workflow tables:
1. In my R12.1.3 instances, above actions synchronizes
Status column in tables FND_USER and WF_LOCAL_ROLES. Doc 1213304.1 has info on three sync concurrent programs. In 11i, the synchronization seems more difficult. See Doc 728331.1 and Doc 364647.1.
2. Two steps are necessary to change a user's
email address and synchronize it to workflow tables. Prior to about steps on Define User form, go to Setup (mostly under a Purchase/PO responsibility) > Personal > Employee form > update user's email address first.
3. If the
Description column is out-of-date in table wf_local_roles, most likely newer data in table PER_ALL_PEOPLE_F is not populated to WF tables. The solution is to run concurrent program 'Synchronize WF LOCAL tables' with parameter FND_USR. That synchronized WF tables in my instances.
After this, run concurrent program Resend Failed/Error Workflow Notifications to resend those FAILED notifications.
This program should send FAILED records out from table wf_notifications. In my case, account APINVGR uses a service email address and each time after Mailer sends a notification to it, its notification_preference becomes DISABLED for unknown reason. After I replace that email address with a real user's email address, the problem is resolved.
If Workflow Mailer stopped, workflow notification will not be sent. Below query shows Workflow Mailer is not running, it needs a fix:
SQL> SELECT component_name, component_status, component_status_info
FROM fnd_svc_components_v
WHERE component_name like 'Workflow%';
component_name component_status component_status_info
------------------------------------------------------------- ----------------------- --------------
Workflow Deferred Agent Listener RUNNING
Workflow Deferred Notification Agent Listener RUNNING
Workflow Error Agent Listener RUNNING
Workflow Inbound JMS Agent Listener STOPPED
Workflow Inbound Notifications Agent Listener RUNNING
Workflow Java Deferred Agent Listener RUNNING
Workflow Java Error Agent Listener RUNNING
Workflow Notification Mailer DEACTIVATED_SYSTEM xxxxx
Doc ID 562551.1 has useful queries. If you want to check into an individual notification, get the NOTIFICATION_ID from above query result and run below quest to see its status from tables WF_NOTIFICATIONS, WF_DEFERRED and WF_NOTIFICATION_OUT.
NOTE: a Failed notification will not reach WF_NOTIFICATION_OUT table
SQL> SELECT
n.begin_date, n.status, n.mail_status, n.recipient_role, de.def_enq_time, de.def_deq_time,
de.def_state, ou.out_enq_time, ou.out_deq_time, ou.out_state
FROM applsys.wf_notifications n,
(SELECT d.enq_time def_enq_time,
d.deq_time def_deq_time,
TO_NUMBER((SELECT VALUE
FROM TABLE(d.user_data.parameter_list)
WHERE NAME = 'NOTIFICATION_ID')) d_notification_id,
msg_state def_state
FROM applsys.aq$wf_deferred d
WHERE d.corr_id = 'APPS:oracle.apps.wf.notification.send') de,
(SELECT o.deq_time out_deq_time,
o.enq_time out_enq_time,
TO_NUMBER((SELECT str_value
FROM TABLE(o.user_data.header.properties)
WHERE NAME = 'NOTIFICATION_ID')) o_notification_id,
msg_state out_state
FROM applsys.aq$wf_notification_out o) ou
WHERE n.notification_id =
15331600
AND n.notification_id = de.d_notification_id(+)
AND n.notification_id = ou.o_notification_id(+) ;
Check if there are stuck messages in The PROCESS Folder:
SQL> SELECT trunc(win.enq_time) PROCESSED_TIME, wfn.status NOTIFICATION_STATUS, COUNT(*) NO_EMAILS
FROM (select wi.msgid, wi.corrid, wi.enq_time, wi.state, wi.sender_name,
(select str_value from table(wi.user_data.header.properties) where name = 'NOTIFICATION_ID') notification_id
from apps.WF_NOTIFICATION_IN wi) win, apps.WF_ERROR werr, apps.wf_notifications wfn
where win.notification_id = wfn.notification_id
and win.msgid = werr.msgid (+)
--and win.enq_time >= sysdate - 4/24 -- In the Process Folder for more than 4 hours
and wfn.status = 'OPEN'
group by trunc(win.enq_time), wfn.status;
