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