CREATE OR REPLACE PROCEDURE ARQUIVO_MIT(
   P_TIPOEVENTO      IN NUMBER,
   P_NRSEQPROJETO    IN NUMBER,
   P_DTEVENTO        IN DATE,
   P_IDQUALIFICAOPJ  IN NUMBER,
   P_IDTRIBUTALUCRO  IN NUMBER,
   P_CDEMPRESA       IN VARCHAR2,
   P_DTINICIAL       IN DATE,
   P_DTFINAL         IN DATE,
   P_VARIACOESMONE   IN NUMBER,
   P_REGIMEPISCOFINS IN NUMBER,
   P_NRSEQARQMAG     IN VARCHAR2)
AS
   V_CONT                    NUMBER;
   V_COUNT                   NUMBER;
   V_COUNTEV                 NUMBER;
   V_CONT_DADOSINI           NUMBER;
   V_CONTDEBITO              NUMBER;
   V_CONTADORULTREG          NUMBER;

   V_PADEBITO                NUMBER;
   V_CNPJSCP                 VARCHAR2(14);

   V_TEMDADOS                VARCHAR2(1);
   V_GEROUCABECALHO          VARCHAR2(1);
   V_GEROUCABEAPOSEV         VARCHAR2(1);
   V_GEROUIRPJ               VARCHAR2(1);
   V_GEROUCSLL               VARCHAR2(1);
   V_GEROUIRRF               VARCHAR2(1);
   V_GEROUCOFINS             VARCHAR2(1);
   V_GEROUPIS                VARCHAR2(1);
   V_GEROUIPI                VARCHAR2(1);
   V_ENVIABALANCO            VARCHAR2(1);
   V_IDBALSUSRED             VARCHAR2(10);
   V_DESCRANO                VARCHAR(30);
   V_DESCULTREG              VARCHAR(200);
   V_SEMOVIMENTO             VARCHAR2(10);
   V_CPFCONTADOR             VARCHAR2(20);
   V_NRCRCCONT2              VARCHAR2(20);
   V_SGESTADO                VARCHAR2(10);
   V_DDD                     VARCHAR2(10);
   V_NUMTELEFONE             VARCHAR2(25);
   V_EMAIL                   VARCHAR2(60); 
   
   V_DATAINICIAL             DATE;
   V_DATAFINAL               DATE;
