APPS.AMS_USER_STATUSES_PKG SQL Statements

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

Line: 7

procedure INSERT_ROW (
  X_ROWID in OUT NOCOPY VARCHAR2,
  X_USER_STATUS_ID in NUMBER,
  X_DEFAULT_FLAG in VARCHAR2,
  X_SEEDED_FLAG in VARCHAR2,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_SYSTEM_STATUS_TYPE in VARCHAR2,
  X_SYSTEM_STATUS_CODE in VARCHAR2,
  X_ENABLED_FLAG in VARCHAR2,
  X_START_DATE_ACTIVE in DATE,
  X_END_DATE_ACTIVE in DATE,
  X_NAME in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_CREATION_DATE in DATE,
  X_CREATED_BY in NUMBER,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER,
  X_APPLICATION_ID in NUMBER DEFAULT '530'
) is
  cursor C is select ROWID from AMS_USER_STATUSES_B
    where USER_STATUS_ID = X_USER_STATUS_ID
    ;

Line: 31

  insert into AMS_USER_STATUSES_B (
    DEFAULT_FLAG,
    SEEDED_FLAG,
    USER_STATUS_ID,
    OBJECT_VERSION_NUMBER,
    SYSTEM_STATUS_TYPE,
    SYSTEM_STATUS_CODE,
    ENABLED_FLAG,
    START_DATE_ACTIVE,
    END_DATE_ACTIVE,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    APPLICATION_ID
  ) values (
    X_DEFAULT_FLAG,
    X_SEEDED_FLAG,
    X_USER_STATUS_ID,
    X_OBJECT_VERSION_NUMBER,
    X_SYSTEM_STATUS_TYPE,
    X_SYSTEM_STATUS_CODE,
    X_ENABLED_FLAG,
    X_START_DATE_ACTIVE,
    X_END_DATE_ACTIVE,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN,
    X_APPLICATION_ID
  );

Line: 65

  insert into AMS_USER_STATUSES_TL (
    USER_STATUS_ID,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    NAME,
    DESCRIPTION,
    LANGUAGE,
    SOURCE_LANG
  ) select
    X_USER_STATUS_ID,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_LOGIN,
    X_NAME,
    X_DESCRIPTION,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from AMS_USER_STATUSES_TL T
    where T.USER_STATUS_ID = X_USER_STATUS_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 103

end INSERT_ROW;

Line: 118

  cursor c is select
      DEFAULT_FLAG,
      SEEDED_FLAG,
      OBJECT_VERSION_NUMBER,
      SYSTEM_STATUS_TYPE,
      SYSTEM_STATUS_CODE,
      ENABLED_FLAG,
      START_DATE_ACTIVE,
      END_DATE_ACTIVE
    from AMS_USER_STATUSES_B
    where USER_STATUS_ID = X_USER_STATUS_ID
    for update of USER_STATUS_ID nowait;

Line: 132

  cursor c1 is select
      NAME,
      DESCRIPTION,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
    from AMS_USER_STATUSES_TL
    where USER_STATUS_ID = X_USER_STATUS_ID
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    for update of USER_STATUS_ID nowait;

Line: 145

    fnd_message.set_name('FND', 'FORM_RECORD_DELETED');

Line: 189

procedure UPDATE_ROW (
  X_USER_STATUS_ID in NUMBER,
  X_DEFAULT_FLAG in VARCHAR2,
  X_SEEDED_FLAG in VARCHAR2,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_SYSTEM_STATUS_TYPE in VARCHAR2,
  X_SYSTEM_STATUS_CODE in VARCHAR2,
  X_ENABLED_FLAG in VARCHAR2,
  X_START_DATE_ACTIVE in DATE,
  X_END_DATE_ACTIVE in DATE,
  X_NAME in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER,
  X_APPLICATION_ID in NUMBER DEFAULT '530'
) is

/*08-May-2006  mayjain  fix for bug 5166318*/
cursor count_def_flag (P_SYSTEM_STATUS_TYPE VARCHAR2, P_SYSTEM_STATUS_CODE VARCHAR2)
IS
select count(1)
from AMS_USER_STATUSES_B
where SYSTEM_STATUS_TYPE = P_SYSTEM_STATUS_TYPE and
SYSTEM_STATUS_CODE = P_SYSTEM_STATUS_CODE and
ENABLED_FLAG = 'Y' and
DEFAULT_FLAG = 'Y' and
SEEDED_FLAG <> 'Y';

