DBA Data[Home] [Help]

APPS.BSC_DIMENSION_GROUPS_PVT SQL Statements

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

Line: 24

 |                        select query.                                                 |
 | 29-MAY-03  All Enhancement Phase I- Functions user group short_name if no id         |
 | 07-JUN-03  mahrao Modified for ALL enhancement                                       |
 | 13-JUN-03  ADEULGAO modified for BUG# 2878840                                        |
 | 13-JUN-03  ADEULGAO modified procedure Create_Dimension_Group for BUG# 2878840       |
 | 14-JUN-03  mahrao   Added Translate_dimesnsion_group procedure                       |
 | 17-JUL-03  mahrao   Modified Translate_dimesnsion_group procedure                    |
 |                     as part of forward porting of ALL enhancement to BSC 5.1         |
 |                     Modified Retrieve_Dim_Group procedure                            |
 |                     as part of forward porting of ALL enhancement to BSC 5.1         |
 | 29-OCT-2003 mahrao  bug#3209967 Added a column to bsc_sys_dim_levels_by_group        |
 | 17-NOV-2003 PAJOHRI  Bug #3232366                                                    |
 | 17-NOV-2003 ADRAO    Bug #3236356 - Removed comments which has Validate_Value()      |
 | 30-Jul-04   rpenneru  Modified for enhancemen#3748519                                |
 | 13-Oct-04   rpenneru  Modified for bug#3945655                                       |
 | 21-DEC-04   vtulasi   Modified for bug#4045278 - Addtion of LUD                      |
 | 01-FEB-05   hengliu	 Modified for bug#4104065 - WHERE_CLAUSE can be null			|
 +======================================================================================+
*/
G_PKG_NAME              CONSTANT        varchar2(30) := 'BSC_DIMENSION_GROUPS_PVT';
Line: 55

  SELECT count(dim_group_id)
    INTO l_count
    FROM BSC_SYS_DIM_GROUPS_VL
    WHERE dim_group_id = p_dim_group_id;
Line: 130

    if l_Dim_Grp_Rec.Bsc_Last_Updated_By is null then
      l_Dim_Grp_Rec.Bsc_Last_Updated_By := 0;
Line: 135

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

    insert into BSC_SYS_DIM_GROUPS_TL( dim_group_id
                                      ,language
                                      ,source_lang
                                      ,name
                                      ,short_name
                                      ,created_by
                                      ,creation_date
                                      ,last_updated_by
                                      ,last_update_date
                                      ,last_update_login)
                               select  l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
                                      ,L.LANGUAGE_CODE
                                      ,userenv('LANG')
                                      ,l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
                                      ,l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name
                                      ,l_Dim_Grp_Rec.Bsc_Created_By -- PMD
                                      ,l_Dim_Grp_Rec.Bsc_Last_Update_Date  -- PMD
                                      ,l_Dim_Grp_Rec.Bsc_Last_Updated_By  -- PMD
                                      ,l_Dim_Grp_Rec.Bsc_Last_Update_Date  -- PMD
                                      ,l_Dim_Grp_Rec.Bsc_Last_Update_Login  -- PMD
                                 from FND_LANGUAGES L
                                where L.INSTALLED_FLAG in ('I', 'B')
                                  and not exists
                                      (select NULL
                                         from BSC_SYS_DIM_GROUPS_TL T
                                        where T.dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
                                          and T.LANGUAGE = L.LANGUAGE_CODE);
Line: 237

  select distinct name
    into x_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
    from BSC_SYS_DIM_GROUPS_VL
   where dim_group_id = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
Line: 288

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_count                         number;
Line: 301

  SELECT dim_group_id
  FROM   BSC_SYS_DIM_GROUPS_TL
  WHERE SHORT_NAME = p_grp_short_name;
Line: 306

  SAVEPOINT UpdateBSCDimGrpPVT;
Line: 348

  IF p_Dim_Grp_Rec.Bsc_Last_Updated_By IS NULL THEN -- Cannot update p_Dim_Grp_Rec
      l_Dim_Grp_Rec.Bsc_Last_Updated_By := FND_GLOBAL.USER_ID;
Line: 352

  IF p_Dim_Grp_Rec.Bsc_Last_Update_Login IS NULL THEN
     l_Dim_Grp_Rec.Bsc_Last_Update_Login := FND_GLOBAL.LOGIN_ID;
