APPS.BSC_SIMULATION_VIEW_PVT SQL Statements

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

Line: 11

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

Line: 44

  l_Sql:=' SELECT format_id, '||
         ' name, '||
         ' REPLACE(FORMAT,''$'',( '||
         '                      SELECT NVL(PROPERTY_VALUE,''$'') NLS_CURRENCY '||
         '                      FROM   BSC_SYS_INIT '||
         '                      WHERE  PROPERTY_CODE =''NLS_CURRENCY'') '||
         '         ) format '||
         'FROM (  '||
         '  SELECT format_id, '||
         '         name,REPLACE(DECODE(dotpos,0,REPLACE(format,'','',:1), '||
         '         REPLACE(SUBSTR(format,0,dotpos-1),'','',:2) || REPLACE(SUBSTR(format,dotpos),''.'',:3)),''#'',''9'')FORMAT '||
         '  FROM (SELECT format_id,name,format,INSTR(format,''||l_replace_format||'') dotpos  '||
         '  FROM bsc_sys_formats)) '||
         ' WHERE format_id ='||p_format_Id;

Line: 59

  SELECT value
  INTO   l_number_format
  FROM  v$nls_parameters
  WHERE parameter = 'NLS_NUMERIC_CHARACTERS';

Line: 102

    SELECT short_name
    INTO   l_region_Code
    FROM   bsc_kpis_b
    WHERE  config_Type =BSC_SIMULATION_VIEW_PUB.c_TYPE
    AND    indicator =  p_tarInd;

Line: 108

    SELECT dat.dataset_id
    INTO   l_dataset_id
    FROM   ak_region_items_vl ak
          ,bis_indicators dat
    WHERE  dat.short_name = ak.attribute2
    AND    ak.region_code =l_region_Code
    AND    ak.attribute1 ='MEASURE_NOTARGET'
    AND    ak.attribute_code = p_attribute_code;

Line: 327

x_arr_columns       BSC_UPDATE_UTIL.t_array_of_varchar2;

Line: 340

SELECT column_name
FROM   all_tab_columns
WHERE  table_name = p_table_name
AND    owner = l_owner
ORDER  BY column_name;

Line: 352

     SELECT DECODE(USER,BSC_APPS.get_user_schema('APPS'),BSC_APPS.get_user_schema('BIS'),USER)
     INTO l_owner FROM DUAL;

Line: 355

     SELECT DECODE(USER,BSC_APPS.get_user_schema('APPS'),BSC_APPS.get_user_schema('FND'),USER)
     INTO l_owner FROM DUAL;

Line: 358

     SELECT DECODE(USER,BSC_APPS.get_user_schema('APPS'),BSC_APPS.get_user_schema('AK'),USER)
     INTO l_owner FROM DUAL;

Line: 366

       h_sql := 'SELECT short_name FROM bsc_kpis_b WHERE  indicator = :1';

Line: 368

       h_sql := 'SELECT short_name FROM bsc_kpis_b@'|| p_DbLink_Name || ' WHERE  indicator = :1';

Line: 382

           h_sql := 'SELECT function_id FROM fnd_form_functions_vl WHERE function_name = :1';

Line: 384

           h_sql := 'SELECT function_id FROM fnd_form_functions_vl@'|| p_DbLink_Name || ' WHERE function_name = :1';

Line: 409

        h_sql:= 'INSERT INTO ( SELECT ';

Line: 418

        h_sql:= h_sql || ' SELECT ';

Line: 439

        BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

Line: 443

       UPDATE FND_FORM_FUNCTIONS
       SET PARAMETERS = REPLACE(PARAMETERS,''||l_region_code||'',''|| l_new_region_code || '')
       WHERE FUNCTION_ID =  l_new_function_id;

Line: 462

SELECT column_name
FROM   all_tab_columns
WHERE  table_name = p_table_name
AND    owner = DECODE(USER,BSC_APPS.get_user_schema('APPS'),BSC_APPS.get_user_schema,USER)
ORDER  BY column_name;

Line: 472

x_arr_columns    BSC_UPDATE_UTIL.t_array_of_varchar2;

Line: 510

    h_sql:= 'INSERT INTO ( SELECT ';

Line: 519

    h_sql:= h_sql || ' SELECT ';

Line: 527

         SELECT bsc_sys_image_id_s.nextval
         INTO l_next_image_id
         FROM dual;

