DBA Data[Home] [Help]

APPS.BSC_BIS_KPI_MEAS_PUB SQL Statements

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

Line: 21

REM |                      Create_KPI_Analysis_Options & Update_KPI         |
REM | 28-Aug-2003 Adeulgao fixed bug#3108877                                |
REM | 02-Sep-2003 ADRAO    fixed bug#3123858                                |
REM | 10-Sep-2003 ADEULGAO fixed bug#3126401                                |
REM | 11-Sep-2003 ADEULGAO fixed bug#3136397                                |
REM | 11-Sep-2003 ADEULGAO fixed bug#3139925                                |
REM | 13-Sep-2003 mahrao fixed bug#3099977, used p_create_view flag in impo.|
REM | 20-OCT-2003 PAJOHRI  Bug#3180374, and modularization of the code      |
REM | 20-OCT-2003 PAJOHRI  Bug#3179995, added two new procedures            |
REM |                      Delete_Dim_Objs_In_DSet, Create_Dim_Objs_In_DSet |
REM | 20-OCT-2003 PAJOHRI  Bug #3179995                                     |
REM | 04-NOV-2003 PAJOHRI  Bug #3152258                                     |
REM | 14-NOV-2003 PAJOHRI  Bug #3248729                                     |
REM | 08-DEC-2003 KYADAMAK Bug #3225685                                     |
REM | 07-JAN-2004 PAJOHRI  Bug #3343860, created two new procudures         |
REM |          Create_Default_Kpi_Dim_Object & Delete_Default_Kpi_Dim_Object|
REM |          to create and delete default row in BSC_KPI_DIM_LEVELS_VL    |
REM | 16-JAN-2004 PAJOHRI  Bug #3372305                                     |
REM | 29-JAN-2004 PAJOHRI  Bug #3404081                                     |
REM | 15-MAR-2004 PAJOHRI  Bug #3504996, Assign_DSet_Analysis_Options       |
REM |                      procedure is modified, to flag stuct. changes.   |
REM | 30-Mar-2004 ADEULGAO fixed DBI Report Issues                          |
REM |                      added apis 1. Is_Pmv_Viewby_Report()             |
REM |                                 2. is_Abstract_Pmv_Dimension()        |
REM |                      removed condition  IF (l_view_by = 'Y')          |
REM |                      in Get_Default_Viewby_For_Measure()              |
REM | 12-APR-2004 PAJOHRI  Bug #3426566, added conditions to filter those   |
REM |                      Dimension whose Short_Name = 'UNASSIGNED'        |
REM | 13-APR-2004 ASHANKAR BUG#3565772 Modified the fucntions               |
REM |                      Is_Time_In_Dim_Object,Is_Time_With_Measure       |
REM | 16-APR-2004 ASHANKAR BUG#3550054 added the validate_list_button       |
REM |                      function                                         |
REM | 23-APR-2004 ASHANKAR  Bug #3518610,Added the fucntion Validate        |
REM |                       listbutton                                      |
REM | 01-JUN-2004 ADRAO    Fixed for Bug#3663301, initiated an Action Flag  |
REM |                      Change when Default Measure is changed for KPI   |
REM |                      Changed Update_Kpi API                           |
REM | 14-JUN-2004 ADRAO    added Short_Name to Analysis Option for          |
REM |                   Enh#3540302 (ADMINISTRATOR TO ADD KPI TO KPI REGION)|
REM | 17-AUG-2004 WLEUNG   added function Remove_Empty_Dims_For_DimSet      |
REM |                      Bug #3784852                                     |
REM | 29-SEP-2004 ashankar added modules is_Period_Circular,                |
REM |                      Parse_Base_Periods and Find_Period_CircularRef   |
REM |                      for bug#3908204                                  |
REM | 10-OCT-2004 ashankar Moved Parse_Base_Periods to BSC_UTILITY package  |
REM |                      and renamed it to Parse_String to make it Generic|
REM |                      enough.This was done as per the review comment   |
REM | 18-JAN-2005 WLEUNG   bug 4036171 fix Get_Default_Viewby_For_Measure   |
REM | 21-FEB-2005 ankagarw  enh# 3862703                                    |
REM | 11-APR-2005 kyadamak bug#4290070 Not validation views for rolling dims|
REM |  18-Jul-2005 ppandey  Enh #4417483, Restrict Internal/Calendar Dims   |
REM |  22-AUG-2005 ashankar Bug#4220400 Modifed the UPDATE_KPI API          |
REM | 19-SEP-2005 adrao     fixed Bug#4615361 modified API Update_Dim_Set   |
REM | 06-Jan-2006 akoduri   Enh#4739401 - Hide Dimensions/Dim Objects       |
REM | 24-Jan-2006 akoduri   Bug#4958055  Dgrp dimension not getting deleted |
REM |                       while disassociating from objective             |
REM | 15-FEB-2006 akoduri  Bug#4305536  Support new attribute type in       |
REM |                      Objective designer                               |
REM | 11-APR-2006 visuri   Bug#5151997 Report not going in Prototype after  |
REM |                      adding PMF Dim Obj                               |
REM | 19-APR-2006 visuri   Bug#5080308 Commented view by validation         |
REM | 31-Jan-2007 akoduri  Enh #5679096 Migration of multibar functionality |
REM |                      from VB to Html                                  |
REM | 13-APR-2007 ankgoel  Bug#5943068 Impact on common dimension by dim    |
REM |                      reorder in a dim set                             |
REM | 09-Mar-2007 akoduri  Bug#5925299 Key Items are not retained in update |
REM |                      and in reordering of dim objects in dimension set|
REM | 20-APR-2007 vtulasi   Warning and caching issue                       |
REM | 07-JUN-2007 vtulasi   Prototype Flag issue                            |
REM | 06-JUN-2007 akoduri   Bug 5958688 Enable YTD as default at KPI        |
REM +=======================================================================+
*/
--PMV abastract dimension type
TIME_COMP_TYPE   CONSTANT  VARCHAR2(100) := 'TIME_COMPARISON_TYPE';
Line: 156

  SELECT
    d.dim_level_id
  INTO
    l_dim_level_id
  FROM
    bsc_kpi_dim_levels_b kd,
    bsc_sys_dim_levels_b d
  WHERE
    kd.indicator = p_indicator
    AND kd.level_table_name = d.level_table_name
    AND kd.dim_set_id = p_dim_set_id
    AND kd.level_table_name = p_level_table_name;
Line: 187

  BSC_DIMENSION_SETS_PUB.Update_Dim_Levels (
    p_commit         =>  FND_API.G_FALSE
   ,p_Dim_Set_Rec    =>  l_Dim_Set_Rec
   ,x_return_status  =>  x_return_status
   ,x_msg_count      =>  x_msg_count
   ,x_msg_data       =>  x_msg_data
  );
Line: 210

  BSC_DIMENSION_SETS_PUB.Update_Dim_Level_Properties (
    p_commit         =>  FND_API.G_FALSE
   ,p_Dim_Set_Rec    =>  l_Dim_Set_Rec
   ,x_return_status  =>  x_return_status
   ,x_msg_count      =>  x_msg_count
   ,x_msg_data       =>  x_msg_data
  );
Line: 263

   SELECT Indicator_Type
   INTO   l_Indic_Type
   FROM   BSC_KPIS_B
   WHERE  Indicator = p_Kpi_ID;
Line: 301

            SELECT COUNT(*) INTO l_Count
            FROM   BSC_KPI_ANALYSIS_OPTIONS_B
            WHERE  Indicator                = p_Kpi_Id
            AND    Analysis_Group_Id        = p_Analysis_Group_ID
            AND    Option_Id                = p_Option_ID
            AND    Parent_Option_Id         = x_Parent_Option_ID;
Line: 319

            SELECT COUNT(*) INTO l_Count
            FROM   BSC_KPI_ANALYSIS_OPTIONS_B
            WHERE  Indicator                = p_Kpi_Id
            AND    Analysis_Group_Id        = p_Analysis_Group_ID
            AND    Option_Id                = p_Option_ID
            AND    Parent_Option_Id         = x_Parent_Option_ID
            AND    Grandparent_Option_Id    = x_GrandParent_Option_ID;
Line: 373

    SELECT   Analysis_Group_Id
         ,   Num_Of_Options
         ,   Dependency_Flag
    FROM     BSC_KPI_ANALYSIS_GROUPS
    WHERE    Indicator = p_kpi_id
    ORDER BY Analysis_Group_Id;
Line: 402

    SELECT  indicator
    FROM    BSC_KPIS_B
    WHERE   Source_Indicator  =  p_kpi_id
    AND     Prototype_Flag   <>  2;
Line: 413

    SELECT COUNT(*) INTO l_Count
    FROM   BSC_KPI_DIM_LEVELS_B
    WHERE  Indicator  =  l_bsc_dimset_rec.Bsc_Kpi_Id
    AND    Dim_Set_Id =  l_bsc_dimset_rec.Bsc_Dim_Set_Id;
Line: 499

PROCEDURE Delete_Default_Kpi_Dim_Object
(       p_commit                IN              VARCHAR2   := FND_API.G_TRUE
    ,   p_kpi_id                IN              NUMBER
    ,   p_dim_set_id            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: 511

    SELECT  indicator
    FROM    BSC_KPIS_B
    WHERE   Source_Indicator  =  p_kpi_id
    AND     Prototype_Flag   <>  2;
Line: 523

    BSC_DIMENSION_SETS_PUB.Delete_Dim_Levels
    (       p_commit                =>  FND_API.G_FALSE
        ,   p_Dim_Set_Rec           =>  l_bsc_dimset_rec
        ,   x_return_status         =>  x_return_status
        ,   x_msg_count             =>  x_msg_count
        ,   x_msg_data              =>  x_msg_data
    );
Line: 538

        BSC_DIMENSION_SETS_PUB.Delete_Dim_Levels
        (       p_commit                =>  FND_API.G_FALSE
            ,   p_Dim_Set_Rec           =>  l_bsc_dimset_rec
            ,   x_return_status         =>  x_return_status
            ,   x_msg_count             =>  x_msg_count
            ,   x_msg_data              =>  x_msg_data
        );
Line: 583

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_Default_Kpi_Dim_Object ';
Line: 585

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_Default_Kpi_Dim_Object ';
Line: 592

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_Default_Kpi_Dim_Object ';
Line: 594

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_Default_Kpi_Dim_Object ';
Line: 597

END Delete_Default_Kpi_Dim_Object;
Line: 627

    SELECT   A.Dim_Group_Id
          ,  A.Dim_Group_Index
          ,  B.Short_Name
    FROM     BSC_KPI_DIM_GROUPS     A
          ,  BSC_SYS_DIM_GROUPS_VL  B
    WHERE    A.Indicator    =  p_kpi_id
    AND      A.Dim_Set_Id   =  p_dim_set_id
    AND      A.Dim_Group_ID =  B.Dim_Group_ID
    ORDER BY A.Dim_Group_Index;
Line: 639

    SELECT  indicator
    FROM    BSC_KPIS_B
    WHERE   Source_Indicator  =  p_kpi_id
    AND     Prototype_Flag   <>  2;
Line: 682

            BSC_DIMENSION_SETS_PUB.Update_Dim_Group_In_Dset
            (       p_commit              =>    FND_API.G_FALSE
                ,   p_Dim_Set_Rec         =>    l_bsc_dimset_rec
                ,   p_create_Dim_Lev_Grp  =>    FALSE
                ,   x_return_status       =>    x_return_status
                ,   x_msg_count           =>    x_msg_count
                ,   x_msg_data            =>    x_msg_data
            );
Line: 697

                BSC_DIMENSION_SETS_PUB.Update_Dim_Group_In_Dset
                (       p_commit              =>    FND_API.G_FALSE
                    ,   p_Dim_Set_Rec         =>    l_bsc_dimset_rec
                    ,   p_create_Dim_Lev_Grp  =>    FALSE
                    ,   x_return_status       =>    x_return_status
                    ,   x_msg_count           =>    x_msg_count
                    ,   x_msg_data            =>    x_msg_data
                );
Line: 723

                BSC_DIMENSION_SETS_PUB.Update_Dim_Group_In_Dset
                (       p_commit              =>    FND_API.G_FALSE
                    ,   p_Dim_Set_Rec         =>    l_bsc_dimset_rec
                    ,   p_create_Dim_Lev_Grp  =>    FALSE
                    ,   x_return_status       =>    x_return_status
                    ,   x_msg_count           =>    x_msg_count
                    ,   x_msg_data            =>    x_msg_data
                );
Line: 738

                    BSC_DIMENSION_SETS_PUB.Update_Dim_Group_In_Dset
                    (       p_commit              =>    FND_API.G_FALSE
                        ,   p_Dim_Set_Rec         =>    l_bsc_dimset_rec
                        ,   p_create_Dim_Lev_Grp  =>    FALSE
                        ,   x_return_status       =>    x_return_status
                        ,   x_msg_count           =>    x_msg_count
                        ,   x_msg_data            =>    x_msg_data
                    );
Line: 762

            ,   p_delete                =>    TRUE
            ,   x_return_status         =>    x_return_status
            ,   x_msg_count             =>    x_msg_count
            ,   x_msg_data              =>    x_msg_data
        );
