[Home] [Help]
PACKAGE BODY: APPS.FND_LANGUAGES_PKG
Source
1 package body FND_LANGUAGES_PKG as
2 /* $Header: AFNLDLGB.pls 120.4 2006/03/08 06:21:25 jvalenti ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_LANGUAGE_CODE in VARCHAR2,
7 X_LANGUAGE_ID in NUMBER,
8 X_NLS_LANGUAGE in VARCHAR2,
9 X_NLS_TERRITORY in VARCHAR2,
10 X_ISO_LANGUAGE in VARCHAR2,
11 X_ISO_TERRITORY in VARCHAR2,
12 X_NLS_CODESET in VARCHAR2,
13 X_INSTALLED_FLAG in VARCHAR2,
14 X_DESCRIPTION in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER,
20 X_LOCAL_DATE_LANGUAGE in VARCHAR2,
21 X_UTF8_DATE_LANGUAGE in VARCHAR2,
22 X_ISO_LANGUAGE_3 in VARCHAR2
23 ) is
24 cursor C is select ROWID from FND_LANGUAGES
25 where LANGUAGE_CODE = X_LANGUAGE_CODE
26 ;
27 begin
28 insert into FND_LANGUAGES (
29 LANGUAGE_CODE,
30 LANGUAGE_ID,
31 NLS_LANGUAGE,
32 NLS_TERRITORY,
33 ISO_LANGUAGE,
34 ISO_TERRITORY,
35 NLS_CODESET,
36 INSTALLED_FLAG,
37 CREATION_DATE,
38 CREATED_BY,
39 LAST_UPDATE_DATE,
40 LAST_UPDATED_BY,
41 LAST_UPDATE_LOGIN,
42 LOCAL_DATE_LANGUAGE,
43 UTF8_DATE_LANGUAGE,
44 ISO_LANGUAGE_3
45 ) values (
46 X_LANGUAGE_CODE,
47 X_LANGUAGE_ID,
48 X_NLS_LANGUAGE,
49 X_NLS_TERRITORY,
50 X_ISO_LANGUAGE,
51 X_ISO_TERRITORY,
52 X_NLS_CODESET,
53 X_INSTALLED_FLAG,
54 X_CREATION_DATE,
55 X_CREATED_BY,
56 X_LAST_UPDATE_DATE,
57 X_LAST_UPDATED_BY,
58 X_LAST_UPDATE_LOGIN,
59 X_LOCAL_DATE_LANGUAGE,
60 X_UTF8_DATE_LANGUAGE,
61 X_ISO_LANGUAGE_3
62 );
63
64 insert into FND_LANGUAGES_TL (
65 LANGUAGE_CODE,
66 DESCRIPTION,
67 CREATED_BY,
68 CREATION_DATE,
69 LAST_UPDATED_BY,
70 LAST_UPDATE_DATE,
71 LAST_UPDATE_LOGIN,
72 LANGUAGE,
73 SOURCE_LANG
74 ) select
75 X_LANGUAGE_CODE,
76 X_DESCRIPTION,
77 X_CREATED_BY,
78 X_CREATION_DATE,
79 X_LAST_UPDATED_BY,
80 X_LAST_UPDATE_DATE,
81 X_LAST_UPDATE_LOGIN,
82 L.LANGUAGE_CODE,
83 userenv('LANG')
84 from FND_LANGUAGES L
85 where L.INSTALLED_FLAG in ('I', 'B')
86 and not exists
87 (select NULL
88 from FND_LANGUAGES_TL T
89 where T.LANGUAGE_CODE = X_LANGUAGE_CODE
90 and T.LANGUAGE = L.LANGUAGE_CODE);
91
92 open c;
93 fetch c into X_ROWID;
94 if (c%notfound) then
95 close c;
96 raise no_data_found;
97 end if;
98 close c;
99
100 end INSERT_ROW;
101
102 procedure LOCK_ROW (
103 X_LANGUAGE_CODE in VARCHAR2,
104 X_LANGUAGE_ID in NUMBER,
105 X_NLS_LANGUAGE in VARCHAR2,
106 X_NLS_TERRITORY in VARCHAR2,
107 X_ISO_LANGUAGE in VARCHAR2,
108 X_ISO_TERRITORY in VARCHAR2,
109 X_NLS_CODESET in VARCHAR2,
110 X_INSTALLED_FLAG in VARCHAR2,
111 X_DESCRIPTION in VARCHAR2
112 ) is
113 cursor c is select
114 LANGUAGE_ID,
115 NLS_LANGUAGE,
116 NLS_TERRITORY,
117 ISO_LANGUAGE,
118 ISO_TERRITORY,
119 NLS_CODESET,
120 INSTALLED_FLAG
121 from FND_LANGUAGES
122 where LANGUAGE_CODE = X_LANGUAGE_CODE
123 for update of LANGUAGE_CODE nowait;
124 recinfo c%rowtype;
125
126 cursor c1 is select
127 DESCRIPTION
128 from FND_LANGUAGES_TL
129 where LANGUAGE_CODE = X_LANGUAGE_CODE
130 and LANGUAGE = userenv('LANG')
131 for update of LANGUAGE_CODE nowait;
132 tlinfo c1%rowtype;
133
134 begin
135 open c;
136 fetch c into recinfo;
137 if (c%notfound) then
138 close c;
139 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
140 app_exception.raise_exception;
141 end if;
142 close c;
143 if ( ((recinfo.LANGUAGE_ID = X_LANGUAGE_ID)
144 OR ((recinfo.LANGUAGE_ID is null) AND (X_LANGUAGE_ID is null)))
145 AND (recinfo.NLS_LANGUAGE = X_NLS_LANGUAGE)
146 AND (recinfo.NLS_TERRITORY = X_NLS_TERRITORY)
147 AND ((recinfo.ISO_LANGUAGE = X_ISO_LANGUAGE)
148 OR ((recinfo.ISO_LANGUAGE is null) AND (X_ISO_LANGUAGE is null)))
149 AND ((recinfo.ISO_TERRITORY = X_ISO_TERRITORY)
150 OR ((recinfo.ISO_TERRITORY is null) AND (X_ISO_TERRITORY is null)))
151 AND ((recinfo.NLS_CODESET = X_NLS_CODESET)
152 OR ((recinfo.NLS_CODESET is null) AND (X_NLS_CODESET is null)))
153 AND (recinfo.INSTALLED_FLAG = X_INSTALLED_FLAG)
154 ) then
155 null;
156 else
157 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
158 app_exception.raise_exception;
159 end if;
160
161 open c1;
162 fetch c1 into tlinfo;
163 if (c1%notfound) then
164 close c1;
165 return;
166 end if;
167 close c1;
168
169 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
170 ) then
171 null;
172 else
173 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
174 app_exception.raise_exception;
175 end if;
176 return;
177 end LOCK_ROW;
178
179 procedure UPDATE_ROW (
180 X_LANGUAGE_CODE in VARCHAR2,
181 X_LANGUAGE_ID in NUMBER,
182 X_NLS_LANGUAGE in VARCHAR2,
183 X_NLS_TERRITORY in VARCHAR2,
184 X_ISO_LANGUAGE in VARCHAR2,
185 X_ISO_TERRITORY in VARCHAR2,
186 X_NLS_CODESET in VARCHAR2,
187 X_INSTALLED_FLAG in VARCHAR2,
188 X_DESCRIPTION in VARCHAR2,
189 X_LAST_UPDATE_DATE in DATE,
190 X_LAST_UPDATED_BY in NUMBER,
191 X_LAST_UPDATE_LOGIN in NUMBER,
192 X_LOCAL_DATE_LANGUAGE in VARCHAR2,
193 X_UTF8_DATE_LANGUAGE in VARCHAR2,
194 X_ISO_LANGUAGE_3 in VARCHAR2
195 ) is
196
197 begin
198
199 update FND_LANGUAGES set
200 LANGUAGE_ID = X_LANGUAGE_ID,
201 NLS_LANGUAGE = X_NLS_LANGUAGE,
202 NLS_TERRITORY = X_NLS_TERRITORY,
203 ISO_LANGUAGE = X_ISO_LANGUAGE,
204 ISO_TERRITORY = X_ISO_TERRITORY,
205 NLS_CODESET = X_NLS_CODESET,
206 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
207 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
208 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
209 LOCAL_DATE_LANGUAGE = X_LOCAL_DATE_LANGUAGE,
210 UTF8_DATE_LANGUAGE = X_UTF8_DATE_LANGUAGE,
211 ISO_LANGUAGE_3 = X_ISO_LANGUAGE_3
212 where LANGUAGE_CODE = X_LANGUAGE_CODE;
213
214 if (sql%notfound) then
215 raise no_data_found;
216 end if;
217
218 update FND_LANGUAGES_TL set
219 DESCRIPTION = X_DESCRIPTION,
220 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
221 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
222 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
223 SOURCE_LANG = userenv('LANG')
224 where LANGUAGE_CODE = X_LANGUAGE_CODE
225 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
226
227 if (sql%notfound) then
228 raise no_data_found;
229 end if;
230 end UPDATE_ROW;
231
232 procedure DELETE_ROW (
233 X_LANGUAGE_CODE in VARCHAR2
234 ) is
235 begin
236 delete from FND_LANGUAGES
237 where LANGUAGE_CODE = X_LANGUAGE_CODE;
238
239 if (sql%notfound) then
240 raise no_data_found;
241 end if;
242
243 delete from FND_LANGUAGES_TL
244 where LANGUAGE_CODE = X_LANGUAGE_CODE;
245
246 if (sql%notfound) then
247 raise no_data_found;
248 end if;
249 end DELETE_ROW;
250
251 procedure ADD_LANGUAGE
252 is
253 begin
254 /* Mar/19/03 requested by Ric Ginsberg */
255 /* The following delete and update statements are commented out */
256 /* as a quick workaround to fix the time-consuming table handler issue */
257 /* Eventually we'll need to turn them into a separate fix_language procedure */
258 /*
259
260 delete from FND_LANGUAGES_TL T
261 where not exists
262 (select NULL
263 from FND_LANGUAGES B
264 where B.LANGUAGE_CODE = T.LANGUAGE_CODE
265 );
266
267 update FND_LANGUAGES_TL T set (
268 DESCRIPTION
269 ) = (select
270 B.DESCRIPTION
271 from FND_LANGUAGES_TL B
272 where B.LANGUAGE_CODE = T.LANGUAGE_CODE
273 and B.LANGUAGE = T.SOURCE_LANG)
274 where (
275 T.LANGUAGE_CODE,
276 T.LANGUAGE
277 ) in (select
278 SUBT.LANGUAGE_CODE,
279 SUBT.LANGUAGE
280 from FND_LANGUAGES_TL SUBB, FND_LANGUAGES_TL SUBT
281 where SUBB.LANGUAGE_CODE = SUBT.LANGUAGE_CODE
282 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
283 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
284 ));
285 */
286
287 insert into FND_LANGUAGES_TL (
288 LANGUAGE_CODE,
289 DESCRIPTION,
290 CREATED_BY,
291 CREATION_DATE,
292 LAST_UPDATED_BY,
293 LAST_UPDATE_DATE,
294 LAST_UPDATE_LOGIN,
295 LANGUAGE,
296 SOURCE_LANG
297 ) select
298 B.LANGUAGE_CODE,
299 B.DESCRIPTION,
300 B.CREATED_BY,
301 B.CREATION_DATE,
302 B.LAST_UPDATED_BY,
303 B.LAST_UPDATE_DATE,
304 B.LAST_UPDATE_LOGIN,
305 L.LANGUAGE_CODE,
306 B.SOURCE_LANG
307 from FND_LANGUAGES_TL B, FND_LANGUAGES L
308 where L.INSTALLED_FLAG in ('I', 'B')
309 and B.LANGUAGE = userenv('LANG')
310 and not exists
311 (select NULL
312 from FND_LANGUAGES_TL T
313 where T.LANGUAGE_CODE = B.LANGUAGE_CODE
314 and T.LANGUAGE = L.LANGUAGE_CODE);
315 end ADD_LANGUAGE;
316
317 procedure TRANSLATE_ROW (
318 X_LANGUAGE_CODE in VARCHAR2,
319 X_DESCRIPTION in VARCHAR2,
320 X_OWNER in VARCHAR2
321 ) is
322 begin
323 TRANSLATE_ROW (
324 X_LANGUAGE_CODE => X_LANGUAGE_CODE,
325 X_DESCRIPTION => X_DESCRIPTION,
326 X_OWNER => X_OWNER,
327 X_LAST_UPDATE_DATE => null,
328 X_CUSTOM_MODE => null);
329 end TRANSLATE_ROW;
330
331 procedure LOAD_ROW (
332 X_LANGUAGE_CODE in VARCHAR2,
333 X_DESCRIPTION in VARCHAR2,
334 X_LANGUAGE_ID in NUMBER,
335 X_NLS_LANGUAGE in VARCHAR2,
336 X_NLS_TERRITORY in VARCHAR2,
337 X_ISO_LANGUAGE in VARCHAR2,
338 X_ISO_TERRITORY in VARCHAR2,
339 X_NLS_CODESET in VARCHAR2,
340 X_INSTALLED_FLAG in VARCHAR2,
341 X_LOCAL_DATE_LANGUAGE in VARCHAR2,
342 X_UTF8_DATE_LANGUAGE in VARCHAR2,
343 X_OWNER in VARCHAR2
344 ) is
345 begin
346 LOAD_ROW (
347 X_LANGUAGE_CODE => X_LANGUAGE_CODE,
348 X_DESCRIPTION => X_DESCRIPTION,
349 X_LANGUAGE_ID => X_LANGUAGE_ID,
350 X_NLS_LANGUAGE => X_NLS_LANGUAGE,
351 X_NLS_TERRITORY => X_NLS_TERRITORY,
352 X_ISO_LANGUAGE => X_ISO_LANGUAGE,
353 X_ISO_TERRITORY => X_ISO_TERRITORY,
354 X_NLS_CODESET => X_NLS_CODESET,
355 X_INSTALLED_FLAG => X_INSTALLED_FLAG,
356 X_LOCAL_DATE_LANGUAGE => X_LOCAL_DATE_LANGUAGE,
357 X_UTF8_DATE_LANGUAGE => X_UTF8_DATE_LANGUAGE,
358 X_OWNER => X_OWNER,
359 X_LAST_UPDATE_DATE => null,
360 X_CUSTOM_MODE => null);
361 end LOAD_ROW;
362
363 procedure LOAD_ROW (
364 X_LANGUAGE_CODE in VARCHAR2,
365 X_DESCRIPTION in VARCHAR2,
366 X_LANGUAGE_ID in NUMBER,
367 X_NLS_LANGUAGE in VARCHAR2,
368 X_NLS_TERRITORY in VARCHAR2,
369 X_ISO_LANGUAGE in VARCHAR2,
370 X_ISO_TERRITORY in VARCHAR2,
371 X_NLS_CODESET in VARCHAR2,
372 X_INSTALLED_FLAG in VARCHAR2,
373 X_LOCAL_DATE_LANGUAGE in VARCHAR2,
374 X_UTF8_DATE_LANGUAGE in VARCHAR2,
375 X_OWNER in VARCHAR2,
376 X_LAST_UPDATE_DATE in VARCHAR2,
377 X_CUSTOM_MODE in VARCHAR2
378 ) is
379 begin
380 LOAD_ROW (
381 X_LANGUAGE_CODE => X_LANGUAGE_CODE,
382 X_DESCRIPTION => X_DESCRIPTION,
383 X_LANGUAGE_ID => X_LANGUAGE_ID,
384 X_NLS_LANGUAGE => X_NLS_LANGUAGE,
385 X_NLS_TERRITORY => X_NLS_TERRITORY,
386 X_ISO_LANGUAGE => X_ISO_LANGUAGE,
387 X_ISO_TERRITORY => X_ISO_TERRITORY,
388 X_NLS_CODESET => X_NLS_CODESET,
389 X_INSTALLED_FLAG => X_INSTALLED_FLAG,
390 X_LOCAL_DATE_LANGUAGE => X_LOCAL_DATE_LANGUAGE,
391 X_UTF8_DATE_LANGUAGE => X_UTF8_DATE_LANGUAGE,
392 X_ISO_LANGUAGE_3 => null,
393 X_OWNER => X_OWNER,
394 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
395 X_CUSTOM_MODE => X_CUSTOM_MODE);
396 end LOAD_ROW;
397
398 procedure TRANSLATE_ROW (
399 X_LANGUAGE_CODE in VARCHAR2,
400 X_DESCRIPTION in VARCHAR2,
401 X_OWNER in VARCHAR2,
402 X_LAST_UPDATE_DATE in VARCHAR2,
403 X_CUSTOM_MODE in VARCHAR2
404 ) is
405 f_luby number; -- entity owner in file
406 f_ludate date; -- entity update date in file
407 db_luby number; -- entity owner in db
408 db_ludate date; -- entity update date in db
409
410 begin
411 -- Translate owner to file_last_updated_by
412 f_luby := fnd_load_util.owner_id(x_owner);
413
414 -- Translate char last_update_date to date
415 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
416
417 begin
418 select last_updated_by, last_update_date
419 into db_luby, db_ludate
420 from fnd_languages_tl
421 where language_code = X_LANGUAGE_CODE
422 and language = userenv('LANG');
423
424 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
428 LAST_UPDATE_DATE = f_ludate,
425 db_ludate, X_CUSTOM_MODE)) then
426 update FND_LANGUAGES_TL set
427 DESCRIPTION = X_DESCRIPTION,
429 LAST_UPDATED_BY = f_luby,
430 LAST_UPDATE_LOGIN = 0,
431 SOURCE_LANG = userenv('LANG')
432 where LANGUAGE_CODE = X_LANGUAGE_CODE
433 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
434 end if;
435 exception
436 when no_data_found then
437 null;
438 end;
439 end TRANSLATE_ROW;
440
441 procedure LOAD_ROW (
442 X_LANGUAGE_CODE in VARCHAR2,
443 X_DESCRIPTION in VARCHAR2,
444 X_LANGUAGE_ID in NUMBER,
445 X_NLS_LANGUAGE in VARCHAR2,
446 X_NLS_TERRITORY in VARCHAR2,
447 X_ISO_LANGUAGE in VARCHAR2,
448 X_ISO_TERRITORY in VARCHAR2,
449 X_NLS_CODESET in VARCHAR2,
450 X_INSTALLED_FLAG in VARCHAR2,
451 X_LOCAL_DATE_LANGUAGE in VARCHAR2,
452 X_UTF8_DATE_LANGUAGE in VARCHAR2,
453 X_ISO_LANGUAGE_3 in VARCHAR2,
454 X_OWNER in VARCHAR2,
455 X_LAST_UPDATE_DATE in VARCHAR2,
456 X_CUSTOM_MODE in VARCHAR2
457 ) is
458 user_id NUMBER;
459 X_ROWID VARCHAR2(64);
460 f_luby number; -- entity owner in file
461 f_ludate date; -- entity update date in file
462 db_luby number; -- entity owner in db
463 db_ludate date; -- entity update date in db
464
465 -- Bug4493112 Moved local variables from UPDATE_ROW to LOAD_ROW.
466
467 L_LANGUAGE_ID NUMBER;
468 L_ISO_LANGUAGE VARCHAR2(2);
469 L_ISO_TERRITORY VARCHAR2(2);
470 L_NLS_CODESET VARCHAR2(30);
471 L_LOCAL_DATE_LANGUAGE VARCHAR2(30);
472 L_UTF8_DATE_LANGUAGE VARCHAR2(30);
473 L_ISO_LANGUAGE_3 VARCHAR2(3);
474
475 begin
476
477 -- Translate owner to file_last_updated_by
478 f_luby := fnd_load_util.owner_id(x_owner);
479
480 -- Translate char last_update_date to date
481 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
482
483 begin
484
485 select LAST_UPDATED_BY, LAST_UPDATE_DATE
486 into db_luby, db_ludate
487 from fnd_languages
488 where language_code = X_LANGUAGE_CODE;
489
490 -- Bug4493112 Moved decode select from UPDATE_ROW to LOAD_ROW.
491 -- Bug4648984 Moved sql to inside exception block to handle the
492 -- no data found.
493
494 select
495 decode(x_language_id, fnd_languages_pkg.null_number, null,
496 null, u.language_id,
497 x_language_id),
498 decode(x_iso_language, fnd_languages_pkg.null_char, null,
499 null, u.iso_language,
500 x_iso_language),
501 decode(x_iso_territory, fnd_languages_pkg.null_char, null,
502 null, u.iso_territory,
503 x_iso_territory),
504 decode(x_nls_codeset, fnd_languages_pkg.null_char, null,
505 null, u.nls_codeset,
506 x_nls_codeset),
507 decode(x_local_date_language, fnd_languages_pkg.null_char, null,
508 null, u.local_date_language,
509 x_local_date_language),
510 decode(x_utf8_date_language, fnd_languages_pkg.null_char, null,
511 null, u.utf8_date_language,
512 x_utf8_date_language),
513 decode(x_iso_language_3, fnd_languages_pkg.null_char, null,
514 null, u.iso_language_3,
515 x_iso_language_3)
516 into l_language_id,l_iso_language,l_iso_territory,l_nls_codeset,
517 l_local_date_language, l_utf8_date_language, l_iso_language_3
518 from fnd_languages U
519 where language_code = X_language_code;
520
521 -- Bug4493112 Modify code to use local variables for UPDATE_ROW and
522 -- INSERT_ROW.
523
524 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
525 db_ludate, X_CUSTOM_MODE)) then
526 FND_LANGUAGES_PKG.UPDATE_ROW(
527 X_LANGUAGE_CODE,
528 L_LANGUAGE_ID,
529 X_NLS_LANGUAGE,
530 X_NLS_TERRITORY,
531 L_ISO_LANGUAGE,
532 L_ISO_TERRITORY,
533 L_NLS_CODESET,
534 X_INSTALLED_FLAG,
535 X_DESCRIPTION,
536 f_ludate,
537 f_luby,
538 0,
539 L_LOCAL_DATE_LANGUAGE,
540 L_UTF8_DATE_LANGUAGE,
541 L_ISO_LANGUAGE_3);
542 end if;
543
544 exception
545 when no_data_found then
546 FND_LANGUAGES_PKG.INSERT_ROW(
547 X_ROWID,
548 X_LANGUAGE_CODE,
549 X_LANGUAGE_ID,
550 X_NLS_LANGUAGE,
551 X_NLS_TERRITORY,
552 X_ISO_LANGUAGE,
553 X_ISO_TERRITORY,
554 X_NLS_CODESET,
555 X_INSTALLED_FLAG,
556 X_DESCRIPTION,
557 f_ludate,
558 f_luby,
559 f_ludate,
560 f_luby,
561 0,
562 X_LOCAL_DATE_LANGUAGE,
563 X_UTF8_DATE_LANGUAGE,
564 X_ISO_LANGUAGE_3);
565 end;
566 end LOAD_ROW;
567 end FND_LANGUAGES_PKG;