How to Bulk Load Lookup Types and Lookup Codes

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

File Format:

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:

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“

We can see the files are 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

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

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.

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

Log file snapshot:

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

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.