Tuesday, August 19, 2014

EBS -- Workflow Notification Mailer diagnostics

Supposing that the functional setup was already done properly, I will list possible causes that may prevent Notification mailer to function properly.
Besides, you will find  full process throubleshooting methods in this post.
These throubleshooting methods can be used to investigate the causes which can not be seen clearly at the first sight.

1)Smtp and Imap connection/timeout problems..

These type of problem can easily be detected, as relevant exceptions are reflected to the notification mailer's logfiles.
Note that : for debugging purposes, you should always set your notification mailer log level to "Statement"
You can read the logfiles using OAM, but I dont recommended using OAM for reading the logs, because it starts to encounter problems when the logfiles become large in size.
Rather than that, connect to the application server and open the log files using vi.
For 12.2 logfiles are located in fs_ne, they are under $APPLCSF/$APPLLOG directory..
All the files starting with FNDCPGSC belongs to the Notification mailer. Order them by date, and open the recent one and make your analysis.

To read mailers log file more efficiently:




2)If you have problems sending mails (outbound), then your out queue may be logically corrupt or mailer may be stuck because of an invalid email address.. 

Stop the notification mailer
Back up the creation scripts of wf_notification_out table and queue.
create table APPLSYS.AQ$WF_NOTIFICATION_OUT_ERM as select * from APPLSYS.AQ$WF_NOTIFICATION_OUT
Dropp the WF_NOTIFICATION_OUT queue with the API; sqlplus apps/apps
exec dbms_aqadm.stop_queue( queue_name => 'APPLSYS.WF_NOTIFICATION_OUT', wait => FALSE);
exec dbms_aqadm.drop_queue_table( queue_table => 'APPLSYS.WF_NOTIFICATION_OUT', force =>TRUE);

Recreate the wf_notification_out table & queue manually, with sqlplus using the scripts , backed up in the first place.;

BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE_TABLE
(
QUEUE_TABLE => 'APPLSYS.WF_NOTIFICATION_OUT'
,QUEUE_PAYLOAD_TYPE => 'SYS.AQ$_JMS_TEXT_MESSAGE'
,COMPATIBLE => '8.1'
,STORAGE_CLAUSE => 'NOCOMPRESS
TABLESPACE APPS_TS_QUEUES
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)'
,SORT_LIST => 'PRIORITY,ENQ_TIME'
,MULTIPLE_CONSUMERS => TRUE
,MESSAGE_GROUPING => 0
,COMMENT => 'Workflow JMS Topic'
,SECURE => FALSE
);
End;


CREATE OR REPLACE SYNONYM APPS.WF_NOTIFICATION_OUT FOR APPLSYS.WF_NOTIFICATION_OUT;
GRANT SELECT ON APPLSYS.WF_NOTIFICATION_OUT TO APPS WITH GRANT OPTION;
GRANT SELECT ON APPLSYS.WF_NOTIFICATION_OUT TO EM_OAM_MONITOR_ROLE;


BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE
(
QUEUE_NAME => 'APPLSYS.WF_NOTIFICATION_OUT'
,QUEUE_TABLE => 'APPLSYS.WF_NOTIFICATION_OUT'
,QUEUE_TYPE => SYS.DBMS_AQADM.NORMAL_QUEUE
,MAX_RETRIES => 5
,RETRY_DELAY => 3600
,RETENTION_TIME => 86400
,COMMENT => 'Workflow JMS Topics'
);
END;


DECLARE
aSubscriber sys.aq$_agent;
BEGIN
aSubscriber := sys.aq$_agent('WF_NOTIFICATION_OUT',
'',
0);
dbms_aqadm.add_subscriber
( queue_name => 'APPLSYS.WF_NOTIFICATION_OUT'
,subscriber => aSubscriber
,rule => '1=1');
END;
/

BEGIN
SYS.DBMS_AQADM.START_QUEUE
(
QUEUE_NAME => 'APPLSYS.WF_NOTIFICATION_OUT'
,ENQUEUE => TRUE
,DEQUEUE => TRUE
);
END;
/


