APPS.BSC_SIMULATION_VIEW_PUB SQL Statements

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

Line: 48

      SELECT measure_col
      INTO   l_measure_col
      FROM   bsc_sys_datasets_b b
           , bsc_sys_measures a
      WHERE  b.measure_id1 =a.measure_id
      AND    b.dataset_id =p_DatasetId;

Line: 77

  SELECT v.attribute_code,
         v.attribute2,
         v.attribute3,
         b.operation|| '('|| b.measure_col || ')'  as measure_col
  FROM   ak_region_items_vl v
        ,bsc_sys_measures b
  WHERE v.attribute2 =b.short_name
  AND   v.region_code =l_short_name
  AND   v.attribute1 =BSC_SIMULATION_VIEW_PUB.c_MEASURE_NO_TARGET
  AND   v.attribute3 IS NULL
  ORDER BY v.display_sequence;

Line: 91

  SELECT v.attribute_code,
         v.attribute2,
         v.attribute3,
         b.measure_col
  FROM   ak_region_items_vl v
        ,bsc_sys_measures b
  WHERE v.attribute2 =b.short_name
  AND   v.region_code =l_short_name
  AND   v.attribute1 =BSC_SIMULATION_VIEW_PUB.c_MEASURE_NO_TARGET
  AND   v.attribute3 IS NOT NULL
  ORDER BY v.display_sequence;

Line: 105

  SELECT short_name
  INTO   l_short_name
  FROM   bsc_kpis_b
  WHERE  indicator =p_indicator;

Line: 210

  SELECT short_name
  INTO   l_short_name
  FROM   bis_indicators
  WHERE  dataset_id = p_Dataset_Id;

Line: 251

  SELECT BSC_SYS_IMAGE_ID_S.NEXTVAL
  INTO   l_next_image_id
  FROM   dual;

Line: 258

    BSC_SYS_IMAGES_PKG.INSERT_ROW
    (
       X_IMAGE_ID         => l_next_image_id
      ,X_FILE_NAME        => p_file_name
      ,X_DESCRIPTION      => p_description
      ,X_WIDTH            => p_width
      ,X_HEIGHT           => p_height
      ,X_MIME_TYPE        => p_mime_type
      ,X_CREATED_BY       => fnd_global.user_id
      ,X_LAST_UPDATED_BY  => fnd_global.user_id
      ,X_LAST_UPDATE_LOGIN=> fnd_global.login_id
    );

Line: 274

      x_msg_data := 'Insertion to BSC_SYS_IMAGES_PKG failed' || SQLERRM;

Line: 278

  BSC_SYS_IMAGES_MAP_PKG.INSERT_ROW
  (
     X_ROWID              => l_str
    ,X_SOURCE_TYPE        => BSC_SIMULATION_VIEW_PUB.c_INDICATOR_TYPE
    ,X_SOURCE_CODE        => p_obj_id
    ,X_TYPE               => BSC_SIMULATION_VIEW_PUB.c_TYPE
    ,X_IMAGE_ID           => l_next_image_id
    ,X_CREATION_DATE      => SYSDATE
    ,X_CREATED_BY         => fnd_global.user_id
    ,X_LAST_UPDATE_DATE   => SYSDATE
    ,X_LAST_UPDATED_BY    => fnd_global.user_id
    ,X_LAST_UPDATE_LOGIN  => fnd_global.login_id
  );

Line: 301

Procedure   : Add_Or_Update_Tab_View_Bg
Description : This proceudres update or add a new canvas image to the simulation tree.
              We will continue to use the tables BSC_SYS_IMAGES and
              BSC_SYS_IMAGE_MAPS_TL table for storing the simulation tree background
              images.
              Source_Type column in BSC_SYS_IMAGE_MAPS_TL will be set to 2 for indicators

              SOURCE_TYPE  --> 1 [ For tabs ]
                           --> 2 [ For indicators ]

/*********************************************************/

