CREATE OR REPLACE PROCEDURE ARQUIVO_GIA(
   P_IDTRANSMITIDO IN NOTAFISCAL.IDSTATUS%TYPE,
   P_CDEMPRESA     IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA    IN EMPRESA.CDINSCESTA%TYPE,
   P_CDIMPOSTO     IN IMPOSTO.CDIMPOSTO%TYPE,
   P_DTINICIAL     IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL       IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_TIPO          IN NOTAFISCAL.IDSTATUS%TYPE,
   P_MOVIMENTO     IN NOTAFISCAL.IDSTATUS%TYPE,
   P_CDVERSAO      IN NOTAFISCAL.CDFILIAL%TYPE,
   P_NRSEQARQMAG   IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_CDLIVROMAG    IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   P_GERADIPAM     IN VARCHAR)
AS
   V_CONTREG        NUMBER;
   V_DIPAM          NUMBER;
   V_PAIS           VARCHAR2(4);
   V_NRSEQUMAPAPU   VARCHAR2(10);
   V_IMPIPI         VARCHAR2(2);
   V_IMPSUBTRIB     VARCHAR2(2);
   V_IMPCARGA       VARCHAR2(2);
   V_NRSEQUMAPAST   VARCHAR2(10);
   V_CONTADOR_10    NUMBER;
   V_CONTADOR_20    NUMBER;
   V_DIFERENCA      NUMBER;
   V_INDICE         NUMBER;
   V_CONTADORES     VARCHAR2(20);
   V_SAIDAS         NUMBER;
   V_ENTRADAS       NUMBER;
   V_VRADICIONADO   NUMBER;
   V_VRADICMUNI     NUMBER;