BEGIN
sys.dbms_aqadm.grant_queue_privilege
('ENQUEUE', 'APPLSYS.WF_NOTIFICATION_OUT', 'APPS', TRUE);
sys.dbms_aqadm.grant_queue_privilege
('DEQUEUE', 'APPLSYS.WF_NOTIFICATION_OUT', 'APPS', TRUE);
END;
/



Execute following from the application node..sqlplus apps @$FND_TOP/patch/115/sql/wfntfqup.sql APPS apps APPLSYS
sqlplus apps/apps @$FND_TOP/patch/115/sql/wfjmsqc2.sql applsys apps
sqlplus apps/apps @$FND_TOP/patch/115/sql/wfmqsubc2.sql APPLSYS APPS
sqlplus apps/apps @$FND_TOP/patch/115/sql/wfntfqup APPS apps APPLSYS --> again

3) Imap related problems:

For Imap related problems, check your mailer log .. Especially, lines containing inboundThreadGroup.. These lines are for the Imap related messages..
Possible problems with IMAP are;
  • PROCESS and INBOX folders missing-> If this is the case, create those folder properly for your IMAP account in your IMAP server.
  • Autoreply or similar unrelated email are sitting in your IMAP inbox folder - > clear them by connecting to your IMAP server..
  • Unable to connect mail store  > If these kind of errors are seen in your log; 
You should do the the following things , check and take actions if necessary;

  • Stop the mailer;
  • Use the Workflow Mailer configuration screens in OAM and change the TAG section to create entries to ignore junk and spam e-mails:
(In Oracle Applications Manager (OAM) --> Navigate to Workflow Manager --> Notification Mailers --> Edit --> Advanced --> Step 6)
For instance, if one of the e-mails that are bugging the mailer has the abstract "AUTOREPLY MESSAGE: The user JSMITH is on holidays" then the text of the new entry can have 'AUTOREPLY MESSAGE' (no quotes) and the action can be ignored.
This way all the messages coming from users with similar subject will be ignored by the java mailer.
Clean the IMAP account mail folders

  • Using any desktop imap client such as Outlook, logon to the imap account and review the contents of the inbox. Remove any spam messages from the inbox. Ensure that any valid message responses are set to 'unread' when you're done. Each IMAP message carries a read / unread flag in the message header and the mailer will not pick up any messages marked as ‘read’. Use the IMAP client tools to delete and purge excess messages from Process and Discard Folders. Don’t delete these folders and don’t use Unix command line utilities to truncate folders from the file system. Use the IMAP client tool instead. When the account is cleaned up, log off of the account and remove the account from your desktop mail client so that you don’t accidentally automatically log in while the mailer is ever running.  Start the mailer using the OAM Workflow manager console.
  • If you have TEST or DEV instances, you should check them.. They must have dedicated IMAP accounts for their notification mailer.Make sure that no other instance is using the same Workflow Mailer account as set in the instance where the issue appears.
You can run the following query in your environments and determine the configured IMAP server accounts..

select c.component_id, c.component_name, 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 ( 'INBOUND_SERVER','ACCOUNT', 'REPLYTO')
order by c.component_id, c.component_name,p.parameter_name;


  • Make sure that Workflow Mailer account is not monitored using an email client while Workflow Notification Mailer is up and running. Folders of this account should be monitored only having the Workflow Notification Mailer stopped to prevent locking it.
  • Make sure that "Processor Close on Read Timeout" and "Expunge Inbox on Close" parameter is checked.
    To check current value you may execute below SQL statement and the value field should be 'Y':
     select c.component_id, c.component_name, 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 = 'PROCESSOR_READ_TIMEOUT_CLOSE'
    order by c.component_name,p.parameter_name;
  • Make sure your IMAP server is reachable from the application server node.
  • use telnet - > telnet <imap_server_name> 143
    And use the actual java program ->
AFJVAPRG -classpath $AF_CLASSPATH -Ddebug=Y -Dprotocol=imap \
-Ddbcfile=$FND_SECURE/<filename>.dbc \
-Dserver=<imap_server_name> -Dport=143 \
-Daccount=<workflow_account> -Dpassword=<password for this account> -Dconnect_timeout= 120 \
-Dfolder=INBOX oracle.apps.fnd.wf.mailer.Mailer

Check the email address using the following query.. Make sure that there are not email addresses which contains a white space..


