CREATE OR REPLACE FUNCTION POSICAO_ESTOQ(
   P_FILIAL     IN POSESTDIA.CDFILIAL%TYPE,
   P_PRODUTO    IN POSESTDIA.CDPRODUTO%TYPE,
   P_DTPOSICAO  IN POSESTDIA.DTPOSIESTQ%TYPE,
   P_ALMOXARIFE IN ALMOXARIFADO.CDALMOXARIFE%TYPE,
   P_LOCALESTOQ IN LOCALESTOQUE.CDLOCALESTOQ%TYPE) RETURN NUMBER IS

   V_QTDEESTO NUMBER;
   
BEGIN

   BEGIN
      SELECT SUM(NVL(QTESTOQDIA,0)) AS QTDEESTO
        INTO V_QTDEESTO
        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);
	EXCEPTION
	   WHEN NO_DATA_FOUND THEN
	      NULL;
	   V_QTDEESTO := 0;
    END;	
					
   RETURN(V_QTDEESTO);

END;
/

-- VERSAO --
BEGIN ATUVEROBJ('POSICAO_ESTOQ','5.25.001'); END;
/
