IF EXISTS (SELECT * FROM SYSOBJECTS O, SYSUSERS U
           WHERE O.NAME = 'ESTOQUE_DISPONIVEL' AND
                 TYPE = 'FN' 		      AND
	              U.UID = O.UID           AND
	              U.NAME = USER)
DROP FUNCTION ESTOQUE_DISPONIVEL
GO

CREATE FUNCTION ESTOQUE_DISPONIVEL
   (@P_CDFILIAL  VARCHAR(4),
    @P_CDPRODUTO VARCHAR(10),
    @P_CDPRODINSUMO VARCHAR(10),
    @P_CDPROCFABRIC VARCHAR(10)) RETURNS DOUBLE PRECISION
AS
BEGIN
   DECLARE @V_VRTOTALEST   DOUBLE PRECISION;
   DECLARE @V_IDSTATUSAUDI VARCHAR(1);
   DECLARE @V_IDSTATLOTAUD VARCHAR(1);
   DECLARE @V_QTPRERETPROD DOUBLE PRECISION;
   DECLARE @V_IDUTLOCESTPRACA VARCHAR(1);
   DECLARE @V_RESULT       DOUBLE PRECISION;
   DECLARE @V_OPANDAMENTO  DOUBLE PRECISION;
   DECLARE @V_TEOR         DOUBLE PRECISION;

   DECLARE @C_SEMIACAB_CDPRODINSUMO VARCHAR(1);
   DECLARE @C_SEMIACAB_IDSEMIACETAP VARCHAR(1);
   DECLARE @C_SEMIACAB_IDUTAPRREQET VARCHAR(1);

   DECLARE @C_LOTE_CDFILIAL   VARCHAR(4);
   DECLARE @C_LOTE_CDPRODUTO  VARCHAR(10);
   DECLARE @C_LOTE_NRLOTEESTQ VARCHAR(20);
   DECLARE @C_LOTE_NRSUBLOTE   VARCHAR(4);
   DECLARE @C_LOTE_QTTOTALESTOQ DOUBLE PRECISION;
   DECLARE @C_LOTE_VRAPROVPROD DOUBLE PRECISION;


   SET @V_VRTOTALEST  = 0;
   SET @V_IDSTATLOTAUD = 'X';
   SET @V_OPANDAMENTO  = 0;

   SELECT @V_IDUTLOCESTPRACA = MAX(IDUTLOCESTPRACA)
     FROM PARAMAN
    WHERE CDFILIAL = @P_CDFILIAL;

   IF @V_IDUTLOCESTPRACA IS NULL BEGIN
      SET @V_IDUTLOCESTPRACA = 'N';
   END;

   DECLARE C_SEMIACAB CURSOR FOR
                               SELECT DISTINCT I.CDPRODINSUMO, ISNULL( IDSEMIACETAP, 'N') AS IDSEMIACETAP, I.IDUTAPRREQET
                                 FROM INSUMETAPA I, PROCFABRICA P
                                WHERE   P.CDPRODUTO    = @P_CDPRODUTO
                                    AND I.CDPRODINSUMO = @P_CDPRODINSUMO
                                    AND P.CDFILIAL     = @P_CDFILIAL
                                    AND P.CDPROCFABRIC = @P_CDPROCFABRIC
                                    AND I.CDPRODUTO    = P.CDPRODUTO
                                    AND I.CDPROCFABRIC = P.CDPROCFABRIC
                                    AND I.CDFILIAL     = P.CDFILIAL;
   OPEN C_SEMIACAB;
   FETCH NEXT FROM C_SEMIACAB INTO @C_SEMIACAB_CDPRODINSUMO, @C_SEMIACAB_IDSEMIACETAP, @C_SEMIACAB_IDUTAPRREQET;

   WHILE(@@FETCH_STATUS =0 )BEGIN

         DECLARE C_REG CURSOR FOR
                               SELECT P.CDFILIAL, P.CDPRODUTO, P.NRLOTEESTQ, P.NRSUBLOTE, P.QTTOTALESTOQ, L.VRAPROVPROD
                               FROM POSESTATU P, PRODFILI F, LOTE L
                               WHERE P.CDFILIAL = @P_CDFILIAL
                                 AND P.QTTOTALESTOQ > 0
                                 AND F.CDFILIAL = P.CDFILIAL
                                 AND P.CDPRODUTO = @P_CDPRODINSUMO
                                 AND F.CDPRODUTO = P.CDPRODUTO
                                 AND((ISNULL(P.CDALMOXARIFE,' ') = ISNULL(F.CDALMOXINSPR,' ')    AND ISNULL(P.CDLOCALESTOQ,' ') = ISNULL(F.CDLOCALINSPR,' ')    AND @C_SEMIACAB_IDSEMIACETAP = 'N')OR
                                     (ISNULL(P.CDALMOXARIFE,' ') = ISNULL(F.CDALMOXPRSEMACA,' ') AND ISNULL(P.CDLOCALESTOQ,' ') = ISNULL(F.CDLOCALPRSEMACA,' ') AND @C_SEMIACAB_IDSEMIACETAP = 'S'))
                                 AND @V_IDUTLOCESTPRACA = 'N'
                                 AND P.CDFILIAL = L.CDFILIAL
                                 AND P.NRLOTEESTQ = L.NRLOTEESTQ
                                 AND P.NRSUBLOTE = L.NRSUBLOTE
                                 AND P.CDPRODUTO = L.CDPRODUTO
                               UNION
                               SELECT P.CDFILIAL, P.CDPRODUTO, P.NRLOTEESTQ, P.NRSUBLOTE, P.QTTOTALESTOQ, L.VRAPROVPROD
                               FROM POSESTATU P, PRODFILI F, LOTE L
                               WHERE P.CDFILIAL = @P_CDFILIAL
                                 AND P.QTTOTALESTOQ > 0
                                 AND F.CDFILIAL = P.CDFILIAL
                                 AND P.CDPRODUTO = @P_CDPRODINSUMO
                                 AND F.CDPRODUTO = @P_CDPRODUTO
                                 AND((ISNULL(P.CDALMOXARIFE,' ') = ISNULL(F.CDALMOXPRSEMACA,' ') AND ISNULL(P.CDLOCALESTOQ,' ') = ISNULL(F.CDLOCALPRSEMACA,' ') AND @C_SEMIACAB_IDSEMIACETAP = 'S')OR
                                     (ISNULL(P.CDALMOXARIFE,' ') = ISNULL(F.CDALMOXINSPR,' ')    AND ISNULL(P.CDLOCALESTOQ,' ') = ISNULL(F.CDLOCALINSPR   ,' ') AND @C_SEMIACAB_IDSEMIACETAP = 'N'))
                                 AND @V_IDUTLOCESTPRACA = 'S'
                                 AND P.CDFILIAL = L.CDFILIAL
                                 AND P.NRLOTEESTQ = L.NRLOTEESTQ
                                 AND P.NRSUBLOTE = L.NRSUBLOTE
                                 AND P.CDPRODUTO = L.CDPRODUTO;

      OPEN C_REG;
      FETCH NEXT FROM C_REG INTO @C_LOTE_CDFILIAL, @C_LOTE_CDPRODUTO, @C_LOTE_NRLOTEESTQ, @C_LOTE_NRSUBLOTE, @C_LOTE_QTTOTALESTOQ, @C_LOTE_VRAPROVPROD;

      WHILE(@@FETCH_STATUS =0 ) BEGIN
         IF(@V_IDSTATLOTAUD = 'X') BEGIN
            SELECT @V_IDSTATUSAUDI= MAX(IDSTATUSAUDI)
            FROM AUDITORIA
            WHERE CDFILIAL = @C_LOTE_CDFILIAL
	      AND IDORIGAUDIT = 'ANA'            	
              AND NRAUDITORIA = (SELECT MAX(NRAUDITORIA)
                                 FROM AUDITORIA
                                 WHERE CDFILIAL  = @C_LOTE_CDFILIAL
                                   AND CDPRODUTO  = @C_LOTE_CDPRODUTO
                                   AND NRLOTEESTQ = ISNULL(@C_LOTE_NRLOTEESTQ,' ')
                                   AND NRSUBLOTE  = ISNULL(@C_LOTE_NRSUBLOTE,' ')
				   AND IDORIGAUDIT = 'ANA')
              AND IDSTATUSAUDI <> '4';

            IF (@V_IDSTATUSAUDI IS NULL) BEGIN
               IF (@C_SEMIACAB_IDUTAPRREQET = 'S') BEGIN
                  IF (ISNULL(@C_LOTE_VRAPROVPROD,0) = 0) BEGIN
                     SET @V_TEOR = 100;
                  END
                  ELSE BEGIN
                     SET @V_TEOR = @C_LOTE_VRAPROVPROD;
                  END;
               END
               ELSE BEGIN
                  SET @V_TEOR = 100;
               END;

               SET @V_VRTOTALEST = (@V_VRTOTALEST + ((@C_LOTE_QTTOTALESTOQ *@V_TEOR)/100));

               SELECT @V_QTPRERETPROD = ISNULL(SUM(L.QTRETLIQPROD),0)
               FROM ORDPRODUCAO O, RETIRADAESTO R, ITEMRETIEST I, LOTITRETIEST L
               WHERE O.CDFILIAL = @P_CDFILIAL
               AND O.IDSITORDPROD = '2'
               AND O.CDFILIAL  = R.CDFILIAL
               AND O.NRORDPROD = R.NRORDPROD
               AND O.NRSUBORDPROD = R.NRSUBORDPROD
               AND R.NRORDPROD IS NOT NULL
               AND R.NRLANCESTQ IS NULL
               AND R.CDFILIAL = I.CDFILIAL
               AND R.NRRETEST = I.NRRETEST
               AND I.CDPRODUTO = @P_CDPRODINSUMO
               AND I.CDFILIAL  = L.CDFILIAL
               AND I.NRRETEST  = L.NRRETEST
               AND I.CDPRODUTO = L.CDPRODUTO
               AND I.NRSEQRETPROD = L.NRSEQRETPROD
               AND L.IDTPRETEST = 'R'
               AND ISNULL(L.NRLOTEESTQ,' ') = ISNULL(@C_LOTE_NRLOTEESTQ,' ')
               AND ISNULL(L.NRSUBLOTE,' ')  = ISNULL(@C_LOTE_NRSUBLOTE,' ');

               SET @V_OPANDAMENTO = @V_OPANDAMENTO + @V_QTPRERETPROD;
            END;
         END;
         FETCH NEXT FROM C_REG INTO @C_LOTE_CDFILIAL, @C_LOTE_CDPRODUTO, @C_LOTE_NRLOTEESTQ, @C_LOTE_NRSUBLOTE, @C_LOTE_QTTOTALESTOQ, @C_LOTE_VRAPROVPROD;

      END;
      CLOSE C_REG;
      DEALLOCATE C_REG;

      SET @V_VRTOTALEST = @V_VRTOTALEST - @V_OPANDAMENTO;
      FETCH NEXT FROM C_SEMIACAB INTO  @C_SEMIACAB_CDPRODINSUMO, @C_SEMIACAB_IDSEMIACETAP, @C_SEMIACAB_IDUTAPRREQET;
   END;
   CLOSE C_SEMIACAB;
   DEALLOCATE C_SEMIACAB;
   RETURN(@V_VRTOTALEST);
END
GO

-- VERSAO --
EXEC ATUVEROBJ 'ESTOQUE_DISPONIVEL','5.07.004';
GO
