APPS.BSC_COLOR_RANGES_PUB SQL Statements

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

Line: 60

  SELECT indicator
  FROM   bsc_kpis_b
  WHERE  source_indicator = p_objective_id
  AND    share_flag = 2 -- shared objective.
  AND    prototype_flag <> 2;

Line: 71

  SELECT BSC_COLOR_RANGE_ID_S.NEXTVAL
  INTO   l_range_id
  FROM   DUAL;

Line: 110

      SELECT BSC_COLOR_RANGE_ID_S.NEXTVAL
      INTO   l_range_id
      FROM   DUAL;

Line: 182

  SELECT indicator
  FROM   bsc_kpis_b
  WHERE  source_indicator = p_objective_id
  AND    share_flag = 2 -- shared objective.
  AND    prototype_flag <> 2;

Line: 201

  SELECT BSC_COLOR_RANGE_ID_S.NEXTVAL
  INTO   l_range_id
  FROM   DUAL;

Line: 243

      SELECT am.analysis_option0,
             am.analysis_option1, am.analysis_option2, am.series_id
      INTO   l_an_opt0, l_an_opt1, l_an_opt2, l_series_id
      FROM   bsc_kpi_analysis_measures_b am
      WHERE  am.indicator = p_objective_id
      AND    am.kpi_measure_id = p_kpi_measure_id;

Line: 254

      SELECT BSC_COLOR_RANGE_ID_S.NEXTVAL
      INTO   l_range_id
      FROM   DUAL;

Line: 259

        SELECT kpi_measure_id
        INTO   l_kpi_measure_id
        FROM   bsc_kpi_analysis_measures_b
        WHERE  indicator= c_shared.indicator
        AND    analysis_option0 = l_an_opt0
        AND    analysis_option1 = l_an_opt1
        AND    analysis_option2 = l_an_opt2
        AND    series_id        = l_series_id;

Line: 402

    SELECT ds.color_method
    INTO   l_color_method
    FROM   bsc_sys_datasets_b ds
          ,bsc_kpi_analysis_measures_b am
    WHERE  ds.dataset_id = am.dataset_id
    AND    am.indicator = p_objective_id
    AND    am.kpi_measure_id = p_kpi_measure_id;

Line: 511

    SELECT COUNT(1)
    INTO   l_range_count
    FROM   bsc_color_type_props
    WHERE  indicator = p_objective_id
    AND    kpi_measure_id IS NULL;

Line: 518

      SELECT COUNT(1)
      INTO   l_range_count
      FROM   bsc_color_type_props
      WHERE  indicator = p_objective_id
      AND    kpi_measure_id = p_kpi_measure_id;

Line: 524

      SELECT COUNT(1)
      INTO   l_range_count
      FROM   bsc_color_type_props
      WHERE  indicator = p_objective_id
      AND    kpi_measure_id = p_kpi_measure_id
      AND    property_value = p_property_value;

Line: 562

      Update_Color_Prop_Ranges(p_objective_id    => p_objective_id
                              ,p_kpi_measure_id  => p_kpi_measure_id
                              ,p_color_type      => p_color_type
                              ,p_threshold_color => p_threshold_color
                              ,p_property_value  => p_property_value
                              ,p_time_stamp      => p_time_stamp
                              ,x_return_status   => x_return_status
                              ,x_msg_count       => x_msg_count
                              ,x_msg_data        => x_msg_data);

Line: 605

  SELECT property_value
  INTO   l_color_count
  FROM   bsc_sys_init
  WHERE  property_code = 'DEF_COLOR_COUNT';

Line: 793

  SELECT color_range_id
  FROM   bsc_color_type_props
  WHERE  indicator = p_objective_id;

Line: 798

  SELECT color_range_id
  FROM   bsc_color_type_props
  WHERE  indicator = p_objective_id
  AND    kpi_measure_id = p_kpi_measure_id
  AND    NVL(property_value, -1) = DECODE(p_property_value, NULL, -1, p_property_value);

Line: 817

PROCEDURE Update_Color_Prop_Ranges(
  p_commit              IN            VARCHAR2 := FND_API.G_FALSE
 ,p_objective_id        IN            NUMBER
 ,p_kpi_measure_id      IN            NUMBER
 ,p_color_type          IN            VARCHAR2  -- PERCENT_OF_TARGET, PERCENT_OF_KPI, CONSTANT
 ,p_threshold_color     IN            THRESHOLD_ARRAY
 ,p_property_value      IN            NUMBER := NULL
 ,p_time_stamp          IN            DATE   := NULL  -- Granular Locking
 ,x_return_status       OUT NOCOPY    VARCHAR2
 ,x_msg_count           OUT NOCOPY    NUMBER
 ,x_msg_data            OUT NOCOPY    VARCHAR2
) IS
  l_loop_count          NUMBER;

Line: 844

    SELECT color_range_id, last_update_date
      FROM  bsc_color_type_props
      WHERE indicator = p_objective_id
      AND   NVL(kpi_measure_id, -999) = NVL(p_kpi_measure_id, -999)
      AND   NVL(property_value, -1) = DECODE(p_property_value, NULL, -1, p_property_value);

Line: 851

  SELECT indicator
  FROM   bsc_kpis_b
  WHERE  source_indicator = p_objective_id
  AND    share_flag = 2 -- shared objective.
  AND    prototype_flag <> 2;

Line: 874

    IF (p_time_stamp IS NOT NULL AND l_color_range_ids_rec.last_update_date IS NOT NULL
        AND p_time_stamp <> l_color_range_ids_rec.last_update_date) THEN
      FND_MESSAGE.SET_NAME('BSC','BSC_UPDATED_KPI_MEASURE');

Line: 881

      UPDATE bsc_color_type_props
      SET    last_update_date = sysdate
            ,last_updated_by  = FND_GLOBAL.USER_ID
      WHERE  indicator = p_objective_id
      AND    kpi_measure_id IS NULL;

Line: 887

      UPDATE bsc_color_type_props
      SET    last_update_date = sysdate
            ,last_updated_by  = FND_GLOBAL.USER_ID
      WHERE  indicator = p_objective_id
      AND    kpi_measure_id = p_kpi_measure_id
      AND    NVL(property_value, -1) = DECODE(p_property_value, NULL, -1, p_property_value);

Line: 895

    BSC_COLOR_RANGE_PVT.Delete_Color_Ranges(p_color_range_id  => l_color_range_ids_rec.color_range_id
                                           ,x_return_status   => x_return_status
                                           ,x_msg_count       => x_msg_count
                                           ,x_msg_data        => x_msg_data);

Line: 920

      SELECT  am.analysis_option0, am.analysis_option1,
              am.analysis_option2, am.series_id
      INTO   l_an_opt0, l_an_opt1, l_an_opt2, l_series_id
      FROM   bsc_color_type_props cprop, bsc_kpi_analysis_measures_b am
      WHERE  cprop.kpi_measure_id = am.kpi_measure_id
      AND    cprop.indicator  = am.indicator
      AND    color_range_id = l_color_range_ids_rec.color_range_id;

Line: 931

        SELECT color_range_id
        INTO   l_range_id
        FROM   bsc_color_type_props
        WHERE  indicator = c_shared.indicator
        AND    kpi_measure_id IS NULL;

Line: 937

        UPDATE bsc_color_type_props
        SET    last_update_date = sysdate
              ,last_updated_by  = FND_GLOBAL.USER_ID
        WHERE  indicator = c_shared.indicator
        AND    kpi_measure_id IS NULL;

Line: 943

        SELECT kpi_measure_id
        INTO   l_kpi_measure_id
        FROM   bsc_kpi_analysis_measures_b
        WHERE  indicator= c_shared.indicator
        AND    analysis_option0 = l_an_opt0
        AND    analysis_option1 = l_an_opt1
        AND    analysis_option2 = l_an_opt2
        AND    series_id        = l_series_id;

Line: 952

        UPDATE bsc_color_type_props
        SET    last_update_date = sysdate
              ,last_updated_by  = FND_GLOBAL.USER_ID
        WHERE  indicator = c_shared.indicator
        AND    kpi_measure_id = l_kpi_measure_id
        AND    NVL(property_value, -1) = DECODE(p_property_value, NULL, -1, p_property_value);

Line: 962

      BSC_COLOR_RANGE_PVT.Delete_Color_Ranges(p_color_range_id  => l_range_id
                                             ,x_return_status   => x_return_status
                                             ,x_msg_count       => x_msg_count
                                             ,x_msg_data        => x_msg_data);

Line: 997

END Update_Color_Prop_Ranges;

Line: 1024

  SELECT BSC_COLOR_RANGE_ID_S.NEXTVAL
  INTO   l_range_id
  FROM   DUAL;

Line: 1222

PROCEDURE Delete_Color_Prop_Ranges (
  p_commit              IN             VARCHAR2:= FND_API.G_FALSE
 ,p_objective_id        IN             NUMBER
 ,p_kpi_measure_id      IN             NUMBER  := NULL
 ,p_cascade_shared      IN             BOOLEAN
 ,x_return_status       OUT NOCOPY     VARCHAR2
 ,x_msg_count           OUT NOCOPY     NUMBER
 ,x_msg_data            OUT NOCOPY     VARCHAR2
) IS
  l_range_id            NUMBER;

Line: 1241

  SELECT indicator
  FROM   bsc_kpis_b
  WHERE  source_indicator = p_objective_id
  AND    share_flag = 2 -- shared objective.
  AND    prototype_flag <> 2;

Line: 1248

  SELECT color_range_id
  FROM   bsc_color_type_props ct
  WHERE  indicator = l_objective_id;