If below SQL statement is returning rows you need to correct the email addresses for associated users:
col name format a25
col email_address format a35
select name, '['||substr(email_address, 1, 25)||']', orig_system from wf_local_roles where email_address like '% %'


  • Increase the Inbound connection time out parameter to 200 or higher (default value is 120) (In OAM --> Navigate to Workflow Manager --> Notification Mailers --> Edit --> Advanced --> Step 3)
  • Check with your IMAP server administrator that there is no time out parameter set to a low value at IMAP server level. This may prevent Workflow Notification Mailer to keep the connection active in order to process the responses.
  • Unsolicited emails in the INBOX can cause the inbound thread used by the mailer to be orphaned. This thread locks the account and prevents any new connection from being made. A new exception was discovered in the latest version of the mailer code.. Check the followind Support not for the fix: Workflow Mailer Is Not Able to Handle Unsolicited Emails Due to Error: java.io.IOException: Error in encoded stream OR java.sql.SQLException: Closed Statement (Doc ID 974088.1)
4. Preference (FND_USER_PREFERENCES) MAILTYPE problems:
The MAILTYPE user preference may be disabled for your users.. This will prevent notification mailer from sending emails to them..
You can check mailtype preference using following query;
select PREFERENCE_VALUE
from FND_USER_PREFERENCES
where PREFERENCE_NAME='MAILTYPE' ;

Also check the workflow tables for the preference,select notification_preference from wf_roles ;
select notification_preferenc from wf_local_roles;


If you see the mailtype preference is disabled for a user, this means that user will not receive any emails..

You may update the preference if necessary.
FND_PREFERENCE.put(p_user_name => 'USER_NAME',
p_module_name => l_module_name,
p_pref_name => l_pref_name,
p_pref_value => l_new_pref_value);


Also update the wf_local_roles table accordingly..

UPDATE wf_local_roles
SET NOTIFICATION_PREFERENCE = l_default_preference
WHERE NAMe =l_notify_user;


Options for the MAILTYPE preference are as follows,
Plain text mail (MAILTEXT) - The notification message appears as plain text, with no attachments.

HTML mail with attachments (MAILHTML) - The notification message appears as HTML-formatted text, with at least one standard attachment that is a link to the notification in the Notification Details web page. If the notification message has 'Content-Attached' message attributes, these attributes appear as additional custom attachments to the message.
HTML mail (MAILHTM2) - The notification message appears as HTML-formatted text, but does not include any standard attachments. If the notification message has 'Content-Attached' message attributes, however, these attributes appear as custom attachments to the message.
Attention: If you wish to view notifications with HTML formatting, but your mail reader is not able to interpret HTML formatting in the mail message body, change your notification preference to 'Plain text mail with HTML attachments' (MAILATTH). The MAILATTH preference delivers an HTML-formatted version of the notification as an attachment to the plain text notification.

Plain text mail with HTML attachments (MAILATTH) - The notification message appears as plain text, with at least two standard attachments. One attachment is an HTML-formatted version of the message, and the other is a link to the notification in the Notification Details web page. If the notification message has 'Content-Attached' message attributes, these attributes appear as additional custom attachments to the message. 

Plain text summary mail (SUMMARY) - The message is a plain text summary of all open notifications. To respond to the individual notifications in the summary, you must access the notifications from the Worklist web page.

HTML summary mail (SUMHTML) - The message is an HTML-formatted summary of all open notifications, with a link to the Worklist web page as well as links to each notification in the Notification Details web page. To respond to the individual notifications in the summary, you must access the notifications from the web pages. This notification preference is currently available only in the version of Oracle Workflow embedded in Oracle Applications.

Do not send me mail (QUERY) - The notification mailers do not send you e-mail notifications. Instead you must query and respond to your notifications from the Worklist web page.
Attention: You can always query and respond to your notifications from the Worklist web page, even if you set your notification preference to send you mail.

5) OTHER -- Diagnosing the full process:


If the problems and fixes explained above are not related with the problem ; you need to debug the Full process. We need to investigate the full process for SMTP or IMAP according to the problem..
What I mean by "full process" is the following;

For SMTP:



For IMAP:


So we need to investigate and debug all the processes described in the above diagram, one by one..

