My Journey to the cloud…

In pursuit of excellence….


Troubleshooting EBS Workflow Notification mailer Issues

Oracle E-Business Suite’s Workflow Notification Mailer sends an email notification in a multi-step process.

After a workflow notification is sent, it immediately appears in the recipient’s EBS Worklist UI. For each workflow notification,business event oracle.apps.wf.notification.send is raised to send the same notification as email.

For a workflow notification to be e-mailed, following statements should be true
The notification’s STATUS is OPEN or CANCELED
The notification’s MAIL_STATUS is MAIL or INVALID
The recipient role has a valid email address
The recipient role’s notification preference must be MAILTEXT, MAILATTH, MAILHTML or MAILHTM2
The Workflow Deferred Agent Listener is running
The Workflow Notification Mailer is running

Most of the information above can be obtained by running the diagnostic script $FND_TOP/sql/wfmlrdbg.sql. It takes the notification id as input.

After the business event oracle.apps.wf.notification.send is raised, it is processed through two queues before it is actually delivered as email to the recipient’s Inbox.
WF_DEFERRED
WF_NOTIFICATION_OUT
The Workflow Notification Mailer dequeues the send event messages from this queue and dispatches it through the designated SMTP server.
To determine at a given time where the email notification is being processed, run $FND_TOP/sql/wfmlrdbg.sql for the notification id

Query to find WF Mailer is up and running


SQL>SELECT component_name, component_status
FROM fnd_svc_components
WHERE component_type = 'WF_MAILER';

SQL>

Query to find the name and location of WF related log files


SQL> select fl.meaning,
       fcp.process_status_code,
       decode(fcq.concurrent_queue_name,
              'WFMLRSVC',
              'maile r container',
              'WFALSNRSVC',
              'listener container',
              fcq.concurrent_queue_name),
       fcp.concurrent_process_id,
       os_process_id,
       fcp.logfile_name
  from apps.fnd_concurrent_queues    fcq,
       apps.fnd_concurrent_processes fcp,
       apps.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

SQL>

Workflow Mailer Log file – FNDCPGSC*.txt

Query to check Failed WF Notifications

SQL>Select NOTIFICATION_ID,
       MESSAGE_TYPE,
       MESSAGE_NAME,
       STATUS,
       MAIL_STATUS,
       FROM_USER,
       TO_USER
  from wf_notifications
 where MAIL_STATUS = 'FAILED'
 
SQL>

Query to find the ‘Pending’ WF Notifications waiting to be processed


SQL>SELECT COUNT(*), message_name
  FROM wf_notifications
 WHERE STATUS = 'OPEN' AND mail_status = 'MAIL'
 GROUP BY message_name
 
SQL>SELECT *
  FROM wf_notifications
 WHERE STATUS = 'OPEN' AND mail_status = 'SENT'
 ORDER BY begin_date DESC
 
SQL>

Query to check if WF Notifications are sent
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”

Query to verify whether the message is processed in WF_DEFERRED queue


SQL>select * 
from applsys.aq$wf_deferred a 
where a.user_data.getEventKey()= '&Notification_ID'
SQL>

–Once message is successfully processed, message will be enqueued to WF_NOTIFICATION_OUT queue and if
–errored out, it will be in WF_ERROR queue

select wf.user_data.event_name Event_Name, wf.user_data.event_key Event_Key,
wf.user_data.error_stack Error_Stack, wf.user_data.error_message Error_Msg
from wf_error wf where wf.user_data.event_key = ‘’

Query to check which WF notification are sent and which are errored out


SQL>Select from_user, to_user, notification_id, status, mail_status, begin_date
  from WF_NOTIFICATIONS
 where status = 'OPEN'

Select from_user,
       to_user,
       notification_id,
       status,
       mail_status,
       begin_date,
       USER_KEY,
       ITEM_KEY,
       MESSAGE_TYPE,
       MESSAGE_NAME begin_date
  from WF_NOTIFICATIONS
 where status = 'OPEN'
SQL>

Query to check different types of WF notification that are stuck


SQL>select message_type, count(1)
  from wf_notifications
 where status = 'OPEN' and mail_status = 'MAIL'
 group by message_type

E.g o/p of query –

MESSAGE_Type COUNT(1)
——– ———-
POAPPRV 21 — 21 mails of Po Approval not sent —
INVTROAP 22
REQAPPRV 9
WFERROR 145 — 145 mails have error
APCCARD 5411 - 5411

SQL>

–For the uset to receive WF notification mails, email preference MUST be MAILHTML
Query to check User’s mail preference setup


SQL>SELECT email_address,
       nvl(WF_PREF.get_pref(name, 'MAILTYPE'), notification_preference)
  FROM wf_roles
 WHERE name = '&recipient_role' --recipient_role --- is the User name in Oracle

SQL>

To debug a WF Notification
SQL> $FND_TOP/sql/wfmlrdbg.sql
It will prompt for Notification ID

