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