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

DROP PROCEDURE ARQUIVO_SINTEGRA_CABECALHO
GO
CREATE PROCEDURE ARQUIVO_SINTEGRA_CABECALHO
   @P_CDEMPRESA     VARCHAR(02),
   @P_CDIMPOSTO     VARCHAR(02),
   @P_CDINSCESTA    VARCHAR(16),
   @P_DTINICIAL     DATETIME,
   @P_DTFINAL       DATETIME,
   @P_NRSEQARQMAG   VARCHAR(10),
   @P_FINALIDADE    VARCHAR(01),
   @P_VERSAO        VARCHAR(01),
   @P_CONTADOR      NUMERIC       OUTPUT
AS
   DECLARE @V_REGISTRO10 VARCHAR(250);
   DECLARE @V_REGISTRO11 VARCHAR(250);
   DECLARE @V_CONTADOR   NUMERIC;
BEGIN
   SET @V_CONTADOR = 0;
   DECLARE C_REGISTRO10 CURSOR FOR
      SELECT SUBSTRING('10'+
             LEFT(SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.',''),',',''),'-',''),'/',''),1,14)+REPLICATE(' ',14),14)+
             LEFT(REPLACE(REPLACE(REPLACE(REPLACE(CASE SUBSTRING(UPPER(IE.CDINSCESTA),1,5)WHEN 'ISENT'THEN 'ISENTO' ELSE IE.CDINSCESTA END,'.',''),',',''),'-',''),'/','')+REPLICATE(' ',14),14)+
             LEFT(EP.NMRAZSOCEMPR + REPLICATE(' ',35),35)+
             LEFT(MU.NMMUNICIPIO + REPLICATE(' ',30),30)+
             LEFT(IE.SGESTADO + REPLICATE(' ',2),2)+
             RIGHT(REPLICATE('0',10)+REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(IE.NRTELEIEST,'0'),' ',''),')',''),'(',''),'-',''),'.',''),10)+
             RTRIM(CONVERT(CHAR,@P_DTINICIAL,112))+
             RTRIM(CONVERT(CHAR,@P_DTFINAL,112))+
             @P_VERSAO +'3'+@P_FINALIDADE,1,126) AS DSARQMAG
        FROM EMPRESA EP, INSCRESTAD IE LEFT JOIN MUNICIPIO MU ON(    IE.CDPAIS      = MU.CDPAIS
                                                                 AND IE.SGESTADO    = MU.SGESTADO
                                                                 AND IE.CDMUNICIPIO = MU.CDMUNICIPIO)
          WHERE EP.CDEMPRESA   = @P_CDEMPRESA
            AND IE.CDEMPRESA   = EP.CDEMPRESA
            AND IE.CDINSCESTA  = @P_CDINSCESTA;

   OPEN C_REGISTRO10;
   FETCH NEXT FROM C_REGISTRO10 INTO @V_REGISTRO10;

   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('00'), 8), @V_REGISTRO10, @P_NRSEQARQMAG);

   FETCH NEXT FROM C_REGISTRO10 INTO @V_REGISTRO10;
   END;
   CLOSE C_REGISTRO10;
   DEALLOCATE C_REGISTRO10;

   SET @V_CONTADOR = @V_CONTADOR + 1;
   DECLARE C_REGISTRO11 CURSOR FOR
      SELECT SUBSTRING(
             '11'+
             LEFT(IE.DSENDEIEST+REPLICATE(' ',34),34)+
             LEFT('00000s/n'+REPLICATE(' ',27),27)+
             LEFT(IE.NMBAIRIEST+REPLICATE(' ',15),15)+
             RIGHT(REPLICATE('0',8)+REPLACE(REPLACE(IE.NRCEPIEST,'.',''),'-',''),8)+
             LEFT(ISNULL(IE.NMREPRINSEST, '')+REPLICATE(' ',28),28)+
             RIGHT(REPLICATE('0',12)+REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(IE.NRTELEIEST,'0'),' ',''),')',''),'(',''),'-',''),'.',''),12)
             ,1,126) AS DSARQMAG
          FROM INSCRESTAD IE
          WHERE IE.CDEMPRESA   = @P_CDEMPRESA
            AND IE.CDINSCESTA  = @P_CDINSCESTA;

   OPEN C_REGISTRO11;
   FETCH NEXT FROM C_REGISTRO11 INTO @V_REGISTRO11;

   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('00'), 8), @V_REGISTRO11, @P_NRSEQARQMAG);

   FETCH NEXT FROM C_REGISTRO11 INTO @V_REGISTRO11;
   END;
   CLOSE C_REGISTRO11;
   DEALLOCATE C_REGISTRO11;
   SET @P_CONTADOR  = @V_CONTADOR;
END;
GO

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

