Oracle Apps Multi-Lingual Support (MLS Function)
In this article, we will discuss about Oracle Apps Multi-Lingual Support (MLS Function). This is a great feature if you want your application to support multiple languages in reporting.
Developers can create an MLS function for concurrent programs. The MLS function determines in which of the installed languages a request should run. MLS functions are PL/SQL stored procedures, written to a specific API. 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.
How To Define MLS Function:
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.
Below is the sample PL/SQL package Spec & Body registered as 'Execution File Name' when you define the MLS function as concurrent program executable.
CREATE OR REPLACE PACKAGE XX_MLS_FUNCTION_PKG AS FUNCTION get_mls_language_code RETURN VARCHAR2; END XX_MLS_FUNCTION_PKG;
CREATE OR REPLACE PACKAGE BODY XX_MLS_FUNCTION_PKG AS FUNCTION get_mls_language_code RETURN VARCHAR2 IS v_conc_prog_name VARCHAR2 (240); v_conc_prog_appl VARCHAR2 (100); v_language VARCHAR2 (50); v_language_code VARCHAR2 (10); v_lang_prameter_name VARCHAR2 (20) := 'Language'; --This is the concurrent program's parameter name v_session_lang VARCHAR2 (50); CURSOR c_fetch_language_code (p_param_value apps.fnd_languages.nls_language%TYPE) IS SELECT language_code FROM apps.fnd_languages fl WHERE fl.nls_language = (SELECT DECODE (p_param_value ,'ENGLISH', 'AMERICAN' ,p_param_value ) FROM DUAL ); BEGIN -- Getting the concurrent program name and application. apps.fnd_request_info.get_program (v_conc_prog_name, v_conc_prog_appl); -- Getting the language parameter value. Make sure the in the concurrent program, the parameter name is Language v_language := apps.fnd_request_info.get_parameter (v_lang_prameter_name); OPEN c_fetch_language_code (v_language); FETCH c_fetch_language_code INTO v_language_code; CLOSE c_fetch_language_code; /* You can write your custom code here. e.g if you want to return the Sales Order Customer's billing address country language. The code goes some thing like below IF (v_conc_prog_name = 'XXOMREPORT') THEN --Query to get the customer billto country and language v_om_header_id := apps.fnd_request_info.get_parameter ('OM_HEADER_ID'); SELECT bill_loc.LANGUAGE INTO v_language_code --assign the the vale in v_language_code FROM apps.oe_order_headers_all ooha ,apps.hz_cust_accounts hca ,apps.hz_cust_site_uses_all bill_su ,apps.hz_cust_acct_sites_all bill_cas ,apps.hz_parties hp ,apps.hz_party_sites hps ,apps.hz_locations bill_loc WHERE ooha.header_id = v_om_header_id AND ooha.sold_to_org_id = hca.cust_account_id AND bill_su.site_use_id = ooha.invoice_to_org_id AND bill_cas.cust_account_id = hca.cust_account_id AND bill_cas.cust_acct_site_id = bill_su.cust_acct_site_id AND hca.party_id = hp.party_id AND hps.party_id = hp.party_id AND hps.party_site_id = bill_cas.party_site_id AND bill_loc.location_id = hps.location_id AND ROWNUM = 1; END IF; */ RETURN v_language_code; EXCEPTION WHEN OTHERS THEN SELECT USERENV ('lang') INTO v_session_lang FROM DUAL; RETURN v_session_lang; -- Returns session language END get_mls_language_code; END XX_MLS_FUNCTION_PKG;
Note: You can have your custom logic inside the MLS function's code to return the language. For example if you want to return the sales order's customer's language, write your code accordingly for that specific program.
Now when you define the concurrent program, you can attach the MLS function to the concurrent program and define a parameter name 'Language' where you can put languages as LOV.
Note: The parameter name should match with the parameter name used in the MLS function.
Now you are good to go and when you run the concurrent program "Test for Demo" you will see two requests submitted, the first one with original language and the 2nd one with the language returned from MLS function.
Select the language parameter as "French" and you will see there are two request ids submitted. The child is submitted from parent request and the language for the child is French (FR -- marked in green box).