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

DROP PROCEDURE ARQUIVO_SINTEGRA_55
GO
CREATE PROCEDURE ARQUIVO_SINTEGRA_55
   @P_CDEMPRESA        VARCHAR(02),
   @P_CDIMPOSTO        VARCHAR(02),
   @P_CDINSCESTA       VARCHAR(16),
   @P_DTINICIAL        DATETIME,
   @P_DTFINAL          DATETIME,
   @P_NRSEQARQMAG      VARCHAR(10),
   @P_CONTADOR         NUMERIC       OUTPUT
AS
   DECLARE @V_CONTREG        NUMERIC;
   DECLARE @V_DSARQUMAG      VARCHAR(250);
   DECLARE @V_STTOTAL        VARCHAR(160);
   DECLARE @V_CONTADOR       NUMERIC;
BEGIN

   SET @V_CONTREG = 0;
   SET @V_CONTADOR = @P_CONTADOR + 1;
   DECLARE C_REGISTRO55 CURSOR FOR
      SELECT SUBSTRING('55' +
             REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'.',''),'-',''),'/','') +
             LEFT(REPLACE(REPLACE(REPLACE(M.CDINSCESTA,'.',''),',',''),'-','') + REPLICATE(' ',14),14) +
             RTRIM(CONVERT(CHAR, P.DTORIGVENPAG,112))+
             LEFT(I.SGESTADO + REPLICATE(' ', 2), 2)+
             LEFT(EF.SGESTADO + REPLICATE(' ', 2), 2)+
             ISNULL(B.CDBANCBOLP, '   ')+
             ISNULL(SUBSTRING(B.CDAGENBOLP,2,5), '    ') +
             ISNULL(M.NRAUTBANDOC, '                    ')+
             RIGHT(REPLICATE('0',12)+REPLACE(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2), ABS(P.VRATUAPAG))),'.',''),12)+
             RTRIM(CONVERT(CHAR, P.DTATUAVENPAG,112))+
             RTRIM(CONVERT(CHAR, P.DTATUAVENPAG,112))+
             LEFT(SUBSTRING(M.NRCONPROMER,1,22) +
             REPLICATE(' ', 30), 30) ,1,126) AS DSARQUMAG
        FROM MAPAAPURAC M, CONTAPAGAR P,  NOTAFISCAL N LEFT JOIN ENDEFORN EF ON (ISNULL(EF.IDTPENDEFORN,'P') = 'P' AND
                                                                                 N.CDFORNECED = EF.CDFORNECED),
             BOLETPAGAR B, INSCRESTAD I
       WHERE M.CDEMPRESA     = @P_CDEMPRESA
         AND M.CDIMPOSTO     = @P_CDIMPOSTO
         AND M.CDINSCESTA    = @P_CDINSCESTA
         AND M.DTINIPERIODO >= @P_DTINICIAL
         AND M.DTFIMPERIODO <= @P_DTFINAL
         AND M.CDFILIAL      = P.CDFILIAL
         AND M.NRLANCTONF    = P.NRLANCTONF
         AND N.CDFILIAL      = P.CDFILIAL
         AND N.NRLANCTONF    = P.NRLANCTONF
         AND P.CDFILIBPAG    = B.CDFILIAL
         AND P.NRLANBOLPAG   = B.NRLANBOLPAG
         AND M.CDEMPRESA     = I.CDEMPRESA
         AND M.CDINSCESTA    = I.CDINSCESTA;

   OPEN C_REGISTRO55;
   FETCH NEXT FROM C_REGISTRO55 INTO @V_DSARQUMAG;
   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_DSARQUMAG, @P_NRSEQARQMAG);

      SET @V_CONTREG   = @V_CONTREG + 1;


   FETCH NEXT FROM C_REGISTRO55 INTO @V_DSARQUMAG;
   END;
   CLOSE C_REGISTRO55;
   DEALLOCATE C_REGISTRO55;

   SET @P_CONTADOR = @V_CONTADOR;
END;
GO

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

