162 lines
3.2 KiB
PL/PgSQL
162 lines
3.2 KiB
PL/PgSQL
drop function if exists mmt_staging2.fn_BUDNI_LCG_ODS ;
|
|
CREATE OR REPLACE FUNCTION mmt_staging2.fn_BUDNI_LCG_ODS()
|
|
RETURNS void AS $$
|
|
declare __test_instance_id int;
|
|
declare __file_syspk int;
|
|
begin
|
|
|
|
/************************************************************
|
|
Function Name:fn_BUDNI_LCG_ODS
|
|
Function Desc: This function populates data into ODS
|
|
File Format: BUDNI
|
|
Sheet Format: BUDNI_LCG
|
|
Creation Date:
|
|
Updation Date:
|
|
Author: compegence team
|
|
Function Call: select mmt_staging2.fn_BUDNI_LCG_ODS()
|
|
***************************************************************/
|
|
|
|
delete from mmt_ods.test_instance where test_file_sheet_format='BUDNI_LCG';
|
|
|
|
delete from mmt_ods.test_instance_engine_info where test_file_sheet_format='BUDNI_LCG';
|
|
|
|
delete from mmt_ods.test_instance_tractor_info where test_file_sheet_format='BUDNI_LCG';
|
|
|
|
delete from mmt_ods.budni_centre_of_gravity_location where test_file_ref_no =261;
|
|
|
|
|
|
|
|
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,
|
|
wheel_base_mm,
|
|
wheel_track_mm,
|
|
overall_height_mm,
|
|
ballast_condition,
|
|
weight_reactions_front_kg,
|
|
weight_reactions_rear_kg
|
|
)
|
|
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,
|
|
Wheel_Base::int wheel_base_mm,
|
|
wheel_track::int wheel_track_mm,
|
|
overall_height::int,
|
|
Ballast_Condition,
|
|
Tractor_Weight_reactions_Front::int weight_reactions_front_kg ,
|
|
Tractor_Weight_reactions_Rear::int weight_reactions_rear_kg
|
|
from mmt_staging2.BUDNI_LCG_Spec_H1_Block where ods_record =1 ;
|
|
|
|
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
|
|
)
|
|
select
|
|
client_id,
|
|
function_id,
|
|
file_syspk ,
|
|
file_format,
|
|
sheet_mnemonic,
|
|
make,
|
|
model
|
|
from mmt_staging2.BUDNI_LCG_Spec_H1_Block where ods_record =1 ;
|
|
|
|
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
|
|
)
|
|
select
|
|
client_id,
|
|
function_id,
|
|
file_syspk ,
|
|
file_format,
|
|
sheet_mnemonic,
|
|
make,
|
|
model,
|
|
rated_rpm::int
|
|
from mmt_staging2.BUDNI_LCG_Spec_H1_Block where ods_record =1 ;
|
|
|
|
|
|
insert into mmt_ods.budni_centre_of_gravity_location
|
|
(
|
|
client_id,
|
|
function_id,
|
|
test_file_ref_no,
|
|
tractor_make,
|
|
tractor_model,
|
|
condition,
|
|
particulars,
|
|
coordinates
|
|
)
|
|
select
|
|
client_id,
|
|
function_id,
|
|
file_syspk ,
|
|
make,
|
|
model,
|
|
column3,
|
|
column6,
|
|
column8
|
|
from mmt_staging2.budni_lcg_gravity_test_block where ods_record=1;
|
|
|
|
|
|
|
|
select file_syspk into __file_syspk from mmt_staging2.BUDNI_LCG_Spec_H1_block;
|
|
select syspk into __test_instance_id 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
|
|
where test_file_ref_no=__file_syspk;
|
|
|
|
|
|
update mmt_ods.test_instance_tractor_info
|
|
set test_instance_id=__test_instance_id
|
|
where test_file_ref_no=__file_syspk;
|
|
|
|
|
|
update mmt_ods.budni_centre_of_gravity_location
|
|
set test_instance_id=__test_instance_id
|
|
where test_file_ref_no=__file_syspk;
|
|
|
|
|
|
end
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
|
|
|
|
|
|
|