The Oracle ERP resource adapter is defined in the com.waveset.adapter.OracleERPResourceAdapter class. This adapter supports Oracle E-Business Suite (EBS).
Waveset also provides an Oracle resource adapter that supports Oracle databases. For detailed information about this adapter, see Chapter 24, Oracle.
None
The Oracle ERP resource adapter is a custom adapter. You must perform the following steps to complete the installation process:
To add an Oracle resource to the Waveset resources list, you must add the following value in the Custom Resources section of the Configure Managed Resources page.
com.waveset.adapter.OracleERPResourceAdapter |
If you are connecting to Oracle Real Application Clusters (RAC) using a thin driver, specify a value in the following format in the Connection URL on the Resource parameters page:
jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=off) (ADDRESS=(PROTOCOL=TCP)(HOST=host01)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=host02)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=host03)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=PROD))) |
If you are using the JDBC thin driver in an environment that does not use Oracle Real Application Clusters, copy the JAR file that contains the JDBC thin driver classes to the $WSHOME$/WEB-INF/lib directory. The JAR file must be compatible with the JDK version of your application server.
If you are using a different driver, specify the driver and connection URL on the Resource Parameters page.
The Oracle ERP adapter supports Oracle E-Business Suite (EBS) version 11.5.9 without further modification; however, the following additional changes are required to support EBS version 11.5.10 and 12:
Delete the responsibilities account attribute from the schema map and add the directResponsibilities and indirectResponsibilities attributes.
Add the following properties to the FormRef attribute to any Oracle ERP user form :
RESOURCE_NAME. Specifies the ERP resource name
VERSION. Specifies the version of the ERP resource. Allowed values are 11.5.9, 11.5.10, 12.
RESP_DESCR_COL_EXISTS. Defines whether the description column exists in the fnd_user_resp_groups_direct table. This property is required if Version is 11.5.10 or 12. Allows values are TRUE and FALSE.
For example, the Tabbed User Form may need to be modified in a manner similar to the following to support EBS version 12.
<FormRef name=’Oracle ERP User Form’> <Property name=’RESOURCE_NAME’ value=’Oracle ERP R12’/> <Property name=’VERSION’ value=’12’/> <Property name=’RESP_DESCR_COL_EXISTS’ value=’TRUE’/> </FormRef> |
This section describes resource parameters that are applicable for the Oracle ERP adapter, including
User security is controlled at three levels within Oracle Applications:
Functional security, which controls user access to the individual menus and menu options within the system.
Data security, which controls what specific data objects a user can operate on.
Role Based Access Control (RBAC), which allows for the creation of roles, to which responsibilities and permissions can be assigned.
The Oracle ERP adapter supports functional security only. Therefore, the adapter cannot list create, update, or delete Oracle data objects, object instances, or instance sets. Nor does the adapter create or manage role objects, role hierarchies or role categories.
This parameter can contain a list of valid Oracle support encryption algorithm names, such as RC4_56 or RC4_128. If this list is empty, all algorithms supported by Oracle for that Oracle release will be available. The client/server will negotiate on which of these algorithms to use based on Oracle Client Encryption Level setting.
The Oracle Server must also be configured to support this type of encryption.
For a more information about the supported algorithms, refer to the Oracle Advanced Security Administrator’s Guide. See the SQLNET.ENCRYPTION_TYPES_CLIENT section for a list of valid values for the thin JDBC client.
This value determines the level of security that the server/client negotiates and enforces. The default value, if left blank, is ACCEPTED. The valid values are REJECTED, ACCEPTED, REQUESTED and REQUIRED. For more details about the use of this parameter, refer to the Oracle Advanced Security Administrator’s Guide and the SQLNET.ENCRYPTION_CLIENT values.
You must also configure the Oracle Server to support this type of encryption.
This value determines the EBS Responsibility used by the Waveset Oracle EBS Admin user to call the EBS application initialization routine. A list of valid responsibilities can be found in the fnd_responsibility_vl table. Also refer to the Oracle EBS documentation for more information.
If the Waveset Oracle EBS Admin user has a valid EBS system account and has a responsibility that matches the value of this parameter, the Oracle session created during connection enables the users’ actions to be audited using the Oracle EBS auditing mechanism. For example, the created_by and the last_updated_by fields of the fnd_user table objects will be updated correctly with the user ID of the Waveset Oracle EBS Admin user.
The securingAttrs account attribute supports the Securing Attributes feature in Oracle E-business Suite. To configure Securing Attributes from the Waveset Create User page, perform the following steps:
Select the Add Securing Attribute checkbox.
Enter a search pattern to narrow the choices of available attributes in the Enter Securing Attribute Search Pattern text box. Use the % character as a wild card. Then click the Load Securing Attributes button. This will load the attributes into the Oracle Securing Attributes select box.
Select an attribute from the drop-down menu, and it will be added to the Securing Attributes table.
You can remove securing attributes by selecting the attribute to be removed from the table and clicking the Remove Selected Securing Attribute button.
Enabling an Oracle EBS user requires the value of the owner attribute to be specified. The value CUST is used by default unless the value is specifically added to the Enable form and sent through the Enable view. The following code example changes the default owner to MYOWNER:
<Field name=’resourceAccounts.currentResourceAccounts[MyOracleERP]. attributes.owner’ type=’string’> <Display class=’Text’> <Property name=’title’ value=’Owner’/> </Display> <Default> <s>MYOWNER</s> </Default> </Field>
The listResourceObjects call can be used to return a user’s responsibilities and other Oracle EBS objects. The following table provides information about the supported object types.
Object |
Options Supported |
Comments |
---|---|---|
auditorResps |
id, activeRespsOnly |
Returns a list of auditor responsibilities for the user. id is a string indicating a resource identity whose responsibilities are returned. If activeRespsOnly is set to true, then only active responsibilities are returned. The default is false. |
responsibilities |
id, activeRespsOnly |
Returns the user’s responsibilities. Valid for 11.5.9 only. |
directResponsibilities |
id, activeRespsOnly |
Returns the user’s direct responsibilities. Valid for 11.5.10 only. |
indirectResponsibilities |
id, activeRespsOnly |
Returns the user’s indirect responsibilities. Valid for 11.5.10 only. |
responsibilityNames |
None |
Returns a list of responsibility names assigned to the user. |
applications |
responsibilityName |
If no responsibility names are specified, then all applications assigned to the user are returned. |
securityGroups |
application |
If no applications are specified, then all security groups assigned to the user are returned. |
account |
activeAccountsOnly |
Returns a list of accounts for the user. If set to true, then only active accounts are returned. The default is false. |
securingAttrs |
searchPattern |
Returns a list of securing attributes that match the specified search pattern. If a pattern is not specified, all securing attributes are returned. |
The following code sample adds a field to the user form that returns active responsibilities. You must replace USER_NAME and RESOURCE_NAME with valid values. auditorResps may be replaced with responsibilities, directResponsibilities, or indirectResponsibilites
<Field name=’respNames’ type=’string’> <Display class=’Text’> <Property name=’title’ value=’Oracle ERP Responsibilities’/> </Display> <Expansion> <invoke name=’listResourceObjects’ class=’com.waveset.ui.FormUtil’> <ref>display.session</ref> <s>auditorResps</s> <s>RESOURCE_NAME</s> <map> <s>id</s> <s>USER_NAME</s> <s>activeRespsOnly</s> <s>true</s> <s>attrsToGet</s> <list> <s>name</s> </list> </map> <s>null</s> </invoke> </Expansion> </Field> |
To audit the sub-items (such as forms and functions) of responsibilities assigned to users, add the auditorObject to the schema map. auditorObject is a complex attribute that contains a set of responsibility objects. The following attributes are always returned in a responsibility object:
responsibility
userMenuNames
menuIds
userFunctionNames
functionIds
formIds
formNames
userFormNames
readOnlyFormIds
readWriteOnlyFormIds
readOnlyFormNames
readOnlyUserFormNames
readWriteOnlyFormNames
readWriteOnlyUserFormNames
functionNames
readOnlyFunctionNames
readWriteOnlyFunctionNames
readOnly and ReadWrite attributes are identified by querying the PARAMETERS column in the fnd_form_functions table for one of the following:
QUERY_ONLY=YES
QUERY_ONLY=”YES”
QUERY_ONLY = YES
QUERY_ONLY =”YES”
QUERY_ONLY=Y
QUERY_ONLY=”Y”
QUERY_ONLY = Y
QUERY_ONLY =”Y”
If the Return Set of Books and/or Organization resource parameter is set to TRUE, the following attributes are also returned:
setOfBooksName
setOfBooksId
organizationalUnitName
organizationalUnitId
With the exception of the responsibility, setOfBooksName, setOfBooksId, organizationalUnitId, and organizationalUnitName attributes, the attribute names match account attribute names that may be added to the schema map. The account attributes contain an aggregate set of values that are assigned to the user. The attributes that are contained in the responsibility objects are specific to the responsibility.
The auditorResps[] view provides access to the responsibility attributes. The following form snippet returns all the active responsibilities (and their attributes) assigned to a user .
<defvar name=’audObj’> <invoke name=’get’> <ref>accounts[Oracle ERP 11i VIS].auditorObject</ref> </invoke> </defvar> <!-- this returns list of responsibility objects --> <defvar name=’respList’> <invoke name=’get’> <ref>audObj</ref> <s>auditorResps[*]</s> </invoke> </defvar> |
For example:
auditorResps[0].responsibility returns the name of the first responsibility object.
auditorResps[0].formNames returns the formNames of the first responsibility object.
The Oracle ERP adapter supports resource actions. To enable these actions, you must supply scripts written in Javascript or BeanShell. The adapter calls these scripts before or after performing the following provisioning actions:
Every action script receives an actionContext map, as defined by the java.util.Map class. The possible map content varies for each action.
Scripts should never close the JDBC Connection that is passed to them. The adapter automatically closes the connection at the appropriate time.
See Chapter 51, Adding Actions to Resources for more information about implementing resource actions. Example scripts are provided in $WSHOME/sample/OracleERPActions.xml.
The actionContext map passed to the action contains the following entries:
Any throw from within the script is considered a failure.
If the script encounters any errors, the script may also add appropriate strings to the errors key. The presence of any items in the errors List is considered a creation failure.
The actionContext map passed to the action contains the following entries:
Any throw from within the script is considered a failure.
If the script encounters any errors, the script may add the appropriate strings to the errors key. The presence of any items in the errors List is considered an update failure.
The actionContext map passed to the action contains the following entries:
Key |
Value Type |
Value Description |
---|---|---|
conn |
java.sql.Connection |
JDBC connection to the database |
adapter |
com.wavset.adapter.OracleERPResourceAdapter |
Adapter instance |
action |
java.lang.String |
The deleteUser string |
timing |
java.lang.String |
Must be before or after |
id |
java.lang.String |
Account ID of the user to delete |
errors |
java.util.List |
Initially, this value is an empty list. The script may add java.lang.String objects to this list if any errors are found during processing. |
trace |
com.sun.idm.logging.trace.Trace |
Object used to trace execution. Scripts can use the methods of this class to make themselves “debuggable” in a customer environment. |
Any throw from within the script is considered a failure.
If the script encounters any errors, the script may add appropriate strings to the errors key. The presence of any items in the errors List is considered a deletion failure.
The actionContext map passed to the action contains the following entries:
Key |
Value Type |
Value Description |
---|---|---|
conn |
java.sql.Connection |
JDBC connection to the database |
adapter |
com.wavset.adapter.OracleERPResourceAdapter |
Adapter instance |
action |
java.lang.String |
The enableUser string |
timing |
java.lang.String |
Must be before or after |
id |
java.lang.String |
User account ID to enable |
errors |
java.util.List |
Initially, this value is an empty list. The script may add java.lang.String objects to this list if any errors are found during processing. |
trace |
com.sun.idm.logging.trace.Trace |
Object used to trace execution. Scripts can use the methods of this class to make themselves “debuggable” in a customer environment. |
Any throw from within the script is considered a failure.
If the script encounters any errors, the script may add the appropriate strings to the errors key. The presence of any items in the errors List is considered a failure.
The actionContext map passed to the action contains the following entries:
Key |
Value Type |
Value Description |
---|---|---|
conn |
java.sql.Connection |
JDBC connection to the database |
adapter |
com.wavset.adapter.OracleERPResourceAdapter |
Adapter instance |
action |
java.lang.String |
The disableUser string |
timing |
java.lang.String |
Must be before or after |
id |
java.lang.String |
User account ID to disable |
errors |
java.util.List |
Initially, this value is an empty list. The script may add java.lang.String objects to this list if any errors are found during processing. |
trace |
com.sun.idm.logging.trace.Trace |
Object used to trace execution. Scripts can use the methods of this class to make themselves “debuggable” in a customer environment. |
Any throw from within the script is considered a failure.
If the script encounters any errors, the script may add the appropriate strings to the errors key. The presence of any items in the errors List is considered a failure.
The getUser action is useful in situations where you need to retrieve additional custom account attributes from the database beyond those retrieved by the standard adapter. To enable this action, specify the name of this resource action by setting the resource parameter labeled GetUser After Action.
The actionContext map passed to the action contains the following entries:
Any throw from within the script is considered a failure.
If the script encounters any errors, it may add appropriate strings to the errors key. The presence of any items in the errors List is considered a fetch failure.
This section provides information about supported connections and privilege requirements.
Waveset can use one of the following drivers to communicate with the Oracle adapter:
JDBC thin driver
JDBC OCI driver
Third-party drivers
Since the Oracle Application stored procedures require unencrypted passwords to be passed to some of the stored procedures used for provisioning, you should implement encrypted communications between Waveset and the Oracle application resource.
Please read the Oracle publication Oracle Advanced Security Administrators Guide and your JDBC driver’s documentation to validate the level of support for encryption that your specific version of Oracle RDBMS and driver provides.
Oracle E-Business Suite requires access to the following tables and stored procedures.
The administrator must be able to run the select command for all tables. In addition, the administrator must be able to update the apps.fnd_user table.
Tables |
Stored Procedures |
---|---|
apps.ak_attributes apps.ak_attributes_tl apps.ak_web_user_sec_attr_values apps.fnd_application apps.fnd_application_tl apps.fnd_application_vl apps.fnd_profile apps.fnd_responsibility apps.fnd_responsibility_vl apps.fnd_security_groups apps.fnd_security_groups_tl |
apps.app_exception.raise_exception apps.fnd_global.apps_initialize apps.fnd_global.user_id apps.fnd_message.get apps.fnd_message.get_token apps.fnd_message.set_name apps.fnd_message.set_token apps.fnd_profile.get apps.fnd_user_pkg.AddResp apps.fnd_user_pkg.CreateUser apps.fnd_user_pkg.DisableUser |
apps.fnd_security_groups_vl apps.fnd_user apps.fnd_user_resp_groups apps.icx_parameters |
apps.fnd_user_pkg.DelResp apps.fnd_user_pkg.UpdateUser apps.fnd_user_pkg.user_synch apps.fnd_user_pkg.validatelogin apps.fnd_user_resp_groups_api.assignment_exists apps.fnd_user_resp_groups_api.insert_assignment apps.fnd_user_resp_groups_api.update_assignment apps.fnd_web_sec.change_password apps.fnd_web_soc.create_user apps.fnd_web_sec.validation_login apps.icx_user_sec_attr_pub.create_user_sec_attr apps.icx_user_sec_attr_pub.delete_user_sec_attr |
The adapter might access additional tables and stored procedures. Refer to the Oracle E-business Suite documentation for additional information.
Oracle states that the Oracle EBS system, including the fnd_user_pkg stored procedures, were designed to be used to administer the ORACLE EBS system as the APPS user. Oracle does NOT recommend creating an alternate administrative user. However, if you need to manage Oracle EBS with a user other than APPS, contact Oracle for guidance.
The alternate administrative user must be granted the same access as the APPS user has to all Oracle data, including tables, views, and stored procedures.
The user will also need synonyms set up so the user will have access to the tables that the APPS user has access to. If a different user is used and the appropriate grants and synonyms have not been created for the user, the following error might be encountered:
Error: ORA-00942: table or view does not exist
Add the appropriate grants and synonyms to correct the error. A sample SQL*Plus script is located in the following directory:
$WSHOME/sample/other/CreateLHERPAdminUser.oracle.
You can modify this script as necessary and use it to create an alternative Oracle EBS Admin user. Usage instructions are documented in the comments at the beginning of the script.
For pass-through authentication only, authority is needed to run the following SQL command:
create or replace function wavesetValidateFunc1 (username IN varchar2, password IN varchar2) RETURN varchar2 IS ret_val boolean; BEGIN ret_val := apps.FND_USER_PKG.ValidateLogin(username, password); IF ret_val = TRUE THEN RETURN ’valid’; ELSE RETURN NULL; END IF; END wavesetValidateFunc1;
The following table summarizes the provisioning capabilities of this adapter. The adapter does not issue any direct table updates during any supported provisioning operation.
Feature |
Supported? |
---|---|
Create a user. |
Yes |
Set start and end dates. |
Yes |
Set password access limits. |
Yes |
Set password lifespan time limit. |
Yes |
Change or reset password. |
Yes. |
Set Employee ID (HRMS link) for a user record. |
Yes |
Set the Email and Fax attributes of a user account. |
Yes |
Set the Customer ID or Supplier ID for a user record. |
Yes |
Assign one or more direct responsibilities to a user. |
Yes |
Assign Securing Attributes to a User account. |
Yes |
Remove or edit Assigned Responsibilities for a User. |
Yes. Note: Responsibilities are end-dated (disabled), rather than deleted. |
Disable an account. |
Yes |
Re-enable an account. |
Yes |
Delete an account. |
Yes. The account is end-dated (disabled). |
Pass-through authentication. |
Yes |
Data Loading Methods: Reconcile, Extract to File, Load from Resource, Load from file. |
Reconciliation Load from resource |
Provision to FND_USER table. |
Yes |
Provision to Oracle HRMS. |
No |
Link FND_USER record to Oracle HRMS on create. |
Yes |
Manage menu definitions or individual responsibilities. |
No |
Assign indirect responsibilities. |
No. Indirect responsibilities can be read, but not assigned. |
Set User Session Limits (ICX:Session Timeout, ICX:Limit Time, ICX: Limit Connects). |
No |
RBAC objects and assignments. |
No |
Use grants of permission sets on specific data objects, data objects instances, or instance sets. |
No |
Before/After actions. |
Yes |
Rename accounts. |
No |
The following table lists the default Oracle ERP account attributes. All attributes are optional.
Resource User Attribute |
Data Type |
Description |
---|---|---|
owner |
string |
The administrator who created the account. |
start_date |
string |
The date the account is effective. |
end_date |
string |
The date the account expires. Set the date to a previous date to disable an account. Specify a null value to indicate no expiration date. Use the sysdate or SYSDATE keyword with end-date to specify an expiration date for a user with the local time of the Oracle EBS server. |
description |
string |
A description of the user, such as the full name. |
password_date |
string |
The datestamp of the last password change. The Oracle ERP adapter can use this datestamp when evaluating the password_lifespan_days attribute value. For example if you set the password_lifespan_days attribute to 90, then Oracle ERP will calculate 90 days out from the last password change date (password_date) to determine if the password is expired. Each time the Oracle ERP adapter performs a password change, it will set the password_date to the current date. |
password_accesses_left |
string |
The number of times the user can use the current password. |
password_lifespan_accesses |
string |
The number of accesses over the life of the password |
password_lifespan_days |
string |
The total number of days the password is valid. |
employee_id |
string |
Identifier of employee to whom the application username is assigned. |
employee_number |
string |
Represents an employee_number from the per_people_f table. When you enter a value on create, the adapter tries to look up a user record in the per_people_f table, retrieve the person_id into the create API, and insert the person_id into the fnd_user table’s employee_id column. If no employee_number is entered on create, no linking is attempted. If you enter an employee_number on create and that number is not found, then the adapter throws an exception. The adapter will try to return the employee_number on a getUser, if employee_number is in the adapter schema. |
person_fullname |
string |
The full name of the user. |
npw_number |
string |
Contingent worker number. It represents an npw_number from the per_people_f table. When you enter a value on create, the adapter tries to lookup a user record in the per_people_f table, retrieve the person_id into the create API, and insert the person_id into the fnd_user table’s employee_id column. If no npw_number is entered on create, no linking is attempted. If you enter an npw_number on create and that number is not found, then the adapter throws an exception. The adapter will try to return the npw_number on a getUser, if npw_number is in the adapter schema. Note: The employee_number attribute and npw_number attribute are mutually exclusive. If both are entered on create, employee_number takes precedence. |
email_address |
string |
The e-mail address of the user. |
fax |
string |
The fax number of the user. |
customer_id |
string |
The customer ID of the user. |
supplier_id |
string |
The supplier ID of the user. |
responsibilities |
string |
The names of the responsibilities assigned to the user. Valid for Oracle EBS 11.5.9 only. Use the sysdate or SYSDATE keyword with to_date to specify an expiration date for a responsibility with the local time of the Oracle EBS server. |
responsibilityKeys |
string |
The keys associated with the user’s list of responsibilities. |
securingAttrs |
string |
Adds supports for securing attributes. |
expirePassword |
boolean |
Indicates whether the password will be expired. |
directResponsibilities |
string |
Returns the user’s direct responsibilities. Valid for 11.5.10 only. |
indirectResponsibilities |
string |
Returns the user’s indirect responsibilities. Valid for 11.5.10 only. |
The Oracle ERP adapter allows you to add several read-only attributes that Waveset can use to audit changes to responsibilities. The values returned in the auditorResps attribute are the active responsibilities for that user. Except for auditorObject, all other attributes listed in the following table are aggregates of each responsibility’s sub-items, minus any menu and function exclusions that may exist.
The auditorObject attribute may be added as well. See Auditing Responsibilities for details about this atttribute.
The following table lists attributes that may be added to the schema map.
Attribute |
Description |
---|---|
auditorResps |
List of a user’s Active Responsibilities. |
formIds |
Concatenates all Form IDs. Includes values returned by readOnlyFormIds and readWriteOnlyFormIds. |
formNames |
Concatenates all Form Names. Includes values returned by readOnlyFormNames and readWriteOnlyFormNames/ |
functionIds |
Concatenates all Function IDs |
functionNames |
Concatenates all Function Names |
menuIds |
Concatenates all Menu IDs |
readOnlyFormIds |
Concatenates all Read-Only Forms IDs |
readOnlyFormNames |
Concatenates all Read-Only Form Names |
readOnlyFunctionNames |
Concatenates all Read-Only Function Names |
readOnlyUserFormNames |
Concatenates all Read-Only User Form Names |
readWriteOnlyFormIds |
Concatenates all Read/Write-Only Forms Ids |
readWriteOnlyFormNames |
Concatenates all Read/Write-Only Form Names |
readWriteOnlyFunctionNames |
Concatenates all Read/Write-Only Function Names |
readWriteOnlyUserFormNames |
Concatenates all Read/Write-Only User Form Names |
userFormNames |
Concatenates all User Form Names. Includes values returned by readOnlyUserFormNames and readWriteOnlyUserFormNames/ |
userFunctionNames |
Concatenates all User Function Names |
userMenuNames |
Concatenates all User Menu Names. |
The Oracle ERP adapter can support any additional custom attributes by using before and after actions for create and update, and by using a custom getUser action. See Using Resource Actions for more information.
Waveset supports the following native objects:
Resource Object |
Features Supported |
Objects Managed |
---|---|---|
responsibilityNames |
Update |
name, userMenuNames, menuIds, userFunctionNames, functionIds, formIds, formNames, userFormNames, readOnlyFormIds, readWriteOnlyFormIds, readOnlyFormNames, readOnlyUserFormNames, readWriteOnlyFormNames, readWriteOnlyUserFormNames, functionNames, readOnlyFunctionNames, readWriteOnlyFunctionNames |
$accountId$
None
OracleERPUserForm.xml
Use the Waveset debug pages to set trace options on the following classes:
com.waveset.adapter.OracleERPResourceAdapter
com.waveset.adapter.JdbcResourceAdapter
com.waveset.adapter.JActionUtil (if using before/after actions)