Line: 1253

  SELECT color_range_id
  FROM   bsc_color_type_props ct
  WHERE  indicator = l_objective_id
  AND    kpi_measure_id = l_kpi_measure_id;

Line: 1279

      BSC_COLOR_RANGE_PVT.Delete_Color_Prop_Ranges(p_color_range_id  => l_range_id
                                                  ,x_return_status   => x_return_status
                                                  ,x_msg_count       => x_msg_count
                                                  ,x_msg_data        => x_msg_data);

Line: 1290

      BSC_COLOR_RANGE_PVT.Delete_Color_Prop_Ranges(p_color_range_id  => l_range_id
                                                  ,x_return_status   => x_return_status
                                                  ,x_msg_count       => x_msg_count
                                                  ,x_msg_data        => x_msg_data);

Line: 1303

      SELECT am.analysis_option0,
             am.analysis_option1, am.analysis_option2, am.series_id
      INTO   l_an_opt0, l_an_opt1, l_an_opt2, l_series_id
      FROM   bsc_kpi_analysis_measures_b am
      WHERE  am.indicator = p_objective_id
      AND    am.kpi_measure_id = l_kpi_measure_id;

Line: 1316

          BSC_COLOR_RANGE_PVT.Delete_Color_Prop_Ranges(p_color_range_id  => l_range_id
                                                      ,x_return_status   => x_return_status
                                                      ,x_msg_count       => x_msg_count
                                                      ,x_msg_data        => x_msg_data);

Line: 1325

        SELECT kpi_measure_id
        INTO   l_kpi_measure_id
        FROM   bsc_kpi_analysis_measures_b
        WHERE  indicator= c_shared.indicator
        AND    analysis_option0 = l_an_opt0
        AND    analysis_option1 = l_an_opt1
        AND    analysis_option2 = l_an_opt2
        AND    series_id        = l_series_id;

Line: 1336

          BSC_COLOR_RANGE_PVT.Delete_Color_Prop_Ranges(p_color_range_id  => l_range_id
                                                      ,x_return_status   => x_return_status
                                                      ,x_msg_count       => x_msg_count
                                                      ,x_msg_data        => x_msg_data);

Line: 1362

END Delete_Color_Prop_Ranges;

Line: 1368

PROCEDURE Delete_Color_Prop_Ranges (
  p_commit              IN             VARCHAR2:= FND_API.G_FALSE
 ,p_color_range_id      IN             NUMBER
 ,x_return_status       OUT NOCOPY     VARCHAR2
 ,x_msg_count           OUT NOCOPY     NUMBER
 ,x_msg_data            OUT NOCOPY     VARCHAR2
) IS
  l_range_id        NUMBER;

Line: 1386

  SELECT indicator
  FROM   bsc_kpis_b
  WHERE  source_indicator = l_objective_id
  AND    share_flag = 2 -- shared objective.
  AND    prototype_flag <> 2;

Line: 1409

  BSC_COLOR_RANGE_PVT.Delete_Color_Prop_Ranges(p_color_range_id  => l_range_id
                                              ,x_return_status   => x_return_status
                                              ,x_msg_count       => x_msg_count
                                              ,x_msg_data        => x_msg_data);

Line: 1417

  SELECT cprop.indicator, am.analysis_option0,
         am.analysis_option1, am.analysis_option2, am.series_id,
         cprop.property_value
  INTO   l_objective_id, l_an_opt0, l_an_opt1, l_an_opt2, l_series_id, l_property_value
  FROM   bsc_color_type_props cprop, bsc_kpi_analysis_measures_b am
  WHERE  cprop.kpi_measure_id = am.kpi_measure_id
  AND    color_range_id = p_color_range_id;

Line: 1426

    SELECT kpi_measure_id
    INTO   l_kpi_measure_id
    FROM   bsc_kpi_analysis_measures_b
    WHERE  indicator= c_shared.indicator
    AND    analysis_option0 = l_an_opt0
    AND    analysis_option1 = l_an_opt1
    AND    analysis_option2 = l_an_opt2
    AND    series_id        = l_series_id;

Line: 1437

    BSC_COLOR_RANGE_PVT.Delete_Color_Prop_Ranges(p_color_range_id  => l_range_id
                                                ,x_return_status   => x_return_status
                                                ,x_msg_count       => x_msg_count
                                                ,x_msg_data        => x_msg_data);

Line: 1460

END Delete_Color_Prop_Ranges;

Line: 1492

    SELECT weighted_color_method
    INTO   l_color_method
    FROM   bsc_kpis_b
    WHERE  indicator = p_objective_id;

Line: 1497

    SELECT color_method
    INTO   l_color_method
    FROM   bsc_kpi_analysis_measures_b am
          ,bsc_sys_datasets_b ds
    WHERE  am.dataset_id = ds.dataset_id
    AND    kpi_measure_id = p_kpi_measure_id;