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_GROUPS_PUB SQL Statements

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

Line: 23


 |                                                  UPDATE_DIMENSION_GROUP      |
 | 29-MAY-03  All Enhancement Phase I- Functions added                          |
 |             "Retrieve_Sys_Dim_Lvls_Grp_Wrap"                                 |
 |                        and set_dim_lvl_grp_prop_wrap                         |
 | 07-JUN-03  mahrao Modified for ALL enhancement                               |
 | 13-JUN-03  Adeulgao fixed Bug#2878840,Added function Get_Next_Value to get   |
 |            the next DIM GROUP ID                                             |
 | 13-JUN-03  Adeulgao Modified procedure Create_Dimension_Group for Bug2878840 |
 | 14-JUN-03  mahrao   Added Translate_dimesnsion_group procedure               |
 | 17-JUL-03  mahrao   Modified exception handling section of                   |
 |                     Translate_Dimension_Group as part of forward porting of  |
 |                     ALL enhancement to BSC 5.1                               |
 |                     Modified load_dimension_group as part of forward porting |
 |                     of ALL enhancement to BSC 5.1.                           |
 |                     Modified exception handling section of                   |
 |                     load_dim_levels_in_group as part of forward porting of   |
 |                     ALL enhancement to BSC 5.1                               |
 |                     Modified exception handling section of as                |
 |                     ret_dimgrpid_fr_shname part of forward port of           |
 |                     ALL enhancement to BSC 5.1                               |
 | 22-JUL-2003 arhegde bug#3050270 Added dim_properties_default_values and calls|
 | 29-OCT-2003 mahrao  bug#3209967 Added a column to bsc_sys_dim_levels_by_group|
 | 14-NOV-2003 mahrao  x_dim_level_where_clause is removed from prcoedure       |
 |                     Retrieve_Sys_Dim_Lvls_Grp_Wrap as PMF 4.0.7 shouldn't    |
 |                     pick up any dependency on 5.1.1                          |
 | 07-JAN-2004 rpenneru bug#3459443 Modified for getting where clause from      |
 |                                 BSC data model		                |
 | 30-Jul-04   rpenneru Modified for enhancemen#3748519                         |
 | 21-DEC-04   vtulasi  Modified for bug#4045278 - Addtion of LUD               |
 | 30-DEC-04   vtulasi  For bug #4093926                                        |
+==============================================================================+
*/
G_PKG_NAME      CONSTANT    varchar2(30) := 'BSC_DIMENSION_GROUPS_PUB';

Line: 106


  l_Dim_Grp_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);

Line: 108


    l_Dim_Grp_Rec.Bsc_Creation_Date := l_Dim_Grp_Rec.Bsc_Last_Update_Date;

Line: 110


  if l_Dim_Grp_Rec.Bsc_Last_Updated_By is null then
    l_Dim_Grp_Rec.Bsc_Last_Updated_By := FND_GLOBAL.USER_ID;

Line: 113


   if l_Dim_Grp_Rec.Bsc_Last_Update_Login is null then
    l_Dim_Grp_Rec.Bsc_Last_Update_Login := FND_GLOBAL.LOGIN_ID;

Line: 158


          select dim_group_id
            into l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
            from BSC_SYS_DIM_GROUPS_VL
           where upper(name) = upper(p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name);

Line: 237


    l_Dim_Grp_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);

Line: 362


procedure Update_Dimension_Group(
  p_commit              IN            varchar2 := FND_API.G_FALSE
 ,p_Dim_Grp_Rec         IN            BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
 ,p_create_Dim_Levels   IN            BOOLEAN
 ,x_return_status       OUT  NOCOPY   varchar2
 ,x_msg_count           OUT  NOCOPY   number
 ,x_msg_data            OUT  NOCOPY   varchar2
) IS

l_Dim_Grp_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;

Line: 375


  l_Dim_Grp_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);

Line: 378


  BSC_DIMENSION_GROUPS_PVT.Update_Dimension_Group( p_commit
                                            ,l_Dim_Grp_Rec
                                            ,x_return_status
                                            ,x_msg_count
                                            ,x_msg_data);

Line: 384


      Update_Dim_Levels_In_Group( p_commit
                                 ,p_Dim_Grp_Rec
                                 ,x_return_status
                                 ,x_msg_count
                                 ,x_msg_data);

Line: 418


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group ';

Line: 420


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group ';

Line: 426


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group ';

