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.
Contents
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:
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“
Lookup Types File Upload
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.
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
Latest posts by Sricharan Monigari (see all)
- Renaming HCM Extracts is a possibility from 20D - November 24, 2020
- Sangam20 – Oracle Users Group Conference - November 23, 2020
- Passing Parameter values from Flow Instance to HDL Transformation Formula - November 23, 2020
- Solve manual FTP server update issue for HCM Extracts post every P2T refresh - November 16, 2020