1 PACKAGE BODY BIX_KPI_CLS_RPT_PKG AS
2 /*$Header: bixxkctb.pls 115.10 2001/09/27 10:42:42 pkm ship $*/
3
4 g_sqlstmt VARCHAR2(2000); -- variable to store SQL query
5 g_date_format_mask VARCHAR2(50); -- variable to store display date format
6
7 /* This procedure gets the conversion rates between 2 currencies
8 for the current system date.
9 */
10 PROCEDURE get_exchange_rate( p_from_currency IN VARCHAR2,
11 p_to_currency IN VARCHAR2,
12 p_denom_rate OUT NUMBER,
13 p_num_rate OUT NUMBER )
14 IS
15 l_conv_type VARCHAR2(30);
16 l_status NUMBER;
17 BEGIN
18 /* Get the BIX conversion type */
19 SELECT fnd_profile.value('BIX_DM_CURR_CONVERSION_TYPE')
20 INTO l_conv_type
21 FROM dual;
22
23 /* Get the exchange rate as per sysdate */
24 bix_util_pkg.get_conversion_rate(p_from_currency, p_to_currency, sysdate,
25 l_conv_type, p_denom_rate, p_num_rate, l_status);
26 EXCEPTION
27 WHEN OTHERS THEN
28 RAISE;
29 END get_exchange_rate;
30
31
32 /* This procedure forms the SQL statement for the given set of parameters.
33 The time range profile option indicates in what time range the data should
34 be displayed in the report. The codes for the time range parameter are as
35 follows :
36 Time range code Meaning
37 ----------------------------------
38 1 half hour
39 2 one hour
40 3 two hours
41 4 four hours
42 5 one day
43 -----------------------------------
44 */
45 PROCEDURE form_sqlstmt(p_total_row_ind IN BOOLEAN,
46 p_classification_id IN NUMBER,
47 p_site_id IN NUMBER )
48 IS
49 l_time_range NUMBER;
50 l_null_class_name VARCHAR2(50);
51 BEGIN
52 g_sqlstmt := NULL;
53
54 /* Fetch the time range (1 hour or 2 hour etc. ) preference of the user */
55 SELECT fnd_profile.value('BIX_DM_RPT_TIME_RANGE')
56 INTO l_time_range
57 FROM dual;
58
59 /* If the user has not specified any time range then
60 report on 1/2 hour basis
61 */
62 IF l_time_range IS NULL THEN
63 l_time_range := 1;
64 END IF;
65
66 /* Form the SQL string to insert the rows into the temp table.
67 For null classifications, 'z99' was chosen as the default classifcation
68 id so that it always appears as the last row in the report */
69 g_sqlstmt := 'INSERT /*+ PARALLEL(tr,2) */ INTO BIX_DM_REPORT tr
70 ( session_id, report_code, col1, col2, col3, col4, col6, col8,
71 col10, col12, col14, col16, col18, col20, col22, col24,
72 col26, col28, col30 )
73 (SELECT /*+ PARALLEL(a,2) */ :session_id, ''BIX_KPI_CLS_RPT'' ,
74 nvl(to_char(a.classification_id), ''z99'') || ''y'' ';
75
76 /* If total row then insert classification description */
77 /* if not then insert the time range */
78 IF (p_total_row_ind = FALSE)
79 THEN
80 g_sqlstmt := g_sqlstmt || ', null ';
81
82 /* If the time range is day report the data by date else by time */
83 IF (l_time_range = 5)
84 THEN
85 g_sqlstmt := g_sqlstmt ||
86 ', to_char(a.period_start_date,''' || g_date_format_mask || ''')
87 , to_char(a.period_start_date,''' || g_date_format_mask || ''')';
88 ELSE
89 g_sqlstmt := g_sqlstmt ||
90 ', a.period_start_time,
91 to_char(to_date(a.period_start_time,''hh24:mi''), ''hh:miAM'') ';
92 END IF;
93 ELSE
94 /* Get the name for null classifications from lookup */
95 l_null_class_name := bix_util_pkg.get_null_lookup;
96 g_sqlstmt := g_sqlstmt || ', nvl(b.classification,''' ||
97 l_null_class_name || '''), null, null ';
98 END IF;
99
100 g_sqlstmt := g_sqlstmt || ' , bix_util_pkg.get_hrmiss_frmt(
101 DECODE( SUM(a.in_calls_handled + a.out_calls_handled), 0, 0,
102 SUM(in_talk_time + out_talk_time)/
103 SUM(a.in_calls_handled + a.out_calls_handled))) ,
104 bix_util_pkg.get_hrmiss_frmt(
105 DECODE( SUM(a.in_calls_handled + a.out_calls_handled), 0, 0,
106 SUM(a.in_wrap_time + a.out_wrap_time) /
107 SUM(a.in_calls_handled + a.out_calls_handled)))
108 , SUM(a.in_calls_handld_gt_thn_x_time)
109 , SUM(a.service_requests_created)
110 , SUM(a.service_requests_opened)
111 , SUM(a.service_requests_closed)
112 , SUM(a.leads_created)
113 , SUM(a.leads_updated)
114 , to_char(SUM(DECODE(:user_currency, a.currency_code, a.leads_amount_txn,
115 ((a.leads_amount / :denom_rate) * :num_rate ))), :format_mask)
116 , SUM(a.opportunities_created)
117 , SUM(a.opportunities_updated)
118 , SUM(a.opportunities_won)
119 , to_char(SUM(DECODE(:user_currency, a.currency_code,
120 a.opportunities_won_amount_txn,
121 ((a.opportunities_won_amount / :denom_rate) * :num_rate ))), :format_mask) ';
122
123 /* Fetch the data from the appropiate MV depending on the time range */
124 IF (l_time_range = 1)
125 THEN
126 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_call_sum a ';
127 ELSIF (l_time_range = 2)
128 THEN
129 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_sum1_mv a ';
130 ELSIF (l_time_range = 3)
131 THEN
132 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_sum2_mv a ';
133 ELSIF (l_time_range = 4)
134 THEN
135 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_sum4_mv a ';
136 ELSE
137 g_sqlstmt := g_sqlstmt || 'FROM bix_dm_agent_sum_day_mv a ';
138 END IF;
139
140 /* For total rows, we need to display the classification name.
141 Hence we need to join to cct_classifications.
142 If the user chooses ALL in the classification parameter(=-999),
143 then we need to display the data for null classifications also.
144 Hence the outer join. */
145 IF p_total_row_ind = TRUE
146 THEN
147 g_sqlstmt := g_sqlstmt || ', cct_classifications b ';
148 IF p_classification_id = -999
149 THEN
150 g_sqlstmt := g_sqlstmt ||
151 'WHERE a.classification_id = b.classification_id (+) ';
152 g_sqlstmt := g_sqlstmt || ' AND :classification_id = -999 ';
153 ELSE
154 g_sqlstmt := g_sqlstmt ||
155 'WHERE a.classification_id = b.classification_id ';
156 g_sqlstmt := g_sqlstmt ||
157 ' AND a.classification_id = :classification_id';
158 END IF;
159 ELSE
160 IF p_classification_id = -999
161 THEN
162 g_sqlstmt := g_sqlstmt ||
163 'WHERE a.classification_id IS NULL ';
164 g_sqlstmt := g_sqlstmt || ' AND :classification_id = -999';
165 ELSE
166 g_sqlstmt := g_sqlstmt ||
167 ' WHERE a.classification_id = :classification_id';
168 END IF;
169 END IF;
170
171 g_sqlstmt := g_sqlstmt || ' AND a.period_start_date_time between
172 :start_date and :end_date ';
173
174 /* Add the filer condition for site if the user has chosen
175 a particular value for site in the parameter */
176 IF (p_site_id <> -999)
177 THEN
178 g_sqlstmt := g_sqlstmt || ' AND a.server_group_id = :site_id ';
179 ELSE
180 g_sqlstmt := g_sqlstmt || ' AND :site_id = -999 ';
181 END IF;
182
183 /* Concatenate the appropiate group by clause */
184 /* Order by will be taken care of in AK */
185 IF (p_total_row_ind = FALSE)
186 THEN
187 IF (l_time_range = 5)
188 THEN
189 g_sqlstmt := g_sqlstmt || 'GROUP BY a.classification_id,
190 a.period_start_date)';
191 ELSE
192 g_sqlstmt := g_sqlstmt || 'GROUP BY a.classification_id,
193 a.period_start_time)';
194 END IF;
195 ELSE
196 g_sqlstmt := g_sqlstmt || ' GROUP BY a.classification_id,
197 b.classification)';
198 END IF;
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 RAISE;
203 END form_sqlstmt;
204
205
206 /* This procedure inserts data into the temp table.
207 */
208 PROCEDURE insert_temp_table(p_total_row_ind IN BOOLEAN,
209 p_session_id IN NUMBER,
210 p_classification_id IN NUMBER,
211 p_site_id IN NUMBER,
212 p_start_date IN DATE,
213 p_end_date In DATE,
214 p_user_currency IN VARCHAR2,
215 p_denom_rate IN NUMBER,
216 p_num_rate In NUMBER )
217 IS
218 l_format_mask VARCHAR2(50);
219 logfile UTL_FILE.file_type;
220 BEGIN
221 /* Get the format mask for the user currency */
222 l_format_mask := fnd_currency.get_format_mask(p_user_currency, 30);
223
224 /* Form and execute the SQL statement to insert into the temp table */
225 /* all the rows coreesponding to the classifications user has chosen */
226 form_sqlstmt(p_total_row_ind, p_classification_id, p_site_id);
227
228 /* The following code is useful while debugging. Make sure that the
229 UTL_FILE_DIR parameter from v$parameter is set to the /sqlcom/log
230 directory. If not change the path in the fopen command below to
231 point to the right directory
232
233 logfile := UTL_FILE.fopen('/sqlcom/log', 'BIXKCLS.sql', 'w', 2000 );
234 UTL_FILE.put_line( logfile, 'After fopen ' );
235 UTL_FILE.put_line( logfile, 'Classification Id : ' ||
236 to_char(p_classification_id ) );
237 UTL_FILE.put_line( logfile, 'Site ID : ' || to_char(p_site_id ) );
238 UTL_FILE.put_line( logfile, 'Start date ' ||
239 to_char(p_start_date, 'DD/MM/YYYY HH24:MI:SS' ) );
240 UTL_FILE.put_line(logfile, 'End date ' ||
241 to_char(p_end_date, 'DD/MM/YYYY HH24:MI:SS' ) );
242
243 UTL_FILE.put_line(logfile, 'Length of sql statement is ' ||
244 length(g_sqlstmt) );
245 UTL_FILE.put_line( logfile, g_sqlstmt );
246 UTL_FILE.fclose(logfile);
247 */
248 EXECUTE IMMEDIATE g_sqlstmt USING p_session_id, p_user_currency,
249 p_denom_rate, p_num_rate, l_format_mask, p_user_currency,
250 p_denom_rate, p_num_rate, l_format_mask, p_classification_id,
251 p_start_date, p_end_date, p_site_id;
252
253 EXCEPTION
254 WHEN OTHERS THEN
255 RAISE;
256 END insert_temp_table;
257
258 /* This procedure gets all the parameter values specified by the user.
259 The codes for the period parameter are as follows :
260 Period code Meaning
261 ----------------------------------
262 1 This Week
263 2 Prior Week
264 3 Month to Date
265 4 Prior Month to Date
266 5 Year to Date
267 6 Prior Year to Date
268 7 Today
269 8 Yesterday
270 9 User specified period
271 -----------------------------------
272 */
273 PROCEDURE get_param_values(p_context IN VARCHAR2,
274 p_classification_parent OUT VARCHAR2,
275 p_classification_id OUT NUMBER,
276 p_site_id OUT NUMBER,
277 p_start_date OUT DATE,
278 p_end_date OUT DATE )
279 IS
280 v_temp_date DATE;
281 l_sysdate DATE;
282 l_udef_start_date DATE;
283 l_udef_end_date DATE;
284 l_udef_start_time VARCHAR2(10);
285 l_udef_end_time VARCHAR2(10);
286 l_period_ind NUMBER;
287 l_classification_id NUMBER;
288 l_site_id NUMBER;
289 l_classification_parent VARCHAR2(50);
290
291 BEGIN
292
293 /* Fetch the sysdate into variable l_sysdate */
294 SELECT sysdate
295 INTO l_sysdate
296 FROM dual;
297
298
299 /* Get the JTF profile date format from the Profile */
300 g_date_format_mask := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
301
302 /* Parse all the parameter values from the variable p_context */
303 l_classification_parent := bix_util_pkg.get_parameter_value(
304 p_context, 'pContext');
305 l_classification_id := TO_NUMBER(bix_util_pkg.get_parameter_value(
306 p_context, 'P_CLASSIFICATION_ID'));
307 l_site_id := TO_NUMBER(bix_util_pkg.get_parameter_value(
308 p_context, 'P_SITE_ID'));
309 l_period_ind := TO_NUMBER(bix_util_pkg.get_parameter_value(
310 p_context, 'P_TIME_RANGE'));
311 l_udef_start_date := TO_DATE(bix_util_pkg.get_parameter_value(
312 p_context, 'P_START_DATE'),g_date_format_mask);
313 l_udef_start_time := bix_util_pkg.get_parameter_value(
314 p_context, 'P_START_TIME');
315 l_udef_end_date := TO_DATE(bix_util_pkg.get_parameter_value(
316 p_context, 'P_END_DATE'), g_date_format_mask);
317
318 l_udef_end_time := bix_util_pkg.get_parameter_value(p_context, 'P_END_TIME');
319
320 /* Calculate the reporting period depending on the user input */
321 IF (l_period_ind IS NULL)
322 THEN
323 SELECT MAX(period_start_date)
324 INTO v_temp_date
325 FROM bix_dm_agent_call_sum;
326
327 p_start_date := to_date(to_char(v_temp_date, 'dd/mm/yyyy') ||
328 ' 00:00:00','dd/mm/yyyy hh24:mi:ss');
329 p_end_date := to_date(to_char(v_temp_date, 'dd/mm/yyyy') ||
330 ' 23:59:59','dd/mm/yyyy hh24:mi:ss');
331 ELSIF (l_period_ind = 7)
332 THEN
333 p_start_date := to_date(to_char(l_sysdate, 'dd/mm/yyyy') ||
334 ' 00:00:00','dd/mm/yyyy hh24:mi:ss');
335 p_end_date := to_date(to_char(l_sysdate, 'dd/mm/yyyy') ||
336 ' 23:59:59','dd/mm/yyyy hh24:mi:ss');
337 ELSIF (l_period_ind = 8)
338 THEN
339 p_start_date := to_date(to_char(l_sysdate-1, 'dd/mm/yyyy') ||
340 ' 00:00:00','dd/mm/yyyy hh24:mi:ss');
341 p_end_date := to_date(to_char(l_sysdate-1, 'dd/mm/yyyy') ||
342 ' 23:59:59','dd/mm/yyyy hh24:mi:ss');
343 ELSIF (l_period_ind = 9)
344 THEN
345 p_start_date := to_date(to_char(l_udef_start_date,'dd/mm/yyyy ') ||
346 l_udef_start_time, 'dd/mm/yyyy hh24');
347 p_end_date := to_date(to_char(l_udef_end_date,'dd/mm/yyyy ') ||
348 l_udef_end_time, 'dd/mm/yyyy hh24');
349 ELSE
350 bix_util_pkg.get_time_range(l_period_ind, p_start_date, p_end_date);
351 END IF;
352
353 p_classification_parent := l_classification_parent;
354 p_classification_id := l_classification_id;
355 p_site_id := l_site_id;
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 RAISE;
360 END get_param_values;
361
362
363 /* This is the main procedure for this package.
364 */
365 PROCEDURE populate(p_context IN VARCHAR2 DEFAULT NULL)
366 IS
367 l_global_currency VARCHAR2(15);
368 l_user_currency VARCHAR2(15);
369 l_denom_rate NUMBER := 1;
370 l_num_rate NUMBER := 1;
371 l_classification_parent VARCHAR2(50);
372 l_classification_id NUMBER;
373 l_site_id NUMBER;
374 l_session_id NUMBER;
375 l_start_date DATE;
376 l_end_date DATE;
377 l_total_row_ind BOOLEAN;
378 l_drill_down_ind VARCHAR2(1);
379
380 BEGIN
381
382 l_session_id := bix_util_pkg.get_icx_session_id;
383
384 /* Delete the table for the current icx session and report */
385 DELETE from bix_dm_report
386 WHERE report_code = 'BIX_KPI_CLS_RPT'
387 AND session_id = l_session_id;
388
389 /* Get the BIX global currency */
390 SELECT fnd_profile.value('BIX_DM_PREFERRED_CURRENCY')
391 INTO l_global_currency
392 FROM dual;
393
394 /* Get the user currency */
395 SELECT fnd_profile.value('JTF_PROFILE_DEFAULT_CURRENCY')
396 INTO l_user_currency
397 FROM dual;
398
399 IF l_user_currency <> l_global_currency
400 THEN
401 /* Get exchange rate from BIX global currency to user preferred currency */
402 get_exchange_rate(l_global_currency, l_user_currency,
403 l_denom_rate, l_num_rate );
404 END IF;
405
406 /* Get all the parameter values */
407 get_param_values(p_context, l_classification_parent, l_classification_id,
408 l_site_id, l_start_date, l_end_date );
409
410 /* If pContext is region_code and l_classification_id is null, then the
411 user has navigated to the report from the report listing page.
412 Don't show anything in report */
413 IF (l_classification_parent = 'BIX_KPI_CLS_RPT')
414 THEN
415 IF l_classification_id IS NULL
416 THEN
417 RETURN ;
418 END IF;
419 l_classification_parent := NULL;
420 END IF;
421
422 l_total_row_ind := TRUE;
423
424 /* Insert the total rows corresponding to the classification */
425 insert_temp_table(l_total_row_ind, l_session_id, l_classification_id,
426 l_site_id, l_start_date, l_end_date, l_user_currency,
427 l_denom_rate, l_num_rate );
428
429 /* If user has clicked on a hyperlink */
430 IF l_classification_parent IS NOT NULL
431 THEN
432 /* Get the drill down indicator */
433 l_drill_down_ind := substr(l_classification_parent,
434 length(l_classification_parent), 1);
435
436 /* If l_drill_down_ind = 'y' : we have to display the detail rows
437 of the classification */
438 IF (l_drill_down_ind = 'y')
439 THEN
440 /* If user has clicked on the null classification row, then set
441 classification_id = -999 as classification_id is a number column */
442 IF l_classification_parent = 'z99y'
443 THEN
444 l_classification_id := -999;
445 UPDATE bix_dm_report
446 SET col1 = 'z99n'
447 WHERE col1 = 'z99y'
448 AND report_code = 'BIX_KPI_CLS_RPT'
449 AND session_id = l_session_id;
450 ELSE
451 l_classification_id := to_number( substr(
452 l_classification_parent, 1, length(l_classification_parent)-1));
453 UPDATE bix_dm_report
454 SET col1 = to_char(l_classification_id) || 'n'
455 WHERE col1 = to_char(l_classification_id) || 'y'
456 AND report_code = 'BIX_KPI_CLS_RPT'
457 AND session_id = l_session_id;
458 END IF;
459
460 /* Fetch the detail rows of data for the classification from the */
461 /* summary table and insert them into bix temp table */
462 l_total_row_ind := FALSE;
463 insert_temp_table( l_total_row_ind, l_session_id,
464 l_classification_id, l_site_id, l_start_date, l_end_date,
465 l_user_currency,l_denom_rate, l_num_rate );
466 END IF;
467 END IF;
468
469 EXCEPTION
470 WHEN OTHERS THEN
471 RAISE;
472 END populate;
473
474 END BIX_KPI_CLS_RPT_PKG;
[Home] [Help]