PROCEDURE Add_Or_Update_Sim_Tree_Bg (
   p_obj_id            IN NUMBER
  ,p_image_id          IN NUMBER
  ,p_file_name         IN VARCHAR2
  ,p_description       IN VARCHAR2
  ,p_width             IN NUMBER
  ,p_height            IN NUMBER
  ,p_mime_type         IN VARCHAR2
  ,x_image_id          OUT NOCOPY NUMBER
  ,x_return_status     OUT NOCOPY VARCHAR2
  ,x_msg_count         OUT NOCOPY NUMBER
  ,x_msg_data          OUT NOCOPY VARCHAR2
) IS
l_count            NUMBER;

Line: 329

  SAVEPOINT AddOrUpdateSimTreeBg;

Line: 334

  SELECT COUNT(0)
  INTO   l_count
  FROM bsc_sys_images bsi,
       bsc_sys_images_map_vl bsim
  WHERE bsim.source_type =BSC_SIMULATION_VIEW_PUB.c_INDICATOR_TYPE
  AND   bsim.source_code = p_obj_id
  AND   bsim.type = BSC_SIMULATION_VIEW_PUB.c_TYPE
  AND   bsim.image_id = p_image_id
  AND   bsim.image_id = bsi.image_id;

Line: 348

      SELECT COUNT(0)
      INTO   l_count
      FROM   bsc_sys_images_map_TL
      WHERE  source_type =BSC_SIMULATION_VIEW_PUB.c_INDICATOR_TYPE
      AND    source_code = p_obj_id
      AND    type = BSC_SIMULATION_VIEW_PUB.c_TYPE
      AND    image_id = p_image_id
      AND    source_lang = USERENV('LANG');

Line: 362

          UPDATE  BSC_SYS_IMAGES
          SET     FILE_NAME              =   p_file_name,
                  DESCRIPTION            =   p_description,
                  WIDTH                  =   p_width,
                  HEIGHT                 =   p_height,
                  MIME_TYPE              =   p_mime_type,
                  LAST_UPDATE_DATE       =   SYSDATE,
                  LAST_UPDATED_BY        =   fnd_global.user_id,
                  LAST_UPDATE_LOGIN      =   fnd_global.login_id,
                  FILE_BODY              =   EMPTY_BLOB()
          WHERE   IMAGE_ID               =   p_image_id;

Line: 375

            ROLLBACK TO AddOrUpdateSimTreeBg;

Line: 377

            x_msg_data := 'Update to BSC_SYS_IMAGES failed' || SQLERRM;

Line: 381

        BSC_SYS_IMAGES_MAP_PKG.UPDATE_ROW
        (
           X_SOURCE_TYPE       => BSC_SIMULATION_VIEW_PUB.c_INDICATOR_TYPE
          ,X_SOURCE_CODE       => p_obj_id
          ,X_TYPE              => BSC_SIMULATION_VIEW_PUB.c_TYPE
          ,X_IMAGE_ID          => p_image_id
          ,X_CREATION_DATE     => SYSDATE
          ,X_CREATED_BY        => fnd_global.user_id
          ,X_LAST_UPDATE_DATE  => SYSDATE
          ,X_LAST_UPDATED_BY   => fnd_global.user_id
          ,X_LAST_UPDATE_LOGIN => fnd_global.login_id
        );

Line: 396

        SELECT BSC_SYS_IMAGE_ID_S.NEXTVAL
        INTO   l_next_image_id
        FROM   dual;

Line: 403

          BSC_SYS_IMAGES_PKG.INSERT_ROW
          (
             X_IMAGE_ID           => l_next_image_id
            ,X_FILE_NAME          => p_file_name
            ,X_DESCRIPTION        => p_description
            ,X_WIDTH              => p_width
            ,X_HEIGHT             => p_height
            ,X_MIME_TYPE          => p_mime_type
            ,X_CREATED_BY         => fnd_global.user_id
            ,X_LAST_UPDATED_BY    => fnd_global.user_id
            ,X_LAST_UPDATE_LOGIN  => fnd_global.login_id
          );

Line: 418

            ROLLBACK TO AddOrUpdateSimTreeBg;