Line: 357

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

      update BSC_SYS_DIM_GROUPS_TL
         set name = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
            ,source_lang = userenv('LANG')
            ,last_updated_by = l_Dim_Grp_Rec.Bsc_Last_Updated_By
            ,last_update_date = l_Dim_Grp_Rec.Bsc_Last_Update_Date
            ,last_update_login = p_Dim_Grp_Rec.Bsc_Last_Update_Login
       where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
         and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
Line: 377

        ROLLBACK TO UpdateBSCDimGrpPVT;
Line: 390

        ROLLBACK TO UpdateBSCDimGrpPVT;
Line: 403

        ROLLBACK TO UpdateBSCDimGrpPVT;
Line: 406

            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Update_Dimension_Group ';
Line: 408

            x_msg_data      := 'BSC_DIMENSION_GROUPS_PVT.Update_Dimension_Group '||SQLERRM;
Line: 416

        ROLLBACK TO UpdateBSCDimGrpPVT;
Line: 419

            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Update_Dimension_Group ';
Line: 421

            x_msg_data      := 'BSC_DIMENSION_GROUPS_PVT.Update_Dimension_Group '||SQLERRM;
Line: 425

end Update_Dimension_Group;
Line: 430

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

l_Dim_Grp_Rec                           BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
Line: 444

  SELECT dim_group_id
  FROM   BSC_SYS_DIM_GROUPS_TL
  WHERE SHORT_NAME = p_grp_short_name;
Line: 449

  SAVEPOINT DeleteBSCDimGrpPVT;
Line: 481

         select count(dim_group_id)
      into l_count
      from BSC_SYS_DIM_GROUPS_TL
          where name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
Line: 491

       select distinct dim_group_id
         into l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
         from BSC_SYS_DIM_GROUPS_VL
         where name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
Line: 506

  select count(dim_group_id)
    into l_count
    from BSC_SYS_DIM_LEVELS_BY_GROUP
   where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
Line: 511

    delete from BSC_SYS_DIM_GROUPS_TL
     where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
Line: 524

        ROLLBACK TO DeleteBSCDimGrpPVT;
Line: 537

        ROLLBACK TO DeleteBSCDimGrpPVT;
Line: 550

        ROLLBACK TO DeleteBSCDimGrpPVT;
Line: 553

            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Delete_Dimension_Group ';
Line: 555

            x_msg_data      := 'BSC_DIMENSION_GROUPS_PVT.Delete_Dimension_Group '||SQLERRM;
Line: 563

        ROLLBACK TO DeleteBSCDimGrpPVT;
Line: 566

            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Delete_Dimension_Group ';
Line: 568

            x_msg_data      := 'BSC_DIMENSION_GROUPS_PVT.Delete_Dimension_Group '||SQLERRM;
Line: 572

end Delete_Dimension_Group;
Line: 594

  SELECT dim_group_id
  FROM   BSC_SYS_DIM_GROUPS_TL
  WHERE SHORT_NAME = p_grp_short_name;
Line: 632

         select count(dim_group_id)
           into l_count
           from BSC_SYS_DIM_GROUPS_TL
           where name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
Line: 642

           select distinct dim_group_id
             into l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
             from BSC_SYS_DIM_GROUPS_VL
            where name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
Line: 674

  select count(*)
    into l_count
    from BSC_SYS_DIM_LEVELS_BY_GROUP
   where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
     and dim_level_id = l_Dim_Grp_Rec.Bsc_Level_Id;
Line: 686

      select  NVL((MAX(dim_level_index) + 1), 0)
        into l_Dim_Grp_Rec.Bsc_Dim_Level_Index
        from BSC_SYS_DIM_LEVELS_BY_GROUP
      where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
