drop function if exists staging2.fn_IHTNST_NST_block;
CREATE OR REPLACE FUNCTION staging2.fn_IHTNST_NST_block(p_client_id int,p_function_id int, p_file_mnemonic text,p_file_sheet_mnemonic text, p_file_syspk int)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
   declare __make text;
   declare __model text;
   
   declare __client_id int :=p_client_id;
   declare __function_id int :=p_function_id;
   declare __file_mnemonic text  :=p_file_mnemonic;
   declare __file_sheet_mnemonic text :=p_file_sheet_mnemonic;
   declare __file_syspk int :=p_file_syspk;
   declare err_state   text;
   declare err_msg     text;
   declare err_detail  text;
   declare err_hint    text;
   declare err_context text;   
   declare err_query int;
   declare err_block text;    

  
 begin

/************************************************************ 
Function Name:fn_IHTNST_NST_block
Function Desc: This function populates data into staging 2 block
File Format: IHTNST
Sheet Format: IHTNST_NST
Creation Date: March 25 2021
Updation Date:
Author: compegence team
Function Call: select staging2.fn_IHTNST_NST_block(p_client_id,p_function_id, p_file_mnemonic,
p_file_sheet_mnemonic, p_file_syspk)
Function call ex: select staging2.fn_IHTNST_NST_block(1,2,'IHTNST','IHTNST_NST',456)
***************************************************************/


SET search_path TO  staging2;

/* to process multiple files - re-runnability*/

truncate table  staging2.IHTNST_NST_H1_Int;
truncate table  staging2.IHTNST_NST_H1_block;
truncate table  staging2.IHTNST_NST_Tyre_Details_Block;
truncate table  staging2.IHTNST_NST_Engine_RPM_Atmos_Int;
truncate table  staging2.IHTNST_NST_Engine_RPM_Block;
truncate table staging2.IHTNST_NST_Weight_Block;
truncate table  staging2.IHTNST_NST_Atmos_Cond_Block;
truncate table  staging2.IHTNST_NST_STAND_Noise_Block;
truncate table  staging2.IHTNST_NST_OEL_Noise_Block;
truncate table  staging2.IHTNST_NST_OEL_Noise_Load_Block;
truncate table  staging2.IHTNST_NST_Footer_Block;
truncate table staging2.stg_specific_table_IHTNST_NST;
truncate table staging2.stg_process_table_IHTNST_NST;


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||'''';

/* transfer data from generic to specific for IHTNST */
execute 'insert into  staging2.stg_specific_table_IHTNST_NST 
select * from staging1.staging_generic_table a
where   a.file_mnemonic='''||p_file_mnemonic||''' and a.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''
and a.file_syspk='||p_file_syspk||'';

select count(*) into err_query from staging2.stg_specific_table_IHTNST_NST;


if err_query=0 then
err_context := 'data not present';
	raise exception using 
	      message = 'No Data for IHT NST',
          detail = 'No data in table stg_specific_table_IHTNST_NST',  
          errcode = '42704', 
          hint = 'check sheet mnemonic in generic table, if it is null update it';
end if;	

/* trimming data */
update staging2.stg_specific_table_IHTNST_NST set column8='Objective' where lower(column8) like 'objective%';

update staging2.stg_specific_table_IHTNST_NST set column8='Condition' where lower(column8) like 'condition%';

update staging2.stg_specific_table_IHTNST_NST set column3='Remarks' where lower(column3) like 'remarks%';

update staging2.stg_specific_table_IHTNST_NST set column7='Acceptance Criteria' 
where lower(column7) like 'acceptance criteria%';


update transactional.source_config set F1_source=F1_modified ;


/* keyword match in config table*/
execute 'update transactional.source_config a  
set row_number_start=(select min(b.row_number)
from staging2.stg_specific_table_IHTNST_NST  b
where upper(F1_modified)=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||'''';

/* reverese update in process table for match*/
execute 'update staging2.stg_specific_table_IHTNST_NST  a  
set is_rownumber_fetched=1
from transactional.source_config  b
where upper(F1_modified)=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||'''';

/*  update config files for row numbers start, end */
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||'''';

/* config file last field update as null otherwise it picks up next format row number*/
execute 'update transactional.source_config a 
set row_read_end = null ,run_time=current_timestamp
where f1_modified =''Prepared by'' 
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_IHTNST_NST
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
from  staging2.stg_specific_table_IHTNST_NST  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_IHTNST_NST
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
from  staging2.stg_specific_table_IHTNST_NST  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=''Tyre Details''
and  b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

