Script to Automate Restart/bouncing of Worflow Mailer from backend in EBS

Following Pl/sql Code will restart the Workflow mailer based on the condition given (The condition can vary according to your requirements)


declare
n_component_id fnd_svc_components.component_id%TYPE;
n_status  varchar2(3000);
nummessages  number;
p_retcode number;
p_errbuf varchar2(100);
begin

begin

SELECT COUNT(*) into  nummessages
FROM wf_notifications
WHERE begin_date >= TRUNC(SYSDATE-1)
AND mail_Status IS NOT NULL
AND mail_status = 'MAIL'
AND message_name <> 'PO_PO_HAS_BEEN_APPROVE'
AND (status <> 'CLOSED' and status <> 'CANCELED')
AND 'PROD' = (SELECT name FROM v$database)
HAVING COUNT(*) <> 0;

exception
when no_data_found then
dbms_output.put_line('Null POS found ' );
nummessages:=0;
end;

begin


select component_id,COMPONENT_STATUS into  n_component_id , n_status
from apps.fnd_svc_components
where component_name='Workflow Notification Mailer';

exception
when no_data_found then
dbms_output.put_line('Component not found ' );
end;

IF   nummessages=0 THEN

fnd_svc_component.start_component(n_component_id, p_retcode, p_errbuf);
       commit;

dbms_output.put_line(n_component_id||' '||n_status ||'' || p_retcode||p_errbuf);

end if;

exception
when no_data_found then
dbms_output.put_line('Nothing to do ' );
end;