[Home] [Help]
PACKAGE BODY: APPS.ISC_MAINT_WO_CMPL_RPT_PKG
Source
1 PACKAGE BODY isc_maint_wo_cmpl_rpt_pkg
2 /*$Header: iscmaintwocrptb.pls 120.1 2005/09/13 05:38:08 nbhamidi noship $ */
3 as
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
17 begin
18
19 l_org_id := isc_maint_rpt_util_pkg.get_parameter_id
20 ( p_param
21 , isc_maint_rpt_util_pkg.G_ORGANIZATION
22 );
23
24 if p_report_type = 'WORK_ORDER_CMPL' then
25
26 l_view_by := isc_maint_rpt_util_pkg.get_parameter_value
27 ( p_param
28 , 'VIEW_BY'
29 );
30
31 isc_maint_rpt_util_pkg.bind_group_id
32 ( p_dim_bmap
33 , p_custom_output
34 /* , isc_maint_rpt_util_pkg.G_DEPARTMENT Removed rollup from MV */
35 , isc_maint_rpt_util_pkg.G_ACTIVITY
36 , isc_maint_rpt_util_pkg.G_ASSET_GROUP
37 , isc_maint_rpt_util_pkg.G_ASSET_NUMBER
38 /* , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE Removed rollup from MV */
39 );
40
41 return ' (select
42 time_id
43 , period_type_id
44 , grp_id
45 , ' ||
46 case l_view_by
47 when isc_maint_rpt_util_pkg.G_DEPARTMENT then 'decode(department_id,-1,-1,organization_id) organization_id'
48 when isc_maint_rpt_util_pkg.G_ASSET_GROUP then 'decode(asset_group_id,-1,-1,organization_id) organization_id'
49 when isc_maint_rpt_util_pkg.G_ASSET_NUMBER then 'decode(instance_id,-1,-1,organization_id) organization_id' /* replaced asset_number with instance_id */
50 when isc_maint_rpt_util_pkg.G_ACTIVITY then 'decode(activity_id,-1,-1,organization_id) organization_id'
51 else 'organization_id'
52 end ||'
53 , organization_id organization_id_c
54 , ' ||
55 case l_view_by
56 when isc_maint_rpt_util_pkg.G_ASSET_NUMBER then 'decode(instance_id,-1,-1,asset_group_id) asset_group_id ' /* replaced asset_number with instance_id */
57 else 'asset_group_id'
58 end ||'
59 , instance_id /* replaced asset_number with instance_id */
60 , activity_id
61 , work_order_type
62 , to_char(department_id) department_id
63 , asset_group_id asset_group_id_c /* removed concatenation to org. to make asset group independent of org. */
64 , decode(instance_id,-1,-1,instance_id) instance_id_c /* replaced asset_number with instance_id */
65 , decode(activity_id,-1,''-1'',activity_id||''-''||organization_id) activity_id_c
66 , decode(department_id,-1,''-1'',department_id||''-1'') department_id_c
67 , num_completion
68 , num_late_completion
69 , days_late
70 from isc_maint_004_mv fact' || '
71 where fact.grp_id = &ISC_GRP_ID ' ||
72 case
73 when l_org_id is null then
74 '
75 and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'fact', l_org_id )
76 end || '
77 )';
78
79 elsif p_report_type = 'WORK_ORDER_LATE_CMPL_AGING' then
80
81 l_view_by := isc_maint_rpt_util_pkg.get_parameter_value
82 ( p_param
83 , 'VIEW_BY'
84 );
85
86 isc_maint_rpt_util_pkg.bind_group_id
87 ( p_dim_bmap
88 , p_custom_output
89 , isc_maint_rpt_util_pkg.G_DEPARTMENT
90 , isc_maint_rpt_util_pkg.G_ACTIVITY
91 , isc_maint_rpt_util_pkg.G_ASSET_GROUP
92 , isc_maint_rpt_util_pkg.G_ASSET_NUMBER
93 , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE
94 );
95
96 return ' (select
97 time_id
98 , period_type_id
99 , grp_id
100 , organization_id
101 , organization_id organization_id_c
102 , bucket_num
103 , asset_group_id
104 , instance_id /* replaced asset_number with instance_id */
105 , activity_id
106 , work_order_type
107 , to_char(department_id) department_id
108 , asset_group_id asset_group_id_c /* removed concatenation to org. to make asset group independent of org. */
109 , decode(instance_id,-1,-1,instance_id) instance_id_c /* replaced asset_number with instance_id */
110 , decode(activity_id,-1,''-1'',activity_id||''-''||organization_id) activity_id_c
111 , decode(department_id,-1,''-1'',department_id||''-1'') department_id_c
112 , num_count
113 from isc_maint_006_mv fact' || '
114 where fact.grp_id = &ISC_GRP_ID ' ||
115 case
116 when l_org_id is null then
117 '
118 and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'fact', l_org_id )
119 end || '
120 )';
121
122
123 elsif p_report_type = 'WORK_ORDER_CMPL_DTL' then
124 return '(select
125 activity_id
126 , organization_id
127 , organization_id organization_id_c
128 , asset_group_id
129 , instance_id /* replaced asset_number with instance_id */
130 , work_order_name
131 , to_char(department_id) department_id
132 , asset_group_id asset_group_id_c
133 , decode(instance_id,-1,-1,instance_id) instance_id_c /* replaced asset_number with instance_id */
134 , decode(activity_id,-1,''-1'',activity_id||''-''||organization_id) activity_id_c
135 , decode(department_id,-1,''-1'',department_id||''-1'') department_id_c
136 , work_order_id
137 , status.value wo_status
138 , work_order_type
139 , SCHEDULED_COMPLETION_DATE
140 , SCHEDULED_START_DATE
141 , COMPLETION_DATE
142 from isc_maint_work_orders_f f,
143 biv_maint_wo_status_lvl_v status
144 where
145 f.user_defined_status_id = status.id
146 and f.Include_WO = 1 /* Do not include Pending Close, Failed Close, Cancelled */
147 and f.COMPLETION_DATE is not null
148 and f.COMPLETION_DATE <= &BIS_CURRENT_ASOF_DATE
149 and f.COMPLETION_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE ' ||
150 case
151 when l_org_id is null then
152 '
153 and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'f', l_org_id )
154 end || '
155 ) ';
156
157 elsif p_report_type = 'WORK_ORDER_LATE_DTL' then
158 return '(select
159 activity_id
160 , organization_id
161 , organization_id organization_id_c
162 , asset_group_id
163 , instance_id /* replaced asset_number with instance_id */
164 , work_order_name
165 , to_char(department_id) department_id
166 , asset_group_id asset_group_id_c /* removed concatenation to org. to make asset group independent of org. */
167 , decode(instance_id,-1,-1,instance_id) instance_id_c /* replaced asset_number with instance_id */
168 , decode(activity_id,-1,''-1'',activity_id||''-''||organization_id) activity_id_c
169 , decode(department_id,-1,''-1'',department_id||''-1'') department_id_c
170 , work_order_id
171 , status.value wo_status
172 , work_order_type
173 , SCHEDULED_COMPLETION_DATE
174 , SCHEDULED_START_DATE
175 , COMPLETION_DATE
176 , DAYS_LATE
177 , case
178 when f.days_late >= b.range1_low and (f.days_late < b.range1_high or b.range1_high is null) then 1
179 when f.days_late >= b.range2_low and (f.days_late < b.range2_high or b.range2_high is null) then 2
180 when f.days_late >= b.range3_low and (f.days_late < b.range3_high or b.range3_high is null) then 3
181 when f.days_late >= b.range4_low and (f.days_late < b.range4_high or b.range4_high is null) then 4
182 when f.days_late >= b.range5_low and (f.days_late < b.range5_high or b.range5_high is null) then 5
183 when f.days_late >= b.range6_low and (f.days_late < b.range6_high or b.range6_high is null) then 6
184 when f.days_late >= b.range7_low and (f.days_late < b.range7_high or b.range7_high is null) then 7
185 when f.days_late >= b.range8_low and (f.days_late < b.range8_high or b.range8_high is null) then 8
186 when f.days_late >= b.range9_low and (f.days_late < b.range9_high or b.range9_high is null) then 9
187 else 10
188 end bucket_num
189 from isc_maint_work_orders_f f,
190 bis_bucket_customizations b,
191 bis_bucket bb,
192 biv_maint_wo_status_lvl_v status
193 where
194 f.user_defined_status_id = status.id
195 and f.Include_WO = 1 /* Do not include Pending Close, Failed Close, Cancelled */
196 and f.COMPLETION_DATE is not null
197 and bb.short_name = ''BIV_MAINT_LATECMPL_AGING''
198 and bb.bucket_id = b.bucket_id
199 and f.SCHEDULED_COMPLETION_DATE < f.COMPLETION_DATE
200 and f.COMPLETION_DATE <= &BIS_CURRENT_ASOF_DATE
201 and f.COMPLETION_DATE >= &BIS_CURRENT_EFFECTIVE_START_DATE ' ||
202 case
203 when l_org_id is null then
204 '
205 and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'f', l_org_id )
206 end || '
207 ) ';
208
209 end if;
210
211 end get_fact_mv_name;
212
213
214 procedure get_tbl_sql
215 ( p_param in bis_pmv_page_parameter_tbl
216 , x_custom_sql out nocopy varchar2
217 , x_custom_output out nocopy bis_query_attributes_tbl
218 )
219 is
220
221 l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
222 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
223 l_dim_bmap number;
224 l_comparison_type varchar2(200);
225 l_xtd varchar2(200);
226 l_where_clause varchar2(10000);
227 l_mv varchar2(10000);
228 l_stmt varchar2(32767);
229
230 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
231 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
232
233 l_custom_output bis_query_attributes_tbl;
234
235 l_cost_element varchar2(200);
236 l_curr_suffix varchar2(2);
237
238 l_viewby_select varchar2(200);
239 l_asset_grp_column varchar2(200);
240 l_inner_query varchar2(5000);
241
242 begin
243
244 -- clear out the tables.
245 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
246 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
247
248 isc_maint_rpt_util_pkg.register_dimension_levels
249 ( l_dimension_tbl
250 , l_dim_filter_map
251 , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
252 , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
253 , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
254 , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
255 );
256
257 isc_maint_rpt_util_pkg.process_parameters
258 ( p_param => p_param
259 , p_dimension_tbl => l_dimension_tbl
260 , p_dim_filter_map => l_dim_filter_map
261 , p_trend => 'N'
262 , p_custom_output => l_custom_output
263 , x_cur_suffix => l_curr_suffix
264 , x_where_clause => l_where_clause
265 , x_viewby_select => l_viewby_select
266 , x_join_tbl => l_join_tbl
267 , x_dim_bmap => l_dim_bmap
268 , x_comparison_type => l_comparison_type
269 , x_xtd => l_xtd
270 );
271
272 l_mv := get_fact_mv_name
273 ( 'WORK_ORDER_CMPL'
274 , p_param
275 , l_dim_bmap
276 , l_custom_output
277 );
278
279 -- The measure columns that need to be aggregated are
280 -- completed_wo, on_time_cmp_wo, late_cmp_wo
281
282 poa_dbi_util_pkg.add_column (l_col_tbl,
283 'num_completion',
284 'num_completion');
285
286 poa_dbi_util_pkg.add_column (l_col_tbl,
287 'num_late_completion',
288 'num_late_completion');
289 poa_dbi_util_pkg.add_column (l_col_tbl,
290 'days_late',
291 'days_late');
292
293 /* if view by is asset number add asset_group column */
294
295 if(isc_maint_rpt_util_pkg.get_parameter_id(p_param,'VIEW_BY')) =
296 isc_maint_rpt_util_pkg.G_ASSET_NUMBER then
297 l_asset_grp_column := isc_maint_rpt_util_pkg.add_asset_group_column(isc_maint_rpt_util_pkg.G_ASSET_NUMBER,l_dimension_tbl);
298 else
299 l_asset_grp_column :='NULL';
300 end if ;
301
302 /* to enable windowing we are using an addition inline view */
303 l_stmt := ' select ' || l_viewby_select ||
304 ', BIV_ATTRIBUTE1
305 , BIV_ATTRIBUTE2
306 , BIV_MEASURE1
307 , BIV_MEASURE2
308 , BIV_MEASURE3
309 , BIV_MEASURE22
310 , BIV_MEASURE4
311 , BIV_MEASURE23
312 , BIV_MEASURE5
313 , BIV_MEASURE6
314 , BIV_MEASURE7
315 , BIV_MEASURE8
316 , BIV_MEASURE9
317 , BIV_MEASURE10
318 , BIV_MEASURE11
319 , BIV_MEASURE12
320 , BIV_MEASURE26
321 , BIV_MEASURE13
322 , BIV_MEASURE14
323 , BIV_MEASURE15
324 , BIV_MEASURE24
325 , BIV_MEASURE16
326 , BIV_MEASURE17
327 , BIV_MEASURE18
328 , BIV_MEASURE28
329 , BIV_MEASURE19
330 , BIV_MEASURE20
331 , BIV_MEASURE21 ,
332 ' ||
333 l_asset_grp_column ||' BIV_MEASURE30 ';
334
335 /* calculate the rank on the sorting column in the inline view */
336
337 l_inner_query := 'from ( select row_number() over(&ORDER_BY_CLAUSE)-1 rnk,iset.*
338 from (select '||
339 case
340 when isc_maint_rpt_util_pkg.get_parameter_id
341 ( p_param
342 , 'VIEW_BY'
343 ) in ( isc_maint_rpt_util_pkg.G_ASSET_GROUP
344 , isc_maint_rpt_util_pkg.G_ASSET_NUMBER
345 , isc_maint_rpt_util_pkg.G_ACTIVITY ) then
346 '
347 ''pFunctionName=ISC_MAINT_WO_CMPL_DTL_RPT_REP'' ||
348 ''&VIEW_BY_NAME=VIEW_BY_ID'' ||
349 ''&pParamIds=Y'' BIV_ATTRIBUTE1 '
350 else '
351 null BIV_ATTRIBUTE1 ' end || '
352 , ''pFunctionName=ISC_MAINT_LATECMPL_DTL_RPT_REP'' ||
353 ''&VIEW_BY_NAME=VIEW_BY_ID'' ||
354 ''&pParamIds=Y'' BIV_ATTRIBUTE2
355 , nvl(oset05.p_num_completion,0) BIV_MEASURE1
356 , nvl(oset05.c_num_completion,0) BIV_MEASURE2' || '
357 , ' ||
358 isc_maint_rpt_util_pkg.change_column
359 ( 'oset05.c_num_completion'
360 , 'oset05.p_num_completion'
361 , 'BIV_MEASURE3' ) || ' /* Completion Change */
362 , nvl(oset05.p_num_completion - oset05.p_num_late_completion,0) BIV_MEASURE22
363 , nvl(oset05.c_num_completion - oset05.c_num_late_completion,0) BIV_MEASURE4 ' || '
364 , ' ||
365 isc_maint_rpt_util_pkg.change_column
366 ( 'oset05.c_num_completion - oset05.c_num_late_completion'
367 , 'oset05.p_num_completion - oset05.p_num_late_completion'
368 , 'BIV_MEASURE23' ) || ' /* On Time Completion Change */
369 , ' ||
370 isc_maint_rpt_util_pkg.rate_column
371 ( 'oset05.p_num_completion - oset05.p_num_late_completion'
372 , 'oset05.p_num_completion'
373 , 'BIV_MEASURE5'
374 , 'Y' ) || '
375 /* Prior On time completion percent */, ' ||
376 isc_maint_rpt_util_pkg.rate_column
377 ( 'oset05.c_num_completion - oset05.c_num_late_completion'
378 , 'oset05.c_num_completion'
379 , 'BIV_MEASURE6'
380 , 'Y' ) || '
381 /* On time completion percent */, ' ||
382 isc_maint_rpt_util_pkg.change_column
383 ( isc_maint_rpt_util_pkg.rate_column
384 ( 'oset05.c_num_completion - oset05.c_num_late_completion'
385 , 'oset05.c_num_completion'
386 , ''
387 , 'Y' )
388 , isc_maint_rpt_util_pkg.rate_column
389 ( 'oset05.p_num_completion - oset05.p_num_late_completion'
390 , 'oset05.p_num_completion'
391 , ''
392 , 'Y' )
393 , 'BIV_MEASURE7'
394 , 'N' ) || ' /* On Time Completion Change */
395 , nvl(oset05.c_num_late_completion,0) BIV_MEASURE8 ' || '
396 , ' ||
397 isc_maint_rpt_util_pkg.rate_column
398 ( 'oset05.p_num_late_completion'
399 , 'oset05.p_num_completion'
400 , 'BIV_MEASURE9'
401 , 'Y' ) || '
402 /* Late Completion Percent */, ' ||
403 isc_maint_rpt_util_pkg.rate_column
404 ( 'oset05.c_num_late_completion'
405 , 'oset05.c_num_completion'
406 , 'BIV_MEASURE10'
407 , 'Y' ) || '
408 /* Prior Late Completion Percent */, ' ||
409 isc_maint_rpt_util_pkg.change_column
410 ( isc_maint_rpt_util_pkg.rate_column
411 ( 'oset05.c_num_late_completion'
412 , 'oset05.c_num_completion'
413 , ''
414 , 'Y' )
415 , isc_maint_rpt_util_pkg.rate_column
416 ( 'oset05.p_num_late_completion'
417 , 'oset05.p_num_completion'
418 , ''
419 , 'Y' )
420 , 'BIV_MEASURE11'
421 , 'N' ) || '
422 /* Late Completion Change */, ' ||
423 isc_maint_rpt_util_pkg.rate_column
424 ( 'oset05.c_days_late'
425 , 'oset05.c_num_late_completion'
426 , 'BIV_MEASURE12'
427 , 'N' ) || ' /* Average Days Late */
428 , nvl(oset05.p_num_completion_total,0) BIV_MEASURE26 ' || '
429 , nvl(oset05.c_num_completion_total,0) BIV_MEASURE13 ' || '
430 , ' ||
431 isc_maint_rpt_util_pkg.rate_column
432 ( 'oset05.c_num_completion_total - oset05.p_num_completion_total'
433 , 'oset05.p_num_completion_total'
434 , 'BIV_MEASURE14'
435 , 'Y' ) || ' /* Grand Total Completion Change */
436 , nvl(oset05.c_num_completion_total - oset05.c_num_late_completion_total,0) BIV_MEASURE15 ' || '
437 , ' ||
438 isc_maint_rpt_util_pkg.rate_column
439 ( 'oset05.c_num_completion_total - oset05.c_num_late_completion_total'
440 , 'oset05.p_num_completion_total - oset05.p_num_late_completion_total'
441 , 'BIV_MEASURE24'
442 , 'Y' ) || ' /* Grand Total On time completion Change */, ' ||
443 isc_maint_rpt_util_pkg.rate_column
444 ( 'oset05.c_num_completion_total - oset05.c_num_late_completion_total'
445 , 'oset05.c_num_completion_total'
446 , 'BIV_MEASURE16'
447 , 'Y' ) || ' /* On time completion percent Grand Total */, ' ||
448 isc_maint_rpt_util_pkg.change_column
449 ( isc_maint_rpt_util_pkg.rate_column
450 ( 'oset05.c_num_completion_total - oset05.c_num_late_completion_total'
451 , 'oset05.c_num_completion_total'
452 , ''
453 , 'Y' )
454 , isc_maint_rpt_util_pkg.rate_column
455 ( 'oset05.p_num_completion_total - oset05.p_num_late_completion_total'
456 , 'oset05.p_num_completion_total'
457 , ''
458 , 'Y' )
459 , 'BIV_MEASURE17'
460 , 'N' ) || ' /* Grand Total On Time Completion Percent Change */
461 , nvl(oset05.c_num_late_completion_total,0) BIV_MEASURE18, ' ||
462 isc_maint_rpt_util_pkg.rate_column
463 ( 'oset05.p_num_late_completion_total'
464 , 'oset05.p_num_completion_total'
465 , 'BIV_MEASURE28'
466 , 'Y' ) || ' /* Prior Late Completion Percent Grand Total */, ' ||
467 isc_maint_rpt_util_pkg.rate_column
468 ( 'oset05.c_num_late_completion_total'
469 , 'oset05.c_num_completion_total'
470 , 'BIV_MEASURE19'
471 , 'Y' ) || ' /* Late Completion Percent Grand Total */, ' ||
472 isc_maint_rpt_util_pkg.change_column
473 ( isc_maint_rpt_util_pkg.rate_column
474 ( 'oset05.c_num_late_completion_total'
475 , 'oset05.c_num_completion_total'
476 , ''
477 , 'Y' )
478 , isc_maint_rpt_util_pkg.rate_column
479 ( 'oset05.p_num_late_completion_total'
480 , 'oset05.p_num_completion_total'
481 , ''
482 , 'Y' )
483 , 'BIV_MEASURE20'
484 , 'N' ) || ' /* Grand Total Late Completion Change */, ' ||
485 isc_maint_rpt_util_pkg.rate_column
486 ( 'oset05.c_days_late_total'
487 , 'c_num_late_completion_total'
488 , 'BIV_MEASURE21'
489 , 'N' ) || ' , ' ||
490 isc_maint_rpt_util_pkg.get_inner_select_col(l_join_tbl)||' from ';
491
492
493 l_stmt := l_stmt || l_inner_query || poa_dbi_template_pkg.status_sql
494 ( p_fact_name => l_mv
495 , p_where_clause => l_where_clause
496 , p_join_tables => l_join_tbl
497 , p_use_windowing => 'Y'
498 , p_col_name => l_col_tbl
499 , p_use_grpid => 'N'
500 , p_paren_count => 2
501 , p_filter_where => '(c_num_completion <> 0 or p_num_completion <> 0
502 or c_num_late_completion <> 0 or p_num_late_completion <> 0 or c_days_late <> 0 or p_days_late <> 0))iset'
503 , p_generate_viewby => 'Y'
504 );
505
506 /* the next line can be used to dump the contents of the PMV parameters as comments into stmt */
507 -- l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);
508
509 x_custom_output := l_custom_output;
510
511 x_custom_sql := l_stmt;
512
513 end get_tbl_sql;
514
515
516
517 procedure get_trd_sql
518 ( p_param in bis_pmv_page_parameter_tbl
519 , x_custom_sql out nocopy varchar2
520 , x_custom_output out nocopy bis_query_attributes_tbl
521 )
522 is
523
524 l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
525 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
526 l_dim_bmap number;
527 l_comparison_type varchar2(200);
528 l_xtd varchar2(200);
529 l_where_clause varchar2(10000);
530 l_mv varchar2(10000);
531 l_stmt varchar2(32767);
532
533 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
534 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
535
536 l_custom_output bis_query_attributes_tbl;
537
538 l_cost_element varchar2(200);
539 l_curr_suffix varchar2(2);
540
541 l_viewby_select varchar2(200);
542
543 begin
544
545 -- clear out the tables.
546 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
547 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
548
549 isc_maint_rpt_util_pkg.register_dimension_levels
550 ( l_dimension_tbl
551 , l_dim_filter_map
552 , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
553 , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
554 , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
555 , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
556 );
557
558 isc_maint_rpt_util_pkg.process_parameters
559 ( p_param => p_param
560 , p_dimension_tbl => l_dimension_tbl
561 , p_dim_filter_map => l_dim_filter_map
562 , p_trend => 'Y'
563 , p_custom_output => l_custom_output
564 , x_cur_suffix => l_curr_suffix
565 , x_where_clause => l_where_clause
566 , x_viewby_select => l_viewby_select
567 , x_join_tbl => l_join_tbl
568 , x_dim_bmap => l_dim_bmap
569 , x_comparison_type => l_comparison_type
570 , x_xtd => l_xtd
571 );
572
573 l_mv := get_fact_mv_name
574 ( 'WORK_ORDER_CMPL'
575 , p_param
576 , l_dim_bmap
577 , l_custom_output
578 );
579
580 -- The measure columns that need to be aggregated are
581 -- completed_wo, on_time_cmp_wo, late_cmp_wo
582 -- No Grand totals required.
583
584 poa_dbi_util_pkg.add_column (l_col_tbl,
585 'num_completion',
586 'num_completion',
587 'N');
588
589 poa_dbi_util_pkg.add_column (l_col_tbl,
590 'num_late_completion',
591 'num_late_completion',
592 'N');
593 poa_dbi_util_pkg.add_column (l_col_tbl,
594 'days_late',
595 'days_late',
596 'N');
597
598 l_stmt := 'select
599 cal.name VIEWBY
600 , nvl(iset.p_num_completion,0) BIV_MEASURE1
601 , nvl(iset.c_num_completion,0) BIV_MEASURE2
602 , ' ||
603 isc_maint_rpt_util_pkg.change_column
604 ( 'iset.c_num_completion'
605 , 'iset.p_num_completion'
606 , 'BIV_MEASURE3' ) || ' /* Completion Change */
607 , nvl(iset.p_num_completion - iset.p_num_late_completion,0) BIV_MEASURE13
608 , nvl(iset.c_num_completion - iset.c_num_late_completion,0) BIV_MEASURE4
609 , ' ||
610 isc_maint_rpt_util_pkg.change_column
611 ( 'iset.c_num_completion - iset.c_num_late_completion'
612 , 'iset.p_num_completion - iset.p_num_late_completion'
613 , 'BIV_MEASURE14' ) || ' /* On time Completion Change */
614 , ' ||
615 isc_maint_rpt_util_pkg.rate_column
616 ( 'iset.p_num_completion - iset.p_num_late_completion'
617 , 'iset.p_num_completion'
618 , 'BIV_MEASURE5'
619 , 'Y' ) || '
620 /* Prior On time completion percent */
621 , ' ||
622 isc_maint_rpt_util_pkg.rate_column
623 ( 'iset.c_num_completion - iset.c_num_late_completion'
624 , 'iset.c_num_completion'
625 , 'BIV_MEASURE6'
626 , 'Y' ) || '
627 /* On time completion percent */
628 , ' ||
629 isc_maint_rpt_util_pkg.change_column
630 ( isc_maint_rpt_util_pkg.rate_column
631 ( 'iset.c_num_completion - iset.c_num_late_completion'
632 , 'iset.c_num_completion'
633 , ''
634 , 'Y' )
635 , isc_maint_rpt_util_pkg.rate_column
636 ( 'iset.p_num_completion - iset.p_num_late_completion'
637 , 'iset.p_num_completion'
638 , ''
639 , 'Y' )
640 , 'BIV_MEASURE7'
641 , 'N' ) || ' /* On Time Completion Change */
642 , nvl(iset.c_num_late_completion,0) BIV_MEASURE8 ' || '
643 , ' ||
644 isc_maint_rpt_util_pkg.rate_column
645 ( 'iset.p_num_late_completion'
646 , 'iset.p_num_completion'
647 , 'BIV_MEASURE9'
648 , 'Y' ) || '
649 /* Prior Late Completion Percent */, ' ||
650 isc_maint_rpt_util_pkg.rate_column
651 ( 'iset.c_num_late_completion'
652 , 'iset.c_num_completion'
653 , 'BIV_MEASURE10'
654 , 'Y' ) || '
655 /* Late Completion Percent */
656 , ' ||
657 isc_maint_rpt_util_pkg.change_column
658 ( isc_maint_rpt_util_pkg.rate_column
659 ( 'iset.c_num_late_completion'
660 , 'iset.c_num_completion'
661 , ''
662 , 'Y' )
663 , isc_maint_rpt_util_pkg.rate_column
664 ( 'iset.p_num_late_completion'
665 , 'iset.p_num_completion'
666 , ''
667 , 'Y' )
668 , 'BIV_MEASURE11'
669 , 'N' ) || '
670 /* Late Completion Change */
671 , ' ||
672 isc_maint_rpt_util_pkg.rate_column
673 ( 'iset.c_days_late'
674 , 'iset.c_num_late_completion'
675 , 'BIV_MEASURE12'
676 , 'N' ) || ' /* Average Days Late */
677 from
678 ' || poa_dbi_template_pkg.trend_sql
679 ( p_xtd => l_xtd
680 , p_comparison_type => l_comparison_type
681 , p_fact_name => l_mv
682 , p_where_clause => l_where_clause
683 , p_col_name => l_col_tbl
684 , p_use_grpid => 'N'
685 );
686
687 /* the next line can be used to dump the contents of the PMV parameters as comments into stmt */
688 --l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);
689
690 x_custom_sql := l_stmt;
691
692 x_custom_output := l_custom_output;
693
694 poa_dbi_util_pkg.get_custom_trend_binds
695 ( x_custom_output => l_custom_output
696 , p_xtd => l_xtd
697 , p_comparison_type => l_comparison_type
698 );
699
700 if l_custom_output is not null then
701 for i in 1..l_custom_output.count loop
702 x_custom_output.extend;
703 x_custom_output(x_custom_output.count) := l_custom_output(i);
704 end loop;
705 end if;
706
707 end get_trd_sql;
708
709
710
711 procedure get_late_cmpl_age
712 ( p_param in bis_pmv_page_parameter_tbl
713 , x_custom_sql out nocopy varchar2
714 , x_custom_output out nocopy bis_query_attributes_tbl
715 )
716 is
717
718 l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
719 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
720 l_dim_bmap number;
721
722 l_comparison_type varchar2(200);
723 l_xtd varchar2(200);
724 l_where_clause varchar2(10000);
725 l_mv varchar2(10000);
726 l_stmt varchar2(32767);
727
728 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
729 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
730
731 l_custom_output bis_query_attributes_tbl;
732
733 l_cost_element varchar2(200);
734 l_curr_suffix varchar2(2);
735
736 l_orderby varchar2(40);
737
738 l_viewby_select varchar2(200);
739
740 begin
741
742 -- clear out the tables.
743 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
744 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
745
746 isc_maint_rpt_util_pkg.register_dimension_levels
747 ( l_dimension_tbl
748 , l_dim_filter_map
749 , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
750 , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
751 , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
752 , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
753 , isc_maint_rpt_util_pkg.G_LATE_CMPL_AGING, 'N'
754 );
755
756 isc_maint_rpt_util_pkg.process_parameters
757 ( p_param => p_param
758 , p_dimension_tbl => l_dimension_tbl
759 , p_dim_filter_map => l_dim_filter_map
760 , p_trend => 'N'
761 , p_custom_output => l_custom_output
762 , x_cur_suffix => l_curr_suffix
763 , x_where_clause => l_where_clause
764 , x_viewby_select => l_viewby_select
765 , x_join_tbl => l_join_tbl
766 , x_dim_bmap => l_dim_bmap
767 , x_comparison_type => l_comparison_type
768 , x_xtd => l_xtd
769 );
770
771 l_mv := get_fact_mv_name
772 ( 'WORK_ORDER_LATE_CMPL_AGING'
773 , p_param
774 , l_dim_bmap
775 , l_custom_output
776 );
777
778 -- The measure columns that need to be aggregated are
779 -- late_comp_wo
780
781 poa_dbi_util_pkg.add_column (l_col_tbl,
782 'num_count',
783 'num_count');
784
785 l_stmt := 'select
786 ' || l_viewby_select || '
787 ,''pFunctionName=ISC_MAINT_LATECMPL_DTL_RPT_REP'' ||
788 ''&VIEW_BY_NAME=VIEW_BY_ID'' ||
789 ''&pParamIds=Y'' BIV_ATTRIBUTE1
790 , nvl(oset.p_num_count,0) BIV_MEASURE1
791 , nvl(oset.c_num_count,0) BIV_MEASURE2' || '
792 , ' ||
793 isc_maint_rpt_util_pkg.change_column
794 ( 'oset.c_num_count'
795 , 'oset.p_num_count'
796 , 'BIV_MEASURE3'
797 , 'Y' ) || ' /* Late Completion Work Orders Change */
798 , ' ||
799 isc_maint_rpt_util_pkg.rate_column
800 ( 'oset.c_num_count'
801 , 'avg(oset.c_num_count_total) over()'
802 , 'BIV_MEASURE4'
803 , 'Y' ) || '
804 /* Percent of Total */ ' || '
805 , nvl(avg(c_num_count_total) over(),0) BIV_MEASURE5
806 , ' ||
807 isc_maint_rpt_util_pkg.change_column
808 ( 'avg(oset.c_num_count_total) over()'
809 , 'avg(oset.p_num_count_total) over()'
810 , 'BIV_MEASURE6'
811 , 'Y' ) || ' /* Grand Total Late Completion Work Orders Change */
812 , ' ||
813 isc_maint_rpt_util_pkg.rate_column
814 ( 'avg(c_num_count_total) over()'
815 , 'avg(c_num_count_total) over ()'
816 , 'BIV_MEASURE7'
817 , 'Y' ) || '
818 /* Grand Total Percent of Total */
819 , null BIV_ATTRIBUTE10
820 from
821 ( select * from ( ' || poa_dbi_template_pkg.status_sql
822 ( p_fact_name => l_mv
823 , p_where_clause => l_where_clause
824 , p_join_tables => l_join_tbl
825 , p_use_windowing => 'N'
826 , p_col_name => l_col_tbl
827 , p_use_grpid => 'N'
828 , p_paren_count => 3
829 , p_filter_where => ''
830 , p_generate_viewby => 'Y'
831 );
832
833 /* the next line can be used to dump the contents of the PMV parameters as comments into stmt */
834 -- l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);
835
836 l_orderby := 'ORDER BY VIEWBYID ASC';
837
838 l_stmt := replace(l_stmt,'&ORDER_BY_CLAUSE',l_orderby);
839
840 x_custom_output := l_custom_output;
841
842 x_custom_sql := l_stmt;
843
844 end get_late_cmpl_age;
845
846
847
848 PROCEDURE get_wo_dtl_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
849 x_custom_sql OUT NOCOPY VARCHAR2,
850 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
851 IS
852
853 l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
854 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
855 l_dim_bmap number;
856 l_comparison_type varchar2(200);
857 l_xtd varchar2(200);
858 l_where_clause varchar2(10000);
859 l_mv varchar2(10000);
860 l_stmt varchar2(32767);
861
862 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
863 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
864
865 l_custom_output bis_query_attributes_tbl;
866
867 l_curr_suffix varchar2(2);
868
869 l_rank_order varchar2(100);
870 l_order_by varchar2(200);
871 l_asc_desc varchar2(100);
872
873 l_viewby_select varchar2(200);
874
875 l_detail_col_tbl isc_maint_rpt_util_pkg.t_detail_column_tbl;
876
877 BEGIN
878
879 -- clear out the tables.
880 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
881 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
882
883 isc_maint_rpt_util_pkg.register_dimension_levels
884 ( l_dimension_tbl
885 , l_dim_filter_map
886 , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
887 , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
888 , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
889 , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
890 );
891
892 isc_maint_rpt_util_pkg.process_parameters
893 ( p_param => p_param
894 , p_dimension_tbl => l_dimension_tbl
895 , p_dim_filter_map => l_dim_filter_map
896 , p_trend => 'D'
897 , p_custom_output => l_custom_output
898 , x_cur_suffix => l_curr_suffix
899 , x_where_clause => l_where_clause
900 , x_viewby_select => l_viewby_select
901 , x_join_tbl => l_join_tbl
902 , x_dim_bmap => l_dim_bmap
903 , x_comparison_type => l_comparison_type
904 , x_xtd => l_xtd
905 );
906
907 l_mv := get_fact_mv_name
908 ( 'WORK_ORDER_CMPL_DTL'
909 , p_param
910 , l_dim_bmap
911 , l_custom_output
912 );
913
914 l_order_by := isc_maint_rpt_util_pkg.get_parameter_value
915 ( p_param
916 , 'ORDERBY'
917 );
918
919 if l_order_by like '% DESC%' then
920 l_asc_desc := ' desc ';
921 else
922 l_asc_desc := ' asc ';
923 end if;
924
925 l_rank_order := 'order by ' ||
926 case
927 when l_order_by like '%BIV_ATTRIBUTE6%' then
928 'wo_status'
929 when l_order_by like '%BIV_ATTRIBUTE9%' then
930 'SCHEDULED_START_DATE'
931 when l_order_by like '%BIV_ATTRIBUTE10%' then
932 'SCHEDULED_COMPLETION_DATE'
933 when l_order_by like '%BIV_ATTRIBUTE11%' then
934 'COMPLETION_DATE'
935 else
936 'COMPLETION_DATE'
937 end ||
938 l_asc_desc ||
939 'nulls last, organization_id, work_order_id';
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 => 'work_order_id'
945 , p_fact_col_total => 'N'
946 , p_column_key => 'work_order_id'
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 => 'work_order_name'
953 , p_fact_col_total => 'N'
954 , p_column_key => 'work_order_name'
955 );
956
957 isc_maint_rpt_util_pkg.add_detail_column
958 ( p_detail_col_tbl => l_detail_col_tbl
959 , p_dimension_tbl => l_dimension_tbl
960 , p_dimension_level => isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE
961 , p_column_key => 'work_order_type'
962 );
963
964 isc_maint_rpt_util_pkg.add_detail_column
965 ( p_detail_col_tbl => l_detail_col_tbl
966 , p_dimension_tbl => l_dimension_tbl
967 , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_NUMBER
968 , p_column_key => 'asset_number'
969 );
970
971 isc_maint_rpt_util_pkg.add_detail_column
972 ( p_detail_col_tbl => l_detail_col_tbl
973 , p_dimension_tbl => l_dimension_tbl
974 , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_GROUP
975 , p_column_key => 'asset_group'
976 );
977
978 isc_maint_rpt_util_pkg.add_detail_column
979 ( p_detail_col_tbl => l_detail_col_tbl
980 , p_dimension_tbl => l_dimension_tbl
981 , p_dimension_level => isc_maint_rpt_util_pkg.G_ACTIVITY
982 , p_column_key => 'activity'
983 );
984
985 isc_maint_rpt_util_pkg.add_detail_column
986 ( p_detail_col_tbl => l_detail_col_tbl
987 , p_dimension_tbl => l_dimension_tbl
988 , p_fact_col_name => 'wo_status'
989 , p_fact_col_total => 'N'
990 , p_column_key => 'wo_status'
991 );
992
993 isc_maint_rpt_util_pkg.add_detail_column
994 ( p_detail_col_tbl => l_detail_col_tbl
995 , p_dimension_tbl => l_dimension_tbl
996 , p_dimension_level => isc_maint_rpt_util_pkg.G_DEPARTMENT
997 , p_column_key => 'department'
998 );
999
1000 isc_maint_rpt_util_pkg.add_detail_column
1001 ( p_detail_col_tbl => l_detail_col_tbl
1002 , p_dimension_tbl => l_dimension_tbl
1003 , p_fact_col_name => 'SCHEDULED_START_DATE'
1004 , p_fact_col_total => 'N'
1005 , p_column_key => 'SCHEDULED_START_DATE'
1006 );
1007
1008 isc_maint_rpt_util_pkg.add_detail_column
1009 ( p_detail_col_tbl => l_detail_col_tbl
1010 , p_dimension_tbl => l_dimension_tbl
1011 , p_fact_col_name => 'SCHEDULED_COMPLETION_DATE'
1012 , p_fact_col_total => 'N'
1013 , p_column_key => 'SCHEDULED_COMPLETION_DATE'
1014 );
1015
1016 isc_maint_rpt_util_pkg.add_detail_column
1017 ( p_detail_col_tbl => l_detail_col_tbl
1018 , p_dimension_tbl => l_dimension_tbl
1019 , p_fact_col_name => 'COMPLETION_DATE'
1020 , p_fact_col_total => 'N'
1021 , p_column_key => 'COMPLETION_DATE'
1022 );
1023
1024
1025 l_stmt := 'select
1026 oset.work_order_name BIV_ATTRIBUTE1' || '
1027 , ' || isc_maint_rpt_util_pkg.get_detail_column
1028 (l_detail_col_tbl,'work_order_type','BIV_ATTRIBUTE2') || '
1029 , ' || isc_maint_rpt_util_pkg.get_detail_column
1030 (l_detail_col_tbl,'asset_number','BIV_ATTRIBUTE3') || '
1031 , ' || isc_maint_rpt_util_pkg.get_detail_column
1032 (l_detail_col_tbl,'asset_group','BIV_ATTRIBUTE4') || '
1033 , ' || isc_maint_rpt_util_pkg.get_detail_column
1034 (l_detail_col_tbl,'activity','BIV_ATTRIBUTE5') || '
1035 , oset.wo_status BIV_ATTRIBUTE6' || '
1036 , ' || isc_maint_rpt_util_pkg.get_detail_column
1037 (l_detail_col_tbl,'department','BIV_ATTRIBUTE7') || '
1038 , oset.SCHEDULED_START_DATE BIV_ATTRIBUTE9
1039 , oset.SCHEDULED_COMPLETION_DATE BIV_ATTRIBUTE10
1040 , oset.COMPLETION_DATE BIV_ATTRIBUTE11 ' || '
1041 , ' || isc_maint_rpt_util_pkg.get_drill_detail('BIV_ATTRIBUTE8') || '
1042 from
1043 ' || isc_maint_rpt_util_pkg.detail_sql
1044 ( p_detail_col_tbl => l_detail_col_tbl
1045 , p_dimension_tbl => l_dimension_tbl
1046 , p_mv_name => l_mv
1047 , p_where_clause => l_where_clause
1048 , p_rank_order => l_rank_order
1049 , p_override_date_clause => '1 = 1 '
1050 );
1051
1052 /* the next line can be used to dump the contents of the PMV parameters as comments into stmt */
1053 -- l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);
1054
1055 x_custom_sql := l_stmt;
1056
1057 x_custom_output := l_custom_output;
1058
1059 end get_wo_dtl_sql;
1060
1061
1062 PROCEDURE get_wo_late_dtl_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
1063 x_custom_sql OUT NOCOPY VARCHAR2,
1064 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1065 IS
1066
1067 l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
1068 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
1069 l_dim_bmap number;
1070
1071 l_comparison_type varchar2(200);
1072 l_xtd varchar2(200);
1073 l_where_clause varchar2(10000);
1074 l_mv varchar2(10000);
1075 l_stmt varchar2(32767);
1076
1077 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
1078 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
1079
1080 l_custom_output bis_query_attributes_tbl;
1081
1082 l_curr_suffix varchar2(2);
1083
1084 l_rank_order varchar2(100);
1085 l_order_by varchar2(200);
1086 l_asc_desc varchar2(100);
1087
1088 l_viewby_select varchar2(200);
1089
1090 l_detail_col_tbl isc_maint_rpt_util_pkg.t_detail_column_tbl;
1091
1092 BEGIN
1093
1094 -- clear out the tables.
1095 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
1096 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
1097
1098 isc_maint_rpt_util_pkg.register_dimension_levels
1099 ( l_dimension_tbl
1100 , l_dim_filter_map
1101 , isc_maint_rpt_util_pkg.G_ASSET_GROUP, 'Y'
1102 , isc_maint_rpt_util_pkg.G_ASSET_NUMBER, 'Y'
1103 , isc_maint_rpt_util_pkg.G_ACTIVITY, 'Y'
1104 , isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE, 'Y'
1105 , isc_maint_rpt_util_pkg.G_LATE_CMPL_AGING, 'Y'
1106 );
1107
1108 isc_maint_rpt_util_pkg.process_parameters
1109 ( p_param => p_param
1110 , p_dimension_tbl => l_dimension_tbl
1111 , p_dim_filter_map => l_dim_filter_map
1112 , p_trend => 'D'
1113 , p_custom_output => l_custom_output
1114 , x_cur_suffix => l_curr_suffix
1115 , x_where_clause => l_where_clause
1116 , x_viewby_select => l_viewby_select
1117 , x_join_tbl => l_join_tbl
1118 , x_dim_bmap => l_dim_bmap
1119 , x_comparison_type => l_comparison_type
1120 , x_xtd => l_xtd
1121 );
1122
1123 l_mv := get_fact_mv_name
1124 ( 'WORK_ORDER_LATE_DTL'
1125 , p_param
1126 , l_dim_bmap
1127 , l_custom_output
1128 );
1129
1130 l_order_by := isc_maint_rpt_util_pkg.get_parameter_value
1131 ( p_param
1132 , 'ORDERBY'
1133 );
1134
1135 if l_order_by like '% DESC%' then
1136 l_asc_desc := ' desc ';
1137 else
1138 l_asc_desc := ' asc ';
1139 end if;
1140
1141 l_rank_order := 'order by ' ||
1142 case
1143 when l_order_by like '%BIV_ATTRIBUTE6%' then
1144 'wo_status'
1145 when l_order_by like '%BIV_ATTRIBUTE9%' then
1146 'SCHEDULED_START_DATE'
1147 when l_order_by like '%BIV_ATTRIBUTE10%' then
1148 'SCHEDULED_COMPLETION_DATE'
1149 when l_order_by like '%BIV_ATTRIBUTE11%' then
1150 'COMPLETION_DATE'
1151 when l_order_by like '%BIV_ATTRIBUTE12%' then
1152 'days_late'
1153 else
1154 'days_late'
1155 end ||
1156 l_asc_desc ||
1157 'nulls last, organization_id, work_order_id';
1158
1159 isc_maint_rpt_util_pkg.add_detail_column
1160 ( p_detail_col_tbl => l_detail_col_tbl
1161 , p_dimension_tbl => l_dimension_tbl
1162 , p_fact_col_name => 'work_order_id'
1163 , p_fact_col_total => 'N'
1164 , p_column_key => 'work_order_id'
1165 );
1166
1167 isc_maint_rpt_util_pkg.add_detail_column
1168 ( p_detail_col_tbl => l_detail_col_tbl
1169 , p_dimension_tbl => l_dimension_tbl
1170 , p_fact_col_name => 'work_order_name'
1171 , p_fact_col_total => 'N'
1172 , p_column_key => 'work_order_name'
1173 );
1174
1175 isc_maint_rpt_util_pkg.add_detail_column
1176 ( p_detail_col_tbl => l_detail_col_tbl
1177 , p_dimension_tbl => l_dimension_tbl
1178 , p_dimension_level => isc_maint_rpt_util_pkg.G_WORK_ORDER_TYPE
1179 , p_column_key => 'work_order_type'
1180 );
1181
1182 isc_maint_rpt_util_pkg.add_detail_column
1183 ( p_detail_col_tbl => l_detail_col_tbl
1184 , p_dimension_tbl => l_dimension_tbl
1185 , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_NUMBER
1186 , p_column_key => 'asset_number'
1187 );
1188
1189 isc_maint_rpt_util_pkg.add_detail_column
1190 ( p_detail_col_tbl => l_detail_col_tbl
1191 , p_dimension_tbl => l_dimension_tbl
1192 , p_dimension_level => isc_maint_rpt_util_pkg.G_ASSET_GROUP
1193 , p_column_key => 'asset_group'
1194 );
1195
1196 isc_maint_rpt_util_pkg.add_detail_column
1197 ( p_detail_col_tbl => l_detail_col_tbl
1198 , p_dimension_tbl => l_dimension_tbl
1199 , p_dimension_level => isc_maint_rpt_util_pkg.G_ACTIVITY
1200 , p_column_key => 'activity'
1201 );
1202
1203 isc_maint_rpt_util_pkg.add_detail_column
1204 ( p_detail_col_tbl => l_detail_col_tbl
1205 , p_dimension_tbl => l_dimension_tbl
1206 , p_fact_col_name => 'wo_status'
1207 , p_fact_col_total => 'N'
1208 , p_column_key => 'wo_status'
1209 );
1210
1211 isc_maint_rpt_util_pkg.add_detail_column
1212 ( p_detail_col_tbl => l_detail_col_tbl
1213 , p_dimension_tbl => l_dimension_tbl
1214 , p_dimension_level => isc_maint_rpt_util_pkg.G_DEPARTMENT
1215 , p_column_key => 'department'
1216 );
1217
1218 isc_maint_rpt_util_pkg.add_detail_column
1219 ( p_detail_col_tbl => l_detail_col_tbl
1220 , p_dimension_tbl => l_dimension_tbl
1221 , p_fact_col_name => 'SCHEDULED_START_DATE'
1222 , p_fact_col_total => 'N'
1223 , p_column_key => 'SCHEDULED_START_DATE'
1224 );
1225
1226 isc_maint_rpt_util_pkg.add_detail_column
1227 ( p_detail_col_tbl => l_detail_col_tbl
1228 , p_dimension_tbl => l_dimension_tbl
1229 , p_fact_col_name => 'SCHEDULED_COMPLETION_DATE'
1230 , p_fact_col_total => 'N'
1231 , p_column_key => 'SCHEDULED_COMPLETION_DATE'
1232 );
1233
1234 isc_maint_rpt_util_pkg.add_detail_column
1235 ( p_detail_col_tbl => l_detail_col_tbl
1236 , p_dimension_tbl => l_dimension_tbl
1237 , p_fact_col_name => 'COMPLETION_DATE'
1238 , p_fact_col_total => 'N'
1239 , p_column_key => 'COMPLETION_DATE'
1240 );
1241
1242 isc_maint_rpt_util_pkg.add_detail_column
1243 ( p_detail_col_tbl => l_detail_col_tbl
1244 , p_dimension_tbl => l_dimension_tbl
1245 , p_fact_col_name => 'DAYS_LATE'
1246 , p_fact_col_total => 'N'
1247 , p_column_key => 'DAYS_LATE'
1248 );
1249
1250
1251 l_stmt := 'select
1252 oset.work_order_name BIV_ATTRIBUTE1' || '
1253 , ' || isc_maint_rpt_util_pkg.get_detail_column
1254 (l_detail_col_tbl,'work_order_type','BIV_ATTRIBUTE2') || '
1255 , ' || isc_maint_rpt_util_pkg.get_detail_column
1256 (l_detail_col_tbl,'asset_number','BIV_ATTRIBUTE3') || '
1257 , ' || isc_maint_rpt_util_pkg.get_detail_column
1258 (l_detail_col_tbl,'asset_group','BIV_ATTRIBUTE4') || '
1259 , ' || isc_maint_rpt_util_pkg.get_detail_column
1260 (l_detail_col_tbl,'activity','BIV_ATTRIBUTE5') || '
1261 , oset.wo_status BIV_ATTRIBUTE6' || '
1262 , ' || isc_maint_rpt_util_pkg.get_detail_column
1263 (l_detail_col_tbl,'department','BIV_ATTRIBUTE7') || '
1264 , oset.SCHEDULED_START_DATE BIV_ATTRIBUTE9
1265 , oset.SCHEDULED_COMPLETION_DATE BIV_ATTRIBUTE10
1266 , oset.COMPLETION_DATE BIV_ATTRIBUTE11
1267 , oset.DAYS_LATE BIV_ATTRIBUTE12 ' || '
1268 , null BIV_ATTRIBUTE13
1269 , ' || isc_maint_rpt_util_pkg.get_drill_detail('BIV_ATTRIBUTE8') || '
1270 from
1271 ' || isc_maint_rpt_util_pkg.detail_sql
1272 ( p_detail_col_tbl => l_detail_col_tbl
1273 , p_dimension_tbl => l_dimension_tbl
1274 , p_mv_name => l_mv
1275 , p_where_clause => l_where_clause
1276 , p_rank_order => l_rank_order
1277 , p_override_date_clause => '1 = 1 '
1278 );
1279
1280 /* the next line can be used to dump the contents of the PMV parameters as comments into stmt */
1281 -- l_stmt := l_stmt || isc_maint_rpt_util_pkg.dump_parameters(p_param);
1282
1283 x_custom_sql := l_stmt;
1284
1285 x_custom_output := l_custom_output;
1286
1287 end get_wo_late_dtl_sql;
1288
1289 end isc_maint_wo_cmpl_rpt_pkg;