create or replace PROCEDURE PRXLOTEPROD(
                              P_CDFILIAL   IN  FILIAL.CDFILIAL%TYPE,
                              P_DTVALILOTE IN  DATE,
                              P_CDPRODUTO  IN  PRODUTO.CDPRODUTO%TYPE,
                              P_RESULT     OUT VARCHAR2) AS

   V_STNUMLOTE    PARAMAN.DSNUMELOTEPR%TYPE;
   V_STSEQLOTE    PARAMAN.DSSEQULOTEPR%TYPE;
   V_CDPRODINTE   PRODUTO.CDPRODINTE%TYPE;
   V_STNUMERO     VARCHAR2(20);
   V_INAJUSTA     NUMBER;
   V_IDENTRSEQ    VARCHAR2(1);     /* Gera lote sequencial - entrada */
   V_STORDELOTE   VARCHAR2(50);   /* Ordenacao do Lote */
   V_NRLOTEESTQ   VARCHAR2(20);
   V_CDORDENALOTE VARCHAR2(20);
   V_COUNT        INTEGER;
   V_NRTAM        NUMBER;
   V_NRTAMSEQ     NUMBER;
   V_STDS         VARCHAR2(20);
   V_STDSSEQLOTE  VARCHAR2(50);
   V_CONTADOR     VARCHAR2(20);
   V_STORDENALOTE VARCHAR2(20);
   V_STATLOTE     LOTE.IDSTATLOTE%TYPE;
   V_SEQULTLOTE   VARCHAR(10);
   V_TAM          NUMBER;
   V_TAMAUX       NUMBER;
   V_CDEXT        NUMBER;
   V_ANO4         NUMBER;
   V_ANO2         NUMBER;
   V_MES          NUMBER;
   V_DIA          NUMBER;
   V_SEQ          NUMBER;
   V_FIL          NUMBER;
   V_TAMSEQ       NUMBER;
   V_TAMCDEXT     NUMBER;
   V_BARRA        NUMBER;
   V_TCDEXT       VARCHAR2(1);
   V_TANO4        VARCHAR2(1);
   V_TANO2        VARCHAR2(1);
   V_TMES         VARCHAR2(1);
   V_TDIA         VARCHAR2(1);
   V_TSEQ         VARCHAR2(1);
   V_TFIL         VARCHAR2(1);
   V_EANO4        VARCHAR2(4);
   V_EANO2        VARCHAR2(2);
   V_EMES         VARCHAR2(2);
   V_EDIA         VARCHAR2(2);

BEGIN
   V_COUNT        := 0;
   V_BARRA        := 0;
   V_NRLOTEESTQ   := NULL;
   V_SEQULTLOTE   := NULL;
   V_TAM := 1;
   V_TAMAUX := 1;
   V_TCDEXT := 'T';
   V_TANO4  := 'T';
   V_TANO2  := 'T';
   V_TMES   := 'T';
   V_TDIA   := 'T';
   V_TSEQ   := 'T';
   V_TFIL   := 'T';

   SELECT MAX(DSNUMELOTEPR),  MAX(DSSEQULOTEPR),
          MAX(IDENTRSEQLOTE), MAX(DSORDENALOTE)
   INTO V_STNUMLOTE, V_STSEQLOTE,
        V_IDENTRSEQ, V_STORDELOTE
   FROM PARAMAN M, PARAMFILIAL F
   WHERE (M.CDFILIAL  = P_CDFILIAL)  AND
         (M.CDFILIAL  = F.CDFILIAL)  AND
         (CDPARAMAN   = '1');

   BEGIN
      SELECT MAX(CDPRODINTE)
        INTO V_CDPRODINTE
        FROM PRODUTO
       WHERE
            (CDPRODUTO = P_CDPRODUTO);
   EXCEPTION
        WHEN NO_DATA_FOUND THEN
             V_CDPRODINTE := NULL;
   END;

   /* Tratativa realizado para o cliente Almeida Prado. Foi dado entrada em um produto de numero de lote
      8296519. Esse lote tem 7 digitos e termina com 19, o que atende aos requisitos de ultimo lote gerado da producao.
      O sistema estava buscando esse lote como ultimo gerado por causa disso, e jogava o sequencial para 8297/19.

      a tratativa foi para verificar se o lote buscado possui alguma '/', caso conste no sequencial do lote do cliente.
      No Caso da Almeida o sequencial era formado por: [SEQ4]/[ANO2].
   */
   BEGIN
      SELECT INSTR('V_STNUMLOTE', '/')
        INTO V_BARRA
        FROM DUAL;
   EXCEPTION
        WHEN NO_DATA_FOUND THEN
             V_BARRA := 0;
   END;

   V_STDSSEQLOTE := V_STORDELOTE;


   WHILE (V_TAMAUX < LENGTH(V_STNUMLOTE)) LOOP
      IF SUBSTR(V_STNUMLOTE, V_TAMAUX, 1) = '[' THEN
         IF (SUBSTR(V_STNUMLOTE, V_TAMAUX+1, 3) = 'MES') THEN

            IF SUBSTR(V_STNUMLOTE, V_TAMAUX-1, 1) IN( '-', '.', '/')  THEN
               V_MES := V_TAM+1;
            ELSE
               V_MES := V_TAM;
            END IF;

            V_TAM := V_MES+2;
            V_TMES := '';
            IF (INSTR(V_STSEQLOTE, 'MES') = 0) THEN
               V_EMES := '12';
            END IF;
         END IF;

         IF (SUBSTR(V_STNUMLOTE, V_TAMAUX+1, 3) = 'DIA') THEN
            IF SUBSTR(V_STNUMLOTE, V_TAMAUX-1, 1) IN( '-', '.', '/')  THEN
               V_DIA := V_TAM+1;
            ELSE
               V_DIA := V_TAM;
            END IF;

            V_TAM := V_DIA+2;
            V_TDIA := '';
            IF (INSTR(V_STSEQLOTE, 'DIA') = 0) THEN
               V_EDIA := '31';
            END IF;
         END IF;

         IF (SUBSTR(V_STNUMLOTE, V_TAMAUX+1, 3) = 'ANO') THEN
            IF SUBSTR(V_STNUMLOTE, INSTR(V_STNUMLOTE, 'ANO')+3, 1) = '2' THEN

               IF SUBSTR(V_STNUMLOTE, V_TAMAUX-1, 1) IN( '-', '.', '/')  THEN
                  V_ANO2 := V_TAM+1;
               ELSE
                  V_ANO2 := V_TAM;
               END IF;
               V_TAM  := V_ANO2+2;
               V_TANO2 := '';
               IF (INSTR(V_STSEQLOTE, 'ANO') = 0) THEN
                  V_EANO2 := TO_CHAR( P_DTVALILOTE, 'YY');
               END IF;
            ELSE
               IF SUBSTR(V_STNUMLOTE, V_TAMAUX-1, 1) IN( '-', '.', '/')  THEN
                  V_ANO4 := V_TAM+1;
               ELSE
                  V_ANO4 := V_TAM;
               END IF;
               V_TAM  := V_ANO4+4;
               V_TANO4 := '';
               IF (INSTR(V_STSEQLOTE, 'ANO') = 0) THEN
                  V_EANO4 := TO_CHAR( P_DTVALILOTE, 'YYYY');
               END IF;
            END IF;
         END IF;

         IF SUBSTR(V_STNUMLOTE, V_TAMAUX+1, 3) = 'SEQ' THEN

            IF SUBSTR(V_STNUMLOTE, V_TAMAUX-1, 1) IN( '-', '.', '/')  THEN
               V_SEQ := V_TAM +1;
            ELSE
               V_SEQ := V_TAM;
            END IF;
            V_TAMSEQ := TO_NUMBER(SUBSTR(V_STNUMLOTE, V_TAMAUX+4, 1));
            V_TAM := V_SEQ + V_TAMSEQ;
            V_TSEQ := '';
         END IF;

         IF SUBSTR(V_STNUMLOTE, V_TAMAUX+1, 3) = 'FIL' THEN
            IF SUBSTR(V_STNUMLOTE, V_TAMAUX-1, 1) IN( '-', '.', '/')  THEN
               V_FIL := V_TAM+1;
            ELSE
               V_FIL := V_TAM;
            END IF;

            V_TAM := V_FIL + 4;
            V_TFIL := '';
         END IF;

         IF (SUBSTR(V_STNUMLOTE, V_TAMAUX+1, 3) = 'CDE') THEN

            V_TAMCDEXT := NVL( LENGTH(V_CDPRODINTE), 0);
            IF (V_TAMCDEXT > 0) THEN

               IF SUBSTR(V_STNUMLOTE, V_TAMAUX-1, 1) IN( '-', '.', '/')  THEN
                   V_CDEXT := V_TAM+1;
               ELSE
                  V_CDEXT := V_TAM;
               END IF;

               V_TAM := V_CDEXT + LENGTH(V_CDPRODINTE);

               IF (INSTR(V_STSEQLOTE, 'CDEXT') > 0) THEN
                  V_TCDEXT := '';
               END IF;
            ELSE
               IF V_TAM = 1 THEN
                  V_TAM := 0;
               END IF;
            END IF;
         END IF;
      END IF;

      V_TAMAUX := V_TAMAUX + 1;
   END LOOP;

   V_TAM := V_TAM - 1;

   BEGIN
      SELECT
            MAX(SUBSTR( NRLOTEESTQ, V_SEQ, V_TAMSEQ  )), MAX(NRLOTEESTQ)
      INTO V_SEQULTLOTE, V_NRLOTEESTQ
      FROM LOTE
      WHERE
         ((SUBSTR( NRLOTEESTQ, V_CDEXT, LENGTH(V_CDPRODINTE)) = V_CDPRODINTE) OR (V_TCDEXT = 'T'))  AND
         ((TO_CHAR( P_DTVALILOTE, 'YY') = SUBSTR( NRLOTEESTQ, V_ANO2, 2)) OR (V_TANO2 = 'T') OR (V_EANO2 >= SUBSTR( NRLOTEESTQ, V_ANO2, 2))) AND
         ((TO_CHAR( P_DTVALILOTE, 'YYYY') = SUBSTR( NRLOTEESTQ, V_ANO4, 4)) OR (V_TANO4 = 'T') OR (V_EANO4 >= SUBSTR( NRLOTEESTQ, V_ANO4, 4))) AND
         ((TO_CHAR( P_DTVALILOTE, 'MM') = SUBSTR( NRLOTEESTQ, V_MES, 2)) OR (V_TMES = 'T') OR (V_EMES >= SUBSTR( NRLOTEESTQ, V_MES, 2))) AND
         ((TO_CHAR( P_DTVALILOTE, 'DD') = SUBSTR( NRLOTEESTQ, V_DIA, 2)) OR (V_TDIA = 'T') OR (V_EDIA >= SUBSTR( NRLOTEESTQ, V_DIA, 2))) AND
         ((SUBSTR( NRLOTEESTQ, V_FIL, 4) = P_CDFILIAL) OR (V_TFIL = 'T')) AND
         ((LENGTH(NRLOTEESTQ) = V_TAM)) AND
         ((CDPRODUTO = P_CDPRODUTO) OR (V_TCDEXT = 'T')) AND
         CDFILIAL = P_CDFILIAL AND
         (((INSTR(NRLOTEESTQ, '/') > 0) AND (V_BARRA > 0)) OR (V_BARRA = 0)) AND
         NRLOTEESTQ <> ' ' AND
         NRLOTEESTQ NOT IN ( SELECT NRLOTEESTQ
                               FROM LOTE M
                              WHERE
                                 M.CDFILIAL = P_CDFILIAL AND
                                 M.NRLOTEESTQ <> ' ' AND
                                 ((CDPRODUTO = P_CDPRODUTO) OR (V_TCDEXT = 'T')) AND
                                 ((INSTR( SUBSTR(M.NRLOTEESTQ, V_SEQ, V_TAMSEQ  ),'/') >0) OR
                                 ( INSTR( SUBSTR(M.NRLOTEESTQ, V_SEQ, V_TAMSEQ  ),'.') >0)OR
                                 ( INSTR( SUBSTR( M.NRLOTEESTQ, V_SEQ, V_TAMSEQ  ),']') >0 ))) AND
                                  SUBSTR( NRLOTEESTQ, V_SEQ, V_TAMSEQ  )= ( SELECT
                                                         MAX(SUBSTR( NRLOTEESTQ, V_SEQ, V_TAMSEQ  ))
                                                     FROM LOTE
                                                    WHERE
                                                       ((SUBSTR( NRLOTEESTQ, V_CDEXT, LENGTH(V_CDPRODINTE)) = V_CDPRODINTE) OR (V_TCDEXT = 'T'))  AND
                                                       ((TO_CHAR( P_DTVALILOTE, 'YY') = SUBSTR( NRLOTEESTQ, V_ANO2, 2)) OR (V_TANO2 = 'T') OR
                                                        (V_EANO2 >= SUBSTR( NRLOTEESTQ, V_ANO2, 2))) AND
                                                       ((TO_CHAR( P_DTVALILOTE, 'DD') = SUBSTR( NRLOTEESTQ, V_DIA, 2)) OR (V_TDIA = 'T') OR
                                                        (V_EDIA >= SUBSTR( NRLOTEESTQ, V_DIA, 2))) AND
                                                       ((TO_CHAR( P_DTVALILOTE, 'MM') = SUBSTR( NRLOTEESTQ, V_MES, 2)) OR (V_TMES = 'T') OR
                                                        (V_EMES >= SUBSTR( NRLOTEESTQ, V_MES, 2))) AND
                                                       ((TO_CHAR( P_DTVALILOTE, 'YYYY') = SUBSTR( NRLOTEESTQ, V_ANO4, 4)) OR (V_TANO4 = 'T') OR
                                                        (V_EANO4 >= SUBSTR( NRLOTEESTQ, V_ANO4, 4))) AND
                                                       ((SUBSTR( NRLOTEESTQ, V_FIL, 4) = P_CDFILIAL) OR (V_TFIL = 'T')) AND
                                                       ((LENGTH(NRLOTEESTQ) = V_TAM)) AND
                                                       ((CDPRODUTO = P_CDPRODUTO) OR (V_TCDEXT = 'T')) AND
                                                       CDFILIAL  = P_CDFILIAL ) AND
                                                       (((INSTR(NRLOTEESTQ, '/') > 0) AND (V_BARRA > 0)) OR (V_BARRA = 0)) AND
                                                       NRLOTEESTQ <> ' ' AND
                                                       NRLOTEESTQ NOT IN ( SELECT NRLOTEESTQ
                                                                             FROM LOTE M
                                                                            WHERE
                                                                               M.CDFILIAL = P_CDFILIAL AND
                                                                               M.NRLOTEESTQ <> ' ' AND
                                                                               ((CDPRODUTO = P_CDPRODUTO) OR (V_TCDEXT = 'T')) AND
                                                                               ((INSTR( SUBSTR(M.NRLOTEESTQ, V_SEQ, V_TAMSEQ  ),'/') >0) OR
                                                                               ( INSTR( SUBSTR(M.NRLOTEESTQ, V_SEQ, V_TAMSEQ  ),'.') >0)OR
                                                                               ( INSTR( SUBSTR( M.NRLOTEESTQ, V_SEQ, V_TAMSEQ  ),']') >0 )));
   EXCEPTION
      WHEN INVALID_NUMBER THEN
         V_SEQULTLOTE := NULL;
         V_NRLOTEESTQ := NULL;
   END;


