drop table mmt_staging2.test_output_BUDNI;

create table mmt_staging2.test_output_BUDNI
(
sno serial,
file_syspk int,
file_format text,
sheet_mnemonic text,
table_name text,
row_count int
);

drop function if exists mmt_staging2.fn_test_output_BUDNI;
CREATE OR REPLACE FUNCTION mmt_staging2.fn_test_output_BUDNI(p_file_syspk int)
   RETURNS table 
     (
       sno int,
       file_syspk int,
       file_format text,
       sheet_mnemonic text,
       table_name text,
       row_count int
     )
     
    as $$
    declare __file_syspk int :=p_file_syspk;
begin 
	
	
truncate table 	mmt_staging2.test_output_BUDNI;
	
execute 'insert into mmt_staging2.test_output_BUDNI
(file_syspk,file_format,sheet_mnemonic,table_name,row_count)
select test_file_ref_no,''BUDNI'',''BUDNI_ARC'',''test_instance'',count(*) 
from mmt_ods.test_instance 
where test_file_ref_no='||p_file_syspk||' and test_file_format =''BUDNI''
and test_file_sheet_format =''BUDNI_ARC''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_ARC'',''test_instance_engine_info'',count(*) 
from mmt_ods.test_instance_engine_info 
where test_file_ref_no='||p_file_syspk||' and test_file_format =''BUDNI''
and test_file_sheet_format =''BUDNI_ARC''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_ARC'',''test_instance_tractor_info'',count(*) 
from mmt_ods.test_instance_tractor_info
where test_file_ref_no='||p_file_syspk||' and test_file_format =''BUDNI''
and test_file_sheet_format =''BUDNI_ARC''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_ARC'',''budni_air_cleaner_perf_results'',count(*) 
from mmt_ods.budni_air_cleaner_perf_results 
where test_file_ref_no='||p_file_syspk||' and test_file_format =''BUDNI''
and test_file_sheet_format =''BUDNI_ARC''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_ARC'',''budni_test_observations'',count(*) 
from mmt_ods.budni_test_observations
where test_file_ref_no='||p_file_syspk||' and test_file_format =''BUDNI''
and test_file_sheet_format =''BUDNI_ARC''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_BRK'',''test_instance'',count(*)
from mmt_ods.test_instance
where test_file_ref_no='||p_file_syspk||'
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_BRK''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_BRK'',''test_instance_engine_info'',count(*)
from mmt_ods.test_instance_engine_info
where test_file_ref_no='||p_file_syspk||'
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_BRK''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_BRK'',''budni_brake_perf_parking_brake_test_results'',count(*)
from mmt_ods.budni_brake_perf_parking_brake_test_results
where test_file_ref_no='||p_file_syspk||'
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_BRK''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_BRK'',''budni_brake_perf_service_brake_test_results'',count(*)
from mmt_ods.budni_brake_perf_service_brake_test_results
where test_file_ref_no='||p_file_syspk||'
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_BRK''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_BRK'',''budni_test_observations'',count(*)
from mmt_ods.budni_test_observations
where test_file_ref_no='||p_file_syspk||'
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_BRK''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_DBP'',''test_instance'',count(*)
from mmt_ods.test_instance
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_DBP''
group by test_file_ref_no
union 
select test_file_ref_no,''BUDNI'',''BUDNI_DBP'',''test_instance_engine_info'',count(*)
from mmt_ods.test_instance_engine_info
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_DBP''
group by test_file_ref_no
union 
select test_file_ref_no,''BUDNI'',''BUDNI_DBP'',''test_instance_tractor_info'',count(*)
from mmt_ods.test_instance_tractor_info
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_DBP''
group by test_file_ref_no
union 
select test_file_ref_no,''BUDNI'',''BUDNI_DBP'',''budni_drawbar_perf_results'',count(*)
from mmt_ods.budni_drawbar_perf_results
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_DBP''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_FLD'',''test_instance'',count(*)
from mmt_ods.test_instance
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_FLD''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_FLD'',''test_instance_engine_info'',count(*)
from mmt_ods.test_instance_engine_info
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_FLD''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_FLD'',''test_instance_tractor_info'',count(*)
from mmt_ods.test_instance_tractor_info
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_FLD''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_FLD'',''test_instance_implement_info'',count(*)
from mmt_ods.test_instance_implement_info
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_FLD''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_FLD'',''budni_field_perf_results'',count(*)
from mmt_ods.budni_field_perf_results
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_FLD''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_FLD'',''budni_test_observations'',count(*)
from mmt_ods.budni_test_observations
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_FLD''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_HDL'',''test_instance'',count(*)
from mmt_ods.test_instance
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HDL''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_HDL'',''test_instance_engine_info'',count(*)
from mmt_ods.test_instance_engine_info
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HDL''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_HDL'',''test_instance_tractor_info'',count(*)
from mmt_ods.test_instance_tractor_info
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HDL''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_HDL'',''test_instance_tractor_info_misc'',count(*)
from mmt_ods.test_instance_tractor_info_misc
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HDL''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_HDL'',''budni_hydraulic_maint_of_lift_load_results'',count(*)
from mmt_ods.budni_hydraulic_maint_of_lift_load_results
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HDL''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_HDL'',''budni_hydraulic_power_test_results'',count(*)
from mmt_ods.budni_hydraulic_power_test_results
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HDL''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_HDL'',''budni_hydraulic_lifting_capacity_test_results'',count(*)
from mmt_ods.budni_hydraulic_lifting_capacity_test_results 
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HDL''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_HDL'',''budni_test_observations'',count(*)
from mmt_ods.test_instance_engine_info
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HDL''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_HLG'',''test_instance'',count(*)
from mmt_ods.test_instance
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HLG''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_HLG'',''test_instance_engine_info'',count(*)
from mmt_ods.test_instance_engine_info
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HLG''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_HLG'',''test_instance_tractor_info'',count(*)
from mmt_ods.test_instance_tractor_info
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HLG''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_HLG'',''budni_haulage_perf_results'',count(*)
from mmt_ods.budni_haulage_perf_results
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HLG''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_HLG'',''budni_test_observations'',count(*)
from mmt_ods.budni_test_observations
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_HLG''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_LCG'',''test_instance'',count(*)
from mmt_ods.test_instance
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_LCG''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_LCG'',''test_instance_engine_info'',count(*)
from mmt_ods.test_instance_engine_info
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_LCG''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_LCG'',''test_instance_tractor_info'',count(*)
from mmt_ods.test_instance_tractor_info
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_LCG''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_LCG'',''budni_centre_of_gravity_location'',count(*)
from mmt_ods.budni_centre_of_gravity_location
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_LCG''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_NMT'',''test_instance'',count(*)
from mmt_ods.test_instance
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_NMT''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_NMT'',''test_instance_engine_info'',count(*)
from mmt_ods.test_instance_engine_info
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_NMT''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_NMT'',''test_instance_tractor_info'',count(*)
from mmt_ods.test_instance_tractor_info
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_NMT''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_NMT'',''budni_noise_atmospheric_conditions'',count(*)
from mmt_ods.budni_noise_atmospheric_conditions
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_NMT''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_NMT'',''budni_noise_measurement_results'',count(*)
from mmt_ods.budni_noise_measurement_results
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_NMT''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_NMT'',''budni_test_observations'',count(*)
from mmt_ods.budni_test_observations
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_NMT''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_PTO'',''test_instance'',count(*)
from mmt_ods.test_instance
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_PTO''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_PTO'',''test_instance_engine_info'',count(*)
from mmt_ods.test_instance_engine_info
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_PTO''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_PTO'',''test_instance_tractor_info'',count(*)
from mmt_ods.test_instance_tractor_info
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_PTO''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_PTO'',''budni_pto_perf_results_summary'',count(*)
from mmt_ods.budni_pto_perf_results_summary
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_PTO''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_PTO'',''budni_pto_perf_results_details'',count(*)
from mmt_ods.budni_pto_perf_results_details
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_PTO''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_PTO'',''budni_test_observations '',count(*)
from mmt_ods.budni_test_observations 
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_PTO''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_VMT'',''test_instance'',count(*)
from mmt_ods.test_instance
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_VMT''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_VMT'',''test_instance_engine_info'',count(*)
from mmt_ods.test_instance_engine_info
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_VMT''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_VMT'',''test_instance_tractor_info'',count(*)
from mmt_ods.test_instance_tractor_info
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_VMT''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_VMT'',''budni_vibration_measurement'',count(*)
from mmt_ods.budni_vibration_measurement
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_VMT''
group by test_file_ref_no
union
select test_file_ref_no,''BUDNI'',''BUDNI_VMT'',''budni_test_observations'',count(*)
from mmt_ods.budni_test_observations
where test_file_ref_no='||p_file_syspk||' 
and test_file_format =''BUDNI'' and test_file_sheet_format =''BUDNI_VMT''
group by test_file_ref_no';
return query 
       
      select  *  from mmt_staging2.test_output_BUDNI;
     
end
$$ LANGUAGE plpgsql;