[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AO_ACTCSCH_RPT_PKG
Source
1 PACKAGE BODY BIX_PMV_AO_ACTCSCH_RPT_PKG AS
2 /*$Header: bixocshr.plb 115.8 2004/04/26 06:04:13 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 l_call_center VARCHAR2(3000);
13 l_view_by VARCHAR2(3000);
14 l_column_name VARCHAR2(1000);
15 l_unknown VARCHAR2(3000);
16
17 l_custom_rec BIS_QUERY_ATTRIBUTES;
18 --added for campaign, schedule and source code
19 l_campaign_id varchar2(3000);
20 l_schedule_id varchar2(3000);
21 l_source_code_id varchar2(3000);
22 l_campaign_where_clause VARCHAR2(3000);
23 l_call_where_clause VARCHAR2(3000);
24 l_schedule_where_clause VARCHAR2(3000);
25 l_source_code_where_clause VARCHAR2(3000);
26 l_agent_group varchar2(3000);
27 l_sess_source_where_clause VARCHAR2(3000);
28 l_as_of_date DATE;
29 l_period_type varchar2(2000);
30 l_comp_type varchar2(2000);
31 l_record_type_id NUMBER;
32
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
61 IF l_call_center IS NOT NULL THEN
62 l_call_where_clause := ' AND server_group_id IN (:l_call_center) ';
63 END IF;
64
65 IF l_campaign_id IS NOT NULL THEN
66 l_campaign_where_clause := ' AND mv.campaign_id IN (:l_campaign_id) ';
67 END IF;
68 IF l_schedule_id IS NOT NULL THEN
69 l_schedule_where_clause := ' AND mv.schedule_id IN (:l_schedule_id) ';
70 END IF;
71 IF l_source_code_id IS NOT NULL THEN
72 l_source_code_where_clause := ' AND campaign_id in (select source_code_for_id from
73 ams_source_codes where arc_source_code_for=''CAMP'' and active_flag=''Y'' and source_code_id IN (:l_source_code_id) ) ';
74 END IF;
75
76 l_where_clause:=l_source_code_where_clause||l_call_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 /**
87 IF l_view_by = 'BIX_TELEPHONY+BIX_CALL_CENTER' THEN
88 l_column_name := 'server_group_id ';
89 ELSIF l_view_by = 'CAMPAIGN+CAMPAIGN' THEN
90 l_column_name := 'source_code_id';
91 ELSE
92 l_column_name := 'server_group_id ';
93 END IF;
94 **/
95
96
97 /* l_sqltext :=
98 'SELECT
99 10 BIX_PMV_AO_CAMPNAME,
100 10 BIX_PMV_AO_CONTPERHR_CP,
101 10 BIX_PMV_AO_CONTPERHR_CG,
102 10 BIX_PMV_AO_PORESP_CP,
103 10 BIX_PMV_AO_PORESP_CG,
104 10 BIX_PMV_AO_ABANRATE_CP,
105 10 BIX_PMV_AO_ABANRATE_CG,
106 10 BIX_PMV_AO_OUTCALLHAND_CP,
107 10 BIX_PMV_AO_OUTCALLHAND_CG,
108 10 BIX_PMV_AO_AVAILRATE_CP,
109 10 BIX_PMV_AO_AVAILRATE_CG,
110 10 BIX_PMV_AO_UTILRATE_CP,
111 10 BIX_PMV_AO_UTILRATE_CG,
112 10 BIX_PMV_AO_AVGTALK_CP,
113 10 BIX_PMV_AO_AVGTALK_CG,
114 10 BIX_PMV_AO_AVGWRAP_CP,
115 10 BIX_PMV_AO_AVGWRAP_CG,
116 10 BIX_PMV_AO_SRCR_CP,
117 10 BIX_PMV_AO_SRCR_CG,
118 10 BIX_PMV_AO_LECR_CP,
119 10 BIX_PMV_AO_LECR_CG,
120 10 BIX_PMV_AO_OPCR_CP,
121 10 BIX_PMV_AO_OPCR_CG,
122 10 BIX_PMV_AO_CUST_CP,
123 10 BIX_PMV_AO_CUST_CG,
124 100 BIX_PMV_TOTAL1,
125 100 BIX_PMV_TOTAL2,
126 100 BIX_PMV_TOTAL3,
127 100 BIX_PMV_TOTAL4,
128 100 BIX_PMV_TOTAL5,
129 100 BIX_PMV_TOTAL6,
130 100 BIX_PMV_TOTAL7,
131 100 BIX_PMV_TOTAL8,
132 100 BIX_PMV_TOTAL9,
133 100 BIX_PMV_TOTAL10,
134 100 BIX_PMV_TOTAL11,
135 100 BIX_PMV_TOTAL12,
136 100 BIX_PMV_TOTAL13,
137 100 BIX_PMV_TOTAL14,
138 100 BIX_PMV_TOTAL15,
139 100 BIX_PMV_TOTAL16,
140 100 BIX_PMV_TOTAL17,
141 100 BIX_PMV_TOTAL18,
142 100 BIX_PMV_TOTAL19,
143 100 BIX_PMV_TOTAL20,
144 100 BIX_PMV_TOTAL21,
145 100 BIX_PMV_TOTAL22
146 FROM DUAL ';
147 */
148
149 /*
150 IF l_column_name = 'server_group_id '
151 THEN
152 l_sqltext := 'SELECT group_name VIEWBY ';
153 ELSE
154 l_sqltext := 'SELECT value VIEWBY ';
155 END IF;
156 */
157
158 l_column_name := 'schedule_id ';
159
160
161 l_sqltext := 'SELECT schedule_name BIX_PMV_AO_CSCHNAME ';
162 l_sqltext := l_sqltext ||
163 ' ,ROUND(i / g1 * 100, 1) BIX_PMV_AO_ABANRATE_CP
164 ,ROUND(SUM(i) OVER() / SUM(g1) OVER() * 100, 1) BIX_PMV_TOTAL1
165 ,ROUND((i / g1 * 100) - (j / h1 * 100), 1) BIX_PMV_AO_ABANRATE_CG
166 ,ROUND(SUM(i) OVER() / SUM(g1) OVER() * 100 - SUM(j) OVER() / SUM(h1) OVER() * 100, 1) BIX_PMV_TOTAL2
167 ,ROUND(j / h1 * 100, 1) BIX_PMV_AO_ABANRATE_PP
168 /* Added for US Abandon Rate */
169 ,ROUND(ius / g1 * 100, 1)
170 BIX_PMV_AO_US_ABANRATE_CP
171 ,ROUND(SUM(ius) OVER() / SUM(g1) OVER() * 100, 1)
172 BIX_PMV_TOTAL25
173 ,ROUND((ius / g1 * 100) - (jus / h1 * 100), 1)
174 BIX_PMV_AO_US_ABANRATE_CG
175 ,ROUND(SUM(ius) OVER() / SUM(g1) OVER() * 100 - SUM(jus) OVER() / SUM(h1) OVER() * 100, 1)
176 BIX_PMV_TOTAL26
177 ,ROUND(jus / h1 * 100, 1)
178 BIX_PMV_AO_US_ABANRATE_PP
179 /* End of Additions */
180 ,nvl(m,0) BIX_PMV_AO_OUTCALLHAND_CP
181 ,nvl(SUM(m) OVER() ,0) BIX_PMV_TOTAL3
182 ,ROUND((m - n) / DECODE(n, 0, NULL, n) * 100, 1) BIX_PMV_AO_OUTCALLHAND_CG
183 ,ROUND((SUM(m) OVER() - SUM(n) OVER()) / DECODE(SUM(n) OVER(), 0, NULL, SUM(n) OVER()) * 100, 1) BIX_PMV_TOTAL4
184 ,nvl(n,0) BIX_PMV_AO_OUTCALLHAND_PP
185 ,ROUND(u / q, 1) BIX_PMV_AO_AVGTALK_CP
186 ,ROUND(SUM(u) OVER() / SUM(q) OVER(), 1) BIX_PMV_TOTAL5
187 ,ROUND(((u / q) - (v / r)) / DECODE(v / r, 0, NULL, v / r) * 100 , 1) BIX_PMV_AO_AVGTALK_CG
188 ,ROUND((SUM(u) OVER() / SUM(q) OVER() - SUM(v) OVER() / SUM(r) OVER()) /
189 DECODE(SUM(v) OVER() / SUM(r) OVER(), 0, NULL, SUM(v) OVER() / SUM(r) OVER()) * 100 , 1) BIX_PMV_TOTAL6
190 ,ROUND(v / r, 1) BIX_PMV_AO_AVGTALK_PP ,ROUND(s / q, 1) BIX_PMV_AO_AVGWRAP_CP
191 ,ROUND(SUM(s) OVER() / SUM(q) OVER(), 1) BIX_PMV_TOTAL7
192 ,ROUND(((s / q) - (t / r)) / DECODE(t / r, 0, NULL, t / r) * 100, 1) BIX_PMV_AO_AVGWRAP_CG
193 ,ROUND((SUM(s) OVER() / SUM(q) OVER() - SUM(t) OVER() / SUM(r) OVER()) /
194 DECODE(SUM(t) OVER() / SUM(r) OVER(), 0, NULL, SUM(t) OVER() / SUM(r) OVER()) * 100, 1) BIX_PMV_TOTAL8
195 ,NVL(w,0) BIX_PMV_AO_SRCR_CP ,NVL(SUM(w) OVER(),0) BIX_PMV_TOTAL9
196 ,ROUND((w - x) / DECODE(x, 0, NULL, x) * 100, 1) BIX_PMV_AO_SRCR_CG
197 ,ROUND((SUM(w) OVER() - SUM(x) OVER()) / DECODE(SUM(x) OVER(), 0, NULL, SUM(x) OVER()) * 100, 1)
198 BIX_PMV_TOTAL10
199 ,NVL(y,0) BIX_PMV_AO_LECR_CP ,NVL(SUM(y) OVER(),0) BIX_PMV_TOTAL11
200 ,ROUND((y - z) / DECODE(z, 0, NULL, z) * 100, 1) BIX_PMV_AO_LECR_CG
201 ,ROUND((SUM(y) OVER() - SUM(z) OVER()) / DECODE(SUM(z) OVER(), 0, NULL, SUM(z) OVER()) * 100, 1)
202 BIX_PMV_TOTAL12 ,NVL(y1,0) BIX_PMV_AO_OPCR_CP
203 ,SUM(y1) OVER() BIX_PMV_TOTAL13 ,ROUND((y1 - z1) / DECODE(z1, 0, NULL, z1) * 100, 1)
204 BIX_PMV_AO_OPCR_CG
205 ,ROUND((SUM(y1) OVER() - SUM(z1) OVER()) / DECODE(SUM(z1) OVER(), 0, NULL, SUM(z1) OVER()) * 100, 1)
206 BIX_PMV_TOTAL14
207 ,NVL(a1,0) BIX_PMV_AO_CUST_CP ,NVL(a9,0) BIX_PMV_TOTAL15
208 ,ROUND((a1 - a2) / DECODE(a2, 0, null, a2) * 100, 1) BIX_PMV_AO_CUST_CG
209 ,ROUND((a9 - a10) / DECODE(a10, 0, NULL, a10) * 100, 1) BIX_PMV_TOTAL16
210 , ROUND(
211 (NVL(loginc,0) - NVL(idlec, 0)) /
212 DECODE(loginc, 0, NULL, loginc) * 100, 1)
213 BIX_PMV_AO_AVAILRATE_CP
214 , ROUND(
215 (NVL(loginc,0) - NVL(idlec, 0)) /
216 DECODE(loginc, 0, NULL, loginc) * 100, 1)
217 /* ROUND(
218 SUM(NVL(loginc,0) - NVL(idlec, 0)) OVER() /
219 DECODE(SUM(loginc) OVER(), 0, NULL, SUM(loginc) OVER() )* 100, 1) */
220 BIX_PMV_TOTAL17
221 , ROUND(
222 (NVL(loginc,0) - NVL(idlec, 0)) /
223 DECODE(loginc, 0, NULL, loginc) * 100
224 -
225 (NVL(loginp,0) - NVL(idlep, 0)) /
226 DECODE(loginp, 0, NULL, loginp) * 100
227 , 1)
228 BIX_PMV_AO_AVAILRATE_CG
229 , ROUND(
230 (NVL(loginc,0) - NVL(idlec, 0)) /
231 DECODE(loginc, 0, NULL, loginc) * 100
232 -
233 (NVL(loginp,0) - NVL(idlep, 0)) /
234 DECODE(loginp, 0, NULL, loginp) * 100
235 , 1)
236 /* ROUND(
237 sum(NVL(loginc,0) - NVL(idlec, 0)) over() /
238 DECODE(sum(loginc) over(), 0, NULL, sum(loginc) over() ) * 100
239 -
240 sum(NVL(loginp,0) - NVL(idlep, 0)) over() /
241 DECODE(sum(loginp) over(), 0, NULL, sum(loginp)over()) * 100
242 , 1) */
243
244 BIX_PMV_TOTAL18
245 ,ROUND(
246 (NVL(loginc,0) - NVL(idlec, 0)-nvl(availc,0)) /
247 DECODE(loginc, 0, NULL, loginc) * 100, 1) BIX_PMV_AO_UTILRATE_CP
248 ,ROUND(
249 (NVL(loginc,0) - NVL(idlec, 0)-nvl(availc,0)) /
250 DECODE(loginc, 0, NULL, loginc) * 100, 1)
251 /* ROUND(
252 SUM(NVL(loginc,0) - NVL(idlec, 0)-nvl(availc,0)) OVER() /
253 DECODE(SUM(loginc) OVER(), 0, NULL, SUM(loginc) OVER() )* 100, 1) */
254 BIX_PMV_TOTAL19
255 , ROUND(
256 (NVL(loginc,0) - NVL(idlec, 0)-nvl(availc,0)) /
257 DECODE(loginc, 0, NULL, loginc) * 100
258 -
259 (NVL(loginp,0) - NVL(idlep, 0)-nvl(availp,0)) /
260 DECODE(loginp, 0, NULL, loginp) * 100
261 , 1)
262 BIX_PMV_AO_UTILRATE_CG
263 , ROUND(
264 (NVL(loginc,0) - NVL(idlec, 0)-nvl(availc,0)) /
265 DECODE(loginc, 0, NULL, loginc) * 100
266 -
267 (NVL(loginp,0) - NVL(idlep, 0)-nvl(availp,0)) /
268 DECODE(loginp, 0, NULL, loginp) * 100
269 , 1)
270 BIX_PMV_TOTAL20
271 /* NVL(ROUND(
272 sum(NVL(loginc,0) - NVL(idlec, 0)-nvl(availc,0)) over() /
273 DECODE(sum(loginc) over(), 0, NULL, sum(loginc) over() ) * 100
274 -
275 sum(NVL(loginp,0) - NVL(idlep, 0)-nvl(availp,0)) over() /
276 DECODE(sum(loginp) over(), 0, NULL, sum(loginp)over()) * 100
277 , 1),0)
278 BIX_PMV_TOTAL20 */
279 ,nvl(prC,0) BIX_PMV_AO_PORESP_CP
280 ,NVL(sum(nvl(prc,0)) OVER(),0) BIX_PMV_TOTAL21
281 ,100*(nvl(PRC,0)-NVL(PRP,0))/decode(PRP,0,NULL,PRP) BIX_PMV_AO_PORESP_CG
282 ,100*(SUM(PRC) OVER() -SUM(PRP) OVER())/ DECODE(SUM(PRP) OVER(),0,NULL,SUM(PRP) OVER())BIX_PMV_TOTAL22
283 ,nvl(PRp,0) BIX_PMV_AO_PORESP_PP
284 ,contc/(decode(loginc,0,null,loginc)/3600) BIX_PMV_AO_CONTPERHR_CP
285 ,sum(contc) over()/(decode(loginc,0,null,loginc)/3600) BIX_PMV_TOTAL23
286 /* , SUM(contc) OVER()/ ( decode( sum(loginc) OVER(),0,null,sum(loginc) OVER() )/3600) BIX_PMV_TOTAL23 */
287 ,(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
288 ,(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
289 /*,sum(contc-contp) over() *100/decode(sum(contc) over(),0,null,sum(contc) over()) BIX_PMV_TOTAL24 */
290 ,contp/(decode(loginp,0,null,loginp)/3600) BIX_PMV_AO_CONTPERHR_PP
291 FROM (
292 SELECT DECODE(a.schedule_id ,''-999'',''-999'',NVL(sched.schedule_name, :l_unknown)) schedule_name
293 ,sum(c) c ,sum(d) d,sum(g) g,sum(h) h,sum(i) i ,sum(j) j ,sum(ius) ius,sum(jus) jus,sum(k) k,sum(l) l
294 ,sum(m) m,sum(n) n,sum(s) s,sum(t) t,sum(u) u,sum(v) v,sum(w) w ,sum(x) x,sum(y) y,sum(z) z,sum(y1) y1
295 ,sum(z1) z1,sum(a1) a1,sum(a2) a2,sum(a9) a9,sum(a10) a10,sum(q) q ,sum(r) r,sum(g1) g1 ,sum(h1) h1
296 ,sum(prp) prp,sum(prc) prc,sum(contp) contp,sum(contc) contc
297 ,min(loginp) loginp
298 ,min(loginc) loginc
299 ,min(idlep) idlep
300 ,min(idlec) idlec
301 ,min(availp) availp
302 ,min(availc) availc FROM (' ;
303
304 /* Add the extra inline view for login handling */
305 l_sqltext := l_sqltext || ' SELECT '|| l_column_name ||'
306 , c,d,g,h,i,j,ius,jus,k,l,m,n,s,t,u,v,w,x,y,z,y1,z1,a1,a2,a9,a10,q,r,g1,h1,prp,prc,contp,contc
307 ,sum(loginp) over() loginp
308 ,sum(loginc) over() loginc
309 ,sum(idlep) over() idlep
310 ,sum(idlec) over() idlec
311 ,sum(availp) over() availp
312 ,sum(availc) over() availc
313 FROM (
314 ';
315 /* End of addition for inline view - Add one extra ")" also at the end*/
316 l_sqltext := l_sqltext || '
317 SELECT
318 ' || l_column_name || '
319 ,DECODE(SUM(c), 0, NULL, SUM(c)) c
320 ,DECODE(SUM(d), 0, NULL, SUM(d)) d
321 ,DECODE(SUM(g), 0, NULL, SUM(g)) g
322 ,DECODE(SUM(h), 0, NULL, SUM(h)) h
323 ,SUM(NVL(i,0)) i
324 ,SUM(NVL(j,0)) j
325 /* Added for US Abandonment rate */
326 ,SUM(NVL(ius,0)) ius
327 ,SUM(NVL(jus,0)) jus
328 /* End of addition */
329 ,SUM(NVL(k,0)) k
330 ,SUM(NVL(l,0)) l
331 ,DECODE(SUM(m), 0, NULL, SUM(m)) m
332 ,DECODE(SUM(n), 0, NULL, SUM(n)) n
333 ,SUM(NVL(s,0)) s
334 ,SUM(NVL(t,0)) t
335 ,SUM(NVL(u,0)) u
336 ,SUM(NVL(v,0)) v
337 ,SUM(NVL(w,0)) w
338 ,SUM(NVL(x,0)) x
339 ,SUM(NVL(y,0)) y
340 ,SUM(NVL(z,0)) z
341 ,SUM(NVL(y1,0)) y1
342 ,SUM(NVL(z1,0)) z1
343 ,COUNT(DISTINCT(CASE WHEN report_date = &BIS_CURRENT_ASOF_DATE
344 AND party_id <> -1
345 THEN PARTY_ID END ))
346 a1
347 ,COUNT(DISTINCT(CASE WHEN report_date = &BIS_PREVIOUS_ASOF_DATE
348 AND party_id <> -1
349 THEN PARTY_ID END ))
350 a2
351 ,MIN(a9) a9
352 ,MIN(a10) a10
353 ,DECODE(SUM(NVL(m,0) + NVL(a3,0)), 0, NULL, SUM(NVL(m,0) + NVL(a3,0))) q
354 ,DECODE(SUM(NVL(n,0) + NVL(a4,0)), 0, NULL, SUM(NVL(n,0) + NVL(a4,0))) r
355 ,DECODE(SUM(NVL(g,0) + NVL(a7,0)), 0, NULL, SUM(NVL(g,0) + NVL(a7,0))) g1
356 ,DECODE(SUM(NVL(h,0) + NVL(a8,0)), 0, NULL, SUM(NVL(h,0) + NVL(a8,0))) h1
357 ,sum(nvl(loginp,0)) loginp
358 ,sum(nvl(loginc,0)) loginc
359 ,sum(nvl(idlep,0)) idlep
360 ,sum(nvl(idlec,0)) idlec
361 ,sum(nvl(availp,0)) availp
362 ,sum(nvl(availc,0)) availc
363 ,sum(nvl(prp,0) ) prp
364 ,sum(nvl(prc,0)) prc
365 ,sum(nvl(contp,0)) contp
366 ,sum(nvl(contc,0)) contc
367
368 FROM ( ';
369
370 l_sqltext := l_sqltext || '
371 SELECT
372 ' || l_column_name || '
373 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_calls_handled_total, 0)
374 c
375 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_calls_handled_total, 0)
376 d
377 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,decode(dialing_method,''PRED'',call_calls_offered_total,0),0)
378 g
379 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,
380 decode(dialing_method,''PRED'',call_calls_offered_total,0), 0)
381 h
382 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,
383 decode(dialing_method,''PRED'',call_calls_abandoned,0)
384 , 0)
385 i
386 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,decode(dialing_method,''PRED'',call_calls_abandoned,0), 0)
387 j
388 /* Added for US Abandon rate */
389 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,decode(dialing_method,''PRED'',call_calls_abandoned_us,0), 0)
390 ius
391 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,decode(dialing_method,''PRED'',call_calls_abandoned_us,0), 0)
392 jus
393 /* End of additions */
394 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,call_calls_transferred, 0)
395 k
396 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,call_calls_transferred, 0)
397 l
398 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,call_calls_handled_total, 0)
399 m
400 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,call_calls_handled_total, 0)
401 n
402 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_wrap_time_nac)
403 s
404 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_wrap_time_nac)
405 t
406 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,call_talk_time)
407 u
408 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,call_talk_time)
409 v
410 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_sr_created)
411 w
412 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_sr_created)
413 x
414 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_leads_created)
415 y
416 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_leads_created)
417 z
418 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_opportunities_created)
419 y1
420 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_opportunities_created)
421 z1
422 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agentcall_pr_count)
423 prc
424 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agentcall_pr_count)
425 prp
426 ,party_id party_id
427 ,calendar.report_date report_date
428 ,NULL a3
429 ,NULL a4
430 ,NULL a7
431 ,NULL a8
432 ,COUNT(DISTINCT(CASE WHEN calendar.report_date = &BIS_CURRENT_ASOF_DATE
433 AND party_id <> -1
434 THEN PARTY_ID END )) OVER()
435 a9
436 ,COUNT(DISTINCT(CASE WHEN calendar.report_date = &BIS_PREVIOUS_ASOF_DATE
437 AND party_id <> -1
438 THEN PARTY_ID END )) OVER()
439 a10
440 ,0 loginp
441 ,0 loginc
442 ,0 idlep
443 ,0 idlec
444 ,0 availp
445 ,0 availc
446 ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agentcall_contact_count)
447 contp
448 ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agentcall_contact_count)
449 contc
450 FROM
451 bix_ao_call_details_mv a,
452 fii_time_rpt_struct calendar
453 WHERE a.row_type = ''C''
454 AND a.time_id = calendar.time_id
455 AND a.period_type_id = calendar.period_type_id
456 AND calendar.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
457 AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) = calendar.record_type_id ';
458
459 l_sqltext := l_sqltext || l_where_clause || '
460 union all
461 /* continue inline view and select session measures */
462 SELECT
463 -999
464 ,NULL c
465 ,NULL d
466 ,NULL g
467 ,NULL h
468 ,NULL i
469 ,NULL j
470 /* Added for US abandon measure */
471 ,NULL ius
472 ,NULL jus
473 /* End of addition */
474 ,NULL k
475 ,NULL l
476 ,NULL m
477 ,NULL n
478 ,NULL s
479 ,NULL t
480 ,NULL u
481 ,NULL v
482 ,NULL w
483 ,NULL x
484 ,NULL y
485 ,NULL z
486 ,NULL y1
487 ,NULL z1
488 ,0 prc
489 ,0 prp
490 ,NULL party_id
491 ,NULL report_date
492 ,0 a3
493 ,0 a4
494 ,0 a7
495 ,0 a8
496 ,0 a9
497 ,0 a10
498 , SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,login_time))
499 loginp
500 , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,login_time))
501 loginc
502 , SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,idle_time))
503 idlep
504 , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,idle_time))
505 idlec
506 , SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,available_time))
507 availp
508 , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,available_time))
509 availc
510 , 0 contp
511 ,0 contc
512 FROM
513 bix_agent_session_f fact,
514 fii_time_rpt_struct calendar
515 WHERE fact.time_id = calendar.time_id
516 AND fact.application_id = 696
517 AND fact.period_type_id = calendar.period_type_id
518 AND calendar.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
519 AND
520 bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) = calendar.record_type_id ';
521
522 l_sqltext := l_sqltext || l_call_where_clause || '
523 group by '||l_column_name|| '
524 UNION ALL
525 /*Continue inline view select continued measures */
526 SELECT
527 ' || l_column_name || '
528 ,NULL c
529 ,NULL d
530 ,NULL g
531 ,NULL h
532 ,NULL i
533 ,NULL j
534 /* Added for US abandon measure */
535 ,NULL ius
536 ,NULL jus
537 /* End of addition */
538 ,NULL k
539 ,NULL l
540 ,NULL m
541 ,NULL n
542 ,NULL s
543 ,NULL t
544 ,NULL u
545 ,NULL v
546 ,NULL w
547 ,NULL x
548 ,NULL y
549 ,NULL z
550 ,NULL y1
551 ,NULL z1
552 ,0 prc
553 ,0 prp
554 ,NULL party_id
555 ,NULL report_date
556 ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),call_cont_calls_handled_tot_na)
557 a3
558 ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')),call_cont_calls_handled_tot_na)
559 a4
560 ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),decode(dialing_method,''PRED'',call_cont_calls_offered_na,0), 0)
561 a7
562 ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')),decode(dialing_method,''PRED'',call_cont_calls_offered_na,0), 0)
563 a8
564 ,NULL a9
565 ,NULL a10
566 ,0 loginp
567 ,0 loginc
568 ,0 idlep
569 ,0 idlec
570 ,0 availp
571 ,0 availc
572 ,0 contp
573 ,0 contc
574 FROM
575 bix_ao_call_details_mv a
576 WHERE row_type = ''C''
577 AND time_id IN (TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),
578 TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')))
579 AND period_type_id = 1 ';
580
581 l_sqltext := l_sqltext || l_where_clause || '
582 )
583 GROUP BY ' || l_column_name || '
584 )
585 ) a , ams_campaign_schedules_vl sched
586 WHERE a.schedule_id= sched.schedule_id (+)
587 GROUP BY DECODE(a.schedule_id ,''-999'',''-999'',NVL(sched.schedule_name, :l_unknown))
588 ) a where a.schedule_name <> ''-999''';
589
590
591 l_sqltext := l_sqltext ||
592 /* ' , ams_campaign_schedules_vl sched
593 WHERE a.schedule_id= sched.schedule_id (+)
594 and a.schedule_id <> ''-999'' */
595 ' &ORDER_BY_CLAUSE ';
596
597 /* Before passing l_sqltext to the calling proc, we trim it up a bit */
598 l_sqltext:=replace(replace(replace(replace(replace(l_sqltext,
599 ' ',' '),' ',' '),' ',' '),' ',' '),' ',' ');
600
601 p_sql_text := l_sqltext;
602
603 l_custom_rec.attribute_name := ':l_unknown' ;
604 l_custom_rec.attribute_value:= l_unknown;
605 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
606 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
607
608 p_custom_output.Extend();
609 p_custom_output(p_custom_output.count) := l_custom_rec;
610
611
612 -- p_custom_output.EXTEND();
613 IF l_call_center IS NOT NULL THEN
614 l_custom_rec.attribute_name := ':l_call_center' ;
615 l_custom_rec.attribute_value:= l_call_center;
616 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
617 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
618
619 p_custom_output.Extend();
620 p_custom_output(p_custom_output.count) := l_custom_rec;
621 END IF;
622 IF l_campaign_id IS NOT NULL
623 THEN
624 l_custom_rec.attribute_name := ':l_campaign_id';
625 l_custom_rec.attribute_value:= l_campaign_id;
626 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
627 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
628
629 p_custom_output.Extend();
630 p_custom_output(p_custom_output.count) := l_custom_rec;
631 END IF;
632
633 IF l_schedule_id IS NOT NULL
634 THEN
635 l_custom_rec.attribute_name := ':l_schedule_id';
636 l_custom_rec.attribute_value:= l_schedule_id;
637 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
638 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
639
640 p_custom_output.Extend();
641 p_custom_output(p_custom_output.count) := l_custom_rec;
642 END IF;
643 IF l_source_code_id IS NOT NULL
644 THEN
645 l_custom_rec.attribute_name := ':l_source_code_id';
646 l_custom_rec.attribute_value:= l_source_code_id;
647 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
648 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
649
650 p_custom_output.Extend();
651 p_custom_output(p_custom_output.count) := l_custom_rec;
652 END IF;
653
654
655
656
657
658
659 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
660 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
661 l_custom_rec.attribute_value := l_view_by;
662
663 p_custom_output.EXTEND();
664 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
665
666
667
668
669 EXCEPTION
670 WHEN OTHERS THEN
671 NULL;
672
673 END GET_SQL;
674 END BIX_PMV_AO_ACTCSCH_RPT_PKG;