Step 1: Create Multi language function in the database
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