CREATE OR ALTER procedure spi_notificar (
    hora timestamp,
    usuario_id varchar(500),
    USUARIO varchar(500),
    assunto varchar(500),
    corpo blob sub_type 1 segment size 80,
    email integer = 1,
    notificacao integer = 1)
as
declare variable email_id integer;
declare variable destinatario varchar(100);
BEGIN

    SELECT FIRST 1 U.EMAIL, iif(char_length(u.nome) > 0, u.nome, u.usuario) FROM TBUSUARIO U WHERE U.CODIGO = :USUARIO_ID
    into :DESTINATARIO, :USUARIO;

    USUARIO = coalesce(USUARIO, '');

    IF ( NOTIFICACAO = 1 ) THEN
    BEGIN

        INSERT INTO TBNOTIFICACAO (TIPO, USUARIO_ID, TODOS, MENU_ID, TITULO, LEITURA, ENVIO, DATA_HORA, EMITENTE, AGENDAMENTO, EXECUTADO, TABELA, TABELA_ID, MENSAGEM)
           VALUES (
            1,
            :USUARIO_ID,
            0,
            0,
            COALESCE(:ASSUNTO,'NOVO EMAIL'),
            0,
            0,
            :HORA,
            :USUARIO_ID,
            :HORA,
            0,
            '',
            0,
            COALESCE(:CORPO,'')
        );

    END

    IF ( EMAIL = 1 ) THEN
    BEGIN

        EMAIL_ID = (SELECT GEN_ID(GTBEMAIL,1) FROM RDB$DATABASE);

        if(:DESTINATARIO is null)then
        begin
          exception "..." ''||USUARIO||' sem email para notificação.';
        end

        INSERT INTO TBEMAIL (
            ID,
            EMAIL,
            USUARIO_ID,
            ASSUNTO,
            CORPO,
            STATUS,
            DATAHORA
        ) VALUES (
            :EMAIL_ID,
            :DESTINATARIO,
            0,
            COALESCE(:ASSUNTO,'NOVO EMAIL'),
            COALESCE(:CORPO,''),
            0,
            :HORA
        );
    END
END;

