Oracle Workflow - Version 11.5.10.2 to 12.2 [Release 11.5.10
to 12.2]
Oracle Order Management - Version 12.2.4 to 12.2.4 [Release
12.2]
Information in this document applies to any platform.
GOAL
Failure in a certain workflows, many error notifications can
be sent to end users or to SYSADMIN notifying them about the error.
After fixing the problem of the failed workflow, all error
notifications still stayed in users worklists waiting to be closed, and it is
required to remove them all to save the overhead done by end user if they close
them one by one.
End users or sysadmin have many Error notifications in the
worklist and that are required to be closed. This document explains the steps
involved in achieving the goal.
SOLUTION
1. Use one of the error notifications to get an item_type
from the following select:
SQL>select message_type from wf_notifications where
notification_id = <NID>;
1-1 prevent cursor from closing recent records:
SQL>select
act.item_type , act.item_key
from wf_item_activity_statuses
act
,wf_notifications n
,wf_items itm
where act.notification_id =
n.notification_id
and act.item_type = itm.item_type
and act.item_key = itm.item_key
and itm.end_date is null
and n.begin_date< '[specify a
date]' --to prevent recent items from being closed/removed.***
and act.item_type =
'<message_type>' -- value returned in step 1
and act.activity_status in
('ERROR','NOTIFIED')
and n.status = 'OPEN'
and act.assigned_user =
'SYSADMIN';
2. Use the following
script to abort all error workflows that has currently notifications with
status OPEN:
Declare
cursor c_item_keys is
select act.item_type , act.item_key
from wf_item_activity_statuses
act
,wf_notifications n
,wf_items itm
where act.notification_id =
n.notification_id
and act.item_type = itm.item_type
and act.item_key = itm.item_key
and itm.end_date is null
and n.begin_date< '[specify a
date]' --to prevent recent items from being closed/removed.***
and act.item_type =
'<message_type>' -- value returned in step 1
and act.activity_status in ('ERROR','NOTIFIED')
and n.status = 'OPEN'
and act.assigned_user = 'SYSADMIN';
counter number;
Begin
counter := 1 ;
for item in c_item_keys loop
wf_engine.abortprocess (item.item_type, item.item_key);
counter := counter + 1 ;
if counter > 1000 then
counter := 1 ;
commit;
end if;
end loop;
commit;
End;
NOTE: - Replace the <message_type> by the
message_type value returned in step 1.
- This script will
remove error notifications from sysadmin.
Replace sysadmin by the end user needed or comment the line to run the
script for all end users.
3. Run the concurrent
request: "Purge Obsolete Workflow
Runtime Data".
NOTE: - In some cases
the error workflow may have a child workflow, for example, POERROR workflow may
fail to send a notification to a certain user, so that it will generate a
WFERROR notification to sysadmin. In
that case one has to run the script in step 2 first for WFERROR workflow.
An item key is not required to run the Purge Obsolete
Workflow Runtime Data request. Running
it without use of an item key, all eligible data will be purged.