CREATE OR REPLACE FUNCTION ARQUIVO_DPI_OUTDEB(
   P_CDIMPOSTO    IN IMPOSTO.CDIMPOSTO%TYPE,
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_CDINSCESTA   IN NOTAFISCAL.CDINSCESTA%TYPE,
   P_CONTADOR     IN NUMBER,
   P_SEQMAGNET    IN VARCHAR)
RETURN NUMBER
AS
   V_CONTADOR     NUMBER;
   V_DIFALIQUOTA  NUMBER;
BEGIN
   V_CONTADOR := NVL(P_CONTADOR,0);

   BEGIN
      SELECT NVL(SUM(I.VRITEMMAPR),0)
        INTO V_DIFALIQUOTA
        FROM MAPAAPURAC M, MAPARECUP R, ITMAPARECUP I
       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  = R.NRSEQUMAPAPU
         AND R.NRSEQUMAPAPU  = I.NRSEQUMAPAPU
         AND I.IDTIPOITMAPR  = 'DA';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_DIFALIQUOTA := 0;
      END;
      IF (V_DIFALIQUOTA <> 0) THEN
         V_CONTADOR := NVL(V_CONTADOR,0)+1;
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
            ('4'||LPAD(V_CONTADOR, 9, '0'),'001~'||V_DIFALIQUOTA||'~',P_SEQMAGNET);
      END IF;
   FOR C_REGISTRO_04 IN(
      SELECT SUBSTR(LPAD(I.NRSEQITMAPR,10,'0'),8,3)||'~'
             ||I.VRITEMMAPR||'~'AS REGISTRO_04
        FROM MAPAAPURAC M, MAPARECUP R, ITMAPARECUP I
       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  = R.NRSEQUMAPAPU
         AND R.NRSEQUMAPAPU  = I.NRSEQUMAPAPU
         AND IDTIPOITMAPR    = 'OD')LOOP

      V_CONTADOR := NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
         ('4'||LPAD(V_CONTADOR, 9, '0'),C_REGISTRO_04.REGISTRO_04,P_SEQMAGNET);
      END LOOP;

RETURN (LPAD(V_CONTADOR, 10, 0));
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_DPI_OUTDEB','5.30.000'); END;
/