/*Pesquisa se o ultimo lote criado possui OP ou Entrada */
   IF NOT (V_NRLOTEESTQ IS NULL) THEN
      SELECT COUNT(*)
      INTO V_COUNT
      FROM(SELECT NRLOTEESTQ
           FROM ITLANCTOEST
           WHERE
              CDFILIMOVI  = P_CDFILIAL     AND
              CDPRODUTO   = P_CDPRODUTO    AND
              NRLOTEESTQ  = V_NRLOTEESTQ
           UNION ALL
           SELECT NRLOTEESTQ
           FROM ORDPRODUCAO
           WHERE
              CDFILIAL    = P_CDFILIAL    AND
              NRLOTEESTQ  = V_NRLOTEESTQ   AND
              CDPRODUTO   = P_CDPRODUTO);
/*Verifica se o ultimo lote esta encerrado */
      BEGIN
         SELECT IDSTATLOTE
         INTO V_STATLOTE
         FROM LOTE
         WHERE
            CDFILIAL    = P_CDFILIAL    AND
            NRLOTEESTQ  = V_NRLOTEESTQ   AND
            CDPRODUTO   = P_CDPRODUTO;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_STATLOTE := 'E';
         END;

      IF V_STATLOTE = 'E' THEN
         V_COUNT := '1';
      END IF;

   END IF;

   /* SE POSSUIR LOTE E NAO ESTIVER RETORNA O ULTIMO LOTE */
   IF (NOT V_NRLOTEESTQ IS NULL) AND (V_IDENTRSEQ = 'S')AND (V_COUNT = 0) THEN
      P_RESULT := SUBSTR(V_NRLOTEESTQ, 1, 20);
   ELSE
