How to check/change Workflow Notification mailer configuration from backend



Last updated on September 29th, 2017 at 05:46 pm

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

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

Workflow Mailer/agent configuration are stored in these tables

fnd_svc_comp_param_vals : It has the values

FND_SVC_COMP_PARAMS_B: It has the parameter name and Parameter id

fnd_svc_components: It list all the workflow mailer/agent components

 

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 configuration,run the below script
Scipt 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;

We know how to set the test address from front end and it need verification code to set it.There is script to do it from 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’
);

Oracle supplied script to do it from backend is

$FND_TOP/sql/afsvcpup.sql

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 and workflow related Concurrent Manager

How to check the status/stop/start Workflow Notification Mailer from Backend

SQL to check the AQ setup and Workflow Agent Listener/WF Java Deferred Agent Listeners are running

Troubleshooting Workflow Notification Mailer issues


Leave a Reply