Step by Step Workflow Mailer configuration

Source the Application ENV
. /VISTEST/PATCH/apps/apps_st/appl/APPSPATCH_our12-dev.env

keytool -genkey -alias bilt -keystore /home/oradev/.keystore

What is your first and last name?
[Unknown]:  Alice
What is the name of your organizational unit?
[Unknown]:  developerWorks
What is the name of your organization?
[Unknown]:  IBM
What is the name of your City or Locality?
[Unknown]:  Winchester
What is the name of your State or Province?
[Unknown]:  Hampshire
What is the two-letter country code for this unit?
[Unknown]:  UK
Is <CN=Alice, OU=developerWorks, O=IBM, L=Winchester, 
ST=Hampshire, C=UK> correct?

[no]:  yes

Get the Server Certification
openssl s_client -host 10.1.0.209 -port 993

keytool -import -file /home/oradev/bilt.crt -keystore /home/oradev/.keystore -alias our12-dev


$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap \ -Ddbcfile=/VISTEST/PATCH/inst/apps/PATCH_our12-dev/appl/fnd/12.0.0/secure/PATCH.dbc  \ -Dport=993 -Dssl=Y \ -Dtruststore=/home/oradev/.keystore \ -Dserver=10.1.0.208 -Daccount=our12.mailtest -Dpassword=******** \ -Dconnect_timeout=120 -Ddebug=Y \ -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer

Follow the below Metalink Note

IMAPSSL Workflow Mailer Setup Using Self Signed Certificate [ID 409026.1]
      Modified 22-SEP-2009     Type HOWTO     Status PUBLISHED    

In this Document
  Goal
  Solution

Applies to:
Oracle Workflow Cartridge - Version: 11.5.10 to 11.5.10.2
Information in this document applies to any platform.
4334965, '11i.ATG_PF.H RUP3'
4676589, '11i.ATG_PF.H.RUP4'
Goal

The purpose of this document is to enable customers to use a self signed certificate with the Workflow Mailer for IMAPSSL.  These steps must be followed in order to avoid the untrusted certificate error being thrown by the minimum supported J2SE (JDK) 1.4.2 for the concurrent manager node.  The Workflow Mailer uses the certificate keystore of the J2SE.  Patch 4676589, '11i.ATG_PF.H.RUP4' is minimum recommended version of ATG for users of IMAPSSL.

javax.mail.MessagingException: sun.security.validator.ValidatorException: No trusted certificate found;

ASSUMPTION

The 3rd party IMAP email server is already configured, tested and verified that it will work with IMAPSSL.  Oracle does not provide instructions on how to implement IMAPSSL on 3rd party products.
Solution

1. Source APPSORA.env

2. Place your self signed certificate created for your IMAP Email Server on the Concurrent Manager Node assigned to the Workflow Mailer Service.

3. Use J2SE 1.4.2 and the J2SE(JDK) keytool to create a standalone keystore or import and trust your self signed certificate into the JDK keystore.

    a. Login as the OS user that owns the appsTier containing the Concurrent Manager Server node running the Workflow Mailer Service.

    b. It is better to create a standalone keystore to mimimize maintenance as the J2SE keystore (cacert) will be different everytime the JDK is upgraded to a new version.

    c. Keytool will create a hidden file called .keystore in the OS user home directory.

NOTE:  Keytool is a 3rd party utility whose syntax is not supported by Oracle.  Please research on the Internet if unfamiliar with utility.

4. Test that your keystore is valid from the command line:

a. Connectivity of IMAP server
    ------------------------------
Test invocation is:
$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

b. IMAPSSL Test Example and Valid Result
----------------------------------------------

 $AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap \
-Ddbcfile=$FND_TOP/secure/VIS_orlncatst-02/vis.dbc.dbc \
-Dport=993 -Dssl=Y \
-Dtruststore=/home/applmgr/.keystore \
-Dserver=gggrant2.us.oracle.com \
-Daccount=orlncatst02 -Dpassword=orlncatst02 \
-Dconnect_timeout=120 -Ddebug=Y \
-Dlogfile=/tmp/garyimaptest.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer

Server gggrant2.us.oracle.com at port 993 is reachable
Debug property -> {true}

DEBUG: getProvider() returning javax.mail.Provider[STORE,imap,com.sun.mail.imap.IMAPStore,Sun Microsystems, Inc]
* OK dovecot ready.
A0 CAPABILITY
* CAPABILITY IMAP4rev1 SORT THREAD=REFERENCES MULTIAPPEND UNSELECT LITERAL+ IDLE CHILDREN LISTEXT LIST-SUBSCRIBED NAMESPACE AUTH=PLAIN
A0 OK Capability completed.
A1 LOGIN orlncatst02 orlncatst02
A1 OK Logged in.
A2 NOOP
A2 OK NOOP completed.
Successfully connected to the IMAP account
Note: Special folders like Inbox/Trash may not get listed on some IMAP servers
Folders defined are:
A3 LSUB "" "*"
* LSUB () "/" "Trash"
* LSUB () "/" "Processed"
A3 OK Lsub completed.
Trash
Processed
A4 LOGOUT
* BYE Logging out
A4 OK Logout completed.
A5 LOGOUT

5. Verify that you have the Workflow Mailer already configured and working on the default non-SSL port 143. If 143 is not available, login to E-Business Suite (EBS) and navigate to Workflow Mailer inside OAM and set Inbound Thread Count = 0 so that you can input the IMAP User Name and Password without validation.

Login to E-Business as a user assigned the System Administrator responsibility and navigate:

System Administrator > Oracle Applications Manager > Workflow > Notification Mailers > Edit > Advanced

6. Run $FND_TOP/sql/afsvcpup.sql from sqlplus to set the following parameters for the Workflow Mailer:

Working Example on ATG RUP3
----------------------------------
NOTE: You can get the component id and parameter id by running these queries:


SELECT component_id, component_name
FROM fnd_svc_components c
WHERE component_name like 'Workflow Notification Mailer'
order by component_id;

COMPONENT_ID COMPONENT_NAME
------------ --------------------------------------------------------------------------------
10006 Workflow Notification Mailer


set pagesize 100
set linesize 132
set feedback off
set verify off
set wrap off

col comp_param_id 999999999
col parameter_value format a35
col component_name format a30

select v.component_parameter_id comp_param_id, v.parameter_value, c.component_name
from fnd_svc_comp_param_vals_v v, fnd_svc_comp_params_b p, fnd_svc_components c, fnd_svc_comp_params_vl vl
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and vl.parameter_id = p.parameter_id
and p.parameter_name in ('MAILER_SSL_TRUSTSTORE');

COMP_PARAM_ID PARAMETER_VALUE                     COMPONENT_NAME
------------- ----------------------------------- ----------------------------
        10475 /home/applmgr/.keystore             Workflow Notification Mailer
        11011 NONE                                Cs_MsgsMailer
        10741 NONE                                AG_Mailer
        10521 NONE                                Oracle Alert Email

    a. Example:

    Updating Debug Mail Session:

    sqlplus apps/<apps_pw> @$FND_TOP/sql/afsvcpup.sql


    Enter Component Id:<component id 10006 for Workflow Notification Mailer>


    Enter the Parameter Id to update : <parameter id  10025 for Debug Mail Session >

    You have selected parameter : Debug Mail Session
    Current value of parameter : N

    Enter a value for the parameter : Y

    b. Update the following Parameters:

    10082 Inbound Thread Count 1
    10025 Debug Mail Session Y (Optional setting to obtain diagnostics data and should be set to N after a successful test)
    10140 Inbound SSL Enabled Y
    10475 SSL Trust store /home/applmgr/.keystore (My standalone keystore)

7. Shutdown and restart the Workflow Mailer Service from inside OAM.

8. Confirm that the Workflow Mailer starts and will process inbound responses from the Workflow Mailer inbox.