create or replace FUNCTION RATEIO_CONHECIMENTO_TRANSPORTE    
(     
   P_CDFILIAL IN NOTAFISCAL.CDFILIAL%TYPE,    
   P_NRLANCTONF IN NOTAFISCAL.NRLANCTONF%TYPE,    
   P_CDGRUPPROD IN NVSUBGRPROD.CDGRUPPROD%TYPE,     
   P_CDSUBGRPROD IN NVSUBGRPROD.CDSUBGRPROD%TYPE    
) RETURN NUMBER IS    
   V_QTFILIAIS NUMBER;
   V_QTNOTAS NUMBER;
      
   V_VRLIQUFRETE  NUMBER;
   V_VRBRUTFRETE  NUMBER;
   V_VRTTLANESTQ  NUMBER;
   V_VRTTLANBRUT  NUMBER;
   V_VRTTCUSENTR  NUMBER;

   V_VRLANCTOEST  NUMBER;
   V_VRLANCTOBRUT NUMBER;
   V_VRCUSTENTR   NUMBER;
   V_VRFATOCONV   NUMBER;
   V_IDENTRSAIDOP VARCHAR2(1);
   
   CURSOR C_ITEMPRODUTO IS
     SELECT DISTINCT NRSEQITPROD, VRLANCTOEST,
            VRLANCTOBRUT, QTITEMPROD, CDARVPROD, CDPRODUTO
       FROM (SELECT A.CDFILISUBGR,I.NRSEQITPROD,
                    VRCONTITEM AS VRLANCTOEST,
                    VRBRUTITEM AS VRLANCTOBRUT,
                    (I.QTITEMPROD * P.VRFATOCONV) AS QTITEMPROD, P.CDARVPROD, A.CDPRODUTO
               FROM PRODUTO P, ITEMPRODUTO I,
            (SELECT A.CDFILISUBGR, A.CDSUBGRPROD,
                    A.CDGRUPPROD, B.CDPRODUTO,
                    B.NMPRODUTO, B.CDARVPROD
               FROM PRODUTO B, NVSUBGRPROD A
              WHERE (A.CDFILISUBGR  = P_CDFILIAL)
                AND (A.CDGRUPPROD   = P_CDGRUPPROD)
                AND (A.CDSUBGRPROD  = P_CDSUBGRPROD)
                AND (B.CDPRODUTO    = A.CDPRODUTO)
                AND (NVL(B.IDPRODATIVPR,'S') = 'S')) A
      WHERE I.NRLANCTONF    = P_NRLANCTONF
        AND I.CDPRODUTO     = P.CDPRODUTO
        AND I.CDFILIAL      = P_CDFILIAL
        AND (I.CDFILIAL     = A.CDFILISUBGR)
        AND (A.CDGRUPPROD   = P_CDGRUPPROD)
        AND (A.CDSUBGRPROD  = P_CDSUBGRPROD)
        AND (P.CDARVPROD    LIKE A.CDARVPROD||'%')
      UNION ALL
     SELECT A.CDFILISUBGR,I.NRSEQITPROD,
            VRCONTITEM AS VRLANCTOEST,
            VRBRUTITEM AS VRLANCTOBRUT,
            (I.QTITEMPROD * P.VRFATOCONV) AS QTITEMPROD,P.CDARVPROD, A.CDPRODUTO
       FROM PRODUTO P, ITEMPRODUTO I,
            (SELECT A.CDFILISUBGR, A.CDSUBGRPROD,
                    A.CDGRUPPROD, B.CDPRODUTO,
                    B.NMPRODUTO, B.CDARVPROD
               FROM PRODUTO B, NVSUBGRPROD A
              WHERE (A.CDFILISUBGR  = 'P')
                AND (A.CDGRUPPROD   = P_CDGRUPPROD)
                AND (A.CDSUBGRPROD  = P_CDSUBGRPROD)
                AND (B.CDPRODUTO    = A.CDPRODUTO)
                AND (NVL(B.IDPRODATIVPR,'S') = 'S')) A
      WHERE I.NRLANCTONF    = P_NRLANCTONF
        AND I.CDPRODUTO     = P.CDPRODUTO
        AND I.CDFILIAL      = P_CDFILIAL
        AND (A.CDFILISUBGR  = 'P')
        AND (A.CDGRUPPROD   = P_CDGRUPPROD)
        AND (A.CDSUBGRPROD  = P_CDSUBGRPROD)
        AND (P.CDARVPROD    LIKE A.CDARVPROD||'%'));
BEGIN    
   FOR C_NOTA IN(SELECT CDFILIALIT, NRLANCTONFIT
                   FROM ITEMNOTAFISC I, NOTAFISCAL N
                  WHERE N.CDFILIAL =  I.CDFILIAL 
                    AND N.NRLANCTONF = I.NRLANCTONF
                    AND I.CDFILIAL   = P_CDFILIAL
                    AND I.NRLANCTONF = P_NRLANCTONF
                    AND N.IDENTRSAIDOP = 'E') LOOP

      SELECT IDENTRSAIDOP
        INTO V_IDENTRSAIDOP
        FROM NOTAFISCAL
       WHERE CDFILIAL   = C_NOTA.CDFILIALIT
         AND NRLANCTONF = C_NOTA.NRLANCTONFIT;

      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_IDENTRSAIDOP = 'E') THEN
      IF V_VRBRUTFRETE IS NOT NULL THEN
         V_VRLANCTOBRUT := 0;    
         FOR C_RATEIO IN C_ITEMPRODUTO LOOP
            IF NVL(V_VRTTLANESTQ, 0) <> 0 THEN
               V_VRLANCTOEST  := V_VRLANCTOEST + (C_RATEIO.VRLANCTOEST/V_VRTTLANESTQ ) * V_VRLIQUFRETE;
            END IF;
            IF NVL(V_VRTTLANBRUT, 0) <> 0 THEN
               V_VRLANCTOBRUT := V_VRLANCTOBRUT + (C_RATEIO.VRLANCTOBRUT/V_VRTTLANBRUT) * V_VRBRUTFRETE;
            END IF;
            IF NVL(V_VRTTCUSENTR, 0) <> 0 THEN
               V_VRCUSTENTR   := V_VRCUSTENTR +(C_RATEIO.VRLANCTOEST/V_VRTTCUSENTR)  * V_VRLIQUFRETE;
            END IF;
         END LOOP;
      END IF;
      RETURN  V_VRLANCTOBRUT;
   ELSE
      RETURN 0;
   END IF;
END;
/
