How to debug SMTP or IMAP server network connections for Workflow mailer in Oracle Apps (EBS)

Below command will give you the debug log for SMTP Network Connections for Workflow mailer
in Oracle Apps
(please substitute the values as per your environment  in the below comand)

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp \
-Ddbcfile=$FND_SECURE/PROD.dbc \
-Dport=25 -Dssl=Y \
-Dtruststore=/home/applprod/.keystore \
-Dserver=mail.oscdubai.com \
-Daccount=xyz@gmail.com -Dpassword=pasword \
-Dconnect_timeout=120 -Ddebug=Y \
-DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer



Below command will give you the debug log for IMAP  Network Connections for Workflow mailer in Oracle Apps


$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap \
-Ddbcfile=$FND_SECURE/PROD.dbc \
-Dport=25 -Dssl=Y \
-Dtruststore=/home/applprod/.keystore \
-Dserver=mail.oscdubai.com \
-Daccount=xyz@gmail.com -Dpassword=pasword \
-Dconnect_timeout=120 -Ddebug=Y \
-DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer




However to Debug the flow you can pick up a particular notification id in WF_NOTIFICATIONS table which is having issue and run the below sql located in $FND_TOP/sql

Workflow debugging scripts wfmlrdbg.sql and wfbesdbg.sql

Query to find the Workflow Log file location (R12.2.x) 

=========================================

select fl.meaning,fcp.process_status_code,

decode(fcq.concurrent_queue_name,'WFMLRSVC','maile r container','WFALSNRSVC','listener container',fcq.concurrent_queue_name),

fcp.concurrent_process_id,os_process_id, fcp.logfile_name

from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl

where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'

and fl.lookup_type='CP_PROCESS_STATUS_CODE' and

fl.lookup_code=fcp.process_status_code

and concurrent_queue_name in('WFMLRSVC')

order by fcp.logfile_name;

How to use GMAIL ( IMAP / SMTP ) for your Workflow Notification mailer

Using GMAIL's IMAP & SMTP Server for  Configuring the Oracle Workflow Mailer 
===========================================


Doc ID 2052059.1
===============

1. Reference the following https://blogs.oracle.com/oracleworkflow/entry/using_gmail_smtp_and_imap

2. Create a New Account in GMAIL:

Located at https://www.gmail.com/intl/en/mail/help/about.html

Create an Account


Name = emailwork  charlery

Choose the username:

emailwork.charlery  <@gmail.com>

ie: emailwork.charlery@gmail.com

Password:

imapacct16



3. Once signed onto the new GMAIL account, go to the Left Menu.

-        Hover the mouse over the <More> menu, go down to the “Create New Label> Link.

-        Create a PROCESS, then a DISCARD Folder/Label.



 4. Record the GMAIL Server IMAP and SMTP Settings:



SMTP Server



HOST NAME

smtp.gmail.com

SSL Port

465

TLS/SSL required

Yes

User Name

emailwork.charlery@gmail.com

Password

imapacct16

IMAP Server



Host Name

imap.gmail.com

SSL Port

993

TLS/SSL Required

Yes

User Name

emailwork.charlery@gmail.com

Password

imapacct16



5. Install GMAIL SSL Certificates into the Concurrent Manager Tier:

  a) Copy the following GMAIL SSL Certificate into a file called gmail.cer



-----BEGIN CERTIFICATE-----

MIIDWzCCAsSgAwIBAgIKaNPuGwADAAAisjANBgkqhkiG9w0BAQUFADBGMQswCQYD

VQQGEwJVUzETMBEGA1UEChMKR29vZ2xlIEluYzEiMCAGA1UEAxMZR29vZ2xlIElu

dGVybmV0IEF1dGhvcml0eTAeFw0xMTAyMTYwNDQzMDRaFw0xMjAyMTYwNDUzMDRa

MGgxCzAJBgNVBAYTAlVTMRMwEQYDVQQIEwpDYWxpZm9ybmlhMRYwFAYDVQQHEw1N

b3VudGFpbiBWaWV3MRMwEQYDVQQKEwpHb29nbGUgSW5jMRcwFQYDVQQDEw5pbWFw

LmdtYWlsLmNvbTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAqfPyPSEHpfzv

Xx+9zGUxoxcOXFrGKCbZ8bfUd8JonC7rfId32t0gyAoLCgM6eU4lN05VenNZUoCh

L/nrX+ApdMQv9UFV58aYSBMU/pMmK5GXansbXlpHao09Mc8eur2xV+4cnEtxUvzp

