CREATE OR REPLACE FUNCTION ARQUIVO_DIC_61R(
   P_CDIMPOSTO    IN IMPOSTO.CDIMPOSTO%TYPE,
   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_DTPOSICAO    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_CDPAIS       IN ESTADO.CDPAIS%TYPE,
   P_SGESTADO     IN ESTADO.SGESTADO%TYPE,
   P_SEQUENCIAL   IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_CONTADOR     IN NUMBER,
   P_CONTTOTAL    IN VARCHAR2) RETURN VARCHAR2
AS
   V_CONTADOR     NUMBER;
   V_CONTREG      NUMBER;
   V_CONTGERAL    NUMBER;
   V_STTOTAL      VARCHAR2(4000);
BEGIN
   V_CONTADOR := P_CONTADOR;
   V_CONTREG := 0;
   FOR C_REGISTRO61R IN(SELECT '61'||
                               'R'||
                               DTEMISSAO||
                               RPAD(CDARVPROD, 14, ' ')||
                               LTRIM(REPLACE(TO_CHAR(ABS(QTITEMPROD),'000000000.000'),'.',''))||
                               LTRIM(REPLACE(TO_CHAR(ABS(VRCONTITEM),'0000000000000.00'),'.',''))||
                               LTRIM(REPLACE(TO_CHAR(ABS(VRBASECALCIT),'0000000000000.00'),'.',''))||
                               LTRIM(REPLACE(TO_CHAR(ABS(VRALIQITEM),'00.00'),'.',''))||
                               RPAD(' ',112,' ') AS DSARQMAG
                        FROM (SELECT TO_CHAR(N.DTEMISSAO, 'mmyyyy') AS DTEMISSAO,
                                     PP.CDARVPROD, SUM(IP.QTITEMPROD) AS QTITEMPROD,
                                     SUM(IP.VRCONTITEM) AS VRCONTITEM, SUM(VRBASECALCIT) AS VRBASECALCIT,
                                     SUM(VRALIQITEM) AS VRALIQITEM
                              FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, ITEMPRODUTO IP,
                                   ITEMVENDA IV, MOVICLIE MC, MODDOCFISC MD, PRODUTO PP,
                                   ITEMPROIMPOS IT
                              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 NVL(I.IDIMPOSTONF, '1') <> '3'
                                AND N.CDFILIAL        = I.CDFILIAL
                                AND N.NRLANCTONF      = I.NRLANCTONF
                                AND N.IDSTATUS IN ('A','E')
                                AND N.IDENTRSAIDOP    = 'S'
                                AND NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '7'
                                AND 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 (+)
                                AND N.CDMODDOCFISC    = MD.CDMODDOCFISC
                                AND MD.CDTPREGFISC    = '61'
                                AND PP.CDPRODUTO      = IP.CDPRODUTO
                                AND IT.CDFILIAL       = IP.CDFILIAL
                                AND IT.NRLANCTONF     = IP.NRLANCTONF
                                AND IT.NRSEQITPROD    = IP.NRSEQITPROD
                              GROUP BY TO_CHAR(N.DTEMISSAO, 'mmyyyy'), PP.CDARVPROD
                              UNION
                              SELECT TO_CHAR(NF.DTEMISSAO, 'mmyyyy') AS DTEMISSAO,
                                     PP.CDARVPROD, SUM(IP.QTITEMPROD) AS QTITEMPROD,
                                     SUM(IP.VRCONTITEM) AS VRCONTITEM, SUM(VRBASECALCIT) AS VRBASECALCIT,
                                     SUM(VRALIQITEM) AS VRALIQITEM
                              FROM MAPAAPURAC M, NOTAFISCAL NF, IMPOSTONF I, MODDOCFISC MD,
                                   ITEMPRODUTO IP, ITEMPROIMPOS IT, PRODUTO PP
                              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 NVL(I.IDIMPOSTONF, '1') <> '3'
                                AND NF.IDENTRSAIDOP   = 'S'
                                AND NF.IDSTATUS IN ('A','E')
                                AND NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '7'
                                AND NF.CDFILIAL        = I.CDFILIAL
                                AND NF.NRLANCTONF      = I.NRLANCTONF
                                AND NF.CDMODDOCFISC    = MD.CDMODDOCFISC
                                AND MD.CDTPREGFISC     = '61'
                                AND PP.CDPRODUTO      = IP.CDPRODUTO
                                AND IT.CDFILIAL       = IP.CDFILIAL
                                AND IT.NRLANCTONF     = IP.NRLANCTONF
                                AND IT.NRSEQITPROD    = IP.NRSEQITPROD
                              GROUP BY TO_CHAR(NF.DTEMISSAO, 'mmyyyy'), PP.CDARVPROD) )LOOP
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        (LPAD(V_CONTADOR, 10, '0'), C_REGISTRO61R.DSARQMAG, P_SEQUENCIAL);

         V_CONTADOR  := NVL(V_CONTADOR,0)+1;
         V_CONTREG   := NVL(V_CONTREG,0) + 1;
         V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
   END LOOP;

   IF V_CONTREG > 0 THEN
      V_STTOTAL := P_CONTTOTAL||'61R'||LPAD(RTRIM(TO_CHAR(V_CONTREG)),8,'0');
      RETURN(TO_CHAR(LPAD(V_CONTGERAL,10,0))||TO_CHAR(LPAD(V_CONTADOR,10,0))||V_STTOTAL);
   END IF;

   IF V_CONTREG = 0 THEN
      RETURN('0');
   END IF;
END;
/
