IF EXISTS(SELECT * FROM sysobjects O, sysusers U
          WHERE
            o.name = 'ARQDIGITAL_BLOCOC' and
            type = 'P'                   and
            u.uid = o.uid                and
            u.name = USER)
  DROP PROCEDURE ARQDIGITAL_BLOCOC
  GO

CREATE PROCEDURE ARQDIGITAL_BLOCOC
   @P_CDIMPOSTO    VARCHAR(02),
   @P_CDEMPRESA    VARCHAR(02),
   @P_CDINSCESTA   VARCHAR(14),
   @P_DTINICIAL    DATETIME,
   @P_DTFINAL      DATETIME,
   @P_NRSEQARQMAG  VARCHAR(20),
   @P_RETURN       NUMERIC OUTPUT
AS
DECLARE   @V_CONTREGC     NUMERIC;
DECLARE   @V_MOVIMENT     NUMERIC;
DECLARE   @V_SEQUENCIAL   VARCHAR(100);
DECLARE   @V_IDFRETE      VARCHAR(1);
DECLARE   @V_IDSEGUR      VARCHAR(1);
DECLARE   @V_IDOUTROS     VARCHAR(1);
DECLARE   @V_CDIMPOSTRIB  VARCHAR(02);
DECLARE   @V_DADOS_INF    VARCHAR(4000);

DECLARE   @V_ARQ_C020          VARCHAR(4000);
DECLARE   @V_ARQ_C250          VARCHAR(4000);
DECLARE   @V_ARQ_C300          VARCHAR(4000);
DECLARE   @V_ARQ_C550          VARCHAR(4000);
DECLARE   @V_ARQ_C555          VARCHAR(4000);
DECLARE   @V_C550_CDFILIAL     VARCHAR(4000); 
DECLARE   @V_C550_NRLANCTONF   VARCHAR(4000);
DECLARE   @V_C020CDFILIAL      VARCHAR(4000);
DECLARE   @V_C020NRLANCTONF    VARCHAR(4000);
DECLARE   @V_C020NRINSJURTRAN  VARCHAR(4000); 
DECLARE   @V_C020IDTPIJURTRAN  VARCHAR(4000); 
DECLARE   @V_C020VRPBRUTOVOLU  VARCHAR(4000); 
DECLARE   @V_C020VRPLIQUVOLU   VARCHAR(4000);
DECLARE   @V_C020NRPLACAVEIC   VARCHAR(4000);
DECLARE   @V_C020DSQTDEVOLU    VARCHAR(4000);
DECLARE   @V_C020DSESPECVOLU   VARCHAR(4000);
DECLARE   @V_C020DSMARCAVOLU   VARCHAR(4000);
DECLARE   @V_C020DSNUMEVOLU    VARCHAR(4000);
DECLARE   @V_C020CDFISCOPERNF  VARCHAR(4000);
    
