[Home] [Help]
PACKAGE BODY: APPS.ISC_FS_TRV_TIM_DIS_RPT_PKG
Source
1 PACKAGE BODY ISC_FS_TRV_TIM_DIS_RPT_PKG as
2 /*$Header: iscfstrvrptb.pls 120.2 2006/04/21 04:26:38 nbhamidi noship $ */
3
4 g_task_rep_func constant varchar2(100) := 'ISC_FS_TRV_TIM_DIS_TBL_REP';
5 g_task_tim_dtr_func constant varchar2(100) := 'ISC_FS_TRV_TIM_DTR_TBL_REP';
6 g_task_dis_dtr_func constant varchar2(100) := 'ISC_FS_TRV_DIS_DTR_TBL_REP';
7 g_task_tim_dtl_func constant varchar2(100) := 'ISC_FS_TRV_TIM_DIS_RPT_REP';
8 g_tot_trd_rep_func constant varchar2(100) := 'ISC_FS_TOT_TRV_TIM_DIS_TRD_REP';
9 g_trd_rep_func constant varchar2(100) := 'ISC_FS_TRV_TIM_DIS_TRD_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 begin
20
21 if p_report_type = 'TRAVEL_TIME_DISTANCE' then
22
23 return '(
24 select
25 f.record_type
26 , f.parent_district_id
27 , f.district_id
28 , f.district_id_c
29 , f.time_id
30 , f.period_type_id
31 , f.actual_travel_duration_min tot_trv_dur_min
32 , f.task_dist_count count_dist_task
33 , f.actual_travel_distance_km * &ISC_FS_DIST_FACTOR tot_trv_dist
34 , f.task_duration_count count_dur_task
35 from isc_fs_017_mv f
36 )' ;
37
38 elsif p_report_type = 'TRAVEL_TIME_DISTRIBUTION' then
39
40 return '(
41 select
42 f.record_type
43 , f.parent_district_id
44 , f.district_id
45 , f.district_id_c
46 , f.time_id
47 , f.period_type_id
48 , f.actual_travel_duration_min tot_trv_dur_min
49 , f.task_duration_count count_dur_task
50 , f.actual_travel_duration_min_b1
51 , f.actual_travel_duration_min_b2
52 , f.actual_travel_duration_min_b3
53 , f.actual_travel_duration_min_b4
54 , f.actual_travel_duration_min_b5
55 , f.actual_travel_duration_min_b6
56 , f.actual_travel_duration_min_b7
57 , f.actual_travel_duration_min_b8
58 , f.actual_travel_duration_min_b9
59 , f.actual_travel_duration_min_b10
60 from isc_fs_017_mv f
61 )' ;
62
63 elsif p_report_type = 'TRAVEL_TASK_DETAIL' then
64
65 if isc_fs_rpt_util_pkg.get_parameter_id
66 ( p_param
67 , isc_fs_rpt_util_pkg.G_DISTRICT
68 ) like '%.%' then
69 return ' (
70 select
71 t.task_id
72 , t.task_number
73 , t.task_type_id
74 , t.owner_id
75 , t.owner_type
76 , t.customer_id
77 , decode( t.location_id
78 , null, ''ADDRESS_ID''
79 , ''LOCATION_ID''
80 ) address_type
81 , nvl(t.location_id, t.address_id) address_id
82 , ta.sched_travel_duration_min
83 , ta.actual_travel_duration_min
84 , (( ta.actual_travel_duration_min - ta.sched_travel_duration_min )*100 )/
85 ta.sched_travel_duration_min travel_duration_var
86 , ta.sched_travel_distance_km * &ISC_FS_DIST_FACTOR sched_travel_distance
87 , ta.actual_travel_distance_km * &ISC_FS_DIST_FACTOR actual_travel_distance
88 , ((ta.actual_travel_distance_km - ta.sched_travel_distance_km )*100 )/
89 ta.sched_travel_distance_km travel_dist_var
90 , ta.resource_id assignee_id
91 , ta.resource_type assignee_type
92 , ta.resource_id district_id
93 , ta.district_id parent_district_id
94 , to_char(ta.resource_id) || ''.'' || ta.district_id district_id_c
95 from
96 isc_fs_task_assignmnts_f ta
97 , isc_fs_tasks_f t
98 where
99 t.task_type_rule = ''DISPATCH''
100 and t.task_id = ta.task_id
101 and ta.deleted_flag <> ''Y''
102 and t.source_object_type_code = ''SR''
103 and ( ( ta.actual_travel_duration_min is not null and
104 nvl(ta.sched_travel_duration_min,0) > 0 ) and /* Bug 5169178 */
105 ( ta.actual_travel_distance_km is not null and
106 nvl(ta.sched_travel_distance_km,0) > 0 )
107 )
108 and ta.report_date between &BIS_CURRENT_EFFECTIVE_START_DATE
109 and &BIS_CURRENT_ASOF_DATE
110 )';
111
112 else
113
114 return ' (
115 select
116 t.task_id
117 , t.task_number
118 , t.task_type_id
119 , t.owner_id
120 , t.owner_type
121 , t.customer_id
122 , decode( t.location_id
123 , null, ''ADDRESS_ID''
124 , ''LOCATION_ID''
125 ) address_type
126 , nvl(t.location_id, t.address_id) address_id
127 , ta.sched_travel_duration_min
128 , ta.actual_travel_duration_min
129 , (( ta.actual_travel_duration_min - ta.sched_travel_duration_min )*100 )/
130 ta.sched_travel_duration_min travel_duration_var
131 , ta.sched_travel_distance_km * &ISC_FS_DIST_FACTOR sched_travel_distance
132 , ta.actual_travel_distance_km * &ISC_FS_DIST_FACTOR actual_travel_distance
133 , (( ta.actual_travel_distance_km - ta.sched_travel_distance_km )*100 )/
134 ta.sched_travel_distance_km travel_dist_var
135 , ta.resource_id assignee_id
136 , ta.resource_type assignee_type
137 , den.prg_id district_id
138 , den.parent_prg_id parent_district_id
139 , to_char(ta.resource_id) || ''.'' || ta.district_id district_id_c
140 from
141 isc_fs_task_assignmnts_f ta
142 , isc_fs_tasks_f t
143 , isc_fs_002_mv den
144 where
145 t.task_type_rule = ''DISPATCH''
146 and t.task_id = ta.task_id
147 and ta.deleted_flag <> ''Y''
148 and ta.district_id = den.rg_id
149 and t.source_object_type_code = ''SR''
150 and ( ( ta.actual_travel_duration_min is not null and
151 nvl(ta.sched_travel_duration_min,0) > 0 ) and /* bug 5169178 */
152 ( ta.actual_travel_distance_km is not null and
153 nvl(ta.sched_travel_distance_km,0) > 0 )
154 )
155 and ta.report_date between &BIS_CURRENT_EFFECTIVE_START_DATE
156 and &BIS_CURRENT_ASOF_DATE
157 )';
158 end if;
159
160 elsif p_report_type = 'TRAVEL_DISTANCE_DISTRIBUTION' then
161
162 return '(
163 select
164 f.record_type
165 , f.parent_district_id
166 , f.district_id
167 , f.district_id_c
168 , f.time_id
169 , f.period_type_id
170 , f.actual_travel_distance_km * &ISC_FS_DIST_FACTOR tot_trv_dist
171 , f.task_dist_count count_dist_task
172 , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b1,f.actual_travel_dist_mi_b1) actual_travel_dist_b1
173 , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b2,f.actual_travel_dist_mi_b2) actual_travel_dist_b2
174 , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b3,f.actual_travel_dist_mi_b3) actual_travel_dist_b3
175 , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b4,f.actual_travel_dist_mi_b4) actual_travel_dist_b4
176 , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b5,f.actual_travel_dist_mi_b5) actual_travel_dist_b5
177 , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b6,f.actual_travel_dist_mi_b6) actual_travel_dist_b6
178 , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b7,f.actual_travel_dist_mi_b7) actual_travel_dist_b7
179 , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b8,f.actual_travel_dist_mi_b8) actual_travel_dist_b8
180 , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b9,f.actual_travel_dist_mi_b9) actual_travel_dist_b9
181 , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b10,f.actual_travel_dist_mi_b10) actual_travel_dist_b10
182 from isc_fs_018_mv f
183 )';
184
185 else -- should not happen!!!
186 return '';
187
188 end if;
189
190 end get_fact_mv_name;
191
192 procedure get_tbl_sql
193 ( p_param in bis_pmv_page_parameter_tbl
194 , x_custom_sql out nocopy varchar2
195 , x_custom_output out nocopy bis_query_attributes_tbl
196 )
197 is
198
199 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
200 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
201 l_dim_bmap number;
202 l_comparison_type varchar2(200);
203 l_xtd varchar2(200);
204 l_where_clause varchar2(10000);
205 l_mv varchar2(10000);
206 l_stmt varchar2(32767);
207 l_drill_down varchar2(1000);
208 l_drill_across varchar2 (1000);
209
210 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
211
212 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
213
214 l_custom_output bis_query_attributes_tbl;
215 l_cost_element varchar2(200);
216 l_curr_suffix varchar2(2);
217 l_to_date_type varchar2(100);
218 l_viewby_select varchar2(400); -- needed to be increased from 200
219 l_view_by varchar2(200);
220 l_distance varchar2(100);
221
222 begin
223
224 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
225 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
226
227 isc_fs_rpt_util_pkg.register_dimension_levels
228 ( l_dimension_tbl
229 , l_dim_filter_map
230 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
231 -- , isc_fs_rpt_util_pkg.G_DISTANCE_UOM, 'Y'
232 );
233
234 isc_fs_rpt_util_pkg.process_parameters
235 ( p_param => p_param
236 , p_dimension_tbl => l_dimension_tbl
237 , p_dim_filter_map => l_dim_filter_map
238 , p_trend => 'N'
239 , p_custom_output => l_custom_output
240 , x_cur_suffix => l_curr_suffix
241 , x_where_clause => l_where_clause
242 , x_viewby_select => l_viewby_select
243 , x_join_tbl => l_join_tbl
244 , x_dim_bmap => l_dim_bmap
245 , x_comparison_type => l_comparison_type
246 , x_xtd => l_xtd
247 , x_uom_suffix => l_distance
248 );
249
250 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
251 l_to_date_type := 'XTD';
252 else
253 l_to_date_type := 'RLX';
254 end if;
255
256 poa_dbi_util_pkg.add_column
257 ( p_col_tbl => l_col_tbl
258 , p_col_name => 'tot_trv_dur_min'
259 , p_alias_name => 'tot_trv_dur_min'
260 , p_grand_total => 'Y'
261 , p_prior_code => poa_dbi_util_pkg.both_priors
262 , p_to_date_type => l_to_date_type
263 );
264
265 poa_dbi_util_pkg.add_column
266 ( p_col_tbl => l_col_tbl
267 , p_col_name => 'count_dur_task'
268 , p_alias_name => 'count_dur_task'
269 , p_grand_total => 'Y'
270 , p_prior_code => poa_dbi_util_pkg.both_priors
271 , p_to_date_type => l_to_date_type
272 );
273
274 poa_dbi_util_pkg.add_column
275 ( p_col_tbl => l_col_tbl
276 , p_col_name => 'tot_trv_dist'
277 , p_alias_name => 'tot_trv_dist'
278 , p_grand_total => 'Y'
279 , p_prior_code => poa_dbi_util_pkg.both_priors
280 , p_to_date_type => l_to_date_type
281 );
282
283 poa_dbi_util_pkg.add_column
284 ( p_col_tbl => l_col_tbl
285 , p_col_name => 'count_dist_task'
286 , p_alias_name => 'count_dist_task'
287 , p_grand_total => 'Y'
288 , p_prior_code => poa_dbi_util_pkg.both_priors
289 , p_to_date_type => l_to_date_type
290 );
291
292 l_view_by := isc_fs_rpt_util_pkg.get_parameter_value
293 ( p_param
294 , 'VIEW_BY'
295 );
296
297 l_mv := get_fact_mv_name
298 ( 'TRAVEL_TIME_DISTANCE'
299 , p_param
300 , l_custom_output
301 );
302
303 l_stmt := 'select
304 ' || l_viewby_select || '
305 , ISC_MEASURE_1
306 , ISC_MEASURE_2
307 , ISC_MEASURE_3
308 , ISC_MEASURE_4
309 , ISC_MEASURE_5
310 , ISC_MEASURE_1 ISC_MEASURE_6
311 , ISC_MEASURE_8 ISC_MEASURE_8
312 , ISC_MEASURE_3 ISC_MEASURE_9
313 , ISC_MEASURE_11
314 , ISC_MEASURE_12
315 , ISC_MEASURE_13
316 , ISC_MEASURE_14
317 , ISC_MEASURE_15
318 , ISC_MEASURE_11 ISC_MEASURE_16
319 , ISC_MEASURE_12 ISC_MEASURE_17
320 , ISC_MEASURE_18 ISC_MEASURE_18
321 , ISC_MEASURE_13 ISC_MEASURE_19
322 , ISC_MEASURE_14 ISC_MEASURE_20
323 , ' || isc_fs_rpt_util_pkg.get_district_drill_down
324 ( l_view_by
325 , g_task_rep_func
326 , 'ISC_ATTRIBUTE_4'
327 ) || '
328 from ( select
329 row_number() over(&ORDER_BY_CLAUSE nulls last, ' || isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
330 , iset.*
331 from ( select * from (
332 select
333 record_type
334 , district_id
335 , district_id_c
336 , ' || isc_fs_rpt_util_pkg.rate_column
337 ( 'c_tot_trv_dur_min'
338 , 'c_count_dur_task'
339 , 'ISC_MEASURE_1'
340 , 'N'
341 ) || '
342 , ' || isc_fs_rpt_util_pkg.rate_column
343 ( 'p_tot_trv_dur_min'
344 , 'p_count_dur_task'
345 , 'ISC_MEASURE_5'
346 , 'N'
347 ) || '
348 , ' || isc_fs_rpt_util_pkg.change_column
349 ( isc_fs_rpt_util_pkg.rate_column
350 ( 'c_tot_trv_dur_min'
351 , 'c_count_dur_task'
352 , null
353 , 'N'
354 )
355 , isc_fs_rpt_util_pkg.rate_column
356 ( 'p_tot_trv_dur_min'
357 , 'p_count_dur_task'
358 , null
359 , 'N'
360 )
361 , 'ISC_MEASURE_2'
362 , 'N'
363 ) || '
364 , ' || isc_fs_rpt_util_pkg.rate_column
365 ( 'c_tot_trv_dist'
366 , 'c_count_dist_task'
367 , 'ISC_MEASURE_3'
368 , 'N'
369 ) || '
370 , ' || isc_fs_rpt_util_pkg.rate_column
371 ( 'p_tot_trv_dist'
372 , 'p_count_dist_task'
373 , 'ISC_MEASURE_8'
374 , 'N'
375 ) || '
376 , ' || isc_fs_rpt_util_pkg.change_column
377 ( isc_fs_rpt_util_pkg.rate_column
378 ( 'c_tot_trv_dist'
379 , 'c_count_dist_task'
380 , null
381 , 'N'
382 )
383 , isc_fs_rpt_util_pkg.rate_column
384 ( 'p_tot_trv_dist'
385 , 'p_count_dist_task'
386 , null
387 , 'N'
388 )
389 , 'ISC_MEASURE_4'
390 , 'N'
391 ) || '
392 , ' || isc_fs_rpt_util_pkg.rate_column
393 ( 'c_tot_trv_dur_min_total'
394 , 'c_count_dur_task_total'
395 , 'ISC_MEASURE_11'
396 , 'N'
397 ) || '
398 , ' || isc_fs_rpt_util_pkg.change_column
399 ( isc_fs_rpt_util_pkg.rate_column
400 ( 'c_tot_trv_dur_min_total'
401 , 'c_count_dur_task_total'
402 , null
403 , 'N'
404 )
405 , isc_fs_rpt_util_pkg.rate_column
406 ( 'p_tot_trv_dur_min_total'
407 , 'p_count_dur_task_total'
408 , null
409 , 'N'
410 )
411 , 'ISC_MEASURE_12'
412 , 'N'
413 ) || '
414 , ' || isc_fs_rpt_util_pkg.rate_column
415 ( 'c_tot_trv_dist_total'
416 , 'c_count_dist_task_total'
417 , 'ISC_MEASURE_13'
418 , 'N'
419 ) ||'
420 , ' || isc_fs_rpt_util_pkg.change_column
421 ( isc_fs_rpt_util_pkg.rate_column
422 ( 'c_tot_trv_dist_total'
423 , 'c_count_dist_task_total'
424 , null
425 , 'N'
426 )
427 , isc_fs_rpt_util_pkg.rate_column
428 ( 'p_tot_trv_dist_total'
429 , 'p_count_dist_task_total'
430 , null
431 , 'N'
432 )
433 , 'ISC_MEASURE_14'
434 , 'N'
435 ) || '
436 , p_tot_trv_dur_min_total ISC_MEASURE_15
437 , p_tot_trv_dist_total ISC_MEASURE_18
438 from ' || poa_dbi_template_pkg.status_sql
439 ( p_fact_name => l_mv
440 , p_where_clause => l_where_clause
441 , p_join_tables => l_join_tbl
442 , p_use_windowing => 'Y'
443 , p_col_name => l_col_tbl
444 , p_use_grpid => 'N'
445 , p_paren_count => 3
446 , p_filter_where => '1=1 ) iset '
447 , p_generate_viewby => 'Y'
448 );
449
450 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
451 poa_dbi_util_pkg.get_custom_rolling_binds
452 ( p_custom_output => l_custom_output
453 , p_xtd => l_xtd
454 );
455
456 isc_fs_rpt_util_pkg.enhance_time_join
457 ( p_query => l_stmt
458 , p_trend => 'N'
459 );
460
461 x_custom_output := l_custom_output;
462 x_custom_sql := l_stmt;
463
464 end get_tbl_sql;
465
466 -----start of detailed report ------------------------------------------
467
468 procedure get_dtl_sql
469 ( p_param in bis_pmv_page_parameter_tbl
470 , x_custom_sql out nocopy varchar2
471 , x_custom_output out nocopy bis_query_attributes_tbl
472 )
473 as
474
475 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
476 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
477 l_custom_output bis_query_attributes_tbl;
478 l_curr_suffix varchar2(3);
479 l_where_clause varchar2(10000);
480 l_viewby_select varchar2(400); -- needed to be increased from 200
481 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
482 l_dim_bmap number;
483 l_comparison_type varchar2(200);
484 l_xtd varchar2(200);
485 l_mv varchar2(10000);
486 l_cost_element varchar2(200);
487 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
488 l_stmt varchar2(32767);
489 l_rank_order varchar2(200);
490 l_detail_col_tbl isc_fs_rpt_util_pkg.t_detail_column_tbl;
491 l_order_by varchar2(200);
492 l_asc_desc varchar2(100);
493 l_distance varchar2(100);
494
495 begin
496
497 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
498 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
499
500 isc_fs_rpt_util_pkg.register_dimension_levels
501 ( l_dimension_tbl
502 , l_dim_filter_map
503 , isc_fs_rpt_util_pkg.G_CUSTOMER, 'N'
504 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
505 , isc_fs_rpt_util_pkg.G_TASK_TYPE, 'N'
506 , isc_fs_rpt_util_pkg.G_TASK_OWNER, 'N'
507 , isc_fs_rpt_util_pkg.G_TASK_ADDRESS, 'N'
508 , isc_fs_rpt_util_pkg.G_TASK_ASSIGNEE, 'N'
509 );
510
511 isc_fs_rpt_util_pkg.process_parameters
512 ( p_param => p_param
513 , p_dimension_tbl => l_dimension_tbl
514 , p_dim_filter_map => l_dim_filter_map
515 , p_trend => 'D'
516 , p_custom_output => l_custom_output
517 , x_cur_suffix => l_curr_suffix
518 , x_where_clause => l_where_clause
519 , x_viewby_select => l_viewby_select
520 , x_join_tbl => l_join_tbl
521 , x_dim_bmap => l_dim_bmap
522 , x_comparison_type => l_comparison_type
523 , x_xtd => l_xtd
524 , x_uom_suffix => l_distance
525 );
526
527 l_mv := get_fact_mv_name
528 ( 'TRAVEL_TASK_DETAIL'
529 , p_param
530 , l_custom_output
531 );
532
533 isc_fs_rpt_util_pkg.add_detail_column
534 ( p_detail_col_tbl => l_detail_col_tbl
535 , p_dimension_tbl => l_dimension_tbl
536 , p_fact_col_name => 'task_number'
537 , p_fact_col_total => 'N'
538 , p_column_key => 'task_number'
539 );
540
541 isc_fs_rpt_util_pkg.add_detail_column
542 ( p_detail_col_tbl => l_detail_col_tbl
543 , p_dimension_tbl => l_dimension_tbl
544 , p_fact_col_name => 'task_id'
545 , p_fact_col_total => 'N'
546 , p_column_key => 'task_id'
547 );
548
549 isc_fs_rpt_util_pkg.add_detail_column
550 ( p_detail_col_tbl => l_detail_col_tbl
551 , p_dimension_tbl => l_dimension_tbl
552 , p_dimension_level => isc_fs_rpt_util_pkg.G_TASK_TYPE
553 , p_column_key => 'task_type'
554 );
555
556 isc_fs_rpt_util_pkg.add_detail_column
557 ( p_detail_col_tbl => l_detail_col_tbl
558 , p_dimension_tbl => l_dimension_tbl
559 , p_dimension_level => isc_fs_rpt_util_pkg.G_TASK_OWNER
560 , p_column_key => 'task_owner'
561 );
562
563 isc_fs_rpt_util_pkg.add_detail_column
564 ( p_detail_col_tbl => l_detail_col_tbl
565 , p_dimension_tbl => l_dimension_tbl
566 , p_dimension_level => isc_fs_rpt_util_pkg.G_TASK_ASSIGNEE
567 , p_column_key => 'task_assignee'
568 );
569
570 isc_fs_rpt_util_pkg.add_detail_column
571 ( p_detail_col_tbl => l_detail_col_tbl
572 , p_dimension_tbl => l_dimension_tbl
573 , p_fact_col_name => 'sched_travel_duration_min'
574 , p_fact_col_total => 'N'
575 , p_column_key => 'sched_travel_duration_min'
576 );
577
578 isc_fs_rpt_util_pkg.add_detail_column
579 ( p_detail_col_tbl => l_detail_col_tbl
580 , p_dimension_tbl => l_dimension_tbl
581 , p_fact_col_name => 'actual_travel_duration_min'
582 , p_fact_col_total => 'N'
583 , p_column_key => 'actual_travel_duration_min'
584 );
585
586 isc_fs_rpt_util_pkg.add_detail_column
587 ( p_detail_col_tbl => l_detail_col_tbl
588 , p_dimension_tbl => l_dimension_tbl
589 , p_fact_col_name => 'travel_duration_var'
590 , p_fact_col_total => 'N'
591 , p_column_key => 'travel_duration_var'
592 );
593
594 isc_fs_rpt_util_pkg.add_detail_column
595 ( p_detail_col_tbl => l_detail_col_tbl
596 , p_dimension_tbl => l_dimension_tbl
597 , p_fact_col_name => 'sched_travel_distance'
598 , p_fact_col_total => 'N'
599 , p_column_key => 'sched_travel_distance'
600 );
601
602 isc_fs_rpt_util_pkg.add_detail_column
603 ( p_detail_col_tbl => l_detail_col_tbl
604 , p_dimension_tbl => l_dimension_tbl
605 , p_fact_col_name => 'actual_travel_distance'
606 , p_fact_col_total => 'N'
607 , p_column_key => 'actual_travel_distance'
608 );
609
610 isc_fs_rpt_util_pkg.add_detail_column
611 ( p_detail_col_tbl => l_detail_col_tbl
612 , p_dimension_tbl => l_dimension_tbl
613 , p_fact_col_name => 'travel_dist_var'
614 , p_fact_col_total => 'N'
615 , p_column_key => 'travel_dist_var'
616 );
617
618 isc_fs_rpt_util_pkg.add_detail_column
619 ( p_detail_col_tbl => l_detail_col_tbl
620 , p_dimension_tbl => l_dimension_tbl
621 , p_dimension_level => isc_fs_rpt_util_pkg.G_CUSTOMER
622 , p_column_key => 'customer'
623 );
624
625 isc_fs_rpt_util_pkg.add_detail_column
626 ( p_detail_col_tbl => l_detail_col_tbl
627 , p_dimension_tbl => l_dimension_tbl
628 , p_dimension_level => isc_fs_rpt_util_pkg.G_TASK_ADDRESS
629 , p_column_key => 'address'
630 );
631
632 l_order_by := isc_fs_rpt_util_pkg.get_parameter_value
633 ( p_param
634 , 'ORDERBY'
635 );
636
637 if l_order_by like '% DESC%' then
638 l_asc_desc := ' desc ';
639 else
640 l_asc_desc := ' asc ';
641 end if;
642
643 l_rank_order := 'order by ' ||
644 case
645 when l_order_by like '%ISC_MEASURE_1%' then
646 'sched_travel_duration_min'
647 when l_order_by like '%ISC_MEASURE_2%' then
648 'actual_travel_duration_min'
649 when l_order_by like '%ISC_MEASURE_3%' then
650 'travel_duration_var'
651 when l_order_by like '%ISC_MEASURE_4%' then
652 'sched_travel_distance'
653 when l_order_by like '%ISC_MEASURE_5%' then
654 'actual_travel_distance'
655 when l_order_by like '%ISC_MEASURE_6%' then
656 'travel_dist_var'
657 end ||
658 l_asc_desc ||
659 'nulls last, task_number';
660
661 l_stmt := 'select
662 oset.task_number ISC_ATTRIBUTE_1
663 , ' || isc_fs_rpt_util_pkg.get_detail_column
664 ( l_detail_col_tbl
665 , 'task_type'
666 , 'ISC_ATTRIBUTE_2'
667 ) || '
668 , ' || isc_fs_rpt_util_pkg.get_detail_column
669 ( l_detail_col_tbl
670 , 'task_owner'
671 , 'ISC_ATTRIBUTE_3'
672 ) || '
673 , ' || isc_fs_rpt_util_pkg.get_detail_column
674 ( l_detail_col_tbl
675 , 'task_assignee'
676 , 'ISC_ATTRIBUTE_4'
677 ) || '
678 , oset.sched_travel_duration_min ISC_MEASURE_1
679 , oset.actual_travel_duration_min ISC_MEASURE_2
680 , oset.travel_duration_var ISC_MEASURE_3
681 , oset.sched_travel_distance ISC_MEASURE_4
682 , oset.actual_travel_distance ISC_MEASURE_5
683 , oset.travel_dist_var ISC_MEASURE_6
684 , ' || isc_fs_rpt_util_pkg.get_detail_column
685 ( l_detail_col_tbl
686 , 'customer'
687 , 'ISC_ATTRIBUTE_6'
688 ) || '
689 , ' || isc_fs_rpt_util_pkg.get_detail_column
690 ( l_detail_col_tbl
691 , 'address'
692 , 'ISC_ATTRIBUTE_7'
693 ) || '
694 , ' || isc_fs_rpt_util_pkg.get_task_detail_page_function
695 ( 'oset.task_id' ) || ' ISC_ATTRIBUTE_8
696 from
697 ' || isc_fs_rpt_util_pkg.detail_sql
698 ( p_detail_col_tbl => l_detail_col_tbl
699 , p_dimension_tbl => l_dimension_tbl
700 , p_mv_name => l_mv
701 , p_where_clause => l_where_clause
702 , p_rank_order => l_rank_order
703 , p_override_date_clause => ' actual_travel_duration_min >= &ISC_FS_LOW and' ||
704 ' actual_travel_duration_min < &ISC_FS_HIGH and' ||
705 ' actual_travel_distance >= &ISC_FS_LOW1 and' ||
706 ' actual_travel_distance < &ISC_FS_HIGH1 and' ||
707 ' travel_duration_var >= &ISC_FS_LOW2 and' ||
708 ' travel_duration_var < &ISC_FS_HIGH2 and' ||
709 ' travel_dist_var >= &ISC_FS_LOW3 and' ||
710 ' travel_dist_var < &ISC_FS_HIGH3 '
711 );
712
713 isc_fs_rpt_util_pkg.bind_low_high
714 ( p_param
715 , isc_fs_rpt_util_pkg.G_TRVL_TIME_DISTRIB
716 , 'BIV_FS_TRVL_TIME'
717 , l_custom_output
718 );
719
720 isc_fs_rpt_util_pkg.bind_low_high
721 ( p_param
722 , isc_fs_rpt_util_pkg.G_TRVL_DIST_DISTRIB
723 , 'BIV_FS_TRVL_DIST'
724 , l_custom_output
725 , '&ISC_FS_LOW1'
726 , '&ISC_FS_HIGH1'
727 );
728
729
730 isc_fs_rpt_util_pkg.bind_low_high
731 ( p_param
732 , isc_fs_rpt_util_pkg.G_TRVL_TIME_VAR_DISTRIB
733 , 'BIV_FS_TRVL_TIME_VAR'
734 , l_custom_output
735 , '&ISC_FS_LOW2'
736 , '&ISC_FS_HIGH2'
737 );
738
739 isc_fs_rpt_util_pkg.bind_low_high
740 ( p_param
741 , isc_fs_rpt_util_pkg.G_TRVL_DIST_VAR_DISTRIB
742 , 'BIV_FS_TRVL_DIST_VAR'
743 , l_custom_output
744 , '&ISC_FS_LOW3'
745 , '&ISC_FS_HIGH3'
746 );
747
748 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
749 --l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
750 isc_fs_rpt_util_pkg.enhance_time_join
751 ( p_query => l_stmt
752 , p_trend => 'N'
753 );
754
755 x_custom_sql := l_stmt;
756 x_custom_output := l_custom_output;
757
758 end get_dtl_sql; -- the detail query ends here
759
760 -------------------------trend query--------------------------------------------
761 procedure get_trd_sql
762 ( p_param in bis_pmv_page_parameter_tbl
763 , x_custom_sql out nocopy varchar2
764 , x_custom_output out nocopy bis_query_attributes_tbl
765 )
766 is
767
768 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
769 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
770 l_custom_output bis_query_attributes_tbl;
771 l_curr_suffix varchar2(3);
772 l_where_clause varchar2(10000);
773 l_viewby_select varchar2(400); -- needed to be increased from 200
774 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
775 l_dim_bmap number;
776 l_comparison_type varchar2(200);
777 l_xtd varchar2(200);
778 l_mv varchar2(10000);
779 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
780 l_stmt varchar2(32767);
781 l_to_date_type varchar2(200);
782 l_distance varchar2(30);
783 l_view_by varchar2(100);
784
785 begin
786
787 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
788 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
789
790 isc_fs_rpt_util_pkg.register_dimension_levels
791 ( l_dimension_tbl
792 , l_dim_filter_map
793 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
794 -- , isc_fs_rpt_util_pkg.G_DISTANCE_UOM, 'Y'
795 );
796
797 isc_fs_rpt_util_pkg.process_parameters
798 ( p_param => p_param
799 , p_dimension_tbl => l_dimension_tbl
800 , p_dim_filter_map => l_dim_filter_map
801 , p_trend => 'N'
802 , p_custom_output => l_custom_output
803 , x_cur_suffix => l_curr_suffix
804 , x_where_clause => l_where_clause
805 , x_viewby_select => l_viewby_select
806 , x_join_tbl => l_join_tbl
807 , x_dim_bmap => l_dim_bmap
808 , x_comparison_type => l_comparison_type
809 , x_xtd => l_xtd
810 , x_uom_suffix => l_distance
811 );
812
813 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
814 l_to_date_type := 'XTD';
815 else
816 l_to_date_type := 'RLX';
817 end if;
818
819 poa_dbi_util_pkg.add_column
820 ( p_col_tbl => l_col_tbl
821 , p_col_name => 'tot_trv_dur_min'
822 , p_alias_name => 'tot_trv_dur_min'
823 , p_grand_total => 'N'
824 , p_prior_code => poa_dbi_util_pkg.both_priors
825 , p_to_date_type => l_to_date_type
826 );
827
828 poa_dbi_util_pkg.add_column
829 ( p_col_tbl => l_col_tbl
830 , p_col_name => 'count_dur_task'
831 , p_alias_name => 'count_dur_task'
832 , p_grand_total => 'N'
833 , p_prior_code => poa_dbi_util_pkg.both_priors
834 , p_to_date_type => l_to_date_type
835 );
836
837 poa_dbi_util_pkg.add_column
838 ( p_col_tbl => l_col_tbl
839 , p_col_name => 'tot_trv_dist'
840 , p_alias_name => 'tot_trv_dist'
841 , p_grand_total => 'N'
842 , p_prior_code => poa_dbi_util_pkg.both_priors
843 , p_to_date_type => l_to_date_type
844 );
845
846 poa_dbi_util_pkg.add_column
847 ( p_col_tbl => l_col_tbl
848 , p_col_name => 'count_dist_task'
849 , p_alias_name => 'count_dist_task'
850 , p_grand_total => 'N'
851 , p_prior_code => poa_dbi_util_pkg.both_priors
852 , p_to_date_type => l_to_date_type
853 );
854
855 l_mv := get_fact_mv_name
856 ( 'TRAVEL_TIME_DISTANCE'
857 , p_param
858 , l_custom_output
859 );
860
861 l_stmt := 'select
862 cal.name VIEWBY ' ||
863 isc_fs_rpt_util_pkg.get_trend_drill
864 ( l_xtd
865 , g_trd_rep_func
866 , 'ISC_ATTRIBUTE_3'
867 , 'ISC_ATTRIBUTE_4'
868 ) || '
869 , ' || isc_fs_rpt_util_pkg.rate_column
870 ( 'c_tot_trv_dur_min'
871 , 'c_count_dur_task'
872 , 'ISC_MEASURE_2'
873 , 'N'
874 ) || '
875 , ' || isc_fs_rpt_util_pkg.change_column
876 ( isc_fs_rpt_util_pkg.rate_column
877 ( 'c_tot_trv_dur_min'
878 , 'c_count_dur_task'
879 , NULL
880 , 'N'
881 )
882 , isc_fs_rpt_util_pkg.rate_column
883 ( 'p_tot_trv_dur_min'
884 , 'p_count_dur_task'
885 , NULL
886 , 'N'
887 )
888 , 'ISC_MEASURE_3'
889 , 'N'
890 ) || '
891 , ' || isc_fs_rpt_util_pkg.rate_column
892 ( 'c_tot_trv_dist'
893 , 'c_count_dist_task'
894 , NULL
895 , 'N'
896 ) || ' ISC_MEASURE_5
897 , ' || isc_fs_rpt_util_pkg.change_column
898 ( isc_fs_rpt_util_pkg.rate_column
899 ( 'c_tot_trv_dist'
900 , 'c_count_dist_task'
901 , NULL
902 , 'N'
903 )
904 , isc_fs_rpt_util_pkg.rate_column
905 ( 'p_tot_trv_dist'
906 , 'p_count_dist_task'
907 , NULL
908 , 'N'
909 )
910 , 'ISC_MEASURE_6'
911 , 'N'
912 ) || '
913 from ' || poa_dbi_template_pkg.trend_sql
914 ( p_xtd => l_xtd
915 , p_comparison_type => l_comparison_type
916 , p_fact_name => l_mv
917 , p_where_clause => l_where_clause
918 , p_col_name => l_col_tbl
919 , p_use_grpid => 'N'
920 );
921
922 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
923 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
924
925 isc_fs_rpt_util_pkg.enhance_time_join
926 ( p_query => l_stmt
927 , p_trend => 'Y'
928 );
929
930 x_custom_sql := l_stmt;
931
932 x_custom_output := l_custom_output;
933
934 poa_dbi_util_pkg.get_custom_rolling_binds
935 ( p_custom_output => l_custom_output
936 , p_xtd => l_xtd
937 );
938
939 x_custom_output := l_custom_output;
940
941 poa_dbi_util_pkg.get_custom_trend_binds
942 ( x_custom_output => l_custom_output
943 , p_xtd => l_xtd
944 , p_comparison_type => l_comparison_type
945 );
946
947 if l_custom_output is not null then
948 for i in 1..l_custom_output.count loop
949 x_custom_output.extend;
950 x_custom_output(x_custom_output.count) := l_custom_output(i);
951 end loop;
952 end if;
953
954 end get_trd_sql;
955
956 procedure get_tot_trd_sql
957 ( p_param in bis_pmv_page_parameter_tbl
958 , x_custom_sql out nocopy varchar2
959 , x_custom_output out nocopy bis_query_attributes_tbl
960 )
961 is
962
963 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
964 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
965 l_custom_output bis_query_attributes_tbl;
966 l_curr_suffix varchar2(3);
967 l_where_clause varchar2(10000);
968 l_viewby_select varchar2(400); -- needed to be increased from 200
969 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
970 l_dim_bmap number;
971 l_comparison_type varchar2(200);
972 l_xtd varchar2(200);
973 l_mv varchar2(10000);
974 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
975 l_stmt varchar2(32767);
976 l_to_date_type varchar2(200);
977 l_distance varchar2(30);
978 l_view_by varchar2(100);
979
980 begin
981
982 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
983 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
984
985 isc_fs_rpt_util_pkg.register_dimension_levels
986 ( l_dimension_tbl
987 , l_dim_filter_map
988 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
989 -- , isc_fs_rpt_util_pkg.G_DISTANCE_UOM, 'Y'
990 );
991
992 isc_fs_rpt_util_pkg.process_parameters
993 ( p_param => p_param
994 , p_dimension_tbl => l_dimension_tbl
995 , p_dim_filter_map => l_dim_filter_map
996 , p_trend => 'N'
997 , p_custom_output => l_custom_output
998 , x_cur_suffix => l_curr_suffix
999 , x_where_clause => l_where_clause
1000 , x_viewby_select => l_viewby_select
1001 , x_join_tbl => l_join_tbl
1002 , x_dim_bmap => l_dim_bmap
1003 , x_comparison_type => l_comparison_type
1004 , x_xtd => l_xtd
1005 , x_uom_suffix => l_distance
1006 );
1007
1008 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
1009 l_to_date_type := 'XTD';
1010 else
1011 l_to_date_type := 'RLX';
1012 end if;
1013
1014 poa_dbi_util_pkg.add_column
1015 ( p_col_tbl => l_col_tbl
1016 , p_col_name => 'tot_trv_dur_min'
1017 , p_alias_name => 'tot_trv_dur_min'
1018 , p_grand_total => 'N'
1019 , p_prior_code => poa_dbi_util_pkg.both_priors
1020 , p_to_date_type => l_to_date_type
1021 );
1022
1023 poa_dbi_util_pkg.add_column
1024 ( p_col_tbl => l_col_tbl
1025 , p_col_name => 'count_dur_task'
1026 , p_alias_name => 'count_dur_task'
1027 , p_grand_total => 'N'
1028 , p_prior_code => poa_dbi_util_pkg.both_priors
1029 , p_to_date_type => l_to_date_type
1030 );
1031
1032 poa_dbi_util_pkg.add_column
1033 ( p_col_tbl => l_col_tbl
1034 , p_col_name => 'tot_trv_dist'
1035 , p_alias_name => 'tot_trv_dist'
1036 , p_grand_total => 'N'
1037 , p_prior_code => poa_dbi_util_pkg.both_priors
1038 , p_to_date_type => l_to_date_type
1039 );
1040
1041 poa_dbi_util_pkg.add_column
1042 ( p_col_tbl => l_col_tbl
1043 , p_col_name => 'count_dist_task'
1044 , p_alias_name => 'count_dist_task'
1045 , p_grand_total => 'N'
1046 , p_prior_code => poa_dbi_util_pkg.both_priors
1047 , p_to_date_type => l_to_date_type
1048 );
1049
1050 l_mv := get_fact_mv_name
1051 ( 'TRAVEL_TIME_DISTANCE'
1052 , p_param
1053 , l_custom_output
1054 );
1055
1056 l_stmt := 'select
1057 cal.name VIEWBY ' ||
1058 isc_fs_rpt_util_pkg.get_trend_drill
1059 ( l_xtd
1060 , g_tot_trd_rep_func
1061 , 'ISC_ATTRIBUTE_3'
1062 , 'ISC_ATTRIBUTE_4'
1063 ) || '
1064 , ' || isc_fs_rpt_util_pkg.rate_column
1065 ( 'c_tot_trv_dur_min'
1066 , '60'
1067 , 'ISC_MEASURE_2'
1068 , 'N'
1069 ) || '
1070 , ' || isc_fs_rpt_util_pkg.change_column
1071 ( isc_fs_rpt_util_pkg.rate_column
1072 ( 'c_tot_trv_dur_min'
1073 , '60'
1074 , NULL
1075 , 'N'
1076 )
1077 , isc_fs_rpt_util_pkg.rate_column
1078 ( 'p_tot_trv_dur_min'
1079 , '60'
1080 , NULL
1081 , 'N'
1082 )
1083 , 'ISC_MEASURE_3'
1084 , 'N'
1085 ) || '
1086 , c_tot_trv_dist ISC_MEASURE_5
1087 , ' || isc_fs_rpt_util_pkg.change_column
1088 ( 'c_tot_trv_dist'
1089 , 'p_tot_trv_dist'
1090 , 'ISC_MEASURE_6'
1091 , 'N'
1092 ) || '
1093 from ' || poa_dbi_template_pkg.trend_sql
1094 ( p_xtd => l_xtd
1095 , p_comparison_type => l_comparison_type
1096 , p_fact_name => l_mv
1097 , p_where_clause => l_where_clause
1098 , p_col_name => l_col_tbl
1099 , p_use_grpid => 'N'
1100 );
1101
1102 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
1103 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
1104
1105 isc_fs_rpt_util_pkg.enhance_time_join
1106 ( p_query => l_stmt
1107 , p_trend => 'Y'
1108 );
1109
1110 x_custom_sql := l_stmt;
1111
1112 x_custom_output := l_custom_output;
1113
1114 poa_dbi_util_pkg.get_custom_rolling_binds
1115 ( p_custom_output => l_custom_output
1116 , p_xtd => l_xtd
1117 );
1118
1119 x_custom_output := l_custom_output;
1120
1121 poa_dbi_util_pkg.get_custom_trend_binds
1122 ( x_custom_output => l_custom_output
1123 , p_xtd => l_xtd
1124 , p_comparison_type => l_comparison_type
1125 );
1126
1127 if l_custom_output is not null then
1128 for i in 1..l_custom_output.count loop
1129 x_custom_output.extend;
1130 x_custom_output(x_custom_output.count) := l_custom_output(i);
1131 end loop;
1132 end if;
1133
1134 end get_tot_trd_sql;
1135
1136 procedure get_time_bucket_sql
1137 ( p_param in bis_pmv_page_parameter_tbl
1138 , x_custom_sql out nocopy varchar2
1139 , x_custom_output out nocopy bis_query_attributes_tbl
1140 )
1141 is
1142
1143 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
1144 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
1145 l_custom_output bis_query_attributes_tbl;
1146 l_curr_suffix varchar2(3);
1147 l_where_clause varchar2(10000);
1148 l_viewby_select varchar2(400); -- needed to be increased from 200
1149 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
1150 l_dim_bmap number;
1151 l_comparison_type varchar2(200);
1152 l_xtd varchar2(200);
1153 l_mv varchar2(10000);
1154 l_view_by varchar2(200);
1155 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
1156 l_product varchar2(50);
1157 l_stmt varchar2(32700);
1158 l_distance varchar2(300);
1159 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
1160 l_to_date_type varchar2(200);
1161 l_drill_across_task varchar2(1000);
1162
1163 begin
1164
1165 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
1166 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
1167
1168 isc_fs_rpt_util_pkg.register_dimension_levels
1169 ( l_dimension_tbl
1170 , l_dim_filter_map
1171 , isc_fs_rpt_util_pkg.G_DISTRICT , 'Y'
1172 );
1173
1174 isc_fs_rpt_util_pkg.process_parameters
1175 ( p_param => p_param
1176 , p_dimension_tbl => l_dimension_tbl
1177 , p_dim_filter_map => l_dim_filter_map
1178 , p_trend => 'N'
1179 , p_custom_output => l_custom_output
1180 , x_cur_suffix => l_curr_suffix
1181 , x_where_clause => l_where_clause
1182 , x_viewby_select => l_viewby_select
1183 , x_join_tbl => l_join_tbl
1184 , x_dim_bmap => l_dim_bmap
1185 , x_comparison_type => l_comparison_type
1186 , x_xtd => l_xtd
1187 );
1188
1189 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
1190 l_to_date_type := 'XTD';
1191 else
1192 l_to_date_type := 'RLX';
1193 end if;
1194
1195 l_mv := get_fact_mv_name
1196 ( 'TRAVEL_TIME_DISTRIBUTION'
1197 , p_param
1198 , l_custom_output
1199 );
1200
1201 poa_dbi_util_pkg.add_column
1202 ( p_col_tbl => l_col_tbl
1203 , p_col_name => 'tot_trv_dur_min'
1204 , p_alias_name => 'tot_trv_dur_min'
1205 , p_to_date_type => l_to_date_type
1206 );
1207
1208 poa_dbi_util_pkg.add_column
1209 ( p_col_tbl => l_col_tbl
1210 , p_col_name => 'count_dur_task'
1211 , p_alias_name => 'count_dur_task'
1212 , p_to_date_type => l_to_date_type
1213 );
1214
1215 poa_dbi_util_pkg.add_bucket_columns
1216 ( p_short_name => 'BIV_FS_TRVL_TIME'
1217 , p_col_tbl => l_col_tbl
1218 , p_col_name => 'actual_travel_duration_min'
1219 , p_alias_name => 'bucket'
1220 , p_prior_code => poa_dbi_util_pkg.no_priors
1221 , p_to_date_type => l_to_date_type
1222 , x_bucket_rec => l_bucket_rec
1223 );
1224
1225 l_view_by := isc_fs_rpt_util_pkg.get_parameter_value
1226 ( p_param
1227 , 'VIEW_BY'
1228 );
1229
1230 l_stmt := 'select
1231 ' || l_viewby_select || '
1232 , ' || isc_fs_rpt_util_pkg.get_district_drill_down
1233 ( l_view_by
1234 , g_task_tim_dtr_func
1235 , 'ISC_ATTRIBUTE_2'
1236 ) || '
1237 , ' || isc_fs_rpt_util_pkg.get_detail_drill_down
1238 ( p_view_by => l_view_by
1239 , p_function_name => g_task_tim_dtl_func
1240 , p_check_column_name => 'ISC_MEASURE_4'
1241 , p_column_alias => 'ISC_ATTRIBUTE_3'
1242 , p_check_resource => 'Y'
1243 ) || '
1244 , ' || isc_fs_rpt_util_pkg.get_bucket_drill_down
1245 ( p_bucket_rec => l_bucket_rec
1246 , p_view_by => l_view_by
1247 , p_function_name => g_task_tim_dtl_func
1248 , p_check_column_name => 'ISC_MEASURE_5'
1249 , p_column_alias => 'ISC_ATTRIBUTE_4'
1250 , p_extra_params => '&BIV_FS_TRVL_TIME='
1251 , p_check_resource => 'Y'
1252 ) || '
1253 , ISC_MEASURE_2
1254 , ISC_MEASURE_3
1255 , ISC_MEASURE_4
1256 ' || poa_dbi_util_pkg.get_bucket_outer_query
1257 ( p_bucket_rec => l_bucket_rec
1258 , p_col_name => 'ISC_MEASURE_5'
1259 , p_alias_name => 'ISC_MEASURE_5'
1260 , p_prefix => null
1261 , p_suffix => null
1262 , p_total_flag => 'N'
1263 ) || '
1264 , ISC_MEASURE_22
1265 , ISC_MEASURE_23
1266 , ISC_MEASURE_24
1267 ' || poa_dbi_util_pkg.get_bucket_outer_query
1268 ( p_bucket_rec => l_bucket_rec
1269 , p_col_name => 'ISC_MEASURE_25'
1270 , p_alias_name => 'ISC_MEASURE_25'
1271 , p_prefix => null
1272 , p_suffix => null
1273 , p_total_flag => 'N'
1274 ) || '
1275 from (
1276 select
1277 row_number() over(&ORDER_BY_CLAUSE nulls last, '|| isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
1278 ,iset.* from ( select * from (
1279 select
1280 record_type
1281 , district_id
1282 , district_id_c
1283 , ' || isc_fs_rpt_util_pkg.rate_column
1284 ( 'c_tot_trv_dur_min'
1285 , 'c_count_dur_task'
1286 , 'ISC_MEASURE_2'
1287 , 'N'
1288 ) || '
1289 , ' || isc_fs_rpt_util_pkg.change_column
1290 ( isc_fs_rpt_util_pkg.rate_column
1291 ( 'c_tot_trv_dur_min'
1292 , 'c_count_dur_task'
1293 , null
1294 , 'N'
1295 )
1296 , isc_fs_rpt_util_pkg.rate_column
1297 ( 'p_tot_trv_dur_min'
1298 , 'p_count_dur_task'
1299 , null
1300 , 'N'
1301 )
1302 , 'ISC_MEASURE_3'
1303 , 'N'
1304 ) || '
1305 , nvl(c_count_dur_task,0) ISC_MEASURE_4
1306 ' || poa_dbi_util_pkg.get_bucket_outer_query
1307 ( p_bucket_rec => l_bucket_rec
1308 , p_col_name => 'bucket'
1309 , p_alias_name => 'ISC_MEASURE_5'
1310 , p_prefix => 'nvl(c_'
1311 , p_suffix => ',0)/abs(decode(c_count_dur_task,0,null,c_count_dur_task))*100'
1312 , p_total_flag => 'N'
1313 ) || '
1314 , ' || isc_fs_rpt_util_pkg.rate_column
1315 ( 'c_tot_trv_dur_min_total'
1316 , 'c_count_dur_task_total'
1317 , 'ISC_MEASURE_22'
1318 , 'N'
1319 ) || '
1320 , ' || isc_fs_rpt_util_pkg.change_column
1321 ( isc_fs_rpt_util_pkg.rate_column
1322 ( 'c_tot_trv_dur_min_total'
1323 , 'c_count_dur_task_total'
1324 , null
1325 , 'N'
1326 )
1327 , isc_fs_rpt_util_pkg.rate_column
1328 ( 'p_tot_trv_dur_min_total'
1329 , 'p_count_dur_task_total'
1330 , null
1331 , 'N'
1332 )
1333 , 'ISC_MEASURE_23'
1334 , 'N'
1335 ) || '
1336 , nvl(c_count_dur_task_total,0) ISC_MEASURE_24
1337 ' || poa_dbi_util_pkg.get_bucket_outer_query
1338 ( p_bucket_rec => l_bucket_rec
1339 , p_col_name => 'bucket'
1340 , p_alias_name => 'ISC_MEASURE_25'
1341 , p_prefix => 'nvl(c_'
1342 , p_suffix => ',0)/abs(decode(c_count_dur_task_total,0,null,c_count_dur_task_total))*100'
1343 , p_total_flag => 'Y'
1344 ) || '
1345 from ' || poa_dbi_template_pkg.status_sql
1346 ( p_fact_name => l_mv
1347 , p_where_clause => l_where_clause
1348 , p_join_tables => l_join_tbl
1349 , p_use_windowing => 'Y' --'N'
1350 , p_col_name => l_col_tbl
1351 , p_use_grpid => 'N'
1352 , p_paren_count => 3
1353 , p_filter_where => 'isc_measure_4 <> 0 ) iset '
1354 , p_generate_viewby => 'Y'
1355 );
1356
1357 --l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
1358
1359 poa_dbi_util_pkg.get_custom_rolling_binds
1360 ( p_custom_output => l_custom_output
1361 , p_xtd => l_xtd
1362 );
1363
1364 isc_fs_rpt_util_pkg.enhance_time_join
1365 ( p_query => l_stmt
1366 , p_trend => 'N'
1367 );
1368
1369 x_custom_output := l_custom_output;
1370 x_custom_sql := l_stmt;
1371
1372 end get_time_bucket_sql;
1373
1374 procedure get_distance_bucket_sql
1375 ( p_param in bis_pmv_page_parameter_tbl
1376 , x_custom_sql out nocopy varchar2
1377 , x_custom_output out nocopy bis_query_attributes_tbl
1378 )
1379 is
1380
1381 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
1382 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
1383 l_custom_output bis_query_attributes_tbl;
1384 l_curr_suffix varchar2(3);
1385 l_where_clause varchar2(10000);
1386 l_viewby_select varchar2(400); -- needed to be increased from 200
1387 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
1388 l_dim_bmap number;
1389 l_comparison_type varchar2(200);
1390 l_xtd varchar2(200);
1391 l_mv varchar2(10000);
1392 l_view_by varchar2(200);
1393 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
1394 l_product varchar2(50);
1395 l_stmt varchar2(32700);
1396 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
1397 l_bucket_name varchar2(100);
1398 l_to_date_type varchar2(200);
1399 l_drill_across_task varchar2(1000);
1400 l_distance varchar2(100);
1401
1402 begin
1403
1404 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
1405 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
1406
1407 isc_fs_rpt_util_pkg.register_dimension_levels
1408 ( l_dimension_tbl
1409 , l_dim_filter_map
1410 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
1411 );
1412
1413 isc_fs_rpt_util_pkg.process_parameters
1414 ( p_param => p_param
1415 , p_dimension_tbl => l_dimension_tbl
1416 , p_dim_filter_map => l_dim_filter_map
1417 , p_trend => 'N'
1418 , p_custom_output => l_custom_output
1419 , x_cur_suffix => l_curr_suffix
1420 , x_where_clause => l_where_clause
1421 , x_viewby_select => l_viewby_select
1422 , x_join_tbl => l_join_tbl
1423 , x_dim_bmap => l_dim_bmap
1424 , x_comparison_type => l_comparison_type
1425 , x_xtd => l_xtd
1426 , x_uom_suffix => l_distance
1427 );
1428
1429 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
1430 l_to_date_type := 'XTD';
1431 else
1432 l_to_date_type := 'RLX';
1433 end if;
1434
1435 l_mv := get_fact_mv_name
1436 ( 'TRAVEL_DISTANCE_DISTRIBUTION'
1437 , p_param
1438 , l_custom_output
1439 );
1440
1441 poa_dbi_util_pkg.add_column
1442 ( p_col_tbl => l_col_tbl
1443 , p_col_name => 'tot_trv_dist'
1444 , p_alias_name => 'tot_trv_dist'
1445 , p_to_date_type => l_to_date_type
1446 );
1447
1448 poa_dbi_util_pkg.add_column
1449 ( p_col_tbl => l_col_tbl
1450 , p_col_name => 'count_dist_task'
1451 , p_alias_name => 'count_dist_task'
1452 , p_to_date_type => l_to_date_type
1453 );
1454
1455 poa_dbi_util_pkg.add_bucket_columns
1456 ( p_short_name => 'BIV_FS_TRVL_DIST'
1457 , p_col_tbl => l_col_tbl
1458 , p_col_name => 'actual_travel_dist'
1459 , p_alias_name => 'bucket'
1460 , p_prior_code => poa_dbi_util_pkg.no_priors
1461 , p_to_date_type => l_to_date_type
1462 , x_bucket_rec => l_bucket_rec
1463 );
1464
1465 l_view_by := isc_fs_rpt_util_pkg.get_parameter_value
1466 ( p_param
1467 , 'VIEW_BY'
1468 );
1469
1470 l_stmt := 'select
1471 ' || l_viewby_select || '
1472 , ' || isc_fs_rpt_util_pkg.get_district_drill_down
1473 ( l_view_by
1474 , g_task_dis_dtr_func
1475 , 'ISC_ATTRIBUTE_2'
1476 ) || '
1477 , ' || isc_fs_rpt_util_pkg.get_detail_drill_down
1478 ( p_view_by => l_view_by
1479 , p_function_name => g_task_tim_dtl_func
1480 , p_check_column_name => 'ISC_MEASURE_4'
1481 , p_column_alias => 'ISC_ATTRIBUTE_3'
1482 , p_check_resource => 'Y'
1483 ) || '
1484 , ' || isc_fs_rpt_util_pkg.get_bucket_drill_down
1485 ( p_bucket_rec => l_bucket_rec
1486 , p_view_by => l_view_by
1487 , p_function_name => g_task_tim_dtl_func
1488 , p_check_column_name => 'ISC_MEASURE_5'
1489 , p_column_alias => 'ISC_ATTRIBUTE_4'
1490 , p_extra_params => '&BIV_FS_TRVL_DIST='
1491 , p_check_resource => 'Y'
1492 ) || '
1493 , ISC_MEASURE_2
1494 , ISC_MEASURE_3
1495 , ISC_MEASURE_4
1496 ' || poa_dbi_util_pkg.get_bucket_outer_query
1497 ( p_bucket_rec => l_bucket_rec
1498 , p_col_name => 'ISC_MEASURE_5'
1499 , p_alias_name => 'ISC_MEASURE_5'
1500 , p_prefix => null
1501 , p_suffix => null
1502 , p_total_flag => 'N'
1503 ) || '
1504 , ISC_MEASURE_22
1505 , ISC_MEASURE_23
1506 , ISC_MEASURE_24
1507 ' || poa_dbi_util_pkg.get_bucket_outer_query
1508 ( p_bucket_rec => l_bucket_rec
1509 , p_col_name => 'ISC_MEASURE_25'
1510 , p_alias_name => 'ISC_MEASURE_25'
1511 , p_prefix => null
1512 , p_suffix => null
1513 , p_total_flag => 'N'
1514 ) || '
1515 from (
1516 select
1517 row_number() over(&ORDER_BY_CLAUSE nulls last , '|| isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
1518 ,iset.* from ( select * from (
1519 select
1520 record_type
1521 , district_id
1522 , district_id_c
1523 , ' || isc_fs_rpt_util_pkg.rate_column
1524 ( 'c_tot_trv_dist'
1525 , 'c_count_dist_task'
1526 , 'ISC_MEASURE_2'
1527 , 'N'
1528 ) || '
1529 , ' || isc_fs_rpt_util_pkg.change_column
1530 ( isc_fs_rpt_util_pkg.rate_column
1531 ( 'c_tot_trv_dist'
1532 , 'c_count_dist_task'
1533 , null
1534 , 'N'
1535 )
1536 , isc_fs_rpt_util_pkg.rate_column
1537 ( 'p_tot_trv_dist'
1538 , 'p_count_dist_task'
1539 , null
1540 , 'N'
1541 )
1542 , 'ISC_MEASURE_3'
1543 , 'N'
1544 ) || '
1545 , nvl(c_count_dist_task,0) ISC_MEASURE_4
1546 ' || poa_dbi_util_pkg.get_bucket_outer_query
1547 ( p_bucket_rec => l_bucket_rec
1548 , p_col_name => 'bucket'
1549 , p_alias_name => 'ISC_MEASURE_5'
1550 , p_prefix => 'nvl(c_'
1551 , p_suffix => ',0)/abs(decode(c_count_dist_task,0,null,c_count_dist_task))*100'
1552 , p_total_flag => 'N'
1553 ) || '
1554 , ' || isc_fs_rpt_util_pkg.rate_column
1555 ( 'c_tot_trv_dist_total'
1556 , 'c_count_dist_task_total'
1557 , 'ISC_MEASURE_22'
1558 , 'N'
1559 ) || '
1560 , ' || isc_fs_rpt_util_pkg.change_column
1561 ( isc_fs_rpt_util_pkg.rate_column
1562 ( 'c_tot_trv_dist_total'
1563 , 'c_count_dist_task_total'
1564 , null
1565 , 'N'
1566 )
1567 , isc_fs_rpt_util_pkg.rate_column
1568 ( 'p_tot_trv_dist_total'
1569 , 'p_count_dist_task_total'
1570 , null
1571 , 'N'
1572 )
1573 , 'ISC_MEASURE_23'
1574 , 'N'
1575 ) ||'
1576 , nvl(c_count_dist_task_total,0) ISC_MEASURE_24
1577 ' || poa_dbi_util_pkg.get_bucket_outer_query
1578 ( p_bucket_rec => l_bucket_rec
1579 , p_col_name => 'bucket'
1580 , p_alias_name => 'ISC_MEASURE_25'
1581 , p_prefix => 'nvl(c_'
1582 , p_suffix => ',0)/abs(decode(c_count_dist_task_total,0,null,c_count_dist_task_total))*100'
1583 , p_total_flag => 'Y'
1584 ) || '
1585 from ' || poa_dbi_template_pkg.status_sql
1586 ( p_fact_name => l_mv
1587 , p_where_clause => l_where_clause
1588 , p_join_tables => l_join_tbl
1589 , p_use_windowing => 'Y' --'N'
1590 , p_col_name => l_col_tbl
1591 , p_use_grpid => 'N'
1592 , p_paren_count => 3
1593 , p_filter_where => 'isc_measure_4 <> 0 ) iset '
1594 , p_generate_viewby => 'Y'
1595 );
1596
1597 --l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
1598
1599 poa_dbi_util_pkg.get_custom_rolling_binds
1600 ( p_custom_output => l_custom_output
1601 , p_xtd => l_xtd
1602 );
1603
1604 isc_fs_rpt_util_pkg.enhance_time_join
1605 ( p_query => l_stmt
1606 , p_trend => 'N'
1607 );
1608
1609 x_custom_output := l_custom_output;
1610 x_custom_sql := l_stmt;
1611
1612 end get_distance_bucket_sql;
1613
1614 end ISC_FS_TRV_TIM_DIS_RPT_PKG;