execute 'insert into staging2.stg_process_table_IHTNST_NST
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
from  staging2.stg_specific_table_IHTNST_NST  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_IHTNST_NST
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
from  staging2.stg_specific_table_IHTNST_NST  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=''BY STANDERS NOISE''
and  b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

execute 'insert into staging2.stg_process_table_IHTNST_NST
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
from  staging2.stg_specific_table_IHTNST_NST  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=''OEL Noise without Load''
and  b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

execute 'insert into staging2.stg_process_table_IHTNST_NST
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
from  staging2.stg_specific_table_IHTNST_NST  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=''OEL Noise with Load''
and  b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

execute 'insert into staging2.stg_process_table_IHTNST_NST
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
from  staging2.stg_specific_table_IHTNST_NST  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=''Prepared by''
and  b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

/*selecting tractor model*/
select column4 into __model from staging2.stg_process_table_IHTNST_nst a
where  block_tag='IHTNST_NST_H1' and trim(column3)='Tractor Model';

/*block starts - IHTNST_NST_H1_BLOCK */

perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
'IHTNST_NST_H1_BLOCK',__file_mnemonic,__file_sheet_mnemonic,1);
err_block:='IHTNST_NST_H1_BLOCK';
insert into staging2.IHTNST_NST_H1_INT (c1,c2)
select a.column3,column4 from  staging2.stg_process_table_IHTNST_NST a where block_tag='IHTNST_NST_H1';

insert into staging2.IHTNST_NST_H1_INT (c1,c2)
select a.column6,column7 from  staging2.stg_process_table_IHTNST_NST a where block_tag='IHTNST_NST_H1';

insert into staging2.IHTNST_NST_H1_INT (c1,c2)
select 'Objective',column8
from  staging2.stg_process_table_IHTNST_NST a 
where block_tag='IHTNST_NST_H1'
and row_number=
(
select row_number+1 from 
staging2.stg_process_table_IHTNST_NST a 
where column8='Objective'
and block_tag='IHTNST_NST_H1'
);

insert into staging2.IHTNST_NST_H1_INT (c1,c2)
select 'Condition',column8
from  staging2.stg_process_table_IHTNST_NST a 
where block_tag='IHTNST_NST_Tyre_Details'
and row_number=
(
select row_number+1 from 
staging2.stg_process_table_IHTNST_NST a 
where column8='Condition'
and block_tag='IHTNST_NST_Tyre_Details'
);

insert into staging2.IHTNST_NST_H1_INT (c1,c2)
select 'Test Purpose',column6
from  staging2.stg_process_table_IHTNST_NST a 
where column3='Test Purpose' and block_tag ='IHTNST_NST_Engine_RPM_Atmos';


update staging2.IHTNST_NST_H1_Int set model=__model;
execute 'update  staging2.IHTNST_NST_H1_Int set
client_id='||p_client_id||',
function_id='||p_function_id||',
file_syspk='||p_file_syspk||',
file_mnemonic='''||p_file_mnemonic||''',
file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';


insert into staging2.IHTNST_NST_H1_Block
(
dummy_f,
Test_Request_no,
Sample_Receipt_Date,
Test_report_No,
Tractor_Model,
tractor_power_hp,
variant,
Generation,
Customer_Name, 
Test_Engineer,
Test_Report_Date,
No_of_Sample,
Test_Start_Date,
Test_End_Date,
Tractor_Sr_No,
drive, 
platform,
Test_Facility,
Operator_Name,
Project_Group,
Test_standard_refer,
Objective,
condition,
Test_Purpose
)
SELECT *
FROM   crosstab(
       'SELECT  unnest(''{c2}''::text[]) AS col
             , row_number() OVER ()
             , unnest(ARRAY[c2::text]) AS val
        FROM  staging2.IHTNST_NST_H1_INT
        ORDER  BY generate_series(1,23),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,a_19 text,a_20 text,a_21 text,a_22 text,a_23 text);

delete from staging2.IHTNST_NST_H1_Block where dummy_f is null ; 
update staging2.IHTNST_NST_H1_Block set model=__model;
execute 'update  staging2.IHTNST_NST_H1_Block set
client_id='||p_client_id||',
function_id='||p_function_id||',
file_syspk='||p_file_syspk||',
file_mnemonic='''||p_file_mnemonic||''',
file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

