eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BSC_DIMENSION_SETS_PUB SQL Statements

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

Line: 26


 |                                          Update_Dim_Levels for Bug# 3141813          |
 |                      19-SEP-2003 ADRAO   Added API Reorder_Dim_Level                 |
 |                      20-SEP-2003 ADRAO   Added a condition not to allow more than 1  |
 |                                          DimObj in Comparison within a DimSet.       |
 |                      20-NOV-2003 PAJOHRI Bug #3269384                                |
 |                      15-DEC-2003 ADRAO   removed Dynamic SQLs for Bug #3236356       |
 |                      12-APR-2004 PAJOHRI Bug #3426566, added conditions to filter    |
 |                                          Dimension whose Short_Name = 'UNASSIGNED'   |
 |                      07-DEC-2004 ADRAO   Added API Get_MN_Table_Name for Bug#4052221 |
 |                      30-MAR-2005 ADRAO   Relaxed the validation to check for mixed   |
 |                                          Dimension Objects within a Dimension for    |
 |                                          BSC 5.3 (Conditionally)                     |
 |                                           (BSC_NO_MIX_DIM_SET_SOURCE)                |
 |                      28-APR-2005 ADRAO   Fixed Bug#4335892                           |
 |                      03-JAN-2006 ashankar Fixed Bug#5734259                          |
 +======================================================================================+
*/


G_PKG_NAME    CONSTANT  varchar2(30) := 'BSC_DIMENSION_SETS_PUB';

Line: 55


    SELECT COUNT(Dim_Group_ID) INTO l_Count
    FROM   BSC_SYS_DIM_GROUPS_VL
    WHERE  Dim_Group_Id  = p_Dim_Group_Id
    AND    Short_Name   <> BSC_BIS_DIMENSION_PUB.Unassigned_Dim;

Line: 78


   SELECT R.RELATION_COL INTO l_Table_Name
   FROM   BSC_SYS_DIM_LEVEL_RELS R
   WHERE  R.DIM_LEVEL_ID        = p_Dim_Level_Id
   AND    R.PARENT_DIM_LEVEL_ID = p_Parent_Dim_Level_Id
   AND    R.RELATION_TYPE       = C_REL_MANY_TO_MANY;

Line: 146


      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_Dim_Set_Rec.Bsc_Kpi_Id
             AND      KPI_GROUP.Dim_Set_Id     =    l_Dim_Set_Rec.Bsc_Dim_Set_Id
             GROUP BY SYS_DIM_LEL.Dim_Level_Id);

Line: 196


      Update_Kpi_Analysis_Options_B( p_commit
                                    ,l_Dim_Set_Rec
                                    ,x_return_status
                                    ,x_msg_count
                                    ,x_msg_data);

Line: 397


procedure Update_Dim_Group_In_Dset(
  p_commit              IN      varchar2 := FND_API.G_FALSE
 ,p_Dim_Set_Rec         IN      BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
 ,p_create_Dim_Lev_Grp  IN         BOOLEAN
 ,x_return_status       OUT NOCOPY     varchar2
 ,x_msg_count           OUT NOCOPY     number
 ,x_msg_data            OUT NOCOPY     varchar2
) is
    l_count    NUMBER := 0;

Line: 408


  BSC_DIMENSION_SETS_PVT.Update_Dim_Group_In_Dset( p_commit
                        ,p_Dim_Set_Rec
                        ,x_return_status
                        ,x_msg_count
                        ,x_msg_data);

Line: 414


  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      =    p_Dim_Set_Rec.Bsc_Kpi_Id
         AND      KPI_GROUP.Dim_Set_Id     =    p_Dim_Set_Rec.Bsc_Dim_Set_Id
         GROUP BY SYS_DIM_LEL.Dim_Level_Id);

Line: 429


    Update_Bsc_Kpi_Dim_Sets_Tl( p_commit
                             ,p_Dim_Set_Rec
                             ,x_return_status
                             ,x_msg_count
                             ,x_msg_data);

Line: 437


    Update_Dim_Level_Properties( p_commit
                              ,p_Dim_Set_Rec
                              ,x_return_status
                              ,x_msg_count
                              ,x_msg_data);

Line: 445


    Update_Dim_Levels( p_commit
                    ,p_Dim_Set_Rec
                    ,x_return_status
                    ,x_msg_count
                    ,x_msg_data);

