[Home] [Help]
PACKAGE BODY: APPS.ISC_MAINT_LAB_BLG_RPT_PKG
Source
1 PACKAGE BODY ISC_MAINT_LAB_BLG_RPT_PKG as
2 /*$Header: iscmaintlblgrptb.pls 120.1 2005/09/13 05:36:44 nbhamidi noship $ */
3 function get_fact_mv_name
4 ( p_report_type in varchar2
5 , p_param in bis_pmv_page_parameter_tbl
6 , p_dim_bmap in number
7 , p_custom_output in out nocopy bis_query_attributes_tbl
8 )
9 return varchar2
10 is
11
12 l_org_id varchar2(200);
13 l_view_by varchar2(200);
14 tmp1 varchar2(2000);
15
16 begin
17 l_org_id := isc_maint_rpt_util_pkg.get_parameter_id
18 ( p_param, isc_maint_rpt_util_pkg.G_ORGANIZATION);
19
20
21
22
23 if p_report_type = 'LAB_BLG_RPT' then
24
25 l_view_by := isc_maint_rpt_util_pkg.get_parameter_value
26 (p_param , 'VIEW_BY');
27 isc_maint_rpt_util_pkg.bind_group_id
28 ( p_dim_bmap
29 , p_custom_output
30 , isc_maint_rpt_util_pkg.G_DEPARTMENT
31 , isc_maint_rpt_util_pkg.G_RESOURCE
32 , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS
33 );
34
35 return '(select
36 grp_id,'||
37 case l_view_by
38 when isc_maint_rpt_util_pkg.G_DEPARTMENT then 'decode(department_id,-1,-1,organization_id) organization_id'
39 when isc_maint_rpt_util_pkg.G_RESOURCE then 'decode(resource_id,-1,-1,organization_id) organization_id'
40 else 'organization_id'
41 end ||'
42 ,user_defined_status_id /* system and user defined status id */
43 ,organization_id organization_id_c
44 ,to_char(department_id) department_id
45 ,to_char(resource_id) resource_id
46 ,to_char(resource_id)||''-''||to_char(department_id)||''-''||to_char(organization_id) resource_id_c
47 ,decode(department_id,-1,''-1'',department_id||''-1'') department_id_c
48 ,hours_required
49 ,hours_charged
50 from isc_maint_002_mv fact' || '
51 where fact.grp_id = &ISC_GRP_ID ' ||
52 case
53 when l_org_id is null then
54 '
55 and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'fact', l_org_id )
56 end || '
57 )';
58
59 end if;
60 return '(select
61 fact.organization_id organization_id_c
62 ,fact.organization_id organization_id
63 ,to_char(fact.department_id) department_id
64 ,decode(fact.department_id,-1,''-1'',fact.department_id||''-1'') department_id_c
65 ,fact.resource_id resource_id
66 ,to_char(resource_id) ||''-''|| to_char(fact.department_id)||''-''||
67 to_char(fact.organization_id) resource_id_c
68 ,fact.user_defined_Status_id user_defined_status_id /* system and user defined status id */
69 ,fact.operation_seq_number operation_seq_number
70 ,fact.op_start_date op_start_date
71 ,fact.op_end_date op_end_date
72 ,fact.hours_required hours_required
73 ,fact.hours_charged hours_charged
74 ,fact.work_order_name work_order_name
75 ,fact.work_order_id work_order_id
76 ,(fact.hours_required - fact.hours_charged ) hours_backlog
77 from
78 isc_maint_lab_blg_f fact where 1=1 ' ||
79 case
80 when l_org_id is null then
81 'and ' || isc_maint_rpt_util_pkg.get_sec_where_clause( 'fact', l_org_id )
82 end || '
83 ) ';
84
85 end get_fact_mv_name;
86
87
88
89 procedure get_tab_sql
90 (
91 p_param in bis_pmv_page_parameter_tbl
92 , x_custom_sql out nocopy varchar2
93 , x_custom_output out nocopy bis_query_attributes_tbl
94 )
95 is
96
97
98 l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
99 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
100 l_dim_bmap number;
101 l_comparison_type varchar2(200);
102 l_xtd varchar2(200);
103 l_where_clause varchar2(10000);
104 l_mv varchar2(10000);
105 l_stmt varchar2(32767);
106 l_drill_down varchar2(1000);
107 l_drill_across varchar2 (1000);
108
109 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
110 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
111
112 l_custom_output bis_query_attributes_tbl;
113
114 l_cost_element varchar2(200);
115 l_curr_suffix varchar2(2);
116 l_inner_query varchar2(1000);
117 l_viewby_select varchar2(200);
118 BEGIN
119
120 -- clear out the tables.
121 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
122 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
123
124 isc_maint_rpt_util_pkg.register_dimension_levels
125 ( l_dimension_tbl
126 , l_dim_filter_map
127 , isc_maint_rpt_util_pkg.G_RESOURCE, 'Y'
128 , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'
129 );
130
131 isc_maint_rpt_util_pkg.process_parameters
132 ( p_param => p_param
133 , p_dimension_tbl => l_dimension_tbl
134 , p_dim_filter_map => l_dim_filter_map
135 , p_trend => 'K'
136 , p_custom_output => l_custom_output
137 , x_cur_suffix => l_curr_suffix
138 , x_where_clause => l_where_clause
139 , x_viewby_select => l_viewby_select
140 , x_join_tbl => l_join_tbl
141 , x_dim_bmap => l_dim_bmap
142 , x_comparison_type => l_comparison_type
143 , x_xtd => l_xtd
144 );
145
146
147
148 l_mv := get_fact_mv_name
149 ( 'LAB_BLG_RPT'
150 ,p_param
151 ,l_dim_bmap
152 ,l_custom_output
153 );
154
155
156
157 poa_dbi_util_pkg.add_column(l_col_tbl,
158 'hours_required',
159 'hours_required',
160 p_grand_total => 'Y',
161 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
162 p_to_date_type => 'NA');
163
164 poa_dbi_util_pkg.add_column (l_col_tbl,
165 'hours_charged',
166 'hours_charged',
167 p_grand_total => 'Y',
168 p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
169 p_to_date_type => 'NA');
170
171 /* to enable windowing we are using an addition inline view */
172 l_stmt := ' select ' || l_viewby_select ||',
173 biv_measure1,
174 biv_measure2,
175 biv_measure3,
176 biv_measure4,
177 biv_measure5,
178 biv_measure6,
179 biv_attribute1 ';
180
181
182 if(isc_maint_rpt_util_pkg.get_parameter_id(p_param,'VIEW_BY') = isc_maint_rpt_util_pkg.G_RESOURCE)
183 then
184 l_drill_across := ', ''pFunctionName=ISC_MAINT_LAB_BLG_RPT_REP'' ||
185 ''&VIEW_BY_NAME=VIEW_BY_ID'' ||''&pParamIds=Y'' BIV_ATTRIBUTE1 ' ;
186 else
187 l_drill_across := ', null BIV_ATTRIBUTE1 ';
188 end if;
189
190 /* calculate the rank on the sorting column in the inline view */
191 l_inner_query := 'from ( select iset.*, row_number() over(&ORDER_BY_CLAUSE nulls last )-1 rnk'||
192 ' from ( select c_hours_required BIV_MEASURE1, '||
193 ' c_hours_charged BIV_MEASURE2, ' ||
194 isc_maint_rpt_util_pkg.change_column('c_hours_required','c_hours_charged','BIV_MEASURE3','X') ||
195 ', c_hours_required_total BIV_MEASURE4 , c_hours_charged_total BIV_MEASURE5 ,' ||
196 isc_maint_rpt_util_pkg.change_column('c_hours_required_total','c_hours_charged_total','BIV_MEASURE6','X')
197 ||l_drill_across ||','
198 ||isc_maint_rpt_util_pkg.get_inner_select_col(l_join_tbl)||' from ';
199
200 l_stmt := l_stmt || l_inner_query;
201 l_stmt := l_stmt || poa_dbi_template_pkg.status_sql
202 ( p_fact_name => l_mv
203 , p_where_clause => l_where_clause
204 , p_join_tables => l_join_tbl
205 , p_use_windowing => 'Y'
206 , p_col_name => l_col_tbl
207 , p_paren_count => 2
208 , p_use_grpid => 'N'
209 , p_filter_where => '1=1)iset'
210 , p_generate_viewby => 'Y'
211 );
212
213 x_custom_output := l_custom_output;
214 x_custom_sql := l_stmt;
215
216 end get_tab_sql;
217
218
219
220 procedure get_lab_blg_dtl
221 (
222 p_param in bis_pmv_page_parameter_tbl
223 , x_custom_sql out nocopy varchar2
224 , x_custom_output out nocopy bis_query_attributes_tbl
225 )
226 is
227 l_dimension_tbl isc_maint_rpt_util_pkg.t_dimension_tbl;
228 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
229 l_dim_bmap number;
230 l_comparison_type varchar2(200);
231 l_xtd varchar2(200);
232 l_where_clause varchar2(10000);
233 l_mv varchar2(10000);
234 l_stmt varchar2(32767);
235 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
236 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
237 l_order_by varchar2(200);
238 l_asc_desc varchar2(100);
239 l_custom_output bis_query_attributes_tbl;
240 l_rank_order varchar2(100);
241 l_cost_element varchar2(200);
242 l_curr_suffix varchar2(2);
243 l_detail_col_tbl isc_maint_rpt_util_pkg.t_detail_column_tbl;
244 l_viewby_select varchar2(200);
245
246 BEGIN
247
248 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
249 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
250
251 isc_maint_rpt_util_pkg.register_dimension_levels
252 ( l_dimension_tbl
253 , l_dim_filter_map
254 , isc_maint_rpt_util_pkg.G_WORK_ORDER_STATUS, 'Y'
255 , isc_maint_rpt_util_pkg.G_RESOURCE, 'Y'
256 );
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 => 'D'
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 ( 'LABOR_BACKLOG_DTL_REPORT'
276 , p_param
277 , l_dim_bmap
278 , l_custom_output
279 );
280 l_order_by := isc_maint_rpt_util_pkg.get_parameter_value
281 ( p_param
282 , 'ORDERBY'
283 );
284
285 if l_order_by like '%DESC%' then
286 l_asc_desc := ' desc ';
287 else
288 l_asc_desc := ' asc ';
289 end if;
290
291
292 l_rank_order := 'order by ' ||
293 case when l_order_by like '%MEASURE5%'
294 then
295 'OP_START_DATE'
296 when l_order_by like '%MEASURE6%'
297 then
298 'OP_END_DATE'
299 when l_order_by like '%MEASURE7%'
300 then
301 'hours_required'
302 when l_order_by like '%MEASURE8%'
303 then
304 'hours_charged'
305 else
306 'hours_backlog'
307 end || l_asc_desc||',work_order_id,operation_seq_number,department_id,resource_id,rowid';
308
309
310 isc_maint_rpt_util_pkg.add_detail_column
311 ( p_detail_col_tbl => l_detail_col_tbl
312 , p_dimension_tbl => l_dimension_tbl
313 , p_dimension_level => isc_maint_rpt_util_pkg.G_RESOURCE
314 , p_column_key => 'resource_id'
315 );
316
317 isc_maint_rpt_util_pkg.add_detail_column
318 ( p_detail_col_tbl => l_detail_col_tbl
319 , p_dimension_tbl => l_dimension_tbl
320 , p_dimension_level => isc_maint_rpt_util_pkg.G_DEPARTMENT
321 , p_column_key => 'department_id'
322 );
323
324 isc_maint_rpt_util_pkg.add_detail_column
325 ( p_detail_col_tbl => l_detail_col_tbl
326 , p_dimension_tbl => l_dimension_tbl
327 , p_fact_col_name => 'OP_START_DATE'
328 , p_fact_col_total => 'N'
329 , p_column_key => 'OP_START_DATE'
330 );
331
332 isc_maint_rpt_util_pkg.add_detail_column
333 ( p_detail_col_tbl => l_detail_col_tbl
334 , p_dimension_tbl => l_dimension_tbl
335 , p_fact_col_name => 'OP_END_DATE'
336 , p_fact_col_total => 'N'
337 , p_column_key => 'OP_END_DATE'
338 );
339
340
341 isc_maint_rpt_util_pkg.add_detail_column
342 ( p_detail_col_tbl => l_detail_col_tbl
343 , p_dimension_tbl => l_dimension_tbl
344 , p_fact_col_name => 'WORK_ORDER_NAME'
345 , p_fact_col_total => 'N'
346 , p_column_key => 'WORK_ORDER_NAME'
347 );
348
349 isc_maint_rpt_util_pkg.add_detail_column
350 ( p_detail_col_tbl => l_detail_col_tbl
351 , p_dimension_tbl => l_dimension_tbl
352 , p_fact_col_name => 'WORK_ORDER_ID'
353 , p_fact_col_total => 'N'
354 , p_column_key => 'WORK_ORDER_ID'
355 );
356
357 isc_maint_rpt_util_pkg.add_detail_column
358 ( p_detail_col_tbl => l_detail_col_tbl
359 , p_dimension_tbl => l_dimension_tbl
360 , p_fact_col_name => 'OPERATION_SEQ_NUMBER'
361 , p_fact_col_total => 'N'
362 , p_column_key => 'OPERATION_SEQ_NUMBER'
363 );
364
365
366
367 isc_maint_rpt_util_pkg.add_detail_column
368 ( p_detail_col_tbl => l_detail_col_tbl
369 , p_dimension_tbl => l_dimension_tbl
370 , p_fact_col_name => 'hours_required'
371 , p_fact_col_total => 'Y'
372 , p_column_key => 'hours_required'
373 );
374
375 isc_maint_rpt_util_pkg.add_detail_column
376 ( p_detail_col_tbl => l_detail_col_tbl
377 , p_dimension_tbl => l_dimension_tbl
378 , p_fact_col_name => 'hours_charged'
379 , p_fact_col_total => 'Y'
380 , p_column_key => 'hours_charged'
381 );
382
383 isc_maint_rpt_util_pkg.add_detail_column
384 ( p_detail_col_tbl => l_detail_col_tbl
385 , p_dimension_tbl => l_dimension_tbl
386 , p_fact_col_name => 'hours_backlog'
387 , p_fact_col_total => 'Y'
388 , p_column_key => 'hours_backlog'
389 );
390
391
392
393 l_stmt := 'select
394 ' || isc_maint_rpt_util_pkg.get_detail_column
395 (l_detail_col_tbl,'resource_id','BIV_MEASURE1') || '
396 , ' || isc_maint_rpt_util_pkg.get_detail_column
397 (l_detail_col_tbl,'department_id','BIV_MEASURE2') || '
398 , oset.WORK_ORDER_NAME BIV_MEASURE3
399 , oset.OPERATION_SEQ_NUMBER BIV_MEASURE4
400 , oset.OP_START_DATE BIV_MEASURE5
401 , oset.OP_END_DATE BIV_MEASURE6
402 , oset.hours_required BIV_MEASURE7
403 , oset.hours_charged BIV_MEASURE8
404 , oset.hours_backlog BIV_MEASURE9
405 , oset.hours_required_total BIV_MEASURE10
406 , oset.hours_charged_total BIV_MEASURE11 , '||
407 isc_maint_rpt_util_pkg.change_column('oset.hours_required_total','oset.hours_charged_total'
408 ,'BIV_MEASURE12','X') ||' , ' ||
409 isc_maint_rpt_util_pkg.get_drill_detail('BIV_ATTRIBUTE1') ||
410 ' from
411 ' || isc_maint_rpt_util_pkg.detail_sql
412 ( p_detail_col_tbl => l_detail_col_tbl
413 , p_dimension_tbl => l_dimension_tbl
414 , p_mv_name => l_mv
415 , p_where_clause => l_where_clause
416 , p_rank_order => l_rank_order
417 , p_override_date_clause => '1 = 1 '
418 );
419
420 x_custom_sql := l_stmt;
421 x_custom_output := l_custom_output;
422
423 end get_lab_blg_dtl;
424
425 end isc_maint_lab_blg_rpt_pkg;
426