co/OaG15HDXcr46c6hN6P4EEFRcb0ccCAwEAAaOCASwwggEoMB0GA1UdDgQWBBQj

27IIOfeIMyk1hDRzfALz4WpRtzAfBgNVHSMEGDAWgBS/wDDr9UMRPme6npH7/Gra

42sSJDBbBgNVHR8EVDBSMFCgTqBMhkpodHRwOi8vd3d3LmdzdGF0aWMuY29tL0dv

b2dsZUludGVybmV0QXV0aG9yaXR5L0dvb2dsZUludGVybmV0QXV0aG9yaXR5LmNy

bDBmBggrBgEFBQcBAQRaMFgwVgYIKwYBBQUHMAKGSmh0dHA6Ly93d3cuZ3N0YXRp

Yy5jb20vR29vZ2xlSW50ZXJuZXRBdXRob3JpdHkvR29vZ2xlSW50ZXJuZXRBdXRo

b3JpdHkuY3J0MCEGCSsGAQQBgjcUAgQUHhIAVwBlAGIAUwBlAHIAdgBlAHIwDQYJ

KoZIhvcNAQEFBQADgYEAxHVhW4aII3BPrKQGUdhOLMmdUyyr3TVmhJM9tPKhcKQ/

IcBYUev6gLsB7FH/n2bIJkkIilwZWIsj9jVJaQyJWP84Hjs3kus4fTpAOHKkLqrb

IZDYjwVueLmbOqr1U1bNe4E/LTyEf37+Y5hcveWBQduIZnHn1sDE2gA7LnUxvAU=

-----END CERTIFICATE-----



   b) Save the file gmail.cer into $ AF_JRE_TOP/lib/security/ directory

   c) Change permissions on gmail.cer to 755.

       $  chmod 755 gmail.cer

   d) Install the SSL Certificate into:

        $  keytool -import -trustcacerts -keystore $AF_JRE_TOP/lib/security/cacerts  -storepass changeit - alias gmail-lnx_chainnedcert -file gmail.cer

  One will see:

 Keytool_command_gmail

Answer: Yes

Trust this certificate? [no]:  Yes

Certificate was added to keystore



6. Run the Mailer command from the Concurrent Manager Tier to test the IMAP Connection:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Ddbcfile=$FND_SECURE/$TWO_TASK.dbc -Dserver=imap.gmail.com -Dport=993 -Dssl=Y -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount=<gmail username> -Dpassword=<password> -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailImapTest.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer

ie:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Ddbcfile=$FND_SECURE/VIS.dbc -Dport=993 -Dssl=Y -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Dserver=imap.gmail.com -Daccount=emailwork.charlery -Dpassword=imapacct16 -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailImapTest.log  -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer



7. Run the Mailer command from the Concurrent Manager Tier to Test the SMTP Connection:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Ddbcfile=$FND_SECURE/$TWO_TASK.dbc -Dserver=smtp.gmail.com -Dport=465 -Dssl=Y -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount=<gmail username> -Dpassword=<password> -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailSmtpTest.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer

ie:

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Ddbcfile=$FND_SECURE/VIS.dbc -Dserver=smtp.gmail.com -Dport=465 -Dssl=Y -Dtruststore=$AF_JRE_TOP/lib/security/gmail.cer -Daccount=emailwork.charlery -Dpassword=imapacct16 -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailSmtpTest.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer

Query to Find responsibility associated for a Concurrent request

Query to Find responsibility associated for a Concurrent request
=============================================

 SELECT frt.responsibility_name, frg.request_group_name,
    frgu.request_unit_type,frgu.request_unit_id,
    fcpt.user_concurrent_program_name
    FROM fnd_Responsibility fr, fnd_responsibility_tl frt,
    fnd_request_groups frg, fnd_request_group_units frgu,
    fnd_concurrent_programs_tl fcpt
    WHERE frt.responsibility_id = fr.responsibility_id
    AND frg.request_group_id = fr.request_group_id
    AND frgu.request_group_id = frg.request_group_id
    AND fcpt.concurrent_program_id = frgu.request_unit_id
    AND frt.LANGUAGE = USERENV('LANG')
    AND fcpt.LANGUAGE = USERENV('LANG')
    AND fcpt.user_concurrent_program_name = :conc_prg_name
    ORDER BY 1,2,3,4 

Email delivery the concurrent request output in Oracle Apps

Settings to send/receive  output file of concurrent request (SRS) through Email .
=======================================================


