puttipolocal(integer, bigint, character varying, character varying, character varying)

begin

    -- VALIDACAO    

        -- EXTERNALID E INTERNALID NAO INFORMADOS

        if coalesce(PutTipoLocal.externalid,'')='' and PutTipoLocal.internalid is null then 

        RAISE EXCEPTION 'externalid = % and internalid = %', PutTipoLocal.externalid, PutTipoLocal.internalid

            USING HINT = 'Fields internalid and externalid must be not null';

        end if;     

​

        --IDNATIVO FORA DO PADRAO 

        if idnativo not in (0,1,-99999) then 

            RAISE EXCEPTION 'idnativo = %', idnativo

                USING HINT = 'Field must not be null or different from 1 or 0.';

        end if;  

        --DESCRICAO NULA

        if descricao is null then 

            RAISE EXCEPTION 'descricao = %', descricao

                USING HINT = 'Field descricao must be not null';

        end if;

        --SGLTIPOLOCAL NULA

        if sgltipolocal is null then 

            RAISE EXCEPTION 'sgltipolocal = %', sgltipolocal

                USING HINT = 'Field sgltipolocal must be not null';

        end if;  

​

    -- LOOKUP

        -- TIPO LOCAL

        IF PutTipoLocal.internalid IS NULL 

        THEN PutTipoLocal.internalid = (SELECT IDTIPOLOCAL FROM TIPOLOCAL WHERE CODIGOERP = PutTipoLocal.externalid);

        END IF;

​

    -- UPDATE

    update tipolocal set 

    descricao = case when PutTipoLocal.descricao = 'NOVALUEWS' then tipolocal.descricao else PutTipoLocal.descricao end, 

    sgltipolocal = case when PutTipoLocal.sgltipolocal = 'NOVALUEWS' then tipolocal.sgltipolocal else PutTipoLocal.sgltipolocal end, 

    idnativo = case when PutTipoLocal.idnativo = -99999 then tipolocal.idnativo else PutTipoLocal.idnativo end,

        codigoerp = case when PutTipoLocal.externalid = 'NOVALUEWS' then codigoerp else PutTipoLocal.externalid end

    where idtipolocal = internalid;

​

    return query select internalid;

END;