BEGIN

   BEGIN
      SELECT NRINSJURPROJ
        INTO V_CNPJSCP
        FROM PROJETOSCP
       WHERE NRSEQPROJETO = P_NRSEQPROJETO;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_CNPJSCP := NULL;
   END;

   
   IF NVL(P_TIPOEVENTO,0) > 0 AND (P_TIPOEVENTO NOT IN ('1','2','3','5') AND (P_TIPOEVENTO IN ('4','6') AND P_DTEVENTO <> P_DTFINAL)) THEN
      V_COUNTEV := 2;
   END IF;

   V_GEROUCABECALHO := 'N';
   V_GEROUIRPJ := 'N';
   V_GEROUCSLL := 'N';
   V_GEROUIRRF := 'N';
   V_GEROUIPI  := 'N';
   V_GEROUPIS  := 'N';
   V_GEROUCOFINS := 'N';

   V_CONTDEBITO := 1;
   
   V_DESCULTREG := NULL;
   IF P_IDQUALIFICAOPJ <> '11' THEN
      FOR V_COUNT IN 1..V_COUNTEV LOOP
         IF (V_COUNT = 1) AND (V_COUNTEV = 2) THEN
            V_DATAINICIAL := P_DTINICIAL;
            V_DATAFINAL   := P_DTEVENTO;
         ELSE
            IF (V_COUNT = 1) AND (V_COUNTEV = 1) THEN
               V_DATAINICIAL := P_DTINICIAL;
               V_DATAFINAL   := P_DTFINAL;
            ELSE
               IF (V_COUNT = 2) THEN
                  V_DATAINICIAL := P_DTEVENTO + 1;
                  V_DATAFINAL   := P_DTFINAL;
               END IF;
            END IF;
         END IF;

         FOR C_IRPJ IN (SELECT REPLACE(LTRIM(TO_CHAR(ROUND(VRDEBITO,2), '999999999990.00')), ',', '.') AS VRDEBITO,
                               CDRECEITACONTR, NRSEQUMAPAPU, IDPERIODICREC, IDDEBSCPINC, DTFIMPERIODO, DTINIPERIODO
                          FROM (SELECT SUM(ROUND(VRTOTAIMPOS,2)) AS VRDEBITO, RPAD(NVL(CDRECEITACONTR||CDVARIACAO,'000000'),6,'0') AS CDRECEITACONTR,
                                      NRSEQUMAPAPU, NVL(IDPERIODICREC,'M') AS IDPERIODICREC, IDDEBSCPINC, DTFIMPERIODO, DTINIPERIODO
                                  FROM MAPAAPURAC MP
                                 WHERE MP.CDEMPRESA     = P_CDEMPRESA
                                   AND MP.CDIMPOSTO    = '25'
                                   AND MP.DTFIMPERIODO BETWEEN V_DATAINICIAL AND V_DATAFINAL
                                   AND ((TO_CHAR(TO_DATE(V_DATAFINAL),'MM') IN ('03','06','09','12') AND MP.IDTPMAPAAPURAC IN ('LPT','LRT')) OR
                                        (MP.IDTPMAPAAPURAC IN ('LPM','LRM')))
                                 GROUP BY RPAD(NVL(CDRECEITACONTR||CDVARIACAO,'000000'),6,'0'), NRSEQUMAPAPU, NVL(IDPERIODICREC,'M'), IDDEBSCPINC, DTFIMPERIODO, DTINIPERIODO)
                          WHERE VRDEBITO > 0
                          ORDER BY NRSEQUMAPAPU )LOOP

             V_TEMDADOS := 'S';

             
             IF (P_IDTRIBUTALUCRO = '1') AND (P_TIPOEVENTO NOT IN ('1','2','3','5') AND (P_TIPOEVENTO IN ('4','6') AND P_DTEVENTO <> P_DTFINAL)) THEN
                V_ENVIABALANCO := 'S';
                BEGIN
                   SELECT DECODE(NVL(IDBALSUSRED,'N'), 'N', 'false', 'true')
                     INTO V_IDBALSUSRED
                     FROM MAPAAPUIMPOFED
                    WHERE NRSEQUMAPAPU = C_IRPJ.NRSEQUMAPAPU;
                EXCEPTION
                   WHEN NO_DATA_FOUND THEN
                      V_IDBALSUSRED := 'false';
                END;
             END IF;

         

             
             IF C_IRPJ.IDPERIODICREC = 'D' THEN
                
                V_PADEBITO := TO_NUMBER(TO_CHAR(TO_DATE(C_IRPJ.DTFIMPERIODO),'DD'));
             ELSE
                
                IF C_IRPJ.IDPERIODICREC = 'X' THEN
                   IF TO_CHAR(C_IRPJ.DTFIMPERIODO,'DD') = '10' THEN
                      V_PADEBITO := 1;
                   ELSE
                      IF TO_CHAR(C_IRPJ.DTFIMPERIODO,'DD') = '20' THEN
                         V_PADEBITO := 2;
                      ELSE
                         V_PADEBITO := 3;
                      END IF;
                   END IF;
                ELSE
                  
                  IF C_IRPJ.IDPERIODICREC = 'Q' THEN
                     IF TO_CHAR(C_IRPJ.DTFIMPERIODO,'DD') = '15' THEN
                        V_PADEBITO := 1;
                     ELSE
                        V_PADEBITO := 2;
                     END IF;
                  ELSE
                     V_PADEBITO := 0;
                  END IF;
                END IF;
             END IF;

             IF V_GEROUCABECALHO = 'N' THEN
                V_CONT := NVL(V_CONT,0)+1;
                IF (V_ENVIABALANCO = 'S') THEN
                   INSERT INTO ARQMAGNETICO
                      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                   VALUES
                      (LPAD(V_CONT,10,'0'),',"Debitos":{
                                              "BalancoLucroReal":'||V_IDBALSUSRED||',',P_NRSEQARQMAG);
                ELSE
                   INSERT INTO ARQMAGNETICO
                      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                   VALUES
                      (LPAD(V_CONT,10,'0'),',"Debitos":{',P_NRSEQARQMAG);

                END IF;

                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"Irpj":{
                                            "ListaDebitos":[',P_NRSEQARQMAG);

                V_GEROUCABECALHO := 'S';
             END IF;

             
             IF (V_COUNT = 2) AND (C_IRPJ.DTINIPERIODO||C_IRPJ.DTFIMPERIODO <> P_DTINICIAL||P_DTFINAL) THEN

                
                IF V_DESCULTREG IS NOT NULL THEN
                   UPDATE ARQMAGNETICO
                      SET DSREGARQMAG = V_DESCULTREG
                    WHERE NRORDREGARQ = V_CONTADORULTREG;
                END IF;

                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'],
                                           "ListaDebitosAposEvento":[',P_NRSEQARQMAG);
             END IF;

             V_CONT := NVL(V_CONT,0)+1;
             INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
                (LPAD(V_CONT,10,'0'),'{
                                        "IdDebito":'||V_CONTDEBITO||',',P_NRSEQARQMAG);

             IF (P_TIPOEVENTO <> '0') AND (V_COUNT = 1) THEN
                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"IdEventoDebito":'||P_TIPOEVENTO||',',P_NRSEQARQMAG);
             END IF;

             V_CONT := NVL(V_CONT,0)+1;
             INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
                (LPAD(V_CONT,10,'0'),'"CodigoDebito":"'||C_IRPJ.CDRECEITACONTR||'",',P_NRSEQARQMAG);

             IF (V_PADEBITO > 0) THEN
                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"PaDebito":'||V_PADEBITO||',',P_NRSEQARQMAG);
             END IF;

           

             IF (C_IRPJ.IDDEBSCPINC = '1') AND (V_CNPJSCP IS NOT NULL) THEN
                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"CnpjScp":"'||TO_NUMBER(V_CNPJSCP)||'",',P_NRSEQARQMAG);
             END IF;

             V_CONT := NVL(V_CONT,0)+1;
             INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
                (LPAD(V_CONT,10,'0'),'"ValorDebito":'||C_IRPJ.VRDEBITO||'},',P_NRSEQARQMAG);

             V_CONTDEBITO := V_CONTDEBITO + 1;
             V_CONTADORULTREG := LPAD(V_CONT,10,'0');
             V_DESCULTREG := '"ValorDebito":'||C_IRPJ.VRDEBITO||'}';
             V_GEROUIRPJ := 'S';
         END LOOP;
      END LOOP;
   END IF;

   IF V_GEROUIRPJ = 'S' THEN
      
      IF V_DESCULTREG IS NOT NULL THEN
         UPDATE ARQMAGNETICO
            SET DSREGARQMAG = V_DESCULTREG
          WHERE NRORDREGARQ = V_CONTADORULTREG;
      END IF;

      
      V_CONT := NVL(V_CONT,0)+1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(V_CONT,10,'0'),']}',P_NRSEQARQMAG);
   END IF;

   V_DESCULTREG := NULL;
   
   IF (P_IDQUALIFICAOPJ <> '11') AND (P_IDTRIBUTALUCRO <> '7') THEN
      V_GEROUCABECALHO := 'N';
      FOR V_COUNT IN 1..V_COUNTEV LOOP
         IF (V_COUNT = 1) AND (V_COUNTEV = 2) THEN
            V_DATAINICIAL := P_DTINICIAL;
            V_DATAFINAL   := P_DTEVENTO;
         ELSE
            IF (V_COUNT = 1) AND (V_COUNTEV = 1) THEN
               V_DATAINICIAL := P_DTINICIAL;
               V_DATAFINAL   := P_DTFINAL;
            ELSE
               IF (V_COUNT = 2) THEN
                  V_DATAINICIAL := P_DTEVENTO + 1;
                  V_DATAFINAL   := P_DTFINAL;
               END IF;
            END IF;
         END IF;

         FOR C_CSLL IN (SELECT REPLACE(LTRIM(TO_CHAR(ROUND(VRDEBITO,2), '999999999990.00')), ',', '.') AS VRDEBITO,
                               CDRECEITACONTR, IDPERIODICREC, IDDEBSCPINC, DTFIMPERIODO, NRSEQUMAPAPU, DTINIPERIODO
                          FROM (SELECT SUM(ROUND(VRTOTAIMPOS,2)) AS VRDEBITO,
                                       RPAD(NVL(CDRECEITACONTR||CDVARIACAO,'000000'),6,'0') AS CDRECEITACONTR, IDPERIODICREC, IDDEBSCPINC, DTFIMPERIODO,
                                       NRSEQUMAPAPU, DTINIPERIODO
                                     FROM MAPAAPURAC MP
                                    WHERE MP.CDEMPRESA    = P_CDEMPRESA
                                      AND MP.CDIMPOSTO    = '70'
                                      AND MP.DTFIMPERIODO BETWEEN V_DATAINICIAL AND V_DATAFINAL
                                      AND ((TO_CHAR(TO_DATE(V_DATAFINAL),'MM') IN ('03','06','09','12') AND MP.IDTPMAPAAPURAC IN ('LPT','LRT')) OR
                                           (MP.IDTPMAPAAPURAC IN ('LPM','LRM')))
                                    GROUP BY RPAD(NVL(CDRECEITACONTR||CDVARIACAO,'000000'),6,'0'), IDPERIODICREC, IDDEBSCPINC, DTFIMPERIODO, NRSEQUMAPAPU, DTINIPERIODO)
                          WHERE VRDEBITO > 0
                          ORDER BY NRSEQUMAPAPU)LOOP

             V_TEMDADOS := 'S';

            

            
             IF C_CSLL.IDPERIODICREC = 'D' THEN
                
                V_PADEBITO := TO_NUMBER(TO_CHAR(TO_DATE(C_CSLL.DTFIMPERIODO),'DD'));
             ELSE
                
                IF C_CSLL.IDPERIODICREC = 'X' THEN
                   IF TO_CHAR(C_CSLL.DTFIMPERIODO,'DD') = '10' THEN
                      V_PADEBITO := 1;
                   ELSE
                      IF TO_CHAR(C_CSLL.DTFIMPERIODO,'DD') = '20' THEN
                         V_PADEBITO := 2;
                      ELSE
                         V_PADEBITO := 3;
                      END IF;
                   END IF;
                ELSE
                  
                  IF C_CSLL.IDPERIODICREC = 'Q' THEN
                     IF TO_CHAR(C_CSLL.DTFIMPERIODO,'DD') = '15' THEN
                        V_PADEBITO := 1;
                     ELSE
                        V_PADEBITO := 2;
                     END IF;
                  ELSE
                     V_PADEBITO := 0;
                  END IF;
                END IF;
             END IF;

             IF (V_GEROUCABECALHO = 'N')  THEN
                 
                IF V_GEROUIRPJ = 'N' THEN
                   V_CONT := NVL(V_CONT,0)+1;
                   INSERT INTO ARQMAGNETICO
                      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                   VALUES
                      (LPAD(V_CONT,10,'0'),',"Debitos":{',P_NRSEQARQMAG);
                END IF;

                
                IF V_GEROUIRPJ = 'S' THEN
                   V_CONT := NVL(V_CONT,0)+1;
                   INSERT INTO ARQMAGNETICO
                      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                   VALUES
                      (LPAD(V_CONT,10,'0'),',',P_NRSEQARQMAG);
                END IF;

                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"Csll":{
                                            "ListaDebitos":[',P_NRSEQARQMAG);

                V_GEROUCABECALHO := 'S';
             END IF;

             IF (V_COUNT = 2) AND (C_CSLL.DTINIPERIODO||C_CSLL.DTFIMPERIODO <> P_DTINICIAL||P_DTFINAL) THEN

                
                IF V_DESCULTREG IS NOT NULL THEN
                   UPDATE ARQMAGNETICO
                      SET DSREGARQMAG = V_DESCULTREG
                    WHERE NRORDREGARQ = V_CONTADORULTREG;
                END IF;

                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'],
                                           "ListaDebitosAposEvento":[',P_NRSEQARQMAG);
             END IF;

             V_CONT := NVL(V_CONT,0)+1;
             INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
                (LPAD(V_CONT,10,'0'),'{
                                        "IdDebito":'||V_CONTDEBITO||',',P_NRSEQARQMAG);

             IF (P_TIPOEVENTO <> '0') AND (V_COUNT = 1) THEN
                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"IdEventoDebito":'||P_TIPOEVENTO||',',P_NRSEQARQMAG);
             END IF;

             V_CONT := NVL(V_CONT,0)+1;
             INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
                (LPAD(V_CONT,10,'0'),'"CodigoDebito":"'||C_CSLL.CDRECEITACONTR||'",',P_NRSEQARQMAG);

             IF (V_PADEBITO > 0) THEN
                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"PaDebito":'||V_PADEBITO||',',P_NRSEQARQMAG);
             END IF;

             V_CONT := NVL(V_CONT,0)+1;
             INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
                (LPAD(V_CONT,10,'0'),'"'||V_DESCRANO||'":'||TO_CHAR(P_DTFINAL,'YYYY')||',',P_NRSEQARQMAG);

             IF (C_CSLL.IDDEBSCPINC = '1') AND (V_CNPJSCP IS NOT NULL) THEN
                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"CnpjScp":"'||TO_NUMBER(V_CNPJSCP)||'",',P_NRSEQARQMAG);
             END IF;

             V_CONT := NVL(V_CONT,0)+1;
             INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
                (LPAD(V_CONT,10,'0'),'"ValorDebito":'||C_CSLL.VRDEBITO||'},',P_NRSEQARQMAG);

             V_CONTDEBITO := V_CONTDEBITO + 1;
             V_CONTADORULTREG := LPAD(V_CONT,10,'0');
             V_DESCULTREG := '"ValorDebito":'||C_CSLL.VRDEBITO||'}';
             V_GEROUCSLL := 'S';
         END LOOP;
      END LOOP;   
   END IF;

   IF V_GEROUCSLL = 'S' THEN
      
      IF V_DESCULTREG IS NOT NULL THEN
         UPDATE ARQMAGNETICO
            SET DSREGARQMAG = V_DESCULTREG
          WHERE NRORDREGARQ = V_CONTADORULTREG;
      END IF;

      
      V_CONT := NVL(V_CONT,0)+1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(V_CONT,10,'0'),']}',P_NRSEQARQMAG);
   END IF;

   V_DESCULTREG := NULL;
   
   IF (P_IDQUALIFICAOPJ NOT IN ('2','3','4','5','6')) AND (P_IDTRIBUTALUCRO <> '7') THEN
      V_GEROUCABECALHO := 'N';
      FOR V_COUNT IN 1..V_COUNTEV LOOP
          IF (V_COUNT = 1) AND (V_COUNTEV = 2) THEN
             V_DATAINICIAL := P_DTINICIAL;
             V_DATAFINAL   := P_DTEVENTO;
          ELSE
             IF (V_COUNT = 1) AND (V_COUNTEV = 1) THEN
                V_DATAINICIAL := P_DTINICIAL;
                V_DATAFINAL   := P_DTFINAL;
             ELSE
                IF (V_COUNT = 2) THEN
                   V_DATAINICIAL := P_DTEVENTO + 1;
                   V_DATAFINAL   := P_DTFINAL;
                END IF;
             END IF;
          END IF;

         FOR C_IRRF IN (SELECT REPLACE(LTRIM(TO_CHAR(ROUND(VRDEBITO,2), '999999999990.00')), ',', '.') AS VRDEBITO,
                               CDRECEITACONTR, IDPERIODICREC, IDDEBSCPINC, DTFIMPERIODO, NRSEQUMAPAPU, DTINIPERIODO
                          FROM (SELECT SUM(VRDEBITO) AS VRDEBITO, RPAD(NVL(CDRECEITACONTR||CDVARIACAO,'000000'),6,'0') AS CDRECEITACONTR,
                                       IDPERIODICREC, IDDEBSCPINC, DTFIMPERIODO, NRSEQUMAPAPU, DTINIPERIODO
                                         FROM (SELECT ROUND(M.VRTOTAIMPOS,2) AS VRDEBITO,
                                                      NVL(M.CDRECEITACONTR,M.CDRECEGUIA) AS CDRECEITACONTR,
                                                      M.CDVARIACAO, M.IDPERIODICREC, M.IDDEBSCPINC, DTFIMPERIODO, NRSEQUMAPAPU, DTINIPERIODO
                                                 FROM MAPAAPURAC M
                                                WHERE M.CDEMPRESA    = P_CDEMPRESA
                                                  AND M.CDIMPOSTO    = '21'
                                                  AND M.DTFIMPERIODO BETWEEN V_DATAINICIAL AND V_DATAFINAL
                                                UNION ALL
                                               SELECT ROUND(G.VRRECEITAGUIA,2) AS VRDEBITO,
                                                      SUBSTR(RPAD(REPLACE(REPLACE(REPLACE(REPLACE(G.CDRECEITAGUIA, '.'), '-'), '/'), ' '),6,'0'),1,4) AS CDRECEITACONTR,
                                                      SUBSTR(RPAD(REPLACE(REPLACE(REPLACE(REPLACE(G.CDRECEITAGUIA, '.'), '-'), '/'), ' '),6,'0'),5,2) AS CDVARIACAO,
                                                      M.IDPERIODICREC, M.IDDEBSCPINC, DTFIMPERIODO, M.NRSEQUMAPAPU, DTINIPERIODO
                                                 FROM MAPAAPURAC M, GUIACOMPLMAPA G
                                                WHERE M.CDEMPRESA    = P_CDEMPRESA
                                                  AND M.CDIMPOSTO    = '21'
                                                  AND M.DTFIMPERIODO BETWEEN V_DATAINICIAL AND V_DATAFINAL
                                                  AND M.NRSEQUMAPAPU = G.NRSEQUMAPAPU)
                                        GROUP BY RPAD(NVL(CDRECEITACONTR||CDVARIACAO,'000000'),6,'0'), IDPERIODICREC, IDDEBSCPINC, DTFIMPERIODO, NRSEQUMAPAPU, DTINIPERIODO)
                          WHERE VRDEBITO > 0
                          ORDER BY NRSEQUMAPAPU)LOOP

             V_TEMDADOS := 'S';

             
             IF C_IRRF.IDPERIODICREC = 'D' THEN
                
                V_PADEBITO := TO_NUMBER(TO_CHAR(TO_DATE(C_IRRF.DTFIMPERIODO),'DD'));
             ELSE
                
                IF C_IRRF.IDPERIODICREC = 'X' THEN
                   IF TO_CHAR(C_IRRF.DTFIMPERIODO,'DD') = '10' THEN
                      V_PADEBITO := 1;
                   ELSE
                      IF TO_CHAR(C_IRRF.DTFIMPERIODO,'DD') = '20' THEN
                         V_PADEBITO := 2;
                      ELSE
                         V_PADEBITO := 3;
                      END IF;
                   END IF;   
                ELSE
                  
                  IF C_IRRF.IDPERIODICREC = 'Q' THEN
                     IF TO_CHAR(C_IRRF.DTFIMPERIODO,'DD') = '15' THEN
                        V_PADEBITO := 1;
                     ELSE
                        V_PADEBITO := 2;
                     END IF;
                  ELSE
                     V_PADEBITO := 0;
                  END IF;
                END IF;
             END IF;

             IF (V_GEROUCABECALHO = 'N')  THEN
                 
                IF (V_GEROUIRPJ = 'N') AND (V_GEROUCSLL = 'N') THEN
                   V_CONT := NVL(V_CONT,0)+1;
                   INSERT INTO ARQMAGNETICO
                      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                   VALUES
                      (LPAD(V_CONT,10,'0'),',"Debitos":{',P_NRSEQARQMAG);
                END IF;

                
                IF (V_GEROUIRPJ = 'S') OR (V_GEROUCSLL = 'S') THEN
                   V_CONT := NVL(V_CONT,0)+1;
                   INSERT INTO ARQMAGNETICO
                      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                   VALUES
                      (LPAD(V_CONT,10,'0'),',',P_NRSEQARQMAG);
                END IF;

                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"Irrf":{
                                            "ListaDebitos":[',P_NRSEQARQMAG);

                V_GEROUCABECALHO := 'S';
             END IF;

             IF (V_COUNT = 2) AND (C_IRRF.DTINIPERIODO||C_IRRF.DTFIMPERIODO <> P_DTINICIAL||P_DTFINAL) THEN

                
                IF V_DESCULTREG IS NOT NULL THEN
                   UPDATE ARQMAGNETICO
                      SET DSREGARQMAG = V_DESCULTREG
                    WHERE NRORDREGARQ = V_CONTADORULTREG;
                END IF;

                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'],
                                           "ListaDebitosAposEvento":[',P_NRSEQARQMAG);
             END IF;
             
             V_CONT := NVL(V_CONT,0)+1;
             INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
                (LPAD(V_CONT,10,'0'),'{
                                        "IdDebito":'||V_CONTDEBITO||',',P_NRSEQARQMAG);

             IF (P_TIPOEVENTO <> '0') AND (V_COUNT = 1) THEN
                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"IdEventoDebito":'||P_TIPOEVENTO||',',P_NRSEQARQMAG);
             END IF;

             V_CONT := NVL(V_CONT,0)+1;
             INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
                (LPAD(V_CONT,10,'0'),'"CodigoDebito":"'||C_IRRF.CDRECEITACONTR||'",',P_NRSEQARQMAG);

             IF (V_PADEBITO > 0) THEN
                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"PaDebito":'||V_PADEBITO||',',P_NRSEQARQMAG);
             END IF;

             V_CONT := NVL(V_CONT,0)+1;
             INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
                (LPAD(V_CONT,10,'0'),'"ValorDebito":'||C_IRRF.VRDEBITO||'},',P_NRSEQARQMAG);

             V_CONTDEBITO := V_CONTDEBITO + 1;
             V_CONTADORULTREG := LPAD(V_CONT,10,'0');
             V_DESCULTREG := '"ValorDebito":'||C_IRRF.VRDEBITO||'}';
             V_GEROUIRRF:= 'S';
         END LOOP;
      END LOOP;      
   END IF;

   IF V_GEROUIRRF = 'S' THEN
      
      IF V_DESCULTREG IS NOT NULL THEN
         UPDATE ARQMAGNETICO
            SET DSREGARQMAG = V_DESCULTREG
          WHERE NRORDREGARQ = V_CONTADORULTREG;
      END IF;

      
      V_CONT := NVL(V_CONT,0)+1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(V_CONT,10,'0'),']}',P_NRSEQARQMAG);
   END IF;

   V_DESCULTREG := NULL;
   
   IF (P_IDQUALIFICAOPJ NOT IN ('2','3','4','5','6')) AND (P_IDTRIBUTALUCRO <> '7') THEN
      V_GEROUCABECALHO  := 'N';
      V_GEROUCABEAPOSEV := 'N';

      FOR V_COUNT IN 1..V_COUNTEV LOOP
         IF (V_COUNT = 1) AND (V_COUNTEV = 2) THEN
            V_DATAINICIAL := P_DTINICIAL;
            V_DATAFINAL   := P_DTEVENTO;
         ELSE
            IF (V_COUNT = 1) AND (V_COUNTEV = 1) THEN
               V_DATAINICIAL := P_DTINICIAL;
               V_DATAFINAL   := P_DTFINAL;
            ELSE
               IF (V_COUNT = 2) THEN
                  V_DATAINICIAL := P_DTEVENTO + 1;
                  V_DATAFINAL   := P_DTFINAL;
               END IF;
            END IF;
         END IF;

         FOR C_IPI IN (SELECT (VRDEBITO - VRCREDITO) AS IMPOSTO, CDEMPRESA, NRINSJURIEST,
                                  RPAD(NVL(CDRECEITACONTR||CDVARIACAO,'000000'),6,'0') AS CDRECEITACONTR, IDPERIODICREC, IDDEBSCPINC, DTFIMPERIODO, DTINIPERIODO
                             FROM (SELECT SUM(CASE WHEN NF.IDENTRSAIDOP = 'E' THEN IMP.VRIMPOSTO ELSE 0 END) AS VRCREDITO,
                                          SUM(CASE WHEN NF.IDENTRSAIDOP = 'S' THEN IMP.VRIMPOSTO ELSE 0 END) AS VRDEBITO,
                                          NF.CDEMPRESA, IE.NRINSJURIEST, E.NRINSJUREMPR, IMP.CDRECEITACONTR,
                                          IMP.CDVARIACAO, IMP.IDPERIODICREC, IMP.IDDEBSCPINC, DTFIMPERIODO, DTINIPERIODO
                                     FROM NOTAFISCAL NF, EMPRESA E, INSCRESTAD IE,
                                          (SELECT CASE I.CDFISCALNF WHEN '1' THEN I.VRIMPOSTONF ELSE 0 END AS VRIMPOSTO, M.NRSEQUMAPAPU, I.CDFILIAL, 
                                                  I.NRLANCTONF, M.CDEMPRESA, NVL(M.CDRECEITACONTR,M.CDRECEGUIA) AS CDRECEITACONTR,
                                                  M.CDVARIACAO, M.IDPERIODICREC, M.IDDEBSCPINC, DTFIMPERIODO, DTINIPERIODO
                                             FROM MAPAAPURAC M, IMPOSTONF I, IMPOSTO IM
                                            WHERE M.CDIMPOSTO = IM.CDIMPOSTONFE
                                              AND I.CDIMPOSTO = IM.CDIMPOSTO
                                              AND M.CDIMPOSTO = '10'
                                              AND ((I.NRSEQUMAPAPU  = M.NRSEQUMAPAPU) OR (M.NRSEQUMAPAPU = I.NRSEQMAPOIMP))
                                              AND M.DTFIMPERIODO BETWEEN V_DATAINICIAL AND V_DATAFINAL) IMP
                                    WHERE NF.CDFILIAL   = IMP.CDFILIAL
                                      AND NF.NRLANCTONF = IMP.NRLANCTONF
                                      AND NF.CDEMPRESA  = E.CDEMPRESA
                                      AND IMP.CDEMPRESA = NF.CDEMPRESA
                                      AND IMP.CDEMPRESA = P_CDEMPRESA
                                      AND NF.CDINSCESTA = IE.CDINSCESTA
                                      AND IE.CDEMPRESA  = E.CDEMPRESA
                                    GROUP BY NF.CDEMPRESA, IE.NRINSJURIEST, E.NRINSJUREMPR, CDRECEITACONTR,
                                          CDVARIACAO, IDPERIODICREC, IDDEBSCPINC, DTFIMPERIODO, DTINIPERIODO
                                    ORDER BY IE.NRINSJURIEST) )LOOP
             
             IF C_IPI.IMPOSTO > 0 THEN
                V_TEMDADOS := 'S';

                
                IF C_IPI.IDPERIODICREC = 'D' THEN
                   
                   V_PADEBITO := TO_NUMBER(TO_CHAR(TO_DATE(C_IPI.DTFIMPERIODO),'DD'));
                ELSE
                   
                   IF C_IPI.IDPERIODICREC = 'X' THEN
                      IF TO_CHAR(C_IPI.DTFIMPERIODO,'DD') = '10' THEN
                         V_PADEBITO := 1;
                      ELSE
                         IF TO_CHAR(C_IPI.DTFIMPERIODO,'DD') = '20' THEN
                            V_PADEBITO := 2;
                         ELSE
                            V_PADEBITO := 3;
                         END IF;
                      END IF;   
                   ELSE
                     
                     IF C_IPI.IDPERIODICREC = 'Q' THEN
                        IF TO_CHAR(C_IPI.DTFIMPERIODO,'DD') = '15' THEN
                           V_PADEBITO := 1;
                        ELSE
                           V_PADEBITO := 2;
                       END IF;
                     ELSE
                        V_PADEBITO := 0;
                     END IF;
                   END IF;
                END IF;

                IF (V_GEROUCABECALHO = 'N')  THEN
                    
                   IF (V_GEROUIRPJ = 'N') AND (V_GEROUCSLL = 'N') AND (V_GEROUIRRF = 'N') THEN
                      V_CONT := NVL(V_CONT,0)+1;
                      INSERT INTO ARQMAGNETICO
                         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                      VALUES
                         (LPAD(V_CONT,10,'0'),',"Debitos":{',P_NRSEQARQMAG);
                   END IF;

                   
                   IF (V_GEROUIRPJ = 'S') OR (V_GEROUCSLL = 'S') AND (V_GEROUIRRF = 'S') THEN
                      V_CONT := NVL(V_CONT,0)+1;
                      INSERT INTO ARQMAGNETICO
                         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                      VALUES
                         (LPAD(V_CONT,10,'0'),',',P_NRSEQARQMAG);
                   END IF;

                   V_CONT := NVL(V_CONT,0)+1;
                   INSERT INTO ARQMAGNETICO
                      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                   VALUES
                      (LPAD(V_CONT,10,'0'),'"Ipi":{
                                               "ListaDebitos":[',P_NRSEQARQMAG);

                   V_GEROUCABECALHO := 'S';
                END IF;

                IF (V_COUNT = 2) AND (C_IPI.DTINIPERIODO||C_IPI.DTFIMPERIODO <> P_DTINICIAL||P_DTFINAL) AND V_GEROUCABEAPOSEV = 'N' THEN
                   V_GEROUCABEAPOSEV := 'S';
                   
                   IF V_DESCULTREG IS NOT NULL THEN
                      UPDATE ARQMAGNETICO
                         SET DSREGARQMAG = V_DESCULTREG
                       WHERE NRORDREGARQ = V_CONTADORULTREG;
                   END IF;

                   V_CONT := NVL(V_CONT,0)+1;
                   INSERT INTO ARQMAGNETICO
                      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                   VALUES
                      (LPAD(V_CONT,10,'0'),'],
                                              "ListaDebitosAposEvento":[',P_NRSEQARQMAG);
                END IF;

                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'{
                                           "IdDebito":'||V_CONTDEBITO||',',P_NRSEQARQMAG);

                IF (P_TIPOEVENTO <> '0') AND (V_COUNT = 1) THEN
                   V_CONT := NVL(V_CONT,0)+1;
                   INSERT INTO ARQMAGNETICO
                      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                   VALUES
                      (LPAD(V_CONT,10,'0'),'"IdEventoDebito":'||P_TIPOEVENTO||',',P_NRSEQARQMAG);
                END IF;

                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"CodigoDebito":"'||C_IPI.CDRECEITACONTR||'",',P_NRSEQARQMAG);

                IF (V_PADEBITO > 0) THEN
                   V_CONT := NVL(V_CONT,0)+1;
                   INSERT INTO ARQMAGNETICO
                      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                   VALUES
                      (LPAD(V_CONT,10,'0'),'"PaDebito":'||V_PADEBITO||',',P_NRSEQARQMAG);
                END IF;

                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"CnpjEstabelecimento":"'||SUBSTR(C_IPI.NRINSJURIEST,9,14)||'",',P_NRSEQARQMAG);

                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"ValorDebito":'||C_IPI.IMPOSTO||'},',P_NRSEQARQMAG);

                V_CONTDEBITO := V_CONTDEBITO + 1;
                V_CONTADORULTREG := LPAD(V_CONT,10,'0');
                V_DESCULTREG := '"ValorDebito":'||C_IPI.IMPOSTO||'}';
                V_GEROUIPI := 'S';
             END IF;
         END LOOP;
      END LOOP;
   END IF;

   IF V_GEROUIPI = 'S' THEN
      
      IF V_DESCULTREG IS NOT NULL THEN
         UPDATE ARQMAGNETICO
            SET DSREGARQMAG = V_DESCULTREG
          WHERE NRORDREGARQ = V_CONTADORULTREG;
      END IF;

      
      V_CONT := NVL(V_CONT,0)+1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(V_CONT,10,'0'),']}',P_NRSEQARQMAG);
   END IF;

   V_DESCULTREG := NULL;
   
   V_GEROUCABECALHO := 'N';
   FOR V_COUNT IN 1..V_COUNTEV LOOP
      IF (V_COUNT = 1) AND (V_COUNTEV = 2) THEN
         V_DATAINICIAL := P_DTINICIAL;
         V_DATAFINAL   := P_DTEVENTO;
      ELSE
         IF (V_COUNT = 1) AND (V_COUNTEV = 1) THEN
            V_DATAINICIAL := P_DTINICIAL;
            V_DATAFINAL   := P_DTFINAL;
         ELSE
            IF (V_COUNT = 2) THEN
               V_DATAINICIAL := P_DTEVENTO + 1;
               V_DATAFINAL   := P_DTFINAL;
            END IF;
         END IF;
      END IF;
      
      FOR C_PIS IN (SELECT REPLACE(LTRIM(TO_CHAR(ROUND(VRDEBITO,2), '999999999990.00')), ',', '.') AS VRDEBITO,
                           RPAD(NVL(CDRECEITACONTR||CDVARIACAO,'000000'),6,'0') AS CDRECEITACONTR, IDPERIODICREC, IDDEBSCPINC, DTFIMPERIODO,
                           NRSEQUMAPAPU, DTINIPERIODO
                          FROM (SELECT SUM(ROUND(NVL(G.VRRECEITAGUIA,MP.VRTOTAIMPOS),2)) AS VRDEBITO,
                                         NVL(G.CDRECEITAGUIA,NVL(MP.CDRECENCUMU,MP.CDRECECUMU)) AS CDRECEITACONTR,
                                         MP.CDVARIACAO, MP.IDPERIODICREC, MP.IDDEBSCPINC, DTFIMPERIODO, MP.NRSEQUMAPAPU, DTINIPERIODO
                                  FROM MAPAAPURAC MP, GUIACOMPAPUCONTR G
                                 WHERE MP.CDEMPRESA     = P_CDEMPRESA
                                   AND MP.CDIMPOSTO    = '50'
                                   AND MP.DTFIMPERIODO BETWEEN V_DATAINICIAL AND V_DATAFINAL
                                   AND G.NRSEQUMAPAPU(+) = MP.NRSEQUMAPAPU
                                GROUP BY MP.CDIMPOSTO, NVL(G.CDRECEITAGUIA,NVL(MP.CDRECENCUMU,MP.CDRECECUMU)),
                                      MP.CDVARIACAO, MP.IDPERIODICREC, MP.IDDEBSCPINC,
                                      DTFIMPERIODO, MP.NRSEQUMAPAPU, DTINIPERIODO)
                          WHERE VRDEBITO > 0
                          ORDER BY NRSEQUMAPAPU)LOOP
            V_TEMDADOS := 'S';

             
             IF C_PIS.IDPERIODICREC = 'D' THEN
                
                V_PADEBITO := TO_NUMBER(TO_CHAR(TO_DATE(C_PIS.DTFIMPERIODO),'DD'));
             ELSE
                
                IF C_PIS.IDPERIODICREC = 'X' THEN
                   IF TO_CHAR(C_PIS.DTFIMPERIODO,'DD') = '10' THEN
                      V_PADEBITO := 1;
                   ELSE
                      IF TO_CHAR(C_PIS.DTFIMPERIODO,'DD') = '20' THEN
                         V_PADEBITO := 2;
                      ELSE
                         V_PADEBITO := 3;
                      END IF;
                   END IF;   
                ELSE
                  
                  IF C_PIS.IDPERIODICREC = 'Q' THEN
                     IF TO_CHAR(C_PIS.DTFIMPERIODO,'DD') = '15' THEN
                        V_PADEBITO := 1;
                     ELSE
                        V_PADEBITO := 2;
                     END IF;
                  ELSE
                     V_PADEBITO := 0;
                  END IF;
                END IF;
             END IF;

             IF (V_GEROUCABECALHO = 'N')  THEN
                 
                IF (V_GEROUIRPJ = 'N') AND (V_GEROUCSLL = 'N') AND (V_GEROUIRRF = 'N') AND (V_GEROUIPI = 'N') THEN
                   V_CONT := NVL(V_CONT,0)+1;
                   INSERT INTO ARQMAGNETICO
                      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                   VALUES
                      (LPAD(V_CONT,10,'0'),',"Debitos":{',P_NRSEQARQMAG);
                END IF;

                
                IF (V_GEROUIRPJ = 'S') OR (V_GEROUCSLL = 'S') OR (V_GEROUIRRF = 'S') OR (V_GEROUIPI = 'S')  THEN
                   V_CONT := NVL(V_CONT,0)+1;
                   INSERT INTO ARQMAGNETICO
                      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                   VALUES
                      (LPAD(V_CONT,10,'0'),',',P_NRSEQARQMAG);
                END IF;

                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"PisPasep":{
                                            "ListaDebitos":[',P_NRSEQARQMAG);

                V_GEROUCABECALHO := 'S';
             END IF;

             IF (V_COUNT = 2) AND (C_PIS.DTINIPERIODO||C_PIS.DTFIMPERIODO <> P_DTINICIAL||P_DTFINAL) THEN

                
                IF V_DESCULTREG IS NOT NULL THEN
                   UPDATE ARQMAGNETICO
                      SET DSREGARQMAG = V_DESCULTREG
                    WHERE NRORDREGARQ = V_CONTADORULTREG;
                END IF;

                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'],
                                           "ListaDebitosAposEvento":[',P_NRSEQARQMAG);
             END IF;

             V_CONT := NVL(V_CONT,0)+1;
             INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
                (LPAD(V_CONT,10,'0'),'{
                                        "IdDebito":'||V_CONTDEBITO||',',P_NRSEQARQMAG);

             IF (P_TIPOEVENTO <> '0') AND (V_COUNT = 1) THEN
                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"IdEventoDebito":'||P_TIPOEVENTO||',',P_NRSEQARQMAG);
             END IF;

             V_CONT := NVL(V_CONT,0)+1;
             INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
                (LPAD(V_CONT,10,'0'),'"CodigoDebito":"'||C_PIS.CDRECEITACONTR||'",',P_NRSEQARQMAG);

             IF (V_PADEBITO > 0) THEN
                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"PaDebito":'||V_PADEBITO||',',P_NRSEQARQMAG);
             END IF;

             IF (C_PIS.IDDEBSCPINC = '1') AND (V_CNPJSCP IS NOT NULL) THEN
                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"CnpjScp":"'||TO_NUMBER(V_CNPJSCP)||'",',P_NRSEQARQMAG);
             END IF;

             V_CONT := NVL(V_CONT,0)+1;
             INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
                (LPAD(V_CONT,10,'0'),'"ValorDebito":'||C_PIS.VRDEBITO||'},',P_NRSEQARQMAG);

             V_CONTDEBITO := V_CONTDEBITO + 1;
             V_CONTADORULTREG := LPAD(V_CONT,10,'0');
             V_DESCULTREG := '"ValorDebito":'||C_PIS.VRDEBITO||'}';
             V_GEROUPIS:= 'S';
      END LOOP;
   END LOOP;   

   IF V_GEROUPIS = 'S' THEN
     
      IF V_DESCULTREG IS NOT NULL THEN
         UPDATE ARQMAGNETICO
            SET DSREGARQMAG = V_DESCULTREG
          WHERE NRORDREGARQ = V_CONTADORULTREG;
      END IF;

      
      V_CONT := NVL(V_CONT,0)+1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(V_CONT,10,'0'),']}',P_NRSEQARQMAG);
   END IF;

   
   V_GEROUCABECALHO := 'N';
   V_DESCULTREG := NULL;
   FOR V_COUNT IN 1..V_COUNTEV LOOP
      IF (V_COUNT = 1) AND (V_COUNTEV = 2) THEN
         V_DATAINICIAL := P_DTINICIAL;
         V_DATAFINAL   := P_DTEVENTO;
      ELSE
         IF (V_COUNT = 1) AND (V_COUNTEV = 1) THEN
            V_DATAINICIAL := P_DTINICIAL;
            V_DATAFINAL   := P_DTFINAL;
         ELSE
            IF (V_COUNT = 2) THEN
               V_DATAINICIAL := P_DTEVENTO + 1;
               V_DATAFINAL   := P_DTFINAL;
            END IF;
         END IF;
      END IF;
      
      FOR C_COFINS IN (SELECT REPLACE(LTRIM(TO_CHAR(ROUND(VRDEBITO,2), '999999999990.00')), ',', '.') AS VRDEBITO,
                           RPAD(NVL(CDRECEITACONTR||CDVARIACAO,'000000'),6,'0') AS CDRECEITACONTR, IDPERIODICREC, IDDEBSCPINC, DTFIMPERIODO,
                           NRSEQUMAPAPU, DTINIPERIODO
                          FROM (SELECT SUM(ROUND(NVL(G.VRRECEITAGUIA,MP.VRTOTAIMPOS),2)) AS VRDEBITO, 
                                            NVL(G.CDRECEITAGUIA,NVL(MP.CDRECENCUMU,MP.CDRECECUMU)) AS CDRECEITACONTR,
                                            MP.CDVARIACAO, MP.IDPERIODICREC, MP.IDDEBSCPINC, DTFIMPERIODO, MP.NRSEQUMAPAPU, DTINIPERIODO
                                       FROM EMPRESA E, MAPAAPURAC MP, GUIACOMPAPUCONTR G
                                      WHERE E.CDEMPRESA       = P_CDEMPRESA
                                        AND E.CDEMPRESA       = MP.CDEMPRESA
                                        AND MP.CDIMPOSTO      = '60'
                                        AND MP.DTFIMPERIODO   BETWEEN V_DATAINICIAL AND V_DATAFINAL
                                        AND G.NRSEQUMAPAPU(+) = MP.NRSEQUMAPAPU
                                      GROUP BY NVL(G.CDRECEITAGUIA,NVL(MP.CDRECENCUMU,MP.CDRECECUMU)),
                                            MP.CDVARIACAO, MP.IDPERIODICREC, MP.IDDEBSCPINC,
                                            DTFIMPERIODO, MP.NRSEQUMAPAPU, DTINIPERIODO)
                          WHERE VRDEBITO > 0
                          ORDER BY NRSEQUMAPAPU)LOOP
                       
             V_TEMDADOS := 'S';
             
             IF C_COFINS.IDPERIODICREC = 'D' THEN
                
                V_PADEBITO := TO_NUMBER(TO_CHAR(TO_DATE(C_COFINS.DTFIMPERIODO),'DD'));
             ELSE
                
                IF C_COFINS.IDPERIODICREC = 'X' THEN
                   IF TO_CHAR(C_COFINS.DTFIMPERIODO,'DD') = '10' THEN
                      V_PADEBITO := 1;
                   ELSE
                      IF TO_CHAR(C_COFINS.DTFIMPERIODO,'DD') = '20' THEN
                         V_PADEBITO := 2;
                      ELSE
                         V_PADEBITO := 3;
                      END IF;
                   END IF;   
                ELSE
                  
                  IF C_COFINS.IDPERIODICREC = 'Q' THEN
                     IF TO_CHAR(C_COFINS.DTFIMPERIODO,'DD') = '15' THEN
                        V_PADEBITO := 1;
                     ELSE
                        V_PADEBITO := 2;
                     END IF;
                  ELSE
                     V_PADEBITO := 0;
                  END IF;
                END IF;
             END IF;

             IF (V_GEROUCABECALHO = 'N')  THEN
                 
                IF (V_GEROUIRPJ = 'N') AND (V_GEROUCSLL = 'N') AND (V_GEROUIRRF = 'N') AND (V_GEROUIPI = 'N') AND (V_GEROUPIS = 'N') THEN
                   V_CONT := NVL(V_CONT,0)+1;
                   INSERT INTO ARQMAGNETICO
                      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                   VALUES
                      (LPAD(V_CONT,10,'0'),',"Debitos":{',P_NRSEQARQMAG);
                END IF;

                
                IF (V_GEROUIRPJ = 'S') OR (V_GEROUCSLL = 'S') OR (V_GEROUIRRF = 'S') OR (V_GEROUIPI = 'S') OR (V_GEROUPIS = 'S') THEN
                   V_CONT := NVL(V_CONT,0)+1;
                   INSERT INTO ARQMAGNETICO
                      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                   VALUES
                      (LPAD(V_CONT,10,'0'),',',P_NRSEQARQMAG);
                END IF;

                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"Cofins":{
                                            "ListaDebitos":[',P_NRSEQARQMAG);

                V_GEROUCABECALHO := 'S';
             END IF;

             IF (V_COUNT = 2) AND (C_COFINS.DTINIPERIODO||C_COFINS.DTFIMPERIODO <> P_DTINICIAL||P_DTFINAL) THEN

                
                IF V_DESCULTREG IS NOT NULL THEN
                   UPDATE ARQMAGNETICO
                      SET DSREGARQMAG = V_DESCULTREG
                    WHERE NRORDREGARQ = V_CONTADORULTREG;
                END IF;

                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'],
                                           "ListaDebitosAposEvento":[',P_NRSEQARQMAG);
             END IF;

             V_CONT := NVL(V_CONT,0)+1;
             INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
                (LPAD(V_CONT,10,'0'),'{
                                        "IdDebito":'||V_CONTDEBITO||',',P_NRSEQARQMAG);

             IF (P_TIPOEVENTO <> '0') AND (V_COUNT = 1) THEN
                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"IdEventoDebito":'||P_TIPOEVENTO||',',P_NRSEQARQMAG);
             END IF;

             V_CONT := NVL(V_CONT,0)+1;
             INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
                (LPAD(V_CONT,10,'0'),'"CodigoDebito":"'||C_COFINS.CDRECEITACONTR||'",',P_NRSEQARQMAG);

             IF (V_PADEBITO > 0) THEN
                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"PaDebito":'||V_PADEBITO||',',P_NRSEQARQMAG);
             END IF;

             IF (C_COFINS.IDDEBSCPINC = '1') AND (V_CNPJSCP IS NOT NULL) THEN
                V_CONT := NVL(V_CONT,0)+1;
                INSERT INTO ARQMAGNETICO
                   (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                VALUES
                   (LPAD(V_CONT,10,'0'),'"CnpjScp":"'||TO_NUMBER(V_CNPJSCP)||'",',P_NRSEQARQMAG);
             END IF;

             V_CONT := NVL(V_CONT,0)+1;
             INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
             VALUES
                (LPAD(V_CONT,10,'0'),'"ValorDebito":'||C_COFINS.VRDEBITO||'},',P_NRSEQARQMAG);

             V_CONTDEBITO := V_CONTDEBITO + 1;
             V_CONTADORULTREG := LPAD(V_CONT,10,'0');
             V_DESCULTREG := '"ValorDebito":'||C_COFINS.VRDEBITO||'}';
             V_GEROUCOFINS:= 'S';
      END LOOP;
   END LOOP;
   
   IF V_GEROUCOFINS = 'S' THEN
      
      IF V_DESCULTREG IS NOT NULL THEN
         UPDATE ARQMAGNETICO
            SET DSREGARQMAG = V_DESCULTREG
          WHERE NRORDREGARQ = V_CONTADORULTREG;
      END IF;

      
      V_CONT := NVL(V_CONT,0)+1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(V_CONT,10,'0'),']}',P_NRSEQARQMAG);
   END IF;

   
   IF (V_GEROUIRPJ = 'S') OR (V_GEROUCSLL = 'S') OR (V_GEROUIRRF = 'S') OR (V_GEROUIPI = 'S') OR (V_GEROUPIS = 'S') OR (V_GEROUCOFINS = 'S') THEN
      V_CONT := NVL(V_CONT,0)+1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(V_CONT,10,'0'),'}',P_NRSEQARQMAG);
   END IF;

   
   V_CONT := NVL(V_CONT,0)+1;
   INSERT INTO ARQMAGNETICO
      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
      (LPAD(V_CONT,10,'0'),'}',P_NRSEQARQMAG);

   BEGIN
      SELECT NRCPFRESPONS,
             C.NRCRCCONT2, C.SGESTADO,
             LPAD(NVL(SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.NRTELCONTATO,'.',''),'-',''),'/',''),' ',''),')',''),'(',''),1,2),' '),4),
             LPAD(NVL(SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.NRTELCONTATO,'.',''),'-',''),'/',''),' ',''),')',''),'(',''),3,9),' '),9),
             C.DSENDINTERN
        INTO V_CPFCONTADOR, V_NRCRCCONT2, V_SGESTADO, V_DDD, V_NUMTELEFONE, V_EMAIL
        FROM EMPRESA E, CONTADOR C, REPRESEMPRESA  R, REPRESLEGAL L
       WHERE E.CDEMPRESA   = P_CDEMPRESA
         AND E.CDCONTADOR2 = C.CDCONTADOR2
         AND E.CDEMPRESA   = R.CDEMPRESA
         AND R.IDATIVO     = 'S'
         and R.CDREPRES    = L.CDREPRES;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_CPFCONTADOR := NULL;
         V_NRCRCCONT2  := NULL;
         V_SGESTADO    := NULL;
         V_DDD         := NULL;
         V_NUMTELEFONE := NULL;
         V_EMAIL       := NULL;
   END;

   IF V_TEMDADOS = 'S' THEN
      V_SEMOVIMENTO := 'false';
   ELSE
      V_SEMOVIMENTO := 'true';
   END IF;
   
   INSERT INTO ARQMAGNETICO
      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
      (LPAD(V_CONT_DADOSINI,10,'0'),'"DadosIniciais": {
                                     "SemMovimento":'||V_SEMOVIMENTO||','||
                                    '"QualificacaoPj":'||P_IDQUALIFICAOPJ||',',P_NRSEQARQMAG);

   IF (V_SEMOVIMENTO = 'false') THEN
      IF (P_IDQUALIFICAOPJ <> '11') THEN
         V_CONT_DADOSINI := V_CONT_DADOSINI + 1;
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(V_CONT_DADOSINI,10,'0'),'"TributacaoLucro": '||P_IDTRIBUTALUCRO||',',P_NRSEQARQMAG);
      END IF;

      V_CONT_DADOSINI := V_CONT_DADOSINI + 1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(V_CONT_DADOSINI,10,'0'),'"VariacoesMonetarias": '||P_VARIACOESMONE||',',P_NRSEQARQMAG);

      IF (P_IDQUALIFICAOPJ = '9') OR
         (P_IDQUALIFICAOPJ IN ('4','8','10') AND P_IDTRIBUTALUCRO IN ('1','2','6')) OR
         (P_IDQUALIFICAOPJ IN ('1','12') AND P_IDTRIBUTALUCRO IN ('1','2','5','6')) THEN
         
         V_CONT_DADOSINI := V_CONT_DADOSINI + 1;
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            (LPAD(V_CONT_DADOSINI,10,'0'),'"RegimePisCofins": '||P_REGIMEPISCOFINS||',',P_NRSEQARQMAG);
      END IF;
   END IF;

   V_CONT_DADOSINI := V_CONT_DADOSINI + 1;
   INSERT INTO ARQMAGNETICO
      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
      (LPAD(V_CONT_DADOSINI,10,'0'),'"ResponsavelApuracao": {',P_NRSEQARQMAG);

   V_CONT_DADOSINI := V_CONT_DADOSINI + 1;
   INSERT INTO ARQMAGNETICO
      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
      (LPAD(V_CONT_DADOSINI,10,'0'),'"CpfResponsavel":"'||V_CPFCONTADOR||'",',P_NRSEQARQMAG);

   IF V_NUMTELEFONE IS NOT NULL THEN
      V_CONT_DADOSINI := V_CONT_DADOSINI + 1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(V_CONT_DADOSINI,10,'0'),'"TelResponsavel":{',P_NRSEQARQMAG); 

      V_CONT_DADOSINI := V_CONT_DADOSINI + 1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(V_CONT_DADOSINI,10,'0'),'"Ddd":"'||TO_NUMBER(V_DDD)||'",',P_NRSEQARQMAG);

      V_CONT_DADOSINI := V_CONT_DADOSINI + 1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(V_CONT_DADOSINI,10,'0'),'"NumTelefone":"'||TO_NUMBER(V_NUMTELEFONE)||'"}',P_NRSEQARQMAG);
   END IF;

   IF V_EMAIL IS NOT NULL THEN
      V_CONT_DADOSINI := V_CONT_DADOSINI + 1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(V_CONT_DADOSINI,10,'0'),',"EmailResponsavel":"'||V_EMAIL||'"',P_NRSEQARQMAG);
   END IF;

   IF V_NRCRCCONT2 IS NOT NULL THEN
      V_CONT_DADOSINI := V_CONT_DADOSINI + 1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(V_CONT_DADOSINI,10,'0'),',"RegistroCrc":{',P_NRSEQARQMAG);

      V_CONT_DADOSINI := V_CONT_DADOSINI + 1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(V_CONT_DADOSINI,10,'0'),'"UfRegistro":"'||V_SGESTADO||'",',P_NRSEQARQMAG);

      V_CONT_DADOSINI := V_CONT_DADOSINI + 1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(V_CONT_DADOSINI,10,'0'),'"NumRegistro":"'||V_NRCRCCONT2||'"}}}',P_NRSEQARQMAG);
   ELSE
      V_CONT_DADOSINI := V_CONT_DADOSINI + 1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (LPAD(V_CONT_DADOSINI,10,'0'),'}}',P_NRSEQARQMAG);
   END IF;
END;
/  
