[Home] [Help]
PACKAGE BODY: APPS.CZ_LOCALIZED_TEXTS_PKG
Source
1 package body CZ_LOCALIZED_TEXTS_PKG as
2 /* $Header: cziloctb.pls 120.4.12020000.2 2012/08/22 11:14:58 vigramak 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 /* Modified Insert statment below to include parallel hints to improve performance - vigramak*/
185
186 insert into CZ_LOCALIZED_TEXTS (
187 LAST_UPDATE_LOGIN,
188 INTL_TEXT_ID,
189 LOCALIZED_STR,
190 LAST_UPDATE_DATE,
191 LAST_UPDATED_BY,
192 CREATION_DATE,
193 LANGUAGE,
194 SOURCE_LANG,
195 model_id,
196 ui_def_id,
197 seeded_flag,
198 UI_PAGE_ID,UI_PAGE_ELEMENT_ID,
199 persistent_intl_text_id,
200 DELETED_FLAG,
201 ORIG_SYS_REF)
202 select /*+ parallel(B) */
203 B.LAST_UPDATE_LOGIN,
204 B.INTL_TEXT_ID,
205 B.LOCALIZED_STR,
206 B.LAST_UPDATE_DATE,
207 B.LAST_UPDATED_BY,
208 B.CREATION_DATE,
209 L.LANGUAGE_CODE,
210 B.SOURCE_LANG,
211 nvl(B.model_id, NO_MODEL_ID),
212 B.ui_def_id,
213 B.seeded_flag,
214 B.UI_PAGE_ID, B.UI_PAGE_ELEMENT_ID,
215 B.persistent_intl_text_id,
216 B.DELETED_FLAG,
217 B.ORIG_SYS_REF
218 from CZ_LOCALIZED_TEXTS B,
219 FND_LANGUAGES L
220 where L.INSTALLED_FLAG in ('I', 'B')
221 and B.LANGUAGE = userenv('LANG')
222 and not exists
223 ( select /*+ parallel(T) */ NULL
224 from CZ_LOCALIZED_TEXTS T
225 where T.INTL_TEXT_ID = B.INTL_TEXT_ID
226 and T.LANGUAGE = L.LANGUAGE_CODE);
227 commit;
228
229 end ADD_LANGUAGE;
230
231
232 -- ----------------------------------------------------------------------
233 -- PROCEDURE: Translate_Row PUBLIC
234 --
235 -- PARAMETERS:
236 -- x_<developer key>
237 -- x_<translated columns>
238 -- x_owner user owning the row (SEED or other)
239 --
240 -- COMMENT:
241 -- Called from the FNDLOAD config file in 'NLS' mode to upload
242 -- translations.
243 -- ----------------------------------------------------------------------
244
245 PROCEDURE Translate_Row
246 (X_INTL_TEXT_ID IN NUMBER,
247 X_LOCALIZED_STR IN VARCHAR2,
248 X_OWNER IN VARCHAR2) IS
249
250 f_luby number; -- entity owner in file
251
252 BEGIN
253
254 -- Translate owner to file_last_updated_by
255 f_luby := fnd_load_util.owner_id(X_OWNER);
256
257 UPDATE CZ_LOCALIZED_TEXTS
258 SET LOCALIZED_STR = NVL(X_LOCALIZED_STR, LOCALIZED_STR)
259 ,LAST_UPDATE_DATE = SYSDATE
260 ,LAST_UPDATED_BY = f_luby
261 ,last_update_login = 0
262 ,source_lang = userenv('LANG')
263 WHERE INTL_TEXT_ID = X_INTL_TEXT_ID
264 AND userenv('LANG') IN (language, source_lang);
265
266 IF ( SQL%NOTFOUND ) THEN
267 RAISE no_data_found;
268 END IF;
269
270 END Translate_Row;
271
272 procedure LOAD_ROW
273 (X_INTL_TEXT_ID in NUMBER,
274 X_LOCALIZED_STR in VARCHAR2,
275 X_ORIG_SYS_REF in VARCHAR2,
276 X_CREATION_DATE in DATE,
277 X_LAST_UPDATE_DATE in DATE,
278 X_DELETED_FLAG in VARCHAR2,
279 X_LOCALE_ID in NUMBER,
280 p_model_id IN NUMBER,
281 p_ui_def_id IN NUMBER,
282 X_OWNER IN VARCHAR2,
283 X_PERSISTENT_INTL_TEXT_ID IN NUMBER,
284 X_SEEDED_FLAG IN VARCHAR2,
285 X_UI_PAGE_ID IN NUMBER,
286 X_UI_PAGE_ELEMENT_ID IN VARCHAR2) IS
287
288 s_intlid cz_localized_texts.intl_text_id%type; -- entity intl_text_id
289 f_luby number; -- entity owner in file
290 f_ludate date; -- entity update date in file
291 row_id varchar2(64);
292
293
294 cursor c_lktx is
295 select intl_text_id
296 from cz_localized_texts
297 where intl_text_id = x_intl_text_id
298 and language = userenv('LANG');
299
300 begin
301
302 -- Translate owner to file_last_updated_by
303 f_luby := fnd_load_util.owner_id(x_owner);
304
305 -- Translate char last_update_date to date
306 f_ludate := nvl(to_date(x_last_update_date, 'RRRR-MM-DD'), sysdate);
307
308 open c_lktx;
309 fetch c_lktx into s_intlid;
310
311 if (c_lktx%notfound) then
312 -- No matching rows
313 CZ_LOCALIZED_TEXTS_PKG.INSERT_ROW(
314 X_ROWID => row_id,
315 X_INTL_TEXT_ID => X_INTL_TEXT_ID,
316 X_LOCALIZED_STR => X_LOCALIZED_STR,
317 X_ORIG_SYS_REF => X_ORIG_SYS_REF,
318 X_CREATION_DATE => nvl(to_date(X_CREATION_DATE, 'RRRR-MM-DD'), sysdate),
319 X_LAST_UPDATE_DATE => nvl(to_date(X_LAST_UPDATE_DATE, 'RRRR-MM-DD'), sysdate),
320 X_DELETED_FLAG => X_DELETED_FLAG,
321 X_CREATED_BY => UID,
322 X_LAST_UPDATED_BY => f_luby,
323 X_LAST_UPDATE_LOGIN => UID,
324 X_LOCALE_ID => X_LOCALE_ID,
325 p_model_id => p_model_id,
326 p_ui_def_id => p_ui_def_id,
327 X_SEEDED_FLAG => X_SEEDED_FLAG,
328 X_PERSISTENT_INTL_TEXT_ID => X_PERSISTENT_INTL_TEXT_ID,
329 X_UI_PAGE_ID => X_UI_PAGE_ID,
330 X_UI_PAGE_ELEMENT_ID => X_UI_PAGE_ELEMENT_ID
331 );
332 else
333 loop
334 -- Update row in all matching locales
335 CZ_LOCALIZED_TEXTS_PKG.UPDATE_ROW (
336 X_INTL_TEXT_ID => X_INTL_TEXT_ID,
337 X_LOCALIZED_STR => X_LOCALIZED_STR,
338 X_ORIG_SYS_REF => X_ORIG_SYS_REF,
339 X_CREATION_DATE => SYSDATE,
340 X_LAST_UPDATE_DATE => f_ludate,
341 X_DELETED_FLAG => X_DELETED_FLAG,
342 X_CREATED_BY => UID,
343 X_LAST_UPDATED_BY => f_luby,
344 X_LAST_UPDATE_LOGIN => 0,
345 X_LOCALE_ID => X_LOCALE_ID,
346 p_model_id => p_model_id,
347 p_ui_def_id => p_ui_def_id,
348 X_PERSISTENT_INTL_TEXT_ID => X_PERSISTENT_INTL_TEXT_ID,
349 X_SEEDED_FLAG => X_SEEDED_FLAG,
350 X_UI_PAGE_ID => X_UI_PAGE_ID,
351 X_UI_PAGE_ELEMENT_ID => X_UI_PAGE_ELEMENT_ID);
352
353 fetch c_lktx into s_intlid;
354 exit when c_lktx%notfound;
355 end loop;
356 end if;
357 close c_lktx;
358
359 end LOAD_ROW;
360
361 procedure UPDATE_ROW
362 (X_INTL_TEXT_ID in NUMBER,
363 X_LOCALIZED_STR in VARCHAR2,
364 X_ORIG_SYS_REF in VARCHAR2,
365 X_DELETED_FLAG in VARCHAR2) is
366
367 begin
368
369 update CZ_LOCALIZED_TEXTS set
370 INTL_TEXT_ID =X_INTL_TEXT_ID
371 ,LOCALIZED_STR = DECODE(userenv('LANG'), language, X_LOCALIZED_STR, source_lang, X_LOCALIZED_STR, localized_str)
372 ,ORIG_SYS_REF =X_ORIG_SYS_REF
373 ,DELETED_FLAG =X_DELETED_FLAG
374 where INTL_TEXT_ID = X_INTL_TEXT_ID;
375
376 if (sql%notfound) then
377 raise no_data_found;
378 end if;
379
380 end UPDATE_ROW;
381
382
383 procedure LOAD_ROW
384 (X_INTL_TEXT_ID in NUMBER,
385 X_LOCALIZED_STR in VARCHAR2,
386 X_ORIG_SYS_REF in VARCHAR2,
387 X_DELETED_FLAG in VARCHAR2) IS
388
389 s_intlid cz_localized_texts.intl_text_id%type; -- entity intl_text_id
390 row_id varchar2(64);
391
392
393 cursor c_lktx is
394 select intl_text_id
395 from cz_localized_texts
396 where intl_text_id = x_intl_text_id
397 and language = userenv('LANG');
398
399 begin
400
401 open c_lktx;
402 fetch c_lktx into s_intlid;
403
404 if (c_lktx%notfound) then
405 -- No matching rows
406 CZ_LOCALIZED_TEXTS_PKG.INSERT_ROW(
407 X_ROWID => row_id,
408 X_INTL_TEXT_ID => X_INTL_TEXT_ID,
409 X_LOCALIZED_STR => X_LOCALIZED_STR,
410 X_ORIG_SYS_REF => X_ORIG_SYS_REF,
411 X_CREATION_DATE => sysdate,
412 X_LAST_UPDATE_DATE => sysdate,
413 X_DELETED_FLAG => X_DELETED_FLAG,
414 X_CREATED_BY => null,
415 X_LAST_UPDATED_BY => null,
416 X_LAST_UPDATE_LOGIN => null,
417 X_LOCALE_ID => null,
418 p_model_id => null,
419 p_ui_def_id => null,
420 X_SEEDED_FLAG => null,
421 X_PERSISTENT_INTL_TEXT_ID => null,
422 X_UI_PAGE_ID => null,
423 X_UI_PAGE_ELEMENT_ID => null
424 );
425 else
426 loop
427 -- Update row in all matching locales
428 CZ_LOCALIZED_TEXTS_PKG.UPDATE_ROW (
429 X_INTL_TEXT_ID => X_INTL_TEXT_ID,
430 X_LOCALIZED_STR => X_LOCALIZED_STR,
431 X_ORIG_SYS_REF => X_ORIG_SYS_REF,
432 X_DELETED_FLAG => X_DELETED_FLAG);
433
434 fetch c_lktx into s_intlid;
435 exit when c_lktx%notfound;
436 end loop;
437 end if;
438 close c_lktx;
439
440 end LOAD_ROW;
441
442
443 end CZ_LOCALIZED_TEXTS_PKG;