create or replace TRIGGER LOG_CDP_ITEMRETIEST AFTER
    DELETE OR INSERT OR UPDATE ON ITEMRETIEST
    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

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

        IF :OLD.CDPRODUTO IS NOT NULL THEN
            V_DESC := V_DESC|| ' Produto='|| :OLD.CDPRODUTO;
        END IF;

       IF :OLD.DTRETPROD IS NOT NULL THEN
            V_DESC := V_DESC|| ' Data Retirada='|| :OLD.DTRETPROD;
        END IF;

       IF :OLD.QTPRERETPROD IS NOT NULL THEN
            V_DESC := V_DESC|| ' Qtde. Retirada='|| :OLD.QTPRERETPROD;
        END IF;

        IF :OLD.CDTIPORETI IS NOT NULL THEN
            V_DESC := V_DESC|| ' Tipo Retirada='|| :OLD.CDTIPORETI;
        END IF;

        IF :OLD.CDTIPOINSU IS NOT NULL THEN
            V_DESC := V_DESC|| ' Cod.Tipo Insumo='|| :OLD.CDTIPOINSU;
        END IF;

       IF :OLD.NMINSUMO IS NOT NULL THEN
            V_DESC := V_DESC|| ' Insumo='|| :OLD.NMINSUMO;
        END IF;

       IF :OLD.UNMEDINSUMO IS NOT NULL THEN
            V_DESC := V_DESC|| ' Un. Medida Insumo='|| :OLD.UNMEDINSUMO;
        END IF;

        IF :OLD.DSFASE IS NOT NULL THEN
            V_DESC := V_DESC|| ' Desc. Fase='|| :OLD.DSFASE;
        END IF;

        IF :OLD.IDLOTEEXT IS NOT NULL THEN
            V_DESC := V_DESC|| ' Lote Ext='|| :OLD.IDLOTEEXT;
        END IF;
        
        IF :OLD.IDREQUIPROD IS NOT NULL THEN
            V_DESC := V_DESC|| ' Id Req. Produto='|| :OLD.IDREQUIPROD;
        END IF;
    
        IF :OLD.NRREQUESTO IS NOT NULL THEN
            V_DESC := V_DESC|| ' Nr. Req. Estoque='|| :OLD.NRREQUESTO;
        END IF;
        
    ELSE
    
        IF UPDATING THEN

            V_TIPO         := '2';
            V_ITPESQ       := :OLD.CDFILIAL|| :OLD.NRRETEST|| :OLD.NRSEQRETPROD|| :OLD.CDPRODUTO;
            V_CDFILLOGOPER := :OLD.CDFILIAL;
 
            IF NVL(:NEW.CDPRODUTO, '!') <> NVL(:OLD.CDPRODUTO, '!') THEN
                V_DESC := V_DESC|| ' Produto='|| :NEW.CDPRODUTO;
            END IF;
            
            IF NVL(:NEW.DTRETPROD, SYSDATE) <> NVL(:OLD.DTRETPROD, SYSDATE) THEN
                V_DESC := V_DESC|| ' Data Retirada='|| :NEW.DTRETPROD;
            END IF;

            IF NVL(:NEW.QTPRERETPROD, 0) <> NVL(:OLD.QTPRERETPROD, 0) THEN
                V_DESC := V_DESC|| ' Qtde. Retirada='|| :NEW.QTPRERETPROD;
            END IF;
        
            IF NVL(:NEW.CDTIPORETI, '!') <> NVL(:OLD.CDTIPORETI, '!') THEN
                V_DESC := V_DESC|| ' Tipo Retirada='|| :NEW.CDTIPORETI;
            END IF;

            IF NVL(:NEW.CDTIPOINSU, '!') <> NVL(:OLD.CDTIPOINSU, '!') THEN
                V_DESC := V_DESC|| ' Cod. Tipo Insumo='|| :NEW.CDTIPOINSU;
            END IF;

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

            IF NVL(:NEW.UNMEDINSUMO, '!') <> NVL(:OLD.UNMEDINSUMO, '!') THEN
                V_DESC := V_DESC|| ' Un. Medida Insumo='|| :NEW.UNMEDINSUMO;
            END IF;

            IF NVL(:NEW.DSFASE, '!') <> NVL(:OLD.DSFASE, '!') THEN
                V_DESC := V_DESC || ' Desc. Fase'|| :NEW.DSFASE;
            END IF;

            IF NVL(:NEW.IDLOTEEXT, '!') <> NVL(:OLD.IDLOTEEXT, '!') THEN
                V_DESC := V_DESC|| ' Id. Lote Ext='|| :NEW.IDLOTEEXT;
            END IF;
            
           IF NVL(:NEW.IDREQUIPROD, '!') <> NVL(:OLD.IDREQUIPROD, '!') THEN
                V_DESC := V_DESC|| ' Id. Req. Produto='|| :NEW.IDREQUIPROD;
            END IF;
            
            IF NVL(:NEW.NRREQUESTO, '!') <> NVL(:OLD.NRREQUESTO, '!') THEN
                V_DESC := V_DESC|| ' Nr. Req. Estoque='|| :NEW.NRREQUESTO;
            END IF;
            
        ELSE

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

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

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

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

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

            IF (:NEW.CDTIPOINSU IS NOT NULL) THEN
                V_DESC := V_DESC|| ' Cod. Tipo Insumo='|| :NEW.CDTIPOINSU;
            END IF;

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

            IF (:NEW.UNMEDINSUMO IS NOT NULL) THEN
                V_DESC := V_DESC || ' Un. Medida Insumo='|| :NEW.UNMEDINSUMO;
            END IF;

            IF (:NEW.DSFASE IS NOT NULL) THEN
                V_DESC := V_DESC|| ' Desc. Fase='|| :NEW.DSFASE;
            END IF;

            IF( :NEW.IDLOTEEXT IS NOT NULL ) THEN
                V_DESC := V_DESC|| ' Id. Lote Ext='|| :NEW.IDLOTEEXT;
            END IF;
            
            IF( :NEW.IDREQUIPROD IS NOT NULL ) THEN
                V_DESC := V_DESC|| ' Id. Req. Produto='|| :NEW.IDREQUIPROD;
            END IF;
            
            IF( :NEW.NRREQUESTO IS NOT NULL ) THEN
                V_DESC := V_DESC|| ' Nr. Req. Estoque='|| :NEW.NRREQUESTO;
            END IF;
        
        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 (
                    'ITEMRETIEST',
                    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;
/