eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ISC_FS_TRV_TIM_DIS_RPT_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 24


       select
         f.record_type
       , f.parent_district_id
       , f.district_id
       , f.district_id_c
       , f.time_id
       , f.period_type_id
       , f.actual_travel_duration_min tot_trv_dur_min
       , f.task_dist_count count_dist_task
       , f.actual_travel_distance_km * &ISC_FS_DIST_FACTOR tot_trv_dist
       , f.task_duration_count count_dur_task
       from isc_fs_017_mv f
     )' ;

Line: 41


       select
         f.record_type
       , f.parent_district_id
       , f.district_id
       , f.district_id_c
       , f.time_id
       , f.period_type_id
       , f.actual_travel_duration_min tot_trv_dur_min
       , f.task_duration_count count_dur_task
       , f.actual_travel_duration_min_b1
       , f.actual_travel_duration_min_b2
       , f.actual_travel_duration_min_b3
       , f.actual_travel_duration_min_b4
       , f.actual_travel_duration_min_b5
       , f.actual_travel_duration_min_b6
       , f.actual_travel_duration_min_b7
       , f.actual_travel_duration_min_b8
       , f.actual_travel_duration_min_b9
       , f.actual_travel_duration_min_b10
       from isc_fs_017_mv f
     )' ;

Line: 70


       select
         t.task_id
       , t.task_number
       , t.task_type_id
       , t.owner_id
       , t.owner_type
       , t.customer_id
       , decode( t.location_id
               , null, ''ADDRESS_ID''
               , ''LOCATION_ID''
               )  address_type
       , nvl(t.location_id, t.address_id)  address_id
       , ta.sched_travel_duration_min
       , ta.actual_travel_duration_min
       , (( ta.actual_travel_duration_min - ta.sched_travel_duration_min  )*100 )/
            ta.sched_travel_duration_min         travel_duration_var
       , ta.sched_travel_distance_km  * &ISC_FS_DIST_FACTOR sched_travel_distance
       , ta.actual_travel_distance_km * &ISC_FS_DIST_FACTOR actual_travel_distance
       , ((ta.actual_travel_distance_km - ta.sched_travel_distance_km  )*100 )/
           ta.sched_travel_distance_km          travel_dist_var
       , ta.resource_id  assignee_id
       , ta.resource_type  assignee_type
       , ta.resource_id  district_id
       , ta.district_id  parent_district_id
       , to_char(ta.resource_id) || ''.'' || ta.district_id district_id_c
       from
         isc_fs_task_assignmnts_f ta
       , isc_fs_tasks_f t
       where
           t.task_type_rule = ''DISPATCH''
       and t.task_id = ta.task_id
       and ta.deleted_flag <> ''Y''
       and t.source_object_type_code = ''SR''
       and ( ( ta.actual_travel_duration_min is not null and
               nvl(ta.sched_travel_duration_min,0) > 0 ) and /* Bug 5169178 */
             ( ta.actual_travel_distance_km is not null and
               nvl(ta.sched_travel_distance_km,0) > 0 )
           )
       and ta.report_date between &BIS_CURRENT_EFFECTIVE_START_DATE
                              and &BIS_CURRENT_ASOF_DATE
     )';

Line: 115


       select
         t.task_id
       , t.task_number
       , t.task_type_id
       , t.owner_id
       , t.owner_type
       , t.customer_id
       , decode( t.location_id
               , null, ''ADDRESS_ID''
               , ''LOCATION_ID''
               )  address_type
       , nvl(t.location_id, t.address_id)  address_id
       , ta.sched_travel_duration_min
       , ta.actual_travel_duration_min
       , (( ta.actual_travel_duration_min - ta.sched_travel_duration_min  )*100 )/
            ta.sched_travel_duration_min         travel_duration_var
       , ta.sched_travel_distance_km * &ISC_FS_DIST_FACTOR sched_travel_distance
       , ta.actual_travel_distance_km * &ISC_FS_DIST_FACTOR actual_travel_distance
       , (( ta.actual_travel_distance_km - ta.sched_travel_distance_km  )*100 )/
            ta.sched_travel_distance_km          travel_dist_var
       , ta.resource_id  assignee_id
       , ta.resource_type  assignee_type
       , den.prg_id  district_id
       , den.parent_prg_id  parent_district_id
       , to_char(ta.resource_id) || ''.'' || ta.district_id district_id_c
       from
         isc_fs_task_assignmnts_f ta
       , isc_fs_tasks_f t
       , isc_fs_002_mv den
       where
           t.task_type_rule = ''DISPATCH''
       and t.task_id = ta.task_id
       and ta.deleted_flag <> ''Y''
       and ta.district_id = den.rg_id
       and t.source_object_type_code = ''SR''
       and ( ( ta.actual_travel_duration_min is not null and
               nvl(ta.sched_travel_duration_min,0) > 0 ) and /* bug 5169178 */
             ( ta.actual_travel_distance_km is not null and
               nvl(ta.sched_travel_distance_km,0) > 0 )
           )
       and ta.report_date between &BIS_CURRENT_EFFECTIVE_START_DATE
                              and &BIS_CURRENT_ASOF_DATE
     )';