Query to find WF related parameters from backend


SQL>select fscpv.parameter_value
    from fnd_svc_comp_params_tl fscpt
    ,fnd_svc_comp_param_vals fscpv
    where fscpt.display_name = 'Framework URL timeout' --'Test Address'
    and fscpt.parameter_id = fscpv.parameter_id

SQL>

Query to check the date/time when the last email was sent by WF Mailer


SQL>select to_char(max(begin_date),'DD-MON-YY HH24:MI:SS')
from apps.wf_notifications  
where mail_status = 'SENT'
SQL>

Query to find the WF Test notification status


SQL>select *
  from apps.wf_notifications
--where notification_id = '&notification_Id' --- Pass Notification Id if any
--where message_type = 'REQAPPRV' AND -- This is type of message, possible value are POAPPRV, REQAPPRV,WFTESTS
--user_key = '42056' -- This is PO# or PR # (can be obtained from user)  
--ITEM_KEY = '908848-170147' --- this can be derived from PO_REQUISITION_HEADERS_ALL if message_type is REQAPPRV
 where recipient_role = '<Application_User_Name>' --- Useful to provide if the message_type is WFTESTS
 and message_type = 'WFTESTS' and trunc(begin_date) = trunc(sysdate) --- Trying to look troubleshoot WF Notifications for current date only
 ORDER BY BEGIN_DATE DESC

      SELECT SEGMENT1,wf_item_type,wf_item_key,last_update_date FROM PO_REQUISITION_HEADERS_ALL
WHERE SEGMENT1 = '42052' -- PO or PR #

SQL>

Query to see workflow configuration


SQL>select p.parameter_id, p.parameter_name, v.parameter_value value
  from apps.fnd_svc_comp_param_vals_v v,
       apps.fnd_svc_comp_params_b     p,
       apps.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')
 order by p.parameter_name

SQL>

Some messages like alerts don’t get a record in wf_notifications table
so you have to watch the WF_NOTIFICATION_OUT queue


SQL>select corr_id, retry_count, msg_state, count(*)
  from applsys.aq$wf_notification_out
 where corr_id = 'APPS:ALR:'
 group by corr_id, msg_state, retry_count
 order by count(*) desc
 
 
select q_name,
       msgid,
       corrid,
       to_char(deq_time, 'YYYY-MON-DD HH12:MI:SSSSS AM') dqtime
  from wf_notification_out
 where --msgid = '65BED43EA74678B1E053652850812B40'
 corrid = 'APPS:ALR:'
 ORDER BY dqtime desc
 
select notification_id,msg_state,msg_id,role,corrid,enq_time,deq_time
from  (select msg_id, o.enq_time, o.deq_time, msg_state
              ,(select str_value
			  from   table (o.user_data.header.properties)
                where  name = 'NOTIFICATION_ID') notification_id
              , (select str_value
                 from   table (o.user_data.header.properties)
                 where  name = 'ROLE') role
              , (select str_value
                 from   table (o.user_data.header.properties)
                 where  name = 'Q_CORRELATION_ID') corrid
       from   applsys.aq$wf_notification_out o)         
where notification_id= '&notification_id'
and rownum=1

SQL>

Query to run from backend to update WF mailer attributes


SQL>select fscpv.parameter_value
    from fnd_svc_comp_params_tl fscpt
    ,fnd_svc_comp_param_vals fscpv
    where fscpt.display_name = 'Test Address'
    and fscpt.parameter_id = fscpv.parameter_id;
SQL>

$FND_TOP/sql/afsvcpup.sql
How to set Workflow Mailer Override Address from Backend ? (Doc ID 1533596.1)

Hope this helps. Happy learning.

-Anand M

Advertisement


3 responses to “Troubleshooting EBS Workflow Notification mailer Issues”

  1. Daniel Nieto Escobar Avatar
    Daniel Nieto Escobar

    Great deep analysis… I am having trouble finding in the query my notifications since they are sent to a group, however since oracle show me them in my noification list, should have some query to get them,,,

  2. Hi Anand,

    This article was very helpful. Thanks a lot.

    Regards
    Pramod

    1. Thanks Pramod that it helped you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

About Me

I’m a Hands-On Technical & Entrprise Solutions Architect based out of Houston, TX. I have been working on Oracle ERP, Oracle Database and Cloud technologies for over 20 years and still going strong for learning new things.

You can connect me on Linkedin and also reach out to me

I am certified for 8x AWS, OCP (Oracle Certified Professionals), PMP, ITTL and 6 Sigma.

Disclaimer

This is a personal blog. Any views or opinions represented in this blog are personal and belong solely to the blog owner and do not represent those of people, institutions or organizations that the owner may or may not be associated with in professional or personal capacity, unless explicitly stated.
All content provided on this blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site.

The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information. Any script available on the blog post MUST be tested before they are run against Production environment.

Newsletter

%d bloggers like this: