[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AI_AGTDTL_RPT_PKG
Source
1 PACKAGE BODY BIX_PMV_AI_AGTDTL_RPT_PKG AS
2 /*$Header: bixiagtr.plb 120.1 2006/04/18 17:26:11 anasubra noship $ */
3
4 FUNCTION GET_ZERONULL_CLAUSE RETURN VARCHAR2
5 IS
6 l_having_clause VARCHAR2(1000);
7
8 BEGIN
9
10 l_having_clause:=' where
11 ( abs(nvl(BIX_PMV_AI_LOGIN_CP,0)) + abs(nvl(BIX_PMV_AI_AVAILRATE_CP,0))
12 + abs(nvl(BIX_PMV_AI_UTILRATE_CP,0))+abs(nvl(BIX_PMV_AI_INCALLHAND_CP,0))
13 + abs(nvl(BIX_PMV_AI_INCALLHAND_PAH,0)) + abs(nvl(BIX_PMV_AI_DIALED_CP,0))
14 + abs(nvl(BIX_PMV_AI_DIALED_PAH,0)) +abs(nvl(BIX_PMV_AI_AVGTALK_CP,0))
15 +abs(nvl(BIX_PMV_AI_AVGWRAP_CP,0)) +abs(nvl(BIX_PMV_AI_WEBCALL_CP,0))
16 +abs(nvl(BIX_PMV_AI_WEBCALL_PAH,0)) +abs(nvl(BIX_PMV_AI_SRCR_CP,0))
17 +abs(nvl(BIX_PMV_AI_LECR_CP,0)) +abs(nvl(BIX_PMV_AI_OPCR_CP,0))
18 +abs(nvl(BIX_CALC_ITEM3,0)) +abs(nvl(BIX_CALC_ITEM7,0))
19 +abs(nvl(BIX_CALC_ITEM11,0)) +abs(nvl(BIX_CALC_ITEM15,0))
20 +abs(nvl(BIX_CALC_ITEM19,0))
21 ) <> 0';
22
23 return l_having_clause;
24
25 END GET_ZERONULL_CLAUSE;
26
27 FUNCTION GET_MEASURES RETURN VARCHAR2
28 IS
29 l_measure_txt VARCHAR2(32000);
30 l_goal NUMBER;
31 BEGIN
32
33 --insert into bix_debug
34 --values ('Entered get_measures ');
35
36 IF (FND_PROFILE.DEFINED('BIX_CALL_SLGOAL_PERCENT')) THEN
37 BEGIN
38 l_goal := TO_NUMBER(FND_PROFILE.VALUE('BIX_CALL_SLGOAL_PERCENT'));
39 EXCEPTION
40 WHEN OTHERS THEN
41 l_goal := 0;
42 END;
43 ELSE
44 l_goal := 0;
45 END IF;
46
47 l_measure_txt :=
48 ' SELECT v.resource_name BIX_PMV_AI_AGENT, '
49 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
50 p_curr=>'to_number(NVL(c_lg,0))'
51 ,p_measurecol=>'BIX_PMV_AI_LOGIN_CP'
52 ,p_totalcol=>'BIX_PMV_TOTAL1'
53 ,p_convertunit=>'/3600'
54 )
55 ||','
56 ||bix_pmv_dbi_utl_pkg.get_pertotal_measure(
57 p_num =>'to_number(NVL(c_lg,0))'
58 ,p_measurecol=>'BIX_PMV_AI_LOGIN_PT'
59 )
60 ||','
61 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
62 p_percentage=>'Y'
63 ,p_num=>'NVL(c_lg, 0)-NVL(c_id,0)'
64 ,p_denom=>'NVL(c_lg,0)'
65 ,p_measurecol=>'BIX_PMV_AI_AVAILRATE_CP'
66 ,p_totalcol=>'BIX_PMV_TOTAL2'
67 )
68 ||','
69 ||bix_pmv_dbi_utl_pkg.get_devavg_measure(
70 p_percentage=>'Y'
71 ,p_num=>'NVL(c_lg, 0)-NVL(c_id,0)'
72 ,p_denom=>'NVL(c_lg,0)'
73 ,p_col=>'BIX_PMV_AI_AVAILRATE_DEV'
74 )
75 ||','
76 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
77 p_percentage=>'Y'
78 ,p_num=>'NVL(c_lg, 0)-NVL(c_id,0)-NVL(c_av,0)'
79 ,p_denom=>'NVL(c_lg,0)'
80 ,p_measurecol=>'BIX_PMV_AI_UTILRATE_CP'
81 ,p_totalcol=>'BIX_PMV_TOTAL3'
82 )
83 ||','
84 ||bix_pmv_dbi_utl_pkg.get_devavg_measure(
85 p_percentage=>'Y'
86 ,p_num=>'NVL(c_lg, 0)-NVL(c_id,0)-NVL(c_av,0)'
87 ,p_denom=>'NVL(c_lg,0)'
88 ,p_col=>'BIX_PMV_AI_UTILRATE_DEV'
89 )
90 ||','
91 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
92 p_curr=>'NVL(c_ic,0)'
93 ,p_measurecol=>'BIX_PMV_AI_INCALLHAND_CP'
94 ,p_totalcol=>'BIX_PMV_TOTAL4'
95 )
96 ||','
97 ||bix_pmv_dbi_utl_pkg.get_pertotal_measure(
98 p_num =>'to_number(NVL(c_ic,0))'
99 ,p_measurecol=>'BIX_PMV_AI_INCALLHAND_PT'
100 )
101 ||','
102 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
103 p_percentage=>'N'
104 ,p_num=>'NVL(c_ic,0)'
105 ,p_denom=>'NVL(c_lg,0)'
106 ,p_measurecol=>'BIX_PMV_AI_INCALLHAND_PAH'
107 ,p_totalcol=>'BIX_PMV_TOTAL5'
108 ,p_convunitfordenom=>'/3600'
109 )
110 ||','
111 ||bix_pmv_dbi_utl_pkg.get_devavg_measure(
112 p_percentage=>'N'
113 ,p_num=>'NVL(c_ic,0)'
114 ,p_denom=>'NVL(c_lg,0)'
115 ,p_col=>'BIX_PMV_AI_INCALLHAND_PAH_DEV'
116 ,p_convunitfordenom=>'/3600'
117 )
118 ||','
119 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
120 p_curr=>'NVL(c_mc,0)'
121 ,p_measurecol=>'BIX_PMV_AI_DIALED_CP'
122 ,p_totalcol=>'BIX_PMV_TOTAL8'
123 )
124 ||','
125 ||bix_pmv_dbi_utl_pkg.get_pertotal_measure(
126 p_num =>'to_number(NVL(c_mc,0))'
127 ,p_measurecol=>'BIX_PMV_AI_DIALED_PT'
128 )
129 ||','
130 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
131 p_percentage=>'N'
132 ,p_num=>'NVL(c_mc,0)'
133 ,p_denom=>'NVL(c_lg,0)'
134 ,p_measurecol=>'BIX_PMV_AI_DIALED_PAH'
135 ,p_totalcol=>'BIX_PMV_TOTAL9'
136 ,p_convunitfordenom=>'/3600'
137 )
138 ||','
139 ||bix_pmv_dbi_utl_pkg.get_devavg_measure(
140 p_percentage=>'N'
141 ,p_num=>'NVL(c_mc,0)'
142 ,p_denom=>'NVL(c_lg,0)'
143 ,p_col=>'BIX_PMV_AI_DIALED_PAH_DEV'
144 ,p_convunitfordenom=>'/3600'
145 )
146 ||','
147 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
148 p_percentage=>'N'
149 ,p_num=>'NVL(c_t, 0)'
150 ,p_denom=>'NVL(c_tc,0)+NVL(c_cch,0)+
151 NVL(c_ctc,0)+NVL(c_cct,0)'
152 ,p_measurecol=>'BIX_PMV_AI_AVGTALK_CP'
153 ,p_totalcol=>'BIX_PMV_TOTAL10'
154 )
155 ||','
156 ||bix_pmv_dbi_utl_pkg.get_devavg_measure(
157 p_percentage=>'N'
158 ,p_num=>'NVL(c_t, 0)'
159 ,p_denom=>'NVL(c_tc,0)+NVL(c_cch,0)+
160 NVL(c_ctc,0)+NVL(c_cct,0)'
161 ,p_col=>'BIX_PMV_AI_AVGTALK_DEV'
162 )
163 ||','
164 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
165 p_percentage=>'N'
166 ,p_num=>'NVL(c_w, 0)'
167 ,p_denom=>'NVL(c_tc,0)+NVL(c_cch,0)+
168 NVL(c_ctc,0)+NVL(c_cct,0)'
169 ,p_measurecol=>'BIX_PMV_AI_AVGWRAP_CP'
170 ,p_totalcol=>'BIX_PMV_TOTAL11'
171 )
172 ||','
173 ||bix_pmv_dbi_utl_pkg.get_devavg_measure(
174 p_percentage=>'N'
175 ,p_num=>'NVL(c_w, 0)'
176 ,p_denom=>'NVL(c_tc,0)+NVL(c_cch,0)+
177 NVL(c_ctc,0)+NVL(c_cct,0)'
178 ,p_col=>'BIX_PMV_AI_AVGWRAP_DEV'
179 )
180 ||','
181 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
182 p_curr=>'NVL(c_wc,0)'
183 ,p_measurecol=>'BIX_PMV_AI_WEBCALL_CP'
184 ,p_totalcol=>'BIX_PMV_TOTAL6'
185 )
186 ||','
187 ||bix_pmv_dbi_utl_pkg.get_pertotal_measure(
188 p_num =>'to_number(NVL(c_wc,0))'
189 ,p_measurecol=>'BIX_PMV_AI_WEBCALL_PT'
190 )
191 ||','
192 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
193 p_percentage=>'N'
194 ,p_num=>'NVL(c_wc,0)'
195 ,p_denom=>'NVL(c_lg,0)'
196 ,p_measurecol=>'BIX_PMV_AI_WEBCALL_PAH'
197 ,p_totalcol=>'BIX_PMV_TOTAL7'
198 ,p_convunitfordenom=>'/3600'
199 )
200 ||','
201 ||bix_pmv_dbi_utl_pkg.get_devavg_measure(
202 p_percentage=>'N'
203 ,p_num=>'NVL(c_wc,0)'
204 ,p_denom=>'NVL(c_lg,0)'
205 ,p_col=>'BIX_PMV_AI_WEBCALL_PAH_DEV'
206 ,p_convunitfordenom=>'/3600'
207 )
208 ||','
209 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
210 p_curr=>'NVL(c_s,0)'
211 ,p_measurecol=>'BIX_PMV_AI_SRCR_CP'
212 ,p_totalcol=>'BIX_PMV_TOTAL13'
213 )
214 ||','
215 ||bix_pmv_dbi_utl_pkg.get_pertotal_measure(
216 p_num =>'to_number(NVL(c_s,0))'
217 ,p_measurecol=>'BIX_PMV_AI_SRCR_PT'
218 )
219 ||','
220 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
221 p_curr=>'NVL(c_l,0)'
222 ,p_measurecol=>'BIX_PMV_AI_LECR_CP'
223 ,p_totalcol=>'BIX_PMV_TOTAL14'
224 )
225 ||','
226 ||bix_pmv_dbi_utl_pkg.get_pertotal_measure(
227 p_num =>'to_number(NVL(c_l,0))'
228 ,p_measurecol=>'BIX_PMV_AI_LECR_PT'
229 )
230 ||','
231 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
232 p_curr=>'NVL(c_o,0)'
233 ,p_measurecol=>'BIX_PMV_AI_OPCR_CP'
234 ,p_totalcol=>'BIX_PMV_TOTAL15'
235 )
236 ||','
237 ||bix_pmv_dbi_utl_pkg.get_pertotal_measure(
238 p_num =>'to_number(NVL(c_o,0))'
239 ,p_measurecol=>'BIX_PMV_AI_OPCR_PT'
240 )
241 ||','
242
243 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
244 p_curr=>'NVL(c_mc,0)'
245 ,p_measurecol=>'BIX_CALC_ITEM1'
246 ,p_totalcol=>'BIX_CALC_ITEM2'
247 )
248 ||','
249 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
250 p_curr=>'NVL(p_mc,0)'
251 ,p_measurecol=>'BIX_CALC_ITEM3'
252 ,p_totalcol=>'BIX_CALC_ITEM4'
253 )
254 ||','
255 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
256 p_curr=>'NVL(c_wc,0)'
257 ,p_measurecol=>'BIX_CALC_ITEM5'
258 ,p_totalcol=>'BIX_CALC_ITEM6'
259 )
260 ||','
261 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
262 p_curr=>'NVL(p_wc,0)'
263 ,p_measurecol=>'BIX_CALC_ITEM7'
264 ,p_totalcol=>'BIX_CALC_ITEM8'
265 )
266 ||','
267 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
268 p_percentage=>'Y'
269 ,p_num=>'NVL(c_lg, 0)-NVL(c_id,0)'
270 ,p_denom=>'NVL(c_lg,0)'
271 ,p_measurecol=>'BIX_CALC_ITEM9'
272 ,p_totalcol=>'BIX_CALC_ITEM10'
273 )
274 ||','
275 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
276 p_percentage=>'Y'
277 ,p_num=>'NVL(p_lg, 0)-NVL(p_id,0)'
278 ,p_denom=>'NVL(p_lg,0)'
279 ,p_measurecol=>'BIX_CALC_ITEM11'
280 ,p_totalcol=>'BIX_CALC_ITEM12'
281 )
282 ||','
283 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
284 p_percentage=>'Y'
285 ,p_num=>'NVL(c_lg, 0)-NVL(c_id,0)-NVL(c_av,0)'
286 ,p_denom=>'NVL(c_lg,0)'
287 ,p_measurecol=>'BIX_CALC_ITEM13'
288 ,p_totalcol=>'BIX_CALC_ITEM14'
289 )
290 ||','
291 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
292 p_percentage=>'Y'
293 ,p_num=>'NVL(p_lg, 0)-NVL(p_id,0)-NVL(p_av,0)'
294 ,p_denom=>'NVL(p_lg,0)'
295 ,p_measurecol=>'BIX_CALC_ITEM15'
296 ,p_totalcol=>'BIX_CALC_ITEM16'
297 )
298 ||','
299 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
300 p_percentage=>'N'
301 ,p_num=>'NVL(c_ic,0)'
302 ,p_denom=>'NVL(c_lg,0)'
303 ,p_measurecol=>'BIX_CALC_ITEM17'
304 ,p_totalcol=>'BIX_CALC_ITEM18'
305 ,p_convunitfordenom=>'/3600'
306 )
307 ||','
308 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
309 p_percentage=>'N'
310 ,p_num=>'NVL(p_ic,0)'
311 ,p_denom=>'NVL(p_lg,0)'
312 ,p_measurecol=>'BIX_CALC_ITEM19'
313 ,p_totalcol=>'BIX_CALC_ITEM20'
314 )
315 ;
316 /**
317 ',0 BIX_CALC_ITEM1,
318 0 BIX_CALC_ITEM2,
319 0 BIX_CALC_ITEM3,
320 0 BIX_CALC_ITEM4,
321 0 BIX_CALC_ITEM5,
322 0 BIX_CALC_ITEM6,
323 0 BIX_CALC_ITEM7,
324 0 BIX_CALC_ITEM8,
325 0 BIX_CALC_ITEM9,
326 0 BIX_CALC_ITEM10,
327 0 BIX_CALC_ITEM11,
328 0 BIX_CALC_ITEM12,
329 0 BIX_CALC_ITEM13,
330 0 BIX_CALC_ITEM14,
331 0 BIX_CALC_ITEM15,
332 0 BIX_CALC_ITEM16,
333 0 BIX_CALC_ITEM17,
334 0 BIX_CALC_ITEM18,
335 0 BIX_CALC_ITEM19,
336 0 BIX_CALC_ITEM20 ';
337 **/
338
339 RETURN l_measure_txt;
340 EXCEPTION
341 WHEN OTHERS THEN
342 RAISE;
343 END GET_MEASURES;
344
345 PROCEDURE GET_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
346 p_sql_text OUT NOCOPY VARCHAR2,
347 p_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
348 )
349 AS
350 l_sqltext VARCHAR2(32000) ;
351 l_sqltext_cont VARCHAR2(32000) ;
352 l_sqltext_sess VARCHAR2(32000) ;
353
354 l_mv VARCHAR2 (240);
355 l_mv_sess VARCHAR2(240);
356 l_comp_type VARCHAR2(500);
357 l_xtd VARCHAR2(500);
358
359 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
360 l_col_tbl_cont poa_dbi_util_pkg.poa_dbi_col_tbl;
361 l_col_tbl_sess poa_dbi_util_pkg.poa_dbi_col_tbl;
362
363 l_where_clause VARCHAR2(1000) ;
364 l_where_clause_sess VARCHAR2(1000);
365
366 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
367 l_join_tbl_sess poa_dbi_util_pkg.poa_dbi_join_tbl;
368
369 l_filter_where VARCHAR2 (2000);
370 l_filter_where_sess VARCHAR2 (2000);
371
372 l_mv_set CONSTANT varchar2(3) := 'ITM';
373 l_mv_set_sess CONSTANT varchar2(3) := 'SES';
374
375 l_view_by_select VARCHAR2 (500);
376 l_view_by_select_sess VARCHAR2 (500);
377
378 l_func_area CONSTANT varchar2(5) := 'IAGTR';
379 l_version varchar2(3) := NULL;
380 l_timetype CONSTANT varchar2(3) := 'XTD';
381
382 l_view_by VARCHAR2 (120);
383 x integer;
384 BEGIN
385 /* Initialize the variables */
386 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
387
388 --insert into bix_Debug values (1);
389 /* Trial for Util Package..*/
390
391 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
392 l_join_tbl_sess := poa_dbi_util_pkg.poa_dbi_join_tbl ();
393
394 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
395 l_col_tbl_cont := poa_dbi_util_pkg.poa_dbi_col_tbl ();
396 l_col_tbl_sess := poa_dbi_util_pkg.poa_dbi_col_tbl ();
397
398 bix_pmv_dbi_utl_pkg.process_parameters
399 ( p_param => p_page_parameter_tbl
400 , p_trend => 'N'
401 , p_func_area => l_func_area
402 , p_version => l_version
403 , p_mv_set => l_mv_set
404 , p_where_clause => l_where_clause
405 , p_mv => l_mv
406 , p_join_tbl => l_join_tbl
407 , p_comp_type => l_comp_type
408 , p_xtd => l_xtd
409 , p_view_by_select => l_view_by_select
410 , p_view_by => l_view_by
411 );
412
413 -- Populate col table with columns for continued measures
414 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl_cont
415 , p_col_name => 'NULL'
416 , p_grand_total => 'N'
417 , p_alias_name => 't'
418 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
419 , p_to_date_type => l_timetype
420 );
421 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
422 , p_col_name => 'NULL'
423 , p_grand_total => 'N'
424 , p_alias_name => 'w'
425 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
426 , p_to_date_type => l_timetype
427 );
428 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
429 , p_col_name => 'NULL'
430 , p_grand_total => 'N'
431 , p_alias_name => 'ic'
432 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
433 , p_to_date_type => l_timetype
434 );
435 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
436 , p_col_name => 'NULL'
437 , p_grand_total => 'N'
438 , p_alias_name => 'wc'
439 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
440 , p_to_date_type => l_timetype
441 );
442 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
443 , p_col_name => 'NULL'
444 , p_grand_total => 'N'
445 , p_alias_name => 'mc'
446 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
447 , p_to_date_type => l_timetype
448 );
449 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
450 , p_col_name => 'NULL'
451 , p_grand_total => 'N'
452 , p_alias_name => 'tc'
453 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
454 , p_to_date_type => l_timetype
455 );
456 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
457 , p_col_name => 'agent_cont_calls_hand_na'
458 , p_grand_total => 'N'
459 , p_alias_name => 'cch'
460 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
461 , p_to_date_type => l_timetype
462 );
463 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
464 , p_col_name => 'NULL'
465 , p_grand_total => 'N'
466 , p_alias_name => 'ctc'
467 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
468 , p_to_date_type => l_timetype
469 );
470 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
471 , p_col_name => 'agent_cont_calls_tc_na'
472 , p_grand_total => 'N'
473 , p_alias_name => 'cct'
474 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
475 , p_to_date_type => l_timetype
476 );
477 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
478 , p_col_name => 'NULL'
479 , p_grand_total => 'N'
480 , p_alias_name => 'l'
481 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
482 , p_to_date_type => l_timetype
483 );
484 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
485 , p_col_name => 'NULL'
486 , p_grand_total => 'N'
487 , p_alias_name => 'o'
488 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
489 , p_to_date_type => l_timetype
490 );
491 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
492 , p_col_name => 'NULL'
493 , p_grand_total => 'N'
494 , p_alias_name => 's'
495 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
496 , p_to_date_type => l_timetype
497 );
498 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
499 , p_col_name => 'NULL'
500 , p_grand_total => 'N'
501 , p_alias_name => 'lg'
502 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
503 , p_to_date_type => l_timetype
504 );
505 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
506 , p_col_name => 'NULL'
507 , p_grand_total => 'N'
508 , p_alias_name => 'id'
509 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
510 , p_to_date_type => l_timetype
511 );
512 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
513 , p_col_name => 'NULL'
514 , p_grand_total => 'N'
515 , p_alias_name => 'av'
516 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
517 , p_to_date_type => l_timetype
518 );
519
520 l_sqltext_cont :=
521 BIX_PMV_DBI_utl_pkg.status_sql_daylevel (
522 p_fact_name => l_mv
523 , p_row_type_where_clause => l_where_clause --this shud come from util package
524 , p_col_name => l_col_tbl_cont
525 , p_join_tables => l_join_tbl
526 , p_time_type => 'ESD'
527 , p_union => 'ALL');
528 --insert into bix_Debug values ('Ended status_sql_daylevel for continued');
529 --
530 --Session SQL
531 --
532 bix_pmv_dbi_utl_pkg.process_parameters
533 ( p_param => p_page_parameter_tbl
534 , p_trend => 'N'
535 , p_func_area => l_func_area
536 , p_version => l_version
537 , p_mv_set => l_mv_set_sess
538 , p_where_clause => l_where_clause_sess
539 , p_mv => l_mv_sess
540 , p_join_tbl => l_join_tbl_sess
541 , p_comp_type => l_comp_type
542 , p_xtd => l_xtd
543 , p_view_by_select => l_view_by_select_sess
544 , p_view_by => l_view_by
545 );
546
547 --insert into bix_Debug values ('Ended process parameters for session');
548
549 -- Populate col_table_session with session measures
550
551 --insert into bix_Debug values ('Starting add columns for session');
552
553 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl_sess
554 , p_col_name => 'login_time'
555 , p_grand_total => 'N'
556 , p_alias_name => 'lg'
557 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
558 , p_to_date_type => l_timetype
559 );
560 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl_sess
561 , p_col_name => 'idle_time'
562 , p_grand_total => 'N'
563 , p_alias_name => 'id'
564 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
565 , p_to_date_type => l_timetype
566 );
567 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl_sess
568 , p_col_name => 'available_time'
569 , p_grand_total => 'N'
570 , p_alias_name => 'av'
571 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
572 , p_to_date_type => l_timetype
573 );
574
575 --insert into bix_Debug values ('Setting nulls in l_sqltext_sess ');
576
577 l_sqltext_sess :='(Select
578 sess.agent_id resource_id,
579 null c_t,null p_t,
580 null c_w,null p_w,
581 null c_ic,null p_ic,
582 null c_wc,null p_wc,
583 null c_mc,null p_mc,
584 null c_tc,null p_tc,
585 null c_cch,null p_cch,
586 null c_ctc,null p_ctc,
587 null c_cct,null p_cct,
588 null c_l,null p_l,
589 null c_o,null p_o,
590 null c_s,null p_s,
591 c_lg,p_lg,c_id,p_id,c_av,p_av
592 from ';
593
594 --insert into bix_Debug values ('Calling status_sql for sess ');
595 --insert into bix_Debug values ('l_mv_sess:'|| l_mv_sess || 'l_where_clause_sess:'|| l_where_clause_sess||
596 --'l_filter_where_sess:'|| l_filter_where_sess);
597
598
599 l_sqltext_sess := l_sqltext_sess ||
600 poa_dbi_template_pkg.status_sql (
601 p_fact_name => l_mv_sess
602 , p_where_clause => l_where_clause_sess
603 , p_filter_where => l_filter_where_sess
604 , p_join_tables => l_join_tbl_sess
605 , p_use_windowing => 'N'
606 , p_col_name => l_col_tbl_sess
607 , p_use_grpid => 'N'
608 , p_paren_count => 1
609 , p_generate_viewby => 'N');
610
611 --insert into bix_Debug values ('Calling add_column for regular columns');
612
613 -- Populate col table with regular columns
614
615 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl
616 , p_col_name => 'agent_talk_time_nac'
617 , p_grand_total => 'N'
618 , p_alias_name => 't'
619 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
620 , p_to_date_type => l_timetype
621 );
622 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
623 , p_col_name => 'agent_wrap_time_nac'
624 , p_grand_total => 'N'
625 , p_alias_name => 'w'
626 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
627 , p_to_date_type => l_timetype
628 );
629 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
630 , p_col_name => '(CASE WHEN media_item_type in (''TELE_INB'',''TELE_DIRECT'') THEN agent_calls_handled_total END)'
631 , p_grand_total => 'N'
632 , p_alias_name => 'ic'
633 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
634 , p_to_date_type => l_timetype
635 );
636 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
637 , p_col_name => '(CASE WHEN media_item_type in (''TELE_WEB_CALLBACK'') THEN agent_calls_handled_total END)'
638 , p_grand_total => 'N'
639 , p_alias_name => 'wc'
640 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
641 , p_to_date_type => l_timetype
642 );
643 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
644 , p_col_name => '(CASE WHEN media_item_type in (''TELE_MANUAL'') THEN agent_calls_handled_total END)'
645 , p_grand_total => 'N'
646 , p_alias_name => 'mc'
647 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
648 , p_to_date_type => l_timetype
649 );
650 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
651 , p_col_name => 'agent_calls_handled_total'
652 , p_grand_total => 'N'
653 , p_alias_name => 'tc'
654 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
655 , p_to_date_type => l_timetype
656 );
657 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
658 , p_col_name => 'NULL'
659 , p_grand_total => 'N'
660 , p_alias_name => 'cch'
661 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
662 , p_to_date_type => l_timetype
663 );
664 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
665 , p_col_name => 'agent_calls_tran_conf_to_nac'
666 , p_grand_total => 'N'
667 , p_alias_name => 'ctc'
668 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
669 , p_to_date_type => l_timetype
670 );
671 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
672 , p_col_name => 'NULL'
673 , p_grand_total => 'N'
674 , p_alias_name => 'cct'
675 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
676 , p_to_date_type => l_timetype
677 );
678 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
679 , p_col_name => 'agent_leads_created'
680 , p_grand_total => 'N'
681 , p_alias_name => 'l'
682 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
683 , p_to_date_type => l_timetype
684 );
685 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
686 , p_col_name => 'agent_opportunities_created'
687 , p_grand_total => 'N'
688 , p_alias_name => 'o'
689 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
690 , p_to_date_type => l_timetype
691 );
692 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
693 , p_col_name => 'agent_sr_created'
694 , p_grand_total => 'N'
695 , p_alias_name => 's'
696 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
697 , p_to_date_type => l_timetype
698 );
699 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
700 , p_col_name => 'NULL'
701 , p_grand_total => 'N'
702 , p_alias_name => 'lg'
703 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
704 , p_to_date_type => l_timetype
705 );
706 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
707 , p_col_name => 'NULL'
708 , p_grand_total => 'N'
709 , p_alias_name => 'id'
710 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
711 , p_to_date_type => l_timetype
712 );
713 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
714 , p_col_name => 'NULL'
715 , p_grand_total => 'N'
716 , p_alias_name => 'av'
717 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
718 , p_to_date_type => l_timetype
719 );
720
721
722 --insert into bix_Debug values ('Calling status_sql for regular columns');
723
724 l_sqltext :=poa_dbi_template_pkg.status_sql (
725 p_fact_name => l_mv
726 , p_where_clause => l_where_clause
727 , p_filter_where => l_filter_where
728 , p_join_tables => l_join_tbl
729 , p_use_windowing => 'N'
730 , p_col_name => l_col_tbl
731 , p_use_grpid => 'N'
732 , p_paren_count => 3
733 , p_generate_viewby => 'Y');
734
735 --insert into bix_Debug values(l_sqltext);
736 --insert into bix_Debug values ('Forming l_sqltext');
737
738 l_sqltext :='select * from ('||get_measures|| ' from (('||l_sqltext_cont||l_sqltext_sess||') sess) UNION ALL '||l_sqltext||')'||GET_ZERONULL_CLAUSE;
739
740 --insert into bix_debug values('Returned from status_sql:' );
741
742 l_sqltext:=replace(replace(replace(replace(replace(l_sqltext,
743 ' ',' '),' ',' '),' ',' '),' ',' '),' ',' ');
744
745 --insert into bix_debug values ('Completed forming l_sqltext');
746
747 x:=length(l_sqltext);
748 --insert into bix_debug values (l_sqltext);
749 --insert into bix_debug values (x);
750
751
752 p_sql_text:=l_sqltext;
753 bix_pmv_dbi_utl_pkg.get_bind_vars (p_custom_output,p_func_area => l_func_area);
754
755 /* End of Trial for Util Package */
756
757 /*****************************
758 l_sqltext :=
759 'SELECT
760 res.resource_name
761 BIX_PMV_AI_AGENT
762 ,ROUND(SUM(NVL(fact.login,0))/3600, 1)
763 BIX_PMV_AI_LOGIN_CP
764 ,ROUND((SUM(SUM(NVL(fact.login,0))) OVER())/3600, 1)
765 BIX_PMV_TOTAL1
766 ,ROUND(SUM(nvl(fact.login,0))*100/
767 decode(SUM(SUM(nvl(fact.login,0))) OVER(), 0, NULL,
768 SUM(SUM(nvl(fact.login,0))) OVER()),1) BIX_PMV_AI_LOGIN_PT
769 ,ROUND((SUM(NVL(fact.login,0) - NVL(fact.idle, 0)) /
770 DECODE(SUM(NVL(fact.login,0)), 0, NULL, SUM(NVL(fact.login,0)))) * 100, 1)
771 BIX_PMV_AI_AVAILRATE_CP
772 ,ROUND(
773 (
774 SUM(SUM(NVL(fact.login,0) - NVL(fact.idle, 0))) OVER() /
775 DECODE(SUM(SUM(NVL(fact.login,0))) OVER(), 0, NULL,
776 SUM(SUM(NVL(fact.login,0))) OVER()
777 )
778 ) * 100
779 ,1)
780 BIX_PMV_TOTAL2
781 ,ROUND(((SUM(NVL(fact.login,0) - NVL(fact.idle, 0)) /
782 DECODE(SUM(NVL(fact.login,0)), 0, NULL, SUM(NVL(fact.login,0)))) * 100)
783 -
784 ((SUM(SUM(NVL(fact.login,0) - NVL(fact.idle, 0))) OVER() /
785 DECODE(SUM(SUM(NVL(fact.login,0))) OVER(), 0, NULL, SUM(SUM(NVL(fact.login,0))) OVER())) * 100) ,1) BIX_PMV_AI_AVAILRATE_DEV
786 ,ROUND(
787 (
788 SUM( NVL(fact.login,0) - NVL(fact.idle, 0) - NVL(fact.available,0) ) /
789 DECODE(SUM(fact.login), 0, NULL, SUM(fact.login))
790 ) * 100
791 , 1)
792 BIX_PMV_AI_UTILRATE_CP
793 ,ROUND(
794 (
795 SUM(SUM( NVL(fact.login,0) - NVL(fact.idle, 0) - NVL(fact.available,0) )) over() /
796 DECODE(SUM(SUM(fact.login)) over(), 0, NULL, SUM(SUM(fact.login)) over())
797 ) * 100
798 , 1) BIX_PMV_TOTAL3
799 ,ROUND(
800 (
801 SUM( NVL(fact.login,0) - NVL(fact.idle, 0) - NVL(fact.available,0) ) /
802 DECODE(SUM(fact.login), 0, NULL, SUM(fact.login))
803 ) * 100
804 -
805 (
806 SUM(SUM( NVL(fact.login,0) - NVL(fact.idle, 0) - NVL(fact.available,0) )) over() /
807 DECODE(SUM(SUM(fact.login)) over(), 0, NULL, SUM(SUM(fact.login)) over())
808 ) * 100
809 ,1) BIX_PMV_AI_UTILRATE_DEV
810 ,SUM(NVL(fact.inb_calls,0))
811 BIX_PMV_AI_INCALLHAND_CP
812 ,SUM(SUM(NVL(fact.inb_calls,0))) OVER()
813 BIX_PMV_TOTAL4
814 ,ROUND(SUM(NVL(fact.inb_calls,0))*100/
815 decode(SUM(SUM(NVL(fact.inb_calls,0))) OVER(),0,NULL,
816 SUM(SUM(NVL(fact.inb_calls,0))) OVER()),1) BIX_PMV_AI_INCALLHAND_PT
817 ,ROUND(SUM(NVL(fact.inb_calls,0)) /
818 DECODE(SUM(NVL(fact.login,0))/3600, 0, NULL, SUM(NVL(fact.login,0))/3600), 1)
819 BIX_PMV_AI_INCALLHAND_PAH
820 ,ROUND(SUM(SUM(NVL(fact.inb_calls,0))) OVER() /
821 DECODE(SUM(SUM(NVL(fact.login,0))) OVER()/3600, 0, NULL, SUM(SUM(NVL(fact.login,0))) OVER()/3600), 1)
822 BIX_PMV_TOTAL5
823 ,ROUND(SUM(NVL(fact.inb_calls,0)) /
824 DECODE(SUM(NVL(fact.login,0))/3600, 0, NULL, SUM(NVL(fact.login,0))/3600), 1) -
825 ROUND(SUM(SUM(NVL(fact.inb_calls,0))) OVER() /
826 DECODE(SUM(SUM(NVL(fact.login,0))) OVER()/3600, 0, NULL, SUM(SUM(NVL(fact.login,0))) OVER()/3600), 1)
827 BIX_PMV_AI_INCALLHAND_PAH_DEV
828 ,SUM(NVL(fact.web_calls,0))
829 BIX_PMV_AI_WEBCALL_CP
830 ,SUM(SUM(NVL(fact.web_calls,0))) OVER()
831 BIX_PMV_TOTAL6
832 ,ROUND(SUM(NVL(fact.web_calls,0))*100/
833 DECODE(SUM(SUM(NVL(fact.web_calls,0))) OVER(),0,NULL,
834 SUM(SUM(NVL(fact.web_calls,0))) OVER()),1) BIX_PMV_AI_WEBCALL_PT
835 ,ROUND(SUM(NVL(fact.web_calls,0)) /
836 DECODE(SUM(NVL(fact.login,0))/3600, 0, NULL, SUM(NVL(fact.login,0))/3600), 1)
837 BIX_PMV_AI_WEBCALL_PAH
838 ,ROUND(SUM(SUM(NVL(fact.web_calls,0))) OVER() /
839 DECODE(SUM(SUM(NVL(fact.login,0))) OVER()/3600, 0, NULL, SUM(SUM(NVL(fact.login,0))) OVER()/3600), 1)
840 BIX_PMV_TOTAL7
841 ,ROUND(SUM(NVL(fact.web_calls,0)) /
842 DECODE(SUM(NVL(fact.login,0))/3600, 0, NULL, SUM(NVL(fact.login,0))/3600), 1) -
843 ROUND(SUM(SUM(NVL(fact.web_calls,0))) OVER() /
844 DECODE(SUM(SUM(NVL(fact.login,0))) OVER()/3600, 0, NULL, SUM(SUM(NVL(fact.login,0))) OVER()/3600), 1)
845 BIX_PMV_AI_WEBCALL_PAH_DEV
846 ,SUM(NVL(fact.man_calls,0))
847 BIX_PMV_AI_DIALED_CP
848 ,SUM(SUM(NVL(fact.man_calls,0))) OVER()
849 BIX_PMV_TOTAL8
850 ,ROUND(SUM(NVL(fact.man_calls,0))*100/
851 DECODE(SUM(SUM(NVL(fact.man_calls,0))) OVER(),0,NULL,
852 SUM(SUM(NVL(fact.man_calls,0))) OVER()),1) BIX_PMV_AI_DIALED_PT
853 ,ROUND(SUM(NVL(fact.man_calls,0)) /
854 DECODE(SUM(NVL(fact.login,0))/3600, 0, NULL, SUM(NVL(fact.login,0))/3600), 1)
855 BIX_PMV_AI_DIALED_PAH
856 ,ROUND(SUM(SUM(NVL(fact.man_calls,0))) OVER() /
857 DECODE(SUM(SUM(NVL(fact.login,0))) OVER()/3600, 0, NULL, SUM(SUM(NVL(fact.login,0))) OVER()/3600), 1)
858 BIX_PMV_TOTAL9
859 ,ROUND(SUM(NVL(fact.man_calls,0)) /
860 DECODE(SUM(NVL(fact.login,0))/3600, 0, NULL, SUM(NVL(fact.login,0))/3600), 1) -
861 ROUND(SUM(SUM(NVL(fact.man_calls,0))) OVER() /
862 DECODE(SUM(SUM(NVL(fact.login,0))) OVER()/3600, 0, NULL, SUM(SUM(NVL(fact.login,0))) OVER()/3600), 1)
863 BIX_PMV_AI_DIALED_PAH_DEV
864 ,ROUND(SUM(NVL(fact.talk,0)) /
865 DECODE(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) + NVL(fact.calls_tran_conf,0)
866 + NVL(cont_calls_tc,0)), 0, NULL, SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) +
867 NVL(fact.calls_tran_conf,0) + NVL(cont_calls_tc,0))), 1)
868 BIX_PMV_AI_AVGTALK_CP
869 ,ROUND(SUM(SUM(NVL(fact.talk,0))) OVER() /
870 DECODE(SUM(SUM(NVL(tot_calls,0) +
871 NVL(fact.cont_calls_hand,0)
872 )) OVER(), 0, NULL,
873 SUM(SUM(NVL(tot_calls,0) +
874 NVL(fact.cont_calls_hand,0)
875 )) OVER()), 1)
876 BIX_PMV_TOTAL10
877 ,ROUND(SUM(NVL(fact.talk,0)) /
878 DECODE(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) + NVL(fact.calls_tran_conf,0)
879 + NVL(cont_calls_tc,0)), 0, NULL, SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) +
880 NVL(fact.calls_tran_conf,0) + NVL(cont_calls_tc,0))), 1) -
881 ROUND(SUM(SUM(NVL(fact.talk,0))) OVER() /
882 DECODE(SUM(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0)
883 )) OVER(), 0, NULL, SUM(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) )) OVER()), 1)
884 BIX_PMV_AI_AVGTALK_DEV
885 ,ROUND(SUM(NVL(fact.wrap,0)) /
886 DECODE(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) + NVL(fact.calls_tran_conf,0)
887 + NVL(cont_calls_tc,0)), 0, NULL, SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) +
888 NVL(fact.calls_tran_conf,0) + NVL(cont_calls_tc,0))), 1)
889 BIX_PMV_AI_AVGWRAP_CP
890 ,ROUND(SUM(SUM(NVL(fact.wrap,0))) OVER() /
891 DECODE(SUM(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0)
892 )) OVER(), 0, NULL, SUM(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) )) OVER()), 1)
893 BIX_PMV_TOTAL11
894 ,ROUND(SUM(NVL(fact.wrap,0)) /
895 DECODE(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) + NVL(fact.calls_tran_conf,0)
896 + NVL(cont_calls_tc,0)), 0, NULL, SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) +
897 NVL(fact.calls_tran_conf,0) + NVL(cont_calls_tc,0))), 1) -
898 ROUND(SUM(SUM(NVL(fact.wrap,0))) OVER() /
899 DECODE(SUM(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0)
900 )) OVER(), 0, NULL, SUM(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) )) OVER()), 1)
901 BIX_PMV_AI_AVGWRAP_DEV
902 ,SUM(NVL(fact.sr,0))
903 BIX_PMV_AI_SRCR_CP
904 ,SUM(SUM(NVL(fact.sr,0))) OVER()
905 BIX_PMV_TOTAL13
906 ,ROUND(SUM(NVL(fact.sr,0))*100/
907 DECODE(SUM(SUM(NVL(fact.sr,0))) OVER(),0,NULL,
908 SUM(SUM(NVL(fact.sr,0))) OVER()),1) BIX_PMV_AI_SRCR_PT
909 ,SUM(NVL(fact.leads,0))
910 BIX_PMV_AI_LECR_CP
911 ,SUM(SUM(NVL(fact.leads,0))) OVER()
912 BIX_PMV_TOTAL14
913 ,ROUND(SUM(NVL(fact.leads,0))*100/
914 DECODE(SUM(SUM(NVL(fact.leads,0))) OVER(),0,NULL,
915 SUM(SUM(NVL(fact.leads,0))) OVER()),1) BIX_PMV_AI_LECR_PT
916 ,SUM(NVL(fact.oppr,0))
917 BIX_PMV_AI_OPCR_CP
918 ,SUM(SUM(NVL(fact.oppr,0))) OVER()
919 BIX_PMV_TOTAL15
920 ,ROUND(SUM(NVL(fact.oppr,0))*100/
921 DECODE(SUM(SUM(NVL(fact.oppr,0))) OVER(),0,NULL,
922 SUM(SUM(NVL(fact.oppr,0))) OVER()),1) BIX_PMV_AI_OPCR_PT
923 FROM (
924 SELECT
925 fact.resource_id
926 agent_id
927 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_talk_time_nac))
928 talk
929 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_wrap_time_nac))
930 wrap
931 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(fact.media_item_type, ''TELE_INB'', agent_calls_handled_total,
932 ''TELE_DIRECT'', agent_calls_handled_total, 0)))
933 inb_calls
934 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(fact.media_item_type, ''TELE_WEB_CALLBACK'',
935 agent_calls_handled_total, 0)))
936 web_calls
937 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(fact.media_item_type, ''TELE_MANUAL'', agent_calls_handled_total, 0)))
938 man_calls
939 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_calls_handled_total))
940 tot_calls
941 ,NULL cont_calls_hand
942 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_calls_tran_conf_to_nac))
943 calls_tran_conf
944 ,NULL cont_calls_tc
945 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_leads_created))
946 leads
947 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_opportunities_created))
948 oppr
949 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_sr_created))
950 sr
951 ,NULL login
952 ,NULL idle
953 ,NULL available
954 FROM
955 bix_ai_call_details_mv fact,
956 fii_time_rpt_struct calendar
957 WHERE fact.row_type = ''CDR''
958 AND fact.time_id = calendar.time_id
959 AND fact.period_type_id = calendar.period_type_id
960 AND calendar.report_date = &BIS_CURRENT_ASOF_DATE
961 AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) = calendar.record_type_id ';
962
963 l_sqltext := l_sqltext || l_call_where_clause ;
964
965 IF l_agent_group IS NOT NULL THEN
966 l_sqltext := l_sqltext ||
967 ' AND EXISTS (
968 SELECT 1
969 FROM jtf_rs_group_members mem
970 WHERE fact.resource_id = mem.resource_id
971 AND mem.group_id IN (:l_agent_group)
972 AND nvl(mem.delete_flag, ''N'') <> ''Y'' )';
973 END IF;
974
975 l_sqltext := l_sqltext || '
976 GROUP BY fact.resource_id
977 UNION ALL
978 SELECT
979 fact.agent_id
980 agent_id
981 , NULL talk
982 , NULL wrap
983 , NULL inb_calls
984 , NULL web_calls
985 , NULL man_calls
986 , NULL tot_calls
987 , NULL cont_calls_hand
988 , NULL calls_tran_conf
989 , NULL cont_calls_tc
990 , NULL leads
991 , NULL oppr
992 , NULL sr
993 , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.login_time))
994 login
995 , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.idle_time))
996 idle
997 , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.available_time))
998 available
999 FROM
1000 bix_agent_session_f fact,
1001 fii_time_rpt_struct calendar
1002 WHERE fact.time_id = calendar.time_id
1003 AND fact.application_id = 696
1004 AND fact.period_type_id = calendar.period_type_id
1005 AND calendar.report_date = &BIS_CURRENT_ASOF_DATE
1006 AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) = calendar.record_type_id ';
1007
1008 l_sqltext := l_sqltext || l_session_where_clause ;
1009
1010 IF l_agent_group IS NOT NULL THEN
1011 l_sqltext := l_sqltext ||
1012 ' AND EXISTS (
1013 SELECT 1
1014 FROM jtf_rs_group_members mem
1015 WHERE fact.agent_id = mem.resource_id
1016 AND mem.group_id IN (:l_agent_group)
1017 AND nvl(mem.delete_flag, ''N'') <> ''Y'' )';
1018 END IF;
1019
1020 l_sqltext := l_sqltext || '
1021 GROUP BY fact.agent_id
1022 UNION ALL
1023 SELECT
1024 fact.resource_id
1025 agent_id
1026 , NULL talk
1027 , NULL wrap
1028 , NULL inb_calls
1029 , NULL web_calls
1030 , NULL man_calls
1031 , NULL tot_calls
1032 , SUM(fact.agent_cont_calls_hand_na)
1033 cont_calls_hand
1034 , NULL calls_tran_conf
1035 , SUM(fact.agent_cont_calls_tc_na)
1036 cont_calls_tc
1037 , NULL leads
1038 , NULL oppr
1039 , NULL sr
1040 , NULL login
1041 , NULL idle
1042 , NULL available
1043 FROM
1044 bix_ai_call_details_mv fact
1045 WHERE fact.row_type = ''CDR''
1046 AND fact.time_id = TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J''))
1047 AND fact.period_type_id = 1 ';
1048
1049 l_sqltext := l_sqltext || l_call_where_clause ;
1050
1051 IF l_agent_group IS NOT NULL THEN
1052 l_sqltext := l_sqltext ||
1053 ' AND EXISTS (
1054 SELECT 1
1055 FROM jtf_rs_group_members mem
1056 WHERE fact.resource_id = mem.resource_id
1057 AND mem.group_id IN (:l_agent_group)
1058 AND nvl(mem.delete_flag, ''N'') <> ''Y'' )';
1059 END IF;
1060
1061 l_sqltext := l_sqltext || '
1062 GROUP BY fact.resource_id
1063 ) fact, jtf_rs_resource_extns_vl res
1064 WHERE fact.agent_id = res.resource_id
1065 GROUP BY res.resource_name &ORDER_BY_CLAUSE ';
1066
1067 ********************************************/
1068
1069 EXCEPTION
1070 WHEN OTHERS THEN
1071 l_sqltext:=sqlerrm;
1072 --insert into bix_Debug values (l_sqltext);
1073 commit;
1074 END GET_SQL;
1075 END BIX_PMV_AI_AGTDTL_RPT_PKG;