Home » Oracle » Troubleshooting Workflow Notification Mailer issues

Troubleshooting Workflow Notification Mailer issues

To Troubleshoot Workflow Notification  Mailer issues, we need to understand how outbound and inbound processing works

Workflow Notification Mailer issues

Outbound Notification Processing –

-Creation of Workflow notification raises oracle.apps.wf.notification.send event.
-This event is deferred immediately and placed on the WF_DEFERRED agent.
-Workflow Agent Listener on the WF_DEFERRED queue processes the deferred subscription.
-Subscription to this event calls the Generate function (WF_XML.Generate) to generate an XML representation of the notification.
-The event is then placed on the WF_NOTIFICATION_OUT agent.
-Workflow Notification Mailer dequeues the message from WF_NOTIFICATION_OUT.
-Before the e-mail notification is sent, Workflow Notification Mailer performs the following actions:
1. Resolves the notification recipient role to a single e-mail address, which itself can be a mail list.
2. Switch its database session to the recipient role’s preferred language and territory.
3. Generates the message and any optional attachments using the appropriate message template.
4. Sends the message by SMTP protocol.

Inbound Notification Processing

-This is followed If the Inbound processing Checkbox is enabled for it in the Workflow setup
-Email received into INBOX on IMAP Server
-Inbound Workflow Notification Mailer thread queries its IMAP e-mail account for incoming messages.
-Use a Java-based e-mail parser to perform high-level verification checks on the message.
-If the message is a valid notification response, create an XML message and place it on WF_NOTIFICATION_IN.
-Workflow Agent Listener runs on WF_NOTIFICATION_IN dequeues the message.

Workflow executes the appropriate response function to record the response and complete the notification.

Some important points about Workflow Notification Mailer issues

For the workflow mailer to work variables AF_CLASSPATH and AFJVAPRG should be set correctly in adovars.env
-Override address feature can be used to test the mailer. By providing this address, mailer sent all the mail to this address only
-$FND_TOP/sql/Wfver.sql can be used to find the version of workflow.There are some more scripts in $FND_TOP/sql that can be used to debug the workflow items

See also  Oracle RAC interview Questions

Some useful queries about Workflow Notification Mailer issues

select count(*), MSG_PRIORITY, corr_id, msg_state from applsys.aq$wf_deferred
where corr_id like '%oracle.apps.wf.notification%' group by MSG_PRIORITY, corr_id, msg_state;

select count(*), msg_state from applsys.aq$wf_notification_out group by msg_state

select status, mail_status, count(*) from wf_notifications group by status, mail_status;

select count(*), MSG_PRIORITY, corr_id, msg_state from applsys.aq$wf_error where corr_id like 'APPS:oracle.apps.wf.notification%' group by MSG_PRIORITY, corr_id, msg_state;

Log file location for Workflow Notification Mailer

Log files are located in $APPLCSF/$APPLLOG on the concurrent tier
The log file starts with FNDCPGSC.  There are three files, One belongs to the Notification mailer while the other two belong to Workflow Agent listener
ls -ltr FNDCPGSC*.txt

Troubleshooting Workflow Notification  Mailer issues

First of all, here are the sanity checks for Inbound and Outbound Processing

Verify Outbound Processing
1 Verify Agent Listeners are running
2 Go to the “View Details” screen for the Mailer you are configuring
3 Verify that Mailer is running
4 Click on the “Test Email” button
5 Enter a role using the LOV
6. The test address will override that role’s email address
7. Click Send, and write down the Notification ID just in case
8. Verify your email account receives this email
Verify Inbound Processing
1 Send an e-mail to the Workflow Mailer reply-to address
2 Subject “Hello World”
3 Body “Hello World”
4. The mailer should process this email and send back an FYI “unsolicited email” message to your email account

Now let’s take some particular issues on the Workflow Notification Mailer

(A)  A particular user is not receiving the mail from the Workflow mailer

We need to  check the user preferences

declare
recipient_role VARCHAR2(100);
display_name VARCHAR2(200);
email VARCHAR2(1000);
notification_pref VARCHAR2(100);
installed VARCHAR2(1);
language VARCHAR2(100);
territory VARCHAR2(100);
orig_system VARCHAR2(100);
orig_system_id number;
begin
recipient_role := '&username';
WF_DIRECTORY.GetRoleInfoMail(recipient_role, display_name, email,
notification_pref,
language, territory,
orig_system, orig_system_id, installed);
dbms_output.put_line('display_name='|| display_name);
dbms_output.put_line('email='|| email);
dbms_output.put_line('language='|| language);
dbms_output.put_line('territory='|| territory);
dbms_output.put_line('notification_pref='|| notification_pref);
end;
/


The notification preferences determine if the notification will be emailed or not. When the notification preference is set to “Do not send me mail” then notifications are not emailed to that user.

See also  Delivery Option while Submitting Concurrent Request In R12.1.3

The script in step#1 also returns the notification preference value. QUERY means “Do not send me mail”.

How to change the user’s notification preference to a value other than “Do not send me mail” ?
Navigation path:
a. Log into the application as the user with this problem
b. Preferences (at the TOP of the main page on your right-hand side).
c. Notifications Section> Email Style field

(B)  Outbound Processing Not working, No emails are being sent from Notification Mailer(workflow notification mailer not sending emails)

