This chapter describes the APIs for the Oracle Workflow directory service. The APIs include PL/SQL functions and procedures that you can use to access the directory service.
This chapter covers the following topics:
The following APIs can be called by an application program or a workflow function in the runtime phase to retrieve information about existing users and roles, as well as create and manage new ad hoc users and roles in the directory service. These APIs are defined in a PL/SQL package called WF_DIRECTORY.
Important: If you implement Oracle Internet Directory integration, you must maintain your users only through Oracle Internet Directory. You must not create ad hoc users in the WF_LOCAL_ROLES table, because you risk discrepancies in your user information and unpredictable results if you use any tool other than Oracle Internet Directory to maintain users after integrating with Oracle Internet Directory. Consequently, if you implement Oracle Internet Directory integration, you must not use the CreateAdHocUser(), SetAdHocUserStatus(), SetAdHocUserExpiration(), or SetAdHocUserAttr() APIs in the WF_DIRECTORY package.
You can still use ad hoc roles, however, since Workflow roles are not maintained through Oracle Internet Directory.
Some directory service APIs use PL/SQL table composite datatypes defined in the WF_DIRECTORY package. The following table shows the column datatype definition for each PL/SQL table type.
PL/SQL Table Type | Column Datatype Definition |
---|---|
UserTable | varchar2(320) |
RoleTable | varchar2(320) |
Related Topics
Standard API for PL/SQL Procedures Called by Function Activities, Oracle Workflow Developer's Guide
procedure GetRoleUsers (role in varchar2, users out wF_DIRECTORY.UserTable);
Returns a table of users for a given role.
Note: A role can contain only individual users as its members. It cannot contain another role.
Variable | Description |
---|---|
role | A valid role name. |
procedure GetUserRoles (user in varchar2, roles out WF_DIRECTORY.RoleTable);
Returns a table of roles that a given user is assigned to.
Variable | Description |
---|---|
user | A valid username. |
procedure GetRoleInfo (Role in varchar2, Display_Name out varchar2, Email_Address out varchar2, Notification_Preference out varchar2, Language out varchar2, Territory out varchar2);
Returns the following information about a role:
Display name
E-mail address
Notification Preference ('QUERY', 'MAILTEXT', 'MAILHTML', 'MAILATTH', 'MAILHTM2', 'DISABLED', 'SUMMARY', or, for Oracle Applications only, 'SUMHTML')
Language
Territory
Note: In Oracle Applications, for roles that are Oracle Applications users marked with an originating system of FND_USR or PER, the GetRoleInfo() procedure by default retrieves the language and territory values from the ICX: Language and ICX: Territory profile options for that Oracle Applications user.
However, if the WF_PREFERENCE resource token is defined and set to FND, then the GetRoleInfo() procedure obtains the language and territory values from the Oracle Workflow preferences table instead.
The WF_PREFERENCE resource token is not used in the standalone version of Oracle Workflow.
Variable | Description |
---|---|
role | A valid role name. |
procedure GetRoleInfo2 (Role in varchar2, Role_Info_Tbl out wf_directory.wf_local_roles_tbl_type);
Returns the following information about a role in a PL/SQL table:
Name
Display name
Description
Notification preference ('QUERY', 'MAILTEXT', 'MAILHTML', 'MAILATTH', 'MAILHTM2', 'DISABLED', 'SUMMARY', or, for Oracle Applications only, 'SUMHTML')
Language
Territory
E-mail address
Fax
Status
Expiration date
Originating system
Originating system ID
Parent originating system
Parent originating system ID
Owner tag
Standard Who columns
Note: In Oracle Applications, for roles that are Oracle Applications users marked with an originating system of FND_USR or PER, the GetRoleInfo2() procedure by default retrieves the language and territory values from the ICX: Language and ICX: Territory profile options for that Oracle Applications user.
However, if the WF_PREFERENCE resource token is defined and set to FND, then the GetRoleInfo2() procedure obtains the language and territory values from the Oracle Workflow preferences table instead.
The WF_PREFERENCE resource token is not used in the standalone version of Oracle Workflow.
Variable | Description |
---|---|
role | A valid role name. |
function IsPerformer (user in varchar2, role in varchar2) return boolean;
Returns TRUE or FALSE to identify whether a user is a performer, also known as a member, of a role.
Variable | Description |
---|---|
user | A valid username. |
role | A valid role name. |
function UserActive (username in varchar2) return boolean;
Determines if a user currently has a status of 'ACTIVE' and is available to participate in a workflow. Returns TRUE if the user has a status of 'ACTIVE', otherwise it returns FALSE.
Variable | Description |
---|---|
username | A valid username. |
procedure GetUserName (p_orig_system in varchar2, p_orig_system_id in varchar2, p_name out varchar2, p_display_name out varchar2);
Returns a Workflow display name and username for a user given the system information from the original user and roles repository.
Variable | Description |
---|---|
p_orig_system | Code that identifies the original repository table. |
p_orig_system_id | ID of a row in the original repository table. |
procedure GetRoleName (p_orig_system in varchar2, p_orig_system_id in varchar2, p_name out varchar2, p_display_name out varchar2);
Returns a Workflow display name and role name for a role given the system information from the original user and roles repository.
Variable | Description |
---|---|
p_orig_system | Code that identifies the original repository table. |
p_orig_system_id | ID of a row in the original repository table. |
function GetRoleDisplayName (p_role_name in varchar2) return varchar2; pragma restrict_references(GetRoleDisplayName, WNDS, WNPS);
Returns a Workflow role's display name given the role's internal name.
Variable | Description |
---|---|
p_role_name | The internal name of the role. |
procedure CreateAdHocUser (name in out varchar2, display_name in out varchar2, language in varchar2 default null, territory in varchar2 default null, description in varchar2 default null, notification_preference in varchar2 default 'MAILHTML', email_address in varchar2 default null, fax in varchar2 default null, status in varchar2 default 'ACTIVE', expiration_date in date default null, parent_orig_system in varchar2 default null, parent_orig_system_id in number default null);
Creates a user at runtime by creating a value in the WF_LOCAL_ROLES table with the user flag set to Y. This is referred to as an ad hoc user.
Important: If you implement Oracle Internet Directory integration for standalone Oracle Workflow, you must maintain your users only through Oracle Internet Directory. You must not use the CreateAdHocUser() API to create new users in the WF_LOCAL_ROLES table, because you risk discrepancies in your user information and unpredictable results if you use any tool other than Oracle Internet Directory to maintain users after integrating with Oracle Internet Directory.
Variable | Description |
---|---|
name | An internal name for the user. The internal name must be no longer than 320 characters. It is recommended that the internal name be all uppercase. This procedure checks that the name provided does not already exist in WF_USERS and returns an error if the name already exists. If you do not provide an internal name, the system generates an internal name for you where the name contains a prefix of '~WF_ADHOC-' followed by a sequence number. |
display_name | The display name of the user. This procedure checks that the display name provided does not already exist in WF_USERS and returns an error if the display name already exists. If you do not provide a display name, the system generates one for you where the display name contains a prefix of '~WF_ADHOC-' followed by a sequence number. |
language | The value of the database NLS_LANGUAGE initialization parameter that specifies the default language-dependent behavior of the user's notification session. If null, the procedure resolves this to the language setting of your current session. |
territory | The value of the database NLS_TERRITORY initialization parameter that specifies the default territory-dependent date and numeric formatting used in the user's notification session. If null, the procedure resolves this to the territory setting of your current session. |
description | An optional description for the user. |
notification_preference | Indicate how this user prefers to receive notifications: 'MAILTEXT', 'MAILHTML', 'MAILATTH', 'MAILHTM2', 'QUERY', 'SUMMARY', or, for Oracle Applications only, 'SUMHTML'. If null, the procedure sets the notification preference to 'MAILHTML'. |
email_address | A optional electronic mail address for this user. |
fax | An optional fax number for the user. |
status | The availability of the user to participate in a workflow process. The possible statuses are 'ACTIVE', 'EXTLEAVE', 'INACTIVE', and 'TMPLEAVE'. If null, the procedure sets the status to 'ACTIVE'. |
expiration_date | The date at which the user is no longer valid in the directory service. |
parent_orig_system | An optional code for the originating system of an entity that you want to mark as being related to this user. |
parent_orig_system_id | The primary key that identifies the parent entity in the parent originating system. |
Related Topics
Setting Up an Oracle Workflow Directory Service, Oracle Workflow Administrator's Guide
procedure CreateAdHocRole (role_name in out varchar2, role_display_name in out varchar2, language in varchar2 default null, territory in varchar2 default null, role_description in varchar2 default null, notification_preference in varchar2 default 'MAILHTML', role_users in varchar2 default null, email_address in varchar2 default null, fax in varchar2 default null, status in varchar2 default 'ACTIVE', expiration_date in date default null, parent_orig_system in varchar2 default null, parent_orig_system_id in number default null, owner_tag in varchar2 default null);
Creates a role at runtime by creating a value in the WF_LOCAL_ROLES table with the user flag set to N. This is referred to as an ad hoc role.
Note: A role can contain only individual users as its members. It cannot contain another role.
Variable | Description |
---|---|
role_name | An internal name for the role. The internal name must be no longer than 320 characters. It is recommended that the internal name be all uppercase. This procedure checks that the name provided does not already exist in WF_ROLES and returns an error if the name already exists. If you do not provide an internal name, the system generates an internal name for you where the name contains a prefix of '~WF_ADHOC-' followed by a sequence number. |
role_display_name | The display name of the role. This procedure checks that the display name provided does not already exist in WF_ROLES and returns an error if the display name already exists. If you do not provide a display name, the system generates one for you where the display name contains a prefix of '~WF_ADHOC-' followed by a sequence number. |
language | The value of the database NLS_LANGUAGE initialization parameter that specifies the default language-dependent behavior of the user's notification session. If null, the procedure resolves this to the language setting of your current session. |
territory | The value of the database NLS_TERRITORY initialization parameter that specifies the default territory-dependent date and numeric formatting used in the user's notification session. If null, the procedure resolves this to the territory setting of your current session. |
role_description | An optional description for the role. |
notification_preference | Indicate how this role receives notifications: 'MAILTEXT', 'MAILHTML', 'MAILATTH', 'MAILHTM2', 'QUERY', 'SUMMARY', or, for Oracle Applications only, 'SUMHTML'. If null, the procedure sets the notification preference to 'MAILHTML'. |
role_users | Indicate the names of the users that belong to this role, using commas or spaces to delimit the list. |
email_address | A optional electronic mail address for this role or a mail distribution list defined by your electronic mail system. |
fax | An optional fax number for the role. |
status | The availability of the role to participate in a workflow process. The possible statuses are ACTIVE, EXTLEAVE, INACTIVE, and TMPLEAVE. If null, the procedure sets the status to 'ACTIVE'. |
expiration_date | The date at which the role is no longer valid in the directory service. |
parent_orig_system | An optional code for the originating system of an entity that you want to mark as being related to this role. |
parent_orig_system_id | The primary key that identifies the parent entity in the parent originating system. |
owner_tag | A code to identify the program or application that owns the information for this role. |
Related Topics
Setting Up an Oracle Workflow Directory Service, Oracle Workflow Administrator's Guide
procedure CreateAdHocRole2 (role_name in out varchar2, role_display_name in out varchar2, language in varchar2 default null, territory in varchar2 default null, role_description in varchar2 default null, notification_preference in varchar2 default 'MAILHTML', role_users in WF_DIRECTORY.UserTable, email_address in varchar2 default null, fax in varchar2 default null, status in varchar2 default 'ACTIVE', expiration_date in date default null, parent_orig_system in varchar2 default null, parent_orig_system_id in number default null, owner_tag in varchar2 default null);
Creates a role at runtime by creating a value in the WF_LOCAL_ROLES table with the user flag set to N. This is referred to as an ad hoc role. CreateAdHocRole2() accepts the list of users who belong to the role in the WF_DIRECTORY.UserTable format, which lets you include user names that contain spaces or commas.
Note: A role can contain only individual users as its members. It cannot contain another role.
Variable | Description |
---|---|
role_name | An internal name for the role. The internal name must be no longer than 320 characters. It is recommended that the internal name be all uppercase. This procedure checks that the name provided does not already exist in WF_ROLES and returns an error if the name already exists. If you do not provide an internal name, the system generates an internal name for you where the name contains a prefix of '~WF_ADHOC-' followed by a sequence number. |
role_display_name | The display name of the role. This procedure checks that the display name provided does not already exist in WF_ROLES and returns an error if the display name already exists. If you do not provide a display name, the system generates one for you where the display name contains a prefix of '~WF_ADHOC-' followed by a sequence number. |
language | The value of the database NLS_LANGUAGE initialization parameter that specifies the default language-dependent behavior of the user's notification session. If null, the procedure resolves this to the language setting of your current session. |
territory | The value of the database NLS_TERRITORY initialization parameter that specifies the default territory-dependent date and numeric formatting used in the user's notification session. If null, the procedure resolves this to the territory setting of your current session. |
role_description | An optional description for the role. |
notification_preference | Indicate how this role receives notifications: 'MAILTEXT', 'MAILHTML', 'MAILATTH', 'MAILHTM2', 'QUERY', 'SUMMARY', or, for Oracle Applications only, 'SUMHTML'. If null, the procedure sets the notification preference to 'MAILHTML'. |
role_users | The names of the users that belong to this role, as a table in the WF_DIRECTORY.UserTable format. |
email_address | A optional electronic mail address for this role or a mail distribution list defined by your electronic mail system. |
fax | An optional fax number for the role. |
status | The availability of the role to participate in a workflow process. The possible statuses are ACTIVE, EXTLEAVE, INACTIVE, and TMPLEAVE. If null, the procedure sets the status to 'ACTIVE'. |
expiration_date | The date at which the role is no longer valid in the directory service. |
parent_orig_system | An optional code for the originating system of an entity that you want to mark as being related to this role. |
parent_orig_system_id | The primary key that identifies the parent entity in the parent originating system. |
owner_tag | A code to identify the program or application that owns the information for this role. |
Related Topics
Setting Up an Oracle Workflow Directory Service, Oracle Workflow Administrator's Guide
procedure AddUsersToAdHocRole (role_name in varchar2, role_users in varchar2);
Adds users to an existing ad hoc role.
Note: A role can contain only individual users as its members. It cannot contain another role.
Variable | Description |
---|---|
role_name | The internal name of the ad hoc role. |
role_users | The list of users, delimited by spaces or commas. Users can be ad hoc users or users defined in an application, but they must already be defined in the Oracle Workflow directory service. |
procedure AddUsersToAdHocRole2 (role_name in varchar2, role_users in WF_DIRECTORY.UserTable);
Adds users to a existing ad hoc role. AddUsersToAdHocRole2() accepts the list of users in the WF_DIRECTORY.UserTable format, which lets you include user names that contain spaces or commas.
Note: A role can contain only individual users as its members. It cannot contain another role.
Variable | Description |
---|---|
role_name | The internal name of the ad hoc role. |
role_users | The list of users, as a table in the WF_DIRECTORY.UserTable format. Users can be ad hoc users or users defined in an application, but they must already be defined in the Oracle Workflow directory service. |
procedure RemoveUsersFromAdHocRole (role_name in varchar2, role_users in varchar2 default null);
Removes users from an existing ad hoc role.
Variable | Description |
---|---|
role_name | The internal name of the ad hoc role. |
role_users | List of users to remove from the ad hoc role. The users are delimited by commas or spaces. If null, all users are removed from the role. |
procedure SetAdHocUserStatus (user_name in varchar2, status in varchar2 default 'ACTIVE');
Sets the status of an ad hoc user as 'ACTIVE' or 'INACTIVE'.
Important: If you implement Oracle Internet Directory integration, you must maintain your users only through Oracle Internet Directory. You must not use the SetAdHocUserStatus() API to update user information in the WF_LOCAL_ROLES table, because you risk discrepancies in your user information and unpredictable results if you use any tool other than Oracle Internet Directory to maintain users after integrating with Oracle Internet Directory.
Variable | Description |
---|---|
user_name | The internal name of the user. |
status | A status of 'ACTIVE' or 'INACTIVE' to set for the user. If null, the status is 'ACTIVE'. |
procedure SetAdHocRoleStatus (role_name in varchar2, status in varchar2 default 'ACTIVE');
Sets the status of an ad hoc role as 'ACTIVE' or 'INACTIVE'.
Variable | Description |
---|---|
role_name | The internal name of the role. |
status | A status of 'ACTIVE' or 'INACTIVE' to set for the role. If null, the status is 'ACTIVE'. |
procedure SetAdHocUserExpiration (user_name in varchar2, expiration_date in date default sysdate);
Updates the expiration date for an ad hoc user.
Note that although users and roles whose expiration date has passed do not appear in the seeded WF_USERS, WF_ROLES, and WF_USER_ROLES views, they are not removed from the Workflow local tables until you purge them using Directory(). You should periodically purge expired users and roles in order to improve performance. See: Directory.
Important: If you implement Oracle Internet Directory integration, you must maintain your users only through Oracle Internet Directory. You must not use the SetAdHocUserExpiration() API to update user information in the WF_LOCAL_ROLES table, because you risk discrepancies in your user information and unpredictable results if you use any tool other than Oracle Internet Directory to maintain users after integrating with Oracle Internet Directory.
Variable | Description |
---|---|
user_name | The internal name of the ad hoc user. |
expiration_date | New expiration date. If null, the procedure defaults the expiration date to sysdate. |
procedure SetAdHocRoleExpiration (role_name in varchar2, expiration_date in date default sysdate);
Updates the expiration date for an ad hoc role.
Note that although users and roles whose expiration date has passed do not appear in the seeded WF_USERS, WF_ROLES, and WF_USER_ROLES views, they are not removed from the Workflow local tables until you purge them using Directory(). You should periodically purge expired users and roles in order to improve performance. See: Directory.
Variable | Description |
---|---|
role_name | The internal name of the ad hoc role. |
expiration_date | New expiration date. If null, the procedure defaults the expiration date to sysdate. |
procedure SetAdHocUserAttr (user_name in varchar2, display_name in varchar2 default null, notification_preference in varchar2 default null, language in varchar2 default null, territory in varchar2 default null, email_address in varchar2 default null, fax in varchar2 default null, parent_orig_system in varchar2 default null, parent_orig_system_id in number default null, owner_tag in varchar2 default null);
Updates the attributes for an ad hoc user.
Important: If you implement Oracle Internet Directory integration, you must maintain your users only through Oracle Internet Directory. You must not use the SetAdHocUserAttr() API to update user information in the WF_LOCAL_ROLES table, because you risk discrepancies in your user information and unpredictable results if you use any tool other than Oracle Internet Directory to maintain users after integrating with Oracle Internet Directory.
Variable | Description |
---|---|
user_name | The internal name of the ad hoc user to update. |
display_name | A new display name for the ad hoc user. If null, the display name is not updated. |
notification_preference | A new notification preference of 'MAILTEXT', 'MAILHTML', 'MAILATTH', 'MAILHTM2', 'QUERY', 'SUMMARY', or, for Oracle Applications only, 'SUMHTML'. If null, the notification preference is not updated. |
language | A new value of the database NLS_LANGUAGE initialization parameter for the ad hoc user. If null, the language setting is not updated. |
territory | A new value of the database NLS_TERRITORY initialization parameter for the ad hoc user. If null, the territory setting is not updated. |
email_address | A new valid electronic mail address for the ad hoc user. If null, the electronic mail address is not updated. |
fax | A new fax number for the ad hoc user. If null, the fax number is not updated. |
parent_orig_system | An optional code for the originating system of an entity that you want to mark as being related to this user. |
parent_orig_system_id | The primary key that identifies the parent entity in the parent originating system. |
owner_tag | A code to identify the program or application that owns the information for this user. |
procedure SetAdHocRoleAttr (role_name in varchar2, display_name in varchar2 default null, notification_preference in varchar2 default null, language in varchar2 default null, territory in varchar2 default null, email_address in varchar2 default null, fax in varchar2 default null, parent_orig_system in varchar2 default null, parent_orig_system_id in number default null, owner_tag in varchar2 default null);
Updates the attributes for an ad hoc role.
Variable | Description |
---|---|
role_name | The internal name of the ad hoc role to update. |
display_name | A new display name for the ad hoc role. If null, the display name is not updated. |
notification_preference | A new notification preference of 'MAILTEXT', 'MAILHTML', 'MAILATTH', 'MAILHTM2', 'QUERY', 'SUMMARY', or, for Oracle Applications only, 'SUMHTML'. If null, the notification preference is not updated. |
language | A new value of the database NLS_LANGUAGE initialization parameter for the ad hoc role. If null, the language setting is not updated. |
territory | A new value of the database NLS_TERRITORY initialization parameter for the ad hoc role. If null, the territory setting is not updated. |
email_address | A new valid electronic mail address for the ad hoc role. If null, the electronic mail address is not updated. |
fax | A new fax number for the ad hoc role. If null, the fax number is not updated. |
parent_orig_system | An optional code for the originating system of an entity that you want to mark as being related to this role. |
parent_orig_system_id | The primary key that identifies the parent entity in the parent originating system. |
owner_tag | A code to identify the program or application that owns the information for this role. |
function ChangeLocalUserName (OldName in varchar2, NewName in varchar2, Propagate in boolean default TRUE) return boolean;
Changes a user's name in the WF_LOCAL_ROLES table. Returns TRUE if the name change completes successfully; otherwise, the API returns FALSE.
Variable | Description |
---|---|
OldName | The current name of the user. |
NewName | The new name for the user. |
Propagate | Specify TRUE to change all occurrences of the old user name to the new user name. |
function IsMLSEnabled (p_orig_system in varchar2) return boolean;
Determines whether Multilingual Support (MLS) is enabled for the specified originating system. Returns TRUE if MLS is enabled; otherwise the API returns FALSE.
Variable | Description |
---|---|
p_orig_system | A system from which directory service information originates. |
Call the following APIs to synchronize local user information in your Workflow directory service with the users in an LDAP directory such as Oracle Internet Directory. These APIs are defined in a PL/SQL package called WF_LDAP.
Related Topics
Synchronizing Workflow Directory Services with Oracle Internet Directory, Oracle Workflow Administrator's Guide
function synch_changes return boolean;
Determines whether there have been any user changes to an LDAP directory since the last synchronization by querying the LDAP change log records; if there are any changes, including creation, modification, and deletion, Synch_changes() stores the user attribute information in an attribute cache and raises the oracle.apps.global.user.change event to alert interested parties. The function connects to the LDAP directory specified in the global workflow preferences. One event is raised for each changed user.
If the function completes successfully, it returns TRUE; otherwise, if it encounters an exception, it returns FALSE.
Related Topics
Synchronizing Workflow Directory Services with Oracle Internet Directory, Oracle Workflow Administrator's Guide
Setting Global User Preferences, Oracle Workflow Administrator's Guide
User Entry Has Changed Event, Oracle Workflow Developer's Guide
function synch_all return boolean;
Retrieves all users from an LDAP directory, stores the user attribute information in an attribute cache, and raises the oracle.apps.global.user.change event to alert interested parties. The function connects to the LDAP directory specified in the global workflow preferences. One event is raised for each user.
Because Synch_all() retrieves information for all users stored in the LDAP directory, you should use this function only once during setup, or as required for recovery or cleanup. Subsequently, you can use Synch_changes() or Schedule_changes() to retrieve only changed user information.
If the function completes successfully, it returns TRUE; otherwise, if it encounters an exception, it returns FALSE.
The standalone Oracle Workflow installation runsSynch_all() to begin your Workflow directory service synchronization with Oracle Internet Directory if you implement Oracle Internet Directory integration.
Related Topics
Synchronizing Workflow Directory Services with Oracle Internet Directory, Oracle Workflow Administrator's Guide
Setting Global User Preferences, Oracle Workflow Administrator's Guide
User Entry Has Changed Event, Oracle Workflow Developer's Guide
procedure schedule_changes (l_day in pls_integer default 0, l_hour in pls_integer default 0, l_minute in pls_integer default 10);
Runs the Synch_changes() API repeatedly at the specified time interval to check for user changes in an LDAP directory and alert interested parties of any changes. The default interval is ten minutes. Schedule_changes() submits a database job using the DBMS_JOB utility to run Synch_changes().
Run Schedule_changes() to maintain your Workflow directory service synchronization with Oracle Internet Directory if you implement Oracle Internet Directory integration.
Variable | Description |
---|---|
l_day | The number of days in the interval to specify how often you want to run the Synch_changes() API. The default value is zero. |
l_hour | The number of hours in the interval to specify how often you want to run the Synch_changes() API. The default value is zero. |
l_minute | The number of minutes in the interval to specify how often you want to run the Synch_changes() API. The default value is ten. |
Related Topics
Synchronizing Workflow Directory Services with Oracle Internet Directory, Oracle Workflow Administrator's Guide
The following APIs can be called in Oracle Applications to synchronize user and role information stored in application tables with the information in the Workflow local tables. These APIs are defined in a PL/SQL package called WF_LOCAL_SYNCH.
Note: The Propagate_User_Role() API from earlier versions of Oracle Workflow is replaced by the PropagateUserRole() API. The current version of Oracle Workflow still recognizes the Propagate_User_Role() API for backward compatibility, but moving forward, you should only use the new PropagateUserRole() API where appropriate.
Related Topics
Setting Up an Oracle Workflow Directory Service, Oracle Workflow Administrator's Guide
procedure Propagate_User (p_orig_system in varchar2, p_orig_system_id in number, p_attributes in wf_parameter_list_t, p_start_date in date default null, p_expiration_date in date default null);
Synchronizes the information for a user from an application table with the WF_LOCAL_ROLES table and marks this record as an individual user by setting the user flag to Y. The user is identified by the specified originating system and originating system ID. The partition ID where the user's information is stored is set automatically depending on the originating system.
Note: For Oracle Applications, only Oracle Applications users from the FND_USER table, Oracle Trading Community Architecture (TCA) person parties, and TCA contacts (relationship parties) should be synchronized using Propagate_User(). All other Oracle Applications modules should synchronize their information using Propagate_Role().
The user information to be stored in the WF_LOCAL_ROLES table must be provided in the WF_PARAMETER_LIST_T format. You can use the WF_EVENT.AddParameterToList() API to add attributes to the list. The following table shows the attributes that should be included in the list to populate the required columns in WF_LOCAL_ROLES. The standard LDAP attribute names should be used for these attributes.
Database Column | Attribute Name |
---|---|
NAME | [USER_NAME] |
DISPLAY_NAME | [DisplayName] |
DESCRIPTION | [description] |
NOTIFICATION_PREFERENCE | [orclWorkFlowNotificationPref] |
LANGUAGE | [preferredLanguage] |
TERRITORY | [orclNLSTerritory] |
EMAIL_ADDRESS | [mail] |
FAX | [FacsimileTelephoneNumber] |
STATUS | [orclIsEnabled] |
EXPIRATION_DATE | [ExpirationDate] |
ORIG_SYSTEM | [orclWFOrigSystem] |
ORIG_SYSTEM_ID | [orclWFOrigSystemID] |
PARENT_ORIG_SYSTEM | [orclWFParentOrigSys] |
PARENT_ORIG_SYSTEM_ID | [orclWFParentOrigSysID] |
OWNER_TAG | [OWNER_TAG] |
PERSON_PARTY_ID | [PERSON_PARTY_ID] |
LAST_UPDATED_BY | [LAST_UPDATED_BY] |
LAST_UPDATE_DATE | [LAST_UPDATE_DATE] |
LAST_UPDATE_LOGIN | [LAST_UPDATE_LOGIN] |
CREATED_BY | [CREATED_BY] |
CREATION_DATE | [CREATION_DATE] |
In normal operating mode, if any of these attributes except USER_NAME are not passed in the attribute list or are null, the existing value in the corresponding field in WF_LOCAL_ROLES remains the same. For example, if no e-mail address is passed, the existing e-mail address for the user is retained. However, you must always pass the USER_NAME attribute, because the Propagate_User() procedure uses this value in a WHERE condition and will fail if the USER_NAME is not provided. Also, if the user record does not already exist, you must pass all of the listed attributes since there are no existing values to use.
For more robust code, you should always pass all of the listed attributes when calling Propagate_User(). In this way you can avoid errors caused by trying to determine dynamically which attributes to pass.
Note: If a display name is not provided in the attribute list when the user record is first created in normal operating mode, this value is set by default to a composite value in the format <orig_system>:<orig_system_ID> in the user record in WF_LOCAL_ROLES. Additionally, if no notification preference is provided, the notification preference for the user record is set by default to MAILHTML, and if no status is provided, the status for the user record is set by default to ACTIVE. If no TCA person party ID is provided, Oracle Workflow uses a value consisting of the originating system and originating system ID as the person party ID.
You can also call Propagate_User() in overwrite mode by including a special attribute named WFSYNCH_OVERWRITE with a value of 'TRUE'. In overwrite mode, if one of the following attributes is not passed or is null, the procedure sets the value of the corresponding field in WF_LOCAL_ROLES to null, deleting the previous value.
description
preferredLanguage
orclNLSTerritory
FacsimileTelephoneNumber
ExpirationDate
orclWFParentOrigSys
orclWFParentOrigSysID
OWNER_TAG
LAST_UPDATED_BY
LAST_UPDATE_DATE
LAST_UPDATE_LOGIN
Consequently, when you are using overwrite mode, you must pass values for all the attributes that you do not want to be null. Also, you must always pass the USER_NAME attribute.
Note: The DISPLAY_NAME, NOTIFICATION_PREFERENCE, STATUS, ORIG_SYSTEM, and ORIG_SYSTEM_ID columns in the WF_LOCAL_ROLES table have a NOT NULL constraint, so these columns retain their existing values if you do not pass a value for the corresponding attributes, even if you are using overwrite mode.
The NAME column in WF_LOCAL_ROLES also has a NOT NULL constraint, and you cannot omit the USER_NAME attribute in any case because it is required for the API.
Certain values, including the originating system, originating system ID, and expiration date, can be passed both as parameters for the Propagate_User() API and as attributes within the attribute list parameter. These values are repeated in the attribute list because Propagate_User() sends only the attribute list to the Entity Manager that coordinates LDAP integration, and not any of the procedure's other parameters.
The originating system and originating system ID values that are passed as parameters to the procedure override any originating system and originating system ID values that are provided as attributes within the attribute list, if these values differ.
Likewise, if an expiration date value is passed as a parameter to the procedure, that value overrides any expiration date value provided as an attribute within the attribute list. However, if the p_expiration_date parameter is null, the value of the ExpirationDate attribute will be used, if one is provided. You must provide the ExpirationDate attribute value in the following format:
to_char(<your date variable>, WF_ENGINE.Date_Format)
Oracle Workflow also provides two additional special attributes that you can use to specify how the user information should be modified.
DELETE - You can use this attribute when you want to remove a user from availability to participate in a workflow. If you include this attribute with a value of 'TRUE', the expiration date for the user in WF_LOCAL_ROLES is set to sysdate and the status is set to INACTIVE.
Note: If you also pass a value for the p_expiration_date parameter, however, that value will override the DELETE attribute. Additionally, if the p_expiration_date parameter is null but you include the ExpirationDate attribute, that attribute value will override the DELETE attribute. In these cases the user will remain valid and active until the specified expiration date.
UpdateOnly - You can use this attribute for performance gain when you want to modify information for a user for whom a record already exists in WF_LOCAL_ROLES. If you include this attribute with a value of 'TRUE', the Propagate_User() API attempts to update the record directly, without first inserting the record.
If this update attempt fails because a record does not already exist for that user, the procedure will then insert the record. However, the initial unsuccessful attempt will degrade performance, so you should only use the UpdateOnly attribute when you are certain that the user record already exists in WF_LOCAL_ROLES.
Variable | Description |
---|---|
p_orig_system | A code that you assign to the directory repository that is the source of the user information. |
p_orig_system_id | The primary key that identifies the user in this repository system. |
p_attributes | A list of attribute name and value pairs containing information about the user. |
p_start_date | The date at which the user becomes valid in the directory service. |
p_expiration_date | The date at which the user is no longer valid in the directory service. |
Related Topics
procedure Propagate_Role (p_orig_system in varchar2, p_orig_system_id in number, p_attributes in wf_parameter_list_t, p_start_date in date default null, p_expiration_date in date default null);
Synchronizes the information for a role from an application table with the WF_LOCAL_ROLES table and sets the user flag for the role to N. The role is identified by the specified originating system and originating system ID. The partition ID where the role's information is stored is set automatically depending on the originating system.
The role information to be stored in the WF_LOCAL_ROLES table must be provided in the WF_PARAMETER_LIST_T format. You can use the WF_EVENT.AddParameterToList() API to add attributes to the list. The following table shows the attributes that should be included in the list to populate the required columns in WF_LOCAL_ROLES. The standard LDAP attribute names should be used for these attributes.
Database Column | Attribute Name |
---|---|
NAME | [USER_NAME] |
DISPLAY_NAME | [DisplayName] |
DESCRIPTION | [description] |
NOTIFICATION_PREFERENCE | [orclWorkFlowNotificationPref] |
LANGUAGE | [preferredLanguage] |
TERRITORY | [orclNLSTerritory] |
EMAIL_ADDRESS | [mail] |
FAX | [FacsimileTelephoneNumber] |
STATUS | [orclIsEnabled] |
EXPIRATION_DATE | [ExpirationDate] |
ORIG_SYSTEM | [orclWFOrigSystem] |
ORIG_SYSTEM_ID | [orclWFOrigSystemID] |
PARENT_ORIG_SYSTEM | [orclWFParentOrigSys] |
PARENT_ORIG_SYSTEM_ID | [orclWFParentOrigSysID] |
OWNER_TAG | [OWNER_TAG] |
LAST_UPDATED_BY | [LAST_UPDATED_BY] |
LAST_UPDATE_DATE | [LAST_UPDATE_DATE] |
LAST_UPDATE_LOGIN | [LAST_UPDATE_LOGIN] |
CREATED_BY | [CREATED_BY] |
CREATION_DATE | [CREATION_DATE] |
In normal operating mode, if any of these attributes except USER_NAME are not passed in the attribute list or are null, the existing value in the corresponding field in WF_LOCAL_ROLES remains the same. For example, if no e-mail address is passed, the existing e-mail address for the role is retained. However, you must always pass the USER_NAME attribute, because the Propagate_Role() procedure uses this value in a WHERE condition and will fail if the USER_NAME is not provided. Also, if the user record does not already exist, you must pass all of the listed attributes since there are no existing values to use.
For more robust code, you should always pass all of the listed attributes when calling Propagate_Role(). In this way you can avoid errors caused by trying to determine dynamically which attributes to pass.
Note: If a display name is not provided in the attribute list when the role record is first created in normal operating mode, this value is set by default to a composite value in the format <orig_system>:<orig_system_ID> in the role record in WF_LOCAL_ROLES. Additionally, if no notification preference is provided, the notification preference for the role record is set by default to MAILHTML, and if no status is provided, the status for the role record is set by default to ACTIVE.
You can also call Propagate_Role() in overwrite mode by including a special attribute named WFSYNCH_OVERWRITE with a value of 'TRUE'. In overwrite mode, if one of the following attributes is not passed or is null, the procedure sets the value of the corresponding field in WF_LOCAL_ROLES to null, deleting the previous value.
description
preferredLanguage
orclNLSTerritory
FacsimileTelephoneNumber
ExpirationDate
orclWFParentOrigSys
orclWFParentOrigSysID
OWNER_TAG
LAST_UPDATED_BY
LAST_UPDATE_DATE
LAST_UPDATE_LOGIN
Consequently, when you are using overwrite mode, you must pass values for all the attributes that you do not want to be null. Also, you must always pass the USER_NAME attribute.
Note: The DISPLAY_NAME, NOTIFICATION_PREFERENCE, STATUS, ORIG_SYSTEM, and ORIG_SYSTEM_ID columns in the WF_LOCAL_ROLES table have a NOT NULL constraint, so these columns retain their existing values if you do not pass a value for the corresponding attributes, even if you are using overwrite mode.
The NAME column in WF_LOCAL_ROLES also has a NOT NULL constraint, and you cannot omit the USER_NAME attribute in any case because it is required for the API.
Certain values, including the originating system, originating system ID, and expiration date, can be passed both as parameters for the Propagate_Role() API and as attributes within the attribute list parameter. These values are repeated in the attribute list because Propagate_Role() sends only the attribute list to the Entity Manager that coordinates LDAP integration, and not any of the procedure's other parameters.
The originating system and originating system ID values that are passed as parameters to the procedure override any originating system and originating system ID values that are provided as attributes within the attribute list, if these values differ.
Likewise, if an expiration date value is passed as a parameter to the procedure, that value overrides any expiration date value provided as an attribute within the attribute list. However, if the p_expiration_date parameter is null, the value of the ExpirationDate attribute will be used, if one is provided. You must provide the ExpirationDate attribute value in the following format:
to_char(<your date variable>, WF_ENGINE.Date_Format)
Oracle Workflow also provides two additional special attributes that you can use to specify how the role information should be modified.
DELETE - You can use this attribute when you want to remove a role from availability to participate in a workflow. If you include this attribute with a value of 'TRUE', the expiration date for the role in WF_LOCAL_ROLES is set to sysdate and the status is set to INACTIVE.
Note: If you also pass a value for the p_expiration_date parameter, however, that value will override the DELETE attribute. Additionally, if the p_expiration_date parameter is null but you include the ExpirationDate attribute, that attribute value will override the DELETE attribute. In these cases the role will remain valid and active until the specified expiration date.
UpdateOnly - You can use this attribute for performance gain when you want to modify information for a role for which a record already exists in WF_LOCAL_ROLES. If you include this attribute with a value of 'TRUE', the Propagate_Role() API attempts to update the record directly, without first inserting the record.
If this update attempt fails because a record does not already exist for that role, the procedure will then insert the record. However, the initial unsuccessful attempt will degrade performance, so you should only use the UpdateOnly attribute when you are certain that the role record already exists in WF_LOCAL_ROLES.
Note: In Oracle Applications, if an Oracle Human Resources person role with an originating system of PER_ROLE is propagated using Propagate_Role(), and that person is linked to an Oracle Applications user, then the procedure updates the corresponding user record with an originating system of PER in WF_LOCAL_ROLES, as well as the person record.
Variable | Description |
---|---|
p_orig_system | A code that you assign to the directory repository that is the source of the role information. |
p_orig_system_id | The primary key that identifies the role in this repository system. |
p_attributes | A list of attribute name and value pairs containing information about the role. |
p_start_date | The date at which the role becomes valid in the directory service. |
p_expiration_date | The date at which the role is no longer valid in the directory service. |
Related Topics
procedure PropagateUserRole (p_user_name in varchar2, p_role_name in varchar2, p_user_orig_system in varchar2 default null, p_user_orig_system_id in number default null, p_role_orig_system in varchar2 default null, p_role_orig_system_id in number default null, p_start_date in date default null, p_expiration_date in date default null, p_overwrite in boolean default FALSE, p_raiseErrors in boolean default FALSE, p_parent_orig_system in varchar2 default null, p_parent_orig_system_id in varchar2 default null, p_ownerTag in varchar2 default null, p_createdBy in number default null, p_lastUpdatedBy in number default null, p_lastUpdateLogin in number default null, p_creationDate in date default null, p_lastUpdateDate in date default null);
Synchronizes the information for an association of a user and a role from an application table with the WF_LOCAL_USER_ROLES table.
Variable | Description |
---|---|
p_user_name | The internal name of the user. |
p_role_name | The internal name of the role. |
p_user_orig_system | A code that you assign to the directory repository that is the source of the user information. |
p_user_orig_system_id | The primary key that identifies the user in this repository system. |
p_role_orig_system | A code that you assign to the directory repository that is the source of the role information. |
p_role_orig_system_id | The primary key that identifies the role in this repository system. |
p_start_date | The date at which the association of this user with this role becomes valid in the directory service. |
p_expiration_date | The date at which the association of this user with this role is no longer valid in the directory service. |
p_overwrite | Specify TRUE or FALSE to determine whether to propagate the information in overwrite mode. In overwrite mode, if any attribute is not passed or is null, the procedure sets the value of the corresponding field in WF_LOCAL_USER_ROLES to null, deleting the previous value.
Note: Overwrite mode does not affect the user name and role name attributes. You must pass values for these parameters, because they are required for this procedure, and because the USER_NAME and ROLE_NAME columns in the WF_LOCAL_USER_ROLES table have a NOT NULL constraint. |
p_raiseErrors | Specify TRUE or FALSE to determine whether the procedure should raise an exception if it encounters an error. |
p_parent_orig_system | A code for the originating system of an entity that you want to mark as being related to the association of this user with this role. |
p_parent_orig_system_id | The primary key that identifies the parent entity in the parent originating system. |
p_ownerTag | A code to identify the program or application that owns the information for the association of this user with this role. |
p_createdBy | Standard Who column. |
p_lastUpdatedBy | Standard Who column. |
p_lastUpdateLogin | Standard Who column. |
p_creationDate | Standard Who column. |
p_lastUpdateDate | Standard Who column. |
The following APIs can be called by an application program or a workflow function in the runtime phase to manage role hierarchy relationships in the Oracle E-Business Suite directory service. These APIs are defined in a PL/SQL package called WF_ROLE_HIERARCHY.
Related Topics
Setting Up an Oracle Workflow Directory Service, Oracle Workflow Administrator's Guide
function AddRelationship (p_sub_name in varchar2, p_super_name in varchar2, p_deferMode in boolean default FALSE, p_enabled in varchar2 default 'Y') return number;
Creates a hierarchical relationship between two roles in the WF_ROLE_HIERARCHIES table and returns the relationship ID.
Variable | Description |
---|---|
p_sub_name | The internal name of the subordinate role. |
p_super_name | The internal name of the superior role. |
p_deferMode | Specify TRUE or FALSE to determine whether to defer propagation of the new relationship. If you specify FALSE, existing user and role assignments are updated according to the new relationship, without deferral. |
p_enabled | Specify 'Y' if the relationship is initially enabled or 'N' if the relationship is initially disabled. |
function ExpireRelationship (p_sub_name in varchar2, p_super_name in varchar2, p_defer_mode in boolean default FALSE) return number;
Expires a hierarchical relationship between two roles in the WF_ROLE_HIERARCHIES table and returns the relationship ID.
Variable | Description |
---|---|
p_sub_name | The internal name of the subordinate role. |
p_super_name | The internal name of the superior role. |
p_defer_mode | Specify TRUE or FALSE to determine whether to defer propagation of the expired relationship. If you specify FALSE, existing user and role assignments are updated according to the expired relationship, without deferral. |
procedure GetRelationships (p_name in varchar2, p_superiors out WF_ROLE_HIERARCHY.relTAB, p_subordinates out WF_ROLE_HIERARCHY.relTAB, p_direction in VARCHAR2 default 'BOTH');
Retrieves the hierarchical relationships for the specified role and returns a table of superior roles and a table of subordinate roles. GetRelationships() stops retrieving relationships in a hierarchy when it encounters a disabled relationship.
Variable | Description |
---|---|
p_name | The internal name of the role. |
p_direction | Specify 'SUPERIORS' to retrieve superior roles for this role, 'SUBORDINATES' to retrieve subordinate roles for this role, or 'BOTH' to retrieve both superior and subordinate roles. |
procedure GetAllRelationships (p_name in varchar2, p_superiors out WF_ROLE_HIERARCHY.relTAB, p_subordinates out WF_ROLE_HIERARCHY.relTAB, p_direction in VARCHAR2 default 'BOTH');
Retrieves the hierarchical relationships for the specified role and returns a table of superior roles and a table of subordinate roles. GetAllRelationships() retrieves all hierarchical relationships, whether they are enabled or disabled.
Variable | Description |
---|---|
p_name | The internal name of the role. |
p_direction | Specify 'SUPERIORS' to retrieve superior roles for this role, 'SUBORDINATES' to retrieve subordinate roles for this role, or 'BOTH' to retrieve both superior and subordinate roles. |
Call the following API to retrieve user preference information. The API is defined in the PL/SQL package called WF_PREF.
function get_pref (p_user_name in varchar2, p_preference_name in varchar2) return varchar2;
Retrieves the value of the specified preference for the specified user.
Variable | Description |
---|---|
p_user_name | The internal name of the user. To retrieve the value for a global preference, specify the user as -WF_DEFAULT-. |
p_preference_name | The name of the user preference whose value you wish to retrieve. Valid preference names are:
|