224 lines
14 KiB
PL/PgSQL
Executable File
224 lines
14 KiB
PL/PgSQL
Executable File
|
|
|
|
CREATE OR REPLACE FUNCTION fw_ods.fn_fw_ods_load(p_client_id int,p_function_id int,p_job_id int,p_step_id int)
|
|
RETURNS void
|
|
AS $$
|
|
declare
|
|
f record;
|
|
w_job_id int := p_job_id;
|
|
w_step_id int := p_step_id;
|
|
w_function_id int := p_function_id;
|
|
w_client_id int := p_client_id;
|
|
w_file_sheet_mnemonic text :='';
|
|
error_count int :=0;
|
|
|
|
begin
|
|
|
|
|
|
/****************************************************************
|
|
******* ForeWarn Licensing and / or usage Terms and Conditions ******
|
|
ForeWarn. The NextGen Insights Solution Platform
|
|
|
|
Copyright 2021 COMPEGENCE. All Rights Reserved
|
|
|
|
ForeWarn is a product of COMPEGENCE.
|
|
To be used only with a valid license from COMPEGENCE
|
|
|
|
www.compegence.com info@compegence.com
|
|
****************************************************************/
|
|
|
|
|
|
for f in select distinct file_syspk, file_mnemonic
|
|
from fw_core.fw_jobctl_file_runschedule
|
|
where latest_runschedule_flag = '1' and
|
|
end_status = 'success' and
|
|
end_status_note = 'TRX_completed'
|
|
order by file_syspk
|
|
loop
|
|
|
|
if f.file_mnemonic in ('FTDRY', 'FTWET', 'FTHLG')
|
|
then
|
|
|
|
perform fw_core.fn_get_function_exception('fw_ods.fn_fw_ods_field_load('||w_client_id||','||w_function_id||','''||f.file_mnemonic||''','''||w_file_sheet_mnemonic||''','||f.file_syspk||')');
|
|
|
|
|
|
perform fw_core.fn_get_function_exception('fw_ods.fn_fw_ods_implement_details_update('||w_client_id||','||w_function_id||','''||f.file_mnemonic||''','''||w_file_sheet_mnemonic||''','||f.file_syspk||')');
|
|
|
|
perform fw_core.fn_get_function_exception('fw_ods.fn_fw_ods_comments_load('||w_client_id||','||w_function_id||','''||f.file_mnemonic||''','''||w_file_sheet_mnemonic||''','||f.file_syspk||')');
|
|
|
|
|
|
perform fw_core.fn_get_function_exception('fw_ods.fn_fw_ods_observations_load('||w_client_id||','||w_function_id||','''||f.file_mnemonic||''','''||w_file_sheet_mnemonic||''','||f.file_syspk||')');
|
|
|
|
|
|
|
|
|
|
elsif f.file_mnemonic = 'BUDNI' then
|
|
perform fw_core.fn_get_function_exception('fw_ods.fn_fw_ods_budni_load('||w_client_id||','||w_function_id||','''||f.file_mnemonic||''','''||w_file_sheet_mnemonic||''','||f.file_syspk||')');
|
|
|
|
|
|
elsif f.file_mnemonic in ('PTOBEN', 'PTOSTD') then
|
|
perform fw_core.fn_get_function_exception('fw_ods.fn_fw_ods_PTO_load('||w_client_id||','||w_function_id||','''||f.file_mnemonic||''','''||w_file_sheet_mnemonic||''','||f.file_syspk||')');
|
|
|
|
|
|
perform fw_core.fn_get_function_exception('fw_ods.fn_fw_ods_pto_observations_load('||w_client_id||','||w_function_id||','''||f.file_mnemonic||''','''||w_file_sheet_mnemonic||''','||f.file_syspk||')');
|
|
|
|
|
|
|
|
elsif f.file_mnemonic in ('IHTBT30','IHTBT50','IHTBTD','IHTCGM','IHTHAM','IHTEMT','IHTHLS','IHTNST','IHTSLL') then
|
|
|
|
perform fw_core.fn_get_function_exception('fw_ods.fn_fw_ods_IHT_load('||w_client_id||','||w_function_id||','''||f.file_mnemonic||''','''||w_file_sheet_mnemonic||''','||f.file_syspk||')');
|
|
|
|
perform fw_core.fn_get_function_exception('fw_ods.fn_fw_ods_detailed_load('||w_client_id||','||w_function_id||','''||f.file_mnemonic||''','''||w_file_sheet_mnemonic||''','||f.file_syspk||')');
|
|
|
|
|
|
|
|
|
|
elsif f.file_mnemonic in ('DBOECD', 'DBSTD') then
|
|
perform fw_core.fn_get_function_exception('fw_ods.fn_fw_ods_drawbar_performance_load('||w_client_id||','||w_function_id||','''||f.file_mnemonic||''','''||w_file_sheet_mnemonic||''','||f.file_syspk||')');
|
|
|
|
perform fw_core.fn_get_function_exception('fw_ods.fn_fw_ods_drawbar_load('||w_client_id||','||w_function_id||','''||f.file_mnemonic||''','''||w_file_sheet_mnemonic||''','||f.file_syspk||')');
|
|
|
|
perform fw_core.fn_get_function_exception('fw_ods.fn_fw_ods_drawbar_observations_load('||w_client_id||','||w_function_id||','''||f.file_mnemonic||''','''||w_file_sheet_mnemonic||''','||f.file_syspk||')');
|
|
|
|
perform fw_core.fn_get_function_exception('fw_ods.fn_fw_ods_drawbar_comments_load('||w_client_id||','||w_function_id||','''||f.file_mnemonic||''','''||w_file_sheet_mnemonic||''','||f.file_syspk||')');
|
|
|
|
|
|
end if;
|
|
|
|
select count(status) from fw_core.db_run_status where file_syspk = f.file_syspk and file_mnemonic = f.file_mnemonic and staging_type = 'ods' and status = 'error' into error_count;
|
|
if error_count = 0 then
|
|
update fw_core.fw_jobctl_file_sheet_runschedule set end_status_note = 'ODS_completed', end_status='success',staging_type = 'ODS',end_time = now()
|
|
where file_syspk=f.file_syspk;
|
|
update fw_core.fw_jobctl_file_runschedule set end_status_note = 'ODS_completed', end_status='success',staging_type = 'ODS',end_time=now()
|
|
where latest_runschedule_flag = '1' and end_status_note ='TRX_completed' and file_syspk = f.file_syspk;
|
|
|
|
else
|
|
update fw_core.fw_jobctl_file_sheet_runschedule set end_status_note = 'ODS_failed', end_status='error',staging_type = 'ODS',end_time = now()
|
|
where file_syspk=f.file_syspk;
|
|
update fw_core.fw_jobctl_file_runschedule set end_status_note = 'ODS_failed', end_status='error',staging_type = 'ODS',end_time=now()
|
|
where latest_runschedule_flag = '1' and end_status_note ='TRX_completed' and file_syspk = f.file_syspk;
|
|
|
|
end if;
|
|
|
|
end loop;
|
|
|
|
|
|
--fw_ods
|
|
update fw_ods.fw_ods set tractor_model=REGEXP_REPLACE(tractor_model,'\r|\n','','g');
|
|
|
|
update fw_ods.fw_ods set tractor_make = m.target_value from fw_ods.fw_ods_misnomer m where trim(tractor_make) = m.source_value ;
|
|
update fw_ods.fw_ods set tractor_model = m.target_value from fw_ods.fw_ods_misnomer m where trim(tractor_model) = m.source_value ;
|
|
update fw_ods.fw_ods set wheel_drive_type = m.target_value from fw_ods.fw_ods_misnomer m where trim(wheel_drive_type) = m.source_value ;
|
|
update fw_ods.fw_ods set name_of_implement = m.target_value from fw_ods.fw_ods_misnomer m where trim(name_of_implement) = m.source_value ;
|
|
update fw_ods.fw_ods set type_of_implement = m.target_value from fw_ods.fw_ods_misnomer m where trim(type_of_implement) = m.source_value ;
|
|
|
|
|
|
update fw_ods.fw_ods a set "configuration" = m."configuration" from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a."configuration" is null;
|
|
update fw_ods.fw_ods a set tractor_engine_hp = m.tractor_engine_hp from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.tractor_engine_hp is null;
|
|
update fw_ods.fw_ods a set wheel_drive_type=m.wheel_drive_type from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.wheel_drive_type is null;
|
|
update fw_ods.fw_ods a set platform = m.platform from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.platform is null;
|
|
update fw_ods.fw_ods a set tractor_make = m.tractor_make from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.tractor_make is null;
|
|
|
|
--fw_ods_comments
|
|
update fw_ods.fw_ods_comments set tractor_model=REGEXP_REPLACE(tractor_model,'\r|\n','','g');
|
|
|
|
update fw_ods.fw_ods_comments set tractor_make = m.target_value from fw_ods.fw_ods_misnomer m where trim(tractor_make) = m.source_value ;
|
|
update fw_ods.fw_ods_comments set tractor_model = m.target_value from fw_ods.fw_ods_misnomer m where trim(tractor_model) = m.source_value ;
|
|
update fw_ods.fw_ods_comments set wheel_drive_type = m.target_value from fw_ods.fw_ods_misnomer m where trim(wheel_drive_type) = m.source_value ;
|
|
update fw_ods.fw_ods_comments set name_of_implement = m.target_value from fw_ods.fw_ods_misnomer m where trim(name_of_implement) = m.source_value ;
|
|
update fw_ods.fw_ods_comments set type_of_implement = m.target_value from fw_ods.fw_ods_misnomer m where trim(type_of_implement) = m.source_value ;
|
|
|
|
|
|
|
|
update fw_ods.fw_ods_comments a set "configuration" = m."configuration" from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a."configuration" is null;
|
|
update fw_ods.fw_ods_comments a set tractor_engine_hp = m.tractor_engine_hp from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.tractor_engine_hp is null;
|
|
update fw_ods.fw_ods_comments a set wheel_drive_type=m.wheel_drive_type from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.wheel_drive_type is null;
|
|
update fw_ods.fw_ods_comments a set platform = m.platform from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.platform is null;
|
|
update fw_ods.fw_ods_comments a set tractor_make = m.tractor_make from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.tractor_make is null;
|
|
|
|
--fw_ods_drawbar_observations
|
|
|
|
|
|
update fw_ods.fw_ods_drawbar_performance set tractor_model=REGEXP_REPLACE(tractor_model,'\r|\n','','g');
|
|
|
|
update fw_ods.fw_ods_drawbar_performance set tractor_make = m.target_value from fw_ods.fw_ods_misnomer m where trim(tractor_make) = m.source_value ;
|
|
update fw_ods.fw_ods_drawbar_performance set tractor_model = m.target_value from fw_ods.fw_ods_misnomer m where trim(tractor_model) = m.source_value ;
|
|
update fw_ods.fw_ods_drawbar_performance set wheel_drive_type = m.target_value from fw_ods.fw_ods_misnomer m where trim(wheel_drive_type) = m.source_value ;
|
|
-- update fw_ods.fw_ods_drawbar_performance set name_of_implement = m.target_value from fw_ods.fw_ods_misnomer m where trim(name_of_implement) = m.source_value ;
|
|
--update fw_ods.fw_ods_drawbar_performance set type_of_implement = m.target_value from fw_ods.fw_ods_misnomer m where trim(type_of_implement) = m.source_value ;
|
|
|
|
|
|
update fw_ods.fw_ods_drawbar_performance a set "configuration" = m."configuration" from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a."configuration" is null;
|
|
update fw_ods.fw_ods_drawbar_performance a set tractor_engine_hp = m.tractor_engine_hp from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.tractor_engine_hp is null;
|
|
update fw_ods.fw_ods_drawbar_performance a set wheel_drive_type=m.wheel_drive_type from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.wheel_drive_type is null;
|
|
update fw_ods.fw_ods_drawbar_performance a set platform = m.platform from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.platform is null;
|
|
update fw_ods.fw_ods_drawbar_performance a set tractor_make = m.tractor_make from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.tractor_make is null;
|
|
|
|
|
|
--fw_ods_detailed
|
|
|
|
update fw_ods.fw_ods_detailed set tractor_model=REGEXP_REPLACE(tractor_model,'\r|\n','','g');
|
|
|
|
update fw_ods.fw_ods_detailed set tractor_make = m.target_value from fw_ods.fw_ods_misnomer m where trim(tractor_make) = m.source_value ;
|
|
update fw_ods.fw_ods_detailed set tractor_model = m.target_value from fw_ods.fw_ods_misnomer m where trim(tractor_model) = m.source_value ;
|
|
update fw_ods.fw_ods_detailed set wheel_drive_type = m.target_value from fw_ods.fw_ods_misnomer m where trim(wheel_drive_type) = m.source_value ;
|
|
-- update fw_ods.fw_ods_detailed set name_of_implement = m.target_value from fw_ods.fw_ods_misnomer m where trim(name_of_implement) = m.source_value ;
|
|
--update fw_ods.fw_ods_detailed set type_of_implement = m.target_value from fw_ods.fw_ods_misnomer m where trim(type_of_implement) = m.source_value ;
|
|
|
|
|
|
|
|
update fw_ods.fw_ods_detailed a set "configuration" = m."configuration" from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a."configuration" is null;
|
|
update fw_ods.fw_ods_detailed a set tractor_engine_hp = m.tractor_engine_hp from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.tractor_engine_hp is null;
|
|
update fw_ods.fw_ods_detailed a set wheel_drive_type=m.wheel_drive_type from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.wheel_drive_type is null;
|
|
update fw_ods.fw_ods_detailed a set platform = m.platform from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.platform is null;
|
|
update fw_ods.fw_ods_detailed a set tractor_make = m.tractor_make from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.tractor_make is null;
|
|
|
|
|
|
--f_ods_observations
|
|
update fw_ods.fw_ods_observations set tractor_model=REGEXP_REPLACE(tractor_model,'\r|\n','','g');
|
|
|
|
update fw_ods.fw_ods_observations set tractor_make = m.target_value from fw_ods.fw_ods_misnomer m where trim(tractor_make) = m.source_value ;
|
|
update fw_ods.fw_ods_observations set tractor_model = m.target_value from fw_ods.fw_ods_misnomer m where trim(tractor_model) = m.source_value ;
|
|
update fw_ods.fw_ods_observations set wheel_drive_type = m.target_value from fw_ods.fw_ods_misnomer m where trim(wheel_drive_type) = m.source_value ;
|
|
update fw_ods.fw_ods_observations set name_of_implement = m.target_value from fw_ods.fw_ods_misnomer m where trim(name_of_implement) = m.source_value ;
|
|
update fw_ods.fw_ods_observations set type_of_implement = m.target_value from fw_ods.fw_ods_misnomer m where trim(type_of_implement) = m.source_value ;
|
|
|
|
|
|
|
|
|
|
update fw_ods.fw_ods_observations a set "configuration" = m."configuration" from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a."configuration" is null;
|
|
update fw_ods.fw_ods_observations a set tractor_engine_hp = m.tractor_engine_hp from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.tractor_engine_hp is null;
|
|
update fw_ods.fw_ods_observations a set wheel_drive_type=m.wheel_drive_type from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.wheel_drive_type is null;
|
|
update fw_ods.fw_ods_observations a set platform = m.platform from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.platform is null;
|
|
update fw_ods.fw_ods_observations a set tractor_make = m.tractor_make from fw_ods.fw_ods_tractor_model_info m where a.tractor_model = m.tractor_model and a.tractor_make is null;
|
|
|
|
|
|
|
|
-- Additional Updates:
|
|
|
|
-- Gear Used:
|
|
update fw_ods.fw_ods set gear_used = m.target_value from fw_ods.fw_ods_misnomer m where trim(gear_used) = m.source_value;
|
|
|
|
update fw_ods.fw_ods_comments set gear_used = m.target_value from fw_ods.fw_ods_misnomer m where trim(gear_used) = m.source_value;
|
|
|
|
update fw_ods.fw_ods_drawbar_performance set gear_used = m.target_value from fw_ods.fw_ods_misnomer m where trim(gear_used) = m.source_value;
|
|
|
|
update fw_ods.fw_ods_observations set gear_used = m.target_value from fw_ods.fw_ods_misnomer m where trim(gear_used) = m.source_value;
|
|
|
|
-- Sensor body Location:
|
|
|
|
update fw_ods.fw_ods set sensor_body_location = m.target_value from fw_ods.fw_ods_misnomer m where trim(sensor_body_location) = m.source_value;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
update fw_core.fw_jobctl_runschedule_jobstep set end_status_note = 'ODS_completed', end_status='success', end_time=now() where job_id = p_job_id and step_id = p_step_id and latest_runschedule_flag = '1';
|
|
|
|
end
|
|
$$
|
|
LANGUAGE plpgsql;
|
|
|
|
|