[Home] [Help]
PACKAGE BODY: APPS.BIX_PMV_AO_COPRHR_PRTLT_PKG
Source
1 PACKAGE BODY BIX_PMV_AO_COPRHR_PRTLT_PKG AS
2 /*$Header: bixocphp.plb 120.1 2005/06/10 04:21:03 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_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 --added for campaign, schedule and source code
22 l_campaign_id varchar2(3000);
23 l_schedule_id varchar2(3000);
24 l_source_code_id varchar2(3000);
25 l_campaign_where_clause VARCHAR2(3000);
26 l_schedule_where_clause VARCHAR2(3000);
27 l_source_code_where_clause VARCHAR2(3000);
28 l_sess_source_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 END IF;
62
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
67 IF l_schedule_id IS NOT NULL THEN
68 l_schedule_where_clause := ' AND mv.schedule_id IN (:l_schedule_id) ';
69 END IF;
70
71 IF l_source_code_id IS NOT NULL THEN
72 l_source_code_where_clause := ' AND mv.campaign_id IN (select source_code_for_id
73 from ams_source_codes where active_flag=''Y'' and source_code_id in (:l_source_code_id)
74 and arc_source_code_for=''CAMP'' ) ';
75 END IF;
76
77 IF (FND_PROFILE.DEFINED('BIX_PMV_AO_TARCONT')) THEN
78 begin
79 l_goal := TO_NUMBER(FND_PROFILE.VALUE('BIX_PMV_AO_TARCONT'));
80 exception
81 when others then
82 l_goal :=0;
83 end;
84 ELSE
85 l_goal := 0;
86 END IF;
87
88
89 IF ( l_comp_type = 'YEARLY' AND l_period_type <> 'FII_TIME_ENT_YEAR' )
90 THEN
91 --
92 --If it enters here it means the comparison is for Week, Month or Quarter
93 --and it is a Year over Year comparison.
94 --
95 l_sqltext := '
96 SELECT fii1.name VIEWBY,
97 NVL(sum(PREV_CONTACTS)/
98 sum(DECODE(PREV_LOGIN/3600,0,NULL,PREV_LOGIN/3600)),0) BIX_PMV_AO_CONTPERHR_PP,
99
100 NVL(sum(CURR_CONTACTS)/
101 sum(DECODE(CURR_LOGIN/3600,0,NULL,CURR_LOGIN/3600)),0) BIX_PMV_AO_CONTPERHR_CP,
102 ' || l_goal ||' BIX_PMV_AO_CONTPERHR_GL
103 FROM
104 (
105 /*start inline view
106 select current contacts and previous contacts
107 */
108 SELECT fii1.sequence SEQUENCE,
109 SUM( CASE when
110 (
111 fii1.start_date between &BIS_CURRENT_REPORT_START_DATE
112 and &BIS_CURRENT_ASOF_DATE
113 and cal.report_date = least(fii1.end_date,&BIS_CURRENT_ASOF_DATE)
114 )
115 then
116 AGENTCALL_CONTACT_COUNT
117 else
118 0
119 end
120 ) CURR_CONTACTS,
121 SUM( CASE when
122 (
123 fii1.start_date between &BIS_PREVIOUS_REPORT_START_DATE
124 and &BIS_PREVIOUS_ASOF_DATE
125 and cal.report_date = least(fii1.end_date,&BIS_PREVIOUS_ASOF_DATE)
126 )
127 then
128 AGENTCALL_CONTACT_COUNT
129 else
130 0
131 end
132 ) PREV_CONTACTS,
133 0 CURR_LOGIN,
134 0 PREV_LOGIN
135 FROM ';
136 l_sqltext := l_sqltext || l_period_type ||' fii1,
137 bix_ao_call_details_mv mv,
138 fii_time_rpt_struct cal
139 WHERE mv.time_id = cal.time_id
140 AND mv.row_type = :l_row_type
141 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
142 AND fii1.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND
143 &BIS_CURRENT_ASOF_DATE
144 AND cal.report_date = (CASE WHEN(
145 fii1.start_date between
146 &BIS_PREVIOUS_REPORT_START_DATE and
147 &BIS_PREVIOUS_ASOF_DATE
148 )
149 THEN
150 least(fii1.end_date, &BIS_PREVIOUS_ASOF_DATE)
151 ELSE
152 least(fii1.end_date, &BIS_CURRENT_ASOF_DATE)
153 END
154 )
155
156 AND cal.period_type_id = mv.period_type_id
157 ';
158 l_sqltext := l_sqltext || l_call_where_clause ||l_source_code_where_clause||
159 '
160 GROUP BY fii1.sequence
161 UNION ALL
162 /*continue inline view
163 select from session tables for current and previous login times
164 */
165 SELECT fii1.sequence,
166 0 CURR_CONTACTS,
167 0 PREV_CONTACTS,
168 SUM( CASE when
169 (
170 fii1.start_date between &BIS_CURRENT_REPORT_START_DATE
171 and &BIS_CURRENT_ASOF_DATE
172 )
173 then
174 LOGIN_TIME
175 else
176 0
177 end
178 ) CURR_LOGIN,
179 SUM( CASE when
180 (
181 fii1.start_date between &BIS_PREVIOUS_REPORT_START_DATE
182 and &BIS_PREVIOUS_ASOF_DATE
183 )
184 then
185 LOGIN_TIME
186 else
187 0
188 end
189 ) PREV_LOGIN
190 FROM ';
191 l_sqltext := l_sqltext || l_period_type ||' fii1,
192 bix_agent_session_f mv,
193 fii_time_rpt_struct cal
194 WHERE mv.time_id = cal.time_id
195 AND mv.application_id = :l_application_id
196 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
197 AND fii1.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND
198 &BIS_CURRENT_ASOF_DATE
199 AND cal.report_date = (CASE WHEN(
200 fii1.start_date between
201 &BIS_PREVIOUS_REPORT_START_DATE and
202 &BIS_PREVIOUS_ASOF_DATE
203 )
204 THEN
205 least(fii1.end_date, &BIS_PREVIOUS_ASOF_DATE)
206 ELSE
207 least(fii1.end_date, &BIS_CURRENT_ASOF_DATE)
208 END
209 )
210 AND cal.period_type_id = mv.period_type_id ';
211
212 l_sqltext := l_sqltext || l_call_where_clause ||
213 '
214 GROUP BY fii1.sequence
215 ) summ, ';
216 l_sqltext := l_sqltext || l_period_type ||' fii1
217 WHERE fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
218 AND &BIS_CURRENT_ASOF_DATE
219 AND fii1.sequence = summ.sequence (+)
220 GROUP BY fii1.name, fii1.start_date, summ.sequence
221 ORDER BY fii1.start_date ';
222
223 ELSE
224 --
225 --If it reaches here it means it is either a Sequential comparison for
226 --week, month or quarter OR it is a YEAR period type. For YEAR period type
227 --it does not matter whether it is a Y/Y comparison or a Sequential comparison
228 --as both will be treated the same.
229 --
230 l_sqltext := '
231 SELECT fii1.name VIEWBY,
232 0 BIX_PMV_AO_CONTPERHR_PP,
233 nvl(SUM(NVL(CURR_CONTACTS,0))/
234 decode(SUM(CURR_LOGIN/3600),0,null,sum(curr_login/3600)),0) BIX_PMV_AO_CONTPERHR_CP,
235 ' || l_goal ||' BIX_PMV_AO_CONTPERHR_GL
236 FROM
237 (
238 /*start of inline view
239 select current contacts from mv */
240 SELECT fii1.name NAME,
241 sum(AGENTCALL_CONTACT_COUNT ) CURR_CONTACTS,
242 0 CURR_LOGIN
243 FROM '||l_period_type||' fii1,
244 bix_ao_call_details_mv mv,
245 fii_time_rpt_struct cal
246 WHERE mv.time_id = cal.time_id
247 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
248 AND fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND
249 &BIS_CURRENT_ASOF_DATE
250 AND cal.report_date = least(&BIS_CURRENT_ASOF_DATE,fii1.end_date)
251 AND cal.period_type_id = mv.period_type_id
252 AND mv.row_type = :l_row_type ';
253 l_sqltext := l_sqltext || l_call_where_clause ||l_source_code_where_clause||
254 '
255 GROUP BY fii1.name
256 UNION ALL
257 /* continue inline view
258 select login time from session tables */
259 SELECT fii1.name,
260 0 CURR_CONTACTS,
261 SUM(LOGIN_TIME) CURR_LOGIN
262 FROM ';
263 l_sqltext := l_sqltext || l_period_type ||' fii1,
264 bix_agent_session_f mv,
265 fii_time_rpt_struct cal
266 WHERE mv.time_id = cal.time_id
267 AND mv.application_id = :l_application_id
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(fii1.end_date, &BIS_CURRENT_ASOF_DATE) ';
272 l_sqltext := l_sqltext || l_call_where_clause ||
273 '
274 GROUP BY fii1.name
275 ) curr, ';
276 l_sqltext := l_sqltext || l_period_type || ' fii1
277 WHERE fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
278 AND &BIS_CURRENT_ASOF_DATE
279 AND fii1.name = curr.name (+)
280 GROUP BY fii1.name, fii1.start_date
281 ORDER BY fii1.start_date
282 ';
283
284 END IF;
285
286 /* Before passing l_sqltext to the calling proc, we trim it up a bit */
287 l_sqltext:=replace(replace(replace(replace(replace(l_sqltext,
288 ' ',' '),' ',' '),' ',' '),' ',' '),' ',' ');
289
290 p_custom_sql := l_sqltext;
291
292 l_custom_rec.attribute_name := ':l_row_type';
293 l_custom_rec.attribute_value:= 'C';
294 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
295 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
296
297 p_custom_output.Extend();
298 p_custom_output(p_custom_output.count) := l_custom_rec;
299
300
301 IF l_call_center IS NOT NULL
302 THEN
303 l_custom_rec.attribute_name := ':l_call_center';
304 l_custom_rec.attribute_value:= l_call_center;
305 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
306 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
307
308 p_custom_output.Extend();
309 p_custom_output(p_custom_output.count) := l_custom_rec;
310 END IF;
311
312 IF l_campaign_id IS NOT NULL
313 THEN
314 l_custom_rec.attribute_name := ':l_campaign_id';
315 l_custom_rec.attribute_value:= l_campaign_id;
316 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
317 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
318
319 p_custom_output.Extend();
320 p_custom_output(p_custom_output.count) := l_custom_rec;
321 END IF;
322
323 IF l_schedule_id IS NOT NULL
324 THEN
325 l_custom_rec.attribute_name := ':l_schedule_id';
326 l_custom_rec.attribute_value:= l_schedule_id;
327 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
328 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
329
330 p_custom_output.Extend();
331 p_custom_output(p_custom_output.count) := l_custom_rec;
332 END IF;
333 IF l_source_code_id IS NOT NULL
334 THEN
335 l_custom_rec.attribute_name := ':l_source_code_id';
336 l_custom_rec.attribute_value:= l_source_code_id;
337 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
338 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
339
340 p_custom_output.Extend();
341 p_custom_output(p_custom_output.count) := l_custom_rec;
342 END IF;
343
344 l_custom_rec.attribute_name := ':l_application_id';
345 l_custom_rec.attribute_value := 696;
346 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
347 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
348
349 p_custom_output.EXTEND;
350 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
351
352 l_custom_rec.attribute_name := ':l_period_type_id';
353 l_custom_rec.attribute_value:= 1;
354 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
355 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
356
357 p_custom_output.Extend();
358 p_custom_output(p_custom_output.count) := l_custom_rec;
359
360
361 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
362 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
363 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
364
365 p_custom_output.EXTEND;
366 p_custom_output(p_custom_output.COUNT) := l_custom_rec;
367
368
369 EXCEPTION
370 WHEN OTHERS THEN
371 l_sql_errm := SQLERRM;
372 NULL;
373 END GET_SQL;
374 END BIX_PMV_AO_COPRHR_PRTLT_PKG;
375