Line: 428


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group ';

Line: 432


END Update_Dimension_Group;

Line: 435


procedure Update_Dimension_Group(
  p_commit              IN            varchar2 := FND_API.G_FALSE
 ,p_Dim_Grp_Rec         IN            BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type
 ,x_return_status       OUT   NOCOPY  varchar2
 ,x_msg_count           OUT   NOCOPY  number
 ,x_msg_data            OUT   NOCOPY  varchar2
) is

l_Dim_Grp_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;

Line: 446


  l_Dim_Grp_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);

Line: 449


  Update_Dimension_Group(
    p_commit              =>  p_commit
   ,p_Dim_Grp_Rec         =>  l_Dim_Grp_Rec
   ,p_create_Dim_Levels   =>  TRUE
   ,x_return_status       =>  x_return_status
   ,x_msg_count           =>  x_msg_count
   ,x_msg_data            =>  x_msg_data
    );

Line: 484


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group ';

Line: 486


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group ';

Line: 492


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group ';

Line: 494


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group ';

Line: 498


end Update_Dimension_Group;

Line: 505


procedure Delete_Dimension_Group(
  p_commit              IN            varchar2 := FND_API.G_FALSE
 ,p_Dim_Grp_Rec         IN            BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_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: 517


  Delete_Dim_Levels_In_Group( p_commit
                             ,p_Dim_Grp_Rec
                             ,x_return_status
                             ,x_msg_count
                             ,x_msg_data);

Line: 525


  BSC_DIMENSION_GROUPS_PVT.Delete_Dimension_Group( p_commit
                                                  ,p_Dim_Grp_Rec
                                                  ,x_return_status
                                                  ,x_msg_count
                                                  ,x_msg_data);

Line: 556


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Delete_Dimension_Group ';

Line: 558


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Delete_Dimension_Group ';

Line: 564


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Delete_Dimension_Group ';

Line: 566


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Delete_Dimension_Group ';

Line: 570


end Delete_Dimension_Group;

Line: 749


procedure Update_Dim_Levels_In_Group(
  p_commit              IN      varchar2 := FND_API.G_FALSE
 ,p_Dim_Grp_Rec       IN      BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_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: 759


  BSC_DIMENSION_GROUPS_PVT.Update_Dim_Levels_In_Group( p_commit
                                                ,p_Dim_Grp_Rec
                                                ,x_return_status
                                                ,x_msg_count
                                                ,x_msg_data);

Line: 789


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Update_Dim_Levels_In_Group ';

Line: 791


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Update_Dim_Levels_In_Group ';

Line: 797


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Update_Dim_Levels_In_Group ';

Line: 799


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Update_Dim_Levels_In_Group ';

Line: 803


end Update_Dim_Levels_In_Group;

Line: 810


procedure Delete_Dim_Levels_In_Group(
  p_commit              IN      varchar2 := FND_API.G_FALSE
 ,p_Dim_Grp_Rec         IN      BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_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: 820


  BSC_DIMENSION_GROUPS_PVT.Delete_Dim_Levels_In_Group( p_commit
                                                ,p_Dim_Grp_Rec
                                                ,x_return_status
                                                ,x_msg_count
                                                ,x_msg_data);

Line: 850


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Delete_Dim_Levels_In_Group ';

Line: 852


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Delete_Dim_Levels_In_Group ';

Line: 858


            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_GROUPS_PUB.Delete_Dim_Levels_In_Group ';

Line: 860


            x_msg_data      :=  SQLERRM||' at BSC_DIMENSION_GROUPS_PUB.Delete_Dim_Levels_In_Group ';

Line: 864


end Delete_Dim_Levels_In_Group;

Line: 903


    SELECT dim_group_id
    FROM   bsc_sys_dim_groups_vl
    WHERE  short_name = cp_dim_shortname;

Line: 908


    SELECT dim_level_id, total_disp_name, comp_disp_name
    FROM   bsc_sys_dim_levels_vl
    WHERE  short_name = cp_dim_level_shortname;

Line: 1107


    SELECT dim_level_id
  INTO   l_dim_level_id
  FROM   bsc_sys_dim_levels_b
  WHERE  short_name = p_dim_level_shortname;

Line: 1118


    SELECT dim_group_id
  INTO   l_dim_group_id
  FROM   bsc_sys_dim_groups_vl
  WHERE  short_name = p_dim_shortname;

Line: 1127


    BSC_DIMENSION_GROUPS_PVT.Update_Dim_Levels_In_Group(
     p_commit => FND_API.G_TRUE
    ,p_dim_grp_rec => l_dim_grp_rec
    ,x_return_status => x_return_status
    ,x_msg_count => x_msg_count
    ,x_msg_data => x_msg_data
    );

Line: 1239


   ,p_is_insert => 'N'
   ,p_counter => 0
   ,x_dim_group_name => l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
  );

Line: 1244


  l_Dim_Grp_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);

Line: 1288


 ,p_is_insert IN VARCHAR2 := 'Y'
 ,x_dim_group_name OUT NOCOPY VARCHAR2
)
IS
  l_count NUMBER;

