IF EXISTS (SELECT
              *
           FROM
              SYSOBJECTS O, SYSUSERS U
           WHERE
              O.NAME = 'ARQDIGITAL_BLOCOB' AND
              TYPE   = 'P' 		   AND
              U.UID  = O.UID               AND
              U.NAME = USER)

DROP PROCEDURE ARQDIGITAL_BLOCOB  
GO

CREATE PROCEDURE ARQDIGITAL_BLOCOB
   @P_CDIMPOSTO    VARCHAR(02),
   @P_CDEMPRESA    VARCHAR(02),
   @P_CDINSCMUNIC  VARCHAR(20),
   @P_DTINICIAL    DATETIME,
   @P_DTFINAL      DATETIME,
   @P_NRSEQARQMAG  VARCHAR(4000), 
   @P_RETURN       NUMERIC OUTPUT
AS
DECLARE   @V_CONTREGB     NUMERIC;
DECLARE   @V_MOVIMENT     NUMERIC;
DECLARE   @V_DADOSB400    NUMERIC;
DECLARE   @V_VRCONT       NUMERIC;

DECLARE   @C_ARQ_B020     VARCHAR(4000);
DECLARE   @C_ARQ_B025     VARCHAR(4000);
DECLARE   @C_ARQ_B400     VARCHAR(4000);
DECLARE   @C_ARQ_B420     VARCHAR(4000);
DECLARE   @C_ARQ_B430     VARCHAR(4000);
DECLARE   @C_ARQ_B440     VARCHAR(4000);
DECLARE   @C_ARQ_B470     VARCHAR(4000);
DECLARE   @C_ARQ_B001     VARCHAR(4000);
DECLARE   @C20_CDFILIAL   VARCHAR(4000);
DECLARE   @C20_NRLANCTONF VARCHAR(4000);
DECLARE   @C430_CDFISCOPERNF VARCHAR(4000);
DECLARE   @C400_SEL       VARCHAR(4000)

