DBA Data[Home] [Help]

APPS.AMS_IMPORT_LIST_PVT SQL Statements

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

Line: 164

   FND_PROGRAM.DELETE_PROGRAM (
		program_short_name   => l_con_program_name,
      application          => G_APP_NAME
   );
Line: 177

   FND_PROGRAM.DELETE_EXECUTABLE (
		executable_short_name      => l_con_program_name,
      application                => G_APP_NAME
	);
Line: 188

	--update the headers table, need to add the export stuff
	--
	IF l_repeat_mode = G_REPEAT_NONE THEN --cancel
			l_cancel_date := SYSDATE;
Line: 214

		UPDATE AMS_IMP_LIST_HEADERS_ALL
		SET REPEAT_MODE = l_repeat_mode, CANCEL_DATE = l_cancel_date,
			 CANCEL_FLAG = l_cancel_flag, REPEAT_TIME = l_repeat_time,
			 REPEAT_INTERVAL = l_repeat_interval, REPEAT_UNIT = l_repeat_unit,
			 REPEAT_END_TIME = l_repeate_end_time, REPEAT_START_TIME = l_repeate_start_time
		WHERE IMPORT_LIST_HEADER_ID = p_obj_id;
Line: 511

	SELECT	import_list_header_id,
				last_update_date,
				last_updated_by,
				creation_date,
				created_by,
				last_update_login,
				object_version_number,
				view_application_id,
				name,
				version,
				import_type,
				owner_user_id,
				list_source_type_id,
				status_code,
				status_date,
				user_status_id,
				source_system,
				vendor_id,
				pin_id,
				org_id,
				scheduled_time,
				null,--loaded_no_of_rows,
				loaded_date,
				rows_to_skip,
				processed_rows,
				headings_flag,
				expiry_date,
				purge_date,
				description,
				keywords,
				transactional_cost,
				transactional_currency_code,
				functional_cost,
				functional_currency_code,
				terminated_by,
				enclosed_by,
				data_filename,
				process_immed_flag,
				dedupe_flag,
				attribute_category,
				attribute1,
				attribute2,
				attribute3,
				attribute4,
				attribute5,
				attribute6,
				attribute7,
				attribute8,
				attribute9,
				attribute10,
				attribute11,
				attribute12,
				attribute13,
				attribute14,
				attribute15,
				custom_setup_id,
				country,
				usage,
				number_of_records,
				data_file_name,
				b2b_flag,
				rented_list_flag,
				server_flag,
				log_file_name,
				null,--number_of_failed_records,
				null,--number_of_duplicate_records,
				enable_word_replacement_flag,
				validate_file,
				server_name,
				user_name,
				password,
				upload_flag,
				parent_imp_header_id,
				record_update_flag,
				error_threshold,
				charset
	FROM AMS_IMP_LIST_HEADERS_ALL
	WHERE IMPORT_LIST_HEADER_ID = p_import_list_header_id;
Line: 595

        SELECT user_status_id
        FROM ams_user_statuses_b
        WHERE system_status_type = c_status_type -- 'AMS_IMPORT_STATUS'
        AND system_status_code = c_status_code -- 'NEW'
        AND default_flag = c_flag; -- 'Y';
Line: 603

   SELECT FILE_TYPE
	FROM AMS_IMP_DOCUMENTS
	WHERE IMPORT_LIST_HEADER_ID = p_import_header_id;
Line: 632

	SELECT AMS_IMP_LIST_HEADERS_ALL_S.NEXTVAL INTO x_ams_import_rec.import_list_header_id
	FROM DUAL;
Line: 637

	x_ams_import_rec.last_update_date := l_current_date;
Line: 672

  update AMS_IMP_LIST_HEADERS_ALL
  set ( generate_list, number_of_instances)=
	(select  generate_list, number_of_instances
   	 from  AMS_IMP_LIST_HEADERS_ALL
     where import_list_header_id = p_import_list_header_id),
	 generated_list_name = name
  where  import_list_header_id =  x_ams_import_rec.import_list_header_id;
