http://appsdbalife.wordpress.com/2009/09/02/notification-mailer-troubleshooting-part-i/
http://appsdbalife.wordpress.com/2009/09/10/notification-mailer-troubleshooting-part-ii/
FIND USER EMAIL AND NOTIFICATION PREFERENCE
SELECT u.user_name, r.notification_preference, r.email_address FROM fnd_user u, wf_local_roles r WHERE r.name = u.user_name and u.user_name = '<username>';
The notification_preference shouldn't be in QUERY or DISABLED, email_address should point to the valid email address. Notice that Mailer checks wf_local_roles for an email address to be used (instead of FND_USER).
SCRIPT 1 :
SELECT d.enq_time, d.deq_time, TO_NUMBER((SELECT VALUE FROM TABLE(d.user_data.parameter_list) WHERE NAME = 'NOTIFICATION_ID')) d_notification_id, msg_state FROM applsys.aq$wf_deferred d WHERE d.corr_id = 'APPS:oracle.apps.wf.notification.send';
Script 2 :
SELECT n.begin_date,
n.status,
n.mail_status,
n.recipient_role,
de.def_enq_time,
de.def_deq_time,
de.def_state,
ou.out_enq_time,
ou.out_deq_time,
ou.out_state
FROM applsys.wf_notifications n,
(SELECT d.enq_time def_enq_time,
d.deq_time def_deq_time,
TO_NUMBER((SELECT VALUE
FROM TABLE(d.user_data.parameter_list)
WHERE NAME = 'NOTIFICATION_ID')) d_notification_id,
msg_state def_state
FROM applsys.aq$wf_deferred d
WHERE d.corr_id = 'APPS:oracle.apps.wf.notification.send') de,
(SELECT o.deq_time out_deq_time,
o.enq_time out_enq_time,
TO_NUMBER((SELECT str_value
FROM TABLE(o.user_data.header.properties)
WHERE NAME = 'NOTIFICATION_ID')) o_notification_id,
msg_state out_state
FROM applsys.aq$wf_notification_out o) ou
WHERE n.notification_id = &NOTIFICATION_ID
AND n.notification_id = de.d_notification_id(+)
AND n.notification_id = ou.o_notification_id(+)
/
msg_state
READY – a message ready to be processed in the next dequeue call. If there are many messages in a queue it indicates that there are some problems with consumer process.
PROCESSED –a message processed by consumer process. The record stays in a queue for keeping activity history only. Messages kept in a queue if the queue has RETENTION parameter set (see DBA_QUEUES view and DBMS_ASADM.ALTER_QUEUE procedure to change it). Be careful with setting this parameter too high as underlying queue table may get very big because of that.
WAIT – a message is not ready for dequeuing as a delay was set during enqueuing process (or it is waiting after failed dequeuing attempt). You probably will not see notifications related messages in that state.
===============================================================
I. Double check 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='&name';
OutPut: email_address= should not be 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='&name';
OutPut should be :
notification_preference= MAILHTML – corresponds to "HTML mail with attachments"
III. Sent a testing email from WF Configuration page to &name 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.
========================================================================================================