create or replace PROCEDURE CONHECIMENTO_TRANSPORTE
   (P_CDFILIAL   IN NOTAFISCAL.CDFILIAL%TYPE,
    P_NRLANCTONF IN NOTAFISCAL.NRLANCTONF%TYPE,
    P_NRLANCESTQ IN LANCTOESTOQ.NRLANCESTQ%TYPE)

IS
   V_VRLIQUFRETE  NUMBER;
   V_VRBRUTFRETE  NUMBER;
   V_VRTTLANESTQ  NUMBER;
   V_VRTTLANBRUT  NUMBER;
   V_VRTTCUSENTR  NUMBER;
   
   V_VRRATEIO     NUMBER;
   V_VRRATEIOTOT  NUMBER;

   V_VRLANCTOEST  NUMBER;
   V_VRLANCTOBRUT NUMBER;
   V_VRCUSTENTR   NUMBER;
   V_VRFATOCONV   NUMBER;
   V_RATCUSTIT    NUMBER;
   V_NRLANCESTQ   VARCHAR2(20);

   CURSOR C_ITEMPRODUTO IS
      SELECT I.NRSEQITPROD,
             VRCONTITEM AS VRLANCTOEST,
             VRBRUTITEM AS VRLANCTOBRUT,
             (I.QTITEMPROD * P.VRFATOCONV) AS QTITEMPROD
      FROM PRODUTO P, ITEMPRODUTO I
      WHERE I.CDFILIAL   = P_CDFILIAL   AND
            I.NRLANCTONF = P_NRLANCTONF AND
            I.CDPRODUTO  = P.CDPRODUTO;

BEGIN

   V_RATCUSTIT := 0;
   V_VRBRUTFRETE := NULL;
   
   BEGIN
   SELECT MAX(1)
     INTO V_RATCUSTIT
     FROM RATEIOCUSTOIT
    WHERE CDFILIAL   = P_CDFILIAL
      AND NRLANCTONF = P_NRLANCTONF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
      V_RATCUSTIT := 0;      
   END;
   
   IF ((V_RATCUSTIT = 0) OR (V_RATCUSTIT IS NULL)) THEN

   FOR C_NOTA IN(SELECT CDFILIALIT, NRLANCTONFIT
                 FROM ITEMNOTAFISC
                 WHERE CDFILIAL   = P_CDFILIAL AND
                       NRLANCTONF = P_NRLANCTONF) LOOP

      SELECT SUM(VRCONTITEM) AS VRTTLANESTQ,
             SUM(VRBRUTITEM) AS VRTTLANBRUT,
             SUM(VRCONTITEM) AS VRTTCUSENTR
      INTO V_VRTTLANESTQ, V_VRTTLANBRUT, V_VRTTCUSENTR
      FROM ITEMPRODUTO I, ITEMNOTAFISC II
      WHERE II.CDFILIALIT    = C_NOTA.CDFILIALIT    AND
            II.NRLANCTONFIT  = C_NOTA.NRLANCTONFIT  AND
            II.CDFILIAL      = I.CDFILIAL           AND
            II.NRLANCTONF    = I.NRLANCTONF;


      SELECT SUM(VRCONTITEM) AS VRLIQUFRETE,
             SUM(VRBRUTITEM) AS VRBRUTFRETE
      INTO V_VRLIQUFRETE,
           V_VRBRUTFRETE
      FROM ITEMPRODUTO IP, ITEMNOTAFISC T
      WHERE IP.CDFILIAL   = T.CDFILIALIT   AND
            IP.NRLANCTONF = T.NRLANCTONFIT AND
            T.CDFILIAL    = P_CDFILIAL     AND
            T.NRLANCTONF  = P_NRLANCTONF;
            
   END LOOP;     

   IF V_VRBRUTFRETE IS NOT NULL THEN
      FOR C_RATEIO IN C_ITEMPRODUTO LOOP
         V_VRLANCTOEST  := (C_RATEIO.VRLANCTOEST/V_VRTTLANESTQ ) * V_VRLIQUFRETE;
         V_VRLANCTOBRUT := (C_RATEIO.VRLANCTOBRUT/V_VRTTLANBRUT) * V_VRBRUTFRETE;
         V_VRCUSTENTR   := (C_RATEIO.VRLANCTOEST/V_VRTTCUSENTR)  * V_VRLIQUFRETE;

         IF C_RATEIO.QTITEMPROD <> 0 THEN
            V_VRLANCTOEST  := (V_VRLANCTOEST/C_RATEIO.QTITEMPROD);
            V_VRLANCTOBRUT := (V_VRLANCTOBRUT/C_RATEIO.QTITEMPROD);
         END IF;

         UPDATE  ITLANCTOEST
         SET VRLANCTOEST  = VRLANCTOEST  + V_VRLANCTOEST,
             VRLANCTOBRUT = VRLANCTOBRUT + V_VRLANCTOBRUT,
             VRCUSTENTR   = VRCUSTENTR   + V_VRCUSTENTR
         WHERE CDFILIAL   = P_CDFILIAL   AND
               NRLANCESTQ = P_NRLANCESTQ AND
               NRSEQUITEM = C_RATEIO.NRSEQITPROD;
      END LOOP;
   END IF;
   ELSE
      V_NRLANCESTQ := NULL;       
      BEGIN
      SELECT NRLANCESTQ
        INTO V_NRLANCESTQ
        FROM LANCTOESTOQ
       WHERE CDFILIAL   = P_CDFILIAL
         AND NRLANCTONF = P_NRLANCTONF;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
         V_NRLANCESTQ := NULL;      
      END;   
      
      IF V_NRLANCESTQ IS NOT NULL THEN
      FOR C_ITEM IN (SELECT NRSEQITPROD, VRRATEIO
                       FROM RATEIOCUSTOIT
                      WHERE CDFILIAL   = P_CDFILIAL
                        AND NRLANCTONF = P_NRLANCTONF 
      
      )LOOP
         UPDATE  ITLANCTOEST
         SET VRLANCTOEST  = VRLANCTOEST  + (C_ITEM.VRRATEIO/QTLANCTOEST),
             VRLANCTOBRUT = VRLANCTOBRUT + (C_ITEM.VRRATEIO/QTLANCTOEST),
             VRCUSTENTR   = VRCUSTENTR   + C_ITEM.VRRATEIO
         WHERE CDFILIAL   = P_CDFILIAL   AND
               NRLANCESTQ = V_NRLANCESTQ AND
               NRSEQUITEM = C_ITEM.NRSEQITPROD;    
      END LOOP;
      END IF;
   END IF;
   
END;
/