drop function if exists mmt_staging2.fn_BUDNI_NMT_ODS; CREATE OR REPLACE FUNCTION mmt_staging2.fn_BUDNI_NMT_ODS(p_file_syspk int) RETURNS text AS $$ declare __test_instance_id int; declare __file_syspk int; declare __model text; declare __make text; declare v_state text; declare v_msg text; declare v_detail text; declare v_hint text; declare v_context text; declare _error int; begin __file_syspk := p_file_syspk; /************************************************************ Function Name:fn_BUDNI_NMT_ODS Function Desc: This function populates data into ODS File Format: BUDNI Sheet Format: BUDNI_NMT Creation Date: Updation Date: Author: compegence team Function Call: select mmt_staging2.fn_BUDNI_NMT_ODS() ***************************************************************/ --delete from mmt_ods.test_instance where test_file_sheet_format='BUDNI_NMT'; -- --delete from mmt_ods.test_instance_engine_info where test_file_sheet_format='BUDNI_NMT'; -- --delete from mmt_ods.test_instance_tractor_info where test_file_sheet_format='BUDNI_NMT'; -- --delete from mmt_ods.budni_noise_atmospheric_conditions where test_file_sheet_format='BUDNI_NMT'; -- --delete from mmt_ods.budni_noise_measurement_results where test_file_sheet_format='BUDNI_NMT'; -- --delete from mmt_ods.budni_test_observations where test_file_sheet_format='BUDNI_NMT'; insert into mmt_ods.test_instance_tractor_info ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, tractor_engine_hp, configuration, transmission_type, wheel_drive_type, fip_type, steering_type, ballast_condition ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make, model, tractor_HP::int tractor_engine_hp, configuration, transmission_type, wheel_drive_type, FIP_type, steering_type, Ballast_Condition from mmt_staging2.BUDNI_NMT_Spec_H1_block; insert into mmt_ods.test_instance ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, type_of_road ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, type_of_track from mmt_staging2.BUDNI_NMT_Spec_H1_block; insert into mmt_ods.test_instance_engine_info ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, rated_rpm, engine_to_pto_ratio_540_pto ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, rated_rpm::int, engine_to_pto_ratio engine_to_pto_ratio_540_pto from mmt_staging2.BUDNI_NMT_Spec_H1_block; /*block */ insert into mmt_ods.budni_noise_atmospheric_conditions ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, noise_position, background_noise_level_dba, temperature_c, pressure_kpa, relative_humidity_pct, wind_velocity_ms ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, column2, column3::numeric, column4::numeric, column5::numeric, column6::numeric, column7::numeric from mmt_staging2.BUDNI_NMT_Atmos_Cond_1_Block where ods_record=1 order by rank; /*block */ insert into mmt_ods.budni_noise_measurement_results ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, noise_position, gear, travel_speed_km_per_hr, noise_level_db_a ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, column2, column3, column6, column8::numeric from mmt_staging2.BUDNI_NMT_Meas_Results_1_Block where ods_record=1; insert into mmt_ods.budni_noise_atmospheric_conditions ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, noise_position, background_noise_level_dba, temperature_c, pressure_kpa, relative_humidity_pct, wind_velocity_ms ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, column2, column3::numeric, column4::numeric, column5::numeric , column6::numeric, column7::numeric from mmt_staging2.BUDNI_NMT_Atmos_Cond_2_Block where ods_record=1 order by rank; /*block */ insert into mmt_ods.budni_noise_measurement_results ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, noise_position, gear, drawbar_pull_max_noise_kn, travel_speed_km_per_hr, noise_level_db_a ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, column2, column3, column4, column6, column8::int from mmt_staging2.BUDNI_NMT_Meas_Results_2_Block where ods_record=1; insert into mmt_ods.budni_test_observations ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, sequence_number, characteristic, category_evaluative_or_non_evaluative, requirements_as_per_is_12207_2008, values_declared_by_the_applicant_d_rqmt, as_observed, whether_meets_the_requirements_yn ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, column3::numeric, column4, column5, column6, column7, column8, column9 from mmt_staging2.budni_nmt_test_obs_summary_block where ods_record=1; select file_syspk into __file_syspk from mmt_staging2.BUDNI_NMT_Spec_H1_block; select syspk into __test_instance_id from mmt_ods.test_instance where test_file_ref_no =__file_syspk; select tractor_model into __model from mmt_ods.test_instance where test_file_ref_no =__file_syspk; select tractor_make into __make from mmt_ods.test_instance where test_file_ref_no =__file_syspk; update mmt_ods.test_instance_engine_info set test_instance_id=__test_instance_id, tractor_model =__model, tractor_make=__make where test_file_ref_no=__file_syspk; update mmt_ods.test_instance_tractor_info set test_instance_id=__test_instance_id, tractor_model =__model, tractor_make=__make where test_file_ref_no=__file_syspk; update mmt_ods.budni_noise_atmospheric_conditions set test_instance_id=__test_instance_id, tractor_model =__model, tractor_make=__make where test_file_ref_no=__file_syspk; update mmt_ods.budni_noise_measurement_results set test_instance_id=__test_instance_id, tractor_model =__model, tractor_make=__make where test_file_ref_no=__file_syspk; update mmt_ods.budni_test_observations set test_instance_id=__test_instance_id, tractor_model =__model, tractor_make=__make where test_file_ref_no=__file_syspk; v_context := ''; perform mmt_staging2.mmt_insert_db_error ( null, null, 1001, 'Compegence', __file_syspk ,'BUDNI','BUDNI_NMT' ,null,'ods', 'fn_BUDNI_NMT_ODS', v_state, v_msg, v_detail, v_hint, v_context,'success'); return v_context; EXCEPTION when OTHERS then GET STACKED DIAGNOSTICS v_state = returned_sqlstate, v_msg = message_text, v_detail = pg_exception_detail, v_hint = pg_exception_hint, v_context = pg_exception_context; perform mmt_staging2.mmt_insert_db_error ( null, null, 1001, 'Compegence', __file_syspk ,'BUDNI','BUDNI_NMT' ,null,'ods', 'fn_BUDNI_NMT_ODS', v_state, v_msg, v_detail, v_hint, v_context,'error'); return v_context; end $$ LANGUAGE plpgsql;