APPS.AMS_LIST_SRC_TYPES_PKG SQL Statements

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

Line: 8

PROCEDURE insert_row (
  x_rowid IN OUT NOCOPY VARCHAR2,
  x_list_source_type_id IN NUMBER,
  x_object_version_number IN NUMBER,
  x_list_source_name IN VARCHAR2,
  x_list_source_type IN VARCHAR2,
  x_source_type_code IN VARCHAR2,
  x_source_object_name IN VARCHAR2,
  x_master_source_type_flag IN VARCHAR2,
  x_source_object_pk_field IN VARCHAR2,
  x_enabled_flag IN VARCHAR2,
  x_description IN VARCHAR2,
  X_JAVA_CLASS_NAME IN VARCHAR2,
  x_view_application_id          in number,
  x_ARC_ACT_SRC_USED_BY          in varchar2,
  x_SOURCE_CATEGORY              in varchar2,
  x_import_type                  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_BASED_ON_TCA_FLAG             IN varchar2
) IS
  l_import_type VARCHAR2(30);

Line: 33

  CURSOR c IS SELECT rowid FROM ams_list_src_types
    WHERE list_source_type_id = x_list_source_type_id;

Line: 36

  CURSOR c_import_type(code IN VARCHAR2) IS SELECT lookup_code FROM ams_lookups
    WHERE lookup_type = 'AMS_IMPORT_TYPE' and enabled_flag='Y'
    AND lookup_code = code;

Line: 52

  INSERT INTO ams_list_src_types (
    list_source_type_id,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    last_update_login,
    object_version_number,
    list_source_name,
    list_source_type,
    source_type_code,
    source_object_name,
    master_source_type_flag,
    source_object_pk_field,
    enabled_flag,
    description,
    java_class_name,
    view_application_id         ,
    ARC_ACT_SRC_USED_BY         ,
    SOURCE_CATEGORY             ,
    IMPORT_TYPE,
    BASED_ON_TCA_FLAG
  )
  values (
    x_list_source_type_id,
    x_last_update_date,
    x_last_updated_by,
    x_creation_date,
    x_created_by,
    x_last_update_login,
    x_object_version_number,
    x_list_source_name,
    x_list_source_type,
    x_source_type_code,
    x_source_object_name,
    x_master_source_type_flag,
    x_source_object_pk_field,
    x_enabled_flag,
    x_description,
    x_java_class_name,
    x_view_application_id          ,
    x_ARC_ACT_SRC_USED_BY          ,
    x_SOURCE_CATEGORY              ,
    x_IMPORT_TYPE,
    x_BASED_ON_TCA_FLAG
  );

Line: 98

  insert into AMS_LIST_SRC_TYPES_TL (
    LANGUAGE,
    SOURCE_LANG,
    LIST_SOURCE_NAME,
    DESCRIPTION,
    LIST_SOURCE_TYPE_ID,
    LAST_UPDATE_DATE,
    LAST_UPDATE_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN
  ) select
    l.language_code,
    userenv('LANG'),
    x_list_source_name,
    x_description,
    x_list_source_type_id,
--Modified for bug 5237401. bmuthukr
/*
    sysdate,
    FND_GLOBAL.user_id,
    sysdate,
    FND_GLOBAL.user_id,
*/
    x_last_update_date,
    x_last_updated_by,
    x_creation_date,
    x_created_by,
