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