Line: 1299


  IF (p_is_insert = 'Y') THEN
    SELECT count(dim_group_id)
    INTO l_count
    FROM bsc_sys_dim_groups_vl
    WHERE
    UPPER(name) = UPPER(p_dim_group_name);

Line: 1305


  ELSE -- for update
    SELECT count(dim_group_id)
    INTO l_count
    FROM bsc_sys_dim_groups_vl
    WHERE
    UPPER(name) = UPPER(p_dim_group_name)
    AND
    UPPER(short_name) <> UPPER(p_dim_group_short_name);

Line: 1321


     ,p_is_insert => p_is_insert
     ,x_dim_group_name => x_dim_group_name
    );

Line: 1346


  l_is_insert VARCHAR2(10) := 'Y';

Line: 1355


  l_Bsc_Dim_Group_Rec.Bsc_Last_Updated_By := p_Dim_Grp_Rec.Bsc_Last_Updated_By;

Line: 1357


  SELECT count(dim_group_id)
    INTO l_count
    FROM bsc_sys_dim_groups_vl
    WHERE
    short_name = l_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Short_Name;

Line: 1363


  IF (l_count > 0) THEN -- update
    l_is_insert := 'N';

Line: 1374


   ,p_is_insert => l_is_insert
   ,p_counter => 0
   ,x_dim_group_name => l_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Name
  );

Line: 1380


  l_Bsc_Dim_Group_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);

Line: 1382


  IF (l_is_insert = 'N') THEN

    l_owner_name := BIS_UTILITIES_PUB.Get_Owner_Name(p_Dim_Grp_Rec.Bsc_Last_Updated_By);

Line: 1386


    BIS_UTIL.Validate_For_Update (p_last_update_date  =>  l_Bsc_Dim_Group_Rec.Bsc_Last_Update_Date
                                 ,p_owner             =>  l_owner_name
			         ,p_force_mode        =>  p_force_mode
			         ,p_table_name        =>  'BSC_SYS_DIM_GROUPS_VL'
			         ,p_key_value         =>  l_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Short_Name
			         ,x_ret_code          =>  l_ret_code
			         ,x_return_status     =>  x_return_status
			         ,x_msg_data          =>  x_msg_data
			         );

Line: 1397


     BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group(
        p_commit => FND_API.G_TRUE
       ,p_Dim_Grp_Rec => l_Bsc_Dim_Group_Rec
       ,p_create_Dim_Levels => FALSE
       ,x_return_status => x_return_status
       ,x_msg_count => x_msg_count
       ,x_msg_data => x_msg_data
     );

Line: 1471


  SELECT dim_level_id
  INTO   l_Bsc_Dim_Group_Rec.Bsc_Level_Id
  FROM   bsc_sys_dim_levels_b
  WHERE  short_name = p_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name;

Line: 1476


  SELECT count(a.dim_level_id)
   INTO l_rel_count
   FROM
   bsc_sys_dim_levels_b a
   , bsc_sys_dim_groups_vl b
   , bsc_sys_dim_levels_by_group c
   WHERE
   a.short_name = p_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name
   and b.short_name = l_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Short_Name
   and a.dim_level_id = c.dim_level_id
   and b.dim_group_id = c.dim_group_id;

Line: 1500


      Update_Dim_Levels_In_Group(
        p_commit => p_Commit
       ,p_Dim_Grp_Rec => l_Bsc_Dim_Group_Rec
       ,x_return_status => x_return_status
       ,x_msg_count => x_msg_count
       ,x_msg_data => x_msg_data
      );

Line: 1551


  SELECT dim_group_id
  FROM   bsc_sys_dim_groups_vl
  WHERE  short_name = cp_short_name;