Line: 420

            x_msg_data := 'Insertion to BSC_SYS_IMAGES_PKG failed' || SQLERRM;

Line: 424

        BSC_SYS_IMAGES_MAP_PKG.UPDATE_ROW
        (
           X_SOURCE_TYPE       => BSC_SIMULATION_VIEW_PUB.c_INDICATOR_TYPE
          ,X_SOURCE_CODE       => p_obj_id
          ,X_TYPE              => BSC_SIMULATION_VIEW_PUB.c_TYPE
          ,X_IMAGE_ID          => p_image_id
          ,X_CREATION_DATE     => SYSDATE
          ,X_CREATED_BY        => fnd_global.user_id
          ,X_LAST_UPDATE_DATE  => SYSDATE
          ,X_LAST_UPDATED_BY   => fnd_global.user_id
          ,X_LAST_UPDATE_LOGIN => fnd_global.login_id
        );

Line: 455

      ROLLBACK TO AddOrUpdateSimTreeBg;

Line: 459

END Add_Or_Update_Sim_Tree_Bg;

Line: 494

     SELECT COUNT(0)
     INTO   l_count
     FROM   bsc_kpis_b
     WHERE  SHORT_NAME = p_Region_Code;

Line: 504

       SELECT a.INDICATOR
             ,a.ind_group_id
             ,b.tab_id
             ,a.prototype_flag
             ,a.name
             ,c.attribute21
       INTO   x_indicator
             ,x_ind_group_id
             ,x_tab_id
             ,x_prototype_flag
             ,x_ind_name
             ,x_ytd_enabled
       FROM  bsc_kpis_vl a,
             bsc_tab_indicators b,
             bis_ak_region_extension c
       WHERE a.short_name = c.region_code(+)
       AND   a.short_name = p_Region_Code
       AND   a.INDICATOR =b.INDICATOR(+)
       AND   a.prototype_flag<>2
       AND   a.share_flag<>2;

Line: 569

PROCEDURE add_or_update_measure
(
   p_tab_id               IN    NUMBER
  ,p_tab_view_id          IN    NUMBER
  ,p_text_object_id       IN    NUMBER
  ,p_text_flag            IN    NUMBER
  ,p_font_size            IN    NUMBER
  ,p_font_style           IN    NUMBER
  ,p_font_color           IN    NUMBER
  ,p_text_left            IN    NUMBER
  ,p_text_top             IN    NUMBER
  ,p_text_width           IN    NUMBER
  ,p_text_height          IN    NUMBER
  ,p_slider_object_id     IN    NUMBER
  ,p_slider_flag          IN    NUMBER
  ,p_slider_left          IN    NUMBER
  ,p_slider_top           IN    NUMBER
  ,p_slider_width         IN    NUMBER
  ,p_slider_height        IN    NUMBER
  ,p_actual_object_id     IN    NUMBER
  ,p_actual_flag          IN    NUMBER
  ,p_actual_left          IN    NUMBER
  ,p_actual_top           IN    NUMBER
  ,p_actual_width         IN    NUMBER
  ,p_actual_height        IN    NUMBER
  ,p_change_object_id     IN    NUMBER
  ,p_change_flag          IN    NUMBER
  ,p_change_left          IN    NUMBER
  ,p_change_top           IN    NUMBER
  ,p_change_width         IN    NUMBER
  ,p_change_height        IN    NUMBER
  ,p_color_object_id      IN    NUMBER
  ,p_color_flag           IN    NUMBER
  ,p_color_left           IN    NUMBER
  ,p_color_top            IN    NUMBER
  ,p_color_width          IN    NUMBER
  ,p_color_height         IN    NUMBER
  ,p_indicator_id         IN    NUMBER
  ,p_function_id          IN    NUMBER
  ,p_Node_Id              IN    NUMBER
  ,p_Node_Name            IN    VARCHAR2
  ,p_Node_Help            IN    VARCHAR2
  ,p_SimulateFlag         IN    NUMBER
  ,p_Format_id            IN    NUMBER
  ,p_Node_Color_flag      IN    NUMBER
  ,p_Node_Color_method    IN    NUMBER
  ,p_Navigates_to_trend   IN    NUMBER
  ,p_Top_position         IN    NUMBER
  ,p_Left_position        IN    NUMBER
  ,p_Width                IN    NUMBER
  ,p_Height               IN    NUMBER
  ,p_Autoscale_flag       IN    NUMBER
  ,p_Y_axis_title         IN    VARCHAR2
  ,p_Node_Attr_Code       IN    VARCHAR2
  ,p_Node_Short_Name      IN    VARCHAR2
  ,p_default_node         IN    NUMBER
  ,p_color_thresholds     IN    VARCHAR2
  ,p_color_by_total       IN    NUMBER
  ,x_return_status      OUT NOCOPY VARCHAR2
  ,x_msg_count          OUT NOCOPY NUMBER
  ,x_msg_data           OUT NOCOPY VARCHAR2
) IS
  l_count         NUMBER;

