/*

@C:\WEB_501\SQL\END_CONV\ORACLE\0012_GERSTPO_CONSULTA_POSICAO.sql

raise_application_error(-20501, '' , True);

*/

CREATE OR REPLACE PROCEDURE CONSULTA_POSICAO(
   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,
   P_LOTEESTQ   IN LOTE.NRLOTEESTQ%TYPE,
   P_SUBLOTE    IN LOTE.NRSUBLOTE%TYPE,
   P_POSNZERO   IN POSESTDIA.IDPOSIZERO%TYPE,
   P_QTDEESTO   OUT NUMBER,
   P_VALRESTO   OUT NUMBER,
   P_VALRBRUT   OUT NUMBER)

AS
   V_QTDEESTO     NUMBER;
   V_VALRESTO     NUMBER;
   V_VALRBRUT     NUMBER;
   V_QTDEANTE     NUMBER;
   V_VALRANTE     NUMBER;
   V_VRBRANTE     NUMBER;

BEGIN
      SELECT NVL(MAX(QTESTOQDIA),0)           AS QTDEESTO,
             NVL(MAX(VRESTOQDIA),0)           AS VALRESTO,
             ROUND(NVL(MAX(VRESTOQBRUT),0),5) AS VALRBRUT,
             ROUND(NVL(MAX(QTESTOQANT),0),5)  AS QTDEANTE,
             ROUND(NVL(MAX(VRESTOQANT),0),5)  AS VALRANTE,
             ROUND(NVL(MAX(VRBRUTOANT),0),5)  AS VRBRANTE
      INTO V_QTDEESTO,
           V_VALRESTO,
           V_VALRBRUT,
           V_QTDEANTE,
           V_VALRANTE,
           V_VRBRANTE
      FROM POSESTDIA P
      WHERE (CDFILIAL     = P_FILIAL)     AND
            (CDALMOXARIFE = P_ALMOXARIFE) AND
            (CDLOCALESTOQ = P_LOCALESTOQ) AND
            (NRLOTEESTQ   = P_LOTEESTQ)   AND
            (NRSUBLOTE    = P_SUBLOTE)    AND
            (CDPRODUTO    = P_PRODUTO)    AND
            (DTPOSIESTQ = (SELECT MAX(DTPOSIESTQ)
                           FROM POSESTDIA
                           WHERE (CDFILIAL     = P_FILIAL)     AND
                                 (CDALMOXARIFE = P_ALMOXARIFE) AND
                                 (CDLOCALESTOQ = P_LOCALESTOQ) AND
                                 (NRLOTEESTQ   = P_LOTEESTQ)   AND
                                 (NRSUBLOTE    = P_SUBLOTE)    AND
                                 (CDPRODUTO    = P_PRODUTO)    AND
                                 (DTPOSIESTQ  <= P_DTPOSICAO)));


   IF (P_POSNZERO = 'S') AND (V_QTDEESTO = 0) THEN
      P_QTDEESTO := V_QTDEANTE;
      P_VALRESTO := V_VALRANTE;
      P_VALRBRUT := V_VRBRANTE;
   ELSE
      P_QTDEESTO := V_QTDEESTO;
      P_VALRESTO := V_VALRESTO;
      P_VALRBRUT := V_VALRBRUT;
   END IF;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('CONSULTA_POSICAO','5.01.007'); END;
/

