[Home] [Help]
PACKAGE BODY: APPS.CCT_ROUTINGWORKFLOW_PUB
Source
1 PACKAGE BODY CCT_RoutingWorkflow_PUB as
2 /* $Header: cctprwfb.pls 120.1 2005/11/14 13:47:36 ibyon noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CCT_RoutingWorkflow_PUB';
5 G_ITEMTYPE CONSTANT VARCHAR2(30) := 'ALLROUTE';
6 G_MAXAGENTS CONSTANT INTEGER := 1000;
7 G_PROCESS_NAME VARCHAR2(200) := NULL;
8 G_TEST_MODE VARCHAR2(20) := 'OFF';
9
10 procedure KevinTest (
11 p_number IN NUMBER
12 , p_varchar out nocopy VARCHAR2
13 , p_varchar2 IN out nocopy VARCHAR2
14 ) IS
15 begin
16 p_varchar := p_varchar2 || ' + ' || p_varchar2;
17 p_varchar2 := p_varchar2 || ' + ' || TO_CHAR(p_number);
18 return;
19 end;
20
21
22 /* ---------------------------------------------------------------------
23 Local Procedures
24 * ------------------------------------------------------------------ */
25 procedure Randomize_Agents(
26 x_agents_tbl IN out nocopy agent_tbl_type
27 , totalCount IN NUMBER
28 ) is
29 l_random NUMBER;
30 l_temp NUMBER;
31 l_maxCounter NUMBER:=G_MAXAGENTS;
32 begin
33 if (totalCount<G_MAXAGENTs) then
34 l_maxCounter:=totalCount-1;
35 end if;
36 FOR counter in 1..l_maxCounter LOOP
37 -- pick a random number between counter and totalCount
38 l_random := CCT_Random_UTIL.Rand_Between(counter, totalCount);
39 -- swap positions of counter object and randomly picked object
40 l_temp := x_agents_tbl(l_random);
41 x_agents_tbl(l_random) := x_agents_tbl(counter);
42 x_agents_tbl(counter) := l_temp;
43 END LOOP;
44 Exception
45 When others then
46 null;
47 end Randomize_Agents;
48
49
50
51 /* -----------------------------------------------------------------------
52 Start of comments
53 API Name : Launch_Workflow_Version2
54 Type : Public
55 Description : Launch a Workflow process to route the specified call
56 Wait for workflow completion
57 Get results and send back to Routing Module on Server
58 Parameters :
59 l_return_val is a concatenation of call_id, customer_name, product_name,
60 and the list of agents. The delimiter is ';:;'
61
62 Version : Initial Version 1.0
63
64 End of comments
65 * ----------------------------------------------------------------------*/
66
67 PROCEDURE Launch_Workflow_Version2 (
68 p_MCM_ID IN NUMBER
69 , p_call_ID IN VARCHAR2
70 , p_ANI IN VARCHAR2
71 , p_contact_num IN out nocopy VARCHAR2
72 , p_customer_name IN out nocopy VARCHAR2
73 , p_product_name IN out nocopy VARCHAR2
74 , p_contract_num IN out nocopy VARCHAR2
75 , p_customer_ID IN out nocopy NUMBER
76 , p_customer_num IN out nocopy VARCHAR2
77 , p_DNIS IN VARCHAR2
78 , p_inventory_item_ID IN out nocopy NUMBER
79 , p_invoice_num IN out nocopy VARCHAR2
80 , p_lot_num IN out nocopy VARCHAR2
81 , p_order_num IN out nocopy NUMBER
82 , p_problem_code IN out nocopy VARCHAR2
83 , p_po_num IN out nocopy VARCHAR2
84 , p_reference_num IN out nocopy VARCHAR2
85 , p_revision_num IN out nocopy VARCHAR2
86 , p_rma_num IN out nocopy NUMBER
87 , p_screen_pop_type IN out nocopy VARCHAR2
88 , p_serial_num IN out nocopy VARCHAR2
89 , p_sr_num IN out nocopy VARCHAR2
90 , p_system_name IN out nocopy VARCHAR2
91 , p_datetime IN VARCHAR2
92 , p_account_code IN out nocopy VARCHAR2
93 , p_preferred_id IN out nocopy NUMBER
94 , p_promotion_code IN out nocopy VARCHAR2
95 , p_quote_num IN out nocopy VARCHAR2
96 , p_competency_lang IN VARCHAR2
97 , p_competency_know IN VARCHAR2
98 , p_competency_prod IN VARCHAR2
99 , p_customer_product_ID out nocopy NUMBER
100 , p_account_num IN out nocopy NUMBER
101 , p_site_num IN out nocopy NUMBER
102 , p_repair_num IN out nocopy NUMBER
103 , p_defect_num IN out nocopy NUMBER
104 , p_cust_status IN out nocopy VARCHAR2
105 , p_event_code IN out nocopy VARCHAR2
106 , p_coll_req IN out nocopy VARCHAR2
107 , p_classification IN out nocopy VARCHAR2
108 , p_email_icntr_map_id IN out nocopy NUMBER
109 , p_return_val out nocopy VARCHAR2
110 )
111 IS
112
113 l_api_name CONSTANT VARCHAR2(30) := 'Launch_Workflow_Version2';
114 l_api_version CONSTANT NUMBER := 1.0;
115
116 -- l_msg_count NUMBER;
117 -- l_msg_data VARCHAR2(2000);
118
119 l_dummy VARCHAR2(240);
120 l_wf_process_id NUMBER;
121 l_nowait BOOLEAN := FALSE;
122 l_process_status VARCHAR2(30);
123 l_process_result VARCHAR2(30);
124 l_num_dummy NUMBER;
125 l_datetime DATE := TO_DATE(p_datetime, 'yyyy-mm-dd hh24:mi:ss');
126
127 l_return_status VARCHAR2(100);
128 l_agent VARCHAR2(32);
129 l_agent1 VARCHAR2(32);
130 l_agent2 VARCHAR2(32);
131 l_agent3 VARCHAR2(32);
132 l_agent4 VARCHAR2(32);
133 l_agent5 VARCHAR2(32);
134 l_agent6 VARCHAR2(32);
135 l_agent7 VARCHAR2(32);
136 l_agent8 VARCHAR2(32);
137 l_agent9 VARCHAR2(32);
138 l_agent10 VARCHAR2(32);
139 l_delimiter VARCHAR2(3) := ';:;' ;
140 p_agent_list VARCHAR2(512);
141
142 l_WORKFLOW_IN_PROGRESS EXCEPTION;
143
144 CURSOR l_WorkflowProcID_csr IS
145 SELECT cct_wf_process_id_s.nextval
146 FROM dual;
147
148 l_itemkey VARCHAR2(240);
149 l_itemtype VARCHAR2(30) := G_ITEMTYPE;
150
151 l_counter NUMBER := 0;
152 l_numAgents NUMBER := 5;
153 l_no_result_Exception EXCEPTION;
154
155 CURSOR l_results_csr IS
156 SELECT agent_ID
157 FROM CCT_ROUTING_RESULTS
158 WHERE call_ID = p_call_ID
159 ORDER BY sort_num;
160
161 err_name VARCHAR2(30);
162 err_msg VARCHAR2(2000);
163 err_stack VARCHAR2(32000);
164
165 BEGIN
166 -- Initialize return status to SUCCESS
167 l_return_status := FND_API.G_RET_STS_SUCCESS;
168
169 -- Get the new workflow process ID
170 OPEN l_WorkflowProcID_csr;
171 FETCH l_WorkflowProcID_csr INTO l_wf_process_id;
172 CLOSE l_WorkflowProcID_csr;
173
174 -- Construct the unique item key
175 --This step is redundant for Synchronous Workflow
176 -- 2:04 PM 2/13/99 Savvas Xenophontos
177 l_itemkey := Encode_Call_Itemkey(p_call_ID, l_wf_process_ID);
178 IF G_TEST_MODE <> 'ON' THEN
179 l_itemkey := wf_engine.eng_synch;
180 END IF;
181
182 -- Create and launch the Workflow process
183 WF_ENGINE.CreateProcess(
184 itemtype => l_itemtype,
185 itemkey => l_itemkey,
186 process => null );
187
188 -- Set Item Attributes
189 WF_ENGINE.SetItemAttrNumber (l_itemtype, l_itemkey, 'MCM_ID', p_mcm_id);
190 WF_ENGINE.SetItemAttrText (l_itemtype, l_itemkey, 'OCCTMEDIAITEMID', p_call_id);
191
192 WF_ENGINE.SetItemAttrText (l_itemtype, l_itemkey, 'OCCTANI', p_ANI);
193 WF_ENGINE.SetItemAttrText (l_itemtype, l_itemkey, 'CONTACTNUM',p_contact_num);
194 WF_ENGINE.SetItemAttrText (l_itemtype, l_itemkey, 'CONTRACTNUM',
195 p_contract_num);
196 WF_ENGINE.SetItemAttrNumber(l_itemtype, l_itemkey, CCT_INTERACTIONKEYS_PUB.KEY_CUSTOMER_ID, p_customer_ID);
197 WF_ENGINE.SetItemAttrText (l_itemtype, l_itemkey, 'CUSTOMERNAME',
198 p_customer_name);
199 WF_ENGINE.SetItemAttrText (l_itemtype, l_itemkey, 'CUSTOMERNUM',
200 p_customer_num);
201 WF_ENGINE.SetItemAttrNumber(l_itemtype, l_itemkey, 'CUSTOMERPRODUCTID',
202 p_customer_product_ID);
203 WF_ENGINE.SetItemAttrText (l_itemtype, l_itemkey, 'OCCTDNIS', p_DNIS);
204 WF_ENGINE.SetItemAttrNumber(l_itemtype, l_itemkey, 'INVENTORYITEMID',
205 p_inventory_item_ID);
206 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'INVOICENUM', p_invoice_num);
207 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'LOTNUM', p_lot_num);
208 WF_ENGINE.SetItemAttrNumber(l_itemtype, l_itemkey, 'ORDERNUM', p_order_num);
209 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'PROBCODE',
210 p_problem_code);
211 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'PRODUCTNAME',
212 p_product_name);
213 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'PURCHASEORDERNUM', p_po_num);
214 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'REFERENCENUM', p_reference_num);
215 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'REVISONNUM', p_revision_num);
216 WF_ENGINE.SetItemAttrNumber(l_itemtype, l_itemkey, 'RMANUM', p_rma_num);
217 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'SCRPOPTYP',
218 p_screen_pop_type);
219 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'SERIALNUM', p_serial_num);
220 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'SERVICEREQUESTNUM', p_sr_num);
221 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'SYSTEMNAME', p_system_name);
222 WF_ENGINE.SetItemAttrDate (l_itemtype, l_itemkey, 'OCCTCREATIONTIME', l_datetime);
223 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'ACCOUNTCODE',
224 p_account_code);
225 WF_ENGINE.SetItemAttrNumber(l_itemtype, l_itemkey, 'PREFERREDID',
226 p_preferred_ID);
227 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'PROMOTIONCODE',
228 p_promotion_code);
229 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'QUOTENUM', p_quote_num);
230
231 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'LANGUAGECOMPETENCY',
232 p_competency_lang);
233 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'KNOWLEDGECOMPETENCY',
234 p_competency_know);
235 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'PRODUCTCOMPETENCY',
236 p_competency_prod);
237 WF_ENGINE.SetItemAttrNumber(l_itemtype, l_itemkey, 'ACCOUNTNUM',
238 p_account_num);
239 WF_ENGINE.SetItemAttrNumber(l_itemtype, l_itemkey, 'SITENUM',
240 p_site_num);
241 WF_ENGINE.SetItemAttrNumber(l_itemtype, l_itemkey, 'REPAIRNUM',
242 p_repair_num);
243 WF_ENGINE.SetItemAttrNumber(l_itemtype, l_itemkey, 'DEFECTNUM',
244 p_defect_num);
245 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'CUSTOMERSTATUS',
246 p_cust_status);
247 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'EVENTCODE',
248 p_event_code);
249 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'COLLATERALREQ',
250 p_coll_req);
251 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'OCCTCLASSIFICATION',
252 p_classification);
253 WF_ENGINE.SetItemAttrText(l_itemtype, l_itemkey, 'EMAILICENTERMAPID',
254 p_email_icntr_map_id);
255
256 -- Set the engine threshold to a very high number to prevent
257 -- this process from ever being deferred
258 WF_ENGINE.THRESHOLD := 999999;
259
260 --
261 -- Start the process
262 -- This procedure call will return only after the process
263 -- completes since only function activities are used.
264 WF_ENGINE.StartProcess(l_itemtype, l_itemkey );
265
266 /************************************************************
267 IF FND_API.To_Boolean(p_commit ) THEN
268 --COMMIT WORK;
269 END IF;
270 ************************************************************/
271 -- Get all the OUT or IN OUT variables from the Workflow
272
273 p_contact_num := WF_ENGINE.GetItemAttrText (l_itemtype, l_itemkey
274 , 'CONTACTNUM');
275 p_customer_name := WF_ENGINE.GetItemAttrNumber(l_itemtype,l_itemkey,
276 'CUSTOMERNAME');
277 p_product_name := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey
278 , 'PRODUCTNAME');
279 p_contract_num := WF_ENGINE.GetItemAttrText (l_itemtype, l_itemkey
280 , 'CONTRACTNUM');
281 p_customer_ID := WF_ENGINE.GetItemAttrNumber(l_itemtype,l_itemkey,CCT_INTERACTIONKEYS_PUB.KEY_CUSTOMER_ID);
282 p_customer_num := WF_ENGINE.GetItemAttrText (l_itemtype, l_itemkey
283 , 'CUSTOMERNUM');
284 p_inventory_item_ID := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemkey
285 , 'INVENTORYITEMID');
286 p_invoice_num := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey, 'INVOICENUM');
287 p_lot_num := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey, 'LOTNUM');
288 p_order_num := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemkey
289 , 'ORDERNUM');
290 p_problem_code := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey
291 , 'PROBCODE');
292 p_po_num := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey, 'PURCHASEORDERNUM');
293 p_reference_num := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey
294 , 'REFERENCENUM');
295 p_revision_num := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey, 'REVISONNUM');
296 p_rma_num := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemkey, 'RMANUM');
297 p_screen_pop_type := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey
298 , 'SCRPOPTYP');
299 p_serial_num := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey, 'SERIALNUM');
300 p_sr_num := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey, 'SERVICEREQUESTNUM');
301 p_system_name := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey, 'SYSTEMNAME');
302 p_account_code := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey
303 , 'ACCOUNTCODE');
304 p_preferred_ID := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemkey
305 , 'PREFERREDID');
306 p_promotion_code := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey
307 , 'PROMOTIONCODE');
308 p_quote_num := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey, 'QUOTENUM');
309 p_customer_product_ID := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemkey,
310 'CUSTOMERPRODUCTID');
311 p_account_num := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemkey
312 , 'ACCOUNTNUM');
313 p_site_num := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemkey, 'SITENUM');
314 p_repair_num := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemkey
315 , 'REPAIRNUM');
316 p_defect_num := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemkey
317 , 'DEFECTNUM');
318 p_cust_status := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey
319 , 'CUSTOMERSTATUS');
320 p_event_code := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey
321 , 'EVENTCODE');
322 p_coll_req := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey, 'COLLATERALREQ');
323 p_classification := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey
324 , 'OCCTCLASSIFICATION');
325 p_email_icntr_map_id := WF_ENGINE.GetItemAttrText(l_itemtype, l_itemkey
326 , 'EMAILICENTERMAPID');
327
328 /* ***************************************************************
329 Retreive results from CCT_ROUTING_RESULTS tables
330 *************************************************************** */
331 begin
332
333 open l_results_csr;
334
335 FOR counter in 1..G_MAXAGENTS LOOP
336 fetch l_results_csr into l_agent;
337 if l_results_csr%NOTFOUND then raise l_no_result_Exception; end if;
338 if counter = 1 then
339 p_agent_list := l_agent;
340 else
341 p_agent_list := p_agent_list || l_delimiter || l_agent;
342 end if;
343
344 END LOOP;
345
346 raise l_no_result_exception;
347 exception
348 WHEN l_no_result_exception THEN
349 CLOSE l_results_csr;
350 -- p_return_val := p_return_val || l_delimiter || p_agent_list;
351 p_return_val := p_agent_list;
352 -- delete the results for this call from the CCT_ROUTING_RESULTS
353 begin
354 DELETE from CCT_ROUTING_RESULTS
355 WHERE call_ID = p_call_ID;
356 --commit work;
357 exception
358 WHEN OTHERS THEN
359 null;
360 end;
361 end;
362
363 EXCEPTION
364 WHEN OTHERS THEN
365 WF_CORE.Get_error(err_name, err_msg, err_stack);
366 if (err_name IS NULL) then
367 l_return_status := 'ORA ERROR : err_name is ' ||
368 to_char(sqlcode) || ' and err_msg is '
369 || sqlerrm;
370
371 else
372 l_return_status := 'WF ERROR : err_name is ' ||
373 err_name || ' and err_msg is ' || err_msg;
374 end if;
375 -- delete the results for this call from the CCT_ROUTING_RESULTS
376 begin
380 exception
377 DELETE from CCT_ROUTING_RESULTS
378 WHERE call_ID = p_call_ID;
379 --commit work;
381 WHEN OTHERS THEN
382 null;
383 end;
384
385 END Launch_Workflow_Version2 ;
386
387
388 -- ---------------------------------------------------------------------------
389 -- Start of comments
390 -- API Name : Cancel_Workflow
391 -- Type : Public
392 -- Description : Abort an active Workflow process for the given call
393 -- request.
394 -- Pre-reqs :
395 --
396 -- Version : Initial Version 1.0
397 --
398 -- Notes :
399 --
400 -- End of comments
401 -- --------------------------------------------------------------------------
402 PROCEDURE Cancel_Workflow (
403 p_api_version IN NUMBER
404 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
405 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
406 , p_return_status out nocopy VARCHAR2
407 , p_msg_count out nocopy NUMBER
408 , p_msg_data out nocopy VARCHAR2
409 , p_call_ID IN NUMBER
410 , p_wf_process_id IN NUMBER
411 , p_user_id IN NUMBER
412 ) IS
413 l_itemtype VARCHAR2(30) := G_ITEMTYPE;
414 l_itemkey VARCHAR2(30) :=
415 Encode_Call_ItemKey(p_call_ID, p_wf_process_ID);
416 l_api_name CONSTANT VARCHAR2(30) := 'Cancel_Workflow';
417 l_api_version CONSTANT NUMBER := 1.0;
418
419 l_not_active EXCEPTION;
420 BEGIN
421 -- API Savepoint
422 SAVEPOINT Cancel_Workflow_PUB;
423
424 -- Check version number
425 IF NOT FND_API.Compatible_API_Call(
426 l_api_version,
427 p_api_version,
428 l_api_name,
429 G_PKG_NAME ) THEN
430 raise FND_API.G_EXC_UNEXPECTED_ERROR;
431 END IF;
432
433 IF FND_API.to_Boolean( p_init_msg_list ) THEN
434 FND_MSG_PUB.initialize;
435 END IF;
436
437 -- Initialize return status to SUCCESS
438 p_return_status := FND_API.G_RET_STS_SUCCESS;
439
440 /**********************************************************************
441 --
442 -- Make sure that the item is still active
443 --
444 IF (CS_Workflow_PKG.Is_Routing_Item_Active (
445 p_request_number => p_request_number,
446 p_wf_process_id => p_wf_process_id ) = 'N') THEN
447 raise l_NOT_ACTIVE;
448 END IF;
449 **********************************************************************/
450
451 -- Call Workflow API to abort the process
452 WF_ENGINE.AbortProcess(
453 itemtype => l_itemtype,
454 itemkey => l_itemkey );
455
456 EXCEPTION
457 WHEN l_NOT_ACTIVE THEN
458 ROLLBACK TO Cancel_Workflow_PUB;
459 p_return_status := FND_API.G_RET_STS_ERROR;
460 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
461 FND_MESSAGE.SET_NAME('CCT', 'CCT_ROUTING_WFLOW_NOT_ACTIVE');
462 FND_MSG_PUB.Add;
463 END IF;
464 FND_MSG_PUB.Count_And_Get(
465 p_count => p_msg_count,
466 p_data => p_msg_data,
467 p_encoded => FND_API.G_FALSE );
468
469 WHEN OTHERS THEN
470 ROLLBACK TO Cancel_Workflow_PUB;
471 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
472 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
473 FND_MESSAGE.SET_NAME('CCT', 'CCT_ROUTING_WFLOW_NOT_ACTIVE');
474 FND_MSG_PUB.Add;
475 END IF;
476 FND_MSG_PUB.Count_And_Get(
477 p_count => p_msg_count,
478 p_data => p_msg_data,
479 p_encoded => FND_API.G_FALSE );
480
481 END Cancel_Workflow;
482
483
484 -- ------------------------------------------------------------------------
485 -- Start of comments
486 -- API Name : Selector
487 -- Type : Public
488 -- Description : Selects the root process to run.
489 --
490 -- Version : Initial Version 1.0
491 --
492 -- ------------------------------------------------------------------------
493 procedure Selector (
494 itemtype in varchar2
495 , itemkey in varchar2
496 , actid in number
497 , funmode in varchar2
498 , resultout in out nocopy varchar2
499 ) IS
500 l_select_process VARCHAR2(30) := 'SELECTOR';
501 begin
502
503 select WIA.TEXT_DEFAULT
504 into resultout
505 from WF_ITEM_ATTRIBUTES WIA
506 where WIA.ITEM_TYPE = itemtype
507 and WIA.NAME = l_select_process;
508 exception
509 WHEN OTHERS THEN
510 null; -- resultout value is not changed.
511
512 end Selector;
513
514
515 -- ------------------------------------------------------------------------
516 -- Start of comments
517 -- API Name : Get_Agents
518 -- Type : Public
519 -- Description : Select the group of agents as determined by the Filter flags
520 -- set.
521 --
522 -- Version : Initial Version 1.0
523 -- Notes : Create a dynamic sql statement by concatenating the clause(s)
524 -- needed for each flag that is set.
525 -- The dynamic sql statement is run on the table CCT_TEMPAGENTS
526 -- and the resulting agents are inserted into CCT_ROUTING_RESULTS
527 -- with sort order info added.
528 -- ------------------------------------------------------------------------
529 procedure Get_Agents (
533 , funmode in varchar2
530 itemtype in varchar2
531 , itemkey in varchar2
532 , actid in number
534 , resultout in out nocopy varchar2
535 ) IS
536 l_call_ID VARCHAR2(40);
537 l_wf_process_ID NUMBER;
538 l_dynamic_select VARCHAR2(4000);
539 l_filter_flag VARCHAR2(1);
540 l_filter_type VARCHAR2(40);
541 l_select_csr INTEGER;
542 l_sort_num NUMBER := 0;
543 l_dummy INTEGER;
544 l_agent_ID VARCHAR(32);
545 l_agents_tbl agent_tbl_type;
546 l_available VARCHAR2(5) := '''T''';
547 l_MCM_ID NUMBER ;
548 l_default_select VARCHAR2(200);
549 l_apos VARCHAR2(4) := '''';
550 l_reroute VARCHAR2(20);
551
552 CURSOR l_filters_csr IS
553 select FILTER_TYPE from CCT_TEMPAGENTS
554 where call_id = l_call_id
555 and agent_id = '-1' ;
556
557
558 BEGIN
559 -- set default result
560 -- If reouted do not reroute again
561 l_reroute := WF_ENGINE.GetItemAttrText(
562 itemtype
563 , itemkey
564 ,'REROUTED');
565
566 resultout := wf_engine.eng_completed ;
567
568 IF ( l_reroute IS NULL) OR ( l_reroute <> 'Y')
569 THEN
570
571 -- get the callid and wf_process_id from the item key
572 l_call_ID := WF_ENGINE.GetItemAttrText(itemtype, itemkey,
573 'OCCTMEDIAITEMID');
574 l_MCM_ID := WF_ENGINE.GetItemAttrNumber(itemtype, itemkey,
575 'MCM_ID');
576 l_default_select :=
577 'Select distinct(A.agent_id) from cct_tempagents a '||
578 'where a.call_id ='||l_apos||l_call_ID||l_apos||
579 ' and a.agent_id <> '||l_apos||-1||l_apos;
580
581 -- Start the dynamic string
582 l_dynamic_select := l_default_select;
583
584
585 FOR l_filter IN l_filters_csr LOOP
586 l_dynamic_select := l_dynamic_select ||
587 ' AND A.agent_ID IN (SELECT agent_ID from CCT_TEMPAGENTS ' ||
588 ' WHERE CALL_ID = ' || l_apos || l_call_ID || l_apos ||
589 ' AND FILTER_TYPE = ' || l_apos || l_filter.FILTER_TYPE || l_apos || ')';
590 END LOOP;
591
592
593 -- now run the select clause using dynamic sql
594 begin
595 l_select_csr := DBMS_SQL.OPEN_CURSOR;
596
597 DBMS_SQL.PARSE(l_select_csr, l_dynamic_select, DBMS_SQL.native);
598 DBMS_SQL.DEFINE_COLUMN(l_select_csr, 1, l_agent_ID, 32);
599 l_dummy := DBMS_SQL.EXECUTE(l_select_csr);
600
601 l_sort_num := 0;
602 LOOP
603 if DBMS_SQL.FETCH_ROWS(l_select_csr) = 0 then
604 EXIT;
605 end if;
606
607 DBMS_SQL.COLUMN_VALUE(l_select_csr, 1, l_agent_ID);
608
609 -- insert the cursor record into the l_agents_tbl Table
610 l_sort_num := l_sort_num + 1;
611 l_agents_tbl(l_sort_num) := l_agent_ID;
612
613 END LOOP;
614
615 IF (l_sort_num = 0) THEN
616 --
617 -- no agents were found
618 -- use any call center agent
619 l_dynamic_select := l_default_select;
620
621 DBMS_SQL.PARSE(l_select_csr, l_dynamic_select, DBMS_SQL.NATIVE);
622 DBMS_SQL.DEFINE_COLUMN(l_select_csr, 1, l_agent_ID, 32);
623 l_dummy := DBMS_SQL.EXECUTE(l_select_csr);
624
625 l_sort_num := 0;
626
627 LOOP
628 if DBMS_SQL.FETCH_ROWS(l_select_csr) = 0 then
629 EXIT;
630 end if;
631
632 DBMS_SQL.COLUMN_VALUE(l_select_csr, 1, l_agent_ID);
633
634 -- insert the cursor record into the l_agents_tbl Table
635 l_sort_num := l_sort_num + 1;
636 l_agents_tbl(l_sort_num) := l_agent_ID;
637
638 END LOOP;
639 END IF;
640
641 -- Close the cursor
642 DBMS_SQL.CLOSE_CURSOR(l_select_csr);
643
644 -- delete the entries for this call from CCT_TEMPAGENTS
645 -- delete the entries for this call from CCT_TEMPAGENTS
646 -- Donot delete if in testmode, then need to explicitly delete this table later
647 IF G_TEST_MODE <> 'ON' THEN
648 DELETE from CCT_TEMPAGENTS
649 WHERE CALL_ID = l_call_ID;
650 END IF;
651
652 -- do the randomization to reduce number of agents returned to TEN
653 if (l_sort_num <= G_MAXAGENTS) then
654 -- randomization needed insert all into CCT_ROUTING_RESULTS
655 Randomize_Agents(l_agents_tbl, l_sort_num);
656 FOR counter IN 1..l_sort_num
657 LOOP
658 INSERT INTO CCT_ROUTING_RESULTS
659 (call_id,itemkey,agent_id,sort_num,
660 routing_Result_id,last_update_date,last_updated_by,
661 creation_Date,created_by)
662 VALUES (l_call_ID, itemkey,l_agents_tbl(counter), counter,
663 1001,sysdate,1,sysdate,1);
664 END LOOP;
665 else
666 -- do the randomization to reduce number of agents returned to TEN
667 Randomize_Agents(l_agents_tbl, l_sort_num);
668 FOR counter IN 1..G_MAXAGENTS
669 LOOP
670 INSERT INTO CCT_ROUTING_RESULTS
671 (call_id,itemkey,agent_id,sort_num,
672 routing_Result_id,last_update_date,last_updated_by,
673 creation_Date,created_by)
674 VALUES (l_call_ID, itemkey,l_agents_tbl(counter), counter,
675 1001,sysdate,1,sysdate,1);
676 END LOOP;
677 null;
678 end if;
679
680 --commit work;
681 exception
685 RAISE;
682 WHEN OTHERS THEN
683 -- close the cursor
684 DBMS_SQL.CLOSE_CURSOR (l_select_csr);
686 end;
687 END IF;
688
689 END Get_Agents;
690
691
692 -- --------------------------------------------------------------------------
693 -- Start of comments
694 -- API Name : Decode_Call_Itemkey
695 -- Type : Public
696 -- Description : Given an encoded Routing Request itemkey, this procedure
697 -- will return the components of the key - call ID, and
698 -- workflow process ID.
699 -- Pre-reqs : None
700 -- Parameters :
701 -- p_itemkey IN VARCHAR2 Requried
702 -- p_call_ID out nocopy NUMBER Required
703 -- p_wf_process_id out nocopy NUMBER Required
704 --
705 -- Version : Initial Version 1.0
706 --
707 -- Notes: :
708 --
709 -- End of comments
710 -- ----------------------------------------------------------------------------
711
712 PROCEDURE Decode_Call_Itemkey(
713 p_itemkey IN VARCHAR2
714 , p_call_ID out nocopy VARCHAR2
715 , p_wf_process_ID out nocopy NUMBER
716 ) IS
717 l_dash_pos NUMBER;
718 BEGIN
719 p_call_ID := NULL;
720 p_wf_process_ID := NULL;
721
722 l_dash_pos := instr(p_itemkey, '-');
723 IF (l_dash_pos = 0) THEN
724 return;
725 END IF;
726
727 p_call_ID := substr(p_itemkey, 1, l_dash_pos - 1);
728 p_wf_process_id := to_number(substr(p_itemkey,
729 l_dash_pos + 1,
730 length(p_itemkey) - l_dash_pos));
731 END Decode_Call_Itemkey;
732
733
734
735 -- ---------------------------------------------------------------------------
736 -- Start of comments
737 -- API Name : Encode_Call_Itemkey
738 -- Type : Public
739 -- Description : Given a Call ID and a Workflow process
740 -- ID, this procedure will construct the corresponding
741 -- itemkey for the Call item type.
742 -- Pre-reqs : None
743 -- Parameters :
744 -- p_call_ID IN NUMBER Required
745 -- p_wf_process_id IN NUMBER Required
746 -- Return Value
747 -- itemkey VARCHAR2
748 --
749 -- Version : Initial Version 1.0
750 --
751 -- End of comments
752 -- ---------------------------------------------------------------------------
753
754 FUNCTION Encode_Call_Itemkey(
755 p_call_ID IN VARCHAR2
756 , p_wf_process_id IN NUMBER
757 ) return VARCHAR2
758 IS
759 l_returnVal VARCHAR2(100);
760 BEGIN
761 l_returnVal := p_call_ID || '-' || TO_CHAR(p_wf_process_id);
762 return l_returnVal;
763 END Encode_Call_Itemkey;
764
765 FUNCTION AddParam(p_name VARCHAR2, p_val varchar2, p_type varchar2) return integer is
766 begin
767 num_params := num_params + 1;
768 param_name(num_params) := upper(p_name);
769 param_val(num_params) := p_val;
770 param_type(num_params) := p_type;
771 return num_params;
772 end AddParam;
773
774 procedure init_param_table is
775 begin
776 param_name.delete;
777 param_val.delete;
778 param_type.delete;
779 num_params := 0;
780 end init_param_table;
781
782 procedure setParamValue(p_param varchar2, p_val varchar2) is
783 begin
784 for i in 1..param_name.count loop
785 if param_name(i) = p_param then
786 param_val(i) := p_val;
787 exit;
788 end if;
789 end loop;
790 end setParamValue;
791
792 FUNCTION getParamValue(p_name varchar2) return varchar2 is
793 begin
794 for i in 1..param_name.count loop
795 if param_name(i) = p_name then
796 return param_val(i);
797 end if;
798 end loop;
799 return null;
800 end getParamValue;
801
802 FUNCTION getParamType(p_name varchar2) return varchar2 is
803 begin
804 for i in 1..param_name.count loop
805 if param_name(i) = p_name then
806 return param_type(i);
807 end if;
808 end loop;
809 return null;
810 end getParamType;
811
812 PROCEDURE Varchar2Table (InString IN varchar2) IS
813 idx BINARY_INTEGER := 1;
814 pos number := 1;
815 str varchar2(32766);
816 flag boolean := true;
817 BEGIN
818 paramHash.delete;
819 str := InString;
820 while flag
821 loop
822 pos := instr(str,'::');
823 if pos = 1 then
824 paramHash (idx) := -1; -- When no response
825 idx := idx + 1;
826 elsif pos <> 0 then
827 paramHash (idx) := substr(str,1,pos-1);
828 idx := idx + 1;
829 else
830 paramHash(idx) := substr(str,1);
831 flag := false;
832 end if;
833 str := substr(str,pos+2);
834 end loop;
835 END;
836
837 FUNCTION Launch_Workflow_Version4
838 return VARCHAR2
839 IS
840
841 l_api_name CONSTANT VARCHAR2(30) := 'Launch_Workflow_Version4';
842 l_api_version CONSTANT NUMBER := 1.0;
843 l_return_status VARCHAR2(100);
844 l_no_result_Exception EXCEPTION;
845 l_wf_process_id NUMBER;
846 l_itemkey VARCHAR2(240);
847 l_itemtype VARCHAR2(30) := G_ITEMTYPE;
848 p_call_ID VARCHAR2(200) := getParamValue('OCCTMEDIAITEMID');
849
850 l_counter NUMBER := 0;
851 l_numAgents NUMBER := 5;
852 l_agent VARCHAR2(32);
853 p_agent_list VARCHAR2(4000);
854 l_delimiter VARCHAR2(3) := ';:;' ;
855
859 SELECT cct_wf_process_id_s.nextval
856 l_WORKFLOW_IN_PROGRESS EXCEPTION;
857
858 CURSOR l_WorkflowProcID_csr IS
860 FROM dual;
861
862 CURSOR l_results_csr IS
863 SELECT agent_ID
864 FROM CCT_ROUTING_RESULTS
865 -- WHERE call_ID = p_call_ID
866 WHERE call_ID = p_call_ID
867 ORDER BY sort_num;
868
869 err_name VARCHAR2(30);
870 err_msg VARCHAR2(2000);
871 err_stack VARCHAR2(32000);
872
873 BEGIN
874 --dbms_output.put_line('p_call_id '|| p_call_id);
875 -- Initialize return status to SUCCESS
876 l_return_status := FND_API.G_RET_STS_SUCCESS;
877 -- Get the new workflow process ID
878 OPEN l_WorkflowProcID_csr;
879 FETCH l_WorkflowProcID_csr INTO l_wf_process_id;
880 CLOSE l_WorkflowProcID_csr;
881
882 -- Construct the unique item key
883 --This step is redundant for Synchronous Workflow
884 -- 2:04 PM 2/13/99 Savvas Xenophontos
885 l_itemkey := Encode_Call_Itemkey(p_call_ID, l_wf_process_ID);
886 -- Do not SYNCH if test mode is ON, all other case SYNCH
887 IF G_TEST_MODE <> 'ON' THEN
888 l_itemkey := wf_engine.eng_synch;
889 END IF;
890
891 begin
892 -- Create and launch the Workflow process
893 WF_ENGINE.CreateProcess(
894 itemtype => l_itemtype,
895 itemkey => l_itemkey,
896 process => G_PROCESS_NAME );
897
898
899 exception when others then
900 --dbms_output.put_line('Error::'|| SQLERRM);
901 null;
902 end;
903
904 --dbms_output.put_line('In Launch 4:: After Create Process');
905
906 -- Set Item Attributes
907 --DBMS_OUTPUT.PUT_LINE('Call ID is : ' || p_call_id );
908 --DBMS_OUTPUT.PUT_LINE('itemType is : ' || l_itemtype || ' itemKey is : ' || l_itemkey );
909 FOR nIndex IN 1..PARAM_NAME.COUNT LOOP
910 Begin
911 IF param_type(nIndex) = 'VARCHAR' THEN
912 WF_ENGINE.SetItemAttrText (l_itemtype, l_itemkey, param_name(nIndex), param_val(nIndex));
913 ELSIF param_type(nIndex)='NUMBER' THEN
914 WF_ENGINE.SetItemAttrNumber (l_itemtype, l_itemkey, param_name(nIndex), param_val(nIndex));
915 ELSE
916 WF_ENGINE.SetItemAttrDate (l_itemtype, l_itemkey, param_name(nIndex), to_date(param_val(nIndex),'yyyy-mm-dd hh24:mi:ss'));
917
918 END IF;
919 Exception
920 when others then
921 --dbms_output.put_line('error in setting attribute '||param_name(nIndex)||':'||param_val(nIndex)||sqlerrm);
922 null;
923 end;
924 END LOOP;
925
926 -- Set the engine threshold to a very high number to prevent
927 -- this process from ever being deferred
928 WF_ENGINE.THRESHOLD := 999999;
929
930 --
931 -- Start the process
932 -- This procedure call will return only after the process
933 -- completes since only function activities are used.
934 WF_ENGINE.StartProcess(l_itemtype, l_itemkey );
935
936 --dbms_output.put_line('Getting Attribute value for ScreenpopApp:'||WF_ENGINE.GetItemAttrText(l_itemtype,l_itemkey,'SCREENPOPAPP'));
937 /* ***************************************************************
938 Retreive results from CCT_ROUTING_RESULTS tables
939 *************************************************************** */
940 begin
941
942 open l_results_csr;
943
944 FOR counter in 1..G_MAXAGENTS LOOP
945
946 fetch l_results_csr into l_agent;
947 if l_results_csr%NOTFOUND then raise l_no_result_Exception; end if;
948 if counter = 1 then
949 p_agent_list := l_agent;
950 else
951 if (length(p_agent_list)>3800) then
952 --this is required because of 4000 length limitation
953 raise l_no_result_Exception;
954 end if;
955 p_agent_list := p_agent_list || l_delimiter || l_agent;
956 end if;
957
958 END LOOP;
959
960 raise l_no_result_exception;
961 exception
962 WHEN l_no_result_exception THEN
963 CLOSE l_results_csr;
964 -- p_return_val := p_return_val || l_delimiter || p_agent_list
965
966 -- delete the results for this call from the CCT_ROUTING_RESULTS
967 begin
968 DELETE from CCT_ROUTING_RESULTS
969 WHERE call_ID = p_call_ID;
970
971
972 --commit work;
973 exception
974 WHEN OTHERS THEN
975 null;
976 end;
977 --dbms_output.put_line('IN .....exe');
978 return p_agent_list;
979 end;
980
981 EXCEPTION
982 WHEN OTHERS THEN
983 WF_CORE.Get_error(err_name, err_msg, err_stack);
984 if (err_name IS NULL) then
985 l_return_status := 'ORA ERROR : err_name is ' ||
986 to_char(sqlcode) || ' and err_msg is '
987 || sqlerrm;
988
989 else
990 l_return_status := 'WF ERROR : err_name is ' ||
991 err_name || ' and err_msg is ' || err_msg;
992 end if;
993 -- delete the results for this call from the CCT_ROUTING_RESULTS
994
995 begin
996 DELETE from CCT_ROUTING_RESULTS
997 WHERE call_ID = p_call_ID;
998 --commit work;
999 exception
1000 WHEN OTHERS THEN
1001 null;
1002 end;
1003
1004 END Launch_Workflow_Version4 ;
1005
1006 FUNCTION Launch_Workflow_Version5(InString varchar2) return varchar2 is
1007 BEGIN
1008 Varchar2Table(InString);
1009 fillParamArray;
1010 RETURN Launch_Workflow_Version4;
1011 end;
1012
1013 PROCEDURE fillParamArray is
1014 ind number := 1;
1018 return;
1015 p_ind number := 1;
1016 begin
1017 if paramHash.count <= 0 then
1019 end if;
1020 init_param_table;
1021 loop
1022 param_name(ind) := UPPER(paramHash(p_ind));
1023 --dbms_output.put_line('Processing Param Name:'||param_name(ind));
1024
1025 -- If SELECTOR is sent as part of string set the global G_PROCESS_NAME
1026 -- this process name will be used to start the wf process
1027 IF param_name(ind) = 'SELECTOR' THEN
1028 G_PROCESS_NAME := paramHash(p_ind+1);
1029 END IF;
1030 -- If TEST MODE is passed set g_test_mode to turn of wf synch
1031 IF param_name(ind) = 'TESTMODE' THEN
1032 G_TEST_MODE := paramHash(p_ind+1);
1033 END IF;
1034 param_val(ind) := paramHash(p_ind+1);
1035 --dbms_output.put_line(' Param Value:'||param_val(ind));
1036 IF (paramHash(p_ind+2) = '1') THEN
1037 param_type(ind) := 'VARCHAR';
1038 ELSIF (paramHash(p_ind+2)='2') THEN
1039 param_type(ind) := 'NUMBER';
1040 ELSE
1041 param_type(ind) :='DATE';
1042 END IF;
1043
1044 ind := ind + 1;
1045 p_ind := p_ind + 3;
1046 IF (p_ind >= paramHash.COUNT) THEN
1047 RETURN;
1048 END IF;
1049 end loop;
1050 end;
1051
1052 -- This PROC is for NO AGENTS reroute
1053 procedure reroute (
1054 itemtype in varchar2
1055 , itemkey in varchar2
1056 , actid in number
1057 , funmode in varchar2
1058 , resultout in out nocopy varchar2
1059 ) IS
1060 l_call_ID VARCHAR2(40);
1061 l_wf_process_ID NUMBER;
1062 l_dynamic_select VARCHAR2(4000);
1063 l_filter_flag VARCHAR2(1);
1064 l_filter_type VARCHAR2(40);
1065 l_select_csr INTEGER;
1066 l_sort_num NUMBER := 0;
1067 l_dummy INTEGER;
1068 l_agent_ID VARCHAR(32);
1069 l_agents_tbl agent_tbl_type;
1070 l_available VARCHAR2(5) := '''T''';
1071 l_MCM_ID NUMBER ;
1072 l_default_select VARCHAR2(200);
1073 l_apos VARCHAR2(4) := '''';
1074 l_reroute VARCHAR2(20);
1075
1076 CURSOR l_filters_csr IS
1077 select FILTER_TYPE from CCT_TEMPAGENTS
1078 where call_id = l_call_id
1079 and agent_id = '-1' ;
1080
1081
1082 BEGIN
1083 -- set default result
1084 -- If reouted do not reroute again
1085 l_reroute := WF_ENGINE.GetItemAttrText(
1086 itemtype
1087 , itemkey
1088 ,'REROUTED');
1089
1090 IF (l_reroute = 'Y') THEN
1091 resultout := 'COMPLETE:N';
1092 END IF;
1093 --resultout := wf_engine.eng_completed ;
1094
1095 -- get the callid and wf_process_id from the item key
1096 l_call_ID := WF_ENGINE.GetItemAttrText(itemtype, itemkey,
1097 'OCCTMEDIAITEMID');
1098 l_MCM_ID := WF_ENGINE.GetItemAttrNumber(itemtype, itemkey,
1099 'MCM_ID');
1100 l_default_select :=
1101 'Select distinct(A.agent_id) from cct_tempagents a '||
1102 'where a.call_id ='||l_apos||l_call_ID||l_apos||
1103 ' and a.agent_id <> '||l_apos||-1||l_apos;
1104
1105 -- Start the dynamic string
1106 l_dynamic_select := l_default_select;
1107
1108
1109 FOR l_filter IN l_filters_csr LOOP
1110 l_dynamic_select := l_dynamic_select ||
1111 ' AND A.agent_ID IN (SELECT agent_ID from CCT_TEMPAGENTS ' ||
1112 ' WHERE CALL_ID = ' || l_apos || l_call_ID || l_apos ||
1113 ' AND FILTER_TYPE = ' || l_apos || l_filter.FILTER_TYPE || l_apos || ')';
1114 END LOOP;
1115
1116
1117 -- now run the select clause using dynamic sql
1118 begin
1119 l_select_csr := DBMS_SQL.OPEN_CURSOR;
1120
1121 DBMS_SQL.PARSE(l_select_csr, l_dynamic_select, DBMS_SQL.native);
1122 DBMS_SQL.DEFINE_COLUMN(l_select_csr, 1, l_agent_ID, 32);
1123 l_dummy := DBMS_SQL.EXECUTE(l_select_csr);
1124
1125 l_sort_num := 0;
1126 LOOP
1127 if DBMS_SQL.FETCH_ROWS(l_select_csr) = 0 then
1128 EXIT;
1129 end if;
1130
1131 DBMS_SQL.COLUMN_VALUE(l_select_csr, 1, l_agent_ID);
1132
1133 -- insert the cursor record into the l_agents_tbl Table
1134 l_sort_num := l_sort_num + 1;
1135 l_agents_tbl(l_sort_num) := l_agent_ID;
1136
1137 END LOOP;
1138
1139 IF (l_sort_num = 0) THEN
1140 --
1141 -- no agents were found
1142 -- use any call center agent
1143 l_dynamic_select := l_default_select;
1144
1145 DBMS_SQL.PARSE(l_select_csr, l_dynamic_select, DBMS_SQL.NATIVE);
1146 DBMS_SQL.DEFINE_COLUMN(l_select_csr, 1, l_agent_ID, 32);
1147 l_dummy := DBMS_SQL.EXECUTE(l_select_csr);
1148
1149 l_sort_num := 0;
1150
1151 LOOP
1152 if DBMS_SQL.FETCH_ROWS(l_select_csr) = 0 then
1153 IF l_reroute is NULL THEN
1154 -- If the call is already rerouted once don't reroute again
1155 -- even if no agents found
1156 -- Set Rerouted to Y as this call is about to be rerouted
1157 WF_ENGINE.SetItemAttrText (
1158 itemtype
1159 , itemkey
1160 , 'REROUTED'
1161 , 'Y');
1162 -- set reroute to Y
1163 resultout := 'COMPLETE:Y';
1164 --dbms_output.put_line ('COMPLETE:Y');
1165 ELSE
1166 --dbms_output.put_line ('COMPLETE:N');
1170 end if;
1167 resultout := 'COMPLETE:N';
1168 END IF;
1169 EXIT;
1171
1172 DBMS_SQL.COLUMN_VALUE(l_select_csr, 1, l_agent_ID);
1173
1174 -- insert the cursor record into the l_agents_tbl Table
1175 l_sort_num := l_sort_num + 1;
1176 l_agents_tbl(l_sort_num) := l_agent_ID;
1177
1178 END LOOP;
1179 ELSE
1180 -- some agents were found hence set wf to no for reroute
1181 -- as agents were found.
1182 resultout := 'COMPLETE:N';
1183 END IF;
1184
1185 -- Close the cursor
1186 DBMS_SQL.CLOSE_CURSOR(l_select_csr);
1187
1188 -- delete the entries for this call from CCT_TEMPAGENTS
1189 DELETE from CCT_TEMPAGENTS
1190 WHERE CALL_ID = l_call_ID;
1191
1192
1193 -- do the randomization to reduce number of agents returned to TEN
1194 if (l_sort_num <= G_MAXAGENTS) then
1195 -- randomization needed insert all into CCT_ROUTING_RESULTS
1196 Randomize_Agents(l_agents_tbl, l_sort_num);
1197 FOR counter IN 1..l_sort_num
1198 LOOP
1199 INSERT INTO CCT_ROUTING_RESULTS
1200 (call_id,itemkey,agent_id,sort_num,
1201 routing_Result_id,last_update_date,last_updated_by,
1202 creation_Date,created_by)
1203 VALUES (l_call_ID, itemkey,l_agents_tbl(counter), counter,
1204 1001,sysdate,1,sysdate,1);
1205 END LOOP;
1206 else
1207 -- do the randomization to reduce number of agents returned to TEN
1208 Randomize_Agents(l_agents_tbl, l_sort_num);
1209 FOR counter IN 1..G_MAXAGENTS
1210 LOOP
1211 INSERT INTO CCT_ROUTING_RESULTS
1212 (call_id,itemkey,agent_id,sort_num,
1213 routing_Result_id,last_update_date,last_updated_by,
1214 creation_Date,created_by)
1215 VALUES (l_call_ID, itemkey,l_agents_tbl(counter), counter,
1216 1001,sysdate,1,sysdate,1);
1217 END LOOP;
1218 null;
1219 end if;
1220
1221 --commit work;
1222 exception
1223 WHEN OTHERS THEN
1224 -- close the cursor
1225 DBMS_SQL.CLOSE_CURSOR (l_select_csr);
1226 RAISE;
1227 end;
1228 END reroute;
1229
1230 PROCEDURE number_of_reroutes (
1231 itemtype in varchar2
1232 , itemkey in varchar2
1233 , actid in number
1234 , funmode in varchar2
1235 , resultout in out nocopy varchar2
1236 ) IS
1237 l_number_of_reroutes VARCHAR2(20) := '0';
1238 BEGIN
1239 l_number_of_reroutes := WF_ENGINE.GetItemAttrText(
1240 itemtype
1241 , itemkey
1242 ,'OCCTREROUTE');
1243 -- Return the number of reroutes between 0 and 5,
1244 -- if number if reroutes is more than 5 return 5 only.
1245 IF l_number_of_reroutes = '0' THEN
1246 resultout := wf_engine.eng_completed||':0';
1247 ELSIF l_number_of_reroutes = '1' THEN
1248 resultout := wf_engine.eng_completed||':1';
1249 ELSIF l_number_of_reroutes = '2' THEN
1250 resultout := wf_engine.eng_completed||':2';
1251 ELSIF l_number_of_reroutes = '3' THEN
1252 resultout := wf_engine.eng_completed||':3';
1253 ELSIF l_number_of_reroutes = '4' THEN
1254 resultout := wf_engine.eng_completed||':4';
1255 ELSIF l_number_of_reroutes = '5' THEN
1256 resultout := wf_engine.eng_completed||':5';
1257 ELSE
1258 resultout := wf_engine.eng_completed||':5';
1259 END IF;
1260
1261 END number_of_reroutes;
1262
1263 END CCT_RoutingWorkflow_PUB;