CREATE OR REPLACE FUNCTION GIAM_TO_SEGMENTO_A(
   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_IDTPESTAB     IN NOTAFISCAL.IDENTRSAIDOP%TYPE,
   P_IDPORTTARE    IN NOTAFISCAL.IDENTRSAIDOP%TYPE,
   P_IDTPESCRIT    IN NOTAFISCAL.IDENTRSAIDOP%TYPE,
   P_VRINICAIXA    IN NOTAFISCAL.VRNOTAFISC%TYPE,
   P_VRFIMCAIXA    IN NOTAFISCAL.VRNOTAFISC%TYPE,
   P_IDUSAECF      IN NOTAFISCAL.IDENTRSAIDOP%TYPE,
   P_CDREPRESENT   IN REPRESLEGAL.CDREPRES%TYPE,
   P_NRTARE        IN VARCHAR2,
   P_VERSAO        IN VARCHAR2,
   P_DTVENCTARE    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_IDMUDDOMICI   IN NOTAFISCAL.IDENTRSAIDOP%TYPE,
   P_CDMUNIANT     IN MUNICIPIO.CDMUNICIPIO%TYPE,
   P_DTINIMUNIANT  IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINMUNIANT  IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTINIMUNIATU  IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINMUNIATU  IN NOTAFISCAL.DTENTRSAID%TYPE) RETURN NUMBER
AS
   V_CONTREG NUMBER;
BEGIN
   FOR C_REG_A IN (SELECT 'A'                                 ||  --IDENTIFICACAO
                          RPAD(M.CDINSCESTA,11,'0')           ||
                          TO_CHAR(M.DTFIMPERIODO,'MMYYYY')    ||
                          P_IDRETIFIC                         ||
                          LPAD(NVL(E.CDCNAE,'0'),7,'0')       ||
                          P_IDTPESTAB                         ||
                          P_IDPORTTARE                        ||
                          P_IDTPESCRIT                        ||
                          REPLACE(LTRIM(TO_CHAR(NVL(P_VRINICAIXA,0),'000000000000.00')),'.') ||
                          REPLACE(LTRIM(TO_CHAR(NVL(P_VRFIMCAIXA,0),'000000000000.00')),'.') ||
                          P_IDUSAECF                          ||
                          LPAD(NVL(R.NRCPFREPRES,'0'),11,'0') ||
                          RPAD(NVL(R.NMREPRES,' '),50,' ')    ||
                          REPLACE(REPLACE(LPAD(REGEXP_REPLACE(C.NRCRCCONT2,'[[:alpha:]]'),10,'0'),'-'),'/') ||
                          RPAD(NVL(C.SGESTADOCRC,' '),2,' ')  ||
                          RPAD(NVL(C.NMCONTADOR,' '),50,' ')  ||
                          RPAD(NVL(TRIM(REPLACE(REPLACE(REPLACE(C.NRTELCONTATO,'('),')'),'-')),' '),20,' ') ||
                          REPLACE(LTRIM(TO_CHAR(NVL(R.VRNORMDEBI,0),'000000000000.00')),'.') || --DEBITO DO IMPOSTO
                          LPAD('0',14,'0')                    ||
                          REPLACE(LTRIM(TO_CHAR(NVL(R.VRESTOCRED,0),'000000000000.00')),'.') ||
                          REPLACE(LTRIM(TO_CHAR(NVL(R.VRNORMCRED,0),'000000000000.00')),'.') || --CREDITO DO IMPOSTO
                          LPAD('0',14,'0')                    ||
                          REPLACE(LTRIM(TO_CHAR(NVL(R.VRESTODEBI,0),'000000000000.00')),'.') ||
                          LPAD('0',14,'0')                    || --APURACAO DO PERIODO
                          LPAD('0',14,'0')                    ||
                          LPAD('0',14,'0')                    || --APURACAO DE SUBSTITUICAO TRIBUTARIA (VERIFICAR VALORES)
                          LPAD('0',14,'0')                    ||
                          LPAD('0',14,'0')                    ||
                          LPAD('0',14,'0')                    ||
                          LPAD('0',14,'0')                    ||
                          LPAD('0',14,'0')                    ||
                          RPAD(NVL(P_NRTARE,' '),20,' ')      || --INFORMACOES ADICIONAIS
                          RPAD(P_VERSAO,5,' ')                ||
                          RPAD(NVL(TO_CHAR(P_DTVENCTARE,'DDMMYYYY'),' '),8,' ') ||
                          LPAD('0',14,'0')                    ||
                          LPAD('0',14,'0')                    ||
                          ' '                                 ||
                          RPAD(' ',255,' ')                   ||
                          LPAD('0',14,'0')                    || --APURACAO DO PERIODO
                          LPAD('0',14,'0')                    ||
                          P_IDMUDDOMICI                       || --INFORMACOES ADICIONAIS
                          RPAD(NVL((SELECT CDMUNICIBGE FROM MUNICIPIO
                                     WHERE CDPAIS      = '0055'
                                       AND SGESTADO    = 'TO'
                                       AND CDMUNICIPIO = P_CDMUNIANT),' '),'7',' ') ||
                          RPAD(NVL(TO_CHAR(P_DTINIMUNIATU,'DDMMYYYY'),' '),8,' ')   ||
                          RPAD(NVL(TO_CHAR(P_DTFINMUNIATU,'DDMMYYYY'),' '),8,' ')   ||
                          RPAD(NVL(TO_CHAR(P_DTINIMUNIANT,'DDMMYYYY'),' '),8,' ')   ||
                          RPAD(NVL(TO_CHAR(P_DTFINMUNIANT,'DDMMYYYY'),' '),8,' ')   AS SEG_A
                     FROM MAPAAPURAC M, MAPARECUP R, EMPRESA E, REPRESLEGAL R, INSCRESTAD I, CONTADOR C
                    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.CDEMPRESA    = E.CDEMPRESA
                      AND R.CDREPRES     = P_CDREPRESENT
                      AND M.CDEMPRESA    = I.CDEMPRESA
                      AND M.CDINSCESTA   = I.CDINSCESTA
                      AND I.CDCONTADOR2  = C.CDCONTADOR2(+)
                      AND M.NRSEQUMAPAPU = R.NRSEQUMAPAPU) LOOP

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

      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('01'||LPAD(RTRIM(V_CONTREG), 8, '0'), C_REG_A.SEG_A, P_NRSEQARQMAG);

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

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