13 Synchronizing with Oracle Human Resources

If you use Oracle Human Resources as the primary repository for employee data in your enterprise, then you may need to synchronize data between it and the Oracle back-end directory. The Oracle Human Resources connector enables you to do this.

This chapter introduces the Oracle Human Resources connector, and explains how to deploy it.

Topics:

• Introduction to Synchronization with Oracle Human Resources

• What are the Data you can Import from Oracle Human Resources?

• Managing Synchronization Between Oracle Human Resources and the Oracle Back-end Directory

• Understanding the Synchronization Process between Oracle Human Resources and the Oracle Back-End directory

• Bootstrapping the Oracle Back-end Directory from Oracle Human Resources

  Note:

  If you are synchronizing with an Oracle Human Resources environment that involves the Oracle E-Business Suite, Oracle recommends using the Oracle E-Business Suite integration solution that Oracle Directory Integration Platform supports, which is described in Integration of Provisioning Data with Oracle E-Business Suite.

  See Also:

  The Release Notes for your Oracle back-end directory to find out which release of Oracle Human Resources can be synchronized with your Oracle back-end directory.

13.1 Introduction to Synchronization with Oracle Human Resources

The Oracle Human Resources connector enables you to import a subset of employee data from Oracle Human Resources into the Oracle back-end directory. It includes both a prepackaged integration profile and an Oracle Human Resources agent that handles communication with the Oracle back-end directory. You can customize the prepackaged integration profile to meet your deployment needs using Oracle Enterprise Manager Fusion Middleware Control.

You can schedule the Oracle Human Resources connector to run at any time, configuring it to extract incremental changes from the Oracle Human Resources system. You can also set and modify mapping between column names in Oracle Human Resources and attributes in the Oracle back-end directory.

13.2 What are the Data you can Import from Oracle Human Resources?

This section describes the attributes that you can Import from Oracle Human Resources.

Table 13-1 lists the tables in the Oracle Human Resources schema. If you choose, you can import most of these attributes into Oracle Internet Directory.

Table 13-1 Tables in Oracle Human Resources Schema

Table Name	Alias Used in the Connector Config Info Field
PER_PEOPLE_F	PER
PER_ADDRESSES	PA
PER_PERIODS_OF_ SERVICE	PPS
PER_PERSON_TYPES	PPT

All of these tables are visible if the login to the Oracle Human Resources database is done with the apps account.

Because attributes can be added or deleted at run time from the configuration file, the Oracle Human Resources connector dynamically creates a SQL statement that selects and retrieves only the required attributes.

Table 13-2 shows some of the fields in the Oracle Human Resources user interface. These fields appear when you add or modify employee data.

Table 13-2 Fields in the Oracle Human Resources User Interface

ATTRIBUTE NAME	DESCRIPTION	FORM/CANVAS/FIELD_NAME
LAST_NAME	Last name of the person	People/Name/Last
FIRST_NAME	First name of the person	People/Name/First
TITLE	Title of the person	People/Name/Title
SUFFIX	Suffix—for example, Jr, Sr, Ph.D.	People/Name/Suffix
MIDDLE_NAME	Middle name	People/Name/Middle
SEX	Sex	Gender List box
START_DATE	Hiring date	People/Hire Date
DATE_OF_BIRTH	Date of birth	People/Personal Information/Birth Date
MARITAL_STATUS	Marital status	People/Personal Information/Status
NATIONAL_ INDENTIFIER	Social security number for US residents	People/Identification/Social Security
EMPLOYEE_NUMBER	Employee number	People/Identification/Employee
REGISTERD_ DISABLED_ FLAG	Indicator that the employee has a disability	People/Personal Information/Has Disability
EMAIL_ADDRESS	Electronic mail address	People/Personal Information/EMail
OFFICE_NUMBER	Office location	People/Office Location Info/Office
MAILSTOP	Mail delivery stop	People/Office Location Info/Mail Stop
INTERNAL_LOCATION	Location	People/Office Location Info/Location
ADDRESS_LINE1	Address line 1	Personal Address Information/Address line 1
ADDRESS_LINE2	Address line 2	Personal Address Information/Address line 2
ADDRESS_LINE3	Address line 3	Personal Address Information/Address line 3
TOWN_OR_CITY	Town or city	Personal Address Information/City
REGION_1	First region	Personal Address Information/County
REGION_2	Second region	Personal Address Information/State
POSTAL_CODE	Postal code	Personal Address Information/Zip Code
COUNTRY	Country	Personal Address Information/Country
TELEPHONE_ NUMBER_1	First telephone number	Personal Address Information/Telephone
TELEPHONE_ NUMBER_2	Second telephone number	Personal Address Information/Telephone2