(i)  Make sure Java mailer and WF Agent Listeners are up and running:

Navigation path:
a. Workflow Administrator web Applications Responsibility
b. Workflow Manager
c. Click on the Service Components icon

The following components should be up and running:
Workflow Notification Mailer
Workflow Deferred Agent Listener

(ii) Are messages in WF_DEFERRED and WF_NOTIFICATION_OUT processed?
a. Execute the following queries:

select substr(wfd.corrid,1,40) corrid,
decode(wfd.state,
0, '0 = Ready',
1, '1 = Delayed',
2, '2 = Retained',
3, '3 = Exception',
to_char(substr(wfd.state,1,12))) State,
count(*) COUNT
from applsys.wf_deferred wfd
group by wfd.corrid, wfd.state;

select substr(wfd.corrid,1,40) corrid,
decode(wfd.state,
0, '0 = Ready',
1, '1 = Delayed',
2, '2 = Retained',
3, '3 = Exception',
to_char(substr(wfd.state,1,12))) State,
count(*) COUNT
from applsys.wf_notification_out wfd
group by wfd.corrid, wfd.state;

b. Re-execute the same queries after 15-20 minutes of the first execution and compare the number of messages in the READY state/status. The number of messages in the READY state/status should decrease.

(iii) Check the SMTP server

Here is the check

telnet <SMTP SERVER> 25Enter
Trying 192…
Connected to <SMTP SERVER>.
Escape character is ‘^]’.
220 <SMTP SERVER> ESMTP Sendmail 8.13.8/8.13.8; Fri, 19 Mar 2016 15:4 0:07 -0400
 
EHLO <SMTP SERVER>Enter
250-<SMTP SERVER> Hello <SMTP SERVER> [192.127.240.8], pleased to meet you
250-ENHANCEDSTATUSCODES
250-PIPELINING
250-EXPN
250-VERB
250-8BITMIME
250-SIZE
250-ETRN
250-AUTH DIGEST-MD5 CRAM-MD5
250-DELIVERBY
250 HELP
 
MAIL FROM:workflow-<ORACLE_SID>@<SMTP SERVER>Enter From address
250 2.1.0 workflow-<ORACLE_SID>@<SMTP SERVER>… Sender ok 
RCPT TO:[email protected]Enter To address
250 2.1.5 [email protected]… Recipient ok 
DATAEnter
354 Enter mail, end with “.” on a line by itself 
Test message from <SMTP server>Enter the message
.enter a . by itself on a new line
250 2.0.0 o2JJe7Ip016348 Message accepted for delivery 
QuitEnter
221 2.0.0 <SMTP SERVER> closing connection
Connection closed by foreign host.
 

or

See also  How to create the weblogic Domain

you can use this API

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Dserver=<server> -Dport=25 -Daccount=<account> -Dconnect_timeout=120 oracle.apps.fnd.wf.mailer.Mailer
Example
$ $AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Dserver=tech1.<host.domain> -Dport=25
-Daccount=user1@tech2.<host.domain> -Dconnect_timeout=120 oracle.apps.fnd.wf.mailer.Mailer

(iv)

Run $FND_TOP/sql/wfmlrdbg.sql for a notification id and check the status of the message in WF_DEFERRED and WF_NOTIFICATION_OUT queue.

PROCESSED in WF_DEFERRED – The message is enqueued to WF_NOTIFICATION_OUT

PROCESSED in WF_NOTIFICATION_OUT – The message is sent as e-mail

READY in WF_DEFERRED – Check if Deferred Agent Listener is running

READY in WF_NOTIFICATION_OUT – Check if Notification Mailer is running

(v)  Try rebuilding the WF_NOTIFICATION_OUT queue if necessary

The wfntfqup.sql script rebuilds the WF_NOTIFICATION_OUT queue by dropping and recreating
that queue, removing pending notification messages from the WF_DEFERRED queue, and
repopulating the WF_NOTIFICATION_OUT queue from the Oracle Workflow Notification System
tables.

Stop Notification Mailer, rebuild Mailer Queue using $FND_TOP/patch/115/sql/wfntfqup.sql.

(C) Inbound Processing not working

(i)  Check the IMAP server

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Dserver=<IMAP Server> -Dport=143 \
-Ddebug=Y \
-Daccount=workflow-<ORACLE_SID> -Dpassword=<workflow password> -Dconnect_timeout=120 \
-Ddbcfile=$FND_SECURE/<ORACLE_SID>.dbc -Dfolder=Inbox oracle.apps.fnd.wf.mailer.Mailer

(ii) Check if the expunge to the inbox is checked or Not

(iii) log in to the IMAP mailbox and clean up any spam email if present. It is recommended to regularly clean the Process and discard the folder

(iv) Create a tag  for the autoreply and delivery failure notification to avoid the continuous sending of mail

(v) Ensure the Mailer inbound processing has been enabled by setting the mailer parameter Inbound thread count=1 and ensure that the Notification mailer is up and running.

Related Links
Unable to Start Output Post Processor
How to check/change Workflow Notification mailer configuration from backend
How to start Workflow Notification Mailer from Backend
SQL to check Workflow Agent Listener
Java Workflow Notification Mailer Configuration in Oracle apps

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top