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

DROP PROCEDURE ARQUIVO_SINTEGRA_70
GO
CREATE PROCEDURE ARQUIVO_SINTEGRA_70
   @P_CDEMPRESA        VARCHAR(02),
   @P_CDIMPOSTO        VARCHAR(02),
   @P_CDINSCESTA       VARCHAR(16),
   @P_DTINICIAL        DATETIME,
   @P_DTFINAL          DATETIME,
   @P_CDPAIS           VARCHAR(04),
   @P_SGESTADO         VARCHAR(02),
   @P_NRSEQARQMAG      VARCHAR(10),
   @V_CDSITUCRT        VARCHAR(01),
   @P_CONTADOR         NUMERIC       OUTPUT
AS
   DECLARE @V_REGISTRO74     VARCHAR(250);
   DECLARE @V_DTENTRSAID     DATETIME;
   DECLARE @V_CONTREG        NUMERIC;
   DECLARE @V_CONTADOR       NUMERIC;
   DECLARE @V_CONTGERAL      NUMERIC;
   DECLARE @V_REGISTRO70     VARCHAR(250);
BEGIN
   SET @V_CONTREG = 0;
   SET @V_CONTADOR = @P_CONTADOR + 1;

   IF @V_CDSITUCRT = '3' BEGIN
       DECLARE C_REGISTRO70 CURSOR FOR
          SELECT SUBSTRING(
                 '70'+
                 LEFT(ISNULL(NF.NRINSJURNF,'00000000000000')+REPLICATE(' ',14),14)+
                 LEFT(ISNULL(REPLACE(REPLACE(REPLACE(FO.NRINSESTFORN,'.',''),',',''),'-',''),'ISENTO')+REPLICATE(' ',14),14)+
                 RTRIM(CONVERT(CHAR, NF.DTENTRSAID,112))+
                 LEFT(ISNULL(EF.SGESTADO,' ')+'  ',2)+
                 LEFT(ISNULL(NF.CDMODDOCFISC,' ')+REPLICATE(' ',2),2)+
                 LEFT((CASE RTRIM(ISNULL(NF.CDSERIEENTR,' ')) WHEN '-' THEN ' ' ELSE ISNULL(NF.CDSERIEENTR,' ') END)+'   ',3)+
                 LEFT(SUBSTRING(NF.NRNOTAFISC,2,6)+REPLICATE(' ',6), 6)+
                 LEFT(ISNULL(NF.CDFISCOPERNF,'0')+'    ',4)+
                 RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(13,2),NF.VRNOTAFISC)),'.','')),13)+
                 RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(13,2),NF.VRBASECALCNF)),'.','')),13)+
                 RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(13,2),NF.VRIMPOSTONF)),'.','')),13)+
                 RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(13,2),NF.VRISENTRIB)),'.','')),13)+
                 RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(13,2),NF.VROUTRCRED)),'.','')),13)+
                 ISNULL(NF.IDPGTOFRETE, '1')+
                 (CASE NF.IDSTATUS WHEN 'C' THEN 'S' ELSE 'N' END),1 , 126) AS DSARQMAG,DTENTRSAID
            FROM (SELECT N.CDFILIAL, N.NRLANCTONF, N.NRINSJURNF, N.CDFORNECED, N.DTENTRSAID, N.CDSERIEENTR,
                         N.NRNOTAFISC, N.VRNOTAFISC, N.IDSTATUS, N.IDPGTOFRETE, I.CDFISCOPERNF,N.CDMODDOCFISC,
                         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 '2' THEN I.VRCONTABILNF ELSE 0 END)) AS VRISENTRIB,
                         SUM((CASE I.CDFISCALNF WHEN '3' THEN I.VRCONTABILNF ELSE 0 END)) AS VROUTRCRED
                   FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, MODDOCFISC MD
                   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.IDENTRSAIDOP = 'E' )                AND
                         (N.IDSTATUS IN ('A', 'E'))             AND
                         (N.CDMODDOCFISC    = MD.CDMODDOCFISC)  AND
                         (MD.CDTPREGFISC    = '70')
                   GROUP BY N.CDFILIAL, N.NRLANCTONF, N.NRINSJURNF, N.CDFORNECED, N.DTENTRSAID, N.CDSERIEENTR,
                         N.NRNOTAFISC, N.VRNOTAFISC, N.IDSTATUS, N.IDPGTOFRETE,I.CDFISCOPERNF,N.CDMODDOCFISC) NF,

                 FORNECEDOR FO LEFT JOIN ENDEFORN EF ON (EF.CDFORNECED = FO.CDFORNECED) AND
                                                           (EF.IDTPENDEFORN = ISNULL(FO.IDENDEMISFOR,'P'))
           WHERE (FO.CDFORNECED      = NF.CDFORNECED  )              AND
                 (EF.CDPAIS          = ISNULL(@P_CDPAIS,EF.CDPAIS))  AND
                 (CHARINDEX(EF.SGESTADO, ISNULL(@P_SGESTADO, EF.SGESTADO)) > 0);

       OPEN C_REGISTRO70;
       FETCH NEXT FROM C_REGISTRO70 INTO @V_REGISTRO70,
                                         @V_DTENTRSAID;

          WHILE (@@FETCH_STATUS = 0) BEGIN


             INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
              VALUES
                (RIGHT(REPLICATE('0',2)+CONVERT(VARCHAR,@V_CONTADOR),2)+RIGHT(REPLICATE('0',8)+CONVERT(VARCHAR,@V_CONTREG),8),@V_REGISTRO70,@P_NRSEQARQMAG);

             SET @V_CONTREG  = ISNULL(@V_CONTREG,0)+1;

       FETCH NEXT FROM C_REGISTRO70 INTO @V_REGISTRO70,
                                         @V_DTENTRSAID;
       END;
   END;

   IF @V_CDSITUCRT <> '3' BEGIN
       DECLARE C_REGISTRO70 CURSOR FOR
          SELECT SUBSTRING(
                 '70'+
                 LEFT(ISNULL(NF.NRINSJURNF,'00000000000000')+REPLICATE(' ',14),14)+
                 LEFT(ISNULL(REPLACE(REPLACE(REPLACE(FO.NRINSESTFORN,'.',''),',',''),'-',''),'ISENTO')+REPLICATE(' ',14),14)+
                 RTRIM(CONVERT(CHAR, NF.DTENTRSAID,112))+
                 LEFT(ISNULL(EF.SGESTADO,' ')+'  ',2)+
                 LEFT(ISNULL(NF.CDMODDOCFISC,' ')+REPLICATE(' ',2),2)+
                 LEFT((CASE RTRIM(ISNULL(NF.CDSERIEENTR,' ')) WHEN '-' THEN ' ' ELSE ISNULL(NF.CDSERIEENTR,' ') END)+'   ',3)+
                 LEFT(SUBSTRING(NF.NRNOTAFISC,2,6)+REPLICATE(' ',6), 6)+
                 LEFT(ISNULL(NF.CDFISCOPERNF,'0')+'    ',4)+
                 RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(13,2),NF.VRNOTAFISC)),'.','')),13)+
                 RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(13,2),NF.VRBASECALCNF)),'.','')),13)+
                 RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(13,2),NF.VRIMPOSTONF)),'.','')),13)+
                 RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(13,2),NF.VRISENTRIB)),'.','')),13)+
                 RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(13,2),NF.VROUTRCRED)),'.','')),13)+
                 ISNULL(NF.IDPGTOFRETE, '1')+
                 (CASE NF.IDSTATUS WHEN 'C' THEN 'S' ELSE 'N' END),1 , 126) AS DSARQMAG,DTENTRSAID
            FROM (SELECT N.CDFILIAL, N.NRLANCTONF, N.NRINSJURNF, N.CDFORNECED, N.DTENTRSAID, N.CDSERIEENTR,
                         N.NRNOTAFISC, N.VRNOTAFISC, N.IDSTATUS, N.IDPGTOFRETE, I.CDFISOPEITEM AS CDFISCOPERNF,N.CDMODDOCFISC,
                         0 AS VRBASECALCNF,
                         0 AS VRIMPOSTONF,
                         0 AS VRISENTRIB,
                         SUM(I.VRCONTITEM) AS VROUTRCRED
                   FROM MAPAAPURACSN M, NOTAFISCAL N, ITEMPRODUTO I, MODDOCFISC MD
                   WHERE (M.CDEMPRESA       = @P_CDEMPRESA)     AND
                         (N.CDINSCESTA      = @P_CDINSCESTA)    AND
                         (M.DTINIPERIODO    = @P_DTINICIAL)     AND
                         (M.DTFIMPERIODO    = @P_DTFINAL)       AND
                         (N.NRSEQUMAPAPUSN  = M.NRSEQUMAPAPU)   AND
                         (N.CDFILIAL   = I.CDFILIAL)            AND
                         (N.NRLANCTONF = I.NRLANCTONF)          AND
                         (N.IDENTRSAIDOP = 'E' )                AND
                         (N.IDSTATUS IN ('A', 'E'))             AND
                         (N.CDMODDOCFISC    = MD.CDMODDOCFISC)  AND
                         (MD.CDTPREGFISC    = '70')
                   GROUP BY N.CDFILIAL, N.NRLANCTONF, N.NRINSJURNF, N.CDFORNECED, N.DTENTRSAID, N.CDSERIEENTR,
                         N.NRNOTAFISC, N.VRNOTAFISC, N.IDSTATUS, N.IDPGTOFRETE,I.CDFISOPEITEM,N.CDMODDOCFISC) NF,

                 FORNECEDOR FO LEFT JOIN ENDEFORN EF ON (EF.CDFORNECED = FO.CDFORNECED) AND
                                                           (EF.IDTPENDEFORN = ISNULL(FO.IDENDEMISFOR,'P'))
           WHERE (FO.CDFORNECED      = NF.CDFORNECED  )              AND
                 (EF.CDPAIS          = ISNULL(@P_CDPAIS,EF.CDPAIS))  AND
                 (CHARINDEX(EF.SGESTADO, ISNULL(@P_SGESTADO, EF.SGESTADO)) > 0);

       OPEN C_REGISTRO70;
       FETCH NEXT FROM C_REGISTRO70 INTO @V_REGISTRO70,
                                         @V_DTENTRSAID;

          WHILE (@@FETCH_STATUS = 0) BEGIN


             INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
              VALUES
                (RIGHT(REPLICATE('0',2)+CONVERT(VARCHAR,@V_CONTADOR),2)+RIGHT(REPLICATE('0',8)+CONVERT(VARCHAR,@V_CONTREG),8),@V_REGISTRO70,@P_NRSEQARQMAG);

             SET @V_CONTREG  = ISNULL(@V_CONTREG,0)+1;

       FETCH NEXT FROM C_REGISTRO70 INTO @V_REGISTRO70,
                                         @V_DTENTRSAID;
       END;
   END;


   CLOSE C_REGISTRO70;
   DEALLOCATE C_REGISTRO70;

   SET @P_CONTADOR = @V_CONTADOR;
END;
GO

-- VERSAO --
EXEC ATUVEROBJ 'ARQUIVO_SINTEGRA_70','5.05.061';
GO

