Showing posts with label Notification mailer. Show all posts
Showing posts with label Notification mailer. Show all posts

Friday, March 25, 2016

Oracle workflow notification mailer

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:
14. Procedure to Update Fnd preferences

----------------------------------------------------
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=&ReqID;

select ORACLE_PROCESS_ID,ORACLE_SESSION_ID,OS_PROCESS_ID from fnd_concurrent_requests where REQUEST_ID=&ReqID;

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 = 'test@xyz.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.

--------------------------------------------------------------
14. Procedure to Update Fnd preferences
--------------------------------------------------------------
DECLARE
 --get users with email style other then 'Do not send emails'
 cursor c1 is
 select u.user_name
 from fnd_user u, wf_local_roles r
 where r.name = u.user_name
 and notification_preference != 'QUERY';
BEGIN
 for c in c1 loop
 --change user preference to 'Do not send emails'
 fnd_preference.put(c.user_name, 'WF', 'MAILTYPE', 'QUERY');
 FND_USER_PKG.User_synch(c.user_name); -- required for updates the WF_LOCAL_ROLES table
 end loop;
 --commit changes
commit;
END;

~~~ Cheers