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

DROP PROCEDURE ARQUIVO_SINTEGRA_90
GO
CREATE PROCEDURE ARQUIVO_SINTEGRA_90
   @P_CDEMPRESA        VARCHAR(02),
   @P_CDINSCESTA       VARCHAR(16),
   @P_NRSEQARQMAG      VARCHAR(10),
   @P_CONTADOR         NUMERIC       OUTPUT
AS
   DECLARE @V_LINHA        VARCHAR(250);
   DECLARE @V_TOTAL_LINHA  VARCHAR(250);
   DECLARE @V_TOTAL_REG    NUMERIC;
   DECLARE @V_TOT_REG      NUMERIC;
   DECLARE @V_REGISTRO90   VARCHAR(250);
   DECLARE @V_CONTADOR     NUMERIC;
BEGIN
   SET @V_CONTADOR = @P_CONTADOR +1;
   DECLARE C_REG_90 CURSOR FOR
      SELECT SUBSTRING(DSREGARQMAG,1,2) AS REGISTRO90,
             RIGHT(REPLICATE('0',8)+CONVERT(VARCHAR,COUNT(*)),8)AS LINHA,
             COUNT(*) AS TOT_REG
        FROM ARQMAGNETICO
       WHERE NRSEQARQMAG = @P_NRSEQARQMAG
         AND SUBSTRING(DSREGARQMAG,1,2) NOT IN ('10','11','90')
       GROUP BY SUBSTRING(DSREGARQMAG,1,2)
       ORDER BY SUBSTRING(DSREGARQMAG,1,2);

   OPEN C_REG_90;
   FETCH NEXT FROM C_REG_90 INTO @V_REGISTRO90,
                                 @V_LINHA,
                                 @V_TOTAL_REG;
      WHILE (@@FETCH_STATUS = 0) BEGIN

         SET @V_TOTAL_LINHA = ISNULL(@V_TOTAL_LINHA,'')+@V_REGISTRO90+ @V_LINHA;
         SET @V_TOT_REG = ISNULL(@V_TOT_REG,0) + @V_TOTAL_REG;

   FETCH NEXT FROM C_REG_90 INTO @V_REGISTRO90,
                                 @V_LINHA,
                                 @V_TOTAL_REG;
   END;
   CLOSE C_REG_90;
   DEALLOCATE C_REG_90;

   SET @V_REGISTRO90 = NULL;

   DECLARE C_REGISTRO90 CURSOR FOR
      SELECT SUBSTRING(LEFT('90'+
             LEFT(ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJURIEST,'.',''),',',''),'/',''),'-',''),'00000000000000')+REPLICATE(' ',14),14)+
             LEFT(ISNULL(REPLACE(REPLACE(REPLACE(CASE SUBSTRING(UPPER(@P_CDINSCESTA),1,5) WHEN 'ISENT' THEN 'ISENTO' ELSE @P_CDINSCESTA END,'.',''),',',''),'-',''),'ISENTO')+REPLICATE(' ',14),14)+
             @V_TOTAL_LINHA +
             '99'+
             RIGHT(REPLICATE('0',8)+CONVERT(VARCHAR,@V_TOT_REG+3),8)+
             REPLICATE(' ',125), 125)+
             '1' ,1, 126) AS DSARQMAG
          FROM  INSCRESTAD
          WHERE CDEMPRESA  = @P_CDEMPRESA AND
                CDINSCESTA = @P_CDINSCESTA;

   OPEN C_REGISTRO90;
   FETCH NEXT FROM C_REGISTRO90 INTO @V_REGISTRO90;

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

   FETCH NEXT FROM C_REGISTRO90 INTO @V_REGISTRO90;
   END;
   CLOSE C_REGISTRO90;
   DEALLOCATE C_REGISTRO90;
   SET @P_CONTADOR = @V_CONTADOR;
END;
GO

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