Lets start with SMTP:

  • Check the first process.. Check the workflow encounters any error while initiating the request for the notification:
Query WF_NOTIFICATIONS and check the desired notification.. Was the row for the notification inserted into that table? Any errors? .. Check the database.. (for tablespace usage , or any errors recorded in Alert log..)
Use the wfstat.sql script to diagnose the issue further.

  • Check the WF_DEFERRED queue..  The messages should be enqueued in this queue. IF you see any problem in this phase, check for database again.. Also note that : WF_DEFERRED queue may be bypassed if oracle.apps.wf.notification.send event is set to Synchronous
  • Check if  the Mailer Agent Listener picks up the message from the WF_DEFERRED queue..
  • The correlation ID for dequeuing the message may not match the correlation ID specified for the enqueued message. Check the configuration of the Notification Mailer Agent Listener and ensure that the Correlation ID is blank.
    If this occurs particularly after a database upgrade, then a rebuild of the notification mailer queues may be required. For rebuilding you can use these sqls: 
    wfevqcln.sql wfntfqup.sql
  • Check if there is any exceptions encountered during the processing of the XML.. Use the wfmlrdbg.sql to extract the content of the WF_ERROR queue for the notification .After correcting problem, you may retry the operation
  • Check if the notification is processed and the generated XMLis enqueued in the WF_NOTIFICATIONS_OUT.. If you dont see a record for the desired notification:
  • The Workflow Deferred Notification Agent Listener may not be running.. If that is the case, start it..  Note that: You may not start it directly.. In that case, you need to fix the underlying problem.. The underlying problem may be the agent listener service, concurrent managers ,  GSM or other runtime errors. Following lists the possible causes and needed actions for fixing them:
The Agent Listener Service is shut down
The Agent Listener Service should be restarted.
The Concurrent Manager is not running
Do the throubleshooting, fix the problem if there are any and start the concurrent manager
A GSM issue. Either not configured properly or not running.
Do the throubleshooting and fix the GSM errors.
Other Errors:
Errors will be recorded in the Workflow Deferred Notification Agent Listener log file. This can be found in the $APPLCSF/$APPLLOG directory. Use the following command to locate the log file:

grep -l WFALSNRSVC FNDCPGSC*.txt
The highest numbered file will be the most recent.
Note that errors such as "oracle.apps.fnd.cp.gsc.server.GenericSvcComponentEOException: ORA-06508: PL/SQL: could not find program unit being called" imply an invalid package. Check the state of the database packages.

If you cant find any problems using the checks above . Then check that the event for oracle.apps.wf.notification.send and oracle.apps.wf.notification.send.group are enabled and that the subscriptions for oracle.apps.wf.notification.send.group are defined and enabled and that the Generate function of WF_XML.Generate is defined for the oracle.apps.wf.notification.send event.

Make sure that the local system is enabled
select name, text
from wf_resources
where name like 'WF_SYSTEM_STATUS';

Also check for tablespace issues..

Check wf_notification_out queue.. Check to see the PROCESSED message counts are increasing.. If everyting is normal READY state messages should be put into PROCESSED state ..

