WORKFLOW RELATED QUERIES:

1. Check the status of Notifications:
====================================
col RECIPIENT_ROLE format a20
col FROM_USER format a20
col TO_USER format a20
set lines 170
select NOTIFICATION_ID,MESSAGE_TYPE,MESSAGE_NAME,RECIPIENT_ROLE,STATUS,FROM_USER,TO_USER from wf_notifications where NOTIFICATION_ID=‘&1′;

2.Check the status of Workflow components:
=========================================
SELECT component_name as Component, component_status as Status FROM fnd_svc_components

3. To check whether notification is present:
=========================================
select recipient_role,notification_id,status,mail_status from wf_notifications where recipient_role like ‘&user_name’;

The e-mail notification is sent only if all of the following is true.
Notification status is OPEN or CANCELED
Notification mail_status is MAIL or INVALID

4. Check Recipient role has a valid e-mail address and notification preference MAIL%
=========================================
SELECT email_address, nvl(WF_PREF.get_pref(name, ‘MAILTYPE’), notification_preference)
FROM wf_roles
WHERE name = ‘&recipient_role’;

Recipient can receive email notification only if

notification preference is not set ‘QUERY’ / ‘DISABLED’ / ‘SUMMARY’ / ‘SUMHTML’ &
recipient has valid email address

5.WF_DEFERRED Queue volume:
===========================
col corrid format a60
set lines 130
set pages 100
select NVL(substr(wfe.corrid,1,50),’NULL – No Value’) corrid, decode(wfe.state,0,’0 = Ready’,1,’1 = Delayed’,2,’2 = Retained’,
3,’3 = Exception’,to_char(substr(wfe.state,1,12))) State,count(*) COUNT
from applsys.wf_deferred wfe group by wfe.corrid, wfe.state;

6.WF_NOTIFICATION_OUT Queue volume:
===================================
col corrid format a60
set lines 130
set pages 100
select NVL(substr(wfe.corrid,1,50),’NULL – No Value’) corrid, decode(wfe.state,0,’0 = Ready’,1,’1 = Delayed’,2,’2 = Retained’,
3,’3 = Exception’,to_char(substr(wfe.state,1,12))) State,count(*)COUNT
from applsys.wf_notification_out wfe group by wfe.corrid, wfe.state;

7.Verify SMTP account:
====================
$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Ddbcfile=$FND_SECURE/$TWO_TASK.dbc -Dserver=mail.overheaddoor.com -Dport=25 -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailSmtpTest.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer

8.Verify IMAP account:
====================
$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Ddbcfile=$FND_SECURE/$TWO_TASK.dbc -Dserver=outlook.office365.com -Dport=993 -Dssl=Y [email protected] -Dpassword={OracleWorkflow2014} -Dconnect_timeout=120 -Dlogfile=GmailImapTest.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8