[Home] [Help]
PACKAGE BODY: APPS.ISC_MAINT_ASSET_DT_RPT_PKG
Source
1 PACKAGE BODY ISC_MAINT_ASSET_DT_RPT_PKG as
2 /*$Header: iscmaintadtrptb.pls 120.1 2005/09/13 05:36:55 nbhamidi noship $ */
3
4
5 function get_fact_mv_name
6 ( p_report_type in varchar2
7 , p_param in bis_pmv_page_parameter_tbl
8 , p_dim_bmap in number
9 , p_custom_output in out nocopy bis_query_attributes_tbl
10 )
11 return varchar2
12 is
13
14 l_org_id varchar2(200);
15 l_view_by varchar2(200);
16 tmp1 varchar2(2000);
17
18 begin
19 l_org_id := isc_maint_rpt_util_pkg.get_parameter_id
20 ( p_param, isc_maint_rpt_util_pkg.G_ORGANIZATION);
21
22 if p_report_type = 'ASSET_DOWNTIME_REPORT' then
23 l_view_by := isc_maint_rpt_util_pkg.get_parameter_value
24 ( p_param , 'VIEW_BY');
25
26 isc_maint_rpt_util_pkg.bind_group_id
27 ( p_dim_bmap
28 , p_custom_output
29 , isc_maint_rpt_util_pkg.G_ASSET_GROUP
30 , isc_maint_rpt_util_pkg.G_ASSET_NUMBER
31 -- , isc_maint_rpt_util_pkg.G_ASSET_CRITICALITY
32 , isc_maint_rpt_util_pkg.G_DEPARTMENT
33 , isc_maint_rpt_util_pkg.G_ASSET_CATEGORY
34 );
35
36
37 return '(select
38 time_id
39 , period_type_id
40 , grp_id
41 , ' ||
42 case l_view_by /* replaced asset_number with instance_id */
43 when isc_maint_rpt_util_pkg.G_DEPARTMENT then 'decode(department_id,-1,-1,organization_id) organization_id'
44 when isc_maint_rpt_util_pkg.G_ASSET_GROUP then 'decode(asset_group_id,-1,-1,organization_id) organization_id'
45 when isc_maint_rpt_util_pkg.G_ASSET_NUMBER then 'decode(instance_id,-1,-1,organization_id) organization_id'
46 when isc_maint_rpt_util_pkg.G_ASSET_CATEGORY then 'decode(category_id,-1,-1,organization_id) organization_id'
47 else 'organization_id'
48 end ||'
49 ,asset_group_id
50 ,instance_id /* replaced asset_number with instance_id */
51 ,organization_id organization_id_c
52 ,category_id
53 ,to_char(department_id) department_id
54 ,asset_group_id asset_group_id_c /* removed concatenation to org. to make asset group independent of org. */
55 ,decode(instance_id,-1,-1,instance_id) instance_id_c /* replaced asset_number with instance_id */
56 ,decode(department_id,-1,''-1'',department_id||''-1'') department_id_c
57 ,asset_criticality_code
58 ,dt_non_overlap_hrs
59 from isc_maint_001_mv fact' || '
60 where fact.grp_id = &ISC_GRP_ID ' ||
61 case
62 when l_org_id is null then
63 '
64 and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'fact', l_org_id )
65 end || '
66 )';
67
68 else
69 return '( select
70 fact.organization_id organization_id
71 ,fact.asset_group_id asset_group_id
72 ,fact.instance_id instance_id /* replace asset_number with instance_id */
73 ,fact.category_id category_id
74 ,fact.asset_criticality_code asset_criticality_code
75 ,fact.organization_id organization_id_c
76 ,to_char(fact.department_id) department_id
77 ,fact.asset_group_id asset_group_id_c /* removed concatenation to org. to make it independent of org. */
78 ,decode(fact.instance_id,-1,-1,fact.instance_id)
79 instance_id_c /* replaced asset_number with instance_id */
80 ,fact.department_id||''-1'' department_id_c
81 ,fact.start_date start_date
82 ,fact.end_date end_date
83 ,24*(fact.end_date - fact.start_date)
84 dt_overlap_hrs
85 ,w.wip_entity_name work_order_name
86 ,fact.work_order_id work_order_id
87 ,fact.description description
88 ,fact.operation_seq_number operation_seq_number
89 from
90 isc_maint_asset_down_f fact
91 ,wip_entities w
92 where
93 w.wip_entity_id(+) = fact.WORK_ORDER_ID and
94 fact.start_date < &BIS_CURRENT_ASOF_DATE+1 and
95 fact.end_date >= &BIS_CURRENT_EFFECTIVE_START_DATE ' ||
96 case
97 when l_org_id is null then
98 'and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'fact', l_org_id )
99 end || '
100 ) ';
101
102 end if ;
103
104 end get_fact_mv_name;
105
106
107 procedure get_tbl_sql
108 (
109 p_param in bis_pmv_page_parameter_tbl
110 , x_custom_sql out nocopy varchar2
111 , x_custom_output out nocopy bis_query_attributes_tbl
112 )
113 is
114
115 l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
116 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
117 l_dim_bmap number;
118 l_comparison_type varchar2(200);
119 l_xtd varchar2(200);
120 l_where_clause varchar2(10000);
121 l_mv varchar2(10000);
122 l_stmt varchar2(32767);
123 l_drill_down varchar2(1000);
124 l_drill_across varchar2 (1000);
125 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
126 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
127 l_custom_output bis_query_attributes_tbl;
128 l_cost_element varchar2(200);
129 l_curr_suffix varchar2(2);
130 l_viewby_select varchar2(200);
131 l_asset_grp_column varchar2(200);
132 l_criticality_column varchar2(1000);
133 l_inner_query varchar2(5000);
134
135 BEGIN
136
137 -- clear out the tables.
138 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
139 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
140
141 ---- view by dimensions that can be possible except org and dept ie register them -----
142
143 isc_maint_rpt_util_pkg.register_dimension_levels
144 ( l_dimension_tbl
145 , l_dim_filter_map
146 , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
147 , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
148 , isc_maint_rpt_util_pkg.G_ASSET_CATEGORY, 'Y'
149 , isc_maint_rpt_util_pkg.G_ASSET_CRITICALITY, 'Y'
150 );
151
152 isc_maint_rpt_util_pkg.process_parameters
153 ( p_param => p_param
157 , p_custom_output => l_custom_output
154 , p_dimension_tbl => l_dimension_tbl
155 , p_dim_filter_map => l_dim_filter_map
156 , p_trend => 'N'
158 , x_cur_suffix => l_curr_suffix
159 , x_where_clause => l_where_clause
160 , x_viewby_select => l_viewby_select
161 , x_join_tbl => l_join_tbl
162 , x_dim_bmap => l_dim_bmap
163 , x_comparison_type => l_comparison_type
164 , x_xtd => l_xtd
165 );
166
167
168
169 l_mv := get_fact_mv_name
170 ( 'ASSET_DOWNTIME_REPORT'
171 , p_param
175
172 , l_dim_bmap
173 , l_custom_output
174 );
176
177 poa_dbi_util_pkg.add_column
178 (l_col_tbl,'dt_non_overlap_hrs','dt_non_overlap_hrs');
179
180
181 /* check for view if asset_number add criticality and asset_group and drill across link
182 if asset_group then add drill down link only
183 else nullify all */
184
185 if(isc_maint_rpt_util_pkg.get_parameter_id(p_param,'VIEW_BY')) = isc_maint_rpt_util_pkg.G_ASSET_NUMBER
186 then
187 l_asset_grp_column := isc_maint_rpt_util_pkg.add_asset_group_column(isc_maint_rpt_util_pkg.G_ASSET_NUMBER,l_dimension_tbl);
188 l_criticality_column := isc_maint_rpt_util_pkg.add_view_by(isc_maint_rpt_util_pkg.G_ASSET_CRITICALITY, l_dimension_tbl,l_join_tbl);
189 l_drill_across := ' ''pFunctionName=ISC_MAINT_ASSET_DT_RPT_REP'' ||
190 ''&VIEW_BY_NAME=VIEW_BY_ID'' ||''&pParamIds=Y'' ' ;
191 l_drill_down := 'NULL';
192 else
193 if(isc_maint_rpt_util_pkg.get_parameter_id(p_param,'VIEW_BY')=isc_maint_rpt_util_pkg.G_ASSET_GROUP)
194 then
195 l_asset_grp_column :='NULL';
196 l_criticality_column := 'NULL';
197 l_drill_across := 'NULL';
198 l_drill_down := '''pFunctionName=ISC_MAINT_ASSET_DT_TBL_REP'' ||
199 ''&VIEW_BY_NAME=VIEW_BY_ID'' ||
200 ''&VIEW_BY=BIV_MAINT_ASSET+BIV_MAINT_ASSET_NUMBER_LVL'' ||
201 ''&pParamIds=Y'' ';
202 else
203 l_asset_grp_column :='NULL';
204 l_criticality_column := 'NULL';
205 l_drill_across := 'NULL';
206 l_drill_down := 'NULL';
207 end if;
208
209 end if ;
210
211
212 /* to enable windowing we are using an addition inline view */
213 l_stmt := ' select ' || l_viewby_select ||
214 ', biv_measure1
215 , biv_measure2
216 , biv_measure3
217 , biv_measure13
218 , biv_measure14
219 , biv_measure15 , ' ||
220 l_asset_grp_column || ' BIV_MEASURE16 ,' ||
221 l_criticality_column || ' BIV_MEASURE20 , ' ||
222 l_drill_across || ' BIV_Attribute1 , '||
223 l_drill_down || ' BIV_Attribute2 ' ;
224
225
226
227 /* calculate the rank on the sorting column in the inline view */
228 l_inner_query := 'from ( select row_number() over(&ORDER_BY_CLAUSE nulls last )-1 rnk,iset.*'||
229 ' from ( select nvl(oset05.p_dt_non_overlap_hrs,0) BIV_MEASURE1
230 ,nvl(oset05.c_dt_non_overlap_hrs,0) BIV_MEASURE2, ' ||
231 isc_maint_rpt_util_pkg.change_column('nvl(oset05.c_dt_non_overlap_hrs,0)',
232 'nvl(oset05.p_dt_non_overlap_hrs,0)','BIV_MEASURE3','X') || '
233 ,nvl(oset05.c_dt_non_overlap_hrs_total,0) BIV_MEASURE13, ' ||
234 isc_maint_rpt_util_pkg.change_column('nvl(oset05.c_dt_non_overlap_hrs_total,0)',
235 'nvl(oset05.p_dt_non_overlap_hrs_total,0)','BIV_MEASURE14','X') || '
236 ,nvl(oset05.p_dt_non_overlap_hrs_total,0) BIV_MEASURE15,'||
237 isc_maint_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ' from ';
238
239
240
241 l_stmt := l_stmt || l_inner_query;
242 l_stmt := l_stmt || poa_dbi_template_pkg.status_sql
243 ( p_fact_name => l_mv
244 , p_where_clause => l_where_clause
245 , p_join_tables => l_join_tbl
246 , p_use_windowing => 'Y'
247 , p_col_name => l_col_tbl
248 , p_use_grpid => 'N'
249 , p_paren_count => 2
250 , p_filter_where => '1=1)iset'
251 , p_generate_viewby => 'Y'
252 );
253
254 x_custom_output := l_custom_output;
255 x_custom_sql := l_stmt;
256
257
258 end get_tbl_sql;
259 -----start of detailed report ------------------------------------------
260
261 procedure get_asset_dt_dtl_sql
262 (
263 p_param in bis_pmv_page_parameter_tbl
264 , x_custom_sql out nocopy varchar2
265 , x_custom_output out nocopy bis_query_attributes_tbl
266 )
267 is
268 l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
269 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
270 l_dim_bmap number;
271 l_comparison_type varchar2(200);
272 l_xtd varchar2(200);
273 l_where_clause varchar2(10000);
274 l_mv varchar2(10000);
275 l_stmt varchar2(32767);
276 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
277 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
278 l_order_by varchar2(200);
279 l_asc_desc varchar2(100);
280 l_custom_output bis_query_attributes_tbl;
281 l_rank_order varchar2(100);
282 l_cost_element varchar2(200);
283 l_curr_suffix varchar2(2);
284 l_detail_col_tbl isc_maint_rpt_util_pkg.t_detail_column_tbl;
285 l_viewby_select varchar2(200);
286 BEGIN
287
288 -- clear out the tables.
289 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
290 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
291
292 isc_maint_rpt_util_pkg.register_dimension_levels
293 ( l_dimension_tbl
294 , l_dim_filter_map
295 , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
296 , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
297 , isc_maint_rpt_util_pkg.G_ASSET_CATEGORY, 'Y'
298 , isc_maint_rpt_util_pkg.G_ASSET_CRITICALITY, 'Y'
299 );
300
301 isc_maint_rpt_util_pkg.process_parameters
302 ( p_param => p_param
303 , p_dimension_tbl => l_dimension_tbl
304 , p_dim_filter_map => l_dim_filter_map
305 , p_trend => 'D'
306 , p_custom_output => l_custom_output
307 , x_cur_suffix => l_curr_suffix
308 , x_where_clause => l_where_clause
309 , x_viewby_select => l_viewby_select
310 , x_join_tbl => l_join_tbl
314 );
311 , x_dim_bmap => l_dim_bmap
312 , x_comparison_type => l_comparison_type
313 , x_xtd => l_xtd
315
316 l_mv := get_fact_mv_name
317 ( 'ASSET_DOWNTIME_DTL_REPORT'
318 , p_param
319 , l_dim_bmap
323 ( p_param
320 , l_custom_output
321 );
322 l_order_by := isc_maint_rpt_util_pkg.get_parameter_value
324 , 'ORDERBY'
325 );
326 if l_order_by like '%DESC%' then
327 l_asc_desc := ' desc ';
328 else
329 l_asc_desc := ' asc ';
330 end if;
331
332 l_rank_order := 'order by ' ||
333 case when l_order_by like '%BIV_MEASURE3%'
334 then
335 'START_DATE '
336 when l_order_by like '%BIV_MEASURE13%'
337 then
338 'END_DATE'
339 else
340 'dt_overlap_hrs'
341 end || l_asc_desc ;
342
343 /* added organization_id since making asset_group independent of org. the org_id is
344 not getting propogated which is required as a parameter in the hyperlink to the
345 work order */
346
347 isc_maint_rpt_util_pkg.add_detail_column
348 ( p_detail_col_tbl => l_detail_col_tbl
349 , p_dimension_tbl => l_dimension_tbl
350 , p_dimension_level => isc_maint_rpt_util_pkg.G_ORGANIZATION
351 , p_column_key => 'organization_id'
352 );
353
354
355 isc_maint_rpt_util_pkg.add_detail_column
356 ( p_detail_col_tbl => l_detail_col_tbl
357 , p_dimension_tbl => l_dimension_tbl
358 , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_NUMBER
359 , p_column_key => 'instance_id'
360 );
361
362 isc_maint_rpt_util_pkg.add_detail_column
363 ( p_detail_col_tbl => l_detail_col_tbl
364 , p_dimension_tbl => l_dimension_tbl
365 , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_GROUP
366 , p_column_key => 'asset_group'
367 );
368
369 isc_maint_rpt_util_pkg.add_detail_column
370 ( p_detail_col_tbl => l_detail_col_tbl
371 , p_dimension_tbl => l_dimension_tbl
372 , p_fact_col_name => 'START_DATE'
373 , p_fact_col_total => 'N'
374 , p_column_key => 'START_DATE'
375 );
376
377 isc_maint_rpt_util_pkg.add_detail_column
378 ( p_detail_col_tbl => l_detail_col_tbl
379 , p_dimension_tbl => l_dimension_tbl
380 , p_fact_col_name => 'END_DATE'
381 , p_fact_col_total => 'N'
382 , p_column_key => 'END_DATE'
383 );
384
385 isc_maint_rpt_util_pkg.add_detail_column
386 ( p_detail_col_tbl => l_detail_col_tbl
387 , p_dimension_tbl => l_dimension_tbl
388 , p_fact_col_name => 'WORK_ORDER_ID'
389 , p_fact_col_total => 'N'
390 , p_column_key => 'WORK_ORDER_ID'
391 );
392
393 isc_maint_rpt_util_pkg.add_detail_column
394 ( p_detail_col_tbl => l_detail_col_tbl
395 , p_dimension_tbl => l_dimension_tbl
396 , p_fact_col_name => 'WORK_ORDER_NAME'
397 , p_fact_col_total => 'N'
398 , p_column_key => 'WORK_ORDER_NAME'
399 );
400
401 isc_maint_rpt_util_pkg.add_detail_column
402 ( p_detail_col_tbl => l_detail_col_tbl
403 , p_dimension_tbl => l_dimension_tbl
404 , p_fact_col_name => 'OPERATION_SEQ_NUMBER'
405 , p_fact_col_total => 'N'
406 , p_column_key => 'OPERATION'
407 );
408
409 isc_maint_rpt_util_pkg.add_detail_column
410 ( p_detail_col_tbl => l_detail_col_tbl
414 , p_column_key => 'DESCRIPTION'
411 , p_dimension_tbl => l_dimension_tbl
412 , p_fact_col_name => 'DESCRIPTION'
413 , p_fact_col_total => 'N'
415 );
416
417 isc_maint_rpt_util_pkg.add_detail_column
418 ( p_detail_col_tbl => l_detail_col_tbl
419 , p_dimension_tbl => l_dimension_tbl
420 , p_fact_col_name => 'dt_overlap_hrs'
421 , p_fact_col_total => 'N'
422 , p_column_key => 'dt_overlap_hrs'
423 );
424
425
426 l_stmt := 'select
427 ' || isc_maint_rpt_util_pkg.get_detail_column
428 (l_detail_col_tbl,'instance_id','BIV_MEASURE1') || '
429 , ' || isc_maint_rpt_util_pkg.get_detail_column
430 (l_detail_col_tbl,'asset_group','BIV_MEASURE2') || '
431 , oset.START_DATE BIV_MEASURE3
432 , oset.END_DATE BIV_MEASURE13
433 , oset.dt_overlap_hrs BIV_MEASURE14
434 , oset.WORK_ORDER_NAME BIV_MEASURE15
435 , oset.OPERATION BIV_MEASURE16
436 , oset.DESCRIPTION BIV_MEASURE17 ,
437 case when oset.work_order_id is null then null else '||
438 isc_maint_rpt_util_pkg.get_drill_detail(' ') ||
439 ' end BIV_ATTRIBUTE1 from
440 ' || isc_maint_rpt_util_pkg.detail_sql
441 ( p_detail_col_tbl => l_detail_col_tbl
442 , p_dimension_tbl => l_dimension_tbl
443 , p_mv_name => l_mv
444 , p_where_clause => l_where_clause
445 , p_rank_order => l_rank_order
446 , p_override_date_clause => '1 = 1 '
447 );
448
449 x_custom_sql := l_stmt;
450 x_custom_output := l_custom_output;
451
452 end get_asset_dt_dtl_sql;
453
454 ----trend query--------------------------------------------
455 procedure get_trd_sql
456 (
457 p_param in bis_pmv_page_parameter_tbl
458 , x_custom_sql out nocopy varchar2
459 , x_custom_output out nocopy bis_query_attributes_tbl
460 )
461 is
462 l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
463 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
464 l_dim_bmap number;
465 l_comparison_type varchar2(200);
466 l_xtd varchar2(200);
467 l_where_clause varchar2(10000);
468 l_mv varchar2(10000);
469 l_stmt varchar2(32767);
470 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
471 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
472 l_custom_output bis_query_attributes_tbl;
473 l_cost_element varchar2(200);
474 l_curr_suffix varchar2(2);
475 l_viewby_select varchar2(200);
476 BEGIN
477 -- clear out the tables.
478 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
479 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
480
481 isc_maint_rpt_util_pkg.register_dimension_levels
482 ( l_dimension_tbl
483 , l_dim_filter_map
484 , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
485 , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
486 , isc_maint_rpt_util_pkg.G_ASSET_CATEGORY, 'Y'
487 , isc_maint_rpt_util_pkg.G_ASSET_CRITICALITY, 'Y'
488 );
489
490 isc_maint_rpt_util_pkg.process_parameters
491 ( p_param => p_param
492 , p_dimension_tbl => l_dimension_tbl
493 , p_dim_filter_map => l_dim_filter_map
497 , x_where_clause => l_where_clause
494 , p_trend => 'Y'
495 , p_custom_output => l_custom_output
496 , x_cur_suffix => l_curr_suffix
498 , x_viewby_select => l_viewby_select
499 , x_join_tbl => l_join_tbl
500 , x_dim_bmap => l_dim_bmap
501 , x_comparison_type => l_comparison_type
502 , x_xtd => l_xtd
503 );
504
505 l_mv := get_fact_mv_name
506 ( 'ASSET_DOWNTIME_REPORT'
507 , p_param
508 , l_dim_bmap
509 , l_custom_output
510 );
511
512
513 poa_dbi_util_pkg.add_column (l_col_tbl,
514 'dt_non_overlap_hrs',
515 'dt_non_overlap_hrs',
516 'N');
517
518 l_stmt := 'select
519 cal.name VIEWBY
520 , nvl(iset.p_dt_non_overlap_hrs,0) BIV_MEASURE2
521 , nvl(iset.c_dt_non_overlap_hrs,0) BIV_MEASURE3 , ' ||
522 isc_maint_rpt_util_pkg.change_column('nvl(iset.c_dt_non_overlap_hrs,0)',
523 'nvl(iset.p_dt_non_overlap_hrs,0)','BIV_MEASURE4','X')||'
524 from ' ||
525 poa_dbi_template_pkg.trend_sql
526 ( p_xtd => l_xtd
527 , p_comparison_type => l_comparison_type
528 , p_fact_name => l_mv
529 , p_where_clause => l_where_clause
533
530 , p_col_name => l_col_tbl
531 , p_use_grpid => 'N'
532 );
534 x_custom_sql := l_stmt;
535 x_custom_output := l_custom_output;
536
537 poa_dbi_util_pkg.get_custom_trend_binds
538 ( x_custom_output => l_custom_output
539 , p_xtd => l_xtd
540 , p_comparison_type => l_comparison_type
541 );
542
543 if l_custom_output is not null then
544 for i in 1..l_custom_output.count loop
545 x_custom_output.extend;
546 x_custom_output(x_custom_output.count) := l_custom_output(i);
547 end loop;
548 end if;
549
550 end get_trd_sql;
551
552
553
554 end isc_maint_asset_dt_rpt_pkg;