create or replace FUNCTION BUSCA_QTTOTLOTE(P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,
                                           P_NRORDPROD    IN PRODACABADO.NRORDPROD%TYPE,
                                           P_NRSUBORDPROD IN PRODACABADO.NRSUBORDPROD%TYPE) RETURN FLOAT


AS

V_QTTOTLOTE PRODACABADO.QTPRODPREV%TYPE := 0;

BEGIN
   BEGIN 
      SELECT SUM(ROUND(NVL(PA.QTPRODPREV,0), 5))
        INTO V_QTTOTLOTE
        FROM PRODACABADO PA
       INNER JOIN SUBPRETAPAOP SE
          ON SE.CDFILIAL     = PA.CDFILIAL
         AND SE.NRORDPROD    = PA.NRORDPROD
         AND SE.NRSUBORDPROD = PA.NRSUBORDPROD
         AND SE.CDSUBPRODETA = PA.CDPRODUTO
         AND SE.NRLOTEESTQ   = PA.NRLOTEESTQ
         AND SE.NRSUBLOTE    = PA.NRSUBLOTE
       WHERE PA.CDFILIAL     = P_CDFILIAL
         AND PA.NRORDPROD    = P_NRORDPROD
         AND PA.NRSUBORDPROD = P_NRSUBORDPROD;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_QTTOTLOTE := 0;
   END;

 RETURN(V_QTTOTLOTE);
END;
/