/*Busca codigo externo do produto */

      V_STNUMLOTE := Replace(V_STNUMLOTE, '[ANO]'  , TO_CHAR(P_DTVALILOTE,'YYYY'));
      V_STNUMLOTE := Replace(V_STNUMLOTE, '[ANO2]' , TO_CHAR(P_DTVALILOTE,'YY'));
      V_STNUMLOTE := Replace(V_STNUMLOTE, '[ANO4]'  , TO_CHAR(P_DTVALILOTE,'YYYY'));
      V_STNUMLOTE := Replace(V_STNUMLOTE, '[MES]'  , TO_CHAR(P_DTVALILOTE,'MM'));
      V_STNUMLOTE := Replace(V_STNUMLOTE, '[DIA]'  , TO_CHAR(P_DTVALILOTE,'DD'));
      V_STNUMLOTE := Replace(V_STNUMLOTE, '[FIL]'  , P_CDFILIAL);

      IF LENGTH(V_CDPRODINTE) >0 THEN
         V_STNUMLOTE := Replace(V_STNUMLOTE, '[CDEXT]', V_CDPRODINTE);
      ELSE
         IF (INSTR(V_STNUMLOTE,'[CDEXT]-')=1) THEN
            V_STNUMLOTE := Replace(V_STNUMLOTE, '[CDEXT]-', '');
         ELSE
            IF (INSTR(V_STNUMLOTE,'[CDEXT]/')=1) THEN
               V_STNUMLOTE := Replace(V_STNUMLOTE, '[CDEXT]/', '');
            ELSE
               IF (INSTR(V_STNUMLOTE,'[CDEXT].')=1) THEN
                   V_STNUMLOTE := Replace(V_STNUMLOTE, '[CDEXT].', '');
               ELSE
                  IF (INSTR(V_STNUMLOTE,'-[CDEXT]')>0) THEN
                      V_STNUMLOTE := Replace(V_STNUMLOTE, '-[CDEXT]', '');
                  ELSE
                     IF (INSTR(V_STNUMLOTE,'/[CDEXT]')>0) THEN
                        V_STNUMLOTE := Replace(V_STNUMLOTE, '/[CDEXT]', '');
                     ELSE
                        IF (INSTR(V_STNUMLOTE,'.[CDEXT]')>0) THEN
                           V_STNUMLOTE := Replace(V_STNUMLOTE, '.[CDEXT]', '');
                        END IF;
                     END IF;
                  END IF;
               END IF;
            END IF;
         END IF;
      END IF;

      V_STSEQLOTE := Replace(V_STSEQLOTE, '[ANO]'  , TO_CHAR(P_DTVALILOTE,'YYYY'));
      V_STSEQLOTE := Replace(V_STSEQLOTE, '[ANO2]' , TO_CHAR(P_DTVALILOTE,'YY'));
      V_STSEQLOTE := Replace(V_STSEQLOTE, '[ANO4]' , TO_CHAR(P_DTVALILOTE,'YYYY'));
      V_STSEQLOTE := Replace(V_STSEQLOTE, '[MES]'  , TO_CHAR(P_DTVALILOTE,'MM'));
      V_STSEQLOTE := Replace(V_STSEQLOTE, '[DIA]'  , TO_CHAR(P_DTVALILOTE,'DD'));
      V_STSEQLOTE := Replace(V_STSEQLOTE, '[FIL]'  , P_CDFILIAL);
      V_STSEQLOTE := Replace(V_STSEQLOTE, '[CDEXT]', '');

      BEGIN
         SELECT LTRIM(NRSEQUENCIAL,0)
         INTO V_STDS
         FROM
            NOVOCODIGO
         WHERE
            CDCONTADOR  = V_STSEQLOTE;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_STDS := '000';
      END;


