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

CREATE FUNCTION POSICAO_ESTOQ(
   @P_FILIAL     VARCHAR(04),
   @P_PRODUTO    VARCHAR(10),
   @P_DTPOSICAO  DATETIME,
   @P_ALMOXARIFE VARCHAR(06),
   @P_LOCALESTOQ VARCHAR(06)) RETURNS NUMERIC(10,5)
AS
BEGIN
   DECLARE @V_QTDEESTO NUMERIC;
   
   SET @V_QTDEESTO = 0;
   
      SELECT @V_QTDEESTO = SUM(ISNULL(QTESTOQDIA,0))
        FROM POSESTDIA P,
	         (SELECT CDFILIAL, CDALMOXARIFE, CDLOCALESTOQ, NRLOTEESTQ, 
		             NRSUBLOTE, CDPRODUTO, MAX(DTPOSIESTQ) AS DTPOSIESTQ
                FROM POSESTDIA
               WHERE (CDFILIAL = @P_FILIAL)
			     AND (CDALMOXARIFE = @P_ALMOXARIFE) 
			     AND (CDLOCALESTOQ = @P_LOCALESTOQ)  
			     AND (CDPRODUTO = @P_PRODUTO)    
			     AND (DTPOSIESTQ < @P_DTPOSICAO)
			   GROUP BY CDFILIAL, CDALMOXARIFE, CDLOCALESTOQ, NRLOTEESTQ, 
		                NRSUBLOTE, CDPRODUTO) X
       WHERE (P.CDFILIAL = X.CDFILIAL)
	     AND (P.CDALMOXARIFE = X.CDALMOXARIFE) 
	     AND (P.CDLOCALESTOQ = X.CDLOCALESTOQ) 
	     AND (P.NRLOTEESTQ = X.NRLOTEESTQ)   
	     AND (P.NRSUBLOTE = X.NRSUBLOTE)    
	     AND (P.CDPRODUTO = X.CDPRODUTO)    
	     AND (P.DTPOSIESTQ = X.DTPOSIESTQ);

   RETURN(@V_QTDEESTO);
END;
GO

-- VERSAO --
EXEC ATUVEROBJ 'POSICAO_ESTOQ','5.25.001';
GO
