CREATE OR REPLACE PROCEDURE CONSULTA_LOTE(
   P_IDUTILLOTE   IN  PARAMFILIAL.IDUTILLOTE%TYPE,
   P_CDFILIAL     IN  LOTE.CDFILIAL%TYPE,
   P_CDALMOXARIFE IN  POSESTATU.CDALMOXARIFE%TYPE,
   P_CDLOCALESTOQ IN  POSESTATU.CDLOCALESTOQ%TYPE,
   P_CDPRODUTO    IN  LOTE.CDPRODUTO%TYPE,
   P_NRLOTEESTQ   OUT NoCopy LOTE.NRLOTEESTQ%TYPE,
   P_NRSUBLOTE    OUT NoCopy LOTE.NRSUBLOTE%TYPE)

AS

   V_LOTEESTQ     VARCHAR2(20);
   V_SUBLOTE      VARCHAR2(04);
   V_IDUTCTRLLOTE VARCHAR2(01);

   w_N            CHAR(1):= 'N';

BEGIN

   IF P_IDUTILLOTE =    w_N THEN
      P_NRLOTEESTQ := ' ';
      P_NRSUBLOTE  := ' ';
   ELSE
      Begin
        SELECT IDUTCTRLLOTE
        INTO   V_IDUTCTRLLOTE
        FROM PRODFILI
        WHERE (CDFILIAL = P_CDFILIAL) AND
              (CDPRODUTO = P_CDPRODUTO);
      Exception
        When No_Data_Found Then
          V_IDUTCTRLLOTE:= w_N;
      End;

      IF (V_IDUTCTRLLOTE = w_N) THEN
         P_NRLOTEESTQ := ' ';
         P_NRSUBLOTE  := ' ';
      ELSE
         SELECT NVL(MIN(NRLOTEESTQ), ' ')
         INTO   V_LOTEESTQ
         FROM POSESTATU
         WHERE CDFILIAL     = P_CDFILIAL     AND
               CDALMOXARIFE = P_CDALMOXARIFE AND
               CDLOCALESTOQ = P_CDLOCALESTOQ AND
               CDPRODUTO    = P_CDPRODUTO    AND
               QTTOTALESTOQ > 0;

         SELECT NVL(MAX(NRSUBLOTE), ' ')
         INTO   V_SUBLOTE
         FROM LOTE
         WHERE (CDFILIAL   = P_CDFILIAL) AND
               (NRLOTEESTQ = V_LOTEESTQ);

         P_NRLOTEESTQ := V_LOTEESTQ;
         P_NRSUBLOTE  := V_SUBLOTE;
      END IF;
   END IF;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('CONSULTA_LOTE','4.11.076'); END;
/