BEGIN
   V_CONTREG    := 0;
   V_DIPAM      := 0;
   V_PAIS       := '0055';
   V_IMPIPI     := '10';
   V_IMPSUBTRIB := '80';
   V_IMPCARGA   := '03';

   SELECT MAX(NRSEQUMAPAPU)
     INTO V_NRSEQUMAPAPU
     FROM MAPAAPURAC MP
    WHERE MP.CDEMPRESA  = P_CDEMPRESA
      AND MP.CDINSCESTA = P_CDINSCESTA
      AND MP.CDIMPOSTO  = P_CDIMPOSTO
      AND TO_CHAR(MP.DTINIPERIODO, 'YYYYMM') BETWEEN TO_CHAR(P_DTINICIAL, 'YYYYMM') AND TO_CHAR(P_DTFINAL, 'YYYYMM');

   GERA_DADOS_GIA_SP(V_NRSEQUMAPAPU);

   FOR C_REGABERTURA IN (SELECT ('01'|| /* Codigo Registro */
                                '01'|| /* Tipo de Documento */
                                TO_CHAR(SYSDATE,'YYYYMMDD')|| /* Data Geracao */
                                TO_CHAR(SYSDATE,'HH24MISS')|| /* Hora Geracao */
                                '0000'|| /* Versao GIA */
                                LPAD(TO_CHAR(P_CDVERSAO),4,0) || /* Versao Layout */
                                LPAD(TO_CHAR(1),4,0) /* Numero de registros 05 */) AS DSARQUMAG
                           FROM DUAL ) LOOP
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD('00',10,'0'), C_REGABERTURA.DSARQUMAG, P_NRSEQARQMAG);
   END LOOP;

   --------- Detalhes de CFOPs ---------
   -------------------------------------
   FOR C_REGCFOP IN (SELECT ('10' || /* Codigo do Registro */
                            RPAD(NVL(T.CDFISCOPERNF,'0'), 6, 0) || /* Codigo Fiscal de Operacao */
                            LTRIM(REPLACE(TO_CHAR(SUM(ROUND(T.VRCONTABILNF, 2)), '0000000000000.00'), '.', '')) || /* Valor Contabil */
                            LTRIM(REPLACE(TO_CHAR(SUM(ROUND(T.VRBASECALCNF, 2)), '0000000000000.00'), '.', '')) || /* Base de Calculo */
                            LTRIM(REPLACE(TO_CHAR(SUM(ROUND(T.VRIMPOSTONF,  2)), '0000000000000.00'), '.', '')) || /* Valor do Imposto */
                            LTRIM(REPLACE(TO_CHAR(SUM(ROUND(T.VRISENTO,     2)), '0000000000000.00'), '.', '')) || /* Isentas e Nao Tributadas */
                            LTRIM(REPLACE(TO_CHAR(SUM(ROUND(T.VROUTROS,     2)), '0000000000000.00'), '.', '')) || /* Outras Operacoes */
                            LTRIM(REPLACE(TO_CHAR(SUM(ROUND(T.VRIMPRETST,   2)), '0000000000000.00'), '.', '')) || /* Imposto Retido por Substituicao Tributaria */
                            LTRIM(REPLACE(TO_CHAR(SUM(ROUND(T.VRSUBSTITUTO, 2)), '0000000000000.00'), '.', '')) || /* Imposto Retido pelo substituto */
                            LTRIM(REPLACE(TO_CHAR(SUM(ROUND(T.VRSUBSTITUIDO,2)), '0000000000000.00'), '.', '')) || /* Imposto Retido por Substituicao Tributaria (Substituido) */
                            LTRIM(REPLACE(TO_CHAR(SUM(ROUND(T.VROUTROSIMP,  2)), '0000000000000.00'), '.', '')) || /* Outros Impostos */
                            LPAD(NVL(NRREGCR14,0),4,0) /* Quantidade de registros CR14 */ ) AS DSARQUMAG, T.CDFISCOPERNF
                       FROM TMP_REGDADOSGIASP T, (SELECT C.CDFISCOPERNF, COUNT(C.NRREGCR14) AS  NRREGCR14
                                                    FROM (SELECT COUNT(ORIGEM_NOTA(CDFILIAL, NRLANCTONF, 'E')) AS NRREGCR14,
                                                                 ORIGEM_NOTA(CDFILIAL, NRLANCTONF, 'C') AS SGESTACFOP,
                                                                 ORIGEM_NOTA(CDFILIAL, NRLANCTONF, 'E') AS CDESTAEXPO,
                                                                 CDFISCOPERNF
                                                            FROM IMPOSTONF IP, IMPOSTO IM
                                                           WHERE IM.CDIMPOSTONFE  = P_CDIMPOSTO
                                                             AND IM.CDIMPOSTO     = IP.CDIMPOSTO
                                                             AND IP.NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                                                             AND NVL(IP.IDIMPOSTONF,'1') <> '3'
                                                             AND ((SUBSTR(IP.CDFISCOPERNF, 1, 1) = '2') OR (SUBSTR(IP.CDFISCOPERNF, 1, 1) = '6'))
                                                           GROUP BY ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C'),
                                                                    ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E'), IP.CDFISCOPERNF) C
                                                   GROUP BY C.CDFISCOPERNF) B
                      WHERE T.CDFISCOPERNF = B.CDFISCOPERNF(+)
                        AND T.CDFISCOPERNF IS NOT NULL
                      GROUP BY T.CDFISCOPERNF, LPAD(NVL(NRREGCR14,0),4,0)
                      ORDER BY T.CDFISCOPERNF) LOOP

      /* Contador do registro 10 */
      V_CONTADOR_10 := NVL(V_CONTADOR_10,0)+1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('02'||LPAD(RTRIM(TO_CHAR(NVL(V_CONTREG,'0'))), 8, '0'), C_REGCFOP.DSARQUMAG, P_NRSEQARQMAG);
      V_CONTREG := V_CONTREG + 1;

      ------ Detalhes Interestaduais ------
      -------------------------------------
      IF SUBSTR(C_REGCFOP.CDFISCOPERNF,1,1) IN ('2', '6') THEN
         FOR C_REGDTEINTER IN (SELECT ('14' || /* Codigo do Registro */
                                      NVL(C.CDESTAEXPO,C.SGESTADO) || /* Unidade de Federacao */
                                      LTRIM(REPLACE(TO_CHAR(SUM(ROUND(C.VRCONTABILC, 2)), '0000000000000.00'), '.', '')) || /* Valor Contabil de Contribuinte */
                                      LTRIM(REPLACE(TO_CHAR(SUM(ROUND(C.VRBASECALCC, 2)), '0000000000000.00'), '.', '')) || /* Base de Calculo de Contribuinte */
                                      LTRIM(REPLACE(TO_CHAR(SUM(ROUND(C.VRCONTABILNC,2)), '0000000000000.00'), '.', '')) || /* Valor Contabil de Nao Contribuinte */
                                      LTRIM(REPLACE(TO_CHAR(SUM(ROUND(C.VRBASECALCNC,2)), '0000000000000.00'), '.', '')) || /* Base de Calculo de Nao Contribuinte */
                                      LTRIM(REPLACE(TO_CHAR(SUM(ROUND(C.VRIMPOSTONF ,2)), '0000000000000.00'), '.', '')) || /* Imposto Creditado ou Debitado */
                                      LTRIM(REPLACE(TO_CHAR(SUM(ROUND(C.VROUTROS,    2)), '0000000000000.00'), '.', '')) || /* Outras operacoes */
                                      LTRIM(REPLACE(TO_CHAR(SUM(ROUND(C.VRCOBRADOST, 2)), '0000000000000.00'), '.', '')) || /* ICMS Cobrado por Substituicao Tributaria */
                                      '000000000000000'                                                                  || /* Petroleo e Energia quando ICMS cobrado por Substituicao Tributaria */
                                      LTRIM(REPLACE(TO_CHAR(SUM(ROUND(C.VROUTRPROD,  2)), '0000000000000.00'), '.', '')) || /* Outros Produtos */
                                      C.IDOPERISENTAZFM                                                                  || /* Indica se ha alguma operacao Beneficiada por isencao de ICMS (ZFM/ALC) */
                                      LPAD(NVL(C.NRREGCR18,0),4,0) /* Quantidade de registros CR18 */ ) AS DSARQUMAG,
                                      C.SGESTADO, C.CDFISCOPERNF
                                 FROM (SELECT ORIGEM_NOTA(T.CDFILIAL, T.NRLANCTONF, 'C') AS SGESTADO,
                                              ORIGEM_NOTA(T.CDFILIAL, T.NRLANCTONF, 'E') AS CDESTAEXPO,
                                              CASE WHEN T.CDFISCOPERNF NOT IN ('6108', '6258', '6307') THEN T.VRCONTABILNF ELSE 0 END AS VRCONTABILC,
                                              CASE WHEN T.CDFISCOPERNF NOT IN ('6108', '6258', '6307') THEN T.VRBASECALCNF ELSE 0 END AS VRBASECALCC,
                                              CASE WHEN T.CDFISCOPERNF     IN ('6108', '6258', '6307') THEN T.VRCONTABILNF ELSE 0 END AS VRCONTABILNC,
                                              CASE WHEN T.CDFISCOPERNF     IN ('6108', '6258', '6307') THEN T.VRBASECALCNF ELSE 0 END AS VRBASECALCNC,
                                              T.VRIMPOSTONF, T.VROUTROS,
                                              DECODE(SUBSTR(T.CDFISCOPERNF,1,1),'6',T.VRSUBSTITUTO+T.VRSUBSTITUIDO,0) AS VRCOBRADOST,
                                              DECODE(SUBSTR(T.CDFISCOPERNF,1,1),'2',T.VRSUBSTITUTO+T.VRSUBSTITUIDO,0) AS VROUTRPROD,
                                              DECODE(SUBSTR(T.CDFISCOPERNF,1,1),'6',DECODE(N.CDFISCOPERNF,T.CDFISCOPERNF,DECODE(N.IDMUNIALC,'S','1','0'),'0'),'0') AS IDOPERISENTAZFM,
                                              T.CDFISCOPERNF, N.NRREGCR18
                                         FROM TMP_REGDADOSGIASP T, (SELECT CDFISCOPERNF, CDESTAEXPO, SGESTACFOP, IDMUNIALC, COUNT(NRLANCTONF) AS NRREGCR18
                                                                      FROM (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF, IP.CDFISCOPERNF,
                                                                                   ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E') AS CDESTAEXPO,
                                                                                   ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C') AS SGESTACFOP,
                                                                                   ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M') AS IDMUNIALC
                                                                              FROM IMPOSTONF IP, IMPOSTO IM
                                                                             WHERE IP.CDFISCALNF <> '1'
                                                                               AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                                                                               AND IP.CDIMPOSTO    = IM.CDIMPOSTO
                                                                               AND IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU
                                                                               AND IP.CDFISCOPERNF = C_REGCFOP.CDFISCOPERNF
                                                                               AND SUBSTR(IP.CDFISCOPERNF,1,1) = '6'
                                                                               AND ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M') = 'S')
                                                                             GROUP BY CDFISCOPERNF, IDMUNIALC, CDESTAEXPO, SGESTACFOP) N
                                        WHERE T.CDFISCOPERNF = C_REGCFOP.CDFISCOPERNF
                                          AND T.CDFISCOPERNF = N.CDFISCOPERNF(+)
                                          AND ORIGEM_NOTA(T.CDFILIAL, T.NRLANCTONF, 'E') = N.CDESTAEXPO(+)
                                          AND T.CDFISCOPERNF IS NOT NULL) C
                                GROUP BY C.CDFISCOPERNF, C.NRREGCR18, IDOPERISENTAZFM, C.CDESTAEXPO, C.SGESTADO
                                ORDER BY C.CDFISCOPERNF, NVL(C.CDESTAEXPO,C.SGESTADO)) LOOP

            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('02'||LPAD(RTRIM(TO_CHAR(NVL(V_CONTREG,'0'))), 8, '0'), C_REGDTEINTER.DSARQUMAG, P_NRSEQARQMAG);
            V_CONTREG := V_CONTREG + 1;

            -------------- ZFM/ALC --------------
            -------------------------------------
            FOR C_REGZFMALC IN (SELECT I.NRSEQUMAPAPU,
                                       (LPAD(I.CDFISCOPERNF,4,0)||NVL(ORIGEM_NOTA(N.CDFILIAL, N.NRLANCTONF, 'E'),'00')||N.NRNOTAFISC) AS CDAUXILIAR,
                                       ('18'|| /* Codigo do Registro */
                                        SUBSTR(LPAD(N.NRNOTAFISC,10,'0'),2,9)|| /* Numero da Nota Fiscal */
                                        TO_CHAR(N.DTEMISSAO, 'YYYYMMDD')|| /* Data de Emissao */
                                        LTRIM(REPLACE(TO_CHAR(I.VRCONTABILNF, '0000000000000.00'), '.', ''))|| /* Valor da Nota Fiscal */
                                        RPAD(REPLACE(REPLACE(REPLACE(REPLACE(N.NRINSJURNF,'.'),'-'),'/'),' '), 14, ' ')|| /* Inscricao Juridica da Nota Fiscal */
                                        NVL(ORIGEM_NOTA(N.CDFILIAL, N.NRLANCTONF, 'O'), LPAD(NVL(ORIGEM_NOTA(N.CDFILIAL, N.NRLANCTONF, 'U'),0), 5, 0)) /* Codigo do Municipio de Destino */) AS DSARQUMAG
                                FROM NOTAFISCAL N, MAPAAPURAC MP,
                                     (SELECT NRSEQUMAPAPU, CDFILIAL, NRLANCTONF,
                                             MAX(CDFISCOPERNF) AS CDFISCOPERNF, SUM(VRCONTABILNF) AS VRCONTABILNF
                                      FROM IMPOSTONF IP, IMPOSTO IM
                                      WHERE CDFISCALNF     <> '1'
                                        AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                                        AND IM.CDIMPOSTO    = IP.CDIMPOSTO
                                        AND NRSEQUMAPAPU    = v_NRSEQUMAPAPU
                                        AND C_REGCFOP.CDFISCOPERNF   = CDFISCOPERNF
                                        AND SUBSTR(CDFISCOPERNF,1,1) = '6'
                                      GROUP BY NRSEQUMAPAPU, CDFILIAL, NRLANCTONF) I
                                WHERE MP.CDEMPRESA    = P_CDEMPRESA
                                  AND MP.CDINSCESTA   = P_CDINSCESTA
                                  AND MP.CDIMPOSTO    = P_CDIMPOSTO
                                  AND TO_CHAR(MP.DTINIPERIODO, 'YYYYMM') BETWEEN TO_CHAR(P_DTINICIAL, 'YYYYMM') AND TO_CHAR(P_DTFINAL, 'YYYYMM')
                                  AND MP.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                                  AND I.CDFILIAL      = N.CDFILIAL
                                  AND I.NRLANCTONF    = N.NRLANCTONF
                                  AND ORIGEM_NOTA(I.CDFILIAL, I.NRLANCTONF, 'C') = C_REGDTEINTER.SGESTADO
                                  AND ORIGEM_NOTA(N.CDFILIAL, N.NRLANCTONF, 'M') = 'S'
                                  AND I.CDFISCOPERNF IS NOT NULL
                                ORDER BY 1, 2, 3 ) LOOP
               INSERT INTO ARQMAGNETICO
                 (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                 ('02'||LPAD(RTRIM(TO_CHAR(NVL(V_CONTREG,'0'))), 8, '0'), C_REGZFMALC.DSARQUMAG, P_NRSEQARQMAG);
               V_CONTREG := V_CONTREG + 1;
            END LOOP;
         END LOOP;
      END IF;
   END LOOP;
   ------------ OCORRENCIAS ------------
   -------------------------------------
   FOR C_REGOCORRENCIA IN (SELECT IT.NRSEQUMAPAPU,
                                  ('99999999'||LPAD(IT.CDITEMOCOR,5,0)) AS CDAUXILIAR,
                                  ('20'|| /* Codigo do Registro */
                                  LPAD(IT.CDITEMOCOR, 5, ' ')|| /* Codigo do Subitem */
                                  LTRIM(REPLACE(TO_CHAR(SUM(IT.VRITEMMAPR), '0000000000000.00'), '.', ''))|| /* Valor do Subitem */
                                  '0' || /* Propria ou de Substituicao Tributaria */
                                  RPAD(NVL(IT.DSFLEGALOCOR,' '), 100, ' ')|| /* Fundamentacao legal do Subitem */
                                  RPAD(NVL(IT.DSITEMOCOR,' '), 300, ' ')|| /* Descricao do Subitem */
                                  '0000' || /* Quantidade de registros CR25 */
                                  '0000' || /* Quantidade de registros CR26 */
                                  '0000' || /* Quantidade de registros CR27 */
                                  '0000'    /* Quantidade de registros CR28 */ ) AS DSARQUMAG
                             FROM ITMAPARECUP IT, MAPAAPURAC MP
                            WHERE MP.CDEMPRESA    = P_CDEMPRESA
                              AND MP.CDINSCESTA   = P_CDINSCESTA
                              AND MP.CDIMPOSTO    = P_CDIMPOSTO
                              AND TO_CHAR(MP.DTINIPERIODO, 'YYYYMM') BETWEEN TO_CHAR(P_DTINICIAL, 'YYYYMM') AND TO_CHAR(P_DTFINAL, 'YYYYMM')
                              AND MP.NRSEQUMAPAPU = IT.NRSEQUMAPAPU
                              AND IT.CDITEMOCOR   IS NOT NULL
                            GROUP BY IT.NRSEQUMAPAPU, IT.CDITEMOCOR, IT.DSFLEGALOCOR, IT.DSITEMOCOR
                            ORDER BY 1, 2, 3) LOOP
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('02'||LPAD(RTRIM(TO_CHAR(NVL(V_CONTREG,'0'))), 8, '0'), C_REGOCORRENCIA.DSARQUMAG, P_NRSEQARQMAG);
      V_CONTREG := V_CONTREG + 1;
      V_CONTADOR_20 := NVL(V_CONTADOR_20,0) + 1;
   END LOOP;

   IF P_GERADIPAM = 'S' THEN
      FOR C_DIPAM_B IN (SELECT SUM(ROUND(I.VRCONTABILNF,2)) AS VRADICIONADO,
                               NVL(MU.CDMUNIEXPO, MU.CDMUNICIPIO) AS CDMUNIEXPO
                          FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I,
                               FORNECEDOR F, ENDEFORN EF, MUNICIPIO MU,
                               (SELECT MAX(CDMODDOCFISC) AS CDMODDOCFISC, NRLANCTONF, CDFILIAL
                                  FROM NFPRODRURAL
                                 GROUP BY NRLANCTONF, CDFILIAL) NP
                         WHERE M.CDEMPRESA     = P_CDEMPRESA
                           AND M.CDINSCESTA    = P_CDINSCESTA
                           AND M.CDIMPOSTO     = P_CDIMPOSTO
                           AND M.DTINIPERIODO >= P_DTINICIAL
                           AND M.DTFIMPERIODO <= P_DTFINAL
                           AND M.NRSEQUMAPAPU  = I.NRSEQUMAPAPU
                           AND I.NRLANCTONF    = N.NRLANCTONF
                           AND I.CDFILIAL      = N.CDFILIAL
                           AND N.CDFILIAL      = NP.CDFILIAL(+)
                           AND N.NRLANCTONF    = NP.NRLANCTONF(+)
                           AND N.CDFORNECED    = F.CDFORNECED
                           AND ((NP.CDMODDOCFISC IS NOT NULL) OR (N.CDMODDOCFISC = '04') OR (F.IDFORNPRODRURAL = 'S'))
                           AND SUBSTR(CDFISCOPERNF,1,1) = '1'
                           AND ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                           AND N.CDFORNECED    = EF.CDFORNECED
                           AND NVL(EF.IDTPENDEFORN,'P') = 'P'
                           AND EF.CDPAIS       = MU.CDPAIS
                           AND EF.SGESTADO     = MU.SGESTADO
                           AND EF.CDMUNICIPIO  = MU.CDMUNICIPIO
                         GROUP BY NVL(MU.CDMUNIEXPO, MU.CDMUNICIPIO)
                         ORDER BY NVL(MU.CDMUNIEXPO, MU.CDMUNICIPIO)) LOOP
         IF C_DIPAM_B.VRADICIONADO > 0 THEN
            V_DIPAM := V_DIPAM + 1;
            INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
               ('08'||LPAD(V_DIPAM,8,'0'),
                '3011'||LPAD(NVL(SUBSTR(C_DIPAM_B.CDMUNIEXPO,2,5),0),5,0)||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(NVL(C_DIPAM_B.VRADICIONADO,0),2),'9999999999990.00')),'.',''),15,0),
                P_NRSEQARQMAG);
         END IF;
      END LOOP;

      /*Calculo do Valor Adicionado Dipam de acordo com a formula: VRM = (SVM / SVC) x VAG, onde:
          -VRM e o valor de rateio por municipio;
          -SVM e o valor total das saidas por municipio;
          -SVC e o valos das saidas geral;
          -VAG diferenca entre Saidas - Entradas.
        Para todos os valores devem ser considerados apenas os CFOP's presentes no livro.*/

      V_SAIDAS       := 0;
      V_ENTRADAS     := 0;
      V_VRADICIONADO := 0;

      BEGIN
         SELECT VRSAIDA, VRENTRADA, (VRSAIDA-VRENTRADA) AS VRADICIONADO
           INTO V_SAIDAS, V_ENTRADAS, V_VRADICIONADO
           FROM (SELECT SUM(DECODE(IDENTRSAIDOP, 'S', ROUND(DECODE(NVL(F.IDFORNPRODRURAL,'N'),'N',I.VRCONTABILNF,0),2), 0)) AS VRSAIDA,
                        SUM(DECODE(IDENTRSAIDOP, 'E', ROUND(DECODE(NVL(F.IDFORNPRODRURAL,'N'),'N',I.VRCONTABILNF,0),2), 0)) AS VRENTRADA
                   FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I,
                        CFOPLIVROMAG L, FORNECEDOR F
                  WHERE M.CDEMPRESA     = P_CDEMPRESA
                    AND M.CDINSCESTA    = P_CDINSCESTA
                    AND M.CDIMPOSTO     = P_CDIMPOSTO
                    AND M.DTINIPERIODO >= P_DTINICIAL
                    AND M.DTFIMPERIODO <= P_DTFINAL
                    AND M.NRSEQUMAPAPU  = I.NRSEQUMAPAPU
                    AND I.NRLANCTONF    = N.NRLANCTONF
                    AND I.CDFILIAL      = N.CDFILIAL
                    AND N.CDFORNECED    = F.CDFORNECED(+)
                    AND N.CDMODDOCFISC  <> '04'
                    AND ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                    AND L.CDLAYOUTLMAG  = '002'
                    AND I.CDFISCOPERNF  = L.CDCFOPLMAG
                    AND L.CDLIVROMAG    = P_CDLIVROMAG)
          WHERE VRSAIDA > VRENTRADA;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_SAIDAS       := 0;
            V_ENTRADAS     := 0;
            V_VRADICIONADO := 0;
      END;

      IF V_VRADICIONADO > 0 THEN
         V_VRADICMUNI := 0;
         FOR C_DIPAM_B IN (SELECT VRSAIDA, CDMUNIEXPO
                             FROM (SELECT SUM(ROUND(DECODE(NVL(F.IDFORNPRODRURAL,'N'),'N',I.VRCONTABILNF,0),2)) AS VRSAIDA,
                                          LPAD(NVL(MU.CDMUNIEXPO, MU.CDMUNICIPIO),5,0) AS CDMUNIEXPO
                                     FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, FORNECEDOR F,
                                          CFOPLIVROMAG L, ENDEFILI EF, MUNICIPIO MU
                                    WHERE M.CDEMPRESA     = P_CDEMPRESA
                                      AND M.CDINSCESTA    = P_CDINSCESTA
                                      AND M.CDIMPOSTO     = P_CDIMPOSTO
                                      AND M.DTINIPERIODO >= P_DTINICIAL
                                      AND M.DTFIMPERIODO <= P_DTFINAL
                                      AND M.NRSEQUMAPAPU  = I.NRSEQUMAPAPU
                                      AND I.NRLANCTONF    = N.NRLANCTONF
                                      AND I.CDFILIAL      = N.CDFILIAL
                                      AND N.CDMODDOCFISC  <> '04'
                                      AND ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                                      AND L.CDLAYOUTLMAG  = '002'
                                      AND I.CDFISCOPERNF  = L.CDCFOPLMAG
                                      AND N.IDENTRSAIDOP  = 'S'
                                      AND N.CDFILIEMIT    = EF.CDFILIAL
                                      AND NVL(EF.IDTPENDEFILI,'P') = 'P'
                                      AND N.CDFORNECED    = F.CDFORNECED(+)
                                      AND EF.CDPAIS       = MU.CDPAIS
                                      AND EF.SGESTADO     = MU.SGESTADO
                                      AND EF.CDMUNICIPIO  = MU.CDMUNICIPIO
                                      AND L.CDLIVROMAG    = P_CDLIVROMAG
                                    GROUP BY NVL(MU.CDMUNIEXPO, MU.CDMUNICIPIO))
                            WHERE VRSAIDA > 0
                            ORDER BY CDMUNIEXPO) LOOP

            V_VRADICMUNI := (C_DIPAM_B.VRSAIDA / V_SAIDAS) * V_VRADICIONADO;
            V_DIPAM := V_DIPAM + 1;
            INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
               ('08'||LPAD(V_DIPAM,8,'0'),
                '3022'||LPAD(NVL(SUBSTR(C_DIPAM_B.CDMUNIEXPO,2,5),0),5,0)||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(NVL(V_VRADICMUNI,0),2),'9999999999990.00')),'.',''),15,0),
                P_NRSEQARQMAG);
         END LOOP;
      END IF;
   END IF;
   FOR C_REGISTRO11 IN (SELECT MP.NRSEQUMAPAPU,
                               '000000000000' AS CDAUXILIAR,
                               ('05'||
                                 RPAD(REPLACE(REPLACE(REPLACE(IE.CDINSCESTA,'.',''),',',''),'-',''),12,' ')||
                                 RPAD(SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.'),'-'),'/'),' '),1,14),14,' ')||
                                 '0000000'||
                                 '01'||
                                 TO_CHAR(MP.DTINIPERIODO, 'YYYYMM')||
                                 '000000'||
                                 DECODE(P_TIPO,'N','01','S','02','C','03')||
                                 DECODE(P_MOVIMENTO,'S','1','N','0')||
                                 REPLACE(REPLACE(P_IDTRANSMITIDO,'S','1'),'N', '0')||
                                 LTRIM(REPLACE(TO_CHAR(MR.VRSLDCRANTE,'0000000000000.00'),'.',''))||
                                 '000000000000000'||
                                 '26269316000177'||
                                 '0'||
                                 '000000000000000'||
                                 '00000000000000000000000000000000') AS DSARQUMAG
                          FROM INSCRESTAD IE, MAPAAPURAC MP, MAPARECUP MR, EMPRESA E
                         WHERE MP.CDEMPRESA    = P_CDEMPRESA
                           AND MP.CDINSCESTA   = P_CDINSCESTA
                           AND MP.CDIMPOSTO    = P_CDIMPOSTO
                           AND E.CDEMPRESA     = P_CDEMPRESA
                           AND IE.CDEMPRESA    = MP.CDEMPRESA
                           AND IE.CDINSCESTA   = MP.CDINSCESTA
                           AND MP.NRSEQUMAPAPU = MR.NRSEQUMAPAPU
                           AND TO_CHAR(MP.DTINIPERIODO, 'YYYYMM') BETWEEN TO_CHAR(P_DTINICIAL, 'YYYYMM') AND TO_CHAR(P_DTFINAL, 'YYYYMM')
                         ORDER BY 1, 2, 3) LOOP
      IF P_CDVERSAO IN ('0209','0210') THEN
         V_CONTADORES := '0000'||
                         LPAD(NVL(V_CONTADOR_10,0), 4, 0)||
                         LPAD(NVL(V_CONTADOR_20,0), 4, 0)||
                         LPAD(NVL(V_DIPAM,0), 4, 0) ||
                         '0000';
      ELSE
         V_CONTADORES := LPAD(NVL(V_CONTADOR_10,0), 4, 0)||
                         LPAD(NVL(V_CONTADOR_20,0), 4, 0)||
                         LPAD(NVL(V_DIPAM,0), 4, 0) ||
                         '0000';
      END IF;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        (LPAD('01',10,'0'), C_REGISTRO11.DSARQUMAG||V_CONTADORES, P_NRSEQARQMAG);
   END LOOP;

   DELETE FROM TMP_REGDADOSGIASP_AUX;
   INSERT INTO TMP_REGDADOSGIASP_AUX (SELECT * FROM  TMP_REGDADOSGIASP);
   DELETE FROM TMP_REGDADOSGIASP;
   COMMIT;
END;
/