Oracle Apps Multi-Lingual Support (MLS Function)

Introduction:

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).