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’,
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) ;
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:
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’