Primeiras Parcelas de Mensalidade Pagas

matriculas_pagas.sql

SELECT
    MP.RA,
    MP.IDHABILITACAOFILIAL,
    MP.IDPERLET,
    MP.CODSTATUS,
    FL.DATABAIXA
FROM
    vw_matricula vm
    JOIN mat_curso_processo_pagamento mcpp (NOLOCK)
         ON mcpp.id = vm.planoPagamentoId
    JOIN mat_curso_processo mcp (NOLOCK)
         ON mcp.cursoId = mcpp.cursoId AND mcp.processoId = mcpp.processoId
    JOIN mat_processo processo (NOLOCK)
         ON mcp.processoId = processo.id
    JOIN Corpore.dbo.SMATRICPL MP (NOLOCK)
         ON mcp.idHabilitacaoFilial = mp.IDHABILITACAOFILIAL AND vm.ra = mp.RA
    JOIN Corpore.dbo.SPLETIVO PL (NOLOCK)
         ON MP.CODCOLIGADA = PL.CODCOLIGADA AND MP.IDPERLET = PL.IDPERLET AND PL.CODPERLET = vm.periodoLetivo
    JOIN Corpore.dbo.SCONTRATO (NOLOCK) CT
         ON MP.CODCOLIGADA = CT.CODCOLIGADA AND MP.IDPERLET = CT.IDPERLET AND
            MP.IDHABILITACAOFILIAL = CT.IDHABILITACAOFILIAL AND MP.RA = CT.RA
    JOIN Corpore.dbo.SPARCELA P (NOLOCK)
         ON CT.CODCOLIGADA = P.CODCOLIGADA AND CT.RA = P.RA AND CT.CODCONTRATO = P.CODCONTRATO AND
            CT.IDPERLET = P.IDPERLET
             AND P.PARCELA = 1
    JOIN Corpore.dbo.SLAN SL (NOLOCK)
         ON P.CODCOLIGADA = SL.CODCOLIGADA AND P.IDPARCELA = SL.IDPARCELA
    JOIN Corpore.dbo.FLAN FL (NOLOCK)
         ON SL.IDLAN = FL.IDLAN AND SL.CODCOLIGADA = FL.CODCOLIGADA
    JOIN Corpore.dbo.FLANBOLETO FLB (NOLOCK)
         ON FL.IDLAN = FLB.IDLAN AND FL.CODCOLIGADA = FLB.CODCOLIGADA
    JOIN Corpore.dbo.FBOLETO FB (NOLOCK)
         ON FLB.CODCOLIGADA = FB.CODCOLIGADA AND FLB.IDBOLETO = FB.IDBOLETO
    JOIN Corpore.dbo.FLANBAIXA FLBX (NOLOCK)
         ON FL.IDLAN = FLBX.IDLAN AND FL.CODCOLIGADA = FLBX.CODCOLIGADA
WHERE
      matriculaFinalizada = 1
  AND MP.CODSTATUS = processo.codStatusPeriodoLetivo
  AND FL.DATABAIXA IS NOT NULL
  AND MP.CODSTATUS NOT IN (17, 71)