drop function if exists mmt_staging2.fn_IHT_BRT_ODS; CREATE OR REPLACE FUNCTION mmt_staging2.fn_IHT_BRT_ODS() RETURNS void AS $$ declare __test_instance_id int; declare __brake_perf_test_condition_id int; declare __file_syspk int; declare __model text; declare __make text; begin /************************************************************ Function Name:fn_IHT_BRT_ODS Function Desc: This function populates data into ODS File Format: IHT Sheet Format: IHT_BRT Creation Date: Updation Date: Author: compegence team Function Call: select mmt_staging2.fn_IHT_BRT_ODS() ***************************************************************/ delete from mmt_ods.test_instance where test_file_sheet_format='IHT_BRT'; delete from mmt_ods.test_instance_tractor_info where test_file_sheet_format='IHT_BRT'; delete from mmt_ods.test_instance_drawbar_info where test_file_sheet_format='IHT_BRT'; delete from mmt_ods.test_instance_weight_reaction where test_file_sheet_format='IHT_BRT'; delete from mmt_ods.test_instance_tyre_info where test_file_sheet_format='IHT_BRT'; delete from mmt_ods.iht_brake_perf_test_condition where test_file_sheet_format='IHT_BRT'; delete from mmt_ods.iht_brake_perf_test_results where test_file_sheet_format='IHT_BRT'; 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, job_order_no, sample_receipt_date, test_report_no, generation, customer_name, test_engineer, test_report_date, no_of_sample, test_start_date, test_end_date, tractor_sr_no, test_standard_ref, test_location_name, test_operator, project_group, objective_of_test, test_condition, test_standard_desc, test_standard_id, acceptance_criteria, remarks, observations ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, Test_Request_no, to_date(sample_receipt_date,'DD-MM-YYYY'), Test_report_No, Generation, Customer_Name, Test_Engineer, to_date(Test_Report_Date,'DD-MM-YYYY'), No_of_Sample, to_date(Test_Start_Date,'DD-MM-YYYY'), to_date(Test_End_Date, 'DD-MM-YYYY'), Tractor_Sr_No, Test_Standard_Refer, test_location, Operator_Name, Project_Group, Objective, condition, test_standard, test_standard_id::numeric, acceptance_criteria, remarks, observations from mmt_staging2.IHT_BRT_H1_block; update mmt_ods.test_instance a set report_prepared_date=to_date(b.prepared_date,'DD-MM-YYYY'), report_reviewed_date=to_date(b.reviewed_date,'DD-MM-YYYY'), report_approved_date=to_date(b.approved_date,'DD-MM-YYYY'), report_prepared_by=b.prepared_by, report_reviewed_by=b.reviewed_by, report_approved_by=b.approved_by, report_template_replaces=b.replaces, report_title=b.comments, report_template_no=b.rev1, report_template_rev_date=b.rev2, report_template_rev_no= b.rev3 from mmt_staging2.IHT_BRT_footer_block b where a.test_file_sheet_format='IHT_BRT'; insert into mmt_ods.test_instance_tyre_info ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, tyre_type, tyre_make, tyre_size, tyre_ply_rating, tyre_use_type, tyre_pressure_kg_per_cm2 ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, tyre_details, tyre_make, tyre_size, ply_rating::numeric, tyre_use_type, pressure_kg_cm2::numeric from mmt_staging2.IHT_BRT_tyre_details_block; insert into mmt_ods.test_instance_drawbar_info ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, test_condition, weight_front_kg, weight_rear_kg, weight_total_kg, status ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, test_condition, front::numeric , rear::numeric , total::numeric , status from mmt_staging2.IHT_BRT_weight_block; /*block */ 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_platform ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, platform from mmt_staging2.iht_brt_h1_block; insert into mmt_ods.iht_brake_perf_test_condition ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, date_of_test, time_of_test, location_name, test_mode ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, column3::date, column8, column6, column10 from mmt_staging2.iht_brt_test_res_block where rank =1; insert into mmt_ods.iht_brake_perf_test_condition ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, date_of_test, time_of_test, location_name, test_mode ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, column3::date, column6, column8, column10 from mmt_staging2.iht_brt_test_res_block where rank =16; insert into mmt_ods.iht_brake_perf_test_condition ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, date_of_test, location_name, time_of_test, test_mode ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, column3::date, column6, column8, column10 from mmt_staging2.iht_brt_test_res_1_block where rank =1; insert into mmt_ods.iht_brake_perf_test_results ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, test_mode, srl_no, trigger_speed_km_per_h, brake_control_input_force_kg, required_corrected_stopping_distance_m, actual_stopping_distance_m, deceleration_m_s2_v2_2s, wind_velocity_kmph, ambient_temp_c, humidity_rh_pct, observation ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, column2, column3::numeric, column4::numeric, column5::numeric, column6::numeric, column7::numeric, column8::numeric, column9, column10::numeric, column11::numeric, column12 from mmt_staging2.iht_brt_test_res_block where ods_record =1; insert into mmt_ods.iht_brake_perf_test_results ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, test_mode, srl_no, trigger_speed_km_per_h, brake_control_input_force_kg, required_corrected_stopping_distance_m, actual_stopping_distance_m, deceleration_m_s2_v2_2s, wind_velocity_kmph, ambient_temp_c, humidity_rh_pct, observation ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, column2, column3::numeric, column4::numeric, column5::numeric, column6::numeric, column7::numeric, column8::numeric, column9, column10::numeric, column11::numeric, column12 from mmt_staging2.iht_brt_test_res_1_block where column3 is not null and column4 is not null and ods_record =1; update mmt_ods.iht_brake_perf_test_results a set brake_perf_test_condition_id=(select syspk from mmt_ods.iht_brake_perf_test_condition b where a.test_mode =b.test_mode); select file_syspk into __file_syspk from mmt_staging2.IHT_BRT_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_tyre_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.test_instance_drawbar_info set test_instance_id=__test_instance_id, tractor_model =__model, tractor_make=__make where test_file_ref_no=__file_syspk; update mmt_ods.iht_brake_perf_test_condition set test_instance_id=__test_instance_id, tractor_model =__model, tractor_make=__make where test_file_ref_no=__file_syspk; update mmt_ods.iht_brake_perf_test_results set test_instance_id=__test_instance_id, tractor_model =__model, tractor_make=__make where test_file_ref_no=__file_syspk; end $$ LANGUAGE plpgsql;