Line: 778

            ,   p_delete                =>    TRUE
            ,   x_return_status         =>    x_return_status
            ,   x_msg_count             =>    x_msg_count
            ,   x_msg_data              =>    x_msg_data
        );
Line: 849

  SELECT Attribute1
  FROM   AK_REGIONS
  WHERE  Region_Code = c_region_code;
Line: 919

    this is called from both create and update KPI Analysis options
 *******************************************************************/
PROCEDURE Get_Default_Viewby_For_Measure
(       p_DimObj_ViewBy_Tbl     IN          BSC_BIS_KPI_MEAS_PUB.DimObj_Viewby_Tbl_Type
    ,   x_dim_objects           OUT NOCOPY  VARCHAR
    ,   x_defaults              OUT NOCOPY  VARCHAR
    ,   x_view_bys              OUT NOCOPY  VARCHAR
    ,   x_return_status         OUT NOCOPY  VARCHAR2
    ,   x_msg_count             OUT NOCOPY  NUMBER
    ,   x_msg_data              OUT NOCOPY  VARCHAR2
)IS
    l_default_level_set     BOOLEAN := FALSE;
Line: 1162

    SELECT  DISTINCT dim_group_id
    FROM    BSC_KPI_DIM_GROUPS
    WHERE   Dim_Set_Id   =  p_dim_set_id
    AND     Indicator    =  p_Kpi_Id;
Line: 1168

    SELECT  C.Source                    Source
    FROM    BSC_SYS_DIM_GROUPS_VL       A
         ,  BSC_SYS_DIM_LEVELS_BY_GROUP B
         ,  BSC_SYS_DIM_LEVELS_B        C
    WHERE   A.Dim_Group_ID    =   B.Dim_Group_ID
    AND     C.Dim_Level_ID    =   B.Dim_Level_ID
    AND     A.Short_Name     <>   BSC_BIS_DIMENSION_PUB.Unassigned_Dim
    AND     A.Dim_Group_ID    =   l_dimension_id;
Line: 1195

                SELECT  short_name INTO l_temp_var
                FROM    BSC_SYS_DIM_GROUPS_VL
                WHERE   dim_group_id = l_dimension_id;
Line: 1227

                BSC_BIS_DIMENSION_PUB.Delete_Dimension
                (       p_commit                =>  FND_API.G_FALSE
                    ,   p_dim_short_name        =>  l_temp_var
                    ,   x_return_status         =>  x_return_status
                    ,   x_msg_count             =>  x_msg_count
                    ,   x_msg_data              =>  x_msg_data
                );
Line: 1322

        SELECT    Short_Name
        INTO      x_measure_short_name
        FROM      BSC_SYS_MEASURES
               ,  BSC_SYS_DATASETS_VL
        WHERE     MEASURE_ID  = MEASURE_ID1
        AND       DATASET_ID  = p_data_set_id;
Line: 1407

        SELECT    Short_Name
        INTO      x_measure_short_name
        FROM      BSC_SYS_MEASURES
               ,  BSC_SYS_DATASETS_VL
        WHERE     MEASURE_ID  = MEASURE_ID1
        AND       DATASET_ID  = p_data_set_id;
Line: 1535

    SELECT  indicator
    FROM    BSC_KPIS_B
    WHERE   Source_Indicator =   p_kpi_id
    AND     Prototype_Flag   <>  2;
Line: 1550

    SELECT  COUNT(*) INTO l_count
    FROM    BSC_KPIS_B
    WHERE   indicator = p_kpi_id;
Line: 1554

        FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
Line: 1563

    SELECT  share_flag
    INTO    l_count
    FROM    BSC_KPIS_B
    WHERE  indicator = l_bsc_dimset_rec.Bsc_Kpi_Id ;
Line: 1577

      , p_Entity_Action_Type    => BSC_UTILITY.c_UPDATE
      , x_Return_Status         => x_return_status
      , x_Msg_Count             => x_msg_count
      , x_Msg_Data              => x_msg_data
    );
Line: 1593

    SELECT  NVL(MAX(dim_set_id) + 1, 0)
    INTO    l_bsc_dimset_rec.bsc_dim_set_id
    FROM    BSC_KPI_DIM_SETS_VL
    WHERE   indicator = l_bsc_dimset_rec.bsc_kpi_id;
Line: 1614

    BSC_DIMENSION_SETS_PUB.Create_Bsc_Kpi_Dim_Sets_Tl--INSERT INTO BSC_KPI_DIM_SETS_TL
    (       p_commit                =>  FND_API.G_FALSE
        ,   p_Dim_Set_Rec           =>  l_bsc_dimset_rec
        ,   x_return_status         =>  x_return_status
        ,   x_msg_count             =>  x_msg_count
        ,   x_msg_data              =>  x_msg_data
    );
Line: 1669

        SELECT  NVL(MAX(dim_set_id) + 1, 0)
        INTO    l_bsc_dimset_rec.bsc_dim_set_id
        FROM    BSC_KPI_DIM_SETS_VL
        WHERE   indicator = l_bsc_dimset_rec.bsc_kpi_id;
Line: 1673

        BSC_DIMENSION_SETS_PUB.Create_Bsc_Kpi_Dim_Sets_Tl--INSERT INTO BSC_KPI_DIM_SETS_TL
        (       p_commit                =>  FND_API.G_FALSE
            ,   p_Dim_Set_Rec           =>  l_bsc_dimset_rec
            ,   x_return_status         =>  x_return_status
            ,   x_msg_count             =>  x_msg_count
            ,   x_msg_data              =>  x_msg_data
        );
Line: 1736

    SELECT COUNT(0) INTO l_count_independent_dim_obj
    FROM   BSC_KPI_DIM_LEVELS_B
    WHERE  INDICATOR = p_kpi_id
    AND    DIM_SET_ID = l_bsc_dimset_rec.bsc_dim_set_id;
Line: 1749

            SELECT NAME INTO l_kpi_name
            FROM   BSC_KPIS_VL
            WHERE  INDICATOR = p_Kpi_Id;
Line: 1814

                        UPDATE DIMENSION-SETS
*********************************************************************************/
PROCEDURE Update_Dim_Set
(       p_commit                IN              VARCHAR2   := FND_API.G_TRUE
    ,   p_kpi_id                IN              NUMBER
    ,   p_dim_set_id            IN              NUMBER
    ,   p_display_name          IN              VARCHAR2
    ,   p_assign_dim_names      IN              VARCHAR2
    ,   p_unassign_dim_names    IN              VARCHAR2
    ,   p_time_stamp            IN              VARCHAR2   := NULL  -- Granular Locking
    ,   x_return_status         OUT    NOCOPY   VARCHAR2
    ,   x_msg_count             OUT    NOCOPY   NUMBER
    ,   x_msg_data              OUT    NOCOPY   VARCHAR2
)IS
    l_bsc_dimset_rec            BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
Line: 1847

      , p_Entity_Action_Type    => BSC_UTILITY.c_UPDATE
      , x_Return_Status         => x_return_status
      , x_Msg_Count             => x_msg_count
      , x_Msg_Data              => x_msg_data
    );
Line: 1857

    BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set
    (       p_commit            =>  FND_API.G_FALSE
        ,   p_kpi_id            =>  p_kpi_id
        ,   p_dim_set_id        =>  p_dim_set_id
        ,   p_display_name      =>  p_display_name
        ,   p_time_stamp        =>  p_time_stamp
        ,   x_return_status     =>  x_return_status
        ,   x_msg_count         =>  x_msg_count
        ,   x_msg_data          =>  x_msg_data
    );
Line: 1891

    SELECT COUNT(0) INTO l_count_independent_dim_obj
    FROM   BSC_KPI_DIM_LEVELS_B
    WHERE  INDICATOR = p_kpi_id
    AND    DIM_SET_ID = p_dim_set_id;
Line: 1905

            SELECT NAME INTO l_kpi_name
            FROM   BSC_KPIS_VL
            WHERE  INDICATOR = p_Kpi_Id;
Line: 1951

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
Line: 1953

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
Line: 1959

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
Line: 1961

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
Line: 1964

END Update_Dim_Set;
Line: 1967

                        UPDATE DIMENSION-SETS
*********************************************************************************/
PROCEDURE Update_Dim_Set
(       p_commit                IN              VARCHAR2   := FND_API.G_TRUE
    ,   p_kpi_id                IN              NUMBER
    ,   p_dim_set_id            IN              NUMBER
    ,   p_display_name          IN              VARCHAR2
    ,   p_dim_short_names       IN              VARCHAR2
    ,   p_time_stamp            IN              VARCHAR2   := NULL  -- Granular Locking
    ,   x_return_status         OUT    NOCOPY   VARCHAR2
    ,   x_msg_count             OUT    NOCOPY   NUMBER
    ,   x_msg_data              OUT    NOCOPY   VARCHAR2
)IS
    l_unassign_dim_names        VARCHAR2(32000);
Line: 1985

    SELECT  short_name FROM BSC_SYS_DIM_GROUPS_VL
    WHERE   dim_group_id IN (SELECT  dim_group_id
    FROM    BSC_KPI_DIM_GROUPS
    WHERE   dim_set_id = p_dim_set_id
    AND     indicator  = p_kpi_id);
Line: 2006

    SELECT COUNT(DIM_SET_ID)
    INTO l_count
    FROM BSC_KPI_DIM_SETS_VL
    WHERE DIM_SET_ID = p_dim_set_id AND INDICATOR = p_kpi_id;
Line: 2012

       FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
Line: 2014

       SELECT MEANING
       INTO l_meaning
       FROM BSC_LOOKUPS
       WHERE LOOKUP_TYPE = 'BSC_UI_COMMON' AND LOOKUP_CODE = 'DIM_SET' ;
Line: 2036

      , p_Entity_Action_Type    => BSC_UTILITY.c_UPDATE
      , x_Return_Status         => x_return_status
      , x_Msg_Count             => x_msg_count
      , x_Msg_Data              => x_msg_data
    );
Line: 2046

    BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set
    (       p_commit                =>  FND_API.G_FALSE
        ,   p_kpi_id                =>  p_kpi_id
        ,   p_dim_set_id            =>  p_dim_set_id
        ,   p_display_name          =>  p_display_name
        ,   p_assign_dim_names      =>  p_dim_short_names
        ,   p_unassign_dim_names    =>  l_unassign_dim_names
        ,   p_time_stamp            =>  p_time_stamp
        ,   x_return_status         =>  x_return_status
        ,   x_msg_count             =>  x_msg_count
        ,   x_msg_data              =>  x_msg_data
    );
