The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
f.record_type
,f.parent_district_id
,f.district_id
,district_id_c
,f.task_type_id
,f.time_id
,f.period_type_id
,f.actual_effort_hrs Labor_Hrs
,f.actual_travel_duration_hrs Travel_Hrs
,nvl(f.actual_effort_hrs,0) + nvl(f.actual_travel_duration_hrs,0) Total_Act_Hrs
from isc_fs_013_mv f
where f.grp_id = &ISC_GRP_ID)';
select
f.record_type
,f.parent_district_id
,f.district_id
,district_id_c
,-999 task_type_id
,f.time_id
,f.period_type_id
,f.planned_hrs Total_Planned_Hrs
from isc_fs_014_mv f)';
l_viewby_select varchar2(400); -- needed to be increased from 200
, 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
);
l_stmt := 'select
' || l_viewby_select || '
, ISC_MEASURE_1
, ISC_MEASURE_2
, ISC_MEASURE_3
, ISC_MEASURE_13
, ISC_MEASURE_6
, ISC_MEASURE_10
, ISC_MEASURE_5
, ISC_MEASURE_7
, ISC_MEASURE_8
, ISC_MEASURE_9
, ISC_MEASURE_11
, ISC_MEASURE_12
, ISC_MEASURE_15
, ISC_MEASURE_16
, ISC_MEASURE_17
, ISC_MEASURE_24
, ISC_MEASURE_18
, ISC_MEASURE_19
, ISC_MEASURE_20
, ISC_MEASURE_21
, ISC_MEASURE_22
, ISC_MEASURE_23
, ' || isc_fs_rpt_util_pkg.get_district_drill_down
( l_view_by
, 'ISC_FS_TECH_UTL_TBL_REP'
, 'ISC_ATTRIBUTE_2' ) || '
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 ' || isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || '
, ' || isc_fs_rpt_util_pkg.rate_column
( 'p_Total_Act_Hrs'
, l_prev_planned_hours
, 'ISC_MEASURE_1'
) || '
, ' || isc_fs_rpt_util_pkg.rate_column
( 'c_Total_Act_Hrs'
, l_cur_planned_hours
, 'ISC_MEASURE_2'
) || '
, ' || isc_fs_rpt_util_pkg.change_column
( isc_fs_rpt_util_pkg.rate_column
( 'c_Total_Act_Hrs'
, l_cur_planned_hours
, null
)
, isc_fs_rpt_util_pkg.rate_column
( 'p_Total_Act_Hrs'
, l_prev_planned_hours
, null
)
, 'ISC_MEASURE_3'
, 'N'
) || '
, nvl(c_Total_Planned_Hrs,0) ISC_MEASURE_13
, ' || isc_fs_rpt_util_pkg.rate_column
( 'p_Labor_Hrs'
, l_prev_planned_hours
, 'ISC_MEASURE_6'
) || '
, ' || isc_fs_rpt_util_pkg.rate_column
( 'p_Travel_Hrs'
, l_cur_planned_hours
, 'ISC_MEASURE_10'
) || '
, nvl(c_Labor_Hrs,0) ISC_MEASURE_5
, ' || isc_fs_rpt_util_pkg.rate_column
( 'c_Labor_Hrs'
, l_cur_planned_hours
, 'ISC_MEASURE_7'
) || '
, ' || isc_fs_rpt_util_pkg.change_column
( isc_fs_rpt_util_pkg.rate_column
( 'c_Labor_Hrs'
, l_cur_planned_hours
, null
)
, isc_fs_rpt_util_pkg.rate_column
( 'p_Labor_Hrs'
, l_prev_planned_hours
, null
)
, 'ISC_MEASURE_8'
, 'N'
) || '
, nvl(c_Travel_Hrs,0) ISC_MEASURE_9
, ' || isc_fs_rpt_util_pkg.rate_column
( 'c_Travel_Hrs'
, l_cur_planned_hours
, 'ISC_MEASURE_11'
) || '
, ' || isc_fs_rpt_util_pkg.change_column
( isc_fs_rpt_util_pkg.rate_column
( 'c_Travel_Hrs'
, l_cur_planned_hours
, null
)
, isc_fs_rpt_util_pkg.rate_column
( 'p_Travel_Hrs'
, l_prev_planned_hours
, null
)
, 'ISC_MEASURE_12'
, 'N'
) || '
, ' || isc_fs_rpt_util_pkg.rate_column
( 'p_Total_Act_Hrs_total'
, 'p_Total_Planned_Hrs_total'
, 'ISC_MEASURE_15'
) || '
, ' || isc_fs_rpt_util_pkg.rate_column
( 'c_Total_Act_Hrs_total'
, 'c_Total_Planned_Hrs_total'
, 'ISC_MEASURE_16'
) || '
, ' || isc_fs_rpt_util_pkg.change_column
( isc_fs_rpt_util_pkg.rate_column
( 'c_Total_Act_Hrs_total'
, 'c_Total_Planned_Hrs_total'
, null
)
, isc_fs_rpt_util_pkg.rate_column
( 'p_Total_Act_Hrs_total'
, 'p_Total_Planned_Hrs_total'
, null
)
, 'ISC_MEASURE_17'
, 'N'
) || '
, nvl(c_Total_Planned_Hrs_total,0) ISC_MEASURE_24
, nvl(c_Labor_Hrs_total,0) ISC_MEASURE_18
, ' || isc_fs_rpt_util_pkg.rate_column
( 'c_Labor_Hrs_total'
, 'c_Total_Planned_Hrs_total'
, 'ISC_MEASURE_19'
) || '
, ' || isc_fs_rpt_util_pkg.change_column
( isc_fs_rpt_util_pkg.rate_column
( 'c_Labor_Hrs_total'
, 'c_Total_Planned_Hrs_total'
, null
)
, isc_fs_rpt_util_pkg.rate_column
( 'p_Labor_Hrs_total'
, 'p_Total_Planned_Hrs_total'
, null
)
, 'ISC_MEASURE_20'
, 'N'
) || '
, nvl(c_Travel_Hrs_total,0) ISC_MEASURE_21
, ' || isc_fs_rpt_util_pkg.rate_column
( 'c_Travel_Hrs_total'
, 'c_Total_Planned_Hrs_total'
, 'ISC_MEASURE_22'
) || '
, ' || isc_fs_rpt_util_pkg.change_column
( isc_fs_rpt_util_pkg.rate_column
( 'c_Travel_Hrs_total'
, 'c_Total_Planned_Hrs_total'
, null
)
, isc_fs_rpt_util_pkg.rate_column
( 'p_Travel_Hrs_total'
, 'p_Total_Planned_Hrs_total'
, null
)
, 'ISC_MEASURE_23'
, 'N'
) || '
from (' || l_stmt;
l_viewby_select varchar2(400); -- needed to be increased from 200
, 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
);
l_stmt := 'select
cal_name VIEWBY ' || '
, ' || isc_fs_rpt_util_pkg.rate_column
( 'p_Total_Act_Hrs'
, 'p_Total_Planned_Hrs'
, 'ISC_MEASURE_1'
) || '
, ' || isc_fs_rpt_util_pkg.rate_column
( 'c_Total_Act_Hrs'
, 'c_Total_Planned_Hrs'
, 'ISC_MEASURE_2'
) || '
, ' || isc_fs_rpt_util_pkg.change_column
( isc_fs_rpt_util_pkg.rate_column
( 'c_Total_Act_Hrs'
, 'c_Total_Planned_Hrs'
, null
)
, isc_fs_rpt_util_pkg.rate_column
( 'p_Total_Act_Hrs'
, 'p_Total_Planned_Hrs'
, null
)
, 'ISC_MEASURE_3'
, 'N'
) || '
, ' || isc_fs_rpt_util_pkg.rate_column
( 'p_Labor_Hrs'
, 'p_Total_Planned_Hrs'
, 'ISC_MEASURE_6'
) || '
, ' || isc_fs_rpt_util_pkg.rate_column
( 'c_Labor_Hrs'
, 'c_Total_Planned_Hrs'
, 'ISC_MEASURE_7'
) || '
, ' || isc_fs_rpt_util_pkg.change_column
( isc_fs_rpt_util_pkg.rate_column
( 'c_Labor_Hrs'
, 'c_Total_Planned_Hrs'
, null
)
, isc_fs_rpt_util_pkg.rate_column
( 'p_Labor_Hrs'
, 'p_Total_Planned_Hrs'
, null
)
, 'ISC_MEASURE_8'
, 'N'
) || '
, ' || isc_fs_rpt_util_pkg.rate_column
( 'p_Travel_Hrs'
, 'p_Total_Planned_Hrs'
, 'ISC_MEASURE_10'
) || '
, ' || isc_fs_rpt_util_pkg.rate_column
( 'c_Travel_Hrs'
, 'c_Total_Planned_Hrs'
, 'ISC_MEASURE_11'
) || '
, ' || isc_fs_rpt_util_pkg.change_column
( isc_fs_rpt_util_pkg.rate_column
( 'c_Travel_Hrs'
, 'c_Total_Planned_Hrs'
, null
)
, isc_fs_rpt_util_pkg.rate_column
( 'p_Travel_Hrs'
, 'p_Total_Planned_Hrs'
, null
)
, 'ISC_MEASURE_12'
, 'N'
) ||
isc_fs_rpt_util_pkg.get_trend_drill
( l_xtd
, 'ISC_FS_TECH_UTL_TRD_REP'
, 'ISC_ATTRIBUTE_1'
, 'ISC_ATTRIBUTE_2'
, p_override_end_date => 'cal_end_date'
) || '
from
' || l_stmt;