[Home] [Help]
PACKAGE BODY: APPS.BIV_DBI_RES_PKG
Source
1 package body biv_dbi_res_pkg
2 /* $Header: bivsrvrresb.pls 120.0 2005/05/25 10:54:09 appldev noship $ */
3 as
4
5 g_closure_rep_func varchar2(50) := 'BIV_DBI_RES_TBL_REP';
6 g_closure_dbn_rep_func varchar2(50) := 'BIV_DBI_RES_DBN_TBL_REP';
7
8 g_closed_detail_rep_func varchar2(50) := 'BIV_DBI_RES_DTL_REP';
9
10 procedure get_tbl_sql
11 ( p_param in bis_pmv_page_parameter_tbl
12 , x_custom_sql out nocopy varchar2
13 , x_custom_output out nocopy bis_query_attributes_tbl
14 , p_distribution in varchar2 := 'N'
15 )
16 is
17
18 l_view_by varchar2(200);
19 l_view_by_col_name varchar2(200);
20 l_comparison_type varchar2(200);
21 l_xtd varchar2(200);
22 l_where_clause varchar2(10000);
23 l_mv varchar2(10000);
24 l_stmt varchar2(32767);
25
26 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
27
28 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
29 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
30
31 l_custom_output bis_query_attributes_tbl;
32
33 l_to_date_type VARCHAR2 (3) ;
34 l_as_of_date date;
35
36 begin
37
38 -- clear out the tables.
39 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
40 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
41 biv_dbi_tmpl_util.process_parameters
42 ( p_param => p_param
43 , p_report_type => 'RESOLVED'
44 , p_trend => 'N'
45 , x_view_by => l_view_by
46 , x_view_by_col_name => l_view_by_col_name
47 , x_comparison_type => l_comparison_type
48 , x_xtd => l_xtd
49 , x_where_clause => l_where_clause
50 , x_mv => l_mv
51 , x_join_tbl => l_join_tbl
52 , x_as_of_date => l_as_of_date
53 );
54
55 IF(l_xtd IN ('DAY','WTD','MTD','QTD','YTD') )
56 THEN
57 l_to_date_type := 'XTD';
58 ELSE
59 l_to_date_type := 'RLX';
60 END IF;
61
62
63 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
64 , p_col_name => 'resolution_count'
65 , p_alias_name => 'closed'
66 , p_to_date_type => l_to_date_type
67 );
68
69 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
70 , p_col_name => 'total_time_to_resolution'
71 , p_alias_name => 'time_to_close'
72 , p_to_date_type => l_to_date_type
73 );
74
75 biv_dbi_tmpl_util.add_bucket_inner_query
76 ( p_short_name => 'BIV_DBI_RESOLUTION_CYCLE_TIME'
77 , p_col_tbl => l_col_tbl
78 , p_col_name => 'time_to_resolution'
79 , p_alias_name => 'close_bucket'
80 , p_grand_total => 'Y'
81 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
82 , p_to_date_type => l_to_date_type
83 , x_bucket_rec => l_bucket_rec
84 );
85
86 l_stmt := 'select
87 ' ||
88 biv_dbi_tmpl_util.get_view_by_col_name(l_view_by) || ' VIEWBY ' ||
89 ', oset.' || l_view_by_col_name || ' VIEWBYID ' ||
90 case
91 when l_view_by = biv_dbi_tmpl_util.g_PRODUCT then
92 ', v.description'
93 else
94 ', null'
95 end
96 || ' BIV_ATTRIBUTE5
97 /* Closed Prior */
98 , nvl(oset.p_closed,0) BIV_MEASURE1
99 /* Closed Current */
100 , nvl(oset.c_closed,0) BIV_MEASURE2
101 /* Closed Change */
102 , ' ||
103 biv_dbi_tmpl_util.change_column('oset.c_closed','oset.p_closed','BIV_MEASURE4') ||
104 '
105 /* Average Time To Close Prior */
106 , ' ||
107 biv_dbi_tmpl_util.rate_column('oset.p_time_to_close','oset.p_closed','BIV_MEASURE5','N') ||
108 '
109 /* Average Time To Close Current */
110 , ' ||
111 biv_dbi_tmpl_util.rate_column('oset.c_time_to_close','oset.c_closed','BIV_MEASURE6','N') ||
112 '
113 /* Average Time To Close Change */
114 , ' ||
115 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('oset.c_time_to_close','oset.c_closed',null,'N')
116 ,biv_dbi_tmpl_util.rate_column('oset.p_time_to_close','oset.p_closed',null,'N')
117 ,'BIV_MEASURE8'
118 ,'N') ||
119 '
120 /* Time to Close Buckets */
121 ' || biv_dbi_tmpl_util.get_bucket_outer_query
122 ( p_bucket_rec => l_bucket_rec
123 , p_column_name_base => 'oset.c_close_bucket'
124 , p_alias_base => 'BIV_MEASURE10'
125 , p_total_flag => 'N'
126 , p_backlog_col => case
127 when p_distribution = 'Y' then 'oset.c_closed'
128 end
129 ) ||
130 '
131 /* GT Closed Current */
132 , nvl(oset.c_closed_total,0) BIV_MEASURE11
133 /* GT Closed Change */
134 , ' ||
135 biv_dbi_tmpl_util.change_column('oset.c_closed_total','oset.p_closed_total','BIV_MEASURE12') ||
136 '
137 /* GT Average Time To Close Current */
138 , ' ||
139 biv_dbi_tmpl_util.rate_column('oset.c_time_to_close_total','oset.c_closed_total','BIV_MEASURE13','N') ||
140 '
141 /* GT Average Time To Close Change */
142 , ' ||
143 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('oset.c_time_to_close_total','oset.c_closed_total',null,'N')
144 ,biv_dbi_tmpl_util.rate_column('oset.p_time_to_close_total','oset.p_closed_total',null,'N')
145 ,'BIV_MEASURE14'
146 ,'N') ||
147 '
148 /* GT Time to Close Buckets*/
149 ' || biv_dbi_tmpl_util.get_bucket_outer_query
150 ( p_bucket_rec => l_bucket_rec
151 , p_column_name_base => 'oset.c_close_bucket'
152 , p_alias_base => 'BIV_MEASURE15'
153 , p_total_flag => 'Y'
154 , p_backlog_col => case
155 when p_distribution = 'Y' then 'oset.c_closed'
156 end
157 ) ||
158 '
159 /* KPI GT Average Time To Close Prior */
160 , ' ||
161 biv_dbi_tmpl_util.rate_column('oset.p_time_to_close_total','oset.p_closed_total','BIV_MEASURE16','N') ||
162 '
163 , ' ||
164 biv_dbi_tmpl_util.get_category_drill_down( l_view_by
165 , case
166 when p_distribution = 'Y' then g_closure_dbn_rep_func
167 else g_closure_rep_func
168 end ) ||
169 biv_dbi_tmpl_util.drill_detail( g_closed_detail_rep_func
170 , 0
171 , null
172 , 'BIV_ATTRIBUTE6') ||
173 case
174 when p_distribution = 'N' then
175 biv_dbi_tmpl_util.bucket_detail_drill( g_closed_detail_rep_func
176 , l_bucket_rec
177 , 'BIV_ATTRIBUTE7' )
178 else
179 null
180 end ||
181 '
182 from
183 ( select * from ( ' || poa_dbi_template_pkg.status_sql
184 ( P_FACT_NAME => l_mv
185 , P_WHERE_CLAUSE => l_where_clause
186 , P_JOIN_TABLES => l_join_tbl
187 , P_USE_WINDOWING => 'N'
188 , P_COL_NAME => l_col_tbl
189 , P_USE_GRPID => 'N'
190 , P_PAREN_COUNT => 3
191 , P_FILTER_WHERE => '(c_closed > 0 or p_closed > 0)'
192 , P_GENERATE_VIEWBY => 'Y'
193 );
194
195 biv_dbi_tmpl_util.override_order_by(l_view_by, p_param, l_stmt);
196
197 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
198 -- l_stmt := l_stmt || biv_dbi_tmpl_util.dump_parameters(p_param);
199
200 x_custom_sql := l_stmt;
201
202 poa_dbi_util_pkg.get_custom_rolling_binds
203 ( p_custom_output => l_custom_output
204 , p_xtd => l_xtd
205 );
206
207 x_custom_output := l_custom_output;
208
209 end get_tbl_sql;
210
211 procedure get_dbn_tbl_sql
212 ( p_param in bis_pmv_page_parameter_tbl
213 , x_custom_sql out nocopy varchar2
214 , x_custom_output out nocopy bis_query_attributes_tbl
215 )
216 is
217 begin
218 get_tbl_sql
219 ( p_param => p_param
220 , x_custom_sql => x_custom_sql
221 , x_custom_output => x_custom_output
222 , p_distribution => 'Y'
223 );
224 end get_dbn_tbl_sql;
225
226
227 procedure get_trd_sql
228 ( p_param in bis_pmv_page_parameter_tbl
229 , x_custom_sql out nocopy varchar2
230 , x_custom_output out nocopy bis_query_attributes_tbl
231 , p_distribution in varchar2 := 'N'
232 )
233 is
234
235 l_view_by varchar2(200);
236 l_view_by_col_name varchar2(200);
237 l_comparison_type varchar2(200);
238 l_xtd varchar2(200);
239 l_where_clause varchar2(10000);
240 l_mv varchar2(10000);
241 l_stmt varchar2(32767);
242
243 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
244
245 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
246 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
247
248 l_custom_output bis_query_attributes_tbl;
249
250 l_to_date_type VARCHAR2 (3) ;
251 l_as_of_date date;
252
253 begin
254
255 -- clear out the tables.
256 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
257 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
258
259 biv_dbi_tmpl_util.process_parameters
260 ( p_param => p_param
261 , p_report_type => 'RESOLVED'
262 , p_trend => 'Y'
263 , x_view_by => l_view_by
264 , x_view_by_col_name => l_view_by_col_name
265 , x_comparison_type => l_comparison_type
266 , x_xtd => l_xtd
267 , x_where_clause => l_where_clause
268 , x_mv => l_mv
269 , x_join_tbl => l_join_tbl
270 , x_as_of_date => l_as_of_date
271 );
272
273
274 IF(l_xtd IN ('DAY','WTD','MTD','QTD','YTD') )
275 THEN
276 l_to_date_type := 'XTD';
277 ELSE
278 l_to_date_type := 'RLX';
279 END IF;
280
281 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
282 , p_col_name => 'resolution_count'
283 , p_alias_name => 'closed'
284 , p_to_date_type => l_to_date_type
285 , p_grand_total => 'N'
286 );
287
288 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
289 , p_col_name => 'total_time_to_resolution'
290 , p_alias_name => 'time_to_close'
291 , p_to_date_type => l_to_date_type
292 , p_grand_total => 'N'
293 );
294
295 biv_dbi_tmpl_util.add_bucket_inner_query
296 ( p_short_name => 'BIV_DBI_RESOLUTION_CYCLE_TIME'
297 , p_col_tbl => l_col_tbl
298 , p_col_name => 'time_to_resolution'
299 , p_alias_name => 'close_bucket'
300 , p_grand_total => 'N'
301 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
302 , p_to_date_type => l_to_date_type
303 , x_bucket_rec => l_bucket_rec
304 );
305
306 l_stmt := 'select
307 cal.name VIEWBY
308 /* End date of the period */
309 , cal.end_date VIEWBYID
310 /* Closed Prior */
311 , nvl(iset.p_closed,0) BIV_MEASURE1
312 /* Closed Current */
313 , nvl(iset.c_closed,0) BIV_MEASURE2
314 /* Closed Change */
315 , ' ||
316 biv_dbi_tmpl_util.change_column('iset.c_closed','iset.p_closed','BIV_MEASURE4') ||
317 '
318 /* Average Time To Close Prior */
319 , ' ||
320 biv_dbi_tmpl_util.rate_column('iset.p_time_to_close','iset.p_closed','BIV_MEASURE5','N') ||
321 '
322 /* Average Time To Close Current */
323 , ' ||
324 biv_dbi_tmpl_util.rate_column('iset.c_time_to_close','iset.c_closed','BIV_MEASURE6','N') ||
325 '
326 /* Average Time To Close Change */
327 , ' ||
328 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('iset.c_time_to_close','iset.c_closed',null,'N')
329 ,biv_dbi_tmpl_util.rate_column('iset.p_time_to_close','iset.p_closed',null,'N')
330 ,'BIV_MEASURE8'
331 ,'N') ||
332 '
333 /* Time to Close Buckets */
334 ' || biv_dbi_tmpl_util.get_bucket_outer_query
335 ( p_bucket_rec => l_bucket_rec
336 , p_column_name_base => 'iset.c_close_bucket'
337 , p_alias_base => 'BIV_MEASURE10'
338 , p_total_flag => 'N'
339 , p_backlog_col => case
340 when p_distribution = 'Y' then 'iset.c_closed'
341 end
342 );
343
344 IF (l_xtd = 'WTD')
345 THEN
346 l_stmt := l_stmt ||','||'''AS_OF_DATE=''||to_char(cal.end_date,''dd/mm/yyyy'')||''&pFunctionName=BIV_DBI_RES_TRD_REP&pParamIds=Y&VIEW_BY=TIME+FII_TIME_DAY&FII_TIME_DAY=FII_TIME_DAY'' BIV_DYNAMIC_URL1' || ',NULL BIV_DYNAMIC_URL2';
347 ELSIF (l_xtd = 'RLW') THEN
348 l_stmt := l_stmt ||',NULL BIV_DYNAMIC_URL1 ,'||'''AS_OF_DATE=''||to_char(cal.end_date,''dd/mm/yyyy'')||''&pFunctionName=BIV_DBI_RES_TRD_REP&pParamIds=Y&VIEW_BY=TIME+FII_TIME_DAY&FII_TIME_DAY=FII_TIME_DAY'' BIV_DYNAMIC_URL2';
349 ELSE
350 l_stmt:= l_stmt || ', NULL BIV_DYNAMIC_URL1, NULL BIV_DYNAMIC_URL2';
351 END IF;
352
353 l_stmt := l_stmt || '
354
355 from
356 ' || poa_dbi_template_pkg.trend_sql
357 ( p_xtd => l_xtd
358 , p_comparison_type => l_comparison_type
359 , p_fact_name => l_mv
360 , p_where_clause => l_where_clause
361 , p_col_name => l_col_tbl
362 , p_use_grpid => 'N'
363 );
364
365 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
366 -- l_stmt := l_stmt || biv_dbi_tmpl_util.dump_parameters(p_param);
367
368 x_custom_sql := l_stmt;
369
370 poa_dbi_util_pkg.get_custom_trend_binds
371 ( x_custom_output => l_custom_output
372 , p_xtd => l_xtd
373 , p_comparison_type => l_comparison_type
374 );
375
376 poa_dbi_util_pkg.get_custom_rolling_binds
377 ( p_custom_output => l_custom_output
378 , p_xtd => l_xtd
379 );
380
381 IF(l_xtd = 'DAY')
382 THEN
383
384 poa_dbi_util_pkg.get_custom_day_binds(p_custom_output => l_custom_output,
385 p_as_of_date => l_as_of_date,
386 p_comparison_type => l_comparison_type);
387 null;
388 END IF;
389
390 x_custom_output := l_custom_output;
391
392 end get_trd_sql;
393
394 procedure get_dbn_trd_sql
395 ( p_param in bis_pmv_page_parameter_tbl
396 , x_custom_sql out nocopy varchar2
397 , x_custom_output out nocopy bis_query_attributes_tbl
398 )
399 is
400 begin
401 get_trd_sql
402 ( p_param => p_param
403 , x_custom_sql => x_custom_sql
404 , x_custom_output => x_custom_output
405 , p_distribution => 'Y'
406 );
407 end get_dbn_trd_sql;
408
409 procedure get_detail_sql
410 ( p_param in bis_pmv_page_parameter_tbl
411 , x_custom_sql out nocopy varchar2
412 , x_custom_output out nocopy bis_query_attributes_tbl
413 )as
414
415 l_where_clause varchar2(10000);
416 l_mv varchar2(10000);
417 l_join_from varchar2(10000);
418 l_join_where varchar2(10000);
419 l_order_by varchar2(100);
420 l_drill_url varchar2(500);
421 l_sr_id varchar2(100);
422
423 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
424 l_custom_output bis_query_attributes_tbl;
425 l_as_of_date date;
426
427 l_xtd varchar2(200);
428
429 begin
430
431 biv_dbi_tmpl_util.get_detail_page_function( l_drill_url, l_sr_id );
432
433 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
434
435 biv_dbi_tmpl_util.process_parameters
436 ( p_param => p_param
437 , p_report_type => 'RESOLVED_DETAIL'
438 , x_where_clause => l_where_clause
439 , x_mv => l_mv
440 , x_xtd => l_xtd
441 , x_join_from => l_join_from
442 , x_join_where => l_join_where
443 , x_join_tbl => l_join_tbl
444 , x_as_of_date => l_as_of_date
445 );
446
447 if l_where_clause like '%<replace this>%' then
448 l_where_clause := replace(l_where_clause,'fact.<replace this> in (&'||biv_dbi_tmpl_util.g_AGING||')'
449 ,'(&RANGE_LOW is null or fact.age >= &RANGE_LOW) and (&RANGE_HIGH is null or fact.age < &RANGE_HIGH)');
450
451 biv_dbi_tmpl_util.bind_low_high
452 ( p_param
453 , 'BIV_DBI_RESOLUTION_CYCLE_TIME'
454 , '&RANGE_LOW'
455 , '&RANGE_HIGH'
456 , l_custom_output );
457
458 end if;
459
460 l_order_by := biv_dbi_tmpl_util.get_order_by(p_param);
461 if l_order_by like '% DESC%' then
462 if l_order_by like '%BIV_MEASURE11%' then
463 l_order_by := 'fact.resolved_date desc, fact.incident_id desc';
464 else
465 l_order_by := 'fact.age desc, fact.incident_id desc';
466 end if;
467 else
468 if l_order_by like '%BIV_MEASURE11%' then
469 l_order_by := 'fact.resolved_date asc, fact.incident_id asc';
470 else
471 l_order_by := 'fact.age asc, fact.incident_id asc';
472 end if;
473 end if;
474
475 x_custom_sql := '
476 select
477 i.incident_number biv_measure1
478 , rt.value biv_measure2 -- request_type
479 , pr.value biv_measure3 -- product
480 , pr.description biv_measure4
481 , cu.value biv_measure5 -- customer
482 , sv.value biv_measure6 -- severity
483 , ag.value biv_measure7 -- assignment_group
484 , re.value biv_measure8 -- resolution
485 , ch.value biv_measure9 -- channel
486 , fact.age biv_measure10
487 , fnd_date.date_to_displaydate(fact.resolved_date) biv_measure11 ' ||
488 case
489 when l_drill_url is not null then
490 '
491 , ''pFunctionName=' || l_drill_url || '&' || l_sr_id || '=''||fact.incident_id biv_attribute1'
492 else
493 '
494 , null biv_attribute1'
495 end ||
496 '
497 from
498 ( select
499 fact.*
500 , rank() over(order by ' || l_order_by || ') -1 rnk
501 from
502 ' || l_mv || ' fact
503 where
504 fact.resolved_date between &BIS_CURRENT_EFFECTIVE_START_DATE and (&BIS_CURRENT_EFFECTIVE_END_DATE + 0.99999)
505 ' || l_where_clause || '
506 ) fact
507 ' || l_join_from || '
508 , cs_incidents_all_b i
509 where
510 1=1
511 and fact.incident_id = i.incident_id' || l_join_where || '
512 and (fact.rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
513 &ORDER_BY_CLAUSE
514 '
515 --|| biv_dbi_tmpl_util.dump_parameters(p_param)
516 ;
517
518 if l_custom_output is null then
519 l_custom_output := bis_query_attributes_tbl();
520 end if;
521
522 x_custom_output := l_custom_output;
523
524 end get_detail_sql;
525
526 end biv_dbi_res_pkg;