Line: 163


       select
         f.record_type
       , f.parent_district_id
       , f.district_id
       , f.district_id_c
       , f.time_id
       , f.period_type_id
       , f.actual_travel_distance_km * &ISC_FS_DIST_FACTOR tot_trv_dist
       , f.task_dist_count count_dist_task
       , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b1,f.actual_travel_dist_mi_b1) actual_travel_dist_b1
       , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b2,f.actual_travel_dist_mi_b2) actual_travel_dist_b2
       , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b3,f.actual_travel_dist_mi_b3) actual_travel_dist_b3
       , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b4,f.actual_travel_dist_mi_b4) actual_travel_dist_b4
       , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b5,f.actual_travel_dist_mi_b5) actual_travel_dist_b5
       , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b6,f.actual_travel_dist_mi_b6) actual_travel_dist_b6
       , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b7,f.actual_travel_dist_mi_b7) actual_travel_dist_b7
       , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b8,f.actual_travel_dist_mi_b8) actual_travel_dist_b8
       , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b9,f.actual_travel_dist_mi_b9) actual_travel_dist_b9
       , decode(&ISC_FS_DIST_FACTOR,1,f.actual_travel_dist_km_b10,f.actual_travel_dist_mi_b10) actual_travel_dist_b10
       from isc_fs_018_mv f
     )';

Line: 218


  l_viewby_select    varchar2(400); -- needed to be increased from 200

Line: 242


  , x_viewby_select    => l_viewby_select
  , x_join_tbl         => l_join_tbl
  , x_dim_bmap         => l_dim_bmap
  , x_comparison_type  => l_comparison_type
  , x_xtd              => l_xtd
  , x_uom_suffix       => l_distance
  );

Line: 303


  l_stmt := 'select
  ' || l_viewby_select || '
, ISC_MEASURE_1
, ISC_MEASURE_2
, ISC_MEASURE_3
, ISC_MEASURE_4
, ISC_MEASURE_5
, ISC_MEASURE_1 ISC_MEASURE_6
, ISC_MEASURE_8 ISC_MEASURE_8
, ISC_MEASURE_3 ISC_MEASURE_9
, ISC_MEASURE_11
, ISC_MEASURE_12
, ISC_MEASURE_13
, ISC_MEASURE_14
, ISC_MEASURE_15
, ISC_MEASURE_11 ISC_MEASURE_16
, ISC_MEASURE_12 ISC_MEASURE_17
, ISC_MEASURE_18 ISC_MEASURE_18
, ISC_MEASURE_13 ISC_MEASURE_19
, ISC_MEASURE_14 ISC_MEASURE_20
, ' || isc_fs_rpt_util_pkg.get_district_drill_down
       ( l_view_by
       , g_task_rep_func
       , 'ISC_ATTRIBUTE_4'
       ) || '
