1 PACKAGE BODY BIX_CALLS_TYPE_BIN_PKG AS
2 /*$Header: bixxbctb.pls 115.11 2003/01/10 00:14:48 achanda ship $*/
3
4 PROCEDURE populate(p_context IN VARCHAR2 DEFAULT NULL)
5 IS
6
7 l_session_id NUMBER;
8 l_reporting_date DATE;
9
10 BEGIN
11
12 /* Get the ICX Session Id */
13 SELECT icx_sec.g_session_id
14 INTO l_session_id
15 FROM dual;
16
17 /* Delete the rows from the table bix_dm_bin for the current icx session and bin */
18 /* so that we donot display the leftover rows from the previous execution of the bin */
19 DELETE bix_dm_bin
20 WHERE bin_code = 'BIX_CALLS_TYPE_BIN'
21 AND session_id = l_session_id;
22
23 /* The bin will always display data for maximum date for which data has been collected */
24 SELECT MAX(period_start_date)
25 INTO l_reporting_date
26 FROM bix_dm_agent_call_sum;
27
28 /* Fetch all the records from the summary table for l_reporting_date grouped by classification */
29 INSERT /*+ PARALLEL(tb,2) */ INTO bix_dm_bin tb (
30 session_id
31 , bin_code
32 , col1
33 , col2
34 , col4
35 , col6
36 , col8 )
37 ( SELECT /*+ PARALLEL(acs,2) */
38 l_session_id
39 , 'BIX_CALLS_TYPE_BIN'
40 , to_char(acs.classification_id) || 'n'
41 , cct.classification
42 , bix_util_pkg.get_hrmiss_frmt(SUM(acs.abandon_time)
43 / DECODE(SUM(acs.calls_abandoned), 0, 1, SUM(acs.calls_abandoned)))
44 , bix_util_pkg.get_hrmiss_frmt(SUM(acs.queue_time)
45 / DECODE(SUM(acs.calls_in_queue), 0, 1, SUM(acs.calls_in_queue)))
46 , trunc((SUM(acs.calls_answrd_within_x_time)
47 / DECODE(SUM(acs.in_calls_handled), 0, 1, SUM(acs.in_calls_handled))) * 100, 2)
48 FROM bix_dm_agent_call_sum acs
49 , cct_classifications cct
50 WHERE acs.period_start_date = l_reporting_date
51 AND acs.classification_id = cct.classification_id
52 GROUP BY acs.classification_id
53 , cct.classification );
54
55 commit;
56
57 EXCEPTION
58 WHEN OTHERS
59 THEN RAISE;
60 END populate;
61
62 END BIX_CALLS_TYPE_BIN_PKG;
[Home] [Help]