Table of Contents
1.
Introduction of MLS Function
2. Implementation Steps
3. Issues faced and its solution
1 . Introduction of MLS Function
Multilingual
Support for Concurrent Requests
Users can submit a single concurrent request for a single
concurrent program to be run multiple times, each time in a different language.
Any output that is produced can be routed to different printers based on
language. Users can also route completion notifications based on the language
of the output.
For example, a user could submit a request for a Print
Invoices program that would
cause that program to run several times, each time in a
different language, with each set of invoices printed on a different printer.
Note: Multilingual
requests cannot be run within request sets.
MLS Functions
Developers can create an MLS function for concurrent
programs. The MLS function determines in which of the installed languages a
request should run.
For example, an MLS function for a Print Invoices program
could require that any request for that program to run only in the preferred
languages of the customers who have pending invoices. This restriction saves
system resources by assuring that the request does not run in languages for which no output will be
produced. This restriction also prevents user error by automatically selecting
the appropriate languages for a request.
MLS functions are PL/SQL stored procedures. When the
concurrent manager processes a multilingual request for a concurrent program
with an associated MLS function, it calls the MLS function to retrieve a list
of languages and submits the appropriate child requests for each language. The
concurrent program application short name, the concurrent program short name,
and the concurrent request parameters are all available to the MLS function to
determine the list of languages that the request should be run in.
MLS functions are registered in the Concurrent Program
Executable form. A registered MLS function can be assigned to one or more
concurrent programs in the Concurrent Programs form.
2. Implementation Steps:
STEP-1 : Creation of MLS function
ñ The MLS function should return list of languages in a
string format such that languages in the string should be divided with
punctuation mark ','.
For
example : F,D,US.
ñ The MLS function can use FND_REQUEST_INFO API to retrieve the concurrent program application
short name, the concurrent program short name, and the concurrent request
parameters if needed.
ñ Let us see some of the functions of FND_REQUEST_INFO API which can be useful while developing MLS
function.
FND_REQUEST_INFO.GET_PARAM_NUMBER :
Summary
function
GET_PARAM_NUMBER
(name
IN VARCHAR2,
param_number OUT
NUMBER);
Description
Use this function to
retrieve the parameter number for a given parameter name. The function will
return -1 if it fails to retrieve the parameter number.
Arguments (input)
Name : The name
of the parameter of the request's concurrent program
FND_REQUEST_INFO.GET_PARAM_INFO :
Summary
function
GET_PARAM_INFO
(param_number IN NUMBER,
name OUT VARCHAR2);
Description
Use this function to
retrieve the parameter name for a given parameter number. The function will
return -1 if it fails to retrieve the parameter name.
Arguments (input)
Param_number :
The number of the parameter of the request's concurrent program.
FND_REQUEST_INFO.GET_PARAMETER :
Summary
function
GET_PARAMETER
(param_number IN NUMBER)
Description
This function returns the
concurrent request's parameter value for a given parameter number. The function
will return the value as varchar2.
Arguments (input)
Param_number : The number of the parameter of the
request's concurrent program.
- We can use the concurrent request parameters to get the languages.
The below is the example code for MLS function which returns the languages of
the customers location in string format.
Suppose you have a concurrent program that will print
invoice details. Assume that the concurrent program will accept a range of
dates like FROM_DATE and TO_DATE.
The customers invoices falling in that dates want to
receive the report in their preferred language. Without an MLS function, the
user who submits this concurrent program has to guess at all the preferred
languages of the customers and select those languages while submitting the
request. Selecting all installed languages might be a waste of resources,
because output may not be required in all installed languages.
In the below Function code, the parameters passed to the
concurrent Request are retrieved using
FND_REQUEST_INFO API and passed as a input value to the query in order
to retrieve the language of each customers location.
The languages retrieved are concatenated and returned as a
string by the function.
PACKAGE SPECIFICATION:
CREATE OR REPLACE
PACKAGE XXTA_CTMLS_MLS_PACKAGE
AS
FUNCTION XXTA_CTMLS_MLS_FUNCTION
RETURN VARCHAR2;
END XXTA_CTMLS_MLS_PACKAGE;
PACKAGE BODY
:
CREATE OR REPLACE
PACKAGE body XXTA_CTMLS_MLS_PACKAGE
AS
FUNCTION XXTA_CTMLS_MLS_FUNCTION
RETURN VARCHAR2
IS
/* parameters used by the query to retrive language*/
P_cust_name VARCHAR2(40) := NULL;
p_date_from
VARCHAR2(30);
p_date_to VARCHAR2(30);
p_userenv_lang
VARCHAR2(4);
p_base_lang VARCHAR2(4);
/* Parameters used by FND_REQUEST_INFO API to parameter
value*/
retval NUMBER;
parm_number NUMBER;
parm_name VARCHAR2(80);
/* The function returns language string stored in this
variable*/
P_lang_string
VARCHAR2(100);
CURSOR
lang_details(P_cust_nam VARCHAR2,p_date_frm VARCHAR2,p_date_t
VARCHAR2,p_userenv_lang VARCHAR2)
IS
SELECT
DISTINCT(NVL(rtrim(SUBSTR(hl.language,1,4)),p_userenv_lang)) language
FROM
ra_customer_trx_all rct,
hz_cust_accounts_all
hca,
hz_cust_acct_sites_all
hcas,
hz_cust_site_uses_all
hcuas,
hz_party_sites
hpa,
hz_locations
hl,
hz_parties hp
WHERE rct.bill_to_customer_id
= hca.cust_account_id
AND
hca.party_id =hp.party_id
AND
hca.cust_account_id
=hcas.cust_account_id
AND
hcas.cust_acct_site_id
=hcuas.cust_acct_site_id
AND
hcas.party_site_id
=hpa.party_site_id
AND
rct.bill_to_site_use_id
=hcuas.site_use_id
AND
hp.party_name =
NVL(p_cust_name,hp.party_name)
AND
TRUNC(rct.trx_date) BETWEEN FND_DATE.CANONICAL_TO_DATE (p_date_frm) AND
FND_DATE.CANONICAL_TO_DATE(p_date_t);
BEGIN
/* Storing the language of environment from where the
function is called, into the variable
which is passed in above cursor in case if the language is
null for the location*/
SELECT
SUBSTR(userenv('LANG'),1,4) INTO p_userenv_lang FROM dual;
SELECT language_code
INTO p_base_lang
FROM fnd_languages
WHERE
installed_flag = 'B';
/* Getting the parameters value using API into the
variables*/
retval
:= FND_REQUEST_INFO.GET_PARAM_NUMBER('CUSTOMER_NAME',parm_number);
IF retval = -1 THEN
P_cust_name := NULL;
ELSE
P_cust_name :=
FND_REQUEST_INFO.GET_PARAMETER(parm_number);
END IF;
retval :=
FND_REQUEST_INFO.GET_PARAM_NUMBER('TRX_DATE_FROM',parm_number);
IF retval = -1 THEN
p_date_from :=
NULL;
ELSE
p_date_from :=
FND_REQUEST_INFO.GET_PARAMETER(parm_number);
END IF;
retval :=
FND_REQUEST_INFO.GET_PARAM_NUMBER('TRX_DATE_TO',parm_number);
IF retval = -1 THEN
p_date_to :=
NULL;
ELSE
p_date_to :=
FND_REQUEST_INFO.GET_PARAMETER(parm_number);
END IF;
/*Passing the parameters which we got from API to the
cursor to retrive language related to the customer location*/
FOR lang_rec IN
lang_details(P_cust_name,p_date_from,p_date_to,p_userenv_lang)
LOOP
IF p_lang_string
IS NULL THEN
P_lang_string :=lang_rec.language;
ELSE
p_lang_string:=p_lang_string
|| ',' || lang_rec.language;
END IF;
END LOOP;
RETURN
P_lang_string;
END;
END;
STEP-2 : Registering MLS function as a Executable
ñ After creation of MLS function,register the MLS function
in the Concurrent Program Executable form.
ñ The Execution method should be 'Multi Language Function'.
|
STEP - 3 : Associating the Executable to the concurrent
program
ñ Then associate the registered MLS function with a
concurrent program in the Concurrent Programs form under 'Request' Frame in
'MLS Function'
|
field.
ñ Note that the 'Use in SRS' check box should be checked in
the Concurrent
Programs
form,This is checked by default. When it is checked, the program
is
available to run from the standard request submission screen.
STEP-4 : Creating Templates and Adding Them to the
Templates Page
ñ Create templates RTF files in different languages as per
the requirement and add them in the Templates page created for the Concurrent
Program under
'XML
Publisher Administrator' Responsibility.
 |
ñ Add multiple template files of different languages in the
Templates Page using 'Add File' button.
ñ Now run the Concurrent Program which implements Multi
language Support functionality and get output in different languages.
3. Issues
Faced and its Solution:
Because of the
usage of the data type “FND_STANDARD_DATE” for concurrent request parameter,
Oracle Applications send the date value in the format “YYYY/MM/DD HH24:MI:SS”
and Oracle database expects the date value in the format “DD-MON-YYYY
HH24:MI:SS” and this is the cause of the issue .
Solution :
Define the Date
parameters with data type VARCHAR2 in procedure
Use
'FND_DATE.CANONICAL_TO_DATE' to convert varchar2 format to oracle
date format and
then use it anywhere in a program.
