APPS.BSC_COLOR_CALC_UTIL SQL Statements

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

Line: 13

, p_colors      IN BSC_UPDATE_UTIL.t_array_of_number
, p_num_colors  IN NUMBER
, x_color       OUT NOCOPY NUMBER
, x_kpi_index   OUT NOCOPY NUMBER
);

Line: 37

    SELECT color_range_id
    FROM bsc_color_type_props
    WHERE indicator  = p_indicator
    --AND  kpi_measure_id = p_kpi_measure_id;

Line: 44

    SELECT color_range_id
    FROM bsc_color_type_props
    WHERE indicator  = p_indicator
    AND  kpi_measure_id = p_kpi_measure_id;

Line: 51

    SELECT color_range_id
    FROM bsc_color_type_props
    WHERE indicator = p_indicator
    AND kpi_measure_id IS NULL;

Line: 57

    SELECT high, color_id
    FROM bsc_color_ranges
    WHERE color_range_id = l_color_range_id
    ORDER BY color_range_sequence;

Line: 63

  SELECT config_type
  INTO   l_config_type
  FROM   bsc_kpis_b
  WHERE  indicator = p_indicator;

Line: 133

    SELECT weighted_color_method
      FROM bsc_kpis_b
      WHERE indicator = p_pbjective_id;

Line: 171

    SELECT weight
      FROM bsc_kpi_measure_weights
      WHERE kpi_measure_id = p_kpi_measure_id
      AND   indicator = p_objective_id;

Line: 233

  p_array_of_number  IN BSC_UPDATE_UTIL.t_array_of_number
)
RETURN NUMBER IS
  l_counter    NUMBER;

Line: 260

  p_array_of_number  IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number
)
IS
  l_index         NUMBER;

Line: 282

  p_objective_color_rec  IN BSC_UPDATE_COLOR.t_objective_color_rec
)
RETURN BOOLEAN IS
  CURSOR c_dim_comb(p_indicator NUMBER, p_tab_id NUMBER) IS
    SELECT DISTINCT
           dim_level1 || dim_level2 || dim_level3 || dim_level4 || dim_level5 || dim_level6 || dim_level7 || dim_level8 || period_id dim_comb,
           dim_level1, dim_level2, dim_level3, dim_level4, dim_level5, dim_level6, dim_level7, dim_level8,
           period_id
      FROM bsc_sys_kpi_colors
      WHERE indicator = p_indicator
      AND   tab_id = p_tab_id;

Line: 296

    SELECT kpi_measure_id, kpi_color
      FROM bsc_sys_kpi_colors
      WHERE dim_level1 || dim_level2 || dim_level3 || dim_level4 || dim_level5 || dim_level6 || dim_level7 || dim_level8 ||  period_id = p_dim_comb
      AND   indicator = p_indicator
      AND   tab_id = p_tab_id
    UNION -- bsc_sys_kpi_colors may not contain a color entry for 'color enabled' KPIs for which data has not been loaded
    SELECT kpi_measure_id, BSC_COLOR_REPOSITORY.NO_COLOR kpi_color
      FROM bsc_kpi_measure_props
      WHERE indicator = p_indicator
      AND   disable_color = 'F'
      AND   kpi_measure_id NOT IN
                           ( SELECT kpi_measure_id FROM bsc_sys_kpi_colors
                             WHERE indicator = p_indicator
                             AND   tab_id = p_tab_id
                           );

Line: 356

      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, driving_kpi_measure_id)
        VALUES
          (p_objective_color_rec.tab_id, p_objective_color_rec.objective_id,
           l_dim_comb.dim_level1, l_dim_comb.dim_level2, l_dim_comb.dim_level3, l_dim_comb.dim_level4,
           l_dim_comb.dim_level5, l_dim_comb.dim_level6, l_dim_comb.dim_level7, l_dim_comb.dim_level8,
           l_dim_comb.period_id, l_obj_color, NULL);

Line: 375

    BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
                    x_source => 'BSC_COLOR_CALC_UTIL.Calc_Obj_Color_By_Weights');

Line: 484

    SELECT property_value dataset_id
      FROM  bsc_kpi_properties
      WHERE property_code = BSC_SIMULATION_VIEW_PUB.c_SIM_NODE_ID
      AND   indicator = p_indicator;

Line: 491

    SELECT kpi_measure_id
      FROM bsc_kpi_analysis_measures_b anal_meas
      WHERE anal_meas.dataset_id = p_dataset_id
      AND   anal_meas.indicator = p_indicator;

Line: 532

  p_objective_color_rec  IN BSC_UPDATE_COLOR.t_objective_color_rec
 ,p_rollup_type          IN bsc_kpis_b.color_rollup_type%TYPE
 ,x_kpi_measure_id       OUT NOCOPY NUMBER
 ,x_color_flag           OUT NOCOPY BOOLEAN

) IS

  CURSOR c_dim_comb(p_indicator NUMBER, p_tab_id NUMBER) IS
    SELECT DISTINCT
           dim_level1 || dim_level2 || dim_level3 || dim_level4 || dim_level5 || dim_level6 || dim_level7 || dim_level8 || period_id dim_comb,
           dim_level1, dim_level2, dim_level3, dim_level4, dim_level5, dim_level6, dim_level7, dim_level8,
           period_id
      FROM bsc_sys_kpi_colors
      WHERE indicator = p_indicator
      AND   tab_id = p_tab_id;

