A user hasn’t got email notifications. Despite of the fact that FND_USER email has been set.
- From: "Jurijs Velikanovs" <j.velikanovs@xxxxxxxxx>
- To: ora-apps-dba@xxxxxxxxxxxxx
- Date: Fri, 23 Jun 2006 01:11:07 +0100
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
Other related posts:
- » A user hasn’t got email notifications. Despite of the fact that FND_USER email has been set.
