[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AO_ATWTR_PRTLT_PKG
Source
1 PACKAGE BODY BIX_PMV_AO_ATWTR_PRTLT_PKG AS
2 /*$Header: bixotwrp.plb 120.1 2005/06/10 04:22:00 appldev $ */
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_previous_report_start_date DATE;
15 l_current_report_start_date DATE;
16 l_previous_as_of_date DATE;
17 l_period_type_id NUMBER;
18 l_time_id_column VARCHAR2(1000);
19 --added for campaign, schedule and source code
20 l_campaign_id varchar2(3000);
21 l_schedule_id varchar2(3000);
22 l_source_code_id varchar2(3000);
23 l_campaign_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_call_where_clause VARCHAR2(3000);
28 l_session_where_clause VARCHAR2(3000);
29 l_call_center VARCHAR2(3000);
30 l_view_by VARCHAR2(3000);
31
32
33
34 l_custom_rec BIS_QUERY_ATTRIBUTES := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
35
36 BEGIN
37 --
38 --Initialize p_custom_output
39 --
40 p_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
41
42 -- Get the parameters
43
44 BIX_PMV_DBI_UTL_PKG.get_ao_page_params( p_page_parameter_tbl,
45 l_as_of_date,
46 l_period_type,
47 l_record_type_id,
48 l_comp_type,
49 l_call_center,
50 l_campaign_id,
51 l_schedule_id,
52 l_source_code_id,
53 l_agent_group,
54 l_view_by
55 );
56
57 IF l_call_center IS NOT NULL THEN
58 l_call_where_clause := ' AND mv.server_group_id IN (:l_call_center) ';
59 END IF;
60 IF l_campaign_id IS NOT NULL THEN
61 l_campaign_where_clause := ' AND mv.campaign_id IN (:l_campaign_id) ';
62 END IF;
63 IF l_schedule_id IS NOT NULL THEN
64 l_schedule_where_clause := ' AND mv.schedule_id IN (:l_schedule_id) ';
65 END IF;
66 IF l_source_code_id IS NOT NULL THEN
67 l_source_code_where_clause := ' AND mv.campaign_id in (select source_code_for_id from
68 ams_source_codes where source_code_id IN (:l_source_code_id) and arc_source_code_for=''CAMP'' and active_flag=''Y'' ) ';
69
70 END IF;
71
72
73 IF ( l_comp_type = 'YEARLY' AND l_period_type <> 'FII_TIME_ENT_YEAR' )
74 THEN
75 --
76 --If it enters here it means the comparison is for Week, Month or Quarter
77 --and it is a Year over Year comparison.
78 --
79 l_sqltext := '
80 SELECT fii1.name VIEWBY,
81 sum(PREV_TALK)/
82 decode(sum(PREV_HAND),0,NULL,sum(PREV_HAND)) BIX_PMV_AO_AVGTALK_PP,
83 sum(CURR_TALK)/
84 decode(sum(CURR_HAND),0,NULL,sum(CURR_HAND)) BIX_PMV_AO_AVGTALK_CP,
85 sum(PREV_WRAP)/
86 decode(sum(PREV_HAND),0,NULL,sum(PREV_HAND)) BIX_PMV_AO_AVGWRAP_PP,
87 sum(CURR_WRAP)/
88 decode(sum(CURR_HAND),0,NULL,sum(CURR_HAND)) BIX_PMV_AO_AVGWRAP_CP
89 FROM
90 ( SELECT fii1.sequence SEQUENCE,
91 SUM( CASE when
92 (
93 fii1.start_date between &BIS_CURRENT_REPORT_START_DATE
94 and &BIS_CURRENT_ASOF_DATE
95 and cal.report_date = least(fii1.end_date,&BIS_CURRENT_ASOF_DATE)
96 )
97 then
98 CALL_TALK_TIME
99 else
100 0
101 end
102 ) CURR_TALK,
103 SUM( CASE when
104 (
105 fii1.start_date between &BIS_CURRENT_REPORT_START_DATE
106 and &BIS_CURRENT_ASOF_DATE
107 and cal.report_date = least(fii1.end_date,&BIS_CURRENT_ASOF_DATE)
108 )
109 then
110 AGENT_WRAP_TIME_NAC
111 else
112 0
113 end
114 ) CURR_WRAP,
115 SUM( CASE when
116 (
117 fii1.start_date between &BIS_CURRENT_REPORT_START_DATE
118 and &BIS_CURRENT_ASOF_DATE
119 and cal.report_date = least(fii1.end_date,&BIS_CURRENT_ASOF_DATE)
120 )
121 then
122 CALL_CALLS_HANDLED_TOTAL
123 else
124 0
125 end
126 ) CURR_HAND,
127 SUM( CASE when
128 (
129 fii1.start_date between &BIS_PREVIOUS_REPORT_START_DATE
130 and &BIS_PREVIOUS_ASOF_DATE
131 and cal.report_date = least(fii1.end_date,&BIS_PREVIOUS_ASOF_DATE)
132 )
133 then
134 CALL_TALK_TIME
135 else
136 0
137 end
138 ) PREV_TALK,
139 SUM( CASE when
140 (
141 fii1.start_date between &BIS_PREVIOUS_REPORT_START_DATE
142 and &BIS_PREVIOUS_ASOF_DATE
143 and cal.report_date = least(fii1.end_date,&BIS_PREVIOUS_ASOF_DATE)
144 )
145 then
146 AGENT_WRAP_TIME_NAC
147 else
148 0
149 end
150 ) PREV_WRAP,
151 SUM( CASE when
152 (
153 fii1.start_date between &BIS_PREVIOUS_REPORT_START_DATE
154 and &BIS_PREVIOUS_ASOF_DATE
155 and cal.report_date = least(fii1.end_date,&BIS_PREVIOUS_ASOF_DATE)
156 )
157 then
158 CALL_CALLS_HANDLED_TOTAL
159 else
160 0
161 end
162 ) PREV_HAND
163 FROM ';
164
165 l_sqltext := l_sqltext || l_period_type||' fii1,
166 bix_ao_call_details_mv mv,
167 fii_time_rpt_struct cal
168 WHERE mv.time_id = cal.time_id
169 AND mv.row_type = :l_row_type
170 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
171 AND fii1.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND
172 &BIS_CURRENT_ASOF_DATE
173 AND cal.report_date = (CASE WHEN(
174 fii1.start_date between
175 &BIS_PREVIOUS_REPORT_START_DATE and
176 &BIS_PREVIOUS_ASOF_DATE
177 )
178 THEN
179 least(fii1.end_date, &BIS_PREVIOUS_ASOF_DATE)
180 ELSE
181 least(fii1.end_date, &BIS_CURRENT_ASOF_DATE)
182 END
183 )
184 AND cal.period_type_id = mv.period_type_id ';
185
186 l_sqltext := l_sqltext || l_call_where_clause ||l_source_code_where_clause||
187 '
188 GROUP BY fii1.sequence
189 UNION ALL
190 /*
191 --Select the continued offered measure
192 */
193 SELECT fii1.sequence,
194 0 CURR_TALK,
195 0 CURR_WRAP,
196 SUM( CASE when
197 (
198 fii1.start_date between &BIS_CURRENT_REPORT_START_DATE
199 and &BIS_CURRENT_ASOF_DATE
200 )
201 then
202 CALL_CONT_CALLS_HANDLED_TOT_NA
203 else
204 0
205 end
206 ) CURR_HAND,
207 0 PREV_TALK,
208 0 PREV_WRAP,
209 SUM( CASE when
210 (
211 fii1.start_date between &BIS_PREVIOUS_REPORT_START_DATE
212 and &BIS_PREVIOUS_ASOF_DATE
213 )
214 then
215 CALL_CONT_CALLS_HANDLED_TOT_NA
216 else
217 0
218 end
219 ) PREV_HAND
220 FROM bix_ao_call_details_mv mv, ';
221
222 l_sqltext := l_sqltext || l_period_type||' fii1
223 WHERE /*mv.period_start_time = :l_period_start_time
224 AND*/ mv.period_type_id = :l_period_type_id
225 AND mv.row_type = :l_row_type
226 AND mv.time_id = to_char(fii1.start_date,''J'')
227 AND fii1.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
228 AND &BIS_CURRENT_ASOF_DATE ';
229
230 l_sqltext := l_sqltext || l_call_where_clause ||l_source_code_where_clause||
231 '
232 GROUP BY fii1.sequence
233 ) summ, '
234 ||l_period_type||' fii1
235 WHERE fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
236 AND &BIS_CURRENT_ASOF_DATE
237 AND fii1.sequence = summ.sequence (+)
238 GROUP BY fii1.name, fii1.start_date, summ.sequence
239 ORDER BY fii1.start_date ';
240
241 ELSE
242 --
243 --If it reaches here it means it is either a Sequential comparison for
244 --week, month or quarter OR it is a YEAR period type. For YEAR period type
245 --it does not matter whether it is a Y/Y comparison or a Sequential comparison
246 --as both will be treated the same.
247 --
248 l_sqltext := '
249 SELECT fii1.name VIEWBY,
250 NULL BIX_PMV_AO_AVGTALK_PP,
251 sum(CURR_TALK)/
252 decode(sum(CURR_HAND),0,NULL,sum(CURR_HAND)) BIX_PMV_AO_AVGTALK_CP,
253 NULL BIX_PMV_AO_AVGWRAP_PP,
254 sum(CURR_WRAP)/
255 decode(sum(CURR_HAND),0,NULL,sum(CURR_HAND)) BIX_PMV_AO_AVGWRAP_CP
256 FROM
257 ( SELECT fii1.name NAME,
258 sum(CALL_TALK_TIME) CURR_TALK,
259 sum(AGENT_WRAP_TIME_NAC) CURR_WRAP,
260 sum(CALL_CALLS_HANDLED_TOTAL) CURR_HAND
261 FROM ';
262
263 l_sqltext := l_sqltext || l_period_type ||' fii1,
264 bix_ao_call_details_mv mv,
265 fii_time_rpt_struct cal
266 WHERE mv.time_id = cal.time_id
267 AND mv.row_type = :l_row_type
268 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
269 AND fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND
270 &BIS_CURRENT_ASOF_DATE
271 AND cal.report_date = least(&BIS_CURRENT_ASOF_DATE,fii1.end_date)
272 AND cal.period_type_id = mv.period_type_id ';
273
274 l_sqltext := l_sqltext || l_call_where_clause ||l_source_code_where_clause||
275 '
276 GROUP BY fii1.name
277 UNION ALL
278 /*
279 --Select the continued offered measure
280 */
281 SELECT fii1.name,
282 0 CURR_TALK,
283 0 CURR_WRAP,
284 SUM(CALL_CONT_CALLS_HANDLED_TOT_NA) CURR_HAND
285 FROM bix_ao_call_details_mv mv, ';
286
287 l_sqltext := l_sqltext || l_period_type ||' fii1
288 WHERE /*mv.period_start_time = :l_period_start_time
289 AND*/ mv.period_type_id = :l_period_type_id
290 AND mv.row_type = :l_row_type
291 AND mv.time_id = to_char(fii1.start_date,''J'')
292 AND fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
293 AND &BIS_CURRENT_ASOF_DATE ';
294
295 l_sqltext := l_sqltext || l_call_where_clause ||l_source_code_where_clause||
296 '
297 GROUP BY fii1.name
298 ) curr, ' ;
299
300 l_sqltext := l_sqltext || l_period_type || ' fii1
301 WHERE fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
302 AND &BIS_CURRENT_ASOF_DATE
303 AND fii1.name = curr.name (+)
304 GROUP BY fii1.name, fii1.start_date
305 ORDER BY fii1.start_date
306 ';
307
308 END IF;
309
310 /* Before passing l_sqltext to the calling proc, we trim it up a bit */
311 l_sqltext:=replace(replace(replace(replace(replace(l_sqltext,
312 ' ',' '),' ',' '),' ',' '),' ',' '),' ',' ');
313
314 p_custom_sql := l_sqltext;
315
316 l_custom_rec.attribute_name := ':l_row_type';
317 l_custom_rec.attribute_value:= 'C';
318 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
319 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
320
321 p_custom_output.Extend();
322 p_custom_output(p_custom_output.count) := l_custom_rec;
323
324 l_custom_rec.attribute_name := ':l_period_type_id';
325 l_custom_rec.attribute_value:= 1;
326 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
327 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
328
329 p_custom_output.Extend();
330 p_custom_output(p_custom_output.count) := l_custom_rec;
331
332 /*l_custom_rec.attribute_name := ':l_period_start_time';
333 l_custom_rec.attribute_value:= '00:00';
334 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
335 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
336
337 p_custom_output.Extend();
338 p_custom_output(p_custom_output.count) := l_custom_rec;
339 */
340
341 IF l_call_center IS NOT NULL
342 THEN
343 l_custom_rec.attribute_name := ':l_call_center';
344 l_custom_rec.attribute_value:= l_call_center;
345 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
346 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
347
348 p_custom_output.Extend();
349 p_custom_output(p_custom_output.count) := l_custom_rec;
350 END IF;
351
352 IF l_campaign_id IS NOT NULL
353 THEN
354 l_custom_rec.attribute_name := ':l_campaign_id';
355 l_custom_rec.attribute_value:= l_campaign_id;
356 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
357 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
358
359 p_custom_output.Extend();
360 p_custom_output(p_custom_output.count) := l_custom_rec;
361 END IF;
362
363 IF l_schedule_id IS NOT NULL
364 THEN
365 l_custom_rec.attribute_name := ':l_schedule_id';
366 l_custom_rec.attribute_value:= l_schedule_id;
367 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
368 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
369
370 p_custom_output.Extend();
371 p_custom_output(p_custom_output.count) := l_custom_rec;
372 END IF;
373 IF l_source_code_id IS NOT NULL
374 THEN
375 l_custom_rec.attribute_name := ':l_source_code_id';
376 l_custom_rec.attribute_value:= l_source_code_id;
377 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
378 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
379
380 p_custom_output.Extend();
381 p_custom_output(p_custom_output.count) := l_custom_rec;
382 END IF;
383
384
385 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
386 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
387 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
388
389 p_custom_output.EXTEND;
390 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
391
392 EXCEPTION
393 WHEN OTHERS THEN
394 NULL;
395 END GET_SQL;
396 END BIX_PMV_AO_ATWTR_PRTLT_PKG;
397