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)

LANGUAGE_CODE

NLS_LANGUAGE

NLS_TERRITORY

ISO_LANGUAGE_3

AR

ARABIC

UNITED ARAB EMIRATES

ARA

BG

BULGARIAN

BULGARIA

BUL

CA

CATALAN

CATALONIA

CAT

CKK

CYRILLIC KAZAKH

KAZAKHSTAN

KAZ

CS

CZECH

CZECH REPUBLIC

CES

CSR

CYRILLIC SERBIAN

SERBIA

SRP

D

GERMAN

GERMANY

DEU

DK

DANISH

DENMARK

DAN

E

SPANISH

SPAIN

SPA

EG

EGYPTIAN

EGYPT

EGY

EL

GREEK

GREECE

ELL

ESA

LATIN AMERICAN SPANISH

AMERICA

SPA

F

FRENCH

FRANCE

FRA

FRC

CANADIAN FRENCH

CANADA

FRA

GB

ENGLISH

UNITED KINGDOM

ENG

HR

CROATIAN

CROATIA

HRV

HU

HUNGARIAN

HUNGARY

HUN

I

ITALIAN

ITALY

ITA

IN

INDONESIAN

INDONESIA

IND

IS

ICELANDIC

ICELAND

ISL

IW

HEBREW

ISRAEL

HEB

JA

JAPANESE

JAPAN

JPN

KO

KOREAN

KOREA

KOR

LSR

LATIN SERBIAN

SERBIA

HBS

LT

LITHUANIAN

LITHUANIA

LIT

N

NORWEGIAN

NORWAY

NOR

NL

DUTCH

THE NETHERLANDS

NLD

PL

POLISH

POLAND

POL

PT

PORTUGUESE

PORTUGAL

POR

PTB

BRAZILIAN PORTUGUESE

BRAZIL

POR

RO

ROMANIAN

ROMANIA

RON

RU

RUSSIAN

RUSSIA

RUS

S

SWEDISH

SWEDEN

SWE

SF

FINNISH

FINLAND

FIN

SK

SLOVAK

SLOVAKIA

SLK

SL

SLOVENIAN

SLOVENIA

SLV

SQ

ALBANIAN

ALBANIA

SQI

TH

THAI

THAILAND

THA

TR

TURKISH

TURKEY

TUR

UK

UKRAINIAN

UKRAINE

UKR

US

AMERICAN

AMERICA

ENG

VN

VIETNAMESE

VIETNAM

VIE

ZHS

SIMPLIFIED CHINESE

CHINA

ZHO

ZHT

TRADITIONAL CHINESE

TAIWAN

ZHO

Sample data from Sap Language Table(T002) :

Language

Language Specifications

Degree of Translation of Lang.

Lng ISO

Name of Language

A

S

0

AF

Afrikaans

A

L

0

AR

Arabic

W

S

4

BG

Bulgarian

C

S

4

CA

Catalan

1

D

0

ZH

Chinese

M

D

0

ZF

Chinese trad.

6

S

0

HR

Croatian

Z

S

0

Z1

Customer reserve

C

S

4

CS

Czech

K

S

3

DA

Danish

N

S

2

NL

Dutch

E

S

1

EN

English

9

S

0

ET

Estonian

U

S

3

FI

Finnish

F

S

2

FR

French

#

S

0

3F

French_CA

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

SAP_OLFM_LANGUAGES (DEFINITION)

Column Name

Data Type

Size

Key

Taken from

SAP_Language

NVARCHAR

1

Primary Key

SPRAS field of T002 table from SAP

Priority

INTEGER

Primary Key

SAP_Lng_ISO

NVARCHAR

2

LAISO field of T002 table from SAP

OLFM_LANGUAGE_CODE

NVARCHAR

4

FND_LANGUAGE table from OLFM

OLFM_NLS_LANGUAGE

NVARCHAR

30

FND_LANGUAGE table from OLFM

OLFM_ISO_LANGUAGE_3

NVARCHAR

3

FND_LANGUAGE table from OLFM

SAP_OLFM_LANGUAGES

IND

SAP_Language

Priority

SAP_Lng

_ISO

OLFM_LANGUAGE

_CODE

OLFM_NLS_

LANGUAGE

OLFM_ISO_

LANGUAGE_3

0

1

SR

CSR

CYRILLIC SERBIAN

SRP

1

1

ZH

ZHS

SIMPLIFIED CHINESE

ZHO

2

1

TH

TH

THAI

THA

3

1

KO

KO

KOREAN

KOR

4

1

RO

RO

ROMANIAN

RON

5

1

SL

SL

SLOVENIAN

SLV

6

1

HR

HR

CROATIAN

HRV

7

1

MS

US

Malay

ENG

8

1

UK

UK

UKRAINIAN

UKR

9

1

ET

US

Estonian

ENG

#

1

3F

FRC

CANADIAN FRENCH

FRA

A

1

AR

AR

ARABIC

ARA

A

1

AF

US

Afrikaans

ENG

B

1

IS

IS

ICELANDIC

ISL

B

1

HE

IW

HEBREW

HEB

C

1

CA

CA

CATALAN

CAT

C

1

CS

CS

CZECH

CES

D

1

DE

D

GERMAN

DEU

D

1

SH

LSR

LATIN SERBIAN

HBS

E

1

EN

US

AMERICAN

ENG

E

2

EN

GB

UNITED KINGDOM

ENG

F

1

FR

F

FRENCH

FRA

G

1

EL

EL

GREEK

ELL

H

1

HU

HU

HUNGARIAN

HUN

I

1

IT

I

ITALIAN

ITA

I

1

ID

IN

INDONESIAN

J

1

JA

JA

JAPANESE

JPN

K

1

DA

DK

DANISH

DAN

L

1

PL

PL

POLISH

POL

M

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.

SAP_Language

Priority

SAP_Lng

OLFM_LANGUAGE

OLFM_NLS_

OLFM_ISO_

_ISO

_CODE

LANGUAGE

LANGUAGE_3

E

1

EN

US

AMERICAN

ENG

E

2

EN

GB

UNITED KINGDOM

ENG

/wp-content/uploads/2015/06/3_716931.jpg

/wp-content/uploads/2015/06/4_716933.jpg

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply