CREATE OR REPLACE FUNCTION REGISTRO60_M_A_D_I_R_SINTEGRA(
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA   IN EMPRESA.CDINSCESTA%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_NRSEQUENCIAL IN NOTAFISCAL.NRLANCTONF%TYPE, 
   P_CONTTOTAL    IN VARCHAR2, 
   P_CDIMPOSTO    IN IMPOSTO.CDIMPOSTO%TYPE) RETURN VARCHAR2
AS
   V_CONTREG        NUMBER;
   V_CONTGERAL      NUMBER;
   V_CONTADOR       NUMBER;
   V_STTOTAL        VARCHAR2(160);
   V_CDARVPROD      VARCHAR2(20);
BEGIN
   FOR C_REGISTRO60M IN (SELECT DISTINCT SUBSTR(
                                '60'||
                                'M'||
                                TO_CHAR(DTREDUCAOZ, 'YYYYMMDD')||
                                RPAD(NVL(NRSERIEREDZ,' '), 20, ' ')||
                                RPAD(CDCAIXA, 3, ' ')||
                                '2D'||
                                LPAD(TO_NUMBER(TRIM(NVL(NRPRIMCFREDZ,'0'))), 6, '0')||
                                LPAD(TO_NUMBER(TRIM(NVL(NRULTCFREDZ,'0'))), 6, '0')||
                                LPAD(TO_NUMBER(TRIM(NVL(NRREDREDZ,'0'))), 6, '0')||
                                SUBSTR(LPAD(TRIM(NVL(NRREINIREDZ,'0')), 10, '0'),8, 10)||
                                LTRIM(REPLACE(TO_CHAR(ABS(NVL(RZ.VRVENBRUREDZ,0)),'00000000000000.00'),'.',''))||
                                LTRIM(REPLACE(TO_CHAR(ABS(NVL(VRGTREDZ,0)),'00000000000000.00'),'.',''))||
                                RPAD(' ', 37, ' '), 1 , 126) AS DSARQUMAG, RZ.DTREDUCAOZ, RZ.NRECFREDZ, RZ.CDCAIXA, RZ.NRSERIEREDZ
                           FROM REDUCAOZ RZ , FILIAL FI, CAIXAPERS CX
                          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 CX.CDFILIAL    = FI.CDFILIAL
                            AND CX.CDCAIXAPERS = RZ.CDCAIXA
                            AND VRVENLIQREDZ   >= 0
                            AND CX.IDTIPOSISTEMA <> 'SPS'
                          ORDER BY RZ.DTREDUCAOZ) LOOP
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('09'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REGISTRO60M.DSARQUMAG, P_NRSEQUENCIAL);
      V_CONTADOR  := NVL(V_CONTADOR,0)+1;
      V_CONTREG   := NVL(V_CONTREG,0)+1;
      V_CONTGERAL := NVL(V_CONTGERAL,0)+1;
      FOR C_REGISTRO60A IN (SELECT SUBSTR(
                                   '60'||
                                   'A'||
                                   TO_CHAR(DTREDUCAOZ, 'YYYYMMDD')||
                                   RPAD(NVL(NRSERIEREDZ, ' '), 20, ' ')||
                                   RPAD(LTRIM(SITUTRIB), 4, ' ')||
                                   LTRIM(REPLACE(TO_CHAR(VRACUMULADO,'0000000000.00'),'.',''))||
                                   RPAD(' ', 79, ' '),1,126) AS DSARQMAG
                              FROM (SELECT NRECFREDZ, DTREDUCAOZ, SITUTRIB,
                                           SUM(VRACUMULADO) AS VRACUMULADO,
                                           NRSERIEREDZ
                                      FROM (SELECT IZ.NRECFREDZ, IZ.DTREDUCAOZ,
                                                   TO_CHAR(DECODE(VRALIQIMPRZ,0.00, 0, (VRALIQIMPRZ*100)),'0000') AS SITUTRIB,
                                                   SUM(VRBCALCIMPRZ) VRACUMULADO, NRSERIEREDZ
                                              FROM IMPOSTOREDZ IZ, FILIAL FI, IMPOSTO I,  REDUCAOZ R
                                             WHERE IZ.DTREDUCAOZ BETWEEN C_REGISTRO60M.DTREDUCAOZ AND C_REGISTRO60M.DTREDUCAOZ
                                               AND IZ.CDFILIAL    = FI.CDFILIAL
                                               AND R.CDFILIAL     = IZ.CDFILIAL
                                               AND R.CDCAIXA      = IZ.CDCAIXA
                                               AND IZ.DTREDUCAOZ  = R.DTREDUCAOZ
                                               AND R.NRECFREDZ    = IZ.NRECFREDZ
                                               AND FI.CDEMPRESA   = P_CDEMPRESA
                                               AND FI.CDINSCESTA  = P_CDINSCESTA
                                               AND IZ.CDIMPOSTO   = I.CDIMPOSTO
                                               AND I.IDTPIMPOSFIS = 'T'
                                               AND C_REGISTRO60M.NRECFREDZ = IZ.NRECFREDZ
                                               AND IZ.CDCAIXA     = C_REGISTRO60M.CDCAIXA
                                             GROUP BY IZ.NRECFREDZ, IZ.DTREDUCAOZ, VRALIQIMPRZ, NRSERIEREDZ)
                                     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 C_REGISTRO60M.DTREDUCAOZ AND C_REGISTRO60M.DTREDUCAOZ
                                       AND RZ.CDFILIAL     = FI.CDFILIAL
                                       AND FI.CDEMPRESA    = P_CDEMPRESA
                                       AND FI.CDINSCESTA   = P_CDINSCESTA
                                       AND VRSTRIBREDZ     >= 0
                                       AND RZ.VRVENLIQREDZ >= 0
                                       AND C_REGISTRO60M.NRECFREDZ = RZ.NRECFREDZ
                                       AND RZ.CDCAIXA      = C_REGISTRO60M.CDCAIXA
                                     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 C_REGISTRO60M.DTREDUCAOZ AND C_REGISTRO60M.DTREDUCAOZ
                                       AND RZ.CDFILIAL     = FI.CDFILIAL
                                       AND FI.CDEMPRESA    = P_CDEMPRESA
                                       AND FI.CDINSCESTA   = P_CDINSCESTA
                                       AND VRISENCREDZ     >= 0
                                       AND RZ.VRVENLIQREDZ >= 0
                                       AND C_REGISTRO60M.NRECFREDZ = RZ.NRECFREDZ
                                       AND C_REGISTRO60M.CDCAIXA   = RZ.CDCAIXA
                                     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 C_REGISTRO60M.DTREDUCAOZ AND C_REGISTRO60M.DTREDUCAOZ
                                       AND RZ.CDFILIAL     = FI.CDFILIAL
                                       AND FI.CDEMPRESA    =  P_CDEMPRESA
                                       AND FI.CDINSCESTA   =  P_CDINSCESTA
                                       AND VRNINCIDREDZ    >= 0
                                       AND RZ.VRVENLIQREDZ >= 0
                                       AND C_REGISTRO60M.NRECFREDZ = RZ.NRECFREDZ
                                       AND C_REGISTRO60M.CDCAIXA   = RZ.CDCAIXA
                                     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  C_REGISTRO60M.DTREDUCAOZ AND C_REGISTRO60M.DTREDUCAOZ
                                       AND RZ.CDFILIAL     = FI.CDFILIAL
                                       AND FI.CDEMPRESA    =  P_CDEMPRESA
                                       AND FI.CDINSCESTA   =  P_CDINSCESTA
                                       AND VRTOTCANREDZ    >= 0
                                       AND RZ.VRVENLIQREDZ >= 0
                                       AND C_REGISTRO60M.NRECFREDZ = RZ.NRECFREDZ
                                       AND C_REGISTRO60M.CDCAIXA   = RZ.CDCAIXA
                                     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  C_REGISTRO60M.DTREDUCAOZ AND C_REGISTRO60M.DTREDUCAOZ
                                       AND RZ.CDFILIAL     = FI.CDFILIAL
                                       AND FI.CDEMPRESA    =  P_CDEMPRESA
                                       AND FI.CDINSCESTA   =  P_CDINSCESTA
                                       AND VRTOTDESREDZ    >= 0
                                       AND RZ.VRVENLIQREDZ >= 0
                                       AND C_REGISTRO60M.NRECFREDZ = RZ.NRECFREDZ
                                       AND C_REGISTRO60M.CDCAIXA   = RZ.CDCAIXA
                                     GROUP BY NRECFREDZ, DTREDUCAOZ, NRSERIEREDZ
                                     UNION ALL
                                    SELECT IZ.NRECFREDZ, IZ.DTREDUCAOZ, 'ISS' AS SITUTRIB, SUM(VRBCALCIMPRZ) VRACUMULADO, NRSERIEREDZ
                                      FROM IMPOSTOREDZ IZ, FILIAL FI, IMPOSTO I, REDUCAOZ R
                                     WHERE IZ.DTREDUCAOZ BETWEEN  C_REGISTRO60M.DTREDUCAOZ AND C_REGISTRO60M.DTREDUCAOZ
                                       AND IZ.CDFILIAL    = FI.CDFILIAL
                                       AND IZ.CDFILIAL    = R.CDFILIAL
                                       AND IZ.DTREDUCAOZ  = R.DTREDUCAOZ
                                       AND FI.CDEMPRESA   =  P_CDEMPRESA
                                       AND FI.CDINSCESTA  =  P_CDINSCESTA
                                       AND IZ.CDIMPOSTO   = I.CDIMPOSTO
                                       AND I.IDTPIMPOSFIS = 'I'
                                       AND IZ.NRECFREDZ   = R.NRECFREDZ
                                       AND IZ.CDCAIXA     = R.CDCAIXA
                                       AND C_REGISTRO60M.NRECFREDZ = IZ.NRECFREDZ
                                       AND C_REGISTRO60M.CDCAIXA   = IZ.CDCAIXA
                                     GROUP BY IZ.NRECFREDZ, IZ.DTREDUCAOZ, NRSERIEREDZ)) LOOP
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('09'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REGISTRO60A.DSARQMAG, P_NRSEQUENCIAL);
         V_CONTADOR  := NVL(V_CONTADOR,0)+1;
         V_CONTREG   := NVL(V_CONTREG,0)+1;
         V_CONTGERAL := NVL(V_CONTGERAL,0)+1;
      END LOOP;
      FOR C_REGISTRO60D IN (SELECT SUBSTR(
                                   '60'||
                                   'D'||
                                   TO_CHAR(A.DTENTRVENDA, 'YYYYMMDD')||
                                   RPAD(NVL(NRSERIEREDZ, ' '), 20, ' ')||
                                   RPAD(A.CDARVPROD, 14, ' ')||
                                   LTRIM(REPLACE(TO_CHAR(A.QTPRODVEND,'0000000000.000'),'.',''))||
                                   LTRIM(REPLACE(TO_CHAR(A.VRUNIT,   '00000000000000.00'),'.',''))||
                                   LTRIM(REPLACE(TO_CHAR(DECODE(A.IDTPIMPOSFIS, 'T', DECODE(ROUND(A.VRIMPOSTO,2),0,0,A.BASECALC), 0), '00000000000000.00'),'.',''))||
                                   DECODE(A.IDTPIMPOSFIS, 'F', 'F   ', DECODE(A.IDTPIMPOSFIS, 'N', 'N   ',DECODE(A.IDTPIMPOSFIS, 'I', 'I   ', DECODE(ROUND(A.VRIMPOSTO,2),0,'I   ',LTRIM(REPLACE(TO_CHAR(A.VRPEALPRODIT, '00.00'),'.', ''))))))||
                                   LTRIM(REPLACE(TO_CHAR(DECODE(ROUND(A.VRIMPOSTO,2),0,0,A.VRIMPOSTO), '00000000000.00'),'.',''))||
                                   RPAD(' ', 19, ' '), 1, 126) AS DSARQMAG
                              FROM (SELECT VD.NRSERIEREDZ, I.IDTPIMPOSFIS, VD.DTENTRVENDA, IT.CDCAIXA, PD.CDARVPROD, SUM(IT.QTPRODVEND) AS QTPRODVEND,
                                           AI.VRPEALPRODIT,
                                           NVL(IT.VRUNITVEND,0)+NVL(IT.VRUNITVENDCL,0)-NVL(IT.VRDESITVEND/IT.QTPRODVEND,0)+NVL(IT.VRACRITVEND/IT.QTPRODVEND,0) AS VRUNIT,
                                           SUM(TRUNC((IT.QTPRODVEND * (NVL(IT.VRUNITVEND,0)+NVL(IT.VRUNITVENDCL,0)))-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0),2)) AS BASECALC,
                                           SUM((TRUNC((IT.QTPRODVEND * (NVL(IT.VRUNITVEND,0)+NVL(IT.VRUNITVENDCL,0)))-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0),2))*(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 C_REGISTRO60M.DTREDUCAOZ AND C_REGISTRO60M.DTREDUCAOZ
                                               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
                                               AND C_REGISTRO60M.NRECFREDZ = R.NRECFREDZ
                                               AND C_REGISTRO60M.CDCAIXA   = R.CDCAIXA
                                             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, AI.VRPEALPRODIT,
                                           NVL(IT.VRUNITVEND,0)+NVL(IT.VRUNITVENDCL,0)-NVL(IT.VRDESITVEND/IT.QTPRODVEND,0)+NVL(IT.VRACRITVEND/IT.QTPRODVEND,0)) A)LOOP
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('09'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REGISTRO60D.DSARQMAG, P_NRSEQUENCIAL);
         V_CONTADOR  := NVL(V_CONTADOR,0)+1;
         V_CONTREG   := NVL(V_CONTREG,0)+1;
         V_CONTGERAL := NVL(V_CONTGERAL,0)+1;
      END LOOP;

      FOR C_REGISTRO60I IN (SELECT SUBSTR(
                                   '60'||
                                   'I'||
                                   TO_CHAR(A.DTENTRVENDA, 'YYYYMMDD')||
                                   RPAD(NVL(NRSERIEREDZ, ' '), 20, ' ')||
                                   '2D'||
                                   LPAD(SUBSTR(A.NRCUPOMFIS,LENGTH(A.NRCUPOMFIS)-5,6),6,'0')||
                                   RPAD(A.NRSEQUITVEND, 3, ' ')||
                                   RPAD(A.CDARVPROD, 14, ' ')||
                                   LTRIM(REPLACE(TO_CHAR(ABS(QTPRODVEND),'0000000000.000'),'.',''))||
                                   LTRIM(REPLACE(TO_CHAR(ABS(VRUNIT),    '00000000000.00'),'.',''))||
                                   LTRIM(REPLACE(TO_CHAR(DECODE(A.IDTPIMPOSFIS,'T',DECODE(ROUND(A.VRIMPOSTO,2),0,0,ABS(BASECALC)), 0),  '0000000000.00'),'.',''))||
                                   DECODE(A.IDTPIMPOSFIS, 'F', 'F   ', DECODE(A.IDTPIMPOSFIS, 'N', 'N   ', DECODE(A.IDTPIMPOSFIS, 'I', 'I   ', DECODE(ROUND(A.VRIMPOSTO,2),0,'I   ',LTRIM(REPLACE(TO_CHAR(A.VRPEALPRODIT, '00.00'),'.', ''))))))||
                                   LTRIM(REPLACE(TO_CHAR(DECODE(A.IDTPIMPOSFIS, 'T', DECODE(ROUND(A.VRIMPOSTO,2),0,0,A.VRIMPOSTO), 0), '0000000000.00'),'.',''))||
                                   RPAD(' ', 16, ' '), 1, 126) AS DSARQMAG
                              FROM (SELECT VD.NRSERIEREDZ, I.IDTPIMPOSFIS, VD.DTENTRVENDA, IT.CDCAIXA, PD.CDARVPROD, IT.QTPRODVEND, IT.NRSEQUITVEND,
                                           AI.VRPEALPRODIT,
                                           NVL(IT.VRUNITVEND,0)+NVL(IT.VRUNITVENDCL,0)-NVL(IT.VRDESITVEND/IT.QTPRODVEND,0)+NVL(IT.VRACRITVEND/IT.QTPRODVEND,0) VRUNIT, VD.NRCUPOMFIS,
                                           SUM(TRUNC((IT.QTPRODVEND * (NVL(IT.VRUNITVEND,0)+NVL(IT.VRUNITVENDCL,0)))-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0),2)) AS BASECALC,
                                           SUM((TRUNC((IT.QTPRODVEND * (NVL(IT.VRUNITVEND,0)+NVL(IT.VRUNITVENDCL,0)))-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0),2))*(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 C_REGISTRO60M.DTREDUCAOZ AND C_REGISTRO60M.DTREDUCAOZ
                                               AND V.CDFILIAL     = R.CDFILIAL
                                               AND V.CDCAIXA      = R.CDCAIXA
                                               AND V.DTENTRVENDA  = R.DTREDUCAOZ
                                               --AND V.IDVENDEBCONS <> 'S'
                                               AND R.VRVENLIQREDZ > 0
                                               AND V.NRCUPOMFIS IS NOT NULL
                                               AND C_REGISTRO60M.NRECFREDZ = R.NRECFREDZ
                                               AND C_REGISTRO60M.CDCAIXA = R.CDCAIXA
                                            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
                                     GROUP BY VD.NRSERIEREDZ, I.IDTPIMPOSFIS, VD.DTENTRVENDA, IT.CDCAIXA, PD.CDARVPROD,
                                           IT.QTPRODVEND, IT.NRSEQUITVEND, AI.VRPEALPRODIT, VD.NRCUPOMFIS,
                                           NVL(IT.VRUNITVEND,0)+NVL(IT.VRUNITVENDCL,0)-NVL(IT.VRDESITVEND/IT.QTPRODVEND,0)+NVL(IT.VRACRITVEND/IT.QTPRODVEND,0)) A)LOOP
          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            ('09'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REGISTRO60I.DSARQMAG, P_NRSEQUENCIAL);
          V_CONTADOR  := NVL(V_CONTADOR,0)+1;
          V_CONTREG   := NVL(V_CONTREG,0)+1;
          V_CONTGERAL := NVL(V_CONTGERAL,0)+1;
       END LOOP;
    END LOOP;
    FOR C_REGISTRO60R IN (SELECT SUBSTR(
                                 '60'||
                                 'R'||
                                 A.DTENTRVENDA||
                                 RPAD(A.CDARVPROD, 14, ' ')||
                                 LTRIM(REPLACE(TO_CHAR(ABS(QTPRODVEND),'0000000000.000'),'.',''))||
                                 LTRIM(REPLACE(TO_CHAR(ABS(VRUNIT),    '00000000000000.00'),'.',''))||
                                 LTRIM(REPLACE(TO_CHAR(ABS(BASECALC),  '00000000000000.00'),'.',''))||
                                 DECODE(A.IDTPIMPOSFIS, 'F', 'F   ', DECODE(A.IDTPIMPOSFIS, 'N', 'N   ',
                                                                     DECODE(A.IDTPIMPOSFIS, 'I', 'I   ',
                                                                     DECODE(A.VRPEALPRODIT,0.00, 'F   ',LTRIM(REPLACE(TO_CHAR(A.VRPEALPRODIT, '00.00'),'.', ''))))))||
                                 RPAD(' ', 54, ' '), 1, 126) AS DSARQMAG,
                                 A.CDARVPROD, A.NMPRODUTO, NVL(A.SGUNIDADE, ' ') AS SGUNIDADE, NVL(CDCLASFISC, ' ')AS CDCLASFISC
                            FROM (SELECT I.IDTPIMPOSFIS, TO_CHAR(VD.DTENTRVENDA, 'MMYYYY') AS DTENTRVENDA, PD.CDARVPROD, PD.NMPRODUTO, PD.SGUNIDADE,
                                         NVL(IT.CDCLASFISC,PD.CDCLASFISC) AS CDCLASFISC, SUM(IT.QTPRODVEND) AS QTPRODVEND,
                                         AI.VRPEALPRODIT,
                                         NVL(IT.VRUNITVEND,0)+NVL(IT.VRUNITVENDCL,0)-NVL(IT.VRDESITVEND/IT.QTPRODVEND,0)+NVL(IT.VRACRITVEND/IT.QTPRODVEND,0) AS VRUNIT,
                                         SUM(TRUNC((IT.QTPRODVEND * (NVL(IT.VRUNITVEND,0)+NVL(IT.VRUNITVENDCL,0)))-NVL(IT.VRDESITVEND,0)+NVL(IT.VRACRITVEND,0),2)) AS BASECALC
                                    FROM (SELECT DISTINCT CX.CDCAIXAPERS, FI.CDFILIAL, FI.CDINSCESTA, FI.CDEMPRESA
                                            FROM FILIAL FI, CAIXAPERS CX
                                           WHERE FI.CDEMPRESA    =  P_CDEMPRESA
                                             AND FI.CDINSCESTA   =  P_CDINSCESTA
                                             AND CX.CDFILIAL     =  FI.CDFILIAL
                                             AND CX.IDTIPOSISTEMA <> 'SPS') FI,
                                          PRODUTO PD, IMPOSTO I, ITVENDAIMPOS AI, ITEMVENDA IT, VENDA VD
                                   WHERE VD.CDFILIAL     = FI.CDFILIAL
                                     AND VD.CDCAIXA      = FI.CDCAIXAPERS
                                     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 VD.NRCUPOMFIS   IS NOT NULL
                                     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 VD.NRCUPOMFIS IS NOT NULL
                                     AND I.IDTPIMPOSFIS  IN('T', 'F', 'N', 'I')
                                   GROUP BY I.IDTPIMPOSFIS, TO_CHAR(VD.DTENTRVENDA, 'MMYYYY'), PD.CDARVPROD, PD.NMPRODUTO,
                                         PD.SGUNIDADE, IT.CDCLASFISC, PD.CDCLASFISC, AI.VRPEALPRODIT,
                                         NVL(IT.VRUNITVEND,0)+NVL(IT.VRUNITVENDCL,0)-NVL(IT.VRDESITVEND/IT.QTPRODVEND,0)+NVL(IT.VRACRITVEND/IT.QTPRODVEND,0)) A
                           ORDER BY A.DTENTRVENDA, A.CDARVPROD) LOOP
       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         ('09'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REGISTRO60R.DSARQMAG, P_NRSEQUENCIAL);
       V_CONTADOR  := NVL(V_CONTADOR,0)+1;

       V_CONTREG   := NVL(V_CONTREG,0)+1;
       V_CONTGERAL := NVL(V_CONTGERAL,0)+1;
       SELECT MAX(CDARVPROD)
         INTO V_CDARVPROD
         FROM TMP_REGISTRO_PRODUTO
        WHERE CDARVPROD = C_REGISTRO60R.CDARVPROD;

       IF V_CDARVPROD IS NULL THEN
          INSERT INTO TMP_REGISTRO_PRODUTO(CDTIPOREG, CDARVPROD, DTINICIAL, DTFINAL, CDCLASFISC, NMPRODUTO,
                                           SGUNIDADE, CDSITUTRIBPR, VRALIQIPI, VRALIQICMS, VRPERREDBCIT, VRBASESUBST)
          VALUES('75', C_REGISTRO60R.CDARVPROD, P_DTINICIAL, P_DTFINAL, C_REGISTRO60R.CDCLASFISC, C_REGISTRO60R.NMPRODUTO,
                 C_REGISTRO60R.SGUNIDADE, '040', 0.00, 0.00, 0.00, 0);
       END IF;
    END LOOP;
    /*REGISTRO 61_HOMOLOGACAO FOS*/
    FOR C_REGISTRO61 IN (SELECT DISTINCT SUBSTR(
                             '61'||
                             RPAD(' ', 28, ' ')||
                             TO_CHAR(DTEMISSAO, 'YYYYMMDD')||
                             '02'||
                             RPAD(NVL(CDSERIE,' '),3, ' ')||
                             RPAD(NVL(NRSUBSERIEVND,' '),2, ' ')||
                             LPAD(NVL(NRORDEMVND,'0'),6,'0')||
                             LPAD(NVL(NRORDEMVND,'0'),6,'0')||
                             TRIM(REPLACE(TO_CHAR(ROUND(VRNOTAFISC,2),'00000000000.00'), '.', '')) ||
                             TRIM(REPLACE(TO_CHAR(ROUND(VRBASECALC,2),'00000000000.00'), '.', '')) ||
                             TRIM(REPLACE(TO_CHAR(ROUND(VRIMPOSTO,2),'0000000000.00'),'.',''))      ||
                             TRIM(REPLACE(TO_CHAR(ROUND(VRISENTAS,2),'00000000000.00'), '.', '')) ||
                             TRIM(REPLACE(TO_CHAR(ROUND(VROUTRAS,2), '00000000000.00'), '.', ''))  ||
                             TRIM(REPLACE(TO_CHAR(ROUND(VRALIQUOTA,2), '00.00'), '.', ''))||
                             RPAD(' ',10, ' '),1 , 126) AS DSARQUMAG
                         FROM
                          (SELECT VE.DTENTRVENDA AS DTEMISSAO, SUBSTR(VE.NRSERIEVND,1,3) AS CDSERIE ,SUBSTR(VE.NRSUBSERIEVND,1,2) AS NRSUBSERIEVND ,VE.NRORDEMVND,
                                  SUM((IT.QTPRODVEND * (IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0))) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0)) AS VRNOTAFISC,
                                  SUM(VRDESITVEND) AS VRDESITVEND,
                                  (CASE WHEN VE.NRSERIEVND IS NULL AND VE.NRSUBSERIEVND IS NULL AND NRCUPOMFIS IS NOT NULL THEN 0
                                        WHEN IDTPIMPOSFIS = 'T' AND IP.CDIMPOSTO = P_CDIMPOSTO THEN
                                            SUM((IT.QTPRODVEND * (IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0))) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0))
                                            ELSE 0
                                            END) AS VRBASECALC,
                                  (CASE WHEN VE.NRSERIEVND IS NULL AND VE.NRSUBSERIEVND IS NULL AND NRCUPOMFIS IS NOT NULL THEN 0
                                        WHEN IDTPIMPOSFIS = 'T' AND IP.CDIMPOSTO = P_CDIMPOSTO THEN SUM(VRIMPOPRODIT)
                                        ELSE 0
                                         END) AS VRIMPOSTO,
                                  (CASE WHEN VE.NRSERIEVND IS NULL AND VE.NRSUBSERIEVND IS NULL AND NRCUPOMFIS IS NOT NULL THEN SUM((IT.QTPRODVEND * (IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0))) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0))
                                        ELSE 0
                                         END) AS VRISENTAS,
                                           0 AS VROUTRAS,
                                  (CASE WHEN VE.NRSERIEVND IS NULL AND VE.NRSUBSERIEVND IS NULL AND NRCUPOMFIS IS NOT NULL THEN 0
                                        ELSE VRPEALPRODIT END) AS VRALIQUOTA
                             FROM VENDA VE, ITEMVENDA IT, ITVENDAIMPOS IVI, IMPOSTO IP
                            WHERE VE.CDEMPRESA    = P_CDEMPRESA
                              AND VE.CDFILIAL     = IT.CDFILIAL
                              AND VE.NRSEQVENDA   = IT.NRSEQVENDA
                              AND VE.CDCAIXA      = IT.CDCAIXA
                              AND IT.CDFILIAL     = IVI.CDFILIAL
                              AND IT.CDCAIXA      = IVI.CDCAIXA
                              AND IT.NRSEQUITVEND = IVI.NRSEQUITVEND
                              AND IT.NRSEQVENDA   = IVI.NRSEQVENDA
                              AND VE.DTENTRVENDA BETWEEN P_DTINICIAL AND P_DTFINAL
                              AND VE.IDSITUVENDA  = 'O'
                              AND VE.NRORDEMVND IS NOT NULL
                              AND VE.NRSUBSERIEVND IS NOT NULL
                              AND VE.NRSERIEVND    IS NOT NULL
                              AND IP.CDIMPOSTO    = IVI.CDIMPOSTO
                            GROUP BY VE.DTENTRVENDA, VE.NRSERIEVND, VE.NRSUBSERIEVND, VE.NRORDEMVND, VE.NRSEQVENDA,VE.NRCUPOMFIS, VE.CDFILIAL, VE.CDCAIXA, IDTPIMPOSFIS, VRPEALPRODIT, IP.CDIMPOSTO))LOOP
       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         ('09'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REGISTRO61.DSARQUMAG, P_NRSEQUENCIAL);
       V_CONTADOR  := NVL(V_CONTADOR,0)+1;
       V_CONTREG   := NVL(V_CONTREG,0)+1;
       V_CONTGERAL := NVL(V_CONTGERAL,0)+1;
    END LOOP;
   V_CONTADOR  := 0;
   IF V_CONTREG > 0 THEN
      V_STTOTAL := P_CONTTOTAL||'60'||LPAD(RTRIM(TO_CHAR(V_CONTREG)),8,'0');
   END IF;
   RETURN(V_STTOTAL||';'||TO_CHAR(V_CONTGERAL));
END;