The following lines contain the word 'select', 'insert', 'update' or 'delete':
FND_PROGRAM.DELETE_PROGRAM (
program_short_name => l_con_program_name,
application => G_APP_NAME
);
FND_PROGRAM.DELETE_EXECUTABLE (
executable_short_name => l_con_program_name,
application => G_APP_NAME
);
--update the headers table, need to add the export stuff
--
IF l_repeat_mode = G_REPEAT_NONE THEN --cancel
l_cancel_date := SYSDATE;
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;
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;
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';
SELECT FILE_TYPE
FROM AMS_IMP_DOCUMENTS
WHERE IMPORT_LIST_HEADER_ID = p_import_header_id;
SELECT AMS_IMP_LIST_HEADERS_ALL_S.NEXTVAL INTO x_ams_import_rec.import_list_header_id
FROM DUAL;
x_ams_import_rec.last_update_date := l_current_date;
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;
AMS_Utility_PVT.Write_Conc_Log (' Start Duplicate_Import_List after update AMS_IMP_LIST_HEADERS_ALL ');
SELECT AMS_IMP_LIST_HEADERS_ALL_s.NEXTVAL
FROM dual;
SELECT 1
FROM AMS_IMP_LIST_HEADERS_ALL
WHERE IMPORT_LIST_HEADER_ID = l_id;
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;
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';
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);
select as_import_interface_s.nextval into l_batch_id from dual;
select as_import_interface_s.nextval into l_batch_id from dual;
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);
UPDATE AMS_IMP_LIST_HEADERS_ALL
SET PASSWORD = l_encrpted_password
WHERE IMPORT_LIST_HEADER_ID = l_import_list_header_id;
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;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Import_List';
SAVEPOINT UPDATE_Import_List_PVT;
AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
p_token_name => 'INFO',
p_token_value => 'Import_List') ;
p_token_value => 'Last_Update_Date') ;
AMS_UTILITY_PVT.debug_message('Private API: Calling update table handler');
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);
ROLLBACK TO UPDATE_Import_List_PVT;
ROLLBACK TO UPDATE_Import_List_PVT;
ROLLBACK TO UPDATE_Import_List_PVT;
End Update_Import_List;
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';
SAVEPOINT DELETE_Import_List_PVT;
AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
AMS_IMP_LIST_HEADERS_PKG.Delete_Row(
p_IMPORT_LIST_HEADER_ID => p_IMPORT_LIST_HEADER_ID);
ROLLBACK TO DELETE_Import_List_PVT;
ROLLBACK TO DELETE_Import_List_PVT;
ROLLBACK TO DELETE_Import_List_PVT;
End Delete_Import_List;
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;
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');
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');
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');
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');
SELECT *
FROM AMS_IMP_LIST_HEADERS_ALL
WHERE import_list_header_id=p_ams_import_rec.import_list_header_id;
SELECT *
FROM AMS_IMP_LIST_HEADERS_VL
WHERE name=p_ams_import_rec.name;
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');
SELECT *
FROM ams_imp_list_headers_all
WHERE import_list_header_id = p_ams_import_rec.import_list_header_id;
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';
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;
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;
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;
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => x_return_status
);