73 lines
2.6 KiB
Plaintext
73 lines
2.6 KiB
Plaintext
drop function if exists mmt_staging2.fn_PTO_MPM_ODS;
|
|
CREATE OR REPLACE FUNCTION mmt_staging2.fn_PTO_MPM_ODS(p_file_syspk int)
|
|
RETURNS text AS $$
|
|
declare __test_instance_id int;
|
|
declare __file_syspk int;
|
|
declare __make text;
|
|
declare __model 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_PTO_MPM_ODS
|
|
Function Desc: This function populates data into ODS blocks
|
|
File Format: PTOBST
|
|
Sheet Format: PTOBST_MPM
|
|
Creation Date: March 21 2021
|
|
Updation Date:
|
|
Author: compegence team
|
|
Function Call: select mmt_staging2.fn_PTO_MPM_ODS(880)
|
|
***************************************************************/
|
|
|
|
select tractor_model into __model from mmt_ods.test_instance_tractor_info where test_file_ref_no =__file_syspk;
|
|
select tractor_make into __make from mmt_ods.test_instance_tractor_info where test_file_ref_no =__file_syspk;
|
|
|
|
insert into mmt_ods.PTO_Multipoint_Mapping
|
|
(
|
|
client_id,function_id,test_file_ref_no,test_file_format,test_file_sheet_format,
|
|
tractor_make ,tractor_model ,
|
|
test_condition,
|
|
engine_speed_rpm,
|
|
engine_load_pct,
|
|
sfc_gm_per_hp_hr,
|
|
fuel_consumption_ltr_per_hr
|
|
)
|
|
|
|
select client_id,function_id,file_syspk,file_format,sheet_mnemonic,make,model,test_condition,
|
|
c2::numeric,c3::numeric,c4::numeric,c5::numeric
|
|
from mmt_staging2.PTOBST_MPM_performance_boost_natuaral_block
|
|
where ods_record=1 ;
|
|
|
|
select file_syspk into __file_syspk from mmt_staging2.PTOBST_RPT_tractor_specs_block;
|
|
select syspk into __test_instance_id from mmt_ods.test_instance where test_file_ref_no =__file_syspk;
|
|
|
|
|
|
update mmt_ods.PTO_Multipoint_Mapping
|
|
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 ,'PTO','PTO_MPM' ,null,'ods', 'fn_PTO_MPM_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 ,'PTO','PTO_MPM' ,null,'ods', 'fn_PTO_MPM_ODS', v_state, v_msg, v_detail, v_hint, v_context,'error');
|
|
return v_context;
|
|
end
|
|
$$ LANGUAGE plpgsql;
|