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

DROP PROCEDURE REGISTRO60_M_A_D_I_R_SINTEGRA
GO
CREATE PROCEDURE REGISTRO60_M_A_D_I_R_SINTEGRA
   @P_CDEMPRESA        VARCHAR(02),
   @P_CDIMPOSTO        VARCHAR(02),
   @P_CDINSCESTA       VARCHAR(16),
   @P_DTINICIAL        DATETIME,
   @P_DTFINAL          DATETIME,
   @P_NRSEQARQMAG      VARCHAR(10),
   @P_CDSITUCRT        VARCHAR(01),
   @P_CONTADOR         NUMERIC        OUTPUT
AS
   DECLARE @V_CONTREG        NUMERIC;
   DECLARE @V_CONTADOR       NUMERIC;
   DECLARE @V_STTOTAL        VARCHAR(160);
   DECLARE @V_CDARVPROD      VARCHAR(20);
   DECLARE @V_REGISTROM      VARCHAR(250);
   DECLARE @V_REGISTROR      VARCHAR(250);
   DECLARE @V_REGISTROA      VARCHAR(250);
   DECLARE @V_REGISTROI      VARCHAR(250);
   DECLARE @V_REGISTROD      VARCHAR(250);
   DECLARE @V_NMPRODUTO      VARCHAR(250);
   DECLARE @V_SGUNIDADE      VARCHAR(250);
   DECLARE @V_CDCLASFISC     VARCHAR(250);
   DECLARE @V_MAXCDARVPROD   VARCHAR(20);
   DECLARE @V_NRECFREDZ      VARCHAR(250);
   DECLARE @V_DTREDUCAOZ     DATETIME;