Line: 533

         SELECT DISTINCT image_id
         INTO   l_image_id
         FROM   BSC_SYS_IMAGES_MAP_TL
         WHERE SOURCE_TYPE =2
         AND   SOURCE_CODE =p_Src_kpi;

Line: 541

         SELECT distinct image_id
         INTO   l_image_id
         FROM   BSC_SYS_IMAGES_MAP_TL
         WHERE SOURCE_TYPE =2
         AND   SOURCE_CODE =p_Trg_kpi;

Line: 559

   BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

Line: 588

   SELECT DISTINCT link_id
   FROM   bsc_tab_view_labels_vl
   WHERE  tab_id =BSC_SIMULATION_VIEW_PUB.c_TAB_ID
   AND    tab_view_id =  p_source_kpi
   AND    label_type
   IN    ( BSC_CUSTOM_VIEW_UI_WRAPPER.c_type_measure,
           BSC_CUSTOM_VIEW_UI_WRAPPER.c_type_measure_actual,BSC_CUSTOM_VIEW_UI_WRAPPER.c_type_measure_change,BSC_SIMULATION_VIEW_PUB.c_TYPE_MEASURE_COLOR,BSC_CUSTOM_VIEW_UI_WRAPPER.c_type_measure_slider
         );

Line: 607

   SELECT COUNT(0)
   INTO   l_count
   FROM  bsc_tab_view_labels_vl
   WHERE tab_id =BSC_SIMULATION_VIEW_PUB.c_TAB_ID
   AND   tab_view_id =  p_target_kpi;

Line: 633

      SELECT short_name
      INTO   l_region_Code
      FROM   bsc_kpis_b
      WHERE  config_Type =BSC_SIMULATION_VIEW_PUB.c_TYPE
      AND    indicator =  p_source_kpi;

Line: 642

        SELECT measure_type,actual_data_source
        INTO   l_type,l_Actual_Data_Source
        FROM   bis_indicators
        WHERE  dataset_id =  l_link_id;

Line: 659

            UPDATE bsc_tab_view_labels_b
            SET    link_id= l_dataset_id
            WHERE  tab_id = BSC_SIMULATION_VIEW_PUB.c_TAB_ID
            AND    tab_view_id = p_target_kpi
            AND    link_id =l_link_id
            AND    label_type
            IN     ( BSC_CUSTOM_VIEW_UI_WRAPPER.c_type_measure,
                     BSC_CUSTOM_VIEW_UI_WRAPPER.c_type_measure_actual,BSC_CUSTOM_VIEW_UI_WRAPPER.c_type_measure_change,BSC_SIMULATION_VIEW_PUB.c_TYPE_MEASURE_COLOR,BSC_CUSTOM_VIEW_UI_WRAPPER.c_type_measure_slider
                   );

Line: 669

            UPDATE bsc_kpi_tree_nodes_b
            SET    node_id =l_dataset_id
            WHERE  indicator =p_target_kpi
            AND    node_id =l_link_id;

Line: 674

            UPDATE bsc_kpi_tree_nodes_tl
            SET    node_id =l_dataset_id
            WHERE  indicator =p_target_kpi
            AND    node_id =l_link_id;

Line: 683

      SELECT a.source ,a.dataset_id
      INTO   l_type ,l_node_id
      FROM   bsc_sys_datasets_b a
            ,bsc_kpi_properties b
      WHERE  b.property_code = BSC_SIMULATION_VIEW_PUB.c_SIM_NODE_ID
      AND    b.property_value =a.dataset_id
      AND    indicator =p_source_kpi;

Line: 695

         SELECT actual_data_source
         INTO   l_Actual_Data_Source
         FROM   bis_indicators
         WHERE  dataset_id =  l_node_id;

Line: 766

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
  l_count          NUMBER;

Line: 783

  SELECT A.region_code,A.region_application_id
  FROM   bsc_kpis_b B,
         ak_regions A
  WHERE  A.region_code =B.short_name
  AND    B.indicator = p_indicator
  AND    B.config_type = BSC_SIMULATION_VIEW_PUB.c_TYPE;

Line: 791

  SAVEPOINT AddOrUpdateYTD;

Line: 802

      SELECT COUNT(0)
      INTO   l_count
      FROM   BIS_AK_REGION_EXTENSION
      WHERE  region_code =cd.region_code;

