create or replace
FUNCTION ARQUIVO_88_GIM_PB(
   P_CDIMPOSTO    IN IMPOSTO.CDIMPOSTO%TYPE,
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA   IN EMPRESA.CDINSCESTA%TYPE,
   P_CDINSCESTAST IN NOTAFISCAL.CDINSCESTAST%TYPE,
   P_FINALIDADE   IN NOTAFISCAL.IDSTATUS%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_SEQMAGNET    IN VARCHAR,
   P_CONTADOR     IN NUMBER,
   P_CDLIVROMAG   IN NOTAFISCAL.NRLANCTONF%TYPE,
   P_RECIBO       IN VARCHAR) RETURN VARCHAR
AS
   V_REG88_01          VARCHAR2(4000);
   V_REG88_02          VARCHAR2(4000);
   V_REG88_04          VARCHAR2(4000);
   V_REG88_23          VARCHAR2(4000);
   V_NRCPFRESPONS      VARCHAR2(4000);
   V_NRCRCCONT2        VARCHAR2(4000);
   V_NMCONTADOR        VARCHAR2(4000);
   V_NRTELCONTATO      VARCHAR2(4000);
   V_DSENDINTERN       VARCHAR2(4000);
   V_LINHA             VARCHAR2(4000);
   V_LINHA_15          VARCHAR2(4000);
   V_LINHA_16          VARCHAR2(4000);
   V_CDIMPOSTOLIVRO    VARCHAR2(02);
   V_CONTREG           NUMBER;
   V_CONTGERAL         NUMBER;
   V_CONTADOR          NUMBER;
   V_VRENTRADAS        NUMBER;
   V_VRFATURAMENTO     NUMBER;
   V_VRDIFERENCA       NUMBER;
   V_VR_TOT_FATUR      NUMBER;
   V_NRSEQUMAPAPU      VARCHAR2(10);
   V_VRDIPAMGIA        NUMBER;
   V_PERCENTUAL        NUMBER;
   V_TPDECLARE         VARCHAR2(1);
