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

DROP PROCEDURE ARQUIVO_SINTEGRA_51
GO
CREATE PROCEDURE ARQUIVO_SINTEGRA_51
   @P_CDEMPRESA        VARCHAR(02),
   @P_CDIMPOSTO        VARCHAR(02),
   @P_CDINSCESTA       VARCHAR(16),
   @P_DTINICIAL        DATETIME,
   @P_DTFINAL          DATETIME,
   @P_CDPAIS           VARCHAR(04),
   @P_SGESTADO         VARCHAR(02),
   @P_IPI              VARCHAR(02),
   @P_NRSEQARQMAG      VARCHAR(10),
   @P_CONTADOR         NUMERIC      OUTPUT
AS
   DECLARE @V_CONTADOR   NUMERIC;
   DECLARE @V_STTOTAL    VARCHAR(15);
   DECLARE @V_REGISTRO51 VARCHAR(250);
   DECLARE @V_DATA       DATETIME;
   DECLARE @V_CONTREG    NUMERIC;
BEGIN
   SET @V_CONTREG  = 0;
   SET @V_CONTADOR  = @P_CONTADOR + 1;

   DECLARE C_REGISTRO51 CURSOR FOR
      SELECT SUBSTRING('51' +
             NRINSJ         +
             NRINSEST       +
             RTRIM(DATA)    +
             SGESTADO       +
             SERIE          +
             NRNOTAFISC     +
             CDFISCOPERNF   +
             VRCONTABILNF   +
             VRIMPOSTONF    +
             VRISENTRIB     +
             VROUTRCRED     +
             LEFT(' '+REPLICATE(' ',20),20)+
             IDSTATUS,1,126) AS DSARQUMAG, DATA
        FROM (SELECT RIGHT(REPLICATE('0',14)+ISNULL(CASE FO.IDTPIJURFORN WHEN 'I' THEN '' WHEN 'O' THEN '' ELSE FO.NRINSJURFORN END,'00000000000000'),14) AS NRINSJ,
                     LEFT(ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(FO.NRINSESTFORN,'.',''),',',''),'-',''),'/',''),'ISENTO')+REPLICATE(' ',14),14) AS NRINSEST,
                     CONVERT(CHAR,CASE NF.IDENTRSAIDOP WHEN 'S' THEN NF.DTEMISSAO ELSE NF.DTENTRSAID END,112) AS DATA,
                     LEFT(ISNULL(EF.SGESTADO,' ')+REPLICATE(' ',2),2)  AS SGESTADO,
                     LEFT(CASE ISNULL(NF.CDSERIEENTR,NF.CDSERIE) WHEN '-' THEN ' ' ELSE REPLACE(ISNULL(NF.CDSERIEENTR,NF.CDSERIE), '-', '') END+REPLICATE(' ',3),3)  AS SERIE,
                     RIGHT(REPLICATE('0',6)+SUBSTRING(NF.NRNOTAFISC,LEN(NF.NRNOTAFISC)-5,6),6) AS NRNOTAFISC,
                     LEFT(ISNULL(NF.CDFISCOPERNF,'0')+REPLICATE(' ',4),4) AS CDFISCOPERNF,
                     RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(NUMERIC(13,2),ISNULL(NF.VRCONTABILNF,0)),'.','')),13)AS VRCONTABILNF,
                     RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(NUMERIC(13,2),ISNULL(NF.VRIMPOSTONF,0)),'.','')),13) AS VRIMPOSTONF,
                     RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(NUMERIC(13,2),ISNULL(NF.VRISENTRIB,0)),'.','')),13)  AS VRISENTRIB,
                     RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(NUMERIC(13,2),ISNULL(NF.VROUTRCRED,0)),'.','')),13)  AS VROUTRCRED,
                     CASE NF.IDSTATUS WHEN 'C' THEN  'S' ELSE 'N' END AS IDSTATUS
                FROM FORNECEDOR FA LEFT JOIN ENDEFORN EF ON (EF.CDFORNECED   = FA.CDFORNECED AND
                                                             EF.IDTPENDEFORN = ISNULL(FA.IDENDEMISFOR,'P')),
                     FORNECEDOR FO RIGHT JOIN
                     (SELECT I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIEENTR, N.CDSERIE, N.CDFORNECED,
                             N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP,
                             SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                             SUM(CASE I.CDFISCALNF WHEN '1' THEN I.VRIMPOSTONF  ELSE 0 END ) AS VRIMPOSTONF,
                             SUM(CASE I.CDFISCALNF WHEN '2' THEN I.VRCONTABILNF ELSE 0 END ) AS VRISENTRIB,
                             SUM(CASE I.CDFISCALNF WHEN '3' THEN I.VRCONTABILNF ELSE 0 END ) AS VROUTRCRED
                        FROM NOTAFISCAL N, IMPOSTONF I, MODDOCFISC MD,
                             (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                                FROM MAPAAPURAC M, IMPOSTONF IP
                               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 M.NRSEQUMAPAPU  = IP.NRSEQUMAPAPU
                                 AND IP.CDIMPOSTO    = @P_CDIMPOSTO) IC
                       WHERE N.CDEMPRESA       = @P_CDEMPRESA
                         AND N.CDINSCESTA      = @P_CDINSCESTA
                         AND N.DTENTRSAID BETWEEN @P_DTINICIAL AND @P_DTFINAL
                         AND N.IDSTATUS   IN ('A', 'E')
                         AND N.CDFILIAL        = I.CDFILIAL
                         AND N.NRLANCTONF      = I.NRLANCTONF
                         AND I.CDIMPOSTO       = @P_IPI
                         AND I.NRSEQUMAPAPU IS NOT NULL
                         AND I.CDFILIAL        = IC.CDFILIAL
                         AND I.NRLANCTONF      = IC.NRLANCTONF
                         AND ISNULL(I.IDIMPOSTONF, '1') <> '3'
                         AND ISNULL(SUBSTRING(I.CDFISCOPERNF,1,1),' ') <> '3'
                         AND N.CDMODDOCFISC    = MD.CDMODDOCFISC
                         AND ISNULL(MD.CDTPREGFISC, '50') IN ('50', '51')
                       GROUP BY I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIEENTR, N.CDSERIE,
                                N.CDFORNECED,N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, I.CDFISCOPERNF,
                                N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP) NF ON (FO.CDFORNECED   = NF.CDFORNECED)
               WHERE FA.CDFORNECED      = NF.CDFORNECED            AND
                     EF.CDPAIS          = ISNULL(@P_CDPAIS,EF.CDPAIS)  AND
                     (CHARINDEX(ISNULL(@P_SGESTADO, EF.SGESTADO), EF.SGESTADO) > 0)

             UNION ALL
              SELECT '00000000000000' AS NRINSJ,
                     'ISENTO        ' AS NRINSEST,
                     CONVERT(CHAR,CASE NF.IDENTRSAIDOP WHEN 'S' THEN  NF.DTEMISSAO ELSE NF.DTENTRSAID END,112) AS DATA,
                     'EX' AS SGESTADO,
                     LEFT(CASE ISNULL(NF.CDSERIEENTR,NF.CDSERIE) WHEN '-' THEN ' 'ELSE REPLACE(ISNULL(NF.CDSERIEENTR,NF.CDSERIE), '-', '')END + REPLICATE(' ',3),3)AS SERIE,
                     RIGHT(REPLICATE('0',6)+SUBSTRING(NF.NRNOTAFISC,LEN(NF.NRNOTAFISC)-5,6),6)AS NRNOTAFISC,
                     LEFT(ISNULL(NF.CDFISCOPERNF,'0')+REPLICATE(' ',4),4)AS CDFISCOPERNF,
                     RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(NUMERIC(13,2),ISNULL(NF.VRCONTABILNF,0)),'.','')),13) AS VRCONTABILNF,
                     RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(NUMERIC(13,2),ISNULL(NF.VRIMPOSTONF,0)),'.','')),13)  AS VRIMPOSTONF,
                     RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(NUMERIC(13,2),ISNULL(NF.VRISENTRIB,0)),'.','')),13)   AS VRISENTRIB,
                     RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(NUMERIC(13,2),ISNULL(NF.VROUTRCRED,0)),'.','')),13)   AS VROUTRCRED,
                     CASE NF.IDSTATUS WHEN 'C' THEN 'S' ELSE 'N' END AS IDSTATUS
                FROM FORNECEDOR FA LEFT JOIN ENDEFORN EF ON (EF.CDFORNECED   = FA.CDFORNECED AND
                                                             EF.IDTPENDEFORN = ISNULL(FA.IDENDEMISFOR,'P')),
                     FORNECEDOR FO RIGHT JOIN
                     (SELECT I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIEENTR, N.CDSERIE, N.CDFORNECED,
                             N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP,
                             SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                             SUM(CASE I.CDFISCALNF WHEN '1' THEN I.VRIMPOSTONF  ELSE 0 END ) AS VRIMPOSTONF,
                             SUM(CASE I.CDFISCALNF WHEN '2' THEN I.VRCONTABILNF ELSE 0 END ) AS VRISENTRIB,
                             SUM(CASE I.CDFISCALNF WHEN '3' THEN I.VRCONTABILNF ELSE 0 END ) AS VROUTRCRED
                        FROM NOTAFISCAL N, IMPOSTONF I, MODDOCFISC MD,
                             (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                                FROM MAPAAPURAC M, IMPOSTONF IP
                               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 M.NRSEQUMAPAPU = IP.NRSEQUMAPAPU
                                 AND IP.CDIMPOSTO   = @P_CDIMPOSTO) IC
                       WHERE N.CDEMPRESA       = @P_CDEMPRESA
                         AND N.CDINSCESTA      = @P_CDINSCESTA
                         AND N.DTENTRSAID BETWEEN @P_DTINICIAL AND @P_DTFINAL
                         AND N.IDSTATUS   IN ('A', 'E')
                         AND N.CDFILIAL        = I.CDFILIAL
                         AND N.NRLANCTONF      = I.NRLANCTONF
                         AND I.CDIMPOSTO       = @P_IPI
                         AND I.CDFILIAL        = IC.CDFILIAL
                         AND I.NRLANCTONF      = IC.NRLANCTONF
                         AND ISNULL(I.IDIMPOSTONF, '1') <> '3'
                         AND I.NRSEQUMAPAPU IS NOT NULL
                         AND SUBSTRING(I.CDFISCOPERNF,1,1) = '3'
                         AND N.CDMODDOCFISC    = MD.CDMODDOCFISC
                         AND ISNULL(MD.CDTPREGFISC, '50') IN ('50', '51')
                       GROUP BY I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIEENTR, N.CDSERIE,
                                N.CDFORNECED,N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, I.CDFISCOPERNF,
                                N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP) NF ON (FO.CDFORNECED = NF.CDFORNECED)
               WHERE FA.CDFORNECED      = NF.CDFORNECED
                 AND EF.CDPAIS          = ISNULL(@P_CDPAIS,EF.CDPAIS)
                 AND (CHARINDEX(ISNULL(@P_SGESTADO, EF.SGESTADO), EF.SGESTADO) > 0)
             UNION ALL
              SELECT RIGHT(REPLICATE('0',14)+ISNULL(CASE CL.IDTPIJURCLIE WHEN 'I' THEN ''WHEN 'O' THEN ''ELSE CL.NRINSJURCLIE END,'00000000000000'),14)AS NRINSJ,
                     LEFT(ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(CL.NRINSESTCLIE,'.',''),',',''),'-',''),'/',''),'ISENTO')+REPLICATE(' ',14),14)AS NRINSEST,
                     CONVERT(CHAR,CASE NF.IDENTRSAIDOP WHEN 'E' THEN  NF.DTENTRSAID ELSE NF.DTEMISSAO END,112) AS DATA,
                     LEFT(ISNULL(EC.SGESTADO,' ')+REPLICATE(' ',2),2)AS SGESTADO,
                     LEFT(CASE ISNULL(NF.CDSERIE,NF.CDSERIEENTR) WHEN '-' THEN ' 'ELSE REPLACE(ISNULL(NF.CDSERIE,NF.CDSERIEENTR), '-', '')END +REPLICATE(' ',3),3)AS SERIE,
                     RIGHT(REPLICATE('0',6)+SUBSTRING(NF.NRNOTAFISC,LEN(NF.NRNOTAFISC)-5,6),6)AS NRNOTAFISC,
                     LEFT(ISNULL(NF.CDFISCOPERNF,'0')+REPLICATE(' ',4),4) AS CDFISCOPERNF,
                     RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(NUMERIC(13,2),ISNULL(NF.VRNOTAFISC,0)),'.','')),13)  AS VRCONTABILNF,
                     RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(NUMERIC(13,2),ISNULL(NF.VRIMPOSTONF,0)),'.','')),13) AS VRIMPOSTONF,
                     RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(NUMERIC(13,2),ISNULL(NF.VROUTRCRED,0)),'.','')),13)  AS VROUTRCRED,
                     RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(NUMERIC(13,2),ISNULL(NF.VRISENTRIB,0)),'.','')),13)  AS VRISENTRIB,
                     CASE NF.IDSTATUS WHEN 'C' THEN 'S' ELSE 'N' END AS IDSTATUS
                FROM CLIENTE CA LEFT JOIN ENDECLIE EC ON (EC.CDCLIENTE    = CA.CDCLIENTE             AND
                                                          EC.IDTPENDECLIE = ISNULL(CA.IDENDEMISCLI,'P')),
                     CLIENTE CL RIGHT JOIN
                     (SELECT I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.CDCLIENTE,
                             N.DTEMISSAO, N.DTENTRSAID, I.VRALIQNF, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP,
                             SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                             SUM(CASE I.CDFISCALNF WHEN '1' THEN I.VRIMPOSTONF  ELSE 0 END ) AS VRIMPOSTONF,
                             SUM(CASE I.CDFISCALNF WHEN '2' THEN I.VRCONTABILNF ELSE 0 END ) AS VRISENTRIB,
                             SUM(CASE I.CDFISCALNF WHEN '3' THEN I.VRCONTABILNF ELSE 0 END ) AS VROUTRCRED
                        FROM NOTAFISCAL N, IMPOSTONF I, MODDOCFISC MD,
                             (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                                FROM MAPAAPURAC M, IMPOSTONF IP
                               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 M.NRSEQUMAPAPU = IP.NRSEQUMAPAPU
                                 AND    IP.CDIMPOSTO   = @P_CDIMPOSTO) IC
                       WHERE N.CDEMPRESA       = @P_CDEMPRESA
                         AND N.CDINSCESTA      = @P_CDINSCESTA
                         AND N.DTEMISSAO BETWEEN @P_DTINICIAL AND @P_DTFINAL
                         AND N.IDSTATUS   IN ('A', 'E')
                         AND N.CDFILIAL        = I.CDFILIAL
                         AND N.NRLANCTONF      = I.NRLANCTONF
                         AND I.CDIMPOSTO       = @P_IPI
                         AND I.NRSEQUMAPAPU IS NOT NULL
                         AND I.CDFILIAL        = IC.CDFILIAL
                         AND I.NRLANCTONF      = IC.NRLANCTONF
                         AND ISNULL(I.IDIMPOSTONF, '1') <> '3'
                         AND ISNULL(SUBSTRING(I.CDFISCOPERNF,1,1),' ') <> '7'
                         AND N.CDMODDOCFISC    = MD.CDMODDOCFISC
                         AND ISNULL(MD.CDTPREGFISC, '50') IN ('50', '51')
                       GROUP BY I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIE, N.CDSERIEENTR,
                                N.CDCLIENTE,N.DTEMISSAO, N.DTENTRSAID, I.VRALIQNF, I.CDFISCOPERNF,
                                N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP) NF ON (CL.CDCLIENTE = NF.CDCLIENTE)
               WHERE CA.CDCLIENTE       = NF.CDCLIENTE             AND
                     EC.CDPAIS          = ISNULL(@P_CDPAIS,EC.CDPAIS)  AND
                     (CHARINDEX(ISNULL(@P_SGESTADO, EC.SGESTADO),EC.SGESTADO) > 0)
             UNION ALL
              SELECT '00000000000000' AS NRINSJ,
                     'ISENTO        'AS NRINSEST,
                     CONVERT(CHAR,CASE NF.IDENTRSAIDOP WHEN 'E' THEN NF.DTENTRSAID ELSE NF.DTEMISSAO END,112) AS DATA,
                     'EX' AS SGESTADO,
                     LEFT(CASE ISNULL(NF.CDSERIE,NF.CDSERIEENTR) WHEN '-' THEN ' ' ELSE REPLACE(ISNULL(NF.CDSERIE,NF.CDSERIEENTR), '-', '') END+REPLICATE(' ',3),3)AS SERIE,
                     RIGHT(REPLICATE('0',6)+SUBSTRING(NF.NRNOTAFISC,LEN(NF.NRNOTAFISC)-5,6),6)AS NRNOTAFISC,
                     LEFT(ISNULL(NF.CDFISCOPERNF,'0')+REPLICATE(' ',4),4) AS CDFISCOPERNF,
                     RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(NUMERIC(13,2),ISNULL(NF.VRNOTAFISC,0)),'.','')),13) AS VRCONTABILNF,
                     RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(NUMERIC(13,2),ISNULL(NF.VRIMPOSTONF,0)),'.','')),13)AS VRIMPOSTONF,
                     RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(NUMERIC(13,2),ISNULL(NF.VROUTRCRED,0)),'.','')),13) AS VROUTRCRED,
                     RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(NUMERIC(13,2),ISNULL(NF.VRISENTRIB,0)),'.','')),13) AS VRISENTRIB,
                     CASE NF.IDSTATUS WHEN 'C' THEN 'S' ELSE 'N' END AS IDSTATUS
                FROM CLIENTE CA LEFT JOIN ENDECLIE EC ON (EC.CDCLIENTE    = CA.CDCLIENTE             AND
                                                          EC.IDTPENDECLIE = ISNULL(CA.IDENDEMISCLI,'P')),
                     CLIENTE CL RIGHT JOIN
                     (SELECT I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.CDCLIENTE,
                             N.DTEMISSAO, N.DTENTRSAID, I.VRALIQNF, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP,
                             SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                             SUM(CASE I.CDFISCALNF WHEN '1' THEN I.VRIMPOSTONF  ELSE 0 END ) AS VRIMPOSTONF,
                             SUM(CASE I.CDFISCALNF WHEN '2' THEN I.VRCONTABILNF ELSE 0 END ) AS VRISENTRIB,
                             SUM(CASE I.CDFISCALNF WHEN '3' THEN I.VRCONTABILNF ELSE 0 END ) AS VROUTRCRED
                        FROM NOTAFISCAL N, IMPOSTONF I, MODDOCFISC MD,
                             (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                                FROM MAPAAPURAC M, IMPOSTONF IP
                               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 M.NRSEQUMAPAPU = IP.NRSEQUMAPAPU
                                 AND IP.CDIMPOSTO   = @P_CDIMPOSTO) IC
                       WHERE N.CDEMPRESA       = @P_CDEMPRESA
                         AND N.CDINSCESTA      = @P_CDINSCESTA
                         AND N.DTEMISSAO BETWEEN @P_DTINICIAL AND @P_DTFINAL
                         AND N.IDSTATUS   IN ('A', 'E')
                         AND N.CDFILIAL        = I.CDFILIAL
                         AND N.NRLANCTONF      = I.NRLANCTONF
                         AND I.CDIMPOSTO       = @P_IPI
                         AND I.NRSEQUMAPAPU IS NOT NULL
                         AND I.CDFILIAL        = IC.CDFILIAL
                         AND I.NRLANCTONF      = IC.NRLANCTONF
                         AND ISNULL(I.IDIMPOSTONF, '1') <> '3'
                         AND SUBSTRING(I.CDFISCOPERNF,1,1) = '7'
                         AND N.CDMODDOCFISC    = MD.CDMODDOCFISC
                         AND ISNULL(MD.CDTPREGFISC, '50') IN ('50', '51')
                       GROUP BY I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIE, N.CDSERIEENTR,
                                N.CDCLIENTE,N.DTEMISSAO, N.DTENTRSAID, I.VRALIQNF, I.CDFISCOPERNF,
                                N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP) NF ON(CL.CDCLIENTE = NF.CDCLIENTE)
               WHERE CA.CDCLIENTE       = NF.CDCLIENTE             AND
                     EC.CDPAIS          = ISNULL(@P_CDPAIS,EC.CDPAIS) AND
                     (CHARINDEX(ISNULL(@P_SGESTADO, EC.SGESTADO),EC.SGESTADO) > 0)) NF
       ORDER BY DATA;

   OPEN C_REGISTRO51;
   FETCH NEXT FROM C_REGISTRO51 INTO @V_REGISTRO51,@V_DATA;
   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)+RTRIM(CONVERT(VARCHAR,@V_CONTREG)),8), @V_REGISTRO51, @P_NRSEQARQMAG);

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

   FETCH NEXT FROM C_REGISTRO51 INTO @V_REGISTRO51,@V_DATA;
   END;
   CLOSE C_REGISTRO51;
   DEALLOCATE C_REGISTRO51;

   SET @P_CONTADOR = @V_CONTADOR;
END;
GO

-- VERSAO --
EXEC ATUVEROBJ 'ARQUIVO_SINTEGRA_51','5.05.061';
GO

