[Home] [Help]
PACKAGE BODY: APPS.CZ_LOCALIZED_TEXTS_PKG
Source
1 package body CZ_LOCALIZED_TEXTS_PKG as
2 /* $Header: cziloctb.pls 120.4 2006/09/26 20:08:47 asiaston ship $ */
3
4 NO_MODEL_ID NUMBER := -1;
5
6 procedure INSERT_ROW
7 (X_ROWID in OUT NOCOPY VARCHAR2,
8 X_INTL_TEXT_ID in NUMBER,
9 X_LOCALIZED_STR in VARCHAR2,
10 X_ORIG_SYS_REF in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_LAST_UPDATE_DATE in DATE,
13 X_DELETED_FLAG in VARCHAR2,
14 X_CREATED_BY in NUMBER,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER,
17 X_LOCALE_ID in NUMBER,
18 p_model_id IN NUMBER,
19 p_ui_def_id IN NUMBER,
20 X_SEEDED_FLAG IN VARCHAR2,
21 X_PERSISTENT_INTL_TEXT_ID IN NUMBER,
22 X_UI_PAGE_ID IN NUMBER,
23 X_UI_PAGE_ELEMENT_ID IN VARCHAR2) is
24
25 cursor C is
26 select ROWID
27 from CZ_LOCALIZED_TEXTS
28 where INTL_TEXT_ID = X_INTL_TEXT_ID
29 and language = userenv('LANG');
30
31 begin
32
33 insert into CZ_LOCALIZED_TEXTS
34 (INTL_TEXT_ID
35 ,LOCALIZED_STR
36 ,ORIG_SYS_REF
37 ,CREATION_DATE
38 ,LAST_UPDATE_DATE
39 ,DELETED_FLAG
40 ,CREATED_BY
41 ,LAST_UPDATED_BY
42 ,LAST_UPDATE_LOGIN
43 ,LOCALE_ID
44 ,model_id
45 ,ui_def_id
46 ,seeded_flag
47 ,source_lang
48 ,language
49 ,PERSISTENT_INTL_TEXT_ID
50 ,UI_PAGE_ID
51 ,UI_PAGE_ELEMENT_ID)
52 select
53 X_INTL_TEXT_ID
54 ,X_LOCALIZED_STR
55 ,X_ORIG_SYS_REF
56 ,X_CREATION_DATE
57 ,X_LAST_UPDATE_DATE
58 ,X_DELETED_FLAG
59 ,X_CREATED_BY
60 ,X_LAST_UPDATED_BY
61 ,X_LAST_UPDATE_LOGIN
62 ,X_LOCALE_ID
63 ,p_model_id
64 ,p_ui_def_id
65 ,X_SEEDED_FLAG, userenv('LANG'), L.LANGUAGE_CODE, X_PERSISTENT_INTL_TEXT_ID
66 ,X_UI_PAGE_ID, X_UI_PAGE_ELEMENT_ID
67 from FND_LANGUAGES L
68 where L.INSTALLED_FLAG in ('I', 'B')
69 and not exists
70 ( select NULL
71 from CZ_LOCALIZED_TEXTS T
72 where T.INTL_TEXT_ID = X_INTL_TEXT_ID
73 and T.LANGUAGE = L.LANGUAGE_CODE);
74
75 open c;
76 fetch c into X_ROWID;
77 if (c%notfound) then
78 close c;
79 raise no_data_found;
80 end if;
81 close c;
82
83 end INSERT_ROW;
84
85 procedure UPDATE_ROW
86 (X_INTL_TEXT_ID in NUMBER,
87 X_LOCALIZED_STR in VARCHAR2,
88 X_ORIG_SYS_REF in VARCHAR2,
89 X_CREATION_DATE in DATE,
90 X_LAST_UPDATE_DATE in DATE,
91 X_DELETED_FLAG in VARCHAR2,
92 X_CREATED_BY in NUMBER,
93 X_LAST_UPDATED_BY in NUMBER,
94 X_LAST_UPDATE_LOGIN in NUMBER,
95 X_LOCALE_ID in NUMBER,
96 p_model_id IN NUMBER,
97 p_ui_def_id IN NUMBER,
98 X_PERSISTENT_INTL_TEXT_ID in NUMBER,
99 X_SEEDED_FLAG IN VARCHAR2,
100 X_UI_PAGE_ID IN NUMBER,
101 X_UI_PAGE_ELEMENT_ID IN VARCHAR2) is
102
103 begin
104
105 update CZ_LOCALIZED_TEXTS set
106 INTL_TEXT_ID =X_INTL_TEXT_ID
107 ,LOCALIZED_STR = DECODE(userenv('LANG'), language, X_LOCALIZED_STR, source_lang, X_LOCALIZED_STR, localized_str)
108 ,ORIG_SYS_REF =X_ORIG_SYS_REF
109 ,DELETED_FLAG =X_DELETED_FLAG
110 ,LAST_UPDATE_LOGIN=X_LAST_UPDATE_LOGIN
111 ,LOCALE_ID =X_LOCALE_ID
112 ,model_id =p_model_id
113 ,ui_def_id =p_ui_def_id
114 ,persistent_intl_text_id = X_PERSISTENT_INTL_TEXT_ID
115 ,seeded_flag = x_seeded_flag
116 ,UI_PAGE_ID = X_UI_PAGE_ID
117 ,UI_PAGE_ELEMENT_ID = X_UI_PAGE_ELEMENT_ID
118 where INTL_TEXT_ID = X_INTL_TEXT_ID;
119
120 if (sql%notfound) then
121 raise no_data_found;
122 end if;
123
124 end UPDATE_ROW;
125
126
127 -- ----------------------------------------------------------------------
128 -- Deletion of categories is not supported.
129 -- ----------------------------------------------------------------------
130
131 procedure DELETE_ROW (
132 X_INTL_TEXT_ID in NUMBER
133 ) is
134 begin
135
136 delete from CZ_LOCALIZED_TEXTS
137 where INTL_TEXT_ID = X_INTL_TEXT_ID ;
138
139 if (sql%notfound) then
140 raise no_data_found;
141 end if;
142
143 end DELETE_ROW;
144
145
146 -- ----------------------------------------------------------------------
147 -- PROCEDURE: ADD_LANGUAGE PUBLIC
148 -- ----------------------------------------------------------------------
149 procedure ADD_LANGUAGE is
150
151 begin
152
153 /* Mar/19/03 requested by Ric Ginsberg */
154 /* The following delete and update statements are commented out */
155 /* as a quick workaround to fix the time-consuming table handler issue */
156 /* Eventually we'll need to turn them into a separate fix_language procedure */
157 /* Added by Pat Passerini July 17, 2003. */
158 /*
159 delete from CZ_LOCALIZED_TEXTS where DELETED_FLAG='1';
160 commit;
161
162 update CZ_LOCALIZED_TEXTS T set (
163 LOCALIZED_STR
164 ) = ( select B.LOCALIZED_STR
165 from CZ_LOCALIZED_TEXTS B
166 where B.INTL_TEXT_ID = T.INTL_TEXT_ID
167 and B.LANGUAGE = T.SOURCE_LANG and B.DELETED_FLAG='0')
168 where (
169 T.INTL_TEXT_ID,
170 T.LANGUAGE
171 ) in ( select
172 SUBT.INTL_TEXT_ID,
173 SUBT.LANGUAGE
174 from CZ_LOCALIZED_TEXTS SUBB,
175 CZ_LOCALIZED_TEXTS SUBT
176 where SUBB.INTL_TEXT_ID = SUBT.INTL_TEXT_ID
177 and SUBB.LANGUAGE = SUBT.SOURCE_LANG and SUBT.DELETED_FLAG='0' and SUBB.DELETED_FLAG='0'
178 and ( SUBB.LOCALIZED_STR <> SUBT.LOCALIZED_STR
179 or ( SUBB.LOCALIZED_STR is null and SUBT.LOCALIZED_STR is not null )
180 or ( SUBB.LOCALIZED_STR is not null and SUBT.LOCALIZED_STR is null ) )
181 );
182 commit;
183 */
184
185 insert into CZ_LOCALIZED_TEXTS (
186 LAST_UPDATE_LOGIN,
187 INTL_TEXT_ID,
188 LOCALIZED_STR,
189 LAST_UPDATE_DATE,
190 LAST_UPDATED_BY,
191 CREATION_DATE,
192 LANGUAGE,
193 SOURCE_LANG,
194 model_id,
195 ui_def_id,
196 seeded_flag,
197 UI_PAGE_ID,UI_PAGE_ELEMENT_ID,
198 persistent_intl_text_id,
199 DELETED_FLAG,
200 ORIG_SYS_REF)
201 select
202 B.LAST_UPDATE_LOGIN,
203 B.INTL_TEXT_ID,
204 B.LOCALIZED_STR,
205 B.LAST_UPDATE_DATE,
206 B.LAST_UPDATED_BY,
207 B.CREATION_DATE,
208 L.LANGUAGE_CODE,
209 B.SOURCE_LANG,
210 nvl(B.model_id, NO_MODEL_ID),
211 B.ui_def_id,
212 B.seeded_flag,
213 B.UI_PAGE_ID, B.UI_PAGE_ELEMENT_ID,
214 B.persistent_intl_text_id,
215 B.DELETED_FLAG,
216 B.ORIG_SYS_REF
217 from CZ_LOCALIZED_TEXTS B,
218 FND_LANGUAGES L
219 where L.INSTALLED_FLAG in ('I', 'B')
220 and B.LANGUAGE = userenv('LANG')
221 and not exists
222 ( select NULL
223 from CZ_LOCALIZED_TEXTS T
224 where T.INTL_TEXT_ID = B.INTL_TEXT_ID
225 and T.LANGUAGE = L.LANGUAGE_CODE);
226 commit;
227
228 end ADD_LANGUAGE;
229
230
231 -- ----------------------------------------------------------------------
232 -- PROCEDURE: Translate_Row PUBLIC
233 --
234 -- PARAMETERS:
235 -- x_<developer key>
236 -- x_<translated columns>
237 -- x_owner user owning the row (SEED or other)
238 --
239 -- COMMENT:
240 -- Called from the FNDLOAD config file in 'NLS' mode to upload
241 -- translations.
242 -- ----------------------------------------------------------------------
243
244 PROCEDURE Translate_Row
245 (X_INTL_TEXT_ID IN NUMBER,
246 X_LOCALIZED_STR IN VARCHAR2,
247 X_OWNER IN VARCHAR2) IS
248
249 f_luby number; -- entity owner in file
250
251 BEGIN
252
253 -- Translate owner to file_last_updated_by
254 f_luby := fnd_load_util.owner_id(X_OWNER);
255
256 UPDATE CZ_LOCALIZED_TEXTS
257 SET LOCALIZED_STR = NVL(X_LOCALIZED_STR, LOCALIZED_STR)
258 ,LAST_UPDATE_DATE = SYSDATE
259 ,LAST_UPDATED_BY = f_luby
260 ,last_update_login = 0
261 ,source_lang = userenv('LANG')
262 WHERE INTL_TEXT_ID = X_INTL_TEXT_ID
263 AND userenv('LANG') IN (language, source_lang);
264
265 IF ( SQL%NOTFOUND ) THEN
266 RAISE no_data_found;
267 END IF;
268
269 END Translate_Row;
270
271 procedure LOAD_ROW
272 (X_INTL_TEXT_ID in NUMBER,
273 X_LOCALIZED_STR in VARCHAR2,
274 X_ORIG_SYS_REF in VARCHAR2,
275 X_CREATION_DATE in DATE,
276 X_LAST_UPDATE_DATE in DATE,
277 X_DELETED_FLAG in VARCHAR2,
278 X_LOCALE_ID in NUMBER,
279 p_model_id IN NUMBER,
280 p_ui_def_id IN NUMBER,
281 X_OWNER IN VARCHAR2,
282 X_PERSISTENT_INTL_TEXT_ID IN NUMBER,
283 X_SEEDED_FLAG IN VARCHAR2,
284 X_UI_PAGE_ID IN NUMBER,
285 X_UI_PAGE_ELEMENT_ID IN VARCHAR2) IS
286
287 s_intlid cz_localized_texts.intl_text_id%type; -- entity intl_text_id
288 f_luby number; -- entity owner in file
289 f_ludate date; -- entity update date in file
290 row_id varchar2(64);
291
292
293 cursor c_lktx is
294 select intl_text_id
295 from cz_localized_texts
296 where intl_text_id = x_intl_text_id
297 and language = userenv('LANG');
298
299 begin
300
301 -- Translate owner to file_last_updated_by
302 f_luby := fnd_load_util.owner_id(x_owner);
303
304 -- Translate char last_update_date to date
305 f_ludate := nvl(to_date(x_last_update_date, 'RRRR-MM-DD'), sysdate);
306
307 open c_lktx;
308 fetch c_lktx into s_intlid;
309
310 if (c_lktx%notfound) then
311 -- No matching rows
312 CZ_LOCALIZED_TEXTS_PKG.INSERT_ROW(
313 X_ROWID => row_id,
314 X_INTL_TEXT_ID => X_INTL_TEXT_ID,
315 X_LOCALIZED_STR => X_LOCALIZED_STR,
316 X_ORIG_SYS_REF => X_ORIG_SYS_REF,
317 X_CREATION_DATE => nvl(to_date(X_CREATION_DATE, 'RRRR-MM-DD'), sysdate),
318 X_LAST_UPDATE_DATE => nvl(to_date(X_LAST_UPDATE_DATE, 'RRRR-MM-DD'), sysdate),
319 X_DELETED_FLAG => X_DELETED_FLAG,
320 X_CREATED_BY => UID,
321 X_LAST_UPDATED_BY => f_luby,
322 X_LAST_UPDATE_LOGIN => UID,
323 X_LOCALE_ID => X_LOCALE_ID,
324 p_model_id => p_model_id,
325 p_ui_def_id => p_ui_def_id,
326 X_SEEDED_FLAG => X_SEEDED_FLAG,
327 X_PERSISTENT_INTL_TEXT_ID => X_PERSISTENT_INTL_TEXT_ID,
328 X_UI_PAGE_ID => X_UI_PAGE_ID,
329 X_UI_PAGE_ELEMENT_ID => X_UI_PAGE_ELEMENT_ID
330 );
331 else
332 loop
333 -- Update row in all matching locales
334 CZ_LOCALIZED_TEXTS_PKG.UPDATE_ROW (
335 X_INTL_TEXT_ID => X_INTL_TEXT_ID,
336 X_LOCALIZED_STR => X_LOCALIZED_STR,
337 X_ORIG_SYS_REF => X_ORIG_SYS_REF,
338 X_CREATION_DATE => SYSDATE,
339 X_LAST_UPDATE_DATE => f_ludate,
340 X_DELETED_FLAG => X_DELETED_FLAG,
341 X_CREATED_BY => UID,
342 X_LAST_UPDATED_BY => f_luby,
343 X_LAST_UPDATE_LOGIN => 0,
344 X_LOCALE_ID => X_LOCALE_ID,
345 p_model_id => p_model_id,
346 p_ui_def_id => p_ui_def_id,
347 X_PERSISTENT_INTL_TEXT_ID => X_PERSISTENT_INTL_TEXT_ID,
348 X_SEEDED_FLAG => X_SEEDED_FLAG,
349 X_UI_PAGE_ID => X_UI_PAGE_ID,
350 X_UI_PAGE_ELEMENT_ID => X_UI_PAGE_ELEMENT_ID);
351
352 fetch c_lktx into s_intlid;
353 exit when c_lktx%notfound;
354 end loop;
358 end LOAD_ROW;
355 end if;
356 close c_lktx;
357
359
360 procedure UPDATE_ROW
361 (X_INTL_TEXT_ID in NUMBER,
362 X_LOCALIZED_STR in VARCHAR2,
363 X_ORIG_SYS_REF in VARCHAR2,
364 X_DELETED_FLAG in VARCHAR2) is
365
366 begin
367
368 update CZ_LOCALIZED_TEXTS set
369 INTL_TEXT_ID =X_INTL_TEXT_ID
370 ,LOCALIZED_STR = DECODE(userenv('LANG'), language, X_LOCALIZED_STR, source_lang, X_LOCALIZED_STR, localized_str)
371 ,ORIG_SYS_REF =X_ORIG_SYS_REF
372 ,DELETED_FLAG =X_DELETED_FLAG
373 where INTL_TEXT_ID = X_INTL_TEXT_ID;
374
375 if (sql%notfound) then
376 raise no_data_found;
377 end if;
378
379 end UPDATE_ROW;
380
381
382 procedure LOAD_ROW
383 (X_INTL_TEXT_ID in NUMBER,
384 X_LOCALIZED_STR in VARCHAR2,
385 X_ORIG_SYS_REF in VARCHAR2,
386 X_DELETED_FLAG in VARCHAR2) IS
387
388 s_intlid cz_localized_texts.intl_text_id%type; -- entity intl_text_id
389 row_id varchar2(64);
390
391
392 cursor c_lktx is
393 select intl_text_id
394 from cz_localized_texts
395 where intl_text_id = x_intl_text_id
396 and language = userenv('LANG');
397
398 begin
399
400 open c_lktx;
401 fetch c_lktx into s_intlid;
402
403 if (c_lktx%notfound) then
404 -- No matching rows
405 CZ_LOCALIZED_TEXTS_PKG.INSERT_ROW(
406 X_ROWID => row_id,
407 X_INTL_TEXT_ID => X_INTL_TEXT_ID,
408 X_LOCALIZED_STR => X_LOCALIZED_STR,
409 X_ORIG_SYS_REF => X_ORIG_SYS_REF,
410 X_CREATION_DATE => sysdate,
411 X_LAST_UPDATE_DATE => sysdate,
412 X_DELETED_FLAG => X_DELETED_FLAG,
413 X_CREATED_BY => null,
414 X_LAST_UPDATED_BY => null,
415 X_LAST_UPDATE_LOGIN => null,
416 X_LOCALE_ID => null,
417 p_model_id => null,
418 p_ui_def_id => null,
419 X_SEEDED_FLAG => null,
420 X_PERSISTENT_INTL_TEXT_ID => null,
421 X_UI_PAGE_ID => null,
422 X_UI_PAGE_ELEMENT_ID => null
423 );
424 else
425 loop
426 -- Update row in all matching locales
427 CZ_LOCALIZED_TEXTS_PKG.UPDATE_ROW (
428 X_INTL_TEXT_ID => X_INTL_TEXT_ID,
429 X_LOCALIZED_STR => X_LOCALIZED_STR,
430 X_ORIG_SYS_REF => X_ORIG_SYS_REF,
431 X_DELETED_FLAG => X_DELETED_FLAG);
432
433 fetch c_lktx into s_intlid;
434 exit when c_lktx%notfound;
435 end loop;
436 end if;
437 close c_lktx;
438
439 end LOAD_ROW;
440
441
442 end CZ_LOCALIZED_TEXTS_PKG;