13.3 Managing Synchronization Between Oracle Human Resources and the Oracle Back-end Directory

Use the steps in the following order to manage synchronization between Oracle Human Resources and the Oracle Back-end Directory.

Topics:

• Configure a Directory Integration Profile for the Oracle Human Resources Connector

• About Oracle Human Resources Attributes to be Synchronized with the Oracle Back-end Directory

• About Mapping Rules for the Oracle Human Resources Connector

• Setting Up Synchronization from Oracle Human Resources to the Oracle Back-end Directory

13.3.1 Configure a Directory Integration Profile for the Oracle Human Resources Connector

To configure the prepackaged integration profile that is installed with the Oracle Human Resources connector, refer to Managing Directory Synchronization Profiles.

For some of the parameters in the prepackaged integration profile, you must specify values specific to integration with the Human Resources Connector. The parameters specific to the Human Resources Connector are listed in Table 13-3.

Table 13-3 Attributes Specific to Oracle Human Resources Connector Integration Profile

Attribute	Description
Profile Name (odip.profile.name)	Unique name by which the connector is identified in the system, used as an RDN component of the DN that identifies the integration profile. The name can contain only alpha-numeric characters. This attribute is mandatory and not modifiable. The default name is OracleHRAgent.
Synchronization Mode (odip.profile.syncmode)	The direction of synchronization between the Oracle back-end directory and a connected directory. IMPORT indicates importing changes from a connected directory to the Oracle back-end directory. EXPORT indicates exporting changes from the Oracle back-end directory to a connected directory. The default is IMPORT. This attribute is mandatory and modifiable. Note: In Oracle Internet Directory 11g Release 1 (11.1.1), only import operations for Oracle Human Resources are supported.
Execution Information
Agent Execution Command (odip.profile.agentexecommand)	Connector executable name and argument list used by the directory integration server to execute the connector. This attribute is mandatory and modifiable. The default is: odihragent OracleHRAgent connect=hrdb \ login=%odip.profile.condiraccount \ pass=%odip.profile.condirpassword \ date=%orclODIPLastSuccessfulExecutionTime \ You must set the value in the argument connect=hrdb to the connect string of the Oracle Human Resources system database.
Connected Directory Account (odip.profile.condiraccount)	Valid user account in the connected directory to be used by the connector for synchronization. For the Human Resources Agent, it is a valid user identifier in the Oracle Human Resources database. See Also: Synchronizing with Oracle Human Resources for typical usage of passing it in the command-line
Advanced Configuration Information (odip.profile.configfile)	Any configuration information that you want the connector to store in the Oracle back-end directory. It is passed by the directory integration server to the connector at time of connector invocation. The information is stored as an attribute and the directory integration server does not have any knowledge of its content. The value stored in this attribute represents (for Oracle Human Resources connector) all attributes that need to be synchronized from Oracle Human Resources. See Also: "About Oracle Human Resources Attributes to be Synchronized with the Oracle Back-end Directory" This attribute is mandatory for the Oracle Human Resources connector.
Connected Directory URL (odip.profile.condirurl)	The host and port details of the connected directory. They must be entered in this format: host:port:sid.
Interface Type (odip.profile.interface)	The interface used for data transfer. Because it is in the form of a tagged file, it is set to TAGGED. Note: You should not modify this attribute for Oracle Human Resources profile.
Mapping Information
Mapping Rules (odip.profile.mapfile)	Attribute for storing the mapping rules. Store the mapping rules in a file by using Oracle Enterprise Manager Fusion Middleware Control. See Managing Directory Synchronization Profiles for information on using Oracle Enterprise Manager Fusion Middleware Control. This attribute is mandatory for Oracle Human Resources and is modifiable. See Also: "understanding-oracle-directory-synchronization-service.html#GUID-DC80ED44-592F-435D-8B19-D4FE32A0DE46__I122151" "Configuring Mapping Rules"
Connected Directory Matching Filter (odip.profile.condirfilter)	This is not used in Oracle Human Resources connectivity.
OID Matching Filter (odip.profile.oidfilter)	This attribute names an LDAP filter that is used to search for a target entry in the Oracle back-end directory. The Oracle Directory Integration Platform uses this filter to find out what kind of LDAP operation it needs to do to synchronize. It is of the form employeenumber=%. It is optional and modifiable.
Status Information
OID Last Applied Change Number (odip.profile.lastchgnum)	This attribute, standard for all export profiles, does not apply to Oracle Human Resources synchronization.

