To Verify Workflow Mailer Configuration & Verify WF mailer IMAP and SMTP Settings using below Query :

To Verify Workflow Mailer Configuration & Verify WF mailer IMAP and SMTP Settings using below Query

Login as applmgr user to apps node

start the application processes

cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME

adstrtal.sh apps/<apps pwd>

sqlplus apps/<apps pwd>

set lines 130

set pages 100

col value format a30

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', 'TEST_ADDRESS')

order by p.parameter_name;

To performance issue:

Are messages in WF_DEFERRED and WF_NOTIFICATION_OUT processed?

a. Execute the following queries:

SQL> select substr(wfd.corrid,1,40) corrid,

decode(wfd.state,

0, '0 = Ready',

1, '1 = Delayed',

2, '2 = Retained',

3, '3 = Exception',

to_char(substr(wfd.state,1,12))) State,

count(*) COUNT

from applsys.wf_deferred wfd

group by wfd.corrid, wfd.state;

b.

SQL> select substr(wfd.corrid,1,40) corrid,

decode(wfd.state,

0, '0 = Ready',

1, '1 = Delayed',

2, '2 = Retained',

3, '3 = Exception',

to_char(substr(wfd.state,1,12))) State,

count(*) COUNT

from applsys.wf_notification_out wfd

group by wfd.corrid, wfd.state;

c. Provide the output of the $FND_TOP/sql/wfmlrdbg.sql script

d. Upload the latest log file. The following query will return the mailer log file and its location.

set linesize 155;

set pagesize 200;

set verify off;

column MANAGER format a15;

column MEANING format a15;

SELECT concurrent_queue_name manager, fcp.last_update_date, fcp.concurrent_process_id pid, meaning, fcp.logfile_name

FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup

WHERE concurrent_queue_name in ('WFMLRSVC')

AND fcq.concurrent_queue_id = fcp.concurrent_queue_id

AND fcq.application_id = fcp.queue_application_id

AND flkup.lookup_code=fcp.process_status_code

AND lookup_type ='CP_PROCESS_STATUS_CODE'

AND meaning='Active';

e. Output of $FND_TOP/sql/wfver.sql

f. One more thing it is necessary to check is the mail_status of the notifications

select mail_status, count(*)

from wf_notifications

where status = 'OPEN'

group by mail_status

order by 2 desc;

 +++++++++1501822.1++++++++