E-Business Suite 12.2 has a region called "Delivery Manager e-mail server credentials" in Preference page.

On the page you can set SMTP authentication.
-----------------------------------------------------
1. Log in the application with the user who will submit the concurrent request with Delivery Options.

2. Click on 'Preferences' under 'Settings' on the right top of home page.

3. Set your SMTP username and password to the fields "e-Mail username" and "e-Mail password"           under "Delivery Manager e-mail server credentials "section , apply the change.

4. Re-login the application and test the issue again.


Note : if this setting is set at application level and user doe not want the notifications sent to him then through same above navigation he can disable the Delivery which will only disable at that user level. 

Queries to find specific concurrent request log files and Output Files Location

select logfile_name from fnd_concurrent_requests where request_id = '5054783'
select outfile_name from fnd_concurrent_requests where request_id ='5054783'

select logfile_name from fnd_concurrent_processes where concurrent_process_id=(select controlling_manager
from fnd_concurrent_requests where request_id='5054783')


SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id =5054783select logfile_name from fnd_concurrent_requests where request_id = '5054783'
select outfile_name from fnd_concurrent_requests where request_id ='5054783'

select logfile_name from fnd_concurrent_processes where concurrent_process_id=(select controlling_manager
from fnd_concurrent_requests where request_id='5054783')


SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
AND fcpp.action_type = 6
AND fcpp.concurrent_request_id =5054783

How to add firewall exceptions in unix for EBS related port number

How to add firewall exceptions in unix for EBS application related ports   or any other Ports 
=================================================================

Login as  root user :

Issue below commands

        iptables -I INPUT -p tcp --dport 8020 --syn -j ACCEPT

        service iptables save

        iptables -I INPUT -p tcp --dport 1541 --syn -j ACCEPT

        service iptables save

iptables -I INPUT -p tcp --dport 7021 --syn -j ACCEPT

service iptables save

Note : In the above example 8020 is my ebs port and 1541 is my database port and 7021 for                           weblogic port

iptables-save > /etc/iptables.conf

==============================================================
Add the following command in /etc/rc.local to reload the rules in every reboot.

        iptables-restore < /etc/iptables.conf

File will look like below after edition : 

 cat /etc/rc.local
#!/bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don't
# want to do the full Sys V style init stuff.

touch /var/lock/subsys/local

iptables-restore < /etc/iptables.conf

If the above line is not added you would have to issue these commands every time the server is rebooted.

==============================================================

Checking Application Url is working from Unix  :

The Below Command from Unix will let you know whether the url is in accessible state or not: 

curl "http://erpprod.skydubai.com:8020/OA_HTML/AppsLogin" |head -n 1 | grep "HTTP/1.[01] [23].."

Output of command would be like below : 

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current

                                 Dload  Upload   Total   Spent    Left  Speed

105   315    0   315    0     0   8569      0 --:--:-- --:--:-- --:--:--  8750

This curl command can be useful for troubleshooting and Daily health check or monitoring scripts to make sure you receive alerts when the application is down 

How to configure Workflow Mailer from backend

I have taken the example of setting override address here however all other parameters of the workflow mailer can be configured by choosing the respective "Comp Param Id"  while running below  script "afsvcpup.sql"

HOW TO SET THE OVERRIDE ADDRESS in WORKFLOW MAILER
------------------------------------------------------------------------------------------------
--Set the override address to  'xyz@abc.com' , using the following script:

--Login to the application server..
cd $FND_TOP/sql

execute below script : (you will be prompted to choose between list of components you would like to configure )

sqlplus apps/apps  @afsvcpup.sql

From the list of components available
Enter Component Id: 10006
Enter the Comp Param Id to update : 10093

Enter a value for the parameter  : xyz@abc.com


--Check the workflow services using the below query.

select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS
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;

--
To Check What is the Current Email Address Set

SQL> select fscpv.parameter_value
    from fnd_svc_comp_params_tl fscpt
    ,fnd_svc_comp_param_vals fscpv
    where fscpt.display_name = 'Test Address'
    and fscpt.parameter_id = fscpv.parameter_id;  2    3    4    5

PARAMETER_VALUE
--------------------------------------------------------------------------------
xyz.abc@domain.com


Also one alternate is there to set it from back-end using the below query
------------------------------------------------------------------------------------------
update fnd_svc_comp_param_vals
set    parameter_value = '&EnterEmailID'
where  parameter_id =
( select parameter_id
 from   fnd_svc_comp_params_tl
 where  display_name = 'Test Address');

