CREATE OR REPLACE PROCEDURE REINF_R2060(
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_NRENVIO      IN NUMBER,
   P_NREVENTO     IN NUMBER,
   P_NRRECIBO     IN VARCHAR2,
   P_INDRETIF     IN VARCHAR2,
   P_IDAMBIENTE   IN VARCHAR2,
   P_CDATIVIDADE  IN VARCHAR2 DEFAULT '00000025',
   P_VRALIQUOTA   IN FLOAT DEFAULT 4.5)
AS
   V_NRSEQREINF      NUMBER;
   V_VREXPORTACAO    NOTAFISCAL.VRNOTAFISC%TYPE;
   V_CDATIVIDADE     VARCHAR2(08);
   V_VRALIQUOTA      FLOAT;
BEGIN
   BEGIN
     SELECT NVL(SUM(N.VRNOTAFISC),0)
       INTO V_VREXPORTACAO
       FROM ENDECLIE E, NOTAFISCAL N
      WHERE E.CDPAIS <> '0055'
        AND E.IDTPENDECLIE = 'P'
        AND E.CDCLIENTE = N.CDCLIENTE
        AND (N.IDSISTEMA NOT IN ('REC','FIS') OR N.IDSISTEMA IS NULL)
        AND N.DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL
        AND N.CDEMPRESA = P_CDEMPRESA
        AND N.IDENTRSAIDOP = 'S'
        AND N.IDSTATUS IN ('E','A','C');
   EXCEPTION
        WHEN NO_DATA_FOUND THEN
        V_VREXPORTACAO := 0;
   END;

   V_CDATIVIDADE := NVL(P_CDATIVIDADE,'00000025');
   V_VRALIQUOTA  := NVL(P_VRALIQUOTA,4.5);

   FOR C_2060 IN (SELECT P_INDRETIF AS INDRETIF,
                         P_NRRECIBO AS NRRECIBO,
                         TO_CHAR(TO_DATE(P_DTINICIAL),'YYYY-MM')  AS PERAPUR,
                         P_IDAMBIENTE AS TPAMB,
                         '1' AS PROCEMI,
                         '1.5.1'  AS VERPROC,
                         DECODE(IDTPIJUREMPR,'J','1','F','2',' ') AS TPINSC,
                         DECODE(IDTPIJUREMPR,'J',SUBSTR(NRINSJUREMPR,1,8), 'F', NRINSJUREMPR) AS NRINSC,
                         '1' AS TPINSCESTAB,
                         NRINSJUREMPR AS NRINSCESTAB,
                         NVL(VRTOTABRUT,0) + NVL(V_VREXPORTACAO,0) - NVL(VRENCARGO,0) AS VLRRECBRUTATOTAL,
                         ROUND((NVL(VRTOTABRUT,0)-NVL(VRENCARGO,0))*(P_VRALIQUOTA/100),2) AS VLRCPAPURTOTAL,
                         0 AS VLRCPRBSUSPTOTAL,
                         P_CDATIVIDADE AS CODATIVECON,
                         NVL(VRTOTABRUT,0) - NVL(VRENCARGO,0) + NVL(V_VREXPORTACAO,0) AS VLRRECBRUTAATIV,
                         NVL(V_VREXPORTACAO,0) AS VLREXCRECBRUTA,
                         0 AS VLRADICRECBRUTA,
                         NVL(VRTOTABRUT,0) - NVL(VRENCARGO,0) AS VLRBCCPRB,
                         ROUND((NVL(VRTOTABRUT,0)-NVL(VRENCARGO,0))*(P_VRALIQUOTA/100),2) AS VLRCPRBAPUR,
                         '0' AS TPAJUSTE,
                         '11' AS CODAJUSTE,
                          NVL(V_VREXPORTACAO,0) AS VLRAJUSTE,
                         'Servicos exterior' AS DESCAJUSTE ,
                         TO_CHAR(TO_DATE(P_DTFINAL),'YYYY-MM') AS DTAJUSTE
                    FROM (SELECT SUM(VRTOTABRUT) AS VRTOTABRUT, SUM(VRENCARGO) AS VRENCARGO,
                                 IDTPIJUREMPR, NRINSJUREMPR
                            FROM (SELECT SUM(NVL(VRNOTAFISC,0)) AS VRTOTABRUT,
                                         SUM((SELECT NVL(SUM(VRITEMENCA),0)
                                                FROM ENCARGO E, ITEMENCARGO ITE
                                               WHERE ITE.CDENCARGO = E.CDENCARGO
                                                 AND N.CDFILIAL    = ITE.CDFILIAL
                                                 AND N.NRLANCTONF  = ITE.NRLANCTONF
                                                 AND E.IDENCARGO   = 'A')) AS VRENCARGO,
                                         E.IDTPIJUREMPR, E.NRINSJUREMPR
                                    FROM NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM, EMPRESA E, ENDECLIE EC
                                   WHERE N.IDENTRSAIDOP = 'S'
                                     AND N.IDSTATUS IN ('E','A','C')
                                     AND (N.IDSISTEMA NOT IN ('REC','FIS') OR N.IDSISTEMA IS NULL)
                                     AND N.DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL
                                     AND N.CDEMPRESA = P_CDEMPRESA
                                     AND N.CDFILIAL  = I.CDFILIAL
                                     AND N.NRLANCTONF = I.NRLANCTONF
                                     AND I.NRSEQUMAPAPU IS NOT NULL
                                     AND I.CDIMPOSTO = IM.CDIMPOSTO
                                     AND IM.CDIMPOSTONFE = '50'
                                     AND E.CDEMPRESA  = N.CDEMPRESA
                                     AND N.CDCLIENTE  = EC.CDCLIENTE
                                     AND EC.CDPAIS    = '0055'
                                     AND EC.IDTPENDECLIE = 'P'
                                   GROUP BY E.IDTPIJUREMPR, E.NRINSJUREMPR
                                   UNION ALL
                                  SELECT 0 AS VRTOTABRUT,
                                         0 AS VRENCARGO,
                                         E.IDTPIJUREMPR, E.NRINSJUREMPR
                                    FROM EMPRESA E
                                   WHERE E.CDEMPRESA = P_CDEMPRESA)
                           GROUP BY IDTPIJUREMPR, NRINSJUREMPR)) LOOP

                  NOVO_CODIGO_NT('NRSEQREINF', V_NRSEQREINF);

                  INSERT INTO ARQEFD_REINF
                         ( CDEMPRESA, NREVENTO, NRENVIO, CDCAMPO1, CDCAMPO2,
                           CDCAMPO3, CDCAMPO4, CDCAMPO5, CDCAMPO6, CDCAMPO7,
                           CDCAMPO8, CDCAMPO9, CDCAMPO10, VRCAMPO1,
                           VRCAMPO2, VRCAMPO3, CDCAMPO11,
                           VRCAMPO4, VRCAMPO5, VRCAMPO6,
                           VRCAMPO7, VRCAMPO8, CDCAMPO12,
                           CDCAMPO13, VRCAMPO9, CDCAMPO14, CDCAMPO15,  NRSEQREINF)

                  VALUES ( P_CDEMPRESA, P_NREVENTO, P_NRENVIO, C_2060.INDRETIF, C_2060.NRRECIBO,
                           C_2060.PERAPUR, C_2060.TPAMB, C_2060.PROCEMI, C_2060.VERPROC, C_2060.TPINSC,
                           C_2060.NRINSC, C_2060.TPINSCESTAB, C_2060.NRINSCESTAB, C_2060.VLRRECBRUTATOTAL,
                           C_2060.VLRCPAPURTOTAL, C_2060.VLRCPRBSUSPTOTAL, C_2060.CODATIVECON,
                           C_2060.VLRRECBRUTAATIV, C_2060.VLREXCRECBRUTA, C_2060.VLRADICRECBRUTA,
                           C_2060.VLRBCCPRB, C_2060.VLRCPRBAPUR, C_2060.TPAJUSTE,
                           C_2060.CODAJUSTE,C_2060.VLRAJUSTE, C_2060.DESCAJUSTE,C_2060.DTAJUSTE, V_NRSEQREINF);
   END LOOP;
END;