4 ------------------------------------------------------------------------------
1 PACKAGE BODY CCT_JTFRESOURCEROUTING_PUB as
2 /* $Header: cctjtfrb.pls 120.0 2005/06/02 10:02:41 appldev noship $ */
3
5 -- Function : Get_Agents_for_Competency
6 -- Usage : Used by the Routing module to get the agents assigned to
7 -- a comp name and type.
8 -- Description : This function retrieves a collection of agent IDs from
9 -- the competency tables given a comp name and type.
10 -- Parameters :
11 -- p_competency_type IN VARCHAR2 Required
12 -- p_competency_name IN VARCHAR2 Required
13 -- x_agent_tbl out nocopy CCT_ROUTINGWORKFLOW_UTL.agent_tbl_type
14 --
15 -- Return : NUMBER
16 -- This function returns the number of agents assigned to
17 -- the given competency_name (0 if there is no agent assigned
18 -- to the competency_name).
19 ------------------------------------------------------------------------------
20 FUNCTION Get_Agents_For_Competency (
21 p_competency_type IN VARCHAR2
22 , p_competency_name IN VARCHAR2
23 , x_agent_tbl out nocopy CCT_ROUTINGWORKFLOW_UTL.agent_tbl_type
24 )
25 RETURN NUMBER IS
26
27 v_total_num_of_emps NUMBER:=0;
28 v_agent_id JTF_RS_RESOURCE_EXTNS.SOURCE_ID%type;
29
30 --
31 -- get a list of employees that is responsible for a given comp name andy type
32 -- Changed 06FRB2002
33 CURSOR c_employees IS
34 SELECT distinct res.resource_id
35 FROM jtf_rs_resource_extns res,
36 jtf_rs_role_relations res_roles,
37 jtf_rs_roles_b roles
38 where res.resource_id in
39 ((SELECT DISTINCT comp_ele.person_id
40 FROM per_competence_elements comp_ele,
41 per_competences comp
42 WHERE (upper(comp_ele.competence_type) = upper(p_competency_type) and
43 upper(comp.name) = upper(p_competency_name) and
44 comp.competence_id = comp_ele.competence_id)))
45 and res.resource_id=res_roles.role_resource_id
46 and res_roles.role_id=roles.role_id
50 BEGIN
47 and (roles.role_type_code='CALLCENTER'
48 or roles.role_type_code='ICENTER');
49
51
52 OPEN c_employees;
53 LOOP
54
55 FETCH c_employees INTO v_agent_id;
56 --dbms_output.put_line('Employee Id from query is'||to_char(v_agent_id));
57 IF c_employees%NOTFOUND THEN
58 CLOSE c_employees;
59 return v_total_num_of_emps;
60 ELSE
61 x_agent_tbl(v_total_num_of_emps) := v_agent_id;
62 END IF;
63 v_total_num_of_emps := v_total_num_of_emps + 1;
64
65 END LOOP;
66 END Get_Agents_For_Competency;
67
68
69
70
71 FUNCTION Get_agents_from_stat_grp_nam (
72 p_group_name IN VARCHAR2
73 ,p_agent_tbl out nocopy CCT_ROUTINGWORKFLOW_UTL.agent_tbl_type
74 )
75 RETURN number IS
76
77 l_total_num_of_agents NUMBER:=0;
78 l_group_id jtf_rs_groups_vl.group_id%TYPE;
79 l_agent_id cct_agent_rt_stats.agent_id%TYPE;
80
81
82
83 CURSOR csr_agents IS
84 SELECT resource_id
85 FROM jtf_rs_group_members
86 WHERE delete_flag='N'
87 AND group_id = ( SELECT distinct a.group_id
88 FROM jtf_rs_groups_vl a,
89 jtf_rs_group_usages_vl b
90 WHERE b.usage='CALL'
91 AND b.group_ID=a.group_ID
92 AND a.start_date_active<=sysdate
93 AND nvl(a.end_date_active,sysdate)>=sysdate
94 AND upper(a.group_name) = upper(p_group_name));
95
96
97
98
99 BEGIN
100 --dbms_output.put_line ('IN GET LOGGED IN AGENTS');
101 OPEN csr_agents;
102 LOOP
103 FETCH csr_agents into l_agent_id;
104 IF csr_agents%NOTFOUND THEN
105 CLOSE csr_agents;
106 RETURN l_total_num_of_agents;
107 ELSE
108 l_total_num_of_agents := l_total_num_of_agents +1;
109 p_agent_tbl(l_total_num_of_agents) := l_agent_id;
110 END IF;
111 END LOOP;
112
113
114 EXCEPTION
115 WHEN OTHERS THEN
116 CLOSE csr_agents;
117 --dbms_output.put_line(' ERROR in Get_logged In Agents '||sqlerrm );
118
119 END Get_agents_from_stat_grp_nam;
120
121 FUNCTION Get_agents_from_stat_grp_num (
122 p_group_number IN VARCHAR2
123 ,p_agent_tbl out nocopy CCT_ROUTINGWORKFLOW_UTL.agent_tbl_type
124 )
125 RETURN number IS
126
127 l_total_num_of_agents NUMBER:=0;
128 l_agent_id cct_agent_rt_stats.agent_id%TYPE;
129 l_group_id jtf_rs_groups_vl.group_id%TYPE;
130
131
132 CURSOR csr_agents IS
133 SELECT resource_id
134 FROM jtf_rs_group_members
135 WHERE delete_flag='N'
136 AND group_id= ( SELECT a.group_id
137 FROM jtf_rs_groups_vl a,
138 jtf_rs_group_usages_vl b
139 WHERE b.usage='CALL'
140 AND b.group_ID=a.group_ID
141 AND a.start_date_active<=sysdate
142 AND nvl(a.end_date_active,sysdate)>=sysdate
143 AND a.group_number = p_group_number);
144
145 BEGIN
146
147 --dbms_output.put_line ('IN GET LOGGED IN AGENTS');
148
149 OPEN csr_agents;
150 LOOP
151 FETCH csr_agents into l_agent_id;
152 IF csr_agents%NOTFOUND THEN
153 CLOSE csr_agents;
154 RETURN l_total_num_of_agents;
155 ELSE
156 l_total_num_of_agents := l_total_num_of_agents +1;
157 p_agent_tbl(l_total_num_of_agents) := l_agent_id;
158 END IF;
159
160 END LOOP;
161
162 EXCEPTION
163 WHEN OTHERS THEN
164 CLOSE csr_agents;
165 --dbms_output.put_line(' ERROR in Get_logged In Agents '||sqlerrm );
166
167 END Get_agents_from_stat_grp_num;
168
169 FUNCTION Get_agents_from_dyn_grp_nam (
170 p_group_name IN VARCHAR2
171 ,p_agent_tbl out nocopy CCT_ROUTINGWORKFLOW_UTL.agent_tbl_type
172 )
173 RETURN number IS
174 l_total_num_of_agents NUMBER:=0;
175 l_text jtf_rs_dynamic_groups_b.sql_text%TYPE;
176 l_agent_id cct_agent_rt_stats.agent_id%TYPE;
177 l_select_csr INTEGER;
178 l_sort_num NUMBER := 0;
179 l_dummy INTEGER;
180
181 CURSOR csr_text IS
182 SELECT a.sql_text
183 FROM JTF_RS_DYNAMIC_GROUPS_vl a
184 WHERE upper(a.usage)='CALL'
185 AND a.start_date_active<=sysdate
186 AND nvl(a.end_date_active,sysdate)>=sysdate
187 AND a.group_name = p_group_name
188 AND sql_text is not null;
189
190
191 BEGIN
192 OPEN csr_text;
193 FETCH csr_text into l_text;
194 IF csr_text%NOTFOUND THEN
195 CLOSE csr_text;
196 END IF;
197
198 BEGIN
199 l_select_csr := DBMS_SQL.OPEN_CURSOR;
200 DBMS_SQL.PARSE(l_select_csr, l_text, DBMS_SQL.native);
201 DBMS_SQL.DEFINE_COLUMN(l_select_csr, 1, l_agent_ID);
202 l_dummy := DBMS_SQL.EXECUTE(l_select_csr);
203
204 l_sort_num := 0;
205 LOOP
206 IF DBMS_SQL.FETCH_ROWS(l_select_csr) = 0 THEN
210 DBMS_SQL.COLUMN_VALUE(l_select_csr, 1, l_agent_ID);
207 EXIT;
208 END IF;
209
211
212 -- insert the cursor record into the l_agents_tbl Table
213 l_sort_num := l_sort_num + 1;
214 p_agent_tbl(l_sort_num) := l_agent_id;
215 END LOOP;
216 -- Close the cursor
217 DBMS_SQL.CLOSE_CURSOR(l_select_csr);
218 END;
219 RETURN l_sort_num;
220 EXCEPTION
221 WHEN OTHERS THEN
222 CLOSE csr_text;
223 RETURN 0;
224 END Get_agents_from_dyn_grp_nam;
225
226 FUNCTION Get_agents_from_dyn_grp_num (
227 p_group_number IN VARCHAR2
228 ,p_agent_tbl out nocopy CCT_ROUTINGWORKFLOW_UTL.agent_tbl_type
229 )
230 RETURN number IS
231 l_total_num_of_agents NUMBER:=0;
232 l_text jtf_rs_dynamic_groups_b.sql_text%TYPE;
233 l_dummy INTEGER;
234 l_agent_id cct_agent_rt_stats.agent_id%TYPE;
235 l_sort_num NUMBER := 0;
236
237 l_select_csr INTEGER;
238
239 CURSOR csr_text IS
240 SELECT a.sql_text
241 FROM JTF_RS_DYNAMIC_GROUPS_B a
242 WHERE upper(a.usage)='CALL'
243 AND a.start_date_active<=sysdate
244 AND nvl(a.end_date_active,sysdate)>=sysdate
245 AND a.group_number = p_group_number
246 AND sql_text is not null;
247
248
249 BEGIN
250 OPEN csr_text;
251 FETCH csr_text into l_text;
252 IF csr_text%NOTFOUND THEN
253 CLOSE csr_text;
254 END IF;
255
256 BEGIN
257 l_select_csr := DBMS_SQL.OPEN_CURSOR;
258 DBMS_SQL.PARSE(l_select_csr, l_text, DBMS_SQL.native);
259 DBMS_SQL.DEFINE_COLUMN(l_select_csr, 1, l_agent_ID);
260 l_dummy := DBMS_SQL.EXECUTE(l_select_csr);
261
262 l_sort_num := 0;
263 LOOP
264 IF DBMS_SQL.FETCH_ROWS(l_select_csr) = 0 THEN
265 EXIT;
266 END IF;
267
268 DBMS_SQL.COLUMN_VALUE(l_select_csr, 1, l_agent_ID);
269
270 -- insert the cursor record into the l_agents_tbl Table
271 l_sort_num := l_sort_num + 1;
272 p_agent_tbl(l_sort_num) := l_agent_id;
273 END LOOP;
274 -- Close the cursor
275 DBMS_SQL.CLOSE_CURSOR(l_select_csr);
276 END;
277
278 RETURN l_sort_num;
279 EXCEPTION
280 WHEN OTHERS THEN
281 CLOSE csr_text;
282 RETURN 0;
283 END Get_agents_from_dyn_grp_num;
284
285 FUNCTION Get_agents_not_in_stat_grp_nam (
286 p_group_name IN VARCHAR2
287 ,p_agent_tbl out nocopy CCT_ROUTINGWORKFLOW_UTL.agent_tbl_type
288 )
289 RETURN number IS
290 l_total_num_of_agents NUMBER:=0;
291 l_agent_id cct_agent_rt_stats.agent_id%TYPE;
292
293
294 CURSOR csr_agents IS
295 select res.resource_id
296 from jtf_rs_resource_extns res
297 ,jtf_rs_role_relations res_roles
298 ,jtf_rs_roles_b roles
299 where res.resource_id = res_roles.role_resource_id
300 and res_roles.role_resource_type = 'RS_INDIVIDUAL'
301 and res_roles.start_date_active<=sysdate
302 and nvl(res_roles.end_date_active,sysdate)>=sysdate
303 and res_roles.delete_flag = 'N'
304 and res_roles.role_id=roles.role_id
305 and (roles.role_type_code = 'CALLCENTER'
306 or roles.role_type_code='ICENTER')
307 MINUS
308 select resource_id
309 from jtf_rs_group_members
310 where delete_flag='N'
311 and group_id = ( select a.group_id
312 from jtf_rs_groups_vl a,
313 jtf_rs_group_usages b
314 where upper(a.group_name) = upper(p_group_name)
315 and b.usage='CALL'
316 and b.group_ID=a.group_ID
317 and a.start_date_active<=sysdate
318 and nvl(a.end_date_active,sysdate)>=sysdate );
319 -- Changed 09/12/02 rajayara
320 --SELECT distinct res.resource_id
321 -- FROM jtf_rs_resource_extns res
322 -- ,jtf_rs_role_relations res_roles
323 -- ,jtf_rs_roles_b roles
324 -- WHERE res.resource_id = res_roles.role_resource_id
325 -- and res_roles.role_id=roles.role_id
326 -- and (roles.role_type_code = 'CALLCENTER'
327 -- or roles.role_type_code='ICENTER')
328 -- MINUS
329 -- SELECT resource_id
330 -- FROM jtf_rs_group_members
331 -- WHERE delete_flag='N'
332 -- AND group_id= ( SELECT distinct a.group_id
333 -- FROM jtf_rs_groups_vl a,
334 -- jtf_rs_group_usages_vl b
335 -- WHERE b.usage='CALL'
336 -- AND b.group_ID=a.group_ID
337 -- AND a.start_date_active<=sysdate
338 -- AND nvl(a.end_date_active,sysdate)>=sysdate
339 -- AND upper(a.group_name) = upper(p_group_name));
340
341
342 BEGIN
343 OPEN csr_agents;
344 LOOP
345 FETCH csr_agents into l_agent_id;
346 IF csr_agents%NOTFOUND THEN
347 CLOSE csr_agents;
348 RETURN l_total_num_of_agents;
349 ELSE
350 p_agent_tbl(l_total_num_of_agents) := l_agent_id;
351 END IF;
355
352
353 l_total_num_of_agents := l_total_num_of_agents + 1;
354 END LOOP;
356 EXCEPTION
357 WHEN OTHERS THEN
358 CLOSE csr_agents;
359 RETURN 0;
360 END Get_agents_not_in_stat_grp_nam;
361
362 FUNCTION Get_agents_not_in_stat_grp_num (
363 p_group_number IN VARCHAR2
364 ,p_agent_tbl out nocopy CCT_ROUTINGWORKFLOW_UTL.agent_tbl_type
365 )
366 RETURN number IS
367 l_total_num_of_agents NUMBER:=0;
368 l_agent_id cct_agent_rt_stats.agent_id%TYPE;
369
370
371 CURSOR csr_agents IS
372 select res.resource_id
373 from jtf_rs_resource_extns res
374 ,jtf_rs_role_relations res_roles
375 ,jtf_rs_roles_b roles
376 where res.resource_id = res_roles.role_resource_id
377 and res_roles.role_resource_type = 'RS_INDIVIDUAL'
378 and res_roles.start_date_active<=sysdate
379 and nvl(res_roles.end_date_active,sysdate)>=sysdate
380 and res_roles.delete_flag = 'N'
381 and res_roles.role_id=roles.role_id
382 and (roles.role_type_code = 'CALLCENTER'
383 or roles.role_type_code='ICENTER')
384 MINUS
385 select resource_id
386 from jtf_rs_group_members
387 where delete_flag='N'
388 and group_id = ( select a.group_id
389 from jtf_rs_groups_b a,
390 jtf_rs_group_usages b
391 where a.group_number = p_group_number
392 and b.usage='CALL'
393 and b.group_ID=a.group_ID
394 and a.start_date_active<=sysdate
395 and nvl(a.end_date_active,sysdate)>=sysdate );
396
397 -- Changed 09/12/02 rajayara
398 -- SELECT distinct res.resource_id
399 -- FROM jtf_rs_resource_extns res
400 -- ,jtf_rs_role_relations res_roles
401 -- ,jtf_rs_roles_b roles
402 -- WHERE res.resource_id = res_roles.role_resource_id
403 -- and res_roles.role_id=roles.role_id
404 -- and (roles.role_type_code = 'CALLCENTER'
405 -- or roles.role_type_code='ICENTER')
406 -- MINUS
407 -- SELECT resource_id
408 -- FROM jtf_rs_group_members
409 -- WHERE delete_flag='N'
410 -- AND group_id = ( SELECT a.group_id
411 -- FROM jtf_rs_groups_vl a,
412 -- jtf_rs_group_usages_vl b
413 -- WHERE b.usage='CALL'
414 -- AND b.group_ID=a.group_ID
415 -- AND a.start_date_active<=sysdate
416 -- AND nvl(a.end_date_active,sysdate)>=sysdate
417 -- AND a.group_number = p_group_number);
418
419
420 BEGIN
421 OPEN csr_agents;
422 LOOP
423 FETCH csr_agents into l_agent_id;
424 IF csr_agents%NOTFOUND THEN
425 CLOSE csr_agents;
426 RETURN l_total_num_of_agents;
427 ELSE
428 p_agent_tbl(l_total_num_of_agents) := l_agent_id;
429 END IF;
430
431 l_total_num_of_agents := l_total_num_of_agents + 1;
432 END LOOP;
433
434 EXCEPTION
435 WHEN OTHERS THEN
436 CLOSE csr_agents;
437 RETURN 0;
438 END Get_agents_not_in_stat_grp_num;
439
440
441 FUNCTION Get_agents_not_in_dyn_grp_nam (
442 p_group_name IN VARCHAR2
443 ,p_agent_tbl out nocopy CCT_ROUTINGWORKFLOW_UTL.agent_tbl_type
444 )
445 RETURN number IS
446 l_agent_id cct_agent_rt_stats.agent_id%TYPE;
447 l_default_select VARCHAR2(4000);
448 l_apos VARCHAR2(4) := '''';
449 l_query VARCHAR2(5000);
450 l_text jtf_rs_dynamic_groups_b.sql_text%TYPE;
451 l_select_csr INTEGER;
452 l_dummy INTEGER;
453 l_sort_num NUMBER := 0;
454
455 CURSOR csr_text IS
456 SELECT a.sql_text
457 FROM JTF_RS_DYNAMIC_GROUPS_vl a
458 WHERE upper(a.usage)='CALL'
459 AND a.start_date_active<=sysdate
460 AND nvl(a.end_date_active,sysdate)>=sysdate
461 AND upper(a.group_name) = upper(p_group_name)
462 AND sql_text is not null;
463
464
465 BEGIN
466 OPEN csr_text;
467 FETCH csr_text into l_text;
468 IF csr_text%NOTFOUND THEN
469 CLOSE csr_text;
470 END IF;
471
472 l_default_select :=
473 'SELECT distinct res.resource_id '||
474 'FROM jtf_rs_resource_extns res '||
475 ' ,jtf_rs_role_relations res_roles '||
476 ' ,jtf_rs_roles_b roles '||
477 'WHERE res.resource_id = res_roles.role_resource_id '||
478 'and res_roles.role_id=roles.role_id '||
479 'and (roles.role_type_code = '||l_apos|| 'CALLCENTER' ||l_apos||
480 'or roles.role_type_code='||l_apos||'ICENTER'||l_apos||
481 ' MINUS ' ;
482 l_query := l_default_select || l_text ;
483 --dbms_output.put_line ('l_dyn_select'|| l_query);
484
485 BEGIN
486 l_select_csr := DBMS_SQL.OPEN_CURSOR;
487 DBMS_SQL.PARSE(l_select_csr, l_query, DBMS_SQL.native);
488 DBMS_SQL.DEFINE_COLUMN(l_select_csr, 1, l_agent_ID);
489 l_dummy := DBMS_SQL.EXECUTE(l_select_csr);
490
491 l_sort_num := 0;
492 LOOP
493 IF DBMS_SQL.FETCH_ROWS(l_select_csr) = 0 THEN
494 EXIT;
495 END IF;
496
497 DBMS_SQL.COLUMN_VALUE(l_select_csr, 1, l_agent_ID);
498
499 -- insert the cursor record into the l_agents_tbl Table
500 l_sort_num := l_sort_num + 1;
501 p_agent_tbl(l_sort_num) := l_agent_id;
502 END LOOP;
503 -- Close the cursor
504 DBMS_SQL.CLOSE_CURSOR(l_select_csr);
505 END;
506
507 RETURN l_sort_num;
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 CLOSE csr_text;
512 RETURN 0;
513 END Get_agents_not_in_dyn_grp_nam;
514
515 FUNCTION Get_agents_not_in_dyn_grp_num (
516 p_group_number IN VARCHAR2
517 ,p_agent_tbl out nocopy CCT_ROUTINGWORKFLOW_UTL.agent_tbl_type
518 )
519 RETURN number IS
520
521 l_agent_id cct_agent_rt_stats.agent_id%TYPE;
522 l_default_select VARCHAR2(4000);
523 l_apos VARCHAR2(4) := '''';
524 l_query VARCHAR2(5000);
525 l_text jtf_rs_dynamic_groups_b.sql_text%TYPE;
526 l_select_csr INTEGER;
527 l_dummy INTEGER;
528 l_sort_num NUMBER := 0;
529
530 CURSOR csr_text IS
531 SELECT a.sql_text
532 FROM JTF_RS_DYNAMIC_GROUPS_vl a
533 WHERE upper(a.usage)='CALL'
534 AND a.start_date_active<=sysdate
535 AND nvl(a.end_date_active,sysdate)>=sysdate
536 AND a.group_number = p_group_number
537 AND sql_text is not null;
538
539
540 BEGIN
541 OPEN csr_text;
542 FETCH csr_text into l_text;
543 IF csr_text%NOTFOUND THEN
544 CLOSE csr_text;
545 END IF;
546
547 l_default_select :=
548 'SELECT distinct res.resource_id '||
549 'FROM jtf_rs_resource_extns res '||
550 ' ,jtf_rs_role_relations res_roles '||
551 ' ,jtf_rs_roles_b roles '||
552 'WHERE res.resource_id = res_roles.role_resource_id '||
553 'and res_roles.role_id=roles.role_id '||
554 'and (roles.role_type_code = '||l_apos|| 'CALLCENTER' ||l_apos||
555 'or roles.role_type_code='||l_apos||'ICENTER'||l_apos||
556 ' MINUS ' ;
557 l_query := l_default_select || l_text ;
558
559
560 BEGIN
561 l_select_csr := DBMS_SQL.OPEN_CURSOR;
562 DBMS_SQL.PARSE(l_select_csr, l_query, DBMS_SQL.native);
563 DBMS_SQL.DEFINE_COLUMN(l_select_csr, 1, l_agent_ID);
564 l_dummy := DBMS_SQL.EXECUTE(l_select_csr);
565
566 l_sort_num := 0;
567 LOOP
568 IF DBMS_SQL.FETCH_ROWS(l_select_csr) = 0 THEN
569 EXIT;
570 END IF;
571
572 DBMS_SQL.COLUMN_VALUE(l_select_csr, 1, l_agent_ID);
573
574 -- insert the cursor record into the l_agents_tbl Table
575 l_sort_num := l_sort_num + 1;
576 p_agent_tbl(l_sort_num) := l_agent_id;
577 END LOOP;
578 -- Close the cursor
579 DBMS_SQL.CLOSE_CURSOR(l_select_csr);
580 END;
581
582 RETURN l_sort_num;
583
584
585 EXCEPTION
586 WHEN OTHERS THEN
587 CLOSE csr_text;
588 RETURN 0;
589 END Get_agents_not_in_dyn_grp_num;
590
591 END CCT_JTFRESOURCEROUTING_PUB;
[Home] [Help]