DBA Data[Home] [Help]

APPS.BSC_SEC_UTILITY SQL Statements

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

Line: 16

  select '1'
  from dual
  where exists
  (select '1'
  from user_objects
  where object_name=p_level_view_name
  and object_type='VIEW');
Line: 32

    l_sql:='SELECT name FROM '||p_level_view_name||' WHERE code = :1';
Line: 52

  select
  DIM_LEVEL_INDEX,
  DIM_LEVEL_VALUE
 from bsc_user_list_access
  where RESPONSIBILITY_ID=p_resp_id
  and TAB_ID=p_tab_id
  order by DIM_LEVEL_INDEX desc;
Line: 74

/**     select
     max(DIM_LEVEL_INDEX)
    into
     l_dim_ind
   from
    bsc_sys_com_dim_levels
   where tab_id=p_tab_id;
Line: 94

select
b.LEVEL_VIEW_NAME
from
bsc_sys_com_dim_levels a,
bsc_sys_dim_levels_vl b
where
a.tab_id=p_tab_id
and a.DIM_LEVEL_INDEX=p_level_index
and a.DIM_LEVEL_ID=b.dim_level_id;
Line: 105

select
b.LEVEL_PK_COL
from
bsc_sys_com_dim_levels a,
bsc_sys_dim_levels_vl b
where a.tab_id=p_tab_id
and a.dim_level_index=p_level_index
and a.PARENT_DIM_LEVEL_ID=b.dim_level_id;
Line: 129

    l_sql:='SELECT  to_char('||l_parent_pk||') FROM '||l_level_view||' WHERE code= :1';
Line: 139

 procedure Update_tab_access (
  P_ROWID		in ROWID       := null,
 P_RESP_ID		in number,
 P_TAB_ID		in number,
 P_START_DATE	in date,
 P_END_DATE	    in date,
 P_LAST_UPDATED_BY	in NUMBER       := null,
 P_LAST_UPDATE_LOGIN	in NUMBER       := null,
 P_LAST_UPDATE_DATE	in DATE         := null,
 p_init_msg_list        IN   VARCHAR2   :=  fnd_api.g_FALSE,
 p_commit               IN   VARCHAR2   :=  fnd_api.g_FALSE,
 x_return_status        OUT NOCOPY  VARCHAR2,
 x_errorcode            OUT NOCOPY  NUMBER,
 x_msg_count            OUT NOCOPY  NUMBER,
 x_msg_data             OUT NOCOPY  VARCHAR2
) is

l_sysdate               DATE         := sysdate;
Line: 157

l_api_name           CONSTANT VARCHAR2(30)   := 'Update_tab_access';
Line: 158

l_last_updated_by    NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
Line: 159

l_last_update_login  NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
Line: 160

l_last_update_date   DATE   := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
Line: 164

select
 indicator
from
bsc_tab_indicators
where tab_id=p_tab_id;
Line: 181

      update bsc_user_tab_access
      set responsibility_id       = P_RESP_ID,
          tab_id                  = P_TAB_ID,
          start_date              = P_START_DATE,
          end_date                = P_END_DATE,
          last_updated_by         = l_last_updated_by,
          last_update_login       = l_last_update_login,
          last_update_date        = l_last_update_date
      where rowid  = P_ROWID;
Line: 191

      UPDATE bsc_user_tab_access
      SET
           start_date            = P_START_DATE,
           end_date              = P_END_DATE,
           LAST_UPDATE_DATE      = L_LAST_UPDATE_DATE,
           LAST_UPDATED_BY       = L_LAST_UPDATED_BY,
           LAST_UPDATE_LOGIN     = L_LAST_UPDATE_LOGIN
      WHERE responsibility_id    = P_RESP_ID
      AND tab_id                 = P_TAB_ID     ;
Line: 221

       insert_kpi_access (
       P_RESP_ID		=>p_resp_id,
       P_INDICATOR	=>l_indicators_rec.indicator,
       P_START_DATE	=>p_start_date,
       P_END_DATE	    =>p_end_date,
       x_return_status  =>x_return_status,
       x_errorcode     => x_errorcode,
       x_msg_count     =>  x_msg_count,
       x_msg_data     =>  x_msg_data
       ) ;
Line: 269

end Update_tab_access;
Line: 272

procedure Update_list_access (
 P_ROWID		in ROWID       := null,
 P_RESP_ID		in number,
 P_TAB_ID		in number,
 P_DIM_LEVEL_INDEX      in number,
 P_DIM_LEVEL_VALUE      in VARCHAR2,
 P_LAST_UPDATED_BY	in NUMBER       := null,
 P_LAST_UPDATE_LOGIN	in NUMBER       := null,
 P_LAST_UPDATE_DATE	in DATE         := null,
 p_init_msg_list        IN   VARCHAR2   :=  fnd_api.g_FALSE,
 p_commit               IN   VARCHAR2   :=  fnd_api.g_FALSE,
 x_return_status        OUT NOCOPY  VARCHAR2,
 x_errorcode            OUT NOCOPY  NUMBER,
 x_msg_count            OUT NOCOPY  NUMBER,
 x_msg_data             OUT NOCOPY  VARCHAR2
) is

l_sysdate               DATE         := sysdate;
Line: 290

l_api_name           CONSTANT VARCHAR2(30)   := 'Update_list_access';
Line: 291

l_last_updated_by    NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
Line: 292

l_last_update_login  NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
Line: 293

l_last_update_date   DATE   := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
Line: 303

      update bsc_user_list_access
      set responsibility_id       = P_RESP_ID,
          tab_id                  = P_TAB_ID,
          DIM_LEVEL_INDEX         = P_DIM_LEVEL_INDEX,
          DIM_LEVEL_VALUE         = P_DIM_LEVEL_VALUE,
          last_updated_by         = l_last_updated_by,
          last_update_login       = l_last_update_login,
          last_update_date        = l_last_update_date
      where rowid  = P_ROWID;
Line: 313

      UPDATE bsc_user_list_access
      SET
           DIM_LEVEL_VALUE        = P_DIM_LEVEL_VALUE,
           LAST_UPDATE_DATE      = L_LAST_UPDATE_DATE,
           LAST_UPDATED_BY       = L_LAST_UPDATED_BY,
           LAST_UPDATE_LOGIN     = L_LAST_UPDATE_LOGIN
      WHERE responsibility_id    = P_RESP_ID
      AND tab_id                 = P_TAB_ID
      and dim_level_index        = P_DIM_LEVEL_INDEX   ;
Line: 359

end update_list_access;
Line: 362

procedure insert_list_access (
 P_RESP_ID		in number,
 P_TAB_ID		in number,
 P_DIM_LEVEL_INDEX in number,
 P_DIM_LEVEL_VALUE in VARCHAR2,
 P_CREATION_DATE in date :=null,
 p_CREATED_BY in number :=null,
 P_LAST_UPDATED_BY	in NUMBER       := null,
 P_LAST_UPDATE_LOGIN	in NUMBER       := null,
 P_LAST_UPDATE_DATE	in DATE         := null,
 p_init_msg_list        IN   VARCHAR2   :=  fnd_api.g_FALSE,
 p_commit               IN   VARCHAR2   :=  fnd_api.g_FALSE,
 x_return_status        OUT NOCOPY  VARCHAR2,
 x_errorcode            OUT NOCOPY  NUMBER,
 x_msg_count            OUT NOCOPY  NUMBER,
 x_msg_data             OUT NOCOPY  VARCHAR2
) is

l_sysdate               DATE         := sysdate;
Line: 381

l_api_name           CONSTANT VARCHAR2(30)   := 'Insert_list_access';
Line: 384

l_last_updated_by    NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
Line: 385

l_last_update_login  NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
Line: 386

l_last_update_date   DATE   := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
Line: 395

    insert into bsc_user_list_access
    (
     RESPONSIBILITY_ID ,
      TAB_ID            ,
      DIM_LEVEL_INDEX    ,
     DIM_LEVEL_VALUE      ,
     CREATION_DATE      ,
     CREATED_BY         ,
     LAST_UPDATE_DATE   ,
     LAST_UPDATED_BY    ,
     LAST_UPDATE_LOGIN
    )
    values
    (
    P_RESP_ID		 ,
    P_TAB_ID		 ,
     P_DIM_LEVEL_INDEX  ,
    P_DIM_LEVEL_VALUE  ,
    l_creation_date,
     l_created_by,
    l_last_update_date,
     l_last_updated_by,
    l_last_update_login
    );
Line: 455

end insert_list_access;
Line: 461

 select 'Y'
 from dual
 where exists (select TAB_ID from bsc_sys_com_dim_levels where tab_id=p_tab_id);
Line: 481

select 'Y'
from dual
where exists
(select 'Y' from bsc_user_list_access
 where RESPONSIBILITY_ID=p_resp_id
 and TAB_ID=p_tab_id);
Line: 509

procedure insert_tab_access (
 P_RESP_ID		in number,
 P_TAB_ID		in number,
 P_START_DATE	in date,
 P_END_DATE	    in date,
 P_CREATED_BY		in NUMBER       := null,
 P_CREATION_DATE	in DATE         := null,
 P_LAST_UPDATED_BY	in NUMBER       := null,
 P_LAST_UPDATE_LOGIN	in NUMBER       := null,
 P_LAST_UPDATE_DATE	in DATE         := null,
 p_init_msg_list        IN   VARCHAR2   :=  fnd_api.g_FALSE,
 p_commit               IN   VARCHAR2   :=  fnd_api.g_FALSE,
 x_return_status        OUT NOCOPY  VARCHAR2,
 x_errorcode            OUT NOCOPY  NUMBER,
 x_msg_count            OUT NOCOPY  NUMBER,
 x_msg_data             OUT NOCOPY  VARCHAR2
) is

  l_sysdate               DATE         := sysdate;
Line: 528

  l_api_name           CONSTANT VARCHAR2(30)   := 'insert_tab_access';
Line: 531

  l_last_updated_by    NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
Line: 532

  l_last_update_login  NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
Line: 533

  l_last_update_date   DATE   := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
Line: 536

select 'Y'
 from dual
 where exists
 (select 'Y'
 from bsc_user_tab_access
  where responsibility_id=p_resp_id
  and tab_id=p_tab_id);
Line: 547

select
 indicator
from
bsc_tab_indicators
where tab_id=p_tab_id;
Line: 556

select distinct
 DIM_LEVEL_INDEX DIM_LEVEL_INDEX,
 '0' DIM_LEVEL_VALUE ---default value 'ALL'
from
bsc_sys_com_dim_levels
where tab_id=p_tab_id;
Line: 584

  Update_tab_access (
 P_RESP_ID		=>p_resp_id,
 P_TAB_ID		=>p_tab_id,
 P_START_DATE	=>p_start_date,
 P_END_DATE	    =>p_end_date,
 x_return_status =>x_return_status,
 x_errorcode     =>x_errorcode,
 x_msg_count     =>x_msg_count,
 x_msg_data      =>x_msg_data
) ;
Line: 597

       insert into bsc_user_tab_access (
        responsibility_id,
        tab_id,
        start_date,
        end_date,
        CREATED_BY,
        CREATION_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_LOGIN,
        LAST_UPDATE_DATE)
    values(
        P_resp_id,
        P_tab_id,
        P_start_date,
        P_end_date,
        L_CREATED_BY,
        L_CREATION_DATE,
        L_LAST_UPDATED_BY,
        L_LAST_UPDATE_LOGIN,
        L_LAST_UPDATE_DATE);
Line: 621

 insert_kpi_access (
 P_RESP_ID		=>p_resp_id,
 P_INDICATOR	=>l_indicators_rec.indicator,
 P_START_DATE	=>p_start_date,
 P_END_DATE	    =>p_end_date,
 x_return_status  =>x_return_status,
 x_errorcode     => x_errorcode,
 x_msg_count     =>  x_msg_count,
 x_msg_data     =>  x_msg_data
) ;
Line: 639

        insert_list_access (
        P_RESP_ID=>p_resp_id,
        P_TAB_ID=>p_tab_id	,
        P_DIM_LEVEL_INDEX=>l_comm_dim_value_rec.DIM_LEVEL_INDEX,
        P_DIM_LEVEL_VALUE=>l_comm_dim_value_rec.DIM_LEVEL_value,
        x_return_status  => x_return_status     ,
        x_errorcode =>    x_errorcode     ,
        x_msg_count =>   x_msg_count         ,
        x_msg_data  =>    x_msg_data     );
Line: 690

end insert_tab_access;
Line: 693

procedure insert_kpi_access (
 P_RESP_ID		in number,
 P_INDICATOR		in number,
 P_START_DATE	in date,
 P_END_DATE	    in date,
 P_CREATED_BY		in NUMBER       := null,
 P_CREATION_DATE	in DATE         := null,
 P_LAST_UPDATED_BY	in NUMBER       := null,
 P_LAST_UPDATE_LOGIN	in NUMBER       := null,
 P_LAST_UPDATE_DATE	in DATE         := null,
 p_init_msg_list        IN   VARCHAR2   :=  fnd_api.g_FALSE,
 p_commit               IN   VARCHAR2   :=  fnd_api.g_FALSE,
 x_return_status        OUT NOCOPY  VARCHAR2,
 x_errorcode            OUT NOCOPY  NUMBER,
 x_msg_count            OUT NOCOPY  NUMBER,
 x_msg_data             OUT NOCOPY  VARCHAR2
) is

  l_sysdate               DATE         := sysdate;
Line: 712

  l_api_name           CONSTANT VARCHAR2(30)   := 'insert_kpi_access';
Line: 715

  l_last_updated_by    NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
Line: 716

  l_last_update_login  NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
Line: 717

  l_last_update_date   DATE   := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
Line: 720

select 'Y'
 from dual
 where exists
 (select 'Y'
 from bsc_user_kpi_access
  where responsibility_id=p_resp_id
 and  indicator=p_indicator);
Line: 747

  Update_kpi_access (
 P_RESP_ID		=>p_resp_id,
 P_INDICATOR		=>p_indicator,
 P_START_DATE	=>p_start_date,
 P_END_DATE	    =>p_end_date,
 x_return_status =>x_return_status,
 x_errorcode     =>x_errorcode,
 x_msg_count     =>x_msg_count,
 x_msg_data      =>x_msg_data
) ;
Line: 760

       insert into bsc_user_kpi_access (
        responsibility_id,
        indicator,
        start_date,
        end_date,
        CREATED_BY,
        CREATION_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_LOGIN,
        LAST_UPDATE_DATE)
    values(
        P_resp_id,
        P_indicator,
        P_start_date,
        P_end_date,
        L_CREATED_BY,
        L_CREATION_DATE,
        L_LAST_UPDATED_BY,
        L_LAST_UPDATE_LOGIN,
        L_LAST_UPDATE_DATE);
Line: 817

end insert_kpi_access;
Line: 821

procedure Update_kpi_access (
  P_ROWID		in ROWID       := null,
 P_RESP_ID		in number,
 P_INDICATOR		in number,
 P_START_DATE	in date,
 P_END_DATE	    in date,
 P_LAST_UPDATED_BY	in NUMBER       := null,
 P_LAST_UPDATE_LOGIN	in NUMBER       := null,
 P_LAST_UPDATE_DATE	in DATE         := null,
 p_init_msg_list        IN   VARCHAR2   :=  fnd_api.g_FALSE,
 p_commit               IN   VARCHAR2   :=  fnd_api.g_FALSE,
 x_return_status        OUT NOCOPY  VARCHAR2,
 x_errorcode            OUT NOCOPY  NUMBER,
 x_msg_count            OUT NOCOPY  NUMBER,
 x_msg_data             OUT NOCOPY  VARCHAR2
) is

l_sysdate               DATE         := sysdate;
Line: 839

l_api_name           CONSTANT VARCHAR2(30)   := 'Update_kpi_access';
Line: 840

l_last_updated_by    NUMBER := nvl(P_LAST_UPDATED_BY, g_current_user_id);
Line: 841

l_last_update_login  NUMBER := nvl(P_LAST_UPDATE_LOGIN, g_current_login_id);
Line: 842

l_last_update_date   DATE   := nvl(P_LAST_UPDATE_DATE, l_Sysdate);
Line: 852

      update bsc_user_kpi_access
      set responsibility_id       = P_RESP_ID,
          indicator               = P_INDICATOR,
          start_date              = P_START_DATE,
          end_date                = P_END_DATE,
          last_updated_by         = l_last_updated_by,
          last_update_login       = l_last_update_login,
          last_update_date        = l_last_update_date
      where rowid  = P_ROWID;
Line: 862

      UPDATE bsc_user_kpi_access
      SET
           start_date            = P_START_DATE,
           end_date              = P_END_DATE,
           LAST_UPDATE_DATE      = L_LAST_UPDATE_DATE,
           LAST_UPDATED_BY       = L_LAST_UPDATED_BY,
           LAST_UPDATE_LOGIN     = L_LAST_UPDATE_LOGIN
      WHERE responsibility_id    = P_RESP_ID
      AND  indicator                 = P_INDICATOR    ;
Line: 908

end Update_kpi_access;
Line: 929

  delete from  bsc_user_kpi_access
  where responsibility_id=p_resp_id
  and indicator=p_indicator;