Workflow Mailer Status

=================

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.