CREATE OR REPLACE PROCEDURE ARQUIVO_GIA_VRBRUTO(
   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_CDVERSAO      IN NOTAFISCAL.CDFILIAL%TYPE,
   P_NRSEQARQMAG   IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_GERADIPAM     IN VARCHAR)
AS
   V_CONTREG      NUMBER;
   V_DIPAM        NUMBER;
   V_PAIS         VARCHAR2(4);
   V_NRSEQUMAPAPU VARCHAR2(20);
BEGIN
   V_CONTREG := 0;
   V_DIPAM   := 0;

   BEGIN
      SELECT CDPAIS
        INTO V_PAIS
        FROM PAIS
       WHERE NMPAIS = 'BRASIL';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_PAIS := NULL;
   END;

   BEGIN
      SELECT NRSEQUMAPAPU
        INTO V_NRSEQUMAPAPU
        FROM MAPAAPURAC
       WHERE CDEMPRESA = P_CDEMPRESA
         AND CDIMPOSTO = P_CDIMPOSTO
         AND CDINSCESTA = P_CDINSCESTA
         AND TO_CHAR(DTINIPERIODO, 'YYYYMM') BETWEEN TO_CHAR(P_DTINICIAL, 'YYYYMM') AND TO_CHAR(P_DTFINAL, 'YYYYMM');
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_NRSEQUMAPAPU := NULL;
   END;

   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(CR5.NRREGCR05),4,0) /* Numero de registros 05 */) AS DSARQUMAG
                        FROM PARAMGERAL P,
                             (SELECT COUNT(*) AS NRREGCR05
                              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')) CR5 ) 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  NF.NRSEQUMAPAPU,
                            (LPAD(NVL(NF.CDFISCOPERNF,'0'),4,0)||'000000000') AS CDAUXILIAR,
                            ('10'|| /* Codigo do Registro */
                             RPAD(NVL(NF.CDFISCOPERNF,'0'), 6, 0)|| /* Codigo Fiscal de Operacao */
                             LTRIM(REPLACE(TO_CHAR(ROUND(SUM(DECODE(IDENTRSAIDOP,'E',VRCONTABILNF,DECODE(IDCONSVRBAPUIMP,'N',VRCONTABILNF,VRCONTABILNF_JLA))),2), '0000000000000.00'), '.', ''))|| /* Valor Contabil */
                             LTRIM(REPLACE(TO_CHAR(ROUND(SUM(DECODE(IDENTRSAIDOP,'E',VRBASECALCNF,DECODE(IDCONSVRBAPUIMP,'N',VRBASECALCNF,VRBASECALCNF_JLA))),2), '0000000000000.00'), '.', ''))|| /* Base de Calculo */
                             LTRIM(REPLACE(TO_CHAR(ROUND(SUM(DECODE(IDENTRSAIDOP,'E',VRIMPOSTONF,DECODE(IDCONSVRBAPUIMP,'N',VRIMPOSTONF,VRIMPOSTONF_JLA))),2), '0000000000000.00'), '.', ''))||  /* Valor do Imposto */
                             LTRIM(REPLACE(TO_CHAR(ROUND(SUM(DECODE(IDENTRSAIDOP,'E',VRISENTRIB,DECODE(IDCONSVRBAPUIMP,'N',VRISENTRIB,VRISENTRIB_JLA))),2), '0000000000000.00'), '.', ''))||   /* Isentas e Nao Tributadas */
                             LTRIM(REPLACE(TO_CHAR(ROUND(SUM(VROUTRCRED),2), '0000000000000.00'), '.', ''))||   /* Outras Operacoes */
                             '000000000000000'|| /* Imposto Retido por Substituicao Tributaria */
                             '000000000000000'|| /* Imposto Retido pelo substituto */
                             '000000000000000'|| /* Imposto Retido por Substituicao Tributaria (Substituido) */
                             LTRIM(REPLACE(TO_CHAR(ROUND(SUM(VROUTROIMPO),2), '0000000000000.00'), '.', ''))|| /* Outros Impostos */
                             LPAD(NVL(NRREGCR14,0), 4, 0) /* Quantidade de registros CR14 */) AS DSARQUMAG,
                             NF.CDFISCOPERNF
                     FROM (SELECT IP.NRSEQUMAPAPU,
                                  IP.CDFILIAL,
                                  IP.NRLANCTONF,
                                  CDFISCOPERNF,
                                  ROUND(SUM(VRCONTABILNF),2) AS VRCONTABILNF,
                                  ROUND(SUM(VRCONTABILNF),2) AS VRCONTABILNF_JLA,
                                  ROUND(SUM(VRBASECALCNF),2) AS VRBASECALCNF,
                                  ROUND(SUM(VRBASECALCNF),2) AS VRBASECALCNF_JLA,
                                  ROUND(SUM(VRIMPOSTONF),2)  AS VRIMPOSTONF,
                                  ROUND(SUM(VRIMPOSTONF),2)  AS VRIMPOSTONF_JLA,
                                  0 AS VROUTRCRED,
                                  0 AS VRISENTRIB,
                                  ROUND(SUM(DECODE(VRPERREDBCNF, 0, 0,(VRCONTABILNF - VRBASECALCNF))),2) AS VRISENTRIB_JLA,
                                  0 AS VROUTROIMPO,
                                  ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E') AS CDESTAEXPO
                           FROM  IMPOSTONF IP
                           WHERE IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU AND
                                 CDFISCALNF      = '1'            AND
                                 IP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                 IP.VRCONTABILNF >= IP.VRBASECALCNF AND
                                 (NVL(IDIMPOSTONF,'1') <> '3')
                           GROUP BY IP.NRSEQUMAPAPU, IP.CDFILIAL, IP.NRLANCTONF, CDFISCOPERNF,
                                    ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E')
                           UNION ALL
                           SELECT IP.NRSEQUMAPAPU,
                                  IP.CDFILIAL,
                                  IP.NRLANCTONF,
                                  CDFISCOPERNF,
                                  ROUND(SUM(VRCONTABILNF),2) AS VRCONTABILNF,
                                  ROUND(SUM(VRBASECALCNF),2) AS VRCONTABILNF_JLA,
                                  ROUND(SUM(VRBASECALCNF),2) AS VRBASECALCNF,
                                  0 AS VRBASECALCNF_JLA,
                                  ROUND(SUM(VRIMPOSTONF),2)  AS VRIMPOSTONF,
                                  0 AS VRIMPOSTONF_JLA,
                                  0 AS VROUTRCRED,
                                  0 AS VRISENTRIB,
                                  ROUND(SUM(VRBASECALCNF),2) AS VRISENTRIB_JLA,
                                  0 AS VROUTROIMPO,
                                  ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E') AS CDESTAEXPO
                           FROM  IMPOSTONF IP
                           WHERE IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU AND
                                 CDFISCALNF      = '1'            AND
                                 IP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                 IP.VRCONTABILNF < IP.VRBASECALCNF AND
                                 (NVL(IDIMPOSTONF,'1') <> '3')
                           GROUP BY IP.NRSEQUMAPAPU, IP.CDFILIAL, IP.NRLANCTONF, CDFISCOPERNF,
                                    ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E')
                           UNION ALL
                           SELECT IP.NRSEQUMAPAPU,
                                  IP.CDFILIAL,
                                  IP.NRLANCTONF,
                                  CDFISCOPERNF,
                                  ROUND(SUM(VRCONTABILNF),2) AS VRCONTABILNF,
                                  ROUND(SUM(VRCONTABILNF),2) AS VRCONTABILNF_JLA,
                                  0 AS VRBASECALCNF,
                                  0 AS VRBASECALCNF_JLA,
                                  0 AS VRIMPOSTONF,
                                  0 AS VRIMPOSTONF_JLA,
                                  0 AS VROUTRCRED,
                                  ROUND(SUM(VRCONTABILNF),2) AS VRISENTRIB,
                                  ROUND(SUM(VRCONTABILNF),2) AS VRISENTRIB_JLA,
                                  0 AS VROUTROIMPO,
                                  ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E') AS CDESTAEXPO
                           FROM  IMPOSTONF IP
                           WHERE IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU AND
                                 CDFISCALNF      = '2'            AND
                                 IP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                 IP.VRCONTABILNF >= IP.VRBASECALCNF AND
                                 (NVL(IDIMPOSTONF,'1') <> '3')
                           GROUP BY IP.NRSEQUMAPAPU, IP.CDFILIAL, IP.NRLANCTONF, CDFISCOPERNF,
                                    ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E')
                           UNION ALL
                           SELECT IP.NRSEQUMAPAPU,
                                  IP.CDFILIAL,
                                  IP.NRLANCTONF,
                                  CDFISCOPERNF,
                                  ROUND(SUM(VRCONTABILNF),2) AS VRCONTABILNF,
                                  ROUND(SUM(VRBASECALCNF),2) AS VRCONTABILNF_JLA,
                                  0 AS VRBASECALCNF,
                                  0 AS VRBASECALCNF_JLA,
                                  0 AS VRIMPOSTONF,
                                  0 AS VRIMPOSTONF_JLA,
                                  0 AS VROUTRCRED,
                                  ROUND(SUM(VRCONTABILNF),2) AS VRISENTRIB,
                                  ROUND(SUM(VRBASECALCNF),2) AS VRISENTRIB_JLA,
                                  0 AS VROUTROIMPO,
                                  ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E') AS CDESTAEXPO
                           FROM  IMPOSTONF IP
                           WHERE IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU AND
                                 CDFISCALNF      = '2'            AND
                                 IP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                 IP.VRCONTABILNF < IP.VRBASECALCNF AND
                                 (NVL(IDIMPOSTONF,'1') <> '3')
                           GROUP BY IP.NRSEQUMAPAPU, IP.CDFILIAL, IP.NRLANCTONF, CDFISCOPERNF,
                                    ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E')
                           UNION ALL
                           SELECT IP.NRSEQUMAPAPU,
                                  IP.CDFILIAL,
                                  IP.NRLANCTONF,
                                  CDFISCOPERNF,
                                  ROUND(SUM(VRCONTABILNF),2) AS VRCONTABILNF,
                                  ROUND(SUM(VRCONTABILNF),2) AS VRCONTABILNF_JLA,
                                  0 AS VRBASECALCNF,
                                  0 AS VRBASECALCNF_JLA,
                                  0 AS VRIMPOSTONF,
                                  0 AS VRIMPOSTONF_JLA,
                                  ROUND(SUM(VRCONTABILNF),2) AS VROUTRCRED,
                                  0 AS VRISENTRIB,
                                  0 AS VRISENTRIB_JLA,
                                  0 AS VROUTROIMPO,
                                  ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E') AS CDESTAEXPO
                           FROM  IMPOSTONF IP
                           WHERE IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU AND
                                 CDFISCALNF      = '3'            AND
                                 IP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                 SUBSTR(NVL(IP.CDFISCOPERNF,'000'),1,1) NOT IN ('2', '6') AND
                                 (NVL(IDIMPOSTONF,'1') <> '3')
                           GROUP BY IP.NRSEQUMAPAPU, IP.CDFILIAL, IP.NRLANCTONF, CDFISCOPERNF,
                                    ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E')
                           UNION ALL
                           SELECT IP.NRSEQUMAPAPU,
                                  IP.CDFILIAL,
                                  IP.NRLANCTONF,
                                  CDFISCOPERNF,
                                  ROUND(SUM(VRCONTABILNF),2) AS VRCONTABILNF,
                                  ROUND(SUM(VRCONTABILNF),2) AS VRCONTABILNF_JLA,
                                  0 AS VRBASECALCNF,
                                  0 AS VRBASECALCNF_JLA,
                                  0 AS VRIMPOSTONF,
                                  0 AS VRIMPOSTONF_JLA,
                                  ROUND(SUM(IP.VRCONTABILNF),2) AS VROUTROPER,
                                  0 AS VRISENTRIB,
                                  0 AS VRISENTRIB_JLA,
                                  0 AS VROUTROIMPO,
                                  ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E') AS CDESTAEXPO
                           FROM  IMPOSTONF IP
                           WHERE IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU AND
                                 IP.CDFISCALNF   = '3'            AND
                                 IP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                 (NVL(IP.IDIMPOSTONF,'1') <> '3')
                           GROUP BY IP.NRSEQUMAPAPU, IP.CDFILIAL, IP.NRLANCTONF, CDFISCOPERNF,
                                    ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E')
                           UNION ALL
                           SELECT IP.NRSEQUMAPAPU,
                                  IP.CDFILIAL,
                                  IP.NRLANCTONF,
                                  CDFISCOPERNF,
                                  ROUND(SUM(VRIMPOSTONF),2) AS VRCONTABILNF,
                                  ROUND(SUM(VRIMPOSTONF),2) AS VRCONTABILNF_JLA,
                                  0 AS VRBASECALCNF,
                                  0 AS VRBASECALCNF_JLA,
                                  0 AS VRIMPOSTONF,
                                  0 AS VRIMPOSTONF_JLA,
                                  ROUND(SUM(VRIMPOSTONF),2) AS VROUTRCRED,
                                  0 AS VRISENTRIB,
                                  0 AS VRISENTRIB_JLA,
                                  ROUND(SUM(VRIMPOSTONF),2) AS VROUTROIMPO,
                                  ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E') AS CDESTAEXPO
                           FROM  IMPOSTONF IP
                           WHERE IP.NRSEQUMAPAPU = V_NRSEQUMAPAPU AND
                                 IP.CDIMPOSTO    <> P_CDIMPOSTO   AND
                                 (NVL(IDIMPOSTONF,'1') <> '3')
                           GROUP BY IP.NRSEQUMAPAPU, IP.CDFILIAL, IP.NRLANCTONF, CDFISCOPERNF,
                                    ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E') ) NF,
                           (SELECT C.NRSEQUMAPAPU, C.CDFISCOPERNF,
                                   COUNT(C.NRREGCR14) AS  NRREGCR14
                            FROM (SELECT NRSEQUMAPAPU, CDFISCOPERNF,
                                         COUNT(ORIGEM_NOTA(CDFILIAL, NRLANCTONF, 'E')) AS NRREGCR14,
                                         ORIGEM_NOTA(CDFILIAL, NRLANCTONF, 'C') AS SGESTACFOP,
                                         ORIGEM_NOTA(CDFILIAL, NRLANCTONF, 'E') AS CDESTAEXPO
                                     FROM IMPOSTONF
                                    WHERE CDIMPOSTO  = P_CDIMPOSTO      AND
                                          NRSEQUMAPAPU IS NOT NULL      AND
                                          NVL(IDIMPOSTONF,'1') <> '3'   AND
                                          ((SUBSTR(CDFISCOPERNF, 1, 1) = '2') OR (SUBSTR(CDFISCOPERNF, 1, 1) = '6'))
                                    GROUP BY NRSEQUMAPAPU, CDFISCOPERNF,
                                             ORIGEM_NOTA(CDFILIAL, NRLANCTONF, 'C'),
                                             ORIGEM_NOTA(CDFILIAL, NRLANCTONF, 'E') ) C
                             GROUP BY C.NRSEQUMAPAPU, C.CDFISCOPERNF) B,
                           IMPOSTO I, NOTAFISCAL N
                     WHERE I.CDIMPOSTO     = P_CDIMPOSTO       AND
                           N.CDFILIAL      = NF.CDFILIAL       AND
                           N.NRLANCTONF    = NF.NRLANCTONF     AND
                           NF.NRSEQUMAPAPU = B.NRSEQUMAPAPU(+) AND
                           NF.CDFISCOPERNF = B.CDFISCOPERNF(+) AND
                           NF.CDFISCOPERNF IS NOT NULL
                     GROUP BY NF.NRSEQUMAPAPU,
                            (LPAD(NVL(NF.CDFISCOPERNF,'0'),4,0)||'000000000'),
                             RPAD(NVL(NF.CDFISCOPERNF,'0'), 6, 0),
                             LPAD(NVL(NRREGCR14,0), 4, 0),
                             NF.CDFISCOPERNF
                     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_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 N.NRSEQUMAPAPU,
                                      N.CDESTAEXPO,
                                      (LPAD(N.CDFISCOPERNF,4,0)||NVL(N.CDESTAEXPO,'00')||'0000000') AS CDAUXILIAR,
                                      ('14'|| /* Codigo do Registro */
                                      NVL(N.CDESTAEXPO,'00')|| /* Unidade de Federacao */
                                      LTRIM(REPLACE(TO_CHAR(ROUND(SUM(DECODE(IDENTRSAIDOP,'E',VRCONTABILNF_1,DECODE(IDCONSVRBAPUIMP,'N',VRCONTABILNF_1,VRCONTABILNF_1_JLA))),2), '0000000000000.00'), '.', ''))|| /* Valor Contabil de Contribuinte */
                                      LTRIM(REPLACE(TO_CHAR(ROUND(SUM(DECODE(IDENTRSAIDOP,'E',VRBASECALCNF_1,DECODE(IDCONSVRBAPUIMP,'N',VRBASECALCNF_1,VRBASECALCNF_1_JLA))),2), '0000000000000.00'), '.', ''))|| /* Base de Calculo de Contribuinte */
                                      LTRIM(REPLACE(TO_CHAR(ROUND(SUM(DECODE(IDENTRSAIDOP,'E',VRCONTABILNF_2,DECODE(IDCONSVRBAPUIMP,'N',VRCONTABILNF_2,VRCONTABILNF_2_JLA))),2), '0000000000000.00'), '.', ''))|| /* Valor Contabil de Nao Contribuinte  */
                                      LTRIM(REPLACE(TO_CHAR(ROUND(SUM(DECODE(IDENTRSAIDOP,'E',VRBASECALCNF_2,DECODE(IDCONSVRBAPUIMP,'N',VRBASECALCNF_2,VRBASECALCNF_2_JLA))),2), '0000000000000.00'), '.', ''))|| /* Base de Calculo de Nao Contribuinte */
                                      LTRIM(REPLACE(TO_CHAR(ROUND(SUM(DECODE(IDENTRSAIDOP,'E',VRIMPOSINTER,DECODE(IDCONSVRBAPUIMP,'N',VRIMPOSINTER,VRIMPOSINTER_JLA))),2), '0000000000000.00'), '.', ''))|| /* Imposto Creditado ou Debitado */
                                      LTRIM(REPLACE(TO_CHAR(ROUND(SUM(VROUTROPER),2), '0000000000000.00'), '.', ''))|| /* Outras operacoes */
                                      '000000000000000'|| /* ICMS Cobrado por Substituicao Tributaria */
                                      '000000000000000'|| /* Petroleo e Energia quando ICMS cobrado por Substituicao Tributaria */
                                      '000000000000000'|| /* Outros Produtos */
                                      DECODE(N.IDMUNIALC,'S','1','0')|| /* Indica se ha alguma operacao Beneficiada por isencao de ICMS (ZFM/ALC) */
                                      LPAD(NVL(T.NRREGCR18,0),4,0) /* Quantidade de registros CR18 */ ) AS DSARQUMAG,
                                      N.SGESTACFOP
                               FROM (SELECT MP.NRSEQUMAPAPU,
                                            IP.CDFISCOPERNF,
                                            IP.CDFILIAL,
                                            IP.NRLANCTONF,
                                            ROUND(SUM(IP.VRCONTABILNF),2) AS VRCONTABILNF_1,
                                            ROUND(SUM(IP.VRCONTABILNF),2) AS VRCONTABILNF_1_JLA,
                                            ROUND(SUM(IP.VRBASECALCNF),2) AS VRBASECALCNF_1,
                                            ROUND(SUM(IP.VRBASECALCNF),2) AS VRBASECALCNF_1_JLA,
                                            0 AS VRCONTABILNF_2,
                                            0 AS VRCONTABILNF_2_JLA,
                                            0 AS VRBASECALCNF_2,
                                            0 AS VRBASECALCNF_2_JLA,
                                            0 AS VROUTROPER,
                                            ROUND(SUM(VRIMPOSTONF),2) AS VRIMPOSINTER,
                                            ROUND(SUM(VRIMPOSTONF),2) AS VRIMPOSINTER_JLA,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C') AS SGESTACFOP,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E') AS CDESTAEXPO,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M') AS IDMUNIALC
                                     FROM  IMPOSTONF IP, MAPAAPURAC MP
                                     WHERE MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND
                                           MP.CDEMPRESA    = P_CDEMPRESA    AND
                                           MP.CDINSCESTA   = P_CDINSCESTA   AND
                                           MP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                           IP.CDFISCOPERNF = C_REGCFOP.CDFISCOPERNF AND
                                           IP.CDFISCOPERNF NOT IN ('6108', '6109', '6258', '6307') AND
                                           TO_CHAR(MP.DTINIPERIODO, 'YYYYMM') BETWEEN TO_CHAR(P_DTINICIAL, 'YYYYMM') AND TO_CHAR(P_DTFINAL, 'YYYYMM') AND
                                           IP.CDFISCALNF = '1'                  AND
                                           IP.CDIMPOSTO  = P_CDIMPOSTO          AND
                                           IP.VRCONTABILNF >= IP.VRBASECALCNF AND
                                           (NVL(IP.IDIMPOSTONF,'1') <> '3')
                                     GROUP BY MP.NRSEQUMAPAPU, IP.CDFISCOPERNF, IP.CDFILIAL, IP.NRLANCTONF,
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C'),
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E'),
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M')
                                     UNION ALL
                                     SELECT MP.NRSEQUMAPAPU,
                                            IP.CDFISCOPERNF,
                                            IP.CDFILIAL,
                                            IP.NRLANCTONF,
                                            0 AS VRCONTABILNF_1,
                                            0 AS VRCONTABILNF_1_JLA,
                                            0 AS VRBASECALCNF_1,
                                            0 AS VRBASECALCNF_1_JLA,
                                            ROUND(SUM(IP.VRCONTABILNF),2) AS VRCONTABILNF_2,
                                            ROUND(SUM(IP.VRCONTABILNF),2) AS VRCONTABILNF_2_JLA,
                                            ROUND(SUM(IP.VRBASECALCNF),2) AS VRBASECALCNF_2,
                                            ROUND(SUM(IP.VRBASECALCNF),2) AS VRBASECALCNF_2_JLA,
                                            0 AS VROUTROPER,
                                            ROUND(SUM(VRIMPOSTONF),2) AS VRIMPOSINTER,
                                            ROUND(SUM(VRIMPOSTONF),2) AS VRIMPOSINTER_JLA,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C') AS SGESTACFOP,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E') AS CDESTAEXPO,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M') AS IDMUNIALC
                                     FROM  IMPOSTONF IP, MAPAAPURAC MP
                                     WHERE MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND
                                           MP.CDEMPRESA    = P_CDEMPRESA    AND
                                           MP.CDINSCESTA   = P_CDINSCESTA   AND
                                           MP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                           IP.CDFISCOPERNF = C_REGCFOP.CDFISCOPERNF AND
                                           IP.CDFISCOPERNF IN ('6108', '6109', '6258', '6307') AND
                                           TO_CHAR(MP.DTINIPERIODO, 'YYYYMM') BETWEEN TO_CHAR(P_DTINICIAL, 'YYYYMM') AND TO_CHAR(P_DTFINAL, 'YYYYMM') AND
                                           IP.CDFISCALNF   = '1'             AND
                                           IP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                           IP.VRCONTABILNF >= IP.VRBASECALCNF AND
                                           (NVL(IP.IDIMPOSTONF,'1') <> '3')
                                     GROUP BY MP.NRSEQUMAPAPU, IP.CDFISCOPERNF, IP.CDFILIAL, IP.NRLANCTONF,
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C'),
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E'),
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M')
                                     UNION ALL
                                     SELECT MP.NRSEQUMAPAPU,
                                            IP.CDFISCOPERNF,
                                            IP.CDFILIAL,
                                            IP.NRLANCTONF,
                                            ROUND(SUM(IP.VRCONTABILNF),2) AS VRCONTABILNF_1,
                                            ROUND(SUM(IP.VRBASECALCNF),2) AS VRCONTABILNF_1_JLA,
                                            ROUND(SUM(IP.VRBASECALCNF),2) AS VRBASECALCNF_1,
                                            0 AS VRBASECALCNF_1_JLA,
                                            0 AS VRCONTABILNF_2,
                                            0 AS VRCONTABILNF_2_JLA,
                                            0 AS VRBASECALCNF_2,
                                            0 AS VRBASECALCNF_2_JLA,
                                            0 AS VROUTROPER,
                                            0 AS VRIMPOSINTER,
                                            0 AS VRIMPOSINTER_JLA,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C') AS SGESTACFOP,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E') AS CDESTAEXPO,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M') AS IDMUNIALC
                                     FROM  IMPOSTONF IP, MAPAAPURAC MP
                                     WHERE MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND
                                           MP.CDEMPRESA    = P_CDEMPRESA    AND
                                           MP.CDINSCESTA   = P_CDINSCESTA   AND
                                           MP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                           IP.CDFISCOPERNF = C_REGCFOP.CDFISCOPERNF AND
                                           IP.CDFISCOPERNF NOT IN ('6108', '6109', '6258', '6307') AND
                                           TO_CHAR(MP.DTINIPERIODO, 'YYYYMM') BETWEEN TO_CHAR(P_DTINICIAL, 'YYYYMM') AND TO_CHAR(P_DTFINAL, 'YYYYMM') AND
                                           IP.CDFISCALNF = '1'                  AND
                                           IP.CDIMPOSTO  = P_CDIMPOSTO          AND
                                           IP.VRCONTABILNF < IP.VRBASECALCNF AND
                                           (NVL(IP.IDIMPOSTONF,'1') <> '3')
                                     GROUP BY MP.NRSEQUMAPAPU, IP.CDFISCOPERNF, IP.CDFILIAL, IP.NRLANCTONF,
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C'),
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E'),
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M')
                                     UNION ALL
                                     SELECT MP.NRSEQUMAPAPU,
                                            IP.CDFISCOPERNF,
                                            IP.CDFILIAL,
                                            IP.NRLANCTONF,
                                            0 AS VRCONTABILNF_1,
                                            0 AS VRCONTABILNF_1_JLA,
                                            0 AS VRBASECALCNF_1,
                                            0 AS VRBASECALCNF_1_JLA,
                                            ROUND(SUM(IP.VRCONTABILNF),2) AS VRCONTABILNF_2,
                                            ROUND(SUM(IP.VRBASECALCNF),2) AS VRCONTABILNF_2_JLA,
                                            ROUND(SUM(IP.VRBASECALCNF),2) AS VRBASECALCNF_2,
                                            0 AS VRBASECALCNF_2_JLA,
                                            0 AS VROUTROPER,
                                            0 AS VRIMPOSINTER,
                                            0 AS VRIMPOSINTER_JLA,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C') AS SGESTACFOP,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E') AS CDESTAEXPO,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M') AS IDMUNIALC
                                     FROM  IMPOSTONF IP, MAPAAPURAC MP
                                     WHERE MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND
                                           MP.CDEMPRESA    = P_CDEMPRESA    AND
                                           MP.CDINSCESTA   = P_CDINSCESTA   AND
                                           MP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                           IP.CDFISCOPERNF = C_REGCFOP.CDFISCOPERNF AND
                                           IP.CDFISCOPERNF IN ('6108', '6109', '6258', '6307') AND
                                           TO_CHAR(MP.DTINIPERIODO, 'YYYYMM') BETWEEN TO_CHAR(P_DTINICIAL, 'YYYYMM') AND TO_CHAR(P_DTFINAL, 'YYYYMM') AND
                                           IP.CDFISCALNF   = '1'             AND
                                           IP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                           IP.VRCONTABILNF < IP.VRBASECALCNF AND
                                           (NVL(IP.IDIMPOSTONF,'1') <> '3')
                                     GROUP BY MP.NRSEQUMAPAPU, IP.CDFISCOPERNF, IP.CDFILIAL, IP.NRLANCTONF,
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C'),
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E'),
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M')
                                     UNION ALL
                                     SELECT MP.NRSEQUMAPAPU,
                                            IP.CDFISCOPERNF,
                                            IP.CDFILIAL,
                                            IP.NRLANCTONF,
                                            ROUND(SUM(IP.VRCONTABILNF),2) AS VRCONTABILNF_1,
                                            ROUND(SUM(IP.VRCONTABILNF),2) AS VRCONTABILNF_1_JLA,
                                            ROUND(SUM(IP.VRBASECALCNF),2) AS VRBASECALCNF_1,
                                            ROUND(SUM(IP.VRBASECALCNF),2) AS VRBASECALCNF_1_JLA,
                                            0 AS VRCONTABILNF_2,
                                            0 AS VRCONTABILNF_2_JLA,
                                            0 AS VRBASECALCNF_2,
                                            0 AS VRBASECALCNF_2_JLA,
                                            0 AS VROUTROPER,
                                            0 AS VRIMPOSINTER,
                                            0 AS VRIMPOSINTER_JLA,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C') AS SGESTACFOP,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E') AS CDESTAEXPO,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M') AS IDMUNIALC
                                     FROM  IMPOSTONF IP, MAPAAPURAC MP
                                     WHERE MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND
                                           MP.CDEMPRESA    = P_CDEMPRESA    AND
                                           MP.CDINSCESTA   = P_CDINSCESTA   AND
                                           MP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                           IP.CDFISCOPERNF = C_REGCFOP.CDFISCOPERNF AND
                                           TO_CHAR(MP.DTINIPERIODO, 'YYYYMM') BETWEEN TO_CHAR(P_DTINICIAL, 'YYYYMM') AND TO_CHAR(P_DTFINAL, 'YYYYMM') AND
                                           IP.CDFISCALNF   = '2'             AND
                                           IP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                           IP.VRCONTABILNF >= IP.VRBASECALCNF AND
                                           (NVL(IP.IDIMPOSTONF,'1') <> '3')
                                     GROUP BY MP.NRSEQUMAPAPU, IP.CDFISCOPERNF, IP.CDFILIAL, IP.NRLANCTONF,
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C'),
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E'),
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M')
                                     UNION ALL
                                     SELECT MP.NRSEQUMAPAPU,
                                            IP.CDFISCOPERNF,
                                            IP.CDFILIAL,
                                            IP.NRLANCTONF,
                                            ROUND(SUM(IP.VRCONTABILNF),2) AS VRCONTABILNF_1,
                                            ROUND(SUM(IP.VRBASECALCNF),2) AS VRCONTABILNF_1_JLA,
                                            ROUND(SUM(IP.VRBASECALCNF),2) AS VRBASECALCNF_1,
                                            0 AS VRBASECALCNF_1_JLA,
                                            0 AS VRCONTABILNF_2,
                                            0 AS VRCONTABILNF_2_JLA,
                                            0 AS VRBASECALCNF_2,
                                            0 AS VRBASECALCNF_2_JLA,
                                            0 AS VROUTROPER,
                                            0 AS VRIMPOSINTER,
                                            0 AS VRIMPOSINTER_JLA,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C') AS SGESTACFOP,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E') AS CDESTAEXPO,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M') AS IDMUNIALC
                                     FROM  IMPOSTONF IP, MAPAAPURAC MP
                                     WHERE MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND
                                           MP.CDEMPRESA    = P_CDEMPRESA    AND
                                           MP.CDINSCESTA   = P_CDINSCESTA   AND
                                           MP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                           IP.CDFISCOPERNF = C_REGCFOP.CDFISCOPERNF AND
                                           TO_CHAR(MP.DTINIPERIODO, 'YYYYMM') BETWEEN TO_CHAR(P_DTINICIAL, 'YYYYMM') AND TO_CHAR(P_DTFINAL, 'YYYYMM') AND
                                           IP.CDFISCALNF   = '2'             AND
                                           IP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                           IP.VRCONTABILNF < IP.VRBASECALCNF AND
                                           (NVL(IP.IDIMPOSTONF,'1') <> '3')
                                     GROUP BY MP.NRSEQUMAPAPU, IP.CDFISCOPERNF, IP.CDFILIAL, IP.NRLANCTONF,
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C'),
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E'),
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M')
                                     UNION ALL
                                     SELECT MP.NRSEQUMAPAPU,
                                            IP.CDFISCOPERNF,
                                            IP.CDFILIAL,
                                            IP.NRLANCTONF,
                                            ROUND(SUM(IP.VRCONTABILNF),2) AS VRCONTABILNF_1,
                                            ROUND(SUM(IP.VRCONTABILNF),2) AS VRCONTABILNF_1_JLA,
                                            ROUND(SUM(IP.VRBASECALCNF),2) AS VRBASECALCNF_1,
                                            ROUND(SUM(IP.VRBASECALCNF),2) AS VRBASECALCNF_1_JLA,
                                            0 AS VRCONTABILNF_2,
                                            0 AS VRCONTABILNF_2_JLA,
                                            0 AS VRBASECALCNF_2,
                                            0 AS VRBASECALCNF_2_JLA,
                                            ROUND(SUM(IP.VRCONTABILNF),2) AS VROUTROPER,
                                            0 AS VRIMPOSINTER,
                                            0 AS VRIMPOSINTER_JLA,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C') AS SGESTACFOP,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E') AS CDESTAEXPO,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M') AS IDMUNIALC
                                     FROM  IMPOSTONF IP, MAPAAPURAC MP
                                     WHERE MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND
                                           MP.CDEMPRESA    = P_CDEMPRESA    AND
                                           MP.CDINSCESTA   = P_CDINSCESTA   AND
                                           MP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                           IP.CDFISCOPERNF = C_REGCFOP.CDFISCOPERNF AND
                                           SUBSTR(IP.CDFISCOPERNF,1,1) = '2' AND
                                           TO_CHAR(MP.DTINIPERIODO, 'YYYYMM') BETWEEN TO_CHAR(P_DTINICIAL, 'YYYYMM') AND TO_CHAR(P_DTFINAL, 'YYYYMM') AND
                                           IP.CDFISCALNF   = '3'             AND
                                           IP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                           (NVL(IP.IDIMPOSTONF,'1') <> '3')
                                     GROUP BY MP.NRSEQUMAPAPU, IP.CDFISCOPERNF, IP.CDFILIAL, IP.NRLANCTONF,
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C'),
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E'),
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M')
                                     UNION ALL
                                     SELECT MP.NRSEQUMAPAPU,
                                            IP.CDFISCOPERNF,
                                            IP.CDFILIAL,
                                            IP.NRLANCTONF,
                                            ROUND(SUM(IP.VRCONTABILNF),2) AS VRCONTABILNF_1,
                                            ROUND(SUM(IP.VRCONTABILNF),2) AS VRCONTABILNF_1_JLA,
                                            ROUND(SUM(IP.VRBASECALCNF),2) AS VRBASECALCNF_1,
                                            ROUND(SUM(IP.VRBASECALCNF),2) AS VRBASECALCNF_1_JLA,
                                            0 AS VRCONTABILNF_2,
                                            0 AS VRCONTABILNF_2_JLA,
                                            0 AS VRBASECALCNF_2,
                                            0 AS VRBASECALCNF_2_JLA,
                                            ROUND(SUM(IP.VRCONTABILNF),2) AS VROUTROPER,
                                            0 AS VRIMPOSINTER,
                                            0 AS VRIMPOSINTER_JLA,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C') AS SGESTACFOP,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E') AS CDESTAEXPO,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M') AS IDMUNIALC
                                     FROM  IMPOSTONF IP, MAPAAPURAC MP
                                     WHERE MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND
                                           MP.CDEMPRESA    = P_CDEMPRESA    AND
                                           MP.CDINSCESTA   = P_CDINSCESTA   AND
                                           MP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                           IP.CDFISCOPERNF = C_REGCFOP.CDFISCOPERNF AND
                                           SUBSTR(IP.CDFISCOPERNF,1,1) = '6' AND
                                           TO_CHAR(MP.DTINIPERIODO, 'YYYYMM') BETWEEN TO_CHAR(P_DTINICIAL, 'YYYYMM') AND TO_CHAR(P_DTFINAL, 'YYYYMM') AND
                                           IP.CDFISCALNF   = '3'             AND
                                           IP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                           (NVL(IP.IDIMPOSTONF,'1') <> '3')
                                     GROUP BY MP.NRSEQUMAPAPU, IP.CDFISCOPERNF, IP.CDFILIAL, IP.NRLANCTONF,
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C'),
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E'),
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M')
                                     UNION ALL
                                     SELECT MP.NRSEQUMAPAPU,
                                            IP.CDFISCOPERNF,
                                            IP.CDFILIAL,
                                            IP.NRLANCTONF,
                                            ROUND(SUM(VRIMPOSTONF),2) AS VRCONTABILNF_1,
                                            ROUND(SUM(VRIMPOSTONF),2) AS VRCONTABILNF_1_JLA,
                                            0 AS VRBASECALCNF_1,
                                            0 AS VRBASECALCNF_1_JLA,
                                            0 AS VRCONTABILNF_2,
                                            0 AS VRCONTABILNF_2_JLA,
                                            0 AS VRBASECALCNF_2,
                                            0 AS VRBASECALCNF_2_JLA,
                                            ROUND(SUM(VRIMPOSTONF),2) AS VROUTROPER,
                                            0 AS VRIMPOSINTER,
                                            0 AS VRIMPOSINTER_JLA,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C') AS SGESTACFOP,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E') AS CDESTAEXPO,
                                            ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M') AS IDMUNIALC
                                     FROM  IMPOSTONF IP, MAPAAPURAC MP
                                     WHERE MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND
                                           MP.CDEMPRESA    = P_CDEMPRESA    AND
                                           MP.CDINSCESTA   = P_CDINSCESTA   AND
                                           MP.CDIMPOSTO    = P_CDIMPOSTO    AND
                                           IP.CDFISCOPERNF = C_REGCFOP.CDFISCOPERNF AND
                                           TO_CHAR(MP.DTINIPERIODO, 'YYYYMM') BETWEEN TO_CHAR(P_DTINICIAL, 'YYYYMM') AND TO_CHAR(P_DTFINAL, 'YYYYMM') AND
                                           IP.CDIMPOSTO    <> P_CDIMPOSTO   AND
                                           (NVL(IDIMPOSTONF,'1') <> '3')
                                     GROUP BY MP.NRSEQUMAPAPU, IP.CDFISCOPERNF, IP.CDFILIAL, IP.NRLANCTONF,
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'C'),
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'E'),
                                              ORIGEM_NOTA(IP.CDFILIAL, IP.NRLANCTONF, 'M') ) N,

                                     (SELECT NRSEQUMAPAPU,
                                             MAX(CDFISCOPERNF) AS CDFISCOPERNF,
                                             COUNT(ORIGEM_NOTA(I.CDFILIAL, I.NRLANCTONF, 'E')) AS NRREGCR18,
                                             ORIGEM_NOTA(I.CDFILIAL, I.NRLANCTONF, 'E') AS CDESTAEXPO
                                      FROM IMPOSTONF I
                                      WHERE CDFISCALNF <> '1'                                 AND
                                            CDIMPOSTO    = P_CDIMPOSTO                        AND
                                            NRSEQUMAPAPU  IS NOT NULL                         AND
                                            I.CDFISCOPERNF = C_REGCFOP.CDFISCOPERNF           AND
                                            SUBSTR(CDFISCOPERNF,1,1) = '6'                    AND
                                            ORIGEM_NOTA(I.CDFILIAL, I.NRLANCTONF, 'M') = 'S'
                                      GROUP BY NRSEQUMAPAPU,
                                               ORIGEM_NOTA(I.CDFILIAL, I.NRLANCTONF, 'E') ) T,
                                     NOTAFISCAL NF, IMPOSTO IM
                               WHERE IM.CDIMPOSTO   = P_CDIMPOSTO       AND
                                     NF.CDFILIAL    = N.CDFILIAL        AND
                                     NF.NRLANCTONF  = N.NRLANCTONF      AND
                                     N.NRSEQUMAPAPU = T.NRSEQUMAPAPU(+) AND
                                     N.CDFISCOPERNF = T.CDFISCOPERNF(+) AND
                                     N.CDESTAEXPO   = T.CDESTAEXPO(+)   AND
                                     N.CDFISCOPERNF IS NOT NULL
                               GROUP BY N.NRSEQUMAPAPU, N.CDESTAEXPO, N.SGESTACFOP,
                                        (LPAD(N.CDFISCOPERNF,4,0)||NVL(N.CDESTAEXPO,'00')||'0000000'),
                                        NVL(N.CDESTAEXPO,'00'),
                                        DECODE(N.IDMUNIALC,'S','1','0'),
                                        LPAD(NVL(T.NRREGCR18,0),4,0)
                               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_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(N.NRNOTAFISC, 2, 6)|| /* Numero da Nota Fiscal */
                                        TO_CHAR(N.DTEMISSAO, 'YYYYMMDD')|| /* Data de Emissao */
                                        LTRIM(REPLACE(TO_CHAR(N.VRNOTAFISC, '0000000000000.00'), '.', ''))|| /* Valor da Nota Fiscal */
                                        RPAD(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
                                      FROM IMPOSTONF
                                      WHERE CDFISCALNF <> '1'           AND
                                            CDIMPOSTO  = P_CDIMPOSTO    AND
                                            NRSEQUMAPAPU IS NOT NULL    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, 'E') = C_REGDTEINTER.CDESTAEXPO     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(MAX(IT.DSFLEGALOCOR),' '), 100, ' ')|| /* Fundamentacao legal do Subitem */
                                 RPAD(NVL(MAX(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
                         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;
   END LOOP;
   IF P_GERADIPAM = 'S' THEN
      FOR C_DIPAM_B IN(SELECT '3022'||
                           LPAD(NVL(SUBSTR(H.CDMUNIEXPO,2,5),0),5,0)||
                           LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(NVL(L.RESULTADO,0),2),'9999999999990.00')),'.',''),15,0) AS REGISTRO
                      FROM MUNICIPIO H ,
                          (SELECT SUM(CASE WHEN (P.NOTAS_DE_SAIDA - P.NOTAS_DE_ENTRADA) < 0 THEN 0 ELSE (P.NOTAS_DE_SAIDA - P.NOTAS_DE_ENTRADA) END) AS RESULTADO,
                                  P.CDMUNICIPIO
                            FROM (SELECT SUM(DECODE(T.IDENTRSAIDOP, 'S', T.VRCONTABILNF,0)) AS NOTAS_DE_SAIDA,
                                         SUM(DECODE(T.IDENTRSAIDOP, 'E', T.VRCONTABILNF,0)) AS NOTAS_DE_ENTRADA,
                                         T.CDMUNICIPIO
                                    FROM (SELECT N.IDENTRSAIDOP,
                                                 DECODE(N.IDENTRSAIDOP,'E',I.VRCONTABILNF,DECODE(IM.IDCONSVRBAPUIMP,'N',I.VRCONTABILNF,(CASE WHEN I.VRCONTABILNF>=I.VRBASECALCNF THEN I.VRCONTABILNF ELSE I.VRBASECALCNF END))) AS VRCONTABILNF,
                                                 E.CDMUNICIPIO
                                            FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM, FORNECEDOR F, ENDEFORN E
                                           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 IM.CDIMPOSTO = I.CDIMPOSTO
                                             AND (SUBSTR(I.CDFISCOPERNF,1,1) = '1' OR SUBSTR(I.CDFISCOPERNF,1,1) = '5')
                                             AND (IDIMPOSTONF <> '3' OR IDIMPOSTONF IS NULL)
                                             AND IDORIGDESTNF = 'F'
                                             AND N.CDFORNECED = F.CDFORNECED
                                             AND F.CDFORNECED = E.CDFORNECED
                                             AND E.IDTPENDEFORN = 'P'
                                             AND E.SGESTADO = 'SP'
                                          UNION ALL
                                          SELECT N.IDENTRSAIDOP,
                                                 DECODE(N.IDENTRSAIDOP,'E',I.VRCONTABILNF,DECODE(IM.IDCONSVRBAPUIMP,'N',I.VRCONTABILNF,(CASE WHEN I.VRCONTABILNF>=I.VRBASECALCNF THEN I.VRCONTABILNF ELSE I.VRBASECALCNF END))) AS VRCONTABILNF,
                                                 E.CDMUNICIPIO
                                            FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM, CLIENTE C, ENDECLIE E
                                           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 IM.CDIMPOSTO = I.CDIMPOSTO
                                             AND (SUBSTR(I.CDFISCOPERNF,1,1) = '1' OR SUBSTR(I.CDFISCOPERNF,1,1) = '5')
                                             AND (IDIMPOSTONF <> '3' OR IDIMPOSTONF IS NULL)
                                             AND IDORIGDESTNF = 'C'
                                             AND N.CDCLIENTE = C.CDCLIENTE
                                             AND C.CDCLIENTE = E.CDCLIENTE
                                             AND E.IDTPENDECLIE = 'P'
                                             AND E.SGESTADO = 'SP'
                                          UNION ALL
                                          SELECT N.IDENTRSAIDOP,
                                                 DECODE(IM.IDCONSVRBAPUIMP,'N',I.VRCONTABILNF,(CASE WHEN I.VRCONTABILNF>=I.VRBASECALCNF THEN I.VRCONTABILNF ELSE I.VRBASECALCNF END)) AS VRCONTABILNF,
                                                 E.CDMUNICIPIO
                                            FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM, FILIAL F, ENDEFILI E
                                           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 IM.CDIMPOSTO = I.CDIMPOSTO
                                             AND SUBSTR(I.CDFISCOPERNF,1,1) = '5'
                                             AND (IDIMPOSTONF <> '3' OR IDIMPOSTONF IS NULL)
                                             AND IDORIGDESTNF = 'L'
                                             AND N.IDENTRSAIDOP = 'S'
                                             AND N.CDFILIRECE = F.CDFILIAL
                                             AND F.CDFILIAL  = E.CDFILIAL
                                             AND E.IDTPENDEFILI = 'P'
                                             AND E.SGESTADO = 'SP'
                                          UNION ALL
                                          SELECT N.IDENTRSAIDOP,
                                                 I.VRCONTABILNF,
                                                 E.CDMUNICIPIO
                                            FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM, FILIAL F, ENDEFILI E
                                           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 IM.CDIMPOSTO = I.CDIMPOSTO
                                             AND SUBSTR(I.CDFISCOPERNF,1,1) = '1'
                                             AND (IDIMPOSTONF <> '3' OR IDIMPOSTONF IS NULL)
                                             AND IDORIGDESTNF = 'L'
                                             AND N.IDENTRSAIDOP = 'E'
                                             AND N.CDFILIEMIT = F.CDFILIAL
                                             AND F.CDFILIAL  = E.CDFILIAL
                                             AND E.IDTPENDEFILI = 'P'
                                             AND E.SGESTADO = 'SP')T
                                   GROUP BY T.CDMUNICIPIO)P
                            GROUP BY P.CDMUNICIPIO)L
                     WHERE H.CDPAIS = V_PAIS
                       AND H.SGESTADO = 'SP'
                       AND L.CDMUNICIPIO = H.CDMUNICIPIO
                     ORDER BY H.CDMUNIEXPO)LOOP
         IF (SUBSTR(C_DIPAM_B.REGISTRO,10,24) <> '000000000000000') THEN
            V_DIPAM := V_DIPAM + 1;
            INSERT INTO ARQMAGNETICO
               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
               ('08'||LPAD(V_DIPAM,8,'0'), C_DIPAM_B.REGISTRO, P_NRSEQARQMAG);
         END IF;
      END LOOP;
   END IF;
   FOR C_REGISTRO11 IN (SELECT MP.NRSEQUMAPAPU,
                               '000000000000' AS CDAUXILIAR,
                               ('05'|| /* Codigo do Registro */
                                 RPAD(REPLACE(REPLACE(REPLACE(IE.CDINSCESTA,'.',''),',',''),'-',''),12,' ')|| /* Inscricao Estadual */
                                 RPAD(SUBSTR(IE.NRINSJURIEST,1,14),14,' ')|| /* Inscricao Juridica */
--                                 LPAD(NVL(E.CDCNAE,' '), 7, ' ')|| /* Classificacao Atividade Econimica */
                                 '0000000'||
                                 '01'|| /* Regime Tributario - RPA (Regime Periodico de Apuracao) */
                                 TO_CHAR(MP.DTINIPERIODO, 'YYYYMM')|| /* Data Referencia */
                                 '000000'|| /* Referencia Inicial - Se Regime = 01 (RPA) - RefInicial =  000000 (zeros) */
                                 '01'|| /* Tipo da GIA - Normal */
                                 '1'|| /* Indica se houve movimento - Sim */
                                 REPLACE(REPLACE(P_IDTRANSMITIDO,'S','1'),'N', '0')|| /* Documento Fiscal  ja foi transmitido */
                                 LTRIM(REPLACE(TO_CHAR(MR.VRSLDCRANTE,'0000000000000.00'),'.',''))|| /* Saldo Credor do Periodo Anterior */
                                 '000000000000000'|| /* Saldo Credor do Periodo Anterior para Substituicao Tributaria */
                                 '26269316000177'|| /* Identificacao do fabricante */
                                 '0'|| /* Pre-Formatado */
                                 '000000000000000'|| /* ICMS Fixado para o periodo - zero para Reg. Trib = '01' */
                                 '00000000000000000000000000000000' ||  /* Chave Interna */
                                 LPAD(NVL(A.NRREGCR10,0), 4, 0)|| /* Quantidade de registros CR10 */
                                 LPAD(NVL(I.NRREGCR20,0), 4, 0)|| /* Quantidade de registros CR20 */
                                 LPAD(NVL(V_DIPAM,0), 4, 0) ||  /* Quantidade de registros CR30 */
                                 '0000'  /* Quantidade de registros CR31 */) AS DSARQUMAG
                        FROM INSCRESTAD IE, MAPAAPURAC MP, MAPARECUP MR, EMPRESA E,
                             (SELECT NRSEQUMAPAPU, COUNT(*) AS NRREGCR10
                              FROM (SELECT DISTINCT I.NRSEQUMAPAPU, I.CDFISCOPERNF
                                      FROM IMPOSTONF I
                                     WHERE I.NRSEQUMAPAPU = V_NRSEQUMAPAPU
                                       AND I.CDFISCOPERNF IS NOT NULL
                                       AND I.CDIMPOSTO = P_CDIMPOSTO
                                       AND NVL(I.IDIMPOSTONF,'1') <> '3')
                              GROUP BY NRSEQUMAPAPU) A,
                             (SELECT NRSEQUMAPAPU, COUNT(*) AS NRREGCR20
                              FROM (SELECT DISTINCT NRSEQUMAPAPU, CDITEMOCOR
                                    FROM ITMAPARECUP
                                    WHERE CDITEMOCOR IS NOT NULL )
                              GROUP BY NRSEQUMAPAPU) I
                        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
                              MP.NRSEQUMAPAPU = A.NRSEQUMAPAPU(+) AND
                              MP.NRSEQUMAPAPU = I.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
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        (LPAD('01',10,'0'), C_REGISTRO11.DSARQUMAG, P_NRSEQARQMAG);
   END LOOP;
END;
/