Setup Notification Mailers in oracle Apps R12 Technical Part 2

alter system set job_queue_processes=20 scope=both;

commit

select * from  wf_users
where name like 'EG%'

select running_processes
    from apps.fnd_concurrent_queues
   where concurrent_queue_name = 'WFMLRSVC';
  
   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');
         
DEACTIVATED_SYSTEM
SYSTEM_ERROR

declare
       p_retcode number;
       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';
       --------------
       -- Stop Mailer
       --------------
       fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);
       commit;
  end;
  /          
 

  declare
       p_retcode number;
       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';
       --------------
       -- Start Mailer
       --------------
       fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
       commit;
  end;
 
  select p.parameter_id,p.parameter_name,v.parameter_value value
from fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p,
fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in
('OUTBOUND_SERVER', 'INBOUND_SERVER',
'ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')
order by p.parameter_name;

SELECT * FROM WF_NOTIFICATIONS
WHERE TRUNC(BEGIN_DATE) = TRUNC(SYSDATE)

order by BEGIN_DATE desc

SELECT * FROM wf_roles
WHERE NAME LIKE '%NUIMI%'

select * from  wf_users
where name like '%NUIMI%'


and wf_users notifi%cation_preference column may not update.

SELECT COUNT(*), message_name FROM wf_notifications
WHERE STATUS='OPEN'
AND mail_status = 'MAIL'
and TRUNC(BEGIN_DATE) = TRUNC(SYSDATE)
GROUP BY message_name;

update WF_NOTIFICATIONS
SET MAIL_STATUS = 'SENT'
WHERE TRUNC(BEGIN_DATE) = TRUNC(SYSDATE)
AND MAIL_STATUS = 'MAIL'

declare
cursor c1 is

select do.owner,do.object_name,do.object_type,dl.session_id,vs.serial#, vs.program,vs.machine,vs.osuser
from dba_locks dl,dba_objects do,v$session vs
where do.object_name ='WF_NOTIFICATIONS' and do.object_type='TABLE' and dl.lock_id1 =do.object_id and vs.sid = dl.session_id;

 alter system kill session '555,55379' immediate;
 
 commit;


select component_type, component_name, Component_status,COMPONENT_STATUS_INFO Error
from fnd_svc_components
where component_type like 'WF%'
order by 1 desc,2,3;


SELECT email_address, nvl(WF_PREF.get_pref(name, 'MAILTYPE'),notification_preference)
FROM wf_roles
WHERE name = upper('&recipient_rolE');