DBA Data[Home] [Help]

APPS.ISC_DBI_OT_ARR_RT_PKG SQL Statements

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

Line: 148

     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);
Line: 171

        l_view_by_fact_col := isc_dbi_sutil_pkg.get_fact_select_columns (p_join_tbl);
Line: 175

        'SELECT
        ' || isc_dbi_sutil_pkg.get_view_by_select_clause (p_view_by_dim)
          || 'oset.ISC_MEASURE_1	ISC_MEASURE_1,
        ' || 'oset.ISC_MEASURE_2 	ISC_MEASURE_2,
        ' || 'oset.ISC_MEASURE_3 	ISC_MEASURE_3,
        ' || 'oset.ISC_MEASURE_4	ISC_MEASURE_4,
        ' || 'oset.ISC_MEASURE_5	ISC_MEASURE_5,
        ' || 'oset.ISC_MEASURE_6	ISC_MEASURE_6,
        ' || 'oset.ISC_MEASURE_7	ISC_MEASURE_7,
        ' || 'oset.ISC_MEASURE_8	ISC_MEASURE_8,
        ' || 'oset.ISC_MEASURE_9	ISC_MEASURE_9,
        ' || 'oset.ISC_MEASURE_10	ISC_MEASURE_10,
        ' || 'oset.ISC_MEASURE_11	ISC_MEASURE_11,
        ' || 'oset.ISC_MEASURE_12 	ISC_MEASURE_12,
        ' || 'oset.ISC_MEASURE_13 	ISC_MEASURE_13,
        ' || 'oset.ISC_MEASURE_14	ISC_MEASURE_14,
        ' || 'oset.ISC_MEASURE_15	ISC_MEASURE_15,
        ' || 'oset.ISC_MEASURE_16	ISC_MEASURE_16,
        ' || 'oset.ISC_MEASURE_17	ISC_MEASURE_17,
        ' || 'oset.ISC_MEASURE_18	ISC_MEASURE_18,
        ' || 'oset.ISC_MEASURE_19	ISC_MEASURE_19,
        ' || 'oset.ISC_MEASURE_20	ISC_MEASURE_20,
        ' || 'oset.ISC_MEASURE_22	ISC_MEASURE_22
        ' || '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,ISC_MEASURE_20,ISC_MEASURE_22
        ' || 'FROM
        ' || '(SELECT
            ' || l_view_by_fact_col || ',
	nvl(c_trip_arrivals,0) 		ISC_MEASURE_1,
	nvl(p_stop_arrivals,0) 		ISC_MEASURE_3,
	nvl(c_stop_arrivals,0) 		ISC_MEASURE_4,
	nvl(c_ot_arrivals,0) 		ISC_MEASURE_6,
	nvl(c_late_arrivals,0) 		ISC_MEASURE_7,
	nvl(c_early_arrivals,0)	 	ISC_MEASURE_8,
	nvl(c_trip_arrivals_total,0)	ISC_MEASURE_12,
	nvl(c_stop_arrivals_total,0)	ISC_MEASURE_14,
	nvl(c_early_arrivals_total,0)	ISC_MEASURE_16,
	nvl(c_late_arrivals_total,0)	ISC_MEASURE_17,
	nvl(c_ot_arrivals_total,0)	ISC_MEASURE_18,
            ' || isc_dbi_sutil_pkg.change_str (
                    p_new_numerator     => 'c_trip_arrivals',
                    p_old_numerator     => 'p_trip_arrivals',
                    p_denominator       => 'p_trip_arrivals',
                    p_measure_name      => 'ISC_MEASURE_2') || ', -- Trip Arrivals Change
            ' || isc_dbi_sutil_pkg.change_str (
                    p_new_numerator     => 'c_stop_arrivals',
                    p_old_numerator     => 'p_stop_arrivals',
                    p_denominator       => 'p_stop_arrivals',
                    p_measure_name      => 'ISC_MEASURE_5') || ', -- Trip Stop Arrivals Change
            ' || isc_dbi_sutil_pkg.rate_str (
                    p_numerator     => 'p_ot_arrivals',
                    p_denominator   => 'p_stop_arrivals',
                    p_rate_type     => 'PERCENT',
                    p_measure_name  => 'ISC_MEASURE_9') || ', -- OT Arrival Rate Prior
            ' || isc_dbi_sutil_pkg.rate_str (
                    p_numerator     => 'p_ot_arrivals_total',
                    p_denominator   => 'p_stop_arrivals_total',
                    p_rate_type     => 'PERCENT',
                    p_measure_name  => 'ISC_MEASURE_22') || ', -- Grand OT Arrival Rate Prior
            ' || isc_dbi_sutil_pkg.rate_str (
                    p_numerator     => 'c_ot_arrivals',
                    p_denominator   => 'c_stop_arrivals',
                    p_rate_type     => 'PERCENT',
                    p_measure_name  => 'ISC_MEASURE_10') || ', -- OT Arrival Rate
            ' || isc_dbi_sutil_pkg.change_rate_str (
                    p_new_numerator     => 'c_ot_arrivals',
                    p_new_denominator   => 'c_stop_arrivals',
                    p_old_numerator     => 'p_ot_arrivals',
                    p_old_denominator   => 'p_stop_arrivals',
                    p_rate_type         => 'PERCENT',
                    p_measure_name      => 'ISC_MEASURE_11') || ', -- OT Arrival Rate Change
            ' || isc_dbi_sutil_pkg.change_str (
                    p_new_numerator     => 'c_trip_arrivals_total',
                    p_old_numerator     => 'p_trip_arrivals_total',
                    p_denominator       => 'p_trip_arrivals_total',
                    p_measure_name      => 'ISC_MEASURE_13') || ', -- Grand Total Trip Arrivals Change
            ' || isc_dbi_sutil_pkg.change_str (
                    p_new_numerator     => 'c_stop_arrivals_total',
                    p_old_numerator     => 'p_stop_arrivals_total',
                    p_denominator       => 'p_stop_arrivals_total',
                    p_measure_name      => 'ISC_MEASURE_15') || ', -- Grand Total Trip Stop Arrivals Change
            ' || isc_dbi_sutil_pkg.rate_str (
                    p_numerator     => 'c_ot_arrivals_total',
                    p_denominator   => 'c_stop_arrivals_total',
                    p_rate_type     => 'PERCENT',
                    p_measure_name  => 'ISC_MEASURE_19') || ', -- Grand Total OT Arrival Rate
            ' || isc_dbi_sutil_pkg.change_rate_str (
                    p_new_numerator     => 'c_ot_arrivals_total',
                    p_new_denominator   => 'c_stop_arrivals_total',
                    p_old_numerator     => 'p_ot_arrivals_total',
                    p_old_denominator   => 'p_stop_arrivals_total',
                    p_rate_type         => 'PERCENT',
                    p_measure_name      => 'ISC_MEASURE_20'); -- Grand Total OT Arrival Rate Change