Line: 642

  BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_measure
  (
    p_tab_id             => p_tab_id
   ,p_tab_view_id        => p_tab_view_id
   ,p_text_object_id     => p_text_object_id
   ,p_text_flag          => p_text_flag
   ,p_font_size          => p_font_size
   ,p_font_style         => p_font_style
   ,p_font_color         => p_font_color
   ,p_text_left          => p_text_left
   ,p_text_top           => p_text_top
   ,p_text_width         => p_text_width
   ,p_text_height        => p_text_height
   ,p_slider_object_id   => p_slider_object_id
   ,p_slider_flag        => p_slider_flag
   ,p_slider_left        => p_slider_left
   ,p_slider_top         => p_slider_top
   ,p_slider_width       => p_slider_width
   ,p_slider_height      => p_slider_height
   ,p_actual_object_id   => p_actual_object_id
   ,p_actual_flag        => p_actual_flag
   ,p_actual_left        => p_actual_left
   ,p_actual_top         => p_actual_top
   ,p_actual_width       => p_actual_width
   ,p_actual_height      => p_actual_height
   ,p_change_object_id   => p_change_object_id
   ,p_change_flag        => p_change_flag
   ,p_change_left        => p_change_left
   ,p_change_top         => p_change_top
   ,p_change_width       => p_change_width
   ,p_change_height      => p_change_height
   ,p_indicator_id       => l_dataset_id
   ,p_function_id        => p_function_id
   ,x_return_status      => x_return_status
   ,x_msg_count          => x_msg_count
   ,x_msg_data           => x_msg_data
  );

Line: 686

  BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_tab_view_label
  (
      p_tab_id          => p_tab_id
    , p_tab_view_id     => p_tab_view_id
    , p_object_id       => p_color_object_id
    , p_object_type     => BSC_SIMULATION_VIEW_PUB.C_TYPE_MEASURE_COLOR
    , p_label_text      => BSC_SIMULATION_VIEW_PUB.C_MEASURE_COLOR
    , p_text_flag       => p_color_flag
    , p_font_color      => p_font_color
    , p_font_size       => p_font_size
    , p_font_style      => p_font_style
    , p_left            => p_color_left
    , p_top             => p_color_top
    , p_width           => p_color_width
    , p_height          => p_color_height
    , p_note_text       => NULL
    , p_link_id         => l_dataset_id
    , p_function_id     => p_function_id
    , x_return_status   => x_return_status
    , x_msg_count       => x_msg_count
    , x_msg_data        => x_msg_data
  );

