WF Mailer is not Sending EMails with LOAD Ba-lancer Setting in EBS

WF Mailer Setup for Load Balancer Setting.

Here is the Steps:

 What is considered as an Advanced Configuration Setting in the Oracle Application environment?

1. Using Dematerialized Zones (DMZ)
2. Using Secured Socket Layer (SSL)
3. Using BigIP Configured Server
4. Using Virtual Naming Conventions
5. Using a Reversed Proxy Server
6. Using Load Balancers

>>>>>

1. Find out from the Network Administrator (DBA or System Administrator) at the site as to what is the actual hostname of the Web Tier:

The uname –a, uname –n,  or hostname commands can be used to determine the name.  These commands should yield almost the same information, but the uname –a command returns information on the operating system and version of the kernel.

$ uname -a
$ uname –n
$ hostname

2. Cross Reference the Web Tier Name with the hostname setting in the AutoConfig Context file on the Web Tier by running the following commands on the Web Server:

 $ grep -ie s_webhost $CONTEXT_FILE
 $ grep -ie s_domainname $CONTEXT_FILE
 $ grep -ie s_webport $CONTEXT_FILE

3.  If the Advanced Configuration setting are already configured, the following Site Level Profile Options should have a URL setting that leads to the  machine name where the Proxy Server, Virtual Name server, DMZ Machine Name, BigIP Machine, SSL, or Load Balancer was installed.

Application Framework Agent (APPS_FRAMEWORK_AGENT)

Apps Servlet Agent (APPS_SERVLET_AGENT)

Applications Web Agent (APPS_WEB_AGENT)      

ICX: Forms Launcher (ICX_FORMS_LAUNCHER)

4. When using Advanced Configuration settings, the following Site Level Profile Option should reference the  Physical Name of the Web Tier (the same as returned in #2).

WF: Workflow Mailer Framework Web Agent (WF_MAIL_WEB_AGENT)

NOTE: The Context File grep commands above is the main indicator of what the Web Tier Physical Machine name, Domain, and Port# are.

5. The following SQL script can be run in sqlplus connected as apps/<appspwd> to determine the profile Option settings in the Database opposed to checking them in the Application:

set linesize 155;
set pagesize 200;
set verify off;
col Profile format a20;
 col Level format a14;
col Value format a10;
col App format a3;
col Responsibility format a30;
col USER format a8;
col UPDATED_BY format a8;
select distinct fpo.profile_option_name Profile,
      fpov.profile_option_value Value,
      decode(fpov.level_id, 10001,'Site',
                       10002,'Application',
                       10003,'Responsibility',
                       10004,'User',
                       10005,'Server',
                       10006,'Organization')"LEVEL",
      fa.application_short_name App,
      fr.responsibility_name Responsibility,
      fu.user_name "USER"
from fnd_profile_option_values fpov,
fnd_profile_options fpo,
fnd_application fa,
fnd_responsibility_vl fr,
fnd_user fu,
fnd_logins fl
where fpo.profile_option_id=fpov.profile_option_id
and fa.application_id(+)=fpov.level_value
and fr.application_id(+)=fpov.level_value_application_id
and fr.responsibility_id(+)=fpov.level_value
and fu.user_id(+)=fpov.level_value
and fl.login_id(+) = fpov.LAST_UPDATE_LOGIN
and fpov.profile_option_value is not Null
and fpo.profile_option_name in
('APPS_FRAMEWORK_AGENT', 'WF_MAIL_WEB_AGENT', 'APPS_WEB_AGENT',
'APPS_SERVLET_AGENT', 'ICX_FORMS_LAUNCHER') order by 1,3;

NOTE: The sql checks the mutilple levels of Profile Option Settings (Site, Application, Responsibility, User, Server).  The lower the level setting, the higher precedence given to the setting over the Site Level setting.  For example: If a Profile Option is set a USER level, that value will override the SITE Level value.  Additionally: The RESPONSIBILITY level takes precedence over the APPLICATION level, which in turn takes precedence over the SITE level value.

6. When using the Advanced Configuration Settings,  the following Workflow Mailer Configuration Parameter should be set identical to the APPS_SERVLET_AGENT Profile Option URL setting (which essentially should be set to reference the Proxy Server URL, Virtual name Server, DMZ Machine Name URL, SSL URL, BigIP Machine URL, or Load Balancer Machine URL.

ie:
PARAMETER_VALUE
------------------------------
HTML Agent

NOTE: The setting of the HTMLAGENT can differ in format depending on the version of the Oracle Applications being used. ‘
See: What Is The Correct Value of Mailer Parameter "HTML Agent" In R12? (Document 578578.1)

7. The following sql query can check the value of the HTMLAGENT from the Database:

SELECT
       c.parameter_name,
       c.display_name,
       a.parameter_value
FROM   fnd_svc_comp_param_vals a,
       fnd_svc_components b,
       fnd_svc_comp_params_vl 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_type = 'WF_MAILER'
AND    c.parameter_name like'%HTMLAGENT%'


8. Once the components of the Profile Option are correctly set, the following steps should be taking 

a. Stop Workflow Mailer and Agent Listener service

b. Change the "WF: Workflow Mailer Web Agent" [WF_MAIL_WEB_AGENT] profile option to point to the
   physical Web Server host address and port

c. Rebuild mailer queue with:

    i.  Find the tablespace where indexes on corrid are created :
        SQL> select tablespace_name from dba_indexes
             where index_name='WF_NOTIFICATION_OUT_N1'
             -----
             ex: APPS_TS_QUEUES


    ii. Rebuild Mailer queue :

        SQL> sqlplus apps/<apps_pwd> @$FND_TOP/patch/115/sql/wfntfqup APPS <apps_pwd> APPLSYS

    iii. Recreate Index on corrid :

        SQL> sqlplus applsys/<pwd>

            CREATE INDEX WF_NOTIFICATION_OUT_N1 ON WF_NOTIFICATION_OUT(CORRID)
            STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
            TABLESPACE &tbs;
            When prompted for the tablespace, enter the tablespace name found at step 2
            above.
            or
        SQL> CREATE INDEX WF_NOTIFICATION_OUT_N1 ON WF_NOTIFICATION_OUT(CORRID)
             STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
             TABLESPACE APPS_TS_QUEUES;


         
d. Start Workflow Mailer and Agent Listener service.

e. Test scenario with a new notification