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

CREATE PROCEDURE SPED_CONTABIL_LANCAMENTO (
   @P_NRSEQARQMAG  VARCHAR(10),
   @P_CDEMPRESA    VARCHAR(2),
   @P_DTREFERENCIA DATETIME,
   @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_DTULTDIAREFE  DATETIME;
   DECLARE @V_STULTDIAREFE  VARCHAR(10);
   DECLARE @V_DATA          VARCHAR(10);
   DECLARE @V_ULTIMODIA     DATETIME;


   /* VARIAVEIS DO CURSOR C_I200 */
   DECLARE @C_I200_DSLINHA  VARCHAR(500);
BEGIN
   IF (@P_IDSPED = 'S') BEGIN
      SET @V_NRORDREGARQ = @P_NRINIORDENA;
      DECLARE C_DATA CURSOR FOR
         SELECT DISTINCT SUBSTRING(CONVERT(VARCHAR, DTDATA, 103),4, 8) DTDATA
           FROM DATA
          WHERE SUBSTRING(CONVERT(VARCHAR, DTMES, 103), 7, 4) = SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 7, 4)
          ORDER BY 1;
         OPEN C_DATA
         FETCH NEXT FROM C_DATA INTO @V_DATA;
         WHILE @@FETCH_STATUS = 0 BEGIN
            SET @V_STULTDIAREFE = (SELECT CONVERT(VARCHAR, MAX(DTDATA), 103)
                                     FROM DATA
                                    WHERE DTMES = '01/' + @V_DATA);
            SET @V_ULTIMODIA = @V_STULTDIAREFE;
            SET @V_STULTDIAREFE = SUBSTRING(@V_STULTDIAREFE, 1, 2) + SUBSTRING(@V_STULTDIAREFE, 4, 2) + SUBSTRING(@V_STULTDIAREFE, 7, 4)

            DECLARE C_I200 CURSOR FOR
               SELECT DSLINHA
                 FROM (SELECT L.NRLANCCONT,
                              '0000' AS NRSEQLANC,
                              '|I200|' +
                              L.NRLANCCONT + '|' +
                              SUBSTRING(CONVERT(VARCHAR, L.DTLANCCONT, 103), 01, 02) +
                              SUBSTRING(CONVERT(VARCHAR, L.DTLANCCONT, 103), 04, 02) +
                              SUBSTRING(CONVERT(VARCHAR, L.DTLANCCONT, 103), 07, 04) + '|' +
                              REPLACE(LTRIM(CONVERT(VARCHAR, ISNULL(SUM(ROUND(L.VRLANCAMENT, 2)),0))),'.',',') + '|' +
                              'N|' AS DSLINHA
                         FROM LANCCONT L, CONTCTBL C
                        WHERE (L.CDEMPRESA = @P_CDEMPRESA)
                          AND L.DTLANCCONT BETWEEN '01/' + @V_DATA AND @V_ULTIMODIA
                          AND (L.VRLANCAMENT > 0)
                          AND (C.CDCONTCTBL = L.CDCONTCTBL)
                          AND (C.IDCONTCCUS <> 'R')
                        GROUP BY NRLANCCONT, DTLANCCONT
                       UNION ALL
                       SELECT NRLANCCONT,
                              NRSEQLANC,
                              '|I250|' +
                              L.CDCONTCTBL + '||' +
                              REPLACE(LTRIM(CONVERT(VARCHAR, ISNULL(ABS(ROUND(VRLANCAMENT, 2)),0))),'.',',') + '|' +
                              (CASE VRLANCAMENT WHEN ABS(VRLANCAMENT) THEN 'C' ELSE 'D' END) + '||' +
                              '|' +
                              REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DSHISTLANC,char(13), ' '),char(09), ' '),char(10), ' '), char(11), ' '),'||',' ') + '||' AS DSLINHA
                         FROM LANCCONT L, CONTCTBL C
                        WHERE L.CDEMPRESA = @P_CDEMPRESA
                          AND L.DTLANCCONT BETWEEN '01/' + @V_DATA AND @V_ULTIMODIA
                          AND (C.CDCONTCTBL = L.CDCONTCTBL)
                          AND (C.IDCONTCCUS <> 'R')) L
                ORDER BY L.NRLANCCONT, L.NRSEQLANC;
                OPEN C_I200;
                FETCH NEXT FROM C_I200 INTO @C_I200_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_I200_DSLINHA, @P_NRSEQARQMAG);

                   FETCH NEXT FROM C_I200 INTO @C_I200_DSLINHA;
                END;
                CLOSE C_I200;
                DEALLOCATE C_I200;
                SET @P_NRORDREGARQ = @V_NRORDREGARQ;
             FETCH NEXT FROM C_DATA INTO @V_DATA;
         END;
         CLOSE C_DATA;
         DEALLOCATE C_DATA;
   END
   ELSE IF (@P_IDSPED = 'F') BEGIN
      SET @P_NRORDREGARQ = @P_NRINIORDENA;
   END;
END;
GO

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