APPS.BSC_COLOR_UPGRADE SQL Statements

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

Line: 11

    SELECT DISTINCT indicator, analysis_option0, analysis_option1, analysis_option2, series_id
    FROM bsc_kpi_analysis_measures_b
    WHERE kpi_measure_id IS NULL
    ORDER BY indicator, analysis_option0, analysis_option1, analysis_option2, series_id;

Line: 23

      SELECT bsc_kpi_measure_s.NEXTVAL INTO l_id from dual;

Line: 24

      UPDATE bsc_kpi_analysis_measures_b
        SET kpi_measure_id = l_id
        WHERE indicator = l_kpi_measure_rec.indicator
        AND   analysis_option0 = l_kpi_measure_rec.analysis_option0
        AND   analysis_option1 = l_kpi_measure_rec.analysis_option1
        AND   analysis_option2 = l_kpi_measure_rec.analysis_option2
        AND   series_id = l_kpi_measure_rec.series_id
        AND   kpi_measure_id IS NULL;

Line: 59

  UPDATE bsc_kpis_b
    SET color_rollup_type = 'DEFAULT_KPI',
        last_update_date = SYSDATE,
        last_updated_by = FND_GLOBAL.USER_ID
    WHERE color_rollup_type IS NULL;

Line: 82

  UPDATE bsc_kpis_b
    SET prototype_color_id = DECODE(prototype_color,
                                    'G', 24865,
                                    'Y', 49919,
                                    'R', 192,
                                    'X', 8421504,
                                    8421504
                                    ),
        last_update_date = SYSDATE,
        last_updated_by = FND_GLOBAL.USER_ID
    WHERE prototype_color_id IS NULL;

Line: 116

    SELECT prototype_color_id
      INTO l_prototype_color_id
      FROM bsc_kpis_b
      WHERE indicator = p_objective_id;

Line: 143

    SELECT property_value
      INTO l_color_by_total
      FROM bsc_kpi_properties
      WHERE indicator = p_objective_id
      AND   property_code = 'COLOR_BY_TOTAL';

Line: 170

    SELECT source
      FROM bsc_sys_datasets_b dts,
           bsc_kpi_analysis_measures_b am
      WHERE dts.dataset_id = am.dataset_id
      AND   am.kpi_measure_id = pkpi_measure_id;

Line: 177

    SELECT short_name
      FROM bsc_kpis_b
      WHERE indicator = pIndicator;

Line: 235

    SELECT indicator_type
      FROM bsc_kpis_b
      WHERE indicator = p_indicator;

Line: 254

    SELECT budget_flag INTO l_apply_color_flag
      FROM bsc_kpi_analysis_measures_b
      WHERE indicator = p_objective_id
      AND   kpi_measure_id = p_kpi_measure_id;

Line: 260

    SELECT apply_color_flag INTO l_apply_color_flag
      FROM bsc_kpis_b
      WHERE indicator = p_objective_id;

Line: 282

    SELECT indicator, kpi_measure_id
    FROM bsc_kpi_analysis_measures_b
    ORDER BY kpi_measure_id;

Line: 288

    SELECT COUNT(1)
    FROM bsc_kpi_measure_props
    WHERE indicator = p_indicator
    AND   kpi_measure_id = p_kpi_measure_id;

Line: 328

        l_kpi_measure_props_rec.last_updated_by := FND_GLOBAL.USER_ID;

Line: 329

        l_kpi_measure_props_rec.last_update_date := SYSDATE;

Line: 330

        l_kpi_measure_props_rec.last_update_login := FND_GLOBAL.LOGIN_ID;

Line: 409

    SELECT DISTINCT indicator objective_id
         , prototype_flag prototype_flag
    FROM   bsc_kpis_b
    WHERE  prototype_flag <> 2;

Line: 415

  l_update_flag             BOOLEAN;

Line: 424

      l_update_flag := FALSE;

Line: 426

      SELECT COUNT(1)
        INTO  l_count
        FROM  bsc_sys_kpi_colors
        WHERE kpi_measure_id IS NOT NULL
        AND   indicator = l_obj_prototype_flag_rec.objective_id;

Line: 433

        l_update_flag := TRUE;  -- either first time upgrade or no color has been calculated as yet.

Line: 436

      IF l_update_flag THEN

        l_default_kpi_measure_id := BSC_COLOR_CALC_UTIL.Get_Default_Kpi_Measure_Id(l_obj_prototype_flag_rec.objective_id);

Line: 452

          UPDATE bsc_kpi_analysis_measures_b
          SET prototype_flag = DECODE(l_obj_prototype_flag_rec.prototype_flag,
                                         0, 0,
                                         7)
            WHERE indicator = l_obj_prototype_flag_rec.objective_id
            AND   kpi_measure_id = l_default_kpi_measure_id;

Line: 459

          UPDATE bsc_kpi_analysis_measures_b
            SET prototype_flag = 7
            WHERE indicator = l_obj_prototype_flag_rec.objective_id
            AND   kpi_measure_id <> l_default_kpi_measure_id;

Line: 466

          UPDATE bsc_kpi_analysis_measures_b
            SET prototype_flag = 7
            WHERE indicator = l_obj_prototype_flag_rec.objective_id;

Line: 502

    SELECT indicator objective_id
         , calculation_id
    FROM bsc_kpi_calculations
    WHERE default_value = 1;

Line: 522

        UPDATE bsc_kpi_measure_props
          SET default_calculation = l_obj_default_calc_rec.calculation_id,
              last_update_date = SYSDATE,
              last_updated_by = FND_GLOBAL.USER_ID
          WHERE indicator = l_obj_default_calc_rec.objective_id
          AND   kpi_measure_id = l_default_kpi_measure_id
          AND   default_calculation IS NULL;

Line: 558

    SELECT DISTINCT indicator
    FROM bsc_sys_kpi_colors
    ORDER BY indicator;

Line: 575

        UPDATE bsc_sys_kpi_colors
          SET kpi_measure_id = l_default_kpi_measure_id
          WHERE indicator = l_default_kpi_color.indicator
          AND   kpi_measure_id IS NULL;

Line: 609

    SELECT COUNT(1)
    FROM bsc_sys_objective_colors;

Line: 613

    SELECT COUNT(1)
    FROM bsc_sys_kpi_colors;

Line: 645

        INSERT INTO
          bsc_sys_objective_colors
          ( tab_id
          , indicator
          , dim_level1
          , dim_level2
          , dim_level3
          , dim_level4
          , dim_level5
          , dim_level6
          , dim_level7
          , dim_level8
          , period_id
          , obj_color
          , obj_trend
          , driving_kpi_measure_id
          )
          SELECT
            tab_id
          , indicator
          , dim_level1
          , dim_level2
          , dim_level3
          , dim_level4
          , dim_level5
          , dim_level6
          , dim_level7
          , dim_level8
          , period_id
          , kpi_color
          , kpi_trend
          , kpi_measure_id
          FROM bsc_sys_kpi_colors
          ORDER BY tab_id, indicator;

Line: 814

    SELECT bis_ind.short_name short_name,
           bsc_dts.name name,
           bsc_dts.help description,
           bsc_dts.dataset_id dataset_id,
           bis_ind.actual_data_source actual_data_source
    FROM bis_indicators bis_ind,
         bsc_sys_datasets_vl bsc_dts
    WHERE bis_ind.dataset_id = bsc_dts.dataset_id
    AND   bis_ind.measure_type = 'CDS_CALC';

Line: 825

    SELECT attribute8
    FROM ak_regions
    WHERE region_code = p_region_code
    AND   attribute10 = 'BSC_DATA_SOURCE'
    AND   attribute8 IS NOT NULL;

Line: 907

SELECT property_code, property_value, created_by,
       creation_date, last_updated_by, last_update_date, last_update_login
FROM bsc_sys_init
WHERE property_code IN ('LGREEN_COLOR', 'GREEN_COLOR', 'LYELLOW_COLOR'
                        ,'YELLOW_COLOR', 'LRED_COLOR', 'RED_COLOR'
                        ,'LGRAY_COLOR', 'DGRAY_COLOR');

Line: 917

        UPDATE bsc_sys_colors_b
        SET    user_forecast_color = c_init_colors.property_value,
               last_update_date = SYSDATE,
               last_updated_by = FND_GLOBAL.USER_ID
        WHERE  perf_sequence = (SELECT
               MIN(perf_sequence) FROM bsc_sys_colors_b);

Line: 924

        UPDATE bsc_sys_colors_b
        SET    user_color = c_init_colors.property_value,
               last_update_date = SYSDATE,
               last_updated_by = FND_GLOBAL.USER_ID
        WHERE  perf_sequence = (SELECT
               MIN(perf_sequence) FROM bsc_sys_colors_b);

Line: 931

        UPDATE bsc_sys_colors_b
        SET    user_forecast_color = c_init_colors.property_value,
               last_update_date = SYSDATE,
               last_updated_by = FND_GLOBAL.USER_ID
        WHERE  short_name = 'AVERAGE_COLOR';