from ( select
row_number() over(&ORDER_BY_CLAUSE nulls last, ' || isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
, iset.*
from ( select * from (
select
  record_type
, district_id
, district_id_c
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_tot_trv_dur_min'
       , 'c_count_dur_task'
       , 'ISC_MEASURE_1'
       , 'N'
       ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'p_tot_trv_dur_min'
       , 'p_count_dur_task'
       , 'ISC_MEASURE_5'
       , 'N'
       ) || '
, ' || isc_fs_rpt_util_pkg.change_column
       ( isc_fs_rpt_util_pkg.rate_column
         ( 'c_tot_trv_dur_min'
         , 'c_count_dur_task'
         , null
         , 'N'
         )
       , isc_fs_rpt_util_pkg.rate_column
         ( 'p_tot_trv_dur_min'
         , 'p_count_dur_task'
         , null
         , 'N'
         )
       , 'ISC_MEASURE_2'
       , 'N'
       ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_tot_trv_dist'
       , 'c_count_dist_task'
       , 'ISC_MEASURE_3'
       , 'N'
       ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'p_tot_trv_dist'
       , 'p_count_dist_task'
       , 'ISC_MEASURE_8'
       , 'N'
       ) || '
, ' || isc_fs_rpt_util_pkg.change_column
       ( isc_fs_rpt_util_pkg.rate_column
         ( 'c_tot_trv_dist'
         , 'c_count_dist_task'
         , null
         , 'N'
         )
       , isc_fs_rpt_util_pkg.rate_column
         ( 'p_tot_trv_dist'
         , 'p_count_dist_task'
         , null
         , 'N'
         )
       , 'ISC_MEASURE_4'
       , 'N'
       ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_tot_trv_dur_min_total'
       , 'c_count_dur_task_total'
       , 'ISC_MEASURE_11'
       , 'N'
       ) || '
, ' || isc_fs_rpt_util_pkg.change_column
       ( isc_fs_rpt_util_pkg.rate_column
         ( 'c_tot_trv_dur_min_total'
         , 'c_count_dur_task_total'
         , null
         , 'N'
         )
       , isc_fs_rpt_util_pkg.rate_column
         ( 'p_tot_trv_dur_min_total'
         , 'p_count_dur_task_total'
         , null
         , 'N'
         )
       , 'ISC_MEASURE_12'
       , 'N'
       ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_tot_trv_dist_total'
       , 'c_count_dist_task_total'
       , 'ISC_MEASURE_13'
       , 'N'
       ) ||'
, ' || isc_fs_rpt_util_pkg.change_column
       ( isc_fs_rpt_util_pkg.rate_column
         ( 'c_tot_trv_dist_total'
         , 'c_count_dist_task_total'
         , null
         , 'N'
         )
       , isc_fs_rpt_util_pkg.rate_column
         ( 'p_tot_trv_dist_total'
         , 'p_count_dist_task_total'
         , null
         , 'N'
         )
       , 'ISC_MEASURE_14'
       , 'N'
       ) || '
, p_tot_trv_dur_min_total ISC_MEASURE_15
, p_tot_trv_dist_total ISC_MEASURE_18
from ' || poa_dbi_template_pkg.status_sql
          ( p_fact_name            => l_mv
          , p_where_clause         => l_where_clause
          , p_join_tables          => l_join_tbl
          , p_use_windowing        => 'Y'
          , p_col_name             => l_col_tbl
          , p_use_grpid            => 'N'
          , p_paren_count          => 3
          , p_filter_where         => '1=1 ) iset '
          , p_generate_viewby      => 'Y'
          );

Line: 480


  l_viewby_select    varchar2(400); -- needed to be increased from 200

Line: 519


  , x_viewby_select    => l_viewby_select
  , x_join_tbl         => l_join_tbl
  , x_dim_bmap         => l_dim_bmap
  , x_comparison_type  => l_comparison_type
  , x_xtd              => l_xtd
  , x_uom_suffix       => l_distance
  );

Line: 661


   l_stmt := 'select
  oset.task_number ISC_ATTRIBUTE_1
, ' || isc_fs_rpt_util_pkg.get_detail_column
       ( l_detail_col_tbl
       , 'task_type'
       , 'ISC_ATTRIBUTE_2'
       ) || '
, ' || isc_fs_rpt_util_pkg.get_detail_column
       ( l_detail_col_tbl
       , 'task_owner'
       , 'ISC_ATTRIBUTE_3'
       ) || '
, ' || isc_fs_rpt_util_pkg.get_detail_column
       ( l_detail_col_tbl
       , 'task_assignee'
       , 'ISC_ATTRIBUTE_4'
       ) || '
, oset.sched_travel_duration_min ISC_MEASURE_1
, oset.actual_travel_duration_min ISC_MEASURE_2
, oset.travel_duration_var ISC_MEASURE_3
, oset.sched_travel_distance ISC_MEASURE_4
, oset.actual_travel_distance ISC_MEASURE_5
, oset.travel_dist_var ISC_MEASURE_6
, ' || isc_fs_rpt_util_pkg.get_detail_column
       ( l_detail_col_tbl
       , 'customer'
       , 'ISC_ATTRIBUTE_6'
       ) || '
, ' || isc_fs_rpt_util_pkg.get_detail_column
       ( l_detail_col_tbl
       , 'address'
       , 'ISC_ATTRIBUTE_7'
       ) || '
, ' || isc_fs_rpt_util_pkg.get_task_detail_page_function
       ( 'oset.task_id' ) || ' ISC_ATTRIBUTE_8
from
' || isc_fs_rpt_util_pkg.detail_sql
     ( p_detail_col_tbl => l_detail_col_tbl
     , p_dimension_tbl  => l_dimension_tbl
     , p_mv_name        => l_mv
     , p_where_clause   => l_where_clause
     , p_rank_order     => l_rank_order
     , p_override_date_clause => ' actual_travel_duration_min >= &ISC_FS_LOW and' ||
                                 ' actual_travel_duration_min < &ISC_FS_HIGH and' ||
                                 ' actual_travel_distance >= &ISC_FS_LOW1 and' ||
                                 ' actual_travel_distance < &ISC_FS_HIGH1 and' ||
                                 ' travel_duration_var >= &ISC_FS_LOW2 and' ||
                                 ' travel_duration_var < &ISC_FS_HIGH2 and' ||
                                 ' travel_dist_var >= &ISC_FS_LOW3 and' ||
                                 ' travel_dist_var < &ISC_FS_HIGH3 '
     );

Line: 773


  l_viewby_select    varchar2(400); -- needed to be increased from 200

Line: 805


  , x_viewby_select    => l_viewby_select
  , x_join_tbl         => l_join_tbl
  , x_dim_bmap         => l_dim_bmap
  , x_comparison_type  => l_comparison_type
  , x_xtd              => l_xtd
  , x_uom_suffix       => l_distance
  );

Line: 861


  l_stmt := 'select
  cal.name VIEWBY ' ||
     isc_fs_rpt_util_pkg.get_trend_drill
     ( l_xtd
     , g_trd_rep_func
     , 'ISC_ATTRIBUTE_3'
     , 'ISC_ATTRIBUTE_4'
     ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_tot_trv_dur_min'
       , 'c_count_dur_task'
       , 'ISC_MEASURE_2'
       , 'N'
       ) || '
, ' || isc_fs_rpt_util_pkg.change_column
       ( isc_fs_rpt_util_pkg.rate_column
         ( 'c_tot_trv_dur_min'
         , 'c_count_dur_task'
         , NULL
         , 'N'
         )
       , isc_fs_rpt_util_pkg.rate_column
         ( 'p_tot_trv_dur_min'
         , 'p_count_dur_task'
         , NULL
         , 'N'
         )
       , 'ISC_MEASURE_3'
       , 'N'
       ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_tot_trv_dist'
       , 'c_count_dist_task'
       , NULL
       , 'N'
       ) || ' ISC_MEASURE_5
, ' || isc_fs_rpt_util_pkg.change_column
       ( isc_fs_rpt_util_pkg.rate_column
         ( 'c_tot_trv_dist'
         , 'c_count_dist_task'
         , NULL
         , 'N'
         )
       , isc_fs_rpt_util_pkg.rate_column
         ( 'p_tot_trv_dist'
         , 'p_count_dist_task'
         , NULL
         , 'N'
         )
       , 'ISC_MEASURE_6'
       , 'N'
       ) || '
from ' || poa_dbi_template_pkg.trend_sql
          ( p_xtd                  => l_xtd
          , p_comparison_type      => l_comparison_type
          , p_fact_name            => l_mv
          , p_where_clause         => l_where_clause
          , p_col_name             => l_col_tbl
          , p_use_grpid            => 'N'
          );

Line: 968


  l_viewby_select    varchar2(400); -- needed to be increased from 200

Line: 1000


  , x_viewby_select    => l_viewby_select
  , x_join_tbl         => l_join_tbl
  , x_dim_bmap         => l_dim_bmap
  , x_comparison_type  => l_comparison_type
  , x_xtd              => l_xtd
  , x_uom_suffix       => l_distance
  );

