[Home] [Help]
PACKAGE BODY: APPS.AST_WEBSWITCH_PVT
Source
1 package body ast_WebSwitch_PVT as
2 /* $Header: astvwbsb.pls 115.4 2002/02/06 11:21:30 pkm ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) :='AST_WEBSWITCH_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) :='astvwbsw.pls';
6
7 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
8 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
9 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
10 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
11 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
12
13 PROCEDURE Create_WebSwitch(
14 p_api_version IN NUMBER,
15 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
16 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
17 p_validation_level IN NUMBER DEFAULT
18 FND_API.G_VALID_LEVEL_FULL,
19 x_return_status OUT VARCHAR2,
20 x_msg_count OUT NUMBER,
21 x_msg_data OUT VARCHAR2,
22 p_cgi_switch_rec IN cgi_switch_rec_type,
23 p_switch_data_rec IN switch_data_rec_type
24 )
25 AS
26 l_api_version CONSTANT NUMBER := 1.0;
27 l_api_name CONSTANT VARCHAR2(30) := 'Create_WebSearch';
28 l_return_status VARCHAR2(1);
29 l_msg_count NUMBER;
30 l_msg_data VARCHAR2(32767);
31
32 CURSOR c1 IS SELECT 'X' FROM ast_cgi_switches
33 WHERE cgi_switch_id = p_cgi_switch_rec.cgi_switch_id;
34
35 CURSOR c2 IS SELECT 'X' FROM ast_switch_data
36 WHERE switch_data_id = p_switch_data_rec.switch_data_id;
37
38 l_dummy char(1);
39
40 -- added these for user hooks
41 l_cgi_switch_rec cgi_switch_rec_type;
42 l_switch_data_rec switch_data_rec_type;
43 begin
44 -- Standard begin of API savepoint
45 SAVEPOINT Create_WebSwitch_PUB;
46
47 -- Standard call to check for call compatibility.
48 IF NOT FND_API.Compatible_API_Call (l_api_version,
49 p_api_version,
50 l_api_name,
51 G_PKG_NAME)
52 THEN
53 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
54 END IF;
55
56 -- Check p_init_msg_list
57 IF FND_API.to_Boolean( p_init_msg_list ) THEN
58 FND_MSG_PUB.initialize;
59 END IF;
60
61 -- Implementation of User Hooks
62 /* Copy all parameters to local variables to be passed to Pre, Post and Business APIs */
63 /* l_rec - will be used as In Out parameter in pre/post/Business API calls */
64 /* l_return_status - will be a out variable to get return code from called APIs */
65 l_cgi_switch_rec := p_cgi_switch_rec;
66 l_switch_data_rec := p_switch_data_rec;
67
68 /* Customer pre -processing section - Mandatory */
69 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'C' ) ) THEN
70 ast_WEBSWITCH_CUHK.Create_WebSwitch_PRE(
71 p_api_version => l_api_version,
72 x_return_status => l_return_status,
73 x_msg_count => l_msg_count,
74 x_msg_data => l_msg_data,
75 p_cgi_switch_rec => l_cgi_switch_rec,
76 p_switch_data_rec => l_switch_data_rec);
77
78 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
79 RAISE FND_API.G_EXC_ERROR;
80 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
81 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
82 END IF;
83 END IF;
84
85
86 /* Verticle industry pre- processing section - mandatory */
87 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'V' ) ) THEN
88 ast_WEBSWITCH_VUHK.Create_WebSwitch_PRE(
89 p_api_version => l_api_version,
90 x_return_status => l_return_status,
91 x_msg_count => l_msg_count,
92 x_msg_data => l_msg_data,
93 p_cgi_switch_rec => l_cgi_switch_rec,
94 p_switch_data_rec => l_switch_data_rec);
95
96 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
97 RAISE FND_API.G_EXC_ERROR;
98 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
99 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
100 END IF;
101 END IF;
102
103 -- Initialize API return status to success
104 x_return_status := FND_API.G_RET_STS_SUCCESS;
105
106 -- API body
107 OPEN c1;
108 FETCH c1 INTO l_dummy;
109 IF c1%notfound THEN
110
111 INSERT INTO ast_cgi_switches(
112 cgi_switch_id,
113 query_string_id,
114 enabled_flag,
115 object_version_number,
116 switch_code,
117 switch_type,
118 is_required_yn,
119 sort_order,
120 data_separator,
121 last_update_date,
122 creation_date,
123 created_by,
124 last_updated_by,
125 last_update_login,
126 attribute_category,
127 attribute1,
128 attribute2,
129 attribute3,
130 attribute4,
131 attribute5,
132 attribute6,
133 attribute7,
134 attribute8,
135 attribute9,
136 attribute10,
137 attribute11,
138 attribute12,
139 attribute13,
140 attribute14,
141 attribute15
142 )
143 VALUES (
144 p_cgi_switch_rec.cgi_switch_id,
145 p_cgi_switch_rec.query_string_id,
146 p_cgi_switch_rec.enabled_flag,
147 p_cgi_switch_rec.object_version_number,
148 p_cgi_switch_rec.switch_code,
149 p_cgi_switch_rec.switch_type,
150 p_cgi_switch_rec.is_required_yn,
151 p_cgi_switch_rec.sort_order,
152 p_cgi_switch_rec.data_separator,
153 p_cgi_switch_rec.last_update_date,
154 p_cgi_switch_rec.creation_date,
155 p_cgi_switch_rec.created_by,
156 p_cgi_switch_rec.last_updated_by,
157 p_cgi_switch_rec.last_update_login,
158 p_cgi_switch_rec.attribute_category,
159 p_cgi_switch_rec.attribute1,
160 p_cgi_switch_rec.attribute2,
161 p_cgi_switch_rec.attribute3,
162 p_cgi_switch_rec.attribute4,
163 p_cgi_switch_rec.attribute5,
164 p_cgi_switch_rec.attribute6,
165 p_cgi_switch_rec.attribute7,
166 p_cgi_switch_rec.attribute8,
167 p_cgi_switch_rec.attribute9,
168 p_cgi_switch_rec.attribute10,
169 p_cgi_switch_rec.attribute11,
170 p_cgi_switch_rec.attribute12,
171 p_cgi_switch_rec.attribute13,
172 p_cgi_switch_rec.attribute14,
173 p_cgi_switch_rec.attribute15
174 );
175 END IF;
176 CLOSE c1;
177
178 OPEN c2;
179 FETCH c2 INTO l_dummy;
180 IF c2%notfound THEN
181 INSERT INTO ast_switch_data(
182 switch_data_id,
183 cgi_switch_id,
184 enabled_flag,
185 object_version_number,
186 first_name_yn,
187 last_name_yn,
188 address_yn,
189 city_yn,
190 state_yn,
191 zip_yn,
192 country_yn,
193 last_update_date,
194 creation_date,
195 created_by,
196 last_updated_by,
197 last_update_login,
198 attribute_category,
199 attribute1,
200 attribute2,
201 attribute3,
202 attribute4,
203 attribute5,
204 attribute6,
205 attribute7,
206 attribute8,
207 attribute9,
208 attribute10,
209 attribute11,
210 attribute12,
211 attribute13,
212 attribute14,
213 attribute15
214 )
215 VALUES (
216 p_switch_data_rec.switch_data_id,
217 p_switch_data_rec.cgi_switch_id,
218 p_switch_data_rec.enabled_flag,
219 p_switch_data_rec.object_version_number,
220 p_switch_data_rec.first_name_yn,
221 p_switch_data_rec.last_name_yn,
222 p_switch_data_rec.address_yn,
223 p_switch_data_rec.city_yn,
224 p_switch_data_rec.state_yn,
225 p_switch_data_rec.zip_yn,
226 p_switch_data_rec.country_yn,
227 p_switch_data_rec.last_update_date,
228 p_switch_data_rec.creation_date,
229 p_switch_data_rec.created_by,
230 p_switch_data_rec.last_updated_by,
231 p_switch_data_rec.last_update_login,
232 p_switch_data_rec.attribute_category,
233 p_switch_data_rec.attribute1,
234 p_switch_data_rec.attribute2,
235 p_switch_data_rec.attribute3,
236 p_switch_data_rec.attribute4,
237 p_switch_data_rec.attribute5,
238 p_switch_data_rec.attribute6,
239 p_switch_data_rec.attribute7,
240 p_switch_data_rec.attribute8,
241 p_switch_data_rec.attribute9,
242 p_switch_data_rec.attribute10,
243 p_switch_data_rec.attribute11,
244 p_switch_data_rec.attribute12,
245 p_switch_data_rec.attribute13,
246 p_switch_data_rec.attribute14,
247 p_switch_data_rec.attribute15
248 );
249 END IF;
250 CLOSE c2;
251
252
253 -- Success Message
254 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
255 THEN
256 FND_MESSAGE.Set_Name('ast', 'API_ROWS_UPDATED');
257 FND_MESSAGE.Set_Token('ROW', 'ast_QUERY_STRING', TRUE);
258 FND_MESSAGE.Set_Token('NUMBER', 1, FALSE);
259 FND_MSG_PUB.Add;
260 END IF;
261
262
263 -- Success Message
264 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) and
265 x_return_status = FND_API.G_RET_STS_SUCCESS
266 THEN
267 FND_MESSAGE.Set_Name('ast', 'API_SUCCESS');
268 FND_MESSAGE.Set_Token('ROW', 'ast_QUERY_STRING', TRUE);
269 FND_MSG_PUB.Add;
270 END IF;
271 -- END of API body
272
273 /* Vertical Post Processing section - mandatory */
274 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'V' ) ) THEN
275 ast_WEBSWITCH_VUHK.Create_WebSwitch_Post(
276 p_api_version => l_api_version,
277 x_return_status => l_return_status,
278 x_msg_count => l_msg_count,
279 x_msg_data => l_msg_data,
280 p_cgi_switch_rec => l_cgi_switch_rec,
281 p_switch_data_rec => l_switch_data_rec);
282 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
283 RAISE FND_API.G_EXC_ERROR;
284 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
286 END IF;
287
288 END IF;
289
290 /* Customer Post Processing section - mandatory */
291 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'C' ) ) THEN
292 ast_WEBSWITCH_CUHK.Create_WebSwitch_Post(
293 p_api_version => l_api_version,
294 x_return_status => l_return_status,
295 x_msg_count => l_msg_count,
296 x_msg_data => l_msg_data,
297 p_cgi_switch_rec => l_cgi_switch_rec,
298 p_switch_data_rec => l_switch_data_rec);
299 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
300 RAISE FND_API.G_EXC_ERROR;
301 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
302 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
303 END IF;
304 END IF;
305
306 -- Standard check of p_commit.
307 IF FND_API.To_Boolean ( p_commit ) THEN
308 COMMIT WORK;
309 END IF;
310
311 -- Debug Message
312 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
313 THEN
314 FND_MESSAGE.Set_Name('ast', 'Pvt WebAssist API: End');
315 FND_MSG_PUB.Add;
316 END IF;
317
318 -- Standard call to get message count and IF count is 1, get message info.
319 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
320 p_data => x_msg_data );
321
322
323 --
324 -- Normal API Exception handling, IF exception occurs outside of phone processing loop
325 --
326 EXCEPTION
327
328 WHEN FND_API.G_EXC_ERROR THEN
329
330 ROLLBACK TO Create_WebSwitch_PUB;
331 x_return_status := FND_API.G_RET_STS_ERROR ;
332
333 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
334 p_data => x_msg_data );
335
336 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
337
338 ROLLBACK TO Create_WebSwitch_PUB;
339 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
340
341 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
342 p_data => x_msg_data );
343
344 WHEN OTHERS THEN
345
346 ROLLBACK TO Create_WebSwitch_PUB;
347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
348
349 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
350 THEN
351 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
352 END IF;
353
354 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
355 p_data => x_msg_data );
356
357 END;
358
359 PROCEDURE Update_WebSwitch(
360 p_api_version IN NUMBER,
361 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
362 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
363 p_validation_level IN NUMBER DEFAULT
364 FND_API.G_VALID_LEVEL_FULL,
365 x_return_status OUT VARCHAR2,
366 x_msg_count OUT NUMBER,
367 x_msg_data OUT VARCHAR2,
368 p_cgi_switch_rec IN cgi_switch_rec_type,
369 p_switch_data_rec IN switch_data_rec_type
370 )
371 AS
372 l_api_version CONSTANT NUMBER := 1.0;
373 l_api_name CONSTANT VARCHAR2(30) := 'Update_WebSwitch';
374 l_return_status VARCHAR2(1);
375 l_msg_count NUMBER;
376 l_msg_data VARCHAR2(32767);
377 -- added these for user hooks
378 l_cgi_switch_rec cgi_switch_rec_type;
379 l_switch_data_rec switch_data_rec_type;
380
381 begin
382 -- Standard begin of API savepoint
383 SAVEPOINT Update_WebSwitch_PVT;
384
385 -- Standard call to check for call compatibility.
386 IF NOT FND_API.Compatible_API_Call (l_api_version,
387 p_api_version,
388 l_api_name,
389 G_PKG_NAME)
390 THEN
391 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
392 END IF;
393
394 -- Check p_init_msg_list
395 IF FND_API.to_Boolean( p_init_msg_list ) THEN
396 FND_MSG_PUB.initialize;
397 END IF;
398
399 -- Implementation of User Hooks
400 /* Copy all parameters to local variables to be passed to Pre, Post and Business APIs */
401 /* l_rec - will be used as In Out parameter in pre/post/Business API calls */
402 /* l_return_status - will be a out variable to get return code from called APIs */
403 l_cgi_switch_rec := p_cgi_switch_rec;
404 l_switch_data_rec := p_switch_data_rec;
405
406 /* Customer pre -processing section - Mandatory */
407 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'C' ) ) THEN
408 ast_WEBSWITCH_CUHK.Update_WebSwitch_PRE(
409 p_api_version => l_api_version,
410 x_return_status => l_return_status,
411 x_msg_count => l_msg_count,
412 x_msg_data => l_msg_data,
413 p_cgi_switch_rec => l_cgi_switch_rec,
414 p_switch_data_rec => l_switch_data_rec);
415
416 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
417 RAISE FND_API.G_EXC_ERROR;
418 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
419 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
420 END IF;
421 END IF;
422
423
424 /* Verticle industry pre- processing section - mandatory */
425 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'V' ) ) THEN
426 ast_WEBSWITCH_VUHK.Update_WebSwitch_PRE(
427 p_api_version => l_api_version,
428 x_return_status => l_return_status,
429 x_msg_count => l_msg_count,
430 x_msg_data => l_msg_data,
431 p_cgi_switch_rec => l_cgi_switch_rec,
432 p_switch_data_rec => l_switch_data_rec);
433
434 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
435 RAISE FND_API.G_EXC_ERROR;
436 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
437 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
438 END IF;
439 END IF;
440
441
442 -- Initialize API return status to success
443 x_return_status := FND_API.G_RET_STS_SUCCESS;
444
445 -- API body
446 UPDATE ast_cgi_switches
447 SET
448 switch_code = p_cgi_switch_rec.switch_code,
449 switch_type = p_cgi_switch_rec.switch_type,
450 enabled_flag = p_cgi_switch_rec.enabled_flag,
451 is_required_yn = p_cgi_switch_rec.is_required_yn,
452 sort_order = p_cgi_switch_rec.sort_order,
453 data_separator = p_cgi_switch_rec.data_separator,
454 last_update_date = p_cgi_switch_rec.last_update_date,
455 last_updated_by = p_cgi_switch_rec.last_updated_by,
456 last_update_login = p_cgi_switch_rec.last_update_login,
457 attribute_category = p_cgi_switch_rec.attribute_category,
458 attribute1 = p_cgi_switch_rec.attribute1,
459 attribute2 = p_cgi_switch_rec.attribute2,
460 attribute3 = p_cgi_switch_rec.attribute3,
461 attribute4 = p_cgi_switch_rec.attribute4,
462 attribute5 = p_cgi_switch_rec.attribute5,
463 attribute6 = p_cgi_switch_rec.attribute6,
464 attribute7 = p_cgi_switch_rec.attribute7,
465 attribute8 = p_cgi_switch_rec.attribute8,
466 attribute9 = p_cgi_switch_rec.attribute9,
467 attribute10 = p_cgi_switch_rec.attribute10,
468 attribute11 = p_cgi_switch_rec.attribute11,
469 attribute12 = p_cgi_switch_rec.attribute12,
470 attribute13 = p_cgi_switch_rec.attribute13,
471 attribute14 = p_cgi_switch_rec.attribute14,
472 attribute15 = p_cgi_switch_rec.attribute15
473 WHERE query_string_id = p_cgi_switch_rec.query_string_id
474 AND cgi_switch_id = p_cgi_switch_rec.cgi_switch_id;
475
476 UPDATE ast_switch_data
477 SET
478 first_name_yn = p_switch_data_rec.first_name_yn,
479 last_name_yn = p_switch_data_rec.last_name_yn,
480 address_yn = p_switch_data_rec.address_yn,
481 city_yn = p_switch_data_rec.city_yn,
482 state_yn = p_switch_data_rec.state_yn,
483 zip_yn = p_switch_data_rec.zip_yn,
484 country_yn = p_switch_data_rec.country_yn,
485 enabled_flag = p_switch_data_rec.enabled_flag,
486 last_update_date = p_switch_data_rec.last_update_date,
487 last_updated_by = p_switch_data_rec.last_updated_by,
488 last_update_login = p_switch_data_rec.last_update_login,
489 attribute_category = p_switch_data_rec.attribute_category,
490 attribute1 = p_switch_data_rec.attribute1,
491 attribute2 = p_switch_data_rec.attribute2,
492 attribute3 = p_switch_data_rec.attribute3,
493 attribute4 = p_switch_data_rec.attribute4,
494 attribute5 = p_switch_data_rec.attribute5,
495 attribute6 = p_switch_data_rec.attribute6,
496 attribute7 = p_switch_data_rec.attribute7,
497 attribute8 = p_switch_data_rec.attribute8,
498 attribute9 = p_switch_data_rec.attribute9,
499 attribute10 = p_switch_data_rec.attribute10,
500 attribute11 = p_switch_data_rec.attribute11,
501 attribute12 = p_switch_data_rec.attribute12,
502 attribute13 = p_switch_data_rec.attribute13,
503 attribute14 = p_switch_data_rec.attribute14,
504 attribute15 = p_switch_data_rec.attribute15
505 WHERE cgi_switch_id = p_switch_data_rec.cgi_switch_id
506 AND switch_data_id = p_switch_data_rec.switch_data_id;
507
508
509 -- Success Message
510 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
511 THEN
512 FND_MESSAGE.Set_Name('ast', 'API_ROWS_UPDATED');
513 FND_MESSAGE.Set_Token('ROW', 'ast_QUERY_STRING', TRUE);
514 FND_MESSAGE.Set_Token('NUMBER', 1, FALSE);
515 FND_MSG_PUB.Add;
516 END IF;
517
518
519 -- Success Message
520 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) and
521 x_return_status = FND_API.G_RET_STS_SUCCESS
522 THEN
523 FND_MESSAGE.Set_Name('ast', 'API_SUCCESS');
524 FND_MESSAGE.Set_Token('ROW', 'ast_QUERY_STRING', TRUE);
525 FND_MSG_PUB.Add;
526 END IF;
527 -- END of API body
528
529
530 /* Vertical Post Processing section - mandatory */
531 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'V' ) ) THEN
532 ast_WEBSWITCH_VUHK.Update_WebSwitch_Post(
533 p_api_version => l_api_version,
534 x_return_status => l_return_status,
535 x_msg_count => l_msg_count,
536 x_msg_data => l_msg_data,
537 p_cgi_switch_rec => l_cgi_switch_rec,
538 p_switch_data_rec => l_switch_data_rec);
539 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
540 RAISE FND_API.G_EXC_ERROR;
541 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
542 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
543 END IF;
544
545 END IF;
546
547 /* Customer Post Processing section - mandatory */
548 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'C' ) ) THEN
549 ast_WEBSWITCH_CUHK.Update_WebSwitch_Post(
550 p_api_version => l_api_version,
551 x_return_status => l_return_status,
552 x_msg_count => l_msg_count,
553 x_msg_data => l_msg_data,
554 p_cgi_switch_rec => l_cgi_switch_rec,
555 p_switch_data_rec => l_switch_data_rec);
556 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
557 RAISE FND_API.G_EXC_ERROR;
558 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
560 END IF;
561 END IF;
562
563 -- Standard check of p_commit.
564 IF FND_API.To_Boolean ( p_commit ) THEN
565 COMMIT WORK;
566 END IF;
567
568 -- Debug Message
569 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
570 THEN
571 FND_MESSAGE.Set_Name('ast', 'Pvt WebAssist API: End');
572 FND_MSG_PUB.Add;
573 END IF;
574
575 -- Standard call to get message count and IF count is 1, get message info.
576 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
577 p_data => x_msg_data );
578
579
580 --
581 -- Normal API Exception handling, IF exception occurs outside of phone processing loop
582 --
583 EXCEPTION
584
585 WHEN FND_API.G_EXC_ERROR THEN
586
587 ROLLBACK TO Update_WebSwitch_PVT;
588 x_return_status := FND_API.G_RET_STS_ERROR ;
589
590 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
591 p_data => x_msg_data );
592
593 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
594
595 ROLLBACK TO Update_WebSwitch_PVT;
596 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
597
598 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
599 p_data => x_msg_data );
600
601 WHEN OTHERS THEN
602
603 ROLLBACK TO Update_WebSwitch_PVT;
604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
605
606 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
607 THEN
608 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
609 END IF;
610
611 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
612 p_data => x_msg_data );
613
614 END;
615
616 PROCEDURE Lock_WebSwitch( p_api_version IN NUMBER,
617 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
618 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
619 p_validation_level IN NUMBER DEFAULT
620 FND_API.G_VALID_LEVEL_FULL,
621 x_return_status OUT VARCHAR2,
622 x_msg_count OUT NUMBER,
623 x_msg_data OUT VARCHAR2,
624 p_cgi_switch_rec IN cgi_switch_rec_type,
625 p_switch_data_rec IN switch_data_rec_type
626 )
627 AS
628 l_api_version CONSTANT NUMBER := 1.0;
629 l_api_name CONSTANT VARCHAR2(30) := 'Lock_WebSwitch';
630 l_return_status VARCHAR2(1);
631 l_msg_count NUMBER;
632 l_msg_data VARCHAR2(32767);
633
634 -- added these for user hookes
635 l_cgi_switch_rec cgi_switch_rec_type;
636 l_switch_data_rec switch_data_rec_type;
637
638 begin
639 -- Standard begin of API savepoint
640 SAVEPOINT Lock_WebSwitch_PVT;
641 -- Standard call to check for call compatibility.
642 IF NOT FND_API.Compatible_API_Call (l_api_version,
643 p_api_version,
644 l_api_name,
645 G_PKG_NAME)
646 THEN
647 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
648 END IF;
649
650 -- Check p_init_msg_list
651 IF FND_API.to_Boolean( p_init_msg_list ) THEN
652 FND_MSG_PUB.initialize;
653 END IF;
654
655 -- Implementation of User Hooks
656 /* Copy all parameters to local variables to be passed to Pre, Post and Business APIs */
657 /* l_rec - will be used as In Out parameter in pre/post/Business API calls */
658 /* l_return_status - will be a out variable to get return code from called APIs */
659 l_cgi_switch_rec := p_cgi_switch_rec;
660 l_switch_data_rec := p_switch_data_rec;
661
662 /* Customer pre -processing section - Mandatory */
663 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'C' ) ) THEN
664 ast_WEBSWITCH_CUHK.Lock_WebSwitch_PRE(
665 p_api_version => l_api_version,
666 x_return_status => l_return_status,
667 x_msg_count => l_msg_count,
668 x_msg_data => l_msg_data,
669 p_cgi_switch_rec => l_cgi_switch_rec,
670 p_switch_data_rec => l_switch_data_rec);
671
672 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
673 RAISE FND_API.G_EXC_ERROR;
674 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
675 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
676 END IF;
677 END IF;
678
679
680 /* Verticle industry pre- processing section - mandatory */
681 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'V' ) ) THEN
682 ast_WEBSWITCH_VUHK.Lock_WebSwitch_PRE(
683 p_api_version => l_api_version,
684 x_return_status => l_return_status,
685 x_msg_count => l_msg_count,
686 x_msg_data => l_msg_data,
687 p_cgi_switch_rec => l_cgi_switch_rec,
688 p_switch_data_rec => l_switch_data_rec);
689 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
690 RAISE FND_API.G_EXC_ERROR;
691 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
692 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
693 END IF;
694 END IF;
695
696 -- Initialize API return status to success
697 x_return_status := FND_API.G_RET_STS_SUCCESS;
698
699 -- API body
700 DECLARE
701 v_dummy CHAR(1);
702 CURSOR c1 IS SELECT 'X'
703 FROM ast_cgi_switches
704 WHERE cgi_switch_id = p_cgi_switch_rec.cgi_switch_id
705 FOR UPDATE;
706 BEGIN
707 OPEN c1;
708 FETCH c1 INTO v_dummy;
709 CLOSE c1;
710 END;
711
712 DECLARE
713 v_dummy Char(1);
714 CURSOR c1 IS SELECT 'X'
715 FROM ast_switch_data
716 WHERE switch_data_id = p_switch_data_rec.switch_data_id
717 FOR UPDATE;
718 BEGIN
719 OPEN c1;
720 FETCH c1 INTO v_dummy;
721 CLOSE c1;
722 END;
723
724
725 -- Success Message
726 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
727 THEN
728 FND_MESSAGE.Set_Name('ast', 'API_ROWS_UPDATED');
729 FND_MESSAGE.Set_Token('ROW', 'ast_QUERY_STRING', TRUE);
730 FND_MESSAGE.Set_Token('NUMBER', 1, FALSE);
731 FND_MSG_PUB.Add;
732 END IF;
733
734 /* Vertical Post Processing section - mandatory */
735 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'V' ) ) THEN
736 ast_WEBSWITCH_VUHK.Lock_WebSwitch_POST(
737 p_api_version => l_api_version,
738 x_return_status => l_return_status,
739 x_msg_count => l_msg_count,
740 x_msg_data => l_msg_data,
741 p_cgi_switch_rec => l_cgi_switch_rec,
742 p_switch_data_rec => l_switch_data_rec);
743
744 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
745 RAISE FND_API.G_EXC_ERROR;
746 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
747 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
748 END IF;
749
750 END IF;
751
752 /* Customer Post Processing section - mandatory */
753 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'C' ) ) THEN
754 ast_WEBSWITCH_CUHK.Lock_WebSwitch_POST (
755 p_api_version => l_api_version,
756 x_return_status => l_return_status,
757 x_msg_count => l_msg_count,
758 x_msg_data => l_msg_data,
759 p_cgi_switch_rec => l_cgi_switch_rec,
760 p_switch_data_rec => l_switch_data_rec);
761
762 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
763 RAISE FND_API.G_EXC_ERROR;
764 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
766 END IF;
767 END IF;
768
769 -- Success Message
770 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) and
771 x_return_status = FND_API.G_RET_STS_SUCCESS
772 THEN
773 FND_MESSAGE.Set_Name('ast', 'API_SUCCESS');
774 FND_MESSAGE.Set_Token('ROW', 'ast_QUERY_STRING', TRUE);
775 FND_MSG_PUB.Add;
776 END IF;
777 -- END of API body
778
779
780
781 -- Standard check of p_commit.
782 IF FND_API.To_Boolean ( p_commit ) THEN
783 COMMIT WORK;
784 END IF;
785
786 -- Debug Message
787 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
788 THEN
789 FND_MESSAGE.Set_Name('ast', 'Pvt WebAssist API: End');
790 FND_MSG_PUB.Add;
791 END IF;
792
793 -- Standard call to get message count and IF count is 1, get message info.
794 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
795 p_data => x_msg_data );
796
797
798 --
799 -- Normal API Exception handling, IF exception occurs outside of phone processing loop
800 --
801 EXCEPTION
802
803 WHEN FND_API.G_EXC_ERROR THEN
804
805 ROLLBACK TO Lock_WebSwitch_PVT;
806 x_return_status := FND_API.G_RET_STS_ERROR ;
807
808 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
809 p_data => x_msg_data );
810
811 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
812
813 ROLLBACK TO Lock_WebSwitch_PVT;
814 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
815
816 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
817 p_data => x_msg_data );
818
819 WHEN OTHERS THEN
820
821 ROLLBACK TO Lock_WebSwitch_PVT;
822 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
823
824 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
825 THEN
826 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
827 END IF;
828
829 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
830 p_data => x_msg_data );
831
832 END;
833
834 PROCEDURE Delete_WebSwitch(
835 p_api_version IN NUMBER,
836 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
837 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
838 p_validation_level IN NUMBER DEFAULT
839 FND_API.G_VALID_LEVEL_FULL,
840 x_return_status OUT VARCHAR2,
841 x_msg_count OUT NUMBER,
842 x_msg_data OUT VARCHAR2,
843 p_cgi_switch_rec IN cgi_switch_rec_type,
844 p_switch_data_rec IN switch_data_rec_type
845 )
846 AS
847 l_api_version CONSTANT NUMBER := 1.0;
848 l_api_name CONSTANT VARCHAR2(30) := 'Delete_WebSwitch';
849 l_return_status VARCHAR2(1);
850 l_msg_count NUMBER;
851 l_msg_data VARCHAR2(32767);
852
853 -- added these for user hookes
854 l_cgi_switch_rec cgi_switch_rec_type;
855 l_switch_data_rec switch_data_rec_type;
856
857 begin
858 -- Standard begin of API savepoint
859 SAVEPOINT Delete_WebSwitch_PUB;
860
861 -- Standard call to check for call compatibility.
862 IF NOT FND_API.Compatible_API_Call (l_api_version,
863 p_api_version,
864 l_api_name,
865 G_PKG_NAME)
866 THEN
867 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
868 END IF;
869 -- Check p_init_msg_list
870 IF FND_API.to_Boolean( p_init_msg_list ) THEN
871 FND_MSG_PUB.initialize;
872 END IF;
873
874 -- Implementation of User Hooks
875 /* Copy all parameters to local variables to be passed to Pre, Post and Business APIs */
876 /* l_rec - will be used as In Out parameter in pre/post/Business API calls */
877 /* l_return_status - will be a out variable to get return code from called APIs */
878 l_cgi_switch_rec := p_cgi_switch_rec;
879 l_switch_data_rec := p_switch_data_rec;
880
881 /* Customer pre -processing section - Mandatory */
882 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'C' ) ) THEN
883 ast_WEBSWITCH_CUHK.Delete_WebSwitch_PRE(
884 p_api_version => l_api_version,
885 x_return_status => l_return_status,
886 x_msg_count => l_msg_count,
887 x_msg_data => l_msg_data,
888 p_cgi_switch_rec => l_cgi_switch_rec,
889 p_switch_data_rec => l_switch_data_rec);
890
891 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
892 RAISE FND_API.G_EXC_ERROR;
893 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
894 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
895 END IF;
896 END IF;
897
898
899 /* Verticle industry pre- processing section - mandatory */
900 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'V' ) ) THEN
901 ast_WEBSWITCH_VUHK.Delete_WebSwitch_PRE(
902 p_api_version => l_api_version,
903 x_return_status => l_return_status,
904 x_msg_count => l_msg_count,
905 x_msg_data => l_msg_data,
906 p_cgi_switch_rec => l_cgi_switch_rec,
907 p_switch_data_rec => l_switch_data_rec);
908 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
909 RAISE FND_API.G_EXC_ERROR;
910 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
911 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
912 END IF;
913 END IF;
914
915 -- Initialize API return status to success
916 x_return_status := FND_API.G_RET_STS_SUCCESS;
917
918 -- API body
919 IF p_switch_data_rec.switch_data_id IS NOT NULL THEN
920 DELETE FROM ast_switch_data
921 WHERE switch_data_id = p_switch_data_rec.switch_data_id;
922 END IF;
923
924
925 IF p_cgi_switch_rec.cgi_switch_id IS NOT NULL THEN
926 DELETE FROM ast_cgi_switches
927 WHERE cgi_switch_id = p_cgi_switch_rec.cgi_switch_id;
928 END IF;
929
930
931 -- Success Message
932 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
933 THEN
934 FND_MESSAGE.Set_Name('ast', 'API_ROWS_UPDATED');
935 FND_MESSAGE.Set_Token('ROW', 'ast_QUERY_STRING', TRUE);
936 FND_MESSAGE.Set_Token('NUMBER', 1, FALSE);
937 FND_MSG_PUB.Add;
938 END IF;
939
940
941 -- Success Message
942 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) and
943 x_return_status = FND_API.G_RET_STS_SUCCESS
944 THEN
945 FND_MESSAGE.Set_Name('ast', 'API_SUCCESS');
946 FND_MESSAGE.Set_Token('ROW', 'ast_QUERY_STRING', TRUE);
947 FND_MSG_PUB.Add;
948 END IF;
949 -- END of API body
950
951 /* Vertical Post Processing section - mandatory */
952 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'V' ) ) THEN
953 ast_WEBSWITCH_VUHK.Delete_WebSwitch_POST(
954 p_api_version => l_api_version,
955 x_return_status => l_return_status,
956 x_msg_count => l_msg_count,
957 x_msg_data => l_msg_data,
958 p_cgi_switch_rec => l_cgi_switch_rec,
959 p_switch_data_rec => l_switch_data_rec);
960 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
961 RAISE FND_API.G_EXC_ERROR;
962 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
963 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
964 END IF;
965
966 END IF;
967
968 /* Customer Post Processing section - mandatory */
969 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'C' ) ) THEN
970 ast_WEBSWITCH_CUHK.Delete_WebSwitch_POST (
971 p_api_version => l_api_version,
972 x_return_status => l_return_status,
973 x_msg_count => l_msg_count,
974 x_msg_data => l_msg_data,
975 p_cgi_switch_rec => l_cgi_switch_rec,
976 p_switch_data_rec => l_switch_data_rec);
977 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
978 RAISE FND_API.G_EXC_ERROR;
979 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
980 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
981 END IF;
982 END IF;
983
984 -- Standard check of p_commit.
985 IF FND_API.To_Boolean ( p_commit ) THEN
986 COMMIT WORK;
987 END IF;
988
989 -- Debug Message
990 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
991 THEN
992 FND_MESSAGE.Set_Name('ast', 'Pvt WebAssist API: End');
993 FND_MSG_PUB.Add;
994 END IF;
995
996 -- Standard call to get message count and IF count is 1, get message info.
997 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
998 p_data => x_msg_data );
999
1000
1001 --
1002 -- Normal API Exception handling, IF exception occurs outside of phone processing loop
1003 --
1004 EXCEPTION
1005
1006 WHEN FND_API.G_EXC_ERROR THEN
1007
1008 ROLLBACK TO Delete_WebSwitch_PUB;
1009 x_return_status := FND_API.G_RET_STS_ERROR ;
1010
1011 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1012 p_data => x_msg_data );
1013
1014 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1015
1016 ROLLBACK TO Delete_WebSwitch_PUB;
1017 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1018
1019 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1020 p_data => x_msg_data );
1021
1022 WHEN OTHERS THEN
1023
1024 ROLLBACK TO Delete_WebSwitch_PUB;
1025 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1026
1027 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1028 THEN
1029 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1030 END IF;
1031
1032 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1033 p_data => x_msg_data );
1034
1035 END;
1036
1037 END;