CREATE OR REPLACE FUNCTION ARQUIVO_DMA_UF(
   P_CDIMPOSTO    IN IMPOSTO.CDIMPOSTO%TYPE,
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA   IN EMPRESA.CDINSCESTA%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE) RETURN NUMBER
AS
   V_CONTADOR        NUMBER;
   V_TOTCONTENTR     NUMBER;
   V_TOTBASEENTR     NUMBER;
   V_TOTOUTRENTR     NUMBER;
   V_TOTCONTSAID     NUMBER;
   V_TOTBASESAID     NUMBER;
   V_TOTOUTRSAID     NUMBER;
   V_REGTOTENTR      VARCHAR2(80);
   V_REGTOTSAID      VARCHAR2(100);
   V_NRSEQUMAPAPU    VARCHAR2(10);
BEGIN
   V_CONTADOR := 0;
   V_TOTCONTENTR := 0;
   V_TOTBASEENTR := 0;
   V_TOTOUTRENTR := 0;
   V_TOTCONTSAID := 0;
   V_TOTBASESAID := 0;
   V_TOTOUTRSAID := 0;

   SELECT MAX(NRSEQUMAPAPU )
     INTO V_NRSEQUMAPAPU
     FROM MAPAAPURAC
    WHERE CDEMPRESA = P_CDEMPRESA
      AND CDIMPOSTO = P_CDIMPOSTO
      AND CDINSCESTA = P_CDINSCESTA
      AND DTINIPERIODO = P_DTINICIAL
      AND DTFIMPERIODO = P_DTFINAL;

   FOR C_REGENTRADA IN (SELECT('08'
                               ||TO_CHAR(P_DTINICIAL, 'YYYYMM')
                               ||LPAD(P_CDINSCESTA, 9, '0')
                               ||NF.SGESTADO
                               ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NF.VRCONTABILNF), '9999999999.00'), '.', '')), 12, '0')
                               ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NF.VRBASECALCNF), '9999999999.00'), '.', '')), 12, '0')
                               ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NF.VRCONTABILNF-NF.VRBASECALCNF-NF.VRISENTAS), '9999999999.00'), '.', '')), 12, '0')
                               ||LPAD('0', 12, '0')
                               ||LPAD('0', 12, '0')) AS ARQ_REGENTRADA, SUM(NF.VRCONTABILNF) AS VRCONTABILNF,
                               SUM(NF.VRBASECALCNF) AS VRBASECALCNF, SUM(NF.VRCONTABILNF-NF.VRBASECALCNF-NF.VRISENTAS) AS VROUTROS
                          FROM (SELECT EF.SGESTADO,
                                       SUM(ROUND(I.VRCONTABILNF,2)) AS VRCONTABILNF,
                                       SUM(DECODE(I.CDFISCALNF, 1, ROUND(I.VRBASECALCNF,2), 0)) AS VRBASECALCNF,
                                       SUM(DECODE(I.CDFISCALNF, 2, ROUND(I.VRCONTABILNF,2), 0)) AS VRISENTAS,
                                       SUM(DECODE(I.CDFISCALNF, 3, ROUND(I.VRCONTABILNF,2), 0)) AS VROUTROS
                                  FROM IMPOSTONF I, NOTAFISCAL N, ENDEFORN EF, FORNECEDOR F, IMPOSTO IM
                                 WHERE I.CDFILIAL      = N.CDFILIAL
                                   AND I.NRLANCTONF    = N.NRLANCTONF
                                   AND I.NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                                   AND NVL(IDORIGDESTNF,'F') = 'F'
                                   AND N.IDENTRSAIDOP  = 'E'
                                   AND N.CDFORNECED    = F.CDFORNECED
                                   AND EF.CDFORNECED   = F.CDFORNECED
                                   AND EF.SGESTADO     <> 'BA'
                                   AND NVL(IDTPENDEFORN,'P') = 'P'
                                   AND I.CDIMPOSTO     = IM.CDIMPOSTO
                                   AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                                GROUP BY EF.SGESTADO
                                UNION ALL
                                SELECT EC.SGESTADO,
                                       SUM(ROUND(I.VRCONTABILNF,2)) AS VRCONTABILNF,
                                       SUM(DECODE(I.CDFISCALNF, 1, ROUND(I.VRBASECALCNF,2), 0)) AS VRBASECALCNF,
                                       SUM(DECODE(I.CDFISCALNF, 2, ROUND(I.VRCONTABILNF,2), 0)) AS VRISENTAS,
                                       SUM(DECODE(I.CDFISCALNF, 3, ROUND(I.VRCONTABILNF,2), 0)) AS VROUTROS
                                  FROM IMPOSTONF I, NOTAFISCAL N, ENDECLIE EC, CLIENTE C, IMPOSTO IM
                                 WHERE I.CDFILIAL      = N.CDFILIAL
                                   AND I.NRLANCTONF    = N.NRLANCTONF
                                   AND N.CDCLIENTE     = C.CDCLIENTE
                                   AND NVL(IDORIGDESTNF,'C') = 'C'
                                   AND N.IDENTRSAIDOP  = 'E'
                                   AND I.NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                                   AND EC.CDCLIENTE    = C.CDCLIENTE
                                   AND EC.SGESTADO     <> 'BA'
                                   AND NVL(IDTPENDECLIE,'P') = 'P'
                                   AND I.CDIMPOSTO     = IM.CDIMPOSTO
                                   AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                                GROUP BY EC.SGESTADO
                                UNION ALL
                                SELECT EF.SGESTADO,
                                       SUM(ROUND(I.VRCONTABILNF,2)) AS VRCONTABILNF,
                                       SUM(DECODE(I.CDFISCALNF, 1, ROUND(I.VRBASECALCNF,2), 0)) AS VRBASECALCNF,
                                       SUM(DECODE(I.CDFISCALNF, 2, ROUND(I.VRCONTABILNF,2), 0)) AS VRISENTAS,
                                       SUM(DECODE(I.CDFISCALNF, 3, ROUND(I.VRCONTABILNF,2), 0)) AS VROUTROS
                                  FROM IMPOSTONF I, NOTAFISCAL N, ENDEFILI EF, FILIAL F, IMPOSTO IM
                                 WHERE I.CDFILIAL      = N. CDFILIAL
                                   AND I.NRLANCTONF    = N.NRLANCTONF
                                   AND NVL(IDORIGDESTNF,'L') = 'L'
                                   AND N.CDFILIEMIT = F.CDFILIAL
                                   AND N.IDENTRSAIDOP  = 'E'
                                   AND I.NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                                   AND EF.CDFILIAL     = F.CDFILIAL
                                   AND EF.SGESTADO     <> 'BA'
                                   AND NVL(IDTPENDEFILI,'P') = 'P'
                                   AND I.CDIMPOSTO     = IM.CDIMPOSTO
                                   AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                                GROUP BY EF.SGESTADO) NF
                        GROUP BY NF.SGESTADO
                        ORDER BY NF.SGESTADO) LOOP
      V_CONTADOR := NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        (V_CONTADOR, C_REGENTRADA.ARQ_REGENTRADA, P_NRSEQARQMAG);

      V_TOTCONTENTR := V_TOTCONTENTR + C_REGENTRADA.VRCONTABILNF;
      V_TOTBASEENTR := V_TOTBASEENTR + C_REGENTRADA.VRBASECALCNF;
      V_TOTOUTRENTR := V_TOTOUTRENTR + C_REGENTRADA.VROUTROS;
   END LOOP;

   /*REGISTRO QUE TOTALIZA AS ENTRADAS*/
   V_REGTOTENTR := '08'
                   ||TO_CHAR(P_DTINICIAL, 'YYYYMM')
                   ||LPAD(P_CDINSCESTA, 9, '0')
                   ||'ZZ'
                   ||LPAD(LTRIM(REPLACE(TO_CHAR(V_TOTCONTENTR, '9999999999.00'), '.', '')), 12, '0')
                   ||LPAD(LTRIM(REPLACE(TO_CHAR(V_TOTBASEENTR, '9999999999.00'), '.', '')), 12, '0')
                   ||LPAD(LTRIM(REPLACE(TO_CHAR(V_TOTOUTRENTR, '9999999999.00'), '.', '')), 12, '0')
                   ||LPAD('0', 12, '0')
                   ||LPAD('0', 12, '0');
   V_CONTADOR := NVL(V_CONTADOR,0)+1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     (V_CONTADOR, V_REGTOTENTR, P_NRSEQARQMAG);

   FOR C_REGSAIDA IN(SELECT('09'
                            ||TO_CHAR(P_DTINICIAL, 'YYYYMM')
                            ||LPAD(P_CDINSCESTA, 9, '0')
                            ||NFS.SGESTADO
                            ||LPAD(NVL('0', ' '), 12, '0')
                            ||LPAD(NVL(LTRIM(REPLACE(TO_CHAR(SUM(NFS.VRCONTABILNF), '9999999999.00'), '.', '')), 0), 12, '0')
                            ||LPAD(NVL('0', ' '), 12, '0')
                            ||LPAD(NVL(LTRIM(REPLACE(TO_CHAR(SUM(NFS.VRBASECALCNF), '9999999999.00'), '.', '')), 0), 12, '0')
                            ||LPAD(NVL(LTRIM(REPLACE(TO_CHAR(SUM(NFS.VRCONTABILNF-NFS.VRBASECALCNF-NFS.VRISENTAS), '9999999999.00'), '.', '')), 0), 12, '0')
                            ||LPAD(NVL('0', ' '), 12, '0')) AS ARQ_REGSAIDA, SUM(NFS.VRCONTABILNF) AS VRCONTABILNF,
                            SUM(NFS.VRBASECALCNF) AS VRBASECALCNF, SUM(NFS.VRCONTABILNF-NFS.VRBASECALCNF-NFS.VRISENTAS) AS VROUTROS
                       FROM(SELECT EF.SGESTADO,
                                   SUM(ROUND(I.VRCONTABILNF,2)) AS VRCONTABILNF,
                                   SUM(DECODE(I.CDFISCALNF, 1, ROUND(I.VRBASECALCNF,2), 0)) AS VRBASECALCNF,
                                   SUM(DECODE(I.CDFISCALNF, 2, ROUND(I.VRCONTABILNF,2), 0)) AS VRISENTAS,
                                   SUM(DECODE(I.CDFISCALNF, 3, ROUND(I.VRCONTABILNF,2), 0)) AS VROUTROS
                              FROM IMPOSTONF I, NOTAFISCAL N, ENDEFORN EF, FORNECEDOR F, IMPOSTO IM
                             WHERE NVL(IDORIGDESTNF,'F') = 'F'
                               AND N.IDENTRSAIDOP  = 'S'
                               AND I.NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                               AND I.CDFILIAL      = N.CDFILIAL
                               AND I.NRLANCTONF    = N.NRLANCTONF
                               AND F.CDFORNECED    = N.CDFORNECED
                               AND EF.CDFORNECED   = F.CDFORNECED
                               AND EF.SGESTADO     <> 'BA'
                               AND NVL(IDTPENDEFORN,'P') = 'P'
                               AND I.CDIMPOSTO     = IM.CDIMPOSTO
                               AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                            GROUP BY EF.SGESTADO
                            UNION ALL
                            SELECT EC.SGESTADO,
                                   SUM(ROUND(I.VRCONTABILNF,2)) AS VRCONTABILNF,
                                   SUM(DECODE(I.CDFISCALNF, 1, ROUND(I.VRBASECALCNF,2), 0)) AS VRBASECALCNF,
                                   SUM(DECODE(I.CDFISCALNF, 2, ROUND(I.VRCONTABILNF,2), 0)) AS VRISENTAS,
                                   SUM(DECODE(I.CDFISCALNF, 3, ROUND(I.VRCONTABILNF,2), 0)) AS VROUTROS
                              FROM IMPOSTONF I, NOTAFISCAL N, ENDECLIE EC, CLIENTE C, IMPOSTO IM
                             WHERE NVL(IDORIGDESTNF,'C') = 'C'
                               AND N.IDENTRSAIDOP  = 'S'
                               AND I.NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                               AND I.CDFILIAL      = N.CDFILIAL
                               AND I.NRLANCTONF    = N.NRLANCTONF
                               AND C.CDCLIENTE     = N.CDCLIENTE
                               AND EC.CDCLIENTE    = C.CDCLIENTE
                               AND EC.SGESTADO     <> 'BA'
                               AND NVL(IDTPENDECLIE,'P') = 'P'
                               AND I.CDIMPOSTO     = IM.CDIMPOSTO
                               AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                            GROUP BY EC.SGESTADO
                            UNION ALL
                            SELECT EF.SGESTADO,
                                   SUM(ROUND(I.VRCONTABILNF,2)) AS VRCONTABILNF,
                                   SUM(DECODE(I.CDFISCALNF, 1, ROUND(I.VRBASECALCNF,2), 0)) AS VRBASECALCNF,
                                   SUM(DECODE(I.CDFISCALNF, 2, ROUND(I.VRCONTABILNF,2), 0)) AS VRISENTAS,
                                   SUM(DECODE(I.CDFISCALNF, 3, ROUND(I.VRCONTABILNF,2), 0)) AS VROUTROS
                              FROM IMPOSTONF I, NOTAFISCAL N, ENDEFILI EF, FILIAL F, IMPOSTO IM
                             WHERE NVL(IDORIGDESTNF,'L') = 'L'
                               AND N.IDENTRSAIDOP  = 'S'
                               AND I.NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                               AND I.CDFILIAL      = N.CDFILIAL
                               AND I.NRLANCTONF    = N.NRLANCTONF
                               AND F.CDFILIAL      = N.CDFILIRECE
                               AND EF.CDFILIAL     = F.CDFILIAL
                               AND EF.SGESTADO     <> 'BA'
                               AND NVL(IDTPENDEFILI,'P') = 'P'
                               AND I.CDIMPOSTO     = IM.CDIMPOSTO
                               AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                            GROUP BY EF.SGESTADO) NFS
                     GROUP BY NFS.SGESTADO) LOOP
      V_CONTADOR := NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        (V_CONTADOR, C_REGSAIDA.ARQ_REGSAIDA, P_NRSEQARQMAG);

      V_TOTCONTSAID := V_TOTCONTSAID + C_REGSAIDA.VRCONTABILNF;
      V_TOTBASESAID := V_TOTBASESAID + C_REGSAIDA.VRBASECALCNF;
      V_TOTOUTRSAID := V_TOTOUTRSAID + C_REGSAIDA.VROUTROS;
   END LOOP;

   /*REGISTRO QUE TOTALIZA AS SAIDAS*/

   V_REGTOTSAID := '09'
                   ||TO_CHAR(P_DTINICIAL, 'YYYYMM')
                   ||LPAD(P_CDINSCESTA, 9, '0')
                   ||'ZZ'
                   ||LPAD(NVL('0', ' '), 12, '0')
                   ||LPAD(LTRIM(REPLACE(TO_CHAR(V_TOTCONTSAID, '9999999999.00'), '.', '')), 12, '0')
                   ||LPAD(NVL('0', ' '), 12, '0')
                   ||LPAD(LTRIM(REPLACE(TO_CHAR(V_TOTBASESAID, '9999999999.00'), '.', '')), 12, '0')
                   ||LPAD(LTRIM(REPLACE(TO_CHAR(V_TOTOUTRSAID, '9999999999.00'), '.', '')), 12, '0')
                   ||LPAD(NVL('0', ' '), 12, '0');
   V_CONTADOR := NVL(V_CONTADOR,0)+1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     (V_CONTADOR, V_REGTOTSAID, P_NRSEQARQMAG);

   RETURN(V_CONTADOR);
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_DMA_UF','6.49.017'); END;
/

