[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AI_TELDTL_RPT_PKG
Source
1 PACKAGE BODY BIX_PMV_AI_TELDTL_RPT_PKG AS
2 /*$Header: bixitelr.plb 120.0 2005/05/25 17:22:49 appldev noship $ */
3 FUNCTION GET_ZERONULL_CLAUSE RETURN VARCHAR2
4 IS
5 l_having_clause VARCHAR2(1500);
6 BEGIN
7 l_having_clause:=') rset where
8 (
9 abs(nvl(BIX_PMV_AI_SL_CP,0)) +abs(nvl(BIX_PMV_AI_SL_CG,0))+abs(nvl(BIX_PMV_AI_SL_PP,0))
10 + abs(nvl(BIX_PMV_AI_SPANS_CP,0)) +abs(nvl(BIX_PMV_AI_SPANS_CG,0))+abs(nvl(BIX_PMV_AI_SPANS_PP,0))
11 + abs(nvl(BIX_PMV_AI_ABANRATE_CP,0)) +abs(nvl(BIX_PMV_AI_ABANRATE_CG,0))+abs(nvl(BIX_PMV_AI_ABANRATE_PP,0))
12 + abs(nvl(BIX_PMV_AI_TRANRATE_CP,0)) +abs(nvl(BIX_PMV_AI_TRANRATE_CG,0))
13 + abs(nvl(BIX_PMV_AI_INCALLHAND_CP,0)) + abs(nvl(BIX_PMV_AI_INCALLHAND_CG,0))+ abs(nvl(BIX_PMV_AI_INCALLHAND_PP,0))
14 + abs(nvl(BIX_PMV_AI_AVGTALK_CP,0)) +abs(nvl(BIX_PMV_AI_AVGTALK_CG,0)) +abs(nvl(BIX_PMV_AI_AVGTALK_PP,0))
15 + abs(nvl(BIX_PMV_AI_AVGWRAP_CP,0)) +abs(nvl(BIX_PMV_AI_AVGWRAP_CG,0))
16 + abs(nvl(BIX_PMV_AI_SRCR_CP,0))+abs(nvl(BIX_PMV_AI_SRCR_CG,0))
17 + abs(nvl(BIX_PMV_AI_LECR_CP,0)) + abs(nvl(BIX_PMV_AI_LECR_CG,0))
18 + abs(nvl(BIX_PMV_AI_OPCR_CP,0)) +abs(nvl(BIX_PMV_AI_OPCR_CG,0))
19 + abs(nvl(BIX_PMV_AI_WEBCALL_CG,0)) +abs(nvl(BIX_PMV_AI_WEBCALL_CP,0))
20 ) <> 0';
21 return l_having_clause;
22 EXCEPTION
23 WHEN OTHERS THEN
24 RAISE;
25 END GET_ZERONULL_CLAUSE;
26
27
28 FUNCTION GET_MEASURES(l_view_by_select VARCHAR2) RETURN VARCHAR2
29 IS
30 l_measure_txt VARCHAR2(32000);
31 l_goal NUMBER;
32 BEGIN
33 IF (FND_PROFILE.DEFINED('BIX_CALL_SLGOAL_PERCENT')) THEN
34 BEGIN
35 l_goal := TO_NUMBER(FND_PROFILE.VALUE('BIX_CALL_SLGOAL_PERCENT'));
36 EXCEPTION
37 WHEN OTHERS THEN
38 l_goal := 0;
39 END;
40 ELSE
41 l_goal := 0;
42 END IF;
43
44 l_measure_txt:=
45 l_view_by_select
46 ||','||l_goal||' BIX_PMV_AI_SLGOAL,'
47 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
48 p_curr=>'to_number(NVL(c_g,0))'
49 ,p_prev=>'NVL(p_g,0)'
50 ,p_measurecol=>'BIX_PMV_AI_INCALLHAND_CP'
51 ,p_totalcol=>'BIX_PMV_TOTAL9'
52 ,p_changecol=>'BIX_PMV_AI_INCALLHAND_CG'
53 ,p_changetotalcol=>'BIX_PMV_TOTAL10'
54 )
55 /* Abandoned Calls */
56 ||',' || bix_pmv_dbi_utl_pkg.get_simple_measure(
57 p_curr=>'NVL(c_e,0)'
58 ,p_prev=>'NVL(p_e,0)'
59 ,p_measurecol=>'BIX_ATTRIBUTE_7'
60 ,p_totalcol=>'BIX_PMV_TOTAL21'
61 ,p_changecol=>'BIX_ATTRIBUTE_9'
62 ,p_changetotalcol=>'BIX_PMV_TOTAL22'
63 )
64 ||','
65 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
66 p_curr=>'NVL(c_l,0)'
67 ,p_prev=>'NVL(p_l,0)'
68 ,p_measurecol=>'BIX_PMV_AI_SRCR_CP'
69 ,p_totalcol=>'BIX_PMV_TOTAL17'
70 ,p_changecol=>'BIX_PMV_AI_SRCR_CG'
71 ,p_changetotalcol=>'BIX_PMV_TOTAL18'
72 )
73 ||','
74 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
75 p_curr=>'NVL(c_m,0)'
76 ,p_prev=>'NVL(p_m,0)'
77 ,p_measurecol=>'BIX_PMV_AI_LECR_CP'
78 ,p_totalcol=>'BIX_PMV_TOTAL19'
79 ,p_changecol=>'BIX_PMV_AI_LECR_CG'
80 ,p_changetotalcol=>'BIX_PMV_TOTAL20'
81 )
82 ||','
83 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
84 p_curr=>'NVL(c_n,0)'
85 ,p_prev=>'NVL(p_n,0)'
86 ,p_measurecol=>'BIX_PMV_AI_OPCR_CP'
87 ,p_totalcol=>'BIX_PMV_TOTAL23'
88 ,p_changecol=>'BIX_PMV_AI_OPCR_CG'
89 ,p_changetotalcol=>'BIX_PMV_TOTAL24'
90 )
91 ||','
92 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
93 p_curr=>'NVL(c_h,0)'
94 ,p_prev=>'NVL(p_h,0)'
95 ,p_measurecol=>'BIX_PMV_AI_WEBCALL_CP'
96 ,p_totalcol=>'BIX_PMV_TOTAL11'
97 ,p_changecol=>'BIX_PMV_AI_WEBCALL_CG'
98 ,p_changetotalcol=>'BIX_PMV_TOTAL12'
99 )
100 ||','
101 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
102 p_percentage=>'Y'
103 ,p_num=>'NVL(c_a, 0)'
104 ,p_denom=>'NVL(c_d,0)'
105 ,p_pnum=>'NVL(p_a, 0)'
106 ,p_pdenom=>'NVL(p_d,0)'
107 ,p_measurecol=>'BIX_PMV_AI_SL_CP'
108 ,p_totalcol=>'BIX_PMV_TOTAL1'
109 ,p_changecol=>'BIX_PMV_AI_SL_CG'
110 ,p_changetotalcol=>'BIX_PMV_TOTAL2'
111 )
112 ||','
113 /* For KPI -Inbound Service Level -Current Value - got from BIX_PMV_AI_SL_CP*/
114 /* For KPI -Inbound Service Level -Prior Value*/
115 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
116 p_percentage=>'Y'
117 ,p_num=>'NVL(p_a, 0)'
118 ,p_denom=>'NVL(p_d,0)'
119 ,p_measurecol=>'BIX_CALC_ITEM3'
120 ,p_totalcol=>'BIX_CALC_ITEM4'
121 )
122 ||','
123 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
124 p_percentage=>'N'
125 ,p_num=>'NVL(c_c, 0)'
126 ,p_denom=>'NVL(c_g,0)'
127 ,p_pnum=>'NVL(p_c, 0)'
128 ,p_pdenom=>'NVL(p_g,0)'
129 ,p_measurecol=>'BIX_PMV_AI_SPANS_CP'
130 ,p_totalcol=>'BIX_PMV_TOTAL3'
131 ,p_changecol=>'BIX_PMV_AI_SPANS_CG'
132 ,p_changetotalcol=>'BIX_PMV_TOTAL4'
133 )
134 ||','
135 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
136 p_percentage=>'Y'
137 ,p_num=>'NVL(c_e, 0)'
138 ,p_denom=>'NVL(c_d,0)'
139 ,p_pnum=>'NVL(p_e, 0)'
140 ,p_pdenom=>'NVL(p_d,0)'
141 ,p_measurecol=>'BIX_PMV_AI_ABANRATE_CP'
142 ,p_totalcol=>'BIX_PMV_TOTAL5'
143 ,p_changecol=>'BIX_PMV_AI_ABANRATE_CG'
144 ,p_changetotalcol=>'BIX_PMV_TOTAL6'
145 )
146 ||','
147 /* For KPI-Abandon rate Current period - got from BIX_PMV_AI_ABANRATE_CP*/
148 /* For KPI-Abandon rate Prior period */
149 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
150 p_percentage=>'Y'
151 ,p_num=>'NVL(p_e, 0)'
152 ,p_denom=>'NVL(p_d,0)'
153 ,p_measurecol=>'BIX_CALC_ITEM11'
154 ,p_totalcol=>'BIX_CALC_ITEM12'
155 )
156 ||','
157 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
158 p_percentage=>'Y'
159 ,p_num=>'NVL(c_f, 0)'
160 ,p_denom=>'NVL(c_g,0)'
161 ,p_pnum=>'NVL(p_f, 0)'
162 ,p_pdenom=>'NVL(p_g,0)'
163 ,p_measurecol=>'BIX_PMV_AI_TRANRATE_CP'
164 ,p_totalcol=>'BIX_PMV_TOTAL7'
165 ,p_changecol=>'BIX_PMV_AI_TRANRATE_CG'
166 ,p_changetotalcol=>'BIX_PMV_TOTAL8'
167 )
168 ||','
169 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
170 p_percentage=>'N'
171 ,p_num=>'NVL(c_k, 0)'
172 ,p_denom=>'NVL(c_i,0)'
173 ,p_pnum=>'NVL(p_k, 0)'
174 ,p_pdenom=>'NVL(p_i,0)'
175 ,p_measurecol=>'BIX_PMV_AI_AVGTALK_CP'
176 ,p_totalcol=>'BIX_PMV_TOTAL13'
177 ,p_changecol=>'BIX_PMV_AI_AVGTALK_CG'
178 ,p_changetotalcol=>'BIX_PMV_TOTAL14'
179 )
180 ||','
181
182 /* For KPI- Average Talk Current got from BIX_PMV_AI_AVGTALK_CP*/
183 /* For KPI- Average Talk Prior*/
184 /*||bix_pmv_dbi_utl_pkg.get_divided_measure(
185 p_percentage=>'N'
186 ,p_num=>'NVL(p_k, 0)'
187 ,p_denom=>'NVL(p_i,0)'
188 ,p_measurecol=>'BIX_CALC_ITEM23'
189 ,p_totalcol=>'BIX_CALC_ITEM24'
190 )
191 ||','
192 */
193 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
194 p_percentage=>'N'
195 ,p_num=>'NVL(c_j, 0)'
196 ,p_denom=>'NVL(c_i,0)'
197 ,p_pnum=>'NVL(p_j, 0)'
198 ,p_pdenom=>'NVL(p_i,0)'
199 ,p_measurecol=>'BIX_PMV_AI_AVGWRAP_CP'
200 ,p_totalcol=>'BIX_PMV_TOTAL15'
201 ,p_changecol=>'BIX_PMV_AI_AVGWRAP_CG'
202 ,p_changetotalcol=>'BIX_PMV_TOTAL16'
203 )
204 ||','
205 /* For KPI- Average Wrap Current - got from BIX_PMV_AI_AVGWRAP_CP */
206 /* For KPI- Average Wrap Prior */
207 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
208 p_percentage=>'N'
209 ,p_num=>'NVL(p_j, 0)'
210 ,p_denom=>'NVL(p_i,0)'
211 ,p_measurecol=>'BIX_CALC_ITEM27'
212 ,p_totalcol=>'BIX_CALC_ITEM28'
213 )
214 ||','
215 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
216 p_percentage=>'Y'
217 ,p_num=>'NVL(p_a, 0)'
218 ,p_denom=>'NVL(p_d,0)'
219 ,p_measurecol=>'BIX_PMV_AI_SL_PP'
220 )
221 ||','
222 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
223 p_percentage=>'N'
224 ,p_num=>'NVL(p_c, 0)'
225 ,p_denom=>'NVL(p_g,0)'
226 ,p_measurecol=>'BIX_PMV_AI_SPANS_PP'
227 )
228 ||','
229 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
230 p_percentage=>'Y'
231 ,p_num=>'NVL(p_e, 0)'
232 ,p_denom=>'NVL(p_d,0)'
233 ,p_measurecol=>'BIX_PMV_AI_ABANRATE_PP'
234 )
235 ||','
236 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
237 p_percentage=>'N'
238 ,p_num=>'NVL(p_k, 0)'
239 ,p_denom=>'NVL(p_i,0)'
240 ,p_measurecol=>'BIX_PMV_AI_AVGTALK_PP'
241 )
242 ||','
243 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
244 p_curr=>'NVL(p_g, 0)'
245 ,p_measurecol=>'BIX_PMV_AI_INCALLHAND_PP'
246 )
247 ||' FROM((';
248 RETURN l_measure_txt;
249 EXCEPTION
250 WHEN OTHERS THEN
251 RAISE;
252 END GET_MEASURES;
253
254
255 PROCEDURE GET_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
256 p_sql_text OUT NOCOPY VARCHAR2,
257 p_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
258 )
259 AS
260 l_sqltext VARCHAR2(32000) ;
261
262
263 l_call_center VARCHAR2(3000);
264 l_classification VARCHAR2(3000);
265 l_dnis VARCHAR2(3000);
266 l_view_by VARCHAR2 (120);
267 l_column_name VARCHAR2(1000);
268
269 l_custom_rec BIS_QUERY_ATTRIBUTES;
270
271 l_as_of_date DATE;
272 l_period_type varchar2(2000);
273 l_record_type_id NUMBER;
274
275
276 l_sqltext_cont VARCHAR2(32000) ;
277 l_where_clause VARCHAR2(1000) ;
278 l_group_by_clause VARCHAR2(1000) ;
279 l_view_by_select VARCHAR2(500) ;
280 l_comp_type VARCHAR2(500) ;
281 l_xtd VARCHAR2(500) ;
282 l_mv VARCHAR2 (240);
283 l_view_by_col VARCHAR2 (120);
284 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
285 l_col_tbl_cont poa_dbi_util_pkg.poa_dbi_col_tbl;
286 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
287 l_filter_where VARCHAR2 (2000);
288 l_func_area CONSTANT varchar2(5) := 'ITATR';
289 l_mv_set CONSTANT varchar2(3) := 'ITM';
290 l_version varchar2(3) := NULL;
291 l_timetype CONSTANT varchar2(3) := 'XTD';
292 l_generate_viewby VARCHAR2(1);
293
294
295
296 BEGIN
297
298 l_generate_viewby :='N';
299
300 /* Initialize the variables */
301 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
302
303 /* Trial for Util Package.. */
304
305 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
306 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
307 l_col_tbl_cont := poa_dbi_util_pkg.poa_dbi_col_tbl ();
308
309
310 bix_pmv_dbi_utl_pkg.process_parameters
311 ( p_param => p_page_parameter_tbl
312 , p_trend => 'N'
313 , p_func_area => l_func_area
314 , p_version => l_version
315 , p_mv_set => l_mv_set
316 , p_where_clause => l_where_clause
317 , p_mv => l_mv
318 , p_join_tbl => l_join_tbl
319 , p_comp_type => l_comp_type
320 , p_xtd => l_xtd
321 , p_view_by_select => l_view_by_select
322 , p_view_by => l_view_by
323 );
324
325 IF l_view_by = bix_pmv_dbi_utl_pkg.g_ai_ccntr_dim THEN
326 l_generate_viewby :='Y';
327 ELSIF l_view_by = bix_pmv_dbi_utl_pkg.g_ai_class_dim THEN
328 l_group_by_clause :='group by classification_value &ORDER_BY_CLAUSE nulls last';
329 ELSIF l_view_by = bix_pmv_dbi_utl_pkg.g_ai_dnis_dim THEN
330 l_group_by_clause :='group by dnis_name &ORDER_BY_CLAUSE nulls last';
331 END IF;
332
333
334 -- Populate col table with columns for continued measures
335 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl_cont
336 , p_col_name => 'NULL'
337 , p_grand_total => 'N'
338 , p_alias_name => 'a'
339 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
340 , p_to_date_type => l_timetype
341 );
342 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl_cont
343 , p_col_name => 'NULL'
344 , p_grand_total => 'N'
345 , p_alias_name => 'b'
346 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
347 , p_to_date_type => l_timetype
348 );
349
350 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl_cont
351 , p_col_name => 'NULL'
352 , p_grand_total => 'N'
353 , p_alias_name => 'c'
354 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
355 , p_to_date_type => l_timetype
356 );
357
358 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl_cont
359 , p_col_name => '(CASE WHEN media_item_type in (''TELE_INB'',''TELE_DIRECT'') THEN call_cont_calls_offered_na END)'
360 , p_grand_total => 'N'
361 , p_alias_name => 'd'
362 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
363 , p_to_date_type => l_timetype
364 );
365
366 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl_cont
367 , p_col_name => 'NULL'
368 , p_grand_total => 'N'
369 , p_alias_name => 'e'
370 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
371 , p_to_date_type => l_timetype
372 );
373
374 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl_cont
375 , p_col_name => 'NULL'
376 , p_grand_total => 'N'
377 , p_alias_name => 'f'
378 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
379 , p_to_date_type => l_timetype
380 );
381
382 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl_cont
383 , p_col_name => '(CASE WHEN media_item_type in (''TELE_INB'',''TELE_DIRECT'') THEN call_cont_calls_handled_tot_na END)'
384 , p_grand_total => 'N'
385 , p_alias_name => 'g'
386 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
387 , p_to_date_type => l_timetype
388 );
389
390 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl_cont
391 , p_col_name => 'NULL'
392 , p_grand_total => 'N'
393 , p_alias_name => 'h'
394 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
395 , p_to_date_type => l_timetype
396 );
397
398 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl_cont
399 , p_col_name => 'call_cont_calls_handled_tot_na'
400 , p_grand_total => 'N'
401 , p_alias_name => 'i'
402 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
403 , p_to_date_type => l_timetype
404 );
405
406 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl_cont
407 , p_col_name => 'NULL'
408 , p_grand_total => 'N'
409 , p_alias_name => 'j'
410 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
411 , p_to_date_type => l_timetype
412 );
413
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 => 'k'
418 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
419 , p_to_date_type => l_timetype
420 );
421
422 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl_cont
423 , p_col_name => 'NULL'
424 , p_grand_total => 'N'
425 , p_alias_name => 'l'
426 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
427 , p_to_date_type => l_timetype
428 );
429
430 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl_cont
431 , p_col_name => 'NULL'
432 , p_grand_total => 'N'
433 , p_alias_name => 'm'
434 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
435 , p_to_date_type => l_timetype
436 );
437
438 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl_cont
439 , p_col_name => 'NULL'
440 , p_grand_total => 'N'
441 , p_alias_name => 'n'
442 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
443 , p_to_date_type => l_timetype
444 );
445
446
447 l_sqltext_cont :=
448 bix_pmv_dbi_utl_pkg.status_sql_daylevel (
449 p_fact_name => l_mv
450 , p_row_type_where_clause => l_where_clause --this shud come from util package
451 , p_col_name => l_col_tbl_cont
452 , p_join_tables => l_join_tbl
453 , p_time_type => 'ESD'
454 , p_union => 'ALL');
455
456 -- Populate col table with regular columns
457
458 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl
459 , p_col_name => '(CASE WHEN media_item_type in (''TELE_INB'',''TELE_DIRECT'') THEN agent_calls_answered_by_goal END)'
460 , p_grand_total => 'N'
461 , p_alias_name => 'a'
462 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
463 , p_to_date_type => l_timetype
464 );
465 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
466 , p_col_name => '(CASE WHEN media_item_type in (''TELE_INB'',''TELE_DIRECT'') THEN agent_calls_handled_total END)'
467 , p_grand_total => 'N'
468 , p_alias_name => 'b'
469 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
470 , p_to_date_type => l_timetype
471 );
472 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
473 , p_col_name => '(CASE WHEN media_item_type in (''TELE_INB'',''TELE_DIRECT'') THEN call_tot_queue_to_answer END)'
474 , p_grand_total => 'N'
475 , p_alias_name => 'c'
476 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
477 , p_to_date_type => l_timetype
478 );
479 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
480 , p_col_name => '(CASE WHEN media_item_type in (''TELE_INB'',''TELE_DIRECT'') THEN call_calls_offered_total END)'
481 , p_grand_total => 'N'
482 , p_alias_name => 'd'
483 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
484 , p_to_date_type => l_timetype
485 );
486 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
487 , p_col_name => '(CASE WHEN media_item_type in (''TELE_INB'',''TELE_DIRECT'') THEN call_calls_abandoned END)'
488 , p_grand_total => 'N'
489 , p_alias_name => 'e'
490 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
491 , p_to_date_type => l_timetype
492 );
493 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
494 , p_col_name => '(CASE WHEN media_item_type in (''TELE_INB'',''TELE_DIRECT'') THEN call_calls_transferred END)'
495 , p_grand_total => 'N'
496 , p_alias_name => 'f'
497 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
498 , p_to_date_type => l_timetype
499 );
500 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
501 , p_col_name => '(CASE WHEN media_item_type in (''TELE_INB'',''TELE_DIRECT'') THEN call_calls_handled_total END)'
502 , p_grand_total => 'N'
503 , p_alias_name => 'g'
504 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
505 , p_to_date_type => l_timetype
506 );
507 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
508 , p_col_name => '(CASE WHEN media_item_type=''TELE_WEB_CALLBACK'' THEN call_calls_handled_total END)'
509 , p_grand_total => 'N'
510 , p_alias_name => 'h'
511 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
512 , p_to_date_type => l_timetype
513 );
514 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
515 , p_col_name => 'call_calls_handled_total'
516 , p_grand_total => 'N'
517 , p_alias_name => 'i'
518 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
519 , p_to_date_type => l_timetype
520 );
521 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
522 , p_col_name => 'agent_wrap_time_nac'
523 , p_grand_total => 'N'
524 , p_alias_name => 'j'
525 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
526 , p_to_date_type => l_timetype
527 );
528 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
529 , p_col_name => 'call_talk_time'
530 , p_grand_total => 'N'
531 , p_alias_name => 'k'
532 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
533 , p_to_date_type => l_timetype
534 );
535 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
536 , p_col_name => 'agent_sr_created'
537 , p_grand_total => 'N'
538 , p_alias_name => 'l'
539 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
540 , p_to_date_type => l_timetype
541 );
542 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
543 , p_col_name => 'agent_leads_created'
544 , p_grand_total => 'N'
545 , p_alias_name => 'm'
546 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
547 , p_to_date_type => l_timetype
548 );
549 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
550 , p_col_name => 'agent_opportunities_created'
551 , p_grand_total => 'N'
552 , p_alias_name => 'n'
553 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
554 , p_to_date_type => l_timetype
555 );
556
557 l_sqltext :='select rset.*,BIX_PMV_AI_SL_CP BIX_CALC_ITEM1,BIX_PMV_TOTAL1 BIX_CALC_ITEM2,'
558 ||'BIX_PMV_AI_ABANRATE_CP BIX_CALC_ITEM9,BIX_PMV_TOTAL5 BIX_CALC_ITEM10,'
559 ||'BIX_PMV_AI_AVGWRAP_CP BIX_CALC_ITEM25,'
560 ||'BIX_PMV_TOTAL15 BIX_CALC_ITEM26 from ('
561 || get_measures(l_view_by_select) || l_sqltext_cont ||
562 poa_dbi_template_pkg.status_sql (
563 p_fact_name => l_mv
564 , p_where_clause => l_where_clause
565 , p_filter_where => l_filter_where
566 , p_join_tables => l_join_tbl
567 , p_use_windowing => 'N'
568 , p_col_name => l_col_tbl
569 , p_use_grpid => 'N'
570 , p_paren_count => 3
571 , p_generate_viewby => l_generate_viewby)
572 ||l_group_by_clause|| get_zeronull_clause;
573
574 l_sqltext:=replace(replace(replace(replace(replace(l_sqltext,
575 ' ',' '),' ',' '),' ',' '),' ',' '),' ',' ');
576
577
578
579 p_sql_text := l_sqltext;
580 bix_pmv_dbi_utl_pkg.get_bind_vars (p_custom_output,p_func_area => l_func_area);
581
582
583 -- l_sqltext:=NULL;
584 -- l_where_clause:=NULL;
585
586 /* Initialize p_custom_output and l_custom_rec */
587 /*p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
588 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
589
590 l_where_clause := NULL;
591 l_call_center := NULL;
592 l_classification := NULL;
593 l_dnis := NULL;
594 l_view_by := NULL;
595 l_column_name := NULL;
596
597 -- Get the parameters
598
599 BIX_PMV_DBI_UTL_PKG.get_ai_page_params( p_page_parameter_tbl,
600 l_as_of_date,
601 l_period_type,
602 l_record_type_id,
603 l_comp_type,
604 l_call_center,
605 l_classification,
606 l_dnis,
607 l_view_by
608 );
609
610 IF l_call_center IS NOT NULL THEN
611 l_where_clause := ' AND a.server_group_id IN (:l_call_center) ';
612 END IF;
613
614 IF l_classification IS NOT NULL THEN
615 l_where_clause := l_where_clause || ' AND a.classification_value IN (:l_classification) ';
616 END IF;
617
618 --insert into bixtest values ('l_dnis is ' || l_dnis);
619 --commit;
620
621 IF l_dnis IS NOT NULL THEN
622 IF l_dnis = '''INBOUND'''
623 THEN
624 l_where_clause := l_where_clause ||
625 ' AND a.dnis_name <> ''OUTBOUND'' ';
626 ELSIF l_dnis = '''OUTBOUND'''
627 THEN
628 l_where_clause := l_where_clause ||
629 ' AND a.dnis_name = ''OUTBOUND'' ';
630 ELSE
631 l_where_clause := l_where_clause ||
632 ' AND a.dnis_name IN (:l_dnis) ';
633 END IF;
634 END IF;
635
636
637 IF l_view_by = 'BIX_TELEPHONY+BIX_CALL_CENTER' THEN
638 l_column_name := 'server_group_id ';
639 --l_column_name := 'dnis_name ';
640 ELSIF l_view_by = 'BIX_TELEPHONY+BIX_CALL_CLASSIFICATION' THEN
641 l_column_name := 'classification_value ';
642 ELSIF l_view_by = 'BIX_TELEPHONY+BIX_DNIS' THEN
643 l_column_name := 'dnis_name ';
644 ELSE
645 l_column_name := 'classification_value ';
646 END IF;
647
648 --
649
650 IF l_column_name = 'server_group_id '
651 THEN
652 l_sqltext := 'SELECT group_name VIEWBY ';
653 ELSE
654 l_sqltext := 'SELECT ' || l_column_name || ' VIEWBY ';
655 END IF;
656
657 l_sqltext := l_sqltext ||
658 '
659 ,ROUND(a / g1 * 100, 1)
660 BIX_PMV_AI_SL_CP
661 ,ROUND(SUM(a) OVER() / SUM(g1) OVER() * 100, 1)
662 BIX_PMV_TOTAL1
663 ,ROUND((a / g1 * 100) - (b / h1 * 100), 1)
664 BIX_PMV_AI_SL_CG
665 ,ROUND((SUM(a) OVER() / SUM(g1) OVER() * 100) - (SUM(b) OVER() / SUM(h1) OVER() * 100), 1)
666 BIX_PMV_TOTAL2
667 ,ROUND(b / h1 * 100, 1)
668 BIX_PMV_AI_SL_PP
669 ,ROUND(e / m1, 1) BIX_PMV_AI_SPANS_CP
670 ,ROUND(SUM(e) OVER() / SUM(m1) OVER(), 1)
671 BIX_PMV_TOTAL3
672 ,ROUND(((e / m1) - (f / n1)) / DECODE(f / n1, 0, NULL, f / n1) * 100 , 1)
673 BIX_PMV_AI_SPANS_CG
674 ,ROUND((SUM(e) OVER() / SUM(m1) OVER() - SUM(f) OVER() / SUM(n1) OVER()) / DECODE(SUM(f) OVER() / SUM(n1) OVER(), 0, NULL,
675 SUM(f) OVER() / SUM(n1) OVER()) * 100 , 1)
676 BIX_PMV_TOTAL4
677 ,ROUND(f / n1, 1) BIX_PMV_AI_SPANS_PP
678 ,ROUND(i / g1 * 100, 1)
679 BIX_PMV_AI_ABANRATE_CP
680 ,ROUND(SUM(i) OVER() / SUM(g1) OVER() * 100, 1)
681 BIX_PMV_TOTAL5
682 ,ROUND((i / g1 * 100) - (j / h1 * 100), 1)
683 BIX_PMV_AI_ABANRATE_CG
684 ,ROUND(SUM(i) OVER() / SUM(g1) OVER() * 100 - SUM(j) OVER() / SUM(h1) OVER() * 100, 1)
685 BIX_PMV_TOTAL6
686 ,ROUND(j / h1 * 100, 1)
687 BIX_PMV_AI_ABANRATE_PP
688 ,ROUND(k / m * 100, 1)
689 BIX_PMV_AI_TRANRATE_CP
690 ,ROUND(SUM(k) OVER() / SUM(m) OVER() * 100, 1)
691 BIX_PMV_TOTAL7
692 ,ROUND((k / m * 100) - (l / n * 100), 1)
693 BIX_PMV_AI_TRANRATE_CG
694 ,ROUND(SUM(k) OVER() / SUM(m) OVER() * 100 - SUM(l) OVER() / SUM(n) OVER() * 100, 1)
695 BIX_PMV_TOTAL8
696 ,nvl(m,0) BIX_PMV_AI_INCALLHAND_CP
697 ,nvl(SUM(m) OVER() ,0)
698 BIX_PMV_TOTAL9
699 ,ROUND((m - n) / DECODE(n, 0, NULL, n) * 100, 1)
700 BIX_PMV_AI_INCALLHAND_CG
701 ,ROUND((SUM(m) OVER() - SUM(n) OVER()) / DECODE(SUM(n) OVER(), 0, NULL, SUM(n) OVER()) * 100, 1)
702 BIX_PMV_TOTAL10
703 ,nvl(n,0) BIX_PMV_AI_INCALLHAND_PP
704 ,o BIX_PMV_AI_WEBCALL_CP
705 ,SUM(o) OVER() BIX_PMV_TOTAL11
706 ,ROUND((o - p) / DECODE(p, 0, NULL, p) * 100, 1)
707 BIX_PMV_AI_WEBCALL_CG
708 ,ROUND((SUM(o) OVER() - SUM(p) OVER()) / DECODE(SUM(p) OVER(), 0, NULL, SUM(p) OVER()) * 100, 1)
709 BIX_PMV_TOTAL12
710 ,ROUND(u / q, 1) BIX_PMV_AI_AVGTALK_CP
711 ,ROUND(SUM(u) OVER() / SUM(q) OVER(), 1)
712 BIX_PMV_TOTAL13
713 ,ROUND(((u / q) - (v / r)) / DECODE(v / r, 0, NULL, v / r) * 100 , 1)
714 BIX_PMV_AI_AVGTALK_CG
715 ,ROUND((SUM(u) OVER() / SUM(q) OVER() - SUM(v) OVER() / SUM(r) OVER()) /
716 DECODE(SUM(v) OVER() / SUM(r) OVER(), 0, NULL, SUM(v) OVER() / SUM(r) OVER()) * 100 , 1)
717 BIX_PMV_TOTAL14
718 ,ROUND(v / r, 1) BIX_PMV_AI_AVGTALK_PP
719 ,ROUND(s / q, 1) BIX_PMV_AI_AVGWRAP_CP
720 ,ROUND(SUM(s) OVER() / SUM(q) OVER(), 1)
721 BIX_PMV_TOTAL15
722 ,ROUND(((s / q) - (t / r)) / DECODE(t / r, 0, NULL, t / r) * 100, 1)
723 BIX_PMV_AI_AVGWRAP_CG
724 ,ROUND((SUM(s) OVER() / SUM(q) OVER() - SUM(t) OVER() / SUM(r) OVER()) /
725 DECODE(SUM(t) OVER() / SUM(r) OVER(), 0, NULL, SUM(t) OVER() / SUM(r) OVER()) * 100, 1)
726 BIX_PMV_TOTAL16
727 ,w BIX_PMV_AI_SRCR_CP
728 ,SUM(w) OVER() BIX_PMV_TOTAL17
729 ,ROUND((w - x) / DECODE(x, 0, NULL, x) * 100, 1)
730 BIX_PMV_AI_SRCR_CG
731 ,ROUND((SUM(w) OVER() - SUM(x) OVER()) / DECODE(SUM(x) OVER(), 0, NULL, SUM(x) OVER()) * 100, 1)
732 BIX_PMV_TOTAL18
733 ,y BIX_PMV_AI_LECR_CP
734 ,SUM(y) OVER() BIX_PMV_TOTAL19
735 ,ROUND((y - z) / DECODE(z, 0, NULL, z) * 100, 1)
736 BIX_PMV_AI_LECR_CG
737 ,ROUND((SUM(y) OVER() - SUM(z) OVER()) / DECODE(SUM(z) OVER(), 0, NULL, SUM(z) OVER()) * 100, 1)
738 BIX_PMV_TOTAL20
739 ,y1 BIX_PMV_AI_OPCR_CP
740 ,SUM(y1) OVER() BIX_PMV_TOTAL23
741 ,ROUND((y1 - z1) / DECODE(z1, 0, NULL, z1) * 100, 1)
742 BIX_PMV_AI_OPCR_CG
743 ,ROUND((SUM(y1) OVER() - SUM(z1) OVER()) / DECODE(SUM(z1) OVER(), 0, NULL, SUM(z1) OVER()) * 100, 1)
744 BIX_PMV_TOTAL24
745 ,a1 BIX_PMV_AI_CUST_CP
746 ,a9 BIX_PMV_TOTAL21
747 ,ROUND((a1 - a2) / DECODE(a2, 0, 1, a2) * 100, 1)
748 BIX_PMV_AI_CUST_CG
749 ,ROUND((a9 - a10) / DECODE(a10, 0, NULL, a10) * 100, 1)
750 BIX_PMV_TOTAL22
751 FROM ( ';
752
753 l_sqltext := l_sqltext || '
754 SELECT
755 ' || l_column_name || '
756 ,SUM(NVL(a,0)) a
757 ,SUM(NVL(b,0)) b
758 ,DECODE(SUM(c), 0, NULL, SUM(c)) c
759 ,DECODE(SUM(d), 0, NULL, SUM(d)) d
760 ,SUM(NVL(e,0)) e
761 ,SUM(NVL(f,0)) f
762 ,DECODE(SUM(g), 0, NULL, SUM(g)) g
763 ,DECODE(SUM(h), 0, NULL, SUM(h)) h
764 ,SUM(NVL(i,0)) i
765 ,SUM(NVL(j,0)) j
766 ,SUM(NVL(k,0)) k
767 ,SUM(NVL(l,0)) l
768 ,DECODE(SUM(m), 0, NULL, SUM(m)) m
769 ,DECODE(SUM(n), 0, NULL, SUM(n)) n
770 ,SUM(NVL(o,0)) o
771 ,SUM(NVL(p,0)) p
772 ,SUM(NVL(s,0)) s
773 ,SUM(NVL(t,0)) t
774 ,SUM(NVL(u,0)) u
775 ,SUM(NVL(v,0)) v
776 ,SUM(NVL(w,0)) w
777 ,SUM(NVL(x,0)) x
778 ,SUM(NVL(y,0)) y
779 ,SUM(NVL(z,0)) z
780 ,SUM(NVL(y1,0)) y1
781 ,SUM(NVL(z1,0)) z1
782 ,COUNT(DISTINCT(CASE WHEN report_date = &BIS_CURRENT_ASOF_DATE
783 AND party_id <> -1
784 THEN PARTY_ID END ))
785 a1
786 ,COUNT(DISTINCT(CASE WHEN report_date = &BIS_PREVIOUS_ASOF_DATE
787 AND party_id <> -1
788 THEN PARTY_ID END ))
789 a2
790 ,MIN(a9) a9
791 ,MIN(a10) a10
792 ,DECODE(SUM(NVL(q,0) + NVL(a3,0)), 0, NULL, SUM(NVL(q,0) + NVL(a3,0))) q
793 ,DECODE(SUM(NVL(r,0) + NVL(a4,0)), 0, NULL, SUM(NVL(r,0) + NVL(a4,0))) r
794 ,DECODE(SUM(NVL(m,0) + NVL(a5,0)), 0, NULL, SUM(NVL(m,0) + NVL(a5,0))) m1
795 ,DECODE(SUM(NVL(n,0) + NVL(a6,0)), 0, NULL, SUM(NVL(n,0) + NVL(a6,0))) n1
796 ,DECODE(SUM(NVL(g,0) + NVL(a7,0)), 0, NULL, SUM(NVL(g,0) + NVL(a7,0))) g1
797 ,DECODE(SUM(NVL(h,0) + NVL(a8,0)), 0, NULL, SUM(NVL(h,0) + NVL(a8,0))) h1
798 FROM ( ';
799
800 l_sqltext := l_sqltext || '
801 SELECT
802 ' || l_column_name || '
803 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',agent_calls_answered_by_goal,
804 ''TELE_DIRECT'',agent_calls_answered_by_goal, 0))
805 a
806 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',agent_calls_answered_by_goal,
807 ''TELE_DIRECT'',agent_calls_answered_by_goal, 0))
808 b
809 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',agent_calls_handled_total,
810 ''TELE_DIRECT'',agent_calls_handled_total, 0))
811 c
812 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',agent_calls_handled_total,
813 ''TELE_DIRECT'',agent_calls_handled_total, 0))
814 d
815 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_tot_queue_to_answer,
816 ''TELE_DIRECT'',call_tot_queue_to_answer, 0))
817 e
818 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_tot_queue_to_answer,
819 ''TELE_DIRECT'',call_tot_queue_to_answer, 0))
820 f
821 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_calls_offered_total,
822 ''TELE_DIRECT'',call_calls_offered_total, 0))
823 g
824 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_calls_offered_total,
825 ''TELE_DIRECT'',call_calls_offered_total, 0))
826 h
827 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_calls_abandoned,
828 ''TELE_DIRECT'',call_calls_abandoned, 0))
829 i
830 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_calls_abandoned,
831 ''TELE_DIRECT'',call_calls_abandoned, 0))
832 j
833 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_calls_transferred,
834 ''TELE_DIRECT'',call_calls_transferred, 0))
835 k
836 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_calls_transferred,
837 ''TELE_DIRECT'',call_calls_transferred, 0))
838 l
839 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'', call_calls_handled_total,
840 ''TELE_DIRECT'', call_calls_handled_total, 0))
841 m
842 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'', call_calls_handled_total,
843 ''TELE_DIRECT'', call_calls_handled_total, 0))
844 n
845 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_WEB_CALLBACK'',
846 call_calls_handled_total, 0))
847 o
848 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_WEB_CALLBACK'',
849 call_calls_handled_total, 0))
850 p
851 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,call_calls_handled_total)
852 q
853 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,call_calls_handled_total)
854 r
855 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_wrap_time_nac)
856 s
857 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_wrap_time_nac)
858 t
859 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,call_talk_time)
860 u
861 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,call_talk_time)
862 v
863 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_sr_created)
864 w
865 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_sr_created)
866 x
867 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_leads_created)
868 y
869 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_leads_created)
870 z
871 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_opportunities_created)
872 y1
873 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_opportunities_created)
874 z1
875 ,party_id party_id
876 ,calendar.report_date report_date
877 ,NULL a3
878 ,NULL a4
879 ,NULL a5
880 ,NULL a6
881 ,NULL a7
882 ,NULL a8
883 ,COUNT(DISTINCT(CASE WHEN calendar.report_date = &BIS_CURRENT_ASOF_DATE
884 AND party_id <> -1
885 THEN PARTY_ID END )) OVER()
886 a9
887 ,COUNT(DISTINCT(CASE WHEN calendar.report_date = &BIS_PREVIOUS_ASOF_DATE
888 AND party_id <> -1
889 THEN PARTY_ID END )) OVER()
890 a10
891 FROM
892 bix_ai_call_details_mv a,
893 fii_time_rpt_struct calendar
894 WHERE a.row_type = ''CDPR''
895 AND a.time_id = calendar.time_id
896 AND a.period_type_id = calendar.period_type_id
897 AND calendar.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
898 AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) = calendar.record_type_id ';
899
900 l_sqltext := l_sqltext || l_where_clause || '
901 UNION ALL
902 SELECT
903 ' || l_column_name || '
904 ,NULL a
905 ,NULL b
906 ,NULL c
907 ,NULL d
908 ,NULL e
909 ,NULL f
910 ,NULL g
911 ,NULL h
912 ,NULL i
913 ,NULL j
914 ,NULL k
915 ,NULL l
916 ,NULL m
917 ,NULL n
918 ,NULL o
919 ,NULL p
920 ,NULL q
921 ,NULL r
922 ,NULL s
923 ,NULL t
924 ,NULL u
925 ,NULL v
926 ,NULL w
927 ,NULL x
928 ,NULL y
929 ,NULL z
930 ,NULL y1
931 ,NULL z1
932 ,NULL party_id
933 ,NULL report_date
934 ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),call_cont_calls_handled_tot_na)
935 a3
936 ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')),call_cont_calls_handled_tot_na)
937 a4
938 ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),DECODE(media_item_type, ''TELE_INB'',
939 call_cont_calls_handled_tot_na, ''TELE_DIRECT'', call_cont_calls_handled_tot_na, 0))
940 a5
941 ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')),DECODE(media_item_type, ''TELE_INB'',
942 call_cont_calls_handled_tot_na, ''TELE_DIRECT'', call_cont_calls_handled_tot_na, 0))
943 a6
944 ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),DECODE(media_item_type, ''TELE_INB'',
945 call_cont_calls_offered_na, ''TELE_DIRECT'', call_cont_calls_offered_na, 0))
946 a7
947 ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')),DECODE(media_item_type, ''TELE_INB'',
948 call_cont_calls_offered_na, ''TELE_DIRECT'', call_cont_calls_offered_na, 0))
949 a8
950 ,NULL a9
951 ,NULL a10
952 FROM
953 bix_ai_call_details_mv a
954 WHERE row_type = ''CDPR''
955 AND time_id IN (TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),
956 TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')))
957 AND period_type_id = 1 ';
958
959 l_sqltext := l_sqltext || l_where_clause || '
960 )
961 GROUP BY ' || l_column_name || '
962 ) a ';
963
964
965 IF l_view_by = 'BIX_TELEPHONY+BIX_CALL_CENTER' THEN
966 l_sqltext := l_sqltext ||
967 ' , ieo_svr_groups grp
968 WHERE a.server_group_id = grp.server_group_id
969 &ORDER_BY_CLAUSE ';
970 ELSIF l_view_by = 'BIX_TELEPHONY+BIX_CALL_CLASSIFICATION' THEN
971 l_sqltext := l_sqltext ||
972 ' &ORDER_BY_CLAUSE ';
973 ELSIF l_view_by = 'BIX_TELEPHONY+BIX_DNIS' THEN
974 l_sqltext := l_sqltext ||
975 ' &ORDER_BY_CLAUSE ';
976 ELSE
977 l_sqltext := l_sqltext ||
978 ' &ORDER_BY_CLAUSE ';
979 END IF;
980
981
982 p_sql_text := l_sqltext;
983
984 --
985 p_custom_output.EXTEND();
986 IF l_call_center IS NOT NULL THEN
987 l_custom_rec.attribute_name := ':l_call_center' ;
988 l_custom_rec.attribute_value:= l_call_center;
989 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
990 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
991
992 p_custom_output.Extend();
993 p_custom_output(p_custom_output.count) := l_custom_rec;
994 END IF;
995
996 IF l_classification IS NOT NULL THEN
997 l_custom_rec.attribute_name := ':l_classification' ;
998 l_custom_rec.attribute_value:= l_classification;
999 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1000 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1001
1002 p_custom_output.Extend();
1003 p_custom_output(p_custom_output.count) := l_custom_rec;
1004 END IF;
1005
1006 IF l_dnis IS NOT NULL AND l_dnis NOT IN ('INBOUND','OUTBOUND')
1007 THEN
1008 l_custom_rec.attribute_name := ':l_dnis';
1009 l_custom_rec.attribute_value:= l_dnis;
1010 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1011 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1012
1013 p_custom_output.Extend();
1014 p_custom_output(p_custom_output.count) := l_custom_rec;
1015 END IF;
1016
1017
1018 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
1019 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
1020 l_custom_rec.attribute_value := l_view_by;
1021
1022 p_custom_output.EXTEND();
1023 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
1024 */
1025 EXCEPTION
1026 WHEN OTHERS THEN
1027 RAISE;
1028 END GET_SQL;
1029 END BIX_PMV_AI_TELDTL_RPT_PKG;