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