Line: 478


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Update_Dim_Group_In_Dset ';

Line: 480


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Update_Dim_Group_In_Dset ';

Line: 486


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Update_Dim_Group_In_Dset ';

Line: 488


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Update_Dim_Group_In_Dset ';

Line: 491


end Update_Dim_Group_In_Dset;

Line: 493


procedure Update_Dim_Group_In_Dset(
  p_commit              IN      varchar2 := FND_API.G_FALSE
 ,p_Dim_Set_Rec       IN      BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
 ,x_return_status       OUT NOCOPY     varchar2
 ,x_msg_count           OUT NOCOPY     number
 ,x_msg_data            OUT NOCOPY     varchar2
) is

begin
    x_return_status :=  FND_API.G_RET_STS_SUCCESS;

Line: 503


    Update_Dim_Group_In_Dset(
      p_commit              => p_commit
     ,p_Dim_Set_Rec         => p_Dim_Set_Rec
     ,p_create_Dim_Lev_Grp  => TRUE
     ,x_return_status       => x_return_status
     ,x_msg_count           => x_msg_count
     ,x_msg_data            => x_msg_data
    );

Line: 538


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Update_Dim_Group_In_Dset ';

Line: 540


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Update_Dim_Group_In_Dset ';

Line: 546


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Update_Dim_Group_In_Dset ';

Line: 548


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Update_Dim_Group_In_Dset ';

Line: 551


end Update_Dim_Group_In_Dset;

Line: 560


procedure Delete_Dim_Group_In_Dset
(
        p_commit                IN          varchar2 := FND_API.G_FALSE
    ,   p_Dim_Set_Rec           IN          BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
    ,   p_create_Dim_Lev_Grp    IN          BOOLEAN
    ,   x_return_status         OUT NOCOPY  varchar2
    ,   x_msg_count             OUT NOCOPY  number
    ,   x_msg_data              OUT NOCOPY  varchar2
) is
    l_Dim_Set_Rec           BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;

Line: 574


    SELECT  dim_group_id
    FROM    BSC_KPI_DIM_GROUPS
    WHERE   indicator = p_Dim_Set_Rec.Bsc_Kpi_Id
    AND     dim_set_id = p_Dim_Set_Rec.Bsc_Dim_Set_Id;

Line: 580


    SELECT  dim_level_id
    FROM    BSC_SYS_DIM_LEVELS_BY_GROUP
    WHERE   dim_group_id = l_group_id;

Line: 594


        BSC_DIMENSION_SETS_PVT.Delete_Dim_Group_In_Dset( p_commit --BSC_KPI_DIM_GROUPS
                                                      ,l_Dim_Set_Rec
                                                      ,x_return_status
                                                      ,x_msg_count
                                                      ,x_msg_data);

Line: 603


          Delete_Dim_Level_Properties( p_commit --BSC_KPI_DIM_LEVEL_PROPERTIES
                                      ,l_Dim_Set_Rec
                                      ,x_return_status
                                      ,x_msg_count
                                      ,x_msg_data);

Line: 611


          Delete_Dim_Levels( p_commit --BSC_KPI_DIM_LEVELS_B
                            ,l_Dim_Set_Rec
                            ,x_return_status
                            ,x_msg_count
                            ,x_msg_data);

Line: 623


    BSC_DIMENSION_SETS_PVT.Delete_Dim_Group_In_Dset( p_commit    --BSC_KPI_DIM_GROUPS
                                                  ,l_Dim_Set_Rec
                                                  ,x_return_status
                                                  ,x_msg_count
                                                  ,x_msg_data);

Line: 631


        Delete_Dim_Level_Properties( p_commit       --BSC_KPI_DIM_LEVEL_PROPERTIES
                                  ,l_Dim_Set_Rec
                                  ,x_return_status
                                  ,x_msg_count
                                  ,x_msg_data);

Line: 639


        Delete_Dim_Levels( p_commit  --BSC_KPI_DIM_LEVELS_B
                        ,l_Dim_Set_Rec
                        ,x_return_status
                        ,x_msg_count
                        ,x_msg_data);

Line: 653


      Update_Kpi_Analysis_Options_B( p_commit
                                    ,l_Dim_Set_Rec
                                    ,x_return_status
                                    ,x_msg_count
                                    ,x_msg_data);