How to change or modify EBS URL to more user friendly name

In some cases an EBS Instance url  has to be changed to a more user friendly name because the existing url is either diffcult to remember or to very similar to another instance or not suitable.

Goal :

Changing url from http://erp.oracle.com:8000 to http://erpuat.oracle.com:8000

Steps :

1.Please ask Network Team to add alias in DNS with new hostname (erpuat.oracle.com) for the IP 10.10.10.11.

     (adding this new alias to DNS would make it be able to resolve to the new url)



      From Command prompt try to ping erp.oracle.com and ping erpuat.oracle.com

      Please check both the hostname should ping with same IP 10.10.10.11 as they are just alias of the         other name


  2.Add new hostname entry in /etc/hosts of Application server

       10.10.10.11         erp.oracle.com        erp      erpuat.oracle.com  erpuat


   3.Now try to ping erpuat.oracle.com from Application server. It should ping self.


   4.Change  the following parameter values with new values in $CONTEXT_FILE


      <login_pageoa_var="s_login_page">http://erpuat.oracle.com:8000/OA_HTML/AppsLogin

       </login_page>

       <externURL oa_var="s_external_url">http://erpuat.oracle.com:8000</externURL>

       <webentryhost oa_var="s_webentryhost">erpuat</webentryhost>

        <webentrydomain oa_var="s_webentrydomain">oracle.com</webentrydomain>


   5.Stop the application services and run Autoconfig.

   6.Please start the application and check the new url http://erpuat.oracle.com:8000

How to deregister and register SSO with Oracle EBS application

Deregister Oracle E-Business Suite from Oracle Access Manager

To deregister your Oracle E-Business Suite instance from Oracle Access Manager:

Stop all services on the Apps Tier 

txkrun.pl -script=SetOAMReg -deregisteroam=yes -ebsProfileLevel=Site

*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS

*** Log File = /u01/ERPCLONE/fs1/inst/apps/ERPCLONE_server02/logs/appl/rgf/TXK/txkSetOAMReg.log

Your previous session did not run to completion.

Do you wish to continue with your previous session(y|n)?

y

Proceeding with previous session...

Enter OAM Console URL (for ex: http://myoam.us.oracle.com:7001): http://server02:7002

Enter OAM console user name (for ex: weblogic): weblogic

Enter OAM console password: xyz

Enter APPS password: abc

oamHost                 = http://server02:7002

oamApplicationDomain    = ERPCLONE.co.uk_443

oamHostIdentifier       = ERPCLONE.co.uk_443

contextFile             = /u01/ERPCLONE/fs1/inst/apps/ERPCLONE_server02/appl/admin/ERPCLONE_server02.xml

webGateInternal         = Yes

ebsProfileLevel         = Site

webGateUrl              = https://erpclone.co.uk:443

contextRoot             = accessgate

######################################################################

Do you wish to continue (y|n)?

y

#### It will also run AUTOCONFIG #####

######################################################################

$ADMIN_SCRIPTS_HOME/adadminsrvctl.sh start

http://server02:7027/console/

Click Lock & Edit

Go to Services à Data Sources à OAEA_DATASOURCE

Select and Press Delete

Go to Deployments à Select “Accessgate” àClick Stop

Click “Deleted” and Delete the deployments

Go to Environments à Servers à Select oaea_server1

Stop and Delete the managed server

Remove the Oracle Internet Directory Configuration from the Cloned Oracle E-Business Suite Instance

$FND_TOP/bin/txkrun.pl -script=SetSSOReg -removereferences=Yes

You are removing all SSO-LDAP related preferences.

Enter Oracle E-Business apps database user password? abc

*** Log File = /u01/ERPCLONE/fs1/inst/apps/ERPCLONE_server02/logs/appl/rgf/TXK/txkSetSSORe.xml

Beginning input parameter validation for Removing Preferences.

Input parameter validation for Removing Preferences completed.

BEGIN REMOVING PREFERENCES:

Oracle Home Instance preferences removed successfully

Updated APPS_SSO profile with value SSWA successfully.

SSO Preferences Removed successfully.

Removing LDAP Directory References Started.

Removing LDAP Directory References Completed Successfully.

Custom DIT Preferences Removed Successfully.

REMOVING PREFERENCES COMPLETED.

End of /u01/ERPCLONE/fs1/EBSapps/appl/fnd/12.0.0/patch/115/bin/txkSetSSOReg.pl : No Errors encountered