Suddenly users got huge old workflow notification mails

Issue : Users complained they got huge number of old notification mails

Action -

Very old items still there in wf_notification_out table.

1) SQL> select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid, decode(wfe.state,0,'0 = Ready',1,'1 = Delayed',2,'2 =  Retained',
3,'3 = Exception',to_char(substr(wfe.state,1,12))) State,
count(*)COUNT from applsys.WF_NOTIFICATION_OUT wfe group by wfe.corrid, wfe.state;
  2    3

CORRID                                             STATE              COUNT
-------------------------------------------------- ------------- ----------
APPS:POAPPRV:EMAIL_PO_PDF                         234 3 = Exception          1
APPS:ALR:                                          0 = Ready             123
APPS:REQAPPRV:PO_REQ_APPROVED_JRAD                 3 = Exception        1625
APPS:POAPPRV:PO_PO_HAS_BEEN_REJECTED               3 = Exception         380
APPS:OECHGORD:ISO_CANCEL_MSG                       3 = Exception          1
APPS:POAPPRV:PO_PO_APPROVE_PDF_OAF                 2 =  Retained         59
APPS:CS_MSGS:FYI_MESSAGE                           3 = Exception          7
APPS:ASOAPPRV:OA_FYI_TO_REQUESTER                  3 = Exception        339
APPS:UMXLHELP:LOGIN_HELP_MSG_PWD                   3 = Exception         11
APPS:FNDCMMSG:SIMPLE_REQ_COMPLETION                2 =  Retained          6
APPS:ASOAPPRV:OA_REQ_APPR_BY_ALL_APPR              3 = Exception        284


2) Stop Workflow notification mailer services

3)  Rebuild the Queue , Prior rebuild Q , please check all Mail status and OPEN status.

$ cd $FND_TOP/patch/115/sql/
$ ls -ltr wfntfqup.sql
-rwxrwxr-x    1 prodappl dba           11862 Mar 25 2010  wfntfqup.sql
$ sqlplus apps/*****

SQL> @wfntfqup.sql apps ****** applsys

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.95

Commit complete.

Elapsed: 00:00:00.01
**** TEMPORARY TABLES / AQs created ****

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.96
**** Messages backed up ****

Commit complete.

Elapsed: 00:00:00.00
**** wfaqback.sql completed,  executing SQLs from wfntfqup

PL/SQL procedure successfully completed.

Elapsed: 00:00:13.18
***  invoking wfaqrenq.sql ******
*** Re-enqueing messages

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17

Commit complete.

Elapsed: 00:00:00.00
***** Re-enqueue OF Alerts completed *****

4) After that Q only having new items:

SQL> select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid, decode(wfe.state,0,'0 = Ready',1,'1 = Delayed',2,'2 =  Retained',
  2  3,'3 = Exception',to_char(substr(wfe.state,1,12))) State,
count(*)COUNT from applsys.wf_notification_out wfe group by wfe.corrid, wfe.state;  3

CORRID                                             STATE              COUNT
-------------------------------------------------- ------------- ----------
APPS:ALR:                                          0 = Ready              6
APPS:WFERROR:DEFAULT_EVENT_ERROR                   0 = Ready              2
APPS:OMERROR:OMERROR_MSG                           0 = Ready              2
APPS:REQAPPRV:PO_REQ_APPROVE_JRAD                  0 = Ready              4

Thanks,