[Home] [Help]
PACKAGE BODY: APPS.BIX_QUEUE_DETAIL_REPORT
Source
1 PACKAGE BODY BIX_QUEUE_DETAIL_REPORT AS
2 /*$Header: bixxrqdb.pls 115.31 2003/01/10 00:14:25 achanda ship $*/
3
4 g_time_range NUMBER;
5 g_session_id NUMBER;
6 g_classification_id NUMBER;
7 g_classification VARCHAR2(80);
8 g_null_desc VARCHAR2(80);
9 g_drilldown NUMBER;
10 /* inserts data into bix_dm_report table used for reporting */
11 /* called by populate(p_context) procedure */
12 PROCEDURE insert_temp_table(p_classification_id IN NUMBER,
13 p_site_id IN NUMBER,
14 p_start_period IN DATE,
15 p_end_period IN DATE)
16 IS
17 l_index NUMBER;
18 v_classification_id NUMBER;
19 l_unclassified_count NUMBER;
20 v_classification VARCHAR2(80);
21
22 /* get all valid classifications for all calls */
23 cursor get_classifications is
24 select distinct c.classification_id, c.classification
25 from cct_classifications c, bix_dm_real_queue_sum b
26 where ((c.classification_id = p_classification_id) or (p_classification_id is null or p_classification_id = -999))
27 and b.classification_id = c.classification_id;
28 /*
29 and ((c.server_group_id = p_site_id) or (p_site_id is null or p_site_id = -1));
30 */
31
32 BEGIN
33
34 /* delete data from the previous runs */
35 delete bix_dm_report
36 where session_id = g_session_id
37 and report_code = 'BIX_QUEUE_DETAIL_REPORT';
38
39 /* find how many calls have no classification */
40 select count(*)
41 into l_unclassified_count
42 from bix_dm_real_queue_sum
43 where (classification_id is null or
44 classification_id not in
45 (select distinct classification_id
46 from cct_classifications));
47
48 l_index := 1; /* used for ordering data for report */
49 /* insert data for all classified calls for the given parameters */
50 for classifications in get_classifications LOOP
51 v_classification_id := classifications.classification_id;
52 v_classification := classifications.classification;
53 /* insert summary level data */
54 INSERT INTO bix_dm_report(
55 report_code
56 , session_id
57 , col1
58 , col2
59 , col3
60 , col4
61 , col6
62 , col8
63 , col10
64 , col12
65 , col14
66 , col16)
67 (SELECT
68 'BIX_QUEUE_DETAIL_REPORT'
69 , g_session_id
70 , 'c' || to_char(v_classification_id) || 'd' || '0'
71 , v_classification
72 , l_index
73 , NULL
74 , decode(SUM(CALLS_OFFERED), NULL, 0, SUM(CALLS_OFFERED))
75 , decode(SUM(CALLS_ABANDONED), NULL, 0, SUM(CALLS_ABANDONED))
76 , decode(SUM(CALLS_ANSWRD_WITHIN_X_TIME), NULL, 0, SUM(CALLS_ANSWRD_WITHIN_X_TIME))
77 , bix_util_pkg.get_hrmiss_frmt(SUM(ABANDON_TIME)/SUM(CALLS_ABANDONED))
78 ,bix_util_pkg.get_hrmiss_frmt(SUM(QUEUE_TIME_ANSWERED)/decode(SUM(calls_answered), 0, 1, SUM(calls_answered)))
79 ,bix_util_pkg.get_hrmiss_frmt(SUM(talk_time)/ DECODE(SUM(calls_handled),0,1,SUM(calls_handled)))
80 from bix_dm_real_queue_sum
81 where classification_id = v_classification_id
82 and session_id = g_session_id
83 and period_start_date_time between p_start_period and p_end_period
84 and ((server_group_id = p_site_id) or (p_site_id is null or p_site_id = -999)));
85 /* insert detailed rows into the bix_dm_report if drilldown clicked */
86 if (g_classification_id <> -999 and g_drilldown = 0) THEN
87 l_index := l_index + 1;
88 INSERT INTO bix_dm_report(
89 report_code
90 , session_id
91 , col1
92 , col2
93 , col3
94 , col4
95 , col6
96 , col8
97 , col10
98 , col12
99 , col14
100 , col16)
101 (SELECT
102 'BIX_QUEUE_DETAIL_REPORT'
103 , g_session_id
104 , l_index
105 , null
106 , l_index
107 , decode(g_time_range,1,to_char(to_date(period_start_time,'hh24:mi'), 'hh24:miAM'),
108 2,to_char(to_date(substr(period_start_time,1,2),'hh24:mi'), 'hh24:miAM'),
109 3,to_char(to_date(floor(substr(period_start_time,1,2) / 2) * 2, 'hh24:mi' ), 'hh24:miAM') ,
110 4,to_char(to_date(floor(substr(period_start_time,1,2) / 4) * 4, 'hh24:mi'), 'hh24:miAM'), period_start_date_time)
111 , decode(sum(CALLS_OFFERED), NULL, 0, sum(CALLS_OFFERED))
112 , decode(sum(CALLS_ABANDONED), NULL, 0,sum(CALLS_ABANDONED))
113 , decode(sum(CALLS_ANSWRD_WITHIN_X_TIME), NULL, 0,sum(CALLS_ANSWRD_WITHIN_X_TIME))
114 , bix_util_pkg.get_hrmiss_frmt(SUM(ABANDON_TIME)/SUM(CALLS_ABANDONED))
115 ,bix_util_pkg.get_hrmiss_frmt(SUM(QUEUE_TIME_ANSWERED)/decode(SUM(calls_answered), 0, 1, SUM(calls_answered)))
116 ,bix_util_pkg.get_hrmiss_frmt(SUM(talk_time)/ DECODE(SUM(calls_handled),0,1,SUM(calls_handled)))
117 from bix_dm_real_queue_sum
118 where classification_id = v_classification_id
119 and session_id = g_session_id
120 and classification_id = g_classification_id
121 and period_start_date_time between p_start_period and p_end_period
122 and ((server_group_id = p_site_id) or (p_site_id is null or p_site_id = -999))
123 GROUP BY decode(g_time_range,1,to_char(to_date(period_start_time,'hh24:mi'), 'hh24:miAM'),
124 2,to_char(to_date(substr(period_start_time,1,2),'hh24:mi'), 'hh24:miAM'),
125 3,to_char(to_date(floor(substr(period_start_time,1,2) / 2) * 2, 'hh24:mi' ), 'hh24:miAM') ,
126 4,to_char(to_date(floor(substr(period_start_time,1,2) / 4) * 4, 'hh24:mi'), 'hh24:miAM'), period_start_date_time));
127 /* update the drilldown to contract on click */
128 /* g_drilldown = 1 implies contract */
129 update bix_dm_report
130 set col1 = 'c' || to_char(v_classification_id) || 'd' || '1'
131 where report_code = 'BIX_QUEUE_DETAIL_REPORT'
132 and session_id = g_session_id
133 and col2 = v_classification
134 and v_classification = g_classification;
135 END IF;
136 l_index := l_index + 1;
137 END LOOP;
138 l_index := l_index + 1;
139
140 /* get data for unclassified calls */
141 IF (l_unclassified_count > 0) then
142 /* insert summary level data into table only if no classification */
143 /* is selected */
144 IF (p_classification_id = -999 or p_classification_id is NULL) then
145 INSERT INTO bix_dm_report(
146 report_code
147 , session_id
148 , col1
149 , col2
150 , col3
151 , col4
152 , col6
153 , col8
154 , col10
155 , col12
156 , col14
157 , col16)
158 (SELECT
159 'BIX_QUEUE_DETAIL_REPORT'
160 , g_session_id
161 , 'c' || to_char(-9999) || 'd' || '0'
162 -- ,g_null_desc
163 ,'unClassified'
164 , l_index
165 , NULL
166 , decode(SUM(CALLS_OFFERED), NULL, 0, SUM(CALLS_OFFERED))
167 , decode(SUM(CALLS_ABANDONED), NULL, 0, SUM(CALLS_ABANDONED))
168 , decode(SUM(CALLS_ANSWRD_WITHIN_X_TIME), NULL, 0, SUM(CALLS_ANSWRD_WITHIN_X_TIME))
169 , bix_util_pkg.get_hrmiss_frmt(SUM(ABANDON_TIME)/SUM(CALLS_ABANDONED))
170 ,bix_util_pkg.get_hrmiss_frmt(SUM(QUEUE_TIME_ANSWERED)/decode(SUM(calls_answered), 0, 1, SUM(calls_answered)))
171 ,bix_util_pkg.get_hrmiss_frmt(SUM(talk_time)/ DECODE(SUM(calls_handled),0,1,SUM(calls_handled)))
172 from bix_dm_real_queue_sum
173 where ( classification_id is null or classification_id not in
174 (select distinct classification_id
175 from cct_classifications))
176 and period_start_date_time between p_start_period and p_end_period
177 and session_id = g_session_id
178 and ((server_group_id = p_site_id) or (p_site_id is null or p_site_id = -999)));
179 end if; /* summary level data for unclassified calls */
180 /* insert detailed rows for unclassified calls only if drilldown clicked */
181 if ((g_classification_id = -9999 and g_drilldown = 0) and (p_classification_id = -999 or p_classification_id is null)) THEN
182 l_index := l_index + 1;
183 INSERT INTO bix_dm_report(
184 report_code
185 , session_id
186 , col1
187 , col2
188 , col3
189 , col4
190 , col6
191 , col8
192 , col10
193 , col12
194 , col14
195 , col16)
196 (SELECT
197 'BIX_QUEUE_DETAIL_REPORT'
198 , g_session_id
199 , l_index
200 , null
201 , l_index
202 , decode(g_time_range,1,to_char(to_date(period_start_time,'hh24:mi'), 'hh24:miAM'),
203 2,to_char(to_date(substr(period_start_time,1,2),'hh24:mi'), 'hh24:miAM'),
204 3,to_char(to_date(floor(substr(period_start_time,1,2) / 2) * 2, 'hh24:mi' ), 'hh24:miAM') ,
205 4,to_char(to_date(floor(substr(period_start_time,1,2) / 4) * 4, 'hh24:mi'), 'hh24:miAM'), period_start_date_time)
206 , decode(sum(CALLS_OFFERED), NULL, 0, sum(CALLS_OFFERED))
207 , decode(sum(CALLS_ABANDONED), NULL, 0,sum(CALLS_ABANDONED))
208 , decode(sum(CALLS_ANSWRD_WITHIN_X_TIME), NULL, 0,sum(CALLS_ANSWRD_WITHIN_X_TIME))
209 , bix_util_pkg.get_hrmiss_frmt(SUM(ABANDON_TIME)/SUM(CALLS_ABANDONED))
210 ,bix_util_pkg.get_hrmiss_frmt(SUM(QUEUE_TIME_ANSWERED)/decode(SUM(calls_answered), 0, 1, SUM(calls_answered)))
211 ,bix_util_pkg.get_hrmiss_frmt(SUM(talk_time)/ DECODE(SUM(calls_handled),0,1,SUM(calls_handled)))
212 from bix_dm_real_queue_sum
213 where (classification_id is null or classification_id not in
214 (select distinct classification_id
215 from cct_classifications))
216 and session_id = g_session_id
217 and period_start_date_time between p_start_period and p_end_period
218 and ((server_group_id = p_site_id) or (p_site_id is null or p_site_id = -999))
219 GROUP BY decode(g_time_range,1,to_char(to_date(period_start_time,'hh24:mi'), 'hh24:miAM'),
220 2,to_char(to_date(substr(period_start_time,1,2),'hh24:mi'), 'hh24:miAM'),
221 3,to_char(to_date(floor(substr(period_start_time,1,2) / 2) * 2, 'hh24:mi' ), 'hh24:miAM') ,
222 4,to_char(to_date(floor(substr(period_start_time,1,2) / 4) * 4, 'hh24:mi'), 'hh24:miAM'), period_start_date_time));
223 /* update the drilldown to contract on click */
224 /* g_drilldown = 1 implies contract */
225 update bix_dm_report
226 set col1 = 'c' || to_char(-9999) || 'd' || '1'
227 where report_code = 'BIX_QUEUE_DETAIL_REPORT'
228 and session_id = g_session_id
229 and col2 = g_null_desc;
230 end if; /* detailed rows for unclassified calls */
231 end if; /* data for unclassified calls */
232
233 delete from bix_dm_real_queue_sum
234 where session_id = g_session_id;
235
236
237 END insert_temp_table;
238
239 /* invoked from the Queue Detail Report */
240 /* parses all the report user parameters and calls */
241 /* insert_temp_table procedure */
242 PROCEDURE populate(p_context IN VARCHAR2)
243 IS
244 v_classification_id NUMBER;
245 p_site_id NUMBER;
246 p_start_period DATE;
247 p_end_period DATE;
248 v_site_id NUMBER;
249 v_start_period DATE;
250 v_end_period DATE;
251 v_parent VARCHAR2(80);
252 l_nls_date_fmt VARCHAR2(80);
253 l_null_desc_count number;
254 BEGIN
255
256 /* get reporting time range for detailed level data */
257 SELECT fnd_profile.value('BIX_DM_RPT_TIME_RANGE')
258 INTO g_time_range
259 FROM dual;
260 /* get label for un classified calls */
261 select count(meaning)
262 into l_null_desc_count
263 FROM fnd_lookups
264 WHERE lookup_type = 'BIX_DM_NULL_DESC'
265 AND lookup_code = 'NULL';
266
267 if (l_null_desc_count = 1) then
268 SELECT meaning
269 INTO g_null_desc
270 FROM fnd_lookups
271 WHERE lookup_type = 'BIX_DM_NULL_DESC'
272 AND lookup_code = 'NULL';
273 else
274 g_null_desc := 'UNKNOWN';
275 end if;
276
277
278 g_drilldown := 0; /* drilldown default to yes */
279 g_session_id := bix_util_pkg.get_icx_session_id; /* get current session id */
280 BIX_DM_REAL_QUEUE_SUMMARY_PKG.get_calls(g_session_id);
281 /* get the classification id for the clicked row or data */
282 v_parent := jtfb_dcf.get_parameter_value(p_context, 'pContext');
283 /* to make the report listing link work */
284 if (v_parent = 'BIX_QUEUE_DETAIL_REPORT') then
285 v_parent := 'NOT_FOUND';
286 end if;
287 /* find if the classification was clicked in the report */
288 /* g_drilldown = 0 means the classification needs to be expanded, else */
289 /* collapsed */
290 g_classification_id := -999;
291 if (v_parent = 'NOT_FOUND') THEN
292 g_classification_id := -999;
293 else
294 g_classification_id := to_number(substr(v_parent, 2, instr(v_parent, 'd')-2));
295 if (g_classification_id <> -9999) then
296 select classification_name
297 into g_classification
298 from bix_dm_classification_param_v
299 where to_number(classification_id) = g_classification_id;
300 end if;
301 g_drilldown := to_number(substr(v_parent, instr(v_parent, 'd')+ 1, 1));
302 end if;
303 IF g_time_range IS NULL THEN
304 g_time_range := 1;
305 END IF;
306 /* retrieve the values for report user parameters */
307 if (jtfb_dcf.get_parameter_value(p_context,'P_CLASSIFICATION_ID') = 'NOT_FOUND') THEN
308 v_classification_id := -999;
309 else
310 v_classification_id := to_number(jtfb_dcf.get_parameter_value(p_context,'P_CLASSIFICATION_ID'));
311 end if;
312 if (jtfb_dcf.get_parameter_value(p_context,'P_SITE_ID') = 'NOT_FOUND') THEN
313 v_site_id := -999;
314 else
315 v_site_id := to_number(jtfb_dcf.get_parameter_value(p_context,'P_SITE_ID'));
316 end if;
317
318 /* initialize data date range for today */
319 v_start_period := trunc(sysdate);
320 v_end_period := sysdate;
321 /* insert data into bix_dm_report */
322 insert_temp_table(v_classification_id, v_site_id, v_start_period, v_end_period);
323
324 EXCEPTION
325 WHEN OTHERS
326 THEN RETURN;
327 END populate;
328
329 FUNCTION get_heading RETURN varchar2
330 IS
331 l_label VARCHAR2(1000);
332 l_message VARCHAR2(1000);
333
334 l_date DATE;
335 BEGIN
336 select max(period_start_date_time)
337 into l_date
338 from bix_dm_real_queue_sum;
339
340 l_message := fnd_message.get_string('BIX', 'BIX_DM_REFRESH_MSG') ;
341 l_label := l_message || ' ' ||to_char(l_date, 'DD-MON-YYYY HH12:MI:SS AM');
342 return l_label;
343 END;
344 END BIX_QUEUE_DETAIL_REPORT;