EBS : Worflow related queries

1. To check workflow mailer service current status

  sqlplus apps/<apps_pwd>
  select running_processes from apps.fnd_concurrent_queues where concurrent_queue_name = 'WFMLRSVC';

  Number of running processes should be greater than 0

2. Find current mailer status

  sqlplus apps/<apps_pwd>
  select component_status
    from apps.fnd_svc_components
   where component_id =
    (select component_id
     from apps.fnd_svc_components
     where component_name = 'Workflow Notification Mailer');

  Values:
  RUNNING
  STARTING
  STOPPED_ERROR
  DEACTIVATED_USER
  DEACTIVATED_SYSTEM

3. Starting Workflow notification mailer

  sqlplus apps/<apps_pwd>
  declare
       p_retcode number;
       p_errbuf varchar2(100);
       m_mailerid fnd_svc_components.component_id%TYPE;
  begin -- To find mailer Id
       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);        -- Starting the Workflow Mailer
      commit;
  end;
  /


4. Stopping Workflow notification mailer

  sqlplus apps/<apps_pwd>
  declare
       p_retcode number;
       p_errbuf varchar2(100);
       m_mailerid fnd_svc_components.component_id%TYPE;
  begin -- To find mailer Id
       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);        -- Stopping the Workflow Mailer
      commit;
  end;
  /


5. To find the open notifications count by message_type

select message_type, mail_status, count(*) from wf_notifications where status = 'OPEN' GROUP BY MESSAGE_TYPE, MAIL_STATUS

6. To find the alerts notifications we need to query WF_NOTIFICATION_OUT queue

select corr_id, retry_count, msg_state, count(*) from applsys.aq$wf_notification_out group by corr_id, msg_state, retry_count order by count(*) desc;