[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AO_CACENT_PRTLT_PKG
Source
1 PACKAGE BODY BIX_PMV_AO_CACENT_PRTLT_PKG AS
2 /*$Header: bixoccnp.plb 115.8 2004/04/13 13:42:32 suray 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
23 l_other_group VARCHAR2(30);
24
25 l_session_where_clause VARCHAR2(3000);
26
27 l_campaign_id varchar2(3000);
28 l_schedule_id varchar2(3000);
29 l_source_code_id varchar2(3000);
30 l_agent_group VARCHAR2(3000);
31 l_call_where_clause VARCHAR2(3000);
32
33 l_campaign_where_clause VARCHAR2(3000);
34 l_source_code_where_clause VARCHAR2(3000);
35 l_schedule_where_clause VARCHAR2(3000);
36 l_sess_source_where_clause VARCHAR2(3000);
37
38 l_unknown VARCHAR2(50);
39 l_view_by VARCHAR2(3000);
40
41 l_custom_rec BIS_QUERY_ATTRIBUTES := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
42
43 BEGIN
44 --
45 --Initialize p_custom_output
46 --
47 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
48
49 -- Get the parameters
50
51 BIX_PMV_DBI_UTL_PKG.get_ao_page_params( p_page_parameter_tbl,
52 l_as_of_date,
53 l_period_type,
54 l_record_type_id,
55 l_comp_type,
56 l_call_center,
57 l_campaign_id,
58 l_schedule_id,
59 l_source_code_id,
60 l_agent_group,
61 l_view_by
62 );
63
64 IF l_call_center IS NOT NULL THEN
65 l_call_where_clause := ' AND mv.server_group_id IN (:l_call_center) ';
66 END IF;
67
68 IF l_campaign_id IS NOT NULL THEN
69 l_campaign_where_clause := ' AND mv.campaign_id IN (:l_campaign_id) ';
70 END IF;
71 IF l_schedule_id IS NOT NULL THEN
72 l_schedule_where_clause := ' AND mv.schedule_id IN (:l_schedule_id) ';
73 END IF;
74 IF l_source_code_id IS NOT NULL THEN
75 -- l_source_code_where_clause := ' AND mv.source_code_id IN (:l_source_code_id) ';
76 l_source_code_where_clause := ' AND mv.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
80 END IF;
81
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
91 l_sqltext := '
92 SELECT DECODE(greatest(RANKING,10),10,group_name,
93 decode(group_name,:l_unknown,
94 ''</a><nobr>''|| :l_unknown ||''</nobr><a href=#>'',
95 ''</a><nobr>''|| :l_other_group ||''</nobr><a href=#>''
96 )
97 ) VIEWBY,
98 sum(HAND) BIX_PMV_AO_OUTCALLHAND,
99 sum(sum(HAND)) over() BIX_PMV_TOTAL2,
100 round(NVL(sum(ABAND)*100/
101 decode(sum(OFFRD),0,NULL,sum(OFFRD)),0),1) BIX_PMV_AO_ABANRATE,
102 round(sum (sum(ABAND)) over()*100/
103 decode(sum(sum(OFFRD)) over(),0,NULL,sum(sum(OFFRD)) over()),1) BIX_PMV_TOTAL4,
104 round(nvl(sum(USABAND)*100/
105 decode(sum(OFFRD),0,NULL,sum(OFFRD)),0),1) BIX_PMV_AO_US_ABANRATE,
106 round(sum(nvl(sum(USABAND),0)) over()*100/
107 decode(sum(sum(OFFRD)) over(),0,NULL,sum(sum(OFFRD)) over()),1) BIX_PMV_TOTAL11,
108 sum(sr) BIX_PMV_AO_SRCR,
109 sum(sum(sr)) over() BIX_PMV_TOTAL6,
110 sum(lead) BIX_PMV_AO_LECR,
111 sum(sum(lead)) over() BIX_PMV_TOTAL7 ,
112 sum(opp) BIX_PMV_AO_OPCR,
113 sum(sum(opp)) over() BIX_PMV_TOTAL8,
114 round(nvl(3600* sum(contacts) /decode(sum(login),0,null,sum(login)),0),1)
115 BIX_PMV_AO_CONTPERHR,
116 round(sum(sum(contacts)) over() /(decode(sum(sum(login)) over(),0,null,sum(sum(login)) over())/3600),1) BIX_PMV_TOTAL9,
117 nvl(sum(pr),0) BIX_PMV_AO_PORESP,
118 sum(nvl(sum(pr),0)) over() BIX_PMV_TOTAL10
119
120 FROM
121 (
122 /*
123 --Additional inline view needed to compute RANK due to continued measures
124 */
125 SELECT nvl(bixcent.value,:l_unknown) group_name,
126 nvl(sum(OFFRD),0) OFFRD, nvl(sum(hand),0) HAND,
127 nvl(sum(ABAND),0) ABAND,
128 nvl(sum(USABAND),0) USABAND,
129 nvl(sum(LOGIN),0) LOGIN,
130 nvl(sum(CONTACTS),0) CONTACTS,
131 nvl(sum(PR),0) PR,
132 nvl(sum(talk),0) TALK, nvl(sum(sr),0) SR, nvl(sum(lead),0) LEAD, nvl(sum(opp),0) OPP,
133 decode(bixcent.value,NULL,11,
134 RANK() OVER (ORDER BY nvl(sum(HAND),0) DESC, bixcent.value)
135 ) RANKING
136 FROM
137 (
138 SELECT server_group_id server_group_id,
139 sum(decode(dialing_method,''PRED'',CALL_CALLS_OFFERED_TOTAL,0) ) OFFRD,
140 sum(AGENT_CALLS_HANDLED_TOTAL) HAND,
141 sum(decode(dialing_method,''PRED'',CALL_CALLS_ABANDONED,0) ) ABAND,
142 sum(decode(dialing_method,''PRED'',CALL_CALLS_ABANDONED_US,0) ) USABAND,
143 sum(CALL_TALK_TIME) TALK,
144 sum(AGENT_SR_CREATED) SR,
145 sum(AGENT_LEADS_CREATED) LEAD,
146 sum(AGENT_OPPORTUNITIES_CREATED) OPP,
147 sum(AGENTCALL_CONTACT_COUNT) CONTACTS,
148 sum(AGENTCALL_PR_COUNT) PR,
149 0 LOGIN
150 FROM bix_ao_call_details_mv mv,
151 fii_time_rpt_struct cal
152 WHERE mv.time_id = cal.time_id
153 AND mv.row_type = :l_row_type
154 AND mv.period_type_id = cal.period_type_id
155 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) =
156 cal.record_type_id
157 AND cal.report_date = &BIS_CURRENT_ASOF_DATE ';
158
159 l_sqltext := l_sqltext || l_call_where_clause ||l_source_code_where_clause||
160 '
161 GROUP BY mv.server_group_id
162 UNION ALL
163 SELECT server_group_id server_group_id,
164 sum(decode(dialing_method,''PRED'',CALL_CONT_CALLS_HANDLED_TOT_NA,0) ) OFFRD,
165 nvl(sum(AGENT_CONT_CALLS_HAND_NA),0) HAND,
166 0 ABAND,
167 0 USBAND,
168 0 TALK,
169 0 SR,
170 0 LEAD,
171 0 OPP,
172 0 CONTACTS,
173 0 PR,
174 0 LOGIN
175 FROM bix_ao_call_details_mv mv
176 WHERE mv.time_id = to_char(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')
177 AND mv.row_type = :l_row_type
178 AND mv.period_type_id = :l_period_type_id
179 ';
180
181 l_sqltext := l_sqltext || l_call_where_clause ||l_source_code_where_clause||
182 '
183 GROUP BY mv.server_group_id
184 UNION ALL
185 SELECT mv.server_group_id ,
186 0 OFFRD,
187 0 HAND,
188 0 ABAND,
189 0 USABAND,
190 0 TALK,
191 0 SR,
192 0 LEAD,
193 0 OPP,
194 0 CONTACTS,
195 0 PR,
196 SUM(LOGIN_TIME) LOGIN
197 FROM
198 bix_agent_session_f mv,
199 fii_time_rpt_struct cal
200 WHERE mv.time_id = cal.time_id
201 AND application_id = :l_application_id
202 AND mv.period_type_id = cal.period_type_id
203 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) =
204 cal.record_type_id
205 AND cal.report_date = &BIS_CURRENT_ASOF_DATE
206 ';
207 l_sqltext := l_sqltext || l_call_where_clause ||
208 ' GROUP BY mv.server_group_id ) summ,
209 BIX_BIS_CALL_CENTER_V BIXCENT
210 WHERE summ.server_group_id = bixcent.id (+)
211 GROUP BY bixcent.value
212 )
213 GROUP BY DECODE(greatest(RANKING,10),10,RANKING,decode(group_name,:l_unknown,11,12)),
214 DECODE(greatest(RANKING,10),10,group_name,
215 decode(group_name,:l_unknown,
216 ''</a><nobr>''|| :l_unknown ||''</nobr><a href=#>'',
217 ''</a><nobr>''|| :l_other_group ||''</nobr><a href=#>''
218 )
219 )
220 ORDER BY DECODE(greatest(RANKING,10),10,RANKING,decode(group_name,:l_unknown,11,12)),
221 DECODE(greatest(RANKING,10),10,group_name,
222 decode(group_name,:l_unknown,
223 ''</a><nobr>''|| :l_unknown ||''</nobr><a href=#>'',
224 ''</a><nobr>''|| :l_other_group ||''</nobr><a href=#>''
225 )
226 ) ';
227 /* Before passing l_sqltext to the calling proc, we trim it up a bit */
228 l_sqltext:=replace(replace(replace(replace(replace(l_sqltext,
229 ' ',' '),' ',' '),' ',' '),' ',' '),' ',' ');
230 p_custom_sql := l_sqltext;
231
232 l_custom_rec.attribute_name := ':l_row_type';
233 l_custom_rec.attribute_value:= 'C';
234 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
235 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
236
237 p_custom_output.Extend();
238 p_custom_output(p_custom_output.count) := l_custom_rec;
239
240 l_custom_rec.attribute_name := ':l_other_group' ;
241 l_custom_rec.attribute_value:= l_other_group;
242 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
243 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
244
245 p_custom_output.Extend();
246 p_custom_output(p_custom_output.count) := l_custom_rec;
247
248 IF l_call_center IS NOT NULL
249 THEN
250 l_custom_rec.attribute_name := ':l_call_center';
251 l_custom_rec.attribute_value:= l_call_center;
252 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
253 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
254
255 p_custom_output.Extend();
256 p_custom_output(p_custom_output.count) := l_custom_rec;
257 END IF;
258
259 IF l_campaign_id IS NOT NULL
260 THEN
261 l_custom_rec.attribute_name := ':l_campaign_id';
262 l_custom_rec.attribute_value:= l_campaign_id;
263 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
264 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
265
266 p_custom_output.Extend();
267 p_custom_output(p_custom_output.count) := l_custom_rec;
268 END IF;
269
270 IF l_schedule_id IS NOT NULL
271 THEN
272 l_custom_rec.attribute_name := ':l_schedule_id';
273 l_custom_rec.attribute_value:= l_schedule_id;
274 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
275 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
276
277 p_custom_output.Extend();
278 p_custom_output(p_custom_output.count) := l_custom_rec;
279 END IF;
280 IF l_source_code_id IS NOT NULL
281 THEN
282 l_custom_rec.attribute_name := ':l_source_code_id';
283 l_custom_rec.attribute_value:= l_source_code_id;
284 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
285 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
286
287 p_custom_output.Extend();
288 p_custom_output(p_custom_output.count) := l_custom_rec;
289 END IF;
290 l_custom_rec.attribute_name := ':l_period_type_id';
291 l_custom_rec.attribute_value:= 1;
292 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
293 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
294
295 p_custom_output.Extend();
296 p_custom_output(p_custom_output.count) := l_custom_rec;
297
298
299 l_custom_rec.attribute_name := ':l_application_id';
300 l_custom_rec.attribute_value := 696;
301 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
302 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
303
304 p_custom_output.EXTEND;
305 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
306
307
308 l_custom_rec.attribute_name := ':l_unknown';
309 l_custom_rec.attribute_value:= l_unknown;
310 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
311 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
312
313 p_custom_output.EXTEND;
314 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
315
316 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
317 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
318 l_custom_rec.attribute_value := 'BIX_TELEPHONY+BIX_CALL_CENTER';
319
320 p_custom_output.EXTEND;
321 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
322
323 EXCEPTION
324 WHEN OTHERS THEN
325 NULL;
326 END GET_SQL;
327 END BIX_PMV_AO_CACENT_PRTLT_PKG;