APPS.BSC_KPI_SERIES_PUB SQL Statements

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

Line: 38

  SELECT
    km.kpi_measure_id
  FROM
    bsc_kpi_analysis_measures_b km
  WHERE km.indicator    = p_Indicator AND
    km.analysis_option0 = p_Analysis_Option0 AND
    km.analysis_option1 = p_Analysis_Option1 AND
    km.analysis_option2 = p_Analysis_Option2 AND
    km.series_id        = p_Series_Id ;

Line: 49

  SELECT
    a0_default, a1_default, a2_default
  FROM
    bsc_db_color_ao_defaults_v
  WHERE
    indicator = p_Indicator;

Line: 57

  SELECT
    source
  FROM
    bsc_sys_datasets_vl
  WHERE
    dataset_id = p_Dataset_Id;

Line: 106

  BSC_KPI_MEASURE_PROPS_PUB.Update_Kpi_Measure_Props (
      p_Commit          =>  FND_API.G_FALSE
    , p_kpi_measure_rec =>  l_kpi_measure_props_rec
    , p_cascade_shared  =>  FALSE
    , x_return_status   =>  x_return_status
    , x_msg_count       =>  x_msg_count
    , x_msg_data        =>  x_msg_data
  ) ;

Line: 186

  SELECT
    km.kpi_measure_id
  FROM
    bsc_kpi_analysis_measures_b km
  WHERE km.indicator    = p_Indicator AND
    km.analysis_option0 = p_Analysis_Option0 AND
    km.analysis_option1 = p_Analysis_Option1 AND
    km.analysis_option2 = p_Analysis_Option2 AND
    km.series_id        = p_Series_Id ;

Line: 197

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

Line: 231

    BSC_KPI_MEASURE_PROPS_PUB.Update_Kpi_Measure_Props (
        p_Commit          =>  FND_API.G_FALSE
      , p_kpi_measure_rec =>  l_kpi_measure_props_rec
      , p_cascade_shared  =>  FALSE
      , x_return_status   =>  x_return_status
      , x_msg_count       =>  x_msg_count
      , x_msg_data        =>  x_msg_data
    ) ;

Line: 338

CURSOR c_Preselected_Series(p_Analysis_Option0 NUMBER,p_Analysis_Option1 NUMBER,p_Analysis_Option2 NUMBER) IS
SELECT
  series_id
FROM
  bsc_kpi_analysis_measures_b
WHERE
  indicator = p_Indicator AND
  analysis_option0 = p_Analysis_Option0 AND
  analysis_option1 = p_Analysis_Option1 AND
  analysis_option2 = p_Analysis_Option2 AND
  default_value = 1;

Line: 355

  SELECT
    a0_default,a1_default,a2_default
  INTO
    l_AnaOpt0_Default, l_AnaOpt1_Default, l_AnaOpt2_Default
  FROM
    bsc_db_color_ao_defaults_v
  WHERE
    indicator = p_Indicator;

Line: 364

  OPEN c_Preselected_Series (l_AnaOpt0_Default,l_AnaOpt1_Default,l_AnaOpt2_Default);

Line: 365

  FETCH c_Preselected_Series INTO l_First_Series_Id;

Line: 366

    UPDATE
      bsc_kpi_analysis_measures_b
    SET
      default_value = 0
    WHERE indicator = p_Indicator AND
      analysis_option0 = l_AnaOpt0_Default AND
      analysis_option1 = l_AnaOpt1_Default AND
      analysis_option2 = l_AnaOpt2_Default AND
      series_id <> l_First_Series_Id;

Line: 376

    UPDATE
      bsc_kpi_analysis_measures_b
    SET
      default_value = 1
    WHERE indicator = p_Indicator AND
      analysis_option0 = l_AnaOpt0_Default AND
      analysis_option1 = l_AnaOpt1_Default AND
      analysis_option2 = l_AnaOpt2_Default AND
      series_id = l_First_Series_Id;

Line: 385

  CLOSE c_Preselected_Series;

Line: 406