--
    FND_GLOBAL.conc_login_id
    from FND_LANGUAGES L
    where L.INSTALLED_FLAG in ('I', 'B')
    and not exists
    (select NULL
    from AMS_LIST_SRC_TYPES_TL T
    where T.LIST_SOURCE_TYPE_ID = x_list_source_type_id
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 145

END insert_row;

Line: 165

  CURSOR C1 IS SELECT
      object_version_number,
      list_source_name,
      list_source_type,
      source_type_code,
      source_object_name,
      master_source_type_flag,
      source_object_pk_field,
      enabled_flag,
      description,
      java_class_name,
      view_application_id          ,
      ARC_ACT_SRC_USED_BY          ,
      SOURCE_CATEGORY              ,
      IMPORT_TYPE,
      BASED_ON_TCA_FLAG
    FROM ams_list_src_types
    WHERE list_source_type_id = x_list_source_type_id
    FOR UPDATE OF list_source_type_id NOWAIT;

Line: 209

PROCEDURE update_row (
  x_list_source_type_id IN NUMBER,
  x_object_version_number IN NUMBER,
  x_list_source_name IN VARCHAR2,
  x_list_source_type IN VARCHAR2,
  x_source_type_code IN VARCHAR2,
  x_source_object_name IN VARCHAR2,
  x_master_source_type_flag IN VARCHAR2,
  x_source_object_pk_field IN VARCHAR2,
  x_enabled_flag IN VARCHAR2,
  x_description IN VARCHAR2,
  X_JAVA_CLASS_NAME IN VARCHAR2,
  x_view_application_id          in number,
  x_ARC_ACT_SRC_USED_BY          in varchar2,
  x_SOURCE_CATEGORY              in varchar2,
  x_IMPORT_TYPE                  in varchar2,
  x_last_update_date IN DATE,
  x_last_updated_by IN NUMBER,
  x_last_update_login IN NUMBER,
  x_BASED_ON_TCA_FLAG             IN varchar2
) IS
  l_import_type VARCHAR2(30);

Line: 231

  CURSOR c_import_type(code IN VARCHAR2) IS SELECT lookup_code FROM ams_lookups
    WHERE lookup_type = 'AMS_IMPORT_TYPE' and enabled_flag='Y'
    AND lookup_code = code;

Line: 247

  UPDATE ams_list_src_types SET
    object_version_number = x_object_version_number,
    list_source_name = x_list_source_name,
    list_source_type = x_list_source_type,
    source_type_code = x_source_type_code,
    source_object_name = x_source_object_name,
    master_source_type_flag = x_master_source_type_flag,
    source_object_pk_field = x_source_object_pk_field,
    enabled_flag = x_enabled_flag,
    description = x_description,
    JAVA_CLASS_NAME = x_java_class_name,
    view_application_id          = x_VIEW_APPLICATION_ID,
    ARC_ACT_SRC_USED_BY          = x_ARC_ACT_SRC_USED_BY,
    SOURCE_CATEGORY              = x_SOURCE_CATEGORY,
    IMPORT_TYPE                  = x_IMPORT_TYPE,
    last_update_date = x_last_update_date,
    last_updated_by = x_last_updated_by,
    last_update_login = x_last_update_login,
    BASED_ON_TCA_FLAG = X_BASED_ON_TCA_FLAG
  WHERE list_source_type_id = x_list_source_type_id;

Line: 268

  update AMS_LIST_SRC_TYPES_TL set
    LIST_SOURCE_NAME = x_list_source_name,
    DESCRIPTION = x_description,
    LAST_UPDATE_DATE = sysdate,
    --for bug 5237401
    -- LAST_UPDATE_BY = FND_GLOBAL.user_id,
    last_update_by = x_last_updated_by,
    LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id,
    SOURCE_LANG = userenv('LANG')
  WHERE list_source_type_id = x_list_source_type_id
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 283

END update_row;

Line: 285

PROCEDURE delete_row (
  x_list_source_type_id IN NUMBER
) IS
BEGIN
  DELETE FROM ams_list_src_types
  WHERE list_source_type_id = x_list_source_type_id;

Line: 296

END delete_row;

Line: 323

   l_last_updated_by number;

Line: 326

     SELECT object_version_number,  last_updated_by
     FROM   ams_list_src_types
     WHERE  list_source_type_id =  x_list_source_type_id;

Line: 331

     SELECT 'x'
     FROM   ams_list_src_types
     WHERE  list_source_type_id = x_list_source_type_id;

Line: 336

      SELECT ams_list_src_types_s.NEXTVAL
      FROM DUAL;

Line: 361

      ams_list_src_types_pkg.Insert_Row (
         X_ROWID                    => l_row_id,
         X_LIST_SOURCE_TYPE_ID      => l_list_source_type_id,
         X_OBJECT_VERSION_NUMBER    => l_obj_verno,
         X_LIST_SOURCE_NAME         => x_list_source_name,
         X_LIST_SOURCE_TYPE         => x_list_source_type,
         X_SOURCE_TYPE_CODE         => x_source_type_code,
         X_SOURCE_OBJECT_NAME       => x_source_object_name,
         X_MASTER_SOURCE_TYPE_FLAG  => x_master_source_type_flag,
         X_SOURCE_OBJECT_PK_FIELD   => x_source_object_pk_field,
         X_ENABLED_FLAG             => x_enabled_flag,
         X_DESCRIPTION              => x_description,
         X_JAVA_CLASS_NAME          => x_java_class_name,
         x_view_application_id      => x_VIEW_APPLICATION_ID,
         x_ARC_ACT_SRC_USED_BY      => x_ARC_ACT_SRC_USED_BY,
         x_SOURCE_CATEGORY          => x_SOURCE_CATEGORY,
	 x_IMPORT_TYPE              => x_IMPORT_TYPE,
          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_BASED_ON_TCA_FLAG        => x_BASED_ON_TCA_FLAG
      );

Line: 388

      FETCH c_obj_verno INTO l_obj_verno,l_last_updated_by;

Line: 392

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

      ams_list_src_types_pkg.Update_Row (
         X_LIST_SOURCE_TYPE_ID      => x_list_source_type_id,
         X_OBJECT_VERSION_NUMBER    => l_obj_verno,
         X_LIST_SOURCE_NAME         => x_list_source_name,
         X_LIST_SOURCE_TYPE         => x_list_source_type,
         X_SOURCE_TYPE_CODE         => x_source_type_code,
         X_SOURCE_OBJECT_NAME       => x_source_object_name,
         X_MASTER_SOURCE_TYPE_FLAG  => x_master_source_type_flag,
         X_SOURCE_OBJECT_PK_FIELD   => x_source_object_pk_field,
         X_ENABLED_FLAG             => x_enabled_flag,
         X_DESCRIPTION              => x_description,
         X_JAVA_CLASS_NAME          =>X_JAVA_CLASS_NAME,
         x_view_application_id      =>x_VIEW_APPLICATION_ID,
         x_ARC_ACT_SRC_USED_BY      =>x_ARC_ACT_SRC_USED_BY,
         x_SOURCE_CATEGORY          =>x_SOURCE_CATEGORY,
 	 x_IMPORT_TYPE              => x_IMPORT_TYPE,
         X_LAST_UPDATE_DATE         => SYSDATE,
         X_LAST_UPDATED_BY          => l_user_id,
         X_LAST_UPDATE_LOGIN        => 0,
	 x_BASED_ON_TCA_FLAG        => x_BASED_ON_TCA_FLAG
      );

Line: 424

  delete from AMS_LIST_SRC_TYPES_TL T
  where not exists
    (select NULL
    from AMS_LIST_SRC_TYPES B
    where B.LIST_SOURCE_TYPE_ID = T.LIST_SOURCE_TYPE_ID
    );

Line: 431

  update AMS_LIST_SRC_TYPES_TL T set (
      LIST_SOURCE_NAME,
      DESCRIPTION
    ) = (select
      B.LIST_SOURCE_NAME,
      B.DESCRIPTION
    from AMS_LIST_SRC_TYPES_TL B
    where B.LIST_SOURCE_TYPE_ID = T.LIST_SOURCE_TYPE_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.LIST_SOURCE_TYPE_ID,
      T.LANGUAGE
  ) in (select
      SUBT.LIST_SOURCE_TYPE_ID,
      SUBT.LANGUAGE
    from AMS_LIST_SRC_TYPES_TL SUBB, AMS_LIST_SRC_TYPES_TL SUBT
    where SUBB.LIST_SOURCE_TYPE_ID = SUBT.LIST_SOURCE_TYPE_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.LIST_SOURCE_NAME <> SUBT.LIST_SOURCE_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: 455

  insert into AMS_LIST_SRC_TYPES_TL (
    LIST_SOURCE_TYPE_ID,
    LAST_UPDATE_DATE,
    LAST_UPDATE_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    LIST_SOURCE_NAME,
    DESCRIPTION,
    LANGUAGE,
    SOURCE_LANG
  ) select /*+ ORDERED */
    B.LIST_SOURCE_TYPE_ID,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATE_BY,
    B.CREATION_DATE,
    B.CREATED_BY,
    B.LAST_UPDATE_LOGIN,
    B.LIST_SOURCE_NAME,
    B.DESCRIPTION,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG
  from AMS_LIST_SRC_TYPES_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_SRC_TYPES_TL T
    where T.LIST_SOURCE_TYPE_ID = B.LIST_SOURCE_TYPE_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 494

 cursor c_last_updated_by is
  select last_update_by
  FROM ams_list_src_types_tl
  where  list_source_type_id =  x_list_source_type_id
  and  USERENV('LANG') = LANGUAGE;

Line: 500

 l_last_updated_by number;

Line: 506

    open c_last_updated_by;

Line: 507

     fetch c_last_updated_by into l_last_updated_by;

Line: 508

     close c_last_updated_by;

Line: 510

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

    update AMS_LIST_SRC_TYPES_TL set
       list_source_name = nvl(x_list_source_name, list_source_name),
       description = nvl(x_description, description),
       source_lang = userenv('LANG'),
       last_update_date = sysdate,
       last_update_by = decode(x_owner, 'SEED', 1, 'ORACLE', 2, 'SYSADMIN', 0, -1),
       last_update_login = 0
    where  list_source_type_id = x_list_source_type_id
    and      userenv('LANG') in (language, source_lang);