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