, , , ,

Multi Language Data Support in Oracle E-business Suite

(MLS) is a feature in Oracle E-Business (EBS) Suite of applications that enables users to run their applications in many different languages. MLS has opened the doors for global rollout of Oracle EBS with users in different countries able to use the application in their own language at the same time.
MLS patch application
Multi Language patches can be applied in Oracle as per the metalink note, R11i / R12 : Requesting Translation Synchronization Patches (Doc ID 252422.1). This metalink note has got references to other notes for applying NLS patches.
Difference between NLS and MLS
National Language Support (NLS) refers to the ability of Oracle EBS to run in any one of the supported languages other than American English. However, MLS refers to the ability in which the instance can be run in as many as languages as are supported at the same time.
Once MLS patches have been applied in Oracle by the DBAs the developers have to keep a few more points in mind while developing custom components.
  • FND_LANGUAGES table shows which languages are installed, and which is the base language and which are non-base languages (Installed Languages)
    Query to identify which language(s) have been installed
SELECT LANGUAGE_CODE, INSTALLED_FLAG FROM FND_LANGUAGES
If the value of INSTALLED_FLAG is I then that language has been installed. If the value of INSTALLED_FLAG is B then it is the base language. By default, language_code is US is the base language.
Example
Execute the following query,
SELECT * FROM FND_LANGUAGES where installed_flag IN (‘I’, ‘B’)
  • The Translated tables (*_TL) contain one record for each language i.e the translated string for each language.
  • Translated views (*_VL) have been introduced for the “_TL” tables, which allow concurrent programs to select data based on the session language without running the risk of returning more rows than expected using a condition within the view query like,t.LANGUAGE= USERENV(‘LANG’)
Hence the view FND_CONCURRENT_PROGRAMS_VL will return rows as per the session language settings.
Note:
If queries are executed from SQL*Plus on the _VL views without setting the session language then no rows will be returned. This is explained in this article.
Example
If we query for concurrent_program_id, 20125, i.e. the program named Trial Balance, from the table FND_CONCURRENT_PROGRAMS_TLwe get the program name in all installed languages.
Querying the same concurrent_program_id from the corresponding _VL view, FND_CONCURRENT_PROGRAMS_VL gives us only 1 record.

MLS development guidelines

A list of general guidelines has been drawn up for developing technical components which will be display more than 1 language.

User messages

  • All user message (labels/pop ups) are to be derived from translation tables or from fnd message.
User message from the following,
  • Form item labels
  • Report item labels
  • Pop up messages
  • Concurrent program output
  • Workflow notifications
  • Form personalizations
Example
After the MLS patches have been applied open the Messages form.
Responsibility: Application Developer
Navigation: Application > Messages
Query for the seeded message named, ABORT.
Notice that the same message has been ported into as many messages as there are installed languages. The value has also been translated. Thus if a piece of code picks up the message text from a message it will only have pick up the message text based on the language and the code need not be changed at all.

SQL Queries

  • SQL queries should access data from _TL tables or _VL tables and have the following WHERE clause
    t.LANGUAGE= USERENV(‘LANG’)
An example of this incorrect usage is demonstrated here.


XML Publisher reports

  • There are 2 ways to develop XML Publisher report templates
  1. Each report will have as many templates as the number of languages it is expected to run on.
  2. Single template across all languages where the labels will also be derived from fnd messages or translation tables
    Single template Multiple template
    Advantage
    • 1 single file to be maintained
    • Modification/Addition of business logic or layout is simple
    • No business logic required to display template associate with language. It is configured in Oracle.
    Disadvantage
    • Modification/Addition of business logic or layout in the template is difficult
    • Business logic required to display the template associated with the language
    • Multiple files to be maintained
Example
Single template example
The developer is allowed to upload as many translatable files as there are installed languages in Oracle.
In this example there are 6 different language translations for a single template.
Multiple template example
You can also have multiple templates for a single report based on a language
In this case there is no need to have a separate translation file.


SQL Loader

Application File System

  • Once the MLS patches have been applied to Oracle a set of directories are created in Unix under reports, forms, etc directories with the 2 character language code.
    • For instance, if patch for Spanish has been applied in Oracle a new set of folders will be created, like $GL_TOP/reports/ES, $AP_TOP/forms/ES
    • The point to keep in mind here is that any custom component created with specific changes for a language must be dropped to the language specific directory.
    • All translatable files reside in a subdirectory which has the language code incorporated in the name (e.g. $AP_TOP/reports/EL, $GL_TOP/forms/F etc.) .
    – Forms, Reports, Messages & Seeded data are translated
Example
If you check $AU_TOP/forms directory in Unix you will find several directories
Now there is 1 directory to keep the same form in a different language. It is the same in all the seeded top directories, e.g. GL_TOP, AP_TOP, etc.

NLS parameters in functions

Many Oracle functions have MLS versions. The versions are listed below.
  • TO_DATE
    • NLS_DATE_LANGUAGE
    • NLS_CALENDAR
  • TO_NUMBER
    • NLS_NUMERIC_CHARACTERS
    • NLS_CURRENCY
    • NLS_DUAL_CURRENCY
    • NLS_ISO_CURRENCY
  • TO_CHAR
    • NLS_DATE_LANGUAGE
    • NLS_NUMERIC_CHARACTERS
    • NLS_CURRENCY
    • NLS_ISO_CURRENCY
    • NLS_DUAL_CURRENCY
    • NLS_CALENDAR
  • TO_NCHAR
    • NLS_DATE_LANGUAGE
    • NLS_NUMERIC_CHARACTERS
    • NLS_CURRENCY
    • NLS_ISO_CURRENCY
    • NLS_DUAL_CURRENCY
    • NLS_CALENDAR
  • NLS_UPPER
    • NLS_SORT
  • NLS_LOWER
    • NLS_SORT
  • NLS_INITCAP
    • NLS_SORT
  • NLSSORT
    • NLS_SORT

Example of usage

TO_NUMBER (’13.000,00′, ’99G999D99″,’nls_numeric_characters = ”,.”’)
Where:
’99G999D99″ –The format mask for the number
‘nls_numeric_characters = ”,.”” –The thousands and decimal separator to be used
The query
select TO_NUMBER (’13.000,00′, ’99G999D99′,’nls_numeric_characters = ”,.”’) Num from dual
will return
13000

Example of error message after applying MLS patches

Once the MLS patches were applied on a particular Oracle instance a DFF value started throwing errors. The error is displayed below.
The error message is the following,
The value ROW for value set ****** OE Shipment Priority occurs in more than one row in column LOOKUP_CODE of table FND_LOOKUP_VALUES and duplicate values are not allowed. Please choose a different value or contact your system administrator.
The reason for this error is,
  • The LOV associated with the DFF segment has not been correctly coded with regards to translations.
If we look into the value set definition for the DFF segment we find that the LOV is based on the table, FND_LOOKUP_VALUES.
As the Oracle instance now has MLS patches the table FND_LOOKUP_VALUES now can contain the same values across languages. We have 2 options to overcome the error faced by the user.
  1. We can add LANGUAGE= USERENV(‘LANG’) in the Where/Order By region to pick up the value based on the user’s language
  2. We can change the table name to FND_LOOKUP_VALUES_VL instead of FND_LOOKUP_VALUES
/by