Line: 715

  BSC_SIMULATION_VIEW_PUB.add_or_update_sim_node_props
  (
     p_indicator            =>  p_tab_view_id
    ,p_Node_Id              =>  l_dataset_id
    ,p_Node_Name            =>  p_Node_Name
    ,p_Node_Help            =>  p_Node_Help
    ,p_SimulateFlag         =>  p_SimulateFlag
    ,p_Format_id            =>  p_Format_id
    ,p_Color_flag           =>  p_Node_Color_flag
    ,p_Color_method         =>  p_Node_Color_method
    ,p_Navigates_to_trend   =>  p_Navigates_to_trend
    ,p_Top_position         =>  p_Top_position
    ,p_Left_position        =>  p_Left_position
    ,p_Width                =>  p_Width
    ,p_Height               =>  p_Height
    ,p_Autoscale_flag       =>  p_Autoscale_flag
    ,p_Y_axis_title         =>  p_Y_axis_title
    ,p_Node_Attr_Code       =>  p_Node_Attr_Code
    ,p_Node_Short_Name      =>  p_Node_Short_Name
    ,x_return_status        =>  x_return_status
    ,x_msg_count            =>  x_msg_count
    ,x_msg_data             =>  x_msg_data
  );

Line: 840

         x_msg_data      :=  x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.add_or_update_measure ';

Line: 842

         x_msg_data      :=  SQLERRM||' at BSC_SIMULATION_VIEW_PUB.add_or_update_measure ';

Line: 848

         x_msg_data      :=  x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.add_or_update_measure ';

Line: 850

         x_msg_data      :=  SQLERRM||' at BSC_SIMULATION_VIEW_PUB.add_or_update_measure ';

Line: 854

END add_or_update_measure;

Line: 857

PROCEDURE add_or_update_sim_node_props
(
    p_indicator          IN NUMBER
   ,p_Node_Id            IN NUMBER
   ,p_Node_Name          IN VARCHAR2
   ,p_Node_Help          IN VARCHAR2
   ,p_SimulateFlag       IN NUMBER
   ,p_Format_id          IN NUMBER
   ,p_Color_flag         IN NUMBER
   ,p_Color_method       IN NUMBER
   ,p_Navigates_to_trend IN NUMBER
   ,p_Top_position       IN NUMBER
   ,p_Left_position      IN NUMBER
   ,p_Width              IN NUMBER
   ,p_Height             IN NUMBER
   ,p_Autoscale_flag     IN NUMBER
   ,p_Y_axis_title       IN VARCHAR2
   ,p_Node_Attr_Code     IN VARCHAR2
   ,p_Node_Short_Name    IN VARCHAR2
   ,x_return_status      OUT NOCOPY VARCHAR2
   ,x_msg_count          OUT NOCOPY NUMBER
   ,x_msg_data           OUT NOCOPY VARCHAR2
) IS
 l_str                VARCHAR2(100);

Line: 884

  SAVEPOINT addorupdatesimnodeprops;

Line: 891

  SELECT COUNT(1)
  INTO  l_count
  FROM  bsc_kpi_tree_nodes_vl
  WHERE indicator = p_indicator
  AND   node_id =p_Node_Id;

Line: 899

    BSC_KPI_TREE_NODES_PKG.INSERT_ROW
    (
       X_ROWID              => l_str
      ,X_INDICATOR          => p_indicator
      ,X_NODE_ID            => p_Node_Id
      ,X_SIMULATE_FLAG      => p_SimulateFlag
      ,X_FORMAT_ID          => p_Format_id
      ,X_COLOR_FLAG         => p_Color_flag
      ,X_COLOR_METHOD       => p_Color_method
      ,X_NAVIGATES_TO_TREND => p_Navigates_to_trend
      ,X_TOP_POSITION       => p_Top_position
      ,X_LEFT_POSITION      => p_Left_position
      ,X_WIDTH              => p_Width
      ,X_HEIGHT             => p_Height
      ,X_NAME               => p_Node_Name
      ,X_HELP               => p_Node_Name  --Right now node help will be same
      ,X_Y_AXIS_TITLE       => p_Y_axis_title
    );

Line: 918

    BSC_KPI_TREE_NODES_PKG.UPDATE_ROW
    (
        X_INDICATOR          => p_indicator
       ,X_NODE_ID            => p_Node_Id
       ,X_SIMULATE_FLAG      => p_SimulateFlag
       ,X_FORMAT_ID          => p_Format_id
       ,X_COLOR_FLAG         => p_Color_flag
       ,X_COLOR_METHOD       => p_Color_method
       ,X_NAVIGATES_TO_TREND => p_Navigates_to_trend
       ,X_TOP_POSITION       => p_Top_position
       ,X_LEFT_POSITION      => p_Left_position
       ,X_WIDTH              => p_Width
       ,X_HEIGHT             => p_Height
       ,X_NAME               => p_Node_Name
       ,X_HELP               => p_Node_Name
       ,X_Y_AXIS_TITLE       => p_Y_axis_title
    );

