MINSUMOSDERIVADOS

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

CODCOLIGADA

DCODCOLIGADA

null

IDPRJ

int

null

IDCMP

int

null

IDISM

int

null

TIPO

varchar

null

View Definition

CREATE VIEW MINSUMOSDERIVADOS
AS
SELECT DISTINCT REC.CODCOLIGADA
	,REC.IDPRJ
	,REC.IDCMP
	,MDERIV.IDISM
	,'INCLUIR' TIPO
FROM MRECCMP REC  WITH (NOLOCK)
	,MISM MDERIV  WITH (NOLOCK)
WHERE REC.CODCOLIGADA = MDERIV.CODCOLIGADA
	AND REC.IDPRJ = MDERIV.IDPRJ
	AND MDERIV.TIPO = 1
	AND EXISTS (
		SELECT 1
		FROM MRECCMP  WITH (NOLOCK)
		WHERE CODCOLIGADA = REC.CODCOLIGADA
			AND IDPRJ = REC.IDPRJ
			AND IDCMP = REC.IDCMP
			AND IDISM IN (
				SELECT IDISMD
				FROM MISMDERIVACAO A  WITH (NOLOCK)
				WHERE A.IDISM = MDERIV.IDISM
					AND A.ATIVO = 1
					AND A.IDISMD IS NOT NULL
				UNION ALL
				SELECT IDISM
				FROM MISM  WITH (NOLOCK)
				WHERE CODCOLIGADA = REC.CODCOLIGADA
					AND IDPRJ = REC.IDPRJ
					AND IDGIS IN (
						SELECT IDGISD
						FROM MISMDERIVACAO A  WITH (NOLOCK)
						WHERE A.IDISM = MDERIV.IDISM
							AND A.ATIVO = 1
							AND A.IDGISD IS NOT NULL
						)
				)
		UNION ALL
		SELECT IDCMP
		FROM MRECCMP  WITH (NOLOCK)
		WHERE CODCOLIGADA = REC.CODCOLIGADA
			AND IDPRJ = REC.IDPRJ
			AND IDCMP = REC.IDCMP
			AND IDCMPFILHA IN (
				SELECT A.IDCMPD
				FROM MISMDERIVACAO A  WITH (NOLOCK)
				WHERE A.IDISM = MDERIV.IDISM
					AND A.ATIVO = 1
					AND A.IDCMPD IS NOT NULL
				)
		)
	AND MDERIV.TIPOISMDERIVADO IN (
		1
		,2
		,4
		)
	AND NOT EXISTS (
		SELECT 1
		FROM MRECCMP  WITH (NOLOCK)
		WHERE CODCOLIGADA = REC.CODCOLIGADA
			AND IDPRJ = REC.IDPRJ
			AND IDCMP = REC.IDCMP
			AND IDISM = MDERIV.IDISM
		)
	AND EXISTS (
		SELECT 1
		FROM MCMP  WITH (NOLOCK)
		WHERE CODCOLIGADA = REC.CODCOLIGADA
			AND IDPRJ = REC.IDPRJ
			AND IDCMP = REC.IDCMP
			AND (
				(
					TIPOCOMPOSICAO = 2
					AND EXISTS (
						SELECT 1
						FROM MPARAMETRO  WITH (NOLOCK)
						WHERE MPARAMETRO.PARAMETRO = 'CONSIDERARISMDERIVADOTURMATIPICA'
							AND MPARAMETRO.VALORINT = 1
							AND MPARAMETRO.CODCOLIGADA = MCMP.CODCOLIGADA
							AND MPARAMETRO.IDPRJ = MCMP.IDPRJ
						)
					)
				OR TIPOCOMPOSICAO <> 2
				)
		)
UNION ALL
SELECT DISTINCT REC.CODCOLIGADA
	,REC.IDPRJ
	,REC.IDCMP
	,MDERIV.IDISM
	,'EXCLUIR' TIPO
FROM MRECCMP REC  WITH (NOLOCK)
	,MISM MDERIV  WITH (NOLOCK)
WHERE REC.CODCOLIGADA = MDERIV.CODCOLIGADA
	AND REC.IDPRJ = MDERIV.IDPRJ
	AND MDERIV.TIPO = 1
	AND NOT EXISTS (
		SELECT 1
		FROM MRECCMP  WITH (NOLOCK)
		WHERE CODCOLIGADA = REC.CODCOLIGADA
			AND IDPRJ = REC.IDPRJ
			AND IDCMP = REC.IDCMP
			AND IDISM IN (
				SELECT IDISMD
				FROM MISMDERIVACAO A  WITH (NOLOCK)
				WHERE A.IDISM = MDERIV.IDISM
					AND A.ATIVO = 1
					AND A.IDISMD IS NOT NULL
				UNION ALL
				SELECT IDISM
				FROM MISM  WITH (NOLOCK)
				WHERE CODCOLIGADA = REC.CODCOLIGADA
					AND IDPRJ = REC.IDPRJ
					AND IDGIS IN (
						SELECT IDGISD
						FROM MISMDERIVACAO A  WITH (NOLOCK)
						WHERE A.IDISM = MDERIV.IDISM
							AND A.ATIVO = 1
							AND A.IDGISD IS NOT NULL
						)
				)
		UNION ALL
		SELECT IDCMP
		FROM MRECCMP  WITH (NOLOCK)
		WHERE CODCOLIGADA = REC.CODCOLIGADA
			AND IDPRJ = REC.IDPRJ
			AND IDCMP = REC.IDCMP
			AND IDCMPFILHA IN (
				SELECT A.IDCMPD
				FROM MISMDERIVACAO A  WITH (NOLOCK)
				WHERE A.IDISM = MDERIV.IDISM
					AND A.ATIVO = 1
					AND A.IDCMPD IS NOT NULL
				)
		)
	AND REC.IDISM = MDERIV.IDISM
UNION ALL
SELECT DISTINCT MCMP.CODCOLIGADA
	,MCMP.IDPRJ
	,MCMP.IDCMP
	,MISM.IDISM
	,'EXCLUIR'
FROM MCMP  WITH (NOLOCK)
	,MRECCMP  WITH (NOLOCK)
	,MISM  WITH (NOLOCK)
WHERE MRECCMP.CODCOLIGADA = MCMP.CODCOLIGADA
	AND MRECCMP.IDPRJ = MCMP.IDPRJ
	AND MRECCMP.IDCMP = MCMP.IDCMP
	AND MRECCMP.CODCOLIGADA = MISM.CODCOLIGADA
	AND MRECCMP.IDPRJ = MISM.IDPRJ
	AND MRECCMP.IDISM = MISM.IDISM
	AND MCMP.TIPOCOMPOSICAO = 2
	AND MISM.TIPO = 1
	AND EXISTS (
		SELECT 1
		FROM MPARAMETRO  WITH (NOLOCK)
		WHERE MPARAMETRO.PARAMETRO = 'CONSIDERARISMDERIVADOTURMATIPICA'
			AND MPARAMETRO.VALORINT = 0
			AND MPARAMETRO.CODCOLIGADA = MCMP.CODCOLIGADA
			AND MPARAMETRO.IDPRJ = MCMP.IDPRJ
		)

Columns

View Definition

Possibly Referenced Tables/Views