How to Bulk Load Lookup Types and Lookup Codes
How to bulk load Lookup Types and Lookup Codes

 595 total views

In this article we will look into how to bulk load the Lookup Types and Lookup Codes into Fusion.

We do not have HDL for Lookups, so we have to use the file based approach listed below to load them.

Bulk Load Lookup Types:

If we have to load the Lookup Codes, first the Lookup Type should exist. If Lookup type already exists, then you can skip this step and proceed with Bulk load of Lookup Codes.

The file should be a pipe (|) delimited format with all required attributes having values in UTF-8 encoding format.

File Format:

Lookup Type File Attributes
Lookup Type File Attributes

SQL to get ModuleType and ModuleKey:

select a.lookup_type, b.MODULE_TYPE, b.MODULE_KEY
from fnd_lookup_types a, FND_APPL_TAXONOMY b
where 1 = 1
--and a.lookup_type=''
and a.module_id=b.module_id

Sample File:

LookupType|Meaning|Description|ModuleType|ModuleKey
ABC_NO_OT_JOBCODES|ABC_NO_OT_JOBCODES||APPLICATION|HXT
ABC_NO_OT_LOCATIONS|ABC_NO_OT_LOCATIONS||APPLICATION|HXT

We can prepare the sample file with the list of all Lookup Types that needs to be loaded. We will look into how to load this file in the later part of this article.

Bulk Load Lookup Codes:

In order to load the Lookup Codes, loading Lookup Types is the pre-requisite. So check if the Lookup Types already exist, else follow the above steps to create the file for Lookup Types.

The file should be a pipe (|) delimited format with all required attributes having values in UTF-8 encoding format.

File Format:

Lookup Code Attributes
Lookup Code Attributes

Sample File: (LookupCodes.csv)

LookupType|LookupCode|DisplaySequence|EnabledFlag|StartDateActive|EndDateActive|Meaning|Description|Tag
ABC_NO_OT_JOBCODES|Lighting director||Y|1/1/1901|31/12/4712|Lighting director||
ABC_NO_OT_LOCATIONS|New York||Y|1/1/1901|31/12/4712|New York||
ABC_NO_OT_LOCATIONS|New Jersey||Y|1/1/1901|31/12/4712|New Jersey||
ABC_NO_OT_LOCATIONS|California||Y|1/1/1901|31/12/4712|California||

The file extension could be .dat/.csv/.txt.. Anything works.

Now that we have the files ready for both Lookup Types and Lookup Codes, let’s look into how to load these files into Fusion.

First let’s upload these files to UCM.
Navigate to Tools > File Import and Export

Upload both of the LookupTypes.csv and LookupCodes.csv using the Account as “Setup/FunctionalSetupManager/Import

Lookup Types File Upload

Lookup Types File Upload
Lookup Codes File Upload
Lookup Codes File Upload

We can see the files are uploaded to UCM

Files uploaded to UCM
Files uploaded to UCM

Now, lets go to the last and important step to upload these files.

Navigate to Setup and Maintenance
Search for Manage Common Lookups task

Once you are on the Lookups page, click the Actions button under search results and click Import

Actions > Import
Actions > Import

Now, select the Account and copy paste the exact file names.

Copy Paste exact file names that were uploaded to UCM
Copy Paste exact file names that were uploaded to UCM

If you are only loading the Lookup Codes, then leave the Lookup Type File as Blank and vice versa.

Click on Upload button to upload the files. The files will be uploaded in Lookup Types followed by Lookup Codes sequence by default.

Download log file to check any errors
Download log file to check any errors

It will show successful message. However download the log file to check if there are any errors that occurred during processing.

Log file snapshot:

Log file showing no errors
Log file showing successes without any errors

Now we can search for the Lookup Types on “Manage Common Lookups” page to check if these values are loaded correctly or not.

Lookup Types and Lookup Codes got loaded successfully
Lookup Types and Lookup Codes got loaded successfully

Tip: Lookup Types should be loaded first followed by Lookup Codes. We can also use this approach to migrate the Custom Lookup Types and Lookup Codes during environment refresh or when migrating to Production.

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

Leave a Reply