Line: 2092

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
Line: 2094

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
Line: 2100

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
Line: 2102

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
Line: 2105

END Update_Dim_Set;
Line: 2107

                        UPDATE DIMENSION-SET
*********************************************************************************/
PROCEDURE Update_Dim_Set
(       p_commit                IN              VARCHAR2   := FND_API.G_TRUE
    ,   p_kpi_id                IN              NUMBER
    ,   p_dim_set_id            IN              NUMBER
    ,   p_display_name          IN              VARCHAR2
    ,   p_time_stamp            IN              VARCHAR2   := NULL  -- Granular Locking
    ,   x_return_status         OUT    NOCOPY   VARCHAR2
    ,   x_msg_count             OUT    NOCOPY   NUMBER
    ,   x_msg_data              OUT    NOCOPY   VARCHAR2
)IS

    l_bsc_dimset_rec        BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
Line: 2125

    SELECT  indicator
    FROM    BSC_KPIS_B
    WHERE   Source_Indicator  =  p_kpi_id
    AND     Prototype_Flag   <>  2;
Line: 2130

    SAVEPOINT UpdateBSCDimSetPMD;
Line: 2149

    SELECT  share_flag
    INTO    l_count
    FROM    BSC_KPIS_B
    WHERE  indicator = l_bsc_dimset_rec.Bsc_Kpi_Id ;
Line: 2164

    SELECT COUNT(*) INTO l_count
    FROM   BSC_KPI_DIM_SETS_VL
    WHERE  indicator  = l_bsc_dimset_rec.Bsc_Kpi_Id
    AND    dim_set_id = l_bsc_dimset_rec.Bsc_Dim_Set_Id;
Line: 2192

    BSC_DIMENSION_SETS_PUB.Update_Bsc_Kpi_Dim_Sets_Tl
    (       p_commit            =>  FND_API.G_FALSE
        ,   p_Dim_Set_Rec       =>  l_bsc_dimset_rec
        ,   x_return_status     =>  x_return_status
        ,   x_msg_count         =>  x_msg_count
        ,   x_msg_data          =>  x_msg_data
    );
Line: 2220

        SELECT COUNT(*) INTO l_count FROM BSC_KPI_DIM_SETS_VL
        WHERE indicator  = l_bsc_dimset_rec.Bsc_Kpi_Id
        AND   dim_set_id = l_bsc_dimset_rec.Bsc_Dim_Set_Id;
Line: 2246

        BSC_DIMENSION_SETS_PUB.Update_Bsc_Kpi_Dim_Sets_Tl
        (       p_commit            =>  FND_API.G_FALSE
            ,   p_Dim_Set_Rec       =>  l_bsc_dimset_rec
            ,   x_return_status     =>  x_return_status
            ,   x_msg_count         =>  x_msg_count
            ,   x_msg_data          =>  x_msg_data
        );
Line: 2280

        ROLLBACK TO UpdateBSCDimSetPMD;
Line: 2291

        ROLLBACK TO UpdateBSCDimSetPMD;
Line: 2302

        ROLLBACK TO UpdateBSCDimSetPMD;
Line: 2305

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
Line: 2307

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
Line: 2311

        ROLLBACK TO UpdateBSCDimSetPMD;
Line: 2314

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
Line: 2316

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
Line: 2319

END Update_Dim_Set;
Line: 2339

  SELECT short_name
  FROM bsc_kpis_b
  WHERE indicator = p_Indicator;
Line: 2347

  SELECT  count(1)
  INTO  l_count
  FROM  BSC_KPI_DIM_LEVELS_VL
  WHERE INDICATOR    = p_Indicator
  AND   DIM_SET_ID   = p_DimSetId
  AND   LEVEL_SOURCE = BSC_BIS_MEASURE_PUB.c_BSC;
Line: 2382

                        DELETE DIMENSION-SETS
*********************************************************************************/
PROCEDURE Delete_Dim_Set
(       p_commit                IN              VARCHAR2   := FND_API.G_TRUE
    ,   p_kpi_id                IN              NUMBER
    ,   p_dim_set_id            IN              NUMBER
    ,   x_return_status         OUT    NOCOPY   VARCHAR2
    ,   x_msg_count             OUT    NOCOPY   NUMBER
    ,   x_msg_data              OUT    NOCOPY   VARCHAR2
)IS
    l_bsc_dimset_rec            BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
Line: 2396

    SELECT  indicator
    FROM    BSC_KPIS_B
    WHERE   Source_Indicator  =  p_kpi_id
    AND     Prototype_Flag   <>  2;
Line: 2401

    SAVEPOINT DeleteBSCDimSetPMD;
Line: 2418

        FND_MESSAGE.SET_NAME('BSC', 'BSC_DIM_SET_0_NO_DELETE');
Line: 2425

    SELECT  share_flag
    INTO    l_count
    FROM    BSC_KPIS_B
    WHERE  indicator = l_bsc_dimset_rec.Bsc_Kpi_Id ;
Line: 2435

    SELECT COUNT(*) INTO l_count
    FROM   BSC_KPI_DIM_SETS_VL
    WHERE  indicator = l_bsc_dimset_rec.Bsc_Kpi_Id;
Line: 2466

    BSC_DIMENSION_SETS_PUB.Delete_Dim_Group_In_Dset
    (       p_commit            =>  FND_API.G_FALSE
        ,   p_Dim_Set_Rec       =>  l_bsc_dimset_rec
        ,   x_return_status     =>  x_return_status
        ,   x_msg_count         =>  x_msg_count
        ,   x_msg_data          =>  x_msg_data
    );
Line: 2481

        SELECT COUNT(*) INTO l_count
        FROM   BSC_KPI_DIM_GROUPS
        WHERE  indicator  = l_bsc_dimset_rec.Bsc_Kpi_Id
        AND    dim_set_id = p_dim_set_id;
Line: 2502

            BSC_DIMENSION_SETS_PUB.Delete_Dim_Group_In_Dset
            (       p_commit            =>  FND_API.G_FALSE
                ,   p_Dim_Set_Rec       =>  l_bsc_dimset_rec
                ,   x_return_status     =>  x_return_status
                ,   x_msg_count         =>  x_msg_count
                ,   x_msg_data          =>  x_msg_data
            );
Line: 2523

        ROLLBACK TO DeleteBSCDimSetPMD;
Line: 2534

        ROLLBACK TO DeleteBSCDimSetPMD;
Line: 2545

        ROLLBACK TO DeleteBSCDimSetPMD;
Line: 2548

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Set ';
Line: 2550

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Set ';
Line: 2554

        ROLLBACK TO DeleteBSCDimSetPMD;
Line: 2557

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Set ';
Line: 2559

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Set ';
Line: 2562

END Delete_Dim_Set;
Line: 2588

    SELECT  indicator
    FROM    BSC_KPIS_B
    WHERE   Source_Indicator  =  p_kpi_id
    AND     Prototype_Flag   <>  2;
Line: 2594

    SELECT
      level_table_name
    FROM
      bsc_kpi_dim_levels_b
    WHERE
      indicator = p_kpi_id
      AND dim_set_id = p_dim_set_id
    INTERSECT
    SELECT
      column_value level_table_name
    FROM
      TABLE(CAST(l_level_table_names AS BSC_EDIT_VLIST));
Line: 2623

    SELECT
      level_table_name
     ,default_key_value
    BULK COLLECT INTO
      l_level_table_names,
      l_key_item_values
    FROM
      bsc_kpi_dim_levels_b
    WHERE
      indicator = p_kpi_id
      AND dim_set_id = p_dim_set_id
      AND default_key_value IS NOT NULL;
Line: 2639

    SELECT  share_flag
    INTO    l_count
    FROM    BSC_KPIS_B
    WHERE  indicator = l_bsc_dimset_rec.Bsc_Kpi_Id ;
Line: 2652

        SELECT COUNT(*) INTO l_count FROM BSC_KPI_DIM_SETS_VL
        WHERE indicator  = l_bsc_dimset_rec.Bsc_Kpi_Id
        AND   dim_set_id = l_bsc_dimset_rec.Bsc_Dim_Set_Id;
Line: 2664

        BSC_BIS_KPI_MEAS_PUB.Delete_Default_Kpi_Dim_Object
        (       p_commit                =>  FND_API.G_FALSE
            ,   p_kpi_id                =>  p_kpi_id
            ,   p_dim_set_id            =>  p_dim_set_id
            ,   x_return_status         =>  x_return_status
            ,   x_msg_count             =>  x_msg_count
            ,   x_msg_data              =>  x_msg_data
        );
Line: 2682

            SELECT  COUNT(*)  INTO l_count
            FROM    BSC_SYS_DIM_GROUPS_VL
            WHERE   short_name = l_dim_short_name;
Line: 2692

            SELECT COUNT(*) INTO l_count
            FROM   BSC_KPI_DIM_GROUPS
            WHERE  dim_group_id = (SELECT dim_group_id FROM BSC_SYS_DIM_GROUPS_VL WHERE Short_Name = l_dim_short_name)
            AND    dim_set_id   =  l_bsc_dimset_rec.Bsc_Dim_Set_Id
            AND    indicator    =  l_bsc_dimset_rec.Bsc_Kpi_Id;
Line: 2751

                SELECT COUNT(*) INTO l_count FROM BSC_KPI_DIM_SETS_VL
                WHERE indicator  = l_bsc_dimset_rec.Bsc_Kpi_Id
                AND   dim_set_id = l_bsc_dimset_rec.Bsc_Dim_Set_Id;
Line: 2927

  SELECT
    level_table_name
  FROM
    bsc_kpi_dim_levels_b
  WHERE
    indicator = p_kpi_id
    AND dim_set_id = p_dim_set_id
  INTERSECT
  SELECT
    column_value level_table_name
  FROM
    TABLE(CAST(l_level_table_names AS BSC_EDIT_VLIST));
Line: 2958

    SELECT
      level_table_name
     ,default_key_value
    BULK COLLECT INTO
      l_level_table_names,
      l_key_item_values
    FROM
      bsc_kpi_dim_levels_b
    WHERE
      indicator = p_kpi_id
      AND dim_set_id = p_dim_set_id
      AND default_key_value IS NOT NULL;
Line: 3053

  So when a dimension set is being updated it has to be validate that
  it contains all the common dimension objects.
  The validation is done for shared kpis also internally.
/************************************************************/
    BSC_COMMON_DIM_LEVELS_PUB.Validate_List_Button
    (
          p_Kpi_Id        =>  p_kpi_id
        , p_Dim_Level_Id  =>  NULL
        , x_return_status =>  x_return_status
        , x_msg_count     =>  x_msg_count
        , x_msg_data      =>  x_msg_data
    );
Line: 3135

    SELECT  indicator
    FROM    BSC_KPIS_B
    WHERE   Source_Indicator =   p_kpi_id
    AND     Prototype_Flag   <>  2;
Line: 3141

    SELECT   B.Short_Name
    FROM     BSC_KPI_DIM_GROUPS     A
          ,  BSC_SYS_DIM_GROUPS_VL  B
    WHERE    A.Indicator    =  p_kpi_id
    AND      A.Dim_Set_Id   =  p_dim_set_id
    AND      A.Dim_Group_ID =  B.Dim_Group_ID
    ORDER BY A.Dim_Group_Index;
Line: 3169

    SELECT  share_flag
    INTO    l_count
    FROM    BSC_KPIS_B
    WHERE  indicator = l_bsc_dimset_rec.Bsc_Kpi_Id;
Line: 3181

    SELECT COUNT(*) INTO l_count FROM BSC_KPI_DIM_SETS_VL
    WHERE indicator  = l_bsc_dimset_rec.Bsc_Kpi_Id
    AND   dim_set_id = l_bsc_dimset_rec.Bsc_Dim_Set_Id;
