[Home] [Help]
PACKAGE BODY: APPS.ISC_MAINT_REQ_CMPL_RPT_PKG
Source
1 package body isc_maint_req_cmpl_rpt_pkg
2 /* $Header: iscmaintreqcmrpb.pls 120.2 2006/02/03 03:24:59 nbhamidi noship $ */
3 as
4
5 procedure bind_request_id
6 ( p_custom_output in out nocopy bis_query_attributes_tbl
7 , p_param in bis_pmv_page_parameter_tbl
8 ) is
9
10 l_custom_rec BIS_QUERY_ATTRIBUTES;
11
12 begin
13
14 if p_custom_output is null then
15 p_custom_output := bis_query_attributes_tbl();
16 end if;
17
18 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
19
20 l_custom_rec.attribute_name := '&ISC_REQUEST_ID' ;
21 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
22 l_custom_rec.attribute_value := isc_maint_rpt_util_pkg.get_parameter_value
23 ( p_param
24 , 'BIV_ATTRIBUTE9'
25 );
26 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
27 p_custom_output.extend;
28 p_custom_output(p_custom_output.count) := l_custom_rec;
29
30 end bind_request_id;
31
32 function get_fact_mv_name
33 ( p_report_type in varchar2
34 , p_param in bis_pmv_page_parameter_tbl
35 , p_dim_bmap in number
36 , p_custom_output in out nocopy bis_query_attributes_tbl
37 )
38 return varchar2
39 is
40
41 l_org_id varchar2(200);
42 l_view_by varchar2(200);
43 l_bucket_num varchar2(200);
44
45 begin
46
47 l_org_id := isc_maint_rpt_util_pkg.get_parameter_id
48 ( p_param
49 , isc_maint_rpt_util_pkg.G_ORGANIZATION
50 );
51
52 if p_report_type = 'REQUEST_TO_COMPLETION' then
53
54 l_view_by := isc_maint_rpt_util_pkg.get_parameter_value
55 ( p_param
56 , 'VIEW_BY'
57 );
58
59 isc_maint_rpt_util_pkg.bind_group_id
60 ( p_dim_bmap
61 , p_custom_output
62 , isc_maint_rpt_util_pkg.G_ASSET_GROUP
63 , isc_maint_rpt_util_pkg.G_ASSET_NUMBER
64 , isc_maint_rpt_util_pkg.G_DEPARTMENT
65 , isc_maint_rpt_util_pkg.G_REQ_CMPL_AGING
66 );
67
68 return '(
69 select
70 time_id
71 , period_type_id
72 , grp_id
73 , ' ||
74 case l_view_by /* replaced asset_number with instance_id */
75 when isc_maint_rpt_util_pkg.G_DEPARTMENT then
76 'decode(department_id,-1,-1,organization_id) organization_id'
77 when isc_maint_rpt_util_pkg.G_ASSET_GROUP then
78 'decode(asset_group_id,-1,-1,organization_id) organization_id'
79 when isc_maint_rpt_util_pkg.G_ASSET_NUMBER then
80 'decode(instance_id,-1,-1,organization_id) organization_id'
81 else
82 'organization_id'
83 end ||'
84 , request_type
85 , ' ||
86 case l_view_by
87 when isc_maint_rpt_util_pkg.G_ASSET_NUMBER then /* replaced asset_number with instance_id */
88 'decode(instance_id,-1,-1,asset_group_id) asset_group_id'
89 else
90 'asset_group_id'
91 end || '
92 , instance_id
93 , to_char(department_id) department_id
94 , bucket_num
95 , organization_id organization_id_c
96 , decode(asset_group_id,-1,-1,asset_group_id) asset_group_id_c /* removed concatenation to org. to make asset group independent of org. */
97 , decode(instance_id,-1,-1,instance_id) instance_id_c /* replaced asset_number with instance_id */
98 , decode(department_id,-1,''-1'',department_id||''-1'') department_id_c
99 , total_requests
100 , total_response_days
101 , total_completion_days
102 from isc_maint_003_mv fact
103 where fact.grp_id = &ISC_GRP_ID' ||
104 case
105 when l_org_id is null then
106 '
107 and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'fact', l_org_id )
108 end || '
109 )';
110
111 elsif p_report_type = 'REQUEST_TO_COMPLETION_DTL' then
112
113 l_bucket_num := isc_maint_rpt_util_pkg.get_parameter_id
114 ( p_param
115 , isc_maint_rpt_util_pkg.G_REQ_CMPL_AGING
116 );
117
118 return '(
119 select
120 f.completion_date report_date
121 , f.organization_id
122 , f.request_type
123 , f.maint_request_id
124 , f.request_number
125 , f.asset_group_id
126 , f.instance_id /* replaced asset_number with instance_id */
127 , to_char(f.department_id) department_id
128 , f.request_severity_id
129 , f.request_start_date
130 , f.response_days
131 , f.completion_days
132 , f.work_order_count
133 , f.organization_id organization_id_c
134 , decode(f.asset_group_id,-1,-1,f.asset_group_id) asset_group_id_c /* removed concatenation to org. to make asset group independent of org. */
135 , decode(f.instance_id,-1,-1,f.instance_id) instance_id_c /* replaced asset_number with instance_id */
136 , decode(f.department_id,-1,''-1'',f.department_id||''-1'') department_id_c' ||
137 case
138 when l_bucket_num is not null then '
139 , case
140 when f.completion_days < b.range1_high or
141 b.range1_high is null then 1
142 when f.completion_days >= b.range1_high and
143 (f.completion_days < b.range2_high or
144 b.range2_high is null) then 2
145 when f.completion_days >= b.range2_high and
146 (f.completion_days < b.range3_high or
147 b.range3_high is null) then 3
148 when f.completion_days >= b.range3_high and
149 (f.completion_days < b.range4_high or
150 b.range4_high is null) then 4
151 when f.completion_days >= b.range4_high and
152 (f.completion_days < b.range5_high or
153 b.range5_high is null) then 5
154 when f.completion_days >= b.range5_high and
155 (f.completion_days < b.range6_high or
156 b.range6_high is null) then 6
157 when f.completion_days >= b.range6_high and
158 (f.completion_days < b.range7_high or
159 b.range7_high is null) then 7
160 when f.completion_days >= b.range7_high and
161 (f.completion_days < b.range8_high or
162 b.range8_high is null) then 8
163 when f.completion_days >= b.range8_high and
164 (f.completion_days < b.range9_high or
165 b.range9_high is null) then 9
166 when f.completion_days >= b.range9_high and
167 (f.completion_days < b.range10_high or
168 b.range10_high is null) then 10
169 else 0
170 end bucket_num
171 '
172 end || '
173 from
174 isc_maint_req_wo_f f' ||
175 case
176 when l_bucket_num is not null then '
177 , bis_bucket_customizations b
178 , bis_bucket bb
179 '
180 end || '
181 where f.work_order_id is not null ' ||
182 case
183 when l_bucket_num is not null then '
184 and bb.short_name = ''BIV_MAINT_REQ_COMP_AGING''
185 and bb.bucket_id = b.bucket_id
186 '
187 end ||
188 case
189 when l_org_id is null then
190 '
191 and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'f', l_org_id )
192 end || '
193 )';
194
195
196 else
197
198 return '(
199 select
200 &BIS_CURRENT_ASOF_DATE report_date
201 , f.request_type
202 , w.organization_id
203 , w.work_order_id
204 , w.work_order_name
205 , w.work_order_type
206 , w.activity_id
207 , w.user_defined_status_id /* bug 5002342 */
208 , w.scheduled_start_date
209 , w.scheduled_completion_date
210 , w.completion_date
211 , f.organization_id organization_id_c
212 , decode(f.asset_group_id,-1,-1,f.asset_group_id) asset_group_id_c /* removed concatenation to org. to make it independent of org. */
213 , decode(f.instance_id,-1,-1,f.instance_id) instance_id_c /* replaced asset_number with instance_id */
214 , decode(f.department_id,-1,''-1'',f.department_id||''-1'') department_id_c
215 from
216 isc_maint_req_wo_f f
217 , isc_maint_work_orders_f w
218 where f.work_order_id = w.work_order_id
219 and f.organization_id = w.organization_id
220 and w.status_type <> 7
221 and f.maint_request_id = &ISC_REQUEST_ID' ||
222 case
223 when l_org_id is null then
224 '
225 and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'f', l_org_id )
226 end || '
227 )';
228
229 end if;
230
231 end get_fact_mv_name;
232
233 procedure get_tbl_sql
234 ( p_param in bis_pmv_page_parameter_tbl
235 , x_custom_sql out nocopy varchar2
236 , x_custom_output out nocopy bis_query_attributes_tbl
237 )
238 is
239
240 l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
241 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
242 l_custom_output bis_query_attributes_tbl;
243 l_curr_suffix varchar2(3);
244 l_where_clause varchar2(10000);
245 l_viewby_select varchar2(200);
246 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
247 l_dim_bmap number;
248 l_comparison_type varchar2(200);
249 l_xtd varchar2(200);
250 l_mv varchar2(10000);
251 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
252 l_stmt varchar2(32767);
253 l_asset_grp_column varchar2(200);
254 l_inner_query varchar2(5000);
255 begin
256
257 isc_maint_rpt_util_pkg.register_dimension_levels
258 ( l_dimension_tbl
259 , l_dim_filter_map
260 , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
261 , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
262 , isc_maint_rpt_util_pkg.G_REQUEST_TYPE, 'Y'
263 );
264
265 isc_maint_rpt_util_pkg.process_parameters
266 ( p_param => p_param
267 , p_dimension_tbl => l_dimension_tbl
268 , p_dim_filter_map => l_dim_filter_map
269 , p_trend => 'N'
270 , p_custom_output => l_custom_output
271 , x_cur_suffix => l_curr_suffix
272 , x_where_clause => l_where_clause
273 , x_viewby_select => l_viewby_select
274 , x_join_tbl => l_join_tbl
275 , x_dim_bmap => l_dim_bmap
276 , x_comparison_type => l_comparison_type
277 , x_xtd => l_xtd
278 );
279
280 l_mv := get_fact_mv_name
281 ( 'REQUEST_TO_COMPLETION'
282 , p_param
283 , l_dim_bmap
284 , l_custom_output
285 );
286
287 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
288
289 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
290 , p_col_name => 'total_requests'
291 , p_alias_name => 'requests'
292 , p_to_date_type => 'XTD'
293 );
294
295 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
296 , p_col_name => 'total_response_days'
297 , p_alias_name => 'response_days'
298 , p_to_date_type => 'XTD'
299 );
300
301 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
302 , p_col_name => 'total_completion_days'
303 , p_alias_name => 'completion_days'
304 , p_to_date_type => 'XTD'
305 );
306
307 /* if view by is asset_number then add the asset_group column */
308 if(isc_maint_rpt_util_pkg.get_parameter_id(p_param,'VIEW_BY')) =
309 isc_maint_rpt_util_pkg.G_ASSET_NUMBER then
310 l_asset_grp_column := isc_maint_rpt_util_pkg.add_asset_group_column(isc_maint_rpt_util_pkg.G_ASSET_NUMBER,l_dimension_tbl);
311 else
312 l_asset_grp_column :='NULL';
313 end if ;
314
315 /* to enable windowing we are using an addition inline view */
316 l_stmt := ' select ' || l_viewby_select ||
317 ',biv_measure1
318 ,biv_measure2
319 ,biv_measure3
320 ,biv_measure4
321 ,biv_measure5
322 ,biv_measure6
323 ,biv_measure7
324 ,biv_measure8
325 ,biv_measure9
326 ,biv_measure10
327 ,biv_measure11
328 ,biv_measure12
329 ,biv_measure13
330 ,biv_measure14
331 ,biv_measure15
332 ,biv_measure17, ' ||
333 l_asset_grp_column ||' BIV_MEASURE20,
334 biv_attribute1 ';
335
336 /* calculate the rank on the sorting column in the inline view */
337 l_inner_query := 'from ( select row_number() over(&ORDER_BY_CLAUSE)-1 rnk,iset.*
338 from (select
339 nvl(oset05.p_requests,0) BIV_MEASURE1
340 , nvl(oset05.c_requests,0) BIV_MEASURE2' || '
341 , ' ||
342 isc_maint_rpt_util_pkg.change_column
343 ( 'oset05.c_requests'
344 , 'oset05.p_requests'
345 , 'BIV_MEASURE3' ) || '
346 , ' ||
347 isc_maint_rpt_util_pkg.rate_column
348 ( 'oset05.p_response_days'
349 , 'oset05.p_requests'
350 , 'BIV_MEASURE4' -- prior response days
351 , 'N' ) || '
352 , ' ||
353 isc_maint_rpt_util_pkg.rate_column
354 ( 'oset05.c_response_days'
355 , 'oset05.c_requests'
356 , 'BIV_MEASURE5' -- current response days
357 , 'N' ) || '
358 , ' ||
359 isc_maint_rpt_util_pkg.change_column
360 ( isc_maint_rpt_util_pkg.rate_column
361 ( 'oset05.c_response_days'
362 , 'oset05.c_requests'
363 , null
364 , 'N' )
365 , isc_maint_rpt_util_pkg.rate_column
366 ( 'oset05.p_response_days'
367 , 'oset05.p_requests'
368 , null
369 , 'N' )
370 , 'BIV_MEASURE6' -- change response days (as float)
371 , 'N' ) || '
372 , ' ||
373 isc_maint_rpt_util_pkg.rate_column
374 ( 'oset05.p_completion_days'
375 , 'oset05.p_requests'
376 , 'BIV_MEASURE7' -- prior completion days
377 , 'N' ) || '
378 , ' ||
379 isc_maint_rpt_util_pkg.rate_column
380 ( 'oset05.c_completion_days'
381 , 'oset05.c_requests'
382 , 'BIV_MEASURE8' -- current completion days
383 , 'N' ) || '
384 , ' ||
385 isc_maint_rpt_util_pkg.change_column
386 ( isc_maint_rpt_util_pkg.rate_column
387 ( 'oset05.c_completion_days'
388 , 'oset05.c_requests'
389 , null
390 , 'N' )
391 , isc_maint_rpt_util_pkg.rate_column
392 ( 'oset05.p_completion_days'
393 , 'oset05.p_requests'
394 , null
395 , 'N' )
396 , 'BIV_MEASURE9' -- change completion days (as float)
397 , 'N' ) || '
398 , nvl(oset05.c_requests_total,0) BIV_MEASURE10' || '
399 , ' ||
400 isc_maint_rpt_util_pkg.change_column
401 ( 'oset05.c_requests_total'
402 , 'oset05.p_requests_total'
403 , 'BIV_MEASURE11' ) || '
404 , ' ||
405 isc_maint_rpt_util_pkg.rate_column
406 ( 'oset05.c_response_days_total'
407 , 'oset05.c_requests_total'
408 , 'BIV_MEASURE12' -- grand total current response days
409 , 'N' ) || '
410 , ' ||
411 isc_maint_rpt_util_pkg.change_column
412 ( isc_maint_rpt_util_pkg.rate_column
413 ( 'oset05.c_response_days_total'
414 , 'oset05.c_requests_total'
415 , null
416 , 'N' )
417 , isc_maint_rpt_util_pkg.rate_column
418 ( 'oset05.p_response_days_total'
419 , 'oset05.p_requests_total'
420 , null
421 , 'N' )
422 , 'BIV_MEASURE13' -- grand total change response days (as float)
423 , 'N' ) || '
424 , ' ||
425 isc_maint_rpt_util_pkg.rate_column
426 ( 'oset05.c_completion_days_total'
427 , 'oset05.c_requests_total'
428 , 'BIV_MEASURE14' -- grand total current completion days
429 , 'N' ) || '
430 , ' ||
431 isc_maint_rpt_util_pkg.change_column
432 ( isc_maint_rpt_util_pkg.rate_column
433 ( 'oset05.c_completion_days_total'
434 , 'oset05.c_requests_total'
435 , null
436 , 'N' )
437 , isc_maint_rpt_util_pkg.rate_column
438 ( 'oset05.p_completion_days_total'
439 , 'oset05.p_requests_total'
440 , null
441 , 'N' )
442 , 'BIV_MEASURE15' -- grand total change completion days (as float)
443 , 'N' ) || '
444 , ' ||
445 isc_maint_rpt_util_pkg.rate_column
446 ( 'oset05.p_completion_days_total'
447 , 'oset05.p_requests_total'
448 , 'BIV_MEASURE17' -- grand total prior completion days
449 , 'N' ) ||
450 case
451 when isc_maint_rpt_util_pkg.get_parameter_value
452 ( p_param
453 , 'VIEW_BY'
454 ) in ( isc_maint_rpt_util_pkg.G_ASSET_GROUP
455 , isc_maint_rpt_util_pkg.G_ASSET_NUMBER ) then
456 '
457 , ''pFunctionName=ISC_MAINT_REQ_CMPL_DTL_RPT_REP'' ||
458 ''&VIEW_BY_NAME=VIEW_BY_ID'' ||
459 ''&pParamIds=Y'' BIV_ATTRIBUTE1, '
460 else
461 '
462 , null BIV_ATTRIBUTE1, '
463 end || isc_maint_rpt_util_pkg.get_inner_select_col(l_join_tbl)||' from ';
464
465 l_stmt := l_stmt || l_inner_query ||
466 poa_dbi_template_pkg.status_sql
467 ( p_fact_name => l_mv
468 , p_where_clause => l_where_clause
469 , p_join_tables => l_join_tbl
470 , p_use_windowing => 'Y'
471 , p_col_name => l_col_tbl
472 , p_use_grpid => 'N'
473 , p_paren_count => 2
474 , p_filter_where => '1=1)iset'
475 , p_generate_viewby => 'Y'
476 );
477
478 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
479 -- l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);
480 x_custom_output := l_custom_output;
481 x_custom_sql := l_stmt;
482
483 end get_tbl_sql;
484
485 procedure get_trd_sql
486 ( p_param in bis_pmv_page_parameter_tbl
487 , x_custom_sql out nocopy varchar2
488 , x_custom_output out nocopy bis_query_attributes_tbl
489 )
490 is
491
492 l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
493 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
494 l_custom_output bis_query_attributes_tbl;
495 l_curr_suffix varchar2(3);
496 l_where_clause varchar2(10000);
497 l_viewby_select varchar2(200);
498 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
499 l_dim_bmap number;
500 l_comparison_type varchar2(200);
501 l_xtd varchar2(200);
502 l_mv varchar2(10000);
503 l_cost_element varchar2(200);
504 l_estimated varchar2(200);
505 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
506 l_stmt varchar2(32767);
507
508 begin
509
510 isc_maint_rpt_util_pkg.register_dimension_levels
511 ( l_dimension_tbl
512 , l_dim_filter_map
513 , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
514 , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
515 , isc_maint_rpt_util_pkg.G_REQUEST_TYPE, 'Y'
516 );
517
518 isc_maint_rpt_util_pkg.process_parameters
519 ( p_param => p_param
520 , p_dimension_tbl => l_dimension_tbl
521 , p_dim_filter_map => l_dim_filter_map
522 , p_trend => 'Y'
523 , p_custom_output => l_custom_output
524 , x_cur_suffix => l_curr_suffix
525 , x_where_clause => l_where_clause
526 , x_viewby_select => l_viewby_select
527 , x_join_tbl => l_join_tbl
528 , x_dim_bmap => l_dim_bmap
529 , x_comparison_type => l_comparison_type
530 , x_xtd => l_xtd
531 );
532
533 l_mv := get_fact_mv_name
534 ( 'REQUEST_TO_COMPLETION'
535 , p_param
536 , l_dim_bmap
537 , l_custom_output
538 );
539
540 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
541
542 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
543 , p_col_name => 'total_requests'
544 , p_alias_name => 'requests'
545 , p_to_date_type => 'XTD'
546 , p_grand_total => 'N'
547 );
548
549 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
550 , p_col_name => 'total_response_days'
551 , p_alias_name => 'response_days'
552 , p_to_date_type => 'XTD'
553 , p_grand_total => 'N'
554 );
555
556 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
557 , p_col_name => 'total_completion_days'
558 , p_alias_name => 'completion_days'
559 , p_to_date_type => 'XTD'
560 , p_grand_total => 'N'
561 );
562
563 l_stmt := 'select
564 cal.name VIEWBY
565 , nvl(iset.p_requests,0) BIV_MEASURE1
566 , nvl(iset.c_requests,0) BIV_MEASURE2' || '
567 , ' ||
568 isc_maint_rpt_util_pkg.change_column
569 ( 'iset.c_requests'
570 , 'iset.p_requests'
571 , 'BIV_MEASURE3' ) || '
572 , ' ||
573 isc_maint_rpt_util_pkg.rate_column
574 ( 'iset.p_response_days'
575 , 'iset.p_requests'
576 , 'BIV_MEASURE4' -- prior response days
577 , 'N' ) || '
578 , ' ||
579 isc_maint_rpt_util_pkg.rate_column
580 ( 'iset.c_response_days'
581 , 'iset.c_requests'
582 , 'BIV_MEASURE5' -- current response days
583 , 'N' ) || '
584 , ' ||
585 isc_maint_rpt_util_pkg.change_column
586 ( isc_maint_rpt_util_pkg.rate_column
587 ( 'iset.c_response_days'
588 , 'iset.c_requests'
589 , null
590 , 'N' )
591 , isc_maint_rpt_util_pkg.rate_column
592 ( 'iset.p_response_days'
593 , 'iset.p_requests'
594 , null
595 , 'N' )
596 , 'BIV_MEASURE6' -- change response days (as float)
597 , 'N' ) || '
598 , ' ||
599 isc_maint_rpt_util_pkg.rate_column
600 ( 'iset.p_completion_days'
601 , 'iset.p_requests'
602 , 'BIV_MEASURE7' -- prior completion days
603 , 'N' ) || '
604 , ' ||
605 isc_maint_rpt_util_pkg.rate_column
606 ( 'iset.c_completion_days'
607 , 'iset.c_requests'
608 , 'BIV_MEASURE8' -- current completion days
609 , 'N' ) || '
610 , ' ||
611 isc_maint_rpt_util_pkg.change_column
612 ( isc_maint_rpt_util_pkg.rate_column
613 ( 'iset.c_completion_days'
614 , 'iset.c_requests'
615 , null
616 , 'N' )
617 , isc_maint_rpt_util_pkg.rate_column
618 ( 'iset.p_completion_days'
619 , 'iset.p_requests'
620 , null
621 , 'N' )
622 , 'BIV_MEASURE9' -- change completion days (as float)
623 , 'N' ) || '
624 from
625 ' || poa_dbi_template_pkg.trend_sql
626 ( p_xtd => l_xtd
627 , p_comparison_type => l_comparison_type
628 , p_fact_name => l_mv
629 , p_where_clause => l_where_clause
630 , p_col_name => l_col_tbl
631 , p_use_grpid => 'N'
632 );
633
634 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
635 -- l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);
636
637 x_custom_sql := l_stmt;
638
639 x_custom_output := l_custom_output;
640
641 poa_dbi_util_pkg.get_custom_trend_binds
642 ( x_custom_output => l_custom_output
643 , p_xtd => l_xtd
644 , p_comparison_type => l_comparison_type
645 );
646
647 if l_custom_output is not null then
648 for i in 1..l_custom_output.count loop
649 x_custom_output.extend;
650 x_custom_output(x_custom_output.count) := l_custom_output(i);
651 end loop;
652 end if;
653
654 end get_trd_sql;
655
656 procedure get_dbn_tbl_sql
657 ( p_param in bis_pmv_page_parameter_tbl
658 , x_custom_sql out nocopy varchar2
659 , x_custom_output out nocopy bis_query_attributes_tbl
660 )
661 as
662
663 l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
664 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
665 l_custom_output bis_query_attributes_tbl;
666 l_curr_suffix varchar2(3);
667 l_where_clause varchar2(10000);
668 l_viewby_select varchar2(200);
669 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
670 l_dim_bmap number;
671 l_comparison_type varchar2(200);
672 l_xtd varchar2(200);
673 l_mv varchar2(10000);
674 l_cost_element varchar2(200);
675 l_estimated varchar2(200);
676 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
677 l_stmt varchar2(32767);
678
679 begin
680
681 isc_maint_rpt_util_pkg.register_dimension_levels
682 ( l_dimension_tbl
683 , l_dim_filter_map
684 , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
685 , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
686 , isc_maint_rpt_util_pkg.G_REQUEST_TYPE, 'Y'
687 , isc_maint_rpt_util_pkg.G_REQ_CMPL_AGING, 'Y'
688 );
689
690 isc_maint_rpt_util_pkg.process_parameters
691 ( p_param => p_param
692 , p_dimension_tbl => l_dimension_tbl
693 , p_dim_filter_map => l_dim_filter_map
694 , p_trend => 'N'
695 , p_custom_output => l_custom_output
696 , x_cur_suffix => l_curr_suffix
697 , x_where_clause => l_where_clause
698 , x_viewby_select => l_viewby_select
699 , x_join_tbl => l_join_tbl
700 , x_dim_bmap => l_dim_bmap
701 , x_comparison_type => l_comparison_type
702 , x_xtd => l_xtd
703 );
704
705 l_mv := get_fact_mv_name
706 ( 'REQUEST_TO_COMPLETION'
707 , p_param
708 , l_dim_bmap
709 , l_custom_output
710 );
711
712 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
713
714 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
715 , p_col_name => 'total_requests'
716 , p_alias_name => 'requests'
717 , p_to_date_type => 'XTD'
718 , p_grand_total => 'Y'
719 );
720
721 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
722 , p_col_name => 'total_completion_days'
723 , p_alias_name => 'completion_days'
724 , p_to_date_type => 'XTD'
725 , p_grand_total => 'Y'
726 );
727
728
729 l_stmt := 'select
730 ' || l_viewby_select || '
731 , nvl(oset.p_requests,0) BIV_MEASURE1 /* prior requests */
732 , nvl(oset.c_requests,0) BIV_MEASURE2 /* current requests */' || '
733 , ' ||
734 isc_maint_rpt_util_pkg.change_column
735 ( 'oset.c_requests'
736 , 'oset.p_requests'
737 , 'BIV_MEASURE3' ) || ' /* change requests */
738 , ' ||
739 isc_maint_rpt_util_pkg.rate_column
740 ( 'oset.c_requests'
741 , 'avg(oset.c_requests_total) over()'
742 , 'BIV_MEASURE4'
743 , 'Y' ) || ' /* percent of total */
744 , nvl(avg(oset.c_requests_total) over(),0) BIV_MEASURE5 /* grand total current requests */' || '
745 , ' ||
746 isc_maint_rpt_util_pkg.change_column
747 ( 'avg(oset.c_requests_total) over()'
748 , 'avg(oset.p_requests_total) over()'
749 , 'BIV_MEASURE6' ) || ' /* grand total change requests */
750 , ' ||
751 isc_maint_rpt_util_pkg.change_column
752 ( 'avg(oset.c_requests_total) over()'
753 , 'avg(oset.c_requests_total) over()'
754 , 'BIV_MEASURE7' ) || ' /* grand total current percent of total */
755 , ''pFunctionName=ISC_MAINT_REQ_CMPL_DTL_RPT_REP'' ||
756 ''&VIEW_BY_NAME=VIEW_BY_ID'' ||
757 ''&pParamIds=Y'' BIV_ATTRIBUTE1
758 , null BIV_ATTRIBUTE2' -- this is needed for bucket to region association
759 || '
760 from
761 ( select * from ( ' || poa_dbi_template_pkg.status_sql
762 ( p_fact_name => l_mv
763 , p_where_clause => l_where_clause
764 , p_join_tables => l_join_tbl
765 , p_use_windowing => 'N'
766 , p_col_name => l_col_tbl
767 , p_use_grpid => 'N'
768 , p_paren_count => 3
769 , p_filter_where => null
770 , p_generate_viewby => 'Y'
771 );
772
773 l_stmt := replace( l_stmt
774 , '&ORDER_BY_CLAUSE'
775 , 'ORDER BY VIEWBYID'
776 );
777
778 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
779 -- l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);
780
781 x_custom_sql := l_stmt;
782
783 x_custom_output := l_custom_output;
784
785 end get_dbn_tbl_sql;
786
787 procedure get_dtl_rpt_sql
788 ( p_param in bis_pmv_page_parameter_tbl
789 , x_custom_sql out nocopy varchar2
790 , x_custom_output out nocopy bis_query_attributes_tbl
791 )
792 as
793
794 l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
795 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
796 l_custom_output bis_query_attributes_tbl;
797 l_curr_suffix varchar2(3);
798 l_where_clause varchar2(10000);
799 l_viewby_select varchar2(200);
800 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
801 l_dim_bmap number;
802 l_comparison_type varchar2(200);
803 l_xtd varchar2(200);
804 l_mv varchar2(10000);
805 l_cost_element varchar2(200);
806 l_estimated varchar2(200);
807 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
808 l_stmt varchar2(32767);
809 l_rank_order varchar2(200);
810 l_detail_col_tbl isc_maint_rpt_util_pkg.t_detail_column_tbl;
811 l_order_by varchar2(200);
812 l_asc_desc varchar2(100);
813
814 begin
815
816 isc_maint_rpt_util_pkg.register_dimension_levels
817 ( l_dimension_tbl
818 , l_dim_filter_map
819 , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
820 , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
824 , isc_maint_rpt_util_pkg.G_REQUEST_SEVERITIES, 'N'
821 , isc_maint_rpt_util_pkg.G_REQUEST_TYPE, 'Y'
822 , isc_maint_rpt_util_pkg.G_REQ_CMPL_AGING, 'Y'
823 , isc_maint_rpt_util_pkg.G_REQUESTS, 'N'
825 );
826
827 isc_maint_rpt_util_pkg.process_parameters
828 ( p_param => p_param
829 , p_dimension_tbl => l_dimension_tbl
830 , p_dim_filter_map => l_dim_filter_map
831 , p_trend => 'D'
832 , p_custom_output => l_custom_output
833 , x_cur_suffix => l_curr_suffix
834 , x_where_clause => l_where_clause
835 , x_viewby_select => l_viewby_select
836 , x_join_tbl => l_join_tbl
837 , x_dim_bmap => l_dim_bmap
838 , x_comparison_type => l_comparison_type
839 , x_xtd => l_xtd
840 );
841
842 l_mv := get_fact_mv_name
843 ( 'REQUEST_TO_COMPLETION_DTL'
844 , p_param
845 , l_dim_bmap
846 , l_custom_output
847 );
848
849 l_order_by := isc_maint_rpt_util_pkg.get_parameter_value
850 ( p_param
851 , 'ORDERBY'
852 );
853
854 if l_order_by like '% DESC%' then
855 l_asc_desc := ' desc ';
856 else
857 l_asc_desc := ' asc ';
858 end if;
859
860 l_rank_order := 'order by ' ||
861 case
862 when l_order_by like '%BIV_MEASURE1%' then
863 'request_start_date'
864 when l_order_by like '%BIV_MEASURE2%' then
865 'response_days'
866 else -- l_order_by like '%BIV_MEASURE3%' then
867 'completion_days'
868 end ||
869 l_asc_desc ||
870 'nulls last, organization_id, request_type, maint_request_id';
871
872 isc_maint_rpt_util_pkg.add_detail_column
873 ( p_detail_col_tbl => l_detail_col_tbl
874 , p_dimension_tbl => l_dimension_tbl
875 , p_fact_col_name => 'request_number'
876 , p_fact_col_total => 'N'
877 , p_column_key => 'request_number'
878 );
879
880 isc_maint_rpt_util_pkg.add_detail_column
881 ( p_detail_col_tbl => l_detail_col_tbl
882 , p_dimension_tbl => l_dimension_tbl
883 , p_dimension_level => isc_maint_rpt_util_pkg.G_REQUESTS
884 , p_dim_level_col_name => 'description'
885 , p_column_key => 'description'
886 );
887
888 isc_maint_rpt_util_pkg.add_detail_column
889 ( p_detail_col_tbl => l_detail_col_tbl
890 , p_dimension_tbl => l_dimension_tbl
891 , p_dimension_level => isc_maint_rpt_util_pkg.G_REQUEST_TYPE
892 , p_column_key => 'request_type'
893 );
894
895 isc_maint_rpt_util_pkg.add_detail_column
896 ( p_detail_col_tbl => l_detail_col_tbl
897 , p_dimension_tbl => l_dimension_tbl
898 , p_fact_col_name => 'work_order_count'
899 , p_fact_col_total => 'N'
900 , p_column_key => 'work_order_count'
901 );
902
903 isc_maint_rpt_util_pkg.add_detail_column
904 ( p_detail_col_tbl => l_detail_col_tbl
905 , p_dimension_tbl => l_dimension_tbl
906 , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_NUMBER
907 , p_column_key => 'asset_number'
908 );
909
910 isc_maint_rpt_util_pkg.add_detail_column
911 ( p_detail_col_tbl => l_detail_col_tbl
912 , p_dimension_tbl => l_dimension_tbl
913 , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_GROUP
914 , p_column_key => 'asset_group'
915 );
916
917 isc_maint_rpt_util_pkg.add_detail_column
918 ( p_detail_col_tbl => l_detail_col_tbl
919 , p_dimension_tbl => l_dimension_tbl
920 , p_dimension_level => isc_maint_rpt_util_pkg.G_DEPARTMENT
921 , p_column_key => 'department'
922 );
923
924 isc_maint_rpt_util_pkg.add_detail_column
925 ( p_detail_col_tbl => l_detail_col_tbl
926 , p_dimension_tbl => l_dimension_tbl
927 , p_dimension_level => isc_maint_rpt_util_pkg.G_REQUEST_SEVERITIES
928 , p_dim_level_col_name => 'name'
929 , p_column_key => 'request_severity'
930 );
931
932 isc_maint_rpt_util_pkg.add_detail_column
933 ( p_detail_col_tbl => l_detail_col_tbl
934 , p_dimension_tbl => l_dimension_tbl
935 , p_fact_col_name => 'request_start_date'
936 , p_fact_col_total => 'N'
937 , p_column_key => 'request_start_date'
938 );
939
940
941 isc_maint_rpt_util_pkg.add_detail_column
942 ( p_detail_col_tbl => l_detail_col_tbl
943 , p_dimension_tbl => l_dimension_tbl
944 , p_fact_col_name => 'response_days'
945 , p_fact_col_total => 'N'
946 , p_column_key => 'response_days'
947 );
948
949 isc_maint_rpt_util_pkg.add_detail_column
950 ( p_detail_col_tbl => l_detail_col_tbl
951 , p_dimension_tbl => l_dimension_tbl
952 , p_fact_col_name => 'completion_days'
953 , p_fact_col_total => 'N'
954 , p_column_key => 'completion_days'
955 );
956
957 l_stmt := 'select
958 oset.request_number BIV_ATTRIBUTE1
959 , ' || isc_maint_rpt_util_pkg.get_detail_column
960 (l_detail_col_tbl,'description','BIV_ATTRIBUTE2') || '
961 , ' || isc_maint_rpt_util_pkg.get_detail_column
962 (l_detail_col_tbl,'request_type','BIV_ATTRIBUTE3') || '
963 , oset.work_order_count BIV_ATTRIBUTE4' || '
964 , ' || isc_maint_rpt_util_pkg.get_detail_column
965 (l_detail_col_tbl,'asset_number','BIV_ATTRIBUTE5') || '
966 , ' || isc_maint_rpt_util_pkg.get_detail_column
967 (l_detail_col_tbl,'asset_group','BIV_ATTRIBUTE6') || '
971 (l_detail_col_tbl,'request_severity','BIV_ATTRIBUTE8') || '
968 , ' || isc_maint_rpt_util_pkg.get_detail_column
969 (l_detail_col_tbl,'department','BIV_ATTRIBUTE7') || '
970 , ' || isc_maint_rpt_util_pkg.get_detail_column
972 , oset.request_start_date BIV_MEASURE1
973 , oset.response_days BIV_MEASURE2
974 , oset.completion_days BIV_MEASURE3
975 , ''pFunctionName=ISC_MAINT_REQ_WO_DTL_RPT_REP'' || ' ||
976 '''&pParamIds=Y'' || ' ||
977 '''&ORGANIZAT_D1='' || oset.organization_id ||' ||
978 '''&ENI_RESOURCE_DEPARTMENT='' || decode(oset.department_id,-1,''-1'',oset.department_id||''-1'') ||' ||
979 '''&BIV_MAINT_ASSET_GROUP_LVL='' || decode(oset.asset_group_id,-1,''-1'',oset.asset_group_id) ||' ||
980 '''&BIV_MAINT_ASSET_NUMBER_LVL='' || decode(oset.instance_id,-1,-1,oset.instance_id) ||' ||
981 '''&BIV_MAINT_REQUEST_TYPE_LVL='' || oset.request_type ||' ||
982 '''&BIV_ATTRIBUTE9='' || oset.maint_request_id || ' ||
983 '''&BIV_ATTRIBUTE6='' || oset.request_number || ' ||
984 '''&BIV_ATTRIBUTE8='' || to_char(oset.request_start_date,fnd_profile.value(''ICX_DATE_FORMAT_MASK'')) || ' ||
985 '''&BIV_ATTRIBUTE7='' || ' || isc_maint_rpt_util_pkg.get_detail_column
986 (l_detail_col_tbl,'request_severity',null) || ' || ' ||
987 ''''' BIV_ATTRIBUTE9
988 , null BIV_ATTRIBUTE10' -- this is needed for bucket to AK region association
989 || '
990 from
991 ' || isc_maint_rpt_util_pkg.detail_sql
992 ( p_detail_col_tbl => l_detail_col_tbl
993 , p_dimension_tbl => l_dimension_tbl
994 , p_mv_name => l_mv
995 , p_where_clause => l_where_clause
996 , p_rank_order => l_rank_order
997 );
998
999 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
1000 --l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);
1001
1002 x_custom_sql := l_stmt;
1003
1004 x_custom_output := l_custom_output;
1005
1006 end get_dtl_rpt_sql;
1007
1008 procedure get_wo_dtl_rpt_sql
1009 ( p_param in bis_pmv_page_parameter_tbl
1010 , x_custom_sql out nocopy varchar2
1011 , x_custom_output out nocopy bis_query_attributes_tbl
1012 )
1013 as
1014
1015 l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
1016 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
1017 l_custom_output bis_query_attributes_tbl;
1018 l_curr_suffix varchar2(3);
1019 l_where_clause varchar2(10000);
1020 l_viewby_select varchar2(200);
1021 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
1022 l_dim_bmap number;
1023 l_comparison_type varchar2(200);
1024 l_xtd varchar2(200);
1025 l_mv varchar2(10000);
1026 l_cost_element varchar2(200);
1027 l_estimated varchar2(200);
1028 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
1029 l_stmt varchar2(32767);
1030 l_rank_order varchar2(200);
1031 l_detail_col_tbl isc_maint_rpt_util_pkg.t_detail_column_tbl;
1032
1033 begin
1034
1035 isc_maint_rpt_util_pkg.register_dimension_levels
1036 ( l_dimension_tbl
1037 , l_dim_filter_map
1038 , isc_maint_rpt_util_pkg.G_ORGANIZATION, 'Y'
1039 , isc_maint_rpt_util_pkg.G_DEPARTMENT, 'N'
1040 , isc_maint_rpt_util_pkg.G_REQUEST_TYPE, 'Y'
1041 , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'N'
1042 , isc_maint_rpt_util_pkg.G_ACTIVITY, 'N'
1043 , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'N'
1044 );
1045
1046 isc_maint_rpt_util_pkg.process_parameters
1047 ( p_param => p_param
1048 , p_dimension_tbl => l_dimension_tbl
1049 , p_dim_filter_map => l_dim_filter_map
1050 , p_trend => 'D'
1051 , p_custom_output => l_custom_output
1052 , x_cur_suffix => l_curr_suffix
1053 , x_where_clause => l_where_clause
1054 , x_viewby_select => l_viewby_select
1055 , x_join_tbl => l_join_tbl
1056 , x_dim_bmap => l_dim_bmap
1057 , x_comparison_type => l_comparison_type
1058 , x_xtd => l_xtd
1059 );
1060
1061 l_mv := get_fact_mv_name
1062 ( 'REQUESTED_WORK_ORDER'
1063 , p_param
1064 , l_dim_bmap
1065 , l_custom_output
1066 );
1067
1068 isc_maint_rpt_util_pkg.add_detail_column
1069 ( p_detail_col_tbl => l_detail_col_tbl
1070 , p_dimension_tbl => l_dimension_tbl
1071 , p_fact_col_name => 'work_order_id'
1072 , p_fact_col_total => 'N'
1073 , p_column_key => 'work_order_id'
1074 );
1075
1076 isc_maint_rpt_util_pkg.add_detail_column
1077 ( p_detail_col_tbl => l_detail_col_tbl
1078 , p_dimension_tbl => l_dimension_tbl
1079 , p_fact_col_name => 'work_order_name'
1080 , p_fact_col_total => 'N'
1081 , p_column_key => 'work_order_name'
1082 );
1083
1084 isc_maint_rpt_util_pkg.add_detail_column
1085 ( p_detail_col_tbl => l_detail_col_tbl
1086 , p_dimension_tbl => l_dimension_tbl
1087 , p_dimension_level => isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE
1088 , p_column_key => 'work_order_type'
1089 );
1090
1091 isc_maint_rpt_util_pkg.add_detail_column
1092 ( p_detail_col_tbl => l_detail_col_tbl
1093 , p_dimension_tbl => l_dimension_tbl
1094 , p_dimension_level => isc_maint_rpt_util_pkg.G_ACTIVITY
1095 , p_column_key => 'activity'
1096 );
1097
1098 isc_maint_rpt_util_pkg.add_detail_column
1099 ( p_detail_col_tbl => l_detail_col_tbl
1100 , p_dimension_tbl => l_dimension_tbl
1101 , p_dimension_level => isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS
1102 , p_column_key => 'work_order_status'
1103 );
1104
1105 isc_maint_rpt_util_pkg.add_detail_column
1106 ( p_detail_col_tbl => l_detail_col_tbl
1110 , p_column_key => 'scheduled_start_date'
1107 , p_dimension_tbl => l_dimension_tbl
1108 , p_fact_col_name => 'scheduled_start_date'
1109 , p_fact_col_total => 'N'
1111 );
1112
1113 isc_maint_rpt_util_pkg.add_detail_column
1114 ( p_detail_col_tbl => l_detail_col_tbl
1115 , p_dimension_tbl => l_dimension_tbl
1116 , p_fact_col_name => 'scheduled_completion_date'
1117 , p_fact_col_total => 'N'
1118 , p_column_key => 'scheduled_completion_date'
1119 );
1120
1121 isc_maint_rpt_util_pkg.add_detail_column
1122 ( p_detail_col_tbl => l_detail_col_tbl
1123 , p_dimension_tbl => l_dimension_tbl
1124 , p_fact_col_name => 'completion_date'
1125 , p_fact_col_total => 'N'
1126 , p_column_key => 'completion_date'
1127 );
1128
1129 l_stmt := 'select
1130 oset.work_order_name BIV_ATTRIBUTE1
1131 , ' || isc_maint_rpt_util_pkg.get_detail_column
1132 (l_detail_col_tbl,'work_order_type','BIV_ATTRIBUTE2') || '
1133 , ' || isc_maint_rpt_util_pkg.get_detail_column
1134 (l_detail_col_tbl,'activity','BIV_ATTRIBUTE3') || '
1135 , ' || isc_maint_rpt_util_pkg.get_detail_column
1136 (l_detail_col_tbl,'work_order_status','BIV_ATTRIBUTE4') || '
1137 , oset.scheduled_start_date BIV_MEASURE1
1138 , oset.scheduled_completion_date BIV_MEASURE2
1139 , oset.completion_date BIV_MEASURE3
1140 , ' ||
1141 isc_maint_rpt_util_pkg.get_drill_detail('BIV_ATTRIBUTE5') || '
1142 from
1143 ' || isc_maint_rpt_util_pkg.detail_sql
1144 ( p_detail_col_tbl => l_detail_col_tbl
1145 , p_dimension_tbl => l_dimension_tbl
1146 , p_mv_name => l_mv
1147 , p_where_clause => l_where_clause
1148 , p_rank_order => null
1149 );
1150
1151 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
1152 -- l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);
1153
1154 x_custom_sql := l_stmt;
1155
1156 bind_request_id( l_custom_output, p_param );
1157
1158 x_custom_output := l_custom_output;
1159
1160 end get_wo_dtl_rpt_sql;
1161
1162 end isc_maint_req_cmpl_rpt_pkg;