APPS.AMS_LISTSOURCETYPE_PVT SQL Statements

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

Line: 117

      SELECT ams_list_src_types_s.NEXTVAL
      FROM   dual;

Line: 121

      SELECT 1
      FROM   ams_list_src_types
      WHERE list_source_type_id  = x_id;

Line: 193

   AMS_Utility_PVT.debug_message (l_full_name || ': Insert');

Line: 195

   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_type,
      source_type_code,
      source_object_name,
      master_source_type_flag,
      source_object_pk_field,
      enabled_flag,
      view_application_id,
      java_class_name,
      import_type,
      arc_act_src_used_by,
      source_category
   )
   VALUES (
      l_listsrctype_rec.list_source_type_id,
      SYSDATE,
      FND_GLOBAL.user_id,
      SYSDATE,
      FND_GLOBAL.user_id,
      FND_GLOBAL.conc_login_id,
      1,    -- object_version_number
      l_listsrctype_rec.list_source_type,
      l_listsrctype_rec.source_type_code,
      l_listsrctype_rec.source_object_name,
      l_listsrctype_rec.master_source_type_flag,
      l_listsrctype_rec.source_object_pk_field,
      -- analytics data sources cannot be enabled when created
      -- they need to have defined targets
      DECODE (l_listsrctype_rec.list_source_type, L_DATA_SOURCE_ANALYTICS, 'N', l_listsrctype_rec.enabled_flag),
      l_listsrctype_rec.view_application_id,
      l_listsrctype_rec.java_class_name,
      l_listsrctype_rec.import_type,
      l_listsrctype_rec.arc_act_src_used_by,
      l_listsrctype_rec.source_category
   );

Line: 238

  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'),
    l_listsrctype_rec.LIST_SOURCE_NAME,
    l_listsrctype_rec.DESCRIPTION,
    l_listsrctype_rec.LIST_SOURCE_TYPE_ID,
    sysdate,
    FND_GLOBAL.user_id,
    sysdate,
    FND_GLOBAL.user_id,
    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 = l_listsrctype_rec.LIST_SOURCE_TYPE_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 349

PROCEDURE Delete_ListSourceType (
   p_api_version        IN  NUMBER,
   p_init_msg_list      IN  VARCHAR2  := FND_API.g_false,
   p_commit             IN  VARCHAR2  := FND_API.g_false,
   p_validation_level   IN  NUMBER    := FND_API.g_valid_level_full,

   x_return_status      OUT NOCOPY VARCHAR2,
   x_msg_count          OUT NOCOPY NUMBER,
   x_msg_data           OUT NOCOPY VARCHAR2,

   p_list_source_type_id IN NUMBER,
   p_object_version      IN NUMBER
)
IS
   l_api_version CONSTANT NUMBER       := 1.0;

Line: 364

   l_api_name    CONSTANT VARCHAR2(30) := 'Delete_ListSourceType';

Line: 366

   cursor c_delete_sources  is
   select 'x'
   from ams_list_src_types a
   WHERE a.list_source_type_id = p_list_source_type_id
   and exists (select 'x'
               from ams_list_headers_all  b
               where b.list_source_type = a.source_type_code) ;

Line: 383

  SELECT list_source_type
    FROM ams_list_src_types t
   WHERE t.list_source_type_id = l_id;

Line: 389

  SELECT count(*)
    FROM AMS_DM_MODELS_VL m, AMS_DM_TARGETS_VL t
   WHERE m.TARGET_ID = t.TARGET_ID
     AND t.data_source_id = l_id;

Line: 396

  SELECT target_id
    FROM ams_dm_targets_vl t
   WHERE t.data_source_id = l_id;

Line: 402

   SAVEPOINT Delete_ListSourceType;

Line: 425

   open c_delete_sources  ;

Line: 427

       fetch c_delete_sources  into l_x;

Line: 428

       exit when  c_delete_sources%notfound;

Line: 431

          FND_MESSAGE.set_name ('AMS', 'AMS_API_CANNOT_DELETE');

Line: 436

   close c_delete_sources  ;

Line: 440

         FND_MESSAGE.set_name ('AMS', 'AMS_API_CANNOT_DELETE');

Line: 464

   DELETE FROM ams_list_src_types
   WHERE list_source_type_id = p_list_source_type_id
   AND   object_version_number = p_object_version
   ;

Line: 469

   DELETE FROM ams_list_src_types_tl
   WHERE list_source_type_id = p_list_source_type_id
   ;

Line: 487

   DELETE FROM ams_list_src_fields
   WHERE list_source_type_id = p_list_source_type_id;

Line: 491

   DELETE FROM ams_list_src_type_assocs
   WHERE master_source_type_id = p_list_source_type_id;

Line: 508

      DELETE FROM ams_dm_targets_b
      WHERE target_id = l_target_id;

Line: 511

      DELETE FROM ams_dm_targets_tl
      WHERE target_id = l_target_id;

Line: 532

      ROLLBACK TO Delete_ListSourceType;

Line: 540

      ROLLBACK TO Delete_ListSourceType;

Line: 548

      ROLLBACK TO Delete_ListSourceType;

Line: 560

END Delete_ListSourceType;

Line: 607

      SELECT object_version_number
      FROM   ams_list_src_types
      WHERE  list_source_type_id = p_list_source_type_id
      AND    object_version_number = p_object_version
      FOR UPDATE NOWAIT;

