[Home] [Help]
PACKAGE BODY: APPS.AST_WEBASSIST_PVT
Source
1 PACKAGE BODY ast_WebAssist_PVT AS
2 /* $Header: astvwbab.pls 115.6 2002/02/06 11:21:26 pkm ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) :='AST_WEBASSIST_PVT';
6 G_FILE_NAME CONSTANT VARCHAR2(12) :='astvwbas.pls';
7
8 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
9 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
10 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
11 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
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,
21 x_msg_count OUT NUMBER,
22 x_msg_data OUT VARCHAR2,
23 p_assist_rec assist_rec_type,
24 p_web_assist_rec web_assist_rec_type,
25 p_web_search_rec web_search_rec_type,
26 p_query_string_rec query_string_rec_type
27 )
28 IS
29 l_api_version CONSTANT NUMBER := 1.0;
30 l_api_name CONSTANT VARCHAR2(30) := 'Create_WebAssist';
31 l_return_status VARCHAR2(1);
32 l_msg_count NUMBER;
33 l_msg_data VARCHAR2(32767);
34
35 CURSOR c0 IS SELECT 'X' FROM ast_assists
36 WHERE assist_id = p_assist_rec.assist_id;
37
38 CURSOR c1 IS SELECT 'X' FROM ast_web_assists
39 WHERE web_assist_id = p_web_assist_rec.web_assist_id;
40
41 CURSOR c2 IS SELECT 'X' FROM ast_web_searches
42 WHERE search_id = p_web_search_rec.search_id;
43
44 CURSOR c3 IS SELECT 'X' FROM ast_query_strings
45 WHERE query_string_id = p_query_string_rec.query_string_id;
46
47 p_dummy CHAR(1);
48
49 l_assist_rec assist_rec_type;
50 l_web_assist_rec web_assist_rec_type;
51 l_web_search_rec web_search_rec_type;
52 l_query_string_rec query_string_rec_type;
53 BEGIN
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
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
72 /* Copy all parameters to local variables to be passed to Pre, Post AND Business APIs */
73 /* l_rec - will be used as In Out parameter in pre/post/Business API calls */
74 /* l_return_status - will be a out variable to get return code FROM called APIs */
75 l_assist_rec := p_assist_rec;
76 l_web_assist_rec := p_web_assist_rec;
77 l_web_search_rec := p_web_search_rec;
78 l_query_string_rec := p_query_string_rec;
79
80 /* Customer pre -processing section - Mandatory */
81 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'C' ) ) THEN
82 ast_WEBASSIST_CUHK.Create_WebAssist_PRE(p_api_version => l_api_version,
83 x_return_status => l_return_status,
84 x_msg_count => l_msg_count,
85 x_msg_data => l_msg_data,
86 p_assist_rec => l_assist_rec,
87 p_web_assist_rec => l_web_assist_rec,
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;
97 END IF;
98
99
100 /* Verticle industry pre- processing section - mandatory */
101 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'V' ) ) THEN
102 ast_WEBASSIST_VUHK.Create_WebAssist_PRE(p_api_version => l_api_version,
103 x_return_status => l_return_status,
104 x_msg_count => l_msg_count,
105 x_msg_data => l_msg_data,
106 p_assist_rec => l_assist_rec,
107 p_web_assist_rec => l_web_assist_rec,
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;
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
126 OPEN c0;
127 FETCH c0 INTO P_DUMMY;
128 IF c0%notfound THEN
129 INSERT INTO ast_assists(
130 assist_id,
131 object_version_number,
132 last_update_date,
133 last_updated_by,
134 creation_date,
135 created_by,
136 last_update_login,
137 assistance_type,
138 assist_location
139 )
140 VALUES(
141 p_assist_rec.assist_id,
142 p_assist_rec.object_version_number,
143 p_assist_rec.last_update_date,
144 p_assist_rec.last_updated_by,
145 p_assist_rec.creation_date,
146 p_assist_rec.created_by,
147 p_assist_rec.last_update_login,
148 p_assist_rec.assistance_type,
149 p_assist_rec.location
150 );
151 END IF;
152
153 CLOSE c0;
154 END IF;
155
156 IF p_web_assist_rec.web_assist_id IS NOT NULL
157 THEN
158 OPEN c1;
159 FETCH c1 INTO P_DUMMY;
160 IF c1%notfound THEN
161 INSERT INTO ast_web_assists(
162 web_assist_id,
163 assist_id,
164 object_version_number,
165 proxy_host,
166 proxy_port,
167 enabled_flag,
168 last_update_date,
169 creation_date,
170 created_by,
171 last_updated_by,
172 last_update_login,
173 attribute_category,
174 attribute1,
175 attribute2,
176 attribute3,
177 attribute4,
178 attribute5,
179 attribute6,
180 attribute7,
181 attribute8,
182 attribute9,
183 attribute10,
184 attribute11,
185 attribute12,
186 attribute13,
187 attribute14,
188 attribute15
189 )
190 VALUES(
191 p_web_assist_rec.web_assist_id,
192 p_web_assist_rec.assist_id,
193 p_web_assist_rec.object_version_number,
194 p_web_assist_rec.proxy_host,
195 p_web_assist_rec.proxy_port,
196 p_web_assist_rec.enabled_flag,
197 p_web_assist_rec.last_update_date,
198 p_web_assist_rec.creation_date,
199 p_web_assist_rec.created_by,
200 p_web_assist_rec.last_updated_by,
201 p_web_assist_rec.last_update_login,
202 p_web_assist_rec.attribute_category,
203 p_web_assist_rec.attribute1,
204 p_web_assist_rec.attribute2,
205 p_web_assist_rec.attribute3,
206 p_web_assist_rec.attribute4,
207 p_web_assist_rec.attribute5,
208 p_web_assist_rec.attribute6,
209 p_web_assist_rec.attribute7,
210 p_web_assist_rec.attribute8,
211 p_web_assist_rec.attribute9,
212 p_web_assist_rec.attribute10,
213 p_web_assist_rec.attribute11,
214 p_web_assist_rec.attribute12,
215 p_web_assist_rec.attribute13,
216 p_web_assist_rec.attribute14,
217 p_web_assist_rec.attribute15
218 );
219 END IF;
220
221 CLOSE c1;
222 END IF;
223
224 IF p_web_search_rec.search_id IS NOT NULL
225 THEN
226 OPEN c2;
227 FETCH c2 INTO P_DUMMY;
228 IF c2%notfound THEN
229 INSERT INTO ast_web_searches(
230 search_id,
231 web_assist_id,
232 object_version_number,
233 enabled_flag,
234 search_url ,
235 cgi_server,
236 next_page_ident,
237 max_nbr_pages,
238 last_update_date,
239 creation_date,
240 created_by,
241 last_updated_by,
242 last_update_login,
243 directory_assist_flag, -- add by jypark 12/26/2000 for new requirement
244 attribute_category,
245 attribute1,
246 attribute2,
247 attribute3,
248 attribute4,
249 attribute5,
250 attribute6,
251 attribute7,
252 attribute8,
253 attribute9,
254 attribute10,
255 attribute11,
256 attribute12,
257 attribute13,
258 attribute14,
259 attribute15
260 )
261 VALUES(
262 p_web_search_rec.search_id,
263 p_web_search_rec.web_assist_id,
264 p_web_search_rec.object_version_number,
265 p_web_search_rec.enabled_flag,
266 p_web_search_rec.search_url,
267 p_web_search_rec.cgi_server,
268 p_web_search_rec.next_page_ident,
269 p_web_search_rec.max_nbr_pages,
270 p_web_search_rec.last_update_date,
271 p_web_search_rec.creation_date,
272 p_web_search_rec.created_by,
273 p_web_search_rec.last_updated_by,
274 p_web_search_rec.last_update_login,
275 p_web_search_rec.directory_assist_flag, -- add by jypark 12/26/2000 for new requirement
276 p_web_search_rec.attribute_category,
277 p_web_search_rec.attribute1,
278 p_web_search_rec.attribute2,
279 p_web_search_rec.attribute3,
280 p_web_search_rec.attribute4,
281 p_web_search_rec.attribute5,
282 p_web_search_rec.attribute6,
283 p_web_search_rec.attribute7,
284 p_web_search_rec.attribute8,
285 p_web_search_rec.attribute9,
286 p_web_search_rec.attribute10,
287 p_web_search_rec.attribute11,
288 p_web_search_rec.attribute12,
289 p_web_search_rec.attribute13,
290 p_web_search_rec.attribute14,
291 p_web_search_rec.attribute15
292 );
293 END IF;
294 CLOSE c2;
295 END IF;
296
297 IF p_query_string_rec.query_string_id IS NOT NULL
298 THEN
299 OPEN c3;
300 FETCH c3 INTO P_DUMMY;
301 IF c3%notfound THEN
302 INSERT INTO ast_query_strings(
303 query_string_id,
304 search_id,
305 object_version_number,
306 switch_separator,
307 url_separator,
308 header_const,
309 trailer_const,
310 enabled_flag,
311 last_update_date,
312 creation_date,
313 created_by,
314 last_updated_by,
315 last_update_login,
316 attribute_category,
317 attribute1,
318 attribute2,
319 attribute3,
320 attribute4,
321 attribute5,
322 attribute6,
323 attribute7,
324 attribute8,
325 attribute9,
326 attribute10,
327 attribute11,
328 attribute12,
329 attribute13,
330 attribute14,
331 attribute15
332 )
333 VALUES(
334 p_query_string_rec.query_string_id,
335 p_query_string_rec.search_id,
336 p_query_string_rec.object_version_number,
337 p_query_string_rec.switch_separator,
338 p_query_string_rec.url_separator,
339 p_query_string_rec.header_const,
340 p_query_string_rec.trailer_const,
341 p_query_string_rec.enabled_flag,
342 p_query_string_rec.last_update_date,
343 p_query_string_rec.creation_date,
344 p_query_string_rec.created_by,
345 p_query_string_rec.last_updated_by,
346 p_query_string_rec.last_update_login,
347 p_query_string_rec.attribute_category,
348 p_query_string_rec.attribute1,
349 p_query_string_rec.attribute2,
350 p_query_string_rec.attribute3,
351 p_query_string_rec.attribute4,
352 p_query_string_rec.attribute5,
353 p_query_string_rec.attribute6,
354 p_query_string_rec.attribute7,
355 p_query_string_rec.attribute8,
356 p_query_string_rec.attribute9,
357 p_query_string_rec.attribute10,
358 p_query_string_rec.attribute11,
359 p_query_string_rec.attribute12,
360 p_query_string_rec.attribute13,
361 p_query_string_rec.attribute14,
362 p_query_string_rec.attribute15
363 );
364 END IF;
365 END IF;
366
367 -- Success Message
368 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
369 THEN
370 FND_MESSAGE.Set_Name('ast', 'API_ROWS_UPDATED');
371 FND_MESSAGE.Set_Token('ROW', 'ast_QUERY_STRING', TRUE);
372 FND_MESSAGE.Set_Token('NUMBER', 1, FALSE);
373 FND_MSG_PUB.Add;
374 END IF;
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;
384 END IF;
385 -- ENDof API boby
386
387 /* Vertical Post Processing section - mandatory */
388 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'V' ) ) THEN
389 ast_WEBASSIST_VUHK.Create_WebAssist_Post(p_api_version => l_api_version,
390 x_return_status => l_return_status,
391 x_msg_count => l_msg_count,
392 x_msg_data => l_msg_data,
393 p_assist_rec => l_assist_rec,
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;
403
404 END IF;
405
406 /* Customer Post Processing section - mandatory */
407 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'C' ) ) THEN
408 ast_WEBASSIST_CUHK.Create_WebAssist_Post (p_api_version => l_api_version,
409 x_return_status => l_return_status,
410 x_msg_count => l_msg_count,
411 x_msg_data => l_msg_data,
412 p_assist_rec => l_assist_rec,
413 p_web_assist_rec => l_web_assist_rec,
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;
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
430 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
431 THEN
432 FND_MESSAGE.Set_Name('ast', 'Pvt WebAssist API: End');
433 FND_MSG_PUB.Add;
434 END IF;
435
436 -- Standard call to get message count AND IF count is 1, get message info.
437 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
438 p_data => x_msg_data );
439
440
441 --
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 );
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;
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;
463 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
464 p_data => x_msg_data );
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,
474 x_msg_count OUT NUMBER,
475 x_msg_data OUT VARCHAR2,
476 p_assist_rec assist_rec_type,
477 p_web_assist_rec web_assist_rec_type,
478 p_web_search_rec web_search_rec_type,
479 p_query_string_rec query_string_rec_type
480 )
481 AS
482 l_api_version CONSTANT NUMBER := 1.0;
483 l_api_name CONSTANT VARCHAR2(30) := 'Lock_WebAssist';
484 l_return_status VARCHAR2(1);
485 l_msg_count NUMBER;
486 l_msg_data VARCHAR2(32767);
487
488 l_assist_rec assist_rec_type;
489 l_web_assist_rec web_assist_rec_type;
490 l_web_search_rec web_search_rec_type;
491 l_query_string_rec query_string_rec_type;
492
493 BEGIN
494
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
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
513 /* Copy all parameters to local variables to be passed to Pre, Post AND Business APIs */
514 /* l_rec - will be used as In Out parameter in pre/post/Business API calls */
515 /* l_return_status - will be a out variable to get return code FROM called APIs */
516 l_assist_rec := p_assist_rec;
517 l_web_assist_rec := p_web_assist_rec;
518 l_web_search_rec := p_web_search_rec;
519 l_query_string_rec := p_query_string_rec;
520
521 /* Customer pre -processing section - Mandatory */
522 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'C' ) ) THEN
523 ast_WEBASSIST_CUHK.Lock_WebAssist_PRE(p_api_version => l_api_version,
524 x_return_status => l_return_status,
525 x_msg_count => l_msg_count,
526 x_msg_data => l_msg_data,
527 p_assist_rec => l_assist_rec,
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;
537 END IF;
538
539
540 /* Verticle industry pre- processing section - mandatory */
541 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'V' ) ) THEN
542 ast_WEBASSIST_VUHK.Lock_WebAssist_PRE(p_api_version => l_api_version,
543 x_return_status => l_return_status,
544 x_msg_count => l_msg_count,
545 x_msg_data => l_msg_data,
546 p_assist_rec => l_assist_rec,
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;
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);
564
565 CURSOR c1 IS SELECT 'X'
566 FROM ast_assists
567 WHERE assist_id = p_assist_rec.assist_id
568 FOR UPDATE;
569 BEGIN
570 OPEN c1;
571 FETCH c1 INTO v_dummy;
572 CLOSE c1;
573 END;
574
575 DECLARE
576 v_dummy char(1);
577
578 CURSOR c1 IS SELECT 'X'
579 FROM ast_web_assists
580 WHERE web_assist_id = p_web_assist_rec.web_assist_id
581 FOR UPDATE;
582 BEGIN
583 OPEN c1;
584 FETCH c1 INTO v_dummy;
585 CLOSE c1;
586 END;
587
588 DECLARE
589 v_dummy char(1);
590 CURSOR c1 IS SELECT 'X'
591 FROM ast_web_searches
592 WHERE search_id = p_web_search_rec.search_id
593 FOR UPDATE;
594 BEGIN
595 OPEN c1;
596 FETCH c1 INTO v_dummy;
597 CLOSE c1;
598 END;
599
600 DECLARE
601 v_dummy char(1);
602 CURSOR c1 IS SELECT 'X'
603 FROM ast_query_strings
604 WHERE query_string_id = p_query_string_rec.query_string_id
605 FOR UPDATE;
606 BEGIN
607 OPEN c1;
608 FETCH c1 INTO v_dummy;
609 CLOSE c1;
610 END;
611
612 -- ENDof API body
613
614 /* Vertical Post Processing section - mandatory */
615 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'V' ) ) THEN
616 ast_WEBASSIST_VUHK.Lock_WebAssist_POST(p_api_version => l_api_version,
617 x_return_status => l_return_status,
618 x_msg_count => l_msg_count,
619 x_msg_data => l_msg_data,
620 p_assist_rec => l_assist_rec,
621 p_web_assist_rec => l_web_assist_rec,
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;
631
632 END IF;
633
634 /* Customer Post Processing section - mandatory */
635 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'C' ) ) THEN
636 ast_WEBASSIST_CUHK.Lock_WebAssist_POST (p_api_version => l_api_version,
637 x_return_status => l_return_status,
638 x_msg_count => l_msg_count,
639 x_msg_data => l_msg_data,
640 p_assist_rec => l_assist_rec,
641 p_web_assist_rec => l_web_assist_rec,
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;
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.
659
660 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
661 p_data => x_msg_data );
662
663
664 --
665 -- Normal API Exception handling, IF exception occurs outside of phone processing loop
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 );
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;
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;
687 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
688 p_data => x_msg_data );
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,
698 x_msg_count OUT NUMBER,
699 x_msg_data OUT VARCHAR2,
700 p_assist_rec assist_rec_type,
701 p_web_assist_rec web_assist_rec_type,
702 p_web_search_rec web_search_rec_type,
703 p_query_string_rec query_string_rec_type
704 )
705 IS
706 l_api_version CONSTANT NUMBER := 1.0;
707 l_api_name CONSTANT VARCHAR2(30) := 'Update_WebAssist';
708 l_return_status VARCHAR2(1);
709 l_msg_count NUMBER;
710 l_msg_data VARCHAR2(32767);
711
712 l_assist_rec assist_rec_type;
713 l_web_assist_rec web_assist_rec_type;
714 l_web_search_rec web_search_rec_type;
715 l_query_string_rec query_string_rec_type;
716
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
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
735 /* Copy all parameters to local variables to be passed to Pre, Post AND Business APIs */
736 /* l_rec - will be used as In Out parameter in pre/post/Business API calls */
737 /* l_return_status - will be a out variable to get return code FROM called APIs */
738 l_assist_rec := p_assist_rec;
739 l_web_assist_rec := p_web_assist_rec;
740 l_web_search_rec := p_web_search_rec;
741 l_query_string_rec := p_query_string_rec;
742
743 /* Customer pre -processing section - Mandatory */
744 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'C' ) ) THEN
745 ast_WEBASSIST_CUHK.Update_WebAssist_PRE(p_api_version => l_api_version,
746 x_return_status => l_return_status,
747 x_msg_count => l_msg_count,
748 x_msg_data => l_msg_data,
749 p_assist_rec => l_assist_rec,
750 p_web_assist_rec => l_web_assist_rec,
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;
760 END IF;
761
762
763 /* Verticle industry pre- processing section - mandatory */
764 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'V' ) ) THEN
765 ast_WEBASSIST_VUHK.Update_WebAssist_PRE(p_api_version => l_api_version,
766 x_return_status => l_return_status,
767 x_msg_count => l_msg_count,
768 x_msg_data => l_msg_data,
769 p_assist_rec => l_assist_rec,
770 p_web_assist_rec => l_web_assist_rec,
771 p_web_search_rec => l_web_search_rec,
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;
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
789 proxy_host = p_web_assist_rec.proxy_host,
790 proxy_port = p_web_assist_rec.proxy_port,
791 last_update_date = p_web_assist_rec.last_update_date,
792 last_updated_by = p_web_assist_rec.last_updated_by,
793 last_update_login = p_web_assist_rec.last_update_login,
794 attribute_category = p_web_assist_rec.attribute_category,
795 attribute1 = p_web_assist_rec.attribute1,
796 attribute2 = p_web_assist_rec.attribute2,
797 attribute3 = p_web_assist_rec.attribute3,
798 attribute4 = p_web_assist_rec.attribute4,
799 attribute5 = p_web_assist_rec.attribute5,
800 attribute6 = p_web_assist_rec.attribute6,
801 attribute7 = p_web_assist_rec.attribute7,
802 attribute8 = p_web_assist_rec.attribute8,
803 attribute9 = p_web_assist_rec.attribute9,
804 attribute10 = p_web_assist_rec.attribute10,
805 attribute11 = p_web_assist_rec.attribute11,
806 attribute12 = p_web_assist_rec.attribute12,
807 attribute13 = p_web_assist_rec.attribute13,
808 attribute14 = p_web_assist_rec.attribute14,
809 attribute15 = p_web_assist_rec.attribute15
810 WHERE web_assist_id = p_web_assist_rec.web_assist_id;
811
812 UPDATE ast_web_searches
813 SET
814 enabled_flag = p_web_search_rec.enabled_flag,
815 search_url = p_web_search_rec.search_url,
816 cgi_server = p_web_search_rec.cgi_server,
817 next_page_ident = p_web_search_rec.next_page_ident,
818 max_nbr_pages = p_web_search_rec.max_nbr_pages,
819 last_update_date = p_web_search_rec.last_update_date,
820 last_updated_by = p_web_search_rec.last_updated_by,
821 last_update_login = p_web_search_rec.last_update_login,
822 directory_assist_flag = p_web_search_rec.directory_assist_flag, -- add by jypakr 12/26/2000 for new requirement
823 attribute_category = p_web_search_rec.attribute_category,
824 attribute1 = p_web_search_rec.attribute1,
825 attribute2 = p_web_search_rec.attribute2,
826 attribute3 = p_web_search_rec.attribute3,
827 attribute4 = p_web_search_rec.attribute4,
828 attribute5 = p_web_search_rec.attribute5,
829 attribute6 = p_web_search_rec.attribute6,
830 attribute7 = p_web_search_rec.attribute7,
831 attribute8 = p_web_search_rec.attribute8,
832 attribute9 = p_web_search_rec.attribute9,
833 attribute10 = p_web_search_rec.attribute10,
834 attribute11 = p_web_search_rec.attribute11,
835 attribute12 = p_web_search_rec.attribute12,
836 attribute13 = p_web_search_rec.attribute13,
837 attribute14 = p_web_search_rec.attribute14,
838 attribute15 = p_web_search_rec.attribute15
839 WHERE search_id = p_web_search_rec.search_id;
840
841 UPDATE ast_query_strings
842 SET
843 switch_separator = p_query_string_rec.switch_separator,
844 url_separator = p_query_string_rec.url_separator,
845 header_const = p_query_string_rec.header_const,
846 trailer_const = p_query_string_rec.trailer_const,
847 last_update_date = p_query_string_rec.last_update_date,
848 last_updated_by = p_query_string_rec.last_updated_by,
849 last_update_login = p_query_string_rec.last_update_login,
850 attribute_category = p_query_string_rec.attribute_category,
851 attribute1 = p_query_string_rec.attribute1,
852 attribute2 = p_query_string_rec.attribute2,
853 attribute3 = p_query_string_rec.attribute3,
854 attribute4 = p_query_string_rec.attribute4,
855 attribute5 = p_query_string_rec.attribute5,
856 attribute6 = p_query_string_rec.attribute6,
857 attribute7 = p_query_string_rec.attribute7,
858 attribute8 = p_query_string_rec.attribute8,
859 attribute9 = p_query_string_rec.attribute9,
860 attribute10 = p_query_string_rec.attribute10,
861 attribute11 = p_query_string_rec.attribute11,
862 attribute12 = p_query_string_rec.attribute12,
863 attribute13 = p_query_string_rec.attribute13,
864 attribute14 = p_query_string_rec.attribute14,
865 attribute15 = p_query_string_rec.attribute15
866 WHERE query_string_id = p_query_string_rec.query_string_id;
867 -- Success Message
868 -- ENDof API body
869
870 /* Vertical Post Processing section - mandatory */
871 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'V' ) ) THEN
872 ast_WEBASSIST_VUHK.Update_WebAssist_Post(p_api_version => l_api_version,
873 x_return_status => l_return_status,
874 x_msg_count => l_msg_count,
875 x_msg_data => l_msg_data,
876 p_assist_rec => l_assist_rec,
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;
886
887 END IF;
888
889 /* Customer Post Processing section - mandatory */
890 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'C' ) ) THEN
891 ast_WEBASSIST_CUHK.Update_WebAssist_Post (p_api_version => l_api_version,
892 x_return_status => l_return_status,
893 x_msg_count => l_msg_count,
894 x_msg_data => l_msg_data,
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;
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.
912 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
913 p_data => x_msg_data );
914
915
916 --
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
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
934 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
935 p_data => x_msg_data );
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);
945 END IF;
946
947 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
948 p_data => x_msg_data );
949
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,
959 x_msg_count OUT NUMBER,
960 x_msg_data OUT VARCHAR2,
961 p_assist_rec assist_rec_type,
962 p_web_assist_rec web_assist_rec_type,
963 p_web_search_rec web_search_rec_type,
964 p_query_string_rec query_string_rec_type
965 )
966 IS
967 l_api_version CONSTANT NUMBER := 1.0;
968 l_api_name CONSTANT VARCHAR2(30) := 'Delete_WebAssist';
969 l_return_status VARCHAR2(1);
970 l_msg_count NUMBER;
971 l_msg_data VARCHAR2(32767);
972
973 l_assist_rec assist_rec_type;
974 l_web_assist_rec web_assist_rec_type;
975 l_web_search_rec web_search_rec_type;
976 l_query_string_rec query_string_rec_type;
977
978 BEGIN
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
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
997 /* Copy all parameters to local variables to be passed to Pre, Post AND Business APIs */
998 /* l_rec - will be used as In Out parameter in pre/post/Business API calls */
999 /* l_return_status - will be a out variable to get return code FROM called APIs */
1000 l_assist_rec := p_assist_rec;
1001 l_web_assist_rec := p_web_assist_rec;
1002 l_web_search_rec := p_web_search_rec;
1003 l_query_string_rec := p_query_string_rec;
1004
1005 /* Customer pre -processing section - Mandatory */
1006 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'C' ) ) THEN
1007 ast_WEBASSIST_CUHK.Delete_WebAssist_PRE(p_api_version => l_api_version,
1008 x_return_status => l_return_status,
1009 x_msg_count => l_msg_count,
1010 x_msg_data => l_msg_data,
1011 p_assist_rec => l_assist_rec,
1012 p_web_assist_rec => l_web_assist_rec,
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;
1022 END IF;
1023
1024
1025 /* Verticle industry pre- processing section - mandatory */
1026 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'V' ) ) THEN
1027 ast_WEBASSIST_VUHK.Delete_WebAssist_PRE(p_api_version => l_api_version,
1028 x_return_status => l_return_status,
1029 x_msg_count => l_msg_count,
1030 x_msg_data => l_msg_data,
1031 p_assist_rec => l_assist_rec,
1032 p_web_assist_rec => l_web_assist_rec,
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;
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
1050 WHERE query_string_id = p_query_string_rec.query_string_id;
1051 END IF;
1052
1053 IF p_web_assist_rec.web_assist_id IS NOT NULL THEN
1054 DELETE FROM ast_web_searches
1055 WHERE search_id = p_web_search_rec.search_id;
1056 END IF;
1057
1058 IF p_web_assist_rec.web_assist_id IS NOT NULL THEN
1059 DELETE FROM ast_web_assists
1060 WHERE web_assist_id = p_web_assist_rec.web_assist_id;
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
1070 /* Vertical Post Processing section - mandatory */
1071 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'V' ) ) THEN
1072 ast_WEBASSIST_VUHK.Delete_WebAssist_POST(p_api_version => l_api_version,
1073 x_return_status => l_return_status,
1074 x_msg_count => l_msg_count,
1075 x_msg_data => l_msg_data,
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;
1085
1086 END IF;
1087
1088 /* Customer Post Processing section - mandatory */
1089 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'C' ) ) THEN
1090 ast_WEBASSIST_CUHK.Delete_WebAssist_POST (p_api_version => l_api_version,
1091 x_return_status => l_return_status,
1092 x_msg_count => l_msg_count,
1093 x_msg_data => l_msg_data,
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;
1103 END IF;
1104
1105 -- Standard call to get message count AND IF count is 1, get message info.
1106 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1107 p_data => x_msg_data );
1108
1109
1110 --
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 );
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;
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;
1132 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1133 p_data => x_msg_data );
1134 END;
1135 END ast_WebAssist_PVT;