CREATE OR REPLACE VIEW RETORNA_CONS_ATIVO
AS
   SELECT NMCONSUMIDOR ALUNO, DTNASCCONS DTNASCALUNO, NMCONSUMIDOR APELIDO,
          CDIDCONSUMID MATRICULA,
          'Ativo' ATIVO,
          IDSEXOCONS SEXO, CDCCUSCLIE CODTURMA,
          DSEMAILCONS EMAILALUNO, NRTELECONS TELEFONEALUNO, NRCELULARCONS NUMSMSAVISO,
          '' CODCURSO
     FROM CONSUMIDOR C, (SELECT E.CDCLIENTE, E.CDCONSUMIDOR, E.CDFAMILISALD, E.DTMOVEXTCONS, SUM(E.VRSALDCONEXT) VRSALDCONEXT
                                    FROM EXTRATOCONS E, (SELECT E.CDCLIENTE, E.CDCONSUMIDOR, E.CDFAMILISALD, D.DTMOVEXTCONS, MAX(E.NRSEQMOVEXT) AS NRSEQMOVEXT
                                                           FROM EXTRATOCONS E, (SELECT E.CDCLIENTE, E.CDCONSUMIDOR, E.CDFAMILISALD, MAX(DTMOVEXTCONS) AS DTMOVEXTCONS
                                                                                  FROM EXTRATOCONS E, CLIENFILIAL CF, PARAVEND P
                                                                                 WHERE E.CDCLIENTE = CF.CDCLIENTE
                                                                                   AND CF.CDFILIAL = P.CDFILIAL
                                                                                   AND P.IDUTINTCATBIO = 'S'
                                                                                 GROUP BY E.CDCLIENTE, E.CDCONSUMIDOR, E.CDFAMILISALD) D
                                                          WHERE E.CDCLIENTE = D.CDCLIENTE
                                                            AND E.CDCONSUMIDOR = D.CDCONSUMIDOR
                                                            AND E.CDFAMILISALD = D.CDFAMILISALD
                                                            AND E.DTMOVEXTCONS = D.DTMOVEXTCONS
                                                          GROUP BY E.CDCLIENTE, E.CDCONSUMIDOR, E.CDFAMILISALD, D.DTMOVEXTCONS) DT
                                   WHERE E.CDCLIENTE = DT.CDCLIENTE
                                     AND E.CDCONSUMIDOR = DT.CDCONSUMIDOR
                                     AND E.CDFAMILISALD = DT.CDFAMILISALD
                                     AND E.DTMOVEXTCONS = DT.DTMOVEXTCONS
                                     AND E.NRSEQMOVEXT = DT.NRSEQMOVEXT
                                   GROUP BY E.CDCLIENTE, E.CDCONSUMIDOR, E.CDFAMILISALD, E.DTMOVEXTCONS) SALDO, CLIENTE CL
    WHERE C.CDCLIENTE = CL.CDCLIENTE
      AND C.CDCLIENTE = SALDO.CDCLIENTE
      AND C.CDCONSUMIDOR = SALDO.CDCONSUMIDOR
      AND NVL(SALDO.VRSALDCONEXT,0) >= CL.VRSALDMIN
    ORDER BY C.CDCLIENTE, C.CDCONSUMIDOR
/

-- VERSAO --
BEGIN ATUVEROBJ('RETORNA_CONS_ATIVO','6.87.xxx'); END;
/