Line: 550

    SELECT kpi_color, kpi_measure_id
      FROM bsc_sys_kpi_colors
      WHERE dim_level1 || dim_level2 || dim_level3 || dim_level4 || dim_level5 || dim_level6 || dim_level7 || dim_level8 ||  period_id = p_dim_comb
      AND   indicator = p_indicator
      AND   tab_id = p_tab_id
    UNION  -- bsc_sys_kpi_colors may not contain a color entry for 'color enabled' KPIs for which data has not been loaded
    SELECT BSC_COLOR_REPOSITORY.NO_COLOR kpi_color, kpi_measure_id
      FROM bsc_kpi_measure_props
      WHERE indicator = p_indicator
      AND   disable_color = 'F'
      AND   kpi_measure_id NOT IN
                           ( SELECT kpi_measure_id FROM bsc_sys_kpi_colors
                             WHERE indicator = p_indicator
                             AND   tab_id = p_tab_id
                           );

Line: 569

  l_colors                  BSC_UPDATE_UTIL.t_array_of_number;

Line: 570

  l_kpi_measures            BSC_UPDATE_UTIL.t_array_of_number;

Line: 609

      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, driving_kpi_measure_id)
        VALUES
          (p_objective_color_rec.tab_id, p_objective_color_rec.objective_id,
           l_dim_comb.dim_level1, l_dim_comb.dim_level2, l_dim_comb.dim_level3, l_dim_comb.dim_level4,
           l_dim_comb.dim_level5, l_dim_comb.dim_level6, l_dim_comb.dim_level7, l_dim_comb.dim_level8,
           l_dim_comb.period_id, l_obj_color, x_kpi_measure_id);

Line: 629

    BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
                    x_source => 'BSC_COLOR_CALC_UTIL.Calc_Obj_Color_By_Single_Kpi');

Line: 650

  p_colors      IN BSC_UPDATE_UTIL.t_array_of_number
, p_num_colors  IN NUMBER
, x_color       OUT NOCOPY NUMBER
, x_kpi_index   OUT NOCOPY NUMBER
) IS

  l_index         NUMBER;

Line: 715

  p_colors      IN BSC_UPDATE_UTIL.t_array_of_number
, p_num_colors  IN NUMBER
, x_color       OUT NOCOPY NUMBER
, x_kpi_index OUT NOCOPY NUMBER
) IS

  l_index         NUMBER;

Line: 779

  p_array_of_number  IN BSC_UPDATE_UTIL.t_array_of_number
, p_max_count        IN NUMBER
, p_colors_array     OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number
, p_color_count      OUT NOCOPY NUMBER
)
IS
  l_index         NUMBER;

Line: 816

  p_colors      IN BSC_UPDATE_UTIL.t_array_of_number
, p_num_colors  IN NUMBER
, x_color       OUT NOCOPY NUMBER
, x_kpi_index   OUT NOCOPY NUMBER
) IS

  l_index            NUMBER;

Line: 826

  l_array_of_number  BSC_UPDATE_UTIL.t_array_of_number;

Line: 827

  l_colors_array     BSC_UPDATE_UTIL.t_array_of_number;

Line: 909

, p_colors      IN BSC_UPDATE_UTIL.t_array_of_number
, p_num_colors  IN NUMBER
, x_color       OUT NOCOPY NUMBER
, x_kpi_index   OUT NOCOPY NUMBER
) IS
BEGIN
  IF p_rollup_type = BEST THEN
    get_best_color(p_colors, p_num_colors, x_color, x_kpi_index);

Line: 936

    SELECT m.kpi_measure_id
      FROM bsc_db_dataset_dim_sets_v m,
           bsc_db_color_ao_defaults_v d
      WHERE m.indicator = d.indicator
      AND   m.a0 = d.a0_default
      AND   m.a1 = d.a1_default
      AND   m.a2 = d.a2_default
      AND   m.default_value = 1
      AND   m.indicator = p_indicator;

Line: 976

  p_objective_color_rec  IN BSC_UPDATE_COLOR.t_objective_color_rec
 ,x_kpi_measure_id       OUT NOCOPY NUMBER
 ,x_color_flag           OUT NOCOPY BOOLEAN
) IS

  l_default_kpi_measure_id  bsc_kpi_measure_props.kpi_measure_id%TYPE;

Line: 995

  INSERT /*+ append */
    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, 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_measure_id
        FROM bsc_sys_kpi_colors
        WHERE kpi_measure_id = l_default_kpi_measure_id
        AND   tab_id = p_objective_color_rec.tab_id
        AND   indicator = p_objective_color_rec.objective_id;

Line: 1026

    SELECT color_rollup_type
      FROM bsc_kpis_b
      WHERE indicator = p_indicator;