create or replace FUNCTION BUSCA_BASE_COMISSAO_FECP( 
   P_CDFILIAL     IN ITEMPRODUTO.CDFILIAL%TYPE, 
   P_NRLANCTONF   IN ITEMPRODUTO.NRLANCTONF%TYPE, 
   P_NRSEQITPROD  IN ITEMPRODUTO.NRSEQITPROD%TYPE) RETURN NUMBER IS 
 
   V_VRTOTAITEM    NUMBER; 
   V_VRICMSST      NUMBER; 
   V_VRPRODUTOS    NUMBER;
   V_VRENCDESC     NUMBER; 
   V_VRENCACR      NUMBER;
   V_VRDESCITEM    NUMBER;
   V_VRIMPDESONERADO NUMBER;
   V_CDFILIEMIT    VARCHAR2(04); 
   V_IDICMSCOMISS  VARCHAR2(01);
   V_IDIMPDESOCOMISS VARCHAR2(01);
   V_RESULT        NUMBER;
   V_VRAJUSTE      NUMBER;
BEGIN 
   SELECT SUM(IT.VRTOTAITEM), SUM(IT.VRDESCITEM) 
     INTO V_VRTOTAITEM, V_VRDESCITEM 
     FROM ITEMPRODUTO IT 
    WHERE IT.CDFILIAL = P_CDFILIAL 
      AND IT.NRLANCTONF = P_NRLANCTONF 
      AND IT.NRSEQITPROD = P_NRSEQITPROD; 

   SELECT MAX(NVL(NF.VRPRODUTOS,0)) 
     INTO V_VRPRODUTOS 
     FROM NOTAFISCAL NF
    WHERE NF.CDFILIAL   = P_CDFILIAL 
      AND NF.NRLANCTONF = P_NRLANCTONF;

     SELECT SUM(DECODE(IDENCARGO,'D',NVL(VRITEMENCA,0),0)),
            SUM(DECODE(IDENCARGO,'A',NVL(VRITEMENCA,0),0))
            INTO V_VRENCDESC, V_VRENCACR
        FROM(
            SELECT I.CDFILIAL, I.NRLANCTONF, E.IDENCARGO, I.VRITEMENCA
              FROM ITEMENCARGO I, ENCARGO E, NOTAFISCAL N
             WHERE (I.CDFILIAL = N.CDFILIAL)
               AND (I.NRLANCTONF = N.NRLANCTONF)
               AND (N.CDFILIEMIT = P_CDFILIAL)
               AND N.NRLANCTONF  = P_NRLANCTONF
               AND (I.CDENCARGO  = E.CDENCARGO)
               AND (I.VRITEMENCA > 0 )
         );  

   SELECT NVL(SUM(IP.VRIMPOSTOIT), 0)+NVL(SUM(IP.VRADICFECP), 0) 
     INTO V_VRICMSST 
     FROM ITEMPROIMPOS IP, IMPOSTO I, IMPOSTONFE INFE 
    WHERE IP.CDFILIAL = P_CDFILIAL 
      AND IP.NRLANCTONF = P_NRLANCTONF 
      AND IP.NRSEQITPROD = P_NRSEQITPROD 
      AND IP.CDFISCALIT = '1' 
      AND IP.CDIMPOSTO = I.CDIMPOSTO 
      AND I.CDIMPOSTONFE = INFE.CDIMPOSTONFE 
      AND INFE.CDIMPOSTONFE = '80'; 

   SELECT NVL(SUM(IP.VRIMPOSTOIT), 0) 
     INTO V_VRIMPDESONERADO 
     FROM ITEMPROIMPOS IP, IMPOSTO I
    WHERE IP.CDFILIAL = P_CDFILIAL 
      AND IP.NRLANCTONF = P_NRLANCTONF 
      AND IP.NRSEQITPROD = P_NRSEQITPROD 
      AND IP.CDFISCALIT = '1' 
      AND IP.CDIMPOSTO = I.CDIMPOSTO
      AND IDIMPDESONERAIT  = 'S'; 

  SELECT NVL(SUM(DECODE(T.IDTPAJRECEBER,'A', 0, DECODE(T.IDAJUCTREC,'D',T.VRAJUSTEREC * -1, T.VRAJUSTEREC))),0) AS SOMAAJUSTE
     INTO V_VRAJUSTE
     FROM(SELECT T.IDTPAJRECEBER, T.IDAJUCTREC, A.VRAJUSTEREC, A.NRLANCTONF, A.CDFILIAL, A.NRSEQVENREC, A.NRPARCELAREC
            FROM NOTAFISCAL N
           INNER JOIN CONTARECEBER C
              ON N.CDFILIAL   = C.CDFILIAL
             AND N.NRLANCTONF = C.NRLANCTONF
           INNER JOIN AJCTRECEBER A
              ON C.CDFILIAL     = A.CDFILIAL
             AND C.NRLANCTONF   = A.NRLANCTONF
             AND C.NRSEQVENREC  = A.NRSEQVENREC
             AND C.NRPARCELAREC = A.NRPARCELAREC
           INNER JOIN TIPOAJRECEBER T
              ON A.CDAJUCTREC = T.CDAJUCTREC
           WHERE N.CDFILIEMIT = P_CDFILIAL
             AND N.NRLANCTONF = P_NRLANCTONF
           UNION
          SELECT T.IDTPAJRECEBER, T.IDAJUCTREC, AJB.VRAJBOLREC, C.NRLANCTONF, C.CDFILIAL, C.NRSEQVENREC, C.NRPARCELAREC
            FROM NOTAFISCAL N
           INNER JOIN CONTARECEBER C
              ON N.CDFILIAL   = C.CDFILIAL
             AND N.NRLANCTONF = C.NRLANCTONF
          INNER JOIN BOLETRECEBER BO
              ON C.CDFILIBOLREC = BO.CDFILIAL
             AND C.NRLANBOLREC  = BO.NRLANBOLREC
           INNER JOIN AJBOLREC AJB
              ON BO.CDFILIAL    = AJB.CDFILIAL
             AND BO.NRLANBOLREC = AJB.NRLANBOLREC
           INNER JOIN TIPOAJRECEBER T
              ON AJB.CDAJUCTREC = T.CDAJUCTREC
           WHERE N.CDFILIEMIT = P_CDFILIAL
             AND N.NRLANCTONF = P_NRLANCTONF)T
    GROUP BY T.NRLANCTONF, T.CDFILIAL, T.NRSEQVENREC, T.NRPARCELAREC;

   SELECT MAX(N.CDFILIEMIT) 
     INTO V_CDFILIEMIT 
     FROM NOTAFISCAL N 
    WHERE N.CDFILIAL = P_CDFILIAL 
      AND N.NRLANCTONF = P_NRLANCTONF; 

   SELECT NVL(MAX(IDICMSCOMISS), 'N'),NVL(MAX(IDIMPDESOCOMISS),'N') 
     INTO V_IDICMSCOMISS, V_IDIMPDESOCOMISS 
     FROM PAREXPEDICAO 
    WHERE CDFILIAL = V_CDFILIEMIT;

   IF V_IDICMSCOMISS = 'N' THEN 
      V_VRICMSST := 0; 
   END IF;

   IF V_IDIMPDESOCOMISS = 'N' THEN 
       V_VRIMPDESONERADO := 0; 
   END IF;

   V_RESULT := (V_VRTOTAITEM + V_VRICMSST - V_VRDESCITEM - V_VRIMPDESONERADO 
   + ROUND(NVL(((V_VRENCACR*(V_VRTOTAITEM - V_VRDESCITEM))/V_VRPRODUTOS),0),2)
   - ROUND(NVL(((V_VRENCDESC*(V_VRTOTAITEM - V_VRDESCITEM))/V_VRPRODUTOS),0),2) + V_VRAJUSTE);

   RETURN(V_RESULT);  
END;
/