DROP TYPE FCT_BUSCA_INDICEREAJUSTE_TABLE
/

DROP TYPE FCT_BUSCA_INDICEREAJUSTE_TYPE
/

CREATE OR REPLACE TYPE FCT_BUSCA_INDICEREAJUSTE_TYPE AS OBJECT
   (NRINDICE NUMBER,
    NRSEQCONTRATO NUMBER,
    NRCONTRATOFCT VARCHAR2(30),
    NRPARCNEGOCIO NUMBER,
    NMPRINCIPALPARC VARCHAR2(250),
    CDTPINDICE VARCHAR2(1),
    IDPERVALOR VARCHAR2(1),
    CDINDICE   VARCHAR2(4),
    NMINDICE   VARCHAR2(30),
    NRPERCENTINDICE NUMBER,
    VRINDICE NUMBER,
    NRORG NUMBER,
    DTINCLUSAO DATE);
/

CREATE OR REPLACE TYPE FCT_BUSCA_INDICEREAJUSTE_TABLE AS TABLE OF FCT_BUSCA_INDICEREAJUSTE_TYPE;
/


/*===-  Criacao da Function FCT_BUSCA_INDICEREAJUSTE  -===*/
CREATE OR REPLACE FUNCTION FCT_BUSCA_INDICEREAJUSTE(P_NRSEQCONTRATO IN VARCHAR2,
                                                    P_NRORG IN FCT_CONTRATO.NRORG%TYPE,
                                                    P_VRPERCENTUAL IN FLOAT DEFAULT NULL)
RETURN FCT_BUSCA_INDICEREAJUSTE_TABLE PIPELINED
IS
OUT_RECORD FCT_BUSCA_INDICEREAJUSTE_TYPE := FCT_BUSCA_INDICEREAJUSTE_TYPE (NULL, NULL, NULL, NULL, NULL,
                                                                           NULL, NULL, NULL, NULL, NULL,
                                                                           NULL, NULL, NULL);
V_NRCONTRATOFCT    VARCHAR2(30);
V_NRPARCNEGOCIO    NUMBER;
V_NRTIPOORCAMENTO  NUMBER;
V_NMPRINCIPALPARC  VARCHAR2(250);
V_INDICE           NUMBER;
V_CDTPINDICE       FCT_INDICESPADRCONTR.CDTPINDICE%TYPE;
V_IDPERVALOR       FCT_INDICESPADRCONTR.IDPERVALOR%TYPE;
V_CDINDICE         FCT_INDICESPADRCONTR.CDINDICE%TYPE;
V_NMINDICE         INDICE.NMINDICE%TYPE;
V_NRPERCENTINDICE  FCT_INDICESPADRCONTR.NRPERCENTINDICE%TYPE;
V_VRINDICE         NUMBER;
V_DTFINVIGENCONT   FCT_CONTRATO.DTFINVIGENCONT%TYPE;
V_DTREFREAJUSTE    DATE;
V_DTPADREAJCONT    DATE;

