1 package body fnd_obiee_i18n as
2 /* $Header: AFOBIEEB.pls 120.2 2011/04/11 09:45:31 nareshku noship $ */
3
4 -- Convert language codes between ORACLE, OBIEE, DLF. currently only provides the following conversion:
5 -- ORACLE->OBIEE
6 -- OBIEE->ORACLE
7 -- OBIEE->DLF
8 -- DLF->OBIEE
9 -- Note that all the conversion types specified have to be in upper cases.
10 -- Unrecognized conversion types will return null.
11 function obiee_convert_langcode (from_type varchar2, to_type varchar2, lang_code varchar2) return varchar2 is
12 target_lang_code varchar2(10) := null;
13 pos integer;
14 lang_code_length integer;
15 u_lang_code varchar2(20) := null;
16 l_lang_code varchar2(20) := null;
17 target_lang varchar2(10) := null;
18 target_terr varchar2(10) := null;
19
20 begin
21
22 -- Convert Oracle language code to OBIEE language code.
23 -- Most commonly it would the corresponding ISO language code, except for
24 -- languages with suffix in OBIEE: 'ptb'->'pt-br', 'zht'->'zh-tw', 'zhs'->'zh-cn'.
25 -- Return 'en' if an exception is encountered
26 if (from_type = 'ORACLE') and (to_type = 'OBIEE') then
27 begin
28 if lang_code is null then
29 return 'en';
30 else
31 u_lang_code := upper(lang_code);
32 end if;
33
34 select decode(fl.language_code,
35 'PTB', fl.ISO_LANGUAGE||'-'||fl.ISO_TERRITORY,
36 'ZHT', fl.ISO_LANGUAGE||'-'||fl.ISO_TERRITORY,
37 'ZHS', fl.ISO_LANGUAGE||'-'||fl.ISO_TERRITORY,
38 fl.iso_language)
39 into target_lang_code
40 from fnd_languages fl
41 where u_lang_code = fl.language_code;
42
43 return lower(target_lang_code);
44 exception
45 when others then
46 return 'en';
47 end;
48
49 -- Convert OBIEE language code to Oracle language code.
50 -- Most commonly it would be the Oracle language code with the matching
51 -- ISO language code, with the following exceptions:
52 -- 1. OBIEE language code with suffix, then it would be the Oracle language
53 -- code with matching ISO language and default ISO territory
54 -- 2. Multiple Oracle language codes have the same ISO language code, then
55 -- the mapping is hard-coded.
56 -- Unrecognized input code including null will return 'US'.
57 elsif (from_type = 'OBIEE') and (to_type = 'ORACLE') then
58 begin
59 if lang_code is null then
60 return 'US';
61 else
62 pos := instr(lang_code, '-');
63 lang_code_length := length(lang_code);
64 u_lang_code := upper(lang_code);
65 l_lang_code := lower(lang_code);
66 end if;
67
68 -- this should no longer happen in OBIEE 11g as OBIEE 11g uses 'he'
69 -- instead of 'iw' for Hebrew language code, while earlier versions of
70 -- OBIEE used 'iw'
71 -- for Hebrew language code.
72 if l_lang_code = 'iw' then
73 return 'IW';
74 end if;
75
76 -- OBIEE language code has language suffix
77 if pos > 0 then
78 begin
79 target_lang := substr(u_lang_code, 1, pos-1);
80 target_terr := substr(u_lang_code, pos+1, lang_code_length-pos);
81
82 if (target_lang is null) or (target_terr is null) then
83 return 'US';
84 end if;
85
86 select fl.language_code
87 into target_lang_code
88 from fnd_languages fl
89 where fl.iso_language = target_lang
90 and fl.iso_territory = target_terr;
91
92 return target_lang_code;
93 exception
94 when others then
95 return 'US';
96 end;
97 else
98 begin
99 -- for OBIEE code that matches Oracle ISO lang code with a unique
100 -- record in fnd_languages
101 select fl.language_code
102 into target_lang_code
103 from fnd_languages fl
104 where fl.iso_language = u_lang_code;
105
106 return target_lang_code;
107 exception
108 -- there are multiple entries in fnd_languages with the matching ISO code
109 -- for the following OBIEE language codes, hardcode the mapping since
110 -- there is no systematic way of finding the mapping and the language codes
111 -- are not expected to change.
112 when too_many_rows then
113 if l_lang_code = 'en' then
114 return 'US';
115 elsif l_lang_code = 'es' then
116 return 'E';
117 elsif l_lang_code = 'fr' then
118 return 'F';
119 elsif l_lang_code = 'pt' then
120 return 'PT';
121 -- this should no longer happen in OBIEE 11g as OBIEE 11g uses
122 -- 'zh-cn' for Simplified Chinese language code, while OBIEE 10g
123 -- uses 'zh' for Simplified Chinese.
124 elsif l_lang_code = 'zh' then
125 return 'ZHS';
126 else
127 return 'US';
128 end if;
129 when others then
130 return 'US';
131 end;
132 end if;
133 exception
134 when others then
135 return 'US';
136 end;
137
138 -- OBIEE and .DLF file both use ISO language codes with the following exceptions:
139 -- 1. Simplified Chinese 'zh'->'zh-CN' (this is only an issue in OBIEE 10g
140 -- or earlier versions as OBIEE 11g uses 'zh-cn' instead)
141 -- 2. Hebrew 'iw'->'he' (this is only an issue in OBIEE 10g or earlier
142 -- versions as OBIEE 11g uses 'he' instead)
143 -- 3. Language suffix is in lower case in OBIEE, while it is in upper case in .DLF file
144 -- Other input language codes are returned as is (in lower case form).
145 -- Unrecognized iput language codes are returned as is (in lower case form)
146 -- as well, just like the general matching case, since there isn't a list of valid
147 -- DLF language codes in the db for validation.
148 elsif (from_type = 'OBIEE') and (to_type = 'DLF') then
149 begin
150 if lang_code is null then
151 return 'en';
152 else
153 pos := instr(lang_code, '-');
154 lang_code_length := length(lang_code);
155 u_lang_code := upper(lang_code);
156 l_lang_code := lower(lang_code);
157 end if;
158
159 -- these two cases (for 'zh' and 'iw') only apply to OBIEE 10g
160 -- or earlier versions
161 if l_lang_code = 'zh' then
162 return 'zh-CN';
163 elsif l_lang_code = 'iw' then
164 return 'he';
165 -- for OBIEE language code with suffix, the territory part of the code is in lower case,
166 -- but for DLF, the territory part of the code is in upper case.
167 elsif pos > 0 then
168 begin
169 target_lang := substr(lang_code, 1, pos-1);
170 target_terr := substr(lang_code, pos+1, lang_code_length-pos);
171
172 if (target_lang is null) or (target_terr is null) then
173 return 'en';
174 else
175 target_lang_code := lower(target_lang) || '-' || upper(target_terr);
176 return target_lang_code;
177 end if;
178 exception
179 -- input code is invalid, return 'en'.
180 when others then
181 return 'en';
182 end;
183 -- OBIEE and DLF codes are the same;
184 -- or unrecognized code, return as is.
185 else
186 return l_lang_code;
187 end if;
188 exception
189 when others then
190 return 'en';
191 end;
192
193 -- .DLF file and OBIEE both use ISO language codes with the following exceptions:
194 -- 1. Simplified Chinese 'zh-CN'->'zh' (this is only an issue in OBIEE 10g or
195 -- earlier versions as OBIEE 11g uses 'zh-cn' instead)
196 -- 2. Hebrew 'he'->'iw' (this is only an issue in OBIEE 10g or earlier
197 -- versions as OBIEE 11g uses 'he' instead)
198 -- 3. Language suffix is in lower case in OBIEE, while it is in upper case in .DLF file
199 -- Other input language codes are returned as is (in lower case form).
200 -- Unrecognized/invalid input language codes are returned as is (in lower case form)
201 -- as well, just like the general matching case, since there isn't a list of valid
202 -- OBIEE language codes in the db for validation.
203 elsif (from_type = 'DLF') and (to_type = 'OBIEE') then
204 begin
205 if lang_code is null then
206 return 'en';
207 else
208 pos := instr(lang_code, '-');
209 lang_code_length := length(lang_code);
210 u_lang_code := upper(lang_code);
211 l_lang_code := lower(lang_code);
212 end if;
213
214 -- OBIEE 11g changed the language codes for Simplified Chinese and Hebrew
215 -- starting in OBIEE 11g such that OBIEE and DLF have the same codes
216 -- if l_lang_code = 'zh-cn' then
217 -- return 'zh';
218 -- elsif l_lang_code = 'he' then
219 -- return 'iw';
220 if pos > 0 then
221 begin
222 target_lang := substr(l_lang_code, 1, pos-1);
223 target_terr := substr(l_lang_code, pos+1, lang_code_length-pos);
224
225 if (target_lang is null) or (target_terr is null) then
226 return 'en';
227 else
228 target_lang_code := target_lang || '-' || target_terr;
229 return target_lang_code;
230 end if;
231 exception
232 -- input code is invalid, return 'en'.
233 when others then
234 return 'en';
235 end;
236 -- DLF and OBIEE codes are the same;
237 -- or input code is invalid, return as is.
238 else
239 return l_lang_code;
240 end if;
241 exception
242 when others then
243 return 'en';
244 end;
245
246 -- Invalid or unsupported conversion type
247 else
248 return null;
249 end if;
250
251 return target_lang_code;
252 end obiee_convert_langcode;
253
254 -- Convert between OBIEE language code and Oracle language (long form, e.g. 'AMERICAN').
255 -- Note that all the conversion types specified have to be in upper cases.
256 -- Invalid conversion types will return null;
257 -- Unrecognized language input will return 'en' or 'AMERICAN'.
258 function obiee_convert_language (from_type varchar2, to_type varchar2, lang varchar2) return varchar2 is
259 target_language varchar2(30) := null;
260 target_lang_code varchar2(10) := null;
261 begin
262
263 if from_type = 'ORACLE' and to_type = 'OBIEE' then
264 begin
265 if lang is null then
266 return 'en';
267 end if;
268
269 select fl.language_code
270 into target_lang_code
271 from fnd_languages fl
272 where fl.nls_language = upper(lang);
273
274 return obiee_convert_langcode('ORACLE', 'OBIEE', target_lang_code);
275 exception
276 when others then
277 return 'en';
278 end;
279 elsif from_type = 'OBIEE' and to_type = 'ORACLE' then
280 begin
281 if lang is null then
282 return 'AMERICAN';
283 end if;
284
285 target_lang_code := obiee_convert_langcode ('OBIEE', 'ORACLE', lang);
286
287 select fl.nls_language
288 into target_language
289 from fnd_languages fl
290 where fl.language_code = upper(target_lang_code);
291
292 return target_language;
293 exception
294 when others then
295 return 'AMERICAN';
296 end;
297 -- invalid/unsupported conversion type
298 else
299 return null;
300 end if;
301 end obiee_convert_language;
302
303
304 -- Convert EBS session language to OBIEE session language code
305 function obiee_session_langcode return varchar2 is
306 begin
307 return obiee_convert_langcode ('ORACLE', 'OBIEE',
308 fnd_global.current_language);
309 end obiee_session_langcode;
310
311
312 -- Convert EBS session language and session territory to OBIEE session locale;
313 -- Since there could be more than one record for the same territory in
314 -- FND_TERRITORIES table, the OBSOLETE_FLAG has to be 'N'
315 function obiee_session_locale return varchar2 is
316 o_locale varchar2(10) := null;
317 begin
318 select lower(fl.ISO_LANGUAGE)||'-'||lower(ft.TERRITORY_CODE)
319 into o_locale
320 from fnd_languages fl, FND_TERRITORIES ft
321 where fnd_global.nls_language = fl.nls_language
322 and fnd_global.nls_territory = ft.nls_territory
323 and ft.OBSOLETE_FLAG = 'N';
324
325 return o_locale;
326 exception
327 when others then
328 return 'en-us';
329 end obiee_session_locale;
330
331
332 -- Convert OBIEE language code to an installed Oracle language code;
333 -- i.e. if the matching Oracle language code is not installed, the Oracle
334 -- base language code will be returned
335 function oracle_installed_langcode (lang varchar2) return varchar2 is
336 target_lang_code varchar2(10) := null;
337 base_lang_code varchar2(10) := null;
338 install_status varchar2(1) := 'D';
339 begin
340 -- Get the base language code
341 select language_code
342 into base_lang_code
343 from fnd_languages
344 where installed_flag = 'B';
345
346 if (lang is null) then
347 return base_lang_code;
348 else
349 begin
350 target_lang_code := obiee_convert_langcode ('OBIEE', 'ORACLE', lang);
351
352 -- if target_lang_code was defaulted to 'US', return base language
353 -- code instead of 'US'
354 if (target_lang_code = 'US') and (lower(lang) <> 'en') then
355 return base_lang_code;
356 else
357 begin
358 select installed_flag
359 into install_status
360 from fnd_languages
361 where language_code = target_lang_code;
362
363 -- If the matching Oracle language is not installed, return
364 -- the base language code.
365 if (install_status = 'I') or (install_status = 'B') then
366 return target_lang_code;
367 else
368 return base_lang_code;
369 end if;
370 exception
371 when others then
372 return base_lang_code;
373 end;
374 end if;
375 exception
376 when others then
377 return base_lang_code;
378 end;
379 end if;
380
381 exception
382 when others then
383 return base_lang_code;
384 end oracle_installed_langcode;
385
386
387 -- Convert OBIEE language code to an installed Oracle language, i.e.
388 -- if the matching Oracle language is not installed, the Oracle
389 -- base language will be returned.
390 function oracle_installed_language (lang varchar2) return varchar2 is
391 target_language varchar2(30) := null;
392 target_lang_code varchar2(10) := null;
393 begin
394 target_lang_code := oracle_installed_langcode (lang);
395
396 select nls_language
397 into target_language
398 from fnd_languages
399 where language_code = target_lang_code;
400
401 return target_language;
402
403 exception
404 when others then
405 return null;
406 end oracle_installed_language;
407
408
409 end fnd_obiee_i18n;
[Home] [Help]