Workflow mails not moving after a fresh clone

After a fresh clone, notification mails sent through Workflow Notification Mailer were not going out. These were the steps taken to fix this issue:

a. Shut down the workflow listeners.

b. Follow the Note 372933.1 and recreate the wf_notification_out queue as follows:

sqlplus apps/ @$FND_TOP/patch/115/sql/wfntfqup.sql APPS
APPLSYS

c. Start up the workflow listeners.

The note 372933.1 describes How to purge e-mail notifications from the workflow queue so the e-mail is not sent.

I found another metalink note 432663.1 (Best Practice for Enabling Email Events in an Oracle EBS Testing Environment) authored by an Oracle customer which recommends:

Infrastructure Setups (One-time)

1. Created a generic email account TestOracle@yourdomain.com. In our case, we use Microsoft Exchange/Outlook with Active Directory.

2. Set the permissions of the INBOX folder of the generic email account to allow a development team distribution list (DL) to have OWNER privilege.

a. It's easier to use a distribution list to open up and manage access to the INBOX folder. This is an added benefit as it exposes the whole team to email activity generated during testing.

b. The OWNER privilege will allow any test team member of the distribution list to read and reply to Oracle generated email notifications.

3. If running Purchasing, we take the additional steps of setting up these default usernames in the system. By doing this in production, we establish default testing accounts. In production these accounts are disabled and re-enabled only in our testing environments. How to structure these accounts as people within the Purchasing Hierarchy is beyond the scope of this document. Listed below are some suggested people and levels:

TEST_APPROVER I
TEST_APPROVER II
TEST_APPROVER III
TEST_BUYER
TEST_REQ

Post Cloning Setups (After Each Clone)

There are numerous post cloning activities that are performed on an instance taken from the production copy. Below are the specific steps we used to enable email events and the use of a generic email account.

1. Update wf_notifications

set status = 'CLOSED',
mail_status = 'SENT',
end_date = sysdate;

2. Update fnd_user

set email_address = 'TestOracle@yourdomain.com ';

3. Update per_people_f

set email_address = 'TestOracle@yourdomain.com ';

4. Update po_vendor_sites_all =

email_address = 'TestOracle@yourdomain.com',
remittance_email = 'TestOracle@yourdomain.com';

5. Purge Workflow data :

Run the "Purge Obsolete Workflow Runtime Data" concurrent program

6. If test usernames are implemented, re-enable these accounts with the following code snippet:

 UPDATE apps.fnd_user
        SET last_update_date = SYSDATE,     -- set update date
            last_updated_by = -1,           -- set to ANONYMOUS
            password_accesses_left = NULL,           
            password_lifespan_accesses = NULL,       
            password_lifespan_days = NULL,
            end_date = NULL                 -- activate user account
      WHERE user_name = 'TEST_APPROVER I'
         OR user_name = 'TEST_APPROVER II'
         OR user_name = 'TEST_APPROVER III'
         OR user_name = 'TEST_BUYER'
         OR user_name = 'TEST_REQ'

7. Alerts can be selectively disabled or changed based on your preferences and the type of alert.

Using the generic email account

Based on the version of Microsoft Outlook, the command to open another user's folder might be different.

File > Open > Other User's Folder

Summary

These simple steps allow us full use of email services in our testing environments and provide our developers with testing accounts for use in Requisition/PO creation, approval and generation.

I am pretty sure if the DBAs had purged the workflow data and cleaned up workflow tables as suggested above, this problem would not have occurred.