Oracle EBS Workflow Gmail/Office365 Configuration Step by Step

Cihan Gedik

5 years ago

I would like to start word “Painful” for this blog spot, My colleagues who are working as Oracle Application DBAs, they all know, sometimes how hard it is to configure Gmail or Office 365 mail account for E-Business System ( EBS ) workflow.

Important Notes :

SMTP Server : aspmx.l.google.com —->>>> Read Important Note Uncheck Outbound SSL Enabled
https://support.google.com/a/answer/176600?hl=tr For SMTP site : smtp.gmail.com:465/587 is using user authentication but EBS 11i ( 11.5.10.2 Version ) is not compatible for this function.
(Doc ID 564361.1) According to the important note gmail have, other options for smtp relay one of them is IP authentication ( smtp-relay.gmail.com) which is not worked for Our Customer WF another one is, no authentication required option ( aspmx.l.google.com ) thats why we used it but There is limit for sending email, It is 2000 Email you can send.

Here is the detailed steps, I configured for many of our customers for the last 2 years.

Prerequisites

Activate 2-Step verification on your gmail or office 365 account
Create application password from gmail/office 365 account settings ( This password will be generated on your account security section )
- Go to this website and check “How to generate an App password” https://support.google.com/accounts/answer/185833?hl=en
- Download certificates from ;

Office 365

Gmail

For Office365 you might not need to import certificate, It is up to the EBS version/latest update of Office365 ( These are the steps in case of you need )
Smtp,Imap servers and ports
Before you start configuration, run 2 java commands ( instead of telnet ) for imap and smtp verification.
Create Discard and Process folder in your email account.

After you have prerequisites, Please test it and see the sample result.

############ TEST FOR IMAP ########################

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Ddbcfile=$FND_SECURE/prod.dbc -Dserver=imap.gmail.com -Dport=993 -Dsecurity=SSL -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount=cihan@ittutorial.gov -Dpassword=password -Dconnect_timeout=600 -Ddebug=Y -DdebugMailSession=Y -Dlogfile=gmailtest6.log oracle.apps.fnd.wf.mailer.Mailer Mailer > gmailtest5.log 2>&1

TEST Succesful Result Should be like this. You should see the folders ;

