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