IF EXISTS (SELECT
              *
           FROM
              SYSOBJECTS O, SYSUSERS U
           WHERE
              O.NAME = 'ARQUIVO_SINTEGRA_61' AND
              TYPE   = 'P' 		 AND
              U.UID  = O.UID             AND
              U.NAME = USER)

DROP PROCEDURE ARQUIVO_SINTEGRA_61
GO
CREATE PROCEDURE ARQUIVO_SINTEGRA_61
   @P_CDEMPRESA        VARCHAR(02),
   @P_CDIMPOSTO        VARCHAR(02),
   @P_CDINSCESTA       VARCHAR(16),
   @P_DTINICIAL        DATETIME,
   @P_DTFINAL          DATETIME,
   @P_NRSEQARQMAG      VARCHAR(10),
   @P_GERAFORSALE      VARCHAR(01),
   @P_CONTADOR         NUMERIC       OUTPUT
AS
   DECLARE @V_REGISTRO61     VARCHAR(250);
   DECLARE @V_DTEMISSAO      DATETIME;
   DECLARE @V_CONTREG        NUMERIC;
   DECLARE @V_CONTADOR       NUMERIC;
BEGIN
   SET @V_CONTREG  = 0;
   SET @V_CONTADOR = @P_CONTADOR + 1;
   DECLARE C_REGISTRO61 CURSOR FOR
      SELECT SUBSTRING(
             '61'+
             REPLICATE(' ', 14)+
             REPLICATE(' ', 14)+
             RTRIM(CONVERT(CHAR, NF.DTEMISSAO, 112))+
             '02'+
             LEFT((CASE RTRIM(ISNULL(NF.CDSERIE,' ')) WHEN '-' THEN ' ' ELSE ISNULL(NF.CDSERIE,' ') END)+REPLICATE(' ', 5), 5)+
             RIGHT(REPLICATE('0',6)+ SUBSTRING(NF.NRNOTAFISC,LEN(NF.NRNOTAFISC)-5,6),6)+
             RIGHT(REPLICATE('0',6)+ SUBSTRING(NF.NRNOTAFISC,LEN(NF.NRNOTAFISC)-5,6),6)+
             RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(13,2),NF.VRNOTAFISC)),'.','')),13)+
             RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(13,2),ABS(NF.VRBASECALCNF))),'.','')),13)+
             RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(12,2),ABS(NF.VRIMPOSTONF))),'.','')),13)+
             RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(13,2),ABS(NF.VRISENTRIB))),'.','')),13)+
             RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(12,2),ABS(NF.VROUTRCRED))),'.','')),12)+
             RIGHT(REPLICATE('0',4)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(4,2),ABS(NF.VRALIQNF))),'.','')),4)+
             ' ',1, 126) AS DSARQMAG,NF.DTEMISSAO
        FROM (SELECT I.NRLANCTONF, N.CDSERIE, N.DTEMISSAO, I.VRALIQNF, N.CDMODDOCFISC, N.VRNOTAFISC,
                     SUM(I.VRCONTABILNF) AS VRCONTABILNF,N.NRNOTAFISC,
                     SUM((CASE I.CDFISCALNF WHEN '1' THEN I.VRBASECALCNF ELSE 0 END)) AS VRBASECALCNF,
                     SUM((CASE I.CDFISCALNF WHEN '1' THEN I.VRIMPOSTONF ELSE 0 END))  AS VRIMPOSTONF,
                     SUM((CASE I.CDFISCALNF WHEN '3' THEN I.VRCONTABILNF ELSE 0 END))   AS VROUTRCRED,
                     SUM((CASE I.CDFISCALNF WHEN '2' THEN I.VRCONTABILNF ELSE (CASE SUBSTRING(I.CDSITUTRIBNF,2,2) WHEN '20' THEN I.VRCONTABILNF-I.VRBASECALCNF ELSE 0 END) END))   AS VRISENTRIB
                FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, MODDOCFISC MD, IMPOSTO P,
                     ITEMPRODUTO IP LEFT JOIN ITEMVENDA IV ON IP.CDFILIAL = IV.CDFILIAL          AND
                                                              IP.CDCAIXA  = IV.CDCAIXA            AND
                                                              IP.NRSEQVENDA     = IV.NRSEQVENDA   AND
                                                              IP.NRSEQUITVEND   = IV.NRSEQUITVEND
                                    LEFT JOIN MOVICLIE MC ON IV.CDFILIAL       = MC.CDFILIAL     AND
                                                             IV.CDCAIXA        = MC.CDCAIXA       AND
                                                             IV.NRSEQVENDA     = MC.NRSEQVENDA
               WHERE M.CDEMPRESA       = @P_CDEMPRESA                   AND
                     M.CDINSCESTA      = @P_CDINSCESTA                  AND
                     M.DTINIPERIODO    = @P_DTINICIAL                   AND
                     M.DTFIMPERIODO    = @P_DTFINAL                     AND
                     M.CDIMPOSTO       = @P_CDIMPOSTO                   AND
                     I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU                 AND
                     I.CDIMPOSTO       = P.CDIMPOSTO                    AND
                     P.CDIMPOSTONFE    = @P_CDIMPOSTO                   AND
                     ISNULL(I.IDIMPOSTONF, '1') <> '3'                  AND
                     N.CDFILIAL        = I.CDFILIAL                     AND
                     N.NRLANCTONF      = I.NRLANCTONF                   AND
                     N.CDFILIAL        = IP.CDFILIAL                    AND
                     N.NRLANCTONF      = IP.NRLANCTONF                  AND
                     N.IDSTATUS IN ('A','E')                            AND
                     N.IDENTRSAIDOP    = 'S'                            AND
                     ISNULL(SUBSTRING(I.CDFISCOPERNF,1,1),' ') <> '7'   AND
                     N.CDMODDOCFISC    = MD.CDMODDOCFISC                AND
                     MD.CDTPREGFISC    = '61'
               GROUP BY I.NRLANCTONF, N.CDSERIE, N.DTEMISSAO, I.VRALIQNF, N.CDMODDOCFISC, N.VRNOTAFISC,N.NRNOTAFISC
               UNION
              SELECT NF.NRLANCTONF, NF.CDSERIE, NF.DTEMISSAO, I.VRALIQNF, NF.CDMODDOCFISC, NF.VRNOTAFISC,
                     SUM(I.VRCONTABILNF) AS VRCONTABILNF,NF.NRNOTAFISC,
                     SUM((CASE I.CDFISCALNF WHEN '1' THEN I.VRBASECALCNF ELSE 0 END)) AS VRBASECALCNF,
                     SUM((CASE I.CDFISCALNF WHEN '1' THEN I.VRIMPOSTONF ELSE 0 END))  AS VRIMPOSTONF,
                     SUM((CASE I.CDFISCALNF WHEN '3' THEN I.VRCONTABILNF ELSE 0 END))   AS VROUTRCRED,
                     SUM((CASE I.CDFISCALNF WHEN '2' THEN I.VRCONTABILNF ELSE (CASE SUBSTRING(I.CDSITUTRIBNF,2,2) WHEN '20' THEN I.VRCONTABILNF-I.VRBASECALCNF ELSE 0 END)END)) AS VRISENTRIB
              FROM MAPAAPURAC M, NOTAFISCAL NF, IMPOSTONF I, MODDOCFISC MD, IMPOSTO P
              WHERE M.CDEMPRESA       = @P_CDEMPRESA                   AND
                    M.CDINSCESTA      = @P_CDINSCESTA                  AND
                    M.DTINIPERIODO    = @P_DTINICIAL                   AND
                    M.DTFIMPERIODO    = @P_DTFINAL                     AND
                    M.CDIMPOSTO       = @P_CDIMPOSTO                   AND
                    I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU                 AND
                    I.CDIMPOSTO       = P.CDIMPOSTO                    AND
                    P.CDIMPOSTONFE    = @P_CDIMPOSTO                   AND
                    ISNULL(I.IDIMPOSTONF, '1') <> '3'                  AND
                    NF.IDENTRSAIDOP     = 'S'                          AND
                    NF.IDSTATUS IN ('A','E')                           AND
                    ISNULL(SUBSTRING(I.CDFISCOPERNF,1,1),' ') <> '7'   AND
                    NF.CDFILIAL        = I.CDFILIAL                    AND
                    NF.NRLANCTONF      = I.NRLANCTONF                  AND
                    NF.CDMODDOCFISC    = MD.CDMODDOCFISC               AND
                    MD.CDTPREGFISC      = '61'
              GROUP BY NF.NRLANCTONF, NF.CDSERIE, NF.DTEMISSAO, I.VRALIQNF, NF.CDMODDOCFISC, NF.VRNOTAFISC,NF.NRNOTAFISC) NF

   OPEN C_REGISTRO61;
   FETCH NEXT FROM C_REGISTRO61 INTO @V_REGISTRO61,
                                     @V_DTEMISSAO;

      WHILE (@@FETCH_STATUS = 0) BEGIN

         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
            (RIGHT(REPLICATE('0',2)+CONVERT(VARCHAR,@V_CONTADOR),2)+RIGHT(REPLICATE('0',8)+CONVERT(VARCHAR,@V_CONTREG),8),@V_REGISTRO61,@P_NRSEQARQMAG);

         SET @V_CONTREG   = ISNULL(@V_CONTREG,0)  + 1;

   FETCH NEXT FROM C_REGISTRO61 INTO @V_REGISTRO61,
                                     @V_DTEMISSAO;
   END;
   CLOSE C_REGISTRO61;
   DEALLOCATE C_REGISTRO61;

   SET @P_CONTADOR = @V_CONTADOR;
END;
GO

-- VERSAO --
EXEC ATUVEROBJ 'ARQUIVO_SINTEGRA_61','5.30.000';
GO

