The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT import_type_code INTO l_imp_type_code
FROM cn_imp_headers
WHERE imp_header_id = p_imp_header_id;
SELECT imp_line_id FROM cn_imp_lines
WHERE imp_header_id = p_imp_header_id
ORDER BY imp_line_id;
UPDATE cn_imp_lines
SET record_num = l_count
WHERE imp_line_id = l_imp_lines_csr.imp_line_id
;
SELECT name, status_code,server_flag
INTO l_imp_header.name ,l_imp_header.status_code ,
l_imp_header.server_flag
FROM cn_imp_headers
WHERE imp_header_id = p_imp_header_id;
update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'FAIL');
update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'FAIL');
SELECT name, status_code
INTO l_name ,l_status_code
FROM cn_imp_headers
WHERE imp_header_id = p_imp_header_id;
SELECT export_conc_program
INTO l_conc_pgm
FROM cn_import_types
WHERE import_type_code = l_imp_type_code;
update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'SUBMIT');
update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'FAIL');
SELECT COUNT(*)
FROM cn_imp_lines
WHERE imp_header_id = p_imp_header_id;
update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'SUBMIT');
SELECT name, status_code,server_flag
INTO l_imp_header.name ,l_imp_header.status_code ,
l_imp_header.server_flag
FROM cn_imp_headers
WHERE imp_header_id = p_imp_header_id;
update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'STAGE_FAIL');
GOTO delete_conc;
update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'STAGE',
p_staged_row => l_loaded_rows);
<< delete_conc >>
-- Clean-up the concurrent programs which were created
-- during program execution.
FND_PROGRAM.DELETE_PROGRAM
(program_short_name => l_short_name,
application => 'CN'
);
cn_message_pkg.debug('Staging Data : Delete Conc Program.');
FND_PROGRAM.DELETE_EXECUTABLE
(executable_short_name => l_short_name,
application => 'CN'
);
cn_message_pkg.debug('Staging Data : Delete Conc Executable.');
SELECT name, status_code,server_flag
INTO l_imp_header.name ,l_imp_header.status_code ,l_imp_header.server_flag
FROM cn_imp_headers
WHERE imp_header_id = p_imp_header_id;
update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'STAGE_FAIL');
update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'STAGE_FAIL');
SELECT conc_program FROM cn_import_types
WHERE import_type_code = l_imp_type_code;
SELECT name, status_code,server_flag
INTO l_imp_header.name ,l_imp_header.status_code,
l_imp_header.server_flag
FROM cn_imp_headers
WHERE imp_header_id = p_imp_header_id;
update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'SCHEDULE');
update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'IMPORT_FAIL');
update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'IMPORT_FAIL');
update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'IMPORT_FAIL');
SELECT f.source_column,f.target_column_name,f.target_table_name,
m.object_version_number map_obj_ver
FROM cn_imp_map_fields f, cn_imp_maps m
WHERE m.imp_map_id = c_imp_map_id
AND f.imp_map_id = m.imp_map_id
ORDER BY f.source_column
;
SELECT COUNT(*)
FROM cn_imp_lines
WHERE imp_header_id = p_imp_header_id;
SELECT name, status_code,server_flag,imp_map_id, source_column_num
INTO l_imp_header.name ,l_imp_header.status_code ,
l_imp_header.server_flag, l_imp_header.imp_map_id,
l_imp_header.source_column_num
FROM cn_imp_headers
WHERE imp_header_id = p_imp_header_id;
update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'SUBMIT');
-- check if mapping is updated by other user
IF c_tar_col.map_obj_ver <> p_map_obj_ver THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
FND_MESSAGE.SET_NAME ('CN' , 'CN_IMP_MAPPED_CHANGED');
cn_import_client_pvt.insert_data
(p_api_version => 1.0,
p_imp_header_id => p_imp_header_id,
p_import_type_code => l_imp_type_code,
p_table_name => l_target_table,
p_col_names => l_columns,
p_data => l_data,
p_row_count => l_row_count,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'STAGE',
p_staged_row => l_loaded_rows);
update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'STAGE_FAIL');
update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'STAGE_FAIL');
update_imp_headers
(p_imp_header_id => p_imp_header_id,
p_status_code => 'STAGE_FAIL');
PROCEDURE update_imp_lines
(p_imp_line_id IN NUMBER,
p_status_code IN VARCHAR2,
p_error_code IN VARCHAR2,
p_error_msg IN VARCHAR2 := NULL) IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE cn_imp_lines
SET status_code = p_status_code, error_code = p_error_code,
error_msg = p_error_msg
WHERE imp_line_id = p_imp_line_id
;
END update_imp_lines;
PROCEDURE update_imp_headers
(p_imp_header_id IN NUMBER,
p_status_code IN VARCHAR2,
p_staged_row IN NUMBER := NULL,
p_processed_row IN NUMBER := NULL,
p_failed_row IN NUMBER := NULL) IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE cn_imp_headers
SET status_code = Decode(p_status_code,NULL,status_code,p_status_code),
staged_row =Decode(p_staged_row,NULL,staged_row,p_staged_row),
processed_row =Decode(p_processed_row,NULL,processed_row,p_processed_row),
failed_row = Decode(p_failed_row,NULL,failed_row,p_failed_row)
WHERE imp_header_id = p_imp_header_id
;
END update_imp_headers;
SELECT f.source_column,f.source_user_column,
f.target_column_name,f.target_table_name
FROM cn_imp_headers h, cn_imp_map_fields f
WHERE h.imp_header_id = p_imp_header_id
AND f.imp_map_id = h.imp_map_id
ORDER BY f.source_column
;
x_sql_stmt := 'SELECT ';
IF x_sql_stmt <> 'SELECT ' THEN
x_sql_stmt := x_sql_stmt || ' || '' , '' || ';