[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_QUERIES_NEW_PKG
Source
1 package body AMS_LIST_QUERIES_NEW_PKG as
2 /* $Header: amstlqrb.pls 120.2.12000000.2 2007/07/02 05:05:43 rsatyava ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_LIST_QUERY_ID in NUMBER,
6 X_QUERY_TEMPLATE_ID in NUMBER,
7 X_OWNER_USER_ID in NUMBER,
8 X_QUERY_TYPE in VARCHAR2,
9 X_ACT_LIST_QUERY_USED_BY_ID in NUMBER,
10 X_ARC_ACT_LIST_QUERY_USED_BY in VARCHAR2,
11 X_SEED_FLAG in VARCHAR2,
12 X_SQL_STRING in VARCHAR2,
13 X_SOURCE_OBJECT_NAME in VARCHAR2,
14 X_PARENT_LIST_QUERY_ID in NUMBER,
15 X_SEQUENCE_ORDER in NUMBER,
16 X_PARAMETERIZED_FLAG in VARCHAR2,
17 X_ADMIN_FLAG in VARCHAR2,
18 X_OBJECT_VERSION_NUMBER in NUMBER,
19 X_TYPE in VARCHAR2,
20 X_QUERY in LONG,
21 X_ENABLED_FLAG in VARCHAR2,
22 X_PRIMARY_KEY in VARCHAR2,
23 X_PUBLIC_FLAG in VARCHAR2,
24 X_COMMENTS in VARCHAR2,
25 X_SECURITY_GROUP_ID in NUMBER,
26 X_NAME in VARCHAR2,
27 X_DESCRIPTION in VARCHAR2,
28 X_CREATION_DATE in DATE,
29 X_CREATED_BY in NUMBER,
30 X_LAST_UPDATE_DATE in DATE,
31 X_LAST_UPDATED_BY in NUMBER,
32 X_LAST_UPDATE_LOGIN in NUMBER
33 ) is
34 cursor C is select ROWID from AMS_LIST_QUERIES_ALL
35 where LIST_QUERY_ID = X_LIST_QUERY_ID
36 ;
37 begin
38 insert into AMS_LIST_QUERIES_ALL (
39 QUERY_TEMPLATE_ID,
40 OWNER_USER_ID,
41 QUERY_TYPE,
42 ACT_LIST_QUERY_USED_BY_ID,
43 ARC_ACT_LIST_QUERY_USED_BY,
44 SEED_FLAG,
45 SQL_STRING,
46 SOURCE_OBJECT_NAME,
47 PARENT_LIST_QUERY_ID,
48 SEQUENCE_ORDER,
49 PARAMETERIZED_FLAG,
50 ADMIN_FLAG,
51 LIST_QUERY_ID,
52 OBJECT_VERSION_NUMBER,
53 TYPE,
54 QUERY,
55 ENABLED_FLAG,
56 PRIMARY_KEY,
57 PUBLIC_FLAG,
58 COMMENTS,
59 SECURITY_GROUP_ID,
60 CREATION_DATE,
61 CREATED_BY,
62 LAST_UPDATE_DATE,
63 LAST_UPDATED_BY,
64 LAST_UPDATE_LOGIN
65 ) values (
66 X_QUERY_TEMPLATE_ID,
67 X_OWNER_USER_ID,
68 X_QUERY_TYPE,
69 X_ACT_LIST_QUERY_USED_BY_ID,
70 X_ARC_ACT_LIST_QUERY_USED_BY,
71 X_SEED_FLAG,
72 X_SQL_STRING,
73 X_SOURCE_OBJECT_NAME,
74 X_PARENT_LIST_QUERY_ID,
75 X_SEQUENCE_ORDER,
76 X_PARAMETERIZED_FLAG,
77 X_ADMIN_FLAG,
78 X_LIST_QUERY_ID,
79 X_OBJECT_VERSION_NUMBER,
80 X_TYPE,
81 -- X_QUERY,
--bmuthukr. Bug 5334951
82 -- NVL(X_QUERY,X_SQL_STRING),
83 -- rsatyava.Bug 5647356
84 X_QUERY,
85 X_ENABLED_FLAG,
86 X_PRIMARY_KEY,
87 X_PUBLIC_FLAG,
88 X_COMMENTS,
89 X_SECURITY_GROUP_ID,
90 X_CREATION_DATE,
91 X_CREATED_BY,
92 X_LAST_UPDATE_DATE,
93 X_LAST_UPDATED_BY,
94 X_LAST_UPDATE_LOGIN
95 );
96
97 insert into AMS_LIST_QUERIES_TL (
98 CREATION_DATE,
99 CREATED_BY,
100 LAST_UPDATE_LOGIN,
101 NAME,
102 DESCRIPTION,
103 LIST_QUERY_ID,
104 LAST_UPDATE_DATE,
105 LAST_UPDATED_BY,
106 LANGUAGE,
107 SOURCE_LANG
108 ) select
109 X_CREATION_DATE,
110 X_CREATED_BY,
111 X_LAST_UPDATE_LOGIN,
112 X_NAME,
113 X_DESCRIPTION,
114 X_LIST_QUERY_ID,
115 X_LAST_UPDATE_DATE,
116 X_LAST_UPDATED_BY,
117 L.LANGUAGE_CODE,
118 userenv('LANG')
119 from FND_LANGUAGES L
120 where L.INSTALLED_FLAG in ('I', 'B')
121 and not exists
122 (select NULL
123 from AMS_LIST_QUERIES_TL T
124 where T.LIST_QUERY_ID = X_LIST_QUERY_ID
125 and T.LANGUAGE = L.LANGUAGE_CODE);
126
127 open c;
128 fetch c into X_ROWID;
129 if (c%notfound) then
130 close c;
131 raise no_data_found;
132 end if;
133 close c;
134
135 end INSERT_ROW;
136
137 procedure LOCK_ROW (
138 X_LIST_QUERY_ID in NUMBER,
139 X_QUERY_TEMPLATE_ID in NUMBER,
140 X_OWNER_USER_ID in NUMBER,
141 X_QUERY_TYPE in VARCHAR2,
142 X_ACT_LIST_QUERY_USED_BY_ID in NUMBER,
143 X_ARC_ACT_LIST_QUERY_USED_BY in VARCHAR2,
144 X_SEED_FLAG in VARCHAR2,
145 X_SQL_STRING in VARCHAR2,
146 X_SOURCE_OBJECT_NAME in VARCHAR2,
147 X_PARENT_LIST_QUERY_ID in NUMBER,
148 X_SEQUENCE_ORDER in NUMBER,
149 X_PARAMETERIZED_FLAG in VARCHAR2,
150 X_ADMIN_FLAG in VARCHAR2,
151 X_OBJECT_VERSION_NUMBER in NUMBER,
152 X_TYPE in VARCHAR2,
153 X_QUERY in LONG,
154 X_ENABLED_FLAG in VARCHAR2,
155 X_PRIMARY_KEY in VARCHAR2,
156 X_PUBLIC_FLAG in VARCHAR2,
157 X_COMMENTS in VARCHAR2,
158 X_SECURITY_GROUP_ID in NUMBER,
159 X_NAME in VARCHAR2,
160 X_DESCRIPTION in VARCHAR2
161 ) is
162 cursor c is select
163 QUERY_TEMPLATE_ID,
164 OWNER_USER_ID,
165 QUERY_TYPE,
166 ACT_LIST_QUERY_USED_BY_ID,
167 ARC_ACT_LIST_QUERY_USED_BY,
168 SEED_FLAG,
169 SQL_STRING,
170 SOURCE_OBJECT_NAME,
171 PARENT_LIST_QUERY_ID,
172 SEQUENCE_ORDER,
173 PARAMETERIZED_FLAG,
174 ADMIN_FLAG,
175 OBJECT_VERSION_NUMBER,
176 TYPE,
177 QUERY,
178 ENABLED_FLAG,
179 PRIMARY_KEY,
180 PUBLIC_FLAG,
181 COMMENTS,
182 SECURITY_GROUP_ID
183 from AMS_LIST_QUERIES_ALL
184 where LIST_QUERY_ID = X_LIST_QUERY_ID
185 for update of LIST_QUERY_ID nowait;
186 recinfo c%rowtype;
187
188 cursor c1 is select
189 NAME,
190 DESCRIPTION,
191 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
192 from AMS_LIST_QUERIES_TL
193 where LIST_QUERY_ID = X_LIST_QUERY_ID
194 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
195 for update of LIST_QUERY_ID nowait;
196 begin
197 open c;
198 fetch c into recinfo;
199 if (c%notfound) then
200 close c;
201 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
202 app_exception.raise_exception;
203 end if;
204 close c;
205 if ( ((recinfo.QUERY_TEMPLATE_ID = X_QUERY_TEMPLATE_ID)
206 OR ((recinfo.QUERY_TEMPLATE_ID is null) AND (X_QUERY_TEMPLATE_ID is null)))
207 AND ((recinfo.OWNER_USER_ID = X_OWNER_USER_ID)
208 OR ((recinfo.OWNER_USER_ID is null) AND (X_OWNER_USER_ID is null)))
209 AND ((recinfo.QUERY_TYPE = X_QUERY_TYPE)
210 OR ((recinfo.QUERY_TYPE is null) AND (X_QUERY_TYPE is null)))
211 AND ((recinfo.ACT_LIST_QUERY_USED_BY_ID = X_ACT_LIST_QUERY_USED_BY_ID)
212 OR ((recinfo.ACT_LIST_QUERY_USED_BY_ID is null) AND (X_ACT_LIST_QUERY_USED_BY_ID is null)))
213 AND ((recinfo.ARC_ACT_LIST_QUERY_USED_BY = X_ARC_ACT_LIST_QUERY_USED_BY)
214 OR ((recinfo.ARC_ACT_LIST_QUERY_USED_BY is null) AND (X_ARC_ACT_LIST_QUERY_USED_BY is null)))
215 AND ((recinfo.SEED_FLAG = X_SEED_FLAG)
216 OR ((recinfo.SEED_FLAG is null) AND (X_SEED_FLAG is null)))
217 AND ((recinfo.SQL_STRING = X_SQL_STRING)
218 OR ((recinfo.SQL_STRING is null) AND (X_SQL_STRING is null)))
219 AND ((recinfo.SOURCE_OBJECT_NAME = X_SOURCE_OBJECT_NAME)
220 OR ((recinfo.SOURCE_OBJECT_NAME is null) AND (X_SOURCE_OBJECT_NAME is null)))
221 AND ((recinfo.PARENT_LIST_QUERY_ID = X_PARENT_LIST_QUERY_ID)
222 OR ((recinfo.PARENT_LIST_QUERY_ID is null) AND (X_PARENT_LIST_QUERY_ID is null)))
223 AND ((recinfo.SEQUENCE_ORDER = X_SEQUENCE_ORDER)
224 OR ((recinfo.SEQUENCE_ORDER is null) AND (X_SEQUENCE_ORDER is null)))
225 AND ((recinfo.PARAMETERIZED_FLAG = X_PARAMETERIZED_FLAG)
226 OR ((recinfo.PARAMETERIZED_FLAG is null) AND (X_PARAMETERIZED_FLAG is null)))
227 AND ((recinfo.ADMIN_FLAG = X_ADMIN_FLAG)
228 OR ((recinfo.ADMIN_FLAG is null) AND (X_ADMIN_FLAG is null)))
229 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
230 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
231 AND ((recinfo.TYPE = X_TYPE)
232 OR ((recinfo.TYPE is null) AND (X_TYPE is null)))
233 AND ((recinfo.QUERY = X_QUERY)
234 OR ((recinfo.QUERY is null) AND (X_QUERY is null)))
235 AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
236 OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
237 AND ((recinfo.PRIMARY_KEY = X_PRIMARY_KEY)
238 OR ((recinfo.PRIMARY_KEY is null) AND (X_PRIMARY_KEY is null)))
239 AND ((recinfo.PUBLIC_FLAG = X_PUBLIC_FLAG)
240 OR ((recinfo.PUBLIC_FLAG is null) AND (X_PUBLIC_FLAG is null)))
241 AND ((recinfo.COMMENTS = X_COMMENTS)
242 OR ((recinfo.COMMENTS is null) AND (X_COMMENTS is null)))
243 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
244 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
245 ) then
246 null;
247 else
248 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
249 app_exception.raise_exception;
250 end if;
251
252 for tlinfo in c1 loop
253 if (tlinfo.BASELANG = 'Y') then
254 if ( (tlinfo.NAME = X_NAME)
255 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
256 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
257 ) then
258 null;
259 else
260 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
261 app_exception.raise_exception;
262 end if;
263 end if;
264 end loop;
265 return;
266 end LOCK_ROW;
267
268 procedure UPDATE_ROW (
269 X_LIST_QUERY_ID in NUMBER,
270 X_QUERY_TEMPLATE_ID in NUMBER,
271 X_OWNER_USER_ID in NUMBER,
272 X_QUERY_TYPE in VARCHAR2,
273 X_ACT_LIST_QUERY_USED_BY_ID in NUMBER,
274 X_ARC_ACT_LIST_QUERY_USED_BY in VARCHAR2,
275 X_SEED_FLAG in VARCHAR2,
276 X_SQL_STRING in VARCHAR2,
277 X_SOURCE_OBJECT_NAME in VARCHAR2,
278 X_PARENT_LIST_QUERY_ID in NUMBER,
279 X_SEQUENCE_ORDER in NUMBER,
280 X_PARAMETERIZED_FLAG in VARCHAR2,
281 X_ADMIN_FLAG in VARCHAR2,
282 X_OBJECT_VERSION_NUMBER in NUMBER,
283 X_TYPE in VARCHAR2,
284 X_QUERY in LONG,
285 X_ENABLED_FLAG in VARCHAR2,
286 X_PRIMARY_KEY in VARCHAR2,
287 X_PUBLIC_FLAG in VARCHAR2,
288 X_COMMENTS in VARCHAR2,
289 X_SECURITY_GROUP_ID in NUMBER,
290 X_NAME in VARCHAR2,
291 X_DESCRIPTION in VARCHAR2,
292 X_LAST_UPDATE_DATE in DATE,
293 X_LAST_UPDATED_BY in NUMBER,
294 X_LAST_UPDATE_LOGIN in NUMBER
295 ) is
296 begin
297 update AMS_LIST_QUERIES_ALL set
298 QUERY_TEMPLATE_ID = X_QUERY_TEMPLATE_ID,
299 OWNER_USER_ID = X_OWNER_USER_ID,
300 QUERY_TYPE = X_QUERY_TYPE,
301 ACT_LIST_QUERY_USED_BY_ID = X_ACT_LIST_QUERY_USED_BY_ID,
302 ARC_ACT_LIST_QUERY_USED_BY = X_ARC_ACT_LIST_QUERY_USED_BY,
303 SEED_FLAG = X_SEED_FLAG,
304 SQL_STRING = X_SQL_STRING,
305 SOURCE_OBJECT_NAME = X_SOURCE_OBJECT_NAME,
306 PARENT_LIST_QUERY_ID = X_PARENT_LIST_QUERY_ID,
307 SEQUENCE_ORDER = X_SEQUENCE_ORDER,
308 PARAMETERIZED_FLAG = X_PARAMETERIZED_FLAG,
309 ADMIN_FLAG = X_ADMIN_FLAG,
310 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
311 TYPE = X_TYPE,
312 --QUERY = X_QUERY,
--bmuthukr. Bug 5334951
313 QUERY = NVL(X_QUERY,X_SQL_STRING),
314 ENABLED_FLAG = X_ENABLED_FLAG,
315 PRIMARY_KEY = X_PRIMARY_KEY,
316 PUBLIC_FLAG = X_PUBLIC_FLAG,
317 COMMENTS = X_COMMENTS,
318 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
319 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
320 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
321 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
322 where LIST_QUERY_ID = X_LIST_QUERY_ID;
323
324 if (sql%notfound) then
325 raise no_data_found;
326 end if;
327
328 update AMS_LIST_QUERIES_TL set
329 NAME = X_NAME,
330 DESCRIPTION = X_DESCRIPTION,
331 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
332 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
333 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
334 SOURCE_LANG = userenv('LANG')
335 where LIST_QUERY_ID = X_LIST_QUERY_ID
336 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
337
338 if (sql%notfound) then
339 raise no_data_found;
340 end if;
341 end UPDATE_ROW;
342
343
344 procedure DELETE_ROW (
345 X_LIST_QUERY_ID in NUMBER
346 ) is
347 begin
348 delete from AMS_LIST_QUERIES_TL
349 where LIST_QUERY_ID = X_LIST_QUERY_ID;
350
351 if (sql%notfound) then
352 raise no_data_found;
353 end if;
354
355 delete from AMS_LIST_QUERIES_ALL
356 where LIST_QUERY_ID = X_LIST_QUERY_ID;
357
358 if (sql%notfound) then
359 raise no_data_found;
360 end if;
361 end DELETE_ROW;
362
363
364 PROCEDURE LOAD_ROW (
365 X_LIST_QUERY_ID in NUMBER,
366 X_QUERY_TEMPLATE_ID in NUMBER,
367 X_OWNER_USER_ID in NUMBER,
368 X_QUERY_TYPE in VARCHAR2,
369 X_ACT_LIST_QUERY_USED_BY_ID in NUMBER,
370 X_ARC_ACT_LIST_QUERY_USED_BY in VARCHAR2,
371 X_SEED_FLAG in VARCHAR2,
372 X_SQL_STRING in VARCHAR2,
373 X_SOURCE_OBJECT_NAME in VARCHAR2,
374 X_PARENT_LIST_QUERY_ID in NUMBER,
375 X_SEQUENCE_ORDER in NUMBER,
376 X_PARAMETERIZED_FLAG in VARCHAR2,
377 X_ADMIN_FLAG in VARCHAR2,
378 X_TYPE in VARCHAR2,
379 X_QUERY in LONG,
380 X_ENABLED_FLAG in VARCHAR2,
381 X_PRIMARY_KEY in VARCHAR2,
382 X_PUBLIC_FLAG in VARCHAR2,
383 X_COMMENTS in VARCHAR2,
384 X_NAME in VARCHAR2,
385 X_DESCRIPTION in VARCHAR2,
386 x_owner IN VARCHAR2,
387 x_custom_mode IN VARCHAR2
388 )
389 IS
390 l_user_id number := 0;
391 l_obj_verno number;
392 l_dummy_char varchar2(1);
393 l_row_id varchar2(100);
394 l_LIST_QUERY_ID number;
395 l_last_updated_by number;
396
397 CURSOR c_obj_verno IS
398 SELECT object_version_number, last_updated_by
399 FROM AMS_LIST_QUERIES_ALL
400 WHERE LIST_QUERY_ID = X_LIST_QUERY_ID;
401
402 CURSOR c_chk_exists is
403 SELECT 'x'
404 FROM AMS_LIST_QUERIES_ALL
405 WHERE LIST_QUERY_ID = X_LIST_QUERY_ID;
406
407 CURSOR c_get_id is
408 SELECT AMS_LIST_QUERIES_ALL_S.NEXTVAL
409 FROM DUAL;
410 BEGIN
411 if X_OWNER = 'SEED' then
412 l_user_id := 1;
413 elsif X_OWNER = 'ORACLE' then
414 l_user_id := 2;
415 elsif X_OWNER = 'SYSADMIN' THEN
416 l_user_id := 0;
417 end if;
418
419 OPEN c_chk_exists;
420 FETCH c_chk_exists INTO l_dummy_char;
421 IF c_chk_exists%notfound THEN
422 CLOSE c_chk_exists;
423
424 IF X_LIST_QUERY_ID IS NULL THEN
425 OPEN c_get_id;
426 FETCH c_get_id INTO l_LIST_QUERY_ID;
427 CLOSE c_get_id;
428 ELSE
429 l_LIST_QUERY_ID := X_LIST_QUERY_ID;
430 END IF;
431
432 l_obj_verno := 1;
433
434 AMS_LIST_QUERIES_NEW_PKG.Insert_Row (
435 X_ROWID => l_row_id,
436 X_LIST_QUERY_ID => l_LIST_QUERY_ID,
437 X_QUERY_TEMPLATE_ID => X_QUERY_TEMPLATE_ID,
438 X_OWNER_USER_ID => X_OWNER_USER_ID,
439 X_QUERY_TYPE => X_QUERY_TYPE,
440 X_ACT_LIST_QUERY_USED_BY_ID => X_ACT_LIST_QUERY_USED_BY_ID,
441 X_ARC_ACT_LIST_QUERY_USED_BY => X_ARC_ACT_LIST_QUERY_USED_BY,
442 X_SEED_FLAG => X_SEED_FLAG,
443 X_SQL_STRING => X_SQL_STRING,
444 X_SOURCE_OBJECT_NAME => X_SOURCE_OBJECT_NAME,
445 X_PARENT_LIST_QUERY_ID => X_PARENT_LIST_QUERY_ID,
446 X_SEQUENCE_ORDER => X_SEQUENCE_ORDER,
447 X_PARAMETERIZED_FLAG => X_PARAMETERIZED_FLAG,
451 X_QUERY =>X_QUERY,
448 X_ADMIN_FLAG => X_ADMIN_FLAG,
449 X_OBJECT_VERSION_NUMBER => l_obj_verno,
450 X_TYPE =>X_TYPE,
452 X_ENABLED_FLAG =>X_ENABLED_FLAG,
453 X_PRIMARY_KEY =>X_PRIMARY_KEY,
454 X_PUBLIC_FLAG =>null,
455 X_COMMENTS =>null,
456 X_SECURITY_GROUP_ID => 0,
457 X_NAME =>X_NAME,
458 X_DESCRIPTION =>X_DESCRIPTION,
459 X_creation_date => SYSDATE,
460 X_created_by => l_user_id,
461 X_last_update_date => SYSDATE,
462 X_last_updated_by => l_user_id,
463 X_last_update_login => 0
464 );
465 ELSE
466 CLOSE c_chk_exists;
467 OPEN c_obj_verno;
468 FETCH c_obj_verno INTO l_obj_verno, l_last_updated_by;
469 CLOSE c_obj_verno;
470
471
472 if (l_last_updated_by in (1,2,0) OR
473 NVL(x_custom_mode,'PRESERVE')='FORCE') THEN
474
475 AMS_LIST_QUERIES_NEW_PKG.Update_Row (
476 X_LIST_QUERY_ID => x_LIST_QUERY_ID,
477 X_QUERY_TEMPLATE_ID => X_QUERY_TEMPLATE_ID,
478 X_OWNER_USER_ID => X_OWNER_USER_ID,
479 X_QUERY_TYPE => X_QUERY_TYPE,
480 X_ACT_LIST_QUERY_USED_BY_ID => X_ACT_LIST_QUERY_USED_BY_ID,
481 X_ARC_ACT_LIST_QUERY_USED_BY => X_ARC_ACT_LIST_QUERY_USED_BY,
482 X_SEED_FLAG => X_SEED_FLAG,
483 X_SQL_STRING => X_SQL_STRING,
484 X_SOURCE_OBJECT_NAME => X_SOURCE_OBJECT_NAME,
485 X_PARENT_LIST_QUERY_ID => X_PARENT_LIST_QUERY_ID,
486 X_SEQUENCE_ORDER => X_SEQUENCE_ORDER,
487 X_PARAMETERIZED_FLAG => X_PARAMETERIZED_FLAG,
488 X_ADMIN_FLAG => X_ADMIN_FLAG,
489 X_OBJECT_VERSION_NUMBER => l_obj_verno,
490 X_TYPE =>X_TYPE,
491 X_QUERY =>X_QUERY,
492 X_ENABLED_FLAG =>X_ENABLED_FLAG,
493 X_PRIMARY_KEY =>X_PRIMARY_KEY,
494 X_PUBLIC_FLAG =>null,
495 X_COMMENTS =>null,
496 X_SECURITY_GROUP_ID => 0,
497 X_NAME =>X_NAME,
498 X_DESCRIPTION =>X_DESCRIPTION,
499 X_last_update_date => SYSDATE,
500 X_last_updated_by => l_user_id,
501 X_last_update_login => 0
502 );
503
504 end if;
505 END IF;
506 END LOAD_ROW;
507
508 procedure ADD_LANGUAGE
509 is
510 begin
511 delete from AMS_LIST_QUERIES_TL T
512 where not exists
513 (select NULL
514 from AMS_LIST_QUERIES_ALL B
515 where B.LIST_QUERY_ID = T.LIST_QUERY_ID
516 );
517
518 update AMS_LIST_QUERIES_TL T set (
519 NAME,
520 DESCRIPTION
521 ) = (select
522 B.NAME,
523 B.DESCRIPTION
524 from AMS_LIST_QUERIES_TL B
525 where B.LIST_QUERY_ID = T.LIST_QUERY_ID
526 and B.LANGUAGE = T.SOURCE_LANG)
527 where (
528 T.LIST_QUERY_ID,
529 T.LANGUAGE
530 ) in (select
531 SUBT.LIST_QUERY_ID,
532 SUBT.LANGUAGE
533 from AMS_LIST_QUERIES_TL SUBB, AMS_LIST_QUERIES_TL SUBT
534 where SUBB.LIST_QUERY_ID = SUBT.LIST_QUERY_ID
535 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
536 and (SUBB.NAME <> SUBT.NAME
537 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
538 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
539 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
540 ));
541
542 insert into AMS_LIST_QUERIES_TL (
543 CREATION_DATE,
544 CREATED_BY,
545 LAST_UPDATE_LOGIN,
546 NAME,
547 DESCRIPTION,
548 LIST_QUERY_ID,
549 LAST_UPDATE_DATE,
550 LAST_UPDATED_BY,
551 LANGUAGE,
552 SOURCE_LANG
553 ) select /*+ ORDERED */
554 B.CREATION_DATE,
555 B.CREATED_BY,
556 B.LAST_UPDATE_LOGIN,
557 B.NAME,
558 B.DESCRIPTION,
559 B.LIST_QUERY_ID,
560 B.LAST_UPDATE_DATE,
561 B.LAST_UPDATED_BY,
562 L.LANGUAGE_CODE,
563 B.SOURCE_LANG
564 from AMS_LIST_QUERIES_TL B, FND_LANGUAGES L
565 where L.INSTALLED_FLAG in ('I', 'B')
566 and B.LANGUAGE = userenv('LANG')
567 and not exists
568 (select NULL
569 from AMS_LIST_QUERIES_TL T
570 where T.LIST_QUERY_ID = B.LIST_QUERY_ID
571 and T.LANGUAGE = L.LANGUAGE_CODE);
572 end ADD_LANGUAGE;
573
574 procedure TRANSLATE_ROW(
575 X_LIST_QUERY_ID in NUMBER,
576 X_NAME in VARCHAR2,
577 X_DESCRIPTION in VARCHAR2,
578 x_owner in VARCHAR2,
579 x_custom_mode in VARCHAR2
580 ) is
581
582 cursor c_last_updated_by is
583 select last_updated_by
584 FROM AMS_LIST_QUERIES_TL
585 where LIST_QUERY_ID = x_LIST_QUERY_ID
586 and USERENV('LANG') = LANGUAGE;
587
588 l_last_updated_by number;
589
590 begin
591
592
593 open c_last_updated_by;
594 fetch c_last_updated_by into l_last_updated_by;
595 close c_last_updated_by;
596
597 if (l_last_updated_by in (1,2,0) OR
598 NVL(x_custom_mode,'PRESERVE')='FORCE') THEN
599
600 update AMS_LIST_QUERIES_TL set
601 NAME= nvl(X_NAME, NAME),
602 DESCRIPTION= nvl(X_DESCRIPTION, DESCRIPTION),
603 source_lang = userenv('LANG'),
604 last_update_date = sysdate,
605 last_updated_by = decode(x_owner, 'SEED', 1, 'ORACLE',2, 'SYSADMIN',0, -1),
606 last_update_login = 0
607 where LIST_QUERY_ID = X_LIST_QUERY_ID
608 and userenv('LANG') in (language, source_lang);
609 end if;
610
611 end TRANSLATE_ROW;
612
613
614
615 end AMS_LIST_QUERIES_NEW_PKG;