[Home] [Help]
PACKAGE BODY: APPS.FND_ISO_LANGUAGES_PKG
Source
1 package body FND_ISO_LANGUAGES_PKG as
2 /* $Header: AFNLISOB.pls 115.1 2004/04/15 22:15:15 rsuzuki noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_ISO_LANGUAGE_3 in VARCHAR2,
7 X_ISO_LANGUAGE_2 in VARCHAR2,
8 X_PRIVATE_USE_FLAG in VARCHAR2,
9 X_NAME in VARCHAR2,
10 X_DESCRIPTION in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 cursor C is select ROWID from FND_ISO_LANGUAGES
18 where ISO_LANGUAGE_3 = X_ISO_LANGUAGE_3
19 ;
20 begin
21 insert into FND_ISO_LANGUAGES (
22 ISO_LANGUAGE_3,
23 ISO_LANGUAGE_2,
24 PRIVATE_USE_FLAG,
25 CREATION_DATE,
26 CREATED_BY,
27 LAST_UPDATE_DATE,
28 LAST_UPDATED_BY,
29 LAST_UPDATE_LOGIN
30 ) values (
31 X_ISO_LANGUAGE_3,
32 X_ISO_LANGUAGE_2,
33 X_PRIVATE_USE_FLAG,
34 X_CREATION_DATE,
35 X_CREATED_BY,
36 X_LAST_UPDATE_DATE,
37 X_LAST_UPDATED_BY,
38 X_LAST_UPDATE_LOGIN
39 );
40
41 insert into FND_ISO_LANGUAGES_TL (
42 ISO_LANGUAGE_3,
43 NAME,
44 DESCRIPTION,
45 CREATED_BY,
46 CREATION_DATE,
47 LAST_UPDATED_BY,
48 LAST_UPDATE_DATE,
49 LAST_UPDATE_LOGIN,
50 LANGUAGE,
51 SOURCE_LANG
52 ) select
53 X_ISO_LANGUAGE_3,
54 X_NAME,
55 X_DESCRIPTION,
56 X_CREATED_BY,
57 X_CREATION_DATE,
58 X_LAST_UPDATED_BY,
59 X_LAST_UPDATE_DATE,
60 X_LAST_UPDATE_LOGIN,
61 L.LANGUAGE_CODE,
62 userenv('LANG')
63 from FND_LANGUAGES L
64 where L.INSTALLED_FLAG in ('I', 'B')
65 and not exists
66 (select NULL
67 from FND_ISO_LANGUAGES_TL T
68 where T.ISO_LANGUAGE_3 = X_ISO_LANGUAGE_3
69 and T.LANGUAGE = L.LANGUAGE_CODE);
70
71 open c;
72 fetch c into X_ROWID;
73 if (c%notfound) then
74 close c;
75 raise no_data_found;
76 end if;
77 close c;
78
79 end INSERT_ROW;
80
81 procedure LOCK_ROW (
82 X_ISO_LANGUAGE_3 in VARCHAR2,
83 X_ISO_LANGUAGE_2 in VARCHAR2,
84 X_PRIVATE_USE_FLAG in VARCHAR2,
85 X_NAME in VARCHAR2,
86 X_DESCRIPTION in VARCHAR2
87 ) is
88 cursor c is select
89 ISO_LANGUAGE_2,
90 PRIVATE_USE_FLAG
91 from FND_ISO_LANGUAGES
92 where ISO_LANGUAGE_3 = X_ISO_LANGUAGE_3
93 for update of ISO_LANGUAGE_3 nowait;
94 recinfo c%rowtype;
95
96 cursor c1 is select
97 NAME,
98 DESCRIPTION,
99 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
100 from FND_ISO_LANGUAGES_TL
101 where ISO_LANGUAGE_3 = X_ISO_LANGUAGE_3
102 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
103 for update of ISO_LANGUAGE_3 nowait;
104 begin
105 open c;
106 fetch c into recinfo;
107 if (c%notfound) then
108 close c;
109 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
110 app_exception.raise_exception;
111 end if;
112 close c;
113 if ( ((recinfo.ISO_LANGUAGE_2 = X_ISO_LANGUAGE_2)
114 OR ((recinfo.ISO_LANGUAGE_2 is null) AND (X_ISO_LANGUAGE_2 is null)))
115 AND (recinfo.PRIVATE_USE_FLAG = X_PRIVATE_USE_FLAG)
116 ) then
117 null;
118 else
119 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
120 app_exception.raise_exception;
121 end if;
122
123 for tlinfo in c1 loop
124 if (tlinfo.BASELANG = 'Y') then
125 if ( (tlinfo.NAME = X_NAME)
126 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
127 ) then
128 null;
129 else
130 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
131 app_exception.raise_exception;
132 end if;
133 end if;
134 end loop;
135 return;
136 end LOCK_ROW;
137
138 procedure UPDATE_ROW (
139 X_ISO_LANGUAGE_3 in VARCHAR2,
140 X_ISO_LANGUAGE_2 in VARCHAR2,
141 X_PRIVATE_USE_FLAG in VARCHAR2,
142 X_NAME in VARCHAR2,
143 X_DESCRIPTION in VARCHAR2,
144 X_LAST_UPDATE_DATE in DATE,
145 X_LAST_UPDATED_BY in NUMBER,
146 X_LAST_UPDATE_LOGIN in NUMBER
147 ) is
148 begin
149 update FND_ISO_LANGUAGES set
150 ISO_LANGUAGE_2 = X_ISO_LANGUAGE_2,
151 PRIVATE_USE_FLAG = X_PRIVATE_USE_FLAG,
152 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
153 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
154 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
155 where ISO_LANGUAGE_3 = X_ISO_LANGUAGE_3;
156
157 if (sql%notfound) then
158 raise no_data_found;
159 end if;
160
161 update FND_ISO_LANGUAGES_TL set
162 NAME = X_NAME,
163 DESCRIPTION = X_DESCRIPTION,
164 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
165 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
166 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
167 SOURCE_LANG = userenv('LANG')
168 where ISO_LANGUAGE_3 = X_ISO_LANGUAGE_3
169 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
170
171 if (sql%notfound) then
172 raise no_data_found;
173 end if;
174 end UPDATE_ROW;
175
176 procedure DELETE_ROW (
177 X_ISO_LANGUAGE_3 in VARCHAR2
178 ) is
179 begin
180 delete from FND_ISO_LANGUAGES_TL
181 where ISO_LANGUAGE_3 = X_ISO_LANGUAGE_3;
182
183 if (sql%notfound) then
184 raise no_data_found;
185 end if;
186
187 delete from FND_ISO_LANGUAGES
188 where ISO_LANGUAGE_3 = X_ISO_LANGUAGE_3;
189
190 if (sql%notfound) then
191 raise no_data_found;
192 end if;
193 end DELETE_ROW;
194
195 procedure ADD_LANGUAGE
196 is
197 begin
198 /* Mar/19/03 requested by Ric Ginsberg */
199 /* The following update statements are commented out */
200 /* as a quick workaround to fix the time-consuming table handler issue */
201 /* Eventually we'll need to turn them into a separate fix_language procedure */
202 /*
203 delete from FND_ISO_LANGUAGES_TL T
204 where not exists
205 (select NULL
206 from FND_ISO_LANGUAGES B
207 where B.ISO_LANGUAGE_3 = T.ISO_LANGUAGE_3
208 );
209
210 update FND_ISO_LANGUAGES_TL T set (
211 NAME,
212 DESCRIPTION
213 ) = (select
214 B.NAME,
215 B.DESCRIPTION
216 from FND_ISO_LANGUAGES_TL B
217 where B.ISO_LANGUAGE_3 = T.ISO_LANGUAGE_3
218 and B.LANGUAGE = T.SOURCE_LANG)
219 where (
220 T.ISO_LANGUAGE_3,
221 T.LANGUAGE
222 ) in (select
223 SUBT.ISO_LANGUAGE_3,
224 SUBT.LANGUAGE
225 from FND_ISO_LANGUAGES_TL SUBB, FND_ISO_LANGUAGES_TL SUBT
226 where SUBB.ISO_LANGUAGE_3 = SUBT.ISO_LANGUAGE_3
227 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
228 and (SUBB.NAME <> SUBT.NAME
229 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
230 ));
231 */
232
233 insert into FND_ISO_LANGUAGES_TL (
234 ISO_LANGUAGE_3,
235 NAME,
236 DESCRIPTION,
237 CREATED_BY,
238 CREATION_DATE,
239 LAST_UPDATED_BY,
240 LAST_UPDATE_DATE,
241 LAST_UPDATE_LOGIN,
242 LANGUAGE,
243 SOURCE_LANG
244 ) select /*+ ORDERED */
245 B.ISO_LANGUAGE_3,
246 B.NAME,
247 B.DESCRIPTION,
248 B.CREATED_BY,
249 B.CREATION_DATE,
250 B.LAST_UPDATED_BY,
251 B.LAST_UPDATE_DATE,
252 B.LAST_UPDATE_LOGIN,
253 L.LANGUAGE_CODE,
254 B.SOURCE_LANG
255 from FND_ISO_LANGUAGES_TL B, FND_LANGUAGES L
256 where L.INSTALLED_FLAG in ('I', 'B')
257 and B.LANGUAGE = userenv('LANG')
258 and not exists
259 (select NULL
260 from FND_ISO_LANGUAGES_TL T
261 where T.ISO_LANGUAGE_3 = B.ISO_LANGUAGE_3
262 and T.LANGUAGE = L.LANGUAGE_CODE);
263 end ADD_LANGUAGE;
264
265 procedure TRANSLATE_ROW (
266 X_ISO_LANGUAGE_3 in VARCHAR2,
267 X_NAME in VARCHAR2,
268 X_DESCRIPTION in VARCHAR2,
269 X_OWNER in VARCHAR2,
270 X_LAST_UPDATE_DATE in VARCHAR2,
271 X_CUSTOM_MODE in VARCHAR2
272 ) is
273 f_luby number; -- entity owner in file
274 f_ludate date; -- entity update date in file
275 db_luby number; -- entity owner in db
276 db_ludate date; -- entity update date in db
277
278 begin
279 -- Translate owner to file_last_updated_by
280 f_luby := fnd_load_util.owner_id(x_owner);
281
282 -- Translate char last_update_date to date
283 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
284
285 begin
286 select last_updated_by, last_update_date
287 into db_luby, db_ludate
288 from fnd_iso_languages_tl
289 where iso_language_3 = X_ISO_LANGUAGE_3
290 and language = userenv('LANG');
291
292 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
293 db_ludate, X_CUSTOM_MODE)) then
294 update FND_ISO_LANGUAGES_TL set
295 NAME = X_NAME,
296 DESCRIPTION = X_DESCRIPTION,
297 LAST_UPDATE_DATE = f_ludate,
298 LAST_UPDATED_BY = f_luby,
299 LAST_UPDATE_LOGIN = 0,
300 SOURCE_LANG = userenv('LANG')
301 where ISO_LANGUAGE_3 = X_ISO_LANGUAGE_3
302 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
303 end if;
304 exception
305 when no_data_found then
306 null;
307 end;
308 end TRANSLATE_ROW;
309
310 procedure LOAD_ROW (
311 X_ISO_LANGUAGE_3 in VARCHAR2,
312 X_ISO_LANGUAGE_2 in VARCHAR2,
313 X_PRIVATE_USE_FLAG in VARCHAR2,
314 X_NAME in VARCHAR2,
315 X_DESCRIPTION in VARCHAR2,
316 X_OWNER in VARCHAR2,
317 X_LAST_UPDATE_DATE in VARCHAR2,
318 X_CUSTOM_MODE in VARCHAR2
319 ) is
320 user_id NUMBER;
321 X_ROWID VARCHAR2(64);
322 f_luby number; -- entity owner in file
323 f_ludate date; -- entity update date in file
324 db_luby number; -- entity owner in db
325 db_ludate date; -- entity update date in db
326
327 begin
328 -- Translate owner to file_last_updated_by
329 f_luby := fnd_load_util.owner_id(x_owner);
330
331 -- Translate char last_update_date to date
332 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
333
334 begin
335 select LAST_UPDATED_BY, LAST_UPDATE_DATE
336 into db_luby, db_ludate
337 from fnd_iso_languages
338 where iso_language_3 = X_ISO_LANGUAGE_3;
339
340 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
341 db_ludate, X_CUSTOM_MODE)) then
342 FND_ISO_LANGUAGES_PKG.UPDATE_ROW(
343 X_ISO_LANGUAGE_3,
344 X_ISO_LANGUAGE_2,
345 X_PRIVATE_USE_FLAG,
346 X_NAME,
347 X_DESCRIPTION,
348 f_ludate,
349 f_luby,
350 0);
351 end if;
352
353 exception
354 when no_data_found then
355 FND_ISO_LANGUAGES_PKG.INSERT_ROW(
356 X_ROWID,
357 X_ISO_LANGUAGE_3,
358 X_ISO_LANGUAGE_2,
359 X_PRIVATE_USE_FLAG,
360 X_NAME,
361 X_DESCRIPTION,
362 f_ludate,
363 f_luby,
364 f_ludate,
365 f_luby,
366 0);
367 end;
368 end LOAD_ROW;
369
370 end FND_ISO_LANGUAGES_PKG;