Tuesday, August 07, 2007

Handy script to find out eligible workflow data for purging

I have the "purge Obsolete Workflow Runtime Data" concurrent request scheduled to run on a weekly basis but I find out that this request is not purging all data that can be purged, so I searched metalink for similar cases and found more that one note talking about the same issue, anyway one of the notes (165316.1) (bde_wf_data.sql - Query Workflow Runtime Data That Is Eligible For Purging) has the bde_wf_data.sql script that can be downloaded from metalink, this script will create a bde_wf_data.lst file that looks like a script but it needs some cleansing, the script has commands like the following
Ex.

exec WF_PURGE.ITEMS('POAPPRV','10289-20146',SYSDATE,FALSE);
exec WF_PURGE.ITEMS('REQAPPRV','4941-13066',SYSDATE, FALSE);

Which will purge data eligible to be purged, also at the end of the .lst file there are statements to delete/build the tables stats for the following tables
WF_ITEM_ACTIVITY_STATUSES, WF_ITEM_ACTIVITY_STATUSES_H, WF_ITEM_ATTRIBUTE_VALUES, WF_ITEMS, WF_NOTIFICATIONS, WF_NOTIFICATION_ATTRIBUTES

Since the script do a lot of purging/delete form those tables so the stats needs to be build again

EXEC DBMS_STATS.DELETE_TABLE_STATS(ownname=>'APPLSYS',tabname=>'WF_ITEM_ACTIVITY_STATUSES');
EXEC FND_STATS.GATHER_TABLE_STATS(ownname=>'APPLSYS',tabname=>'WF_ITEM_ACTIVITY_STATUSES',percent=>10,granularity=>'DEFAULT');

And during the search I found note (144806.1) (A Detailed Approach To Purging Oracle Workflow Runtime Data) which I recommend so much for reading

have a nice free bugs day
fadi

2 comments:

Anonymous said...

Dear Faid,
I dont know whether it is the right place ask you something or not. I am new to Oracle Apps and facing a problem in GL while opening new Set Of Book, after entring the information when I tried to save the record, it gives me following error

"There is an error in your fiscal calendar. Not all dates between 01-Jan-01 and 01-FEB-01 are assigned
to a period. Please fix the calendar or pick the another one"

I have posted this Error on couple of forums but no one replies that is why I am asking you this problem.

Best Regards
Ahmer

fhasweh said...

hi ahmer,
you error seems a setup issue, i will try to help you but ot gain time can you please post the same error under i am sure you will get better help there since i am more a technical than a functional http://forums.oracle.com/forums/forum.jspa?forumID=111

fadi