CREATE OR REPLACE FUNCTION ARQUIVO_DAC_41(
   P_CDEMPRESA   IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA  IN NOTAFISCAL.CDINSCESTA%TYPE,
   P_CDIMPOSTO   IN IMPOSTO.CDIMPOSTO%TYPE,
   P_DTINICIAL   IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL     IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_GUIA        IN VARCHAR,
   P_AIDF        IN VARCHAR,
   P_CONTADOR    IN NUMBER,
   P_SEQMAGNET   IN VARCHAR,
   P_INDMOV      IN VARCHAR) RETURN NUMBER
AS
   V_CDIMPOSTRIB    VARCHAR2(2);
   V_CONTADOR       NUMBER;
   V_CONTGERAL      NUMBER;
   V_MINNOTA        VARCHAR2(20);
   V_MAXNOTA        VARCHAR2(20);
   V_CANCELADAS     VARCHAR2(10);
BEGIN
   V_CONTADOR  := P_CONTADOR;
   IF P_INDMOV = 'S' THEN

      FOR C_REGISTRO_41 IN(
         SELECT '41'
                ||TO_CHAR(P_DTINICIAL,'YYYYMM')
                ||TO_CHAR(P_DTFINAL,'YYYYMM')
                ||RPAD(RTRIM(NVL(CDRECEGUIA,0)),5,0)
                ||LPAD(NVL(TO_CHAR(DTVENCGUIA,'YYYYMMDD'),0),8,0)
                ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(VRTOTAGUIA,2),'999999999990.00')),'.',''),14,0) AS REGISTRO_41
           FROM MAPAAPURAC M
          WHERE M.CDEMPRESA = P_CDEMPRESA
            AND M.CDINSCESTA = P_CDINSCESTA
            AND M.CDIMPOSTO = P_CDIMPOSTO
            AND M.DTINIPERIODO >= P_DTINICIAL
            AND M.DTFIMPERIODO <= P_DTFINAL
            and CDRECEGUIA is not null
          UNION ALL
          SELECT '41'
                ||TO_CHAR(P_DTINICIAL,'YYYYMM')
                ||TO_CHAR(P_DTFINAL,'YYYYMM')
                ||RPAD(RTRIM(NVL(CDRECEITAGUIA,0)),5,0)
                ||LPAD(NVL(TO_CHAR(DTVENCGUIACOMPL,'YYYYMMDD'),0),8,0)
                ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(VRRECEITAGUIA,2),'999999999990.00')),'.',''),14,0) AS REGISTRO_41
           FROM MAPAAPURAC M, GUIACOMPLMAPA G
          WHERE M.CDEMPRESA = P_CDEMPRESA
            AND M.CDINSCESTA = P_CDINSCESTA
            AND M.CDIMPOSTO = P_CDIMPOSTO
            AND M.DTINIPERIODO >= P_DTINICIAL
            AND M.DTFIMPERIODO <= P_DTFINAL
            AND G.NRSEQUMAPAPU = M.NRSEQUMAPAPU) LOOP

             V_CONTADOR := NVL(V_CONTADOR,0) +1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              (LPAD(V_CONTADOR, 10, '0'),C_REGISTRO_41.REGISTRO_41,P_SEQMAGNET);

      END LOOP;
   END IF;

   FOR C_REGISTRO_42 IN(
      SELECT '42'
             ||LPAD(RTRIM(NVL(CDRECEGUIA,0)),5,0)
             ||NVL(TO_CHAR(DTVENCGUIA,'YYYYMMDD'),'00000000')
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(VRTOTAIMPOS,2),'999999999990.00')),'.',''),14,0)
             ||LPAD(NVL(C.CDBANCFORN,0),3,0)
             ||RPAD(NVL(B.NMBANCO,' '),40,' ') AS REGISTRO_42
        FROM MAPAAPURAC M, IMPOSTO I, CCORFORN C, BANCO B
       WHERE M.CDEMPRESA = P_CDEMPRESA
         AND M.CDINSCESTA = P_CDINSCESTA
         AND M.CDIMPOSTO = P_CDIMPOSTO
         AND M.DTINIPERIODO >= ADD_MONTHS(P_DTINICIAL, -1)
         AND M.DTFIMPERIODO <= ADD_MONTHS(P_DTFINAL, -1)
         AND M.CDIMPOSTO = I.CDIMPOSTONFE
         AND I.CDFORNECED = C.CDFORNECED
         AND C.CDBANCFORN = B.CDBANCO) LOOP
      V_CONTADOR := NVL(V_CONTADOR,0) +1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(V_CONTADOR, 10, '0'),C_REGISTRO_42.REGISTRO_42,P_SEQMAGNET);
   END LOOP;
   FOR C_REGISTRO_43 IN(
      SELECT SUBSTR(LPAD(REPLACE(REPLACE(REPLACE(NVL(F.NRINSESTFORN, 0),'.',''),'-',''),'ISENT','ISENTO'),9,0),1,9)
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRBASECALCNF),2),'999999999990.00')),'.',''),14,0)
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRIMPOSTONF),2),'999999999990.00')),'.',''),14,0) AS REGISTRO_43,
             LPAD(0,32,0)  AS VAZIO
       FROM IMPOSTONF I, NOTAFISCAL N, MAPAAPURAC M,
            FORNECEDOR F
      WHERE M.CDEMPRESA = P_CDEMPRESA
        AND M.CDINSCESTA = P_CDINSCESTA
        AND M.CDIMPOSTO = '80'
        AND M.DTINIPERIODO >= P_DTINICIAL
        AND M.DTFIMPERIODO <= P_DTFINAL
        AND M.CDFILIAL = I.CDFILIAL
        AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
        AND I.NRLANCTONF = N.NRLANCTONF
        AND SUBSTR(I.CDFISCOPERNF,1,1) = '2'
        AND N.CDFORNECED = F.CDFORNECED
      GROUP BY F.NRINSESTFORN) LOOP
      IF (C_REGISTRO_43.REGISTRO_43 IS NOT NULL) THEN
         V_CONTADOR := NVL(V_CONTADOR,0) +1;
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
            (LPAD(V_CONTADOR, 10, '0'),'43'||C_REGISTRO_43.REGISTRO_43||C_REGISTRO_43.VAZIO,P_SEQMAGNET);
      END IF;
   END LOOP;
   FOR C_REGISTRO_45 IN(
      SELECT '45'
             ||P_AIDF
             ||LPAD(NVL(DECODE(N.CDMODDOCFISC,'01','  ',N.CDMODDOCFISC),' '),2,' ')
             ||MAX(LPAD(SUBSTR(ES.CDESPECDOCFISC,8,10),3,0))
             ||LPAD(NVL(N.CDSERIE,' '),3,' ')
             ||LPAD(' ',2,' ')
             ||LPAD(NVL(MIN(N.NRNOTAFISC),0),6,0)
             ||LPAD(NVL(MAX(N.NRNOTAFISC),0),6,0) AS REGISTRO_45,
             MAX(ES.CDESPECDOCFISC) AS ESPECIE,
             LPAD(NVL(N.CDSERIE,' '),2,' ')     AS CDSERIE,
             LPAD(NVL(N.CDMODDOCFISC,' '),2,' ')AS CDMODDOCFISC,
             LPAD(NVL(MIN(N.NRNOTAFISC),0),6,0) AS MENOR_NT,
             LPAD(NVL(MAX(N.NRNOTAFISC),0),6,0) AS MAIOR_NT
        FROM NOTAFISCAL N, IMPOSTONF I, MAPAAPURAC M,ESPECDOCFISC ES
       WHERE M.CDEMPRESA = P_CDEMPRESA
         AND M.CDINSCESTA = P_CDINSCESTA
         AND M.CDIMPOSTO = P_CDIMPOSTO
         AND M.DTINIPERIODO >= P_DTINICIAL
         AND M.DTFIMPERIODO <= P_DTFINAL
         AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
         AND I.CDFILIAL = N.CDFILIAL
         AND I.NRLANCTONF = N.NRLANCTONF
         AND N.CDMODDOCFISC = ES.CDMODDOCFISC
         AND N.IDENTRSAIDOP = 'S'
         AND N.IDSTATUS IN ('A','E','C')
       GROUP BY N.CDSERIE,N.CDMODDOCFISC) LOOP
         BEGIN
            SELECT LPAD(NVL(COUNT(*),0),6,0) AS CANCELADAS
              INTO V_CANCELADAS
              FROM NOTAFISCAL N,ESPECDOCFISC ES
             WHERE CDSERIE = C_REGISTRO_45.CDSERIE
               AND N.CDMODDOCFISC = C_REGISTRO_45.CDMODDOCFISC
               AND N.CDMODDOCFISC = ES.CDMODDOCFISC
               AND ES.CDESPECDOCFISC = C_REGISTRO_45.ESPECIE
               AND NRNOTAFISC BETWEEN C_REGISTRO_45.MENOR_NT  AND C_REGISTRO_45.MAIOR_NT
               AND IDENTRSAIDOP = 'S'
               AND IDSTATUS = 'C'
               AND DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL
               AND CDINSCESTA = P_CDINSCESTA
               AND CDEMPRESA = P_CDEMPRESA;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_CANCELADAS := LPAD(0,6,0);
         END;
           V_CONTADOR := NVL(V_CONTADOR,0) +1;
           INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
           VALUES
           (LPAD(V_CONTADOR, 10, '0'),C_REGISTRO_45.REGISTRO_45||V_CANCELADAS,P_SEQMAGNET);
           V_CANCELADAS := '0';
      FOR C_REGISTRO_46 IN(
         SELECT '46'
                ||P_AIDF
                ||LPAD(NVL(DECODE(N.CDMODDOCFISC,'01','  ',N.CDMODDOCFISC),' '),2,' ')
                ||LPAD(SUBSTR(ES.CDESPECDOCFISC,8,10),3,0)
                ||LPAD(NVL(CDSERIE,' '),3,' ')
                ||LPAD(' ',2,' ')
                ||LPAD(NVL(NRNOTAFISC,0),6,0)
                ||LPAD(NVL(NRNOTAFISC,0),6,0) AS REGISTRO_46
           FROM NOTAFISCAL N,ESPECDOCFISC ES
          WHERE CDSERIE = C_REGISTRO_45.CDSERIE
            AND N.CDMODDOCFISC = C_REGISTRO_45.CDMODDOCFISC
            AND N.CDMODDOCFISC = ES.CDMODDOCFISC
            AND ES.CDESPECDOCFISC = C_REGISTRO_45.ESPECIE
            AND NRNOTAFISC BETWEEN C_REGISTRO_45.MENOR_NT  AND C_REGISTRO_45.MAIOR_NT
            AND IDENTRSAIDOP = 'S'
            AND IDSTATUS = 'C'
            AND DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL
            AND CDINSCESTA = P_CDINSCESTA
            AND CDEMPRESA = P_CDEMPRESA) LOOP
            V_CONTADOR := NVL(V_CONTADOR,0) +1;
            INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
            (LPAD(V_CONTADOR, 10, '0'),C_REGISTRO_46.REGISTRO_46,P_SEQMAGNET);
      END LOOP;
   END LOOP;
   RETURN(LPAD(V_CONTADOR,10,0));
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_DAC_41','5.65.000'); END;
/