Line: 710

PROCEDURE Update_ListSourceType (
   p_api_version        IN  NUMBER,
   p_init_msg_list      IN  VARCHAR2  := FND_API.g_false,
   p_commit             IN  VARCHAR2  := FND_API.g_false,
   p_validation_level   IN  NUMBER    := FND_API.g_valid_level_full,

   x_return_status      OUT NOCOPY VARCHAR2,
   x_msg_count          OUT NOCOPY NUMBER,
   x_msg_data           OUT NOCOPY VARCHAR2,

   p_listsrctype_rec    IN  ListSourceType_Rec_Type
)
IS
   l_api_version CONSTANT NUMBER := 1.0;

Line: 724

   l_api_name    CONSTANT VARCHAR2(30) := 'Update_ListSourceType';

Line: 731

   SAVEPOINT Update_ListSourceType;

Line: 757

         FND_MESSAGE.set_name ('AMS', 'AMS_API_CANNOT_UPDATE');

Line: 773

         p_validation_mode    => JTF_PLSQL_API.g_update,
         x_return_status      => l_return_status
      );

Line: 799

   UPDATE ams_list_src_types
   SET
      last_update_date        = SYSDATE,
      last_updated_by         = FND_GLOBAL.user_id,
      last_update_login       = FND_GLOBAL.conc_login_id,
      object_version_number   = object_version_number + 1,
      list_source_type        = l_listsrctype_rec.list_source_type,
      source_type_code        = l_listsrctype_rec.source_type_code,
      source_object_name      = l_listsrctype_rec.source_object_name,
      master_source_type_flag = l_listsrctype_rec.master_source_type_flag,
      source_object_pk_field  = l_listsrctype_rec.source_object_pk_field,
      enabled_flag            = l_listsrctype_rec.enabled_flag,
      view_application_id     = l_listsrctype_rec.view_application_id,
      java_class_name         = l_listsrctype_rec.java_class_name,
      import_type             = l_listsrctype_rec.import_type,
      arc_act_src_used_by     = l_listsrctype_rec.arc_act_src_used_by,
      source_category         = l_listsrctype_rec.source_category
   WHERE list_source_type_id = l_listsrctype_rec.list_source_type_id
   AND   object_version_number = l_listsrctype_rec.object_version_number
   ;

Line: 828

  update AMS_LIST_SRC_TYPES_TL set
    LIST_SOURCE_NAME = l_listsrctype_rec.LIST_SOURCE_NAME,
    DESCRIPTION = l_listsrctype_rec.DESCRIPTION,
    LAST_UPDATE_DATE = sysdate,
    LAST_UPDATE_BY = FND_GLOBAL.user_id,
    LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id,
    SOURCE_LANG = userenv('LANG')
  where list_source_type_id = l_listsrctype_rec.list_source_type_id
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 866

      ROLLBACK TO Update_ListSourceType;

Line: 874

      ROLLBACK TO Update_ListSourceType;

Line: 882

      ROLLBACK TO Update_ListSourceType;

Line: 895

END Update_ListSourceType;

Line: 1028

  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: 1033

     SELECT length(nvl(TRANSLATE(code,
     '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_',
     ' '), 0))
     FROM DUAL;

Line: 1175

      SELECT list_source_type
           , source_type_code
           , source_object_name
           , master_source_type_flag
           , source_object_pk_field
           , enabled_flag
           , description
           , view_application_id
           , list_source_name
           , java_class_name
           , arc_act_src_used_by
           , source_category
           , import_type
      FROM   AMS_LIST_SRC_TYPES_VL
      WHERE  list_source_type_id = p_list_source_type_id
      ;

Line: 1194

      SELECT 1
      FROM   ams_dm_targets_b
      WHERE  data_source_id = p_list_source_type_id
      AND    active_flag = 'Y'
      ;

Line: 1243

   x_listsrctype_rec.last_update_date := FND_API.g_miss_date;

Line: 1244

   x_listsrctype_rec.last_updated_by := FND_API.g_miss_num;

Line: 1247

   x_listsrctype_rec.last_update_login := FND_API.g_miss_num;

Line: 1283

      SELECT *
      FROM   ams_list_src_types_vl
      WHERE  list_source_type_id = p_listsrctype_rec.list_source_type_id
      ;

Line: 1447

      SELECT 1
      FROM   ams_list_src_types_vl
      WHERE  list_source_name = p_name;

Line: 1451

   CURSOR c_name_update (p_name IN VARCHAR2, p_id IN NUMBER) IS
      SELECT 'Y'
      FROM   ams_list_src_types_vl
      WHERE  list_source_name = p_name
      AND    list_source_type_id <> p_id;

Line: 1487

   ELSE  -- update operations have to exclude the current record (by ID)
      -- Validate that the list_source_name is unique.
      OPEN c_name_update (p_listsrctype_rec.list_source_name, p_listsrctype_rec.list_source_type_id);

Line: 1490

      FETCH c_name_update INTO l_valid_flag;

Line: 1491

      CLOSE c_name_update;

Line: 1681

      SELECT DISTINCT column_name, data_type
      FROM   sys.all_tab_columns
      WHERE  table_name = p_source_name
      AND    data_type IN ('NUMBER', 'VARCHAR2')
      ;