CREATE OR REPLACE FUNCTION ARQUIVO_61_GIM_PB(
   P_CDIMPOSTO    IN IMPOSTO.CDIMPOSTO%TYPE,
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA   IN EMPRESA.CDINSCESTA%TYPE,
   P_CDINSCESTAST IN NOTAFISCAL.CDINSCESTAST%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_SEQMAGNET    IN VARCHAR,
   P_CONTADOR     IN NUMBER) RETURN VARCHAR
AS
   V_CONTADOR     NUMBER;
   V_CONTREG      NUMBER;
   V_CONTGERAL    NUMBER;
BEGIN
   V_CONTREG  := 0;
   V_CONTADOR := P_CONTADOR;
    FOR C_REGISTRO61 IN(SELECT SUBSTR('61'||
                                     RPAD(' ', 14, ' ')||
                                     RPAD(' ', 14, ' ')||
                                     TO_CHAR(NF.DTEMISSAO, 'yyyymmdd')||
                                     RPAD('02', 2, ' ')||
                                     RPAD(DECODE(TRIM(NVL(NF.CDSERIE,' ')),'-',' ',NVL(NF.CDSERIE,' ')),5,' ')||
                                     LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0')||
                                     LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0')||
                                     LTRIM(REPLACE(TO_CHAR(NF.VRNOTAFISC, '00000000000.00'),'.',''))||
                                     LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.',''))||
                                     LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF),'0000000000.00'),'.',''))||
                                     LTRIM(REPLACE(TO_CHAR(ABS(NF.VRISENTRIB),'00000000000.00'),'.',''))||
                                     LTRIM(REPLACE(TO_CHAR(ABS(NF.VROUTRCRED),'00000000000.00'),'.',''))||
                                     LTRIM(REPLACE(TO_CHAR(ABS(NF.VRALIQNF),'00.00'),'.',''))||
                                     RPAD(' ', 1, ' '),1, 126) AS DSARQMAG, NF.DTEMISSAO
                       FROM
                          (SELECT NF.NRLANCTONF, NF.CDSERIE, NF.DTEMISSAO, I.VRALIQNF, NF.CDMODDOCFISC, NF.VRNOTAFISC,
                                   SUM(I.VRCONTABILNF) AS VRCONTABILNF, NF.NRNOTAFISC,
                                   SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                   SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VRIMPOSTONF,
                                   SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0))   AS VROUTRCRED,
                                   SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,DECODE(SUBSTR(I.CDSITUTRIBNF,2,2),'20',I.VRCONTABILNF-I.VRBASECALCNF,0))) AS VRISENTRIB
                            FROM MAPAAPURAC M, NOTAFISCAL NF, IMPOSTONF I, MODDOCFISC MD, IMPOSTO IM
                            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       = IM.CDIMPOSTO                  AND
                                  IM.CDIMPOSTONFE   = 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.CDINSCESTAST = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND NF.CDINSCESTAST IS NULL)) AND
                                  NF.CDMODDOCFISC   = MD.CDMODDOCFISC               AND
                                  MD.CDTPREGFISC    = '61'
                            GROUP BY NF.NRLANCTONF, NF.CDSERIE, NF.DTEMISSAO, I.VRALIQNF, NF.CDMODDOCFISC, NF.VRNOTAFISC,
                                     NF.NRNOTAFISC) NF
                       ORDER BY NF.DTEMISSAO) LOOP

      V_CONTADOR  := NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('61'||LPAD(V_CONTADOR, 8, '0'), C_REGISTRO61.DSARQMAG, P_SEQMAGNET);

      V_CONTREG   := V_CONTREG + 1;
      V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
   END LOOP;
   /*
   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, IMPOSTO IM
                              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       = IM.CDIMPOSTO
                                AND IM.CDIMPOSTONFE    = 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, IMPOSTO IM
                              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       = IM.CDIMPOSTO
                                AND IM.CDIMPOSTONFE   = 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

      V_CONTADOR  := NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('61'||LPAD(V_CONTADOR, 8, '0'), C_REGISTRO61R.DSARQMAG,P_SEQMAGNET);

         V_CONTREG   := NVL(V_CONTREG,0) + 1;
         V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
   END LOOP;
   */
   IF V_CONTREG > 0 THEN
      RETURN(TO_CHAR(LPAD(V_CONTGERAL,10,0))||TO_CHAR(LPAD(V_CONTADOR,10,0)));
   END IF;
   IF  V_CONTREG = 0 THEN
      RETURN('0');
   END IF;
END;
/