IF EXISTS(SELECT NAME FROM SYSOBJECTS
           WHERE NAME = 'SPED_CONTABIL_ENCERRAMENTO')
   DROP PROCEDURE SPED_CONTABIL_ENCERRAMENTO
GO

CREATE PROCEDURE SPED_CONTABIL_ENCERRAMENTO (
   @P_NRSEQARQMAG  VARCHAR(10),
   @P_CDEMPRESA    VARCHAR(2),
   @P_DTREFERENCIA DATETIME,
   @P_DSNATUREZA   VARCHAR(100),
   @P_NRINIORDENA  INTEGER,
   @P_IDSPED       VARCHAR(01), /* PARAMETRO QUE DEFINE SE SERA GERADO SPED OU FCONT */
   @P_NRORDREGARQ  INTEGER OUTPUT)
AS
   DECLARE @V_NRORDREGARQ   INTEGER;
   DECLARE @V_AUXILIAR      VARCHAR(200);
   DECLARE @V_CONTADOR      INTEGER;

   /* VARIAVEIS DO CURSOR C_9900 */
   DECLARE @C_9900_DSLINHA  VARCHAR(500);

   /* VARIAVEIS DO CURSOR C_ATUARQMAG */
   DECLARE @C_ATUARQMAG_DSREGARQMAG VARCHAR(500);
BEGIN
   SET @V_NRORDREGARQ = @P_NRINIORDENA;

   SET @V_AUXILIAR    = '|9001|0|';
   SET @V_NRORDREGARQ = @V_NRORDREGARQ + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     (RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, @V_NRORDREGARQ), 10), @V_AUXILIAR, @P_NRSEQARQMAG);

   SET @V_CONTADOR = 1;

   DECLARE C_9900 CURSOR FOR
      SELECT '|9900|' +
             SUBSTRING(DSREGARQMAG, 02, 04) + '|' +
             CONVERT(VARCHAR, COUNT(*)) + '|' AS DSLINHA
        FROM ARQMAGNETICO
       WHERE NRSEQARQMAG = @P_NRSEQARQMAG
         AND SUBSTRING(DSREGARQMAG, 02, 01) <> '9'
       GROUP BY SUBSTRING(DSREGARQMAG, 02, 04)
       ORDER BY SUBSTRING(DSREGARQMAG, 02, 04);
   OPEN C_9900;
   FETCH NEXT FROM C_9900 INTO @C_9900_DSLINHA;

   WHILE (@@FETCH_STATUS = 0) BEGIN
      SET @V_NRORDREGARQ = @V_NRORDREGARQ + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        (RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, @V_NRORDREGARQ), 10), @C_9900_DSLINHA, @P_NRSEQARQMAG);

      SET @V_CONTADOR = @V_CONTADOR + 1;
      FETCH NEXT FROM C_9900 INTO @C_9900_DSLINHA;
   END;
   CLOSE C_9900;
   DEALLOCATE C_9900;

   SET @V_AUXILIAR    = '|9900|9001|1|';
   SET @V_NRORDREGARQ = @V_NRORDREGARQ + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     (RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, @V_NRORDREGARQ), 10), @V_AUXILIAR, @P_NRSEQARQMAG);

   SET @V_AUXILIAR    = '|9900|9900|' + CONVERT(VARCHAR, @V_CONTADOR + 3) + '|';
   SET @V_NRORDREGARQ = @V_NRORDREGARQ + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     (RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, @V_NRORDREGARQ), 10), @V_AUXILIAR, @P_NRSEQARQMAG);

   SET @V_AUXILIAR    = '|9900|9990|1|';
   SET @V_NRORDREGARQ = @V_NRORDREGARQ + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     (RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, @V_NRORDREGARQ), 10), @V_AUXILIAR, @P_NRSEQARQMAG);

   SET @V_AUXILIAR    = '|9900|9999|1|';
   SET @V_NRORDREGARQ = @V_NRORDREGARQ + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     (RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, @V_NRORDREGARQ), 10), @V_AUXILIAR, @P_NRSEQARQMAG);

   SELECT @V_AUXILIAR = '|9990|' +
          CONVERT(VARCHAR, COUNT(*) + 2) + '|'
     FROM ARQMAGNETICO
    WHERE NRSEQARQMAG = @P_NRSEQARQMAG
      AND SUBSTRING(DSREGARQMAG, 02, 01) = '9';

   SET @V_NRORDREGARQ = @V_NRORDREGARQ + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     (RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, @V_NRORDREGARQ), 10), @V_AUXILIAR, @P_NRSEQARQMAG);

   SELECT @V_AUXILIAR = '|9999|' + CONVERT(VARCHAR, COUNT(*) + 1) + '|',
          @V_CONTADOR = (COUNT(*) + 1)
     FROM ARQMAGNETICO
    WHERE NRSEQARQMAG = @P_NRSEQARQMAG;

   SET @V_NRORDREGARQ = @V_NRORDREGARQ + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     (RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, @V_NRORDREGARQ), 10), @V_AUXILIAR, @P_NRSEQARQMAG);

   UPDATE ARQMAGNETICO
      SET DSREGARQMAG = REPLACE(CONVERT(VARCHAR(200), DSREGARQMAG), '#QTDE_TOTAL#', CONVERT(VARCHAR, @V_CONTADOR))
    WHERE NRSEQARQMAG = @P_NRSEQARQMAG
      AND SUBSTRING(DSREGARQMAG, 02, 04) IN ('I030','J900');


   SET @P_NRORDREGARQ = @V_NRORDREGARQ;
END;
GO

-- VERSAO --
EXEC ATUVEROBJ 'SPED_CONTABIL_ENCERRAMENTO','5.45.000';
GO