APPS.BSC_COPY_INDICATOR_PUB SQL Statements

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

Line: 57

  SELECT
    a.ind_group_id
  FROM
    bsc_kpis_b a
  WHERE
    a.indicator = p_Indicator;

Line: 97

  SELECT
    tab_id
  FROM
    bsc_tab_indicators
  WHERE
    indicator = p_Indicator;

Line: 115

    SELECT
      COUNT(1)
    INTO
      l_Check_Association
    FROM
      bsc_tab_ind_groups_vl
    WHERE
      tab_id = p_Indicator
      AND ind_group_id = p_New_Indicator_Group;

Line: 128

     BSC_KPI_GROUP_PVT.Update_Kpi_Group(
        p_commit             => FND_API.G_FALSE
       ,p_Bsc_Kpi_Group_Rec  => l_Bsc_Kpi_Group_Rec
       ,x_return_status      => x_return_status
       ,x_msg_count          => x_msg_count
       ,x_msg_data           => x_msg_data
      );

Line: 140

     SELECT
       COUNT(1)
     INTO
       l_Indicator_Count
     FROM
       bsc_tab_indicators ti,
       bsc_kpis_vl k
     WHERE
       ti.tab_id = l_Tab_Id
       AND ti.indicator = k.indicator
       AND k.ind_group_id = p_Old_Indicator_Group;

Line: 155

       BSC_KPI_GROUP_PVT.Delete_Kpi_Group(
         p_commit             => FND_API.G_FALSE
        ,p_Bsc_Kpi_Group_Rec  => l_Bsc_Kpi_Group_Rec
        ,x_return_status      => x_return_status
        ,x_msg_count          => x_msg_count
        ,x_msg_data           => x_msg_data
       );

Line: 220

PROCEDURE Update_Kpi_Group_Properties (
  p_commit                   IN    VARCHAR2 := FND_API.G_FALSE
, p_New_Indicator_Group      IN    NUMBER
, x_return_status            OUT   NOCOPY  VARCHAR2
, x_msg_count                OUT   NOCOPY  NUMBER
, x_msg_data                 OUT   NOCOPY  VARCHAR2
)
IS
  l_Bsc_Kpi_Group_Rec        BSC_KPI_GROUP_PUB.Bsc_Kpi_Group_Rec;

Line: 234

  SAVEPOINT BscUpdateKpiGroupProperties;

Line: 250

  SELECT COUNT(1)
  INTO
    l_Kpi_Count
  FROM
    bsc_kpis_b
  WHERE
    ind_group_id = p_New_Indicator_Group
    AND prototype_flag <> -2
    AND share_flag <> 2;

Line: 265

  BSC_KPI_GROUP_PUB.Update_Kpi_Group(
    p_commit            => FND_API.G_FALSE
   ,p_Bsc_Kpi_Group_Rec => l_Bsc_Kpi_Group_Rec
   ,x_return_status     => x_return_status
   ,x_msg_count         => x_msg_count
   ,x_msg_data          => x_msg_data
  );

Line: 281

    ROLLBACK TO BscUpdateKpiGroupProperties;

Line: 291

    ROLLBACK TO BscUpdateKpiGroupProperties;

Line: 301

    ROLLBACK TO BscUpdateKpiGroupProperties;

Line: 304

      x_msg_data := x_msg_data || ' -> BSC_COPY_INDICATOR_PUB.Update_Kpi_Group_Properties ';

Line: 306

      x_msg_data := SQLERRM || ' at BSC_COPY_INDICATOR_PUB.Update_Kpi_Group_Properties ';

Line: 309

    ROLLBACK TO BscUpdateKpiGroupProperties;

Line: 312

      x_msg_data := x_msg_data || ' -> BSC_COPY_INDICATOR_PUB.Update_Kpi_Group_Properties ';

Line: 314

      x_msg_data := SQLERRM || ' at BSC_COPY_INDICATOR_PUB.Update_Kpi_Group_Properties ';

Line: 316

END Update_Kpi_Group_Properties;

