drop function if exists staging2.fn_PTO_PRF_block;
CREATE OR REPLACE FUNCTION staging2.fn_PTO_PRF_block(p_client_id int,p_function_id int, p_file_mnemonic text,
p_file_sheet_mnemonic text, p_file_syspk int)
   RETURNS text AS $$
   declare __make text;
   declare __model text;
  
   declare __client_id int :=p_client_id;
   declare __function_id int :=p_function_id;
   declare __file_mnemonic text  :=p_file_mnemonic;
   declare __file_sheet_mnemonic text :=p_file_sheet_mnemonic;
   declare __file_syspk int :=p_file_syspk;
   declare err_state   text;
   declare err_msg     text;
   declare err_detail  text;
   declare err_hint    text;
   declare err_context text;   
   declare err_query int;
   declare err_block text;  
 begin
 
 /***********************************************************************************
Function Name:fn_PTOBEN_PRF_block
Function Desc: This function populates data into staging2 blocks
File Format: PTOBEN
Sheet Format: PTOBEN_PRF
Creation Date: April 26 2021
Updation Date:
Author: compegence team
Function Call: select staging2.fn_PTO_PRF_block(1,1,'PTOBEN','PTOBEN_PRFN',944)
***************************************************************************************/


SET search_path TO  staging2;
/* rerunnability - delete block tables and update config tables to null */
truncate table  staging2.stg_specific_table_PTOBEN_PRF;
truncate table  staging2.stg_process_table_PTOBEN_PRF;
truncate table  staging2.PTOBEN_PRF_tractor_specs_int;
truncate table  staging2.PTOBEN_PRF_tractor_specs_block;
truncate table  staging2.PTOBEN_PRF_key_performance_parameters_int;
truncate table  staging2.PTOBEN_PRF_key_performance_parameters_block;
truncate table  staging2.PTOBEN_PRF_varying_speed_natural_ambient_block;
truncate table  staging2.PTOBEN_PRF_2hrs_max_power_natural_ambient_block;
truncate table  staging2.PTOBEN_PRF_varying_load_rated_rpm_block;
truncate table  staging2.PTOBEN_PRF_varying_load_std_pto_block;
truncate table  staging2.PTOBEN_PRF_smoke_test_block;
truncate table  staging2.PTOBEN_PRF_varying_speed_test_high_ambient_block ;
truncate table  staging2.PTOBEN_PRF_2hrs_max_power_test_high_ambient_block ;
truncate table  staging2.PTOBEN_PRF_engine_oil_consumption_block ;

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 PTOBEN */
execute 'insert into  staging2.stg_specific_table_PTOBEN_PRF 
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||'';


