Starting Stopping Workflow Notification Mailer Using Shell Script


I am sharing the backend way to start stop notification mailer along with shell script example


Find current 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');

Status Can be :
    RUNNING
   STARTING
   STOPPED_ERROR
   DEACTIVATED_USER
   DEACTIVATED_SYSTEM
   
   
       
Stopping Workflow Mailer
   
   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;
/

Start Workflow Mailer Script

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;
/

Shell Script to check the status of mailer and start it automatically.

$ cat wf_auto_start.sh
#!/bin/bash
#Built by Himanshu

. ~/.bash_profile

sqlplus -s $APPSLOGIN << EOF >/tmp/WF_start.log
set head off;
set echo off;
set feedback off;
SELECT component_status FROM fnd_svc_components WHERE component_id =(SELECT component_id FROM fnd_svc_components WHERE component_name ='Workflow Notification Mailer');
exit;
EOF
sed -i '/^$/d' /tmp/WF_start.log
if [ `cat /tmp/WF_start.log` == "DEACTIVATED_SYSTEM" ]
then
sqlplus -s $APPSLOGIN << EOF
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;
/
exit;
EOF
fi

Cron Schedule the Job

0 * * * * /home/applmgr/scripts/wf_auto_start.sh > /tmp/wf_auto_start.log 2>&1



If you like please follow and comment