APPS.AMS_LIST_QUERIES_NEW_PKG SQL Statements

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

Line: 3

procedure INSERT_ROW (
  X_ROWID in OUT NOCOPY VARCHAR2,
  X_LIST_QUERY_ID in NUMBER,
  X_QUERY_TEMPLATE_ID in NUMBER,
  X_OWNER_USER_ID in NUMBER,
  X_QUERY_TYPE in VARCHAR2,
  X_ACT_LIST_QUERY_USED_BY_ID in NUMBER,
  X_ARC_ACT_LIST_QUERY_USED_BY in VARCHAR2,
  X_SEED_FLAG in VARCHAR2,
  X_SQL_STRING in VARCHAR2,
  X_SOURCE_OBJECT_NAME in VARCHAR2,
  X_PARENT_LIST_QUERY_ID in NUMBER,
  X_SEQUENCE_ORDER in NUMBER,
  X_PARAMETERIZED_FLAG in VARCHAR2,
  X_ADMIN_FLAG in VARCHAR2,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_TYPE in VARCHAR2,
  X_QUERY in LONG,
  X_ENABLED_FLAG in VARCHAR2,
  X_PRIMARY_KEY in VARCHAR2,
  X_PUBLIC_FLAG in VARCHAR2,
  X_COMMENTS in VARCHAR2,
  X_SECURITY_GROUP_ID in NUMBER,
  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
) is
  cursor C is select ROWID from AMS_LIST_QUERIES_ALL
    where LIST_QUERY_ID = X_LIST_QUERY_ID
    ;

Line: 38

  insert into AMS_LIST_QUERIES_ALL (
    QUERY_TEMPLATE_ID,
    OWNER_USER_ID,
    QUERY_TYPE,
    ACT_LIST_QUERY_USED_BY_ID,
    ARC_ACT_LIST_QUERY_USED_BY,
    SEED_FLAG,
    SQL_STRING,
    SOURCE_OBJECT_NAME,
    PARENT_LIST_QUERY_ID,
    SEQUENCE_ORDER,
    PARAMETERIZED_FLAG,
    ADMIN_FLAG,
    LIST_QUERY_ID,
    OBJECT_VERSION_NUMBER,
    TYPE,
    QUERY,
    ENABLED_FLAG,
    PRIMARY_KEY,
    PUBLIC_FLAG,
    COMMENTS,
    SECURITY_GROUP_ID,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN
  ) values (
    X_QUERY_TEMPLATE_ID,
    X_OWNER_USER_ID,
    X_QUERY_TYPE,
    X_ACT_LIST_QUERY_USED_BY_ID,
    X_ARC_ACT_LIST_QUERY_USED_BY,
    X_SEED_FLAG,
    X_SQL_STRING,
    X_SOURCE_OBJECT_NAME,
    X_PARENT_LIST_QUERY_ID,
    X_SEQUENCE_ORDER,
    X_PARAMETERIZED_FLAG,
    X_ADMIN_FLAG,
    X_LIST_QUERY_ID,
    X_OBJECT_VERSION_NUMBER,
    X_TYPE,
--    X_QUERY,
 --bmuthukr. Bug 5334951
--   NVL(X_QUERY,X_SQL_STRING),
--  rsatyava.Bug 5647356
     X_QUERY,
    X_ENABLED_FLAG,
    X_PRIMARY_KEY,
    X_PUBLIC_FLAG,
    X_COMMENTS,
    X_SECURITY_GROUP_ID,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN
  );

Line: 97

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

Line: 135

end INSERT_ROW;

Line: 162

  cursor c is select
      QUERY_TEMPLATE_ID,
      OWNER_USER_ID,
      QUERY_TYPE,
      ACT_LIST_QUERY_USED_BY_ID,
      ARC_ACT_LIST_QUERY_USED_BY,
      SEED_FLAG,
      SQL_STRING,
      SOURCE_OBJECT_NAME,
      PARENT_LIST_QUERY_ID,
      SEQUENCE_ORDER,
      PARAMETERIZED_FLAG,
      ADMIN_FLAG,
      OBJECT_VERSION_NUMBER,
      TYPE,
      QUERY,
      ENABLED_FLAG,
      PRIMARY_KEY,
      PUBLIC_FLAG,
      COMMENTS,
      SECURITY_GROUP_ID
    from AMS_LIST_QUERIES_ALL
    where LIST_QUERY_ID = X_LIST_QUERY_ID
    for update of LIST_QUERY_ID nowait;

Line: 188

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

Line: 201

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

Line: 268

