Script to Start/Stop Oracle EBS Workflow Mailer From Backend

You might hurry to start or stop the workflow mailer, but logging in to the Oracle Application and navigating might take more time. So you might be looking for the script to start the workflow mailer from the backend. When I did the same I found three different scripts for each operation. I am looking for something to increase the efficiency of my work I shouldn't be saving three different scripts to check the status, start and stop the workflow mailer. I wanted to create a script to get the user input interactively and perform actions based on the user input something like adadmin. But we cannot do that PL/SQL cause of the limitation of PL/SQL. It is easy to achieve in a shell script, but you need to copy the code and save it on the server again. Please find the PL/SQL script to start, stop and check the status of the workflow mailer below.

set serveroutput on
prompt
prompt
prompt******************************************************************************************************************
prompt******************************************************************************************************************
prompt 1. Start the workflow
prompt 2. Stop the workflow
prompt 3. Check the status
prompt
prompt******************************************************************************************************************
prompt******************************************************************************************************************
declare
p_retcode number;
cur_inp number :=&cur_inp ;
WF_STATUS varchar2(20);
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
-- Find mailer Id
-----------------
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
-- Find workflow status
SELECT component_status
INTO WF_STATUS
  FROM apps.fnd_svc_components
 WHERE component_id =
          (SELECT component_id
             FROM apps.fnd_svc_components
            WHERE component_name = 'Workflow Notification Mailer');
-- Print the options
dbms_output.put_line('Current workflow status is:  ' || WF_STATUS); 
 
IF ( cur_inp = 1 ) THEN  
dbms_output.put_line('Starting the Workflow Mailer' ); 
----------------
-- Start Mailer
-----------------
fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
commit;
ELSIF ( cur_inp = 2 ) THEN
dbms_output.put_line('Stopping the Workflow Mailer' ); 
----------------
-- Stop Mailer
-----------------
fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);
commit;
ELSIF ( cur_inp = 3 ) THEN
-- Find workflow status
SELECT component_status
INTO WF_STATUS
  FROM apps.fnd_svc_components
 WHERE component_id =
          (SELECT component_id
             FROM apps.fnd_svc_components
            WHERE component_name = 'Workflow Notification Mailer');
-- Print the options
dbms_output.put_line('Current workflow status is:  ' || WF_STATUS);

ELSE
dbms_output.put_line('Please enter Valid input' );
END IF;
end;
/

Please find the sample output below:

SQL> ******************************************************************************************************************
SQL> ******************************************************************************************************************
SQL> SQL>
SQL> 1. Start the workflow
SQL> 2. Stop the workflow
SQL> 2. Check the status
SQL>
SQL> ******************************************************************************************************************
SQL> ******************************************************************************************************************
SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51   52   53   54   55
Enter value for cur_inp: 3
old   3: cur_inp number :=&cur_inp ;
new   3: cur_inp number :=3 ;
Current workflow status is:  RUNNING
Current workflow status is:  RUNNING

PL/SQL procedure successfully completed.