APPS.AMS_LISTSOURCETYPE_PVT SQL Statements

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

Line: 119

      SELECT ams_list_src_types_s.NEXTVAL
      FROM   dual;

Line: 123

      SELECT 1
      FROM   ams_list_src_types
      WHERE list_source_type_id  = x_id;

Line: 195

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

Line: 197

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

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

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

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

Line: 368

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

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

Line: 391

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

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

Line: 404

   SAVEPOINT Delete_ListSourceType;

Line: 427

   open c_delete_sources  ;

Line: 429

       fetch c_delete_sources  into l_x;

Line: 430

       exit when  c_delete_sources%notfound;

Line: 433

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

Line: 438

   close c_delete_sources  ;

Line: 442

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

Line: 466

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

Line: 471

   DELETE FROM ams_list_src_types_tl
   WHERE list_source_type_id = p_list_source_type_id
   ;

Line: 489

   DELETE FROM ams_list_src_fields
   WHERE list_source_type_id = p_list_source_type_id;

Line: 493

   DELETE FROM ams_list_src_type_assocs
   WHERE master_source_type_id = p_list_source_type_id;

Line: 510

      DELETE FROM ams_dm_targets_b
      WHERE target_id = l_target_id;

Line: 513

      DELETE FROM ams_dm_targets_tl
      WHERE target_id = l_target_id;

Line: 534

      ROLLBACK TO Delete_ListSourceType;

Line: 542

      ROLLBACK TO Delete_ListSourceType;

Line: 550

      ROLLBACK TO Delete_ListSourceType;

Line: 562

END Delete_ListSourceType;

Line: 609

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

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

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

Line: 733

   SAVEPOINT Update_ListSourceType;

Line: 759

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

Line: 775

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

Line: 801

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

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

      ROLLBACK TO Update_ListSourceType;

Line: 876

      ROLLBACK TO Update_ListSourceType;

Line: 884

      ROLLBACK TO Update_ListSourceType;

Line: 897

END Update_ListSourceType;

Line: 1030

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

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

Line: 1177

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

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

Line: 1245

   x_listsrctype_rec.last_update_date := FND_API.g_miss_date;

Line: 1246

   x_listsrctype_rec.last_updated_by := FND_API.g_miss_num;

Line: 1249

   x_listsrctype_rec.last_update_login := FND_API.g_miss_num;

Line: 1285

      SELECT *
      FROM   ams_list_src_types_vl
      WHERE  list_source_type_id = p_listsrctype_rec.list_source_type_id
      ;

Line: 1449

      SELECT 1
      FROM   ams_list_src_types_vl
      WHERE  list_source_name = p_name;

Line: 1453

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

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

      FETCH c_name_update INTO l_valid_flag;

Line: 1493

      CLOSE c_name_update;

Line: 1683

      /* SELECT DISTINCT column_name, data_type  -- Fix for bug 15876260
      FROM   sys.all_tab_columns
      WHERE  table_name = p_source_name
      AND    data_type IN ('NUMBER', 'VARCHAR2') ; */

Line: 1688

      SELECT column_name, data_type
      FROM   user_synonyms syn, dba_tab_columns col
      WHERE  syn.synonym_name = p_source_name
        AND col.owner = syn.table_owner
        AND col.table_name = syn.table_name
	AND data_type IN ('NUMBER', 'VARCHAR2')
   order by col.column_id;