procedure UPDATE_ROW (
  X_LIST_QUERY_ID in NUMBER,
  X_QUERY_TEMPLATE_ID in NUMBER,
  X_OWNER_USER_ID in NUMBER,
  X_QUERY_TYPE in VARCHAR2,
  X_ACT_LIST_QUERY_USED_BY_ID in NUMBER,
  X_ARC_ACT_LIST_QUERY_USED_BY in VARCHAR2,
  X_SEED_FLAG in VARCHAR2,
  X_SQL_STRING in VARCHAR2,
  X_SOURCE_OBJECT_NAME in VARCHAR2,
  X_PARENT_LIST_QUERY_ID in NUMBER,
  X_SEQUENCE_ORDER in NUMBER,
  X_PARAMETERIZED_FLAG in VARCHAR2,
  X_ADMIN_FLAG in VARCHAR2,
  X_OBJECT_VERSION_NUMBER in NUMBER,
  X_TYPE in VARCHAR2,
  X_QUERY in LONG,
  X_ENABLED_FLAG in VARCHAR2,
  X_PRIMARY_KEY in VARCHAR2,
  X_PUBLIC_FLAG in VARCHAR2,
  X_COMMENTS in VARCHAR2,
  X_SECURITY_GROUP_ID in NUMBER,
  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
) is
begin
  update AMS_LIST_QUERIES_ALL set
    QUERY_TEMPLATE_ID = X_QUERY_TEMPLATE_ID,
    OWNER_USER_ID = X_OWNER_USER_ID,
    QUERY_TYPE = X_QUERY_TYPE,
    ACT_LIST_QUERY_USED_BY_ID = X_ACT_LIST_QUERY_USED_BY_ID,
    ARC_ACT_LIST_QUERY_USED_BY = X_ARC_ACT_LIST_QUERY_USED_BY,
    SEED_FLAG = X_SEED_FLAG,
    SQL_STRING = X_SQL_STRING,
    SOURCE_OBJECT_NAME = X_SOURCE_OBJECT_NAME,
    PARENT_LIST_QUERY_ID = X_PARENT_LIST_QUERY_ID,
    SEQUENCE_ORDER = X_SEQUENCE_ORDER,
    PARAMETERIZED_FLAG = X_PARAMETERIZED_FLAG,
    ADMIN_FLAG = X_ADMIN_FLAG,
    OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
    TYPE = X_TYPE,
    --QUERY = X_QUERY,
 --bmuthukr. Bug 5334951
    QUERY = NVL(X_QUERY,X_SQL_STRING),
    ENABLED_FLAG = X_ENABLED_FLAG,
    PRIMARY_KEY = X_PRIMARY_KEY,
    PUBLIC_FLAG = X_PUBLIC_FLAG,
    COMMENTS = X_COMMENTS,
    SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
  where LIST_QUERY_ID = X_LIST_QUERY_ID;

Line: 328

  update AMS_LIST_QUERIES_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 LIST_QUERY_ID = X_LIST_QUERY_ID
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 341

end UPDATE_ROW;

Line: 344

procedure DELETE_ROW (
  X_LIST_QUERY_ID in NUMBER
) is
begin
  delete from AMS_LIST_QUERIES_TL
  where LIST_QUERY_ID = X_LIST_QUERY_ID;

Line: 355

  delete from AMS_LIST_QUERIES_ALL
  where LIST_QUERY_ID = X_LIST_QUERY_ID;

Line: 361

end DELETE_ROW;

Line: 395

   l_last_updated_by number;

Line: 398

     SELECT object_version_number, last_updated_by
     FROM   AMS_LIST_QUERIES_ALL
     WHERE  LIST_QUERY_ID =  X_LIST_QUERY_ID;

Line: 403

     SELECT 'x'
     FROM   AMS_LIST_QUERIES_ALL
     WHERE  LIST_QUERY_ID = X_LIST_QUERY_ID;

Line: 408

      SELECT AMS_LIST_QUERIES_ALL_S.NEXTVAL
      FROM DUAL;

Line: 434

      AMS_LIST_QUERIES_NEW_PKG.Insert_Row (
         X_ROWID                      => l_row_id,
         X_LIST_QUERY_ID             => l_LIST_QUERY_ID,
         X_QUERY_TEMPLATE_ID         => X_QUERY_TEMPLATE_ID,
         X_OWNER_USER_ID             => X_OWNER_USER_ID,
         X_QUERY_TYPE                => X_QUERY_TYPE,
         X_ACT_LIST_QUERY_USED_BY_ID  => X_ACT_LIST_QUERY_USED_BY_ID,
         X_ARC_ACT_LIST_QUERY_USED_BY => X_ARC_ACT_LIST_QUERY_USED_BY,
         X_SEED_FLAG                  => X_SEED_FLAG,
	 X_SQL_STRING                 =>  X_SQL_STRING,
         X_SOURCE_OBJECT_NAME          => X_SOURCE_OBJECT_NAME,
         X_PARENT_LIST_QUERY_ID        => X_PARENT_LIST_QUERY_ID,
	 X_SEQUENCE_ORDER              => X_SEQUENCE_ORDER,
         X_PARAMETERIZED_FLAG          => X_PARAMETERIZED_FLAG,
	 X_ADMIN_FLAG                  => X_ADMIN_FLAG,
         X_OBJECT_VERSION_NUMBER       => l_obj_verno,
	 X_TYPE                        =>X_TYPE,
	 X_QUERY                       =>X_QUERY,
	 X_ENABLED_FLAG                =>X_ENABLED_FLAG,
	 X_PRIMARY_KEY                 =>X_PRIMARY_KEY,
	 X_PUBLIC_FLAG                 =>null,
	 X_COMMENTS                    =>null,
 	 X_SECURITY_GROUP_ID        => 0,
	 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
      );