/* VERIFICA SE O SEQUENCIAL DO LOTE ESTA IGUAL AO SEQUENCIAL DO ULTIMO LOTE */
      IF TO_NUMBER(V_STDS) <> TO_NUMBER( NVL(V_SEQULTLOTE,'0')) THEN
         UPDATE NOVOCODIGO
         SET NRSEQUENCIAL = LPAD(NVL(V_SEQULTLOTE,'0'),20,'0')
         WHERE
            CDCONTADOR = V_STSEQLOTE;
      END IF;

      IF INSTR(V_STNUMLOTE,'[SEQ]') > 0 THEN
         V_INAJUSTA  := 10;
         NOVO_CODIGO_SC(V_STSEQLOTE, V_STNUMERO, 1);
         V_STNUMERO  := SUBSTR(V_STNUMERO,21-V_INAJUSTA,V_INAJUSTA);
         V_STNUMLOTE := Replace(V_STNUMLOTE, '[SEQ]', V_STNUMERO);
      ELSIF INSTR(V_STNUMLOTE,'[SEQ') > 0 THEN
         V_INAJUSTA  := TO_NUMBER(SUBSTR(V_STNUMLOTE, INSTR(V_STNUMLOTE,'[SEQ') + 4, 1));
         NOVO_CODIGO_SC(V_STSEQLOTE, V_STNUMERO, 1);

         V_STNUMERO  := SUBSTR(V_STNUMERO,21-V_INAJUSTA,V_INAJUSTA);
         V_STNUMLOTE := REPLACE(V_STNUMLOTE, '[SEQ'||TO_CHAR(V_INAJUSTA)||']', V_STNUMERO);
      END IF;

      P_RESULT := SUBSTR(V_STNUMLOTE, 1, 20);
   END IF;
END;
/