[Home] [Help]
PACKAGE BODY: APPS.ISC_FS_TRV_TIM_DIS_VAR_RPT_PKG
Source
1 PACKAGE BODY ISC_FS_TRV_TIM_DIS_VAR_RPT_PKG as
2 /*$Header: iscfstrvvarrptb.pls 120.2 2005/12/13 19:41:24 kreardon noship $ */
3
4
5 g_task_tim_var_tbl_func constant varchar2(100) := 'ISC_FS_TRV_TIM_VAR_TBL_REP';
6 g_task_tim_var_dtr_tbl_func constant varchar2(100) := 'ISC_FS_TRV_TIM_VAR_DTR_TBL_REP';
7 g_task_dis_var_tbl_func constant varchar2(100) := 'ISC_FS_TRV_DIS_VAR_TBL_REP';
8 g_task_dis_var_dtr_tbl_func constant varchar2(100) := 'ISC_FS_TRV_DIS_VAR_DTR_TBL_REP';
9 g_task_tim_dtl_func constant varchar2(100) := 'ISC_FS_TRV_TIM_DIS_RPT_REP';
10
11 function get_fact_mv_name
12 ( p_report_type in varchar2
13 , p_param in bis_pmv_page_parameter_tbl
14 , p_custom_output in out NOCOPY bis_query_attributes_tbl
15 )
16 return varchar2
17 is
18
19 l_resource varchar2(10);
20
21 begin
22
23 if p_report_type = 'TRAVEL_TIME_VAR' then
24
25 return '(
26 select
27 record_type
28 , parent_district_id
29 , district_id
30 , decode( den_record_type, ''GROUP'', to_char(district_id), to_char(district_id) || ''.'' || parent_district_id ) district_id_c
31 , bucket_num
32 , time_id
33 , period_type_id
34 , actual_travel_duration_min tot_trv_dur_min_act
35 , sched_travel_duration_min tot_trv_dur_min_sch
36 , task_duration_count count_dur_task
37 , decode(bucket_num,1,task_duration_count,0) actual_travel_time_var_b1
38 , decode(bucket_num,2,task_duration_count,0) actual_travel_time_var_b2
39 , decode(bucket_num,3,task_duration_count,0) actual_travel_time_var_b3
40 , decode(bucket_num,4,task_duration_count,0) actual_travel_time_var_b4
41 , decode(bucket_num,5,task_duration_count,0) actual_travel_time_var_b5
42 , decode(bucket_num,6,task_duration_count,0) actual_travel_time_var_b6
43 , decode(bucket_num,7,task_duration_count,0) actual_travel_time_var_b7
44 , decode(bucket_num,8,task_duration_count,0) actual_travel_time_var_b8
45 , decode(bucket_num,9,task_duration_count,0) actual_travel_time_var_b9
46 , decode(bucket_num,10,task_duration_count,0) actual_travel_time_var_b10
47 from isc_fs_019_mv
48 )';
49
50 elsif p_report_type = 'TRAVEL_DIST_VAR' then
51
52 return '(
53 select
54 record_type
55 , parent_district_id
56 , district_id
57 , decode( den_record_type, ''GROUP'', to_char(district_id), to_char(district_id) || ''.'' || parent_district_id ) district_id_c
58 , bucket_num
59 , time_id
60 , period_type_id
61 , actual_travel_distance_km * &ISC_FS_DIST_FACTOR tot_trv_dist_act
62 , sched_travel_distance_km * &ISC_FS_DIST_FACTOR tot_trv_dist_sch
63 , task_distance_count count_dist_task
64 , decode(bucket_num,1,task_distance_count,0) actual_travel_dist_var_b1
65 , decode(bucket_num,2,task_distance_count,0) actual_travel_dist_var_b2
66 , decode(bucket_num,3,task_distance_count,0) actual_travel_dist_var_b3
67 , decode(bucket_num,4,task_distance_count,0) actual_travel_dist_var_b4
68 , decode(bucket_num,5,task_distance_count,0) actual_travel_dist_var_b5
69 , decode(bucket_num,6,task_distance_count,0) actual_travel_dist_var_b6
70 , decode(bucket_num,7,task_distance_count,0) actual_travel_dist_var_b7
71 , decode(bucket_num,8,task_distance_count,0) actual_travel_dist_var_b8
72 , decode(bucket_num,9,task_distance_count,0) actual_travel_dist_var_b9
73 , decode(bucket_num,10,task_distance_count,0) actual_travel_dist_var_b10
74 from isc_fs_020_mv
75 )';
76
77 else -- should not happen!!!
78 return '';
79
80 end if;
81
82 end get_fact_mv_name;
83
84 procedure get_time_var_sql
85 ( p_param in bis_pmv_page_parameter_tbl
86 , x_custom_sql out nocopy varchar2
87 , x_custom_output out nocopy bis_query_attributes_tbl
88 )
89 is
90
91 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
92 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
93 l_custom_output bis_query_attributes_tbl;
94 l_curr_suffix varchar2(3);
95 l_where_clause varchar2(10000);
96 l_viewby_select varchar2(400); -- needed to be increased from 200
97 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
98 l_dim_bmap number;
99 l_comparison_type varchar2(200);
100 l_xtd varchar2(200);
101 l_mv varchar2(10000);
102 l_view_by varchar2(200);
103 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
104 l_product varchar2(50);
105 l_stmt varchar2(32700);
106 l_distance varchar2(300);
107 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
108 l_to_date_type varchar2(200);
109 l_drill_across_task varchar2(1000);
110
111 begin
112
113 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
114 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
115
116 isc_fs_rpt_util_pkg.register_dimension_levels
117 ( l_dimension_tbl
118 , l_dim_filter_map
119 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
120 );
121
122 isc_fs_rpt_util_pkg.process_parameters
123 ( p_param => p_param
124 , p_dimension_tbl => l_dimension_tbl
125 , p_dim_filter_map => l_dim_filter_map
126 , p_trend => 'N'
127 , p_custom_output => l_custom_output
128 , x_cur_suffix => l_curr_suffix
129 , x_where_clause => l_where_clause
130 , x_viewby_select => l_viewby_select
131 , x_join_tbl => l_join_tbl
132 , x_dim_bmap => l_dim_bmap
133 , x_comparison_type => l_comparison_type
134 , x_xtd => l_xtd
135 );
136
137 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
138 l_to_date_type := 'XTD';
139 else
140 l_to_date_type := 'RLX';
141 end if;
142
143 l_mv := get_fact_mv_name
144 ( 'TRAVEL_TIME_VAR'
145 , p_param
146 , l_custom_output
147 );
148
149 poa_dbi_util_pkg.add_column
150 ( p_col_tbl => l_col_tbl
151 , p_col_name => 'tot_trv_dur_min_act'
152 , p_alias_name => 'tot_trv_dur_min_act'
153 , p_to_date_type => l_to_date_type
154 );
155
156 poa_dbi_util_pkg.add_column
157 ( p_col_tbl => l_col_tbl
158 , p_col_name => 'tot_trv_dur_min_sch'
159 , p_alias_name => 'tot_trv_dur_min_sch'
160 , p_to_date_type => l_to_date_type
161 );
162
163 poa_dbi_util_pkg.add_column
164 ( p_col_tbl => l_col_tbl
165 , p_col_name => 'count_dur_task'
166 , p_alias_name => 'count_dur_task'
167 , p_to_date_type => l_to_date_type
168 );
169
170 poa_dbi_util_pkg.add_bucket_columns
171 ( p_short_name => 'BIV_FS_TRVL_TIME_VAR'
172 , p_col_tbl => l_col_tbl
173 , p_col_name => 'ACTUAL_TRAVEL_TIME_VAR'
174 , p_alias_name => 'bucket'
175 , p_prior_code => poa_dbi_util_pkg.no_priors
176 , p_to_date_type => l_to_date_type
177 , x_bucket_rec => l_bucket_rec
178 );
179
180 l_view_by := isc_fs_rpt_util_pkg.get_parameter_value
181 ( p_param
182 , 'VIEW_BY'
183 );
184
185 l_stmt := 'select
186 ' || l_viewby_select || '
187 , ' || isc_fs_rpt_util_pkg.get_district_drill_down
188 ( l_view_by
189 , g_task_tim_var_tbl_func
190 , 'ISC_ATTRIBUTE_2'
191 ) || '
192 , ' || isc_fs_rpt_util_pkg.get_detail_drill_down
193 ( p_view_by => l_view_by
194 , p_function_name => g_task_tim_var_dtr_tbl_func
195 , p_check_column_name => 'ISC_MEASURE_4'
196 , p_column_alias => 'ISC_ATTRIBUTE_3'
197 , p_check_resource => 'Y'
198 ) || '
199 , ' || isc_fs_rpt_util_pkg.get_bucket_drill_down
200 ( p_bucket_rec => l_bucket_rec
201 , p_view_by => l_view_by
202 , p_function_name => g_task_tim_dtl_func
203 , p_check_column_name => 'ISC_MEASURE_5'
204 , p_column_alias => 'ISC_ATTRIBUTE_4'
205 , p_extra_params => '&BIV_FS_TRVL_TIME_VAR='
206 , p_check_resource => 'Y'
207 ) || '
208 , ISC_MEASURE_1
209 , ISC_MEASURE_2
210 , ISC_MEASURE_13
211 , ISC_MEASURE_3
212 , ISC_MEASURE_12
213 , ISC_MEASURE_3 ISC_MEASURE_14
214 , ISC_MEASURE_4
215 ' || poa_dbi_util_pkg.get_bucket_outer_query
216 ( p_bucket_rec => l_bucket_rec
217 , p_col_name => 'ISC_MEASURE_5'
218 , p_alias_name => 'ISC_MEASURE_5'
219 , p_prefix => null
220 , p_suffix => null
221 , p_total_flag => 'N'
222 ) || '
223 , ISC_MEASURE_21
224 , ISC_MEASURE_22
225 , case nvl(isc_measure_22,0) * nvl(isc_measure_21,0)
226 when 0 then null
227 else ' || isc_fs_rpt_util_pkg.change_column
228 ( 'abs(isc_measure_22 - isc_measure_21)'
229 , 'prior_diff_total'
230 , null
231 , 'Y'
232 ) || '
233 end ISC_MEASURE_23
234 , ISC_MEASURE_24
235 ' || poa_dbi_util_pkg.get_bucket_outer_query
236 ( p_bucket_rec => l_bucket_rec
237 , p_col_name => 'ISC_MEASURE_25'
238 , p_alias_name => 'ISC_MEASURE_25'
239 , p_prefix => null
240 , p_suffix => null
241 , p_total_flag => 'N'
242 ) || '
243 , abs( isc_measure_22 - isc_measure_21) ISC_MEASURE_26
244 from (
245 select
246 row_number() over(&ORDER_BY_CLAUSE nulls last , '|| isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
247 ,iset.* from ( select * from (
248 select
249 record_type
250 , district_id
251 , district_id_c
252 , ' || isc_fs_rpt_util_pkg.rate_column
253 ( 'c_tot_trv_dur_min_sch'
254 , 'c_count_dur_task'
255 , 'ISC_MEASURE_1'
256 , 'N'
257 ) || '
258 , ' || isc_fs_rpt_util_pkg.rate_column
259 ( 'c_tot_trv_dur_min_act'
260 , 'c_count_dur_task'
261 , 'ISC_MEASURE_2'
262 , 'N'
263 ) || '
264 , ' || isc_fs_rpt_util_pkg.rate_column
265 ( 'abs(p_tot_trv_dur_min_act - p_tot_trv_dur_min_sch)'
266 , 'p_count_dur_task'
267 , 'ISC_MEASURE_13'
268 , 'N'
269 ) || '
270 , ' || isc_fs_rpt_util_pkg.rate_column
271 ( 'abs(c_tot_trv_dur_min_act - c_tot_trv_dur_min_sch)'
272 , 'c_count_dur_task'
273 , 'ISC_MEASURE_3'
274 , 'N'
275 ) || '
276 , case nvl(c_tot_trv_dur_min_act,0) * nvl(c_tot_trv_dur_min_sch,0)
277 when 0 then null
278 else ' || isc_fs_rpt_util_pkg.change_column
279 ( isc_fs_rpt_util_pkg.rate_column
280 ( 'abs(c_tot_trv_dur_min_act - c_tot_trv_dur_min_sch )'
281 , 'c_count_dur_task'
282 , null
283 , 'N'
284 )
285 , isc_fs_rpt_util_pkg.rate_column
286 ( 'abs(p_tot_trv_dur_min_act - p_tot_trv_dur_min_sch )'
287 , 'p_count_dur_task'
288 , null
289 , 'N'
290 )
291 , null
292 , 'Y'
293 ) || '
294 end ISC_MEASURE_12
295 , nvl(c_count_dur_task,0) ISC_MEASURE_4
296 ' || poa_dbi_util_pkg.get_bucket_outer_query
297 ( p_bucket_rec => l_bucket_rec
298 , p_col_name => 'bucket'
299 , p_alias_name => 'ISC_MEASURE_5'
300 , p_prefix => 'c_'
301 , p_suffix => '/abs(decode(nvl(c_count_dur_task,0),0,1,c_count_dur_task))*100'
302 , p_total_flag => 'N'
303 ) ||'
304 , ' || isc_fs_rpt_util_pkg.rate_column
305 ( 'c_tot_trv_dur_min_sch_total'
306 , 'c_count_dur_task_total'
307 , 'ISC_MEASURE_21'
308 , 'N'
309 ) || '
310 , ' || isc_fs_rpt_util_pkg.rate_column
311 ( 'c_tot_trv_dur_min_act_total'
312 , 'c_count_dur_task_total'
313 , 'ISC_MEASURE_22'
314 , 'N'
315 ) || '
316 , ' || isc_fs_rpt_util_pkg.rate_column
317 ( 'abs(p_tot_trv_dur_min_act_total - p_tot_trv_dur_min_sch_total) '
318 , 'p_count_dur_task_total'
319 , 'prior_diff_total'
320 , 'N'
321 ) || '
322 , nvl(c_count_dur_task_total,0) ISC_MEASURE_24
323 ' || poa_dbi_util_pkg.get_bucket_outer_query
324 ( p_bucket_rec => l_bucket_rec
325 , p_col_name => 'bucket'
326 , p_alias_name => 'ISC_MEASURE_25'
327 , p_prefix => 'c_'
328 , p_suffix => '/abs(decode(c_count_dur_task_total,0,null,c_count_dur_task_total))*100'
329 , p_total_flag => 'Y'
330 ) || '
331 from ' || poa_dbi_template_pkg.status_sql
332 ( p_fact_name => l_mv
333 , p_where_clause => l_where_clause
334 , p_join_tables => l_join_tbl
335 , p_use_windowing => 'Y' --'N'
336 , p_col_name => l_col_tbl
337 , p_use_grpid => 'N'
338 , p_paren_count => 3
339 , p_filter_where => '1=1 ) iset '
340 , p_generate_viewby => 'Y'
341 );
342
343 --l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
344
345 poa_dbi_util_pkg.get_custom_rolling_binds
346 ( p_custom_output => l_custom_output
347 , p_xtd => l_xtd
348 );
349
350 isc_fs_rpt_util_pkg.enhance_time_join
351 ( p_query => l_stmt
352 , p_trend => 'N'
353 );
354
355 x_custom_output := l_custom_output;
356 x_custom_sql := l_stmt;
357
358 end get_time_var_sql;
359
360 procedure get_dist_var_sql
361 ( p_param in bis_pmv_page_parameter_tbl
362 , x_custom_sql out nocopy varchar2
363 , x_custom_output out nocopy bis_query_attributes_tbl
364 )
365 is
366
367 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
368 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
369 l_custom_output bis_query_attributes_tbl;
370 l_curr_suffix varchar2(3);
371 l_where_clause varchar2(10000);
372 l_viewby_select varchar2(400); -- needed to be increased from 200
373 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
374 l_dim_bmap number;
375 l_comparison_type varchar2(200);
376 l_xtd varchar2(200);
377 l_mv varchar2(10000);
378 l_view_by varchar2(200);
379 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
380 l_product varchar2(50);
381 l_stmt varchar2(32700);
382 l_distance varchar2(300);
383 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
384 l_to_date_type varchar2(200);
385 l_drill_across_task varchar2(1000);
386
387 begin
388
389 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
390 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
391
392 isc_fs_rpt_util_pkg.register_dimension_levels
393 ( l_dimension_tbl
394 , l_dim_filter_map
395 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
396 );
397
398 isc_fs_rpt_util_pkg.process_parameters
399 ( p_param => p_param
400 , p_dimension_tbl => l_dimension_tbl
401 , p_dim_filter_map => l_dim_filter_map
402 , p_trend => 'N'
403 , p_custom_output => l_custom_output
404 , x_cur_suffix => l_curr_suffix
405 , x_where_clause => l_where_clause
406 , x_viewby_select => l_viewby_select
407 , x_join_tbl => l_join_tbl
408 , x_dim_bmap => l_dim_bmap
409 , x_comparison_type => l_comparison_type
410 , x_xtd => l_xtd
411 , x_uom_suffix => l_distance
412 );
413
414 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
415 l_to_date_type := 'XTD';
416 else
417 l_to_date_type := 'RLX';
418 end if;
419
420 l_mv := get_fact_mv_name
421 ( 'TRAVEL_DIST_VAR'
422 , p_param
423 , l_custom_output
424 );
425
426 poa_dbi_util_pkg.add_column
427 ( p_col_tbl => l_col_tbl
428 , p_col_name => 'tot_trv_dist_act'
429 , p_alias_name => 'tot_trv_dist_act'
430 , p_to_date_type => l_to_date_type
431 );
432
433 poa_dbi_util_pkg.add_column
434 ( p_col_tbl => l_col_tbl
435 , p_col_name => 'tot_trv_dist_sch'
436 , p_alias_name => 'tot_trv_dist_sch'
437 , p_to_date_type => l_to_date_type
438 );
439
440 poa_dbi_util_pkg.add_column
441 ( p_col_tbl => l_col_tbl
442 , p_col_name => 'count_dist_task'
443 , p_alias_name => 'count_dist_task'
444 , p_to_date_type => l_to_date_type
445 );
446
447 poa_dbi_util_pkg.add_bucket_columns
448 ( p_short_name => 'BIV_FS_TRVL_DIST_VAR'
449 , p_col_tbl => l_col_tbl
450 , p_col_name => 'ACTUAL_TRAVEL_DIST_VAR'
451 , p_alias_name => 'bucket'
452 , p_prior_code => poa_dbi_util_pkg.no_priors
453 , p_to_date_type => l_to_date_type
454 , x_bucket_rec => l_bucket_rec
455 );
456
457 l_view_by := isc_fs_rpt_util_pkg.get_parameter_value
458 ( p_param
459 , 'VIEW_BY'
460 );
461
462 l_stmt := 'select
463 ' || l_viewby_select || '
464 , ' || isc_fs_rpt_util_pkg.get_district_drill_down
465 ( l_view_by
466 , g_task_dis_var_tbl_func
467 , 'ISC_ATTRIBUTE_2'
468 ) || '
469 , ' || isc_fs_rpt_util_pkg.get_detail_drill_down
470 ( p_view_by => l_view_by
471 , p_function_name => g_task_dis_var_dtr_tbl_func
472 , p_check_column_name => 'ISC_MEASURE_4'
473 , p_column_alias => 'ISC_ATTRIBUTE_3'
474 , p_check_resource => 'Y'
475 ) || '
476 , ' || isc_fs_rpt_util_pkg.get_bucket_drill_down
477 ( p_bucket_rec => l_bucket_rec
478 , p_view_by => l_view_by
479 , p_function_name => g_task_tim_dtl_func
480 , p_check_column_name => 'ISC_MEASURE_5'
481 , p_column_alias => 'ISC_ATTRIBUTE_4'
482 , p_extra_params => '&BIV_FS_TRVL_DIST_VAR='
483 , p_check_resource => 'Y'
484 ) || '
485 , ISC_MEASURE_1
486 , ISC_MEASURE_2
487 , ISC_MEASURE_13
488 , ISC_MEASURE_3
489 , ISC_MEASURE_12
490 , ISC_MEASURE_3 ISC_MEASURE_14
491 , ISC_MEASURE_4
492 ' || poa_dbi_util_pkg.get_bucket_outer_query
493 ( p_bucket_rec => l_bucket_rec
494 , p_col_name => 'ISC_MEASURE_5'
495 , p_alias_name => 'ISC_MEASURE_5'
496 , p_prefix => null
497 , p_suffix => null
498 , p_total_flag => 'N'
499 ) || '
500 , ISC_MEASURE_21
501 , ISC_MEASURE_22
502 , case nvl(isc_measure_22,0) * nvl(isc_measure_21,0)
503 when 0 then null
504 else ' || isc_fs_rpt_util_pkg.change_column
505 ( 'abs(isc_measure_22 - isc_measure_21)'
506 , 'prior_diff_total'
507 , null
508 , 'Y'
509 ) || '
510 end ISC_MEASURE_23
511 , ISC_MEASURE_24
512 ' || poa_dbi_util_pkg.get_bucket_outer_query
513 ( p_bucket_rec => l_bucket_rec
514 , p_col_name => 'ISC_MEASURE_25'
515 , p_alias_name => 'ISC_MEASURE_25'
516 , p_prefix => null
517 , p_suffix => null
518 , p_total_flag => 'N'
519 ) || '
520 , abs( isc_measure_22 - isc_measure_21 ) ISC_MEASURE_26
521 from (
522 select
523 row_number() over(&ORDER_BY_CLAUSE nulls last , '|| isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
524 , iset.* from ( select * from (
525 select
526 record_type
527 , district_id
528 , district_id_c
529 , ' || isc_fs_rpt_util_pkg.rate_column
530 ( 'c_tot_trv_dist_sch'
531 , 'c_count_dist_task'
532 , 'ISC_MEASURE_1'
533 , 'N'
534 ) || '
535 , ' || isc_fs_rpt_util_pkg.rate_column
536 ( 'c_tot_trv_dist_act'
537 , 'c_count_dist_task'
538 , 'ISC_MEASURE_2'
539 , 'N'
540 ) || '
541 , ' || isc_fs_rpt_util_pkg.rate_column
542 ( 'abs(p_tot_trv_dist_act - p_tot_trv_dist_sch)'
543 , 'p_count_dist_task'
544 , 'ISC_MEASURE_13'
545 , 'N'
546 ) || '
547 , ' || isc_fs_rpt_util_pkg.rate_column
548 ( 'abs(c_tot_trv_dist_act - c_tot_trv_dist_sch)'
549 , 'c_count_dist_task'
550 , 'ISC_MEASURE_3'
551 , 'N'
552 ) || '
553 , case nvl(c_tot_trv_dist_act,0) * nvl(p_tot_trv_dist_sch,0)
554 when 0 then NULL
555 else ' || isc_fs_rpt_util_pkg.change_column
556 ( isc_fs_rpt_util_pkg.rate_column
557 ( 'abs(c_tot_trv_dist_act - c_tot_trv_dist_sch)'
558 , 'c_count_dist_task'
559 , null
560 , 'N'
561 )
562 , isc_fs_rpt_util_pkg.rate_column
563 ( 'abs(p_tot_trv_dist_act - p_tot_trv_dist_sch)'
564 , 'p_count_dist_task'
565 , null
566 , 'N'
567 )
568 , null
569 , 'Y'
570 ) || '
571 end ISC_MEASURE_12
572 , nvl(c_count_dist_task,0) ISC_MEASURE_4
573 ' || poa_dbi_util_pkg.get_bucket_outer_query
574 ( p_bucket_rec => l_bucket_rec
575 , p_col_name => 'bucket'
576 , p_alias_name => 'ISC_MEASURE_5'
577 , p_prefix => 'c_'
578 , p_suffix => '/abs(decode(nvl(c_count_dist_task,0),0,1,c_count_dist_task))*100'
579 , p_total_flag => 'N'
580 ) || '
581 , ' || isc_fs_rpt_util_pkg.rate_column
582 ( 'c_tot_trv_dist_sch_total'
583 , 'c_count_dist_task_total'
584 , 'ISC_MEASURE_21'
585 , 'N'
586 ) || '
587 , ' || isc_fs_rpt_util_pkg.rate_column
588 ( 'c_tot_trv_dist_act_total'
589 , 'c_count_dist_task_total'
590 , 'ISC_MEASURE_22'
591 , 'N'
592 ) || '
593 , ' || isc_fs_rpt_util_pkg.rate_column
594 ( 'abs(p_tot_trv_dist_act_total-p_tot_trv_dist_sch_total)'
595 , 'p_count_dist_task_total'
596 , 'prior_diff_total'
597 , 'N'
598 ) || '
599 , nvl(c_count_dist_task_total,0) ISC_MEASURE_24
600 ' || poa_dbi_util_pkg.get_bucket_outer_query
601 ( p_bucket_rec => l_bucket_rec
602 , p_col_name => 'bucket'
603 , p_alias_name => 'ISC_MEASURE_25'
604 , p_prefix => 'c_'
605 , p_suffix => '/abs(decode(c_count_dist_task_total,0,null,c_count_dist_task_total))*100'
606 , p_total_flag => 'Y'
607 ) || '
608 from ' || poa_dbi_template_pkg.status_sql
609 ( p_fact_name => l_mv
610 , p_where_clause => l_where_clause
611 , p_join_tables => l_join_tbl
612 , p_use_windowing => 'Y' --'N'
613 , p_col_name => l_col_tbl
614 , p_use_grpid => 'N'
615 , p_paren_count => 3
616 , p_filter_where => '1=1 ) iset '
617 , p_generate_viewby => 'Y'
618 );
619
620 --l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
621
622 poa_dbi_util_pkg.get_custom_rolling_binds
623 ( p_custom_output => l_custom_output
624 , p_xtd => l_xtd
625 );
626
627 isc_fs_rpt_util_pkg.enhance_time_join
628 ( p_query => l_stmt
629 , p_trend => 'N'
630 );
631
632 x_custom_output := l_custom_output;
633 x_custom_sql := l_stmt;
634
635 end get_dist_var_sql;
636
637 procedure get_time_var_dtr_sql
638 ( p_param in bis_pmv_page_parameter_tbl
639 , x_custom_sql out nocopy varchar2
640 , x_custom_output out nocopy bis_query_attributes_tbl
641 )
642 is
643
644 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
645 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
646 l_dim_bmap number;
647 l_comparison_type varchar2(200);
648 l_xtd varchar2(200);
649 l_where_clause varchar2(10000);
650 l_mv varchar2(10000);
651 l_stmt varchar2(32767);
652
653 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
654 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
655
656 l_custom_output bis_query_attributes_tbl;
657
658 l_curr_suffix varchar2(2);
659
660 l_rank_order varchar2(100);
661
662 l_viewby_select varchar2(400); -- needed to be increased from 200
663
664 l_orderby varchar2(40);
665 l_to_date_type varchar2(200);
666 l_detail_col_tbl isc_fs_rpt_util_pkg.t_detail_column_tbl;
667
668 begin
669
670 -- clear out the tables.
671 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
672 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
673
674 isc_fs_rpt_util_pkg.register_dimension_levels
675 ( l_dimension_tbl
676 , l_dim_filter_map
677 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
678 , isc_fs_rpt_util_pkg.G_TRVL_TIME_VAR_DISTRIB, 'N'
679 );
680
681 isc_fs_rpt_util_pkg.process_parameters
682 ( p_param => p_param
683 , p_dimension_tbl => l_dimension_tbl
684 , p_dim_filter_map => l_dim_filter_map
685 , p_trend => 'N'
686 , p_custom_output => l_custom_output
687 , x_cur_suffix => l_curr_suffix
688 , x_where_clause => l_where_clause
689 , x_viewby_select => l_viewby_select
690 , x_join_tbl => l_join_tbl
691 , x_dim_bmap => l_dim_bmap
692 , x_comparison_type => l_comparison_type
693 , x_xtd => l_xtd
694 );
695
696 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
697 l_to_date_type := 'XTD';
698 else
699 l_to_date_type := 'RLX';
700 end if;
701
702 l_mv := get_fact_mv_name
703 ( 'TRAVEL_TIME_VAR'
704 , p_param
705 , l_custom_output
706 );
707
708 poa_dbi_util_pkg.add_column
709 ( p_col_tbl => l_col_tbl
710 , p_col_name => 'count_dur_task'
711 , p_alias_name => 'count_dur_task'
712 , p_to_date_type => l_to_date_type
713 );
714
715 l_stmt := 'select
716 ' || l_viewby_select || '
717 , nvl(oset.c_count_dur_task,0) ISC_MEASURE_1
718 , case avg(oset.c_count_dur_task_total) over() * avg(oset.p_count_dur_task_total) over()
719 when 0 then NULL
720 else ' || isc_fs_rpt_util_pkg.change_column
721 ( isc_fs_rpt_util_pkg.rate_column
722 ( 'nvl(oset.c_count_dur_task,0)*100'
723 , 'avg(oset.c_count_dur_task_total) over()'
724 , NULL
725 , 'N'
726 )
727 , isc_fs_rpt_util_pkg.rate_column
728 ( 'nvl(oset.p_count_dur_task,0)*100'
729 , 'avg(oset.p_count_dur_task_total) over()'
730 , NULL
731 , 'N' )
732 , NULL
733 , 'N'
734 ) || '
735 end ISC_MEASURE_3
736 , ' || isc_fs_rpt_util_pkg.rate_column
737 ( 'oset.c_count_dur_task'
738 , 'avg(oset.c_count_dur_task_total) over()'
739 , 'ISC_MEASURE_2'
740 , 'Y' ) || '
741 , nvl(avg(c_count_dur_task_total) over(),0) ISC_MEASURE_4
742 , decode(avg(c_count_dur_task_total) over(),null,null,100) ISC_MEASURE_5
743 , ''pFunctionName=ISC_FS_TRV_TIM_DIS_RPT_REP&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' ISC_ATTRIBUTE_1
744 from
745 ( select * from ( ' || poa_dbi_template_pkg.status_sql
746 ( p_fact_name => l_mv
747 , p_where_clause => l_where_clause
748 , p_join_tables => l_join_tbl
749 , p_use_windowing => 'N'
750 , p_col_name => l_col_tbl
751 , p_use_grpid => 'N'
752 , p_paren_count => 3
753 , p_filter_where => ''
754 , p_generate_viewby => 'Y'
755 );
756
757 l_stmt:= replace( l_stmt
758 , '&ORDER_BY_CLAUSE'
759 , 'ORDER BY NLSSORT(VIEWBYID,''NLS_SORT=BINARY'') ASC '
760 );
761
762 --l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
763
764 poa_dbi_util_pkg.get_custom_rolling_binds
765 ( p_custom_output => l_custom_output
766 , p_xtd => l_xtd
767 );
768
769 isc_fs_rpt_util_pkg.enhance_time_join
770 ( p_query => l_stmt
771 , p_trend => 'N'
772 );
773
774 x_custom_sql := l_stmt;
775 x_custom_output := l_custom_output;
776
777 end get_time_var_dtr_sql;
778
779 procedure get_dist_var_dtr_sql
780 ( p_param in bis_pmv_page_parameter_tbl
781 , x_custom_sql out nocopy varchar2
782 , x_custom_output out nocopy bis_query_attributes_tbl
783 )
784 is
785 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
786 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
787 l_dim_bmap number;
788 l_comparison_type varchar2(200);
789 l_xtd varchar2(200);
790 l_where_clause varchar2(10000);
791 l_mv varchar2(10000);
792 l_stmt varchar2(32767);
793
794 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
795 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
796
797 l_custom_output bis_query_attributes_tbl;
798
799 l_curr_suffix varchar2(2);
800
801 l_rank_order varchar2(100);
802
803 l_viewby_select varchar2(400); -- needed to be increased from 200
804
805 l_orderby varchar2(40);
806 l_to_date_type varchar2(200);
807 l_detail_col_tbl isc_fs_rpt_util_pkg.t_detail_column_tbl;
808
809 begin
810
811 -- clear out the tables.
812 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
813 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
814
815 isc_fs_rpt_util_pkg.register_dimension_levels
816 ( l_dimension_tbl
817 , l_dim_filter_map
818 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
819 , isc_fs_rpt_util_pkg.G_TRVL_DIST_VAR_DISTRIB, 'N'
820 );
821
822 isc_fs_rpt_util_pkg.process_parameters
823 ( p_param => p_param
824 , p_dimension_tbl => l_dimension_tbl
825 , p_dim_filter_map => l_dim_filter_map
826 , p_trend => 'N'
827 , p_custom_output => l_custom_output
828 , x_cur_suffix => l_curr_suffix
829 , x_where_clause => l_where_clause
830 , x_viewby_select => l_viewby_select
831 , x_join_tbl => l_join_tbl
832 , x_dim_bmap => l_dim_bmap
833 , x_comparison_type => l_comparison_type
834 , x_xtd => l_xtd
835 );
836
837 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
838 l_to_date_type := 'XTD';
839 else
840 l_to_date_type := 'RLX';
841 end if;
842
843 l_mv := get_fact_mv_name
844 ( 'TRAVEL_DIST_VAR'
845 , p_param
846 , l_custom_output
847 );
848
849 poa_dbi_util_pkg.add_column
850 ( p_col_tbl => l_col_tbl
851 , p_col_name => 'count_dist_task'
852 , p_alias_name => 'count_dist_task'
853 , p_to_date_type => l_to_date_type
854 );
855
856 l_stmt := 'select
857 ' || l_viewby_select || '
858 , nvl(oset.c_count_dist_task,0) ISC_MEASURE_1
859 , case avg(oset.p_count_dist_task_total) over () * avg(oset.p_count_dist_task_total) over ()
860 when 0 then NULL
861 else ' || isc_fs_rpt_util_pkg.change_column
862 ( isc_fs_rpt_util_pkg.rate_column
863 ( 'nvl(oset.c_count_dist_task,0)*100'
864 , 'avg(oset.c_count_dist_task_total) over()'
865 , NULL
866 , 'N'
867 )
868 , isc_fs_rpt_util_pkg.rate_column
869 ( 'nvl(oset.p_count_dist_task,0)*100'
870 , 'avg(oset.p_count_dist_task_total) over()'
871 , NULL
872 , 'N'
873 )
874 , null
875 , 'N'
876 ) || '
877 end ISC_MEASURE_3
878 , ' || isc_fs_rpt_util_pkg.rate_column
879 ( 'oset.c_count_dist_task'
880 , 'avg(oset.c_count_dist_task_total) over()'
881 , 'ISC_MEASURE_2'
882 ,'Y'
883 ) || '
884 , nvl(avg(c_count_dist_task_total) over(),0) ISC_MEASURE_4
885 , decode (avg(c_count_dist_task_total) over(),null,null,100) ISC_MEASURE_5
886 , ''pFunctionName=ISC_FS_TRV_TIM_DIS_RPT_REP&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' ISC_ATTRIBUTE_1
887 from
888 ( select * from ( ' || poa_dbi_template_pkg.status_sql
889 ( p_fact_name => l_mv
890 , p_where_clause => l_where_clause
891 , p_join_tables => l_join_tbl
892 , p_use_windowing => 'N'
893 , p_col_name => l_col_tbl
894 , p_use_grpid => 'N'
895 , p_paren_count => 3
896 , p_filter_where => ''
897 , p_generate_viewby => 'Y'
898 );
899
900 l_stmt:= replace( l_stmt
901 , '&ORDER_BY_CLAUSE'
902 , 'ORDER BY NLSSORT(VIEWBYID,''NLS_SORT=BINARY'') ASC '
903 );
904
905 poa_dbi_util_pkg.get_custom_rolling_binds
906 ( p_custom_output => l_custom_output
907 , p_xtd => l_xtd
908 );
909
910 --l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
911
912 isc_fs_rpt_util_pkg.enhance_time_join
913 ( p_query => l_stmt
914 , p_trend => 'N'
915 );
916
917 x_custom_sql := l_stmt;
918 x_custom_output := l_custom_output;
919
920 end get_dist_var_dtr_sql;
921
922 end ISC_FS_TRV_TIM_DIS_VAR_RPT_PKG;
923