SQL018.sql

/*
	SQL para criação de relatório Carta de Cobrança
	Parametros: CODTURMA
	CREATE BY Bitts	(22/08/2016)
	
*/
SELECT 	
	G.CODTURMA AS TURMA,
	F.RA,
	I.NOME AS ALUNO,
	G.CODSTATUS ,
	J.DESCRICAO,
	G.IDPERLET,
	K.DESCRICAO AS PERIODO_LETIVO,
	B.IDCFO, 
	B.NOMEFANTASIA AS RESPONSAVEL_FINANCEIRO, 
	E.NOME AS SERVICO,
	D.PARCELA,
	MONTH(A.DATAVENCIMENTO) MES,
	CONVERT(VARCHAR, A.DATAVENCIMENTO, 103) AS VENCIMENTO,
	A.VALORORIGINAL TOTAL
FROM 
	FLAN AS A (NOLOCK)
		LEFT JOIN FCFO AS B (NOLOCK) ON
			A.CODCOLIGADA = B.CODCOLIGADA AND
			A.CODCFO = B.CODCFO
		LEFT JOIN SLAN AS C (NOLOCK) ON
			A.CODCOLIGADA = C.CODCOLIGADA AND
			A.IDLAN = C.IDLAN
		LEFT JOIN SPARCELA AS D (NOLOCK) ON
			C.CODCOLIGADA = D.CODCOLIGADA AND
			C.IDPARCELA = D.IDPARCELA
		LEFT JOIN SSERVICO AS E (NOLOCK) ON
			D.CODCOLIGADA = E.CODCOLIGADA AND
			D.CODSERVICO = E.CODSERVICO	
		LEFT JOIN SCONTRATO AS F (NOLOCK) ON
			D.CODCOLIGADA = F.CODCOLIGADA AND 
			D.CODCONTRATO = F.CODCONTRATO AND 
			D.IDPERLET = F.IDPERLET AND 
			D.RA = F.RA
		LEFT JOIN SMATRICPL AS G (NOLOCK) ON
			F.CODCOLIGADA = G.CODCOLIGADA AND
			F.CODCONTRATO = F.CODCONTRATO AND
			F.CODFILIAL = G.CODFILIAL AND
			F.RA = G.RA AND
			F.IDPERLET = G.IDPERLET AND
			F.IDHABILITACAOFILIAL = G.IDHABILITACAOFILIAL
		LEFT JOIN SALUNO H (NOLOCK) ON
			G.RA = H.RA AND
			G.CODCOLIGADA = H.CODCOLIGADA
		LEFT JOIN PPESSOA AS I (NOLOCK) ON
			I.CODIGO = H.CODPESSOA
		LEFT JOIN SSTATUS AS J (NOLOCK) ON
			G.CODCOLIGADA = J.CODCOLIGADA AND
			G.CODSTATUS = J.CODSTATUS
		LEFT JOIN SPLETIVO AS K (NOLOCK) ON
			G.CODCOLIGADA = K.CODCOLIGADA AND
			G.CODFILIAL = K.CODFILIAL AND
			G.IDPERLET = K.IDPERLET
WHERE 
	B.IDCFO NOT IN (2358) AND
	F.CODFILIAL = 3 AND
	A.STATUSLAN = 0 AND
	A.DATAVENCIMENTO < GETDATE() AND
	G.CODTURMA = 'EF31' 
	
	--AND D.RA = '0003435'