APPS.BSC_SCORECARD_PVT SQL Statements

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

Line: 25

 |                        1. Modified Update Query for  BSC_TABS_TL, BSC_TAB_CSF_TL     |
 | 30-APR-2003 PWALI  Bug #2926199                                                      |
 |                    1. Modified Retrieve_Tab(), to change the Query filter            |
 | 13-MAY-2003 PWALI  Bug #2942895, SQL BIND COMPLIANCE                                 |
 | 18-MAY-04          adrao Modified PL/SQL records and CRUD to accept SHORT_NAME       |
 | 02-SEP-04            ashankar fix for the bug 3866577                                |
 | 28-OCT-04   wleung modified delete_tab() adding delete_function() logic enh 3934298  |
 | 29-Mar-2005 kyadamak bug#4268439
 |       30-May-2007  ashankar ER#TGSS 5844382                                          |
 +======================================================================================+
*/
G_PKG_NAME              CONSTANT        varchar2(30) := 'BSC_SCORECARD_PVT';

Line: 52

SELECT language_code
FROM   fnd_languages
WHERE  installed_flag IN ('I','B');

Line: 63

    SELECT COUNT(1) INTO l_Count
    FROM   BSC_TABS_B T
    WHERE  T.TAB_ID = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 76

        SELECT COUNT(1) INTO l_Count
        FROM   BSC_TABS_B T
        WHERE  T.SHORT_NAME = p_Bsc_Tab_Entity_Rec.Bsc_Short_Name;

Line: 97

  INSERT INTO BSC_TABS_B( TAB_ID
                         ,KPI_MODEL
                         ,BSC_MODEL
                         ,CROSS_MODEL
                         ,DEFAULT_MODEL
                         ,ZOOM_FACTOR
                         ,CREATED_BY
                         ,CREATION_DATE
                         ,LAST_UPDATED_BY
                         ,LAST_UPDATE_DATE
                         ,LAST_UPDATE_LOGIN
                         ,TAB_INDEX
                         ,PARENT_TAB_ID
                         ,OWNER_ID
                         ,SHORT_NAME)
                  VALUES( p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
                         ,p_Bsc_Tab_Entity_Rec.Bsc_Kpi_Model
                         ,p_Bsc_Tab_Entity_Rec.Bsc_Bsc_Model
                         ,p_Bsc_Tab_Entity_Rec.Bsc_Cross_Model
                         ,p_Bsc_Tab_Entity_Rec.Bsc_Default_Model
                         ,p_Bsc_Tab_Entity_Rec.Bsc_Zoom_Factor
                         ,p_Bsc_Tab_Entity_Rec.Bsc_Created_By
                         ,SYSDATE
                         ,p_Bsc_Tab_Entity_Rec.Bsc_Last_Updated_By
                         ,SYSDATE
                         ,p_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Login
                         ,p_Bsc_Tab_Entity_Rec.Bsc_Tab_Index
                         ,p_Bsc_Tab_Entity_Rec.Bsc_Parent_Tab_Id
                         ,p_Bsc_Tab_Entity_Rec.Bsc_Owner_Id
                         ,p_Bsc_Tab_Entity_Rec.Bsc_Short_Name);

Line: 140

  INSERT INTO BSC_TABS_TL( TAB_ID
                           ,LANGUAGE
                           ,SOURCE_LANG
                           ,NAME
                           ,HELP
                           ,ADDITIONAL_INFO)
                     VALUES( p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
                            ,l_Language
                            ,USERENV('LANG')
                            ,p_Bsc_Tab_Entity_Rec.Bsc_Tab_Name
                            ,p_Bsc_Tab_Entity_Rec.Bsc_Tab_Help
                            ,p_Bsc_Tab_Entity_Rec.Bsc_Tab_Info);

