12: G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
13:
14: PROCEDURE Create_WebAssist(
15: p_api_version IN NUMBER,
16: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
17: p_commit IN VARCHAR2 := FND_API.G_FALSE,
18: p_validation_level IN NUMBER :=
19: FND_API.G_VALID_LEVEL_FULL,
20: x_return_status OUT VARCHAR2,
13:
14: PROCEDURE Create_WebAssist(
15: p_api_version IN NUMBER,
16: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
17: p_commit IN VARCHAR2 := FND_API.G_FALSE,
18: p_validation_level IN NUMBER :=
19: FND_API.G_VALID_LEVEL_FULL,
20: x_return_status OUT VARCHAR2,
21: x_msg_count OUT NUMBER,
15: p_api_version IN NUMBER,
16: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
17: p_commit IN VARCHAR2 := FND_API.G_FALSE,
18: p_validation_level IN NUMBER :=
19: FND_API.G_VALID_LEVEL_FULL,
20: x_return_status OUT VARCHAR2,
21: x_msg_count OUT NUMBER,
22: x_msg_data OUT VARCHAR2,
23: p_assist_rec assist_rec_type,
54: -- Standard begin of API savepoint
55: SAVEPOINT Create_WebAssist_PVT;
56:
57: -- Standard call to check FOR call compatibility.
58: IF NOT FND_API.Compatible_API_Call (l_api_version,
59: p_api_version,
60: l_api_name,
61: G_PKG_NAME)
62: THEN
59: p_api_version,
60: l_api_name,
61: G_PKG_NAME)
62: THEN
63: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
64: END IF;
65:
66: -- Check p_init_msg_list
67: IF FND_API.to_Boolean( p_init_msg_list ) THEN
63: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
64: END IF;
65:
66: -- Check p_init_msg_list
67: IF FND_API.to_Boolean( p_init_msg_list ) THEN
68: FND_MSG_PUB.initialize;
69: END IF;
70:
71: -- Implementation of User Hooks
88: p_web_search_rec => l_web_search_rec,
89: p_query_string_rec => l_query_string_rec
90: );
91:
92: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
93: RAISE FND_API.G_EXC_ERROR;
94: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
95: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
96: END IF;
89: p_query_string_rec => l_query_string_rec
90: );
91:
92: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
93: RAISE FND_API.G_EXC_ERROR;
94: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
95: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
96: END IF;
97: END IF;
90: );
91:
92: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
93: RAISE FND_API.G_EXC_ERROR;
94: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
95: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
96: END IF;
97: END IF;
98:
91:
92: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
93: RAISE FND_API.G_EXC_ERROR;
94: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
95: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
96: END IF;
97: END IF;
98:
99:
108: p_web_search_rec => l_web_search_rec,
109: p_query_string_rec => l_query_string_rec
110: );
111:
112: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
113: RAISE FND_API.G_EXC_ERROR;
114: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
115: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
116: END IF;
109: p_query_string_rec => l_query_string_rec
110: );
111:
112: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
113: RAISE FND_API.G_EXC_ERROR;
114: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
115: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
116: END IF;
117: END IF;
110: );
111:
112: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
113: RAISE FND_API.G_EXC_ERROR;
114: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
115: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
116: END IF;
117: END IF;
118:
111:
112: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
113: RAISE FND_API.G_EXC_ERROR;
114: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
115: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
116: END IF;
117: END IF;
118:
119:
117: END IF;
118:
119:
120: -- Initialize API return status to success
121: x_return_status := FND_API.G_RET_STS_SUCCESS;
122:
123: -- API body
124: IF p_assist_rec.assist_id IS NOT NULL
125: THEN
375:
376:
377: -- Success Message
378: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) AND
379: x_return_status = FND_API.G_RET_STS_SUCCESS
380: THEN
381: FND_MESSAGE.Set_Name('ast', 'API_SUCCESS');
382: FND_MESSAGE.Set_Token('ROW', 'ast_QUERY_STRING', TRUE);
383: FND_MSG_PUB.Add;
394: p_web_assist_rec => l_web_assist_rec,
395: p_web_search_rec => l_web_search_rec,
396: p_query_string_rec => l_query_string_rec
397: );
398: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
399: RAISE FND_API.G_EXC_ERROR;
400: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
401: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
402: END IF;
395: p_web_search_rec => l_web_search_rec,
396: p_query_string_rec => l_query_string_rec
397: );
398: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
399: RAISE FND_API.G_EXC_ERROR;
400: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
401: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
402: END IF;
403:
396: p_query_string_rec => l_query_string_rec
397: );
398: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
399: RAISE FND_API.G_EXC_ERROR;
400: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
401: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
402: END IF;
403:
404: END IF;
397: );
398: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
399: RAISE FND_API.G_EXC_ERROR;
400: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
401: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
402: END IF;
403:
404: END IF;
405:
414: p_web_search_rec => l_web_search_rec,
415: p_query_string_rec => l_query_string_rec
416: );
417:
418: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
419: RAISE FND_API.G_EXC_ERROR;
420: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
421: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
422: END IF;
415: p_query_string_rec => l_query_string_rec
416: );
417:
418: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
419: RAISE FND_API.G_EXC_ERROR;
420: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
421: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
422: END IF;
423: END IF;
416: );
417:
418: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
419: RAISE FND_API.G_EXC_ERROR;
420: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
421: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
422: END IF;
423: END IF;
424: -- Standard check of p_commit.
417:
418: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
419: RAISE FND_API.G_EXC_ERROR;
420: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
421: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
422: END IF;
423: END IF;
424: -- Standard check of p_commit.
425: IF FND_API.To_Boolean ( p_commit ) THEN
421: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
422: END IF;
423: END IF;
424: -- Standard check of p_commit.
425: IF FND_API.To_Boolean ( p_commit ) THEN
426: COMMIT WORK;
427: END IF;
428:
429: -- Debug Message
442: -- Normal API Exception handling, IF exception occurs outside of phone processing loop
443: --
444: EXCEPTION
445:
446: WHEN FND_API.G_EXC_ERROR THEN
447: ROLLBACK TO Create_WebAssist_PVT;
448: x_return_status := FND_API.G_RET_STS_ERROR ;
449: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
450: p_data => x_msg_data );
444: EXCEPTION
445:
446: WHEN FND_API.G_EXC_ERROR THEN
447: ROLLBACK TO Create_WebAssist_PVT;
448: x_return_status := FND_API.G_RET_STS_ERROR ;
449: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
450: p_data => x_msg_data );
451: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
452: ROLLBACK TO Create_WebAssist_PVT;
447: ROLLBACK TO Create_WebAssist_PVT;
448: x_return_status := FND_API.G_RET_STS_ERROR ;
449: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
450: p_data => x_msg_data );
451: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
452: ROLLBACK TO Create_WebAssist_PVT;
453: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
454: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
455: p_data => x_msg_data );
449: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
450: p_data => x_msg_data );
451: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
452: ROLLBACK TO Create_WebAssist_PVT;
453: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
454: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
455: p_data => x_msg_data );
456: WHEN OTHERS THEN
457: ROLLBACK TO Create_WebAssist_PVT;
454: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
455: p_data => x_msg_data );
456: WHEN OTHERS THEN
457: ROLLBACK TO Create_WebAssist_PVT;
458: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
459: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
460: THEN
461: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
462: END IF;
465: END;
466:
467: PROCEDURE Lock_WebAssist(
468: p_api_version IN NUMBER := 1.0,
469: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
470: p_commit IN VARCHAR2 := FND_API.G_FALSE,
471: p_validation_level IN NUMBER :=
472: FND_API.G_VALID_LEVEL_FULL,
473: x_return_status OUT VARCHAR2,
466:
467: PROCEDURE Lock_WebAssist(
468: p_api_version IN NUMBER := 1.0,
469: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
470: p_commit IN VARCHAR2 := FND_API.G_FALSE,
471: p_validation_level IN NUMBER :=
472: FND_API.G_VALID_LEVEL_FULL,
473: x_return_status OUT VARCHAR2,
474: x_msg_count OUT NUMBER,
468: p_api_version IN NUMBER := 1.0,
469: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
470: p_commit IN VARCHAR2 := FND_API.G_FALSE,
471: p_validation_level IN NUMBER :=
472: FND_API.G_VALID_LEVEL_FULL,
473: x_return_status OUT VARCHAR2,
474: x_msg_count OUT NUMBER,
475: x_msg_data OUT VARCHAR2,
476: p_assist_rec assist_rec_type,
495: -- Standard begin of API savepoint
496: SAVEPOINT Lock_WebAssist_PVT;
497:
498: -- Standard call to check FOR call compatibility.
499: IF NOT FND_API.Compatible_API_Call (l_api_version,
500: p_api_version,
501: l_api_name,
502: G_PKG_NAME)
503: THEN
500: p_api_version,
501: l_api_name,
502: G_PKG_NAME)
503: THEN
504: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
505: END IF;
506:
507: -- Check p_init_msg_list
508: IF FND_API.to_Boolean( p_init_msg_list ) THEN
504: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
505: END IF;
506:
507: -- Check p_init_msg_list
508: IF FND_API.to_Boolean( p_init_msg_list ) THEN
509: FND_MSG_PUB.initialize;
510: END IF;
511:
512: -- Implementation of User Hooks
528: p_web_assist_rec => l_web_assist_rec,
529: p_web_search_rec => l_web_search_rec,
530: p_query_string_rec => l_query_string_rec
531: );
532: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
533: RAISE FND_API.G_EXC_ERROR;
534: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
535: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
536: END IF;
529: p_web_search_rec => l_web_search_rec,
530: p_query_string_rec => l_query_string_rec
531: );
532: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
533: RAISE FND_API.G_EXC_ERROR;
534: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
535: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
536: END IF;
537: END IF;
530: p_query_string_rec => l_query_string_rec
531: );
532: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
533: RAISE FND_API.G_EXC_ERROR;
534: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
535: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
536: END IF;
537: END IF;
538:
531: );
532: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
533: RAISE FND_API.G_EXC_ERROR;
534: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
535: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
536: END IF;
537: END IF;
538:
539:
547: p_web_assist_rec => l_web_assist_rec,
548: p_web_search_rec => l_web_search_rec,
549: p_query_string_rec => l_query_string_rec
550: );
551: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
552: RAISE FND_API.G_EXC_ERROR;
553: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
554: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
555: END IF;
548: p_web_search_rec => l_web_search_rec,
549: p_query_string_rec => l_query_string_rec
550: );
551: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
552: RAISE FND_API.G_EXC_ERROR;
553: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
554: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
555: END IF;
556: END IF;
549: p_query_string_rec => l_query_string_rec
550: );
551: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
552: RAISE FND_API.G_EXC_ERROR;
553: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
554: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
555: END IF;
556: END IF;
557:
550: );
551: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
552: RAISE FND_API.G_EXC_ERROR;
553: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
554: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
555: END IF;
556: END IF;
557:
558: -- Initialize API return status to success
555: END IF;
556: END IF;
557:
558: -- Initialize API return status to success
559: x_return_status := FND_API.G_RET_STS_SUCCESS;
560:
561: -- API body
562: DECLARE
563: v_dummy char(1);
622: p_web_search_rec => l_web_search_rec,
623: p_query_string_rec => l_query_string_rec
624: );
625:
626: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
627: RAISE FND_API.G_EXC_ERROR;
628: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
629: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
630: END IF;
623: p_query_string_rec => l_query_string_rec
624: );
625:
626: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
627: RAISE FND_API.G_EXC_ERROR;
628: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
629: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
630: END IF;
631:
624: );
625:
626: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
627: RAISE FND_API.G_EXC_ERROR;
628: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
629: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
630: END IF;
631:
632: END IF;
625:
626: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
627: RAISE FND_API.G_EXC_ERROR;
628: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
629: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
630: END IF;
631:
632: END IF;
633:
642: p_web_search_rec => l_web_search_rec,
643: p_query_string_rec => l_query_string_rec
644: );
645:
646: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
647: RAISE FND_API.G_EXC_ERROR;
648: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
649: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
650: END IF;
643: p_query_string_rec => l_query_string_rec
644: );
645:
646: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
647: RAISE FND_API.G_EXC_ERROR;
648: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
649: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
650: END IF;
651: END IF;
644: );
645:
646: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
647: RAISE FND_API.G_EXC_ERROR;
648: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
649: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
650: END IF;
651: END IF;
652:
645:
646: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
647: RAISE FND_API.G_EXC_ERROR;
648: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
649: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
650: END IF;
651: END IF;
652:
653: -- Standard check of p_commit.
650: END IF;
651: END IF;
652:
653: -- Standard check of p_commit.
654: IF FND_API.To_Boolean ( p_commit ) THEN
655: COMMIT WORK;
656: END IF;
657:
658: -- Standard call to get message count AND IF count is 1, get message info.
666: --
667:
668: EXCEPTION
669:
670: WHEN FND_API.G_EXC_ERROR THEN
671: ROLLBACK TO Lock_WebAssist_PVT;
672: x_return_status := FND_API.G_RET_STS_ERROR ;
673: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
674: p_data => x_msg_data );
668: EXCEPTION
669:
670: WHEN FND_API.G_EXC_ERROR THEN
671: ROLLBACK TO Lock_WebAssist_PVT;
672: x_return_status := FND_API.G_RET_STS_ERROR ;
673: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
674: p_data => x_msg_data );
675: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
676: ROLLBACK TO Lock_WebAssist_PVT;
671: ROLLBACK TO Lock_WebAssist_PVT;
672: x_return_status := FND_API.G_RET_STS_ERROR ;
673: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
674: p_data => x_msg_data );
675: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
676: ROLLBACK TO Lock_WebAssist_PVT;
677: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
678: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
679: p_data => x_msg_data );
673: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
674: p_data => x_msg_data );
675: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
676: ROLLBACK TO Lock_WebAssist_PVT;
677: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
678: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
679: p_data => x_msg_data );
680: WHEN OTHERS THEN
681: ROLLBACK TO Lock_WebAssist_PVT;
678: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
679: p_data => x_msg_data );
680: WHEN OTHERS THEN
681: ROLLBACK TO Lock_WebAssist_PVT;
682: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
683: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
684: THEN
685: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
686: END IF;
689: END;
690:
691: PROCEDURE Update_WebAssist(
692: p_api_version IN NUMBER,
693: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
694: p_commit IN VARCHAR2 := FND_API.G_FALSE,
695: p_validation_level IN NUMBER :=
696: FND_API.G_VALID_LEVEL_FULL,
697: x_return_status OUT VARCHAR2,
690:
691: PROCEDURE Update_WebAssist(
692: p_api_version IN NUMBER,
693: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
694: p_commit IN VARCHAR2 := FND_API.G_FALSE,
695: p_validation_level IN NUMBER :=
696: FND_API.G_VALID_LEVEL_FULL,
697: x_return_status OUT VARCHAR2,
698: x_msg_count OUT NUMBER,
692: p_api_version IN NUMBER,
693: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
694: p_commit IN VARCHAR2 := FND_API.G_FALSE,
695: p_validation_level IN NUMBER :=
696: FND_API.G_VALID_LEVEL_FULL,
697: x_return_status OUT VARCHAR2,
698: x_msg_count OUT NUMBER,
699: x_msg_data OUT VARCHAR2,
700: p_assist_rec assist_rec_type,
717: BEGIN
718: -- Standard begin of API savepoint
719: SAVEPOINT Update_WebAssist_PVT;
720: -- Standard call to check FOR call compatibility.
721: IF NOT FND_API.Compatible_API_Call (l_api_version,
722: p_api_version,
723: l_api_name,
724: G_PKG_NAME)
725: THEN
722: p_api_version,
723: l_api_name,
724: G_PKG_NAME)
725: THEN
726: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
727: END IF;
728:
729: -- Check p_init_msg_list
730: IF FND_API.to_Boolean( p_init_msg_list ) THEN
726: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
727: END IF;
728:
729: -- Check p_init_msg_list
730: IF FND_API.to_Boolean( p_init_msg_list ) THEN
731: FND_MSG_PUB.initialize;
732: END IF;
733:
734: -- Implementation of User Hooks
751: p_web_search_rec => l_web_search_rec,
752: p_query_string_rec => l_query_string_rec
753: );
754:
755: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
756: RAISE FND_API.G_EXC_ERROR;
757: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
758: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
759: END IF;
752: p_query_string_rec => l_query_string_rec
753: );
754:
755: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
756: RAISE FND_API.G_EXC_ERROR;
757: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
758: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
759: END IF;
760: END IF;
753: );
754:
755: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
756: RAISE FND_API.G_EXC_ERROR;
757: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
758: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
759: END IF;
760: END IF;
761:
754:
755: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
756: RAISE FND_API.G_EXC_ERROR;
757: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
758: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
759: END IF;
760: END IF;
761:
762:
772: p_query_string_rec => l_query_string_rec
773: );
774:
775:
776: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
777: RAISE FND_API.G_EXC_ERROR;
778: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
779: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
780: END IF;
773: );
774:
775:
776: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
777: RAISE FND_API.G_EXC_ERROR;
778: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
779: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
780: END IF;
781: END IF;
774:
775:
776: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
777: RAISE FND_API.G_EXC_ERROR;
778: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
779: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
780: END IF;
781: END IF;
782:
775:
776: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
777: RAISE FND_API.G_EXC_ERROR;
778: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
779: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
780: END IF;
781: END IF;
782:
783: -- Initialize API return status to success
780: END IF;
781: END IF;
782:
783: -- Initialize API return status to success
784: x_return_status := FND_API.G_RET_STS_SUCCESS;
785:
786: -- API body
787: UPDATE ast_web_assists
788: SET
877: p_web_assist_rec => l_web_assist_rec,
878: p_web_search_rec => l_web_search_rec,
879: p_query_string_rec => l_query_string_rec
880: );
881: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
882: RAISE FND_API.G_EXC_ERROR;
883: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
884: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
885: END IF;
878: p_web_search_rec => l_web_search_rec,
879: p_query_string_rec => l_query_string_rec
880: );
881: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
882: RAISE FND_API.G_EXC_ERROR;
883: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
884: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
885: END IF;
886:
879: p_query_string_rec => l_query_string_rec
880: );
881: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
882: RAISE FND_API.G_EXC_ERROR;
883: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
884: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
885: END IF;
886:
887: END IF;
880: );
881: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
882: RAISE FND_API.G_EXC_ERROR;
883: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
884: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
885: END IF;
886:
887: END IF;
888:
895: p_assist_rec => l_assist_rec,
896: p_web_assist_rec => l_web_assist_rec,
897: p_web_search_rec => l_web_search_rec,
898: p_query_string_rec => l_query_string_rec);
899: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
900: RAISE FND_API.G_EXC_ERROR;
901: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
902: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
903: END IF;
896: p_web_assist_rec => l_web_assist_rec,
897: p_web_search_rec => l_web_search_rec,
898: p_query_string_rec => l_query_string_rec);
899: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
900: RAISE FND_API.G_EXC_ERROR;
901: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
902: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
903: END IF;
904: END IF;
897: p_web_search_rec => l_web_search_rec,
898: p_query_string_rec => l_query_string_rec);
899: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
900: RAISE FND_API.G_EXC_ERROR;
901: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
902: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
903: END IF;
904: END IF;
905:
898: p_query_string_rec => l_query_string_rec);
899: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
900: RAISE FND_API.G_EXC_ERROR;
901: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
902: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
903: END IF;
904: END IF;
905:
906: -- Standard check of p_commit.
903: END IF;
904: END IF;
905:
906: -- Standard check of p_commit.
907: IF FND_API.To_Boolean ( p_commit ) THEN
908: COMMIT WORK;
909: END IF;
910:
911: -- Standard call to get message count AND IF count is 1, get message info.
917: -- Normal API Exception handling, IF exception occurs outside of phone processing loop
918: --
919: EXCEPTION
920:
921: WHEN FND_API.G_EXC_ERROR THEN
922:
923: ROLLBACK TO Update_WebAssist_PVT;
924: x_return_status := FND_API.G_RET_STS_ERROR ;
925:
920:
921: WHEN FND_API.G_EXC_ERROR THEN
922:
923: ROLLBACK TO Update_WebAssist_PVT;
924: x_return_status := FND_API.G_RET_STS_ERROR ;
925:
926: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
927: p_data => x_msg_data );
928:
925:
926: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
927: p_data => x_msg_data );
928:
929: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
930:
931: ROLLBACK TO Update_WebAssist_PVT;
932: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
933:
928:
929: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
930:
931: ROLLBACK TO Update_WebAssist_PVT;
932: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
933:
934: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
935: p_data => x_msg_data );
936:
936:
937: WHEN OTHERS THEN
938:
939: ROLLBACK TO Update_WebAssist_PVT;
940: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
941:
942: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
943: THEN
944: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
950: END;
951:
952: PROCEDURE Delete_WebAssist(
953: p_api_version IN NUMBER,
954: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
955: p_commit IN VARCHAR2 := FND_API.G_FALSE,
956: p_validation_level IN NUMBER :=
957: FND_API.G_VALID_LEVEL_FULL,
958: x_return_status OUT VARCHAR2,
951:
952: PROCEDURE Delete_WebAssist(
953: p_api_version IN NUMBER,
954: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
955: p_commit IN VARCHAR2 := FND_API.G_FALSE,
956: p_validation_level IN NUMBER :=
957: FND_API.G_VALID_LEVEL_FULL,
958: x_return_status OUT VARCHAR2,
959: x_msg_count OUT NUMBER,
953: p_api_version IN NUMBER,
954: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
955: p_commit IN VARCHAR2 := FND_API.G_FALSE,
956: p_validation_level IN NUMBER :=
957: FND_API.G_VALID_LEVEL_FULL,
958: x_return_status OUT VARCHAR2,
959: x_msg_count OUT NUMBER,
960: x_msg_data OUT VARCHAR2,
961: p_assist_rec assist_rec_type,
979: -- Standard begin of API savepoint
980: SAVEPOINT Delete_WebAssist_PVT;
981:
982: -- Standard call to check FOR call compatibility.
983: IF NOT FND_API.Compatible_API_Call (l_api_version,
984: p_api_version,
985: l_api_name,
986: G_PKG_NAME)
987: THEN
984: p_api_version,
985: l_api_name,
986: G_PKG_NAME)
987: THEN
988: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
989: END IF;
990:
991: -- Check p_init_msg_list
992: IF FND_API.to_Boolean( p_init_msg_list ) THEN
988: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
989: END IF;
990:
991: -- Check p_init_msg_list
992: IF FND_API.to_Boolean( p_init_msg_list ) THEN
993: FND_MSG_PUB.initialize;
994: END IF;
995:
996: -- Implementation of User Hooks
1013: p_web_search_rec => l_web_search_rec,
1014: p_query_string_rec => l_query_string_rec
1015: );
1016:
1017: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1018: RAISE FND_API.G_EXC_ERROR;
1019: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1020: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1021: END IF;
1014: p_query_string_rec => l_query_string_rec
1015: );
1016:
1017: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1018: RAISE FND_API.G_EXC_ERROR;
1019: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1020: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1021: END IF;
1022: END IF;
1015: );
1016:
1017: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1018: RAISE FND_API.G_EXC_ERROR;
1019: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1020: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1021: END IF;
1022: END IF;
1023:
1016:
1017: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1018: RAISE FND_API.G_EXC_ERROR;
1019: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1020: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1021: END IF;
1022: END IF;
1023:
1024:
1033: p_web_search_rec => l_web_search_rec,
1034: p_query_string_rec => l_query_string_rec
1035: );
1036:
1037: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1038: RAISE FND_API.G_EXC_ERROR;
1039: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1040: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1041: END IF;
1034: p_query_string_rec => l_query_string_rec
1035: );
1036:
1037: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1038: RAISE FND_API.G_EXC_ERROR;
1039: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1040: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1041: END IF;
1042: END IF;
1035: );
1036:
1037: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1038: RAISE FND_API.G_EXC_ERROR;
1039: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1040: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1041: END IF;
1042: END IF;
1043:
1036:
1037: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1038: RAISE FND_API.G_EXC_ERROR;
1039: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1040: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1041: END IF;
1042: END IF;
1043:
1044: -- Initialize API return status to success
1041: END IF;
1042: END IF;
1043:
1044: -- Initialize API return status to success
1045: x_return_status := FND_API.G_RET_STS_SUCCESS;
1046:
1047: -- API body
1048: IF p_web_assist_rec.web_assist_id IS NOT NULL THEN
1049: DELETE FROM ast_query_strings
1061: END IF;
1062:
1063:
1064: -- Standard check of p_commit.
1065: IF FND_API.To_Boolean ( p_commit ) THEN
1066: commit work;
1067: END IF;
1068: -- ENDof API body
1069:
1076: p_assist_rec => l_assist_rec,
1077: p_web_assist_rec => l_web_assist_rec,
1078: p_web_search_rec => l_web_search_rec,
1079: p_query_string_rec => l_query_string_rec);
1080: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1081: RAISE FND_API.G_EXC_ERROR;
1082: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1083: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1084: END IF;
1077: p_web_assist_rec => l_web_assist_rec,
1078: p_web_search_rec => l_web_search_rec,
1079: p_query_string_rec => l_query_string_rec);
1080: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1081: RAISE FND_API.G_EXC_ERROR;
1082: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1083: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1084: END IF;
1085:
1078: p_web_search_rec => l_web_search_rec,
1079: p_query_string_rec => l_query_string_rec);
1080: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1081: RAISE FND_API.G_EXC_ERROR;
1082: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1083: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1084: END IF;
1085:
1086: END IF;
1079: p_query_string_rec => l_query_string_rec);
1080: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1081: RAISE FND_API.G_EXC_ERROR;
1082: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1083: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1084: END IF;
1085:
1086: END IF;
1087:
1094: p_assist_rec => l_assist_rec,
1095: p_web_assist_rec => l_web_assist_rec,
1096: p_web_search_rec => l_web_search_rec,
1097: p_query_string_rec => l_query_string_rec);
1098: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1099: RAISE FND_API.G_EXC_ERROR;
1100: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1101: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1102: END IF;
1095: p_web_assist_rec => l_web_assist_rec,
1096: p_web_search_rec => l_web_search_rec,
1097: p_query_string_rec => l_query_string_rec);
1098: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1099: RAISE FND_API.G_EXC_ERROR;
1100: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1101: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1102: END IF;
1103: END IF;
1096: p_web_search_rec => l_web_search_rec,
1097: p_query_string_rec => l_query_string_rec);
1098: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1099: RAISE FND_API.G_EXC_ERROR;
1100: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1101: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1102: END IF;
1103: END IF;
1104:
1097: p_query_string_rec => l_query_string_rec);
1098: IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1099: RAISE FND_API.G_EXC_ERROR;
1100: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1101: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1102: END IF;
1103: END IF;
1104:
1105: -- Standard call to get message count AND IF count is 1, get message info.
1111: -- Normal API Exception handling, IF exception occurs outside of phone processing loop
1112: --
1113: EXCEPTION
1114:
1115: WHEN FND_API.G_EXC_ERROR THEN
1116: ROLLBACK TO Delete_WebAssist_PVT;
1117: x_return_status := FND_API.G_RET_STS_ERROR ;
1118: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1119: p_data => x_msg_data );
1113: EXCEPTION
1114:
1115: WHEN FND_API.G_EXC_ERROR THEN
1116: ROLLBACK TO Delete_WebAssist_PVT;
1117: x_return_status := FND_API.G_RET_STS_ERROR ;
1118: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1119: p_data => x_msg_data );
1120: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1121: ROLLBACK TO Delete_WebAssist_PVT;
1116: ROLLBACK TO Delete_WebAssist_PVT;
1117: x_return_status := FND_API.G_RET_STS_ERROR ;
1118: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1119: p_data => x_msg_data );
1120: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1121: ROLLBACK TO Delete_WebAssist_PVT;
1122: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1123: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1124: p_data => x_msg_data );
1118: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1119: p_data => x_msg_data );
1120: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1121: ROLLBACK TO Delete_WebAssist_PVT;
1122: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1123: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1124: p_data => x_msg_data );
1125: WHEN OTHERS THEN
1126: ROLLBACK TO Delete_WebAssist_PVT;
1123: FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1124: p_data => x_msg_data );
1125: WHEN OTHERS THEN
1126: ROLLBACK TO Delete_WebAssist_PVT;
1127: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1128: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1129: THEN
1130: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1131: END IF;