drop function if exists staging2.fn_PTO_PRF_TRX; CREATE OR REPLACE FUNCTION staging2.fn_PTO_PRF_TRX(p_client_id int,p_function_id int, p_file_mnemonic text, p_file_sheet_mnemonic text, p_file_syspk int) RETURNS text AS $$ declare __test_instance_id int; declare __test_instance_tractor_id int; declare __client_id int :=p_client_id; declare __function_id int :=p_function_id; declare __file_mnemonic text :=p_file_mnemonic; declare __file_sheet_mnemonic text :=p_file_sheet_mnemonic; declare __file_syspk int :=p_file_syspk; declare __make text; declare __model text; declare err_state text; declare err_msg text; declare err_detail text; declare err_hint text; declare err_context text; declare _error int; declare __test_master_id int; begin /*********************************************************** Function Name:fn_PTO_PNA_TRX Function Desc: This function populates data into ODS blocks File Format: PTOBST Sheet Format: PTOBST_PNA Creation Date: April 27 2021 Updation Date: Author: compegence team Function Call: select staging2.fn_PTO_PRF_TRX(907) ***************************************************************/ SET search_path TO staging2; select syspk from transactional.test_master into __test_master_id where test_type ='PTO'; insert into transactional.test_instance ( client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, tractor_make, tractor_model, tractor_sr_no, test_standard_ref ) select client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, make, model, tractor_serial_number, test_standard from staging2.ptoben_prf_tractor_specs_block where trx_record=1; insert into transactional.test_instance_tractor_info ( client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, tractor_make, tractor_model, tractor_sr_no ) select client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, make, model, tractor_serial_number from staging2.ptoben_prf_tractor_specs_block where trx_record=1; update transactional.test_instance a set (test_location_name,test_engineer,date_of_test) = (select place,test_engineer,test_date::date from staging2.PTOBEN_PRF_Key_Performance_Parameters_block where file_syspk = __file_syspk and test_condition='observed') where file_syspk = __file_syspk; update transactional.test_instance set test_file_name =(select a.file_name from staging1.staging_generic_table a where file_syspk = __file_syspk limit 1) where file_syspk = __file_syspk; update transactional.test_instance_tractor_info a set (gear_ratio) = (select engine_to_pto_ratio::numeric from staging2.PTOBEN_PRF_Key_Performance_Parameters_block where file_syspk = __file_syspk and test_condition='observed') where file_syspk = __file_syspk; insert into transactional.PTO_Key_Perf_Parameters ( client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, tractor_model, tractor_make, tractor_serial_number, tractor_emission, test_standard, pto_gear_ratio, specific_gravity_of_fuel_kg_per_m3, pto_power_declared_hp, pto_power_declared_kw, pto_sfc_declared_gm_per_hp_hr, pto_sfc_declared_kg_per_kw_hr, pct_of_backup_torque_declared, max_equ_crankshaft_torque_declared_nm, equ_crankshaft_torque_at_max_power_declared_nm, engine_speed_at_max_equ_crankshaft_torque_declared_rpm, rated_engine_speed_declared_rpm, max_torque_engine_speed_declared_rpm, engine_high_idle_speed_declared_rpm, engine_low_idle_speed_declared_rpm, engine_oil_temp_at_na_declared_c, coolant_temp_at_na_declared_c, engine_oil_temp_at_ha_declared_c, coolant_temp_at_ha_declared_c, ambient_pressure_declared_bar, ambient_pressure_declared_mm_of_hg, back_pressure_declared_bar, back_pressure_declared_mm_of_hg, engine_oil_pressure_declared_bar, exhaust_temp_declared_c, relative_humidity_declared_pct, coolant_water_pct_of_total_coolant_capacity_declared, test_engineer,test_bed,location_name,total_run_hrs,test_date,tooled_up, created_by ) select client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, model, make, tractor_serial_number, tractor_emmission, test_standard, engine_to_pto_ratio::numeric, specific_gravity_of_fuel::numeric, pto_power_hp, pto_power_kw, pto_sfc_gm_hp, pto_sfc_kw_hp, pct_of_backup_torque, maximum_equ_crankshaft_torque_nm, equ_crankshaft_torque_maximum_power_nm, engine_speed_at_maximum_equivalent_crankshaft_torque_rpm, rated_engine_speed, max_torque_engine_speed, engine_high_idle_speed, engine_low_idle_speed, engine_oil_temp_na, coolant_temp_na, engine_oil_temp_ha, coolant_temp_ha, ambient_pressure_bar, ambient_pressure_mm_of_hg, back_pressure_bar, back_pressure_mm_of_hg, engine_oil_pressure_bar, exhaust_temperature_c, relative_humidity_pct, coolant_water_pct, test_engineer,test_bed,place,total_run_hrs::int, test_date::date , tooled_up, 'Compegence' from staging2.ptoben_prf_key_performance_parameters_block where trx_record=1 and test_condition ='declared'; update transactional.PTO_Key_Perf_Parameters a set (pto_power_observed_hp,pto_power_observed_kw,pto_sfc_observed_gm_per_hp_hr,pto_sfc_observed_kg_per_kw_hr, pct_of_backup_torque_observed,max_equ_crankshaft_torque_observed_nm,equ_crankshaft_torque_at_max_power_observed_nm, engine_speed_at_max_equ_crankshaft_torque_observed_rpm,rated_engine_speed_observed_rpm ,max_torque_engine_speed_observed_rpm,engine_high_idle_speed_observed_rpm,engine_low_idle_speed_observed_rpm, engine_oil_temp_at_na_observed_c,coolant_temp_at_na_observed_c,engine_oil_temp_at_ha_observed_c,coolant_temp_at_ha_observed_c, ambient_pressure_observed_bar,ambient_pressure_observed_mm_of_hg,back_pressure_observed_bar,back_pressure_observed_mm_of_hg, engine_oil_pressure_observed_bar,exhaust_temp_observed_c,relative_humidity_observed_pct,coolant_water_pct_of_total_coolant_capacity_observed) = (select pto_power_hp::numeric, pto_power_kw::numeric, pto_sfc_gm_hp::numeric, pto_sfc_kw_hp::numeric, pct_of_backup_torque::numeric, maximum_equ_crankshaft_torque_nm::numeric, equ_crankshaft_torque_maximum_power_nm::numeric, engine_speed_at_maximum_equivalent_crankshaft_torque_rpm::numeric, rated_engine_speed::numeric, max_torque_engine_speed::numeric, engine_high_idle_speed::numeric, engine_low_idle_speed::numeric, engine_oil_temp_na::numeric, coolant_temp_na::numeric, engine_oil_temp_ha::numeric, coolant_temp_ha::numeric, ambient_pressure_bar::numeric, ambient_pressure_mm_of_hg::numeric, back_pressure_bar::numeric, back_pressure_mm_of_hg::numeric, engine_oil_pressure_bar::numeric, exhaust_temperature_c::numeric, relative_humidity_pct::numeric, coolant_water_pct::numeric from staging2.PTOBEN_PRF_Key_Performance_Parameters_block where test_condition='observed') where file_syspk = __file_syspk; update transactional.PTO_Key_Perf_Parameters set test_mode = 'Normal' where file_sheet_mnemonic = 'PTOBEN_PRFN' and file_syspk = __file_syspk; update transactional.PTO_Key_Perf_Parameters set test_mode = 'Boost' where file_sheet_mnemonic = 'PTOBEN_PRFB' and file_syspk = __file_syspk; update transactional.PTO_Key_Perf_Parameters set test_mode = 'Eco' where file_sheet_mnemonic = 'PTOBEN_PRFE' and file_syspk = __file_syspk; update transactional.PTO_Key_Perf_Parameters set test_mode = 'Standard' where file_sheet_mnemonic = 'PTOSTD_PRF' and file_syspk = __file_syspk; /* block */ insert into transactional.PTO_Perf_Results ( client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, tractor_model, tractor_make, pto_test_type, serial_number, speed_engine_rpm, speed_pto_rpm, brake_torque_n_m, brake_torque_kg_m, power_kw, power_hp, eq_crank_torque_nm, fuel_consumption_kg_per_hr, fuel_consumption_gm_per_hp_hr, fuel_consumption_kg_per_kwh, fuel_consumption_ltr_per_hr, fuel_consumption_mm3_per_str_per_cyl, sp_energy_kwh_per_ltr, temperatures_fuel_c, temperatures_ambient_c, temperatures_air_intake_c, temperatures_water_outlet_c, temperatures_exhaust_c, temperatures_engine_oil_c, pressure_ambient_bar, pressure_ambient_mm_of_hg, pressure_exhaust_bar, pressure_exhaust_mm_of_hg, pressure_lub_oil_bar, pressure_lub_oil_mm_of_hg, rh_pct ) select client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, model, make, test_condition, c2, c3::numeric,c4::numeric,c5::numeric,c6::numeric,c7::numeric,c8::numeric,c9::numeric,c10::numeric, c11::numeric, c12::numeric,c13::numeric,c14::numeric,c15::numeric,c16::numeric,c17::numeric,c18::numeric, c19::numeric,c20::numeric,c21::numeric,c22::numeric,c23::numeric,c24::numeric,c25::numeric, c26::numeric,c27::numeric,c28::numeric from staging2.ptoben_prf_varying_speed_natural_ambient_block where trx_record=1; /* block */ insert into transactional.PTO_Perf_Results ( client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, tractor_model, tractor_make, pto_test_type, serial_number, speed_engine_rpm, speed_pto_rpm, brake_torque_n_m, brake_torque_kg_m, power_kw, power_hp, eq_crank_torque_nm, fuel_consumption_kg_per_hr, fuel_consumption_gm_per_hp_hr, fuel_consumption_kg_per_kwh, fuel_consumption_ltr_per_hr, fuel_consumption_mm3_per_str_per_cyl, sp_energy_kwh_per_ltr, temperatures_fuel_c, temperatures_ambient_c, temperatures_air_intake_c, temperatures_water_outlet_c, temperatures_exhaust_c, temperatures_engine_oil_c, pressure_ambient_bar, pressure_ambient_mm_of_hg, pressure_exhaust_bar, pressure_exhaust_mm_of_hg, pressure_lub_oil_bar, pressure_lub_oil_mm_of_hg, rh_pct ) select client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, model, make, test_condition, c2, c3::numeric,c4::numeric,c5::numeric,c6::numeric,c7::numeric,c8::numeric,c9::numeric,c10::numeric, c11::numeric, c12::numeric,c13::numeric,c14::numeric,c15::numeric,c16::numeric,c17::numeric,c18::numeric, c19::numeric,c20::numeric,c21::numeric,c22::numeric,c23::numeric,c24::numeric,c25::numeric, c26::numeric,c27::numeric,c28::numeric from staging2.ptoben_prf_2hrs_max_power_natural_ambient_block where trx_record=1; update transactional.PTO_Perf_Results set average_yn = CASE WHEN serial_number = 'Avg' THEN 'Y' end where pto_test_type = '2Hrs Max Power Test @ Natural Ambient'; /* block */ insert into transactional.PTO_Perf_Results ( client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, tractor_model, tractor_make, pto_test_type, pto_test_sub_type, serial_number, speed_engine_rpm, speed_pto_rpm, brake_torque_n_m, brake_torque_kg_m, power_kw, power_hp, eq_crank_torque_nm, fuel_consumption_kg_per_hr, fuel_consumption_gm_per_hp_hr, fuel_consumption_kg_per_kwh, fuel_consumption_ltr_per_hr, fuel_consumption_mm3_per_str_per_cyl, sp_energy_kwh_per_ltr, temperatures_fuel_c, temperatures_ambient_c, temperatures_air_intake_c, temperatures_water_outlet_c, temperatures_exhaust_c, temperatures_engine_oil_c, pressure_ambient_bar, pressure_ambient_mm_of_hg, pressure_exhaust_bar, pressure_exhaust_mm_of_hg, pressure_lub_oil_bar, pressure_lub_oil_mm_of_hg, rh_pct ) select client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, model, make, test_condition, c3, c2, c1::numeric,c4::numeric,c5::numeric,c6::numeric,c7::numeric,c8::numeric,c9::numeric,c10::numeric, c11::numeric, c12::numeric,c13::numeric,c14::numeric,c15::numeric,c16::numeric,c17::numeric,c18::numeric, c19::numeric,c20::numeric,c21::numeric,c22::numeric,c23::numeric,c24::numeric,c25::numeric, c26::numeric,c27::numeric,c28::numeric from staging2.ptoben_prf_varying_load_rated_rpm_block where trx_record=1; /* block */ insert into transactional.PTO_Perf_Results ( client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, tractor_model, tractor_make, pto_test_type, pto_test_sub_type, serial_number, speed_engine_rpm, speed_pto_rpm, brake_torque_n_m, brake_torque_kg_m, power_kw, power_hp, eq_crank_torque_nm, fuel_consumption_kg_per_hr, fuel_consumption_gm_per_hp_hr, fuel_consumption_kg_per_kwh, fuel_consumption_ltr_per_hr, fuel_consumption_mm3_per_str_per_cyl, sp_energy_kwh_per_ltr, temperatures_fuel_c, temperatures_ambient_c, temperatures_air_intake_c, temperatures_water_outlet_c, temperatures_exhaust_c, temperatures_engine_oil_c, pressure_ambient_bar, pressure_ambient_mm_of_hg, pressure_exhaust_bar, pressure_exhaust_mm_of_hg, pressure_lub_oil_bar, pressure_lub_oil_mm_of_hg, rh_pct ) select client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, model, make, test_condition, c3, c2, c1::numeric,c4::numeric,c5::numeric,c6::numeric,c7::numeric,c8::numeric,c9::numeric,c10::numeric, c11::numeric, c12::numeric,c13::numeric,c14::numeric,c15::numeric,c16::numeric,c17::numeric,c18::numeric, c19::numeric,c20::numeric,c21::numeric,c22::numeric,c23::numeric,c24::numeric,c25::numeric, c26::numeric,c27::numeric,c28::numeric from staging2.ptoben_prf_varying_load_std_pto_block where trx_record=1; /* block */ insert into transactional.PTO_Perf_Results ( client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, tractor_model, tractor_make, pto_test_type, serial_number, speed_engine_rpm, speed_pto_rpm, brake_torque_n_m, brake_torque_kg_m, power_kw, power_hp, eq_crank_torque_nm, fuel_consumption_kg_per_hr, fuel_consumption_gm_per_hp_hr, fuel_consumption_kg_per_kwh, fuel_consumption_ltr_per_hr, fuel_consumption_mm3_per_str_per_cyl, sp_energy_kwh_per_ltr, temperatures_fuel_c, temperatures_ambient_c, temperatures_air_intake_c, temperatures_water_outlet_c, temperatures_exhaust_c, temperatures_engine_oil_c, pressure_ambient_bar, pressure_ambient_mm_of_hg, pressure_exhaust_bar, pressure_exhaust_mm_of_hg, pressure_lub_oil_bar, pressure_lub_oil_mm_of_hg, rh_pct ) select client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, model, make, test_condition, c2, c3::numeric,c4::numeric,c5::numeric,c6::numeric,c7::numeric,c8::numeric,c9::numeric,c10::numeric, c11::numeric, c12::numeric,c13::numeric,c14::numeric,c15::numeric,c16::numeric,c17::numeric,c18::numeric, c19::numeric,c20::numeric,c21::numeric,c22::numeric,c23::numeric,c24::numeric,c25::numeric, c26::numeric,c27::numeric,c28::numeric from staging2.ptoben_prf_varying_speed_test_high_ambient_block where trx_record=1; insert into transactional.PTO_Perf_Results ( client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, tractor_model, tractor_make, pto_test_type, serial_number, speed_engine_rpm, speed_pto_rpm, brake_torque_n_m, brake_torque_kg_m, power_kw, power_hp, eq_crank_torque_nm, fuel_consumption_kg_per_hr, fuel_consumption_gm_per_hp_hr, fuel_consumption_kg_per_kwh, fuel_consumption_ltr_per_hr, fuel_consumption_mm3_per_str_per_cyl, sp_energy_kwh_per_ltr, temperatures_fuel_c, temperatures_ambient_c, temperatures_air_intake_c, temperatures_water_outlet_c, temperatures_exhaust_c, temperatures_engine_oil_c, pressure_ambient_bar, pressure_ambient_mm_of_hg, pressure_exhaust_bar, pressure_exhaust_mm_of_hg, pressure_lub_oil_bar, pressure_lub_oil_mm_of_hg, rh_pct ) select client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, model, make, test_condition, c2, c3::numeric,c4::numeric,c5::numeric,c6::numeric,c7::numeric,c8::numeric,c9::numeric,c10::numeric, c11::numeric, c12::numeric,c13::numeric,c14::numeric,c15::numeric,c16::numeric,c17::numeric,c18::numeric, c19::numeric,c20::numeric,c21::numeric,c22::numeric,c23::numeric,c24::numeric,c25::numeric, c26::numeric,c27::numeric,c28::numeric from staging2.ptoben_prf_2hrs_max_power_test_high_ambient_block where trx_record=1; update transactional.PTO_Perf_Results set test_mode = 'Normal' where file_sheet_mnemonic = 'PTOBEN_PRFN' and file_syspk = __file_syspk; update transactional.PTO_Perf_Results set test_mode = 'Boost' where file_sheet_mnemonic = 'PTOBEN_PRFB' and file_syspk = __file_syspk; update transactional.PTO_Perf_Results set test_mode = 'Eco' where file_sheet_mnemonic = 'PTOBEN_PRFE' and file_syspk = __file_syspk; update transactional.PTO_Perf_Results set test_mode = 'Standard' where file_sheet_mnemonic = 'PTOSTD_PRF' and file_syspk = __file_syspk; update transactional.PTO_Perf_Results set average_yn = CASE WHEN serial_number = 'Avg' THEN 'Y' end where pto_test_type = '2Hrs Max Power Test @ High Ambient'; /* block */ insert into transactional.pto_smoke_test ( client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, tractor_make, tractor_model, srl_no, engine_speed_rpm, pto_speed_rpm , torque_100_pct , torque_80_pct , smoke_fsn_100_pct , smoke_fsn_80_pct ) select client_id, function_id, file_syspk, file_mnemonic, file_sheet_mnemonic, make, model, c2::numeric,c3::numeric,c4::numeric,c5::numeric,c6::numeric,c7::numeric,c8::numeric from staging2.ptoben_prf_smoke_test_block ppstb where trx_record=1 ; update transactional.pto_smoke_test set test_mode = 'Normal' where file_sheet_mnemonic = 'PTOBEN_PRFN' and file_syspk = __file_syspk; update transactional.pto_smoke_test set test_mode = 'Boost' where file_sheet_mnemonic = 'PTOBEN_PRFB' and file_syspk = __file_syspk; update transactional.pto_smoke_test set test_mode = 'Eco' where file_sheet_mnemonic = 'PTOBEN_PRFE' and file_syspk = __file_syspk; update transactional.pto_smoke_test set test_mode = 'Standard' where file_sheet_mnemonic = 'PTOSTD_PRF' and file_syspk = __file_syspk; insert into transactional.pto_engine_oil_consumption ( client_id,function_id,file_syspk,file_mnemonic,file_sheet_mnemonic, tractor_make, tractor_model, srl_no, oil_consumption_gm_per_hr, oil_consumption_pct_of_fuel_consumption ) select client_id,function_id,file_syspk,file_mnemonic,file_sheet_mnemonic,make,model, c2::numeric,c3::numeric,c5::numeric from staging2.ptoben_prf_engine_oil_consumption_block ppeocb where trx_record=1; update transactional.pto_engine_oil_consumption set test_mode = 'Normal' where file_sheet_mnemonic = 'PTOBEN_PRFN' and file_syspk = __file_syspk; update transactional.pto_engine_oil_consumption set test_mode = 'Boost' where file_sheet_mnemonic = 'PTOBEN_PRFB' and file_syspk = __file_syspk; update transactional.pto_engine_oil_consumption set test_mode = 'Eco' where file_sheet_mnemonic = 'PTOBEN_PRFE' and file_syspk = __file_syspk; update transactional.pto_engine_oil_consumption set test_mode = 'Standard' where file_sheet_mnemonic = 'PTOSTD_PRF' and file_syspk = __file_syspk; update transactional.test_instance set test_mode = 'Normal' where file_sheet_mnemonic = 'PTOBEN_PRFN' and file_syspk = __file_syspk; update transactional.test_instance set test_mode = 'Boost' where file_sheet_mnemonic = 'PTOBEN_PRFB' and file_syspk = __file_syspk; update transactional.test_instance set test_mode = 'Eco' where file_sheet_mnemonic = 'PTOBEN_PRFE' and file_syspk = __file_syspk; update transactional.test_instance set test_mode = 'Standard' where file_sheet_mnemonic = 'PTOSTD_PRF' and file_syspk = __file_syspk; select syspk into __test_instance_id from transactional.test_instance where file_syspk =__file_syspk and file_sheet_mnemonic =__file_sheet_mnemonic; select syspk into __test_instance_tractor_id from transactional.test_instance_tractor_info where file_syspk =__file_syspk and file_sheet_mnemonic =__file_sheet_mnemonic; update transactional.test_instance set test_tractor_id = __test_instance_tractor_id, test_master_id = __test_master_id where file_syspk=__file_syspk and file_sheet_mnemonic =__file_sheet_mnemonic; update transactional.PTO_Key_Perf_Parameters set test_instance_id=__test_instance_id, test_instance_tractor_id = __test_instance_tractor_id where file_syspk=__file_syspk and file_sheet_mnemonic =__file_sheet_mnemonic; update transactional.test_instance_tractor_info set test_instance_id=__test_instance_id where file_syspk=__file_syspk and file_sheet_mnemonic =__file_sheet_mnemonic; update transactional.PTO_Perf_Results set test_instance_id=__test_instance_id, test_instance_tractor_id = __test_instance_tractor_id where file_syspk=__file_syspk; update transactional.pto_engine_oil_consumption set test_instance_id=__test_instance_id, test_instance_tractor_id = __test_instance_tractor_id where file_syspk=__file_syspk; update transactional.pto_smoke_test set test_instance_id=__test_instance_id, test_instance_tractor_id = __test_instance_tractor_id where file_syspk=__file_syspk; update transactional.test_instance set test_type = a.test_type, test_name= a.test_name from fw_core.test_file_sheet_format_master a where transactional.test_instance.file_sheet_mnemonic = a.file_sheet_mnemonic and transactional.test_instance.file_syspk=__file_syspk; err_context := ''; perform fw_core.fn_insert_db_error ( __client_id, __function_id, 1001, 'Compegence', __file_syspk ,__file_mnemonic,__file_sheet_mnemonic ,null,'trx', 'fn_PTO_PRF_TRX', err_state, err_msg, err_detail, err_hint, err_context,'success'); return err_context; EXCEPTION when OTHERS then GET STACKED DIAGNOSTICS err_state = returned_sqlstate, err_msg = message_text, err_detail = pg_exception_detail, err_hint = pg_exception_hint, err_context = pg_exception_context; perform fw_core.fn_insert_db_error (__client_id, __function_id, 1001, 'Compegence', __file_syspk ,__file_mnemonic,__file_sheet_mnemonic ,null,'trx', 'fn_PTO_PRF_TRX', err_state, err_msg, err_detail, err_hint, err_context,'error'); return err_context; end $$ LANGUAGE plpgsql;