SELECT
	vw_vinculadas.dmda_num_inscr || '/' || vw_vinculadas.dmda_ano AS "Demanda",
	bp.pes_nome AS "Pessoa",
	CASE
		WHEN edra.dmdara_vaga_oferecida = 1
			THEN 'SIM'
		ELSE 'NÃO'
		END AS "Vaga Oferecida",
	edra.dmdara_qm_atendeu AS "Contato",
	edra.dmdara_dthra AS "Data/Hora",
	edra.dmdara_obs AS "Observação"
FROM (
	SELECT *
	FROM (
		/* do mais antigo para o mais recente */
		WITH RECURSIVE
			demanda_vinculada AS (
				SELECT
					ed.dmda_cod,
					ed.dmda_num_inscr,
					ed.dmda_ano,
					ed.dmda_dmdacod
				FROM edu_demanda ed

				WHERE
					ed.dmda_cod = :DMDA_COD

				UNION

				SELECT
					ed.dmda_cod,
					ed.dmda_num_inscr,
					ed.dmda_ano,
					dv.dmda_dmdacod
				FROM edu_demanda ed

				INNER JOIN demanda_vinculada dv
				           ON dv.dmda_cod = ed.dmda_dmdacod
			)
		SELECT *
		FROM demanda_vinculada dv

		WHERE
			  1 = 1
		  AND dmda_cod <> :DMDA_COD
	) AS vw


	UNION ALL

	SELECT *
	FROM (
		/* do mais rescente para o mais antigo */
		WITH RECURSIVE
			demanda_vinculada AS (
				SELECT
					ed.dmda_cod,
					ed.dmda_num_inscr,
					ed.dmda_ano,
					ed.dmda_dmdacod
				FROM edu_demanda ed
				WHERE
					  ed.dmda_cod = :DMDA_COD
				  AND ed.dmda_dmdacod IS NOT NULL
				UNION
				SELECT
					ed.dmda_cod,
					ed.dmda_num_inscr,
					ed.dmda_ano,
					ed.dmda_dmdacod
				FROM edu_demanda ed
				INNER JOIN demanda_vinculada dv
				           ON dv.dmda_dmdacod = ed.dmda_cod
			)
		SELECT *
		FROM demanda_vinculada

		WHERE
			dmda_cod <> :DMDA_COD
	) AS vw
) AS vw_vinculadas

INNER JOIN edu_demanda_status eds
           ON eds.dmdast_dmdacod = vw_vinculadas.dmda_cod
	           AND eds.dmdast_ativo = 1

INNER JOIN bas_status bs
           ON bs.sta_numero = eds.dmdast_status
	           AND bs.sta_tipo = 'DEMANDA_STATUS'

INNER JOIN edu_demanda_reg_atend edra
           ON vw_vinculadas.dmda_cod = edra.dmdara_dmdacod
	           AND edra.dmdara_pescod IS NOT NULL

INNER JOIN bas_pessoa bp
           ON edra.dmdara_pescod = bp.pes_cod

ORDER BY
	edra.dmdara_dthra DESC;