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