-- Check the number of notification mailer processes. If the number is 0, the mailer is not running.

select running_processes

from FND_CONCURRENT_QUEUES

where concurrent_queue_name = 'WFMLRSVC';

-- Check the notification mailer status (should be “RUNNING”)

select component_status

from fnd_svc_components

where component_id =

    (select component_id

        from fnd_svc_components

     where component_name = 'Workflow Notification Mailer');

set serveroutput on

declare

 v_out number(10);

 v_sts wf_notifications.mail_status%type := '';

 v_waited integer := 0;

 v_max_wait_time integer := 3600; -- 60 min

 v_start_date date := NULL;

begin

  select sysdate into v_start_date from dual;

  v_out := wf_notification.send('<ROLE>',

              'CS_MSGS',

              'FYI_MESSAGE',

              sysdate,

              NULL,

              NULL,

              NULL,

              1);

  wf_notification.setattrtext(v_out,'OBJECT_TYPE','Testing Notification Mailer');

  wf_notification.setattrtext(v_out,'SENDER','Oracle Workflow');

  update wf_notifications set subject = 'Testing Notification Mailer' where notification_id = v_out;

  commit;

  -- Check mail status

  --------------------

  select mail_status into v_sts from wf_notifications where notification_id = v_out;

  v_waited := 0;

  while v_sts != 'SENT' and v_waited <= v_max_wait_time

  loop

     dbms_lock.sleep(10);

     v_waited := v_waited + 10;

     select mail_status into v_sts from wf_notifications where notification_id = v_out;

  end loop;

  commit;

  dbms_output.put_line(v_sts);

end;

/

      set serveroutput on

      declare

        p_retcode number;

        p_errbuf varchar2(100);

        m_mailerid fnd_svc_components.component_id%TYPE;

        m_status fnd_svc_components.component_status%TYPE;

        m_max_iterations number := 30;

        m_iterations number := 0;

      begin

        -- Find Notification Mailer component_id

        ---------------------------------------------------

        select component_id

          into m_mailerid

          from fnd_svc_components

         where component_name = 'Workflow Notification Mailer';

        -- Check the Notification Mailer's status

        ---------------------------------------------------

        select component_status

          into m_status

          from fnd_svc_components

         where component_id = m_mailerid;

        dbms_output.put_line('Current status: '||m_status);

        if m_status not in ('DEACTIVATED_USER','DEACTIVATED_SYSTEM','STOPPED_ERROR')

        then

            -- Stop Notification Mailer

            ---------------------------------

            fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);

            if p_retcode != 0

            then

              dbms_output.put_line('Could not stop Notification Mailer: '||p_errbuf);

            end if;

            commit;

        end if;

        -- Check the Notification Mailer's status

        ---------------------------------------------

        select component_status

          into m_status

          from fnd_svc_components

         where component_id = m_mailerid;

        while m_status not in ('DEACTIVATED_USER', 'DEACTIVATED_SYSTEM',

                 'STOPPED_ERROR') and m_iterations < m_max_iterations

        loop

              dbms_lock.sleep(10);

              select component_status

                into m_status

                from fnd_svc_components

               where component_id = m_mailerid;

              m_iterations := m_iterations + 1;

        end loop;

        if m_status in ('DEACTIVATED_USER','DEACTIVATED_SYSTEM','STOPPED_ERROR')

        then

           -- Start Notification Mailer

           ----------------------------

           fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);

           if p_retcode != 0

           then

              dbms_output.put_line('Could not start Notification Mailer: '||p_errbuf);

           end if;

        else

           dbms_output.put_line('Could not start Notification Mailer. Please, check it''s status');

        end if;

        commit;

      exception

        when others then dbms_output.put_line(SQLERRM);

      end;

/