Line: 696

    insert into BSC_SYS_DIM_LEVELS_BY_GROUP( dim_group_id
                                            ,dim_level_id
                                            ,dim_level_index
                                            ,total_flag
                                            ,comparison_flag
                                            ,filter_column
                                            ,filter_value
                                            ,default_value
                                            ,default_type
                                            ,parent_in_total
                                            ,no_items
                        ,where_clause)
                                     values( l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
                                            ,l_Dim_Grp_Rec.Bsc_Level_Id
                                            ,l_Dim_Grp_Rec.Bsc_Dim_Level_Index
                                            ,l_Dim_Grp_Rec.Bsc_Group_Level_Total_Flag
                                            ,l_Dim_Grp_Rec.Bsc_Group_Level_Comp_Flag
                                            ,l_Dim_Grp_Rec.Bsc_Group_Level_Filter_Col
                                            ,l_Dim_Grp_Rec.Bsc_Group_Level_Filter_Value
                                            ,l_Dim_Grp_Rec.Bsc_Group_Level_Default_Value
                                            ,l_Dim_Grp_Rec.Bsc_Group_Level_Default_Type
                                            ,l_Dim_Grp_Rec.Bsc_Group_Level_Parent_In_Tot
                                            ,l_Dim_Grp_Rec.Bsc_Group_Level_No_Items
                        ,l_Dim_Grp_Rec.Bsc_Group_Level_Where_Clause);
Line: 800

    SELECT DISTINCT dim_level_index
                   ,total_flag
                   ,comparison_flag
                   ,filter_column
                   ,filter_value
                   ,default_value
                   ,default_type
                   ,parent_in_total
                   ,no_items
           ,where_clause
    INTO   x_Dim_Grp_Rec.Bsc_Dim_Level_Index
          ,x_Dim_Grp_Rec.Bsc_Group_Level_Total_Flag
          ,x_Dim_Grp_Rec.Bsc_Group_Level_Comp_Flag
          ,x_Dim_Grp_Rec.Bsc_Group_Level_Filter_Col
          ,x_Dim_Grp_Rec.Bsc_Group_Level_Filter_Value
          ,x_Dim_Grp_Rec.Bsc_Group_Level_Default_Value
          ,x_Dim_Grp_Rec.Bsc_Group_Level_Default_Type
          ,x_Dim_Grp_Rec.Bsc_Group_Level_Parent_In_Tot
          ,x_Dim_Grp_Rec.Bsc_Group_Level_No_Items
      ,x_Dim_Grp_Rec.Bsc_Group_Level_Where_Clause
    FROM  BSC_SYS_DIM_LEVELS_BY_GROUP
    WHERE dim_group_id = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
    AND   dim_level_id = p_Dim_Grp_Rec.Bsc_Level_Id;
Line: 852

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

l_Dim_Grp_Rec                   BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
Line: 867

  SELECT dim_group_id
  FROM   BSC_SYS_DIM_GROUPS_TL
  WHERE SHORT_NAME = p_grp_short_name;
Line: 872

  SAVEPOINT UpdateBSCDimLevInGrpPVT;
Line: 902

         select count(dim_group_id)
          into l_count
          from BSC_SYS_DIM_GROUPS_TL
          where name = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
Line: 912

           select distinct dim_group_id
            into l_Dim_Grp_Rec_in.Bsc_Dim_Level_Group_Id
            from BSC_SYS_DIM_GROUPS_VL
            where name = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
Line: 990

  update BSC_SYS_DIM_LEVELS_BY_GROUP
     set dim_level_index = l_Dim_Grp_Rec.Bsc_Dim_Level_Index
        ,total_flag = l_Dim_Grp_Rec.Bsc_Group_Level_Total_Flag
        ,comparison_flag = l_Dim_Grp_Rec.Bsc_Group_Level_Comp_Flag
        ,filter_column = l_Dim_Grp_Rec.Bsc_Group_Level_Filter_Col
        ,filter_value = l_Dim_Grp_Rec.Bsc_Group_Level_Filter_Value
        ,default_value = l_Dim_Grp_Rec.Bsc_Group_Level_Default_Value
        ,default_type = l_Dim_Grp_Rec.Bsc_Group_Level_Default_Type
        ,parent_in_total = l_Dim_Grp_Rec.Bsc_Group_Level_Parent_In_Tot
        ,no_items = l_Dim_Grp_Rec.Bsc_Group_Level_No_Items
    ,where_clause = l_Dim_Grp_Rec.Bsc_Group_Level_Where_Clause
   where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
     and dim_level_id = l_Dim_Grp_Rec.Bsc_Level_Id;
Line: 1010

        ROLLBACK TO UpdateBSCDimLevInGrpPVT;
Line: 1021

        ROLLBACK TO UpdateBSCDimLevInGrpPVT;
Line: 1032

        ROLLBACK TO UpdateBSCDimLevInGrpPVT;