Line: 1056


  l_stmt := 'select
  cal.name VIEWBY ' ||
     isc_fs_rpt_util_pkg.get_trend_drill
     ( l_xtd
     , g_tot_trd_rep_func
     , 'ISC_ATTRIBUTE_3'
     , 'ISC_ATTRIBUTE_4'
     ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_tot_trv_dur_min'
       , '60'
       , 'ISC_MEASURE_2'
       , 'N'
       ) || '
, ' || isc_fs_rpt_util_pkg.change_column
       ( isc_fs_rpt_util_pkg.rate_column
         ( 'c_tot_trv_dur_min'
         , '60'
         , NULL
         , 'N'
         )
       , isc_fs_rpt_util_pkg.rate_column
         ( 'p_tot_trv_dur_min'
         , '60'
         , NULL
         , 'N'
         )
       , 'ISC_MEASURE_3'
       , 'N'
       ) || '
, c_tot_trv_dist ISC_MEASURE_5
, ' || isc_fs_rpt_util_pkg.change_column
       ( 'c_tot_trv_dist'
       , 'p_tot_trv_dist'
       , 'ISC_MEASURE_6'
       , 'N'
       ) || '
from ' || poa_dbi_template_pkg.trend_sql
          ( p_xtd                  => l_xtd
          , p_comparison_type      => l_comparison_type
          , p_fact_name            => l_mv
          , p_where_clause         => l_where_clause
          , p_col_name             => l_col_tbl
          , p_use_grpid            => 'N'
          );

