[Home] [Help]
PACKAGE BODY: APPS.CCT_AQ_PUB
Source
1 PACKAGE BODY CCT_AQ_PUB AS
2 /* $Header: cctpaqb.pls 115.40 2004/02/04 23:56:39 svinamda noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CCT_AQ_PUB';
5
6 -- return value = 0 => server is up
7 -- return value <> 0 => server is down.
8
9 FUNCTION IS_SERVER_UP (l_server_id IN NUMBER) RETURN NUMBER IS
10 l_serverStatus NUMBER;
11 l_serverLastUpdateDate DATE;
12 l_refreshRate CONSTANT NUMBER := 60000; -- refresh rate is 1 minute for all cct servers.
13
14 BEGIN
15
16 --dbms_output.put_line ('Begin function is_server_up, server_id= ' || l_server_id);
17 SELECT STATUS, LAST_UPDATE_DATE into l_serverStatus, l_serverLastUpdateDate
18 FROM IEO_SVR_RT_INFO
19 WHERE SERVER_ID = l_server_id;
20 -- status 4 = started and ready
21 IF ((l_serverStatus >= 4) and
22 ((( sysdate - l_serverLastUpdateDate )*24*60*60*1000) < l_refreshRate)) THEN
23 --dbms_output.put_line ('End is_server_up, returning 0');
24 return 0;
25 END IF;
26 --dbms_output.put_line ('End is_server_up, returning 1');
27 return 1;
28 END IS_SERVER_UP;
29
30
31
32
33
34
35 PROCEDURE ENQUEUE_ITEM
36 ( p_api_version IN NUMBER ,
37 p_init_msg_list IN VARCHAR2,
38 p_commit IN VARCHAR2 ,
39 p_server_group_name IN VARCHAR2 ,
40 p_app_id IN NUMBER,
41 p_xml_data1 IN VARCHAR2 ,
42 p_media_type IN NUMBER ,
43 p_delay IN NUMBER ,
44 x_return_status OUT NOCOPY VARCHAR2 ,
45 x_msg_count OUT NOCOPY NUMBER ,
46 x_msg_data OUT NOCOPY VARCHAR2
47 )
48 IS
49 l_api_name CONSTANT VARCHAR2(30) := 'ENQUEUE_ITEM';
50 l_api_version CONSTANT NUMBER := 1.0;
51 enqueue_options dbms_aq.enqueue_options_t;
52 message_properties dbms_aq.message_properties_t;
53 message_handle RAW(16);
54 message SYSTEM.cct_ibmedia_type;
55 final_xml_data1 VARCHAR2(2048);
56 server_group_name_null EXCEPTION;
57 server_group_id_null EXCEPTION;
58 xml_data1_null EXCEPTION;
59 p_server_group_id NUMBER;
60
61 BEGIN
62 -- Standard Start of API savepoint
63 SAVEPOINT ENQUEUE_ITEM_PUB;
64 -- Standard call to check for call compatibility.
65 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
66 p_api_version ,
67 l_api_name ,
68 G_PKG_NAME )
69 THEN
70 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
71 END IF;
72 -- Initialize message list if p_init_msg_list is set to TRUE.
73 IF FND_API.To_Boolean( p_init_msg_list ) THEN
74 FND_MSG_PUB.initialize;
75 END IF;
76 -- Initialize API return status to success
77 x_return_status := FND_API.G_RET_STS_SUCCESS;
78 x_msg_data := 'CCT_AQ_SUCCESS';
79 x_msg_count := 0;
80 -- API body
81 IF p_server_group_name IS NULL then
82 raise server_group_name_null;
83 END IF;
84
85 IF p_xml_data1 IS NULL then
86 raise xml_data1_null;
87 END IF;
88
89
90 -- Assemble Data into Object
91
92
93 select server_group_id into p_server_group_id from ieo_svr_groups
94 where group_name = p_server_group_name;
95
96
97 IF p_server_group_id IS NULL then
98 raise server_group_id_null;
99 END IF;
100
101 --dbms_output.put_line ('ENQUEUE_ITEM: ServerGroupId = ' || p_server_group_id);
102 --dbms_output.put_line ('ENQUEUE_ITEM: Xmldata1 = ' || p_xml_data1);
103
104 message_properties.correlation := p_server_group_id;
105
106 IF (p_delay > 0) THEN
107 message_properties.delay := p_delay;
108 END IF;
109
110 final_xml_data1 := p_xml_data1 || ';UUID:' || p_media_type;
111
112 message := SYSTEM.cct_ibmedia_type(SYSDATE,p_server_group_id,p_app_id,final_xml_data1);
113
114 --dbms_output.put_line ('ENQUEUE_ITEM: MessageCreated');
115
116 dbms_aq.enqueue(queue_name => CCT_QUEUE.queue_name,
117 enqueue_options => enqueue_options,
118 message_properties => message_properties,
119 payload => message,
120 msgid => message_handle);
121
122 --dbms_output.put_line ('ENQUEUE_ITEM: Enqueue done');
123
124 -- End of API body.
125 -- Standard check of p_commit.
126 IF FND_API.To_Boolean( p_commit ) THEN
127 COMMIT WORK;
128 END IF;
129
130 EXCEPTION
131
132 WHEN server_group_id_null THEN
133 ROLLBACK TO ENQUEUE_ITEM_PUB;
134 x_msg_data := 'CCT_AQ_SERVER_GROUP_ID_NULL' ;
135 x_return_status := FND_API.G_RET_STS_ERROR;
136 x_msg_count := 1;
137
138 WHEN server_group_name_null THEN
139 ROLLBACK TO ENQUEUE_ITEM_PUB;
140 x_msg_data := 'CCT_AQ_SERVER_GROUP_NAME_NULL';
141 x_return_status := FND_API.G_RET_STS_ERROR;
142 x_msg_count := 1;
143
144 WHEN xml_data1_null THEN
145 ROLLBACK TO ENQUEUE_ITEM_PUB;
146 x_msg_data := 'CCT_AQ_XML_DATA1_NULL';
147 x_return_status := FND_API.G_RET_STS_ERROR;
148 x_msg_count := 1;
149
150 WHEN FND_API.G_EXC_ERROR THEN
151 ROLLBACK TO ENQUEUE_ITEM_PUB;
152 x_msg_data := 'CCT_AQ_ERROR';
153 x_return_status := FND_API.G_RET_STS_ERROR;
154 x_msg_count := 1;
155
156 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
157 ROLLBACK TO ENQUEUE_ITEM_PUB;
158 x_msg_data := 'CCT_AQ_ERROR';
159 x_return_status := FND_API.G_RET_STS_ERROR;
160 x_msg_count := 1;
161
162 WHEN OTHERS THEN
163 ROLLBACK TO ENQUEUE_ITEM_PUB;
164 x_msg_data := 'CCT_AQ_ERROR';
165 x_return_status := FND_API.G_RET_STS_ERROR;
166 x_msg_count := 1;
167
168 END ENQUEUE_ITEM;
169
170 PROCEDURE GET_ROOT_GROUP_NAME
171 ( p_api_version IN NUMBER,
172 p_init_msg_list IN VARCHAR2 ,
173 p_server_group_name IN VARCHAR2,
174 x_return_status OUT NOCOPY VARCHAR2 ,
175 x_msg_count OUT NOCOPY NUMBER ,
176 x_msg_data OUT NOCOPY VARCHAR2 ,
177 x_root_server_group_name OUT NOCOPY VARCHAR2
178 )
179 IS
180 l_api_name CONSTANT VARCHAR2(30) := 'GET_ROOT_GROUP_NAME';
181 l_api_version CONSTANT NUMBER := 1.0;
182 curr_group_group_id NUMBER;
183 curr_server_group_name VARCHAR2(256);
184 server_group_name_null EXCEPTION;
185
186 BEGIN
187
188 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
189 p_api_version ,
190 l_api_name ,
191 G_PKG_NAME )
192 THEN
193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
194 END IF;
195 -- Initialize message list if p_init_msg_list is set to TRUE.
196 IF FND_API.to_Boolean( p_init_msg_list ) THEN
197 FND_MSG_PUB.initialize;
198 END IF;
199 -- Initialize API return status to success
200 -- Initialize API return status to succes
201 x_return_status := FND_API.G_RET_STS_SUCCESS;
202 x_msg_data := 'CCT_AQ_SUCCESS';
203 x_msg_count := 0;
204
205 -- API body
206 IF p_server_group_name IS NULL then
207 raise server_group_name_null;
208 END IF;
209 curr_server_group_name := p_server_group_name;
210 select group_group_id into curr_group_group_id from ieo_svr_groups where group_name = curr_server_group_name;
211 WHILE (true)
212 LOOP
213 select group_name ,
214 group_group_id into curr_server_group_name,curr_group_group_id
215 from ieo_svr_groups
216 where server_group_id = curr_group_group_id;
217
218 END LOOP;
219
220 EXCEPTION
221
222 WHEN NO_DATA_FOUND THEN
223 x_root_server_group_name := curr_server_group_name;
224
225 WHEN server_group_name_null THEN
226 x_msg_data := 'CCT_AQ_SERVER_GROUP_ID_NULL';
227 x_return_status := FND_API.G_RET_STS_ERROR;
228 x_msg_count := 1;
229
230 WHEN FND_API.G_EXC_ERROR THEN
231 x_msg_data := 'CCT_AQ_ERROR';
232 x_return_status := FND_API.G_RET_STS_ERROR;
233 x_msg_count := 1;
234
235 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
236 x_msg_data := 'CCT_AQ_ERROR';
237 x_return_status := FND_API.G_RET_STS_ERROR;
238 x_msg_count := 1;
239
240 WHEN OTHERS THEN
241 x_msg_data := 'CCT_AQ_ERROR';
242 x_return_status := FND_API.G_RET_STS_ERROR;
243 x_msg_count := 1;
244
245 END GET_ROOT_GROUP_NAME;
246
247 PROCEDURE DEQUEUE_ITEM
248 ( p_api_version IN NUMBER ,
249 p_init_msg_list IN VARCHAR2,
250 p_commit IN VARCHAR2,
251 p_server_group_id IN NUMBER ,
252 x_return_status OUT NOCOPY VARCHAR2 ,
253 x_msg_count OUT NOCOPY NUMBER ,
254 x_msg_data OUT NOCOPY VARCHAR2 ,
255 x_server_group_name OUT NOCOPY VARCHAR2 ,
256 x_xml_data1 OUT NOCOPY VARCHAR2
257 )
258 IS
259 l_api_name CONSTANT VARCHAR2(30) := 'DEQUEUE_ITEM';
260 l_api_version CONSTANT NUMBER := 1.0;
261 dequeue_options dbms_aq.dequeue_options_t;
262 message_properties dbms_aq.message_properties_t;
263 message_handle RAW(16);
264 message SYSTEM.cct_ibmedia_type;
265 server_group_id_null EXCEPTION;
266 no_messages exception;
267 pragma exception_init (no_messages, -25228);
268
269 BEGIN
270 -- Standard Start of API savepoint
271 SAVEPOINT DEQUEUE_ITEM_PUB;
272 -- Standard call to check for call compatibility.
273 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
274 p_api_version ,
275 l_api_name ,
276 G_PKG_NAME )
277 THEN
278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
279 END IF;
280 -- Initialize message list if p_init_msg_list is set to TRUE.
281 IF FND_API.to_Boolean( p_init_msg_list ) THEN
282 FND_MSG_PUB.initialize;
283 END IF;
284 -- Initialize API return status to success
285 x_return_status := FND_API.G_RET_STS_SUCCESS;
286 x_msg_data := 'CCT_AQ_SUCCESS';
287 x_msg_count := 0;
288
289 -- API body
290
291 dequeue_options.correlation := p_server_group_id;
292 dequeue_options.wait := 15 ;
293 dequeue_options.visibility := DBMS_AQ.IMMEDIATE;
294 dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE ;
295
296
297 --dbms_output.put_line('DEQUEUE_ITEM: dequeue correlation ' || p_server_group_id);
298
299 dbms_aq.dequeue(
300 queue_name => CCT_QUEUE.queue_name,
301 dequeue_options => dequeue_options,
302 message_properties => message_properties,
303 payload => message,
304 msgid => message_handle);
305 --dbms_output.put_line('DEQUEUE_ITEM: Dequeue done');
306 x_xml_data1 := message.xml_data1;
307 select group_name into x_server_group_name from ieo_svr_groups where
308 server_group_id = message.server_group_id;
309 --dbms_output.put_line('DEQUEUE_ITEM: Select done');
310
311 -- End of API body.
312 -- Standard check of p_commit.
313 IF FND_API.To_Boolean( p_commit ) THEN
314 COMMIT WORK;
315 END IF;
316
317 EXCEPTION
318 WHEN no_messages THEN
319 x_return_status := G_TIMEOUT ;
320 WHEN server_group_id_null THEN
321 ROLLBACK TO DEQUEUE_ITEM_PUB;
322 x_msg_data := 'CCT_AQ_SERVER_GROUP_ID_NULL';
323 x_return_status := FND_API.G_RET_STS_ERROR;
324 x_msg_count := 1;
325 WHEN FND_API.G_EXC_ERROR THEN
326 ROLLBACK TO DEQUEUE_ITEM_PUB;
327 x_msg_data := 'CCT_AQ_ERROR';
328 x_return_status := FND_API.G_RET_STS_ERROR;
329 x_msg_count := 1;
330
331 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
332 --dbms_output.put_line('Unexpected error');
333 ROLLBACK TO DEQUEUE_ITEM_PUB;
334 x_msg_data := 'CCT_AQ_ERROR';
335 x_return_status := FND_API.G_RET_STS_ERROR;
336 x_msg_count := 1;
337
338 WHEN OTHERS THEN
339 ROLLBACK TO DEQUEUE_ITEM_PUB;
340 x_msg_data := 'CCT_AQ_ERROR';
341 x_return_status := FND_API.G_RET_STS_ERROR;
342 x_msg_count := 1;
343
344 END DEQUEUE_ITEM;
345
346
347
348
349 PROCEDURE ENQUEUE_WEB_CALLBACK_ITEM
350 ( p_api_version IN NUMBER,
351 p_init_msg_list IN VARCHAR2 ,
352 p_commit IN VARCHAR2,
353 p_server_group_name IN VARCHAR2,
354 p_app_id IN NUMBER,
355 p_country_code IN NUMBER,
356 p_area_code IN NUMBER,
357 p_phone_number IN NUMBER,
358 p_delay IN NUMBER,
359 p_key1 IN VARCHAR2,
360 p_value1 IN VARCHAR2,
361 p_key2 IN VARCHAR2,
362 p_value2 IN VARCHAR2,
363 p_key3 IN VARCHAR2,
364 p_value3 IN VARCHAR2,
365 p_key4 IN VARCHAR2,
366 p_value4 IN VARCHAR2,
367 p_key5 IN VARCHAR2,
368 p_value5 IN VARCHAR2,
369 p_key6 IN VARCHAR2,
370 p_value6 IN VARCHAR2,
371 p_key7 IN VARCHAR2,
372 p_value7 IN VARCHAR2,
373 p_key8 IN VARCHAR2,
374 p_value8 IN VARCHAR2,
375 p_key9 IN VARCHAR2,
376 p_value9 IN VARCHAR2,
377 p_key10 IN VARCHAR2,
378 p_value10 IN VARCHAR2,
379 x_return_status OUT NOCOPY VARCHAR2,
380 x_msg_count OUT NOCOPY NUMBER,
381 x_msg_data OUT NOCOPY VARCHAR2
382 )
383 IS
384 l_api_name CONSTANT VARCHAR2(30) := 'ENQUEUE_WEB_CALLBACK_ITEM';
385 l_api_version CONSTANT NUMBER := 1.0;
386 l_xml_data1 VARCHAR2(2048) DEFAULT NULL;
387 l_iqdServerId NUMBER;
388 l_isWebCallback VARCHAR2(6) Default 'false';
389 l_return_status varchar2(1);
390 l_msg_count NUMBER;
391 l_msg_data VARCHAR2(256);
392
393 l_key_country_code CONSTANT VARCHAR2(30) := G_KEY_COUNTRY_CODE;
394 l_key_area_code CONSTANT VARCHAR2(30) := G_KEY_AREA_CODE;
395 l_key_phone_number CONSTANT VARCHAR2(30) := G_KEY_PHONE_NUMBER;
396 l_key_local_server_group_name CONSTANT VARCHAR2(30) := G_KEY_LOCAL_SERVER_GROUP_NAME;
397 l_key_root_server_group_name CONSTANT VARCHAR2(30) := G_KEY_SUPER_SERVER_GROUP_NAME;
398 l_root_server_group_name VARCHAR2(256);
399 l_server_group_id NUMBER;
400 l_item_id NUMBER;
401 l_kvp cct_keyvalue_varr := cct_keyvalue_varr();
402 l_put_result varchar2(256);
403
404 server_group_name_null EXCEPTION;
405 country_code_null EXCEPTION;
406 area_code_null EXCEPTION;
407 phone_number_null EXCEPTION;
408 web_callback_false EXCEPTION;
409 server_down EXCEPTION;
410
411
412 BEGIN
413 -- Standard Start of API savepoint
414 SAVEPOINT ENQUEUE_WEB_CALLBACK_ITEM_PUB;
415 -- Standard call to check for call compatibility.
416 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
417 p_api_version ,
418 l_api_name ,
419 G_PKG_NAME )
420 THEN
421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
422 END IF;
423 -- Initialize message list if p_init_msg_list is set to TRUE.
424 IF FND_API.To_Boolean( p_init_msg_list ) THEN
425 FND_MSG_PUB.initialize;
426 END IF;
427 -- Initialize API return status to success
428 x_return_status := FND_API.G_RET_STS_SUCCESS;
429 x_msg_data := 'CCT_AQ_SUCCESS';
430 x_msg_count := 0;
431 -- API body
432
433 IF p_server_group_name IS NULL then
434 raise server_group_name_null;
435 END IF;
436
437 cct_aq_pub.get_root_group_name (
438 p_api_version => 1.0,
442 x_msg_data => x_msg_data,
439 p_server_group_name => p_server_group_name,
440 x_return_status => x_return_status,
441 x_msg_count => x_msg_count,
443 x_root_server_group_name => l_root_server_group_name
444 );
445
446 IF p_server_group_name <> G_BASIC_SDK_GROUP_NAME THEN
447
448 BEGIN
449
450 select server_id into l_iqdServerId from ieo_svr_servers
451 where member_svr_group_id =
452 (select server_group_id from ieo_svr_groups where group_name = l_root_server_group_name)
453 and type_id = 10110 ;
454
455 --dbms_output.put_line('ENQUEUE_WEB_CALLBACK_ITEM: IQD Server id =' || l_iqdServerId );
456 begin
457 select value into l_isWebCallback from ieo_svr_values
458 where param_id = (select param_id from ieo_svr_params
459 where type_id = 10110 and param_name = 'IS_WEB_CALLBACK')
460 and server_id = l_iqdServerId;
461
462 if sql%notfound then
463 l_isWebCallback := 'false';
464 end if;
465 exception when others then null;
466 end;
467
468 --dbms_output.put_line('l_isWebCallback = ' || l_isWebCallback);
469
470 IF UPPER(l_isWebCallback) <> UPPER('true') then
471 raise web_callback_false;
472 END IF;
473
474 END;
475 END IF;
476
477
478
479 IF p_phone_number IS NULL then
480 raise phone_number_null;
481 END IF;
482
483
484 -- Assemble Data into Object
485
486 l_xml_data1 := l_key_phone_number || ':' || p_phone_number ;
487 l_put_result:=CCT_COLLECTION_UTIL_PUB.PUT(l_kvp,l_key_phone_number, p_phone_number );
488
489 l_xml_data1 := l_xml_data1 || ';' || l_key_local_server_group_name || ':' || p_server_group_name;
490 l_put_result:=CCT_COLLECTION_UTIL_PUB.PUT(l_kvp,l_key_local_server_group_name, p_server_group_name);
491
492 l_xml_data1 := l_xml_data1 || ';' || l_key_root_server_group_name || ':' || l_root_server_group_name;
493 l_put_result:=CCT_COLLECTION_UTIL_PUB.PUT(l_kvp,l_key_root_server_group_name, l_root_server_group_name);
494
495
496 IF (p_country_code IS NOT NULL) THEN
497
498 l_xml_data1 := l_xml_data1 || ';' || l_key_country_code || ':' || p_country_code ;
499 l_put_result:=CCT_COLLECTION_UTIL_PUB.PUT(l_kvp,l_key_country_code, p_country_code);
500 END IF;
501
502 IF (p_area_code IS NOT NULL) THEN
503
504 l_xml_data1 := l_xml_data1 || ';' || l_key_area_code || ':' || p_area_code ;
505 l_put_result:=CCT_COLLECTION_UTIL_PUB.PUT(l_kvp,l_key_area_code, p_area_code);
506
507 END IF;
508
509 IF ((p_key1 IS NOT NULL) AND (p_value1 IS NOT NULL)) THEN
510 l_xml_data1 := l_xml_data1 || ';' || p_key1 || ':' || p_value1;
511 l_put_result:=CCT_COLLECTION_UTIL_PUB.PUT(l_kvp,p_key1, p_value1);
512 END IF;
513 IF ((p_key2 IS NOT NULL) AND (p_value2 IS NOT NULL)) THEN
514 l_xml_data1 := l_xml_data1 || ';' || p_key2 || ':' || p_value2;
515 l_put_result:=CCT_COLLECTION_UTIL_PUB.PUT(l_kvp,p_key2, p_value2);
516 END IF;
517 IF ((p_key3 IS NOT NULL) AND (p_value3 IS NOT NULL)) THEN
518 l_xml_data1 := l_xml_data1 || ';' || p_key3 || ':' || p_value3;
519 l_put_result:=CCT_COLLECTION_UTIL_PUB.PUT(l_kvp,p_key3, p_value3);
520 END IF;
521 IF ((p_key4 IS NOT NULL) AND (p_value4 IS NOT NULL)) THEN
522 l_xml_data1 := l_xml_data1 || ';' || p_key4 || ':' || p_value4;
523 l_put_result:=CCT_COLLECTION_UTIL_PUB.PUT(l_kvp,p_key4, p_value4);
524 END IF;
525 IF ((p_key5 IS NOT NULL) AND (p_value5 IS NOT NULL)) THEN
526 l_xml_data1 := l_xml_data1 || ';' || p_key5 || ':' || p_value5;
527 l_put_result:=CCT_COLLECTION_UTIL_PUB.PUT(l_kvp,p_key5, p_value5);
528 END IF;
529 IF ((p_key6 IS NOT NULL) AND (p_value6 IS NOT NULL)) THEN
530 l_xml_data1 := l_xml_data1 || ';' || p_key6 || ':' || p_value6;
531 l_put_result:=CCT_COLLECTION_UTIL_PUB.PUT(l_kvp,p_key6, p_value6);
532 END IF;
533 IF ((p_key7 IS NOT NULL) AND (p_value7 IS NOT NULL)) THEN
534 l_xml_data1 := l_xml_data1 || ';' || p_key7 || ':' || p_value7;
535 l_put_result:=CCT_COLLECTION_UTIL_PUB.PUT(l_kvp,p_key7, p_value7);
536 END IF;
537 IF ((p_key8 IS NOT NULL) AND (p_value8 IS NOT NULL)) THEN
538 l_xml_data1 := l_xml_data1 || ';' || p_key8 || ':' || p_value8;
539 l_put_result:=CCT_COLLECTION_UTIL_PUB.PUT(l_kvp,p_key8, p_value8);
540 END IF;
541 IF ((p_key9 IS NOT NULL) AND (p_value9 IS NOT NULL)) THEN
542 l_xml_data1 := l_xml_data1 || ';' || p_key9 || ':' || p_value9;
543 l_put_result:=CCT_COLLECTION_UTIL_PUB.PUT(l_kvp,p_key9, p_value9);
544 END IF;
545 IF ((p_key10 IS NOT NULL) AND (p_value10 IS NOT NULL)) THEN
546 l_xml_data1 := l_xml_data1 || ';' || p_key10 || ':' || p_value10;
547 l_put_result:=CCT_COLLECTION_UTIL_PUB.PUT(l_kvp,p_key10, p_value10);
548 END IF;
549
550
551 IF p_server_group_name <> G_BASIC_SDK_GROUP_NAME THEN
552
553 BEGIN
554
555 --dbms_output.put_line('ENQUEUE_WEB_CALLBACK_ITEM: xml_data1= ' || l_xml_data1);
556
557 enqueue_item (
558 p_api_version => p_api_version,
559 p_init_msg_list => p_init_msg_list,
560 p_commit => p_commit,
561 p_server_group_name => l_root_server_group_name,
562 p_app_id => p_app_id,
563 p_xml_data1 => l_xml_data1,
564 p_media_type => CCT_MEDIA_TYPES_PUB.G_WEB_CALLBACK,
565 p_delay => p_delay,
566 x_return_status => x_return_status,
570
567 x_msg_count => l_msg_count,
568 x_msg_data => l_msg_data
569 );
571 --dbms_output.put_line('ENQUEUE_WEB_CALLBACK_ITEM: Enqueue done!');
572 --dbms_output.put_line('ENQUEUE_WEB_CALLBACK_ITEM: ' || x_return_status);
573 --dbms_output.put_line('ENQUEUE_WEB_CALLBACK_ITEM: ' || l_msg_count);
574 --dbms_output.put_line('ENQUEUE_WEB_CALLBACK_ITEM: ' || l_msg_data);
575 -- End of API body.
576 -- Standard check of p_commit.
577
578 -- web callback is true, now check if server is up do this at the end, otherwise ,
579 -- values of x_msg_data will get over-written by the enqueue call.
580 if (is_server_up(l_iqdServerId) <> 0) then
581 x_msg_count := 1;
582 x_msg_data := 'CCT_AQ_IQD_SERVER_DOWN';
583 end if;
584
585 END;
586
587 ELSE
588
589 BEGIN
590
591
592 --dbms_output.put_line('CCT_AQ_PUB - printing kvp');
593 --FOR i IN l_kvp.FIRST..l_kvp.LAST
594 --LOOP
595 --dbms_output.put_line(l_kvp(i));
596 --END LOOP;
597
598
599 CCT_MQD_PUB.RECEIVE_MEDIA_ITEM
600 (p_api_version => p_api_version,
601 p_init_msg_list => p_init_msg_list,
602 p_commit => p_commit,
603 p_app_id => 172,
604 p_item_type => CCT_MEDIA_TYPES_PUB.G_BASIC_WEB_CALLBACK,
605 p_classification => 'unClassified',
606 p_kvp => l_kvp,
607 p_server_group_name => G_BASIC_SDK_GROUP_NAME,
608 p_direction => CCT_IH_PUB.G_IH_DIRECTION_INBOUND,
609 p_ih_item_type => CCT_IH_PUB.G_IH_TELE_WEB_CALLBACK,
610 x_return_status => l_return_status,
611 x_msg_count => l_msg_count,
612 x_msg_data => l_msg_data,
613 x_media_id => l_item_id);
614
615 --dbms_output.put_line('ENQUEUE_WEB_CALLBACK_ITEM: post CCT_AQ_PUB.RECEIVE_MEDIA_ITEM ');
616
617 CCT_QDE_PUB.UPDATE_ROUTE_RESULT
618 (p_api_version => p_api_version,
619 p_init_msg_list => p_init_msg_list,
620 p_commit => p_commit,
621 p_item_id => l_item_id,
622 p_item_type => CCT_MEDIA_TYPES_PUB.G_BASIC_WEB_CALLBACK,
623 p_classification => 'unClassified',
624 p_route_result => null,
625 p_is_route_to_all => 'Y',
626 p_is_reroute => 'N',
627 p_kvp => l_kvp,
628 x_return_status => x_return_status,
629 x_msg_count => x_msg_count,
630 x_msg_data => x_msg_data);
631
632 --dbms_output.put_line('ENQUEUE_WEB_CALLBACK_ITEM: post CCT_QDE_MEDIA_PUB.UPDATE_ROUTE_RESULT ');
633
634 END;
635 END IF;
636
637
638 IF FND_API.To_Boolean( p_commit ) THEN
639 COMMIT WORK;
640 END IF;
641
642
643 EXCEPTION
644
645 WHEN web_callback_false THEN
646 ROLLBACK TO ENQUEUE_WEB_CALLBACK_ITEM_PUB;
647 x_msg_data := 'CCT_AQ_WEB_CALLBACK_FALSE';
648 x_return_status := FND_API.G_RET_STS_ERROR;
649 x_msg_count := 1;
650 WHEN server_group_name_null THEN
651 ROLLBACK TO ENQUEUE_WEB_CALLBACK_ITEM_PUB;
652 x_msg_data := 'CCT_AQ_SERVER_GROUP_NAME_NULL';
653 x_return_status := FND_API.G_RET_STS_ERROR;
654 x_msg_count := 1;
655 WHEN country_code_null THEN
656 ROLLBACK TO ENQUEUE_WEB_CALLBACK_ITEM_PUB;
657 x_msg_data := 'CCT_AQ_COUNTRY_CODE_NULL';
658 x_return_status := FND_API.G_RET_STS_ERROR;
659 x_msg_count := 1;
660 WHEN area_code_null THEN
661 ROLLBACK TO ENQUEUE_WEB_CALLBACK_ITEM_PUB;
662 x_msg_data := 'CCT_AQ_AREA_CODE_NULL';
663 x_return_status := FND_API.G_RET_STS_ERROR;
664 x_msg_count := 1;
665 WHEN phone_number_null THEN
666 ROLLBACK TO ENQUEUE_WEB_CALLBACK_ITEM_PUB;
667 x_msg_data := 'CCT_AQ_PHONE_NUMBER_NULL';
668 x_return_status := FND_API.G_RET_STS_ERROR;
669 x_msg_count := 1;
670 WHEN FND_API.G_EXC_ERROR THEN
671 ROLLBACK TO ENQUEUE_WEB_CALLBACK_ITEM_PUB;
672 x_msg_data := 'CCT_AQ_ERROR' || SQLCODE || SUBSTR(SQLERRM, 1, 100);
673 x_return_status := FND_API.G_RET_STS_ERROR;
674 x_msg_count := 1;
675 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
676 ROLLBACK TO ENQUEUE_WEB_CALLBACK_ITEM_PUB;
677 x_msg_data := 'CCT_AQ_ERROR' || SQLCODE || SUBSTR(SQLERRM, 1, 100);
678 x_return_status := FND_API.G_RET_STS_ERROR;
679 x_msg_count := 1;
680 WHEN OTHERS THEN
681 ROLLBACK TO ENQUEUE_WEB_CALLBACK_ITEM_PUB;
682 x_msg_data := 'CCT_AQ_ERROR' || SQLCODE || SUBSTR(SQLERRM, 1, 100);
683 x_return_status := FND_API.G_RET_STS_ERROR;
684 x_msg_count := 1;
685 END ENQUEUE_WEB_CALLBACK_ITEM;
686
687
688 PROCEDURE ENQUEUE_WEB_COLLAB_ITEM
689 ( p_api_version IN NUMBER,
690 p_init_msg_list IN VARCHAR2,
691 p_commit IN VARCHAR2,
692 p_server_group_id IN NUMBER,
693 p_server_group_name IN VARCHAR2,
694 p_app_id IN NUMBER,
695 p_meeting_url IN VARCHAR2 ,
696 p_with_callback IN VARCHAR2,
697 p_ih_media_item_id IN NUMBER,
698 p_country_code IN NUMBER,
699 p_area_code IN NUMBER,
700 p_phone_number IN NUMBER,
701 p_key1 IN VARCHAR2,
702 p_value1 IN VARCHAR2,
706 p_value3 IN VARCHAR2,
703 p_key2 IN VARCHAR2,
704 p_value2 IN VARCHAR2,
705 p_key3 IN VARCHAR2,
707 p_key4 IN VARCHAR2,
708 p_value4 IN VARCHAR2,
709 p_key5 IN VARCHAR2,
710 p_value5 IN VARCHAR2,
711 p_key6 IN VARCHAR2,
712 p_value6 IN VARCHAR2,
713 p_key7 IN VARCHAR2,
714 p_value7 IN VARCHAR2,
715 p_key8 IN VARCHAR2,
716 p_value8 IN VARCHAR2,
717 p_key9 IN VARCHAR2,
718 p_value9 IN VARCHAR2,
719 p_key10 IN VARCHAR2,
720 p_value10 IN VARCHAR2,
721 x_return_status OUT NOCOPY VARCHAR2,
722 x_msg_count OUT NOCOPY NUMBER,
723 x_msg_data OUT NOCOPY VARCHAR2
724 )
725 IS
726 l_api_name CONSTANT VARCHAR2(30) := 'ENQUEUE_WEB_COLLAB_ITEM';
727 l_api_version CONSTANT NUMBER := 1.0;
728 l_xml_data1 VARCHAR2(2048) DEFAULT NULL;
729
730
731 l_key_country_code CONSTANT VARCHAR2(30) := G_KEY_COUNTRY_CODE;
732 l_key_area_code CONSTANT VARCHAR2(30) := G_KEY_AREA_CODE;
733 l_key_phone_number CONSTANT VARCHAR2(30) := G_KEY_PHONE_NUMBER;
734 l_key_local_server_group_name CONSTANT VARCHAR2(30) := G_KEY_LOCAL_SERVER_GROUP_NAME;
735 l_key_root_server_group_name CONSTANT VARCHAR2(30) := G_KEY_SUPER_SERVER_GROUP_NAME;
736 l_local_server_group_name VARCHAR2(256) DEFAULT NULL;
737 l_root_server_group_name VARCHAR2(256);
738
739 l_key_meeting_url CONSTANT VARCHAR2(30) := G_KEY_MEETING_URL;
740 l_key_with_callback CONSTANT VARCHAR2(30) := G_KEY_WITH_CALLBACK;
741 l_key_ih_media_item_id CONSTANT VARCHAR2(30) := G_KEY_MEDIA_ITEM_ID;
742
743 server_group_undefined EXCEPTION;
744 meeting_url_null EXCEPTION;
745 ih_media_item_id_null EXCEPTION;
746 phone_number_null EXCEPTION;
747
748 BEGIN
749 -- Standard Start of API savepoint
750 SAVEPOINT ENQUEUE_WEB_COLLAB_ITEM_PUB;
751 -- Standard call to check for call compatibility.
752 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
753 p_api_version ,
754 l_api_name ,
755 G_PKG_NAME )
756 THEN
757 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
758 END IF;
759 -- Initialize message list if p_init_msg_list is set to TRUE.
760 IF FND_API.To_Boolean( p_init_msg_list ) THEN
761 FND_MSG_PUB.initialize;
762 END IF;
763 -- Initialize API return status to success
764
765 x_return_status := FND_API.G_RET_STS_SUCCESS;
766 x_msg_data := 'CCT_AQ_SUCCESS';
767 x_msg_count := 0;
768
769 -- API body
770
771 IF ((p_server_group_name IS NULL) and (p_server_group_id IS NULL)) THEN
772 raise server_group_undefined;
773 END IF;
774
775 IF (p_meeting_url IS NULL) THEN
776 raise meeting_url_null;
777 END IF;
778
779 IF (p_ih_media_item_id = -1) THEN
780 raise ih_media_item_id_null;
781 END IF;
782
783 IF (p_with_callback = FND_API.G_TRUE) THEN
784 IF (p_phone_number IS NULL) THEN
785 raise phone_number_null;
786 END IF;
787 END IF;
788
789 -- Assemble Data into Object
790
791 IF (p_server_group_id IS NOT NULL) THEN
792 select group_name into l_local_server_group_name from ieo_svr_groups where server_group_id = p_server_group_id;
793 IF (l_local_server_group_name IS NULL) THEN
794 raise server_group_undefined;
795 END IF;
796 l_xml_data1 := l_key_local_server_group_name || ':' || l_local_server_group_name;
797 ELSE
798 IF (p_server_group_name IS NULL) THEN
799 raise server_group_undefined;
800 END IF;
801 l_xml_data1 := l_key_local_server_group_name || ':' || p_server_group_name;
802 END IF;
803
804 IF (p_meeting_url IS NOT NULL) THEN
805 l_xml_data1 := l_xml_data1 || ';' || l_key_meeting_url || ':' || p_meeting_url ;
806 END IF;
807
808 IF (p_with_callback IS NOT NULL) THEN
809 l_xml_data1 := l_xml_data1 || ';' || l_key_with_callback || ':' || p_with_callback ;
810 END IF;
811
812 IF (p_ih_media_item_id IS NOT NULL) THEN
813 l_xml_data1 := l_xml_data1 || ';' || l_key_ih_media_item_id || ':' || p_ih_media_item_id ;
814 END IF;
815
816 IF (p_country_code IS NOT NULL) THEN
817 l_xml_data1 := l_xml_data1 || ';' || l_key_country_code || ':' || p_country_code ;
818 END IF;
819
820 IF (p_area_code IS NOT NULL) THEN
821 l_xml_data1 := l_xml_data1 || ';' || l_key_area_code || ':' || p_area_code ;
822 END IF;
823
824 IF (p_phone_number IS NOT NULL) THEN
825 l_xml_data1 := l_xml_data1 || ';' || l_key_phone_number || ':' || p_phone_number ;
826 END IF;
827
828
829 IF ((p_key1 IS NOT NULL) AND (p_value1 IS NOT NULL)) THEN
830 l_xml_data1 := l_xml_data1 || ';' || p_key1 || ':' || p_value1;
831 END IF;
832 IF ((p_key2 IS NOT NULL) AND (p_value2 IS NOT NULL)) THEN
833 l_xml_data1 := l_xml_data1 || ';' || p_key2 || ':' || p_value2;
834 END IF;
835 IF ((p_key3 IS NOT NULL) AND (p_value3 IS NOT NULL)) THEN
836 l_xml_data1 := l_xml_data1 || ';' || p_key3 || ':' || p_value3;
837 END IF;
838 IF ((p_key4 IS NOT NULL) AND (p_value4 IS NOT NULL)) THEN
839 l_xml_data1 := l_xml_data1 || ';' || p_key4 || ':' || p_value4;
840 END IF;
841 IF ((p_key5 IS NOT NULL) AND (p_value5 IS NOT NULL)) THEN
842 l_xml_data1 := l_xml_data1 || ';' || p_key5 || ':' || p_value5;
846 END IF;
843 END IF;
844 IF ((p_key6 IS NOT NULL) AND (p_value6 IS NOT NULL)) THEN
845 l_xml_data1 := l_xml_data1 || ';' || p_key6 || ':' || p_value6;
847 IF ((p_key7 IS NOT NULL) AND (p_value7 IS NOT NULL)) THEN
848 l_xml_data1 := l_xml_data1 || ';' || p_key7 || ':' || p_value7;
849 END IF;
850 IF ((p_key8 IS NOT NULL) AND (p_value8 IS NOT NULL)) THEN
851 l_xml_data1 := l_xml_data1 || ';' || p_key8 || ':' || p_value8;
852 END IF;
853 IF ((p_key9 IS NOT NULL) AND (p_value9 IS NOT NULL)) THEN
854 l_xml_data1 := l_xml_data1 || ';' || p_key9 || ':' || p_value9;
855 END IF;
856 IF ((p_key10 IS NOT NULL) AND (p_value10 IS NOT NULL)) THEN
857 l_xml_data1 := l_xml_data1 || ';' || p_key10 || ':' || p_value10;
858 END IF;
859
860 get_root_group_name (
861 p_api_version => 1.0,
862 p_server_group_name => p_server_group_name,
863 x_return_status => x_return_status,
864 x_msg_count => x_msg_count,
865 x_msg_data => x_msg_data,
866 x_root_server_group_name => l_root_server_group_name
867 );
868
869 --dbms_output.put_line('ENQUEUE_WEB_COLLAB_ITEM: xml_data1= ' || l_xml_data1);
870
871 l_xml_data1 := l_xml_data1 || ';' || l_key_root_server_group_name || ':' || l_root_server_group_name;
872
873 enqueue_item (
874 p_api_version => p_api_version,
875 p_init_msg_list => p_init_msg_list,
876 p_commit => p_commit,
877 p_server_group_name => l_root_server_group_name,
878 p_app_id => p_app_id,
879 p_xml_data1 => l_xml_data1,
880 p_media_type => CCT_MEDIA_TYPES_PUB.G_WEB_COLLAB,
881 x_return_status => x_return_status,
882 x_msg_count => x_msg_count,
883 x_msg_data => x_msg_data
884 );
885
886
887 --dbms_output.put_line('ENQUEUE_WEB_COLLAB_ITEM: Enqueue done!');
888 --End of API body.
889 --Standard check of p_commit.
890 IF FND_API.To_Boolean( p_commit ) THEN
891 COMMIT WORK;
892 END IF;
893
894 EXCEPTION
895
896 WHEN server_group_undefined THEN
897 ROLLBACK TO ENQUEUE_WEB_COLLAB_ITEM_PUB;
898 x_msg_data := 'CCT_AQ_SERVER_GROUP_NAME_NULL';
899 x_return_status := FND_API.G_RET_STS_ERROR;
900 x_msg_count := 1;
901
902 WHEN meeting_url_null THEN
903 ROLLBACK TO ENQUEUE_WEB_COLLAB_ITEM_PUB;
904 x_msg_data := 'CCT_AQ_MEETING_URL_NULL';
905 x_return_status := FND_API.G_RET_STS_ERROR;
906 x_msg_count := 1;
907
908 WHEN ih_media_item_id_null THEN
909 ROLLBACK TO ENQUEUE_WEB_COLLAB_ITEM_PUB;
910 x_msg_data := 'CCT_AQ_IH_MEDIA_ITEM_ID_NULL';
911 x_return_status := FND_API.G_RET_STS_ERROR;
912 x_msg_count := 1;
913
914 WHEN phone_number_null THEN
915 ROLLBACK TO ENQUEUE_WEB_COLLAB_ITEM_PUB;
916 x_msg_data := 'CCT_AQ_PHONE_NUMBER_NULL';
917 x_return_status := FND_API.G_RET_STS_ERROR;
918 x_msg_count := 1;
919
920 WHEN FND_API.G_EXC_ERROR THEN
921 ROLLBACK TO ENQUEUE_WEB_COLLAB_ITEM_PUB;
922 x_msg_data := 'CCT_AQ_ERROR';
923 x_return_status := FND_API.G_RET_STS_ERROR;
924 x_msg_count := 1;
925
926 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
927 ROLLBACK TO ENQUEUE_WEB_COLLAB_ITEM_PUB;
928 x_msg_data := 'CCT_AQ_ERROR';
929 x_return_status := FND_API.G_RET_STS_ERROR;
930 x_msg_count := 1;
931
932 WHEN OTHERS THEN
933 ROLLBACK TO ENQUEUE_WEB_COLLAB_ITEM_PUB;
934 x_msg_data := 'CCT_AQ_ERROR';
935 x_return_status := FND_API.G_RET_STS_ERROR;
936 x_msg_count := 1;
937
938 END ENQUEUE_WEB_COLLAB_ITEM;
939
940
941 PROCEDURE ABANDON_MEDIA
942 ( p_api_version IN NUMBER,
943 p_init_msg_list IN VARCHAR2,
944 p_commit IN VARCHAR2,
945 p_server_group_name IN VARCHAR2,
946 p_app_id IN NUMBER,
947 p_media_item_id IN NUMBER ,
948 x_return_status OUT NOCOPY VARCHAR2,
949 x_msg_count OUT NOCOPY NUMBER,
950 x_msg_data OUT NOCOPY VARCHAR2
951 )
952 IS
953 l_api_name CONSTANT VARCHAR2(30) := 'ABANDON_MEDIA';
954 l_api_version CONSTANT NUMBER := 1.0;
955 l_xml_data1 VARCHAR2(2048) DEFAULT NULL;
956 -- l_key_server_group CONSTANT VARCHAR2(30) := 'ServerGroup';
957 l_key_media_item_id CONSTANT VARCHAR2(30) := 'MediaItemId';
958
959 l_root_server_group_name VARCHAR2(256);
960
961 server_group_name_null EXCEPTION;
962 BEGIN
963 -- Standard Start of API savepoint
964 SAVEPOINT ABANDON_MEDIA_PUB;
965 -- Standard call to check for call compatibility.
966 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
967 p_api_version ,
968 l_api_name ,
969 G_PKG_NAME )
970 THEN
971 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
972 END IF;
973 -- Initialize message list if p_init_msg_list is set to TRUE.
974 IF FND_API.To_Boolean( p_init_msg_list ) THEN
975 FND_MSG_PUB.initialize;
976 END IF;
977 -- Initialize API return status to success
978 x_return_status := FND_API.G_RET_STS_SUCCESS;
979 x_msg_data := 'CCT_AQ_SUCCESS';
980 x_msg_count := 0;
981
982 -- API body
983 IF p_server_group_name IS NULL then
984 raise server_group_name_null;
985 END IF;
986
987 -- Assemble Data into Object
988
989 get_root_group_name (
990 p_api_version => 1.0,
991 p_server_group_name => p_server_group_name,
992 x_return_status => x_return_status,
996 );
993 x_msg_count => x_msg_count,
994 x_msg_data => x_msg_data,
995 x_root_server_group_name => l_root_server_group_name
997
998 -- l_xml_data1 := l_key_server_group || ':' || p_server_group_name ;
999
1000 l_xml_data1 := l_key_media_item_id || ':' || p_media_item_id ;
1001
1002 --dbms_output.put_line('ABANDON_MEDIA: xml_data1= ' || l_xml_data1);
1003
1004 enqueue_item (
1005 p_api_version => p_api_version,
1006 p_init_msg_list => p_init_msg_list,
1007 p_commit => p_commit,
1008 p_server_group_name => l_root_server_group_name,
1009 p_app_id => p_app_id,
1010 p_xml_data1 => l_xml_data1,
1011 p_media_type => G_ABANDON_MEDIA,
1012 x_return_status => x_return_status,
1013 x_msg_count => x_msg_count,
1014 x_msg_data => x_msg_data
1015 );
1016
1017
1018 --dbms_output.put_line('ABANDON_MEDIA: Enqueue done!');
1019 --End of API body.
1020 --Standard check of p_commit.
1021 IF FND_API.To_Boolean( p_commit ) THEN
1022 COMMIT WORK;
1023 END IF;
1024
1025 EXCEPTION
1026
1027 WHEN server_group_name_null THEN
1028 ROLLBACK TO ABANDON_MEDIA_PUB;
1029 x_msg_data := 'CCT_AQ_SERVER_GROUP_NAME_NULL';
1030 x_return_status := FND_API.G_RET_STS_ERROR;
1031 x_msg_count := 1;
1032
1033 WHEN FND_API.G_EXC_ERROR THEN
1034 ROLLBACK TO ABANDON_MEDIA_PUB;
1035 x_msg_data := 'CCT_AQ_ERROR';
1036 x_return_status := FND_API.G_RET_STS_ERROR;
1037 x_msg_count := 1;
1038
1039 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1040 ROLLBACK TO ABANDON_MEDIA_PUB;
1041 x_msg_data := 'CCT_AQ_ERROR';
1042 x_return_status := FND_API.G_RET_STS_ERROR;
1043 x_msg_count := 1;
1044
1045 WHEN OTHERS THEN
1046 ROLLBACK TO ABANDON_MEDIA_PUB;
1047 x_msg_data := 'CCT_AQ_ERROR';
1048 x_return_status := FND_API.G_RET_STS_ERROR;
1049 x_msg_count := 1;
1050
1051 END ABANDON_MEDIA;
1052
1053
1054
1055 END CCT_AQ_PUB;