Line: 680

  AMS_Utility_PVT.Write_Conc_Log (' Start Duplicate_Import_List after update AMS_IMP_LIST_HEADERS_ALL ');
Line: 774

      SELECT AMS_IMP_LIST_HEADERS_ALL_s.NEXTVAL
      FROM dual;
Line: 778

      SELECT 1
      FROM AMS_IMP_LIST_HEADERS_ALL
      WHERE IMPORT_LIST_HEADER_ID = l_id;
Line: 783

      SELECT version, object_version_number, status_code, status_date,
             user_status_id, country, validate_file
      FROM AMS_IMP_LIST_HEADERS_ALL
      WHERE IMPORT_LIST_HEADER_ID = l_id;
Line: 791

   SELECT user_status_id
   FROM ams_user_statuses_b
   WHERE system_status_type = c_status_type -- 'AMS_IMPORT_STATUS'
   AND system_status_code = c_status_code -- 'NEW'
   AND default_flag = c_flag; -- 'Y';
Line: 891

                Update_Import_List(
                    p_api_version_number         => 1.0,
                    p_init_msg_list              => FND_API.G_FALSE,
                    p_commit                     => FND_API.G_FALSE,
                    p_validation_level           => FND_API.G_VALID_LEVEL_FULL,
                    x_return_status              => x_return_status,
                    x_msg_count                  => x_msg_count,
                    x_msg_data                   => x_msg_data,
                    p_ams_import_rec             => l_ams_import_rec,
                    x_object_version_number      => l_object_version_number);
Line: 1010

	  select as_import_interface_s.nextval into l_batch_id  from dual;
Line: 1014

      select as_import_interface_s.nextval into l_batch_id  from dual;