Line: 937

        UPDATE bsc_sys_colors_b
        SET    user_color = c_init_colors.property_value,
               last_update_date = SYSDATE,
               last_updated_by = FND_GLOBAL.USER_ID
        WHERE  short_name = 'AVERAGE_COLOR';

Line: 943

        UPDATE bsc_sys_colors_b
        SET    user_forecast_color = c_init_colors.property_value,
               last_update_date = SYSDATE,
               last_updated_by = FND_GLOBAL.USER_ID
        WHERE  perf_sequence = (SELECT
               MAX(perf_sequence) FROM bsc_sys_colors_b);

Line: 950

        UPDATE bsc_sys_colors_b
        SET    user_color = c_init_colors.property_value,
               last_update_date = SYSDATE,
               last_updated_by = FND_GLOBAL.USER_ID
        WHERE  perf_sequence = (SELECT
               MAX(perf_sequence) FROM bsc_sys_colors_b);

Line: 957

          UPDATE bsc_sys_colors_b
          SET    user_forecast_color = c_init_colors.property_value,
                 last_update_date = SYSDATE,
                 last_updated_by = FND_GLOBAL.USER_ID
          WHERE  perf_sequence IS NULL;

Line: 963

          UPDATE bsc_sys_colors_b
          SET    user_color = c_init_colors.property_value,
                 last_update_date = SYSDATE,
                 last_updated_by = FND_GLOBAL.USER_ID
          WHERE  perf_sequence IS NULL;

Line: 980

    DELETE bsc_sys_init
    WHERE property_code IN ('LGREEN_COLOR', 'GREEN_COLOR', 'LYELLOW_COLOR'
                           ,'YELLOW_COLOR', 'LRED_COLOR', 'RED_COLOR'
                           ,'LGRAY_COLOR', 'DGRAY_COLOR');

Line: 986

       BSC_MESSAGE.Add( x_message => 'set_kpi_measure_ids() failed for delete of old system colors :-' ||SQLERRM
                     , x_source  => 'BSCCOLUB.pls'
                     , x_mode    => 'I'
                     );

Line: 1103

SELECT indicator, config_type
FROM   bsc_kpis_b
WHERE  prototype_flag <> 2;

Line: 1108

SELECT  an.kpi_measure_id
       ,ds.color_method
FROM   bsc_kpi_analysis_measures_b an
      ,bsc_sys_datasets_b ds
WHERE an.dataset_id = ds.dataset_id
AND   an.indicator = l_indicator
AND   NOT EXISTS (SELECT
      NULL from bsc_color_type_props p
      WHERE p.kpi_measure_id = an.kpi_measure_id
      );

Line: 1120

SELECT property_code, property_value
FROM   bsc_kpi_properties
WHERE  property_code in ('COL_M1_LEVEL1', 'COL_M1_LEVEL2', 'COL_M2_LEVEL1', 'COL_M2_LEVEL2',
                         'COL_M3_LEVEL1', 'COL_M3_LEVEL2', 'COL_M3_LEVEL3', 'COL_M3_LEVEL4')
AND indicator = l_indicator;

Line: 1189

  /*DELETE  bsc_kpi_properties
  WHERE  property_code in ('COL_M1_LEVEL1', 'COL_M1_LEVEL2', 'COL_M2_LEVEL1', 'COL_M2_LEVEL2',
                         'COL_M3_LEVEL1', 'COL_M3_LEVEL2', 'COL_M3_LEVEL3', 'COL_M3_LEVEL4');*/

Line: 1210

    SELECT indicator
      FROM   bsc_kpis_b
      WHERE  config_type = 7
      AND    prototype_flag <> 2
      AND    short_name IS NULL;

Line: 1226

      BSC_PMF_UI_WRAPPER.Delete_Kpi
      ( p_commit              => FND_API.G_FALSE
      , p_kpi_id              => cd.indicator
      , x_return_status       => l_return_status
      , x_msg_count           => l_msg_count
      , x_msg_data            => l_msg_data
      );

Line: 1249

  DELETE FROM bsc_sys_files
    WHERE file_type = 'F1'
    AND INDICATOR = 0;

Line: 1271

SELECT  comment_id
       ,indicator
       ,trend_flag
FROM   bsc_kpi_comments
WHERE nvl(trend_flag,0)<10
AND   trend_flag <>0
AND   indicator IS NOT NULL;

Line: 1317

       UPDATE bsc_kpi_comments
       SET    color_flag=l_color, trend_flag=l_new_trend
       WHERE  comment_id = c_kpi_comm.comment_id
       AND    indicator  = c_kpi_comm.indicator;