CREATE OR REPLACE FUNCTION SPED_BLOCOE_FOS(
   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_LIVROMAG     IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_TOTFORPER_S  IN IMPOSTONF.VRIMPOSTONF%TYPE,
   P_TOTFORPER_E  IN IMPOSTONF.VRIMPOSTONF%TYPE) RETURN NUMBER
AS
   V_CONTREGE          NUMBER;
   V_MOVIMENT          NUMBER;
   V_VRTOTIMPOSNF      NUMBER;
   V_VRTOTENTR         NUMBER;
   V_VRTOTOUTRCRED     NUMBER;
   V_VL_TOT_DEBITOS    NUMBER;
   V_VL_TOT_CREDITOS   NUMBER;
   V_SLD_APURADO       NUMBER;
   V_TOT_DED           NUMBER;
   V_CDIMPOSTRIB       VARCHAR2(02);
   V_IPI               VARCHAR2(02);
BEGIN
   BEGIN
      SELECT CDIMPOSTOLIVRO
      INTO V_IPI
      FROM TPIMPOSLIVRO
      WHERE CDLIVROMAG = P_LIVROMAG
        AND SGIMPOSTOLIVRO LIKE '%IPI%';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IPI := NULL;
   END;
   /* valor do imposto nf saida menos as nf de saida fora do periodo */
   BEGIN
      SELECT SUM(VRIMPOSTONF)-NVL(P_TOTFORPER_S,0)
        INTO V_VL_TOT_DEBITOS
        FROM IMPOSTONF I, NOTAFISCAL N, MAPAAPURAC M
       WHERE M.CDEMPRESA  = P_CDEMPRESA
         AND M.CDINSCESTA = P_CDINSCESTA
         AND DTINIPERIODO = P_DTINICIAL
         AND DTFIMPERIODO = P_DTFINAL
         AND M.CDIMPOSTO  = P_CDIMPOSTO
         AND I.CDIMPOSTO  = P_CDIMPOSTO
         AND SUBSTR(CDFISCOPERNF,1,1) IN ('5','6','7')
         AND I.NRSEQUMAPAPU = M.NRSEQUMAPAPU
         AND I.CDFILIAL = N.CDFILIAL
         AND I.NRLANCTONF = N.NRLANCTONF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_VL_TOT_DEBITOS := 0;
   END;
   BEGIN
      SELECT SUM(VRIMPOSTONF)
        INTO V_VL_TOT_CREDITOS
        FROM IMPOSTONF I, NOTAFISCAL N, MAPAAPURAC M
       WHERE M.CDEMPRESA  = P_CDEMPRESA
         AND M.CDINSCESTA = P_CDINSCESTA
         AND DTINIPERIODO = P_DTINICIAL
         AND DTFIMPERIODO = P_DTFINAL
         AND M.CDIMPOSTO  = P_CDIMPOSTO
         AND I.CDIMPOSTO  = P_CDIMPOSTO
         AND SUBSTR(CDFISCOPERNF,1,1) IN ('1','2','3')
         AND CDFISCALNF = '1'
         AND I.NRSEQUMAPAPU = M.NRSEQUMAPAPU
         AND I.CDFILIAL = N.CDFILIAL
         AND I.NRLANCTONF = N.NRLANCTONF
         AND N.CDMODDOCFISC IN ('01', '04','21','22','55');
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_VL_TOT_CREDITOS := 0;
   END;
   /* valor total saldo devedor */
   V_SLD_APURADO := ABS(NVL(V_VL_TOT_DEBITOS,0) - NVL(V_VL_TOT_CREDITOS,0));
   FOR C_REGE100 IN (SELECT ('|E100'                             || '|' ||
                             TO_CHAR(DTINIPERIODO,'ddmmyyyy')    || '|' ||
                             TO_CHAR(DTFIMPERIODO,'ddmmyyyy')    || '|'  ) AS ARQ_E100,
                             TO_CHAR(DTINIPERIODO,'ddmmyyyy') as DTINICIAL,
                             TO_CHAR(DTFIMPERIODO,'ddmmyyyy') as DTFINAL
                     FROM MAPAAPURAC
                     WHERE CDEMPRESA     = P_CDEMPRESA                   AND
                           CDINSCESTA    = P_CDINSCESTA                  AND
                           DTINIPERIODO  = P_DTINICIAL                   AND
                           DTFIMPERIODO  = P_DTFINAL                     AND
                           CDIMPOSTO     = P_CDIMPOSTO  ) LOOP
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('53'||LPAD(RTRIM(V_CONTREGE), 8, '0'), C_REGE100.ARQ_E100, P_NRSEQARQMAG);
      V_CONTREGE := NVL(V_CONTREGE,0) + 1;
      FOR C_REGE110 IN (SELECT ('|E110'   || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_VL_TOT_DEBITOS,2),0), '999999999990.00')), '.', ',')   || '|' ||
                                '0,00'   || '|' ||
                                '0,00'   || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRESTOCRED,2),0), '999999999990.00')), '.', ',')   || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_VL_TOT_CREDITOS,2),0), '999999999990.00')), '.', ',')   || '|' ||
                                '0,00'   || '|' ||
                                '0,00'   || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRESTODEBI,2),0), '999999999990.00')), '.', ',')   || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRSLDCRANTE,2),0), '999999999990.00')), '.', ',')   || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_SLD_APURADO,2),0), '999999999990.00')), '.', ',')   || '|' || -- SALDO_DEVEDOR
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRDEDUCAO,2),0), '999999999990.00')), '.', ',')   || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(ABS(V_SLD_APURADO-VRDEDUCAO),2),0), '999999999990.00')), '.', ',')   || '|' || --CAMPO_13
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRSLDCRANTE,2),0), '999999999990.00')), '.', ',')   || '|' ||
                                '0,00'   || '|'  ) AS ARQ_E110, VRDEDUCAO
                        FROM MAPAAPURAC M, MAPARECUP R
                        WHERE M.CDEMPRESA    = P_CDEMPRESA
                          AND M.CDINSCESTA   = P_CDINSCESTA
                          AND M.DTINIPERIODO = P_DTINICIAL
                          AND M.DTFIMPERIODO = P_DTFINAL
                          AND M.CDIMPOSTO    = P_CDIMPOSTO
                          AND M.NRSEQUMAPAPU = R.NRSEQUMAPAPU) LOOP
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('54'||LPAD(RTRIM(V_CONTREGE), 8, '0'), C_REGE110.ARQ_E110, P_NRSEQARQMAG);
         V_CONTREGE := NVL(V_CONTREGE,0) + 1;
         V_TOT_DED := NVL(V_TOT_DED,0) + NVL(C_REGE110.VRDEDUCAO,0);
      END LOOP;
      /*
      FOR C_REGE110 IN (SELECT ('|E110'   || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRTOTADEBI,2),0), '999999999990.00')), '.', ',')   || '|' ||
                                '0,00'   || '|' ||
                                '0,00'   || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRESTOCRED,2),0), '999999999990.00')), '.', ',')   || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRTOTACRED,2),0), '999999999990.00')), '.', ',')   || '|' ||
                                '0,00'   || '|' ||
                                '0,00'   || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRESTODEBI,2),0), '999999999990.00')), '.', ',')   || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRSLDCRANTE,2),0), '999999999990.00')), '.', ',')   || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRSALDODEBI,2),0), '999999999990.00')), '.', ',')   || '|' || -- SALDO_DEVEDOR
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRDEDUCAO,2),0), '999999999990.00')), '.', ',')   || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRTOTAIMPOS,2),0), '999999999990.00')), '.', ',')   || '|' || --CAMPO_13
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRSLDCRANTE,2),0), '999999999990.00')), '.', ',')   || '|' ||
                                '0,00'   || '|'  ) AS ARQ_E110, VRDEDUCAO
                        FROM MAPAAPURAC M, MAPARECUP R
                        WHERE M.CDEMPRESA    = P_CDEMPRESA
                          AND M.CDINSCESTA   = P_CDINSCESTA
                          AND M.DTINIPERIODO = P_DTINICIAL
                          AND M.DTFIMPERIODO = P_DTFINAL
                          AND M.CDIMPOSTO    = P_CDIMPOSTO
                          AND M.NRSEQUMAPAPU = R.NRSEQUMAPAPU) LOOP
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('54'||LPAD(RTRIM(V_CONTREGE), 8, '0'), C_REGE110.ARQ_E110, P_NRSEQARQMAG);
         V_CONTREGE := NVL(V_CONTREGE,0) + 1;
         V_TOT_DED := NVL(V_TOT_DED,0) + NVL(C_REGE110.VRDEDUCAO,0);
      END LOOP;
      */
      FOR C_REGE116 IN (SELECT ('|E116'                         || '|' ||
                                '000'                           || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(V_VL_TOT_DEBITOS,2),0), '999999999990.00')), '.', ',')   || '|' ||
                                TO_CHAR(DTVENCGUIA, 'ddmmyyyy') || '|' ||
                                CDRECEGUIA                      || '|' ||
                                ''                              || '|' ||
                                ''                              || '|' ||
                                ''                              || '|' ||
                                ''                              || '|'  ) AS ARQ_E116
                        FROM MAPAAPURAC
                        WHERE CDEMPRESA    = P_CDEMPRESA
                          AND CDINSCESTA   = P_CDINSCESTA
                          AND DTINIPERIODO = P_DTINICIAL
                          AND DTFIMPERIODO = P_DTFINAL
                          AND CDIMPOSTO    = P_CDIMPOSTO) LOOP
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('55'||LPAD(RTRIM(V_CONTREGE), 8, '0'), C_REGE116.ARQ_E116, P_NRSEQARQMAG);
         V_CONTREGE := NVL(V_CONTREGE,0) + 1;
      END LOOP;
   END LOOP;
   FOR C_REGE200 IN (SELECT ('|E200'                            | | '|' ||
                             I.SGESTADO                         || '|' ||
                             TO_CHAR(M.DTINIPERIODO,'ddmmyyyy') || '|' ||
                             TO_CHAR(M.DTFIMPERIODO,'ddmmyyyy') || '|'  ) AS ARQ_E200,
                             TO_CHAR(M.DTINIPERIODO,'ddmmyyyy') as DTINICIAL,
                             TO_CHAR(M.DTFIMPERIODO,'ddmmyyyy') as DTFINAL
                     FROM MAPAAPURAC M, INSCRESTAD I
                     WHERE M.CDEMPRESA     = P_CDEMPRESA                   AND
                           I.CDEMPRESA     = P_CDEMPRESA                   AND
                           M.CDINSCESTA    = P_CDINSCESTA                  AND
                           I.CDINSCESTA    = P_CDINSCESTA                  AND
                           M.DTINIPERIODO  = P_DTINICIAL                   AND
                           M.DTFIMPERIODO  = P_DTFINAL                     AND
                           M.CDIMPOSTO     = P_CDIMPOSTO) LOOP
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('56'||LPAD(RTRIM(V_CONTREGE), 8, '0'), C_REGE200.ARQ_E200, P_NRSEQARQMAG);
      V_CONTREGE := NVL(V_CONTREGE,0) + 1;
   END LOOP;
   FOR C_REGE210 IN (SELECT ('|E210'                                                                              || '|' ||
                             '0'                                                                                  || '|' ||
                             '0,00'                                                                               || '|' ||
                       		    '0,00'                                                                               || '|' ||
                       		    '0,00'                                                                               || '|' ||
                             '0,00'                                                                               || '|' ||
                             '0,00'                                                                               || '|' ||
                             '0,00'                                                                               || '|' ||
                             '0,00'                                                                               || '|' ||
                             '0,00'                                                                               || '|' ||
                             '0,00'                                                                               || '|' ||
                             '0,00'                                                                               || '|' ||
                             '0,00'                                                                               || '|' ||
                             '0,00'                                                                               || '|' ||
                             '0,00'                                                                               || '|'  ) AS ARQ_E210
                       FROM MAPAAPURAC M, MAPARECUP R
                      WHERE M.CDEMPRESA    = P_CDEMPRESA
                        AND M.CDINSCESTA   = P_CDINSCESTA
                        AND M.DTINIPERIODO = P_DTINICIAL
                        AND M.DTFIMPERIODO = P_DTFINAL
                        AND M.CDIMPOSTO    = P_CDIMPOSTO
                        AND M.NRSEQUMAPAPU = R.NRSEQUMAPAPU) LOOP
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('57'||LPAD(RTRIM(V_CONTREGE), 8, '0'), C_REGE210.ARQ_E210, P_NRSEQARQMAG);
      V_CONTREGE := NVL(V_CONTREGE,0) + 1;
   END LOOP;
   FOR C_REGE500 IN (SELECT ('|E500'                          || '|' ||
                            '0'                                  || '|' ||
                             TO_CHAR(DTINIPERIODO,'ddmmyyyy')    || '|' ||
                             TO_CHAR(DTFIMPERIODO,'ddmmyyyy')    || '|'  ) AS ARQ_E500,
                             TO_CHAR(DTINIPERIODO,'ddmmyyyy') as DTINICIAL,
                             TO_CHAR(DTFIMPERIODO,'ddmmyyyy') as DTFINAL
                       FROM MAPAAPURAC M, MAPARECUP R
                      WHERE M.CDEMPRESA    = P_CDEMPRESA
                        AND M.CDINSCESTA   = P_CDINSCESTA
                        AND M.CDIMPOSTO    = V_IPI
                        AND M.DTINIPERIODO = P_DTINICIAL
                        AND M.DTFIMPERIODO = P_DTFINAL
                     			AND M.NRSEQUMAPAPU = R.NRSEQUMAPAPU) LOOP
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('58'||LPAD(RTRIM(V_CONTREGE), 8, '0'), C_REGE500.ARQ_E500, P_NRSEQARQMAG);
         V_CONTREGE := NVL(V_CONTREGE,0) + 1;
       FOR C_REGE510 IN (SELECT ('|E510'                                                                                 || '|' ||
                                N.CDFISCOPERNF                                                                          || '|' ||
                                N.IMPOS                                                                                 || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(N.VRCONTABILNF,2),0), '999999999990.00')), '.', ',')    || '|' ||
                                REPLACE(LTRIM(TO_CHAR(NVL(ROUND(N.VRBASECALCNF,2),0), '999999999990.00')), '.', ',')    || '|' ||
                                N.IMPOSTRIB                                                                             || '|'  ) AS ARQ_E510
                       FROM (SELECT DECODE(I.CDFISCALNF, '1', DECODE(I.VRALIQNF, 0, '02', '00'), '2', '03', '3', '04') AS IMPOS,
                                    I.CDFISCOPERNF, I.CDIMPOSTO,
                                    SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                                    SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                    MAX(VALOR_IMPOSTO_NF(N.CDFILIAL, N.NRLANCTONF, IM.CDIMPOSTRIB, 'I')) AS IMPOSTRIB
                             FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM
                             WHERE M.CDEMPRESA       = P_CDEMPRESA
                               AND M.CDINSCESTA      = P_CDINSCESTA
                               AND M.DTINIPERIODO    = C_REGE500.DTINICIAL
                               AND M.DTFIMPERIODO    = C_REGE500.DTFINAL
                               AND M.CDIMPOSTO       = V_IPI
                               AND I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU
                               AND I.CDIMPOSTO       = IM.CDIMPOSTO
                               AND NVL(I.IDIMPOSTONF, '1') <> '3'
                               AND N.CDFILIAL        = I.CDFILIAL
                               AND NVL(N.CDSERIE,' ')         <> 'ECF'
                               AND N.NRLANCTONF      = I.NRLANCTONF
                               AND ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E'))
                             GROUP BY DECODE(I.CDFISCALNF, '1', DECODE(I.VRALIQNF, 0, '02', '00'), '2', '03', '3', '04'),
                                      I.CDFISCOPERNF, I.CDSITUTRIBNF, I.CDIMPOSTO )N
                       GROUP BY N.CDFISCOPERNF, N.IMPOS, N.VRCONTABILNF, N.VRBASECALCNF, N.IMPOSTRIB ) LOOP
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('59'||LPAD(RTRIM(V_CONTREGE), 8, '0'), C_REGE510.ARQ_E510, P_NRSEQARQMAG);
         V_CONTREGE := NVL(V_CONTREGE,0) + 1;
      END LOOP;
   END LOOP;
   /*  */
   FOR C_REGE520 IN (SELECT ('|E520'                                                                            || '|' ||
                             REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRSLDCRANTE,2),0), '999999999990.00')), '.', ',')  || '|' ||
                             REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRTOTADEBI,2),0), '999999999990.00')), '.', ',')   || '|' ||
                             REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRTOTACRED,2),0), '999999999990.00')), '.', ',')   || '|' ||
                             REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VROUTRDEBI,2),0), '999999999990.00')), '.', ',')   || '|' ||
                             REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VROUTRCRED,2),0), '999999999990.00')), '.', ',')   || '|' ||
                             REPLACE(LTRIM(TO_CHAR(NVL(ROUND(VRSLDCRANTE,2),0), '999999999990.00')), '.', ',')  || '|' ||
                             REPLACE(LTRIM(TO_CHAR(NVL(ROUND(((VRTOTADEBI+VROUTRDEBI)-(VRSLDCRANTE+VRTOTACRED+VROUTRCRED)),2),0), '999999999990.00')), '.', ',')   || '|' ) AS ARQ_E520
                       FROM MAPAAPURAC M, MAPARECUP R
                      WHERE M.CDEMPRESA    = P_CDEMPRESA
                        AND M.CDINSCESTA   = P_CDINSCESTA
                        AND M.CDIMPOSTO    = V_IPI
                        AND M.DTINIPERIODO = P_DTINICIAL
                        AND M.DTFIMPERIODO = P_DTFINAL
                     			AND M.NRSEQUMAPAPU = R.NRSEQUMAPAPU) LOOP
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('60'||LPAD(RTRIM(V_CONTREGE), 8, '0'), C_REGE520.ARQ_E520, P_NRSEQARQMAG);
      V_CONTREGE := NVL(V_CONTREGE,0) + 1;
   END LOOP;
   /* abertuta do bloco E */
   IF V_CONTREGE <> 0 THEN
      V_MOVIMENT := 0;
   ELSE
      V_MOVIMENT := 1;
   END IF;
   V_CONTREGE := NVL(V_CONTREGE,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('52'||LPAD(RTRIM(V_CONTREGE), 8, '0'), '|E001'|| '|' ||V_MOVIMENT|| '|', P_NRSEQARQMAG);
   /* ENCERRAMENTO DO BLOCO E */
   V_CONTREGE := NVL(V_CONTREGE,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('61'||LPAD(RTRIM(V_CONTREGE), 8, '0'), '|E990'|| '|' ||V_CONTREGE|| '|', P_NRSEQARQMAG);
   RETURN(V_CONTREGE);
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('SPED_BLOCOE_FOS','5.05.000'); END;
/