[applmgr@krb lib]$ cat gmailtest6.log [Mar 20, 2017 4:58:21 PM EET]:1490021901548:Thread[main,5,main]:0:-1:mail.mailserver.com:192.168.0.55:-1:-1:EXCEPTION:[MLRTST : oracle.apps.fnd.cp.gsc.Logger.start(WorkflowContext, int)]:Logger has been successfully started -> Logger{mLog=[$Header: AppsLog.java 115.78 2004/12/16 16:17:13 rnix ship $ @11317592 {oracle.apps.fnd.profiles.ExtendedProfileStore@1af33d6}],mUniqueId=MLRTST,mLevel=1} [Mar 20, 2017 4:58:22 PM EET]:1490021902549:Thread[main,5,main]:0:-1:mail.mailserver.com:192.168.0.55:-1:-1:EXCEPTION:[MLRTST : oracle.apps.fnd.wf.mailer.Mailer.hostConnectionTest(WorkflowContext, Logger, Properties)]:Server {imap.gmail.com} at port {993} is reachable [Mar 20, 2017 4:58:22 PM EET]:1490021902550:Thread[main,5,main]:0:-1:mail.mailserver.com:192.168.0.55:-1:-1:EXCEPTION:[MLRTST : oracle.apps.fnd.wf.mailer.Mailer.transportTest(WorkflowContext, Logger, Properties]:Debug property -> {true} [Mar 20, 2017 5:08:22 PM EET]:1490022502609:Thread[main,5,main]:0:-1:mail.mailserver.com:192.168.0.55:-1:-1:ERROR:[MLRTST : oracle.apps.fnd.wf.mailer.Mailer.transportTest(WorkflowContext, Logger, Properties]:Unable to connect to SMTP account {cihan@ittutorial.gov}, please verify the account details [Mar 20, 2017 5:33:05 PM EET]:1490023985030:Thread[main,5,main]:0:-1:mail.mailserver.com:192.168.0.55:-1:-1:EXCEPTION:[MLRTST : oracle.apps.fnd.cp.gsc.Logger.start(WorkflowContext, int)]:Logger has been successfully started -> Logger{mLog=[$Header: AppsLog.java 115.78 2004/12/16 16:17:13 rnix ship $ @24389376 {oracle.apps.fnd.profiles.ExtendedProfileStore@acb158}],mUniqueId=MLRTST,mLevel=1} [Mar 20, 2017 5:33:06 PM EET]:1490023986031:Thread[main,5,main]:0:-1:mail.mailserver.com:192.168.0.55:-1:-1:EXCEPTION:[MLRTST : oracle.apps.fnd.wf.mailer.Mailer.hostConnectionTest(WorkflowContext, Logger, Properties)]:Server {imap.gmail.com} at port {993} is reachable [Mar 20, 2017 5:33:06 PM EET]:1490023986032:Thread[main,5,main]:0:-1:mail.mailserver.com:192.168.0.55:-1:-1:EXCEPTION:[MLRTST : oracle.apps.fnd.wf.mailer.Mailer.storeTest(WorkflowContext, Logger, Properties)]:Debug property -> {true} [Mar 20, 2017 5:43:08 PM EET]:1490024588073:Thread[main,5,main]:0:-1:mail.mailserver.com:192.168.0.55:-1:-1:EXCEPTION:[MLRTST : oracle.apps.fnd.wf.mailer.Mailer.storeTest(WorkflowContext, Logger, Properties)]:Successfully connected to the IMAP account [Mar 20, 2017 5:43:08 PM EET]:1490024588427:Thread[main,5,main]:0:-1:mail.mailserver.com:192.168.0.55:-1:-1:EXCEPTION:[MLRTST : oracle.apps.fnd.wf.mailer.Mailer.storeTest(WorkflowContext, Logger, Properties)]: Note: Special folders like Inbox/Trash may not get listed on some

IMAP servers Folders defined are:

INBOX

Discard

Process [Gmail]

[Gmail]/Gönderilmiş Postalar

[Gmail]/Spam

[Gmail]/Taslaklar

[Gmail]/Tüm Postalar

[Gmail]/Yıldızlı

[Gmail]/Çöp kutusu

[Gmail]/Önemli

############ TEST FOR SMTP ########################

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Ddbcfile=$FND_SECURE/prod.dbc -Dserver=smtp.gmail.com -Dport=587 -Dsecurity=SSL -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount=cihan@ittutorial.gov -Dpassword=password -Dconnect_timeout=600 -Ddebug=Y -DdebugMailSession=Y -Dlogfile=gmailtest6.log oracle.apps.fnd.wf.mailer.Mailer Mailer > gmailtest5.log 2>&1

TEST Succesful Result Should be like this.

Successfully connected to the SMTP account; [applmgr@krb lib]$ cat gmailtest6.log [Mar 20, 2017 5:45:55 PM EET]:1490024755709:Thread[main,5,main]:0:-1:mail.mailserver.com:192.168.0.55:-1:-1:EXCEPTION:[MLRTST : oracle.apps.fnd.cp.gsc.Logger.start(WorkflowContext, int)]:Logger has been successfully started -> Logger{mLog=[$Header: AppsLog.java 115.78 2004/12/16 16:17:13 rnix ship $ @28259286 {oracle.apps.fnd.profiles.ExtendedProfileStore@17431b9}],mUniqueId=MLRTST,mLevel=1} [Mar 20, 2017 5:45:56 PM EET]:1490024756710:Thread[main,5,main]:0:-1:mail.mailserver.com:192.168.0.55:-1:-1:EXCEPTION:[MLRTST : oracle.apps.fnd.wf.mailer.Mailer.hostConnectionTest(WorkflowContext, Logger, Properties)]:Server {smtp.gmail.com} at port {587} is reachable [Mar 20, 2017 5:45:56 PM EET]:1490024756711:Thread[main,5,main]:0:-1:mail.mailserver.com:192.168.0.55:-1:-1:EXCEPTION:[MLRTST : oracle.apps.fnd.wf.mailer.Mailer.transportTest(WorkflowContext, Logger, Properties]:Debug property -> {true} [Mar 20, 2017 5:45:57 PM EET]:1490024757731:Thread[main,5,main]:0:-1:mail.mailserver.com:192.168.0.55:-1:-1:EXCEPTION:[MLRTST : oracle.apps.fnd.wf.mailer.Mailer.transportTest(WorkflowContext, Logger, Properties]:Successfully connected to the SMTP account

1- First of all you have to download certificate from site and import with below command :

Link : https://pki.google.com/

Import Certificate to the keystore

[aprelive@erp ~]$ ls –lrt

-rw-r–r–. 1 root root 1012 Jan 2 14:06 GIAG2.cer

[aprelive@erp]$ keytool -import -trustcacerts -keystore $AF_JRE_TOP/lib/security/cacerts -storepass changeit -alias GIAG2 -file GIAG2.cer

Certificate was added to keystore

2- Set your keystore location with backend sql to the location which is used in the import command. Use full path of the keystore

sqlplus apps/apps @$FND_TOP/sql/afsvcpup.sql

3- For Imap configuration. Connect to the mail server via webmail of the customer and create two folder under the inbox.

Process and Discard

4- Set your server name , username, password and Reply-To Address as in the screenshot. Apply the configuration and start WF.