CREATE OR REPLACE FUNCTION DTPOSICAO_POSESTDIA_SREF(P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,
                                                    P_CDPRODUTO    IN PRODUTO.CDPRODUTO%TYPE,
                                                    P_CDALMOXARIFE IN ALMOXARIFADO.CDALMOXARIFE%TYPE,
                                                    P_CDLOCALESTOQ IN LOCALESTOQUE.CDLOCALESTOQ%TYPE,
                                                    P_NRLOTEESTQ   IN LOTE.NRLOTEESTQ%TYPE,
                                                    P_NRSUBLOTE    IN LOTE.NRSUBLOTE%TYPE) RETURN DATE
AS
   V_CDALMOXARIFE  ALMOXARIFADO.CDALMOXARIFE%TYPE;
   V_CDLOCALESTOQ  LOCALESTOQUE.CDLOCALESTOQ%TYPE;
   V_NRLOTEESTQ    LOTE.NRLOTEESTQ%TYPE;
   V_NRSUBLOTE     LOTE.NRSUBLOTE%TYPE;
   V_DTPOSIESTQ    DATE;
BEGIN
   V_CDALMOXARIFE := NVL(P_CDALMOXARIFE, ' ');
   V_CDLOCALESTOQ := NVL(P_CDLOCALESTOQ, ' ');
   V_NRLOTEESTQ   := NVL(P_NRLOTEESTQ, ' ');
   V_NRSUBLOTE    := NVL(P_NRSUBLOTE, ' ');
   V_DTPOSIESTQ   := NULL;

   SELECT DTPOSIESTQ
     INTO V_DTPOSIESTQ
     FROM (SELECT P.DTPOSIESTQ
             FROM POSESTDIA P
            WHERE P.CDFILIAL     = P_CDFILIAL
              AND P.CDPRODUTO    = P_CDPRODUTO
              AND P.CDALMOXARIFE = V_CDALMOXARIFE
              AND P.CDLOCALESTOQ = V_CDLOCALESTOQ
              AND P.NRLOTEESTQ   = V_NRLOTEESTQ
              AND P.NRSUBLOTE    = V_NRSUBLOTE
            ORDER BY 1 DESC)
    WHERE ROWNUM = 1;

   RETURN(V_DTPOSIESTQ);
END;
/