Line: 662


      Delete_Bsc_Kpi_Dim_Sets_Tl( p_commit -- delete from BSC_KPI_DIM_SETS_TL
                                 ,l_Dim_Set_Rec
                                 ,x_return_status
                                 ,x_msg_count
                                 ,x_msg_data);

Line: 672


      Delete_Dim_Levels( p_commit
                        ,l_Dim_Set_Rec
                        ,x_return_status
                        ,x_msg_count
                        ,x_msg_data);

Line: 705


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Delete_Dim_Group_In_Dset ';

Line: 707


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Delete_Dim_Group_In_Dset ';

Line: 713


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Delete_Dim_Group_In_Dset ';

Line: 715


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Delete_Dim_Group_In_Dset ';

Line: 718


end Delete_Dim_Group_In_Dset;

Line: 721


procedure Delete_Dim_Group_In_Dset(
  p_commit      IN    varchar2 := FND_API.G_FALSE
 ,p_Dim_Set_Rec   IN  BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
 ,x_return_status       OUT NOCOPY   varchar2
 ,x_msg_count           OUT NOCOPY     number
 ,x_msg_data            OUT NOCOPY     varchar2
) is

l_Dim_Set_Rec     BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;

Line: 733


    Delete_Dim_Group_In_Dset(
      p_commit              => p_commit
     ,p_Dim_Set_Rec         => p_Dim_Set_Rec
     ,p_create_Dim_Lev_Grp  => TRUE
     ,x_return_status       => x_return_status
     ,x_msg_count           => x_msg_count
     ,x_msg_data            => x_msg_data
    );

Line: 768


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Delete_Dim_Group_In_Dset ';

Line: 770


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Delete_Dim_Group_In_Dset ';

Line: 776


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Delete_Dim_Group_In_Dset ';

Line: 778


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Delete_Dim_Group_In_Dset ';

Line: 781


end Delete_Dim_Group_In_Dset;

Line: 906


procedure Update_Bsc_Kpi_Dim_Sets_Tl(
  p_commit              IN      varchar2 := FND_API.G_FALSE
 ,p_Dim_Set_Rec       IN      BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
 ,x_return_status       OUT NOCOPY     varchar2
 ,x_msg_count           OUT NOCOPY     number
 ,x_msg_data            OUT NOCOPY     varchar2
) is

begin
  x_return_status :=  FND_API.G_RET_STS_SUCCESS;

Line: 916


  BSC_DIMENSION_SETS_PVT.Update_Bsc_Kpi_Dim_Sets_Tl( p_commit
                                                    ,p_Dim_Set_Rec
                                                    ,x_return_status
                                                    ,x_msg_count
                                                    ,x_msg_data);

Line: 946


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Update_Bsc_Kpi_Dim_Sets_Tl ';

Line: 948


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Update_Bsc_Kpi_Dim_Sets_Tl ';

Line: 954


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Update_Bsc_Kpi_Dim_Sets_Tl ';

Line: 956


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Update_Bsc_Kpi_Dim_Sets_Tl ';

Line: 959


end Update_Bsc_Kpi_Dim_Sets_Tl;

Line: 964


procedure Delete_Bsc_Kpi_Dim_Sets_Tl(
  p_commit      IN    varchar2 := FND_API.G_FALSE
 ,p_Dim_Set_Rec   IN  BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
 ,x_return_status       OUT NOCOPY   varchar2
 ,x_msg_count           OUT NOCOPY     number
 ,x_msg_data            OUT NOCOPY     varchar2
) is

l_count       number;

Line: 976


  BSC_DIMENSION_SETS_PVT.Delete_Bsc_Kpi_Dim_Sets_Tl( p_commit
                                                    ,p_Dim_Set_Rec
                                                    ,x_return_status
                                                    ,x_msg_count
                                                    ,x_msg_data);

Line: 1006


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Delete_Bsc_Kpi_Dim_Sets_Tl ';

Line: 1008


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Delete_Bsc_Kpi_Dim_Sets_Tl ';

Line: 1014


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Delete_Bsc_Kpi_Dim_Sets_Tl ';

Line: 1016


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Delete_Bsc_Kpi_Dim_Sets_Tl ';

Line: 1019