Line: 3215

            SELECT  COUNT(*)  INTO l_count
            FROM    BSC_SYS_DIM_GROUPS_VL  A
                ,   BSC_KPI_DIM_GROUPS     B
            WHERE   A.short_name   = l_dim_short_name
            AND     A.Dim_Group_Id = B.Dim_Group_Id
            AND     B.Indicator    = l_bsc_dimset_rec.Bsc_Kpi_Id
            AND     B.Dim_Set_Id   = p_dim_set_id;
Line: 3223

                SELECT  dim_group_id
                INTO    l_bsc_dimset_rec.Bsc_Dim_Level_Group_Id
                FROM    BSC_SYS_DIM_GROUPS_VL
                WHERE   short_name = l_dim_short_name;
Line: 3229

                BSC_DIMENSION_SETS_PUB.Delete_Dim_Group_In_Dset
                (       p_commit                =>  FND_API.G_FALSE
                    ,   p_Dim_Set_Rec           =>  l_bsc_dimset_rec
                    ,   p_create_Dim_Lev_Grp    =>  FALSE
                    ,   x_return_status         =>  x_return_status
                    ,   x_msg_count             =>  x_msg_count
                    ,   x_msg_data              =>  x_msg_data
                );
Line: 3269

                SELECT  COUNT(*)  INTO l_count
                FROM    BSC_SYS_DIM_GROUPS_VL  A
                    ,   BSC_KPI_DIM_GROUPS     B
                WHERE   A.short_name   = l_dim_short_name
                AND     A.Dim_Group_Id = B.Dim_Group_Id
                AND     B.Indicator    = l_bsc_dimset_rec.Bsc_Kpi_Id
                AND     B.Dim_Set_Id   = p_dim_set_id;
Line: 3277

                    SELECT  dim_group_id
                    INTO    l_bsc_dimset_rec.Bsc_Dim_Level_Group_Id
                    FROM    BSC_SYS_DIM_GROUPS_VL
                    WHERE   short_name = l_dim_short_name;
Line: 3296

                    BSC_DIMENSION_SETS_PUB.Delete_Dim_Group_In_Dset
                    (       p_commit                =>  FND_API.G_FALSE
                        ,   p_Dim_Set_Rec           =>  l_bsc_dimset_rec
                        ,   p_create_Dim_Lev_Grp    =>  FALSE
                        ,   x_return_status         =>  x_return_status
                        ,   x_msg_count             =>  x_msg_count
                        ,   x_msg_data              =>  x_msg_data
                    );
Line: 3427

                      UPDATE DIMENSION LEVEL SELECTIONS
*********************************************************************************/
PROCEDURE Create_Dim_Grp_Lev_In_Dset
(       p_commit                IN              VARCHAR2   := FND_API.G_TRUE
    ,   p_kpi_id                IN              NUMBER
    ,   p_dim_set_id            IN              NUMBER
    ,   p_dim_short_names       IN              VARCHAR2
    ,   x_return_status         OUT    NOCOPY   VARCHAR2
    ,   x_msg_count             OUT    NOCOPY   NUMBER
    ,   x_msg_data              OUT    NOCOPY   VARCHAR2
)IS
    l_bsc_dimset_rec        BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
Line: 3493

            SELECT COUNT(*) INTO l_count
            FROM   BSC_KPI_DIM_GROUPS     A
                ,  BSC_SYS_DIM_GROUPS_VL  B
            WHERE  A.Dim_Group_Id  = B.Dim_Group_Id
            AND    A.Indicator     = l_bsc_dimset_rec.bsc_kpi_id
            AND    A.Dim_Set_Id    = l_bsc_dimset_rec.Bsc_Dim_Set_Id
            AND    B.Short_Name    = l_dim_short_name;
Line: 3504

                SELECT  NVL(MAX(Dim_Group_Index) + 1, 0)
                INTO    l_bsc_dimset_rec.Bsc_Dim_Level_Group_Index
                FROM    BSC_KPI_DIM_GROUPS
                WHERE   indicator  = l_bsc_dimset_rec.bsc_kpi_id
                AND     dim_set_id = l_bsc_dimset_rec.bsc_dim_set_id;
Line: 3510

                SELECT  dim_group_id
                INTO    l_bsc_dimset_rec.Bsc_Dim_Level_Group_Id
                FROM    BSC_SYS_DIM_GROUPS_VL
                WHERE   short_name = l_dim_short_name;
Line: 3528

                BSC_DIMENSION_SETS_PUB.Create_Dim_Group_In_Dset--insert into BSC_KPI_DIM_GROUPS
                (       p_commit                =>  FND_API.G_FALSE
                    ,   p_Dim_Set_Rec           =>  l_bsc_dimset_rec
                    ,   p_create_Dim_Lev_Grp    =>  FALSE
                    ,   x_return_status         =>  x_return_status
                    ,   x_msg_count             =>  x_msg_count
                    ,   x_msg_data              =>  x_msg_data
                );
Line: 3540

                BSC_DIMENSION_SETS_PUB.Create_Dim_Level_Properties--insert into BSC_KPI_DIM_LEVEL_PROPERTIES
                (       p_commit            =>  FND_API.G_FALSE
                    ,   p_Dim_Set_Rec       =>  l_bsc_dimset_rec
                    ,   x_return_status     =>  x_return_status
                    ,   x_msg_count         =>  x_msg_count
                    ,   x_msg_data          =>  x_msg_data
                );
Line: 3566

            SELECT  COUNT(*)
            INTO    l_count
            FROM    BSC_KPI_DIM_LEVELS_B
            WHERE   INDICATOR   = l_bsc_dimset_rec.Bsc_Kpi_Id
            AND     DIM_SET_ID  = l_bsc_dimset_rec.Bsc_Dim_Set_Id;
Line: 3662

    SELECT  indicator
    FROM    BSC_KPIS_B
    WHERE   Source_Indicator =   p_kpi_id
    AND     Prototype_Flag   <>  2;
Line: 3705

    SELECT  COUNT(Indicator) INTO l_Count
    FROM    BSC_KPI_ANALYSIS_OPTIONS_B
    WHERE   Dim_Set_Id        = l_bsc_dimset_rec.Bsc_Dim_Set_Id
    AND     Indicator         = l_bsc_dimset_rec.Bsc_Kpi_Id
    AND     Analysis_Group_Id = l_bsc_dimset_rec.Bsc_Analysis_Id
    AND     Option_Id         = l_bsc_dimset_rec.Bsc_Option_Id;
Line: 3713

        SELECT COUNT(*) INTO l_count FROM BSC_KPI_DIM_SETS_VL
        WHERE indicator  = l_bsc_dimset_rec.Bsc_Kpi_Id
        AND   dim_set_id = l_bsc_dimset_rec.Bsc_Dim_Set_Id;
Line: 3723

        BSC_DIMENSION_SETS_PUB.Update_Kpi_Analysis_Options_B
        (       p_commit            =>  FND_API.G_FALSE
            ,   p_Dim_Set_Rec       =>  l_bsc_dimset_rec
            ,   x_return_status     =>  x_return_status
            ,   x_msg_count         =>  x_msg_count
            ,   x_msg_data          =>  x_msg_data
        );
Line: 3735

        BSC_DESIGNER_PVT.Deflt_Update_AOPTS(l_bsc_dimset_rec.Bsc_Kpi_Id);
Line: 3741

            SELECT COUNT(*) INTO l_count FROM BSC_KPI_DIM_SETS_VL
            WHERE indicator  = l_bsc_dimset_rec.Bsc_Kpi_Id
            AND   dim_set_id = l_bsc_dimset_rec.Bsc_Dim_Set_Id;
Line: 3751

            BSC_DIMENSION_SETS_PUB.Update_Kpi_Analysis_Options_B
            (       p_commit            =>  FND_API.G_FALSE
                ,   p_Dim_Set_Rec       =>  l_bsc_dimset_rec
                ,   x_return_status     =>  x_return_status
                ,   x_msg_count         =>  x_msg_count
                ,   x_msg_data          =>  x_msg_data
            );
Line: 3848

    SELECT   LEVEL_SOURCE
    FROM     BSC_KPI_DIM_LEVELS_VL
    WHERE    INDICATOR    = p_Indicator
    AND      DIM_SET_ID   = p_DimSetId
    AND      LEVEL_SOURCE IS NOT NULL;
Line: 3855

    SELECT   D.SOURCE
           , D.DataSet_ID
    FROM     BSC_SYS_DATASETS_VL        D
           , BSC_DB_DATASET_DIM_SETS_V  B
    WHERE    D.DATASET_ID = B.DATASET_ID
    AND      B.INDICATOR  = p_Indicator
    AND      B.DIM_SET_ID = p_DimSetId;
Line: 3926

    SELECT  MAX( ANALYSIS_GROUP_ID)
    INTO    h_ag_count
    FROM    BSC_KPI_ANALYSIS_GROUPS
    WHERE   INDICATOR   =   p_indicator;
Line: 3931

        SELECT  NAME INTO l_anal_name
        FROM    BSC_KPI_ANALYSIS_OPTIONS_VL
        WHERE   ANALYSIS_GROUP_ID =0
        AND     OPTION_ID = p_a0
        AND     INDICATOR = p_indicator;
Line: 3937

        SELECT  DEPENDENCY_FLAG INTO h_ag_depend
        FROM    BSC_KPI_ANALYSIS_GROUPS
        WHERE   ANALYSIS_GROUP_ID =1
        AND     INDICATOR   =   p_indicator;
Line: 3942

            SELECT  NAME INTO l_anal_name
            FROM    BSC_KPI_ANALYSIS_OPTIONS_VL
            WHERE   ANALYSIS_GROUP_ID = 1
            AND     OPTION_ID   =   p_a1
            AND     INDICATOR   =   p_indicator;
Line: 3949

                SELECT  NAME INTO l_anal_name
                FROM    BSC_KPI_ANALYSIS_OPTIONS_VL
                WHERE   ANALYSIS_GROUP_ID =1
                AND     OPTION_ID         = p_a1
                AND     PARENT_OPTION_ID  = p_a0
                AND     INDICATOR         = p_indicator;
Line: 3961

        SELECT  DEPENDENCY_FLAG
        INTO    h_ag1_depend
        FROM    BSC_KPI_ANALYSIS_GROUPS
        WHERE   ANALYSIS_GROUP_ID =1
        AND     INDICATOR   =   p_indicator;
Line: 3967

        SELECT  DEPENDENCY_FLAG
        INTO    h_ag2_depend
        FROM    BSC_KPI_ANALYSIS_GROUPS
        WHERE   ANALYSIS_GROUP_ID = 2
        AND     INDICATOR   =   p_indicator;
Line: 3973

            SELECT  NAME
            INTO    l_anal_name
            FROM    BSC_KPI_ANALYSIS_OPTIONS_VL
            WHERE   ANALYSIS_GROUP_ID =2
            AND     OPTION_ID=p_a2
            AND     INDICATOR=p_indicator;
Line: 3982

                    SELECT  NAME
                    INTO    l_anal_name
                    FROM    BSC_KPI_ANALYSIS_OPTIONS_VL
                    WHERE   ANALYSIS_GROUP_ID   =   2
                    AND     OPTION_ID           =   p_a2
                    AND     PARENT_OPTION_ID    =   p_a1
                    AND     INDICATOR           =   p_indicator;
Line: 3995

                SELECT  NAME
                INTO    l_anal_name
                FROM    BSC_KPI_ANALYSIS_OPTIONS_VL
                WHERE   ANALYSIS_GROUP_ID     = 2
                AND     OPTION_ID             = p_a2
                AND     PARENT_OPTION_ID      = p_a1
                AND     GRANDPARENT_OPTION_ID = p_a0
                AND     INDICATOR             = p_indicator;
