create or replace FUNCTION VERIFICA_SEMIACABADO(
   P_CDFILIAL     IN ORDPRODUCAO.CDFILIAL%TYPE,
   P_NRORDPROD    IN ORDPRODUCAO.NRORDPROD%TYPE,
   P_NRSUBORDPROD IN ORDPRODUCAO.NRSUBORDPROD%TYPE,
   P_CDPRODINSUMO IN ORDPRODUCAO.CDPRODUTO%TYPE) RETURN VARCHAR IS

   V_IDSEMIACETAP INSUMETAPA.IDSEMIACETAP%TYPE;
BEGIN
   V_IDSEMIACETAP := 'N';


   BEGIN
      SELECT NVL(MAX(I.IDSEMIACETAP), 'N')
        INTO V_IDSEMIACETAP
        FROM ORDPRODUCAO O JOIN INSUMETAPA I
                             ON O.CDFILIAL = I.CDFILIAL
                            AND O.CDPRODUTO = I.CDPRODUTO
                            AND O.CDPROCFABRIC = I.CDPROCFABRIC
       WHERE O.CDFILIAL = P_CDFILIAL
         AND O.NRORDPROD = P_NRORDPROD
         AND O.NRSUBORDPROD = P_NRSUBORDPROD
         AND I.CDPRODINSUMO = P_CDPRODINSUMO;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IDSEMIACETAP := 'N';
   END;

   RETURN(V_IDSEMIACETAP);
END;
/
