Lookups are an essential functionality in Oracle Fusion, used for various purposes. Often, there is a need to load a large number of lookup codes into the system. Flexfields, Value sets, and Lookups are key components to the extensibility of the Oracle 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.
Manually entering these values is time-consuming, error-prone, and can lead to issues in other applications. Additionally, this process needs to be repeated across multiple environments like Development, Test, and Production.
Oracle’s File Import feature offers a quick and efficient solution to load lookup values and value sets, eliminating the challenges of manual data entry.
How to bulk import Lookup Types and Lookup Codes using File Based Loader ?
Below are the steps to load lookup codes.
Step 1: Create Lookup Type
From setup task = Manage Common Lookups or Manage Standard 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)
| Column Name | Parameter |
|---|---|
| LookupType | Mandatory |
| LookupCode | Mandatory |
| DisplaySequence | Optional |
| EnabledFlag | Mandatory (Y/N) |
| 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: newlookup.dat
Step 4: Upload the file to UCM Import the file
From the Home page/ Navigator, access Tools > File Import and Export
Navigation: Tools->File Import 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: Navigate to Common or Standard Lookups
Create lookup type as per requirement.(you can also create lookup type using import functionality)
Step 6: Import the lookups
Go to the setup task = Manage Common Lookups, and search for the lookup type you created in Step 1. Select the lookup code, Actions->Import.
Under the Search Results section, Click on Actions drop down and select Import.
In the Import Common Lookups window, Provide the details and click on Upload button.
Select Account = hcm/dataloader/import
Lookup Code File = <enter the name of the .dat file you imported> in our case newlookup.dat
Click Upload. Check the log once the process is complete.
Step 7: Verify Lookup
Once completed, click OK. Click Save in the Common Lookups page.
In the Lookup Codes region, verify that the lookup codes have been imported.