BEGIN
   
   SET @V_CONTREGB = 0;   

   DECLARE C_REGB020 CURSOR FOR (SELECT ('|B020'                                                                            + '|' +
                             '0'                                                                                            + '|' +
                             '1'                                                                                            + '|' +
                             ISNULL(NF.CDFORNECED, '')                                                                      + '|' +
                             ISNULL(NF.CDMODDOCFISC, '')                                                                    + '|' +
                             ISNULL(NF.STATUS, '')                                                                          + '|' +
                             ISNULL(NF.SERIE, '')                                                                           + '|' +
                             ''                                                                                             + '|' +
                             ISNULL(NF.NRNOTAFISC, '')                                                                      + '|' +
                             ISNULL(REPLACE(CONVERT(VARCHAR, NF.DTEMISSAO,103),'/', ''), '')                                + '|' +
                             ISNULL(CONVERT(VARCHAR, TEKNISA.GERA_CFPS(NF.CDFISCOPERNF)), '')                                                             + '|' +
                             ISNULL(NF.NRLANCTBDOC, '')                                                                     + '|' +
                             ISNULL(MU.CDMUNICIBGE, '')                                                                     + '|' +
                             ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRCONTABILNF))),'.', ','),'0,00')+ '|' +
                             '0'                                                                                            + '|' +
                             '0' /* campo 16 */                                                                             + '|' +
                             ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRCONTABILNF))),'.',','),'0,00000')+ '|' +
                             ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRBASEDEDU))), '.', ','),'0,00') + '|' +
                             ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRBASEISSQN))),'.', ','),'0,00') + '|' +
                             ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRBASERETENC))),'.', ','),'0,00')+ '|' +
                             ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRTOMADOR))),  '.', ','),'0,00') + '|' +
                             ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRDESTACADO))),'.', ','),'0,00') + '|' +
                             ''                                                                                             + '|'  ) AS ARQ_B020,
                             NF.CDFILIAL, NF.NRLANCTONF
                     FROM FORNECEDOR CL, ENDEFORN EN,  MUNICIPIO MU,
                          (SELECT SUM(CASE I.CDFISCALNF WHEN '2' THEN I.VRCONTABILNF ELSE '0' END) AS VRISENTRIB,
                                  SUM(VRBASECALCNF) AS VRBASEISSQN,
                                  SUM(VRBASECALCNF) AS VRBASEDEDU,
                                  SUM(VRBASECALCNF) AS VRBASERETENC,
                                  SUM(VRIMPOSTONF)  AS VRTOMADOR,
                                  SUM(VRIMPOSTONF)  AS VRDESTACADO,
                                  SUM(VRCONTABILNF) AS VRCONTABILNF,
                                  I.CDFILIAL, I.NRLANCTONF, N.CDFORNECED, N.NRNOTAFISC, N.DTEMISSAO, N.NRLANCTBDOC,
                                 		  CASE N.IDENTRSAIDOP WHEN 'E' THEN N.CDSERIEENTR ELSE N.CDSERIE END AS SERIE, I.CDFISCOPERNF,
                                  N.CDMODDOCFISC, CASE N.IDSTATUS     WHEN 'C' THEN '02'          ELSE '00'      END AS STATUS
                           FROM MAPAAPURAC M,  NOTAFISCAL N, IMPOSTONF I
                           WHERE M.CDEMPRESA     = @P_CDEMPRESA                    AND
                                 M.CDINSCMUNI    = @P_CDINSCMUNIC                  AND
                                 M.DTINIPERIODO  = @P_DTINICIAL                    AND
                                 M.DTFIMPERIODO  = @P_DTFINAL                      AND
                                 M.CDIMPOSTO     = '30'                            AND
                                 I.NRSEQUMAPAPU  = M.NRSEQUMAPAPU                  AND
                                 I.CDIMPOSTO     = '30'                            AND
                                 N.CDMODDOCFISC  IN('01','03')                     AND
                                 N.CDFILIAL      = I.CDFILIAL                      AND
                                 N.NRLANCTONF    = I.NRLANCTONF                    AND
                                 ISNULL(I.IDIMPOSTONF, '1') <> '3'                 AND
                                 ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E'))
                           GROUP BY I.CDFILIAL, I.NRLANCTONF, N.CDFORNECED, N.NRNOTAFISC, N.DTEMISSAO, N.NRLANCTBDOC,
                                    CASE N.IDENTRSAIDOP WHEN 'E' THEN N.CDSERIEENTR ELSE N.CDSERIE END, I.CDFISCOPERNF,
                                    N.CDMODDOCFISC, CASE N.IDSTATUS     WHEN 'C' THEN  '02'         ELSE '00'      END) NF
                     WHERE NF.CDFORNECED    		= CL.CDFORNECED                         AND
                           EN.CDFORNECED    		= CL.CDFORNECED                         AND
                           ISNULL(EN.IDTPENDEFORN, 'P') = 'P'            		        AND
                           MU.CDPAIS      		= EN.CDPAIS                             AND
                           MU.SGESTADO     		= EN.SGESTADO                           AND
                           MU.CDMUNICIPIO  		= EN.CDMUNICIPIO
                     UNION ALL
                     SELECT ('|B020'                                                                                   + '|' +
                             '1'                                                                                       + '|' +
                             '0'                                                                                       + '|' +
                             ISNULL(NF.CDCLIENTE, '')                                                                  + '|' +
                             ISNULL(NF.CDMODDOCFISC, '')                                                               + '|' +
                             ISNULL(NF.STATUS, '')                                                                     + '|' +
                             ISNULL(NF.SERIE, '')                                                                      + '|' +
                             ''                                                                                        + '|' +
                             ISNULL(NF.NRNOTAFISC, '')                                                                 + '|' +
                             ISNULL(REPLACE(CONVERT(VARCHAR, NF.DTEMISSAO,103),'/', ''), '')                           + '|' +
                             ISNULL(CONVERT(VARCHAR, TEKNISA.GERA_CFPS(NF.CDFISCOPERNF)), '')                                         + '|' +
                             ISNULL(NF.NRLANCTBDOC, '')                                                                + '|' +
                             ISNULL(MU.CDMUNICIBGE, '')                                                                + '|' +
                             ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRCONTABILNF))), '.', ','),'0,00')+ '|' +
                             '0'                                                                                       + '|' +
                             '0'                                                                                       + '|' +
                             CASE CONVERT(VARCHAR, TEKNISA.GERA_CFPS(NF.CDFISCOPERNF)) WHEN '9104' THEN ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRCONTABILNF))), '.', ','),'0,00') ELSE '0' END + '|' + --LINHA 17
                             '0'                                                                                       + '|' + --LINHA 18
                             ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRBASERETENC))), '.', ','),'0,00')+ '|' +
                             ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2), 0))), '.', ','),'0,00')       + '|' +
                             ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),0))), '.', ','),'0,00')        + '|' +
                             ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRTOMADOR))), '.', ','),'0,00')   + '|' +
                             ''                                                                                        + '|'  ) AS ARQ_B020,
                             NF.CDFILIAL, NF.NRLANCTONF
                     FROM CLIENTE CL, ENDECLIE EN,  MUNICIPIO MU,
                          (SELECT SUM(CASE I.CDFISCALNF WHEN '2' THEN I.VRCONTABILNF ELSE 0 END) AS VRISENTRIB,
                                  SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '42', 'B', '')) AS VRBASEISSQN,
                                  SUM(VRBASECALCNF) AS VRBASEDEDU,
                                  SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '31', 'B', '')) AS VRBASERETENC,
                                  SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '42', 'C', '')) AS VRDESTACADO,
                                  SUM(VRCONTABILNF) AS VRCONTABILNF,
                                  SUM(VRIMPOSTONF)  AS VRTOMADOR,
                                  I.CDFILIAL, I.NRLANCTONF, N.CDCLIENTE, N.NRNOTAFISC, N.DTEMISSAO, N.NRLANCTBDOC,
                                  CASE N.IDENTRSAIDOP WHEN 'E' THEN  N.CDSERIEENTR ELSE N.CDSERIE END AS SERIE, I.CDFISCOPERNF,
                                  N.CDMODDOCFISC, CASE N.IDSTATUS WHEN 'C' THEN '02' ELSE '00' END AS STATUS
                           FROM MAPAAPURAC M,  NOTAFISCAL N, IMPOSTONF I
                           WHERE M.CDEMPRESA     = @P_CDEMPRESA         	             AND
                                 M.CDINSCMUNI    = @P_CDINSCMUNIC               	     AND
                                 M.DTINIPERIODO  = @P_DTINICIAL                 	     AND
                                 M.DTFIMPERIODO  = @P_DTFINAL                      	     AND
                                 M.CDIMPOSTO     = '31'                           	     AND
                                 I.NRSEQUMAPAPU  = M.NRSEQUMAPAPU                  	     AND
                                 I.CDIMPOSTO     = '31'                            	     AND
                                 TEKNISA.GERA_CFPS(I.CDFISCOPERNF) IN ('9101', '9103','9104')AND
                                 N.CDMODDOCFISC  IN('01','03')                     AND
                                 N.CDFILIAL      = I.CDFILIAL                      AND
                                 N.NRLANCTONF    = I.NRLANCTONF                    AND
                                 ISNULL(I.IDIMPOSTONF, '1') <> '3'                 AND
                                 ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E'))
                           GROUP BY I.CDFILIAL, I.NRLANCTONF, N.CDCLIENTE, N.NRNOTAFISC, N.DTEMISSAO, N.NRLANCTBDOC,
                                    CASE N.IDENTRSAIDOP WHEN 'E' THEN N.CDSERIEENTR ELSE N.CDSERIE END, I.CDFISCOPERNF,
                                    N.CDMODDOCFISC,
                                    CASE N.IDSTATUS     WHEN 'C' THEN '02'          ELSE '00'      END) NF
                     WHERE NF.CDCLIENTE                = CL.CDCLIENTE                    AND
                           EN.CDCLIENTE                = CL.CDCLIENTE                    AND
                           ISNULL(EN.IDTPENDECLIE,'P') = 'P'            		 AND
                           MU.CDPAIS                   = EN.CDPAIS                       AND
                           MU.SGESTADO                 = EN.SGESTADO                     AND
                           MU.CDMUNICIPIO              = EN.CDMUNICIPIO
                     UNION ALL
                     SELECT ('|B020'                                                                                     + '|' +
                             '1'                                                                                         + '|' +
                             '0'                                                                                         + '|' +
                             ISNULL(NF.CDCLIENTE, '')                                                                                + '|' +
                             ISNULL(NF.CDMODDOCFISC, '')                                                                             + '|' +
                             ISNULL(NF.STATUS, '')                                                                                   + '|' +
                             ISNULL(NF.SERIE, '')                                                                                    + '|' +
                             ''                                                                                          + '|' +
                             ISNULL(NF.NRNOTAFISC, '')                                                                               + '|' +
                             ISNULL(REPLACE(CONVERT(VARCHAR, NF.DTEMISSAO,103),'/', ''), '')                                                            + '|' +
                             ISNULL(CONVERT(VARCHAR, TEKNISA.GERA_CFPS(NF.CDFISCOPERNF)), '')                                                                  + '|' +
                             ISNULL(NF.NRLANCTBDOC, '')                                                                              + '|' +
                             ISNULL(MU.CDMUNICIBGE, '')                                                                              + '|' +
                             ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRCONT))), '.', ','),'0,00')        + '|' +
                             '0'                                                                                         + '|' +
                             '0'                                                                                         + '|' +
                             '0'                                                                                         + '|' +
                             '0'                                                                                         + '|' + --LINHA 18
                             ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRBASEISSQN))), '.', ','),'0,00')   + '|' +
                             ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),0))), '.', ','),'0,00')   + '|' +
                             '0'                                                                                         + '|' +
                             ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRCONTABILNF))), '.', ','),'0,00')  + '|' +
                             ''                                                                                          + '|'  ) AS ARQ_B020,
                             NF.CDFILIAL, NF.NRLANCTONF
                     FROM CLIENTE CL, ENDECLIE EN,  MUNICIPIO MU,
                          (SELECT SUM(CASE I.CDFISCALNF WHEN '2' THEN I.VRCONTABILNF ELSE 0 END) AS VRISENTRIB,
                                  SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '42', 'B', '')) AS VRBASEISSQN,
                                  SUM(VRBASECALCNF) AS VRBASEDEDU,
                                  SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '42', 'B', '')) AS VRBASERETENC,
                                  SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '31', 'C', '')) AS VRTOMADOR,
                                  SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '42', 'C', '')) AS VRDESTACADO,
                                  SUM(VRCONTABILNF) AS VRCONT,
                                  (SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '31', 'I', '')) + SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '42', 'I', ''))) AS VRCONTABILNF,
                                  I.CDFILIAL, I.NRLANCTONF, N.CDCLIENTE, N.NRNOTAFISC, N.DTEMISSAO, N.NRLANCTBDOC,
                                  CASE  N.IDENTRSAIDOP WHEN  'E' THEN  N.CDSERIEENTR ELSE  N.CDSERIE END  AS SERIE, I.CDFISCOPERNF,
                                  N.CDMODDOCFISC, CASE N.IDSTATUS WHEN 'C' THEN '02' ELSE '00' END AS STATUS
                           FROM MAPAAPURAC M,  NOTAFISCAL N, IMPOSTONF I
                           WHERE M.CDEMPRESA     = @P_CDEMPRESA                               AND
                                 M.CDINSCMUNI    = @P_CDINSCMUNIC                             AND
                                 M.DTINIPERIODO  = @P_DTINICIAL                               AND
                                 M.DTFIMPERIODO  = @P_DTFINAL                                 AND
                                 M.CDIMPOSTO     = '42'                                       AND
                                 I.NRSEQUMAPAPU  = M.NRSEQUMAPAPU                             AND
                                 I.CDIMPOSTO     = '42'                                       AND
                                 TEKNISA.GERA_CFPS(I.CDFISCOPERNF) IN ('9101', '9103','9104') AND
                                 N.CDMODDOCFISC  IN('01','03')                      	      AND
                                 N.CDFILIAL      = I.CDFILIAL                       	      AND
                                 N.NRLANCTONF    = I.NRLANCTONF                               AND
                                 ISNULL(I.IDIMPOSTONF, '1') <> '3'                  	      AND
                                 ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E'))
                           GROUP BY I.CDFILIAL, I.NRLANCTONF, N.CDCLIENTE, N.NRNOTAFISC, N.DTEMISSAO, N.NRLANCTBDOC,
                                    CASE N.IDENTRSAIDOP WHEN 'E' THEN  N.CDSERIEENTR ELSE N.CDSERIE END, I.CDFISCOPERNF,
                                    N.CDMODDOCFISC,
                                    CASE N.IDSTATUS  WHEN 'C' THEN '02' ELSE '00'END ) NF
                     WHERE NF.CDCLIENTE    = CL.CDCLIENTE      AND
                           EN.CDCLIENTE    = CL.CDCLIENTE      AND
                           ISNULL(EN.IDTPENDECLIE, 'P') = 'P'  AND
                           MU.CDPAIS       = EN.CDPAIS         AND
                           MU.SGESTADO     = EN.SGESTADO       AND
                           MU.CDMUNICIPIO  = EN.CDMUNICIPIO ) 

      OPEN C_REGB020;

      FETCH NEXT FROM C_REGB020 INTO @C_ARQ_B020, @C20_CDFILIAL, @C20_NRLANCTONF;
        WHILE (@@FETCH_STATUS = 0) BEGIN
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('14'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGB), 8), @C_ARQ_B020, @P_NRSEQARQMAG);
            SET @V_CONTREGB = ISNULL(@V_CONTREGB,0) +1;            

      FETCH NEXT FROM C_REGB020 INTO @C_ARQ_B020, @C20_CDFILIAL, @C20_NRLANCTONF;
    
   END;
   CLOSE C_REGB020;
   DEALLOCATE C_REGB020;

   SELECT @V_VRCONT = SUM(I.VRIMPOSTONF)
   FROM IMPOSTONF I, NOTAFISCAL N, MAPAAPURAC M
   WHERE M.CDEMPRESA    = @P_CDEMPRESA           AND
         M.CDINSCMUNI   = @P_CDINSCMUNIC         AND
         M.DTINIPERIODO = @P_DTINICIAL           AND
         M.DTFIMPERIODO = @P_DTFINAL             AND
         M.CDIMPOSTO    IN ('30', '31', '42')    AND
         I.CDIMPOSTO    IN ('30', '31', '42')    AND
         ISNULL(I.IDIMPOSTONF, '1') <> '3'       AND
         I.NRSEQUMAPAPU = M.NRSEQUMAPAPU         AND
         N.CDFILIAL     = I.CDFILIAL             AND
         N.NRLANCTONF   = I.NRLANCTONF           AND
         I.VRIMPOSTONF  > 0;
   IF (@V_VRCONT IS NULL) BEGIN
      SET @V_DADOSB400 = 1;
   END
   ELSE BEGIN
      SET @V_DADOSB400 = 0;
   END;

   DECLARE C_REGB400 CURSOR FOR (SELECT ( '|B400'           			 + '|' +
             				  ISNULL(REPLACE(CONVERT(VARCHAR, @P_DTINICIAL,103),'/', ''), '') + '|' +
                                          ISNULL(REPLACE(CONVERT(VARCHAR, @P_DTFINAL,  103),'/', ''), '') + '|' +
                                          ISNULL(CONVERT(VARCHAR, @V_DADOSB400), '')  + '|' )AS ARQ_B400 
                                   FROM  PARAMGERAL ) 

   OPEN C_REGB400;

   FETCH NEXT FROM C_REGB400 INTO @C_ARQ_B400;

   WHILE (@@FETCH_STATUS = 0) BEGIN

      INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('16'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGB), 8), @C_ARQ_B400, @P_NRSEQARQMAG);
     SET @V_CONTREGB = ISNULL(@V_CONTREGB,0) +1;

      

      DECLARE C_REGB420 CURSOR FOR (SELECT ('|B420'+ '|' +
                                ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRCONT_1))), '.', ','),'0,00000')  + '|' + -- IMP 31+42
                                ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRCONT_2))), '.', ','),'0,00000')  + '|' +
                                ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRALIQUOTA))), '.', ','),'0,00000')+ '|' +
                                ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRCONT_4))), '.', ','),'0,00000')  + '|' +
                                ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRCONT_5))), '.', ','),'0,00000')  + '|' +
                                ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRCONT_6))), '.', ','),'0,00000')  + '|' +
                                ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRCONT_7))), '.', ','),'0,00000')  + '|'  )AS ARQ_B420
                        FROM(SELECT SUM(I.VRCONTABILNF) AS VRCONT_1,
                                    SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '31', 'B', '')) + SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '42', 'B', '')) AS VRCONT_2,
                                    SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '31', 'A', '')) + SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '42', 'A', '')) AS VRCONT_3,
                                    SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '31', 'B', '')) AS VRCONT_4,
                                    SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '31', 'I', '')) AS VRCONT_5,
                                    0 AS VRCONT_6,
                                    SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '31', 'I', '')) + SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '42', 'I', '')) AS VRCONT_7,
                                    '5,00' AS VRALIQUOTA
                             FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I
                             WHERE M.CDEMPRESA       = @P_CDEMPRESA            AND
                                   M.CDINSCMUNI      = @P_CDINSCMUNIC          AND
                                   M.DTINIPERIODO    = @P_DTINICIAL            AND
                                   M.DTFIMPERIODO    = @P_DTFINAL              AND
                                   M.CDIMPOSTO       IN('42','31')             AND
                                   I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU          AND
                                   I.CDIMPOSTO       IN('42','31')             AND
                                   N.CDMODDOCFISC    IN('01','03')             AND
                                   ISNULL(I.IDIMPOSTONF, '1') <> '3'           AND
                                   TEKNISA.GERA_CFPS(I.CDFISCOPERNF) IN ('9101', '9103')  AND
                                   N.CDFILIAL        = I.CDFILIAL                         AND
                                   N.NRLANCTONF      = I.NRLANCTONF                       AND
                                   ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E'))
                             GROUP BY I.VRALIQNF
                             UNION ALL
                     		 SELECT SUM(I.VRCONTABILNF) AS VRCONT_1,
                                    SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '31', 'B', '')) + SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '42', 'B', '')) AS VRCONT_2,
                                    SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '31', 'A', '')) + SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '42', 'A', '')) AS VRCONT_3,
                                    SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '31', 'B', '')) AS VRCONT_4,
                                    SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '31', 'I', '')) AS VRCONT_5,
                                    SUM(I.VRCONTABILNF) AS VRCONT_6,
                                    SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '31', 'I', '')) + SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '42', 'I', '')) AS VRCONT_7,
                                    '0,00' AS VRALIQUOTA
                             FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I
                             WHERE M.CDEMPRESA       = @P_CDEMPRESA            AND
                                   M.CDINSCMUNI      = @P_CDINSCMUNIC          AND
                                   M.DTINIPERIODO    = @P_DTINICIAL            AND
                                   M.DTFIMPERIODO    = @P_DTFINAL              AND
                                   M.CDIMPOSTO       IN('42','31')             AND
                                   I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU          AND
                                   I.CDIMPOSTO       IN('42','31')             AND
                                   N.CDMODDOCFISC    IN('01','03')             AND
                                   ISNULL(I.IDIMPOSTONF, '1') <> '3'           AND
                                   TEKNISA.GERA_CFPS(I.CDFISCOPERNF) ='9104'   AND
                                   N.CDFILIAL        = I.CDFILIAL              AND
                                   N.NRLANCTONF      = I.NRLANCTONF            AND
                                   ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E'))
                             GROUP BY I.VRALIQNF) NF )

      OPEN C_REGB420;
      FETCH NEXT FROM C_REGB420 INTO @C_ARQ_B420;

      WHILE (@@FETCH_STATUS = 0) BEGIN

       INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
        ('16'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGB), 8), @C_ARQ_B420, @P_NRSEQARQMAG);
       SET @V_CONTREGB = ISNULL(@V_CONTREGB,0) +1;

       FETCH NEXT FROM C_REGB420 INTO @C_ARQ_B420;

      END;
      CLOSE C_REGB420;
      DEALLOCATE C_REGB420;
   
      DECLARE C_REGB430 CURSOR FOR (SELECT ('|B430'                                                                                         + '|' +
                                ISNULL(CONVERT(VARCHAR, NF.CDFISCOPERNF), '')                                                                                 + '|' +
                                ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),SUM(NF.VRCONT_1)))), '.', ','),'0,00000')  + '|' +
                                ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),SUM(NF.VRCONT_2)))), '.', ','),'0,00000')  + '|' +
                                ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),SUM(NF.VRCONT_3)))), '.', ','),'0,00000')  + '|' +
                                ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),SUM(NF.VRCONT_4)))), '.', ','),'0,00000')  + '|' +
                                ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),SUM(NF.VRCONT_5)))), '.', ','),'0,00000')  + '|' +
                                ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),SUM(NF.VRCONT_6)))), '.', ','),'0,00000')  + '|' +
                                ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),SUM(NF.VRCONT_7)))), '.', ','),'0,00000')  + '|' +
                                ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),SUM(NF.VRCONT_8)))), '.', ','),'0,00000')  + '|' +
                                ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),SUM(NF.VRCONT_9)))), '.', ','),'0,00000')  + '|'  ) AS ARQ_B430,
                                NF.CDFISCOPERNF
                        FROM(SELECT TEKNISA.GERA_CFPS(I.CDFISCOPERNF) AS CDFISCOPERNF,
                                    SUM(I.VRCONTABILNF) AS VRCONT_1,
                                    SUM(I.VRBASECALCNF) AS VRCONT_2,
                                    0  AS VRCONT_3,
                                    0  AS VRCONT_4,
                                    SUM(I.VRCONTABILNF) AS VRCONT_5,
                                    0  AS VRCONT_6,
                                    SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '42', 'B', '')) AS VRCONT_7,
                                    SUM(I.VRBASECALCNF) AS VRCONT_8,
                                    SUM(I.VRIMPOSTONF) AS VRCONT_9
                             FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I
                             WHERE M.CDEMPRESA       = @P_CDEMPRESA            AND
                                   M.CDINSCMUNI      = @P_CDINSCMUNIC          AND
                                   M.DTINIPERIODO    = @P_DTINICIAL            AND
                                   M.DTFIMPERIODO    = @P_DTFINAL              AND
                                   M.CDIMPOSTO       = '30'                    AND
                                   I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU          AND
                                   I.CDIMPOSTO       = '30'                    AND
                                   TEKNISA.GERA_CFPS(I.CDFISCOPERNF) IN ('9201','9203') AND
                                   N.CDMODDOCFISC  IN('01','03')               AND
                                   ISNULL(I.IDIMPOSTONF, '1') <> '3'           AND
                                   N.CDFILIAL        = I.CDFILIAL              AND
                                   N.NRLANCTONF      = I.NRLANCTONF            AND
                                   ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E'))
                             GROUP BY TEKNISA.GERA_CFPS(I.CDFISCOPERNF)
                             UNION ALL
                             SELECT TEKNISA.GERA_CFPS(I.CDFISCOPERNF) AS CDFISCOPERNF,
                                    SUM(I.VRCONTABILNF) AS VRCONT_1,
                                    SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '31', 'B', '')) AS VRCONT_2,
                                    0 AS VRCONT_3,
                                    0 AS VRCONT_4,
                                    CASE TEKNISA.GERA_CFPS(I.CDFISCOPERNF) WHEN '9104' THEN SUM(I.VRCONTABILNF) ELSE 0 END AS VRCONT_5,
                                    0  AS VRCONT_6,
                                    SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '42', 'B', '')) AS VRCONT_7,
                                    0 AS VRCONT_8,
                                    SUM(I.VRIMPOSTONF) AS VRCONT_9
                             FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I
                             WHERE M.CDEMPRESA       = @P_CDEMPRESA            AND
                                   M.CDINSCMUNI      = @P_CDINSCMUNIC          AND
                                   M.DTINIPERIODO    = @P_DTINICIAL            AND
                                   M.DTFIMPERIODO    = @P_DTFINAL              AND
                                   M.CDIMPOSTO       = '31'                    AND
                                   I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU          AND
                                   I.CDIMPOSTO       = '31'                    AND
                                   N.CDMODDOCFISC  IN('01','03')               AND
                                   ISNULL(I.IDIMPOSTONF, '1') <> '3'           AND
                                   N.CDFILIAL        = I.CDFILIAL              AND
                                   N.NRLANCTONF      = I.NRLANCTONF            AND
                                   ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E'))
                             GROUP BY TEKNISA.GERA_CFPS(I.CDFISCOPERNF)
                             UNION ALL
                             SELECT TEKNISA.GERA_CFPS(I.CDFISCOPERNF) AS CDFISCOPERNF,
                                    SUM(I.VRCONTABILNF)		      AS VRCONT_1,
                                    SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '42', 'B', '')) AS VRCONT_2,
                                    0 AS VRCONT_3,
                                    0 AS VRCONT_4,
                                    0 AS VRCONT_5,
                                    0 AS VRCONT_6,
                                    0 AS VRCONT_7,
                                    0 AS VRCONT_8,
                                    SUM(TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '31', 'I', '') + TEKNISA.VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, '42', 'I', ''))  AS VRCONT_9
                             FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I
                             WHERE M.CDEMPRESA       = @P_CDEMPRESA            AND
                                   M.CDINSCMUNI      = @P_CDINSCMUNIC          AND
                                   M.DTINIPERIODO    = @P_DTINICIAL            AND
                                   M.DTFIMPERIODO    = @P_DTFINAL              AND
                                   M.CDIMPOSTO       = '42'                    AND
                                   I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU          AND
                                   I.CDIMPOSTO       = '42'                    AND
                                   N.CDMODDOCFISC    IN('01','03')             AND
                                   ISNULL(I.IDIMPOSTONF, '1') <> '3'           AND
                                   N.CDFILIAL        = I.CDFILIAL              AND
                                   N.NRLANCTONF      = I.NRLANCTONF            AND
                                   ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E'))
                             GROUP BY TEKNISA.GERA_CFPS(I.CDFISCOPERNF)  ) NF
                        GROUP BY NF.CDFISCOPERNF) 
      OPEN C_REGB430;
      FETCH NEXT FROM C_REGB430 INTO @C_ARQ_B430, @C430_CDFISCOPERNF;

      WHILE (@@FETCH_STATUS = 0) BEGIN

       INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
        ('16'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGB), 8), @C_ARQ_B430, @P_NRSEQARQMAG);
       SET @V_CONTREGB = ISNULL(@V_CONTREGB,0) +1;

       FETCH NEXT FROM C_REGB430 INTO @C_ARQ_B430, @C430_CDFISCOPERNF;

      END;
      CLOSE C_REGB430;
      DEALLOCATE C_REGB430;

      
      FETCH NEXT FROM C_REGB400 INTO @C_ARQ_B400;
 
     END;
    CLOSE C_REGB400;
    DEALLOCATE C_REGB400;

   IF @V_CONTREGB <> 0 BEGIN
      SET @V_MOVIMENT = 0;
   END
   ELSE BEGIN
      SET @V_MOVIMENT = 1;
   END;

   /* ABERTURA DO REGISTRO B */
   SET @V_CONTREGB = ISNULL(@V_CONTREGB,0) +1;
   DECLARE C_REGB001 CURSOR FOR (SELECT ('|B001'+ '|' +
                                         ISNULL(CONVERT(VARCHAR, @V_MOVIMENT), '')       + '|' +
                              		 ISNULL(CONVERT(VARCHAR, M.CDMUNICIBGE), '')     + '|'  ) AS ARQ_B001
                     FROM INSCRESTAD E, MUNICIPIO M
                     WHERE E.CDPAIS      = M.CDPAIS       AND
                           E.SGESTADO    = M.SGESTADO     AND
                           E.CDMUNICIPIO = M.CDMUNICIPIO  AND
                           E.CDEMPRESA   = @P_CDEMPRESA   AND
                           E.CDINSCESTA  = @P_CDINSCMUNIC) 

   OPEN C_REGB001;
   FETCH NEXT FROM C_REGB001 INTO @C_ARQ_B001;
   WHILE (@@FETCH_STATUS = 0) BEGIN
       INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
           ('13'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGB), 8), @C_ARQ_B001, @P_NRSEQARQMAG);      
       FETCH NEXT FROM C_REGB001 INTO @C_ARQ_B001;
   END;
   CLOSE C_REGB001;
   DEALLOCATE C_REGB001;
 
      /* encerramento do bloco B */
   SET @V_CONTREGB = ISNULL(@V_CONTREGB,0) +1;
   INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
        ('17'+RIGHT(REPLICATE('0', 8)+ ('2'), 8),'|B990' + '|' + CONVERT(VARCHAR, @V_CONTREGB) + '|', @P_NRSEQARQMAG);

   SET @P_RETURN = @V_CONTREGB;   
END
GO

-- VERSAO --
EXEC ATUVEROBJ 'ARQDIGITAL_BLOCOB','5.05.008';
GO