perform fw_core.fn_jobctl_block_end(__file_syspk,'IHTNST_NST_H1_BLOCK'); 

/*block starts - IHTNST_NST_Tyre_Details_Block */

perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
'IHTNST_NST_Tyre_Details_Block',__file_mnemonic,__file_sheet_mnemonic,2);
err_block:='IHTNST_NST_Tyre_Details_Block';

insert into staging2.IHTNST_NST_Tyre_Details_Block
(
dummy_f,
Tyre_Details,
Tyre_Make,
Tyre_size,
Ply_Rating,
Load_Carrying_Capacity,
Wheel_rim_Make_size
)
SELECT *
FROM   crosstab(
       'SELECT  unnest(''{column4,column6}''::text[]) AS col
             , row_number() OVER ()
             , unnest(ARRAY[column4::text,column6::text]) AS val
        FROM  staging2.stg_process_table_IHTNST_NST where block_tag=''IHTNST_NST_Tyre_Details''
        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);
  
update staging2.IHTNST_NST_Tyre_Details_Block
set ply_rating =replace(ply_rating,' Ply','');

delete from staging2.IHTNST_NST_Tyre_Details_Block where dummy_f is null ; 
update staging2.IHTNST_NST_Tyre_Details_Block set model=__model;
execute 'update  staging2.IHTNST_NST_Tyre_Details_Block set
client_id='||p_client_id||',
function_id='||p_function_id||',
file_syspk='||p_file_syspk||',
file_mnemonic='''||p_file_mnemonic||''',
file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

perform fw_core.fn_jobctl_block_end(__file_syspk,'IHTNST_NST_Tyre_Details_Block'); 


/* inserting Engine_RPM_Atmos_INT */

insert into staging2.IHTNST_NST_Engine_RPM_Atmos_Int
(
column3,column4,column5,column6,column7,
column8,column9,block_row_number
)
select 
column3,column4,column5,column6,column7,
column8,column9,block_row_number
from  staging2.stg_process_table_IHTNST_NST a
where block_tag='IHTNST_NST_Engine_RPM_Atmos'
order by block_row_number;

update staging2.IHTNST_NST_Engine_RPM_Atmos_Int
set column8=(select column6 from staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where block_row_number=9)  where block_row_number=5;


update staging2.IHTNST_NST_Engine_RPM_Atmos_Int
set column6 = null where block_row_number=9;

update staging2.IHTNST_NST_Engine_RPM_Atmos_Int
set column9=(select column7 from staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where block_row_number=9) where block_row_number=5;

update staging2.IHTNST_NST_Engine_RPM_Atmos_Int
set column7=null where  block_row_number=9;

update staging2.IHTNST_NST_Engine_RPM_Atmos_Int
set column8=(select column8 from staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where block_row_number=9)where block_row_number=6;

update staging2.IHTNST_NST_Engine_RPM_Atmos_Int
set column8 =null where block_row_number=9;

update staging2.IHTNST_NST_Engine_RPM_Atmos_Int
set column9=(select column9 from staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where block_row_number=9)where block_row_number=6;


update staging2.IHTNST_NST_Engine_RPM_Atmos_Int
set column9 =null where block_row_number=9;

update staging2.IHTNST_NST_Engine_RPM_Atmos_Int set model=__model;
execute 'update  staging2.IHTNST_NST_Engine_RPM_Atmos_Int set
client_id='||p_client_id||',
function_id='||p_function_id||',
file_syspk='||p_file_syspk||',
file_mnemonic='''||p_file_mnemonic||''',
file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

/*block starts - IHTNST_NST_Engine_RPM_Block */

perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
'IHTNST_NST_Engine_RPM_Block',__file_mnemonic,__file_sheet_mnemonic,3);
err_block:='IHTNST_NST_Engine_RPM_Block';
insert  into staging2.IHTNST_NST_Engine_RPM_Block(dummy_f) values ('dummy');

update staging2.IHTNST_NST_Engine_RPM_Block
set low_idle_declared=(select column4 from
staging2.IHTNST_NST_Engine_RPM_Atmos_Int 
where  block_row_number=2 and column3='Low Idle RPM')
where dummy_f='dummy';