Line: 847

         BIS_REGION_EXTENSION_PVT.UPDATE_REGION_EXTN_RECORD
         (
            p_commit     =>  FND_API.G_FALSE
           ,pRegionCode  =>  cd.region_code
           ,pRegionAppId =>  cd.region_application_id
           ,pAttribute16 =>  NULL
           ,pAttribute17 =>  NULL
           ,pAttribute18 =>  NULL
           ,pAttribute19 =>  NULL
           ,pAttribute20 =>  NULL
           ,pAttribute21 =>  l_YTD
           ,pAttribute22 =>  NULL
           ,pAttribute23 =>  NULL
           ,pAttribute24 =>  NULL
           ,pAttribute25 =>  NULL
           ,pAttribute26 =>  NULL
           ,pAttribute27 =>  NULL
           ,pAttribute28 =>  NULL
           ,pAttribute29 =>  NULL
           ,pAttribute30 =>  NULL
           ,pAttribute31 =>  NULL
           ,pAttribute32 =>  NULL
           ,pAttribute33 =>  NULL
           ,pAttribute34 =>  NULL
           ,pAttribute35 =>  NULL
           ,pAttribute36 =>  NULL
           ,pAttribute37 =>  NULL
           ,pAttribute38 =>  NULL
           ,pAttribute39 =>  NULL
           ,pAttribute40 =>  NULL
        );

Line: 892

      UPDATE bsc_kpi_calculations
      SET    user_level0 =BSC_SIMULATION_VIEW_PUB.c_HIDE
            ,user_level1 =BSC_SIMULATION_VIEW_PUB.c_HIDE
      WHERE indicator =  p_indicator;

Line: 897

      UPDATE bsc_kpi_calculations
      SET    user_level0 =BSC_SIMULATION_VIEW_PUB.c_VISIBLE
            ,user_level1 =BSC_SIMULATION_VIEW_PUB.c_VISIBLE
      WHERE indicator =  p_indicator
      AND   calculation_id =BSC_SIMULATION_VIEW_PUB.c_YTD_CALC;

Line: 908

     ROLLBACK TO AddOrUpdateYTD;

Line: 917

     ROLLBACK TO AddOrUpdateYTD;

Line: 926

      ROLLBACK TO AddOrUpdateYTD;

Line: 929

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

Line: 931

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

Line: 935

     ROLLBACK TO AddOrUpdateYTD;

Line: 938

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

Line: 940

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

Line: 943

END Add_Or_Update_YTD;

Line: 974

  SELECT  DECODE(p_color_flag,1,BSC_SIMULATION_VIEW_PVT.C_SHOW_COLOR,BSC_SIMULATION_VIEW_PVT.C_DISABLE_COLOR)
  INTO    l_kpi_measure_rec.disable_color
  FROM DUAL;

Line: 984

  BSC_KPI_MEASURE_PROPS_PUB.Update_Kpi_Measure_Props (
    p_commit           => FND_API.G_FALSE
  , p_kpi_measure_rec  => l_kpi_measure_rec
  , p_cascade_shared   => TRUE
  , x_return_status    => x_return_status
  , x_msg_count        => x_msg_count
  , x_msg_data         => x_msg_data
  );

Line: 1050

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

Line: 1065

  SELECT COUNT(0)
  INTO   l_count
  FROM   bsc_sys_datasets_b
  WHERE  dataset_id= p_dataset_id
  AND    color_method =p_color_method;

Line: 1071

  UPDATE  bsc_sys_datasets_b
  SET     color_method =p_color_method
  WHERE   dataset_id = p_dataset_id;

Line: 1171

       SELECT COUNT(0)
       INTO   l_count
       FROM   bsc_kpi_properties
       WHERE  indicator = p_indicator
       AND    property_code  =BSC_SIMULATION_VIEW_PUB.c_SIM_NODE_ID;

Line: 1179

          INSERT INTO bsc_kpi_properties
          (   indicator
            , property_code
            , property_value
            , secondary_value
          ) VALUES
          (  p_indicator
            ,BSC_SIMULATION_VIEW_PUB.c_SIM_NODE_ID
            ,p_dataset_id
            ,NULL
          );

Line: 1191

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

