FULL OF WORKFLOW-MAILER
Oracle Apps DBA
point of view which is Workflow Notification Mailer .
Currently with
OWF.H (Oracle Work Flow Minipack H) or 11.5.10 Oracle Applications Uses Java
Mailer & before that it used to be C Mailer.
Some of you who
are on 11.5.8 or lower version might still be using wfmail.cfg under FND_TOP .
Configuration
is quite simple via OAM (Oracle Application Manager) provided you or your unix
team setup inbound & outbound mails set up correctly.
Notification
Mailer will inturn use Operating System command/mail for inbound(for receiving
mail) & outbound(for sending mails).
In 11.5.10 Java Notification Mailer is available out of the box , you simply need to configure via Oracle Application Manager .
In 11.5.10 Java Notification Mailer is available out of the box , you simply need to configure via Oracle Application Manager .
Workflow
Notification Mailer Setup in Oracle Apps R12/12i
————————————————————————–
Workflow
notification mailer setup in R12 is similar to 11i ( In both release 11i (OWF.H
and higher in 11i) & R12 are Java Mailer)
* Previous
version of Notification Mailer in 11i was based on C also called as C Mailer
Things to note
1. You use Oracle Application Manager (OAM) to configure Workflow Notification Mailer.
2. There are two kind of Notification (Outbound & Inbound) in Workflow Mailer
3. For Outbound Notification, CM (Concurrent Manager) node should be able to connect to SMTP (Simple Mail Transfer Protocol) server/relay.
4. For Inbound Notification (Optional), CM node should be able to connect to IMAP (Internet Message Access Protocol) Server.
5. Log file for Workflow Mailer Notification are at $APPLCSF/$APPLLOG/FNDC*.txt
6. Workflow Notification Mailer in background run as Concurrent Manager (Workflow Mailer Service, Workflow Agent Listener Service)
7. If you don’t wish to send mail notification to end user (from Dev/Test instance) then configure Test Address in configuration screen.
1. You use Oracle Application Manager (OAM) to configure Workflow Notification Mailer.
2. There are two kind of Notification (Outbound & Inbound) in Workflow Mailer
3. For Outbound Notification, CM (Concurrent Manager) node should be able to connect to SMTP (Simple Mail Transfer Protocol) server/relay.
4. For Inbound Notification (Optional), CM node should be able to connect to IMAP (Internet Message Access Protocol) Server.
5. Log file for Workflow Mailer Notification are at $APPLCSF/$APPLLOG/FNDC*.txt
6. Workflow Notification Mailer in background run as Concurrent Manager (Workflow Mailer Service, Workflow Agent Listener Service)
7. If you don’t wish to send mail notification to end user (from Dev/Test instance) then configure Test Address in configuration screen.
Step to configure Workflow Notification Mailer
1. Login to
Apps R12 with System Administrator Reponsibility
2. under Workflow : Oracle Applications Manager click on Workflow Manager
2. under Workflow : Oracle Applications Manager click on Workflow Manager
If this is
first time you are configuring Workflow Notification Mailer in Oracle Apps
R12/12i you will see Notification Mailers as unavailable
Click on Notification
Mailers
In next screen
(as shown below, click on Edit Button)
Here you have
option to select Inbound notification setup or Just outgoing
Notification Setup.
Provide SMTP
Server Name (ensure that CM node should be able to connect to SMTP
Server or SMTP Relay)
Uncheck Inbound
Processing (from above screen), if you don’t wish to configure Inbound
Notification Mailer.
If you wish to
configure Inbound Notification as well then ensure IMAP Server should be
configured with a valid user (createInbox, Processed & Discard folder
for this User)
Click on Apply button
to finish configuration, at this stage Notification Mailer will test SMTP
Server & IMAP Server connectivity.
For Advanced
setup, click on Advanced at top right of configuration screen.
Metalink Notes for Notification Mailer
1. 453137.1 Oracle Workflow Best Practices Release 12 and Release 11i
2. 274764.1 Oracle Workflow Cartridge Workflow Java Mailer Setup Test
3. 433359.1 Tracking Workflow Notification Event Messages
4. 456921.1 Queries Related to Alert and Mailer Integration Post RUP4
5. 454706.1 How to Stop mails from Workflow Notification Mailer
Metalink Notes for Notification Mailer
1. 453137.1 Oracle Workflow Best Practices Release 12 and Release 11i
2. 274764.1 Oracle Workflow Cartridge Workflow Java Mailer Setup Test
3. 433359.1 Tracking Workflow Notification Event Messages
4. 456921.1 Queries Related to Alert and Mailer Integration Post RUP4
5. 454706.1 How to Stop mails from Workflow Notification Mailer
Oracle Workflow
Notification Mailer Outbound Processing
===========================================
Here are
steps/events for Oracle Workflow Notification Outbound Processing(eMail
from Oracle Applications Workflow to Users)
1.When workflow
Engine determines that a notification message must be sent, it raises an event
in BES (Business Event System) oracle.apps.wf.notifications.send
Event is raised with Notification ID (NID) as event key
Event is raised with Notification ID (NID) as event key
2. There is
seeded subscription to this Event
3. Event is
placed on WF_DEFERRED agent
4.Event is
dequeued from WF_DEFERRED and subscription is processed
5. Subscription
places event message to WF_NOTIFICATION_OUT agent.
6.Notification
Mailer dequeues message from WF_NOTIFICATION_OUT agent and
6.1convert XML representation of notification into MIME encoded message (Multi-purpose Internet Mail Extensions) and
6.2 Send message by SMTP (Simple Mail Transfer Protocol) to intended user (If Test Address/Override Address is set then email is sent to Test Address
6.1convert XML representation of notification into MIME encoded message (Multi-purpose Internet Mail Extensions) and
6.2 Send message by SMTP (Simple Mail Transfer Protocol) to intended user (If Test Address/Override Address is set then email is sent to Test Address
E-Mail
Notification is sent if all below conditions are true
a) Notification status is OPEN or CANCELED and
b) Notification mail_status is MAIL or INVALID and
c) Recipient Role has a valid e-mail address and Notification Preference is in the format MAIL%
d) Workflow Deferred Agent Listener is running
e) Workflow Notification Mailer is running
To check a) & b) run
below query
SELECT status,
mail_status FROM wf_notifications WHERE notification_id = ‘&NID’;
mail_status >>
SENT means mail has gone out of mailer to user
To check c) run
below query
SELECT email_address, nvl(WF_PREF.get_pref(name, ‘MAILTYPE’),notification_preference)
FROM wf_roles
WHERE name = upper(‘&recipient_role’);
SELECT email_address, nvl(WF_PREF.get_pref(name, ‘MAILTYPE’),notification_preference)
FROM wf_roles
WHERE name = upper(‘&recipient_role’);
To check d)
& e) Use OAM (Oracle Application Manager)
.
Reference
Workflow
Administrator Guide (Page 73-75)
How to Update
Workflow Administrator Role in Oracle Applications 11i/R12
———————————————————————————————-
If you want to
see workflow details (owned by other users) or status diagram in
Oracle Applications 11i/R12 then you should belong to one of responsibilities/user
listed under WF_ADMIN_ROLE.
By default (in
11i & R12) this role is set to user sysadmin (In old
versions 11.5.8 or prior, it used to set to *)
If you wish to
change WF_ADMIN_ROLE, as per most of metalink notes either
1. Change it
via Workflow Administrator Web Applications responsibility
(Login as sysadmin >> Workflow Administrator Web Applications >>
Administration)
or update table
2. SQL> update
wf_resources set text=’&Enter_Admin_Name’ where name=’WF_ADMIN_ROLE’;
to set it to everyone,
use
SQL> update wf_resources set text=’*’ where name=’WF_ADMIN_ROLE’;
SQL> update wf_resources set text=’*’ where name=’WF_ADMIN_ROLE’;
.
Problem with
above solution
– Execution of Autoconfig will override above settings. Autoconfig will pick value against parameter s_wf_admin_role(default value SYSADMIN) from context file $CONTEXT_FILE
.
– Execution of Autoconfig will override above settings. Autoconfig will pick value against parameter s_wf_admin_role(default value SYSADMIN) from context file $CONTEXT_FILE
.
Correct way to
set Workflow Administrator Role
1. First
identify Workflow Role Name associated with User
or Responsibility.
A. For setting Admin Role to specific
user
SQL> select name from apps.wf_roles where
DISPLAY_NAME like '&USER_NAME' and ORIG_SYSTEM='FND_USR';
B. For setting it to a
responsibility (so that all users with that responsibility can view other
user’s Workflow)
SQL> select name from apps.wf_roles where
DISPLAY_NAME like '&Responsibility_Name' and ORIG_SYSTEM='FND_RESP';
SQL> select name from apps.wf_roles
where DISPLAY_NAME like ‘System Administrator‘; (output
for System Administrator responsibility should look like)FND_RESP|SYSADMIN|SYSTEM_ADMINISTRATOR|STANDARD
SQL> select name from apps.wf_roles
where DISPLAY_NAME like ‘Application Developer‘; (output for Application
Developer responsibility should look like)
FND_RESP|FND|APPLICATION_DEVELOPER|STANDARD
2. Update context file ($CONTEXT_FILE)
variable s_wf_admin_role (If you don’t see this parameter in context
file then apply latest Autoconfig Patch) to value from above query
For Sysadmin User - Set it to SYSADMIN
For System Administrator Responsibility - Set it to
FND_RESP|SYSADMIN|SYSTEM_ADMINISTRATOR|STANDARD
For Application Developer Responsibility - Set it toFND_RESP|FND|APPLICATION_DEVELOPER|STANDARD
3. Run Autoconfig -
$OAD_TOP/admin/scripts/$CONTEXT_NAME/adautocfg.sh (11i)
$ADMIN_SCRIPTS_HOME/adautocfg.sh (R12)
Reference documents for Workflow mailer
setup
=============================================
268085.1 Configuring the Oracle
Workflow 2.6/11i.OWF.H Java-based Notification Mailer with Oracle Applications
11i
164871.1 Configuring the Workflow
Notification Mailer in Oracle Applications Manager 11i
172174.1 WF 2.6: Oracle Workflow
Notification Mailer Architecture in Release 11i
453137.1 Oracle Workflow Best Practices
Release 12 and Release 11i
274764.1 Oracle Workflow Cartridge
Workflow Java Mailer Setup Test
433359.1 Tracking Workflow Notification
Event Messages
456921.1 Queries Related to Alert and
Mailer Integration Post RUP4
454706.1 How to Stop mails from
Workflow Notification Mailer
How to Perform a Meaningful SMTP Telnet Test to
Troubleshoot Java Mailer For Sending Email Notifications [ID 753845.1]
This document
details the steps to perform a meaningful telnet test to investigate
java mailer issues related to sending workflow email notifications to the SMTP
server.As it is explained in the Oracle Workflow Administrator’s Guide, it
is mandatory to have an SMTP server set up in order to send the workflow
notification e-mail messages. This means that the notification mailer does not
send the notification e-mails itself but completely relies on the SMTP server
to achieve this task.
For this
reason, it is important to be able to send an email using telnet on the SMTP
server in the conditions that are used by the mailer.
Note: this
test is useful in the following situations (the below list is not
exhaustive):
– You are
setting the workflow notification mailer up and you want to validate the
outbound processing parameters.
– You suspect a connectivity issue between the java mailer node and the SMTP server node.
– You suspect that SMTP server does not process emails.
– You notice that given notifications in WF_NOTIFICATIONS have MAIL_STATUS = FAILED, and that the notification preference of the notification recipient has been switched to DISABLED (this can be seen in $FND_TOP/sql/wfmlrdbg.sql output againt the given notification id).
– You want to validate a given email address.
…
– You suspect a connectivity issue between the java mailer node and the SMTP server node.
– You suspect that SMTP server does not process emails.
– You notice that given notifications in WF_NOTIFICATIONS have MAIL_STATUS = FAILED, and that the notification preference of the notification recipient has been switched to DISABLED (this can be seen in $FND_TOP/sql/wfmlrdbg.sql output againt the given notification id).
– You want to validate a given email address.
…
More
particularly this test will provide relevant output when the following strings
are seen in mailer log:
-
javax.mail.SendFailedException: 550 5.7.1 Unable to relay
- javax.mail.MessagingException
- Invalid Address
- Relay access denied
- Unable to relay- Relaying denied
- Client does not have permission to submit mail to this server
- Validation failed for the following parameters -> {OUTBOUND_SERVER=Unable to make a network connection.}
- EXCEPTION:[SVC-GSM-WFMLRSVC-12848-10006 : oracle.apps.fnd.wf.mailer.SMTPOutboundProcessor.read]:Not sending notification {3939936}because the notification mail status is null OR not 'MAIL'
- Updating notification {3939936} status {FAILED} {WFMLRSND_FAILED_UNDELIVERABLE} {{SYSADMIN}}
etc...
- javax.mail.MessagingException
- Invalid Address
- Relay access denied
- Unable to relay- Relaying denied
- Client does not have permission to submit mail to this server
- Validation failed for the following parameters -> {OUTBOUND_SERVER=Unable to make a network connection.}
- EXCEPTION:[SVC-GSM-WFMLRSVC-12848-10006 : oracle.apps.fnd.wf.mailer.SMTPOutboundProcessor.read]:Not sending notification {3939936}because the notification mail status is null OR not 'MAIL'
- Updating notification {3939936} status {FAILED} {WFMLRSND_FAILED_UNDELIVERABLE} {{SYSADMIN}}
etc...
It is very
important to perform all the steps mentioned below; they will allow to perform
the telnet SMTP test from the right node and to pass the correct values during
the test.
1) Identify the
concurrent tiers node where mailer runs
by running script below:
by running script below:
select target_node from fnd_concurrent_queues where concurrent_queue_name like 'WFMLRSVC%';
It will return
for example:
TARGET_NODE
------------------------------
ebiz1
------------------------------
ebiz1
In this
example ebiz1 is the node where java mailer runs.
2) Gather other
parameters values necessary for the SMTP telnet test:
To perform the SMTP telnet test, in addition to mailer node, you will also need to know on which node is the SMTP server (this is mailer “outbound server” parameter), and what is the reply to address that is set up for the java mailer (this is mailer “reply to” parameter).
To perform the SMTP telnet test, in addition to mailer node, you will also need to know on which node is the SMTP server (this is mailer “outbound server” parameter), and what is the reply to address that is set up for the java mailer (this is mailer “reply to” parameter).
To get these
values run the following:
SELECT b.component_name,
c.parameter_name,
a.parameter_value
FROM fnd_svc_comp_param_vals a,
fnd_svc_components b,
fnd_svc_comp_params_b c
WHERE b.component_id = a.component_id
AND b.component_type = c.component_type
AND c.parameter_id = a.parameter_id
AND c.encrypted_flag = 'N'
AND b.component_name like '%Mailer%'
AND c.parameter_name in ('OUTBOUND_SERVER', 'REPLYTO')
ORDER BY c.parameter_name;
c.parameter_name,
a.parameter_value
FROM fnd_svc_comp_param_vals a,
fnd_svc_components b,
fnd_svc_comp_params_b c
WHERE b.component_id = a.component_id
AND b.component_type = c.component_type
AND c.parameter_id = a.parameter_id
AND c.encrypted_flag = 'N'
AND b.component_name like '%Mailer%'
AND c.parameter_name in ('OUTBOUND_SERVER', 'REPLYTO')
ORDER BY c.parameter_name;
It will return
for example:
COMPONENT_NAME PARAMETER_NAME PARAMETER_VALUE
------------------------------- ------------------------------ -----------------------
Workflow Notification Mailer OUTBOUND_SERVER mitini1
Workflow Notification Mailer REPLYTO jmailer1@dummy_domain.com
------------------------------- ------------------------------ -----------------------
Workflow Notification Mailer OUTBOUND_SERVER mitini1
Workflow Notification Mailer REPLYTO jmailer1@dummy_domain.com
In this
example the outbound server is on mitini1 node and the reply to
address is set to jmailer1@dummy_domain.com.
3) Perform the
SMTP telnet test as follows:
3.1) Log on to the node where mailer runs (to identify it, please refer to step 1)
This is mandatory. SMTP telnet test is only meaningful when it is performed from the concurrent tier where mailer runs.
In our example you should log to ebiz1 node.
3.1) Log on to the node where mailer runs (to identify it, please refer to step 1)
This is mandatory. SMTP telnet test is only meaningful when it is performed from the concurrent tier where mailer runs.
In our example you should log to ebiz1 node.
3.2)
From mailer node, issue the following commands one by one:
telnet [outbound server] 25
EHLO [mailer node]
MAIL FROM: [reply_to address]
RCPT TO: [my_test_email_address]
DATA
Subject: Test messageTest message body
.
quit
EHLO [mailer node]
MAIL FROM: [reply_to address]
RCPT TO: [my_test_email_address]
DATA
Subject: Test messageTest message body
.
quit
Notes:a) Very
important, the commands needs to be entered one by one
b) Replace [outbound server] by the value retrieved for OUTBOUND_SERVER in step 2.
c) Replace [mailer node] by the value retrieved in step 1.
d) Replace [reply_to address] by the value retrieved for REPLYTO in step 2.
e) Replace [my_test_email_address] by the email address that you need to test.
f) By default SMTP server runs on port 25. If another port is used you’ll have to modify the port accordingly in the syntax below.
g) Enter a blank line after the email subject and after the text ‘Test message body’.
h) The end of the message is signaled by a “.” on a line by itself.
i) To exit the telnet session, type ‘quit’ and then hit ‘enter’.
b) Replace [outbound server] by the value retrieved for OUTBOUND_SERVER in step 2.
c) Replace [mailer node] by the value retrieved in step 1.
d) Replace [reply_to address] by the value retrieved for REPLYTO in step 2.
e) Replace [my_test_email_address] by the email address that you need to test.
f) By default SMTP server runs on port 25. If another port is used you’ll have to modify the port accordingly in the syntax below.
g) Enter a blank line after the email subject and after the text ‘Test message body’.
h) The end of the message is signaled by a “.” on a line by itself.
i) To exit the telnet session, type ‘quit’ and then hit ‘enter’.
So the commands
to enter in the context of our example are:
telnet mitini1 25
EHLO ebiz1
MAIL FROM: jmailer1@dummy_domain.com
RCPT TO: robert.king@dummy_domain.com
DATA
Subject: Test messageTest message body
.
quit
EHLO ebiz1
MAIL FROM: jmailer1@dummy_domain.com
RCPT TO: robert.king@dummy_domain.com
DATA
Subject: Test messageTest message body
.
quit
(let’s assume
robert.king@dummy_domain.com is the given address you want to test)
3.3) Then
verify the following:
a) Has an error
message been thrown during the test?
To compare the output you get with the normal output received during telnet SMTP test, please refer to section 8a (Verify SMTP Server ) in note 242941.1 How To Troubleshoot Java-based Workflow Notification Mailer In 11.5.9 and OWF.G.
To compare the output you get with the normal output received during telnet SMTP test, please refer to section 8a (Verify SMTP Server ) in note 242941.1 How To Troubleshoot Java-based Workflow Notification Mailer In 11.5.9 and OWF.G.
b) Check the
INBOX of the email address used for the test (my_test_email_address)
Is there an email here with Subject: Test message and with sender corresponding to reply_to_address email address?
In our example, you should check robert.king@dummy_domain.com INBOX and research a message with subject “Test message” and that is from sender jmailer1@dummy_domain.com.
4) Test interpretation
Is there an email here with Subject: Test message and with sender corresponding to reply_to_address email address?
In our example, you should check robert.king@dummy_domain.com INBOX and research a message with subject “Test message” and that is from sender jmailer1@dummy_domain.com.
4) Test interpretation
If an error
message has been thrown during the test, or if the test email has not been
received, this means the SMTP telnet test is not successful. Because the java
mailer uses the SMTP server to send notification emails, it is a prerequisite
for a correct behavior of the java mailer that this test is successful.
If the test is not successful, depending on the message received, the problem has to be addressed by the SMTP Server administrator or the Network administrator. Commonly, relaying needs to be enabled.
5) Finishing tasks
a) Confirm that the SMTP server defined within the Workflow Notification mailer setup reflects the correct address.
b) Once the mail server tests are complete and successful, to re-send any failed notifications, please run the Resend Failed Notifications concurrent request.
If the test is not successful, depending on the message received, the problem has to be addressed by the SMTP Server administrator or the Network administrator. Commonly, relaying needs to be enabled.
5) Finishing tasks
a) Confirm that the SMTP server defined within the Workflow Notification mailer setup reflects the correct address.
b) Once the mail server tests are complete and successful, to re-send any failed notifications, please run the Resend Failed Notifications concurrent request.
- Please
reference the Workflow Product Information Center Document for Top Workflow
Resources: Document
1320509.1
- For
additional help, please refer to one of the following documents on
diagnostics to address current needs. Providing diagnostic output on an
issue for support when logging a service request is very helpful.Document
179661.1 for 11i or Document
421245.1 for Rel 12.x
- Visit
the Core
Workflow community for help with industry experts or to
share knowledge.
- Please
see Document
1186338.1 for recorded Workflow Webcasts
Troubleshooting Workflow Notification Mailer Issues
Troubleshooting
Workflow Notification Mailer Issues
Troubleshooting Workflow Notification Mailer Issues
Find Workflow Notification Mailer is up and Running?
Troubleshooting Workflow Notification Mailer Issues
Find Workflow Notification Mailer is up and Running?
SELECT
component_name, component_status
FROM fnd_svc_components
WHERE component_type = ‘WF_MAILER’;
FROM fnd_svc_components
WHERE component_type = ‘WF_MAILER’;
Workflow log’s:
FNDCPGSC*.txt under $APPLCSF/$APPLOG directory
Find the Failed
One’s?
Select
NOTIFICATION_ID, MESSAGE_TYPE, MESSAGE_NAME, STATUS, MAIL_STATUS, FROM_USER,
TO_USER from wf_notifications where MAIL_STATUS=’FAILED’;
Check pending
e-mail notification that was pending for process.
Sql> SELECT
COUNT(*), message_name FROM wf_notifications
WHERE STATUS=’OPEN’
AND mail_status = ‘MAIL’
GROUP BY message_name;
WHERE STATUS=’OPEN’
AND mail_status = ‘MAIL’
GROUP BY message_name;
Sql> SELECT
* FROM wf_notifications
WHERE STATUS=’OPEN’
AND mail_status = ‘SENT’
ORDER BY begin_date DESC
WHERE STATUS=’OPEN’
AND mail_status = ‘SENT’
ORDER BY begin_date DESC
Check the
Workflow notification has been sent or not?
select
mail_status, status from wf_notifications where notification_id=
–If mail_status
is MAIL, it means the email delivery is pending for workflow mailer to send the
notification
–If mail_status is SENT, its means mailer has sent email
–If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is “Don’t send email”
–Notification preference of user can be set by user by logging in application + click on preference + the notification preference
–If mail_status is SENT, its means mailer has sent email
–If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is “Don’t send email”
–Notification preference of user can be set by user by logging in application + click on preference + the notification preference
1. Verify
whether the message is processed in WF_DEFERRED queue
select * from
applsys.aq$wf_deferred a where a.user_data.getEventKey()= ”
– notification id
– notification id
2. If the
message is processed successfully message will be enqueued to
WF_NOTIFICATION_OUT queue, if it errored out it will be enqueued to WF_ERROR
queue
select
wf.user_data.event_name Event_Name, wf.user_data.event_key Event_Key,
wf.user_data.error_stack Error_Stack, wf.user_data.error_message Error_Msg
from wf_error wf where wf.user_data.event_key = ‘
To check what all mails have went and which all failed ?
wf.user_data.error_stack Error_Stack, wf.user_data.error_message Error_Msg
from wf_error wf where wf.user_data.event_key = ‘
To check what all mails have went and which all failed ?
Select
from_user,to_user,notification_id, status, mail_status, begin_date
from WF_NOTIFICATIONS where status = ‘OPEN’;
from WF_NOTIFICATIONS where status = ‘OPEN’;
Select
from_user, to_user, notification_id, status,
mail_status,begin_date,USER_KEY,ITEM_KEY,MESSAGE_TYPE,MESSAGE_NAME begin_date
from WF_NOTIFICATIONS where status = ‘OPEN’;
from WF_NOTIFICATIONS where status = ‘OPEN’;
Users complain
that notifications are stuck ?
Use the
following query to check to see whatever the users are saying is correct
SQL> select
message_type, count(1) from wf_notifications
where status=’OPEN’ and mail_status=’MAIL’ group by message_type;
where status=’OPEN’ and mail_status=’MAIL’ group by message_type;
E.g o/p of
query –
MESSAGE_Type
COUNT(1)
——– ———-
POAPPRV 11 — 11 mails of Po Approval not sent —
INVTROAP 12
REQAPPRV 9
WFERROR 45 — 45 mails have error
——– ———-
POAPPRV 11 — 11 mails of Po Approval not sent —
INVTROAP 12
REQAPPRV 9
WFERROR 45 — 45 mails have error
If Mail not
received by User ?
select
Name,DISPLAY_NAME,EMAIL_ADDRESS,NOTIFICATION_PREFERENCE,STATUS
from wf_users where DISPLAY_NAME=’xxx,yyy’ ;
from wf_users where DISPLAY_NAME=’xxx,yyy’ ;
Status – Active
Notification_preference-> Mailtext
Email Address should not be null
Notification_preference-> Mailtext
Email Address should not be null
Notification
not sent waiting to be mailed ?
SQL> select
notification_id, status, mail_status, begin_date from WF_NOTIFICATIONS
where status = ‘OPEN’ and mail_status = ‘MAIL’;
To debug the notification id ?
where status = ‘OPEN’ and mail_status = ‘MAIL’;
To debug the notification id ?
$FND_TOP/sql
run wfmlrdbg.sql
******************************
run wfmlrdbg.sql
******************************
Note: 1054215.1
– How to Check if the Workflow Mailer is Running
Note: 415516.1 – How to Check Whether Notification Mailer is Working or Not
Note: 415516.1 – How to Check Whether Notification Mailer is Working or Not
Note: 831982.1
– 11i/R12 – A guide for troubleshoting Workflow Notification Emails – Inbound
and Outbound
Note: 1012344.7 – Notifications Not Being Sent In Workflow
Note: 560472.1 – Workflow Mailers Not Sending Notifications
Note: 1012344.7 – Notifications Not Being Sent In Workflow
Note: 560472.1 – Workflow Mailers Not Sending Notifications
Please see
(Note: 753845.1 – How to Perform a Meaningful SMTP Telnet Test to Troubleshoot
Java Mailer Issues), the same error is reported in this doc.
Oracle workflow
notification mailer status – sql
Oracle workflow
notification mailer sql’s
=========================================
=========================================
1. Workflow:
version
2. check
workflow status.
3. check if
workflow is used by only one instance
4. check if processor_read_timeout_close
is set to ‘Y’
5. check for
bad e-mail address
6. How to know
mail sent to a user with details:
7. How to know
whether it is set to correct url from porfile options:
8. How to know
reqid, process id, sid..
9. workflow
patches
10. Workflow:
To see failed, open notifications
11. To check if
email address, notification preference, display_name
12. How to know
workflow responsibility from backend:
13. Steps to
drop and recreate WF_CONTROL queue:
=================
1. Workflow: version
=================
$FND_TOP/sql/wfver.sql
1. Workflow: version
=================
$FND_TOP/sql/wfver.sql
----------------------------------------------------
2. 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;
----------------------------------------------------
3. 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;
----------------------------------------------------
4. check if processor_read_timeout_close is
set to 'Y'
----------------------------------------------------
set pagesize 100
set linesize 132
set feedback off
set verify off
col value format a35
col component_name format a30
select c.component_id,
c.component_name, 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 =
'PROCESSOR_READ_TIMEOUT_CLOSE'
order by c.component_name,p.parameter_name;
—————————————————-
5. check for bad e-mail address
—————————————————-
If below SQL statement is returning rows you need to correct the email addresses for associated users:
5. check for bad e-mail address
—————————————————-
If below SQL statement is returning rows you need to correct the email addresses for associated users:
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 '%%';
============================================
6. How to know mail sent to a user with
details:
============================================
select name, display_name,
notification_preference, email_address from wf_local_roles where name = '';
====================================================================
7. How to know whether it is set to correct
url from porfile options:
====================================================================
set linesize 155;
set pagesize 200;
set verify off;
col Profile format a50;
col Value format a50;
select t.PROFILE_OPTION_ID ID,
z.USER_PROFILE_OPTION_NAME Profile,
nvl(v.PROFILE_OPTION_VALUE,'Replace with
non-virtual URL') Value
from apps.fnd_profile_options
t, apps.fnd_profile_option_values v,apps.fnd_profile_options_tl
z
where (v.PROFILE_OPTION_ID (+) =
t.PROFILE_OPTION_ID)
and (z.PROFILE_OPTION_NAME =
t.PROFILE_OPTION_NAME)
and (t.PROFILE_OPTION_NAME in
('WF_MAIL_WEB_AGENT'));
====================================
8. How to know reqid, process id, sid..
=====================================
select
request_id,phase_code,status_code,hold_flag from fnd_concurrent_requests where
REQUEST_ID=20422815;
select
ORACLE_PROCESS_ID,ORACLE_SESSION_ID,OS_PROCESS_ID from fnd_concurrent_requests
where REQUEST_ID=20422815;
select a.session_id,
b.owner,
b.object_type,
b.object_name,
a.oracle_username,
a.os_user_name,
a.process,
a.locked_mode
from v$locked_object a, dba_objects b
where b.object_id = a.object_id
and a.session_id='3383';
select name, display_name,
notification_preference, email_address from wf_local_roles where name =
'SYSADMIN';
===================
9. workflow patches
===================
will provide us information as to your base line code level.
Many issues are only relevant to a certain code level so this information is essential:
9. workflow patches
===================
will provide us information as to your base line code level.
Many issues are only relevant to a certain code level so this information is essential:
set linesize 155;
set pagesize 200;
set verify off;
select b.bug_number bug, b.LAST_UPDATED_BY
ldate, decode( bug_number, 2728236 , 'OWF.G INCLUDED IN 11.5.9',
3031977, 'POST OWF.G ROLLUP 1 - 11.5.9.1',
3061871, 'POST OWF.G ROLLUP 2 - 11.5.9.2',
3124460, 'POST OWF.G ROLLUP 3 - 11.5.9.3',
3316333, 'POST OWF.G ROLLUP 4 - 11.5.9.4.1',
3314376, 'POST OWF.G ROLLUP 5 - 11.5.9.5',
3409889, 'POST OWF.G ROLLUP 5 Consolidated
Fixes For OWF.G RUP5', 3492743, 'POST OWF.G ROLLUP 6 - 11.5.9.6',
3868138, 'POST OWF.G ROLLUP 7 - 11.5.9.7',
3262919, 'FMWK.H',
3262159, 'FND.H INCLUDE OWF.H',
3258819, 'OWF.H INCLUDED IN 11.5.10',
3438354, '11i.ATG_PF.H INCLUDE OWF.H',
3140000, 'ORACLE APPLICATIONS
RELEASE 11.5.10 MAINTENANCE PACK',
3240000, '11.5.10 ORACLE E-BUSINESS
SUITE CONSOLIDATED UPDATE 1',
3460000, '11.5.10 ORACLE E-BUSINESS
SUITE CONSOLIDATED UPDATE 2',
3480000, 'ORACLE APPLICATIONS
RELEASE 11.5.10.2 MAINTENANCE PACK',
4017300, 'ATG_PF:11.5.10 Consolidated Update
(CU1) for ATG Product Family',
4125550, 'ATG_PF:11.5.10 Consolidated Update
(CU2) for ATG Product Family',
4719658, 'ONE OFF PATCH FOR MISSING
RESPONSIBILITIES - WFDS Fix',
5121512, 'AOL USER RESPONSIBILITY SECURITY
FIXES VERSION 1',
6008417, 'AOL USER RESPONSIBILITY SECURITY
FIXES 2b',
4676589, '11i.ATG_PF.H RUP4',
5473858, '11i.ATG_PF.H RUP5',
5903765, '11i.ATG_PF.H RUP6',
4334965, '11i.ATG_PF.H RUP3') patch
from apps.AD_BUGS b
where b. BUG_NUMBER in
('2728236','3031977','3061871','3124460','3316333','3314376','3409889','3492743','3262159','3262919','3868138','3258819','3438354','3240000','3460000','3140000','3480000','4017300','4125550','4719658','5121512','6008417','4676589','5473858','5903765','4334965')
order by patch;
===============================================
10. Workflow: 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;
====================================================================
11. To check if email address, notification preference, display_name
====================================================================
11. To check if email address, notification preference, display_name
====================================================================
select DISPLAY_NAME,
NOTIFICATION_PREFERENCE from wf_users where EMAIL_ADDRESS = 'h@h.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 = 'mmmmm@yahoo.com';
====================================================
12. How to know workflow responsibility from backend:
====================================================
12. How to know workflow responsibility from backend:
====================================================
select wes.status, wes.phase,
wes.rule_function, wes.on_error_code from wf_events we, wf_event_subscriptions
wes
where we.name='oracle.apps.fnd.wf.ds.userRole.updated'
and we.guid=wes.event_filter_guid;
============================================
13. Steps to drop and recreate WF_CONTROL queue:
============================================
13. Steps to drop and recreate WF_CONTROL queue:
============================================
a. Shut down
the concurrent managers.
b. Connect to sqlplus
session as APPS user:
Execute: (For Workflow Embedded within Apps)
Execute: (For Workflow Embedded within Apps)
SQL>exec
DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'APPLSYS.WF_CONTROL', force
=>TRUE);
SQL> commit;
Execute: (For
Standalone Workflow)
sqlplus / @wfctlqec.sql
sqlplus / @wfctlqec.sql
c. Execute
wfjmsqc2.sql for creating all Advanced Queues with JMS Text structures.
Usage:
sqlplus / @wfjmsqc2.sql
sqlplus / @wfjmsqc2.sql
Both of the
script are under $FND_TOP/patch/115/sql or $FND_TOP/sql directories.
d.Run
afwfqgnt.sql to recreate grants/synonyms for Workflow Advanced Queue tables
Usage:
sqlplus apps/apps @$FND_TOP/patch/115/sql/afwfqgnt.sql APPSAPPLSYS
Additional Information: For Rebuilding all other WF Queue refer Note 754468.1 Title: How to Rebuild Workflow Queues ,based on your application version.
sqlplus apps/apps @$FND_TOP/patch/115/sql/afwfqgnt.sql APPSAPPLSYS
Additional Information: For Rebuilding all other WF Queue refer Note 754468.1 Title: How to Rebuild Workflow Queues ,based on your application version.
Mostly, this
option will come handy after a clone, wherein you want to retain all the
production / source instance data and continue to run workflow mailer but
without sending emails to the real end users.
To achieve this
you can set an Override address to route all the notifications to a single
email address.
Steps
1. Login as
sysadmin
2. Select System Administration Responsibility
3. Click Workflow under Oracle Applications Manager
4. Click on the tick mark or page icon next to Notification Mailers
5. Click on Workflow Notification Mailer under Name column
6. Click “Set Override Address” button next to Test Mailer button
7. Key in the new email address and click submit.
2. Select System Administration Responsibility
3. Click Workflow under Oracle Applications Manager
4. Click on the tick mark or page icon next to Notification Mailers
5. Click on Workflow Notification Mailer under Name column
6. Click “Set Override Address” button next to Test Mailer button
7. Key in the new email address and click submit.
After
submitting, Java mailer will send a verification email to the mentioned email
address with a verification code and link to activate the override entry.
Either you can
request the email id owner to send you the email with the verification code so
that you can key-in the code or if the email address owner has access to Oracle
Apps, he/she can click the link and enter the verification code.
No comments:
Post a Comment