Entering all the values of lookup type and code into the application manually will consume a significant effort during data migration. It may result in wrong data entry which could lead to other application issues. Apart from this, the data entry activity has to be repeated in other instances of applications.
Oracle fusion’s File import and export feature provides a faster way of loading the lookup type and codes which helps consultants overcome the issues that come with manual data entry.
Steps to load the value set using FBL:
Step 1: Prepare the Import file with Lookup data
The below table contains the information that you should include in your import files for Lookup Types and Lookup Codes.
Lookup Type – Header Fields and Descriptions
| Header Field | M-Mandatory O-Optional |
Data type | Size | Description | |
| 1 | LookupType | M | String | 30 | Lookup Type |
| 2 | Meaning | M | String | 80 | |
| 3 | Description | O | String | 240 | |
| 4 | ModuleType | M | String | 60 | |
| 5 | ModuleKey | M | String | 60 |
Lookup Codes – Header Fields and Descriptions
| Header Field | M – Mandatory O – Optional |
Data type | Size | Description | |
| 1 | LookupType | M | String | 30 | Lookup Type |
| 2 | LookupCode | M | String | 30 | |
| 3 | DisplaySequence | O | Long | ||
| 4 | EnabledFlag | M | String | 1 | Y or N |
| 5 | StartDateActive | O | Date | Format to use is dd/M/yyyy Example 22/5/2015 |
|
| 6 | EndDateActive | O | Date | Format to use is dd/M/yyyy Example 22/5/2017 |
|
| 7 | Meaning | M | String | 80 | |
| 8 | Description | O | String | 240 | |
| 9 | Tag | O | String | 150 |
While creating the import file use this pipe ‘|’ as your delimiter and save this file in .csv file extension.
Sample Lookup File
LookupType|Meaning|Description|ModuleKey|ModuleType
POC_TYPE| POC_TYPE_MEANING| POC_LTYPE_DESC|PER|APPLICATION
Sample Lookup Code File
LookupType|LookupCode|DisplaySequence|EnabledFlag|StartDateActive|EndDateActive|Meaning|Description|Tag
POC_TYPE|POC_TYPE_CODE|1|Y|04/07/2022|31/12/4712|POC_TYPE_CODE_MEANING|POC_TYPE_CODE _DESC|CODE _TAG
Step 2: Navigation to upload the files to the UCM Server
Navigation: Navigator >> Tools >> File Import and Export
Fig 1: Tools > File Import
Click on Upload icon.
Fig 2: Search Screen
Browse the prepared import file to upload then select ‘setup/functionalSetupManger/import’ from the Account drop-down list. Click on save and close.
Fig 3: Upload Screen
Fig 4: Search Results
Step 3: Import the uploaded data into fusion as lookup data.
Go to Setup and Maintenance.
Fig 5: Setup & Maintenance
Click on Search
Fig 6: Search Capability
Search for Manage common lookups and Select import from the Actions menu.
Fig 7: Import
In Import Standard Lookups, select the Account where you imported the files (setup/functionalSetupManger/import).
Give the Full name of the file to be imported, including the csv extension (For example Lookup_type.csv) and click on upload.
Fig 8: Upload
You can view the progress of the process and you will able to download the process log file when the process has completed.
Fig 9: Success Upload
Navigate to the lookup field in the user interface to verify that your lookup values imported as expected.
Fig 10: Imported Data
Points to remember:
Create separate files for Lookup Types and Lookup Codes. Files should be of UTF-8 encoding (“UTF-8 without BOM”. Notepad ++ had the feature to convert the encoding)
Business Benefits:
- Time and Efforts saved during Data Migration
- Ease of repetition into other instances
- Faster and Correct means of Data Migrated
Author
Mohamed Thowfik Rahman
Oracle Cloud Consultant | Kovaion