CREATE OR REPLACE FUNCTION ARQUIVO_DCTF_R21 (
   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,
   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_CONTREGAR21   NUMBER;
   V_CDRECEITA    VARCHAR2(6);
BEGIN
   FOR C_REG_21_IRPJ IN (SELECT (RPAD('R21',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                                                                                       ||
                                NVL(NVL(CDRECEITACONTR,CDRECEGUIA)||CDVARIACAO,'000000')                                     ||
                                NVL(IDPERIODICREC,'T')                                                                       ||
                                TO_CHAR(DTFIMPERIODO,'YYYY')                                                                 ||
                                DECODE(TO_CHAR(DTFIMPERIODO,'MM'),'03','01',DECODE(TO_CHAR(DTFIMPERIODO,'MM'),'06','02',DECODE(TO_CHAR(DTFIMPERIODO,'MM'),'09','03','04')))||
                                '00'                                                                                         ||
                                RPAD('0',6,'0')                                                                              ||
                                RPAD('0',14,'0')                                                                             ||
                                '0'                                                                                          ||
                                TO_CHAR(TO_DATE(P_DTINICIAL),'DDMMYYYY')                                                     ||
                                LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)      ||
                                CDRECEITACONTR                                                                               ||
                                TO_CHAR(TO_DATE(DTVENCGUIA),'DDMMYYYY')                                                      ||
                                RPAD(' ',17,' ')                                                                             ||/*N DE REFERENCIA*/
                                LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRDEBITO,   0),'000000000000.00'),'.')),'14','0')             ||/*VALOR DO PRINCIPAL*/
                                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(' ',10,'0')    AS REG_21_IRPJ,
                                NVL(IDGUIAIMPR,'N') AS IDGUIAIMPR
                           FROM (SELECT SUM(ROUND(VRTOTAIMPOS,2)) AS VRDEBITO, E.NRINSJUREMPR, CDRECEITACONTR, VRJUROGUIA,
                                        VRMULTAGUIA, DTVENCGUIA, IDGUIAIMPR, CDVARIACAO, IDPERIODICREC,
                                        MAX(MP.DTFIMPERIODO) AS DTFIMPERIODO, CDRECEGUIA
                                   FROM EMPRESA E, MAPAAPURAC MP
                                  WHERE E.CDEMPRESA     = P_CDEMPRESA
                                    AND E.CDEMPRESA     = MP.CDEMPRESA
                                    AND MP.CDIMPOSTO    = '25'
                                    AND TO_CHAR(TO_DATE(P_DTFINAL),'MM') IN ('03','06','09','12')
                                    AND MP.DTINIPERIODO = ADD_MONTHS(P_DTINICIAL,-5)
                                    AND MP.DTFIMPERIODO = ADD_MONTHS(P_DTFINAL,-3)
                                  GROUP BY E.NRINSJUREMPR, CDRECEITACONTR, VRJUROGUIA, VRMULTAGUIA, DTVENCGUIA,
                                        IDGUIAIMPR, CDVARIACAO, IDPERIODICREC, CDRECEGUIA)
                           WHERE VRDEBITO > 0 )LOOP

      IF C_REG_21_IRPJ.IDGUIAIMPR = 'S' THEN
         V_CONTREGAR21 := NVL(V_CONTREGAR21,0)+1;
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            ('2041'||LPAD(RTRIM(V_CONTREGAR21), 6, '0'), C_REG_21_IRPJ.REG_21_IRPJ, P_NRSEQARQMAG);
      END IF;
   END LOOP;

   FOR C_REG_21_CSLL IN (SELECT (RPAD('R21',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                                                                                       ||
                                NVL(NVL(CDRECEITACONTR,CDRECEGUIA)||CDVARIACAO,'000000')                                     ||
                                NVL(IDPERIODICREC,'T')                                                                       ||
                                TO_CHAR(DTFIMPERIODO,'YYYY')                                                                 ||
                                DECODE(TO_CHAR(DTFIMPERIODO,'MM'),'03','01',DECODE(TO_CHAR(DTFIMPERIODO,'MM'),'06','02',DECODE(TO_CHAR(DTFIMPERIODO,'MM'),'09','03','04')))||
                                '00'                                                                                         ||
                                RPAD('0',6,'0')                                                                              ||
                                RPAD('0',14,'0')                                                                             ||
                                '0'                                                                                          ||
                                TO_CHAR(TO_DATE(P_DTINICIAL),'DDMMYYYY')                                                     ||
                                LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)      ||
                                CDRECEITACONTR                                                                               ||
                                TO_CHAR(TO_DATE(DTVENCGUIA),'DDMMYYYY')                                                      ||
                                RPAD(' ',17,' ')                                                                             ||/*N DE REFERENCIA*/
                                LPAD(LTRIM(REPLACE(TO_CHAR(NVL(VRDEBITO,   0),'000000000000.00'),'.')),'14','0')             ||/*VALOR DO PRINCIPAL*/
                                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(' ',10,'0')    AS REG_21_CSLL,
                                NVL(IDGUIAIMPR,'N') AS IDGUIAIMPR
                           FROM (SELECT SUM(ROUND(VRTOTAIMPOS,2)) AS VRDEBITO, E.NRINSJUREMPR, CDRECEITACONTR, VRJUROGUIA,
                                        VRMULTAGUIA, DTVENCGUIA, IDGUIAIMPR, CDVARIACAO, IDPERIODICREC,
                                        MAX(MP.DTFIMPERIODO) AS DTFIMPERIODO, CDRECEGUIA
                                   FROM EMPRESA E, MAPAAPURAC MP
                                  WHERE E.CDEMPRESA     = P_CDEMPRESA
                                    AND E.CDEMPRESA     = MP.CDEMPRESA
                                    AND MP.CDIMPOSTO    = '70'
                                    AND TO_CHAR(TO_DATE(P_DTFINAL),'MM') IN ('03','06','09','12')
                                    AND MP.DTINIPERIODO = ADD_MONTHS(P_DTINICIAL,-5)
                                    AND MP.DTFIMPERIODO = ADD_MONTHS(P_DTFINAL,-3)
                                  GROUP BY E.NRINSJUREMPR, CDRECEITACONTR, VRJUROGUIA, VRMULTAGUIA, DTVENCGUIA,
                                        IDGUIAIMPR, CDVARIACAO, IDPERIODICREC, CDRECEGUIA)
                           WHERE VRDEBITO > 0 )LOOP

      IF C_REG_21_CSLL.IDGUIAIMPR = 'S' THEN
         V_CONTREGAR21 := NVL(V_CONTREGAR21,0)+1;
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            ('2101'||LPAD(RTRIM(V_CONTREGAR21), 6, '0'), C_REG_21_CSLL.REG_21_CSLL, P_NRSEQARQMAG);
      END IF;
   END LOOP;
   RETURN V_CONTREGAR21;
END;
/