=================
1. Workflow: version
=================
$FND_TOP/sql/wfver.sql
----------------------------------------------------
2. check workflow status.
----------------------------------------------------
select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS,fcq.last_update_date
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;
----------------------------------------------------
3. check if workflow is used by only one instance
----------------------------------------------------
select p.parameter_id,
p.parameter_name,
v.parameter_value value
from apps.fnd_svc_comp_param_vals_v v,
apps.fnd_svc_comp_params_b p,
apps.fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in ( 'INBOUND_SERVER','ACCOUNT', 'REPLYTO')
order by p.parameter_name;
----------------------------------------------------
4. check if processor_read_timeout_close is set to 'Y'
----------------------------------------------------
set pagesize 100
set linesize 132
set feedback off
set verify off
col value format a35
col component_name format a30
select c.component_id, c.component_name, p.parameter_id, p.parameter_name, v.parameter_value value
from apps.fnd_svc_comp_param_vals_v v, apps.fnd_svc_comp_params_b p, apps.fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name = 'PROCESSOR_READ_TIMEOUT_CLOSE'
order by c.component_name,p.parameter_name;
----------------------------------------------------
5. check for bad e-mail address
----------------------------------------------------
If below SQL statement is returning rows you need to correct the email addresses for associated users:
set linesize 170
col name format a40
col email_address format a80
select name, email_address from apps.wf_local_roles where email_address like '% %';
select name, email_address from apps.wf_local_roles where email_address like '%%';
============================================
6. How to know mail sent to a user with details:
============================================
select name, display_name, notification_preference, email_address from wf_local_roles where name = '';
====================================================================
7. How to know whether it is set to correct url from porfile options:
====================================================================
set linesize 155;
set pagesize 200;
set verify off;
col Profile format a50;
col Value format a50;
select t.PROFILE_OPTION_ID ID, z.USER_PROFILE_OPTION_NAME Profile,
nvl(v.PROFILE_OPTION_VALUE,'Replace with non-virtual URL') Value
from apps.fnd_profile_options t, apps.fnd_profile_option_values v, apps.fnd_profile_options_tl z
where (v.PROFILE_OPTION_ID (+) = t.PROFILE_OPTION_ID)
and (z.PROFILE_OPTION_NAME = t.PROFILE_OPTION_NAME)
and (t.PROFILE_OPTION_NAME in ('WF_MAIL_WEB_AGENT'));
====================================
8. How to know reqid, process id, sid..
=====================================
select request_id,phase_code,status_code,hold_flag from fnd_concurrent_requests where REQUEST_ID=20422815;
select ORACLE_PROCESS_ID,ORACLE_SESSION_ID,OS_PROCESS_ID from fnd_concurrent_requests where REQUEST_ID=20422815;
select a.session_id,
b.owner,
b.object_type,
b.object_name,
a.oracle_username,
a.os_user_name,
a.process,
a.locked_mode
from v$locked_object a, dba_objects b
where b.object_id = a.object_id
and a.session_id='3383';
select name, display_name, notification_preference, email_address from wf_local_roles where name = 'SYSADMIN';
===============================================
9. Workflow: To see failed, open notifications
===============================================
SELECT message_type, COUNT(1)
FROM apps.wf_notifications
WHERE 1 = 1 AND mail_status = 'FAILED' AND status = 'OPEN'
GROUP BY message_type;
====================================================================
10. To check if email address, notification preference, display_name
====================================================================
select DISPLAY_NAME, NOTIFICATION_PREFERENCE from wf_users where EMAIL_ADDRESS = 'h@h.com' and STATUS = 'ACTIVE' and DISPLAY_NAME = 'xxxxx';
select DISPLAY_NAME, NOTIFICATION_PREFERENCE, EMAIL_ADDRESS from wf_users where NOTIFICATION_PREFERENCE='MAILTEXT' and STATUS = 'ACTIVE';
select distinct(count(*)) from wf_users where NOTIFICATION_PREFERENCE='MAILTEXT' and STATUS = 'ACTIVE' and EMAIL_ADDRESS = 'mmmmm@yahoo.com';
====================================================
11. How to know workflow responsibility from backend:
====================================================
select wes.status, wes.phase, wes.rule_function, wes.on_error_code from wf_events we, wf_event_subscriptions wes
where we.name='oracle.apps.fnd.wf.ds.userRole.updated' and we.guid=wes.event_filter_guid;
============================================
12. Steps to drop and recreate WF_CONTROL queue:
============================================
a. Shut down the concurrent managers.
b. Connect to sqlplus session as APPS user:
Execute: (For Workflow Embedded within Apps)
SQL>exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'APPLSYS.WF_CONTROL', force =>TRUE);
SQL> commit;
Execute: (For Standalone Workflow)
sqlplus / @wfctlqec.sql
c. Execute wfjmsqc2.sql for creating all Advanced Queues with JMS Text structures.
Usage:
sqlplus / @wfjmsqc2.sql
Both of the script are under $FND_TOP/patch/115/sql or $FND_TOP/sql directories.
d.Run afwfqgnt.sql to recreate grants/synonyms for Workflow Advanced Queue tables
Usage:
sqlplus apps/apps @$FND_TOP/patch/115/sql/afwfqgnt.sql APPS APPLSYS
Additional Information: For Rebuilding all other WF Queue refer Note 754468.1 Title: How to Rebuild Workflow Queues ,based on your application version.
3. Restart the services.