--///////////////////////////////////////////////////////////////////////////////////
update TBNFE_PARCELAMENTO f set f.parcela = gen_id(GPARCELA,1);
  
--///////////////////////////////////////////////////////////////////////////////////  
select
	FN_GERAR_DOCUMENTO(f.observacao, f.empresa_codigo, 1) nfe_controle,
	f.observacao,
	f.CONTROLE

from TBNFE_PARCELAMENTO f

--///////////////////////////////////////////////////////////////////////////////////
update TBNFE_PARCELAMENTO f set f.numero_notafiscal = f.nfe_controle

--///////////////////////////////////////////////////////////////////////////////////		
select

    'UPDATE TBNFE_PARCELAMENTO SET PARCELA = FN_GERAR_PARCELA('''||a.nfe_controle||''','||iif(a.ISNUMBER2, iif(trim(a.PARCELA3) <> '',a.PARCELA3,a.linha), iif(trim(a.PARCELA2) <> '',a.PARCELA2,a.linha))||','||a.CONTROLE||') WHERE CONTROLE = '||a.CONTROLE||';' as SQL

from(
    select
        a.*,
        fn_isnumber(PARCELA2) as ISNUMBER1
    from(
    
        select
            a.*,
            fn_isnumber(PARCELA3) as ISNUMBER2,
            iif( position('/',PARCELA3) > 0, fn_array(PARCELA3,2,'/'),
                iif( position('.',PARCELA3) > 0, fn_array(PARCELA3,2,'.'),
                iif( position('S',PARCELA3) > 0, fn_array(PARCELA3,2,'S'),
                iif( position('|',PARCELA3) > 0, fn_array(PARCELA3,2,'|'),
                iif( position('N',PARCELA3) > 0, fn_array(PARCELA3,2,'N'),
                iif( position('E',PARCELA3) > 0, fn_array(PARCELA3,2,'E'),
            '')))))) as PARCELA2
        from(
        
            select
                f.nfe_controle,
                f.observacao,
                f.CONTROLE,
                f.PARCELA,
                row_number() over() as linha,
                iif(position('-', f.observacao) > 0, fn_array(f.observacao,2,'-'), f.observacao) as PARCELA3
            
            from TBNFE_PARCELAMENTO f
            --where f.observacao like 'AD012021%'
        
        ) a
    ) a
) a
--///////////////////////////////////////////////////////////////////////////////////

select
    --a.*,
    cast('UPDATE TBNFE_PARCELAMENTO s SET s.numero_notafiscal = '|| iif( ISNUMBER1 , DOC2,  iif(DOC3 = '', nfe_controle ,DOC3)) ||' WHERE CONTROLE = '||a.CONTROLE||';' as varchar(7000)) as SQL

from(
    select
        a.*,
        fn_isnumber(DOC2) as ISNUMBER1
    from(
    
        select
            a.*,
            FN_REPLACE(
            iif( position('/',DOC) > 0, fn_array(DOC,1,'/'),
                iif( position('.',DOC) > 0, fn_array(DOC,1,'.'),
                iif( position('S',DOC) > 0, fn_array(DOC,1,'S'),
                iif( position('|',DOC) > 0, fn_array(DOC,1,'|'),
                iif( position('N',DOC) > 0, fn_array(DOC,1,'N'),
                iif( position('E',DOC) > 0, fn_array(DOC,1,'E'),
            DOC))))))


        ,'A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|W|X|Y|Z|.|/|,|#|:|;|\|-', '') as DOC2,

        FN_REPLACE(DOC,'A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|W|X|Y|Z|.|/|,|#|:|;|\|-', '') as DOC3

        from(
        
            select
                f.nfe_controle,
                f.observacao,
                f.CONTROLE,
                f.PARCELA,
                row_number() over() as linha,
                upper(iif(position('-', f.observacao) > 0, fn_array(f.observacao,1,'-'), f.observacao)) as DOC
            
            from TBNFE_PARCELAMENTO f
            --where f.observacao like 'AD012021%'
        
        ) a
    ) a
) a
