drop function if exists  mmt_ods.fn_update_NA();
create or replace function mmt_ods.fn_update_NA()
   returns void   AS $$
    /***********
    select mmt_ods.fn_update_NA()
    ********/
declare 
 __n integer:=0;
 __na_sql text;
 __space_sql text;
 __div_sql text;
 __ref_sql text;
 __hyphen_sql text;
__not_applicable text;
__NR text;
begin
	loop 
		exit when __n=53;
	    __n := __n+1;
	   __na_sql := 'update mmt_staging1.mmt_staging_generic_table set '|| concat('column',__n)||' =null
 where  '||concat('column',__n)||'=''NA''';
	   __space_sql := 'update  mmt_staging1.mmt_staging_generic_table set '|| concat('column',__n)||' =null
 where  '||concat('column',__n)||'=''''';
	   __div_sql := 'update  mmt_staging1.mmt_staging_generic_table set '|| concat('column',__n)||' =null
 where  '||concat('column',__n)||'=''#REF!''';
	   __ref_sql := 'update  mmt_staging1.mmt_staging_generic_table set '|| concat('column',__n)||' =null
 where  '||concat('column',__n)||'=''#DIV/0!''';
      __hyphen_sql := 'update  mmt_staging1.mmt_staging_generic_table set '|| concat('column',__n)||' =null
 where  '||concat('column',__n)||'=''-''';
      __not_applicable := 'update  mmt_staging1.mmt_staging_generic_table set '|| concat('column',__n)||' =null
 where  '||concat('column',__n)||'=''Not Applicable ''';
      __NR := 'update  mmt_staging1.mmt_staging_generic_table set '|| concat('column',__n)||' =null
 where  '||concat('column',__n)||'=''NR''';
	  execute  __na_sql;
	  execute  __space_sql;
	  execute  __div_sql;
	  execute  __ref_sql;
	  execute  __hyphen_sql;
	  execute  __not_applicable;
	  execute  __NR;
	end loop;	
end; 
$$ LANGUAGE plpgsql;




drop function if exists mmt_staging2.fn_jobctrl_block_begin;
CREATE OR REPLACE FUNCTION mmt_staging2.fn_jobctrl_block_begin(p_client_id int,p_function_id int,p_file_syspk int,p_block_table_name text,p_file_format text,p_sheet_mnemonic text,p_block_seq int)
   RETURNS void AS $$
begin

/************************* 
select mmt_staging2.fn_jobctrl_block_begin(1,2,255,'block1','file','sheet',1) 
***********************/

execute 'insert into mmt_ods.fw_jobctrl_file_sheet_block_run_schedule
(client_id,function_id,file_syspk,block_table_name,file_format,sheet_mnemonic,begin_status,block_table_load_seq,start_time)
 select '||p_client_id||', '||p_function_id||', '''||p_file_syspk||''','''||p_block_table_name||''','''||p_file_format||''','''||p_sheet_mnemonic||''',1,'||p_block_seq||',current_timestamp';
end
$$ LANGUAGE plpgsql;


drop function if exists mmt_staging2.fn_jobctrl_block_end;
CREATE OR REPLACE FUNCTION mmt_staging2.fn_jobctrl_block_end(p_file_syspk int,p_block_table_name text)
   RETURNS void AS $$
begin

/********************************* 
select mmt_staging2.fn_jobctrl_block_end(255,'block1')
*********************************/

execute 'update mmt_ods.fw_jobctrl_file_sheet_block_run_schedule set end_status=1 , end_time=current_timestamp
where file_syspk='''||p_file_syspk||'''and block_table_name='''||p_block_table_name||'''';

end
$$ LANGUAGE plpgsql;