Workflow Override Email Address

It is very common requirement to stop workflow notifications going out of the system, particularly from Development instances and Test instances. Oracle have given a functionality “workflow override email address” to achieve this requirement.

In many companies this task is done by Database Administrators (DBA) but incase you need to do it yourself you follow the below steps.  There are two ways to setup this: 

1. Login to Oracle Apps preferably with a user who is "Workflow System Administrator".

Navigate to “Workflow Administrator Web Applications” Responsibility
Expand "Oracle Applications Manager"

Click "Workflow Manager"

Click "Service Components"

Click “Workflow Notification Mailer”

Click "Set Override Address"

Once you provide the email address you will receive a verification code for the first time which you need to enter it back in the above page to complete the process. Verification field will be enabled after you provide the email address.
2.  From backend, connect to the database using apps user and run below update statement.

update fnd_svc_comp_param_valsset    parameter_value = '<email address>'where  parameter_value = 'NONE'and    parameter_id =( select parameter_idfrom   fnd_svc_comp_params_tlwhere  display_name = 'Test Address');
commit;

Workflow Notification Mailer (Oracle E-Business Suite) Status, Stop and Start from backend.

Run all scripts as 'APPS' user.

1) Workflow Notification Mailer Status:

select component_status
from fnd_svc_components
where component_id =
(select component_id
from fnd_svc_components
where component_name = 'Workflow Notification Mailer');

2) Workflow Mailer Services / Service components Status:

select fcq.USER_CONCURRENT_QUEUE_NAME Container_Name, DECODE(fcp.OS_PROCESS_ID,NULL,'NotRunning',fcp.OS_PROCESS_ID) PROCID,
fcq.MAX_PROCESSES TARGET,
fcq.RUNNING_PROCESSES ACTUAL,
fcq.ENABLED_FLAG ENABLED,
fsc.COMPONENT_NAME,
fsc.STARTUP_MODE,
fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, APPS.FND_CP_SERVICES fcs, APPS.FND_CONCURRENT_PROCESSES
fcp, fnd_svc_components fsc
where fcq.MANAGER_TYPE = fcs.SERVICE_ID
and fcs.SERVICE_HANDLE = 'FNDCPGSC'
and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
and fcq.concurrent_queue_id = fcp.concurrent_queue_id(+)
and fcq.application_id = fcp.queue_application_id(+)
and fcp.process_status_code(+) = 'A'
order by fcp.OS_PROCESS_ID, fsc.STARTUP_MODE;

3) Workflow Mailes Status:

select  *
from    wf_notifications
where   1=1
and     begin_Date >= sysdate-1
--and     mail_status='FAILED'
order by begin_Date desc;

4) Stop Notification Mailer from backend:
This will deactivate notification mailer.  Run this script as apps user.

declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);
commit;
end;
/

5) Start Notification Mailer from backend:

Run this script as apps user.

declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
commit;
end;
/