Line: 158

  INSERT INTO BSC_TAB_CSF_B(  TAB_ID
                             ,CSF_ID
                             ,CSF_TYPE
                             ,INTERMEDIATE_FLAG)
                      VALUES( p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
                             ,p_Bsc_Tab_Entity_Rec.Bsc_Csf_Id
                             ,p_Bsc_Tab_Entity_Rec.Bsc_Csf_Type
                             ,p_Bsc_Tab_Entity_Rec.Bsc_Intermediate_Flag);

Line: 178

   INSERT INTO BSC_TAB_CSF_TL(  TAB_ID
                               ,CSF_ID
                               ,LANGUAGE
                               ,SOURCE_LANG
                               ,NAME
                               ,HELP)
                            VALUES( p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
                                   ,p_Bsc_Tab_Entity_Rec.Bsc_Csf_Id
                                   ,l_language
                                   ,USERENV('LANG')
                                   ,p_Bsc_Tab_Entity_Rec.Bsc_Tab_Name
                                   ,p_Bsc_Tab_Entity_Rec.Bsc_Tab_Help);

Line: 257

  SELECT DISTINCT A.KPI_MODEL
                 ,A.BSC_MODEL
                 ,A.CROSS_MODEL
                 ,A.DEFAULT_MODEL
                 ,A.ZOOM_FACTOR
                 ,A.CREATED_BY
                 ,A.CREATION_DATE
                 ,A.LAST_UPDATED_BY
                 ,A.LAST_UPDATE_DATE
                 ,A.LAST_UPDATE_LOGIN
                 ,A.TAB_INDEX
                 ,A.PARENT_TAB_ID
                 ,A.OWNER_ID
                 ,A.SHORT_NAME
                 ,B.NAME
                 ,B.HELP
                 ,B.ADDITIONAL_INFO
                 ,C.CSF_ID
                 ,C.CSF_TYPE
                 ,C.INTERMEDIATE_FLAG
                 ,D.CSF_ID
            INTO  x_Bsc_Tab_Entity_Rec.Bsc_Kpi_Model
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Bsc_Model
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Cross_Model
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Default_Model
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Zoom_Factor
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Created_By
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Creation_Date
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Last_Updated_By
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Date
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Login
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Tab_Index
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Parent_Tab_Id
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Owner_Id
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Short_Name
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Tab_Name
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Tab_Help
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Tab_Info
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Csf_Id
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Csf_Type
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Intermediate_Flag
                 ,x_Bsc_Tab_Entity_Rec.Bsc_Csf_Id
            FROM  BSC_TABS_B A
                 ,BSC_TABS_TL B
                 ,BSC_TAB_CSF_B C
                 ,BSC_TAB_CSF_TL D
           WHERE A.TAB_ID   = B.TAB_ID
             AND A.TAB_ID   = C.TAB_ID
             AND C.TAB_ID   = D.TAB_ID
             AND A.TAB_ID   = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
             AND B.LANGUAGE = USERENV('LANG')
             AND D.LANGUAGE = USERENV('LANG');

Line: 352

procedure Update_Tab(
  p_commit              IN      varchar2 := FND_API.G_FALSE
 ,p_Bsc_Tab_Entity_Rec  IN      BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
 ,x_return_status       OUT NOCOPY     varchar2
 ,x_msg_count           OUT NOCOPY     number
 ,x_msg_data            OUT NOCOPY     varchar2
) is

l_Bsc_Tab_Entity_Rec        BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec;

Line: 369

   SAVEPOINT UpdateBSCTabPVT;

Line: 428

  if p_Bsc_Tab_Entity_Rec.Bsc_Last_Updated_By is not null then
    l_Bsc_Tab_Entity_Rec.Bsc_Last_Updated_By := p_Bsc_Tab_Entity_Rec.Bsc_Last_Updated_By;

Line: 431

  if p_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Date is not null then
    l_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Date := p_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Date;

Line: 434

  if p_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Login is not null then
    l_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Login := p_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Login;