end Delete_Bsc_Kpi_Dim_Sets_Tl;

Line: 1041


SELECT DISTINCT DIM_LEVEL_ID
FROM   BSC_SYS_DIM_LEVELS_BY_GROUP
WHERE  DIM_GROUP_ID = p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id;

Line: 1216


procedure Update_Dim_Level_Properties(
  p_commit              IN      varchar2 := FND_API.G_FALSE
 ,p_Dim_Set_Rec       IN      BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
 ,x_return_status       OUT NOCOPY     varchar2
 ,x_msg_count           OUT NOCOPY     number
 ,x_msg_data            OUT NOCOPY     varchar2
) is

begin
  x_return_status :=  FND_API.G_RET_STS_SUCCESS;

Line: 1226


  BSC_DIMENSION_SETS_PVT.Update_Dim_Level_Properties( p_commit
                                                     ,p_Dim_Set_Rec
                                                     ,x_return_status
                                                     ,x_msg_count
                                                     ,x_msg_data);

Line: 1256


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Update_Dim_Level_Properties ';

Line: 1258


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Update_Dim_Level_Properties ';

Line: 1264


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Update_Dim_Level_Properties ';

Line: 1266


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Update_Dim_Level_Properties ';

Line: 1269


end Update_Dim_Level_Properties;

Line: 1274


procedure Delete_Dim_Level_Properties(
  p_commit        IN          varchar2 := FND_API.G_FALSE
 ,p_Dim_Set_Rec   IN          BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
 ,x_return_status   OUT NOCOPY  varchar2
 ,x_msg_count       OUT NOCOPY  number
 ,x_msg_data        OUT NOCOPY  varchar2
) is

--Define a Table Record.
l_Dim_Set_Rec     BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Tbl_Type;

Line: 1293


SELECT DISTINCT DIM_LEVEL_ID
FROM   BSC_SYS_DIM_LEVELS_BY_GROUP
WHERE  DIM_GROUP_ID = p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id;

Line: 1335


    BSC_DIMENSION_SETS_PVT.Delete_Dim_Level_Properties( p_commit
                                                       ,l_Dim_Set_Rec(i)
                                                       ,x_return_status
                                                       ,x_msg_count
                                                       ,x_msg_data);

Line: 1368


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Delete_Dim_Level_Properties ';

Line: 1370


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Delete_Dim_Level_Properties ';

Line: 1376


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Delete_Dim_Level_Properties ';

Line: 1378


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Delete_Dim_Level_Properties ';

Line: 1381


end Delete_Dim_Level_Properties;

Line: 1401


  SELECT DIM_LEVEL_INDEX,
         PARENT_LEVEL_INDEX,
         PARENT_LEVEL_INDEX2
  FROM   BSC_KPI_DIM_LEVELS_VL
  WHERE  Indicator            =   p_Dim_Set_Rec.Bsc_Kpi_Id
  AND    Dim_Set_Id           =   p_Dim_Set_Rec.Bsc_Dim_Set_Id
  ORDER  BY DIM_LEVEL_INDEX;

Line: 1414


           UPDATE  BSC_KPI_DIM_LEVELS_B
           SET     Parent_Level_Index  =  l_count
           WHERE   Indicator           =  p_Dim_Set_Rec.Bsc_Kpi_Id
           AND     Dim_Set_Id          =  p_Dim_Set_Rec.Bsc_Dim_Set_Id
           AND     Parent_Level_Index  =  cd.Dim_Level_Index;

Line: 1421


           UPDATE  BSC_KPI_DIM_LEVELS_B
           SET     Parent_Level_Index2  =  l_count
           WHERE   Indicator            =  p_Dim_Set_Rec.Bsc_Kpi_Id
           AND     Dim_Set_Id           =  p_Dim_Set_Rec.Bsc_Dim_Set_Id
           AND     Parent_Level_Index2  =  cd.Dim_Level_Index;

Line: 1427


           UPDATE  BSC_KPI_DIM_LEVELS_TL
           SET     Dim_Level_Index      =  l_count
           WHERE   Indicator            =  p_Dim_Set_Rec.Bsc_Kpi_Id
           AND     Dim_Set_Id           =  p_Dim_Set_Rec.Bsc_Dim_Set_Id
           AND     Dim_Level_Index      =  cd.Dim_Level_Index;

