All About Oracle WorkFlow Mailer

In this Topic We will discuss the Workflow mailer In Oracle Apps ad how to check it Let Start :

Workflow: version

  $FND_TOP/sql/wfver.sql

check workflow status.

set linesize 120
set pagesize 50
column COMPONENT_NAME format a45
column STARTUP_MODE format a15
column COMPONENT_STATUS format a15

select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS,fcq.last_update_date
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;

check if workflow is used by only one instance
col value format a20
select p.parameter_id,
p.parameter_name,
v.parameter_value value
from apps.fnd_svc_comp_param_vals_v v,
apps.fnd_svc_comp_params_b p,
apps.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 ( ‘INBOUND_SERVER’,’ACCOUNT’, ‘REPLYTO’)

order by p.parameter_name;

check for bad e-mail address

set linesize 170
col name format a40
col email_address format a80
select name, email_address from apps.wf_local_roles where email_address like ‘% %’;

select name, email_address from apps.wf_local_roles where email_address like ‘%%’;

 “If this retuning Row The there’s bad Email”

How to know mail sent to a user with details:

select name, display_name, notification_preference, email_address
 from wf_local_roles where name = ”;

To see failed, open notifications

SELECT message_type, COUNT(1)
FROM apps.wf_notifications
WHERE 1 = 1 AND mail_status = ‘FAILED’ AND status = ‘OPEN’
GROUP BY message_type;

To check if email address, notification preference, display_name

select DISPLAY_NAME, NOTIFICATION_PREFERENCE from wf_users where EMAIL_ADDRESS = ‘HR@domainname.com‘ and STATUS = ‘ACTIVE’ and DISPLAY_NAME = ‘xxxxx’;
select DISPLAY_NAME, NOTIFICATION_PREFERENCE, EMAIL_ADDRESS from wf_users where NOTIFICATION_PREFERENCE=’MAILTEXT’ and STATUS = ‘ACTIVE’;
select distinct(count(*)) from wf_users where NOTIFICATION_PREFERENCE=’MAILTEXT’ and STATUS = ‘ACTIVE’ and EMAIL_ADDRESS = ‘HR@Domainname.com‘;

Thank you
Osama mustafa

2 thoughts on “All About Oracle WorkFlow Mailer

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.