Line: 1029

      AMS_IMP_LIST_HEADERS_PKG.Insert_Row(
          px_import_list_header_id  => l_import_list_header_id,
          p_last_update_date  => SYSDATE,
          p_last_updated_by  => FND_GLOBAL.USER_ID,
          p_creation_date  => SYSDATE,
          p_created_by  => FND_GLOBAL.USER_ID,
          p_last_update_login  => FND_GLOBAL.CONC_LOGIN_ID,
          px_object_version_number  => l_object_version_number,
          p_view_application_id  => p_ams_import_rec.view_application_id,
          p_name  => p_ams_import_rec.name,
          p_version  => '1.0',--p_ams_import_rec.version,
          p_import_type  => p_ams_import_rec.import_type,
          p_owner_user_id  => p_ams_import_rec.owner_user_id,
          p_list_source_type_id  => p_ams_import_rec.list_source_type_id,
          p_status_code  => 'NEW',--p_ams_import_rec.status_code,
          p_status_date  => sysdate,--p_ams_import_rec.status_date,
          p_user_status_id  => l_user_status_id, --3001,--p_ams_import_rec.user_status_id,
          p_source_system  => p_ams_import_rec.source_system,
          p_vendor_id  => p_ams_import_rec.vendor_id,
          p_pin_id  => p_ams_import_rec.pin_id,
          px_org_id  => l_org_id,
          p_scheduled_time  => p_ams_import_rec.scheduled_time,
          p_loaded_no_of_rows  => p_ams_import_rec.loaded_no_of_rows,
          p_loaded_date  => p_ams_import_rec.loaded_date,
          p_rows_to_skip  => p_ams_import_rec.rows_to_skip,
          p_processed_rows  => p_ams_import_rec.processed_rows,
          p_headings_flag  => p_ams_import_rec.headings_flag,
          p_expiry_date  => p_ams_import_rec.expiry_date,
          p_purge_date  => p_ams_import_rec.purge_date,
          p_description  => p_ams_import_rec.description,
          p_keywords  => p_ams_import_rec.keywords,
          p_transactional_cost  => p_ams_import_rec.transactional_cost,
          p_transactional_currency_code  => p_ams_import_rec.transactional_currency_code,
          p_functional_cost  => p_ams_import_rec.functional_cost,
          p_functional_currency_code  => p_ams_import_rec.functional_currency_code,
          p_terminated_by  => l_terminated_by,
          p_enclosed_by  => l_enclosed_by,
          p_data_filename  => p_ams_import_rec.data_filename,
          p_process_immed_flag  => p_ams_import_rec.process_immed_flag,
          p_dedupe_flag  => p_ams_import_rec.dedupe_flag,
          p_attribute_category  => p_ams_import_rec.attribute_category,
          p_attribute1  => p_ams_import_rec.attribute1,
          p_attribute2  => p_ams_import_rec.attribute2,
          p_attribute3  => p_ams_import_rec.attribute3,
          p_attribute4  => p_ams_import_rec.attribute4,
          p_attribute5  => p_ams_import_rec.attribute5,
          p_attribute6  => p_ams_import_rec.attribute6,
          p_attribute7  => p_ams_import_rec.attribute7,
          p_attribute8  => p_ams_import_rec.attribute8,
          p_attribute9  => p_ams_import_rec.attribute9,
          p_attribute10  => p_ams_import_rec.attribute10,
          p_attribute11  => p_ams_import_rec.attribute11,
          p_attribute12  => p_ams_import_rec.attribute12,
          p_attribute13  => p_ams_import_rec.attribute13,
          p_attribute14  => p_ams_import_rec.attribute14,
          p_attribute15  => p_ams_import_rec.attribute15,
          p_custom_setup_id  => p_ams_import_rec.custom_setup_id,
          p_country  => l_country,
          p_usage  => p_ams_import_rec.usage,
          p_number_of_records  => p_ams_import_rec.number_of_records,
          p_data_file_name  => p_ams_import_rec.data_file_name,
          p_b2b_flag  => p_ams_import_rec.b2b_flag,
          p_rented_list_flag  => p_ams_import_rec.rented_list_flag,
          p_server_flag  => p_ams_import_rec.server_flag,
          p_log_file_name  => p_ams_import_rec.log_file_name,
          p_number_of_failed_records  => p_ams_import_rec.number_of_failed_records,
          p_number_of_duplicate_records  => p_ams_import_rec.number_of_duplicate_records,
          p_enable_word_replacement_flag  => p_ams_import_rec.enable_word_replacement_flag,
			 p_batch_id => l_batch_id,
			 p_server_name => p_ams_import_rec.server_name,
			 p_user_name   => p_ams_import_rec.user_name,
			 p_password  => l_encrpted_password, --p_ams_import_rec.password,
			 p_upload_flag	=> p_ams_import_rec.upload_flag,
			 p_parent_imp_header_id => p_ams_import_rec.parent_imp_header_id,
			 p_record_update_flag => p_ams_import_rec.record_update_flag,
		    p_error_threshold => p_ams_import_rec.error_threshold,
			 p_charset => p_ams_import_rec.charset);
Line: 1116

			UPDATE AMS_IMP_LIST_HEADERS_ALL
			SET PASSWORD = l_encrpted_password
			WHERE IMPORT_LIST_HEADER_ID = l_import_list_header_id;
Line: 1184

PROCEDURE Update_Import_List(
    p_api_version_number         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_ams_import_rec               IN    ams_import_rec_type,
    x_object_version_number      OUT NOCOPY  NUMBER
    )

 IS

CURSOR c_get_import_list(import_list_header_id NUMBER) IS
    SELECT *
    FROM  AMS_IMP_LIST_HEADERS_ALL
    WHERE import_list_header_id=p_ams_import_rec.import_list_header_id;
Line: 1206

L_API_NAME                  CONSTANT VARCHAR2(30) := 'Update_Import_List';
Line: 1217

      SAVEPOINT UPDATE_Import_List_PVT;
Line: 1247

      AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
Line: 1256

  AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
   p_token_name   => 'INFO',
 p_token_value  => 'Import_List') ;
Line: 1274

 p_token_value  => 'Last_Update_Date') ;
Line: 1310

      AMS_UTILITY_PVT.debug_message('Private API: Calling update table handler');