/* trimming data */
update staging2.stg_specific_table_PTOBEN_PRF set column3 =  TRIM (TRAILING FROM column3 );
update staging2.stg_specific_table_PTOBEN_PRF set column3 =  TRIM (LEADING FROM column3 );

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_PTOBEN_PRF  b
where trim(upper(F1_modified))=trim(upper(column2))
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||'''';


/*  update config files for row numbers start, end */
execute 'update staging2.stg_specific_table_PTOBEN_PRF  a  
set is_rownumber_fetched=1
from transactional.source_config  b
where trim(upper(F1_modified))=trim(upper(column3))
and b.row_number_start=a.row_number
and is_rownumber_fetched is null
and a.file_mnemonic='''||p_file_mnemonic||''' and a.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

execute 'update transactional.source_config a set row_previous_number=row_number_start-1
where a.file_mnemonic='''||p_file_mnemonic||''' and a.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

execute 'update transactional.source_config a
set row_read_end= (select b.row_number_start
from transactional.source_config b 
where b.syspk=a.syspk+1 and file_mnemonic='''||p_file_mnemonic||''' and file_sheet_mnemonic='''||p_file_sheet_mnemonic||''' ) 
where a.file_mnemonic='''||p_file_mnemonic||''' and a.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';


/* 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 =''Engine Oil Consumption @ High Ambient'' 
and a.file_mnemonic='''||p_file_mnemonic||''' and a.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

/* tagging block_row_numbers for each block in process table*/

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

execute 'insert into staging2.stg_process_table_PTOBEN_PRF
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag 
from  staging2.stg_specific_table_PTOBEN_PRF  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=''Varying Speed Test / Full Throttle Performance @ Natural Ambient''
and  b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

execute 'insert into staging2.stg_process_table_PTOBEN_PRF
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag 
from  staging2.stg_specific_table_PTOBEN_PRF  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=''2Hrs Max Power Test @ Natural Ambient''
and  b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

execute 'insert into staging2.stg_process_table_PTOBEN_PRF
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag from  staging2.stg_specific_table_PTOBEN_PRF  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=''Varying loads @ Rated RPM @ Natural Ambient''
and  b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

execute 'insert into staging2.stg_process_table_PTOBEN_PRF
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag from  staging2.stg_specific_table_PTOBEN_PRF  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=''Varying load @ STD. PTO @ Natural Ambient''
and  b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

execute 'insert into staging2.stg_process_table_PTOBEN_PRF
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
from  staging2.stg_specific_table_PTOBEN_PRF  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=''Smoke Test @ Natural Ambient''
and  b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';

execute 'insert into staging2.stg_process_table_PTOBEN_PRF
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
from  staging2.stg_specific_table_PTOBEN_PRF  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=''Varying Speed Test / Full Throttle Performance @ High Ambient''
and  b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';


execute 'insert into staging2.stg_process_table_PTOBEN_PRF
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
from  staging2.stg_specific_table_PTOBEN_PRF  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=''2Hrs Max Power Test @ High Ambient''
and  b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';



execute 'insert into staging2.stg_process_table_PTOBEN_PRF
select a.* , RANK () OVER ( ORDER BY row_number) as block_row_number,b.block_tag as block_tag
from  staging2.stg_specific_table_PTOBEN_PRF  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 Oil Consumption @ High Ambient''
and  b.file_mnemonic='''||p_file_mnemonic||''' and b.file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';


/* fetcching tractor model and make */
select column5 into __make from staging2.stg_process_table_PTOBEN_PRF a
where  block_tag='PTOBEN_PRF_Key_Performance_Parameters' and block_row_number=7 ;

select column5  into __model from staging2.stg_process_table_PTOBEN_PRF a
where  block_tag='PTOBEN_PRF_Key_Performance_Parameters' and block_row_number=9;


/* blocks data loading start - PTOBEN_PRF_key_performance_parameters_int */


insert into staging2.PTOBEN_PRF_tractor_specs_int(c1,c2,block_row_number)
select column2,column5,block_row_number from staging2.stg_process_table_PTOBEN_PRF 
where block_tag = 'PTOBEN_PRF_Key_Performance_Parameters' and block_row_number in (3,7,8,9,10); 

perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
'PTOBEN_PRF_tractor_specs_block',__file_mnemonic,__file_sheet_mnemonic,1); 
err_block := 'PTOBEN_PRF_tractor_specs_block';
insert into  staging2.PTOBEN_PRF_tractor_specs_block
(
dummy_f,
test_standard,
make,
tractor_emmission,
model,
tractor_serial_number
)
SELECT *
FROM   crosstab(
       'SELECT  unnest(''{c2}''::text[]) AS col
             , row_number() OVER ()
             , unnest(ARRAY[c2::text]) AS val
        FROM  staging2.PTOBEN_PRF_tractor_specs_int
        ORDER  BY generate_series(1,9),2'
   ) t (col text,a1 text,a2 text,a3 text,a4 text,a5 text);

delete from staging2.PTOBEN_PRF_tractor_specs_block where dummy_f is null ;
execute 'update staging2.PTOBEN_PRF_tractor_specs_block set file_syspk='||p_file_syspk||''; 
execute 'update staging2.PTOBEN_PRF_tractor_specs_block set client_id='||p_client_id||''; 
execute 'update staging2.PTOBEN_PRF_tractor_specs_block set function_id='||p_function_id||''; 
execute 'update staging2.PTOBEN_PRF_tractor_specs_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
execute 'update staging2.PTOBEN_PRF_tractor_specs_block set file_mnemonic='''||p_file_mnemonic||'''';
update staging2.PTOBEN_PRF_tractor_specs_block set make=__make; 
update staging2.PTOBEN_PRF_tractor_specs_block set model=__model;
perform fw_core.fn_jobctl_block_end(__file_syspk,'PTOBEN_PRF_tractor_specs_block'); 

 
  
insert into staging2.PTOBEN_PRF_key_performance_parameters_int
( file_syspk,c1,c2,c3,block_row_number)
select file_syspk,column2,column5,column53,block_row_number from staging2.stg_process_table_PTOBEN_PRF 
where block_tag = 'PTOBEN_PRF_Key_Performance_Parameters' and block_row_number not in (1,2,5,7,9)
union 
select file_syspk,column8,column10,column11,block_row_number from staging2.stg_process_table_PTOBEN_PRF 
where block_tag = 'PTOBEN_PRF_Key_Performance_Parameters' and block_row_number not in (1,2) 
union 
select  file_syspk,column12,column14,column15,block_row_number from staging2.stg_process_table_PTOBEN_PRF 
where block_tag = 'PTOBEN_PRF_Key_Performance_Parameters' and block_row_number not in (1,2) 
union 
select  file_syspk,column16,column19,column20,block_row_number from staging2.stg_process_table_PTOBEN_PRF 
where block_tag = 'PTOBEN_PRF_Key_Performance_Parameters' and block_row_number not in (1,2) 
union 
select  file_syspk,column21,column25,column53 ,block_row_number from staging2.stg_process_table_PTOBEN_PRF 
where block_tag = 'PTOBEN_PRF_Key_Performance_Parameters' and block_row_number not in (1,2,4,6) 
order by block_row_number,column2;

perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
'PTOBEN_PRF_key_performance_parameters_block',__file_mnemonic,__file_sheet_mnemonic,2); 
err_block := 'PTOBEN_PRF_key_performance_parameters_block';
insert into  staging2.PTOBEN_PRF_key_performance_parameters_block
(
dummy_f,
Ambient_Pressure_Bar,
PTO_power_hp,
Rated_engine_speed,
Test_Engineer, 
test_standard,
Ambient_pressure_mm_of_hg,
max_torque_engine_speed, 
Engine_to_PTO_ratio,
PTO_power_Kw,
back_pressure_bar,
Engine_high_Idle_speed,
PTO_SFC_gm_hp,
Test_Bed, 
back_pressure_mm_of_hg,
Engine_low_Idle_speed, 
PTO_SFC_Kw_hp,
specific_gravity_of_fuel,
pct_of_backup_torque,
Engine_oil_pressure_bar,
Engine_oil_temp_NA,
Place,
Coolant_temp_NA,
exhaust_temperature_C,
Maximum_Equ_crankshaft_Torque_Nm,
Total_run_hrs,
tractor_emmission,
Engine_oil_temp_HA,
Equ_crankshaft_Torque_Maximum_Power_Nm,
Relative_humidity_pct,
Test_date,
Coolant_water_pct,
Coolant_temp_HA,
Engine_speed_at_maximum_equivalent_crankshaft_torque_rpm,
Tooled_up,
tractor_serial_number
)
SELECT *
FROM   crosstab(
       'SELECT  unnest(''{c2,c3}''::text[]) AS col
             , row_number() OVER ()
             , unnest(ARRAY[c2::text,c3::text]) AS val
        FROM  staging2.PTOBEN_PRF_Key_Performance_Parameters_int
        ORDER  BY generate_series(1,15),2'
   ) t (col text,a1 text,a2 text,a3 text,a4 text,a5 text,
  a6 text,a7 text,a8 text,a9 text,a10 text,a11 text,a12 text,a13 text,a14 text,
 a15 text,a16 text,a17 text,a18 text,a19 text,a20 text,a21 text,a22 text,a23 text,a24 text,a25 text
,a26 text,a27 text,a28 text,a29 text,a30 text,a31 text,a32 text,a33 text, a34 text, a35 text);


   
delete from staging2.PTOBEN_PRF_Key_Performance_Parameters_block where dummy_f is null;

update staging2.PTOBEN_PRF_Key_Performance_Parameters_block set trx_record=0 where dummy_f='c1';

update staging2.PTOBEN_PRF_Key_Performance_Parameters_block set test_condition='declared' where dummy_f='c2';
update staging2.PTOBEN_PRF_Key_Performance_Parameters_block set test_condition='observed' where dummy_f='c3';


update staging2.PTOBEN_PRF_Key_Performance_Parameters_block a 
set (test_standard,tractor_serial_number,tractor_emmission,engine_to_pto_ratio,specific_gravity_of_fuel,test_engineer,test_bed,place,total_run_hrs,test_date,tooled_up) = (select test_standard,tractor_serial_number,tractor_emmission,engine_to_pto_ratio,specific_gravity_of_fuel,test_engineer,test_bed,place,total_run_hrs,test_date,tooled_up from staging2.PTOBEN_PRF_Key_Performance_Parameters_block where test_condition='declared')
where test_condition='observed';


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

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

/* blocks data loading start - PTOBEN_PRF_varying_speed_natural_ambient_block */

perform fw_core.fn_jobctl_block_begin(__client_id,__function_id,__file_syspk,
'PTOBEN_PRF_varying_speed_natural_ambient_block',__file_mnemonic,__file_sheet_mnemonic,3); 
err_block := 'PTOBEN_PRF_varying_speed_natural_ambient_block';
insert into staging2.PTOBEN_PRF_varying_speed_natural_ambient_block
(
c2,
c3,
c4,
c5,
c6,
c7,
c8,
c9,
c10,
c11,
c12,
c13,
c14,
c15,
c16,
c17,
c18,
c19,
c20,
c21,
c22,
c23,
c24,
c25,
c26,c27,c28,
block_row_number
)
select column2,
column3,
column4,
column5,
column6,
column7,
column8,
column9,
column10,
column11,
column12,
column13,
column14,
column15,
column16,
column17,
column18,
column19,
column20,
column21,
column22,
column23,
column24,
column25,
column26,
column27,
column28,
block_row_number
from staging2.stg_process_table_PTOBEN_PRF 
where block_tag = 'PTOBEN_PRF_varying_speed_test_natural_ambient';

update staging2.PTOBEN_PRF_varying_speed_natural_ambient_block
set test_condition=c2 where block_row_number=1;

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


update staging2.PTOBEN_PRF_varying_speed_natural_ambient_block set trx_record=0 where
block_row_number in (1,2,3,4) or c4 is null;


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

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

/* blocks data loading start - PTOBEN_PRF_2hrs_max_power_natural_ambient_block */

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

insert into staging2.PTOBEN_PRF_2hrs_max_power_natural_ambient_block
(
c2,
c3,
c4,
c5,
c6,
c7,
c8,
c9,
c10,
c11,
c12,
c13,
c14,
c15,
c16,
c17,
c18,
c19,
c20,
c21,
c22,
c23,
c24,
c25,
c26,c27,c28,
block_row_number
)
select column2,
column3,
column4,
column5,
column6,
column7,
column8,
column9,
column10,
column11,
column12,
column13,
column14,
column15,
column16,
column17,
column18,
column19,
column20,
column21,
column22,
column23,
column24,
column25,
column26,
column27,
column28,
block_row_number
from staging2.stg_process_table_PTOBEN_PRF 
where block_tag = 'PTOBEN_PRF_2hrs_max_power_natural_ambient';

update staging2.PTOBEN_PRF_2hrs_max_power_natural_ambient_block
set test_condition=c2 where block_row_number=1;

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

update staging2.PTOBEN_PRF_2hrs_max_power_natural_ambient_block set trx_record=0 where
block_row_number in (1,2,3,4) or c4 is null;

update staging2.PTOBEN_PRF_2hrs_max_power_natural_ambient_block set trx_record=0 where (c3 is null or c3::numeric = 0) and c2 = 'Avg';


 execute 'update staging2.PTOBEN_PRF_2hrs_max_power_natural_ambient_block set client_id='||p_client_id||'';
 execute 'update staging2.PTOBEN_PRF_2hrs_max_power_natural_ambient_block set function_id='||p_function_id||'';
 execute 'update staging2.PTOBEN_PRF_2hrs_max_power_natural_ambient_block set file_mnemonic='''||p_file_mnemonic||'''';
 execute 'update staging2.PTOBEN_PRF_2hrs_max_power_natural_ambient_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
  execute 'update staging2.PTOBEN_PRF_2hrs_max_power_natural_ambient_block set file_syspk='||p_file_syspk||'';
  
     update staging2.PTOBEN_PRF_2hrs_max_power_natural_ambient_block set make=__make; 
 update staging2.PTOBEN_PRF_2hrs_max_power_natural_ambient_block set model=__model;
 
perform fw_core.fn_jobctl_block_end(__file_syspk,'PTOBEN_PRF_2hrs_max_power_natural_ambient_block'); 

/* blocks data loading start - PTOBEN_PRF_varying_load_rated_rpm_block */

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

 insert into staging2.PTOBEN_PRF_varying_load_rated_rpm_block
(
c2,
c3,
c4,
c5,
c6,
c7,
c8,
c9,
c10,
c11,
c12,
c13,
c14,
c15,
c16,
c17,
c18,
c19,
c20,
c21,
c22,
c23,
c24,
c25,c26,c27,c28,
block_row_number
)
select column2,
column3,
column4,
column5,
column6,
column7,
column8,
column9,
column10,
column11,
column12,
column13,
column14,
column15,
column16,
column17,
column18,
column19,
column20,
column21,
column22,
column23,
column24,
column25,
column26,
column27,
column28,
block_row_number
from staging2.stg_process_table_PTOBEN_PRF 
where block_tag='PTOBEN_PRF_varying_load_rated_rpm';

update staging2.PTOBEN_PRF_varying_load_rated_rpm_block 
set c1 =c3
where c2 is null and trx_record=1;

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


update staging2.PTOBEN_PRF_varying_load_rated_rpm_block
set test_condition=c2 where block_row_number=1;

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

update staging2.PTOBEN_PRF_varying_load_rated_rpm_block 
set c3 = null
where c1=c3;

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

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

update staging2.PTOBEN_PRF_varying_load_rated_rpm_block 
set trx_record = 0
where c4 is null or block_row_number=3;



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

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

/* blocks data loading start - PTOBEN_PRF_varying_load_std_pto_block */

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

 insert into staging2.PTOBEN_PRF_varying_load_std_pto_block
(
c2,
c3,
c4,
c5,
c6,
c7,
c8,
c9,
c10,
c11,
c12,
c13,
c14,
c15,
c16,
c17,
c18,
c19,
c20,
c21,
c22,
c23,
c24,
c25,c26,c27,c28,
block_row_number
)
select column2,
column3,
column4,
column5,
column6,
column7,
column8,
column9,
column10,
column11,
column12,
column13,
column14,
column15,
column16,
column17,
column18,
column19,
column20,
column21,
column22,
column23,
column24,
column25,
column26,
column27,
column28,
block_row_number
from staging2.stg_process_table_PTOBEN_PRF 
where block_tag='PTOBEN_PRF_varying_load_std_pto' ;

update staging2.PTOBEN_PRF_varying_load_std_pto_block 
set c1 =c3
where c2 is null and trx_record=1;

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

update staging2.PTOBEN_PRF_varying_load_std_pto_block
set test_condition=c2 where block_row_number=1;

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

update staging2.PTOBEN_PRF_varying_load_std_pto_block 
set c3 = null
where c1=c3;

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

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

update staging2.PTOBEN_PRF_varying_load_std_pto_block 
set trx_record = 0
where c4 is null or block_row_number=3;

update staging2.PTOBEN_PRF_varying_load_std_pto_block set trx_record=0 where
block_row_number in (1,2,3,4);

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

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

/* blocks data loading start - PTOBEN_PRF_smoke_test_block */

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

 insert into staging2.PTOBEN_PRF_smoke_test_block
(
c2,
c3,
c4,
c5,
c6,
c7,
c8,
block_row_number
)
select 
column2,
column3,column4,column5,
column6,column7,column8,block_row_number
from staging2.stg_process_table_PTOBEN_PRF where block_tag='PTOBEN_PRF_smoke_test';


update staging2.PTOBEN_PRF_smoke_test_block
set test_condition=c2 where block_row_number=1;

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

update staging2.PTOBEN_PRF_smoke_test_block
set trx_record=0 where block_row_number in (1,2,3) or c4 is null;

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

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

/* blocks data loading start - PTOBEN_PRF_varying_speed_test_high_ambient_block */

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

 insert into staging2.PTOBEN_PRF_varying_speed_test_high_ambient_block
(
c2,
c3,
c4,
c5,
c6,
c7,
c8,
c9,
c10,
c11,
c12,
c13,
c14,
c15,
c16,
c17,
c18,
c19,
c20,
c21,
c22,
c23,
c24,
c25,
c26,
c27,
c28,
block_row_number
)
select column2,
column3,
column4,
column5,
column6,
column7,
column8,
column9,
column10,
column11,
column12,
column13,
column14,
column15,
column16,
column17,
column18,
column19,
column20,
column21,
column22,
column23,
column24,
column25,
column26,
column27,
column28,
block_row_number
from staging2.stg_process_table_PTOBEN_PRF 
where block_tag='PTOBEN_PRF_varying_speed_test_high_ambient';

update staging2.PTOBEN_PRF_varying_speed_test_high_ambient_block set trx_record=0 where
block_row_number in (1,2,3,4) or c4 is null;

update staging2.PTOBEN_PRF_varying_speed_test_high_ambient_block
set test_condition=c2 where block_row_number=1;

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

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

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

/* blocks data loading start - PTOBEN_PRF_2hrs_max_power_test_high_ambient_block */

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

 insert into staging2.PTOBEN_PRF_2hrs_max_power_test_high_ambient_block
(
c2,
c3,
c4,
c5,
c6,
c7,
c8,
c9,
c10,
c11,
c12,
c13,
c14,
c15,
c16,
c17,
c18,
c19,
c20,
c21,
c22,
c23,
c24,
c25,
c26,
c27,
c28,
block_row_number
)
select column2,
column3,
column4,
column5,
column6,
column7,
column8,
column9,
column10,
column11,
column12,
column13,
column14,
column15,
column16,
column17,
column18,
column19,
column20,
column21,
column22,
column23,
column24,
column25,
column26,
column27,
column28,
block_row_number
from staging2.stg_process_table_PTOBEN_PRF 
where block_tag='PTOBEN_PRF_2hrs_max_power_high_ambient';

update staging2.PTOBEN_PRF_2hrs_max_power_test_high_ambient_block set trx_record=0 where
block_row_number in (1,2,3,4) or c4 is null;

update staging2.PTOBEN_PRF_2hrs_max_power_test_high_ambient_block set trx_record=0 where (c3 is null or c3::numeric <> 0) and c2 = 'Avg';

update staging2.PTOBEN_PRF_2hrs_max_power_test_high_ambient_block
set test_condition=c2 where block_row_number=1;

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

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


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

/* blocks data loading start - PTOBEN_PRF_engine_oil_consumption_block */

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

 insert into staging2.PTOBEN_PRF_engine_oil_consumption_block
(
c2,
c3,c5,
block_row_number
)
select column2,column3,column5 ,block_row_number from staging2.stg_process_table_PTOBEN_PRF 
where block_tag='PTOBEN_PRF_engine_oil_consumption';

update staging2.PTOBEN_PRF_engine_oil_consumption_block set trx_record=0 where
block_row_number in (1,2) or c3 is null ;

 execute 'update staging2.PTOBEN_PRF_engine_oil_consumption_block set client_id='||p_client_id||'';
 execute 'update staging2.PTOBEN_PRF_engine_oil_consumption_block set function_id='||p_function_id||'';
 execute 'update staging2.PTOBEN_PRF_engine_oil_consumption_block set file_mnemonic='''||p_file_mnemonic||'''';
 execute 'update staging2.PTOBEN_PRF_engine_oil_consumption_block set file_sheet_mnemonic='''||p_file_sheet_mnemonic||'''';
  execute 'update staging2.PTOBEN_PRF_engine_oil_consumption_block set file_syspk='||p_file_syspk||'';
  
     update staging2.PTOBEN_PRF_engine_oil_consumption_block set make=__make; 
 update staging2.PTOBEN_PRF_engine_oil_consumption_block set model=__model;
perform fw_core.fn_jobctl_block_end(__file_syspk,'PTOBEN_PRF_engine_oil_consumption_block'); 

 err_context := '';
perform  fw_core.fn_insert_db_error ( __client_id, __function_id, 1001, 'Compegence', __file_syspk ,__file_mnemonic,__file_sheet_mnemonic ,null,'stg2', 'fn_PTO_PRF_block', err_state, err_msg, err_detail, err_hint, err_context,'success');
return err_context;	

EXCEPTION when OTHERS then 
	
GET STACKED DIAGNOSTICS 
       err_state   = returned_sqlstate,
       err_msg     = message_text,
       err_detail  = pg_exception_detail,
       err_hint    = pg_exception_hint,
       err_context = pg_exception_context;

   	   perform  fw_core.fn_insert_db_error ( __client_id, __function_id, 1001, 'Compegence', __file_syspk ,__file_mnemonic,__file_sheet_mnemonic ,null,'stg2', 'fn_PTO_PRF_block', err_state, err_msg, err_detail, err_hint, err_context,'error');
return err_context;
end
$$ LANGUAGE plpgsql;