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>
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.