1343 lines
60 KiB
Plaintext
1343 lines
60 KiB
Plaintext
drop function if exists mmt_staging2.fn_dbstd_test_block;
|
||
CREATE OR REPLACE FUNCTION mmt_staging2.fn_dbstd_test_block(p_client_id int,p_function_id int, p_file_format text,
|
||
p_sheet_mnemonic text, p_file_syspk int)
|
||
RETURNS void AS $$
|
||
declare __make text;
|
||
declare __model text;
|
||
|
||
declare __client_id int :=p_client_id;
|
||
declare __function_id int :=p_function_id;
|
||
declare __file_format text :=p_file_format;
|
||
declare __sheet_mnemonic text :=p_sheet_mnemonic;
|
||
declare __file_syspk int :=p_file_syspk;
|
||
|
||
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 mmt_staging2.fn_dbstd_test_block(p_client_id,p_function_id, p_file_format,
|
||
p_sheet_mnemonic, p_file_syspk)
|
||
Function call ex: select mmt_staging2.fn_dbstd_test_block(1,2,'DBSTD','DBSTD_TEST',526)
|
||
***************************************************************/
|
||
|
||
|
||
SET search_path TO mmt_staging2;
|
||
|
||
/* to process multiple files - re-runnability*/
|
||
|
||
truncate table mmt_staging2.dbstd_test_transmission_wheels_int;
|
||
truncate table mmt_staging2.dbstd_test_h1_int;
|
||
truncate table mmt_staging2.dbstd_test_engine_rpm_engine_to_pto_block;
|
||
truncate table mmt_staging2.dbstd_test_engine_rpm_required_pull_block;
|
||
truncate table mmt_staging2.dbstd_test_engine_rpm_weight_block;
|
||
truncate table mmt_staging2.dbstd_test_engine_rpm_tyre_details_block;
|
||
truncate table mmt_staging2.DBSTD_test_engine_RPM_tyre_details_int;
|
||
truncate table mmt_staging2.dbstd_test_test_condition_block;
|
||
truncate table mmt_staging2.dbstd_test_drawbar_performance_selected_summary_block;
|
||
truncate table mmt_staging2.dbstd_test_drawbar_performance_gear_performance_block;
|
||
truncate table mmt_staging2.dbstd_test_test_equipment_used_block;
|
||
truncate table mmt_staging2.dbstd_test_measurement_uncertainty_block;
|
||
truncate table mmt_staging2.dbstd_test_tractor_specifications_engine_details_block;
|
||
truncate table mmt_staging2.dbstd_test_tractor_specifications_cylinders_block;
|
||
truncate table mmt_staging2.dbstd_test_tractor_specifications_fuel_and_injection_block;
|
||
truncate table mmt_staging2.dbstd_test_tractor_specifications_cleaner_block;
|
||
truncate table mmt_staging2.dbstd_test_transmission_clutch_block;
|
||
truncate table mmt_staging2.dbstd_test_transmission_drawbar_block;
|
||
truncate table mmt_staging2.dbstd_test_transmission_speed_chart_desc_block;
|
||
truncate table mmt_staging2.dbstd_test_transmission_wheels_block;
|
||
truncate table mmt_staging2.dbstd_test_transmission_fuel_lubricant_int;
|
||
truncate table mmt_staging2.dbstd_test_remarks_block;
|
||
truncate table mmt_staging2.dbstd_test_remarks_footer_block;
|
||
truncate table mmt_staging2.dbstd_test_h1_block;
|
||
truncate table mmt_staging2.dbstd_test_transmission_fuel_lubricant_block;
|
||
truncate table mmt_staging2.dbstd_test_engine_rpm_rpm_block;
|
||
truncate table mmt_staging2.dbstd_test_transmission_speed_chart_block;
|
||
truncate table mmt_staging2.stg_specific_table_dbstd_test;
|
||
truncate table mmt_staging2.stg_process_table_dbstd_test;
|
||
|
||
|
||
execute 'delete from mmt_ods.fw_jobctrl_file_sheet_block_run_schedule where file_syspk='||p_file_syspk||' and sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
|
||
|
||
execute 'update mmt_ods.mmt_config a
|
||
set row_number_start=null,
|
||
row_previous_number=null,
|
||
row_read_end=null,
|
||
run_time=null
|
||
where a.file_format='''||p_file_format||''' and a.sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
|
||
/*run specific data */
|
||
execute 'insert into mmt_staging2.stg_specific_table_dbstd_test
|
||
select * from mmt_staging1.mmt_staging_generic_table a
|
||
where a.file_format='''||p_file_format||''' and a.sheet_mnemonic='''||p_sheet_mnemonic||'''
|
||
and a.file_syspk='||p_file_syspk||'';
|
||
|
||
|
||
/* standardizing keywords */
|
||
update mmt_staging2.stg_specific_table_dbstd_test set column3='Remarks' where column3 like 'Remarks%';
|
||
update mmt_staging2.stg_specific_table_dbstd_test set column15='Objective' where column15 like 'Objective%';
|
||
|
||
update mmt_staging2.stg_specific_table_dbstd_test set column3 = TRIM (TRAILING FROM column3 );
|
||
update mmt_staging2.stg_specific_table_dbstd_test set column3 = TRIM (LEADING FROM column3 );
|
||
|
||
/* To process repeated keywords */
|
||
update mmt_ods.mmt_config set F1_modified = TRIM (TRAILING FROM F1_modified) ;
|
||
update mmt_ods.mmt_config set F1_modified = TRIM (LEADING FROM F1_modified) ;
|
||
update mmt_ods.mmt_config set F1_source=F1_modified ;
|
||
update mmt_ods.mmt_config set F1_source = TRIM (TRAILING FROM F1_source);
|
||
update mmt_ods.mmt_config set F1_source = TRIM (LEADING FROM F1_source);
|
||
|
||
/*updating row numbers in mmt_config table*/
|
||
execute 'update mmt_ods.mmt_config a
|
||
set row_number_start=(select min(b.row_number)
|
||
from mmt_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_format='''||p_file_format||''' and a.sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
|
||
|
||
execute 'update mmt_staging2.stg_specific_table_dbstd_test a
|
||
set is_rownumber_fetched=1
|
||
from mmt_ods.mmt_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_format='''||p_file_format||''' and a.sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
|
||
execute 'update mmt_ods.mmt_config a set row_previous_number=row_number_start-1
|
||
where a.file_format='''||p_file_format||''' and a.sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
|
||
execute 'update mmt_ods.mmt_config a
|
||
set row_read_end= (select b.row_number_start
|
||
from mmt_ods.mmt_config b
|
||
where b.syspk=a.syspk+1 and file_format='''||p_file_format||''' and sheet_mnemonic='''||p_sheet_mnemonic||''' )
|
||
where a.file_format='''||p_file_format||''' and a.sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
|
||
/* update null for the last keyword as read end beacause of overlap of multiple formats*/
|
||
execute 'update mmt_ods.mmt_config a
|
||
set row_read_end = null ,run_time=current_timestamp
|
||
where f1_modified =''Remarks''
|
||
and a.file_format='''||p_file_format||''' and a.sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
|
||
/* tagging ranks*/
|
||
|
||
execute 'insert into mmt_staging2.stg_process_table_dbstd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as rank,b.rank_tag as rank_tag
|
||
from mmt_staging2.stg_specific_table_dbstd_test a
|
||
join mmt_ods.mmt_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_format='''||p_file_format||''' and b.sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
|
||
execute 'insert into mmt_staging2.stg_process_table_dbstd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as rank,b.rank_tag as rank_tag from mmt_staging2.stg_specific_table_dbstd_test a
|
||
join mmt_ods.mmt_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_format='''||p_file_format||''' and b.sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
|
||
execute 'insert into mmt_staging2.stg_process_table_dbstd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as rank,b.rank_tag as rank_tag from mmt_staging2.stg_specific_table_dbstd_test a
|
||
join mmt_ods.mmt_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_format='''||p_file_format||''' and b.sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
|
||
execute 'insert into mmt_staging2.stg_process_table_dbstd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as rank,b.rank_tag as rank_tag from mmt_staging2.stg_specific_table_dbstd_test a
|
||
join mmt_ods.mmt_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_format='''||p_file_format||''' and b.sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
|
||
execute 'insert into mmt_staging2.stg_process_table_dbstd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as rank,b.rank_tag as rank_tag
|
||
from mmt_staging2.stg_specific_table_dbstd_test a
|
||
join mmt_ods.mmt_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_format='''||p_file_format||''' and b.sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
|
||
|
||
execute 'insert into mmt_staging2.stg_process_table_dbstd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as rank,b.rank_tag as rank_tag
|
||
from mmt_staging2.stg_specific_table_dbstd_test a
|
||
join mmt_ods.mmt_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_format='''||p_file_format||''' and b.sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
|
||
|
||
execute 'insert into mmt_staging2.stg_process_table_dbstd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as rank,b.rank_tag as rank_tag
|
||
from mmt_staging2.stg_specific_table_dbstd_test a
|
||
join mmt_ods.mmt_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_format='''||p_file_format||''' and b.sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
|
||
execute 'insert into mmt_staging2.stg_process_table_dbstd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as rank,b.rank_tag as rank_tag
|
||
from mmt_staging2.stg_specific_table_dbstd_test a
|
||
join mmt_ods.mmt_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_format='''||p_file_format||''' and b.sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
|
||
|
||
execute 'insert into mmt_staging2.stg_process_table_dbstd_test
|
||
select a.* , RANK () OVER ( ORDER BY row_number) as rank,b.rank_tag as rank_tag
|
||
from mmt_staging2.stg_specific_table_dbstd_test a
|
||
join mmt_ods.mmt_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_format='''||p_file_format||''' and b.sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
|
||
|
||
/*selecting tactor make*/
|
||
select column6 into __make from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications' and trim(column4)='Make' ;
|
||
|
||
select column6 into __model from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications' and trim(column4)='Model';
|
||
|
||
|
||
/*block starts - DBSTD_TEST_H1_BLOCK */
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_TEST_H1_BLOCK',__file_format,__sheet_mnemonic,1);
|
||
|
||
insert into mmt_staging2.DBSTD_TEST_H1_INT (c1,c2)
|
||
select a.column3,column6 from mmt_staging2.stg_process_table_dbstd_test a where rank_tag='DBSTD_Test_H1';
|
||
|
||
insert into mmt_staging2.DBSTD_TEST_H1_INT (c1,c2)
|
||
select a.column9,column12 from mmt_staging2.stg_process_table_dbstd_test a where rank_tag='DBSTD_Test_H1';
|
||
|
||
insert into mmt_staging2.DBSTD_TEST_H1_INT (c1,c2)
|
||
select 'Objective',column15
|
||
from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_Test_H1'
|
||
and row_number=
|
||
(
|
||
select row_number+1 from
|
||
mmt_staging2.stg_process_table_dbstd_test a
|
||
where column15='Objective'
|
||
and rank_tag='DBSTD_Test_H1'
|
||
);
|
||
|
||
insert into mmt_staging2.DBSTD_TEST_H1_INT (c1,c2)
|
||
select 'Acceptance criteria',column15
|
||
from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_Test_H1'
|
||
and row_number=
|
||
(
|
||
select row_number+1 from
|
||
mmt_staging2.stg_process_table_dbstd_test a
|
||
where column15='Acceptance criteria'
|
||
and rank_tag='DBSTD_Test_H1'
|
||
);
|
||
|
||
insert into mmt_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 mmt_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 mmt_staging2.DBSTD_TEST_H1_Block where dummy_f is null ;
|
||
execute 'update mmt_staging2.DBSTD_TEST_H1_Block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_TEST_H1_Block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_TEST_H1_Block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_TEST_H1_Block set make=__make;
|
||
update mmt_staging2.DBSTD_TEST_H1_Block set model=__model;
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_TEST_H1_BLOCK');
|
||
|
||
/* block DBSTD_test_engine_RPM_RPM_block */
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_engine_RPM_RPM_block',__file_format,__sheet_mnemonic,2);
|
||
|
||
insert into mmt_staging2.DBSTD_test_engine_RPM_RPM_block(dummy_f) values ('dummy');
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_RPM_block
|
||
set low_idle_declared=(select column6 from
|
||
mmt_staging2.stg_process_table_dbstd_test where rank=2 and column3='Low Idle RPM' and rank_tag='DBSTD_test_engine_RPM')
|
||
where dummy_f='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_RPM_block
|
||
set low_idle_observed=(select column8 from
|
||
mmt_staging2.stg_process_table_dbstd_test where rank=2 and column3='Low Idle RPM' and rank_tag='DBSTD_test_engine_RPM')
|
||
where dummy_f='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_RPM_block
|
||
set high_idle_declared=(select column6 from
|
||
mmt_staging2.stg_process_table_dbstd_test where rank=3 and column3='High Idle RPM' and rank_tag='DBSTD_test_engine_RPM')
|
||
where dummy_f='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_RPM_block
|
||
set high_idle_observed=(select column8 from
|
||
mmt_staging2.stg_process_table_dbstd_test where rank=3 and column3='High Idle RPM' and rank_tag='DBSTD_test_engine_RPM')
|
||
where dummy_f='dummy';
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_engine_RPM_RPM_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_engine_RPM_RPM_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_engine_RPM_RPM_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_engine_RPM_RPM_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_engine_RPM_RPM_block set model=__model;
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_engine_RPM_RPM_block');
|
||
|
||
/* block DBSTD_test_engine_RPM_engine_to_PTO_block*/
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'dbstd_test_engine_rpm_engine_to_pto_block',__file_format,__sheet_mnemonic,3);
|
||
insert into mmt_staging2.DBSTD_test_engine_RPM_engine_to_PTO_block(dummy_f) values ('dummy');
|
||
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set rated_speed=
|
||
(select column6 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.rank_tag='DBSTD_test_engine_RPM'
|
||
and rank=4 )
|
||
where dummy_f='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set engine_to_PTO_ratio=
|
||
(select column6 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.rank_tag='DBSTD_test_engine_RPM'
|
||
and rank=5 )
|
||
where dummy_f='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set wheel_base_mm=
|
||
(select column6 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.rank_tag='DBSTD_test_engine_RPM'
|
||
and rank=6 )
|
||
where dummy_f='dummy';
|
||
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set engine_power_hp=
|
||
(select column6 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.file_format='DBSTD' and a.sheet_mnemonic='DBSTD_TEST' and a.rank_tag='DBSTD_test_engine_RPM'
|
||
and rank=7 )
|
||
where dummy_f='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set PTO_Power_hp=
|
||
(select column6 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.file_format='DBSTD' and a.sheet_mnemonic='DBSTD_TEST' and a.rank_tag='DBSTD_test_engine_RPM'
|
||
and rank=8 )
|
||
where dummy_f='dummy';
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_engine_RPM_engine_to_PTO_block set model=__model;
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'dbstd_test_engine_rpm_engine_to_pto_block');
|
||
|
||
/* block DBSTD_test_engine_RPM_required_pull_block */
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_engine_RPM_required_pull_block',__file_format,__sheet_mnemonic,4);
|
||
|
||
insert into mmt_staging2.DBSTD_test_engine_RPM_required_pull_block ( pull_type) values ('UB'), ('B');
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_required_pull_block
|
||
set required_pull_in_kg=(select column7 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.rank_tag='DBSTD_test_engine_RPM'
|
||
and column3='Required pull in (kg)')
|
||
where pull_type='UB';
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_required_pull_block
|
||
set required_pull_in_kg=(select column9 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.rank_tag='DBSTD_test_engine_RPM'
|
||
and column3='Required pull in (kg)')
|
||
where pull_type='B';
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_required_pull_block
|
||
set required_power_in_hp=(select column7 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.rank_tag='DBSTD_test_engine_RPM'
|
||
and column3='Required Power in hp')
|
||
where pull_type='UB';
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_required_pull_block
|
||
set required_power_in_hp=(select column9 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.rank_tag='DBSTD_test_engine_RPM'
|
||
and column3='Required Power in hp')
|
||
where pull_type='B';
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_required_pull_block
|
||
set actual_pull_in_kg=(select column7 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.rank_tag='DBSTD_test_engine_RPM'
|
||
and column3='Actual pull in (kg)')
|
||
where pull_type='UB';
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_required_pull_block
|
||
set actual_pull_in_kg=(select column9 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.rank_tag='DBSTD_test_engine_RPM'
|
||
and column3='Actual pull in (kg)')
|
||
where pull_type='B';
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_required_pull_block
|
||
set actual_power_in_hp=(select column7 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.rank_tag='DBSTD_test_engine_RPM'
|
||
and column3='actual power in hp')
|
||
where pull_type='UB';
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_required_pull_block
|
||
set actual_power_in_hp=(select column9 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.rank_tag='DBSTD_test_engine_RPM'
|
||
and column3='actual power in hp')
|
||
where pull_type='B';
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_required_pull_block
|
||
set calculated_hitch_height_mm=(select column7 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.rank_tag='DBSTD_test_engine_RPM'
|
||
and column3='Calculated hitch height (mm)')
|
||
where pull_type='UB';
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_required_pull_block
|
||
set calculated_hitch_height_mm=(select column9 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.rank_tag='DBSTD_test_engine_RPM'
|
||
and column3='Calculated hitch height (mm)')
|
||
where pull_type='B';
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_required_pull_block
|
||
set actual_hitch_height_mm=(select column7 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.rank_tag='DBSTD_test_engine_RPM'
|
||
and column3='Actual hitch height (mm)')
|
||
where pull_type='UB';
|
||
|
||
update mmt_staging2.DBSTD_test_engine_RPM_required_pull_block
|
||
set actual_hitch_height_mm=(select column9 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.rank_tag='DBSTD_test_engine_RPM'
|
||
and column3='Actual hitch height (mm)')
|
||
where pull_type='B';
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_engine_RPM_required_pull_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_engine_RPM_required_pull_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_engine_RPM_required_pull_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_engine_RPM_required_pull_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_engine_RPM_required_pull_block set model=__model;
|
||
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_engine_RPM_required_pull_block');
|
||
|
||
/* block DBSTD_test_engine_RPM_weight_block */
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_engine_RPM_weight_block',__file_format,__sheet_mnemonic,5);
|
||
|
||
insert into mmt_staging2.DBSTD_test_engine_RPM_weight_block(
|
||
weight_kg ,
|
||
Front ,
|
||
rear ,
|
||
Total
|
||
)
|
||
select column10,column13,column17,column22 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.rank_tag='DBSTD_test_engine_RPM'
|
||
and rank in(2,3);
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_engine_RPM_weight_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_engine_RPM_weight_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_engine_RPM_weight_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_engine_RPM_weight_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_engine_RPM_weight_block set model=__model;
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_engine_RPM_weight_block');
|
||
|
||
/*block - rpm tyre details block*/
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_engine_RPM_tyre_details_block',__file_format,__sheet_mnemonic,6);
|
||
|
||
insert into mmt_staging2.DBSTD_test_engine_RPM_tyre_details_int
|
||
( s_no,tyre_details,front,rear)
|
||
|
||
select column10,column11,column15,column20
|
||
from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.rank_tag='DBSTD_test_engine_RPM'
|
||
and rank >=5 and rank<=14;
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_engine_RPM_tyre_details_int set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_engine_RPM_tyre_details_int set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_engine_RPM_tyre_details_int set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_engine_RPM_tyre_details_int set make=__make;
|
||
update mmt_staging2.DBSTD_test_engine_RPM_tyre_details_int set model=__model;
|
||
|
||
insert into mmt_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 mmt_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 mmt_staging2.DBSTD_test_engine_RPM_tyre_details_block where dummy_f is null;
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_engine_RPM_tyre_details_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_engine_RPM_tyre_details_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_engine_RPM_tyre_details_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_engine_RPM_tyre_details_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_engine_RPM_tyre_details_block set model=__model;
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_engine_RPM_tyre_details_block');
|
||
|
||
/* block DBSTD_test_test_condition_block */
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_test_condition_block',__file_format,__sheet_mnemonic,7);
|
||
|
||
insert into mmt_staging2.DBSTD_test_test_condition_block(c1,c2,c3)
|
||
select column3 c1,column10 c2,column15 c3
|
||
from mmt_staging2.stg_process_table_dbstd_test a
|
||
where a.rank_tag='DBSTD_test_test_condition';
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_test_condition_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_test_condition_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_test_condition_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_test_condition_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_test_condition_block set model=__model;
|
||
|
||
update mmt_staging2.dbstd_test_test_condition_block
|
||
set c3='B'
|
||
where c2='Ballasted';
|
||
|
||
update mmt_staging2.dbstd_test_test_condition_block
|
||
set c3='UB'
|
||
where c2='Un-ballasted';
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_test_condition_block');
|
||
|
||
/* block DBSTD_test_drawbar_performance_selected_summary_block */
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_drawbar_performance_selected_summary_block',__file_format,__sheet_mnemonic,8);
|
||
|
||
insert into mmt_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 mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_drawbar_performance_selected_summary'
|
||
and rank>3;
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_drawbar_performance_selected_summary_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_drawbar_performance_selected_summary_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_drawbar_performance_selected_summary_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_drawbar_performance_selected_summary_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_drawbar_performance_selected_summary_block set model=__model;
|
||
|
||
perform mmt_staging2.fn_jobctrl_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 mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_drawbar_performance_gear_performance_block',__file_format,__sheet_mnemonic,9);
|
||
|
||
insert into mmt_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 mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_drawbar_performance_gear_performance'
|
||
and rank>3;
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_drawbar_performance_gear_performance_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_drawbar_performance_gear_performance_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_drawbar_performance_gear_performance_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_drawbar_performance_gear_performance_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_drawbar_performance_gear_performance_block set model=__model;
|
||
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_drawbar_performance_gear_performance_block');
|
||
|
||
/* block DBSTD_test_test_equipment_used_block */
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_test_equipment_used_block',__file_format,__sheet_mnemonic,10);
|
||
|
||
insert into mmt_staging2.DBSTD_test_test_equipment_used_block( s_no,instruments,instruments_no,cali_due_date)
|
||
select column3,column4,column8,column12
|
||
from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_test_equipment'
|
||
and rank>2;
|
||
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_test_equipment_used_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_test_equipment_used_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_test_equipment_used_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_test_equipment_used_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_test_equipment_used_block set model=__model;
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_test_equipment_used_block');
|
||
|
||
/* block DBSTD_test_measurement_uncertainty_block */
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_measurement_uncertainty_block',__file_format,__sheet_mnemonic,11);
|
||
|
||
insert into mmt_staging2.DBSTD_test_measurement_uncertainty_block(dummy_f) values ('dummy');
|
||
|
||
update mmt_staging2.DBSTD_test_measurement_uncertainty_block set load_cell=
|
||
(select column20 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_test_equipment' and trim(column15)='Load cell');
|
||
|
||
update mmt_staging2.DBSTD_test_measurement_uncertainty_block set rpm_meter=
|
||
(select column20 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_test_equipment' and trim(column15)='RPM Meter');
|
||
|
||
update mmt_staging2.DBSTD_test_measurement_uncertainty_block set speed=
|
||
(select column20 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_test_equipment' and trim(column15)='Speed');
|
||
|
||
|
||
update mmt_staging2.DBSTD_test_measurement_uncertainty_block set fuel_flow_meter=
|
||
(select column20 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_test_equipment' and trim(column15)='Fuel Flow meter');
|
||
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_measurement_uncertainty_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_measurement_uncertainty_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_measurement_uncertainty_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_measurement_uncertainty_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_measurement_uncertainty_block set model=__model;
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_measurement_uncertainty_block');
|
||
|
||
/* block DBSTD_test_tractor_specifications_engine_details_block */
|
||
|
||
/*tractor_specifications_engine_details_block*/
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_tractor_specifications_engine_details_block',__file_format,__sheet_mnemonic,12);
|
||
|
||
insert into mmt_staging2.DBSTD_test_tractor_specifications_engine_details_block(dummy_f)
|
||
values ('dummy');
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_engine_details_block
|
||
set make_in_block=(select column6 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column4)='Make'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_engine_details_block
|
||
set type=(select column6 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column4)='Type'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_engine_details_block
|
||
set model_in_block=(select column6 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column4)='Model'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_engine_details_block
|
||
set serial_no=(select column6 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column4)='Serial No'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_engine_details_block
|
||
set E_P_ratio=(select column6 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column4)='E P Ratio'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_engine_details_block
|
||
set rated_speed=(select column6 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column4)='Rated Speed'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_engine_details_block
|
||
set high_idle=(select column6 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column4)='High Idle'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_engine_details_block
|
||
set high_idle=(select column6 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column4)='High Idle'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_engine_details_block
|
||
set low_idle=(select column8 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column4)='High Idle'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_tractor_specifications_engine_details_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_tractor_specifications_engine_details_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_tractor_specifications_engine_details_block set function_id='||p_function_id||'';
|
||
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_tractor_specifications_engine_details_block');
|
||
|
||
/* block DBSTD_test_tractor_specifications_cylinders_block */
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_tractor_specifications_cylinders_block',__file_format,__sheet_mnemonic,13);
|
||
|
||
insert into mmt_staging2.DBSTD_test_tractor_specifications_cylinders_block(dummy_f)
|
||
values ('dummy');
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_cylinders_block
|
||
set number=(select column11 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column9)='Number'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_cylinders_block
|
||
set stroke=(select column11 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column9)='Stroke'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_cylinders_block
|
||
set bore=(select column11 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column9)='Bore'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_cylinders_block
|
||
set capacity=(select column11 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column9)='Capacity'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_tractor_specifications_cylinders_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_tractor_specifications_cylinders_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_tractor_specifications_cylinders_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_cylinders_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_cylinders_block set model=__model;
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_tractor_specifications_cylinders_block');
|
||
|
||
/*block fuel_and_injection_block*/
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_tractor_specifications_fuel_and_injection_block',__file_format,__sheet_mnemonic,14);
|
||
|
||
insert into mmt_staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block(dummy_f)
|
||
values ('dummy');
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block
|
||
set Make_type_and_model_of_injection_pump=(select column17 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column12)='Make, type and model of injection pump'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block
|
||
set capacity_of_fuel_tank_lit=(select column17 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column12)='Capacity of fuel tank lit'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block
|
||
set Make_type_and_model_of_injection_pump=(select column17 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column12)='Make, type and model of injection pump'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block
|
||
set Manufacturer_production_setting=(select column17 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column12)='Manufacturer’s production setting'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block
|
||
set Make_type_and_model_of_injectors=(select column17 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column12)='Make, type and model of injectors'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block
|
||
set Make_type_and_model_of_magneto_coil_and_distributor=(select column17 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column12)='Make, type and model of magneto, coil and distributor'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block
|
||
set Make_type_and_model_of_carburetor=(select column17 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column12)='Make, type and model of carburetor'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block
|
||
set Ignition_or_injection_timing_Manual_or_automatic=(select column17 from mmt_staging2.stg_process_table_dbstd_test a
|
||
where rank_tag='DBSTD_test_tractor_specifications'
|
||
and trim(column12)='Ignition or injection timing (Manual or automatic)'
|
||
)
|
||
where dummy_f is not null;
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_fuel_and_injection_block set model=__model;
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_tractor_specifications_fuel_and_injection_block');
|
||
|
||
/*tractor_specifications_cleaner_block*/
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_tractor_specifications_cleaner_block',__file_format,__sheet_mnemonic,15);
|
||
|
||
insert into mmt_staging2.DBSTD_test_tractor_specifications_cleaner_block(cleaner_type)
|
||
values ('Air cleaner'),('Precleaner (if fitted)');
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_cleaner_block
|
||
set type=
|
||
(select column23 from mmt_staging2.stg_process_table_dbstd_test a where
|
||
rank_tag='DBSTD_test_tractor_specifications'
|
||
and column21='Type' and rank=5)
|
||
where cleaner_type='Air cleaner';
|
||
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_cleaner_block
|
||
set type=
|
||
(select column23 from mmt_staging2.stg_process_table_dbstd_test a where
|
||
rank_tag='DBSTD_test_tractor_specifications'
|
||
and column21='Type' and rank=9)
|
||
where cleaner_type='Precleaner (if fitted)';
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_tractor_specifications_cleaner_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_tractor_specifications_cleaner_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_tractor_specifications_cleaner_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_cleaner_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_tractor_specifications_cleaner_block set model=__model;
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_tractor_specifications_cleaner_block');
|
||
|
||
|
||
/*transmission_clutch_block*/
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_transmission_clutch_block',__file_format,__sheet_mnemonic,16);
|
||
|
||
insert into mmt_staging2.DBSTD_test_transmission_clutch_block(dummy_f) values ('dummy');
|
||
|
||
|
||
update mmt_staging2.DBSTD_test_transmission_clutch_block a
|
||
set clutch_type=(
|
||
select column6 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.column3='Type' and rank=2 and b.rank_tag='DBSTD_test_transmission')
|
||
where dummy_F='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_transmission_clutch_block a
|
||
set steering_type=(
|
||
select column6 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.column3='Type' and rank=5 and b.rank_tag='DBSTD_test_transmission')
|
||
where dummy_F='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_transmission_clutch_block a
|
||
set diameter_of_disc_mm=(
|
||
select column6 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where trim(b.column3)='Diameter of disc, mm' and b.rank_tag='DBSTD_test_transmission')
|
||
where dummy_F='dummy';
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_transmission_clutch_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_transmission_clutch_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_transmission_clutch_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_transmission_clutch_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_transmission_clutch_block set model=__model;
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_transmission_clutch_block');
|
||
|
||
/*transmission_drawbar_block*/
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_transmission_drawbar_block',__file_format,__sheet_mnemonic,17);
|
||
|
||
insert into mmt_staging2.DBSTD_test_transmission_drawbar_block(dummy_f) values ('dummy');
|
||
|
||
update mmt_staging2.DBSTD_test_transmission_drawbar_block a
|
||
set type=(
|
||
select column8 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where trim(b.column3)='Type' and rank=7 and b.rank_tag='DBSTD_test_transmission')
|
||
where dummy_F='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_transmission_drawbar_block a
|
||
set Height_above_ground_max_mm=(
|
||
select column8 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where trim(b.column3)='Height above ground (max) mm' and rank=9 and b.rank_tag='DBSTD_test_transmission')
|
||
where dummy_F='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_transmission_drawbar_block a
|
||
set Height_above_ground_max_mm=(
|
||
select column8 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where trim(b.column3)='Height above ground (max) mm' and rank=9 and b.rank_tag='DBSTD_test_transmission')
|
||
where dummy_F='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_transmission_drawbar_block a
|
||
set Height_above_ground_min_mm=(
|
||
select column8 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where trim(b.column3)='Height above ground (min) mm' and rank=11 and b.rank_tag='DBSTD_test_transmission')
|
||
where dummy_F='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_transmission_drawbar_block a
|
||
set Position_related_to_PTO=(
|
||
select column8 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where trim(b.column3)='Position related to PTO' and rank=13 and b.rank_tag='DBSTD_test_transmission')
|
||
where dummy_F='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_transmission_drawbar_block a
|
||
set Wheel_Base=(
|
||
select column8 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where trim(b.column3)='Wheel Base' and rank=15 and b.rank_tag='DBSTD_test_transmission')
|
||
where dummy_F='dummy';
|
||
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_transmission_drawbar_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_transmission_drawbar_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_transmission_drawbar_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_transmission_drawbar_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_transmission_drawbar_block set model=__model;
|
||
|
||
|
||
update mmt_staging2.dbstd_test_transmission_drawbar_block a
|
||
set test_condition=b.c3
|
||
from mmt_staging2.dbstd_test_test_condition_block b
|
||
where a.file_syspk=b.file_syspk;
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_transmission_drawbar_block');
|
||
|
||
/*transmission_speed_chart_desc_block*/
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_transmission_speed_chart_desc_block',__file_format,__sheet_mnemonic,18);
|
||
|
||
insert into mmt_staging2.DBSTD_test_transmission_speed_chart_desc_block
|
||
(
|
||
descr)
|
||
select column10
|
||
from
|
||
mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_transmission'
|
||
and rank=1;
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_transmission_speed_chart_desc_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_transmission_speed_chart_desc_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_transmission_speed_chart_desc_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_transmission_speed_chart_desc_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_transmission_speed_chart_desc_block set model=__model;
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_transmission_speed_chart_desc_block');
|
||
|
||
/*transmission_speed_chart_desc_block*/
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_transmission_speed_chart_block',__file_format,__sheet_mnemonic,19);
|
||
|
||
insert into mmt_staging2.DBSTD_test_transmission_speed_chart_block
|
||
(gear,forward,reverse)
|
||
select column10,column11, column13 from
|
||
mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_transmission'
|
||
and rank>=3;
|
||
|
||
update mmt_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 mmt_staging2.dbstd_test_transmission_speed_chart_block
|
||
ORDER BY syspk ASC
|
||
) as q) b where a.syspk = b.syspk;
|
||
|
||
|
||
execute 'update mmt_staging2.dbstd_test_transmission_speed_chart_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.dbstd_test_transmission_speed_chart_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.dbstd_test_transmission_speed_chart_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.dbstd_test_transmission_speed_chart_block set make=__make;
|
||
update mmt_staging2.dbstd_test_transmission_speed_chart_block set model=__model;
|
||
delete from mmt_staging2.dbstd_test_transmission_speed_chart_block where gear is null;
|
||
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_transmission_speed_chart_block');
|
||
|
||
/*transmission_wheels_in*/
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_transmission_wheels_block',__file_format,__sheet_mnemonic,20);
|
||
|
||
|
||
insert into mmt_staging2.DBSTD_test_transmission_wheels_int(Descriptions,Steered_wheels,Driving_wheel)
|
||
select column15,column20,column23 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_transmission'
|
||
and rank>=3 and rank<=11;
|
||
|
||
update mmt_staging2.DBSTD_test_transmission_wheels_int
|
||
set Driving_wheel=Steered_wheels
|
||
where trim(Descriptions)='Location of driving wheel'
|
||
and Driving_wheel ='';
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_transmission_wheels_int set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_transmission_wheels_int set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_transmission_wheels_int set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_transmission_wheels_int set make=__make;
|
||
update mmt_staging2.DBSTD_test_transmission_wheels_int set model=__model;
|
||
|
||
|
||
insert into mmt_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 mmt_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 mmt_staging2.dbstd_test_transmission_wheels_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.dbstd_test_transmission_wheels_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.dbstd_test_transmission_wheels_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.dbstd_test_transmission_wheels_block set make=__make;
|
||
update mmt_staging2.dbstd_test_transmission_wheels_block set model=__model;
|
||
|
||
|
||
insert into mmt_staging2.DBSTD_test_transmission_fuel_lubricant_int( Descriptions,Diesel_BS_IV,Engine_oil,Transmission_oil)
|
||
select column15,column18,column20,column23 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_transmission'
|
||
and rank>=14 and rank<=18;
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_transmission_fuel_lubricant_int set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_transmission_fuel_lubricant_int set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_transmission_fuel_lubricant_int set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_transmission_fuel_lubricant_int set make=__make;
|
||
update mmt_staging2.DBSTD_test_transmission_fuel_lubricant_int set model=__model;
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_transmission_wheels_block');
|
||
|
||
/*transmission_fuel_lubricant*/
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_transmission_fuel_lubricant_block',__file_format,__sheet_mnemonic,21);
|
||
|
||
|
||
insert into mmt_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 mmt_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 mmt_staging2.DBSTD_test_transmission_fuel_lubricant_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_transmission_fuel_lubricant_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_transmission_fuel_lubricant_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_transmission_fuel_lubricant_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_transmission_fuel_lubricant_block set model=__model;
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_transmission_fuel_lubricant_block');
|
||
|
||
/*DBSTD_test_remarks_block*/
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_remarks_block',__file_format,__sheet_mnemonic,22);
|
||
|
||
insert into mmt_staging2.DBSTD_test_remarks_block
|
||
(
|
||
remarks ,
|
||
parameter ,
|
||
acceptance_criteria,
|
||
observations
|
||
)
|
||
select column3,column13,column15,column23 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_remarks'
|
||
and rank>3 and rank<=5;
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_remarks_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_remarks_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_remarks_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_remarks_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_remarks_block set model=__model;
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_remarks_block');
|
||
|
||
/*DBSTD_test_remarks_footer_block*/
|
||
perform mmt_staging2.fn_jobctrl_block_begin(__client_id,__function_id,__file_syspk,
|
||
'DBSTD_test_remarks_footer_block',__file_format,__sheet_mnemonic,23);
|
||
|
||
insert into mmt_staging2.DBSTD_test_remarks_footer_block(dummy_f) values ('dummy');
|
||
|
||
update mmt_staging2.DBSTD_test_remarks_footer_block a
|
||
set prepared_by=( select column7 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_remarks'
|
||
and trim(column3)='Prepared by' )
|
||
where dummy_F='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_remarks_footer_block a
|
||
set reviewed_by=( select column7 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_remarks'
|
||
and trim(column3)='Reviewed by' )
|
||
where dummy_F='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_remarks_footer_block a
|
||
set approved_by=( select column7 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_remarks'
|
||
and trim(column3)='Approved by' )
|
||
where dummy_F='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_remarks_footer_block a
|
||
set comments=( select column3 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_remarks'
|
||
and column3 like 'THIS DOCUMENT%' )
|
||
where dummy_F='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_remarks_footer_block a
|
||
set rev1=( select column3 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_remarks'
|
||
and rank=(select rank + 1 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_remarks'
|
||
and column3 like 'THIS DOCUMENT%') )
|
||
where dummy_F='dummy';
|
||
|
||
|
||
update mmt_staging2.DBSTD_test_remarks_footer_block a
|
||
set rev2=( select column9 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_remarks'
|
||
and rank=(select rank + 1 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_remarks'
|
||
and column3 like 'THIS DOCUMENT%') )
|
||
where dummy_F='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_remarks_footer_block a
|
||
set rev3=( select column18 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_remarks'
|
||
and rank=(select rank + 1 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_remarks'
|
||
and column3 like 'THIS DOCUMENT%') )
|
||
where dummy_F='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_remarks_footer_block a
|
||
set replaces=( select column23 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_remarks'
|
||
and column23 like 'Replaces%')
|
||
where dummy_F='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_remarks_footer_block a
|
||
set revision_no=( select column23 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_remarks'
|
||
and column23 like 'Revision%')
|
||
where dummy_F='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_remarks_footer_block a
|
||
set date=( select column19 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_remarks'
|
||
and rank=(select rank+1 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_remarks'
|
||
and column23 like 'Revision%'))
|
||
where dummy_F='dummy';
|
||
|
||
update mmt_staging2.DBSTD_test_remarks_footer_block a
|
||
set date=( select column19 from mmt_staging2.stg_process_table_dbstd_test b
|
||
where b.rank_tag='DBSTD_test_remarks'
|
||
and rank=7 and column9 is not null)
|
||
where dummy_F='dummy';
|
||
|
||
|
||
execute 'update mmt_staging2.DBSTD_test_remarks_footer_block set file_syspk='||p_file_syspk||'';
|
||
execute 'update mmt_staging2.DBSTD_test_remarks_footer_block set client_id='||p_client_id||'';
|
||
execute 'update mmt_staging2.DBSTD_test_remarks_footer_block set function_id='||p_function_id||'';
|
||
update mmt_staging2.DBSTD_test_remarks_footer_block set make=__make;
|
||
update mmt_staging2.DBSTD_test_remarks_footer_block set model=__model;
|
||
|
||
perform mmt_staging2.fn_jobctrl_block_end(__file_syspk,'DBSTD_test_remarks_footer_block');
|
||
|
||
execute 'update dbstd_test_transmission_fuel_lubricant_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_h1_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_engine_rpm_rpm_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_engine_rpm_engine_to_pto_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_engine_rpm_required_pull_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_engine_rpm_weight_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_engine_rpm_tyre_details_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_test_condition_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_drawbar_performance_selected_summary_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_drawbar_performance_gear_performance_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_test_equipment_used_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_measurement_uncertainty_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_tractor_specifications_engine_details_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_tractor_specifications_cylinders_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_tractor_specifications_fuel_and_injection_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_tractor_specifications_cleaner_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_transmission_clutch_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_transmission_drawbar_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_transmission_speed_chart_desc_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_transmission_speed_chart_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_transmission_wheels_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_transmission_fuel_lubricant_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_remarks_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
execute 'update dbstd_test_remarks_footer_block set file_format='''||p_file_format||''' , sheet_mnemonic='''||p_sheet_mnemonic||'''';
|
||
|
||
|
||
end
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|