PROCEDURE Update_Color_Structure_Flags (
  p_commit            IN   VARCHAR2 := FND_API.G_FALSE
 ,p_Indicator         IN   NUMBER
 ,p_Action_Flag       IN   NUMBER := 3
 ,x_return_status     OUT NOCOPY   VARCHAR2
 ,x_msg_count         OUT NOCOPY   NUMBER
 ,x_msg_data          OUT NOCOPY   VARCHAR2
) IS

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

Line: 472

            x_msg_data      :=  x_msg_data||' ->BSC_KPI_SERIES_PUB.Update_Color_Structure_Flags ';

Line: 474

            x_msg_data      :=  SQLERRM||' at BSC_KPI_SERIES_PUB.Update_Color_Structure_Flags ';

Line: 476

END Update_Color_Structure_Flags;

Line: 547

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

Line: 763

PROCEDURE Update_Analysis_Measure_UI(
  p_commit                IN   VARCHAR2 := FND_API.G_FALSE
 ,p_Indicator             IN   NUMBER
 ,p_Analysis_Option0      IN   NUMBER
 ,p_Analysis_Option1      IN   NUMBER
 ,p_Analysis_Option2      IN   NUMBER
 ,p_Series_Id             IN   NUMBER
 ,p_Axis                  IN   NUMBER := 0
 ,p_Series_Type           IN   NUMBER := 0
 ,p_Bm_Flag               IN   NUMBER := 0
 ,p_Budget_Flag           IN   NUMBER := 0
 ,p_Default_Flag          IN   NUMBER := 0
 ,p_Stack_Series_Id       IN   NUMBER := NULL
 ,p_Series_Name           IN   VARCHAR2
 ,p_Series_Help           IN   VARCHAR2
 ,p_dataset_Id            IN   NUMBER := -1
 ,p_Color_Values          IN   FND_TABLE_OF_NUMBER := NULL
 ,p_default_calculation   IN   NUMBER := NULL
 ,p_time_stamp            IN   VARCHAR2 := NULL
 ,x_return_status         OUT NOCOPY   VARCHAR2
 ,x_msg_count             OUT NOCOPY   NUMBER
 ,x_msg_data              OUT NOCOPY   VARCHAR2
) IS
  l_Anal_Opt_Rec          BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;

Line: 795

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

Line: 839

    SELECT
      dim_set_id
    INTO
      l_DimSet_Id
    FROM
      bsc_db_dataset_dim_sets_v v
    WHERE
      v.indicator = p_indicator AND
      v.A0 = p_Analysis_Option0 AND
      v.A1 = p_Analysis_Option1 AND
      v.A2 = p_Analysis_Option2;

Line: 851

    SELECT
      name
    INTO
      l_Option_Name
    FROM
      bsc_kpi_analysis_options_vl
    WHERE
      indicator = p_Indicator AND
      analysis_group_id = 0 AND
      option_id = p_Analysis_Option0;

Line: 862

    BSC_BIS_KPI_MEAS_PUB.Update_KPI_Analysis_Options -- This will cascade to the shared
    (       p_commit                =>  FND_API.G_FALSE
        ,   p_kpi_id                =>  p_Indicator
        ,   p_data_source           =>  l_Measure_Source
        ,   p_analysis_group_id     =>  0
        ,   p_analysis_option_id0   =>  p_Analysis_Option0
        ,   p_analysis_option_id1   =>  p_Analysis_Option1
        ,   p_analysis_option_id2   =>  p_Analysis_Option2
        ,   p_series_id             =>  0
        ,   p_data_set_id           =>  p_DataSet_Id
        ,   p_dim_set_id            =>  l_DimSet_Id
        ,   p_option0_Name          =>  l_Option_Name
        ,   p_option1_Name          =>  NULL
        ,   p_option2_Name          =>  NULL
        ,   p_measure_short_name    =>  NULL
        ,   p_dim_obj_short_names   =>  NULL
        ,   p_default_short_names   =>  NULL
        ,   p_view_by_name          =>  NULL
        ,   p_measure_name          =>  p_Series_Name
        ,   p_measure_help          =>  p_Series_Help
        ,   p_default_value         =>  p_Default_Flag
        ,   p_time_stamp            =>  NULL
        ,   p_update_ana_opt        =>  TRUE
        ,   x_return_status         =>  x_return_status
        ,   x_msg_count             =>  x_msg_count
        ,   x_msg_data              =>  x_msg_data
    );

