Troubleshooting Oracle EBS Workflow Mailer: HTTPClientException for Framework Regions

If you're an Oracle Apps DBA, you've likely run into issues with the Workflow Mailer. A particularly tricky one is when email notifications with embedded OA Framework regions fail to send. This post will walk you through identifying the problem, understanding the cause, and applying a comprehensive solution.

---

Symptom

You'll notice that certain workflow email notifications just aren't going out. When you check the Workflow Mailer log file (e.g., FNDCPGSC*.txt), you'll find a distinct error message that points to a problem fetching HTML content.

Problem getting the HTML content -> oracle.apps.fnd.wf.mailer.NotificationFormatter$FormatterSAXException: Problem obtaining the HTML content -> oracle.apps.fnd.wf.common.HTTPClientException: Unable to invoke method HTTPClient.HTTPConnection.Get

---

Cause

This error is almost always caused by an incorrect configuration of the profile option "WF: Workflow Mailer Framework Web Agent" (internal name WF_MAIL_WEB_AGENT).

When a notification includes an embedded OA Framework region, the mailer needs to make an HTTP call to the web server to render that region into HTML for the email body. If this profile option is blank, or points to a load balancer address instead of a direct, physical web server URL, the mailer can't make the connection, and the notification fails.

---

Solution

Here’s the step-by-step guide to fix it. You can safely perform these steps in a production environment, but we always recommend testing in a non-production instance first.

1. Stop the Workflow Mailer and Agent Listener services.
   1. Log in to Oracle Application Manager (OAM).
   2. Navigate to Workflow Manager -> Service Components.
   3. Select and stop the "Workflow Notification Mailer" and "Workflow Agent Listener Service".
   4. Wait for the services to show a status of "Deactivated" with 0 actual and 0 target processes.
   5. Confirm they are stopped with the following SQL query:

      SELECT component_name, component_status, component_status_info
      FROM fnd_svc_components_v
      WHERE component_name like 'Workflow%';

2. Find the tablespace for the workflow queue indexes.

   You'll need this tablespace name in a later step. Run this query to find it:

   SELECT DISTINCT tablespace_name
   FROM dba_indexes, dba_queues
   WHERE index_name LIKE 'WF%N1'
   AND table_name = queue_table
   AND name LIKE 'WF%';

3. Set the "WF: Workflow Mailer Framework Web Agent" profile option.

   This is the core of the fix. You need to set this profile option at the Site level to a URL pointing to one of your physical application web servers.

   To construct the correct URL, find these values in the context file ($CONTEXT_FILE) on one of your web nodes:

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

   Your URL will be in the format http://<s_webhost>.<s_domainname>:<s_webport>. For example: http://myhost.mydomain.com:8000.

   Note:

   • The protocol must be http, not https, as the connection is internal.
   • The port must be the web port (s_webport), not an SSL port.

   Validate the URL from any application tier node using wget. You should get a "200 OK" response.

   $ wget http://myhost.mydomain.com:8000
   ...
   HTTP request sent, awaiting response... 200 OK
   ...

4. Rebuild the Workflow Mailer queue.

   Connect to the database as the APPS user and run the following script:

   @$FND_TOP/patch/115/sql/wfntfqup.sql APPS <APPS_SCHEMA_PASSWORD> APPLSYS

5. Recreate the index on the CORRID column.

   Connect as the APPLSYS user and run the following SQL. When prompted, enter the tablespace name you found in step 2.

   CREATE INDEX WF_NOTIFICATION_OUT_N1
   ON WF_NOTIFICATION_OUT(CORRID)
   STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
   TABLESPACE &tbs;

6. Start the Workflow Mailer and Agent Listener services.

   Go back to OAM (Workflow Manager -> Service Components) and start the services you stopped in step 1. Verify they become "Activated" with 1 actual and 1 target process.

7. Wait and retest.

   Allow about 30 minutes for the mailer to process the backlog of notifications in the queue. Then, test the fix with a fresh notification that previously failed.

IMPORTANT: Prevent AutoConfig from Undoing Your Fix

Your hard work can be undone the next time you run AutoConfig. To prevent this, you must ensure the context file variable s_wfmail_agent is set to the same physical web server URL on ALL application nodes. If this variable is blank, AutoConfig will overwrite your profile option setting, and the issue will return.

---

Initial Diagnostic Information

Before you begin, it's helpful to gather some baseline information. Here are the queries and commands often used to diagnose mailer issues.

1. Check the Mailer Queue Status:

SELECT
    corr_id CORRID,
    wfno.user_data.GET_STRING_PROPERTY('BES_EVENT_KEY') NOTIFICATION_ID,
    wfno.user_data.GET_STRING_PROPERTY('ROLE') ROLE,
    wfno.msg_state STATE,
    to_char(enq_time, 'YYYY/MM/DD HH24:MI:SS') enq_time,
    to_char(deq_time, 'YYYY/MM/DD HH24:MI:SS') deq_time,
    to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS') sys_date,
    retry_count RETRY
FROM applsys.aq$wf_notification_out wfno
WHERE wfno.user_data.GET_STRING_PROPERTY('BES_EVENT_NAME') <> 'oracle.apps.wf.notification.summary.send';

2. Get Workflow Version Information:

-- Navigate to $FND_TOP/sql
-- Connect to SQL*Plus as the APPS user

spool wfver.txt
sta wfver.sql
spool off

3. Find Active Mailer Log Files:

SELECT logfile_name
FROM fnd_concurrent_processes
WHERE process_status_code = 'A'
AND concurrent_queue_id IN
(SELECT concurrent_queue_id FROM fnd_concurrent_queues
 WHERE concurrent_queue_name IN ('WFMLRSVC','WFALSNRSVC'));