Line: 241

  update AMS_USER_STATUSES_B set
    DEFAULT_FLAG = l_default_flag, /*08-May-2006  mayjain  fix for bug 5166318*/
    SEEDED_FLAG = X_SEEDED_FLAG,
    OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
    SYSTEM_STATUS_TYPE = X_SYSTEM_STATUS_TYPE,
    SYSTEM_STATUS_CODE = X_SYSTEM_STATUS_CODE,
    ENABLED_FLAG = X_ENABLED_FLAG,
    START_DATE_ACTIVE = X_START_DATE_ACTIVE,
    END_DATE_ACTIVE = X_END_DATE_ACTIVE,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
    APPLICATION_ID = X_APPLICATION_ID
  where USER_STATUS_ID = X_USER_STATUS_ID;

Line: 260

  update AMS_USER_STATUSES_TL set
    NAME = X_NAME,
    DESCRIPTION = X_DESCRIPTION,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
    SOURCE_LANG = userenv('LANG')
  where USER_STATUS_ID = X_USER_STATUS_ID
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 273

end UPDATE_ROW;

Line: 275

procedure DELETE_ROW (
  X_USER_STATUS_ID in NUMBER
) is
begin
  delete from AMS_USER_STATUSES_TL
  where USER_STATUS_ID = X_USER_STATUS_ID;

Line: 286

  delete from AMS_USER_STATUSES_B
  where USER_STATUS_ID = X_USER_STATUS_ID;

Line: 292

end DELETE_ROW;

Line: 297

  delete from AMS_USER_STATUSES_TL T
  where not exists
    (select NULL
    from AMS_USER_STATUSES_B B
    where B.USER_STATUS_ID = T.USER_STATUS_ID
    );

Line: 304

  update AMS_USER_STATUSES_TL T set (
      NAME,
      DESCRIPTION
    ) = (select
      B.NAME,
      B.DESCRIPTION
    from AMS_USER_STATUSES_TL B
    where B.USER_STATUS_ID = T.USER_STATUS_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.USER_STATUS_ID,
      T.LANGUAGE
  ) in (select
      SUBT.USER_STATUS_ID,
      SUBT.LANGUAGE
    from AMS_USER_STATUSES_TL SUBB, AMS_USER_STATUSES_TL SUBT
    where SUBB.USER_STATUS_ID = SUBT.USER_STATUS_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.NAME <> SUBT.NAME
      or (SUBB.NAME is null and SUBT.NAME is not null)
      or (SUBB.NAME is not null and SUBT.NAME is null)
      or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
      or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
      or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
  ));

Line: 330

  insert into AMS_USER_STATUSES_TL (
    USER_STATUS_ID,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    NAME,
    DESCRIPTION,
    LANGUAGE,
    SOURCE_LANG
  ) select
    B.USER_STATUS_ID,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATED_BY,
    B.CREATION_DATE,
    B.CREATED_BY,
    B.LAST_UPDATE_LOGIN,
    B.NAME,
    B.DESCRIPTION,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG
  from AMS_USER_STATUSES_TL B, FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  and not exists
    (select NULL
    from AMS_USER_STATUSES_TL T
    where T.USER_STATUS_ID = B.USER_STATUS_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 372

    update AMS_USER_STATUSES_TL set
       name = nvl(x_name, name),
       description = nvl(x_description, description),
       source_lang = userenv('LANG'),
       last_update_date = sysdate,
       last_updated_by = decode(x_owner, 'SEED', 1, 0),
       last_update_login = 0
    where  user_status_id = x_user_status_id
    and      userenv('LANG') in (language, source_lang);

Line: 387

      by comparing last updated by value to be
          SEED/DATAMERGE(1), or
          INITIAL SETUP/ORACLE (2), or
          SYSTEM ADMINISTRATOR (0).or
    2) modify the whole data when custom_mode is 'FORCE'
    3) if the data in db is modified by customer, which can be found by
      by comparing last updated by value to be not of 0,1,2, then
        in that case modify only the user unexposed data with last updated by as 3 to
        distinguish that data is updated by patch.
