[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_bulk_insert_limit CONSTANT NUMBER := 250;
l_location_last_update_date FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
l_location_last_updated_by FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
l_location_last_update_login FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
l_location_program_update_date FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
l_deleted_download_ids FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
select count(fmdl.download_file_id)
from fte_mile_download_lines fmdl
where fmdl.download_file_id = cp_download_file_id;
select fmdf.download_file_id,
fmdf.template_id,
fmdf.download_date,
fmdf.upload_date,
fmdf.identifier_type
from fte_mile_download_files fmdf
where fmdf.file_name = cp_file_name;
select fmdl.origin_id,
fmdl.destination_id
from fte_mile_download_lines fmdl
where fmdl.download_file_id = cp_download_file_id;
select fmtc.column_id,
fmtc.start_position,
fmtc.length
from fte_mile_template_columns fmtc
where fmtc.column_type = cp_column_type
and fmtc.template_id = cp_template_id;
select fmca.enabled_flag,
fmca.return_format,
fmca.db_uom
from fte_mile_column_attributes fmca
where fmca.column_id = cp_column_id;
select tt.origin_id,
tt.destination_id,
fdlt.origin_id,
fdlt.destination_id,
fdlt.identifier_type,
fdlt.distance,
fdlt.distance_uom,
fdlt.transit_time,
fdlt.transit_time_uom,
fdlt.creation_date,
fdlt.created_by,
fdlt.last_update_date,
fdlt.last_updated_by,
fdlt.last_update_login,
fdlt.program_application_id,
fdlt.program_id,
fdlt.program_update_date,
fdlt.request_id
from (SELECT origin_id,
destination_id,
identifier_type,
distance,
distance_uom,
transit_time,
transit_time_uom,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id FROM FTE_DISTANCE_LOADER_TMP) fdlt,
FTE_LOCATION_MILEAGES tt
where fdlt.origin_id = tt.origin_id(+)
and fdlt.destination_id = tt.destination_id(+);
l_u_last_update_date_tab FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
l_u_last_updated_by_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
l_u_last_update_login_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
l_u_program_update_date_tab FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
l_i_last_update_date_tab FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
l_i_last_updated_by_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
l_i_last_update_login_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
l_i_program_update_date_tab FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
l_new_last_update_date_tab FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
l_new_last_updated_by_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
l_new_last_update_login_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
l_new_program_update_date_tab FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
l_insert_ctr PLS_INTEGER;
l_update_ctr PLS_INTEGER;
select muomv.uom_code
from mtl_units_of_measure_vl muomv,
wsh_global_parameters wgp
where wgp.gu_time_class = muomv.uom_class
and muomv.unit_of_measure = x_unit_of_measure
and muomv.language = x_language;
select muomv.uom_code
from mtl_units_of_measure_vl muomv,
wsh_global_parameters wgp
where wgp.GU_DISTANCE_CLASS = muomv.uom_class
and muomv.UNIT_OF_MEASURE = y_unit_of_measure
and muomv.language = y_language;
l_location_last_update_date(l_load_ctr) := l_cur_date;
l_location_last_updated_by(l_load_ctr) := g_user_id;
l_location_last_update_login(l_load_ctr) := null;
l_location_program_update_date(l_load_ctr) := null;
WSH_DEBUG_SV.logmsg(l_module_name,'we are going to insert 250 at a time into the global temp table insert into fte_distance_loader_tmp');
FND_FILE.PUT_LINE(FND_FILE.log, 'Inserting 250 records into temp table');
insert into fte_distance_loader_tmp(ORIGIN_ID,
DESTINATION_ID,
IDENTIFIER_TYPE,
DISTANCE,
DISTANCE_UOM,
TRANSIT_TIME,
TRANSIT_TIME_UOM,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID)
values(l_location_origin_id(i),
l_location_destination_id(i),
l_location_identifier_type(i),
l_location_distance(i),
l_location_distance_uom(i),
l_location_transit_time(i),
l_location_transit_time_uom(i),
l_location_creation_date(i),
l_location_created_by(i),
l_location_last_update_date(i),
l_location_last_updated_by(i),
l_location_last_update_login(i),
l_location_program_app_id(i),
l_location_program_id(i),
l_location_program_update_date(i),
l_location_request_id(i));
l_location_origin_id.DELETE;
l_location_destination_id.DELETE;
l_location_identifier_type.DELETE;
l_location_distance.DELETE;
l_location_distance_uom.DELETE;
l_location_transit_time.DELETE;
l_location_transit_time_uom.DELETE;
l_location_creation_date.DELETE;
l_location_created_by.DELETE;
l_location_last_update_date.DELETE;
l_location_last_updated_by.DELETE;
l_location_last_update_login.DELETE;
l_location_program_app_id.DELETE;
l_location_program_id.DELETE;
l_location_program_update_date.DELETE;
l_location_request_id.DELETE;
WSH_DEBUG_SV.logmsg(l_module_name,'insert the last few lines that did not sum upto 250 into the Global temp table');
FND_FILE.PUT_LINE(FND_FILE.log, 'Inserting remainder of records into temp table');
insert into fte_distance_loader_tmp(ORIGIN_ID,
DESTINATION_ID,
IDENTIFIER_TYPE,
DISTANCE,
DISTANCE_UOM,
TRANSIT_TIME,
TRANSIT_TIME_UOM,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID)
values(l_location_origin_id(i),
l_location_destination_id(i),
l_location_identifier_type(i),
l_location_distance(i),
l_location_distance_uom(i),
l_location_transit_time(i),
l_location_transit_time_uom(i),
l_location_creation_date(i),
l_location_created_by(i),
l_location_last_update_date(i),
l_location_last_updated_by(i),
l_location_last_update_login(i),
l_location_program_app_id(i),
l_location_program_id(i),
l_location_program_update_date(i),
l_location_request_id(i));
l_location_origin_id.DELETE;
l_location_destination_id.DELETE;
l_location_identifier_type.DELETE;
l_location_distance.DELETE;
l_location_distance_uom.DELETE;
l_location_transit_time.DELETE;
l_location_transit_time_uom.DELETE;
l_location_creation_date.DELETE;
l_location_created_by.DELETE;
l_location_last_update_date.DELETE;
l_location_last_updated_by.DELETE;
l_location_last_update_login.DELETE;
l_location_program_app_id.DELETE;
l_location_program_id.DELETE;
l_location_program_update_date.DELETE;
l_location_request_id.DELETE;
FND_FILE.PUT_LINE(FND_FILE.log, 'Number of lines in the table matched the number of lines in the file, inserting and/or updating into the location mileages table');
WSH_DEBUG_SV.logmsg(l_module_name,'Number of lines in the table matched the number of lines in the file, inserting and/or updating into the location mileages table');
l_old_origin_id_tab.DELETE;
l_old_destination_id_tab.DELETE;
l_new_origin_id_tab.DELETE;
l_new_destination_id_tab.DELETE;
l_new_identifier_type_tab.DELETE;
l_new_distance_tab.DELETE;
l_new_distance_uom_tab.DELETE;
l_new_transit_time_tab.DELETE;
l_new_transit_time_uom_tab.DELETE;
l_new_creation_date_tab.DELETE;
l_new_created_by_tab.DELETE;
l_new_last_update_date_tab.DELETE;
l_new_last_updated_by_tab.DELETE;
l_new_last_update_login_tab.DELETE;
l_new_program_app_id_tab.DELETE;
l_new_program_id_tab.DELETE;
l_new_program_update_date_tab.DELETE;
l_new_request_id_tab.DELETE;
FND_FILE.PUT_LINE(FND_FILE.log,'Open the cursor, c_get_merge_data, to get the records to update and/or insert');
WSH_DEBUG_SV.logmsg(l_module_name,'Open the cursor, c_get_merge_data, to get the records to update and/or insert');
l_new_last_update_date_tab,
l_new_last_updated_by_tab,
l_new_last_update_login_tab,
l_new_program_app_id_tab,
l_new_program_id_tab,
l_new_program_update_date_tab,
l_new_request_id_tab
LIMIT l_bulk_collect_size;
FND_FILE.PUT_LINE(FND_FILE.log,'cleaning the temp update tables');
WSH_DEBUG_SV.logmsg(l_module_name,'cleaning the temp update tables');
l_u_origin_id_tab.DELETE;
l_u_destination_id_tab.DELETE;
l_u_identifier_type_tab.DELETE;
l_u_distance_tab.DELETE;
l_u_distance_uom_tab.DELETE;
l_u_transit_time_tab.DELETE;
l_u_transit_time_uom_tab.DELETE;
l_u_last_update_date_tab.DELETE;
l_u_last_updated_by_tab.DELETE;
l_u_last_update_login_tab.DELETE;
l_u_program_app_id_tab.DELETE;
l_u_program_id_tab.DELETE;
l_u_program_update_date_tab.DELETE;
l_u_request_id_tab.DELETE;
FND_FILE.PUT_LINE(FND_FILE.log,'cleaning the temp insert tables');
WSH_DEBUG_SV.logmsg(l_module_name,'cleaning the temp insert tables');
l_i_origin_id_tab.DELETE;
l_i_destination_id_tab.DELETE;
l_i_identifier_type_tab.DELETE;
l_i_distance_tab.DELETE;
l_i_distance_uom_tab.DELETE;
l_i_transit_time_tab.DELETE;
l_i_transit_time_uom_tab.DELETE;
l_i_creation_date_tab.DELETE;
l_i_created_by_tab.DELETE;
l_i_last_update_date_tab.DELETE;
l_i_last_updated_by_tab.DELETE;
l_i_last_update_login_tab.DELETE;
l_i_program_app_id_tab.DELETE;
l_i_program_id_tab.DELETE;
l_i_program_update_date_tab.DELETE;
l_i_request_id_tab.DELETE;
l_insert_ctr := 0;
l_update_ctr := 0;
FND_FILE.PUT_LINE(FND_FILE.log, 'checking for insert and update records');
WSH_DEBUG_SV.logmsg(l_module_name,'checking for insert and update records');
FND_FILE.PUT_LINE(FND_FILE.log, 'getting insert records');
WSH_DEBUG_SV.logmsg(l_module_name,'getting insert records');
l_insert_ctr := l_insert_ctr + 1;
l_i_origin_id_tab(l_insert_ctr) := l_new_origin_id_tab(kk);
l_i_destination_id_tab(l_insert_ctr) := l_new_destination_id_tab(kk);
l_i_identifier_type_tab(l_insert_ctr) := l_new_identifier_type_tab(kk);
l_i_distance_tab(l_insert_ctr) := l_new_distance_tab(kk);
l_i_distance_uom_tab(l_insert_ctr) := l_new_distance_uom_tab(kk);
l_i_transit_time_tab(l_insert_ctr) := l_new_transit_time_tab(kk);
l_i_transit_time_uom_tab(l_insert_ctr) := l_new_transit_time_uom_tab(kk);
l_i_creation_date_tab(l_insert_ctr) := l_new_creation_date_tab(kk);
l_i_created_by_tab(l_insert_ctr) := l_new_created_by_tab(kk);
l_i_last_update_date_tab(l_insert_ctr) := l_new_last_update_date_tab(kk);
l_i_last_updated_by_tab(l_insert_ctr) := l_new_last_updated_by_tab(kk);
l_i_last_update_login_tab(l_insert_ctr) := l_new_last_update_login_tab(kk);
l_i_program_app_id_tab(l_insert_ctr) := l_new_program_app_id_tab(kk);
l_i_program_id_tab(l_insert_ctr) := l_new_program_id_tab(kk);
l_i_program_update_date_tab(l_insert_ctr) := l_new_program_update_date_tab(kk);
l_i_request_id_tab(l_insert_ctr) := l_new_request_id_tab(kk);
FND_FILE.PUT_LINE(FND_FILE.log, 'getting update records');
WSH_DEBUG_SV.logmsg(l_module_name,'getting update records');
l_update_ctr := l_update_ctr + 1;
l_u_origin_id_tab(l_update_ctr) := l_old_origin_id_tab(kk);
l_u_destination_id_tab(l_update_ctr) := l_old_destination_id_tab(kk);
l_u_identifier_type_tab(l_update_ctr) := l_new_identifier_type_tab(kk);
l_u_distance_tab(l_update_ctr) := l_new_distance_tab(kk);
l_u_distance_uom_tab(l_update_ctr) := l_new_distance_uom_tab(kk);
l_u_transit_time_tab(l_update_ctr) := l_new_transit_time_tab(kk);
l_u_transit_time_uom_tab(l_update_ctr) := l_new_transit_time_uom_tab(kk);
l_u_last_update_date_tab(l_update_ctr) := l_new_last_update_date_tab(kk);
l_u_last_updated_by_tab(l_update_ctr) := l_new_last_updated_by_tab(kk);
l_u_last_update_login_tab(l_update_ctr) := l_new_last_update_login_tab(kk);
l_u_program_app_id_tab(l_update_ctr) := l_new_program_app_id_tab(kk);
l_u_program_id_tab(l_update_ctr) := l_new_program_id_tab(kk);
l_u_program_update_date_tab(l_update_ctr) := l_new_program_update_date_tab(kk);
l_u_request_id_tab(l_update_ctr) := l_new_request_id_tab(kk);
END LOOP; -- END OF LOOP_02 (checking for insert and update records
FND_FILE.PUT_LINE(FND_FILE.log, 'Bulk update of mileage records');
WSH_DEBUG_SV.logmsg(l_module_name,'Bulk update of mileage records');
UPDATE FTE_LOCATION_MILEAGES
SET identifier_type = l_u_identifier_type_tab(m),
distance = l_u_distance_tab(m),
distance_uom = l_u_distance_uom_tab(m),
transit_time = l_u_transit_time_tab(m),
transit_time_uom = l_u_transit_time_uom_tab(m),
last_update_date = l_u_last_update_date_tab(m),
last_updated_by = l_u_last_updated_by_tab(m),
last_update_login = l_u_last_update_login_tab(m),
program_application_id = l_u_program_app_id_tab(m),
program_id = l_u_program_id_tab(m),
program_update_date = l_u_program_update_date_tab(m),
request_id = l_u_request_id_tab(m)
WHERE origin_id = l_u_origin_id_tab(m)
AND destination_id = l_u_destination_id_tab(m);
FND_FILE.PUT_LINE(FND_FILE.log, 'Bulk insert of mileage records');
WSH_DEBUG_SV.logmsg(l_module_name,'Bulk insert of mileage records');
insert into FTE_LOCATION_MILEAGES(origin_id,
destination_id,
identifier_type,
distance,
distance_uom,
transit_time,
transit_time_uom,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id)
values(l_i_origin_id_tab(i),
l_i_destination_id_tab(i),
l_i_identifier_type_tab(i),
l_i_distance_tab(i),
l_i_distance_uom_tab(i),
l_i_transit_time_tab(i),
l_i_transit_time_uom_tab(i),
l_i_creation_date_tab(i),
l_i_created_by_tab(i),
l_i_last_update_date_tab(i),
l_i_last_updated_by_tab(i),
l_i_last_update_login_tab(i),
l_i_program_app_id_tab(i),
l_i_program_id_tab(i),
l_i_program_update_date_tab(i),
l_i_request_id_tab(i));
update fte_mile_download_files
set upload_date = l_cur_date,
upload_id = p_load_id
where download_file_id = l_download_file_id;
delete fte_mile_download_lines
where download_file_id = l_download_file_id
returning download_file_id BULK COLLECT INTO l_deleted_download_ids;
WSH_DEBUG_SV.logmsg(l_module_name,'The file has less lines than when it was created, i.e. lines from the file have been deleted, thus we cannot load, RAISE FTE_DIST_LESS_FILE_LINES exception');