Oracle Apps DBA: Workflow Notification Mailer - Mails incorrectly being moved to Discard Folder

In the recent past, we were faced with two separate issues pertaining to Workflow Notification Mailer in our production environment. We have OWF.H in our environment and of course, it is a java mailer. I will write about one situation here and the other in a separate post.

Here is the situation:

Inbound mail processing is erratic. Some mails are processed and some are not.

Symptoms:

1. Outbound notifications are working fine.
2. Inbound notifications are not being processed correctly. Some mails are being processed and some legitimate mails are directly moved from "Inbox" to "Discard" folder.

Troubleshooting Tips:

I first started with a legitimate email which was incorrectly moved to discard folder directly from inbox. I noted the NID of the notification which is available in the email response. The next step was to see if something was obviously wrong with the notification.. like for example the requisition was approved from within application and hence the notification was discarded. This was achieved by querying up the records for this NID from wf_notifications table. Everything seemed OK there.

The next step was to scan through the log files for the workflow notification mailer in the production environment by searching for this specific NID. I was able to locate log entries which relating to sending out the mail with this NID. However, there was no record of any inbound mail with this specific NID. This enabled me to conclude that the production workflow notification mailer never was aware of this email which came to the inbox.

This can happen in following situation amongst others:

1. Some rules are defined at the mailbox level that move the specific mails automatically
2. Some other mailer is also working on the same mailbox
3. Human intervention

In our case, I verified that rules were not defined at the mailbox level and there was no human intervention.

The remaining candidate was some other notification mailer accessing the same mailbox.

That pointed out to the latest refresh that happened from this production environment. I verified in the target instance and i was able to locate these NIDs in the mailer logs. That suggests that the portion of the refresh process which addresses the Mailer related settings was overlooked.

Now, as far as the mystery of why some legitimate notifications were being sent to discard folder is concerned, here is what i discovered:

There are two scenarios possible:

1. Set of notifications (or the originating processes) in production, that were created before the target database was refreshed.
2. Set of notifications (or the originating processes) in production, that were created after the target database copy was made.

In the first case, since the target database already had the relevant data about the notifications and hence it actioned (i.e., either approved or rejected) in the target (dev) database.

In the second case, the dev database did not have the entries for those NIDs and hence was moved to discard as "bad" responses.

Finally, there was a third category which were processed by the legitimate production mailer which was all ok.

Hence, the mailes in "process" folder does not essentially mean that notification was updated appropriately in the production database. This needs to be addressed on a case to case basis and that is where my developer friends can contribute.

- Aravind Kamath