[Home] [Help]
PACKAGE BODY: APPS.ISC_FS_MTTR_RPT_PKG
Source
1 package body isc_fs_mttr_rpt_pkg
2 /* $Header: iscfsmttrrptb.pls 120.5 2006/04/12 20:44:48 kreardon noship $ */
3 as
4
5 g_detail_rep_func constant varchar2(50) := 'ISC_FS_MTTR_RPT_REP';
6 g_task_rep_func constant varchar2(50) := 'ISC_FS_MTTR_TBL_REP';
7 g_trd_rep_func constant varchar2(50) := 'ISC_FS_MTTR_TRD_REP';
8
9 function get_fact_mv_name
10 ( p_report_type in varchar2
11 , p_param in bis_pmv_page_parameter_tbl
12 , p_dim_bmap in number
13 , p_custom_output in out nocopy bis_query_attributes_tbl
14 )
15 return varchar2
16 is
17
18 l_top_node varchar2(1);
19 l_resource varchar2(1);
20 l_district_leaf_node varchar2(1);
21
22 begin
23
24 if nvl(isc_fs_rpt_util_pkg.get_parameter_value
25 ( p_param
26 , isc_fs_rpt_util_pkg.G_CATEGORY
27 ),'All') = 'All' then
28 l_top_node := 'Y';
29 else
30 l_top_node := 'N';
31 end if;
32
33 if p_report_type = 'MTTR' then
34
35 l_district_leaf_node := isc_fs_rpt_util_pkg.is_district_leaf_node
36 ( p_param );
37
38 isc_fs_rpt_util_pkg.bind_group_id
39 ( p_dim_bmap
40 , p_custom_output
41 , isc_fs_rpt_util_pkg.G_CATEGORY
42 , isc_fs_rpt_util_pkg.G_PRODUCT
43 , isc_fs_rpt_util_pkg.G_CUSTOMER
44 );
45
46 if bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
47
48 return '(
49 select
50 v.top_node_flag vbh_top_node_flag
51 , v.parent_id vbh_parent_category_id
52 , v.imm_child_id vbh_child_category_id
53 , f.time_id
54 , f.period_type_id ' || case
55 when l_district_leaf_node = 'N' then '
56 , den.parent_prg_id parent_district_id
57 , decode( den.record_type, ''GROUP'', den.record_type, f.record_type ) record_type
58 , decode( den.record_type, ''GROUP'', den.prg_id , f.district_id ) district_id
59 , decode( den.record_type, ''GROUP'', to_char(den.prg_id), f.district_id_c ) district_id_c'
60 else '
61 , f.parent_district_id
62 , f.record_type
63 , f.district_id
64 , f.district_id_c'
65 end || '
66 , f.customer_id
67 , f.product_id
68 , f.incident_severity_id
69 , f.resolved_count
70 , f.total_time_to_resolve
71 , f.time_to_resolve_b1
72 , f.time_to_resolve_b2
73 , f.time_to_resolve_b3
74 , f.time_to_resolve_b4
75 , f.time_to_resolve_b5
76 , f.time_to_resolve_b6
77 , f.time_to_resolve_b7
78 , f.time_to_resolve_b8
79 , f.time_to_resolve_b9
80 , f.time_to_resolve_b10
81 from
82 isc_fs_009_mv f' || case
83 when l_district_leaf_node = 'N' then '
84 , isc_fs_002_mv den'
85 end || '
86 , eni_denorm_hierarchies v
87 , mtl_default_category_sets m
88 where
89 m.functional_area_id = 11
90 and v.object_id = m.category_set_id
91 and v.dbi_flag = ''Y''
92 and v.object_type = ''CATEGORY_SET''
93 and f.vbh_category_id = v.child_id' || case
94 when l_district_leaf_node = 'N' then '
95 and f.parent_district_id = den.rg_id'
96 end || '
97 and f.grp_id = &ISC_GRP_ID' || case
98 when l_top_node = 'Y' then '
99 and v.top_node_flag = ''Y''' end || '
100 )';
101
102 elsif bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_PRODUCT_BMAP) = isc_fs_rpt_util_pkg.G_PRODUCT_BMAP or
103 bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CUSTOMER_BMAP) = isc_fs_rpt_util_pkg.G_CUSTOMER_BMAP or
104 bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_SEVERITY_BMAP) = isc_fs_rpt_util_pkg.G_SEVERITY_BMAP then
105
106 return '(
107 select
108 f.time_id
109 , f.period_type_id ' || case
110 when l_district_leaf_node = 'N' then '
111 , den.parent_prg_id parent_district_id
112 , decode( den.record_type, ''GROUP'', den.record_type, f.record_type ) record_type
113 , decode( den.record_type, ''GROUP'', den.prg_id , f.district_id ) district_id
114 , decode( den.record_type, ''GROUP'', to_char(den.prg_id), f.district_id_c ) district_id_c'
115 else '
116 , f.parent_district_id
117 , f.record_type
118 , f.district_id
119 , f.district_id_c'
120 end || '
121 , f.customer_id
122 , f.product_id
123 , f.incident_severity_id
124 , f.resolved_count
125 , f.total_time_to_resolve
126 , f.time_to_resolve_b1
127 , f.time_to_resolve_b2
128 , f.time_to_resolve_b3
129 , f.time_to_resolve_b4
130 , f.time_to_resolve_b5
131 , f.time_to_resolve_b6
132 , f.time_to_resolve_b7
133 , f.time_to_resolve_b8
134 , f.time_to_resolve_b9
135 , f.time_to_resolve_b10
136 from
137 isc_fs_009_mv f' || case
138 when l_district_leaf_node = 'N' then '
139 , isc_fs_002_mv den'
140 end || '
141 where
142 f.grp_id = &ISC_GRP_ID ' || case
143 when l_district_leaf_node = 'N' then '
144 and f.parent_district_id = den.rg_id'
145 end || '
146 )';
147
148 else
149
150 return '(
151 select
152 f.time_id
153 , f.period_type_id
154 , f.parent_district_id
155 , f.record_type
156 , f.district_id
157 , f.district_id_c
158 , f.resolved_count
159 , f.total_time_to_resolve
160 , f.time_to_resolve_b1
161 , f.time_to_resolve_b2
162 , f.time_to_resolve_b3
163 , f.time_to_resolve_b4
164 , f.time_to_resolve_b5
165 , f.time_to_resolve_b6
166 , f.time_to_resolve_b7
167 , f.time_to_resolve_b8
168 , f.time_to_resolve_b9
169 , f.time_to_resolve_b10
170 from isc_fs_010_mv f
171 )';
172
173 end if;
174
175 elsif p_report_type = 'MTTR_DETAIL' then
176
177 if isc_fs_rpt_util_pkg.get_parameter_id
178 ( p_param
179 , isc_fs_rpt_util_pkg.G_DISTRICT
180 ) like '%.%' then
181 l_resource := 'Y';
182 else
183 l_resource := 'N';
184 end if;
185
186 return '(
187 select
188 t.task_id
189 , t.task_number
190 , t.owner_id
191 , t.owner_type
192 , t.ttr_assignee_id assignee_id
193 , t.ttr_assignee_type assignee_type ' ||
194 case
195 when l_resource = 'N' then '
196 , d.parent_prg_id parent_district_id '
197 else '
198 , decode( t.ftf_ttr_district_rule, ''0'', t.ttr_assignee_id, t.owner_id ) || ''.'' || decode( t.ftf_ttr_district_rule, ''0'', t.ttr_district_id, t.owner_district_id ) district_id_c '
199 end || '
200 , t.source_object_name
201 , t.source_object_id
202 , t.incident_date
203 , r.customer_id
204 , r.time_to_resolution*24 time_to_resolve
205 , r.report_date
206 , nvl(s.master_id,s.id) product_id ' ||
207 case
208 when bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
209 '
210 , v.top_node_flag vbh_top_node_flag
211 , v.parent_id vbh_parent_category_id
212 , v.imm_child_id vbh_child_category_id'
213 end || '
214 , r.incident_severity_id
215 from
216 isc_fs_tasks_f t
217 , biv_dbi_resolution_sum_f r
218 , eni_oltp_item_star s' ||
219 case
220 when bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
221 '
222 , eni_denorm_hierarchies v
223 , mtl_default_category_sets m'
224 end ||
225 case
226 when l_resource = 'N' then '
227 , isc_fs_002_mv d'
228 end || '
229 where
230 t.include_task_in_ttr_flag = ''Y''
231 and t.source_object_id = r.incident_id
232 and r.time_to_resolution is not null
233 and r.inventory_item_id = s.inventory_item_id
234 and r.inv_organization_id = s.organization_id' ||
235 case
236 when bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
237 '
238 and m.functional_area_id = 11
239 and v.object_id = m.category_set_id
240 and v.dbi_flag = ''Y''
241 and v.object_type = ''CATEGORY_SET''
242 and s.vbh_category_id = v.child_id' ||
243 case l_top_node when 'Y' then ' and v.top_node_flag = ''Y''' end
244 end ||
245 case
246 when l_resource = 'N' then '
247 and d.rg_id = decode( t.ftf_ttr_district_rule, ''0'', t.ttr_district_id, t.owner_district_id )'
248 end || '
249 )';
250
251 else -- should not happen!!!
252 return '';
253
254 end if;
255
256 end get_fact_mv_name;
257
258 procedure get_tbl_sql
259 ( p_param in bis_pmv_page_parameter_tbl
260 , x_custom_sql out nocopy varchar2
261 , x_custom_output out nocopy bis_query_attributes_tbl
262 )
263 is
264
265 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
266 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
267 l_custom_output bis_query_attributes_tbl;
268 l_curr_suffix varchar2(3);
269 l_where_clause varchar2(10000);
270 l_viewby_select varchar2(400); -- needed to be increased from 200
271 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
272 l_dim_bmap number;
273 l_comparison_type varchar2(200);
274 l_xtd varchar2(200);
275 l_mv varchar2(10000);
276 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
277 l_view_by varchar2(200);
278 l_product varchar2(50);
279 l_stmt varchar2(32700);
280
281 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
282 l_to_date_type varchar2(200);
283
284 begin
285
286 isc_fs_rpt_util_pkg.register_dimension_levels
287 ( l_dimension_tbl
288 , l_dim_filter_map
289 , isc_fs_rpt_util_pkg.G_CATEGORY, 'Y'
290 , isc_fs_rpt_util_pkg.G_PRODUCT, 'Y'
291 , isc_fs_rpt_util_pkg.G_CUSTOMER, 'Y'
292 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
293 , isc_fs_rpt_util_pkg.G_SEVERITY, 'Y'
294 );
295
296 isc_fs_rpt_util_pkg.process_parameters
297 ( p_param => p_param
298 , p_dimension_tbl => l_dimension_tbl
299 , p_dim_filter_map => l_dim_filter_map
300 , p_trend => 'N'
301 , p_custom_output => l_custom_output
302 , x_cur_suffix => l_curr_suffix
303 , x_where_clause => l_where_clause
304 , x_viewby_select => l_viewby_select
305 , x_join_tbl => l_join_tbl
306 , x_dim_bmap => l_dim_bmap
307 , x_comparison_type => l_comparison_type
308 , x_xtd => l_xtd
309 );
310
311 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
312 l_to_date_type := 'XTD';
313 else
314 l_to_date_type := 'RLX';
315 end if;
316
317 l_view_by := isc_fs_rpt_util_pkg.get_parameter_value
318 ( p_param
319 , 'VIEW_BY'
320 );
321
322 if l_view_by = isc_fs_rpt_util_pkg.G_PRODUCT then
323 l_product := 'v4.description ISC_ATTRIBUTE_2';
324 else
325 l_product := 'null ISC_ATTRIBUTE_2';
326 end if;
327
328 l_mv := get_fact_mv_name
329 ( 'MTTR'
330 , p_param
331 , l_dim_bmap
332 , l_custom_output
333 );
334
335 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
336
337 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
338 , p_col_name => 'resolved_count'
339 , p_alias_name => 'count'
340 , p_to_date_type => l_to_date_type
341 );
342
343 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
344 , p_col_name => 'total_time_to_resolve'
345 , p_alias_name => 'total_hours'
346 , p_to_date_type => l_to_date_type
347 );
348
349 poa_dbi_util_pkg.add_bucket_columns
350 ( p_short_name => 'BIV_FS_TIME_TO_RES'
351 , p_col_tbl => l_col_tbl
352 , p_col_name => 'time_to_resolve'
353 , p_alias_name => 'bucket'
354 , p_prior_code => poa_dbi_util_pkg.no_priors
355 , p_to_date_type => l_to_date_type
356 , x_bucket_rec => l_bucket_rec
357 );
358
359
360 l_stmt := 'select
361 ' || l_viewby_select || '
362 , ISC_MEASURE_1
363 , ISC_MEASURE_2
364 , ISC_MEASURE_3
365 , ISC_MEASURE_4
366 ' || poa_dbi_util_pkg.get_bucket_outer_query
367 ( p_bucket_rec => l_bucket_rec
368 , p_col_name => 'ISC_MEASURE_5'
369 , p_alias_name => 'ISC_MEASURE_5'
370 , p_prefix => null
371 , p_suffix => null
372 , p_total_flag => 'N'
373 ) || '
374 , ISC_MEASURE_1 ISC_MEASURE_6
375 , ISC_MEASURE_2 ISC_MEASURE_7
376 , ISC_MEASURE_2 ISC_MEASURE_8
377 , ISC_MEASURE_3 ISC_MEASURE_9
378 , ISC_MEASURE_21
379 , ISC_MEASURE_22
380 , ISC_MEASURE_23
381 , ISC_MEASURE_24
382 ' || poa_dbi_util_pkg.get_bucket_outer_query
383 ( p_bucket_rec => l_bucket_rec
384 , p_col_name => 'ISC_MEASURE_25'
385 , p_alias_name => 'ISC_MEASURE_25'
386 , p_prefix => null
387 , p_suffix => null
388 , p_total_flag => 'N'
389 ) || '
390 , ISC_MEASURE_21 ISC_MEASURE_26
391 , ISC_MEASURE_22 ISC_MEASURE_27
392 , ISC_MEASURE_22 ISC_MEASURE_28
393 , ISC_MEASURE_23 ISC_MEASURE_29
394 , ' || l_product || '
395 , ' || isc_fs_rpt_util_pkg.get_district_drill_down
396 ( l_view_by
397 , g_task_rep_func
398 , 'ISC_ATTRIBUTE_3' ) || '
399 , ' || isc_fs_rpt_util_pkg.get_category_drill_down
400 ( l_view_by
401 , g_task_rep_func
402 , 'ISC_ATTRIBUTE_4' ) || '
403 , ' || isc_fs_rpt_util_pkg.get_bucket_drill_down
404 ( p_bucket_rec => l_bucket_rec
405 , p_view_by => l_view_by
406 , p_function_name => case
407 when l_view_by in ( isc_fs_rpt_util_pkg.G_PRODUCT
408 , isc_fs_rpt_util_pkg.G_CUSTOMER
409 , isc_fs_rpt_util_pkg.G_SEVERITY
410 , isc_fs_rpt_util_pkg.G_DISTRICT ) then
411 g_detail_rep_func
412 else null
413 end
414 , p_check_column_name => 'ISC_MEASURE_5'
415 , p_column_alias => 'ISC_ATTRIBUTE_5'
416 , p_extra_params => '&BIV_FS_TIME_TO_RES='
417 , p_check_resource => 'Y'
418 ) || '
419 from (
420 select
421 row_number() over(&ORDER_BY_CLAUSE nulls last, '|| isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
422 , iset.*
423 from ( select * from (
424 select ' || isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || '
425 , ' || isc_fs_rpt_util_pkg.rate_column
426 ( 'p_total_hours'
427 , 'p_count'
428 , 'ISC_MEASURE_1'
429 , 'N'
430 ) || '
431 , ' || isc_fs_rpt_util_pkg.rate_column
432 ( 'c_total_hours'
433 , 'c_count'
434 , 'ISC_MEASURE_2'
435 , 'N'
436 ) || '
437 , ' || isc_fs_rpt_util_pkg.change_column
438 ( isc_fs_rpt_util_pkg.rate_column
439 ( 'c_total_hours'
440 , 'c_count'
441 , null
442 , 'N'
443 )
444 , isc_fs_rpt_util_pkg.rate_column
445 ( 'p_total_hours'
446 , 'p_count'
447 , null
448 , 'N'
449 )
453 , nvl(c_count,0) ISC_MEASURE_4
450 , 'ISC_MEASURE_3'
451 , 'N'
452 ) || '
454 ' || poa_dbi_util_pkg.get_bucket_outer_query
455 ( p_bucket_rec => l_bucket_rec
456 , p_col_name => 'bucket'
457 , p_alias_name => 'ISC_MEASURE_5'
458 , p_prefix => 'nvl(c_'
459 , p_suffix => ',0)/abs(decode(c_count,0,null,c_count))*100'
460 , p_total_flag => 'N'
461 ) || '
462 , ' || isc_fs_rpt_util_pkg.rate_column
463 ( 'p_total_hours_total'
464 , 'p_count_total'
465 , 'ISC_MEASURE_21'
466 , 'N'
467 ) || '
468 , ' || isc_fs_rpt_util_pkg.rate_column
469 ( 'c_total_hours_total'
470 , 'c_count_total'
471 , 'ISC_MEASURE_22'
472 , 'N'
473 ) || '
474 , ' || isc_fs_rpt_util_pkg.change_column
475 ( isc_fs_rpt_util_pkg.rate_column
476 ( 'c_total_hours_total'
477 , 'c_count_total'
478 , null
479 , 'N'
480 )
481 , isc_fs_rpt_util_pkg.rate_column
482 ( 'p_total_hours_total'
483 , 'p_count_total'
484 , null
485 , 'N'
486 )
487 , 'ISC_MEASURE_23'
488 , 'N'
489 ) || '
490 , nvl(c_count_total,0) ISC_MEASURE_24
491 ' || poa_dbi_util_pkg.get_bucket_outer_query
492 ( p_bucket_rec => l_bucket_rec
493 , p_col_name => 'bucket'
494 , p_alias_name => 'ISC_MEASURE_25'
495 , p_prefix => 'nvl(c_'
496 , p_suffix => ',0)/abs(decode(c_count_total,0,null,c_count_total))*100'
497 , p_total_flag => 'Y'
498 ) || '
499 from ' || poa_dbi_template_pkg.status_sql
500 ( p_fact_name => l_mv
501 , p_where_clause => l_where_clause
502 , p_join_tables => l_join_tbl
503 , p_use_windowing => 'Y' --'N'
504 , p_col_name => l_col_tbl
505 , p_use_grpid => 'N'
506 , p_paren_count => 3
507 , p_filter_where => '1=1 ) iset '
508 , p_generate_viewby => 'Y'
509 );
510
511 isc_fs_rpt_util_pkg.enhance_time_join
512 ( l_stmt
513 , 'N'
514 );
515
516 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
517 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
518
519 poa_dbi_util_pkg.get_custom_rolling_binds
520 ( p_custom_output => l_custom_output
521 , p_xtd => l_xtd
522 );
523
524 x_custom_output := l_custom_output;
525
526 x_custom_sql := l_stmt;
527
528 end get_tbl_sql;
529
530 procedure get_trd_sql
531 ( p_param in bis_pmv_page_parameter_tbl
532 , x_custom_sql out nocopy varchar2
533 , x_custom_output out nocopy bis_query_attributes_tbl
534 )
535 is
536
537 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
538 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
539 l_custom_output bis_query_attributes_tbl;
540 l_curr_suffix varchar2(3);
541 l_where_clause varchar2(10000);
542 l_viewby_select varchar2(400); -- needed to be increased from 200
543 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
544 l_dim_bmap number;
545 l_comparison_type varchar2(200);
546 l_xtd varchar2(200);
547 l_mv varchar2(10000);
548 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
549 l_stmt varchar2(32767);
550 l_to_date_type varchar2(200);
551
552 begin
553
554 isc_fs_rpt_util_pkg.register_dimension_levels
555 ( l_dimension_tbl
556 , l_dim_filter_map
557 , isc_fs_rpt_util_pkg.G_CATEGORY, 'Y'
558 , isc_fs_rpt_util_pkg.G_PRODUCT, 'Y'
559 , isc_fs_rpt_util_pkg.G_CUSTOMER, 'Y'
560 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
561 , isc_fs_rpt_util_pkg.G_SEVERITY, 'Y'
562 );
563
564 isc_fs_rpt_util_pkg.process_parameters
565 ( p_param => p_param
566 , p_dimension_tbl => l_dimension_tbl
567 , p_dim_filter_map => l_dim_filter_map
568 , p_trend => 'Y'
569 , p_custom_output => l_custom_output
570 , x_cur_suffix => l_curr_suffix
571 , x_where_clause => l_where_clause
572 , x_viewby_select => l_viewby_select
573 , x_join_tbl => l_join_tbl
574 , x_dim_bmap => l_dim_bmap
575 , x_comparison_type => l_comparison_type
576 , x_xtd => l_xtd
577 );
578
579 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
580 l_to_date_type := 'XTD';
581 else
582 l_to_date_type := 'RLX';
583 end if;
584
585 --l_where_clause := l_where_clause || ' and fact.closed > 0';
586
587 l_mv := get_fact_mv_name
588 ( 'MTTR'
589 , p_param
590 , l_dim_bmap
591 , l_custom_output
592 );
593
594 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
595
596 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
597 , p_col_name => 'resolved_count'
598 , p_alias_name => 'count'
599 , p_to_date_type => l_to_date_type
600 , p_grand_total => 'N'
601 );
602
603 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
604 , p_col_name => 'total_time_to_resolve'
605 , p_alias_name => 'total_hours'
606 , p_to_date_type => l_to_date_type
610 l_stmt := 'select
607 , p_grand_total => 'N'
608 );
609
611 cal.name VIEWBY
612 , ' || isc_fs_rpt_util_pkg.rate_column
613 ( 'iset.p_total_hours'
614 , 'iset.p_count'
615 , 'ISC_MEASURE_1'
616 , 'N'
617 ) || '
618 , ' || isc_fs_rpt_util_pkg.rate_column
619 ( 'iset.c_total_hours'
620 , 'iset.c_count'
621 , 'ISC_MEASURE_2'
622 , 'N'
623 ) || '
624 , ' || isc_fs_rpt_util_pkg.change_column
625 ( isc_fs_rpt_util_pkg.rate_column
626 ( 'iset.c_total_hours'
627 , 'iset.c_count'
628 , null
629 , 'N'
630 )
631 , isc_fs_rpt_util_pkg.rate_column
632 ( 'iset.p_total_hours'
633 , 'iset.p_count'
634 , null
635 , 'N'
636 )
637 , 'ISC_MEASURE_3'
638 , 'N'
639 ) ||
640 isc_fs_rpt_util_pkg.get_trend_drill
641 ( l_xtd
642 , g_trd_rep_func
643 , 'ISC_ATTRIBUTE_2'
644 , 'ISC_ATTRIBUTE_3'
645 ) || '
646 from
647 ' || poa_dbi_template_pkg.trend_sql
648 ( p_xtd => l_xtd
649 , p_comparison_type => l_comparison_type
650 , p_fact_name => l_mv
651 , p_where_clause => l_where_clause
652 , p_col_name => l_col_tbl
653 , p_use_grpid => 'N'
654 );
655
656 isc_fs_rpt_util_pkg.enhance_time_join
657 ( l_stmt
658 , 'Y'
659 );
660
661 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
662 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
663
664 x_custom_sql := l_stmt;
665
666 x_custom_output := l_custom_output;
667
668 poa_dbi_util_pkg.get_custom_rolling_binds
669 ( p_custom_output => l_custom_output
670 , p_xtd => l_xtd
671 );
672
673 x_custom_output := l_custom_output;
674
675 poa_dbi_util_pkg.get_custom_trend_binds
676 ( x_custom_output => l_custom_output
677 , p_xtd => l_xtd
678 , p_comparison_type => l_comparison_type
679 );
680
681 if l_custom_output is not null then
682 for i in 1..l_custom_output.count loop
683 x_custom_output.extend;
684 x_custom_output(x_custom_output.count) := l_custom_output(i);
685 end loop;
686 end if;
687
688 end get_trd_sql;
689
690 procedure get_dtl_rpt_sql
691 ( p_param in bis_pmv_page_parameter_tbl
692 , x_custom_sql out nocopy varchar2
693 , x_custom_output out nocopy bis_query_attributes_tbl
694 )
695 as
696
697 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
698 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
699 l_custom_output bis_query_attributes_tbl;
700 l_curr_suffix varchar2(3);
701 l_where_clause varchar2(10000);
702 l_viewby_select varchar2(400); -- needed to be increased from 200
703 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
704 l_dim_bmap number;
705 l_comparison_type varchar2(200);
706 l_xtd varchar2(200);
707 l_mv varchar2(10000);
708 l_cost_element varchar2(200);
709 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
710 l_stmt varchar2(32767);
711 l_rank_order varchar2(200);
712 l_detail_col_tbl isc_fs_rpt_util_pkg.t_detail_column_tbl;
713 l_order_by varchar2(200);
714 l_asc_desc varchar2(100);
715
716 begin
717
718 isc_fs_rpt_util_pkg.register_dimension_levels
719 ( l_dimension_tbl
720 , l_dim_filter_map
721 , isc_fs_rpt_util_pkg.G_CATEGORY, 'Y'
722 , isc_fs_rpt_util_pkg.G_PRODUCT, 'Y'
723 , isc_fs_rpt_util_pkg.G_CUSTOMER, 'Y'
724 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
725 , isc_fs_rpt_util_pkg.G_SEVERITY, 'Y'
726 , isc_fs_rpt_util_pkg.G_TASK_OWNER, 'N'
727 , isc_fs_rpt_util_pkg.G_TASK_ASSIGNEE, 'N'
728 );
729
730 isc_fs_rpt_util_pkg.process_parameters
731 ( p_param => p_param
732 , p_dimension_tbl => l_dimension_tbl
733 , p_dim_filter_map => l_dim_filter_map
734 , p_trend => 'D'
735 , p_custom_output => l_custom_output
736 , x_cur_suffix => l_curr_suffix
737 , x_where_clause => l_where_clause
738 , x_viewby_select => l_viewby_select
739 , x_join_tbl => l_join_tbl
740 , x_dim_bmap => l_dim_bmap
741 , x_comparison_type => l_comparison_type
742 , x_xtd => l_xtd
743 );
744
745 l_mv := get_fact_mv_name
746 ( 'MTTR_DETAIL'
747 , p_param
748 , l_dim_bmap
749 , l_custom_output
750 );
751
752 l_order_by := isc_fs_rpt_util_pkg.get_parameter_value
753 ( p_param
754 , 'ORDERBY'
755 );
756
757 if l_order_by like '% DESC%' then
758 l_asc_desc := ' desc ';
759 else
760 l_asc_desc := ' asc ';
761 end if;
762
763 l_rank_order := 'order by ' ||
764 case
765 when l_order_by like '%ISC_MEASURE_1 %' then
766 'report_date'
767 when l_order_by like '%ISC_MEASURE_2 %' then
768 'time_to_resolve'
769 end ||
770 l_asc_desc ||
771 'nulls last, task_id';
772
773 isc_fs_rpt_util_pkg.add_detail_column
777 , p_fact_col_total => 'N'
774 ( p_detail_col_tbl => l_detail_col_tbl
775 , p_dimension_tbl => l_dimension_tbl
776 , p_fact_col_name => 'source_object_name'
778 , p_column_key => 'source_object_name'
779 );
780
781 isc_fs_rpt_util_pkg.add_detail_column
782 ( p_detail_col_tbl => l_detail_col_tbl
783 , p_dimension_tbl => l_dimension_tbl
784 , p_fact_col_name => 'source_object_id'
785 , p_fact_col_total => 'N'
786 , p_column_key => 'source_object_id'
787 );
788
789 isc_fs_rpt_util_pkg.add_detail_column
790 ( p_detail_col_tbl => l_detail_col_tbl
791 , p_dimension_tbl => l_dimension_tbl
792 , p_dimension_level => isc_fs_rpt_util_pkg.G_CUSTOMER
793 , p_column_key => 'customer'
794 );
795
796 isc_fs_rpt_util_pkg.add_detail_column
797 ( p_detail_col_tbl => l_detail_col_tbl
798 , p_dimension_tbl => l_dimension_tbl
799 , p_dimension_level => isc_fs_rpt_util_pkg.G_PRODUCT
800 , p_column_key => 'product'
801 );
802
803 isc_fs_rpt_util_pkg.add_detail_column
804 ( p_detail_col_tbl => l_detail_col_tbl
805 , p_dimension_tbl => l_dimension_tbl
806 , p_fact_col_name => 'task_number'
807 , p_fact_col_total => 'N'
808 , p_column_key => 'task_number'
809 );
810
811 isc_fs_rpt_util_pkg.add_detail_column
812 ( p_detail_col_tbl => l_detail_col_tbl
813 , p_dimension_tbl => l_dimension_tbl
814 , p_fact_col_name => 'task_id'
815 , p_fact_col_total => 'N'
816 , p_column_key => 'task_id'
817 );
818
819 isc_fs_rpt_util_pkg.add_detail_column
820 ( p_detail_col_tbl => l_detail_col_tbl
821 , p_dimension_tbl => l_dimension_tbl
822 , p_dimension_level => isc_fs_rpt_util_pkg.G_TASK_OWNER
823 , p_column_key => 'task_owner'
824 );
825
826 isc_fs_rpt_util_pkg.add_detail_column
827 ( p_detail_col_tbl => l_detail_col_tbl
828 , p_dimension_tbl => l_dimension_tbl
829 , p_dimension_level => isc_fs_rpt_util_pkg.G_TASK_ASSIGNEE
830 , p_column_key => 'task_assignee'
831 );
832
833 isc_fs_rpt_util_pkg.add_detail_column
834 ( p_detail_col_tbl => l_detail_col_tbl
835 , p_dimension_tbl => l_dimension_tbl
836 , p_fact_col_name => 'report_date'
837 , p_fact_col_total => 'N'
838 , p_column_key => 'report_date'
839 );
840
841 isc_fs_rpt_util_pkg.add_detail_column
842 ( p_detail_col_tbl => l_detail_col_tbl
843 , p_dimension_tbl => l_dimension_tbl
844 , p_fact_col_name => 'time_to_resolve'
845 , p_fact_col_total => 'N'
846 , p_column_key => 'time_to_resolve'
847 );
848
849 l_stmt := 'select
850 oset.source_object_name ISC_ATTRIBUTE_1
851 , ' || isc_fs_rpt_util_pkg.get_detail_column
852 (l_detail_col_tbl,'customer','ISC_ATTRIBUTE_2') || '
853 , ' || isc_fs_rpt_util_pkg.get_detail_column
854 (l_detail_col_tbl,'product','ISC_ATTRIBUTE_3') || '
855 , oset.task_number ISC_ATTRIBUTE_4
856 , ' || isc_fs_rpt_util_pkg.get_detail_column
857 (l_detail_col_tbl,'task_owner','ISC_ATTRIBUTE_5') || '
858 , ' || isc_fs_rpt_util_pkg.get_detail_column
859 (l_detail_col_tbl,'task_assignee','ISC_ATTRIBUTE_6') || '
860 , oset.report_date ISC_MEASURE_1
861 , oset.time_to_resolve ISC_MEASURE_2
862 , ' || isc_fs_rpt_util_pkg.get_sr_detail_page_function('oset.source_object_id') || ' ISC_ATTRIBUTE_7
863 , null ISC_ATTRIBUTE_8'
864 -- above is needed to associate bucket set with report
865 || '
866 , ' || isc_fs_rpt_util_pkg.get_task_detail_page_function('oset.task_id') || ' ISC_ATTRIBUTE_9
867 from
868 ' || isc_fs_rpt_util_pkg.detail_sql
869 ( p_detail_col_tbl => l_detail_col_tbl
870 , p_dimension_tbl => l_dimension_tbl
871 , p_mv_name => l_mv
872 , p_where_clause => l_where_clause || ' and time_to_resolve between &ISC_FS_LOW and &ISC_FS_HIGH'
873 , p_rank_order => l_rank_order
874 , p_override_date_clause => 'report_date >= &BIS_CURRENT_EFFECTIVE_START_DATE and report_date < &BIS_CURRENT_ASOF_DATE +1'
875 );
876
877 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
878 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
879
880 x_custom_sql := l_stmt;
881
882 isc_fs_rpt_util_pkg.bind_low_high
883 ( p_param
884 , isc_fs_rpt_util_pkg.G_TIME_TO_RES_DISTRIB
885 , 'BIV_FS_TIME_TO_RES'
886 , l_custom_output
887 );
888
889 x_custom_output := l_custom_output;
890
891 end get_dtl_rpt_sql;
892
893 end isc_fs_mttr_rpt_pkg;