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

GO


CREATE PROCEDURE SPED_CONTABIL_CABECALHO (
   @P_NRSEQARQMAG  VARCHAR(10),
   @P_CDEMPRESA    VARCHAR(4),
   @P_DTREFERENCIA DATETIME,
   @P_IDESCRITURA  CHAR,
   @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(1000);
   DECLARE @V_DSLINHA     VARCHAR(1000);
   DECLARE @V_ULTDIAREFE  DATETIME;

   /* VARIAVEIS DO CURSOR C_0007 */
   DECLARE @C_0007_DSLINHA VARCHAR(2000);

BEGIN
   IF (@P_IDSPED = 'S') BEGIN
      BEGIN
         SET @V_NRORDREGARQ = 0;

         SELECT @V_ULTDIAREFE = MAX(DTDATA) 
           FROM DATA
          WHERE (DTMES = '01/' + SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 04, 07));

         SELECT @V_AUXILIAR = '|0000|' + 
                'LECD|' + 
                '0101'  + SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 7, 4) + '|' +       
                SUBSTRING(CONVERT(VARCHAR, @V_ULTDIAREFE, 103), 1, 2) + SUBSTRING(CONVERT(VARCHAR, @V_ULTDIAREFE, 103), 4, 2) + SUBSTRING(CONVERT(VARCHAR, @V_ULTDIAREFE, 103), 7, 4) + '|' + 
                E.NMRAZSOCEMPR + '|' + 
                E.NRINSJUREMPR + '|' + 
                E.SGESTADO + '|' + 
                ISNULL(E.NRINSCESTA,'') + '|' + 
                RIGHT(REPLICATE('0', 7) + ISNULL(M.CDMUNICIBGE,'0'), 7) + '|' + 
                ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(E.CDINSCMUNI,''), ' ', ''),'.',''),',',''),'-',''),'/',''),'') + '||'
         FROM EMPRESA E, MUNICIPIO M
         WHERE E.CDPAIS      = M.CDPAIS
           AND E.SGESTADO    = M.SGESTADO
           AND E.CDMUNICIPIO = M.CDMUNICIPIO
           AND E.CDEMPRESA   = @P_CDEMPRESA;

         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_NRORDREGARQ = @V_NRORDREGARQ + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, @V_NRORDREGARQ), 10), '|0001|0|', @P_NRSEQARQMAG);

         DECLARE C_0007 CURSOR FOR
            SELECT '|0007|' + 
                   I.SGESTADO + '|' + 
                   I.CDINSCESTA + '|' AS DSLINHA
              FROM INSCRESTAD I
             WHERE I.CDEMPRESA = @P_CDEMPRESA
             ORDER BY I.SGESTADO, I.CDINSCESTA;
         OPEN C_0007;
         FETCH NEXT FROM C_0007 INTO @C_0007_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_0007_DSLINHA, @P_NRSEQARQMAG);
 
            FETCH NEXT FROM C_0007 INTO @C_0007_DSLINHA;
         END;
         CLOSE C_0007;
         DEALLOCATE C_0007;

         SET @V_NRORDREGARQ = @V_NRORDREGARQ + 1;

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

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

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

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

         SET @P_NRORDREGARQ = @V_NRORDREGARQ;
      END;
   END
   ELSE IF (@P_IDSPED = 'F') BEGIN
      BEGIN

         SELECT @V_ULTDIAREFE = MAX(DTDATA) 
           FROM DATA
          WHERE (DTMES = '01/12/' + SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 07, 04));

         SET @V_NRORDREGARQ = 0;

         SELECT @V_AUXILIAR = '|0000|' + 
                'LALU|' + 
                '0101' + SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 07, 04)+ '|' + 
                SUBSTRING(CONVERT(VARCHAR, @V_ULTDIAREFE, 103), 01, 02) + SUBSTRING(CONVERT(VARCHAR, @V_ULTDIAREFE, 103), 04, 02) + SUBSTRING(CONVERT(VARCHAR, @V_ULTDIAREFE, 103), 07, 04) + '|' + 
                E.NMRAZSOCEMPR + '|' + 
                E.NRINSJUREMPR + '|' + 
                E.SGESTADO + '|' + 
                ISNULL(E.NRINSCESTA,'') + '|' + 
                RIGHT(REPLICATE('0', 7) + ISNULL(M.CDMUNICIBGE,'0'), 7) + '|' + 
                ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(E.CDINSCMUNI,''), ' ', ''),'.',''),',',''),'-',''),'/',''),'') + '||0|'
           FROM EMPRESA E, MUNICIPIO M
          WHERE E.CDPAIS      = M.CDPAIS
            AND E.SGESTADO    = M.SGESTADO
            AND E.CDMUNICIPIO = M.CDMUNICIPIO
            AND E.CDEMPRESA   = @P_CDEMPRESA;

         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_NRORDREGARQ = @V_NRORDREGARQ + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, @V_NRORDREGARQ), 10), '|0001|0|', @P_NRSEQARQMAG);

         DECLARE C_0007 CURSOR FOR
            SELECT '|0007|' + 
                   I.SGESTADO + '|' + 
                   I.CDINSCESTA + '|' AS DSLINHA
              FROM INSCRESTAD I
             WHERE I.CDEMPRESA = @P_CDEMPRESA
             ORDER BY I.SGESTADO, I.CDINSCESTA;
         OPEN C_0007;
         FETCH NEXT FROM C_0007 INTO @C_0007_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_0007_DSLINHA, @P_NRSEQARQMAG);

            FETCH NEXT FROM C_0007 INTO @C_0007_DSLINHA;
         END;
         CLOSE C_0007;
         DEALLOCATE C_0007;
         
         SET @V_NRORDREGARQ = @V_NRORDREGARQ + 1;
         SELECT @V_AUXILIAR = '|0990|' + 
                CONVERT(VARCHAR, COUNT(NRSEQARQMAG) + 1) + '|'
           FROM ARQMAGNETICO
          WHERE NRSEQARQMAG = @P_NRSEQARQMAG;

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

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

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

         SET @P_NRORDREGARQ = @V_NRORDREGARQ;
      END;
   END;
END;
GO

-- *VERSAO* --
EXEC ATUVEROBJ 'PROCEDURE SPED_CONTABIL_CABECALHO','5.30.000';
GO

