[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Description: builds the outer select clause
---------------------------------------------------*/
FUNCTION get_rpt_sel_clause(p_view_by_dim IN VARCHAR2,
p_join_tbl IN
poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
RETURN VARCHAR2
IS
l_sel_clause VARCHAR2(32000);
l_view_by_fact_col := isc_dbi_sutil_pkg.get_fact_select_columns (p_join_tbl);
'SELECT
' || isc_dbi_sutil_pkg.get_view_by_select_clause (p_view_by_dim)
|| 'oset.ISC_MEASURE_9 ISC_MEASURE_9,
' || 'oset.ISC_MEASURE_1 ISC_MEASURE_1,
' || 'oset.ISC_MEASURE_12 ISC_MEASURE_12,
' || 'oset.ISC_MEASURE_2 ISC_MEASURE_2,
' || 'oset.ISC_MEASURE_13 ISC_MEASURE_13,
' || 'oset.ISC_MEASURE_3 ISC_MEASURE_3,
' || 'oset.ISC_MEASURE_14 ISC_MEASURE_14,
' || 'oset.ISC_MEASURE_10 ISC_MEASURE_10,
' || 'oset.ISC_MEASURE_4 ISC_MEASURE_4,
' || 'oset.ISC_MEASURE_15 ISC_MEASURE_15,
' || 'oset.ISC_MEASURE_5 ISC_MEASURE_5,
' || 'oset.ISC_MEASURE_16 ISC_MEASURE_16,
' || 'oset.ISC_MEASURE_6 ISC_MEASURE_6,
' || 'oset.ISC_MEASURE_17 ISC_MEASURE_17,
' || 'oset.ISC_MEASURE_11 ISC_MEASURE_11,
' || 'oset.ISC_MEASURE_7 ISC_MEASURE_7,
' || 'oset.ISC_MEASURE_18 ISC_MEASURE_18,
' || 'oset.ISC_MEASURE_8 ISC_MEASURE_8,
' || 'oset.ISC_MEASURE_19 ISC_MEASURE_19
' || 'FROM
' || '(SELECT (rank () over
' || ' (&ORDER_BY_CLAUSE nulls last,
' || l_view_by_fact_col || ')) - 1 rnk,
' || l_view_by_fact_col || ',
' || 'ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,
ISC_MEASURE_6,ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9,ISC_MEASURE_10,
ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_14,ISC_MEASURE_15,
ISC_MEASURE_16,ISC_MEASURE_17,ISC_MEASURE_18,ISC_MEASURE_19
' || 'FROM
' || '(SELECT
' || l_view_by_fact_col || ',
nvl(c_freight_cost,0) ISC_MEASURE_1,
nvl(p_freight_cost,0) ISC_MEASURE_9,
nvl(c_freight_cost_total,0) ISC_MEASURE_12,
nvl(p_freight_distance,0) ISC_MEASURE_10,
nvl(c_freight_distance,0) ISC_MEASURE_4,
nvl(c_freight_distance_total,0) ISC_MEASURE_15,
' || isc_dbi_sutil_pkg.change_str (
p_new_numerator => 'c_freight_cost',
p_old_numerator => 'p_freight_cost',
p_denominator => 'p_freight_cost',
p_measure_name => 'ISC_MEASURE_2') || ', -- Rated Fr Cost Change
' || isc_dbi_sutil_pkg.change_str (
p_new_numerator => 'c_freight_cost_total',
p_old_numerator => 'p_freight_cost_total',
p_denominator => 'p_freight_cost_total',
p_measure_name => 'ISC_MEASURE_13') || ', -- Grand Total Rated Fr Cost Change
' || isc_dbi_sutil_pkg.rate_str (
p_numerator => 'c_freight_cost',
p_denominator => 'c_freight_cost_total',
p_rate_type => 'PERCENT',
p_measure_name => 'ISC_MEASURE_3') || ', -- Percent of Total Fr Cost
' || isc_dbi_sutil_pkg.rate_str (
p_numerator => 'c_freight_cost_total',
p_denominator => 'c_freight_cost_total',
p_rate_type => 'PERCENT',
p_measure_name => 'ISC_MEASURE_14') || ', -- Grand total Percent of Total Fr Cost
' || isc_dbi_sutil_pkg.change_str (
p_new_numerator => 'c_freight_distance',
p_old_numerator => 'p_freight_distance',
p_denominator => 'p_freight_distance',
p_measure_name => 'ISC_MEASURE_5') || ', -- Rated Fr Distance Change
' || isc_dbi_sutil_pkg.change_str (
p_new_numerator => 'c_freight_distance_total',
p_old_numerator => 'p_freight_distance_total',
p_denominator => 'p_freight_distance_total',
p_measure_name => 'ISC_MEASURE_16') || ', -- Grand Total Rated Fr Distance Change
' || isc_dbi_sutil_pkg.rate_str (
p_numerator => 'c_freight_distance',
p_denominator => 'c_freight_distance_total',
p_rate_type => 'PERCENT',
p_measure_name => 'ISC_MEASURE_6') || ', -- Percent of Total Fr Distance
' || isc_dbi_sutil_pkg.rate_str (
p_numerator => 'c_freight_cost_total',
p_denominator => 'c_freight_cost_total',
p_rate_type => 'PERCENT',
p_measure_name => 'ISC_MEASURE_17') || ', -- Grand total Percent of total Fr Cost
' || isc_dbi_sutil_pkg.rate_str (
p_numerator => 'p_freight_cost',
p_denominator => 'p_freight_distance',
p_rate_type => 'RATIO',
p_measure_name => 'ISC_MEASURE_11') || ', -- Fr Cost per Distance Prior
' || isc_dbi_sutil_pkg.rate_str (
p_numerator => 'c_freight_cost',
p_denominator => 'c_freight_distance',
p_rate_type => 'RATIO',
p_measure_name => 'ISC_MEASURE_7') || ', -- Fr Cost per Distance
' || isc_dbi_sutil_pkg.rate_str (
p_numerator => 'c_freight_cost_total',
p_denominator => 'c_freight_distance_total',
p_rate_type => 'RATIO',
p_measure_name => 'ISC_MEASURE_18') || ', -- Grand Total Fr Cost per Distance
' || isc_dbi_sutil_pkg.change_rate_str (
p_new_numerator => 'c_freight_cost',
p_new_denominator => 'c_freight_distance',
p_old_numerator => 'p_freight_cost',
p_old_denominator => 'p_freight_distance',
p_rate_type => 'RATIO',
p_measure_name => 'ISC_MEASURE_8') || ', -- Fr Cost per Distance Change
' || isc_dbi_sutil_pkg.change_rate_str (
p_new_numerator => 'c_freight_cost_total',
p_new_denominator => 'c_freight_distance_total',
p_old_numerator => 'p_freight_cost_total',
p_old_denominator => 'p_freight_distance_total',
p_rate_type => 'RATIO',
p_measure_name => 'ISC_MEASURE_19'); -- Grand Total Fr Cost per Distance Change