Line: 1433


           UPDATE  BSC_KPI_DIM_LEVELS_B
           SET     Dim_Level_Index      =  l_count
           WHERE   Indicator            =  p_Dim_Set_Rec.Bsc_Kpi_Id
           AND     Dim_Set_Id           =  p_Dim_Set_Rec.Bsc_Dim_Set_Id
           AND     Dim_Level_Index      =  cd.Dim_Level_Index;

Line: 1507


    l_Update_Dim_Set_Rec    BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;

Line: 1513


    SELECT    A.Dim_Level_Id
           ,  B.Level_Table_Name
           ,  B.Level_Pk_Col
           ,  B.Name
           ,  B.Help
           ,  B.Total_Disp_Name
           ,  B.Comp_Disp_Name
           ,  B.Level_View_Name
           ,  B.Value_Order_By
           ,  B.Comp_Order_By
           ,  A.Filter_Column
           ,  A.Filter_Value
           ,  A.Default_Value
           ,  A.Default_Type
           ,  A.Parent_In_Total
           ,  A.No_Items
           ,  A.Total_Flag
           ,  A.Comparison_Flag
           ,  B.Source
    FROM      BSC_SYS_DIM_LEVELS_BY_GROUP  A
           ,  BSC_SYS_DIM_LEVELS_VL        B
           ,  BSC_SYS_DIM_GROUPS_VL        C
    WHERE     A.Dim_Group_Id               =  p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id
    AND       C.Dim_Group_Id               =  A.Dim_Group_Id
    AND       C.Short_Name                <>  BSC_BIS_DIMENSION_PUB.Unassigned_Dim
    AND       A.Dim_Level_Id               =  B.Dim_Level_Id
    ORDER  BY A.Dim_Level_Index;

Line: 1542


    SELECT    E.Dim_Level_Id
           ,  E.Parent_Dim_Level_ID
           ,  A.Dim_Level_Index         Dim_Level_Index
           ,  B.Dim_Level_Index         Par_Dim_Level_Index
           ,  C.Abbreviation            Abbreviation
           ,  D.Abbreviation            Parent_Abbreviation
           ,  E.Relation_Type
           ,  D.Level_Pk_Col
           ,  C.Level_Table_Name
           ,  C.Source
    FROM      BSC_KPI_DIM_LEVELS_B    A -- current
           ,  BSC_KPI_DIM_LEVELS_B    B -- parent
           ,  BSC_SYS_DIM_LEVELS_B    C -- current
           ,  BSC_SYS_DIM_LEVELS_B    D -- parent
           ,  BSC_SYS_DIM_LEVEL_RELS  E
    WHERE     A.Indicator             =  p_Dim_Set_Rec.Bsc_Kpi_Id
    AND       A.Dim_Set_Id            =  p_Dim_Set_Rec.Bsc_Dim_Set_Id
    AND       A.Indicator             =  B.Indicator
    AND       A.Dim_Set_Id            =  B.Dim_Set_Id
    AND       A.Level_Table_Name      =  C.Level_Table_Name
    AND       B.Level_Table_Name      =  D.Level_Table_Name
    AND       E.Dim_Level_Id          =  C.Dim_Level_Id
    AND       E.Parent_Dim_Level_Id   =  D.Dim_Level_Id
    AND       C.Source                = 'BSC'
    ORDER BY  B.Dim_Level_Index;

Line: 1569


    SELECT    E.Dim_Level_Id
           ,  E.Parent_Dim_Level_ID
           ,  A.Dim_Level_Index         Dim_Level_Index
           ,  B.Dim_Level_Index         Par_Dim_Level_Index
           ,  C.Abbreviation            Abbreviation
           ,  D.Abbreviation            Parent_Abbreviation
           ,  E.Relation_Type
           ,  D.Level_Pk_Col
           ,  C.Level_Table_Name
           ,  C.Source
    FROM      BSC_KPI_DIM_LEVELS_B    A -- current
           ,  BSC_KPI_DIM_LEVELS_B    B -- parent
           ,  BSC_SYS_DIM_LEVELS_B    C -- current
           ,  BSC_SYS_DIM_LEVELS_B    D -- parent
           ,  BSC_SYS_DIM_LEVEL_RELS  E
    WHERE     A.Indicator             =  p_Dim_Set_Rec.Bsc_Kpi_Id
    AND       A.Dim_Set_Id            =  p_Dim_Set_Rec.Bsc_Dim_Set_Id
    AND       A.Indicator             =  B.Indicator
    AND       A.Dim_Set_Id            =  B.Dim_Set_Id
    AND       A.Level_Table_Name      =  C.Level_Table_Name
    AND       B.Level_Table_Name      =  D.Level_Table_Name
    AND       E.Dim_Level_Id          =  C.Dim_Level_Id
    AND       E.Parent_Dim_Level_Id   =  D.Dim_Level_Id
    AND       C.Source                = 'PMF'
    ORDER BY  B.Dim_Level_Index;

