CREATE OR REPLACE TRIGGER USR_ATU_FRETE_AF
BEFORE UPDATE OF VRTOTALAF ON AUTOFORNE
FOR EACH ROW
DECLARE
   V_CDENCARGO   ENCARGO.CDENCARGO%TYPE;
   V_NRSEQAPURAF AUTOFORNE.NRSEQAPURAF%TYPE;
   V_CDFORNECED  AUTOFORNE.CDFORNECED%TYPE;
   V_VRFRETEFORN FORNECEDOR.VRFRETEFORN%TYPE;
   V_CDREGIAO    FILIAL.CDREGIAO%TYPE;
   V_IDENCARGO   ENCARGO.IDENCARGO%TYPE;
   V_NRAUTOFORN  ENCARGOAF.NRAUTOFORN%TYPE;
BEGIN
   SELECT NVL(MAX(CDENCARGO), 'WW'), MAX(IDENCARGO)
     INTO V_CDENCARGO, V_IDENCARGO
     FROM ENCARGO
    WHERE UPPER(NMENCARGO) ='FRETE';

   IF V_CDENCARGO <> 'WW' THEN
      SELECT NVL(MAX(:NEW.NRSEQAPURAF), 'W'), MAX(:NEW.CDFORNECED),
             NVL(MAX(FORNECEDOR.VRFRETEFORN), 0), MAX(FILIAL.CDREGIAO)
        INTO V_NRSEQAPURAF, V_CDFORNECED, V_VRFRETEFORN, V_CDREGIAO
        FROM FORNECEDOR, FILIAL
       WHERE :NEW.CDFORNECED = FORNECEDOR.CDFORNECED
         AND :NEW.CDFILDEST  = FILIAL.CDFILIAL;

      FOR C_ITEMAUTF IN (SELECT NVL(MAX(VRFRETEFORNEPLANCOT), MAX(V_VRFRETEFORN)) VRFRETEFORNEPLANCOT, 
                                DTPROGENTAF
                           FROM ITEMAUTF, ITEMCOLE
                          WHERE ITEMAUTF.CDFILDEST   = :NEW.CDFILDEST
                            AND ITEMAUTF.CDFILLANC   = :NEW.CDFILLANC
                            AND ITEMAUTF.NRAUTOFORN  = :NEW.NRAUTOFORN
                            AND ITEMCOLE.CDPRODUTO   = ITEMAUTF.CDPRODUTO
                            AND ITEMCOLE.CDMARCA     = ITEMAUTF.CDMARCA
                            AND ITEMCOLE.CDREGIAO    = V_CDREGIAO
                            AND ITEMCOLE.CDFORNECED  = V_CDFORNECED
                            AND ITEMAUTF.DTPROGENTAF BETWEEN ITEMCOLE.DTINIVALITM AND ITEMCOLE.DTFINVALITM
                          GROUP BY DTPROGENTAF) LOOP
         IF C_ITEMAUTF.VRFRETEFORNEPLANCOT > 0 THEN
            SELECT NVL(MAX(NRAUTOFORN), 'W')
              INTO V_NRAUTOFORN
              FROM ENCARGOAF
             WHERE CDFILDEST   = :NEW.CDFILDEST
               AND CDFILLANC   = :NEW.CDFILLANC
               AND NRAUTOFORN  = :NEW.NRAUTOFORN
               AND DTENTRENCAF = C_ITEMAUTF.DTPROGENTAF
               AND CDENCARGO   = V_CDENCARGO;

            IF V_NRAUTOFORN = 'W' THEN
               INSERT
                 INTO ENCARGOAF(CDFILDEST, CDFILLANC, NRAUTOFORN, DTENTRENCAF, CDENCARGO, VRENCARGOAF)
               VALUES (:NEW.CDFILDEST, :NEW.CDFILLANC, :NEW.NRAUTOFORN, C_ITEMAUTF.DTPROGENTAF, V_CDENCARGO, C_ITEMAUTF.VRFRETEFORNEPLANCOT);

               :NEW.VRTOTALAF := :NEW.VRTOTALAF+C_ITEMAUTF.VRFRETEFORNEPLANCOT;
            END IF;
         END IF;
      END LOOP;
   END IF;
END;
/


-- VERSAO --
BEGIN ATUVEROBJ('USR_ATU_FRETE_AF','6.10.XXX'); END;
/
