CREATE OR REPLACE PROCEDURE ARQUIVO_DFC_GI(
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_CDIMPOSTO    IN IMPOSTO.CDIMPOSTO%TYPE,
   P_CDINSCESTA   IN EMPRESA.CDINSCESTA%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_TPDOCUMENTO  IN IMPOSTO.CDIMPOSTO%TYPE,
   P_MOD_DFC      IN IMPOSTO.CDIMPOSTO%TYPE,
   P_CDLIVROMAG   IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   P_NRSEQARQMAG  IN VARCHAR2)
AS
   V_COLUNA            VARCHAR2(4000);
   V_SEQUENCIAL        VARCHAR2(20);
   V_CNPJ              VARCHAR2(20);
   V_CDINSCESTA        VARCHAR2(20);
   V_CRCCONTADOR       VARCHAR2(15);
   V_CPFCONTADOR       VARCHAR2(15);
   V_CDMUNICIPIO       VARCHAR2(04);
   V_CFOPDFC           VARCHAR2(03);
   V_CFOPDFC_CUR       VARCHAR2(20);
   V_CFOPBASE          VARCHAR2(20);
   V_CFOPISEN          VARCHAR2(20);
   V_CFOPOUTR          VARCHAR2(20);
   V_CONTADOR          NUMBER;
   V_CONTADOR_GI       NUMBER;
   V_ENTRADA_CONT      NUMBER;
   V_ENTRADA_BASE      NUMBER;
   V_ENTRADA_ISEN      NUMBER;
   V_ENTRADA_OUTR      NUMBER;
   V_VRCONTABILNF      NUMBER;
   V_VRBASECALCNF      NUMBER;
   V_VROUTRCRED        NUMBER;
   V_VRISENTRIB        NUMBER;
   V_SAIDA_CONT        NUMBER;
   V_SAIDA_BASE        NUMBER;
   V_SAIDA_ISEN        NUMBER;
   V_SAIDA_OUTR        NUMBER;
