create or replace TRIGGER LOG_CDP_LOTITRETIEST AFTER
    DELETE OR INSERT OR UPDATE ON LOTITRETIEST
    FOR EACH ROW

DECLARE
    V_USUARIO           VARCHAR2(40);
    V_TIPO              VARCHAR2(1);
    V_ITPESQ            VARCHAR2(40);
    V_DESC              VARCHAR2(4000);
    V_CDFILLOGOPER      VARCHAR2(4);
    V_DSREMOTEADDR      VARCHAR2(15);
    V_DSREMOTEHOST      VARCHAR2(15);
    V_DSSERVERRDM       VARCHAR2(20);
    V_DSSERVMETHOD      VARCHAR2(30);
    V_DSREMOTEOPER      VARCHAR2(12);
    V_DTOPER            DATE;
    V_TAM               INTEGER;
    V_QTDE_ITEMRETIEST  FLOAT;
BEGIN

    V_TIPO         := '0';
    V_DESC         := '';
    V_CDFILLOGOPER := '';

    IF DELETING THEN
        IF :OLD.NRLOTEESTQ IS NOT NULL THEN
            V_DESC := V_DESC|| ' Lote='|| :OLD.NRLOTEESTQ;
        END IF;

        IF :OLD.NRSUBLOTE IS NOT NULL THEN
            V_DESC := V_DESC|| ' SubLote='|| :OLD.NRSUBLOTE;
        END IF;

        IF :OLD.CDALMOXARIFE IS NOT NULL THEN
            V_DESC := V_DESC || ' Almox='|| :OLD.CDALMOXARIFE;
        END IF;

        IF :OLD.CDLOCALESTOQ IS NOT NULL THEN
            V_DESC := V_DESC || ' Local='|| :OLD.CDLOCALESTOQ;
        END IF;

        IF :OLD.IDTPRETEST IS NOT NULL THEN
            V_DESC := V_DESC || ' Id.Reti='|| :OLD.IDTPRETEST;
        END IF;

        IF :OLD.NRSEQRETLOTE IS NOT NULL THEN
            V_DESC := V_DESC|| ' Seq='|| :OLD.NRSEQRETLOTE;
        END IF;

        IF :OLD.NRLANCESTQ IS NOT NULL THEN
            V_DESC := V_DESC|| ' Lanc.Estq='|| :OLD.NRLANCESTQ;
        END IF;

        IF :OLD.NRSEQUITEM IS NOT NULL THEN
            V_DESC := V_DESC|| ' Seq.Item='|| :OLD.NRSEQUITEM;
        END IF;

        IF :OLD.QTDEVRETPROD IS NOT NULL THEN
            V_DESC := V_DESC|| ' Qt.Dev='|| :OLD.QTDEVRETPROD;
        END IF;

        IF :OLD.QTEXTRETPROD IS NOT NULL THEN
            V_DESC := V_DESC|| ' Qt.Ext='|| :OLD.QTEXTRETPROD;
        END IF;

        IF :OLD.QTPERRETPROD IS NOT NULL THEN
            V_DESC := V_DESC|| ' Qt.Per='|| :OLD.QTPERRETPROD;
        END IF;

        IF :OLD.QTREJRETPROD IS NOT NULL THEN
            V_DESC := V_DESC||' Qt.Retirada: '|| :OLD.QTREJRETPROD;
        END IF;

        IF :OLD.QTRETPROD IS NOT NULL THEN
            V_DESC := V_DESC || ' Qtde.Prevista='|| :OLD.QTRETPROD;
        END IF;

        IF :OLD.QTRETLIQPROD IS NOT NULL THEN
            V_DESC := V_DESC || ' Qt. Movimentada='|| :OLD.QTRETLIQPROD;
        END IF;

        IF :OLD.DTLANCRETDEV IS NOT NULL THEN
            V_DESC := V_DESC|| ' Dt.Ret='|| :OLD.DTLANCRETDEV;
        END IF;

        V_TIPO         := '3';
        V_ITPESQ       := :OLD.CDFILIAL|| :OLD.NRRETEST|| :OLD.NRSEQRETPROD|| :OLD.CDPRODUTO;
        V_CDFILLOGOPER := :OLD.CDFILIAL;
    ELSE
        IF UPDATING THEN

            IF NVL(:NEW.NRLOTEESTQ, '!') <> NVL(:OLD.NRLOTEESTQ, '!') THEN
                V_DESC := V_DESC|| ' Lote='|| :NEW.NRLOTEESTQ;
            END IF;

            IF NVL(:NEW.NRSUBLOTE, '!') <> NVL(:OLD.NRSUBLOTE, '!') THEN
                V_DESC := V_DESC|| ' SubLote='|| :NEW.NRSUBLOTE;
            END IF;

            IF NVL(:NEW.CDALMOXARIFE, '!') <> NVL(:OLD.CDALMOXARIFE, '!') THEN
                V_DESC := V_DESC|| ' Almox='|| :NEW.CDALMOXARIFE;
            END IF;

            IF NVL(:NEW.CDLOCALESTOQ, '!') <> NVL(:OLD.CDLOCALESTOQ, '!') THEN
                V_DESC := V_DESC|| ' Local='|| :NEW.CDLOCALESTOQ;
            END IF;

            IF NVL(:NEW.IDTPRETEST, '!') <> NVL(:OLD.IDTPRETEST, '!') THEN
                V_DESC := V_DESC|| ' Id.Reti='|| :NEW.IDTPRETEST;
            END IF;

            IF NVL(:NEW.NRSEQRETLOTE, '!') <> NVL(:OLD.NRSEQRETLOTE, '!') THEN
                V_DESC := V_DESC|| ' Seq='|| :NEW.NRSEQRETLOTE;
            END IF;

            IF NVL(:NEW.NRLANCESTQ, '!') <> NVL(:OLD.NRLANCESTQ, '!') THEN
                V_DESC := V_DESC || ' Lanc.Estq='|| :NEW.NRLANCESTQ;
            END IF;

            IF NVL(:NEW.NRSEQUITEM, '!') <> NVL(:OLD.NRSEQUITEM, '!') THEN
                V_DESC := V_DESC|| ' Seq.Item='|| :NEW.NRSEQUITEM;
            END IF;

            IF( :NEW.QTDEVRETPROD IS NOT NULL )AND ( :NEW.QTDEVRETPROD <> :OLD.QTDEVRETPROD )THEN
                V_DESC := V_DESC|| ' Qt.Dev='|| :NEW.QTDEVRETPROD;
            END IF;

            IF( :NEW.QTEXTRETPROD IS NOT NULL )AND ( :NEW.QTEXTRETPROD <> :OLD.QTEXTRETPROD ) THEN
                V_DESC := V_DESC || ' Qt.Ext='|| :NEW.QTEXTRETPROD;
            END IF;

            IF( :NEW.QTPERRETPROD IS NOT NULL )AND (:NEW.QTPERRETPROD <> :OLD.QTPERRETPROD ) THEN
                V_DESC := V_DESC|| ' Qt.Per='|| :NEW.QTPERRETPROD;
            END IF;

            IF( :NEW.QTREJRETPROD IS NOT NULL ) AND ( :NEW.QTREJRETPROD <> :OLD.QTREJRETPROD )THEN

               /*TRECHO DA ANTIGA 00NK_CDPTRGO_DTULTATU_LOTITRETIEST*/
                BEGIN
                  SELECT IT.QTPRERETPROD
                   INTO V_QTDE_ITEMRETIEST
                   FROM ITEMRETIEST IT
                  WHERE IT.CDFILIAL     = :NEW.CDFILIAL
                    AND IT.NRRETEST     = :NEW.NRRETEST
                    AND IT.NRSEQRETPROD = :NEW.NRSEQRETPROD
                    AND IT.CDPRODUTO    = :NEW.CDPRODUTO;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN BEGIN
                     V_QTDE_ITEMRETIEST := NULL;
                  END;
               END;

               IF V_QTDE_ITEMRETIEST IS NOT NULL THEN
                  IF V_QTDE_ITEMRETIEST <> :NEW.QTREJRETPROD THEN
                    V_DESC := V_DESC||' Alteracao Qt.Retirada - Valor Anterior: '||:OLD.QTREJRETPROD||' Valor Atual: '||:NEW.QTREJRETPROD||' Teor: '||:NEW.VRAPROVPRODRET||' Operador: '||:NEW.CDOPERADORRETI||' Data: '||:NEW.DTRETIRADA;
                  END IF;
               END IF;
               /********/
            END IF;

            IF( :NEW.QTRETPROD IS NOT NULL ) AND ( :NEW.QTRETPROD <> :OLD.QTRETPROD ) THEN
                V_DESC := V_DESC || ' Qtde.Prevista='|| :NEW.QTRETPROD;
            END IF;

            IF( :NEW.QTRETLIQPROD IS NOT NULL ) AND ( :NEW.QTRETLIQPROD <> :OLD.QTRETLIQPROD )THEN
                V_DESC := V_DESC || ' Qt. Movimentada='|| :NEW.QTRETLIQPROD;
            END IF;

            IF ( :NEW.DTLANCRETDEV IS NOT NULL )AND ( :NEW.DTLANCRETDEV <> :OLD.DTLANCRETDEV ) THEN
                V_DESC := V_DESC || ' Dt.Ret='|| :NEW.DTLANCRETDEV;
            END IF;

            V_TIPO         := '2';
            V_ITPESQ       := :OLD.CDFILIAL|| :OLD.NRRETEST|| :OLD.NRSEQRETPROD|| :OLD.CDPRODUTO;
            V_CDFILLOGOPER := :OLD.CDFILIAL;

        ELSE

            IF (:NEW.NRLOTEESTQ IS NOT NULL) THEN
                V_DESC := V_DESC|| ' Lote='|| :NEW.NRLOTEESTQ;
            END IF;

            IF (:NEW.NRSUBLOTE IS NOT NULL) THEN
                V_DESC := V_DESC|| ' SubLote='|| :NEW.NRSUBLOTE;
            END IF;

            IF (:NEW.CDALMOXARIFE IS NOT NULL) THEN
                V_DESC := V_DESC|| ' Almox='|| :NEW.CDALMOXARIFE;
            END IF;

            IF (:NEW.CDLOCALESTOQ IS NOT NULL) THEN
                V_DESC := V_DESC|| ' Local='|| :NEW.CDLOCALESTOQ;
            END IF;

            IF (:NEW.IDTPRETEST IS NOT NULL) THEN
                V_DESC := V_DESC|| ' Id.Reti='|| :NEW.IDTPRETEST;
            END IF;

            IF (:NEW.NRSEQRETLOTE IS NOT NULL) THEN
                V_DESC := V_DESC|| ' Seq='|| :NEW.NRSEQRETLOTE;
            END IF;

            IF (:NEW.NRLANCESTQ IS NOT NULL) THEN
                V_DESC := V_DESC || ' Lanc.Estq='|| :NEW.NRLANCESTQ;
            END IF;

            IF (:NEW.NRSEQUITEM IS NOT NULL) THEN
                V_DESC := V_DESC|| ' Seq.Item='|| :NEW.NRSEQUITEM;
            END IF;

            IF( :NEW.QTDEVRETPROD IS NOT NULL ) THEN
                V_DESC := V_DESC|| ' Qt.Dev='|| :NEW.QTDEVRETPROD;
            END IF;

            IF( :NEW.QTEXTRETPROD IS NOT NULL ) THEN
                V_DESC := V_DESC || ' Qt.Ext='|| :NEW.QTEXTRETPROD;
            END IF;

            IF( :NEW.QTPERRETPROD IS NOT NULL ) THEN
                V_DESC := V_DESC|| ' Qt.Per='|| :NEW.QTPERRETPROD;
            END IF;

            IF( :NEW.QTREJRETPROD IS NOT NULL ) THEN
                V_DESC := V_DESC||' Qt.Retirada: '|| :NEW.QTREJRETPROD;
            END IF;

            IF( :NEW.QTRETPROD IS NOT NULL ) THEN
                V_DESC := V_DESC || ' Qtde.Prevista='|| :NEW.QTRETPROD;
            END IF;

            IF( :NEW.QTRETLIQPROD IS NOT NULL )THEN
                V_DESC := V_DESC || ' Qt. Movimentada='|| :NEW.QTRETLIQPROD;
            END IF;

            IF ( :NEW.DTLANCRETDEV IS NOT NULL ) THEN
                V_DESC := V_DESC || ' Dt.Ret='|| :NEW.DTLANCRETDEV;
            END IF;

            V_TIPO         := '1';
            V_ITPESQ       := :NEW.CDFILIAL || :NEW.NRRETEST|| :NEW.NRSEQRETPROD|| :NEW.CDPRODUTO;
            V_CDFILLOGOPER := :NEW.CDFILIAL;

        END IF;
    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
            V_TAM := LENGTH(V_DESC);
            WHILE ( V_TAM >= 0 ) LOOP
                V_DTOPER := SYSDATE;
                INSERT INTO LOGOPERA (
                    NMTABELA,
                    DTOPERACAO,
                    NMRESPOPER,
                    IDTIPOOPER,
                    CDPESQITEM,
                    DSOPERACAO,
                    CDFILLOGOPER,
                    DSRDMUSUOPER,
                    DSENDUSUOPER,
                    DSNMUSUOPER,
                    DSMETUSUOPER,
                    CDORESPOPER
                ) VALUES (
                    'LOTITRETIEST',
                    V_DTOPER,
                    V_USUARIO,
                    V_TIPO,
                    V_ITPESQ,
                    SUBSTR(V_DESC, 1, 300),
                    V_CDFILLOGOPER,
                    V_DSSERVERRDM,
                    V_DSREMOTEADDR,
                    V_DSREMOTEHOST,
                    V_DSSERVMETHOD,
                    V_DSREMOTEOPER
                );

                V_DESC := SUBSTR(V_DESC, 300, V_TAM);
                V_TAM  := LENGTH(V_DESC);
            END LOOP;
        END IF;
    END IF;
END;

/