How to Update Oracle EBS Workflow Mailer Parameters from the Backend

Oracle E-Business Suite provides the afsvcpup.sql script to update Workflow Notification Mailer and Workflow Agent Listener parameters directly from SQL*Plus. This is useful when the Oracle EBS application login page or Oracle Applications Manager is unavailable.

Run this procedure during an approved maintenance window and record the existing parameter value before making any change.

1. Identify the Workflow Mailer Component ID

Connect to the Oracle EBS database as the APPS user:

sqlplus apps

Enter the APPS password when prompted.

Run the following query:

SET LINESIZE 200
SET PAGESIZE 100

COLUMN component_name FORMAT A50

SELECT component_id,
       component_name
FROM   fnd_svc_components
WHERE  component_type = 'WF_MAILER'
ORDER BY component_id;

Example output:

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

If multiple Workflow Notification Mailers exist, select the correct component carefully.

2. Review the Existing Parameter Values

Before changing anything, capture the current configuration:

SET LINESIZE 250
SET PAGESIZE 1000

COLUMN component_name         FORMAT A35
COLUMN component_status       FORMAT A18
COLUMN parameter_name         FORMAT A35
COLUMN parameter_display_name FORMAT A45
COLUMN parameter_value        FORMAT A70
COLUMN startup_mode           FORMAT A15

SELECT sc.component_id,
       sc.component_name,
       sc.component_status,
       sc.correlation_id AS corrid,
       v.parameter_id,
       p.parameter_name,
       v.parameter_display_name,
       v.parameter_value,
       sc.startup_mode
FROM   fnd_svc_comp_param_vals_v v,
       fnd_svc_components sc,
       fnd_svc_comp_params_b p
WHERE  v.component_id = sc.component_id
AND    sc.component_type = 'WF_MAILER'
AND    v.parameter_id = p.parameter_id
ORDER BY sc.component_id,
         v.parameter_display_name;

Record the following information for the parameter being changed:

• Component ID

• Parameter ID

• Parameter name

• Existing parameter value

• New parameter value

3. Run the Parameter Update Script

Ensure that the Oracle EBS application environment is sourced and confirm that $FND_TOP is set:

echo $FND_TOP

Start SQL*Plus as the APPS user:

sqlplus apps

Run the seeded Oracle EBS script:

@$FND_TOP/sql/afsvcpup.sql

The script will display the parameters and prompt for the required information.

Prompt 1: Component ID

Enter Component Id:

Enter the Workflow Notification Mailer component ID identified earlier.

Example:

10006

Prompt 2: Component Parameter ID

Enter the Comp Param Id to update:

Enter the parameter ID corresponding to the parameter you want to modify.

Prompt 3: Parameter Value

Enter a value for the parameter:

Enter the new value for the selected parameter.

Review the script output carefully and confirm that it completes without errors.

4. Verify the Updated Value

After running the script, execute the following query:

SET LINESIZE 250
SET PAGESIZE 1000

COLUMN component_name         FORMAT A35
COLUMN component_status       FORMAT A18
COLUMN parameter_name         FORMAT A35
COLUMN parameter_display_name FORMAT A45
COLUMN parameter_value        FORMAT A70
COLUMN startup_mode           FORMAT A15

SELECT sc.component_id,
       sc.component_name,
       sc.component_status,
       sc.correlation_id AS corrid,
       v.parameter_id,
       p.parameter_name,
       v.parameter_display_name,
       v.parameter_value,
       sc.startup_mode
FROM   fnd_svc_comp_param_vals_v v,
       fnd_svc_components sc,
       fnd_svc_comp_params_b p
WHERE  v.component_id = sc.component_id
AND    sc.component_type = 'WF_MAILER'
AND    v.parameter_id = p.parameter_id
ORDER BY sc.component_id,
         v.parameter_display_name;

To verify only the updated parameter, add the relevant component and parameter IDs:

AND sc.component_id = 10006
AND v.parameter_id = <parameter_id>

Place these conditions before the ORDER BY clause.

5. Restart the Workflow Mailer if Required

Depending on the parameter changed, the Workflow Notification Mailer may need to be restarted for the new value to take effect.

Use Oracle Applications Manager to stop and start the Workflow Notification Mailer. If the application interface is unavailable, use the approved administrative procedure for your environment.

After restarting, verify:

• Workflow Mailer component status

• Inbound and outbound mail processing

• SMTP and IMAP connectivity

• Workflow Mailer log files

• Pending or failed notifications

• Test notification delivery

Important Precautions

• Test the change in a non-production environment first.

• Do not pass the APPS password directly on the command line because it may appear in shell history or the operating-system process list.

• Confirm the correct component ID when multiple mailers exist.

• Capture the original value before updating the parameter.

• Do not update the underlying Workflow tables directly.

• Ensure that a rollback value and validation plan are available.

• Avoid changing passwords or sensitive values while terminal logging or screen recording is enabled.

Conclusion

The seeded $FND_TOP/sql/afsvcpup.sql script provides a controlled method for changing Workflow Notification Mailer and Workflow Agent Listener parameters without logging in to Oracle E-Business Suite. Always capture the existing configuration, select the correct component and parameter IDs, verify the updated value, and restart the affected service when required.