[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_QUERY_PVT
Source
1 PACKAGE BODY AMS_List_Query_PVT as
2 /* $Header: amsvliqb.pls 120.1 2005/09/20 05:50:34 aanjaria noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_List_Query_PVT
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_Query_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvliqb.pls';
19
20
21 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
22 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
23 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
24
25 PROCEDURE Complete_list_query_Rec (
26 P_list_query_rec IN list_query_rec_type,
27 x_complete_rec OUT NOCOPY list_query_rec_type
28 ) ;
29 PROCEDURE Complete_List_Query_Rec_tbl(
30 p_listquery_rec IN list_query_rec_type_tbl ,
31 x_complete_rec OUT NOCOPY list_query_rec_type_tbl
32 );
33 -- Hint: Primary key needs to be returned.
34 PROCEDURE Create_List_Query(
35 p_api_version_number IN NUMBER,
36 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
37 p_commit IN VARCHAR2 := FND_API.G_FALSE,
38 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
39 x_return_status OUT NOCOPY VARCHAR2,
40 x_msg_count OUT NOCOPY NUMBER,
41 x_msg_data OUT NOCOPY VARCHAR2,
42 p_list_query_rec IN list_query_rec_type := g_miss_list_query_rec,
43 x_list_query_id OUT NOCOPY NUMBER
44 )
45
46 IS
47 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_List_Query';
48 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
49 l_return_status_full VARCHAR2(1);
50 l_object_version_number NUMBER := 1;
51 l_org_id NUMBER := FND_API.G_MISS_NUM;
52 l_LIST_QUERY_ID NUMBER;
53 l_parent_list_query_id number;
54 l_dummy NUMBER;
55
56 CURSOR c_id IS
57 SELECT AMS_LIST_QUERIES_ALL_s.NEXTVAL
58 FROM dual;
59
60 CURSOR c_id_exists (l_id IN NUMBER) IS
61 SELECT 1 FROM dual
62 WHERE EXISTS (SELECT 1 FROM AMS_LIST_QUERIES_ALL
63 WHERE LIST_QUERY_ID = l_id);
64
65 BEGIN
66 -- Standard Start of API savepoint
67 SAVEPOINT CREATE_List_Query_PVT;
68
69 -- Standard call to check for call compatibility.
70 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
71 p_api_version_number,
72 l_api_name,
73 G_PKG_NAME)
74 THEN
75 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
76 END IF;
77
78 -- Initialize message list if p_init_msg_list is set to TRUE.
79 IF FND_API.to_Boolean( p_init_msg_list )
80 THEN
81 FND_MSG_PUB.initialize;
82 END IF;
83
84 -- Debug Message
85 IF (AMS_DEBUG_HIGH_ON) THEN
86
87 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
88 END IF;
89 -- Initialize API return status to SUCCESS
90 x_return_status := FND_API.G_RET_STS_SUCCESS;
91
92 -- Local variable initialization
93
94 IF p_list_query_rec.LIST_QUERY_ID IS NULL OR p_list_query_rec.LIST_QUERY_ID = FND_API.g_miss_num THEN
95 LOOP
96 l_dummy := NULL;
97 OPEN c_id;
98 FETCH c_id INTO l_LIST_QUERY_ID;
99 CLOSE c_id;
100
101 OPEN c_id_exists(l_LIST_QUERY_ID);
102 FETCH c_id_exists INTO l_dummy;
103 CLOSE c_id_exists;
104 EXIT WHEN l_dummy IS NULL;
105 END LOOP;
106 END IF;
107
108 -- =========================================================================
109 -- Validate Environment
110 -- =========================================================================
111
112 IF FND_GLOBAL.User_Id IS NULL
113 THEN
114 AMS_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
115 RAISE FND_API.G_EXC_ERROR;
116 END IF;
117
118 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
119 THEN
120 -- Debug message
121 IF (AMS_DEBUG_HIGH_ON) THEN
122
123 AMS_UTILITY_PVT.debug_message('Private API: Validate_List_Query');
124 END IF;
125
126 -- Invoke validation procedures
127 Validate_list_query(
128 p_api_version_number => 1.0,
129 p_init_msg_list => FND_API.G_FALSE,
130 p_validation_level => p_validation_level,
131 p_list_query_rec => p_list_query_rec,
132 x_return_status => x_return_status,
133 x_msg_count => x_msg_count,
134 x_msg_data => x_msg_data);
135 END IF;
136
137 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
138 RAISE FND_API.G_EXC_ERROR;
139 END IF;
140
141
142 -- Debug Message
143 if (p_list_query_rec.parent_list_query_id is null or
144 p_list_query_rec.parent_list_query_id = FND_API.g_miss_num )then
145 l_parent_list_query_id := l_list_query_id;
146 else
147 l_parent_list_query_id := p_list_query_rec.parent_list_query_id;
148 end if;
149 IF (AMS_DEBUG_HIGH_ON) THEN
150
151 AMS_UTILITY_PVT.debug_message( 'Private API: Call create table handler');
152 END IF;
153 -- Invoke table handler(AMS_LIST_QUERIES_PKG.Insert_Row)
154 AMS_LIST_QUERIES_PKG.Insert_Row(
155 px_list_query_id => l_list_query_id,
156 p_last_update_date => SYSDATE,
157 p_last_updated_by => FND_GLOBAL.USER_ID,
158 p_creation_date => SYSDATE,
159 p_created_by => FND_GLOBAL.USER_ID,
160 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
161 px_object_version_number => l_object_version_number,
162 p_name => p_list_query_rec.name,
163 p_type => p_list_query_rec.type,
164 p_enabled_flag => p_list_query_rec.enabled_flag,
165 p_primary_key => p_list_query_rec.primary_key,
166 p_source_object_name => p_list_query_rec.source_object_name,
167 p_public_flag => p_list_query_rec.public_flag,
168 px_org_id => l_org_id,
169 p_comments => p_list_query_rec.comments,
170 p_act_list_query_used_by_id
171 => p_list_query_rec.act_list_query_used_by_id,
172 p_arc_act_list_query_used_by
173 => p_list_query_rec.arc_act_list_query_used_by,
174 p_sql_string => p_list_query_rec.sql_string,
175 p_parent_list_query_id => l_parent_list_query_id,
176 p_sequence_order => p_list_query_rec.sequence_order);
177 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
178 RAISE FND_API.G_EXC_ERROR;
179 END IF;
180
181 x_list_query_id := l_list_query_id;
182 --
183 -- End of API body
184 --
185
186 -- Standard check for p_commit
187 IF FND_API.to_Boolean( p_commit )
188 THEN
189 COMMIT WORK;
190 END IF;
191
192
193 -- Debug Message
194 IF (AMS_DEBUG_HIGH_ON) THEN
195
196 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
197 END IF;
198
199 -- Standard call to get message count and if count is 1, get message info.
200 FND_MSG_PUB.Count_And_Get
201 (p_count => x_msg_count,
202 p_data => x_msg_data
203 );
204 EXCEPTION
205
206 WHEN AMS_Utility_PVT.resource_locked THEN
207 x_return_status := FND_API.g_ret_sts_error;
208 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
209
210 WHEN FND_API.G_EXC_ERROR THEN
211 ROLLBACK TO CREATE_List_Query_PVT;
212 x_return_status := FND_API.G_RET_STS_ERROR;
213 -- Standard call to get message count and if count=1, get the message
214 FND_MSG_PUB.Count_And_Get (
215 p_encoded => FND_API.G_FALSE,
216 p_count => x_msg_count,
217 p_data => x_msg_data
218 );
219
220 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
221 ROLLBACK TO CREATE_List_Query_PVT;
222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
223 -- Standard call to get message count and if count=1, get the message
224 FND_MSG_PUB.Count_And_Get (
225 p_encoded => FND_API.G_FALSE,
226 p_count => x_msg_count,
227 p_data => x_msg_data
228 );
229
230 WHEN OTHERS THEN
231 ROLLBACK TO CREATE_List_Query_PVT;
232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
233 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
234 THEN
235 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
236 END IF;
237 -- Standard call to get message count and if count=1, get the message
238 FND_MSG_PUB.Count_And_Get (
239 p_encoded => FND_API.G_FALSE,
240 p_count => x_msg_count,
241 p_data => x_msg_data
242 );
243 End Create_List_Query;
244
245
246 PROCEDURE Create_List_Query(
247 p_api_version_number IN NUMBER,
248 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
249 p_commit IN VARCHAR2 := FND_API.G_FALSE,
250 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
251 x_return_status OUT NOCOPY VARCHAR2,
252 x_msg_count OUT NOCOPY NUMBER,
253 x_msg_data OUT NOCOPY VARCHAR2,
254 p_list_query_rec_tbl IN list_query_rec_type_tbl ,
255 p_sql_string_tbl in sql_string_tbl ,
256 x_parent_list_query_id OUT NOCOPY NUMBER
257 ) is
258 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_List_Query_tbl';
259 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
260 l_list_query_rec list_query_rec_type;
261 l_list_query_id number;
262 begin
263 x_parent_list_query_id := FND_API.g_miss_num;
264 -- Standard Start of API savepoint
265 SAVEPOINT CREATE_List_Query_PVT_TBL;
266
267 -- Standard call to check for call compatibility.
268 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
269 p_api_version_number,
270 l_api_name,
271 G_PKG_NAME)
272 THEN
273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
274 END IF;
275
276 -- Initialize message list if p_init_msg_list is set to TRUE.
277 IF FND_API.to_Boolean( p_init_msg_list )
278 THEN
279 FND_MSG_PUB.initialize;
280 END IF;
281
282 -- Debug Message
283 IF (AMS_DEBUG_HIGH_ON) THEN
284
285 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
286 END IF;
287 -- Initialize API return status to SUCCESS
288 x_return_status := FND_API.G_RET_STS_SUCCESS;
289
290 -- Local variable initialization
291
292 -- =========================================================================
293 -- Validate Environment
294 -- =========================================================================
295
296 IF FND_GLOBAL.User_Id IS NULL
297 THEN
298 AMS_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
299 RAISE FND_API.G_EXC_ERROR;
300 END IF;
301
302 for i in 1 .. p_sql_string_tbl.last
303 loop
304
305 l_list_query_rec.LIST_QUERY_ID := p_list_query_rec_tbl.LIST_QUERY_ID;
306 l_list_query_rec.LAST_UPDATE_DATE := p_list_query_rec_tbl.LAST_UPDATE_DATE;
307 l_list_query_rec.LAST_UPDATED_BY := p_list_query_rec_tbl.LAST_UPDATED_BY;
308 l_list_query_rec.CREATION_DATE := p_list_query_rec_tbl.CREATION_DATE;
309 l_list_query_rec.CREATED_BY := p_list_query_rec_tbl.CREATED_BY;
310 l_list_query_rec.LAST_UPDATE_LOGIN := p_list_query_rec_tbl.LAST_UPDATE_LOGIN;
311 l_list_query_rec.OBJECT_VERSION_NUMBER := p_list_query_rec_tbl.OBJECT_VERSION_NUMBER;
312 l_list_query_rec.NAME := p_list_query_rec_tbl.NAME;
313 l_list_query_rec.TYPE := p_list_query_rec_tbl.TYPE;
314 l_list_query_rec.ENABLED_FLAG := p_list_query_rec_tbl.ENABLED_FLAG;
315 l_list_query_rec.PRIMARY_KEY := p_list_query_rec_tbl.PRIMARY_KEY;
316 l_list_query_rec.PUBLIC_FLAG := p_list_query_rec_tbl.PUBLIC_FLAG;
317 l_list_query_rec.ORG_ID := p_list_query_rec_tbl.ORG_ID;
318 l_list_query_rec.COMMENTS := p_list_query_rec_tbl.COMMENTS;
319 l_list_query_rec.ACT_LIST_QUERY_USED_BY_ID :=
320 p_list_query_rec_tbl.ACT_LIST_QUERY_USED_BY_ID;
321 l_list_query_rec.ARC_ACT_LIST_QUERY_USED_BY :=
322 p_list_query_rec_tbl.ARC_ACT_LIST_QUERY_USED_BY;
323 l_list_query_rec.SEED_FLAG := p_list_query_rec_tbl.SEED_FLAG;
324 l_list_query_rec.SQL_STRING :=
325 p_sql_string_tbl(i);
326 l_list_query_rec.SOURCE_OBJECT_NAME :=
327 p_list_query_rec_tbl.SOURCE_OBJECT_NAME;
328 l_list_query_rec.PARENT_LIST_QUERY_ID := x_parent_list_query_id ;
329 l_list_query_rec.SEQUENCE_ORDER := i;
330 --IF (AMS_DEBUG_HIGH_ON) THENAMS_UTILITY_PVT.debug_message( 'Private API: Call create Query - Child');END IF;
331 -- Invoke table handler(AMS_LIST_QUERIES_PKG.Insert_Row)
332 Create_List_Query(
333 p_api_version_number => p_api_version_number,
334 p_init_msg_list => p_init_msg_list,
335 p_commit => p_commit ,
336 p_validation_level => p_validation_level ,
337 x_return_status => x_return_status ,
338 x_msg_count => x_msg_count ,
339 x_msg_data => x_msg_data ,
340 p_list_query_rec => l_list_query_rec ,
341 x_list_query_id => l_list_query_id
342 );
343 if i = 1 then
344 x_parent_list_query_id := l_list_query_id;
345 end if;
346
347 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
348 RAISE FND_API.G_EXC_ERROR;
349 END IF;
350 --
351 end loop;
352 --
353 -- End of API body
354 --
355
356 -- Standard check for p_commit
357 IF FND_API.to_Boolean( p_commit )
358 THEN
359 COMMIT WORK;
360 END IF;
361
362
363 -- Debug Message
364 IF (AMS_DEBUG_HIGH_ON) THEN
365
366 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
367 END IF;
368
369 -- Standard call to get message count and if count is 1, get message info.
370 FND_MSG_PUB.Count_And_Get
371 (p_count => x_msg_count,
372 p_data => x_msg_data
373 );
374 EXCEPTION
375
376 WHEN AMS_Utility_PVT.resource_locked THEN
377 x_return_status := FND_API.g_ret_sts_error;
378 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
379
380 WHEN FND_API.G_EXC_ERROR THEN
381 ROLLBACK TO CREATE_List_Query_PVT_TBL;
382 x_return_status := FND_API.G_RET_STS_ERROR;
383 -- Standard call to get message count and if count=1, get the message
384 FND_MSG_PUB.Count_And_Get (
385 p_encoded => FND_API.G_FALSE,
386 p_count => x_msg_count,
387 p_data => x_msg_data
388 );
389
390 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
391 ROLLBACK TO CREATE_List_Query_PVT_TBL;
392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
393 -- Standard call to get message count and if count=1, get the message
394 FND_MSG_PUB.Count_And_Get (
395 p_encoded => FND_API.G_FALSE,
396 p_count => x_msg_count,
397 p_data => x_msg_data
398 );
399
400 WHEN OTHERS THEN
401 ROLLBACK TO CREATE_List_Query_PVT_TBL;
402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
403 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
404 THEN
405 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
406 END IF;
407 -- Standard call to get message count and if count=1, get the message
408 FND_MSG_PUB.Count_And_Get (
409 p_encoded => FND_API.G_FALSE,
410 p_count => x_msg_count,
411 p_data => x_msg_data
412 );
413 end create_list_query;
414
415 PROCEDURE Create_List_Query(
416 p_api_version_number IN NUMBER,
417 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
418 p_commit IN VARCHAR2 := FND_API.G_FALSE,
419 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
420 x_return_status OUT NOCOPY VARCHAR2,
421 x_msg_count OUT NOCOPY NUMBER,
422 x_msg_data OUT NOCOPY VARCHAR2,
423 p_list_query_rec_tbl IN list_query_rec_type_tbl ,--:= g_miss_list_query_tbl ,
424 p_sql_string_tbl in sql_string_tbl ,
425 p_query_param in sql_string_tbl ,
426 x_parent_list_query_id OUT NOCOPY NUMBER
427 ) is
428 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_List_Query_tbl';
429 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
430 l_list_query_rec list_query_rec_type;
431 l_list_query_id number;
432 l_parent_list_query_id number;
433 l_return_status VARCHAR2(2000);
434 l_msg_count NUMBER;
435 l_msg_data VARCHAR2(2000);
436 begin
437 x_parent_list_query_id := FND_API.g_miss_num;
438 -- Standard Start of API savepoint
439 SAVEPOINT CREATE_List_Query_PVT_TBL_;
440
441 -- Standard call to check for call compatibility.
442 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
443 p_api_version_number,
444 l_api_name,
445 G_PKG_NAME)
446 THEN
447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
448 END IF;
449
450 -- Initialize message list if p_init_msg_list is set to TRUE.
451 IF FND_API.to_Boolean( p_init_msg_list )
452 THEN
453 FND_MSG_PUB.initialize;
454 END IF;
455
456 -- Debug Message
457 IF (AMS_DEBUG_HIGH_ON) THEN
458
459 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
460 END IF;
461 -- Initialize API return status to SUCCESS
462 x_return_status := FND_API.G_RET_STS_SUCCESS;
463
464 -- Local variable initialization
465
466 -- =========================================================================
467 -- Validate Environment
468 -- =========================================================================
469
470 IF FND_GLOBAL.User_Id IS NULL
471 THEN
472 AMS_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
473 RAISE FND_API.G_EXC_ERROR;
474 END IF;
475
476 Create_List_Query(
477 p_api_version_number => p_api_version_number ,
478 p_init_msg_list => p_init_msg_list ,
479 p_commit => p_commit ,
480 p_validation_level => p_validation_level ,
481 x_return_status => l_return_status ,
482 x_msg_count => l_msg_count ,
483 x_msg_data => l_msg_data ,
484 p_list_query_rec_tbl => p_list_query_rec_tbl ,
485 p_sql_string_tbl => p_sql_string_tbl ,
486 x_parent_list_query_id => l_parent_list_query_id
487 ) ;
488 x_return_status := l_return_status ;
489 x_msg_count := l_msg_count ;
490 x_msg_data := l_msg_data ;
491 x_parent_list_query_id := l_parent_list_query_id ;
492 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
493 RAISE FND_API.G_EXC_ERROR;
494 END IF;
495 update ams_list_queries_all set parameterized_flag = 'Y'
496 where parent_list_query_id = l_parent_list_query_id;
497 delete from ams_list_queries_param
498 where list_query_id = l_parent_list_query_id ;
499 FOR I in p_query_param.first .. p_query_param.last
500 loop
501 INSERT INTO AMS_LIST_QUERIES_PARAM(
502 LIST_QUERY_PARAM_ID ,
503 LIST_QUERY_ID ,
504 LAST_UPDATE_DATE ,
505 LAST_UPDATED_BY ,
506 CREATION_DATE ,
507 CREATED_BY ,
508 LAST_UPDATE_LOGIN ,
509 OBJECT_VERSION_NUMBER ,
510 PARAMETER_ORDER ,
511 PARAMETER_VALUE
512 )
513 VALUES (
514 AMS_LIST_QUERIES_PARAM_S.NEXTVAL
515 ,l_parent_list_query_id
516 ,SYSDATE
517 ,FND_GLOBAL.User_Id
518 ,SYSDATE
519 ,FND_GLOBAL.User_Id
520 ,FND_GLOBAL.Conc_Login_Id
521 ,1
522 ,i
523 , p_query_param(i)
524 ) ;
525 end loop;
526 --
527 --
528 -- End of API body
529 --
530
531 -- Standard check for p_commit
532 IF FND_API.to_Boolean( p_commit )
533 THEN
534 COMMIT WORK;
535 END IF;
536
537
538 -- Debug Message
539 IF (AMS_DEBUG_HIGH_ON) THEN
540
541 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
542 END IF;
543
544 -- Standard call to get message count and if count is 1, get message info.
545 FND_MSG_PUB.Count_And_Get
546 (p_count => x_msg_count,
547 p_data => x_msg_data
548 );
549 EXCEPTION
550
551 WHEN AMS_Utility_PVT.resource_locked THEN
552 x_return_status := FND_API.g_ret_sts_error;
553 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
554
555 WHEN FND_API.G_EXC_ERROR THEN
556 ROLLBACK TO CREATE_List_Query_PVT_TBL_;
557 x_return_status := FND_API.G_RET_STS_ERROR;
558 -- Standard call to get message count and if count=1, get the message
559 FND_MSG_PUB.Count_And_Get (
560 p_encoded => FND_API.G_FALSE,
561 p_count => x_msg_count,
562 p_data => x_msg_data
563 );
564
565 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
566 ROLLBACK TO CREATE_List_Query_PVT_TBL_;
567 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
568 -- Standard call to get message count and if count=1, get the message
569 FND_MSG_PUB.Count_And_Get (
570 p_encoded => FND_API.G_FALSE,
571 p_count => x_msg_count,
572 p_data => x_msg_data
573 );
574
575 WHEN OTHERS THEN
576 ROLLBACK TO CREATE_List_Query_PVT_TBL_;
577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
578 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
579 THEN
580 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
581 END IF;
582 -- Standard call to get message count and if count=1, get the message
583 FND_MSG_PUB.Count_And_Get (
584 p_encoded => FND_API.G_FALSE,
585 p_count => x_msg_count,
586 p_data => x_msg_data
587 );
588 end ;
589
590
591 PROCEDURE Update_List_Query(
592 p_api_version_number IN NUMBER,
593 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
594 p_commit IN VARCHAR2 := FND_API.G_FALSE,
595 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
596 x_return_status OUT NOCOPY VARCHAR2,
597 x_msg_count OUT NOCOPY NUMBER,
598 x_msg_data OUT NOCOPY VARCHAR2,
599 p_list_query_rec IN list_query_rec_type,
600 x_object_version_number OUT NOCOPY NUMBER
601 )
602
603 IS
604
605 CURSOR c_get_list_query(list_query_id NUMBER) IS
606 SELECT *
607 FROM AMS_LIST_QUERIES_ALL
608 WHERE list_query_id = p_list_query_rec.list_query_id;
609
610 -- Hint: Developer need to provide Where clause
611
612 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_List_Query';
613 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
614 -- Local Variables
615 l_object_version_number NUMBER;
616 l_LIST_QUERY_ID NUMBER;
617 l_ref_list_query_rec c_get_List_Query%ROWTYPE ;
618 l_tar_list_query_rec AMS_List_Query_PVT.list_query_rec_type := P_list_query_rec;
619 l_list_query_rec AMS_List_Query_PVT.list_query_rec_type := P_list_query_rec;
620 l_rowid ROWID;
621
622 BEGIN
623 -- Standard Start of API savepoint
624 SAVEPOINT UPDATE_List_Query_PVT;
625
626 -- Standard call to check for call compatibility.
627 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
628 p_api_version_number,
629 l_api_name,
630 G_PKG_NAME)
631 THEN
632 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
633 END IF;
634
635 -- Initialize message list if p_init_msg_list is set to TRUE.
636 IF FND_API.to_Boolean( p_init_msg_list )
637 THEN
638 FND_MSG_PUB.initialize;
639 END IF;
640
641 -- Debug Message
642 IF (AMS_DEBUG_HIGH_ON) THEN
643
644 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
645 END IF;
646
647
648 -- Initialize API return status to SUCCESS
649 x_return_status := FND_API.G_RET_STS_SUCCESS;
650
651 -- Debug Message
652 --IF (AMS_DEBUG_HIGH_ON) THENAMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');END IF;
653
654
655 OPEN c_get_List_Query( l_tar_list_query_rec.list_query_id);
656
657 FETCH c_get_List_Query INTO l_ref_list_query_rec ;
658
659 IF ( c_get_List_Query%NOTFOUND) THEN
660 AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
661 p_token_name => 'INFO',
662 p_token_value => 'List_Query') ;
663 RAISE FND_API.G_EXC_ERROR;
664 END IF;
665 -- Debug Message
666 IF (AMS_DEBUG_HIGH_ON) THEN
667
668 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
669 END IF;
670 CLOSE c_get_List_Query;
671
672
673 IF (l_tar_list_query_rec.object_version_number is NULL or
674 l_tar_list_query_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
675 AMS_Utility_PVT.Error_Message(p_message_name
676 => 'API_VERSION_MISSING',
677 p_token_name => 'COLUMN',
678 p_token_value
679 => 'Object_version Number') ;
680 raise FND_API.G_EXC_ERROR;
681 END IF;
682
683 -- Check Whether record has been changed by someone else
684 IF (l_tar_list_query_rec.object_version_number <> l_ref_list_query_rec.object_version_number) THEN
685 AMS_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
686 p_token_name => 'INFO',
687 p_token_value => 'List_Query') ;
688 raise FND_API.G_EXC_ERROR;
689 END IF;
690
691 Complete_list_query_Rec(
692 p_list_query_rec => p_list_query_rec,
693 x_complete_rec => l_list_query_rec
694 );
695 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
696 THEN
697 -- Debug message
698 IF (AMS_DEBUG_HIGH_ON) THEN
699
700 AMS_UTILITY_PVT.debug_message('Private API: Validate_List_Query');
701 END IF;
702
703 -- Invoke validation procedures
704 Validate_list_query(
705 p_api_version_number => 1.0,
706 p_init_msg_list => FND_API.G_FALSE,
707 p_validation_level => p_validation_level,
708 p_list_query_rec => l_list_query_rec,
709 x_return_status => x_return_status,
710 x_msg_count => x_msg_count,
711 x_msg_data => x_msg_data);
712 END IF;
713
714 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
715 RAISE FND_API.G_EXC_ERROR;
716 END IF;
717
718
719 -- Debug Message
720 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_UTILITY_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler'); END IF;
721
722
723 -- Invoke table handler(AMS_LIST_QUERIES_PKG.Update_Row)
724 AMS_LIST_QUERIES_PKG.Update_Row(
725 p_list_query_id => l_list_query_rec.list_query_id,
726 p_last_update_date => SYSDATE,
727 p_last_updated_by => FND_GLOBAL.USER_ID,
728 p_creation_date => l_list_query_rec.creation_date,
729 p_created_by => l_list_query_rec.created_by,
730 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
731 p_object_version_number => l_list_query_rec.object_version_number,
732 p_name => l_list_query_rec.name,
733 p_type => l_list_query_rec.type,
734 p_enabled_flag => l_list_query_rec.enabled_flag,
735 p_primary_key => l_list_query_rec.primary_key,
736 p_source_object_name => l_list_query_rec.source_object_name,
737 p_public_flag => l_list_query_rec.public_flag,
738 p_org_id => l_list_query_rec.org_id,
739 p_comments => l_list_query_rec.comments,
740 p_act_list_query_used_by_id =>
741 l_list_query_rec.act_list_query_used_by_id,
742 p_arc_act_list_query_used_by =>
743 l_list_query_rec.arc_act_list_query_used_by,
744 p_sql_string => l_list_query_rec.sql_string,
745 p_parent_list_query_id => p_list_query_rec.parent_list_query_id,
746 p_sequence_order => p_list_query_rec.sequence_order);
747 --
748 -- End of API body.
749 --
750 -- Standard check for p_commit
751 IF FND_API.to_Boolean( p_commit )
752 THEN
753 COMMIT WORK;
754 END IF;
755
756 --Set object version number
757 x_object_version_number:=l_list_query_rec.object_version_number+1;
758
759
760 -- Debug Message
761 IF (AMS_DEBUG_HIGH_ON) THEN
762
763 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
764 END IF;
765
766 -- Standard call to get message count and if count is 1, get message info.
767 FND_MSG_PUB.Count_And_Get
768 (p_count => x_msg_count,
769 p_data => x_msg_data
770 );
771 EXCEPTION
772
773 WHEN AMS_Utility_PVT.resource_locked THEN
774 x_return_status := FND_API.g_ret_sts_error;
775 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
776
777 WHEN FND_API.G_EXC_ERROR THEN
778 ROLLBACK TO UPDATE_List_Query_PVT;
779 x_return_status := FND_API.G_RET_STS_ERROR;
780 -- Standard call to get message count and if count=1, get the message
781 FND_MSG_PUB.Count_And_Get (
782 p_encoded => FND_API.G_FALSE,
783 p_count => x_msg_count,
784 p_data => x_msg_data
785 );
786
787 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
788 ROLLBACK TO UPDATE_List_Query_PVT;
789 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
790 -- Standard call to get message count and if count=1, get the message
791 FND_MSG_PUB.Count_And_Get (
792 p_encoded => FND_API.G_FALSE,
793 p_count => x_msg_count,
794 p_data => x_msg_data
795 );
796
797 WHEN OTHERS THEN
798 ROLLBACK TO UPDATE_List_Query_PVT;
799 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
800 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
801 THEN
802 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
803 END IF;
804 -- Standard call to get message count and if count=1, get the message
805 FND_MSG_PUB.Count_And_Get (
806 p_encoded => FND_API.G_FALSE,
807 p_count => x_msg_count,
808 p_data => x_msg_data
809 );
810 End Update_List_Query;
811 PROCEDURE Update_List_Query(
812 p_api_version_number IN NUMBER,
813 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
814 p_commit IN VARCHAR2 := FND_API.G_FALSE,
815 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
816 x_return_status OUT NOCOPY VARCHAR2,
817 x_msg_count OUT NOCOPY NUMBER,
818 x_msg_data OUT NOCOPY VARCHAR2,
819 p_list_query_rec_tbl IN list_query_rec_type_tbl,
820 p_sql_string_tbl in sql_string_tbl ,
821 x_object_version_number OUT NOCOPY NUMBER
822 ) is
823
824
825 CURSOR c_get_list_query IS
826 SELECT *
827 FROM AMS_LIST_QUERIES_ALL
828 WHERE list_query_id = p_list_query_rec_tbl.parent_list_query_id;
829 cursor c_get_list_count is
830 SELECT count(1)
831 FROM AMS_LIST_QUERIES_ALL
832 WHERE parent_list_query_id = p_list_query_rec_tbl.parent_list_query_id;
833
834 -- Hint: Developer need to provide Where clause
835
836 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_List_Query_tbl';
837 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
838 -- Local Variables
839 l_object_version_number NUMBER;
840 l_LIST_QUERY_ID NUMBER;
841 l_list_query_temp number;
842 l_list_query_rec AMS_List_Query_PVT.list_query_rec_type ;
843 l_list_query_rec_tbl AMS_List_Query_PVT.list_query_rec_type_tbl ;
844 l_rowid ROWID;
845 l_no_of_records number;
846 l_no_of_records_upd number;
847 l_parent_list_query_id number;
848 cursor c_get_query_id (cur_parent_query_id number,
849 cur_sequence_order number) is
850 select list_query_id ,object_version_number
851 from ams_list_queries_all
852 where parent_list_query_id = cur_parent_query_id
853 and sequence_order = cur_sequence_order ;
854
855 cursor c_query(cur_parent_query_id number) is
856 select query --sql_string column is obsolete bug 4604653
857 from ams_list_queries_all
858 where parent_list_query_id = cur_parent_query_id;
859 l_sql_string varchar2(4000);
860 l_sql_string_tbl AMS_List_Query_PVT.sql_string_tbl ;
861 j number:=0 ;
862 BEGIN
863 -- Standard Start of API savepoint
864 SAVEPOINT UPDATE_List_Query_PVT_TBL;
865
866 -- Standard call to check for call compatibility.
867 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
868 p_api_version_number,
869 l_api_name,
870 G_PKG_NAME)
871 THEN
872 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
873 END IF;
874
875 -- Initialize message list if p_init_msg_list is set to TRUE.
876 IF FND_API.to_Boolean( p_init_msg_list )
877 THEN
878 FND_MSG_PUB.initialize;
879 END IF;
880
881 -- Debug Message
882 IF (AMS_DEBUG_HIGH_ON) THEN
883
884 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
885 END IF;
886
887
888 -- Initialize API return status to SUCCESS
889 x_return_status := FND_API.G_RET_STS_SUCCESS;
890
891 -- Debug Message
892 --IF (AMS_DEBUG_HIGH_ON) THENAMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');END IF;
893
894
895 IF (AMS_DEBUG_HIGH_ON) THEN
896
897
898
899
900
901 AMS_UTILITY_PVT.debug_message('parent list header id'
902 || p_list_query_rec_tbl.PARENT_LIST_QUERY_ID );
903
904
905 END IF;
906 if p_list_query_rec_tbl.PARENT_LIST_QUERY_ID is null
907 or p_list_query_rec_tbl.PARENT_LIST_QUERY_ID = FND_API.G_MISS_NUM then
908 --gjoby Add proper message name
909 IF (AMS_DEBUG_HIGH_ON) THEN
910
911 AMS_UTILITY_PVT.debug_message('Please Provide parent list header id' );
912 END IF;
913 x_return_status := FND_API.g_ret_sts_error;
914 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
915 end if;
916
917 open c_get_list_count ;
918 fetch c_get_list_count into l_no_of_records ;
919 close c_get_list_count ;
920
921 Complete_list_query_Rec_tbl (
922 p_list_query_rec_tbl,
923 l_list_query_rec_tbl
924 );
925
926 if p_sql_string_tbl.count = 0 then
927 open c_query(p_list_query_rec_tbl.parent_list_query_id) ;
928 loop
929 fetch c_query into l_sql_string;
930 exit when c_query%notfound;
931 j := j+1;
932 l_sql_string_tbl(j) := l_sql_string;
933 end loop;
934 close c_query;
935 else
936 for j in 1 .. p_sql_string_tbl.last
937 loop
938 l_sql_string_tbl(j) := p_sql_string_tbl(j);
939 end loop;
940 end if;
941 for i in 1 .. l_sql_string_tbl.last
942 loop
943 l_list_query_rec.LIST_QUERY_ID := l_list_query_rec_tbl.LIST_QUERY_ID;
944 l_list_query_rec.LAST_UPDATE_DATE := l_list_query_rec_tbl.LAST_UPDATE_DATE;
945 l_list_query_rec.LAST_UPDATED_BY := l_list_query_rec_tbl.LAST_UPDATED_BY;
946 l_list_query_rec.CREATION_DATE := l_list_query_rec_tbl.CREATION_DATE;
947 l_list_query_rec.CREATED_BY := l_list_query_rec_tbl.CREATED_BY;
948 l_list_query_rec.LAST_UPDATE_LOGIN := l_list_query_rec_tbl.LAST_UPDATE_LOGIN;
949 l_list_query_rec.OBJECT_VERSION_NUMBER := l_list_query_rec_tbl.OBJECT_VERSION_NUMBER;
950 l_list_query_rec.NAME := l_list_query_rec_tbl.NAME;
951 l_list_query_rec.TYPE := l_list_query_rec_tbl.TYPE;
952 l_list_query_rec.ENABLED_FLAG := l_list_query_rec_tbl.ENABLED_FLAG;
953 l_list_query_rec.PRIMARY_KEY := l_list_query_rec_tbl.PRIMARY_KEY;
954 l_list_query_rec.PUBLIC_FLAG := l_list_query_rec_tbl.PUBLIC_FLAG;
955 l_list_query_rec.ORG_ID := l_list_query_rec_tbl.ORG_ID;
956 l_list_query_rec.COMMENTS := l_list_query_rec_tbl.COMMENTS;
957 l_list_query_rec.ACT_LIST_QUERY_USED_BY_ID :=
958 l_list_query_rec_tbl.ACT_LIST_QUERY_USED_BY_ID;
959 l_list_query_rec.ARC_ACT_LIST_QUERY_USED_BY :=
960 l_list_query_rec_tbl.ARC_ACT_LIST_QUERY_USED_BY;
961 l_list_query_rec.SEED_FLAG := l_list_query_rec_tbl.SEED_FLAG;
962 l_list_query_rec.SQL_STRING :=
963 l_sql_string_tbl(i);
964 l_list_query_rec.SOURCE_OBJECT_NAME :=
965 l_list_query_rec_tbl.SOURCE_OBJECT_NAME;
966 l_list_query_rec.PARENT_LIST_QUERY_ID := l_list_query_rec_tbl.PARENT_LIST_QUERY_ID ;
967 l_list_query_rec.SEQUENCE_ORDER := i;
968 IF (AMS_DEBUG_HIGH_ON) THEN
969
970 AMS_UTILITY_PVT.debug_message( 'Private API: Child->' || i || '<-'
971 || '->' || l_no_of_records|| '<-');
972 END IF;
973 if i > l_no_of_records then
974 --IF (AMS_DEBUG_HIGH_ON) THENAMS_UTILITY_PVT.debug_message( 'Private API: Create Child->' || i || '<-');END IF;
975 l_list_query_rec.LIST_QUERY_ID := '';
976 Create_List_Query(
977 p_api_version_number => p_api_version_number,
978 p_init_msg_list => p_init_msg_list,
979 p_commit => p_commit ,
980 p_validation_level => p_validation_level ,
981 x_return_status => x_return_status ,
982 x_msg_count => x_msg_count ,
983 x_msg_data => x_msg_data ,
984 p_list_query_rec => l_list_query_rec ,
985 x_list_query_id => l_list_query_id
986 );
987 else
988 IF (AMS_DEBUG_HIGH_ON) THEN
989
990 AMS_UTILITY_PVT.debug_message( '->' || l_list_query_rec.sequence_order || '<-');
991 END IF;
992 open c_get_query_id (l_list_query_rec.parent_list_query_id,
993 l_list_query_rec.sequence_order);
994 fetch c_get_query_id into l_list_query_rec.list_query_id,
995 l_list_query_rec.object_version_number;
996 IF (AMS_DEBUG_HIGH_ON) THEN
997
998 AMS_UTILITY_PVT.debug_message( '->' || l_list_query_rec.list_query_id || '<-');
999 END IF;
1000
1001 Update_List_Query(
1002 p_api_version_number => p_api_version_number,
1003 p_init_msg_list => p_init_msg_list,
1004 p_commit => p_commit ,
1005 p_validation_level => p_validation_level ,
1006 x_return_status => x_return_status ,
1007 x_msg_count => x_msg_count ,
1008 x_msg_data => x_msg_data ,
1009 p_list_query_rec => l_list_query_rec ,
1010 x_object_version_number => l_object_version_number
1011 );
1012 IF (AMS_DEBUG_HIGH_ON) THEN
1013
1014 AMS_UTILITY_PVT.debug_message( '->' || x_return_status || '<-');
1015 END IF;
1016 close c_get_query_id ;
1017
1018 end if;
1019 if i = 1 then
1020 l_parent_list_query_id := l_list_query_rec.parent_list_query_id;
1021 end if;
1022
1023 l_no_of_records_upd := i;
1024 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1025 RAISE FND_API.G_EXC_ERROR;
1026 END IF;
1027 end loop;
1028 IF (AMS_DEBUG_HIGH_ON) THEN
1029
1030 AMS_UTILITY_PVT.debug_message( '->' || l_no_of_records_upd || '<-');
1031 END IF;
1032 IF (AMS_DEBUG_HIGH_ON) THEN
1033
1034 AMS_UTILITY_PVT.debug_message( '->' || l_parent_list_query_id || '<-');
1035 END IF;
1036 delete from ams_list_queries_all
1037 where parent_list_query_id = l_parent_list_query_id
1038 and sequence_order > l_no_of_records_upd ;
1039
1040
1041 -- End of API body.
1042 --
1043 -- Standard check for p_commit
1044 IF FND_API.to_Boolean( p_commit )
1045 THEN
1046 COMMIT WORK;
1047 END IF;
1048
1049
1050 -- Debug Message
1051 IF (AMS_DEBUG_HIGH_ON) THEN
1052
1053 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1054 END IF;
1055
1056 -- Standard call to get message count and if count is 1, get message info.
1057 FND_MSG_PUB.Count_And_Get
1058 (p_count => x_msg_count,
1059 p_data => x_msg_data
1060 );
1061 EXCEPTION
1062
1063 WHEN AMS_Utility_PVT.resource_locked THEN
1064 x_return_status := FND_API.g_ret_sts_error;
1065 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1066
1067 WHEN FND_API.G_EXC_ERROR THEN
1068 ROLLBACK TO UPDATE_List_Query_PVT_tbl;
1069 x_return_status := FND_API.G_RET_STS_ERROR;
1070 -- Standard call to get message count and if count=1, get the message
1071 FND_MSG_PUB.Count_And_Get (
1072 p_encoded => FND_API.G_FALSE,
1073 p_count => x_msg_count,
1074 p_data => x_msg_data
1075 );
1076
1077 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1078 ROLLBACK TO UPDATE_List_Query_PVT_tbl;
1079 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1080 -- Standard call to get message count and if count=1, get the message
1081 FND_MSG_PUB.Count_And_Get (
1082 p_encoded => FND_API.G_FALSE,
1083 p_count => x_msg_count,
1084 p_data => x_msg_data
1085 );
1086
1087 WHEN OTHERS THEN
1088 ROLLBACK TO UPDATE_List_Query_PVT_tbl;
1089 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1090 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1091 THEN
1092 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1093 END IF;
1094 -- Standard call to get message count and if count=1, get the message
1095 FND_MSG_PUB.Count_And_Get (
1096 p_encoded => FND_API.G_FALSE,
1097 p_count => x_msg_count,
1098 p_data => x_msg_data
1099 );
1100 END Update_List_Query;
1101
1102
1103
1104 PROCEDURE Delete_List_Query(
1105 p_api_version_number IN NUMBER,
1106 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1107 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1108 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1109 x_return_status OUT NOCOPY VARCHAR2,
1110 x_msg_count OUT NOCOPY NUMBER,
1111 x_msg_data OUT NOCOPY VARCHAR2,
1112 p_list_query_id IN NUMBER,
1113 p_object_version_number IN NUMBER
1114 )
1115
1116 IS
1117 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_List_Query';
1118 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1119 l_object_version_number NUMBER;
1120
1121 BEGIN
1122 -- Standard Start of API savepoint
1123 SAVEPOINT DELETE_List_Query_PVT;
1124
1125 -- Standard call to check for call compatibility.
1126 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1127 p_api_version_number,
1128 l_api_name,
1129 G_PKG_NAME)
1130 THEN
1131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1132 END IF;
1133
1134 -- Initialize message list if p_init_msg_list is set to TRUE.
1135 IF FND_API.to_Boolean( p_init_msg_list )
1136 THEN
1137 FND_MSG_PUB.initialize;
1138 END IF;
1139
1140 -- Debug Message
1141 IF (AMS_DEBUG_HIGH_ON) THEN
1142
1143 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1144 END IF;
1145
1146
1147 -- Initialize API return status to SUCCESS
1148 x_return_status := FND_API.G_RET_STS_SUCCESS;
1149
1150 --
1151 -- Api body
1152 --
1153 -- Debug Message
1154 IF (AMS_DEBUG_HIGH_ON) THEN
1155
1156 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
1157 END IF;
1158
1159 -- Invoke table handler(AMS_LIST_QUERIES_PKG.Delete_Row)
1160 AMS_LIST_QUERIES_PKG.Delete_Row(
1161 p_LIST_QUERY_ID => p_LIST_QUERY_ID);
1162 --
1163 -- End of API body
1164 --
1165
1166 -- Standard check for p_commit
1167 IF FND_API.to_Boolean( p_commit )
1168 THEN
1169 COMMIT WORK;
1170 END IF;
1171
1172
1173 -- Debug Message
1174 IF (AMS_DEBUG_HIGH_ON) THEN
1175
1176 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1177 END IF;
1178
1179 -- Standard call to get message count and if count is 1, get message info.
1180 FND_MSG_PUB.Count_And_Get
1181 (p_count => x_msg_count,
1182 p_data => x_msg_data
1183 );
1184 EXCEPTION
1185
1186 WHEN AMS_Utility_PVT.resource_locked THEN
1187 x_return_status := FND_API.g_ret_sts_error;
1188 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1189
1190 WHEN FND_API.G_EXC_ERROR THEN
1191 ROLLBACK TO DELETE_List_Query_PVT;
1192 x_return_status := FND_API.G_RET_STS_ERROR;
1193 -- Standard call to get message count and if count=1, get the message
1194 FND_MSG_PUB.Count_And_Get (
1195 p_encoded => FND_API.G_FALSE,
1196 p_count => x_msg_count,
1197 p_data => x_msg_data
1198 );
1199
1200 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1201 ROLLBACK TO DELETE_List_Query_PVT;
1202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1203 -- Standard call to get message count and if count=1, get the message
1204 FND_MSG_PUB.Count_And_Get (
1205 p_encoded => FND_API.G_FALSE,
1206 p_count => x_msg_count,
1207 p_data => x_msg_data
1208 );
1209
1210 WHEN OTHERS THEN
1211 ROLLBACK TO DELETE_List_Query_PVT;
1212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1213 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1214 THEN
1215 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1216 END IF;
1217 -- Standard call to get message count and if count=1, get the message
1218 FND_MSG_PUB.Count_And_Get (
1219 p_encoded => FND_API.G_FALSE,
1220 p_count => x_msg_count,
1221 p_data => x_msg_data
1222 );
1223 End Delete_List_Query;
1224
1225 PROCEDURE Delete_parent_List_Query(
1226 p_api_version_number IN NUMBER,
1227 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1228 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1229 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1230 x_return_status OUT NOCOPY VARCHAR2,
1231 x_msg_count OUT NOCOPY NUMBER,
1232 x_msg_data OUT NOCOPY VARCHAR2,
1233 p_parent_list_query_id IN NUMBER,
1234 p_object_version_number IN NUMBER
1235 ) is
1236 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_List_Query';
1237 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1238 l_object_version_number NUMBER;
1239
1240 cursor c_get_child_ids
1241 is select list_query_id
1242 from ams_list_queries_all
1243 where parent_list_query_id = p_parent_list_query_id ;
1244 l_list_query_id number;
1245
1246 BEGIN
1247 -- Standard Start of API savepoint
1248 SAVEPOINT DELETE_List_Query_PVT_tbl;
1249
1250 -- Standard call to check for call compatibility.
1251 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1252 p_api_version_number,
1253 l_api_name,
1254 G_PKG_NAME)
1255 THEN
1256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1257 END IF;
1258
1259 -- Initialize message list if p_init_msg_list is set to TRUE.
1260 IF FND_API.to_Boolean( p_init_msg_list )
1261 THEN
1262 FND_MSG_PUB.initialize;
1263 END IF;
1264
1265 -- Debug Message
1266 IF (AMS_DEBUG_HIGH_ON) THEN
1267
1268 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1269 END IF;
1270
1271
1272 -- Initialize API return status to SUCCESS
1273 x_return_status := FND_API.G_RET_STS_SUCCESS;
1274
1275 --
1276 -- Api body
1277 --
1278 -- Debug Message
1279 IF (AMS_DEBUG_HIGH_ON) THEN
1280
1281 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
1282 END IF;
1283
1284 open c_get_child_ids;
1285 loop
1286 fetch c_get_child_ids into l_list_query_id ;
1287 exit when c_get_child_ids%notfound;
1288 -- Invoke table handler(AMS_LIST_QUERIES_PKG.Delete_Row)
1289 AMS_LIST_QUERIES_PKG.Delete_Row(
1290 p_LIST_QUERY_ID => l_LIST_QUERY_ID);
1291 end loop;
1292 --
1293 -- End of API body
1294 --
1295
1296 -- Standard check for p_commit
1297 IF FND_API.to_Boolean( p_commit )
1298 THEN
1299 COMMIT WORK;
1300 END IF;
1301
1302
1303 -- Debug Message
1304 IF (AMS_DEBUG_HIGH_ON) THEN
1305
1306 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1307 END IF;
1308
1309 -- Standard call to get message count and if count is 1, get message info.
1310 FND_MSG_PUB.Count_And_Get
1311 (p_count => x_msg_count,
1312 p_data => x_msg_data
1313 );
1314 EXCEPTION
1315
1316 WHEN AMS_Utility_PVT.resource_locked THEN
1317 x_return_status := FND_API.g_ret_sts_error;
1318 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1319
1320 WHEN FND_API.G_EXC_ERROR THEN
1321 ROLLBACK TO DELETE_List_Query_PVT_tbl;
1322 x_return_status := FND_API.G_RET_STS_ERROR;
1323 -- Standard call to get message count and if count=1, get the message
1324 FND_MSG_PUB.Count_And_Get (
1325 p_encoded => FND_API.G_FALSE,
1326 p_count => x_msg_count,
1327 p_data => x_msg_data
1328 );
1329
1330 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1331 ROLLBACK TO DELETE_List_Query_PVT_tbl;
1332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1333 -- Standard call to get message count and if count=1, get the message
1334 FND_MSG_PUB.Count_And_Get (
1335 p_encoded => FND_API.G_FALSE,
1336 p_count => x_msg_count,
1337 p_data => x_msg_data
1338 );
1339
1340 WHEN OTHERS THEN
1341 ROLLBACK TO DELETE_List_Query_PVT_tbl;
1342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1343 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1344 THEN
1345 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1346 END IF;
1347 -- Standard call to get message count and if count=1, get the message
1348 FND_MSG_PUB.Count_And_Get (
1349 p_encoded => FND_API.G_FALSE,
1350 p_count => x_msg_count,
1351 p_data => x_msg_data
1352 );
1353 END Delete_parent_List_Query;
1354
1355
1356
1357
1358 -- Hint: Primary key needs to be returned.
1359 PROCEDURE Lock_List_Query(
1360 p_api_version_number IN NUMBER,
1361 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1362
1363 x_return_status OUT NOCOPY VARCHAR2,
1364 x_msg_count OUT NOCOPY NUMBER,
1365 x_msg_data OUT NOCOPY VARCHAR2,
1366
1367 p_list_query_id IN NUMBER,
1368 p_object_version IN NUMBER
1369 )
1370
1371 IS
1372 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_List_Query';
1373 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1374 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1375 l_LIST_QUERY_ID NUMBER;
1376
1377 CURSOR c_List_Query IS
1378 SELECT LIST_QUERY_ID
1379 FROM AMS_LIST_QUERIES_ALL
1380 WHERE LIST_QUERY_ID = p_LIST_QUERY_ID
1381 AND object_version_number = p_object_version
1382 FOR UPDATE NOWAIT;
1383
1384 BEGIN
1385
1386 -- Debug Message
1387 IF (AMS_DEBUG_HIGH_ON) THEN
1388
1389 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1390 END IF;
1391
1392 -- Initialize message list if p_init_msg_list is set to TRUE.
1393 IF FND_API.to_Boolean( p_init_msg_list )
1394 THEN
1395 FND_MSG_PUB.initialize;
1396 END IF;
1397
1398 -- Standard call to check for call compatibility.
1399 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1400 p_api_version_number,
1401 l_api_name,
1402 G_PKG_NAME)
1403 THEN
1404 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1405 END IF;
1406
1407
1408 -- Initialize API return status to SUCCESS
1409 x_return_status := FND_API.G_RET_STS_SUCCESS;
1410
1411
1412 ------------------------ lock -------------------------
1413
1414 IF (AMS_DEBUG_HIGH_ON) THEN
1415
1416
1417
1418 AMS_Utility_PVT.debug_message(l_full_name||': start');
1419
1420 END IF;
1421 OPEN c_List_Query;
1422
1423 FETCH c_List_Query INTO l_LIST_QUERY_ID;
1424
1425 IF (c_List_Query%NOTFOUND) THEN
1426 CLOSE c_List_Query;
1427 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1428 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1429 FND_MSG_PUB.add;
1430 END IF;
1431 RAISE FND_API.g_exc_error;
1432 END IF;
1433
1434 CLOSE c_List_Query;
1435
1436 -------------------- finish --------------------------
1437 FND_MSG_PUB.count_and_get(
1438 p_encoded => FND_API.g_false,
1439 p_count => x_msg_count,
1440 p_data => x_msg_data);
1441 IF (AMS_DEBUG_HIGH_ON) THEN
1442
1443 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1444 END IF;
1445 EXCEPTION
1446
1447 WHEN AMS_Utility_PVT.resource_locked THEN
1448 x_return_status := FND_API.g_ret_sts_error;
1449 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1450
1451 WHEN FND_API.G_EXC_ERROR THEN
1452 ROLLBACK TO LOCK_List_Query_PVT;
1453 x_return_status := FND_API.G_RET_STS_ERROR;
1454 -- Standard call to get message count and if count=1, get the message
1455 FND_MSG_PUB.Count_And_Get (
1456 p_encoded => FND_API.G_FALSE,
1457 p_count => x_msg_count,
1458 p_data => x_msg_data
1459 );
1460
1461 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1462 ROLLBACK TO LOCK_List_Query_PVT;
1463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1464 -- Standard call to get message count and if count=1, get the message
1465 FND_MSG_PUB.Count_And_Get (
1466 p_encoded => FND_API.G_FALSE,
1467 p_count => x_msg_count,
1468 p_data => x_msg_data
1469 );
1470
1471 WHEN OTHERS THEN
1472 ROLLBACK TO LOCK_List_Query_PVT;
1473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1474 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1475 THEN
1476 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1477 END IF;
1478 -- Standard call to get message count and if count=1, get the message
1479 FND_MSG_PUB.Count_And_Get (
1480 p_encoded => FND_API.G_FALSE,
1481 p_count => x_msg_count,
1482 p_data => x_msg_data
1483 );
1484 End Lock_List_Query;
1485
1486
1487 PROCEDURE check_list_query_uk_items(
1488 p_list_query_rec IN list_query_rec_type,
1489 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1490 x_return_status OUT NOCOPY VARCHAR2)
1491 IS
1492 l_valid_flag VARCHAR2(1);
1493
1494 --changed vbhandar 03/22/2005 to fix FTS performance issue.Also created index see case change 4115572 bugs
1495 cursor c_check_name
1496 is select FND_API.g_FALSE
1497 from ams_list_queries_vl
1498 where ( parent_list_query_id <> p_list_query_rec.parent_list_query_id
1499 or (parent_list_query_id is null
1500 and list_query_id <> p_list_query_rec.list_query_id ))
1501 and name = p_list_query_rec.name ;
1502 BEGIN
1503 x_return_status := FND_API.g_ret_sts_success;
1504
1505 -- check for uniqueness of id
1506 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1507 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1508 'AMS_LIST_QUERIES_ALL',
1509 'LIST_QUERY_ID = ''' || p_list_query_rec.LIST_QUERY_ID ||''''
1510 );
1511 END IF;
1512
1513 IF l_valid_flag = FND_API.g_false THEN
1514 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_LIST_QUERY_ID_DUPLICATE');
1515 x_return_status := FND_API.g_ret_sts_error;
1516 RETURN;
1517 END IF;
1518
1519 -- check for uniqueness of name
1520
1521 l_valid_flag := FND_API.g_true ;
1522 open c_check_name;
1523 fetch c_check_name into l_valid_flag;
1524 close c_check_name;
1525
1526
1527 IF l_valid_flag = FND_API.g_false THEN
1528 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1529
1530 IF (AMS_DEBUG_HIGH_ON) THEN
1531
1532
1533
1534 AMS_UTILITY_PVT.debug_message('parent id: ' || p_list_query_rec.parent_list_query_id );
1535
1536 END IF;
1537 FND_MESSAGE.set_name('AMS', 'AMS_LIST_QUERY_DUPE_NAME');
1538 FND_MSG_PUB.add;
1539 END IF;
1540 x_return_status := FND_API.g_ret_sts_error;
1541 RETURN;
1542 END IF;
1543
1544 END check_list_query_uk_items;
1545
1546 PROCEDURE check_list_query_req_items(
1547 p_list_query_rec IN list_query_rec_type,
1548 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1549 x_return_status OUT NOCOPY VARCHAR2
1550 )
1551 IS
1552 BEGIN
1553 x_return_status := FND_API.g_ret_sts_success;
1554
1555 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1556
1557 IF p_list_query_rec.name = FND_API.g_miss_char OR p_list_query_rec.name IS NULL THEN
1558 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_list_query_NO_name');
1559 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1560 FND_MESSAGE.Set_Name('AMS', 'AMS_LIST_QUERY_NO_NAME');
1561 FND_MSG_PUB.Add;
1562 END IF;
1563 x_return_status := FND_API.g_ret_sts_error;
1564 RETURN;
1565 END IF;
1566
1567
1568 /*
1569 IF p_list_query_rec.act_list_query_used_by_id = FND_API.g_miss_num OR p_list_query_rec.act_list_query_used_by_id IS NULL THEN
1570 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_list_query_NO_act_list_query_used_by_id');
1571 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1572 FND_MESSAGE.Set_Name('AMS', 'AMS_LIST_QUERY_NO_USEDBY_ID');
1573 FND_MSG_PUB.Add;
1574 END IF;
1575 x_return_status := FND_API.g_ret_sts_error;
1576 RETURN;
1577 END IF;
1578
1579
1580
1581 IF p_list_query_rec.arc_act_list_query_used_by = FND_API.g_miss_char OR p_list_query_rec.arc_act_list_query_used_by IS NULL THEN
1582 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_list_query_NO_arc_act_list_query_used_by');
1583 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1584 FND_MESSAGE.Set_Name('AMS', 'AMS_LIST_QUERY_NO_USEDBY');
1585 FND_MSG_PUB.Add;
1586 END IF;
1587 x_return_status := FND_API.g_ret_sts_error;
1588 RETURN;
1589 END IF;
1590
1591 */
1592 IF p_list_query_rec.sql_string = FND_API.g_miss_char OR p_list_query_rec.sql_string IS NULL THEN
1593 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1594 FND_MESSAGE.Set_Name('AMS', 'AMS_LIST_QUERY_NO_SQLSTRING');
1595 FND_MSG_PUB.Add;
1596 END IF;
1597 x_return_status := FND_API.g_ret_sts_error;
1598 RETURN;
1599 END IF;
1600
1601 /* ------------------------ DON"T THINK I NEED IT BEGIN
1602 ELSE
1603
1604
1605 IF p_list_query_rec.list_query_id IS NULL THEN
1606 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_list_query_NO_list_query_id');
1607 x_return_status := FND_API.g_ret_sts_error;
1608 RETURN;
1609 END IF;
1610
1611
1612 IF p_list_query_rec.name IS NULL THEN
1613 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_list_query_NO_name');
1614 x_return_status := FND_API.g_ret_sts_error;
1615 RETURN;
1616 END IF;
1617
1618
1619 IF p_list_query_rec.type IS NULL THEN
1620 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_list_query_NO_type');
1621 x_return_status := FND_API.g_ret_sts_error;
1622 RETURN;
1623 END IF;
1624
1625
1626 IF p_list_query_rec.enabled_flag IS NULL THEN
1627 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_list_query_NO_enabled_flag');
1628 x_return_status := FND_API.g_ret_sts_error;
1629 RETURN;
1630 END IF;
1631
1632
1633 IF p_list_query_rec.primary_key IS NULL THEN
1634 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_list_query_NO_primary_key');
1635 x_return_status := FND_API.g_ret_sts_error;
1636 RETURN;
1637 END IF;
1638
1639
1640 IF p_list_query_rec.public_flag IS NULL THEN
1641 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_list_query_NO_public_flag');
1642 x_return_status := FND_API.g_ret_sts_error;
1643 RETURN;
1644 END IF;
1645
1646 */------------------------ DON"T THINK I NEED IT END
1647
1648 END IF;
1649
1650 END check_list_query_req_items;
1651
1652 PROCEDURE check_list_query_FK_items(
1653 p_list_query_rec IN list_query_rec_type,
1654 x_return_status OUT NOCOPY VARCHAR2
1655 )
1656 IS
1657 l_table_name varchar2(100);
1658 l_pk_name varchar2(100);
1659
1660 BEGIN
1661 x_return_status := FND_API.g_ret_sts_success;
1662
1663 IF p_list_query_rec.arc_act_list_query_used_by <> FND_API.g_miss_char THEN
1664
1665 AMS_Utility_PVT.get_qual_table_name_and_pk(
1666 p_sys_qual => p_list_query_rec.arc_act_list_query_used_by,
1667 x_return_status => x_return_status,
1668 x_table_name => l_table_name,
1669 x_pk_name => l_pk_name
1670 );
1671
1672 IF x_return_status <> FND_API.g_ret_sts_success THEN
1673 RETURN;
1674 END IF;
1675
1676 IF p_list_query_rec.act_list_query_used_by_id <> FND_API.g_miss_num THEN
1677 IF ( AMS_Utility_PVT.Check_FK_Exists(l_table_name
1678 , l_pk_name
1679 , p_list_query_rec.act_list_query_used_by_id)
1680 = FND_API.G_TRUE)
1681 THEN
1682 x_return_status := FND_API.G_RET_STS_SUCCESS;
1683
1684 ELSE
1685 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1686 THEN
1687 FND_MESSAGE.set_name('AMS', 'AMS_LIST_QRY_USEDBYID_INVALID');
1688 FND_MSG_PUB.Add;
1689 END IF;
1690 x_return_status := FND_API.G_RET_STS_ERROR;
1691 RAISE FND_API.G_EXC_ERROR;
1692 END IF; -- end AMS_Utility_PVT.Check_FK_Exists
1693 END IF; -- end p_list_query_rec.act_list_query_used_by_id
1694 END IF; --end p_list_query_rec.arc_act_list_query_used_by <> FND_API.g_miss_char
1695
1696 END check_list_query_FK_items;
1697
1698 PROCEDURE check_list_query_Lookup_items(
1699 p_list_query_rec IN list_query_rec_type,
1700 x_return_status OUT NOCOPY VARCHAR2
1701 )
1702 IS
1703 BEGIN
1704 x_return_status := FND_API.g_ret_sts_success;
1705
1706 ----------------------- arc_act_list_query_used_by ------------------------
1707 IF p_list_query_rec.arc_act_list_query_used_by <> FND_API.g_miss_char THEN
1708 IF AMS_Utility_PVT.check_lookup_exists(
1709 p_lookup_type => 'AMS_LIST_QUERY_TYPE',
1710 p_lookup_code => p_list_query_rec.arc_act_list_query_used_by
1711 ) = FND_API.g_false
1712 THEN
1713 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1714 THEN
1715 FND_MESSAGE.set_name('AMS', 'AMS_LIST_QUERY_USEDBY_INVALID');
1716 FND_MSG_PUB.add;
1717 END IF;
1718
1719 x_return_status := FND_API.g_ret_sts_error;
1720 RETURN;
1721 END IF;
1722 END IF;
1723
1724 END check_list_query_Lookup_items;
1725
1726 PROCEDURE Check_list_query_Items (
1727 P_list_query_rec IN list_query_rec_type,
1728 p_validation_mode IN VARCHAR2,
1729 x_return_status OUT NOCOPY VARCHAR2
1730 )
1731 IS
1732 BEGIN
1733
1734 -- Check Items Uniqueness API calls
1735
1736 check_list_query_uk_items(
1737 p_list_query_rec => p_list_query_rec,
1738 p_validation_mode => p_validation_mode,
1739 x_return_status => x_return_status);
1740 IF x_return_status <> FND_API.g_ret_sts_success THEN
1741 RETURN;
1742 END IF;
1743
1744 -- Check Items Required/NOT NULL API calls
1745
1746 check_list_query_req_items(
1747 p_list_query_rec => p_list_query_rec,
1748 p_validation_mode => p_validation_mode,
1749 x_return_status => x_return_status);
1750 IF x_return_status <> FND_API.g_ret_sts_success THEN
1751 RETURN;
1752 END IF;
1753 -- Check Items Foreign Keys API calls
1754
1755 check_list_query_FK_items(
1756 p_list_query_rec => p_list_query_rec,
1757 x_return_status => x_return_status);
1758 IF x_return_status <> FND_API.g_ret_sts_success THEN
1759 RETURN;
1760 END IF;
1761 -- Check Items Lookups
1762
1763 check_list_query_Lookup_items(
1764 p_list_query_rec => p_list_query_rec,
1765 x_return_status => x_return_status);
1766 IF x_return_status <> FND_API.g_ret_sts_success THEN
1767 RETURN;
1768 END IF;
1769
1770 END Check_list_query_Items;
1771
1772
1773 PROCEDURE Complete_list_query_Rec (
1774 P_list_query_rec IN list_query_rec_type,
1775 x_complete_rec OUT NOCOPY list_query_rec_type
1776 )
1777 IS
1778 CURSOR c_query IS
1779 SELECT *
1780 FROM ams_list_queries_all
1781 WHERE list_query_id = p_list_query_rec.list_query_id;
1782
1783 l_query_rec c_query%ROWTYPE;
1784 BEGIN
1785
1786 x_complete_rec := p_list_query_rec;
1787
1788 OPEN c_query;
1789 FETCH c_query INTO l_query_rec;
1790 IF c_query%NOTFOUND THEN
1791 CLOSE c_query;
1792 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1793 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1794 FND_MSG_PUB.add;
1795 END IF;
1796 RAISE FND_API.g_exc_error;
1797 END IF;
1798 CLOSE c_query;
1799
1800
1801 IF p_list_query_rec.list_query_id = FND_API.g_miss_num THEN
1802 x_complete_rec.list_query_id := l_query_rec.list_query_id;
1803 END IF;
1804
1805 IF p_list_query_rec.last_update_date = FND_API.g_miss_date THEN
1806 x_complete_rec.last_update_date := l_query_rec.last_update_date;
1807 END IF;
1808
1809 IF p_list_query_rec.last_updated_by = FND_API.g_miss_num THEN
1810 x_complete_rec.last_updated_by := l_query_rec.last_updated_by;
1811 END IF;
1812
1813 IF p_list_query_rec.creation_date = FND_API.g_miss_date THEN
1814 x_complete_rec.creation_date := l_query_rec.creation_date;
1815 END IF;
1816
1817 IF p_list_query_rec.created_by = FND_API.g_miss_num THEN
1818 x_complete_rec.created_by := l_query_rec.created_by;
1819 END IF;
1820
1821 IF p_list_query_rec.last_update_login = FND_API.g_miss_num THEN
1822 x_complete_rec.last_update_login := l_query_rec.last_update_login;
1823 END IF;
1824
1825 IF p_list_query_rec.object_version_number = FND_API.g_miss_num THEN
1826 x_complete_rec.object_version_number := l_query_rec.object_version_number;
1827 END IF;
1828
1829 IF p_list_query_rec.name = FND_API.g_miss_char THEN
1830 x_complete_rec.name := l_query_rec.name;
1831 END IF;
1832
1833 IF p_list_query_rec.type = FND_API.g_miss_char THEN
1834 x_complete_rec.type := l_query_rec.type;
1835 END IF;
1836
1837
1838 IF p_list_query_rec.enabled_flag = FND_API.g_miss_char THEN
1839 x_complete_rec.enabled_flag := l_query_rec.enabled_flag;
1840 END IF;
1841
1842 IF p_list_query_rec.public_flag = FND_API.g_miss_char THEN
1843 x_complete_rec.public_flag := l_query_rec.public_flag ;
1844 END IF;
1845
1846 IF p_list_query_rec.org_id = FND_API.g_miss_num THEN
1847 x_complete_rec.org_id := l_query_rec.org_id;
1848 END IF;
1849
1850 IF p_list_query_rec.comments = FND_API.g_miss_char THEN
1851 x_complete_rec.comments := l_query_rec.comments;
1852 END IF;
1853
1854 IF p_list_query_rec.primary_key = FND_API.g_miss_char THEN
1855 x_complete_rec.primary_key := l_query_rec.primary_key;
1856 END IF;
1857
1858 IF p_list_query_rec.source_object_name = FND_API.g_miss_char THEN
1859 x_complete_rec.source_object_name := l_query_rec.source_object_name;
1860 END IF;
1861
1862
1863 IF p_list_query_rec.arc_act_list_query_used_by = FND_API.g_miss_char THEN
1864 x_complete_rec.arc_act_list_query_used_by := l_query_rec.arc_act_list_query_used_by;
1865 END IF;
1866
1867 IF p_list_query_rec.act_list_query_used_by_id = FND_API.g_miss_num THEN
1868 x_complete_rec.act_list_query_used_by_id := l_query_rec.act_list_query_used_by_id;
1869 END IF;
1870
1871 IF p_list_query_rec.sql_string = FND_API.g_miss_char THEN
1872 x_complete_rec.sql_string := l_query_rec.sql_string;
1873 END IF;
1874 IF p_list_query_rec.parent_list_query_id = FND_API.g_miss_num THEN
1875 x_complete_rec.parent_list_query_id := l_query_rec.parent_list_query_id;
1876 END IF;
1877 IF p_list_query_rec.sequence_order = FND_API.g_miss_num THEN
1878 x_complete_rec.sequence_order := l_query_rec.sequence_order;
1879 END IF;
1880
1881
1882
1883 END Complete_list_query_Rec;
1884
1885 PROCEDURE Validate_list_query(
1886 p_api_version_number IN NUMBER,
1887 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1888 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1889 p_list_query_rec IN list_query_rec_type,
1890 x_return_status OUT NOCOPY VARCHAR2,
1891 x_msg_count OUT NOCOPY NUMBER,
1892 x_msg_data OUT NOCOPY VARCHAR2
1893 )
1894 IS
1895 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_List_Query';
1896 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1897 l_object_version_number NUMBER;
1898 l_list_query_rec AMS_List_Query_PVT.list_query_rec_type;
1899
1900 BEGIN
1901 -- Standard Start of API savepoint
1902 SAVEPOINT VALIDATE_List_Query_;
1903
1904 -- Standard call to check for call compatibility.
1905 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1906 p_api_version_number,
1907 l_api_name,
1908 G_PKG_NAME)
1909 THEN
1910 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1911 END IF;
1912
1913 -- Initialize message list if p_init_msg_list is set to TRUE.
1914 IF FND_API.to_Boolean( p_init_msg_list )
1915 THEN
1916 FND_MSG_PUB.initialize;
1917 END IF;
1918 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1919 Check_list_query_Items(
1920 p_list_query_rec => p_list_query_rec,
1921 p_validation_mode => JTF_PLSQL_API.g_update,
1922 x_return_status => x_return_status
1923 );
1924
1925 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1926 RAISE FND_API.G_EXC_ERROR;
1927 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1928 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1929 END IF;
1930 END IF;
1931
1932
1933 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1934 Validate_list_query_Rec(
1935 p_api_version_number => 1.0,
1936 p_init_msg_list => FND_API.G_FALSE,
1937 x_return_status => x_return_status,
1938 x_msg_count => x_msg_count,
1939 x_msg_data => x_msg_data,
1940 p_list_query_rec => l_list_query_rec);
1941
1942 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1943 RAISE FND_API.G_EXC_ERROR;
1944 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1945 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1946 END IF;
1947 END IF;
1948
1949
1950 -- Debug Message
1951 IF (AMS_DEBUG_HIGH_ON) THEN
1952
1953 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1954 END IF;
1955
1956
1957 -- Initialize API return status to SUCCESS
1958 x_return_status := FND_API.G_RET_STS_SUCCESS;
1959
1960
1961 -- Debug Message
1962 IF (AMS_DEBUG_HIGH_ON) THEN
1963
1964 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1965 END IF;
1966
1967 -- Standard call to get message count and if count is 1, get message info.
1968 FND_MSG_PUB.Count_And_Get
1969 (p_count => x_msg_count,
1970 p_data => x_msg_data
1971 );
1972 EXCEPTION
1973
1974 WHEN AMS_Utility_PVT.resource_locked THEN
1975 x_return_status := FND_API.g_ret_sts_error;
1976 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1977
1978 WHEN FND_API.G_EXC_ERROR THEN
1979 ROLLBACK TO VALIDATE_List_Query_;
1980 x_return_status := FND_API.G_RET_STS_ERROR;
1981 -- Standard call to get message count and if count=1, get the message
1982 FND_MSG_PUB.Count_And_Get (
1983 p_encoded => FND_API.G_FALSE,
1984 p_count => x_msg_count,
1985 p_data => x_msg_data
1986 );
1987
1988 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1989 ROLLBACK TO VALIDATE_List_Query_;
1990 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1991 -- Standard call to get message count and if count=1, get the message
1992 FND_MSG_PUB.Count_And_Get (
1993 p_encoded => FND_API.G_FALSE,
1994 p_count => x_msg_count,
1995 p_data => x_msg_data
1996 );
1997
1998 WHEN OTHERS THEN
1999 ROLLBACK TO VALIDATE_List_Query_;
2000 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2001 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2002 THEN
2003 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2004 END IF;
2005 -- Standard call to get message count and if count=1, get the message
2006 FND_MSG_PUB.Count_And_Get (
2007 p_encoded => FND_API.G_FALSE,
2008 p_count => x_msg_count,
2009 p_data => x_msg_data
2010 );
2011 End Validate_List_Query;
2012
2013
2014 PROCEDURE Validate_list_query_rec(
2015 p_api_version_number IN NUMBER,
2016 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2017 x_return_status OUT NOCOPY VARCHAR2,
2018 x_msg_count OUT NOCOPY NUMBER,
2019 x_msg_data OUT NOCOPY VARCHAR2,
2020 p_list_query_rec IN list_query_rec_type
2021 )
2022 IS
2023 BEGIN
2024 -- Initialize message list if p_init_msg_list is set to TRUE.
2025 IF FND_API.to_Boolean( p_init_msg_list )
2026 THEN
2027 FND_MSG_PUB.initialize;
2028 END IF;
2029
2030 -- Initialize API return status to SUCCESS
2031 x_return_status := FND_API.G_RET_STS_SUCCESS;
2032
2033 -- Hint: Validate data
2034 -- If data not valid
2035 -- THEN
2036 -- x_return_status := FND_API.G_RET_STS_ERROR;
2037
2038 -- Debug Message
2039 IF (AMS_DEBUG_HIGH_ON) THEN
2040
2041 AMS_UTILITY_PVT.debug_message('Private API: Validate_query__rec->'
2042 || x_return_status);
2043 END IF;
2044 -- Standard call to get message count and if count is 1, get message info.
2045 FND_MSG_PUB.Count_And_Get
2046 (p_count => x_msg_count,
2047 p_data => x_msg_data
2048 );
2049 END Validate_list_query_Rec;
2050
2051 PROCEDURE Init_List_query_Rec(
2052 x_listquery_rec OUT NOCOPY list_query_rec_type_tbl
2053 )
2054 IS
2055 BEGIN
2056 x_listquery_rec.LIST_QUERY_ID := FND_API.g_miss_num ;
2057 x_listquery_rec.LAST_UPDATE_DATE := FND_API.g_miss_date ;
2058 x_listquery_rec.LAST_UPDATED_BY := FND_API.g_miss_num ;
2059 x_listquery_rec.CREATION_DATE := FND_API.g_miss_date ;
2060 x_listquery_rec.CREATED_BY := FND_API.g_miss_num ;
2061 x_listquery_rec.LAST_UPDATE_LOGIN := FND_API.g_miss_num ;
2062 x_listquery_rec.OBJECT_VERSION_NUMBER := FND_API.g_miss_num ;
2063 x_listquery_rec.NAME := FND_API.g_miss_char ;
2064 x_listquery_rec.TYPE := FND_API.g_miss_char ;
2065 x_listquery_rec.ENABLED_FLAG := FND_API.g_miss_char ;
2066 x_listquery_rec.PRIMARY_KEY := FND_API.g_miss_char ;
2067 x_listquery_rec.PUBLIC_FLAG := FND_API.g_miss_char ;
2068 x_listquery_rec.ORG_ID := FND_API.g_miss_num ;
2069 x_listquery_rec.COMMENTS := FND_API.g_miss_char ;
2070 x_listquery_rec.ACT_LIST_QUERY_USED_BY_ID := FND_API.g_miss_num ;
2071 x_listquery_rec.ARC_ACT_LIST_QUERY_USED_BY := FND_API.g_miss_char ;
2072 x_listquery_rec.SEED_FLAG := FND_API.g_miss_char ;
2073 x_listquery_rec.SOURCE_OBJECT_NAME := FND_API.g_miss_char ;
2074 x_listquery_rec.PARENT_LIST_QUERY_ID := FND_API.g_miss_num ;
2075 x_listquery_rec.SEQUENCE_ORDER := FND_API.g_miss_num ;
2076 END Init_List_Query_rec;
2077
2078
2079 PROCEDURE Init_List_query_Rec(
2080 x_listquery_rec OUT NOCOPY list_query_rec_type
2081 )
2082 IS
2083 BEGIN
2084 x_listquery_rec.LIST_QUERY_ID := FND_API.g_miss_num ;
2085 x_listquery_rec.LAST_UPDATE_DATE := FND_API.g_miss_date ;
2086 x_listquery_rec.LAST_UPDATED_BY := FND_API.g_miss_num ;
2087 x_listquery_rec.CREATION_DATE := FND_API.g_miss_date ;
2088 x_listquery_rec.CREATED_BY := FND_API.g_miss_num ;
2089 x_listquery_rec.LAST_UPDATE_LOGIN := FND_API.g_miss_num ;
2090 x_listquery_rec.OBJECT_VERSION_NUMBER := FND_API.g_miss_num ;
2091 x_listquery_rec.NAME := FND_API.g_miss_char ;
2092 x_listquery_rec.TYPE := FND_API.g_miss_char ;
2093 x_listquery_rec.ENABLED_FLAG := FND_API.g_miss_char ;
2094 x_listquery_rec.PRIMARY_KEY := FND_API.g_miss_char ;
2095 x_listquery_rec.PUBLIC_FLAG := FND_API.g_miss_char ;
2096 x_listquery_rec.ORG_ID := FND_API.g_miss_num ;
2097 x_listquery_rec.COMMENTS := FND_API.g_miss_char ;
2098 x_listquery_rec.ACT_LIST_QUERY_USED_BY_ID := FND_API.g_miss_num ;
2099 x_listquery_rec.ARC_ACT_LIST_QUERY_USED_BY := FND_API.g_miss_char ;
2100 x_listquery_rec.SEED_FLAG := FND_API.g_miss_char ;
2101 x_listquery_rec.SQL_STRING := FND_API.g_miss_char ;
2102 x_listquery_rec.SOURCE_OBJECT_NAME := FND_API.g_miss_char ;
2103 x_listquery_rec.PARENT_LIST_QUERY_ID := FND_API.g_miss_num ;
2104 x_listquery_rec.SEQUENCE_ORDER := FND_API.g_miss_num ;
2105 END Init_List_Query_rec;
2106
2107 PROCEDURE Complete_List_Query_Rec_tbl(
2108 p_listquery_rec IN list_query_rec_type_tbl ,
2109 x_complete_rec OUT NOCOPY list_query_rec_type_tbl
2110 )
2111 IS
2112
2113 CURSOR c_listquery IS
2114 SELECT *
2115 FROM ams_list_queries_all
2116 WHERE list_query_id = p_listquery_rec.parent_list_query_id
2117 and sequence_order = 1;
2118
2119 l_listquery_rec c_listquery%ROWTYPE;
2120
2121 BEGIN
2122
2123 x_complete_rec := p_listquery_rec;
2124 OPEN c_listquery;
2125 FETCH c_listquery INTO l_listquery_rec;
2126 IF c_listquery%NOTFOUND THEN
2127 CLOSE c_listquery;
2128 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2129 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2130 FND_MSG_PUB.add;
2131 END IF;
2132 RAISE FND_API.g_exc_error;
2133 END IF;
2134 CLOSE c_listquery;
2135
2136 IF p_listquery_rec.LIST_QUERY_ID = FND_API.g_miss_num THEN
2137 x_complete_rec.LIST_QUERY_ID := l_listquery_rec.LIST_QUERY_ID;
2138 END IF;
2139
2140 IF p_listquery_rec.LAST_UPDATE_DATE = FND_API.g_miss_date THEN
2141 x_complete_rec.LAST_UPDATE_DATE := l_listquery_rec.LAST_UPDATE_DATE;
2142 END IF;
2143
2144 IF p_listquery_rec.LAST_UPDATED_BY = FND_API.g_miss_num THEN
2145 x_complete_rec.LAST_UPDATED_BY := l_listquery_rec.LAST_UPDATED_BY;
2146 END IF;
2147
2148
2149 IF p_listquery_rec.CREATION_DATE = FND_API.g_miss_date THEN
2150 x_complete_rec.CREATION_DATE := l_listquery_rec.CREATION_DATE;
2151 END IF;
2152
2153 IF p_listquery_rec.CREATED_BY = FND_API.g_miss_num THEN
2154 x_complete_rec.CREATED_BY := l_listquery_rec.CREATED_BY;
2155 END IF;
2156
2157 IF p_listquery_rec.LAST_UPDATE_LOGIN = FND_API.g_miss_num THEN
2158 x_complete_rec.LAST_UPDATE_LOGIN := l_listquery_rec.LAST_UPDATE_LOGIN;
2159 END IF;
2160
2161 IF p_listquery_rec.OBJECT_VERSION_NUMBER = FND_API.g_miss_num THEN
2162 x_complete_rec.OBJECT_VERSION_NUMBER := l_listquery_rec.OBJECT_VERSION_NUMBER;
2163 END IF;
2164
2165 IF p_listquery_rec.NAME = FND_API.g_miss_char THEN
2166 x_complete_rec.NAME := l_listquery_rec.NAME;
2167 END IF;
2168
2169 IF p_listquery_rec.TYPE = FND_API.g_miss_char THEN
2170 x_complete_rec.TYPE := l_listquery_rec.TYPE;
2171 END IF;
2172
2173 IF p_listquery_rec.ENABLED_FLAG = FND_API.g_miss_char THEN
2174 x_complete_rec.ENABLED_FLAG := l_listquery_rec.ENABLED_FLAG;
2175 END IF;
2176
2177 IF p_listquery_rec.PRIMARY_KEY = FND_API.g_miss_char THEN
2178 x_complete_rec.PRIMARY_KEY := l_listquery_rec.PRIMARY_KEY;
2179 END IF;
2180
2181 IF p_listquery_rec.PUBLIC_FLAG = FND_API.g_miss_char THEN
2182 x_complete_rec.PUBLIC_FLAG := l_listquery_rec.PUBLIC_FLAG;
2183 END IF;
2184
2185 IF p_listquery_rec.ORG_ID = FND_API.g_miss_num THEN
2186 x_complete_rec.ORG_ID := l_listquery_rec.ORG_ID;
2187 END IF;
2188
2189 IF p_listquery_rec.COMMENTS = FND_API.g_miss_char THEN
2190 x_complete_rec.COMMENTS := l_listquery_rec.COMMENTS;
2191 END IF;
2192
2193
2194 IF p_listquery_rec.ACT_LIST_QUERY_USED_BY_ID = FND_API.g_miss_num THEN
2195 x_complete_rec.ACT_LIST_QUERY_USED_BY_ID := l_listquery_rec.ACT_LIST_QUERY_USED_BY_ID;
2196 END IF;
2197
2198 IF p_listquery_rec.ARC_ACT_LIST_QUERY_USED_BY = FND_API.g_miss_char THEN
2199 x_complete_rec.ARC_ACT_LIST_QUERY_USED_BY := l_listquery_rec.ARC_ACT_LIST_QUERY_USED_BY;
2200 END IF;
2201
2202 IF p_listquery_rec.SEED_FLAG = FND_API.g_miss_char THEN
2203 x_complete_rec.SEED_FLAG := l_listquery_rec.SEED_FLAG;
2204 END IF;
2205
2206
2207 IF p_listquery_rec.SOURCE_OBJECT_NAME = FND_API.g_miss_char THEN
2208 x_complete_rec.SOURCE_OBJECT_NAME := l_listquery_rec.SOURCE_OBJECT_NAME;
2209 END IF;
2210
2211 IF p_listquery_rec.PARENT_LIST_QUERY_ID = FND_API.g_miss_num THEN
2212 x_complete_rec.PARENT_LIST_QUERY_ID := l_listquery_rec.PARENT_LIST_QUERY_ID;
2213 END IF;
2214
2215 IF p_listquery_rec.SEQUENCE_ORDER = FND_API.g_miss_num THEN
2216 x_complete_rec.SEQUENCE_ORDER := l_listquery_rec.SEQUENCE_ORDER;
2217 END IF;
2218
2219
2220 END Complete_List_Query_rec_tbl;
2221
2222 PROCEDURE Complete_List_Query_Rec(
2223 p_listquery_rec IN list_query_rec_type ,
2224 x_complete_rec OUT NOCOPY list_query_rec_type
2225 )
2226 IS
2227
2228 CURSOR c_listquery IS
2229 SELECT *
2230 FROM ams_list_queries_all
2231 WHERE list_query_id = p_listquery_rec.list_query_id;
2232
2233 l_listquery_rec c_listquery%ROWTYPE;
2234
2235 BEGIN
2236
2237 x_complete_rec := p_listquery_rec;
2238 OPEN c_listquery;
2239 FETCH c_listquery INTO l_listquery_rec;
2240 IF c_listquery%NOTFOUND THEN
2241 CLOSE c_listquery;
2242 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2243 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2244 FND_MSG_PUB.add;
2245 END IF;
2246 RAISE FND_API.g_exc_error;
2247 END IF;
2248 CLOSE c_listquery;
2249
2250 IF p_listquery_rec.LIST_QUERY_ID = FND_API.g_miss_num THEN
2251 x_complete_rec.LIST_QUERY_ID := l_listquery_rec.LIST_QUERY_ID;
2252 END IF;
2253
2254 IF p_listquery_rec.LAST_UPDATE_DATE = FND_API.g_miss_date THEN
2255 x_complete_rec.LAST_UPDATE_DATE := l_listquery_rec.LAST_UPDATE_DATE;
2256 END IF;
2257
2258 IF p_listquery_rec.LAST_UPDATED_BY = FND_API.g_miss_num THEN
2259 x_complete_rec.LAST_UPDATED_BY := l_listquery_rec.LAST_UPDATED_BY;
2260 END IF;
2261
2262
2263 IF p_listquery_rec.CREATION_DATE = FND_API.g_miss_date THEN
2264 x_complete_rec.CREATION_DATE := l_listquery_rec.CREATION_DATE;
2265 END IF;
2266
2267 IF p_listquery_rec.CREATED_BY = FND_API.g_miss_num THEN
2268 x_complete_rec.CREATED_BY := l_listquery_rec.CREATED_BY;
2269 END IF;
2270
2271 IF p_listquery_rec.LAST_UPDATE_LOGIN = FND_API.g_miss_num THEN
2272 x_complete_rec.LAST_UPDATE_LOGIN := l_listquery_rec.LAST_UPDATE_LOGIN;
2273 END IF;
2274
2275 IF p_listquery_rec.OBJECT_VERSION_NUMBER = FND_API.g_miss_num THEN
2276 x_complete_rec.OBJECT_VERSION_NUMBER := l_listquery_rec.OBJECT_VERSION_NUMBER;
2277 END IF;
2278
2279 IF p_listquery_rec.NAME = FND_API.g_miss_char THEN
2280 x_complete_rec.NAME := l_listquery_rec.NAME;
2281 END IF;
2282
2283 IF p_listquery_rec.TYPE = FND_API.g_miss_char THEN
2284 x_complete_rec.TYPE := l_listquery_rec.TYPE;
2285 END IF;
2286
2287 IF p_listquery_rec.ENABLED_FLAG = FND_API.g_miss_char THEN
2288 x_complete_rec.ENABLED_FLAG := l_listquery_rec.ENABLED_FLAG;
2289 END IF;
2290
2291 IF p_listquery_rec.PRIMARY_KEY = FND_API.g_miss_char THEN
2292 x_complete_rec.PRIMARY_KEY := l_listquery_rec.PRIMARY_KEY;
2293 END IF;
2294
2295 IF p_listquery_rec.PUBLIC_FLAG = FND_API.g_miss_char THEN
2296 x_complete_rec.PUBLIC_FLAG := l_listquery_rec.PUBLIC_FLAG;
2297 END IF;
2298
2299 IF p_listquery_rec.ORG_ID = FND_API.g_miss_num THEN
2300 x_complete_rec.ORG_ID := l_listquery_rec.ORG_ID;
2301 END IF;
2302
2303 IF p_listquery_rec.COMMENTS = FND_API.g_miss_char THEN
2304 x_complete_rec.COMMENTS := l_listquery_rec.COMMENTS;
2305 END IF;
2306
2307
2308 IF p_listquery_rec.ACT_LIST_QUERY_USED_BY_ID = FND_API.g_miss_num THEN
2309 x_complete_rec.ACT_LIST_QUERY_USED_BY_ID := l_listquery_rec.ACT_LIST_QUERY_USED_BY_ID;
2310 END IF;
2311
2312 IF p_listquery_rec.ARC_ACT_LIST_QUERY_USED_BY = FND_API.g_miss_char THEN
2313 x_complete_rec.ARC_ACT_LIST_QUERY_USED_BY := l_listquery_rec.ARC_ACT_LIST_QUERY_USED_BY;
2314 END IF;
2315
2316 IF p_listquery_rec.SEED_FLAG = FND_API.g_miss_char THEN
2317 x_complete_rec.SEED_FLAG := l_listquery_rec.SEED_FLAG;
2318 END IF;
2319
2320 IF p_listquery_rec.SQL_STRING = FND_API.g_miss_char THEN
2321 x_complete_rec.SQL_STRING := l_listquery_rec.SQL_STRING;
2322 END IF;
2323
2324 IF p_listquery_rec.SOURCE_OBJECT_NAME = FND_API.g_miss_char THEN
2325 x_complete_rec.SOURCE_OBJECT_NAME := l_listquery_rec.SOURCE_OBJECT_NAME;
2326 END IF;
2327
2328 IF p_listquery_rec.PARENT_LIST_QUERY_ID = FND_API.g_miss_num THEN
2329 x_complete_rec.PARENT_LIST_QUERY_ID := l_listquery_rec.PARENT_LIST_QUERY_ID;
2330 END IF;
2331
2332 IF p_listquery_rec.SEQUENCE_ORDER = FND_API.g_miss_num THEN
2333 x_complete_rec.SEQUENCE_ORDER := l_listquery_rec.SEQUENCE_ORDER;
2334 END IF;
2335
2336
2337 END Complete_List_Query_rec;
2338
2339 PROCEDURE Copy_List_Queries
2340 ( p_api_version IN NUMBER,
2341 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2342 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2343 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2344 p_source_listheader_id IN NUMBER,
2345 p_new_listheader_id IN NUMBER,
2346 p_new_listheader_name IN VARCHAR2,
2347 x_return_status OUT NOCOPY VARCHAR2,
2348 x_msg_count OUT NOCOPY NUMBER,
2349 x_msg_data OUT NOCOPY VARCHAR2
2350 )IS
2351
2352 l_api_name CONSTANT VARCHAR2(30) := 'Copy_List_Queries';
2353 l_api_version CONSTANT NUMBER := 1.0;
2354 j NUMBER:=0;
2355
2356 -- Status Local Variables
2357 l_return_status VARCHAR2(1); -- Return value from procedures
2358
2359 --l_listheader_id number;
2360
2361 x_rowid VARCHAR2(30);
2362
2363 l_sqlerrm varchar2(600);
2364 l_sqlcode varchar2(100);
2365
2366 l_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
2367
2368
2369 CURSOR fetch_list_queries (listqueryId NUMBER) IS
2370 SELECT *
2371 FROM ams_list_queries_all
2372 WHERE parent_list_query_id =listqueryId
2373 ORDER BY sequence_order;
2374
2375 CURSOR fetch_list_select_actions(list_id NUMBER) IS
2376 SELECT incl_object_id,rank,order_number,description,list_action_type
2377 ,no_of_rows_requested,no_of_rows_available,no_of_rows_used
2378 ,distribution_pct,no_of_rows_targeted
2379 FROM ams_list_select_actions
2380 WHERE action_used_by_id =list_id
2381 AND arc_action_used_by='LIST'
2382 AND arc_incl_object_from='SQL';
2383
2384 l_list_queries_rec fetch_list_queries%ROWTYPE;
2385 l_action_rec AMS_ListAction_PVT.action_rec_type;
2386
2387 l_list_query_rec_type_tbl list_query_rec_type_tbl;
2388 l_sql_string_tbl sql_string_tbl;
2389 l_parent_list_query_id NUMBER;
2390 l_action_id NUMBER;
2391 BEGIN
2392
2393 -- Standard Start of API savepoint
2394 SAVEPOINT Copy_List_Queries_PVT;
2395
2396 x_return_status := FND_API.G_RET_STS_SUCCESS;
2397 -- Standard call to check for call compatibility.
2398 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2399 p_api_version,
2400 l_api_name,
2401 G_PKG_NAME) THEN
2402 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2403 END IF;
2404
2405
2406 -- Initialize message list IF p_init_msg_list is set to TRUE.
2407 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2408 FND_MSG_PUB.initialize;
2409 END IF;
2410
2411 -- Debug Message
2412 IF (AMS_DEBUG_HIGH_ON) THEN
2413 FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
2414 FND_MESSAGE.Set_Token('TEXT', 'AMS_List_Query_PVT.Copy_List_Queries: Start', TRUE);
2415 FND_MSG_PUB.Add;
2416 END IF;
2417
2418 -- Initialize API return status to success
2419 x_return_status := FND_API.G_RET_STS_SUCCESS;
2420
2421
2422 FOR l_list_actions_rec IN fetch_list_select_actions(p_source_listheader_id)
2423 LOOP
2424
2425 j:=0;
2426 l_sql_string_tbl.DELETE;
2427 Init_List_query_Rec(l_list_query_rec_type_tbl);
2428
2429 open fetch_list_queries(l_list_actions_rec.incl_object_id);
2430 loop
2431 fetch fetch_list_queries into l_list_queries_rec;
2432 exit when fetch_list_queries%notfound;
2433
2434 IF l_list_queries_rec.list_query_id IS NOT NULL THEN
2435 -- l_list_query_rec_type_tbl.NAME := p_new_listheader_name || l_list_queries_rec.NAME;
2436 l_list_query_rec_type_tbl.NAME :=substr(rpad(substr(l_list_queries_rec.NAME,1,150),150,' ')||p_new_listheader_id||'_'||p_new_listheader_name,1,240);
2437 l_list_query_rec_type_tbl.TYPE := l_list_queries_rec.TYPE ;
2438 l_list_query_rec_type_tbl.ENABLED_FLAG := l_list_queries_rec.ENABLED_FLAG ;
2439 l_list_query_rec_type_tbl.PRIMARY_KEY := l_list_queries_rec.PRIMARY_KEY ;
2440 l_list_query_rec_type_tbl.PUBLIC_FLAG := l_list_queries_rec.PUBLIC_FLAG ;
2441 l_list_query_rec_type_tbl.SOURCE_OBJECT_NAME := l_list_queries_rec.SOURCE_OBJECT_NAME;
2442 j := j+1;
2443 l_sql_string_tbl(j) := l_list_queries_rec.sql_string;
2444 END IF;
2445 end loop;
2446 close fetch_list_queries;
2447
2448 IF l_list_queries_rec.list_query_id IS NOT NULL THEN
2449
2450 Create_List_Query(
2451 l_api_version ,
2452 l_init_msg_list,
2453 p_commit,
2454 p_validation_level ,
2455 x_return_status,
2456 x_msg_count,
2457 x_msg_data ,
2458 l_list_query_rec_type_tbl ,
2459 l_sql_string_tbl ,
2460 l_parent_list_query_id
2461 ) ;
2462
2463 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2464 RAISE FND_API.G_EXC_ERROR;
2465 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2466 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2467 END IF;
2468
2469 AMS_ListAction_PVT.init_action_rec(l_action_rec);
2470 l_action_rec.list_select_action_id := NULL;
2471 l_action_rec.order_number := l_list_actions_rec.order_number;
2472 l_action_rec.list_action_type := l_list_actions_rec.list_action_type;
2473 l_action_rec.arc_incl_object_from := 'SQL';
2474 l_action_rec.incl_object_id := l_parent_list_query_id;
2475 l_action_rec.rank := l_list_actions_rec.rank;
2476 l_action_rec.no_of_rows_available := l_list_actions_rec.no_of_rows_available;
2477 l_action_rec.no_of_rows_requested := l_list_actions_rec.no_of_rows_requested;
2478 l_action_rec.no_of_rows_used := l_list_actions_rec.no_of_rows_used;
2479 l_action_rec.distribution_pct := l_list_actions_rec.distribution_pct;
2480 l_action_rec.description := l_list_actions_rec.description;
2481 l_action_rec.arc_action_used_by := 'LIST';
2482 l_action_rec.action_used_by_id := p_new_listheader_id;
2483 l_action_rec.no_of_rows_targeted := l_list_actions_rec.no_of_rows_targeted;
2484
2485
2486
2487 AMS_ListAction_PVT.Create_ListAction
2488 (l_api_version,
2489 l_init_msg_list,
2490 p_commit ,
2491 p_validation_level,
2492 x_return_status ,
2493 x_msg_count,
2494 x_msg_data,
2495 l_action_rec,
2496 l_action_id
2497 ) ;
2498
2499 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2500 RAISE FND_API.G_EXC_ERROR;
2501 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2502 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2503 END IF;
2504
2505
2506 END IF; --l_list_queries_rec.list_query_id IS NOT NULL
2507
2508
2509 END LOOP;
2510
2511 -- Standard check of p_commit.
2512 IF FND_API.To_Boolean ( p_commit ) THEN
2513 COMMIT WORK;
2514 END IF;
2515
2516 -- Success Message
2517 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
2518 THEN
2519 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
2520 FND_MESSAGE.Set_Token('ROW', 'AMS_List_Query_PVT.Copy_List_Queries', TRUE);
2521 FND_MSG_PUB.Add;
2522 END IF;
2523
2524 IF (AMS_DEBUG_HIGH_ON) THEN
2525 FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
2526 FND_MESSAGE.Set_Token('TEXT', 'AMS_List_Query_PVT.Copy_List_Queries: END', TRUE);
2527 FND_MSG_PUB.Add;
2528 END IF;
2529
2530
2531 -- Standard call to get message count AND IF count is 1, get message info.
2532 FND_MSG_PUB.Count_AND_Get
2533 ( p_count => x_msg_count,
2534 p_data => x_msg_data,
2535 p_encoded => FND_API.G_FALSE
2536 );
2537
2538 EXCEPTION
2539 WHEN FND_API.G_EXC_ERROR THEN
2540 ROLLBACK TO Copy_List_Queries_PVT;
2541 x_return_status := FND_API.G_RET_STS_ERROR ;
2542
2543 FND_MSG_PUB.Count_AND_Get
2544 ( p_count => x_msg_count,
2545 p_data => x_msg_data,
2546 p_encoded => FND_API.G_FALSE
2547 );
2548
2549
2550 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2551 ROLLBACK TO Copy_List_Queries_PVT;
2552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2553 FND_MSG_PUB.Count_AND_Get
2554 ( p_count => x_msg_count,
2555 p_data => x_msg_data,
2556 p_encoded => FND_API.G_FALSE
2557 );
2558
2559 WHEN OTHERS THEN
2560 ROLLBACK TO Copy_List_Queries_PVT;
2561 FND_MESSAGE.set_name('AMS','SQL ERROR ->' || sqlerrm );
2562 FND_MSG_PUB.Add;
2563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2564 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2565 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2566 END IF;
2567
2568 FND_MSG_PUB.Count_AND_Get
2569 ( p_count => x_msg_count,
2570 p_data => x_msg_data,
2571 p_encoded => FND_API.G_FALSE
2572 );
2573
2574 END Copy_List_Queries;
2575
2576
2577 END AMS_List_Query_PVT;