Line: 909

   BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Measures(
       p_commit        =>    FND_API.G_FALSE
      ,p_Anal_Opt_Rec  =>    l_Anal_Opt_Rec
      ,x_return_status =>    x_return_status
      ,x_msg_count     =>    x_msg_count
      ,x_msg_data      =>    x_msg_data
   );

Line: 938

      BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Measures(
          p_commit        =>    FND_API.G_FALSE
         ,p_Anal_Opt_Rec  =>    l_Anal_Opt_Rec
         ,x_return_status =>    x_return_status
         ,x_msg_count     =>    x_msg_count
         ,x_msg_data      =>    x_msg_data
      );

Line: 1047

            x_msg_data      :=  x_msg_data||' ->BSC_KPI_SERIES_PUB.Update_Analysis_Measure_UI ';

Line: 1049

            x_msg_data      :=  SQLERRM||'BSC_KPI_SERIES_PUB.Update_Analysis_Measure_UI ';

Line: 1054

            x_msg_data      :=  x_msg_data||' ->BSC_KPI_SERIES_PUB.Update_Analysis_Measure_UI ';

Line: 1056

            x_msg_data      :=  SQLERRM||' at BSC_KPI_SERIES_PUB.Update_Analysis_Measure_UI ';

Line: 1058

END Update_Analysis_Measure_UI;

Line: 1067

PROCEDURE Delete_Analysis_Measure_UI(
  p_commit                IN   VARCHAR2 := FND_API.G_FALSE
 ,p_Indicator             IN   NUMBER
 ,p_Analysis_Option0      IN   NUMBER
 ,p_Analysis_Option1      IN   NUMBER
 ,p_Analysis_Option2      IN   NUMBER
 ,p_Series_Id             IN   NUMBER
 ,p_time_stamp            IN   VARCHAR2 := NULL
 ,x_return_status         OUT NOCOPY   VARCHAR2
 ,x_msg_count             OUT NOCOPY   NUMBER
 ,x_msg_data              OUT NOCOPY   VARCHAR2
) IS
  l_Anal_Opt_Rec          BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;

Line: 1087

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

Line: 1126

   SELECT
     kpi_measure_id
   INTO
     l_kpi_measure_id
   FROM
     bsc_kpi_analysis_measures_b
   WHERE
     indicator = p_Indicator AND
     analysis_option0 = p_Analysis_Option0 AND
     analysis_option1 = p_Analysis_Option1 AND
     analysis_option2 = p_Analysis_Option2 AND
     series_id        = p_Series_Id;

Line: 1150

   BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Measures(
       p_commit        =>    FND_API.G_FALSE
      ,p_Anal_Opt_Rec  =>    l_Anal_Opt_Rec
      ,x_return_status =>    x_return_status
      ,x_msg_count     =>    x_msg_count
      ,x_msg_data      =>    x_msg_data
   );

Line: 1161

   Delete_Kpi_Series_Colors(
       p_commit        =>    FND_API.G_FALSE
      ,p_Anal_Opt_Rec  =>    l_Anal_Opt_Rec
      ,x_return_status =>    x_return_status
      ,x_msg_count     =>    x_msg_count
      ,x_msg_data      =>    x_msg_data
   );

Line: 1187

    BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Measures(
        p_commit        =>    FND_API.G_FALSE
       ,p_Anal_Opt_Rec  =>    l_Anal_Opt_Rec
       ,x_return_status =>    x_return_status
       ,x_msg_count     =>    x_msg_count
       ,x_msg_data      =>    x_msg_data
    );

Line: 1198

    Delete_Kpi_Series_Colors(
        p_commit        =>    FND_API.G_FALSE
       ,p_Anal_Opt_Rec  =>    l_Anal_Opt_Rec
       ,x_return_status =>    x_return_status
       ,x_msg_count     =>    x_msg_count
       ,x_msg_data      =>    x_msg_data
    );

