CREATE OR REPLACE PROCEDURE ARQUIVO_GIA_RJ(
   P_CDEMPRESA      IN EMPRESA.CDEMPRESA%TYPE,
   P_CDIMPOSTO      IN IMPOSTO.CDIMPOSTO%TYPE,
   P_CDINSCESTA     IN INSCRESTAD.CDINSCESTA%TYPE,
   P_DTINIPERIODO   IN MAPAAPURAC.DTINIPERIODO%TYPE,
   P_DTFIMPERIODO   IN MAPAAPURAC.DTINIPERIODO%TYPE,
   P_MOVOPPR        IN NOTAFISCAL.IDDOCFISCAL%TYPE,
   P_MOVST          IN NOTAFISCAL.IDDOCFISCAL%TYPE,
   P_MOVICMS        IN NOTAFISCAL.IDDOCFISCAL%TYPE,
   P_MOVALC         IN NOTAFISCAL.IDDOCFISCAL%TYPE,
   P_VERSAO         IN MAPAAPURAC.NRSEQUMAPAPU%TYPE,
   P_RETIFICADORA   IN NOTAFISCAL.IDDOCFISCAL%TYPE,
   P_NRSEQUENCIAL   IN NOTAFISCAL.NRLANCTONF%TYPE)
AS
   V_CABECALHO   VARCHAR(1000);
   V_RODAPE      VARCHAR(1000);
   V_SEQLINHA    NUMBER;
   V_VRTOTREG110 NUMBER;
   V_VRTOTREG120 NUMBER;
   V_VRTOTREG130 NUMBER;
   V_VRTOTREG140 NUMBER;
   V_VRTOTREG150 NUMBER;
   V_VRTOTREG160 NUMBER;
   V_VRTOTREG170 NUMBER;
   V_VRTOTREG180 NUMBER;
   V_VRTOTREG200 NUMBER;
   V_VRTOTREG210 NUMBER;
   V_VRTOTREG220 NUMBER;
   V_VRTOTAL     NUMBER;

