There is a lot of Oracle e-Business Suite installations that use workflow mailer to communicate messages to users and many of these environments have clones for development and testing purposes. It’s obvious in most cases people that actually use the system will not be the same that develop customizations or do testing, therefore we need to reconfigure the workflow mailer to avoid sending misleading messages to business users from development or test environments.

Lets take a look at what options we have!

  • Don’t start up the workflow mailer in the test environment - yap, that’s a valid solution as the scope of development or testing may not need the functionality of sending the e-mails.
  • Set the “Test address” that will receive all mails sent by the workflow mailer. This option can be set using Oracle Application Manager (OAM) by editing the workflow mailer configuration and entering the email address in the “Test address” field (prior ATG_PF.H RUP3) or using the “Set Override Address” button. (starting from ATG_PF.H RUP3). This is easy to set option, but it’s not always possible to use it as there may be too many mails incomming to the test address or too many people need acces to these mails
  • Modifying user preferences - users can use the preferences of their account to modify the settings of incoming notifications by themselves.
User Preferences

User Preferences

User preferences define what type of emails should be sent and also allows to turn off email notifications. The problem here is that those preferences can be modified only for user account that is logged on currently, so there is no built in functionality to change the settings for all users at once, therefore we need some update statements to do it manually.

Workflow mailer reads the data in WF_LOCAL_ROLES table to find the configuration of email notifications, but values presented in “Preferences” screen are stored in WF_ATTRIBUTE_CACHE table. So if we want to change the settings for all users (to disable the email notifications in our case) we have to use a method that will properly update the values in all the tables where this configuration is stored. I used trace to investigate how the changed value of “email style” is being saved into the database. The data are saved by executing a single procedure:

BEGIN 
  fnd_preference.put (p_user, 'WF', 'MAILTYPE', p_email_style); 
END;

… where “p_user” is the username and p_email_style is a value from the list (including the value description displayed in preferences window):

  • DISABLED - Disabled
  • QUERY - Do not send me mail
  • MAILHTM2 - HTML mail
  • MAILHTML - HTML mail with attachments
  • SUMHTML - HTML summary mail
  • MAILTEXT - Plain text mail
  • MAILATTH - Plain text mail with HTML attachments
  • SUMMARY - Plain text summary mail

For example, to update the email style to value “Do not send me mail” for user “TESTUSER”:

BEGIN
  fnd_preference.put('TESTUSER', 'WF', 'MAILTYPE', 'QUERY');
  COMMIT;
END;

in fact, if fnd_preference.put is executed to update the email style, a business event “oracle.apps.fnd.wf.ds.user.updated” is raised that initiates the updates to WF_LOCAL_ROLES and WF_ATTRIBUTE_CACHE tables.

For a test or development environment we would need to disable all the email notifications, and the following procedure (executed with APPS user) can be used to achieve this aim:

DECLARE
  --get users with email style other then 'Do not send emails'
  cursor c1 is
    select u.user_name
      from fnd_user u, wf_local_roles r
     where r.name = u.user_name
       and notification_preference != 'QUERY';
BEGIN
  for c in c1 loop
    --change user preference to 'Do not send emails'
    fnd_preference.put(c.user_name, 'WF', 'MAILTYPE', 'QUERY');
  end loop;
  --commit changes
  commit;
END;

When it’s done, the users that need the email notifications from the dev/test environment just have to change their preferences manually.