IF EXISTS (SELECT O.NAME
             FROM SYSOBJECTS O, SYSUSERS U
            WHERE O.NAME = 'GERA_LOTE_CMV'
              AND TYPE = 'P'
              AND U.UID = O.UID
              AND U.NAME = USER)
   DROP PROCEDURE GERA_LOTE_CMV
GO

CREATE PROCEDURE GERA_LOTE_CMV
   @P_CDEMPRESA  VARCHAR(2),
   @P_CDFILIAL   VARCHAR(4),
   @P_DTREFEMES  DATETIME,
   @P_CDLOTE     VARCHAR(20)

AS
   DECLARE @V_CDCONTREDU     VARCHAR(20);
   DECLARE @V_CDCONTCTBL     VARCHAR(20);
   DECLARE @V_CDHISTPADR     VARCHAR(4);
   DECLARE @V_DSHISTLOTE     VARCHAR(60);
   DECLARE @V_IDCONTCCUS     VARCHAR(1);
   DECLARE @V_NRSEQUENCIAL   VARCHAR(20);

   DECLARE @C_NF_CDFILIAL    VARCHAR(4);
   DECLARE @C_NF_NRLANCTONF  VARCHAR(10);
   DECLARE @C_NF_CDSERIEENTR VARCHAR(5);
   DECLARE @C_NF_NRNOTAFISC  VARCHAR(20);
   DECLARE @C_NF_VRNOTAFISC  DOUBLE PRECISION;
   DECLARE @C_NF_DTENTRSAID  DATETIME;
   DECLARE @C_NF_DTEMISSAO   DATETIME;
   DECLARE @C_NF_VRCUSTOSAID DOUBLE PRECISION;
   DECLARE @C_NF_CDCTBLCMV   VARCHAR(20);
   DECLARE @C_NF_CDCTBLENTR  VARCHAR(20);
   DECLARE @C_NF_CDFILIEMIT  VARCHAR(4);
   DECLARE @C_NF_CDCENTCUST  VARCHAR(4);
   DECLARE @C_NF_CDFILIRECE  VARCHAR(4);