Line: 1198

            UPDATE bsc_kpi_properties
            SET   property_value = p_dataset_id
            WHERE indicator =p_indicator
            AND   property_code =BSC_SIMULATION_VIEW_PUB.c_SIM_NODE_ID;

Line: 1275

SELECT node_id
FROM   bsc_kpi_tree_nodes_vl
WHERE  indicator =p_target_kpi;

Line: 1298

  SELECT FND_FORM_FUNCTIONS_S.NEXTVAL
  INTO l_new_function_id
  FROM dual;

Line: 1304

   BSC_KPI_TREE_NODES_PKG.DELETE_ROW
   (
       X_INDICATOR =>  p_target_kpi
      ,X_NODE_ID   =>  cd.node_id
   );

Line: 1322

  UPDATE bsc_kpis_b
  SET    short_name =l_new_region_code
  WHERE  indicator =p_target_kpi;

Line: 1326

  UPDATE ak_regions
  SET    attribute8 = p_target_kpi || '.'||BSC_SIMULATION_VIEW_PVT.C_DEFAULT_ANA_OPTION
  WHERE  region_code =l_new_region_code;

Line: 1330

  UPDATE bsc_kpi_analysis_options_b
  SET    short_name  =l_new_region_code
  WHERE  indicator =p_target_kpi;

Line: 1354

  SELECT short_name
  FROM   bsc_kpis_vl
  WHERE  indicator =p_indicator;

Line: 1359

  SELECT a.attribute_code,b.attribute_code AS childAttrCode
  FROM   ak_region_items_vl a,ak_region_items_vl b
  WHERE  a.region_code =b.region_code
  AND    b.attribute2(+)=a.attribute_code
  AND    a.REGION_CODE = l_region_code
  AND    a.attribute1=BSC_SIMULATION_VIEW_PVT.C_MEASURE_NOTARGET
  AND    a.attribute2= l_meas_short_name;

Line: 1375

      SELECT short_name
      INTO   l_meas_short_name
      FROM   bis_indicators
      WHERE  dataset_id =p_dataset_Id;

Line: 1384

        UPDATE ak_region_items
        SET    attribute7=  l_attribute7
        WHERE  region_code =l_region_code
        AND    attribute_code= cd_c.attribute_code;

Line: 1390

        UPDATE ak_region_items
        SET    attribute7=  l_attribute7
        WHERE  region_code =l_region_code
        AND    attribute_code= cd_c.childAttrCode;

Line: 1450

 SELECT  indicator,short_name
 FROM    BSC_KPIS_B
 WHERE   Source_Indicator  =  p_indicator
 AND     config_type =7
 AND     Prototype_Flag  <>  2;

Line: 1492

     BSC_KPI_PUB.Delete_Sim_Tree_Data
     (
         p_commit                => FND_API.G_FALSE
       , p_Bsc_Kpi_Entity_Rec    => l_Bsc_Kpi_Entity_Rec
       , x_return_status         => x_return_status
       , x_msg_count             => x_msg_count
       , x_msg_data              => x_msg_data
     );

Line: 1538

      UPDATE ak_regions
      SET    attribute8 = l_target_kpi || '.'||BSC_SIMULATION_VIEW_PVT.C_DEFAULT_ANA_OPTION
      WHERE  region_code =l_region_code;

Line: 1549

        BSC_KPI_PVT.Update_Kpi_Properties
        (    p_commit              => FND_API.G_FALSE
            ,p_Bsc_Kpi_Entity_Rec  => l_Bsc_Kpi_Entity_Rec
            ,x_return_status       => x_return_status
            ,x_msg_count           => x_msg_count
            ,x_msg_data            => x_msg_data
        );

Line: 1625

  l_arr_columns  BSC_UPDATE_UTIL.t_array_of_varchar2;

Line: 1639

  SELECT
    short_name
  FROM
    bsc_sys_dim_levels_vl
  WHERE
    dim_level_id IN (SELECT DISTINCT
                       column_value
                     FROM
                       TABLE(CAST(p_New_Dim_Levels AS FND_TABLE_OF_NUMBER)));

Line: 1655

  SELECT
    COUNT(1)
  INTO
    l_Count
  FROM
    bsc_sys_dim_groups_vl
  WHERE
    short_name = p_Region_Code;

Line: 1770

  SELECT parameters
  FROM   fnd_form_functions_vl
  WHERE  function_name =p_ind_Sht_Name;