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