update staging2.IHTNST_NST_Engine_RPM_Block
set low_idle_observed=(select column6 from
staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where  block_row_number=2 and column3='Low Idle RPM')
where dummy_f='dummy';

update staging2.IHTNST_NST_Engine_RPM_Block
set high_idle_declared=(select column4 from
staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where  block_row_number=3 and column3='High IdleRPM')
where dummy_f='dummy';

update staging2.IHTNST_NST_Engine_RPM_Block
set high_idle_observed=(select column6 from
staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where  block_row_number=3 and column3='High IdleRPM')
where dummy_f='dummy';

update staging2.IHTNST_NST_Engine_RPM_Block
set rated_rpm_declared=(select column4 from
staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where  block_row_number=4 and column3='Rated RPM')
where dummy_f='dummy';

update staging2.IHTNST_NST_Engine_RPM_Block
set rated_rpm_observed=(select column6 from
staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where  block_row_number=4 and column3='Rated RPM')
where dummy_f='dummy';

update staging2.IHTNST_NST_Engine_RPM_Block
set Engine_to_PTO_Ratio=(select column4 from
staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where  block_row_number=9 and column3='Engine to PTO Ratio')
where dummy_f='dummy';

update  staging2.IHTNST_NST_Engine_RPM_Block
set low_idle_remark= (select column7
FROM  staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where  block_row_number=2 and column3='Low Idle RPM')
where dummy_f='dummy';

update  staging2.IHTNST_NST_Engine_RPM_Block
set high_idle_remark= (select column7
FROM  staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where  block_row_number=3 and column3='High IdleRPM')
where dummy_f='dummy';


update staging2.IHTNST_NST_Engine_RPM_Block set model=__model;
execute 'update  staging2.IHTNST_NST_Engine_RPM_Block set
client_id='||p_client_id||',
function_id='||p_function_id||',
file_syspk='||p_file_syspk||',
file_mnemonic='''||p_file_mnemonic||''',
file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

perform fw_core.fn_jobctl_block_end(__file_syspk,'IHTNST_NST_Engine_RPM_Block'); 

/*block starts -IHTNST_NST_Weight_Block*/

perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
'IHTNST_NST_Weight_Block',__file_mnemonic,__file_sheet_mnemonic,4);
err_block:='IHTNST_NST_Weight_Block';
insert  into staging2.IHTNST_NST_Weight_Block(dummy_f) values ('dummy');

update staging2.IHTNST_NST_Weight_Block
set Front_weight_declared=(select column4 from
staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where  block_row_number=6 and column3='Front')
where dummy_f='dummy';

update staging2.IHTNST_NST_Weight_Block
set Front_weight_observed=(select column6 from
staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where  block_row_number=6 and column3='Front')
where dummy_f='dummy';

update staging2.IHTNST_NST_Weight_Block
set Rear_weight_declared=(select column4 from
staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where  block_row_number=7 and column3='Rear')
where dummy_f='dummy';

update staging2.IHTNST_NST_Weight_Block
set Rear_weight_observed=(select column6 from
staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where  block_row_number=7 and column3='Rear')
where dummy_f='dummy';

update staging2.IHTNST_NST_Weight_Block 
set Total_weight_declared=(select column4 from
staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where  block_row_number=8 and column3='Total')
where dummy_f='dummy';

update staging2.IHTNST_NST_Weight_Block 
set Total_weight_observed=(select column6 from
staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where  block_row_number=8 and column3='Total')
where dummy_f='dummy';

update  staging2.IHTNST_NST_Weight_Block
set front_weight_remark= (select column7
FROM  staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where  block_row_number=6 and column3='Front')
where dummy_f='dummy';

update  staging2.IHTNST_NST_Weight_Block
set rear_weight_remark= (select column7
FROM  staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where  block_row_number=7 and column3='Rear')
where dummy_f='dummy';

update  staging2.IHTNST_NST_Weight_Block
set total_weight_remark= (select column7
FROM  staging2.IHTNST_NST_Engine_RPM_Atmos_Int
where  block_row_number=8 and column3='Total')
where dummy_f='dummy';


update staging2.IHTNST_NST_Weight_Block set model=__model;
execute 'update  staging2.IHTNST_NST_Weight_Block set
client_id='||p_client_id||',
function_id='||p_function_id||',
file_syspk='||p_file_syspk||',
file_mnemonic='''||p_file_mnemonic||''',
file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