Line: 478

  update BSC_TABS_B
     set  kpi_model = l_Bsc_Tab_Entity_Rec.Bsc_Kpi_Model
         ,bsc_model = l_Bsc_Tab_Entity_Rec.Bsc_Bsc_Model
         ,cross_model = l_Bsc_Tab_Entity_Rec.Bsc_Cross_Model
         ,default_model = l_Bsc_Tab_Entity_Rec.Bsc_Default_Model
         ,zoom_factor = l_Bsc_Tab_Entity_Rec.Bsc_Zoom_Factor
         ,created_by = l_Bsc_Tab_Entity_Rec.Bsc_Created_By
         ,creation_date = l_Bsc_Tab_Entity_Rec.Bsc_Creation_Date
         ,last_updated_by = l_Bsc_Tab_Entity_Rec.Bsc_Last_Updated_By
         ,last_update_date = l_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Date
         ,last_update_login = l_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Login
         ,tab_index = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Index
         ,parent_tab_id = l_Bsc_Tab_Entity_Rec.Bsc_Parent_Tab_Id
         ,owner_id = l_Bsc_Tab_Entity_Rec.Bsc_Owner_Id
   where tab_id = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 494

  update BSC_TABS_TL
     set  name = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Name
         ,help = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Help
         ,ADDITIONAL_INFO = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Info
         ,SOURCE_LANG     = userenv('LANG')
   where tab_id = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
     and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);

Line: 502

  update BSC_TAB_CSF_B
     set  csf_id = l_Bsc_Tab_Entity_Rec.Bsc_Csf_Id
         ,csf_type = l_Bsc_Tab_Entity_Rec.Bsc_Csf_Type
         ,intermediate_flag = l_Bsc_Tab_Entity_Rec.Bsc_Intermediate_Flag
   where tab_id = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 508

  update BSC_TAB_CSF_TL
     set  csf_id = l_Bsc_Tab_Entity_Rec.Bsc_Csf_Id
         ,name = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Name
         ,help = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Help
         ,SOURCE_LANG     = userenv('LANG')
   where tab_id = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
     and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);

Line: 532

        ROLLBACK TO UpdateBSCTabPVT;

Line: 542

        ROLLBACK TO UpdateBSCTabPVT;

Line: 552

        ROLLBACK TO UpdateBSCTabPVT;

Line: 555

            x_msg_data      :=  x_msg_data||' -> BSC_SCORECARD_PVT.Update_Tab ';

Line: 557

            x_msg_data      :=  SQLERRM||' at BSC_SCORECARD_PVT.Update_Tab ';

Line: 562

        ROLLBACK TO UpdateBSCTabPVT;

Line: 565

            x_msg_data      :=  x_msg_data||' -> BSC_SCORECARD_PVT.Update_Tab ';

Line: 567

            x_msg_data      :=  SQLERRM||' at BSC_SCORECARD_PVT.Update_Tab ';

Line: 572

end Update_Tab;

Line: 577

procedure Delete_Tab(
  p_commit              IN          VARCHAR2 := FND_API.G_FALSE
 ,p_Bsc_Tab_Entity_Rec  IN          BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
 ,x_return_status       OUT NOCOPY  VARCHAR2
 ,x_msg_count           OUT NOCOPY  NUMBER
 ,x_msg_data            OUT NOCOPY  VARCHAR2
) is

TYPE Recdc_value        IS REF CURSOR;

Line: 594

SELECT  image_id
FROM    BSC_SYS_IMAGES
WHERE   image_id NOT IN
(   SELECT DISTINCT(image_id)
      FROM   BSC_SYS_IMAGES_MAP_TL);

Line: 601

SELECT INDICATOR
FROM   BSC_TAB_INDICATORS
WHERE  TAB_ID = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 606

  SELECT tab_id, tab_view_id
  FROM BSC_TAB_VIEWS_B
  WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 615

   SAVEPOINT DeleteBSCTabPVT;

Line: 619

     SELECT COUNT(0)
     INTO   l_count
     FROM   BSC_TABS_B
     WHERE  Tab_Id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 640

  select max(tab_index)
    into l_tab_index
    from BSC_TABS_B
   where tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 645

  l_sql := 'select tab_id ' ||
           '  from BSC_TABS_B ' ||
           ' where parent_tab_id = :1';

