[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AI_CACLASS_PRTLT_PKG
Source
1 PACKAGE BODY BIX_PMV_AI_CACLASS_PRTLT_PKG AS
2 /*$Header: bixiclap.plb 115.11 2003/12/25 00:39:52 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_class 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
76 l_other_class := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_ALLACCT');
77
78 IF l_other_class IS NULL OR l_other_class = 'BIX_PMV_ALLACCT'
79 THEN
80 l_other_class := 'Others';
81 END IF;
82
83 l_unknown := FND_MESSAGE.GET_STRING('BIX','BIX_PMV_UNKNOWN');
84
85 IF l_unknown IS NULL OR l_unknown = 'BIX_PMV_UNKNOWN'
86 THEN
87 l_unknown := 'Unknown';
88 END IF;
89
90 --insert into bixtest
91 --values ('l_period_type ' || l_period_type || ' l_comp_type ' || l_comp_type);
92 --commit;
93
94 l_sqltext := '
95 SELECT DECODE(greatest(RANKING,10),10,class,
96 decode(class,:l_unknown,
97 ''</a><nobr>''|| :l_unknown ||''</nobr><a href=#>'',
98 ''</a><nobr>''|| :l_other_class ||'' </nobr><a href=#>''
99 )
100 ) VIEWBY,
101 round(sum(in_ansgoal)*100/decode(sum(IN_OFFRD),0,NULL,sum(IN_OFFRD)),1) BIX_PMV_AI_SL,
102 round(sum(sum(in_ansgoal)) over()*100/
103 decode(sum(sum(IN_OFFRD)) over(),0,NULL,sum(sum(IN_OFFRD)) over()),1) BIX_PMV_TOTAL1,
104 sum(in_fresh_hand) BIX_PMV_AI_INCALLHAND,
105 sum(sum(in_fresh_hand)) over() BIX_PMV_TOTAL2,
106 round(sum(IN_QTOANS)/
107 decode(sum(in_hand),0,NULL,sum(in_hand)),1) BIX_PMV_AI_SPANS,
108 round(sum(sum(IN_QTOANS)) over()/
109 decode(sum(sum(in_hand)) over(),0,NULL,sum(sum(in_hand)) over()),1) BIX_PMV_TOTAL3,
110 round(sum(IN_ABAND)*100/
111 decode(sum(IN_OFFRD),0,NULL,sum(IN_OFFRD)),1) BIX_PMV_AI_ABANRATE,
112 round(sum(sum(IN_ABAND)) over()*100/
113 decode(sum(sum(IN_OFFRD)) over(),0,NULL,sum(sum(IN_OFFRD)) over()),1) BIX_PMV_TOTAL4,
114 round(sum(talk)/
115 decode(sum(hand),0,NULL,sum(hand)),1) BIX_PMV_AI_AVGTALK,
116 round(sum(sum(talk)) over()/
117 decode(sum(sum(hand)) over(),0,NULL,sum(sum(hand)) over()),1) BIX_PMV_TOTAL5,
118 sum(sr) BIX_PMV_AI_SRCR,
119 sum(sum(sr)) over() BIX_PMV_TOTAL6,
120 sum(lead) BIX_PMV_AI_LECR,
121 sum(sum(lead)) over() BIX_PMV_TOTAL7,
122 sum(opp) BIX_PMV_AI_OPCR,
123 sum(sum(opp)) over() BIX_PMV_TOTAL8
124 FROM
125 (
126 --
127 --Additional inline view needed to compute RANK due to continued measures
128 --
129 SELECT nvl(class,:l_unknown) class,
130 nvl(sum(IN_OFFRD),0) IN_OFFRD, nvl(sum(in_ansgoal),0) in_ansgoal, nvl(sum(in_hand),0) in_hand,
131 nvl(sum(in_fresh_hand),0) IN_FRESH_HAND,
132 nvl(sum(hand),0) hand, nvl(sum(IN_QTOANS),0) IN_QTOANS, nvl(sum(IN_ABAND),0) IN_ABAND,
133 nvl(sum(talk),0) talk, nvl(sum(sr),0) sr, nvl(sum(lead),0) lead, nvl(sum(opp),0) opp,
134 decode(class,NULL,11,
135 RANK() OVER (ORDER BY nvl(sum(in_fresh_hand),0) DESC, class)
136 ) RANKING
137 FROM
138 (
139 SELECT classification_value class,
140 sum(decode(mv.media_item_type,
141 ''TELE_INB'',CALL_CALLS_OFFERED_TOTAL,
142 ''TELE_DIRECT'',CALL_CALLS_OFFERED_TOTAL,
143 0)
144 ) IN_OFFRD,
145 sum(decode(mv.media_item_type,
146 ''TELE_INB'',AGENT_CALLS_ANSWERED_BY_GOAL,
147 ''TELE_DIRECT'',AGENT_CALLS_ANSWERED_BY_GOAL,
148 0)
149 ) IN_ANSGOAL,
150 sum(decode(mv.media_item_type,
151 ''TELE_INB'',AGENT_CALLS_HANDLED_TOTAL,
152 ''TELE_DIRECT'',AGENT_CALLS_HANDLED_TOTAL,
153 0)
154 ) IN_HAND,
155 sum(decode(mv.media_item_type,
156 ''TELE_INB'',AGENT_CALLS_HANDLED_TOTAL,
157 ''TELE_DIRECT'',AGENT_CALLS_HANDLED_TOTAL,
158 0)
159 ) IN_FRESH_HAND,
160 sum(AGENT_CALLS_HANDLED_TOTAL) HAND,
161 sum(decode(mv.media_item_type,
162 ''TELE_INB'',CALL_TOT_QUEUE_TO_ANSWER,
163 ''TELE_DIRECT'',CALL_TOT_QUEUE_TO_ANSWER,
164 0)
165 ) IN_QTOANS,
166 sum(decode(mv.media_item_type,
167 ''TELE_INB'',CALL_CALLS_ABANDONED,
168 ''TELE_DIRECT'',CALL_CALLS_ABANDONED,
169 0)
170 ) IN_ABAND,
171 sum(CALL_TALK_TIME) TALK,
172 sum(AGENT_SR_CREATED) SR,
173 sum(AGENT_LEADS_CREATED) LEAD,
174 sum(AGENT_OPPORTUNITIES_CREATED) OPP
175 FROM bix_ai_call_details_mv mv,
176 fii_time_rpt_struct cal
177 WHERE mv.time_id = cal.time_id
178 AND mv.row_type = :l_row_type
179 AND mv.period_type_id = cal.period_type_id
180 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) =
181 cal.record_type_id
182 AND cal.report_date = &BIS_CURRENT_ASOF_DATE ';
183
184 l_sqltext := l_sqltext || l_call_where_clause ||
185 '
186 GROUP BY mv.classification_value
187 UNION ALL
188 SELECT classification_value class,
189 nvl(sum(decode(mv.media_item_type,
190 ''TELE_INB'',CALL_CONT_CALLS_OFFERED_NA,
191 ''TELE_DIRECT'', CALL_CONT_CALLS_OFFERED_NA,
192 0)
193 ),0) IN_OFFRD,
194 0 IN_ANSGOAL,
195 nvl(sum(decode(mv.media_item_type,
196 ''TELE_INB'',AGENT_CONT_CALLS_HAND_NA,
197 ''TELE_DIRECT'', AGENT_CONT_CALLS_HAND_NA,
198 0)
199 ),0) IN_HAND,
200 0 IN_FRESH_HAND,
201 nvl(sum(AGENT_CONT_CALLS_HAND_NA),0) HAND,
202 0 IN_QTOANS,
203 0 IN_ABAND,
204 0 TALK,
205 0 SR,
206 0 LEAD,
207 0 OPP
208 FROM bix_ai_call_details_mv mv
209 WHERE mv.time_id = to_char(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')
210 AND mv.row_type = :l_row_type
211 AND mv.period_type_id = :l_period_type_id
212 AND period_start_time = :l_period_start_time ';
213
214 l_sqltext := l_sqltext || l_call_where_clause ||
215 '
216 GROUP BY mv.classification_value
217 )
218 GROUP BY class
219 )
220 GROUP BY DECODE(greatest(RANKING,10),10,RANKING,decode(class,:l_unknown,11,12)),
221 DECODE(greatest(RANKING,10),10,class,
222 decode(class,:l_unknown,
223 ''</a><nobr>''|| :l_unknown ||''</nobr><a href=#>'',
224 ''</a><nobr>''|| :l_other_class ||'' </nobr><a href=#>''
225 )
226 )
227 ORDER BY DECODE(greatest(RANKING,10),10,RANKING,decode(class,:l_unknown,11,12)),
228 DECODE(greatest(RANKING,10),10,class,
229 decode(class,:l_unknown,
230 ''</a><nobr>''|| :l_unknown ||''</nobr><a href=#>'',
231 ''</a><nobr>''|| :l_other_class ||'' </nobr><a href=#>''
232 )
233 ) ';
234
235 p_custom_sql := l_sqltext;
236
237 l_custom_rec.attribute_name := ':l_row_type';
238 l_custom_rec.attribute_value:= 'CDR';
239 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
240 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
241
242 p_custom_output.Extend();
243 p_custom_output(p_custom_output.count) := l_custom_rec;
244
245 l_custom_rec.attribute_name := ':l_other_class' ;
246 l_custom_rec.attribute_value:= l_other_class;
247 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
248 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
249
250 p_custom_output.Extend();
251 p_custom_output(p_custom_output.count) := l_custom_rec;
252
253 IF l_call_center IS NOT NULL
254 THEN
255 l_custom_rec.attribute_name := ':l_call_center';
256 l_custom_rec.attribute_value:= l_call_center;
257 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
258 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
259
260 p_custom_output.Extend();
261 p_custom_output(p_custom_output.count) := l_custom_rec;
262 END IF;
263
264 IF l_classification IS NOT NULL
265 THEN
266 l_custom_rec.attribute_name := ':l_classification';
267 l_custom_rec.attribute_value:= l_classification;
268 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
269 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
270
271 p_custom_output.EXTEND;
272 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
273 END IF;
274
275 IF l_dnis IS NOT NULL AND l_dnis NOT IN ('INBOUND','OUTBOUND')
276 THEN
277 l_custom_rec.attribute_name := ':l_dnis';
278 l_custom_rec.attribute_value:= l_dnis;
279 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
280 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
281
282 p_custom_output.Extend();
283 p_custom_output(p_custom_output.count) := l_custom_rec;
284 END IF;
285
286 l_custom_rec.attribute_name := ':l_period_type_id';
287 l_custom_rec.attribute_value:= 1;
288 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
289 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
290
291 p_custom_output.Extend();
292 p_custom_output(p_custom_output.count) := l_custom_rec;
293
294 l_custom_rec.attribute_name := ':l_period_start_time';
295 l_custom_rec.attribute_value:= '00:00';
296 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
297 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
298
299 p_custom_output.EXTEND;
300 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
301
302 l_custom_rec.attribute_name := ':l_unknown';
303 l_custom_rec.attribute_value:= l_unknown;
304 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
305 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
306
307 p_custom_output.EXTEND;
308 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
309
310 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
311 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
312 l_custom_rec.attribute_value := 'BIX_TELEPHONY+BIX_CALL_CLASSIFICATION';
313
314 p_custom_output.EXTEND;
315 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
316
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_CACLASS_PRTLT_PKG;