concurrent program can provide output in a language if the language is installed in Oracle. To find out which languages are installed in Oracle you can check this article. Generally seeded programs provide output of a program based on the user preferred language. In this example we have illustrated how to override user preference and provide the output of a concurrent program and give multi lingual output based on a certain rule/condition.

Step 1: Create Multi language function in the database
We created a multi language packaged function named, XX_MLS_LANG.GET_LANG. The function expects an input from a concurrent program. This input will come from a concurrent program parameter named, Language. In this example the user will enter a language name and this name will be converted into the language code by the function. For example, if the users enter GERMAN the language function will return D, that is the code in Oracle for German.
The code for this function is given below.
CREATE OR REPLACE PACKAGE APPS.xx_mls_lang AUTHID CURRENT_USER
AS
   FUNCTION get_lang
      RETURN VARCHAR2;
END xx_mls_lang;
/
 
CREATE OR REPLACE PACKAGE BODY apps.xx_mls_lang
AS
   FUNCTION get_lang
      RETURN VARCHAR2
   IS
      p_lingo              VARCHAR2 (40)   := NULL;
      l_select_statement   VARCHAR2 (4000);
      source_cursor        INTEGER;
      lang_string          VARCHAR2 (240)  := NULL;
      l_lang               VARCHAR2 (30);
      l_lang_str           VARCHAR2 (500)  := NULL;
      l_base_lang          VARCHAR2 (30);
      l_dummy              INTEGER;
      ret_val              NUMBER          := NULL;
      parm_number          NUMBER;
      l_trns_lang_check    NUMBER;
   BEGIN
      -- Parameter Entry
      ret_val := fnd_request_info.get_param_number ('Language', parm_number);
 
      IF (ret_val = -1)
      THEN
         p_lingo := NULL;
      ELSE
         p_lingo := fnd_request_info.get_parameter (parm_number);
      END IF;
 
      -- Get Base Language
      SELECT language_code
        INTO l_base_lang
        FROM fnd_languages
       WHERE installed_flag = 'B';
 
      -- If the user has entered a language/value for the parameter then
      -- extract it the value
      IF p_lingo IS NOT NULL
      THEN
         -- Open the cursor
         source_cursor := DBMS_SQL.open_cursor;
 
         -- Create a query string to get languages based on parameters.
         l_select_statement :=
            'SELECT language_code FROM fnd_languages where nls_language = UPPER(:p_language)';
         DBMS_SQL.parse (source_cursor, l_select_statement, DBMS_SQL.v7);
         DBMS_SQL.bind_variable (source_cursor, ':p_language', p_lingo);
 
         -- Execute the cursor
         DBMS_SQL.define_column (source_cursor, 1, l_lang, 30);
         l_dummy := DBMS_SQL.EXECUTE (source_cursor);
 
         -- If the cursor has returned more than 1 row then
         -- get the output of the cursor into respective variables
         IF DBMS_SQL.fetch_rows (source_cursor) <> 0
         THEN
            DBMS_SQL.COLUMN_VALUE (source_cursor, 1, l_lang);
            l_lang_str := l_lang;
         ELSE
            -- If the cursor returned 0 rows then return the base language
            l_lang_str := l_base_lang;
         END IF;
 
         -- Close the cursor
         DBMS_SQL.close_cursor (source_cursor);
      ELSE
         -- If the user has not entered any value then return the base language
         l_lang_str := l_base_lang;
      END IF;
 
      fnd_file.put_line
         (fnd_file.LOG,
          'Checking to see if the derived language has a translated layout or not'
         );
 
      BEGIN
         -- Check if the language entered by the user is associated to a translated template or not
         SELECT 1
           INTO l_trns_lang_check
           FROM xdo_lobs xl
          WHERE xl.lob_type = 'MLS_TEMPLATE'
            AND xl.trans_complete = 'Y'
            AND xl.LANGUAGE = l_lang_str
            AND xl.lob_code =
                   ( -- Get the actual program name from the MLS request
                    SELECT argument2  -- Prog name
                      FROM fnd_run_req_pp_actions
                     WHERE parent_request_id = fnd_global.conc_request_id -- Request id of the MLS function
                       AND action_type = 6)             -- Template Code
                                                 ;
      EXCEPTION
         WHEN OTHERS
         THEN
            -- If the chosen language does not have an associated template the SQL will fail
            -- and therefore return the default language
            fnd_file.put_line (fnd_file.LOG,
                                  'There is no layout for language: '
                               || l_lang_str
                              );
            fnd_file.put_line
               (fnd_file.LOG,
                   'Therefore we are using the default template for language: '
                || l_base_lang
               );
            l_lang_str := l_base_lang;
      END;
 
      RETURN (l_lang_str);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_SQL.close_cursor (source_cursor);
         RAISE;
   END get_lang;
