1473 lines
69 KiB
PL/PgSQL
Executable File
1473 lines
69 KiB
PL/PgSQL
Executable File
drop function if exists staging2.fn_dboecd_test_block;
|
||
CREATE OR REPLACE FUNCTION staging2.fn_dboecd_test_block(p_client_id int,p_function_id int, p_file_mnemonic text,p_file_sheet_mnemonic text, p_file_syspk int)
|
||
RETURNS void
|
||
LANGUAGE plpgsql
|
||
AS $function$
|
||
declare __make text;
|
||
declare __model text;
|
||
declare __client_id int :=p_client_id;
|
||
declare __function_id int :=p_function_id;
|
||
declare __file_mnemonic text :=p_file_mnemonic;
|
||
declare __file_sheet_mnemonic text :=p_file_sheet_mnemonic;
|
||
declare __file_syspk int :=p_file_syspk;
|
||
declare err_state text;
|
||
declare err_msg text;
|
||
declare err_detail text;
|
||
declare err_hint text;
|
||
declare err_context text;
|
||
declare err_query int;
|
||
declare err_block text;
|
||
|
||
begin
|
||
|
||
/*************************************************************
|
||
Function Name:fn_dboecd_test_block
|
||
Function Desc: This function populates data into staging 2 block
|
||
File Format: DBOECD
|
||
Sheet Format: DBOECD_TEST
|
||
Creation Date: March 25 2021
|
||
Updation Date:
|
||
Author: compegence team
|
||
Function Call: select staging2.fn_dboecd_test_block(p_client_id,p_function_id, p_file_mnemonic,
|
||
p_file_sheet_mnemonic, p_file_syspk)
|
||
***************************************************************/
|
||
|
||
SET search_path TO staging2;
|
||
|
||
/* to process multiple files - re-runnability*/
|
||
truncate table staging2.dboecd_test_transmission_wheels_int;
|
||
truncate table staging2.dboecd_test_h1_int;
|
||
truncate table staging2.dboecd_test_engine_rpm_engine_to_pto_block;
|
||
truncate table staging2.dboecd_test_engine_rpm_required_pull_block;
|
||
truncate table staging2.dboecd_test_engine_rpm_weight_block;
|
||
truncate table staging2.dboecd_test_engine_rpm_tyre_details_block;
|
||
truncate table staging2.dboecd_test_engine_RPM_tyre_details_int;
|
||
truncate table staging2.dboecd_test_test_condition_block;
|
||
truncate table staging2.dboecd_test_drawbar_performance_selected_summary_block;
|
||
truncate table staging2.dboecd_test_drawbar_performance_gear_performance_block;
|
||
truncate table staging2.dboecd_test_drawbar_performance_fuel_consumption_block;
|
||
truncate table staging2.dboecd_test_test_equipment_used_block;
|
||
truncate table staging2.dboecd_test_measurement_uncertainty_block;
|
||
truncate table staging2.dboecd_test_tractor_specifications_engine_details_block;
|
||
truncate table staging2.dboecd_test_tractor_specifications_cylinders_block;
|
||
truncate table staging2.dboecd_test_tractor_specifications_fuel_and_injection_block;
|
||
truncate table staging2.dboecd_test_tractor_specifications_cleaner_block;
|
||
truncate table staging2.dboecd_test_transmission_clutch_block;
|
||
truncate table staging2.dboecd_test_transmission_drawbar_block;
|
||
truncate table staging2.dboecd_test_transmission_speed_chart_desc_block;
|
||
truncate table staging2.dboecd_test_transmission_wheels_block;
|
||
truncate table staging2.dboecd_test_transmission_fuel_lubricant_int;
|
||
truncate table staging2.dboecd_test_remarks_block;
|
||
truncate table staging2.dboecd_test_remarks_footer_block;
|
||
truncate table staging2.dboecd_test_h1_block;
|
||
truncate table staging2.dboecd_test_transmission_fuel_lubricant_block;
|
||
truncate table staging2.dboecd_test_engine_rpm_rpm_block;
|
||
truncate table staging2.dboecd_test_transmission_speed_chart_block;
|
||
truncate table staging2.stg_specific_table_dboecd_test;
|
||
truncate table staging2.stg_process_table_dboecd_test;
|
||
execute 'delete from fw_core.fw_jobctl_file_sheet_block_run_schedule where file_syspk='||p_file_syspk||' and file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
|
||
execute 'update transactional.source_config a
|
||
set row_number_start=null,
|
||
row_previous_number=null,
|
||
row_read_end=null,
|
||
run_time=null
|
||
where a.file_mnemonic='''||p_file_mnemonic||''' and a.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
|
||
/*run specific data */
|
||
execute 'insert into staging2.stg_specific_table_dboecd_test
|
||
select * from staging1.staging_generic_table a
|
||
where a.file_mnemonic='''||p_file_mnemonic||''' and a.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''
|
||
and a.file_syspk='||p_file_syspk||'';
|
||
|
||
select count(*) into err_query from staging2.stg_specific_table_dboecd_test;
|
||
|
||
if err_query=0 then
|
||
err_context := 'data not present';
|
||
raise exception using
|
||
message = 'No Data for DBOECD Test',
|
||
detail = 'No data in table stg_specific_table_dboecd_test',
|
||
errcode = '42704',
|
||
hint = 'check sheet mnemonic in generic table, if it is null update it';
|
||
end if;
|
||
|
||
/* standardizing keywords */
|
||
update staging2.stg_specific_table_dboecd_test set column3='Remarks:' where column3 like 'Remarks:%';
|
||
update staging2.stg_specific_table_dboecd_test set column15='Objective' where column15 like 'Objective%';
|
||
|
||
update staging2.stg_specific_table_dboecd_test set column3 = TRIM (TRAILING FROM column3 );
|
||
update staging2.stg_specific_table_dboecd_test set column3 = TRIM (LEADING FROM column3 );
|
||
|
||
/* To process repeated keywords */
|
||
--update transactional.source_config set F1_modified = TRIM (TRAILING FROM F1_modified) ;
|
||
--update transactional.source_config set F1_modified = TRIM (LEADING FROM F1_modified) ;
|
||
update transactional.source_config set F1_source=F1_modified ;
|
||
--update transactional.source_config set F1_source = TRIM (TRAILING FROM F1_source);
|
||
--update transactional.source_config set F1_source = TRIM (LEADING FROM F1_source);
|
||
|
||
/*updating row numbers in source_config table*/
|
||
|
||
execute 'update transactional.source_config a
|
||
set row_number_start=(select min(b.row_number)
|
||
from staging2.stg_specific_table_dboecd_test b
|
||
where trim(upper(F1_modified))=trim(upper(column3))
|
||
and b.is_rownumber_fetched is null)
|
||
where a.row_number_start is null
|
||
and a.file_mnemonic='''||p_file_mnemonic||''' and a.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
|
||
|
||
execute 'update staging2.stg_specific_table_dboecd_test a
|
||
set is_rownumber_fetched=1
|
||
from transactional.source_config b
|
||
where trim(upper(F1_modified))=trim(upper(column3))
|
||
and b.row_number_start=a.row_number
|
||
and is_rownumber_fetched is null
|
||
and a.file_mnemonic='''||p_file_mnemonic||''' and a.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
|
||
execute 'update transactional.source_config a set row_previous_number=row_number_start-1
|
||
where a.file_mnemonic='''||p_file_mnemonic||''' and a.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
|
||
execute 'update transactional.source_config a
|
||
set row_read_end= (select b.row_number_start
|
||
from transactional.source_config b
|
||
where b.syspk=a.syspk+1 and file_mnemonic='''||p_file_mnemonic||''' and file_sheet_mnemonic='''||p_file_sheet_mnemonic||''' )
|
||
where a.file_mnemonic='''||p_file_mnemonic||''' and a.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
|
||
/* update null for the last keyword as read end beacause of overlap of multiple formats*/
|
||
execute 'update transactional.source_config a
|
||
set row_read_end = null ,run_time=current_timestamp
|
||
where f1_modified =''Remarks:''
|
||
and a.file_mnemonic='''||p_file_mnemonic||''' and a.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
|
||
/* tagging block_row_numbers for each block */
|
||
|
||
execute 'insert into staging2.stg_process_table_dboecd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
|
||
from staging2.stg_specific_table_dboecd_test a
|
||
join transactional.source_config b
|
||
on a.row_number >=row_number_start
|
||
and (a.row_number< row_read_end or row_read_end is null)
|
||
and f1_modified=''Test Request no.''
|
||
and b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
|
||
execute 'insert into staging2.stg_process_table_dboecd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag from staging2.stg_specific_table_dboecd_test a
|
||
join transactional.source_config b
|
||
on a.row_number >=row_number_start
|
||
and (a.row_number< row_read_end or row_read_end is null)
|
||
and f1_modified=''Engine (RPM)''
|
||
and b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
|
||
execute 'insert into staging2.stg_process_table_dboecd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag from staging2.stg_specific_table_dboecd_test a
|
||
join transactional.source_config b
|
||
on a.row_number >=row_number_start
|
||
and (a.row_number< row_read_end or row_read_end is null)
|
||
and f1_modified=''Test condition''
|
||
and b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
|
||
execute 'insert into staging2.stg_process_table_dboecd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag from staging2.stg_specific_table_dboecd_test a
|
||
join transactional.source_config b
|
||
on a.row_number >=row_number_start
|
||
and (a.row_number< row_read_end or row_read_end is null)
|
||
and f1_modified=''Drawbar Performance Test Results (Selected Summary)''
|
||
and b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
|
||
execute 'insert into staging2.stg_process_table_dboecd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
|
||
from staging2.stg_specific_table_dboecd_test a
|
||
join transactional.source_config b
|
||
on a.row_number >=row_number_start
|
||
and (a.row_number< row_read_end or row_read_end is null)
|
||
and f1_modified=''Drawbar Performance Test Results (Gear Performance Test)''
|
||
and b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
|
||
execute 'insert into staging2.stg_process_table_dboecd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
|
||
from staging2.stg_specific_table_dboecd_test a
|
||
join transactional.source_config b
|
||
on a.row_number >=row_number_start
|
||
and (a.row_number< row_read_end or row_read_end is null)
|
||
and f1_modified=''3.3.2''
|
||
and b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
|
||
execute 'insert into staging2.stg_process_table_dboecd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
|
||
from staging2.stg_specific_table_dboecd_test a
|
||
join transactional.source_config b
|
||
on a.row_number >=row_number_start
|
||
and (a.row_number< row_read_end or row_read_end is null)
|
||
and f1_modified=''Test equipment’s used''
|
||
and b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
|
||
|
||
execute 'insert into staging2.stg_process_table_dboecd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
|
||
from staging2.stg_specific_table_dboecd_test a
|
||
join transactional.source_config b
|
||
on a.row_number >=row_number_start
|
||
and (a.row_number< row_read_end or row_read_end is null)
|
||
and f1_modified=''Tractor specification''
|
||
and b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
|
||
execute 'insert into staging2.stg_process_table_dboecd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
|
||
from staging2.stg_specific_table_dboecd_test a
|
||
join transactional.source_config b
|
||
on a.row_number >=row_number_start
|
||
and (a.row_number< row_read_end or row_read_end is null)
|
||
and f1_modified=''Transmission''
|
||
and b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
|
||
|
||
execute 'insert into staging2.stg_process_table_dboecd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
|
||
from staging2.stg_specific_table_dboecd_test a
|
||
join transactional.source_config b
|
||
on a.row_number >=row_number_start
|
||
and (a.row_number< row_read_end or row_read_end is null)
|
||
and f1_modified=''Remarks:''
|
||
and b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
|
||
/*selecting tactor make*/
|
||
select column6 into __make from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications' and trim(' :' from column4)='Make';
|
||
|
||
/*selecting tractor model*/
|
||
select column6 into __model from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_Test_H1' and trim(column3)='Tractor Model';
|
||
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_TEST_H1_BLOCK',__file_mnemonic,__file_sheet_mnemonic,1);
|
||
err_block:='DBOECD_TEST_H1_BLOCK';
|
||
|
||
/*selecting first block column and their data*/
|
||
insert into staging2.DBOECD_TEST_H1_INT (c1,c2,block_row_number)
|
||
select a.column3,column6,block_row_number from staging2.stg_process_table_dboecd_test a where block_tag='DBOECD_Test_H1'
|
||
union
|
||
select a.column9,column12,block_row_number from staging2.stg_process_table_dboecd_test a where block_tag='DBOECD_Test_H1'
|
||
order by column3,"block_row_number";
|
||
|
||
/*execute if using old format*/
|
||
update staging2.stg_process_table_dboecd_test set column11 = null
|
||
where column11 ='-';
|
||
update staging2.stg_process_table_dboecd_test set column12 = null
|
||
where column12 = '-';
|
||
update staging2.stg_process_table_dboecd_test set column9 = null
|
||
where column9 ='-';
|
||
update staging2.stg_process_table_dboecd_test set column21 = null
|
||
where column21 = '-';
|
||
update staging2.stg_process_table_dboecd_test set column12 = null
|
||
where column12 = '..';
|
||
update staging2.stg_process_table_dboecd_test set column8 = null
|
||
where column8 = '.';
|
||
update staging2.stg_process_table_dboecd_test set column20 = trim(' Kg' from column20)
|
||
where block_row_number =7 and block_tag = 'DBOECD_test_transmission';
|
||
update staging2.stg_process_table_dboecd_test set column23 = trim(' Kg' from column23)
|
||
where block_row_number =7 and block_tag = 'DBOECD_test_transmission';
|
||
|
||
/*selecting objective column and its data*/
|
||
insert into staging2.DBOECD_TEST_H1_INT (c1,c2)
|
||
select 'Objective',column15
|
||
from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_Test_H1'
|
||
and row_number=
|
||
(
|
||
select row_number+1 from
|
||
staging2.stg_process_table_dboecd_test a
|
||
where column15='Objective'
|
||
and block_tag='DBOECD_Test_H1'
|
||
);
|
||
|
||
/*selecting acceptance criteria column and its data*/
|
||
insert into staging2.dboecd_TEST_H1_INT (c1,c2)
|
||
select 'Acceptance criteria',column15
|
||
from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_Test_H1'
|
||
and row_number=
|
||
(
|
||
select row_number+1 from
|
||
staging2.stg_process_table_dboecd_test a
|
||
where trim(':' from column15)='Acceptance criteria'
|
||
and block_tag='DBOECD_Test_H1'
|
||
);
|
||
|
||
/*filling h1 block by transposing h1 int block*/
|
||
insert into staging2.DBOECD_TEST_H1_Block
|
||
(
|
||
dummy_f,
|
||
Customer_Name,
|
||
Generation,
|
||
No_of_Sample,
|
||
Operator_Name,
|
||
Project_Group,
|
||
Sample_Receipt_Date,
|
||
Test_End_Date,
|
||
Test_Engineer,
|
||
Test_Location,
|
||
Test_Report_Date,
|
||
Test_Request_no,
|
||
Test_Standard_Refer,
|
||
Test_Start_Date,
|
||
Test_report_No,
|
||
Tractor_Model,
|
||
Tractor_Sr_No,
|
||
Objective,
|
||
Acceptance_criteria
|
||
)
|
||
SELECT *
|
||
FROM crosstab(
|
||
'SELECT unnest(''{c2}''::text[]) AS col
|
||
, row_number() OVER ()
|
||
, unnest(ARRAY[c2::text]) AS val
|
||
FROM staging2.DBOECD_TEST_H1_INT
|
||
ORDER BY generate_series(1,15),2'
|
||
) t (col text,a_1 text,a_2 text,a_3 text,a_4 text,a_5 text,a_6 text,a_7 text,a_8 text,
|
||
a_9 text,a_10 text,a_11 text,a_12 text,a_13 text,a_14 text,a_15 text,a_16 text,a_17 text,a_18 text);
|
||
|
||
|
||
/*deleting dummy null record and filling make, model and other id's*/
|
||
delete from staging2.DBOECD_TEST_H1_Block where dummy_f is null ;
|
||
execute 'update staging2.DBOECD_TEST_H1_Block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_TEST_H1_Block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_TEST_H1_Block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_TEST_H1_Block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_TEST_H1_Block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_TEST_H1_Block set make=__make;
|
||
update staging2.DBOECD_TEST_H1_Block set model=__model;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_TEST_H1_BLOCK');
|
||
|
||
/* engine_RPM_RPM_block*/
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_engine_RPM_RPM_block',__file_mnemonic,__file_sheet_mnemonic,2);
|
||
err_block:='DBOECD_test_engine_RPM_RPM_block';
|
||
insert into staging2.DBOECD_test_engine_RPM_RPM_block(dummy_f) values ('dummy');
|
||
|
||
update staging2.DBOECD_test_engine_RPM_RPM_block
|
||
set low_idle_declared=(select column6 from
|
||
staging2.stg_process_table_dboecd_test where block_row_number=2 and column3='Low Idle RPM' and block_tag='DBOECD_test_engine_RPM')
|
||
where dummy_f='dummy';
|
||
|
||
update staging2.DBOECD_test_engine_RPM_RPM_block
|
||
set low_idle_observed=(select column8 from
|
||
staging2.stg_process_table_dboecd_test where block_row_number=2 and column3='Low Idle RPM' and block_tag='DBOECD_test_engine_RPM')
|
||
where dummy_f='dummy';
|
||
|
||
update staging2.DBOECD_test_engine_RPM_RPM_block
|
||
set high_idle_declared=(select column6 from
|
||
staging2.stg_process_table_dboecd_test where block_row_number=3 and column3='High Idle RPM' and block_tag='DBOECD_test_engine_RPM')
|
||
where dummy_f='dummy';
|
||
|
||
update staging2.DBOECD_test_engine_RPM_RPM_block
|
||
set high_idle_observed=(select column8 from
|
||
staging2.stg_process_table_dboecd_test where block_row_number=3 and column3='High Idle RPM' and block_tag='DBOECD_test_engine_RPM')
|
||
where dummy_f='dummy';
|
||
|
||
execute 'update staging2.DBOECD_test_engine_RPM_RPM_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_RPM_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_RPM_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_RPM_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_RPM_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_engine_RPM_RPM_block set make=__make;
|
||
update staging2.DBOECD_test_engine_RPM_RPM_block set model=__model;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_engine_RPM_RPM_block');
|
||
|
||
/*engine_rpm_engine_to_pto_block*/
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'dboecd_test_engine_rpm_engine_to_pto_block',__file_mnemonic,__file_sheet_mnemonic,3);
|
||
err_block:='dboecd_test_engine_rpm_engine_to_pto_block';
|
||
|
||
insert into staging2.dboecd_test_engine_rpm_engine_to_pto_block(dummy_f) values ('dummy');
|
||
|
||
update staging2.DBOECD_test_engine_RPM_engine_to_PTO_block set rated_speed=
|
||
(select column6 from staging2.stg_process_table_dboecd_test a
|
||
where a.block_tag='DBOECD_test_engine_RPM'
|
||
and block_row_number=4 )
|
||
where dummy_f='dummy';
|
||
|
||
update staging2.DBOECD_test_engine_RPM_engine_to_PTO_block set engine_to_PTO_ratio=
|
||
(select column6 from staging2.stg_process_table_dboecd_test a
|
||
where a.block_tag='DBOECD_test_engine_RPM'
|
||
and block_row_number=5 )
|
||
where dummy_f='dummy';
|
||
|
||
update staging2.DBOECD_test_engine_RPM_engine_to_PTO_block set wheel_base_mm=
|
||
(select column6 from staging2.stg_process_table_dboecd_test a
|
||
where a.block_tag='DBOECD_test_engine_RPM'
|
||
and block_row_number=6 )
|
||
where dummy_f='dummy';
|
||
|
||
update staging2.DBOECD_test_engine_RPM_engine_to_PTO_block set engine_power_hp=
|
||
(select column6 from staging2.stg_process_table_dboecd_test a
|
||
where a.file_mnemonic='DBOECD' and a.file_sheet_mnemonic='DBOECD_TEST' and a.block_tag='DBOECD_test_engine_RPM'
|
||
and block_row_number=7 )
|
||
where dummy_f='dummy';
|
||
|
||
update staging2.DBOECD_test_engine_RPM_engine_to_PTO_block set PTO_Power_hp=
|
||
(select column6 from staging2.stg_process_table_dboecd_test a
|
||
where a.file_mnemonic='DBOECD' and a.file_sheet_mnemonic='DBOECD_TEST' and a.block_tag='DBOECD_test_engine_RPM'
|
||
and block_row_number=8 )
|
||
where dummy_f='dummy';
|
||
|
||
execute 'update staging2.DBOECD_test_engine_RPM_engine_to_PTO_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_engine_to_PTO_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_engine_to_PTO_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_engine_to_PTO_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_engine_to_PTO_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_engine_RPM_engine_to_PTO_block set make=__make;
|
||
update staging2.DBOECD_test_engine_RPM_engine_to_PTO_block set model=__model;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'dboecd_test_engine_rpm_engine_to_pto_block');
|
||
|
||
/*engine_RPM_required_pull_block */
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_engine_RPM_required_pull_block',__file_mnemonic,__file_sheet_mnemonic,4);
|
||
err_block:='DBOECD_test_engine_RPM_required_pull_block';
|
||
|
||
|
||
insert into staging2.DBOECD_test_engine_RPM_required_pull_block ( pull_type) values ('UB'), ('B');
|
||
|
||
update staging2.DBOECD_test_engine_RPM_required_pull_block
|
||
set required_pull_in_kg=(select column7 from staging2.stg_process_table_dboecd_test a
|
||
where a.block_tag='DBOECD_test_engine_RPM'
|
||
and column3='Required pull in (kg)')
|
||
where pull_type='UB';
|
||
|
||
update staging2.DBOECD_test_engine_RPM_required_pull_block
|
||
set required_pull_in_kg=(select column9 from staging2.stg_process_table_dboecd_test a
|
||
where a.block_tag='DBOECD_test_engine_RPM'
|
||
and column3='Required pull in (kg)')
|
||
where pull_type='B';
|
||
|
||
update staging2.DBOECD_test_engine_RPM_required_pull_block
|
||
set required_power_in_hp=(select column7 from staging2.stg_process_table_dboecd_test a
|
||
where a.block_tag='DBOECD_test_engine_RPM'
|
||
and column3='Required Power in hp')
|
||
where pull_type='UB';
|
||
|
||
update staging2.DBOECD_test_engine_RPM_required_pull_block
|
||
set required_power_in_hp=(select column9 from staging2.stg_process_table_dboecd_test a
|
||
where a.block_tag='DBOECD_test_engine_RPM'
|
||
and column3='Required Power in hp')
|
||
where pull_type='B';
|
||
|
||
update staging2.DBOECD_test_engine_RPM_required_pull_block
|
||
set actual_pull_in_kg=(select column7 from staging2.stg_process_table_dboecd_test a
|
||
where a.block_tag='DBOECD_test_engine_RPM'
|
||
and column3='Actual pull in (kg)')
|
||
where pull_type='UB';
|
||
|
||
update staging2.DBOECD_test_engine_RPM_required_pull_block
|
||
set actual_pull_in_kg=(select column9 from staging2.stg_process_table_dboecd_test a
|
||
where a.block_tag='DBOECD_test_engine_RPM'
|
||
and column3='Actual pull in (kg)')
|
||
where pull_type='B';
|
||
|
||
update staging2.DBOECD_test_engine_RPM_required_pull_block
|
||
set actual_power_in_hp=(select column7 from staging2.stg_process_table_dboecd_test a
|
||
where a.block_tag='DBOECD_test_engine_RPM'
|
||
and column3='actual power in hp')
|
||
where pull_type='UB';
|
||
|
||
update staging2.DBOECD_test_engine_RPM_required_pull_block
|
||
set actual_power_in_hp=(select column9 from staging2.stg_process_table_dboecd_test a
|
||
where a.block_tag='DBOECD_test_engine_RPM'
|
||
and column3='actual power in hp')
|
||
where pull_type='B';
|
||
|
||
update staging2.DBOECD_test_engine_RPM_required_pull_block
|
||
set calculated_hitch_height_mm=(select column7 from staging2.stg_process_table_dboecd_test a
|
||
where a.block_tag='DBOECD_test_engine_RPM'
|
||
and column3='Calculated hitch height (mm)')
|
||
where pull_type='UB';
|
||
|
||
update staging2.DBOECD_test_engine_RPM_required_pull_block
|
||
set calculated_hitch_height_mm=(select column9 from staging2.stg_process_table_dboecd_test a
|
||
where a.block_tag='DBOECD_test_engine_RPM'
|
||
and column3='Calculated hitch height (mm)')
|
||
where pull_type='B';
|
||
|
||
update staging2.DBOECD_test_engine_RPM_required_pull_block
|
||
set actual_hitch_height_mm=(select column7 from staging2.stg_process_table_dboecd_test a
|
||
where a.block_tag='DBOECD_test_engine_RPM'
|
||
and column3='Actual hitch height (mm)')
|
||
where pull_type='UB';
|
||
|
||
update staging2.DBOECD_test_engine_RPM_required_pull_block
|
||
set actual_hitch_height_mm=(select column9 from staging2.stg_process_table_dboecd_test a
|
||
where a.block_tag='DBOECD_test_engine_RPM'
|
||
and column3='Actual hitch height (mm)')
|
||
where pull_type='B';
|
||
|
||
execute 'update staging2.DBOECD_test_engine_RPM_required_pull_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_required_pull_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_required_pull_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_required_pull_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_required_pull_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_engine_RPM_required_pull_block set make=__make;
|
||
update staging2.DBOECD_test_engine_RPM_required_pull_block set model=__model;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_engine_RPM_required_pull_block');
|
||
|
||
/* RPM_weight_block*/
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_engine_RPM_weight_block',__file_mnemonic,__file_sheet_mnemonic,5);
|
||
err_block:='DBOECD_test_engine_RPM_weight_block';
|
||
|
||
insert into staging2.DBOECD_test_engine_RPM_weight_block(
|
||
weight_kg ,
|
||
Front ,
|
||
rear ,
|
||
Total
|
||
)
|
||
select column10,column13,column17,column22 from staging2.stg_process_table_dboecd_test a
|
||
where a.block_tag='DBOECD_test_engine_RPM'
|
||
and block_row_number in(2,3);
|
||
|
||
execute 'update staging2.DBOECD_test_engine_RPM_weight_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_weight_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_weight_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_weight_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_weight_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_engine_RPM_weight_block set make=__make;
|
||
update staging2.DBOECD_test_engine_RPM_weight_block set model=__model;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_engine_RPM_weight_block');
|
||
|
||
/*rpm tyre details block*/
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_engine_RPM_tyre_details_block',__file_mnemonic,__file_sheet_mnemonic,6);
|
||
err_block:='DBOECD_test_engine_RPM_tyre_details_block';
|
||
|
||
insert into staging2.DBOECD_test_engine_RPM_tyre_details_int
|
||
( s_no,tyre_details,front,rear)
|
||
|
||
select column10,column11,column15,column20
|
||
from staging2.stg_process_table_dboecd_test a
|
||
where a.block_tag='DBOECD_test_engine_RPM'
|
||
and block_row_number >=5 and block_row_number<=14
|
||
order by block_row_number;
|
||
|
||
|
||
execute 'update staging2.DBOECD_test_engine_RPM_tyre_details_int set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_tyre_details_int set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_tyre_details_int set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_tyre_details_int set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_tyre_details_int set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_engine_RPM_tyre_details_int set make=__make;
|
||
update staging2.DBOECD_test_engine_RPM_tyre_details_int set model=__model;
|
||
|
||
/*rpm tyre details block*/
|
||
|
||
insert into staging2.DBOECD_test_engine_RPM_tyre_details_block
|
||
(
|
||
dummy_f,
|
||
tyre_Make,
|
||
size,
|
||
Ply_Rating,
|
||
Load_Carrying_Capacity_in_Kg,
|
||
Pressure_kg_cm2,
|
||
Number_of_lug,
|
||
Number_of_no_load_lug_30m,
|
||
Lug_Height,
|
||
Dynamic_rolling_radius_mm,
|
||
Wheel_Rim_Make_Size
|
||
)
|
||
SELECT *
|
||
FROM crosstab(
|
||
'SELECT unnest(''{front,rear}''::text[]) AS col
|
||
, row_number() OVER ()
|
||
, unnest(ARRAY[front::text,rear::text]) AS val
|
||
FROM staging2.DBOECD_test_engine_RPM_tyre_details_int
|
||
ORDER BY generate_series(1,15),2'
|
||
) t ( col text,a_1 text,a_2 text,a_3 text,a_4 text,a_5 text,a_6 text,a_7 text,a_8 text,a_9 text
|
||
,a_10 text);
|
||
|
||
delete from staging2.DBOECD_test_engine_RPM_tyre_details_block where dummy_f is null;
|
||
|
||
execute 'update staging2.DBOECD_test_engine_RPM_tyre_details_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_tyre_details_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_tyre_details_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_tyre_details_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_engine_RPM_tyre_details_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_engine_RPM_tyre_details_block set make=__make;
|
||
update staging2.DBOECD_test_engine_RPM_tyre_details_block set model=__model;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_engine_RPM_tyre_details_block');
|
||
|
||
/* test condition block */
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_test_condition_block',__file_mnemonic,__file_sheet_mnemonic,7);
|
||
err_block:='DBOECD_test_test_condition_block';
|
||
|
||
|
||
insert into staging2.DBOECD_test_test_condition_block(c1,c2,c3)
|
||
select column3 c1,column10 c2,column15 c3
|
||
from staging2.stg_process_table_dboecd_test a
|
||
where a.block_tag='DBOECD_test_test_condition';
|
||
|
||
|
||
execute 'update staging2.DBOECD_test_test_condition_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_test_condition_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_test_condition_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_test_condition_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_test_condition_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_test_condition_block set make=__make;
|
||
update staging2.DBOECD_test_test_condition_block set model=__model;
|
||
|
||
update staging2.dboecd_test_test_condition_block
|
||
set c3='B'
|
||
where c2='Ballasted';
|
||
|
||
|
||
update staging2.dboecd_test_test_condition_block
|
||
set c3='UB'
|
||
where c2='Un-ballasted';
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_test_condition_block');
|
||
|
||
/* performance_selected_summary_block */
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_drawbar_performance_selected_summary_block',__file_mnemonic,__file_sheet_mnemonic,8);
|
||
|
||
err_block:='DBOECD_test_drawbar_performance_selected_summary_block';
|
||
|
||
insert into staging2.DBOECD_test_drawbar_performance_selected_summary_block(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25)
|
||
select column3 c1,
|
||
column4 c2,
|
||
column5 c3,
|
||
column6 c4,
|
||
column7 c5,
|
||
column8 c6,
|
||
column9 c7,
|
||
column10 c8,
|
||
column11 c9,
|
||
column12 c10,
|
||
column13 c11,
|
||
column14 c12,
|
||
column15 c13,
|
||
column16 c14,
|
||
column17 c15,
|
||
column18 c16,
|
||
column19 c17,
|
||
column20 c18,
|
||
column21 c19,
|
||
column22 c20,
|
||
column23 c21,
|
||
column24 c22,
|
||
column25 c23,
|
||
column26 c24,
|
||
column27 c25
|
||
from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_drawbar_performance_selected_summary'
|
||
and block_row_number>3;
|
||
|
||
|
||
|
||
execute 'update staging2.DBOECD_test_drawbar_performance_selected_summary_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_drawbar_performance_selected_summary_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_drawbar_performance_selected_summary_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_drawbar_performance_selected_summary_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_drawbar_performance_selected_summary_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_drawbar_performance_selected_summary_block set make=__make;
|
||
update staging2.DBOECD_test_drawbar_performance_selected_summary_block set model=__model;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_drawbar_performance_selected_summary_block');
|
||
|
||
/* Fuel_consumption_block*/
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'dboecd_test_drawbar_performance_fuel_consumption_block',__file_mnemonic,__file_sheet_mnemonic,9);
|
||
err_block:='dboecd_test_drawbar_performance_fuel_consumption_block';
|
||
|
||
|
||
insert into staging2.dboecd_test_drawbar_performance_fuel_consumption_block(c1_1,c2_1,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25)
|
||
with fuel_consumtion_data as(
|
||
select column3 c1,
|
||
column4 c2,
|
||
column5 c3,
|
||
column6 c4,
|
||
column7 c5,
|
||
column8 c6,
|
||
column9 c7,
|
||
column10 c8,
|
||
column11 c9,
|
||
column12 c10,
|
||
column13 c11,
|
||
column14 c12,
|
||
column15 c13,
|
||
column16 c14,
|
||
column17 c15,
|
||
column18 c16,
|
||
column19 c17,
|
||
column20 c18,
|
||
column21 c19,
|
||
column22 c20,
|
||
column23 c21,
|
||
column24 c22,
|
||
column25 c23,
|
||
column26 c24,
|
||
column27 c25,
|
||
block_row_number
|
||
from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_fuel_consumption' and column5 is not null
|
||
and block_row_number>3),
|
||
data_required as
|
||
(
|
||
select column3 as c1_1,column4 as c2_1 from staging2.stg_process_table_dboecd_test a where block_tag='DBOECD_test_fuel_consumption'
|
||
and block_row_number>3 and column5 is null
|
||
),
|
||
stag1_info_table as
|
||
(select c1_1,c2_1,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25
|
||
from (select *,row_number() over () as rn from data_required) t1,
|
||
(select *,row_number() over () as rn from fuel_consumtion_data)t2
|
||
where t1.rn = t2.rn)
|
||
select * from stag1_info_table;
|
||
|
||
update staging2.dboecd_test_drawbar_performance_fuel_consumption_block set Remark = (select replace(column3,'Remark: ','') from staging2.stg_process_table_dboecd_test where column3 like 'Remark%' and block_tag='DBOECD_test_fuel_consumption');
|
||
|
||
|
||
execute 'update staging2.dboecd_test_drawbar_performance_fuel_consumption_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.dboecd_test_drawbar_performance_fuel_consumption_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.dboecd_test_drawbar_performance_fuel_consumption_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.dboecd_test_drawbar_performance_fuel_consumption_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.dboecd_test_drawbar_performance_fuel_consumption_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.dboecd_test_drawbar_performance_fuel_consumption_block set make=__make;
|
||
update staging2.dboecd_test_drawbar_performance_fuel_consumption_block set model=__model;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'dboecd_test_drawbar_performance_fuel_consumption_block');
|
||
|
||
/* performance_gear_summary_block */
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_drawbar_performance_gear_performance_block',__file_mnemonic,__file_sheet_mnemonic,10);
|
||
err_block:='DBOECD_test_drawbar_performance_gear_performance_block';
|
||
|
||
|
||
insert into staging2.DBOECD_test_drawbar_performance_gear_performance_block (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25)
|
||
select column3 c1,
|
||
column4 c2,
|
||
column5 c3,
|
||
column6 c4,
|
||
column7 c5,
|
||
column8 c6,
|
||
column9 c7,
|
||
column10 c8,
|
||
column11 c9,
|
||
column12 c10,
|
||
column13 c11,
|
||
column14 c12,
|
||
column15 c13,
|
||
column16 c14,
|
||
column17 c15,
|
||
column18 c16,
|
||
column19 c17,
|
||
column20 c18,
|
||
column21 c19,
|
||
column22 c20,
|
||
column23 c21,
|
||
column24 c22,
|
||
column25 c23,
|
||
column26 c24,
|
||
column27 c25
|
||
from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_drawbar_performance_gear_performance'
|
||
and block_row_number>3;
|
||
|
||
|
||
|
||
execute 'update staging2.DBOECD_test_drawbar_performance_gear_performance_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_drawbar_performance_gear_performance_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_drawbar_performance_gear_performance_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_drawbar_performance_gear_performance_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_drawbar_performance_gear_performance_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_drawbar_performance_gear_performance_block set make=__make;
|
||
update staging2.DBOECD_test_drawbar_performance_gear_performance_block set model=__model;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_drawbar_performance_gear_performance_block');
|
||
|
||
/*test_equipment_used_block */
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_test_equipment_used_block',__file_mnemonic,__file_sheet_mnemonic,11);
|
||
err_block:='DBOECD_test_test_equipment_used_block';
|
||
|
||
|
||
insert into staging2.DBOECD_test_test_equipment_used_block( s_no,instruments,instruments_no,cali_due_date)
|
||
select column3,column4,column8,column12
|
||
from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_test_equipment'
|
||
and block_row_number>2;
|
||
|
||
execute 'update staging2.DBOECD_test_test_equipment_used_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_test_equipment_used_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_test_equipment_used_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_test_equipment_used_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_test_equipment_used_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_test_equipment_used_block set make=__make;
|
||
update staging2.DBOECD_test_test_equipment_used_block set model=__model;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_test_equipment_used_block');
|
||
|
||
/*measurement_uncertainty_block*/
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_measurement_uncertainty_block',__file_mnemonic,__file_sheet_mnemonic,12);
|
||
err_block:='DBOECD_test_measurement_uncertainty_block';
|
||
|
||
|
||
insert into staging2.DBOECD_test_measurement_uncertainty_block(dummy_f) values ('dummy');
|
||
|
||
update staging2.DBOECD_test_measurement_uncertainty_block set load_cell=
|
||
(select column20 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_test_equipment' and trim(' :' from column15)='Load cell');
|
||
|
||
update staging2.DBOECD_test_measurement_uncertainty_block set rpm_meter=
|
||
(select column20 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_test_equipment' and trim(' :' from column15)='RPM Meter');
|
||
|
||
update staging2.DBOECD_test_measurement_uncertainty_block set speed=
|
||
(select column20 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_test_equipment' and trim(' :' from column15)='Speed');
|
||
|
||
|
||
update staging2.DBOECD_test_measurement_uncertainty_block set fuel_flow_meter=
|
||
(select column20 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_test_equipment' and trim(' :' from column15)='Fuel Flow meter');
|
||
|
||
|
||
|
||
execute 'update staging2.DBOECD_test_measurement_uncertainty_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_measurement_uncertainty_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_measurement_uncertainty_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_measurement_uncertainty_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_measurement_uncertainty_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_measurement_uncertainty_block set make=__make;
|
||
update staging2.DBOECD_test_measurement_uncertainty_block set model=__model;
|
||
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_measurement_uncertainty_block');
|
||
|
||
/*tractor_specifications_engine_details_block*/
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_tractor_specifications_engine_details_block',__file_mnemonic,__file_sheet_mnemonic,13);
|
||
err_block:='DBOECD_test_tractor_specifications_engine_details_block';
|
||
|
||
|
||
insert into staging2.DBOECD_test_tractor_specifications_engine_details_block(dummy_f)
|
||
values ('dummy');
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_engine_details_block
|
||
set make_in_block=(select column6 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(' :' from column4)='Make'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_engine_details_block
|
||
set type=(select column6 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(' :' from column4)='Type'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_engine_details_block
|
||
set model_in_block=(select column6 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(' :' from column4)='Model'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_engine_details_block
|
||
set serial_no=(select column6 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(' :' from column4)='Serial No'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_engine_details_block
|
||
set E_P_ratio=(select column6 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(' :' from column4)='E/P Ratio'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_engine_details_block
|
||
set rated_speed=(select column6 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(' :' from column4)='Rated Speed'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_engine_details_block
|
||
set high_idle=(select column6 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(' :' from column4)='High Idle'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_engine_details_block
|
||
set high_idle=(select column6 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(' :' from column4)='High Idle'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_engine_details_block
|
||
set low_idle=(select replace(column8,'Low Idle:- ','') from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(column4)='High Idle'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_engine_details_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_engine_details_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_engine_details_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_engine_details_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_engine_details_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_tractor_specifications_engine_details_block');
|
||
|
||
/*tractor_specifications_cylinders_block*/
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_tractor_specifications_cylinders_block',__file_mnemonic,__file_sheet_mnemonic,14);
|
||
err_block:='DBOECD_test_tractor_specifications_cylinders_block';
|
||
|
||
|
||
insert into staging2.DBOECD_test_tractor_specifications_cylinders_block(dummy_f)
|
||
values ('dummy');
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_cylinders_block
|
||
set number=(select column11 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(' :' from column9)='Number'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_cylinders_block
|
||
set stroke=(select column11 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(' :' from column9)='Stroke'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_cylinders_block
|
||
set bore=(select column11 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(' :' from column9)='Bore'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_cylinders_block
|
||
set capacity=(select column11 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(' :' from column9)='Capacity'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_cylinders_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_cylinders_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_cylinders_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_cylinders_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_cylinders_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_tractor_specifications_cylinders_block set make=__make;
|
||
update staging2.DBOECD_test_tractor_specifications_cylinders_block set model=__model;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_tractor_specifications_cylinders_block');
|
||
|
||
/*fuel_and_injection_block*/
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_tractor_specifications_fuel_and_injection_block',__file_mnemonic,__file_sheet_mnemonic,15);
|
||
err_block:='DBOECD_test_tractor_specifications_fuel_and_injection_block';
|
||
|
||
|
||
insert into staging2.DBOECD_test_tractor_specifications_fuel_and_injection_block(dummy_f)
|
||
values ('dummy');
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_fuel_and_injection_block
|
||
set Make_type_and_model_of_injection_pump=(select column17 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(column12)='Make, type and model of injection pump'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_fuel_and_injection_block
|
||
set capacity_of_fuel_tank_lit=(select column17 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim('. :-' from column12)='Capacity of fuel tank, lit'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_fuel_and_injection_block
|
||
set Make_type_and_model_of_injection_pump=(select column17 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(' :-' from column12)='Make, type and model of injection pump'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_fuel_and_injection_block
|
||
set Manufacturer_production_setting=(select column17 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(' :-' from column12)='Manufacturers production setting'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_fuel_and_injection_block
|
||
set Make_type_and_model_of_injectors=(select column17 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(' :-' from column12)='Make, type and model of injectors'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_fuel_and_injection_block
|
||
set Make_type_and_model_of_magneto_coil_and_distributor=(select column17 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(' :-' from column12)='Make, type and model of magneto, coil and distributor'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_fuel_and_injection_block
|
||
set Make_type_and_model_of_carburetor=(select column17 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(' :-' from column12)='Make, type and model of carburetor'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_fuel_and_injection_block
|
||
set Ignition_or_injection_timing_Manual_or_automatic=(select column17 from staging2.stg_process_table_dboecd_test a
|
||
where block_tag='DBOECD_test_tractor_specifications'
|
||
and trim(' :-' from column12)='Ignition or injection timing (Manual or automatic)'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_fuel_and_injection_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_fuel_and_injection_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_fuel_and_injection_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_fuel_and_injection_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_fuel_and_injection_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_tractor_specifications_fuel_and_injection_block set make=__make;
|
||
update staging2.DBOECD_test_tractor_specifications_fuel_and_injection_block set model=__model;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_tractor_specifications_fuel_and_injection_block');
|
||
|
||
/*tractor_specifications_cleaner_block*/
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_tractor_specifications_cleaner_block',__file_mnemonic,__file_sheet_mnemonic,16);
|
||
err_block:='DBOECD_test_tractor_specifications_cleaner_block';
|
||
|
||
|
||
insert into staging2.DBOECD_test_tractor_specifications_cleaner_block(cleaner_type)
|
||
values ('Air cleaner'),('Precleaner (if fitted)');
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_cleaner_block
|
||
set make_and_model =
|
||
(select column23 from staging2.stg_process_table_dboecd_test a where
|
||
block_tag='DBOECD_test_tractor_specifications'
|
||
and column21='Make and model' and block_row_number=3)
|
||
where cleaner_type='Air cleaner';
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_cleaner_block
|
||
set type=
|
||
(select column23 from staging2.stg_process_table_dboecd_test a where
|
||
block_tag='DBOECD_test_tractor_specifications'
|
||
and column21='Type' and block_row_number=5)
|
||
where cleaner_type='Air cleaner';
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_cleaner_block
|
||
set make_and_model =
|
||
(select column23 from staging2.stg_process_table_dboecd_test a where
|
||
block_tag='DBOECD_test_tractor_specifications'
|
||
and column21='Make and model' and block_row_number=7)
|
||
where cleaner_type='Precleaner (if fitted)';
|
||
|
||
update staging2.DBOECD_test_tractor_specifications_cleaner_block
|
||
set type=
|
||
(select column23 from staging2.stg_process_table_dboecd_test a where
|
||
block_tag='DBOECD_test_tractor_specifications'
|
||
and column21='Type' and block_row_number=9)
|
||
where cleaner_type='Precleaner (if fitted)';
|
||
|
||
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_cleaner_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_cleaner_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_cleaner_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_cleaner_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_tractor_specifications_cleaner_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_tractor_specifications_cleaner_block set make=__make;
|
||
update staging2.DBOECD_test_tractor_specifications_cleaner_block set model=__model;
|
||
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_tractor_specifications_cleaner_block');
|
||
|
||
/*transmission_clutch_block*/
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_transmission_clutch_block',__file_mnemonic,__file_sheet_mnemonic,17);
|
||
err_block:='DBOECD_test_transmission_clutch_block';
|
||
|
||
|
||
insert into staging2.DBOECD_test_transmission_clutch_block(dummy_f) values ('dummy');
|
||
|
||
|
||
update staging2.DBOECD_test_transmission_clutch_block a
|
||
set clutch_type=(
|
||
select column6 from staging2.stg_process_table_dboecd_test b
|
||
where trim(' :' from b.column3)='Type' and block_row_number=2 and b.block_tag='DBOECD_test_transmission')
|
||
where dummy_F='dummy';
|
||
|
||
update staging2.DBOECD_test_transmission_clutch_block a
|
||
set steering_type=(
|
||
select column6 from staging2.stg_process_table_dboecd_test b
|
||
where trim(' :' from b.column3)='Type' and block_row_number=5 and b.block_tag='DBOECD_test_transmission')
|
||
where dummy_F='dummy';
|
||
|
||
update staging2.DBOECD_test_transmission_clutch_block a
|
||
set diameter_of_disc_mm=(
|
||
select column6 from staging2.stg_process_table_dboecd_test b
|
||
where trim(' :' from b.column3)='Diameter of disc, mm' and b.block_tag='DBOECD_test_transmission')
|
||
where dummy_F='dummy';
|
||
|
||
|
||
execute 'update staging2.DBOECD_test_transmission_clutch_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_transmission_clutch_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_transmission_clutch_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_transmission_clutch_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_transmission_clutch_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_transmission_clutch_block set make=__make;
|
||
update staging2.DBOECD_test_transmission_clutch_block set model=__model;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_transmission_clutch_block');
|
||
|
||
/*transmission_drawbar_block*/
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_transmission_drawbar_block',__file_mnemonic,__file_sheet_mnemonic,18);
|
||
err_block:='DBOECD_test_transmission_drawbar_block';
|
||
|
||
insert into staging2.DBOECD_test_transmission_drawbar_block(dummy_f) values ('dummy');
|
||
|
||
update staging2.DBOECD_test_transmission_drawbar_block a
|
||
set type=(
|
||
select column8 from staging2.stg_process_table_dboecd_test b
|
||
where trim(' :' from b.column3)='Type' and block_row_number=7 and b.block_tag='DBOECD_test_transmission')
|
||
where dummy_F='dummy';
|
||
|
||
update staging2.DBOECD_test_transmission_drawbar_block a
|
||
set Height_above_ground_max_mm=(
|
||
select column8 from staging2.stg_process_table_dboecd_test b
|
||
where trim(' :' from b.column3)='Height above ground (max), mm' and block_row_number=9 and b.block_tag='DBOECD_test_transmission')
|
||
where dummy_F='dummy';
|
||
|
||
update staging2.DBOECD_test_transmission_drawbar_block a
|
||
set Height_above_ground_min_mm=(
|
||
select column8 from staging2.stg_process_table_dboecd_test b
|
||
where trim(' :' from b.column3)='Height above ground (min), mm' and block_row_number=11 and b.block_tag='DBOECD_test_transmission')
|
||
where dummy_F='dummy';
|
||
|
||
update staging2.DBOECD_test_transmission_drawbar_block a
|
||
set Position_related_to_PTO=(
|
||
select column8 from staging2.stg_process_table_dboecd_test b
|
||
where trim(' :' from b.column3)='Position related to PTO' and block_row_number=13 and b.block_tag='DBOECD_test_transmission')
|
||
where dummy_F='dummy';
|
||
|
||
update staging2.DBOECD_test_transmission_drawbar_block a
|
||
set Wheel_Base=(
|
||
select column8 from staging2.stg_process_table_dboecd_test b
|
||
where trim(' :' from b.column3)='Wheel Base' and block_row_number=15 and b.block_tag='DBOECD_test_transmission')
|
||
where dummy_F='dummy';
|
||
|
||
execute 'update staging2.DBOECD_test_transmission_drawbar_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_transmission_drawbar_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_transmission_drawbar_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_transmission_drawbar_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_transmission_drawbar_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_transmission_drawbar_block set make=__make;
|
||
update staging2.DBOECD_test_transmission_drawbar_block set model=__model;
|
||
|
||
update staging2.dboecd_test_transmission_drawbar_block a
|
||
set test_condition=b.c3
|
||
from staging2.dboecd_test_test_condition_block b
|
||
where a.file_syspk=b.file_syspk;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_transmission_drawbar_block');
|
||
|
||
/*transmission_speed_chart_desc_block*/
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_transmission_speed_chart_desc_block',__file_mnemonic,__file_sheet_mnemonic,19);
|
||
err_block:='DBOECD_test_transmission_speed_chart_desc_block';
|
||
|
||
insert into staging2.DBOECD_test_transmission_speed_chart_desc_block
|
||
(descr)
|
||
select column10
|
||
from
|
||
staging2.stg_process_table_dboecd_test b
|
||
where b.block_tag='DBOECD_test_transmission'
|
||
and block_row_number=1;
|
||
|
||
|
||
execute 'update staging2.DBOECD_test_transmission_speed_chart_desc_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_transmission_speed_chart_desc_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_transmission_speed_chart_desc_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_transmission_speed_chart_desc_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_transmission_speed_chart_desc_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_transmission_speed_chart_desc_block set make=__make;
|
||
update staging2.DBOECD_test_transmission_speed_chart_desc_block set model=__model;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_transmission_speed_chart_desc_block');
|
||
|
||
/*transmission_speed_chart_desc_block*/
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_transmission_speed_chart_block',__file_mnemonic,__file_sheet_mnemonic,20);
|
||
/*transmission_speed_chart_block*/
|
||
err_block:='DBOECD_test_transmission_speed_chart_block';
|
||
|
||
insert into staging2.DBOECD_test_transmission_speed_chart_block
|
||
(gear,forward,reverse)
|
||
select column10,column11, column13 from
|
||
staging2.stg_process_table_dboecd_test b
|
||
where b.block_tag='DBOECD_test_transmission'
|
||
and block_row_number>=3;
|
||
|
||
|
||
execute 'update staging2.dboecd_test_transmission_speed_chart_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.dboecd_test_transmission_speed_chart_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.dboecd_test_transmission_speed_chart_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.dboecd_test_transmission_speed_chart_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.dboecd_test_transmission_speed_chart_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.dboecd_test_transmission_speed_chart_block set make=__make;
|
||
update staging2.dboecd_test_transmission_speed_chart_block set model=__model;
|
||
delete from staging2.dboecd_test_transmission_speed_chart_block where gear is null;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_transmission_speed_chart_block');
|
||
|
||
/*transmission_wheels_in*/
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_transmission_wheels_block',__file_mnemonic,__file_sheet_mnemonic,21);
|
||
err_block:='DBOECD_test_transmission_wheels_block';
|
||
|
||
insert into staging2.DBOECD_test_transmission_wheels_int(Descriptions,Steered_wheels,Driving_wheel)
|
||
select column15,column20,column23 from staging2.stg_process_table_dboecd_test b
|
||
where b.block_tag='DBOECD_test_transmission'
|
||
and block_row_number>=3 and block_row_number<=11
|
||
order by block_row_number;
|
||
|
||
update staging2.DBOECD_test_transmission_wheels_int
|
||
set Driving_wheel=Steered_wheels
|
||
where trim(' :' from Descriptions)='Location of driving wheel'
|
||
and Driving_wheel ='';
|
||
|
||
|
||
execute 'update staging2.DBOECD_test_transmission_wheels_int set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_transmission_wheels_int set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_transmission_wheels_int set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_transmission_wheels_int set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_transmission_wheels_int set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_transmission_wheels_int set make=__make;
|
||
update staging2.DBOECD_test_transmission_wheels_int set model=__model;
|
||
|
||
|
||
/*transmission_wheels_block*/
|
||
insert into staging2.dboecd_test_transmission_wheels_block
|
||
(
|
||
dummy_f,
|
||
Location_of_driving_wheel,
|
||
Make_of_tyres,
|
||
types,
|
||
size,
|
||
Maximum_permissible_load_kg,
|
||
Ply_rating,
|
||
Track_width_max_mm,
|
||
Track_width_min_mm,
|
||
Inflation_pressure_kg_cm2
|
||
|
||
)
|
||
SELECT *
|
||
FROM crosstab(
|
||
'SELECT unnest(''{steered_wheels,driving_wheel}''::text[]) AS col
|
||
, row_number() OVER ()
|
||
, unnest(ARRAY[steered_wheels::text,driving_wheel::text]) AS val
|
||
FROM staging2.dboecd_test_transmission_wheels_int
|
||
ORDER BY generate_series(1,10),2'
|
||
) t ( col text,a_1 text,a_2 text,a_3 text,a_4 text,a_5 text,a_6 text,a_7 text,a_8 text,a_9 text);
|
||
|
||
delete from dboecd_test_transmission_wheels_block where dummy_f is null;
|
||
|
||
|
||
execute 'update staging2.dboecd_test_transmission_wheels_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.dboecd_test_transmission_wheels_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.dboecd_test_transmission_wheels_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.dboecd_test_transmission_wheels_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.dboecd_test_transmission_wheels_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.dboecd_test_transmission_wheels_block set make=__make;
|
||
update staging2.dboecd_test_transmission_wheels_block set model=__model;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_transmission_wheels_block');
|
||
|
||
/*transmission_fuel_lubricant*/
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_transmission_fuel_lubricant_block',__file_mnemonic,__file_sheet_mnemonic,22);
|
||
err_block:='DBOECD_test_transmission_fuel_lubricant_block';
|
||
|
||
|
||
insert into staging2.DBOECD_test_transmission_fuel_lubricant_int( Descriptions,Diesel_BS_IV,Engine_oil,Transmission_oil)
|
||
select column15,column18,column20,column23 from staging2.stg_process_table_dboecd_test b
|
||
where b.block_tag='DBOECD_test_transmission'
|
||
and block_row_number>=14 and block_row_number<=18
|
||
order by block_row_number;
|
||
|
||
|
||
execute 'update staging2.DBOECD_test_transmission_fuel_lubricant_int set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_transmission_fuel_lubricant_int set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_transmission_fuel_lubricant_int set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_transmission_fuel_lubricant_int set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_transmission_fuel_lubricant_int set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_transmission_fuel_lubricant_int set make=__make;
|
||
update staging2.DBOECD_test_transmission_fuel_lubricant_int set model=__model;
|
||
|
||
|
||
/*transmission_fuel_lubricant_block*/
|
||
insert into staging2.DBOECD_test_transmission_fuel_lubricant_block
|
||
(
|
||
dummy_f,
|
||
Trade_name,
|
||
type,
|
||
Octane_Cetane_number,
|
||
Viscosity,
|
||
Density_at_15C
|
||
)
|
||
SELECT *
|
||
FROM crosstab(
|
||
'SELECT unnest(''{diesel_bs_iv,engine_oil,transmission_oil}''::text[]) AS col
|
||
, row_number() OVER ()
|
||
, unnest(ARRAY[diesel_bs_iv::text,engine_oil::text,transmission_oil::text]) AS val
|
||
FROM staging2.DBOECD_test_transmission_fuel_lubricant_int
|
||
ORDER BY generate_series(1,15),2'
|
||
) t (col text,a_1 text,a_2 text,a_3 text,a_4 text,a_5 text);
|
||
|
||
delete from DBOECD_test_transmission_fuel_lubricant_block where dummy_f is null;
|
||
|
||
execute 'update staging2.DBOECD_test_transmission_fuel_lubricant_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_transmission_fuel_lubricant_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_transmission_fuel_lubricant_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_transmission_fuel_lubricant_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_transmission_fuel_lubricant_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_transmission_fuel_lubricant_block set make=__make;
|
||
update staging2.DBOECD_test_transmission_fuel_lubricant_block set model=__model;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_transmission_fuel_lubricant_block');
|
||
|
||
/*DBOECD_test_remarks_block*/
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_remarks_block',__file_mnemonic,__file_sheet_mnemonic,23);
|
||
err_block:='DBOECD_test_remarks_block';
|
||
|
||
insert into staging2.DBOECD_test_remarks_block
|
||
(
|
||
remarks ,
|
||
parameter ,
|
||
acceptance_criteria,
|
||
observations
|
||
)
|
||
select column3,column13,column15,column23 from staging2.stg_process_table_dboecd_test b
|
||
where b.block_tag='DBOECD_test_remarks'
|
||
and block_row_number>3 and block_row_number<=5;
|
||
|
||
|
||
execute 'update staging2.DBOECD_test_remarks_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_remarks_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_remarks_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_remarks_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_remarks_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_remarks_block set make=__make;
|
||
update staging2.DBOECD_test_remarks_block set model=__model;
|
||
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_remarks_block');
|
||
|
||
/*DBOECD_test_remarks_footer_block*/
|
||
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBOECD_test_remarks_footer_block',__file_mnemonic,__file_sheet_mnemonic,24);
|
||
err_block:='DBOECD_test_remarks_footer_block';
|
||
|
||
insert into staging2.DBOECD_test_remarks_footer_block(dummy_f) values ('dummy');
|
||
|
||
update staging2.DBOECD_test_remarks_footer_block a
|
||
set prepared_by=( select column7 from staging2.stg_process_table_dboecd_test b
|
||
where b.block_tag='DBOECD_test_remarks'
|
||
and trim(column3)='Prepared by' )
|
||
where dummy_F='dummy';
|
||
|
||
update staging2.DBOECD_test_remarks_footer_block a
|
||
set approved_by=( select column7 from staging2.stg_process_table_dboecd_test b
|
||
where b.block_tag='DBOECD_test_remarks'
|
||
and trim(column3)='Approved by' )
|
||
where dummy_F='dummy';
|
||
|
||
update staging2.DBOECD_test_remarks_footer_block a
|
||
set comments=( select column3 from staging2.stg_process_table_dboecd_test b
|
||
where b.block_tag='DBOECD_test_remarks'
|
||
and block_row_number=9 )
|
||
where dummy_F='dummy';
|
||
|
||
update staging2.DBOECD_test_remarks_footer_block a
|
||
set rev1=( select column3 from staging2.stg_process_table_dboecd_test b
|
||
where b.block_tag='DBOECD_test_remarks'
|
||
and block_row_number=10 )
|
||
where dummy_F='dummy';
|
||
|
||
|
||
update staging2.DBOECD_test_remarks_footer_block a
|
||
set rev2=( select column9 from staging2.stg_process_table_dboecd_test b
|
||
where b.block_tag='DBOECD_test_remarks'
|
||
and block_row_number=10 )
|
||
where dummy_F='dummy';
|
||
|
||
update staging2.DBOECD_test_remarks_footer_block a
|
||
set rev3=( select column18 from staging2.stg_process_table_dboecd_test b
|
||
where b.block_tag='DBOECD_test_remarks'
|
||
and block_row_number=10 )
|
||
where dummy_F='dummy';
|
||
|
||
update staging2.DBOECD_test_remarks_footer_block a
|
||
set replaces=( select column23 from staging2.stg_process_table_dboecd_test b
|
||
where b.block_tag='DBOECD_test_remarks'
|
||
and block_row_number=6)
|
||
where dummy_F='dummy';
|
||
|
||
update staging2.DBOECD_test_remarks_footer_block a
|
||
set revision_no=( select column23 from staging2.stg_process_table_dboecd_test b
|
||
where b.block_tag='DBOECD_test_remarks'
|
||
and block_row_number=7)
|
||
where dummy_F='dummy';
|
||
|
||
update staging2.DBOECD_test_remarks_footer_block a
|
||
set date=( select column19 from staging2.stg_process_table_dboecd_test b
|
||
where b.block_tag='DBOECD_test_remarks'
|
||
and block_row_number=6)
|
||
where dummy_F='dummy';
|
||
|
||
update staging2.DBOECD_test_remarks_footer_block a
|
||
set date=( select column19 from staging2.stg_process_table_dboecd_test b
|
||
where b.block_tag='DBOECD_test_remarks'
|
||
and block_row_number=7 and column9 is not null)
|
||
where dummy_F='dummy';
|
||
|
||
|
||
execute 'update staging2.DBOECD_test_remarks_footer_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update staging2.DBOECD_test_remarks_footer_block set client_id='||p_client_id||'';
|
||
execute 'update staging2.DBOECD_test_remarks_footer_block set function_id='||p_function_id||'';
|
||
execute 'update staging2.DBOECD_test_remarks_footer_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
||
execute 'update staging2.DBOECD_test_remarks_footer_block set file_mnemonic='''||p_file_mnemonic||'''';
|
||
update staging2.DBOECD_test_remarks_footer_block set make=__make;
|
||
update staging2.DBOECD_test_remarks_footer_block set model=__model;
|
||
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBOECD_test_remarks_footer_block');
|
||
|
||
perform fw_core.fn_insert_db_error ( __client_id, __function_id, 1001, 'Compegence', __file_syspk ,__file_mnemonic,__file_sheet_mnemonic ,null,'stg2', 'fn_dboecd_test_block', err_state, err_msg, err_detail, err_hint, err_context,'success');
|
||
|
||
end
|
||
$function$
|
||
;
|