/*
   aplicar a funcao que busca o cpf ou cnpj do cliente
*/
CREATE OR REPLACE PROCEDURE ARQUIVO_DIRF(
   P_CDLIVROMAG   IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   P_DTFINAL      IN NOTAFISCAL.CDFILIAL%TYPE,
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_CDCONTADOR   IN CONTADOR.CDCONTADOR2%TYPE,
   P_NRSEQUENCIAL IN NOTAFISCAL.NRLANCTONF%TYPE,
   P_TPDECLARACAO IN NOTAFISCAL.IDDOCFISCAL%TYPE,
   P_STDECLARACAO IN NOTAFISCAL.IDDOCFISCAL%TYPE,
   P_TPRENDIMENTO IN NOTAFISCAL.IDDOCFISCAL%TYPE,
   P_IDDECLARANTE IN NOTAFISCAL.IDDOCFISCAL%TYPE,
   P_IDSOCIO      IN NOTAFISCAL.IDDOCFISCAL%TYPE,
   P_TPEVENTO     IN NOTAFISCAL.IDDOCFISCAL%TYPE
)
AS
   V_CONTADOR       NUMBER;
   V_CONTREGI       NUMBER;
   V_DADOS_1        VARCHAR2(1000);
   V_DADOS_2        VARCHAR2(1000);
   V_EMPRESA        VARCHAR2(1000);
   V_DADOS          VARCHAR2(4000);
   V_LINHA          VARCHAR2(4000);
   V_LINHA_FIM      VARCHAR2(4000);
   V_DADOS_TOT      VARCHAR2(4000);
   V_DADOS_TOT_1    VARCHAR2(4000);
   V_LINHA_FIM_TOT  VARCHAR2(4000);
   V_TIPO_CPF_CNPJ  VARCHAR2(01);
   V_CDRECEGUIA     VARCHAR2(20);

