create or replace FUNCTION QTDEDEVOLVIDA_RETIRADA_OP(
   P_CDFILIAL     IN ORDPRODUCAO.CDFILIAL%TYPE,
   P_NRORDPROD    IN ORDPRODUCAO.NRORDPROD%TYPE,
   P_NRSUBORDPROD IN ORDPRODUCAO.NRSUBORDPROD%TYPE,
   P_CDPRODINSUMO IN PRODUTO.CDPRODUTO%TYPE,
   P_NRLOTEESTQ   IN LOTE.NRLOTEESTQ%TYPE,
   P_NRSUBLOTE    IN LOTE.NRSUBLOTE%TYPE) RETURN FLOAT IS

   V_QTDEVOLVIDA LOTITRETIEST.QTRETPROD%TYPE;
BEGIN
   V_QTDEVOLVIDA := 0;
   BEGIN
      SELECT NVL(SUM(LT.QTRETPROD), 0)
        INTO V_QTDEVOLVIDA
        FROM ORDPRODUCAO OP JOIN PARAMAN PM
                              ON OP.CDFILIAL = PM.CDFILIAL
                            JOIN RETIRADAESTO R
                              ON OP.CDFILIAL = R.CDFILIAL
                             AND OP.NRORDPROD = R.NRORDPROD
                             AND OP.NRSUBORDPROD = R.NRSUBORDPROD
                            JOIN ETAPAPROD E
                              ON R.CDETAPAPROD = E.CDETAPAPROD
                            JOIN ITEMRETIEST I
                              ON R.CDFILIAL = I.CDFILIAL
                             AND R.NRRETEST = I.NRRETEST
                            LEFT JOIN LOTITRETIEST LT
                                   ON I.CDFILIAL = LT.CDFILIAL
                                  AND I.NRRETEST = LT.NRRETEST
                                  AND I.CDPRODUTO = LT.CDPRODUTO
                                  AND I.NRSEQRETPROD = LT.NRSEQRETPROD
                            JOIN PRODUTO PROD
                              ON LT.CDPRODUTO = PROD.CDPRODUTO
                            LEFT JOIN ITLANCTOEST IT
                                   ON LT.CDFILIAL = IT.CDFILIAL
                                  AND LT.NRLANCESTQ = IT.NRLANCESTQ
                                  AND LT.NRSEQUITEM = IT.NRSEQUITEM
       WHERE (LT.IDTPRETEST     = 'D')
         AND (LT.NRLANCESTQ IS NOT NULL)
         AND (OP.CDFILIAL = P_CDFILIAL)
         AND (OP.NRORDPROD = P_NRORDPROD)
         AND (OP.NRSUBORDPROD = P_NRSUBORDPROD)
         AND (IT.CDPRODUTO = P_CDPRODINSUMO)
         AND (LT.NRLOTEESTQ = P_NRLOTEESTQ)
         AND (LT.NRSUBLOTE = P_NRSUBLOTE);
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_QTDEVOLVIDA := '0';
   END;
   RETURN(V_QTDEVOLVIDA);
END;
/
