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’

 

BEGIN

WF_ITEM_ACTIVITY_STATUS.Create_Status(itemtype  => ‘WFERROR’,

itemkey=>’10037187′,

actid=> 255020,

status=> ‘COMPLETE’,

result=>null,

beginning=> null,

ending=> sysdate,

suspended=> FALSE,

newStatus=> FALSE);

END;

 

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:

 

BEGIN

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

LOOP

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

END LOOP;

END;

 

Helpful Views

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

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

 

WF_ITEM_ATTRIBUTES_VL

select * from apps.WF_ITEM_TYPES_VL where

name=’WFERROR’

WF_ITEM_ACTIVITY_STATUSES_V

 

wf_purge.notifications

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

BEGIN

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

END;

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’