[Home] [Help]
PACKAGE BODY: APPS.BIX_AGENT_CLASS_P
Source
1 PACKAGE BODY BIX_AGENT_CLASS_P AS
2 /*$Header: bixxabcb.pls 115.31 2001/10/26 17:44:34 pkm ship $*/
3
4 g_sysdate DATE;
5 g_time_range NUMBER;
6 g_parent VARCHAR2(50);
7 g_agent_group VARCHAR2(50);
8 g_period_ind NUMBER;
9 g_from_date DATE;
10 g_from_time VARCHAR2(10);
11 g_to_date DATE;
12 g_to_time VARCHAR2(10);
13 g_class number;
14 g_site number;
15 g_sqlstmt VARCHAR2(4000);
16 g_classname VARCHAR2(100);
17 l_session_id number;
18 g_idx number;
19
20
21 PROCEDURE form_rs_sqlstmt(p_total_row_ind IN number)
22 IS
23
24 BEGIN
25 g_sqlstmt := NULL;
26
27 /* Form the SQL string to insert the rows into the temp table */
28 g_sqlstmt := '
29 INSERT /*+ PARALLEL(tr,2) */ INTO BIX_DM_REPORT tr (
30 session_id,
31 report_code
32 , col3
33 , col4
34 , col5
35 , col6
36 , col8
37 , col10
38 , col12
39 , col13
40 , col14
41 , col15
42 , col16
43 , col17
44 , col18
45 , col20
46 , col22
47 , col23
48 , col24
49 , col25
50 , col26
51 , col27
52 , col28
53 , col30
54 , col32
55 , col34
56 , col36
57 , col38
58 , col40 ) ';
59 g_sqlstmt := g_sqlstmt || ' (SELECT ' || to_char(l_session_id) || ',' || ' ''BIX_AGENT_CLASS_REPORT'' ';
60
61 IF (p_total_row_ind = 1/*TRUE*/) THEN
62 g_sqlstmt := g_sqlstmt ||
63 ' , ''p'' || mem.group_id || ''c'' || to_char(a.resource_id) || ''n''
64 , rsc.source_name
65 , a.resource_id
66 , null
67 , null ';
68 ELSif (p_total_row_ind = 2) then
69 IF (g_time_range = 5) THEN
70 g_sqlstmt := g_sqlstmt ||
71 ' , a.resource_id
72 , null
73 , a.resource_id
74 , null
75 , to_char(period_start_date) ';
76 ELSE
77 g_sqlstmt := g_sqlstmt ||
78 ' , a.resource_id
79 , null
80 , a.resource_id
81 , null
82 , to_char(to_date(period_start_time,''hh24:mi''),''hh:miAM'') ';
83 END IF;
84 else /* for class summary */
85
86 g_sqlstmt := g_sqlstmt || ',null
87 , null
88 , a.resource_id,';
89 g_sqlstmt := g_sqlstmt || ' '' ' || g_className || ' '' ' || ', null ';
90 END IF;
91
92 g_sqlstmt := g_sqlstmt ||
93 ' , sum(a.in_calls_handled),
94
95 nvl(sum(a.in_calls_handld_gt_thn_x_time),0),
96
97 ''col13'',
98 bix_util_pkg.get_hrmiss_frmt(
99 sum(a.in_talk_time)/sum(decode(a.in_calls_handled,0,1,a.in_calls_handled)) ),
100
101 ''col15'',
102
103 bix_util_pkg.get_hrmiss_frmt(
104 sum(a.in_wrap_time)/sum(decode(a.in_calls_handled,0,1,a.in_calls_handled)) ),
105
106 ''col17'',
107 sum(a.out_calls_handled) ,
108 nvl(sum(a.out_cals_handld_gt_thn_x_time),0),
109
110 bix_util_pkg.get_hrmiss_frmt(
111 sum(a.out_talk_time)/sum(decode(a.out_calls_handled,0,1,a.out_calls_handled)) ),
112
113 ''col23'',
114 bix_util_pkg.get_hrmiss_frmt(
115 sum(a.out_wrap_time)/sum(decode(a.out_calls_handled,0,1,a.out_calls_handled)) ),
116
117 ''col25'',
118 to_char(
119 round(sum(a.in_talk_time+a.out_talk_time+a.in_wrap_time+a.out_wrap_time)*100/
120 sum(decode(a.available_time+a.in_talk_time+a.out_talk_time+a.in_wrap_time+
121 a.out_wrap_time,0,1,a.available_time+a.in_talk_time+a.out_talk_time+
122 a.in_wrap_time+a.out_wrap_time)), 2), ''990.99'') || ''%'' ,';
123
124 if (p_total_row_ind = 3) then g_sqlstmt := g_sqlstmt ||
125 ' ''col27'',
126 trunc(sum(a.in_calls_handled+a.out_calls_handled)),
127 bix_util_pkg.get_hrmiss_frmt(trunc(sum(a.in_talk_time+a.out_talk_time+a.in_wrap_time+a.out_wrap_time))) ,';
128 else g_sqlstmt := g_sqlstmt ||
129 ' null,
130 null,
131 null, ';
132 end if;
133
134 /* col32 */
135 if (p_total_row_ind = 1) then
136 g_sqlstmt := g_sqlstmt || 'concat( ' || ' '' ' ||bix_util_pkg.get_hrmiss_frmt(g_idx)|| ' '''
137 || ', rsc.source_name) , ';
138 elsif (p_total_row_ind =3) then g_sqlstmt := g_sqlstmt || 'concat( ' || ' '' ' ||bix_util_pkg.get_hrmiss_frmt(g_idx)||' ''' || ', ''9'' ), ';
139 else
140 if (g_time_range = 5) then
141 g_sqlstmt := g_sqlstmt || 'concat( ' || ' '' ' ||bix_util_pkg.get_hrmiss_frmt(g_idx)|| ' '' ' || ',to_char(to_date(period_start_date,''dd/mm/yyyy''),''dd/mm/yyyy'') ), ';
142 else
143 g_sqlstmt := g_sqlstmt || 'concat( ' || ' '' ' ||bix_util_pkg.get_hrmiss_frmt(g_idx)|| ' '' ' || ',to_char(to_date(period_start_time,''hh24:mi''),''hh24:mi'') ), ';
144 end if;
145
146 end if;
147 /* col34 */
148 if (p_total_row_ind = 1) then g_sqlstmt := g_sqlstmt || ' ''LIST'','; /* for re-order */
149 else g_sqlstmt := g_sqlstmt || ' ''DETAIL'', ';
150 end if;
151 g_sqlstmt := g_sqlstmt || '
152 null,
153 null,
154 null ';
155
156 /* Fetch the data from the appropiate MV depending on the time range */
157 IF (g_time_range = 1) THEN
158 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_call_sum a ';
159 ELSIF (g_time_range = 2) THEN
160 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_sum1_mv a ';
161 ELSIF (g_time_range = 3) THEN
162 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_sum2_mv a ';
163 ELSIF (g_time_range = 4) THEN
164 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_sum4_mv a ';
165 ELSE
166 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_sum_day_mv a ';
167 END IF;
168
169 IF (p_total_row_ind = 1 /*or p_total_row_ind = 3*/) THEN
170 g_sqlstmt := g_sqlstmt ||
171 ', jtf_rs_group_members mem
172 , jtf_rs_resource_extns rsc ';
173 END IF;
174
175 g_sqlstmt := g_sqlstmt || 'WHERE period_start_date_time between :start_date and :end_date ';
176
180 AND mem.resource_id = rsc.resource_id
177 IF (p_total_row_ind = 1 /*or p_total_row_ind = 3*/) THEN
178 g_sqlstmt := g_sqlstmt ||
179 'AND mem.group_id = :group_id
181 AND rsc.resource_id = a.resource_id ';
182 ELSE
183 g_sqlstmt := g_sqlstmt || 'AND a.resource_id = :resource_id ';
184 END IF;
185
186 g_sqlstmt := g_sqlstmt || ' and (a.classification_id = :v_class_id or :v_class_id = -999
187 or( :v_class_id is null and a.classification_id is null) )
188 and (:v_site_id = a.server_group_id
189 or :v_site_id = -999) ';
190 /*
191 g_sqlstmt := g_sqlstmt || ' and (a.classification_id = :v_class_id
192 or :v_class_id = -999)
193 and (:v_site_id = a.server_group_id
194 or :v_site_id = -999) ';
195 */
196 IF (p_total_row_ind = 1 /*or p_total_row_ind = 3*/) THEN
197 g_sqlstmt := g_sqlstmt || 'GROUP BY mem.group_id, a.resource_id, rsc.source_name)';
198 ELSIF (p_total_row_ind = 2) then
199 IF (g_time_range = 5) THEN
200 g_sqlstmt := g_sqlstmt || 'GROUP BY period_start_date, a.resource_id)';
201 ELSE
202 g_sqlstmt := g_sqlstmt || 'GROUP BY period_start_time, a.resource_id)';
203 END IF;
204 else g_sqlstmt := g_sqlstmt || 'GROUP BY a.resource_id)';
205 END IF;
206
207 EXCEPTION
208 WHEN OTHERS THEN
209 RAISE;
210 END form_rs_sqlstmt;
211
212 PROCEDURE form_group_sqlstmt(p_total_row_ind IN BOOLEAN)
213 IS
214 BEGIN
215 g_sqlstmt := NULL;
216
217 /* Form the SQL string to insert the rows into the temp table */
218 g_sqlstmt := '
219 INSERT /*+ PARALLEL(tr,2) */ INTO BIX_DM_REPORT tr (
220 session_id,
221 report_code
222 , col2
223 , col3
224 , col4
225 , col5
226 , col6
227 , col8
228 , col10
229 , col12
230 , col13
231 , col14
232 , col15
233 , col16
234 , col17
235 , col18
236 , col20
237 , col22
238 , col23
239 , col24
240 , col25
241 , col26
242 , col27
243 , col28
244 , col30
245 , col32
246 , col34
247 , col36
248 , col38
249 , col40 ) ';
250
251 g_sqlstmt := g_sqlstmt || ' (SELECT ' || to_char(l_session_id) || ',' || ' ''BIX_AGENT_CLASS_REPORT'' ';
252
253 IF (p_total_row_ind = TRUE) THEN
254 g_sqlstmt := g_sqlstmt ||
255 ' , grp.group_name
256 , null
257 , null
258 , grp.group_id
259 ,null /*''class'' */
260 , null ';
261 ELSE
262 g_sqlstmt := g_sqlstmt ||
263 ' , null
264 , ''p'' || a.group_id
265 , grp.group_name
266 , dnm.group_id
267 , null /*''class'' */
268 , null ';
269 END IF;
270
271 g_sqlstmt := g_sqlstmt ||
272 '
273 , sum(a.in_calls_handled),
274
275 nvl(sum(a.in_calls_handld_gt_thn_x_time),0),
276
277 ''col13'',
278 bix_util_pkg.get_hrmiss_frmt(
279 sum(a.in_talk_time)/sum(decode(a.in_calls_handled,0,1,a.in_calls_handled)) ),
280
281 ''col15'',
282
283 bix_util_pkg.get_hrmiss_frmt(
284 sum(a.in_wrap_time)/sum(decode(a.in_calls_handled,0,1,a.in_calls_handled)) ),
285
286 ''col17'',
287 sum(a.out_calls_handled) ,
288 nvl(sum(a.out_cals_handld_gt_thn_x_time),0),
289
290 bix_util_pkg.get_hrmiss_frmt(
291 sum(a.out_talk_time)/sum(decode(a.out_calls_handled,0,1,a.out_calls_handled)) ),
292
293 ''col23'',
294 bix_util_pkg.get_hrmiss_frmt(
295 sum(a.out_wrap_time)/sum(decode(a.out_calls_handled,0,1,a.out_calls_handled)) ),
296
297 ''col25'',
298 to_char(
299 trunc(sum(a.in_talk_time+a.out_talk_time+a.in_wrap_time+a.out_wrap_time)*100/
300 sum(decode(a.available_time+a.in_talk_time+a.out_talk_time+a.in_wrap_time+
301 a.out_wrap_time,0,1,a.available_time+a.in_talk_time+a.out_talk_time+a.in_wrap_time+a.out_wrap_time)),2),''990.99'') || ''%'' ,
302
303
304
305 ''col27'',
306 decode(:agentNum,0,0,trunc(sum(a.in_calls_handled+a.out_calls_handled)/:agentNum) ) ,
307
308 bix_util_pkg.get_hrmiss_frmt(decode(:agentNum,0,0,
309 trunc(sum(a.in_talk_time+a.out_talk_time+a.in_wrap_time+a.out_wrap_time)/:agentNum ))) , ';
310
311 g_sqlstmt := g_sqlstmt || 'CONCAT( ' || ' '' '||bix_util_pkg.get_hrmiss_frmt(g_idx)|| ' '' ' || ',grp.group_name),';
312 g_sqlstmt := g_sqlstmt ||
313 ' null,
314 null,
315 null,
316 null ';
317
318
319
320 /* Fetch the data from the appropiate MV depending on the time range */
321 IF (g_time_range = 1) THEN
322 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_call_sum a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
323 ELSIF (g_time_range = 2) THEN
324 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum1_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
325 ELSIF (g_time_range = 3) THEN
326 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum2_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
327 ELSIF (g_time_range = 4) THEN
328 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum4_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
329 ELSE
333 IF (p_total_row_ind = FALSE) THEN
330 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_group_sum_day_mv a, jtf_rs_groups_vl grp/*,JTF_RS_GROUP_MEMBERS J*/ ';
331 END IF;
332
334 g_sqlstmt := g_sqlstmt ||
335 ', jtf_rs_groups_denorm dnm ';
336 END IF;
337
338 g_sqlstmt := g_sqlstmt || 'WHERE period_start_date_time between :start_date and :end_date ';
339
340 IF (p_total_row_ind = FALSE) THEN
341 g_sqlstmt := g_sqlstmt ||
342 'AND dnm.parent_group_id = :group_id
343 AND dnm.group_id = grp.group_id
344 AND dnm.immediate_parent_flag = ''Y''
345 AND a.group_id = grp.group_id ';
346 ELSE
347 g_sqlstmt := g_sqlstmt || 'AND a.group_id = :group_id ';
348 END IF;
349
350 g_sqlstmt := g_sqlstmt || ' and a.group_id = grp.group_id
351
352 and (a.classification_id = :v_class_id
353 or :v_class_id = -999)
354 and (:v_site_id = a.server_group_id
355 or :v_site_id = -999) ';
356
357 IF (p_total_row_ind = TRUE) THEN
358 g_sqlstmt := g_sqlstmt || 'GROUP BY grp.group_id, grp.group_name)';
359 ELSE
360 g_sqlstmt := g_sqlstmt || 'GROUP BY dnm.group_id, a.group_id, grp.group_name)';
361 END IF;
362
363 EXCEPTION
364 WHEN OTHERS THEN
365 RAISE;
366 END form_group_sqlstmt;
367
368 /*********************************************************************************/
369 PROCEDURE insert_rs_detail_temp_table(p_agent_id IN NUMBER)
370 IS
371
372 l_unknown varchar2(100);
373 id number;
374
375 Cursor getClassList is
376 select nvl(classification,l_unknown) className,
377 classification_id classId
378 from cct_classifications
379 where g_class = -999
380 or classification_id = g_class;
381
382
383 BEGIN
384 select bix_util_pkg.get_null_lookup into l_unknown
385 from dual;
386 /*
387 SELECT meaning
388 into l_unknown
389 FROM fnd_lookups
390 WHERE lookup_type = 'BIX_DM_NULL_DESC'
391 AND lookup_code = 'NULL';
392 */
393 for rec in getClassList
394 loop
395
396 g_classname :=rec.className;
397
398 /* Form and execute the SQL statement to insert into the temp table */
399 /* all the detail rows coreesponding to the agent , user has chosen */
400 g_idx := g_idx+1;
401 form_rs_sqlstmt(3);
402
403 EXECUTE IMMEDIATE g_sqlstmt USING g_from_date, g_to_date, p_agent_id,
404 rec.classId, rec.classId,rec.classId,
405 g_site, g_site;
406 g_idx := g_idx+1;
407 form_rs_sqlstmt(2);
408 EXECUTE IMMEDIATE g_sqlstmt USING g_from_date, g_to_date, p_agent_id,
409 rec.classId, rec.classId,rec.classId,
410 g_site, g_site;
411 end loop;
412
413 /* for un-classified calls */
414 id :=null;
415 g_classname := l_unknown;
416 g_idx := g_idx+1;
417 form_rs_sqlstmt(3);
418
419 EXECUTE IMMEDIATE g_sqlstmt USING g_from_date, g_to_date, p_agent_id,
420 id, id,id,
421 g_site, g_site;
422 g_idx := g_idx+1;
423 form_rs_sqlstmt(2);
424 EXECUTE IMMEDIATE g_sqlstmt USING g_from_date, g_to_date, p_agent_id,
425 id, id,id,
426 g_site, g_site;
427 EXCEPTION
428 WHEN OTHERS THEN
429 RAISE;
430
431 END insert_rs_detail_temp_table;
432
433 /*********************************************************************************/
434 PROCEDURE insert_rs_total_temp_table(p_group_id IN NUMBER)
435 IS
436 BEGIN
437
438 /* Form and execute the SQL statement to insert into the temp table */
439 /* all the rows coreesponding to the agent user has chosen */
440 g_idx := g_idx+1;
441 form_rs_sqlstmt(1);
442 EXECUTE IMMEDIATE g_sqlstmt USING g_from_date, g_to_date, p_group_id,
443 g_class ,g_class, g_class,
444 g_site, g_site;
445
446 EXCEPTION
447 WHEN OTHERS THEN
448 RAISE;
449 END insert_rs_total_temp_table;
450 /*********************************************************************************/
451
452 PROCEDURE insert_group_temp_table(p_group_id IN NUMBER)
453 IS
454 idx number;
455 agentNum number;
456
457 BEGIN
458
459 /* Form and execute the SQL statement to insert into the temp table */
460 /* the total row corresponding to the agent group user has chosen */
461
462 select count( distinct(b.resource_id) ) into agentNum
463 from bix_dm_agent_call_sum b, JTF_RS_GROUP_MEMBERS j
464 where group_id = p_group_id
465 and b.resource_id = j.resource_id;
466
467 if agentNum is null then agentNum :=0;
468 end if;
469
470 g_idx := g_idx+1;
471 form_group_sqlstmt(TRUE);
472 EXECUTE IMMEDIATE g_sqlstmt USING agentNum, agentNum,agentNum, agentNum,
473 g_from_date, g_to_date, p_group_id,
474 g_class ,g_class,
475 g_site, g_site;
476
477 /* Form and execute the SQL statement to insert into the temp table all */
478 /* the child agent groups corresponding to the agent group user has chosen */
479 idx := g_idx+1;
480 g_idx :=10000; /* make it teh very last */
481 form_group_sqlstmt(FALSE);
482 EXECUTE IMMEDIATE g_sqlstmt USING agentNum, agentNum,agentNum, agentNum,
483 g_from_date, g_to_date, p_group_id,
484 g_class ,g_class,
488 EXCEPTION
485 g_site, g_site;
486 g_idx :=idx;
487
489 WHEN OTHERS THEN
490 RAISE;
491 END insert_group_temp_table;
492
493
494
495 PROCEDURE insert_group_temp_table_child(p_group_id IN NUMBER)
496 IS
497 BEGIN
498
499 /* Form and execute the SQL statement to insert into the temp table all */
500 /* the child agent groups corresponding to the agent group user has chosen */
501 g_idx := g_idx+1;
502 form_group_sqlstmt(FALSE);
503 EXECUTE IMMEDIATE g_sqlstmt USING g_from_date, g_to_date, p_group_id,
504 g_class ,g_class,
505 g_site, g_site;
506 EXCEPTION
507 WHEN OTHERS THEN
508 RAISE;
509 END insert_group_temp_table_child;
510
511
512
513 PROCEDURE get_param_values(p_context IN VARCHAR2)
514 IS
515 v_temp_date DATE;
516 l_date_format_mask VARCHAR2(50);
517
518 BEGIN
519
520 /* Fetch the nls date format*/
521 l_date_format_mask := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
522
523 /* Parse all the parameter values from the variable p_context */
524
525 g_parent := bix_util_pkg.get_parameter_value(p_context, 'pContext');
526 g_agent_group := bix_util_pkg.get_parameter_value(p_context, 'P_GROUP_ID');
527 g_period_ind := TO_NUMBER(bix_util_pkg.get_parameter_value(p_context, 'P_TIME'));
528 g_from_date := TO_DATE(bix_util_pkg.get_parameter_value(p_context, 'P_START_PERIOD'), l_date_format_mask);
529 g_from_time := bix_util_pkg.get_parameter_value(p_context, 'P_START_HR');
530 g_to_date := TO_DATE(bix_util_pkg.get_parameter_value(p_context, 'P_END_PERIOD'),l_date_format_mask);
531 g_to_time := bix_util_pkg.get_parameter_value(p_context, 'P_END_HR');
532 g_class := TO_NUMBER(bix_util_pkg.get_parameter_value(p_context, 'P_CLASSIFICATION'));
533 g_site := TO_NUMBER(bix_util_pkg.get_parameter_value(p_context, 'P_SITE_ID'));
534
535 /*
536 g_class := -999;
537 g_site :=-999;
538
539
540 g_parent :=null;
541 g_agent_group :='p-999';
542 g_period_ind :=null;
543
544 g_from_date :=null;
545 g_from_time :=null;
546 g_to_date :=null;
547 g_to_time := null;
548 */
549
550
551 /* Calculate the reporting period depending on the user input */
552 IF (g_period_ind IS NULL) THEN
553
554 SELECT MAX(period_start_date)
555 INTO v_temp_date
556 FROM bix_dm_agent_call_sum;
557
558 g_from_date := to_date(to_char(v_temp_date, 'dd/mm/yyyy') || ' 00:00:00','dd/mm/yyyy hh24:mi:ss');
559 g_to_date := to_date(to_char(v_temp_date, 'dd/mm/yyyy') || ' 23:59:59','dd/mm/yyyy hh24:mi:ss');
560
561 ELSIF (g_period_ind = 7) THEN
562
563 g_from_date := to_date(to_char(g_sysdate, 'dd/mm/yyyy') || ' 00:00:00','dd/mm/yyyy hh24:mi:ss');
564 g_to_date := to_date(to_char(g_sysdate, 'dd/mm/yyyy') || ' 23:59:59','dd/mm/yyyy hh24:mi:ss');
565
566 ELSIF (g_period_ind = 8) THEN
567
568 g_from_date := to_date(to_char(g_sysdate-1, 'dd/mm/yyyy') || ' 00:00:00','dd/mm/yyyy hh24:mi:ss');
569 g_to_date := to_date(to_char(g_sysdate-1, 'dd/mm/yyyy') || ' 23:59:59','dd/mm/yyyy hh24:mi:ss');
570
571 ELSIF (g_period_ind = 9) THEN
572
573 g_from_date := to_date(to_char(g_from_date,'dd/mm/yyyy ') || g_from_time, 'dd/mm/yyyy hh24');
574 g_to_date := to_date(to_char(g_to_date,'dd/mm/yyyy ') || g_to_time, 'dd/mm/yyyy hh24');
575
576 ELSE
577
578 bix_util_pkg.get_time_range(g_period_ind, g_from_date, g_to_date);
579
580 END IF;
581
582 EXCEPTION
583 WHEN OTHERS THEN
584 RAISE;
585 END get_param_values;
586
587 PROCEDURE populate(p_context IN VARCHAR2)
588 IS
589
590 v_drill_down_ind VARCHAR2(1);
591 v_agent_id NUMBER;
592 v_group_id NUMBER;
593 l_prefix VARCHAR2(100);
594
595 Cursor getPrefix is
596 select col32 prefix
597 from BIX_DM_REPORT
598 where report_code = 'BIX_AGENT_CLASS_REPORT'
599 and col34 = 'LIST'
600 and session_id = l_session_id
601 and col5= to_char(v_agent_id);
602
603 BEGIN
604
605 /* Get the ICX Session Id */
606 l_session_id:= bix_util_pkg.get_icx_session_id;
607
608 delete from BIX_DM_REPORT
609 where session_id = l_session_id
610 and report_code = 'BIX_AGENT_CLASS_REPORT';
611
612 /* Fetch the sysdate */
613 SELECT sysdate
614 INTO g_sysdate
615 FROM dual;
616
617
618 g_idx :=0;
619 /* Get the User preferred time range (1/2 or 1 or 2 or 4 hour or 1 day) */
620 g_time_range := NULL;
621 SELECT fnd_profile.value('BIX_DM_RPT_TIME_RANGE')
622 INTO g_time_range
623 FROM dual;
624
625 /* If there is no user prefered time range , set it to 1/2 hour */
626 IF g_time_range IS NULL THEN
627 g_time_range := 1;
628 END IF;
629
630 /* Get all the parameter values by parsing the string p_context */
631 get_param_values(p_context);
632
633
634 /* If pContext is region_code , then the user has navigated to the report */
635 /* from the report listing page ; so nothing should be displayed */
636 IF (g_parent = 'BIX_AGENT_CLASS_REPORT') THEN
637 g_parent := NULL;
638 END IF;
639
640
641
642 /* Get the parent agent group id */
643 /* g_parent = "not null" : User has navigated to the report either */
644 /* from the bin or by clicking the agent/agent group in the report */
645 /* g_parent = null : User has displayed the report by clicking the */
649 INTO v_group_id
646 /* "go" button in the paramter section */
647 IF (g_parent IS NOT NULL) THEN
648 SELECT TO_NUMBER(substr(g_parent, 2, decode(instr(g_parent,'c'), 0, length(g_parent), instr(g_parent,'c')-2)))
650 FROM dual;
651 ELSIF (g_agent_group IS NOT NULL) THEN
652 v_group_id := g_agent_group;
653 ELSE
654 RETURN;
655 END IF;
656
657 /* If the user has selected "All" for agent group paramter , display the default group of the user */
658 IF (v_group_id = -999) THEN
659 SELECT fnd_profile.value('BIX_DM_DEFAULT_GROUP')
660 INTO v_group_id
661 FROM dual;
662 END IF;
663
664
665
666 /* l_group_id = null : user has navigated to the report from the report listing page */
667 /* or the user has selected "all" as agent group paramter and (s)he is not assigned to any default group */
668 IF (v_group_id IS NULL) THEN
669 RETURN;
670 END IF;
671
672 /* Process all the groups whose parent is the agent group v_group_id */
673 insert_group_temp_table(v_group_id);
674
675 /* Process all the agents belonging to the group */
676 insert_rs_total_temp_table(v_group_id);
677
678 /* Check if the user has clicked on an agent */
679 IF (instr(g_parent,'c') <> 0) THEN
680
681 v_agent_id := TO_NUMBER(substr(g_parent, instr(g_parent, 'c')+1, length(g_parent) - (instr(g_parent,'c')+1)));
682 v_drill_down_ind := substr(g_parent, length(g_parent), 1);
683
684 /* v_drill_down_ind = 'n' : we have to display the detail rows of the agent */
685 IF (v_drill_down_ind = 'n') THEN
686 UPDATE BIX_DM_REPORT
687 SET col3 = 'p' || v_group_id || 'c' || to_char(v_agent_id) || 'y'
688 WHERE col3 = 'p' || v_group_id || 'c' || to_char(v_agent_id) || 'n';
689
690 /* Fetch the detail rows of data for the agent from the */
691 /* summary table and insert them into bix temp table */
692 insert_rs_detail_temp_table(v_agent_id);
693
694 for rec in getPrefix loop
695 l_prefix :=rec.prefix;
696 end loop;
697
698 /* update index order that the detail get listed under the agent */
699 update BIX_DM_REPORT
700 set col32 = concat(l_prefix, col32)
701 where report_code = 'BIX_AGENT_CLASS_REPORT'
702 and session_id = l_session_id
703 and col34 = 'DETAIL';
704 END IF;
705 END IF;
706
707
708 /*
709 update BIX_DM_REPORT
710 set col32 = concat(l_prefix, col32)
711 where report_code = 'BIX_AGENT_CLASS_REPORT'
712 and session_id = l_session_id
713 and col34 = 'DETAIL'; */
714
715 EXCEPTION
716 WHEN OTHERS THEN
717
718 RAISE;
719 END populate;
720
721 END BIX_AGENT_CLASS_P;