[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AI_CACENT_PRTLT_PKG
Source
1 PACKAGE BODY BIX_PMV_AI_CACENT_PRTLT_PKG AS
2 /*$Header: bixicenp.plb 115.11 2003/12/25 00:40:04 anasubra noship $ */
3
4 PROCEDURE GET_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
5 p_custom_sql OUT NOCOPY VARCHAR2,
6 p_custom_output OUT NOCOPY bis_query_attributes_TBL
7 )
8 AS
9 l_sqltext VARCHAR2(32000) ;
10 l_as_of_date DATE;
11 l_period_type varchar2(2000);
12 l_record_type_id NUMBER;
13 l_comp_type varchar2(2000);
14 l_sql_errm varchar2(32000);
15 l_previous_report_start_date DATE;
16 l_current_report_start_date DATE;
17 l_previous_as_of_date DATE;
18 l_period_type_id NUMBER;
19 l_time_id_column VARCHAR2(1000);
20 l_goal NUMBER;
21 l_call_center VARCHAR2(3000);
22 l_classification VARCHAR2(3000);
23 l_other_group VARCHAR2(30);
24 l_call_where_clause VARCHAR2(3000);
25 l_session_where_clause VARCHAR2(3000);
26 l_dnis VARCHAR2(3000);
27 l_unknown VARCHAR2(50);
28 l_view_by VARCHAR2(3000);
29
30 l_custom_rec BIS_QUERY_ATTRIBUTES := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
31
32 BEGIN
33 --
34 --Initialize p_custom_output
35 --
36 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
37
38 -- Get the parameters
39
40 BIX_PMV_DBI_UTL_PKG.get_ai_page_params( p_page_parameter_tbl,
41 l_as_of_date,
42 l_period_type,
43 l_record_type_id,
44 l_comp_type,
45 l_call_center,
46 l_classification,
47 l_dnis,
48 l_view_by
49 );
50
51 IF l_call_center IS NOT NULL THEN
52 l_call_where_clause := ' AND mv.server_group_id IN (:l_call_center) ';
53 l_session_where_clause := ' AND mv.server_group_id IN (:l_call_center) ';
54 END IF;
55
56 IF l_classification IS NOT NULL THEN
57 l_call_where_clause := l_call_where_clause || ' AND mv.classification_value IN (:l_classification) ';
58 END IF;
59
60
61 IF l_dnis IS NOT NULL THEN
62 IF l_dnis = '''INBOUND'''
63 THEN
64 l_call_where_clause := l_call_where_clause ||
65 ' AND mv.dnis_name <> ''OUTBOUND'' ';
66 ELSIF l_dnis = '''OUTBOUND'''
67 THEN
68 l_call_where_clause := l_call_where_clause ||
69 ' AND mv.dnis_name = ''OUTBOUND'' ';
70 ELSE
71 l_call_where_clause := l_call_where_clause ||
72 ' AND mv.dnis_name IN (:l_dnis) ';
73 END IF;
74 END IF;
75 l_other_group := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_ALLACCT');
76
77 IF l_other_group IS NULL OR l_other_group = 'BIX_PMV_ALLACCT'
78 THEN
79 l_other_group := 'Others';
80 END IF;
81
82 l_unknown := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_UNKNOWN');
83
84 IF l_unknown IS NULL OR l_unknown = 'BIX_PMV_UNKNOWN'
85 THEN
86 l_unknown := 'Unknown';
87 END IF;
88
89 --insert into bixtest
90 --values ('l_period_type ' || l_period_type || ' l_comp_type ' || l_comp_type);
91 --commit;
92
93 l_sqltext := '
94 SELECT DECODE(greatest(RANKING,10),10,group_name,
95 decode(group_name,:l_unknown,
96 ''</a><nobr>''|| :l_unknown ||''</nobr><a href=#>'',
97 ''</a><nobr>''|| :l_other_group ||''</nobr><a href=#>''
98 )
99 ) VIEWBY,
100 round(sum(in_ansgoal)*100/decode(sum(IN_OFFRD),0,NULL,sum(IN_OFFRD)),1) BIX_PMV_AI_SL,
101 round(sum(sum(in_ansgoal)) over()*100/
102 decode(sum(sum(IN_OFFRD)) over(),0,NULL,sum(sum(IN_OFFRD)) over()),1) BIX_PMV_TOTAL1,
103 sum(in_fresh_hand) BIX_PMV_AI_INCALLHAND,
104 sum(sum(in_fresh_hand)) over() BIX_PMV_TOTAL2,
105 round(sum(IN_QTOANS)/
106 decode(sum(in_hand),0,NULL,sum(in_hand)),1) BIX_PMV_AI_SPANS,
107 round(sum(sum(IN_QTOANS)) over()/
108 decode(sum(sum(in_hand)) over(),0,NULL,sum(sum(in_hand)) over()),1) BIX_PMV_TOTAL3,
109 round(sum(IN_ABAND)*100/
110 decode(sum(IN_OFFRD),0,NULL,sum(IN_OFFRD)),1) BIX_PMV_AI_ABANRATE,
111 round(sum(sum(IN_ABAND)) over()*100/
112 decode(sum(sum(IN_OFFRD)) over(),0,NULL,sum(sum(IN_OFFRD)) over()),1) BIX_PMV_TOTAL4,
113 round(sum(talk)/
114 decode(sum(hand),0,NULL,sum(hand)),1) BIX_PMV_AI_AVGTALK,
115 round(sum(sum(talk)) over()/
116 decode(sum(sum(hand)) over(),0,NULL,sum(sum(hand)) over()),1) BIX_PMV_TOTAL5,
117 sum(sr) BIX_PMV_AI_SRCR,
118 sum(sum(sr)) over() BIX_PMV_TOTAL6,
119 sum(lead) BIX_PMV_AI_LECR,
120 sum(sum(lead)) over() BIX_PMV_TOTAL7 ,
121 sum(opp) BIX_PMV_AI_OPCR,
122 sum(sum(opp)) over() BIX_PMV_TOTAL8
123 FROM
124 (
125 --
126 --Additional inline view needed to compute RANK due to continued measures
127 --
128 SELECT nvl(group_name,:l_unknown) group_name,
129 nvl(sum(IN_OFFRD),0) IN_OFFRD, nvl(sum(in_ansgoal),0) in_ansgoal, nvl(sum(in_hand),0) in_hand,
130 nvl(sum(IN_FRESH_HAND),0) IN_FRESH_HAND,
131 nvl(sum(hand),0) hand, nvl(sum(IN_QTOANS),0) IN_QTOANS, nvl(sum(IN_ABAND),0) IN_ABAND,
132 nvl(sum(talk),0) talk, nvl(sum(sr),0) sr, nvl(sum(lead),0) lead, nvl(sum(opp),0) opp,
133 decode(group_name,NULL,11,
134 RANK() OVER (ORDER BY nvl(sum(in_fresh_hand),0) DESC, group_name)
135 ) RANKING
136 FROM
137 (
138 SELECT server_group_id server_group_id,
139 sum(decode(mv.media_item_type,
140 ''TELE_INB'',CALL_CALLS_OFFERED_TOTAL,
141 ''TELE_DIRECT'',CALL_CALLS_OFFERED_TOTAL,
142 0)
143 ) IN_OFFRD,
144 sum(decode(mv.media_item_type,
145 ''TELE_INB'',AGENT_CALLS_ANSWERED_BY_GOAL,
146 ''TELE_DIRECT'',AGENT_CALLS_ANSWERED_BY_GOAL,
147 0)
148 ) IN_ANSGOAL,
149 sum(decode(mv.media_item_type,
150 ''TELE_INB'',AGENT_CALLS_HANDLED_TOTAL,
151 ''TELE_DIRECT'',AGENT_CALLS_HANDLED_TOTAL,
152 0)
153 ) IN_HAND,
154 sum(decode(mv.media_item_type,
155 ''TELE_INB'',AGENT_CALLS_HANDLED_TOTAL,
156 ''TELE_DIRECT'',AGENT_CALLS_HANDLED_TOTAL,
157 0)
158 ) IN_FRESH_HAND,
159 sum(AGENT_CALLS_HANDLED_TOTAL) HAND,
160 sum(decode(mv.media_item_type,
161 ''TELE_INB'',CALL_TOT_QUEUE_TO_ANSWER,
162 ''TELE_DIRECT'',CALL_TOT_QUEUE_TO_ANSWER,
163 0)
164 ) IN_QTOANS,
165 sum(decode(mv.media_item_type,
166 ''TELE_INB'',CALL_CALLS_ABANDONED,
167 ''TELE_DIRECT'',CALL_CALLS_ABANDONED,
168 0)
169 ) IN_ABAND,
170 sum(CALL_TALK_TIME) TALK,
171 sum(AGENT_SR_CREATED) SR,
172 sum(AGENT_LEADS_CREATED) LEAD,
173 sum(AGENT_OPPORTUNITIES_CREATED) OPP
174 FROM bix_ai_call_details_mv mv,
175 fii_time_rpt_struct cal
176 WHERE mv.time_id = cal.time_id
177 AND mv.row_type = :l_row_type
178 AND mv.period_type_id = cal.period_type_id
179 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) =
180 cal.record_type_id
181 AND cal.report_date = &BIS_CURRENT_ASOF_DATE ';
182
183 l_sqltext := l_sqltext || l_call_where_clause ||
184 '
185 GROUP BY mv.server_group_id
186 UNION ALL
187 SELECT server_group_id server_group_id,
188 nvl(sum(decode(mv.media_item_type,
189 ''TELE_INB'',CALL_CONT_CALLS_OFFERED_NA,
190 ''TELE_DIRECT'', CALL_CONT_CALLS_OFFERED_NA,
191 0)
192 ),0) IN_OFFRD,
193 0 IN_ANSGOAL,
194 nvl(sum(decode(mv.media_item_type,
195 ''TELE_INB'',AGENT_CONT_CALLS_HAND_NA,
196 ''TELE_DIRECT'', AGENT_CONT_CALLS_HAND_NA,
197 0)
198 ),0) IN_HAND,
199 0 IN_FRESH_HAND,
200 nvl(sum(AGENT_CONT_CALLS_HAND_NA),0) HAND,
201 0 IN_QTOANS,
202 0 IN_ABAND,
203 0 TALK,
204 0 SR,
205 0 LEAD,
206 0 OPP
207 FROM bix_ai_call_details_mv mv
208 WHERE mv.time_id = to_char(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')
209 AND mv.row_type = :l_row_type
210 AND mv.period_type_id = :l_period_type_id
211 AND period_start_time = :l_period_start_time ';
212
213 l_sqltext := l_sqltext || l_call_where_clause ||
214 '
215 GROUP BY mv.server_group_id
216 ) summ,
217 IEO_SVR_GROUPS SG
218 WHERE sg.server_group_id (+) = summ.server_group_id
219 GROUP BY group_name
220 )
221 GROUP BY DECODE(greatest(RANKING,10),10,RANKING,decode(group_name,:l_unknown,11,12)),
222 DECODE(greatest(RANKING,10),10,group_name,
223 decode(group_name,:l_unknown,
224 ''</a><nobr>''|| :l_unknown ||''</nobr><a href=#>'',
225 ''</a><nobr>''|| :l_other_group ||''</nobr><a href=#>''
226 )
227 )
228 ORDER BY DECODE(greatest(RANKING,10),10,RANKING,decode(group_name,:l_unknown,11,12)),
229 DECODE(greatest(RANKING,10),10,group_name,
230 decode(group_name,:l_unknown,
231 ''</a><nobr>''|| :l_unknown ||''</nobr><a href=#>'',
232 ''</a><nobr>''|| :l_other_group ||''</nobr><a href=#>''
233 )
234 ) ';
235
236 p_custom_sql := l_sqltext;
237
238 l_custom_rec.attribute_name := ':l_row_type';
239 l_custom_rec.attribute_value:= 'CDR';
240 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
241 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
242
243 p_custom_output.Extend();
244 p_custom_output(p_custom_output.count) := l_custom_rec;
245
246 l_custom_rec.attribute_name := ':l_other_group' ;
247 l_custom_rec.attribute_value:= l_other_group;
248 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
249 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
250
251 p_custom_output.Extend();
252 p_custom_output(p_custom_output.count) := l_custom_rec;
253
254 IF l_call_center IS NOT NULL
255 THEN
256 l_custom_rec.attribute_name := ':l_call_center';
257 l_custom_rec.attribute_value:= l_call_center;
258 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
259 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
260
261 p_custom_output.Extend();
262 p_custom_output(p_custom_output.count) := l_custom_rec;
263 END IF;
264
265 IF l_classification IS NOT NULL
266 THEN
267 l_custom_rec.attribute_name := ':l_classification';
268 l_custom_rec.attribute_value:= l_classification;
269 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
270 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
271
272 p_custom_output.EXTEND;
273 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
274 END IF;
275
276 IF l_dnis IS NOT NULL AND l_dnis NOT IN ('INBOUND','OUTBOUND')
277 THEN
278 l_custom_rec.attribute_name := ':l_dnis';
279 l_custom_rec.attribute_value:= l_dnis;
280 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
281 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
282
283 p_custom_output.Extend();
284 p_custom_output(p_custom_output.count) := l_custom_rec;
285 END IF;
286
287 l_custom_rec.attribute_name := ':l_period_type_id';
288 l_custom_rec.attribute_value:= 1;
289 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
290 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
291
292 p_custom_output.Extend();
293 p_custom_output(p_custom_output.count) := l_custom_rec;
294
295 l_custom_rec.attribute_name := ':l_period_start_time';
296 l_custom_rec.attribute_value:= '00:00';
297 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
298 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
299
300 p_custom_output.EXTEND;
301 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
302
303 l_custom_rec.attribute_name := ':l_unknown';
304 l_custom_rec.attribute_value:= l_unknown;
305 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
306 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
307
308 p_custom_output.EXTEND;
309 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
310
311 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
312 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
313 l_custom_rec.attribute_value := 'BIX_TELEPHONY+BIX_CALL_CENTER';
314
315 p_custom_output.EXTEND;
316 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
317
318 EXCEPTION
319 WHEN OTHERS THEN
320 l_sql_errm := SQLERRM;
321 --insert into bixtest values (l_sql_errm);
322 --commit;
323 NULL;
324 END GET_SQL;
325 END BIX_PMV_AI_CACENT_PRTLT_PKG;