CREATE OR REPLACE PROCEDURE ARQUIVO_DCIPSC(
   P_CDEMPRESA      IN NOTAFISCAL.CDEMPRESA%TYPE, 
   P_CDINSCESTA     IN INSCRESTAD.CDINSCESTA%TYPE,
   P_DTINICIAL      IN MAPAAPURAC.DTINIPERIODO%TYPE, 
   P_DTFINAL        IN MAPAAPURAC.DTFIMPERIODO%TYPE, 
   P_NRSEQARQMAG    IN ARQMAGNETICO.NRSEQARQMAG%TYPE)
AS
   V_NRSEQUMAPAPU      VARCHAR2(12);
   V_TOTARQUIVO        NUMBER;
   V_NRSAT             VARCHAR2(15);
   V_LINHA             VARCHAR2(5000);
   V_CONTADOR          NUMBER; 

BEGIN 
   
   BEGIN 
      SELECT NRSEQUMAPAPU 
        INTO V_NRSEQUMAPAPU 
        FROM MAPAAPURAC 
       WHERE CDEMPRESA    = P_CDEMPRESA 
         AND CDINSCESTA   = P_CDINSCESTA 
         AND CDIMPOSTO    = '00'
         AND DTINIPERIODO = P_DTINICIAL 
         AND DTFIMPERIODO = P_DTFINAL; 
   EXCEPTION 
      WHEN NO_DATA_FOUND THEN 
         V_NRSEQUMAPAPU := NULL; 
   END; 

   /*OUTROS CREDITOS*/
   FOR C_REG_040 IN (SELECT NVL(SUM(VRITEMMAPR),0) AS VRITEMMAPR,
                            CDCREDDCIP
                       FROM ITMAPARECUP
                      WHERE NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                        AND IDTIPOITMAPR IN('OC','ED','DD') 
                        AND CDTIPODCIP = '2'
                        AND CDCREDDCIP IS NOT NULL
                      GROUP BY CDCREDDCIP, CDCREDDCIP) LOOP

      BEGIN
         SELECT NVL(NRSAT,0)
           INTO V_NRSAT
           FROM CODIGODCIP
           WHERE CDTIPO  = '2'
             AND CDCREDITO = C_REG_040.CDCREDDCIP
             AND P_DTINICIAL >= DTINIVALID
             AND P_DTFINAL   <= NVL(DTFINVALID,P_DTFINAL);
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
             V_NRSAT   := 0;
       END;

       IF NVL(V_NRSAT,0) IN ('9','13') THEN
          V_NRSAT := TO_CHAR(P_DTFINAL,'mmyyyy');
       END IF;

       V_LINHA := REPLACE(REPLACE(REPLACE(SUBSTR(P_CDINSCESTA,1,9),'-',''),'.',''),'/','')||
                  TO_CHAR(P_DTFINAL,'mmyyyy')||
                  '040'||
                  LPAD(C_REG_040.CDCREDDCIP,3,'0')||
                  LTRIM(REPLACE(TO_CHAR(ABS(NVL(C_REG_040.VRITEMMAPR,0)),'000000000000000.00'),'.',''))||
                  LPAD(NVL(V_NRSAT,0), 15, '0');

       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         (LPAD('01',10,'0'), V_LINHA, P_NRSEQARQMAG);

       V_TOTARQUIVO := NVL(V_TOTARQUIVO,0)+1;
       V_LINHA := NULL;
   END LOOP;

   /*TOTALIZADOR OUTROS CREDITOS*/
   FOR C_REG_050 IN (SELECT NVL(SUM(VRITEMMAPR),0) AS VRITEMMAPR
                           FROM ITMAPARECUP
                          WHERE NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                            AND IDTIPOITMAPR IN('OC','ED','DD')
                            AND CDTIPODCIP = '2'
                            AND CDCREDDCIP IS NOT NULL) LOOP

       V_LINHA := REPLACE(REPLACE(REPLACE(SUBSTR(P_CDINSCESTA,1,9),'-',''),'.',''),'/','')||
                  TO_CHAR(P_DTFINAL,'mmyyyy')||
                  '050'||
                  LTRIM(REPLACE(TO_CHAR(ABS(NVL(C_REG_050.VRITEMMAPR,0)),'000000000000000.00'),'.',''));

       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         (LPAD('01',10,'0'), V_LINHA, P_NRSEQARQMAG);

       V_TOTARQUIVO := NVL(V_TOTARQUIVO,0)+1;
       V_LINHA := NULL;
   END LOOP;

  /*CREDITOS PRESUMIDOS*/
   FOR C_REG_060 IN (SELECT NVL(SUM(VRITEMMAPR),0) AS VRITEMMAPR,
                            CDCREDDCIP
                       FROM ITMAPARECUP
                      WHERE NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                        AND IDTIPOITMAPR IN ('OC','ED','DD')
                        AND CDTIPODCIP = '3'
                        AND CDCREDDCIP IS NOT NULL
                      GROUP BY CDCREDDCIP, CDCREDDCIP) LOOP

      BEGIN
         SELECT NVL(NRSAT,0)
           INTO V_NRSAT
           FROM CODIGODCIP
           WHERE CDTIPO  = '3'
             AND CDCREDITO = C_REG_060.CDCREDDCIP
             AND P_DTINICIAL >= DTINIVALID
             AND P_DTFINAL   <= NVL(DTFINVALID,P_DTFINAL);
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
             V_NRSAT   := 0;
       END;

       IF NVL(V_NRSAT,0) IN ('9','13') THEN
          V_NRSAT := TO_CHAR(P_DTFINAL,'mmyyyy');
       END IF;

       V_LINHA := REPLACE(REPLACE(REPLACE(SUBSTR(P_CDINSCESTA,1,9),'-',''),'.',''),'/','')||
                  TO_CHAR(P_DTFINAL,'mmyyyy')||
                  '060'||
                  LPAD(C_REG_060.CDCREDDCIP,3,'0')||
                  LTRIM(REPLACE(TO_CHAR(ABS(NVL(C_REG_060.VRITEMMAPR,0)),'000000000000000.00'),'.',''))||
                  LPAD(NVL(V_NRSAT,0), 15, '0');

       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         (LPAD('01',10,'0'), V_LINHA, P_NRSEQARQMAG);

       V_TOTARQUIVO := NVL(V_TOTARQUIVO,0)+1;
       V_LINHA := NULL;
   END LOOP;

   /*TOTALIZADOR CREDITOS PRESUMIDOS*/
   FOR C_REG_070 IN (SELECT NVL(SUM(VRITEMMAPR),0) AS VRITEMMAPR
                           FROM ITMAPARECUP
                          WHERE NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                            AND IDTIPOITMAPR IN ('OC','ED','DD')
                            AND CDTIPODCIP = '3'
                            AND CDCREDDCIP IS NOT NULL) LOOP

       V_LINHA := REPLACE(REPLACE(REPLACE(SUBSTR(P_CDINSCESTA,1,9),'-',''),'.',''),'/','')||
                  TO_CHAR(P_DTFINAL,'mmyyyy')||
                  '070'||
                  LTRIM(REPLACE(TO_CHAR(ABS(NVL(C_REG_070.VRITEMMAPR,0)),'000000000000000.00'),'.',''));

       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         (LPAD('01',10,'0'), V_LINHA, P_NRSEQARQMAG);

       V_TOTARQUIVO := NVL(V_TOTARQUIVO,0)+1;
       V_LINHA := NULL;
   END LOOP;

   /*ESTORNO DE DEBITOS*/
   FOR C_REG_080 IN (SELECT NVL(SUM(VRITEMMAPR),0) AS VRITEMMAPR,
                            CDCREDDCIP
                       FROM ITMAPARECUP
                      WHERE NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                        AND IDTIPOITMAPR IN ('OC','ED','DD')
                        AND CDTIPODCIP = '4'
                        AND CDCREDDCIP IS NOT NULL
                      GROUP BY CDCREDDCIP, CDCREDDCIP) LOOP

      BEGIN
         SELECT NVL(NRSAT,0)
           INTO V_NRSAT
           FROM CODIGODCIP
           WHERE CDTIPO  = '4'
             AND CDCREDITO = C_REG_080.CDCREDDCIP
             AND P_DTINICIAL >= DTINIVALID
             AND P_DTFINAL   <= NVL(DTFINVALID,P_DTFINAL);
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
             V_NRSAT   := 0;
       END;

       IF NVL(V_NRSAT,0) IN ('9','13') THEN
          V_NRSAT := TO_CHAR(P_DTFINAL,'mmyyyy');
       END IF;

       V_LINHA := REPLACE(REPLACE(REPLACE(SUBSTR(P_CDINSCESTA,1,9),'-',''),'.',''),'/','')||
                  TO_CHAR(P_DTFINAL,'mmyyyy')||
                  '040'||
                  LPAD(C_REG_080.CDCREDDCIP,3,'0')||
                  LTRIM(REPLACE(TO_CHAR(ABS(NVL(C_REG_080.VRITEMMAPR,0)),'000000000000000.00'),'.',''))||
                  LPAD(NVL(V_NRSAT,0), 15, '0');

       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         (LPAD('01',10,'0'), V_LINHA, P_NRSEQARQMAG);

       V_TOTARQUIVO := NVL(V_TOTARQUIVO,0)+1;
       V_LINHA := NULL;
   END LOOP;

   /*TOTALIZADOR ESTORNO DEBITOS*/
   FOR C_REG_090 IN (SELECT NVL(SUM(VRITEMMAPR),0) AS VRITEMMAPR
                           FROM ITMAPARECUP
                          WHERE NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                            AND IDTIPOITMAPR IN('OC','ED','DD') 
                            AND CDTIPODCIP = '4'
                            AND CDCREDDCIP IS NOT NULL) LOOP

       V_LINHA := REPLACE(REPLACE(REPLACE(SUBSTR(P_CDINSCESTA,1,9),'-',''),'.',''),'/','')||
                  TO_CHAR(P_DTFINAL,'mmyyyy')||
                  '090'||
                  LTRIM(REPLACE(TO_CHAR(ABS(NVL(C_REG_090.VRITEMMAPR,0)),'000000000000000.00'),'.',''));

       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         (LPAD('01',10,'0'), V_LINHA, P_NRSEQARQMAG);

       V_TOTARQUIVO := NVL(V_TOTARQUIVO,0)+1;
       V_LINHA := NULL;
   END LOOP;

  /*CREDITO CONTRIBUICAO / APLICACAO EM FUNDOS*/
   FOR C_REG_100 IN (SELECT NVL(SUM(VRITEMMAPR),0) AS VRITEMMAPR,
                            CDCREDDCIP
                       FROM ITMAPARECUP
                      WHERE NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                        AND IDTIPOITMAPR IN ('OC','ED','DD') 
                        AND CDTIPODCIP = '5'
                        AND CDCREDDCIP IS NOT NULL
                      GROUP BY CDCREDDCIP, CDCREDDCIP) LOOP

      BEGIN
         SELECT NVL(NRSAT,0)
           INTO V_NRSAT
           FROM CODIGODCIP
           WHERE CDTIPO  = '5'
             AND CDCREDITO = C_REG_100.CDCREDDCIP
             AND P_DTINICIAL >= DTINIVALID
             AND P_DTFINAL   <= NVL(DTFINVALID,P_DTFINAL);
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
             V_NRSAT   := 0;
       END;

       IF NVL(V_NRSAT,0) IN ('9','13') THEN
          V_NRSAT := TO_CHAR(P_DTFINAL,'mmyyyy');
       END IF;

       V_LINHA := REPLACE(REPLACE(REPLACE(SUBSTR(P_CDINSCESTA,1,9),'-',''),'.',''),'/','')||
                  TO_CHAR(P_DTFINAL,'mmyyyy')||
                  '100'||
                  LPAD(C_REG_100.CDCREDDCIP,3,'0')||
                  LTRIM(REPLACE(TO_CHAR(ABS(NVL(C_REG_100.VRITEMMAPR,0)),'000000000000000.00'),'.',''))||
                  LPAD(NVL(V_NRSAT,0), 15, '0');

       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         (LPAD('01',10,'0'), V_LINHA, P_NRSEQARQMAG);

       V_TOTARQUIVO := NVL(V_TOTARQUIVO,0)+1;
       V_LINHA := NULL;
   END LOOP;

   /*TOTALIZADOR CREDITO CONTRIBUICAO / APLICACAO EM FUNDOS*/
   FOR C_REG_110 IN (SELECT NVL(SUM(VRITEMMAPR),0) AS VRITEMMAPR
                           FROM ITMAPARECUP
                          WHERE NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                            AND IDTIPOITMAPR IN('OC','ED','DD')
                            AND CDTIPODCIP = '5'
                            AND CDCREDDCIP IS NOT NULL) LOOP

       V_LINHA := REPLACE(REPLACE(REPLACE(SUBSTR(P_CDINSCESTA,1,9),'-',''),'.',''),'/','')||
                  TO_CHAR(P_DTFINAL,'mmyyyy')||
                  '110'||
                  LTRIM(REPLACE(TO_CHAR(ABS(NVL(C_REG_110.VRITEMMAPR,0)),'000000000000000.00'),'.',''));

       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         (LPAD('01',10,'0'), V_LINHA, P_NRSEQARQMAG);

       V_TOTARQUIVO := NVL(V_TOTARQUIVO,0)+1;
       V_LINHA := NULL;
   END LOOP;

   /*CREDITO IMPOSTO RETIDO SUBSTITUICAO TRIBUTARIA*/
   FOR C_REG_140 IN (SELECT NVL(SUM(VRITEMMAPR),0) AS VRITEMMAPR,
                            CDCREDDCIP
                       FROM ITMAPARECUP
                      WHERE NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                        AND IDTIPOITMAPR IN('OC','ED','DD')
                        AND CDTIPODCIP = '6'
                        AND CDCREDDCIP IS NOT NULL
                      GROUP BY CDCREDDCIP, CDCREDDCIP) LOOP

      BEGIN
         SELECT NVL(NRSAT,0)
           INTO V_NRSAT
           FROM CODIGODCIP
           WHERE CDTIPO  = '6'
             AND CDCREDITO  = C_REG_140.CDCREDDCIP
             AND P_DTINICIAL >= DTINIVALID
             AND P_DTFINAL   <= NVL(DTFINVALID,P_DTFINAL);
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
             V_NRSAT   := 0;
       END;

       IF NVL(V_NRSAT,0) IN ('9','13') THEN
          V_NRSAT := TO_CHAR(P_DTFINAL,'mmyyyy');
       END IF;

       V_LINHA := REPLACE(REPLACE(REPLACE(SUBSTR(P_CDINSCESTA,1,9),'-',''),'.',''),'/','')||
                  TO_CHAR(P_DTFINAL,'mmyyyy')||
                  '140'||
                  LPAD(C_REG_140.CDCREDDCIP,3,'0')||
                  LTRIM(REPLACE(TO_CHAR(ABS(NVL(C_REG_140.VRITEMMAPR,0)),'000000000000000.00'),'.',''))||
                  LPAD(NVL(V_NRSAT,0), 15, '0');

       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         (LPAD('01',10,'0'), V_LINHA, P_NRSEQARQMAG);

       V_TOTARQUIVO := NVL(V_TOTARQUIVO,0)+1;
       V_LINHA := NULL;
   END LOOP;

   /*TOTALIZADOR CREDITO IMPOSTO RETIDO SUBSTITUICAO TRIBUTARIA**/
   FOR C_REG_150 IN (SELECT NVL(SUM(VRITEMMAPR),0) AS VRITEMMAPR
                           FROM ITMAPARECUP
                          WHERE NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                            AND IDTIPOITMAPR IN('OC','ED','DD')
                            AND CDTIPODCIP = '6'
                            AND CDCREDDCIP IS NOT NULL) LOOP

       V_LINHA := REPLACE(REPLACE(REPLACE(SUBSTR(P_CDINSCESTA,1,9),'-',''),'.',''),'/','')||
                  TO_CHAR(P_DTFINAL,'mmyyyy')||
                  '150'||
                  LTRIM(REPLACE(TO_CHAR(ABS(NVL(C_REG_150.VRITEMMAPR,0)),'000000000000000.00'),'.',''));

       INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
       VALUES
         (LPAD('01',10,'0'), V_LINHA, P_NRSEQARQMAG);

       V_TOTARQUIVO := NVL(V_TOTARQUIVO,0)+1;
       V_LINHA := NULL;
   END LOOP;

   V_LINHA := NULL;
   V_LINHA := '900'||
              LPAD(NVL(V_TOTARQUIVO+1,0), 7, '0');
   V_CONTADOR := V_CONTADOR + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     (LPAD(TO_CHAR(V_CONTADOR),10,'0'), V_LINHA, P_NRSEQARQMAG);
END;
/