select corr_id, msg_state, retry_count, count(Msg_id)
from applsys.aq$wf_notification_out
group by corr_id, msg_state, retry_count;


  • If there are a lot of ready messages, and processed ones are not increasing, check if the notification mailer is running.. Do the same checks, as I mentioned above for the Workflow Deferred Notification Agent Listener .. In addition check the database, IMAP and Outbound's SMTP server connectivity. If the notification mailer is up, set its log level to statement, and analyze the log in detail.. In addition make a telnet test from the application server , and ensure the emails are sent..
  • telnet [outbound server] 25
    EHLO [mailer node]
    MAIL FROM: [reply_to address]
    RCPT TO: [my_test_email_address]
    DATA
    Subject: Test message

    Test message body
    .
    quit
    Also you may test the smtp connection using Oracle's java program;
     $AFJVAPRG -classpath $AF_CLASSPATH  -Dprotocol=smtp \
            ( -Ddbcfile=<dbcfileLocation_here> | -Ddbuser -Ddbpassword -Ddburl )\
            -Dserver=<servername_here> [-Dport=<port> default 25 ] \
            [-DoutboundUser=<smtp_user_name_here> \
            [-DoutboundPassword=<smtp_user_password_here> \
            [ -Dssl= <Y|N> default N ]\
            [ -Dtruststore=<truststore_here> ]\
            [ -Dconnect_timeout=<seconds> default 5 ] \
            [ -Ddebug=<Y|N> default N ]\
            [ -Dlogfile=<log filename> default test.log ]\
       oracle.apps.fnd.wf.mailer.Mailer

  • Again check the email address. They must be in the form of  local-part "@" domain..
  • Note that : Rejection by the SMTP server to accept any recipient will cause the mailer to set the notification status to FAILED and raise the WFMLRSND_FAILED_UNDELIVERABLE error with a list of the recipients that failed delivery. This will result in the oracle.apps.wf.notification.send.failure event. The subscription for this event will then mark each of the recipients that, failed delivery, with a notification preference of DISABLED.
  • Other communication issues between the Notification Mailer and the SMTP server can be also verified with the Notification Mailer configuration parameter DEBUG_MAIL_SESSION. Setting this to "Y" will cause the SMTP dialogue to be displayed in the log file. 
To enable DEBUG_MAIL_SESSION;

run sqlplus apps/<apps_pw> @$FND_TOP/sql/afsvcpup.sql
sqlplus apps/apps @$FND_TOP/sql/afsvcpup.sql
SQL*Plus: Release 10.1.0.5.0 - Production on Tue Aug 19 15:51:02 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options


Component Id Component Name Component Status Type Containe
------------ ------------------------------ ---------------- --------------- --------
10000 ECX Inbound Agent Listener STOPPED WF_AGENT_LISTEN GSM
10001 ECX Transaction Agent Listener STOPPED WF_AGENT_LISTEN GSM
10002 Workflow Deferred Agent Listen RUNNING WF_AGENT_LISTEN GSM
10003 Workflow Deferred Notification RUNNING WF_AGENT_LISTEN GSM
10004 Workflow Error Agent Listener RUNNING WF_AGENT_LISTEN GSM
10005 Workflow Inbound Notifications RUNNING WF_AGENT_LISTEN GSM
10006 Workflow Notification Mailer RUNNING WF_MAILER GSM
10020 Web Services IN Agent STOPPED WF_JAVA_AGENT_L GSM
10021 Web Services OUT Agent STOPPED WF_DOCUMENT_WEB GSM
10022 Workflow Java Deferred Agent L RUNNING WF_JAVA_AGENT_L GSM
10023 Workflow Java Error Agent List RUNNING WF_JAVA_AGENT_L GSM
10040 WF_JMS_IN Listener(M4U) RUNNING WF_JAVA_AGENT_L GSM
10041 Workflow Inbound JMS Agent Lis STOPPED WF_AGENT_LISTEN GSM

Enter 10006 as , it belongs to Notification mailer.

Then Enter 10061 for setting Debug Mail Session (default to N)
Lastly Update Debug Mail Session parameter to 'Y'

INBOUND (IMAP):
  • If users can not send response email, check SMTP server, it may not be up.
  • TEST IMAP connectivity using the following example:
  • telnet IMAP_SERVERS_IP 143
    Trying 192.168.10.10...
    Connected to localhost.
    Escape character is '^]'.
    * OK
    A1 OK LOGIN completed
    --> A2 list "" "*"
    --> list all folders
    * LIST () "/" "INBOX"
    * LIST () "/" "Mickey"
    * LIST () "/" "Minnie"
    a2 OK LIST completed
    --> A3 select "INBOX"
    --> selecting a folder
    * 5 EXISTS
    * 0 RECENT
    * OK [UIDVALIDITY 1] UID validity status
    * FLAGS (\Seen \Deleted \Answered \Flagged \Draft X-ORACLE-COMMENT $MDNSENT X-DS
    NREQUESTED X-MDNREQUESTED X-DELETEAFTERSUBMIT X-ORACLE-FILTERED)
    * OK [PERMANENTFLAGS (\Seen \Deleted \Answered \Flagged \Draft X-ORACLE-COMMENT
    $MDNSENT X-DSNREQUESTED X-MDNREQUESTED X-DELETEAFTERSUBMIT X-ORACLE-FILTERED)] P
    ermanent flags
    * OK [UNSEEN 5] 5 is first unseen
    a3 OK [READ-WRITE] SELECT completed
    --> A4 fetch 1:2 RFC822
    --> fetch mail number 1 and 2 and see the mail including header information
    * 1 FETCH (RFC822 {497}
    Return-Path: <dagobert.duck@ducksville.com>
    Received: from sniblade5.ducksville.com by sniblade5.ducksville.com
    with ESMTP id 10031084275002; Tue, 11 May 2004 13:30:02 +0200
    Received: (from orasup@localhost)
    by sniblade5.ducksville.com (8.11.6/8.11.6) id i4BBTwY06042
    for donald.duck@ducksville.com; Tue, 11 May 2004 13:29:58 +0200
    Date: Tue, 11 May 2004 13:29:58 +0200
    From: dagobert.duck@ducksville.com
    Message-Id: <200405111129.i4BBTwY06042@ducksville.com>

    Hello Donald.
    )
    * 2 FETCH (RFC822 {561}
    Return-Path: <orclguest@ducksville.com>
    Received: from sniblade5.ducksville.com by sniblade5.ducksville.com
    with ESMTP id 10041084275106; Tue, 11 May 2004 13:31:46 +0200
    Message-ID: <812650.1084275106601.JavaMail.orasup@sniblade5.ducksville.com>
    Date: Tue, 11 May 2004 13:31:46 +0200 (CEST)
    From: orclguest@ducksville.com
    To: donald.duck@ducksville.com
    Subject: hello from webmail
    Mime-Version: 1.0
    Content-Type: text/plain; charset=ISO-8859-1
    Content-Transfer-Encoding: 7bit
    X-Priority: 3
    X-Mailer: Oracle Webmail Client(UIX)

    )
    --> A99 logout
    * BYE Oracle IMAP4 server terminating connection
    A99 OK LOGOUT completed
    Connection closed by foreign host.

  • In addition ; Use Application Diagnostics responsibility to test IMAP connections
To execute the test, do the following:

Start Oracle E-Business Suite
Connect to responsibility Application Diagnostics
Select the Diagnose menu option
Click button Select Application and select Application "Oracle eMail Center"
Scroll down to group "Email Account"
Select test name "IMAP Connection"
Input Parameters (* required)N/A
Output containsReports if there are any problems connecting to the IMAP Server.

You may use oracle's java to test the Imap connections, as well...


   $AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap  \
        ( -Ddbcfile=<dbcfileLocation_here> | -Ddbuser -Ddbpassword -Ddburl )\
        -Dserver=<servername_here> [-Dport=<port> default 143] \
        -Daccount=<accountname_here> -Dpassword=<password_here> \
        [ -Dfolder=<foldername_here> ]  \
        [ -Dconnect_timeout=5 ] \
        [ -Dssl= <Y|N> default N ] \
        [ -Dtruststore=<truststore_here> ]\
        [ -Dconnect_timeout=<seconds> default 5 ] \
        [ -Ddebug=<Y|N> default N ]\
        [ -Dlogfile=<log filename> default test.log ]\
        oracle.apps.fnd.wf.mailer.Mailer

  • Check wf_notifications_in queue .. If you cant find the desired notification there check the following; 
Database connectivity and database object status
Database table space issues
Invalid Objects
Business Event System Status

  • Check if the Workflow Inbound Notification Agent Listener is running.
The Agent Listener Service is shut down
The Agent Listener Service should be restarted.
The Concurrent Manager is not running
Do the throubleshooting, fix the problem if there are any and start the concurrent manager
A GSM issue. Either not configured properly or not running.
Do the throubleshooting and fix the GSM errors.
Other Errors:
Errors will be recorded in the Workflow Deferred Notification Agent Listener log file. This can be found in the $APPLCSF/$APPLLOG directory. Use the following command to locate the log file.
grep -l WFALSNRSVC FNDCPGSC*.txt
The highest numbered file will be the most recent.
Note that errors such as "oracle.apps.fnd.cp.gsc.server.GenericSvcComponentEOException: ORA-06508: PL/SQL: could not find program unit being called" imply an invalid package. Check the state of the database packages.

If you cant determine the error in the start process Workflow Inbound Notification Agent Listener, use following script to start the listener in command line and gather the information.

sqlplus -s $1 << SESSION_END
set serveroutput on size 1000000;
spool listen.log;
prompt Executing listener
exec wf_event.listen(p_agent_name => 'WF_NOTIFICATION_IN', p_correlation => 'oracle.apps.wf.notification.receive%');
prompt Obtaining output. This may take a few minutes...
spool off;
quit;
SESSION_END
  • Check  WF_ERROR table.. If you see errors there, use wfmlrdbg.sql for the respective notifications will provide the WF_ERROR message details, as will the output of the wfstat.sql script.

11 comments :

  1. Hi!
    Any Idea how to query wf_notification_out user_data column ?
    For example If I want to see that specific alert went out , i cannot query on that data..
    Tried but not working:
    SELECT user_data.GetValueForParameter('NOTIFICATION_ID') NOTIFICATION_ID
    ,A.user_data.GetValueForParameter('ROLE') ROLE
    ,A.user_data.GetValueForParameter('LANGUAGE') LANGUAGE
    , A.user_data.GetValueForParameter('GROUP_ID') GROUP_ID
    ,A.user_data.GetValueForParameter('TERRITORY') TERRITORY
    ,A.user_data.GetValueForParameter('Q_CORRELATION_ID') Q_CORRELATION_ID
    ,A.MSG_STATE
    ,A.CORR_ID
    from applsys.aq$wf_notification_out A where corr_id='APPS:ALR:'
    ;
    Thanks,L

    ReplyDelete
  2. Hi,

    Check this way;

    use $FND_TOP/sql/wfmlrdbg.sql with notification ids.
    wfmlrdbg.sql will produce you lots of details.
    (use the notification id gathered from wf_notifications)
    If you can link wf_notifications and applsys.aq$wf_notification_out and bring it further.

    ReplyDelete
  3. Hi ! Thanks for replay.. But this is my problem.
    I dont have alert in wf_notifications table, so i DONT know that notification id.
    SO, I cannot search by notification_id.
    Its Alerts and not the normal WF notification.
    Any other suggestion ?
    Thanks,L

    ReplyDelete
  4. That column is in AQ$_JMS_TEXT_MESSAGE.
    So you should play with AQ$_JMS_TEXT_MESSAGE type and create your query.

    Here is an example;

    select wfc.user_data.text_vc
    from applsys.aq$wf_notification_out wfc
    where corr_id = 'APPS:ALR';

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. You are running the command wrongly.
    You didnt use -Ddbcfile

    Check the blog post and write the command properly.

    ReplyDelete
    Replies
    1. The correct syntax is below, please modify your command accordingly.

      AFJVAPRG -classpath $AF_CLASSPATH -Ddebug=Y -Dprotocol=imap \
      -Ddbcfile=$FND_SECURE/.dbc \
      -Dserver= -Dport=143 \
      -Daccount= -Dpassword= -Dconnect_timeout= 120 \
      -Dfolder=INBOX oracle.apps.fnd.wf.mailer.Mailer

      Delete
  7. Also ,please use my forum for ask questions..

    That is, you can open an issue in my forum and ask me your question if you need assistance
    my forum : http://ermanarslan.blogspot.com.tr/p/forum.html

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  8. Hi Emran,

    can you assist me to configure oracle workflow mailer using oracle 365 cloud account, i tried to configure but failed. currently i am trying to configure it in R12.2.4 environment. Note:2077434.1 Configuring an Oracle Workflow Notification Mailer with Cloud E-Mail Servers
    applied patch as per the document. patch id 24290674. i have installed certificates from office 365 server directly as mentioned here http://oradig.com/configuring-outlook365-imapsmtp-ssl-server-with-oracle-r12-workflow-mailer/

    ReplyDelete
  9. Please use my forum to create an issue and ask your questions from there.
    Subscribe to my forum, and ask your question. You can upload files(logs,screenshots) to there as well.

    Here is the link:

    http://ermanarslan.blogspot.com.tr/p/forum.html


    I give tech support through my forum.. (not through emails or through comments)

    Please create your issue .

    ReplyDelete