Line: 1148


  l_viewby_select    varchar2(400); -- needed to be increased from 200

Line: 1182


  , x_viewby_select    => l_viewby_select
  , x_join_tbl         => l_join_tbl
  , x_dim_bmap         => l_dim_bmap
  , x_comparison_type  => l_comparison_type
  , x_xtd              => l_xtd
  );

Line: 1230


  l_stmt := 'select
  ' || l_viewby_select || '
, ' || isc_fs_rpt_util_pkg.get_district_drill_down
       ( l_view_by
       , g_task_tim_dtr_func
       , 'ISC_ATTRIBUTE_2'
       ) || '
, ' || isc_fs_rpt_util_pkg.get_detail_drill_down
       ( p_view_by           => l_view_by
       , p_function_name     => g_task_tim_dtl_func
       , p_check_column_name => 'ISC_MEASURE_4'
       , p_column_alias      => 'ISC_ATTRIBUTE_3'
       , p_check_resource    => 'Y'
       ) || '
, ' || isc_fs_rpt_util_pkg.get_bucket_drill_down
       ( p_bucket_rec        => l_bucket_rec
       , p_view_by           => l_view_by
       , p_function_name     => g_task_tim_dtl_func
       , p_check_column_name => 'ISC_MEASURE_5'
       , p_column_alias      => 'ISC_ATTRIBUTE_4'
       , p_extra_params      => '&BIV_FS_TRVL_TIME='
       , p_check_resource    => 'Y'
       ) || '
, ISC_MEASURE_2
, ISC_MEASURE_3
, ISC_MEASURE_4
' || poa_dbi_util_pkg.get_bucket_outer_query
     ( p_bucket_rec => l_bucket_rec
     , p_col_name   => 'ISC_MEASURE_5'
     , p_alias_name => 'ISC_MEASURE_5'
     , p_prefix     => null
     , p_suffix     => null
     , p_total_flag => 'N'
     ) || '
, ISC_MEASURE_22
, ISC_MEASURE_23
, ISC_MEASURE_24
' ||  poa_dbi_util_pkg.get_bucket_outer_query
      ( p_bucket_rec => l_bucket_rec
      , p_col_name   => 'ISC_MEASURE_25'
      , p_alias_name => 'ISC_MEASURE_25'
      , p_prefix     => null
      , p_suffix     => null
      , p_total_flag => 'N'
      ) || '
