CREATE OR REPLACE FUNCTION ARQUIVO_DAC_21(
   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)
RETURN NUMBER
AS
   V_CONTADOR      NUMBER;
   V_CONTGERAL     NUMBER;
   V_REGNULO_21    VARCHAR2(500);
   V_REGNULO_22    VARCHAR2(500);
   V_REGISTRO_23   VARCHAR2(500);
   V_REGNULO_24     VARCHAR2(500);
   V_REGNULO_25    VARCHAR2(500);
BEGIN
   V_REGNULO_21 := '0';
   V_REGNULO_22 := '0';
   V_REGNULO_24 := '0';
   V_REGNULO_25 := '0';
   V_CONTADOR := NVL(P_CONTADOR,0);

   FOR C_REGISTRO_21 IN(
      SELECT '21'
             ||TO_CHAR(P_DTINICIAL,'YYYYMM')
             ||TO_CHAR(P_DTFINAL,'YYYYMM')
             ||I.CDFISCOPERNF
             ||LPAD(REPLACE(LTRIM(TO_CHAR(SUM(ROUND(VRCONTABILNF,2)),'999999999990.00')),'.',''),14,'0')
             ||LPAD(REPLACE(LTRIM(TO_CHAR(SUM(ROUND(VRBASECALCNF,2)),'999999999990.00')),'.',''),14,'0')
             ||LPAD(REPLACE(LTRIM(TO_CHAR(SUM(ROUND(VRIMPOSTONF,2)),'999999999990.00')),'.',''),14,'0')
             ||LPAD(REPLACE(LTRIM(TO_CHAR(SUM(DECODE(I.CDFISCALNF,'2', ROUND(I.VRCONTABILNF,2), 0)),'999999999990.00')),'.',''),14,'0')
             ||LPAD(REPLACE(LTRIM(TO_CHAR(SUM(DECODE(I.CDFISCALNF,'3', ROUND(I.VRCONTABILNF,2), 0)),'999999999990.00')),'.',''),14,'0')
             ||LPAD(0,14,0) AS REGISTRO_21
       FROM IMPOSTONF I, NOTAFISCAL N, MAPAAPURAC M
      WHERE I.CDFILIAL = N.CDFILIAL
        AND I.NRLANCTONF = N.NRLANCTONF
        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 I.NRSEQUMAPAPU = M.NRSEQUMAPAPU
        AND ((IDIMPOSTONF <> '3') OR (IDIMPOSTONF IS NULL))
        AND N.CDMODDOCFISC NOT IN ('99','98')
        AND N.IDENTRSAIDOP = 'E'
      GROUP BY I.CDFISCOPERNF)LOOP
        V_CONTADOR := NVL(V_CONTADOR,0) +1;
        INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
           VALUES
           (LPAD(V_CONTADOR, 10, '0'),C_REGISTRO_21.REGISTRO_21,P_SEQMAGNET);
           V_REGNULO_21 := '1';
   END LOOP;
   /*
   IF (V_REGNULO_21 = '0') THEN
      V_REGNULO_21 := '21'||TO_CHAR(P_DTINICIAL,'YYYYMM')||TO_CHAR(P_DTFINAL,'YYYYMM')||RPAD('0',88,'0');
      V_CONTADOR := NVL(V_CONTADOR,0) +1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
         (LPAD(V_CONTADOR, 10, '0'),V_REGNULO_21,P_SEQMAGNET);
   END IF;
   */
   --IF (P_PDEC = 'M') THEN
      FOR C_REGISTRO_22 IN(
         SELECT '22'
                ||TO_CHAR(P_DTINICIAL,'YYYYMM')
                ||TO_CHAR(P_DTFINAL,'YYYYMM')
                ||LPAD(NVL(I.CDFISCOPERNF,0),4,0)
                ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRCONTABILNF),2),'999999999990.00')),'.',''),14,'0')
                ||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')
                ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(DECODE(I.CDFISCALNF,'2', I.VRCONTABILNF, 0)),2),'999999999990.00')),'.',''),14,'0')
                ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(DECODE(I.CDFISCALNF,'3', I.VRCONTABILNF, 0)),2),'999999999990.00')),'.',''),14,'0')
                ||LPAD('0',14,'0') AS REGISTRO_22
           FROM IMPOSTONF I, NOTAFISCAL N, MAPAAPURAC M
          WHERE I.CDFILIAL = N.CDFILIAL
            AND I.NRLANCTONF = N.NRLANCTONF
            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 I.NRSEQUMAPAPU = M.NRSEQUMAPAPU
            AND N.CDMODDOCFISC NOT IN ('99','98')
            AND N.IDENTRSAIDOP = 'S'
          GROUP BY I.CDFISCOPERNF) LOOP
            V_CONTADOR := NVL(V_CONTADOR,0)+1;
            INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
               (LPAD(V_CONTADOR, 10, '0'),C_REGISTRO_22.REGISTRO_22,P_SEQMAGNET);
               V_REGNULO_22 := '1';
      END LOOP;
      /*
      IF (V_REGNULO_22 = '0') THEN
         V_REGNULO_22 := '22'||RPAD('0',100,'0');
         V_CONTADOR := NVL(V_CONTADOR,0)+1;
         INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
         (LPAD(V_CONTADOR, 10, '0'),V_REGNULO_22,P_SEQMAGNET);
      END IF;
      */

   --END IF;
   IF (P_PDEC = 'Q') THEN
      V_REGISTRO_23 := '23'||TO_CHAR(P_DTINICIAL,'YYYYMM')||TO_CHAR(P_DTFINAL,'YYYYMM')||
                        RPAD('0',14,'0')||RPAD(' ',14,' ')||RPAD(' ',14,' ');
      V_CONTADOR := NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
         (LPAD(V_CONTADOR, 10, '0'),V_REGISTRO_23,P_SEQMAGNET);
   END IF;
   IF (P_PDEC = 'Q') THEN
      FOR C_REGISTRO_24 IN(
         SELECT  '24'
                 ||TO_CHAR(P_DTINICIAL,'YYYYMM')
                 ||TO_CHAR(P_DTFINAL,'YYYYMM')
                 ||I.CDFISCOPERNF
                 ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(VRCONTABILNF),2),'999999999990.00')),'.',''),14,'0')
                 ||RPAD('0',14,'0')
                 ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(DECODE(I.CDFISCALNF,'3', I.VRCONTABILNF, 0)),2),'999999999990.00')),'.',''),14,'0')
                 ||RPAD('0',14,'0')
                 ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(I.VRPERREDBCNF),2),'999999999990.00')),'.',''),14,'0')
                 ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(DECODE(IDSTATUS, 'C', VRCONTABILNF, 0)),2),'999999999990.00')),'.',''),14,'0') AS REGISTRO_24
             FROM IMPOSTONF I, NOTAFISCAL N, MAPAAPURAC M
            WHERE I.CDFILIAL = N.CDFILIAL
              AND I.NRLANCTONF = N.NRLANCTONF
              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 I.NRSEQUMAPAPU = M.NRSEQUMAPAPU
              AND N.CDMODDOCFISC IN ('03','07')
              AND N.IDENTRSAIDOP = 'S'
            GROUP BY I.CDFISCOPERNF) LOOP
               V_CONTADOR := NVL(V_CONTADOR,0)+1;
               INSERT INTO ARQMAGNETICO
                  (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                  VALUES
                  (LPAD(V_CONTADOR, 10, '0'),C_REGISTRO_24.REGISTRO_24,P_SEQMAGNET);
                  V_REGNULO_24 := '1';
      END LOOP;
      /*
      IF (V_REGNULO_24 = '0') THEN
         V_REGNULO_24 := '24'||TO_CHAR(P_DTINICIAL,'YYYYMM')||TO_CHAR(P_DTFINAL,'YYYYMM')||RPAD('0',88,'0');
         V_CONTADOR := NVL(V_CONTADOR,0)+1;
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
            (LPAD(V_CONTADOR, 10, '0'),V_REGNULO_24,P_SEQMAGNET);
      END IF;
      */
   END IF;
   FOR C_REGISTRO_25 IN (
      SELECT '25'
             ||TO_CHAR(P_DTINICIAL,'YYYYMM')
             ||TO_CHAR(P_DTFINAL,'YYYYMM')
             ||'4' AS PARTE1,
             LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NF.VRCONTABILNF),2),'999999999990.00')),'.',''),14,'0')
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NF.VRBASECALCNF),2),'999999999990.00')),'.',''),14,'0')
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NF.VRIMPOSTONF),2),'999999999990.00')),'.',''),14,'0')
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NF.VRISENTO),2),'999999999990.00')),'.',''),14,'0')
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NF.VROUTROS),2),'999999999990.00')),'.',''),14,'0') AS REGISTRO_25,
             LPAD('0',14,'0') AS VAZIO
       FROM
            (SELECT SUM(VRCONTABILNF) AS VRCONTABILNF,
                    SUM(VRBASECALCNF) AS VRBASECALCNF,
                    SUM(VRIMPOSTONF) AS VRIMPOSTONF,
                    SUM(DECODE(I.CDFISCALNF,'2', I.VRCONTABILNF, 0)) AS VRISENTO,
                    SUM(DECODE(I.CDFISCALNF,'3', I.VRCONTABILNF, 0)) AS VROUTROS
              FROM IMPOSTONF I, NOTAFISCAL N, MAPAAPURAC M, FORNECEDOR F, ENDEFORN EF
             WHERE I.CDFILIAL = N.CDFILIAL
               AND I.NRLANCTONF = N.NRLANCTONF
               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 I.NRSEQUMAPAPU = M.NRSEQUMAPAPU
               AND N.CDMODDOCFISC IN ('03','07')
               AND N.IDENTRSAIDOP = 'E'
               AND N.CDFORNECED = F.CDFORNECED
               AND F.CDFORNECED = EF.CDFORNECED
               AND EF.SGESTADO  IN ('RS', 'SC', 'PR', 'SP','RJ','MG')
             GROUP BY I.CDFISCOPERNF
            UNION ALL
            SELECT SUM(VRCONTABILNF) AS VRCONTABILNF,
                   SUM(VRBASECALCNF) AS VRBASECALCNF,
                   SUM(VRIMPOSTONF) AS VRIMPOSTONF,
                   SUM(DECODE(I.CDFISCALNF,'2', I.VRCONTABILNF, 0)) AS VRISENTO,
                   SUM(DECODE(I.CDFISCALNF,'3', I.VRCONTABILNF, 0)) AS VROUTROS
              FROM IMPOSTONF I, NOTAFISCAL N, MAPAAPURAC M,
                   CLIENTE C, ENDECLIE EC
             WHERE I.CDFILIAL = N.CDFILIAL
               AND I.NRLANCTONF = N.NRLANCTONF
               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 I.NRSEQUMAPAPU = M.NRSEQUMAPAPU
               AND N.CDMODDOCFISC IN ('03','07')
               AND N.IDENTRSAIDOP = 'E'
               AND N.CDCLIENTE = C.CDCLIENTE
               AND C.CDCLIENTE = EC.CDCLIENTE
               AND EC.SGESTADO  IN ('RS', 'SC', 'PR', 'SP','RJ','MG')
             GROUP BY I.CDFISCOPERNF
            UNION ALL
            SELECT SUM(VRCONTABILNF) AS VRCONTABILNF,
                   SUM(VRBASECALCNF) AS VRBASECALCNF,
                   SUM(VRIMPOSTONF) AS VRIMPOSTONF,
                   SUM(DECODE(I.CDFISCALNF,'2', I.VRCONTABILNF, 0)) AS VRISENTO,
                   SUM(DECODE(I.CDFISCALNF,'3', I.VRCONTABILNF, 0)) AS VROUTROS
              FROM IMPOSTONF I, NOTAFISCAL N, MAPAAPURAC M,
                   FILIAL F, ENDEFILI EF
             WHERE I.CDFILIAL = N.CDFILIAL
               AND I.NRLANCTONF = N.NRLANCTONF
               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 I.NRSEQUMAPAPU = M.NRSEQUMAPAPU
               AND N.CDMODDOCFISC IN ('03','07')
               AND N.IDENTRSAIDOP = 'E'
               AND N.CDFILIRECE = F.CDFILIAL
               AND F.CDFILIAL = EF.CDFILIAL
               AND EF.SGESTADO  IN ('RS', 'SC', 'PR', 'SP','RJ','MG')
             GROUP BY I.CDFISCOPERNF ) NF) LOOP
         IF (C_REGISTRO_25.REGISTRO_25 IS NOT NULL) THEN
            V_CONTADOR := NVL(V_CONTADOR,0)+1;
            INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
               (LPAD(V_CONTADOR, 10, '0'),C_REGISTRO_25.PARTE1||C_REGISTRO_25.REGISTRO_25||C_REGISTRO_25.VAZIO,P_SEQMAGNET);
            V_REGNULO_25 := '1';
         END IF;
   END LOOP;
   /*
   IF (V_REGNULO_25 = '0') THEN
      V_REGNULO_25 := '25'||TO_CHAR(P_DTINICIAL,'YYYYMM')||TO_CHAR(P_DTFINAL,'YYYYMM')||'4'||RPAD(0,84,0);
      V_CONTADOR := NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
         (LPAD(V_CONTADOR, 10, '0'),V_REGNULO_25,P_SEQMAGNET);
   END IF;
   */
   RETURN(LPAD(V_CONTADOR,10,0));
END;
/

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

