create or replace TRIGGER REPLICA_INTERLOCUTOR_INTEPROSP
AFTER INSERT OR UPDATE
ON CRM_INTERLOCUTOR
FOR EACH ROW
DECLARE
  V_CDPROSPECT_RELACIONADO VARCHAR2(50);
  V_NOVO_CODIGO_INTEPROSP VARCHAR2(2);
  V_CARGO_INTERLOCUTOR VARCHAR2(50);
  V_BAIRRO_INTERLOCUTOR VARCHAR2(100);
BEGIN
    BEGIN
      SELECT CDPROSPECT
      INTO V_CDPROSPECT_RELACIONADO
      FROM PROSPECT
      WHERE NRPARCNEGOCIO = :NEW.NRPARCNEGOCIO
      AND NRORG = :NEW.NRORG;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
        V_CDPROSPECT_RELACIONADO  := NULL;
    END;
  IF V_CDPROSPECT_RELACIONADO IS NOT NULL THEN
    SELECT NVL(MAX(TO_NUMBER(CDINTEPROS)) + 1, '01')
    INTO V_NOVO_CODIGO_INTEPROSP
    FROM INTEPROSP
    WHERE CDPROSPECT = V_CDPROSPECT_RELACIONADO
    AND NRORG = :NEW.NRORG;
    BEGIN
      SELECT NMCARGOINTERLOC
      INTO V_CARGO_INTERLOCUTOR
      FROM CARGOINTERLOC
      WHERE CDCARGOINTERLOC = :NEW.CDCARGOINTERLOC
      AND NRORG = :NEW.NRORG;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          V_CARGO_INTERLOCUTOR  := '';
    END;
    BEGIN
      SELECT NMBAIRRO
      INTO V_BAIRRO_INTERLOCUTOR
      FROM BAIRRO
      WHERE CDPAIS = :NEW.CDPAIS
      AND SGESTADO = :NEW.SGESTADO
      AND CDMUNICIPIO = :NEW.CDMUNICIPIO
      AND CDBAIRRO = :NEW.CDBAIRRO
      AND NRORG = :NEW.NRORG;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          V_BAIRRO_INTERLOCUTOR  := '';
    END;
    IF INSERTING THEN
        INSERT INTO INTEPROSP (CDPROSPECT, CDINTEPROS, NRSEQINTERLOCUTOR, NMINTEPROS, CDCARGOINTERLOC, DSCARGINPROS, IDINPROSRECPRES, CDPAIS, SGESTADO, CDMUNICIPIO,
        NMBAIRINPROS, NRCEPINPROS, DSENDEINPROS, IDTPINTEPROS, DTNASCINPROS, DSEMAILIPROS, NRTEL1INPROS, NRTEL2INPROS, DTINCLUSAO)
        VALUES(V_CDPROSPECT_RELACIONADO, V_NOVO_CODIGO_INTEPROSP, :NEW.NRSEQINTERLOCUTOR, :NEW.NMINTERLOCUTOR, :NEW.CDCARGOINTERLOC, V_CARGO_INTERLOCUTOR, NVL(:NEW.IDRECEBEPRESENTE, 'N'),
        :NEW.CDPAIS, :NEW.SGESTADO, :NEW.CDMUNICIPIO, V_BAIRRO_INTERLOCUTOR, :NEW.NRCEPINTERLOCUTOR, :NEW.DSENDEINTERLOCUTOR, 'O', :NEW.DTNASCIMENTO, :NEW.EMAILINTERLOC, :NEW.NRTELEFONE1,
        :NEW.NRTELEFONE2, SYSDATE);
    ELSE
      UPDATE INTEPROSP
      SET NMINTEPROS = :NEW.NMINTERLOCUTOR, CDCARGOINTERLOC = :NEW.CDCARGOINTERLOC, DSCARGINPROS = V_CARGO_INTERLOCUTOR, CDPAIS = :NEW.CDPAIS, SGESTADO = :NEW.SGESTADO,
      CDMUNICIPIO = :NEW.CDMUNICIPIO, NMBAIRINPROS = V_BAIRRO_INTERLOCUTOR, NRCEPINPROS = :NEW.NRCEPINTERLOCUTOR, DSENDEINPROS = :NEW.DSENDEINTERLOCUTOR,
      IDINPROSRECPRES = NVL(:NEW.IDRECEBEPRESENTE, 'N'), DTNASCINPROS = :NEW.DTNASCIMENTO, DSEMAILIPROS = :NEW.EMAILINTERLOC,
      NRTEL1INPROS = :NEW.NRTELEFONE1, NRTEL2INPROS = :NEW.NRTELEFONE2, DTULTATU = SYSDATE
      WHERE NRSEQINTERLOCUTOR = :OLD.NRSEQINTERLOCUTOR;
    END IF;
  END IF;
END;