Line: 468

      FETCH c_obj_verno INTO l_obj_verno, l_last_updated_by;

Line: 472

   if (l_last_updated_by in (1,2,0) OR
          NVL(x_custom_mode,'PRESERVE')='FORCE') THEN

      AMS_LIST_QUERIES_NEW_PKG.Update_Row (
         X_LIST_QUERY_ID             => x_LIST_QUERY_ID,
         X_QUERY_TEMPLATE_ID         => X_QUERY_TEMPLATE_ID,
         X_OWNER_USER_ID             => X_OWNER_USER_ID,
         X_QUERY_TYPE                => X_QUERY_TYPE,
         X_ACT_LIST_QUERY_USED_BY_ID  => X_ACT_LIST_QUERY_USED_BY_ID,
         X_ARC_ACT_LIST_QUERY_USED_BY => X_ARC_ACT_LIST_QUERY_USED_BY,
         X_SEED_FLAG                  => X_SEED_FLAG,
	 X_SQL_STRING                 => X_SQL_STRING,
         X_SOURCE_OBJECT_NAME          => X_SOURCE_OBJECT_NAME,
         X_PARENT_LIST_QUERY_ID        => X_PARENT_LIST_QUERY_ID,
	 X_SEQUENCE_ORDER              => X_SEQUENCE_ORDER,
         X_PARAMETERIZED_FLAG          => X_PARAMETERIZED_FLAG,
	 X_ADMIN_FLAG                  => X_ADMIN_FLAG,
         X_OBJECT_VERSION_NUMBER       => l_obj_verno,
	 X_TYPE                        =>X_TYPE,
	 X_QUERY                       =>X_QUERY,
	 X_ENABLED_FLAG                =>X_ENABLED_FLAG,
	 X_PRIMARY_KEY                 =>X_PRIMARY_KEY,
	 X_PUBLIC_FLAG                 =>null,
	 X_COMMENTS                    =>null,
 	 X_SECURITY_GROUP_ID        => 0,
	 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
      );

Line: 511

  delete from AMS_LIST_QUERIES_TL T
  where not exists
    (select NULL
    from AMS_LIST_QUERIES_ALL B
    where B.LIST_QUERY_ID = T.LIST_QUERY_ID
    );

Line: 518

  update AMS_LIST_QUERIES_TL T set (
      NAME,
      DESCRIPTION
    ) = (select
      B.NAME,
      B.DESCRIPTION
    from AMS_LIST_QUERIES_TL B
    where B.LIST_QUERY_ID = T.LIST_QUERY_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.LIST_QUERY_ID,
      T.LANGUAGE
  ) in (select
      SUBT.LIST_QUERY_ID,
      SUBT.LANGUAGE
    from AMS_LIST_QUERIES_TL SUBB, AMS_LIST_QUERIES_TL SUBT
    where SUBB.LIST_QUERY_ID = SUBT.LIST_QUERY_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.NAME <> SUBT.NAME
      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: 542

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

Line: 582

  cursor c_last_updated_by is
	  select last_updated_by
	  FROM AMS_LIST_QUERIES_TL
	  where  LIST_QUERY_ID =  x_LIST_QUERY_ID
	  and  USERENV('LANG') = LANGUAGE;

Line: 588

l_last_updated_by number;

Line: 593

     open c_last_updated_by;

Line: 594

     fetch c_last_updated_by into l_last_updated_by;

Line: 595

     close c_last_updated_by;

Line: 597

     if (l_last_updated_by in (1,2,0) OR
            NVL(x_custom_mode,'PRESERVE')='FORCE') THEN

	    update AMS_LIST_QUERIES_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, 'ORACLE',2, 'SYSADMIN',0, -1),
	       last_update_login = 0
	    where  LIST_QUERY_ID = X_LIST_QUERY_ID
	    and      userenv('LANG') in (language, source_lang);