MLS (MULTI LANGUAGE SUPPORT) FUNCTION AND ITS IMPLEMENTATION

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.