1359 lines
62 KiB
PL/PgSQL
Executable File
1359 lines
62 KiB
PL/PgSQL
Executable File
drop function if exists staging2.fn_dbstd_test_block;
|
|
CREATE OR REPLACE FUNCTION staging2.fn_dbstd_test_block(p_client_id int,p_function_id int, p_file_mnemonic text,
|
|
p_file_sheet_mnemonic text, p_file_syspk int)
|
|
RETURNS text AS $$
|
|
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 _error int;
|
|
declare err_block text;
|
|
|
|
begin
|
|
|
|
/************************************************************
|
|
Function Name:fn_dbstd_test_block
|
|
Function Desc: This function populates data into staging 2 block
|
|
File Format: DBSTD
|
|
Sheet Format: DBSTD_TEST
|
|
Creation Date: March 25 2021
|
|
Updation Date:
|
|
Author: compegence team
|
|
Function Call: select staging2.fn_dbstd_test_block(p_client_id,p_function_id, p_file_mnemonic,
|
|
p_file_sheet_mnemonic, p_file_syspk)
|
|
Function call ex: select staging2.fn_dbstd_test_block(1,2,'DBSTD','DBSTD_TEST',262)
|
|
***************************************************************/
|
|
|
|
|
|
SET search_path TO staging2;
|
|
|
|
/* to process multiple files - re-runnability*/
|
|
|
|
truncate table staging2.dbstd_test_transmission_wheels_int;
|
|
truncate table staging2.dbstd_test_h1_int;
|
|
truncate table staging2.dbstd_test_engine_rpm_engine_to_pto_block;
|
|
truncate table staging2.dbstd_test_engine_rpm_required_pull_block;
|
|
truncate table staging2.dbstd_test_engine_rpm_weight_block;
|
|
truncate table staging2.dbstd_test_engine_rpm_tyre_details_block;
|
|
truncate table staging2.DBSTD_test_engine_RPM_tyre_details_int;
|
|
truncate table staging2.dbstd_test_test_condition_block;
|
|
truncate table staging2.dbstd_test_drawbar_performance_selected_summary_block;
|
|
truncate table staging2.dbstd_test_drawbar_performance_gear_performance_block;
|
|
truncate table staging2.dbstd_test_test_equipment_used_block;
|
|
truncate table staging2.dbstd_test_measurement_uncertainty_block;
|
|
truncate table staging2.dbstd_test_tractor_specifications_engine_details_block;
|
|
truncate table staging2.dbstd_test_tractor_specifications_cylinders_block;
|
|
truncate table staging2.dbstd_test_tractor_specifications_fuel_and_injection_block;
|
|
truncate table staging2.dbstd_test_tractor_specifications_cleaner_block;
|
|
truncate table staging2.dbstd_test_transmission_clutch_block;
|
|
truncate table staging2.dbstd_test_transmission_drawbar_block;
|
|
truncate table staging2.dbstd_test_transmission_speed_chart_desc_block;
|
|
truncate table staging2.dbstd_test_transmission_wheels_block;
|
|
truncate table staging2.dbstd_test_transmission_fuel_lubricant_int;
|
|
truncate table staging2.dbstd_test_remarks_block;
|
|
truncate table staging2.dbstd_test_remarks_footer_block;
|
|
truncate table staging2.dbstd_test_h1_block;
|
|
truncate table staging2.dbstd_test_transmission_fuel_lubricant_block;
|
|
truncate table staging2.dbstd_test_engine_rpm_rpm_block;
|
|
truncate table staging2.dbstd_test_transmission_speed_chart_block;
|
|
truncate table staging2.stg_specific_table_dbstd_test;
|
|
truncate table staging2.stg_process_table_dbstd_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_dbstd_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||'';
|
|
|
|
|
|
/* standardizing keywords */
|
|
update staging2.stg_specific_table_dbstd_test set column3='Remarks' where column3 like 'Remarks%';
|
|
update staging2.stg_specific_table_dbstd_test set column15='Objective' where column15 like 'Objective%';
|
|
|
|
update staging2.stg_specific_table_dbstd_test set column3 = TRIM (TRAILING FROM column3 );
|
|
update staging2.stg_specific_table_dbstd_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_dbstd_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_dbstd_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*/
|
|
|
|
execute 'insert into staging2.stg_process_table_dbstd_test
|
|
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
|
|
from staging2.stg_specific_table_dbstd_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_dbstd_test
|
|
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag from staging2.stg_specific_table_dbstd_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_dbstd_test
|
|
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag from staging2.stg_specific_table_dbstd_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_dbstd_test
|
|
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag from staging2.stg_specific_table_dbstd_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_dbstd_test
|
|
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
|
|
from staging2.stg_specific_table_dbstd_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_dbstd_test
|
|
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
|
|
from staging2.stg_specific_table_dbstd_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 equipments used''
|
|
and b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
|
|
|
|
execute 'insert into staging2.stg_process_table_dbstd_test
|
|
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
|
|
from staging2.stg_specific_table_dbstd_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_dbstd_test
|
|
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
|
|
from staging2.stg_specific_table_dbstd_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_dbstd_test
|
|
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
|
|
from staging2.stg_specific_table_dbstd_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_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications' and trim(column4)='Make' ;
|
|
|
|
select column6 into __model from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_Test_H1' and trim(column3) = 'Tractor Model';
|
|
|
|
/*block starts - DBSTD_TEST_H1_BLOCK */
|
|
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_TEST_H1_BLOCK',__file_mnemonic,__file_sheet_mnemonic,1);
|
|
|
|
insert into staging2.DBSTD_TEST_H1_INT (c1,c2)
|
|
select a.column3,column6 from staging2.stg_process_table_dbstd_test a where block_tag='DBSTD_Test_H1';
|
|
|
|
insert into staging2.DBSTD_TEST_H1_INT (c1,c2)
|
|
select a.column9,column12 from staging2.stg_process_table_dbstd_test a where block_tag='DBSTD_Test_H1';
|
|
|
|
insert into staging2.DBSTD_TEST_H1_INT (c1,c2)
|
|
select 'Objective',column15
|
|
from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_Test_H1'
|
|
and row_number=
|
|
(
|
|
select row_number+1 from
|
|
staging2.stg_process_table_dbstd_test a
|
|
where column15='Objective'
|
|
and block_tag='DBSTD_Test_H1'
|
|
);
|
|
|
|
insert into staging2.DBSTD_TEST_H1_INT (c1,c2)
|
|
select 'Acceptance criteria',column15
|
|
from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_Test_H1'
|
|
and row_number=
|
|
(
|
|
select row_number+1 from
|
|
staging2.stg_process_table_dbstd_test a
|
|
where column15='Acceptance criteria'
|
|
and block_tag='DBSTD_Test_H1'
|
|
);
|
|
|
|
insert into staging2.DBSTD_TEST_H1_Block
|
|
(
|
|
dummy_f,
|
|
Test_Request_no,
|
|
Sample_Receipt_Date,
|
|
Test_report_No,
|
|
Tractor_Model,
|
|
Generation,
|
|
Customer_Name,
|
|
Test_Engineer,
|
|
Test_Report_Date,
|
|
No_of_Sample,
|
|
Test_Start_Date,
|
|
Test_End_Date,
|
|
Tractor_Sr_No,
|
|
Test_Standard_Refer,
|
|
Test_Location,
|
|
Operator_Name,
|
|
Project_Group,
|
|
Objective,
|
|
Acceptance_criteria
|
|
)
|
|
SELECT *
|
|
FROM crosstab(
|
|
'SELECT unnest(''{c2}''::text[]) AS col
|
|
, row_number() OVER ()
|
|
, unnest(ARRAY[c2::text]) AS val
|
|
FROM staging2.DBSTD_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);
|
|
|
|
|
|
delete from staging2.DBSTD_TEST_H1_Block where dummy_f is null ;
|
|
execute 'update staging2.DBSTD_TEST_H1_Block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_TEST_H1_Block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_TEST_H1_Block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_TEST_H1_Block set make=__make;
|
|
update staging2.DBSTD_TEST_H1_Block set model=__model;
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_TEST_H1_BLOCK');
|
|
|
|
/* block DBSTD_test_engine_RPM_RPM_block */
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_engine_RPM_RPM_block',__file_mnemonic,__file_sheet_mnemonic,2);
|
|
|
|
insert into staging2.DBSTD_test_engine_RPM_RPM_block(dummy_f) values ('dummy');
|
|
|
|
update staging2.DBSTD_test_engine_RPM_RPM_block
|
|
set low_idle_declared=(select column6 from
|
|
staging2.stg_process_table_dbstd_test where block_row_number=2 and column3='Low Idle RPM' and block_tag='DBSTD_test_engine_RPM')
|
|
where dummy_f='dummy';
|
|
|
|
update staging2.DBSTD_test_engine_RPM_RPM_block
|
|
set low_idle_observed=(select column8 from
|
|
staging2.stg_process_table_dbstd_test where block_row_number=2 and column3='Low Idle RPM' and block_tag='DBSTD_test_engine_RPM')
|
|
where dummy_f='dummy';
|
|
|
|
update staging2.DBSTD_test_engine_RPM_RPM_block
|
|
set high_idle_declared=(select column6 from
|
|
staging2.stg_process_table_dbstd_test where block_row_number=3 and column3='High Idle RPM' and block_tag='DBSTD_test_engine_RPM')
|
|
where dummy_f='dummy';
|
|
|
|
update staging2.DBSTD_test_engine_RPM_RPM_block
|
|
set high_idle_observed=(select column8 from
|
|
staging2.stg_process_table_dbstd_test where block_row_number=3 and column3='High Idle RPM' and block_tag='DBSTD_test_engine_RPM')
|
|
where dummy_f='dummy';
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_engine_RPM_RPM_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_engine_RPM_RPM_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_engine_RPM_RPM_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_engine_RPM_RPM_block set make=__make;
|
|
update staging2.DBSTD_test_engine_RPM_RPM_block set model=__model;
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_engine_RPM_RPM_block');
|
|
|
|
/* block DBSTD_test_engine_RPM_engine_to_PTO_block*/
|
|
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'dbstd_test_engine_rpm_engine_to_pto_block',__file_mnemonic,__file_sheet_mnemonic,3);
|
|
insert into staging2.DBSTD_test_engine_RPM_engine_to_PTO_block(dummy_f) values ('dummy');
|
|
|
|
|
|
update staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set rated_speed=
|
|
(select column6 from staging2.stg_process_table_dbstd_test a
|
|
where a.block_tag='DBSTD_test_engine_RPM'
|
|
and block_row_number=4 )
|
|
where dummy_f='dummy';
|
|
|
|
update staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set engine_to_PTO_ratio=
|
|
(select column6 from staging2.stg_process_table_dbstd_test a
|
|
where a.block_tag='DBSTD_test_engine_RPM'
|
|
and block_row_number=5 )
|
|
where dummy_f='dummy';
|
|
|
|
update staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set wheel_base_mm=
|
|
(select column6 from staging2.stg_process_table_dbstd_test a
|
|
where a.block_tag='DBSTD_test_engine_RPM'
|
|
and block_row_number=6 )
|
|
where dummy_f='dummy';
|
|
|
|
|
|
update staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set engine_power_hp=
|
|
(select column6 from staging2.stg_process_table_dbstd_test a
|
|
where a.file_mnemonic='DBSTD' and a.file_sheet_mnemonic='DBSTD_TEST' and a.block_tag='DBSTD_test_engine_RPM'
|
|
and block_row_number=7 )
|
|
where dummy_f='dummy';
|
|
|
|
update staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set PTO_Power_hp=
|
|
(select column6 from staging2.stg_process_table_dbstd_test a
|
|
where a.file_mnemonic='DBSTD' and a.file_sheet_mnemonic='DBSTD_TEST' and a.block_tag='DBSTD_test_engine_RPM'
|
|
and block_row_number=8 )
|
|
where dummy_f='dummy';
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set make=__make;
|
|
update staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set model=__model;
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'dbstd_test_engine_rpm_engine_to_pto_block');
|
|
|
|
/* block DBSTD_test_engine_RPM_required_pull_block */
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_engine_RPM_required_pull_block',__file_mnemonic,__file_sheet_mnemonic,4);
|
|
|
|
insert into staging2.DBSTD_test_engine_RPM_required_pull_block ( pull_type) values ('UB'), ('B');
|
|
|
|
update staging2.DBSTD_test_engine_RPM_required_pull_block
|
|
set required_pull_in_kg=(select column7 from staging2.stg_process_table_dbstd_test a
|
|
where a.block_tag='DBSTD_test_engine_RPM'
|
|
and column3='Required pull in (kg)')
|
|
where pull_type='UB';
|
|
|
|
update staging2.DBSTD_test_engine_RPM_required_pull_block
|
|
set required_pull_in_kg=(select column9 from staging2.stg_process_table_dbstd_test a
|
|
where a.block_tag='DBSTD_test_engine_RPM'
|
|
and column3='Required pull in (kg)')
|
|
where pull_type='B';
|
|
|
|
update staging2.DBSTD_test_engine_RPM_required_pull_block
|
|
set required_power_in_hp=(select column7 from staging2.stg_process_table_dbstd_test a
|
|
where a.block_tag='DBSTD_test_engine_RPM'
|
|
and column3='Required Power in hp')
|
|
where pull_type='UB';
|
|
|
|
update staging2.DBSTD_test_engine_RPM_required_pull_block
|
|
set required_power_in_hp=(select column9 from staging2.stg_process_table_dbstd_test a
|
|
where a.block_tag='DBSTD_test_engine_RPM'
|
|
and column3='Required Power in hp')
|
|
where pull_type='B';
|
|
|
|
update staging2.DBSTD_test_engine_RPM_required_pull_block
|
|
set actual_pull_in_kg=(select column7 from staging2.stg_process_table_dbstd_test a
|
|
where a.block_tag='DBSTD_test_engine_RPM'
|
|
and column3='Actual pull in (kg)')
|
|
where pull_type='UB';
|
|
|
|
update staging2.DBSTD_test_engine_RPM_required_pull_block
|
|
set actual_pull_in_kg=(select column9 from staging2.stg_process_table_dbstd_test a
|
|
where a.block_tag='DBSTD_test_engine_RPM'
|
|
and column3='Actual pull in (kg)')
|
|
where pull_type='B';
|
|
|
|
update staging2.DBSTD_test_engine_RPM_required_pull_block
|
|
set actual_power_in_hp=(select column7 from staging2.stg_process_table_dbstd_test a
|
|
where a.block_tag='DBSTD_test_engine_RPM'
|
|
and column3='actual power in hp')
|
|
where pull_type='UB';
|
|
|
|
update staging2.DBSTD_test_engine_RPM_required_pull_block
|
|
set actual_power_in_hp=(select column9 from staging2.stg_process_table_dbstd_test a
|
|
where a.block_tag='DBSTD_test_engine_RPM'
|
|
and column3='actual power in hp')
|
|
where pull_type='B';
|
|
|
|
update staging2.DBSTD_test_engine_RPM_required_pull_block
|
|
set calculated_hitch_height_mm=(select column7 from staging2.stg_process_table_dbstd_test a
|
|
where a.block_tag='DBSTD_test_engine_RPM'
|
|
and column3='Calculated hitch height (mm)')
|
|
where pull_type='UB';
|
|
|
|
update staging2.DBSTD_test_engine_RPM_required_pull_block
|
|
set calculated_hitch_height_mm=(select column9 from staging2.stg_process_table_dbstd_test a
|
|
where a.block_tag='DBSTD_test_engine_RPM'
|
|
and column3='Calculated hitch height (mm)')
|
|
where pull_type='B';
|
|
|
|
update staging2.DBSTD_test_engine_RPM_required_pull_block
|
|
set actual_hitch_height_mm=(select column7 from staging2.stg_process_table_dbstd_test a
|
|
where a.block_tag='DBSTD_test_engine_RPM'
|
|
and column3='Actual hitch height (mm)')
|
|
where pull_type='UB';
|
|
|
|
update staging2.DBSTD_test_engine_RPM_required_pull_block
|
|
set actual_hitch_height_mm=(select column9 from staging2.stg_process_table_dbstd_test a
|
|
where a.block_tag='DBSTD_test_engine_RPM'
|
|
and column3='Actual hitch height (mm)')
|
|
where pull_type='B';
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_engine_RPM_required_pull_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_engine_RPM_required_pull_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_engine_RPM_required_pull_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_engine_RPM_required_pull_block set make=__make;
|
|
update staging2.DBSTD_test_engine_RPM_required_pull_block set model=__model;
|
|
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_engine_RPM_required_pull_block');
|
|
|
|
/* block DBSTD_test_engine_RPM_weight_block */
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_engine_RPM_weight_block',__file_mnemonic,__file_sheet_mnemonic,5);
|
|
|
|
insert into staging2.DBSTD_test_engine_RPM_weight_block(
|
|
weight_kg ,
|
|
Front ,
|
|
rear ,
|
|
Total
|
|
)
|
|
select column10,column13,column17,column22 from staging2.stg_process_table_dbstd_test a
|
|
where a.block_tag='DBSTD_test_engine_RPM'
|
|
and block_row_number in(2,3);
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_engine_RPM_weight_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_engine_RPM_weight_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_engine_RPM_weight_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_engine_RPM_weight_block set make=__make;
|
|
update staging2.DBSTD_test_engine_RPM_weight_block set model=__model;
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_engine_RPM_weight_block');
|
|
|
|
/*block - rpm tyre details block*/
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_engine_RPM_tyre_details_block',__file_mnemonic,__file_sheet_mnemonic,6);
|
|
|
|
insert into staging2.DBSTD_test_engine_RPM_tyre_details_int
|
|
( s_no,tyre_details,front,rear)
|
|
|
|
select column10,column11,column15,column20
|
|
from staging2.stg_process_table_dbstd_test a
|
|
where a.block_tag='DBSTD_test_engine_RPM'
|
|
and block_row_number >=5 and block_row_number<=14;
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_engine_RPM_tyre_details_int set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_engine_RPM_tyre_details_int set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_engine_RPM_tyre_details_int set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_engine_RPM_tyre_details_int set make=__make;
|
|
update staging2.DBSTD_test_engine_RPM_tyre_details_int set model=__model;
|
|
|
|
insert into staging2.DBSTD_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.DBSTD_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.DBSTD_test_engine_RPM_tyre_details_block where dummy_f is null;
|
|
|
|
execute 'update staging2.DBSTD_test_engine_RPM_tyre_details_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_engine_RPM_tyre_details_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_engine_RPM_tyre_details_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_engine_RPM_tyre_details_block set make=__make;
|
|
update staging2.DBSTD_test_engine_RPM_tyre_details_block set model=__model;
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_engine_RPM_tyre_details_block');
|
|
|
|
/* block DBSTD_test_test_condition_block */
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_test_condition_block',__file_mnemonic,__file_sheet_mnemonic,7);
|
|
|
|
insert into staging2.DBSTD_test_test_condition_block(c1,c2,c3)
|
|
select column3 c1,column10 c2,column15 c3
|
|
from staging2.stg_process_table_dbstd_test a
|
|
where a.block_tag='DBSTD_test_test_condition';
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_test_condition_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_test_condition_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_test_condition_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_test_condition_block set make=__make;
|
|
update staging2.DBSTD_test_test_condition_block set model=__model;
|
|
|
|
update staging2.dbstd_test_test_condition_block
|
|
set c3='B'
|
|
where c2='Ballasted';
|
|
|
|
update staging2.dbstd_test_test_condition_block
|
|
set c3='UB'
|
|
where c2='Un-ballasted';
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_test_condition_block');
|
|
|
|
/* block DBSTD_test_drawbar_performance_selected_summary_block */
|
|
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_drawbar_performance_selected_summary_block',__file_mnemonic,__file_sheet_mnemonic,8);
|
|
|
|
insert into staging2.DBSTD_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_dbstd_test a
|
|
where block_tag='DBSTD_test_drawbar_performance_selected_summary'
|
|
and block_row_number>3;
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_drawbar_performance_selected_summary_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_drawbar_performance_selected_summary_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_drawbar_performance_selected_summary_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_drawbar_performance_selected_summary_block set make=__make;
|
|
update staging2.DBSTD_test_drawbar_performance_selected_summary_block set model=__model;
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_drawbar_performance_selected_summary_block');
|
|
|
|
/* block DBSTD_test_drawbar_performance_gear_performance_block */
|
|
|
|
/* DBSTD_test_drawbar_performance_gear_performance_block*/
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_drawbar_performance_gear_performance_block',__file_mnemonic,__file_sheet_mnemonic,9);
|
|
|
|
insert into staging2.DBSTD_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_dbstd_test a
|
|
where block_tag='DBSTD_test_drawbar_performance_gear_performance'
|
|
and block_row_number>3;
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_drawbar_performance_gear_performance_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_drawbar_performance_gear_performance_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_drawbar_performance_gear_performance_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_drawbar_performance_gear_performance_block set make=__make;
|
|
update staging2.DBSTD_test_drawbar_performance_gear_performance_block set model=__model;
|
|
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_drawbar_performance_gear_performance_block');
|
|
|
|
/* block DBSTD_test_test_equipment_used_block */
|
|
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_test_equipment_used_block',__file_mnemonic,__file_sheet_mnemonic,10);
|
|
|
|
insert into staging2.DBSTD_test_test_equipment_used_block( s_no,instruments,instruments_no,cali_due_date)
|
|
select column3,column4,column8,column12
|
|
from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_test_equipment'
|
|
and block_row_number>2;
|
|
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_test_equipment_used_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_test_equipment_used_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_test_equipment_used_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_test_equipment_used_block set make=__make;
|
|
update staging2.DBSTD_test_test_equipment_used_block set model=__model;
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_test_equipment_used_block');
|
|
|
|
/* block DBSTD_test_measurement_uncertainty_block */
|
|
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_measurement_uncertainty_block',__file_mnemonic,__file_sheet_mnemonic,11);
|
|
|
|
insert into staging2.DBSTD_test_measurement_uncertainty_block(dummy_f) values ('dummy');
|
|
|
|
update staging2.DBSTD_test_measurement_uncertainty_block set load_cell=
|
|
(select column20 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_test_equipment' and trim(column15)='Load cell');
|
|
|
|
update staging2.DBSTD_test_measurement_uncertainty_block set rpm_meter=
|
|
(select column20 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_test_equipment' and trim(column15)='RPM Meter');
|
|
|
|
update staging2.DBSTD_test_measurement_uncertainty_block set speed=
|
|
(select column20 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_test_equipment' and trim(column15)='Speed');
|
|
|
|
|
|
update staging2.DBSTD_test_measurement_uncertainty_block set fuel_flow_meter=
|
|
(select column20 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_test_equipment' and trim(column15)='Fuel Flow meter');
|
|
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_measurement_uncertainty_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_measurement_uncertainty_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_measurement_uncertainty_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_measurement_uncertainty_block set make=__make;
|
|
update staging2.DBSTD_test_measurement_uncertainty_block set model=__model;
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_measurement_uncertainty_block');
|
|
|
|
/* block DBSTD_test_tractor_specifications_engine_details_block */
|
|
|
|
/*tractor_specifications_engine_details_block*/
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_tractor_specifications_engine_details_block',__file_mnemonic,__file_sheet_mnemonic,12);
|
|
|
|
insert into staging2.DBSTD_test_tractor_specifications_engine_details_block(dummy_f)
|
|
values ('dummy');
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_engine_details_block
|
|
set make_in_block=(select column6 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column4)='Make'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_engine_details_block
|
|
set type=(select column6 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column4)='Type'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_engine_details_block
|
|
set model_in_block=(select column6 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column4)='Model'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_engine_details_block
|
|
set serial_no=(select column6 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column4)='Serial No'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_engine_details_block
|
|
set E_P_ratio=(select column6 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column4)='E P Ratio'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_engine_details_block
|
|
set rated_speed=(select column6 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column4)='Rated Speed'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_engine_details_block
|
|
set high_idle=(select column6 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column4)='High Idle'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_engine_details_block
|
|
set high_idle=(select column6 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column4)='High Idle'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_engine_details_block
|
|
set low_idle=(select column8 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column4)='High Idle'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_tractor_specifications_engine_details_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_tractor_specifications_engine_details_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_tractor_specifications_engine_details_block set function_id='||p_function_id||'';
|
|
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_tractor_specifications_engine_details_block');
|
|
|
|
/* block DBSTD_test_tractor_specifications_cylinders_block */
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_tractor_specifications_cylinders_block',__file_mnemonic,__file_sheet_mnemonic,13);
|
|
|
|
insert into staging2.DBSTD_test_tractor_specifications_cylinders_block(dummy_f)
|
|
values ('dummy');
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_cylinders_block
|
|
set number=(select column11 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column9)='Number'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_cylinders_block
|
|
set stroke=(select column11 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column9)='Stroke'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_cylinders_block
|
|
set bore=(select column11 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column9)='Bore'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_cylinders_block
|
|
set capacity=(select column11 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column9)='Capacity'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_tractor_specifications_cylinders_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_tractor_specifications_cylinders_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_tractor_specifications_cylinders_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_tractor_specifications_cylinders_block set make=__make;
|
|
update staging2.DBSTD_test_tractor_specifications_cylinders_block set model=__model;
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_tractor_specifications_cylinders_block');
|
|
|
|
/*block fuel_and_injection_block*/
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_tractor_specifications_fuel_and_injection_block',__file_mnemonic,__file_sheet_mnemonic,14);
|
|
|
|
insert into staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block(dummy_f)
|
|
values ('dummy');
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block
|
|
set Make_type_and_model_of_injection_pump=(select column17 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column12)='Make, type and model of injection pump'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block
|
|
set capacity_of_fuel_tank_lit=(select column17 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column12)='Capacity of fuel tank lit'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block
|
|
set Make_type_and_model_of_injection_pump=(select column17 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column12)='Make, type and model of injection pump'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block
|
|
set Manufacturer_production_setting=(select column17 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column12)='Manufacturers production setting'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block
|
|
set Make_type_and_model_of_injectors=(select column17 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column12)='Make, type and model of injectors'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block
|
|
set Make_type_and_model_of_magneto_coil_and_distributor=(select column17 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column12)='Make, type and model of magneto, coil and distributor'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block
|
|
set Make_type_and_model_of_carburetor=(select column17 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column12)='Make, type and model of carburetor'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block
|
|
set Ignition_or_injection_timing_Manual_or_automatic=(select column17 from staging2.stg_process_table_dbstd_test a
|
|
where block_tag='DBSTD_test_tractor_specifications'
|
|
and trim(column12)='Ignition or injection timing (Manual or automatic)'
|
|
)
|
|
where dummy_f is not null;
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block set make=__make;
|
|
update staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block set model=__model;
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_tractor_specifications_fuel_and_injection_block');
|
|
|
|
/*tractor_specifications_cleaner_block*/
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_tractor_specifications_cleaner_block',__file_mnemonic,__file_sheet_mnemonic,15);
|
|
|
|
insert into staging2.DBSTD_test_tractor_specifications_cleaner_block(cleaner_type)
|
|
values ('Air cleaner'),('Precleaner (if fitted)');
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_cleaner_block
|
|
set type=
|
|
(select column23 from staging2.stg_process_table_dbstd_test a where
|
|
block_tag='DBSTD_test_tractor_specifications'
|
|
and column21='Type' and block_row_number=5)
|
|
where cleaner_type='Air cleaner';
|
|
|
|
update staging2.DBSTD_test_tractor_specifications_cleaner_block
|
|
set type=
|
|
(select column23 from staging2.stg_process_table_dbstd_test a where
|
|
block_tag='DBSTD_test_tractor_specifications'
|
|
and column21='Type' and block_row_number=9)
|
|
where cleaner_type='Precleaner (if fitted)';
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_tractor_specifications_cleaner_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_tractor_specifications_cleaner_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_tractor_specifications_cleaner_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_tractor_specifications_cleaner_block set make=__make;
|
|
update staging2.DBSTD_test_tractor_specifications_cleaner_block set model=__model;
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_tractor_specifications_cleaner_block');
|
|
|
|
|
|
/*transmission_clutch_block*/
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_transmission_clutch_block',__file_mnemonic,__file_sheet_mnemonic,16);
|
|
|
|
insert into staging2.DBSTD_test_transmission_clutch_block(dummy_f) values ('dummy');
|
|
|
|
|
|
update staging2.DBSTD_test_transmission_clutch_block a
|
|
set clutch_type=(
|
|
select column6 from staging2.stg_process_table_dbstd_test b
|
|
where b.column3='Type' and block_row_number=2 and b.block_tag='DBSTD_test_transmission')
|
|
where dummy_F='dummy';
|
|
|
|
update staging2.DBSTD_test_transmission_clutch_block a
|
|
set steering_type=(
|
|
select column6 from staging2.stg_process_table_dbstd_test b
|
|
where b.column3='Type' and block_row_number=5 and b.block_tag='DBSTD_test_transmission')
|
|
where dummy_F='dummy';
|
|
|
|
update staging2.DBSTD_test_transmission_clutch_block a
|
|
set diameter_of_disc_mm=(
|
|
select column6 from staging2.stg_process_table_dbstd_test b
|
|
where trim(b.column3)='Diameter of disc, mm' and b.block_tag='DBSTD_test_transmission')
|
|
where dummy_F='dummy';
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_transmission_clutch_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_transmission_clutch_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_transmission_clutch_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_transmission_clutch_block set make=__make;
|
|
update staging2.DBSTD_test_transmission_clutch_block set model=__model;
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_transmission_clutch_block');
|
|
|
|
/*transmission_drawbar_block*/
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_transmission_drawbar_block',__file_mnemonic,__file_sheet_mnemonic,17);
|
|
|
|
insert into staging2.DBSTD_test_transmission_drawbar_block(dummy_f) values ('dummy');
|
|
|
|
update staging2.DBSTD_test_transmission_drawbar_block a
|
|
set type=(
|
|
select column8 from staging2.stg_process_table_dbstd_test b
|
|
where trim(b.column3)='Type' and block_row_number=7 and b.block_tag='DBSTD_test_transmission')
|
|
where dummy_F='dummy';
|
|
|
|
update staging2.DBSTD_test_transmission_drawbar_block a
|
|
set Height_above_ground_max_mm=(
|
|
select column8 from staging2.stg_process_table_dbstd_test b
|
|
where trim(b.column3)='Height above ground (max) mm' and block_row_number=9 and b.block_tag='DBSTD_test_transmission')
|
|
where dummy_F='dummy';
|
|
|
|
update staging2.DBSTD_test_transmission_drawbar_block a
|
|
set Height_above_ground_max_mm=(
|
|
select column8 from staging2.stg_process_table_dbstd_test b
|
|
where trim(b.column3)='Height above ground (max) mm' and block_row_number=9 and b.block_tag='DBSTD_test_transmission')
|
|
where dummy_F='dummy';
|
|
|
|
update staging2.DBSTD_test_transmission_drawbar_block a
|
|
set Height_above_ground_min_mm=(
|
|
select column8 from staging2.stg_process_table_dbstd_test b
|
|
where trim(b.column3)='Height above ground (min) mm' and block_row_number=11 and b.block_tag='DBSTD_test_transmission')
|
|
where dummy_F='dummy';
|
|
|
|
update staging2.DBSTD_test_transmission_drawbar_block a
|
|
set Position_related_to_PTO=(
|
|
select column8 from staging2.stg_process_table_dbstd_test b
|
|
where trim(b.column3)='Position related to PTO' and block_row_number=13 and b.block_tag='DBSTD_test_transmission')
|
|
where dummy_F='dummy';
|
|
|
|
update staging2.DBSTD_test_transmission_drawbar_block a
|
|
set Wheel_Base=(
|
|
select column8 from staging2.stg_process_table_dbstd_test b
|
|
where trim(b.column3)='Wheel Base' and block_row_number=15 and b.block_tag='DBSTD_test_transmission')
|
|
where dummy_F='dummy';
|
|
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_transmission_drawbar_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_transmission_drawbar_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_transmission_drawbar_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_transmission_drawbar_block set make=__make;
|
|
update staging2.DBSTD_test_transmission_drawbar_block set model=__model;
|
|
|
|
|
|
update staging2.dbstd_test_transmission_drawbar_block a
|
|
set test_condition=b.c3
|
|
from staging2.dbstd_test_test_condition_block b
|
|
where a.file_syspk=b.file_syspk;
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_transmission_drawbar_block');
|
|
|
|
/*transmission_speed_chart_desc_block*/
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_transmission_speed_chart_desc_block',__file_mnemonic,__file_sheet_mnemonic,18);
|
|
|
|
insert into staging2.DBSTD_test_transmission_speed_chart_desc_block
|
|
(
|
|
descr)
|
|
select column10
|
|
from
|
|
staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_transmission'
|
|
and block_row_number=1;
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_transmission_speed_chart_desc_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_transmission_speed_chart_desc_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_transmission_speed_chart_desc_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_transmission_speed_chart_desc_block set make=__make;
|
|
update staging2.DBSTD_test_transmission_speed_chart_desc_block set model=__model;
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_transmission_speed_chart_desc_block');
|
|
|
|
/*transmission_speed_chart_desc_block*/
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_transmission_speed_chart_block',__file_mnemonic,__file_sheet_mnemonic,19);
|
|
|
|
insert into staging2.DBSTD_test_transmission_speed_chart_block
|
|
(gear,forward,reverse)
|
|
select column10,column11, column13 from
|
|
staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_transmission'
|
|
and block_row_number>=3;
|
|
|
|
update staging2.dbstd_test_transmission_speed_chart_block a
|
|
set reverse= b.first_value from (SELECT
|
|
syspk, reverse, value_partition, first_value(reverse) over (partition by value_partition order by syspk)
|
|
FROM (
|
|
SELECT
|
|
syspk,
|
|
reverse,
|
|
sum(case when reverse is null then 0 else 1 end) over (order by syspk) as value_partition
|
|
FROM staging2.dbstd_test_transmission_speed_chart_block
|
|
ORDER BY syspk ASC
|
|
) as q) b where a.syspk = b.syspk;
|
|
|
|
|
|
execute 'update staging2.dbstd_test_transmission_speed_chart_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.dbstd_test_transmission_speed_chart_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.dbstd_test_transmission_speed_chart_block set function_id='||p_function_id||'';
|
|
update staging2.dbstd_test_transmission_speed_chart_block set make=__make;
|
|
update staging2.dbstd_test_transmission_speed_chart_block set model=__model;
|
|
delete from staging2.dbstd_test_transmission_speed_chart_block where gear is null;
|
|
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_transmission_speed_chart_block');
|
|
|
|
/*transmission_wheels_in*/
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_transmission_wheels_block',__file_mnemonic,__file_sheet_mnemonic,20);
|
|
|
|
|
|
insert into staging2.DBSTD_test_transmission_wheels_int(Descriptions,Steered_wheels,Driving_wheel)
|
|
select column15,column20,column23 from staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_transmission'
|
|
and block_row_number>=3 and block_row_number<=11 order by block_row_number;
|
|
|
|
update staging2.DBSTD_test_transmission_wheels_int
|
|
set Driving_wheel=Steered_wheels
|
|
where trim(Descriptions)='Location of driving wheel'
|
|
and Driving_wheel ='';
|
|
|
|
execute 'update staging2.DBSTD_test_transmission_wheels_int set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_transmission_wheels_int set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_transmission_wheels_int set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_transmission_wheels_int set make=__make;
|
|
update staging2.DBSTD_test_transmission_wheels_int set model=__model;
|
|
|
|
|
|
insert into staging2.dbstd_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.dbstd_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 dbstd_test_transmission_wheels_block where dummy_f is null;
|
|
|
|
|
|
execute 'update staging2.dbstd_test_transmission_wheels_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.dbstd_test_transmission_wheels_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.dbstd_test_transmission_wheels_block set function_id='||p_function_id||'';
|
|
update staging2.dbstd_test_transmission_wheels_block set make=__make;
|
|
update staging2.dbstd_test_transmission_wheels_block set model=__model;
|
|
|
|
|
|
insert into staging2.DBSTD_test_transmission_fuel_lubricant_int( Descriptions,Diesel_BS_IV,Engine_oil,Transmission_oil)
|
|
select column15,column18,column20,column23 from staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_transmission'
|
|
and block_row_number>=14 and block_row_number<=18 order by block_row_number;
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_transmission_fuel_lubricant_int set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_transmission_fuel_lubricant_int set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_transmission_fuel_lubricant_int set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_transmission_fuel_lubricant_int set make=__make;
|
|
update staging2.DBSTD_test_transmission_fuel_lubricant_int set model=__model;
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_transmission_wheels_block');
|
|
|
|
/*transmission_fuel_lubricant*/
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_transmission_fuel_lubricant_block',__file_mnemonic,__file_sheet_mnemonic,21);
|
|
|
|
|
|
insert into staging2.DBSTD_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.DBSTD_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 DBSTD_test_transmission_fuel_lubricant_block where dummy_f is null;
|
|
|
|
execute 'update staging2.DBSTD_test_transmission_fuel_lubricant_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_transmission_fuel_lubricant_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_transmission_fuel_lubricant_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_transmission_fuel_lubricant_block set make=__make;
|
|
update staging2.DBSTD_test_transmission_fuel_lubricant_block set model=__model;
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_transmission_fuel_lubricant_block');
|
|
|
|
/*DBSTD_test_remarks_block*/
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_remarks_block',__file_mnemonic,__file_sheet_mnemonic,22);
|
|
|
|
insert into staging2.DBSTD_test_remarks_block
|
|
(
|
|
remarks ,
|
|
parameter ,
|
|
acceptance_criteria,
|
|
observations
|
|
)
|
|
select column3,column13,column15,column23 from staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_remarks'
|
|
and block_row_number>3 and block_row_number<=5;
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_remarks_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_remarks_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_remarks_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_remarks_block set make=__make;
|
|
update staging2.DBSTD_test_remarks_block set model=__model;
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_remarks_block');
|
|
|
|
/*DBSTD_test_remarks_footer_block*/
|
|
perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
|
|
'DBSTD_test_remarks_footer_block',__file_mnemonic,__file_sheet_mnemonic,23);
|
|
|
|
insert into staging2.DBSTD_test_remarks_footer_block(dummy_f) values ('dummy');
|
|
|
|
update staging2.DBSTD_test_remarks_footer_block a
|
|
set prepared_by=( select column7 from staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_remarks'
|
|
and trim(column3)='Prepared by' )
|
|
where dummy_F='dummy';
|
|
|
|
update staging2.DBSTD_test_remarks_footer_block a
|
|
set reviewed_by=( select column7 from staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_remarks'
|
|
and trim(column3)='Reviewed by' )
|
|
where dummy_F='dummy';
|
|
|
|
update staging2.DBSTD_test_remarks_footer_block a
|
|
set approved_by=( select column7 from staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_remarks'
|
|
and trim(column3)='Approved by' )
|
|
where dummy_F='dummy';
|
|
|
|
update staging2.DBSTD_test_remarks_footer_block a
|
|
set comments=( select column3 from staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_remarks'
|
|
and column3 like 'THIS DOCUMENT%' )
|
|
where dummy_F='dummy';
|
|
|
|
update staging2.DBSTD_test_remarks_footer_block a
|
|
set rev1=( select column3 from staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_remarks'
|
|
and block_row_number=(select block_row_number + 1 from staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_remarks'
|
|
and column3 like 'THIS DOCUMENT%') )
|
|
where dummy_F='dummy';
|
|
|
|
|
|
update staging2.DBSTD_test_remarks_footer_block a
|
|
set rev2=( select column9 from staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_remarks'
|
|
and block_row_number=(select block_row_number + 1 from staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_remarks'
|
|
and column3 like 'THIS DOCUMENT%') )
|
|
where dummy_F='dummy';
|
|
|
|
update staging2.DBSTD_test_remarks_footer_block a
|
|
set rev3=( select column18 from staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_remarks'
|
|
and block_row_number=(select block_row_number + 1 from staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_remarks'
|
|
and column3 like 'THIS DOCUMENT%') )
|
|
where dummy_F='dummy';
|
|
|
|
update staging2.DBSTD_test_remarks_footer_block a
|
|
set replaces=( select column23 from staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_remarks'
|
|
and column23 like 'Replaces%')
|
|
where dummy_F='dummy';
|
|
|
|
update staging2.DBSTD_test_remarks_footer_block a
|
|
set revision_no=( select column23 from staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_remarks'
|
|
and column23 like 'Revision%')
|
|
where dummy_F='dummy';
|
|
|
|
update staging2.DBSTD_test_remarks_footer_block a
|
|
set date=( select column19 from staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_remarks'
|
|
and block_row_number=(select block_row_number+1 from staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_remarks'
|
|
and column23 like 'Revision%'))
|
|
where dummy_F='dummy';
|
|
|
|
update staging2.DBSTD_test_remarks_footer_block a
|
|
set date=( select column19 from staging2.stg_process_table_dbstd_test b
|
|
where b.block_tag='DBSTD_test_remarks'
|
|
and block_row_number=7 and column9 is not null)
|
|
where dummy_F='dummy';
|
|
|
|
|
|
execute 'update staging2.DBSTD_test_remarks_footer_block set file_syspk='||p_file_syspk||'';
|
|
execute 'update staging2.DBSTD_test_remarks_footer_block set client_id='||p_client_id||'';
|
|
execute 'update staging2.DBSTD_test_remarks_footer_block set function_id='||p_function_id||'';
|
|
update staging2.DBSTD_test_remarks_footer_block set make=__make;
|
|
update staging2.DBSTD_test_remarks_footer_block set model=__model;
|
|
|
|
perform fw_core.fn_jobctl_block_end(__file_syspk,'DBSTD_test_remarks_footer_block');
|
|
|
|
execute 'update dbstd_test_transmission_fuel_lubricant_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_h1_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_engine_rpm_rpm_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_engine_rpm_engine_to_pto_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_engine_rpm_required_pull_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_engine_rpm_weight_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_engine_rpm_tyre_details_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_test_condition_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_drawbar_performance_selected_summary_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_drawbar_performance_gear_performance_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_test_equipment_used_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_measurement_uncertainty_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_tractor_specifications_engine_details_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_tractor_specifications_cylinders_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_tractor_specifications_fuel_and_injection_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_tractor_specifications_cleaner_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_transmission_clutch_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_transmission_drawbar_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_transmission_speed_chart_desc_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_transmission_speed_chart_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_transmission_wheels_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_transmission_fuel_lubricant_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_remarks_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
execute 'update dbstd_test_remarks_footer_block set file_mnemonic='''||p_file_mnemonic||''' , file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
|
|
err_context := '';
|
|
perform fw_core.fn_insert_db_error ( __client_id, __function_id, 1001, 'Compegence', __file_syspk ,__file_mnemonic,__file_sheet_mnemonic ,null,'stg2', 'fn_DBSTD_test_block', err_state, err_msg, err_detail, err_hint, err_context,'success');return err_context;
|
|
|
|
EXCEPTION when OTHERS then
|
|
|
|
GET STACKED DIAGNOSTICS
|
|
err_state = returned_sqlstate,
|
|
err_msg = message_text,
|
|
err_detail = pg_exception_detail,
|
|
err_hint = pg_exception_hint,
|
|
err_context = pg_exception_context;
|
|
|
|
perform fw_core.fn_insert_db_error ( __client_id, __function_id, 1001, 'Compegence', __file_syspk ,__file_mnemonic,__file_sheet_mnemonic ,err_block,'stg2', 'fn_DBSTD_test_block', err_state, err_msg, err_detail, err_hint, err_context,'error');
|
|
return err_context;
|
|
end
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|