CREATE OR REPLACE TRIGGER LOG_FIN_TRANSFB
AFTER DELETE OR INSERT OR UPDATE
ON ITTRANSFBANC FOR EACH ROW
DECLARE
  V_DSREMOTEADDR VARCHAR2(15);
  V_DSREMOTEHOST VARCHAR2(15);
  V_DSSERVERRDM  VARCHAR2(20);
  V_DSSERVMETHOD VARCHAR2(30);
  V_DSREMOTEOPER VARCHAR2(12);
  V_CDFILLOGOPER VARCHAR2(4);
  V_USUARIO     VARCHAR2(40);
  V_TIPO        VARCHAR2(1);
  V_ITPESQ      VARCHAR2(40);
  V_DESC        VARCHAR2(500);
  V_DTOPER      DATE;
  V_CDCLASSFINA VARCHAR2(6);
  V_CDFILIAL    VARCHAR2(4);
  V_DTMOVFLUXO  VARCHAR2(10);
  V_IDMOVFLUXO  VARCHAR2(1);
  V_DTTRANBANC  VARCHAR2(10);
  V_VRTRANBANC  VARCHAR2(15);
BEGIN
   IF DELETING THEN
      V_TIPO    := '3';
      SELECT MAX(CDCLASSFINA),
             MAX(CDFILIAL),
             SUBSTR(TO_CHAR(MAX(DTMOVFLUXO), 'DD/MM/YYYY'),1, 10),
             MAX(IDMOVFLUXO),
             SUBSTR(TO_CHAR(MAX(DTTRANBANC), 'DD/MM/YYYY'),1, 10),
             SUBSTR(TO_CHAR(MAX(VRTRANBANC)), 15)
      INTO
         V_CDCLASSFINA,
         V_CDFILIAL,
         V_DTMOVFLUXO,
         V_IDMOVFLUXO,
         V_DTTRANBANC,
         V_VRTRANBANC

      FROM TRANSFBANC
      WHERE
         CDEMPRESA  = :OLD.CDEMPRESA AND
         NRTRANBANC = :OLD.NRTRANBANC;

      V_DESC   := 'Dt. Transf. '    || V_DTTRANBANC              ||
                  ' Valor Transf. ' || TO_CHAR(:OLD.VRMOVITBANC) ||
                  ' ContCC '        || :OLD.CDREDUCONT           ||
                  ' Empresa '       || :OLD.CDEMPRESA            ||
                  ' Tipo Transf '   || :OLD.IDMOVITBANC          ||
                  ' Cheque '        || :OLD.NRCHEQUE             ||
                  ' NrMovBanc '     || :OLD.NRMOVBANC            ||
                  ' Classf '        || V_CDCLASSFINA             ||
                  ' FiliFx '        || V_CDFILIAL                ||
                  ' DtFx'           || V_DTMOVFLUXO              ||
                  ' IdTpFx '        || V_IDMOVFLUXO              ||
                  ' VrTotTransf '   || V_VRTRANBANC;

      V_ITPESQ  := :OLD.CDEMPRESA||:OLD.NRTRANBANC||:OLD.CDREDUCONT|| '/' ||:OLD.NRMOVBANC;
   ELSE
      IF UPDATING THEN
         V_ITPESQ := :OLD.CDEMPRESA||:OLD.NRTRANBANC||:OLD.CDREDUCONT|| '/' ||:OLD.NRMOVBANC;

         IF (:NEW.VRMOVITBANC <> :OLD.VRMOVITBANC) OR
            (:NEW.CDREDUCONT  <> :OLD.CDREDUCONT)  OR
            (:NEW.CDEMPRESA   <> :OLD.CDEMPRESA)   OR
            (:NEW.IDMOVITBANC <> :OLD.IDMOVITBANC) OR
            (:NEW.NRCHEQUE    <> :OLD.NRCHEQUE)    THEN
            V_TIPO   := '2';
         END IF;
      ELSE
         V_TIPO   := '1';
         V_ITPESQ := :NEW.CDEMPRESA||:NEW.NRTRANBANC||:NEW.CDREDUCONT|| '/' ||:NEW.NRMOVBANC;
      END IF;

      SELECT MAX(CDCLASSFINA),
             MAX(CDFILIAL),
             SUBSTR(TO_CHAR(MAX(DTMOVFLUXO), 'DD/MM/YYYY'),1, 10),
             MAX(IDMOVFLUXO),
             SUBSTR(TO_CHAR(MAX(DTTRANBANC), 'DD/MM/YYYY'),1, 10),
             SUBSTR(TO_CHAR(MAX(VRTRANBANC)), 15)
      INTO
         V_CDCLASSFINA,
         V_CDFILIAL,
         V_DTMOVFLUXO,
         V_IDMOVFLUXO,
         V_DTTRANBANC,
         V_VRTRANBANC
      FROM TRANSFBANC
      WHERE
         CDEMPRESA  = :NEW.CDEMPRESA AND
         NRTRANBANC = :NEW.NRTRANBANC;

      V_DESC   := 'Dt. Transf. '    || V_DTTRANBANC              ||
            ' Valor Transf. ' || TO_CHAR(:NEW.VRMOVITBANC) ||
            ' ContCC '        || :NEW.CDREDUCONT           ||
            ' Empresa '       || :NEW.CDEMPRESA            ||
            ' Tipo Transf '   || :NEW.IDMOVITBANC          ||
            ' Cheque '        || :NEW.NRCHEQUE             ||
            ' NrMovBanc '     || :NEW.NRMOVBANC            ||
            ' Classf '        || V_CDCLASSFINA             ||
            ' FiliFx '        || V_CDFILIAL                ||
            ' DtFx'           || V_DTMOVFLUXO              ||
            ' IdTpFx '        || V_IDMOVFLUXO              ||
            ' VrTotTransf '   || V_VRTRANBANC;

   END IF;

   IF V_TIPO <> '0' THEN
      NMUSUARIO(V_USUARIO);
      WEBTUNNING(V_DSREMOTEADDR,
                 V_DSREMOTEHOST,
                 V_DSSERVERRDM,
                 V_DSSERVMETHOD,
                 V_DSREMOTEOPER);
      IF NOT V_DESC IS NULL THEN
         IF NOT V_USUARIO IS NULL THEN
            V_DTOPER := SYSDATE;

            INSERT INTO LOGOPERA
               (NMTABELA,   DTOPERACAO, NMRESPOPER,
                IDTIPOOPER, CDPESQITEM, DSOPERACAO,
                DSRDMUSUOPER, DSENDUSUOPER, DSNMUSUOPER,
                DSMETUSUOPER, CDORESPOPER, CDFILLOGOPER)
            VALUES
               ('ITTRANSFBANC', V_DTOPER, V_USUARIO,
                V_TIPO,         V_ITPESQ, SUBSTR(V_DESC,1, 300),
                V_DSSERVERRDM, V_DSREMOTEADDR, V_DSREMOTEHOST,
                V_DSSERVMETHOD, V_DSREMOTEOPER, V_CDFILLOGOPER);
         END IF;
      END IF;
   END IF;
END;
/