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

Workflow again

-an update on this we applied the patch and it solved our issue
Some end-users open cases with the support claiming that they don’t receive all the email notifications related to some of their purchase orders, I thought that maybe their email were spelled wrong or maybe they changed their email preference so they don’t receive an emails at oracle application level like the last time, I checked both but none of the above applies on the case so I had to check an one of there POs on the wf_notifications table, I run the following query

select * from wf_notifications where subject like '%200700001052%'

and the email_status value was ERROR so I took the notification_id and search for it in the emails that were sent by the notification mailer to the sysadmin and I found an email with the following content

"Event Error Name: -20001Event Error Message: ORA-20001: Oracle error -20001: ORA-20001: APP-FND-02500: Error occurred during product initialization for MO when executing 'begin MO_GLOBAL.INIT; end;'.SQLCODE = -20001 SQLERROR = ORA-20001: APP-FND-02902: Multi-Org profile option is required. Please set either MO: Security Profile or MO: Operating Unit profile option.has been detected in FND_GLOBAL.INITIALIZE."

I check that error on metalink and I found 5 notes saying that I must apply patch (5209533) p5209533_11i_GENERIC.zip

I will apply that patch on my test/clone instance this weekend and I will update you if the issue was resolved.

For more info you can check note (393847.1) or search for the error/patch


Hope that helped
Fadi