[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_QUERIES_PKG
Source
1 PACKAGE BODY AMS_LIST_QUERIES_PKG as
2 /* $Header: amstliqb.pls 120.3 2006/06/27 06:21:16 bmuthukr noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_LIST_QUERIES_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_LIST_QUERIES_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstliqb.pls';
19
20
21 ----------------------------------------------------------
22 ---- MEDIA ----
23 ----------------------------------------------------------
24
25 -- ========================================================
26 --
27 -- NAME
28 -- createInsertBody
29 --
30 -- PURPOSE
31 --
32 -- NOTES
33 --
34 -- HISTORY
35 --
36 -- ========================================================
37 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
38 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
39 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
40
41 PROCEDURE Insert_Row(
42 px_list_query_id IN OUT NOCOPY NUMBER,
43 p_last_update_date DATE,
44 p_last_updated_by NUMBER,
45 p_creation_date DATE,
46 p_created_by NUMBER,
47 p_last_update_login NUMBER,
48 px_object_version_number IN OUT NOCOPY NUMBER,
49 p_name VARCHAR2,
50 p_type VARCHAR2,
51 p_enabled_flag VARCHAR2,
52 p_primary_key VARCHAR2,
53 p_source_object_name VARCHAR2,
54 p_public_flag VARCHAR2,
55 px_org_id IN OUT NOCOPY NUMBER,
56 p_comments VARCHAR2,
57 p_act_list_query_used_by_id NUMBER,
58 p_arc_act_list_query_used_by VARCHAR2,
59 p_sql_string VARCHAR2,
60 p_parent_list_query_id number,
61 p_sequence_order in number)
62
63 IS
64 x_rowid VARCHAR2(30);
65
66
67 BEGIN
68
69 IF (px_org_id IS NULL OR px_org_id = FND_API.G_MISS_NUM) THEN
70 SELECT NVL(rtrim(ltrim(SUBSTRB(USERENV('CLIENT_INFO'),1,10))),
71 '')
72 INTO px_org_id
73 FROM DUAL;
74 END IF;
75
76
77 px_object_version_number := 1;
78
79
80 INSERT INTO AMS_LIST_QUERIES_ALL(
81 list_query_id,
82 last_update_date,
83 last_updated_by,
84 creation_date,
85 created_by,
86 last_update_login,
87 object_version_number,
88 name,
89 type,
90 enabled_flag,
91 primary_key,
92 source_object_name ,
93 public_flag,
94 org_id,
95 comments,
96 act_list_query_used_by_id,
97 arc_act_list_query_used_by,
98 -- sql_string,
99 query,
100 parent_list_query_id ,
101 sequence_order
102 ) VALUES (
103 DECODE( px_list_query_id, FND_API.g_miss_num, NULL, px_list_query_id),
104 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
105 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
106 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
107 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
108 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
109 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
110 null,--DECODE( p_name, FND_API.g_miss_char, NULL, p_name),
111 DECODE( p_type, FND_API.g_miss_char, NULL, p_type),
112 DECODE( p_enabled_flag, FND_API.g_miss_char, 'Y', p_enabled_flag),
113 DECODE( p_primary_key, FND_API.g_miss_char, NULL, p_primary_key),
114 DECODE( p_source_object_name, FND_API.g_miss_char, NULL, p_source_object_name),
115 DECODE( p_public_flag, FND_API.g_miss_char,'N' , p_public_flag),
116 DECODE( px_org_id, FND_API.g_miss_num, NULL, px_org_id),
117 DECODE( p_comments, FND_API.g_miss_char, NULL, p_comments),
118 DECODE( p_act_list_query_used_by_id, FND_API.g_miss_num, NULL, p_act_list_query_used_by_id),
119 DECODE( p_arc_act_list_query_used_by, FND_API.g_miss_char, NULL, p_arc_act_list_query_used_by),
120 DECODE( p_sql_string, FND_API.g_miss_char, NULL, p_sql_string),
121 DECODE( p_parent_list_query_id, FND_API.g_miss_num, NULL, p_parent_list_query_id),
122 DECODE( p_sequence_order, FND_API.g_miss_num, NULL, p_sequence_order));
123
124
125 insert into AMS_LIST_QUERIES_TL (
126 LANGUAGE,
127 SOURCE_LANG,
128 NAME,
129 DESCRIPTION,
130 LIST_QUERY_ID,
131 LAST_UPDATE_DATE,
132 LAST_UPDATED_BY,
133 CREATION_DATE,
134 CREATED_BY,
135 LAST_UPDATE_LOGIN
136 ) select
137 l.language_code,
138 userenv('LANG'),
139 decode(p_name ,FND_API.g_miss_char,null,p_name) ,
140 decode(p_comments ,FND_API.g_miss_char,null,p_comments) ,
141 decode(px_list_query_id ,FND_API.g_miss_num,null,px_list_query_id) ,
142 sysdate,
143 FND_GLOBAL.user_id,
144 sysdate,
145 FND_GLOBAL.user_id,
146 FND_GLOBAL.conc_login_id
147 from FND_LANGUAGES L
148 where L.INSTALLED_FLAG in ('I', 'B')
149 and not exists
150 (select NULL
151 from AMS_LIST_QUERIES_TL T
152 where T.list_query_id = px_list_query_id
153 and T.LANGUAGE = L.LANGUAGE_CODE);
154
155 END Insert_Row;
156
157
158 ----------------------------------------------------------
159 ---- MEDIA ----
160 ----------------------------------------------------------
161
162 -- ========================================================
163 --
164 -- NAME
165 -- createUpdateBody
166 --
167 -- PURPOSE
168 --
169 -- NOTES
170 --
171 -- HISTORY
172 --
173 -- ========================================================
174 PROCEDURE Update_Row(
175 p_list_query_id NUMBER,
176 p_last_update_date DATE,
177 p_last_updated_by NUMBER,
178 p_creation_date DATE,
179 p_created_by NUMBER,
180 p_last_update_login NUMBER,
181 p_object_version_number NUMBER,
182 p_name VARCHAR2,
183 p_type VARCHAR2,
184 p_enabled_flag VARCHAR2,
185 p_primary_key VARCHAR2,
186 p_source_object_name VARCHAR2,
187 p_public_flag VARCHAR2,
188 p_org_id NUMBER,
189 p_comments VARCHAR2,
190 p_act_list_query_used_by_id NUMBER,
191 p_arc_act_list_query_used_by VARCHAR2,
192 p_sql_string VARCHAR2,
193 p_parent_list_query_id number,
194 p_sequence_order in number)
195 IS
196 BEGIN
197 Update AMS_LIST_QUERIES_ALL
198 SET
199 list_query_id = DECODE( p_list_query_id, FND_API.g_miss_num, list_query_id, p_list_query_id),
200 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
201 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
202 creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
203 created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
204 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
205 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, 1, p_object_version_number+1 ),
206 name = DECODE( p_name, FND_API.g_miss_char, name, p_name),
207 type = DECODE( p_type, FND_API.g_miss_char, type, p_type),
208 enabled_flag = DECODE( p_enabled_flag, FND_API.g_miss_char, enabled_flag, p_enabled_flag),
209 primary_key = DECODE( p_primary_key, FND_API.g_miss_char, primary_key, p_primary_key),
210 source_object_name = DECODE( p_source_object_name, FND_API.g_miss_char, NULL, p_source_object_name),
211 public_flag = DECODE( p_public_flag, FND_API.g_miss_char, public_flag, p_public_flag),
212 org_id = DECODE( p_org_id, FND_API.g_miss_num, org_id, p_org_id),
213 comments = DECODE( p_comments, FND_API.g_miss_char, comments, p_comments),
214 act_list_query_used_by_id = DECODE( p_act_list_query_used_by_id, FND_API.g_miss_num, act_list_query_used_by_id, p_act_list_query_used_by_id),
215 arc_act_list_query_used_by = DECODE( p_arc_act_list_query_used_by, FND_API.g_miss_char, arc_act_list_query_used_by, p_arc_act_list_query_used_by),
216 -- sql_string = DECODE( p_sql_string, FND_API.g_miss_char,sql_string, p_sql_string),
217 query = DECODE( p_sql_string, FND_API.g_miss_char,sql_string, p_sql_string),
218 parent_list_query_id = decode(p_parent_list_query_id, FND_API.g_miss_num, parent_list_query_id, p_parent_list_query_id),
219 sequence_order = decode(p_sequence_order, FND_API.g_miss_num, sequence_order, p_sequence_order)
220 WHERE LIST_QUERY_ID = p_LIST_QUERY_ID
221 AND object_version_number = p_object_version_number;
222
223 IF (SQL%NOTFOUND) THEN
224 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
225 END IF;
226 END Update_Row;
227
228
229 ----------------------------------------------------------
230 ---- MEDIA ----
231 ----------------------------------------------------------
232
233 -- ========================================================
234 --
235 -- NAME
236 -- createDeleteBody
237 --
238 -- PURPOSE
239 --
240 -- NOTES
241 --
242 -- HISTORY
243 --
244 -- ========================================================
245 PROCEDURE Delete_Row(
246 p_LIST_QUERY_ID NUMBER)
247 IS
248 BEGIN
249 DELETE FROM AMS_LIST_QUERIES_ALL
250 WHERE LIST_QUERY_ID = p_LIST_QUERY_ID;
251 If (SQL%NOTFOUND) then
252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
253 End If;
254 END Delete_Row ;
255
256
257
258 ----------------------------------------------------------
259 ---- MEDIA ----
260 ----------------------------------------------------------
261
262 -- ========================================================
263 --
264 -- NAME
265 -- createLockBody
266 --
267 -- PURPOSE
268 --
269 -- NOTES
270 --
271 -- HISTORY
272 --
273 -- ========================================================
274 PROCEDURE Lock_Row(
275 p_list_query_id NUMBER,
276 p_last_update_date DATE,
277 p_last_updated_by NUMBER,
278 p_creation_date DATE,
279 p_created_by NUMBER,
280 p_last_update_login NUMBER,
281 p_object_version_number NUMBER,
282 p_name VARCHAR2,
283 p_type VARCHAR2,
284 p_enabled_flag VARCHAR2,
285 p_primary_key VARCHAR2,
286 p_source_object_name VARCHAR2,
287 p_public_flag VARCHAR2,
288 p_org_id NUMBER,
289 p_comments VARCHAR2,
290 p_act_list_query_used_by_id NUMBER,
291 p_arc_act_list_query_used_by VARCHAR2,
292 p_sql_string VARCHAR2,
293 p_parent_list_query_id number,
294 p_sequence_order in number)
295
296 IS
297 CURSOR C IS
298 SELECT *
299 FROM AMS_LIST_QUERIES_ALL
300 WHERE LIST_QUERY_ID = p_LIST_QUERY_ID
301 FOR UPDATE of LIST_QUERY_ID NOWAIT;
302 Recinfo C%ROWTYPE;
303 BEGIN
304 OPEN c;
305 FETCH c INTO Recinfo;
306 If (c%NOTFOUND) then
307 CLOSE c;
308 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
309 APP_EXCEPTION.RAISE_EXCEPTION;
310 END IF;
311 CLOSE C;
312 END Lock_Row;
313
314 PROCEDURE load_row(
315 p_owner varchar2,
316 p_list_query_id NUMBER,
317 p_last_update_date DATE,
318 p_last_updated_by NUMBER,
319 p_creation_date DATE,
320 p_created_by NUMBER,
321 p_last_update_login NUMBER,
322 p_object_version_number NUMBER,
323 p_name VARCHAR2,
324 p_type VARCHAR2,
325 p_enabled_flag VARCHAR2,
326 p_primary_key VARCHAR2,
327 p_source_object_name VARCHAR2,
328 p_public_flag VARCHAR2,
329 p_org_id NUMBER,
330 p_comments VARCHAR2,
331 p_act_list_query_used_by_id NUMBER,
332 p_arc_act_list_query_used_by VARCHAR2,
333 p_sql_string VARCHAR2,
334 p_custom_mode VARCHAR2
335
336 ) is
337 l_d_object_version_number number;
338 x_return_status varchar2(1);
339 l_row_id varchar2(100);
340 l_user_id number;
341
342 l_object_version_number NUMBER := p_object_version_number ;
343 l_list_query_id NUMBER := p_list_query_id ;
344 l_org_id number := p_org_id;
345 l_last_updated_by number;
346 l_obj_verno NUMBER;
347
348 cursor c_chk_col_exists is
349 select object_version_number
350 from ams_list_queries_all
351 where list_query_id = p_list_query_id;
352
353 CURSOR c_obj_verno IS
354 SELECT object_version_number, last_updated_by
355 FROM ams_list_queries_all
356 where list_query_id = p_list_query_id;
357
358 begin
359 if p_OWNER = 'SEED' then
360 l_user_id := 1;
361 elsif p_OWNER = 'ORACLE' then
362 l_user_id := 2;
363 elsif p_OWNER = 'SYSADMIN' THEN
364 l_user_id := 0;
365
366 end if;
367 open c_chk_col_exists;
368 fetch c_chk_col_exists into l_d_object_version_number;
369 if c_chk_col_exists%notfound then
370 close c_chk_col_exists;
371
372 Insert_Row(
373 px_list_query_id => l_list_query_id ,
374 p_last_update_date => p_last_update_date,
375 p_last_updated_by => p_last_updated_by,
376 p_creation_date => p_creation_date,
377 p_created_by => p_created_by,
378 p_last_update_login => p_last_update_login,
379 px_object_version_number => l_object_version_number,
380 p_name => p_name ,
381 p_type => p_type,
382 p_enabled_flag => p_enabled_flag ,
383 p_primary_key => p_primary_key,
384 p_source_object_name => p_source_object_name ,
385 p_public_flag => p_public_flag ,
386 px_org_id => l_org_id ,
387 p_comments => p_comments ,
388 p_act_list_query_used_by_id => p_act_list_query_used_by_id ,
389 p_arc_act_list_query_used_by => p_arc_act_list_query_used_by ,
390 p_sql_string => p_sql_string ,
391 p_parent_list_query_id => l_list_query_id,
392 p_sequence_order => 1);
393 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
394 RAISE FND_API.G_EXC_ERROR;
395 END IF;
396 else
397 close c_chk_col_exists;
398
399 OPEN c_obj_verno;
400 FETCH c_obj_verno INTO l_obj_verno ,l_last_updated_by;
401 CLOSE c_obj_verno;
402
403
404 if (l_last_updated_by in (1,2,0) OR
405 NVL(p_custom_mode,'PRESERVE')='FORCE') THEN
406
407
408 Update_Row(
409 p_list_query_id => p_list_query_id ,
410 p_last_update_date => p_last_update_date ,
411 p_last_updated_by => p_last_updated_by ,
412 p_creation_date => p_creation_date ,
413 p_created_by => p_created_by ,
414 p_last_update_login => p_last_update_login ,
415 p_object_version_number => l_obj_verno ,
416 p_name => p_name ,
417 p_type => p_type ,
418 p_enabled_flag => p_enabled_flag ,
419 p_primary_key => p_primary_key ,
420 p_source_object_name => p_source_object_name ,
421 p_public_flag => p_public_flag ,
422 p_org_id => p_org_id ,
423 p_comments => p_comments ,
424 p_act_list_query_used_by_id => p_act_list_query_used_by_id ,
425 p_arc_act_list_query_used_by => p_arc_act_list_query_used_by ,
426 p_sql_string => p_sql_string,
427 p_parent_list_query_id => p_list_query_id ,
428 p_sequence_order => 1 );
429
430 end if;
431 --
432 end if;
433 end ;
434
435 --added for bug 5086232
436 procedure ADD_LANGUAGE
437 is
438 begin
439 delete from AMS_LIST_QUERIES_TL T
440 where not exists
441 (select NULL
442 from AMS_LIST_QUERIES_ALL B
443 where B.LIST_QUERY_ID = T.LIST_QUERY_ID
444 );
445
446 update AMS_LIST_QUERIES_TL T set (
447 NAME,
448 DESCRIPTION
449 ) = (select
450 B.NAME,
451 B.DESCRIPTION
452 from AMS_LIST_QUERIES_TL B
453 where B.LIST_QUERY_ID = T.LIST_QUERY_ID
454 and B.LANGUAGE = T.SOURCE_LANG)
455 where (
456 T.LIST_QUERY_ID,
457 T.LANGUAGE
458 ) in (select
459 SUBT.LIST_QUERY_ID,
460 SUBT.LANGUAGE
461 from AMS_LIST_QUERIES_TL SUBB, AMS_LIST_QUERIES_TL SUBT
462 where SUBB.LIST_QUERY_ID = SUBT.LIST_QUERY_ID
463 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
464 and (SUBB.NAME <> SUBT.NAME
465 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
466 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
467 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
468 ));
469
470 insert into AMS_LIST_QUERIES_TL (
471 CREATION_DATE,
472 CREATED_BY,
473 LAST_UPDATE_LOGIN,
474 NAME,
475 DESCRIPTION,
476 LIST_QUERY_ID,
477 LAST_UPDATE_DATE,
478 LAST_UPDATED_BY,
479 LANGUAGE,
480 SOURCE_LANG
481 ) select /*+ ORDERED */
482 B.CREATION_DATE,
483 B.CREATED_BY,
484 B.LAST_UPDATE_LOGIN,
485 B.NAME,
486 B.DESCRIPTION,
487 B.LIST_QUERY_ID,
488 B.LAST_UPDATE_DATE,
489 B.LAST_UPDATED_BY,
490 L.LANGUAGE_CODE,
491 B.SOURCE_LANG
492 from AMS_LIST_QUERIES_TL B, FND_LANGUAGES L
493 where L.INSTALLED_FLAG in ('I', 'B')
494 and B.LANGUAGE = userenv('LANG')
495 and not exists
496 (select NULL
497 from AMS_LIST_QUERIES_TL T
498 where T.LIST_QUERY_ID = B.LIST_QUERY_ID
499 and T.LANGUAGE = L.LANGUAGE_CODE);
500 end ADD_LANGUAGE;
501
502 PROCEDURE translate_row(
503 p_list_query_id in number,
504 p_name in varchar2,
505 p_owner in varchar2,
506 p_custom_mode in varchar2
507 ) is
508
509 cursor c_last_updated_by is
510 select last_updated_by
511 FROM AMS_LIST_QUERIES_TL
512 where LIST_QUERY_ID = p_list_query_id
513 and USERENV('LANG') = LANGUAGE;
514
515 l_last_updated_by number;
516
517 begin
518
519
520 open c_last_updated_by;
521 fetch c_last_updated_by into l_last_updated_by;
522 close c_last_updated_by;
523
524 if (l_last_updated_by in (1,2,0) OR
525 NVL(p_custom_mode,'PRESERVE')='FORCE') THEN
526 update ams_list_queries_tl
527 set name= nvl(p_name, name),
528 source_lang = userenv('LANG'),
529 last_update_date = sysdate,
530 last_updated_by = decode(p_owner, 'SEED', 1, 'ORACLE',2, 'SYSADMIN',0, -1),
531 last_update_login = 0
532 where LIST_QUERY_ID = p_list_query_id
533 and userenv('LANG') in (language, source_lang);
534 end if;
535
536 end TRANSLATE_ROW;
537
538 END AMS_LIST_QUERIES_PKG;