Line: 4027

    SELECT COUNT(SERIES_ID)
    FROM   BSC_KPI_ANALYSIS_MEASURES_VL
    WHERE  INDICATOR     = p_indicator
    AND    ANALYSIS_OPTION0 = p_a0
    AND    ANALYSIS_OPTION1 = p_a1
    AND    ANALYSIS_OPTION2 = p_a2;
Line: 4049

         API TO UPDATE PMF/BSC MEASURES/ANALYSIS OPTIONS WITHIN AN INDICATOR
*********************************************************************************/
PROCEDURE Update_KPI_Analysis_Options
(       p_commit                IN          VARCHAR2   := FND_API.G_TRUE
    ,   p_kpi_id                IN          NUMBER
    ,   p_data_source           IN          VARCHAR2  --this parameter is not needed and can be removed from the API
    ,   p_analysis_group_id     IN          NUMBER
    ,   p_analysis_option_id0   IN          NUMBER
    ,   p_analysis_option_id1   IN          NUMBER
    ,   p_analysis_option_id2   IN          NUMBER
    ,   p_series_id             IN          NUMBER
    ,   p_data_set_id           IN          NUMBER
    ,   p_dim_set_id            IN          NUMBER
    ,   p_option0_Name          IN          VARCHAR2
    ,   p_option1_Name          IN          VARCHAR2
    ,   p_option2_Name          IN          VARCHAR2
    ,   p_measure_short_name    IN          VARCHAR2
    ,   p_dim_obj_short_names   IN          VARCHAR2  --comma seperated dimension objects needed for PMF Measures
    ,   p_default_short_names   IN          VARCHAR2  :=  NULL
    ,   p_view_by_name          IN          VARCHAR2  :=  NULL
    ,   p_measure_name          IN          VARCHAR2  --BSC_KPI_ANALYSIS_MEASURES_VL.name
    ,   p_measure_help          IN          VARCHAR2  --BSC_KPI_ANALYSIS_MEASURES_VL.help
    ,   p_default_value         IN          NUMBER
    ,   p_time_stamp            IN          VARCHAR2  := NULL
    ,   p_update_ana_opt        IN          BOOLEAN := FALSE
    ,   x_return_status         OUT NOCOPY  VARCHAR2
    ,   x_msg_count             OUT NOCOPY  NUMBER
    ,   x_msg_data              OUT NOCOPY  VARCHAR2
) IS
    l_Bsc_Anal_Opt_Rec      BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
Line: 4108

    SELECT  dataset_id
    FROM    BSC_KPI_ANALYSIS_MEASURES_B
    WHERE   indicator        = p_kpi_id
    AND     analysis_option0 = NVL(p_analysis_option_id0, 0)
    AND     analysis_option1 = NVL(p_analysis_option_id1, 0)
    AND     analysis_option2 = NVL(p_analysis_option_id2, 0)
    AND     series_id        = NVL(p_series_id, 0);
Line: 4119

    SELECT  DISTINCT a.dataset_id DataSet_Id
    FROM    BSC_SYS_DATASETS_B a
          , BSC_SYS_MEASURES   b
    WHERE   UPPER(b.short_name) = UPPER(p_measure_short_name)
    AND     a.measure_id1       = b.measure_id
    AND     ROWNUM < 2;
Line: 4127

    SELECT  indicator
    FROM    BSC_KPIS_B
    WHERE   Source_Indicator  =  p_kpi_id
    AND     Prototype_Flag   <>  2;
Line: 4133

    SELECT  D.SOURCE,D.DATASET_ID
    FROM    BSC_SYS_DATASETS_B D,
            BSC_KPI_ANALYSIS_MEASURES_B K
    WHERE   D.DATASET_ID = K.DATASET_ID
    AND     k.indicator      = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
    AND     analysis_option0 = 0
    AND     analysis_option1 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group1
    AND     analysis_option2 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group2
    AND     series_id        = 0;
Line: 4144

    SELECT
      stack_series_id
    FROM
      bsc_kpi_analysis_measures_b
    WHERE
      indicator  = p_kpi_id AND
      analysis_option0 = p_analysis_option_id0 AND
      analysis_option1 = p_analysis_option_id1 AND
      analysis_option2 = p_analysis_option_id2 AND
      series_id        = p_series_id;
Line: 4156

    SELECT
      default_calculation
    FROM
      bsc_kpi_measure_props kp,
      bsc_kpi_analysis_measures_b km
    WHERE
      km.indicator = p_kpi_id AND
      km.indicator = kp.indicator AND
      km.kpi_measure_id = kp.kpi_measure_id AND
      km.analysis_option0 = p_analysis_option_id0 AND
      km.analysis_option1 = p_analysis_option_id1 AND
      km.analysis_option2 = p_analysis_option_id2 AND
      km.series_id        = p_series_id;
Line: 4174

    SAVEPOINT UpdateBSCKPIAnaOpts;
Line: 4223

    SELECT  COUNT(*) INTO l_count
    FROM    BSC_KPIS_B
    WHERE   indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
Line: 4227

        FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
Line: 4233

    SELECT  share_flag  INTO  l_count
    FROM    BSC_KPIS_B
    WHERE   indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id ;
Line: 4243

    SELECT  Source INTO l_data_source
    FROM    BSC_SYS_DATASETS_B
    WHERE   Dataset_Id = l_Bsc_Anal_Opt_Rec.Bsc_Dataset_Id;
Line: 4246

    IF (NOT p_update_ana_opt AND l_Bsc_Anal_Opt_Rec.Bsc_Dim_Set_Id IS NOT NULL) THEN
        BSC_BIS_KPI_MEAS_PUB.get_Dim_Set_Source_Info
        (     p_Indicator   =>  l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
            , p_DimSetId    =>  l_Bsc_Anal_Opt_Rec.Bsc_Dim_Set_Id
            , x_Source      =>  l_temp_data_source
            , x_Data_Set_Id =>  l_data_set_id
        );
Line: 4259

            FND_MESSAGE.SET_NAME('BSC','BSC_NO_UPDATE_MEASURE_SOURCE');
Line: 4380

                SELECT COUNT(1) INTO l_Count
                FROM   BSC_SYS_DIM_LEVELS_B
                WHERE  SHORT_NAME = l_dim_obj_name;
Line: 4416

            SELECT NVL(MAX(dim_group_id) + 1, 0) INTO l_count
            FROM   BSC_SYS_DIM_GROUPS_VL;
Line: 4424

                SELECT  COUNT(*) INTO l_count
                FROM    BSC_SYS_DIM_GROUPS_VL
                WHERE   UPPER(short_name) = l_temp_var
                OR      UPPER(name)       = l_temp_var;
Line: 4463

            SELECT  NVL(MAX(dim_set_id) + 1, 0)
            INTO    l_Bsc_Anal_Opt_Rec.Bsc_Dim_Set_Id
            FROM    BSC_KPI_DIM_SETS_VL
            WHERE   indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
Line: 4482

            SELECT  COUNT(*) INTO l_count
            FROM    BSC_KPI_DIM_SETS_VL
            WHERE   indicator  = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
            AND     dim_set_id = l_Bsc_Anal_Opt_Rec.Bsc_Dim_Set_Id;
Line: 4521

                    SELECT level_table_name
                    INTO   l_bsc_dimset_rec.Bsc_Level_Name
                    FROM   BSC_SYS_DIM_LEVELS_B
                    WHERE  Short_Name = l_dim_obj_name;
Line: 4525

                    BSC_DIMENSION_SETS_PUB.Update_Dim_Levels
                    (       p_commit            =>  FND_API.G_FALSE
                        ,   p_Dim_Set_Rec       =>  l_bsc_dimset_rec
                        ,   x_return_status     =>  x_return_status
                        ,   x_msg_count         =>  x_msg_count
                        ,   x_msg_data          =>  x_msg_data
                    );
Line: 4539

                        BSC_DIMENSION_SETS_PUB.Update_Dim_Levels
                        (       p_commit            =>  FND_API.G_FALSE
                            ,   p_Dim_Set_Rec       =>  l_bsc_dimset_rec
                            ,   x_return_status     =>  x_return_status
                            ,   x_msg_count         =>  x_msg_count
                            ,   x_msg_data          =>  x_msg_data
                        );
Line: 4559

                SELECT level_table_name
                INTO   l_bsc_dimset_rec.Bsc_Level_Name
                FROM   BSC_SYS_DIM_LEVELS_B
                WHERE  Short_Name = l_view_by_name;
Line: 4563

                BSC_DIMENSION_SETS_PUB.Update_Dim_Levels
                (       p_commit            =>  FND_API.G_FALSE
                    ,   p_Dim_Set_Rec       =>  l_bsc_dimset_rec
                    ,   x_return_status     =>  x_return_status
                    ,   x_msg_count         =>  x_msg_count
                    ,   x_msg_data          =>  x_msg_data
                );
Line: 4577

                    BSC_DIMENSION_SETS_PUB.Update_Dim_Levels
                    (       p_commit            =>  FND_API.G_FALSE
                        ,   p_Dim_Set_Rec       =>  l_bsc_dimset_rec
                        ,   x_return_status     =>  x_return_status
                        ,   x_msg_count         =>  x_msg_count
                        ,   x_msg_data          =>  x_msg_data
                    );
Line: 4593

        SELECT  indicator_type,config_type
        INTO    l_indic_type, l_config_type
        FROM    bsc_kpis_b
        WHERE   indicator  =  l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
Line: 4604

                    BSC_ANALYSIS_OPTION_PUB.delete_extra_series(
                      p_Bsc_Anal_Opt_Rec    => l_Bsc_Anal_Opt_Rec
                    , x_return_status       => x_return_status
                    , x_msg_count           => x_msg_count
                    , x_msg_data            => x_msg_data
                    );
Line: 4617

        SELECT  COUNT(1)   INTO    l_namecount
        FROM    BSC_KPI_ANALYSIS_OPTIONS_VL
        WHERE   indicator           = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
Line: 4621

        SELECT  COUNT(1)   INTO    l_index
        FROM    BSC_KPI_ANALYSIS_MEASURES_B
        WHERE   indicator        = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
        AND     analysis_option0 = 0
        AND     analysis_option1 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group1
        AND     analysis_option2 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group2
        AND     series_id        = 0
        AND     dataset_id       = -1;
Line: 4631

        IF (p_update_ana_opt OR ((l_dim_set_id IS NOT NULL) OR
             ((l_dim_set_id IS NULL) AND
                ((l_index = 1) AND (p_analysis_option_id0 = 0 OR l_namecount = 1))))) THEN
            --dbms_output.PUT_LINE('IN IF');
Line: 4659

            BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options
            (       p_commit         =>  FND_API.G_FALSE
                ,   p_Anal_Opt_Rec   =>  l_Bsc_Anal_Opt_Rec
                ,   x_return_status  =>  x_return_status
                ,   x_msg_count      =>  x_msg_count
                ,   x_msg_data       =>  x_msg_data
            );
Line: 4773

            BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options
            (       p_commit           =>    FND_API.G_FALSE
                ,   p_Anal_Opt_Rec     =>    l_Bsc_Anal_Opt_Rec
                ,   p_data_Source      =>    l_data_source
                ,   x_return_status    =>    x_return_status
                ,   x_msg_count        =>    x_msg_count
                ,   x_msg_data         =>    x_msg_data
            );
Line: 4807

                BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options
                (      p_commit           =>    FND_API.G_FALSE
                    ,   p_Anal_Opt_Rec     =>    l_Bsc_Anal_Opt_Rec
                    ,   p_data_Source      =>    l_data_source
                    ,   x_return_status    =>    x_return_status
                    ,   x_msg_count        =>    x_msg_count
                    ,   x_msg_data         =>    x_msg_data
                );
Line: 4841

                BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options
                (       p_commit           =>    FND_API.G_FALSE
                    ,   p_Anal_Opt_Rec     =>    l_Bsc_Anal_Opt_Rec
                    ,   p_data_Source      =>    l_data_source
                    ,   x_return_status    =>    x_return_status
                    ,   x_msg_count        =>    x_msg_count
                    ,   x_msg_data         =>    x_msg_data
                );
