Oracle workflow notification mailer status - sql

Oracle workflow notification mailer sql's
=========================================

1. Workflow: version
2. check workflow status.
3. check if workflow is used by only one instance
4. check if processor_read_timeout_close is set to 'Y'
5. check for bad e-mail address
6. How to know mail sent to a user with details:
7. How to know whether it is set to correct url from porfile options:
8. How to know reqid, process id, sid..
9. workflow patches
10. Workflow: To see failed, open notifications
11. To check if email address, notification preference, display_name
12. How to know workflow responsibility from backend:
13. Steps to drop and recreate WF_CONTROL queue:


=================
1. Workflow: version
=================
$FND_TOP/sql/wfver.sql

----------------------------------------------------
2. check workflow status.
----------------------------------------------------
set linesize 120
set pagesize 50
column COMPONENT_NAME format a45
column STARTUP_MODE format a15
column COMPONENT_STATUS format a15
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
----------------------------------------------------
col value format a20
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 patches
===================
will provide us information as to your base line code level.
Many issues are only relevant to a certain code level so this information is essential:

set linesize 155;
set pagesize 200;
set verify off;
select b.bug_number bug, b.LAST_UPDATED_BY ldate, decode( bug_number, 2728236 , 'OWF.G INCLUDED IN 11.5.9',
3031977, 'POST OWF.G ROLLUP 1 - 11.5.9.1',
3061871, 'POST OWF.G ROLLUP 2 - 11.5.9.2',
3124460, 'POST OWF.G ROLLUP 3 - 11.5.9.3',
3316333, 'POST OWF.G ROLLUP 4 - 11.5.9.4.1',
3314376, 'POST OWF.G ROLLUP 5 - 11.5.9.5',
3409889, 'POST OWF.G ROLLUP 5 Consolidated Fixes For OWF.G RUP 5', 3492743, 'POST OWF.G ROLLUP 6 - 11.5.9.6',
3868138, 'POST OWF.G ROLLUP 7 - 11.5.9.7',
3262919, 'FMWK.H',
3262159, 'FND.H INCLUDE OWF.H',
3258819, 'OWF.H INCLUDED IN 11.5.10',
3438354, '11i.ATG_PF.H INCLUDE OWF.H',
3140000, 'ORACLE APPLICATIONS RELEASE 11.5.10 MAINTENANCE PACK',
3240000, '11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 1',
3460000, '11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 2',
3480000, 'ORACLE APPLICATIONS RELEASE 11.5.10.2 MAINTENANCE PACK',
4017300, 'ATG_PF:11.5.10 Consolidated Update (CU1) for ATG Product Family',
4125550, 'ATG_PF:11.5.10 Consolidated Update (CU2) for ATG Product Family',
4719658, 'ONE OFF PATCH FOR MISSING RESPONSIBILITIES - WFDS Fix',
5121512, 'AOL USER RESPONSIBILITY SECURITY FIXES VERSION 1',
6008417, 'AOL USER RESPONSIBILITY SECURITY FIXES 2b',
4676589, '11i.ATG_PF.H RUP4',
5473858, '11i.ATG_PF.H RUP5',
5903765, '11i.ATG_PF.H RUP6',
4334965, '11i.ATG_PF.H RUP3') patch
from apps.AD_BUGS b
where b. BUG_NUMBER in ('2728236','3031977','3061871','3124460','3316333','3314376','3409889','3492743','3262159','3262919','3868138','3258819','3438354','3240000','3460000','3140000','3480000','4017300','4125550','4719658','5121512','6008417','4676589','5473858','5903765','4334965')
order by patch;

===============================================
10. 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;



====================================================================
11. 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';

====================================================
12. 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;

============================================
13. 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.