[Home] [Help]
PACKAGE BODY: APPS.IEX_WEBDIR_PKG
Source
1 PACKAGE BODY IEX_WEBDIR_PKG AS
2 /* $Header: iexvwbab.pls 120.2 2005/07/06 19:16:17 jypark noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) :='IEX_WEBDIR_PKG';
6 G_FILE_NAME CONSTANT VARCHAR2(12) :='iexvwbas.pls';
7
8 G_APPL_ID NUMBER;
9 G_LOGIN_ID NUMBER;
10 G_PROGRAM_ID NUMBER;
11 G_USER_ID NUMBER;
12 G_REQUEST_ID NUMBER;
13
14 PROCEDURE Create_WebAssist(
15 p_api_version IN NUMBER,
16 p_init_msg_list IN VARCHAR2,
17 p_commit IN VARCHAR2,
18 p_validation_level IN NUMBER,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2,
22 p_assist_rec IN assist_rec_type,
23 p_web_assist_rec IN web_assist_rec_type,
24 p_web_search_rec IN web_search_rec_type,
25 p_query_string_rec IN query_string_rec_type
26 )
27 IS
28 l_api_version CONSTANT NUMBER := 1.0;
29 l_api_name CONSTANT VARCHAR2(30) := 'Create_WebAssist';
30 l_return_status VARCHAR2(1);
31 l_msg_count NUMBER;
32 l_msg_data VARCHAR2(32767);
33
34 CURSOR c1 IS SELECT 'X' FROM IEX_WEB_ASSISTS
35 WHERE web_assist_id = p_web_assist_rec.web_assist_id;
36
37 CURSOR c2 IS SELECT 'X' FROM IEX_WEB_SEARCHES
38 WHERE search_id = p_web_search_rec.search_id;
39
40 CURSOR c3 IS SELECT 'X' FROM IEX_QUERY_STRINGS
41 WHERE query_string_id = p_query_string_rec.query_string_id;
42
43 p_dummy CHAR(1);
44
45 l_web_assist_rec web_assist_rec_type;
46 l_web_search_rec web_search_rec_type;
47 l_query_string_rec query_string_rec_type;
48 BEGIN
49 -- Standard begin of API savepoint
50 SAVEPOINT Create_WebAssist_PVT;
51
52 -- Standard call to check FOR call compatibility.
53 IF NOT FND_API.Compatible_API_Call (l_api_version,
54 p_api_version,
55 l_api_name,
56 G_PKG_NAME)
57 THEN
58 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
59 END IF;
60
61 -- Check p_init_msg_list
62 IF FND_API.to_Boolean( p_init_msg_list ) THEN
63 FND_MSG_PUB.initialize;
64 END IF;
65
66 -- Initialize API return status to success
67 x_return_status := FND_API.G_RET_STS_SUCCESS;
68
69 -- API body
70 IF p_web_assist_rec.web_assist_id IS NOT NULL
71 THEN
72 OPEN c1;
73 FETCH c1 INTO P_DUMMY;
74 IF c1%notfound THEN
75 INSERT INTO IEX_WEB_ASSISTS(
76 web_assist_id,
77 assist_id,
78 object_version_number,
79 proxy_host,
80 proxy_port,
81 enabled_flag,
82 last_update_date,
83 creation_date,
84 created_by,
85 last_updated_by,
86 last_update_login,
87 attribute_category,
88 attribute1,
89 attribute2,
90 attribute3,
91 attribute4,
92 attribute5,
93 attribute6,
94 attribute7,
95 attribute8,
96 attribute9,
97 attribute10,
98 attribute11,
99 attribute12,
100 attribute13,
101 attribute14,
102 attribute15
103 )
104 VALUES(
105 p_web_assist_rec.web_assist_id,
106 p_web_assist_rec.assist_id,
107 p_web_assist_rec.object_version_number,
108 p_web_assist_rec.proxy_host,
109 p_web_assist_rec.proxy_port,
110 p_web_assist_rec.enabled_flag,
111 p_web_assist_rec.last_update_date,
112 p_web_assist_rec.creation_date,
113 p_web_assist_rec.created_by,
114 p_web_assist_rec.last_updated_by,
115 p_web_assist_rec.last_update_login,
116 p_web_assist_rec.attribute_category,
117 p_web_assist_rec.attribute1,
118 p_web_assist_rec.attribute2,
119 p_web_assist_rec.attribute3,
120 p_web_assist_rec.attribute4,
121 p_web_assist_rec.attribute5,
122 p_web_assist_rec.attribute6,
123 p_web_assist_rec.attribute7,
124 p_web_assist_rec.attribute8,
125 p_web_assist_rec.attribute9,
126 p_web_assist_rec.attribute10,
127 p_web_assist_rec.attribute11,
128 p_web_assist_rec.attribute12,
129 p_web_assist_rec.attribute13,
130 p_web_assist_rec.attribute14,
131 p_web_assist_rec.attribute15
132 );
133 END IF;
134
135 CLOSE c1;
136 END IF;
137
138 IF p_web_search_rec.search_id IS NOT NULL
139 THEN
140 OPEN c2;
141 FETCH c2 INTO P_DUMMY;
142 IF c2%notfound THEN
143 INSERT INTO IEX_WEB_SEARCHES(
144 search_id,
145 web_assist_id,
146 object_version_number,
147 enabled_flag,
148 search_url ,
149 cgi_server,
150 next_page_ident,
151 max_nbr_pages,
152 last_update_date,
153 creation_date,
154 created_by,
155 last_updated_by,
156 last_update_login,
157 directory_assist_flag, -- add by jypark 12/26/2000 for new requirement
158 attribute_category,
159 attribute1,
160 attribute2,
161 attribute3,
162 attribute4,
163 attribute5,
164 attribute6,
165 attribute7,
166 attribute8,
167 attribute9,
168 attribute10,
169 attribute11,
170 attribute12,
171 attribute13,
172 attribute14,
173 attribute15
174 )
175 VALUES(
176 p_web_search_rec.search_id,
177 p_web_search_rec.web_assist_id,
178 p_web_search_rec.object_version_number,
179 p_web_search_rec.enabled_flag,
180 p_web_search_rec.search_url,
181 p_web_search_rec.cgi_server,
182 p_web_search_rec.next_page_ident,
183 p_web_search_rec.max_nbr_pages,
184 p_web_search_rec.last_update_date,
185 p_web_search_rec.creation_date,
186 p_web_search_rec.created_by,
187 p_web_search_rec.last_updated_by,
188 p_web_search_rec.last_update_login,
189 p_web_search_rec.directory_assist_flag, -- add by jypark 12/26/2000 for new requirement
190 p_web_search_rec.attribute_category,
191 p_web_search_rec.attribute1,
192 p_web_search_rec.attribute2,
193 p_web_search_rec.attribute3,
194 p_web_search_rec.attribute4,
195 p_web_search_rec.attribute5,
196 p_web_search_rec.attribute6,
197 p_web_search_rec.attribute7,
198 p_web_search_rec.attribute8,
199 p_web_search_rec.attribute9,
200 p_web_search_rec.attribute10,
201 p_web_search_rec.attribute11,
202 p_web_search_rec.attribute12,
203 p_web_search_rec.attribute13,
204 p_web_search_rec.attribute14,
205 p_web_search_rec.attribute15
206 );
207 END IF;
208 CLOSE c2;
209 END IF;
210
211 IF p_query_string_rec.query_string_id IS NOT NULL
212 THEN
213 OPEN c3;
214 FETCH c3 INTO P_DUMMY;
215 IF c3%notfound THEN
216 INSERT INTO IEX_QUERY_STRINGS(
217 query_string_id,
218 search_id,
219 object_version_number,
220 switch_separator,
221 url_separator,
222 header_const,
223 trailer_const,
224 enabled_flag,
225 last_update_date,
226 creation_date,
227 created_by,
228 last_updated_by,
229 last_update_login,
230 attribute_category,
231 attribute1,
232 attribute2,
233 attribute3,
234 attribute4,
235 attribute5,
236 attribute6,
237 attribute7,
238 attribute8,
239 attribute9,
240 attribute10,
241 attribute11,
242 attribute12,
243 attribute13,
244 attribute14,
245 attribute15
246 )
247 VALUES(
248 p_query_string_rec.query_string_id,
249 p_query_string_rec.search_id,
250 p_query_string_rec.object_version_number,
251 p_query_string_rec.switch_separator,
252 p_query_string_rec.url_separator,
253 p_query_string_rec.header_const,
254 p_query_string_rec.trailer_const,
255 p_query_string_rec.enabled_flag,
256 p_query_string_rec.last_update_date,
257 p_query_string_rec.creation_date,
258 p_query_string_rec.created_by,
259 p_query_string_rec.last_updated_by,
260 p_query_string_rec.last_update_login,
261 p_query_string_rec.attribute_category,
262 p_query_string_rec.attribute1,
263 p_query_string_rec.attribute2,
264 p_query_string_rec.attribute3,
265 p_query_string_rec.attribute4,
266 p_query_string_rec.attribute5,
267 p_query_string_rec.attribute6,
268 p_query_string_rec.attribute7,
269 p_query_string_rec.attribute8,
270 p_query_string_rec.attribute9,
271 p_query_string_rec.attribute10,
272 p_query_string_rec.attribute11,
273 p_query_string_rec.attribute12,
274 p_query_string_rec.attribute13,
275 p_query_string_rec.attribute14,
276 p_query_string_rec.attribute15
277 );
278 END IF;
279 END IF;
280
281 -- Standard check of p_commit.
282 IF FND_API.To_Boolean ( p_commit ) THEN
283 COMMIT WORK;
284 END IF;
285
286 -- Standard call to get message count AND IF count is 1, get message info.
287 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
288 p_data => x_msg_data );
289
290
291 --
292 -- Normal API Exception handling, IF exception occurs outside of phone processing loop
293 --
294 EXCEPTION
295
296 WHEN FND_API.G_EXC_ERROR THEN
297 ROLLBACK TO Create_WebAssist_PVT;
298 x_return_status := FND_API.G_RET_STS_ERROR ;
299 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
300 p_data => x_msg_data );
301 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
302 ROLLBACK TO Create_WebAssist_PVT;
303 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
304 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
305 p_data => x_msg_data );
306 WHEN OTHERS THEN
307 ROLLBACK TO Create_WebAssist_PVT;
308 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
309 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
310 THEN
311 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
312 END IF;
313 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
314 p_data => x_msg_data );
315 END;
316
317 PROCEDURE Lock_WebAssist(
318 p_api_version IN NUMBER,
319 p_init_msg_list IN VARCHAR2,
320 p_commit IN VARCHAR2,
321 p_validation_level IN NUMBER,
322 x_return_status OUT NOCOPY VARCHAR2,
323 x_msg_count OUT NOCOPY NUMBER,
324 x_msg_data OUT NOCOPY VARCHAR2,
325 p_assist_rec IN assist_rec_type,
326 p_web_assist_rec IN web_assist_rec_type,
327 p_web_search_rec IN web_search_rec_type,
328 p_query_string_rec IN query_string_rec_type
329 )
330 AS
331 l_api_version CONSTANT NUMBER := 1.0;
332 l_api_name CONSTANT VARCHAR2(30) := 'Lock_WebAssist';
333 l_return_status VARCHAR2(1);
334 l_msg_count NUMBER;
335 l_msg_data VARCHAR2(32767);
336
337 l_web_assist_rec web_assist_rec_type;
338 l_web_search_rec web_search_rec_type;
339 l_query_string_rec query_string_rec_type;
340
341 BEGIN
342
343 -- Standard begin of API savepoint
344 SAVEPOINT Lock_WebAssist_PVT;
345
346 -- Standard call to check FOR call compatibility.
347 IF NOT FND_API.Compatible_API_Call (l_api_version,
348 p_api_version,
349 l_api_name,
350 G_PKG_NAME)
351 THEN
352 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
353 END IF;
354
355 -- Check p_init_msg_list
356 IF FND_API.to_Boolean( p_init_msg_list ) THEN
357 FND_MSG_PUB.initialize;
358 END IF;
359
360 -- Initialize API return status to success
361 x_return_status := FND_API.G_RET_STS_SUCCESS;
362
363 -- API body
364 DECLARE
365 v_dummy char(1);
366
367 CURSOR c1 IS SELECT 'X'
368 FROM IEX_WEB_assists
369 WHERE web_assist_id = p_web_assist_rec.web_assist_id
370 FOR UPDATE;
371 BEGIN
372 OPEN c1;
373 FETCH c1 INTO v_dummy;
374 CLOSE c1;
375 END;
376
377 DECLARE
378 v_dummy char(1);
379 CURSOR c1 IS SELECT 'X'
380 FROM IEX_WEB_searches
381 WHERE search_id = p_web_search_rec.search_id
382 FOR UPDATE;
383 BEGIN
384 OPEN c1;
385 FETCH c1 INTO v_dummy;
386 CLOSE c1;
387 END;
388
389 DECLARE
390 v_dummy char(1);
391 CURSOR c1 IS SELECT 'X'
392 FROM iex_query_strings
393 WHERE query_string_id = p_query_string_rec.query_string_id
394 FOR UPDATE;
395 BEGIN
396 OPEN c1;
397 FETCH c1 INTO v_dummy;
398 CLOSE c1;
399 END;
400
401 -- ENDof API body
402
403 -- Standard check of p_commit.
404 IF FND_API.To_Boolean ( p_commit ) THEN
405 COMMIT WORK;
406 END IF;
407
408 -- Standard call to get message count AND IF count is 1, get message info.
409
410 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
411 p_data => x_msg_data );
412
413
414 --
415 -- Normal API Exception handling, IF exception occurs outside of phone processing loop
416 --
417
418 EXCEPTION
419
420 WHEN FND_API.G_EXC_ERROR THEN
421 ROLLBACK TO Lock_WebAssist_PVT;
422 x_return_status := FND_API.G_RET_STS_ERROR ;
423 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
424 p_data => x_msg_data );
425 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
426 ROLLBACK TO Lock_WebAssist_PVT;
427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
428 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
429 p_data => x_msg_data );
430 WHEN OTHERS THEN
431 ROLLBACK TO Lock_WebAssist_PVT;
432 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
433 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
434 THEN
435 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
436 END IF;
437 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
438 p_data => x_msg_data );
439 END;
440
441 PROCEDURE Update_WebAssist(
442 p_api_version IN NUMBER,
443 p_init_msg_list IN VARCHAR2,
447 x_msg_count OUT NOCOPY NUMBER,
444 p_commit IN VARCHAR2,
445 p_validation_level IN NUMBER,
446 x_return_status OUT NOCOPY VARCHAR2,
448 x_msg_data OUT NOCOPY VARCHAR2,
449 p_assist_rec IN assist_rec_type,
450 p_web_assist_rec IN web_assist_rec_type,
451 p_web_search_rec IN web_search_rec_type,
452 p_query_string_rec IN query_string_rec_type
453 )
454 IS
455 l_api_version CONSTANT NUMBER := 1.0;
456 l_api_name CONSTANT VARCHAR2(30) := 'Update_WebAssist';
457 l_return_status VARCHAR2(1);
458 l_msg_count NUMBER;
459 l_msg_data VARCHAR2(32767);
460
461 l_web_assist_rec web_assist_rec_type;
462 l_web_search_rec web_search_rec_type;
463 l_query_string_rec query_string_rec_type;
464
465 BEGIN
466 -- Standard begin of API savepoint
467 SAVEPOINT Update_WebAssist_PVT;
468 -- Standard call to check FOR call compatibility.
469 IF NOT FND_API.Compatible_API_Call (l_api_version,
470 p_api_version,
471 l_api_name,
472 G_PKG_NAME)
473 THEN
474 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
475 END IF;
476
477 -- Check p_init_msg_list
478 IF FND_API.to_Boolean( p_init_msg_list ) THEN
479 FND_MSG_PUB.initialize;
480 END IF;
481
482 -- Implementation of User Hooks
483 /* Copy all parameters to local variables to be passed to Pre, Post AND Business APIs */
484 /* l_rec - will be used as In Out parameter in pre/post/Business API calls */
485 /* l_return_status - will be a OUT NOCOPY VARiable to get return code FROM called APIs */
486 l_web_assist_rec := p_web_assist_rec;
487 l_web_search_rec := p_web_search_rec;
488 l_query_string_rec := p_query_string_rec;
489
490 -- Initialize API return status to success
491 x_return_status := FND_API.G_RET_STS_SUCCESS;
492
493 -- API body
494 UPDATE IEX_WEB_assists
495 SET
496 proxy_host = p_web_assist_rec.proxy_host,
497 proxy_port = p_web_assist_rec.proxy_port,
498 last_update_date = p_web_assist_rec.last_update_date,
499 last_updated_by = p_web_assist_rec.last_updated_by,
500 last_update_login = p_web_assist_rec.last_update_login,
501 attribute_category = p_web_assist_rec.attribute_category,
502 attribute1 = p_web_assist_rec.attribute1,
503 attribute2 = p_web_assist_rec.attribute2,
504 attribute3 = p_web_assist_rec.attribute3,
505 attribute4 = p_web_assist_rec.attribute4,
506 attribute5 = p_web_assist_rec.attribute5,
507 attribute6 = p_web_assist_rec.attribute6,
508 attribute7 = p_web_assist_rec.attribute7,
509 attribute8 = p_web_assist_rec.attribute8,
510 attribute9 = p_web_assist_rec.attribute9,
511 attribute10 = p_web_assist_rec.attribute10,
512 attribute11 = p_web_assist_rec.attribute11,
513 attribute12 = p_web_assist_rec.attribute12,
514 attribute13 = p_web_assist_rec.attribute13,
515 attribute14 = p_web_assist_rec.attribute14,
516 attribute15 = p_web_assist_rec.attribute15
517 WHERE web_assist_id = p_web_assist_rec.web_assist_id;
518
519 UPDATE IEX_WEB_searches
520 SET
521 enabled_flag = p_web_search_rec.enabled_flag,
522 search_url = p_web_search_rec.search_url,
523 cgi_server = p_web_search_rec.cgi_server,
524 next_page_ident = p_web_search_rec.next_page_ident,
525 max_nbr_pages = p_web_search_rec.max_nbr_pages,
526 last_update_date = p_web_search_rec.last_update_date,
527 last_updated_by = p_web_search_rec.last_updated_by,
528 last_update_login = p_web_search_rec.last_update_login,
529 directory_assist_flag = p_web_search_rec.directory_assist_flag, -- add by jypakr 12/26/2000 for new requirement
530 attribute_category = p_web_search_rec.attribute_category,
531 attribute1 = p_web_search_rec.attribute1,
532 attribute2 = p_web_search_rec.attribute2,
533 attribute3 = p_web_search_rec.attribute3,
534 attribute4 = p_web_search_rec.attribute4,
535 attribute5 = p_web_search_rec.attribute5,
536 attribute6 = p_web_search_rec.attribute6,
537 attribute7 = p_web_search_rec.attribute7,
538 attribute8 = p_web_search_rec.attribute8,
539 attribute9 = p_web_search_rec.attribute9,
540 attribute10 = p_web_search_rec.attribute10,
541 attribute11 = p_web_search_rec.attribute11,
542 attribute12 = p_web_search_rec.attribute12,
543 attribute13 = p_web_search_rec.attribute13,
544 attribute14 = p_web_search_rec.attribute14,
545 attribute15 = p_web_search_rec.attribute15
546 WHERE search_id = p_web_search_rec.search_id;
547
548 UPDATE iex_query_strings
549 SET
550 switch_separator = p_query_string_rec.switch_separator,
551 url_separator = p_query_string_rec.url_separator,
552 header_const = p_query_string_rec.header_const,
553 trailer_const = p_query_string_rec.trailer_const,
554 last_update_date = p_query_string_rec.last_update_date,
555 last_updated_by = p_query_string_rec.last_updated_by,
556 last_update_login = p_query_string_rec.last_update_login,
557 attribute_category = p_query_string_rec.attribute_category,
558 attribute1 = p_query_string_rec.attribute1,
559 attribute2 = p_query_string_rec.attribute2,
560 attribute3 = p_query_string_rec.attribute3,
561 attribute4 = p_query_string_rec.attribute4,
562 attribute5 = p_query_string_rec.attribute5,
563 attribute6 = p_query_string_rec.attribute6,
567 attribute10 = p_query_string_rec.attribute10,
564 attribute7 = p_query_string_rec.attribute7,
565 attribute8 = p_query_string_rec.attribute8,
566 attribute9 = p_query_string_rec.attribute9,
568 attribute11 = p_query_string_rec.attribute11,
569 attribute12 = p_query_string_rec.attribute12,
570 attribute13 = p_query_string_rec.attribute13,
571 attribute14 = p_query_string_rec.attribute14,
572 attribute15 = p_query_string_rec.attribute15
573 WHERE query_string_id = p_query_string_rec.query_string_id;
574
575 -- Standard check of p_commit.
576 IF FND_API.To_Boolean ( p_commit ) THEN
577 COMMIT WORK;
578 END IF;
579
580 -- Standard call to get message count AND IF count is 1, get message info.
581 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
582 p_data => x_msg_data );
583
584
585 --
586 -- Normal API Exception handling, IF exception occurs outside of phone processing loop
587 --
588 EXCEPTION
589
590 WHEN FND_API.G_EXC_ERROR THEN
591
592 ROLLBACK TO Update_WebAssist_PVT;
593 x_return_status := FND_API.G_RET_STS_ERROR ;
594
595 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
596 p_data => x_msg_data );
597
598 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
599
600 ROLLBACK TO Update_WebAssist_PVT;
601 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
602
603 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
604 p_data => x_msg_data );
605
606 WHEN OTHERS THEN
607
608 ROLLBACK TO Update_WebAssist_PVT;
609 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
610
611 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
612 THEN
613 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
614 END IF;
615
616 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
617 p_data => x_msg_data );
618
619 END;
620
621 PROCEDURE Delete_WebAssist(
622 p_api_version IN NUMBER,
623 p_init_msg_list IN VARCHAR2,
624 p_commit IN VARCHAR2,
625 p_validation_level IN NUMBER,
626 x_return_status OUT NOCOPY VARCHAR2,
627 x_msg_count OUT NOCOPY NUMBER,
628 x_msg_data OUT NOCOPY VARCHAR2,
629 p_assist_rec IN assist_rec_type,
630 p_web_assist_rec IN web_assist_rec_type,
631 p_web_search_rec IN web_search_rec_type,
632 p_query_string_rec IN query_string_rec_type
633 )
634 IS
635 l_api_version CONSTANT NUMBER := 1.0;
636 l_api_name CONSTANT VARCHAR2(30) := 'Delete_WebAssist';
637 l_return_status VARCHAR2(1);
638 l_msg_count NUMBER;
639 l_msg_data VARCHAR2(32767);
640
641 l_web_assist_rec web_assist_rec_type;
642 l_web_search_rec web_search_rec_type;
643 l_query_string_rec query_string_rec_type;
644
645 BEGIN
646 -- Standard begin of API savepoint
647 SAVEPOINT Delete_WebAssist_PVT;
648
649 -- Standard call to check FOR call compatibility.
650 IF NOT FND_API.Compatible_API_Call (l_api_version,
651 p_api_version,
652 l_api_name,
653 G_PKG_NAME)
654 THEN
655 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
656 END IF;
657
658 -- Check p_init_msg_list
659 IF FND_API.to_Boolean( p_init_msg_list ) THEN
660 FND_MSG_PUB.initialize;
661 END IF;
662
663 -- Implementation of User Hooks
664 /* Copy all parameters to local variables to be passed to Pre, Post AND Business APIs */
665 /* l_rec - will be used as In Out parameter in pre/post/Business API calls */
666 /* l_return_status - will be a OUT NOCOPY VARiable to get return code FROM called APIs */
667 l_web_assist_rec := p_web_assist_rec;
668 l_web_search_rec := p_web_search_rec;
669 l_query_string_rec := p_query_string_rec;
670
671 -- Initialize API return status to success
672 x_return_status := FND_API.G_RET_STS_SUCCESS;
673
674 -- API body
675 IF p_query_string_rec.query_string_id IS NOT NULL THEN
676 DELETE FROM ast_query_strings
677 WHERE query_string_id = p_query_string_rec.query_string_id;
678 END IF;
679
680 IF p_web_search_rec.search_id IS NOT NULL THEN
681 DELETE FROM IEX_WEB_searches
682 WHERE search_id = p_web_search_rec.search_id;
683 END IF;
684
685 IF p_web_assist_rec.web_assist_id IS NOT NULL THEN
686 DELETE FROM IEX_WEB_assists
687 WHERE web_assist_id = p_web_assist_rec.web_assist_id;
688 END IF;
689
690
691 -- Standard check of p_commit.
692 IF FND_API.To_Boolean ( p_commit ) THEN
693 commit work;
694 END IF;
695 -- ENDof API body
696
697 -- Standard call to get message count AND IF count is 1, get message info.
698 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
699 p_data => x_msg_data );
700
701
702 --
703 -- Normal API Exception handling, IF exception occurs outside of phone processing loop
704 --
705 EXCEPTION
706
707 WHEN FND_API.G_EXC_ERROR THEN
708 ROLLBACK TO Delete_WebAssist_PVT;
709 x_return_status := FND_API.G_RET_STS_ERROR ;
710 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
711 p_data => x_msg_data );
712 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
713 ROLLBACK TO Delete_WebAssist_PVT;
714 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
715 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
716 p_data => x_msg_data );
717 WHEN OTHERS THEN
718 ROLLBACK TO Delete_WebAssist_PVT;
719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
720 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
721 THEN
722 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
723 END IF;
724 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
725 p_data => x_msg_data );
726 END;
727 BEGIN
728
729 G_APPL_ID := FND_GLOBAL.Prog_Appl_Id;
730 G_LOGIN_ID := FND_GLOBAL.Conc_Login_Id;
731 G_PROGRAM_ID := FND_GLOBAL.Conc_Program_Id;
732 G_USER_ID := FND_GLOBAL.User_Id;
733 G_REQUEST_ID := FND_GLOBAL.Conc_Request_Id;
734 END IEX_WEBDIR_PKG;