perform fw_core.fn_jobctl_block_end(__file_syspk,'IHTNST_NST_Weight_Block');


/*block starts - IHTNST_NST_Atmos_Cond_Block */

perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
'IHTNST_NST_Atmos_Cond_Block',__file_mnemonic,__file_sheet_mnemonic,5);
err_block:='IHTNST_NST_Atmos_Cond_Block';
insert into  staging2.IHTNST_NST_Atmos_Cond_Block
(
dummy_f,
Ambient_temp_C,
humidity,
Pressure_kPa,
Background_Noise,
wind_velocity
)
SELECT *
FROM   crosstab(
       'SELECT  unnest(''{column9}''::text[]) AS col
             , row_number() OVER ()
             , unnest(ARRAY[column9::text]) AS val
        FROM  staging2.IHTNST_NST_Engine_RPM_Atmos_Int where block_row_number between 2 and 6
        ORDER  BY generate_series(1,15),block_row_number,2'
   ) t (col text,a_1 text,a_2 text,a_3 text,a_4 text,a_5 text);

delete from staging2.IHTNST_NST_Atmos_Cond_Block where dummy_f is null ; 
update  staging2.IHTNST_NST_Atmos_Cond_Block set model=__model;
execute 'update   staging2.IHTNST_NST_Atmos_Cond_Block set
client_id='||p_client_id||',
function_id='||p_function_id||',
file_syspk='||p_file_syspk||',
file_mnemonic='''||p_file_mnemonic||''',
file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

perform fw_core.fn_jobctl_block_end(__file_syspk,'IHTNST_NST_Atmos_Cond_Block'); 


/*block starts - IHTNST_NST_STAND_Noise_Block */

perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
'IHTNST_NST_STAND_Noise_Block',__file_mnemonic,__file_sheet_mnemonic,6);
err_block:='IHTNST_NST_STAND_Noise_Block';
insert into staging2.IHTNST_NST_STAND_Noise_Block
(
column3,column4,column5,column6,column7,
column8,column9,block_row_number
)
select 
column3,column4,column5,column6,column7,
column8,column9,block_row_number
from  staging2.stg_process_table_IHTNST_NST a
where block_tag='IHTNST_NST_STAND_Noise'
order by block_row_number;

update staging2.IHTNST_NST_STAND_Noise_Block
set column2=column3 where column4 is null and column3 like 'Mode%';

update  staging2.IHTNST_NST_STAND_Noise_Block
set column1=column3 where column4 is null and block_row_number =1;

update staging2.IHTNST_NST_STAND_Noise_Block a
 set column1= b.first_value from (SELECT
block_row_number, column1, value_partition, first_value(column1) over (partition by value_partition order by block_row_number)
FROM (
SELECT
block_row_number,
column1,
sum(case when column1 is null then 0 else 1 end) over (order by block_row_number) as value_partition
 FROM  staging2.IHTNST_NST_STAND_Noise_Block
ORDER BY block_row_number ASC
) as q) b where a.block_row_number = b.block_row_number;

update staging2.IHTNST_NST_STAND_Noise_Block a
 set column2= b.first_value from (SELECT
block_row_number, column2, value_partition, first_value(column2) over (partition by value_partition order by block_row_number)
FROM (
SELECT
block_row_number,
column2,
sum(case when column2 is null then 0 else 1 end) over (order by block_row_number) as value_partition
 FROM  staging2.IHTNST_NST_STAND_Noise_Block
ORDER BY block_row_number ASC
) as q) b where a.block_row_number = b.block_row_number;

update staging2.IHTNST_NST_STAND_Noise_Block
set trx_record=0; 

update staging2.IHTNST_NST_STAND_Noise_Block
set trx_record=1 where 
column3 is not null and
column4 is not null and
column5 is not null and
column6 is not null and
column7 is not null and
column8 is not null and
column9 is not null 
;


update staging2.IHTNST_NST_STAND_Noise_Block
set remarks=(select column3
from staging2.stg_process_table_IHTNST_NST a 
where block_tag='IHTNST_NST_STAND_Noise' and row_number=
(
select row_number+1 from 
staging2.stg_process_table_IHTNST_NST a 
where lower(column3)='remarks'
and block_tag='IHTNST_NST_STAND_Noise'
));

