CREATE OR REPLACE PROCEDURE LIBERA_LOTE_VND(
   P_CDFILIAL   IN FILIAL.CDFILIAL%TYPE,
   P_CDCAIXA    IN CAIXA.CDCAIXA%TYPE,
   P_DTREDUZ    IN DATE,
   P_DTEMISSAO  IN DATE,
   P_NRLANC     IN LANCCONT.NRLANCCONT%TYPE,
   P_CDOPERADOR IN LANCCONT.CDOPERINS%TYPE,
   P_CDLOJA     IN CAIXA.CDLOJA%TYPE DEFAULT NULL)
IS
   V_NRLANCLOTE LANCLOTE.NRLANCLOTE%TYPE;
   V_NRLANCCONT LANCCONT.NRLANCCONT%TYPE;
   V_IDCONTGERE CONTOPECTBL.IDCONTGERE%TYPE;
   V_IDCONTCOME CONTOPECTBL.IDCONTCOME%TYPE;
   V_IDLANCINTE VARCHAR2(1);
   V_SEQUENCIAL VARCHAR2(5);
BEGIN
   V_NRLANCCONT := P_NRLANC;
   V_NRLANCLOTE := '0';
   V_SEQUENCIAL := '00001';

   FOR C_RELAC IN (SELECT R.CDEMPRESA, R.CDLOTE, R.NRLANCLOTE, R.NRSEQLANLOTE, R.CDFILIAL, R.NRLANCTONF
                     FROM RELACNFLANCLOTE R, NOTAFISCAL N
                    WHERE R.CDFILIALVND = P_CDFILIAL
                      AND NVL(R.CDCAIXA,'T') = NVL(P_CDCAIXA,'T')
                      AND NVL(R.CDLOJA,'T')  = NVL(P_CDLOJA,'T')
                      AND R.DTREDUCAOZ  = P_DTREDUZ
                      AND R.DTEMISSAO   = P_DTEMISSAO
                      AND R.CDFILIAL    = N.CDFILIAL
                      AND R.NRLANCTONF  = N.NRLANCTONF
                      AND N.NRLANCTBDOC IS NULL) LOOP

      FOR LOTE IN (SELECT LANCLOTE.*
                     FROM LANCLOTE
                    WHERE LANCLOTE.CDEMPRESA  = C_RELAC.CDEMPRESA
                      AND LANCLOTE.CDLOTE     = C_RELAC.CDLOTE
                      AND LANCLOTE.NRLANCLOTE = C_RELAC.NRLANCLOTE
                    ORDER BY  NRLANCLOTE, NRSEQLANLOTE) LOOP

         V_IDLANCINTE := 'I';

         INSERT INTO LANCCONT
            (CDEMPRESA, NRLANCCONT, NRSEQLANC, CDCONTCTBL, CDHISTPADR,
             DTLANCCONT, CDDOCUCONT, IDTIPOLANC, DSHISTLANC, VRLANCAMENT,
             IDLANCCOME, CDCONTCOME, IDLANCSALDO, IDLANCINTE, IDLANCMANI,
             CDOPERINS, DTULTATULAN, IDSALDLANCO, IDSALDLANGE, IDSISTEMA)
         VALUES
            (C_RELAC.CDEMPRESA, V_NRLANCCONT, V_SEQUENCIAL, LOTE.CDCONTCTBL,
             LOTE.CDHISTPADR, LOTE.DTLANCLOTE, LOTE.NRDOCULOTE, 'C',
             LOTE.DSHISTLOTE, LOTE.VRLANCLOTE, LOTE.IDLLOTCOME,
             LOTE.CDLLOTCOME, 'N',V_IDLANCINTE,'N', P_CDOPERADOR, SYSDATE, 'N', 'N', 'VND');

         UPDATE LANCLOTE
            SET LANCLOTE.NRLANCDEFI   = V_NRLANCCONT,
                LANCLOTE.NRSEQUDEFI   = V_SEQUENCIAL
          WHERE LANCLOTE.CDEMPRESA    = LOTE.CDEMPRESA
            AND LANCLOTE.CDLOTE       = LOTE.CDLOTE
            AND LANCLOTE.NRLANCLOTE   = LOTE.NRLANCLOTE
            AND LANCLOTE.NRSEQLANLOTE = LOTE.NRSEQLANLOTE;

         INSERT INTO LANCCCUSTO
            (CDEMPRESA, NRLANCCONT, NRSEQLANC, CDFILIAL, CDCENTCUST, VRLANCCUST, DTCOMPCUSTO)
         (SELECT C_RELAC.CDEMPRESA, V_NRLANCCONT, V_SEQUENCIAL,
                 RATECCUS.CDFILIAL, RATECCUS.CDCENTCUST, RATECCUS.VRRATECCUS, RATECCUS.DTCOMPRATECCUS
            FROM RATECCUS
           WHERE RATECCUS.CDEMPRESA    = LOTE.CDEMPRESA
             AND RATECCUS.CDLOTE       = LOTE.CDLOTE
             AND RATECCUS.NRLANCLOTE   = LOTE.NRLANCLOTE
             AND RATECCUS.NRSEQLANLOTE = LOTE.NRSEQLANLOTE);

         V_NRLANCLOTE := LOTE.NRLANCLOTE;
         V_SEQUENCIAL := LPAD(TO_CHAR(TO_NUMBER(V_SEQUENCIAL) + 1), 5, '0');
      END LOOP;

      UPDATE NOTAFISCAL
         SET NRLANCTBDOC = V_NRLANCCONT
       WHERE CDFILIAL    = C_RELAC.CDFILIAL
         AND NRLANCTONF  = C_RELAC.NRLANCTONF;
  END LOOP;
END;
/