Line: 1036

            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Update_Dim_Levels_In_Group ';
Line: 1038

            x_msg_data      := 'BSC_DIMENSION_GROUPS_PVT.Update_Dim_Levels_In_Group '||SQLERRM;
Line: 1043

        ROLLBACK TO UpdateBSCDimLevInGrpPVT;
Line: 1047

            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Update_Dim_Levels_In_Group ';
Line: 1049

            x_msg_data      := 'BSC_DIMENSION_GROUPS_PVT.Update_Dim_Levels_In_Group '||SQLERRM;
Line: 1053

end Update_Dim_Levels_In_Group;
Line: 1058

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

l_Dim_Grp_Rec                   BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
Line: 1072

  SELECT dim_group_id
  FROM   BSC_SYS_DIM_GROUPS_TL
  WHERE SHORT_NAME = p_grp_short_name;
Line: 1077

  SAVEPOINT DeleteBSCDimLevInGrpPVT;
Line: 1110

         select count(dim_group_id)
           into l_count
           from BSC_SYS_DIM_GROUPS_TL
           where name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
Line: 1120

           select distinct dim_group_id
            into l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
            from BSC_SYS_DIM_GROUPS_VL
            where name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name;
Line: 1143

     delete from BSC_SYS_DIM_LEVELS_BY_GROUP
      where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
       and dim_level_id = l_Dim_Grp_Rec.Bsc_Level_Id;
Line: 1147

     delete from BSC_SYS_DIM_LEVELS_BY_GROUP
      where dim_group_id = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id;
Line: 1157

        ROLLBACK TO DeleteBSCDimLevInGrpPVT;
Line: 1168

        ROLLBACK TO DeleteBSCDimLevInGrpPVT;
Line: 1179

        ROLLBACK TO DeleteBSCDimLevInGrpPVT;
Line: 1182

            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Delete_Dim_Levels_In_Group ';
Line: 1184

            x_msg_data      := 'BSC_DIMENSION_GROUPS_PVT.Delete_Dim_Levels_In_Group '||SQLERRM;
Line: 1190

        ROLLBACK TO DeleteBSCDimLevInGrpPVT;
Line: 1193

            x_msg_data      :=  x_msg_data||' -> BSC_DIMENSION_GROUPS_PVT.Delete_Dim_Levels_In_Group ';
Line: 1195

            x_msg_data      := 'BSC_DIMENSION_GROUPS_PVT.Delete_Dim_Levels_In_Group '||SQLERRM;
Line: 1199

end Delete_Dim_Levels_In_Group;
Line: 1216

  Select distinct DIM_GROUP_ID
    into v_Id
    from BSC_SYS_DIM_GROUPS_TL
    where SHORT_NAME = p_Short_Name;
Line: 1256

  l_Dim_Grp_rec.Bsc_Last_Update_Date := NVL(p_Dim_Grp_Rec.Bsc_Last_Update_Date, SYSDATE);
Line: 1258

  UPDATE bsc_sys_dim_groups_tl
  SET    name = l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
        ,source_lang = userenv('LANG')
        ,last_updated_by = NVL(l_Dim_Grp_Rec.Bsc_Last_Updated_By, p_Dim_Grp_Rec.Bsc_Last_Updated_By)
        ,last_update_date = l_Dim_Grp_rec.Bsc_Last_Update_Date
        ,last_update_login = p_Dim_Grp_Rec.Bsc_Last_Update_Login
  WHERE short_name = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name
  AND    userenv('LANG') in (LANGUAGE, SOURCE_LANG);
Line: 1324

  SELECT distinct name, dim_group_id, short_name
  INTO
    x_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
    ,x_Dim_Grp_Rec.Bsc_Dim_Level_Group_Id
    ,x_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name
    FROM BSC_SYS_DIM_GROUPS_VL
    WHERE short_name = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name;
Line: 1390

    UPDATE BSC_SYS_DIM_GROUPS_TL
    SET  NAME              = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name
        ,SOURCE_LANG       = p_Dim_Grp_Rec.Bsc_Source_Language
        ,LAST_UPDATE_DATE  = p_Dim_Grp_Rec.Bsc_Last_Update_Date
    WHERE SHORT_NAME    = p_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name
    AND LANGUAGE        = p_Dim_Grp_Rec.Bsc_Language;