Would like to share with you email notifications troubleshooting experience I had today.
From some point in time all email notifications for my personal user in Apps (production) stopped from to be sent. I haven't time to investigate at that point and created a dummy user for email notifications testing purposes. Today I discovered the reason for stopping notifications to be sent. I think it might be useful for you for future troubleshooting or better understanding how WF notifications are working. The story a bit long but I hope it will be beneficial for you to read thought. Take a look:
My username in the system is VJV.
--------------------------------- I. Double checked that the user account have got an email address assigned to it by navigating thought a forms interface (System Administrator -> Security -> Users -> Define) or executing the following SQL: select a.user_name, a.email_address from fnd_user a where a.user_name='VJV'; OutPut: email_address= in not null
--------------------------------- II. Double check if email preferences for my username (available from Self Service login home page) is set correctly (doesn't set to DISABLE). Can be checked from SQL as well: select a.notification_preference from applsys.wf_local_roles a where a.name='VJV'; OutPut: notification_preference= MAILHTML – corresponds to "HTML mail with attachments"
--------------------------------- III. Sent a testing email from WF Configuration page to VJV and didn't receive it. a. Have checked WF_NOTIFICATIONS for the particular notification. select n.status, n.mail_status from wf_notifications n where n.notification_id=245868; OutPut: STATUS=OPEN MAIL_STATUS=NULL
b. Checked WF_NOTIFICATION_OUT queue for a particular notification OutPut: SELECT o.deq_time ,o.enq_time ,msg_state ,o.user_data FROM applsys.aq$wf_notification_out o where TO_NUMBER ((SELECT str_value FROM TABLE (o.user_data.header.properties) WHERE NAME = 'NOTIFICATION_ID')) =245868; deq_time= not null enq_time= not null msg_state=PROCESSED -- Above fields values indicates that the message has been enqueued (by deferred agent) and dequeued (processed if you wish) by WF notification mailer. -- There the hack my email then ? user_data.text_lob= <?xml version="1.0" ?><NOTIFICATIONGROUP maxcount="1"><NOTIFICATION nid="245868" language="AMERICAN" territory="AMERICA" codeset="UTF8" full-document="N" reason="no_members"></NOTIFICATION> </NOTIFICATIONGROUP> -- The value of user_data.text_lob looks interested. Normally that you expecting to see is full email text in the XML format. This message doen't looks like a test notification (to short). Interesting bit to pay attention to is full-document="N" reason="no_members" Bit misleading isn't it (no results in Metalink looking for key words)?
--------------------------------- IV. After digging thought a WF_XML package code discovered the following condition: Procedure GenerateMessage ================================================ if email is not null then ... else str := '<?xml version="1.0" ?>'; ... generateGroupDoc(p_doc, pos, recipient_role, notification_pref, if members_t.count = 0 then ... AddElementAttribute('full-document', 'N', attrlist); -- corresponds to full-document="N" AddElementAttribute('reason', 'no_members', attrlist); -- corresponds to reason="no_members" ... ================================================ How WF_XML checked email address for my username? By running the following SQL: SELECT a.email_address, a.orig_system FROM applsys.wf_local_roles a where a.name='VJV'; OutPut: email_address=null orig_system=REP
--------------------------------- V. Next steps are straightforward: a. Why email address for my user is null in WF tables but is not null in FND? This is because for WF, the primary module in context for user information is PER=HR (as indicated in previous SQL output).
b. At this point I have remembered that for CRM/HR troubleshooting reasons I have asked a business administrator to assign my HR record in Apps to my username record! Obviously hi didn't specified email address in the my employee record: select full_name ,email_address from per_people_f ppf where full_name like '%Jurijs%' AND sysdate between ppf.effective_start_date and ppf.effective_end_date; OutPut: full_name=Velikanovs, Mr. Jurijs
email_address=jvelikanovs@xxxxxxxxxxxxxxx
c. The last thing that I needed to do is to ask the business administrator to indicate my email address in HR tables. He accomplished it using Super HRMS Manager responsibility.
--------------------------------- Thank you for reading so far :) Hope you have found something for you from this story. I will be glad to receive any feedback.
Thanks again, Yury