[Home] [Help]
PACKAGE BODY: APPS.JTF_PERZ_QUERY_PUB
Source
1 PACKAGE BODY Jtf_Perz_Query_Pub AS
2 /* $Header: jtfzppqb.pls 120.2 2005/11/02 04:38:14 skothe ship $ */
3
4 -- NAME
5 -- Jtf_PERZ_QUERY_PUB
6
7 -- PURPOSE
8 -- Public API for saving, retrieving and updating personalized queries.
9 --
10 -- NOTES
11 -- This is a pulicly accessible pacakge. It should be used by all
12 -- sources for saving, retrieving and updating personalized queries
13 -- within the personalization framework.
14 -- HISTORY
15 -- 04/18/2000 SMATTEGU Created
16 -- 06/26/2000 CCHANDRA modified
17
18 -- *****************************************************************************
19 G_PKG_NAME CONSTANT VARCHAR2(30):='Jtf_Perz_Query_Pub';
20 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfzppqb.pls';
21 -- *****************************************************************************
22
23
24 PROCEDURE Save_Perz_Query
25 ( p_api_version_number IN NUMBER,
26 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
27 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
28
29 p_application_id IN NUMBER,
30 p_profile_id IN NUMBER,
31 p_profile_name IN VARCHAR2,
32 p_profile_type IN VARCHAR2,
33 p_Profile_Attrib IN Jtf_Perz_Profile_Pub.PROFILE_ATTRIB_TBL_TYPE
34 := Jtf_Perz_Profile_Pub.G_MISS_PROFILE_ATTRIB_TBL,
35
36 p_query_id IN NUMBER,
37 p_query_name IN VARCHAR2,
38 p_query_type IN VARCHAR2,
39 p_query_desc IN VARCHAR2,
40 p_query_data_source IN VARCHAR2,
41
42 p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
43 := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,
44 p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE
45 := Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,
46 p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE
47 := Jtf_Perz_Query_Pub.G_MISS_QUERY_RAW_SQL_REC,
48
49 x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
50 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
51 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
52 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
53
54 )IS
55 l_api_version_number NUMBER := p_api_version_number;
56 l_api_name CONSTANT VARCHAR2(30) := 'Save Query';
57
58 BEGIN
59
60 -- ******* Standard Begins ********
61
62 -- Standard Start of API savepoint
63 SAVEPOINT SAVE_JTF_PERZ_QUERY_PUB;
64
65 -- Standard call to check for call compatibility.
66 -- IF NOT FND_API.Compatible_API_Call (
67 -- l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME)
68 -- THEN
69 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70 -- END IF;
71
72 -- Initialize message list if p_init_msg_list is set to TRUE.
73 IF Fnd_Api.to_Boolean( p_init_msg_list )
74 THEN
75 Fnd_Msg_Pub.initialize;
76 END IF;
77
78 -- Initialize API return status to success
79 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
80
81 Jtf_Perz_Query_Pvt.Save_Perz_Query
82
83 ( p_api_version_number,
84 p_init_msg_list,
85 p_commit,
86 p_application_id,
87 p_profile_id,
88 p_profile_name,
89 p_profile_type,
90 p_Profile_Attrib,
91 p_query_id,
92 p_query_name,
93 p_query_type,
94 p_query_desc,
95 p_query_data_source,
96 p_query_param_tbl,
97 p_query_order_by_tbl,
98 p_query_raw_sql_rec,
99 x_query_id,
100 x_return_status,
101 x_msg_count,
102 x_msg_data
103 );
104 -- ******** Standard Ends ***********
105 --
106 -- End of API body.
107 --
108 -- 7. Commit the whole thing
109
110 -- Standard check of p_commit.
111 IF Fnd_Api.To_Boolean ( p_commit )
112 THEN
113 COMMIT WORK;
114 END IF;
115
116 -- Debug Message
117 -- Standard call to get message count and if count is 1, get message info.
118 Fnd_Msg_Pub.Count_And_Get
119 (
120 p_count => x_msg_count,
121 p_data => x_msg_data
122 );
123
124
125 EXCEPTION
126
127 WHEN Fnd_Api.G_EXC_ERROR THEN
128
129 ROLLBACK TO SAVE_JTF_PERZ_QUERY_PUB;
130 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
131
132 Fnd_Msg_Pub.Count_And_Get
133 ( p_count => x_msg_count,
134 p_data => x_msg_data
135 );
136
137 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
138 THEN
139 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
140 END IF;
141
142 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
143
144 ROLLBACK TO SAVE_JTF_PERZ_QUERY_PUB;
145 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
146
147 Fnd_Msg_Pub.Count_And_Get
148 ( p_count => x_msg_count,
149 p_data => x_msg_data
150 );
151
152 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
153 THEN
154 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
155 END IF;
156
157 WHEN OTHERS THEN
158
159 ROLLBACK TO SAVE_JTF_PERZ_QUERY_PUB;
160 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
161
162 Fnd_Msg_Pub.Count_And_Get
163 ( p_count => x_msg_count,
164 p_data => x_msg_data
165 );
166
167 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
168 THEN
169 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
170 END IF;
171 END Save_Perz_Query;
172 -- ****************************************************************************
173 PROCEDURE Create_Perz_Query
174 ( p_api_version_number IN NUMBER,
175 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
176 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
177
178 p_application_id IN NUMBER,
179 p_profile_id IN NUMBER,
180 p_profile_name IN VARCHAR2,
181
182 p_query_id IN NUMBER,
183 p_query_name IN VARCHAR2,
184 p_query_type IN VARCHAR2,
185 p_query_desc IN VARCHAR2,
186 p_query_data_source IN VARCHAR2,
187
188 p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
189 := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,
190 p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE
191 := Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,
192 p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE
193 := Jtf_Perz_Query_Pub.G_MISS_QUERY_RAW_SQL_REC,
194
195 x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
196 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
197 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
198 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
199 )IS
200
201 -- ******* Local Variables ********
202
203 l_api_version_number NUMBER := p_api_version_number;
204 l_api_name CONSTANT VARCHAR2(30) := 'Create Perz Query';
205
206 BEGIN
207
208 -- ******* Standard Begins ********
209
210 -- Standard Start of API savepoint
211 SAVEPOINT CREATE_JTF_PERZ_QUERY_PUB;
212
213 -- Standard call to check for call compatibility.
214 -- IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME)
215 -- THEN
216 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
217 -- END IF;
218
219 -- Initialize message list if p_init_msg_list is set to TRUE.
220 IF Fnd_Api.to_Boolean( p_init_msg_list )
221 THEN
222 Fnd_Msg_Pub.initialize;
223 END IF;
224
225 -- Initialize API return status to success
226 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
227
228 Jtf_Perz_Query_Pvt.Create_Perz_Query
229 ( p_api_version_number,
230 p_init_msg_list,
231 p_commit,
232 p_application_id,
233 p_profile_id,
234 p_profile_name,
235 p_query_id,
236 p_query_name,
237 p_query_type,
238 p_query_desc,
239 p_query_data_source,
240 p_query_param_tbl,
241 p_query_order_by_tbl,
242 p_query_raw_sql_rec,
243 x_query_id,
244 x_return_status,
245 x_msg_count,
246 x_msg_data
247 );
248
249 -- ******** Standard Ends ***********
250 --
251 -- End of API body.
252 --
253 -- 7. Commit the whole thing
254
255 -- Standard check of p_commit.
256 IF Fnd_Api.To_Boolean ( p_commit )
257 THEN
258 COMMIT WORK;
259 END IF;
260
261 -- Debug Message
262 -- Standard call to get message count and if count is 1, get message info.
263 Fnd_Msg_Pub.Count_And_Get
264 (
265 p_count => x_msg_count,
266 p_data => x_msg_data
267 );
268
269
270 EXCEPTION
271
272 WHEN Fnd_Api.G_EXC_ERROR THEN
273
274 ROLLBACK TO CREATE_JTF_PERZ_QUERY_PUB;
275 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
276
277 Fnd_Msg_Pub.Count_And_Get
278 ( p_count => x_msg_count,
279 p_data => x_msg_data
280 );
281
282 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
283 THEN
284 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
285 END IF;
286
287 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
288
289 ROLLBACK TO CREATE_JTF_PERZ_QUERY_PUB;
290 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
291
292 Fnd_Msg_Pub.Count_And_Get
293 ( p_count => x_msg_count,
294 p_data => x_msg_data
295 );
296
297 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
298 THEN
299 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
300 END IF;
301
302 WHEN OTHERS THEN
303
304 ROLLBACK TO CREATE_JTF_PERZ_QUERY_PUB;
305 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
306
307 Fnd_Msg_Pub.Count_And_Get
308 ( p_count => x_msg_count,
309 p_data => x_msg_data
310 );
311
312 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
313 THEN
314 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
315 END IF;
316
317 END Create_Perz_Query;
318 -- ****************************************************************************
319
320 PROCEDURE Get_Perz_Query
321 ( p_api_version_number IN NUMBER,
322 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
323
324 p_application_id IN NUMBER,
325 p_profile_id IN NUMBER,
326 p_profile_name IN VARCHAR2,
327
328 p_query_id IN NUMBER,
329 p_query_name IN VARCHAR2,
330 p_query_type IN VARCHAR2,
331
332 x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
333 x_query_name OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
334 x_query_type OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
335 x_query_desc OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
336 x_query_data_source OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
337
338 x_query_param_tbl OUT NOCOPY /* file.sql.39 change */ Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE,
339 x_query_order_by_tbl OUT NOCOPY /* file.sql.39 change */ Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE,
340 x_query_raw_sql_rec OUT NOCOPY /* file.sql.39 change */ Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE,
341
342 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
343 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
344 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
345 )IS
346 -- ******* Local Variables ********
347
348 l_api_version_number NUMBER := p_api_version_number;
349 l_api_name CONSTANT VARCHAR2(30) := 'Create Perz Query';
350
351 BEGIN
352
353 -- ******* Standard Begins ********
354
355 -- Standard call to check for call compatibility.
356 -- IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME)
357 -- THEN
358 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
359 -- END IF;
360
361 -- Initialize message list if p_init_msg_list is set to TRUE.
362 IF Fnd_Api.to_Boolean( p_init_msg_list )
363 THEN
364 Fnd_Msg_Pub.initialize;
365 END IF;
366
367 -- Initialize API return status to success
368 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
369
370 Jtf_Perz_Query_Pvt.Get_Perz_Query
371 ( p_api_version_number,
372 p_init_msg_list,
373
374 p_application_id,
375 p_profile_id,
376 p_profile_name,
377
378 p_query_id,
379 p_query_name,
380 p_query_type,
381
382 x_query_id ,
383 x_query_name ,
384 x_query_type,
385 x_query_desc,
386 x_query_data_source,
387
388 x_query_param_tbl,
389 x_query_order_by_tbl,
390 x_query_raw_sql_rec,
391
392 x_return_status,
393 x_msg_count,
394 x_msg_data
395 );
396
397 -- ******** Standard Ends ***********
398 --
399 -- End of API body.
400
401 -- Debug Message
402 -- Standard call to get message count and if count is 1, get message info.
403 Fnd_Msg_Pub.Count_And_Get
404 (
405 p_count => x_msg_count,
406 p_data => x_msg_data
407 );
408
409
410 EXCEPTION
411
412 WHEN Fnd_Api.G_EXC_ERROR THEN
413
414 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
415
416 Fnd_Msg_Pub.Count_And_Get
417 ( p_count => x_msg_count,
418 p_data => x_msg_data
419 );
420
421 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
422 THEN
423 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
424 END IF;
425
426 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
427
428 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
429
430 Fnd_Msg_Pub.Count_And_Get
431 ( p_count => x_msg_count,
432 p_data => x_msg_data
433 );
434
435 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
436 THEN
437 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
438 END IF;
439
440 WHEN OTHERS THEN
441
442
443 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
444
445 Fnd_Msg_Pub.Count_And_Get
446 ( p_count => x_msg_count,
447 p_data => x_msg_data
448 );
449
450 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
451 THEN
452 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
453 END IF;
454
455
456 END Get_Perz_Query;
457
458 -- ****************************************************************************
459 PROCEDURE Get_Perz_Query_Summary
460 ( p_api_version_number IN NUMBER,
461 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
462
463 p_application_id IN NUMBER,
464 p_profile_id IN NUMBER,
465 p_profile_name IN VARCHAR2,
466
467 p_query_id IN NUMBER,
468 p_query_name IN VARCHAR2,
469 p_query_type IN VARCHAR2,
470
471 x_query_out_tbl OUT NOCOPY /* file.sql.39 change */ Jtf_Perz_Query_Pub.QUERY_OUT_TBL_TYPE,
472
473 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
474 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
475 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
476 )IS
477
478 -- ******* Local Variables ********
479
480 l_api_version_number NUMBER := p_api_version_number;
481 l_api_name CONSTANT VARCHAR2(30) := 'Create Perz Query';
482
483 BEGIN
484
485 -- ******* Standard Begins ********
486
487 -- Standard call to check for call compatibility.
488 -- IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME)
489 -- THEN
490 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
491 -- END IF;
492
493 -- Initialize message list if p_init_msg_list is set to TRUE.
494 IF Fnd_Api.to_Boolean( p_init_msg_list )
495 THEN
496 Fnd_Msg_Pub.initialize;
497 END IF;
498
499 -- Initialize API return status to success
500 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
501
502 Jtf_Perz_Query_Pvt.Get_Perz_Query_Summary
503 ( p_api_version_number,
504 p_init_msg_list,
505
506 p_application_id,
507 p_profile_id,
508 p_profile_name,
509 p_query_id,
510 p_query_name,
511 p_query_type,
512
513 x_query_out_tbl,
514
515
516 x_return_status,
517 x_msg_count,
518 x_msg_data
519 );
520
521
522 -- ******** Standard Ends ***********
523 --
524 -- End of API body.
525
526 -- Debug Message
527 -- Standard call to get message count and if count is 1, get message info.
528 Fnd_Msg_Pub.Count_And_Get
529 (
530 p_count => x_msg_count,
531 p_data => x_msg_data
532 );
533
534
535 EXCEPTION
536
537 WHEN Fnd_Api.G_EXC_ERROR THEN
538
539 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
540
541 Fnd_Msg_Pub.Count_And_Get
542 ( p_count => x_msg_count,
543 p_data => x_msg_data
544 );
545
546 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
547 THEN
548 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
549 END IF;
550
551 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
552
553 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
554
555 Fnd_Msg_Pub.Count_And_Get
556 ( p_count => x_msg_count,
557 p_data => x_msg_data
558 );
559
560 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
561 THEN
562 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
563 END IF;
564
565 WHEN OTHERS THEN
566
567
568 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
569
570 Fnd_Msg_Pub.Count_And_Get
571 ( p_count => x_msg_count,
572 p_data => x_msg_data
573 );
574
575 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
576 THEN
577 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
578 END IF;
579 END Get_Perz_Query_Summary;
580 -- *****************************************************************************
581 PROCEDURE Update_Perz_Query
582 ( p_api_version_number IN NUMBER,
583 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
584 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
585
586 p_application_id IN NUMBER,
587 p_profile_id IN NUMBER,
588
589 p_query_id IN NUMBER,
590 p_query_name IN VARCHAR2,
591 p_query_type IN VARCHAR2,
592 p_query_desc IN VARCHAR2,
593 p_query_data_source IN VARCHAR2,
594
595 p_query_param_tbl IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
596 := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,
597 p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE
598 := Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,
599 p_query_raw_sql_rec IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE
600 := Jtf_Perz_Query_Pub.G_MISS_QUERY_RAW_SQL_REC,
601
602 x_query_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
603 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
604 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
605 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
606 )IS
607
608 -- ******* Local Variables ********
609
610 l_api_version_number NUMBER := p_api_version_number;
611 l_api_name CONSTANT VARCHAR2(30) := 'Create Perz Query';
612
613 BEGIN
614
615 -- ******* Standard Begins ********
616
617 -- Standard Start of API savepoint
618 SAVEPOINT UPDATE_JTF_PERZ_QUERY_PUB;
619
620 -- Standard call to check for call compatibility.
621 -- IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME)
622 -- THEN
623 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
624 -- END IF;
625
626 -- Initialize message list if p_init_msg_list is set to TRUE.
627 IF Fnd_Api.to_Boolean( p_init_msg_list )
628 THEN
629 Fnd_Msg_Pub.initialize;
630 END IF;
631
632 -- Initialize API return status to success
633 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
634
635 Jtf_Perz_Query_Pvt.Update_Perz_Query
636 ( p_api_version_number,
637 p_init_msg_list,
638 p_commit,
639 p_application_id,
640 p_profile_id,
641 p_query_id,
642 p_query_name,
643 p_query_type,
644 p_query_desc,
645 p_query_data_source,
646 p_query_param_tbl,
647 p_query_order_by_tbl,
648 p_query_raw_sql_rec,
649 x_query_id,
650 x_return_status,
651 x_msg_count,
652 x_msg_data
653 );
654
655
656 -- ******** Standard Ends ***********
657 --
658 -- End of API body.
659 --
660 -- 7. Commit the whole thing
661
662 -- Standard check of p_commit.
663 IF Fnd_Api.To_Boolean ( p_commit )
664 THEN
665 COMMIT WORK;
666 END IF;
667
668 -- Debug Message
669 -- Standard call to get message count and if count is 1, get message info.
670 Fnd_Msg_Pub.Count_And_Get
671 (
672 p_count => x_msg_count,
673 p_data => x_msg_data
674 );
675
676
677 EXCEPTION
678
679 WHEN Fnd_Api.G_EXC_ERROR THEN
680
681 ROLLBACK TO UPDATE_JTF_PERZ_QUERY_PUB;
682 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
683
684 Fnd_Msg_Pub.Count_And_Get
685 ( p_count => x_msg_count,
686 p_data => x_msg_data
687 );
688
689 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
690 THEN
691 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
692 END IF;
693
694 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
695
696 ROLLBACK TO UPDATE_JTF_PERZ_QUERY_PUB;
697 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
698
699 Fnd_Msg_Pub.Count_And_Get
700 ( p_count => x_msg_count,
701 p_data => x_msg_data
702 );
703
704 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
705 THEN
706 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
707 END IF;
708
709 WHEN OTHERS THEN
710
711 ROLLBACK TO UPDATE_JTF_PERZ_QUERY_PUB;
712 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
713
714 Fnd_Msg_Pub.Count_And_Get
715 ( p_count => x_msg_count,
716 p_data => x_msg_data
717 );
718
719 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
720 THEN
721 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
722 END IF;
723
724
725 END Update_Perz_Query;
726 -- *****************************************************************************
727 PROCEDURE Delete_Perz_Query
728 ( p_api_version_number IN NUMBER,
729 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
730 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
731
732 p_application_id IN NUMBER,
733 p_profile_id IN NUMBER,
734 p_query_id IN NUMBER,
735
736 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
737 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
738 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
739 )IS
740
741
742 -- ******* Local Variables ********
743
744 l_api_version_number NUMBER := p_api_version_number;
745 l_api_name CONSTANT VARCHAR2(30) := 'Create Perz Query';
746
747 BEGIN
748
749 -- ******* Standard Begins ********
750
751 -- Standard Start of API savepoint
752 SAVEPOINT DELETE_JTF_PERZ_QUERY_PUB;
753
754 -- Standard call to check for call compatibility.
755 -- IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME)
756 -- THEN
757 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
758 -- END IF;
759
760 -- Initialize message list if p_init_msg_list is set to TRUE.
761 IF Fnd_Api.to_Boolean( p_init_msg_list )
762 THEN
763 Fnd_Msg_Pub.initialize;
764 END IF;
765
766 -- Initialize API return status to success
767 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
768 Jtf_Perz_Query_Pvt.Delete_Perz_Query
769 ( p_api_version_number,
770 p_init_msg_list,
771 p_commit,
772 p_application_id,
773 p_profile_id,
774 p_query_id,
775
776 x_return_status,
777 x_msg_count,
778 x_msg_data
779 );
780
781
782 -- ******** Standard Ends ***********
783 --
784 -- End of API body.
785 --
786 -- 7. Commit the whole thing
787
788 -- Standard check of p_commit.
789 IF Fnd_Api.To_Boolean ( p_commit )
790 THEN
791 COMMIT WORK;
792 END IF;
793
794 -- Debug Message
795 -- Standard call to get message count and if count is 1, get message info.
796 Fnd_Msg_Pub.Count_And_Get
797 (
798 p_count => x_msg_count,
799 p_data => x_msg_data
800 );
801
802
803 EXCEPTION
804
805 WHEN Fnd_Api.G_EXC_ERROR THEN
806
807 ROLLBACK TO DELETE_JTF_PERZ_QUERY_PUB;
808 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
809
810 Fnd_Msg_Pub.Count_And_Get
811 ( p_count => x_msg_count,
812 p_data => x_msg_data
813 );
814
815 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
816 THEN
817 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
818 END IF;
819
820 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
821
822 ROLLBACK TO DELETE_JTF_PERZ_QUERY_PUB;
823 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
824
825 Fnd_Msg_Pub.Count_And_Get
826 ( p_count => x_msg_count,
827 p_data => x_msg_data
828 );
829
830 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
831 THEN
832 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
833 END IF;
834
835 WHEN OTHERS THEN
836
837 ROLLBACK TO DELETE_JTF_PERZ_QUERY_PUB;
838 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
839
840 Fnd_Msg_Pub.Count_And_Get
841 ( p_count => x_msg_count,
842 p_data => x_msg_data
843 );
844
845 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
846 THEN
847 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME, l_api_name );
848 END IF;
849
850
851
852 END Delete_Perz_Query;
853
854 END Jtf_Perz_Query_Pub;