WF_NOTIFICATIONS表中的MAIL_STATUS



Troubleshooting Workflow Notification Mailer IssuesFind Workflow Notification Mailer is up and Running?
SELECT component_name, component_statusFROM fnd_svc_componentsWHERE component_type = 'WF_MAILER';
Workflow logs: FNDCPGSC*.txt under $APPLCSF/$APPLOG directoryFind the Failed Ones?
Select NOTIFICATION_ID, MESSAGE_TYPE, MESSAGE_NAME, STATUS, MAIL_STATUS, FROM_USER, TO_USER from wf_notifications where MAIL_STATUS='FAILED';
Check pending e-mail notification that was pending for process.
Sql> SELECT COUNT(*), message_name FROM wf_notificationsWHERE STATUS='OPEN'AND mail_status = 'MAIL'GROUP BY message_name;
Sql> SELECT * FROM wf_notificationsWHERE STATUS='OPEN'AND mail_status = 'SENT'ORDER BY begin_date DESC
Check the Workflow notification has been sent or not?
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 emailas notification preference of user is "Don't send email"
--Notification preference of user can be set by user by logging in application +click on preference + the notification preference
1. Verify whether the message is processed in WF_DEFERRED queue
select * from applsys.aq$wf_deferred a where a.user_data.getEventKey()= '<nid>'<nid> - notification id
2. If the message is processed successfully message will be enqueued to WF_NOTIFICATION_OUT queue, if it errored out it will be enqueued to 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_Msgfrom wf_error wf where wf.user_data.event_key = '<nid'>To check what all mails have went and which all failed ?
Select from_user,to_user,notification_id, status, mail_status, begin_datefrom 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_datefrom WF_NOTIFICATIONS where status = 'OPEN';

Users complain that notifications are stuck ?
Use the following query to check to see whatever the users are saying is correct
SQL> select message_type, count(1) from wf_notificationswhere status='OPEN' and mail_status='MAIL' group by message_type;
E.g o/p of query -
MESSAGE_Type COUNT(1)
-------- ----------
POAPPRV 11 --- 11 mails of Po Approval not sent --
-
INVTROAP 12
REQAPPRV 9
WFERROR 45 --- 45 mails have error
If Mail not received by User ?
select Name,DISPLAY_NAME,EMAIL_ADDRESS,NOTIFICATION_PREFERENCE,STATUSfrom wf_users where DISPLAY_NAME=xxx,yyy;
StatusActive
Notification_preference-> Mailtext
Email Address should not be null
Notification not sent waiting to be mailed ?
SQL> select notification_id, status, mail_status, begin_date from WF_NOTIFICATIONSwhere status =OPENand mail_status =MAIL;
To debug the notification id ?
$FND_TOP/sqlrun wfmlrdbg.sql
Change Mail Preferences for all Application Users (FND USERS)How to mass change Mail Preferences for FND USERS [ID 451929.1]
Solution :Using sqlplus as the apps user:
1st Method:
1.update wf_local_rolesset notification_preference = 'MAILHTML'
where user_flag='Y'
2.update fnd_user_preferencesset preference_value = 'MAILHTML'where module_name = 'WF'and preference_name = 'MAILTYPE'
commit;
2nd method:
Navigation: Connect to Applications as Sysadmin user and select responsibility
Workflow Administrator Web Applications --> Administration --> Global Preferences

Tablespace Usage/Free
TEMP Usage per session ?
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,COUNT(*) sort_opsFROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process PWHERE T.session_addr = S.saddrAND S.paddr = P.addrAND T.tablespace = TBS.tablespace_nameGROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,S.program, TBS.block_size, T.tablespaceORDER BY sid_serial;
Temp Space used/free?
select tablespace_name, bytes_used/1024/1024, bytes_free/1024/1024 from v$temp_space_header;
Users Using Temp tablespace?
select b.tablespace, (b.blocks*p.value)/1024/1024 , a.sid, a.username from sys.v _$session a, sys.v_$sort_usage b, sys.v_$parameter pwhere p.name=b.session_addr;
To get which user is using temp tablespace ?
select username,tablespace,extents from v$sort_usage;
Free and Used Space in Temp Tablespace ?
SELECT A.tablespace_name tablespace, D.mb_total,SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,