1 PACKAGE BODY BIX_REAL_TIME_BINS_PKG
2 /* $Header: bixquebb.pls 115.38 2003/01/10 00:15:03 achanda ship $ */
3 AS
4
5 g_session_id NUMBER;
6
7 /******************************************** BINs **************************/
8
9 /* populates data for Queue Status Bin */
10 /* component code: BIX_QUEUE_STATUS */
11 /* component type: BIN */
12 PROCEDURE POPULATE_BIN(p_context IN VARCHAR2)
13 AS
14
15 l_date_low date;
16 l_date_high date;
17 l_timestring varchar2(10);
18 l_classification_id number;
19 l_count number;
20 /* get calls in queue, average queue time by classification */
21 Cursor getBinValue is
22 Select count(C1.MEDIA_ITEM_ID) calls_waiting,
23 nvl(sum(sysdate - C1.last_update_date)*24*3600/decode(count(C1.MEDIA_ITEM_ID),0,1,null,1,count(C1.media_item_id)),0)
24 avg_queue_time,
25 C1.classification classification
26 from CCT_MEDIA_ITEMS C1
27 where C1.status = 1
28 and media_type = 0
29 and C1.creation_date between l_date_low and l_date_high
30 group by classification;
31 BEGIN
32
33 /* initialize date ranges for today */
34 l_date_low := trunc(sysdate);
35 l_date_high := sysdate;
36
37 /* get the session identifier for the web session */
38 g_session_id := bix_util_pkg.get_icx_session_id;
39
40 /* delete data from previous runs */
41 delete from bix_dm_bin
42 where session_id = g_session_id
43 and bin_code = 'BIX_QUEUE_STATUS';
44
45 /* for all calls in queue */
46 for rec in getBinValue
47 Loop
48
49 /* retrieve the classification id given the classification */
50 select count(classification_id)
51 into l_count
52 from cct_classifications
53 where classification = rec.classification;
54
55 if (l_count = 1) then
56 select classification_id
57 into l_classification_id
58 from cct_classifications
59 where classification = rec.classification;
60 else
61 l_classification_id := -999;
62 end if;
63
64 l_timestring := bix_util_pkg.get_hrmiss_frmt(rec.avg_queue_time);
65
66 /* insert the calls in queue in table for the reporting */
67 insert into bix_dm_bin (bin_code, session_id, col1, col2, col4, col6)
68 Values ('BIX_QUEUE_STATUS',g_session_id, l_classification_id, rec.classification,
69 rec.calls_waiting,l_timestring);
70 end loop; /* end for all calls in queue */
71
72 END POPULATE_BIN;
73
74
75
76 /* populate data AGENT STATUS bin */
77 /* component code: BIX_AGENT_STATUS */
78 /* component type: BIN */
79
80 PROCEDURE populate_agent_status_bin(p_context IN VARCHAR2)
81 AS
82 available number;
83 talk number;
84 wrap number;
85 idle number;
86 num_agents number;
87 out number;
88 loggedin number;
89 l_date_low date;
90 l_date_high date;
91
92 BEGIN
93
94 /* get data date range for today */
95 l_date_low := trunc(sysdate);
96 l_date_high := sysdate;
97
98 /* get session identifier for the web session calling this procedure */
99 g_session_id := bix_util_pkg.get_icx_session_id;
100
101 /* delete data from pervious runs */
102 delete from bix_dm_bin
103 where session_id = g_session_id
104 and bin_code = 'BIX_AGENT_STATUS';
105
106 /* get available agents */
107 select count(distinct I1.resource_id) into available
108 from IEU_SH_SESSIONS I1,
109 IEU_SH_ACTIVITIES I2
110 where I1.session_id = I2.session_id
111 and I1.application_id = 696
112 and I1.active_flag ='T'
113 and I1.end_date_time is NULL
114 and I2.activity_type_code = 'MEDIA'
115 and I2.active_flag = 'T'
116 and I2.end_date_time is NULL
117 and I2.deliver_date_time is NULL
118 and I1.begin_date_time between l_date_low and l_date_high;
119
120 /* get talking agents */
121 Select count(distinct C1.agent_id) into talk
122 from CCT_AGENT_RT_STATS C1
123 where C1.has_call = 'T'
124 and C1.last_update_date between l_date_low and l_date_high;
125
126 /* get wrapping agents */
127 Select count(distinct I1.resource_id) into wrap
128 from IEU_SH_SESSIONS I1,
129 IEU_SH_ACTIVITIES I2,
130 CCT_AGENT_RT_STATS C1
131 where I1.session_id = I2.session_id
132 and I1.application_id = 696
133 and I2.active_flag ='T'
134 and I2.activity_type_code = 'MEDIA'
135 and I2.end_date_time is null
136 and I2.deliver_date_time is not null
137 and I1.resource_id = C1.agent_id
138 and C1.has_call = 'F'
139 and I1.begin_date_time between l_date_low and l_date_high;
140
141 /* get agents who are logged into UWQ */
142 Select count(distinct I1.resource_id) into loggedin
143 from IEU_SH_SESSIONS I1
144 where I1.active_flag = 'T'
145 and I1.application_id = 696
146 and I1.end_date_time is NULL
147 and I1.begin_date_time between l_date_low and l_date_high;
148
149 /* get agents who are logged into UWQ */
150 Select count(distinct I1.resource_id) into out
151 from IEU_SH_SESSIONS I1
152 where I1.active_flag <> 'T'
153 and I1.application_id = 696
154 and I1.end_date_time is not NULL
155 and I1.begin_date_time between l_date_low and l_date_high
156 and I1.resource_id not in(
157 select distinct(resource_id) from IEU_SH_SESSIONS
158 where active_flag = 'T'
159 and application_id = 696
160 and end_date_time is null
161 and begin_date_time between l_date_low and l_date_high
162 );
163
164 idle := loggedin - talk - available - wrap;
165
166
167 /* insert the agent data into table for reporting */
168 insert into bix_dm_bin (bin_code, session_id, col1, col2, col4)
169 values ('BIX_AGENT_STATUS', g_session_id, '2', 'Available', available);
170
171 insert into bix_dm_bin (bin_code, session_id, col1, col2, col4)
172 values ('BIX_AGENT_STATUS', g_session_id, '3', 'Talk', talk);
173
174 insert into bix_dm_bin (bin_code, session_id, col1, col2, col4)
175 values ('BIX_AGENT_STATUS', g_session_id, '4', 'Wrap', wrap);
176
177 insert into bix_dm_bin (bin_code, session_id, col1, col2, col4)
178 values ('BIX_AGENT_STATUS', g_session_id, '5', 'Out', out);
179
180 insert into bix_dm_bin (bin_code, session_id, col1, col2, col4)
181 values ('BIX_AGENT_STATUS', g_session_id, '1', 'Idle', idle);
182
183 END populate_agent_status_bin;
184
185 /******************************************** BINs **************************/
186 /******************************************** end **************************/
187
188
189 END BIX_REAL_TIME_BINS_PKG;
[Home] [Help]