Line: 657

      update BSC_TABS_B
         set parent_tab_id = null
            ,tab_index = l_tab_index
       where tab_id = l_child_tab;

Line: 667

      DELETE FROM BSC_TAB_INDICATORS
      WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;*/

Line: 695

        BSC_CUSTOM_VIEW_UI_WRAPPER.delete_function( p_tab_id        => cd.tab_id
                                                   ,p_tab_view_id   => cd.tab_view_id
                                                   ,x_return_status => x_return_status
                                                   ,x_msg_count     => x_msg_count
                                                   ,x_msg_data      => x_msg_data);

Line: 706

    DELETE FROM BSC_TABS_B
    WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 709

    DELETE FROM BSC_TABS_TL
    WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 712

    DELETE FROM BSC_TAB_IND_GROUPS_B
    WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 715

    DELETE FROM BSC_TAB_IND_GROUPS_TL
    WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 718

    DELETE FROM BSC_TAB_CSF_B
    WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 721

    DELETE FROM BSC_TAB_CSF_TL
    WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 724

    DELETE FROM BSC_USER_TAB_ACCESS
    WHERE tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 727

    DELETE FROM BSC_TAB_VIEW_LABELS_B
    WHERE TAB_ID = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 730

    DELETE FROM BSC_TAB_VIEW_LABELS_TL
    WHERE TAB_ID =p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 733

    DELETE FROM BSC_TAB_VIEWS_B
    WHERE TAB_ID = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 736

    DELETE FROM BSC_TAB_VIEWS_TL
    WHERE TAB_ID = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 739

    DELETE
    FROM    BSC_SYS_IMAGES_MAP_TL
    WHERE   SOURCE_TYPE IN (1,3)
    AND     SOURCE_CODE =   p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 746

      DELETE
      FROM   BSC_SYS_IMAGES
      WHERE  IMAGE_ID   = cd.image_id;

Line: 752

    DELETE FROM BSC_SYS_COM_DIM_LEVELS
    WHERE TAB_ID = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 765

        ROLLBACK TO DeleteBSCTabPVT;

Line: 778

        ROLLBACK TO DeleteBSCTabPVT;

Line: 792

        ROLLBACK TO DeleteBSCTabPVT;

Line: 799

            x_msg_data      :=  x_msg_data||' -> BSC_SCORECARD_PVT.Delete_Tab ';

Line: 801

            x_msg_data      :=  SQLERRM||' at BSC_SCORECARD_PVT.Delete_Tab ';

Line: 806

        ROLLBACK TO DeleteBSCTabPVT;

Line: 813

            x_msg_data      :=  x_msg_data||' -> BSC_SCORECARD_PVT.Delete_Tab ';

Line: 815

            x_msg_data      :=  SQLERRM||' at BSC_SCORECARD_PVT.Delete_Tab ';

Line: 820

end Delete_Tab;

Line: 859

  insert into BSC_USER_TAB_ACCESS( responsibility_id
                                  ,tab_id
                                  ,creation_date
                                  ,created_by
                                  ,last_update_date
                                  ,last_updated_by
                                  ,last_update_login
                                  ,start_date
                                  ,end_date)
                           values( p_Bsc_Tab_Entity_Rec.Bsc_Responsibility_Id
                                  ,p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
                                  ,sysdate
                                  ,p_Bsc_Tab_Entity_Rec.Bsc_Created_By
                                  ,sysdate
                                  ,p_Bsc_Tab_Entity_Rec.Bsc_Last_Updated_By
                                  ,p_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Login
                                  ,sysdate
                                  ,p_Bsc_Tab_Entity_Rec.Bsc_Resp_End_Date);

Line: 946

   Insert_Scorecard_Grants(
     p_tab_id => p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
    ,p_user_name => FND_GLOBAL.USER_NAME);

Line: 1023

  l_sql := 'select distinct tab_id, tab_index ' ||
           '  from BSC_TABS_B ' ||
           ' where tab_index >= :1' ||
           '   and tab_id <>  :2' ||
           ' order by tab_index asc';

