workflow deferred and out queue -
clear columns
set lines 200 pages 200
col "Request Date" for a30
col "Last Mail Sent" for a30
col "Mails Status" for a20
col "Mail Action" for a25
col "Request Status" for a20
col "Total Mails" for 9999999
select min(to_char(begin_date,'DD-MON-YYYY:HH24:MI:SS')) "Request Date",(select max(to_char(sent_Date,'DD-MON-YYYY:HH24:MI:SS'))
from apps.wf_notifications where mail_status='SENT' and status in ('OPEN','CLOSED') and to_char(sent_Date)=to_char(sysdate))
"Last Mail Sent",decode(MAIL_STATUS,'MAIL','PENDING','SENT','SENT','CLOSED','COMPLETED') "Mails Status",decode(STATUS,'OPEN','OPEN','CLOSED','COMPLETED')
"Request Status",decode(MAIL_STATUS,'MAIL','READY TO SEND','SENT','SENT') "Mail Action",COUNT(*) "Total Mails"from apps.WF_NOTIFICATIONS where
to_char(begin_date)=to_char(Sysdate) and mail_status in ('MAIL','SENT') and status in ('OPEN','CLOSED') and end_Date is null group by status,mail_status;
to check waiting and sent count -
select count(*) ,decode(deq_time,NULL,'Waiting','Sent') from apps.wf_notification_out group by decode(deq_time,NULL,'Waiting','Sent');
to check based on message type -
select tab.msg_state, count(*) from applsys.aq$wf_notification_out tab group by tab.msg_state;
to check workflow component status-
col COMPONENT_TYPE for a30
col COMPONENT_NAME for a60
col COMPONENT_STATUS for a30
set lines 400
select SC.COMPONENT_TYPE, SC.COMPONENT_NAME,
FND_SVC_COMPONENT.Get_Component_Status(SC.COMPONENT_NAME) COMPONENT_STATUS
from FND_SVC_COMPONENTS SC order by 1, 2;
to check workflow mailer logfile location-
set lines 200 pages 200
col meaning for a10
col OS_PROCESS_ID for 99
col LOGFILE_NAME for a60
select fl.meaning,fcp.process_status_code,
decode(fcq.concurrent_queue_name,'WFMLRSVC','maile r container','WFALSNRSVC','listener container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and fl.lookup_type='CP_PROCESS_STATUS_CODE' and fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
order by fcp.logfile_name;
How to check workflow mails are pending:
select count(*)
from apps.wf_notifications
Where Status In ('OPEN')
And Mail_Status In ('MAIL')
and trunc(begin_date) >= trunc(sysdate-3)
order by notification_id;
select count(*) from WF_NOTIFICATIONS where mail_status in
('MAIL','INVALID') and Status In ('OPEN', 'CANCELED')
and trunc(begin_date) < trunc(sysdate);
--update WF_NOTIFICATIONS set mail_status = 'SENT'
where mail_status in ('MAIL','INVALID')
and Status In ('OPEN', 'CANCELED')
and trunc(begin_date) < trunc(sysdate);
check workflow queue with timings:-
select corr_id CORRID,
wfno.user_data.GET_STRING_PROPERTY('BES_EVENT_KEY') NOTIFICATION_ID, wfno.user_data.GET_STRING_PROPERTY('ROLE') ROLE,
wfno.msg_state STATE,
to_char(enq_time, 'YYYY/MM/DD HH24:MI:SS') enq_time,
to_char(deq_time, 'YYYY/MM/DD HH24:MI:SS') deq_time,
to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS') sys_date,
retry_count RETRY
from applsys.aq$wf_notification_out wfno
Workflow data footprint:-
select c.item_type child, decode(c.end_date,null,'OPEN','CLOSED') child_status,
c.parent_item_type parent, decode(c.parent_item_type,null,'NOPARENT',decode(p.end_date,null,'OPEN','CLOSED')) parent_status,
count(*), to_char(p.begin_date, 'YYYY') Yr
from
wf_items p,
wf_items c
where
p.item_type(+) = c.parent_item_type
and p.item_key(+) = c.parent_item_key
group by c.item_type, decode(c.end_date,null,'OPEN','CLOSED'), c.parent_item_type ,
decode(c.parent_item_type,null,'NOPARENT',decode(p.end_date,null,'OPEN','CLOSED')),
to_char(p.begin_date, 'YYYY')
order by c.item_type , c.parent_item_type;
Volume based on queue:-
select corr_id, msg_state, count(*) from applsys.aq$wf_deferred group by corr_id, msg_state;
select corr_id, retry_count, msg_state, count(*)
from applsys.aq$wf_notification_out where 1=1
--corr_id = 'APPS:ALR:' group by corr_id, msg_state, retry_count
order by count(*) desc
Number of Workflow Mail Processed Last Two Hours and Last One Hour
select count(*) from wf_notifications where mail_status like 'SENT' and begin_date> sysdate-1/12;
select count(*) from wf_notifications where mail_status like 'SENT' and begin_date> sysdate-1/24;
workflow errors and exceptions from log files:-
grep ":ERROR:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrerr.log
grep "Exception:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrexc.log
grep ":UNEXPECTED:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrunexp.log
-upload mlrerr.log , mlrexc.log ,and mlrunexp.log
grep ":ERROR:" $APPLCSF/$APPLLOG/FNDCPGSC1329331.txt > mlrerr_02_12_1.log
grep "Exception:" $APPLCSF/$APPLLOG/FNDCPGSC1329331.txt > mlrexc_02_12_1.log
grep ":UNEXPECTED:" $APPLCSF/$APPLLOG/FNDCPGSC1329331.txt > mlrunexp_02_12_1.log
grep ":ERROR:" $APPLCSF/$APPLLOG/FNDCPGSC1329328.txt > mlrerr_02_12_2.log
grep "Exception:" $APPLCSF/$APPLLOG/FNDCPGSC1329328.txt > mlrexc_02_12_2.log
grep ":UNEXPECTED:" $APPLCSF/$APPLLOG/FNDCPGSC1329328.txt > mlrunexp_02_12_2.log
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;
Workflow count based on year and wf type:-
select c.item_type child, decode(c.end_date,null,'OPEN','CLOSED') child_status,
c.parent_item_type parent, decode(c.parent_item_type,null,'NOPARENT',decode(p.end_date,null,'OPEN','CLOSED')) parent_status,
count(*), to_char(p.begin_date, 'YYYY') Yr
from
wf_items p,
wf_items c
where
p.item_type(+) = c.parent_item_type
and p.item_key(+) = c.parent_item_key
group by c.item_type, decode(c.end_date,null,'OPEN','CLOSED'), c.parent_item_type ,
decode(c.parent_item_type,null,'NOPARENT',decode(p.end_date,null,'OPEN','CLOSED')),
to_char(p.begin_date, 'YYYY')
order by c.item_type , c.parent_item_type;
workflow emails status:- Ex retained
select substr(wfd.corrid,1,40) corrid,
decode(wfd.state,
0, '0 = Ready',
1, '1 = Delayed',
2, '2 = Retained',
3, '3 = Exception',
to_char(substr(wfd.state,1,12))) State,
count(*) COUNT
from applsys.wf_notification_Out wfd
group by wfd.corrid, wfd.state
Run $FND_TOP/sql/wfver.sql and spool the output and upload the same to SR. Below note can be referred to gather the same.
How to Run Script wfver.sql and Collect its Output? (Doc ID 1364295.1)
Workflow test email server:-
telnet <SMTP SERVER> 25
EHLO <SERVER IP>
MAIL FROM:
RCPT TO:
DATA
Subject: Test Email from CM node
This is the email body.
It can have multiple lines.
.
QUIT