13.3.2 About Oracle Human Resources Attributes to be Synchronized with the Oracle Back-end Directory

The default Oracle Human Resources profile provides a default list of attributes to be synchronized from Oracle Human Resources to the Oracle back-end directory. You can customize this list, adding attributes to it or removing attributes from it.

• Configure the List of Attributes to be Synchronized with the Oracle Back-end Directory

• Modifying Additional Oracle Human Resources Attributes for Synchronization

• Excluding Oracle Human Resources Attributes from Synchronization

• Configure a SQL SELECT Statement in the Configuration File to Support Complex Selection Criteria

13.3.2.1 Configure the List of Attributes to be Synchronized with the Oracle Back-end Directory

The default attribute list is stored in the orclodipAgentConfigInfo attribute as part of the integration profile.The configuration information is also available in the file oraclehragent.cfg.master that is located under the $ORACLE_HOME/ldap/odi/conf directory.

Note:

Do not modify the oraclehragent.cfg.master file; it serves as a backup.

Table 13-4 describes columns in the default list of Oracle Human Resources attributes.

Table 13-4 Oracle Human Resources Attributes Synchronized with the Oracle Back-end Directory by Default

Column	Description
ATTRNAME	The output tag generated in the output data file.
COLUMN_NAME	Database column name from where to obtain this value.
TABLE_NAME	Database table name from where to obtain this value.
FORMAT	The column data type of this attribute (ASCII, NUMBER, DATE).
MAP	Indicator of whether to extract this attribute from Oracle Human Resources. A value of Y indicates that it will be extracted, and a value of N indicates that it will not be.

The oraclehragent.cfg.master file contains the following:

ATTRNAME:COLUMN_NAME:TABLE_NAME:FORMAT:MAP
PersonId:person_id:PER:NUMBER:Y
PersonType:person_type_id:PER:NUMBER:Y
PersonTypeName:system_person_type:PPT:ASCII:Y
LastName:last_name:PER:ASCII:Y
StartDate:start_date:PER:DATE:Y
BirthDate:date_of_birth:PER:DATE:Y
EMail:email_address:PER:ASCII:Y
EmployeeNumber:employee_number:PER:NUMBER:Y
FirstName:first_name:PER:ASCII:Y
FullName:full_name:PER:ASCII:Y
knownas:known_as:PER:ASCII:Y
MaritalStatus:marital_status:PER:ASCII:Y
middleName:middle_names:PER:ASCII:Y
country:country:PA:ASCII:Y
socialsecurity:national_identifier:PER:ASCII:Y
Sex:sex:PER:ASCII:Y
Title:title:PER:ASCII:Y
suffix:suffix:PER:ASCII:Y
street1:address_line1:PA:ASCII:Y
zip:postal_code:PA:ASCII:Y
Address1:address_line1:PA:ASCII:Y
Address2:address_line2:PA:ASCII:Y
Address3:address_line3:PA:ASCII:Y
TelephoneNumber1:telephone_number_1:PA:ASCII:Y
TelephoneNumber2:telephone_number_2:PA:ASCII:Y
TelephoneNumber3:telephone_number_3:PA:ASCII:Y
town_or_city:town_or_city:PA:ASCII:Y
state:region_2:PA:ASCII:Y
Start_date:effective_start_date:PER:DATE:Y
End_date:effective_end_date:PER:DATE:Y
per_updateTime:last_update_date:PER:DATE:Y
pa_updateTime:last_update_date:PA:DATE:Y

13.3.2.2 Modifying Additional Oracle Human Resources Attributes for Synchronization

To include additional Oracle Human Resources attributes for synchronization, follow these steps:

