Purging Workflow – Updated version

Purging Workflow Item Type and Notifications

In the past, I’ve blogged about how to set workflow item_types so they can be purged using scripts in $FND_TOP. One such script is wf_purge.total. Below, I give an example of using wf_purge.total to item_types where the PARENT_ITEM_TYPE is null. I also write about two methods to purge notifications, wf_purge.notifications and manually updating the wf_notifications table and set status=’CLOSED’, mail_status=’SENT’, and END_DATE=sysdate-1.

This blog also  introduces using the WF_ITEM_ACTIVITY_STATUS.Create_Status procedure to set the status to ‘COMPLETE’ from ‘NOTIFIED’. This can be used to change the status.

— When STATUS=’NOTIFIED’ it can’t be purged. Use API to set the STATUS to ‘COMPLETE’



WF_ITEM_ACTIVITY_STATUS.Create_Status(itemtype  => ‘WFERROR’,


actid=> 255020,

status=> ‘COMPLETE’,


beginning=> null,

ending=> sysdate,

suspended=> FALSE,

newStatus=> FALSE);



Queries used to find WF_ITEMS that can’t be purged. It turns out the biggest offender was FAFLEXWF (41 million rows), because of a custom portion of the workflow for cash management had an error.


The next item_type with the most issues was WFERROR. This is very common. The problem with resolving WFERROR item_types, is you need to know the item_key, based on some criteria.


First, find the item_types that have issues, in this case the PARENT_ITEM_TYPE is null:


select count(*) as cnt, item_type from apps.wf_items where  PARENT_ITEM_TYPE is null group by item_type


Or, use the following to order by descending count:

select count(1) from apps.wf_items where item_type=’WFERROR’

group by PARENT_ITEM_TYPE order by 2 desc


Second, find the item_keys associated with the issue:


select item_type, item_key from apps.wf_items where item_type=’WFERROR’ and PARENT_ITEM_TYPE is null


Third, purge WFERROR item types where the parent_item_type is null using wf_purge.total, by looping through all the item_keys:



FOR c1_rec IN (select item_type, item_key from apps.wf_items where item_type=’WFERROR’and PARENT_ITEM_TYPE is null)


apps.wf_purge.total(c1_rec.item_type, c1_rec.item_key)   ;




Helpful Views

select * from all_views where view_name like ‘%WF%ITEM%’

select * from apps.WF_ITEMS_V where item_type=’FAFLEXWF’



select * from apps.WF_ITEM_TYPES_VL where





In this example, we will purge notifications, in this case, CREATEPO and passing it yesterday’s date as the END_DATE:


wf_purge.notifications(‘CREATEPO’, sysdate-1);


For example, we had nine wf_notifications where the message_type=’CREATEPO’ and the status was not ‘CLOSED’ or the mail_status was not ‘SENT’ or there was no end_date.

select * from apps.wf_notifications where message_type=’CREATEPO’

update apps.wf_notifications set status=’CLOSED’, mail_status=’SENT’, END_DATE=sysdate-1 where message_type=’CREATEPO’