Adding Translations Lexicons to Control Files#

In the situation where a large number of values need to be translated from the legacy system to the application , it may be necessary to build a translation lexicon for the SQL Loader control file to make use of. This is the same process that has already been done for several fields in the conversion process, such as CHANGE_CODE.

Building a translation lexicon can be done easily within Excel and then added to the current list of translations in the system. The following is an example of adding a translation lexicon into the system for the CHANGE_CODE. The first step is to build a comma separated values, or CSV, file that lists all the CHANGE_CODES that will be extracted from the current system and their equivalent value in the application. The following file is a section of one of these files:

TRAN_DCR,.old,.new,P2K Description
TRAN_DCR,01,EMPLOY CAREER,01 EMPLOYMENT - CAREER                           
TRAN_DCR,02,EMPLOY TERM,02 EMPLOYMENT - TERM                             
TRAN_DCR,04,EMPLOY TO CAREER,04 CONVERSION TO CAREER (EMPLOYMENT)             
TRAN_DCR,05,REHIRE,05 REHIRE                                        
TRAN_DCR,06,REACT ASSIGNMT,06 REACTIVATE ASSIGNMENT                         
TRAN_DCR,08,TERMINATION,08 TERMINATION OF EMPLOYMENT                     
TRAN_DCR,15,CAREER FT TO PT,15 CAREER FULL-TIME TO CAREER PART-TIME          
TRAN_DCR,16,CAREER PT TO FT,16 CAREER PART-TIME TO CAREER FULL-TIME          

The first column is the name of the lexicon that will be created in the IMLN screen to hold the translation lexicon values. The second column contains the values that will be extracted from the legacy system, and the third column contains the value that it will be translated to. The fourth column is optional, and can contain the meaning of the value or its original value from the legacy system.

Once the comma delimited file has been created, use a text editor to append this list of translations onto the current list in the TRANSLATE.DAT file. The order of the records in this TRANSLATE.DAT file is not important, but should be listed in alphabetical order to make it easier to read and locate entries.

The next step is to add the name of the lexicon to the CV_TRAN_LOAD.SQL script so that the entry will be created the next time the script is run. Below is a section of the SQL script that inserts the lexicon names into the database. Add another entry to the end of the script to build the entry.

-- MODIFICATION HISTORY
-- ----------------------------------------------------
-- 20011015-IB-created to easily delete and rebuild translation tables.
--             loads the P2K_SMGLXID function
--             inserts the TRAN_% Lexicon Names
--             loads in the Lexicon_Values

@P2K_SVGLI.sql

delete from p2k_am_lexicon_names
where  LEXICON_NAME like 'TRAN_%'
and    LEXICON_TYPE = 'UC'
/
commit;
insert into p2k_am_lexicon_names
       (LEXICON_NAME, LEXICON_TYPE, VALUE_LENGTH)
values ('TRAN_PPC', 'UC', 5)
/
insert into p2k_am_lexicon_names
       (LEXICON_NAME, LEXICON_TYPE, VALUE_LENGTH)
values ('TRAN_DES', 'UC', 30)
/
.
.
.
insert into p2k_am_lexicon_names
       (LEXICON_NAME, LEXICON_TYPE, VALUE_LENGTH)
values ('TRAN_DTR', 'UC', 30)
/
insert into p2k_am_lexicon_names
       (LEXICON_NAME, LEXICON_TYPE, VALUE_LENGTH)
values ('TRAN_DCR', 'UC', 30)
/
commit;

host sqlldr p2k/p2k@PROD Translate.ctl

In the script listed above, you will see that the last INSERT statement adds the TRAN_DCR entry to the lexicon names. The entry is defined as a User Created lexicon UC, with a maximum size of thirty characters. Sixteen characters is typically enough for most translations, but can be increased to as much as thirty characters. Once the changes have been made to both the TRANSLATE.CSV file and the CV_TRAN_LOAD.SQL script run SQL Plus, log in as P2K and execute the CV_TRAN_LOAD script. This will delete and recreate all the translation lexicons in the target database.

The last step is to alter the SQL Loader control file to make use of the new translation lexicon. The example listed below is a modified version of the ASSIGNMENTS.CTL file, showing the translation of the CHANGE_CODE column as it is being loaded into the INF_CV_ASSIGNMENTS table:

LOAD DATA
INFILE 'assignments.dat'
TRUNCATE
INTO TABLE INF_CV_ASSIGNMENTS
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  ENTITY_CODE			      POSITION(*)                
, PERSON_CODE			      POSITION(*)                
, EMPLOYMENT_TYPE		      POSITION(*)            
, HIRE_DATE			      POSITION(*) "TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')"
, ASSIGNMENT_CODE		      POSITION(*)            
, ASSIGNMENT_TYPE                 POSITION(*) 
, ASSIGNMENT_STATUS               POSITION(*)          
.
.
.
, CHANGE_CODE			      POSITION(*) "p2k_smglx('TRAN_DCR', rtrim(ltrim(:change_code)))"

As you can see from this example, the translation is actually performed by a stored procedure created by High Line call P2K_SMGLX. This function takes two parameters and returns a string for the translation. The first parameter is the lexicon name that was created to hold the translation list, the second is the field that contains the coded value that needs to be translated. In this example the field that contains the coded value is the same field that the translation is being performs on, but this is not a requirement. It is possible to use any column, or set of columns, in the records for translation.