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

   @P_NRSEQARQMAG     VARCHAR(10),
   @P_CDEMPRESA       VARCHAR(02),
   @P_DTREFERENCIA    DATETIME,
   @P_DSNATUREZA      VARCHAR(100),
   @P_IDGERADEMCTB    VARCHAR(1),
   @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_VRITEMSTRU      DOUBLE PRECISION;
   DECLARE @V_DTULTDIAREFE    DATETIME;
   DECLARE @V_STULTDIAREFE    VARCHAR(08);
   DECLARE @V_NRLIVROESCRITU  VARCHAR(04);

   /* VARIAVEIS DO CURSOR C_AUX */
   DECLARE @C_AUX_CDTIPODEMO     VARCHAR(02);
   DECLARE @C_AUX_CDITEMSTRU     VARCHAR(04);
   DECLARE @C_AUX_NMITEMSTRU     VARCHAR(50);
   DECLARE @C_AUX_IDNATUITEM     VARCHAR(01);
   DECLARE @C_AUX_IDORIGVALOR    VARCHAR(01);
   DECLARE @C_AUX_IDTIPOVALOR    VARCHAR(01);
   DECLARE @C_AUX_IDOPERACAO     VARCHAR(01);
   DECLARE @C_AUX_VRVALORCALC    DOUBLE PRECISION;

   /* VARIAVEIS DO CURSOR C_J100 */
   DECLARE @C_J100_DSLINHA       VARCHAR(1000);

   /* VARIAVEIS DO CURSOR C_J150 */
   DECLARE @C_J150_DSLINHA       VARCHAR(1000);

   /* VARIAVEIS DO CURSOR C_CONTADOR */
   DECLARE @V_DSLINHA            VARCHAR(1000); 

   /* VARIAVEIS DO CURSOR C_SIGNATARIO */
   DECLARE @C_SIGNATARIO_DSLINHA   VARCHAR(1000);