update staging2.IHTNST_NST_STAND_Noise_Block
set acceptance_criteria=(select column7
from staging2.stg_process_table_IHTNST_NST a 
where block_tag='IHTNST_NST_STAND_Noise' and row_number=
(
select row_number+1 from 
staging2.stg_process_table_IHTNST_NST a 
where lower(column7)='acceptance criteria'
and block_tag='IHTNST_NST_STAND_Noise'
));


update staging2.IHTNST_NST_OEL_Noise_Block
set trx_record = 0
where column3 = 'Remarks';



update  staging2.IHTNST_NST_STAND_Noise_Block set model=__model;
execute 'update   staging2.IHTNST_NST_STAND_Noise_Block  set
client_id='||p_client_id||',
function_id='||p_function_id||',
file_syspk='||p_file_syspk||',
file_mnemonic='''||p_file_mnemonic||''',
file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

perform fw_core.fn_jobctl_block_end(__file_syspk,'IHTNST_NST_STAND_Noise_Block'); 


/*block starts - IHTNST_NST_OEL_Noise_Block */

perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
'IHTNST_NST_OEL_Noise_Block',__file_mnemonic,__file_sheet_mnemonic,7);
err_block:='IHTNST_NST_OEL_Noise_Block';
insert into staging2.IHTNST_NST_OEL_Noise_Block
(
column3,column4,column5,column6,column7,
column8,column9,block_row_number
)
select 
column3,column4,column5,column6,column7,
column8,column9,block_row_number
from  staging2.stg_process_table_IHTNST_NST a
where block_tag='IHTNST_NST_OEL_Noise'
order by block_row_number;

update staging2.IHTNST_NST_OEL_Noise_Block
set column2=column3 where column4 is null and column3 like 'Mode%';

update  staging2.IHTNST_NST_OEL_Noise_Block
set column1=column3 where column4 is null and block_row_number =1;

update staging2.IHTNST_NST_OEL_Noise_Block a
 set column1= b.first_value from (SELECT
block_row_number, column1, value_partition, first_value(column1) over (partition by value_partition order by block_row_number)
FROM (
SELECT
block_row_number,
column1,
sum(case when column1 is null then 0 else 1 end) over (order by block_row_number) as value_partition
 FROM  staging2.IHTNST_NST_OEL_Noise_Block
ORDER BY block_row_number ASC
) as q) b where a.block_row_number = b.block_row_number;

update staging2.IHTNST_NST_OEL_Noise_Block a
 set column2= b.first_value from (SELECT
block_row_number, column2, value_partition, first_value(column2) over (partition by value_partition order by block_row_number)
FROM (
SELECT
block_row_number,
column2,
sum(case when column2 is null then 0 else 1 end) over (order by block_row_number) as value_partition
 FROM  staging2.IHTNST_NST_OEL_Noise_Block
ORDER BY block_row_number ASC
) as q) b where a.block_row_number = b.block_row_number;


update staging2.IHTNST_NST_OEL_Noise_Block 
set trx_record=0; 

update staging2.IHTNST_NST_OEL_Noise_Block 
set trx_record=1 where 
column5 is not null and
column6 is not null
;

update staging2.IHTNST_NST_OEL_Noise_Block 
set trx_record=0 where column4 like 'L%';


update staging2.IHTNST_NST_OEL_Noise_Block 
set remarks=(select column3
from staging2.stg_process_table_IHTNST_NST a 
where block_tag='IHTNST_NST_OEL_Noise' and row_number=
(
select row_number+1 from 
staging2.stg_process_table_IHTNST_NST a 
where lower(column3)='remarks'
and block_tag='IHTNST_NST_OEL_Noise'
));

update staging2.IHTNST_NST_OEL_Noise_Block 
set acceptance_criteria=(select column7
from staging2.stg_process_table_IHTNST_NST a 
where block_tag='IHTNST_NST_OEL_Noise' and row_number=
(
select row_number+1 from 
staging2.stg_process_table_IHTNST_NST a 
where lower(column7)='acceptance criteria'
and block_tag='IHTNST_NST_OEL_Noise'
));

update  staging2.IHTNST_NST_OEL_Noise_Block set model=__model;
execute 'update   staging2.IHTNST_NST_OEL_Noise_Block set
client_id='||p_client_id||',
function_id='||p_function_id||',
file_syspk='||p_file_syspk||',
file_mnemonic='''||p_file_mnemonic||''',
file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';



