Oracle Ebusiness Suite
(EBS) Workflow Mailer Troubleshooting
Check EBS workflow
mailer service current status
========================================
SQL> select
running_processes from apps.fnd_concurrent_queues where concurrent_queue_name =
'WFMLRSVC';
Number of running processes should be greater than 0
Current mailer status
=================
SQL> select
component_status from apps.fnd_svc_components where component_id = (select
component_id from apps.fnd_svc_components where component_name = 'Workflow
Notification Mailer');
Possible values:
RUNNING
STARTING
STOPPED_ERROR
DEACTIVATED_USER
DEACTIVATED_SYSTEM
Stop notification mailer
===================
SQL>
declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
-- Find mailer Id
-----------------
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
--------------
-- Stop Mailer
--------------
fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);
commit;
end;
/
Start notification mailer
===================
SQL>
declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
-- Find mailer Id
-----------------
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
--------------
-- Start Mailer
--------------
fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
commit;
end;
/
A workflow notification send event (notification email) can fail at several
different points, so monitoring it using one method usually is not going to
give you a complete picture.Additionally, you have to keep in mind that the
process is dynamic, meaning that as transactions are created into the queues
they are also mailed out; so a
count of data is at
best only a snapshot of a particular moment in time.
Here is a more robust script for monitoring the wf_notifications table:
select message_type,
mail_status, count(*) from wf_notifications
where status = 'OPEN'
GROUP BY MESSAGE_TYPE,
MAIL_STATUS
messages in 'FAILED'
status can be resent using the concurrent request 'resend failed workflow
notificaitons'
messages which are
OPEN but where mail_status is null have a missing email address for the
recipient, but the notification preference is 'send me mail'
Some messages like alerts don't get a record in wf_notifications table so
you have to watch the WF_NOTIFICATION_OUT queue.
select corr_id, retry_count, msg_state, count(*)
from
applsys.aq$wf_notification_out
group by corr_id,
msg_state, retry_count
order by count(*)
desc;
Messages with a high
retry count have been cycling through the queue and are not passed to smtp
service.Messages which are 'expired' can be rebuilt using the wfntfqup.sql
SQL to collect all the info except IMAP account password.
===============================================
select p.parameter_id,
p.parameter_name,
v.parameter_value
value
from
fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b
p,
fnd_svc_components c
where c.component_type
= ‘WF_MAILER’
and v.component_id =
c.component_id
and v.parameter_id =
p.parameter_id
and p.parameter_name
in (‘OUTBOUND_SERVER’, ‘INBOUND_SERVER’,
‘ACCOUNT’, ‘FROM’,
‘NODENAME’, ‘REPLYTO’,'DISCARD’ ,’PROCESS’,'INBOX’)
How to Enable the Oracle EBS Workflow Logging:
=========================================
From Self Service > Select “Workflow Manager” under “Oracle Applications
Manager” > Click “Notification Mailers” > Service Components (Service
Components: <SID>) >
b. Click “Workflow
Mailer Service” under “Container” Column.
e. From “Service
Instances for Generic Service Component Container:<SID>”page, click “Pull
Down” button from the Start All/ Stop All.
f . Select Stop All
> Go.
g. We conformed that
for the Services to read Actual 0 Target 0 and Deactivated.
h. Restart the mailer
services using the “Start All” button.
I. We run the
following SQL to make sure service are stopped.
SELECT component_name,
component_status, component_status_info
FROM
fnd_svc_components_v
WHERE component_name
like ‘Workflow%’;
Enable the Statement
logging in Workflow Mailer.
Log files are
created $APPLCSF/$APPLLOG/FNDCPGSC*.txt i.e. the log file for the Active
process for Workflow Mailer Service and Agent Listener services.
To retrieve the last 2
log files for Workflow Mailer and Agent Listener services, run the following
command:
ls -lt
$APPLCSF/$APPLLOG/FNDCPGSC*.txt
How does workflow Notification Mailer IMAP (Inbound Processing) Works:
============================================================
This is the inbound
flow:
1. Approver sends email response which is directed to the value defined in
Replyto address.
a. This address has
been setup by the customer’s mail administrator to route incoming mail to the
IMAP Inbox folder.
2. The Workflow
Inbound Agent Listener picks up the message. Only messages which are in
‘unread’ state are evaluated; the rest of the messages in the inbox are
ignored.
3. The message is scanned to see if it matches entries in the TAG file . Mail
tags are defined in the OAM mailer configuration pages and these list strings
of text and actions to take if those strings are encountered. An example of
this are ‘Out of Office’ replies. If the string of the message matches a mail
tag and the action is ‘Ignore’ then nothing else will happen.
4. The message is then scanned for the presence of a Notification Id (NID).
This NID is matched against valid NID for the mailer node.
5. If valid NID is not detected, (and there is no matching TAG file entry) then
the message is placed into the DISCARD folder.
6. If a valid NID is detected the listener raises a subscription to the
WF_NOTIFICATION_IN queue and places the mail message in the Processed folder.
7. From this point on the message is handled by the product workflow (like PO
APPROVAL) . An event created by that group will monitor the WF_NOTIFICATION_IN
queue and will trigger the rest of the approval workflow.
Steps/events for Oracle Workflow Notification Outbound Processing(eMail from
Oracle Applications Workflow to Users)
=================================================
1.When workflow Engine
determines that a notification message must be sent, it raises an event in BES
(Business Event System) oracle.apps.wf.notifications.send
Event is raised with
Notification ID (NID) as event key
2. There is seeded subscription to this Event
3. Event is placed on WF_DEFERRED agent
4.Event is dequeued from WF_DEFERRED and subscription is processed
5. Subscription places event message to WF_NOTIFICATION_OUT agent.
6.Notification Mailer dequeues message from WF_NOTIFICATION_OUT agent and
6.1convert XML
representation of notification into MIME encoded message (Multi-purpose
Internet Mail Extensions) and
6.2 Send message by
SMTP (Simple Mail Transfer Protocol) to intended user (IfTest Address/Override
Address is set then email is sent to Test Address
E-Mail Notification is sent if all below conditions are truea) Notification
status is OPEN or CANCELED and
b) Notification
mail_status is MAIL or INVALID and
c) Recipient Role has
a valid e-mail address and Notification Preference is in the format MAIL%
d) Workflow Deferred
Agent Listener is running
e) Workflow Notification
Mailer is running
To check a) & b) run below query
SELECT status,
mail_status FROM wf_notifications WHERE notification_id = ‘&NID’;
mail_status >> SENT means mail has gone out of mailer to user
To check c) run below query
SELECT email_address,
nvl(WF_PREF.get_pref(name, ‘MAILTYPE’),notification_preference)
FROM wf_roles
WHERE name =
upper(‘&recipient_role’);
To check d) & e) Use OAM (Oracle Application Manager)
How to purge e-mail notifications from the Workflow queue
================================================
Sometimes Due to large
number of e-mail notifications to accumulated in the queue Workflow mailer will
not start,To fix this issue we need purge the notifications from the Workflow
queue.
The below outlines the steps, Please take proper backup before performing the
below.
1) You need to update the notifications you do not want sent, in the
WF_NOTIFICATIONS table.
2) Check the WF_NOTIFICATIONS table as below. Records where status = ‘OPEN’ and
mail_status = ‘MAIL’ are notifications that will have an e-mail notification
sent.
SQL> select notification_id,status,mail_status,begin_date from
WF_NOTIFICATIONS where status = ‘OPEN’ and mail_status = ‘MAIL’;
3) This should show you which notifications are waiting to be e-mailed.
4) To update a notification so that it will not get e-mailed. Set the
MAIL_STATUS = ‘SENT’. The mailer will think the e-mail has already been sent
and it will not send it again.
SQL> update WF_NOTIFICATIONS set mail_status = ‘SENT’ where mail_status =
‘MAIL’;
-> This will update all notifications waiting to be sent by the mailer.
5) Then run the script wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and
rebuild it with data currently in the WF_NOTIFICATIONS table. This is what
purges all notifications waiting in the queue to be sent.Only the ones where
mail_status = ‘MAIL’ and status = ‘OPEN’ will be placed in the
WF_NOTIFICATION_OUT queue and sent by the mailer.
SQL>sqlplus apps/apps_pwd @$FND_TOP/patch/115/sql/wfntfqup APPS APPS_PWD
APPLSYS
6) Now you can start your WF Containers and then Mailer
Workflow Mailer Debugging Script for Debugging Emails issues
====================================================
This article contains
various Workflow and Business Event debugging scripts.
--Checking workflow Components status whether are they running or stopped.
select component_type,
component_name, Component_status,COMPONENT_STATUS_INFO Error
from
fnd_svc_components
where component_type
like 'WF%'
order by 1 desc,2,3;
Query to get the log file of active workflow mailer and workflow agent
listener Container
=====================================================================
--Note All Workflow
Agent Components logs will stored in single file i.e. container log file.
select
fl.meaning,fcp.process_status_code,
decode(fcq.concurrent_queue_name,'WFMLRSVC', 'mailer container',
'WFALSNRSVC','listener
container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id,
fcp.logfile_name
from
fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where
fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and
fl.lookup_type='CP_PROCESS_STATUS_CODE' and
fl.lookup_code=fcp.process_status_code
and
concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
order by
fcp.logfile_name;
Linux Shell script Command to get outbound error in Mailergrep -i
'^\[[A-Za-z].*\(in\|out\).*boundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*'
<logfilename> | tail -10 ;
--Note: All Mailer log
files starts with name FNDCPGSC prefix
Linux Shell script Command to get inbound processing error in Mailer grep -i
'^\[[A-Za-z].*.*inboundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*'
<logfilename> | tail -10 ;
Query to Check Workflow Mailer Backlog:
==================================
--State=Ready
implies that emails are not being sent & Waiting mailer to send emails
select tab.msg_state, count(*) from applsys.aq$wf_notification_out tab group by
tab.msg_state ;
Check any particular Alert Message email has be pending by Mailer
select
decode(wno.state,
0, '0 = Pending in
mailer queue',
1, '1 = Pending in
mailer queue',
2, '2 = Sent by mailer
on '||to_char(DEQ_TIME),
3, '3 = Exception',
4,'4 = Wait', to_char(state)) State,
to_char(DEQ_TIME),
wno.user_data.TEXT_VC
from
wf_notification_out wno
where
corrid='APPS:ALR'
and
upper(wno.user_data.TEXT_VC) like '%<Subject of Alert Email>%';
Check The Workflow notification has been sent or not
============================================
select mail_status,
status from wf_notifications where notification_id=<notification_id>
--If mail_status is
MAIL , it means the email delivery is pending for workflow mailer to send the
notification
--If mail_status is
SENT, its means mailer has sent email
--If mail_status is
Null & status is OPEN, its means that no need to send email as notification
preference of user is "Don't send email"
--Notification
preference of user can be set by user by logging in application + click on
preference + the notification preference
Check Whether workflow background Engine is working for given workflow or
not in last 2 days
=====================================================================
-- Note: Workflow
Deferred activities are run by workflow background engine.
select
a.argument1,a.phase_code, a.status_code ,a.actual_start_date,a.* from
fnd_concurrent_requests a
where
CONCURRENT_PROGRAM_ID =
(select
concurrent_program_id from fnd_concurrent_programs where
CONCURRENT_PROGRAM_NAME='FNDWFBG')
and
last_update_Date>sysdate-2 and argument1='<Workflow Item Type>'
order by
last_update_date desc
Check whether any business event is pending to process
===============================================
i.e. Query to get
event status & parameters value of particular event in wf_deferred table.
select
wd.user_Data.event_name,wd.user_Data.event_key,
rank() over (
partition by wd.user_Data.event_name, wd.user_Data.event_key order by n.name)
as serial_no,
n.NAME Parameter_name,
N.value Parameter_value ,
decode(state, 0, '0 =
Ready', 1, '1 = Delayed', 2, '2 = Retained',
3, '3 = Exception',
4,'4 = Wait', to_char(state)) state,
wd.user_Data.SEND_DATE,
wd.user_Data.ERROR_MESSAGE,
wd.user_Data.ERROR_STACK,
wd.msgid,wd.delay
from WF_DEFERRED wd ,
TABLE(wd.user_Data.PARAMETER_LIST) n
where
lower(wd.user_data.event_name)='<event Name >'
order by
wd.user_Data.event_name, wd.user_Data.event_key, n.name
When the workflow mailer is either disabled or the mail server restricts its
access to the workflow mailer, all the workflow notifications will retry and
upto the limit set at the workflow mailer configuration and later all the
notifications will be failed. This will result in 2 things at Application level
i) MAIL_STATUS column of the WF_NOTIFICATIONS set to FAILED.
ii) After a series of failure notifications the Notification Style for the
respected User will be set as Disabled and a corresponding user details are
sent to SYSADMIN.
A similar issue araised in one of the client and the many of the users
notification style has been set to Disabled.
Solution:
To overcome the above issue, initially fix the workflow mailer issue and later
need to change the Notification Preference at Workflow Administrator to
"HTML with Attachments" (or any other as per the client's
requirement).
But sometime this setting won't be affected to all the users whose Preference
is set to Disabled. To overcome this situation:
i) Backup the following tables:
CREATE FND_USER_PREFERENCES_BAK as select * from FND_USER_PREFERENCES;
CREATE
WF_LOCAL_ROLES_BAK as select * from WF_LOCAL_ROLES;
ii) To set the mail preference for all the users execute the following:
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-';
iii) To update the mail preference of the users only whose preference is set to
"FAILED", execute the below steps:
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")
SUMHTML (corresponds
to preference value "HTML summary mail")
DISABLED (corresponds
to preference value "Disabled")
iii) To Resend all the failed notifications again to the respected users run
the following concurrent program as SYSADMIN, with the relevant parameters.
Retry Error Workflow Activities
Happy Learning!