BEGIN

   V_CONTREG  := 0;
   V_CONTADOR := P_CONTADOR;
    IF P_FINALIDADE = '1' THEN
       V_TPDECLARE := 'N';
    ELSE
       V_TPDECLARE := 'R';
    END IF;

   IF P_CDINSCESTAST IS NULL THEN
       /* registro 88 01 credito de icms */
      BEGIN
         SELECT MAX('88'||
                '01'||
                SUBSTR(REPLACE(REPLACE(REPLACE(P_CDINSCESTAST,'.',''),'/',''),'-',''),1,9)||
                TO_CHAR(DTINIPERIODO, 'yyyymm')||
                DECODE(P_FINALIDADE,'1','N','R')||
                LTRIM(REPLACE(TO_CHAR(ABS(R.VRNORMCRED),'00000000000.00'),'.',''))||
                RPAD('0',13,'0')||
                RPAD('0',13,'0')||
                RPAD('0',13,'0')||
                RPAD('0',13,'0')||
                LTRIM(REPLACE(TO_CHAR(ABS(R.VROUTRCRED),'00000000000.00'),'.',''))||
                LTRIM(REPLACE(TO_CHAR(ABS(R.VRESTODEBI),'00000000000.00'),'.',''))||
                LTRIM(REPLACE(TO_CHAR(ABS(R.VRSLDCRANTE),'00000000000.00'),'.',''))||
                RPAD(' ',2,' ')), M.NRSEQUMAPAPU
           INTO V_REG88_01, V_NRSEQUMAPAPU
           FROM MAPAAPURAC M, MAPARECUP R
          WHERE M.CDEMPRESA     = P_CDEMPRESA
            AND M.CDINSCESTA    = P_CDINSCESTA
            AND M.DTINIPERIODO >= P_DTINICIAL
            AND M.DTFIMPERIODO <= P_DTFINAL
            AND M.CDIMPOSTO     = P_CDIMPOSTO
            AND M.NRSEQUMAPAPU  = R.NRSEQUMAPAPU
          GROUP BY M.NRSEQUMAPAPU;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
            V_REG88_01     := NULL;
            V_NRSEQUMAPAPU := NULL;
      END;
         IF V_REG88_01 IS NOT NULL THEN
            V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
            V_CONTADOR := NVL(V_CONTADOR,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('88'||LPAD(V_CONTADOR, 8, '0'), V_REG88_01, P_SEQMAGNET);
         END IF;

       /* registro 88 02 debito de icms */
      BEGIN
          SELECT MAX('88'||
                 '02'||
                 SUBSTR(REPLACE(REPLACE(REPLACE(P_CDINSCESTAST,'.',''),'/',''),'-',''),1,9)||
                 TO_CHAR(DTINIPERIODO, 'yyyymm')||
                 DECODE(P_FINALIDADE,'1','N','R')||
                 LTRIM(REPLACE(TO_CHAR(ABS(R.VRNORMDEBI),'00000000000.00'),'.',''))||
                 RPAD('0',13,'0')||
                 LTRIM(REPLACE(TO_CHAR(ABS(R.VROUTRDEBI),'00000000000.00'),'.',''))||
                 LTRIM(REPLACE(TO_CHAR(ABS(R.VRESTOCRED),'00000000000.00'),'.',''))||
                 RPAD('0',13,'0')||
                 RPAD('0',13,'0')||
                 RPAD('0',13,'0')||
                 RPAD('0',13,'0')||
                 RPAD(' ',2,' '))
            INTO V_REG88_02
            FROM MAPAAPURAC M, MAPARECUP R
           WHERE M.CDEMPRESA     = P_CDEMPRESA
             AND M.CDINSCESTA    = P_CDINSCESTA
             AND M.DTINIPERIODO >= P_DTINICIAL
             AND M.DTFIMPERIODO <= P_DTFINAL
             AND M.CDIMPOSTO     = P_CDIMPOSTO
             AND M.NRSEQUMAPAPU  = R.NRSEQUMAPAPU;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
            V_REG88_02 := NULL;
      END;

         IF V_REG88_02 IS NOT NULL THEN
            V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
            V_CONTADOR := NVL(V_CONTADOR,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('88'||LPAD(V_CONTADOR, 8, '0'), V_REG88_02, P_SEQMAGNET);
         END IF;
       /* registro 88 04 - informacoes complementares */
       SELECT LPAD(NRCPFRESPONS, 14, '0'),
              RPAD(NVL(SUBSTR(REPLACE(NRCRCCONT2,'/',''),2,11),' '), 10, '0'),
              LPAD(NVL(NMCONTADOR,' '), 40, ' '),
              LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRTELCONTATO,')',''),'(',''),'-',''),' ',''), 12, '0'),
              LPAD(NVL(DSENDINTERN,' '), 40, ' ')
         INTO V_NRCPFRESPONS, V_NRCRCCONT2, V_NMCONTADOR, V_NRTELCONTATO, V_DSENDINTERN
         FROM CONTADOR C, INSCRESTAD I
        WHERE I.CDEMPRESA   = P_CDEMPRESA
          AND I.CDINSCESTA  = P_CDINSCESTA
          AND I.CDCONTADOR2 = C.CDCONTADOR2;
      BEGIN
          SELECT MAX('88'||
                 '04'||
                 SUBSTR(REPLACE(REPLACE(REPLACE(P_CDINSCESTAST,'.',''),'/',''),'-',''),1,9)||
                 TO_CHAR(DTINIPERIODO, 'yyyymm')||
                 DECODE(P_FINALIDADE,'1','N','R')||
                 LTRIM(REPLACE(TO_CHAR(ABS(R.VRDIFALIQU),'00000000000.00'),'.',''))||
                 RPAD('0',13,'0')||
                 V_DSENDINTERN||
                 TO_CHAR(M.DTINIPERIODO, 'yyyymmdd')||
                 '0000'|| --verificar versao
                 '1'||
                 RPAD(' ',27,' '))
            INTO V_REG88_04
            FROM MAPAAPURAC M, MAPARECUP R
           WHERE M.CDEMPRESA     = P_CDEMPRESA
             AND M.CDINSCESTA    = P_CDINSCESTA
             AND M.DTINIPERIODO >= P_DTINICIAL
             AND M.DTFIMPERIODO <= P_DTFINAL
             AND M.CDIMPOSTO     = P_CDIMPOSTO
             AND M.NRSEQUMAPAPU  = R.NRSEQUMAPAPU;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
            V_REG88_04 := NULL;
      END;
         IF V_REG88_04 IS NOT NULL THEN
            V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
            V_CONTADOR := NVL(V_CONTADOR,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('88'||LPAD(V_CONTADOR, 8, '0'), V_REG88_04, P_SEQMAGNET);
         END IF;
       /* registro 88 05 informacoes do contabilista */
       V_LINHA := '88'||
                  '05'||
                  V_NRCPFRESPONS||
                  V_NRCRCCONT2  ||
                  V_NMCONTADOR  ||
                  V_NRTELCONTATO||
                  V_DSENDINTERN ||
                  RPAD(' ',6, ' ');

          V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
          V_CONTADOR := NVL(V_CONTADOR,0) + 1;
          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            ('88'||LPAD(V_CONTADOR, 8, '0'), V_LINHA, P_SEQMAGNET);

       /* registro 88 15 credito presumido */
       V_LINHA_15 := '88'||
                     '15'||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD(' ',18,' ');

       V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
       V_CONTADOR := NVL(V_CONTADOR,0) + 1;
       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         ('88'||LPAD(V_CONTADOR, 8, '0'), V_LINHA_15, P_SEQMAGNET);

       /* registro 88 16 deducoes do fundo de combate */
       V_LINHA_16 := '88'||
                     '16'||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD(' ',57,' ');

       V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
       V_CONTADOR := NVL(V_CONTADOR,0) + 1;
       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         ('88'||LPAD(V_CONTADOR, 8, '0'), V_LINHA_16, P_SEQMAGNET);

       /* registro 88 21 informacoes dos ajustes a debito de sub. trib. */
       V_CDIMPOSTOLIVRO := '80';
      BEGIN
         SELECT MAX('88'||
                '23'||
                TO_CHAR(P_DTINICIAL, 'yyyymm')||
                LTRIM(REPLACE(TO_CHAR(ABS(R.VROUTRDEBI),'00000000000.00'),'.',''))||
                RPAD(' ',103,' '))
           INTO V_REG88_23
           FROM MAPAAPURAC M, MAPARECUP R
          WHERE M.CDEMPRESA     = P_CDEMPRESA
            AND M.CDINSCESTA    = P_CDINSCESTA
            AND M.DTINIPERIODO >= P_DTINICIAL
            AND M.DTFIMPERIODO <= P_DTFINAL
            AND M.CDIMPOSTO     = P_CDIMPOSTO
            AND M.NRSEQUMAPAPU  = R.NRSEQUMAPAPU;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
            V_REG88_23 := NULL;
      END;

         IF V_REG88_23 IS NOT NULL THEN
            V_CONTADOR := NVL(V_CONTADOR,0) + 1;
            V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('88'||LPAD(V_CONTADOR, 8, '0'), V_REG88_23, P_SEQMAGNET);
            V_CONTREG   := NVL(V_CONTREG,0) + 1;
            V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
         END IF;

      /* REGISTRO 88 29 */
       BEGIN
         SELECT SUM(VRCONTABILNF) AS VRFATURAMENTO
           INTO V_VRFATURAMENTO
           FROM IMPOSTONF I, IMPOSTO IP
          WHERE NRSEQUMAPAPU    = V_NRSEQUMAPAPU
            AND IP.CDIMPOSTONFE = P_CDIMPOSTO
            AND IP.CDIMPOSTO    = I.CDIMPOSTO
            AND CDFISCOPERNF IN ('5102','6102','5403','6403','5401', '6401');
       EXCEPTION
       WHEN NO_DATA_FOUND THEN
            V_VRFATURAMENTO := NULL;
       END;
       BEGIN
         SELECT SUM(VRCONTABILNF) AS VRENTRADAS
           INTO V_VRENTRADAS
           FROM IMPOSTONF I, IMPOSTO IP
          WHERE NRSEQUMAPAPU    = V_NRSEQUMAPAPU
            AND IP.CDIMPOSTONFE = P_CDIMPOSTO
            AND IP.CDIMPOSTO    = I.CDIMPOSTO
            AND CDFISCOPERNF IN ('1102','2102','1403','2403','1401', '2401');
       EXCEPTION
       WHEN NO_DATA_FOUND THEN
            V_VRENTRADAS := NULL;
       END;
       V_VRDIFERENCA := NVL(V_VRENTRADAS,0) - NVL(V_VRFATURAMENTO,0);

       FOR C_DIMPA_GIA IN (SELECT SUM(VRFATURAMENTO) AS VRFATURAMENTO, CDMUNIEXPO
                             FROM (
                                   SELECT SUM(VRCONTABILNF) AS VRFATURAMENTO, CDFILIEMIT,
                                          (SELECT MAX(CDMUNIEXPO) AS CDMUNIEXPO
                                             FROM FILIAL F, ENDEFILI E, MUNICIPIO M
                                            WHERE F.CDFILIAL    = N.CDFILIEMIT
                                              AND E.CDFILIAL    = F.CDFILIAL
                                              AND E.CDPAIS      = M.CDPAIS
                                              AND E.SGESTADO    = M.SGESTADO
                                              AND E.CDMUNICIPIO = M.CDMUNICIPIO) AS CDMUNIEXPO,
                                           (SELECT MAX(NMMUNICIPIO) AS NMMUNICIPIO
                                             FROM FILIAL F, ENDEFILI E, MUNICIPIO M
                                            WHERE F.CDFILIAL    = N.CDFILIEMIT
                                              AND E.CDFILIAL    = F.CDFILIAL
                                              AND E.CDPAIS      = M.CDPAIS
                                              AND E.SGESTADO    = M.SGESTADO
                                              AND E.CDMUNICIPIO = M.CDMUNICIPIO) AS NMMUNICIPIO
                                     FROM IMPOSTONF I, NOTAFISCAL N, IMPOSTO IP
                                    WHERE NRSEQUMAPAPU    = V_NRSEQUMAPAPU
                                      AND IP.CDIMPOSTONFE = P_CDIMPOSTO
                                      AND IP.CDIMPOSTO    = I.CDIMPOSTO
                                      AND CDFISCOPERNF IN ('5102','6102','5403','6403','5401', '6401')
                                      AND I.CDFILIAL      = N.CDFILIAL
                                      AND I.NRLANCTONF    = N.NRLANCTONF
                                    GROUP BY CDFILIEMIT)
                            GROUP BY CDMUNIEXPO ) LOOP
          V_PERCENTUAL := TRUNC(NVL(C_DIMPA_GIA.VRFATURAMENTO,0) / NVL(V_VRFATURAMENTO,0),6);
          V_VRDIPAMGIA := ROUND(NVL(V_VRDIFERENCA,0)* V_PERCENTUAL,2);
          V_LINHA := '8829'||
                     NVL(C_DIMPA_GIA.CDMUNIEXPO,'INFORME O MUNICIPIO')||
                     LTRIM(REPLACE(TO_CHAR(ABS(V_VRDIPAMGIA),'00000000000.00'),'.',''))||
                     RPAD(' ', 104, ' ');
          V_CONTADOR := NVL(V_CONTADOR,0) + 1;
          V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            ('88'||LPAD(V_CONTADOR, 8, '0'), V_LINHA, P_SEQMAGNET);
          V_LINHA := NULL;
       END LOOP;

       /* REGISTRO 88 30 */
       V_LINHA := '8830'||
                  LPAD(NVL(P_RECIBO,' '),32,' ')||
                  '0'||
                  LPAD('0', 8, '0')||
                  LPAD(' ', 81, ' ');
       V_CONTADOR := NVL(V_CONTADOR,0) + 1;
       V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         ('88'||LPAD(V_CONTADOR, 8, '0'), V_LINHA, P_SEQMAGNET);
       V_LINHA := NULL;

       IF V_CONTREG > 0 THEN
          RETURN(TO_CHAR(LPAD(V_CONTGERAL,10,0))||TO_CHAR(LPAD(V_CONTADOR,10,0)));
       END IF;
       IF  V_CONTREG = 0 THEN
          RETURN('0');
       END IF;
   ELSE
      /* registro 88 01 credito de icms */
     BEGIN
       SELECT MAX('88'||
              '01'||
              SUBSTR(REPLACE(REPLACE(REPLACE(P_CDINSCESTAST,'.',''),'/',''),'-',''),1,9)||
              TO_CHAR(DTINIPERIODO, 'yyyymm')||
              DECODE(P_FINALIDADE,'1','N','R')||
              LTRIM(REPLACE(TO_CHAR(ABS(IP.VRIMPOSTONF),'00000000000.00'),'.',''))||
              RPAD('0',13,'0')||
              RPAD('0',13,'0')||
              RPAD('0',13,'0')||
              RPAD('0',13,'0')||
              LTRIM(REPLACE(TO_CHAR(ABS(0),'00000000000.00'),'.',''))||
              LTRIM(REPLACE(TO_CHAR(ABS(0),'00000000000.00'),'.',''))||
              LTRIM(REPLACE(TO_CHAR(ABS(0),'00000000000.00'),'.',''))||
              RPAD(' ',2,' ')), M.NRSEQUMAPAPU
         INTO V_REG88_01, V_NRSEQUMAPAPU
         FROM MAPAAPURAC M, IMPOSTONF IP, NOTAFISCAL N
        WHERE M.CDEMPRESA     = P_CDEMPRESA
          AND M.CDINSCESTA    = P_CDINSCESTA
          AND M.DTINIPERIODO >= P_DTINICIAL
          AND M.DTFIMPERIODO <= P_DTFINAL
          AND M.CDIMPOSTO     = P_CDIMPOSTO
          AND M.NRSEQUMAPAPU  = IP.NRSEQUMAPAPU
          AND N.CDINSCESTAST  = P_CDINSCESTAST
          AND N.CDFILIAL      = IP.CDFILIAL
          AND N.NRLANCTONF    = IP.NRLANCTONF
          AND IP.CDIMPOSTO    = P_CDIMPOSTO
          AND IDENTRSAIDOP    = 'E'
        GROUP BY M.NRSEQUMAPAPU;
     EXCEPTION
      WHEN NO_DATA_FOUND THEN
           V_REG88_01 := '88'||
                         '01'||
                         SUBSTR(REPLACE(REPLACE(REPLACE(P_CDINSCESTAST,'.',''),'/',''),'-',''),1,9)||
                         TO_CHAR(P_DTINICIAL, 'yyyymm')||
                         V_TPDECLARE||
                         LTRIM(REPLACE(TO_CHAR(ABS(0),'00000000000.00'),'.',''))||
                         RPAD('0',13,'0')||
                         RPAD('0',13,'0')||
                         RPAD('0',13,'0')||
                         RPAD('0',13,'0')||
                         LTRIM(REPLACE(TO_CHAR(ABS(0),'00000000000.00'),'.',''))||
                         LTRIM(REPLACE(TO_CHAR(ABS(0),'00000000000.00'),'.',''))||
                         LTRIM(REPLACE(TO_CHAR(ABS(0),'00000000000.00'),'.',''))||
                         RPAD(' ',2,' ');
           V_NRSEQUMAPAPU := NULL;
      END;
          IF V_REG88_01 IS NOT NULL THEN
             V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
             V_CONTADOR := NVL(V_CONTADOR,0) + 1;
             INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
               ('88'||LPAD(V_CONTADOR, 8, '0'), V_REG88_01, P_SEQMAGNET);
          END IF;
       /* registro 88 02 debito de icms */
       BEGIN
          SELECT MAX('88'||
                 '02'||
                 SUBSTR(REPLACE(REPLACE(REPLACE(P_CDINSCESTAST,'.',''),'/',''),'-',''),1,9)||
                 TO_CHAR(DTINIPERIODO, 'yyyymm')||
                 DECODE(P_FINALIDADE,'1','N','R')||
                 LTRIM(REPLACE(TO_CHAR(ABS(SUM(IP.VRIMPOSTONF)),'00000000000.00'),'.',''))||
                 RPAD('0',13,'0')||
                 LTRIM(REPLACE(TO_CHAR(ABS(0),'00000000000.00'),'.',''))||
                 LTRIM(REPLACE(TO_CHAR(ABS(0),'00000000000.00'),'.',''))||
                 RPAD('0',13,'0')||
                 RPAD('0',13,'0')||
                 RPAD('0',13,'0')||
                 RPAD('0',13,'0')||
                 RPAD(' ',2,' '))
            INTO V_REG88_02
            FROM MAPAAPURAC M, IMPOSTONF IP, NOTAFISCAL N
           WHERE M.CDEMPRESA     = P_CDEMPRESA
             AND M.CDINSCESTA    = P_CDINSCESTA
             AND M.DTINIPERIODO >= P_DTINICIAL
             AND M.DTFIMPERIODO <= P_DTFINAL
             AND M.CDIMPOSTO     = P_CDIMPOSTO
             AND M.NRSEQUMAPAPU  = IP.NRSEQUMAPAPU
             AND N.CDINSCESTAST  = P_CDINSCESTAST
             AND N.CDFILIAL      = IP.CDFILIAL
             AND N.NRLANCTONF    = IP.NRLANCTONF
             AND IP.CDIMPOSTO    = P_CDIMPOSTO
             AND IDENTRSAIDOP    = 'S'
           GROUP BY SUBSTR(REPLACE(REPLACE(REPLACE(P_CDINSCESTAST,'.',''),'/',''),'-',''),1,9),
                     TO_CHAR(DTINIPERIODO, 'yyyymm');
       EXCEPTION
       WHEN NO_DATA_FOUND THEN
            V_REG88_02 := '88'||
                          '02'||
                          SUBSTR(REPLACE(REPLACE(REPLACE(P_CDINSCESTAST,'.',''),'/',''),'-',''),1,9)||
                          TO_CHAR(P_DTINICIAL, 'yyyymm')||
                          V_TPDECLARE||
                          LTRIM(REPLACE(TO_CHAR(ABS(0),'00000000000.00'),'.',''))||
                          RPAD('0',13,'0')||
                          LTRIM(REPLACE(TO_CHAR(ABS(0),'00000000000.00'),'.',''))||
                          LTRIM(REPLACE(TO_CHAR(ABS(0),'00000000000.00'),'.',''))||
                          RPAD('0',13,'0')||
                          RPAD('0',13,'0')||
                          RPAD('0',13,'0')||
                          RPAD('0',13,'0')||
                          RPAD(' ',2,' ');
       END;
         IF V_REG88_02 IS NOT NULL THEN
            V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
            V_CONTADOR := NVL(V_CONTADOR,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('88'||LPAD(V_CONTADOR, 8, '0'), V_REG88_02, P_SEQMAGNET);
         END IF;
       /* registro 88 04 - informacoes complementares */
       SELECT LPAD(NRCPFRESPONS, 14, '0'),
              RPAD(NVL(SUBSTR(REPLACE(NRCRCCONT2,'/',''),2,11),' '), 10, '0'),
              LPAD(NVL(NMCONTADOR,' '), 40, ' '),
              LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRTELCONTATO,')',''),'(',''),'-',''),' ',''), 12, '0'),
              LPAD(NVL(DSENDINTERN,' '), 40, ' ')
         INTO V_NRCPFRESPONS, V_NRCRCCONT2, V_NMCONTADOR, V_NRTELCONTATO, V_DSENDINTERN
         FROM CONTADOR C, INSCRESTAD I
        WHERE I.CDEMPRESA   = P_CDEMPRESA
          AND I.CDINSCESTA  = P_CDINSCESTA
          AND I.CDCONTADOR2 = C.CDCONTADOR2;

       BEGIN
          SELECT MAX('88'||
                 '04'||
                 SUBSTR(REPLACE(REPLACE(REPLACE(P_CDINSCESTAST,'.',''),'/',''),'-',''),1,9)||
                 TO_CHAR(DTINIPERIODO, 'yyyymm')||
                 DECODE(P_FINALIDADE,'1','N','R')||
                 LTRIM(REPLACE(TO_CHAR(ABS(R.VRDIFALIQU),'00000000000.00'),'.',''))||
                 RPAD('0',13,'0')||
                 V_DSENDINTERN||
                 TO_CHAR(M.DTINIPERIODO, 'yyyymmdd')||
                 '0000'|| --verificar versao
                 '1'||
                 RPAD(' ',27,' '))
            INTO V_REG88_04
            FROM MAPAAPURAC M, MAPARECUP R
           WHERE M.CDEMPRESA     = P_CDEMPRESA
             AND M.CDINSCESTA    = P_CDINSCESTA
             AND M.DTINIPERIODO >= P_DTINICIAL
             AND M.DTFIMPERIODO <= P_DTFINAL
             AND M.CDIMPOSTO     = P_CDIMPOSTO
             AND M.NRSEQUMAPAPU  = R.NRSEQUMAPAPU;
       EXCEPTION
       WHEN NO_DATA_FOUND THEN
            V_REG88_04 := '88'||
                          '04'||
                          SUBSTR(REPLACE(REPLACE(REPLACE(P_CDINSCESTAST,'.',''),'/',''),'-',''),1,9)||
                          TO_CHAR(P_DTINICIAL, 'yyyymm')||
                          V_TPDECLARE||
                          LTRIM(REPLACE(TO_CHAR(ABS(0),'00000000000.00'),'.',''))||
                          RPAD('0',13,'0')||
                          V_DSENDINTERN||
                          TO_CHAR(P_DTINICIAL, 'yyyymmdd')||
                          '0000'|| --verificar versao
                          '1'||
                          RPAD(' ',27,' ');
       END;
          IF V_REG88_04 IS NOT NULL THEN
             V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
             V_CONTADOR := NVL(V_CONTADOR,0) + 1;
             INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
               ('88'||LPAD(V_CONTADOR, 8, '0'), V_REG88_04, P_SEQMAGNET);
          END IF;
       /* registro 88 05 informacoes do contabilista */
       V_LINHA := '88'||
                  '05'||
                  V_NRCPFRESPONS||
                  V_NRCRCCONT2  ||
                  V_NMCONTADOR  ||
                  V_NRTELCONTATO||
                  V_DSENDINTERN ||
                  RPAD(' ',6, ' ');

          V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
          V_CONTADOR := NVL(V_CONTADOR,0) + 1;
          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            ('88'||LPAD(V_CONTADOR, 8, '0'), V_LINHA, P_SEQMAGNET);

       /* registro 88 15 credito presumido */
       V_LINHA_15 := '88'||
                     '15'||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD(' ',18,' ');

       V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
       V_CONTADOR := NVL(V_CONTADOR,0) + 1;
       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         ('88'||LPAD(V_CONTADOR, 8, '0'), V_LINHA_15, P_SEQMAGNET);

       /* registro 88 16 deducoes do fundo de combate */
       V_LINHA_16 := '88'||
                     '16'||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD('0',13,'0')||
                     RPAD(' ',57,' ');

       V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
       V_CONTADOR := NVL(V_CONTADOR,0) + 1;
       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         ('88'||LPAD(V_CONTADOR, 8, '0'), V_LINHA_16, P_SEQMAGNET);

       /* registro 88 21 informacoes dos ajustes a debito de sub. trib. */
        V_CDIMPOSTOLIVRO := '80';
        BEGIN
            SELECT MAX('88'||
                   '23'||
                   TO_CHAR(P_DTINICIAL, 'yyyymm')||
                   LTRIM(REPLACE(TO_CHAR(ABS(R.VROUTRDEBI),'00000000000.00'),'.',''))||
                   RPAD(' ',103,' '))
            INTO V_REG88_23
            FROM MAPAAPURAC M, MAPARECUP R
            WHERE M.CDEMPRESA     = P_CDEMPRESA
              AND M.CDINSCESTA    = P_CDINSCESTA
              AND M.DTINIPERIODO >= P_DTINICIAL
              AND M.DTFIMPERIODO <= P_DTFINAL
              AND M.CDIMPOSTO     = P_CDIMPOSTO
              AND M.NRSEQUMAPAPU  = R.NRSEQUMAPAPU;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
             V_REG88_23 := '88'||
                           '23'||
                           TO_CHAR(P_DTINICIAL, 'yyyymm')||
                           LTRIM(REPLACE(TO_CHAR(ABS(0),'00000000000.00'),'.',''))||
                           RPAD(' ',103,' ');
        END;
          IF V_REG88_23 IS NOT NULL THEN
             V_CONTADOR := NVL(V_CONTADOR,0) + 1;
             V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
             INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
               ('88'||LPAD(V_CONTADOR, 8, '0'), V_REG88_23, P_SEQMAGNET);
          END IF;

      /* REGISTRO 88 29 */
       SELECT SUM(VRCONTABILNF) AS VRFATURAMENTO
         INTO V_VRFATURAMENTO
         FROM IMPOSTONF I, IMPOSTO IP
        WHERE NRSEQUMAPAPU    = V_NRSEQUMAPAPU
          AND IP.CDIMPOSTONFE = P_CDIMPOSTO
          AND IP.CDIMPOSTO    = I.CDIMPOSTO
          AND CDFISCOPERNF IN ('5102','6102','5403','6403','5401', '6401');

       SELECT SUM(VRCONTABILNF) AS VRENTRADAS
         INTO V_VRENTRADAS
         FROM IMPOSTONF I, IMPOSTO IP
        WHERE NRSEQUMAPAPU    = V_NRSEQUMAPAPU
          AND IP.CDIMPOSTONFE = P_CDIMPOSTO
          AND IP.CDIMPOSTO    = I.CDIMPOSTO
          AND CDFISCOPERNF IN ('1102','2102','1403','2403','1401', '2401');

       V_VRDIFERENCA := NVL(V_VRENTRADAS,0) - NVL(V_VRFATURAMENTO,0);

       FOR C_DIMPA_GIA IN (SELECT SUM(VRFATURAMENTO) AS VRFATURAMENTO, CDMUNIEXPO
                             FROM (
                                   SELECT SUM(VRCONTABILNF) AS VRFATURAMENTO, CDFILIEMIT,
                                          (SELECT MAX(CDMUNIEXPO) AS CDMUNIEXPO
                                             FROM FILIAL F, ENDEFILI E, MUNICIPIO M
                                            WHERE F.CDFILIAL    = N.CDFILIEMIT
                                              AND E.CDFILIAL    = F.CDFILIAL
                                              AND E.CDPAIS      = M.CDPAIS
                                              AND E.SGESTADO    = M.SGESTADO
                                              AND E.CDMUNICIPIO = M.CDMUNICIPIO) AS CDMUNIEXPO,
                                           (SELECT MAX(NMMUNICIPIO) AS NMMUNICIPIO
                                             FROM FILIAL F, ENDEFILI E, MUNICIPIO M
                                            WHERE F.CDFILIAL    = N.CDFILIEMIT
                                              AND E.CDFILIAL    = F.CDFILIAL
                                              AND E.CDPAIS      = M.CDPAIS
                                              AND E.SGESTADO    = M.SGESTADO
                                              AND E.CDMUNICIPIO = M.CDMUNICIPIO) AS NMMUNICIPIO
                                     FROM IMPOSTONF I, NOTAFISCAL N, IMPOSTO IP
                                    WHERE NRSEQUMAPAPU    = V_NRSEQUMAPAPU
                                      AND IP.CDIMPOSTONFE = P_CDIMPOSTO
                                      AND IP.CDIMPOSTO    = I.CDIMPOSTO
                                      AND CDFISCOPERNF IN ('5102','6102','5403','6403','5401', '6401')
                                      AND I.CDFILIAL      = N.CDFILIAL
                                      AND I.NRLANCTONF    = N.NRLANCTONF
                                    GROUP BY CDFILIEMIT)
                            GROUP BY CDMUNIEXPO ) LOOP
          V_PERCENTUAL := TRUNC(NVL(C_DIMPA_GIA.VRFATURAMENTO,0) / NVL(V_VRFATURAMENTO,0),6);
          V_VRDIPAMGIA := ROUND(NVL(V_VRDIFERENCA,0)* V_PERCENTUAL,2);
          V_LINHA := '8829'||
                     NVL(C_DIMPA_GIA.CDMUNIEXPO,'INFORME O MUNICIPIO')||
                     LTRIM(REPLACE(TO_CHAR(ABS(V_VRDIPAMGIA),'00000000000.00'),'.',''))||
                     RPAD(' ', 104, ' ');
          V_CONTADOR := NVL(V_CONTADOR,0) + 1;
          V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            ('88'||LPAD(V_CONTADOR, 8, '0'), V_LINHA, P_SEQMAGNET);
          V_LINHA := NULL;
       END LOOP;

       /* REGISTRO 88 30 */
       V_LINHA := '8830'||
                  LPAD(NVL(P_RECIBO,' '),32,' ')||
                  '1'||
                  /*LPAD('0', 8, '0')*/REPLACE(P_DTINICIAL,'/','')||
                  LPAD(' ', 81, ' ');
       V_CONTADOR := NVL(V_CONTADOR,0) + 1;
       V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         ('88'||LPAD(V_CONTADOR, 8, '0'), V_LINHA, P_SEQMAGNET);
       V_LINHA := NULL;

       IF V_CONTREG > 0 THEN
          RETURN(TO_CHAR(LPAD(V_CONTGERAL,10,0))||TO_CHAR(LPAD(V_CONTADOR,10,0)));
       END IF;
       IF  V_CONTREG = 0 THEN
          RETURN('0');
       END IF;
   END IF;
END;
/