1. Copy the oraclehragent.cfg.master file and name it anything other than Agent_Name.cfg. This is because the Oracle Directory Integration Platform generates a configuration file with that name, using it to pass the configuration information to the Oracle Human Resources agent at run time.
2. Include an additional Oracle Human Resources attribute for synchronization by adding a record to this file.

   To do this, you need this information:

   • Table name in the database from which the attribute value is to be extracted. These tables are listed in Table 13-1. The file uses abbreviated names for the four tables used in the synchronization.

   • Column name in the table.

   • Column data type. Valid values are ASCII, NUMBER, and DATE.

   You also need to assign an attribute name to the column name. This acts as the output tag that is used to identify this attribute in the output file. This tag is used in the mapping rules to establish a rule between the Oracle Human Resources attribute and the Oracle back-end directory attribute.

   You must also ensure that the map column—that is, the last column in the record—is set to the value Y.

   Note:

   If you add a new attribute in the attribute list, then you must define a corresponding rule in the orclodipAttributeMappingRules attribute. Otherwise the Oracle Human Resources attribute is not synchronized with the Oracle back-end directory, even if it is being extracted by the Oracle Human Resources connector.

13.3.2.3 Excluding Oracle Human Resources Attributes from Synchronization

To exclude an Oracle Human Resources attribute that is currently being synchronized with Oracle Internet Directory, do the following:

1. Copy the oraclehragent.cfg.master file and name it anything other than Agent_Name.cfg. This is because the directory integration server generates a configuration file with that name, using it to pass the configuration information to the Oracle Human Resources connector at run time.
2. Do one of the following:

   • Comment out the corresponding record in the attribute list by putting a number sign (#) in front of it.

   • Set the value of the column map to N.

13.3.2.4 Configure a SQL SELECT Statement in the Configuration File to Support Complex Selection Criteria

If the previous supporting attribute configuration is not sufficient to extract data from the Oracle Human Resources database, then the Oracle Human Resources agent can also execute a preconfigured SQL SELECT statement in the configuration file. There is a tag to indicate this in the configuration file, namely, a [SELECT] in the configuration file.

The following example shows a sample select statement to retrieve some information from the Oracle Human Resources database. Note that only the SQL statement should follow the [SELECT] tag. The BINDVAR bind variable needs to be there to retrieve incremental changes. The substitutes passes this value (the time stamp) to the Oracle Human Resources connector.

All the column expressions retrieved in the SELECT statement must have column names—for example, REPLACE(ppx.email_address),'@ORACLE.COM','') is retrieved as EMAILADDRESS. The Oracle Human Resources connector writes out EMAILADDRESS as the attribute name in the output file with its value as the result of the expression REPLACE(ppx.email_address),'@ORACLE.COM'''.

The following is an example of a a SELECT statement in a configuration file.

[SELECT]

SELECT
     REPLACE(ppx.email_address,'@ORACLE.COM','') EMAILADDRESS ,
     UPPER(ppx.attribute26) GUID,
     UPPER(ppx.last_name) LASTNAME,
     UPPER(ppx.first_name) FIRSTNAME,
     UPPER(ppx.middle_names) MIDDLENAME,
     UPPER(ppx.known_as) NICKNAME,
     UPPER(SUBSTR(ppx.date_of_birth,1,6)) BIRTHDAY,
     UPPER(ppx.employee_number) EMPLOYEEID,
     UPPER(ppos.date_start)  HIREDATE
FROM
   hr_organization_units hou,
   per_people_x ppx,
   per_people_x mppx,
   per_periods_of_service ppos
  WHERE
      pax.supervisor_id  = mppx.person_id(+)
  AND pax.organization_id = hou.organization_id(+)
  AND ppx.person_id = ppos.person_id
  AND ppx.person_id = pax.person_id
  AND ppos.actual_termination_date IS NULL
  AND UPPER(ppx.current_employee_flag) = 'Y'
  AND ppx.last_update_date >= (:BINDVAR,'YYYYMMDDHH24MISS')

13.3.3 About Mapping Rules for the Oracle Human Resources Connector

Attribute mapping rules govern how the Oracle Directory Integration Platform converts attributes between Oracle Human Resources and Oracle Internet Directory. You can customize the mapping rules you want the Oracle Directory Integration Platform to use.

The Oracle Human Resources agent profile has a default mapping file with a set of mapping rules in the attribute orclodipAttributeMappingRules. This information is also stored in the file named oraclehragent.map.master located under the $ORACLE_HOME/ldap/odi/conf directory.

Note:

Do not modify the oraclehragent.map.master file. It serves as a backup.

See Also:

