CREATE OR REPLACE FUNCTION ARQUIVO_DCTF_R11(
   P_CDLIVROMAG   IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_SITUACAO     IN NOTAFISCAL.IDSTATUS%TYPE,
   P_DBTOQTA      IN NOTAFISCAL.IDSTATUS%TYPE,
   V_IRPJ         VARCHAR2,
   V_IRRF         VARCHAR2,
   V_IPI          VARCHAR2,
   V_IOF          VARCHAR2,
   V_CSLL         VARCHAR2,
   V_PIS          VARCHAR2,
   V_COFINS       VARCHAR2,
   V_CPMF         VARCHAR2,
   V_CIDE         VARCHAR2,
   V_RET          VARCHAR2,
   V_CSRF         VARCHAR2,
   V_COSIRF       VARCHAR2,
   V_CPSSS        VARCHAR2) RETURN NUMBER
AS
   V_CONTREGAR11   NUMBER;
   V_REG11_IPI    VARCHAR2(1000);
   V_ORDESTAB     VARCHAR2(14);
   V_CNPJEMPR     VARCHAR2(14);
BEGIN
   V_CONTREGAR11 := 2;
   IF P_DBTOQTA = '0' THEN
      FOR C_REG_11_IRPJ IN (SELECT (RPAD('R11',3,' '))                                                                           ||
                                   LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)       ||
                                   TO_CHAR(TO_DATE(P_DTINICIAL),'YYYYMM')                                                        ||
                                   P_SITUACAO                                                                                    ||
                                   DECODE(P_SITUACAO,'0',RPAD('0',8,'0'), TO_CHAR(TO_DATE(P_DTINICIAL),'YYYYMMDD'))              ||
                                   V_IRPJ                                                                                        ||
                                   RPAD(NVL(CDRECEITACONTR||CDVARIACAO,'000000'),6,'0')                                          ||
                                   NVL(IDPERIODICREC,0)                                                                          ||
                                   TO_CHAR(DTFIMPERIODO,'YYYY')                                                                  ||/*ANO DO FATO GERADOR*/
                                   CASE WHEN NVL(IDPERIODICREC,'M') = 'T' THEN DECODE(TO_CHAR(DTFIMPERIODO,'MM'),'03','01',DECODE(TO_CHAR(DTFIMPERIODO,'MM'),'06','02',DECODE(TO_CHAR(DTFIMPERIODO,'MM'),'09','03','04')))
                                        WHEN NVL(IDPERIODICREC,'M') IN ('M','B','U','E') THEN TO_CHAR(DTFIMPERIODO,'MM')         ELSE '00' END || /*MES/BIMESTRE/TRIMESTRE/QUADRIMESTRE/SEMESTRE DO PERIODO DE APURACAO*/
                                   CASE WHEN NVL(IDPERIODICREC,'M') IN ('D','S','X','Q') THEN TO_CHAR(TO_DATE(P_DTINICIAL),'DD') ELSE '00' END ||
                                   LPAD('0',6,'0')                                                                               || /*APENAS IPI*/
                                   LPAD('0',14,'0')                                                                              || /*APENAS RET*/
                                   '0'                                                                                           ||
                                   TO_CHAR(TO_DATE(P_DTINICIAL),'DDMMYYYY')                                                      ||
                                   LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)       ||
                                   NVL(CDRECEITACONTR,'0000')                                                                    ||
                                   TO_CHAR(DTVENCGUIA,'DDMMYYYY')                                                                ||
                                   RPAD(' ',17,' ')                                                                              ||
                                   LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRDEBITO,   0),'000000000000.00'),'.')),'14','0')              ||
                                   LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRMULTAGUIA,0),'000000000000.00'),'.')),'14','0')              ||
                                   LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRJUROGUIA, 0),'000000000000.00'),'.')),'14','0')              ||
                                   LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRDEBITO,   0),'000000000000.00'),'.')),'14','0')              ||
                                   RPAD('0',10,'0')  AS  REG_11_IRPJ,
                                   NVL(IDGUIAIMPR,'N') AS IDGUIAIMPR
                             FROM (SELECT SUM(ROUND(VRTOTAIMPOS,2)) AS VRDEBITO, E.NRINSJUREMPR, CDRECEITACONTR,
                                          VRJUROGUIA, VRMULTAGUIA, DTVENCGUIA, IDGUIAIMPR, CDVARIACAO,IDPERIODICREC, MP.DTFIMPERIODO
                                     FROM EMPRESA E, MAPAAPURAC MP
                                    WHERE E.CDEMPRESA     = P_CDEMPRESA
                                      AND E.CDEMPRESA     = MP.CDEMPRESA
                                      AND MP.CDIMPOSTO    = '25'
                                      AND MP.DTFIMPERIODO = P_DTFINAL
                                      AND ((TO_CHAR(TO_DATE(P_DTFINAL),'MM') IN ('03','06','09','12') AND MP.IDTPMAPAAPURAC IN ('LPT','LRT')) OR
                                           (MP.IDTPMAPAAPURAC IN ('LPM','LRM')))
                                    GROUP BY E.NRINSJUREMPR, CDRECEITACONTR, VRJUROGUIA, VRMULTAGUIA, DTVENCGUIA,
                                          IDGUIAIMPR, CDVARIACAO, IDPERIODICREC,  MP.DTFIMPERIODO))LOOP
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('104'||LPAD(RTRIM(V_CONTREGAR11), 7, '0'), C_REG_11_IRPJ.REG_11_IRPJ, P_NRSEQARQMAG);
			 V_CONTREGAR11 := V_CONTREGAR11+2; 
      END LOOP ;
   END IF;

   FOR C_REG_11_IRRF IN (SELECT (RPAD('R11',3,' '))                                                                           ||
                                LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)       ||
                                TO_CHAR(TO_DATE(P_DTINICIAL),'YYYYMM')                                                        ||
                                P_SITUACAO                                                                                    ||
                                DECODE(P_SITUACAO,'0',RPAD('0',8,'0'), TO_CHAR(TO_DATE(P_DTINICIAL),'YYYYMMDD'))              ||
                                V_IRRF                                                                                        ||
                                RPAD(NVL(CDRECEITACONTR||CDVARIACAO,'000000'),6,'0')                                          ||
                                NVL(IDPERIODICREC,'M')                                                                        ||
                                TO_CHAR(TO_DATE(P_DTINICIAL),'YYYY')                                                          ||
                                CASE WHEN NVL(IDPERIODICREC,'M') IN ('M','B','U','T','E') THEN TO_CHAR(TO_DATE(P_DTINICIAL),'MM') ELSE '00' END ||
                                CASE WHEN NVL(IDPERIODICREC,'M') IN ('D','S','X','Q')     THEN TO_CHAR(TO_DATE(P_DTINICIAL),'DD') ELSE '00' END ||
                                LPAD('0',6,'0')                                                                               || /*APENAS IPI*/
                                LPAD('0',14,'0')                                                                              || /*APENAS RET*/
                                '0'                                                                                           ||
                                TO_CHAR(TO_DATE(P_DTINICIAL),'DDMMYYYY')                                                      ||
                                LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)       ||
                                NVL(CDRECEITACONTR,'0000')                                                                    ||
                                TO_CHAR(DTVENCGUIA,'DDMMYYYY')                                                                ||
                                RPAD(' ',17,' ')                                                                              ||
                                LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRDEBITO,   0),'000000000000.00'),'.')),'14','0')              ||
                                LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRMULTAGUIA,0),'000000000000.00'),'.')),'14','0')              ||
                                LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRJUROGUIA, 0),'000000000000.00'),'.')),'14','0')              ||
                                LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRDEBITO,   0),'000000000000.00'),'.')),'14','0')              ||
                                RPAD('0',10,'0')  AS  REG_11_IRRF,
                                NVL(IDGUIAIMPR,'N') AS IDGUIAIMPR, SEQUENCIAL
                           FROM (SELECT SUM(VRDEBITO) AS VRDEBITO, NRINSJUREMPR, CDRECEITACONTR,
                                        SUM(VRJUROGUIA) AS VRJUROGUIA, SUM(VRMULTAGUIA) AS VRMULTAGUIA,
                                        DTVENCGUIA, IDGUIAIMPR, CDVARIACAO, IDPERIODICREC, MIN(SEQUENCIAL) AS SEQUENCIAL
                                   FROM (SELECT ROUND(M.VRTOTAIMPOS,2) AS VRDEBITO, E.NRINSJUREMPR,
                                                NVL(M.CDRECEITACONTR,M.CDRECEGUIA) AS CDRECEITACONTR,
                                                M.VRJUROGUIA, M.VRMULTAGUIA, M.DTVENCGUIA, M.IDGUIAIMPR,
                                                M.CDVARIACAO, M.IDPERIODICREC, '00' AS SEQUENCIAL
                                           FROM EMPRESA E, MAPAAPURAC M
                                          WHERE E.CDEMPRESA    = P_CDEMPRESA
                                            AND E.CDEMPRESA    = M.CDEMPRESA
                                            AND M.CDIMPOSTO    = '21'
                                            AND M.DTINIPERIODO = P_DTINICIAL
                                            AND M.DTFIMPERIODO = P_DTFINAL
                                          UNION ALL
                                         SELECT ROUND(G.VRRECEITAGUIA,2) AS VRDEBITO, E.NRINSJUREMPR,
                                                SUBSTR(RPAD(G.CDRECEITAGUIA,6,'0'),1,4) AS CDRECEITACONTR,
                                                0 AS VRJUROGUIA, 0 AS VRMULTAGUIA, G.DTVENCGUIACOMPL, M.IDGUIAIMPR,
                                                SUBSTR(RPAD(G.CDRECEITAGUIA,6,'0'),5,2) AS CDVARIACAO, M.IDPERIODICREC,
                                                SUBSTR(G.NRSEQGUIACOMPL,9,2) AS SEQUENCIAL
                                           FROM EMPRESA E, MAPAAPURAC M, GUIACOMPLMAPA G
                                          WHERE E.CDEMPRESA    = P_CDEMPRESA
                                            AND E.CDEMPRESA    = M.CDEMPRESA
                                            AND M.CDIMPOSTO    = '21'
                                            AND M.DTINIPERIODO = P_DTINICIAL
                                            AND M.DTFIMPERIODO = P_DTFINAL
                                            AND M.NRSEQUMAPAPU = G.NRSEQUMAPAPU)
                                  GROUP BY NRINSJUREMPR, CDRECEITACONTR, DTVENCGUIA, IDGUIAIMPR, CDVARIACAO, IDPERIODICREC)) LOOP
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('104'||C_REG_11_IRRF.SEQUENCIAL||'1'||LPAD(RTRIM(V_CONTREGAR11), 4, '0'), C_REG_11_IRRF.REG_11_IRRF, P_NRSEQARQMAG);
		   V_CONTREGAR11 := V_CONTREGAR11+2;
   END LOOP;

   FOR C_REG_11_CSRF IN (SELECT (RPAD('R11',3,' '))                                                                           ||
                                LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)       ||
                                TO_CHAR(TO_DATE(P_DTINICIAL),'YYYYMM')                                                        ||
                                P_SITUACAO                                                                                    ||
                                DECODE(P_SITUACAO,'0',RPAD('0',8,'0'), TO_CHAR(TO_DATE(P_DTINICIAL),'YYYYMMDD'))              ||
                                V_CSRF                                                                                        ||
                                RPAD(NVL(CDRECEITACONTR||CDVARIACAO,'000000'),6,'0')                                          ||
                                NVL(IDPERIODICREC,'M')                                                                        ||
                                TO_CHAR(TO_DATE(P_DTINICIAL),'YYYY')                                                          ||
                                CASE WHEN NVL(IDPERIODICREC,'M') IN ('M','B','U','T','E') THEN TO_CHAR(TO_DATE(P_DTINICIAL),'MM') ELSE '00' END ||
                                CASE WHEN NVL(IDPERIODICREC,'M') IN ('D','S','X','Q')     THEN TO_CHAR(TO_DATE(P_DTINICIAL),'DD') ELSE '00' END ||
                                LPAD('0',6,'0')                                                                               || /*APENAS IPI*/
                                LPAD('0',14,'0')                                                                              || /*APENAS RET*/
                                '0'                                                                                           ||
                                TO_CHAR(TO_DATE(P_DTINICIAL),'DDMMYYYY')                                                      ||
                                LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)       ||
                                NVL(CDRECEITACONTR,'0000')                                                                    ||
                                TO_CHAR(DTVENCGUIA,'DDMMYYYY')                                                                ||
                                RPAD(' ',17,' ')                                                                              ||
                                LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRDEBITO,   0),'000000000000.00'),'.')),'14','0')              ||
                                LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRMULTAGUIA,0),'000000000000.00'),'.')),'14','0')              ||
                                LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRJUROGUIA, 0),'000000000000.00'),'.')),'14','0')              ||
                                LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRDEBITO,   0),'000000000000.00'),'.')),'14','0')              ||
                                RPAD('0',10,'0')  AS  REG_11_CSRF,
                                NVL(IDGUIAIMPR,'N') AS IDGUIAIMPR, SEQUENCIAL
                           FROM (SELECT SUM(VRDEBITO) AS VRDEBITO, NRINSJUREMPR, CDRECEITACONTR,
                                        SUM(VRJUROGUIA) AS VRJUROGUIA, SUM(VRMULTAGUIA) AS VRMULTAGUIA,
                                        DTVENCGUIA, IDGUIAIMPR, CDVARIACAO, IDPERIODICREC, MIN(SEQUENCIAL) AS SEQUENCIAL
                                   FROM (SELECT ROUND(M.VRTOTAIMPOS,2) AS VRDEBITO, E.NRINSJUREMPR,
                                                NVL(M.CDRECEITACONTR,M.CDRECEGUIA) AS CDRECEITACONTR,
                                                M.VRJUROGUIA, M.VRMULTAGUIA, M.DTVENCGUIA, M.IDGUIAIMPR,
                                                M.CDVARIACAO, M.IDPERIODICREC, '00' AS SEQUENCIAL
                                           FROM EMPRESA E, MAPAAPURAC M
                                          WHERE E.CDEMPRESA    = P_CDEMPRESA
                                            AND E.CDEMPRESA    = M.CDEMPRESA
                                            AND M.CDIMPOSTO    = '24'
                                            AND M.DTINIPERIODO = P_DTINICIAL
                                            AND M.DTFIMPERIODO = P_DTFINAL
                                          UNION ALL
                                         SELECT ROUND(G.VRRECEITAGUIA,2) AS VRDEBITO, E.NRINSJUREMPR,
                                                SUBSTR(RPAD(G.CDRECEITAGUIA,6,'0'),1,4) AS CDRECEITACONTR,
                                                0 AS VRJUROGUIA, 0 AS VRMULTAGUIA, G.DTVENCGUIACOMPL, M.IDGUIAIMPR,
                                                SUBSTR(RPAD(G.CDRECEITAGUIA,6,'0'),5,2) AS CDVARIACAO, M.IDPERIODICREC,
                                                SUBSTR(G.NRSEQGUIACOMPL,9,2) AS SEQUENCIAL
                                           FROM EMPRESA E, MAPAAPURAC M, GUIACOMPLMAPA G
                                          WHERE E.CDEMPRESA    = P_CDEMPRESA
                                            AND E.CDEMPRESA    = M.CDEMPRESA
                                            AND M.CDIMPOSTO    = '24'
                                            AND M.DTINIPERIODO = P_DTINICIAL
                                            AND M.DTFIMPERIODO = P_DTFINAL
                                            AND M.NRSEQUMAPAPU = G.NRSEQUMAPAPU)
                                  GROUP BY NRINSJUREMPR, CDRECEITACONTR, DTVENCGUIA, IDGUIAIMPR, CDVARIACAO, IDPERIODICREC)) LOOP
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('104'||C_REG_11_CSRF.SEQUENCIAL||'1'||LPAD(RTRIM(V_CONTREGAR11), 4, '0'), C_REG_11_CSRF.REG_11_CSRF, P_NRSEQARQMAG);
		   V_CONTREGAR11 := V_CONTREGAR11+2;
   END LOOP;

   V_ORDESTAB := 0;
   /*FAZ O SELECT BUSCANDO ENTRADAS E SAIDAS PORQUE PARA O IPI E NECESSARIO QUEBRAR O VALOR POR INSCRICAO ESTADUAL*/
   FOR C_REG_11_IPI IN (SELECT (VRDEBITO - VRCREDITO) AS IMPOSTO, CDEMPRESA, NRINSJUREMPR, NRINSJURIEST,
                              CDRECEITACONTR, DTVENCGUIA, CDVARIACAO, IDPERIODICREC, VRJUROGUIA, VRMULTAGUIA,
                              NVL(IDGUIAIMPR,'N') AS IDGUIAIMPR
                         FROM (SELECT SUM(DECODE(NF.IDENTRSAIDOP,'E',IMP.VRIMPOSTO,0)) AS VRCREDITO,
                                      SUM(DECODE(NF.IDENTRSAIDOP,'S',IMP.VRIMPOSTO,0)) AS VRDEBITO,
                                      NF.CDEMPRESA, IE.NRINSJURIEST, E.NRINSJUREMPR, IMP.IDGUIAIMPR, IMP.NRSEQUMAPAPU,
                                      IMP.VRJUROGUIA, IMP.VRMULTAGUIA, IMP.IDPERIODICREC, IMP.CDVARIACAO, IMP.DTVENCGUIA,
                                      IMP.CDRECEITACONTR
                                 FROM NOTAFISCAL NF, EMPRESA E, INSCRESTAD IE,
                                      (SELECT DECODE(I.CDFISCALNF, '1',I.VRIMPOSTONF, 0) AS VRIMPOSTO, I.CDFILIAL, I.NRLANCTONF,
                                              M.CDEMPRESA, NVL(M.CDRECEITACONTR,M.CDRECEGUIA) AS CDRECEITACONTR, M.DTVENCGUIA,
                                              M.CDVARIACAO, M.IDPERIODICREC,  M.VRJUROGUIA, M.VRMULTAGUIA, M.NRSEQUMAPAPU,
                                              M.IDGUIAIMPR
                                         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.DTINIPERIODO = P_DTINICIAL
                                          AND M.DTFIMPERIODO = P_DTFINAL) 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, IMP.IDGUIAIMPR, IMP.NRSEQUMAPAPU,
                                      IMP.VRJUROGUIA, IMP.VRMULTAGUIA, IMP.IDPERIODICREC, IMP.CDVARIACAO, IMP.DTVENCGUIA,
                                      IMP.CDRECEITACONTR
                                ORDER BY IE.NRINSJURIEST))LOOP

      SELECT (RPAD('R11',3,' '))                                                                                       ||
             LPAD(REPLACE(REPLACE(REPLACE(REPLACE(C_REG_11_IPI.NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)      ||
             TO_CHAR(TO_DATE(P_DTINICIAL),'YYYYMM')                                                                    ||
             P_SITUACAO                                                                                                ||
             DECODE(P_SITUACAO,'0',RPAD('0',8,'0'),TO_CHAR(TO_DATE(P_DTINICIAL),'YYYYMMDD'))                           ||
             V_IPI                                                                                                     ||
             RPAD(NVL(C_REG_11_IPI.CDRECEITACONTR||C_REG_11_IPI.CDVARIACAO,'000000'),6,'0')                            ||
             NVL(C_REG_11_IPI.IDPERIODICREC,'M')                                                                       ||
             TO_CHAR(TO_DATE(P_DTINICIAL),'YYYY')                                                                      ||
             CASE WHEN NVL(C_REG_11_IPI.IDPERIODICREC,'M') IN ('M','B','U','T','E') THEN TO_CHAR(TO_DATE(P_DTINICIAL),'MM') ELSE '00' END||
             CASE WHEN NVL(C_REG_11_IPI.IDPERIODICREC,'M') IN ('D','S','X','Q')     THEN TO_CHAR(TO_DATE(P_DTINICIAL),'DD') ELSE '00' END||
             SUBSTR(C_REG_11_IPI.NRINSJURIEST,9,14)                                                                    || /*APENAS IPI*/
             RPAD('0',14,'0')                                                                                          || /*APENAS RET*/
             '0'                                                                                                       ||
             TO_CHAR(TO_DATE(P_DTINICIAL),'DDMMYYYY')                                                                  ||
             LPAD(REPLACE(REPLACE(REPLACE(REPLACE(C_REG_11_IPI.NRINSJURIEST,'.',''),'-',''),'/',''),' ',''),14,0)      ||
             NVL(C_REG_11_IPI.CDRECEITACONTR,'0000')                                                                   ||
             TO_CHAR(C_REG_11_IPI.DTVENCGUIA,'DDMMYYYY')                                                               ||
             RPAD(' ',17,' ')                                                                                          ||
             LPAD(LTRIM(REPLACE(TO_CHAR(NVL(C_REG_11_IPI.IMPOSTO,    0),'000000000000.00'),'.')),'14','0')             ||
             LPAD(LTRIM(REPLACE(TO_CHAR(NVL(C_REG_11_IPI.VRMULTAGUIA,0),'000000000000.00'),'.')),'14','0')             ||
             LPAD(LTRIM(REPLACE(TO_CHAR(NVL(C_REG_11_IPI.VRJUROGUIA, 0),'000000000000.00'),'.')),'14','0')             ||
             LPAD(LTRIM(REPLACE(TO_CHAR(NVL(C_REG_11_IPI.IMPOSTO,    0),'000000000000.00'),'.')),'14','0')             ||
             RPAD('0',10,'0')  AS  REG_11_IPI
        INTO V_REG11_IPI
        FROM DUAL;

      V_ORDESTAB := NVL(V_ORDESTAB,0) + 1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('110'||V_ORDESTAB||'1'||LPAD(RTRIM(V_CONTREGAR11), 5, '0'), V_REG11_IPI, P_NRSEQARQMAG);
		   V_CONTREGAR11 := V_CONTREGAR11+2;
   END LOOP ;

   IF P_DBTOQTA = '0' THEN
      FOR C_REG_11_CSLL IN (SELECT (RPAD('R11',3,' '))                                                                           ||
                                   LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)       ||
                                   TO_CHAR(TO_DATE(P_DTINICIAL),'YYYYMM')                                                        ||
                                   P_SITUACAO                                                                                    ||
                                   DECODE(P_SITUACAO,'0',RPAD('0',8,'0'), TO_CHAR(TO_DATE(P_DTINICIAL),'YYYYMMDD'))              ||
                                   V_CSLL                                                                                        ||
                                   RPAD(NVL(CDRECEITACONTR||CDVARIACAO,'000000'),6,'0')                                          ||
                                   NVL(IDPERIODICREC,'M')                                                                        ||
                                   TO_CHAR(TO_DATE(P_DTINICIAL),'YYYY')                                                          ||
                                   CASE WHEN NVL(IDPERIODICREC,'M') IN ('M','B','U','T','E') THEN TO_CHAR(TO_DATE(P_DTINICIAL),'MM') ELSE '00' END ||
                                   CASE WHEN NVL(IDPERIODICREC,'M') IN ('D','S','X','Q')     THEN TO_CHAR(TO_DATE(P_DTINICIAL),'DD') ELSE '00' END ||
                                   LPAD('0',6,'0')                                                                               || /*APENAS IPI*/
                                   LPAD('0',14,'0')                                                                              || /*APENAS RET*/
                                   '0'                                                                                           ||
                                   TO_CHAR(TO_DATE(P_DTINICIAL),'DDMMYYYY')                                                      ||
                                   LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)       ||
                                   NVL(CDRECEITACONTR,'0000')                                                                    ||
                                   TO_CHAR(DTVENCGUIA,'DDMMYYYY')                                                                ||
                                   RPAD(' ',17,' ')                                                                              ||
                                   LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRDEBITO,   0),'000000000000.00'),'.')),'14','0')              ||
                                   LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRMULTAGUIA,0),'000000000000.00'),'.')),'14','0')              ||
                                   LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRJUROGUIA, 0),'000000000000.00'),'.')),'14','0')              ||
                                   LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRDEBITO,   0),'000000000000.00'),'.')),'14','0')              ||
                                   RPAD('0',10,'0')  AS  REG_11_CSLL,
                                   NVL(IDGUIAIMPR,'N') AS IDGUIAIMPR
                              FROM (SELECT SUM(ROUND(VRTOTAIMPOS,2)) AS VRDEBITO, E.NRINSJUREMPR, CDRECEITACONTR, VRJUROGUIA,
                                           VRMULTAGUIA, DTVENCGUIA, IDGUIAIMPR, CDVARIACAO, IDPERIODICREC
                                      FROM EMPRESA E, MAPAAPURAC MP
                                     WHERE E.CDEMPRESA     = P_CDEMPRESA
                                       AND E.CDEMPRESA     = MP.CDEMPRESA
                                       AND MP.CDIMPOSTO    = '70'
                                       AND MP.DTFIMPERIODO = P_DTFINAL
                                       AND ((TO_CHAR(TO_DATE(P_DTFINAL),'MM') IN ('03','06','09','12') AND MP.IDTPMAPAAPURAC IN ('LPT','LRT')) OR
                                            (MP.IDTPMAPAAPURAC IN ('LPM','LRM')))
                                     GROUP BY E.NRINSJUREMPR, CDRECEITACONTR, VRJUROGUIA, VRMULTAGUIA, DTVENCGUIA,
                                           IDGUIAIMPR, CDVARIACAO, IDPERIODICREC))LOOP
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('104'||LPAD(RTRIM(V_CONTREGAR11), 7, '0'), C_REG_11_CSLL.REG_11_CSLL, P_NRSEQARQMAG);
			  V_CONTREGAR11 := V_CONTREGAR11+2;
      END LOOP ;
   END IF;

   FOR C_REG_11_PIS IN (SELECT (RPAD('R11',3,' '))                                                                           ||
                               LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)       ||
                               TO_CHAR(TO_DATE(P_DTINICIAL),'YYYYMM')                                                        ||
                               P_SITUACAO                                                                                    ||
                               DECODE(P_SITUACAO,'0',RPAD('0',8,'0'), TO_CHAR(TO_DATE(P_DTINICIAL),'YYYYMMDD'))              ||
                               V_PIS                                                                                         ||
                               RPAD(NVL(CDRECEITACONTR||CDVARIACAO,'000000'),6,'0')                                          ||
                               NVL(IDPERIODICREC,'M')                                                                        ||
                               TO_CHAR(TO_DATE(P_DTINICIAL),'YYYY')                                                          ||
                               CASE WHEN NVL(IDPERIODICREC,'M') IN ('M','B','U','T','E') THEN TO_CHAR(TO_DATE(P_DTINICIAL),'MM') ELSE '00' END ||
                               CASE WHEN NVL(IDPERIODICREC,'M') IN ('D','S','X','Q')     THEN TO_CHAR(TO_DATE(P_DTINICIAL),'DD') ELSE '00' END ||
                               LPAD('0',6,'0')                                                                               || /*APENAS IPI*/
                               LPAD('0',14,'0')                                                                              || /*APENAS RET*/
                               '0'                                                                                           ||
                               TO_CHAR(TO_DATE(P_DTINICIAL),'DDMMYYYY')                                                      ||
                               LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)       ||
                               RPAD(NVL(CDRECEITACONTR,'0000'),4,'0')                                                        ||
                               TO_CHAR(DTVENCGUIA,'DDMMYYYY')                                                                ||
                               RPAD(' ',17,' ')                                                                              ||
                               LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRDEBITO,   0),'000000000000.00'),'.')),'14','0')              ||
                               LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRMULTAGUIA,0),'000000000000.00'),'.')),'14','0')              ||
                               LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRJUROGUIA, 0),'000000000000.00'),'.')),'14','0')              ||
                               LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRDEBITO,   0),'000000000000.00'),'.')),'14','0')              ||
                               RPAD('0',10,'0')  AS  REG_11_PIS,
                               NVL(IDGUIAIMPR,'N') AS IDGUIAIMPR
                         FROM (SELECT SUM(ROUND(NVL(VRRECEITAGUIA,VRTOTAIMPOS),2)) AS VRDEBITO, E.NRINSJUREMPR,
                                      NVL(CDRECEITAGUIA,NVL(CDRECENCUMU, CDRECECUMU)) AS CDRECEITACONTR,
                                      VRJUROGUIA, VRMULTAGUIA, DTVENCGUIA, IDGUIAIMPR, CDVARIACAO, IDPERIODICREC
                                 FROM EMPRESA E, MAPAAPURAC MP, guiacompapucontr G
                                WHERE E.CDEMPRESA     = P_CDEMPRESA
                                  AND E.CDEMPRESA     = MP.CDEMPRESA
                                  AND MP.CDIMPOSTO    = '50'
                                  AND MP.DTINIPERIODO = P_DTINICIAL
                                  AND MP.DTFIMPERIODO = P_DTFINAL
                                  and G.NRSEQUMAPAPU(+) = MP.NRSEQUMAPAPU
                                GROUP BY E.NRINSJUREMPR, NVL(CDRECEITAGUIA,NVL(CDRECENCUMU, CDRECECUMU)) ,
                                      VRJUROGUIA, VRMULTAGUIA, DTVENCGUIA, IDGUIAIMPR, CDVARIACAO, IDPERIODICREC))LOOP
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('104'||LPAD(RTRIM(V_CONTREGAR11), 7, '0'), C_REG_11_PIS.REG_11_PIS, P_NRSEQARQMAG);
		   V_CONTREGAR11 := V_CONTREGAR11+2;
   END LOOP;

   FOR C_REG_11_COFINS IN (SELECT (RPAD('R11',3,' '))                                                                           ||
                                  LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)       ||
                                  TO_CHAR(TO_DATE(P_DTINICIAL),'YYYYMM')                                                        ||
                                  P_SITUACAO                                                                                    ||
                                  DECODE(P_SITUACAO,'0',RPAD('0',8,'0'), TO_CHAR(TO_DATE(P_DTINICIAL),'YYYYMMDD'))              ||
                                  V_COFINS                                                                                      ||
                                  RPAD(NVL(CDRECEITACONTR||CDVARIACAO,'000000'),6,'0')                                          ||
                                  NVL(IDPERIODICREC,'M')                                                                        ||
                                  TO_CHAR(TO_DATE(P_DTINICIAL),'YYYY')                                                          ||
                                  CASE WHEN NVL(IDPERIODICREC,'M') IN ('M','B','U','T','E') THEN TO_CHAR(TO_DATE(P_DTINICIAL),'MM') ELSE '00' END ||
                                  CASE WHEN NVL(IDPERIODICREC,'M') IN ('D','S','X','Q')     THEN TO_CHAR(TO_DATE(P_DTINICIAL),'DD') ELSE '00' END ||
                                  LPAD('0',6,'0')                                                                               || /*APENAS IPI*/
                                  LPAD('0',14,'0')                                                                              || /*APENAS RET*/
                                  '0'                                                                                           ||
                                  TO_CHAR(TO_DATE(P_DTINICIAL),'DDMMYYYY')                                                      ||
                                  LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)       ||
                                  RPAD(NVL(CDRECEITACONTR,'0000'),4,'0')                                                        ||
                                  TO_CHAR(DTVENCGUIA,'DDMMYYYY')                                                                ||
                                  RPAD(' ',17,' ')                                                                              ||
                                  LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRDEBITO,   0),'000000000000.00'),'.')),'14','0')                        ||
                                  LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRMULTAGUIA,0),'000000000000.00'),'.')),'14','0')                     ||
                                  LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRJUROGUIA, 0),'000000000000.00'),'.')),'14','0')                      ||
                                  LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRDEBITO,   0),'000000000000.00'),'.')),'14','0')                        ||
                                  RPAD('0',10,'0')  AS  REG_11_COFINS,
                                  NVL(IDGUIAIMPR,'N') AS IDGUIAIMPR
                            FROM (SELECT SUM(ROUND(NVL(VRRECEITAGUIA,VRTOTAIMPOS),2)) AS VRDEBITO, E.NRINSJUREMPR,
                                         NVL(CDRECEITAGUIA,NVL(CDRECENCUMU, CDRECECUMU)) AS CDRECEITACONTR,
                                         VRJUROGUIA, VRMULTAGUIA, DTVENCGUIA, IDGUIAIMPR, CDVARIACAO, IDPERIODICREC
                                    FROM EMPRESA E, MAPAAPURAC MP, guiacompapucontr G
                                   WHERE E.CDEMPRESA     = P_CDEMPRESA
                                     AND E.CDEMPRESA     = MP.CDEMPRESA
                                     AND MP.CDIMPOSTO    = '60'
                                     AND MP.DTINIPERIODO = P_DTINICIAL
                                     AND MP.DTFIMPERIODO = P_DTFINAL
                                     and G.NRSEQUMAPAPU(+) = MP.NRSEQUMAPAPU
                                   GROUP BY E.NRINSJUREMPR, NVL(CDRECEITAGUIA,NVL(CDRECENCUMU, CDRECECUMU)) ,
                                         VRJUROGUIA, VRMULTAGUIA, DTVENCGUIA, IDGUIAIMPR, CDVARIACAO, IDPERIODICREC))LOOP
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('104'||LPAD(RTRIM(V_CONTREGAR11), 7, '0'), C_REG_11_COFINS.REG_11_COFINS,P_NRSEQARQMAG);
		   V_CONTREGAR11 := V_CONTREGAR11+2;
   END LOOP ;
   RETURN V_CONTREGAR11;
END;
/