[Home] [Help]
PACKAGE BODY: APPS.CCT_QDE_PUB
Source
1 PACKAGE BODY CCT_QDE_PUB AS
2 /* $Header: cctpqdeb.pls 115.6 2003/10/20 20:12:21 svinamda noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CCT_QDE_PUB';
5
6
7
8 FUNCTION UPDATE_CLASSIFICATION_COUNT
9 (
10 p_commit IN VARCHAR2,
11 p_agent_id IN NUMBER,
12 p_item_type IN NUMBER,
13 p_classification IN VARCHAR2,
14 p_count IN NUMBER
15 )
16 RETURN NUMBER IS
17 ENTRY_NOT_FOUND EXCEPTION;
18 x_err_num NUMBER;
19 x_err_msg VARCHAR2(256);
20 l_item_type number;
21 BEGIN
22 --dbms_output.put_line('UPDATE_CLASSIFICATION_COUNT:'
23 --|| ' agent_id = ' || p_agent_id
24 --|| ' p_item_type = ' || p_item_type
25 --|| ' p_classification = ' || p_classification
26 --|| ' p_count = ' || p_count);
27
28 l_item_type := CCT_MEDIA_TYPES_PUB.GET_UWQ_MEDIA_TYPE_ID(p_item_type);
29
30 update cct_qde_agent_queues set count = p_count
31 where agent_id = p_agent_id
32 and item_type = l_item_type
33 and ((p_classification is null and classification is null) or (p_classification is not null
34 and classification = p_classification)) ;
35 if sql%notfound
36 then raise ENTRY_NOT_FOUND;
37 else
38 begin
39 IF FND_API.To_Boolean( p_commit ) THEN
40 COMMIT WORK;
41 END IF;
42 return 0;
43 end;
44 end if;
45
46 EXCEPTION
47 WHEN ENTRY_NOT_FOUND THEN
48 insert into cct_qde_agent_queues
49 ( agent_queue_id, agent_id, item_type, classification, count,
50 created_by, creation_date,
51 last_updated_by, last_update_date, last_update_login
52 )
53 values
54 (
55 cct_qde_agent_queues_s1.nextval, p_agent_id, l_item_type, p_classification, p_count,
56 1, sysdate, 1, sysdate, 1
57 );
58 IF FND_API.To_Boolean( p_commit ) THEN
59 COMMIT WORK;
60 END IF;
61 return 0;
62 WHEN OTHERS THEN
63 rollback;
64 x_err_num := SQLCODE;
65 x_err_msg := SUBSTR(SQLERRM, 1, 100);
66 --dbms_output.put_line(x_err_num || x_err_msg);
67 RETURN -1;
68 END UPDATE_CLASSIFICATION_COUNT;
69
70
71
72 -- return value = 0 => bit is set.
73 -- return value <> 0 => bit is not set.
74
75 FUNCTION IS_BIT_SET (agent_id IN RAW, route_result IN RAW) RETURN NUMBER IS
76 short_raw RAW(2000);
77 short_len number;
78 BEGIN
79 --dbms_output.put_line('IS_BIT_SET' || 'agent_id= '
80 --|| agent_id || 'route_result = ' || route_result);
81 if utl_raw.length(agent_id) > utl_raw.length(route_result)
82 then
83 -- truncate agent_id to size of route result.
84 short_len := utl_raw.length(route_result);
85 short_raw := utl_raw.substr(agent_id,-short_len,short_len);
86 return utl_raw.compare(utl_raw.bit_and(short_raw, route_result), short_raw);
87 else
88 -- truncate route_result to size of agent_id
89 short_len := utl_raw.length(agent_id);
90 short_raw := utl_raw.substr(route_result,-short_len,short_len);
91 --dbms_output.put_line('resutl ' || utl_raw.compare(utl_raw.bit_and(short_raw, agent_id), agent_id));
92 return utl_raw.compare(utl_raw.bit_and(short_raw, agent_id), agent_id);
93 end if;
94
95 END IS_BIT_SET;
96
97
98
99 PROCEDURE RECEIVE_ITEM
100 (
101 p_api_version IN NUMBER,
102 p_init_msg_list IN VARCHAR2 ,
103 p_commit IN VARCHAR2,
104 p_app_id IN NUMBER,
105 p_item_id IN NUMBER,
106 p_item_type IN NUMBER,
107 p_classification IN VARCHAR2,
108 p_kvp IN cct_keyvalue_varr,
109 p_delay IN NUMBER,
110 x_return_status OUT NOCOPY VARCHAR2 ,
111 x_msg_count OUT NOCOPY NUMBER ,
112 x_msg_data OUT NOCOPY VARCHAR2
113 )
114 IS
115 l_api_name CONSTANT VARCHAR2(30) := 'RECEIVE_ITEM';
116 l_api_version CONSTANT NUMBER := 1.0;
117 l_return_status varchar2(256);
118 l_msg_count number;
119 l_msg_data varchar2(256);
120 x_err_num NUMBER;
121 x_err_msg VARCHAR2(256);
122
123 BEGIN
124 IF NOT FND_API.Compatible_API_Call (l_api_version ,
125 p_api_version ,
126 l_api_name ,
127 G_PKG_NAME )
128 THEN
129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
130 END IF;
131 -- Initialize message list if p_init_msg_list is set to TRUE.
132 IF FND_API.to_Boolean( p_init_msg_list ) THEN
133 FND_MSG_PUB.initialize;
134 END IF;
135 -- Initialize API return status to failure
136
137 x_return_status := FND_API.G_RET_STS_SUCCESS ;
138 x_msg_count := 0;
139 x_msg_data := null;
140
141 -- API body
142 --dbms_output.put_line('RECEIVE_ITEM: pre insert data');
143 insert into cct_qde_data (item_id,
144 item_kvp,
145 created_by, creation_date,
146 last_updated_by, last_update_date, last_update_login)
147 values (p_item_id,
148 p_kvp,
149 1, sysdate, 1, sysdate, 1);
150 --dbms_output.put_line('RECEIVE_ITEM: post insert data');
151 --dbms_output.put_line('RECEIVE_ITEM: pre insert rr');
152 insert into cct_qde_route_result (item_id,
153 item_type, classification, route_result,
154 is_route_to_all, is_reroute, is_routed, start_time,
155 created_by, creation_date,
156 last_updated_by, last_update_date, last_update_login)
157 values (p_item_id,
158 p_item_type, p_classification, null,
159 'N', 'N', 'N', sysdate,
160 1, sysdate, 1, sysdate, 1);
161 --dbms_output.put_line('RECEIVE_ITEM: post insert rr');
162 IF FND_API.To_Boolean( p_commit ) THEN
163 COMMIT WORK;
164 END IF;
165
166 -- with release 9i, if delay is present then enqueue item to AQ with delay.
167
168 -- Routing.enqueue.item.
169
170 EXCEPTION
171 WHEN FND_API.G_EXC_ERROR THEN
172 rollback;
173 x_return_status := FND_API.G_RET_STS_ERROR ;
174 x_msg_count := 1;
175 x_err_num := SQLCODE;
176 x_err_msg := SUBSTR(SQLERRM, 1, 100);
177 x_msg_data := 'RECEIVE_ITEM: CCT_ERROR'
178 || ' ErrorCode = ' || x_err_num
179 || ' ErrorMsg = ' || x_err_msg;
180 --dbms_output.put_line(x_msg_data);
181
182 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
183 --dbms_output.put_line('Unexpected error');
184 ROLLBACK;
185 x_return_status := FND_API.G_RET_STS_ERROR ;
186 x_msg_count := 1;
187 x_err_num := SQLCODE;
188 x_err_msg := SUBSTR(SQLERRM, 1, 100);
189 x_msg_data := 'RECEIVE_ITEM: CCT_ERROR'
190 || ' ErrorCode = ' || x_err_num
191 || ' ErrorMsg = ' || x_err_msg;
192 --dbms_output.put_line(x_msg_data);
193 WHEN OTHERS THEN
194 rollback;
195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
196 x_msg_count := 1;
197 x_err_num := SQLCODE;
198 x_err_msg := SUBSTR(SQLERRM, 1, 100);
199 x_msg_data := 'RECEIVE_ITEM: CCT_ERROR'
200 || ' ErrorCode = ' || x_err_num
201 || ' ErrorMsg = ' || x_err_msg;
202 --dbms_output.put_line(x_msg_data);
203 END RECEIVE_ITEM;
204
205
206
207 PROCEDURE UPDATE_ROUTE_RESULT
208 (
209 p_api_version IN NUMBER,
210 p_init_msg_list IN VARCHAR2,
211 p_commit IN VARCHAR2,
212 p_item_id IN NUMBER,
213 p_item_type IN NUMBER,
214 p_classification IN VARCHAR2,
215 p_route_result IN VARCHAR2,
216 p_is_route_to_all IN VARCHAR2,
217 p_is_reroute IN VARCHAR2,
218 p_kvp IN cct_keyvalue_varr,
219 x_return_status OUT NOCOPY VARCHAR2,
220 x_msg_count OUT NOCOPY NUMBER,
221 x_msg_data OUT NOCOPY VARCHAR2
222 )
223 IS
224 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ROUTE_RESULT';
225 l_api_version CONSTANT NUMBER := 1.0;
226 l_item_id NUMBER;
227 x_err_num NUMBER;
228 x_err_msg VARCHAR2(256);
229 AGENT_NOT_FOUND EXCEPTION;
230 RESULT_NOT_FOUND EXCEPTION;
231 BIT_NOT_SET EXCEPTION;
232 RESOURCE_NOT_LOCKED EXCEPTION;
233 l_route_result VARCHAR2(2000);
234 l_agent_id number;
235 l_kvp cct_keyvalue_varr;
236 enqueue_options dbms_aq.enqueue_options_t;
237 message_properties dbms_aq.message_properties_t;
238 message_handle RAW(16);
239 message SYSTEM.cct_qde_response;
240
241 PRAGMA EXCEPTION_INIT(RESOURCE_NOT_LOCKED, -54);
242
243 BEGIN
244 IF NOT FND_API.Compatible_API_Call (l_api_version ,
245 p_api_version ,
246 l_api_name ,
247 G_PKG_NAME )
248 THEN
249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
250 END IF;
251 -- Initialize message list if p_init_msg_list is set to TRUE.
252 IF FND_API.to_Boolean( p_init_msg_list ) THEN
253 FND_MSG_PUB.initialize;
254 END IF;
255 -- Initialize API return status to success
256 x_return_status := FND_API.G_RET_STS_SUCCESS;
257 x_msg_count := 0;
258 x_msg_data := null;
259
260 -- API body
261 --dbms_output.put_line('UPDATE_ROUTE_RESULT: item_id = ' || p_item_id
262 --|| ' item_type = ' || p_item_type
263 --|| ' classification = ' || p_classification
264 --|| ' p_route_result = ' || p_route_result
265 --|| ' p_is_route_to_all = ' || p_is_route_to_all
266 --|| ' p_is_reroute = ' || p_is_reroute);
267
268 l_route_result := p_route_result;
269 if ((p_is_route_to_all = 'Y') or (p_route_result is null)) then
270 l_route_result := '0000';
271 end if;
272
273 update cct_qde_route_result
274 set classification = p_classification,
275 route_result = l_route_result,
276 is_route_to_all = p_is_route_to_all,
277 is_reroute = p_is_reroute,
278 is_routed = 'Y'
279 where item_id = p_item_id;
280
281 update cct_qde_data
282 set item_kvp = p_kvp
283 where item_id = p_item_id;
284
285 commit;
286 -- check if agent is available to process media item.
287
288 declare cursor r1 is
289 select agent_id, raw_agent_index, item_type, classification
290 from cct_qde_agent_vw
291 where (item_type = p_item_type)
292 and ((classification is null) or (classification is not null
293 and classification = p_classification))
294 and (is_get_work = 1)
295 and ((p_is_route_to_all = 'Y') or
296 ((p_is_route_to_all = 'N') and (is_bit_set(raw_agent_index, l_route_result) = 0)));
297
298 begin
299 --dbms_output.put_line('UPDATE_ROUTE_RESULT: post declare cursor');
300 for r1_rec in r1 loop
301 begin
302 SAVEPOINT GET_NEXT_AGENT_SAVEPOINT_2;
303 --dbms_output.put_line('agent_id = ' || r1_rec.agent_id
304 --|| ' item_type = ' || r1_rec.item_type
305 --|| ' classification = ' || r1_rec.classification);
306 --dbms_output.put_line('UPDATE_ROUTE_RESULT: pre lock agent');
307 begin
308
309 select agent_id into l_agent_id
310 from cct_qde_agent
311 where agent_id = r1_rec.agent_id and is_get_work = 1
312 for update nowait;
313 --dbms_output.put_line('UPDATE_ROUTE_RESULT: post lock agent');
314 exception
315 when NO_DATA_FOUND then
316 raise AGENT_NOT_FOUND ;
317 when RESOURCE_NOT_LOCKED then
318 raise AGENT_NOT_FOUND;
319
320 end;
321
322 begin
323 --dbms_output.put_line('UPDATE_ROUTE_RESULT: pre lock route result');
324 select item_id into l_item_id
325 from cct_qde_route_result
326 where item_id = p_item_id
327 for update NOWAIT;
328 --dbms_output.put_line('UPDATE_ROUTE_RESULT: post lock route result');
329 exception
330 when NO_DATA_FOUND then
331 raise RESULT_NOT_FOUND ;
332 when RESOURCE_NOT_LOCKED then
333 raise RESULT_NOT_FOUND ;
334
335 end;
336
337 select item_kvp into l_kvp from cct_qde_data
338 where item_id = p_item_id;
339
340 -- enqueue result.
341
342 --dbms_output.put_line('Match found...');
343 --dbms_output.put_line('Matched item_id = ' || p_item_id || ' , agent_id = ' || r1_rec. agent_id);
344
345 delete from cct_qde_route_result where item_id = p_item_id;
346 --dbms_output.put_line('UPDATE_ROUTE_RESULT: post delete route result');
347
348
349 delete from cct_qde_data where item_id = p_item_id;
350
351 update cct_qde_agent
352 set is_get_work = 0, item_type = null, classification = null
353 where agent_id = r1_rec.agent_id;
354
355 -- TODO Handle what happens if dequeue returns failure.
356 -- routing.stop reroute.
357
358
359 message := SYSTEM.cct_qde_response(r1_rec.agent_id, p_item_id, p_item_type, p_classification, 172, l_kvp);
360 message_properties.correlation := r1_rec.agent_id;
361 dbms_aq.enqueue(queue_name => CCT_QDE_QUEUE.queue_name,
362 enqueue_options => enqueue_options,
363 message_properties => message_properties,
364 payload => message,
365 msgid => message_handle);
366
367 --dbms_output.put_line('UPDATE_ROUTE_RESULT: post dequeue result');
368 IF FND_API.To_Boolean( p_commit ) THEN
369 COMMIT WORK;
370 END IF;
371 exit;
372 exception
373 WHEN BIT_NOT_SET then
374 -- try to find match with other route result.
375 IF FND_API.To_Boolean( p_commit ) THEN
376 COMMIT WORK;
377 END IF;
378 WHEN AGENT_NOT_FOUND then
379 IF FND_API.To_Boolean( p_commit ) THEN
380 COMMIT WORK;
381 END IF;
382 WHEN RESULT_NOT_FOUND then
383 IF FND_API.To_Boolean( p_commit ) THEN
384 COMMIT WORK;
385 END IF;
386 exit;
387 WHEN NO_DATA_FOUND then
388 -- possible that agent request is already processed.
389 IF FND_API.To_Boolean( p_commit ) THEN
390 COMMIT WORK;
391 END IF;
392 WHEN OTHERS then
393 rollback to GET_NEXT_AGENT_SAVEPOINT_2;
394 x_err_num := SQLCODE;
395 x_err_msg := SUBSTR(SQLERRM, 1, 100);
396 x_msg_data :='UPDATE_ROUTE_RESULT: CCT_ERROR'
397 || ' ErrorCode = ' || x_err_num
398 || ' ErrorMsg = ' || x_err_msg;
399 --dbms_output.put_line(x_msg_data);
400 end;
401 end loop;
402
403 IF FND_API.To_Boolean( p_commit ) THEN
404 COMMIT WORK;
405 END IF;
406 end;
407 EXCEPTION
408 WHEN FND_API.G_EXC_ERROR THEN
409 rollback;
410 x_return_status := FND_API.G_RET_STS_ERROR ;
411 x_msg_count := 1;
412 x_err_num := SQLCODE;
413 x_err_msg := SUBSTR(SQLERRM, 1, 100);
414 x_msg_data :='UPDATE_ROUTE_RESULT: CCT_ERROR'
415 || ' ErrorCode = ' || x_err_num
416 || ' ErrorMsg = ' || x_err_msg;
417 --dbms_output.put_line(x_msg_data);
418 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
419 ROLLBACK;
420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
421 x_msg_count := 1;
422 x_err_num := SQLCODE;
423 x_err_msg := SUBSTR(SQLERRM, 1, 100);
424 x_msg_data :='UPDATE_ROUTE_RESULT: CCT_ERROR'
425 || ' ErrorCode = ' || x_err_num
426 || ' ErrorMsg = ' || x_err_msg;
427 --dbms_output.put_line(x_msg_data);
428 WHEN OTHERS THEN
429 rollback;
430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
431 x_msg_count := 1;
432 x_err_num := SQLCODE;
433 x_err_msg := SUBSTR(SQLERRM, 1, 100);
434 x_msg_data :='UPDATE_ROUTE_RESULT: CCT_ERROR'
435 || ' ErrorCode = ' || x_err_num
436 || ' ErrorMsg = ' || x_err_msg;
437 --dbms_output.put_line(x_msg_data);
438 END UPDATE_ROUTE_RESULT;
439
440
441 PROCEDURE GET_NEXT_ITEM
442 (
443 p_api_version IN NUMBER,
444 p_init_msg_list IN VARCHAR2,
445 p_commit IN VARCHAR2,
446 p_agent_id IN NUMBER,
447 p_item_type IN NUMBER,
448 p_classification IN VARCHAR2,
449 x_return_status OUT NOCOPY VARCHAR2,
450 x_msg_count OUT NOCOPY NUMBER,
451 x_msg_data OUT NOCOPY VARCHAR2,
452 x_app_id OUT NOCOPY NUMBER,
453 x_item_id OUT NOCOPY NUMBER,
454 x_item_type OUT NOCOPY NUMBER,
455 x_classification OUT NOCOPY VARCHAR2,
456 x_kvp OUT NOCOPY cct_keyvalue_varr
457 )
458 IS
459 l_api_name CONSTANT VARCHAR2(30) := 'GET_NEXT_ITEM';
460 l_api_version CONSTANT NUMBER := 1.0;
461 AGENT_NOT_FOUND EXCEPTION;
462 RESULT_NOT_FOUND EXCEPTION;
463 BIT_NOT_SET EXCEPTION;
464 RESOURCE_NOT_LOCKED EXCEPTION;
465 l_raw_agent_index RAW(2000);
466 l_agent_id number;
467 l_return_status varchar2(256);
468 l_msg_count number;
469 l_msg_data varchar2(256);
470 x_err_num NUMBER;
471 x_err_msg VARCHAR2(256);
472
473 PRAGMA EXCEPTION_INIT(RESOURCE_NOT_LOCKED, -54);
474
475
476 BEGIN
477 IF NOT FND_API.Compatible_API_Call (l_api_version ,
478 p_api_version ,
479 l_api_name ,
480 G_PKG_NAME )
481 THEN
482 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
483 END IF;
484 -- Initialize message list if p_init_msg_list is set to TRUE.
485 IF FND_API.to_Boolean( p_init_msg_list ) THEN
486 FND_MSG_PUB.initialize;
487 END IF;
488 -- Initialize API return status to failure
489 SAVEPOINT GET_NEXT_ITEM_SAVEPOINT_1;
490
491 x_return_status := FND_API.G_RET_STS_SUCCESS ;
492 x_msg_count := 0;
493 x_msg_data := null;
494 x_item_id := -1;
495 x_item_type := -1;
496 x_classification := null;
497 x_kvp := cct_keyvalue_varr();
498
499 -- API body
500
501 --dbms_output.put_line('GET_NEXT_ITEM: agent_id = ' || p_agent_id
502 --|| ' item_type = ' || p_item_type
503 --|| ' classification = ' || p_classification);
504
505
506 --dbms_output.put_line('GET_NEXT_ITEM: pre select raw_agent_id');
507
508 select raw_agent_index into l_raw_agent_index from cct_qde_agent
509 where agent_id = p_agent_id ;
510
511 update cct_qde_agent
512 set is_get_work = 1, item_type = p_item_type,
513 classification = p_classification,
514 gw_req_time = sysdate
515 where agent_id = p_agent_id;
516
517 commit;
518 --dbms_output.put_line('GET_NEXT_ITEM: post select l_raw_agent_index= ' || l_raw_agent_index);
519 -- $$$$ change no NO_DATA_FOUND
520 declare cursor r1 is
521 select item_id, item_type, classification, route_result, is_route_to_all
522 from cct_qde_route_result_vw
523 where (item_type = p_item_type)
524 and ((p_classification is null) or (p_classification is not null
525 and classification = p_classification))
526 and ((is_route_to_all = 'Y') or
527 ((is_route_to_all = 'N') and (is_bit_set(l_raw_agent_index,route_result) = 0)));
528 begin
529 --dbms_output.put_line('GET_NEXT_ITEM: post declare cursor');
530 for r1_rec in r1 loop
531 begin
532
533 SAVEPOINT GET_NEXT_ITEM_SAVEPOINT_2;
534 --dbms_output.put_line('item_id = ' || r1_rec.item_id
535 --|| ' item_type = ' || r1_rec.item_type
536 --|| ' classification = ' || r1_rec.classification
537 --|| ' route_result = ' || r1_rec.route_result
538 --|| ' is_route_to_all = ' || r1_rec.is_route_to_all);
539 /*if r1_rec.is_route_to_all = 'N' then
540 begin
541 if is_bit_set(l_raw_agent_index,r1_rec.route_result) <> 0 then
542 raise BIT_NOT_SET;
543 end if;
544 end;
545 end if; */
546 --dbms_output.put_line('GET_NEXT_ITEM: pre select route result');
547 begin
548 select agent_id into l_agent_id from cct_qde_agent
549 where agent_id = p_agent_id
550 for update nowait;
551 exception
552 when NO_DATA_FOUND then
553 raise AGENT_NOT_FOUND ;
554 when RESOURCE_NOT_LOCKED then
555 raise AGENT_NOT_FOUND ;
556 end;
557
558 begin
559 select item_id,item_type,classification
560 into x_item_id, x_item_type, x_classification
561 from cct_qde_route_result
562 where item_id = r1_rec.item_id
563 for update nowait;
564 exception
565 when NO_DATA_FOUND then
566 raise RESULT_NOT_FOUND ;
567 when RESOURCE_NOT_LOCKED
568 then raise RESULT_NOT_FOUND ;
569 end;
570
571 --dbms_output.put_line('GET_NEXT_ITEM: post select route result');
572 --- $$$$ change to N0_DATA_FOUND
573 -- if sql%notfound then raise RESULT_NOT_FOUND;
574 -- end if;
575 --dbms_output.put_line('GET_NEXT_ITEM: pre delete route result');
576 delete from cct_qde_route_result where item_id = r1_rec.item_id;
577 --dbms_output.put_line('GET_NEXT_ITEM: post delete route result');
578 select item_kvp into x_kvp from cct_qde_data
579 where item_id = r1_rec.item_id;
580 delete from cct_qde_data where item_id = r1_rec.item_id;
581
582 update cct_qde_agent
583 set is_get_work = 0, item_type = null, classification = null
584 where agent_id = p_agent_id;
585
586 -- TODO Handle what happens if dequeue returns failure.
587 -- routing.stop reroute.
588 --dbms_output.put_line('GET_NEXT_ITEM: post dequeue result');
589 IF FND_API.To_Boolean( p_commit ) THEN
590 COMMIT WORK;
591 END IF;
592 exit;
593 exception
594 WHEN BIT_NOT_SET then
595 -- try to find match with other route result.
596 IF FND_API.To_Boolean( p_commit ) THEN
597 COMMIT WORK;
598 END IF;
599 WHEN AGENT_NOT_FOUND then
600 --possible that agent is assigned to some other media item as are result of a new route. exit.
601 commit;
602 exit;
603 WHEN RESULT_NOT_FOUND then
604 -- possible that route result was already assigned to an agent proceed to next media item.
605 commit;
606 WHEN OTHERS then
607 rollback to GET_NEXT_ITEM_SAVEPOINT_2;
608 x_err_num := SQLCODE;
609 x_err_msg := SUBSTR(SQLERRM, 1, 100);
610 x_msg_data :='GET_NEXT_ITEM: CCT_ERROR'
611 || ' ErrorCode = ' || x_err_num
612 || ' ErrorMsg = ' || x_err_msg;
613 --dbms_output.put_line(x_msg_data);
614 end;
615 end loop;
616 --dbms_output.put_line('GET_NEXT_ITEM: loop ended');
617 IF FND_API.To_Boolean( p_commit ) THEN
618 COMMIT WORK;
619 END IF;
620 end;
621 EXCEPTION
622 WHEN NO_DATA_FOUND THEN
623 -- return failure - agent not logged on.
624 rollback to GET_NEXT_ITEM_SAVEPOINT_1;
625 x_return_status := FND_API.G_RET_STS_ERROR ;
626 x_msg_count := 1;
627 x_err_num := SQLCODE;
628 x_err_msg := SUBSTR(SQLERRM, 1, 100);
629 x_msg_data :='GET_NEXT_ITEM: CCT_NO_AGENT_FOUND'
630 || ' ErrorCode = ' || x_err_num
631 || ' ErrorMsg = ' || x_err_msg;
632 --dbms_output.put_line(x_msg_data);
633
634 WHEN FND_API.G_EXC_ERROR THEN
635 rollback to GET_NEXT_ITEM_SAVEPOINT_1;
636 x_return_status := FND_API.G_RET_STS_ERROR ;
637 x_msg_count := 1;
638 x_err_num := SQLCODE;
639 x_err_msg := SUBSTR(SQLERRM, 1, 100);
640 x_msg_data :='GET_NEXT_ITEM: CCT_ERROR'
641 || ' ErrorCode = ' || x_err_num
642 || ' ErrorMsg = ' || x_err_msg;
643 --dbms_output.put_line(x_msg_data);
644 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
645 rollback to GET_NEXT_ITEM_SAVEPOINT_1;
646 x_return_status := FND_API.G_RET_STS_ERROR ;
647 x_msg_count := 1;
648 x_err_num := SQLCODE;
649 x_err_msg := SUBSTR(SQLERRM, 1, 100);
650 x_msg_data :='GET_NEXT_ITEM: CCT_ERROR'
651 || ' ErrorCode = ' || x_err_num
652 || ' ErrorMsg = ' || x_err_msg;
653 --dbms_output.put_line(x_msg_data);
654 WHEN OTHERS THEN
655 rollback to GET_NEXT_ITEM_SAVEPOINT_1;
656 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
657 x_msg_count := 1;
658 x_err_num := SQLCODE;
659 x_err_msg := SUBSTR(SQLERRM, 1, 100);
660 x_msg_data :='GET_NEXT_ITEM: CCT_ERROR'
661 || ' ErrorCode = ' || x_err_num
662 || ' ErrorMsg = ' || x_err_msg;
663 --dbms_output.put_line(x_msg_data);
664 END GET_NEXT_ITEM;
665
666
667
668 PROCEDURE UPDATE_AGENT_QUEUES
669 (
670 p_api_version IN NUMBER,
671 p_init_msg_list IN VARCHAR2,
672 p_commit IN VARCHAR2,
673 p_agent_id IN NUMBER,
674 p_item_type IN NUMBER,
675 x_return_status OUT NOCOPY VARCHAR2,
676 x_msg_count OUT NOCOPY NUMBER,
677 x_msg_data OUT NOCOPY VARCHAR2
678 )
679 IS
680 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_AGENT_QUEUES';
681 l_api_version CONSTANT NUMBER := 1.0;
682 AGENT_NOT_FOUND EXCEPTION;
683 BIT_NOT_SET EXCEPTION;
684 l_raw_agent_index RAW(2000);
685 l_curr_classification varchar2(1024);
686 l_prev_classification varchar2(1024);
687 l_prev_classification_count number;
688 l_any_classification_count number;
689 l_return_val number;
690 l_return_status varchar2(256);
691 l_msg_count number;
692 l_msg_data varchar2(256);
693 x_err_num NUMBER;
694 x_err_msg VARCHAR2(256);
695 TYPE ClassifyArr IS VARRAY(9999) of VARCHAR2(2000);
696 l_clArr ClassifyArr := ClassifyArr();
697 l_found varchar2(256);
698 i number;
699
700 BEGIN
701 IF NOT FND_API.Compatible_API_Call (l_api_version ,
702 p_api_version ,
703 l_api_name ,
704 G_PKG_NAME )
705 THEN
706 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
707 END IF;
708 -- Initialize message list if p_init_msg_list is set to TRUE.
709 IF FND_API.to_Boolean( p_init_msg_list ) THEN
710 FND_MSG_PUB.initialize;
711 END IF;
712 -- Initialize API return status to failure
713
714 SAVEPOINT UPDATE_QUEUE_COUNTS_SAVEPOINT;
715
716 x_return_status := FND_API.G_RET_STS_SUCCESS ;
717 x_msg_count := 0;
718 x_msg_data := null;
719
720 -- API body
721 --dbms_output.put_line('UPDATE_AGENT_QUEUES: pre select raw_agent_id');
722 select raw_agent_index into l_raw_agent_index from cct_qde_agent
723 where agent_id = p_agent_id;
724 --dbms_output.put_line('UPDATE_AGENT_QUEUES: post select raw_agent_id');
725 --- $$$$
726 -- if sql%notfound then raise AGENT_NOT_FOUND;
727 -- end if;
728
729 declare cursor r1 is
730 select item_id, item_type, classification, route_result, is_route_to_all
731 from cct_qde_route_result_vw
732 where (item_type = p_item_type)
733 order by classification;
734 begin
735 --dbms_output.put_line('UPDATE_AGENT_QUEUES: post declare cursor');
736 l_curr_classification := null;
737 l_prev_classification := null;
738 l_prev_classification_count := 0;
739 l_any_classification_count := 0;
740 for r1_rec in r1 loop
741 begin
742 --dbms_output.put_line('item_id = ' || r1_rec.item_id
743 --|| ' item_type = ' || r1_rec.item_type
744 --|| ' classification = ' || r1_rec.classification
745 --|| ' route_result = ' || r1_rec.route_result
746 --|| ' is_route_to_all = ' || r1_rec.is_route_to_all
747 --|| ' l_any_classification_count = ' || l_any_classification_count);
748
749 if r1_rec.is_route_to_all = 'N' then
750 begin
751 if is_bit_set(l_raw_agent_index,r1_rec.route_result) <> 0 then
752 raise BIT_NOT_SET;
753 end if;
754 end;
755 end if;
756 -- item routed to this agent.
757 l_curr_classification := r1_rec.classification;
758 if (l_prev_classification is null)
759 then
760 l_prev_classification := l_curr_classification;
761 end if;
762 if (l_curr_classification = l_prev_classification)
763 then
764 begin
765 l_prev_classification_count := l_prev_classification_count + 1;
766 l_any_classification_count := l_any_classification_count + 1;
767 end;
768 else
769 -- new classification
770 begin
771 -- update prev classification and count to db.
772 l_return_val := UPDATE_CLASSIFICATION_COUNT(FND_API.G_TRUE,
773 p_agent_id, p_item_type, l_prev_classification, l_prev_classification_count);
774 --dbms_output.put_line('update done');
775 --dbms_output.put_line('update done');
776 l_clArr.EXTEND;
777 --dbms_output.put_line('update done');
778 l_clArr(l_clArr.LAST) := l_prev_classification;
779 --dbms_output.put_line('Added to classificationArray' || l_prev_classification);
780 l_prev_classification_count :=0;
781 l_prev_classification := l_curr_classification;
782 l_prev_classification_count := l_prev_classification_count +1;
783 l_any_classification_count := l_any_classification_count + 1;
784 end;
785 end if;
786 exception
787 WHEN BIT_NOT_SET then
788 -- try to find match with other route result.
789 null;
790 WHEN OTHERS then
791 rollback to UPDATE_QUEUE_COUNTS_SAVEPOINT;
792 x_err_num := SQLCODE;
793 x_err_msg := SUBSTR(SQLERRM, 1, 100);
794 --dbms_output.put_line(x_err_num || x_err_msg);
795 end;
796 end loop;
797 --dbms_output.put_line('UPDATE_AGENT_QUEUES: loop ended');
798 --dbms_output.put_line(' l_prev_classification ' || l_prev_classification);
799 --dbms_output.put_line(' l_prev_classification_count ' || l_prev_classification_count);
800 --dbms_output.put_line(' l_any_classification_count ' || l_any_classification_count);
801 if ((l_prev_classification is not null) and (l_prev_classification_count <> 0))
802 then
803 l_return_val := UPDATE_CLASSIFICATION_COUNT(FND_API.G_TRUE, p_agent_id,
804 p_item_type,l_prev_classification, l_prev_classification_count);
805 l_clArr.EXTEND;
806 l_clArr(l_clArr.LAST) := l_prev_classification;
807 --dbms_output.put_line('Added to classificationArray' || l_prev_classification);
808 end if;
809 --dbms_output.put_line('l_clArr.LAST = ' || l_clArr.LAST);
810 l_return_val := UPDATE_CLASSIFICATION_COUNT(FND_API.G_TRUE, p_agent_id,
811 p_item_type,null, l_any_classification_count);
812
813 -- remove old classifications that are no longer valid.
814
815
816 declare cursor r2 is
817 select agent_queue_id , classification
818 from cct_qde_agent_queues
819 where (item_type = CCT_MEDIA_TYPES_PUB.GET_UWQ_MEDIA_TYPE_ID(p_item_type))
820 and classification is not null
821 and agent_id = p_agent_id ;
822 begin
823 for r2_rec in r2 loop
824 begin
825 --dbms_output.put_line('Processing classification' || r2_rec.classification);
826 l_found := 'false';
827 if (l_clArr.FIRST is not null) and (l_clArr.LAST is not null)
828 then
829 FOR i IN l_clArr.FIRST..l_clArr.LAST LOOP
830 begin
831 if r2_rec.classification = l_clArr(i)
832 then
833 l_found := 'true';
834 exit;
835 end if;
836 end;
837 end loop;
838 end if;
839 if l_found = 'false'
840 then
841 begin
842 --dbms_output.put_line('Deleting classification' || r2_rec.classification);
843 delete from cct_qde_agent_queues
844 where agent_queue_id = r2_rec.agent_queue_id;
845 IF FND_API.To_Boolean( p_commit ) THEN
846 COMMIT WORK;
847 END IF;
848 end;
849 end if;
850 end;
851 end loop;
852 end;
853
854 IF FND_API.To_Boolean( p_commit ) THEN
855 COMMIT WORK;
856 END IF;
857 end;
858 EXCEPTION
859 -- WHEN AGENT_NOT_FOUND THEN
860 WHEN NO_DATA_FOUND THEN
861 -- return failure - agent not logged on.
862
863 x_return_status := FND_API.G_RET_STS_ERROR ;
864 x_msg_count := 1;
865 x_err_num := SQLCODE;
866 x_err_msg := SUBSTR(SQLERRM, 1, 100);
867 x_msg_data :='UPDATE_AGENT_QUEUES: CCT_NO_AGENT_FOUND'
868 || ' ErrorCode = ' || x_err_num
869 || ' ErrorMsg = ' || x_err_msg;
870 --dbms_output.put_line(x_msg_data);
871 rollback to UPDATE_QUEUE_COUNTS_SAVEPOINT;
872 WHEN FND_API.G_EXC_ERROR THEN
873
874 x_return_status := FND_API.G_RET_STS_ERROR ;
875 x_msg_count := 1;
876 x_err_num := SQLCODE;
877 x_err_msg := SUBSTR(SQLERRM, 1, 100);
878 x_msg_data :='UPDATE_AGENT_QUEUES: CCT_ERROR'
879 || ' ErrorCode = ' || x_err_num
880 || ' ErrorMsg = ' || x_err_msg;
881 --dbms_output.put_line(x_msg_data);
882 rollback to UPDATE_QUEUE_COUNTS_SAVEPOINT;
883 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
884
885 x_return_status := FND_API.G_RET_STS_ERROR ;
886 x_msg_count := 1;
887 x_err_num := SQLCODE;
888 x_err_msg := SUBSTR(SQLERRM, 1, 100);
889 x_msg_data :='UPDATE_AGENT_QUEUES: CCT_ERROR'
890 || ' ErrorCode = ' || x_err_num
891 || ' ErrorMsg = ' || x_err_msg;
892 --dbms_output.put_line(x_msg_data);
893 rollback to UPDATE_QUEUE_COUNTS_SAVEPOINT;
894 WHEN OTHERS THEN
895
896 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
897 x_msg_count := 1;
898 x_err_num := SQLCODE;
899 x_err_msg := SUBSTR(SQLERRM, 1, 100);
900 x_msg_data :='UPDATE_AGENT_QUEUES: CCT_ERROR'
901 || ' ErrorCode = ' || x_err_num
902 || ' ErrorMsg = ' || x_err_msg;
903 --dbms_output.put_line(x_msg_data);
904 rollback to UPDATE_QUEUE_COUNTS_SAVEPOINT;
905 END UPDATE_AGENT_QUEUES;
906
907
908 PROCEDURE CREATE_AGENT_INDEX
909 (
910 p_commit IN VARCHAR2,
911 p_agent_id IN NUMBER,
912 x_agent_index OUT NOCOPY NUMBER,
913 x_raw_agent_index OUT NOCOPY RAW
914 )
915 IS
916 x_err_num NUMBER;
917 x_err_msg VARCHAR2(256);
918 l_max_agent_index number;
919 l_hex_agent_index varchar2(2000);
920 BEGIN
921 --dbms_output.put_line('CREATE_AGENT_INDEX:'
922 --|| ' agent_id = ' || p_agent_id);
923 lock table cct_qde_agent_global_lock in exclusive mode;
924 select max(agent_index) into l_max_agent_index
925 from cct_qde_agent;
926 if (l_max_agent_index is null) then
927 l_max_agent_index := -1;
928 end if;
929 x_agent_index := l_max_agent_index + 1;
930 --dbms_output.put_line('x_agent_index := ' || x_agent_index);
931 l_hex_agent_index := CCT_HEX_UTIL_PUB.dec_to_hex(x_agent_index);
932 --dbms_output.put_line('l_hex_agent_index := '||l_hex_agent_index );
933 x_raw_agent_index := hextoraw(l_hex_agent_index);
934 --dbms_output.put_line('x_raw_agent_index :=' || x_raw_agent_index);
935 insert into cct_qde_agent
936 ( agent_id, agent_index, raw_agent_index,
937 created_by, creation_date,
938 last_updated_by, last_update_date, last_update_login
939 )
940 values
941 (
942 p_agent_id, x_agent_index, x_raw_agent_index,
943 1, sysdate, 1, sysdate, 1
944 );
945 IF FND_API.To_Boolean( p_commit ) THEN
946 COMMIT WORK;
947 END IF;
948 EXCEPTION
949 WHEN OTHERS THEN
950 rollback;
951 x_err_num := SQLCODE;
952 x_err_msg := SUBSTR(SQLERRM, 1, 100);
953 --dbms_output.put_line(x_err_num || x_err_msg);
954 END CREATE_AGENT_INDEX;
955
956
957 PROCEDURE GET_AGENT_INDEX
958 (
959 p_api_version IN NUMBER,
960 p_init_msg_list IN VARCHAR2,
961 p_commit IN VARCHAR2,
962 p_agent_id IN NUMBER,
963 x_return_status OUT NOCOPY VARCHAR2,
964 x_msg_count OUT NOCOPY NUMBER,
965 x_msg_data OUT NOCOPY VARCHAR2,
966 x_agent_index OUT NOCOPY NUMBER,
967 x_raw_agent_index OUT NOCOPY RAW
968 )
969 IS
970 l_api_name CONSTANT VARCHAR2(30) := 'GET_AGENT_INDEX';
971 l_api_version CONSTANT NUMBER := 1.0;
972 x_err_num NUMBER;
973 x_err_msg VARCHAR2(256);
974 l_item_type number;
975 BEGIN
976 --dbms_output.put_line('GET_AGENT_INDEX:'
977 --|| ' agent_id = ' || p_agent_id);
978
979 IF NOT FND_API.Compatible_API_Call (l_api_version ,
980 p_api_version ,
981 l_api_name ,
982 G_PKG_NAME )
983 THEN
984 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
985 END IF;
986 -- Initialize message list if p_init_msg_list is set to TRUE.
987 IF FND_API.to_Boolean( p_init_msg_list ) THEN
988 FND_MSG_PUB.initialize;
989 END IF;
990 -- Initialize API return status to failure
991 SAVEPOINT GET_AGENT_INDEX_SP;
992
993 x_return_status := FND_API.G_RET_STS_SUCCESS ;
994 x_msg_count := 0;
995 x_msg_data := null;
996
997 select agent_index, raw_agent_index
998 into x_agent_index, x_raw_agent_index from cct_qde_agent
999 where agent_id = p_agent_id;
1000 IF FND_API.To_Boolean( p_commit ) THEN
1001 COMMIT WORK;
1002 END IF;
1003 EXCEPTION
1004 WHEN NO_DATA_FOUND then
1005 create_agent_index(p_commit,p_agent_id,x_agent_index,x_raw_agent_index);
1006 WHEN OTHERS THEN
1007 rollback;
1008 x_err_num := SQLCODE;
1009 x_err_msg := SUBSTR(SQLERRM, 1, 100);
1010 --dbms_output.put_line(x_err_num || x_err_msg);
1011 END GET_AGENT_INDEX;
1012
1013
1014
1015
1016 END CCT_QDE_PUB;