Language mapping between Oracle Lease and Finance Management (OLFM) and SAP HANA

Business Case:                                   

During cross system integration between SAP HANA and Oracle Lease and Finance Management (OLFM) it is not possible to get the OLFM language text description based on the system logon language of SAP HANA as  there is no common language mapping available.                                                                                                                                                                                            select SESSION_CONTEXT(‘LOCALE_SAP’) from dummy) this will fetch us the system logon language

Language Mapping Table:                                                 

In order to get the get the corresponding OLFM language code for SAP logon language, we need to create a mapping table between the logon language of SAP HANA and OLFM languages.

Sample data from OLFM Language Table(FND_LANGUAGE)

Sample data from Sap Language Table(T002) :

The mapping table between  OLFM(FND_LANGUAGE) and SAP HANA  .

SAP_OLFM_LANGUAGES (DEFINITION)

SAP_OLFM_LANGUAGES

Note : this table is based as per the requirement so manipulation  in the table fields are possible .

STEPS TO ACHIEVE THIS FUNCTIONALITY:

Now in order to get the fields on the basis of priority we need to create a Scripted Calculation  View(SCV) which will give you the logon language based on priority.

So the code for the Scripted calculation view will be like

V_CNT_1 INTEGER;

V_CNT INTEGER;

V_LOOP INTEGER;

V_CHK NVARCHAR(4);

V_SYSLANG NVARCHAR(1);

BEGIN

SELECT SESSION_CONTEXT(‘LOCALE_SAP’) INTO V_SYSLANG  FROM DUMMY;

SELECT COUNT(*) INTO V_CNT  FROM SAP_OLFM_LANGUAGES

WHERE SAP_OLFM_LANGUAGES.SAP_LANGUAGE = V_SYSLANG ;

IF :V_CNT = 1 THEN

                VAR_OUT = SELECT

                                                                                SAP_OLFM_LANGUAGES.OLFM_LANGUAGE_CODE AS OLFM_LANG_CODE,

                                SAP_OLFM_LANGUAGES.PRIORITY AS PRIORITY_CODE

                                      FROM  SAP_OLFM_LANGUAGES

                                      WHERE SAP_OLFM_LANGUAGES.SAP_LANGUAGE = V_SYSLANG;

ELSEIF :V_CNT > 1 THEN

                V_LOOP := 1;

                                WHILE :V_LOOP <= :V_CNT            DO

                                                  SELECT SAP_OLFM_LANGUAGES.OLFM_LANGUAGE_CODE INTO V_CHK

                                                  FROM SAP_OLFM_LANGUAGES

                                                                WHERE SAP_OLFM_LANGUAGES.PRIORITY = :V_LOOP

                                                                AND SAP_OLFM_LANGUAGES.SAP_LANGUAGE = V_SYSLANG;

                                                                SELECT COUNT(*) INTO V_CNT_1 FROM FA_ADDITIONS_TL

                                                                WHERE FA_ADDITIONS_TL.LANGUAGE =  :V_CHK;

                                                IF :V_CNT_1 > 0 THEN

                                                                VAR_OUT = SELECT

                                                                                                                V_CHK AS OLFM_LANG_CODE,

                                                                                                                V_LOOP AS PRIORITY_CODE

                                                                                                  FROM DUMMY;

                                                                V_LOOP :=  :V_CNT + 1;

                                                ELSE

                                                                V_LOOP := :V_LOOP + 1;

                                                END IF ;

                                END WHILE;

ELSE

VAR_OUT = SELECT  ‘US’ AS OLFM_LANG_CODE,1 AS PRIORITY_CODE FROM DUMMY;

END IF;

END;

This will have 2 output columns

Now this SCV will be taken into a projection and that projection will be joined with other transactional tables based on Language field of OLFM table to SAP_OLFM_LANGUAGES  (Preferably Inner join)

For Ex:- English ’E’ is the logon language for two countries i.e ‘US’ and ‘UK’ as shown below , so to handle this kind of scenario we have added a priority field.in the SCV we have coded in such a way that based on the priority field value the first entry in the mapping table will be checked with the transactional table on  “OLFM_LANGUAGE

_CODE” if the entry dosen’t  exist for the priority 1 field then automatically the priority value 2 will be checked. And the corresponding language text description will be retrieved for the output.