Line: 361

  BSC_KPI_PVT.Update_Kpi(
    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: 449

  SELECT
    indicator
  FROM
    bsc_kpis_b
  WHERE
    ind_group_id = p_New_Indicator_Group
    AND prototype_flag <> 2
    AND share_flag <> 2
    AND BSC_BIS_KPI_CRUD_PUB.is_KPI_EndToEnd_KPI(short_name) <> 'T'
  ORDER BY
    disp_order,indicator;

Line: 462

  SELECT
    indicator
  FROM
    bsc_kpis_b
  WHERE
    source_indicator = p_Indicator_id
    AND share_flag = 2
    AND prototype_flag <> 2;

Line: 506

    BSC_KPI_PVT.Update_Kpi(
      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: 532

      BSC_KPI_PVT.Update_Kpi(
        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: 613

  SELECT
    indicator
  FROM
    bsc_kpis_b
  WHERE
    source_indicator = p_Indicator
    AND share_flag = 2
    AND prototype_flag <> 2;

Line: 644

    Update_Kpi_Group_Properties (
      p_commit              => FND_API.G_FALSE
     ,p_New_Indicator_Group => p_New_Indicator_Group
     ,x_return_status       => x_return_status
     ,x_msg_count           => x_msg_count
     ,x_msg_data            => x_msg_data
    );

Line: 696

  BSC_KPI_PUB.Update_Kpi_Time_Stamp (
    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: 705

    BSC_KPI_PUB.Update_Kpi_Time_Stamp (
      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: 791

    l_sql := BSC_DESIGNER_PVT.Format_DbLink_String(' SELECT NVL(edw_flag,0) FROM bsc_kpis_b');

Line: 802

    l_sql := BSC_DESIGNER_PVT.Format_DbLink_String('SELECT indicator_type,config_type FROM bsc_kpis_b');

Line: 809

      l_sql := BSC_DESIGNER_PVT.Format_DbLink_String(' SELECT COUNT(1) FROM bsc_kpi_analysis_measures_b');

Line: 983

  l_Bsc_Kpi_Entity_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;

Line: 984

  l_Bsc_Kpi_Entity_Rec.Last_Update_Login := FND_GLOBAL.LOGIN_ID;

Line: 1069

  l_sql := 'SELECT analysis_option0 ,analysis_option1 ,analysis_option2,series_id';

Line: 1079

        UPDATE
          bsc_kpi_analysis_measures_b
        SET
          dataset_id = p_New_DataSet_Map(i)
        WHERE
          indicator = p_Target_Indicator
          AND analysis_option0 =  l_analysis_option0
          AND analysis_option1 =  l_analysis_option1
          AND analysis_option2 =  l_analysis_option2
          AND series_id = l_series_id;

Line: 1092

          UPDATE bsc_kpi_analysis_measures_tl km
          SET name = (SELECT d.name FROM bsc_sys_datasets_tl d WHERE
                      d.dataset_id = p_New_DataSet_Map(i) AND d.language = km.language),
          help = (SELECT d.help FROM bsc_sys_datasets_tl d WHERE
                      d.dataset_id = p_New_DataSet_Map(i) AND d.language = km.language)
          WHERE indicator = p_Target_Indicator;

Line: 1156

PROCEDURE Update_Annual_Current_Period(
  p_commit                   IN    VARCHAR2 := FND_API.G_FALSE
, p_Target_Indicator         IN    NUMBER
, x_return_status            OUT   NOCOPY  VARCHAR2
, x_msg_count                OUT   NOCOPY  NUMBER
, x_msg_data                 OUT   NOCOPY  VARCHAR2
)IS

CURSOR c_Periodicity IS
SELECT
  sp.periodicity_id ,
  sc.fiscal_year
FROM
  bsc_kpi_periodicities kp,
  bsc_sys_periodicities_vl sp,
  bsc_sys_calendars_vl sc
WHERE
  kp.indicator = p_Target_Indicator AND
  sp.periodicity_id = kp.periodicity_id AND
  sp.periodicity_type = 1 AND
  sc.calendar_id = sp.calendar_id ;

Line: 1185

    UPDATE
      bsc_kpi_periodicities
    SET
      current_period = cd.fiscal_year
    WHERE
      indicator = p_Target_Indicator AND
      periodicity_id = cd.periodicity_id ;

Line: 1223

      x_msg_data      :=  x_msg_data||' ->  BSC_COPY_INDICATOR_PUB.Update_Annual_Current_Period ';

Line: 1225

      x_msg_data      :=  SQLERRM||' at  BSC_COPY_INDICATOR_PUB.Update_Annual_Current_Period ';

Line: 1231

      x_msg_data      :=  x_msg_data||' ->  BSC_COPY_INDICATOR_PUB.Update_Annual_Current_Period ';

Line: 1233

      x_msg_data      :=  SQLERRM||' AT  BSC_COPY_INDICATOR_PUB.Update_Annual_Current_Period ';

Line: 1235

END Update_Annual_Current_Period;

Line: 1265

l_Deleted_Periodicities    FND_TABLE_OF_NUMBER;

Line: 1275

    Update_Annual_Current_Period (
      p_commit           => FND_API.G_FALSE
     ,p_Target_Indicator => p_Target_Indicator
     ,x_return_status    => x_return_status
     ,x_msg_count        => x_msg_count
     ,x_msg_data         => x_msg_data
    );

Line: 1288

  l_sql := BSC_DESIGNER_PVT.Format_DbLink_String('SELECT periodicity_id FROM bsc_kpi_periodicities');

Line: 1290

  l_Deleted_Periodicities := FND_TABLE_OF_NUMBER();

Line: 1302

      l_Deleted_Periodicities.EXTEND(1);

Line: 1303

      l_Deleted_Periodicities(l_Deleted_Periodicities.LAST) := l_Periodicity_Id;

Line: 1308

  IF l_Deleted_Periodicities.COUNT > 0 THEN
    l_sql := ' DELETE FROM bsc_kpi_periodicities';

Line: 1311

    FOR i IN 1..l_Deleted_Periodicities.COUNT LOOP
      l_sql := l_sql || l_Deleted_Periodicities(i) || ',';

Line: 1322

      UPDATE
        bsc_kpi_periodicities
      SET
        periodicity_id = p_New_Periodicities(i) ,
        display_order = (i - 1)
      WHERE
        indicator = p_Target_Indicator AND
        periodicity_id = p_Old_Periodicities(i);

Line: 1332

    l_sql := BSC_DESIGNER_PVT.Format_DbLink_String('SELECT calendar_id FROM bsc_kpis_b');

Line: 1339

    l_sql := BSC_DESIGNER_PVT.Format_DbLink_String('SELECT periodicity_type FROM bsc_sys_periodicities');

Line: 1346

    SELECT
      periodicity_type
    INTO
      l_Target_Per_Type
    FROM
      bsc_sys_periodicities
    WHERE
      calendar_id = p_Target_Calendar AND
      periodicity_id = p_New_Periodicities(i);

Line: 1358

      l_sql := ' UPDATE bsc_kpi_periodicities';

Line: 1374

  Update_Annual_Current_Period(
    p_commit           => FND_API.G_FALSE
   ,p_Target_Indicator => p_Target_Indicator
   ,x_return_status    => x_return_status
   ,x_msg_count        => x_msg_count
   ,x_msg_data         => x_msg_data
  );

Line: 1386

  SELECT
    periodicity_id
  INTO
    l_Default_Periodicity
  FROM
    bsc_kpis_b
  WHERE
    indicator = p_Target_Indicator;

Line: 1402

    SELECT
      periodicity_id
    INTO
      l_New_Periodicity
    FROM
      bsc_kpi_periodicities
    WHERE
      indicator = p_Target_Indicator AND
      ROWNUM < 2
    ORDER BY
      display_order;

Line: 1415

  UPDATE
    bsc_kpis_b
  SET
    periodicity_id = l_New_Periodicity ,
    calendar_id = p_Target_Calendar
  WHERE
    indicator = p_Target_Indicator;

Line: 1475

PROCEDURE Update_Bsc_Kpi_Props(
  p_commit                   IN    VARCHAR2 := FND_API.G_FALSE
, p_Target_Indicator         IN    NUMBER
, p_Property_code            IN    VARCHAR2
, p_Property_value           IN    NUMBER
, x_return_status            OUT   NOCOPY  VARCHAR2
, x_msg_count                OUT   NOCOPY  NUMBER
, x_msg_data                 OUT   NOCOPY  VARCHAR2
)IS
l_Count NUMBER := 0;

Line: 1492

  SELECT
    COUNT(1)
  INTO
    l_Count
  FROM
    bsc_kpi_properties
  WHERE
    indicator = p_Target_Indicator AND
    UPPER(property_code) = p_Property_code ;

Line: 1503

    INSERT INTO bsc_kpi_properties (
      indicator
     ,property_code
     ,property_value)
    VALUES
      (p_Target_Indicator
      ,p_Property_code
      ,p_Property_value);

Line: 1512

    UPDATE
      bsc_kpi_properties
    SET
      property_value = p_Property_value
    WHERE
      indicator = p_Target_Indicator AND
      property_code = p_Property_code;

Line: 1550

      x_msg_data      :=  x_msg_data||' ->  BSC_COPY_INDICATOR_PUB.Update_Bsc_Kpi_Props ';

Line: 1552

      x_msg_data      :=  SQLERRM||' at  BSC_COPY_INDICATOR_PUB.Update_Bsc_Kpi_Props ';

Line: 1558

      x_msg_data      :=  x_msg_data||' ->  BSC_COPY_INDICATOR_PUB.Update_Bsc_Kpi_Props ';

Line: 1560

      x_msg_data      :=  SQLERRM||' AT  BSC_COPY_INDICATOR_PUB.Update_Bsc_Kpi_Props ';

Line: 1562

END Update_Bsc_Kpi_Props;

Line: 1631

    l_sql := BSC_DESIGNER_PVT.Format_DbLink_String('SELECT dim_set_id,dim_group_id,dim_group_index FROM bsc_kpi_dim_groups');

Line: 1640

          SELECT
            short_name
          INTO
            l_Short_Name
          FROM
            bsc_sys_dim_groups_vl
          WHERE
            dim_group_id = p_New_Dim_Groups(i);

Line: 1670

    l_sql := BSC_DESIGNER_PVT.Format_DbLink_String('SELECT distinct dim_set_id FROM bsc_kpi_dim_levels_vl');

Line: 1671

    l_sql := l_sql || 'WHERE indicator = :1 MINUS SELECT distinct dim_set_id FROM bsc_kpi_dim_levels_vl';

Line: 1820

SELECT
  kl.dim_set_id,dim_level_index,kl.level_table_name,kl.level_pk_col,dl.dim_level_id,
  parent_level_index,parent_level_rel,table_relation,
  parent_level_index2,parent_level_rel2
FROM
  bsc_kpi_dim_levels_b kl,
  bsc_sys_dim_levels_b dl
WHERE
  kl.indicator = p_Target_Indicator  AND
  kl.level_table_name = dl.level_Table_name
ORDER BY
  dim_set_id,dim_level_index;

Line: 1840

  l_sql := 'SELECT kl.dim_set_id,dim_level_index,kl.level_table_name,kl.level_pk_col,dl.dim_level_id,';

Line: 1877

    UPDATE
      bsc_kpi_dim_level_properties
    SET
      default_key_value = NULL
     ,target_level=1
    WHERE
      indicator = p_Target_Indicator;

Line: 1885

    UPDATE
      bsc_kpi_dim_levels_b
    SET
      default_key_value = NULL
     ,target_level=1
    WHERE
      indicator = p_Target_Indicator;

Line: 1893

/*    Update_Bsc_Kpi_Props (
       p_commit             =>  FND_API.G_FALSE
      ,p_Target_Indicator   =>  p_Target_Indicator
      ,p_Property_code      =>  'DB_TRANSFORM'
      ,p_Property_value      => 2
      ,x_return_status      =>  x_return_status
      ,x_msg_count          =>  x_msg_count
      ,x_msg_data           =>  x_msg_data
    );

Line: 1908

    l_sql := 'UPDATE bsc_kpi_dim_levels_b tar SET tar.default_key_value = (SELECT src.default_key_value ';

Line: 1914

    l_sql := 'UPDATE bsc_kpi_dim_level_properties tar SET tar.default_key_value = (SELECT src.default_key_value ';

Line: 1992

  SELECT
    dim_set_id
  INTO
    l_dim_set_id
  FROM
    bsc_oaf_analysys_opt_comb_v da,
    bsc_db_basic_dim_sets_v ds
  WHERE
    ds.indicator = da.indicator AND
    ds.a0 = da.analysis_option0 AND
    ds.a1 = da.analysis_option1 AND
    ds.a2 = da.analysis_option2 AND
    ds.series_id = da.series_id AND
    da.default_flag = 1 AND
    ds.indicator = p_Target_Indicator;

Line: 2010

    SELECT
      COUNT(1)
    INTO
      l_count
    FROM
      bsc_kpi_dim_levels_b
    WHERE
      indicator = p_Target_Indicator AND
      dim_set_id = l_dim_set_id AND
      default_key_value IS NULL AND UPPER(default_value)= 'C';

Line: 2022

      Update_Bsc_Kpi_Props (
         p_commit             =>  FND_API.G_FALSE
        ,p_Target_Indicator   =>  p_Target_Indicator
        ,p_Property_code      =>  'COLOR_BY_TOTAL'
        ,p_Property_value      => 0
        ,x_return_status      =>  x_return_status
        ,x_msg_count          =>  x_msg_count
        ,x_msg_data           =>  x_msg_data
      );

Line: 2101

SELECT
  indicator_type,config_type
FROM
  bsc_kpis_vl
WHERE
  indicator = p_Target_Indicator;

Line: 2120

    SELECT
      COUNT(1)
    INTO
      l_Count
    FROM
      bsc_kpi_dim_levels_b
    WHERE
      indicator = p_Target_Indicator;

Line: 2133

    Update_Bsc_Kpi_Props (
       p_commit             =>  FND_API.G_FALSE
      ,p_Target_Indicator   =>  p_Target_Indicator
      ,p_Property_code      =>  'PL_DRILL_FLAG'
      ,p_Property_value      => l_Drill_Flag
      ,x_return_status      =>  x_return_status
      ,x_msg_count          =>  x_msg_count
      ,x_msg_data           =>  x_msg_data
    );

Line: 2217

  SELECT
    COUNT(1)
  INTO
    l_Count
  FROM
    bsc_kpi_data_tables
  WHERE
    indicator = p_Target_Indicator;

Line: 2227

   INSERT INTO bsc_kpi_data_tables (
     indicator
    ,periodicity_id
    ,dim_set_id
    ,level_comb
    ,table_name
    ,filter_condition)
   (SELECT
     indicator indicator
    ,periodicity_id periodicity_id
    ,0 dim_set_id
    ,'?' level_comb
    ,NULL table_name
    ,NULL filter_condition
    FROM
      bsc_kpi_periodicities
    WHERE
      INDICATOR = p_Target_Indicator);

Line: 2307

SELECT
  distinct source_lang
 ,name
FROM
  bsc_kpis_tl
WHERE indicator = p_Target_Indicator;

Line: 2327

    SELECT
      COUNT(1)
    INTO
      l_Count
    FROM
      bsc_kpis_vl
    WHERE
      UPPER(name) = UPPER(cd.Name);

Line: 2347

      UPDATE
        bsc_kpis_tl
      SET
        name = l_new_name
      WHERE
        indicator = p_Target_Indicator AND
        source_lang = cd.source_lang;

Line: 2355

        UPDATE
          bsc_kpis_tl
        SET
          help = p_Description
        WHERE
          indicator = p_Target_Indicator AND
          source_lang = cd.source_lang;

Line: 2510

  SELECT
    short_name
  INTO
    l_Short_Name
  FROM
    bsc_kpis_b
  WHERE
    indicator = l_Target_Indicator;

Line: 2606

      BSC_DESIGNER_PVT.Deflt_Update_Dim_Values (
        x_indicator => l_Target_Indicator
      );

Line: 2610

      BSC_DESIGNER_PVT.Deflt_Update_Dim_Names (
        x_indicator => l_Target_Indicator
      );

Line: 2641

    BSC_DESIGNER_PVT.Deflt_Update_SN_FM_CM (
      x_indicator => l_Target_Indicator
    );

Line: 2725

  BSC_KPI_PUB.Update_Kpi_Time_Stamp
  ( 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
  );