Line: 1630


        SELECT  NVL(MAX(dim_level_index)+1, 0)
        INTO    l_index_cnt
        FROM    BSC_KPI_DIM_LEVELS_B
        WHERE   Indicator  = l_Dim_Set_Rec.Bsc_Kpi_Id
        AND     Dim_Set_Id = l_Dim_Set_Rec.Bsc_Dim_Set_Id;

Line: 1687


            SELECT COUNT(1) INTO l_Count
            FROM   BSC_KPI_DIM_LEVELS_B
            WHERE  Indicator         =  p_Dim_Set_Rec.Bsc_Kpi_Id
            AND    Dim_Set_Id        =  p_Dim_Set_Rec.Bsc_Dim_Set_Id
            AND    Dim_Level_Index   =  cd.Dim_Level_Index
            AND    Parent_Level_Index IS NULL;

Line: 1695


                    l_Update_Dim_Set_Rec.Bsc_Kpi_Id                      :=  p_Dim_Set_Rec.Bsc_Kpi_Id;

Line: 1696


                    l_Update_Dim_Set_Rec.Bsc_Dim_Set_Id                  :=  p_Dim_Set_Rec.Bsc_Dim_Set_Id;

Line: 1697


                    l_Update_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index        :=  cd.Dim_Level_Index;

Line: 1698


                    l_Update_Dim_Set_Rec.Bsc_Level_Name                  :=  cd.Level_Table_Name;

Line: 1700


                        l_Update_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel   :=  cd.Level_Pk_Col;

Line: 1701


                        l_Update_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index :=  cd.Par_Dim_Level_Index;

Line: 1702


                        l_Update_Dim_Set_Rec.Bsc_Dset_Table_Relation     :=  NULL;

Line: 1704


                        l_Update_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel   :=  cd.Level_Pk_Col;

Line: 1705


                        l_Update_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index :=  cd.Par_Dim_Level_Index;

Line: 1706


                        l_Update_Dim_Set_Rec.Bsc_Dset_Table_Relation     :=  NULL;

Line: 1709


                        l_Update_Dim_Set_Rec.Bsc_Dset_Table_Relation     :=
                                     Get_MN_Table_Name(cd.Dim_Level_ID, cd.Parent_Dim_Level_ID);

Line: 1712


                        IF (l_Update_Dim_Set_Rec.Bsc_Dset_Table_Relation IS NULL) THEN
                            IF (cd.Abbreviation < cd.Parent_Abbreviation) THEN
                                l_Update_Dim_Set_Rec.Bsc_Dset_Table_Relation := 'BSC_D_'||cd.Abbreviation||'_'||cd.Parent_Abbreviation;

Line: 1716


                                l_Update_Dim_Set_Rec.Bsc_Dset_Table_Relation := 'BSC_D_'||cd.Parent_Abbreviation||'_'||cd.Abbreviation;

Line: 1720


                        l_Update_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel   :=  NULL;

Line: 1721


                        l_Update_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index :=  NULL;

Line: 1722


                        l_Update_Dim_Set_Rec.Bsc_Dset_Table_Relation     :=  NULL;

Line: 1724


                    BSC_DIMENSION_SETS_PUB.Update_Dim_Levels
                    (       p_commit          =>  FND_API.G_FALSE
                        ,   p_Dim_Set_Rec     =>  l_Update_Dim_Set_Rec
                        ,   x_return_status   =>  x_return_status
                        ,   x_msg_count       =>  x_msg_count
                        ,   x_msg_data        =>  x_msg_data
                    );