Line: 1314

      AMS_IMP_LIST_HEADERS_PKG.Update_Row(
          p_import_list_header_id  => p_ams_import_rec.import_list_header_id,
          p_last_update_date  => SYSDATE,
          p_last_updated_by  => FND_GLOBAL.USER_ID,
          p_last_update_login  => FND_GLOBAL.CONC_LOGIN_ID,
          p_object_version_number  => p_ams_import_rec.object_version_number,
          p_view_application_id  => p_ams_import_rec.view_application_id,
          p_name  => p_ams_import_rec.name,
          p_version  => p_ams_import_rec.version,
          p_import_type  => p_ams_import_rec.import_type,
          p_owner_user_id  => p_ams_import_rec.owner_user_id,
          p_list_source_type_id  => p_ams_import_rec.list_source_type_id,
          p_status_code  => p_ams_import_rec.status_code,
          p_status_date  => p_ams_import_rec.status_date,
          p_user_status_id  => p_ams_import_rec.user_status_id,
          p_source_system  => p_ams_import_rec.source_system,
          p_vendor_id  => p_ams_import_rec.vendor_id,
          p_pin_id  => p_ams_import_rec.pin_id,
          p_org_id  => p_ams_import_rec.org_id,
          p_scheduled_time  => p_ams_import_rec.scheduled_time,
          p_loaded_no_of_rows  => p_ams_import_rec.loaded_no_of_rows,
          p_loaded_date  => p_ams_import_rec.loaded_date,
          p_rows_to_skip  => p_ams_import_rec.rows_to_skip,
          p_processed_rows  => p_ams_import_rec.processed_rows,
          p_headings_flag  => p_ams_import_rec.headings_flag,
          p_expiry_date  => p_ams_import_rec.expiry_date,
          p_purge_date  => p_ams_import_rec.purge_date,
          p_description  => p_ams_import_rec.description,
          p_keywords  => p_ams_import_rec.keywords,
          p_transactional_cost  => p_ams_import_rec.transactional_cost,
          p_transactional_currency_code  => p_ams_import_rec.transactional_currency_code,
          p_functional_cost  => p_ams_import_rec.functional_cost,
          p_functional_currency_code  => p_ams_import_rec.functional_currency_code,
          p_terminated_by  => p_ams_import_rec.terminated_by,
          p_enclosed_by  => p_ams_import_rec.enclosed_by,
          p_data_filename  => p_ams_import_rec.data_filename,
          p_process_immed_flag  => p_ams_import_rec.process_immed_flag,
          p_dedupe_flag  => p_ams_import_rec.dedupe_flag,
          p_attribute_category  => p_ams_import_rec.attribute_category,
          p_attribute1  => p_ams_import_rec.attribute1,
          p_attribute2  => p_ams_import_rec.attribute2,
          p_attribute3  => p_ams_import_rec.attribute3,
          p_attribute4  => p_ams_import_rec.attribute4,
          p_attribute5  => p_ams_import_rec.attribute5,
          p_attribute6  => p_ams_import_rec.attribute6,
          p_attribute7  => p_ams_import_rec.attribute7,
          p_attribute8  => p_ams_import_rec.attribute8,
          p_attribute9  => p_ams_import_rec.attribute9,
          p_attribute10  => p_ams_import_rec.attribute10,
          p_attribute11  => p_ams_import_rec.attribute11,
          p_attribute12  => p_ams_import_rec.attribute12,
          p_attribute13  => p_ams_import_rec.attribute13,
          p_attribute14  => p_ams_import_rec.attribute14,
          p_attribute15  => p_ams_import_rec.attribute15,
          p_custom_setup_id  => p_ams_import_rec.custom_setup_id,
          p_country  => p_ams_import_rec.country,
          p_usage  => p_ams_import_rec.usage,
          p_number_of_records  => p_ams_import_rec.number_of_records,
          p_data_file_name  => p_ams_import_rec.data_file_name,
          p_b2b_flag  => p_ams_import_rec.b2b_flag,
          p_rented_list_flag  => p_ams_import_rec.rented_list_flag,
          p_server_flag  => p_ams_import_rec.server_flag,
          p_log_file_name  => p_ams_import_rec.log_file_name,
          p_number_of_failed_records  => p_ams_import_rec.number_of_failed_records,
          p_number_of_duplicate_records  => p_ams_import_rec.number_of_duplicate_records,
          p_enable_word_replacement_flag  => p_ams_import_rec.enable_word_replacement_flag,
	       p_validate_file => p_ams_import_rec.validate_file,
			 p_record_update_flag => p_ams_import_rec.record_update_flag,
		    p_error_threshold => p_ams_import_rec.error_threshold);
