[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AO_TELDTL_RPT_PKG
Source
1 PACKAGE BODY BIX_PMV_AO_TELDTL_RPT_PKG AS
2 /*$Header: bixotelr.plb 115.20 2004/05/04 11:11:23 pubalasu noship $ */
3
4
5 PROCEDURE GET_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
6 p_sql_text OUT NOCOPY VARCHAR2,
7 p_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
8 )
9 AS
10 l_sqltext VARCHAR2(32000) ;
11 l_where_clause VARCHAR2(1000) ;
12
13 l_call_center VARCHAR2(3000);
14 l_view_by VARCHAR2(3000);
15 l_unknown VARCHAR2(3000);
16 l_column_name VARCHAR2(1000);
17
18 l_custom_rec BIS_QUERY_ATTRIBUTES;
19 --added for campaign, schedule and source code
20 l_campaign_id varchar2(3000);
21 l_schedule_id varchar2(3000);
22 l_source_code_id varchar2(3000);
23 l_campaign_where_clause VARCHAR2(3000);
24 l_call_where_clause VARCHAR2(3000);
25 l_schedule_where_clause VARCHAR2(3000);
26 l_source_code_where_clause VARCHAR2(3000);
27 l_agent_group varchar2(3000);
28 l_sess_source_where_clause VARCHAR2(3000);
29 l_as_of_date DATE;
30 l_period_type varchar2(2000);
31 l_comp_type varchar2(2000);
32 l_record_type_id NUMBER;
33
34 BEGIN
35 /* Initialize p_custom_output and l_custom_rec */
36 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
37 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
38
39 l_where_clause := NULL;
40 l_call_center := NULL;
41 l_view_by := NULL;
42 l_column_name := NULL;
43
44 -- Get the parameters
45
46 BIX_PMV_DBI_UTL_PKG.get_ao_page_params( p_page_parameter_tbl,
47 l_as_of_date,
48 l_period_type,
49 l_record_type_id,
50 l_comp_type,
51 l_call_center,
52 l_campaign_id,
53 l_schedule_id,
54 l_source_code_id,
55 l_agent_group,
56 l_view_by
57 );
58
59
60 IF l_call_center IS NOT NULL THEN
61 l_call_where_clause := ' AND server_group_id IN (:l_call_center) ';
62 END IF;
63
64 IF l_campaign_id IS NOT NULL THEN
65 l_campaign_where_clause := ' AND mv.campaign_id IN (:l_campaign_id) ';
66 END IF;
67 IF l_schedule_id IS NOT NULL THEN
68 l_schedule_where_clause := ' AND mv.schedule_id IN (:l_schedule_id) ';
69 END IF;
70 IF l_source_code_id IS NOT NULL THEN
71 l_source_code_where_clause := ' AND campaign_id in (select source_code_for_id from
72 ams_source_codes where source_code_id IN (:l_source_code_id) and arc_source_code_for=''CAMP'' and active_flag=''Y'') ';
73
74 END IF;
75
76 l_where_clause:=l_call_where_clause||l_source_code_where_clause;
77
78 l_unknown := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_UNKNOWN');
79
80 IF l_unknown IS NULL OR l_unknown = 'BIX_PMV_UNKNOWN'
81 THEN
82 l_unknown := 'Unknown';
83 END IF;
84
85
86 IF l_view_by = 'CAMPAIGN+CAMPAIGN' THEN
87
88 l_sqltext:=' select VIEWBY,VIEWBYID,ROUND(i / g1 * 100, 1)
89 BIX_PMV_AO_ABANRATE_CP
90 ,ROUND(SUM(i) OVER() / SUM(g1) OVER() * 100, 1)
91 BIX_PMV_TOTAL1
92 ,ROUND((i / g1 * 100) - (j / h1 * 100), 1)
93 BIX_PMV_AO_ABANRATE_CG
94 ,ROUND(SUM(i) OVER() / SUM(g1) OVER() * 100 - SUM(j) OVER() / SUM(h1) OVER() * 100, 1)
95 BIX_PMV_TOTAL2
96 ,ROUND(j / h1 * 100, 1)
97 BIX_PMV_AO_ABANRATE_PP
98 /* Added for US Abandon Rate */
99 ,ROUND(ius / g1 * 100, 1)
100 BIX_PMV_AO_US_ABANRATE_CP
101 ,ROUND(SUM(ius) OVER() / SUM(g1) OVER() * 100, 1)
102 BIX_PMV_TOTAL25
103 ,ROUND((ius / g1 * 100) - (jus / h1 * 100), 1)
104 BIX_PMV_AO_US_ABANRATE_CG
105 ,ROUND(SUM(ius) OVER() / SUM(g1) OVER() * 100 - SUM(jus) OVER() / SUM(h1) OVER() * 100, 1)
106 BIX_PMV_TOTAL26
107 ,ROUND(jus / h1 * 100, 1)
108 BIX_PMV_AO_US_ABANRATE_PP
109 /* End of Additions */
110 ,nvl(m,0) BIX_PMV_AO_OUTCALLHAND_CP
111 ,nvl(SUM(m) OVER() ,0)
112 BIX_PMV_TOTAL3
113 ,ROUND((m - n) / DECODE(n, 0, NULL, n) * 100, 1)
114 BIX_PMV_AO_OUTCALLHAND_CG
115 ,ROUND((SUM(m) OVER() - SUM(n) OVER()) / DECODE(SUM(n) OVER(), 0, NULL, SUM(n) OVER()) * 100, 1)
116 BIX_PMV_TOTAL4
117 ,nvl(n,0) BIX_PMV_AO_OUTCALLHAND_PP
118 ,ROUND(u / q, 1) BIX_PMV_AO_AVGTALK_CP
119 ,ROUND(SUM(u) OVER() / SUM(q) OVER(), 1)
120 BIX_PMV_TOTAL5
121 ,ROUND(((u / q) - (v / r)) / DECODE(v / r, 0, NULL, v / r) * 100 , 1)
122 BIX_PMV_AO_AVGTALK_CG
123 ,ROUND((SUM(u) OVER() / SUM(q) OVER() - SUM(v) OVER() / SUM(r) OVER()) /
124 DECODE(SUM(v) OVER() / SUM(r) OVER(), 0, NULL, SUM(v) OVER() / SUM(r) OVER()) * 100 , 1)
125 BIX_PMV_TOTAL6
126 ,ROUND(v / r, 1) BIX_PMV_AO_AVGTALK_PP
127 ,ROUND(s / q, 1) BIX_PMV_AO_AVGWRAP_CP
128 ,ROUND(SUM(s) OVER() / SUM(q) OVER(), 1)
129 BIX_PMV_TOTAL7
130 ,ROUND(((s / q) - (t / r)) / DECODE(t / r, 0, NULL, t / r) * 100, 1)
131 BIX_PMV_AO_AVGWRAP_CG
132 ,ROUND((SUM(s) OVER() / SUM(q) OVER() - SUM(t) OVER() / SUM(r) OVER()) /
133 DECODE(SUM(t) OVER() / SUM(r) OVER(), 0, NULL, SUM(t) OVER() / SUM(r) OVER()) * 100, 1)
134 BIX_PMV_TOTAL8
135 ,w BIX_PMV_AO_SRCR_CP
136 ,SUM(w) OVER() BIX_PMV_TOTAL9
137 ,ROUND((w - x) / DECODE(x, 0, NULL, x) * 100, 1)
138 BIX_PMV_AO_SRCR_CG
139 ,ROUND((SUM(w) OVER() - SUM(x) OVER()) / DECODE(SUM(x) OVER(), 0, NULL, SUM(x) OVER()) * 100, 1)
140 BIX_PMV_TOTAL10
141 ,y BIX_PMV_AO_LECR_CP
142 ,SUM(y) OVER() BIX_PMV_TOTAL11
143 ,ROUND((y - z) / DECODE(z, 0, NULL, z) * 100, 1)
144 BIX_PMV_AO_LECR_CG
145 ,ROUND((SUM(y) OVER() - SUM(z) OVER()) / DECODE(SUM(z) OVER(), 0, NULL, SUM(z) OVER()) * 100, 1)
146 BIX_PMV_TOTAL12
147 ,y1 BIX_PMV_AO_OPCR_CP
148 ,SUM(y1) OVER() BIX_PMV_TOTAL13
149 ,ROUND((y1 - z1) / DECODE(z1, 0, NULL, z1) * 100, 1)
150 BIX_PMV_AO_OPCR_CG
151 ,ROUND((SUM(y1) OVER() - SUM(z1) OVER()) / DECODE(SUM(z1) OVER(), 0, NULL, SUM(z1) OVER()) * 100, 1)
152 BIX_PMV_TOTAL14
153 ,a1 BIX_PMV_AO_CUST_CP
154 ,a9 BIX_PMV_TOTAL15
155 ,ROUND((a1 - a2) / DECODE(a2, 0, null, a2) * 100, 1)
156 BIX_PMV_AO_CUST_CG
157 ,ROUND((a9 - a10) / DECODE(a10, 0, NULL, a10) * 100, 1)
158 BIX_PMV_TOTAL16
159 ,ROUND(
160 (NVL(loginc,0) - NVL(idlec, 0)) /
161 DECODE(loginc, 0, NULL, loginc) * 100, 1)
162 BIX_PMV_AO_AVAILRATE_CP
163 ,ROUND(
164 SUM(NVL(loginc,0) - NVL(idlec, 0)) OVER() /
165 DECODE(SUM(loginc) OVER(), 0, NULL, SUM(loginc) OVER() )* 100, 1)
166 BIX_PMV_TOTAL17
167 ,ROUND(
168 (NVL(loginc,0) - NVL(idlec, 0)) /
169 DECODE(loginc, 0, NULL, loginc) * 100
170 -
171 (NVL(loginp,0) - NVL(idlep, 0)) /
172 DECODE(loginp, 0, NULL, loginp) * 100
173 , 1)
174 BIX_PMV_AO_AVAILRATE_CG
175 ,ROUND(
176 sum(NVL(loginc,0) - NVL(idlec, 0)) over() /
177 DECODE(sum(loginc) over(), 0, NULL, sum(loginc) over() ) * 100
178 -
179 sum(NVL(loginp,0) - NVL(idlep, 0)) over() /
180 DECODE(sum(loginp) over(), 0, NULL, sum(loginp)over()) * 100
181 , 1)
182 BIX_PMV_TOTAL18
183 ,ROUND(
184 (NVL(loginc,0) - NVL(idlec, 0)-nvl(availc,0)) /
185 DECODE(loginc, 0, NULL, loginc) * 100, 1) BIX_PMV_AO_UTILRATE_CP
186 ,ROUND(
187 SUM(NVL(loginc,0) - NVL(idlec, 0)-nvl(availc,0)) OVER() /
188 DECODE(SUM(loginc) OVER(), 0, NULL, SUM(loginc) OVER() )* 100, 1)
189 BIX_PMV_TOTAL19
190 ,ROUND(
191 (NVL(loginc,0) - NVL(idlec, 0)-nvl(availc,0)) /
192 DECODE(loginc, 0, NULL, loginc) * 100
193 -
194 (NVL(loginp,0) - NVL(idlep, 0)-nvl(availp,0)) /
195 DECODE(loginp, 0, NULL, loginp) * 100
196 , 1)
197 BIX_PMV_AO_UTILRATE_CG
198 ,
199 ROUND(
200 sum(NVL(loginc,0) - NVL(idlec, 0)-nvl(availc,0)) over() /
201 DECODE(sum(loginc) over(), 0, NULL, sum(loginc) over() ) * 100
202 -
203 sum(NVL(loginp,0) - NVL(idlep, 0)-nvl(availp,0)) over() /
204 DECODE(sum(loginp) over(), 0, NULL, sum(loginp)over()) * 100
205 , 1) BIX_PMV_TOTAL20
206 ,nvl(prC,0) BIX_PMV_AO_PORESP_CP
207 ,sum(nvl(prc,0)) OVER() BIX_PMV_TOTAL21
208 /* ,nvl(PRC,0)-NVL(PRP,0) BIX_PMV_AO_PORESP_CG
209 ,SUM(nvl(PRC,0)-NVL(PRP,0)) OVER() BIX_PMV_TOTAL22
210 */
211 ,(nvl(PRC,0)-NVL(PRP,0))*100/decode(prp,0,null,prp) BIX_PMV_AO_PORESP_CG
212 ,sum(nvl(PRC,0)-NVL(PRP,0)) over()*100/sum(decode(prp,0,null,prp)) over() BIX_PMV_TOTAL22
213 ,nvl(PRp,0) BIX_PMV_AO_PORESP_PP
214 ,contc/(decode(loginc,0,null,loginc)/3600) BIX_PMV_AO_CONTPERHR_CP , sum(nvl(contc,0)) over() /(decode(loginc ,0,null,loginc)/3600) BIX_PMV_TOTAL23
215 , (contc/decode(loginc,0,NULL,loginc) -contp/decode(loginp,0,null,loginp))*100/decode(contp/decode(loginp,0,null,loginp),0,null,contp/decode(loginp,0,null,loginp)) BIX_PMV_AO_CONTPERHR_CG
216 ,(sum(contc) over()/decode(loginc,0,null,loginc) - sum(contp) over()/decode(loginp,0,null,loginp))*100/decode(sum(contp) over()/decode(loginp,0,null,loginp),0,null,sum(contp) over()/decode(loginp,0,null,loginp) ) BIX_PMV_TOTAL24
217 ,contp/(decode(loginp,0,null,loginp)/3600) BIX_PMV_AO_CONTPERHR_PP
218 FROM (
219 /* First level inline view */
220 SELECT decode(campmast.value,null, :l_unknown ,campmast.value) VIEWBY
221 ,nvl(campmast.id,-1) VIEWBYID
222 ,DECODE(SUM(c), 0, NULL, SUM(c)) c
223 ,DECODE(SUM(d), 0, NULL, SUM(d)) d
224 ,DECODE(SUM(g), 0, NULL, SUM(g)) g
225 ,DECODE(SUM(h), 0, NULL, SUM(h)) h
226 ,SUM(NVL(i,0)) i
227 ,SUM(NVL(j,0)) j
228 /* Added for US Abandonment rate */
229 ,SUM(NVL(ius,0)) ius
230 ,SUM(NVL(jus,0)) jus
231 /* End of addition */
232 ,SUM(NVL(k,0)) k
233 ,SUM(NVL(l,0)) l
234 ,DECODE(SUM(m), 0, NULL, SUM(m)) m
235 ,DECODE(SUM(n), 0, NULL, SUM(n)) n
236 ,SUM(NVL(s,0)) s
237 ,SUM(NVL(t,0)) t
238 ,SUM(NVL(u,0)) u
239 ,SUM(NVL(v,0)) v
240 ,SUM(NVL(w,0)) w
241 ,SUM(NVL(x,0)) x
242 ,SUM(NVL(y,0)) y
243 ,SUM(NVL(z,0)) z
244 ,SUM(NVL(y1,0)) y1
245 ,SUM(NVL(z1,0)) z1
246 ,sum(a1) a1
247 ,sum(a2) a2
248 ,MIN(a9) a9
249 ,MIN(a10) a10
250 ,SUM(q) q
251 ,sum ( r ) r
252 ,sum(g1) g1
253 ,sum(h1) h1 ,
254 min(sourcecode) sourcecode
255 ,min(loginp) loginp
256 ,min(loginc) loginc
257 ,min(idlep) idlep
258 ,min(idlec) idlec
259 ,min(availp) availp
260 ,min(availc ) availc
261 ,sum(nvl(prp,0) ) prp
262 ,sum(nvl(prc,0)) prc
263 ,sum(nvl(contp,0)) contp
264 ,sum(nvl(contc,0)) contc
265 FROM (
266 /* Added this for eliminating campaign id with -999 */
267 SELECT campaign_id
268 ,DECODE(SUM(c), 0, NULL, SUM(c)) c
269 ,DECODE(SUM(d), 0, NULL, SUM(d)) d
270 ,DECODE(SUM(g), 0, NULL, SUM(g)) g
271 ,DECODE(SUM(h), 0, NULL, SUM(h)) h
272 ,SUM(NVL(i,0)) i
273 ,SUM(NVL(j,0)) j
274 /* Added for US Abandonment rate */
275 ,SUM(NVL(ius,0)) ius
276 ,SUM(NVL(jus,0)) jus
277 /* End of addition */
278 ,SUM(NVL(k,0)) k
279 ,SUM(NVL(l,0)) l
280 ,DECODE(SUM(m), 0, NULL, SUM(m)) m
281 ,DECODE(SUM(n), 0, NULL, SUM(n)) n
282 ,SUM(NVL(s,0)) s
283 ,SUM(NVL(t,0)) t
284 ,SUM(NVL(u,0)) u
285 ,SUM(NVL(v,0)) v
286 ,SUM(NVL(w,0)) w
287 ,SUM(NVL(x,0)) x
288 ,SUM(NVL(y,0)) y
289 ,SUM(NVL(z,0)) z
290 ,SUM(NVL(y1,0)) y1
291 ,SUM(NVL(z1,0)) z1
292 ,COUNT(DISTINCT(CASE WHEN report_date = &BIS_CURRENT_ASOF_DATE
293 AND party_id <> -1
294 THEN PARTY_ID END ))
295 a1
296 ,COUNT(DISTINCT(CASE WHEN report_date = &BIS_PREVIOUS_ASOF_DATE
297 AND party_id <> -1
298 THEN PARTY_ID END ))
299 a2
300 ,MIN(a9) a9
301 ,MIN(a10) a10
302 ,DECODE(SUM(NVL(m,0) + NVL(a3,0)), 0, NULL, SUM(NVL(m,0) + NVL(a3,0))) q
303 ,DECODE(SUM(NVL(n,0) + NVL(a4,0)), 0, NULL, SUM(NVL(n,0) + NVL(a4,0))) r
304 ,DECODE(SUM(NVL(g,0) + NVL(a7,0)), 0, NULL, SUM(NVL(g,0) + NVL(a7,0))) g1
305 ,DECODE(SUM(NVL(h,0) + NVL(a8,0)), 0, NULL, SUM(NVL(h,0) + NVL(a8,0))) h1 ,min(sourcecode) sourcecode
306 ,sum(sum(nvl(loginp,0))) over( ) loginp
307 ,sum(sum(nvl(loginc,0))) over( ) loginc
308 ,sum(sum(nvl(idlep,0))) over( ) idlep
309 ,sum(sum(nvl(idlec,0))) over( ) idlec
310 ,sum(sum(nvl(availp,0))) over() availp
311 ,sum(sum(nvl(availc,0))) over() availc
312 ,sum(nvl(prp,0) ) prp
313 ,sum(nvl(prc,0)) prc
314 ,sum(nvl(contp,0)) contp
315 ,sum(nvl(contc,0)) contc
316 from
317 (
318 /* START OF UNION ALL CLAUSES - INNER MOST QUERY */
319 SELECT
320 campaign_id
321 ,source_code_id sourcecode
322 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_calls_handled_total, 0)
323 c
324 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_calls_handled_total, 0)
325 d
326 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,
327 decode(dialing_method,''PRED'',call_calls_offered_total,0),0)
328 g
329 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,decode(dialing_method,''PRED'',call_calls_offered_total,0), 0)
330 h
331 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,decode(dialing_method,''PRED'',call_calls_abandoned,0), 0)
332 i
333 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,decode(dialing_method,''PRED'',call_calls_abandoned,0), 0)
334 j
335 /* Added for US Abandon rate */
336 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,decode(dialing_method,''PRED'',call_calls_abandoned_us,0), 0)
337 ius
338 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,decode(dialing_method,''PRED'',call_calls_abandoned_us,0), 0)
339 jus
340 /* End of additions */
341 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,call_calls_transferred, 0)
342 k
343 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,call_calls_transferred, 0)
344 l
345 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,call_calls_handled_total, 0)
346 m
347 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,call_calls_handled_total, 0)
348 n
349 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_wrap_time_nac)
350 s
351 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_wrap_time_nac)
352 t
353 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,call_talk_time)
354 u
355 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,call_talk_time)
356 v
357 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_sr_created)
358 w
359 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_sr_created)
360 x
361 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_leads_created)
362 y
363 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_leads_created)
364 z
365 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_opportunities_created)
366 y1
367 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_opportunities_created)
368 z1
369 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agentcall_pr_count)
370 prc
371 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agentcall_pr_count)
372 prp
373 ,party_id party_id
374 ,calendar.report_date report_date
375 ,NULL a3
376 ,NULL a4
377 ,NULL a7
378 ,NULL a8
379 ,COUNT(DISTINCT(CASE WHEN calendar.report_date = &BIS_CURRENT_ASOF_DATE
380 AND party_id <> -1
381 THEN PARTY_ID END )) OVER()
382 a9
383 ,COUNT(DISTINCT(CASE WHEN calendar.report_date = &BIS_PREVIOUS_ASOF_DATE
384 AND party_id <> -1
385 THEN PARTY_ID END )) OVER()
386 a10
387 ,0 loginp
388 ,0 loginc
389 ,0 idlep
390 ,0 idlec
391 ,0 availp
392 ,0 availc
393 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agentcall_contact_count)
394 contp
395 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agentcall_contact_count)
396 contc
397 FROM
398 bix_ao_call_details_mv a,
399 fii_time_rpt_struct calendar
400 WHERE a.row_type = ''C''
401 AND a.time_id = calendar.time_id
402 AND a.period_type_id = calendar.period_type_id
403 AND calendar.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
404 AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) = calendar.record_type_id
405 '||l_where_clause||' union all
406 /* continue inline view and select session measures */
407 SELECT -999
408 ,-999 sourcecode
409 ,NULL c
410 ,NULL d
411 ,NULL g
412 ,NULL h
413 ,NULL i
414 ,NULL j
415 /* Added for US abandon measure */
416 ,NULL ius
417 ,NULL jus
418 /* End of addition */
419 ,NULL k
420 ,NULL l
421 ,NULL m
422 ,NULL n
423 ,NULL s
424 ,NULL t
425 ,NULL u
426 ,NULL v
427 ,NULL w
428 ,NULL x
429 ,NULL y
430 ,NULL z
431 ,NULL y1
432 ,NULL z1
433 ,0 prc
434 ,0 prp
435 ,NULL party_id
436 ,NULL report_date
437 ,0 a3
438 ,0 a4
439 ,0 a7
440 ,0 a8
441 ,0 a9
442 ,0 a10
443 , SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,login_time))
444 loginp
445 , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,login_time))
446 loginc
447 , SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,idle_time))
448 idlep
449 , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,idle_time))
450 idlec
451 , SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,available_time))
452 availp
453 , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,available_time))
454 availc
455 , 0 contp
456 ,0 contc
457 FROM
458 bix_agent_session_f fact,
459 fii_time_rpt_struct calendar
460 WHERE fact.time_id = calendar.time_id
461 AND fact.application_id = 696
462 AND fact.period_type_id = calendar.period_type_id
463 AND calendar.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
464 AND
465 bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) = calendar.record_type_id
466 '||l_call_where_clause ||'
467 group by campaign_id
468 UNION ALL
469 /*Continue inline view select continued measures */
470 SELECT campaign_id
471 ,source_code_id sourcecode
472 ,NULL c
473 ,NULL d
474 ,NULL g
475 ,NULL h
476 ,NULL i
477 /* Added for US abandon measure */
478 ,NULL ius
479 ,NULL j
480 ,NULL jus
481 /* End of addition */
482 ,NULL k
483 ,NULL l
484 ,NULL m
485 ,NULL n
486 ,NULL s
487 ,NULL t
488 ,NULL u
489 ,NULL v
490 ,NULL w
491 ,NULL x
492 ,NULL y
493 ,NULL z
494 ,NULL y1
495 ,NULL z1
496 ,0 prc
497 ,0 prp
498 ,NULL party_id
499 ,NULL report_date
500 ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),call_cont_calls_handled_tot_na)
501 a3
502 ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')),call_cont_calls_handled_tot_na)
503 a4
504 ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')), decode(dialing_method,''PRED'',call_cont_calls_offered_na,0), 0)
505 a7
506 ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')),decode(dialing_method,''PRED'',call_cont_calls_offered_na,0), 0)
507 a8
508 ,NULL a9
509 ,NULL a10
510 ,0 loginp
511 ,0 loginc
512 ,0 idlep
513 ,0 idlec
514 ,0 availp
515 ,0 availc
516 ,0 contp
517 ,0 contc
518 FROM
519 bix_ao_call_details_mv a
520 WHERE row_type = ''C''
521 AND time_id IN (TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),
522 TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')))
523 AND period_type_id = 1 '||l_where_clause||'
524 )
525 group by campaign_id
526 ) a ,( select source_code_for_id,camp.id id,camp.value value from bim_dimv_campaigns camp,ams_source_codes scodes
527 where scodes.source_code_id=camp.id and
528 arc_source_code_for=:l_camp and active_flag=''Y'' ) campmast
529 where a.campaign_id=campmast.source_code_for_id(+)
530 and a.campaign_id<>-999
531 group by campmast.value,campmast.id
532 )
533
534 &ORDER_BY_CLAUSE';
535 ELSIF l_view_by = 'BIX_TELEPHONY+BIX_CALL_CENTER' THEN
536 l_sqltext:='
537 select VIEWBY,VIEWBYID,ROUND(i / g1 * 100, 1)
538 BIX_PMV_AO_ABANRATE_CP
539 ,ROUND(SUM(i) OVER() / SUM(g1) OVER() * 100, 1)
540 BIX_PMV_TOTAL1
541 ,ROUND((i / g1 * 100) - (j / h1 * 100), 1)
542 BIX_PMV_AO_ABANRATE_CG
543 ,ROUND(SUM(i) OVER() / SUM(g1) OVER() * 100 - SUM(j) OVER() / SUM(h1) OVER() * 100, 1)
544 BIX_PMV_TOTAL2
545 ,ROUND(j / h1 * 100, 1)
546 BIX_PMV_AO_ABANRATE_PP
547 /* Added for US Abandon Rate */
548 ,ROUND(ius / g1 * 100, 1)
549 BIX_PMV_AO_US_ABANRATE_CP
550 ,ROUND(SUM(ius) OVER() / SUM(g1) OVER() * 100, 1)
551 BIX_PMV_TOTAL25
552 ,ROUND((ius / g1 * 100) - (jus / h1 * 100), 1)
553 BIX_PMV_AO_US_ABANRATE_CG
554 ,ROUND(SUM(ius) OVER() / SUM(g1) OVER() * 100 - SUM(jus) OVER() / SUM(h1) OVER() * 100, 1)
555 BIX_PMV_TOTAL26
556 ,ROUND(jus / h1 * 100, 1)
557 BIX_PMV_AO_US_ABANRATE_PP
558 /* End of Additions */
559 ,nvl(m,0) BIX_PMV_AO_OUTCALLHAND_CP
560 ,nvl(SUM(m) OVER() ,0)
561 BIX_PMV_TOTAL3
562 ,ROUND((m - n) / DECODE(n, 0, NULL, n) * 100, 1)
563 BIX_PMV_AO_OUTCALLHAND_CG
564 ,ROUND((SUM(m) OVER() - SUM(n) OVER()) / DECODE(SUM(n) OVER(), 0, NULL, SUM(n) OVER()) * 100, 1)
565 BIX_PMV_TOTAL4
566 ,nvl(n,0) BIX_PMV_AO_OUTCALLHAND_PP
567 ,ROUND(u / q, 1) BIX_PMV_AO_AVGTALK_CP
568 ,ROUND(SUM(u) OVER() / SUM(q) OVER(), 1)
569 BIX_PMV_TOTAL5
570 ,ROUND(((u / q) - (v / r)) / DECODE(v / r, 0, NULL, v / r) * 100 , 1)
571 BIX_PMV_AO_AVGTALK_CG
572 ,ROUND((SUM(u) OVER() / SUM(q) OVER() - SUM(v) OVER() / SUM(r) OVER()) /
573 DECODE(SUM(v) OVER() / SUM(r) OVER(), 0, NULL, SUM(v) OVER() / SUM(r) OVER()) * 100 , 1)
574 BIX_PMV_TOTAL6
575 ,ROUND(v / r, 1) BIX_PMV_AO_AVGTALK_PP
576 ,ROUND(s / q, 1) BIX_PMV_AO_AVGWRAP_CP
577 ,ROUND(SUM(s) OVER() / SUM(q) OVER(), 1)
578 BIX_PMV_TOTAL7
579 ,ROUND(((s / q) - (t / r)) / DECODE(t / r, 0, NULL, t / r) * 100, 1)
580 BIX_PMV_AO_AVGWRAP_CG
581 ,ROUND((SUM(s) OVER() / SUM(q) OVER() - SUM(t) OVER() / SUM(r) OVER()) /
582 DECODE(SUM(t) OVER() / SUM(r) OVER(), 0, NULL, SUM(t) OVER() / SUM(r) OVER()) * 100, 1)
583 BIX_PMV_TOTAL8
584 ,w BIX_PMV_AO_SRCR_CP
585 ,SUM(w) OVER() BIX_PMV_TOTAL9
586 ,ROUND((w - x) / DECODE(x, 0, NULL, x) * 100, 1)
587 BIX_PMV_AO_SRCR_CG
588 ,ROUND((SUM(w) OVER() - SUM(x) OVER()) / DECODE(SUM(x) OVER(), 0, NULL, SUM(x) OVER()) * 100, 1)
589 BIX_PMV_TOTAL10
590 ,y BIX_PMV_AO_LECR_CP
591 ,SUM(y) OVER() BIX_PMV_TOTAL11
592 ,ROUND((y - z) / DECODE(z, 0, NULL, z) * 100, 1)
593 BIX_PMV_AO_LECR_CG
594 ,ROUND((SUM(y) OVER() - SUM(z) OVER()) / DECODE(SUM(z) OVER(), 0, NULL, SUM(z) OVER()) * 100, 1)
595 BIX_PMV_TOTAL12
596 ,y1 BIX_PMV_AO_OPCR_CP
597 ,SUM(y1) OVER() BIX_PMV_TOTAL13
598 ,ROUND((y1 - z1) / DECODE(z1, 0, NULL, z1) * 100, 1)
599 BIX_PMV_AO_OPCR_CG
600 ,ROUND((SUM(y1) OVER() - SUM(z1) OVER()) / DECODE(SUM(z1) OVER(), 0, NULL, SUM(z1) OVER()) * 100, 1)
601 BIX_PMV_TOTAL14
602 ,a1 BIX_PMV_AO_CUST_CP
603 ,a9 BIX_PMV_TOTAL15
604 ,ROUND((a1 - a2) / DECODE(a2, 0, null, a2) * 100, 1)
605 BIX_PMV_AO_CUST_CG
606 ,ROUND((a9 - a10) / DECODE(a10, 0, NULL, a10) * 100, 1)
607 BIX_PMV_TOTAL16
608 ,ROUND(
609 (NVL(loginc,0) - NVL(idlec, 0)) /
610 DECODE(loginc, 0, NULL, loginc) * 100, 1)
611 BIX_PMV_AO_AVAILRATE_CP
612 ,ROUND(
613 SUM(NVL(loginc,0) - NVL(idlec, 0)) OVER() /
614 DECODE(SUM(loginc) OVER(), 0, NULL, SUM(loginc) OVER() )* 100, 1)
615 BIX_PMV_TOTAL17
616 ,ROUND(
617 (NVL(loginc,0) - NVL(idlec, 0)) /
618 DECODE(loginc, 0, NULL, loginc) * 100
619 -
620 (NVL(loginp,0) - NVL(idlep, 0)) /
621 DECODE(loginp, 0, NULL, loginp) * 100
622 , 1)
623 BIX_PMV_AO_AVAILRATE_CG
624 ,ROUND(
625 sum(NVL(loginc,0) - NVL(idlec, 0)) over() /
626 DECODE(sum(loginc) over(), 0, NULL, sum(loginc) over() ) * 100
627 -
628 sum(NVL(loginp,0) - NVL(idlep, 0)) over() /
629 DECODE(sum(loginp) over(), 0, NULL, sum(loginp)over()) * 100
630 , 1)
631 BIX_PMV_TOTAL18
632 ,ROUND(
633 (NVL(loginc,0) - NVL(idlec, 0)-nvl(availc,0)) /
634 DECODE(loginc, 0, NULL, loginc) * 100, 1) BIX_PMV_AO_UTILRATE_CP
635 ,ROUND(
636 SUM(NVL(loginc,0) - NVL(idlec, 0)-nvl(availc,0)) OVER() /
637 DECODE(SUM(loginc) OVER(), 0, NULL, SUM(loginc) OVER() )* 100, 1)
638 BIX_PMV_TOTAL19
639 ,ROUND(
640 (NVL(loginc,0) - NVL(idlec, 0)-nvl(availc,0)) /
641 DECODE(loginc, 0, NULL, loginc) * 100
642 -
643 (NVL(loginp,0) - NVL(idlep, 0)-nvl(availp,0)) /
644 DECODE(loginp, 0, NULL, loginp) * 100
645 , 1)
646 BIX_PMV_AO_UTILRATE_CG
647 ,
648 ROUND(
649 sum(NVL(loginc,0) - NVL(idlec, 0)-nvl(availc,0)) over() /
650 DECODE(sum(loginc) over(), 0, NULL, sum(loginc) over() ) * 100
651 -
652 sum(NVL(loginp,0) - NVL(idlep, 0)-nvl(availp,0)) over() /
653 DECODE(sum(loginp) over(), 0, NULL, sum(loginp)over()) * 100
654 , 1) BIX_PMV_TOTAL20
655 ,nvl(prC,0) BIX_PMV_AO_PORESP_CP
656 ,sum(nvl(prc,0)) OVER() BIX_PMV_TOTAL21
657 /* ,nvl(PRC,0)-NVL(PRP,0) BIX_PMV_AO_PORESP_CG
658 ,SUM(nvl(PRC,0)-NVL(PRP,0)) OVER() BIX_PMV_TOTAL22
659 */
660 ,(nvl(PRC,0)-NVL(PRP,0))*100/decode(prp,0,null,prp) BIX_PMV_AO_PORESP_CG
661 ,sum(nvl(PRC,0)-NVL(PRP,0)) over()*100/sum(decode(prp,0,null,prp)) over() BIX_PMV_TOTAL22
662 ,nvl(PRp,0) BIX_PMV_AO_PORESP_PP
663 ,contc/(decode(loginc,0,null,loginc)/3600) BIX_PMV_AO_CONTPERHR_CP , sum(nvl(contc,0)) over() /(decode(sum(loginc) over() ,0,null,sum(loginc) over() )/3600) BIX_PMV_TOTAL23
664 , (contc/decode(loginc,0,NULL,loginc) -contp/decode(loginp,0,null,loginp))*100/decode(contp/decode(loginp,0,null,loginp),0,null,contp/decode(loginp,0,null,loginp)) BIX_PMV_AO_CONTPERHR_CG
665 , (sum(contc) over()/decode(sum(loginc) over(),0,null,sum(loginc) over()) -
666 sum(contp) over()/decode(sum(loginp) over(),0,null,sum(loginp) over()))*100/
667 decode(sum(contp) over()/decode(sum(loginp) over(),0,null,sum(loginp) over()),0,null,sum(contp) over()
668 /decode(sum(loginp) over(),0,null,sum(loginp) over()) ) BIX_PMV_TOTAL24
669 ,contp/(decode(loginp,0,null,loginp)/3600) BIX_PMV_AO_CONTPERHR_PP
670 FROM ( SELECT nvl(group_name,:l_unknown) VIEWBY,a.server_group_id VIEWBYID
671 ,DECODE(SUM(c), 0, NULL, SUM(c)) c
672 ,DECODE(SUM(d), 0, NULL, SUM(d)) d
673 ,DECODE(SUM(g), 0, NULL, SUM(g)) g
674 ,DECODE(SUM(h), 0, NULL, SUM(h)) h
675 ,SUM(NVL(i,0)) i
676 ,SUM(NVL(j,0)) j
677 /* Added for US Abandonment rate */
678 ,SUM(NVL(ius,0)) ius
679 ,SUM(NVL(jus,0)) jus
680 /* End of addition */
681 ,SUM(NVL(k,0)) k
682 ,SUM(NVL(l,0)) l
683 ,DECODE(SUM(m), 0, NULL, SUM(m)) m
684 ,DECODE(SUM(n), 0, NULL, SUM(n)) n
685 ,SUM(NVL(s,0)) s
686 ,SUM(NVL(t,0)) t
687 ,SUM(NVL(u,0)) u
688 ,SUM(NVL(v,0)) v
689 ,SUM(NVL(w,0)) w
690 ,SUM(NVL(x,0)) x
691 ,SUM(NVL(y,0)) y
692 ,SUM(NVL(z,0)) z
693 ,SUM(NVL(y1,0)) y1
694 ,SUM(NVL(z1,0)) z1
695 ,COUNT(DISTINCT(CASE WHEN report_date = &BIS_CURRENT_ASOF_DATE
696 AND party_id <> -1
697 THEN PARTY_ID END ))
698 a1
699 ,COUNT(DISTINCT(CASE WHEN report_date = &BIS_PREVIOUS_ASOF_DATE
700 AND party_id <> -1
701 THEN PARTY_ID END ))
702 a2
703 ,MIN(a9) a9
704 ,MIN(a10) a10
705 ,DECODE(SUM(NVL(m,0) + NVL(a3,0)), 0, NULL, SUM(NVL(m,0) + NVL(a3,0))) q
706 ,DECODE(SUM(NVL(n,0) + NVL(a4,0)), 0, NULL, SUM(NVL(n,0) + NVL(a4,0))) r
707 ,DECODE(SUM(NVL(g,0) + NVL(a7,0)), 0, NULL, SUM(NVL(g,0) + NVL(a7,0))) g1
708 ,DECODE(SUM(NVL(h,0) + NVL(a8,0)), 0, NULL, SUM(NVL(h,0) + NVL(a8,0))) h1 ,sum(nvl(loginp,0)) loginp
709 ,sum(nvl(loginc,0)) loginc
710 ,sum(nvl(idlep,0)) idlep
711 ,sum(nvl(idlec,0)) idlec
712 ,sum(nvl(availp,0)) availp
713 ,sum(nvl(availc,0)) availc
714 ,sum(nvl(prp,0) ) prp
715 ,sum(nvl(prc,0)) prc
716 ,sum(nvl(contp,0)) contp
717 ,sum(nvl(contc,0)) contc
718 FROM (
719 SELECT
720 server_group_id
721 ,source_code_id sourcecode
722 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_calls_handled_total, 0)
723 c
724 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_calls_handled_total, 0)
725 d
726 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,
727 decode(dialing_method,''PRED'',call_calls_offered_total,0),0)
728 g
729 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,decode(dialing_method,''PRED'',call_calls_offered_total,0), 0)
730 h
731 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,decode(dialing_method,''PRED'',call_calls_abandoned,0), 0)
732 i
733 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,decode(dialing_method,''PRED'',call_calls_abandoned,0), 0)
734 j
735 /* Added for US Abandon rate */
736 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,decode(dialing_method,''PRED'',call_calls_abandoned_us,0), 0)
737 ius
738 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,decode(dialing_method,''PRED'',call_calls_abandoned_us,0), 0)
739 jus
740 /* End of additions */
741 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,call_calls_transferred, 0)
742 k
743 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,call_calls_transferred, 0)
744 l
745 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,call_calls_handled_total, 0)
746 m
747 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,call_calls_handled_total, 0)
748 n
749 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_wrap_time_nac)
750 s
751 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_wrap_time_nac)
752 t
753 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,call_talk_time)
754 u
755 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,call_talk_time)
756 v
757 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_sr_created)
758 w
759 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_sr_created)
760 x
761 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_leads_created)
762 y
763 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_leads_created)
764 z
765 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_opportunities_created)
766 y1
767 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_opportunities_created)
768 z1
769 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agentcall_pr_count)
770 prc
771 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agentcall_pr_count)
772 prp
773 ,party_id party_id
774 ,calendar.report_date report_date
775 ,NULL a3
776 ,NULL a4
777 ,NULL a7
778 ,NULL a8
779 ,COUNT(DISTINCT(CASE WHEN calendar.report_date = &BIS_CURRENT_ASOF_DATE
780 AND party_id <> -1
781 THEN PARTY_ID END )) OVER()
782 a9
783 ,COUNT(DISTINCT(CASE WHEN calendar.report_date = &BIS_PREVIOUS_ASOF_DATE
784 AND party_id <> -1
785 THEN PARTY_ID END )) OVER()
786 a10
787 ,0 loginp
788 ,0 loginc
789 ,0 idlep
790 ,0 idlec
791 ,0 availp
792 ,0 availc
793 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agentcall_contact_count)
794 contp
795 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agentcall_contact_count)
796 contc
797 FROM
798 bix_ao_call_details_mv a,
799 fii_time_rpt_struct calendar
800 WHERE a.row_type = ''C''
801 AND a.time_id = calendar.time_id
802 AND a.period_type_id = calendar.period_type_id
803 AND calendar.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
804 AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) = calendar.record_type_id
805 '||l_where_clause||' union all
806 /* continue inline view and select session measures */
807 SELECT server_group_id
808 ,-999 sourcecode
809 ,NULL c
810 ,NULL d
811 ,NULL g
812 ,NULL h
813 ,NULL i
814 ,NULL j
815 /* Added for US abandon measure */
816 ,NULL ius
817 ,NULL jus
818 /* End of addition */
819 ,NULL k
820 ,NULL l
821 ,NULL m
822 ,NULL n
823 ,NULL s
824 ,NULL t
825 ,NULL u
826 ,NULL v
827 ,NULL w
828 ,NULL x
829 ,NULL y
830 ,NULL z
831 ,NULL y1
832 ,NULL z1
833 ,0 prc
834 ,0 prp
835 ,NULL party_id
836 ,NULL report_date
837 ,0 a3
838 ,0 a4
839 ,0 a7
840 ,0 a8
841 ,0 a9
842 ,0 a10
843 , SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,login_time))
844 loginp
845 , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,login_time))
846 loginc
847 , SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,idle_time))
848 idlep
849 , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,idle_time))
850 idlec
851 , SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,available_time))
852 availp
853 , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,available_time))
854 availc
855 , 0 contp
856 ,0 contc
857 FROM
858 bix_agent_session_f fact,
859 fii_time_rpt_struct calendar
860 WHERE fact.time_id = calendar.time_id
861 AND fact.application_id = 696
862 AND fact.period_type_id = calendar.period_type_id
863 AND calendar.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
864 AND
865 bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) = calendar.record_type_id
866 '||l_call_where_clause||' group by server_group_id
867 UNION ALL
868 /*Continue inline view select continued measures */
869 SELECT server_group_id
870 ,source_code_id sourcecode
871 ,NULL c
872 ,NULL d
873 ,NULL g
874 ,NULL h
875 ,NULL i
876 /* Added for US abandon measure */
877 ,NULL ius
878 ,NULL j
879 ,NULL jus
880 /* End of addition */
881 ,NULL k
882 ,NULL l
883 ,NULL m
884 ,NULL n
885 ,NULL s
886 ,NULL t
887 ,NULL u
888 ,NULL v
889 ,NULL w
890 ,NULL x
891 ,NULL y
892 ,NULL z
893 ,NULL y1
894 ,NULL z1
895 ,0 prc
896 ,0 prp
897 ,NULL party_id
898 ,NULL report_date
899 ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),call_cont_calls_handled_tot_na)
900 a3
901 ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')),call_cont_calls_handled_tot_na)
902 a4
903 ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')), decode(dialing_method,''PRED'',call_cont_calls_offered_na,0), 0)
904 a7
905 ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')),decode(dialing_method,''PRED'',call_cont_calls_offered_na,0), 0)
906 a8
907 ,NULL a9
908 ,NULL a10
909 ,0 loginp
910 ,0 loginc
911 ,0 idlep
912 ,0 idlec
913 ,0 availp
914 ,0 availc
915 ,0 contp
916 ,0 contc
917 FROM
918 bix_ao_call_details_mv a
919 WHERE row_type = ''C''
920 AND time_id IN (TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),
921 TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')))
922 AND period_type_id = 1 '||l_where_clause||'
923 ) a , ieo_svr_groups grp
924 ';
925 l_sqltext:=l_sqltext||'WHERE a.server_group_id = grp.server_group_id(+)';
926
927 l_sqltext:=l_sqltext||'
928 group by group_name,a.server_group_id
929 ) &ORDER_BY_CLAUSE
930 ';
931
932 END IF;
933
934 /* l_sqltext :=
935 'SELECT
936 10 BIX_PMV_AO_CAMPNAME,
937 10 BIX_PMV_AO_CONTPERHR_CP,
938 10 BIX_PMV_AO_CONTPERHR_CG,
939 10 BIX_PMV_AO_PORESP_CP,
940 10 BIX_PMV_AO_PORESP_CG,
941 10 BIX_PMV_AO_ABANRATE_CP,
942 10 BIX_PMV_AO_ABANRATE_CG,
943 10 BIX_PMV_AO_OUTCALLHAND_CP,
944 10 BIX_PMV_AO_OUTCALLHAND_CG,
945 10 BIX_PMV_AO_AVAILRATE_CP,
946 10 BIX_PMV_AO_AVAILRATE_CG,
947 10 BIX_PMV_AO_UTILRATE_CP,
948 10 BIX_PMV_AO_UTILRATE_CG,
949 10 BIX_PMV_AO_AVGTALK_CP,
950 10 BIX_PMV_AO_AVGTALK_CG,
951 10 BIX_PMV_AO_AVGWRAP_CP,
952 10 BIX_PMV_AO_AVGWRAP_CG,
953 10 BIX_PMV_AO_SRCR_CP,
954 10 BIX_PMV_AO_SRCR_CG,
955 10 BIX_PMV_AO_LECR_CP,
956 10 BIX_PMV_AO_LECR_CG,
957 10 BIX_PMV_AO_OPCR_CP,
958 10 BIX_PMV_AO_OPCR_CG,
959 10 BIX_PMV_AO_CUST_CP,
960 10 BIX_PMV_AO_CUST_CG,
961 100 BIX_PMV_TOTAL1,
962 100 BIX_PMV_TOTAL2,
963 100 BIX_PMV_TOTAL3,
964 100 BIX_PMV_TOTAL4,
965 100 BIX_PMV_TOTAL5,
966 100 BIX_PMV_TOTAL6,
967 100 BIX_PMV_TOTAL7,
968 100 BIX_PMV_TOTAL8,
969 100 BIX_PMV_TOTAL9,
970 100 BIX_PMV_TOTAL10,
971 100 BIX_PMV_TOTAL11,
972 100 BIX_PMV_TOTAL12,
973 100 BIX_PMV_TOTAL13,
974 100 BIX_PMV_TOTAL14,
975 100 BIX_PMV_TOTAL15,
976 100 BIX_PMV_TOTAL16,
977 100 BIX_PMV_TOTAL17,
978 100 BIX_PMV_TOTAL18,
979 100 BIX_PMV_TOTAL19,
980 100 BIX_PMV_TOTAL20,
981 100 BIX_PMV_TOTAL21,
982 100 BIX_PMV_TOTAL22
983 FROM DUAL ';
984 */
985
986
987
988
989
990 l_sqltext:=replace(replace(replace(replace(replace(l_sqltext,
991 ' ',' '),' ',' '),' ',' '),' ',' '),' ',' ');
992
993 p_sql_text := l_sqltext;
994
995
996 l_custom_rec.attribute_name := ':l_unknown' ;
997 l_custom_rec.attribute_value:= l_unknown;
998 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
999 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1000
1001 p_custom_output.Extend();
1002 p_custom_output(p_custom_output.count) := l_custom_rec;
1003
1004 -- p_custom_output.EXTEND();
1005 IF l_call_center IS NOT NULL THEN
1006 l_custom_rec.attribute_name := ':l_call_center' ;
1007 l_custom_rec.attribute_value:= l_call_center;
1008 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1009 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1010
1011 p_custom_output.Extend();
1012 p_custom_output(p_custom_output.count) := l_custom_rec;
1013 END IF;
1014 IF l_campaign_id IS NOT NULL
1015 THEN
1016 l_custom_rec.attribute_name := ':l_campaign_id';
1017 l_custom_rec.attribute_value:= l_campaign_id;
1018 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1019 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1020
1021 p_custom_output.Extend();
1022 p_custom_output(p_custom_output.count) := l_custom_rec;
1023 END IF;
1024
1025 IF l_schedule_id IS NOT NULL
1026 THEN
1027 l_custom_rec.attribute_name := ':l_schedule_id';
1028 l_custom_rec.attribute_value:= l_schedule_id;
1029 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1030 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1031
1032 p_custom_output.Extend();
1033 p_custom_output(p_custom_output.count) := l_custom_rec;
1034 END IF;
1035 IF l_source_code_id IS NOT NULL
1036 THEN
1037 l_custom_rec.attribute_name := ':l_source_code_id';
1038 l_custom_rec.attribute_value:= l_source_code_id;
1039 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1040 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1041
1042 p_custom_output.Extend();
1043 p_custom_output(p_custom_output.count) := l_custom_rec;
1044 END IF;
1045
1046 IF l_view_by = 'CAMPAIGN+CAMPAIGN' THEN
1047
1048 l_custom_rec.attribute_name := ':l_camp';
1049 l_custom_rec.attribute_value:= 'CAMP';
1050 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1051 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1052
1053 p_custom_output.Extend();
1054 p_custom_output(p_custom_output.count) := l_custom_rec;
1055
1056 END IF;
1057
1058
1059 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
1060 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
1061 l_custom_rec.attribute_value := l_view_by;
1062
1063 p_custom_output.EXTEND();
1064 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
1065
1066
1067 EXCEPTION
1068 WHEN OTHERS THEN
1069 NULL;
1070 END GET_SQL;
1071 END BIX_PMV_AO_TELDTL_RPT_PKG;