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

Open Workflow notifications list is very slow

Here I am publishing a note which I already published on metlaink through the Customer Knowledge Exchange program the note id is 428573.1, they had this program a couple of months ago and I managed to publish two notes and I will be sharing them here with you.

I faced a slow performance issue which only applies on the notifications list page, opening the workflow notifications list to see a list with two notifications when the problem happened used to take a lot of time (more that 20 minutes and most of the times it used to time out) so I followed the below to solve the issue

SQL> select message_type,count(*) from WF_NOTIFICATIONS group by message_type;

MESSAGE_ COUNT(*)
-------- ----------
AZNM000 3
FERASWF 8
POAPPRV 121
POERROR 29
PORCPT 38
POSDSALE 332
REQAPPRV 131
WFERROR 1137436

8 rows selected.
SQL> select count(*) from WF_NOTIFICATIONS where status='OPEN'
and MESSAGE_TYPE='WFERROR'

998976 rows selected.

I find out that I have a lot of the WFERROR message_type and most of them with an open status so I took a backup of the table just incase any thing went wrong using the following statement

SQL> CREATE TABLE WF_NOTIFICATIONS_bck AS SELECT * from WF_NOTIFICATIONS;

Table created.

Then I run the following update statement to set the status of all open notifications of the WFERROR type

SQL> update wf_notifications
set status='CLOSED', mail_status='CANCELED', end_date=sysdate
where status='OPEN'
and MESSAGE_TYPE='WFERROR' ;

998976 rows created
SQL> commit;

After that I tried the notifications list page it did not take more than 3 seconds and every thing went back to normal, so I scheduled a database job that run every month to close all the open notifications with message_type wferror.

To avoid this problem from happing again I am make sure that all the sysadmin notifications are closed and I also have monthly task to check that the above job is running.

i hope that helped
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

Small issue with workflow email notifications

Well been busy last week and couldn't update the blog, before a couple of days tow users called me complaining that they stopped receiving emails informing them that they have a new notifications that needs their approval, well to be honest I thought I have a serious issue because both users called in different time at the same day, I check the workflow email server setup and it is working fine, after a little investigation I find out that in some how both users preferences got changed, so I change it back as follow

-Once logged into Applications, go to preferences link in the Home page . - At the bottom of that page is the Header - Notifications, Email Type - Select HTML Mail (It was empty)

And every thing back to normal.

Fadi

New credentials for oracle applications

Oracle has lunched a new certification programs after they have changed their apps dba OCP program then new certifications are as follow:

-Oracle 11i System Administrator Certified Expert. (1Z0-232)
Exam Number:
1Z0-232
Duration:
120 minutes
Associated Certifications:
TBD
Number of Questions:
87
Exam Price:
$195 USD
Passing Score:
60%

-Oracle 11i Workflow Certified Expert. (1Z0-231)
Exam Number:
1Z0-231
Duration:
90 minutes
Associated Certifications:
TBD
Number of Questions:
72
Exam Price:
$195 USD
Passing Score:
59%


If you pass any of the above exams you will consider an expert neither an ocp nor oca. Also both exams doesn't require any pre-requests exames.

Fadi