BEGIN
  FOR C_INDICE IN (SELECT NRSEQCONTRATO
                     FROM FCT_CONTRATO
                    WHERE ((INSTR(P_NRSEQCONTRATO, '_' || TO_CHAR(NRSEQCONTRATO) || '_') > 0) OR (P_NRSEQCONTRATO IS NULL))
                      AND NRORG = P_NRORG

  ) LOOP

    SELECT NRCONTRATOFCT, NRPARCNEGOCIO, NRTIPOORCAMENTO, TRUNC(NVL(DTFINVIGENCONT, DTFIMCONTRATO)) AS DTFINVIGENCONT, DTPADREAJCONT
      INTO V_NRCONTRATOFCT, V_NRPARCNEGOCIO, V_NRTIPOORCAMENTO, V_DTFINVIGENCONT, V_DTPADREAJCONT
      FROM FCT_CONTRATO
     WHERE NRSEQCONTRATO = C_INDICE.NRSEQCONTRATO
       AND NRORG = P_NRORG;

    V_DTREFREAJUSTE := ADD_MONTHS(NVL(V_DTPADREAJCONT, V_DTFINVIGENCONT+1), -1);

    IF (V_NRTIPOORCAMENTO IS NULL) THEN

      SELECT NRTIPOORCAMENTO
        INTO V_NRTIPOORCAMENTO
        FROM FCT_REFEORCACONT
       WHERE NRSEQCONTRATO = C_INDICE.NRSEQCONTRATO
         AND NRORG = P_NRORG;

    END IF;

    IF (V_NRPARCNEGOCIO IS NOT NULL) THEN

      SELECT NMPRINCIPALPARC
        INTO V_NMPRINCIPALPARC
        FROM PARCNEGOCIO
       WHERE NRPARCNEGOCIO = V_NRPARCNEGOCIO
         AND NRORG = P_NRORG;

    END IF;

    IF (P_VRPERCENTUAL IS NOT NULL) THEN
        
        OUT_RECORD.NRINDICE        := 1;
        OUT_RECORD.NRSEQCONTRATO   := C_INDICE.NRSEQCONTRATO;
        OUT_RECORD.NRCONTRATOFCT   := V_NRCONTRATOFCT;
        OUT_RECORD.NRPARCNEGOCIO   := V_NRPARCNEGOCIO;
        OUT_RECORD.NMPRINCIPALPARC := V_NMPRINCIPALPARC;
        OUT_RECORD.CDTPINDICE      := NULL;
        OUT_RECORD.IDPERVALOR      := NULL;
        OUT_RECORD.CDINDICE        := NULL;
        OUT_RECORD.NMINDICE        := 'Percentual Fixo';
        OUT_RECORD.NRPERCENTINDICE := 100;
        OUT_RECORD.VRINDICE        := P_VRPERCENTUAL;
        OUT_RECORD.NRORG           := P_NRORG;
        OUT_RECORD.DTINCLUSAO      := SYSDATE;
        
        PIPE ROW (OUT_RECORD);
        
        CONTINUE;
        
    END IF;

    V_INDICE := 0;

    FOR C_INDPADCONTR IN (SELECT FCT_INDICESPADRCONTR.NRINDICESPADRCONTR, FCT_INDICESPADRCONTR.CDTPINDICE, FCT_INDICESPADRCONTR.IDPERVALOR,
                                 FCT_INDICESPADRCONTR.CDINDICE, INDICE.NMINDICE, FCT_INDICESPADRCONTR.NRPERCENTINDICE
                            FROM FCT_INDICESPADRCONTR
                            JOIN INDICE
                              ON INDICE.CDTPINDICE = FCT_INDICESPADRCONTR.CDTPINDICE
                             AND INDICE.IDPERVALOR = FCT_INDICESPADRCONTR.IDPERVALOR
                             AND INDICE.CDINDICE = FCT_INDICESPADRCONTR.CDINDICE
                             AND INDICE.NRORG = FCT_INDICESPADRCONTR.NRORG
                           WHERE NRSEQCONTRATO =  C_INDICE.NRSEQCONTRATO
                             AND FCT_INDICESPADRCONTR.NRORG = P_NRORG) LOOP

        V_INDICE := V_INDICE+1;

        /* Falta calcular o valor do indice */

        V_VRINDICE := 1;

        FOR C_VRINDICE IN (SELECT CDTPINDICE, IDPERVALOR, CDINDICE, DTREFIND, VRINDICE
                             FROM MOVINDICE
                            WHERE TRUNC(DTREFIND) BETWEEN ADD_MONTHS(FIRST_DAY(V_DTREFREAJUSTE), -11) AND LAST_DAY(V_DTREFREAJUSTE)
                              AND CDTPINDICE = C_INDPADCONTR.CDTPINDICE
                              AND IDPERVALOR = C_INDPADCONTR.IDPERVALOR
                              AND CDINDICE = C_INDPADCONTR.CDINDICE
                              AND NRORG = P_NRORG) LOOP

           V_VRINDICE := V_VRINDICE * (1 + (C_VRINDICE.VRINDICE/100));

        END LOOP;

        V_VRINDICE := V_VRINDICE-1;

        V_VRINDICE := ROUND(V_VRINDICE*100,4);

        OUT_RECORD.NRINDICE        := V_INDICE;
        OUT_RECORD.NRSEQCONTRATO   := C_INDICE.NRSEQCONTRATO;
        OUT_RECORD.NRCONTRATOFCT   := V_NRCONTRATOFCT;
        OUT_RECORD.NRPARCNEGOCIO   := V_NRPARCNEGOCIO;
        OUT_RECORD.NMPRINCIPALPARC := V_NMPRINCIPALPARC;
        OUT_RECORD.CDTPINDICE      := C_INDPADCONTR.CDTPINDICE;
        OUT_RECORD.IDPERVALOR      := C_INDPADCONTR.IDPERVALOR;
        OUT_RECORD.CDINDICE        := C_INDPADCONTR.CDINDICE;
        OUT_RECORD.NMINDICE        := C_INDPADCONTR.NMINDICE;
        OUT_RECORD.NRPERCENTINDICE := C_INDPADCONTR.NRPERCENTINDICE;
        OUT_RECORD.VRINDICE        := V_VRINDICE;
        OUT_RECORD.NRORG           := P_NRORG;
        OUT_RECORD.DTINCLUSAO      := SYSDATE;

        PIPE ROW (OUT_RECORD);

    END LOOP;

    IF (V_INDICE = 0) THEN

      FOR C_INDPADPARC IN (SELECT FCT_INDICESPADRPARC.NRINDICESPADRPARC, FCT_INDICESPADRPARC.CDTPINDICE, FCT_INDICESPADRPARC.IDPERVALOR,
                                  FCT_INDICESPADRPARC.CDINDICE, INDICE.NMINDICE, FCT_INDICESPADRPARC.NRPERCENTINDICE
                             FROM FCT_INDICESPADRPARC
                             JOIN INDICE
                               ON INDICE.CDTPINDICE = FCT_INDICESPADRPARC.CDTPINDICE
                              AND INDICE.IDPERVALOR = FCT_INDICESPADRPARC.IDPERVALOR
                              AND INDICE.CDINDICE = FCT_INDICESPADRPARC.CDINDICE
                              AND INDICE.NRORG = FCT_INDICESPADRPARC.NRORG
                            WHERE NRPARCNEGOCIO =  V_NRPARCNEGOCIO
                              AND FCT_INDICESPADRPARC.NRORG = P_NRORG) LOOP

          V_INDICE := V_INDICE+1;

          V_VRINDICE := 1;

          FOR C_VRINDICE IN (SELECT CDTPINDICE, IDPERVALOR, CDINDICE, DTREFIND, VRINDICE
                               FROM MOVINDICE
                              WHERE TRUNC(DTREFIND) BETWEEN ADD_MONTHS(FIRST_DAY(V_DTREFREAJUSTE), -11) AND LAST_DAY(V_DTREFREAJUSTE)
                                AND CDTPINDICE = C_INDPADPARC.CDTPINDICE
                                AND IDPERVALOR = C_INDPADPARC.IDPERVALOR
                                AND CDINDICE = C_INDPADPARC.CDINDICE
                                AND NRORG = P_NRORG) LOOP

             V_VRINDICE := V_VRINDICE * (1 + (C_VRINDICE.VRINDICE/100));

          END LOOP;

          V_VRINDICE := V_VRINDICE-1;

          V_VRINDICE := ROUND(V_VRINDICE*100,4);

          OUT_RECORD.NRINDICE        := V_INDICE;
          OUT_RECORD.NRSEQCONTRATO   := C_INDICE.NRSEQCONTRATO;
          OUT_RECORD.NRCONTRATOFCT   := V_NRCONTRATOFCT;
          OUT_RECORD.NRPARCNEGOCIO   := V_NRPARCNEGOCIO;
          OUT_RECORD.NMPRINCIPALPARC := V_NMPRINCIPALPARC;
          OUT_RECORD.CDTPINDICE      := C_INDPADPARC.CDTPINDICE;
          OUT_RECORD.IDPERVALOR      := C_INDPADPARC.IDPERVALOR;
          OUT_RECORD.CDINDICE        := C_INDPADPARC.CDINDICE;
          OUT_RECORD.NMINDICE        := C_INDPADPARC.NMINDICE;
          OUT_RECORD.NRPERCENTINDICE := C_INDPADPARC.NRPERCENTINDICE;
          OUT_RECORD.VRINDICE        := V_VRINDICE;
          OUT_RECORD.NRORG           := P_NRORG;
          OUT_RECORD.DTINCLUSAO      := SYSDATE;

          PIPE ROW (OUT_RECORD);

      END LOOP;

    END IF;

    IF (V_INDICE = 0) THEN

      FOR C_INDPADESC IN (SELECT FCT_INDICESPADRESCOPO.NRINDICESPADRESCOPO, FCT_INDICESPADRESCOPO.CDTPINDICE, FCT_INDICESPADRESCOPO.IDPERVALOR,
                                 FCT_INDICESPADRESCOPO.CDINDICE, INDICE.NMINDICE, FCT_INDICESPADRESCOPO.NRPERCENTINDICE
                            FROM FCT_INDICESPADRESCOPO
                            JOIN INDICE
                              ON INDICE.CDTPINDICE = FCT_INDICESPADRESCOPO.CDTPINDICE
                             AND INDICE.IDPERVALOR = FCT_INDICESPADRESCOPO.IDPERVALOR
                             AND INDICE.CDINDICE = FCT_INDICESPADRESCOPO.CDINDICE
                             AND INDICE.NRORG = FCT_INDICESPADRESCOPO.NRORG
                           WHERE NRTIPOORCAMENTO =  V_NRTIPOORCAMENTO
                             AND FCT_INDICESPADRESCOPO.NRORG = P_NRORG) LOOP

          V_INDICE := V_INDICE+1;

          V_VRINDICE := 1;

          FOR C_VRINDICE IN (SELECT CDTPINDICE, IDPERVALOR, CDINDICE, DTREFIND, VRINDICE
                               FROM MOVINDICE
                              WHERE TRUNC(DTREFIND) BETWEEN ADD_MONTHS(FIRST_DAY(V_DTREFREAJUSTE), -11) AND LAST_DAY(V_DTREFREAJUSTE)
                                AND CDTPINDICE = C_INDPADESC.CDTPINDICE
                                AND IDPERVALOR = C_INDPADESC.IDPERVALOR
                                AND CDINDICE = C_INDPADESC.CDINDICE
                                AND NRORG = P_NRORG) LOOP

             V_VRINDICE := V_VRINDICE * (1 + (C_VRINDICE.VRINDICE/100));

          END LOOP;

          V_VRINDICE := V_VRINDICE-1;

          V_VRINDICE := ROUND(V_VRINDICE*100,4);


          OUT_RECORD.NRINDICE        := V_INDICE;
          OUT_RECORD.NRSEQCONTRATO   := C_INDICE.NRSEQCONTRATO;
          OUT_RECORD.NRCONTRATOFCT   := V_NRCONTRATOFCT;
          OUT_RECORD.NRPARCNEGOCIO   := V_NRPARCNEGOCIO;
          OUT_RECORD.NMPRINCIPALPARC := V_NMPRINCIPALPARC;
          OUT_RECORD.CDTPINDICE      := C_INDPADESC.CDTPINDICE;
          OUT_RECORD.IDPERVALOR      := C_INDPADESC.IDPERVALOR;
          OUT_RECORD.CDINDICE        := C_INDPADESC.CDINDICE;
          OUT_RECORD.NMINDICE        := C_INDPADESC.NMINDICE;
          OUT_RECORD.NRPERCENTINDICE := C_INDPADESC.NRPERCENTINDICE;
          OUT_RECORD.VRINDICE        := V_VRINDICE;
          OUT_RECORD.NRORG           := P_NRORG;
          OUT_RECORD.DTINCLUSAO      := SYSDATE;

          PIPE ROW (OUT_RECORD);

      END LOOP;

    END IF;

    IF (V_INDICE = 0) THEN

      FOR C_INDPADRPARAM IN (SELECT FCT_INDICESPADROES.NRINDICESPADROES, FCT_INDICESPADROES.CDTPINDICE, FCT_INDICESPADROES.IDPERVALOR,
                                    FCT_INDICESPADROES.CDINDICE, INDICE.NMINDICE, FCT_INDICESPADROES.NRPERCENTINDICE
                               FROM FCT_INDICESPADROES
                               JOIN INDICE
                                 ON INDICE.CDTPINDICE = FCT_INDICESPADROES.CDTPINDICE
                                AND INDICE.IDPERVALOR = FCT_INDICESPADROES.IDPERVALOR
                                AND INDICE.CDINDICE = FCT_INDICESPADROES.CDINDICE
                                AND INDICE.NRORG = FCT_INDICESPADROES.NRORG
                                AND FCT_INDICESPADROES.NRORG = P_NRORG) LOOP

         V_INDICE := V_INDICE+1;

         V_VRINDICE := 1;

         FOR C_VRINDICE IN (SELECT CDTPINDICE, IDPERVALOR, CDINDICE, DTREFIND, VRINDICE
                              FROM MOVINDICE
                             WHERE TRUNC(DTREFIND) BETWEEN ADD_MONTHS(FIRST_DAY(V_DTREFREAJUSTE), -11) AND LAST_DAY(V_DTREFREAJUSTE)
                               AND CDTPINDICE = C_INDPADRPARAM.CDTPINDICE
                               AND IDPERVALOR = C_INDPADRPARAM.IDPERVALOR
                               AND CDINDICE = C_INDPADRPARAM.CDINDICE
                               AND NRORG = P_NRORG) LOOP

            V_VRINDICE := V_VRINDICE * (1 + (C_VRINDICE.VRINDICE/100));

          END LOOP;

          V_VRINDICE := V_VRINDICE-1;

          V_VRINDICE := ROUND(V_VRINDICE*100,4);

          OUT_RECORD.NRINDICE        := V_INDICE;
          OUT_RECORD.NRSEQCONTRATO   := C_INDICE.NRSEQCONTRATO;
          OUT_RECORD.NRCONTRATOFCT   := V_NRCONTRATOFCT;
          OUT_RECORD.NRPARCNEGOCIO   := V_NRPARCNEGOCIO;
          OUT_RECORD.NMPRINCIPALPARC := V_NMPRINCIPALPARC;
          OUT_RECORD.CDTPINDICE      := C_INDPADRPARAM.CDTPINDICE;
          OUT_RECORD.IDPERVALOR      := C_INDPADRPARAM.IDPERVALOR;
          OUT_RECORD.CDINDICE        := C_INDPADRPARAM.CDINDICE;
          OUT_RECORD.NMINDICE        := C_INDPADRPARAM.NMINDICE;
          OUT_RECORD.NRPERCENTINDICE := C_INDPADRPARAM.NRPERCENTINDICE;
          OUT_RECORD.VRINDICE        := V_VRINDICE;
          OUT_RECORD.NRORG           := P_NRORG;
          OUT_RECORD.DTINCLUSAO      := SYSDATE;

          PIPE ROW (OUT_RECORD);

      END LOOP;

     END IF;

   END LOOP;

   RETURN;

END;
/