update staging2.IHTNST_NST_OEL_Noise_Block
set trx_record = 0
where column3 = 'Remarks';

update staging2.IHTNST_NST_OEL_Noise_Block
set trx_record = 0
where block_row_number = 
(select block_row_number+1 from staging2.IHTNST_NST_OEL_Noise_Block where column3 = 'Remarks' );


perform fw_core.fn_jobctl_block_end(__file_syspk,'IHTNST_NST_OEL_Noise_Block'); 

/*block starts - IHTNST_NST_OEL_Noise_Block */

perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
'IHTNST_NST_OEL_Noise_Load_Block',__file_mnemonic,__file_sheet_mnemonic,8);
err_block:='IHTNST_NST_OEL_Noise_Load_Block';
insert into staging2.IHTNST_NST_OEL_Noise_Load_Block
(
column3,column4,column5,column6,column7,
column8,column9,block_row_number
)
select 
column3,column4,column5,column6,column7,
column8,column9,block_row_number
from  staging2.stg_process_table_IHTNST_NST a
where block_tag='IHTNST_NST_OEL_Noise_Load'
order by block_row_number;

update staging2.IHTNST_NST_OEL_Noise_Load_Block
set column2=column3 where column4 is null and column3 like 'Mode%';

update  staging2.IHTNST_NST_OEL_Noise_Load_Block
set column1=column3 where column4 is null and block_row_number =1;

update staging2.IHTNST_NST_OEL_Noise_Load_Block a
 set column1= b.first_value from (SELECT
block_row_number, column1, value_partition, first_value(column1) over (partition by value_partition order by block_row_number)
FROM (
SELECT
block_row_number,
column1,
sum(case when column1 is null then 0 else 1 end) over (order by block_row_number) as value_partition
 FROM  staging2.IHTNST_NST_OEL_Noise_Load_Block
ORDER BY block_row_number ASC
) as q) b where a.block_row_number = b.block_row_number;

update staging2.IHTNST_NST_OEL_Noise_Load_Block a
 set column2= b.first_value from (SELECT
block_row_number, column2, value_partition, first_value(column2) over (partition by value_partition order by block_row_number)
FROM (
SELECT
block_row_number,
column2,
sum(case when column2 is null then 0 else 1 end) over (order by block_row_number) as value_partition
 FROM  staging2.IHTNST_NST_OEL_Noise_Load_Block
ORDER BY block_row_number ASC
) as q) b where a.block_row_number = b.block_row_number;

update staging2.IHTNST_NST_OEL_Noise_Load_Block 
set trx_record=0; 

update staging2.IHTNST_NST_OEL_Noise_Load_Block 
set trx_record=1 where 
column5 is not null and
column6 is not null and
column7 is not null and
column8 is not null and
column9 is not null 
;

update staging2.IHTNST_NST_OEL_Noise_Load_Block 
set remarks=(select column3
from staging2.stg_process_table_IHTNST_NST a 
where block_tag='IHTNST_NST_OEL_Noise_Load' and row_number=
(
select row_number+1 from 
staging2.stg_process_table_IHTNST_NST a 
where lower(column3)='remarks'
and block_tag='IHTNST_NST_OEL_Noise_Load'
));

update staging2.IHTNST_NST_OEL_Noise_Load_Block 
set acceptance_criteria=(select column7
from staging2.stg_process_table_IHTNST_NST a 
where block_tag='IHTNST_NST_OEL_Noise_Load' and row_number=
(
select row_number+1 from 
staging2.stg_process_table_IHTNST_NST a 
where lower(column7)='acceptance criteria'
and block_tag='IHTNST_NST_OEL_Noise_Load'
));

update  staging2.IHTNST_NST_OEL_Noise_Load_Block set model=__model;
execute 'update   staging2.IHTNST_NST_OEL_Noise_Load_Block set
client_id='||p_client_id||',
function_id='||p_function_id||',
file_syspk='||p_file_syspk||',
file_mnemonic='''||p_file_mnemonic||''',
file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';


update staging2.IHTNST_NST_OEL_Noise_Load_Block
set trx_record = 0
where column3 = 'Remarks';

update staging2.IHTNST_NST_OEL_Noise_Load_Block
set trx_record = 0
where block_row_number = 
(select block_row_number+1 from staging2.IHTNST_NST_OEL_Noise_load_Block where column3 = 'Remarks' );