END xx_mls_lang;
/

Step 2: Create an executable for Multi Language
When the multi language packs are installed in Oracle a new type of concurrent executable is created, Multi Language Function. We shall create a concurrent executable of this type for the database function we have created.

Step 3: Add the parameter to the concurrent program (Optional)
In this step we are going to modify the seeded program, Active Users, to provide the output in multi language. Since this program does not take any parameters we are going to add a parameter to this program to accept a language name as a user entry (as explained in Step 1).
Step 4: Attach the multi language executable
Now we shall attach the multi language executable to the concurrent program, Active Users, so that the output language is taken from the MLS function.
Once the language packs are installed a field named, MLS function, is enabled on concurrent program form. Enter the MLS executable here.
Note: MLS function field has a LOV attached to it. The LOV has the list of executables that are of type Multi Language Function, i.e. executables defined as in Step 2.

Test the concurrent program
Now we shall execute the concurrent program to check the output. Open the SRS form
Now select the program as Active Users.
We get a prompt for the parameter we had created, i.e. Language. Enter a language, say Spanish.
Press OK and submit the program.
Notice that 2 concurrent programs are executed by Oracle instead of 1.
  1. Active Users (Multiple Languages)
  2. ES-ES: (Active Users)
This is because the first request is for the MLS language function and the second request is for the concurrent program.
When the concurrent programs complete check the log and output of both the requests.
  • Output of request, Active Users (Multiple Languages)
There is no output of the request that is kicked off for the MLS function.
  • Log of request, Active Users (Multiple Languages)
+---------------------------------------------------------------------------+
Application Object Library: Version : 12.0.0
 
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
 
FNDMLSUB module: Multiple Languages
+---------------------------------------------------------------------------+
 
Current system time is 15-AUG-2013 08:35:59
 
+---------------------------------------------------------------------------+
 
**Starts**15-AUG-2013 08:35:59
**Ends**15-AUG-2013 08:35:59
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
Calling language function xx_mls_lang.get_lang  : 15-AUG-2013 08:35:59
Language function returned the following languages : E .  : 15-AUG-2013 08:35:59
+---------------------------------------------------------------------------+
The following are the details of submitted requests:
Request ID Language
------------------------------------------
     57613357       SPANISH
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+

Important: When a concurrent request gives output in multiple languages it is the header or the data labels that are changed into different languages. The data remains in the same language as it is stored in the database.
  • Log of request, Active Users (Multiple Languages)
+---------------------------------------------------------------------------+
Application Object Library: Version : 12.0.0
 
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
 
FNDSCURS module: Usuarios Activos
+---------------------------------------------------------------------------+
 
Hora actual del sistema: 15-AGO-2013 08:35:59
 
+---------------------------------------------------------------------------+
 
+-----------------------------
| Iniciando la ejecución del programa simultáneo...
+-----------------------------
 
Argumentos
------------
Language='Spanish'
------------
 
APPLLCSP Environment Variable set to :
 
 Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
SPANISH_SPAIN.UTF8
 
' '
 
Introduzca la Contraseña:
REP-0092: Advertencia: Argumento LANGUAGE versión 1.1 no soportado. Use en su lugar la variable de entorno de Idioma Nacional de ORACLE.
REP-0092: Advertencia: Argumento LANGUAGE versión 1.1 no soportado. Use en su lugar la variable de entorno de Idioma Nacional de ORACLE.
 
Report Builder: Release 10.1.2.3.0 - Production on Jue Ago 15 08:36:02 2013
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
+---------------------------------------------------------------------------+
Inicio del log de mensajes de FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
Fin del log de mensajes de FND_FILE
+---------------------------------------------------------------------------+
 
Note that the log file has now changed to Spanish. This means that MLS affects data labels, headers and log files but not data.
Appendix:
Now if we were to take off the MLS function from Step 4 and executed Active Users concurrent program then Oracle would have executed only 1 request.