EBS R12 -- Notification Mailer problem/fnd_user_preferences/Relay access denied/MalformedURLException

If you are working as an Apps Dba Consultant, your life may go by diagnosing Notification Mailer's problems..  Every time a different EBS environment, a different mail server , that' s why a different workflow notification mailer problem..
Altough I feel myself  oversaturated with it, I cant get enough of solving Notification Mailer problems, as they are in a great demand nowadays :)

Okay here we are diagnosing the Workflow Notification Mailer again..

This time, the customer complains about the notification emails, which are not sent to their receipients..
WOW! What a great surprise! :)

First thing I checked was the notifications table;

select notification_id, status, mail_status, begin_date
from WF_NOTIFICATIONS
where status = 'OPEN' and mail_status = 'MAIL';

By using this query, I saw that mails are sitting in the wf_notifications to be sent to their recipients..

This was indicating a problem, clearly.. The mailer did not send or was unable to send the emails.. Okay, but why?

Then I checked the mailer, and saw its queue and concurrent manager were active..

SELECT concurrent_queue_name manager, fcp.last_update_date, fcp.concurrent_process_id pid, meaning, fcp.logfile_name
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup
WHERE concurrent_queue_name in ('WFMLRSVC')
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id
AND flkup.lookup_code=fcp.process_status_code
AND lookup_type ='CP_PROCESS_STATUS_CODE'
AND meaning='Active'

At this point, I got suspected it might be related with mail preferences of the EBS users..

Actually, mail preferences of the users might be DISABLED..

So I updated all the mailtypes from DISABLED to MAILHTML..

update wf_local_roles
set notification_preference='MAILHTML'
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='MAILHTML'
where preference_name='MAILTYPE'
and module_name='WF'
and preference_value='DISABLED';

commit;

By using above query, I updated a lot of records, so yes! Mail preferences of the users were set to DISABLED..

Okay, this should be the fix , right? 

To answer this question, I made the customer test the workflow that would send a notification email in its last step..

The result was negative :(.. The receipients could not get their notification emails..

Then I checked fnd_user_preferences to be sure that Mail Preferences were still set to MAILHTML..

select * from 

fnd_user_preferences
where preference_name='MAILTYPE'
and module_name='WF'
and preference_value='DISABLED';

By using the above query, I saw that preference_value column of the user that was suppose to get an email , was set to DISABLED again...

So, It must be notificaiton mailer making the user's mail preference to be set to DISABLED..

Notification mailer does that if it can't send an email to that user. Especially, if Mailer encounters a problem queuing its emails to the Mail Server, It marks the status of the notification as  FAILED, and makes the mail preference of the related user to be set to DISABLED..

I also saw that It was coming in the workflow administrator:)

The notification with the ID of 727095 experienced problems when attempting to dispatch an email notification to the role S_ERMAN. Subsequently the notification preference for the following users has been set to DISABLED. Please correct the issue and re-enable their notification preference.

By knowing this, I made an SMTP test from the command line;

(Note that : The Mail server was requiring a user/pass)

telnet [outbound server] 25

EHLO [mailer node]
auth login
334 BSDLSADX
b3JhY2xlQGNleXBvBCcnY29tdsRy
334 UGFzc3dvMDS6
Y2V5MTSAA==
235 2.0.0 Authentication successful

MAIL FROM: [reply_to address]
RCPT TO: [my_test_email_address]
DATA
Subject: Test message

Test message body
.
quit

So , it got queued.. The test message was delivered without a problem..

Then I decided the make a line by line analysis on the Notification Mailer's log file..

To do that, I first set the log level to statement, reproduced the error and then analyzed the log file.. While analyzing the log file , I saw the following;

oracle.apps.fnd.wf.mailer.SMTPOutboundProcessor.send(Message)]:
From address -> erman@blabladomain.com.tr
Email to [] was not accepted for delivery.[Aug 29, 2014 5:05:38 PM oracle.apps.fnd.wf.mailer.SMTPOutboundProcessor.send(Message)]:Problem encountered when sending to {[[<ahmet@blabladomain.com.tr>]]} -> javax.mail.SendFailedException: Invalid Addresses; nested exception is:
class javax.mail.SendFailedException: 554 5.7.1 <ahmet@blabladomain.com.tr>: Relay access denied

It was clear.. There was a Relay problem.. But what a minute! The mailer was authenticating with user/pass, so we should not get any Relay errors.. 

Okay... Maybe the notification mailer did not authenticate itself using user/pass, although the user and pass information was supplied to it.

At this point, I checked the Oracle Support, and reached the bug record in 2 minutes:)

Bug 13609378 - 1OFF:12.1.3:MAILER FAILS TO DETECT SMTP AUTHENTICATION FOR SOME SERVERS
Bug 14676206 - 1OFF:12.1.3:JAVAX.MAIL.SENDFAILEDEXCEPTION: 554 5.7.1 SENDER ADDRESS REJECTED

When outbound server configuration is applied from OAM UI SMTPUtils.isValidOutbound() API is called to validate the server configuration.
When SMTP server is supporting authentication and outbound user name and password are given, session object will be created using the authenticator with the given details.
When SMTP server is not supporting authentication or outbound user name and password are not given, session object will be created without using the authenticator.
Hence no error will be thrown even if the SMTP server is supporting authentication but Mailer fails to detect it.

