Clearing Email Notifications and manually setting the Workflow/Java Notification Mailer Override/Test Address via SQL for the Oracle eBusiness Suite

Update: 14-Mar-2012 Added supported back-end method to update Workflow Notification Mailer attributes. Warning: This post also contains unsupported SQL updates, use at your own risk!

I think the Workflow Notification Mailer is excellent, but one of my pet hates is the setup process for a mailer. I never seem to be able to get it setup cleanly. One thing that really bugs me is that the Service Component Manager (Workflow Mailer Service) needs to be running before you can set the "Override Address". So when setting up a mailer you have to be very careful that the "Manual" startup option is set or your Mailer will start after configuration or when you startup the Workflow Mailer Service. Similarly caution is needed when cloning non-Prod environments from Prod when the Prod Override Address is not set, but Test/Dev etc need to be set.

Additionally, there's no button anywhere with a label like "Click me to update all Mail_Status pending Notifications as Sent" which is what the following accomplishes:

update WF_NOTIFICATIONS
set mail_status = 'SENT'
where mail_status = 'MAIL';

# Note: Change the apps_password in the following command
sqlplus apps @$FND_TOP/patch/115/sql/wfntfqup.sql APPS apps_password APPLSYS

Okay, so here's how you get the current Override Address via SQL on the backend:

select fscpv.parameter_value
from   fnd_svc_comp_params_tl fscpt
,      fnd_svc_comp_param_vals fscpv
where  fscpt.display_name = 'Test Address'
and    fscpt.parameter_id = fscpv.parameter_id; 

And now here's a very unsupported way to update the Override Address - please try this on a Test environment first to make sure it works for you! Note this will update all Mailers you have setup. Recommended to stop and start the mailers after making this change. Also make sure you change the email address to your Test Address and change the NONE to your current value (from previous SQL) if necessary:

update fnd_svc_comp_param_vals
set    parameter_value = 'MyTestAddress@MyCompany.com'
where  parameter_value = 'NONE'
and    parameter_id =
( select parameter_id
 from   fnd_svc_comp_params_tl
 where  display_name = 'Test Address'
);

commit;

Update: 14-Mar-2012 - There is a supported way to make back-end updates to Workflow Notification Mailer (and other service components). This is documented in Support Note 1399464.1. In short run the following and follow the prompts:

sqlplus apps @$FND_TOP/sql/afsvcpup.sql

PS. See Metalink Note 467229.1 if you get the dreaded "ORA-20000: Oracle Error = -20000 - ORA-20000: 330 Alert messages exist in WF_NOTIFICATION_OUT. Ensure these messages are processed before running wfntfqup.sql"

PPS. Anil has a nice post clear post where the Override Address (Test Address) lives these days here.