Line: 1741


            SELECT COUNT(1) INTO l_Count
            FROM   BSC_KPI_DIM_LEVELS_B
            WHERE  Indicator         =  p_Dim_Set_Rec.Bsc_Kpi_Id
            AND    Dim_Set_Id        =  p_Dim_Set_Rec.Bsc_Dim_Set_Id
            AND    Dim_Level_Index   =  cd.Dim_Level_Index
            AND    Parent_Level_Index IS NULL;

Line: 1750


                l_Update_Dim_Set_Rec.Bsc_Kpi_Id                  :=  p_Dim_Set_Rec.Bsc_Kpi_Id;

Line: 1751


                l_Update_Dim_Set_Rec.Bsc_Dim_Set_Id              :=  p_Dim_Set_Rec.Bsc_Dim_Set_Id;

Line: 1752


                l_Update_Dim_Set_Rec.Bsc_Dset_Dim_Level_Index    :=  cd.Dim_Level_Index;

Line: 1755


                l_Update_Dim_Set_Rec.Bsc_Level_Name              :=  cd.Level_Table_Name;

Line: 1756


                l_Update_Dim_Set_Rec.Bsc_Dset_Parent_Level_Rel   :=  cd.Level_Pk_Col;

Line: 1757


                l_Update_Dim_Set_Rec.Bsc_Dset_Parent_Level_Index :=  cd.Par_Dim_Level_Index;

Line: 1758


                l_Update_Dim_Set_Rec.Bsc_Dset_Table_Relation     :=  NULL;

Line: 1759


                BSC_DIMENSION_SETS_PUB.Update_Dim_Levels
                (       p_commit          =>  FND_API.G_FALSE
                    ,   p_Dim_Set_Rec     =>  l_Update_Dim_Set_Rec
                    ,   x_return_status   =>  x_return_status
                    ,   x_msg_count       =>  x_msg_count
                    ,   x_msg_data        =>  x_msg_data
                );

Line: 1790


        SELECT COUNT(0) INTO l_count
        FROM   BSC_KPI_DIM_LEVELS_VL
        WHERE  Indicator   =   p_Dim_Set_Rec.Bsc_Kpi_Id
        AND    Dim_Set_Id  =   p_Dim_Set_Rec.Bsc_Dim_Set_Id
        AND    Level_Source = 'BSC';

Line: 1806


            SELECT COUNT(DISTINCT(Level_Source)) INTO l_count
            FROM   BSC_KPI_DIM_LEVELS_VL
            WHERE  Indicator   =   p_Dim_Set_Rec.Bsc_Kpi_Id
            AND    Dim_Set_Id  =   p_Dim_Set_Rec.Bsc_Dim_Set_Id
            AND    Level_Source IS NOT NULL;

Line: 1819


        SELECT COUNT(0) INTO l_count
        FROM   BSC_KPI_DIM_LEVELS_VL
        WHERE  Indicator     =   p_Dim_Set_Rec.Bsc_Kpi_Id
        AND    Dim_Set_Id    =   p_Dim_Set_Rec.Bsc_Dim_Set_Id
        AND    DEFAULT_VALUE = 'C'
        AND    Level_Source  = 'BSC';

Line: 1943


procedure Update_Dim_Levels(
  p_commit              IN      varchar2 := FND_API.G_FALSE
 ,p_Dim_Set_Rec       IN      BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
 ,x_return_status       OUT NOCOPY     varchar2
 ,x_msg_count           OUT NOCOPY     number
 ,x_msg_data            OUT NOCOPY     varchar2
) is
    l_count     NUMBER;

Line: 1954


  BSC_DIMENSION_SETS_PVT.Update_Dim_Levels( p_commit
                                           ,p_Dim_Set_Rec
                                           ,x_return_status
                                           ,x_msg_count
                                           ,x_msg_data);

Line: 1967


      SELECT COUNT(DISTINCT(Level_Source)) INTO l_count
      FROM   BSC_KPI_DIM_LEVELS_VL
      WHERE  Indicator   =   p_Dim_Set_Rec.Bsc_Kpi_Id
      AND    Dim_Set_Id  =   p_Dim_Set_Rec.Bsc_Dim_Set_Id
      AND    Level_Source IS NOT NULL;