from (
select
row_number() over(&ORDER_BY_CLAUSE nulls last, '|| isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
,iset.* from ( select * from (
select
  record_type
, district_id
, district_id_c
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_tot_trv_dur_min'
       , 'c_count_dur_task'
       , 'ISC_MEASURE_2'
       , 'N'
       ) || '
, ' || isc_fs_rpt_util_pkg.change_column
       ( isc_fs_rpt_util_pkg.rate_column
         ( 'c_tot_trv_dur_min'
         , 'c_count_dur_task'
         , null
         , 'N'
         )
       , isc_fs_rpt_util_pkg.rate_column
         ( 'p_tot_trv_dur_min'
         , 'p_count_dur_task'
         , null
         , 'N'
         )
       , 'ISC_MEASURE_3'
       , 'N'
       ) || '
,  nvl(c_count_dur_task,0)  ISC_MEASURE_4
' || poa_dbi_util_pkg.get_bucket_outer_query
     ( p_bucket_rec => l_bucket_rec
     , p_col_name   => 'bucket'
     , p_alias_name => 'ISC_MEASURE_5'
     , p_prefix     => 'nvl(c_'
     , p_suffix     => ',0)/abs(decode(c_count_dur_task,0,null,c_count_dur_task))*100'
     , p_total_flag => 'N'
     ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_tot_trv_dur_min_total'
       , 'c_count_dur_task_total'
       , 'ISC_MEASURE_22'
       , 'N'
       ) || '
, ' || isc_fs_rpt_util_pkg.change_column
       ( isc_fs_rpt_util_pkg.rate_column
         ( 'c_tot_trv_dur_min_total'
         , 'c_count_dur_task_total'
         , null
         , 'N'
         )
       , isc_fs_rpt_util_pkg.rate_column
         ( 'p_tot_trv_dur_min_total'
         , 'p_count_dur_task_total'
         , null
         , 'N'
         )
       , 'ISC_MEASURE_23'
       , 'N'
       ) || '
, nvl(c_count_dur_task_total,0) ISC_MEASURE_24
' || poa_dbi_util_pkg.get_bucket_outer_query
     ( p_bucket_rec => l_bucket_rec
     , p_col_name   => 'bucket'
     , p_alias_name => 'ISC_MEASURE_25'
     , p_prefix     => 'nvl(c_'
     , p_suffix     => ',0)/abs(decode(c_count_dur_task_total,0,null,c_count_dur_task_total))*100'
     , p_total_flag => 'Y'
     ) || '
from ' || poa_dbi_template_pkg.status_sql
          ( p_fact_name            => l_mv
          , p_where_clause         => l_where_clause
          , p_join_tables          => l_join_tbl
          , p_use_windowing        => 'Y' --'N'
          , p_col_name             => l_col_tbl
          , p_use_grpid            => 'N'
          , p_paren_count          => 3
          , p_filter_where         => 'isc_measure_4 <> 0 ) iset '
          , p_generate_viewby      => 'Y'
          );

Line: 1386


  l_viewby_select    varchar2(400); -- needed to be increased from 200

Line: 1421


  , x_viewby_select    => l_viewby_select
  , x_join_tbl         => l_join_tbl
  , x_dim_bmap         => l_dim_bmap
  , x_comparison_type  => l_comparison_type
  , x_xtd              => l_xtd
  , x_uom_suffix        => l_distance
  );

Line: 1470


  l_stmt := 'select
  ' || l_viewby_select || '
, ' || isc_fs_rpt_util_pkg.get_district_drill_down
       ( l_view_by
       , g_task_dis_dtr_func
       , 'ISC_ATTRIBUTE_2'
       ) || '
, ' || isc_fs_rpt_util_pkg.get_detail_drill_down
       ( p_view_by           => l_view_by
       , p_function_name     => g_task_tim_dtl_func
       , p_check_column_name => 'ISC_MEASURE_4'
       , p_column_alias      => 'ISC_ATTRIBUTE_3'
       , p_check_resource    => 'Y'
       ) || '
, ' || isc_fs_rpt_util_pkg.get_bucket_drill_down
       ( p_bucket_rec        => l_bucket_rec
       , p_view_by           => l_view_by
       , p_function_name     => g_task_tim_dtl_func
       , p_check_column_name => 'ISC_MEASURE_5'
       , p_column_alias      => 'ISC_ATTRIBUTE_4'
       , p_extra_params      => '&BIV_FS_TRVL_DIST='
       , p_check_resource    => 'Y'
       ) || '
, ISC_MEASURE_2
, ISC_MEASURE_3
, ISC_MEASURE_4
' || poa_dbi_util_pkg.get_bucket_outer_query
     ( p_bucket_rec => l_bucket_rec
     , p_col_name   => 'ISC_MEASURE_5'
     , p_alias_name => 'ISC_MEASURE_5'
     , p_prefix     => null
     , p_suffix     => null
     , p_total_flag => 'N'
     ) || '
, ISC_MEASURE_22
, ISC_MEASURE_23
, ISC_MEASURE_24
' || poa_dbi_util_pkg.get_bucket_outer_query
     ( p_bucket_rec => l_bucket_rec
     , p_col_name   => 'ISC_MEASURE_25'
     , p_alias_name => 'ISC_MEASURE_25'
     , p_prefix     => null
     , p_suffix     => null
     , p_total_flag => 'N'
     ) || '
from (
select
row_number() over(&ORDER_BY_CLAUSE nulls last , '|| isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
,iset.* from ( select * from (
select
  record_type
, district_id
, district_id_c
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_tot_trv_dist'
       , 'c_count_dist_task'
       , 'ISC_MEASURE_2'
       , 'N'
       ) || '
, ' || isc_fs_rpt_util_pkg.change_column
       ( isc_fs_rpt_util_pkg.rate_column
         ( 'c_tot_trv_dist'
         , 'c_count_dist_task'
         , null
         , 'N'
         )
       , isc_fs_rpt_util_pkg.rate_column
         ( 'p_tot_trv_dist'
         , 'p_count_dist_task'
         , null
         , 'N'
         )
       , 'ISC_MEASURE_3'
       , 'N'
       ) || '
,  nvl(c_count_dist_task,0)  ISC_MEASURE_4
' || poa_dbi_util_pkg.get_bucket_outer_query
     ( p_bucket_rec => l_bucket_rec
     , p_col_name   => 'bucket'
     , p_alias_name => 'ISC_MEASURE_5'
     , p_prefix     => 'nvl(c_'
     , p_suffix     => ',0)/abs(decode(c_count_dist_task,0,null,c_count_dist_task))*100'
     , p_total_flag => 'N'
     ) || '
, ' || isc_fs_rpt_util_pkg.rate_column
       ( 'c_tot_trv_dist_total'
       , 'c_count_dist_task_total'
       , 'ISC_MEASURE_22'
       , 'N'
       ) || '
, ' || isc_fs_rpt_util_pkg.change_column
       ( isc_fs_rpt_util_pkg.rate_column
         ( 'c_tot_trv_dist_total'
         , 'c_count_dist_task_total'
         , null
         , 'N'
         )
       , isc_fs_rpt_util_pkg.rate_column
         ( 'p_tot_trv_dist_total'
         , 'p_count_dist_task_total'
         , null
         , 'N'
         )
       , 'ISC_MEASURE_23'
       , 'N'
       ) ||'
, nvl(c_count_dist_task_total,0) ISC_MEASURE_24
' || poa_dbi_util_pkg.get_bucket_outer_query
     ( p_bucket_rec => l_bucket_rec
     , p_col_name   => 'bucket'
     , p_alias_name => 'ISC_MEASURE_25'
     , p_prefix     => 'nvl(c_'
     , p_suffix     => ',0)/abs(decode(c_count_dist_task_total,0,null,c_count_dist_task_total))*100'
     , p_total_flag => 'Y'
     ) || '
from ' || poa_dbi_template_pkg.status_sql
          ( p_fact_name            => l_mv
          , p_where_clause         => l_where_clause
          , p_join_tables          => l_join_tbl
          , p_use_windowing        => 'Y' --'N'
          , p_col_name             => l_col_tbl
          , p_use_grpid            => 'N'
          , p_paren_count          => 3
          , p_filter_where         => 'isc_measure_4 <> 0 ) iset '
          , p_generate_viewby      => 'Y'
          );