1 PACKAGE BODY BIX_CALLS_TYPE_RPT_PKG AS
2 /*$Header: bixxrctb.pls 115.16 2001/09/27 10:42:45 pkm ship $*/
3
4 g_session_id NUMBER;
5 g_time_range NUMBER;
6 g_site_id NUMBER;
7 g_period_ind NUMBER;
8 g_from_date DATE;
9 g_from_time VARCHAR2(10);
10 g_to_date DATE;
11 g_to_time VARCHAR2(10);
12 g_sqlstmt VARCHAR2(4000);
13 g_sysdate DATE;
14 g_classification_param VARCHAR2(50);
15 g_classification_parent VARCHAR2(50);
16 g_null_desc VARCHAR2(50);
17 g_nls_date_format VARCHAR2(50);
18
19 /* This procedure forms the SQL statement to process both the total and detail row of a classification */
20 PROCEDURE form_sqlstmt(p_total_row_ind IN BOOLEAN,
21 p_classification_id IN NUMBER) IS
22 BEGIN
23
24 g_sqlstmt := NULL;
25
26 /* Form the SQL string to insert the rows into the temp table */
27 g_sqlstmt := '
28 INSERT /*+ PARALLEL(tr,2) */ INTO bix_dm_report tr (
29 session_id
30 , report_code
31 , col1
32 , col2
33 , col3
34 , col4
35 , col5
36 , col6
37 , col8
38 , col10
39 , col12
40 , col14
41 , col16
42 , col18
43 , col20
44 , col22
45 , col24
46 , col26
47 , col28
48 , col30 )
49 (SELECT /*+ PARALLEL(a,2) */
50 :session_id
51 , ''BIX_CALLS_TYPE_RPT'' ';
52
53 /* If detail row */
54 /* col3 : classification_id || period_start_date (used for sorting) */
55 /* col4 : period start date (displayed in the report) */
56 /* col5 : classification desc || period_start_date (displayed in the graph) */
57 IF (p_total_row_ind = FALSE) THEN
58 g_sqlstmt := g_sqlstmt ||
59 ', null
60 , null ';
61
62 /* If the time range is day report the data by date else by time */
63 IF (g_time_range = 5) THEN
64 g_sqlstmt := g_sqlstmt ||
65 ', nvl(to_char(a.classification_id),''-999'') || to_char(a.period_start_date,''yyyy/mm/dd'')
66 , to_char(period_start_date,''' || g_nls_date_format || ''')
67 , nvl(b.classification,:null_desc) || '' '' ||
68 to_char(a.period_start_date,''' || g_nls_date_format || ''') ';
69 ELSE
70 g_sqlstmt := g_sqlstmt ||
71 ', nvl(to_char(a.classification_id),''-999'') || to_char(a.period_start_time)
72 , to_char(to_date(a.period_start_time,''hh24:mi''),''hh:miAM'')
73 , nvl(b.classification,:null_desc) || '' ''
74 || to_char(to_date(a.period_start_time,''hh24:mi''),''hh:miAM'') ';
75 END IF;
76
77 ELSE
78 /* If detail row */
79 /* col1 : classification_id || 'y' (Used for drill down) */
80 /* col2 : classification description (displayed in the report) */
81 /* col3 : classification_id (used for sorting) */
82 /* col5 : classification desc (displayed in the graph) */
83 g_sqlstmt := g_sqlstmt ||
84 ', nvl(to_char(a.classification_id),''-999'') || ''y''
85 , nvl(b.classification,:null_desc)
86 , nvl(to_char(a.classification_id),''-999'')
87 , null
88 , nvl(b.classification,:null_desc) ';
89 END IF;
90
91 /* Get the measures */
92 g_sqlstmt := g_sqlstmt ||
93 ' , trunc((SUM(calls_answrd_within_x_time)
94 / DECODE(SUM(in_calls_handled),0,1,SUM(in_calls_handled))) * 100, 2)
95 , SUM(calls_answrd_within_x_time)
96 , bix_util_pkg.get_hrmiss_frmt(SUM(queue_time)
97 / DECODE(SUM(in_calls_handled),0,1,SUM(in_calls_handled)))
98 , bix_util_pkg.get_hrmiss_frmt(MAX(max_queue_time))
99 , bix_util_pkg.get_hrmiss_frmt(SUM(abandon_time)
100 / DECODE(SUM(calls_abandoned),0,1,SUM(calls_abandoned)))
101 , bix_util_pkg.get_hrmiss_frmt(MAX(max_abandon_time))
102 , bix_util_pkg.get_hrmiss_frmt(SUM(ivr_time + available_time + in_talk_time + out_talk_time +
103 in_wrap_time + out_wrap_time) / DECODE(SUM(in_calls_handled + out_calls_handled),0,1,
104 SUM(in_calls_handled + out_calls_handled)))
105 , SUM(calls_offered)
106 , SUM(in_calls_handled + out_calls_handled)
107 , SUM(calls_abandoned)
108 , SUM(calls_transfered)
109 , SUM(primary_count)
110 , trunc(SUM(primary_count)
111 / DECODE(SUM(primary_count + other_count), 0, 1, SUM(primary_count + other_count)) * 100, 2) ';
112
113 /* Fetch the data from the appropiate MV depending on the time range */
114 /* g_time_range = 1 implies user wants data by 1/2 hour : get the data from summary table */
115 /* g_time_range = 2 implies user wants data by 1 hour : get the data from 1 hour MV */
116 /* g_time_range = 3 implies user wants data by 2 hour : get the data from 2 hour MV */
117 /* g_time_range = 4 implies user wants data by 4 hour : get the data from 4 hour MV */
118 /* g_time_range = 5 implies user wants data by day : get the data from day MV */
119 IF (g_time_range = 1) THEN
120 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_call_sum a, cct_classifications b ';
121 ELSIF (g_time_range = 2) THEN
122 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_sum1_mv a, cct_classifications b ';
123 ELSIF (g_time_range = 3) THEN
124 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_sum2_mv a, cct_classifications b ';
125 ELSIF (g_time_range = 4) THEN
126 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_sum4_mv a, cct_classifications b ';
127 ELSE
128 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_sum_day_mv a, cct_classifications b ';
129 END IF;
130
131 /* Add the join condition ot the classification master table */
132 /* Outer join is necessary to get the rows which have null classification */
133 IF (p_classification_id = -999) THEN
134 g_sqlstmt := g_sqlstmt || 'WHERE a.classification_id = b.classification_id(+)
135 AND :classification_id = -999 ';
136 IF (p_total_row_ind = FALSE) THEN
137 /* This is necessary to get the detail rows of null classification */
138 g_sqlstmt := g_sqlstmt || 'AND a.classification_id IS NULL ';
139 END IF;
140 ELSE
141 g_sqlstmt := g_sqlstmt || 'WHERE a.classification_id = b.classification_id
142 AND a.classification_id = :classification_id ';
143 END IF;
144
145 /* Apply the filter condition for reporting period */
146 g_sqlstmt := g_sqlstmt || 'AND a.period_start_date_time between :start_date and :end_date ';
147
148 /* Add the filer condition for site if the user has chosen */
149 /* a particular value for site (or all) in the parameter */
150 IF (g_site_id <> -999) THEN
151 g_sqlstmt := g_sqlstmt || 'AND a.server_group_id = :site_id ';
152 ELSE
153 g_sqlstmt := g_sqlstmt || 'AND :site_id = -999 ';
154 END IF;
155
156 /* Concatenate the appropiate group by clause */
157 IF (p_total_row_ind = FALSE) THEN
158 IF (g_time_range = 5) THEN
159 g_sqlstmt := g_sqlstmt || 'GROUP BY a.classification_id, b.classification, a.period_start_date)';
160 ELSE
161 g_sqlstmt := g_sqlstmt || 'GROUP BY a.classification_id, b.classification, a.period_start_time)';
162 END IF;
163 ELSE
164 g_sqlstmt := g_sqlstmt || 'GROUP BY a.classification_id, b.classification)';
165 END IF;
166
167 EXCEPTION
168 WHEN OTHERS THEN
169 RAISE;
170 END form_sqlstmt;
171
172 /* This procedure forms and executes the SQL statement that inserts the */
173 /* detail rows corresponding o the classification in the table bix_dm_report */
174 PROCEDURE insert_detail_rows(p_classification_id IN NUMBER)
175 IS
176 BEGIN
177
178 /* Form and execute the SQL statement to insert into the temp table */
179 /* the detail rows coreesponding to the classification p_classification_id */
180 form_sqlstmt(FALSE, p_classification_id);
181 EXECUTE IMMEDIATE g_sqlstmt USING g_session_id, g_null_desc, p_classification_id, g_from_date, g_to_date, g_site_id;
182
183 EXCEPTION
184 WHEN OTHERS THEN
185 RAISE;
186 END insert_detail_rows;
187
188 /* This procedure forms and executes the SQL statement that inserts the */
189 /* total row corresponding o the classification in the table bix_dm_report */
190 PROCEDURE insert_total_rows(p_classification_id IN NUMBER)
191 IS
192 BEGIN
193
194 /* Form and execute the SQL statement to insert into the temp table */
195 /* the total rows coreesponding to the classifications p_classification_id */
196 form_sqlstmt(TRUE, p_classification_id);
197 EXECUTE IMMEDIATE g_sqlstmt USING g_session_id, g_null_desc, g_null_desc, p_classification_id, g_from_date,
198 g_to_date, g_site_id;
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 RAISE;
203 END insert_total_rows;
204
205
206 /* This procedure gets all the paramter values including pContext by parsing the string p_context */
207 PROCEDURE get_param_values(p_context IN VARCHAR2)
208 IS
209 l_temp_date DATE;
210 BEGIN
211
212 /* Fetch the icx date format mask ; paramters from and to date is passed to the package in this format */
213 g_nls_date_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
214
215 /* Parse all the parameter values from the variable p_context : pContext (Drill down ID) , */
216 /* Classification Site , Period Indicator , From date and Time , To Date and Time */
217 g_classification_parent := bix_util_pkg.get_parameter_value(p_context, 'pContext');
218 g_classification_param := bix_util_pkg.get_parameter_value(p_context, 'P_CLASSIFICATION');
219 g_site_id := nvl(TO_NUMBER(bix_util_pkg.get_parameter_value(p_context, 'P_SITE_ID')),-999);
220 g_period_ind := TO_NUMBER(bix_util_pkg.get_parameter_value(p_context, 'P_TIME_RANGE'));
221 g_from_date := TO_DATE(bix_util_pkg.get_parameter_value(p_context, 'P_START_DATE'), g_nls_date_format);
222 g_from_time := bix_util_pkg.get_parameter_value(p_context, 'P_FROM_TIME');
223 g_to_date := TO_DATE(bix_util_pkg.get_parameter_value(p_context, 'P_END_DATE'), g_nls_date_format);
224 g_to_time := bix_util_pkg.get_parameter_value(p_context, 'P_TO_TIME');
225
226 /* Calculate the reporting period depending on the user input */
227 IF (g_period_ind IS NULL) THEN
228 /* If the period indicator is NULL then report on the maximum date for which data has been collected */
229 SELECT MAX(period_start_date)
230 INTO l_temp_date
231 FROM bix_dm_agent_call_sum;
232
233 g_from_date := to_date(to_char(l_temp_date, 'dd/mm/yyyy') || ' 00:00:00','dd/mm/yyyy hh24:mi:ss');
234 g_to_date := to_date(to_char(l_temp_date, 'dd/mm/yyyy') || ' 23:59:59','dd/mm/yyyy hh24:mi:ss');
235
236 ELSIF (g_period_ind = 7) THEN
237 /* Period Indicator = 7 indicates that user has selected today as reporting period */
238 g_from_date := to_date(to_char(g_sysdate, 'dd/mm/yyyy') || ' 00:00:00','dd/mm/yyyy hh24:mi:ss');
239 g_to_date := to_date(to_char(g_sysdate, 'dd/mm/yyyy') || ' 23:59:59','dd/mm/yyyy hh24:mi:ss');
240
241 ELSIF (g_period_ind = 8) THEN
242 /* Period Indicator = 8 indicates that user has selected yesterday as reporting period */
243 g_from_date := to_date(to_char(g_sysdate-1, 'dd/mm/yyyy') || ' 00:00:00','dd/mm/yyyy hh24:mi:ss');
244 g_to_date := to_date(to_char(g_sysdate-1, 'dd/mm/yyyy') || ' 23:59:59','dd/mm/yyyy hh24:mi:ss');
245
246 ELSIF (g_period_ind = 9) THEN
247 /* Period indicator = 9 indicates user has specified from date time and to date time */
248 g_from_date := to_date(to_char(g_from_date,'dd/mm/yyyy ') || g_from_time, 'dd/mm/yyyy hh24');
249 g_to_date := to_date(to_char(g_to_date,'dd/mm/yyyy ') || g_to_time, 'dd/mm/yyyy hh24');
250
251 ELSE
252 /* Period Indicator = 1 to 6 indicates Current Week , Prior Week , Current Month , Prior Month */
253 /* Current Year and Prior Year respectively ; get_time_range procedure will return appropiate */
254 /* g_from_date and g_to_date depending on the period indicator (g_period_ind) */
255 bix_util_pkg.get_time_range(g_period_ind, g_from_date, g_to_date);
256
257 END IF;
258
259 EXCEPTION
260 WHEN OTHERS THEN
261 RAISE;
262 END get_param_values;
263
264
265 /* This procedure "populate" is the starting point of the package */
266 PROCEDURE populate(p_context IN VARCHAR2 DEFAULT NULL)
267 IS
268 l_drill_down_ind VARCHAR2(1);
269 l_classification_id NUMBER;
270 BEGIN
271
272 /* Get the ICX Session Id */
273 SELECT icx_sec.g_session_id
274 INTO g_session_id
275 FROM dual;
276
277 /* Delete the rows from the table bix_dm_report for the current icx session and report */
278 /* so that we donot display the leftover rows from the previous execution of the report */
279 DELETE bix_dm_report
280 WHERE report_code = 'BIX_CALLS_TYPE_RPT'
284 SELECT sysdate
281 AND session_id = g_session_id;
282
283 /* Fetch the sysdate into variable g_sysdate */
285 INTO g_sysdate
286 FROM dual;
287
288 /* Get the description of NULL */
289 g_null_desc := bix_util_pkg.get_null_lookup;
290
291 /* Fetch the time range (1 hour or 2 hour etc. ) preference of the user */
292 SELECT fnd_profile.value('BIX_DM_RPT_TIME_RANGE')
293 INTO g_time_range
294 FROM dual;
295
296 /* If the user has not specified any time range then report on 1/2 hour basis */
297 IF g_time_range IS NULL THEN
298 g_time_range := 1;
299 END IF;
300
301 /* Get all the parameter values */
302 get_param_values(p_context);
303
304 /* If pContext is region_code , then the user has navigated to the report */
305 /* from the report listing page ; so nothing should be displayed */
306 IF (g_classification_parent = 'BIX_CALLS_TYPE_RPT') THEN
307 RETURN;
308 END IF;
309
310 /* Insert the total rows corresponding to the classification */
311 IF (g_classification_param IS NOT NULL) THEN
312 insert_total_rows(to_number(g_classification_param));
313 ELSIF (g_classification_parent IS NOT NULL) THEN
314 insert_total_rows(to_number(substr(g_classification_parent, 1, length(g_classification_parent)-1)));
315 ELSE
316 RETURN;
317 END IF;
318
319 /* Get the drill down indicator */
320 l_drill_down_ind := substr(g_classification_parent, length(g_classification_parent), 1);
321
322 /* l_drill_down_ind = 'y' : we have to display the detail rows of the classification */
323 IF (l_drill_down_ind = 'y') THEN
324
325 l_classification_id := to_number(substr(g_classification_parent, 1, length(g_classification_parent)-1));
326
327 /* Update the temp table so that next time the user clicked on the */
328 /* same classification we donot display the detail rows again */
329 UPDATE bix_dm_report
330 SET col1 = to_char(l_classification_id) || 'n'
331 WHERE col1 = to_char(l_classification_id) || 'y'
332 AND report_code = 'BIX_CALLS_TYPE_RPT'
333 AND session_id = g_session_id;
334
335 /* Fetch the detail rows of data for the classification from the */
336 /* summary table and insert them into bix temp table */
337 insert_detail_rows(l_classification_id);
338 END IF;
339
340 commit;
341
342 EXCEPTION
343 WHEN OTHERS THEN
344 RAISE;
345 END populate;
346
347 END BIX_CALLS_TYPE_RPT_PKG;
[Home] [Help]