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.
- Active Users (Multiple Languages)
- 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.
Recent Comments