[Home] [Help]
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));
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
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 */
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'),