"understanding-oracle-directory-synchronization-service.html#GUID-DC80ED44-592F-435D-8B19-D4FE32A0DE46__I122151" for the contents of the oraclehragent.map.master file and a description of the format of the mapping rules records

13.3.4 Setting Up Synchronization from Oracle Human Resources to the Oracle Back-end Directory

You must set up synchronization from Oracle Human Resources to the Oracle back-end directory.

To do so, perform the following steps:

1. Set the ORACLE_HOME environment variable in the environment where the wls_ods1 managed server is running.
2. Ensure that the Oracle Human Resources connector and the directory integration server are installed on the host from which you want to run the Oracle Human Resources connector.
3. Ensure that you have the information for accessing the Oracle Human Resources system, including:

   • Connect string to the Oracle Human Resources system database

   • Access account

   • Password

4. Configure an integration profile for the Oracle Human Resources connector, as described in "Configure a Directory Integration Profile for the Oracle Human Resources Connector". Ensure that all values in the integration profile are properly set, including:

   • Oracle Human Resources attribute list

   • Oracle Human Resources attribute mapping rules

   • Scheduling interval

5. Once everything is properly set, set the Profile Status attribute to ENABLE. This indicates that the Oracle Human Resources connector is ready to run.
6. Start the Oracle directory server and the Oracle Human Resources system if they are not already running on the respective hosts.
7. When everything is ready, start the directory integration server if it is not already running on this host.

13.4 Understanding the Synchronization Process between Oracle Human Resources and the Oracle Back-End directory

These section explains the synchronization process between Oracle Human Resources and the Oracle back-end directory.

Once the Oracle Human Resources system, Oracle back-end directory, and the Oracle Directory Integration Platform are running, and the Oracle Human Resources connector is enabled, the Oracle Directory Integration Platform automatically starts synchronizing changes from the Oracle Human Resources system into Oracle back-end directory. It follows this process:

1. Depending on the value specified in the Last Execution Time (orclodipLastExecutionTime) and the Scheduling Interval (orclodipschedulinginterval), the Oracle Directory Integration Platform invokes the Oracle Human Resources connector.

2. The Human Resources agent extracts:

   • All the changes from the Oracle Human Resources System based on the time specified in the orclodipLastSuccessfulExecutionTime attribute in the integration profile

   • Only the attributes specified in the orclodipAgentConfigInfo attribute in the profile

   It then writes the changes into the Oracle Human Resources import file, namely $ORACLE_HOME/ldap/odi/import/HR_Agent_Name.dat.

3. After the agent completes execution, it creates a data file that looks similar to the following:

   FirstName: John
   LastName: Liu
   EmployeeNumber: 12345
   Title: Mr.
   Sex: M
   MaritalStatus: Married
   TelephoneNumber: 123-456-7891
   Mail: Jliu@my_company.com
   Address: 100 Jones Parkway
   City: MyTown
   

4. The Oracle Directory Integration Platform imports the changes to the Oracle back-end directory by doing the following:

   • Reading each change record from the import file.

   • Converting each change record into an LDAP change entry based on the rules specified in the Mapping Rules (orclodipAttributeMappingRules) in the integration profile.

5. After importing all the changes to the Oracle back-end directory, Oracle Human Resources connector moves the import file to the archive directory, $ORACLE_HOME/ldap/odi/import/archive. The status attributes Last Execution Time (orclodipLastExecutionTime) and Last Successful Execution Time (orclodipLastSuccessfulExecutionTime) are updated to the current time.

   If the import operation fails, only the Last Execution Time (orclodipLastExecutionTime) attribute is updated, and the connector attempts to extract the changes from Human Resources system based on the Last Successful Execution Time (orclodipLastSuccessfulExecutionTime) attribute. The reason for failure is logged in the trace file in $ORACLE_HOME/ldap/odi/HR_Agent_Name.trc file.

13.5 Bootstrapping the Oracle Back-end Directory from Oracle Human Resources

You can use the Oracle Human Resources connector or external tools to bootstrap Oracle back-end directory from Oracle Human Resources.

To bootstrap Oracle back-end directory from Oracle Human Resources:

• Use the Oracle Human Resources connector. In the integration profile, set the orclodipLastSuccessfulExecutionTime attribute to a time before Oracle Human Resources was installed.

• Use external tools to migrate data from Oracle Human Resources into Oracle Internet Directory.