1 PACKAGE BODY BIX_CALLS_HANDLED_RPT_PKG AS
2 /*$Header: bixxrchb.pls 115.17 2001/10/10 15:56:18 pkm ship $*/
3
4 g_session_id NUMBER;
5 g_sysdate DATE;
6 g_time_range NUMBER;
7 g_parent VARCHAR2(50);
8 g_agent_group VARCHAR2(50);
9 g_period_ind NUMBER;
10 g_from_date DATE;
11 g_from_time VARCHAR2(10);
12 g_to_date DATE;
13 g_to_time VARCHAR2(10);
14 g_sqlstmt VARCHAR2(4000);
15 g_nls_date_format VARCHAR2(50);
16
17
18 /* This procedure forms the SQL string to get either the total or detail information of an agent */
19 PROCEDURE form_rs_sqlstmt(p_total_row_ind IN BOOLEAN)
20 IS
21 BEGIN
22 g_sqlstmt := NULL;
23
24 /* Form the SQL string to insert the rows into the temp table */
25 g_sqlstmt := '
26 INSERT /*+ PARALLEL(tr,2) */ INTO bix_dm_report tr (
27 session_id
28 , report_code
29 , col3
30 , col4
31 , col5
32 , col6
33 , col7
34 , col8
35 , col10
36 , col12
37 , col13
38 , col14
39 , col15
40 , col16
41 , col17
42 , col18
43 , col20
44 , col22
45 , col23
46 , col24
47 , col25
48 , col26
49 , col27
50 , col28
51 , col30
52 , col32
53 , col34
54 , col36
55 , col38
56 , col40 )
57 (SELECT /*+ PARALLEL(a,2) */
58 :session_id
59 , ''BIX_CALLS_HANDLED_RPT'' ';
60
61 IF (p_total_row_ind = TRUE) THEN
62 /* If we are processing total row , then col3 = 'p' || group_id to which agent belongs */
63 /* || 'c' || agent_id || 'n' (used for drill down), col4 = agent name (which is */
64 /* displayed in the report) ; col5 = agent name || agent_id (used to sort rows) */
65 /* col7 = agent name (displayed in the graph) */
66 g_sqlstmt := g_sqlstmt ||
67 ' , ''p'' || mem.group_id || ''c'' || to_char(a.resource_id) || ''n''
68 , rsc.source_name
69 , rsc.source_name || to_char(a.resource_id)
70 , null
71 , rsc.source_name ';
72 ELSE
73 /* If we are processing detail row , then */
74 /* col5 = agent name || agent id || date or hour (used for sorting rows) */
75 /* col6 = date (if time range is 5) or hour (displayed in the report) */
76 /* col7 = agent name || date ot hour (displayed in the garph) */
77 g_sqlstmt := g_sqlstmt ||
78 ' , null
79 , null ';
80
81 /* If the time range is day report the data by date else by time */
82 IF (g_time_range = 5) THEN
83 g_sqlstmt := g_sqlstmt ||
84 ', rsc.source_name || to_char(a.resource_id) || to_char(period_start_date,''yyyy/mm/dd'')
85 , to_char(period_start_date,''' || g_nls_date_format || ''')
86 , rsc.source_name|| '' '' || to_char(a.period_start_date,''' || g_nls_date_format || ''') ';
87 ELSE
88 g_sqlstmt := g_sqlstmt ||
89 ', rsc.source_name || to_char(a.resource_id) || period_start_time
90 , to_char(to_date(period_start_time,''hh24:mi''),''hh:miAM'')
91 , rsc.source_name || '' '' || to_char(to_date(period_start_time,''hh24:mi''),''hh:miAM'') ';
92 END IF;
93
94
95 END IF;
96
97 /* Get the measures */
98 g_sqlstmt := g_sqlstmt ||
99 ' , bix_util_pkg.get_hrmiss_frmt(SUM(login_time))
100 , trunc(SUM(available_time + in_talk_time + out_talk_time + in_wrap_time + out_wrap_time)
101 / DECODE(SUM(login_time),0,1,SUM(login_time)) * 100,2)
102 , SUM(in_calls_handled)
103 , trunc(SUM(in_talk_time + in_wrap_time)
104 / DECODE(SUM(in_calls_handled),0,1,SUM(in_calls_handled)), 2)
105 , bix_util_pkg.get_hrmiss_frmt(SUM(in_talk_time + in_wrap_time)
106 / DECODE(SUM(in_calls_handled),0,1,SUM(in_calls_handled)))
107 , trunc(SUM(in_talk_time)
108 / DECODE(SUM(in_calls_handled),0,1,SUM(in_calls_handled)), 2)
109 , bix_util_pkg.get_hrmiss_frmt(SUM(in_talk_time)
110 / DECODE(SUM(in_calls_handled),0,1,SUM(in_calls_handled)))
111 , trunc(SUM(in_wrap_time)
112 / DECODE(SUM(in_calls_handled),0,1,SUM(in_calls_handled)), 2)
113 , bix_util_pkg.get_hrmiss_frmt(SUM(in_wrap_time)
114 / DECODE(SUM(in_calls_handled),0,1,SUM(in_calls_handled)))
115 , trunc(SUM(available_time + in_talk_time + in_wrap_time)
116 / DECODE(SUM(login_time),0,1,SUM(login_time)) * 100, 2)
117 , SUM(out_calls_handled)
118 , trunc(SUM(out_talk_time + out_wrap_time)
119 / DECODE(SUM(out_calls_handled),0,1,SUM(out_calls_handled)), 2)
120 , bix_util_pkg.get_hrmiss_frmt(SUM(out_talk_time + out_wrap_time)
121 / DECODE(SUM(out_calls_handled),0,1,SUM(out_calls_handled)))
122 , trunc(SUM(out_talk_time)
123 / DECODE(SUM(out_calls_handled),0,1,SUM(out_calls_handled)), 2)
124 , bix_util_pkg.get_hrmiss_frmt(SUM(out_talk_time)
125 / DECODE(SUM(out_calls_handled),0,1,SUM(out_calls_handled)))
126 , trunc(SUM(out_wrap_time)
127 / DECODE(SUM(out_calls_handled),0,1,SUM(out_calls_handled)), 2)
128 , bix_util_pkg.get_hrmiss_frmt(SUM(out_wrap_time)
129 / DECODE(SUM(out_calls_handled),0,1,SUM(out_calls_handled)))
130 , trunc(SUM(available_time + out_talk_time + out_wrap_time)
131 / DECODE(SUM(login_time),0,1,SUM(login_time)) * 100, 2)
132 , trunc(SUM(available_time)
133 / DECODE(SUM(login_time),0,1,SUM(login_time)) * 100, 2)
134 , trunc(SUM(idle_time)
135 / DECODE(SUM(login_time),0,1,SUM(login_time)) * 100, 2)
136 , trunc(SUM(in_wrap_time + out_wrap_time)
137 / DECODE(SUM(login_time),0,1,SUM(login_time)) * 100, 2)
138 , bix_util_pkg.get_hrmiss_frmt(SUM(available_time + in_talk_time + out_talk_time
139 + in_wrap_time + out_wrap_time))
140 , trunc(SUM(in_talk_time + out_talk_time + in_wrap_time + out_wrap_time)
141 / DECODE( SUM(available_time + in_talk_time + out_talk_time + in_wrap_time + out_wrap_time),0,1,
145 /* g_time_range = 1 implies user wants data by 1/2 hour : get the data from summary table */
142 SUM(available_time + in_talk_time + out_talk_time + in_wrap_time + out_wrap_time)) * 100,2) ';
143
144 /* Fetch the data from the appropiate MV depending on the time range */
146 /* g_time_range = 2 implies user wants data by 1 hour : get the data from 1 hour MV */
147 /* g_time_range = 3 implies user wants data by 2 hour : get the data from 2 hour MV */
148 /* g_time_range = 4 implies user wants data by 4 hour : get the data from 4 hour MV */
149 /* g_time_range = 5 implies user wants data by day : get the data from day MV */
150 IF (g_time_range = 1) THEN
151 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_call_sum a ';
152 ELSIF (g_time_range = 2) THEN
153 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_sum1_mv a ';
154 ELSIF (g_time_range = 3) THEN
155 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_sum2_mv a ';
156 ELSIF (g_time_range = 4) THEN
157 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_sum4_mv a ';
158 ELSE
159 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_sum_day_mv a ';
160 END IF;
161
162 /* If we are processing total row get all the agents belonging to the group from jtf_rs_group_members table */
163 IF (p_total_row_ind = TRUE) THEN
164 g_sqlstmt := g_sqlstmt ||
165 ', jtf_rs_group_members mem
166 , jtf_rs_resource_extns rsc ';
167 ELSE
168 g_sqlstmt := g_sqlstmt ||
169 ', jtf_rs_resource_extns rsc ';
170 END IF;
171
172 /* Add the filter condition for reporting period */
173 g_sqlstmt := g_sqlstmt || 'WHERE period_start_date_time between :start_date and :end_date ';
174
175 /* Add the appropiate join condition */
176 IF (p_total_row_ind = TRUE) THEN
177 g_sqlstmt := g_sqlstmt ||
178 'AND mem.group_id = :group_id
179 AND mem.resource_id = rsc.resource_id
180 AND rsc.resource_id = a.resource_id ';
181 ELSE
182 g_sqlstmt := g_sqlstmt ||
183 'AND a.resource_id = :resource_id
184 AND rsc.resource_id = a.resource_id ';
185 END IF;
186
187 /* Add the appropiate GROUP BY clause */
188 IF (p_total_row_ind = TRUE) THEN
189 g_sqlstmt := g_sqlstmt || 'GROUP BY mem.group_id, a.resource_id, rsc.source_name)';
190 ELSE
191 IF (g_time_range = 5) THEN
192 g_sqlstmt := g_sqlstmt || 'GROUP BY period_start_date, a.resource_id, rsc.source_name)';
193 ELSE
194 g_sqlstmt := g_sqlstmt || 'GROUP BY period_start_time, a.resource_id, rsc.source_name)';
195 END IF;
196 END IF;
197
198 EXCEPTION
199 WHEN OTHERS THEN
200 RAISE;
201 END form_rs_sqlstmt;
202
203 /* This procedure forms the SQL string to get either the total or detail information of a group */
204 PROCEDURE form_group_sqlstmt(p_total_row_ind IN BOOLEAN)
205 IS
206 BEGIN
207 g_sqlstmt := NULL;
208
212 session_id
209 /* Form the SQL string to insert the rows into the temp table */
210 g_sqlstmt := '
211 INSERT /*+ PARALLEL(tr,2) */ INTO bix_dm_report tr (
213 , report_code
214 , col2
215 , col3
216 , col4
217 , col5
218 , col7
219 , col8
220 , col10
221 , col12
222 , col13
223 , col14
224 , col15
225 , col16
226 , col17
227 , col18
228 , col20
229 , col22
230 , col23
231 , col24
232 , col25
233 , col26
234 , col27
235 , col28
236 , col30
237 , col32
238 , col34
239 , col36
240 , col38
241 , col40 )
242 (SELECT /*+ PARALLEL(a,2) */
243 :session_id
244 , ''BIX_CALLS_HANDLED_RPT'' ';
245
246 IF (p_total_row_ind = TRUE) THEN
247 /* If total row ; col2 = group name (displayed in the report) ; */
248 /* col5 = group name || group id (used to sort rows) */
249 /* col7 = group name (displayed in the graph) */
250 g_sqlstmt := g_sqlstmt ||
251 ' , grp.group_name
252 , null
253 , null
254 , grp.group_name || to_char(grp.group_id)
255 , grp.group_name ';
256 ELSE
257 /* If detail rows ; col3 = 'p' || group_id (used for drill down) ; col4 = group */
258 /* name (displayed in the report) ; col5 = group name || group id (to sort rows) */
259 /* col7 = group name (displayed in the graph) */
260 g_sqlstmt := g_sqlstmt ||
261 ' , null
262 , ''p'' || a.group_id
263 , grp.group_name
264 , grp.group_name || to_char(dnm.group_id)
265 , grp.group_name ';
266 END IF;
267
268 /* Get the measures */
269 g_sqlstmt := g_sqlstmt ||
270 ' , bix_util_pkg.get_hrmiss_frmt(SUM(login_time))
271 , trunc(SUM(available_time + in_talk_time + out_talk_time + in_wrap_time + out_wrap_time)
272 / DECODE(SUM(login_time),0,1,SUM(login_time)) * 100,2)
273 , SUM(in_calls_handled)
274 , trunc(SUM(in_talk_time + in_wrap_time)
275 / DECODE(SUM(in_calls_handled),0,1,SUM(in_calls_handled)), 2)
276 , bix_util_pkg.get_hrmiss_frmt(SUM(in_talk_time + in_wrap_time)
277 / DECODE(SUM(in_calls_handled),0,1,SUM(in_calls_handled)))
278 , trunc(SUM(in_talk_time)
279 / DECODE(SUM(in_calls_handled),0,1,SUM(in_calls_handled)), 2)
280 , bix_util_pkg.get_hrmiss_frmt(SUM(in_talk_time)
281 / DECODE(SUM(in_calls_handled),0,1,SUM(in_calls_handled)))
282 , trunc(SUM(in_wrap_time)
283 / DECODE(SUM(in_calls_handled),0,1,SUM(in_calls_handled)), 2)
284 , bix_util_pkg.get_hrmiss_frmt(SUM(in_wrap_time)
285 / DECODE(SUM(in_calls_handled),0,1,SUM(in_calls_handled)))
286 , trunc(SUM(available_time + in_talk_time + in_wrap_time)
290 / DECODE(SUM(out_calls_handled),0,1,SUM(out_calls_handled)), 2)
287 / DECODE(SUM(login_time),0,1,SUM(login_time)) * 100, 2)
288 , SUM(out_calls_handled)
289 , trunc(SUM(out_talk_time + out_wrap_time)
291 , bix_util_pkg.get_hrmiss_frmt(SUM(out_talk_time + out_wrap_time)
292 / DECODE(SUM(out_calls_handled),0,1,SUM(out_calls_handled)))
293 , trunc(SUM(out_talk_time)
294 / DECODE(SUM(out_calls_handled),0,1,SUM(out_calls_handled)), 2)
295 , bix_util_pkg.get_hrmiss_frmt(SUM(out_talk_time)
296 / DECODE(SUM(out_calls_handled),0,1,SUM(out_calls_handled)))
297 , trunc(SUM(out_wrap_time)
298 / DECODE(SUM(out_calls_handled),0,1,SUM(out_calls_handled)), 2)
299 , bix_util_pkg.get_hrmiss_frmt(SUM(out_wrap_time)
300 / DECODE(SUM(out_calls_handled),0,1,SUM(out_calls_handled)))
301 , trunc(SUM(available_time + out_talk_time + out_wrap_time)
302 / DECODE(SUM(login_time),0,1,SUM(login_time)) * 100, 2)
303 , trunc(SUM(available_time)
304 / DECODE(SUM(login_time),0,1,SUM(login_time)) * 100, 2)
305 , trunc(SUM(idle_time)
306 / DECODE(SUM(login_time),0,1,SUM(login_time)) * 100, 2)
307 , trunc(SUM(in_wrap_time + out_wrap_time)
308 / DECODE(SUM(login_time),0,1,SUM(login_time)) * 100, 2)
309 , bix_util_pkg.get_hrmiss_frmt(SUM(available_time + in_talk_time + out_talk_time
310 + in_wrap_time + out_wrap_time))
311 , trunc(SUM(in_talk_time + out_talk_time + in_wrap_time + out_wrap_time)
312 / DECODE( SUM(available_time + in_talk_time + out_talk_time + in_wrap_time + out_wrap_time),0,1,
313 SUM(available_time + in_talk_time + out_talk_time + in_wrap_time + out_wrap_time)) * 100,2) ';
314
315 /* Fetch the data from the appropiate MV depending on the time range */
316 /* g_time_range = 1 implies user wants data by 1/2 hour : get the data from summary table */
317 /* g_time_range = 2 implies user wants data by 1 hour : get the data from 1 hour MV */
318 /* g_time_range = 3 implies user wants data by 2 hour : get the data from 2 hour MV */
319 /* g_time_range = 4 implies user wants data by 4 hour : get the data from 4 hour MV */
320 /* g_time_range = 5 implies user wants data by day : get the data from day MV */
321 IF (g_time_range = 1) THEN
322 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_call_sum a, jtf_rs_groups_vl grp ';
323 ELSIF (g_time_range = 2) THEN
324 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum1_mv a, jtf_rs_groups_vl grp ';
325 ELSIF (g_time_range = 3) THEN
326 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum2_mv a, jtf_rs_groups_vl grp ';
327 ELSIF (g_time_range = 4) THEN
328 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum4_mv a, jtf_rs_groups_vl grp ';
329 ELSE
330 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum_day_mv a, jtf_rs_groups_vl grp ';
331 END IF;
332
333 /* If detial row get the child groups from the denorm table */
334 IF (p_total_row_ind = FALSE) THEN
335 g_sqlstmt := g_sqlstmt ||
336 ', jtf_rs_groups_denorm dnm ';
337 END IF;
338
339 /* Add the filter condition for reporting period */
340 g_sqlstmt := g_sqlstmt || 'WHERE period_start_date_time between :start_date and :end_date ';
341
342 /* Add the appropiate join condition */
343 IF (p_total_row_ind = FALSE) THEN
344 g_sqlstmt := g_sqlstmt ||
345 'AND dnm.parent_group_id = :group_id
346 AND dnm.group_id = grp.group_id
347 AND dnm.immediate_parent_flag = ''Y''
351 AND a.group_id = grp.group_id ';
348 AND a.group_id = grp.group_id ';
349 ELSE
350 g_sqlstmt := g_sqlstmt || 'AND a.group_id = :group_id
352 END IF;
353
354 /* Add the appropiate GROUP BY clause */
355 IF (p_total_row_ind = TRUE) THEN
356 g_sqlstmt := g_sqlstmt || 'GROUP BY grp.group_id, grp.group_name)';
357 ELSE
358 g_sqlstmt := g_sqlstmt || 'GROUP BY dnm.group_id, a.group_id, grp.group_name)';
359 END IF;
360
361 EXCEPTION
362 WHEN OTHERS THEN
363 RAISE;
364 END form_group_sqlstmt;
365
366 /* This procedure form and execute the SQL statement to insert into */
367 /* BIX temp table all the detail rows of a resource */
368 PROCEDURE insert_rs_detail_temp_table(p_agent_id IN NUMBER)
369 IS
370 BEGIN
371
372 /* Form and execute the SQL statement to insert into the temp table */
373 /* all the detail rows corresponding to the agent p_agent_id */
374 form_rs_sqlstmt(FALSE);
375 EXECUTE IMMEDIATE g_sqlstmt USING g_session_id, g_from_date, g_to_date, p_agent_id;
376
377 EXCEPTION
378 WHEN OTHERS THEN
379 RAISE;
380 END insert_rs_detail_temp_table;
381
382 /* This procedure form and execute the SQL statement to */
383 /* insert into BIX temp table the total row of a resource */
384 PROCEDURE insert_rs_total_temp_table(p_group_id IN NUMBER)
385 IS
386 BEGIN
387
388 /* Form and execute the SQL statement to insert into the temp table one */
389 /* row for each of the agents which belong to the agent group p_group_id */
390 form_rs_sqlstmt(TRUE);
391 EXECUTE IMMEDIATE g_sqlstmt USING g_session_id, g_from_date, g_to_date, p_group_id;
392
393 EXCEPTION
394 WHEN OTHERS THEN
395 RAISE;
396 END insert_rs_total_temp_table;
397
398 /* This procedure form and execute the SQL statement to insert both */
399 /* the detail and total row for the agent group */
400 PROCEDURE insert_group_temp_table(p_group_id IN NUMBER)
401 IS
402 BEGIN
403
404 /* Form and execute the SQL statement to insert into the temp table */
405 /* the total row corresponding to the agent group "p_group_id" */
406 form_group_sqlstmt(TRUE);
407 EXECUTE IMMEDIATE g_sqlstmt USING g_session_id, g_from_date, g_to_date, p_group_id;
408
409 /* Form and execute the SQL statement to insert into the temp table one row for each */
410 /* of the agent groups which are immediate children of the group p_group_id */
411 form_group_sqlstmt(FALSE);
412 EXECUTE IMMEDIATE g_sqlstmt USING g_session_id, g_from_date, g_to_date, p_group_id;
413
414 EXCEPTION
415 WHEN OTHERS THEN
416 RAISE;
417 END insert_group_temp_table;
418
419 /* This procedure gets all the paramter values including pContext by parsing the string p_context */
420 PROCEDURE get_param_values(p_context IN VARCHAR2)
421 IS
422 l_temp_date DATE;
423 BEGIN
424
425 /* Fetch the icx date format mask ; paramters from and to date is passed to the package in this format */
426 g_nls_date_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
427
428 /* Parse all the parameter values from the variable p_context : pContext (Drill down ID) , Agent Group */
429 /* Site , Classification , Period Indicator , From date and Time , To Date and Time */
430 g_parent := bix_util_pkg.get_parameter_value(p_context, 'pContext');
431 g_agent_group := bix_util_pkg.get_parameter_value(p_context, 'P_AGENT_GROUP');
432 g_period_ind := TO_NUMBER(bix_util_pkg.get_parameter_value(p_context, 'P_PERIOD_IND'));
433 g_from_date := TO_DATE(bix_util_pkg.get_parameter_value(p_context, 'P_FROM_DATE'), g_nls_date_format);
434 g_from_time := bix_util_pkg.get_parameter_value(p_context, 'P_FROM_TIME');
435 g_to_date := TO_DATE(bix_util_pkg.get_parameter_value(p_context, 'P_TO_DATE'), g_nls_date_format);
436 g_to_time := bix_util_pkg.get_parameter_value(p_context, 'P_TO_TIME');
437
438 /* Calculate the reporting period depending on the user input */
439 IF (g_period_ind IS NULL) THEN
440 /* If the period indicator is NULL then report on the maximum date for which data has been collected */
441 SELECT MAX(period_start_date)
442 INTO l_temp_date
443 FROM bix_dm_agent_call_sum;
444
445 g_from_date := to_date(to_char(l_temp_date, 'dd/mm/yyyy') || ' 00:00:00','dd/mm/yyyy hh24:mi:ss');
446 g_to_date := to_date(to_char(l_temp_date, 'dd/mm/yyyy') || ' 23:59:59','dd/mm/yyyy hh24:mi:ss');
447
448 ELSIF (g_period_ind = 7) THEN
449 /* Period Indicator = 7 indicates that user has selected today as reporting period */
450 g_from_date := to_date(to_char(g_sysdate, 'dd/mm/yyyy') || ' 00:00:00','dd/mm/yyyy hh24:mi:ss');
451 g_to_date := to_date(to_char(g_sysdate, 'dd/mm/yyyy') || ' 23:59:59','dd/mm/yyyy hh24:mi:ss');
452
453 ELSIF (g_period_ind = 8) THEN
454 /* Period Indicator = 8 indicates that user has selected yesterday as reporting period */
455 g_from_date := to_date(to_char(g_sysdate-1, 'dd/mm/yyyy') || ' 00:00:00','dd/mm/yyyy hh24:mi:ss');
456 g_to_date := to_date(to_char(g_sysdate-1, 'dd/mm/yyyy') || ' 23:59:59','dd/mm/yyyy hh24:mi:ss');
457
458 ELSIF (g_period_ind = 9) THEN
459 /* Period indicator = 9 indicates user has specified from date time and to date time */
460 g_from_date := to_date(to_char(g_from_date,'dd/mm/yyyy ') || g_from_time, 'dd/mm/yyyy hh24');
461 g_to_date := to_date(to_char(g_to_date,'dd/mm/yyyy ') || g_to_time, 'dd/mm/yyyy hh24');
462
463 ELSE
464 /* Period Indicator = 1 to 6 indicates Current Week , Prior Week , Current Month , Prior Month */
465 /* Current Year and Prior Year respectively ; get_time_range procedure will return appropiate */
466 /* g_from_date and g_to_date depending on the period indicator (g_period_ind) */
467 bix_util_pkg.get_time_range(g_period_ind, g_from_date, g_to_date);
468
472 WHEN OTHERS THEN
469 END IF;
470
471 EXCEPTION
473 RAISE;
474 END get_param_values;
475
476 /* This procedure "populate" is the starting point of the package */
477 PROCEDURE populate(p_context IN VARCHAR2)
478 IS
479
480 l_drill_down_ind VARCHAR2(1);
481 l_agent_id NUMBER;
482 l_group_id NUMBER;
483
484 BEGIN
485
486 /* Get the ICX Session Id */
487 SELECT icx_sec.g_session_id
488 INTO g_session_id
489 FROM dual;
490
491 /* Delete the rows from the table bix_dm_report for the current icx session and report */
492 /* so that we donot display the leftover rows from the previous execution of the report */
493 DELETE bix_dm_report
494 WHERE report_code = 'BIX_CALLS_HANDLED_RPT'
495 AND session_id = g_session_id;
496
497 /* Fetch the sysdate */
498 SELECT sysdate
499 INTO g_sysdate
500 FROM dual;
501
502 /* Get the User preferred time range (1/2 or 1 or 2 or 4 hour or 1 day) */
503 SELECT fnd_profile.value('BIX_DM_RPT_TIME_RANGE')
504 INTO g_time_range
505 FROM dual;
506
507 /* If there is no user prefered time range , set it to 1/2 hour */
508 IF g_time_range IS NULL THEN
509 g_time_range := 1;
510 END IF;
511
512 /* Get all the parameter values by parsing the string p_context */
513 get_param_values(p_context);
514
515 /* If pContext is region_code , then the user has navigated to the report */
516 /* from the report listing page ; so nothing should be displayed */
517 IF (g_parent = 'BIX_CALLS_HANDLED_RPT') THEN
518 RETURN;
519 END IF;
520
521 /* g_parent = "not null" : User has navigated to the report either from the bin or */
522 /* by clicking the child column in the report which is hyperlinked */
523 /* g_agent_group = "not null" : User has navigated to the report from the parameter page */
524 IF (g_parent IS NOT NULL) THEN
525 SELECT TO_NUMBER(substr(g_parent, 2, decode(instr(g_parent,'c'), 0, length(g_parent), instr(g_parent,'c')-2)))
526 INTO l_group_id
527 FROM dual;
528 ELSIF (g_agent_group IS NOT NULL) THEN
529 l_group_id := TO_NUMBER(g_agent_group);
530 ELSE
531 RETURN;
532 END IF;
533
534 /* If the user has selected "All" for agent group paramter , display the default group of the user */
535 IF (l_group_id = -999) THEN
536 SELECT fnd_profile.value('BIX_DM_DEFAULT_GROUP')
537 INTO l_group_id
538 FROM dual;
539 END IF;
540
541 /* l_group_id = "null" : user has selected "All" as agent group paramter */
542 /* and (s)he is not assigned to any default group */
543 IF (l_group_id IS NULL) THEN
544 RETURN;
545 END IF;
546
547 /* Process the agent group l_group_id and also which are immediate children of l_group_id */
548 insert_group_temp_table(l_group_id);
549
550 /* Process all the agents belonging to the group l_group_id */
551 insert_rs_total_temp_table(l_group_id);
552
553 /* Check if the user has clicked on an agent : if so we also have */
554 /* to insert the detail rows of the agent into the temp table */
555 IF (instr(g_parent,'c') <> 0) THEN
556 l_agent_id := TO_NUMBER(substr(g_parent, instr(g_parent, 'c')+1, length(g_parent) - (instr(g_parent,'c')+1)));
557 l_drill_down_ind := substr(g_parent, length(g_parent), 1);
558
559 /* l_drill_down_ind = 'n' : we have to display the detail rows of the agent */
560 IF (l_drill_down_ind = 'n') THEN
561 /* Update the temp table so that next time the user clicked on the */
562 /* same agent we donot display the detail rows of the agent again */
563 UPDATE bix_dm_report
564 SET col3 = 'p' || l_group_id || 'c' || to_char(l_agent_id) || 'y'
565 WHERE col3 = 'p' || l_group_id || 'c' || to_char(l_agent_id) || 'n'
566 AND report_code = 'BIX_CALLS_HANDLED_RPT'
567 AND session_id = g_session_id;
568
569 /* Fetch the detail rows of data for the agent from the */
570 /* summary table and insert them into bix temp table */
571 insert_rs_detail_temp_table(l_agent_id);
572 END IF;
573 END IF;
574
575 commit;
576
577 EXCEPTION
578 WHEN OTHERS THEN
579 RAISE;
580 END populate;
581
582 END BIX_CALLS_HANDLED_RPT_PKG;
[Home] [Help]