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