BEGIN
   IF (P_TPDOCUMENTO = '21') OR (P_TPDOCUMENTO = '22') OR (P_TPDOCUMENTO = '24') THEN
      BEGIN
         SELECT I.CDINSCESTA, I.NRINSJURIEST, C.NRCRCCONT2, C.NRINSJURCONT
           INTO V_CDINSCESTA, V_CNPJ, V_CRCCONTADOR, V_CPFCONTADOR
           FROM INSCRESTAD I, CONTADOR C
          WHERE I.CDEMPRESA   = P_CDEMPRESA
            AND I.CDINSCESTA  = P_CDINSCESTA
            AND I.CDCONTADOR2 = C.CDCONTADOR2;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDINSCESTA  := NULL;
            V_CNPJ        := NULL;
            V_CRCCONTADOR := NULL;
      END;
      V_COLUNA := '1'||
                  P_TPDOCUMENTO||
                  REPLACE(REPLACE(V_CDINSCESTA,'.',''),'-','') ||
                  V_CNPJ||
                  TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'yyyy'))-1)|| '00' ||
                  'F'||
                  RPAD(NVL(REPLACE(REPLACE(V_CPFCONTADOR,'-',''),'/',''),' '), 15, '0')||
                  P_MOD_DFC||
                  LPAD(' ', 59, ' ')||
                  '001';
      V_CONTADOR := 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
      V_ENTRADA_CONT := 801;
      V_ENTRADA_BASE := 826;
      V_ENTRADA_ISEN := 851;
      V_ENTRADA_OUTR := 876;
      LOOP
         BEGIN
            BEGIN
               SELECT RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_ENTRADA_CONT)) AS CFOPDFC,
                      RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_ENTRADA_BASE)) AS CFOPBASE,
                      RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_ENTRADA_ISEN)) AS CFOPISEN,
                      RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_ENTRADA_OUTR)) AS CFOPOUTR,
                      SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                      SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                      SUM(DECODE(CDTIPOOPER,'03',ROUND(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0),2),ROUND((I.VRCONTABILNF-(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)+DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0))),2))) AS VROUTRCRED,
                      SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0)) AS VRISENTRIB
               INTO   V_CFOPDFC_CUR, V_CFOPBASE, V_CFOPISEN, V_CFOPOUTR, V_VRCONTABILNF, V_VRBASECALCNF, V_VROUTRCRED, V_VRISENTRIB
               FROM IMPOSTONF I, MAPAAPURAC M, NOTAFISCAL N
               WHERE M.CDEMPRESA    = P_CDEMPRESA
                 AND M.CDIMPOSTO    = P_CDIMPOSTO
                 AND M.CDINSCESTA   = P_CDINSCESTA
                 AND M.DTINIPERIODO >= P_DTINICIAL
                 AND M.DTFIMPERIODO <= P_DTFINAL
                 AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                 AND ((IDIMPOSTONF <> '3') OR (IDIMPOSTONF IS NULL))
                 AND I.CDFILIAL     = N.CDFILIAL
                 AND I.NRLANCTONF   = N.NRLANCTONF
                 AND N.IDENTRSAIDOP = 'E'
                 AND RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_ENTRADA_CONT)) IS NOT NULL
                 AND RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_ENTRADA_BASE)) IS NOT NULL
                 AND RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_ENTRADA_ISEN)) IS NOT NULL
                 AND RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_ENTRADA_OUTR)) IS NOT NULL
               GROUP BY RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_ENTRADA_CONT)),
                        RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_ENTRADA_BASE)),
                        RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_ENTRADA_ISEN)),
                        RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_ENTRADA_OUTR))
               ORDER BY 1;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_CFOPDFC_CUR  := NULL;
                  V_CFOPBASE     := NULL;
                  V_CFOPISEN     := NULL;
                  V_CFOPOUTR     := NULL;
                  V_VRCONTABILNF := 0;
                  V_VRBASECALCNF := 0;
                  V_VROUTRCRED   := 0;
                  V_VRISENTRIB   := 0;
            END;
         END;
         IF V_CFOPDFC_CUR IS NOT NULL THEN
            V_COLUNA := NULL;
            V_COLUNA := '2'||V_CFOPDFC_CUR||'0'||LPAD( TO_CHAR(ROUND(NVL(V_VRCONTABILNF,'0'),0)), 15, '0');
            V_CONTADOR := NVL(V_CONTADOR,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
         END IF;
         IF V_CFOPBASE IS NOT NULL THEN
            V_COLUNA := NULL;
            V_COLUNA := '2'||V_CFOPBASE||'0'||LPAD( TO_CHAR(ROUND(NVL(V_VRBASECALCNF,'0'),0)), 15, '0');
            V_CONTADOR := NVL(V_CONTADOR,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
         END IF;
         IF V_CFOPISEN IS NOT NULL THEN
            V_COLUNA := NULL;
            V_COLUNA := '2'||V_CFOPISEN||'0'||LPAD( TO_CHAR(ROUND(NVL(V_VRISENTRIB,'0'),0)), 15, '0');
            V_CONTADOR := NVL(V_CONTADOR,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
         END IF;
         IF V_CFOPOUTR IS NOT NULL THEN
            V_COLUNA := NULL;
            V_COLUNA := '2'||V_CFOPOUTR||'0'||LPAD( TO_CHAR(ROUND(NVL(V_VROUTRCRED,'0'),0)), 15, '0');
            V_CONTADOR := NVL(V_CONTADOR,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
         END IF;
         V_ENTRADA_CONT := NVL(V_ENTRADA_CONT,0)+1;
         V_ENTRADA_BASE := NVL(V_ENTRADA_BASE,0)+1;
         V_ENTRADA_ISEN := NVL(V_ENTRADA_ISEN,0)+1;
         V_ENTRADA_OUTR := NVL(V_ENTRADA_OUTR,0)+1;
         EXIT WHEN V_ENTRADA_CONT > 823;
      END LOOP;
      V_SAIDA_CONT := 901;
      V_SAIDA_BASE := 926;
      V_SAIDA_ISEN := 951;
      V_SAIDA_OUTR := 976;
      V_CFOPDFC_CUR  := NULL;
      V_CFOPBASE     := NULL;
      V_CFOPISEN     := NULL;
      V_CFOPOUTR     := NULL;
      V_VRCONTABILNF := 0;
      V_VRBASECALCNF := 0;
      V_VROUTRCRED   := 0;
      V_VRISENTRIB   := 0;
      LOOP
         BEGIN
            BEGIN
               SELECT RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_SAIDA_CONT)) AS CFOPDFC,
                      RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_SAIDA_BASE)) AS CFOPBASE,
                      RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_SAIDA_ISEN)) AS CFOPISEN,
                      RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_SAIDA_OUTR)) AS CFOPOUTR,
                      SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                      SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                      SUM(DECODE(CDTIPOOPER,'03',ROUND(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0),2),ROUND((I.VRCONTABILNF-(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)+DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0))),2))) AS VROUTRCRED,
                      SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0)) AS VRISENTRIB
               INTO   V_CFOPDFC_CUR, V_CFOPBASE, V_CFOPISEN, V_CFOPOUTR, V_VRCONTABILNF, V_VRBASECALCNF, V_VROUTRCRED, V_VRISENTRIB
               FROM IMPOSTONF I, MAPAAPURAC M, NOTAFISCAL N
               WHERE M.CDEMPRESA    = P_CDEMPRESA
                 AND M.CDIMPOSTO    = P_CDIMPOSTO
                 AND M.CDINSCESTA   = P_CDINSCESTA
                 AND M.DTINIPERIODO >= P_DTINICIAL
                 AND M.DTFIMPERIODO <= P_DTFINAL
                 AND ((IDIMPOSTONF <> '3') OR (IDIMPOSTONF IS NULL))
                 AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                 AND I.CDFILIAL     = N.CDFILIAL
                 AND I.NRLANCTONF   = N.NRLANCTONF
                 AND N.IDENTRSAIDOP = 'S'
                 AND RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_SAIDA_CONT)) IS NOT NULL
                 AND RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_SAIDA_BASE)) IS NOT NULL
                 AND RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_SAIDA_ISEN)) IS NOT NULL
                 AND RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_SAIDA_OUTR)) IS NOT NULL
               GROUP BY RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_SAIDA_CONT)),
                        RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_SAIDA_BASE)),
                        RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_SAIDA_ISEN)),
                        RETORNA_CFOP_DFC(P_CDLIVROMAG, I.CDFISCOPERNF, TO_CHAR(V_SAIDA_OUTR))
               ORDER BY 1;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_CFOPDFC_CUR  := NULL;
                  V_CFOPBASE     := NULL;
                  V_CFOPISEN     := NULL;
                  V_CFOPOUTR     := NULL;
                  V_VRCONTABILNF := 0;
                  V_VRBASECALCNF := 0;
                  V_VROUTRCRED   := 0;
                  V_VRISENTRIB   := 0;
            END;
         END;
         IF V_CFOPDFC_CUR IS NOT NULL THEN
            V_COLUNA := NULL;
            V_COLUNA := '2'||V_CFOPDFC_CUR||'0'||LPAD( TO_CHAR(ROUND(NVL(V_VRCONTABILNF,'0'),0)), 15, '0');
            V_CONTADOR := NVL(V_CONTADOR,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
         END IF;
         IF V_CFOPBASE IS NOT NULL THEN
            V_COLUNA := NULL;
            V_COLUNA := '2'||V_CFOPBASE||'0'||LPAD( TO_CHAR(ROUND(NVL(V_VRBASECALCNF,'0'),0)), 15, '0');
            V_CONTADOR := NVL(V_CONTADOR,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
         END IF;
         IF V_CFOPISEN IS NOT NULL THEN
            V_COLUNA := NULL;
            V_COLUNA := '2'||V_CFOPISEN||'0'||LPAD( TO_CHAR(ROUND(NVL(V_VRISENTRIB,'0'),0)), 15, '0');
            V_CONTADOR := NVL(V_CONTADOR,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
         END IF;
         IF V_CFOPOUTR IS NOT NULL THEN
            V_COLUNA := NULL;
            V_COLUNA := '2'||V_CFOPOUTR||'0'||LPAD( TO_CHAR(ROUND(NVL(V_VROUTRCRED,'0'),0)), 15, '0');
            V_CONTADOR := NVL(V_CONTADOR,0) + 1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
         END IF;
         V_SAIDA_CONT := NVL(V_SAIDA_CONT,0)+1;
         V_SAIDA_BASE := NVL(V_SAIDA_BASE,0)+1;
         V_SAIDA_ISEN := NVL(V_SAIDA_ISEN,0)+1;
         V_SAIDA_OUTR := NVL(V_SAIDA_OUTR,0)+1;
         EXIT WHEN V_SAIDA_CONT > 921;
      END LOOP;
      V_COLUNA       := NULL;
      FOR ENTRADA_NOTA IN (SELECT SUM(I.VRCONTABILNF) AS VRCONTABILNF, SUBSTR(MU.CDMUNIEXPO,2,5) AS CDMUNICIPIO
                           FROM IMPOSTONF I, MAPAAPURAC M, NOTAFISCAL N,
                                FORNECEDOR F, ENDEFORN EF, MUNICIPIO MU
                           WHERE M.CDEMPRESA    = P_CDEMPRESA
                             AND M.CDIMPOSTO    = P_CDIMPOSTO
                             AND M.CDINSCESTA   = P_CDINSCESTA
                             AND M.DTINIPERIODO >= P_DTINICIAL
                             AND M.DTFIMPERIODO <= P_DTFINAL
                             AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                             AND N.CDFILIAL     = I.CDFILIAL
                             AND N.NRLANCTONF   = I.NRLANCTONF
                             AND ((IDIMPOSTONF <> '3') OR (IDIMPOSTONF IS NULL))
                             AND N.CDFORNECED   = F.CDFORNECED
                             AND EF.CDFORNECED  = F.CDFORNECED
                             AND EF.CDPAIS      = MU.CDPAIS
                             AND EF.SGESTADO    = MU.SGESTADO
                             AND EF.CDMUNICIPIO = MU.CDMUNICIPIO
                             AND NVL(EF.IDTPENDEFORN, 'P') = 'P'
                             AND N.IDENTRSAIDOP = 'E'
                             AND IDORIGDESTNF   = 'F'
                             AND EF.SGESTADO    = 'PR'
                             AND N.CDTIPOOPER IN( '08','11')
                           GROUP BY SUBSTR(MU.CDMUNIEXPO,2,5)
                           UNION ALL
                           SELECT SUM(I.VRCONTABILNF) AS VRCONTABILNF, SUBSTR(MU.CDMUNIEXPO,2,5) AS CDMUNICIPIO
                           FROM IMPOSTONF I, MAPAAPURAC M, NOTAFISCAL N,
                                CLIENTE C, ENDECLIE EC, MUNICIPIO MU
                           WHERE M.CDEMPRESA    = P_CDEMPRESA
                             AND M.CDIMPOSTO    = P_CDIMPOSTO
                             AND M.CDINSCESTA   = P_CDINSCESTA
                             AND M.DTINIPERIODO >= P_DTINICIAL
                             AND M.DTFIMPERIODO <= P_DTFINAL
                             AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                             AND N.CDFILIAL     = I.CDFILIAL
                             AND N.NRLANCTONF   = I.NRLANCTONF
                             AND N.CDCLIENTE    = C.CDCLIENTE
                             AND ((IDIMPOSTONF <> '3') OR (IDIMPOSTONF IS NULL))
                             AND EC.CDCLIENTE   = C.CDCLIENTE
                             AND EC.CDPAIS      = MU.CDPAIS
                             AND EC.SGESTADO    = MU.SGESTADO
                             AND EC.CDMUNICIPIO = MU.CDMUNICIPIO
                             AND NVL(EC.IDTPENDECLIE, 'P') = 'P'
                             AND N.IDENTRSAIDOP = 'E'
                             AND IDORIGDESTNF   = 'C'
                             AND EC.SGESTADO    = 'PR'
                             AND N.CDTIPOOPER IN( '08','11')
                           GROUP BY SUBSTR(MU.CDMUNIEXPO,2,5)
                           UNION ALL
                           SELECT SUM(I.VRCONTABILNF) AS VRCONTABILNF, SUBSTR(MU.CDMUNIEXPO,2,5) AS CDMUNICIPIO
                           FROM IMPOSTONF I, MAPAAPURAC M, NOTAFISCAL N,
                                FILIAL F, ENDEFILI EF, MUNICIPIO MU
                           WHERE M.CDEMPRESA    = P_CDEMPRESA
                             AND M.CDIMPOSTO    = P_CDIMPOSTO
                             AND M.CDINSCESTA   = P_CDINSCESTA
                             AND M.DTINIPERIODO >= P_DTINICIAL
                             AND M.DTFIMPERIODO <= P_DTFINAL
                             AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                             AND N.CDFILIAL     = I.CDFILIAL
                             AND ((IDIMPOSTONF <> '3') OR (IDIMPOSTONF IS NULL))
                             AND N.NRLANCTONF   = I.NRLANCTONF
                             AND N.CDFILIEMIT   = F.CDFILIAL
                             AND EF.CDFILIAL    = F.CDFILIAL
                             AND EF.CDPAIS      = MU.CDPAIS
                             AND EF.SGESTADO    = MU.SGESTADO
                             AND EF.CDMUNICIPIO = MU.CDMUNICIPIO
                             AND NVL(EF.IDTPENDEFILI, 'P') = 'P'
                             AND N.IDENTRSAIDOP = 'E'
                             AND IDORIGDESTNF   = 'L'
                             AND EF.SGESTADO    = 'PR'
                             AND N.CDTIPOOPER IN( '08','11')
                           GROUP BY SUBSTR(MU.CDMUNIEXPO,2,5) ) LOOP
         V_COLUNA := '2'||NVL(ENTRADA_NOTA.CDMUNICIPIO,'    ')||LTRIM(TO_CHAR(ENTRADA_NOTA.VRCONTABILNF, '000000000000000'));
         V_CONTADOR := NVL(V_CONTADOR,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
      END LOOP;
      V_COLUNA := NULL;
      FOR NOTA_TRANSP IN (SELECT SUM(I.VRCONTABILNF) AS VRCONTABILNF, SUBSTR(MU.CDMUNIEXPO,2,5) AS CDMUNICIPIO
                          FROM IMPOSTONF I, MAPAAPURAC M, NOTAFISCAL N,
                               CLIENTE C, ENDECLIE EC, MUNICIPIO MU
                          WHERE M.CDEMPRESA    = P_CDEMPRESA
                            AND M.CDIMPOSTO    = P_CDIMPOSTO
                            AND M.CDINSCESTA   = P_CDINSCESTA
                            AND M.DTINIPERIODO >= P_DTINICIAL
                            AND M.DTFIMPERIODO <= P_DTFINAL
                            AND ((IDIMPOSTONF <> '3') OR (IDIMPOSTONF IS NULL))
                            AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                            AND N.CDFILIAL     = I.CDFILIAL
                            AND N.NRLANCTONF   = I.NRLANCTONF
                            AND N.CDCLIENTE    = C.CDCLIENTE
                            AND C.CDCLIENTE    = EC.CDCLIENTE
                            AND EC.CDPAIS      = MU.CDPAIS
                            AND EC.SGESTADO    = MU.SGESTADO
                            AND EC.CDMUNICIPIO = MU.CDMUNICIPIO
                            AND N.IDENTRSAIDOP = 'E'
                            AND N.CDMODDOCFISC = '07'
                            AND NVL(EC.IDTPENDECLIE, 'P') = 'P'
                            AND EC.SGESTADO    = 'PR'
                            AND IDORIGDESTNF   = 'C'
                          GROUP BY SUBSTR(MU.CDMUNIEXPO,2,5)
                          UNION ALL
                          SELECT SUM(I.VRCONTABILNF) AS VRCONTABILNF, SUBSTR(MU.CDMUNIEXPO,2,5) AS CDMUNICIPIO
                          FROM IMPOSTONF I, MAPAAPURAC M, NOTAFISCAL N,
                               FORNECEDOR F, ENDEFORN EF, MUNICIPIO MU
                          WHERE M.CDEMPRESA    = P_CDEMPRESA
                            AND M.CDIMPOSTO    = P_CDIMPOSTO
                            AND M.CDINSCESTA   = P_CDINSCESTA
                            AND M.DTINIPERIODO >= P_DTINICIAL
                            AND M.DTFIMPERIODO <= P_DTFINAL
                            AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                            AND N.CDFILIAL     = I.CDFILIAL
                            AND N.NRLANCTONF   = I.NRLANCTONF
                            AND N.CDFORNECED   = F.CDFORNECED
                            AND F.CDFORNECED   = EF.CDFORNECED
                            AND EF.CDPAIS      = MU.CDPAIS
                            AND EF.SGESTADO    = MU.SGESTADO
                            AND ((IDIMPOSTONF <> '3') OR (IDIMPOSTONF IS NULL))
                            AND EF.CDMUNICIPIO = MU.CDMUNICIPIO
                            AND N.IDENTRSAIDOP = 'E'
                            AND N.CDMODDOCFISC = '07'
                            AND EF.SGESTADO    = 'PR'
                            AND NVL(EF.IDTPENDEFORN, 'P') = 'P'
                            AND IDORIGDESTNF   = 'F'
                          GROUP BY SUBSTR(MU.CDMUNIEXPO,2,5)
                          UNION ALL
                          SELECT SUM(I.VRCONTABILNF) AS VRCONTABILNF, SUBSTR(MU.CDMUNIEXPO,2,5) AS CDMUNICIPIO
                          FROM IMPOSTONF I, MAPAAPURAC M, NOTAFISCAL N,
                               FILIAL F, ENDEFILI EF, MUNICIPIO MU
                          WHERE M.CDEMPRESA    = P_CDEMPRESA
                            AND M.CDIMPOSTO    = P_CDIMPOSTO
                            AND M.CDINSCESTA   = P_CDINSCESTA
                            AND M.DTINIPERIODO >= P_DTINICIAL
                            AND M.DTFIMPERIODO <= P_DTFINAL
                            AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                            AND N.CDFILIAL     = I.CDFILIAL
                            AND N.NRLANCTONF   = I.NRLANCTONF
                            AND N.CDFILIEMIT   = F.CDFILIAL
                            AND F.CDFILIAL     = EF.CDFILIAL
                            AND EF.CDPAIS      = MU.CDPAIS
                            AND ((IDIMPOSTONF <> '3') OR (IDIMPOSTONF IS NULL))
                            AND EF.SGESTADO    = MU.SGESTADO
                            AND EF.CDMUNICIPIO = MU.CDMUNICIPIO
                            AND N.IDENTRSAIDOP = 'E'
                            AND N.CDMODDOCFISC = '07'
                            AND EF.SGESTADO    = 'PR'
                            AND NVL(EF.IDTPENDEFILI, 'P') = 'P'
                            AND IDORIGDESTNF   = 'L'
                          GROUP BY SUBSTR(MU.CDMUNIEXPO,2,5) ) LOOP
         V_COLUNA := '2'||NVL(NOTA_TRANSP.CDMUNICIPIO,'    ')||LTRIM(TO_CHAR(NVL(NOTA_TRANSP.VRCONTABILNF,LPAD(0, 15, 0)), '000000000000000'));
         V_CONTADOR := NVL(V_CONTADOR,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
      END LOOP;
      FOR NOTA_ENERGIA IN (SELECT SUM(I.VRCONTABILNF) AS VRCONTABILNF, SUBSTR(MU.CDMUNIEXPO,2,5) AS CDMUNICIPIO
                           FROM IMPOSTONF I, MAPAAPURAC M, NOTAFISCAL N,
                                CLIENTE C, ENDECLIE EC, MUNICIPIO MU
                           WHERE M.CDEMPRESA    = P_CDEMPRESA
                             AND M.CDIMPOSTO    = P_CDIMPOSTO
                             AND M.CDINSCESTA   = P_CDINSCESTA
                             AND M.DTINIPERIODO >= P_DTINICIAL
                             AND M.DTFIMPERIODO <= P_DTFINAL
                             AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                             AND N.CDFILIAL     = I.CDFILIAL
                             AND N.NRLANCTONF   = I.NRLANCTONF
                             AND N.CDCLIENTE    = C.CDCLIENTE
                             AND ((IDIMPOSTONF <> '3') OR (IDIMPOSTONF IS NULL))
                             AND C.CDCLIENTE    = EC.CDCLIENTE
                             AND EC.CDPAIS      = MU.CDPAIS
                             AND EC.SGESTADO    = MU.SGESTADO
                             AND EC.CDMUNICIPIO = MU.CDMUNICIPIO
                             AND N.IDENTRSAIDOP = 'E'
                             AND EC.SGESTADO    = 'PR'
                             AND N.CDMODDOCFISC IN ('21', '06')
                             AND NVL(EC.IDTPENDECLIE, 'P') = 'P'
                             AND IDORIGDESTNF   = 'C'
                           GROUP BY SUBSTR(MU.CDMUNIEXPO,2,5)
                           UNION ALL
                           SELECT SUM(I.VRCONTABILNF) AS VRCONTABILNF, SUBSTR(MU.CDMUNIEXPO,2,5) AS CDMUNICIPIO
                           FROM IMPOSTONF I, MAPAAPURAC M, NOTAFISCAL N,
                                FORNECEDOR F, ENDEFORN EF, MUNICIPIO MU
                           WHERE M.CDEMPRESA    = P_CDEMPRESA
                             AND M.CDIMPOSTO    = P_CDIMPOSTO
                             AND M.CDINSCESTA   = P_CDINSCESTA
                             AND ((IDIMPOSTONF <> '3') OR (IDIMPOSTONF IS NULL))
                             AND M.DTINIPERIODO >= P_DTINICIAL
                             AND M.DTFIMPERIODO <= P_DTFINAL
                             AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                             AND N.CDFILIAL     = I.CDFILIAL
                             AND N.NRLANCTONF   = I.NRLANCTONF
                             AND N.CDFORNECED   = F.CDFORNECED
                             AND F.CDFORNECED   = EF.CDFORNECED
                             AND EF.CDPAIS      = MU.CDPAIS
                             AND EF.SGESTADO    = MU.SGESTADO
                             AND EF.CDMUNICIPIO = MU.CDMUNICIPIO
                             AND N.IDENTRSAIDOP = 'E'
                             AND EF.SGESTADO    = 'PR'
                             AND N.CDMODDOCFISC IN ('21', '06')
                             AND NVL(EF.IDTPENDEFORN, 'P') = 'P'
                             AND IDORIGDESTNF   = 'F'
                           GROUP BY SUBSTR(MU.CDMUNIEXPO,2,5)
                           UNION ALL
                           SELECT SUM(I.VRCONTABILNF) AS VRCONTABILNF, SUBSTR(MU.CDMUNIEXPO,2,5) AS CDMUNICIPIO
                           FROM IMPOSTONF I, MAPAAPURAC M, NOTAFISCAL N,
                                FILIAL F, ENDEFILI EF, MUNICIPIO MU
                           WHERE M.CDEMPRESA    = P_CDEMPRESA
                             AND M.CDIMPOSTO    = P_CDIMPOSTO
                             AND M.CDINSCESTA   = P_CDINSCESTA
                             AND M.DTINIPERIODO >= P_DTINICIAL
                             AND M.DTFIMPERIODO <= P_DTFINAL
                             AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                             AND N.CDFILIAL     = I.CDFILIAL
                             AND N.NRLANCTONF   = I.NRLANCTONF
                             AND N.CDFILIEMIT   = F.CDFILIAL
                             AND F.CDFILIAL     = EF.CDFILIAL
                             AND EF.CDPAIS      = MU.CDPAIS
                             AND ((IDIMPOSTONF <> '3') OR (IDIMPOSTONF IS NULL))
                             AND EF.SGESTADO    = MU.SGESTADO
                             AND EF.CDMUNICIPIO = MU.CDMUNICIPIO
                             AND N.IDENTRSAIDOP = 'E'
                             AND EF.SGESTADO    = 'PR'
                             AND N.CDMODDOCFISC IN ('21', '06')
                             AND NVL(EF.IDTPENDEFILI, 'P') = 'P'
                             AND IDORIGDESTNF   = 'L'
                           GROUP BY SUBSTR(MU.CDMUNIEXPO,2,5) ) LOOP
         V_COLUNA := '2'||NVL(NOTA_ENERGIA.CDMUNICIPIO,'    ')||LTRIM(TO_CHAR(NVL(NOTA_ENERGIA.VRCONTABILNF,LPAD(0, 15, 0)), '000000000000000'));
         V_CONTADOR := NVL(V_CONTADOR,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
      END LOOP;
   ELSE
      FOR C_GI_ENTRADAS IN (SELECT VRCONTABILNF,
                                   VRBASECALCNF,
                                   VROUTRCRED,
                                   NMESTADO,
                                   DECODE(NMESTADO, 'AC', '01', DECODE(NMESTADO, 'AL', '02', DECODE(NMESTADO, 'AP', '03', DECODE(NMESTADO, 'AM', '04',
                                   DECODE(NMESTADO, 'BA', '05', DECODE(NMESTADO, 'CE', '06', DECODE(NMESTADO, 'DF', '07', DECODE(NMESTADO, 'ES', '08',
                                   DECODE(NMESTADO, 'GO', '10', DECODE(NMESTADO, 'MA', '12', DECODE(NMESTADO, 'MT', '13', DECODE(NMESTADO, 'MG', '14',
                                   DECODE(NMESTADO, 'PA', '15', DECODE(NMESTADO, 'PB', '16', DECODE(NMESTADO, 'PE', '18', DECODE(NMESTADO, 'PI', '19',
                                   DECODE(NMESTADO, 'RN', '20', DECODE(NMESTADO, 'RS', '21', DECODE(NMESTADO, 'RJ', '22', DECODE(NMESTADO, 'RO', '23',
                                   DECODE(NMESTADO, 'RR', '24', DECODE(NMESTADO, 'SC', '25', DECODE(NMESTADO, 'SP', '26', DECODE(NMESTADO, 'SE', '27',
                                   DECODE(NMESTADO, 'MS', '28', DECODE(NMESTADO, 'TO', '29','')))))))))))))))))))))))))) AS COD_ESTADO
                              FROM (SELECT SUM(A.VRCONTABILNF)  AS VRCONTABILNF,
                                           SUM(A.VRBASECALCNF)  AS VRBASECALCNF,
                                           SUM(A.VROUTRCRED+A.VRISENTRIB) AS VROUTRCRED,
                                           NVL(NVL(ET.SGESTADO, ETL.SGESTADO), ETC.SGESTADO) AS NMESTADO
                                      FROM ENDEFORN EF, NOTAFISCAL NF,
                                           EMPRESA EP, INSCRESTAD IE, ESTADO ET,
                                           ENDECLIE EC, ENDEFILI EL,  ESTADO ETL, ESTADO ETC,
                                           (SELECT MP.CDEMPRESA, MP.CDINSCESTA,
                                                   I.NRSEQUMAPAPU, I.CDFILIAL, I.NRLANCTONF, CDFISCOPERNF,
                                                   MIN(MP.DTINIPERIODO) AS DTINIPERIODO,
                                                   MAX(MP.DTFIMPERIODO) AS DTFIMPERIODO,
                                                   SUM(ROUND(VRCONTABILNF,2)) AS VRCONTABILNF,
                                                   SUM(ROUND(DECODE(CDFISCALNF,'1',VRBASECALCNF,0),2)) AS VRBASECALCNF,
                                                   SUM(ROUND(DECODE(CDFISCALNF,'1',VRIMPOSTONF,0),2))  AS VRIMPOSTONF,
                                                   SUM(ROUND(DECODE(CDFISCALNF,'3',VRCONTABILNF,0),2)) AS VROUTRCRED,
                                                   SUM(ROUND(DECODE(CDFISCALNF, '2', VRCONTABILNF, DECODE(CDFISCALNF, 1, DECODE(VRPERREDBCNF, 0, 0, (VRCONTABILNF - VRBASECALCNF)) ,0)),2)) AS VRISENTRIB
                                            FROM IMPOSTONF I, MAPAAPURAC MP
                                            WHERE (MP.CDEMPRESA       = P_CDEMPRESA)    AND
                                                  (MP.CDINSCESTA      = P_CDINSCESTA)   AND
                                                  (MP.CDIMPOSTO       = P_CDIMPOSTO)    AND
                                                  (MP.DTINIPERIODO   >= P_DTINICIAL)    AND
                                                  (MP.DTFIMPERIODO   <= P_DTFINAL)      AND
                                                  (MP.NRSEQUMAPAPU    = I.NRSEQUMAPAPU) AND
                                                  (I.CDIMPOSTO = P_CDIMPOSTO) AND
                                                  ((IDIMPOSTONF <> '3') OR (IDIMPOSTONF IS NULL))
                                            GROUP BY MP.CDEMPRESA, MP.CDINSCESTA, I.NRSEQUMAPAPU,
                                                     I.CDFILIAL, I.NRLANCTONF, CDFISCOPERNF, MP.DTINIPERIODO, MP.DTFIMPERIODO
                                            UNION
                                            SELECT MP.CDEMPRESA, MP.CDINSCESTA,
                                                   I.NRSEQUMAPAPU, I.CDFILIAL, I.NRLANCTONF, CDFISCOPERNF,
                                                   MP.DTINIPERIODO,
                                                   MP.DTFIMPERIODO,
                                                   0 AS VRCONTABILNF,
                                                   0 AS VRBASECALCNF, 0 AS VRIMPOSTONF,
                                                   SUM(ROUND(VRIMPOSTONF,2)) AS VROUTRCRED,   0 AS VRISENTRIB
                                            FROM IMPOSTONF I, MAPAAPURAC MP
                                            WHERE (MP.CDEMPRESA     = P_CDEMPRESA)      AND
                                                  (MP.CDINSCESTA    = P_CDINSCESTA)     AND
                                                  (MP.CDIMPOSTO     = P_CDIMPOSTO)      AND
                                                  (MP.DTINIPERIODO >= P_DTINICIAL)      AND
                                                  (MP.DTFIMPERIODO <= P_DTFINAL)        AND
                                                  (MP.NRSEQUMAPAPU  = I.NRSEQUMAPAPU)   AND
                                                  (I.CDIMPOSTO     <> P_CDIMPOSTO)      AND
                                                  (IDIMPOSTONF      = '4')
                                            GROUP BY MP.CDEMPRESA, MP.CDINSCESTA, I.NRSEQUMAPAPU,
                                                     I.CDFILIAL, I.NRLANCTONF, CDFISCOPERNF, MP.DTINIPERIODO, MP.DTFIMPERIODO) A
                                      WHERE (NF.CDFILIAL        = A.CDFILIAL)     AND
                                            (NF.NRLANCTONF      = A.NRLANCTONF)   AND
                                            (EF.CDFORNECED(+)   = NF.CDFORNECED)  AND
                                            (EC.CDCLIENTE(+)    = NF.CDCLIENTE)   AND
                                            (EL.CDFILIAL(+)     = NF.CDFILIEMIT)  AND
                                            (EF.IDTPENDEFORN(+) = 'P')            AND
                                            (EC.IDTPENDECLIE(+) = 'P')            AND
                                            (EL.IDTPENDEFILI(+) = 'P')            AND
                                            (NF.IDENTRSAIDOP    = 'E')            AND
                                            (EP.CDEMPRESA       = A.CDEMPRESA)    AND
                                            (IE.CDEMPRESA       = A.CDEMPRESA)    AND
                                            (IE.CDINSCESTA      = A.CDINSCESTA)   AND
                                            (SUBSTR(A.CDFISCOPERNF,1,1) = '2')    AND
                                            (ET.CDPAIS(+)       = EF.CDPAIS)      AND
                                            (ET.SGESTADO(+)     = EF.SGESTADO)    AND
                                            (ETL.CDPAIS(+)      = EL.CDPAIS)      AND
                                            (ETL.SGESTADO(+)    = EL.SGESTADO)    AND
                                            (ETC.CDPAIS(+)      = EC.CDPAIS)      AND
                                            (ETC.SGESTADO(+)    = EC.SGESTADO)    AND
                                            ((ETL.SGESTADO <> 'PR') OR (ETC.SGESTADO <> 'PR') OR (ET.SGESTADO <> 'PR'))
                                      GROUP BY NVL(NVL(ET.SGESTADO, ETL.SGESTADO), ETC.SGESTADO)
                                      ORDER BY NMESTADO )
                             ORDER BY 5 ) LOOP
         V_COLUNA := NULL;
         V_COLUNA := '23'||C_GI_ENTRADAS.COD_ESTADO||'1'||LPAD( TO_CHAR(ROUND(NVL(C_GI_ENTRADAS.VRCONTABILNF,'0'),0)), 15, '0');
         V_CONTADOR := 2;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
         V_CONTADOR_GI := NVL(V_CONTADOR_GI,0) + 1;
         V_COLUNA := NULL;
         V_COLUNA := '23'||C_GI_ENTRADAS.COD_ESTADO||'2'||LPAD( TO_CHAR(ROUND(NVL(C_GI_ENTRADAS.VRBASECALCNF,'0'),0)), 15, '0');
         V_CONTADOR := NVL(V_CONTADOR,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
         V_CONTADOR_GI := NVL(V_CONTADOR_GI,0) + 1;
         V_COLUNA := NULL;
         V_COLUNA := '23'||C_GI_ENTRADAS.COD_ESTADO||'3'||LPAD( TO_CHAR(ROUND(NVL(C_GI_ENTRADAS.VROUTRCRED,'0'),0)), 15, '0');
         V_CONTADOR := NVL(V_CONTADOR,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
         V_CONTADOR_GI := NVL(V_CONTADOR_GI,0) + 1;
      END LOOP;
      FOR C_GI_SAIDAS IN (SELECT VRCONTABILNF,
                                 VRBASECALCNF,
                                 VROUTRCRED,
                                 NMESTADO,
                                 DECODE(NMESTADO, 'AC', '01', DECODE(NMESTADO, 'AL', '02', DECODE(NMESTADO, 'AP', '03', DECODE(NMESTADO, 'AM', '04',
                                 DECODE(NMESTADO, 'BA', '05', DECODE(NMESTADO, 'CE', '06', DECODE(NMESTADO, 'DF', '07', DECODE(NMESTADO, 'ES', '08',
                                 DECODE(NMESTADO, 'GO', '10', DECODE(NMESTADO, 'MA', '12', DECODE(NMESTADO, 'MT', '13', DECODE(NMESTADO, 'MG', '14',
                                 DECODE(NMESTADO, 'PA', '15', DECODE(NMESTADO, 'PB', '16', DECODE(NMESTADO, 'PE', '18', DECODE(NMESTADO, 'PI', '19',
                                 DECODE(NMESTADO, 'RN', '20', DECODE(NMESTADO, 'RS', '21', DECODE(NMESTADO, 'RJ', '22', DECODE(NMESTADO, 'RO', '23',
                                 DECODE(NMESTADO, 'RR', '24', DECODE(NMESTADO, 'SC', '25', DECODE(NMESTADO, 'SP', '26', DECODE(NMESTADO, 'SE', '27',
                                 DECODE(NMESTADO, 'MS', '28', DECODE(NMESTADO, 'TO', '29','')))))))))))))))))))))))))) AS COD_ESTADO
                            FROM (SELECT SUM(A.VRCONTABILNF) AS VRCONTABILNF,
                                         SUM(A.VRBASECALCNF) AS VRBASECALCNF,
                                         SUM(A.VROUTRCRED+A.VRISENTRIB)   AS VROUTRCRED,
                                         NVL(NVL(ET.SGESTADO, ETF.SGESTADO), ETL.SGESTADO) AS NMESTADO
                                  FROM NOTAFISCAL NF, ENDECLIE EC,
                                       EMPRESA EP, INSCRESTAD IE, ESTADO ET,
                                       ENDEFORN EF, ENDEFILI EL, ESTADO ETF,
                                       ESTADO ETL,
                                       (SELECT MP.CDEMPRESA, MP.CDINSCESTA,
                                               MP.NRSEQUMAPAPU, IP.CDFILIAL, IP.NRLANCTONF, CDFISCOPERNF,
                                               MIN(MP.DTINIPERIODO)AS DTINIPERIODO,
                                               MAX(MP.DTFIMPERIODO)AS DTFIMPERIODO,
                                               SUM(ROUND(IP.VRCONTABILNF,2)) AS VRCONTABILNF,
                                               SUM(ROUND(DECODE(IP.CDFISCALNF, '1',IP.VRBASECALCNF,0),2)) AS VRBASECALCNF,
                                               SUM(ROUND(DECODE(IP.CDFISCALNF, '1',IP.VRIMPOSTONF,0),2))  AS VRIMPOSTONF,
                                               SUM(ROUND(DECODE(IP.CDFISCALNF, '3',IP.VRCONTABILNF,0),2))   AS VROUTRCRED,
                                               SUM(ROUND(DECODE(CDFISCALNF, '2', VRCONTABILNF, DECODE(CDFISCALNF, 1, DECODE(VRPERREDBCNF, 0, 0, (CASE WHEN VRCONTABILNF > VRBASECALCNF THEN VRCONTABILNF - VRBASECALCNF ELSE 0 END)) ,0)), 2)) AS VRISENTRIB
                                        FROM MAPAAPURAC MP, IMPOSTONF IP, IMPOSTO I
                                        WHERE (MP.CDEMPRESA    = P_CDEMPRESA)     AND
                                              (MP.CDINSCESTA   = P_CDINSCESTA)    AND
                                              (MP.CDIMPOSTO    = P_CDIMPOSTO)     AND
                                              (MP.DTINIPERIODO >= P_DTINICIAL)    AND
                                              (MP.DTFIMPERIODO <= P_DTFINAL)      AND
                                              (MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU) AND
                                              (IP.CDIMPOSTO    = I.CDIMPOSTO)     AND
                                              (I.CDIMPOSTONFE  = P_CDIMPOSTO)     AND
                                              ((IP.IDIMPOSTONF <> '3') OR (IP.IDIMPOSTONF IS NULL))
                                        GROUP BY MP.CDEMPRESA, MP.CDINSCESTA, MP.NRSEQUMAPAPU, IP.CDFILIAL, IP.NRLANCTONF, CDFISCOPERNF
                                        UNION
                                        SELECT MP.CDEMPRESA, MP.CDINSCESTA,
                                               MP.NRSEQUMAPAPU, IP.CDFILIAL, IP.NRLANCTONF, CDFISCOPERNF,
                                               MIN(MP.DTINIPERIODO)AS DTINIPERIODO,
                                               MAX(MP.DTFIMPERIODO)AS DTFIMPERIODO,
                                               0 AS VRCONTABILNF,
                                               0 AS VRBASECALCNF, 0 AS VRIMPOSTONF,
                                               SUM(ROUND(VRIMPOSTONF,2)) AS VROUTRCRED,   0 AS VRISENTRIB
                                        FROM IMPOSTONF IP, MAPAAPURAC MP, IMPOSTO I
                                        WHERE (MP.CDEMPRESA    = P_CDEMPRESA)      AND
                                              (MP.CDINSCESTA   = P_CDINSCESTA)     AND
                                              (MP.CDIMPOSTO    = P_CDIMPOSTO)      AND
                                              (MP.DTINIPERIODO >= P_DTINICIAL)     AND
                                              (MP.DTFIMPERIODO <= P_DTFINAL)       AND
                                              (MP.NRSEQUMAPAPU  = IP.NRSEQUMAPAPU) AND
                                              (IP.CDIMPOSTO     = I.CDIMPOSTO)     AND
                                              (I.CDIMPOSTONFE  <> P_CDIMPOSTO)     AND
                                              (IDIMPOSTONF      = '4')
                                        GROUP BY MP.CDEMPRESA, MP.CDINSCESTA, MP.NRSEQUMAPAPU, IP.CDFILIAL, IP.NRLANCTONF, CDFISCOPERNF ) A
                                  WHERE
                                        (A.CDFILIAL         = NF.CDFILIAL)    AND
                                        (A.NRLANCTONF       = NF.NRLANCTONF)  AND
                                        (NF.IDENTRSAIDOP    = 'S')            AND
                                        (EP.CDEMPRESA       = A.CDEMPRESA)   AND
                                        (IE.CDEMPRESA       = A.CDEMPRESA)   AND
                                        (IE.CDINSCESTA      = A.CDINSCESTA)  AND
                                        (SUBSTR(A.CDFISCOPERNF,1,1) = '6')    AND
                                        (EF.CDFORNECED(+)   = NF.CDFORNECED)  AND
                                        (EC.CDCLIENTE(+)    = NF.CDCLIENTE)   AND
                                        (EL.CDFILIAL(+)     = NF.CDFILIRECE)  AND
                                        (EF.IDTPENDEFORN(+) = 'P')            AND
                                        (EC.IDTPENDECLIE(+) = 'P')            AND
                                        (EL.IDTPENDEFILI(+) = 'P')            AND
                                        (ET.CDPAIS(+)       = EC.CDPAIS)      AND
                                        (ET.SGESTADO(+)     = EC.SGESTADO)    AND
                                        (ETF.CDPAIS(+)      = EF.CDPAIS)      AND
                                        (ETF.SGESTADO(+)    = EF.SGESTADO)    AND
                                        (ETL.CDPAIS(+)      = EL.CDPAIS)      AND
                                        (ETL.SGESTADO(+)    = EL.SGESTADO)    AND
                                        ((ETL.SGESTADO <> 'PR') OR (ETF.SGESTADO <> 'PR') OR (ET.SGESTADO <> 'PR'))
                                  GROUP BY NVL(NVL(ET.SGESTADO, ETF.SGESTADO), ETL.SGESTADO) )
                                  ORDER BY 5  ) LOOP
         V_COLUNA := NULL;
         V_COLUNA := '25'||C_GI_SAIDAS.COD_ESTADO||'2'||LPAD( TO_CHAR(ROUND(NVL(C_GI_SAIDAS.VRCONTABILNF,'0'),0)), 15, '0');
         V_CONTADOR := NVL(V_CONTADOR,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
         V_CONTADOR_GI := NVL(V_CONTADOR_GI,0) + 1;
         V_COLUNA := NULL;
         V_COLUNA := '25'||C_GI_SAIDAS.COD_ESTADO||'4'||LPAD( TO_CHAR(ROUND(NVL(C_GI_SAIDAS.VRBASECALCNF,'0'),0)), 15, '0');
         V_CONTADOR := NVL(V_CONTADOR,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
         V_CONTADOR_GI := NVL(V_CONTADOR_GI,0) + 1;
         V_COLUNA := NULL;
         V_COLUNA := '25'||C_GI_SAIDAS.COD_ESTADO||'5'||LPAD( TO_CHAR(ROUND(NVL(C_GI_SAIDAS.VROUTRCRED,'0'),0)), 15, '0');
         V_CONTADOR := NVL(V_CONTADOR,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
         V_CONTADOR_GI := NVL(V_CONTADOR_GI,0) + 1;
      END LOOP;
      V_CONTADOR := 1;
      BEGIN
         SELECT I.CDINSCESTA, I.NRINSJURIEST, C.NRCRCCONT2, C.NRINSJURCONT
           INTO V_CDINSCESTA, V_CNPJ, V_CRCCONTADOR, V_CPFCONTADOR
           FROM INSCRESTAD I, CONTADOR C
          WHERE I.CDEMPRESA   = P_CDEMPRESA
            AND I.CDINSCESTA  = P_CDINSCESTA
            AND I.CDCONTADOR2 = C.CDCONTADOR2;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDINSCESTA  := NULL;
            V_CNPJ        := NULL;
            V_CRCCONTADOR := NULL;
      END;
      V_COLUNA := '1'||
                  P_TPDOCUMENTO||
                  REPLACE(REPLACE(V_CDINSCESTA,'.',''),'-','') ||
                  V_CNPJ||
                  TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'yyyy'))-1)|| '00' ||
                  'F'||
                  RPAD(NVL(REPLACE(REPLACE(V_CPFCONTADOR,'-',''),'/',''),' '), 15, '0')||
                  ' '||
                  LPAD(' ', 59, ' ')||
                  LPAD(TO_CHAR(V_CONTADOR_GI), 3, '0');
      V_CONTADOR := 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        (LPAD(RTRIM(V_CONTADOR), 10, '0'), V_COLUNA, P_NRSEQARQMAG);
   END IF;
END;

/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_DFC_GI','5.07.200'); END;
/