BEGIN
   /** CABECALHO **/
   V_SEQLINHA := 1;
   BEGIN
      SELECT '0000'                             ||
             TO_CHAR(SYSDATE, 'YYYYMMDDHHMISS') ||
             'N'                                ||
             RPAD(P_VERSAO,10,' ')              ||
             LPAD(' ',816,' ')                  ||
             LPAD(V_SEQLINHA,5,0)
        INTO V_CABECALHO
        FROM DUAL;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
        V_CABECALHO := '';
   END;

   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
   VALUES
     (LPAD(V_SEQLINHA,10,0),V_CABECALHO,P_NRSEQUENCIAL);

   /** REGISTRO 0110 **/
   FOR C_REG0110 IN (SELECT ('0110'||
                             LPAD('1',15,'0')||
                             LPAD(NVL(P_CDINSCESTA,0),8,0)||
                             TO_CHAR(P_DTINIPERIODO, 'YYYY')||
                             TO_CHAR(P_DTINIPERIODO, 'MM')||
                             P_RETIFICADORA||
                             'M'||
                             RPAD(NVL(IE.NMREPRINSEST,' '),64,' ')||
                             RPAD(' ',4,' ')||
                             RPAD(SUBSTR(NVL(REPLACE(REPLACE(REPLACE(IE.NRTELEIEST,'(',''),')',''),'-',''),'          '),3,8),8,' ')  ||
                             RPAD(NVL(CO.NMCONTADOR,' '),64,' ')||
                             RPAD(NVL(CO.DSENDINTERN,' '),40,' ')||
                             RPAD(' ',4,' ')||
                             RPAD(SUBSTR(NVL(REPLACE(REPLACE(REPLACE(CO.NRTELCONTATO,'(',''),')',''),'-',''),'          '),3,8),8,' ')||
                             REPLACE(LTRIM(TO_CHAR(MR.VRSLDCRANTE,'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR((SELECT NVL(MR.VRSLDCRANTE,0) AS VRSLDCRANTE
                                                      FROM MAPAAPURAC MP, MAPARECUP MR
                                                     WHERE MP.CDIMPOSTO    = '80'
                                                       AND MP.CDEMPRESA    = P_CDEMPRESA
                                                       AND MP.CDINSCESTA   = P_CDINSCESTA
                                                       AND MP.DTINIPERIODO = P_DTINIPERIODO
                                                       AND MP.DTFIMPERIODO = P_DTFIMPERIODO
                                                       AND MR.NRSEQUMAPAPU = MP.NRSEQUMAPAPU),'0000000000000.00')),'.')||
                             RPAD(' ',255,' ')||
                             RPAD(' ',255,' ')||
                             REPLACE(LTRIM(TO_CHAR(0,'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(0,'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(0,'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(0,'0000000000000.00')),'.')||
                             P_MOVOPPR||
                             P_MOVST||
                             P_MOVICMS||
                             P_MOVALC||
                             RPAD(' ',14,' ')) AS DSARQUIVO, NVL(MR.VRSLDCRANTE,0) AS VRSLDCRANTE
                        FROM MAPAAPURAC MP, MAPARECUP MR, INSCRESTAD IE, CONTADOR CO
                       WHERE MP.CDIMPOSTO    = P_CDIMPOSTO
                         AND MP.CDEMPRESA    = P_CDEMPRESA
                         AND MP.CDINSCESTA   = P_CDINSCESTA
                         AND MP.DTINIPERIODO = P_DTINIPERIODO
                         AND MP.DTFIMPERIODO = P_DTFIMPERIODO
                         AND MR.NRSEQUMAPAPU = MP.NRSEQUMAPAPU
                         AND IE.CDEMPRESA    = MP.CDEMPRESA
                         AND IE.CDINSCESTA   = MP.CDINSCESTA
                         AND IE.CDCONTADOR2  = CO.CDCONTADOR2(+)
                     )LOOP

      V_VRTOTREG110 := NVL(V_VRTOTREG110,0) + C_REG0110.VRSLDCRANTE;
      V_SEQLINHA := V_SEQLINHA + 1;

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
      VALUES
        (LPAD(V_SEQLINHA,10,0),C_REG0110.DSARQUIVO||LPAD(V_SEQLINHA,5,0),P_NRSEQUENCIAL);

   END LOOP;

   /** REGISTRO 0120 **/
   FOR C_REG0120 IN (SELECT ('0120'||
                             LPAD('1',15,'0')||
                             RPAD(A.CDFISCOPERNF,4,' ')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(A.VRCONTABILNF,0)),'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(A.VRBASECALCNF,0)),'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(A.IMPOS,0)),'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(A.ISENT,0)),'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(A.OUTRA,0)),'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(B.BASE,0)),'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(B.IMPO,0)),'0000000000000.00')),'.')||
                             RPAD(' ',717,' ')) AS DSARQUIVO,
                             SUM(NVL(A.VRCONTABILNF,0)) AS VRCONTABILNF,
                             SUM(NVL(A.VRBASECALCNF,0)) AS VRBASECALCNF,
                             SUM(NVL(A.IMPOS,0)) AS IMPOS,
                             SUM(NVL(A.ISENT,0)) AS ISENT,
                             SUM(NVL(A.OUTRA,0)) AS OUTRA,
                             SUM(NVL(B.BASE,0)) AS BASE,
                             SUM(NVL(B.IMPO,0)) AS IMPO
                        FROM
                           (SELECT IP.CDFILIAL, IP.NRLANCTONF,
                                   IP.CDFISCOPERNF,
                                   ROUND(IP.VRCONTABILNF,2) AS VRCONTABILNF,
                                   ROUND(IP.VRBASECALCNF,2) AS VRBASECALCNF,
                                   ROUND(DECODE(IP.CDFISCALNF,'1',IP.VRIMPOSTONF+NVL(IP.VRADICFECP,0),0),2) AS IMPOS,
                                   ROUND(DECODE(IP.CDFISCALNF,'2',IP.VRCONTABILNF,0),2) AS ISENT,
                                   ROUND(DECODE(IP.CDFISCALNF,'3',IP.VRCONTABILNF,0),2) AS OUTRA
                              FROM MAPAAPURAC MP, IMPOSTONF IP, NOTAFISCAL NF, IMPOSTO IM
                             WHERE MP.CDIMPOSTO    = P_CDIMPOSTO
                               AND MP.CDEMPRESA    = P_CDEMPRESA
                               AND MP.CDINSCESTA   = P_CDINSCESTA
                               AND MP.DTINIPERIODO = P_DTINIPERIODO
                               AND MP.DTFIMPERIODO = P_DTFIMPERIODO
                               AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                               AND IM.CDIMPOSTO    = IP.CDIMPOSTO
                               AND MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU
                               AND IP.CDFILIAL     = NF.CDFILIAL
                               AND IP.NRLANCTONF   = NF.NRLANCTONF
                               AND NF.IDENTRSAIDOP = 'E')A,
                           (SELECT IP.CDFILIAL, IP.NRLANCTONF,
                                   ROUND(DECODE(IP.CDFISCOPERNF,'1',IP.VRBASECALCNF,DECODE(IP.CDFISCOPERNF,'1',IP.VRBASECALCNF,0)),2) AS BASE,
                                   ROUND(DECODE(IP.CDFISCOPERNF,'1',IP.VRIMPOSTONF+NVL(IP.VRADICFECP,0),DECODE(IP.CDFISCOPERNF,'1',IP.VRIMPOSTONF+NVL(IP.VRADICFECP,0),0)),2) AS IMPO
                              FROM MAPAAPURAC MP, IMPOSTONF IP, NOTAFISCAL NF, IMPOSTO IM
                             WHERE MP.CDIMPOSTO    = '80' /*Imposto de Substituição tributária*/
                               AND MP.CDEMPRESA    = P_CDEMPRESA
                               AND MP.CDINSCESTA   = P_CDINSCESTA
                               AND MP.DTINIPERIODO = P_DTINIPERIODO
                               AND MP.DTFIMPERIODO = P_DTFIMPERIODO
                               AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                               AND IM.CDIMPOSTO    = IP.CDIMPOSTO
                               AND MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU
                               AND IP.CDFILIAL     = NF.CDFILIAL
                               AND IP.NRLANCTONF   = NF.NRLANCTONF
                               AND NF.IDENTRSAIDOP = 'E')B
                       WHERE A.CDFILIAL = B.CDFILIAL(+)
                         AND A.NRLANCTONF = B.NRLANCTONF(+)
                       GROUP BY A.CDFISCOPERNF
                       ORDER BY A.CDFISCOPERNF
                     )LOOP

      V_VRTOTREG120 := NVL(V_VRTOTREG120,0) + C_REG0120.VRCONTABILNF+C_REG0120.VRBASECALCNF+C_REG0120.IMPOS+C_REG0120.ISENT+C_REG0120.OUTRA+C_REG0120.BASE+C_REG0120.IMPO;
      V_SEQLINHA := V_SEQLINHA + 1;

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
      VALUES
        (LPAD(V_SEQLINHA,10,0),C_REG0120.DSARQUIVO||LPAD(V_SEQLINHA,5,0),P_NRSEQUENCIAL);

   END LOOP;

   /** REGISTRO 0130 **/
   FOR C_REG0130 IN (SELECT ('0130'||
                             LPAD('1',15,'0')||
                             RPAD(A.CDFISCOPERNF,4,' ')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(A.VRCONTABILNF,0)),'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(A.VRBASECALCNF,0)),'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(A.IMPOS,0)),'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(A.ISENT,0)),'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(A.OUTRA,0)),'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(B.BASE,0)),'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(B.IMPO,0)),'0000000000000.00')),'.')||
                             RPAD(' ',717,' ')) AS DSARQUIVO,
                             SUM(NVL(A.VRCONTABILNF,0)) AS VRCONTABILNF,
                             SUM(NVL(A.VRBASECALCNF,0)) AS VRBASECALCNF,
                             SUM(NVL(A.IMPOS,0)) AS IMPOS,
                             SUM(NVL(A.ISENT,0)) AS ISENT,
                             SUM(NVL(A.OUTRA,0)) AS OUTRA,
                             SUM(NVL(B.BASE,0)) AS BASE,
                             SUM(NVL(B.IMPO,0)) AS IMPO
                        FROM
                           (SELECT IP.CDFILIAL, IP.NRLANCTONF,
                                   IP.CDFISCOPERNF,
                                   ROUND(IP.VRCONTABILNF,2) AS VRCONTABILNF,
                                   ROUND(IP.VRBASECALCNF,2) AS VRBASECALCNF,
                                   ROUND(DECODE(CDFISCALNF,'1',IP.VRIMPOSTONF+NVL(IP.VRADICFECP,0),0),2) AS IMPOS,
                                   ROUND(DECODE(CDFISCALNF,'2',IP.VRCONTABILNF, 0),2) AS ISENT,
                                   ROUND(DECODE(CDFISCALNF,'3',IP.VRCONTABILNF, 0),2) AS OUTRA
                              FROM MAPAAPURAC MP, IMPOSTONF IP, NOTAFISCAL NF, IMPOSTO IM
                             WHERE MP.CDIMPOSTO    = P_CDIMPOSTO
                               AND MP.CDEMPRESA    = P_CDEMPRESA
                               AND MP.CDINSCESTA   = P_CDINSCESTA
                               AND MP.DTINIPERIODO = P_DTINIPERIODO
                               AND MP.DTFIMPERIODO = P_DTFIMPERIODO
                               AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                               AND IM.CDIMPOSTO    = IP.CDIMPOSTO
                               AND MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU
                               AND IP.CDFILIAL     = NF.CDFILIAL
                               AND IP.NRLANCTONF   = NF.NRLANCTONF
                               AND NF.IDENTRSAIDOP = 'S')A,
                           (SELECT IP.CDFILIAL, IP.NRLANCTONF,
                                   ROUND(DECODE(IP.CDFISCOPERNF,'1',IP.VRBASECALCNF,DECODE(IP.CDFISCOPERNF,'5',IP.VRBASECALCNF,0)),2) AS BASE,
                                   ROUND(DECODE(IP.CDFISCOPERNF,'1',IP.VRIMPOSTONF+NVL(IP.VRADICFECP,0),DECODE(IP.CDFISCOPERNF,'5',IP.VRIMPOSTONF+NVL(IP.VRADICFECP,0),0)),2) AS IMPO
                              FROM MAPAAPURAC MP, IMPOSTONF IP, NOTAFISCAL NF, IMPOSTO IM
                             WHERE MP.CDIMPOSTO    = '80' /*Imposto de Substituição tributária*/
                               AND MP.CDEMPRESA    = P_CDEMPRESA
                               AND MP.CDINSCESTA   = P_CDINSCESTA
                               AND MP.DTINIPERIODO = P_DTINIPERIODO
                               AND MP.DTFIMPERIODO = P_DTFIMPERIODO
                               AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                               AND IM.CDIMPOSTO    = IP.CDIMPOSTO
                               AND MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU
                               AND IP.CDFILIAL     = NF.CDFILIAL
                               AND IP.NRLANCTONF   = NF.NRLANCTONF
                               AND NF.IDENTRSAIDOP = 'S')B
                       WHERE A.CDFILIAL = B.CDFILIAL(+)
                         AND A.NRLANCTONF = B.NRLANCTONF(+)
                       GROUP BY A.CDFISCOPERNF
                       ORDER BY A.CDFISCOPERNF
                     )LOOP

      V_VRTOTREG130 := NVL(V_VRTOTREG130,0) + C_REG0130.VRCONTABILNF+C_REG0130.VRBASECALCNF+C_REG0130.IMPOS+C_REG0130.ISENT+C_REG0130.OUTRA+C_REG0130.BASE+C_REG0130.IMPO;
      V_SEQLINHA := V_SEQLINHA + 1;

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
      VALUES
        (LPAD(V_SEQLINHA,10,0),C_REG0130.DSARQUIVO||LPAD(V_SEQLINHA,5,0),P_NRSEQUENCIAL);

   END LOOP;

   /** REGISTRO 0140 **/
   FOR C_REG0140 IN (SELECT ('0140'||
                             LPAD('1',15,'0')||
                             LPAD(NVL(IT.CDITEMOCOR,0),5,0)||
                             REPLACE(LTRIM(TO_CHAR(NVL(IT.VRITEMMAPR,0),'0000000000000.00')),'.')||
                             RPAD(NVL(IT.DSITEMMAPR,' '),150,' ')||
                             RPAD(NVL(IT.DSITEMOCOR,' '),120,' ')||
                             RPAD(NVL(IT.DSFLEGALOCOR,' '),120,' ')||
                             RPAD(NVL(SUBSTR(IT.DSDADOCOMPLOCOR,1,  120),' '),120,' ')||
                             RPAD(NVL(SUBSTR(IT.DSDADOCOMPLOCOR,121,240),' '),120,' ')||
                             RPAD(NVL(SUBSTR(IT.DSDADOCOMPLOCOR,241,360),' '),120,' ')||
                             RPAD(' ',56,' ')) AS DSARQUIVO, NVL(IT.VRITEMMAPR,0) AS VRITEMMAPR
                       FROM MAPAAPURAC MP, ITMAPARECUP IT
                      WHERE MP.CDIMPOSTO    = P_CDIMPOSTO
                        AND MP.CDEMPRESA    = P_CDEMPRESA
                        AND MP.CDINSCESTA   = P_CDINSCESTA
                        AND MP.DTINIPERIODO = P_DTINIPERIODO
                        AND MP.DTFIMPERIODO = P_DTFIMPERIODO
                        AND MP.NRSEQUMAPAPU = IT.NRSEQUMAPAPU
                        AND IT.IDTIPOITMAPR = 'OD')LOOP

      V_VRTOTREG140 := NVL(V_VRTOTREG140,0) + C_REG0140.VRITEMMAPR;
      V_SEQLINHA := V_SEQLINHA + 1;

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
      VALUES
        (LPAD(V_SEQLINHA,10,0),C_REG0140.DSARQUIVO||LPAD(V_SEQLINHA,5,0),P_NRSEQUENCIAL);
   END LOOP;
   
   /** REGISTRO 0150 **/
   FOR C_REG0150 IN (SELECT ('0150'||
                             LPAD('1',15,'0')||
                             LPAD(NVL(IT.CDITEMOCOR,0),5,0)||
                             REPLACE(LTRIM(TO_CHAR(NVL(IT.VRITEMMAPR,0),'0000000000000.00')),'.')||
                             RPAD(NVL(IT.DSITEMMAPR,' '),150,' ')||
                             RPAD(NVL(IT.DSITEMOCOR,' '),120,' ')||
                             RPAD(NVL(IT.DSFLEGALOCOR,' '),120,' ')||
                             RPAD(NVL(SUBSTR(IT.DSDADOCOMPLOCOR,1,  120),' '),120,' ')||
                             RPAD(NVL(SUBSTR(IT.DSDADOCOMPLOCOR,121,240),' '),120,' ')||
                             RPAD(NVL(SUBSTR(IT.DSDADOCOMPLOCOR,241,360),' '),120,' ')||
                             RPAD(' ',56,' ')) AS DSARQUIVO, NVL(IT.VRITEMMAPR,0) AS VRITEMMAPR
                       FROM MAPAAPURAC MP, ITMAPARECUP IT
                      WHERE MP.CDIMPOSTO    = P_CDIMPOSTO
                        AND MP.CDEMPRESA    = P_CDEMPRESA
                        AND MP.CDINSCESTA   = P_CDINSCESTA
                        AND MP.DTINIPERIODO = P_DTINIPERIODO
                        AND MP.DTFIMPERIODO = P_DTFIMPERIODO
                        AND MP.NRSEQUMAPAPU = IT.NRSEQUMAPAPU
                        AND IT.IDTIPOITMAPR = 'EC')LOOP

      V_VRTOTREG150 := NVL(V_VRTOTREG150,0) + C_REG0150.VRITEMMAPR;
      V_SEQLINHA := V_SEQLINHA + 1;

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
      VALUES
        (LPAD(V_SEQLINHA,10,0),C_REG0150.DSARQUIVO||LPAD(V_SEQLINHA,5,0),P_NRSEQUENCIAL);
   END LOOP;
   
   /** REGISTRO 0160 **/
   FOR C_REG0160 IN (SELECT ('0160'||
                             LPAD('1',15,'0')||
                             LPAD(NVL(IT.CDITEMOCOR,0),5,0)||
                             REPLACE(LTRIM(TO_CHAR(NVL(IT.VRITEMMAPR,0),'0000000000000.00')),'.')||
                             RPAD(NVL(IT.DSITEMMAPR,' '),150,' ')||
                             RPAD(NVL(IT.DSITEMOCOR,' '),120,' ')||
                             RPAD(NVL(IT.DSFLEGALOCOR,' '),120,' ')||
                             RPAD(NVL(SUBSTR(IT.DSDADOCOMPLOCOR,1,  120),' '),120,' ')||
                             RPAD(NVL(SUBSTR(IT.DSDADOCOMPLOCOR,121,240),' '),120,' ')||
                             RPAD(NVL(SUBSTR(IT.DSDADOCOMPLOCOR,241,360),' '),120,' ')||
                             RPAD(' ',56,' ')) AS DSARQUIVO, NVL(IT.VRITEMMAPR,0) AS VRITEMMAPR
                       FROM MAPAAPURAC MP, ITMAPARECUP IT
                      WHERE MP.CDIMPOSTO    = P_CDIMPOSTO
                        AND MP.CDEMPRESA    = P_CDEMPRESA
                        AND MP.CDINSCESTA   = P_CDINSCESTA
                        AND MP.DTINIPERIODO = P_DTINIPERIODO
                        AND MP.DTFIMPERIODO = P_DTFIMPERIODO
                        AND MP.NRSEQUMAPAPU = IT.NRSEQUMAPAPU
                        AND IT.IDTIPOITMAPR = 'OC')LOOP

      V_VRTOTREG160 := NVL(V_VRTOTREG160,0) + C_REG0160.VRITEMMAPR;
      V_SEQLINHA := V_SEQLINHA + 1;

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
      VALUES
        (LPAD(V_SEQLINHA,10,0),C_REG0160.DSARQUIVO||LPAD(V_SEQLINHA,5,0),P_NRSEQUENCIAL);
   END LOOP;

   /** REGISTRO 0170 **/
   FOR C_REG0170 IN (SELECT ('0170'||
                             LPAD('1',15,'0')||
                             LPAD(NVL(IT.CDITEMOCOR,0),5,0)||
                             REPLACE(LTRIM(TO_CHAR(NVL(IT.VRITEMMAPR,0),'0000000000000.00')),'.')||
                             RPAD(NVL(IT.DSITEMMAPR,' '),150,' ')||
                             RPAD(NVL(IT.DSITEMOCOR,' '),120,' ')||
                             RPAD(NVL(IT.DSFLEGALOCOR,' '),120,' ')||
                             RPAD(NVL(SUBSTR(IT.DSDADOCOMPLOCOR,1,  120),' '),120,' ')||
                             RPAD(NVL(SUBSTR(IT.DSDADOCOMPLOCOR,121,240),' '),120,' ')||
                             RPAD(NVL(SUBSTR(IT.DSDADOCOMPLOCOR,241,360),' '),120,' ')||
                             RPAD(' ',56,' ')) AS DSARQUIVO, NVL(IT.VRITEMMAPR,0) AS VRITEMMAPR
                       FROM MAPAAPURAC MP, ITMAPARECUP IT
                      WHERE MP.CDIMPOSTO    = P_CDIMPOSTO
                        AND MP.CDEMPRESA    = P_CDEMPRESA
                        AND MP.CDINSCESTA   = P_CDINSCESTA
                        AND MP.DTINIPERIODO = P_DTINIPERIODO
                        AND MP.DTFIMPERIODO = P_DTFIMPERIODO
                        AND MP.NRSEQUMAPAPU = IT.NRSEQUMAPAPU
                        AND IT.IDTIPOITMAPR = 'ED')LOOP

      V_VRTOTREG170 := NVL(V_VRTOTREG170,0) + C_REG0170.VRITEMMAPR;
      V_SEQLINHA := V_SEQLINHA + 1;

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
      VALUES
        (LPAD(V_SEQLINHA,10,0),C_REG0170.DSARQUIVO||LPAD(V_SEQLINHA,5,0),P_NRSEQUENCIAL);
   END LOOP;

   /** REGISTRO 0180 **/
   FOR C_REG0180 IN (SELECT ('0180'||
                             LPAD('1',15,'0')||
                             LPAD(NVL(IT.CDITEMOCOR,0),5,0)||
                             REPLACE(LTRIM(TO_CHAR(NVL(IT.VRITEMMAPR,0),'0000000000000.00')),'.')||
                             RPAD(NVL(IT.DSITEMMAPR,' '),150,' ')||
                             RPAD(NVL(IT.DSITEMOCOR,' '),120,' ')||
                             RPAD(NVL(IT.DSFLEGALOCOR,' '),120,' ')||
                             RPAD(NVL(SUBSTR(IT.DSDADOCOMPLOCOR,1,  120),' '),120,' ')||
                             RPAD(NVL(SUBSTR(IT.DSDADOCOMPLOCOR,121,240),' '),120,' ')||
                             RPAD(NVL(SUBSTR(IT.DSDADOCOMPLOCOR,241,360),' '),120,' ')||
                             RPAD(' ',56,' ')) AS DSARQUIVO, NVL(IT.VRITEMMAPR,0) AS VRITEMMAPR
                       FROM MAPAAPURAC MP, ITMAPARECUP IT
                      WHERE MP.CDIMPOSTO    = P_CDIMPOSTO
                        AND MP.CDEMPRESA    = P_CDEMPRESA
                        AND MP.CDINSCESTA   = P_CDINSCESTA
                        AND MP.DTINIPERIODO = P_DTINIPERIODO
                        AND MP.DTFIMPERIODO = P_DTFIMPERIODO
                        AND MP.NRSEQUMAPAPU = IT.NRSEQUMAPAPU
                        AND IT.IDTIPOITMAPR = 'DD')LOOP

      V_VRTOTREG180 := NVL(V_VRTOTREG180,0) + C_REG0180.VRITEMMAPR;
      V_SEQLINHA := V_SEQLINHA + 1;

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
      VALUES
        (LPAD(V_SEQLINHA,10,0),C_REG0180.DSARQUIVO||LPAD(V_SEQLINHA,5,0),P_NRSEQUENCIAL);
   END LOOP;

   /** REGISTRO 0200 **/
   FOR C_REG0200 IN (SELECT ('0200'||
                             LPAD('1',15,'0')||
                             LPAD(NVL(IT.CDITEMOCOR,0),5,0)||
                             REPLACE(LTRIM(TO_CHAR(NVL(IT.VRITEMMAPR,0),'0000000000000.00')),'.')||
                             RPAD(NVL(IT.DSITEMMAPR,' '),150,' ')||
                             RPAD(NVL(IT.DSITEMOCOR,' '),120,' ')||
                             RPAD(NVL(IT.DSFLEGALOCOR,' '),120,' ')||
                             RPAD(NVL(SUBSTR(IT.DSDADOCOMPLOCOR,1,  120),' '),120,' ')||
                             RPAD(NVL(SUBSTR(IT.DSDADOCOMPLOCOR,121,240),' '),120,' ')||
                             RPAD(NVL(SUBSTR(IT.DSDADOCOMPLOCOR,241,360),' '),120,' ')||
                             RPAD(' ',56,' ')) AS DSARQUIVO, NVL(IT.VRITEMMAPR,0) AS VRITEMMAPR
                       FROM MAPAAPURAC MP, ITMAPARECUP IT
                      WHERE MP.CDIMPOSTO    = P_CDIMPOSTO
                        AND MP.CDEMPRESA    = P_CDEMPRESA
                        AND MP.CDINSCESTA   = P_CDINSCESTA
                        AND MP.DTINIPERIODO = P_DTINIPERIODO
                        AND MP.DTFIMPERIODO = P_DTFIMPERIODO
                        AND MP.NRSEQUMAPAPU = IT.NRSEQUMAPAPU
                        AND IT.IDTIPOITMAPR IN ('DE','DA'))LOOP

      V_VRTOTREG200 := NVL(V_VRTOTREG200,0) + C_REG0200.VRITEMMAPR;
      V_SEQLINHA := V_SEQLINHA + 1;

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
      VALUES
        (LPAD(V_SEQLINHA,10,0),C_REG0200.DSARQUIVO||LPAD(V_SEQLINHA,5,0),P_NRSEQUENCIAL);
   END LOOP;

   /** REGISTRO 0210 **/
   FOR C_REG0210 IN (SELECT ('0210'||
                             LPAD('1',15,'0')||
                             LPAD(NVL(A.UF,0),3,0)||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(VRCONT,0)),'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(VRBASE,0)),'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(VROUTRAS,0)),'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(0,'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(0,'0000000000000.00')),'.')||
                             RPAD(' ',748,' ')) AS DSARQUIVO,
                             SUM(NVL(VRCONT,0)) AS VRCONT,
                             SUM(NVL(VRBASE,0)) AS VRBASE,
                             SUM(NVL(VROUTRAS,0)) AS VROUTRAS
                       FROM
                         (SELECT E.CDESTADOIBGE AS UF,
                                 ROUND(IP.VRCONTABILNF,2) AS VRCONT,
                                 ROUND(IP.VRBASECALCNF,2) AS VRBASE,
                                 ROUND((DECODE(CDFISCALNF,'2',IP.VRCONTABILNF,0) + DECODE(CDFISCALNF,'3',IP.VRCONTABILNF,0)),2) AS VROUTRAS
                            FROM MAPAAPURAC MP, IMPOSTONF IP, NOTAFISCAL NF,
                                 ENDECLIE EC, ENDEFORN EF, ENDEFILI EL,
                                 ESTADO E, IMPOSTO IM
                           WHERE MP.CDIMPOSTO    = P_CDIMPOSTO
                             AND MP.CDEMPRESA    = P_CDEMPRESA
                             AND MP.CDINSCESTA   = P_CDINSCESTA
                             AND MP.DTINIPERIODO = P_DTINIPERIODO
                             AND MP.DTFIMPERIODO = P_DTFIMPERIODO
                             AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                             AND IM.CDIMPOSTO    = IP.CDIMPOSTO
                             AND MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU
                             AND SUBSTR(IP.CDFISCOPERNF,1,1) = '2'
                             AND IP.CDFILIAL     = NF.CDFILIAL
                             AND IP.NRLANCTONF   = NF.NRLANCTONF
                             AND NF.IDENTRSAIDOP = 'E'
                             AND NF.CDCLIENTE    = EC.CDCLIENTE(+)
                             AND IDTPENDECLIE(+) = 'P'
                             AND NF.CDFORNECED   = EF.CDFORNECED(+)
                             AND IDTPENDEFORN(+) = 'P'
                             AND NF.CDFILIEMIT   = EL.CDFILIAL(+)
                             AND IDTPENDEFILI(+) = 'P'
                             AND DECODE(NF.IDORIGDESTNF, 'C', EC.CDPAIS, DECODE(NF.IDORIGDESTNF, 'F', EF.CDPAIS, EL.CDPAIS)) = E.CDPAIS
                             AND DECODE(NF.IDORIGDESTNF, 'C', EC.SGESTADO, DECODE(NF.IDORIGDESTNF, 'F', EF.SGESTADO, EL.SGESTADO)) = E.SGESTADO)A
                      GROUP BY A.UF
                      ORDER BY A.UF
                     )LOOP

      V_VRTOTREG210 := NVL(V_VRTOTREG210,0) + C_REG0210.VRCONT+C_REG0210.VRBASE+C_REG0210.VROUTRAS;
      V_SEQLINHA := V_SEQLINHA + 1;

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
      VALUES
        (LPAD(V_SEQLINHA,10,0),C_REG0210.DSARQUIVO||LPAD(V_SEQLINHA,5,0),P_NRSEQUENCIAL);

   END LOOP;

   /** REGISTRO 0220 **/
   FOR C_REG0220 IN (SELECT ('0220'||
                             LPAD('1',15,'0')||
                             LPAD(NVL(A.UF,0),3,0)||
                             REPLACE(LTRIM(TO_CHAR(0,'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(A.VRCONT,0)),'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(0,'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(A.VRBASE,0)),'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(A.VROUTRAS,0)),'0000000000000.00')),'.')||
                             REPLACE(LTRIM(TO_CHAR(SUM(NVL(B.VRIMPST,0)),'0000000000000.00')),'.')||
                             RPAD(' ',733,' ')) AS DSARQUIVO,
                             SUM(NVL(A.VRCONT,0)) AS VRCONT,
                             SUM(NVL(A.VRBASE,0)) AS VRBASE,
                             SUM(NVL(A.VROUTRAS,0)) AS VROUTRAS,
                             SUM(NVL(B.VRIMPST,0)) AS VRIMPST
                       FROM
                         (SELECT IP.CDFILIAL, IP.NRLANCTONF,
                                 E.CDESTADOIBGE AS UF,
                                 ROUND(IP.VRCONTABILNF,2) AS VRCONT,
                                 ROUND(IP.VRBASECALCNF,2) AS VRBASE,
                                 ROUND((DECODE(CDFISCALNF,'2',IP.VRCONTABILNF,0) + DECODE(CDFISCALNF,'3',IP.VRCONTABILNF,0)),2) AS VROUTRAS
                            FROM MAPAAPURAC MP, IMPOSTONF IP, NOTAFISCAL NF,
                                 ENDECLIE EC, ENDEFORN EF, ENDEFILI EL,
                                 ESTADO E, IMPOSTO IM
                           WHERE MP.CDIMPOSTO    = P_CDIMPOSTO
                             AND MP.CDEMPRESA    = P_CDEMPRESA
                             AND MP.CDINSCESTA   = P_CDINSCESTA
                             AND MP.DTINIPERIODO = P_DTINIPERIODO
                             AND MP.DTFIMPERIODO = P_DTFIMPERIODO
                             AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                             AND IM.CDIMPOSTO    = IP.CDIMPOSTO
                             AND MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU
                             AND SUBSTR(IP.CDFISCOPERNF,1,1) = '6'
                             AND IP.CDFILIAL     = NF.CDFILIAL
                             AND IP.NRLANCTONF   = NF.NRLANCTONF
                             AND NF.IDENTRSAIDOP = 'S'
                             AND NF.CDCLIENTE    = EC.CDCLIENTE(+)
                             AND IDTPENDECLIE(+) = 'P'
                             AND NF.CDFORNECED   = EF.CDFORNECED(+)
                             AND IDTPENDEFORN(+) = 'P'
                             AND NF.CDFILIRECE   = EL.CDFILIAL(+)
                             AND IDTPENDEFILI(+) = 'P'
                             AND DECODE(NF.IDORIGDESTNF, 'C', EC.CDPAIS, DECODE(NF.IDORIGDESTNF, 'F', EF.CDPAIS, EL.CDPAIS)) = E.CDPAIS
                             AND DECODE(NF.IDORIGDESTNF, 'C', EC.SGESTADO, DECODE(NF.IDORIGDESTNF, 'F', EF.SGESTADO, EL.SGESTADO)) = E.SGESTADO)A,
                         (SELECT IP.CDFILIAL, IP.NRLANCTONF, ROUND(IP.VRIMPOSTONF+NVL(IP.VRADICFECP,0),2) AS VRIMPST
                            FROM MAPAAPURAC MP, IMPOSTONF IP, NOTAFISCAL NF, IMPOSTO IM
                           WHERE MP.CDIMPOSTO    = '80' /*Imposto de Substituição tributária*/
                             AND MP.CDEMPRESA    = P_CDEMPRESA
                             AND MP.CDINSCESTA   = P_CDINSCESTA
                             AND MP.DTINIPERIODO = P_DTINIPERIODO
                             AND MP.DTFIMPERIODO = P_DTFIMPERIODO
                             AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                             AND IM.CDIMPOSTO    = IP.CDIMPOSTO
                             AND MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU
                             AND IP.CDFILIAL     = NF.CDFILIAL
                             AND IP.NRLANCTONF   = NF.NRLANCTONF
                             AND NF.IDENTRSAIDOP = 'S')B
                      WHERE A.CDFILIAL = B.CDFILIAL(+)
                        AND A.NRLANCTONF = B.NRLANCTONF(+)
                      GROUP BY A.UF
                      ORDER BY A.UF
                     )LOOP

      V_VRTOTREG220 := NVL(V_VRTOTREG220,0) + C_REG0220.VRCONT+C_REG0220.VRBASE+C_REG0220.VROUTRAS+C_REG0220.VRIMPST;
      V_SEQLINHA := V_SEQLINHA + 1;

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
      VALUES
        (LPAD(V_SEQLINHA,10,0),C_REG0220.DSARQUIVO||LPAD(V_SEQLINHA,5,0),P_NRSEQUENCIAL);

   END LOOP;

   /** RODAPE **/
   V_SEQLINHA := V_SEQLINHA + 1;

   V_VRTOTAL := NVL(V_VRTOTREG110,0)+
                NVL(V_VRTOTREG120,0)+
                NVL(V_VRTOTREG130,0)+
                NVL(V_VRTOTREG140,0)+
                NVL(V_VRTOTREG150,0)+
                NVL(V_VRTOTREG160,0)+
                NVL(V_VRTOTREG170,0)+
                NVL(V_VRTOTREG180,0)+
                NVL(V_VRTOTREG200,0)+
                NVL(V_VRTOTREG210,0)+
                NVL(V_VRTOTREG220,0);

   BEGIN
      SELECT '9999'                ||
             REPLACE(LTRIM(TO_CHAR(SUM(NVL(V_VRTOTAL,0)),'00000000000000000000000.00')),'.')||
             '00001'               ||
             LPAD(V_SEQLINHA,5,0)  ||
             LPAD(' ',806,' ')     ||
             LPAD(V_SEQLINHA,5,0)
        INTO V_RODAPE
        FROM DUAL;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
        V_RODAPE := '';
   END;

   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
   VALUES
     (LPAD(V_SEQLINHA,10,0),V_RODAPE,P_NRSEQUENCIAL);
END;
/

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

