CREATE OR REPLACE PROCEDURE GERA_LOTECONTABIL(
  P_CDEMPRESA  IN EMPRESA.CDEMPRESA%TYPE, 
  P_TIPOMOVI   IN VARCHAR2,
  P_CDFILIEMIT IN FILIAL.CDFILIAL%TYPE,
  P_CDFILIRECE IN FILIAL.CDFILIAL%TYPE,
  P_DTMOVI     IN DATE,
  P_TIPOOPERAC IN VARCHAR2,
  P_TPCREDDEB  IN VARCHAR2,
  P_CDLOTE     OUT VARCHAR2,
  P_NRLANCLOTE OUT VARCHAR2,
  P_CODLOTE    IN VARCHAR2 DEFAULT NULL)
IS
  V_CDLOTE     VARCHAR2(20);
  V_CDLOTE1    VARCHAR2(20);
  V_SEQUENCIAL VARCHAR2(20);
  V_NRLANCLOTE VARCHAR2(20);
  V_EXISTELOTE VARCHAR2(20);
  V_DTINI      DATE;
  V_DTFIN      DATE;
BEGIN
  CASE P_TIPOMOVI
    WHEN '00' THEN BEGIN
                     SELECT CDREFLOTECTBTO
                       INTO V_CDLOTE
                       FROM TIPOOPERAC A
                      WHERE (CDTIPOOPER = P_TIPOOPERAC)
                        AND (IDENTRSAIDOP = 'E');
                   EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                       V_CDLOTE := NULL;
                   END;
    WHEN '01' THEN BEGIN
                     SELECT CDREFLOTECTBTO
                       INTO V_CDLOTE
                       FROM TIPOOPERAC A
                      WHERE (CDTIPOOPER = P_TIPOOPERAC)
                        AND (IDENTRSAIDOP = 'S');
                   EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                       V_CDLOTE := NULL;
                   END;

    WHEN '02' THEN BEGIN
                     SELECT CDREFLOTECTBRET
                       INTO V_CDLOTE
                       FROM  PARAMESTOQUE;
                   EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                       V_CDLOTE := NULL;
                   END;

    WHEN '03' THEN BEGIN
                     SELECT CDREFLOTECTBPRO
                       INTO V_CDLOTE
                       FROM PARAMESTOQUE;
                   EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                       V_CDLOTE := NULL;
                   END;

    WHEN '04' THEN BEGIN
                     SELECT CDREFLOTECTBSAI
                       INTO V_CDLOTE
                       FROM  PARAMESTOQUE;
                   EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                       V_CDLOTE := NULL;
                   END;

    WHEN '05' THEN BEGIN
                     SELECT CDREFLOTECTBIMP
                       INTO V_CDLOTE
                       FROM  PARAMESTOQUE;
                   EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                       V_CDLOTE := NULL;
                   END;

    WHEN '06' THEN BEGIN
                     SELECT CDREFLOTECTBTP
                       INTO V_CDLOTE
                       FROM TIPOCTPAGAR
                      WHERE CDTPCTPAGAR = P_TIPOOPERAC;
                   EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                       V_CDLOTE := NULL;
                   END;

    WHEN '07' THEN BEGIN
                     SELECT CDREFLOTECTBTR
                       INTO V_CDLOTE
                       FROM TIPOCTRECEBER
                      WHERE CDTPCTRECEB = P_TIPOOPERAC;
                   EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                       V_CDLOTE := NULL;
                   END;

    WHEN '08' THEN IF P_TPCREDDEB = 'C' THEN
                       BEGIN
                         SELECT CDREFLOTECTBTCB
                           INTO V_CDLOTE
                           FROM TPCREDBANC
                          WHERE CDTPCREDBANC = P_TIPOOPERAC;
                       EXCEPTION
                         WHEN NO_DATA_FOUND THEN
                           V_CDLOTE := NULL;
                       END;
                   ELSE
                       BEGIN
                         SELECT CDREFLOTECTBTDB
                           INTO V_CDLOTE
                           FROM TPDEBIBANC
                          WHERE CDTPDEBIBANC = P_TIPOOPERAC;
                       EXCEPTION
                         WHEN NO_DATA_FOUND THEN
                          V_CDLOTE := NULL;
                       END;
                   END IF;
    WHEN '09' THEN BEGIN
                     SELECT CDREFLOTECTBL
                       INTO V_CDLOTE
                       FROM PARAMCTBL;
                     V_CDLOTE := V_CDLOTE;
                   EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                       V_CDLOTE := NULL;
                   END;
    WHEN '10' THEN BEGIN
                     SELECT CDREFLOTECTBTBP
                       INTO V_CDLOTE
                       FROM TIPOBAIXAPAG
                      WHERE CDTPBAIXAPAG = P_TIPOOPERAC;
                   EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                       V_CDLOTE := NULL;
                   END;

    WHEN '11' THEN BEGIN
                     SELECT CDREFLOTECTBTBR
                       INTO V_CDLOTE
                       FROM TIPOBAIXAREC
                      WHERE CDTPBAIXAREC = P_TIPOOPERAC;
                   EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                       V_CDLOTE := NULL;
                   END;

    WHEN '12' THEN BEGIN
                     SELECT CDREFLOTECTBTR
                       INTO V_CDLOTE
                       FROM TIPOCTRECEBER
                      WHERE CDTPCTRECEB = P_TIPOOPERAC;
                   EXCEPTION
                      WHEN NO_DATA_FOUND THEN
                       V_CDLOTE := NULL;
                   END;
  END CASE;

  IF V_CDLOTE IS NULL THEN
     BEGIN
       SELECT CDREFLOTECTBL
         INTO V_CDLOTE
         FROM PARAMCTBL;
     EXCEPTION
       WHEN NO_DATA_FOUND THEN
        V_CDLOTE := NULL;
     END;
     IF P_CODLOTE IS NOT NULL THEN
        V_CDLOTE := P_CODLOTE;
     ELSE
        V_CDLOTE := V_CDLOTE || P_TIPOMOVI;
     END IF;
  END IF;

  V_CDLOTE := REPLACE(V_CDLOTE,'[EMP]',P_CDEMPRESA);

  V_CDLOTE := REPLACE(V_CDLOTE,'[FIEM]',P_CDFILIEMIT);

  V_CDLOTE := REPLACE(V_CDLOTE,'[FIRE]',P_CDFILIRECE);

  V_CDLOTE := REPLACE(V_CDLOTE,'[ANO]',SUBSTR(TO_CHAR(P_DTMOVI,'DD/MM/YYYY'),7,4));

  V_CDLOTE := REPLACE(V_CDLOTE,'[MES]',SUBSTR(TO_CHAR(P_DTMOVI,'DD/MM/YYYY'),4,2));

  V_CDLOTE := REPLACE(V_CDLOTE,'[DIA]',SUBSTR(TO_CHAR(P_DTMOVI,'DD/MM/YYYY'),1,2));

  IF (INSTR(V_CDLOTE,'[SEQ]')>0) THEN
     SELECT MAX(CDLOTE)
       INTO V_CDLOTE1
       FROM CAPALOTE
      WHERE CDEMPRESA = P_CDEMPRESA
        AND IDLOTE <> 'L'
        AND CDLOTE LIKE REPLACE(V_CDLOTE,'[SEQ]','') || '%';

     IF V_CDLOTE1 IS NULL THEN
        NOVO_CODIGO_NT(V_CDLOTE,V_SEQUENCIAL,1);
        V_CDLOTE := REPLACE(V_CDLOTE,'[SEQ]',SUBSTR(V_SEQUENCIAL,17,4));
     ELSE
        V_CDLOTE := V_CDLOTE1;
     END IF;
  END IF;

  NOVO_CODIGO_NT('LANCLOTE'||P_CDEMPRESA||V_CDLOTE, V_NRLANCLOTE);
  V_NRLANCLOTE := SUBSTR(V_NRLANCLOTE,15,6);

  BEGIN
    SELECT CDLOTE
      INTO V_EXISTELOTE
      FROM CAPALOTE
     WHERE CDEMPRESA = P_CDEMPRESA
       AND CDLOTE = V_CDLOTE;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      V_EXISTELOTE := NULL;
  END;

  IF P_TIPOMOVI IN ('06','07','08','09','10','11') THEN
     BEGIN
       SELECT DTINICCTBL,DTFINACCTBL
         INTO V_DTINI, V_DTFIN
         FROM CONTOPECTBL
        WHERE CDEMPRESA = P_CDEMPRESA;
     EXCEPTION
       WHEN NO_DATA_FOUND THEN
        V_DTINI := NULL;
        V_DTFIN := NULL;
     END;
  ELSIF (P_TIPOMOVI = '01') OR (P_TIPOMOVI = '12')  THEN
     BEGIN
        SELECT DTINIINTCONTDFS, DTFININTCONTDFS
          INTO V_DTINI, V_DTFIN
          FROM CONTOPECTBL
         WHERE CDEMPRESA = P_CDEMPRESA;
     EXCEPTION
        WHEN NO_DATA_FOUND THEN
         V_DTINI := NULL;
         V_DTFIN := NULL;
     END;
  ELSE
     BEGIN
        SELECT DTINIINTCONTDFE, DTFININTCONTDFE
          INTO V_DTINI, V_DTFIN
          FROM CONTOPECTBL
         WHERE CDEMPRESA = P_CDEMPRESA;
     EXCEPTION
        WHEN NO_DATA_FOUND THEN
         V_DTINI := NULL;
         V_DTFIN := NULL;
     END;
  END IF;

  IF V_EXISTELOTE IS NULL THEN
    INSERT INTO CAPALOTE
      (CDEMPRESA, CDLOTE, DTINICLOTE, DTFINALOTE,IDTPMOVLOTE,IDLOTE, DTULTATU)
    VALUES
      (P_CDEMPRESA, V_CDLOTE, V_DTINI, V_DTFIN, P_TIPOMOVI, 'D', SYSDATE);
  END IF;

  P_CDLOTE     := V_CDLOTE;
  P_NRLANCLOTE := V_NRLANCLOTE;
END;
/