Please convert below plsql procedure to snowflake sql procedure and based need work in snowflake .while running below query showing multiple errors

Question

Please convert below plsql procedure to snowflake sql procedure and based need work in snowflake .while running below query showing multiple errors

CREATE OR REPLACE PROCEDURE ucddbfix.ekyc_analysis_automation(IN p_countrycode character varying, IN p_batch_size bigint, IN p_sleep_time integer, IN p_rec_limit integer) LANGUAGE plpgsql AS $body$ DECLARE v_start BIGINT; v_end BIGINT; v_srl BIGINT; v_comments varchar :='' ; v_final_comments varchar:=null; v_cnt INT; v_id_type INT; v_debug_msg varchar; v_country_id int; v_vm_id int; v_cvmfv_id int; --ID Type primary key value from config table v_fv_cnt int; v_adv_cnt int; v_ckdvendor_cnt int; v_procname VARCHAR(30); v_loopcounter BIGINT; v_errmsg VARCHAR(999); v_max_srl INTEGER;

        rc_kyc_config record;
        lf1 record;
        lf2 record;
        lf3 record;
        lc_kyc_config cursor(c_country_id integer, c_veri_id integer,c_idtype_id integer) for
        with column_values_tab as 
        (select cvmf.field_id,cvmfv.extn_field_value
        ,count(1) over (partition by cvmf.field_id,cvmfv.field_value) cnt 
        from ucddbfix.di_country_verification_method cvm,
        ucddbfix.di_cvm_fields_config cvmf,
        (select cvmfe.cvmf_id,cvmfv.field_value,cvmfe.field_value extn_field_value,cvmfv.cvmfv_id
        from ucddbfix.di_cvm_fields_values cvmfv,
        ucddbfix.di_cvm_fields_values_extn cvmfe
        where cvmfv.cvmfv_id=cvmfe.cvmfv_id)cvmfv
        where 1=1
        and cvm.flag = 'Y'
        and cvmf.flag = 'Y'
        and cvm.country_id=c_country_id
        and cvm.verification_id=c_veri_id
        and cvm.cvm_id=cvmf.cvm_id
        and cvmf.cvmf_id=cvmfv.cvmf_id
        and cvmfv.cvmfv_id=c_idtype_id)
        select max((case when field_id=1 then field_value end )) verification_date,
        max((case when field_id=2 then field_value end )) verification_method,
        max((case when field_id=3 then field_value end )) id_number,
        max((case when field_id=4 then field_value end )) id_type,
        max((case when field_id=5 then field_value end )) id_issuing_country,
        max((case when field_id=6 then field_value end )) id_issuing_agency,
        max((case when field_id=7 then field_value end )) id_issuing_date,
        max((case when field_id=8 then field_value end )) id_expiry_date,
        max((case when field_id=9 then field_value end )) mtcn,
        max((case when field_id=10 then field_value end )) transactiondate,
        max((case when field_id=11 then field_value end )) doc_ref_number,
        max((case when field_id=12 then field_value end )) ref_number,
        max((case when field_id=13 then field_value end )) idvv_status,
        max((case when field_id=14 then field_value end )) idvv_status_date,
        max((case when field_id=15 then field_value end )) customer_kyc_details_seq,
        max((case when field_id=16 then field_value end )) id_method,
        max((case when field_id=17 then field_value end )) vendor,
        max((case when field_id=18 then field_value end )) verification_sources
        from (select fc.field_id,cvm.cnt::text||'-'||cvm.extn_field_value field_value 
        from ucddbfix.di_fields_config fc
        left outer join column_values_tab cvm on cvm.field_id=fc.field_id ) a; 
        lc_field_values cursor(c_country_id integer, c_veri_id integer,c_idtype_id integer,c_field_name varchar,c_field_value varchar) for
        select count(1) 
        from ucddbfix.di_fields_config fc,
        ucddbfix.di_cvm_fields_config cvmf,
        ucddbfix.di_country_verification_method cvm,
        ucddbfix.di_cvm_fields_values cvmfv,
        ucddbfix.di_cvm_fields_values_extn cvmfe
        where cvm.flag = 'Y'
        and cvmf.flag = 'Y'
        and fc.field_id=cvmf.field_id
        and cvm.cvm_id=cvmf.cvm_id
        and cvmfv.cvmfv_id=cvmfe.cvmfv_id
        and cvmf.cvmf_id=cvmfe.cvmf_id
        and cvm.country_id=c_country_id
        and cvm.verification_id=c_veri_id
        and cvmfv.cvmfv_id=c_idtype_id
        and fc.field_name=c_field_name
        and cvmfe.field_value=c_field_value;***strong text***
        lc_ckdvendor cursor(c_customer_id bigint,c_pcp_number varchar,c_id_type varchar) for
        select count(1) 
        from ucd.customer_kyc_vendors
        where customer_id = c_customer_id
        and pcp_number = c_pcp_number
        and id_type = c_id_type
        and channel_type = 1;
        BEGIN
        v_procname := p_countrycode||'EKYC_ANALYSIS_AUTOMATION';
        delete from ucdstg.iteration_tracking_table where procname=v_procname;
        truncate table ucddbfix.ekyc_verified_customers_temp;
        insert into ucddbfix.ekyc_verified_customers_temp
        (country_code,customer_id,pcp_number,verification_method,verification_date,srl)
        select country_code,customer_id,pcp_number,verification_method,verification_date,row_number() over() srl 
        from (  
            select distinct country_code,customer_id,pcp_number,verification_method,verification_date
            from ucddbfix.ekyc_verified_customers
            where (country_code=p_countrycode OR p_countrycode = 'ALL')
            --and ReportAnalysis_date is null
            order by country_code
            limit p_rec_limit
            ) src;
        --commit;
        perform pg_sleep(p_sleep_time);
        select COALESCE(max(srl),0) into v_max_srl from ucddbfix.ekyc_verified_customers_temp;--temp_kyc_di_analysis_stg; 
        v_loopcounter := v_max_srl;
        v_start := 1;
        v_end := (case when v_loopcounter<=p_batch_size then v_loopcounter else  p_batch_size end) ;
        IF NOT EXISTS (SELECT 1 FROM ucdstg.iteration_tracking_table WHERE procname=v_procname)
        THEN
            INSERT INTO ucdstg.iteration_tracking_table(procname,last_processed_record_number) VALUES(v_procname,0);
        END IF;
        WHILE (v_start<=v_loopcounter)
        LOOP
        update ucddbfix.ekyc_analysis_report t 
        set verification_method = 'COPY OF ID UPLOAD-OCR'
        from ucddbfix.ekyc_verified_customers_temp stg
        WHERE t.customer_id=stg.customer_id
        and t.pcp_number=stg.pcp_number
        and t.PCP_NUMBER IN ( 
        select distinct pcp_number FROM ucddbfix.ekyc_analysis_report 
          where verification_sources in ('ID OCR') 
          and vendor in ('Veryfi','VERYFI','Microblink','MICROBLINK')
          and verification_method = 'COPY OF ID UPLOAD'
        ) 
        and stg.srl between v_start and v_end;
        v_debug_msg:='Validating Country code: '||p_countrycode;
        raise notice '%', v_debug_msg;
        update ucddbfix.ekyc_analysis_report t
        set ucd_comments='Without ID&Evidence'
        from ucddbfix.ekyc_verified_customers_temp stg
        WHERE t.customer_id=stg.customer_id
        and t.pcp_number=stg.pcp_number
        and t.id_type is null
        and stg.srl between v_start and v_end;
        --commit;
        for lf1 in (select tg.pcp_number,tg.verification_method,tg.verification_date,tg.country_code 
                    from ucddbfix.ekyc_analysis_report tg,
        ucddbfix.ekyc_verified_customers_temp stg
        where  tg.pcp_number=stg.pcp_number
        and tg.customer_id=stg.customer_id
        and tg.id_type is not null)
        loop
        select country_id into v_country_id from ucddbfix.di_country_code cc where country_code=lf1.country_code;
          v_comments:='';
        v_debug_msg:='Validating VM for PCP: '||lf1.pcp_number||'-'||COALESCE(lf1.verification_method,'VM_NULL')||'-'||COALESCE(v_country_id,0) ;
        --raise notice '%', v_debug_msg;
        if lf1.verification_method is null then v_comments:= v_comments||'VM' ; end if ;
        if lf1.verification_date is null then v_comments:= v_comments||',VD' ; end if ;
          v_vm_id:=NULL;
        if lf1.verification_method is not null then 
        select vm.verification_id into v_vm_id from ucddbfix.di_verification_method vm, ucddbfix.di_country_verification_method cvm
        where cvm.flag = 'Y'
        and vm.verification_method=lf1.verification_method
        and cvm.country_id=v_country_id
        and cvm.verification_id=vm.verification_id;
        end if;
        for lf2 in (select * from ucddbfix.ekyc_analysis_report
        where pcp_number=lf1.pcp_number 
        and v_vm_id is not null 
        order by COALESCE(customer_kyc_details_seq,1))
        loop
        v_debug_msg:='Validating PCP at ID level:'||COALESCE(lf2.id_type,'ID_NULL');
        select cvmfv_id into v_cvmfv_id
        from ucddbfix.di_country_verification_method cvm,
        ucddbfix.di_cvm_fields_config cvmf,
        ucddbfix.di_cvm_fields_values cvmfv
        where 1=1
        and cvm.flag = 'Y'
        and cvmf.flag = 'Y'
        and cvm.cvm_id=cvmf.cvm_id
        and cvmf.cvmf_id=cvmfv.cvmf_id
        and cvmfv.field_value=lf2.id_type
        and cvm.country_id=v_country_id
        and cvm.verification_id=v_vm_id; 
        if v_cvmfv_id is null then 
        v_comments:= v_comments||',Invalid ID';
        else 
        open lc_kyc_config(v_country_id,v_vm_id,v_cvmfv_id);
        fetch lc_kyc_config into rc_kyc_config;
        close lc_kyc_config;
        v_debug_msg:='Validating ID Number';
        if rc_kyc_config.id_number is not null then
        if substr(rc_kyc_config.id_number,3)='NOT NULL' and lf2.id_number is not null then null ;
        elsif substr(rc_kyc_config.id_number,3)='NULL'  and lf2.id_number is null then null ;
        else v_comments:= v_comments||',IDNumber';
        end if;
        end if;
        v_debug_msg:='Validating id_issuing_country';
        if rc_kyc_config.id_issuing_country is not null then 
        if substr(rc_kyc_config.id_issuing_country,3)='NOT NULL' and lf2.id_issuing_country is not null then null;
        elsif substr(rc_kyc_config.id_issuing_country,3)='NULL' and lf2.id_issuing_country is null then null;
        elsif substr(rc_kyc_config.id_issuing_country,1,1)::integer=1 and lf2.id_issuing_country=substr(rc_kyc_config.id_issuing_country,3) then null ; 
        elsif substr(rc_kyc_config.id_issuing_country,1,1)::integer>1 then 
        open lc_field_values(v_country_id,v_vm_id,v_cvmfv_id,'id_issuing_country',lf2.id_issuing_country);
        fetch lc_field_values into v_fv_cnt;
        close lc_field_values;
        if v_fv_cnt>0 then null ; else v_comments:= v_comments||',ISC' ; end if;
        else v_comments:= v_comments||',ISC' ;
        end if;
        end if ;
        v_debug_msg:='Validating id_issuing_agency';
        if rc_kyc_config.id_issuing_agency is not null then 
        if substr(rc_kyc_config.id_issuing_agency,3)='NOT NULL' and lf2.id_issuing_agency is not null then null;
        elsif substr(rc_kyc_config.id_issuing_agency,3)='NULL' and lf2.id_issuing_agency is null then null;
        elsif substr(rc_kyc_config.id_issuing_agency,1,1)::integer=1 and lf2.id_issuing_agency=substr(rc_kyc_config.id_issuing_agency,3) then null;
        elsif substr(rc_kyc_config.id_issuing_agency,1,1)::integer>1 then 
        open lc_field_values(v_country_id,v_vm_id,v_cvmfv_id,'id_issuing_agency',lf2.id_issuing_agency) ;
        fetch lc_field_values into v_fv_cnt;
        close lc_field_values;
        if v_fv_cnt>0 then null ;else v_comments:= v_comments||',ISA'; end if;
        else v_comments:= v_comments||',ISA' ;
        end if;
        end if ;
        v_debug_msg:='Validating ID Issue Date';
        if rc_kyc_config.id_issuing_date is not null then
        if substr(rc_kyc_config.id_issuing_date,3)='NOT NULL' and lf2.id_issuing_date is not null then null;
        elsif substr(rc_kyc_config.id_issuing_date,3)='NULL'  and lf2.id_issuing_date is null then null;
        else v_comments:= v_comments||',ISD' ;
        end if;
        end if;
        v_debug_msg:='Validating ID Expiry Date';
        if rc_kyc_config.id_expiry_date is not null then
        if substr(rc_kyc_config.id_expiry_date,3)='NOT NULL' and lf2.id_expiry_date is not null then null ; 
        elsif substr(rc_kyc_config.id_expiry_date,3)='NULL'  and lf2.id_expiry_date is null then null;
        else v_comments:= v_comments||',IED' ;
        end if;
        end if;
        v_debug_msg:='Validating MTCN';
        if rc_kyc_config.mtcn is not null then
        if substr(rc_kyc_config.mtcn,3)='NOT NULL' and lf2.mtcn is not null then null;
        elsif substr(rc_kyc_config.mtcn,3)='NULL'  and lf2.mtcn is null then null;
        else v_comments:= v_comments||',MTCN';
        end if;
        end if;
        v_debug_msg:='Validating transactiondate';
        if rc_kyc_config.transactiondate is not null then
        if substr(rc_kyc_config.transactiondate,3)='NOT NULL' and lf2.transactiondate is not null then null;
        elsif substr(rc_kyc_config.transactiondate,3)='NULL'  and lf2.transactiondate is null then null;
        else v_comments:= v_comments||',TxnDT' ;
        end if;
        end if;
        v_debug_msg:='Validating doc_ref_number';
        if rc_kyc_config.doc_ref_number is not null then
        if substr(rc_kyc_config.doc_ref_number,3)='NOT NULL' and lf2.doc_ref_number is not null then null;
        elsif substr(rc_kyc_config.doc_ref_number,3)='NULL'  and lf2.doc_ref_number is null then null;
        elsif lf2.doc_ref_number like substr(rc_kyc_config.doc_ref_number,3)||'%' then null;
        else v_comments:= v_comments||',DRN' ;
        end if;
        end if;
        v_debug_msg:='Validating Ref number';
        if rc_kyc_config.ref_number is not null then
        if substr(rc_kyc_config.ref_number,3)='NOT NULL' and lf2.ref_number is not null then null;
        elsif substr(rc_kyc_config.ref_number,3)='NULL'  and lf2.ref_number is null then null;
        else v_comments:= v_comments||',RefNumber' ;
        end if;
        end if;
        v_debug_msg:='Validating IDVV status';
        if rc_kyc_config.idvv_status is not null then
        if substr(rc_kyc_config.idvv_status,3)='NOT NULL' and lf2.idvv_status is not null then null;
        elsif substr(rc_kyc_config.idvv_status,3)='NULL'  and lf2.idvv_status is null then null;
        elsif lf2.idvv_status=substr(rc_kyc_config.idvv_status,3) then null;
        else v_comments:= v_comments||',IDStatus' ;
        end if;
        end if;
        v_debug_msg:='Validating idvv_status_date';
        if rc_kyc_config.idvv_status_date is not null then
        if substr(rc_kyc_config.idvv_status_date,3)='NOT NULL' and lf2.idvv_status_date is not null then null;
        elsif substr(rc_kyc_config.idvv_status_date,3)='NULL'  and lf2.idvv_status_date is null then null; 
        else v_comments:= v_comments||',IDVVDt' ;
        end if;
        end if;
        v_debug_msg:='Validating customer_kyc_details_seq';
        if rc_kyc_config.customer_kyc_details_seq is not null then
        if substr(rc_kyc_config.customer_kyc_details_seq,1,1)::integer=1 and lf2.customer_kyc_details_seq=substr(rc_kyc_config.customer_kyc_details_seq,3)::numeric then null ;
        elsif substr(rc_kyc_config.customer_kyc_details_seq,1,1)::integer>1 then 
        open lc_field_values(v_country_id,v_vm_id,v_cvmfv_id,'customer_kyc_details_seq',lf2.customer_kyc_details_seq) ;
        fetch lc_field_values into v_fv_cnt;
        close lc_field_values;
        if v_fv_cnt>0 then null ; else v_comments:= v_comments||',KYCSeq';  end if;
        else v_comments:= v_comments||',KYCSeq' ; end if;
        end if;
        v_debug_msg:='Validating ID Method';
        if rc_kyc_config.id_method is not null then
        if lf2.id_method=substr(rc_kyc_config.id_method,3) then null ;
        elsif substr(rc_kyc_config.id_method,3)='NULL'  and lf2.id_method is null then null;
        elsif substr(rc_kyc_config.id_method,3)='NOT NULL' and lf2.id_method is not null then null;
        else v_comments:= v_comments||',IDMethod' ;
        end if;
        end if;
        v_debug_msg:='Validating vendor';
        if rc_kyc_config.vendor is not null then
        if lf2.vendor=substr(rc_kyc_config.vendor,3) then null ;
        elsif substr(rc_kyc_config.vendor,3)='NULL'  and lf2.vendor is null then null;
        elsif substr(rc_kyc_config.vendor,3)='NOT NULL' and lf2.vendor is not null then null;
        elsif substr(rc_kyc_config.vendor,3)='CKDVENDOR' then
        open lc_ckdvendor(lf2.customer_id,lf2.pcp_number,lf2.id_type);
        fetch lc_ckdvendor into v_ckdvendor_cnt;
        close lc_ckdvendor;
        if v_ckdvendor_cnt>0 then null; else v_comments:= v_comments||',Vendor';  end if;
        else v_comments:= v_comments||',Vendor' ; end if;
        end if;
        v_debug_msg:='Validating verification sources';
        if rc_kyc_config.verification_sources is not null then
        if lf2.verification_sources=substr(rc_kyc_config.verification_sources,3) then null ;
        elsif substr(rc_kyc_config.verification_sources,3)='NULL'  and lf2.verification_sources is null then null;
        elsif substr(rc_kyc_config.verification_sources,3)='NOT NULL' and lf2.verification_sources is not null then null;
        elsif substr(rc_kyc_config.verification_sources,3)='CKDVENDOR' then
        open lc_ckdvendor(lf2.customer_id,lf2.pcp_number,lf2.id_type);
        fetch lc_ckdvendor into v_ckdvendor_cnt;
        close lc_ckdvendor;
        if v_ckdvendor_cnt>0 then null; else v_comments:= v_comments||',VS';  end if;
        else v_comments:= v_comments||',VS' ; end if;
        end if;
        for lf3 in (select di_condition,error_msg
        from ucddbfix.di_cvm_additional_logic cl,
        ucddbfix.di_country_verification_method cvm,
        ucddbfix.di_cvm_fields_values cvmfv
        where cvm.flag = 'Y'
        and cl.cvm_id=cvm.cvm_id
        and cl.cvmfv_id=cvmfv.cvmfv_id
        and cvm.country_id=v_country_id
        and cvm.verification_id=v_vm_id
        and cvmfv.cvmfv_id=v_cvmfv_id)
        loop
        EXECUTE 'select count(1) cnt from ucddbfix.ekyc_analysis_report where pcp_number=$1 and id_type=$2 and '||lf3.di_condition 
        into v_adv_cnt using lf2.pcp_number,lf2.id_type ;
        if v_adv_cnt>0 then null ; else v_comments:= v_comments||','||lf3.error_msg ; end if;
        end loop;
        end if ;
        if LENGTH(trim(v_comments)) <= 0 then
        update ucddbfix.ekyc_analysis_report rt
        set UCD_Comments=coalesce(lf2.customer_kyc_details_seq,0)||'-'||'Valid'
        ---,ReportAnalysis_date=cast (clock_timestamp() as timestamp(6) without time zone)
        where rt.pcp_number=lf2.pcp_number 
        and coalesce(rt.id_type,'')=coalesce(lf2.id_type,'');
        else
        update ucddbfix.ekyc_analysis_report rt
        set UCD_Comments=coalesce(lf2.customer_kyc_details_seq,0)||'-'||v_comments
        --,ReportAnalysis_date=cast (clock_timestamp() as timestamp(6) without time zone)
        where rt.pcp_number=lf2.pcp_number 
        and coalesce(rt.id_type,'')=coalesce(lf2.id_type,'');
        end if;
        v_comments:='';
        --commit;
        end loop;
        end loop;
        update  ucddbfix.ekyc_analysis_report stg
        set final_ucd_comments=stg1.final_comments, Report_createddate=stg1.Report_createddate
        from (select a.pcp_number, trim(STRING_AGG(a.ucd_comments, '|' ORDER BY COALESCE(customer_kyc_details_seq,1),ucd_comments)) final_comments ,
        a.Report_createddate
        from ucddbfix.ekyc_analysis_report a, ucddbfix.ekyc_verified_customers_temp b
        where  a.pcp_number=b.pcp_number
        and a.customer_id=b.customer_id and b.srl between v_start and v_end
        group by a.pcp_number,a.Report_createddate) stg1
        where stg.pcp_number=stg1.pcp_number ;
        INSERT INTO ucddbfix.ekyc_analysis_report_history
        (   customer_id,pcp_number,country_code,verified_by,verification_date,verification_method,operator_id,calc_verification_date,id_number,id_type,id_issuing_country,id_issuing_agency,id_issuing_date,id_expiry_date,mtcn,transactiondate,doc_ref_number,doc_updated_date,ref_number,idvv_status,idvv_status_date,customer_kyc_details_seq,created_date,created_by,id_method,vendor,verification_sources,no_of_ids,id_order,idtype_comb,vendor_comb,verification_sources_comb,mtcn_present,txn_date_present,category,id_method_comb,doc_ref_present,vendor_ref_present,ucd_comments,final_ucd_comments,report_createddate
        )
        SELECT DISTINCT a.customer_id,a.pcp_number,a.country_code,a.verified_by,a.verification_date,a.verification_method,
        a.operator_id,a.calc_verification_date,a.id_number,a.id_type,a.id_issuing_country,a.id_issuing_agency,a.id_issuing_date,
        a.id_expiry_date,a.mtcn,a.transactiondate,a.doc_ref_number,a.doc_updated_date,a.ref_number,a.idvv_status,
        a.idvv_status_date,a.customer_kyc_details_seq,a.created_date,a.created_by,a.id_method,a.vendor,a.verification_sources,
        a.no_of_ids,a.id_order,a.idtype_comb,a.vendor_comb,a.verification_sources_comb,a.mtcn_present,
        a.txn_date_present,a.category,a.id_method_comb,a.doc_ref_present,a.vendor_ref_present,a.ucd_comments,
        a.final_ucd_comments,a.report_createddate
        FROM ucddbfix.ekyc_analysis_report a,ucddbfix.ekyc_verified_customers_temp b
        where  a.pcp_number=b.pcp_number
        and a.customer_id=b.customer_id and b.srl between v_start and v_end;
        insert into ucddbfix.ekyc_fix_tracking_table( stage,verification_method,country_code,report_createddate,updated_by,updated_date)
        values ('ANALYSIS','ALL',p_countrycode,cast (clock_timestamp() as timestamp(6) without time zone),'SYSTEM',now());
        update ucdstg.iteration_tracking_table
            set last_processed_record_number = COALESCE(last_processed_record_number,0)+((case when v_end<=v_loopcounter then v_end else  v_loopcounter end)-v_start+1)
            where procname = v_procname;
             
        --  commit;
            v_start    := v_end+1;
            v_end      := v_end+p_batch_size;
            perform pg_sleep(p_sleep_time);
        end loop;
        EXCEPTION 
           when others then 
        RAISE NOTICE 'exception: % % ', sqlstate ,  sqlerrm ;
        raise exception 'exception: % % ', sqlstate ,  sqlerrm ;  
        END;
        $body$
        ;
        COMMIT;