[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AO_AGTDTL_RPT_PKG
Source
1 PACKAGE BODY BIX_PMV_AO_AGTDTL_RPT_PKG AS
2 /*$Header: bixoagdr.plb 115.6 2004/05/05 07:55:34 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
14 l_as_of_date DATE;
15 l_period_type varchar2(2000);
16 l_comp_type varchar2(2000);
17 l_period_type_id NUMBER := 1;
18 l_call_center VARCHAR2(3000);
19 l_campaign_id varchar2(3000);
20 l_schedule_id varchar2(3000);
21 l_source_code_id varchar2(3000);
22 l_agent_group VARCHAR2(3000);
23 l_call_where_clause VARCHAR2(3000);
24 l_session_where_clause VARCHAR2(3000);
25 l_campaign_where_clause VARCHAR2(3000);
26 l_source_code_where_clause VARCHAR2(3000);
27 l_schedule_where_clause VARCHAR2(3000);
28 l_view_by VARCHAR2(3000);
29 l_record_type_id NUMBER;
30
31 l_unknown VARCHAR2(50);
32 l_custom_rec BIS_QUERY_ATTRIBUTES;
33
34 BEGIN
35
36 /* Initialize the variables */
37 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
38 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
39 l_where_clause := NULL;
40 l_call_center := NULL;
41 l_agent_group := NULL;
42
43
44 l_unknown := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_UNKNOWN');
45
46 IF l_unknown IS NULL OR l_unknown = 'BIX_PMV_UNKNOWN'
47 THEN
48 l_unknown := 'Unknown';
49 END IF;
50
51 -- Get the parameters
52
53 BIX_PMV_DBI_UTL_PKG.get_ao_page_params( p_page_parameter_tbl,
54 l_as_of_date,
55 l_period_type,
56 l_record_type_id,
57 l_comp_type,
58 l_call_center,
59 l_campaign_id,
60 l_schedule_id,
61 l_source_code_id,
62 l_agent_group,
63 l_view_by
64 );
65
66 IF l_call_center IS NOT NULL THEN
67 l_call_where_clause := ' AND fact.server_group_id IN (:l_call_center) ';
68 END IF;
69 IF l_campaign_id IS NOT NULL THEN
70 l_campaign_where_clause := ' AND fact.campaign_id IN (:l_campaign_id) ';
71 END IF;
72 IF l_schedule_id IS NOT NULL THEN
73 l_schedule_where_clause := ' AND fact.schedule_id IN (:l_schedule_id) ';
74 END IF;
75 IF l_source_code_id IS NOT NULL THEN
76 l_source_code_where_clause := ' AND fact.campaign_id in (select source_code_for_id from
77 ams_source_codes where source_code_id IN (:l_source_code_id) and arc_source_code_for=''CAMP'' and active_flag=''Y'') ';
78
79 END IF;
80
81
82
83 /* Agent Group where clause is not formed here as the table bix_call_details_f and bix_agent_session_f */
84 /* have two different column names , resource_id and agent_id respectively , for the agent */
85
86 l_sqltext :=
87 'SELECT
88 res.resource_name BIX_PMV_AO_AGENT
89 ,ROUND(SUM(NVL(fact.login,0))/3600, 1) BIX_PMV_AO_LOGIN_CP
90 ,ROUND(nvl((SUM(SUM(NVL(fact.login,0))) OVER())/3600,0), 1) BIX_PMV_TOTAL1
91 , ROUND(nvl(SUM(NVL(fact.login,0))*100/DECODE(SUM(SUM(fact.login)) OVER(),0,NULL,SUM(SUM(fact.login)) OVER()),0), 1)
92 BIX_PMV_AO_PERTOTAL1
93 ,ROUND(SUM(NVL(fact.login,0) - NVL(fact.idle, 0)) /
94 DECODE(SUM(NVL(fact.login,0)), 0, NULL, SUM(NVL(fact.login,0))) * 100, 1)
95 BIX_PMV_AO_AVAILRATE_CP
96 ,ROUND
97 ( sum( SUM(NVL(fact.login,0) - NVL(fact.idle, 0))) over() *100/
98 DECODE(sum(SUM(NVL(fact.login,0))) over() , 0, NULL,
99 sum( SUM(NVL(fact.login,0))) over() )
100 ,1)
101 BIX_PMV_TOTAL2
102 ,ROUND((SUM(NVL(fact.login,0) - NVL(fact.idle, 0)) /
103 DECODE(SUM(NVL(fact.login,0)), 0, NULL, SUM(NVL(fact.login,0)))) * 100
104 -
105 AVG( (SUM(NVL(fact.login,0) - NVL(fact.idle, 0)))*100/
106 DECODE(SUM(NVL(fact.login,0)) , 0, NULL,
107 SUM(NVL(fact.login,0))) ) OVER()
108 , 1) BIX_PMV_AO_AVAILRATE_VAR
109 ,ROUND(
110 SUM( NVL(fact.login,0) - NVL(fact.idle, 0) - NVL(fact.available,0) ) /
111 DECODE(SUM(fact.login), 0, NULL, SUM(fact.login))
112 * 100
113 , 1) BIX_PMV_AO_UTILRATE_CP
114 , ROUND(
115 SUM(SUM( NVL(fact.login,0) - NVL(fact.idle, 0) - NVL(fact.available,0) )) over() *100
116 /
117 DECODE(SUM(SUM(fact.login)) over(), 0, NULL, SUM(SUM(fact.login)) over())
118 , 1)
119 BIX_PMV_TOTAL3
120 ,ROUND(
121 SUM( NVL(fact.login,0) - NVL(fact.idle, 0) - NVL(fact.available,0) )*100 /
122 DECODE(SUM(fact.login), 0, NULL, SUM(fact.login))
123 -
124 AVG(
125 SUM( NVL(fact.login,0) - NVL(fact.idle, 0) - NVL(fact.available,0) )*100
126 /
127 DECODE(SUM(fact.login), 0, NULL, SUM(fact.login)) )
128 over()
129 ,1) BIX_PMV_AO_UTILRATE_VAR
130 ,SUM(NVL(fact.tot_calls,0)) BIX_PMV_AO_OUTCALLHAND_CP
131 ,SUM(SUM(NVL(fact.tot_calls,0))) OVER() BIX_PMV_TOTAL4
132 ,ROUND(SUM(fact.tot_calls)*100/decode(SUM(SUM(fact.tot_calls)) OVER(),0,null,SUM(SUM(fact.tot_calls)) OVER()),1)
133 BIX_PMV_AO_PERTOTAL2
134 ,ROUND(SUM(NVL(fact.tot_calls,0)) /
135 DECODE(SUM(NVL(fact.login,0))/3600, 0, NULL, SUM(NVL(fact.login,0))/3600), 1)
136 BIX_PMV_AO_OUTCALLHAND_PAH_CP
137 ,ROUND(SUM(SUM(NVL(fact.tot_calls,0))) OVER() /
138 DECODE(SUM(SUM(NVL(fact.login,0))) OVER()/3600, 0, NULL, SUM(SUM(NVL(fact.login,0))) OVER()/3600), 1)
139 BIX_PMV_TOTAL5
140 ,ROUND(SUM(NVL(fact.tot_calls,0)) /
141 DECODE(SUM(NVL(fact.login,0))/3600, 0, NULL, SUM(NVL(fact.login,0))/3600), 1) -
142 ROUND(SUM(SUM(NVL(fact.tot_calls,0))) OVER() /
143 DECODE(SUM(SUM(NVL(fact.login,0))) OVER()/3600, 0, NULL, SUM(SUM(NVL(fact.login,0))) OVER()/3600), 1)
144 BIX_PMV_AO_OUTCALLHAND_PAH_VAR
145 ,ROUND(
146 nvl(
147 SUM(NVL(fact.talk,0)) /
148 DECODE(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) + NVL(fact.calls_tran_conf,0)
149 + NVL(cont_calls_tc,0)), 0, NULL, SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) +
150 NVL(fact.calls_tran_conf,0) + NVL(cont_calls_tc,0))),0), 1)
151 BIX_PMV_AO_AVGTALK_CP
152 ,ROUND(nvl(
153 SUM(SUM(NVL(fact.talk,0))) OVER()/
154 DECODE(SUM(SUM(NVL(tot_calls,0) +
155 NVL(fact.cont_calls_hand,0)
156 )) OVER(), 0, NULL,
157 SUM(SUM(NVL(tot_calls,0) +
158 NVL(fact.cont_calls_hand,0)
159 )) OVER()),0), 1) BIX_PMV_TOTAL6
160 ,ROUND(SUM(NVL(fact.talk,0)) /
161 DECODE(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) + NVL(fact.calls_tran_conf,0)
162 + NVL(cont_calls_tc,0)), 0, NULL, SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) +
163 NVL(fact.calls_tran_conf,0) + NVL(cont_calls_tc,0))) -
164 SUM(SUM(NVL(fact.talk,0))) OVER() /
165 DECODE(SUM(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0)
166 )) OVER(), 0, NULL, SUM(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) )) OVER()), 1)
167 BIX_PMV_AO_AVGTALK_VAR
168 ,ROUND(SUM(NVL(fact.wrap,0)) /
169 DECODE(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) + NVL(fact.calls_tran_conf,0)
170 + NVL(cont_calls_tc,0)), 0, NULL, SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) +
171 NVL(fact.calls_tran_conf,0) + NVL(cont_calls_tc,0))), 1)
172 BIX_PMV_AO_AVGWRAP_CP
173 ,ROUND(SUM(SUM(NVL(fact.wrap,0))) OVER() /
174 DECODE(SUM(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0)
175 )) OVER(), 0, NULL, SUM(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) )) OVER()), 1)
176 BIX_PMV_TOTAL7
177 ,ROUND(SUM(NVL(fact.wrap,0)) /
178 DECODE(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) + NVL(fact.calls_tran_conf,0)
179 + NVL(cont_calls_tc,0)), 0, NULL, SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) +
180 NVL(fact.calls_tran_conf,0) + NVL(cont_calls_tc,0))) -
181 SUM(SUM(NVL(fact.wrap,0))) OVER() /
182 DECODE(SUM(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0)
183 )) OVER(), 0, NULL, SUM(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) )) OVER()), 1)
184 BIX_PMV_AO_AVGWRAP_VAR
185 ,SUM(NVL(fact.sr,0))
186 BIX_PMV_AO_SRCR_CP
187 ,SUM(SUM(NVL(fact.sr,0))) OVER()
188 BIX_PMV_TOTAL8
189 ,ROUND(nvl(SUM(NVL(fact.sr,0))*100/
190 DECODE(SUM(SUM(NVL(fact.sr,0))) OVER(),0,NULL,
191 SUM(SUM(NVL(fact.sr,0))) OVER()),0),1) BIX_PMV_AO_PERTOTAL3
192 ,SUM(NVL(fact.leads,0))
193 BIX_PMV_AO_LECR_CP
194 ,SUM(SUM(NVL(fact.leads,0))) OVER()
195 BIX_PMV_TOTAL9
196 ,ROUND(SUM(NVL(fact.leads,0))*100/
197 DECODE(SUM(SUM(NVL(fact.leads,0))) OVER(),0,NULL,
198 SUM(SUM(NVL(fact.leads,0))) OVER()),1) BIX_PMV_AO_PERTOTAL4
199 ,
200 ROUND(SUM(nvl(fact.calls_tran_conf,0))/
201 DECODE(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) + NVL(fact.calls_tran_conf,0)
202 + NVL(cont_calls_tc,0)),0,NULL,SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) +
203 NVL(fact.calls_tran_conf,0)
204 + NVL(cont_calls_tc,0))
205 ),1) BIX_PMV_AO_TRNSFR_CP
206 , ROUND(AVG(SUM(nvl(fact.calls_tran_conf,0))/
207 DECODE(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) + NVL(fact.calls_tran_conf,0)
208 + NVL(cont_calls_tc,0)),0,NULL,SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) +
209 NVL(fact.calls_tran_conf,0)
210 + NVL(cont_calls_tc,0))
211 )) OVER() ,1) BIX_PMV_TOTAL10
212 , ROUND(SUM(nvl(fact.calls_tran_conf,0))/
213 DECODE(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) + NVL(fact.calls_tran_conf,0)
214 + NVL(cont_calls_tc,0)),0,NULL,SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) +
215 NVL(fact.calls_tran_conf,0)
216 + NVL(cont_calls_tc,0))
217 )
218 -
219 AVG(SUM(nvl(fact.calls_tran_conf,0))/
220 DECODE(SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) + NVL(fact.calls_tran_conf,0)
221 + NVL(cont_calls_tc,0)),0,NULL,SUM(NVL(tot_calls,0) + NVL(fact.cont_calls_hand,0) +
222 NVL(fact.calls_tran_conf,0)
223 + NVL(cont_calls_tc,0))
224 )) OVER(),1) BIX_PMV_AO_TRNSFR_VAR
225 ,NVL(SUM(NVL(fact.oppr,0)),0) BIX_PMV_AO_OPCR_CP
226 ,SUM(SUM(NVL(fact.oppr,0))) OVER() BIX_PMV_TOTAL25
227 ,ROUND(SUM(NVL(fact.oppr,0))*100/
228 DECODE(SUM(SUM(NVL(fact.oppr,0))) OVER(),0,NULL,
229 SUM(SUM(NVL(fact.oppr,0))) OVER()),1) BIX_PMV_AO_PERTOTAL5
230 FROM (
231 SELECT
232 fact.resource_id
233 agent_id
234 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_talk_time_nac))
235 talk
236 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.call_talk_time))
237 calltalk
238 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_wrap_time_nac))
239 wrap
240 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_calls_handled_total))
241 tot_calls
242 ,NULL cont_calls_hand
243 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_calls_tran_conf_to_nac))
244 calls_tran_conf
245 ,NULL cont_calls_tc
246 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_leads_created))
247 leads
248 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_opportunities_created))
249 oppr
250 ,SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.agent_sr_created))
251 sr
252 ,NULL login
253 ,NULL idle
254 ,NULL available
255 FROM
256 bix_ao_call_details_mv fact,
257 fii_time_rpt_struct calendar
258 WHERE fact.row_type = ''CR''
259 AND fact.time_id = calendar.time_id
260 AND fact.period_type_id = calendar.period_type_id
261 AND calendar.report_date = &BIS_CURRENT_ASOF_DATE
262 AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) = calendar.record_type_id ';
263
264 l_sqltext := l_sqltext || l_call_where_clause ||l_source_code_where_clause;
265
266 IF l_agent_group IS NOT NULL THEN
267 l_sqltext := l_sqltext ||
268 ' AND EXISTS (
269 SELECT 1
270 FROM jtf_rs_group_members mem
271 WHERE fact.resource_id = mem.resource_id
272 AND mem.group_id IN (:l_agent_group)
273 AND nvl(mem.delete_flag, ''N'') <> ''Y'' )';
274 END IF;
275
276 l_sqltext := l_sqltext || '
277 GROUP BY fact.resource_id
278 UNION ALL
279 SELECT
280 fact.agent_id
281 agent_id
282 , NULL talk
283 , NULL calltalk
284 , NULL wrap
285 , NULL tot_calls
286 , NULL cont_calls_hand
287 , NULL calls_tran_conf
288 , NULL cont_calls_tc
289 , NULL leads
290 , NULL oppr
291 , NULL sr
292 , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.login_time))
293 login
294 , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.idle_time))
295 idle
296 , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,fact.available_time))
297 available
298 FROM
299 bix_agent_session_f fact,
300 fii_time_rpt_struct calendar
301 WHERE fact.time_id = calendar.time_id
302 AND fact.application_id = 696
303 AND fact.period_type_id = calendar.period_type_id
304 AND calendar.report_date = &BIS_CURRENT_ASOF_DATE
305 AND bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) = calendar.record_type_id ';
306
307 l_sqltext := l_sqltext || l_call_where_clause ;
308
309 IF l_agent_group IS NOT NULL THEN
310 l_sqltext := l_sqltext ||
311 ' AND EXISTS (
312 SELECT 1
313 FROM jtf_rs_group_members mem
314 WHERE fact.agent_id = mem.resource_id
315 AND mem.group_id IN (:l_agent_group)
316 AND nvl(mem.delete_flag, ''N'') <> ''Y'' )';
317 END IF;
318
319 l_sqltext := l_sqltext || '
320 GROUP BY fact.agent_id
321 UNION ALL
322 SELECT
323 fact.resource_id
324 agent_id
325 , NULL talk
326 , NULL calltalk
327 , NULL wrap
328 , NULL tot_calls
329 , SUM(fact.agent_cont_calls_hand_na)
330 cont_calls_hand
331 , NULL calls_tran_conf
332 , SUM(fact.agent_cont_calls_tc_na)
333 cont_calls_tc
334 , NULL leads
335 , NULL oppr
336 , NULL sr
337 , NULL login
338 , NULL idle
339 , NULL available
340 FROM
341 bix_ao_call_details_mv fact
342 WHERE fact.row_type = ''CR''
343 AND fact.time_id = TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J''))
344 AND fact.period_type_id = 1 ';
345
346 l_sqltext := l_sqltext || l_call_where_clause ||l_source_code_where_clause;
347
348 IF l_agent_group IS NOT NULL THEN
349 l_sqltext := l_sqltext ||
350 ' AND EXISTS (
351 SELECT 1
352 FROM jtf_rs_group_members mem
353 WHERE fact.resource_id = mem.resource_id
354 AND mem.group_id IN (:l_agent_group)
355 AND nvl(mem.delete_flag, ''N'') <> ''Y'' )';
356 END IF;
357
358 l_sqltext := l_sqltext || '
359 GROUP BY fact.resource_id
360 ) fact, jtf_rs_resource_extns_vl res
361 WHERE fact.agent_id = res.resource_id
362 GROUP BY res.resource_name &ORDER_BY_CLAUSE ';
363
364
365 /* Before passing l_sqltext to the calling proc, we trim it up a bit */
366 l_sqltext:=replace(replace(replace(replace(replace(l_sqltext,
367 ' ',' '),' ',' '),' ',' '),' ',' '),' ',' ');
368
369 p_sql_text := l_sqltext;
370
371 p_custom_output.EXTEND();
372 IF l_agent_group IS NOT NULL THEN
373 l_custom_rec.attribute_name := ':l_agent_group' ;
374 l_custom_rec.attribute_value:= l_agent_group;
375 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
376 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
377
378 p_custom_output.Extend();
379 p_custom_output(p_custom_output.count) := l_custom_rec;
380 END IF;
381
382 IF l_call_center IS NOT NULL THEN
383 l_custom_rec.attribute_name := ':l_call_center' ;
384 l_custom_rec.attribute_value:= l_call_center;
385 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
386 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
387
388 p_custom_output.Extend();
389 p_custom_output(p_custom_output.count) := l_custom_rec;
390 END IF;
391
392 IF l_campaign_id IS NOT NULL
393 THEN
394 l_custom_rec.attribute_name := ':l_campaign_id';
395 l_custom_rec.attribute_value:= l_campaign_id;
396 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
397 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
398
399 p_custom_output.Extend();
400 p_custom_output(p_custom_output.count) := l_custom_rec;
401 END IF;
402
403 IF l_schedule_id IS NOT NULL
404 THEN
405 l_custom_rec.attribute_name := ':l_schedule_id';
406 l_custom_rec.attribute_value:= l_schedule_id;
407 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
408 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
409
410 p_custom_output.Extend();
411 p_custom_output(p_custom_output.count) := l_custom_rec;
412 END IF;
413 IF l_source_code_id IS NOT NULL
414 THEN
415 l_custom_rec.attribute_name := ':l_source_code_id';
416 l_custom_rec.attribute_value:= l_source_code_id;
417 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
418 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
419
420 p_custom_output.Extend();
421 p_custom_output(p_custom_output.count) := l_custom_rec;
422 END IF;
423
424
425 EXCEPTION
426 WHEN OTHERS THEN
427 NULL;
428 END GET_SQL;
429 END BIX_PMV_AO_AGTDTL_RPT_PKG;
430
431
432
433
434
435