CREATE OR REPLACE FUNCTION VALOR_BONIFICACAO_PEDIDO
   (P_CDFILIAL   IN PEDIDO.CDFILIAL%TYPE,
    P_NRPEDIDO   IN PEDIDO.NRPEDIDO%TYPE) RETURN NUMBER
AS
   V_VRITEM NUMBER;
   V_VRIMPOSTO NUMBER;
   V_VRBONIFICACAO NUMBER;
BEGIN
   V_VRITEM := 0;
   SELECT NVL(SUM((I.QTITEMPED * I.VRPREUNITPED) - (NVL(I.VRDESITPED,0))),0) VRITEM
     INTO V_VRITEM
     FROM ITEMPEDIDO I
    WHERE I.CDFILIAL = P_CDFILIAL
      AND I.NRPEDIDO = P_NRPEDIDO
      AND NVL(I.IDPRODBONIF, 'N') = 'S';

   SELECT NVL(SUM(NVL(IP.VRIMPOSTOITPED,0)),0) VRIMPOSTO
     INTO V_VRIMPOSTO
     FROM IMPOSTO IM, IMPITEMPEDIDO IP, ITEMPEDIDO IT
    WHERE IT.CDFILIAL = P_CDFILIAL
      AND IT.NRPEDIDO = P_NRPEDIDO
      AND NVL(IT.IDPRODBONIF, 'N') = 'S'
      AND IT.CDFILIAL = IP.CDFILIAL
      AND IT.NRPEDIDO = IP.NRPEDIDO
      AND IT.NRITEMPED = IP.NRITEMPED
      AND IP.CDIMPOSTO = IM.CDIMPOSTO
      AND IM.IDTRATIMPO = 'A';

   V_VRBONIFICACAO := V_VRITEM + V_VRIMPOSTO;
   RETURN(V_VRBONIFICACAO);
END;
/