perform fw_core.fn_jobctl_block_end(__file_syspk,'IHTNST_NST_OEL_Noise_Load_Block'); 



/*block starts - IHTNST_NST_Footer_block */

perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
'IHTNST_NST_Footer_block',__file_mnemonic,__file_sheet_mnemonic,9);
err_block:='IHTNST_NST_Footer_block';

insert into staging2.IHTNST_NST_Footer_block(dummy_f) values ('dummy');

update staging2.IHTNST_NST_Footer_block a
set prepared_by=( select column4 from staging2.stg_process_table_IHTNST_NST b 
where b.block_tag='IHTNST_NST_Footer'
and lower(column3)='prepared by' )
where dummy_F='dummy';

update staging2.IHTNST_NST_Footer_block a
set reviewed_by=( select column4 from staging2.stg_process_table_IHTNST_NST b 
where b.block_tag='IHTNST_NST_Footer'
and lower(column3)='reviewed by' )
where dummy_F='dummy';

update staging2.IHTNST_NST_Footer_block a
set approved_by=( select column4 from staging2.stg_process_table_IHTNST_NST b 
where  b.block_tag='IHTNST_NST_Footer'
and lower(column3)='approved by' )
where dummy_F='dummy';

update staging2.IHTNST_NST_Footer_block a
set comments=( select column3 from staging2.stg_process_table_IHTNST_NST b 
where b.block_tag='IHTNST_NST_Footer'
and block_row_number=5 )
where dummy_F='dummy';

update staging2.IHTNST_NST_Footer_block a
set rev1=( select column3 from staging2.stg_process_table_IHTNST_NST b 
where b.block_tag='IHTNST_NST_Footer'
and block_row_number=6 )
where dummy_F='dummy';


update staging2.IHTNST_NST_Footer_block a
set rev2=( select column5 from staging2.stg_process_table_IHTNST_NST b 
where  b.block_tag='IHTNST_NST_Footer'
and block_row_number=6 )
where dummy_F='dummy';

update staging2.IHTNST_NST_Footer_block a
set rev3=( select column8 from staging2.stg_process_table_IHTNST_NST b 
where  b.block_tag='IHTNST_NST_Footer'
and block_row_number=6 )
where dummy_F='dummy';

update staging2.IHTNST_NST_Footer_block a
set replaces=( select column9 from staging2.stg_process_table_IHTNST_NST b 
where b.block_tag='IHTNST_NST_Footer'
and block_row_number=1)
where dummy_F='dummy';

update staging2.IHTNST_NST_Footer_block a
set revision_no=( select column9 from staging2.stg_process_table_IHTNST_NST b 
where b.block_tag='IHTNST_NST_Footer'
and block_row_number=2)
where dummy_F='dummy';

update staging2.IHTNST_NST_Footer_block a
set prepared_date=( select column8 from staging2.stg_process_table_IHTNST_NST b 
where  b.block_tag='IHTNST_NST_Footer'
and block_row_number=1)
where dummy_F='dummy';

update staging2.IHTNST_NST_Footer_block a
set reviewed_date=( select column8 from staging2.stg_process_table_IHTNST_NST b 
where  b.block_tag='IHTNST_NST_Footer'
and block_row_number=2)
where dummy_F='dummy';

update staging2.IHTNST_NST_Footer_block a
set approved_date=( select column8 from staging2.stg_process_table_IHTNST_NST b 
where  b.block_tag='IHTNST_NST_Footer'
and block_row_number=3)
where dummy_F='dummy';

update  staging2.IHTNST_NST_Footer_block set model=__model;
execute 'update   staging2.IHTNST_NST_Footer_block set
client_id='||p_client_id||',
function_id='||p_function_id||',
file_syspk='||p_file_syspk||',
file_mnemonic='''||p_file_mnemonic||''',
file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

perform fw_core.fn_jobctl_block_end(__file_syspk,'IHTNST_NST_Footer_block'); 
perform  fw_core.fn_insert_db_error ( __client_id, __function_id, 1001, 'Compegence', __file_syspk ,__file_mnemonic,__file_sheet_mnemonic ,null,'stg2', 'fn_IHTNST_NST_block', err_state, err_msg, err_detail, err_hint, err_context,'success');

end
$function$
;