Efficiently Load bulk Lookup Values using ORACLE FUSION HCM’s New feature

Flexfields, Value sets, and Lookups are key components to the extensibility of the HCM Cloud applications. In almost all implementations, situations were faced where lookup values need to be created in value sets or values were added up to the list delivered in the application.

Entering these values manually consume time, effort, and could also result in mistakes leading to other application issues. Moreover, the data entry must be repeated in multiple environments such as Development, Test and Production.

Oracle’s File Import feature provides a fast, easy, and efficient mechanism to load the values for lookups and value sets, helping you overcome the hassles and issues that come with manual data entry.

Get the most out of this feature to import the data in minutes. If you have the lookup data and list of values in an excel sheet, importing it into the application is easy. Follow the steps given below:

Step 1: Create Lookup Type

From setup task = Manage Common Lookups and create the Lookup Type

Step 2: Prepare Data in Excel File

Columns: (Column Names should be same as below – case sensitive and no spaces)

LookupType               –           Mandatory

LookupCode              –          Mandatory

DisplaySequence       –           Optional

EnabledFlag               –          Mandatory

StartDateActive         –          Optional

EndDateActive           –          Optional

Meaning                     –           Mandatory

Description                –          Optional

Tag                              –           Optional

Step 3: Prepare the file for Import

Copy the contents of the excel file in Notepad++ editor (or Text Pad)

Press ‘Ctrl + F’

Replace Tab Spaces with Pipe ‘|’ symbol

Once you perform the ‘Replace All’, you will get the dat file content

Save the file as .dat – Example: grades.dat

Step 4: Import the file

From the Home page/ Navigator, access Tools > File Import and Export

Under the Search Results section, select the + button to Upload

In the Upload File window, browse to and select the .dat file you saved

For Account, drop-down and select hcm/dataloader/import

Step 5: Import the lookup values

Go to the setup task = Manage Common Lookups, and search for the lookup type you created in Step 1

Under the Search Results section, Click on Actions drop down and select Import

In the Import Common Lookups window:

Select Account = hcm/dataloader/import

Lookup Code File = <enter the name of the .dat file you imported>

Click Upload

Once completed, click OK. Click Save in the Common Lookups page.

In the Lookup Codes region, verify that the lookup codes have been imported.

About the Author

The author S. Thanigaivelan is an HCM Practice Manager with 4iApps and has over 16 years of experience in Oracle ERP (EBS and Fusion) Implementations, Support, and Training. He is a Subject Matter Expert for all the Fusion Talent Management applications, having worked closely with the cloud product development team at Oracle during his time with the Oracle Cloud support organization. He also has strong expertise in common application features such as Fusion HCM Security, Data Loading using HDL and HSDL, and Business Intelligence.