Line: 1250

            x_msg_data      :=  x_msg_data||' ->BSC_KPI_SERIES_PUB.Delete_Analysis_Measure_UI ';

Line: 1252

            x_msg_data      :=  SQLERRM||'BSC_KPI_SERIES_PUB.Delete_Analysis_Measure_UI ';

Line: 1257

            x_msg_data      :=  x_msg_data||' ->BSC_KPI_SERIES_PUB.Delete_Analysis_Measure_UI ';

Line: 1259

            x_msg_data      :=  SQLERRM||' at BSC_KPI_SERIES_PUB.Delete_Analysis_Measure_UI ';

Line: 1261

END Delete_Analysis_Measure_UI;

Line: 1288

  DELETE FROM bsc_kpi_series_colors
  WHERE indicator   =  p_Anal_Opt_Rec.Bsc_Kpi_Id AND
  analysis_option0  =  p_Anal_Opt_Rec.Bsc_Option_Group0 AND
  analysis_option1  =  p_Anal_Opt_Rec.Bsc_Option_Group1 AND
  analysis_option2  =  p_Anal_Opt_Rec.Bsc_Option_Group2 AND
  series_id =  p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;

Line: 1299

     INSERT INTO bsc_kpi_series_colors (indicator
                                       ,analysis_option0
                                       ,analysis_option1
                                       ,analysis_option2
                                       ,series_id
                                       ,bm_id
                                       ,color
                                      )
                                VALUES( p_Anal_Opt_Rec.Bsc_Kpi_Id
                                       ,p_Anal_Opt_Rec.Bsc_Option_Group0
                                       ,p_Anal_Opt_Rec.Bsc_Option_Group1
                                       ,p_Anal_Opt_Rec.Bsc_Option_Group2
                                       ,p_Anal_Opt_Rec.Bsc_Dataset_Series_Id
                                       ,l_bm_id
                                       ,l_bm_color
                                       );

Line: 1370

PROCEDURE Delete_Kpi_Series_Colors(
  p_commit          IN   VARCHAR2 := FND_API.G_FALSE
 ,p_Anal_Opt_Rec    IN   BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
 ,x_return_status   OUT NOCOPY   VARCHAR2
 ,x_msg_count       OUT NOCOPY   NUMBER
 ,x_msg_data        OUT NOCOPY   VARCHAR2
) IS
BEGIN

  SAVEPOINT Delete_Kpi_SeriesColor_PUB;

Line: 1383

  DELETE FROM bsc_kpi_series_colors
  WHERE indicator   =  p_Anal_Opt_Rec.Bsc_Kpi_Id AND
  analysis_option0  =  p_Anal_Opt_Rec.Bsc_Option_Group0 AND
  analysis_option1  =  p_Anal_Opt_Rec.Bsc_Option_Group1 AND
  analysis_option2  =  p_Anal_Opt_Rec.Bsc_Option_Group2 AND
  series_id =  p_Anal_Opt_Rec.Bsc_Dataset_Series_Id;

Line: 1397

        ROLLBACK TO Delete_Kpi_SeriesColor_PUB;

Line: 1407

        ROLLBACK TO Delete_Kpi_SeriesColor_PUB;

Line: 1417

        ROLLBACK TO Delete_Kpi_SeriesColor_PUB;

Line: 1420

            x_msg_data      :=  x_msg_data||' ->BSC_KPI_SERIES_PUB.Delete_Kpi_Series_Colors ';

Line: 1422

            x_msg_data      :=  SQLERRM||'BSC_KPI_SERIES_PUB.Delete_Kpi_Series_Colors ';

Line: 1425

        ROLLBACK TO Delete_Kpi_SeriesColor_PUB;

Line: 1428

            x_msg_data      :=  x_msg_data||' ->BSC_KPI_SERIES_PUB.Delete_Kpi_Series_Colors ';

Line: 1430

            x_msg_data      :=  SQLERRM||' at BSC_KPI_SERIES_PUB.Delete_Kpi_Series_Colors ';

