CREATE OR REPLACE FUNCTION ARQUIVO_DCTF_R20 (
   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_BALNCRED     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_CONTREGAR20   NUMBER;
   V_CDRECEITA    VARCHAR2(6);
BEGIN
   FOR C_REG_20_IRPJ IN (SELECT (RPAD('R20',3,' '))                                                                           ||/*REGISTRO*/
                                LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)       ||/*CNPJ DA EMPRESA*/
                                TO_CHAR(TO_DATE(P_DTINICIAL),'YYYYMM')                                                        ||/*MES E ANO DO FATO GERADOR*/
                                P_SITUACAO                                                                                    ||/*SITUACAO*/
                                DECODE(P_SITUACAO,'0',RPAD('0',8,'0'), TO_CHAR(TO_DATE(P_DTINICIAL),'YYYYMMDD'))              ||/*DATA DO EVENTO*/
                                V_IRPJ                                                                                        ||/*IMPOSTO*/
                                NVL(NVL(CDRECEITACONTR,CDRECEGUIA)||CDVARIACAO,'000000')                                      ||/*CODIGO DA RECEITA/VARIACAO*/
                                NVL(IDPERIODICREC,'T')                                                                        ||/*PERIODICIDADE*/
                                TO_CHAR(DTFIMPERIODO,'YYYY')                                                                  ||/*ANO DO FATO GERADOR*/
                                DECODE(TO_CHAR(DTFIMPERIODO,'MM'),'03','01',DECODE(TO_CHAR(DTFIMPERIODO,'MM'),'06','02',DECODE(TO_CHAR(DTFIMPERIODO,'MM'),'09','03','04')))||/*TRIMESTRE*/
                                '00'                                                                                          ||/*RESERVADO*/
                                RPAD('0',6,'0')                                                                               ||/*RESERVADO*/
                                RPAD('0',14,'0')                                                                              ||/*RESERVADO*/
                                '0'                                                                                           ||/*RESERVADO*/
                                LPAD(LTRIM(REPLACE(TO_CHAR(VRDEBITO,'000000000000.00'),'.')),'14','0')                        ||/*DEBITO*/
                                '0'                                                                                           ||/*RESERVADO*/
                                '0'                                                                                           ||/*RESERVADO*/
                                CASE WHEN VRDEBITO < 3000.00 THEN '2' ELSE '3' END                                            ||/*QUANTIDADE DE QUOTAS*/
                                IDDEBSCPINC                                                                                   ||/*DEBITO DE SCP/INC*/
                                RPAD('0',10,'0')  AS  REG_20_IRPJ
                           FROM (SELECT SUM(ROUND(VRTOTAIMPOS,2)) AS VRDEBITO, E.NRINSJUREMPR, MP.CDIMPOSTO,
                                        CDRECEITACONTR, DTVENCGUIA, CDVARIACAO, IDPERIODICREC, IDDEBSCPINC,
                                        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, MP.CDIMPOSTO, CDRECEITACONTR, DTVENCGUIA,
                                        CDVARIACAO, IDPERIODICREC, IDDEBSCPINC, CDRECEGUIA)
                           WHERE VRDEBITO > 0 )LOOP
      V_CONTREGAR20 := NVL(V_CONTREGAR20,0)+1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('2040'||LPAD(RTRIM(V_CONTREGAR20), 6, '0'), C_REG_20_IRPJ.REG_20_IRPJ, P_NRSEQARQMAG);
   END LOOP;

   FOR C_REG_20_CSLL IN (SELECT (RPAD('R20',3,' '))                                                                           ||/*REGISTRO*/
                                LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)       ||/*CNPJ DA EMPRESA*/
                                TO_CHAR(TO_DATE(P_DTINICIAL),'YYYYMM')                                                        ||/*MES E ANO DO FATO GERADOR*/
                                P_SITUACAO                                                                                    ||/*SITUACAO*/
                                DECODE(P_SITUACAO,'0',RPAD('0',8,'0'), TO_CHAR(TO_DATE(P_DTINICIAL),'YYYYMMDD'))              ||/*DATA DO EVENTO*/
                                V_CSLL                                                                                        ||/*IMPOSTO*/
                                NVL(NVL(CDRECEITACONTR,CDRECEGUIA)||CDVARIACAO,'000000')                                      ||/*CODIGO DA RECEITA/VARIACAO*/
                                NVL(IDPERIODICREC,'T')                                                                        ||/*PERIODICIDADE*/
                                TO_CHAR(DTFIMPERIODO,'YYYY')                                                                  ||/*ANO DO FATO GERADOR*/
                                DECODE(TO_CHAR(DTFIMPERIODO,'MM'),'03','01',DECODE(TO_CHAR(DTFIMPERIODO,'MM'),'06','02',DECODE(TO_CHAR(DTFIMPERIODO,'MM'),'09','03','04')))||/*TRIMESTRE*/
                                '00'                                                                                          ||/*RESERVADO*/
                                RPAD('0',6,'0')                                                                               ||/*RESERVADO*/
                                RPAD('0',14,'0')                                                                              ||/*RESERVADO*/
                                '0'                                                                                           ||/*RESERVADO*/
                                LPAD(LTRIM(REPLACE(TO_CHAR(VRDEBITO,'000000000000.00'),'.')),'14','0')                        ||/*DEBITO*/
                                '0'                                                                                           ||/*RESERVADO*/
                                '0'                                                                                           ||/*RESERVADO*/
                                CASE WHEN VRDEBITO < 3000.00 THEN '2' ELSE '3' END                                            ||/*QUANTIDADE DE QUOTAS*/
                                IDDEBSCPINC                                                                                   ||/*DEBITO DE SCP/INC*/
                                RPAD('0',10,'0')  AS  REG_20_CSLL
                           FROM (SELECT SUM(ROUND(VRTOTAIMPOS,2)) AS VRDEBITO, E.NRINSJUREMPR, MP.CDIMPOSTO,
                                        CDRECEITACONTR, DTVENCGUIA, CDVARIACAO, IDPERIODICREC, IDDEBSCPINC,
                                        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, MP.CDIMPOSTO, CDRECEITACONTR, DTVENCGUIA,
                                         CDVARIACAO, IDPERIODICREC, IDDEBSCPINC, CDRECEGUIA)
                           WHERE VRDEBITO > 0 )LOOP
      V_CONTREGAR20 := NVL(V_CONTREGAR20,0)+1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('2100'||LPAD(RTRIM(V_CONTREGAR20), 6, '0'), C_REG_20_CSLL.REG_20_CSLL, P_NRSEQARQMAG);
   END LOOP;
   RETURN V_CONTREGAR20 ;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_DCTF_R20','6.45.039'); END;
/
