CREATE OR REPLACE PROCEDURE ARQUIVO_DIRF(
   P_ANOREFERENCIA  IN LEIAUTEREFDIRF.CDANOREF%TYPE,
   P_ANOCALENDARIO  IN LEIAUTEREFDIRF.CDANOREF%TYPE,
   P_CDEMPRESA      IN EMPRESA.CDEMPRESA%TYPE,
   P_NRSEQARQMAG    IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_IDRETIFICADOR  IN NOTAFISCAL.IDENTRSAIDOP%TYPE,
   P_NRRECIBO       IN VARCHAR,
   P_CDREPRESENT    IN REPRESLEGAL.CDREPRES%TYPE,
   P_NATUDECLARANTE IN NOTAFISCAL.IDENTRSAIDOP%TYPE,
   P_IDSOCIO        IN NOTAFISCAL.IDENTRSAIDOP%TYPE,
   P_IDDEPCRED      IN NOTAFISCAL.IDENTRSAIDOP%TYPE,
   P_IDFUNINVEST    IN NOTAFISCAL.IDENTRSAIDOP%TYPE,
   P_IDRENDEXT      IN NOTAFISCAL.IDENTRSAIDOP%TYPE,
   P_IDPLANSAUDE    IN NOTAFISCAL.IDENTRSAIDOP%TYPE,
   P_IDPAGIMUISE    IN NOTAFISCAL.IDENTRSAIDOP%TYPE,
   P_IDFUNDPUBL     IN NOTAFISCAL.IDENTRSAIDOP%TYPE,
   P_IDSITUESPECIAL IN NOTAFISCAL.IDENTRSAIDOP%TYPE,
   P_DTEVENTO       IN NOTAFISCAL.DTENTRSAID%TYPE)
AS
   V_CONT           NUMBER;
   V_DTINICIAL      DATE;
   V_DTFINAL        DATE;
   V_CDRECEITA      VARCHAR2(04);
   V_IDREC          VARCHAR2(25);
   V_BPFDEC         VARCHAR2(150);
   V_BPJDEC         VARCHAR2(250);
   V_RTRT           VARCHAR2(200);
   V_RTIRF          VARCHAR2(200);