Line: 1412

     ROLLBACK TO UPDATE_Import_List_PVT;
Line: 1422

     ROLLBACK TO UPDATE_Import_List_PVT;
Line: 1432

     ROLLBACK TO UPDATE_Import_List_PVT;
Line: 1444

End Update_Import_List;
Line: 1447

PROCEDURE Delete_Import_List(
    p_api_version_number         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_import_list_header_id                   IN  NUMBER,
    p_object_version_number      IN   NUMBER
    )

 IS
L_API_NAME                  CONSTANT VARCHAR2(30) := 'Delete_Import_List';
Line: 1466

      SAVEPOINT DELETE_Import_List_PVT;
Line: 1499

      AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
Line: 1503

      AMS_IMP_LIST_HEADERS_PKG.Delete_Row(
          p_IMPORT_LIST_HEADER_ID  => p_IMPORT_LIST_HEADER_ID);
Line: 1534

     ROLLBACK TO DELETE_Import_List_PVT;
Line: 1544

     ROLLBACK TO DELETE_Import_List_PVT;
Line: 1554

     ROLLBACK TO DELETE_Import_List_PVT;
Line: 1566

End Delete_Import_List;
Line: 1590

   SELECT IMPORT_LIST_HEADER_ID
   FROM AMS_IMP_LIST_HEADERS_ALL
   WHERE IMPORT_LIST_HEADER_ID = p_IMPORT_LIST_HEADER_ID
   AND object_version_number = p_object_version
   FOR UPDATE NOWAIT;
Line: 1752

      IF p_ams_import_rec.last_update_date = FND_API.g_miss_date OR p_ams_import_rec.last_update_date IS NULL THEN
 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_last_update_date');
Line: 1759

      IF p_ams_import_rec.last_updated_by = FND_API.g_miss_num OR p_ams_import_rec.last_updated_by IS NULL THEN
 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_last_updated_by');
Line: 1843

      IF p_ams_import_rec.last_update_date IS NULL THEN
 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_last_update_date');
Line: 1850

      IF p_ams_import_rec.last_updated_by IS NULL THEN
 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_last_updated_by');
Line: 1960

    SELECT *
    FROM  AMS_IMP_LIST_HEADERS_ALL
    WHERE import_list_header_id=p_ams_import_rec.import_list_header_id;
Line: 1966

    SELECT *
    FROM  AMS_IMP_LIST_HEADERS_VL
    WHERE name=p_ams_import_rec.name;
Line: 1991

   IF p_validation_mode = JTF_PLSQL_API.g_update
   THEN
      --Exp date and Usage validation only applied to Rented lists
      IF (p_ams_import_rec.rented_list_flag = 'R') and
         (p_ams_import_rec.expiry_date <> FND_API.g_miss_date) and
         (p_ams_import_rec.expiry_date < SYSDATE)
      THEN
         --AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ams_import_NO_name');
Line: 2151

      SELECT *
      FROM ams_imp_list_headers_all
      WHERE import_list_header_id = p_ams_import_rec.import_list_header_id;
Line: 2157

   SELECT user_status_id
   FROM ams_user_statuses_vl
   WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
         system_status_code = 'NEW' and default_flag = 'Y';
Line: 2180

   IF p_ams_import_rec.last_update_date = FND_API.g_miss_date THEN
      x_complete_rec.last_update_date := l_ams_import_rec.last_update_date;
Line: 2185

   IF p_ams_import_rec.last_updated_by = FND_API.g_miss_num THEN
      x_complete_rec.last_updated_by := l_ams_import_rec.last_updated_by;
Line: 2200

   IF p_ams_import_rec.last_update_login = FND_API.g_miss_num THEN
      x_complete_rec.last_update_login := l_ams_import_rec.last_update_login;
Line: 2558

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