drop function if exists mmt_staging2.fn_IHT_SLL_ODS; CREATE OR REPLACE FUNCTION mmt_staging2.fn_IHT_SLL_ODS() RETURNS void AS $$ declare __test_instance_id int; declare __file_syspk int; begin /************************************************************ Function Name:fn_IHT_SLL_ODS Function Desc: This function populates data into ODS File Format: IHT Sheet Format: IHT_SLL Creation Date: Updation Date: Author: compegence team Function Call: select mmt_staging2.fn_IHT_SLL_ODS() ***************************************************************/ delete from mmt_ods.test_instance where test_file_sheet_format='IHT_SLL'; delete from mmt_ods.test_instance_tractor_info where test_file_sheet_format='IHT_SLL'; delete from mmt_ods.test_instance_tyre_info where test_file_sheet_format='IHT_SLL'; delete from mmt_ods.test_instance_weight_reaction where test_file_sheet_format='IHT_SLL'; delete from mmt_ods.iht_gear_max_speed where test_file_sheet_format='IHT_SLL'; delete from mmt_ods.iht_speed_lead_lag_measurement where test_file_sheet_format='IHT_SLL'; 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, acceptance_criteria, remarks ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, Test_Request_no, date '1899-12-30' + sample_receipt_date::int * interval '1' day as Sample_Receipt_Date, Test_report_No, Generation, Customer_Name, Test_Engineer, date '1899-12-30' + Test_Report_Date::int * interval '1' day as Test_Report_Date, 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, Acceptance_Criteria, Remarks from mmt_staging2.IHT_SLL_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_SLL_footer_block b where a.test_file_sheet_format ='IHT_SLL'; 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_load_carrying_capacity, tyre_pressure_kg_per_cm2, tyre_static_rolling_radius_mm, tyre_dynamic_rolling_radius_mm, tyre_wheel_rim_make_and_size ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, tyre_details, tyre_make, tyre_size, ply_rating::numeric, load_carrying_capacity, Pressure_kg_cm2::numeric , Dynamic_rolling_radius::numeric , Static_rolling_radius::numeric , wheel_rim_make_size from mmt_staging2.IHT_SLL_tyre_details_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, wheel_base_mm, rear_tractor_width_mm ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, wheel_base::numeric , rear_track_width::numeric from mmt_staging2.IHT_SLL_wheel_block; /*block */ insert into mmt_ods.test_instance_weight_reaction ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, test_condition, total_weight_kg, front_reaction_kg, rear_reaction_kg, left_reaction_fl_rl_kg, right_reaction_fr_rr_kg, front_left_reaction_kg, front_right_reactionkg, rear_left_reaction_kg, rear_right_reactionkg, distance_of_lifting_point_from_rear_axle_mm_d1 ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, Weight_Reaction, Total_Weight_kg::numeric, Front_Reaction_kg::numeric , Rear_Reaction_kg::numeric , Left_Reaction_FL_RL_kg::numeric , Right_reaction_FR_RR_kg::numeric , Front_Left_Reaction_kg::numeric , Front_Right_Reaction_kg::numeric , Rear_Left_Reaction_kg::numeric , Rear_Right_Reaction_kg::numeric , Distance_of_lifting_point_from_rear_axle_mm_d1::numeric from mmt_staging2.IHT_SLL_weight_block; insert into mmt_ods.iht_gear_max_speed ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, test_condition, gear, low_1000_rpm, rated_2300_rpm, specification, high_2500_rpm, actual_speed_pct, diff_in_speed_kmph ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, column2, column3, column4::numeric , column5::numeric, column6::numeric, column7::numeric, column8::numeric, column9::numeric from mmt_staging2.iht_sll_forward_block where ods_record=1; insert into mmt_ods.iht_gear_max_speed ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, test_condition, gear, low_1000_rpm, rated_2300_rpm, specification, high_2500_rpm, actual_speed_pct, diff_in_speed_kmph ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, column2, column3, column4::numeric , column5::numeric, column6::numeric, column7::numeric, column8::numeric, column9::numeric from mmt_staging2.iht_sll_reverse_block where ods_record=1; insert into mmt_ods.iht_speed_lead_lag_measurement ( client_id, function_id, test_file_ref_no, test_file_format, test_file_sheet_format, tractor_make, tractor_model, gear, engine_rpm, wheel_drive_type, no_of_revolution_front, ratio, lead_lag_pct ) select client_id, function_id, file_syspk, file_format, sheet_mnemonic, make,model, column3, column4::numeric , column5, column6::numeric, column7::numeric, column8 from mmt_staging2.iht_sll_lead_lag_block where ods_record=1; select file_syspk into __file_syspk from mmt_staging2.IHT_SLL_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_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_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_weight_reaction set test_instance_id=__test_instance_id, tractor_model =__model, tractor_make=__make where test_file_ref_no=__file_syspk; update mmt_ods.iht_gear_max_speed set test_instance_id=__test_instance_id, tractor_model =__model, tractor_make=__make where test_file_ref_no=__file_syspk; update mmt_ods.iht_speed_lead_lag_measurement set test_instance_id=__test_instance_id, tractor_model =__model, tractor_make=__make where test_file_ref_no=__file_syspk; end $$ LANGUAGE plpgsql;