Line: 1040

    update BSC_TABS_B
       set tab_index = l_Bsc_Tab_Entity_Tbl(i).Bsc_Tab_Index + 1
     where tab_id = l_Bsc_Tab_Entity_Tbl(i).Bsc_Tab_Id;

Line: 1096

procedure Update_Tab_Time_Stamp(
  p_commit              IN      varchar2 := FND_API.G_FALSE
 ,p_Bsc_Tab_Entity_Rec  IN      BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
 ,x_return_status       OUT NOCOPY     varchar2
 ,x_msg_count           OUT NOCOPY     number
 ,x_msg_data            OUT NOCOPY     varchar2
) is

begin
   FND_MSG_PUB.Initialize;

Line: 1107

   SAVEPOINT UpdateBSCTabTimStmPVT;

Line: 1108

  update BSC_TABS_B
     set last_update_date = sysdate
   where tab_id = p_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;

Line: 1118

        ROLLBACK TO UpdateBSCTabTimStmPVT;

Line: 1128

        ROLLBACK TO UpdateBSCTabTimStmPVT;

Line: 1138

        ROLLBACK TO UpdateBSCTabTimStmPVT;

Line: 1141

            x_msg_data      :=  x_msg_data||' -> BSC_SCORECARD_PVT.Update_Tab_Time_Stamp ';

Line: 1143

            x_msg_data      :=  SQLERRM||' at BSC_SCORECARD_PVT.Update_Tab_Time_Stamp ';

Line: 1148

        ROLLBACK TO UpdateBSCTabTimStmPVT;

Line: 1151

            x_msg_data      :=  x_msg_data||' -> BSC_SCORECARD_PVT.Update_Tab_Time_Stamp ';

Line: 1153

            x_msg_data      :=  SQLERRM||' at BSC_SCORECARD_PVT.Update_Tab_Time_Stamp ';

Line: 1158

end Update_Tab_Time_Stamp;

Line: 1163

procedure Update_System_Time_Stamp(
  p_commit              IN      varchar2 := FND_API.G_FALSE
 ,p_Bsc_Tab_Entity_Rec  IN      BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec
 ,x_return_status       OUT NOCOPY     varchar2
 ,x_msg_count           OUT NOCOPY     number
 ,x_msg_data            OUT NOCOPY     varchar2
) is

begin
   FND_MSG_PUB.Initialize;

Line: 1174

   SAVEPOINT UpdateBSCTabSysTimStmPVT;

Line: 1175

  update BSC_SYS_INIT
     set last_update_date = sysdate
   where property_code = 'LOCK_SYSTEM';

Line: 1185

        ROLLBACK TO UpdateBSCTabSysTimStmPVT;

Line: 1195

        ROLLBACK TO UpdateBSCTabSysTimStmPVT;

Line: 1205

        ROLLBACK TO UpdateBSCTabSysTimStmPVT;

Line: 1208

            x_msg_data      :=  x_msg_data||' -> BSC_SCORECARD_PVT.Update_System_Time_Stamp ';

Line: 1210

            x_msg_data      :=  SQLERRM||' at BSC_SCORECARD_PVT.Update_System_Time_Stamp ';

Line: 1215

        ROLLBACK TO UpdateBSCTabSysTimStmPVT;

Line: 1218

            x_msg_data      :=  x_msg_data||' -> BSC_SCORECARD_PVT.Update_System_Time_Stamp ';

Line: 1220

            x_msg_data      :=  SQLERRM||' at BSC_SCORECARD_PVT.Update_System_Time_Stamp ';

Line: 1225

end Update_System_Time_Stamp;

Line: 1238

  select count(*)
    into l_count
    from BSC_TABS_TL
   where upper(name) = upper(p_Tab_Name);

Line: 1262

  select count(*)
    into l_count
    from BSC_TAB_IND_GROUPS_TL
   where upper(name) = upper(p_Kpi_Group_Name);

