CREATE OR REPLACE FUNCTION SPED_BLOCOD_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_PERFIL       IN NOTAFISCAL.IDSTATUS%TYPE,
   P_VERSAO       IN VARCHAR2) RETURN VARCHAR
AS
   V_CONTREGD        NUMBER;
   V_VL_BC_ICMS_ST   NUMBER;
   V_VL_ICMS_ST      NUMBER;
   V_VL_IP           NUMBER;
   V_TOTFORPER_S     NUMBER;
   V_TOTFORPER_E     NUMBER;
   V_VRFORAPER       NUMBER;
   V_VRFORAPER_E     NUMBER;
   V_VL_RED_BC       NUMBER;
   V_VRDESCONTO      NUMBER;
   V_SEQUENCIAL      VARCHAR2(100);
   V_IDOUTROS        VARCHAR2(1);
   V_CDIMPOSTRIB     VARCHAR2(02);
   V_IPI             VARCHAR2(02);
   V_PIS             VARCHAR2(02);
   V_COFINS          VARCHAR2(02);
   V_DTPERIODO       VARCHAR2(01);
   V_DADOS_2         VARCHAR2(10);
   V_IDSTATUS        VARCHAR2(01);
   V_ICMS            VARCHAR2(02);
   V_MOVIMENT        VARCHAR2(01);
   V_NRSEQUMAPAPU    VARCHAR2(10);
   V_COD_SIT         VARCHAR2(10);
   V_LINHA           VARCHAR2(4000);
BEGIN
   BEGIN
      SELECT CDIMPOSTOLIVRO
      INTO V_IPI
      FROM TPIMPOSLIVRO
      WHERE SGIMPOSTOLIVRO LIKE '%IPI'
        AND CDLIVROMAG = P_LIVROMAG;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
      V_IPI := NULL;
   END;
   BEGIN
      SELECT CDIMPOSTOLIVRO
      INTO V_PIS
      FROM TPIMPOSLIVRO
      WHERE SGIMPOSTOLIVRO LIKE '%PIS'
        AND CDLIVROMAG = P_LIVROMAG;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_PIS := NULL;
   END;
   BEGIN
      SELECT CDIMPOSTOLIVRO
      INTO V_COFINS
      FROM TPIMPOSLIVRO
      WHERE SGIMPOSTOLIVRO LIKE '%COFINS'
        AND CDLIVROMAG = P_LIVROMAG;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
      V_COFINS := NULL;
   END;
   BEGIN
      SELECT EN.IDENCARGO
      INTO V_IDOUTROS
      FROM RELACENCARGO E, ENCARGO EN
      WHERE E.CDENCARGO = EN.CDENCARGO AND
            E.IDENCARGODOS LIKE '%OUTR%';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
      V_IDOUTROS    := NULL;
   END;
   BEGIN
      SELECT CDIMPOSTRIB
      INTO V_CDIMPOSTRIB
      FROM IMPOSTO
      WHERE CDIMPOSTO = P_CDIMPOSTO;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
      V_CDIMPOSTRIB    := NULL;
   END;
   BEGIN
      SELECT NRSEQUMAPAPU
        INTO V_NRSEQUMAPAPU
        FROM MAPAAPURAC M
       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;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_NRSEQUMAPAPU := NULL;
   END;
   V_LINHA := NULL;
   FOR C_REGD100 IN (SELECT NF.REG                 || '|' ||
                            NF.IND_OPER            || '|' ||
                            NF.IND_EMIT            || '|' ||
                            NF.COD_PAR             || '|' ||
                            NF.COD_MOD             || '|'  AS PARTE_1,
                            NF.SER                 || '|' ||
                            NF.SUB                 || '|' ||
                            NF.NUM_DOC             || '|' ||
                            NF.CHV_CTE             || '|' ||
                            NF.DT_DOC              || '|' ||
                            NF.DT_A_P              || '|' ||
                            NF.TP_CT_e             || '|' ||
                            NF.CHV_CTE_REF         || '|' ||
                            REPLACE(LTRIM(TO_CHAR(ROUND(NF.VL_DOC,2), '999999999990.00')), '.', ',')    || '|' AS PARTE_3,
                            NF.IND_FRT             || '|' ||
                            REPLACE(LTRIM(TO_CHAR(ROUND(NF.VL_SERV,2), '999999999990.00')), '.', ',')    || '|' ||
                            REPLACE(LTRIM(TO_CHAR(ROUND(NF.VL_BC_ICMS,2), '999999999990.00')), '.', ',')    || '|' ||
                            REPLACE(LTRIM(TO_CHAR(ROUND(NF.VL_ICMS,2), '999999999990.00')), '.', ',')    || '|' ||
                            REPLACE(LTRIM(TO_CHAR(ROUND(NF.VL_NT,2), '999999999990.00')), '.', ',')    || '|' ||
                            NF.COD_INF             || '|' ||
                            NF.COD_CTA             || '|'  AS PARTE_5,
                            NF.CDFILIAL, NF.NRLANCTONF, NF.DTEMISSAO, NF.DTENTRSAID, NF.IDSTATUS, NF.IDENTRSAIDOP, NF.VL_SERV,
                            NF.COD_MOD
                       FROM(SELECT '|D100' AS REG,
                                   DECODE(N.IDENTRSAIDOP, 'E', '0','1') AS IND_OPER,
                                   DECODE(N.IDENTRSAIDOP, 'E', DECODE(N.IDSTATUS,'E','0','1'), '0') AS IND_EMIT,
                                   DECODE(N.IDORIGDESTNF, 'F', N.CDFORNECED, DECODE(N.IDORIGDESTNF, 'C', N.CDCLIENTE , DECODE(N.IDENTRSAIDOP, 'E', N.CDFILIEMIT, N.CDFILIRECE))) AS COD_PAR,
                                   N.CDMODDOCFISC AS COD_MOD,
                                   REPLACE(REPLACE(DECODE(N.IDENTRSAIDOP, 'E', N.CDSERIEENTR, N.CDSERIE),'-',''),'.','') AS SER,
                                   '' AS SUB,
                                   LTRIM(N.NRNOTAFISC, '0') AS NUM_DOC,
                                   '' AS CHV_CTE,
                                   TO_CHAR(N.DTEMISSAO,'ddmmyyyy')  AS DT_DOC,
                                   TO_CHAR(N.DTENTRSAID,'ddmmyyyy') AS DT_A_P,
                                   '' AS TP_CT_e,
                                   '' AS CHV_CTE_REF,
                                   SUM(I.VRCONTABILNF) AS VL_DOC,
                                   NVL(N.IDPGTOFRETE,'9') AS IND_FRT,
                                   SUM(I.VRCONTABILNF) AS VL_SERV,
                                   SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VL_BC_ICMS,
                                   SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VL_ICMS,
                                   SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,'0')) AS VL_NT,
                                   '' AS COD_INF,
                                   '' AS COD_CTA,
                                   N.CDFILIAL, N.NRLANCTONF, N.DTEMISSAO, N.DTENTRSAID, N.IDSTATUS, N.IDENTRSAIDOP
                              FROM IMPOSTONF I, NOTAFISCAL N
                             WHERE I.CDFILIAL = N.CDFILIAL
                               AND I.NRLANCTONF = N.NRLANCTONF
                               AND N.CDMODDOCFISC IN ('07', '08', '09', '10', '11', '26', '27')
                               AND I.NRSEQUMAPAPU = V_NRSEQUMAPAPU
                               AND I.CDIMPOSTO = P_CDIMPOSTO
                               AND NVL(I.IDIMPOSTONF, '1') <> '3'
                               AND N.IDSTATUS IN ('E','A')
                             GROUP BY DECODE(N.IDENTRSAIDOP, 'E', '0','1'),
                                   DECODE(N.IDENTRSAIDOP, 'E', DECODE(N.IDSTATUS,'E','0','1'), '0'),
                                   DECODE(N.IDORIGDESTNF, 'F', N.CDFORNECED, DECODE(N.IDORIGDESTNF, 'C', N.CDCLIENTE , DECODE(N.IDENTRSAIDOP, 'E', N.CDFILIEMIT, N.CDFILIRECE))),
                                   N.CDMODDOCFISC, NVL(N.IDPGTOFRETE,'9'),
                                   REPLACE(REPLACE(DECODE(N.IDENTRSAIDOP, 'E', N.CDSERIEENTR, N.CDSERIE),'-',''),'.',''),
                                   LTRIM(N.NRNOTAFISC, '0'), TO_CHAR(N.DTEMISSAO,'ddmmyyyy'), TO_CHAR(N.DTENTRSAID,'ddmmyyyy'),
                                   N.CDFILIAL, N.NRLANCTONF, N.DTEMISSAO, N.DTENTRSAID, N.IDSTATUS, N.IDENTRSAIDOP
                             UNION ALL
                             /***********NOTA CANCELADA************/
                             SELECT '|D100' AS REG,
                                   DECODE(N.IDENTRSAIDOP, 'E', '0','1') AS IND_OPER,
                                   DECODE(N.IDENTRSAIDOP, 'E', DECODE(N.IDSTATUS,'E','0','1'), '0') AS IND_EMIT,
                                   DECODE(N.IDORIGDESTNF, 'F', N.CDFORNECED, DECODE(N.IDORIGDESTNF, 'C', N.CDCLIENTE , DECODE(N.IDENTRSAIDOP, 'E', N.CDFILIEMIT, N.CDFILIRECE))) AS COD_PAR,
                                   N.CDMODDOCFISC AS COD_MOD,
                                   REPLACE(REPLACE(DECODE(N.IDENTRSAIDOP, 'E', N.CDSERIEENTR, N.CDSERIE),'-',''),'.','') AS SER,
                                   '' AS SUB,
                                   LTRIM(N.NRNOTAFISC, '0') AS NUM_DOC,
                                   '' AS CHV_CTE,
                                   TO_CHAR(N.DTEMISSAO,'ddmmyyyy')  AS DT_DOC,
                                   TO_CHAR(N.DTENTRSAID,'ddmmyyyy') AS DT_A_P,
                                   '' AS TP_CT_e,
                                   '' AS CHV_CTE_REF,
                                   SUM(N.VRNOTAFISC) AS VL_DOC,
                                   NVL(N.IDPGTOFRETE,'9') AS IND_FRT,
                                   0 AS VL_SERV,
                                   0 AS VL_BC_ICMS,
                                   0 AS VL_ICMS,
                                   0 AS VL_NT,
                                   '' AS COD_INF,
                                   '' AS COD_CTA,
                                   N.CDFILIAL, N.NRLANCTONF, N.DTEMISSAO, N.DTENTRSAID, N.IDSTATUS, N.IDENTRSAIDOP
                              FROM NOTAFISCAL N
                             WHERE N.CDMODDOCFISC IN ('07', '08', '09', '10', '11', '26', '27')
                               AND N.DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL
                               AND N.IDENTRSAIDOP = 'S'
                               AND N.IDSTATUS = 'C'
                               AND N.CDINSCESTA = P_CDINSCESTA
                               AND N.CDEMPRESA = P_CDEMPRESA
                             GROUP BY DECODE(N.IDENTRSAIDOP, 'E', '0','1'),
                                   DECODE(N.IDENTRSAIDOP, 'E', DECODE(N.IDSTATUS,'E','0','1'), '0'),
                                   DECODE(N.IDORIGDESTNF, 'F', N.CDFORNECED, DECODE(N.IDORIGDESTNF, 'C', N.CDCLIENTE , DECODE(N.IDENTRSAIDOP, 'E', N.CDFILIEMIT, N.CDFILIRECE))),
                                   N.CDMODDOCFISC, NVL(N.IDPGTOFRETE,'9'),
                                   REPLACE(REPLACE(DECODE(N.IDENTRSAIDOP, 'E', N.CDSERIEENTR, N.CDSERIE),'-',''),'.',''),
                                   LTRIM(N.NRNOTAFISC, '0'), TO_CHAR(N.DTEMISSAO,'ddmmyyyy'), TO_CHAR(N.DTENTRSAID,'ddmmyyyy'),
                                   N.CDFILIAL, N.NRLANCTONF, N.DTEMISSAO, N.DTENTRSAID, N.IDSTATUS, N.IDENTRSAIDOP) NF ) LOOP
      /* verifica a situacao do documento */
      IF C_REGD100.IDENTRSAIDOP = 'E' THEN
         IF C_REGD100.IDSTATUS = 'C' THEN
            /* documentos cancelados */
            V_COD_SIT := '02|';
         ELSE
            IF NOT C_REGD100.DTENTRSAID BETWEEN P_DTINICIAL AND P_DTFINAL THEN
               /* documentos de entrada fora do periodo */
               V_COD_SIT := '01|';
            ELSE
               V_COD_SIT := '00|';
            END IF;
         END IF;
      ELSE
         IF C_REGD100.IDSTATUS = 'C' THEN
            /* documentos cancelados */
            V_COD_SIT := '02|';
         ELSE
            IF NOT (C_REGD100.DTENTRSAID BETWEEN P_DTINICIAL AND P_DTFINAL) AND NOT(C_REGD100.DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL) THEN
               /* documentos de entrada fora do periodo */
               V_COD_SIT := '01|';
            ELSE
               V_COD_SIT := '00|';
            END IF;
         END IF;
      END IF;
      /* resgata o valor total de descontos por nota fiscal */
      SELECT SUM(I.VRITEMENCA)
        INTO V_VRDESCONTO
        FROM ITEMENCARGO I, ENCARGO E
       WHERE E.IDENCARGO = 'D'
         AND I.CDENCARGO = E.CDENCARGO
         AND I.CDFILIAL = C_REGD100.CDFILIAL
         AND I.NRLANCTONF = C_REGD100.NRLANCTONF;
      V_LINHA := C_REGD100.PARTE_1||
                 V_COD_SIT||
                 C_REGD100.PARTE_3||
                 REPLACE(LTRIM(TO_CHAR(ROUND(V_VRDESCONTO,2), '999999999990.00')), '.', ',')    || '|' ||
                 C_REGD100.PARTE_5;
      V_CONTREGD :=  NVL(V_CONTREGD,0) + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('42'||LPAD(RTRIM(V_CONTREGD), 8, '0'), V_LINHA, P_NRSEQARQMAG);
      V_LINHA := NULL;
      IF (C_REGD100.IDENTRSAIDOP = 'S') AND (C_REGD100.COD_MOD = '07') THEN
         V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');
         FOR C_REGD110 IN (SELECT CDARVPROD    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(C_REGD100.VL_SERV,2), '999999999990.00')), '.', ',')    || '|' ||
                                  '0,00'       || '|' AS PART_2
                             FROM ITEMPRODUTO I, PRODUTO P
                            WHERE I.CDFILIAL = C_REGD100.CDFILIAL
                              AND I.NRLANCTONF = C_REGD100.NRLANCTONF
                              AND I.CDPRODUTO = P.CDPRODUTO ) LOOP
            V_CONTREGD :=  NVL(V_CONTREGD,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('42'||LPAD(RTRIM(V_CONTREGD), 8, '0'), '|D110'|| '|' ||V_SEQUENCIAL|| '|' ||C_REGD110.PART_2, P_NRSEQARQMAG);
            V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_SEQUENCIAL))+1)), 3,'0');
         END LOOP;
      END IF;
      /* valor reducao de base de calculo por nf */
      SELECT NVL(SUM(VRCONTABILNF),0)
        INTO V_VL_RED_BC
        FROM IMPOSTONF
       WHERE CDFILIAL   = C_REGD100.CDFILIAL
         AND NRLANCTONF = C_REGD100.NRLANCTONF
         AND CDIMPOSTO  = P_CDIMPOSTO
         AND IDIMPOSTONF = '2';
      FOR C_REGD190 IN(SELECT ('|D190'          || '|' ||
                               T.CDSITUTRIBNF   || '|' ||
                               T.CDFISCOPERNF   || '|' ||
                               REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VRALIQNF,0),2), '999999999990.00')), '.', ',')    || '|' ||
                               REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VL_OPR,0),2), '999999999990.00')), '.', ',')      || '|' ||
                               REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VL_BC_ICMS,0),2), '999999999990.00')), '.', ',')  || '|' ||
                               REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VL_ICMS,0),2), '999999999990.00')), '.', ',')     || '|' ||
                               REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VL_RED_BC,0),2), '999999999990.00')), '.', ',')   || '|' ||
                               ''               || '|' ) ARQ_D190, T.VL_ICMS
                       FROM (SELECT VRALIQNF AS VRALIQNF,
                                    REPLACE(CDFISCOPERNF,'.','') AS CDFISCOPERNF,
                                    LPAD(CDSITUTRIBNF, 3, '0') AS CDSITUTRIBNF,
                                    NVL(SUM(DECODE(IDIMPOSTONF, '1', VRCONTABILNF, 0)),0) AS VL_OPR,
                                    NVL(SUM(DECODE(IDIMPOSTONF, '1', VRBASECALCNF, 0)),0) AS VL_BC_ICMS,
                                    NVL(SUM(DECODE(IDIMPOSTONF, '1', VRIMPOSTONF, 0)),0) AS VL_ICMS
                               FROM IMPOSTONF
                              WHERE CDFILIAL   = C_REGD100.CDFILIAL
                                AND NRLANCTONF = C_REGD100.NRLANCTONF
                                AND CDIMPOSTO  = P_CDIMPOSTO
                                AND IDIMPOSTONF = 1
                                AND NRSEQUMAPAPU = V_NRSEQUMAPAPU
                              GROUP BY VRALIQNF, REPLACE(CDFISCOPERNF,'.',''), LPAD(CDSITUTRIBNF, 3, '0')) T) LOOP
         V_VL_BC_ICMS_ST := 0;
         V_VL_ICMS_ST := 0;
         V_VL_RED_BC := 0;
         V_VL_IP := 0;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('42'||LPAD(RTRIM(V_CONTREGD), 8, '0'), C_REGD190.ARQ_D190, P_NRSEQARQMAG);
         V_CONTREGD := NVL(V_CONTREGD,0) + 1;
      END LOOP;
   END LOOP;
   FOR C_REGD500 IN(SELECT ('|D500'                                                                         || '|' ||
                            NF.CAMPO2                                                                       || '|' ||
                            NF.CAMPO3                                                                       || '|' ||
                            DECODE(NF.IDORIGDESTNF, 'F', NF.CDFORNECED, DECODE(NF.IDORIGDESTNF, 'C', NF.CDCLIENTE , DECODE(NF.IDENTRSAIDOP, 'E', NF.CDFILIEMIT, NF.CDFILIRECE)))   || '|' ||
                            NF.CDMODDOCFISC                                                                 || '|' ) AS DADOS_1,
                            (REPLACE(REPLACE(DECODE(NF.IDENTRSAIDOP, 'E', NF.CDSERIEENTR, NF.CDSERIE),'-',''),'.','')                                   || '|' ||
                             ''                                                                             || '|' ||
                             LTRIM(NF.NRNOTAFISC, '0')                                                      || '|' ||
                             TO_CHAR(NF.DTEMISSAO,'ddmmyyyy')                                               || '|' ||                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                             TO_CHAR(NF.DTENTRSAID,'ddmmyyyy')                                              || '|' ||                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                             REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRCONTABILNF,2), '999999999990.00')), '.', ',') || '|' ||                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                             REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_ENCARGO(NF.CDFILIAL, NF.NRLANCTONF, 'D'),2), '999999999990.00')), '.', ',')              || '|' ||                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                             REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRPRODUTOS,2), '999999999990.00')), '.', ',')   || '|' || --campo 14                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                             REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRNAOTRIBUT,2), '999999999990.00')), '.', ',')  || '|' ||                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                             '0,00'                                                                         || '|' ||                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                             REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_ENCARGO(NF.CDFILIAL, NF.NRLANCTONF, V_IDOUTROS),2), '999999999990.00')), '.', ',')       || '|' || --campo 17                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                             REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRBASECALCNF,2), '999999999990.00')), '.', ',') || '|' || -- campo 18                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                             REPLACE(LTRIM(TO_CHAR(ROUND(NF.VRIMPOSTONF,2), '999999999990.00')), '.', ',')  || '|' || -- campo 19                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                             ''                                                                             || '|' ||                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                             REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NF(NF.CDFILIAL, NF.NRLANCTONF, V_PIS, 'I'),2), '999999999990.00')), '.', ',')    || '|' || -- valor do PIS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                             REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_NF(NF.CDFILIAL, NF.NRLANCTONF, V_COFINS, 'I'),2), '999999999990.00')), '.', ',') || '|' || -- valor do COFINS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                             '' || '|'  ) AS DADOS_3,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                             NF.CDFILIAL AS CDFILIAL, NF.NRLANCTONF AS NRLANCTONF, NF.NRINSJURTRAN, NF.IDTPIJURTRAN, NF.VRPBRUTOVOLU, NF.VRPLIQUVOLU,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                             NF.NRPLACAVEIC, NF.DSQTDEVOLU, NF.DSESPECVOLU, NF.DSMARCAVOLU, NF.DSNUMEVOLU, NF.CDFISCOPERNF, NF.NRNOTAFISC AS NRNOTAFISC,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                             NF.DTEMISSAO, NF.DTENTRSAID, NF.IDENTRSAIDOP, NF.VRIMPOSTONF,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                             DECODE(NF.IDORIGDESTNF, 'F', NF.CDFORNECED, DECODE(NF.IDORIGDESTNF, 'C', NF.CDCLIENTE , DECODE(NF.IDENTRSAIDOP, 'E', NF.CDFILIEMIT, NF.CDFILIRECE))) AS CODPART                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                    FROM (SELECT DECODE(IDENTRSAIDOP, 'E', '0','1') AS CAMPO2,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
                                 '0' AS CAMPO3, NRACESSONFE,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                                 I.CDFILIAL, I.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, SUBSTR(N.CDSERIE,2,4) AS CDSERIE,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                                 SUBSTR(N.CDSERIEENTR,2,4) AS CDSERIEENTR, N.NRINSJURNF, N.IDSTATUS, N.DTENTRSAID, N.VRNOTAFISC, N.VRPRODUTOS,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
                                 N.IDPGTOFRETE, N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                                 N.CDFILIEMIT, N.CDFILIRECE, N.DTEMISSAO, MAX(I.CDFISCOPERNF) AS CDFISCOPERNF, N.CDMODDOCFISC, N.CDCLIENTE, N.CDFORNECED, N.IDORIGDESTNF,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                                 SUM(I.VRCONTABILNF) AS VRCONTABILNF, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                 SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                 SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VRIMPOSTONF,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                                 SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0))   AS VROUTRCRED,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                 SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,'1',DECODE(I.VRPERREDBCNF,0, 0,I.VRCONTABILNF-I.VRBASECALCNF), 0)) AS VRISENTRIB,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                                 SUM(DECODE(I.CDFISCALNF,'1',0,I.VRCONTABILNF)) AS VRNAOTRIBUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
                          FROM NOTAFISCAL N, IMPOSTONF I                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                          WHERE I.NRSEQUMAPAPU    = V_NRSEQUMAPAPU                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                            AND I.CDIMPOSTO       = P_CDIMPOSTO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                            AND (NVL(N.IDSISTEMA, 'DFS') <> 'REC'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                            AND NVL(N.IDSISTEMA, 'DFS')  <> 'PAG'
                            AND NVL(N.IDSISTEMA, 'DFS')  <> 'CMP'
                            AND NVL(N.IDSISTEMA, 'DFS')  <> 'SUP'
                            AND NVL(N.IDSISTEMA, 'DFS')  <> 'FIS')
                            AND N.IDENTRSAIDOP    = 'S'
                            AND P_PERFIL = 'A'
                            AND NVL(I.IDIMPOSTONF, '1') <> '3'
                            AND N.CDFILIAL        = I.CDFILIAL
                            AND N.NRLANCTONF      = I.NRLANCTONF
                            AND ((N.IDSTATUS = 'A') OR (N.IDSTATUS ='E'))
                            AND N.CDMODDOCFISC IN ('21', '22')
                          GROUP BY DECODE(IDENTRSAIDOP, 'E', '0','1'), I.CDFILIAL, I.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR,
                                   N.NRINSJURNF, N.IDSTATUS, N.DTENTRSAID, N.VRNOTAFISC, N.VRPRODUTOS, N.IDPGTOFRETE,
                                   N.DTEMISSAO, N.CDMODDOCFISC, N.CDCLIENTE, N.CDFORNECED, N.IDORIGDESTNF,
                                   N.CDFILIEMIT, N.CDFILIRECE, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,
                                   N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU, NRACESSONFE
                          UNION ALL
                          /*************NOTA CANCELADA***************/
                          SELECT DECODE(IDENTRSAIDOP, 'E', '0','1') AS CAMPO2,
                                 '0' AS CAMPO3, NRACESSONFE,
                                 N.CDFILIAL, N.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, SUBSTR(N.CDSERIE,2,4) AS CDSERIE,
                                 SUBSTR(N.CDSERIEENTR,2,4) AS CDSERIEENTR, N.NRINSJURNF, N.IDSTATUS, N.DTENTRSAID, N.VRNOTAFISC, N.VRPRODUTOS,
                                 N.IDPGTOFRETE, N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU,
                                 N.CDFILIEMIT, N.CDFILIRECE, N.DTEMISSAO, '' AS CDFISCOPERNF, N.CDMODDOCFISC, N.CDCLIENTE, N.CDFORNECED, N.IDORIGDESTNF,
                                 SUM(N.VRNOTAFISC) AS VRCONTABILNF, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,
                                 0 AS VRBASECALCNF,
                                 0 AS VRIMPOSTONF,
                                 0 AS VROUTRCRED,
                                 0 AS VRISENTRIB,
                                 0 AS VRNAOTRIBUT
                          FROM NOTAFISCAL N
                          WHERE (NVL(N.IDSISTEMA, 'DFS') <> 'REC'
                            AND NVL(N.IDSISTEMA, 'DFS')  <> 'PAG'
                            AND NVL(N.IDSISTEMA, 'DFS')  <> 'CMP'
                            AND NVL(N.IDSISTEMA, 'DFS')  <> 'SUP'
                            AND NVL(N.IDSISTEMA, 'DFS')  <> 'FIS')
                            AND N.IDENTRSAIDOP    = 'S'
                            AND P_PERFIL = 'A'
                            AND N.IDSTATUS = 'C'
                            AND N.CDMODDOCFISC IN ('21', '22')
                            AND N.CDINSCESTA = P_CDINSCESTA
                            AND N.CDEMPRESA = P_CDEMPRESA
                          GROUP BY DECODE(IDENTRSAIDOP, 'E', '0','1'), N.CDFILIAL, N.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR,
                                   N.NRINSJURNF, N.IDSTATUS, N.DTENTRSAID, N.VRNOTAFISC, N.VRPRODUTOS, N.IDPGTOFRETE,
                                   N.DTEMISSAO, N.CDMODDOCFISC, N.CDCLIENTE, N.CDFORNECED, N.IDORIGDESTNF,
                                   N.CDFILIEMIT, N.CDFILIRECE, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,
                                   N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU, NRACESSONFE
                          UNION ALL
                          SELECT DECODE(IDENTRSAIDOP, 'E', '0','1') AS CAMPO2,
                                 '1' AS CAMPO3, NRACESSONFE,
                                 I.CDFILIAL, I.NRLANCTONF, N.IDENTRSAIDOP, N.NRNOTAFISC, SUBSTR(N.CDSERIE,2,4) AS CDSERIE,
                                 SUBSTR(N.CDSERIEENTR,2,4) AS CDSERIEENTR, N.NRINSJURNF, N.IDSTATUS, N.DTENTRSAID, N.VRNOTAFISC, N.VRPRODUTOS,
                                 N.IDPGTOFRETE, N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU,
                                 N.CDFILIEMIT, N.CDFILIRECE, N.DTEMISSAO, MAX(I.CDFISCOPERNF) AS CDFISCOPERNF, N.CDMODDOCFISC, N.CDCLIENTE, N.CDFORNECED, N.IDORIGDESTNF,
                                 SUM(I.VRCONTABILNF) AS VRCONTABILNF, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,
                                 SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                 SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VRIMPOSTONF,
                                 SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0))   AS VROUTRCRED,
                                 SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,'1',DECODE(I.VRPERREDBCNF,0, 0,I.VRCONTABILNF-I.VRBASECALCNF), 0)) AS VRISENTRIB,
                                 SUM(DECODE(I.CDFISCALNF,'1',0,I.VRCONTABILNF)) AS VRNAOTRIBUT
                          FROM NOTAFISCAL N, IMPOSTONF I
                          WHERE I.NRSEQUMAPAPU    = V_NRSEQUMAPAPU
                            AND I.CDIMPOSTO       = P_CDIMPOSTO
                            AND (NVL(N.IDSISTEMA, 'DFS')  <> 'REC'
                            AND NVL(N.IDSISTEMA, 'DFS')  <> 'PAG'
                            AND NVL(N.IDSISTEMA, 'DFS')  <> 'CMP'
                            AND NVL(N.IDSISTEMA, 'DFS')  <> 'SUP'
                            AND NVL(N.IDSISTEMA, 'DFS')  <> 'FIS' )
                            AND N.IDENTRSAIDOP    = 'E'
                            AND NVL(I.IDIMPOSTONF, '1') <> '3'
                            AND N.CDFILIAL        = I.CDFILIAL
                            AND N.NRLANCTONF      = I.NRLANCTONF
                            AND ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E'))
                            AND N.CDMODDOCFISC IN ('21', '22')
                          GROUP BY DECODE(IDENTRSAIDOP, 'E', '0','1'), I.CDFILIAL, I.NRLANCTONF, N.IDENTRSAIDOP,
                                 N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.NRINSJURNF, N.IDSTATUS, N.DTENTRSAID, NRACESSONFE,
                                 N.VRNOTAFISC, N.VRPRODUTOS, N.IDPGTOFRETE, N.DTEMISSAO, N.CDMODDOCFISC, N.CDCLIENTE,
                                 N.CDFORNECED, N.IDORIGDESTNF, N.CDFILIEMIT, N.CDFILIRECE, N.NRINSJURTRAN, N.IDTPIJURTRAN, N.VRPBRUTOVOLU, N.VRPLIQUVOLU,
                                 N.NRPLACAVEIC, N.DSQTDEVOLU, N.DSESPECVOLU, N.DSMARCAVOLU, N.DSNUMEVOLU, NRACESSONFE) NF,
                         (SELECT MAX(C.DTORIGVENREC) DTORIGVENREC,  N.CDFILIAL, N.NRLANCTONF
                          FROM NOTAFISCAL N, CONTARECEBER C
                          WHERE N.CDFILIAL   = C.CDFILIAL   AND
                                N.NRLANCTONF = C.NRLANCTONF
                          GROUP BY N.CDFILIAL, N.NRLANCTONF) PGTO
                    WHERE NF.CDFILIAL     = PGTO.CDFILIAL(+) AND
                          NF.NRLANCTONF   = PGTO.NRLANCTONF(+)   )LOOP
      BEGIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
         SELECT IDSTATUS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
         INTO V_IDSTATUS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
         FROM NOTAFISCAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
         WHERE CDFILIAL = C_REGD500.CDFILIAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
           AND NRLANCTONF = C_REGD500.NRLANCTONF;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
      EXCEPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         WHEN NO_DATA_FOUND THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
           V_IDSTATUS := NULL;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
      END;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
      BEGIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
         SELECT '1'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
         INTO V_DTPERIODO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
         FROM NOTAFISCAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
         WHERE CDFILIAL = C_REGD500.CDFILIAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
           AND NRLANCTONF = C_REGD500.NRLANCTONF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
           AND ((C_REGD500.IDENTRSAIDOP = 'S' AND (DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL) AND                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                (DTENTRSAID BETWEEN P_DTINICIAL AND P_DTFINAL)) OR                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                (DTENTRSAID BETWEEN P_DTINICIAL AND P_DTFINAL AND C_REGD500.IDENTRSAIDOP = 'E'));                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
      EXCEPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         WHEN NO_DATA_FOUND THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
           V_DTPERIODO := NULL;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
      END;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
      BEGIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
         SELECT DECODE(V_IDSTATUS, 'C', '02', DECODE(V_DTPERIODO, '1', '00', '01')) || '|'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
         INTO V_DADOS_2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
         FROM DUAL;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
      EXCEPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         WHEN NO_DATA_FOUND THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
            V_DADOS_2 := NULL;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
      END;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
      /* valor total de imposto fora do periodo - nf saida */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      V_VRFORAPER := 0;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      IF (V_DADOS_2 = '01|') AND (C_REGD500.IDENTRSAIDOP = 'S') THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
         BEGIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
            SELECT SUM(VRIMPOSTONF)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
            INTO V_VRFORAPER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
            FROM IMPOSTONF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
            WHERE CDFILIAL = C_REGD500.CDFILIAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
              AND NRLANCTONF = C_REGD500.NRLANCTONF;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
         EXCEPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
            WHEN NO_DATA_FOUND THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
               V_VRFORAPER := 0;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
         END;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
         V_TOTFORPER_S :=  NVL(V_TOTFORPER_S,0)+NVL(V_VRFORAPER,0);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
      END IF;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      /* valor total de imposto fora do periodo - nf entrada */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
      V_VRFORAPER_E := 0;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
      IF (V_DADOS_2 = '01|') AND (C_REGD500.IDENTRSAIDOP = 'E') THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
         BEGIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
            SELECT SUM(VRIMPOSTONF)
            INTO V_VRFORAPER_E
            FROM IMPOSTONF
            WHERE CDFILIAL = C_REGD500.CDFILIAL
              AND NRLANCTONF = C_REGD500.NRLANCTONF;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_VRFORAPER_E := 0;
         END;
         V_TOTFORPER_E :=  NVL(V_TOTFORPER_E,0)+NVL(V_VRFORAPER_E,0);
      END IF;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('43'||LPAD(RTRIM(V_CONTREGD), 8, '0'), C_REGD500.DADOS_1||V_DADOS_2||C_REGD500.DADOS_3, P_NRSEQARQMAG);
      V_CONTREGD :=  NVL(V_CONTREGD,0) + 1;
      V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');
      IF C_REGD500.IDENTRSAIDOP = 'S' THEN
         FOR C_REGD510 IN(SELECT (PR.CDARVPROD       ||'|'||
                                  ''                 ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(IT.QTITEMPROD,3), '999999999990.000')), '.', ',')       || '|' ||
                                  PR.SGUNIDADE       ||'|'||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(IT.VRUNITITEM,2), '999999999990.00')), '.', ',')        || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(IT.VRDESCITEM,2), '999999999990.00')), '.', ',')        || '|' ||
                                  LPAD(NVL(IP.CDSITUTRIBIT,0), 3, '0')           || '|' ||
                                  REPLACE(IP.CDFISCOPERIT,'.','')                || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(IP.CDFISCALIT, '1', IP.VRBASECALCIT, 0),2), '999999999990.00')), '.', ',')   || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(DECODE(IP.CDFISCALIT, '1', IP.VRALIQITEM, 0),2), '999999999990.00')), '.', ',')     || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(IP.VRIMPOSTOIT,2), '999999999990.00')), '.', ',')        || '|' ||   /* valor imposto item */
                                  REPLACE(LTRIM(TO_CHAR(ROUND(NVL(VALOR_IMPOSTO_ITEM(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, V_CDIMPOSTRIB,'A'),'0'),2), '999999999990.00')), '.', ',')   || '|' ||
                                  '0,00'      	      || '|' ||
                                  ''                 || '|' ||
                                  C_REGD500.CODPART  || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, '50', 'I'),2), '999999999990.00')), '.', ',')                    || '|' ||
                                  REPLACE(LTRIM(TO_CHAR(ROUND(VALOR_IMPOSTO_ITEM(IP.CDFILIAL, IP.NRLANCTONF, IP.NRSEQITPROD, '60', 'I'),2), '999999999990.00')), '.', ',')                    || '|' ||
                                  ''                 || '|' ) AS ARQ_D510
                            FROM ITEMPRODUTO IT, PRODUTO PR, ITEMPROIMPOS IP
                           WHERE IT.CDFILIAL      = C_REGD500.CDFILIAL
                             AND IT.NRLANCTONF    = C_REGD500.NRLANCTONF
                             AND IP.CDIMPOSTO     = P_CDIMPOSTO
                             AND IT.CDFILIAL      = IP.CDFILIAL
                             AND IT.NRLANCTONF    = IP.NRLANCTONF
                             AND IT.NRSEQITPROD   = IP.NRSEQITPROD
                             AND IT.CDPRODUTO     = PR.CDPRODUTO
                             AND IP.NRITPROIMPOS  = '1'
                             AND P_PERFIL         = 'A'
                             AND C_REGD500.IDENTRSAIDOP = 'S' )LOOP
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('43'||LPAD(RTRIM(V_CONTREGD), 8, '0'), '|D510'|| '|' ||V_SEQUENCIAL|| '|' || C_REGD510.ARQ_D510, P_NRSEQARQMAG);
            V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_SEQUENCIAL))+1)), 3,'0');
            V_CONTREGD :=  NVL(V_CONTREGD,0) + 1;
         END LOOP;
      END IF;
      /* valor de ipi por nf */
      SELECT NVL(SUM(VRIMPOSTONF),0)
      INTO V_VL_IP
      FROM IMPOSTONF
      WHERE CDFILIAL   = C_REGD500.CDFILIAL
        AND NRLANCTONF = C_REGD500.NRLANCTONF
        AND CDIMPOSTO  = V_IPI;
      /* valor de sub. tributaria por nf */
      SELECT NVL(SUM(VRBASECALCNF),0), NVL(SUM(VRIMPOSTONF),0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
        INTO V_VL_BC_ICMS_ST, V_VL_ICMS_ST                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
        FROM IMPOSTONF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
       WHERE CDFILIAL   = C_REGD500.CDFILIAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
         AND NRLANCTONF = C_REGD500.NRLANCTONF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
         AND CDIMPOSTO  = V_CDIMPOSTRIB;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
      /* valor reducao de base de calculo por nf */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
      SELECT NVL(SUM(VRCONTABILNF),0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
        INTO V_VL_RED_BC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
        FROM IMPOSTONF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
       WHERE CDFILIAL   = C_REGD500.CDFILIAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
         AND NRLANCTONF = C_REGD500.NRLANCTONF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
         AND CDIMPOSTO  = P_CDIMPOSTO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
         AND IDIMPOSTONF = '2';                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
      FOR C_REGD590 IN(SELECT ('|D590'|| '|' ||                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                               T.CDSITUTRIBNF|| '|' ||                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                               T.CDFISCOPERNF|| '|' ||                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                               REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VRALIQNF,0),2), '999999999990.00')), '.', ',')    || '|' ||                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                               REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VL_OPR,0),2), '999999999990.00')), '.', ',')    || '|' ||                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                               REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VL_BC_ICMS,0),2), '999999999990.00')), '.', ',')    || '|' ||                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
                               REPLACE(LTRIM(TO_CHAR(ROUND(NVL(T.VL_ICMS,0),2), '999999999990.00')), '.', ',')    || '|' ||                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                               REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VL_BC_ICMS_ST,0),2), '999999999990.00')), '.', ',')    || '|' ||                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                               REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VL_ICMS_ST,0),2), '999999999990.00')), '.', ',')    || '|' ||                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
                               REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VL_RED_BC,0),2), '999999999990.00')), '.', ',')    || '|' ||                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                               ''   || '|' ) ARQ_D590, T.VL_ICMS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                       FROM (SELECT VRALIQNF AS VRALIQNF,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                                    REPLACE(CDFISCOPERNF,'.','') AS CDFISCOPERNF,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                    LPAD(CDSITUTRIBNF, 3, '0') AS CDSITUTRIBNF,
                                    NVL(SUM(DECODE(IDIMPOSTONF, '1', VRCONTABILNF, 0)),0) AS VL_OPR,
                                    NVL(SUM(DECODE(IDIMPOSTONF, '1', VRBASECALCNF, 0)),0) AS VL_BC_ICMS,
                                    NVL(SUM(DECODE(IDIMPOSTONF, '1', VRIMPOSTONF, 0)),0) AS VL_ICMS
                               FROM IMPOSTONF
                              WHERE CDFILIAL   = C_REGD500.CDFILIAL
                                AND NRLANCTONF = C_REGD500.NRLANCTONF
                                AND CDIMPOSTO  = P_CDIMPOSTO
                                AND IDIMPOSTONF = 1
                                AND NRSEQUMAPAPU = V_NRSEQUMAPAPU
                              GROUP BY VRALIQNF, REPLACE(CDFISCOPERNF,'.',''), LPAD(CDSITUTRIBNF, 3, '0')) T) LOOP
         V_VL_BC_ICMS_ST := 0;
         V_VL_ICMS_ST := 0;
         V_VL_RED_BC := 0;
         V_VL_IP := 0;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('43'||LPAD(RTRIM(V_CONTREGD), 8, '0'), C_REGD590.ARQ_D590, P_NRSEQARQMAG);
         V_CONTREGD := NVL(V_CONTREGD,0) + 1;
      END LOOP;
   END LOOP;
   IF V_CONTREGD <> 0 THEN
      V_MOVIMENT := 0;
   ELSE
      V_MOVIMENT := 1;
   END IF;
   /* ABERTURA DO BLOCO D */
   V_CONTREGD := NVL(V_CONTREGD,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('41'||LPAD(RTRIM(V_CONTREGD), 8, '0'), '|D001'|| '|' ||V_MOVIMENT|| '|' , P_NRSEQARQMAG);
   /* ENCERRAMENTO DO BLOCO D */
   V_CONTREGD := NVL(V_CONTREGD,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('45'||LPAD(RTRIM(V_CONTREGD), 8, '0'), '|D990'|| '|' ||V_CONTREGD|| '|', P_NRSEQARQMAG);
   RETURN(LPAD(TO_CHAR(NVL(V_CONTREGD,0)),10, '0')||LPAD(TO_CHAR(NVL(V_TOTFORPER_S,0)),10, '0')||LPAD(TO_CHAR(NVL(V_VRFORAPER_E,0)),10, '0'));
END;
/

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