Line: 940

    ROLLBACK TO addorupdatesimnodeprops;

Line: 943

END add_or_update_sim_node_props;

Line: 1044

  SELECT label_id,label_type,link_id
  FROM bsc_tab_view_labels_vl
  WHERE tab_id = p_tab_id
  AND tab_view_id = p_obj_Id;

Line: 1050

  SELECT measure_type
  FROM   bis_indicators
  WHERE  dataset_id = l_dataset_id;

Line: 1056

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

Line: 1089

        BSC_TAB_VIEW_LABELS_PKG.DELETE_ROW
        (
           X_TAB_ID      => p_tab_id
          ,X_TAB_VIEW_ID => p_obj_Id
          ,X_LABEL_ID    => l_label_cur.label_id
        );

Line: 1103

        BSC_KPI_TREE_NODES_PKG.DELETE_ROW
        (
           X_INDICATOR  => p_obj_Id
          ,X_NODE_ID    => l_links_table(cd)
        );

Line: 1140

    SELECT COUNT(0)
    INTO   l_count
    FROM   bsc_kpi_tree_nodes_b
    WHERE  indicator =p_obj_Id;

Line: 1303

  SELECT COUNT(0)
  INTO   l_same_name
  FROM   bsc_tab_indicators
  WHERE  tab_id = p_tabId
  AND   indicator IN (SELECT indicator
                      FROM BSC_KPIS_TL
                      WHERE UPPER(name) = UPPER(p_name));

Line: 1360

PROCEDURE Add_Or_Update_YTD
(
   p_indicator            IN      NUMBER
  ,p_YTD                  IN      VARCHAR2
  ,p_prev_YTD             IN      VARCHAR2
  ,x_return_status    OUT NOCOPY  VARCHAR2
  ,x_msg_count        OUT NOCOPY  NUMBER
  ,x_msg_data         OUT NOCOPY  VARCHAR2
) IS
BEGIN
  FND_MSG_PUB.INITIALIZE;

Line: 1373

  BSC_SIMULATION_VIEW_PVT.Add_Or_Update_YTD
  (
     p_indicator       =>  p_indicator
    ,p_YTD             =>  p_YTD
    ,p_prev_YTD        =>  p_prev_YTD
    ,x_return_status   =>  x_return_status
    ,x_msg_count       =>  x_msg_count
    ,x_msg_data        =>  x_msg_data
  );

Line: 1409

         x_msg_data      :=  x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Add_Or_Update_YTD ';

Line: 1411

         x_msg_data      :=  SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Add_Or_Update_YTD ';

Line: 1417

         x_msg_data      :=  x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Add_Or_Update_YTD ';

Line: 1419

         x_msg_data      :=  SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Add_Or_Update_YTD ';

Line: 1423

END Add_Or_Update_YTD;

Line: 1442

 SELECT a.color_range_sequence,a.low,a.high,a.color_id
 FROM   bsc_color_ranges a,
        bsc_color_type_props b
 WHERE  a.color_range_id =b.color_range_id
 AND    b.INDICATOR=p_indicator
 AND    b.kpi_measure_id =l_kpi_measure_id
 ORDER BY a.color_range_sequence;

Line: 1592

  SELECT kpi_measure_id
  INTO   l_kpi_measure_id
  FROM   bsc_kpi_analysis_measures_b
  WHERE  indicator = p_indicator
  AND    dataset_id = p_dataset_id;

Line: 1680

  SELECT indicator
        ,short_name
  FROM  bsc_kpis_b
  WHERE prototype_flag<>2
  AND   share_flag =2
  AND   config_type =7
  AND   source_indicator = p_indicator;

Line: 1782

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