create or replace FUNCTION VALOR_PEDIDO( 
   P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE, 
   P_NRPEDIDO     IN PEDIDO.NRPEDIDO%TYPE) RETURN NUMBER IS 
 
   V_VRPEDIDO NUMBER; 
   V_VRITEM NUMBER;
   V_VRDESCITEM NUMBER;
   V_VRENCARGO NUMBER; 
   V_VRIMPOSTO NUMBER; 
   V_VRIMPOSTODESON NUMBER; 
BEGIN 
   V_VRPEDIDO := 0; 
   SELECT SUM(I.QTITEMPED * I.VRPREUNITPED), SUM(NVL(I.VRDESITPED,0)) VRITEM 
     INTO V_VRITEM, V_VRDESCITEM 
     FROM ITEMPEDIDO I 
    WHERE I.CDFILIAL = P_CDFILIAL 
      AND I.NRPEDIDO = P_NRPEDIDO; 
 
   SELECT NVL(SUM(CASE EN.IDENCARGO WHEN 'A' THEN NVL(E.VRENCARGOPED,0) 
                                    WHEN 'D' THEN (NVL(E.VRENCARGOPED,0) * -1) 
                                    ELSE 0 END),0) VRENCARGO 
     INTO V_VRENCARGO 
     FROM ENCARGOPED E, ENCARGO EN 
    WHERE E.CDENCARGO = EN.CDENCARGO 
      AND E.CDFILIAL = P_CDFILIAL 
      AND E.NRPEDIDO = P_NRPEDIDO; 
    SELECT NVL(SUM(VRIMPOSTOITPED),0) 
      INTO V_VRIMPOSTO
      FROM(SELECT IM.SGIMPOSTO,IP.IDIMPDESONERAIT, IM.IDTRATIMPO,
        CASE WHEN IM.IDTRATIMPO = 'A' THEN  NVL(IP.VRIMPOSTOITPED,0)
             WHEN IM.IDTRATIMPO = 'D' THEN (NVL(IP.VRIMPOSTOITPED,0) *(-1))
             WHEN IM.IDTRATIMPO = 'V' THEN  0 END VRIMPOSTOITPED 
     FROM IMPOSTO IM, IMPITEMPEDIDO IP, ITEMPEDIDO IT 
    WHERE IT.CDFILIAL = P_CDFILIAL 
      AND IT.NRPEDIDO = P_NRPEDIDO 
      AND IT.CDFILIAL = IP.CDFILIAL 
      AND IT.NRPEDIDO = IP.NRPEDIDO 
      AND IT.NRITEMPED = IP.NRITEMPED 
      AND IP.CDIMPOSTO = IM.CDIMPOSTO
      AND IP.IDIMPDESONERAIT ='N'); 
   
   SELECT NVL(SUM(NVL(IP.VRIMPOSTOITPED,0)),0) 
     INTO V_VRIMPOSTODESON 
     FROM IMPITEMPEDIDO IP 
    WHERE IP.CDFILIAL = P_CDFILIAL 
      AND IP.NRPEDIDO = P_NRPEDIDO 
      AND IP.IDIMPDESONERAIT = 'S'; 
      
      V_VRITEM     := ROUND(V_VRITEM,3);
      V_VRDESCITEM := ROUND(V_VRDESCITEM,3);
      V_VRENCARGO  := ROUND(V_VRENCARGO,3);
      V_VRIMPOSTO  := ROUND(V_VRIMPOSTO,3); 
      V_VRIMPOSTODESON := ROUND(V_VRIMPOSTODESON,3);
      
 
   V_VRPEDIDO := ROUND(((((V_VRITEM - V_VRDESCITEM) + V_VRENCARGO) + V_VRIMPOSTO) - V_VRIMPOSTODESON),2); 
 
   RETURN(V_VRPEDIDO); 
END; 
/