Line: 1980


  SELECT COUNT(0) INTO l_count
  FROM   BSC_KPI_DIM_LEVELS_VL
  WHERE  Indicator   =   p_Dim_Set_Rec.Bsc_Kpi_Id
  AND    Dim_Set_Id  =   p_Dim_Set_Rec.Bsc_Dim_Set_Id
  AND    Level_Source = 'BSC';

Line: 1992


  SELECT COUNT(0) INTO l_count
  FROM   BSC_KPI_DIM_LEVELS_VL
  WHERE  Indicator   =   p_Dim_Set_Rec.Bsc_Kpi_Id
  AND    Dim_Set_Id  =   p_Dim_Set_Rec.Bsc_Dim_Set_Id
  AND    DEFAULT_VALUE= 'C';

Line: 2028


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Update_Dim_Levels ';

Line: 2030


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Update_Dim_Levels ';

Line: 2036


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Update_Dim_Levels ';

Line: 2038


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Update_Dim_Levels ';

Line: 2041


end Update_Dim_Levels;

Line: 2046


procedure Delete_Dim_Levels
(
   p_commit           IN             VARCHAR2 := FND_API.G_FALSE
 , p_Dim_Set_Rec      IN             BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
 , x_return_status       OUT NOCOPY  VARCHAR2
 , x_msg_count           OUT NOCOPY  NUMBER
 , x_msg_data            OUT NOCOPY  VARCHAR2
) is

-- Define a Table Record.
l_Dim_Set_Rec                   BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Tbl_Type;

Line: 2058


CURSOR c_Delete_Dim_Level IS
SELECT DISTINCT A.DIM_LEVEL_ID
               ,B.LEVEL_TABLE_NAME
               ,B.LEVEL_PK_COL
               ,C.NAME
FROM  BSC_SYS_DIM_LEVELS_BY_GROUP  A,
      BSC_SYS_DIM_LEVELS_B         B,
      BSC_SYS_DIM_LEVELS_TL        C
WHERE A.DIM_GROUP_ID = p_Dim_Set_Rec.Bsc_Dim_Level_Group_Id
AND   A.DIM_LEVEL_ID = B.DIM_LEVEL_ID
AND   B.DIM_LEVEL_ID = C.DIM_LEVEL_ID;

Line: 2095


        FOR cr IN c_Delete_Dim_Level LOOP
           l_Dim_Set_Rec(l_count + 1).Bsc_Level_Id            := cr.Dim_Level_Id;

Line: 2129


          BSC_DIMENSION_SETS_PVT.Delete_Dim_Levels( p_commit
                                                   ,l_Dim_Set_Rec(i)
                                                   ,x_return_status
                                                   ,x_msg_count
                                                   ,x_msg_data);

Line: 2138


        BSC_DIMENSION_SETS_PVT.Delete_Dim_Levels( p_commit
                                                 ,p_Dim_Set_Rec
                                                 ,x_return_status
                                                 ,x_msg_count
                                                 ,x_msg_data);

Line: 2175


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Delete_Dim_Levels ';

Line: 2177


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Delete_Dim_Levels ';

Line: 2183


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Delete_Dim_Levels ';

Line: 2185


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Delete_Dim_Levels ';

Line: 2188


end Delete_Dim_Levels;

Line: 2196


procedure Update_Kpi_Analysis_Options_B(
  p_commit      IN    varchar2 := FND_API.G_FALSE
 ,p_Dim_Set_Rec   IN  BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type
 ,x_return_status       OUT NOCOPY   varchar2
 ,x_msg_count           OUT NOCOPY     number
 ,x_msg_data            OUT NOCOPY     varchar2
) is
begin
    x_return_status :=  FND_API.G_RET_STS_SUCCESS;

Line: 2205


    BSC_DIMENSION_SETS_PVT.Update_Kpi_Analysis_Options_B( p_commit
                                                         ,p_Dim_Set_Rec
                                                         ,x_return_status
                                                         ,x_msg_count
                                                         ,x_msg_data);

Line: 2236


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Update_Kpi_Analysis_Options_B ';

Line: 2238


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Update_Kpi_Analysis_Options_B ';

Line: 2244


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_SETS_PUB.Update_Kpi_Analysis_Options_B ';

Line: 2246


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_SETS_PUB.Update_Kpi_Analysis_Options_B ';

Line: 2249


end Update_Kpi_Analysis_Options_B;