Line: 1286

  select count(*)
    into l_count
    from BSC_KPIS_TL
   where upper(name) = upper(p_Kpi_Name);

Line: 1318

PROCEDURE Insert_Scorecard_Grants
(
    p_tab_id        IN      NUMBER
  , p_user_name     IN      VARCHAR2
)IS

CURSOR c_BscUserPool IS
SELECT distinct usr.user_name
FROM   fnd_user_resp_groups ur,
       fnd_responsibility r,
       fnd_user  usr
WHERE  ur.responsibility_id = r.responsibility_id
AND    usr.user_id = ur.user_id
AND    ur.responsibility_application_id = r.application_id
AND    r.application_id = 271
AND    r.responsibility_key IN ('BSC_DESIGNER' ,'BSC_Manager')
AND    SYSDATE BETWEEN usr.Start_Date AND NVL(usr.End_Date, SYSDATE)
AND    SYSDATE BETWEEN r.Start_Date   AND NVL(r.End_Date, SYSDATE)
AND    SYSDATE BETWEEN ur.Start_Date  AND NVL(ur.End_Date, SYSDATE);

Line: 1423

           , x_source    =>  'BSC_SCORECARD_PVT.Insert_Scorecard_Grants'
           , x_mode      => 'I'
        );

Line: 1431

           , x_source    => 'BSC_SCORECARD_PVT.Insert_Scorecard_Grants'
           , x_mode      => 'I'
        );

Line: 1438

           , x_source    =>  'BSC_SCORECARD_PVT.Insert_Scorecard_Grants'
           , x_mode      => 'I'
        );

Line: 1442

END Insert_Scorecard_Grants;

Line: 1448

              after the scorecard is deleted.

Input   :  p_tab_id,
           p_user_name
Creator :
        ashankar 05-05-04
Note:   This API is called from VB and PMD part.. so don't change the exception block.
        Any exception raised will be logged into BSC_Messages and will be checked in
        VB part.
/***********************************************************************************/

PROCEDURE Remove_Scorecard_Grants
(
    p_tab_id        IN      NUMBER
)IS
l_success       VARCHAR2(5);

Line: 1467

SELECT DISTINCT U.USER_ID,U.USER_NAME,U.FULL_NAME
FROM   bsc_apps_users_v  U,
       FND_USER_RESP_GROUPS fug,
       FND_RESPONSIBILITY rd
WHERE  U.BSC_VALID_FLAG =1
AND    U.USER_ID = fug.USER_ID
AND    fug.RESPONSIBILITY_ID = rd.RESPONSIBILITY_ID
AND    UPPER(RESPONSIBILITY_KEY) IN ('BSC_MANAGER','BSC_DESIGNER') ;*/

Line: 1481

       FND_GRANTS_PKG.delete_grant
        (
              p_grantee_type        => BSC_GRANTEE_TYPE
            , p_object_name         => BSC_OBJECT_NAME
            , p_grantee_key         => upper(user_pool.user_name)
            , p_instance_type       => BSC_INSTANCE_TYPE
            , p_instance_pk1_value  => to_char(p_tab_id)
            , p_instance_pk2_value  => '*NULL*'
            , p_instance_pk3_value  => '*NULL*'
            , p_instance_pk4_value  => '*NULL*'
            , p_instance_pk5_value  => '*NULL*'
            , p_program_name        => BSC_PROGRAM_NAME
            , x_success             => l_success
            , x_errcode             => l_errorcode
        );

Line: 1503

    SELECT OBJECT_ID
    INTO   l_object_id
    FROM   FND_OBJECTS
    WHERE  OBJ_NAME = 'BSC_TAB';

Line: 1508

    DELETE FROM FND_GRANTS
    WHERE  OBJECT_ID  = TO_CHAR(l_object_id)
    AND INSTANCE_TYPE = 'INSTANCE'
    AND INSTANCE_PK1_VALUE = TO_CHAR(p_tab_id)
    AND  PROGRAM_NAME = 'BSC_PMD_GRANTS';