BEGIN
   SET @V_CONTREG = 0;
   SET @V_CONTADOR = @P_CONTADOR + 1;

   DECLARE C_REGISTRO60M CURSOR FOR
      SELECT SUBSTRING(
             '60'+
             'M'+
             RTRIM(CONVERT(CHAR,DTREDUCAOZ, 112))+
             LEFT(NRSERIEREDZ+REPLICATE(' ',20), 20)+
             LEFT(CDCAIXA+'   ', 3)+
             '2D'+
             RIGHT(REPLICATE('0',6)+ISNULL(LTRIM((CASE LEN(NRPRIMCFREDZ) WHEN 10 THEN SUBSTRING(NRPRIMCFREDZ, 5, 6) ELSE RIGHT(REPLICATE('0',6)+NRPRIMCFREDZ, 6) END)),'0'), 6)+
             RIGHT(REPLICATE('0',6)+ISNULL(LTRIM((CASE LEN(NRULTCFREDZ)  WHEN 10 THEN SUBSTRING(NRULTCFREDZ, 5, 6)  ELSE RIGHT(REPLICATE('0',6)+NRULTCFREDZ, 6)  END)),'0'), 6)+
             RIGHT(REPLICATE('0',6)+ISNULL(LTRIM((CASE LEN(NRREDREDZ) WHEN 10 THEN SUBSTRING(NRREDREDZ, 5, 6) ELSE RIGHT(REPLICATE('0',6)+NRREDREDZ, 6) END)),'0'), 6)+
             RIGHT(REPLICATE('0',3)+ISNULL(LTRIM((CASE LEN(NRREINIREDZ) WHEN 10 THEN SUBSTRING(NRREINIREDZ, 8, 3) ELSE RIGHT(REPLICATE('0', 3)+NRREINIREDZ, 3) END)),'0'), 3)+
             RIGHT(REPLICATE('0',16)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(16,2), ABS(ISNULL(RZ.VRVENBRUREDZ,0)))),'.','')),16)+
             RIGHT(REPLICATE('0',16)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(16,2),ABS(ISNULL(VRGTREDZ,0)))),'.','')), 16)+
             REPLICATE(' ', 37), 1 , 126) AS DSARQUMAG,RZ.DTREDUCAOZ,RZ.NRSERIEREDZ
        FROM (SELECT DTREDUCAOZ AS DTREDUCAOZ,
                     NRSERIEREDZ AS NRSERIEREDZ,
                     CDCAIXA AS CDCAIXA,
                     NRPRIMCFREDZ AS NRPRIMCFREDZ,
                     NRULTCFREDZ AS NRULTCFREDZ,
                     NRREDREDZ AS NRREDREDZ,
                     NRREINIREDZ AS NRREINIREDZ,
                     SUM(RZ.VRVENBRUREDZ) AS VRVENBRUREDZ,
                     SUM(VRGTREDZ)        AS VRGTREDZ
                FROM REDUCAOZ RZ ,FILIAL FI
               WHERE (RZ.DTREDUCAOZ BETWEEN @P_DTINICIAL AND @P_DTFINAL) AND
                     (RZ.CDFILIAL    = FI.CDFILIAL)      AND
                     (FI.CDEMPRESA   = @P_CDEMPRESA )    AND
                     (FI.CDINSCESTA  = @P_CDINSCESTA)    AND
                     (RZ.VRVENLIQREDZ >= 0)
               GROUP BY DTREDUCAOZ,
                        NRSERIEREDZ,
                        CDCAIXA,
                        NRPRIMCFREDZ,
                        NRULTCFREDZ,
                        NRREDREDZ,
                        NRREINIREDZ)RZ
       ORDER BY DTREDUCAOZ,NRSERIEREDZ;
   OPEN C_REGISTRO60M;
   FETCH NEXT FROM C_REGISTRO60M INTO @V_REGISTROM,@V_DTREDUCAOZ,@V_NRECFREDZ;
   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_REGISTROM, @P_NRSEQARQMAG);

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

      DECLARE C_REGISTRO60A CURSOR FOR
         SELECT SUBSTRING(
                '60'+
                'A'+
                RTRIM(CONVERT(CHAR, DTREDUCAOZ, 112))+
                LEFT(NRSERIEREDZ+REPLICATE(' ',20), 20)+
                LEFT(LTRIM(SITUTRIB)+REPLICATE(' ', 4), 4)+
                RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(12,2),VRACUMULADO)),'.','')),12)+
                REPLICATE(' ', 79),1,126) AS DSARQMAG
           FROM (
                  SELECT NRECFREDZ, NRSERIEREDZ, DTREDUCAOZ, SITUTRIB,
                         SUM(VRACUMULADO) AS VRACUMULADO
                  FROM
                    (SELECT NRECFREDZ, DTREDUCAOZ,
                          CASE @P_CDSITUCRT WHEN '3' THEN RIGHT('0000'+CONVERT(CHAR, (CASE VRALIQIMPRZ WHEN 0.00 THEN 0 ELSE (VRALIQIMPRZ*100) END)), 4)
                                                    ELSE 'N' END AS SITUTRIB,
                          SUM(VRBCALCIMPRZ) VRACUMULADO,
                          '' AS NRSERIEREDZ
                     FROM IMPOSTOREDZ IZ, FILIAL FI, IMPOSTO I
                     WHERE (DTREDUCAOZ BETWEEN @V_DTREDUCAOZ AND @V_DTREDUCAOZ) AND
                           IZ.NRECFREDZ = @V_NRECFREDZ      AND
                           (IZ.CDFILIAL   = FI.CDFILIAL)    AND
                           (FI.CDEMPRESA  = @P_CDEMPRESA)   AND
                           (FI.CDINSCESTA = @P_CDINSCESTA)  AND
                           (IZ.CDIMPOSTO  = I.CDIMPOSTO)    AND
                           (I.IDTPIMPOSFIS = 'T')
                    GROUP BY NRECFREDZ, DTREDUCAOZ, VRALIQIMPRZ) AS AGRUPA
                GROUP BY NRECFREDZ, DTREDUCAOZ, SITUTRIB, NRSERIEREDZ
                UNION ALL
                SELECT NRECFREDZ, DTREDUCAOZ, 'F' AS SITUTRIB, SUM(VRSTRIBREDZ) VRACUMULADO, NRSERIEREDZ
                FROM REDUCAOZ RZ, FILIAL FI
                WHERE (DTREDUCAOZ BETWEEN @V_DTREDUCAOZ AND @V_DTREDUCAOZ) AND
                      RZ.NRECFREDZ = @V_NRECFREDZ      AND
                      (RZ.CDFILIAL   = FI.CDFILIAL)    AND
                      (FI.CDEMPRESA  = @P_CDEMPRESA)   AND
                      (FI.CDINSCESTA = @P_CDINSCESTA)  AND
                      (VRSTRIBREDZ >= 0)                AND
                      (RZ.VRVENLIQREDZ >= 0)
                GROUP BY NRECFREDZ, DTREDUCAOZ, NRSERIEREDZ
                UNION ALL
                SELECT NRECFREDZ, DTREDUCAOZ, 'I' AS SITUTRIB, SUM(VRISENCREDZ) AS VRACUMULADO, NRSERIEREDZ
                FROM REDUCAOZ RZ, FILIAL FI
                WHERE (DTREDUCAOZ BETWEEN @V_DTREDUCAOZ AND @V_DTREDUCAOZ) AND
                      RZ.NRECFREDZ   = @V_NRECFREDZ    AND
                      (RZ.CDFILIAL   = FI.CDFILIAL)    AND
                      (FI.CDEMPRESA  = @P_CDEMPRESA)   AND
                      (FI.CDINSCESTA = @P_CDINSCESTA)  AND
                      (VRISENCREDZ >= 0)                AND
                      (RZ.VRVENLIQREDZ >= 0)
                GROUP BY NRECFREDZ, DTREDUCAOZ, NRSERIEREDZ

                UNION ALL
                SELECT NRECFREDZ, DTREDUCAOZ, 'N' AS SITUTRIB, SUM(VRNINCIDREDZ) AS VRACUMULADO, NRSERIEREDZ
                FROM REDUCAOZ RZ, FILIAL FI
                WHERE (DTREDUCAOZ BETWEEN @V_DTREDUCAOZ AND @V_DTREDUCAOZ) AND
                      RZ.NRECFREDZ = @V_NRECFREDZ      AND
                      (RZ.CDFILIAL   = FI.CDFILIAL)    AND
                      (FI.CDEMPRESA  =  @P_CDEMPRESA)  AND
                      (FI.CDINSCESTA =  @P_CDINSCESTA) AND
                      (VRNINCIDREDZ >= 0 )              AND
                      (RZ.VRVENLIQREDZ >= 0)
                GROUP BY NRECFREDZ, DTREDUCAOZ, NRSERIEREDZ

                UNION ALL
                SELECT NRECFREDZ, DTREDUCAOZ, 'CANC' AS SITUTRIB, SUM(VRTOTCANREDZ) AS VRACUMULADO, NRSERIEREDZ
                FROM REDUCAOZ RZ, FILIAL FI
                WHERE (DTREDUCAOZ BETWEEN  @V_DTREDUCAOZ AND  @V_DTREDUCAOZ) AND
                      RZ.NRECFREDZ = @V_NRECFREDZ      AND
                      (RZ.CDFILIAL   = FI.CDFILIAL)     AND
                      (FI.CDEMPRESA  =  @P_CDEMPRESA)   AND
                      (FI.CDINSCESTA =  @P_CDINSCESTA)  AND
                      (VRTOTCANREDZ >= 0)                AND
                      (RZ.VRVENLIQREDZ >= 0)
                GROUP BY NRECFREDZ, DTREDUCAOZ, NRSERIEREDZ

                UNION ALL
                SELECT NRECFREDZ, DTREDUCAOZ, 'DESC' AS SITUTRIB, SUM(VRTOTDESREDZ) AS VRACUMULADO, NRSERIEREDZ
                FROM REDUCAOZ RZ, FILIAL FI
                WHERE (DTREDUCAOZ BETWEEN  @V_DTREDUCAOZ AND  @V_DTREDUCAOZ) AND
                      RZ.NRECFREDZ = @V_NRECFREDZ       AND
                      (RZ.CDFILIAL   = FI.CDFILIAL)     AND
                      (FI.CDEMPRESA  =  @P_CDEMPRESA)   AND
                      (FI.CDINSCESTA =  @P_CDINSCESTA)  AND
                      (VRTOTDESREDZ >= 0)                AND
                      (RZ.VRVENLIQREDZ >= 0)
                GROUP BY NRECFREDZ, DTREDUCAOZ, NRSERIEREDZ

                UNION ALL
                SELECT NRECFREDZ, DTREDUCAOZ, 'ISS' AS SITUTRIB, SUM(VRBCALCIMPRZ) VRACUMULADO,'' AS NRSERIEREDZ
                FROM IMPOSTOREDZ IZ, FILIAL FI, IMPOSTO I
                WHERE (DTREDUCAOZ BETWEEN  @V_DTREDUCAOZ AND  @V_DTREDUCAOZ) AND
                      IZ.NRECFREDZ = @V_NRECFREDZ      AND
                      (IZ.CDFILIAL   = FI.CDFILIAL)     AND
                      (FI.CDEMPRESA  =  @P_CDEMPRESA)   AND
                      (FI.CDINSCESTA =  @P_CDINSCESTA)  AND
                      (IZ.CDIMPOSTO = I.CDIMPOSTO)      AND
                      (I.IDTPIMPOSFIS = 'I')
                GROUP BY NRECFREDZ, DTREDUCAOZ)A;

      OPEN C_REGISTRO60A;
      FETCH NEXT FROM C_REGISTRO60A INTO @V_REGISTROA;
         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_REGISTROA,@P_NRSEQARQMAG);

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


      FETCH NEXT FROM C_REGISTRO60A INTO @V_REGISTROA;
      END;
      CLOSE C_REGISTRO60A;
      DEALLOCATE C_REGISTRO60A;

      DECLARE C_REGISTRO60D CURSOR FOR
         SELECT SUBSTRING(
            '60'+
            'D'+
            RTRIM(CONVERT(CHAR, A.DTENTRVENDA, 112))+
            LEFT(ISNULL(NRSERIEREDZ+REPLICATE(' ',20), ' '), 20)+
            LEFT(A.CDARVPROD+REPLICATE(' ',14), 14)+
            RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(13,3),A.QTPRODVEND)),'.','')),13)+
            RIGHT(REPLICATE('0',16)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(16,2), A.VRUNIT)),'.','')), 16)+
            (CASE @P_CDSITUCRT WHEN '3' THEN RIGHT(REPLICATE('0',16)+LTRIM(REPLACE(CONVERT(CHAR, (CASE A.IDTPIMPOSFIS WHEN 'T' THEN (CASE ROUND(A.VRIMPOSTO,2) WHEN 0 THEN 0 ELSE A.BASECALC END) ELSE 0 END)),'.','')),16) ELSE '0000000000000000' END)+
            (CASE @P_CDSITUCRT WHEN '3' THEN (CASE A.IDTPIMPOSFIS WHEN 'F' THEN 'F   ' ELSE (CASE A.IDTPIMPOSFIS WHEN 'N' THEN 'N   ' ELSE
                                                           (CASE A.IDTPIMPOSFIS WHEN 'I' THEN 'I   ' ELSE LTRIM(REPLACE(CONVERT(CHAR, A.VRPEALPRODIT*100),'.', ''))END) END) END)
                                        ELSE 'N   ' END) +
            RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(13,2),(CASE ROUND(A.VRIMPOSTO,2) WHEN 0 THEN 0 ELSE A.VRIMPOSTO END))),'.','')),13)+
            REPLICATE(' ', 19), 1, 126) AS DSARQMAG
         FROM
            (SELECT VD.NRSERIEREDZ, I.IDTPIMPOSFIS, VD.DTENTRVENDA, IT.CDCAIXA, PD.CDARVPROD, IT.QTPRODVEND,
                    AI.VRPEALPRODIT, SUM(IT.VRUNITVEND+VRUNITVENDCL) AS VRUNIT,
                    SUM(IT.QTPRODVEND * ((IT.VRUNITVEND+IT.VRUNITVENDCL)-ISNULL(IT.VRDESITVEND,0) + ISNULL(IT.VRACRITVEND,0))) AS BASECALC,
                    SUM((IT.QTPRODVEND * ((IT.VRUNITVEND+IT.VRUNITVENDCL)-ISNULL(IT.VRDESITVEND,0) + ISNULL(IT.VRACRITVEND,0)) * (AI.VRPEALPRODIT/100))) AS VRIMPOSTO
             FROM ITEMVENDA IT, ITVENDAIMPOS AI, PRODUTO PD, IMPOSTO I,
                  (SELECT V.CDFILIAL, V.CDCAIXA, V.DTENTRVENDA, V.NRSEQVENDA, MAX(R.NRSERIEREDZ) AS NRSERIEREDZ
                   FROM VENDA V, FILIAL FI, REDUCAOZ R
                   WHERE FI.CDEMPRESA    =  @P_CDEMPRESA     AND
                         FI.CDINSCESTA   =  @P_CDINSCESTA    AND
                         V.CDFILIAL      = FI.CDFILIAL       AND
                         V.IDSITUVENDA   = 'O'               AND
                         V.DTENTRVENDA BETWEEN @V_DTREDUCAOZ AND  @V_DTREDUCAOZ AND
                         R.NRECFREDZ    = @V_NRECFREDZ       AND
                         V.CDFILIAL     = R.CDFILIAL         AND
                         V.CDCAIXA      = R.CDCAIXA          AND
                         V.DTENTRVENDA  = R.DTREDUCAOZ       AND
                         V.IDVENDEBCONS <> 'S'               AND
                         V.NRCUPOMFIS IS NOT NULL            AND
                         R.VRVENLIQREDZ > 0
                   GROUP BY V.CDFILIAL, V.CDCAIXA, V.DTENTRVENDA, V.NRSEQVENDA) VD
             WHERE VD.CDFILIAL     = IT.CDFILIAL        AND
                   VD.CDCAIXA      = IT.CDCAIXA         AND
                   VD.NRSEQVENDA   = IT.NRSEQVENDA      AND
                   IT.CDFILIAL     = AI.CDFILIAL        AND
                   IT.CDCAIXA      = AI.CDCAIXA         AND
                   IT.NRSEQVENDA   = AI.NRSEQVENDA      AND
                   IT.NRSEQUITVEND = AI.NRSEQUITVEND    AND
                   IT.CDPRODUTO    = PD.CDPRODUTO       AND
                   AI.CDIMPOSTO    = I.CDIMPOSTO        AND
                   I.IDTPIMPOSFIS IS NOT NULL
             GROUP BY
                VD.NRSERIEREDZ, I.IDTPIMPOSFIS, VD.DTENTRVENDA, IT.CDCAIXA, PD.CDARVPROD, IT.QTPRODVEND, AI.VRPEALPRODIT ) A
         WHERE ROUND(A.VRIMPOSTO,2) > 0.00;

      OPEN C_REGISTRO60D;
      FETCH NEXT FROM C_REGISTRO60D INTO @V_REGISTROD;
         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_REGISTROD,@P_NRSEQARQMAG);

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


      FETCH NEXT FROM C_REGISTRO60D INTO @V_REGISTROD;
      END;
      CLOSE C_REGISTRO60D;
      DEALLOCATE C_REGISTRO60D;

      DECLARE C_REGISTRO60I CURSOR FOR
         SELECT SUBSTRING(
                '60'+
                'I'+
                RTRIM(CONVERT(CHAR,A.DTENTRVENDA, 112))+
                LEFT(ISNULL(NRSERIEREDZ+REPLICATE(' ',20), ' '), 20)+
                '2D'+
                RIGHT(REPLICATE('0',6)+ISNULL(A.NRCUPOMFIS, '0'), 6)+
                LEFT(A.NRSEQUITVEND+REPLICATE(' ',3), 3)+
                LEFT(A.CDARVPROD+REPLICATE(' ',14), 14)+
                RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(13,3),ABS(QTPRODVEND))),'.','')),13)+
                RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(13,2),ABS(VRUNIT))),'.','')),13)+
                (CASE @P_CDSITUCRT WHEN '3' THEN RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(12,2),(CASE A.IDTPIMPOSFIS WHEN 'T' THEN (CASE ROUND(A.VRIMPOSTO,2) WHEN 0 THEN 0 ELSE ABS(BASECALC) END) ELSE 0 END))),'.','')), 12) ELSE '000000000000' END)+
                (CASE @P_CDSITUCRT WHEN '3' THEN (CASE A.IDTPIMPOSFIS WHEN 'F' THEN 'F   ' ELSE (CASE A.IDTPIMPOSFIS WHEN 'N' THEN 'N   ' ELSE
                                                    (CASE A.IDTPIMPOSFIS WHEN 'I' THEN 'I   ' ELSE RIGHT('0000'+LTRIM(REPLACE(CONVERT(CHAR,A.VRPEALPRODIT),'.', '')),4) END) END) END)
                                           ELSE 'N   ' END) +
                (CASE @P_CDSITUCRT WHEN '3' THEN RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(12,2), (CASE A.IDTPIMPOSFIS WHEN 'T' THEN (CASE ROUND(A.VRIMPOSTO,2) WHEN 0 THEN 0 ELSE A.VRIMPOSTO END) ELSE 0 END))),'.','')), 12)
                                           ELSE '000000000000' END) +
                REPLICATE(' ', 16), 1, 126) AS DSARQMAG
           FROM (SELECT VD.NRSERIEREDZ, I.IDTPIMPOSFIS, VD.DTENTRVENDA, IT.CDCAIXA, PD.CDARVPROD, IT.QTPRODVEND, IT.NRSEQUITVEND,
                        AI.VRPEALPRODIT, (IT.VRUNITVEND+VRUNITVENDCL) AS VRUNIT, VD.NRCUPOMFIS,
                        (IT.QTPRODVEND * ((IT.VRUNITVEND+IT.VRUNITVENDCL)-ISNULL(IT.VRDESITVEND,0)+ ISNULL(IT.VRACRITVEND,0))) AS BASECALC,
                        ((IT.QTPRODVEND * ((IT.VRUNITVEND+IT.VRUNITVENDCL)-ISNULL(IT.VRDESITVEND,0)+ ISNULL(IT.VRACRITVEND,0)) * (AI.VRPEALPRODIT/100))) AS VRIMPOSTO
                 FROM ITEMVENDA IT, ITVENDAIMPOS AI, PRODUTO PD, IMPOSTO I,
                      (SELECT V.CDFILIAL, V.CDCAIXA, V.DTENTRVENDA, V.NRSEQVENDA, V.NRCUPOMFIS, MAX(R.NRSERIEREDZ) AS NRSERIEREDZ
                       FROM VENDA V, FILIAL FI, REDUCAOZ R
                       WHERE FI.CDEMPRESA    =  @P_CDEMPRESA     AND
                             FI.CDINSCESTA   =  @P_CDINSCESTA    AND
                             V.CDFILIAL      = FI.CDFILIAL       AND
                             V.IDSITUVENDA   = 'O'               AND
                             V.DTENTRVENDA BETWEEN  @V_DTREDUCAOZ  AND  @V_DTREDUCAOZ AND
                             R.NRECFREDZ    = @V_NRECFREDZ       AND
                             V.CDFILIAL     = R.CDFILIAL         AND
                             V.CDCAIXA      = R.CDCAIXA          AND
                             V.DTENTRVENDA  = R.DTREDUCAOZ       AND
                             V.IDVENDEBCONS <> 'S'               AND
                             V.NRCUPOMFIS IS NOT NULL            AND
                             R.VRVENLIQREDZ > 0
                       GROUP BY V.CDFILIAL, V.CDCAIXA, V.DTENTRVENDA, V.NRSEQVENDA, V.NRCUPOMFIS) VD
                 WHERE VD.CDFILIAL     = IT.CDFILIAL        AND
                       VD.CDCAIXA      = IT.CDCAIXA         AND
                       VD.NRSEQVENDA   = IT.NRSEQVENDA      AND
                       IT.CDPRODUTO    = PD.CDPRODUTO       AND
                       IT.CDFILIAL     = AI.CDFILIAL        AND
                       IT.CDCAIXA      = AI.CDCAIXA         AND
                       IT.NRSEQVENDA   = AI.NRSEQVENDA      AND
                       IT.NRSEQUITVEND = AI.NRSEQUITVEND    AND
                       AI.CDIMPOSTO    = I.CDIMPOSTO        AND
                       I.IDTPIMPOSFIS IS NOT NULL) A
             WHERE ROUND(A.VRIMPOSTO,2) > 0.00;

      OPEN C_REGISTRO60I;
      FETCH NEXT FROM C_REGISTRO60I INTO @V_REGISTROI;
         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_REGISTROI,@P_NRSEQARQMAG);

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


      FETCH NEXT FROM C_REGISTRO60I INTO @V_REGISTROI;
      END;
      CLOSE C_REGISTRO60I;
      DEALLOCATE C_REGISTRO60I;

  FETCH NEXT FROM C_REGISTRO60M INTO @V_REGISTROM,@V_DTREDUCAOZ,@V_NRECFREDZ;
   END;
   CLOSE C_REGISTRO60M;
   DEALLOCATE C_REGISTRO60M;


      DECLARE C_REGISTRO60R CURSOR FOR
         SELECT SUBSTRING(
                '60'+
                'R'+
                SUBSTRING(RTRIM(CONVERT(CHAR, A.DTENTRVENDA, 112)),5,2)+ SUBSTRING(RTRIM(CONVERT(CHAR, A.DTENTRVENDA, 112)),1,4)+
                LEFT(A.CDARVPROD+REPLICATE(' ', 14), 14)+
                RIGHT(REPLICATE('0',13)+LTRIM(REPLACE(CONVERT(CHAR,CONVERT(NUMERIC(13,3),ABS(QTPRODVEND))),'.','')),13)+
                RIGHT(REPLICATE('0',16)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(16,2),ABS(VRUNIT))),'.','')),16)+
                (CASE @P_CDSITUCRT WHEN '3' THEN RIGHT(REPLICATE('0',16)+LTRIM(REPLACE(CONVERT(CHAR, CONVERT(NUMERIC(16,2),ABS(BASECALC))),'.','')),16)
                                           ELSE '0000000000000000' END) +
                (CASE @P_CDSITUCRT WHEN '3' THEN (CASE A.IDTPIMPOSFIS WHEN 'F' THEN 'F   ' ELSE (CASE A.IDTPIMPOSFIS WHEN 'N' THEN 'N   ' ELSE
                                                               (CASE A.IDTPIMPOSFIS WHEN 'I' THEN 'I   ' ELSE
                                                              (CASE A.VRPEALPRODIT WHEN 0.00 THEN 'F   ' ELSE RIGHT(REPLICATE('0',4)+LTRIM(REPLACE(CONVERT(CHAR, A.VRPEALPRODIT),'.', '')),4)END)END) END) END)
                                           ELSE 'N   ' END)+
                 REPLICATE(' ', 54), 1, 126) AS DSARQMAG,
                A.CDARVPROD, A.NMPRODUTO, ISNULL(A.SGUNIDADE, ' ') AS SGUNIDADE, ISNULL(CDCLASFISC, ' ')AS CDCLASFISC
         FROM
             (SELECT I.IDTPIMPOSFIS, VD.DTENTRVENDA, PD.CDARVPROD, PD.NMPRODUTO, PD.SGUNIDADE, PD.CDCLASFISC, SUM(IT.QTPRODVEND) AS QTPRODVEND,
                     AI.VRPEALPRODIT, SUM(IT.QTPRODVEND * ((IT.VRUNITVEND+IT.VRUNITVENDCL)-ISNULL(IT.VRDESITVEND,0)+ ISNULL(IT.VRACRITVEND,0))) AS VRUNIT,
                     SUM(IT.QTPRODVEND * ((IT.VRUNITVEND+IT.VRUNITVENDCL)-ISNULL(IT.VRDESITVEND,0)+ ISNULL(IT.VRACRITVEND,0))) AS BASECALC
                FROM VENDA VD, FILIAL FI, ITEMVENDA IT,  ITVENDAIMPOS AI, PRODUTO PD, IMPOSTO I
               WHERE FI.CDEMPRESA    =  @P_CDEMPRESA      AND
                     FI.CDINSCESTA   =  @P_CDINSCESTA     AND
                     VD.CDFILIAL     = FI.CDFILIAL        AND
                     VD.IDSITUVENDA = 'O'                 AND
                     VD.DTENTRVENDA BETWEEN  @P_DTINICIAL AND  @P_DTFINAL AND
                     VD.CDFILIAL     = IT.CDFILIAL        AND
                     VD.CDCAIXA      = IT.CDCAIXA         AND
                     VD.NRSEQVENDA   = IT.NRSEQVENDA      AND
                     VD.IDVENDEBCONS <> 'S'               AND
                     IT.CDPRODUTO    = PD.CDPRODUTO       AND
                     IT.CDFILIAL     = AI.CDFILIAL        AND
                     IT.CDCAIXA      = AI.CDCAIXA         AND
                     IT.NRSEQVENDA   = AI.NRSEQVENDA      AND
                     IT.NRSEQUITVEND = AI.NRSEQUITVEND    AND
                     AI.CDIMPOSTO    = I.CDIMPOSTO        AND
                     I.IDTPIMPOSFIS  IN('T', 'F', 'N', 'I')
               GROUP BY
                     I.IDTPIMPOSFIS, VD.DTENTRVENDA, PD.CDARVPROD, PD.NMPRODUTO, PD.SGUNIDADE, PD.CDCLASFISC, AI.VRPEALPRODIT ) A;

      OPEN C_REGISTRO60R;
      FETCH NEXT FROM C_REGISTRO60R INTO @V_REGISTROR,
                                         @V_CDARVPROD,
                                         @V_NMPRODUTO,
                                         @V_SGUNIDADE,
                                         @V_CDCLASFISC;
         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_REGISTROR,@P_NRSEQARQMAG);

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

            SELECT @V_MAXCDARVPROD = MAX(CDARVPROD)
              FROM TMP_REGISTRO_PRODUTO
             WHERE CDARVPROD = @V_CDARVPROD;

            IF @V_MAXCDARVPROD IS NULL BEGIN
               INSERT INTO TMP_REGISTRO_PRODUTO(CDTIPOREG, CDARVPROD, DTINICIAL, DTFINAL, CDCLASFISC, NMPRODUTO,
                                                SGUNIDADE, CDSITUTRIBPR, VRALIQIPI, VRALIQICMS, VRPERREDBCIT, VRBASESUBST)
               VALUES('75', @V_CDARVPROD, @P_DTINICIAL, @P_DTFINAL, @V_CDCLASFISC, @V_NMPRODUTO,
                      @V_SGUNIDADE, '040', 0.00, 0.00, 0.00, 0);
            END;
      FETCH NEXT FROM C_REGISTRO60R INTO @V_REGISTROR,
                                         @V_CDARVPROD,
                                         @V_NMPRODUTO,
                                         @V_SGUNIDADE,
                                         @V_CDCLASFISC;
      END;
      CLOSE C_REGISTRO60R;
      DEALLOCATE C_REGISTRO60R;

   SET @P_CONTADOR = @V_CONTADOR;
END;
GO


-- VERSAO --
EXEC ATUVEROBJ 'REGISTRO60_M_A_D_I_R_SINTEGRA','5.30.000';
GO