Line: 1433

END Delete_Kpi_Series_Colors;

Line: 1489

 ,p_Delete_Mode           IN   NUMBER := 0
 ,x_return_status         OUT NOCOPY   VARCHAR2
 ,x_msg_count             OUT NOCOPY   NUMBER
 ,x_msg_data              OUT NOCOPY   VARCHAR2
) IS

  l_Structure_Change  BOOLEAN := FALSE;

Line: 1512

  SELECT
    db.dim_set_id, db.dataset_id, count(1)
  FROM
    bsc_db_dataset_dim_sets_v db,
    bsc_sys_datasets_vl ds
  WHERE
    ds.source = 'BSC' AND
    db.indicator =  p_indicator AND db.dataset_id = ds.dataset_id
  GROUP BY db.dim_set_id, db.dataset_id
  ORDER by db.dim_set_id, db.dataset_id;

Line: 1524

  SELECT
    db.dim_set_id, db.dataset_id, count(1)
  FROM
    bsc_db_dataset_dim_sets_v db,
    bsc_sys_datasets_vl ds
  WHERE
    ds.source = 'BSC' AND
    db.indicator =  p_indicator AND
    db.dataset_id = ds.dataset_id AND
    db.A0 = p_Analysis_Option0 AND
    db.A1 = p_Analysis_Option1 AND
    db.A2 = p_Analysis_Option2
  GROUP BY db.dim_set_id, db.dataset_id
  ORDER BY db.dim_set_id, db.dataset_id;

Line: 1540

  SELECT
    db.dim_set_id, db.dataset_id
  FROM
    bsc_db_dataset_dim_sets_v db,
    bsc_sys_datasets_vl ds
  WHERE
    ds.source = 'BSC' AND
    db.indicator =  p_indicator AND
    db.dataset_id = ds.dataset_id AND
    db.A0 = p_Analysis_Option0 AND
    db.A1 = p_Analysis_Option1 AND
    db.A2 = p_Analysis_Option2 AND
    db.series_id = p_Series_Id
  ORDER BY db.dim_set_id, db.dataset_id;

Line: 1556

  SELECT DISTINCT
    dim_set_id
  FROM
    bsc_db_dataset_dim_sets_v
  WHERE
    indicator = p_Indicator AND
    A0 = p_Analysis_Option0 AND
    A1 = p_Analysis_Option1 AND
    A2 = p_Analysis_Option2;

Line: 1568

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

Line: 1610

            l_All_Comb_Map_New.DELETE(j);

Line: 1631

          l_All_Comb_Map_New.DELETE(j);

Line: 1642

  IF p_Delete_Mode = 0 THEN
    FOR i in 1..p_new_dataset_map.COUNT LOOP
      l_Combination_Cnt := Check_DimSet_DataSet_Exists (
                        l_All_Comb_Map_New,
                        l_Current_DimSet,
                        p_new_dataset_map(i)
                      );

Line: 1732

PROCEDURE Update_Kpi_Time_Stamp(
  p_commit              IN      varchar2 := FND_API.G_FALSE
 ,p_Indicator           IN      NUMBER
 ,x_return_status       OUT NOCOPY     varchar2
 ,x_msg_count           OUT NOCOPY     number
 ,x_msg_data            OUT NOCOPY     varchar2
) IS

  l_Bsc_Kpi_Entity_Rec    BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;

Line: 1743

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

Line: 1757

   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: 1767

    BSC_DESIGNER_PVT.Deflt_Update_AOPTS ( x_indicator => l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id);

Line: 1771

     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: 1781

     BSC_DESIGNER_PVT.Deflt_Update_AOPTS ( x_indicator => cd.indicator );

Line: 1803

            x_msg_data      :=  x_msg_data||' ->BSC_KPI_SERIES_PUB.Update_Kpi_Time_Stamp ';

Line: 1805

            x_msg_data      :=  SQLERRM||' at BSC_KPI_SERIES_PUB.Update_Kpi_Time_Stamp ';

Line: 1807

END Update_Kpi_Time_Stamp;