1 PACKAGE BODY CCT_SERVERGROUPROUTING_PUB as
2 /* $Header: cctsvgrb.pls 120.0 2005/06/02 09:38:27 appldev noship $ */
3
4 ------------------------------------------------------------------------------
5 -- Function : Get_Srv_Group_from_MCMID
6 -- Usage : Used by the Routing module to get the Name of the Server Group
7 -- Center to which the given MCM is associated
8 -- Parameters :
9 -- p_MCMID IN NUMBER Required
10 --
11 -- Return : VARCHAR2
12 -- This function returns the Name of the Server Group to
13 -- which the given MCM is associated
14 ------------------------------------------------------------------------------
15 FUNCTION Get_Srv_Group_from_MCMID (
16 p_MCMID IN NUMBER
17 )
18 RETURN VARCHAR2 IS
19 l_servergroup_name VARCHAR2(32);
20 BEGIN
21 select GRP.GROUP_NAME into l_servergroup_name
22 from IEO_SVR_GROUPS GRP, IEO_SVR_SERVERS svr
23 where grp.server_group_id=svr.member_svr_group_id
24 and svr.server_id=p_MCMID;
25
26 return l_servergroup_name;
27
28 EXCEPTION
29 WHEN OTHERS THEN
30 return null;
31
32 END Get_Srv_Group_from_MCMID;
33
34 FUNCTION Get_Agents_logged_in (
35 p_mcm_id IN NUMBER
36 ,p_agent_tbl OUT nocopy CCT_ROUTINGWORKFLOW_UTL.agent_tbl_type
37 )
38 RETURN number IS
39 l_total_num_of_agents NUMBER:=0;
40 l_agent_id cct_agent_rt_stats.agent_id%TYPE;
41
42
43 CURSOR csr_agents IS
44 SELECT a.agent_id
45 FROM cct_agent_rt_stats a
46 WHERE mcm_id = p_mcm_id;
47
48
49 BEGIN
50 --dbms_output.put_line ('IN GET LOGGED IN AGENTS');
51 OPEN csr_agents;
52 LOOP
53 FETCH csr_agents into l_agent_id;
54 IF csr_agents%NOTFOUND THEN
55 CLOSE csr_agents;
56 RETURN l_total_num_of_agents;
57 ELSE
58 p_agent_tbl(l_total_num_of_agents) := l_agent_id;
59 END IF;
60
61 l_total_num_of_agents := l_total_num_of_agents + 1;
62 END LOOP;
63
64 EXCEPTION
65 WHEN OTHERS THEN
66 CLOSE csr_agents;
67 --dbms_output.put_line(' ERROR in Get_logged In Agents '||sqlerrm );
68
69 END Get_Agents_logged_in;
70
71 Procedure Get_AppForClassification(
72 p_classification IN VARCHAR2
73 ,p_mediaTypeUUID IN VARCHAR2
74 ,p_app_id out nocopy NUMBER
75 ,p_app_name out nocopy VARCHAR2) IS
76 Begin
77 Begin
78 --dbms_output.put_line('1:GetAPPforClass='||p_classification);
79 Select def.application_id,decode(def.application_id,511,'TELESERVICE',
80 521,'TELESALES','OTHER')
81 into p_app_id,p_app_name
82 from ieu_uwq_media_actions act,ieu_uwq_media_types_b type,ieu_uwq_maction_defs_b def
83 where act.maction_def_id=def.maction_def_id
84 and type.media_type_uuid=p_mediaTypeUUID
85 and type.media_type_id=act.media_type_id
86 and upper(act.classification)=upper(p_classification);
87 -- dbms_output.put_line('1:GetAPPforClass app_name='||p_app_name||':'||to_char(p_app_id));
88 Exception
89 When NO_DATA_FOUND THEN
90 Begin
91 Select act.application_id,decode(act.application_id,511,'TELESERVICE',
92 521,'TELESALES','OTHER')
93 into p_app_id,p_app_name
94 from ieu_uwq_media_actions act,ieu_uwq_media_types_b type,ieu_uwq_maction_defs_b def
95 where act.maction_def_id=def.maction_def_id
96 and type.media_type_uuid=p_mediaTypeUUID
97 and type.media_type_id=act.media_type_id
98 and upper(act.classification)=upper('unClassified');
99 -- dbms_output.put_line('2:GetAPPforClass app_name='||p_app_name||':'||to_char(p_app_id));
100 Exception
101 When no_data_found then
102 Select act.application_id,decode(act.application_id,511,'TELESERVICE',
103 521,'TELESALES','OTHER')
104 into p_app_id,p_app_name
105 from ieu_uwq_media_actions act,ieu_uwq_media_types_b type,ieu_uwq_maction_defs_b def
106 where act.maction_def_id=def.maction_def_id
107 and type.media_type_uuid=p_mediaTypeUUID
108 and type.media_type_id=act.media_type_id
109 and act.classification is null;
110 -- dbms_output.put_line('3:GetAPPforClass app_name='||p_app_name||':'||to_char(p_app_id));
111 END;
112 End;
113 Exception
114 When Others then
115 null;
116 -- dbms_output.put_line('Error in GetAPPforClass');
117 End;
118
119
120 END CCT_SERVERGROUPROUTING_PUB;