[Home] [Help]
PACKAGE BODY: APPS.ISC_DEPOT_MTTR_PKG
Source
1 PACKAGE BODY ISC_DEPOT_MTTR_PKG AS
2 --$Header: iscdepotmttrrqb.pls 120.0 2005/05/25 17:41:39 appldev noship $
3
4 FUNCTION GET_MTTR_TBL_SEL_CLAUSE(p_view_by_dim IN VARCHAR2,p_view_by_col IN VARCHAR2,p_bucket_rec IN bis_bucket_pub.bis_bucket_rec_type)
5 RETURN VARCHAR2;
6
7 FUNCTION get_drill_down (p_view_by_dim IN VARCHAR2,p_function_name IN VARCHAR2)
8 RETURN VARCHAR2;
9
10 FUNCTION GET_MTTR_TRD_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_bucket_rec IN bis_bucket_pub.bis_bucket_rec_type)
11 RETURN VARCHAR2;
12
13 FUNCTION GET_MTTR_DIST_TBL_SEL_CLAUSE(p_view_by_dim IN VARCHAR2,p_view_by_col IN VARCHAR2,p_bucket_rec IN bis_bucket_pub.bis_bucket_rec_type)
14 RETURN VARCHAR2;
15
16 FUNCTION GET_MTTR_DIST_TRD_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_bucket_rec IN bis_bucket_pub.bis_bucket_rec_type)
17 RETURN VARCHAR2;
18
19 FUNCTION GET_MTTR_DTL_SEL_CLAUSE(l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type ,l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type)
20 RETURN VARCHAR2;
21
22 FUNCTION GET_SRVC_TBL_SEL_CLAUSE(p_view_by_dim IN VARCHAR2,p_view_by_col IN VARCHAR2)
23 RETURN VARCHAR2;
24
25 --Package level variables
26
27
28 -- MEAN TIME TO REPAIR STATUS REPORT
29
30 PROCEDURE GET_MTTR_TBL_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
31 x_custom_sql OUT NOCOPY VARCHAR2,
32 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
33 IS
34 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
35 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
36 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
37 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
38 l_comparison_type VARCHAR2(1);
39 l_cur_suffix VARCHAR2(2);
40 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
41 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
42 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
43 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
44 l_mv_set VARCHAR2(50);
45 l_aggregation_flag NUMBER;
46 l_custom_rec BIS_QUERY_ATTRIBUTES;
47 l_mv_type VARCHAR2(10);
48 l_err_stage VARCHAR2(32767);
49 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
50 l_debug_mode VARCHAR2(1);
51 l_module_name ISC_DEPOT_RPT_UTIL_PKG .g_module_name_typ%type;
52
53 BEGIN
54 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
55 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
56
57 -- clear out the tables.
58 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
59 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
60
61 -- get all the query parameters
62 ISC_DEPOT_RPT_UTIL_PKG .process_parameters (p_param => p_param,
63 x_view_by => l_view_by,
64 x_view_by_col_name => l_view_by_col,
65 x_comparison_type => l_comparison_type,
66 x_xtd => l_xtd,
67 x_cur_suffix => l_cur_suffix,
68 x_where_clause => l_where_clause,
69 x_mv => l_mv,
70 x_join_tbl => l_join_tbl,
71 x_mv_type => l_mv_type,
72 x_aggregation_flag => l_aggregation_flag,
73 p_trend => 'N',
74 p_mv_set => 'MTTR',
75 x_custom_output => x_custom_output);
76
77 IF (l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%') THEN
78 l_err_stage:='After calling DR_DBI_RPT_UTIL_PKG.process_parameters';
79 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_MTTR_TBL : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
80 END IF;
81
82 -- Add measure columns that need to be aggregated
83 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
84 p_col_name => 'ro_count',
85 p_alias_name => 'ro_count',
86 p_grand_total => 'Y',
87 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
88 p_to_date_type => 'XTD');
89
90 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
91 p_col_name => 'time_to_repair',
92 p_alias_name => 'time_to_repair',
93 p_grand_total => 'Y',
94 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
95 p_to_date_type => 'XTD');
96
97 poa_dbi_util_pkg.add_bucket_columns(p_short_name => 'ISC_DEPOT_MTTR'
98 , p_col_tbl => l_col_tbl
99 , p_col_name => 'time_to_repair'
100 , p_alias_name => 'ttr_distribution'
101 , p_grand_total => 'Y'
102 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
103 , p_to_date_type => 'XTD'
104 , x_bucket_rec => l_bucket_rec);
105
106 l_query := GET_MTTR_TBL_SEL_CLAUSE (l_view_by,l_view_by_col,l_bucket_rec)
107 || ' from
108 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
109 p_where_clause => l_where_clause,
110 p_join_tables => l_join_tbl,
111 p_use_windowing => 'Y',
112 p_col_name => l_col_tbl,
113 p_use_grpid => 'N',
114 p_paren_count => 3,
115 p_filter_where => NULL,
116 p_generate_viewby => 'Y',
117 p_in_join_tables => NULL);
118
119 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
120 l_err_stage:='The query is : || l_query';
121 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_MTTR_TBL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
122 END IF;
123
124 -- prepare output for bind variables
125 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
126 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
127
128 -- set the basic bind variables for the status SQL
129 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
130
131 -- Passing AGGREGATION_LEVEL_FLAG to PMV
132 l_custom_rec.attribute_name := ':AGGREGATION_FLAG';
133 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
134 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
135 l_custom_rec.attribute_value := l_aggregation_flag;
136 x_custom_output.extend;
137 x_custom_output(x_custom_output.count) := l_custom_rec;
138
139 x_custom_sql := l_query;
140
141 EXCEPTION
142
143 WHEN OTHERS THEN
144 l_err_stage := SQLERRM;
145 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
146 l_err_stage:='The exception is : ' || l_err_stage;
147 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_MTTR_TBL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
148 END IF;
149
150 END GET_MTTR_TBL_SQL;
151
152
153 FUNCTION GET_MTTR_TBL_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_view_by_col IN VARCHAR2, p_bucket_rec IN bis_bucket_pub.bis_bucket_rec_type)
154 RETURN VARCHAR2
155 IS
156
157 l_sel_clause VARCHAR2(8000);
158 l_view_by_col_name VARCHAR2(120);
159 l_description VARCHAR2(30);
160 l_drill_down_rep_1 VARCHAR2(500);
161
162 BEGIN
163 l_description := 'null';
164 l_drill_down_rep_1 := 'null';
165
166 -- Item Description for item view by
167 IF (p_view_by_dim = 'ITEM+ENI_ITEM') THEN
168 l_description := ' v. description ';
169 END IF;
170
171 l_drill_down_rep_1 := get_drill_down (p_view_by_dim => p_view_by_dim, p_function_name => 'ISC_DEPOT_MTTR_TBL_REP');
172
173 l_sel_clause :=
174 'SELECT '|| ISC_DEPOT_RPT_UTIL_PKG.get_viewby_select_clause (p_view_by_dim) || fnd_global.newline ||
175 l_description || ' BIV_ATTRIBUTE10 ';
176
177 l_sel_clause := l_sel_clause || poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
178 , p_col_name => 'BIV_ATTRIBUTE1'
179 , p_alias_name => 'BIV_ATTRIBUTE1'
180 , p_total_flag => 'N'
181 , p_prefix => NULL
182 , p_suffix => NULL
183 ) || fnd_global.newline ;
184
185 l_sel_clause := l_sel_clause ||
186 ',BIV_MEASURE11
187 ,BIV_MEASURE1
188 ,BIV_MEASURE2
189 ,BIV_MEASURE12
190 ,BIV_MEASURE3
191 ,BIV_MEASURE4
192 ,BIV_MEASURE21
193 ,BIV_MEASURE22
194 ,BIV_MEASURE23
195 ,BIV_MEASURE24
196 ,BIV_MEASURE27
197 ,BIV_MEASURE28
198 ,BIV_MEASURE29
199 ,BIV_MEASURE30 '|| fnd_global.newline;
200
201 l_sel_clause := l_sel_clause || poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
202 , p_col_name => 'BIV_MEASURE5'
203 , p_alias_name => 'BIV_MEASURE5'
204 , p_total_flag => 'N'
205 , p_prefix => NULL
206 , p_suffix => NULL
207 ) || fnd_global.newline ;
208
209 l_sel_clause := l_sel_clause || poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
210 , p_col_name => 'BIV_MEASURE25'
211 , p_alias_name => 'BIV_MEASURE25'
212 , p_total_flag => 'N'
213 , p_prefix => NULL
214 , p_suffix => NULL
215 ) || fnd_global.newline ;
216
217
218 l_sel_clause := l_sel_clause || ' ,'||l_drill_down_rep_1||' BIV_DYNAMIC_URL1' || fnd_global.newline;
219
220 l_sel_clause := l_sel_clause ||
221 'FROM ( SELECT
222 rank() over (&ORDER_BY_CLAUSE'||' nulls last, '||p_view_by_col||') - 1 rnk
223 ,'||p_view_by_col;
224
225 l_sel_clause := l_sel_clause || poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
226 , p_col_name => 'BIV_ATTRIBUTE1'
227 , p_alias_name => 'BIV_ATTRIBUTE1'
228 , p_total_flag => 'N'
229 , p_prefix => NULL
230 , p_suffix => NULL
231 ) || fnd_global.newline ;
232
233 l_sel_clause := l_sel_clause ||
234 ',BIV_MEASURE11
235 ,BIV_MEASURE1
236 ,BIV_MEASURE2
237 ,BIV_MEASURE12
238 ,BIV_MEASURE3
239 ,BIV_MEASURE4
240 ,BIV_MEASURE21
241 ,BIV_MEASURE22
242 ,BIV_MEASURE23
243 ,BIV_MEASURE24
244 ,BIV_MEASURE27
245 ,BIV_MEASURE28
246 ,BIV_MEASURE29
247 ,BIV_MEASURE30 '|| fnd_global.newline;
248
249 l_sel_clause := l_sel_clause || poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
250 , p_col_name => 'BIV_MEASURE5'
251 , p_alias_name => 'BIV_MEASURE5'
252 , p_total_flag => 'N'
253 , p_prefix => NULL
254 , p_suffix => NULL
255 ) || fnd_global.newline ;
256
257 l_sel_clause := l_sel_clause || poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
258 , p_col_name => 'BIV_MEASURE25'
259 , p_alias_name => 'BIV_MEASURE25'
260 , p_total_flag => 'N'
261 , p_prefix => NULL
262 , p_suffix => NULL
263 ) || fnd_global.newline ;
264
265 l_sel_clause := l_sel_clause ||
266 'FROM ( SELECT ' || fnd_global.newline ||
267 p_view_by_col || fnd_global.newline ||
268 ',' || 'NVL(p_ro_count,0) BIV_MEASURE11 ' || fnd_global.newline ||
269 ',' || 'NVL(c_ro_count,0) BIV_MEASURE1 ' || fnd_global.newline ||
270 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_ro_count'
271 ,prior_col => 'p_ro_count'
272 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
273 || 'BIV_MEASURE2' || fnd_global.newline ||
274 ',' || '(p_time_to_repair/(decode(p_ro_count,0,to_number(NULL),p_ro_count))) BIV_MEASURE12 ' || fnd_global.newline ||
275 ',' || '(c_time_to_repair/(decode(c_ro_count,0,to_number(NULL),c_ro_count))) BIV_MEASURE3 ' || fnd_global.newline ||
279 || 'BIV_MEASURE4' || fnd_global.newline ||
276 ',' || poa_dbi_util_pkg.change_clause( cur_col => '(c_time_to_repair/(decode(c_ro_count,0,to_number(NULL),c_ro_count)))'
277 ,prior_col => '(p_time_to_repair/(decode(p_ro_count,0,to_number(NULL),p_ro_count)))'
278 ,change_type => 'P') -- 'P' for Percent ; 'NP' for non percent
280 ',' || 'NVL(c_ro_count_total,0) BIV_MEASURE21 ' || fnd_global.newline ||
281 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_ro_count_total'
282 ,prior_col => 'p_ro_count_total'
283 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
284 || 'BIV_MEASURE22' || fnd_global.newline ||
285 ',' || '(c_time_to_repair_total/(decode(c_ro_count_total,0,to_number(NULL),c_ro_count_total))) BIV_MEASURE23 ' || fnd_global.newline ||
286 ',' || poa_dbi_util_pkg.change_clause( cur_col => '(c_time_to_repair_total/(decode(c_ro_count_total,0,to_number(NULL),c_ro_count_total)))'
287 ,prior_col => '(p_time_to_repair_total/(decode(p_ro_count_total,0,to_number(NULL),p_ro_count_total)))'
288 ,change_type => 'P') -- 'P' for Percent ; 'NP' for non percent
289 || 'BIV_MEASURE24' || fnd_global.newline ||
290 ',' || '(c_time_to_repair/(decode(c_ro_count,0,to_number(NULL),c_ro_count))) BIV_MEASURE27 ' || fnd_global.newline ||
291 ',' || '(p_time_to_repair/(decode(p_ro_count,0,to_number(NULL),p_ro_count))) BIV_MEASURE28 ' || fnd_global.newline ||
292 ',' || '(c_time_to_repair_total/(decode(c_ro_count_total,0,to_number(NULL),c_ro_count_total))) BIV_MEASURE29 ' || fnd_global.newline ||
293 ',' || '(p_time_to_repair_total/(decode(p_ro_count_total,0,to_number(NULL),p_ro_count_total))) BIV_MEASURE30 ' || fnd_global.newline;
294
295 l_sel_clause := l_sel_clause ||
296 poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
297 , p_col_name => 'c_ttr_distribution'
298 , p_alias_name => 'BIV_MEASURE5'
299 , p_total_flag => 'N'
300 , p_prefix => 'NVL('
301 , p_suffix => ',0)'
302 ) || fnd_global.newline ;
303
304 l_sel_clause := l_sel_clause ||
305 poa_dbi_util_pkg.get_bucket_drill_url( p_bucket_rec
306 , 'BIV_ATTRIBUTE1'
307 ,'''pFunctionName=ISC_DEPOT_MTTR_DTL_TBL_REP&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&BIV_ATTRIBUTE1=-1&BIV_DR_BACKLOG_BUCKET+BIV_DR_BACKLOG_BUCKET='
308 , ''''
309 , p_add_bucket_num => 'Y');
310
311 l_sel_clause := l_sel_clause ||
312 poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
313 , p_col_name => 'c_ttr_distribution'
314 , p_alias_name => 'BIV_MEASURE25'
315 , p_total_flag => 'Y'
316 , p_prefix => 'NVL('
317 , p_suffix => ',0)'
318 ) || fnd_global.newline ;
319
320
321
322 RETURN l_sel_clause;
323
324 END GET_MTTR_TBL_SEL_CLAUSE;
325
326 FUNCTION get_drill_down (p_view_by_dim IN VARCHAR2,p_function_name IN VARCHAR2)
327 RETURN VARCHAR2
328 IS
329 l_drill_down varchar2 (500);
330 BEGIN
331 l_drill_down := 'null';
332 IF (p_view_by_dim = 'ITEM+ENI_ITEM_VBH_CAT') THEN
333 l_drill_down := 'decode(v.leaf_node_flag, ''Y'',
334 ''pFunctionName='|| p_function_name ||'&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM&pParamIds=Y'',
335 ''pFunctionName='|| p_function_name ||'&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'') ';
336 END IF;
337 RETURN l_drill_down;
338 END get_drill_down ;
339
340 -- MEAN TIME TO REPAIR TREND REPORT
341
342 PROCEDURE GET_MTTR_TRD_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
343 x_custom_sql OUT NOCOPY VARCHAR2,
344 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
345 IS
346
347 l_query ISC_DEPOT_RPT_UTIL_PKG .g_query_typ%type;
348 l_view_by ISC_DEPOT_RPT_UTIL_PKG .g_view_by_typ%type;
349 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG .g_view_by_col_typ%type;
350 l_xtd ISC_DEPOT_RPT_UTIL_PKG .g_xtd_typ%type;
351 l_comparison_type VARCHAR2(1);
352 l_cur_suffix VARCHAR2(2);
353 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
354 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
355 l_where_clause ISC_DEPOT_RPT_UTIL_PKG .g_where_clause_typ%type;
356 l_mv ISC_DEPOT_RPT_UTIL_PKG .g_mv_typ%type;
357 l_mv_set VARCHAR2(50);
358 l_aggregation_flag NUMBER;
359 l_custom_rec BIS_QUERY_ATTRIBUTES;
360 l_mv_type VARCHAR2(10);
361 l_err_stage VARCHAR2(32767);
362 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
363 l_debug_mode VARCHAR2(1);
364 l_module_name ISC_DEPOT_RPT_UTIL_PKG .g_module_name_typ%type;
365
366 BEGIN
367 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
368 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
369
370 -- clear out the tables.
371 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
375 ISC_DEPOT_RPT_UTIL_PKG .process_parameters (p_param => p_param,
372 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
373
374 -- get all the query parameters
376 x_view_by => l_view_by,
377 x_view_by_col_name => l_view_by_col,
378 x_comparison_type => l_comparison_type,
379 x_xtd => l_xtd,
380 x_cur_suffix => l_cur_suffix,
381 x_where_clause => l_where_clause,
382 x_mv => l_mv,
383 x_join_tbl => l_join_tbl,
384 x_mv_type => l_mv_type,
385 x_aggregation_flag => l_aggregation_flag,
386 p_trend => 'Y',
387 p_mv_set => 'MTTR',
388 x_custom_output => x_custom_output);
389
390 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
391 l_err_stage:='After calling DR_DBI_RPT_UTIL_PKG.process_parameters';
392 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_MTTR_TRD : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
393 END IF;
394
395 -- Add measure columns that need to be aggregated
396 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
397 p_col_name => 'ro_count' ,
398 p_alias_name => 'ro_count',
399 p_grand_total => 'N',
400 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
401 p_to_date_type => 'XTD');
402
403 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
404 p_col_name => 'time_to_repair' ,
405 p_alias_name => 'time_to_repair',
406 p_grand_total => 'N',
407 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
408 p_to_date_type => 'XTD');
409
410 poa_dbi_util_pkg.add_bucket_columns(p_short_name => 'ISC_DEPOT_MTTR'
411 , p_col_tbl => l_col_tbl
412 , p_col_name => 'time_to_repair'
413 , p_alias_name => 'ttr_distribution'
414 , p_grand_total => 'N'
415 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
416 , p_to_date_type => 'XTD'
417 , x_bucket_rec => l_bucket_rec
418 );
419
420
421 l_query := GET_MTTR_TRD_SEL_CLAUSE (l_view_by,l_bucket_rec)
422 || ' from
423 ' || poa_dbi_template_pkg.trend_sql(p_xtd => l_xtd,
424 p_comparison_type => l_comparison_type,
425 p_fact_name => l_mv,
426 p_where_clause => l_where_clause,
427 p_col_name => l_col_tbl,
428 p_use_grpid => 'N',
429 p_in_join_tables => NULL);
430
431 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
432 l_err_stage:='The query is : ' || l_query;
433 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_MTTR_TRD : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
434 END IF;
435
436 -- prepare output for bind variables
437 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
438 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
439
440 -- set the basic bind variables for the trend SQL
441 poa_dbi_util_pkg.get_custom_trend_binds (p_xtd => l_xtd,
442 p_comparison_type => l_comparison_type,
443 x_custom_output => x_custom_output);
444
445 -- Passing AGGREGATION_LEVEL_FLAG to PMV
446 l_custom_rec.attribute_name := ':AGGREGATION_FLAG';
447 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
448 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
449 l_custom_rec.attribute_value := l_aggregation_flag;
450 x_custom_output.extend;
451 x_custom_output(x_custom_output.count) := l_custom_rec;
452
453 x_custom_sql := l_query;
454 EXCEPTION
455
456 WHEN OTHERS THEN
457 l_err_stage := SQLERRM;
458 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
459 l_err_stage:='The exception is : ' || l_err_stage;
460 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_MTTR_TRD : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
461 END IF;
462
463 END GET_MTTR_TRD_SQL;
464
465 FUNCTION GET_MTTR_TRD_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_bucket_rec IN bis_bucket_pub.bis_bucket_rec_type)
466 RETURN VARCHAR2
467 IS
468
469 l_sel_clause VARCHAR2(8000);
470 l_view_by_col_name VARCHAR2(120);
471 l_description VARCHAR2(30);
472
473 BEGIN
474
475 l_sel_clause :=
476 'SELECT cal.name VIEWBY ' || fnd_global.newline ||
480 ,prior_col => 'iset.p_ro_count'
477 ',' || 'NVL(iset.p_ro_count,0) BIV_MEASURE11 ' || fnd_global.newline ||
478 ',' || 'NVL(iset.c_ro_count,0) BIV_MEASURE1 ' || fnd_global.newline ||
479 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'iset.c_ro_count'
481 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
482 || 'BIV_MEASURE2' || fnd_global.newline ||
483 ',' || '(iset.p_time_to_repair/(decode(iset.p_ro_count,0,to_number(NULL),iset.p_ro_count))) BIV_MEASURE12 ' || fnd_global.newline ||
484 ',' || '(iset.c_time_to_repair/(decode(iset.c_ro_count,0,to_number(NULL),iset.c_ro_count))) BIV_MEASURE3 ' || fnd_global.newline ||
485 ',' || poa_dbi_util_pkg.change_clause( cur_col => '(iset.c_time_to_repair/(decode(iset.c_ro_count,0,to_number(NULL),iset.c_ro_count)))'
486 ,prior_col => '(iset.p_time_to_repair/(decode(iset.p_ro_count,0,to_number(NULL),iset.p_ro_count)))'
487 ,change_type => 'P') -- 'P' for Percent ; 'NP' for non percent
488 || 'BIV_MEASURE4' || fnd_global.newline;
489
490 l_sel_clause := l_sel_clause ||
491 poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
492 , p_col_name => 'iset.c_ttr_distribution'
493 , p_alias_name => 'BIV_MEASURE5'
494 , p_total_flag => 'N'
495 , p_prefix => 'NVL('
496 , p_suffix => ',0)'
497 ) || fnd_global.newline ;
498
499 RETURN l_sel_clause;
500
501 END GET_MTTR_TRD_SEL_CLAUSE;
502
503 -- MEAN TIME TO REPAIR DETAIL REPORT
504
505 PROCEDURE GET_MTTR_DTL_TBL_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
506 x_custom_sql OUT NOCOPY VARCHAR2,
507 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
508 IS
509
510 l_query ISC_DEPOT_RPT_UTIL_PKG .g_query_typ%type;
511 l_view_by ISC_DEPOT_RPT_UTIL_PKG .g_view_by_typ%type;
512 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG .g_view_by_col_typ%type;
513 l_xtd ISC_DEPOT_RPT_UTIL_PKG .g_xtd_typ%type;
514 l_comparison_type VARCHAR2(1);
515 l_cur_suffix VARCHAR2(2);
516 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
517 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
518 l_where_clause ISC_DEPOT_RPT_UTIL_PKG .g_where_clause_typ%type;
519 l_mv ISC_DEPOT_RPT_UTIL_PKG .g_mv_typ%type;
520 l_mv_set VARCHAR2(50);
521 l_aggregation_flag NUMBER;
522 l_custom_rec BIS_QUERY_ATTRIBUTES;
523 l_mv_type VARCHAR2(10);
524 l_err_stage VARCHAR2(32767);
525 l_debug_mode VARCHAR2(1);
526 l_module_name ISC_DEPOT_RPT_UTIL_PKG .g_module_name_typ%type;
527
528 BEGIN
529 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
530 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
531
532 -- clear out the tables.
533 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
534 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
535
536 -- get all the query parameters
537 ISC_DEPOT_RPT_UTIL_PKG .process_parameters (p_param => p_param,
538 x_view_by => l_view_by,
539 x_view_by_col_name => l_view_by_col,
540 x_comparison_type => l_comparison_type,
541 x_xtd => l_xtd,
542 x_cur_suffix => l_cur_suffix,
543 x_where_clause => l_where_clause,
544 x_mv => l_mv,
545 x_join_tbl => l_join_tbl,
546 x_mv_type => l_mv_type,
547 x_aggregation_flag => l_aggregation_flag,
548 p_trend => 'N',
549 p_mv_set => 'MDTL',
550 x_custom_output => x_custom_output);
551
552 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
553 l_err_stage:='After calling DR_DBI_RPT_UTIL_PKG.process_parameters';
554 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_MTTR_DTL_TBL : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
555 END IF;
556
557 l_query := GET_MTTR_DTL_SEL_CLAUSE(l_mv,l_where_clause);
558
559 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
560 l_err_stage:='The query is : ' || l_query;
561 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_MTTR_DTL_TBL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
562 END IF;
563
564 x_custom_sql := l_query;
565
566 EXCEPTION
567
568 WHEN OTHERS THEN
569 l_err_stage := SQLERRM;
570 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
571 l_err_stage:='The exception is : ' || l_err_stage;
572 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_MTTR_DTL_TBL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
573 END IF;
574
575 END GET_MTTR_DTL_TBL_SQL;
576
580
577 FUNCTION GET_MTTR_DTL_SEL_CLAUSE(l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type ,l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type)
578 RETURN VARCHAR2
579 IS
581 l_sel_clause VARCHAR2(8000);
582
583 BEGIN
584
585 l_sel_clause :=
586 'SELECT
587 BIV_ATTRIBUTE1
588 ,BIV_ATTRIBUTE2
589 ,BIV_ATTRIBUTE3
590 ,BIV_ATTRIBUTE4
591 ,BIV_ATTRIBUTE5
592 ,BIV_ATTRIBUTE6
593 ,BIV_MEASURE1
594 ,BIV_ATTRIBUTE7
595 ,BIV_ATTRIBUTE8
596 ,BIV_DATE1
597 ,BIV_DATE2
598 ,BIV_DATE3
599 ,BIV_MEASURE2
600 ,BIV_MEASURE3
601 ,BIV_MEASURE4
602 ,''pFunctionName=CSD_RO_DETAILS&csdInvOrgId=''||BIV_MEASURE4||''&csdRepairLineId=''||BIV_MEASURE3 BIV_DYNAMIC_URL1
603 ,' || ISC_DEPOT_RPT_UTIL_PKG.get_service_request_url || '|| BIV_ATTRIBUTE8 BIV_DYNAMIC_URL2
604 FROM (
605 SELECT
606 rank() over (&ORDER_BY_CLAUSE nulls last,repair_line_id ) - 1 rnk
607 ,BIV_ATTRIBUTE1
608 ,BIV_ATTRIBUTE2
609 ,BIV_ATTRIBUTE3
610 ,BIV_ATTRIBUTE4
611 ,BIV_ATTRIBUTE5
612 ,BIV_ATTRIBUTE6
613 ,BIV_MEASURE1
614 ,BIV_ATTRIBUTE7
615 ,BIV_ATTRIBUTE8
616 ,BIV_DATE1
617 ,BIV_DATE2
618 ,BIV_DATE3
619 ,BIV_MEASURE2
620 ,BIV_MEASURE3
621 ,BIV_MEASURE4
622 FROM ( SELECT fact1.repair_line_id repair_line_id' || fnd_global.newline ||
623 ',' || ' fact.repair_number BIV_ATTRIBUTE1 ' || fnd_global.newline ||
624 ',' || ' fact.incident_number BIV_ATTRIBUTE2 ' || fnd_global.newline ||
625 ',' || ' crt.name BIV_ATTRIBUTE3 ' || fnd_global.newline ||
626 ',' || ' eiov.value BIV_ATTRIBUTE4 ' || fnd_global.newline ||
627 ',' || ' eiov.description BIV_ATTRIBUTE5 ' || fnd_global.newline ||
628 ',' || ' mum.unit_of_measure BIV_ATTRIBUTE6 ' || fnd_global.newline ||
629 ',' || ' fact.incident_id BIV_ATTRIBUTE8 ' || fnd_global.newline ||
630 ',' || ' fact.repair_line_id BIV_MEASURE3 ' || fnd_global.newline ||
631 ',' || ' fact.master_organization_id BIV_MEASURE4 ' || fnd_global.newline ||
632 ',' || ' fact.quantity BIV_MEASURE1 ' || fnd_global.newline ||
633 ',' || ' fact.serial_number BIV_ATTRIBUTE7 ' || fnd_global.newline ||
634 ',' || ' fact.promise_date BIV_DATE1 ' || fnd_global.newline ||
635 ',' || ' trunc(fact1.repair_start_date) BIV_DATE2 ' || fnd_global.newline ||
636 ',' || ' trunc(fact1.repair_end_date) BIV_DATE3 ' || fnd_global.newline ||
637 ',' || ' fact1.time_to_repair BIV_MEASURE2 ' || fnd_global.newline ||
638 ' FROM '|| fnd_global.newline || l_mv ||
639 ' ISC_DR_REPAIR_ORDERS_F fact' || fnd_global.newline ||
640 ',' || ' ISC_DR_MTTR_F fact1' || fnd_global.newline ||
641 ',' || ' MTL_UNITS_OF_MEASURE_VL mum' || fnd_global.newline ||
642 ',' || ' ENI_ITEM_V eiov' || fnd_global.newline ||
643 ' WHERE fact.dbi_date_closed BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE ' || fnd_global.newline ||
644 ' AND fact.item_org_id = eiov.id' || fnd_global.newline ||
645 ' AND fact.repair_line_id = fact1.repair_line_id' || fnd_global.newline ||
646 ' AND fact1.time_to_repair >= 0' || fnd_global.newline ||
647 ' AND mum.uom_code = fact.uom_code' || fnd_global.newline || l_where_clause
648 || ' ) ) where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
649 ORDER BY rnk' ;
650
651 RETURN l_sel_clause;
652
653 END GET_MTTR_DTL_SEL_CLAUSE;
654
655 -- MEAN TIME TO REPAIR DISTRIBUTION STATUS REPORT
656
657 PROCEDURE GET_MTTR_DIST_TBL_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
658 x_custom_sql OUT NOCOPY VARCHAR2,
659 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
660 IS
661 l_query ISC_DEPOT_RPT_UTIL_PKG .g_query_typ%type;
662 l_view_by ISC_DEPOT_RPT_UTIL_PKG .g_view_by_typ%type;
663 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG .g_view_by_col_typ%type;
664 l_xtd ISC_DEPOT_RPT_UTIL_PKG .g_xtd_typ%type;
665 l_comparison_type VARCHAR2(1);
666 l_cur_suffix VARCHAR2(2);
667 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
668 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
669 l_where_clause ISC_DEPOT_RPT_UTIL_PKG .g_where_clause_typ%type;
670 l_mv ISC_DEPOT_RPT_UTIL_PKG .g_mv_typ%type;
671 l_mv_set VARCHAR2(50);
672 l_aggregation_flag NUMBER;
673 l_custom_rec BIS_QUERY_ATTRIBUTES;
674 l_mv_type VARCHAR2(10);
675 l_err_stage VARCHAR2(32767);
676 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
677 l_debug_mode VARCHAR2(1);
678 l_module_name ISC_DEPOT_RPT_UTIL_PKG .g_module_name_typ%type;
679
680 BEGIN
681 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
682 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
683
684 -- clear out the tables.
685 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
686 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
687
688 -- get all the query parameters
689 ISC_DEPOT_RPT_UTIL_PKG .process_parameters ( p_param => p_param,
690 x_view_by => l_view_by,
691 x_view_by_col_name => l_view_by_col,
692 x_comparison_type => l_comparison_type,
693 x_xtd => l_xtd,
697 x_join_tbl => l_join_tbl,
694 x_cur_suffix => l_cur_suffix,
695 x_where_clause => l_where_clause,
696 x_mv => l_mv,
698 x_mv_type => l_mv_type,
699 x_aggregation_flag => l_aggregation_flag,
700 p_trend => 'N',
701 p_mv_set => 'MTTR',
702 x_custom_output => x_custom_output);
703
704 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
705 l_err_stage:='After calling DR_DBI_RPT_UTIL_PKG.process_parameters';
706 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_MTTR_DIST_TBL : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
707 END IF;
708
709 -- Add measure columns that need to be aggregated
710 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
711 p_col_name => 'ro_count' ,
712 p_alias_name => 'ro_count',
713 p_grand_total => 'Y',
714 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
715 p_to_date_type => 'XTD');
716
717 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
718 p_col_name => 'time_to_repair' ,
719 p_alias_name => 'time_to_repair',
720 p_grand_total => 'Y',
721 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
722 p_to_date_type => 'XTD');
723
724 poa_dbi_util_pkg.add_bucket_columns(p_short_name => 'ISC_DEPOT_MTTR'
725 , p_col_tbl => l_col_tbl
726 , p_col_name => 'time_to_repair'
727 , p_alias_name => 'ttr_distribution'
728 , p_grand_total => 'Y'
729 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
730 , p_to_date_type => 'XTD'
731 , x_bucket_rec => l_bucket_rec
732 );
733
734 l_query := GET_MTTR_DIST_TBL_SEL_CLAUSE (l_view_by,l_view_by_col,l_bucket_rec)
735 || ' from
736 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
737 p_where_clause => l_where_clause,
738 p_join_tables => l_join_tbl,
739 p_use_windowing => 'Y',
740 p_col_name => l_col_tbl,
741 p_use_grpid => 'N',
742 p_paren_count => 3,
743 p_filter_where => NULL,
744 p_generate_viewby => 'Y',
745 p_in_join_tables => NULL);
746
747 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
748 l_err_stage:='The query is : ' || l_query;
749 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_MTTR_DIST_TBL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
750 END IF;
751
752 -- prepare output for bind variables
753 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
754 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
755
756 -- set the basic bind variables for the status SQL
757 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
758
759 -- Passing AGGREGATION_LEVEL_FLAG to PMV
760 l_custom_rec.attribute_name := ':AGGREGATION_FLAG';
761 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
762 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
763 l_custom_rec.attribute_value := l_aggregation_flag;
764 x_custom_output.extend;
765 x_custom_output(x_custom_output.count) := l_custom_rec;
766
767 x_custom_sql := l_query;
768
769 EXCEPTION
770
771 WHEN OTHERS THEN
772 l_err_stage := SQLERRM;
773 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
774 l_err_stage:='The exception is : ';
775 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_MTTR_DIST_TBL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
776 END IF;
777
778 END GET_MTTR_DIST_TBL_SQL;
779
780 FUNCTION GET_MTTR_DIST_TBL_SEL_CLAUSE(p_view_by_dim IN VARCHAR2,p_view_by_col IN VARCHAR2,p_bucket_rec IN bis_bucket_pub.bis_bucket_rec_type)
781 RETURN VARCHAR2
782 IS
783 l_sel_clause VARCHAR2(8000);
784 l_view_by_col_name VARCHAR2(120);
785 l_description VARCHAR2(30);
786 l_drill_down_rep_1 VARCHAR2(500);
787
788 BEGIN
789
790 l_description := 'null';
791 l_drill_down_rep_1 := 'null';
792
793 -- Item Description for item view by
794 IF (p_view_by_dim = 'ITEM+ENI_ITEM') THEN
795 l_description := ' v. description ';
796 END IF;
797
798 l_drill_down_rep_1 := get_drill_down (p_view_by_dim => p_view_by_dim, p_function_name =>'ISC_DEPOT_MTTR_DIST_TBL_REP');
799
800 l_sel_clause :=
804 ,BIV_MEASURE1
801 'SELECT '|| ISC_DEPOT_RPT_UTIL_PKG.get_viewby_select_clause (p_view_by_dim) || fnd_global.newline ||
802 l_description || ' BIV_ATTRIBUTE10
803 ,BIV_MEASURE11
805 ,BIV_MEASURE2
806 ,BIV_MEASURE12
807 ,BIV_MEASURE3
808 ,BIV_MEASURE4
809 ,BIV_MEASURE21
810 ,BIV_MEASURE22
811 ,BIV_MEASURE23
812 ,BIV_MEASURE24';
813
814 l_sel_clause := l_sel_clause || poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
815 , p_col_name => 'BIV_MEASURE5'
816 , p_alias_name => 'BIV_MEASURE5'
817 , p_total_flag => 'N'
818 , p_prefix => NULL
819 , p_suffix => NULL
820 ) || fnd_global.newline ;
821
822 l_sel_clause := l_sel_clause || poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
823 , p_col_name => 'BIV_MEASURE25'
824 , p_alias_name => 'BIV_MEASURE25'
825 , p_total_flag => 'N'
826 , p_prefix => NULL
827 , p_suffix => NULL
828 ) || fnd_global.newline ;
829 l_sel_clause := l_sel_clause || ' ,'||l_drill_down_rep_1||' BIV_DYNAMIC_URL1' || fnd_global.newline;
830
831 l_sel_clause := l_sel_clause ||
832 'FROM ( SELECT rank() over (&ORDER_BY_CLAUSE'||' nulls last, '||p_view_by_col||' ) - 1 rnk
833 ,'||p_view_by_col||'
834 ,BIV_MEASURE11
835 ,BIV_MEASURE1
836 ,BIV_MEASURE2
837 ,BIV_MEASURE12
838 ,BIV_MEASURE3
839 ,BIV_MEASURE4
840 ,BIV_MEASURE21
841 ,BIV_MEASURE22
842 ,BIV_MEASURE23
843 ,BIV_MEASURE24';
844
845 l_sel_clause := l_sel_clause || poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
846 , p_col_name => 'BIV_MEASURE5'
847 , p_alias_name => 'BIV_MEASURE5'
848 , p_total_flag => 'N'
849 , p_prefix => NULL
850 , p_suffix => NULL
851 ) || fnd_global.newline ;
852
853 l_sel_clause := l_sel_clause || poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
854 , p_col_name => 'BIV_MEASURE25'
855 , p_alias_name => 'BIV_MEASURE25'
856 , p_total_flag => 'N'
857 , p_prefix => NULL
858 , p_suffix => NULL
859 ) || fnd_global.newline ;
860
861 l_sel_clause := l_sel_clause || 'FROM ( SELECT ' || fnd_global.newline ||
862 p_view_by_col || fnd_global.newline ||
863 ',' || 'NVL(p_ro_count,0) BIV_MEASURE11 ' || fnd_global.newline ||
864 ',' || 'NVL(c_ro_count,0) BIV_MEASURE1 ' || fnd_global.newline ||
865 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_ro_count'
866 ,prior_col => 'p_ro_count'
867 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
868 || 'BIV_MEASURE2' || fnd_global.newline ||
869 ',' || '(p_time_to_repair/(decode(p_ro_count,0,to_number(NULL),p_ro_count))) BIV_MEASURE12 ' || fnd_global.newline ||
870 ',' || '(c_time_to_repair/(decode(c_ro_count,0,to_number(NULL),c_ro_count))) BIV_MEASURE3 ' || fnd_global.newline ||
871 ',' || poa_dbi_util_pkg.change_clause( cur_col => '(c_time_to_repair/(decode(c_ro_count,0,to_number(NULL),c_ro_count)))'
872 ,prior_col => '(p_time_to_repair/(decode(p_ro_count,0,to_number(NULL),p_ro_count)))'
873 ,change_type => 'P') -- 'P' for Percent ; 'NP' for non percent
874 || 'BIV_MEASURE4' || fnd_global.newline ||
875 ',' || 'NVL(c_ro_count_total,0) BIV_MEASURE21 ' || fnd_global.newline ||
876 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_ro_count_total'
877 ,prior_col => 'p_ro_count_total'
878 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
879 || 'BIV_MEASURE22' || fnd_global.newline ||
880 ',' || '(c_time_to_repair_total/(decode(c_ro_count_total,0,to_number(NULL),c_ro_count_total))) BIV_MEASURE23 ' || fnd_global.newline ||
881 ',' || poa_dbi_util_pkg.change_clause( cur_col => '(c_time_to_repair_total/(decode(c_ro_count_total,0,to_number(NULL),c_ro_count_total)))'
882 ,prior_col => '(p_time_to_repair_total/(decode(p_ro_count_total,0,to_number(NULL),p_ro_count_total)))'
883 ,change_type => 'P') -- 'P' for Percent ; 'NP' for non percent
887 poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
884 || 'BIV_MEASURE24' || fnd_global.newline;
885
886 l_sel_clause := l_sel_clause ||
888 , p_col_name => '(100/(decode(c_ro_count,0,to_number(NULL),c_ro_count))) * c_ttr_distribution'
889 , p_alias_name => 'BIV_MEASURE5'
890 , p_total_flag => 'N'
891 , p_prefix => NULL
892 , p_suffix => NULL
893 ) || fnd_global.newline ;
894
895 l_sel_clause := l_sel_clause ||
896 poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
897 , p_col_name => '(100/(decode(c_ro_count_total,0,to_number(NULL),c_ro_count_total))) * c_ttr_distribution'
898 , p_alias_name => 'BIV_MEASURE25'
899 , p_total_flag => 'Y'
900 , p_prefix => NULL
901 , p_suffix => NULL
902 ) || fnd_global.newline ;
903
904 RETURN l_sel_clause;
905
906 END GET_MTTR_DIST_TBL_SEL_CLAUSE;
907
908 -- MEAN TIME TO REPAIR DISTRIBUTION TREND REPORT
909
910 PROCEDURE GET_MTTR_DIST_TRD_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
911 x_custom_sql OUT NOCOPY VARCHAR2,
912 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
913 IS
914
915 l_query ISC_DEPOT_RPT_UTIL_PKG .g_query_typ%type;
916 l_view_by ISC_DEPOT_RPT_UTIL_PKG .g_view_by_typ%type;
917 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG .g_view_by_col_typ%type;
918 l_xtd ISC_DEPOT_RPT_UTIL_PKG .g_xtd_typ%type;
919 l_comparison_type VARCHAR2(1);
920 l_cur_suffix VARCHAR2(2);
921 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
922 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
923 l_where_clause ISC_DEPOT_RPT_UTIL_PKG .g_where_clause_typ%type;
924 l_mv ISC_DEPOT_RPT_UTIL_PKG .g_mv_typ%type;
925 l_mv_set VARCHAR2(50);
926 l_aggregation_flag NUMBER;
927 l_custom_rec BIS_QUERY_ATTRIBUTES;
928 l_mv_type VARCHAR2(10);
929 l_err_stage VARCHAR2(32767);
930 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
931 l_debug_mode VARCHAR2(1);
932 l_module_name ISC_DEPOT_RPT_UTIL_PKG .g_module_name_typ%type;
933
934 BEGIN
935 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
936 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
937
938 -- clear out the tables.
939 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
940 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
941
942 -- get all the query parameters
943 ISC_DEPOT_RPT_UTIL_PKG .process_parameters (p_param => p_param,
944 x_view_by => l_view_by,
945 x_view_by_col_name => l_view_by_col,
946 x_comparison_type => l_comparison_type,
947 x_xtd => l_xtd,
948 x_cur_suffix => l_cur_suffix,
949 x_where_clause => l_where_clause,
950 x_mv => l_mv,
951 x_join_tbl => l_join_tbl,
952 x_mv_type => l_mv_type,
953 x_aggregation_flag => l_aggregation_flag,
954 p_trend => 'Y',
955 p_mv_set => 'MTTR',
956 x_custom_output => x_custom_output);
957
958 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
959 l_err_stage:='After calling DR_DBI_RPT_UTIL_PKG.process_parameters';
960 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_MTTR_DIST_TRD : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
961 END IF;
962
963 -- Add measure columns that need to be aggregated
964 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
965 p_col_name => 'ro_count' ,
966 p_alias_name => 'ro_count',
967 p_grand_total => 'N',
968 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
969 p_to_date_type => 'XTD');
970
971 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
972 p_col_name => 'time_to_repair' ,
973 p_alias_name => 'time_to_repair',
974 p_grand_total => 'N',
975 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
976 p_to_date_type => 'XTD');
977
978 poa_dbi_util_pkg.add_bucket_columns(p_short_name => 'ISC_DEPOT_MTTR'
979 , p_col_tbl => l_col_tbl
980 , p_col_name => 'time_to_repair'
981 , p_alias_name => 'ttr_distribution'
982 , p_grand_total => 'N'
986 );
983 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
984 , p_to_date_type => 'XTD'
985 , x_bucket_rec => l_bucket_rec
987
988 l_query := GET_MTTR_DIST_TRD_SEL_CLAUSE (l_view_by,l_bucket_rec)
989 || ' from
990 ' || poa_dbi_template_pkg.trend_sql(p_xtd => l_xtd,
991 p_comparison_type => l_comparison_type,
992 p_fact_name => l_mv,
993 p_where_clause => l_where_clause,
994 p_col_name => l_col_tbl,
995 p_use_grpid => 'N',
996 p_in_join_tables => NULL);
997
998 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
999 l_err_stage:='The query is : ' || l_query;
1000 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_MTTR_DIST_TRD : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
1001 END IF;
1002
1003 -- prepare output for bind variables
1004 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1005 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1006
1007 -- set the basic bind variables for the trend SQL
1008 poa_dbi_util_pkg.get_custom_trend_binds (p_xtd => l_xtd,
1009 p_comparison_type => l_comparison_type,
1010 x_custom_output => x_custom_output);
1011 -- Passing AGGREGATION_LEVEL_FLAG to PMV
1012 l_custom_rec.attribute_name :=':AGGREGATION_FLAG';
1013 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1014 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1015 l_custom_rec.attribute_value := l_aggregation_flag;
1016 x_custom_output.extend;
1017 x_custom_output(x_custom_output.count) := l_custom_rec;
1018
1019 x_custom_sql := l_query;
1020 EXCEPTION
1021
1022 WHEN OTHERS THEN
1023 l_err_stage := SQLERRM;
1024 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
1025 l_err_stage:='The exception is : ' || l_err_stage;
1026 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_MTTR_DIST_TRD : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
1027 END IF;
1028
1029
1030 END GET_MTTR_DIST_TRD_SQL;
1031
1032 FUNCTION GET_MTTR_DIST_TRD_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_bucket_rec IN bis_bucket_pub.bis_bucket_rec_type)
1033 RETURN VARCHAR2
1034 IS
1035
1036 l_sel_clause VARCHAR2(8000);
1037 l_view_by_col_name VARCHAR2(120);
1038 l_description VARCHAR2(30);
1039
1040 BEGIN
1041 l_sel_clause :=
1042 'SELECT cal.name VIEWBY ' || fnd_global.newline ||
1043 ',' || 'NVL(iset.p_ro_count,0) BIV_MEASURE11 ' || fnd_global.newline ||
1044 ',' || 'NVL(iset.c_ro_count,0) BIV_MEASURE1 ' || fnd_global.newline ||
1045 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'iset.c_ro_count'
1046 ,prior_col => 'iset.p_ro_count'
1047 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
1048 || 'BIV_MEASURE2' || fnd_global.newline ||
1049 ',' || '(iset.p_time_to_repair/(decode(iset.p_ro_count,0,to_number(NULL),iset.p_ro_count))) BIV_MEASURE12 ' || fnd_global.newline ||
1050 ',' || '(iset.c_time_to_repair/(decode(iset.c_ro_count,0,to_number(NULL),iset.c_ro_count))) BIV_MEASURE3 ' || fnd_global.newline ||
1051 ',' || poa_dbi_util_pkg.change_clause( cur_col => '(iset.c_time_to_repair/(decode(iset.c_ro_count,0,to_number(NULL),iset.c_ro_count)))'
1052 ,prior_col => '(iset.p_time_to_repair/(decode(iset.p_ro_count,0,to_number(NULL),iset.p_ro_count)))'
1053 ,change_type => 'P') -- 'P' for Percent ; 'NP' for non percent
1054 || 'BIV_MEASURE4' || fnd_global.newline;
1055
1056 l_sel_clause := l_sel_clause ||
1057 poa_dbi_util_pkg.get_bucket_outer_query( p_bucket_rec => p_bucket_rec
1058 , p_col_name =>'(100/(decode(iset.c_ro_count,0,to_number(NULL),iset.c_ro_count))) * iset.c_ttr_distribution'
1059 , p_alias_name =>'BIV_MEASURE5'
1060 , p_total_flag =>'N'
1061 , p_prefix => NULL
1062 , p_suffix => NULL
1063 ) || fnd_global.newline ;
1064
1065 RETURN l_sel_clause;
1066
1067 END GET_MTTR_DIST_TRD_SEL_CLAUSE;
1068
1069 -- REPAIR ORDER SERVICE CODE SUMMARY REPORT
1070
1071 PROCEDURE GET_SRVC_TBL_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
1072 x_custom_sql OUT NOCOPY VARCHAR2,
1073 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1074 IS
1075 l_query ISC_DEPOT_RPT_UTIL_PKG .g_query_typ%type;
1076 l_view_by ISC_DEPOT_RPT_UTIL_PKG .g_view_by_typ%type;
1077 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG .g_view_by_col_typ%type;
1078 l_xtd ISC_DEPOT_RPT_UTIL_PKG .g_xtd_typ%type;
1079 l_comparison_type VARCHAR2(1);
1080 l_cur_suffix VARCHAR2(2);
1081 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1085 l_mv_set VARCHAR2(50);
1082 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1083 l_where_clause ISC_DEPOT_RPT_UTIL_PKG .g_where_clause_typ%type;
1084 l_mv ISC_DEPOT_RPT_UTIL_PKG .g_mv_typ%type;
1086 l_aggregation_flag NUMBER;
1087 l_custom_rec BIS_QUERY_ATTRIBUTES;
1088 l_mv_type VARCHAR2(10);
1089 l_err_stage VARCHAR2(32767);
1090 l_debug_mode VARCHAR2(1);
1091 l_module_name ISC_DEPOT_RPT_UTIL_PKG .g_module_name_typ%type;
1092
1093 BEGIN
1094 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1095 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1096
1097 -- clear out the tables.
1098 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
1099 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
1100
1101 -- get all the query parameters
1102 ISC_DEPOT_RPT_UTIL_PKG .process_parameters (p_param => p_param,
1103 x_view_by => l_view_by,
1104 x_view_by_col_name => l_view_by_col,
1105 x_comparison_type => l_comparison_type,
1106 x_xtd => l_xtd,
1107 x_cur_suffix => l_cur_suffix,
1108 x_where_clause => l_where_clause,
1109 x_mv => l_mv,
1110 x_join_tbl => l_join_tbl,
1111 x_mv_type => l_mv_type,
1112 x_aggregation_flag => l_aggregation_flag,
1113 p_trend => 'N',
1114 p_mv_set => 'SRVC',
1115 x_custom_output => x_custom_output);
1116
1117 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
1118 l_err_stage:='After calling DR_DBI_RPT_UTIL_PKG.process_parameters';
1119 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_SRVC_TBL : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
1120 END IF;
1121
1122 -- Add measure columns that need to be aggregated
1123 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1124 p_col_name => 'ro_count',
1125 p_alias_name => 'ro_count',
1126 p_grand_total => 'Y',
1127 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1128 p_to_date_type => 'XTD');
1129
1130 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
1131 l_err_stage:='After initializing';
1132 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_SRVC_TBL : ' ,l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
1133 END IF;
1134
1135
1136 l_query := GET_SRVC_TBL_SEL_CLAUSE(l_view_by,l_view_by_col)
1137 || ' from
1138 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
1139 p_where_clause => l_where_clause,
1140 p_join_tables => l_join_tbl,
1141 p_use_windowing => 'Y',
1142 p_col_name => l_col_tbl,
1143 p_use_grpid => 'N',
1144 p_paren_count => 3,
1145 p_filter_where => NULL,
1146 p_generate_viewby => 'Y',
1147 p_in_join_tables => NULL);
1148
1149 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
1150 l_err_stage:='The query is : ' || l_query;
1151 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_SRVC_TBL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
1152 END IF;
1153
1154 -- prepare output for bind variables
1155 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1156 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1157
1158 -- set the basic bind variables for the status SQL
1159 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
1160
1161 -- Passing AGGREGATION_LEVEL_FLAG to PMV
1162 l_custom_rec.attribute_name := ':AGGREGATION_FLAG';
1163 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1164 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1165 l_custom_rec.attribute_value := l_aggregation_flag;
1166 x_custom_output.extend;
1167 x_custom_output(x_custom_output.count) := l_custom_rec;
1168 x_custom_sql := l_query;
1169
1170 EXCEPTION
1171
1172 WHEN OTHERS THEN
1173 l_err_stage := SQLERRM;
1174 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
1175 l_err_stage:='The exception is : ' ||l_err_stage;
1176 ISC_DEPOT_RPT_UTIL_PKG .write('BIS_ISC_DEPOT_SRVC_TBL : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG .C_DEBUG_LEVEL);
1177 END IF;
1178
1179 END GET_SRVC_TBL_SQL;
1180
1181 FUNCTION GET_SRVC_TBL_SEL_CLAUSE(p_view_by_dim VARCHAR2,p_view_by_col IN VARCHAR2)
1182 RETURN VARCHAR2
1183 IS
1184
1185 l_sel_clause VARCHAR2(8000);
1186 l_percent VARCHAR2(500);
1187 l_percent_total VARCHAR2(500);
1188
1189 BEGIN
1190 l_percent := '(c_ro_count/(decode(c_ro_count_total,0,to_number(NULL),c_ro_count_total))) * 100';
1191 l_percent_total := '(c_ro_count_total/(decode(c_ro_count_total,0,to_number(NULL),c_ro_count_total))) * 100';
1192
1193
1194 l_sel_clause :=
1195 'SELECT '|| ISC_DEPOT_RPT_UTIL_PKG.get_viewby_select_clause (p_view_by_dim) || fnd_global.newline ||
1196 ' v.description BIV_ATTRIBUTE6 ' || fnd_global.newline ||
1197 ',BIV_MEASURE11
1198 ,BIV_MEASURE1
1199 ,BIV_MEASURE2
1200 ,BIV_MEASURE12
1201 ,BIV_MEASURE5
1202 ,BIV_MEASURE6
1203 ,BIV_MEASURE7' || fnd_global.newline ||
1204 'FROM ( SELECT
1205 rank() over (&ORDER_BY_CLAUSE'||' nulls last ,'||p_view_by_col||' ) - 1 rnk
1206 ,'||p_view_by_col||'
1207 ,BIV_MEASURE11
1208 ,BIV_MEASURE1
1209 ,BIV_MEASURE2
1210 ,BIV_MEASURE12
1211 ,BIV_MEASURE5
1212 ,BIV_MEASURE6
1213 ,BIV_MEASURE7' || fnd_global.newline ||
1214 'FROM ( SELECT ' || fnd_global.newline ||
1215 p_view_by_col || fnd_global.newline ||
1216 ',' || ' NVL(p_ro_count,0) BIV_MEASURE11' || fnd_global.newline ||
1217 ',' || ' NVL(c_ro_count,0) BIV_MEASURE1 ' || fnd_global.newline ||
1218 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_ro_count'
1219 ,prior_col => 'p_ro_count'
1220 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
1221 || ' BIV_MEASURE2' || fnd_global.newline ||
1222 ',' || l_percent || ' BIV_MEASURE12' || fnd_global.newline ||
1223 ',' || ' NVL(c_ro_count_total,0) BIV_MEASURE5 ' || fnd_global.newline ||
1224 ',' || poa_dbi_util_pkg.change_clause( cur_col => 'c_ro_count_total'
1225 ,prior_col => 'p_ro_count_total'
1226 ,change_type => 'NP') -- 'P' for Percent ; 'NP' for non percent
1227 || ' BIV_MEASURE6' || fnd_global.newline ||
1228 ',' || l_percent_total || ' BIV_MEASURE7' || fnd_global.newline ;
1229
1230 RETURN l_sel_clause;
1231
1232 END GET_SRVC_TBL_SEL_CLAUSE;
1233
1234 END ISC_DEPOT_MTTR_PKG;