So I applied the fix for that :) The fix was Patch 14676206.

This patch brought a new version SMTPUtils.class.

$JAVA_TOP/oracle/apps/fnd/wf/mailer/SMTPUtils.class - 120.0.12010000.6

After applying Patch 14676206, I updated mail preferences to MAILTYPE (just in case) and  asked my customer to test the problematic workflow once again.. 

The result was negative .. Again... But this time, user prefences was not set to DISABLED.. So we clearly made a progress..

Okay, at this point, I jumped into the Mailer's log ,as I was curious to see the new problem:)

By the way, I use the following query to locate the mailers log file.. (it is in $APPLCSF/log actually)

SELECT concurrent_queue_name manager, fcp.logfile_name  

FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup  

WHERE concurrent_queue_name in ('WFMLRSVC', 'WFALSNRSVC')  

AND fcq.concurrent_queue_id = fcp.concurrent_queue_id  

AND fcq.application_id = fcp.queue_application_id  

AND flkup.lookup_code=fcp.process_status_code  

AND lookup_type ='CP_PROCESS_STATUS_CODE' 

So, It was as I expected, the errors in the log file were changed..

Now there were XML parse errors as follows;

oracle.apps.fnd.wf.mailer.NotificationFormatter.getFormattedMessages()]:Problem parsing XML -> org.xml.sax.SAXException: Problem obtaining the RESOURCE content -> java.net.MalformedURLException[Sep 1, 2014 11:10:31 AM EEST]:1409559031792:-1:-1:erpapps:192.168.0.5:-1:-1:1:20420:SYSADMIN(0):-1:Thread[outboundThreadGroup1,5,outboundThreadGroup]:1943056439:69743:1409558843248:4:ERROR:[SVC-GSM-WFMLRSVC-115970-10006 : oracle.apps.fnd.wf.mailer.SMTPMessageHandler.prepareMessages(String)]:FormatterException > oracle.apps.fnd.wf.mailer.FormatterException: Problem parsing XML> org.xml.sax.SAXException: Problem obtaining the RESOURCE content -> java.net.MalformedURLException at oracle.apps.fnd.wf.mailer.NotificationFormatter.handleResEndTag(NotificationFormatter.java:3470)
at oracle.apps.fnd.wf.mailer.NotificationFormatter.endElement(NotificationFormatter.java:578) at oracle.xml.parser.v2.XMLContentHandler.endElement(XMLContentHandler.java:210)
at oracle.xml.parser.v2.NonValidatingParser.parseElement(NonValidatingParser.java:1345) at oracle.xml.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:362)
at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:308) at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:337)
at oracle.apps.fnd.wf.mailer.NotificationFormatter.getFormattedMessages(NotificationFormatter.java:354) at oracle.apps.fnd.wf.mailer.SMTPMessageHandler.prepareMessages(SMTPMessageHandler.java:77)
at oracle.apps.fnd.wf.mailer.SMTPOutboundProcessor.read(SMTPOutboundProcessor.java:796) at oracle.apps.fnd.cp.gsc.SvcComponentProcessor.process(SvcComponentProcessor.java:604) at oracle.apps.fnd.cp.gsc.Processor.run(Processor.java:283)
at java.lang.Thread.run(Thread.java:619) Caused by: org.xml.sax.SAXException: Problem obtaining the RESOURCE content -> java.net.MalformedURLException

So , by looking to the error stack, I could say that these errors must be related with a another bug , because this time the errors were coming purely from the code obviously.
That's why, I did not made any controls and directly jumped in to the Oracle Support and found the related bug as follows;

BUG 9757926 - NOT RECEIVING PURCHASING REQUISITION EMAIL NOTIFICATION:JAVA.NET.MALFORMEDURLEXC
When "Attach images to outbound emails" is enabled the Workflow Mailer will embed any referenced image URL into the email message. PO Requisition (PO_REQ_APPROVE_JRAD) message includes an image that will be included in the email message. The complete URL pointing to this images is not is not being populated by the Workflow Mailer code. The code should retrieve http://<host.domain:port>/OA_MEDIA/ag_transparentpixel.gif. It is actually retrieving http://. This is not a valid URL.
When a Notification contains an Oracle Application Framework regions which is referring to an absolute URL then 'address' value is coming as null in URLMIMETagListener.editImageRef() method and 'java.net.MalformedURLException' is thrown.
For Example, the following image URL in the framework region would cause such problem:
<img src="http://server:port/OA_MEDIA/ag_transparentpixel.gif ...>So the fix was Patch 9868639. This patch was bringing a new version URLMIMETagListener.java, and the last step in the treatment of this Notification Mailer. :)

$JAVA_TOP/oracle/apps/fnd/wf/mailer/URLMIMETagListener.class  120.4.12010000.6

I want to remind some important things before I finish;

To able to solve a problem in EBS, we must at least have an idea about the things we dealing with.

If the problem is in notification mailer, we as Apps Dbas have to know the technology and how it works in the background.. We cant always use heuristic approach to solve EBS problems..

Based on my experiences, I can say that there are a lot combinations for being false, but most of the time there is only a one good combination for being true in E-Business Suite:)