CREATE OR REPLACE FUNCTION GIAM_TO_SEGMENTO_E(
   P_DTINICIO      IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFIM         IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_CDEMPRESA     IN MAPAAPURAC.CDEMPRESA%TYPE,
   P_CDINSCESTA    IN MAPAAPURAC.CDINSCESTA%TYPE,
   P_NRSEQARQMAG   IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_IDRETIFIC     IN NOTAFISCAL.CDTIPOOPER%TYPE) RETURN NUMBER
AS
   V_CONTREG NUMBER;
BEGIN
   FOR C_REG_E IN (SELECT 'E'                               ||
                          LPAD(CDINSCESTA,11,'0')           ||
                          TO_CHAR(DTFIMPERIODO,'MMYYYY')    ||
                          P_IDRETIFIC                       ||
                          TIPO                              ||
                          LPAD(NVL(TO_CHAR(DTVENCIMENTO,'DDMMYYYY'),' '),8,' ')  ||
                          REPLACE(LTRIM(TO_CHAR(NVL(VRRECOLHER,0),'000000000000.00')),'.') AS SEG_E
                     FROM (SELECT M.CDINSCESTA, M.DTFIMPERIODO, M.VRRECEGUIA AS VRRECOLHER,
                                  M.DTVENCMAPA AS DTVENCIMENTO, 'N' AS TIPO
                             FROM MAPAAPURAC M
                            WHERE M.CDEMPRESA    = P_CDEMPRESA
                              AND M.CDINSCESTA   = P_CDINSCESTA
                              AND M.DTINIPERIODO = P_DTINICIO
                              AND M.DTFIMPERIODO = P_DTFIM
                              AND M.CDIMPOSTO    = '00'
                            UNION ALL
                           SELECT M.CDINSCESTA, M.DTFIMPERIODO, G.VRRECEITAGUIA AS VRRECOLHER,
                                  G.DTVENCGUIACOMPL AS DTVENCIMENTO,
                                  NVL(SUBSTR(G.DSHISTGUIACOMPL,1,1),'D') AS TIPO
                             FROM MAPAAPURAC M, GUIACOMPLMAPA G
                            WHERE M.CDEMPRESA    = P_CDEMPRESA
                              AND M.CDINSCESTA   = P_CDINSCESTA
                              AND M.DTINIPERIODO = P_DTINICIO
                              AND M.DTFIMPERIODO = P_DTFIM
                              AND M.CDIMPOSTO    = '00'
                              AND M.NRSEQUMAPAPU = G.NRSEQUMAPAPU)) LOOP

      V_CONTREG := NVL(V_CONTREG,0) + 1;

      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('05'||LPAD(RTRIM(V_CONTREG), 8, '0'), C_REG_E.SEG_E, P_NRSEQARQMAG);

   END LOOP;
   RETURN(NVL(V_CONTREG,0));
END;