BEGIN

   INSERT INTO CAPALOTE
      (CDEMPRESA, CDLOTE, DTINICLOTE, DTFINALOTE)
   VALUES
      (@P_CDEMPRESA, @P_CDLOTE, @P_DTREFEMES, DATEADD(MM, 1, @P_DTREFEMES) - DAY(DATEADD(MM, 1, @P_DTREFEMES)));

   SELECT @V_CDHISTPADR = MAX(T.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;

   SET @V_NRSEQUENCIAL = '000001';

   DECLARE C_NF CURSOR FOR
   SELECT N.CDFILIAL, N.NRLANCTONF, N.CDSERIE, N.NRNOTAFISC,
          N.VRNOTAFISC, N.DTENTRSAID, N.DTEMISSAO, N.CDFILIRECE,
          ROUND(SUM(I.QTITEMPROD * C.VRCUSTOCTBL),2) AS VRCUSTOSAID,
          ISNULL(PF.CDCTBLPRACPF, TEKNISA.CTBL_CMV(C.CDPRODUTO)) AS CDCTBLCMV,
          ISNULL(PF.CDCTBLENTRPF, TEKNISA.CTBL_PRODUTO(C.CDPRODUTO, 'E')) AS CDCTBLENTR,
          N.CDFILIRECE, F.CDCENTCUST
     FROM NOTAFISCAL N, ITEMPRODUTO I, FILIAL F, PRODUTO PR,
          CUSTMEDCTBL C LEFT JOIN PRODFILI PF ON (C.CDFILIAL = PF.CDFILIAL AND
                                                  C.CDPRODUTO = PF.CDPRODUTO)
     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 DATEADD(MM, 1, @P_DTREFEMES) - DAY(DATEADD(MM, 1, @P_DTREFEMES))
      AND C.DTCOMPCTBL = @P_DTREFEMES
      AND N.CDFILIEMIT = @P_CDFILIAL
      AND N.IDENTRSAIDOP = 'S'
      AND N.IDSTATUS IN ('A','E')
      AND F.CDFILIAL = N.CDFILIRECE
    GROUP BY N.CDFILIAL, N.NRLANCTONF, N.CDSERIE, N.NRNOTAFISC,
             N.VRNOTAFISC, N.DTENTRSAID, N.DTEMISSAO, N.CDFILIRECE,
             ISNULL(PF.CDCTBLPRACPF, TEKNISA.CTBL_CMV(C.CDPRODUTO)),
             ISNULL(PF.CDCTBLENTRPF, TEKNISA.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, N.CDFILIRECE,
          ROUND(SUM(I.QTITEMPROD * C.VRCUSTOCTBL),2)  * (-1) AS VRCUSTOSAID,
          ISNULL(PF.CDCTBLPRACPF, TEKNISA.CTBL_CMV(C.CDPRODUTO)) AS CDCTBLCMV,
          ISNULL(PF.CDCTBLENTRPF, TEKNISA.CTBL_PRODUTO(C.CDPRODUTO, 'E')) AS CDCTBLENTR,
          N.CDFILIEMIT, F.CDCENTCUST
     FROM NOTAFISCAL N, ITEMPRODUTO I, FILIAL F, PRODUTO PR,
          CUSTMEDCTBL C LEFT JOIN PRODFILI PF ON (C.CDFILIAL = PF.CDFILIAL AND
                                                  C.CDPRODUTO = PF.CDPRODUTO)
    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 DATEADD(MM, 1, @P_DTREFEMES) - DAY(DATEADD(MM, 1, @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 F.CDFILIAL = N.CDFILIEMIT
    GROUP BY N.CDFILIAL, N.NRLANCTONF, N.CDSERIEENTR, N.NRNOTAFISC,
             N.VRNOTAFISC * (-1), N.DTENTRSAID, N.DTEMISSAO, N.CDFILIRECE,
             ISNULL(PF.CDCTBLPRACPF, TEKNISA.CTBL_CMV(C.CDPRODUTO)),
             ISNULL(PF.CDCTBLENTRPF, TEKNISA.CTBL_PRODUTO(C.CDPRODUTO, 'E')),
             N.CDFILIEMIT, F.CDCENTCUST
   ORDER BY 5
   OPEN C_NF;

   FETCH NEXT FROM C_NF INTO @C_NF_CDFILIAL, @C_NF_NRLANCTONF, @C_NF_CDSERIEENTR, @C_NF_NRNOTAFISC,
                             @C_NF_VRNOTAFISC, @C_NF_DTENTRSAID, @C_NF_DTEMISSAO, @C_NF_CDFILIRECE,
                             @C_NF_VRCUSTOSAID, @C_NF_CDCTBLCMV, @C_NF_CDCTBLENTR, @C_NF_CDFILIEMIT,
                             @C_NF_CDCENTCUST;

   WHILE (@@FETCH_STATUS = 0) BEGIN

      EXEC MONTA_HISTORICO @C_NF_CDFILIAL, @C_NF_NRLANCTONF, @V_CDHISTPADR, @C_NF_NRNOTAFISC, @C_NF_DTEMISSAO, '00', '', @V_DSHISTLOTE;

      SELECT @V_CDCONTREDU = MAX(CDCONTREDU),
             @V_IDCONTCCUS = MAX(IDCONTCCUS)
        FROM CONTCTBL
       WHERE CDCONTCTBL = @C_NF_CDCTBLCMV;

      INSERT INTO LANCLOTE
         (CDEMPRESA, CDLOTE, NRLANCLOTE, NRSEQLANLOTE, CDCONTCTBL,
          CDHISTPADR, DTLANCLOTE, NRDOCULOTE, DSHISTLOTE, VRLANCLOTE,
          CDREDUZIDO)
      VALUES
         (@P_CDEMPRESA, @P_CDLOTE, @V_NRSEQUENCIAL, '00001', @C_NF_CDCTBLCMV,
          '0002', @C_NF_DTENTRSAID, @C_NF_NRNOTAFISC, @V_DSHISTLOTE, (@C_NF_VRCUSTOSAID * -1),
          @V_CDCONTREDU);

      IF @V_IDCONTCCUS <> 'O' BEGIN
         INSERT INTO RATECCUS
            (CDEMPRESA, CDLOTE, NRLANCLOTE, NRSEQLANLOTE, CDFILIAL, CDCENTCUST,
             VRRATECCUS, DTCOMPRATECCUS)
         VALUES
            (@P_CDEMPRESA, @P_CDLOTE, @V_NRSEQUENCIAL, '00001', @C_NF_CDFILIRECE, @C_NF_CDCENTCUST,
            (@C_NF_VRCUSTOSAID * -1), @C_NF_DTENTRSAID);
      END;

      SELECT @V_CDCONTREDU = MAX(CDCONTREDU),
             @V_IDCONTCCUS = MAX(IDCONTCCUS)
        FROM CONTCTBL
       WHERE CDCONTCTBL = @C_NF_CDCTBLENTR;

      INSERT INTO LANCLOTE
         (CDEMPRESA, CDLOTE, NRLANCLOTE, NRSEQLANLOTE, CDCONTCTBL,
          CDHISTPADR, DTLANCLOTE, NRDOCULOTE, DSHISTLOTE, VRLANCLOTE,
          CDREDUZIDO)
      VALUES
         (@P_CDEMPRESA, @P_CDLOTE, @V_NRSEQUENCIAL, '00002', @C_NF_CDCTBLENTR,
          '0002', @C_NF_DTENTRSAID, @C_NF_NRNOTAFISC, @V_DSHISTLOTE, @C_NF_VRCUSTOSAID,
          @V_CDCONTREDU);

      IF @V_IDCONTCCUS <> 'O' BEGIN
         INSERT INTO RATECCUS
            (CDEMPRESA, CDLOTE, NRLANCLOTE, NRSEQLANLOTE, CDFILIAL, CDCENTCUST,
             VRRATECCUS, DTCOMPRATECCUS)
         VALUES
            (@P_CDEMPRESA, @P_CDLOTE, @V_NRSEQUENCIAL, '00002', @C_NF_CDFILIRECE, @C_NF_CDCENTCUST,
             @C_NF_VRCUSTOSAID, @C_NF_DTENTRSAID);
      END;

      SET @V_NRSEQUENCIAL = RIGHT('000000' + @V_NRSEQUENCIAL + 1, 6);

      FETCH NEXT FROM C_NF INTO @C_NF_CDFILIAL, @C_NF_NRLANCTONF, @C_NF_CDSERIEENTR, @C_NF_NRNOTAFISC,
                                @C_NF_VRNOTAFISC, @C_NF_DTENTRSAID, @C_NF_DTEMISSAO, @C_NF_CDFILIRECE,
                                @C_NF_VRCUSTOSAID, @C_NF_CDCTBLCMV, @C_NF_CDCTBLENTR, @C_NF_CDFILIEMIT,
                                @C_NF_CDCENTCUST;
   END;
   CLOSE C_NF;
   DEALLOCATE C_NF;
END;
GO