BEGIN
      SET @V_CONTREGC = 0;

      SELECT @V_IDFRETE = EN.IDENCARGO
      FROM RELACENCARGO E, ENCARGO EN
      WHERE E.CDENCARGO = EN.CDENCARGO AND
            E.IDENCARGODOS LIKE '%FRET%';

      SELECT @V_IDSEGUR = EN.IDENCARGO
      FROM RELACENCARGO E, ENCARGO EN
      WHERE E.CDENCARGO = EN.CDENCARGO AND
            E.IDENCARGODOS LIKE '%SEGU%';

      SELECT @V_IDOUTROS = EN.IDENCARGO
      FROM RELACENCARGO E, ENCARGO EN
      WHERE E.CDENCARGO = EN.CDENCARGO AND
            E.IDENCARGODOS LIKE '%OUTR%';

      SELECT @V_CDIMPOSTRIB = CDIMPOSTRIB
      FROM IMPOSTO
      WHERE CDIMPOSTO = '00';

      DECLARE C_REGC020 CURSOR FOR (SELECT ('|C020'							     						        + '|' +
                                            ISNULL(NF.CAMPO2, '')                                                                      						        + '|' +
                                            ISNULL(NF.CAMPO3, '')                                                                      							+ '|' +
                                            ISNULL(CASE NF.IDORIGDESTNF WHEN 'F' THEN NF.CDFORNECED  ELSE CASE NF.IDORIGDESTNF WHEN 'C' THEN NF.CDCLIENTE   ELSE CASE NF.IDENTRSAIDOP WHEN 'E' THEN NF.CDFILIEMIT  ELSE NF.CDFILIRECE END END END, '')  + '|' +
                                            ISNULL(NF.CDMODDOCFISC, '')                                                                   					 	        + '|' +
                                            ISNULL(CASE NF.IDSTATUS     WHEN 'C' THEN '02'           ELSE '00'       END, '')           						        + '|' +
                                            ISNULL(CASE NF.IDENTRSAIDOP WHEN 'E' THEN NF.CDSERIEENTR ELSE NF.CDSERIE END, '')              						+ '|' +
                                            ISNULL(NF.NRNOTAFISC, '')                                                                    						        + '|' +
                                            ISNULL(REPLACE(CONVERT(VARCHAR, NF.DTEMISSAO,103), '/',''), '')                                          				        + '|' +
                                            ISNULL(REPLACE(CONVERT(VARCHAR, NF.DTENTRSAID,103), '/',''), '')                                              			                + '|' +
                                            ISNULL(NF.CDFISCOPERNF, '')                                                                  						        + '|' +
                                            ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRCONTABILNF))), '.', ','), '')						        + '|' +
                                            ISNULL(CASE PGTO.DTORIGVENREC WHEN NF.DTEMISSAO THEN  '0' ELSE  '1' END, '')                						        + '|' +
                                            ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),TEKNISA.VALOR_ENCARGO(NF.CDFILIAL, NF.NRLANCTONF, 'D', '')))),'.', ','), '')         + '|' +
                                            ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2), NF.VRPRODUTOS))), '.', ','), '')                                                    + '|' +
                                            CASE(ISNULL(NF.IDPGTOFRETE,'1') WHEN '0' THEN '1' ELSE CASE(ISNULL(NF.IDPGTOFRETE,'1') WHEN '1' THEN '0' ELSE CASE(ISNULL(NF.IDPGTOFRETE,'1') WHEN '2' THEN '1' ELSE CASE(ISNULL(NF.IDPGTOFRETE,'1') WHEN '9' THEN '1' END)  + '|' +
                                            ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),TEKNISA.VALOR_ENCARGO(NF.CDFILIAL, NF.NRLANCTONF, @V_IDFRETE, '')))), '.', ','), '') + '|' +
                                            ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),TEKNISA.VALOR_ENCARGO(NF.CDFILIAL, NF.NRLANCTONF, @V_IDSEGUR, '')))), '.', ','), '') + '|' +
                                            ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),TEKNISA.VALOR_ENCARGO(NF.CDFILIAL, NF.NRLANCTONF, @V_IDOUTROS,'')))), '.', ','), '') + '|' +
                                            ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRBASECALCNF))), '.', ','), '') + '|' +
                                            ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRIMPOSTONF))), '.', ','), '') + '|' +
                                            ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),NF.VRBASECALCNF))), '.', ','), '') + '|' +
                                            ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),TEKNISA.VALOR_IMPOSTO_NF(NF.CDFILIAL, NF.NRLANCTONF, @V_CDIMPOSTRIB, 'I', '')))), '.', ','), '') + '|' +
                                            ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),TEKNISA.VALOR_IMPOSTO_NF(NF.CDFILIAL, NF.NRLANCTONF, '10', 'I', '')))), '.', ','), '') + '|' +
                                            ''                                                      + '|'  )  AS ARQ_C020,
                                            NF.CDFILIAL, NF.NRLANCTONF, NF.NRINSJURTRAN, NF.IDTPIJURTRAN, NF.VRPBRUTOVOLU, NF.VRPLIQUVOLU,
                                            NF.NRPLACAVEIC, NF.DSQTDEVOLU, NF.DSESPECVOLU, NF.DSMARCAVOLU, NF.DSNUMEVOLU, NF.CDFISCOPERNF
                                    FROM (SELECT '1' AS CAMPO2,
						 '0' AS CAMPO3,
                                                 I.CDFILIAL, 
                                                 I.NRLANCTONF,
						 N.IDENTRSAIDOP,
						 N.NRNOTAFISC,  
						 N.CDSERIE,
						 N.CDSERIEENTR, 
                                                 N.NRINSJURNF, 
						 N.IDSTATUS, 
						 N.DTENTRSAID,   
						 N.VRNOTAFISC,   
						 N.VRPRODUTOS, 
						 N.IDPGTOFRETE, 
                                                 N.NRPLACAVEIC, 
						 N.DSQTDEVOLU, 
						 N.DSESPECVOLU,  
						 N.DSMARCAVOLU,  
						 N.DSNUMEVOLU, 
                                                 N.CDFILIEMIT, 
						 N.CDFILIRECE,
						 N.DTEMISSAO, 
						 MAX(I.CDFISCOPERNF) AS CDFISCOPERNF,  
						 N.CDMODDOCFISC,  
						 N.CDCLIENTE, 
						 N.CDFORNECED, 
						 N.IDORIGDESTNF,
                                                 SUM(I.VRCONTABILNF) AS VRCONTABILNF,  
						 N.NRINSJURTRAN,  
						 N.IDTPIJURTRAN,  
						 N.VRPBRUTOVOLU,  
						 N.VRPLIQUVOLU,
                                                 SUM(CASE I.CDFISCALNF WHEN '1' THEN I.VRBASECALCNF ELSE 0 END) AS VRBASECALCNF,
                                                 SUM(CASE I.CDFISCALNF WHEN '1' THEN I.VRIMPOSTONF  ELSE 0 END) AS VRIMPOSTONF,
                                                 SUM(CASE I.CDFISCALNF WHEN '3' THEN I.VRCONTABILNF ELSE 0 END) AS VROUTRCRED,
                                                 SUM(CASE I.CDFISCALNF WHEN '2' THEN I.VRCONTABILNF ELSE 0 END) AS VRISENTRIB
                                          FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I
                                          WHERE M.CDEMPRESA       = @P_CDEMPRESA              AND
                                                M.CDINSCESTA      = @P_CDINSCESTA             AND
                                                M.DTINIPERIODO    = @P_DTINICIAL              AND
                                                M.DTFIMPERIODO    = @P_DTFINAL                AND
                                                M.CDIMPOSTO       = @P_CDIMPOSTO              AND
                                                I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU            AND
                                                I.CDIMPOSTO       = @P_CDIMPOSTO              AND
                                                (ISNULL(N.IDSISTEMA, 'DFS')  <> 'REC'         AND
                                                 ISNULL(N.IDSISTEMA, 'DFS')  <> 'PAG'         AND
                                                 ISNULL(N.IDSISTEMA, 'DFS')  <> 'CMP'         AND
                                                 ISNULL(N.IDSISTEMA, 'DFS')  <> 'SUP'         AND
                                                 ISNULL(N.IDSISTEMA, 'DFS')  <> 'FIS')        AND
                                                N.IDENTRSAIDOP    = 'S'                       AND
                                                ISNULL(I.IDIMPOSTONF, '1') <> '3'             AND
                                                N.CDFILIAL        = I.CDFILIAL                AND
                                                N.NRLANCTONF      = I.NRLANCTONF              AND
                                                ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E'))    AND
                                                N.CDMODDOCFISC IN ('01', '04', '55')
                                          GROUP BY I.CDFILIAL,  I.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR,
                                                 N.NRINSJURNF,  N.IDSTATUS, N.DTENTRSAID, N.VRNOTAFISC, N.VRPRODUTOS, N.IDPGTOFRETE,
                                                 N.DTEMISSAO,   N.CDMODDOCFISC, N.CDCLIENTE, N.CDFORNECED, N.IDORIGDESTNF,
                                                 N.CDFILIEMIT,  N.CDFILIRECE, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,
                                                 N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU
                                          UNION ALL
                                          SELECT '0' AS CAMPO2, '1' AS CAMPO3,
                                                 I.CDFILIAL, I.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR,
                                                 N.NRINSJURNF, N.IDSTATUS, N.DTENTRSAID, N.VRNOTAFISC, N.VRPRODUTOS, N.IDPGTOFRETE,
                                                 N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU,
                                                 N.CDFILIEMIT, N.CDFILIRECE, N.DTEMISSAO, MAX(I.CDFISCOPERNF) AS CDFISCOPERNF, N.CDMODDOCFISC, N.CDCLIENTE, N.CDFORNECED, N.IDORIGDESTNF,
                                                 SUM(I.VRCONTABILNF) AS VRCONTABILNF, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,
                                                 SUM(CASE I.CDFISCALNF WHEN '1' THEN I.VRBASECALCNF ELSE 0 END) AS VRBASECALCNF,
                                                 SUM(CASE I.CDFISCALNF WHEN '1' THEN I.VRIMPOSTONF  ELSE 0 END) AS VRIMPOSTONF,
                                                 SUM(CASE I.CDFISCALNF WHEN '3' THEN I.VRCONTABILNF ELSE 0 END) AS VROUTRCRED,
                                                 SUM(CASE I.CDFISCALNF WHEN '2' THEN I.VRCONTABILNF ELSE 0 END) AS VRISENTRIB
                                          FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I
                                          WHERE M.CDEMPRESA       = @P_CDEMPRESA              AND
                                                M.CDINSCESTA      = @P_CDINSCESTA             AND
                                                M.DTINIPERIODO    = @P_DTINICIAL              AND
                                                M.DTFIMPERIODO    = @P_DTFINAL                AND
                                                M.CDIMPOSTO       = @P_CDIMPOSTO              AND
                                                I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU            AND
                                                I.CDIMPOSTO       = @P_CDIMPOSTO              AND
                                                (ISNULL(N.IDSISTEMA, 'DFS')  <> 'REC'         AND
                                                 ISNULL(N.IDSISTEMA, 'DFS')  <> 'PAG'         AND
                                                 ISNULL(N.IDSISTEMA, 'DFS')  <> 'CMP'         AND
                                                 ISNULL(N.IDSISTEMA, 'DFS')  <> 'SUP'         AND
                                                 ISNULL(N.IDSISTEMA, 'DFS')  <> 'FIS' )       AND
                                                N.IDENTRSAIDOP    = 'E'                       AND
                                                ISNULL(I.IDIMPOSTONF, '1')   <> '3'           AND
                                                N.CDFILIAL        = I.CDFILIAL                AND
                                                N.NRLANCTONF      = I.NRLANCTONF              AND
                                                ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E'))    AND
                                                N.CDMODDOCFISC IN ('01', '04', '55')
                                          GROUP BY I.CDFILIAL, I.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR,
                                                 N.NRINSJURNF, N.IDSTATUS, N.DTENTRSAID, N.VRNOTAFISC, N.VRPRODUTOS, N.IDPGTOFRETE,
                                                 N.DTEMISSAO, N.CDMODDOCFISC, N.CDCLIENTE, N.CDFORNECED, N.IDORIGDESTNF,
                                                 N.CDFILIEMIT, N.CDFILIRECE, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,
                                                 N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU) NF,
                                         (SELECT MAX(C.DTORIGVENREC) DTORIGVENREC,  N.CDFILIAL, N.NRLANCTONF
                                          FROM NOTAFISCAL N, CONTARECEBER C
                                          WHERE N.CDFILIAL   = C.CDFILIAL AND
                                                N.NRLANCTONF = C.NRLANCTONF
                                          GROUP BY N.CDFILIAL, N.NRLANCTONF) PGTO
                                    WHERE NF.CDFILIAL     = PGTO.CDFILIAL AND
                                          NF.NRLANCTONF   = PGTO.NRLANCTONF      )
      OPEN C_REGC020;

      FETCH NEXT FROM C_REGC020 INTO @V_ARQ_C020, @V_C020CDFILIAL,  @V_C020NRLANCTONF, @V_C020NRINSJURTRAN,
                                     @V_C020IDTPIJURTRAN, @V_C020VRPBRUTOVOLU, @V_C020VRPLIQUVOLU, @V_C020NRPLACAVEIC,
	                 	     @V_C020DSQTDEVOLU, @V_C020DSESPECVOLU, @V_C020DSMARCAVOLU, @V_C020DSNUMEVOLU,
				     @V_C020CDFISCOPERNF;

      WHILE (@@FETCH_STATUS = 0) BEGIN
       
      	  INSERT INTO ARQMAGNETICO
          (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
          ('19'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGC), 8), @V_ARQ_C020, @P_NRSEQARQMAG);
          SET @V_CONTREGC = ISNULL(@V_CONTREGC,0) + 1;     
    
      DECLARE C_REGC250 CURSOR FOR (SELECT ('|C250'              + '|' +  
                                            ISNULL(T.CDFORNECED, '')         + '|' +
                                            ISNULL(@V_C020NRPLACAVEIC, '')   + '|' +
                                            ISNULL(T.SGESTADO, '')          + '|' +
                                            ISNULL(@V_C020DSQTDEVOLU, '')    + '|' +
                                            ISNULL(@V_C020DSESPECVOLU, '')   + '|' +
                                            ISNULL(@V_C020DSMARCAVOLU, '')   + '|' +
                                            ISNULL(@V_C020DSNUMEVOLU, '')    + '|' +
                                            ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,3), @V_C020VRPBRUTOVOLU))), '.', ','), '') + '|' +
                                            ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,3), @V_C020VRPLIQUVOLU ))), '.', ','), '') + '|'  ) AS ARQ_C250
                                    FROM  TRANSPORT T
                                    WHERE T.IDTPIJURTRAN = @V_C020IDTPIJURTRAN AND
                                          T.NRINSJURTRAN = @V_C020NRINSJURTRAN   )
      OPEN C_REGC250;

      FETCH NEXT FROM C_REGC250 INTO @V_ARQ_C250;

      WHILE (@@FETCH_STATUS = 0) BEGIN 
       
      	  INSERT INTO ARQMAGNETICO
          (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
          ('19'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGC), 8), @V_ARQ_C250, @P_NRSEQARQMAG);
          SET @V_CONTREGC = ISNULL(@V_CONTREGC,0) + 1;

          FETCH NEXT FROM C_REGC250 INTO @V_ARQ_C250;

      END;
      CLOSE C_REGC250;
      DEALLOCATE C_REGC250;

      SET @V_SEQUENCIAL = RIGHT(REPLICATE('0',3)+RTRIM(CONVERT(CHAR(20),CONVERT(DOUBLE PRECISION,('1')))),3);
    
      SELECT @V_DADOS_INF = SUM(CONVERT(DOUBLE PRECISION, NRLANCTONF)) 
      FROM ITEMPROIMPOS  
      WHERE CDFILIAL   = @V_C020CDFILIAL  AND 
            NRLANCTONF = @V_C020NRLANCTONF;     
  
      IF (@V_DADOS_INF IS NOT NULL) BEGIN
         DECLARE C_REGC300 CURSOR FOR (SELECT (ISNULL(PR.CDARVPROD, '')                                                                + '|' +
                                               ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,3),IT.VRUNITITEM))), '.', ','), '')         + '|' +
                                               ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,3),IT.QTITEMPROD))), '.', ','), '')         + '|' +
                                               ISNULL(PR.SGUNIDADE, '')                                                                           + '|' +
                                               ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),IT.VRTOTAITEM))), '.', ','), '')         + '|' +
                                               ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),IT.VRDESCITEM))), '.', ','), '')         + '|' +
                                               '0'                                                                                     + '|' +
                                               ISNULL(SUBSTRING(PR.CDCLASFISC,3,9), '')                                                            + '|' +
                                               ''                                                                                      + '|' +
                                               ''                                                                                      + '|' +
                                               ISNULL(RIGHT(REPLICATE('0', 3)+ISNULL(IP.CDSITUTRIBIT,0), 3), '')                                   + '|' +
                                               ISNULL(IP.CDFISCOPERIT, '')                                                                         + '|' +
                                               ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),CASE IP.CDFISCALIT WHEN '1' THEN IP.VRBASECALCIT ELSE 0 END))), '.', ','), '')                                    + '|' +
                                               ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),CASE IP.CDFISCALIT WHEN '1' THEN IP.VRALIQITEM ELSE  0 END))), '.', ','), '')                                    + '|' +
                                               ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),CASE IP.CDFISCALIT WHEN '1' THEN IP.VRIMPOSTOIT ELSE 0 END))), '.', ','), '')                                    + '|' +
                                               ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),TEKNISA.VALOR_IMPOSTO_ITEM(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, @V_CDIMPOSTRIB,'B', '', '')))), '.', ','), '')    + '|' +
                                               ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),TEKNISA.VALOR_IMPOSTO_ITEM(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, @V_CDIMPOSTRIB,'A', '', '')))), '.', ','), '')    + '|' +
                                               ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),TEKNISA.VALOR_IMPOSTO_ITEM(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, @V_CDIMPOSTRIB,'I', '', '')))), '.', ','), '')    + '|' +
                                               ISNULL(SUBSTRING(CDSITUTRIBIT,2,3), '')                                                                                                                                           + '|' +
                                               ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),TEKNISA.VALOR_IMPOSTO_ITEM(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, '10','B', '', '')))), '.', ','), '')              + '|' +
                                               ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),TEKNISA.VALOR_IMPOSTO_ITEM(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, '10','A', '', '')))), '.', ','), '')              + '|' +
                                               ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),TEKNISA.VALOR_IMPOSTO_ITEM(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, '10','I', '', '')))), '.', ','), '')              + '|' ) AS ARQ_C300
                                       FROM ITEMPRODUTO IT, PRODUTO PR, ITEMPROIMPOS IP
                                       WHERE IT.CDFILIAL      = @V_C020CDFILIAL    AND
                                             IT.NRLANCTONF    = @V_C020NRLANCTONF  AND
                                             IT.CDFILIAL      = IP.CDFILIAL           AND
                                             IT.NRLANCTONF    = IP.NRLANCTONF         AND
                                             IT.NRSEQITPROD   = IP.NRSEQITPROD        AND
                                             IT.CDPRODUTO     = PR.CDPRODUTO          AND
                                             IP.CDIMPOSTO     = @P_CDIMPOSTO          AND
                                             IP.NRITPROIMPOS  = '1'                     )

         OPEN C_REGC300;

         FETCH NEXT FROM C_REGC300 INTO @V_ARQ_C300;

         WHILE (@@FETCH_STATUS = 0) BEGIN

       	     INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
          	('19'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGC), 8), '|C300'+ '|' +@V_SEQUENCIAL+ '|' + @V_ARQ_C300, @P_NRSEQARQMAG);
             SET @V_SEQUENCIAL = RIGHT(REPLICATE('0', 3)+RTRIM(CONVERT(VARCHAR,CONVERT(DOUBLE PRECISION,(RTRIM(@V_SEQUENCIAL))+1))), 3);
             SET @V_CONTREGC =  ISNULL(@V_CONTREGC,0) + 1;

    	     FETCH NEXT FROM C_REGC300 INTO @V_ARQ_C300;

      END;
      CLOSE C_REGC300;
      DEALLOCATE C_REGC300;

     END; -- end if  
 
     FETCH NEXT FROM C_REGC020 INTO @V_ARQ_C020, @V_C020CDFILIAL,  @V_C020NRLANCTONF, @V_C020NRINSJURTRAN,
                                    @V_C020IDTPIJURTRAN, @V_C020VRPBRUTOVOLU, @V_C020VRPLIQUVOLU, @V_C020NRPLACAVEIC,
		         	    @V_C020DSQTDEVOLU, @V_C020DSESPECVOLU, @V_C020DSMARCAVOLU, @V_C020DSNUMEVOLU,
	   			    @V_C020CDFISCOPERNF;
   END; 
   CLOSE C_REGC020;
   DEALLOCATE C_REGC020;

   DECLARE C_REGC550 CURSOR FOR(SELECT DISTINCT ('|C550'                                			  + '|' +
                                                  ISNULL(N.CDMODDOCFISC, '')                                              			  + '|' +
                                                  CASE N.IDSTATUS WHEN 'C' THEN '02' ELSE '00' END           				  + '|' +
                                                  ISNULL(N.CDSERIE, '')                                                   			  + '|' +
                                                  ''                                                          			  + '|' +
                                                  ISNULL(N.NRNOTAFISC, '')                                                  			  + '|' +
                                                  ISNULL(REPLACE(CONVERT(CHAR, N.DTEMISSAO,103), '/',''), '')                             				  + '|' +
                                                  ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),N.VRNOTAFISC))), '.', ','), '')  + '|' +
                                                  ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),TEKNISA.VALOR_ENCARGO(N.CDFILIAL, N.NRLANCTONF, DESCONTO.IDENCARGO, DESCONTO.CDENCARGO)))), '.', ','), '')    + '|' +
                                                  ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),N.VRPRODUTOS))), '.', ','), '')  + '|' +
                                                  ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),N.VRBASECALCNF))), '.', ','), '') + '|' +
                                                  ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),N.VRIMPOSTONF))), '.', ','), '') + '|' +
                                                  ''                                                            + '|'  ) AS ARQ_C550,
                                                  N.CDFILIAL, N.NRLANCTONF
                                          FROM ITEMPRODUTO IP, ITEMVENDA IV, MOVICLIE MC,
                                               (SELECT MAX(E.CDENCARGO)  AS CDENCARGO,
                                                       MAX(EN.IDENCARGO) AS IDENCARGO
                                                FROM RELACENCARGO E, ENCARGO EN
                                                WHERE E.CDENCARGO  = EN.CDENCARGO AND
                                                      EN.IDENCARGO = 'D'          AND
                                                      E.IDENCARGODOS LIKE '%DESC%') DESCONTO,
                                               (SELECT I.CDFILIAL, I.NRLANCTONF, N.CDSERIE, N.NRNOTAFISC, N.DTEMISSAO, N.VRNOTAFISC,
                                                       N.VRPRODUTOS, N.IDSTATUS, N.CDMODDOCFISC,
                                                       SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                                                       SUM(CASE I.CDFISCALNF WHEN '1' THEN I.VRBASECALCNF ELSE 0 END) AS VRBASECALCNF,
                                                       SUM(CASE I.CDFISCALNF WHEN '1' THEN I.VRIMPOSTONF  ELSE 0 END) AS VRIMPOSTONF,
                                                       SUM(CASE I.CDFISCALNF WHEN '3' THEN I.VRCONTABILNF ELSE 0 END) AS VROUTRCRED,
                                                       SUM(CASE I.CDFISCALNF WHEN '2' THEN I.VRCONTABILNF ELSE 0 END) AS VRISENTRIB
                                                FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I
                                                WHERE M.CDEMPRESA       = @P_CDEMPRESA                                 AND
                                                      M.CDINSCESTA      = @P_CDINSCESTA                                AND
                                                      M.DTINIPERIODO    = @P_DTINICIAL                                 AND
                                                      M.DTFIMPERIODO    = @P_DTFINAL                                   AND
                                                      M.CDIMPOSTO       = @P_CDIMPOSTO                                 AND
                                                      I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU                               AND
                                                      I.CDIMPOSTO       = @P_CDIMPOSTO                                 AND
                                                      ISNULL(I.IDIMPOSTONF, '1') <> '3'                                AND
                                                      N.CDFILIAL        = I.CDFILIAL                                   AND
                                                      N.NRLANCTONF      = I.NRLANCTONF                                 AND
                                                      ((N.IDSTATUS      = 'A') OR (N.IDSTATUS = 'E'))                       AND
                                                      N.IDENTRSAIDOP    = 'S'                                          AND
                                                      N.CDMODDOCFISC    = '02'
                                                GROUP BY I.CDFILIAL, I.NRLANCTONF, N.CDSERIE, N.NRNOTAFISC, N.DTEMISSAO, N.VRNOTAFISC,
                                                         N.VRPRODUTOS,N.IDSTATUS, N.CDMODDOCFISC) N
                                          WHERE N.CDFILIAL      = IP.CDFILIAL                AND
                                                N.NRLANCTONF    = IP.NRLANCTONF              AND
                                                IP.CDFILIAL     = IV.CDFILIAL                AND
                                                IP.CDCAIXA      = IV.CDCAIXA                 AND
                                                IP.NRSEQVENDA   = IV.NRSEQVENDA              AND
                                                IP.NRSEQUITVEND = IV.NRSEQUITVEND            AND
                                                IV.CDFILIAL     = MC.CDFILIAL                AND
                                                IV.CDCAIXA      = MC.CDCAIXA                 AND
                                                IV.NRSEQVENDA   = MC.NRSEQVENDA)

      OPEN C_REGC550;

      FETCH NEXT FROM C_REGC550 INTO @V_ARQ_C550, @V_C550_CDFILIAL, @V_C550_NRLANCTONF;

      WHILE (@@FETCH_STATUS = 0) BEGIN

      	  INSERT INTO ARQMAGNETICO
          (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
          ('19'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGC), 8), @V_ARQ_C550, @P_NRSEQARQMAG);
            SET @V_SEQUENCIAL = RIGHT(REPLICATE('0', 3)+RTRIM(CONVERT(VARCHAR,CONVERT(DOUBLE PRECISION,('1')))), 3);
            SET @V_CONTREGC =  ISNULL(@V_CONTREGC,0) + 1;	            

           DECLARE C_REGC555 CURSOR FOR (SELECT (ISNULL(PR.CDARVPROD, '')                                                                      + '|' +
                                           ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,3),IP.QTITEMPROD))), '.', ','), '')         + '|' +
                                           ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,3),IP.VRUNITITEM))), '.', ','), '')         + '|' +
                                           ISNULL(PR.SGUNIDADE, '')                                                                            + '|' +
                                           ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),IP.VRTOTAITEM))), '.', ','), '')         + '|' +
                                           ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2),IP.VRDESCITEM))), '.', ','), '')         + '|' +
                                           ISNULL(IP.CDSITUTRIBPR, '')                                                                         + '|' +
                                           ISNULL(IP.CDFISOPEITEM, '')                                                                         + '|' +
                                           ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,SUM(CONVERT(NUMERIC(12,2), IT.VRBASECALCIT)))), '.', ','), '') + '|' +
                                           ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,IT.VRALIQITEM)), '.', ','), '')                                + '|' +
                                           ISNULL(REPLACE(LTRIM(CONVERT(VARCHAR,SUM(CONVERT(NUMERIC(12,2), IT.VRIMPOSTOIT)))), '.', ','), '')  + '|' )AS ARQ_C555
                                    FROM ITEMPRODUTO IP, PRODUTO PR, ITEMPROIMPOS IT
                                    WHERE @V_C550_CDFILIAL   = IP.CDFILIAL                   AND
                                          @V_C550_NRLANCTONF = IP.NRLANCTONF                 AND
                                          IP.CDFILIAL        = IT.CDFILIAL                   AND
                                          IP.NRLANCTONF      = IT.NRLANCTONF                 AND
                                          IP.NRSEQITPROD     = IT.NRSEQITPROD                AND
                                          PR.CDPRODUTO       = IP.CDPRODUTO
                                    GROUP BY IP.NRSEQITPROD, PR.CDARVPROD,
                                             IP.QTITEMPROD,
                                             IP.VRUNITITEM,
                                             PR.SGUNIDADE,
                                             IP.VRTOTAITEM,
                                             IP.VRDESCITEM,
                                             IP.CDSITUTRIBPR,
                                             IP.CDFISOPEITEM, 
                                             IT.VRALIQITEM)
      OPEN C_REGC555;

      FETCH NEXT FROM C_REGC555 INTO @V_ARQ_C555;

      WHILE (@@FETCH_STATUS = 0) BEGIN 
      	  INSERT INTO ARQMAGNETICO
          (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
          ('20'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGC), 8), '|C555'+ '|' +@V_SEQUENCIAL+ '|' + @V_ARQ_C555, @P_NRSEQARQMAG);
          SET @V_CONTREGC =  ISNULL(@V_CONTREGC,0) + 1;
	  FETCH NEXT FROM C_REGC550 INTO @V_ARQ_C555;
      END;
      CLOSE C_REGC555;
      DEALLOCATE C_REGC555;  

      FETCH NEXT FROM C_REGC550 INTO @V_ARQ_C550, @V_C550_CDFILIAL, @V_C550_NRLANCTONF;

    END;
    CLOSE C_REGC550;
    DEALLOCATE C_REGC550;
  
   IF @V_CONTREGC <> 0 BEGIN
      SET @V_MOVIMENT = 0;
   END
   ELSE BEGIN
      SET @V_MOVIMENT = 1;
   END;
   /* ABERTURA DO REGISTRO C */
   SET @V_CONTREGC = ISNULL(@V_CONTREGC,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('18'+RIGHT(REPLICATE('0', 8)+ '0',8), '|C001'+ '|' + CONVERT(VARCHAR, @V_MOVIMENT)+ '|', @P_NRSEQARQMAG);
   /* ENCERRAMENTO DO BLOCO C */
   SET @V_CONTREGC = ISNULL(@V_CONTREGC,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('23'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGC), 8), '|C990'+ '|' + CONVERT(VARCHAR, @V_CONTREGC) + '|', @P_NRSEQARQMAG);
   RETURN(@V_CONTREGC);

   SET @P_RETURN = @V_CONTREGC; 
END
GO

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

