Jul 29, 2018

Oracle HCM Cloud: How to Mass update Work Email Addresses and User Names Format



This is my first blog post on Oracle HCM Cloud related topics. Hope you continue to find them useful. Use comments section below to let me know. 😊

When a new hire is created, or Employee records are converted for the 1st time using HCM Data Loader (HDL), username creation is based on the settings in Security Console -> Administration -> General tab.





In this screenshot, username creation is set to automatic in the format FirstName.LastName. This setting is applicable to all channels including employee conversions using HDL, hiring a pending worker, entering a new hire record or creating new hire using REST API. Changing this setting to “Email” will mean username format creation by using work email address or personal email address in case of missing work email or FirstName.LastName in case of no email address. This behavior is for future records and existing username formats will remain as they are.

This blog post explains the steps involved in changing the username format from FirstName.LastName to Email format both for existing records. A typical business use case for doing this would be, while enabling Active Directory Bridge (also known as AD bridge) to match user name formats in Enterprise Active Directory. Another use case would be, for Security Administrators to bulk update missing work email addresses and clean up usernames without reassigning the existing roles.

Step1) Obvious step, change the email format to the desired.





Step2) Identify and prepare the list of employees to update using OTBI report with subject area “Workforce Management – Person Real Time”

Step3) Use HDL to update the work email addresses on employee records, using the following columns

UserKey Apporach:

File Name: Worker.dat

METADATA|PersonEmail|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|DateFrom|DateTo|EmailType|EmailAddress|PrimaryFlag

MERGE|PersonEmail|HR_DATA|111111_EMAIL_W1|111111|2018/01/01|4712/12/31|W1|work.email1@enterprise.com|Y

MERGE|PersonEmail|HR_DATA|222222_EMAIL_W1|222222|2017/01/01|4712/12/31|W1|work.email2@enterprise.com|Y

MERGE|PersonEmail|HR_DATA|333333_EMAIL_W1|333333|2016/01/01|4712/12/31|W1|work.email3@enterprise.com|Y

SourceSystemId - Use the format from original loads (format used during initial conversions). If you are not sure about this format, try GUID approach as described below.
PersonId(SourceSystemId) - use “Employee Number” 
DateFrom - use “Enterprise hire Date”

GUID Approach:
If you above format is coming out as error similar to "A parent for this record was not found", try this GUID method to locate the exact records.

METADATA|PersonEmail|EmailAddressId|PersonId|DateFrom|DateTo|EmailType|EmailAddress|PrimaryFlag

MERGE|PersonEmail|HR_DATA|300097545|4005984513|2018/01/01|4712/12/31|W1|work.email1@enterprise.com|Y

MERGE|PersonEmail|HR_DATA|300785431|4005391594|2017/01/01|4712/12/31|W1|work.email2@enterprise.com|Y


MERGE|PersonEmail|HR_DATA|300037898|4005788205|2016/01/01|4712/12/31|W1|work.email3@enterprise.com|Y

Alternate Approach:
Well, that isn't helping either? Try Spreadsheet loader by creating a new spreadsheet template using My Client Groups -> Data Exchange -> tasks -> Manage Spreadsheet Templates area.

Business Object: Worker
Supported Action: Create and update

Select the following columns:



Preview template to download excel and enter details:
Note - "Date From" here is enterprise hire date.





Step4) Use HDL to submit request to update user name

File Name: User.dat

METADATA|User|PersonNumber|Username|CredentialsEmailSent
MERGE|User|111111|work.email1@enterprise.com|Y
MERGE|User|222222|work.email2@enterprise.com|Y
MERGE|User|333333|work.email3@enterprise.com|Y

Step5) Submit a scheduled job “Send Pending LDAP Requests” from Navigator -> Scheduled Processes -> Schedule New Process