*/
procedure LOAD_ROW (
  X_USER_STATUS_ID		in NUMBER,
  X_DEFAULT_FLAG		in VARCHAR2 DEFAULT 'N',
  X_SEEDED_FLAG			in VARCHAR2 DEFAULT 'Y',
  X_OBJECT_VERSION_NUMBER	in NUMBER,
  X_SYSTEM_STATUS_TYPE		in VARCHAR2,
  X_SYSTEM_STATUS_CODE		in VARCHAR2,
  X_ENABLED_FLAG		in VARCHAR2 DEFAULT 'Y',
  X_START_DATE_ACTIVE		in DATE,
  X_END_DATE_ACTIVE		in DATE,
  X_NAME			in VARCHAR2,
  X_DESCRIPTION			in VARCHAR2,
  X_OWNER			in VARCHAR2,
  X_APPLICATION_ID		in NUMBER DEFAULT '530',
  X_CUSTOM_MODE                 in VARCHAR2
  ) IS



l_user_id   number := 1;

Line: 424

  select object_version_number
  from    AMS_USER_STATUSES_B
  where  user_status_id =  X_USER_STATUS_ID;

Line: 429

  select 'x'
  from    AMS_USER_STATUSES_B
  where  user_status_id =  X_USER_STATUS_ID;

Line: 434

   select AMS_USER_STATUSES_B_S.nextval
   from dual;

Line: 438

  select last_updated_by, nvl(object_version_number,1)
  from   AMS_USER_STATUSES_B
  where  user_status_id =  X_USER_STATUS_ID;

Line: 468

    AMS_USER_STATUSES_PKG.INSERT_ROW(
    X_ROWID			=> l_row_id,
    X_USER_STATUS_ID		=> l_user_status_id,
    X_DEFAULT_FLAG		=> X_DEFAULT_FLAG,
    X_SEEDED_FLAG		=> X_SEEDED_FLAG,
    X_OBJECT_VERSION_NUMBER	=> l_obj_verno,
    X_SYSTEM_STATUS_TYPE	=> X_SYSTEM_STATUS_TYPE,
    X_SYSTEM_STATUS_CODE	=> X_SYSTEM_STATUS_CODE,
    X_ENABLED_FLAG		=> X_ENABLED_FLAG,
    X_START_DATE_ACTIVE		=> X_START_DATE_ACTIVE,
    X_END_DATE_ACTIVE		=> X_END_DATE_ACTIVE,
    X_NAME			=> X_NAME,
    X_DESCRIPTION		=> X_DESCRIPTION,
    X_CREATION_DATE		=> SYSDATE,
    X_CREATED_BY		=> l_user_id,
    X_LAST_UPDATE_DATE		=> SYSDATE,
    X_LAST_UPDATED_BY		=> l_user_id,
    X_LAST_UPDATE_LOGIN		=> 0,
    X_APPLICATION_ID		=> X_APPLICATION_ID);

Line: 501

      AMS_USER_STATUSES_PKG.UPDATE_ROW(
         X_USER_STATUS_ID               => l_user_status_id,
         X_OBJECT_VERSION_NUMBER        => l_obj_verno + 1,
         X_DEFAULT_FLAG                 => X_DEFAULT_FLAG,
         X_SEEDED_FLAG                  => X_SEEDED_FLAG,
         X_SYSTEM_STATUS_TYPE           => X_SYSTEM_STATUS_TYPE,
         X_SYSTEM_STATUS_CODE           => X_SYSTEM_STATUS_CODE,
         X_ENABLED_FLAG                 => X_ENABLED_FLAG,
         X_START_DATE_ACTIVE            => X_START_DATE_ACTIVE,
         X_END_DATE_ACTIVE              => X_END_DATE_ACTIVE,
         X_NAME	                        => X_NAME,
         X_DESCRIPTION                  => X_DESCRIPTION,
         X_LAST_UPDATE_DATE             => SYSDATE,
         X_LAST_UPDATED_BY              => l_user_id,
         X_LAST_UPDATE_LOGIN            => 0,
         X_APPLICATION_ID               => X_APPLICATION_ID);