CREATE OR REPLACE FUNCTION GIAM_TO_SEGMENTO_P(
   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_P IN (SELECT 'P'                                 ||
                          RPAD(I.CDINSCESTA,11,'0')           ||
                          TO_CHAR(I.DTFIMPERIODO,'MMYYYY')    ||
                          P_IDRETIFIC                         ||
                          I.DOMICILIOFISC                     ||
                          I.ESTADO                            ||
                          REPLACE(LTRIM(TO_CHAR(NVL(SUM(I.VRCONTABILNF),0),'000000000000.00')),'.') ||
                          REPLACE(LTRIM(TO_CHAR(NVL(SUM(I.VRBASECALC),0),'000000000000.00')),'.')   ||
                          REPLACE(LTRIM(TO_CHAR(NVL(SUM(I.VRIMPOSTO),0),'000000000000.00')),'.') ||
                          REPLACE(LTRIM(TO_CHAR(NVL(I.ALIQ,0),'00.00')),'.') AS SEG_P
                     FROM (SELECT M.CDINSCESTA, M.DTFIMPERIODO,
                                  NVL(S.CDESTAEXPO,E.SGESTADO) AS ESTADO,
                                  SUM(ROUND(I.VRBASECALCNF,2)) AS VRBASECALC,
                                  SUM(ROUND(I.VRCONTABILNF,2)) AS VRCONTABILNF,
                                  SUM(ROUND(I.VRIMPOSTONF,2)) AS VRIMPOSTO,
                                  CASE WHEN N.DTENTRSAID < P_DTINIMUNIATU THEN 'B' ELSE 'A' END AS DOMICILIOFISC,
                                  CONSULTA_QUADRO_ALIQUOTAS(E.SGESTADO,'TO') AS ALIQ
                             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
                              AND I.IDIMPOSTONF  = '3'
                            GROUP BY M.CDINSCESTA, M.DTFIMPERIODO, NVL(S.CDESTAEXPO,E.SGESTADO),
                                  CASE WHEN N.DTENTRSAID < P_DTINIMUNIATU THEN 'B' ELSE 'A' END,
                                  CONSULTA_QUADRO_ALIQUOTAS(E.SGESTADO,'TO'))I
                    GROUP BY I.CDINSCESTA, I.DTFIMPERIODO, I.ESTADO, I.DOMICILIOFISC, ALIQ) LOOP

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

      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('16'||LPAD(RTRIM(V_CONTREG), 8, '0'), C_REG_P.SEG_P, P_NRSEQARQMAG);

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

-- VERSAO --
BEGIN ATUVEROBJ('GIAM_TO_SEGMENTO_P','0.00.000'); END;
/