CREATE OR REPLACE PROCEDURE APURAC_INVESTROP(
   P_CDFILIAL       FILIAL.CDFILIAL%TYPE,
   P_FILIALPAR      FILIAL.CDFILIAL%TYPE,
   P_DTINICIAL      IN DATE,
   P_DTFINAL        IN DATE,
   P_NRROPFILIAL    IN VARCHAR)
IS
   V_IDAPUINVFILROP     CHAR;
   V_IDAPURAINVCTPAGROP CHAR;
   V_CDFILCENTPME       VARCHAR2(04);
   V_IDDTAPUCTPAGAR     VARCHAR2(30);
BEGIN
   BEGIN
      SELECT IDAPUINVFILROP, IDAPURAINVCTPAGROP, IDDTAPUCTPAGAR
        INTO V_IDAPUINVFILROP, V_IDAPURAINVCTPAGROP, V_IDDTAPUCTPAGAR
        FROM PARFILIALROP P
       WHERE P.CDFILIAL = P_FILIALPAR;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN BEGIN
         V_IDAPUINVFILROP := NULL;
         V_IDAPURAINVCTPAGROP := 'N';
         V_IDDTAPUCTPAGAR := 'VENCIMENTO';
      END;
   END;

   BEGIN
     SELECT CASE WHEN IDFILCENTPME = 'S'
                 THEN CDFILIAL
                 ELSE CDFILCENTPME
            END
       INTO V_CDFILCENTPME
       FROM FILIAL
      WHERE CDFILIAL = P_CDFILIAL;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       V_CDFILCENTPME := NULL;
   END;

   IF V_IDAPUINVFILROP = 'S' THEN
      FOR C_LANCINVEFILI IN (SELECT CDINVEST, DTINVEFILI, VRINVEFILI, CDFILIAL
                               FROM LANCINVEFILI
                              WHERE CDFILIAL = P_CDFILIAL                         
                                AND TRUNC(DTINVEFILI) = P_DTINICIAL) LOOP

         INSERT INTO INVESTIMENTOROP
                    (NRFILIALROP, NRINVESTIMENTOROP, IDTPLANCROP, DTINVESTIMENTO, DTLANCAMENTO,
                     CDFILCENTPME, CDINVEST, QTINVEST, VRINVEST, CDFILIALNF,
                     NRLANCTONF, NRSEQVENPAG, NRPARCELAPAG)
              VALUES
                    (P_NRROPFILIAL, SEQ_INVESTIMENTOROP.NEXTVAL, 'INVESTIMENTO', C_LANCINVEFILI.DTINVEFILI, SYSDATE,
                     V_CDFILCENTPME, C_LANCINVEFILI.CDINVEST, 1, C_LANCINVEFILI.VRINVEFILI, NULL,
                     NULL, NULL, NULL);
      END LOOP;
      COMMIT;
   END IF;   
    
   IF V_IDAPURAINVCTPAGROP = 'S' THEN
      FOR C_CTPAGINV IN (SELECT CP.IDSTATPAG, CP.CDFILIBPAG, CP.CDFILIAL,CP.NRLANCTONF,CP.NRSEQVENPAG, CP.NRPARCELAPAG,
                                TC.CDTPCTPAGAR,
                                CASE WHEN NVL(TC.IDDTAPUCTPAGAR,V_IDDTAPUCTPAGAR) = 'ENTRADA' THEN NF.DTENTRSAID
                                     WHEN NVL(TC.IDDTAPUCTPAGAR,V_IDDTAPUCTPAGAR) = 'VENCIMENTO' THEN CP.DTATUAVENPAG
                                     ELSE BP.DTBAIXAPAG
                                END DTBAIXAPAG, NVL(VRRATCCCTPAG,CP.VRATUAPAG) AS VRCONTARATEADA
                           FROM CONTAPAGAR CP INNER JOIN NOTAFISCAL NF ON NF.CDFILIAL = CP.CDFILIAL
                                                                      AND NF.NRLANCTONF = CP.NRLANCTONF
                                              INNER JOIN BOLETPAGAR BO ON CP.CDFILIBPAG = BO.CDFILIAL
                                                                      AND CP.NRLANBOLPAG = BO.NRLANBOLPAG
                                              LEFT  JOIN BAIXAPAGA BP ON BO.CDFILIBAIPAG = BP.CDFILIAL
                                                                     AND BO.NRBAIXAPAG = BP.NRBAIXAPAG
                                              INNER JOIN TIPOCTPAGROP TC ON TC.CDTPCTPAGAR = CP.CDTPCTPAGAR
                                              LEFT  JOIN RATCTPAGAR R ON CP.CDFILIAL = R.CDFILIAL
                                                                     AND CP.NRLANCTONF = R.NRLANCTONF
                                                                     AND CP.NRSEQVENPAG = R.NRSEQVENPAG
                                                                     AND CP.NRPARCELAPAG = R.NRPARCELAPAG
                          WHERE ((('ENTRADA' = NVL(TC.IDDTAPUCTPAGAR,V_IDDTAPUCTPAGAR)) AND (NF.DTENTRSAID = P_DTINICIAL)) OR
                               (('VENCIMENTO' = NVL(TC.IDDTAPUCTPAGAR,V_IDDTAPUCTPAGAR)) AND (CP.DTATUAVENPAG = P_DTINICIAL)) OR
                               (('BAIXA' = NVL(TC.IDDTAPUCTPAGAR,V_IDDTAPUCTPAGAR)) AND (BP.DTBAIXAPAG = P_DTINICIAL)))
                            AND TC.CDFILIAL = P_FILIALPAR
                            AND TC.IDTPCTPAGROP = 'INV'
                            AND ((CDFIRATCTPAG = P_CDFILIAL) OR (CDFIRATCTPAG IS NULL) AND (NF.CDFILIRECE = P_CDFILIAL))) LOOP

         INSERT INTO INVESTIMENTOROP
                    (NRFILIALROP, NRINVESTIMENTOROP, IDTPLANCROP, DTINVESTIMENTO, DTLANCAMENTO,
                     CDFILCENTPME, CDINVEST, QTINVEST, VRINVEST, CDFILIALNF,
                     NRLANCTONF, NRSEQVENPAG, NRPARCELAPAG)
              VALUES
                    (P_NRROPFILIAL, SEQ_INVESTIMENTOROP.NEXTVAL, 'INVESTIMENTO', C_CTPAGINV.DTBAIXAPAG, SYSDATE,
                     V_CDFILCENTPME, C_CTPAGINV.CDTPCTPAGAR, 1, C_CTPAGINV.VRCONTARATEADA, C_CTPAGINV.CDFILIAL,
                     C_CTPAGINV.NRLANCTONF, C_CTPAGINV.NRSEQVENPAG, C_CTPAGINV.NRPARCELAPAG);
      END LOOP;
      COMMIT;
   END IF;
END;
/

