[Home] [Help]
PACKAGE BODY: APPS.BIV_DBI_CLO_PKG
Source
1 package body biv_dbi_clo_pkg
2 /* $Header: bivsrvrclob.pls 120.0 2005/05/25 10:55:26 appldev noship $ */
3 as
4
5 g_closure_rep_func varchar2(50) := 'BIV_DBI_CLO_TBL_REP';
6 g_closure_dbn_rep_func varchar2(50) := 'BIV_DBI_CLO_DBN_TBL_REP';
7
8 g_closed_detail_rep_func varchar2(50) := 'BIV_DBI_CLO_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
42 biv_dbi_tmpl_util.process_parameters
43 ( p_param => p_param
44 , p_report_type => 'CLOSED'
45 , p_trend => 'N'
46 , x_view_by => l_view_by
47 , x_view_by_col_name => l_view_by_col_name
48 , x_comparison_type => l_comparison_type
49 , x_xtd => l_xtd
50 , x_where_clause => l_where_clause
51 , x_mv => l_mv
52 , x_join_tbl => l_join_tbl
53 , x_as_of_date => l_as_of_date
54 );
55
56 IF(l_xtd IN ('DAY','WTD','MTD','QTD','YTD') )
57 THEN
58 l_to_date_type := 'XTD';
59 ELSE
60 l_to_date_type := 'RLX';
61 END IF;
62
63
64 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
65 , p_col_name => 'closed_count'
66 , p_alias_name => 'closed'
67 , p_to_date_type => l_to_date_type
68 );
69
70 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
71 , p_col_name => 'total_time_to_close'
72 , p_alias_name => 'time_to_close'
73 , p_to_date_type => l_to_date_type
74 );
75
76 biv_dbi_tmpl_util.add_bucket_inner_query
77 ( p_short_name => 'BIV_DBI_CLOSURE_CYCLE_TIME'
78 , p_col_tbl => l_col_tbl
79 , p_col_name => 'time_to_close'
80 , p_alias_name => 'close_bucket'
81 , p_grand_total => 'Y'
82 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
83 , p_to_date_type => l_to_date_type
84 , x_bucket_rec => l_bucket_rec
85 );
86
87 l_stmt := 'select
88 ' ||
89 biv_dbi_tmpl_util.get_view_by_col_name(l_view_by) || ' VIEWBY ' ||
90 ', oset.' || l_view_by_col_name || ' VIEWBYID ' ||
91 case
92 when l_view_by = biv_dbi_tmpl_util.g_PRODUCT then
93 ', v.description'
94 else
95 ', null'
96 end
97 || ' BIV_ATTRIBUTE5
98 /* Closed Prior */
99 , nvl(oset.p_closed,0) BIV_MEASURE1
100 /* Closed Current */
101 , nvl(oset.c_closed,0) BIV_MEASURE2
102 /* Closed Change */
103 , ' ||
104 biv_dbi_tmpl_util.change_column('oset.c_closed','oset.p_closed','BIV_MEASURE4') ||
105 '
106 /* Average Time To Close Prior */
107 , ' ||
108 biv_dbi_tmpl_util.rate_column('oset.p_time_to_close','oset.p_closed','BIV_MEASURE5','N') ||
109 '
110 /* Average Time To Close Current */
111 , ' ||
112 biv_dbi_tmpl_util.rate_column('oset.c_time_to_close','oset.c_closed','BIV_MEASURE6','N') ||
113 '
114 /* Average Time To Close Change */
115 , ' ||
116 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('oset.c_time_to_close','oset.c_closed',null,'N')
117 ,biv_dbi_tmpl_util.rate_column('oset.p_time_to_close','oset.p_closed',null,'N')
118 ,'BIV_MEASURE8'
119 ,'N') ||
120 '
121 /* Time to Close Buckets */
122 ' || biv_dbi_tmpl_util.get_bucket_outer_query
123 ( p_bucket_rec => l_bucket_rec
124 , p_column_name_base => 'oset.c_close_bucket'
125 , p_alias_base => 'BIV_MEASURE10'
126 , p_total_flag => 'N'
127 , p_backlog_col => case
128 when p_distribution = 'Y' then 'oset.c_closed'
129 end
130 ) ||
131 '
132 /* GT Closed Current */
133 , nvl(oset.c_closed_total,0) BIV_MEASURE11
134 /* GT Closed Change */
135 , ' ||
136 biv_dbi_tmpl_util.change_column('oset.c_closed_total','oset.p_closed_total','BIV_MEASURE12') ||
137 '
138 /* GT Average Time To Close Current */
139 , ' ||
140 biv_dbi_tmpl_util.rate_column('oset.c_time_to_close_total','oset.c_closed_total','BIV_MEASURE13','N') ||
141 '
142 /* GT Average Time To Close Change */
143 , ' ||
144 biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('oset.c_time_to_close_total','oset.c_closed_total',null,'N')
145 ,biv_dbi_tmpl_util.rate_column('oset.p_time_to_close_total','oset.p_closed_total',null,'N')
146 ,'BIV_MEASURE14'
147 ,'N') ||
148 '
149 /* GT Time to Close Buckets*/
150 ' || biv_dbi_tmpl_util.get_bucket_outer_query
151 ( p_bucket_rec => l_bucket_rec
152 , p_column_name_base => 'oset.c_close_bucket'
153 , p_alias_base => 'BIV_MEASURE15'
154 , p_total_flag => 'Y'
155 , p_backlog_col => case
156 when p_distribution = 'Y' then 'oset.c_closed'
157 end
158 ) ||
159 '
160 /* KPI GT Average Time To Close Prior */
161 , ' ||
162 biv_dbi_tmpl_util.rate_column('oset.p_time_to_close_total','oset.p_closed_total','BIV_MEASURE16','N') ||
163 '
164 , ' ||
165 biv_dbi_tmpl_util.get_category_drill_down( l_view_by
166 , case
167 when p_distribution = 'Y' then g_closure_dbn_rep_func
168 else g_closure_rep_func
169 end ) ||
170 biv_dbi_tmpl_util.drill_detail( g_closed_detail_rep_func
171 , 0
172 , null
173 , 'BIV_ATTRIBUTE6') ||
174 case
175 when p_distribution = 'N' then
176 biv_dbi_tmpl_util.bucket_detail_drill( g_closed_detail_rep_func
177 , l_bucket_rec
178 , 'BIV_ATTRIBUTE7' )
179 else
180 null
181 end ||
182 '
183 from
184 ( select * from ( ' || poa_dbi_template_pkg.status_sql
185 ( P_FACT_NAME => l_mv
186 , P_WHERE_CLAUSE => l_where_clause
187 , P_JOIN_TABLES => l_join_tbl
188 , P_USE_WINDOWING => 'N'
189 , P_COL_NAME => l_col_tbl
190 , P_USE_GRPID => 'N'
191 , P_PAREN_COUNT => 3
192 , P_FILTER_WHERE => '(c_closed > 0 or p_closed > 0)'
193 , P_GENERATE_VIEWBY => 'Y'
194 );
195
196 biv_dbi_tmpl_util.override_order_by(l_view_by, p_param, l_stmt);
197
198 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
199 -- l_stmt := l_stmt || biv_dbi_tmpl_util.dump_parameters(p_param);
200
201 x_custom_sql := l_stmt;
202
203 poa_dbi_util_pkg.get_custom_rolling_binds
204 ( p_custom_output => l_custom_output
205 , p_xtd => l_xtd
206 );
207
208 x_custom_output := l_custom_output;
209
210 end get_tbl_sql;
211
212 procedure get_dbn_tbl_sql
213 ( p_param in bis_pmv_page_parameter_tbl
214 , x_custom_sql out nocopy varchar2
215 , x_custom_output out nocopy bis_query_attributes_tbl
216 )
217 is
218 begin
219 get_tbl_sql
220 ( p_param => p_param
221 , x_custom_sql => x_custom_sql
222 , x_custom_output => x_custom_output
223 , p_distribution => 'Y'
224 );
225 end get_dbn_tbl_sql;
226
227
228 procedure get_trd_sql
229 ( p_param in bis_pmv_page_parameter_tbl
230 , x_custom_sql out nocopy varchar2
231 , x_custom_output out nocopy bis_query_attributes_tbl
232 , p_distribution in varchar2 := 'N'
233 )
234 is
235
236 l_view_by varchar2(200);
237 l_view_by_col_name varchar2(200);
238 l_comparison_type varchar2(200);
239 l_xtd varchar2(200);
240 l_where_clause varchar2(10000);
241 l_mv varchar2(10000);
242 l_stmt varchar2(32767);
243
244 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
245
246 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
247 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
248
249 l_custom_output bis_query_attributes_tbl;
250
251 l_to_date_type VARCHAR2 (3) ;
252 l_as_of_date date;
253
254 begin
255
256 -- clear out the tables.
257 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
258 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
259
260 biv_dbi_tmpl_util.process_parameters
261 ( p_param => p_param
262 , p_report_type => 'CLOSED'
263 , p_trend => 'Y'
264 , x_view_by => l_view_by
265 , x_view_by_col_name => l_view_by_col_name
266 , x_comparison_type => l_comparison_type
267 , x_xtd => l_xtd
268 , x_where_clause => l_where_clause
269 , x_mv => l_mv
270 , x_join_tbl => l_join_tbl
271 , x_as_of_date => l_as_of_date
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 => 'closed_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_close'
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_CLOSURE_CYCLE_TIME'
297 , p_col_tbl => l_col_tbl
298 , p_col_name => 'time_to_close'
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_CLO_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_CLO_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 poa_dbi_util_pkg.get_custom_day_binds(p_custom_output => l_custom_output,
384 p_as_of_date => l_as_of_date,
385 p_comparison_type => l_comparison_type);
386 null;
387 END IF;
388
389 x_custom_output := l_custom_output;
390
391 end get_trd_sql;
392
393 procedure get_dbn_trd_sql
394 ( p_param in bis_pmv_page_parameter_tbl
395 , x_custom_sql out nocopy varchar2
396 , x_custom_output out nocopy bis_query_attributes_tbl
397 )
398 is
399 begin
400 get_trd_sql
401 ( p_param => p_param
402 , x_custom_sql => x_custom_sql
403 , x_custom_output => x_custom_output
404 , p_distribution => 'Y'
405 );
406 end get_dbn_trd_sql;
407
408 procedure get_detail_sql
409 ( p_param in bis_pmv_page_parameter_tbl
410 , x_custom_sql out nocopy varchar2
411 , x_custom_output out nocopy bis_query_attributes_tbl
412 )as
413
414 l_where_clause varchar2(10000);
415 l_mv varchar2(10000);
416 l_join_from varchar2(10000);
417 l_join_where varchar2(10000);
418 l_order_by varchar2(100);
419 l_drill_url varchar2(500);
420 l_sr_id varchar2(100);
421
422 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
423 l_custom_output bis_query_attributes_tbl;
424 l_as_of_date date;
425
426 l_xtd varchar2(200);
427 begin
428
429 biv_dbi_tmpl_util.get_detail_page_function( l_drill_url, l_sr_id );
430
431 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
432
433 biv_dbi_tmpl_util.process_parameters
434 ( p_param => p_param
435 , p_report_type => 'CLOSED_DETAIL'
436 , x_where_clause => l_where_clause
437 , x_mv => l_mv
438 , x_xtd => l_xtd
439 , x_join_from => l_join_from
440 , x_join_where => l_join_where
441 , x_join_tbl => l_join_tbl
442 , x_as_of_date => l_as_of_date
443 );
444
445
446 if l_where_clause like '%<replace this>%' then
447 l_where_clause := replace(l_where_clause,'fact.<replace this> in (&'||biv_dbi_tmpl_util.g_AGING||')'
448 ,'(&RANGE_LOW is null or fact.age >= &RANGE_LOW) and (&RANGE_HIGH is null or fact.age < &RANGE_HIGH)');
449
450 biv_dbi_tmpl_util.bind_low_high
451 ( p_param
452 , 'BIV_DBI_CLOSURE_CYCLE_TIME'
453 , '&RANGE_LOW'
454 , '&RANGE_HIGH'
455 , l_custom_output );
456
457 end if;
458
459 l_order_by := biv_dbi_tmpl_util.get_order_by(p_param);
460 if l_order_by like '% DESC%' then
461 if l_order_by like '%BIV_MEASURE11%' then
462 l_order_by := 'fact.closed_date desc, fact.incident_id desc';
463 else
464 l_order_by := 'fact.age desc, fact.incident_id desc';
465 end if;
466 else
467 if l_order_by like '%BIV_MEASURE11%' then
468 l_order_by := 'fact.closed_date asc, fact.incident_id asc';
469 else
470 l_order_by := 'fact.age asc, fact.incident_id asc';
471 end if;
472 end if;
473
474 x_custom_sql := '
475 select
476 i.incident_number biv_measure1
477 , rt.value biv_measure2 -- request_type
478 , pr.value biv_measure3 -- product
479 , pr.description biv_measure4
480 , cu.value biv_measure5 -- customer
481 , sv.value biv_measure6 -- severity
482 , ag.value biv_measure7 -- assignment_group
483 , re.value biv_measure8 -- resolution
484 , ch.value biv_measure9 -- channel
485 , fact.age biv_measure10
486 , fnd_date.date_to_displaydate(fact.closed_date) biv_measure11 ' ||
487 case
488 when l_drill_url is not null then
489 '
490 , ''pFunctionName=' || l_drill_url || '&' || l_sr_id || '=''||fact.incident_id biv_attribute1'
491 else
492 '
493 , null biv_attribute1'
494 end ||
495 '
496 from
497 ( select
498 fact.*
499 , rank() over(order by ' || l_order_by || ') -1 rnk
500 from
501 ' || l_mv || ' fact
502 where
503 fact.closed_date between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_EFFECTIVE_END_DATE
504 ' || l_where_clause || '
505 ) fact
506 ' || l_join_from || '
507 , cs_incidents_all_b i
508 where
509 1=1
510 and fact.incident_id = i.incident_id' || l_join_where || '
511 and (fact.rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
512 &ORDER_BY_CLAUSE
513 '
514 --|| biv_dbi_tmpl_util.dump_parameters(p_param)
515 ;
516
517 if l_custom_output is null then
518 l_custom_output := bis_query_attributes_tbl();
519 end if;
520
521 x_custom_output := l_custom_output;
522
523 end get_detail_sql;
524
525 end biv_dbi_clo_pkg;