Oracle EBS - Workflow Notification Mailer, Agent Listener& Service Container Start and Stop Troubleshooting

As a part of DBA activity we usually come across our Workflow Notification Mailer Issue. Along with WorkFlow Notification Mailers we get issue with the Agent listeners and Service Components.

In workflow system we have six components.

1.Notification Mailers
2. Agent Listeners
3. Service Components
4. Background Engines
5. Purge
6. Control Queue Programs


We have to make sure all components are up and running.

Queries to start and stop notification mailers from backend:

1. Check the status of WFNM

SELECT running_processes
FROM fnd_concurrent_queues
WHERE concurrent_queue_name ='WFMLRSVC';

SELECT component_status
  FROM fnd_svc_components
 WHERE component_id =(SELECT component_id
                        FROM fnd_svc_components
                       WHERE component_name ='Workflow Notification Mailer');

Output will show the COMPONENT_STATUS- Running

2. Check workflow mailer service current status

SELECT running_processes
FROM fnd_concurrent_queues
WHERE concurrent_queue_name ='WFMLRSVC';

Note : Number of running processes > 0 

3. 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');

Possible statuses are :
    RUNNING 
   STARTING 
   STOPPED_ERROR 
   DEACTIVATED_USER 
   DEACTIVATED_SYSTEM 

4. Stop notification 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;

/

5. Start notification 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.start_component (m_mailerid,

                                      p_retcode,

                                      p_errbuf

                                     );

COMMIT;

END;

/

6. SELECT running_processes

FROM fnd_concurrent_queues

WHERE concurrent_queue_name ='WFMLRSVC';

SELECT component_status

  FROM fnd_svc_components

 WHERE component_id =(SELECT component_id

                        FROM fnd_svc_components

                       WHERE component_name ='Workflow Notification Mailer');

Verify the status of these components from front end.

1. Goto System Administrator

2. Oracle Application Manager

3. Workflow

From backend.


set pages 100
set lines 250

SELECT COMPONENT_TYPE,COMPONENT_NAME,COMPONENT_STATUS FROM APPS.FND_SVC_COMPONENTS;