Wednesday, May 28, 2008

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.

7 comments:

Nagamohan Uppara said...

Gareth,
Great post! Just wanted to add regarding the dreaded messages related to Alert messages.wfaqback.sql is called within the wfntfqup.sql to back up all these messages related to alerts. This message should be appearing only if this back up gets into trouble.All these backed up messages are re-enqueued.

PS: Either you are vacationing or into something big! Very silent these days as I do not see your updates on the forum or blog posts :)

Thanks
Nagamohan

Gareth said...

Hi Nagamohan,
Thanks for the comments - yeah had a frantic couple of months, a bunch of posts on the drawing board though.
Regards,
Gareth

Nagamohan Uppara said...

Gareth
Looking forward to your posts!
Thanks
Nagamohan

Oracle DBA said...

nice article...We knew the web method only..Good to see the sql method

Raghu said...

Hi,

I have a issue with Alerts.Alert is not send any mail..But my workflow is up and running.I am receiving test mail from WF.

How can i solve this issue?

Gareth said...

Hi Raghu,
Check your Alert setup to ensure Workflow mailer option. Check that if you are using scheduled emails that the Periodic Alert Scheduler is running. Any issues, suggest you create thread on forums.oracle.com

Regards,
Gareth

Vikram Das said...

Hi Gareth,

Do you have any idea how to programatically decrypt the IMAP_PASSWORD in fnd_svc_comp_param_vals ?

- Vikram