IF OBJECT_ID('V_EST_NF_SAI_CARTA_CORRECAO_VALIDACAO') IS NOT NULL
BEGIN
	DROP VIEW V_EST_NF_SAI_CARTA_CORRECAO_VALIDACAO
END
GO

CREATE VIEW [dbo].[V_EST_NF_SAI_CARTA_CORRECAO_VALIDACAO] AS

SELECT
		CD_EMP,
		CD_FILIAL,
		CD_NF,
		NF_NF,
		CD_CTR_CARTA_CORRECAO,
		DS_MOTIVO,
		STS_CARTA,
		STS_VALIDACAO
FROM

(
	SELECT
		VAL.CD_NF,
		VAL.NF_NF,
		VAL.STS_CARTA,
		VAL.DS_MOTIVO,
		SAI.CD_EMP,
		SAI.CD_FILIAL,
		SAI.CD_CTR_CARTA_CORRECAO,
		'VALIDADA' AS STS_VALIDACAO
	FROM NFe..EST_NF_NFE_CARTACORRECAO VAL 
		INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NF_SAI SAI ON
			VAL.CD_NF = SAI.CD_NF
		INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NF_SAI SAI2 ON
			SAI2.CD_NF = SAI.CD_NF
	WHERE 
		VAL.STS_CARTA = 1

	UNION

	SELECT
			INVAL.CD_NF,
			INVAL.NF_NF,
			INVAL.STS_CARTA,
			INVAL.DS_MOTIVO,
			SAI.CD_EMP,
			SAI.CD_FILIAL,
			SAI.CD_CTR_CARTA_CORRECAO,
			'REJEITADA' AS STS_VALIDACAO
		FROM NFe..EST_NF_NFE_CARTACORRECAO INVAL 
			INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NF_ENT SAI ON
				INVAL.CD_NF = SAI.CD_NF
			INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NF_SAI SAI2 ON
				SAI2.CD_NF = SAI.CD_NF
		WHERE 
			INVAL.STS_CARTA = 2 
			AND NOT EXISTS (

				SELECT
					VAL.CD_NF,
					VAL.NF_NF
				FROM NFe..EST_NF_NFE_CARTACORRECAO VAL 
				INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NF_ENT SAI2 ON
					VAL.CD_NF = SAI2.CD_NF
				WHERE 
					VAL.CD_NF = INVAL.CD_NF
					AND VAL.NF_NF = INVAL.NF_NF
					AND VAL.STS_CARTA = 1

			) 

) AS CONS_CARTA_CORRECAO_SAI
GO


