CREATE OR REPLACE PROCEDURE GERA_LOTE_CMV
   (P_CDEMPRESA  IN EMPRESA.CDEMPRESA%TYPE,
    P_CDFILIAL   IN FILIAL.CDFILIAL%TYPE,
    P_DTREFEMES  IN POSESTCONS.DTPOSESTCON%TYPE,
    P_CDLOTE     IN CAPALOTE.CDLOTE%TYPE)

IS
   V_CDCONTREDU  VARCHAR2(20);
   V_CDCONTCTBL  VARCHAR2(20);
   V_CDHISTPADR  VARCHAR2(04);
   V_DSHISTLOTE  VARCHAR2(200);
   V_IDCONTCCUS  VARCHAR2(01);

BEGIN

   INSERT INTO CAPALOTE
      (CDEMPRESA, CDLOTE, DTINICLOTE, DTFINALOTE)
   VALUES
      (P_CDEMPRESA, P_CDLOTE, P_DTREFEMES, LAST_DAY(P_DTREFEMES));

   SELECT MAX(CDHISTPADR)
     INTO V_CDHISTPADR
     FROM TIPOOPERAC T
    WHERE T.IDENTRSAIDOP = 'S'
      AND T.CDTIPOOPER = '01';

   UPDATE CUSTMEDCTBL
      SET CDEMPRESA = P_CDEMPRESA,
          CDLOTE = P_CDLOTE
    WHERE CDFILIAL = P_CDFILIAL
      AND DTCOMPCTBL = P_DTREFEMES;

   FOR NF IN (
   SELECT LPAD(ROWNUM,6,'0') AS SEQUENCIAL, N.*
     FROM (SELECT N.CDFILIAL, N.NRLANCTONF, N.CDSERIE, N.NRNOTAFISC, N.VRNOTAFISC, N.DTENTRSAID, N.DTEMISSAO,
                  ROUND(SUM(I.QTITEMPROD * C.VRCUSTOCTBL),2) AS VRCUSTOSAID,
                  NVL(PF.CDCTBLPRACPF, CTBL_CMV(C.CDPRODUTO)) AS CDCTBLCMV, NVL(PF.CDCTBLENTRPF, CTBL_PRODUTO(C.CDPRODUTO, 'E')) AS CDCTBLENTR,
                  N.CDFILIRECE, F.CDCENTCUST
             FROM NOTAFISCAL N, ITEMPRODUTO I, CUSTMEDCTBL C, PRODFILI PF, FILIAL F, PRODUTO PR
            WHERE N.CDFILIAL = I.CDFILIAL
              AND N.NRLANCTONF = I.NRLANCTONF
              AND I.CDPRODUTO = PR.CDPRODUTO
              AND I.CDFILIMOVI = C.CDFILIAL
              AND PR.CDPRODESTO = C.CDPRODUTO
              AND N.DTENTRSAID BETWEEN P_DTREFEMES AND LAST_DAY(P_DTREFEMES)
              AND C.DTCOMPCTBL = P_DTREFEMES
              AND N.CDFILIEMIT = P_CDFILIAL
              AND N.IDENTRSAIDOP = 'S'
              AND N.IDSTATUS IN ('A','E')
              AND C.CDFILIAL = PF.CDFILIAL(+)
              AND C.CDPRODUTO = PF.CDPRODUTO(+)
              AND F.CDFILIAL = N.CDFILIRECE
            GROUP BY N.CDFILIAL, N.NRLANCTONF, N.CDSERIE, N.NRNOTAFISC, N.VRNOTAFISC, N.DTENTRSAID, N.DTEMISSAO,
                     NVL(PF.CDCTBLPRACPF, CTBL_CMV(C.CDPRODUTO)), NVL(PF.CDCTBLENTRPF, CTBL_PRODUTO(C.CDPRODUTO, 'E')),
                     N.CDFILIRECE, F.CDCENTCUST
           UNION ALL
           SELECT N.CDFILIAL, N.NRLANCTONF, N.CDSERIEENTR, N.NRNOTAFISC, N.VRNOTAFISC * (-1), N.DTENTRSAID, N.DTEMISSAO,
                  ROUND(SUM(I.QTITEMPROD * C.VRCUSTOCTBL),2)  * (-1) AS VRCUSTOSAID,
                  NVL(PF.CDCTBLPRACPF, CTBL_CMV(C.CDPRODUTO)) AS CDCTBLCMV, NVL(PF.CDCTBLENTRPF, CTBL_PRODUTO(C.CDPRODUTO, 'E')) AS CDCTBLENTR,
                  N.CDFILIEMIT, F.CDCENTCUST
             FROM NOTAFISCAL N, ITEMPRODUTO I, CUSTMEDCTBL C, PRODFILI PF, FILIAL F, PRODUTO PR
            WHERE N.CDFILIAL = I.CDFILIAL
              AND N.NRLANCTONF = I.NRLANCTONF
              AND I.CDPRODUTO = PR.CDPRODUTO
              AND I.CDFILIMOVI = C.CDFILIAL
              AND PR.CDPRODESTO = C.CDPRODUTO
              AND N.DTENTRSAID BETWEEN P_DTREFEMES AND LAST_DAY(P_DTREFEMES)
              AND C.DTCOMPCTBL = P_DTREFEMES
              AND N.CDFILIRECE = P_CDFILIAL
              AND N.IDENTRSAIDOP = 'E'
              AND N.IDSTATUS IN ('A','E')
              AND N.CDFILIEMIT IS NOT NULL
              AND C.CDFILIAL = PF.CDFILIAL(+)
              AND C.CDPRODUTO = PF.CDPRODUTO(+)
              AND F.CDFILIAL = N.CDFILIEMIT
            GROUP BY N.CDFILIAL, N.NRLANCTONF, N.CDSERIEENTR, N.NRNOTAFISC, N.VRNOTAFISC * (-1), N.DTENTRSAID, N.DTEMISSAO,
                     NVL(PF.CDCTBLPRACPF, CTBL_CMV(C.CDPRODUTO)), NVL(PF.CDCTBLENTRPF, CTBL_PRODUTO(C.CDPRODUTO, 'E')),
                     N.CDFILIEMIT, F.CDCENTCUST) N
            ORDER BY N.NRNOTAFISC) LOOP

      MONTA_HISTORICO(NF.CDFILIAL, NF.NRLANCTONF, V_CDHISTPADR, NF.NRNOTAFISC, NF.DTEMISSAO, '00', '', V_DSHISTLOTE);

      SELECT MAX(CDCONTREDU), MAX(IDCONTCCUS)
        INTO V_CDCONTREDU, V_IDCONTCCUS
        FROM CONTCTBL
       WHERE CDCONTCTBL = NF.CDCTBLCMV;

      INSERT INTO LANCLOTE
         (CDEMPRESA, CDLOTE, NRLANCLOTE, NRSEQLANLOTE, CDCONTCTBL,
          CDHISTPADR, DTLANCLOTE, NRDOCULOTE, DSHISTLOTE, VRLANCLOTE,
          CDREDUZIDO)
      VALUES
         (P_CDEMPRESA, P_CDLOTE, NF.SEQUENCIAL, '00001', NF.CDCTBLCMV,
          '0002', NF.DTENTRSAID, NF.NRNOTAFISC, V_DSHISTLOTE, (NF.VRCUSTOSAID * -1),
          V_CDCONTREDU);

      IF V_IDCONTCCUS <> 'O' THEN
         INSERT INTO RATECCUS
            (CDEMPRESA, CDLOTE, NRLANCLOTE, NRSEQLANLOTE, CDFILIAL, CDCENTCUST,
             VRRATECCUS, DTCOMPRATECCUS)
         VALUES
            (P_CDEMPRESA, P_CDLOTE, NF.SEQUENCIAL, '00001', NF.CDFILIRECE, NF.CDCENTCUST,
            (NF.VRCUSTOSAID * -1), NF.DTENTRSAID);
      END IF;

      SELECT MAX(CDCONTREDU), MAX(IDCONTCCUS)
        INTO V_CDCONTREDU, V_IDCONTCCUS
        FROM CONTCTBL
       WHERE CDCONTCTBL = NF.CDCTBLENTR;

      INSERT INTO LANCLOTE
         (CDEMPRESA, CDLOTE, NRLANCLOTE, NRSEQLANLOTE, CDCONTCTBL,
          CDHISTPADR, DTLANCLOTE, NRDOCULOTE, DSHISTLOTE, VRLANCLOTE,
          CDREDUZIDO)
      VALUES
         (P_CDEMPRESA, P_CDLOTE, NF.SEQUENCIAL, '00002', NF.CDCTBLENTR,
          '0002', NF.DTENTRSAID, NF.NRNOTAFISC, V_DSHISTLOTE, NF.VRCUSTOSAID,
          V_CDCONTREDU);

      IF V_IDCONTCCUS <> 'O' THEN
         INSERT INTO RATECCUS
            (CDEMPRESA, CDLOTE, NRLANCLOTE, NRSEQLANLOTE, CDFILIAL, CDCENTCUST,
             VRRATECCUS, DTCOMPRATECCUS)
         VALUES
            (P_CDEMPRESA, P_CDLOTE, NF.SEQUENCIAL, '00002', NF.CDFILIRECE, NF.CDCENTCUST,
             NF.VRCUSTOSAID, NF.DTENTRSAID);
      END IF;
   END LOOP;
END;
/