Line: 4857

            SELECT  COUNT(*) INTO l_count FROM BSC_KPI_ANALYSIS_MEASURES_B
            WHERE   indicator        = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
            AND     analysis_option0 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group0
            AND     analysis_option1 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group1
            AND     analysis_option2 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group2;
Line: 4863

                UPDATE BSC_KPI_ANALYSIS_MEASURES_B
                SET    default_value     = 1
                WHERE  indicator         = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
                AND    analysis_option0  = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group0
                AND    analysis_option1  = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group1
                AND    analysis_option2  = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group2
                AND    series_id         = p_default_value;
Line: 4871

                UPDATE  BSC_KPI_ANALYSIS_MEASURES_B
                SET     default_value    = 0
                WHERE   indicator        = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
                AND     analysis_option0 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group0
                AND     analysis_option1 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group1
                AND     analysis_option2 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group2
                AND     series_id       <> p_default_value;
Line: 4925

        ROLLBACK TO UpdateBSCKPIAnaOpts;
Line: 4939

        ROLLBACK TO UpdateBSCKPIAnaOpts;
Line: 4953

        ROLLBACK TO UpdateBSCKPIAnaOpts;
Line: 4956

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_KPI_Analysis_Options ';
Line: 4958

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_KPI_Analysis_Options ';
Line: 4965

        ROLLBACK TO UpdateBSCKPIAnaOpts;
Line: 4968

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_KPI_Analysis_Options ';
Line: 4970

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_KPI_Analysis_Options ';
Line: 4973

END Update_KPI_Analysis_Options;
Line: 5002

    SELECT  Source
    FROM    BSC_SYS_DATASETS_B
    WHERE   Dataset_Id = p_data_set_id;
Line: 5007

    SELECT  indicator
    FROM    BSC_KPIS_B
    WHERE   Source_Indicator =   p_kpi_id
    AND     Prototype_Flag   <>  2;
Line: 5025

        FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
Line: 5026

        SELECT MEANING INTO l_meaning
        FROM   BSC_LOOKUPS
        WHERE  LOOKUP_TYPE = 'BSC_UI_COMMON' AND LOOKUP_CODE = 'EDW_MEASURE' ;
Line: 5071

    SELECT  COUNT(*) INTO l_count
    FROM    BSC_KPIS_B
    WHERE   indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
Line: 5075

        FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
Line: 5081

    SELECT  share_flag  INTO  l_count
    FROM    BSC_KPIS_B
    WHERE   indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id ;
Line: 5114

        BSC_BIS_KPI_MEAS_PUB.Update_KPI_Analysis_Options
        (       p_commit                =>  FND_API.G_FALSE
            ,   p_kpi_id                =>  l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
            ,   p_data_source           =>  l_datasource
            ,   p_analysis_group_id     =>  l_Bsc_Anal_Opt_Rec.Bsc_Analysis_Group_Id
            ,   p_analysis_option_id0   =>  NULL
            ,   p_analysis_option_id1   =>  NULL
            ,   p_analysis_option_id2   =>  NULL
            ,   p_series_id             =>  NULL
            ,   p_data_set_id           =>  l_Bsc_Anal_Opt_Rec.Bsc_Dataset_Id
            ,   p_dim_set_id            =>  NULL
            ,   p_option0_Name          =>  l_Bsc_Anal_Opt_Rec.Bsc_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          =>  l_Bsc_Anal_Opt_Rec.Bsc_Measure_Long_Name
            ,   p_measure_help          =>  l_Bsc_Anal_Opt_Rec.Bsc_Measure_Help
            ,   p_default_value         =>  NULL
            ,   p_time_stamp            =>  NULL
            ,   x_return_status         =>  x_return_status
            ,   x_msg_count             =>  x_msg_count
            ,   x_msg_data              =>  x_msg_data
        );
Line: 5154

        If all the above conditons are met then only update the option 0.
        other wise create a new analysis option.
        *********************************************************************************/
        SELECT  COUNT(0)
        INTO    l_namecount
        FROM    BSC_KPI_ANALYSIS_OPTIONS_VL
        WHERE   indicator           = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
Line: 5162

        SELECT  COUNT(*)
        INTO    l_index
        FROM    BSC_KPI_ANALYSIS_MEASURES_B
        WHERE   indicator        = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
        AND     analysis_option0 = 0
        AND     analysis_option1 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group1
        AND     analysis_option2 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group2
        AND     series_id        = 0
        AND     dataset_id = -1;
Line: 5172

            SELECT  Option_Id
            INTO    l_Bsc_Anal_Opt_Rec.Bsc_Option_Group0
            FROM    BSC_KPI_ANALYSIS_OPTIONS_B
            WHERE   Indicator           = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
            AND     Analysis_Group_Id   = l_Bsc_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
Line: 5196

            BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options
            (       p_commit         =>  FND_API.G_FALSE
                ,   p_Anal_Opt_Rec   =>  l_Bsc_Anal_Opt_Rec
                ,   x_return_status  =>  x_return_status
                ,   x_msg_count      =>  x_msg_count
                ,   x_msg_data       =>  x_msg_data
            );
Line: 5213

            SELECT  COUNT(Option_Id) + 1
            INTO    l_Bsc_Anal_Opt_Rec.Bsc_Option_Group0
            FROM    BSC_KPI_ANALYSIS_OPTIONS_B
            WHERE   Indicator           = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
            AND     Analysis_Group_Id   = l_Bsc_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
Line: 5255

    SELECT short_name
    INTO l_sname
    FROM BSC_KPIS_B
    WHERE INDICATOR =  p_kpi_id ;
Line: 5330

         API TO DELETE PMF/BSC MEASURES/ANALYSIS OPTIONS WITHIN AN INDICATOR
*********************************************************************************/
/*PROCEDURE Delete_KPI_Analysis_Options
(       p_commit                IN          VARCHAR2   := FND_API.G_TRUE
    ,   p_kpi_id                IN          NUMBER
    ,   p_data_source           IN          VARCHAR2
    ,   p_option_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_count                 NUMBER;
Line: 5354

    SAVEPOINT DeleteBSCKPIAnaOpts;
Line: 5396

    SELECT  COUNT(*) INTO l_count
    FROM    BSC_KPIS_B
    WHERE   indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
Line: 5400

        FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
Line: 5418

    SELECT  share_flag  INTO    l_count
    FROM    BSC_KPIS_B
    WHERE   indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id ;
Line: 5428

    SELECT  COUNT(indicator) INTO l_count
    FROM    BSC_TAB_INDICATORS
    WHERE   indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
Line: 5433

        SELECT  tab_id INTO l_tab_id
        FROM    BSC_TAB_INDICATORS
        WHERE   indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
Line: 5438

    SELECT  dim_set_id INTO l_dim_set_id
    FROM    BSC_KPI_ANALYSIS_OPTIONS_B
    WHERE   indicator         = p_kpi_id
    AND     analysis_group_id = 0
    AND     option_id         = p_option_id;
Line: 5458

    SELECT COUNT (DISTINCT dataset_id) INTO l_count
    FROM BSC_KPI_ANALYSIS_MEASURES_B
    WHERE INDICATOR = p_kpi_id;
Line: 5463

    BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Options
    (       p_commit              =>    FND_API.G_FALSE
        ,   p_Anal_Opt_Rec        =>    l_Bsc_Anal_Opt_Rec
        ,   x_return_status       =>    x_return_status
        ,   x_msg_count           =>    x_msg_count
        ,   x_msg_data            =>    x_msg_data
    );
Line: 5475

    SELECT COUNT (DISTINCT dataset_id) INTO l_new_count
    FROM BSC_KPI_ANALYSIS_MEASURES_B
    WHERE INDICATOR=p_kpi_id;
Line: 5483

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

        SELECT COUNT(option_id) INTO l_count
        FROM   BSC_KPI_ANALYSIS_OPTIONS_B
        WHERE  indicator    = p_kpi_id
        AND    dim_set_id   = l_dim_set_id;
Line: 5502

            BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Set
            (       p_commit         =>  FND_API.G_FALSE
                ,   p_kpi_id         =>  p_kpi_id
                ,   p_dim_set_id     =>  l_dim_set_id
                ,   x_return_status  =>  x_return_status
                ,   x_msg_count      =>  x_msg_count
                ,   x_msg_data       =>  x_msg_data
            );
Line: 5536

        ROLLBACK TO DeleteBSCKPIAnaOpts;
Line: 5547

        ROLLBACK TO DeleteBSCKPIAnaOpts;
Line: 5558

        ROLLBACK TO DeleteBSCKPIAnaOpts;
Line: 5561

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Analysis_Options ';
Line: 5563

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Analysis_Options ';
Line: 5567

        ROLLBACK TO DeleteBSCKPIAnaOpts;
Line: 5570

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Analysis_Options ';
Line: 5572

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Analysis_Options ';
Line: 5575

END Delete_KPI_Analysis_Options;   */
Line: 5577

                            DELETE_KPI_ANALYSIS_OPTIONS
