create or replace
FUNCTION ARQUIVO_DCTF_R15 (
      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_CONTREGAR15   NUMBER;
V_CDRECEITA    VARCHAR2(6);
BEGIN
     IF P_DBTOQTA = '0' THEN
         FOR C_REG_15_IRPJ IN( SELECT (RPAD('R15',3,' '))                                                                           ||
                                      LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)       ||
                                      TO_CHAR(P_DTINICIAL,'YYYYMM')                                                                 ||
                                      P_SITUACAO                                                                                    ||
                                      DECODE(P_SITUACAO,'0','00000000', TO_CHAR(P_DTINICIAL,'DDMMYYYY'))                            ||
                                      V_IRPJ                                                                                        ||
                                      CDRECEGUIA||CDVARIACAO                                                                        ||
                                      IDPERIODICREC                                                                                 ||
                                      TO_CHAR(P_DTINICIAL,'YYYY')                                                                   ||
                                      CASE WHEN IDPERIODICREC IN ('M','B','U','T','E') THEN TO_CHAR(P_DTINICIAL,'MM') ELSE '00' END ||
                                      CASE WHEN IDPERIODICREC IN ('D','S','X','Q')     THEN TO_CHAR(P_DTINICIAL,'DD') ELSE '00' END ||
                                      '000000'                                                                                      || /*APENAS IPI*/
                                      LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'-',''),'/',''),' ',''),14,0)       ||
                                      '0'                                                                                           ||
                                      NRPROCESSOGUIA                                                                                ||
                                      LPAD(LTRIM(REPLACE(TO_CHAR(VRPARCELA,'000000000000.00'),'.')),'14','0')                       ||
                                      RPAD(' ',10,' ')    AS REG_15_IRPJ                                                                                                                                       
                                    FROM (SELECT SUM(VRORIGPAG) AS VRPARCELA,E.NRINSJUREMPR, MP.CDVARIACAO , MP.IDPERIODICREC,  MP.CDRECEGUIA, MP.IDDEBSCPINC, G.NRPROCESSOGUIA, NRPARCELAPAG 
                                            FROM EMPRESA E , MAPAAPURAC MP, GUIACOMPLMAPA G, CONTAPAGAR CP
                                            WHERE MP.CDEMPRESA  = E.CDEMPRESA
                                            AND E.CDEMPRESA     = P_CDEMPRESA
                                            AND MP.DTINIPERIODO = P_DTINICIAL
                                            AND MP.DTFIMPERIODO = P_DTFINAL
                                            AND MP.CDIMPOSTO    = '25'
                                            AND MP.NRSEQUMAPAPU = G.NRSEQUMAPAPU(+)
                                            AND CP.DSCONTAPAG   = MP.NMMAPAAPUR
                                          GROUP BY E.NRINSJUREMPR, MP.CDVARIACAO , MP.IDPERIODICREC,  MP.CDRECEGUIA, MP.IDDEBSCPINC, G.NRPROCESSOGUIA, NRPARCELAPAG))LOOP
                     V_CONTREGAR15 := NVL(V_CONTREGAR15,0)+1;
              INSERT INTO ARQMAGNETICO(NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                     VALUES('04'||LPAD(RTRIM(V_CONTREGAR15), 8, '0'), C_REG_15_IRPJ.REG_15_IRPJ, P_NRSEQARQMAG);
         END LOOP;                                  
     END IF; 
  RETURN V_CONTREGAR15 ;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_DCTF_R15','5.03.034'); END;
/