Check
workflow mailer service current status
sqlplus apps/<apps password>
select running_processes
from apps.fnd_concurrent_queues
where concurrent_queue_name = 'WFMLRSVC';
Number of running processes should be greater than 0
Find current mailer status
sqlplus apps/<apps password>
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
sqlplus apps/<apps password>
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
sqlplus apps/<apps password>
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
The following 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 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.
Here are 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 true
a) 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
sqlplus apps/<apps password>
select running_processes
from apps.fnd_concurrent_queues
where concurrent_queue_name = 'WFMLRSVC';
Number of running processes should be greater than 0
Find current mailer status
sqlplus apps/<apps password>
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
sqlplus apps/<apps password>
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
sqlplus apps/<apps password>
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
The following 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 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.
Here are 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 true
a) 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
No comments:
Post a Comment