QUERY1312

ALTER VIEW [dbo].[vw_consulta_relatorio_inscricoes] AS
	SELECT
	TAB.nr_simulacao,
	TAB.cd_instituidor,
	TAB.cd_param,
	TAB.cd_agencia,
	--
	TAB.cd_plano,
	TAB.ds_plano,
	TAB.nm_instituidor,
	TAB.nm_agencia,
	TAB.id_situacao,
	TAB.nm_associado,
	TAB.nr_cpf,
	TAB.nm_instituicao,
	TAB.nr_inscricao_oficial,
	TAB.dt_inscricao,
	TAB.dt_inclusao,
	TAB.dt_doc,
	SUM(TAB.vl_contribuicao) AS vl_contribuicao,
	SUM(TAB.vl_contr_morte) AS vl_contr_morte,
	SUM(TAB.vl_contr_inval) AS vl_contr_inval,
	TAB.ds_vendedor,
	TAB.id_origem,
	TAB.nr_idade,
	TAB.is_assinado_digitalmente,
	TAB.situacao_risco,
	TAB.data_pgto_risco,
	TAB.dt_inscricao_risco
	TAB.angariador_digital
FROM
(	SELECT
		--
		qpi.nr_simulacao,
		qi.cd_instituidor,
		qp.cd_param,
		qa.cd_agencia,
		qp.cd_param as cd_plano,
		qp.ds_plano,
		qi.nm_instituidor,
		qa.nm_agencia,
		qpi.id_situacao, --situacao da previdência
		qpi.nm_associado,
		qpi.nr_cpf,
		ins.nm_instituicao,
		qpi.nr_inscricao_oficial,
		qpi.dt_inscricao, --data de inscricao da previdencia
		qpi.dt_inclusao,
		qv.angariador_digital
		( /* recupera a data de pagto da previdencia (em separado ou tudo, se for tudo de uma vez só) */
			select MIN(dd.dt_doc) --haverá casos em que a o valor da prev e do risco será igual, então traz a menor data, pq TEORICAMENTE a previdencia se paga antes
			from qta_registra_doc dd
			join qta_proposta_inscricao ii on (dd.nr_inscricao_oficial = ii.nr_inscricao_oficial)
			left join qta_proposta_inscricao_risco rr on (rr.nr_inscricao_oficial = ii.nr_inscricao_oficial AND rr.nr_simulacao = ii.nr_simulacao)
			where dd.nr_inscricao_oficial = qrd.nr_inscricao_oficial
			and dd.id_origem = 2
			and (
				((ISNULL(ii.vl_contribuicao, 0)) = dd.vl_doc)
				or
				((ISNULL(ii.vl_contribuicao, 0) + ISNULL(rr.vl_contr_inval, 0) + ISNULL(rr.vl_contr_morte, 0)) = dd.vl_doc)
			)
		) AS dt_doc,
		CASE
			WHEN qrd.id_origem = 2 AND (ISNULL(qpi.vl_contribuicao, 0)) = qrd.vl_doc THEN ISNULL(qpi.vl_contribuicao, 0)
			WHEN qrd.id_origem = 2 AND (ISNULL(qpi.vl_contribuicao, 0) + ISNULL(qpir.vl_contr_inval, 0) + ISNULL(qpir.vl_contr_morte, 0)) = qrd.vl_doc THEN ISNULL(qpi.vl_contribuicao, 0)
			ELSE 0
		END AS vl_contribuicao,
		CASE
			WHEN qrd.id_origem = 2 AND (ISNULL(qpir.vl_contr_inval, 0) + ISNULL(qpir.vl_contr_morte, 0)) = qrd.vl_doc THEN ISNULL(qpir.vl_contr_morte, 0)
			WHEN qrd.id_origem = 2 AND (ISNULL(qpi.vl_contribuicao, 0) + ISNULL(qpir.vl_contr_inval, 0) + ISNULL(qpir.vl_contr_morte, 0)) = qrd.vl_doc THEN ISNULL(qpir.vl_contr_morte, 0)
			ELSE 0
		END AS vl_contr_morte,
		CASE
			WHEN qrd.id_origem = 2 AND (ISNULL(qpir.vl_contr_inval, 0) + ISNULL(qpir.vl_contr_morte, 0)) = qrd.vl_doc THEN ISNULL(qpir.vl_contr_inval, 0)
			WHEN qrd.id_origem = 2 AND (ISNULL(qpi.vl_contribuicao, 0) + ISNULL(qpir.vl_contr_inval, 0) + ISNULL(qpir.vl_contr_morte, 0)) = qrd.vl_doc THEN ISNULL(qpir.vl_contr_inval, 0)
			ELSE 0
		END AS vl_contr_inval,
		qv.ds_vendedor,
		qrd.id_origem,
		CASE
			WHEN DATEADD(YEAR, DATEDIFF(YEAR, qpi.dt_nascimento, qpi.dt_inclusao), qpi.dt_nascimento) > qpi.dt_inclusao THEN DATEDIFF(YEAR, qpi.dt_nascimento, qpi.dt_inclusao) - 1
			ELSE DATEDIFF(YEAR, qpi.dt_nascimento, qpi.dt_inclusao)
		END AS nr_idade,
		CASE
			WHEN qrd.id_origem = 2 AND (ISNULL(qpi.vl_contribuicao, 0)) = qrd.vl_doc THEN qpi.is_assinado_digitalmente
			WHEN qrd.id_origem = 2 AND (ISNULL(qpi.vl_contribuicao, 0) + ISNULL(qpir.vl_contr_inval, 0) + ISNULL(qpir.vl_contr_morte, 0)) = qrd.vl_doc THEN qpi.is_assinado_digitalmente
			ELSE qpir.is_assinado_digitalmente
		END AS is_assinado_digitalmente,
		qpir.id_situacao AS situacao_risco,
		( /* recupera a data de pagto do risco (pode ter sido junto ou separado) */
			select MAX(dd.dt_doc) --haverá casos em que a o valor da prev e do risco será igual, então traz a MAIOR data, pq TEORICAMENTE o risco se paga por último
			from qta_registra_doc dd
			join qta_proposta_inscricao ii on (dd.nr_inscricao_oficial = ii.nr_inscricao_oficial)
			left join qta_proposta_inscricao_risco rr on (rr.nr_inscricao_oficial = ii.nr_inscricao_oficial AND rr.nr_simulacao = ii.nr_simulacao)
			where dd.nr_inscricao_oficial = qrd.nr_inscricao_oficial
			and dd.id_origem = 2
			and (
				((ISNULL(rr.vl_contr_inval, 0) + ISNULL(rr.vl_contr_morte, 0)) = dd.vl_doc)
				or
				((ISNULL(ii.vl_contribuicao, 0) + ISNULL(rr.vl_contr_inval, 0) + ISNULL(rr.vl_contr_morte, 0)) = dd.vl_doc)
			)
		) AS data_pgto_risco,
		qpir.dt_inscricao as dt_inscricao_risco

		FROM
			qta_parametros qp
			INNER JOIN qta_instituidor qi ON (qp.cd_param = qi.cd_plano)
			INNER JOIN qta_agencia qa ON (qi.cd_instituidor = qa.cd_instituidor)
			INNER JOIN qta_proposta_inscricao qpi ON (qp.cd_param = qpi.cd_plano AND qi.cd_instituidor = qpi.cd_instituidor AND qa.cd_agencia = qpi.cd_agencia)
			LEFT JOIN qta_proposta_inscricao_risco qpir ON (qpir.nr_inscricao_oficial = qpi.nr_inscricao_oficial AND qpir.nr_simulacao = qpi.nr_simulacao)
			INNER JOIN qta_vendedor qv ON (qv.cd_vendedor = qpi.cd_vendedor)
			LEFT JOIN qta_instituicao ins ON (ins.cd_instituicao = qpi.cd_instituicao)
			LEFT JOIN qta_registra_doc qrd ON(qrd.nr_inscricao_oficial = qpi.nr_inscricao_oficial)

		WHERE
			(qrd.id_origem not in (1, 3, 4) or qrd.id_origem is null)
) TAB
GROUP BY
	TAB.nr_simulacao,
	TAB.cd_instituidor,
	TAB.cd_param,
	TAB.cd_agencia,
	--
	TAB.cd_plano,
	TAB.ds_plano,
	TAB.nm_instituidor,
	TAB.nm_agencia,
	TAB.id_situacao,
	TAB.nm_associado,
	TAB.nr_cpf,
	TAB.nm_instituicao,
	TAB.nr_inscricao_oficial,
	TAB.dt_inscricao,
	TAB.dt_inclusao,
	TAB.dt_doc,
	TAB.ds_vendedor,
	TAB.id_origem,
	TAB.nr_idade,
	TAB.is_assinado_digitalmente,
	TAB.situacao_risco,
	TAB.data_pgto_risco,
	TAB.angariador_digital,
	TAB.dt_inscricao_risco;