Workflow Notification Status:
select a.status,
a.mail_status,
COUNT(1)
from wf_notifications a,
wf_notification_out b
where 1=1
and TRUNC(a.begin_date) = trunc(sysdate)
and a.notification_id = (select str_value
from table(b.user_data.header.properties)
where name = 'NOTIFICATION_ID')
AND a.status not in ( 'CANCELED' , 'CLOSED')
GROUP BY a.status, a.mail_status
order by 1,2;
query to check stuck transactions :
select decode(wfc.state,
0, '0 = Ready',
1, '1 = Delayed',
2, '2 = Retained/Processed',
3, '3 = Exception',
to_char(substr(wfc.state,1,12))) State,
count(*)
from applsys.wf_notification_out wfc
group by state;
deq_time is not always populated in WF_DEFERRED. The best way to monitor is to check if there are any READY events
select msg_state,count(*) from applsys.aq$wf_deferred group by msg_state;
For getting Item_Type and Display name for Runnable processes.
SELECT WFA_ACT.ITEM_TYPE ITEM_TYPE ,WFA_ACT.NAME PROCESS_NAME ,
WFA_ACT.DISPLAY_NAME DISPLAY_NAME
FROM wf_activities_vl wfa_act
WHERE wfa_act.runnable_flag = 'Y'
AND wfa_act. type = 'PROCESS'
AND sysdate BETWEEN wfa_act.begin_date
AND NVL(wfa_act.end_date, sysdate);
Query to find records that are pending in each of the workflow agent listener queues
SELECT 'select ''' || t.component_name || ' (queue_table: ' || p.queue_table ||
')''||'' Count: ''||count(*) c from ' || p.owner || '.' || .queue_table ||
' where deq_time is null and nvl(delay,enq_time)<sysdate-1/24 ||
nvl2(t.correlation_id,
'and corrid like ''' || t.correlation_id || ''' ',
NULL) || 'having count(*)>0;'
FROM applsys.fnd_svc_components t,
applsys.wf_agents o,
dba_queues p
WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
AND p.owner || '.' || p.name = o.queue_name
AND t.component_type LIKE 'WF_%AGENT%';
Query helps to determine which activities are in the WF_DEFERRED_TABLE_M
select w.user_data.itemtype "Item Type", w.user_data.itemkey "Item Key",
decode(w.state, 0, '0 = Ready',
1, '1 = Delayed',
2, '2 = Retained',
3, '3 = Exception',
to_char(w.state)) State,
w.priority, w.ENQ_TIME, w.DEQ_TIME, w.msgid
from wf_deferred_table_m w
where w.user_data.itemtype = '&item_type';
Check the various workflow agent listeners and their statuses
SELECT t.component_name,
p.owner,
p.queue_table,
t.correlation_id
FROM applsys.fnd_svc_components t,
applsys.wf_agents o,
dba_queues p
WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
AND p.owner || '.' || p.name = o.queue_name
AND t.component_type LIKE 'WF_%AGENT%';
Select all workflow items for a given item type
SELECT item_type,
item_key,
to_char(begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
to_char(end_date,
'DD-MON-RR HH24:MI:SS') end_date,
root_activity activity
FROM apps.wf_items
WHERE item_type = '&item_type'
AND end_date IS NULL
ORDER BY to_date(begin_date,
'DD-MON-YYYY hh24:mi:ss') DESC;
Notifications sent by a given workflow
select wn.notification_id nid,
wn.context,
wn.group_id,
wn.status,
wn.mail_status,
wn.message_type,
wn.message_name,
wn.access_key,
wn.priority,
wn.begin_date,
wn.end_date,
wn.due_date,
wn.callback,
wn.recipient_role,
wn.responder,
wn.original_recipient,
wn.from_user,
wn.to_user,
wn.subject
from wf_notifications wn, wf_item_activity_statuses wias
where wn.group_id = wias.notification_id
and wias.item_type = 'WSHSUPI'
and wias.item_key = 'CMS21408';
Find the Activity Statuses for all workflow activities of a given item type and item key
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = '&item_type'
AND ias.item_key = '&item_key'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.item_type = '&item_type'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
UNION ALL
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses_h ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = '&item_type'
AND ias.item_key = '&item_key'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.item_type = '&item_type'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY 2, 1;
Get a list of all Errored Workflow Activities for a given item type/ item key
SELECT ac.display_name activity,
ias.activity_result_code RESULT,
ias.error_name error_name,
ias.error_message error_message,
ias.error_stack error_stack
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = '&item_type'
AND ias.item_key = '&item_key'
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.item_type = '&item_type'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY ias.execution_time’
Error Process Activity Statuses
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.parent_item_type = '&item_type'
AND i.parent_item_key = '&item_key'
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
UNION ALL
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses_h ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.parent_item_type = '&item_type'
AND i.parent_item_key = '&item_key'
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY 2, 1;
Error Process Errored Activities
SELECT ac.display_name activity,
ias.activity_result_code RESULT,
ias.error_name error_name,
ias.error_message error_message,
ias.error_stack error_stack
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.parent_item_type = '&item_type'
AND i.parent_item_key = '&item_key'
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY ias.execution_time;
Attribute Values
SELECT NAME attr_name,
nvl(text_value,
nvl(to_char(number_value),
to_char(date_value))) VALUE
FROM wf_item_attribute_values
WHERE item_type = upper('&item_type')
AND item_key = nvl('&item_key',
item_key)’
Count of all workflow deferred activities based
SELECT COUNT(1),
was.item_type
FROM apps.wf_items wi,
apps.wf_item_activity_statuses was,
apps.wf_process_activities pra
WHERE wi.item_type = was.item_type
AND wi.item_key = was.item_key
AND wi.end_date IS NULL
AND was.end_date IS NULL
AND was.activity_status = 'DEFERRED'
--AND was.item_type = 'REQAPPRV'
AND was.item_type = wi.item_type
AND pra.instance_id(+) = was.process_activity
GROUP BY was.item_type;
Make sure the Index WF_DEFERRED_TABLE_M_N1 exist. This index is created on the corrid olumn of the WF_DEFERRED_TABLE_M queue table.
select index_name, column_name, table_name, INDEX_OWNER
from dba_ind_columnswhere table_name like '%WF_DEFERRED_TABLE_M%';
The following query will show workflows with high volumes of looping activity. If that is the case, it is required to change the workflow which is causing the looping. The workflow has to be changed as soon as possible otherwise it can generate several rows in the WF tables.
select sta.item_type ITEM_TYPE,
sta.item_key ITEM_KEY,
count(*) COUNT,
wfi.begin_date BEGIN_DATE,
wfi.end_date END_DATE,
decode(wfi.end_date, NULL, 'Run $FND_TOP/sql/WFSTAT.SQL to pursue closing item',
('exec WF_PURGE.ITEMS('''||STA.ITEM_TYPE||''','''||STA.ITEM_KEY||''',SYSDATE,FALSE);')
) STATEMENT
from wf_item_activity_statuses_h sta,
wf_items wfi
where sta.item_type = wfi.item_type
and sta.item_key = wfi.item_key
and wfi.item_type like nvl('FECWBR','%')
group by sta.item_type,
sta.item_key,
wfi.USER_KEY,
wfi.begin_date,
wfi.end_date
having count(*) > 300
order by 3 desc;
Activities are not Processed by Workflow Background Engine
Activities are in READY status in WF_DEFERRED_TABLE_M queue even though the Workflow Background engine program was already executed.
First review if the aq_tm_processes parameter which needs to be higher than zero to dequeue the messages from WF_DEFERRED_TABLE_M queue.
How to check the current value of aq_tm_processes?
SELECT value
FROM v$parameter
WHERE name = 'aq_tm_processes';
If this value is equal to zero, please contact the DBA to change it to 1 or higher.
If aq_tm_processes parameter is different than zero and the messags are still not dequeued from WF_DEFERRED_TABLE_M queue then the recommendation is to re-create this queue as follows:
a. Stop the Workflow background engine programs in running status
b. Recreate the wf_deferred_table_m queue by executing:
sqlplus wf_account/wf_account @$FND_TOP/sql/wfbkgbld wf_schema
Example Syntax:
sqlplus apps/apps @$FND_TOP/sql/wfbkgbld applsys
c. Execute the wf background engine program again
d. Retest the issue.
Note.1176723.1 also contains the step by step instructions to recreate the WF_DEFERRED_TABLE_M queue.
References:
11.i - 12 How to Resolve the Most Common Workflow Background Engine Problems (Doc ID 466535.1)SRDC - Data Collection Request for EBS: Workflow Background Engine (Doc ID 2093946.1)
Workflow Scripts (Doc ID 183643.1)
FAQ: Best Practices For Custom Order Entry Workflow Design (Doc ID 402144.1)
