CREATE OR REPLACE FUNCTION ARQUIVO_DAC_31(
   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_PDEC        IN VARCHAR,
   P_CONTADOR    IN NUMBER,
   P_SEQMAGNET   IN VARCHAR,
   P_INDMOV      IN VARCHAR) RETURN NUMBER
AS
   V_REGISTRO_31  VARCHAR2(500);
   V_REGISTRO_33  VARCHAR2(500);
   V_REGISTRO_35  VARCHAR2(500);
   V_CONTADOR     NUMBER;
   V_CONTGERAL    NUMBER;
   V_CREDPRESUMIDO NUMBER;
   V_ICMSANTECIPADO NUMBER;
   V_DATA         VARCHAR2(12);
   V_PARTE1       VARCHAR2(128);
   V_VAZIO        VARCHAR2(42);
   V_PARTE2       VARCHAR2(114);
   V_PARTE3      VARCHAR2(114);
   V_TOTCRED      VARCHAR2(14);
BEGIN
   V_CONTGERAL := 0;
   V_CONTADOR := NVL(P_CONTADOR,0);
   IF (P_PDEC = 'M') THEN
         SELECT TO_CHAR(P_DTINICIAL,'YYYYMM')||TO_CHAR(P_DTFINAL,'YYYYMM'),
                LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(MAX(NVL(MA.VRSLDCRANTE,0)),2),'999999999990.00')),'.',''),14,0)
                ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(MAX(NVL(MA.VRDEDUCAO,0)),2),'999999999990.00')),'.',''),14,0)
                ||MAX(UPPER(RPAD(DECODE(IDTIPOITMAPR,'DD',DSITEMMAPR,' '),100,' '))),                
		LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(MAX(NVL(MA.VRSALDOCRED,0)),2),'999999999990.00')),'.',''),14,0)
		||RPAD(' ',14,' ')
                ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(MAX(NVL(MA.VROUTRDEBI,0)),2),'999999999990.00')),'.',''),14,0), 	
		LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(MAX(NVL(MA.VRESTOCRED,0)),2),'999999999990.00')),'.',''),14,0)
                ||MAX(UPPER(RPAD(DECODE(IDTIPOITMAPR,'EC',DSITEMMAPR,' '),100,' '))),
                LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(MAX(NVL(MA.VRESTODEBI,0)),2),'999999999990.00')),'.',''),14,0)
                ||MAX(UPPER(RPAD(DECODE(IDTIPOITMAPR,'ED',DSITEMMAPR,' '),100,' '))),
                RPAD(' ',14, ' ')
           INTO V_DATA,V_PARTE1,V_VAZIO,V_PARTE2,V_PARTE3,V_TOTCRED
           FROM MAPAAPURAC M, MAPARECUP MA, 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 = MA.NRSEQUMAPAPU
            AND MA.NRSEQUMAPAPU = I.NRSEQUMAPAPU(+);
       IF ((V_PARTE1 IS NOT NULL) OR (V_PARTE2 IS NOT NULL) OR (V_PARTE3 IS NOT NULL)) THEN
          V_CONTADOR := NVL(V_CONTADOR,0)+1;
          INSERT INTO ARQMAGNETICO
             (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
             (LPAD(V_CONTADOR, 10, '0'),'31'||V_DATA||V_PARTE1||V_VAZIO||V_PARTE2||V_VAZIO||V_PARTE3||V_TOTCRED,P_SEQMAGNET);
       ELSE
          V_REGISTRO_31 := '31'||TO_CHAR(P_DTINICIAL,'YYYYMM')||TO_CHAR(P_DTFINAL,'YYYYMM')||RPAD(0,28,0)||RPAD(' ',100,' ')||RPAD(' ',42,' ')||RPAD('0',14,'0')||
                           RPAD(' ',100,' ')||RPAD(' ',42,' ')||RPAD('0',14,'0')||RPAD(' ',100,' ')||
                           RPAD(' ',14,' ');
          V_CONTADOR := NVL(V_CONTADOR,0)+1;
          INSERT INTO ARQMAGNETICO
             (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
             (LPAD(V_CONTADOR, 10, '0'),V_REGISTRO_31,P_SEQMAGNET);
       END IF;
   END IF;
   IF (P_PDEC = 'M') THEN
      IF P_INDMOV = 'S' THEN
         SELECT SUM(ROUND(VRITEMMAPR,2))
           INTO V_CREDPRESUMIDO
           FROM ITMAPARECUP I, MAPAAPURAC M
          WHERE IDTIPOITMAPR = 'OC'
            AND SUBSTR(DSITEMMAPR,1,17) = 'CREDITO PRESUMIDO'
            AND 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;

         SELECT SUM(ROUND(VRITEMMAPR,2))
           INTO V_ICMSANTECIPADO
           FROM ITMAPARECUP I, MAPAAPURAC M
          WHERE IDTIPOITMAPR = 'OC'
            AND SUBSTR(DSITEMMAPR,1,15) = 'ICMS ANTECIPADO'
            AND 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;

         BEGIN
            SELECT '33'
                   ||TO_CHAR(P_DTINICIAL,'YYYYMM')
                   ||TO_CHAR(P_DTFINAL,'YYYYMM')
                   ||RPAD(' ',14,' ')
                   ||LPAD(REPLACE(LTRIM(NVL(TO_CHAR(ROUND(SUM(VROUTRDEBI),2),'999999999990.00'),0)),'.',''),14,0)
                   ||RPAD(' ',100,' ')
                   ||LPAD(REPLACE(LTRIM(NVL(TO_CHAR(ROUND(SUM(V_ICMSANTECIPADO),2),'999999999990.00'),0)),'.',''),14,0)
                   ||LPAD(REPLACE(LTRIM(NVL(TO_CHAR(ROUND(SUM(V_CREDPRESUMIDO),2),'999999999990.00'),0)),'.',''),14,0) --VROUTRCRED
                   ||RPAD(0,84,0)
                   ||RPAD(' ',100,' ')
              INTO V_REGISTRO_33
              FROM MAPAAPURAC M, MAPARECUP I
             WHERE M.DTINIPERIODO >= P_DTINICIAL
               AND M.DTFIMPERIODO <= P_DTFINAL
               AND M.CDIMPOSTO = P_CDIMPOSTO
               AND M.CDEMPRESA = P_CDEMPRESA
               AND M.CDINSCESTA = P_CDINSCESTA
               AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU;
         EXCEPTION WHEN NO_DATA_FOUND THEN
            V_REGISTRO_33 := '33'||TO_CHAR(P_DTINICIAL,'YYYYMM')||TO_CHAR(P_DTFINAL,'YYYYMM')||
                                 RPAD(' ',14,' ')||RPAD('0',14,'0')||RPAD(' ',100,' ')||
                                 RPAD('0',112,'0')||RPAD(' ',100,' ');

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

      END IF;
   END IF;
   IF (P_PDEC = 'Q') THEN
      V_REGISTRO_35 := '35'
                       ||TO_CHAR(P_DTINICIAL,'YYYYMM')
                       ||TO_CHAR(P_DTFINAL,'YYYYMM')
                       ||RPAD('0',70,'0')
                       ||RPAD(' ',14,' ');
         V_CONTADOR := NVL(V_CONTADOR,0)+1;
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
            (LPAD(V_CONTADOR, 10, '0'),V_REGISTRO_35,P_SEQMAGNET);
   END IF; 
   RETURN(LPAD(V_CONTADOR,10,0));
END;
/

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