************************************************************************************/
PROCEDURE Delete_KPI_Analysis_Options
(       p_Bsc_Anal_Opt_Rec      IN          BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
    ,   p_data_source           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: 5593

      SELECT
        dim_group_id
      FROM
        BSC_KPI_DIM_GROUPS
      WHERE
        Dim_Set_Id   =  p_Bsc_Anal_Opt_Rec.Bsc_Dim_Set_Id AND
        Indicator    =  p_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
Line: 5602

      SELECT
        short_name
      FROM
        BSC_SYS_DIM_GROUPS_VL
      WHERE
        dim_group_id = p_dim_group_id;
Line: 5614

    SAVEPOINT DeleteBSCKPIAnaOpts;
Line: 5619

    BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Options
    (       p_commit              =>    FND_API.G_FALSE
        ,   p_Anal_Opt_Rec        =>    p_Bsc_Anal_Opt_Rec
        ,   x_return_status       =>    x_return_status
        ,   x_msg_count           =>    x_msg_count
        ,   x_msg_data            =>    x_msg_data
    );
Line: 5632

        SELECT COUNT(option_id) INTO l_count
        FROM   BSC_KPI_ANALYSIS_OPTIONS_B
        WHERE  indicator    = p_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
        AND    dim_set_id   = p_Bsc_Anal_Opt_Rec.Bsc_Dim_Set_Id;
Line: 5645

            BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Set
            (       p_commit         =>  FND_API.G_FALSE
                ,   p_kpi_id         =>  p_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
                ,   p_dim_set_id     =>  p_Bsc_Anal_Opt_Rec.Bsc_Dim_Set_Id
                ,   x_return_status  =>  x_return_status
                ,   x_msg_count      =>  x_msg_count
                ,   x_msg_data       =>  x_msg_data
            );
Line: 5659

            SELECT  COUNT(indicator) INTO l_count
            FROM    BSC_TAB_INDICATORS
            WHERE   indicator = p_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
Line: 5664

                SELECT  tab_id INTO l_tab_id
                FROM    BSC_TAB_INDICATORS
                WHERE   indicator = p_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
Line: 5689

             BSC_BIS_DIMENSION_PUB.Delete_Dimension
              (    p_commit                =>  FND_API.G_FALSE
               ,   p_dim_short_name        =>  l_dim_shortName
               ,   x_return_status         =>  x_return_status
               ,   x_msg_count             =>  x_msg_count
               ,   x_msg_data              =>  x_msg_data
              );
Line: 5706

        ROLLBACK TO DeleteBSCKPIAnaOpts;
Line: 5717

        ROLLBACK TO DeleteBSCKPIAnaOpts;
Line: 5728

        ROLLBACK TO DeleteBSCKPIAnaOpts;
Line: 5731

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Analysis_Options ';
Line: 5733

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Analysis_Options ';
Line: 5737

        ROLLBACK TO DeleteBSCKPIAnaOpts;
Line: 5740

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Analysis_Options ';
Line: 5742

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Analysis_Options ';
Line: 5745

END Delete_KPI_Analysis_Options;
Line: 5747

                            UPDATE KPIS
************************************************************************************/
PROCEDURE Update_Kpi
(       p_commit                IN          VARCHAR2   := FND_API.G_TRUE
    ,   p_kpi_id                IN          NUMBER
    ,   p_kpi_name              IN          VARCHAR2
    ,   p_kpi_help              IN          VARCHAR2   := NULL
    ,   p_responsibility_id     IN          NUMBER     := NULL
    ,   p_default_value         IN          NUMBER
    ,   p_BM_Property_Value     IN          NUMBER     := BSC_KPI_PUB.Benchmark_Kpi_Line_Graph -- 0 For Lines and 1 for Bars
    ,   p_time_stamp            IN          VARCHAR2   := NULL
    ,   p_Anal_opt0             IN          BSC_KPI_ANALYSIS_MEASURES_B.analysis_option0%TYPE
    ,   p_Anal_opt1             IN          BSC_KPI_ANALYSIS_MEASURES_B.analysis_option1%TYPE
    ,   p_Anal_opt2             IN          BSC_KPI_ANALYSIS_MEASURES_B.analysis_option2%TYPE
    ,   p_Anal_Series           IN          BSC_KPI_ANALYSIS_MEASURES_B.series_id%TYPE
    ,   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: 5782

    SELECT  indicator
    FROM    BSC_KPIS_B
    WHERE   Source_Indicator =   p_kpi_id
    AND     Prototype_Flag   <>  2;
Line: 5788

    SELECT  analysis_option0
          , analysis_option1
          , analysis_option2
          , series_id
    FROM    bsc_oaf_analysys_opt_comb_v
    WHERE   indicator    =  p_kpi_id
    AND     default_flag =  1;
Line: 5799

    SAVEPOINT UpdatePMDBSCKPIs;
Line: 5808

    SELECT  COUNT(*) INTO l_count
    FROM    BSC_KPIS_B
    WHERE   indicator = p_kpi_id;
Line: 5812

        FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
Line: 5818

    SELECT  share_flag
    INTO    l_count
    FROM    bsc_kpis_b
    WHERE   indicator = p_kpi_id ;
Line: 5881

    SELECT  COUNT(indicator)   INTO   l_count
    FROM    BSC_TAB_INDICATORS A
    WHERE   A.Indicator <>  p_kpi_id
    AND     A.Tab_Id     = (SELECT Tab_Id FROM BSC_TAB_INDICATORS WHERE Indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id)
    AND     A.Indicator IN (SELECT Indicator FROM BSC_KPIS_TL WHERE Name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Name);
Line: 5887

        SELECT v.name
        INTO   l_tab_name
        FROM   bsc_tabs_vl v
              ,bsc_tab_indicators w
        WHERE  v.tab_id =w.tab_id
        AND    w.indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
Line: 5902

    BSC_KPI_PUB.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: 5920

            SELECT kpi_measure_id
            INTO l_kpi_measure_id
            FROM bsc_kpi_analysis_measures_vl
	    WHERE analysis_option0 = p_Anal_opt0
	      AND analysis_option1 = p_Anal_opt1
	      AND analysis_option2 = p_Anal_opt2
	      AND series_id = p_Anal_Series
              AND indicator = p_kpi_id;
Line: 5929

	    SELECT bk.color_rollup_type, km.kpi_measure_id, bk.prototype_flag
	    INTO   l_color_rollup_type, l_def_kpi_measure_id, l_obj_prototype_flag
	    FROM bsc_db_color_ao_defaults_v dd, bsc_kpi_analysis_measures_vl km, bsc_kpis_b bk
	    WHERE km.indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id AND
	  	bk.indicator = km.indicator AND
		km.indicator = dd.indicator AND
		dd.a0_default = km.analysis_option0 AND
		dd.a1_default = km.analysis_option1 AND
		dd.a2_default = km.analysis_option2 AND
		km.default_value = 1;
Line: 5996

        ROLLBACK TO UpdatePMDBSCKPIs;
Line: 6007

        ROLLBACK TO UpdatePMDBSCKPIs;
Line: 6018

        ROLLBACK TO UpdatePMDBSCKPIs;
Line: 6021

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_Kpi ';
Line: 6023

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_Kpi ';
Line: 6027

        ROLLBACK TO UpdatePMDBSCKPIs;
Line: 6030

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_Kpi ';
Line: 6032

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_Kpi ';
Line: 6035

END Update_Kpi;
Line: 6098

    l_Bsc_Kpi_Entity_Rec.Last_Updated_By                :=  0;
Line: 6099

    l_Bsc_Kpi_Entity_Rec.Last_Update_Login              :=  0;
Line: 6100

    l_Bsc_Kpi_Entity_Rec.Last_Update_Login              :=  0;
Line: 6263

    SELECT  Full_Name
    INTO    l_tokens
    FROM    bsc_oaf_analysys_opt_comb_v
    WHERE   Indicator        = p_Kpi_Id
    AND     Analysis_Option0 = p_Option_0
    AND     Analysis_Option1 = p_Option_1
    AND     Analysis_Option2 = p_Option_2
    and     Series_Id        = p_Sid;
Line: 6273

        FND_MESSAGE.SET_NAME('BSC','BSC_MEAS_DELETE_DEPEND');
Line: 6315

    ,   p_delete                IN              BOOLEAN    := FALSE
    ,   x_return_status         OUT    NOCOPY   VARCHAR2
    ,   x_msg_count             OUT    NOCOPY   NUMBER
    ,   x_msg_data              OUT    NOCOPY   VARCHAR2
)IS
    l_bsc_dimset_rec        BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
Line: 6325

    SELECT  indicator
    FROM    BSC_KPIS_B
    WHERE   Source_Indicator  =  p_kpi_id
    AND     Prototype_Flag    <> 2;
Line: 6331

    SELECT   Dim_Group_ID
    FROM     BSC_KPI_DIM_GROUPS
    WHERE    Indicator     =  l_bsc_dimset_rec.Bsc_Kpi_Id
    AND      Dim_Set_Id    =  l_bsc_dimset_rec.Bsc_Dim_Set_Id
    ORDER BY Dim_Group_Index;
Line: 6338

    SELECT   A.Dim_Level_Id  Dim_Level_Id
    FROM     BSC_SYS_DIM_LEVELS_BY_GROUP  A
          ,  BSC_SYS_DIM_GROUPS_VL        B
    WHERE    A.Dim_Group_Id = l_bsc_dimset_rec.Bsc_Dim_Level_Group_Id
    AND      A.Dim_Group_ID = B.Dim_Group_ID
    AND      B.Short_Name  <> BSC_BIS_DIMENSION_PUB.Unassigned_Dim;
Line: 6388

    SELECT MAX(NUM) INTO l_count
    FROM    (SELECT   COUNT(SYS_DIM_LEL.Dim_Group_Id) NUM
         ,   SYS_DIM_LEL.Dim_Level_Id
    FROM     BSC_KPI_DIM_GROUPS            KPI_GROUP
         ,   BSC_SYS_DIM_LEVELS_BY_GROUP   SYS_DIM_LEL
    WHERE    KPI_GROUP.Dim_Group_Id   =    SYS_DIM_LEL.Dim_Group_Id
    AND      KPI_GROUP.Indicator      =    l_bsc_dimset_rec.Bsc_Kpi_Id
    AND      KPI_GROUP.Dim_Set_Id     =    l_bsc_dimset_rec.Bsc_Dim_Set_Id
    GROUP BY SYS_DIM_LEL.Dim_Level_Id);
Line: 6404

    IF (p_delete) THEN
        --delete all the dimension objects before creating.
        --dbms_output.PUT_LINE('DELETE ENABLED');
Line: 6407

        BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Objs_In_DSet
        (       p_commit            =>  FND_API.G_FALSE
            ,   p_kpi_id            =>  p_kpi_id
            ,   p_dim_set_id        =>  p_dim_set_id
            ,   x_return_status     =>  x_return_status
            ,   x_msg_count         =>  x_msg_count
            ,   x_msg_data          =>  x_msg_data
        );
Line: 6421

    BSC_BIS_KPI_MEAS_PUB.Delete_Default_Kpi_Dim_Object
    (       p_commit                =>  FND_API.G_FALSE
        ,   p_kpi_id                =>  p_kpi_id
        ,   p_dim_set_id            =>  p_dim_set_id
        ,   x_return_status         =>  x_return_status
        ,   x_msg_count             =>  x_msg_count
        ,   x_msg_data              =>  x_msg_data
    );
Line: 6448

        BSC_DIMENSION_SETS_PUB.Create_Dim_Level_Properties--insert into BSC_KPI_DIM_LEVEL_PROPERTIES
        (       p_commit            =>  FND_API.G_FALSE
            ,   p_Dim_Set_Rec       =>  l_bsc_dimset_rec
            ,   x_return_status     =>  x_return_status
            ,   x_msg_count         =>  x_msg_count
            ,   x_msg_data          =>  x_msg_data
        );
Line: 6504

        SELECT MAX(NUM) INTO l_count
        FROM  (SELECT   COUNT(SYS_DIM_LEL.Dim_Group_Id) NUM
             ,   SYS_DIM_LEL.Dim_Level_Id
        FROM     BSC_KPI_DIM_GROUPS            KPI_GROUP
             ,   BSC_SYS_DIM_LEVELS_BY_GROUP   SYS_DIM_LEL
        WHERE    KPI_GROUP.Dim_Group_Id   =    SYS_DIM_LEL.Dim_Group_Id
        AND      KPI_GROUP.Indicator      =    l_bsc_dimset_rec.Bsc_Kpi_Id
        AND      KPI_GROUP.Dim_Set_Id     =    l_bsc_dimset_rec.Bsc_Dim_Set_Id
        GROUP BY SYS_DIM_LEL.Dim_Level_Id);
Line: 6533

            BSC_DIMENSION_SETS_PUB.Create_Dim_Level_Properties--insert into BSC_KPI_DIM_LEVEL_PROPERTIES
            (       p_commit            =>  FND_API.G_FALSE
                ,   p_Dim_Set_Rec       =>  l_bsc_dimset_rec
                ,   x_return_status     =>  x_return_status
                ,   x_msg_count         =>  x_msg_count
                ,   x_msg_data          =>  x_msg_data
            );
Line: 6645

          API to DELETE DIMENSION-OBJECTS IN  DIMENSION SETS USED IN CASCADING
          It should only be called for BSC type of Dimension Sets not for PMF type
*********************************************************************************/
PROCEDURE Delete_Dim_Objs_In_DSet
(       p_commit                IN              VARCHAR2   := FND_API.G_TRUE
    ,   p_kpi_id                IN              NUMBER
    ,   p_dim_set_id            IN              NUMBER
    ,   x_return_status         OUT    NOCOPY   VARCHAR2
    ,   x_msg_count             OUT    NOCOPY   NUMBER
    ,   x_msg_data              OUT    NOCOPY   VARCHAR2
)IS
    l_bsc_dimset_rec        BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
Line: 6661

    SELECT  indicator
    FROM    BSC_KPIS_B
    WHERE   Source_Indicator  =  p_kpi_id
    AND     Prototype_Flag   <>  2;
Line: 6667

    SELECT  Dim_Group_ID
    FROM    BSC_KPI_DIM_GROUPS
    WHERE   Indicator     =  l_bsc_dimset_rec.Bsc_Kpi_Id
    AND     Dim_Set_Id    =  l_bsc_dimset_rec.Bsc_Dim_Set_Id
    ORDER BY Dim_Group_Index;
Line: 6674

    SELECT  dim_level_id
    FROM    BSC_SYS_DIM_LEVELS_BY_GROUP
    WHERE   dim_group_id = l_bsc_dimset_rec.Bsc_Dim_Level_Group_Id;
Line: 6699

            BSC_DIMENSION_SETS_PUB.Delete_Dim_Level_Properties
            (       p_commit            =>  FND_API.G_FALSE
                ,   p_Dim_Set_Rec       =>  l_bsc_dimset_rec
                ,   x_return_status     =>  x_return_status
                ,   x_msg_count         =>  x_msg_count
                ,   x_msg_data          =>  x_msg_data
            );
Line: 6710

            BSC_DIMENSION_SETS_PUB.Delete_Dim_Levels
            (       p_commit            =>  FND_API.G_FALSE
                ,   p_Dim_Set_Rec       =>  l_bsc_dimset_rec
                ,   x_return_status     =>  x_return_status
                ,   x_msg_count         =>  x_msg_count
                ,   x_msg_data          =>  x_msg_data
            );
Line: 6753

                BSC_DIMENSION_SETS_PUB.Delete_Dim_Level_Properties
                (       p_commit            =>  FND_API.G_FALSE
                    ,   p_Dim_Set_Rec       =>  l_bsc_dimset_rec
                    ,   x_return_status     =>  x_return_status
                    ,   x_msg_count         =>  x_msg_count
                    ,   x_msg_data          =>  x_msg_data
                );
Line: 6764

                BSC_DIMENSION_SETS_PUB.Delete_Dim_Levels
                (       p_commit            =>  FND_API.G_FALSE
                    ,   p_Dim_Set_Rec       =>  l_bsc_dimset_rec
                    ,   x_return_status     =>  x_return_status
                    ,   x_msg_count         =>  x_msg_count
                    ,   x_msg_data          =>  x_msg_data
                );
Line: 6832

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Objs_In_DSet ';
Line: 6834

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Objs_In_DSet ';
Line: 6840

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Objs_In_DSet ';
Line: 6842

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Objs_In_DSet ';
Line: 6845

END Delete_Dim_Objs_In_DSet;
Line: 6857

    SELECT  Name||'['||Indicator||']' Indicator
    FROM    BSC_KPIS_VL
    WHERE  (Source_Indicator =  p_kpi_id
    OR      Indicator        =  p_kpi_id)
    AND     share_flag      <>  2;
Line: 6872

        SELECT COUNT(*) INTO l_Count
        FROM   BSC_KPI_DIM_GROUPS
        WHERE  Indicator  =  p_Kpi_ID
        AND    Dim_Set_Id =  p_Dim_Set_ID;
Line: 6956

    SELECT   A.Short_Name
          ,  B.Dim_Group_Index
    FROM     BSC_SYS_DIM_GROUPS_VL    A
          ,  BSC_KPI_DIM_GROUPS       B
    WHERE    A.Dim_Group_Id   =   B.Dim_Group_Id
    AND      B.Indicator      =   p_Kpi_ID
    AND      B.Dim_Set_ID     =   p_Dim_Set_ID
    ORDER BY B.Dim_Group_Index;
Line: 7137

    SELECT   A.Short_Name
    FROM     BSC_SYS_DIM_GROUPS_VL    A
           , BSC_KPI_DIM_GROUPS       B
    WHERE    A.Dim_Group_Id   =   B.Dim_Group_Id
    AND      B.Indicator      =   p_Kpi_ID
    AND      B.Dim_Set_ID     =   p_Dim_Set_ID
    ORDER BY B.Dim_Group_Index;
Line: 7146

    SELECT   VL.SHORT_NAME
    FROM     BSC_SYS_DIM_LEVELS_B        VL ,
             BSC_SYS_DIM_LEVELS_BY_GROUP DG ,
             BSC_SYS_DIM_GROUPS_VL       TL
    WHERE    VL.DIM_LEVEL_ID = DG.DIM_LEVEL_ID
    AND      DG.dim_group_id = TL.DIM_GROUP_ID
    AND      INSTR(l_final_dim_names,','||TL.SHORT_NAME||',') > 0;
Line: 7164

    l_old_dim_array.delete;
Line: 7203

    SELECT COUNT(b.dim_level_id) INTO l_count
    FROM   BSC_SYS_DIM_LEVELS_BY_GROUP b,
           BSC_SYS_DIM_GROUPS_VL       vl
    WHERE  b.dim_group_id = vl.dim_group_id
    AND    INSTR(l_final_dim_names,','||vl.short_name ||',')>0 ;
Line: 7218

    SELECT COUNT(DIM_SET_ID) INTO l_dim_set_count
    FROM   BSC_KPI_DIM_SETS_VL
    WHERE  INDICATOR = p_Kpi_ID;
Line: 7242

    l_dim_objs_array.delete;
Line: 7322

    SELECT PARENT_SHORT_NAME
    FROM   BSC_SYS_DIM_LEVEL_RELS_V
    WHERE  SHORT_NAME = l_dim_short_name_temp;
Line: 7342

       dim_objs_array_temp.delete;
Line: 7395

SELECT short_name
INTO l_sname
FROM BSC_KPIS_B
WHERE INDICATOR = p_kpi_id;
Line: 7402

 SELECT COUNT (DISTINCT dataset_id) INTO l_count
   FROM BSC_KPI_ANALYSIS_MEASURES_B
   WHERE INDICATOR = p_kpi_id;
Line: 7408

  SELECT COUNT (DISTINCT A.dataset_id) INTO l_count
    FROM BSC_KPI_ANALYSIS_MEASURES_B A, BSC_SYS_DATASETS_B B
    WHERE INDICATOR = p_kpi_id
    AND A.DATASET_ID = B.DATASET_ID
    AND B.SOURCE = 'BSC';
Line: 7424

         API TO DELETE PMF/BSC MEASURES/ANALYSIS OPTIONS WITHIN AN INDICATOR
*********************************************************************************/
PROCEDURE Delete_KPI_Multi_Groups_Opts
(       p_commit                IN          VARCHAR2   := FND_API.G_TRUE
    ,   p_kpi_id                IN          NUMBER
    ,   p_data_source           IN          VARCHAR2
    ,   p_Option_0              IN          NUMBER
    ,   p_Option_1              IN          NUMBER
    ,   p_Option_2              IN          NUMBER
    ,   p_Sid                   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_Num_Tbl              BSC_ANALYSIS_OPTION_PUB.Anal_Opt_Comb_Num_Tbl_Type;
Line: 7447

    l_Default_Delete            BOOLEAN := FALSE;
Line: 7453

    l_series_delete             BOOLEAN;
Line: 7457

    SELECT   Analysis_Group_Id
         ,   Num_Of_Options
         ,   Dependency_Flag
    FROM     BSC_KPI_ANALYSIS_GROUPS
    WHERE    Indicator = p_kpi_id
    ORDER BY Analysis_Group_Id;
Line: 7465

    SELECT  indicator
    FROM    BSC_KPIS_B
    WHERE   Source_Indicator =   p_kpi_id
    AND     Prototype_Flag   <>  2;
Line: 7472

    SAVEPOINT DeleteBSCKPIMulAnaOpts;
Line: 7487

    l_series_delete                                 :=  FALSE;
Line: 7512

    SELECT  COUNT(*) INTO l_count
    FROM    BSC_KPIS_B
    WHERE   indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
Line: 7516

        FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
Line: 7534

    SELECT  share_flag  INTO    l_count
    FROM    BSC_KPIS_B
    WHERE   indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
Line: 7543

    SELECT  dim_set_id INTO l_Bsc_Anal_Opt_Rec.Bsc_Dim_Set_Id
    FROM    BSC_KPI_ANALYSIS_OPTIONS_B
    WHERE   indicator         = p_kpi_id
    AND     analysis_group_id = 0
    AND     option_id         = p_Option_0;
Line: 7563

    /*SELECT COUNT (DISTINCT dataset_id) INTO l_count
    FROM BSC_KPI_ANALYSIS_MEASURES_B
    WHERE INDICATOR = p_kpi_id;*/
Line: 7586

        SELECT COUNT(0)
        INTO   l_max_group_count
        FROM   BSC_KPI_ANALYSIS_GROUPS
        WHERE  INDICATOR = p_kpi_id;
Line: 7596

            BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups
            (      p_commit              =>    FND_API.G_FALSE
               ,   p_Kpi_id              =>    p_kpi_id
               ,   p_Anal_Opt_Tbl        =>    l_Anal_Opt_Tbl
               ,   p_max_group_count     =>    l_max_group_count
               ,   p_Anal_Opt_Comb_Tbl   =>    l_Anal_Num_Tbl
               ,   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: 7612

            l_Default_Delete    := TRUE;
Line: 7615

    IF ((p_data_source = 'PMF') OR (l_Default_Delete)) THEN
        BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Analysis_Options
        (       p_Bsc_Anal_Opt_Rec  =>  l_Bsc_Anal_Opt_Rec
            ,   p_data_source       =>  p_data_source
            ,   x_return_status     =>  x_return_status
            ,   x_msg_count         =>  x_msg_count
            ,   x_msg_data          =>  x_msg_data
        );
Line: 7642

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

/*    SELECT COUNT (DISTINCT dataset_id) INTO l_new_count
    FROM BSC_KPI_ANALYSIS_MEASURES_B
    WHERE INDICATOR = p_kpi_id;*/
Line: 7693

        SELECT COUNT (DISTINCT dataset_id) INTO l_new_count
        FROM BSC_KPI_ANALYSIS_MEASURES_B
        WHERE INDICATOR = l_kpi_id;
Line: 7737

        ROLLBACK TO DeleteBSCKPIMulAnaOpts;
Line: 7748

        ROLLBACK TO DeleteBSCKPIMulAnaOpts;
Line: 7759

        ROLLBACK TO DeleteBSCKPIMulAnaOpts;
Line: 7762

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Multi_Groups_Opts ';
Line: 7764

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Multi_Groups_Opts ';
Line: 7768

        ROLLBACK TO DeleteBSCKPIMulAnaOpts;
Line: 7771

            x_msg_data      :=  x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Multi_Groups_Opts ';
Line: 7773

            x_msg_data      :=  SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Multi_Groups_Opts ';
Line: 7776

END Delete_KPI_Multi_Groups_Opts;
Line: 7809

    SELECT SOURCE
    INTO   l_Source
    FROM  BSC_SYS_PERIODICITIES
    WHERE PERIODICITY_ID =l_baseperiod;
Line: 7891

  SELECT MES.SHORT_NAME
  INTO   l_measure_short_name
  FROM   BSC_SYS_MEASURES MES,
         BSC_SYS_DATASETS_B  SYS
  WHERE  MES.MEASURE_ID = SYS.MEASURE_ID1
  AND    SYS.DATASET_ID = p_dataset_id;
Line: 7940

                   SELECT NAME
                   INTO   x_dimobj_name
                   FROM   BIS_LEVELS_VL
                   WHERE  SHORT_NAME = l_dim_sht_name;
Line: 7963

                    SELECT NAME
                    INTO   x_dimobj_name
                    FROM   BIS_LEVELS_VL
                    WHERE  SHORT_NAME = l_comma_shtnames;
Line: 8027

    SELECT actual_data_source
    INTO   l_actual_data_source
    FROM   BIS_INDICATORS
    WHERE  SHORT_NAME = p_Measure_Short_Name;
Line: 8074

    SELECT DISTINCT tab_id
      FROM bsc_tab_indicators
      WHERE indicator = p_indicator;
Line: 8079

    SELECT 1
      FROM bsc_sys_com_dim_levels
      WHERE tab_id = p_tab_id
      AND   dim_level_id IN ( SELECT dim_level_id
                                FROM bsc_sys_dim_levels_by_group dim_lvl, bsc_sys_dim_groups_vl dim
                                WHERE dim.short_name = p_dim
                                AND   dim.dim_group_id = dim_lvl.dim_group_id
                            );
Line: 8137

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