[Home] [Help]
PACKAGE BODY: APPS.AMS_QUERY_COND_DISP_STRUCT_PKG
Source
1 package body AMS_QUERY_COND_DISP_STRUCT_PKG as
2 /* $Header: amstcdsb.pls 120.1 2005/06/27 05:39:52 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_QUERY_COND_DISP_STRUCT_ID in NUMBER,
6 X_QUERY_TEMPLATE_ID in NUMBER,
7 X_QUERY_CONDITION_ID in NUMBER,
8 X_TOKEN_TYPE in VARCHAR2,
9 X_TOKEN_SEQUENCE in NUMBER,
10 X_AVAILABLE_LOV_ID in NUMBER,
11 X_VALUE_TYPE in VARCHAR2,
12 X_NON_VARIANT_VALUE in VARCHAR2,
13 X_QUERY_ALIAS_ID in NUMBER,
14 X_DATA_TYPE in VARCHAR2,
15 X_EXP_LOGIC_SEQ in NUMBER,
16 X_EXP_DISPLAY_SEQ in NUMBER,
17 X_OBJECT_VERSION_NUMBER in NUMBER,
18 X_REQUEST_ID in NUMBER,
19 X_SECURITY_GROUP_ID in NUMBER,
20 X_DISPLAY_TEXT in VARCHAR2,
21 X_CREATION_DATE in DATE,
22 X_CREATED_BY in NUMBER,
23 X_LAST_UPDATE_DATE in DATE,
24 X_LAST_UPDATED_BY in NUMBER,
25 X_LAST_UPDATE_LOGIN in NUMBER,
26 X_PROGRAM_ID in NUMBER,
27 X_PROGRAM_APPLICATION_ID in NUMBER,
28 X_PROGRAM_UPDATE_DATE in DATE
29 ) is
30 cursor C is select ROWID from AMS_QUERY_COND_DISP_STRUCT_ALL
31 where QUERY_COND_DISP_STRUCT_ID = X_QUERY_COND_DISP_STRUCT_ID
32 ;
33 begin
34 insert into AMS_QUERY_COND_DISP_STRUCT_ALL (
35 QUERY_COND_DISP_STRUCT_ID,
36 QUERY_TEMPLATE_ID,
37 QUERY_CONDITION_ID,
38 TOKEN_TYPE,
39 TOKEN_SEQUENCE,
40 AVAILABLE_LOV_ID,
41 VALUE_TYPE,
42 NON_VARIANT_VALUE,
43 QUERY_ALIAS_ID,
44 DATA_TYPE,
45 EXP_LOGIC_SEQ,
46 EXP_DISPLAY_SEQ,
47 OBJECT_VERSION_NUMBER,
48 REQUEST_ID,
49 SECURITY_GROUP_ID,
50 CREATION_DATE,
51 CREATED_BY,
52 LAST_UPDATE_DATE,
53 LAST_UPDATED_BY,
54 LAST_UPDATE_LOGIN,
55 PROGRAM_ID,
56 PROGRAM_APPLICATION_ID,
57 PROGRAM_UPDATE_DATE
58 ) values (
59 X_QUERY_COND_DISP_STRUCT_ID,
60 X_QUERY_TEMPLATE_ID,
61 X_QUERY_CONDITION_ID,
62 X_TOKEN_TYPE,
63 X_TOKEN_SEQUENCE,
64 X_AVAILABLE_LOV_ID,
65 X_VALUE_TYPE,
66 X_NON_VARIANT_VALUE,
67 X_QUERY_ALIAS_ID,
68 X_DATA_TYPE,
69 X_EXP_LOGIC_SEQ,
70 X_EXP_DISPLAY_SEQ,
71 X_OBJECT_VERSION_NUMBER,
72 X_REQUEST_ID,
73 X_SECURITY_GROUP_ID,
74 X_CREATION_DATE,
75 X_CREATED_BY,
76 X_LAST_UPDATE_DATE,
77 X_LAST_UPDATED_BY,
78 X_LAST_UPDATE_LOGIN,
79 X_PROGRAM_ID,
80 X_PROGRAM_APPLICATION_ID,
81 X_PROGRAM_UPDATE_DATE
82 );
83
84 insert into AMS_QUERY_COND_DISP_STRUCT_TL (
85 QUERY_COND_DISP_STRUCT_ID,
86 DISPLAY_TEXT,
87 LAST_UPDATE_DATE,
88 LAST_UPDATED_BY,
89 CREATION_DATE,
90 CREATED_BY,
91 LAST_UPDATE_LOGIN,
92 LANGUAGE,
93 SOURCE_LANG
94 ) select
95 X_QUERY_COND_DISP_STRUCT_ID,
96 X_DISPLAY_TEXT,
97 X_LAST_UPDATE_DATE,
98 X_LAST_UPDATED_BY,
99 X_CREATION_DATE,
100 X_CREATED_BY,
101 X_LAST_UPDATE_LOGIN,
102 L.LANGUAGE_CODE,
103 userenv('LANG')
104 from FND_LANGUAGES L
105 where L.INSTALLED_FLAG in ('I', 'B')
106 and not exists
107 (select NULL
108 from AMS_QUERY_COND_DISP_STRUCT_TL T
109 where T.QUERY_COND_DISP_STRUCT_ID = X_QUERY_COND_DISP_STRUCT_ID
110 and T.LANGUAGE = L.LANGUAGE_CODE);
111
112 open c;
113 fetch c into X_ROWID;
114 if (c%notfound) then
115 close c;
116 raise no_data_found;
117 end if;
118 close c;
119
120 end INSERT_ROW;
121
122 procedure LOCK_ROW (
123 X_QUERY_COND_DISP_STRUCT_ID in NUMBER,
124 X_QUERY_TEMPLATE_ID in NUMBER,
125 X_QUERY_CONDITION_ID in NUMBER,
126 X_TOKEN_TYPE in VARCHAR2,
127 X_TOKEN_SEQUENCE in NUMBER,
128 X_AVAILABLE_LOV_ID in NUMBER,
129 X_VALUE_TYPE in VARCHAR2,
130 X_NON_VARIANT_VALUE in VARCHAR2,
131 X_QUERY_ALIAS_ID in NUMBER,
132 X_DATA_TYPE in VARCHAR2,
133 X_EXP_LOGIC_SEQ in NUMBER,
134 X_EXP_DISPLAY_SEQ in NUMBER,
135 X_OBJECT_VERSION_NUMBER in NUMBER,
136 X_REQUEST_ID in NUMBER,
137 X_SECURITY_GROUP_ID in NUMBER,
138 X_DISPLAY_TEXT in VARCHAR2,
139 X_PROGRAM_ID in NUMBER,
140 X_PROGRAM_APPLICATION_ID in NUMBER,
141 X_PROGRAM_UPDATE_DATE in DATE
142 ) is
143 cursor c is select
144 QUERY_TEMPLATE_ID,
145 QUERY_CONDITION_ID,
146 TOKEN_TYPE,
147 TOKEN_SEQUENCE,
148 AVAILABLE_LOV_ID,
149 VALUE_TYPE,
150 NON_VARIANT_VALUE,
151 QUERY_ALIAS_ID,
152 DATA_TYPE,
153 EXP_LOGIC_SEQ,
154 EXP_DISPLAY_SEQ,
155 OBJECT_VERSION_NUMBER,
156 REQUEST_ID,
157 SECURITY_GROUP_ID
158 from AMS_QUERY_COND_DISP_STRUCT_ALL
159 where QUERY_COND_DISP_STRUCT_ID = X_QUERY_COND_DISP_STRUCT_ID
160 for update of QUERY_COND_DISP_STRUCT_ID nowait;
161 recinfo c%rowtype;
162
163 cursor c1 is select
164 DISPLAY_TEXT,
165 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
166 from AMS_QUERY_COND_DISP_STRUCT_TL
167 where QUERY_COND_DISP_STRUCT_ID = X_QUERY_COND_DISP_STRUCT_ID
168 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
169 for update of QUERY_COND_DISP_STRUCT_ID nowait;
170 begin
171 open c;
172 fetch c into recinfo;
173 if (c%notfound) then
174 close c;
175 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
176 app_exception.raise_exception;
177 end if;
178 close c;
179 if ( ((recinfo.QUERY_TEMPLATE_ID = X_QUERY_TEMPLATE_ID)
180 OR ((recinfo.QUERY_TEMPLATE_ID is null) AND (X_QUERY_TEMPLATE_ID is null)))
181 AND ((recinfo.QUERY_CONDITION_ID = X_QUERY_CONDITION_ID)
182 OR ((recinfo.QUERY_CONDITION_ID is null) AND (X_QUERY_CONDITION_ID is null)))
183 AND ((recinfo.TOKEN_TYPE = X_TOKEN_TYPE)
184 OR ((recinfo.TOKEN_TYPE is null) AND (X_TOKEN_TYPE is null)))
185 AND ((recinfo.TOKEN_SEQUENCE = X_TOKEN_SEQUENCE)
186 OR ((recinfo.TOKEN_SEQUENCE is null) AND (X_TOKEN_SEQUENCE is null)))
187 AND ((recinfo.AVAILABLE_LOV_ID = X_AVAILABLE_LOV_ID)
188 OR ((recinfo.AVAILABLE_LOV_ID is null) AND (X_AVAILABLE_LOV_ID is null)))
189 AND ((recinfo.VALUE_TYPE = X_VALUE_TYPE)
190 OR ((recinfo.VALUE_TYPE is null) AND (X_VALUE_TYPE is null)))
191 AND ((recinfo.NON_VARIANT_VALUE = X_NON_VARIANT_VALUE)
192 OR ((recinfo.NON_VARIANT_VALUE is null) AND (X_NON_VARIANT_VALUE is null)))
193 AND ((recinfo.QUERY_ALIAS_ID = X_QUERY_ALIAS_ID)
194 OR ((recinfo.QUERY_ALIAS_ID is null) AND (X_QUERY_ALIAS_ID is null)))
195 AND ((recinfo.DATA_TYPE = X_DATA_TYPE)
196 OR ((recinfo.DATA_TYPE is null) AND (X_DATA_TYPE is null)))
197 AND ((recinfo.EXP_LOGIC_SEQ = X_EXP_LOGIC_SEQ)
198 OR ((recinfo.EXP_LOGIC_SEQ is null) AND (X_EXP_LOGIC_SEQ is null)))
199 AND ((recinfo.EXP_DISPLAY_SEQ = X_EXP_DISPLAY_SEQ)
200 OR ((recinfo.EXP_DISPLAY_SEQ is null) AND (X_EXP_DISPLAY_SEQ is null)))
201 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
202 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
203 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
204 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
205 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
206 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
207 ) then
208 null;
209 else
210 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
211 app_exception.raise_exception;
212 end if;
213
214 for tlinfo in c1 loop
215 if (tlinfo.BASELANG = 'Y') then
216 if ( ((tlinfo.DISPLAY_TEXT = X_DISPLAY_TEXT)
217 OR ((tlinfo.DISPLAY_TEXT is null) AND (X_DISPLAY_TEXT is null)))
218 ) then
219 null;
220 else
221 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
222 app_exception.raise_exception;
223 end if;
224 end if;
225 end loop;
226 return;
227 end LOCK_ROW;
228
229 procedure UPDATE_ROW (
230 X_QUERY_COND_DISP_STRUCT_ID in NUMBER,
231 X_QUERY_TEMPLATE_ID in NUMBER,
232 X_QUERY_CONDITION_ID in NUMBER,
233 X_TOKEN_TYPE in VARCHAR2,
234 X_TOKEN_SEQUENCE in NUMBER,
235 X_AVAILABLE_LOV_ID in NUMBER,
236 X_VALUE_TYPE in VARCHAR2,
237 X_NON_VARIANT_VALUE in VARCHAR2,
238 X_QUERY_ALIAS_ID in NUMBER,
239 X_DATA_TYPE in VARCHAR2,
240 X_EXP_LOGIC_SEQ in NUMBER,
241 X_EXP_DISPLAY_SEQ in NUMBER,
242 X_OBJECT_VERSION_NUMBER in NUMBER,
243 X_REQUEST_ID in NUMBER,
244 X_SECURITY_GROUP_ID in NUMBER,
245 X_DISPLAY_TEXT in VARCHAR2,
246 X_LAST_UPDATE_DATE in DATE,
247 X_LAST_UPDATED_BY in NUMBER,
248 X_LAST_UPDATE_LOGIN in NUMBER,
249 X_PROGRAM_ID in NUMBER,
250 X_PROGRAM_APPLICATION_ID in NUMBER,
251 X_PROGRAM_UPDATE_DATE in DATE
252 ) is
253 begin
254 update AMS_QUERY_COND_DISP_STRUCT_ALL set
255 QUERY_TEMPLATE_ID = X_QUERY_TEMPLATE_ID,
256 QUERY_CONDITION_ID = X_QUERY_CONDITION_ID,
257 TOKEN_TYPE = X_TOKEN_TYPE,
258 TOKEN_SEQUENCE = X_TOKEN_SEQUENCE,
259 AVAILABLE_LOV_ID = X_AVAILABLE_LOV_ID,
260 VALUE_TYPE = X_VALUE_TYPE,
261 NON_VARIANT_VALUE = X_NON_VARIANT_VALUE,
262 QUERY_ALIAS_ID = X_QUERY_ALIAS_ID,
263 DATA_TYPE = X_DATA_TYPE,
264 EXP_LOGIC_SEQ = X_EXP_LOGIC_SEQ,
265 EXP_DISPLAY_SEQ = X_EXP_DISPLAY_SEQ,
266 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
267 REQUEST_ID = X_REQUEST_ID,
268 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
269 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
270 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
271 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
272 PROGRAM_ID = X_PROGRAM_ID,
273 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
274 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
275 where QUERY_COND_DISP_STRUCT_ID = X_QUERY_COND_DISP_STRUCT_ID;
276
277 if (sql%notfound) then
278 raise no_data_found;
279 end if;
280
281 update AMS_QUERY_COND_DISP_STRUCT_TL set
282 DISPLAY_TEXT = X_DISPLAY_TEXT,
283 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
284 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
285 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
286 SOURCE_LANG = userenv('LANG')
287 where QUERY_COND_DISP_STRUCT_ID = X_QUERY_COND_DISP_STRUCT_ID
288 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
289
290 if (sql%notfound) then
291 raise no_data_found;
292 end if;
293 end UPDATE_ROW;
294
295
296 PROCEDURE LOAD_ROW (
297 X_QUERY_COND_DISP_STRUCT_ID IN NUMBER,
298 X_QUERY_TEMPLATE_ID in NUMBER,
299 X_QUERY_CONDITION_ID in NUMBER,
300 X_TOKEN_TYPE in VARCHAR2,
301 X_TOKEN_SEQUENCE in NUMBER,
302 X_AVAILABLE_LOV_ID in NUMBER,
303 X_VALUE_TYPE in VARCHAR2,
304 X_NON_VARIANT_VALUE in VARCHAR2,
305 X_QUERY_ALIAS_ID in NUMBER,
306 X_DATA_TYPE in VARCHAR2,
307 X_EXP_LOGIC_SEQ in NUMBER,
308 X_EXP_DISPLAY_SEQ in NUMBER,
309 X_DISPLAY_TEXT in VARCHAR2,
310 x_owner IN VARCHAR2,
311 x_custom_mode IN VARCHAR2
312 )
313 IS
314 l_user_id number := 0;
315 l_obj_verno number;
316 l_dummy_char varchar2(1);
317 l_row_id varchar2(100);
318 l_disp_struct_id number;
319 l_db_luby_id number;
320
321 CURSOR c_obj_verno IS
322 SELECT object_version_number, last_updated_by
323 FROM AMS_QUERY_COND_DISP_STRUCT_ALL
324 WHERE QUERY_COND_DISP_STRUCT_ID = X_QUERY_COND_DISP_STRUCT_ID;
325
326 CURSOR c_chk_exists is
327 SELECT 'x'
328 FROM AMS_QUERY_COND_DISP_STRUCT_ALL
329 WHERE QUERY_COND_DISP_STRUCT_ID = X_QUERY_COND_DISP_STRUCT_ID;
330
331 CURSOR c_get_id is
332 SELECT AMS_QUERY_COND_DISP_STRUCT_S.NEXTVAL
333 FROM DUAL;
334 BEGIN
335 if X_OWNER = 'SEED' then
336 l_user_id := 1;
337 elsif X_OWNER = 'ORACLE' then
338 l_user_id := 2;
339 elsif X_OWNER = 'SYSADMIN' then
340 l_user_id := 0;
341
342 end if;
343
344 OPEN c_chk_exists;
345 FETCH c_chk_exists INTO l_dummy_char;
346 IF c_chk_exists%notfound THEN
347 CLOSE c_chk_exists;
348
349 IF X_QUERY_COND_DISP_STRUCT_ID IS NULL THEN
350 OPEN c_get_id;
351 FETCH c_get_id INTO l_disp_struct_id;
352 CLOSE c_get_id;
353 ELSE
354 l_disp_struct_id := X_QUERY_COND_DISP_STRUCT_ID;
355 END IF;
356
357 l_obj_verno := 1;
358
359 AMS_QUERY_COND_DISP_STRUCT_PKG.Insert_Row (
360 X_ROWID => l_row_id,
361 X_QUERY_COND_DISP_STRUCT_ID => l_disp_struct_id,
362 X_QUERY_TEMPLATE_ID => X_QUERY_TEMPLATE_ID,
363 X_QUERY_CONDITION_ID => X_QUERY_CONDITION_ID,
364 X_TOKEN_TYPE => X_TOKEN_TYPE,
365 X_TOKEN_SEQUENCE => X_TOKEN_SEQUENCE,
366 X_AVAILABLE_LOV_ID => X_AVAILABLE_LOV_ID,
367 X_VALUE_TYPE => X_VALUE_TYPE,
368 X_NON_VARIANT_VALUE => X_NON_VARIANT_VALUE,
369 X_QUERY_ALIAS_ID => X_QUERY_ALIAS_ID,
370 X_DATA_TYPE => X_DATA_TYPE,
371 X_EXP_LOGIC_SEQ => X_EXP_LOGIC_SEQ,
372 X_EXP_DISPLAY_SEQ => X_EXP_DISPLAY_SEQ,
373 X_OBJECT_VERSION_NUMBER => l_obj_verno,
374 X_REQUEST_ID => 0,
375 X_SECURITY_GROUP_ID => 0,
376 X_DISPLAY_TEXT => X_DISPLAY_TEXT,
377 X_creation_date => SYSDATE,
378 X_created_by => l_user_id,
379 X_last_update_date => SYSDATE,
380 X_last_updated_by => l_user_id,
381 X_last_update_login => 0,
382 X_PROGRAM_ID => 0,
383 X_PROGRAM_APPLICATION_ID => 0,
384 X_PROGRAM_UPDATE_DATE => SYSDATE
385 );
386 ELSE
387 CLOSE c_chk_exists;
388 OPEN c_obj_verno;
389 FETCH c_obj_verno INTO l_obj_verno, l_db_luby_id;
390 CLOSE c_obj_verno;
391
392
393 if (l_db_luby_id IN (0, 1, 2) or NVL(x_custom_mode, 'PRESERVE')='FORCE')
394 then
395
396 AMS_QUERY_COND_DISP_STRUCT_PKG.Update_Row (
397 X_QUERY_COND_DISP_STRUCT_ID => X_QUERY_COND_DISP_STRUCT_ID ,
398 X_QUERY_TEMPLATE_ID => X_QUERY_TEMPLATE_ID,
399 X_QUERY_CONDITION_ID => X_QUERY_CONDITION_ID,
400 X_TOKEN_TYPE => X_TOKEN_TYPE,
401 X_TOKEN_SEQUENCE => X_TOKEN_SEQUENCE,
402 X_AVAILABLE_LOV_ID => X_AVAILABLE_LOV_ID,
403 X_VALUE_TYPE => X_VALUE_TYPE,
404 X_NON_VARIANT_VALUE => X_NON_VARIANT_VALUE,
405 X_QUERY_ALIAS_ID => X_QUERY_ALIAS_ID,
406 X_DATA_TYPE => X_DATA_TYPE,
407 X_EXP_LOGIC_SEQ => X_EXP_LOGIC_SEQ,
408 X_EXP_DISPLAY_SEQ => X_EXP_DISPLAY_SEQ,
409 X_OBJECT_VERSION_NUMBER => l_obj_verno,
410 X_REQUEST_ID => 0,
411 X_SECURITY_GROUP_ID => 0,
412 X_DISPLAY_TEXT => X_DISPLAY_TEXT,
413 X_last_update_date => SYSDATE,
414 X_last_updated_by => l_user_id,
415 X_last_update_login => 0,
416 X_PROGRAM_ID => 0,
417 X_PROGRAM_APPLICATION_ID => 0,
418 X_PROGRAM_UPDATE_DATE => SYSDATE
419 );
420 end if;
421 END IF;
422 END LOAD_ROW;
423
424 procedure DELETE_ROW (
425 X_QUERY_COND_DISP_STRUCT_ID in NUMBER
426 ) is
427 begin
428 delete from AMS_QUERY_COND_DISP_STRUCT_TL
429 where QUERY_COND_DISP_STRUCT_ID = X_QUERY_COND_DISP_STRUCT_ID;
430
431 if (sql%notfound) then
432 raise no_data_found;
433 end if;
434
435 delete from AMS_QUERY_COND_DISP_STRUCT_ALL
436 where QUERY_COND_DISP_STRUCT_ID = X_QUERY_COND_DISP_STRUCT_ID;
437
438 if (sql%notfound) then
439 raise no_data_found;
440 end if;
441 end DELETE_ROW;
442
443 procedure ADD_LANGUAGE
444 is
445 begin
446 delete from AMS_QUERY_COND_DISP_STRUCT_TL T
447 where not exists
448 (select NULL
449 from AMS_QUERY_COND_DISP_STRUCT_ALL B
450 where B.QUERY_COND_DISP_STRUCT_ID = T.QUERY_COND_DISP_STRUCT_ID
451 );
452
453 update AMS_QUERY_COND_DISP_STRUCT_TL T set (
454 DISPLAY_TEXT
455 ) = (select
456 B.DISPLAY_TEXT
457 from AMS_QUERY_COND_DISP_STRUCT_TL B
458 where B.QUERY_COND_DISP_STRUCT_ID = T.QUERY_COND_DISP_STRUCT_ID
459 and B.LANGUAGE = T.SOURCE_LANG)
460 where (
461 T.QUERY_COND_DISP_STRUCT_ID,
462 T.LANGUAGE
463 ) in (select
464 SUBT.QUERY_COND_DISP_STRUCT_ID,
465 SUBT.LANGUAGE
466 from AMS_QUERY_COND_DISP_STRUCT_TL SUBB, AMS_QUERY_COND_DISP_STRUCT_TL SUBT
467 where SUBB.QUERY_COND_DISP_STRUCT_ID = SUBT.QUERY_COND_DISP_STRUCT_ID
468 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
469 and (SUBB.DISPLAY_TEXT <> SUBT.DISPLAY_TEXT
470 or (SUBB.DISPLAY_TEXT is null and SUBT.DISPLAY_TEXT is not null)
471 or (SUBB.DISPLAY_TEXT is not null and SUBT.DISPLAY_TEXT is null)
472 ));
473
474 insert into AMS_QUERY_COND_DISP_STRUCT_TL (
475 QUERY_COND_DISP_STRUCT_ID,
476 DISPLAY_TEXT,
477 LAST_UPDATE_DATE,
478 LAST_UPDATED_BY,
479 CREATION_DATE,
480 CREATED_BY,
481 LAST_UPDATE_LOGIN,
482 LANGUAGE,
483 SOURCE_LANG
484 ) select /*+ ORDERED */
485 B.QUERY_COND_DISP_STRUCT_ID,
486 B.DISPLAY_TEXT,
487 B.LAST_UPDATE_DATE,
488 B.LAST_UPDATED_BY,
489 B.CREATION_DATE,
490 B.CREATED_BY,
491 B.LAST_UPDATE_LOGIN,
492 L.LANGUAGE_CODE,
493 B.SOURCE_LANG
494 from AMS_QUERY_COND_DISP_STRUCT_TL B, FND_LANGUAGES L
495 where L.INSTALLED_FLAG in ('I', 'B')
496 and B.LANGUAGE = userenv('LANG')
497 and not exists
498 (select NULL
499 from AMS_QUERY_COND_DISP_STRUCT_TL T
500 where T.QUERY_COND_DISP_STRUCT_ID = B.QUERY_COND_DISP_STRUCT_ID
501 and T.LANGUAGE = L.LANGUAGE_CODE);
502 end ADD_LANGUAGE;
503
504
505 procedure TRANSLATE_ROW(
506 X_QUERY_COND_DISP_STRUCT_ID in NUMBER,
507 X_DISPLAY_TEXT in VARCHAR2,
508 x_owner in VARCHAR2,
509 x_custom_mode in VARCHAR2
510 ) is
511
512 cursor c_last_updated_by is
513 select last_updated_by
514 from AMS_QUERY_COND_DISP_STRUCT_TL
515 where QUERY_COND_DISP_STRUCT_ID = x_QUERY_COND_DISP_STRUCT_ID
516 and USERENV('LANG') = LANGUAGE;
517
518 l_luby number; --last updated by
519
520 begin
521
522 open c_last_updated_by;
523 fetch c_last_updated_by into l_luby;
524 close c_last_updated_by;
525
526 if (l_luby IN (0, 1, 2) or NVL(x_custom_mode, 'PRESERVE')='FORCE')
527 then
528 update AMS_QUERY_COND_DISP_STRUCT_TL set
529 DISPLAY_TEXT = nvl(x_DISPLAY_TEXT, DISPLAY_TEXT),
530 source_lang = userenv('LANG'),
531 last_update_date = sysdate,
532 last_updated_by = decode(x_owner, 'SEED', 1, 'ORACLE', 2, 'SYSADMIN', 0, -1),
533 last_update_login = 0
534 where QUERY_COND_DISP_STRUCT_ID = x_QUERY_COND_DISP_STRUCT_ID
535 and userenv('LANG') in (language, source_lang);
536 end if;
537 end TRANSLATE_ROW;
538
539
540 end AMS_QUERY_COND_DISP_STRUCT_PKG;