BEGIN

   /****************************************** registro tipo 1 *********************************************/
   V_CDRECEGUIA := '';
   SELECT MAX('1'||
          REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/','') ||
          'DIRF'||
          P_DTFINAL||
          P_TPDECLARACAO||
          P_STDECLARACAO||
          '2'||
          '0'||
          P_TPRENDIMENTO||
          '2010'||
          P_IDDECLARANTE||
          P_IDSOCIO||
          RPAD(NVL(NMRAZSOCEMPR,' '), 60,  ' ') ),
          MAX(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''))
     INTO V_DADOS_1, V_EMPRESA
     FROM EMPRESA
    WHERE CDEMPRESA = P_CDEMPRESA;

   SELECT MAX(REPLACE(REPLACE(REPLACE(NRCPFRESPONS,'.',''),'/',''),'-','')||
          RPAD(' ', 8, ' ')||
          RPAD(' ', 284, ' ')||
          REPLACE(REPLACE(REPLACE(NRCPFRESPONS,'.',''),'/',''),'-','')||
          RPAD(NVL(NMCONTADOR,' '), 60, ' ')||
          '00'||REPLACE(REPLACE(REPLACE(NRTELCONTATO,' ',''),'-',''),'.','')||
          LPAD(NVL(NRRAMALCONTA,'0'), 6, '0')||
          LPAD(NVL(NRFAXCONTATO,'0'), 8, '0')||
          RPAD(NVL(DSENDINTERN,' '), 50, ' ')||
          RPAD(' ', 165, ' ')||
          RPAD(' ', 12, ' ')||
          '9')
     INTO V_DADOS_2
     FROM CONTADOR
    WHERE CDCONTADOR2 = P_CDCONTADOR;

    V_CONTADOR := NVL(V_CONTADOR,0)+1;
    INSERT INTO ARQMAGNETICO
      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
    VALUES
      ('01'||LPAD(RTRIM(TO_CHAR(0)), 8, '0'), LPAD(TO_CHAR(V_CONTADOR),'8','0')||V_DADOS_1||V_DADOS_2, P_NRSEQUENCIAL);


   /* impostos que devem ser gerados */
   FOR C_IMPOSTO IN (SELECT CDIMPOSTOLIVRO, SGIMPOSTOLIVRO
                       FROM TPIMPOSLIVRO
                      WHERE CDLIVROMAG = P_CDLIVROMAG
                      ORDER BY CDIMPOSTOLIVRO) LOOP

       /* codigo da receita */
       SELECT MAX(CDRECEGUIA)
         INTO V_CDRECEGUIA
         FROM MAPAAPURAC
        WHERE CDIMPOSTO = C_IMPOSTO.CDIMPOSTOLIVRO
          AND CDEMPRESA = P_CDEMPRESA
          AND TO_CHAR(DTINIPERIODO, 'YYYY') = P_DTFINAL;

      /****************************************** registro tipo 2 *********************************************/
      FOR C_NOTAS IN (SELECT RPAD(NVL(F.NMRAZSOCFORN,' '), 60, ' ') AS NMRAZSOCFORN,
                             SUM(VRBASECALCNF) AS VRTRIBUTAVEL,
                             0 DEDUCAO,
                             SUM(VRIMPOSTONF) AS VRIMPOSTONF,
                             N.NRINSJURNF AS NRINSJURNF
                        FROM MAPAAPURAC M, IMPOSTONF I, NOTAFISCAL N, FORNECEDOR F
                       WHERE M.CDIMPOSTO = C_IMPOSTO.CDIMPOSTOLIVRO
                         AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                         AND I.CDIMPOSTO = C_IMPOSTO.CDIMPOSTOLIVRO
                         AND I.CDFILIAL = N.CDFILIAL
                         AND I.NRLANCTONF = N.NRLANCTONF
                         AND I.VRBASECALCNF > 0
                         AND F.CDFORNECED = N.CDFORNECED
                         AND N.IDORIGDESTNF = 'F'
                         AND TO_CHAR(M.DTINIPERIODO, 'YYYY') = P_DTFINAL
                       GROUP BY N.NRINSJURNF, RPAD(NVL(F.NMRAZSOCFORN,' '), 60, ' ')
                       UNION ALL
                      SELECT RPAD(NVL(C.NMRAZSOCCLIE,' '), 60, ' ') AS NMRAZSOCFORN,
                             SUM(VRBASECALCNF) AS VRTRIBUTAVEL,
                             0 DEDUCAO,
                             SUM(VRIMPOSTONF) AS VRIMPOSTONF,
                             N.NRINSJURNF AS NRINSJURNF
                        FROM MAPAAPURAC M, IMPOSTONF I, NOTAFISCAL N, CLIENTE C
                       WHERE M.CDIMPOSTO = C_IMPOSTO.CDIMPOSTOLIVRO
                         AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                         AND I.CDIMPOSTO = C_IMPOSTO.CDIMPOSTOLIVRO
                         AND I.CDFILIAL = N.CDFILIAL
                         AND I.NRLANCTONF = N.NRLANCTONF
                         AND I.VRBASECALCNF > 0
                         AND C.CDCLIENTE = N.CDCLIENTE
                         AND TO_CHAR(M.DTINIPERIODO, 'YYYY') = P_DTFINAL
                         AND N.IDORIGDESTNF = 'C'
                       GROUP BY N.NRINSJURNF, RPAD(NVL(C.NMRAZSOCCLIE,' '), 60, ' ')
                       UNION ALL
                      SELECT RPAD(NVL(F.NMRAZSOCFILI,' '), 60, ' ') AS NMRAZSOCFORN,
                             SUM(VRBASECALCNF) AS VRTRIBUTAVEL,
                             0 DEDUCAO,
                             SUM(VRIMPOSTONF) AS VRIMPOSTONF,
                             N.NRINSJURNF AS NRINSJURNF
                        FROM MAPAAPURAC M, IMPOSTONF I, NOTAFISCAL N, FILIAL F
                       WHERE M.CDIMPOSTO = C_IMPOSTO.CDIMPOSTOLIVRO
                         AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                         AND I.CDIMPOSTO = C_IMPOSTO.CDIMPOSTOLIVRO
                         AND I.CDFILIAL = N.CDFILIAL
                         AND I.NRLANCTONF = N.NRLANCTONF
                         AND I.VRBASECALCNF > 0
                         AND F.CDFILIAL = DECODE(N.IDENTRSAIDOP, 'E', N.CDFILIRECE, N.CDFILIEMIT)
                         AND N.IDORIGDESTNF = 'L'
                         AND TO_CHAR(M.DTINIPERIODO, 'YYYY') = P_DTFINAL
                       GROUP BY N.NRINSJURNF, RPAD(NVL(F.NMRAZSOCFILI,' '), 60, ' ') ) LOOP

         V_TIPO_CPF_CNPJ := '';
         /* tipo cnpj */
         IF VALIDA_CPF_CNPJ(C_NOTAS.NRINSJURNF) = 'CPF' THEN
            V_TIPO_CPF_CNPJ := '1';
         ELSE
            V_TIPO_CPF_CNPJ := '2';
         END IF;

         V_LINHA := '2'||
                 V_EMPRESA||
                 V_CDRECEGUIA||
                 V_TIPO_CPF_CNPJ;

         V_DADOS := '';
         FOR C_REG IN (SELECT DTINIPERIODO,
                              SUM(VRTRIBUTAVEL) AS VRTRIBUTAVEL,
                              SUM(DEDUCAO) AS DEDUCAO,
                              SUM(VRIMPOSTONF) AS VRIMPOSTONF
                         FROM (SELECT M.DTINIPERIODO,
                                      SUM(VRBASECALCNF) AS VRTRIBUTAVEL,
                                      0 DEDUCAO,
                                      SUM(VRIMPOSTONF) AS VRIMPOSTONF
                                 FROM MAPAAPURAC M, IMPOSTONF I, NOTAFISCAL N
                                WHERE M.CDIMPOSTO = C_IMPOSTO.CDIMPOSTOLIVRO
                                  AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                                  AND I.CDIMPOSTO = C_IMPOSTO.CDIMPOSTOLIVRO
                                  AND NRINSJURNF = C_NOTAS.NRINSJURNF
                                  AND I.CDFILIAL = N.CDFILIAL
                                  AND I.NRLANCTONF = N.NRLANCTONF
                                GROUP BY M.DTINIPERIODO
                                UNION ALL
                               SELECT DTMES AS DTINIPERIODO,
                                      0 AS VRTRIBUTAVEL,
                                      0 AS DEDUCAO,
                                      0 AS VRIMPOSTONF
                                 FROM DATA
                                WHERE TO_CHAR(DTMES, 'yyyy') = P_DTFINAL
                                GROUP BY DTMES )
                        GROUP BY DTINIPERIODO
                        ORDER BY DTINIPERIODO) LOOP

            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '01/01/||P_DTFINAL' THEN
               V_DADOS := LPAD(NVL(C_NOTAS.NRINSJURNF, '0'), 14, '0')   ||
                          C_NOTAS.NMRAZSOCFORN ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.',''))||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;
            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '16/01/||P_DTFINAL' THEN
                          V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.',''))||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;

            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '01/02/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.',''))||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;
            
            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '16/02/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.',''))||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;

            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '01/03/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;
           
            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '16/03/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;
            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '01/04/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;
            
            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '16/04/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;
            
            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '01/05/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;

            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '01/05/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;

            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '01/06/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;

            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '16/06/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;
            
            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '01/07/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;

            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '16/07/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;

            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '01/08/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;
            
            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '16/08/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;

            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '01/09/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;

            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '16/09/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;

            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '01/10/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;

            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '16/10/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;

            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '01/11/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;

            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '16/11/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''));
            END IF;

            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '01/12/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''))  ;
            END IF;

            IF TO_CHAR(C_REG.DTINIPERIODO,'DD/MM/YYYY') = '16/12/||P_DTFINAL' THEN
               V_DADOS := V_DADOS||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.DEDUCAO),'0000000000000.00'),'.',''))      ||
                          LTRIM(REPLACE(TO_CHAR(ABS(C_REG.VRIMPOSTONF),'0000000000000.00'),'.',''))  ;
            END IF;
         END LOOP;
         V_LINHA_FIM := '0'||
                        '0'||
                        RPAD(' ',8,' ')||
                        RPAD('0',31,'0')||
                        '9';

         V_CONTADOR := NVL(V_CONTADOR,0)+1;
         V_CONTREGI := NVL(V_CONTREGI,0)+1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('02'||LPAD(RTRIM(TO_CHAR(0)), 8, '0'), LPAD(TO_CHAR(V_CONTADOR),'8','0')||V_LINHA||V_DADOS||RPAD('0',45,'0')||V_LINHA_FIM, P_NRSEQUENCIAL);

      END LOOP;
      /****************************************** registro tipo 2 *********************************************/
      V_DADOS_TOT := '3'||
                     V_EMPRESA||
                     V_CDRECEGUIA|| /* codigo de receita */
                     LPAD(NVL(TO_CHAR(V_CONTREGI),0), 8, '0')||
                     RPAD(' ', 67, ' ');

      FOR C_TOTAL IN (SELECT DTINIPERIODO,
                             SUM(VRTRIBUTAVEL) AS VRTRIBUTAVEL,
                             SUM(DEDUCAO) AS DEDUCAO,
                             SUM(VRIMPOSTONF) AS VRIMPOSTONF
                        FROM (SELECT M.DTINIPERIODO,
                                     SUM(VRBASECALCNF) AS VRTRIBUTAVEL,
                                     0 DEDUCAO,
                                     SUM(VRIMPOSTONF) AS VRIMPOSTONF
                                FROM MAPAAPURAC M, IMPOSTONF I, NOTAFISCAL N
                               WHERE M.CDIMPOSTO = C_IMPOSTO.CDIMPOSTOLIVRO
                                 AND M.NRSEQUMAPAPU = I.NRSEQUMAPAPU
                                 AND I.CDIMPOSTO = C_IMPOSTO.CDIMPOSTOLIVRO
                                 AND I.CDFILIAL = N.CDFILIAL
                                 AND I.NRLANCTONF = N.NRLANCTONF
                               GROUP BY M.DTINIPERIODO
                               UNION ALL
                              SELECT DTMES AS DTINIPERIODO,
                                     0 AS VRTRIBUTAVEL,
                                     0 AS DEDUCAO,
                                     0 AS VRIMPOSTONF
                                FROM DATA
                               WHERE TO_CHAR(DTMES, 'yyyy') = P_DTFINAL
                               GROUP BY DTMES )
                       GROUP BY DTINIPERIODO
                       ORDER BY DTINIPERIODO) LOOP

         IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '01/01/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.',''))||
                             LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))||
                             LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '16/01/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.',''))||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
         IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '01/02/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.',''))||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '16/02/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.',''))||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
         IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '01/03/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '16/03/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
         IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '01/04/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '16/04/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
         IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '01/05/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '16/05/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
         IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '01/06/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '16/06/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
         IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '01/07/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '16/07/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
         IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '01/08/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '16/08/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
         IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '01/09/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '16/09/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
         IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '01/10/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '16/10/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
         IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '01/11/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '16/11/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
         IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '01/12/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''));
         END IF;
IF TO_CHAR(C_TOTAL.DTINIPERIODO,'DD/MM/YYYY') = '16/12/||P_DTFINAL' THEN
            V_DADOS_TOT_1 := V_DADOS_TOT_1||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRTRIBUTAVEL),'0000000000000.00'),'.','')) ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.DEDUCAO),'0000000000000.00'),'.',''))      ||
                       LTRIM(REPLACE(TO_CHAR(ABS(C_TOTAL.VRIMPOSTONF),'0000000000000.00'),'.',''))  ;
         END IF;
      END LOOP;
      V_LINHA_FIM_TOT := RPAD(' ',30,' ')||
                         RPAD(' ',12,' ')||
                         '9';

      V_CONTADOR := NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('03'||LPAD(RTRIM(TO_CHAR(0)), 8, '0'), LPAD(TO_CHAR(V_CONTADOR),'8','0')||V_DADOS_TOT||V_DADOS_TOT_1||RPAD('0',45,'0')||V_LINHA_FIM_TOT, P_NRSEQUENCIAL);
   END LOOP;
END;
/
