[Home] [Help]
PACKAGE BODY: APPS.XTR_SYS_LANGUAGES_PKG
Source
1 package body XTR_SYS_LANGUAGES_PKG as
2 /* $Header: xtrlangb.pls 120.3 2005/06/29 10:10:50 badiredd ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_MODULE_NAME in VARCHAR2,
6 X_CANVAS_TYPE in VARCHAR2,
7 X_ITEM_NAME in VARCHAR2,
8 X_ORIGINAL_TEXT in VARCHAR2,
9 X_TEXT in VARCHAR2,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 cursor C is select ROWID from XTR_SYS_LANGUAGES
17 where MODULE_NAME = X_MODULE_NAME
18 and CANVAS_TYPE = X_CANVAS_TYPE
19 and ITEM_NAME = X_ITEM_NAME
20 ;
21 v_rowid VARCHAR2(30);
22 begin
23 insert into XTR_SYS_LANGUAGES (
24 MODULE_NAME,
25 CANVAS_TYPE,
26 ITEM_NAME,
27 ORIGINAL_TEXT,
28 CREATION_DATE,
29 CREATED_BY,
30 LAST_UPDATE_DATE,
31 LAST_UPDATED_BY,
32 LAST_UPDATE_LOGIN
33 ) values (
34 X_MODULE_NAME,
35 X_CANVAS_TYPE,
36 X_ITEM_NAME,
37 X_ORIGINAL_TEXT,
38 X_CREATION_DATE,
39 X_CREATED_BY,
40 X_LAST_UPDATE_DATE,
41 X_LAST_UPDATED_BY,
42 X_LAST_UPDATE_LOGIN
43 );
44
45 insert into XTR_SYS_LANGUAGES_TL (
46 MODULE_NAME,
47 TEXT,
48 CREATED_BY,
49 CREATION_DATE,
50 LAST_UPDATED_BY,
51 LAST_UPDATE_DATE,
52 LAST_UPDATE_LOGIN,
53 CANVAS_TYPE,
54 ITEM_NAME,
55 LANGUAGE,
56 SOURCE_LANG
57 ) select
58 X_MODULE_NAME,
59 X_TEXT,
60 X_CREATED_BY,
61 X_CREATION_DATE,
62 X_LAST_UPDATED_BY,
63 X_LAST_UPDATE_DATE,
64 X_LAST_UPDATE_LOGIN,
65 X_CANVAS_TYPE,
66 X_ITEM_NAME,
67 L.LANGUAGE_CODE,
68 userenv('LANG')
69 from FND_LANGUAGES L
70 where L.INSTALLED_FLAG in ('I', 'B')
71 and not exists
72 (select NULL
73 from XTR_SYS_LANGUAGES_TL T
74 where T.MODULE_NAME = X_MODULE_NAME
75 and T.CANVAS_TYPE = X_CANVAS_TYPE
76 and T.ITEM_NAME = X_ITEM_NAME
77 and T.LANGUAGE = L.LANGUAGE_CODE);
78
79 open c;
80 fetch c into v_rowid;
81 if (c%notfound) then
82 close c;
83 raise no_data_found;
84 end if;
85 close c;
86 end INSERT_ROW;
87
88 procedure LOAD_ROW (
89 X_MODULE_NAME in VARCHAR2,
90 X_CANVAS_TYPE in VARCHAR2,
91 X_ITEM_NAME in VARCHAR2,
92 X_ORIGINAL_TEXT VARCHAR2,
93 X_TEXT in VARCHAR2,
94 X_OWNER in VARCHAR2 )
95 is
96 begin
97 declare
98 row_id varchar2(64);
99 user_id number ;
100 begin
101 if ( X_OWNER ='SEED')
102 then
103 user_id:=1;
104 else
105 user_id:=0;
106 end if;
107 xtr_sys_languages_pkg.update_row (
108 x_module_name => X_MODULE_NAME,
109 x_canvas_type => X_CANVAS_TYPE,
110 x_item_name => X_ITEM_NAME,
111 x_original_text => X_ORIGINAL_TEXT ,
112 x_text => X_TEXT ,
113 x_lang => null,
114 x_last_update_date => sysdate ,
115 x_last_updated_by => user_id,
116 x_last_update_login => 0);
117 exception
118 when no_data_found then
119 xtr_sys_languages_pkg.insert_row(
120 X_ROWID => row_id,
121 x_module_name => X_MODULE_NAME,
122 x_canvas_type => X_CANVAS_TYPE,
123 x_item_name => X_ITEM_NAME,
124 x_original_text => X_ORIGINAL_TEXT,
125 x_text => X_TEXT ,
126 x_creation_date => sysdate,
127 x_created_by => 1,
128 x_last_update_date => sysdate ,
129 x_last_updated_by =>user_id,
130 x_last_update_login => 0);
131 end;
132 end LOAD_ROW;
133
134 procedure LOCK_ROW (
135 X_MODULE_NAME in VARCHAR2,
136 X_CANVAS_TYPE in VARCHAR2,
137 X_ITEM_NAME in VARCHAR2,
138 X_ORIGINAL_TEXT in VARCHAR2,
139 X_TEXT in VARCHAR2
140 ) is
141 cursor c1 is select
142 TEXT,
143 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
144 from XTR_SYS_LANGUAGES_TL
145 where MODULE_NAME = X_MODULE_NAME
146 and CANVAS_TYPE = X_CANVAS_TYPE
147 and ITEM_NAME = X_ITEM_NAME
148 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
149 for update of MODULE_NAME nowait;
150 begin
151 for tlinfo in c1 loop
152 if (tlinfo.BASELANG = 'Y') then
153 if ( ((tlinfo.TEXT = X_TEXT)
154 OR ((tlinfo.TEXT is null) AND (X_TEXT is null)))
155 ) then
156 null;
157 else
158 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
159 app_exception.raise_exception;
160 end if;
161 end if;
162 end loop;
163 return;
164 end LOCK_ROW;
165
166 procedure UPDATE_ROW (
167 X_MODULE_NAME in VARCHAR2,
168 X_CANVAS_TYPE in VARCHAR2,
169 X_ITEM_NAME in VARCHAR2,
170 X_ORIGINAL_TEXT in VARCHAR2,
171 X_TEXT in VARCHAR2,
172 X_LANG in VARCHAR2,
173 X_LAST_UPDATE_DATE in DATE,
174 X_LAST_UPDATED_BY in NUMBER,
175 X_LAST_UPDATE_LOGIN in NUMBER
176 ) is
177 /* Update Logic
178
179 File Database Update
180 ---------------------------------------------
181 CUSTOM SEED Yes
182 CUSTOM CUSTOM Yes
183 SEED CUSTOM No
184 SEED SEED YES
185 */
186
187
188 begin
189 declare
190 l_last_updated_by number;
191 l_original_text varchar2(100);
192 begin
193 /* need to update xtr_sys_languages if original_text
194 is changed */
195 select original_text
196 into l_original_text
197 from xtr_sys_languages
198 where MODULE_NAME = X_MODULE_NAME
199 and CANVAS_TYPE = X_CANVAS_TYPE
200 and ITEM_NAME = X_ITEM_NAME;
201
202 if ((l_original_text <> x_original_text)
203 and
204 (x_original_text is not null))
205 then
206 update XTR_SYS_LANGUAGES set
207 ORIGINAL_TEXT = X_ORIGINAL_TEXT,
208 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
209 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
210 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
211 where MODULE_NAME = X_MODULE_NAME
212 and CANVAS_TYPE = X_CANVAS_TYPE
213 and ITEM_NAME = X_ITEM_NAME;
214 end if;
215
216 /* end of change for update of original_text */
217 select last_updated_by
218 into l_last_updated_by
219 from xtr_sys_languages_tl
220 where MODULE_NAME = X_MODULE_NAME
221 and CANVAS_TYPE = X_CANVAS_TYPE
222 and ITEM_NAME = X_ITEM_NAME
223 and LANGUAGE = userenv('LANG') ;
224
225 If ( l_last_updated_by = 1 or x_last_updated_by <> 1)
226 /* Update as long as Database is not CUSTOM and File is SEED */
227 then
228 update XTR_SYS_LANGUAGES_TL set
229 TEXT = X_TEXT,
230 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
231 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
232 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
233 SOURCE_LANG = userenv('LANG')
234 where MODULE_NAME = X_MODULE_NAME
235 and CANVAS_TYPE = X_CANVAS_TYPE
236 and ITEM_NAME = X_ITEM_NAME
237 and LANGUAGE = nvl(X_LANG, userenv('LANG'))
238 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
239
240 if (sql%notfound) then
241 raise no_data_found;
242 end if;
243 end if;
244 end;
245 end UPDATE_ROW;
246
247
248 procedure TRANSLATE_ROW (
249 X_MODULE_NAME in VARCHAR2,
250 X_CANVAS_TYPE in VARCHAR2,
251 X_ITEM_NAME in VARCHAR2,
252 X_ORIGINAL_TEXT in VARCHAR2,
253 X_TEXT in VARCHAR2,
254 X_OWNER in VARCHAR2
255 ) is
256 Begin
257 declare
258 user_id number;
259 begin
260
261 if ( X_OWNER ='SEED')
262 then
263 user_id:=1;
264 else
265 user_id:=0;
266 end if;
267 xtr_sys_languages_pkg.update_row (
268 x_module_name => X_MODULE_NAME,
269 x_canvas_type => X_CANVAS_TYPE,
270 x_item_name => X_ITEM_NAME,
271 x_original_text => X_ORIGINAL_TEXT ,
272 x_text => X_TEXT ,
273 x_last_update_date => sysdate ,
274 x_last_updated_by => user_id,
275 x_last_update_login => 0);
276
277 /* update XTR_SYS_LANGUAGES_TL set
278 TEXT = X_TEXT,
279 LAST_UPDATE_DATE = sysdate,
280 LAST_UPDATED_BY = 1,
281 LAST_UPDATE_LOGIN = 0,
282 SOURCE_LANG = userenv('LANG')
283 where MODULE_NAME = X_MODULE_NAME
284 and CANVAS_TYPE = X_CANVAS_TYPE
285 and ITEM_NAME = X_ITEM_NAME
286 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
287 */
288
289 end;
290 end TRANSLATE_ROW;
291
292
293 procedure DELETE_ROW (
294 X_MODULE_NAME in VARCHAR2,
295 X_CANVAS_TYPE in VARCHAR2,
296 X_ITEM_NAME in VARCHAR2
297 ) is
298 begin
299 delete from XTR_SYS_LANGUAGES_TL
300 where MODULE_NAME = X_MODULE_NAME
301 and CANVAS_TYPE = X_CANVAS_TYPE
302 and ITEM_NAME = X_ITEM_NAME;
303
304 if (sql%notfound) then
305 raise no_data_found;
306 end if;
307
308 delete from XTR_SYS_LANGUAGES
309 where MODULE_NAME = X_MODULE_NAME
310 and CANVAS_TYPE = X_CANVAS_TYPE
311 and ITEM_NAME = X_ITEM_NAME;
312
313 if (sql%notfound) then
314 raise no_data_found;
315 end if;
316 end DELETE_ROW;
317
318 procedure ADD_LANGUAGE
319 is
320 begin
321 delete from XTR_SYS_LANGUAGES_TL T
322 where not exists
323 (select NULL
324 from XTR_SYS_LANGUAGES B
325 where B.MODULE_NAME = T.MODULE_NAME
326 and B.CANVAS_TYPE = T.CANVAS_TYPE
327 and B.ITEM_NAME = T.ITEM_NAME
328 );
329
330 update XTR_SYS_LANGUAGES_TL T set (
331 TEXT
332 ) = (select
333 B.TEXT
334 from XTR_SYS_LANGUAGES_TL B
335 where B.MODULE_NAME = T.MODULE_NAME
336 and B.CANVAS_TYPE = T.CANVAS_TYPE
337 and B.ITEM_NAME = T.ITEM_NAME
338 and B.LANGUAGE = T.SOURCE_LANG)
339 where (
340 T.MODULE_NAME,
341 T.CANVAS_TYPE,
342 T.ITEM_NAME,
343 T.LANGUAGE
344 ) in (select
345 SUBT.MODULE_NAME,
346 SUBT.CANVAS_TYPE,
347 SUBT.ITEM_NAME,
348 SUBT.LANGUAGE
349 from XTR_SYS_LANGUAGES_TL SUBB, XTR_SYS_LANGUAGES_TL SUBT
350 where SUBB.MODULE_NAME = SUBT.MODULE_NAME
351 and SUBB.CANVAS_TYPE = SUBT.CANVAS_TYPE
352 and SUBB.ITEM_NAME = SUBT.ITEM_NAME
353 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
354 and (SUBB.TEXT <> SUBT.TEXT
355 or (SUBB.TEXT is null and SUBT.TEXT is not null)
356 or (SUBB.TEXT is not null and SUBT.TEXT is null)
357 ));
358
359 insert into XTR_SYS_LANGUAGES_TL (
360 MODULE_NAME,
361 TEXT,
362 CREATED_BY,
363 CREATION_DATE,
364 LAST_UPDATED_BY,
365 LAST_UPDATE_DATE,
366 LAST_UPDATE_LOGIN,
367 CANVAS_TYPE,
368 ITEM_NAME,
369 LANGUAGE,
370 SOURCE_LANG
371 ) select
372 B.MODULE_NAME,
373 B.TEXT,
374 B.CREATED_BY,
375 B.CREATION_DATE,
376 B.LAST_UPDATED_BY,
377 B.LAST_UPDATE_DATE,
378 B.LAST_UPDATE_LOGIN,
379 B.CANVAS_TYPE,
380 B.ITEM_NAME,
381 L.LANGUAGE_CODE,
382 B.SOURCE_LANG
383 from XTR_SYS_LANGUAGES_TL B, FND_LANGUAGES L
384 where L.INSTALLED_FLAG in ('I', 'B')
385 and B.LANGUAGE = userenv('LANG')
386 and not exists
387 (select NULL
388 from XTR_SYS_LANGUAGES_TL T
389 where T.MODULE_NAME = B.MODULE_NAME
390 and T.CANVAS_TYPE = B.CANVAS_TYPE
391 and T.ITEM_NAME = B.ITEM_NAME
392 and T.LANGUAGE = L.LANGUAGE_CODE);
393 end ADD_LANGUAGE;
394
395 end XTR_SYS_LANGUAGES_PKG;