This page (revision-4) was last changed on 26-Nov-2021 10:22 by rforbes

This page was created on 26-Nov-2021 10:22 by JEscott

Only authorized users are allowed to rename pages.

Only authorized users are allowed to delete pages.

Page revision history

Version Date Modified Size Author Changes ... Change note
4 26-Nov-2021 10:22 5 KB rforbes to previous
3 26-Nov-2021 10:22 5 KB JEscott to previous | to last
2 26-Nov-2021 10:22 5 KB JAiken to previous | to last
1 26-Nov-2021 10:22 5 KB JEscott to last

Page References

Incoming links Outgoing links
TRANSLATION LEXICONS TO CTL FILE
TRANSLATION LEXICONS TO CTL FILE

Version management

Difference between version and

At line 3 changed one line
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.
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].
At line 5 changed one line
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:
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|CHANGE_CODE] 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:
At line 62 changed one line
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.
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, 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.
At line 64 changed one line
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:
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:
At line 86 changed one line
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.
As you can see from this example, the translation is actually performed by a stored procedure called 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.
----
![Notes|Edit:Internal.TRANSLATION+LEXICONS+TO+CTL+FILE]
[{InsertPage page='Internal.TRANSLATION+LEXICONS+TO+CTL+FILE' default='Click to create a new notes page'}]
This page (revision-4) was last changed on 26-Nov-2021 10:22 by rforbesTop