[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AI_CSTDTL_RPT_PKG
Source
1 PACKAGE BODY BIX_PMV_AI_CSTDTL_RPT_PKG AS
2 /*$Header: bixicstr.plb 120.0 2005/05/25 17:21:40 appldev noship $ */
3
4 FUNCTION GET_ZERONULL_CLAUSE RETURN VARCHAR2
5 IS
6 l_having_clause VARCHAR2(1000);
7 BEGIN
8 l_having_clause:=') where
9 ( abs(nvl(BIX_PMV_AI_INCALLHAND_CP,0)) + abs(nvl(BIX_PMV_AI_INCALLHAND_CG,0))
10 + abs(nvl(BIX_PMV_AI_SRCR_CP,0))+abs(nvl(BIX_PMV_AI_SRCR_CG,0))
11 + abs(nvl(BIX_PMV_AI_LECR_CP,0)) + abs(nvl(BIX_PMV_AI_LECR_CG,0))
12 + abs(nvl(BIX_PMV_AI_OPCR_CP,0)) +abs(nvl(BIX_PMV_AI_OPCR_CG,0))
13 +abs(nvl(BIX_PMV_AI_SL_CP,0)) +abs(nvl(BIX_PMV_AI_SL_CG,0))
14 +abs(nvl(BIX_PMV_AI_SPANS_CP,0)) +abs(nvl(BIX_PMV_AI_SPANS_CG,0))
15 +abs(nvl(BIX_PMV_AI_TRANRATE_CP,0)) +abs(nvl(BIX_PMV_AI_TRANRATE_CG,0))
16 +abs(nvl(BIX_PMV_AI_AVGTALK_CP,0)) +abs(nvl(BIX_PMV_AI_AVGTALK_CG,0))
17 +abs(nvl(BIX_PMV_AI_AVGWRAP_CP,0)) +abs(nvl(BIX_PMV_AI_AVGWRAP_CG,0))
18 ) <> 0';
19 return l_having_clause;
20 END GET_ZERONULL_CLAUSE;
21
22
23 FUNCTION GET_MEASURES RETURN VARCHAR2
24 IS
25 l_measure_txt VARCHAR2(8000);
26 l_goal NUMBER;
27 BEGIN
28 IF (FND_PROFILE.DEFINED('BIX_CALL_SLGOAL_PERCENT')) THEN
29 BEGIN
30 l_goal := TO_NUMBER(FND_PROFILE.VALUE('BIX_CALL_SLGOAL_PERCENT'));
31 EXCEPTION
32 WHEN OTHERS THEN
33 l_goal := 0;
34 END;
35 ELSE
36 l_goal := 0;
37 END IF;
38
39 l_measure_txt :=
40 ' SELECT nvl(party_name,:l_unknown) BIX_PMV_AI_CUSTOMER,'||l_goal||' BIX_PMV_AI_SLGOAL,'
41 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
42 p_curr=>'to_number(NVL(c_e,0))'
43 ,p_prev=>'NVL(p_e,0)'
44 ,p_measurecol=>'BIX_PMV_AI_INCALLHAND_CP'
45 ,p_totalcol=>'BIX_PMV_TOTAL7'
46 ,p_changecol=>'BIX_PMV_AI_INCALLHAND_CG'
47 ,p_changetotalcol=>'BIX_PMV_TOTAL8'
48 )
49 ||','
50 /* For KPI- Inbound calls Handled Current */
51 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
52 p_curr=>'NVL(c_e,0)'
53 ,p_measurecol=>'BIX_CALC_ITEM17'
54 ,p_totalcol=>'BIX_CALC_ITEM18'
55 )
56 ||',' /* For KPI- Inbound calls Handled Previous */
57 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
58 p_curr=>'NVL(p_e,0)'
59 ,p_measurecol=>'BIX_CALC_ITEM19'
60 ,p_totalcol=>'BIX_CALC_ITEM20'
61 )
62 ||','
63 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
64 p_curr=>'NVL(c_i,0)'
65 ,p_prev=>'NVL(p_i,0)'
66 ,p_measurecol=>'BIX_PMV_AI_SRCR_CP'
67 ,p_totalcol=>'BIX_PMV_TOTAL13'
68 ,p_changecol=>'BIX_PMV_AI_SRCR_CG'
69 ,p_changetotalcol=>'BIX_PMV_TOTAL14'
70 )
71 ||','
72 ||/* For KPI- SR created -Current period */
73 bix_pmv_dbi_utl_pkg.get_simple_measure(
74 p_curr=>'NVL(c_i,0)'
75 ,p_measurecol=>'BIX_CALC_ITEM29'
76 ,p_totalcol=>'BIX_CALC_ITEM30'
77 )
78 ||','
79 ||/* For KPI- SR created -Prior period */
80 bix_pmv_dbi_utl_pkg.get_simple_measure(
81 p_curr=>'NVL(p_i,0)'
82 ,p_measurecol=>'BIX_CALC_ITEM31'
83 ,p_totalcol=>'BIX_CALC_ITEM32'
84 )
85 ||','
86 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
87 p_curr=>'NVL(c_j,0)'
88 ,p_prev=>'NVL(p_j,0)'
89 ,p_measurecol=>'BIX_PMV_AI_LECR_CP'
90 ,p_totalcol=>'BIX_PMV_TOTAL15'
91 ,p_changecol=>'BIX_PMV_AI_LECR_CG'
92 ,p_changetotalcol=>'BIX_PMV_TOTAL16'
93 )
94 ||','
95 ||/* For KPI- Leads created -Current period */
96 bix_pmv_dbi_utl_pkg.get_simple_measure(
97 p_curr=>'NVL(c_j,0)'
98 ,p_measurecol=>'BIX_CALC_ITEM33'
99 ,p_totalcol=>'BIX_CALC_ITEM34'
100 )
101 ||','
102 ||/* For KPI- Leads created -Priorperiod */
103 bix_pmv_dbi_utl_pkg.get_simple_measure(
104 p_curr=>'NVL(p_j,0)'
105 ,p_measurecol=>'BIX_CALC_ITEM35'
106 ,p_totalcol=>'BIX_CALC_ITEM36'
107 )
108 ||','
109 ||bix_pmv_dbi_utl_pkg.get_simple_measure(
110 p_curr=>'NVL(c_n,0)'
111 ,p_prev=>'NVL(p_n,0)'
112 ,p_measurecol=>'BIX_PMV_AI_OPCR_CP'
113 ,p_totalcol=>'BIX_PMV_TOTAL19'
114 ,p_changecol=>'BIX_PMV_AI_OPCR_CG'
115 ,p_changetotalcol=>'BIX_PMV_TOTAL20'
116 )
117 ||','
118 ||/* For KPI- Opportunities created -Current period */
119 bix_pmv_dbi_utl_pkg.get_simple_measure(
120 p_curr=>'NVL(c_n,0)'
121 ,p_measurecol=>'BIX_CALC_ITEM37'
122 ,p_totalcol=>'BIX_CALC_ITEM38'
123 )
124 ||','
125 ||/* For KPI- Opportunities created -Prior period */
126 bix_pmv_dbi_utl_pkg.get_simple_measure(
127 p_curr=>'NVL(p_n,0)'
128 ,p_measurecol=>'BIX_CALC_ITEM39'
129 ,p_totalcol=>'BIX_CALC_ITEM40'
130 )
131 ||','
132 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
133 p_percentage=>'Y'
134 ,p_num=>'NVL(c_a, 0)'
135 ,p_denom=>'NVL(c_c,0)'
136 ,p_pnum=>'NVL(p_a, 0)'
137 ,p_pdenom=>'NVL(p_c,0)'
138 ,p_measurecol=>'BIX_PMV_AI_SL_CP'
139 ,p_totalcol=>'BIX_PMV_TOTAL1'
140 ,p_changecol=>'BIX_PMV_AI_SL_CG'
141 ,p_changetotalcol=>'BIX_PMV_TOTAL2'
142 )
143 ||','
144 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
145 p_percentage=>'N'
146 ,p_num=>'NVL(c_b, 0)'
147 ,p_denom=>'NVL(c_e,0)'
148 ,p_pnum=>'NVL(p_b, 0)'
149 ,p_pdenom=>'NVL(p_e,0)'
150 ,p_measurecol=>'BIX_PMV_AI_SPANS_CP'
151 ,p_totalcol=>'BIX_PMV_TOTAL3'
152 ,p_changecol=>'BIX_PMV_AI_SPANS_CG'
153 ,p_changetotalcol=>'BIX_PMV_TOTAL4'
154 )
155 ||',' /* KPI - Average Speed to Answer */
156 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
157 p_percentage=>'N'
158 ,p_num=>'NVL(c_b, 0)'
159 ,p_denom=>'NVL(c_e,0)'
160 ,p_measurecol=>'BIX_CALC_ITEM21'
161 ,p_totalcol=>'BIX_CALC_ITEM22'
162 )
163 ||','
164 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
165 p_percentage=>'N'
166 ,p_num=>'NVL(p_b, 0)'
167 ,p_denom=>'NVL(p_e,0)'
168 ,p_measurecol=>'BIX_CALC_ITEM23'
169 ,p_totalcol=>'BIX_CALC_ITEM24'
170 )
171 ||','
172
173 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
174 p_percentage=>'Y'
175 ,p_num=>'NVL(c_d, 0)'
176 ,p_denom=>'NVL(c_e,0)'
177 ,p_pnum=>'NVL(p_d, 0)'
178 ,p_pdenom=>'NVL(p_e,0)'
179 ,p_measurecol=>'BIX_PMV_AI_TRANRATE_CP'
180 ,p_totalcol=>'BIX_PMV_TOTAL5'
181 ,p_changecol=>'BIX_PMV_AI_TRANRATE_CG'
182 ,p_changetotalcol=>'BIX_PMV_TOTAL6'
183 )
184 ||',' /* KPI - Transfer Rate */
185 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
186 p_percentage=>'Y'
187 ,p_num=>'NVL(c_d, 0)'
188 ,p_denom=>'NVL(c_e,0)'
189 ,p_measurecol=>'BIX_CALC_ITEM25'
190 ,p_totalcol=>'BIX_CALC_ITEM26'
191 )
192 ||','
193 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
194 p_percentage=>'Y'
195 ,p_num=>'NVL(p_d, 0)'
196 ,p_denom=>'NVL(p_e,0)'
197 ,p_measurecol=>'BIX_CALC_ITEM27'
198 ,p_totalcol=>'BIX_CALC_ITEM28'
199 )
200 ||','
201 /* Average Talk KPI */
202 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
203 p_percentage=>'N'
204 ,p_num=>'NVL(c_h, 0)'
205 ,p_denom=>'NVL(c_f,0)'
206 ,p_measurecol=>'BIX_CALC_ITEM1'
207 ,p_totalcol=>'BIX_CALC_ITEM2'
208 )
209 ||','
210 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
211 p_percentage=>'N'
212 ,p_num=>'NVL(p_h, 0)'
213 ,p_denom=>'NVL(p_f,0)'
214 ,p_measurecol=>'BIX_CALC_ITEM3'
215 ,p_totalcol=>'BIX_CALC_ITEM4'
216 )
217 ||','
218 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
219 p_percentage=>'N'
220 ,p_num=>'NVL(c_h, 0)'
221 ,p_denom=>'NVL(c_f,0)'
222 ,p_pnum=>'NVL(p_h, 0)'
223 ,p_pdenom=>'NVL(p_f,0)'
224 ,p_measurecol=>'BIX_PMV_AI_AVGTALK_CP'
225 ,p_totalcol=>'BIX_PMV_TOTAL9'
226 ,p_changecol=>'BIX_PMV_AI_AVGTALK_CG'
227 ,p_changetotalcol=>'BIX_PMV_TOTAL10'
228 )
229 ||','
230 ||bix_pmv_dbi_utl_pkg.get_divided_measure(
231 p_percentage=>'N'
232 ,p_num=>'NVL(c_g, 0)'
233 ,p_denom=>'NVL(c_f,0)'
234 ,p_pnum=>'NVL(p_g, 0)'
235 ,p_pdenom=>'NVL(p_f,0)'
236 ,p_measurecol=>'BIX_PMV_AI_AVGWRAP_CP'
237 ,p_totalcol=>'BIX_PMV_TOTAL11'
238 ,p_changecol=>'BIX_PMV_AI_AVGWRAP_CG'
239 ,p_changetotalcol=>'BIX_PMV_TOTAL12'
240 )
241
242 ||' FROM ( (';
243 RETURN l_measure_txt;
244 EXCEPTION
245 WHEN OTHERS THEN
246 -- insert into bix_debug values('error in get measures',9876);
247 RAISE;
248 END GET_MEASURES;
249
250
251
252 PROCEDURE GET_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
253 p_sql_text OUT NOCOPY VARCHAR2,
254 p_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
255 )
256 AS
257 l_sqltext VARCHAR2(32000) ;
258 l_sqltext_cont VARCHAR2(32000) ;
259 l_where_clause VARCHAR2(1000) ;
260 l_mv VARCHAR2 (240);
261 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
262 l_col_tbl_cont poa_dbi_util_pkg.poa_dbi_col_tbl;
263 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
264 l_filter_where VARCHAR2 (2000);
265 l_func_area varchar2(5) := 'ICSTR';
266 l_mv_set varchar2(3) := 'ITM';
267 l_version varchar2(3) := NULL;
268 l_timetype varchar2(3) := 'XTD';
269 l_view_by_select VARCHAR2 (500);
270 l_comp_type VARCHAR2 (500);
271 l_xtd VARCHAR2 (500);
272 l_view_by VARCHAR2 (120);
273
274
275 BEGIN
276 /* Initialize the variables */
277 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
278
279 /* Trial for Util Package..*/
280
281 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
282 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
283 l_col_tbl_cont := poa_dbi_util_pkg.poa_dbi_col_tbl ();
284
285 bix_pmv_dbi_utl_pkg.process_parameters
286 ( p_param => p_page_parameter_tbl
287 , p_trend => 'N'
288 , p_func_area => l_func_area
289 , p_version => l_version
290 , p_mv_set => l_mv_set
291 , p_where_clause => l_where_clause
292 , p_mv => l_mv
293 , p_join_tbl => l_join_tbl
294 , p_comp_type => l_comp_type
295 , p_xtd => l_xtd
296 , p_view_by_select => l_view_by_select
297 , p_view_by => l_view_by
298 );
299 -- Populate col table with columns for continued measures
300 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl_cont
301 , p_col_name => 'NULL'
302 , p_grand_total => 'N'
303 , p_alias_name => 'a'
304 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
305 , p_to_date_type => l_timetype
306 );
307 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
308 , p_col_name => 'NULL'
309 , p_grand_total => 'N'
310 , p_alias_name => 'b'
311 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
312 , p_to_date_type => l_timetype
313 );
314 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
315 , p_col_name => '(CASE WHEN media_item_type in (''TELE_INB'',''TELE_DIRECT'') THEN call_cont_calls_offered_na END)'
316 , p_grand_total => 'N'
317 , p_alias_name => 'c'
318 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
319 , p_to_date_type => l_timetype
320 );
321 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
322 , p_col_name => 'NULL'
323 , p_grand_total => 'N'
324 , p_alias_name => 'd'
325 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
326 , p_to_date_type => l_timetype
327 );
328 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
329 , p_col_name => '(CASE WHEN media_item_type in (''TELE_INB'',''TELE_DIRECT'') THEN call_cont_calls_handled_tot_na END)'
330 , p_grand_total => 'N'
331 , p_alias_name => 'e'
332 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
333 , p_to_date_type => l_timetype
334 );
335 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
336 , p_col_name => 'call_cont_calls_handled_tot_na'
337 , p_grand_total => 'N'
338 , p_alias_name => 'f'
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 => 'g'
346 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
347 , p_to_date_type => l_timetype
348 );
349 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
350 , p_col_name => 'NULL'
351 , p_grand_total => 'N'
352 , p_alias_name => 'h'
353 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
354 , p_to_date_type => l_timetype
355 );
356 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
357 , p_col_name => 'NULL'
358 , p_grand_total => 'N'
359 , p_alias_name => 'i'
360 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
361 , p_to_date_type => l_timetype
362 );
363 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
364 , p_col_name => 'NULL'
365 , p_grand_total => 'N'
366 , p_alias_name => 'j'
367 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
368 , p_to_date_type => l_timetype
369 );
370 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl_cont
371 , p_col_name => 'NULL'
372 , p_grand_total => 'N'
373 , p_alias_name => 'n'
374 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
375 , p_to_date_type => l_timetype
376 );
377 l_sqltext_cont :=
378 bix_pmv_dbi_utl_pkg.status_sql_daylevel (
379 p_fact_name => l_mv
380 , p_row_type_where_clause => l_where_clause --this shud come from util package
381 , p_col_name => l_col_tbl_cont
382 , p_join_tables => l_join_tbl
383 , p_time_type => 'ESD'
384 , p_union => 'ALL');
385
386 -- Populate col table with regular columns
387
388 poa_dbi_util_pkg.add_column(p_col_tbl => l_col_tbl
389 , p_col_name => '(CASE WHEN media_item_type in (''TELE_INB'',''TELE_DIRECT'') THEN agent_calls_answered_by_goal END)'
390 , p_grand_total => 'N'
391 , p_alias_name => 'a'
392 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
393 , p_to_date_type => l_timetype
394 );
395 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
396 , p_col_name => '(CASE WHEN media_item_type in (''TELE_INB'',''TELE_DIRECT'') THEN call_tot_queue_to_answer END)'
397 , p_grand_total => 'N'
398 , p_alias_name => 'b'
399 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
400 , p_to_date_type => l_timetype
401 );
402 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
403 , p_col_name => '(CASE WHEN media_item_type in (''TELE_INB'',''TELE_DIRECT'') THEN call_calls_offered_total END)'
404 , p_grand_total => 'N'
405 , p_alias_name => 'c'
406 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
407 , p_to_date_type => l_timetype
408 );
409 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
410 , p_col_name => '(CASE WHEN media_item_type in (''TELE_INB'',''TELE_DIRECT'') THEN call_calls_transferred END)'
411 , p_grand_total => 'N'
412 , p_alias_name => 'd'
413 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
414 , p_to_date_type => l_timetype
415 );
416 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
417 , p_col_name => '(CASE WHEN media_item_type in (''TELE_INB'',''TELE_DIRECT'') THEN call_calls_handled_total END)'
418 , p_grand_total => 'N'
419 , p_alias_name => 'e'
420 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
421 , p_to_date_type => l_timetype
422 );
423 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
424 , p_col_name => 'call_calls_handled_total'
425 , p_grand_total => 'N'
426 , p_alias_name => 'f'
427 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
428 , p_to_date_type => l_timetype
429 );
430 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
431 , p_col_name => 'agent_wrap_time_nac'
432 , p_grand_total => 'N'
433 , p_alias_name => 'g'
434 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
435 , p_to_date_type => l_timetype
436 );
437 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
438 , p_col_name => 'call_talk_time'
439 , p_grand_total => 'N'
440 , p_alias_name => 'h'
441 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
442 , p_to_date_type => l_timetype
443 );
444 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
445 , p_col_name => 'agent_sr_created'
446 , p_grand_total => 'N'
447 , p_alias_name => 'i'
448 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
449 , p_to_date_type => l_timetype
450 );
451 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
452 , p_col_name => 'agent_leads_created'
453 , p_grand_total => 'N'
454 , p_alias_name => 'j'
455 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
456 , p_to_date_type => l_timetype
457 );
458 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
459 , p_col_name => 'agent_opportunities_created'
460 , p_grand_total => 'N'
461 , p_alias_name => 'n'
462 , p_prior_code => poa_dbi_util_pkg.COL_PRIOR_ONLY
463 , p_to_date_type => l_timetype
464 );
465
466 l_sqltext :='select * from ('||
467 get_measures || l_sqltext_cont ||
468 poa_dbi_template_pkg.status_sql (
469 p_fact_name => l_mv
470 , p_where_clause => l_where_clause
471 , p_filter_where => l_filter_where
472 , p_join_tables => l_join_tbl
473 , p_use_windowing => 'N'
474 , p_col_name => l_col_tbl
475 , p_use_grpid => 'N'
476 , p_paren_count => 3
477 , p_generate_viewby => 'Y')
478 || get_zeronull_clause;
479
480
481 p_sql_text:=l_sqltext;
482 bix_pmv_dbi_utl_pkg.get_bind_vars (p_custom_output,p_func_area => l_func_area);
483
484
485 /* End of Trial for Util Package */
486
487
488 /*The original query
489 l_sqltext :=
490 'SELECT nvl(party.party_name,:l_unknown) BIX_PMV_AI_CUSTOMER
491 ,ROUND(SUM(NVL(a, 0)) / DECODE(SUM(NVL(e,0)+NVL(y,0)), 0, NULL, SUM(NVL(e,0)+NVL(y,0))) * 100, 1)
492 BIX_PMV_AI_SL_CP
493 ,ROUND(SUM(SUM(NVL(a, 0))) OVER() / DECODE(SUM(SUM(NVL(e,0)+NVL(y,0))) OVER(), 0, NULL,
494 SUM(SUM(NVL(e,0)+NVL(y,0))) OVER()) * 100, 1)
495 BIX_PMV_TOTAL1
496 ,ROUND((SUM(NVL(a, 0)) / DECODE(SUM(NVL(e,0)+NVL(y,0)), 0, NULL, SUM(NVL(e,0)+NVL(y,0))) * 100) -
497 (SUM(NVL(b, 0)) / DECODE(SUM(NVL(f,0)+NVL(z,0)), 0, NULL, SUM(NVL(f,0)+NVL(z,0))) * 100), 1)
498 BIX_PMV_AI_SL_CG
499 ,ROUND(SUM(SUM(NVL(a, 0))) OVER() / DECODE(SUM(SUM(NVL(e,0)+NVL(y,0))) OVER(), 0, NULL,
500 SUM(SUM(NVL(e,0)+NVL(y,0))) OVER()) * 100, 1) -
501 ROUND(SUM(SUM(NVL(b, 0))) OVER() / DECODE(SUM(SUM(NVL(f,0)+NVL(z,0))) OVER(), 0, NULL,
502 SUM(SUM(NVL(f,0)+NVL(z,0))) OVER()) * 100, 1)
503 BIX_PMV_TOTAL2
504 ,ROUND(SUM(NVL(c, 0)) / DECODE(SUM(NVL(i,0)+NVL(w,0)), 0, NULL, SUM(NVL(i,0)+NVL(w,0))), 1)
505 BIX_PMV_AI_SPANS_CP
506 ,ROUND(SUM(SUM(NVL(c, 0))) OVER() / DECODE(SUM(SUM(NVL(i,0)+NVL(w,0))) OVER(), 0, NULL,
507 SUM(SUM(NVL(i,0)+NVL(w,0))) OVER()), 1)
508 BIX_PMV_TOTAL3
509 ,((
510 (SUM(NVL(c, 0)) / DECODE(SUM(NVL(i,0)+NVL(w,0)), 0, NULL, SUM(NVL(i,0)+NVL(w,0)))) -
511 (SUM(NVL(d, 0)) / DECODE(SUM(NVL(j,0)+NVL(x,0)), 0, NULL, SUM(NVL(j,0)+NVL(x,0))))) /
512 DECODE(SUM(NVL(d, 0)) / DECODE(SUM(NVL(j,0)+NVL(x,0)), 0, NULL, SUM(NVL(j,0)+NVL(x,0))), 0, NULL,
513 SUM(NVL(d, 0)) / DECODE(SUM(NVL(j,0)+NVL(x,0)), 0, NULL, SUM(NVL(j,0)+NVL(x,0))))) * 100
514 BIX_PMV_AI_SPANS_CG
515 ,ROUND((((SUM(SUM(NVL(c, 0))) OVER() /
516 DECODE(SUM(SUM(NVL(i,0)+NVL(w,0))) OVER(), 0, NULL, SUM(SUM(NVL(i,0)+NVL(w,0))) OVER())) -
517 (SUM(SUM(NVL(d, 0))) OVER() /
518 DECODE(SUM(SUM(NVL(j,0)+NVL(x,0))) OVER(), 0, NULL, SUM(SUM(NVL(j,0)+NVL(x,0))) OVER()))) /
519 DECODE(SUM(SUM(NVL(d, 0))) OVER() /
520 DECODE(SUM(SUM(NVL(j,0)+NVL(x,0))) OVER(), 0, NULL, SUM(SUM(NVL(j,0)+NVL(x,0))) OVER()), 0, NULL,
521 SUM(SUM(NVL(d, 0))) OVER() /
522 DECODE(SUM(SUM(NVL(j,0)+NVL(x,0))) OVER(), 0, NULL, SUM(SUM(NVL(j,0)+NVL(x,0))) OVER()))) * 100, 1)
523 BIX_PMV_TOTAL4
524 ,ROUND(SUM(NVL(g, 0)) / DECODE(SUM(i), 0, NULL, SUM(i)) * 100, 1)
525 BIX_PMV_AI_TRANRATE_CP
526 ,ROUND(SUM(SUM(NVL(g, 0))) OVER() / DECODE(SUM(SUM(i)) OVER(), 0, NULL, SUM(SUM(i)) OVER()) * 100, 1)
527 BIX_PMV_TOTAL5
528 ,ROUND((SUM(NVL(g, 0)) / DECODE(SUM(i), 0, NULL, SUM(i)) * 100) -
529 (SUM(NVL(h, 0)) / DECODE(SUM(j), 0, NULL, SUM(j)) * 100), 1)
530 BIX_PMV_AI_TRANRATE_CG
531 ,ROUND(SUM(SUM(NVL(g, 0))) OVER() / DECODE(SUM(SUM(i)) OVER(), 0, NULL, SUM(SUM(i)) OVER()) * 100 -
532 SUM(SUM(NVL(h, 0))) OVER() / DECODE(SUM(SUM(j)) OVER(), 0, NULL, SUM(SUM(j)) OVER()) * 100, 1)
533 BIX_PMV_TOTAL6
534 ,SUM(NVL(i,0))
535 BIX_PMV_AI_INCALLHAND_CP
536 ,SUM(SUM(NVL(i,0))) OVER()
537 BIX_PMV_TOTAL7
538 ,ROUND(((SUM(NVL(i,0)) - SUM(NVL(j,0))) / DECODE(SUM(NVL(j,0)), 0, NULL, SUM(NVL(j,0)))) * 100, 1)
539 BIX_PMV_AI_INCALLHAND_CG
540 ,ROUND(((SUM(SUM(NVL(i,0))) OVER() - SUM(SUM(NVL(j,0))) OVER()) /
541 DECODE(SUM(SUM(NVL(j,0))) OVER(), 0, NULL, SUM(SUM(NVL(j,0))) OVER())) * 100, 1)
542 BIX_PMV_TOTAL8
543 ,ROUND(SUM(NVL(o, 0)) / DECODE(SUM(NVL(k,0)+NVL(u,0)), 0, NULL, SUM(NVL(k,0)+NVL(u,0))), 1)
544 BIX_PMV_AI_AVGTALK_CP
545 ,ROUND(SUM(SUM(NVL(o, 0))) OVER() / DECODE(SUM(SUM(NVL(k,0)+NVL(u,0))) OVER(), 0, NULL,
546 SUM(SUM(NVL(k,0)+NVL(u,0))) OVER()), 1)
547 BIX_PMV_TOTAL9
548 ,ROUND((((SUM(NVL(o, 0)) / DECODE(SUM(NVL(k,0)+NVL(u,0)), 0, NULL, SUM(NVL(k,0)+NVL(u,0)))) -
549 (SUM(NVL(p, 0)) / DECODE(SUM(NVL(l,0)+NVL(v,0)), 0, NULL, SUM(NVL(l,0)+NVL(v,0))))) /
550 DECODE(SUM(NVL(p, 0)) / DECODE(SUM(NVL(l,0)+NVL(v,0)), 0, NULL, SUM(NVL(l,0)+NVL(v,0))), 0, NULL,
551 SUM(NVL(p, 0)) / DECODE(SUM(NVL(l,0)+NVL(v,0)), 0, NULL, SUM(NVL(l,0)+NVL(v,0))))) * 100, 1)
552 BIX_PMV_AI_AVGTALK_CG
553 ,ROUND((((SUM(SUM(NVL(o, 0))) OVER() /
554 DECODE(SUM(SUM(NVL(k,0)+NVL(u,0))) OVER(), 0, NULL, SUM(SUM(NVL(k,0)+NVL(u,0))) OVER())) -
555 (SUM(SUM(NVL(p, 0))) OVER() /
556 DECODE(SUM(SUM(NVL(l,0)+NVL(v,0))) OVER(), 0, NULL, SUM(SUM(NVL(l,0)+NVL(v,0))) OVER()))) /
557 DECODE(SUM(SUM(NVL(p, 0))) OVER() /
558 DECODE(SUM(SUM(NVL(l,0)+NVL(v,0))) OVER(), 0, NULL, SUM(SUM(NVL(l,0)+NVL(v,0))) OVER()), 0, NULL,
559 SUM(SUM(NVL(p, 0))) OVER() /
560 DECODE(SUM(SUM(NVL(l,0)+NVL(v,0))) OVER(), 0, NULL, SUM(SUM(NVL(l,0)+NVL(v,0))) OVER()))) * 100, 1)
561 BIX_PMV_TOTAL10
562 ,ROUND(SUM(NVL(m, 0)) / DECODE(SUM(NVL(k,0)+NVL(u,0)), 0, NULL, SUM(NVL(k,0)+NVL(u,0))), 1)
563 BIX_PMV_AI_AVGWRAP_CP
564 ,ROUND(SUM(SUM(NVL(m, 0))) OVER() /
565 DECODE(SUM(SUM(NVL(k,0)+NVL(u,0))) OVER(), 0, NULL, SUM(SUM(NVL(k,0)+NVL(u,0))) OVER()) ,1)
566 BIX_PMV_TOTAL11
567 ,ROUND((((SUM(NVL(m, 0)) / DECODE(SUM(NVL(k,0)+NVL(u,0)), 0, NULL, SUM(NVL(k,0)+NVL(u,0)))) -
568 (SUM(NVL(n, 0)) / DECODE(SUM(NVL(l,0)+NVL(v,0)), 0, NULL, SUM(NVL(l,0)+NVL(v,0))))) /
569 DECODE(SUM(NVL(n, 0)) / DECODE(SUM(NVL(l,0)+NVL(v,0)), 0, NULL, SUM(NVL(l,0)+NVL(v,0))), 0, NULL,
570 SUM(NVL(n, 0)) / DECODE(SUM(NVL(l,0)+NVL(v,0)), 0, NULL, SUM(NVL(l,0)+NVL(v,0))))) * 100, 1)
571 BIX_PMV_AI_AVGWRAP_CG
572 ,ROUND((((SUM(SUM(NVL(m, 0))) OVER() /
573 DECODE(SUM(SUM(NVL(k,0)+NVL(u,0))) OVER(), 0, NULL, SUM(SUM(NVL(k,0)+NVL(u,0))) OVER())) -
574 (SUM(SUM(NVL(n, 0))) OVER() /
575 DECODE(SUM(SUM(NVL(l,0)+NVL(v,0))) OVER(), 0, NULL, SUM(SUM(NVL(l,0)+NVL(v,0))) OVER()))) /
576 DECODE(SUM(SUM(NVL(n, 0))) OVER() /
577 DECODE(SUM(SUM(NVL(l,0)+NVL(v,0))) OVER(), 0, NULL, SUM(SUM(NVL(l,0)+NVL(v,0))) OVER()), 0, NULL,
578 SUM(SUM(NVL(n, 0))) OVER() /
579 DECODE(SUM(SUM(NVL(l,0)+NVL(v,0))) OVER(), 0, NULL, SUM(SUM(NVL(l,0)+NVL(v,0))) OVER()))) * 100, 1)
580 BIX_PMV_TOTAL12
581 ,SUM(NVL(q,0))
582 BIX_PMV_AI_SRCR_CP
583 ,SUM(SUM(NVL(q,0))) OVER()
584 BIX_PMV_TOTAL13
585 ,ROUND(((SUM(NVL(q,0)) - SUM(NVL(r,0))) / DECODE(SUM(NVL(r,0)), 0, NULL, SUM(NVL(r,0)))) * 100, 1)
586 BIX_PMV_AI_SRCR_CG
587 ,ROUND((((SUM(SUM(NVL(q,0))) OVER()) - (SUM(SUM(NVL(r,0))) OVER())) /
588 DECODE(SUM(SUM(NVL(r,0))) OVER(), 0, NULL, SUM(SUM(NVL(r,0))) OVER())) * 100, 1)
589 BIX_PMV_TOTAL14
590 ,SUM(NVL(s,0))
591 BIX_PMV_AI_LECR_CP
592 ,SUM(SUM(NVL(s,0))) OVER()
593 BIX_PMV_TOTAL15
594 ,ROUND(((SUM(NVL(s,0)) - SUM(NVL(t,0))) / DECODE(SUM(NVL(t,0)), 0, NULL, SUM(NVL(t,0)))) * 100, 1)
595 BIX_PMV_AI_LECR_CG
596 ,ROUND((((SUM(SUM(NVL(s,0))) OVER()) - (SUM(SUM(NVL(t,0))) OVER())) /
597 DECODE(SUM(SUM(NVL(t,0))) OVER(), 0, NULL, SUM(SUM(NVL(t,0))) OVER())) * 100, 1)
598 BIX_PMV_TOTAL16
599 ,SUM(NVL(s1,0))
600 BIX_PMV_AI_OPCR_CP
601 ,SUM(SUM(NVL(s1,0))) OVER()
602 BIX_PMV_TOTAL19
603 ,ROUND(((SUM(NVL(s1,0)) - SUM(NVL(t1,0))) / DECODE(SUM(NVL(t1,0)), 0, NULL, SUM(NVL(t1,0)))) * 100, 1)
604 BIX_PMV_AI_OPCR_CG
605 ,ROUND((((SUM(SUM(NVL(s1,0))) OVER()) - (SUM(SUM(NVL(t1,0))) OVER())) /
606 DECODE(SUM(SUM(NVL(t1,0))) OVER(), 0, NULL, SUM(SUM(NVL(t1,0))) OVER())) * 100, 1)
607 BIX_PMV_TOTAL20
608 ,SUM(NVL(k,0))
609 BIX_PMV_AI_CNCT_CP
610 ,SUM(SUM(NVL(k,0))) OVER()
611 BIX_PMV_TOTAL17
612 ,ROUND(((SUM(NVL(k,0)) - SUM(NVL(l,0))) / DECODE(SUM(NVL(l,0)), 0, NULL, SUM(NVL(l,0)))) * 100, 1)
613 BIX_PMV_AI_CNCT_CG
614 ,ROUND((((SUM(SUM(NVL(k,0))) OVER()) - (SUM(SUM(NVL(l,0))) OVER())) /
615 DECODE(SUM(SUM(NVL(l,0))) OVER(), 0, NULL, SUM(SUM(NVL(l,0))) OVER())) * 100, 1)
616 BIX_PMV_TOTAL18
617 FROM
618 (
619 SELECT
620 party_id party_id
621 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',agent_calls_answered_by_goal,
622 ''TELE_DIRECT'',agent_calls_answered_by_goal, 0)))
623 a
624 ,SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',agent_calls_answered_by_goal,
625 ''TELE_DIRECT'',agent_calls_answered_by_goal, 0)))
626 b
627 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_tot_queue_to_answer,
628 ''TELE_DIRECT'',call_tot_queue_to_answer, 0)))
629 c
630 ,SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_tot_queue_to_answer,
631 ''TELE_DIRECT'',call_tot_queue_to_answer, 0)))
632 d
633 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_calls_offered_total,
634 ''TELE_DIRECT'',call_calls_offered_total, 0)))
635 e
636 ,SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_calls_offered_total,
637 ''TELE_DIRECT'',call_calls_offered_total, 0)))
638 f
639 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_calls_transferred,
640 ''TELE_DIRECT'',call_calls_transferred, 0)))
641 g
642 ,SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_calls_transferred,
643 ''TELE_DIRECT'',call_calls_transferred, 0)))
644 h
645 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'', call_calls_handled_total,
646 ''TELE_DIRECT'', call_calls_handled_total, 0)))
647 i
648 ,SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'', call_calls_handled_total,
649 ''TELE_DIRECT'', call_calls_handled_total, 0)))
650 j
651 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,call_calls_handled_total))
652 k
653 ,SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,call_calls_handled_total))
654 l
655 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_wrap_time_nac))
656 m
657 ,SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_wrap_time_nac))
658 n
659 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,call_talk_time))
660 o
661 ,SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,call_talk_time))
662 p
663 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_sr_created))
664 q
665 ,SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_sr_created))
666 r
667 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_leads_created))
668 s
669 ,SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_leads_created))
670 t
671 ,NULL u
672 ,NULL v
673 ,NULL w
674 ,NULL x
675 ,NULL y
676 ,NULL z
677 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_opportunities_created))
678 s1
679 ,SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_opportunities_created))
680 t1
681 FROM
682 bix_ai_call_details_mv a,
683 fii_time_rpt_struct calendar
684 WHERE a.row_type = ''CDPR''
685 AND a.time_id = calendar.time_id
686 AND a.period_type_id = calendar.period_type_id
687 AND calendar.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
688 AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) = calendar.record_type_id ';
689
690 l_sqltext := l_sqltext || l_where_clause || '
691 GROUP BY a.party_id
692 UNION ALL
693 SELECT
694 party_id
695 party_id
696 ,NULL a
697 ,NULL b
698 ,NULL c
699 ,NULL d
700 ,NULL e
701 ,NULL f
702 ,NULL g
703 ,NULL h
704 ,NULL i
705 ,NULL j
706 ,NULL k
707 ,NULL l
708 ,NULL m
709 ,NULL n
710 ,NULL o
711 ,NULL p
712 ,NULL q
713 ,NULL r
714 ,NULL s
715 ,NULL t
716 ,SUM(DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),call_cont_calls_handled_tot_na))
717 u
718 ,SUM(DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')),call_cont_calls_handled_tot_na))
719 v
720 ,SUM(DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),DECODE(media_item_type, ''TELE_INB'',
721 call_cont_calls_handled_tot_na, ''TELE_DIRECT'', call_cont_calls_handled_tot_na, 0)))
722 w
723 ,SUM(DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')),DECODE(media_item_type, ''TELE_INB'',
724 call_cont_calls_handled_tot_na, ''TELE_DIRECT'', call_cont_calls_handled_tot_na, 0)))
725 x
726 ,SUM(DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),DECODE(media_item_type, ''TELE_INB'',
727 call_cont_calls_offered_na, ''TELE_DIRECT'', call_cont_calls_offered_na, 0)))
728 y
729 ,SUM(DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')),DECODE(media_item_type, ''TELE_INB'',
730 call_cont_calls_offered_na, ''TELE_DIRECT'', call_cont_calls_offered_na, 0)))
731 z
732 ,NULL s1
733 ,NULL t1
734 FROM
735 bix_ai_call_details_mv
736 WHERE row_type = ''CDPR''
737 AND time_id IN (TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),
738 TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')))
739 AND period_type_id = 1 ';
740
741 l_sqltext := l_sqltext || l_where_clause || '
742 GROUP BY party_id
743
744 ) b, hz_parties party
745 WHERE b.party_id = party.party_id (+)
746 GROUP BY nvl(party.party_name,:l_unknown) &ORDER_BY_CLAUSE ';
747
748 */
749
750 EXCEPTION
751 WHEN OTHERS THEN
752 l_sqltext:=sqlerrm;
753 END GET_SQL;
754 END BIX_PMV_AI_CSTDTL_RPT_PKG;