How To Change The E-mail Notification Preference En Masse For Multiple Users (Doc ID 417030.1)

In this Document

Goal

Solution

Workflow Information Center, Diagnostics, & Community

References

---

APPLIES TO:

---

Oracle Workflow - Version 11.5.9 to 12.2 [Release 11.5 to 12.2]

Information in this document applies to any platform.

GOAL

---

When there is need to change the notification preference for more than one user it is cumbersome to do it through the user interface (application forms); it is impractical when the number of users to change is very high.

In this scenario the use of APIs is the best way to perform this changes.

SOLUTION

---

There are several user properties that can be changed using these APIs. This note provides the script to change a user mail notification preference using these APIs but it can be adjusted to change other parameters as well.

1. The simplest way to change the e-mail notification preference is to call FND_PREFERENCE package, as follows:

exec FND_PREFERENCE.put('<username>', 'WF', 'MAILTYPE', 'QUERY');
COMMIT;

In this example, just change <username> to the target user's name.

2. The other way to change the notification preference is a call to WF_LOCAL_SYNCH. Here is an example:

declare
myList wf_parameter_list_t;
begin
wf_event.AddParameterToList('USER_NAME','<username>', myList);
wf_event.AddParameterToList('ORCLWORKFLOWNOTIFICATIONPREF', 'MAILHTML', mylist);
wf_local_synch.propagate_user(p_orig_system=>'FND_USR',p_orig_system_id=>1007880, p_attributes=>mylist);
end;
/

In the example above, just need to change the user name from <username> to the target user and <user_id> to the user_id in table FND_USER for the target user.

NOTE: These two APIs provide the synchronization of the user data into the Workflow Directory Services tables. Direct updates on the workflow or FND% tables will not propagate the change properly, hence the need of using the right APIs.

These APIs can be used in a cursor containing all the users to be changed.

Lastly, other preference that can be changed instead or along with ORCLWORKFLOWNOTIFICATIONPREF in the API are:

USER_NAME

DISPLAYNAME

ORCLWFORIGSYSTEM

ORCLWFORIGSYSTEMID

PREFERREDLANGUAGE

ORCLNLSTERRITORY

DESCRIPTION

ORCLWORKFLOWNOTIFICATIONPREF

MAIL

FACSIMILETELEPHONENUMBER

ORCLISENABLED

RAISEERRORS

WFSYNCH_OVERWRITE

EXPIRATIONDATE

NOTE: Running the Following SQL only returns the QUERY Preference_Value:

select Preference_name, preference_value

from fnd_user_preferences

where PREFERENCE_NAME='MAILTYPE'

Ideally, have the following MAILTYPES must be present in the table:

MAILTYPE DISABLED

MAILTYPE MAILATTH

MAILTYPE MAILHTML

MAILTYPE MAILHTML2

MAILTYPE QUERY

MAILTYPE (null)

---

Workflow Information Center, Diagnostics, & Community

• Please reference the Workflow Product Information Center Document for Top Workflow Resources: Document 1320509.1

• For additional help, please refer to one of the following documents on diagnostics to address current needs. Providing diagnostic output on an issue for support when logging a service request is very helpful.
  
  Document 179661.1 for 11i or Document 421245.1 for Rel 12.x

• Visit the Core Workflow community for help with industry experts or to share knowledge.

REFERENCES

---

NOTE:179661.1 - E-Business Suite Diagnostics 11i Test Catalog

NOTE:1320509.1 - Information Center: E-Business Suite Oracle Workflow (WF)