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

CREATE PROCEDURE SPED_CONTABIL_PLANOCONTA (
   @P_NRSEQARQMAG   VARCHAR(10),
   @P_CDEMPRESA     VARCHAR(2),
   @P_DTREFERENCIA  DATETIME,
   @P_DSNATUREZA    VARCHAR(100),
   @P_IDPLANREFE    VARCHAR(02),
   @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(08);
   DECLARE @V_NRLIVROESCRITU VARCHAR(04);

   /* VARIAVEIS DO CURSOR C_I050 */
   DECLARE @C_I050_DSLINHA         VARCHAR(200);
   DECLARE @C_I050_IDTIPOCONT      CHAR;
   DECLARE @C_I050_CDCONTPLANREFE  VARCHAR(20);
   DECLARE @C_I050_CDCONTCTBL      VARCHAR(20);

BEGIN
   SET @V_NRORDREGARQ = @P_NRINIORDENA;

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

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

   SELECT @V_AUXILIAR = '|I030|' +
          'TERMO DE ABERTURA|'   +
          @V_NRLIVROESCRITU + '|' +
          @P_DSNATUREZA + '|' +
          '#QTDE_TOTAL#|' +
          E.NMRAZSOCEMPR + '|' +
          ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(E.NRREGJUNCOM, ' ', ''),'.',''),',',''),'-',''),'/',''),'') + '|' +
          E.NRINSJUREMPR + '|' +
          SUBSTRING(CONVERT(VARCHAR, E.DTREGJUNCOM, 103), 1, 2) +
          SUBSTRING(CONVERT(VARCHAR, E.DTREGJUNCOM, 103), 4, 2) +
          SUBSTRING(CONVERT(VARCHAR, E.DTREGJUNCOM, 103), 7, 4) +'||' +
          M.NMMUNICIPIO + '|'
     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);

   DECLARE C_I050 CURSOR FOR
      SELECT '|I050|' +
             '0101'   + SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 7, 4) + '|' +
             (SELECT ISNULL(MAX(A.IDNATUCONT),'00')
                FROM CONTCTBL A
               WHERE (A.CDCONTCTBL = (SELECT MAX(B.CDCONTCTBL)
                                        FROM CONTCTBL B
                                       WHERE (C.CDCONTCTBL LIKE B.CDCONTCTBL + '%')
                                         AND (ISNULL(B.IDNATUCONT,'00') <> '00')
                                         AND (B.IDCONTCCUS <> 'R')))
                 AND (A.IDCONTCCUS <> 'R')) + '|' +
             C.IDTIPOCONT + '|' +
             C.CDNVCONTCTBL + '|' +
             C.CDCONTCTBL + '|' +
             (CASE C.CDNVCONTCTBL WHEN '1' THEN ISNULL(C.CDCONTANTE, '') ELSE C.CDCONTANTE END) + '|' +
             C.NMCONTCTBL + '|' AS DSLINHA,
             C.IDTIPOCONT,
             C.CDCONTPLANREFE,
             C.CDCONTCTBL
        FROM CONTCTBL C
       WHERE ((C.IDTIPOCONT = 'S') OR (C.CDCONTPLANREFE IS NOT NULL))
         AND (C.IDCONTCCUS <> 'R')
       ORDER BY C.CDCONTCTBL;
   OPEN C_I050;
   FETCH NEXT FROM C_I050 INTO @C_I050_DSLINHA, @C_I050_IDTIPOCONT, @C_I050_CDCONTPLANREFE,
                               @C_I050_CDCONTCTBL;

   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_I050_DSLINHA, @P_NRSEQARQMAG);

      IF (@C_I050_IDTIPOCONT = 'A') BEGIN
         IF (@C_I050_CDCONTPLANREFE IS NOT NULL) BEGIN
            SET @V_AUXILIAR    = '|I051|' + @P_IDPLANREFE + '||' + @C_I050_CDCONTPLANREFE + '|';
            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);
         END;

         DECLARE C_I052 CURSOR FOR
            SELECT '|I052||' +
                   I.IDGRUPOBALSPED + I.CDITEMSTRU + '|' AS DSLINHA
              FROM TIPODEMO T, ITEMDEMOF I, ITEMAPUCTB IA
             WHERE T.CDTIPODEMO = I.CDTIPODEMO
              AND I.CDTIPODEMO  = IA.CDTIPODEMO
              AND I.CDITEMSTRU  = IA.CDITEMSTRU
              AND IA.IDTIPOORIG = 'S'
              AND @C_I050_CDCONTCTBL LIKE IA.CDORIGDEMO + '%'
              AND T.IDREFESPED = 'B'
              AND I.IDGRUPOBALSPED <> 0
              AND I.IDGRUPOBALSPED IS NOT NULL;
            OPEN C_I052;
            FETCH NEXT FROM C_I052 INTO @V_AUXILIAR;
               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_AUXILIAR, @P_NRSEQARQMAG);
                  FETCH NEXT FROM C_I052 INTO @V_AUXILIAR;
               END;
               CLOSE C_I052;
               DEALLOCATE C_I052;
      END;
      FETCH NEXT FROM C_I050 INTO @C_I050_DSLINHA, @C_I050_IDTIPOCONT, @C_I050_CDCONTPLANREFE,
                                  @C_I050_CDCONTCTBL;
   END;
   CLOSE C_I050;
   DEALLOCATE C_I050;

   SET @P_NRORDREGARQ = @V_NRORDREGARQ;
END;
GO

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