How to check/change Workflow Notification mailer configuration from backend

We already know How to check/change the Workflow Notification mailer configuration from OAM.

Today I am going to speak about How to check/change  Workflow Notification mailer configuration from the backend ie. database side

Workflow Mailer/agent configuration are stored in these tables

How to take backup of all the workflow mailer configuration from backend

Suppose we want to take backup of all the workflow mailer configuration before running Big patches or autoconfig, Then here  is the script to do it

spool wf_mailer.log
set lines 130
set pages 200
col value format a30
select p.parameter_id,p.parameter_name,v.parameter_value value
from apps.fnd_svc_comp_param_vals_v v,
apps.fnd_svc_comp_params_b p,
apps.fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in
('OUTBOUND_SERVER', 'INBOUND_SERVER',
'ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')
order by p.parameter_name;

Again to list down all the major Notification mailer configurations, run the below script
Script to see workflow configuration

SQL> select p.parameter_id,p.parameter_name,v.parameter_value value
from fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p,
fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in
('OUTBOUND_SERVER', 'INBOUND_SERVER',
'ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')
order by p.parameter_name;

How to set override Address in workflow mailer configuration from backend

We know how to set the test address from the front end and it needs a verification code to set it. There is a script to do it from the backend also. You do not need the verification code to set the override address using the below script

update fnd_svc_comp_param_vals
set parameter_value = '&1'
where parameter_id =
( select parameter_id
from fnd_svc_comp_params_tl
where display_name = 'Test Address'
);

Change Workflow Notification mailer configuration from backend using Oracle Supplied Script

Oracle supplied script to do it from backend is

Use the script $FND_TOP/sql/afsvcpup.sql

This will enable you to change any and all of your Mailer components.

A. Run the script
B. It will show you the components – pick the comp id for the Workflow Mailer – default is 10006
C. then it will show you the parameters and their values for the mailer
D. Pick the comp param id for the Override address (it will say Test Address ) – default is 10093

The script will display the following:

You have selected parameter: Test Address
The current value of the parameter: NONE

Enter a value for the parameter :

Retest the Workflow mailer and confirm the override now works as expected.

How to set inbound Password in workflow mailer configuration from backend

Similarly, we  can change the inbound account password from backup using the below script

update APPLSYS.FND_SVC_COMP_PARAM_VALS set PARAMETER_VALUE= '&1'
where PARAMETER_ID=
( select parameter_id from APPLSYS.FND_SVC_COMP_PARAMS_B where parameter_name = 'INBOUND_PASSWORD');

Related Links

Unable to Start Output Post Processor : Unable to Start Output Post Processor is one of the common problems faced. We try to solve the problem by applying the various technique
SQL to check the Workflow Agent Listener : Check out various SQL to check Workflow Agent Listeners are running, Wokflow Java Deferred Agent listener, Logfile for them
workflow notification mailer not sending emails : Check out this post for the most common Workflow Notification Mailer issues and resolution. , workflow notification mailer not sending emails