BEGIN
   SET @V_NRORDREGARQ = @P_NRINIORDENA;

   SELECT @V_AUXILIAR = ('|I990|' + CONVERT(VARCHAR, COUNT(*) + 1) + '|')
     FROM ARQMAGNETICO
    WHERE NRSEQARQMAG = @P_NRSEQARQMAG
      AND SUBSTRING(DSREGARQMAG,2,1) = 'I';

   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    = '|J001|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);

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

      SET @V_STULTDIAREFE = SUBSTRING(CONVERT(VARCHAR, @V_DTULTDIAREFE, 103), 01, 02) +
                            SUBSTRING(CONVERT(VARCHAR, @V_DTULTDIAREFE, 103), 04, 02) +
                            SUBSTRING(CONVERT(VARCHAR, @V_DTULTDIAREFE, 103), 07, 04);
   END
   ELSE IF (@P_IDSPED = 'F') BEGIN
      SELECT @V_DTULTDIAREFE = MAX(DTDATA)
        FROM DATA
       WHERE DTMES = '01/12/' + SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 07, 04);

      SET @V_STULTDIAREFE = SUBSTRING(CONVERT(VARCHAR, @V_DTULTDIAREFE, 103), 01, 02) +
                            SUBSTRING(CONVERT(VARCHAR, @V_DTULTDIAREFE, 103), 04, 02) +
                            SUBSTRING(CONVERT(VARCHAR, @V_DTULTDIAREFE, 103), 07, 04);
   END;

   SET @V_AUXILIAR = '|J005|01' + SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 04, 02) +
                     SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 07, 04) + '|' +
                     @V_STULTDIAREFE + '|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);

   IF (@P_IDGERADEMCTB = 'S') BEGIN
      /*INICIO GERACAO DO REGISTRO J100*/
      DECLARE C_AUX CURSOR FOR
         SELECT I.CDTIPODEMO,  I.CDITEMSTRU,
                I.NMITEMSTRU,  I.IDNATUITEM,
                I.IDORIGVALOR, I.IDTIPOVALOR,
                I.IDOPERACAO,  I.VRVALORCALC
           FROM ITEMDEMOF I, TIPODEMO T
          WHERE I.CDTIPODEMO = T.CDTIPODEMO
            AND T.IDREFESPED = 'B'
            AND T.IDTIPODEMO = 'O'
            AND I.IDORIGVALOR <> 'T'
          ORDER BY I.CDTIPODEMO, I.CDITEMSTRU;
      OPEN C_AUX;
      FETCH NEXT FROM C_AUX INTO @C_AUX_CDTIPODEMO, @C_AUX_CDITEMSTRU,  @C_AUX_NMITEMSTRU,
                                 @C_AUX_IDNATUITEM, @C_AUX_IDORIGVALOR, @C_AUX_IDTIPOVALOR,
                                 @C_AUX_IDOPERACAO, @C_AUX_VRVALORCALC;

      WHILE (@@FETCH_STATUS = 0) BEGIN
         IF (@C_AUX_IDORIGVALOR = 'A') BEGIN
            SELECT @V_VRITEMSTRU = SUM(VRSALDOITEM)
              FROM (SELECT ISNULL(SUM(ROUND(S.VRSALDEMPR, 2)), 0) AS VRSALDOITEM
                      FROM SALDEMPR S, ITEMAPUCTB I
                     WHERE (S.CDEMPRESA  = @P_CDEMPRESA)
                       AND (S.DTSALDEMPR = @V_DTULTDIAREFE)
                       AND (S.CDCONTCTBL = I.CDORIGDEMO)
                       AND (I.CDTIPODEMO = @C_AUX_CDTIPODEMO)
                       AND (I.CDITEMSTRU = @C_AUX_CDITEMSTRU)
                       AND (I.IDTIPOORIG = 'S')
                     UNION ALL
                    SELECT ISNULL(SUM(ROUND(S.VRITAPSPED, 2)), 0) AS VRSALDOITEM
                      FROM SALDITAPSPED S, ITEMAPUCTB I
                     WHERE S.NRITAPSPED = @P_NRSEQARQMAG
                       AND I.CDTIPODEMO = @C_AUX_CDTIPODEMO
                       AND I.CDITEMSTRU = @C_AUX_CDITEMSTRU
                       AND S.CDDEMOSPED = SUBSTRING(I.CDORIGDEMO, 1, 2)
                       AND S.CDITAPSPED = SUBSTRING(I.CDORIGDEMO, 3, 4)
                       AND I.IDTIPOORIG = 'V') A;

            INSERT INTO SALDITAPSPED
               (NRITAPSPED, CDDEMOSPED, CDITAPSPED, VRITAPSPED)
            VALUES
               (@P_NRSEQARQMAG, @C_AUX_CDTIPODEMO, @C_AUX_CDITEMSTRU, @V_VRITEMSTRU);
         END;
         FETCH NEXT FROM C_AUX INTO @C_AUX_CDTIPODEMO, @C_AUX_CDITEMSTRU,  @C_AUX_NMITEMSTRU,
                                    @C_AUX_IDNATUITEM, @C_AUX_IDORIGVALOR, @C_AUX_IDTIPOVALOR,
                                    @C_AUX_IDOPERACAO, @C_AUX_VRVALORCALC;
      END;
      CLOSE C_AUX;
      DEALLOCATE C_AUX;

      DECLARE C_J100 CURSOR FOR
         SELECT '|J100|' +
                I.IDGRUPOBALSPED + I.CDITEMSTRU + '|' +
                I.CDNIVELITDEMOF + '|' +
                I.IDGRUPOBALSPED + '|' +
                I.NMITEMSTRU + '|' +
                REPLACE(LTRIM(CONVERT(VARCHAR, ISNULL(ABS(S.VRITAPSPED),0))),'.',',') + '|' +
                (CASE S.VRITAPSPED WHEN ABS(S.VRITAPSPED) THEN 'C' ELSE 'D' END) + '|' AS DSLINHA
           FROM ITEMDEMOF I, TIPODEMO T, SALDITAPSPED S
          WHERE I.CDTIPODEMO = T.CDTIPODEMO
            AND T.IDREFESPED = 'B'
            AND T.IDTIPODEMO = 'O'
            AND I.IDORIGVALOR <> 'T'
            AND I.CDTIPODEMO = S.CDDEMOSPED
            AND I.CDITEMSTRU = S.CDITAPSPED
            AND S.NRITAPSPED = @P_NRSEQARQMAG
          ORDER BY I.CDTIPODEMO, I.CDITEMSTRU;
      OPEN C_J100;
      FETCH NEXT FROM C_J100 INTO @C_J100_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_J100_DSLINHA, @P_NRSEQARQMAG);

         FETCH NEXT FROM C_J100 INTO @C_J100_DSLINHA;
      END;
      CLOSE C_J100;
      DEALLOCATE C_J100;
      /*FIM GERACAO DO REGISTRO J100*/

      /*INICIO GERACAO DO REGISTRO J150*/
      DECLARE C_AUX CURSOR FOR
         SELECT I.CDTIPODEMO, I.CDITEMSTRU,
                I.NMITEMSTRU, I.IDNATUITEM,
                I.IDORIGVALOR, I.IDTIPOVALOR,
                I.IDOPERACAO, I.VRVALORCALC
           FROM ITEMDEMOF I, TIPODEMO T
          WHERE I.CDTIPODEMO = T.CDTIPODEMO
            AND T.IDREFESPED = 'D'
            AND T.IDTIPODEMO = 'O'
            AND I.IDORIGVALOR <> 'T'
          ORDER BY I.CDTIPODEMO, I.CDITEMSTRU;
      OPEN C_AUX;
      FETCH NEXT FROM C_AUX INTO @C_AUX_CDTIPODEMO, @C_AUX_CDITEMSTRU,  @C_AUX_NMITEMSTRU,
                                 @C_AUX_IDNATUITEM, @C_AUX_IDORIGVALOR, @C_AUX_IDTIPOVALOR,
                                 @C_AUX_IDOPERACAO, @C_AUX_VRVALORCALC;

      WHILE (@@FETCH_STATUS = 0) BEGIN
         IF (@C_AUX_IDORIGVALOR = 'A') BEGIN
            SELECT @V_VRITEMSTRU = SUM(VRSALDOITEM)
              FROM (SELECT ISNULL(SUM(ROUND(S.VRSALDOTRANSF, 2)),0) AS VRSALDOITEM
                      FROM SALDOTRANSF S, ITEMAPUCTB I
                     WHERE S.CDEMPRESA     = @P_CDEMPRESA
                       AND S.DTSALDOTRANSF = @V_DTULTDIAREFE
                       AND S.CDCONTCTBL    = I.CDORIGDEMO
                       AND I.CDTIPODEMO    = @C_AUX_CDTIPODEMO
                       AND I.CDITEMSTRU    = @C_AUX_CDITEMSTRU
                       AND I.IDTIPOORIG    = 'S'
                    UNION ALL
                    SELECT ISNULL(SUM(ROUND(S.VRITAPSPED, 2)), 0) AS VRSALDOITEM
                      FROM SALDITAPSPED S, ITEMAPUCTB I
                     WHERE S.NRITAPSPED = @P_NRSEQARQMAG
                       AND I.CDTIPODEMO = @C_AUX_CDTIPODEMO
                       AND I.CDITEMSTRU = @C_AUX_CDITEMSTRU
                       AND S.CDDEMOSPED = SUBSTRING(I.CDORIGDEMO, 1, 2)
                       AND S.CDITAPSPED = SUBSTRING(I.CDORIGDEMO, 3, 4)
                       AND I.IDTIPOORIG = 'V') A;

            INSERT INTO SALDITAPSPED
               (NRITAPSPED, CDDEMOSPED, CDITAPSPED, VRITAPSPED)
            VALUES
               (@P_NRSEQARQMAG, @C_AUX_CDTIPODEMO, @C_AUX_CDITEMSTRU, @V_VRITEMSTRU);
         END;
         FETCH NEXT FROM C_AUX INTO @C_AUX_CDTIPODEMO, @C_AUX_CDITEMSTRU,  @C_AUX_NMITEMSTRU,
                                    @C_AUX_IDNATUITEM, @C_AUX_IDORIGVALOR, @C_AUX_IDTIPOVALOR,
                                    @C_AUX_IDOPERACAO, @C_AUX_VRVALORCALC;
      END;
      CLOSE C_AUX;
      DEALLOCATE C_AUX;

      DECLARE C_J150 CURSOR FOR
         SELECT '|J150|' +
                I.IDGRUPOBALSPED + I.CDITEMSTRU + '|' +
                I.CDNIVELITDEMOF + '|' +
                I.NMITEMSTRU + '|' +
                REPLACE(LTRIM(CONVERT(VARCHAR, CAST(ROUND(ISNULL(ABS(S.VRITAPSPED), 0), 2) AS MONEY))),'.',',') + '|' +
                I.IDSTSALDOITSPED + '|' AS DSLINHA
           FROM ITEMDEMOF I, TIPODEMO T, SALDITAPSPED S
          WHERE I.CDTIPODEMO = T.CDTIPODEMO
            AND T.IDREFESPED = 'D'
            AND T.IDTIPODEMO = 'O'
            AND I.IDORIGVALOR <> 'T'
            AND I.CDTIPODEMO = S.CDDEMOSPED
            AND I.CDITEMSTRU = S.CDITAPSPED
            AND ISNULL(I.IDSTSALDOITSPED,'I') <> 'I'
            AND S.NRITAPSPED = @P_NRSEQARQMAG
          ORDER BY I.CDTIPODEMO, I.CDITEMSTRU
      OPEN C_J150;
      FETCH NEXT FROM C_J150 INTO @C_J150_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_J150_DSLINHA, @P_NRSEQARQMAG);

         FETCH NEXT FROM C_J150 INTO @C_J150_DSLINHA;
      END;
      CLOSE C_J150;
      DEALLOCATE C_J150;
      /*FIM GERACAO DO REGISTRO J150*/

      DELETE FROM SALDITAPSPED
       WHERE NRITAPSPED = @P_NRSEQARQMAG;
   END;

   SELECT @V_NRLIVROESCRITU = NRLIVROESCRITU
     FROM TERMOABERT
    WHERE CDLIVRCONT = 'D'
     AND CDEMPRESA =  @P_CDEMPRESA
     AND IDMODELO   = 'E';

   IF @V_NRLIVROESCRITU IS NULL BEGIN
      SET @V_NRLIVROESCRITU = '1';
   END;

   SELECT @V_AUXILIAR = '|J900|TERMO DE ENCERRAMENTO|'+
          @V_NRLIVROESCRITU + '|' +
          @P_DSNATUREZA + '|' +
          E.NMRAZSOCEMPR + '|' +
          '#QTDE_TOTAL#|' +
          '01' + SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 04, 02) +
          SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 07, 04) + '|' +
          @V_STULTDIAREFE + '|'
     FROM EMPRESA E
    WHERE 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);

   DECLARE C_CONTADOR CURSOR FOR
      SELECT '|J930|' +
             NMCONTADOR + '|' +
             NRCPFRESPONS + '|' +
             ISNULL(Q.NMQUALIFASSINA,'Contabilista') + '|' +
             ISNULL(C.CDQUALIFASSINA,'900') + '|' +
             ISNULL(NRCRCCONT2,'') + '|' AS DSLINHA
        FROM EMPRESA E, CONTADOR C, QUALIFASSINANTE Q
       WHERE E.CDEMPRESA      = @P_CDEMPRESA
         AND E.CDCONTADOR2    = C.CDCONTADOR2
         AND Q.CDQUALIFASSINA = C.CDQUALIFASSINA;
   OPEN C_CONTADOR;
   FETCH NEXT FROM C_CONTADOR INTO @V_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), @V_DSLINHA, @P_NRSEQARQMAG);
      FETCH NEXT FROM C_CONTADOR INTO @V_DSLINHA;
   END;
   CLOSE C_CONTADOR;
   DEALLOCATE C_CONTADOR;

   DECLARE C_SIGNATARIO CURSOR FOR
       SELECT '|J930|' +
              R.NMREPRES + '|' +
              R.NRCPFREPRES + '|' +
              Q.NMQUALIFASSINA + '|' +
              R.CDQUALIFASSINA + '||' AS DSLINHA
         FROM REPRESLEGAL R, REPRESEMPRESA RE, QUALIFASSINANTE Q
        WHERE RE.CDEMPRESA      = @P_CDEMPRESA
          AND RE.CDREPRES = R.CDREPRES
          AND RE.IDATIVO = 'S'
          AND R.CDQUALIFASSINA = Q.CDQUALIFASSINA;
   OPEN C_SIGNATARIO;
   FETCH NEXT FROM C_SIGNATARIO INTO @C_SIGNATARIO_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_SIGNATARIO_DSLINHA, @P_NRSEQARQMAG);

      FETCH NEXT FROM C_SIGNATARIO INTO @C_SIGNATARIO_DSLINHA;
   END;
   CLOSE C_SIGNATARIO;
   DEALLOCATE C_SIGNATARIO;

   SELECT @V_AUXILIAR = '|J990|' +
          CONVERT(VARCHAR, COUNT(*) + 1) + '|'
     FROM ARQMAGNETICO
    WHERE NRSEQARQMAG = @P_NRSEQARQMAG
      AND SUBSTRING(DSREGARQMAG,2,1) = 'J';

   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 @P_NRORDREGARQ = @V_NRORDREGARQ;
END;
GO

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