create or replace FUNCTION BUSCA_DADOS_NF_PEDIDO(
   P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,
   P_NRPEDIDO     IN PEDIDO.NRPEDIDO%TYPE) RETURN VARCHAR2 IS

   V_NRNOTAFISC NOTAFISCAL.NRNOTAFISC%TYPE;
   V_DTEMISSAO  NOTAFISCAL.DTEMISSAO%TYPE;
   V_NRLANCTONF NOTAFISCAL.NRLANCTONF%TYPE;
   V_RESULT VARCHAR2(255);
BEGIN
   V_RESULT := '';
   FOR C_NOTAS IN (SELECT DISTINCT CDFILIAL, NRLANCTONF
                     FROM ITEMPRODUTO
                    WHERE CDFILIALPED = P_CDFILIAL
                      AND NRPEDIDO = P_NRPEDIDO)
   LOOP
      SELECT MAX(NRNOTAFISC), MAX(DTEMISSAO), MAX(NRLANCTONF)
        INTO V_NRNOTAFISC, V_DTEMISSAO, V_NRLANCTONF
        FROM NOTAFISCAL
       WHERE CDFILIAL = C_NOTAS.CDFILIAL
         AND NRLANCTONF = C_NOTAS.NRLANCTONF;
      IF (V_NRNOTAFISC IS NULL) THEN
         V_RESULT := V_RESULT ||' N Lanc '|| V_NRLANCTONF;   
      ELSE
         V_RESULT := V_RESULT ||' N NF '+ V_NRNOTAFISC+' Dt.Emis. '||TO_CHAR(V_DTEMISSAO, 'DD/MM/YYYY');
      END IF;   
   END LOOP;
   RETURN(V_RESULT);
END;