BEGIN
   /**** REGISTRO DE IDENTIFICACAO DA DECLARACAO (IDENTIFICADOR DIRF) ****/
   V_CONT := 0;
   FOR C_REGDIRF IN (SELECT 'Dirf'          ||'|'||
                            P_ANOREFERENCIA ||'|'||
                            P_ANOCALENDARIO ||'|'||
                            P_IDRETIFICADOR ||'|'||
                            P_NRRECIBO      ||'|'||
                            L.IDESTRUTURA   ||'|' AS REG
                       FROM LEIAUTEREFDIRF L
                      WHERE L.CDANOREF  = P_ANOREFERENCIA) LOOP
      V_CONT := NVL(V_CONT,0)+1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('01'||LPAD(RTRIM(V_CONT),8,'0'), C_REGDIRF.REG, P_NRSEQARQMAG);
   END LOOP;

   /**** REGISTRO DO RESPONSAVEL PELO PREENCHIMENTO DA DECLARACAO (IDENTIFICADOR RESPO) ****/
   FOR C_REGRESPO IN (SELECT 'RESPO'                                    ||'|'||
                             REPLACE(REPLACE(R.NRCPFREPRES,'.'),'-')    ||'|'||
                             SUBSTR(R.NMREPRES,1,60)                    ||'|'||
                             SUBSTR(LPAD(REPLACE(REPLACE(R.NRDDDTELEREPRES1,')'),'('),3,'0'),2,2)     ||'|'||
                             TRIM(SUBSTR(LPAD(REPLACE(REPLACE(R.NRTELEREPRES1,'-'),' '),12,' '),4,9)) ||'|'||
                             R.NRRAMALREPRES1                           ||'|'||
                             TRIM(SUBSTR(LPAD(REPLACE(REPLACE(REPLACE(R.NRFAXREPRES,')'),'('),'-'),12,' '),4,9)) ||'|'||
                             SUBSTR(R.DSEMAILREPRES,1,50)               ||'|' AS REG
                        FROM REPRESLEGAL R
                       WHERE R.CDREPRES = P_CDREPRESENT) LOOP
      V_CONT := NVL(V_CONT,0)+1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('01'||LPAD(RTRIM(V_CONT),8,'0'), C_REGRESPO.REG, P_NRSEQARQMAG);
   END LOOP;

   /****  REGISTRO DE IDENTIFICACAO DO DECLARANTE PESSOA JURIDICA (IDENTIFICADOR DECPJ) ****/
   FOR C_REGDECPJ IN (SELECT 'DECPJ'                                     ||'|'||
                             REPLACE(REPLACE(REPLACE(E.NRINSJUREMPR,'.'),'/'),'-') ||'|'||
                             RETIRA_CARACTER_ESPECIAL(E.NMRAZSOCEMPR,'S')||'|'||
                             P_NATUDECLARANTE                            ||'|'||
                             REPLACE(REPLACE(L.NRCPFREPRES,'.'),'-')     ||'|'||
                             P_IDSOCIO                                   ||'|'||
                             P_IDDEPCRED                                 ||'|'||
                             P_IDFUNINVEST                               ||'|'||
                             P_IDRENDEXT                                 ||'|'||
                             P_IDPLANSAUDE                               ||'|'||
                             P_IDPAGIMUISE                               ||'|'||
                             P_IDFUNDPUBL                                ||'|'||
                             P_IDSITUESPECIAL                            ||'|'||
                             DECODE(P_IDSITUESPECIAL,'S',TO_CHAR(P_DTEVENTO,'DDMMYYYY'),'') ||'|' AS REG
                        FROM EMPRESA E, REPRESEMPRESA  R, REPRESLEGAL L
                       WHERE E.CDEMPRESA = P_CDEMPRESA
                         AND E.CDEMPRESA = R.CDEMPRESA
                         AND R.IDATIVO   = 'S'
                         AND R.CDREPRES  = L.CDREPRES) LOOP
      V_CONT := NVL(V_CONT,0)+1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('01'||LPAD(RTRIM(V_CONT),8,'0'), C_REGDECPJ.REG, P_NRSEQARQMAG);
   END LOOP;

   V_DTINICIAL := '01/01/'||P_ANOCALENDARIO;
   IF P_ANOREFERENCIA = P_ANOCALENDARIO THEN
      V_DTFINAL   := P_DTEVENTO;
   ELSE
      V_DTFINAL   := '31/12/'||P_ANOCALENDARIO;
   END IF;

   V_CDRECEITA := NULL;
   FOR C_IMP IN (SELECT CDRECEITA, IDTPIJURFORN, NRINSJURFORN, NMRAZSOCFORN,
                        SUM(DECODE(DATA,'01',VRBASECALC,0)) AS BASE_JAN, SUM(DECODE(DATA,'02',VRBASECALC,0)) AS BASE_FEV,
                        SUM(DECODE(DATA,'03',VRBASECALC,0)) AS BASE_MAR, SUM(DECODE(DATA,'04',VRBASECALC,0)) AS BASE_ABR,
                        SUM(DECODE(DATA,'05',VRBASECALC,0)) AS BASE_MAI, SUM(DECODE(DATA,'06',VRBASECALC,0)) AS BASE_JUN,
                        SUM(DECODE(DATA,'07',VRBASECALC,0)) AS BASE_JUL, SUM(DECODE(DATA,'08',VRBASECALC,0)) AS BASE_AGO,
                        SUM(DECODE(DATA,'09',VRBASECALC,0)) AS BASE_SET, SUM(DECODE(DATA,'10',VRBASECALC,0)) AS BASE_OUT,
                        SUM(DECODE(DATA,'11',VRBASECALC,0)) AS BASE_NOV, SUM(DECODE(DATA,'12',VRBASECALC,0)) AS BASE_DEZ,
                        SUM(DECODE(DATA,'01',VRIMPOSTO,0)) AS IMP_JAN, SUM(DECODE(DATA,'02',VRIMPOSTO,0)) AS IMP_FEV,
                        SUM(DECODE(DATA,'03',VRIMPOSTO,0)) AS IMP_MAR, SUM(DECODE(DATA,'04',VRIMPOSTO,0)) AS IMP_ABR,
                        SUM(DECODE(DATA,'05',VRIMPOSTO,0)) AS IMP_MAI, SUM(DECODE(DATA,'06',VRIMPOSTO,0)) AS IMP_JUN,
                        SUM(DECODE(DATA,'07',VRIMPOSTO,0)) AS IMP_JUL, SUM(DECODE(DATA,'08',VRIMPOSTO,0)) AS IMP_AGO,
                        SUM(DECODE(DATA,'09',VRIMPOSTO,0)) AS IMP_SET, SUM(DECODE(DATA,'10',VRIMPOSTO,0)) AS IMP_OUT,
                        SUM(DECODE(DATA,'11',VRIMPOSTO,0)) AS IMP_NOV, SUM(DECODE(DATA,'12',VRIMPOSTO,0)) AS IMP_DEZ,
                        SUM(VRIMPOSTO) AS VRTOTIMP
                   FROM (SELECT DATA, SUBSTR(CDRECEDIRF,1,4) AS CDRECEITA, SUBSTR(CDRECEDIRF,5,1) AS IDGERADIRF,
                                IDTPIJURFORN, NRINSJURFORN, NMRAZSOCFORN, VRBASECALC, SUM(VRIMPOSTO) AS VRIMPOSTO,
                                CDFILIAL, NRLANCTONF
                           FROM (SELECT TO_CHAR(NF.DTENTRSAID,'MM') AS DATA, FO.NRINSJURFORN, FO.NMRAZSOCFORN, FO.IDTPIJURFORN,
                                        SUM(IP.VRCONTABILNF) AS VRBASECALC, SUM(IP.VRIMPOSTONF) AS VRIMPOSTO,
                                        RETORNA_COD_RECEITA(IP.CDIMPOSTO,'F',FO.CDFORNECED) AS CDRECEDIRF, IP.CDIMPOSTO,
                                        NF.CDFILIAL, NF.NRLANCTONF
                                   FROM MAPAAPURAC MP, NOTAFISCAL NF, FORNECEDOR FO, IMPOSTONF IP
                                  WHERE MP.CDIMPOSTO    IN ('55','65','75','24','21','22')
                                    AND MP.CDEMPRESA    = P_CDEMPRESA
                                    AND MP.DTINIPERIODO BETWEEN V_DTINICIAL AND V_DTFINAL
                                    AND MP.NRSEQUMAPAPU = IP.NRSEQUMAPAPU
                                    AND IP.NRLANCTONF   = NF.NRLANCTONF
                                    AND IP.CDFILIAL     = NF.CDFILIAL
                                    AND NF.CDFORNECED   = FO.CDFORNECED
                                    AND NF.IDORIGDESTNF = 'F'
                                  GROUP BY TO_CHAR(NF.DTENTRSAID,'MM'), FO.NRINSJURFORN, FO.NMRAZSOCFORN, FO.IDTPIJURFORN,
                                        RETORNA_COD_RECEITA(IP.CDIMPOSTO,'F',FO.CDFORNECED), IP.CDIMPOSTO, NF.CDFILIAL, NF.NRLANCTONF)
                          GROUP BY DATA, CDRECEDIRF, IDTPIJURFORN, NRINSJURFORN, NMRAZSOCFORN, VRBASECALC, CDFILIAL, NRLANCTONF)
                  WHERE IDGERADIRF = 'S'
                  GROUP BY CDRECEITA, IDTPIJURFORN, NRINSJURFORN, NMRAZSOCFORN
                  ORDER BY CDRECEITA, IDTPIJURFORN, NRINSJURFORN) LOOP

      IF C_IMP.VRTOTIMP > 0 THEN
         IF (V_CDRECEITA IS NULL) OR (V_CDRECEITA <> C_IMP.CDRECEITA) THEN
            V_IDREC := 'IDREC'         ||'|'||
                       C_IMP.CDRECEITA ||'|';

            V_CDRECEITA := C_IMP.CDRECEITA;
            V_CONT := NVL(V_CONT,0)+1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('01'||LPAD(RTRIM(V_CONT),8,'0'), V_IDREC, P_NRSEQARQMAG);
         END IF;

         IF C_IMP.IDTPIJURFORN = 'F' THEN
            V_BPFDEC := 'BPFDEC'   ||'|'||
                        REPLACE(REPLACE(REPLACE(C_IMP.NRINSJURFORN,'.'),'/'),'-')     ||'|'||
                        SUBSTR(RETIRA_CARACTER_ESPECIAL(C_IMP.NMRAZSOCFORN,'S'),1,60) ||'|'||
                        ''         ||'|'||
                        'N'        ||'|'||
                        'N'        ||'|';

            V_CONT := NVL(V_CONT,0)+1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('01'||LPAD(RTRIM(V_CONT),8,'0'), V_BPFDEC, P_NRSEQARQMAG);
         ELSE
            V_BPJDEC := 'BPJDEC'  ||'|'||
                        REPLACE(REPLACE(REPLACE(C_IMP.NRINSJURFORN,'.'),'/'),'-')      ||'|'||
                        SUBSTR(RETIRA_CARACTER_ESPECIAL(C_IMP.NMRAZSOCFORN,'S'),1,150) ||'|';

            V_CONT := NVL(V_CONT,0)+1;
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('01'||LPAD(RTRIM(V_CONT),8,'0'), V_BPJDEC, P_NRSEQARQMAG);
         END IF;

         V_RTRT  :=  'RTRT' ||'|'||
                     REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.BASE_JAN,2), '99999999990.00')), '.', '') ||'|'||
                     REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.BASE_FEV,2), '99999999990.00')), '.', '') ||'|'||
                     REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.BASE_MAR,2), '99999999990.00')), '.', '') ||'|'||
                     REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.BASE_ABR,2), '99999999990.00')), '.', '') ||'|'||
                     REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.BASE_MAI,2), '99999999990.00')), '.', '') ||'|'||
                     REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.BASE_JUN,2), '99999999990.00')), '.', '') ||'|'||
                     REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.BASE_JUL,2), '99999999990.00')), '.', '') ||'|'||
                     REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.BASE_AGO,2), '99999999990.00')), '.', '') ||'|'||
                     REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.BASE_SET,2), '99999999990.00')), '.', '') ||'|'||
                     REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.BASE_OUT,2), '99999999990.00')), '.', '') ||'|'||
                     REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.BASE_NOV,2), '99999999990.00')), '.', '') ||'|'||
                     REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.BASE_DEZ,2), '99999999990.00')), '.', '') ||'|'||
                     '000'  ||'|';
         V_CONT := NVL(V_CONT,0)+1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('01'||LPAD(RTRIM(V_CONT),8,'0'), V_RTRT, P_NRSEQARQMAG);

         V_RTIRF := 'RTIRF' ||'|'||
                    REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.IMP_JAN,2), '99999999990.00')), '.', '') ||'|'||
                    REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.IMP_FEV,2), '99999999990.00')), '.', '') ||'|'||
                    REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.IMP_MAR,2), '99999999990.00')), '.', '') ||'|'||
                    REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.IMP_ABR,2), '99999999990.00')), '.', '') ||'|'||
                    REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.IMP_MAI,2), '99999999990.00')), '.', '') ||'|'||
                    REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.IMP_JUN,2), '99999999990.00')), '.', '') ||'|'||
                    REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.IMP_JUL,2), '99999999990.00')), '.', '') ||'|'||
                    REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.IMP_AGO,2), '99999999990.00')), '.', '') ||'|'||
                    REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.IMP_SET,2), '99999999990.00')), '.', '') ||'|'||
                    REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.IMP_OUT,2), '99999999990.00')), '.', '') ||'|'||
                    REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.IMP_NOV,2), '99999999990.00')), '.', '') ||'|'||
                    REPLACE(LTRIM(TO_CHAR(ROUND(C_IMP.IMP_DEZ,2), '99999999990.00')), '.', '') ||'|'||
                    '000'  ||'|';
         V_CONT := NVL(V_CONT,0)+1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('01'||LPAD(RTRIM(V_CONT),8,'0'), V_RTIRF, P_NRSEQARQMAG);
      END IF;
   END LOOP;

   V_CONT := NVL(V_CONT,0)+1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('01'||LPAD(RTRIM(V_CONT),8,'0'), 'FIMDirf|', P_NRSEQARQMAG);
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_DIRF','6.39.014'); END;
/
