[Home] [Help]
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);
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;
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);
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
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
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'
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
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'
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
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'
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
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'
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
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:
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
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);
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);
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
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
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
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);
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);
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
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
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'
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
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'
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
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'
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
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'
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
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:
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: );
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
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);
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);
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
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
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'
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
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'
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
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'
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
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'
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
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:
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: );
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
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);
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;
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
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
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
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
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'
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:
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
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
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_'
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_'
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:
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
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);
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);
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
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
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
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
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'
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:
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
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
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_'
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_'
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: