CREATE OR REPLACE FUNCTION GIAM_TO_SEGMENTO_D(
   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,
   P_DTINIMUNIATU  IN NOTAFISCAL.DTENTRSAID%TYPE) RETURN NUMBER
AS
   V_CONTREG NUMBER;
BEGIN
   FOR C_REG_D IN (SELECT 'D'                                 ||
                          LPAD(I.CDINSCESTA,11,'0')           ||
                          TO_CHAR(I.DTFIMPERIODO,'MMYYYY')    ||
                          P_IDRETIFIC                         ||
                          I.IDENTRSAIDOP                      ||
                          I.ESTADO                            ||
                          REPLACE(LTRIM(TO_CHAR(NVL(SUM(DECODE(I.IDCONTRIBICMS,'S',I.VRBASECALC,0)),0),'000000000000.00')),'.') ||
                          REPLACE(LTRIM(TO_CHAR(NVL(SUM(DECODE(I.IDCONTRIBICMS,'N',I.VRBASECALC,0)),0),'000000000000.00')),'.') ||
                          REPLACE(LTRIM(TO_CHAR(NVL(SUM(I.VRISENTO),0),'000000000000.00')),'.')      ||
                          REPLACE(LTRIM(TO_CHAR(NVL(SUM(CASE WHEN NVL(I.VRCONTABILNF,0) - NVL(I.VRBASECALC,0) - NVL(I.VRISENTO,0) - NVL(I.VRIMPST,0) > 0
                                                       THEN NVL(I.VRCONTABILNF,0) - NVL(I.VRBASECALC,0) - NVL(I.VRISENTO,0) - NVL(I.VRIMPST,0)
                                                       ELSE 0 END),0),'000000000000.00')),'.') ||
                          REPLACE(LTRIM(TO_CHAR(NVL(SUM(I.VRIMPST),0),'000000000000.00')),'.')       ||
                          REPLACE(LTRIM(TO_CHAR(NVL(SUM(DECODE(I.IDCONTRIBICMS,'S',I.VRCONTABILNF,0)),0),'000000000000.00')),'.') ||
                          REPLACE(LTRIM(TO_CHAR(NVL(SUM(DECODE(I.IDCONTRIBICMS,'N',I.VRCONTABILNF,0)),0),'000000000000.00')),'.') ||
                          REPLACE(LTRIM(TO_CHAR(NVL(SUM(DECODE(I.IDCONTRIBICMS,'S',I.VRIMPOSTO,0)),0),'000000000000.00')),'.') ||
                          REPLACE(LTRIM(TO_CHAR(NVL(SUM(DECODE(I.IDCONTRIBICMS,'N',I.VRIMPOSTO,0)),0),'000000000000.00')),'.') ||
                          I.DOMICILIOFISC AS SEG_D
                     FROM (SELECT M.CDINSCESTA, M.DTFIMPERIODO,
                                  DECODE(N.IDENTRSAIDOP,'E','0','1') AS IDENTRSAIDOP,
                                  NVL(S.CDESTAEXPO,E.SGESTADO) AS ESTADO, 'S' AS IDCONTRIBICMS,
                                  SUM(ROUND(I.VRBASECALCNF,2)) AS VRBASECALC,
                                  SUM(ROUND(CASE WHEN I.CDFISCALNF IN ('2','3') AND I.IDIMPOSTONF <> '2'
                                                 THEN I.VRCONTABILNF ELSE 0 END,2)) AS VRISENTO,
                                  SUM(ROUND(I.VRCONTABILNF,2)) AS VRCONTABILNF, 0 AS VRIMPST,
                                  SUM(ROUND(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0),2)) AS VRIMPOSTO,
                                  CASE WHEN N.DTENTRSAID < P_DTINIMUNIATU THEN 'B' ELSE 'A' END AS DOMICILIOFISC
                             FROM MAPAAPURAC M, IMPOSTONF I, NOTAFISCAL N, ENDEFORN E, ESTADO S
                            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 = I.NRSEQUMAPAPU
                              AND I.CDFILIAL     = N.CDFILIAL
                              AND I.NRLANCTONF   = N.NRLANCTONF
                              AND N.CDFORNECED   = E.CDFORNECED
                              AND E.IDTPENDEFORN = 'P'
                              AND E.SGESTADO     = S.SGESTADO
                              AND E.CDPAIS       = S.CDPAIS
                            GROUP BY M.CDINSCESTA, M.DTFIMPERIODO, N.IDENTRSAIDOP, NVL(S.CDESTAEXPO,E.SGESTADO),
                                  CASE WHEN N.DTENTRSAID < P_DTINIMUNIATU THEN 'B' ELSE 'A' END
                            UNION ALL
                           SELECT M.CDINSCESTA, M.DTFIMPERIODO,
                                  DECODE(N.IDENTRSAIDOP,'E','0','1') AS IDENTRSAIDOP,
                                  NVL(S.CDESTAEXPO,E.SGESTADO) AS ESTADO, DECODE(N.IDENTRSAIDOP,'E','S',C.IDCONTRIBICMS) AS IDCONTRIBICMS,
                                  SUM(ROUND(I.VRBASECALCNF,2)) AS VRBASECALC,
                                  SUM(ROUND(CASE WHEN I.CDFISCALNF IN ('2','3') AND I.IDIMPOSTONF <> '2'
                                                 THEN I.VRCONTABILNF ELSE 0 END,2)) AS VRISENTO,
                                  SUM(ROUND(I.VRCONTABILNF,2)) AS VRCONTABILNF, 0 AS VRIMPST,
                                  SUM(ROUND(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0),2)) AS VRIMPOSTO,
                                  CASE WHEN N.DTENTRSAID < P_DTINIMUNIATU THEN 'B' ELSE 'A' END AS DOMICILIOFISC
                             FROM MAPAAPURAC M, IMPOSTONF I, NOTAFISCAL N, CLIENTE C, ENDECLIE E, ESTADO S
                            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 = I.NRSEQUMAPAPU
                              AND I.CDFILIAL     = N.CDFILIAL
                              AND I.NRLANCTONF   = N.NRLANCTONF
                              AND N.CDCLIENTE    = C.CDCLIENTE
                              AND C.CDCLIENTE    = E.CDCLIENTE
                              AND E.IDTPENDECLIE = 'P'
                              AND E.SGESTADO     = S.SGESTADO
                              AND E.CDPAIS       = S.CDPAIS
                            GROUP BY M.CDINSCESTA, M.DTFIMPERIODO, N.IDENTRSAIDOP, NVL(S.CDESTAEXPO,E.SGESTADO),
                                  CASE WHEN N.DTENTRSAID < P_DTINIMUNIATU THEN 'B' ELSE 'A' END,
                                  DECODE(N.IDENTRSAIDOP,'E','S',C.IDCONTRIBICMS)
                            UNION ALL
                           SELECT M.CDINSCESTA, M.DTFIMPERIODO,
                                  DECODE(N.IDENTRSAIDOP,'E','0','1') AS IDENTRSAIDOP,
                                  NVL(S.CDESTAEXPO,E.SGESTADO) AS ESTADO, 'S' AS IDCONTRIBICMS,
                                  SUM(ROUND(I.VRBASECALCNF,2)) AS VRBASECALC,
                                  SUM(ROUND(CASE WHEN I.CDFISCALNF IN ('2','3') AND I.IDIMPOSTONF <> '2'
                                                 THEN I.VRCONTABILNF ELSE 0 END,2)) AS VRISENTO,
                                  SUM(ROUND(I.VRCONTABILNF,2)) AS VRCONTABILNF, 0 AS VRIMPST,
                                  SUM(ROUND(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0),2)) AS VRIMPOSTO,
                                  CASE WHEN N.DTENTRSAID < P_DTINIMUNIATU THEN 'B' ELSE 'A' END AS DOMICILIOFISC
                             FROM MAPAAPURAC M, IMPOSTONF I, NOTAFISCAL N, ENDEFILI E, ESTADO S
                            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 = I.NRSEQUMAPAPU
                              AND I.CDFILIAL     = N.CDFILIAL
                              AND I.NRLANCTONF   = N.NRLANCTONF
                              AND DECODE(N.IDENTRSAIDOP,'E',N.CDFILIEMIT,N.CDFILIRECE) = E.CDFILIAL
                              AND E.IDTPENDEFILI = 'P'
                              AND E.SGESTADO     = S.SGESTADO
                              AND E.CDPAIS       = S.CDPAIS
                            GROUP BY M.CDINSCESTA, M.DTFIMPERIODO, N.IDENTRSAIDOP, NVL(S.CDESTAEXPO,E.SGESTADO),
                                  CASE WHEN N.DTENTRSAID < P_DTINIMUNIATU THEN 'B' ELSE 'A' END)I
                    GROUP BY I.CDINSCESTA, I.DTFIMPERIODO, I.IDENTRSAIDOP, I.ESTADO, I.DOMICILIOFISC) LOOP

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

      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('04'||LPAD(RTRIM(V_CONTREG), 8, '0'), C_REG_D.SEG_D, P_NRSEQARQMAG);

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