Workflow Mailer Notification not sending out mails

Today Amanda pinged me about an issue with Workflow Notification Mailer in an R12.1.3 instance.  Mails were not going out.  I checked the SMTP with commandline:

telnet justanexample.com 25
mail from:john.doe@justanexample.com
rcpt from:jane.doe@justanexample.com
data
Subject: Test mail from smtp commandline of justanexample.com
Test.
.

The mail was going out fine.  So there was no issue on the unix Sendmail side. I suggested that we check the configuration inside Apps.  A search on support.oracle.com revealed this article:

What to Review When Notifications are not Emailed (Outbound Processing)? [ID 1051421.1]

We ran the query in this article:

1. Make sure the user who you sent the notification to has an email address in the wf tables. There are 2 ways to review this:


a. In the Define Users form, query the user and make sure the user has an email address in this form.

b. Execute the following API to review if that email was synchronized to the workflow tables. The script will request the user name as a parameter:

set serveroutput on
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;
/
Enter value for username: SYSADMIN

old 13: recipient_role := '&username';
new 13: recipient_role := 'SYSADMIN';
display_name=Doe, Jane
email=
language=AMERICAN
territory=AMERICA
notification_pref=MAILHTML

Email was coming up blank.

So I queried the fnd_user table:

select user_name,email_address
from fnd_user
where user_name='SYSADMIN';

USER_NAME

--------------------------------------------------------------------------------
EMAIL_ADDRESS
--------------------------------------------------------------------------------
SYSADMIN
jane.doe@justanexample.com

To sycnrhonize email address in workflow tables,  I executed the sqls given in the article
How To Run The Workflow Directory Services Concurrent Program From The SQLplus Prompt [ID 1213304.1]

1. Sync responsibility role data into the Workflow table:

begin
fnd_user_resp_groups_api.sync_roles_all_resp_secgrps(TRUE);
end;

2. Synchronize WF LOCAL tables:
exec WF_LOCAL_SYNCH.BulkSynchronization('ALL');

3. Workflow Directory Services User/Role Validation:
exec wf_maintenance.ValidateUserRoles(p_BatchSize => null, p_check_dangling => TRUE, p_check_missing_ura => TRUE, p_UpdateWho => FALSE);

Then I re-ran the first query
/

Enter value for username: SYSADMIN
old 13: recipient_role := '&username';
new 13: recipient_role := 'SYSADMIN';
display_name=Doe, Jane
email=
language=AMERICAN
territory=AMERICA
notification_pref=MAILHTML

The result was still the same.

So I decided to check the WF tables:

select table_name,column_name

from dba_tab_columns
where column_name='EMAIL_ADDRESS' and
table_name like 'WF%'
SQL> /




TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
WF_LOCAL_ROLES EMAIL_ADDRESS
WF_LOCAL_ROLES_OLD EMAIL_ADDRESS
WF_LOCAL_ROLES_STAGE EMAIL_ADDRESS
WF_LOCAL_USERS EMAIL_ADDRESS
WF_ALL_ROLES_VL EMAIL_ADDRESS
WF_ALL_ROLE_LOV_VL EMAIL_ADDRESS
WF_AMV_APPR_ROLES EMAIL_ADDRESS
WF_AMV_CHN_ROLES EMAIL_ADDRESS
WF_ENG_LIST_ROLES EMAIL_ADDRESS
WF_FND_RESP_ROLES EMAIL_ADDRESS
WF_FND_USR_ROLES EMAIL_ADDRESS
WF_GBX_ROLES EMAIL_ADDRESS
WF_HZ_GROUP_ROLES EMAIL_ADDRESS
WF_PER_ROLE_ROLES EMAIL_ADDRESS
WF_POS_ROLES EMAIL_ADDRESS
WF_PQH_ROLE_ROLES EMAIL_ADDRESS
WF_ROLES EMAIL_ADDRESS
WF_ROLE_LOV_VL EMAIL_ADDRESS
WF_USERS EMAIL_ADDRESS
WF_USER_LOV_VL EMAIL_ADDRESS

20 rows selected.

On a whim, I updated the email address in the WF_LOCAL_ROLES table:

update wf_local_roles

set email_address='Jane.Doe@justanexample.com'
where name='SYSADMIN;

and re-ran the verificaiton query:
/

Enter value for username: SYSADMIN
old 13: recipient_role := '&username';
new 13: recipient_role := 'SYSADMIN';
display_name=Doe, Jane
email=jane.doe@justanexample.com
language=AMERICAN
territory=AMERICA
notification_pref=MAILHTML

I asked Amanda to test notification mailer now, and sure enough it worked.

For now it is good. However, we need to figure out why the email is not getting synchronized to WF tables automatically.  I did find another article on support.oracle.com which advises the same thing we did. Though it says that it is applicable to 11i, but it works for R12.1 too:

Notify Function When Running Concurrent Request Not Sending Notification to User [ID 1370390.1]


Applies to:

Oracle Concurrent Processing - Version: 11.5.10.2 to 11.5.10.2 - Release: 11.5 to 11.5

Information in this document applies to any platform.

Symptoms

When viewed from OAM, the Workflow Mailer Service is running well.

From the concurrent request submission form (FNDRSRUN), submit any request with "Option" of "Notify the following People", but after running the concurrent request, the user cannot receive the notification.

Cause

The cause of the issue was found to be an invalid or missing email address for the user.

Solution

1. From the request log, verify the notification id has been generated and make a note of the id.

2. Check that the notification mail_status is "MAIL", using the following select statement, which means the notification is in processing.

select NOTIFICATION_ID, STATUS, MAIL_STATUS from wf_notifications where NOTIFICATION_ID = '423828';

3. Using the script identified below together with the notification id to check the notification detail information:

sqlplus apps/apps @$FND_TOP/sql/wfmlrdbg.sql

This will identify a missing vaild email address while sending notification.

4. Using following select to find if the user has been defined with a valid email address:

select user_name, email_address from fnd_user where user_name = '';

5. Verify the email address is not missing in view wf_roles as following:

select display_name, email_address from wf_roles where display_name = '';

6. Manually update table wf_local_roles to set the user a valid email address:

update WF_LOCAL_ROLES set email_address = ''where display_name = '';

commit;

7. User should now receive the notification normally.